Intermittent Error "SQL Error: ORA-00001: unique constraint (PS_IN_DP_DM2_TAO4) violated'" when Running the Deplete On Hand Qty' Process (IN_FUL_DPL) (Doc ID 1939113.1)

Last updated on AUGUST 04, 2016

Applies to:

PeopleSoft Enterprise SCM Inventory - Version 9 to 9 [Release 9]
Information in this document applies to any platform.

Symptoms

The 'Deplete On Hand Qty' process (IN_FUL_DPL) fails intermittently with the following error:

SQL error. Stmt #: 1623 Error Position: 0 Return: 805 - [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.PS_IN_DP_DM2_TAO4' with unique index 'PS_IN_DP_DM2_TAO4'.
Failed SQL stmt: INSERT INTO PS_IN_DP_DM2_TAO4 (PROCESS_INSTANCE , BUSINESS_UNIT , RECEIVER_ID , DESTIN_BU) SELECT DISTINCT T1.PROCESS_INSTANCE , T1.BUSINESS_UNIT , T1.INTERUNIT_ID , T1.DESTIN_BU FROM PS_IN_DP_DMD_TAO4 T1 WHERE T1.PROCESS_INSTANCE=1956447 AND T1.PROCESS_STATUS='R' AND NOT EXISTS ( SELECT 'X' FROM PS_IN_DP_DM2_TAO4 B WHERE B.BUSINESS_UNIT =T1.BUSINESS_UNIT AND B.PROCESS_INSTANCE= 1956447 AND B.PROCESS_INSTANCE=T1.PROCESS_INSTANCE AND B.RECEIVER_ID =T1.INTERUNIT_ID AND B.DESTIN_BU=T1.DESTIN_BU )

Process 1956447 ABENDED at Step IN_DEPLETE.IUTPutJb.Step010 (SQL) -- RC = 805 (108,524)

 

 -- 21.46.04 Process 7809 ABENDED at Step IN_DEPLETE.IUTPutJb.Step010 (Action SQL) -- RC = 805
ROLLBACK
/
-- 21.46.04 SQL Error: ORA-00001: unique constraint (EMDBO.PS_IN_DP_DM2_TAO4)
violated

 
The issue occurs when running the IN_FUL_DPL against multiple InterUnit Transfers which different destination Business Units and which have been released using the Auto-Processing option called 'Advance to Shipped'.

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