ETL9.1: Process ABENDED At Step TL_TA000700.TA000700.Step180 (SQL) -- RC = 1427 (108,524)
Last updated on JULY 13, 2016
Applies to:PeopleSoft Enterprise HCM Time and Labor - Version 9.1 and later
Information in this document applies to any platform.
On : 9.1 version, Time Administration Bundles 11 thru 15 applied
When attempting to run Time Administration process after applying bundles 11 thru 15 they are getting an abend due to what appears to be a batching issue for employee's who have been rehired and have with multiple effective dates in tl_empl_data.
the following error occurs.
"1427 - ORA-01427: single-row subquery returns more than one row "
failed at batch 23 on step : TL_TA000700.TA000700.Step1
Failed SQL stmt:DELETE FROM PS_TL_EXCEPTION WHERE EXCEPTION_SOURCE = 'TA' AND
EXCEPTION_STATUS NOT IN ('A', 'R') AND EXISTS ( SELECT 'X' FROM
PS_TL_TA_BATCH10 A1 WHERE A1.PROCESS_INSTANCE=3305821 AND A1.EMPLID =
PS_TL_EXCEPTION.EMPLID AND A1.EMPL_RCD = PS_TL_EXCEPTION.EMPL_RCD AND
A1.BATCH_NUM = 23) AND PS_TL_EXCEPTION.DUR >= ( SELECT B.START_DT FROM
PS_TL_TA_BATCHC10 B WHERE B.PROCESS_INSTANCE=3305821 AND B.BATCH_NUM=23 AND
B.EMPLID=PS_TL_EXCEPTION.EMPLID AND B.EMPL_RCD=PS_TL_EXCEPTION.EMPL_RCD) AND
PS_TL_EXCEPTION.DUR < ( SELECT B1.END_DT FROM PS_TL_TA_BATCHC10 B1 WHERE
B1.PROCESS_INSTANCE=3305821 AND B1.BATCH_NUM=23 AND
B1.EMPLID=PS_TL_EXCEPTION.EMPLID AND B1.EMPL_RCD=PS_TL_EXCEPTION.EMPL_RCD)
AND PS_TL_EXCEPTION.EMPLID NOT IN ( SELECT DISTINCT I.EMPLID FROM
PS_TL_IPT110 I WHERE I.PROCESS_INSTANCE=3305821 AND I.EMPLID =
PS_TL_EXCEPTION.EMPLID AND I.EMPL_RCD=PS_TL_EXCEPTION.EMPL_RCD AND
I.DUR=PS_TL_EXCEPTION.DUR AND I.IN_BATCH='Y')
The issue can be reproduced at will with the following steps:
1. apply bundles 11 thru 15
2. rehire 3 employees who have multiple effective dated rows in PS_TL_EMPL_DATA record. One for hire, one for inactive and another for the rehire.
3. Run Time admin using current date processing mode for multiple dynamic groups.
Due to this issue, they cannot apply these bundles to production environment until fix is identifed that resolves the issue.
More Issues addressed:
The solution also address following issues:
-Erroneously created offsets resulting in wrong payable time.
-Unnecessary offsets for rehired employees from previous hire and no payable time generated for current hire
-Also resolved issue with holiday template 080 for rehired employees:
Failed SQL stmt: INSERT INTO PS_TL_WRK176 (PROCESS_INSTANCE, EMPLID, EMPL_RCD, HOLIDAY, START_DT, END_DT) SELECT 1449128, C.EMPLID, C.EMPL_RCD, A.HOLI
AY, ((A.HOLIDAY) + (-1)), ((A.HOLIDAY) + (1)) FROM PS_HOLIDAY_DATE A, PS_TL_TA_BATCHC6 C, PS_TL_WRKGRP_TBL D WHERE C.PROCESS_INSTANCE = 1449128 AND A.
OLIDAY_SCHEDULE = D.HOLIDAY_SCHEDULE AND C.BATCH_NUM = 2 AND D.WORKGROUP = 'T_CA_WKG01' AND D.EFFDT = TO_DATE('2011-08-28','YYYY-MM-DD') AND A.HOLIDAY
>= C.START_DT AND A.HOLIDAY <= C.END_DT
Process 1449128 ABENDED at Step TL_TA_RULES.THOLIDAY.Step30 (SQL) -- RC = 805 (108,524)
Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms