Oracle Ar Invoice Numbering Sequence alter to avoid missing in between by cache

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;