Dashboards - Business Intelligence

Gapless Transaction Numbering

Oct 21, 2008 AR

In Oracle Apps, each transaction id, Document numbers etc is derived from Database sequences. These sequences increment in value everytime you use them. When you are in Oracle Apps, it fetchs 20 or so sequences in bulk when you are in the process of creating invoices or any kind of transactions. For e.g if you login to create a single invoice and let say the current sequence is at 1000, then it will fetch 20 sequences and the next time anybody queries the sequence it will start the counter from 1020. So when you log in later to create a second invoice the number will start from 1020. So effectively there is a GAP in the numbering.

100% gapless transaction numbering is not guaranteed even with the cache on the
sequence turned off. In Release 11 and 11i,one can make the invoice numbering
follow document sequencing and this will bring the system closer to 100%
gapless numbering.
Setup Steps:
           1. Define a Transaction Type.
           (N) Setup -> Transactions -> Transaction Type
           2. Define a Batch Source that will copy the document number to
           the transaction number.
           (N) Setup -> Transactions -> Sources
           You may choose to manually number or automatically number your invoi
           since in the end the transaction number will be the document number.
           Check the box Copy Document Number to Transaction Number.
           Save your work.
.
3. Define Document sequencing in Sysadmin
           a. Define Sequence
           (N) Application -> Document -> Define
           Choose sequence type of Gapless
           Note: It is not necessary to define a separate sequence for each
           transaction you enter. You may decide to define only 3 sequences
           one for transactions, one for receipts and one for adjustments.
.
b. Define Document Categories
           (N) Application -> Document -> Categories
           Ensure that the name of the specific transaction type, payment
           method, and receivables activity is in the CODE field.
           For transactions the tarble is RA_CUSTOMER_TRX
           For receipts the table is AR_CASH_RECEIPTS
           For Adjustments the table is AR_ADJUSTMENTS
           Note: One must define a category for every transaction type, payment
           method, and receivables activity that you have defined. The categoris
           are based on table names.
.
c. Assign the Sequences to the categories
           (N) Application -> Document -> Assign
           The category you created will be in the list of values as well
           as the sequence that you defined.
.
4. Set the profile options in System Administration:
           (N) Profile -> System
           Check site and enter applicaiton Oracle Receivables
           Query the profile Sequential Numbering
           This should be set to Partially Used or Always Used depending on
           your setup.
           Next query the profile AR: Document Number Generation
           You may set this to When the transaction in committed or
           When the transaction is completed.
           This option determines when the document sequence number is generated
.
5. Create a Transaction using the Batch Source and Transaction Type that
you created and assigned to the Document Sequence.
Depending on the option you set for the AR: Document Number Generation
you will see the following either upon commit or completion:
Example of initial invoice:
Customer_trx_id trx_number Doc_sequence_value Old_trx_number
           ------------- --------- ---------------- -----------
                    16622 20014
.
Example following the document number generation:
           Customer_trx_id trx_number Doc_sequence_value Old_trx_number
           ------------- --------- ---------------- -----------
           16622 5 5 20014
           The new column old_trx_number was added to the ra_customer_trx table
           for this feature.
.
Solution Explanation:
           Follow the following steps:
1.Define you Transaction Type and Batch Source.
2.Define Document Sequencing.
3.Set the profile options Sequential Numbering and
  AR: Document Number Generation.
4.Create a transaction.