Case 00053952 – AR Customer Transaction Lines Sequence Out of Sync

AR Customer Transaction Lines Sequence Out of Sync

Case Number	00053952
Oracle Module	Accounts Receivable (AR)
EBS Version	R12
Functional Area	Invoice/Transaction Processing / Database Sequence
Related Patches	None

Symptoms

AR transaction or invoice creation fails with a unique constraint violation or duplicate key error.
The Oracle sequence RA_CUSTOMER_TRX_LINES_S has a NEXTVAL that is lower than the current maximum CUSTOMER_TRX_LINE_ID in RA_CUSTOMER_TRX_LINES_ALL.
New transaction lines cannot be inserted because the sequence generates values that already exist in the table (likely from a database restore or data import that did not reset sequences).

Root Cause

The Oracle sequence RA_CUSTOMER_TRX_LINES_S has drifted out of sync with the data in RA_CUSTOMER_TRX_LINES_ALL. This typically occurs after a database refresh, clone, import, or data migration where existing rows were inserted with IDs higher than the sequence's current position. When Oracle attempts to use the sequence to generate a new primary key, it produces a value that already exists, causing unique constraint violations.

Resolution

This fix must be performed by a DBA. It is recommended to test in a non-production clone before applying to production, as sequence values may differ between environments.

Step 1 – Record the current sequence next value

Execute the following and note the returned value:

SELECT ra_customer_trx_lines_s.NEXTVAL FROM DUAL;

Step 2 – Record the current maximum table ID

Execute the following and note the returned value:

SELECT MAX(customer_trx_line_id) FROM ra_customer_trx_lines_all;

Step 3 – Calculate the required increment

The sequence NEXTVAL (Step 1) must be greater than the maximum ID (Step 2). Calculate the difference and add a buffer. For example, if NEXTVAL = 20,000 and MAX ID = 16,300, use an increment of at least 4,000.

Step 4 – Temporarily increase the sequence increment

Alter the sequence to increment by the calculated value:

ALTER SEQUENCE ra_customer_trx_lines_s INCREMENT BY <increment_value>;

Step 5 – Advance the sequence

Run NEXTVAL exactly once to advance the sequence by the large increment:

SELECT ra_customer_trx_lines_s.NEXTVAL FROM DUAL;

Verify the returned value is now greater than the maximum table ID from Step 2.

Step 6 – Reset the sequence increment to 1

ALTER SEQUENCE ra_customer_trx_lines_s INCREMENT BY 1;

Step 7 – Validate

Confirm the sequence is properly set:

SELECT * FROM DBA_SEQUENCES WHERE sequence_name = 'RA_CUSTOMER_TRX_LINES_S';

Retry the failing AR transaction creation to confirm the issue is resolved.

Note: Sequence values in production may differ from test/clone environments. Always re-check current NEXTVAL and MAX ID values in the target environment before applying this fix. The sequence must never be set lower than the current maximum table ID or the problem will recur.

Script Inventory

File	Description
data_fix.txt	Step-by-step DBA instructions to resync the RA_CUSTOMER_TRX_LINES_S sequence with the maximum customer_trx_line_id in RA_CUSTOMER_TRX_LINES_ALL. Includes statements to check current values, alter sequence increment, advance the sequence, and reset increment to 1.

Key Objects Affected

Object	Action
RA_CUSTOMER_TRX_LINES_S (sequence)	Increment temporarily; advance NEXTVAL; reset increment to 1
RA_CUSTOMER_TRX_LINES_ALL (reference)	Read only — used to determine MAX(customer_trx_line_id)