Error 'INSERT or UPDATE would result in duplicate rows in a table with a UNIQUE index' When Running Identify Phase for Calculate Absence and Payroll Process (Doc ID 2578833.1)

Last updated on APRIL 26, 2023

Applies to:

Symptoms

When running GPPDPRUN for a Calendar for the Identify phase, the process is running to No Success.
After enabling level 159 trace for this process, the following error is observed:

15:58:57.991      15259     0.000     0.000   #1     RC=0     GETSTMT Stmt=GPPIDNT5_I_OLDWR13, length=1759
15:58:57.997      15260     0.000     0.000   #1     RC=0     COM Stmt=INSERT INTO PS_GP_OLD_RTO_WRK (CAL_RUN_ID ,EMPLID ,EMPL_RCD ,GP_PAYGROUP ,CAL_ID ,ORIG_CAL_RUN_ID ,RSLT_VER_NUM ,RSLT_REV_NUM ,PRC_ORD_TS ,SEL_STAT ,SEL_RSN ,RTO_PRC_ID ,SEL_ACTION ,PYMT_DT ,PRC_BGN_DT ,PRC_END_DT ,CALC_FROM_DT ,CALC_THRU_DT ,GP_TX_TYPE ) SELECT H.CAL_RUN_ID ,B.EMPLID ,B.EMPL_RCD ,B.GP_PAYGROUP ,B.CAL_ID ,B.ORIG_CAL_RUN_ID ,B.RSLT_VER_NUM ,B.RSLT_REV_NUM ,B.PRC_ORD_TS ,B.SEL_STAT ,B.SEL_RSN ,H.RTO_PRC_ID ,B.SEL_ACTION ,B.PYMT_DT ,B.PRC_BGN_DT ,B.PRC_END_DT ,B.CALC_FROM_DT ,B.CALC_THRU_DT ,B.GP_TX_TYPE FROM PS_GP_CAL_RUN_DTL  A ,PS_GP_PYE_PRC_STAT B ,PS_GP_RTO_PRC_WRK  H WHERE H.CAL_RUN_ID = :1 AND H.COUNTRY=A.COUNTRY AND A.GP_PAYGROUP = B.GP_PAYGROUP AND A.CAL_ID      = B.CAL_ID AND A.CAL_FINAL_TS IS NOT NULL AND A.CAL_FINAL_TS >= H.CAL_FINAL_TS AND B.EMPLID = H.EMPLID AND B.SEL_STAT IN ('A', 'I') AND B.SEL_RSN NOT IN ('0A','0B') AND B.GP_TX_TYPE=' ' AND B.RSLT_VER_NUM = (SELECT MAX(M.RSLT_VER_NUM) FROM PS_GP_PYE_PRC_STAT M WHERE M.EMPLID = B.EMPLID AND M.EMPL_RCD = B.EMPL_RCD AND M.CAL_ID = B.CAL_ID AND M.GP_PAYGROUP = B.GP_PAYGROUP AND M.SEL_STAT IN ('A', 'I') AND M.PRC_ORD_TS = (SELECT MAX(Q.PRC_ORD_TS) FROM PS_GP_PYE_PRC_STAT Q WHERE Q.EMPLID=M.EMPLID AND Q.EMPL_RCD=M.EMPL_RCD AND Q.CAL_ID=M.CAL_ID AND Q.SEL_STAT IN ('A', 'I') AND Q.GP_PAYGROUP=M.GP_PAYGROUP)) AND B.RSLT_REV_NUM = (SELECT MAX(N.RSLT_REV_NUM) FROM PS_GP_PYE_PRC_STAT N WHERE N.EMPLID = B.EMPLID AND N.EMPL_RCD = B.EMPL_RCD AND N.CAL_ID = B.CAL_ID AND N.GP_PAYGROUP = B.GP_PAYGROUP AND N.RSLT_VER_NUM = B.RSLT_VER_NUM AND N.SEL_STAT IN ('A', 'I') AND N.PRC_ORD_TS = (SELECT MAX(Q.PRC_ORD_TS) FROM PS_GP_PYE_PRC_STAT Q WHERE Q.EMPLID=N.EMPLID AND Q.EMPL_RCD=N.EMPL_RCD AND Q.CAL_ID=N.CAL_ID AND Q.SEL_STAT IN ('A', 'I') AND Q.GP_PAYGROUP=N.GP_PAYGROUP))
15:58:58.001      15261     0.000     0.000   #1     RC=0     Bind-1, type=SQLPBUF, length=11, value=<CAL_RUN_ID>
15:58:59.220      15262     1.219     1.219   #1     RC=805   EXE
15:58:59.243      15263     0.015     0.000   #0     RC=805   ERR rtncd=805 msg=INSERT or UPDATE would result in duplicate rows in a table with a UNIQUE index
15:58:59.264      15264     0.032     0.000   #14    RC=0     EPO error pos=0
15:58:59.271      15265     0.000     0.000   #14    RC=0     Rollback


The issue can be reproduced at will with the following steps:
1. Navigation: Global Payroll & Absence Mgmt > Absence and Payroll Processing > Calculate Absence and Payroll
2. Run process for Calendar Group ID with the Identify processing phase selected
3. Go to the Process Monitor and note GPPDPRUN runs to 'No Success'

Changes

Cause

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