MSCPDCW - Planning ODS Load Is Erroring ORA-01452: cannot CREATE UNIQUE INDEX OR ORA-00001 on ITEM_CATEGORIES_U1_xxx (Doc ID 333758.1)

Last updated on JANUARY 09, 2023

Applies to:

Symptoms

When running the ODS Load workers, it fails with:

OPTION #1 - LATEST CODE WITH MORE DIRECT ERROR MESSAGE

07-FEB 17:16:43 : Procedure MSC_CL_ITEM_ODS_LOAD.LOAD_CATEGORY started.
07-FEB 17:16:43 : Procedure alter_temp_table started.
07-FEB 17:16:43 : MSC_CL_EXCHANGE_PARTTBL.Initialize successful
07-FEB 17:16:44 : ERROR
07-FEB 17:16:44 : Unique index violated - ITEM_CATEGORIES_U1_PRD
07-FEB 17:16:44 : ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
07-FEB 17:16:44 : ======= Below records violate the unique constraint =======
07-FEB 17:16:44 : ORGANIZATION_ID 85 / SR_INSTANCE_ID 1 / INVENTORY_ITEM_ID 19001 / CATEGORY_SET_ID 9 / SR_CATEGORY_ID 1123
07-FEB 17:16:44 : ORGANIZATION_ID 85 / SR_INSTANCE_ID 1 / INVENTORY_ITEM_ID 19001 / CATEGORY_SET_ID 8 / SR_CATEGORY_ID 2127
07-FEB 17:16:44 : ===========================================================
07-FEB 17:16:44 : <>
07-FEB 17:16:44 : <> CREATE UNIQUE INDEX ITEM_CATEGORIES_U1_PRD ON ITEM_CATEGORIES_PRD(ORGANIZATION_ID,SR_INSTANCE_ID,INVENTORY_ITEM_ID,CATEGORY_SET_ID,SR_CATEGORY_ID) PARALLEL TABLESPACE APPS_TS_TX_IDX PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
07-FEB 17:16:44 : ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
07-FEB 17:16:44 : UNIQUE index creation on temporary table ITEM_CATEGORIES_PRD has completed with ERROR.
07-FEB 17:16:44 : User-Defined Exception
07-FEB 17:16:44 : User-Defined Exception
07-FEB 17:16:44 : User-Defined Exception
07-FEB 17:16:44 : Sending message :-9999999 to monitor.
07-FEB 17:16:44 : Error_Stack...
07-FEB 17:16:44 : ORA-06510: PL/SQL: unhandled user-defined exception
07-FEB 17:16:44 : Error_Backtrace...
07-FEB 17:16:44 : ORA-06512: at "APPS.MSC_CL_COLLECTION", line 2022
ORA-06512: at "APPS.MSC_CL_COLLECTION", line 3237

OPTION #2 - OLDER CODE

MSCPDCW module: Planning ODS Load Worker

ORA-00001: unique constraint (MSC.ITEM_CATEGORIES_U1_TBV) violated
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
Purge Batch Size is set to : 2000000.
Procedure LOAD_CATEGORY started.

Error in PROCEDURE=LOAD_CATEGORY, TABLE=MSC_ITEM_CATEGORIES
ORA-00001: unique constraint (MSC.ITEM_CATEGORIES_U1_TBV) violated
ORA-00001: unique constraint (MSC.ITEM_CATEGORIES_U1_TBV) violated
ORA-00001: unique constraint (MSC.ITEM_CATEGORIES_U1_TBV) violated
Sending message :-9999999 to monitor.

MSCPDCW module: Planning ODS Load Worker
Procedure LOAD_ITEM started.

Error in PROCEDURE=LOAD_ITEM, TABLE=MSC_SYSTEM_ITEMS
ORA-00001: unique constraint (MSC.SYSTEM_ITEMS_U1_TBV) violated
ORA-00001: unique constraint (MSC.SYSTEM_ITEMS_U1_TBV) violated
ORA-00001: unique constraint (MSC.SYSTEM_ITEMS_U1_TBV) violated
Sending message :-9999999 to monitor.

MSCPDCW module: Planning ODS Load Worker
There is an Unknown Error in the Worker.

The navigation path is:
Order Management Super User > Scheduling > ATP Data Collections
OR
ASCP / Collections /Oracle Systems / Standard Collections

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.