Error :The table is missing the index JAI_CMN_RG_I_TRXS_UK1 or index JAI_CMN_RG_I_TRXS_UK1 exists on another table (Doc ID 2243618.1)

Last updated on MARCH 14, 2017

Applies to:

Oracle Financials for India - Version 12.1.1 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.

Symptoms

On : 12.1.1 version, Excise - Order to Cash Cycle

Observed that when applying Patch 25406230:R12.JAI.B getting failed, it is getting failed. 

ERROR
-----------------------
*******************************************************
The table is missing the index JAI_CMN_RG_I_TRXS_UK1
or index JAI_CMN_RG_I_TRXS_UK1 exists on another table.
Create it with the statement:

Warning: Object "JAI_CMN_RG_I_TRXS" is not registered in table "FND_OBJECT_TABLESPACES".


Start time for statement below is: Mon Feb 27 2017 10:15:45

CREATE UNIQUE INDEX JA.JAI_CMN_RG_I_TRXS_UK1 ON JA.JAI_CMN_RG_I_TRXS
(ORGANIZATION_ID, LOCATION_ID, FIN_YEAR, INVENTORY_ITEM_ID, SLNO) LOGGING
STORAGE (FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE 60 INITRANS 11 MAXTRANS
255 COMPUTE STATISTICS TABLESPACE APPS_TS_TX_IDX

Statement executed.

AD Worker error:
The index cannot be created as the table has duplicate keys.


Use the following SQL statement to identify the duplicate keys:

SELECT ORGANIZATION_ID, LOCATION_ID, FIN_YEAR, INVENTORY_ITEM_ID, SLNO, count(*)
FROM JA.JAI_CMN_RG_I_TRXS
GROUP BY ORGANIZATION_ID, LOCATION_ID, FIN_YEAR, INVENTORY_ITEM_ID, SLNO
HAVING count(*)>1

AD Worker error:
Unable to compare or correct tables or indexes or keys
because of the error above
 




STEPS
-----------------------
The issue can be reproduced at will with the following steps:
When trying to apply patch , it is failing with error.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot proceed with patch application.

Cause

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