ETL9.2: TL_TIMEADMIN Abends with 'ORA-00001: unique constraint (SYSADM.PS_TL_ELP_TMP7) violated' Error (Doc ID 2451454.1)

Last updated on OCTOBER 04, 2022

Applies to:

Symptoms

On : 9.2 version, PT 8.55.25 PUM 27

After applying PUM 27, TL_TIMEADMIN is abending with the following message:


ERROR
-----------------------
File: e:\pt85525b-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1723 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_TL_ELP_TMP7) violated
Failed SQL stmt: INSERT INTO PS_TL_ELP_TMP7 (PROCESS_INSTANCE, EMPLID, EMPL_RCD, DUR, SEQ_NBR, TRC, TL_QUANTITY, COMP_TIME_PLAN, EFFDT_USED, CALC_EXP_DT, ACT_EXP_DT, CT_BALANCE) SELECT DISTINCT A.PROCESS_INSTANCE, A.EMPLID, A.EMPL_RCD, A.DUR, A.SEQ_NBR, A.TRC, A.TL_QUANTITY, ' ' , TO_DATE('1900-01-01','YYYY-MM-DD') , TO_DATE('1900-01-01','YYYY-MM-DD') , TO_DATE('1900-01-01','YYYY-MM-DD') , 0 FROM PS_TL_ELP_WRB7 A , PS_TL_EMPL_COMP B WHERE A.PROCESS_INSTANCE=:1 AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND B.EFF_STATUS = 'A' AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_TL_EMPL_COMP B1 WHERE B1.EMPLID = B.EMPLID AND B1.EMPL_RCD = B.EMPL_RCD AND B1.EFFDT <= A.DUR AND B1.COMP_TIME_PLAN = B.COMP_TIME_PLAN) AND NOT EXISTS ( SELECT '1' FROM PS_TL_COMP_TRC C WHERE B.COMP_TIME_PLAN = C.COMP_TIME_PLAN AND C.TRC = A.TRC AND C.EFF_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_TL_COMP_TRC C1 WHERE C1.COMP_TIME_PLAN = C.COMP_TIME_PLAN AND C1.EFFDT <= A.DUR) ) AND NOT EXISTS ( SELECT 'X' FROM PS_TL_EXCEPT_WRK7 B ,PS_TL_EXCEPT_DEFN K WHERE B.PROCESS_INSTANCE=:2 AND B.EMPLID=A.EMPLID AND B.EMPL_RCD=A.EMPL_RCD AND B.DUR=A.DUR AND B.SEQ_NBR = A.SEQ_NBR AND B.EXCEPTION_ID = K.EXCEPTION_ID AND K.SEVERITY ='H' AND K.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_TL_EXCEPT_DEFN D1 WHERE D1.EXCEPTION_ID = B.EXCEPTION_ID AND D1.EFFDT <= A.DUR ) AND NOT EXISTS ( SELECT 1 FROM PS_TL_EXCEPTION C WHERE C.EMPLID=A.EMPLID AND C.EMPL_RCD=A.EMPL_RCD AND C.DUR=A.DUR AND C.EXCEPTION_ID = B.EXCEPTION_ID AND C.EXCEPTION_STATUS='A'))

Process 651518 ABENDED at Step TL_TA000900.DG000.Step021 (SQL) -- RC = 805 (108,524)

Steps To Replicate
-----------------------

1-Enroll the employee into two different comp plans.
   These two comp plans should not share any TRCs
2-Report 1 hour for one of the COMP TRCs
3-Run Time Admin

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.