ETL9.2: Time Admin Failing With Unique Constraint Error at Step TL_TA000200.DD000.Step025 After Applying Bug Fix 30985551
(Doc ID 2689663.1)
Last updated on NOVEMBER 18, 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.
Customer has applied fix for Bug 30852482 in their environment. However, after also applying fix for Bug 30985551 the following unique constraint error is generated:
PeopleTools 8.57.12 - Application Engine Server
Copyright (c) 1988-2020 Oracle and/or its affiliates.
All Rights Reserved
PSAESRV started service request at 12.50.23 2020-07-09
2020-07-09-12.51.32.000000: Starting TL_TIMEADMIN Core Time Administration App. (13507,13)
2020-07-09-12.51.34.000000: Starting TL_TA000200 Determine TRs To Process. (13507,13)
File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1725 Error Position: 0 Return: 805 -
ORA-00001: unique constraint (HC.PS_TL_TA_TRLIST4) violated
Failed SQL stmt: INSERT INTO PS_TL_TA_TRLIST4 (PROCESS_INSTANCE, EMPLID, EMPL_RCD,
START_DT, END_DT, EARLIEST_CHGDT, RUN_CNTL_ID, EARLY_START_DT, WORKGROUP,
IN_BATCH) SELECT DISTINCT 40033, ST.EMPLID, ST.EMPL_RCD, TO_DATE('1900-01-01','YYYY-
MM-DD'), TO_DATE('1900-01-01','YYYY-MM-DD'), ST.EARLIEST_CHGDT, A.RUN_CNTL_ID,
TO_DATE('1900-01-01','YYYY-MM-DD'), E.WORKGROUP, 'Y' FROM PS_TL_TA_TRRSLV4 A,
PS_TL_TR_STATUS ST, PS_TL_EMPL_DATA E WHERE A.PROCESS_INSTANCE = 40033 AND
A.EMPLID = E.EMPLID AND E.EMPL_RCD = ST.EMPL_RCD AND E.EMPLID = ST.EMPLID AND
(E.EMPL_RCD = A.EMPL_RCD OR 'Y' = 'Y') AND ST.TA_STATUS IN ('X', 'Y') AND
ST.EARLIEST_CHGDT <= TO_DATE('2020-07-31','YYYY-MM-DD') AND EXISTS ( SELECT 'X' FROM
PS_TL_EMPL_DATA E1 WHERE E1.EMPLID = E.EMPLID AND E1.EMPL_RCD = E.EMPL_RCD AND
E1.EFFDT > ST.EARLIEST_CHGDT AND E.TIME_RPTG_STATUS = 'A') AND NOT EXISTS ( SELECT
'X' FROM PS_TL_TA_TRLIST4 X WHERE X.PROCESS_INSTANCE = A.PROCESS_INSTANCE AND
X.EMPLID = E.EMPLID AND X.EMPL_RCD = E.EMPL_RCD)
Process 40033 ABENDED at Step TL_TA000200.DD000.Step025 (SQL) -- RC = 805 (108,524)
Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s
PSAESRV completed service request at 12.51.35 2020-07-09
Steps to Recreate:
1. Select delivered demo multiple job employee.
2. Terminate Employee empl_rcd 0 on June 20th along with the Time Reporter Data inactivated for same effective date.
3. Rehire employee on July 8th (mid period) and the time reporter data was also activated with the same effective date and put into a different workgroup.
4. Check Update TA Status - change to Employee rehire date and ta_status if necessary.
5. Enter time for one day in the current period then Submit
6. Run Time Admin through end of current period.
7. Time Admin run to No Success due to unique constraint abend.
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