Transaction Fails to Persist to Database Due to 'ORA-00001: Unique Constraint Violated' Error and Is Sent to QueueException Folder
Last updated on OCTOBER 05, 2017
Applies to:Oracle Retail Point-of-Service - Version 13.4 to 14.1 [Release 13.4 to 14.1]
Information in this document applies to any platform.
In Oracle Retail Point of Service (ORPOS), transactions are missing in the store database due to reuse of a transaction sequence number. This duplication causes a 'unique constraint violated' error when saving the transaction.
Steps to Reproduce:
Prerequisite: Store/Register/Till open and close are done in Back Office.
1. Close till for current business day.
2. Shut down the Register instance.
3. Reconcile till from Back Office.
4. Close Register/Store from Back Office.
5. Open Store/Register/Till for next business day from Back Office.
6. Launch Register.
7. At Lookupregister site, shut down Store Server instance.
8. Note that the sequence number is reused, leading to the ORA-00001 error and the missing transaction.
Another way to reproduce this issue is as follows :
1. Open Store from Back Office.
2. Open a register (i.e., 129) in Back Office.
3. Open Till 001 for the register and Operator 'POS' from BO.
4. Select F3 POS.
5. Enter login details of user 'user1'.
6. Select Yes at the prompt to insert Till 001.
7. Complete a sale transaction.
8. Close Till 001 from POS.
9. Log in as Operator 'user2' from POS.
10. Open Till 002.
11. Complete a sale transaction.
12. Enter another item in sale screen but do not tender.
13. Log in to Back office as 'POS' user.
14. Reconcile Till 001. Note that a new transaction with TY_TRN=11 is inserted (while 11 is for till close).
15. In POS, add item to sale screen while logged in as 'user2' and complete the transaction. The receipt is printed and transaction is NOT saved to database.JL_ENR for the transaction created in step 14 has the content of the missing transaction.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms