Where is my Missing Transaction Numbers?

Posted on April 25th, 2009 by Sanjit Anand | Print This Post | Email This Post

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

1. 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.
2. 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.

Navigate>Setup>Invoice>Source

2) Execute the following script using your source as in step 1.

 
SELECT batch_source_id, org_id, name FROM ra_batch_sources_all WHERE name LIKE '<source>';
 

or use this query if you have access from database.

 
SELECT 'alter sequence AR.'||seq.sequence_name||' nocache;' FROM
(SELECT
name bsname, org_id org,
'RA_TRX_NUMBER_'||BATCH_SOURCE_ID||'_'||org_id||'_S' seqname
FROM RA_batch_sources_all) src,
dba_sequences seq
WHERE src.seqname=seq.sequence_name
AND seq.cache_size;
ORDER BY org
/
 
 

3) Using the batch_source_id from the above query , execute the followingscript:

 
SELECT sequence_owner, sequence_name, cache_size 
FROM all_sequences 
WHERE sequence_name LIKE 'RA_TRX_NUMBER%1146%'; 
 
 

4) Execute the following:

 
ALTER sequence SEQUENCE_NAME increment BY 1 nocache; 
COMMIT; 
 
 

Example, if your sequnce is RA_TRX_NUMBER_1146_1166_S

then script would be

 
ALTER sequence RA_TRX_NUMBER_1146_1166_S increment BY 1 nocache;     
 

Few Tips

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.

1. Auto Invoice: Transaction Flexfield
2. R12 SLA: Transaction Account Builder
3. Why “Alternative Payee” is missing in Oracle Payable/Payment
4. Know some of the concurrent managers