Error: 'ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found in EBS R12

 

On : 12.2.10 version, Patch Application Issues

Upgrade from R12.1.3 to R12.2.10 - Patch 30399999 Error: ORA-01452

 

When attempting to apply patch 30399999, the following error occurs:

ERROR
-----------------------
SQL: create UNIQUE index "GME"."GME~RESOURCE~TXNS~PK" on "GME"."GME_RESOURCE_TXNS" (POC_TRANS_ID#1) tablespace APPS_TS_TX_IDX storage (initial 128K next 128K) online

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found


STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1. Apply Patch 30399999.
2. Review AD Worker Log.

The issue is caused by the following:

Per Bug: 34130503, issue relates to code issue after upgrading from R12.1.3 to R12.2.10. Additionally, the below query returns count for duplicate records (Count > 0):

Select count(1) from "GME"."GME_RESOURCE_TXNS" where POC_TRANS_ID#1=-1;

o implement the solution, please execute the following steps:

1. Download and review the readme and pre-requisites for the following patches. Apply them in sequence:

a. Patch 22962971:R12.GME.C
b. Patch 31564811:R12.GME.C

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:

   GMEVRLBB.pls 120.39.12020000.18
   GMEVRLSB.pls 120.15.12020000.10

   GMERTXN.odf 120.2.12020000.8

You can generate a Diagnostic Apps Check for the Process Manufacturing Process Execution module; see Note: 838707.1 - Diagnostics Apps Check.

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.