ETL9.2: TL_TIMEADMIN Abending At Step TL_ABS_RSLV.GA100.Step03 when Person has a Job Change. (Doc ID 2777591.1)

Last updated on JUNE 20, 2024

Applies to:

Symptoms

When attempting to run TimeAdmin,
the following error occurs.

ERROR
-----------------------

File: e:\pt858p09c_2101131159-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1738 Error Position: 0 Return: 805 - [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot insert duplicate key row in object 'dbo.PS_TL_ABS_WRK4' with unique index 'PS_TL_ABS_WRK4'. The duplicate key value is (235957, 15086, 0, Mar 15 2021 12:00AM, 180484).
Failed SQL stmt: INSERT INTO PS_TL_ABS_WRK4 (PROCESS_INSTANCE, EMPLID, EMPL_RCD, DUR, PIN_TAKE_NUM, SEQ_NBR, TL_QUANTITY, EXCL_HOLIDAYS, EXCL_NON_WORK, TL_EXCLUDE_HOL, HOLIDAY_SCHEDULE, BEGIN_DAY_IND, END_DAY_IND, BEGIN_DAY_HRS, BEGIN_DAY_HALF_IND, END_DAY_HRS, END_DAY_HALF_IND, ALL_DAYS_IND, OFFDAY_IND, TRC, COUNTRY, HOLIDAY_FLAG, PAY_SYSTEM_FLG, MAX_SEQ_NBR) SELECT A.PROCESS_INSTANCE, A.EMPLID, A.EMPL_RCD, TQ.DUR, A.PIN_TAKE_NUM, 0, A.DAY_COUNT_PAID, 'Y', 'Y', PW.TL_EXCLUDE_HOL, PW.HOLIDAY_SCHEDULE , ' ', ' ', 0, 'N', 0, 'N', 'N', ' ', ' ', PW.COUNTRY, 'N', PW.PAY_SYSTEM_FLG, 0 FROM PS_TL_ABS_RS_TAO4 A, PS_TL_PROF_LIST4 PL, PS_TL_PROF_WRK4 PW, PS_TL_TOTAL_QTY4 TQ WHERE PL.PROCESS_INSTANCE = 235957 AND PW.PROCESS_INSTANCE = 235957 AND TQ.PROCESS_INSTANCE = 235957 AND PW.PROCESS_INSTANCE = PL.PROCESS_INSTANCE AND TQ.PROCESS_INSTANCE = PL.PROCESS_INSTANCE AND A.EMPLID = PL.EMPLID AND A.EMPL_RCD = PL.EMPL_RCD AND A.EMPLID = TQ.EMPLID AND A.EMPL_RCD = TQ.EMPL_RCD AND A.EMPLID = PW.EMPLID AND A.EMPL_RCD = PW.EMPL_RCD AND A.ABSENCE_DATE BETWEEN PL.START_DT AND PL.END_DT AND TQ.DUR = A.ABSENCE_DATE AND TQ.DUR BETWEEN PL.START_DT AND PL.END_DT AND TQ.DUR BETWEEN PW.EFFDT AND PW.END_EFFDT

Process 235957 ABENDED at Step TL_ABS_RSLV.GA100.Step03 (SQL) -- RC = 805 (108,524)

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

REPLICATION STEPS
=================
1. Employee Job Change History
As of 7/1/20, this employee belonged to a Pay Group “A” for Payroll NA, and “B” for AM.
As of 3/22/21 with promotion, this employee’s Pay Group for AM changed from "B" to “A”.
The Pay Period is Bi-weekly, and 3/22/21 was a middle of a pay period that runs between 3/14/21 and 3/28/21.

2.  Timesheet for a pay period (3/14/21 - 3/28/21)
3/15 - 5 hrs Sick (Absence Management)
3/16 - 7:30am IN, 12:30pm OUT, REG
3/17 - 7:30am IN, 12:30pm OUT, REG
3/18 - 7:30am IN, 12:30pm OUT, REG

3/22 - 7:30am IN, 12:30pm OUT, REG
3/23 - 5 hrs Sick (Absence Management)
3/24 - 7:30am IN, 12:30pm OUT, REG
3/25 - 7:30am IN, 12:30pm OUT, REG

3.  Run TimeAdmin process 

4.  This issue can occur when running TL_ABS_GENPT either via GPPDPRUN or the Generate Absence Payable Time tile.

Cause

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