From past many years we have been getting duplicate transaction numbers in Oracle AR. Because of this the new invoices get stuck in the interface table(ra_interface_lines_all) with the error Duplicate Invoice Number.
To address this we are changing the old invoice numbers present in the base tables(ar_payment_schedules_all,ra_customer_trx_all and ra_customer_trx_lines_all) with -1 as suffix ,so that the newer transactions will flow into oracle.But we need to find a permanent solution to this problem.
Any suggestions will be welcomed.
in receivables module the transaction number sequence is controlled by the configuration "Transaction Source" .... typically any organization would have two sources one for manual transaction entered in the receivables module directly ... another for transactions imported from Order management or any third party systems ...
When there are two different sources, it is often said to have two different sequence numbers (which cannot overlay each other) ... probably a larger sequence ...say For Manual Invoices 10000001 and for Imported Invoices 5000001 .. in which case chances of duplication would be NIL ....
Also check the Transaction source configuration, since there is an option available to accept Duplicate Invoice numbers .. make sure that checkbox is unchecked as well .....
Also it is recommended by oracle not to make updates to base table / transaction data .. it is a much simpler approach to assign unique transactions numbers for new ones getting imported ...
The key areas to check is the Transaction source configuration ... navigation in receivables module is Setup > Transactions > Sources
We are getting all the AR details from the third party only and the set up is like we have the trx number like
2 digit till no 4 digit invoice-DDMM date format .Most of the duplicate transactions are from a single source only.
See my assumption is you are passing Transcation number into interface tables. The way you are passing is 01,02...99 + DDMM(example January 4th is 0401).
The same number can be repeated after a year, eventhough the dailt transcations limit is less than 100.
So you need to tell the business to change the number generation code. That should be sequence number+DDMMYY(This condition works only if the daily generated transcation total is below 100).
Hope you understand.
Yes I have checked that that Allo Duplicate transaction numbers checkbox is disabled itself .
And daily we receive thousands of transactions so that is not feasible sivakumar.Moreover we are using the complete length of trx_number as provided by Oracle i.e complete 20 characters we are using.