How to reset the sequence value when a date is changed ? (Urgent)
I have a requirement where my procedure needs to print the invoices for the customers at a routine process daily.
Assume that this procedure will run n number of times per a day. Inside the procedure I should have a
default value which has to be printed on the bills. It should have the current system date + counter.
Example 10072004 || 0001 -----------------> 100720040001
The value of the counter should be reset to zero when the current system date has been changed.I can use the
counter from the sequence generator, but how can I reset to zero and how to check the system date.
I think you're going to have to serialise on (say) a single-row table containing the EffectiveDate and FirstSequence, in order to deal with the date change & sequence reset. This will only happen once per day so shouldn't cause performance problems (?) - the rest of the time you can just use the sequence.nextval:
a) check & deal with date change:
Select * from serial_table
where EffectiveDate <> trunc(sysdate) for update;
If you get a row, update it with trunc(sysdate) & my_seq.nextval.
b) in all cases:
Build your Invoice number out of: sysdate & (my_seq.nextval - FirstSequence)