Planning ODS Load (MSCPDC) Fails With ORA-01452 On SYSTEM_ITEMS_U1_XXX
(Doc ID 2916152.1)
Last updated on JANUARY 30, 2023
Applies to:
Oracle Advanced Supply Chain Planning - Version 12.1.3 and laterInformation in this document applies to any platform.
Symptoms
When attempting to run the Data Collections, the following error occurs in the Planning ODS Load/Worker (MSCPDC/MSCPDCW)...
ERROR
-----------------------
17-NOV 06:04:56 : ERROR
17-NOV 06:04:56 : Unique index violated - "SYSTEM_ITEMS_U1_PRD"
17-NOV 06:04:56 : ORA-12801: error signaled in parallel query server P008
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
17-NOV 06:04:56 : ======= Below records violate the unique constraint =======
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 10089 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 10563 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 10673 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 10579 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 5482 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 32641 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 8658 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 9293 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 32211 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 32308 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 32410 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 1130224 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 1147235 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 1147229 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 1147230 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 1147233 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 1147234 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 1145224 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 1145227 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 19785 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 4959 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 5245 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 11543 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : PLAN_ID -1 / SR_INSTANCE_ID 1 / ORGANIZATION_ID 108 / INVENTORY_ITEM_ID 18721 / NEW_PLAN_ID / SIMULATION_SET_ID
17-NOV 06:04:56 : ===========================================================
17-NOV 06:04:56 : <
17-NOV 06:04:56 : <> CREATE UNIQUE INDEX "SYSTEM_ITEMS_U1_PRD" ON SYSTEM_ITEMS_PRD(PLAN_ID,SR_INSTANCE_ID,ORGANIZATION_ID,INVENTORY_ITEM_ID,NEW_PLAN_ID,SIMULATION_SET_ID) PARALLEL TABLESPACE APPS_TS_TX_IDX STORAGE( INITIAL 8388608) PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
17-NOV 06:04:56 : ORA-12801: error signaled in parallel query server P008
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
17-NOV 06:04:56 : UNIQUE index creation on temporary table SYSTEM_ITEMS_PRD has completed with ERROR.
17-NOV 06:04:56 : User-Defined Exception
17-NOV 06:04:56 : User-Defined Exception
17-NOV 06:04:56 : User-Defined Exception
17-NOV 06:04:56 : Sending message :
-9999999 to monitor.
17-NOV 06:04:56 : Error_Stack...
17-NOV 06:04:56 : ORA-06510: PL/SQL: unhandled user-defined exception
17-NOV 06:04:56 : Error_Backtrace...
17-NOV 06:04:56 : ORA-06512: at "APPS.MSC_CL_COLLECTION", line 2410
ORA-06512: at "APPS.MSC_CL_COLLECTION", line 3925
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Responsibility: Advanced Supply Chain Planner
2. Navigation: Collections > Oracle Systems > Standard Collections
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |