ETL9.1: Performance Issue With TimeAdmin Due Recalculating The Comp. End Balances.

(Doc ID 1369373.1)

Last updated on JULY 27, 2016

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.1 to 9.1 [Release 9]
Information in this document applies to any platform.
*** Checked for relevance on 13-Apr-2012 ***


Symptoms


The Time Administration process takes 2-2.5 hrs to process for 500 employees. During Customer's analysis they found that the App Engine TL_TA000900 (Validate balances) was processing rows from 2003.
The process TL_TA000900 is calling UPDATE section in app engine TL_COMPTIME which has the following SQL in Do Select of Step01.
%Select(EMPLID,EMPL_RCD,COMP_TIME_PLAN,DUR)
SELECT DISTINCT B.EMPLID
,B.EMPL_RCD
,B.COMP_TIME_PLAN
,MIN(%DateOut(B.DUR))
FROM %Table(TL_COMP_DAY_BAL) B
WHERE EXISTS (
SELECT 'X'
FROM %Table(TL_TA_BATCH) C
, %Table(TL_COMPLEAV_TBL) D
WHERE C.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
AND ((D.COMP_LEAV_IND IN ('CERN')
AND D.EXP_DT >= %Bind(TL_TA_MAIN_AET.FROMDATE))
OR (D.COMP_LEAV_IND IN ('CTKN')
AND D.DUR >= %Bind(TL_TA_MAIN_AET.FROMDATE)))
AND C.BATCH_NUM = %Bind(TL_TA_MAIN_AET.BATCH_NUM)
AND C.EMPLID = D.EMPLID
AND C.EMPL_RCD = D.EMPL_RCD
AND D.EMPLID = B.EMPLID
AND D.EMPL_RCD = B.EMPL_RCD
AND D.DUR = B.DUR
AND D.TRC = B.TRC
AND D.COMP_TIME_PLAN = B.COMP_TIME_PLAN )
GROUP BY B.EMPLID, B.EMPL_RCD, B.COMP_TIME_PLAN

This is selecting rows that have an EXP_DT in the TL_COMPLEAV_TBL that is greater than the TL_TA_MAIN_AET.FROMDATE.
Customer has some COMP TIME PLANS that do not expire and they have an EXP_DT as Jan-01-2025(As defaulted by PeopleSoft for non expiring comp time plans).
Even though they have their DUR in the year 2003, the above SQL is not limiting the date range of future dated rows or eliminating the comp time plans that do not expire and all the rows are being processed.
Note: This code was introduced as a fix for the Incident# 1992881000 " Incorrect END_BAL on TL_COMP_DAY_BAL Table" that posted in 9.1 bundle#4.

REPLICATION STEPS
=================
1. Employee enrolled into comp plan which setup to never expire.
2. Employee has a history rows populated in tl_comp_day_bal and tl_compleav_tbl since 2003 with correct data and with expiration of 1/1/2025.
3. Submit time on 10/1/11 that set the ECD to 10/1/11.
4. Run TimeAdmin.  TA would reprocess time from 2003 and delete/reinsert all data into tl_comp_day_bal.

Changes

n/a

Cause

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