SQL PO_ER_EQUE_INS in App Engine PO_EROUT_AE Fails with a Unique Constraint Error (Doc ID 1609732.1)

Last updated on JULY 19, 2016

Applies to:

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

Symptoms

In  PO Dispatch as well as PV Dispatch

SQL PO_ER_EQUE_INS in app engine PO_EROUT_AE fails on unique constraint error  . The SQL PO_ER_EQUE_INS is failing on a unique constraint error in the step PO_EROUT_AE.CheckQue.Step02. This insert can be run more than once in the program (as evidenced by the comment /* Put any additional records back to the early queue table */)

In the NOT EXISTS subselect, there is a join on the COMMENT_ID field. The reason we get a unique constraint error is because the COMMENT_ID fields could both be NULL. On Oracle databases, NULL does NOT equal NULL in a SQL statement like this so this not exist fails - which means that the insert is attempted for data that already exists.


Error: 

 Error Position:  0
 Return:  805 - ORA-00001: unique constraint (SYSADM.PS_PO_ER_E_MSGQUE) violated
 Statement:  INSERT INTO PS_PO_ER_E_MSGQUE(BUSINESS_UNIT ,PO_ID ,LINE_NBR ,SCHED_NBR ,DISTRIB_LINE_NUM ,COMMENT_ID ,RANDOM_CMMT_NBR ,AUDIT_ACTN ,PROCESS_INSTANCE ,DTTM_STAMP ) SELECT SQUE.BUSINESS_UNIT ,SQUE.PO_ID ,SQUE.LINE_NBR ,SQUE.SCHED_NBR ,SQUE.DISTRIB_LINE_NUM ,SQUE.COMMENT_ID ,SQUE.RANDOM_CMMT_NBR ,SQUE.AUDIT_ACTN ,0 ,SQUE.DTTM_STAMP FROM PS_PO_ER_MSGQUE SQUE ,PS_PO_ER_MSG_Q_TMP STMP WHERE SQUE.PROCESS_INSTANCE = :1 AND SQUE.PROCESS_INSTANCE = STMP.PROCESS_INSTANCE AND SQUE.BUSINESS_UNIT = STMP.BUSINESS_UNIT AND SQUE.PO_ID = STMP.PO_ID AND NOT EXISTS ( SELECT 'X' FROM PS_PO_ER_E_MSGQUE EQUE WHERE EQUE.BUSINESS_UNIT = SQUE.BUSINESS_UNIT AND EQUE.PO_ID = SQUE.PO_ID AND EQUE.LINE_NBR = SQUE.LINE_NBR AND EQUE.SCHED_NBR = SQUE.SCHED_NBR AND EQUE.DISTRIB_LINE_NUM = SQUE.DISTRIB_LINE_NUM AND EQUE.COMMENT_ID = SQUE.COMMENT_ID AND EQUE.RANDOM_CMMT_NBR = SQUE.RANDOM_CMMT_NBR)




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