When you set the Automatic Invoice Numbering to Yes while defining the batch source. Sometime invoice numbers are skipped by 20 instead of sequencial numbering. This happens due to Oracle Application creating the automatic number
sequencing with CACHE 20 option.

To minimize this change the sequences used to generate Invoice Numbers to not use caching. This can be done via a SQL statement such as:-
      ALTER SEQUENCE RA_TRX_NUMBER__S NOCACHE;

To find out the batch_source_id query the table
RA_BATCH_SOURCES_ALL table

SELECT batch_source_id
FROM RA_BATCH_SOURCES_ALL
WHERE name = 'Batch Name'

But setting the NOCACHE option affect the performance of Invoice entry in very high volume situations.

Return to : Oracle Database, SQL, Application, Programming Tips