- OracleApps Epicenter - http://www.oracleappshub.com -
Where is my Missing Transaction Numbers?
Posted By Sanjit Anand On April 25, 2009 @ 5:14 pm In Oracle Receivable | 1 Comment
Lets revisit some Basic things
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
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:
- COMMIT ;
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.
Article printed from OracleApps Epicenter: http://www.oracleappshub.com
URL to article: http://www.oracleappshub.com/accounts-receivable/where-is-my-missing-transaction-numbers/
URLs in this post:
 Robert Vollman: http://thinkoracle.blogspot.com/2006/02/oracle-sequences.html
 SELECT: http://www.oracle.com/pls/db92/db92.drilldown?word=SELECT
 FROM: http://www.oracle.com/pls/db92/db92.drilldown?word=FROM
 WHERE: http://www.oracle.com/pls/db92/db92.drilldown?word=WHERE
 LIKE: http://www.oracle.com/pls/db92/db92.drilldown?word=LIKE
 AND: http://www.oracle.com/pls/db92/db92.drilldown?word=AND
 ORDER: http://www.oracle.com/pls/db92/db92.drilldown?word=ORDER
 BY: http://www.oracle.com/pls/db92/db92.drilldown?word=BY
 ALTER: http://www.oracle.com/pls/db92/db92.drilldown?word=ALTER
 COMMIT: http://www.oracle.com/pls/db92/db92.drilldown?word=COMMIT
Disclaimer The opinions expressed here represent solely my own represent personal conjecture based upon experience, practice and observation and does not represent the thoughts, intentions, plans or strategies of my current & previous employers and their clients. Copyright © 2007-2010 OracleApps Epicenter. All rights reserved.