My Oracle Support Banner

MSCPDCW Planning ODS Load fails with ORA-01452 in LOAD_ITEM - Also Shows ORA-12801 (Doc ID 960370.1)

Last updated on JULY 16, 2024

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.1.1 and later
Information in this document applies to any platform.

Symptoms

Using Advanced Supply Chain Planning / Standard Data Collection On 12.1.1,  when attempting to perform Planning ODS Load, the following error occurs.

Unique index violated - SYSTEM_ITEMS_U1_xxx
ORA-12801: error signaled in parallel query server P000
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found


Investigation of other duplicate key errors on OPERATION_RESOURCES, OP_RESOURCE_SEQ, and SUPPLY deferred until ITEM issue is resolved since item duplication may be the cause of the others.

-- Steps To Reproduce:
1. (R) Advanced Supply Chain Planner
2. (N) Collections > Oracle Systems > Standard Collection
3. Inspect the Planning ODS Load Worker log handling LOAD_ITEM and observe messages of the form:

Unique index violated - SYSTEM_ITEMS_U1_PRD
ORA-12801: error signaled in parallel query server P000
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Usually also see
Unique index violated - ITEM_CATEGORIES_U1_PRD
ORA-12801: error signaled in parallel query server P000
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

ANOTHER EXAMPLE FROM DIFFERENT SR:

22-OCT 20:44:50 : ERROR
22-OCT 20:44:50 : Unique index violated - ITEM_CATEGORIES_U1_AMC
22-OCT 20:44:50 : ORA-12801: error signaled in parallel query server P004
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
22-OCT 20:44:50 : ======= Below records violate the unique constraint =======
22-OCT 20:44:50 : ORGANIZATION_ID 101 / SR_INSTANCE_ID 22 / INVENTORY_ITEM_ID 54005 / CATEGORY_SET_ID 7 / SR_CATEGORY_ID 2296
22-OCT 20:44:50 : ===========================================================

Error:   Planning ODS Load Worker - r18048028
22-OCT 20:44:57 : ERROR
22-OCT 20:44:57 : Unique index violated - SYSTEM_ITEMS_U1_AMC
22-OCT 20:44:57 : ORA-12801: error signaled in parallel query server P005
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
22-OCT 20:44:57 : ======= Below records violate the unique constraint =======
22-OCT 20:44:57 : PLAN_ID -1 / SR_INSTANCE_ID 22 / ORGANIZATION_ID 101 / INVENTORY_ITEM_ID 54005 / NEW_PLAN_ID  / SIMULATION_SET_ID
22-OCT 20:44:57 : ===========================================================

  

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

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