ETL 9.1: Performance Issue TL_PUB_TM1.PUBLISH.Step010 & Step030 Slow (Doc ID 1603637.1)

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.

Symptoms

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.
(TL_PUB_TM1.DILUTION.Step010) (SQL)
%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 = (
SELECT MAX(EFFDT)
 FROM PS_TL_TRC_TBL
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.

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