| Batch creation at 10 records [merged by jd] [message #535373] |
Tue, 13 December 2011 14:16  |
 |
Llagi
Messages: 2
Registered: December 2011
|
Junior Member |
|
|
Hi,
Would appreciate help with my request.
I'm working on a Self assessment project regarding our tax returns. Currently, this is how it works - a return lodged generates a return number, but is batched later. In the change proposed, they want the same process whereby a return is generated still, but at a count of 10 returns generated on the same screen, a batch is to be created and these 10 returns will have to be added to that batch. We are on Oracle 10G and work with Forms, Reports10G and TOAD/SQL Plus as tools so I was thinking of changing it on Post-Query but suggestions are to add on to System Parameter table.
Any help is highly appreciated...
Thanks.
|
|
|
|
|
|
|
|
| Re: Batch creation at 10 records [merged by jd] [message #536465 is a reply to message #536411] |
Wed, 21 December 2011 01:20  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I think I understand now; thank you for the explanation.
You said that 10 returns make 1 batch. If you want to do that automatically, I can think of two options.
Step 0 (zero): create a stored procedure which will do the "smart" part of the job - count returns per an individual user and - if there are 10 returns, generate a new batch number and assign it to these returns.
The first option means that you should create a database trigger which would fire AFTER INSERT into the TAX_RETURNS table. It would call the procedure (created in step 0) which would do the rest of the job.
Another one is to schedule step 0 procedure to run as a job (you'd use DBMS_SCHEDULER package to do that). How frequently? I don't know, it most probably depends on how many returns you have. If there are 2 returns a day, you could schedule it daily. If there are 2 returns per hour, you'd schedule it every 10 minutes or so. If there are far too many returns (such as CSV file load via SQL*Loader), option 1 is probably a better choice, unless you don't really care WHEN a batch number is set, so you could schedule a job once a day (for example, at 2 AM, when nobody is working).
[Updated on: Wed, 21 December 2011 01:21] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
