My Oracle Support Banner

ETL9.2: TA Fails When Processing Employees with More Than One Comp Plan at Step TL_TA000900.DG000.Step021 (Doc ID 2451579.1)

Last updated on JULY 22, 2020

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Time Administration

TimeAdmin process has been failing when there are employees associated with more than one comp plan. 

ERROR
-----------------------
805 ORA-00001:unique contraintINSERT INTO PS_TL_ELP_TMP1 (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, ' ' , '1900-01-01' , '1900-01-01' , '1900-01-01' , 0 FROM PS_TL_ELP_WRB4 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_WRK4 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 2046464 ABENDED at Step TL_TA000900.DG000.Step021 (SQL) -- RC = 805 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s





STEPS
-----------------------
The issue can be reproduced at will with the following steps:


1. Run Time Admin for an employee with more than one comp plans.
2. Time admin abends with above error


Changes

 

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
Changes
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.