Home » SQL & PL/SQL » SQL & PL/SQL » Transactions than can be rejected, temporary tables or not? (11.1.2.10)
Transactions than can be rejected, temporary tables or not? [message #673777] Mon, 10 December 2018 04:42 Go to next message
OraFerro
Messages: 387
Registered: July 2011
Senior Member
Hi All,

I need consultation regarding a design issue. I have a transaction (creation of voucher, journal entry, and sub-ledger), data are entered and they are prone to approval/rejection. Since data affects more than one table, I though of the following options:
1- Create a temporary structure of tables (temp_voucher, temp_journal, temp_sublegdger)
- This way tables that contain approved data will not be affected unless transaction is approved.
- No disruption to VOUCHER_NUMBER that is automatically created when new record has been entered and its sequential nature is important to business.
- Log data is available in case there is a need to refer to rejected transactions.
However
- Extra tables are created.
- Copy approved transaction on the approved-data tables is not straight forward as there are business rules that should be checked.

2- Use the same structure but with different key ranges or add new indicator field (is_temporary)
- No extra tables created
- Same insert business rules
- Possibility to consider temporary data (inserted before approval) in new business rules (from one table)
- Simple actions in case of approval or rejection
However
- Reports and other PLSQL queries should be adjusted to exclude temporary data
- Create entries with fake VOUCHER_NUMBER and then adjust it in case of approval across all tables (referential integrity)

3-Other solutions that I am not aware of.

Thanks,
Ferro

[Updated on: Mon, 10 December 2018 04:46]

Report message to a moderator

Re: Transactions than can be rejected, temporary tables or not? [message #673781 is a reply to message #673777] Mon, 10 December 2018 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67368
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Until you commit the new data are not visible to others.

Quote:
No disruption to VOUCHER_NUMBER that is automatically created when new record has been entered and its sequential nature is important to business.
This should not. Your business rules are flawed, fix them first then you will see there is no more problem.

Re: Transactions than can be rejected, temporary tables or not? [message #673811 is a reply to message #673781] Mon, 10 December 2018 21:55 Go to previous messageGo to next message
OraFerro
Messages: 387
Registered: July 2011
Senior Member
Hi Michel,

Thanks for your reply.

Quote:

Until you commit the new data are not visible to others.
I agree, but I need the data to be viewed by others in order to review it and approve/disapprove the transaction. Which is why I though of options 1 and 2 and asked for consultation. What do you think?

Quote:

No disruption to VOUCHER_NUMBER that is automatically created when new record has been entered and its sequential nature is important to business.
VOUCHER_NUMBER is not the primary key of the table, its a business sequence that presents the Voucher Number in accounting. Despite the fact that sometimes VOUCHER NUMBERS are cancelled or reversed, accountants respect this number and try to keep a gap-less sequence for audit purposes. Got me?

Thanks,
Ferro
Re: Transactions than can be rejected, temporary tables or not? [message #673814 is a reply to message #673811] Tue, 11 December 2018 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67368
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
try to keep a gap-less sequence

So they accept gap.

Quote:
... for audit purposes

How audit can be impacted by a sequence number?

You point yourself to the flaw: "Despite the fact that sometimes VOUCHER NUMBERS are cancelled or reversed,", this implies you can"t have gap-less sequence, and if you reuse canceled numbers then sequence is no more a sequence as the numbers go back and forth..

Quote:
What do you think?

Why not just a STATUS column in your table?



Re: Transactions than can be rejected, temporary tables or not? [message #673816 is a reply to message #673814] Tue, 11 December 2018 01:23 Go to previous messageGo to next message
OraFerro
Messages: 387
Registered: July 2011
Senior Member
Thanks Michel for your detailed reply, appreciated really...

Quote:

You point yourself to the flaw
I agree with you, it is not something holy but I meant it is not the same flaw as if I am trying to make sure that the sequence-generated primary key is gap-less. Its just a business preference and its not recommended to take a direction that would make this field ALWAYS not in sequence.

Quote:

Why not just a STATUS column in your table?
So your recommendation is option 2 in my choices
Quote:
Use the same structure ....add new indicator field (is_temporary)
to act as a status?

Thanks,
Ferro

[Updated on: Tue, 11 December 2018 01:24]

Report message to a moderator

Re: Transactions than can be rejected, temporary tables or not? [message #673817 is a reply to message #673816] Tue, 11 December 2018 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67368
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
if I am trying to make sure that the sequence-generated primary key is gap-less.
You just can't do it without using a manual sequence and blocking everyone when one is working.
Note that in your previous post you said "VOUCHER_NUMBER is not the primary key of the table", so is it or not?


Quote:
So your recommendation is

Not a recommendation as I don't know the details of your environment (including organization one) just a thought and just this without any fake numbers.

Re: Transactions than can be rejected, temporary tables or not? [message #673818 is a reply to message #673817] Tue, 11 December 2018 01:48 Go to previous messageGo to next message
OraFerro
Messages: 387
Registered: July 2011
Senior Member
Dear Michel,

VOUCHER_NUMBER is not the primary key of the table. So when I try to keep it in sequence I am not committing a flaw as if I was trying to keep a primary-key in sequence. It is just another field where keeping it in sequence is a business preference.

For the recommendation, based on my analysis of pros and cons of each option, would you go for a temporary table (option 1) or use the same table with a status indicator (option 2)?

Thanks,
Ferro
Re: Transactions than can be rejected, temporary tables or not? [message #674037 is a reply to message #673818] Fri, 28 December 2018 13:51 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
The only thing I can think of is to store the voucher number as a negative number (negative sequence?) when it is approved then do the following

1) lock the table exclusive.
2) select max(voucher_number) + 1
3) update the negative voucher number in all files to the value found in 2.
4) commit, which releases the table lock.

Since you are ONLY locking the table when you are approving the voucher the lock should be very quick and the indicator that it needs to be approved/cancelled is that it has a negative voucher number. If it is cancelled then simply delete all rows with the negative voucher value.

[Updated on: Fri, 28 December 2018 13:52]

Report message to a moderator

Re: Transactions than can be rejected, temporary tables or not? [message #674038 is a reply to message #674037] Fri, 28 December 2018 14:09 Go to previous message
Michel Cadot
Messages: 67368
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is what I meant by
Quote:
You just can't do it without using a manual sequence and blocking everyone when one is working.

But then the sequence are not in the order of creation time (which is the usual sequence) but approval one (which may not be legal).

Previous Topic: How to flatten one column from multiple rows
Next Topic: TRIM function
Goto Forum:
  


Current Time: Wed Sep 23 21:13:07 CDT 2020