ETL9.2:Perfromance Issue When Entering New TRC

(Doc ID 2351424.1)

Last updated on JANUARY 24, 2018

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.2 and later
Information in this document applies to any platform.

Goal

On : 9.2 version, Perfromance issue when entering new TRC ,We installed pum 21 and are having still having the timing out issue. I have included below the sql from record TL_COMP_TRC field TRC save edit. What is in pum 21 isn't the same fix that you gave us six months ago.
The sql in PUM21

SQLExec("SELECT COUNT(*) FROM PS_TL_EMPL_COMP T WHERE EXISTS (SELECT 'X' FROM PS_TL_EMPL_COMP T1 WHERE T1.EMPLID=T.EMPLID AND T1.EMPL_RCD=T.EMPL_RCD AND T1.COMP_TIME_PLAN IN (SELECT C.COMP_TIME_PLAN FROM PS_TL_COMP_TRC C WHERE C.TRC=:1 AND C.EFFDT=(SELECT MAX(C1.EFFDT) FROM PS_TL_COMP_TRC C1 WHERE C1.COMP_TIME_PLAN=C.COMP_TIME_PLAN AND C1.TRC=C.TRC)) AND T1.EFF_STATUS='A' AND T1.EFFDT=(SELECT MAX(E.EFFDT) FROM PS_TL_EMPL_COMP E WHERE E.EMPLID=T1.EMPLID AND E.EMPL_RCD=T1.EMPL_RCD AND E.COMP_TIME_PLAN<>T1.COMP_TIME_PLAN AND T1.COMP_TIME_PLAN <> :2 ) AND T1.COMP_TIME_PLAN <> :2) AND T.COMP_TIME_PLAN=:2", &TRC, &PLAN, &COUNT1);

The sql provided for the bug fix that was to be included in pum 21 you will notice a difference in the sql towards the very end below:

SQLExec("SELECT COUNT(*) FROM PS_TL_EMPL_COMP T WHERE EXISTS (SELECT 'X' FROM PS_TL_EMPL_COMP T1 WHERE T1.EMPLID=T.EMPLID AND T1.EMPL_RCD=T.EMPL_RCD AND T1.COMP_TIME_PLAN IN (SELECT C.COMP_TIME_PLAN FROM PS_TL_COMP_TRC C WHERE C.TRC=:1 AND C.EFFDT=(SELECT MAX(C1.EFFDT) FROM PS_TL_COMP_TRC C1 WHERE C1.COMP_TIME_PLAN=C.COMP_TIME_PLAN AND C1.TRC=C.TRC)) AND T1.EFF_STATUS='A' AND T1.EFFDT=(SELECT MAX(E.EFFDT) FROM PS_TL_EMPL_COMP E WHERE E.EMPLID=T1.EMPLID AND E.EMPL_RCD=T1.EMPL_RCD AND E.COMP_TIME_PLAN<>T1.COMP_TIME_PLAN AND T1.COMP_TIME_PLAN <> :2) AND T1.COMP_TIME_PLAN <> :2) AND T.COMP_TIME_PLAN=:3", &TRC, &PLAN, &COMP_TIME_PLAN, &COUNT1);
 




 

Solution

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