ETL 9.1: Performance Issue TL_PUB_TM1.PUBLISH.Step010 & Step030 Slow
Last updated on MARCH 09, 2017
Applies to:PeopleSoft Enterprise HRMS Time and Labor - Version 9.1 and later
Information in this document applies to any platform.
On : 9.1 version, TL Integrations
PY_PULL_COST process runs for excessive amount of time due to bad sql in TL_PUB_TM1.DILUTION.Step30
If payable time doesn't need to be sent to projects where Publish_flag is set to N or I it should not be selected in the SQL.
The issue can be reproduced at will with the following steps:
1) Enter time for an employee using a nontask taskgroup – submit and approve time.
(2) Run time admin, approve payable time.
(3) Run processes to load time to payroll and then calc, confirm, etc.
(4) When ready, run PY_PULL_COST.
(5) Notice that the PUBLISH_SW flag is still set to “N” in payable time even after PY_PULL_COST runs.
(6) Run PY_PULL_COST again and afterward query whichever instance of PS_WRK_PROJ_TAO that was used by the program and notice that it continues to pull in this data. Also, if your database has rows with an “Ignore” status (PUBLISH_SW = ‘I’), you will also see those rows in this table as well.
%InsertSelect(WRK_PROJ_TAO, TL_PAYABLE_TIME A,PROCESS_INSTANCE=%BIND(PROCESS_INSTANCE,STATIC), TRC_TYPE = D.TRC_TYPE, UNIT_OF_MEASURE = D.UNIT_OF_MEASURE, EST_GROSS = %RoundCurrency(A.EST_GROSS,A.CURRENCY_CD), LBR_DIST_AMT = %RoundCurrency(A.LBR_DIST_AMT,A.CURRENCY_CD), DILUTED_GROSS = %RoundCurrency(A.DILUTED_GROSS,A.CURRENCY_CD), MSGNODENAME = ' ', PUBLISH_SW = 'N')
FROM PS_TL_PAYABLE_TIME A , PS_TL_TRC_TBL D
WHERE A.PAYABLE_STATUS IN ('PD', 'DL', 'CL', 'IG')
AND (A.ACTUAL_PUB_DATE IS NULL
OR A.ACTUAL_PUB_DATE = %DateIn('1900-01-01') )
AND A.PUBLISH_SW <> 'Y'
AND A.TRC = D.TRC
AND D.EFF_STATUS = 'A'
AND D.EFFDT = (
WHERE TRC = D.TRC
AND EFFDT AND D.ADD_GROSS = 'Y'
The issue has the following business impact:
Due to this issue, the program runs for 8 or more hours processing old data unnecessarily.
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