|Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today|
Lets revisit some Basic things
- Invoice Numbers/Transaction Number : These are Numbers generated and controlled by the Source definition. Oracle Reference column is TRX_NUMBER.
Document Numbers : This you need to define a Sequence & assign it to the Document Category which is the AR Transaction Type. Oracle Reference column is DOCUMENT_SEQUENCE_VALUE.
Why Transaction Number sequence is so important
As far as I know, some of European Countries(Italy) and some of APAC(Taiwan, Singapore) Countries have to maintain the Transaction number in sequence. Any Jump in the transaction number may be subject to audit issue with auditors. Therefore its very important to alter the underline seeded sequence definition.
Why Oracle gives some jump in number
The reason is cache value which comes with seeded sequence .
If you have "caching" enabled on the sequences. This is a feature that is meant to speed up retrieval of heavily used sequences. If the sequence isn't used for a while the cache "ages out" and those numbers that were in the cache get skipped.
Most of Oracle seeded sequence come with Default 20 values.
Technical note on Oracle Sequence
Based out of Robert Vollman note in his blog. Basically there are 2 potential issues with Oracle sequences
- The sequence number may "jump", that is 1,2,3,5,6,8,... with number 4 and 7 missing. One reason is if you do a transaction with NEXTVAL and then rollback, the sequence doesn't roll back to where you started.
- By default, the sequence caches the 20 next values for faster retrieval. Thats the reason why most of seeded Oracle sequence have value 20. If there is a system failure, we will lost the cached values upon system recovery. Thus you end up with a gap of up to 20. One way to avoid this is to use NOCACHE command when creating the sequence. And of course you lose the benefit cache brings as well.
Step to alter the Seeded sequence
1) Query up your invoice source , which user have reported Jump in number.
2) Execute the following script using your source as in step 1.
or use this query if you have access from database.
3) Using the batch_source_id from the above query , execute the followingscript:
4) Execute the following:
Example, if your sequnce is RA_TRX_NUMBER_1146_1166_S
then script would be
1.When you create a source and attach a sequence, Oracle will create a sequence like RA_TRX_NUMBER_<batch_id>_<org_id>_S
2. Sequence RA_TRX_NUMBER_<batch_id>_<org_id> _S as is owned by AR.
How to handle those missing Number
If you have identified in early stage, and missing numbers are very less in count, than create another source with non-automatic number sequence and tell user to use those number which havn't used by Oracle earlier. Once you used all missing number than end date the source.