My Oracle Support Banner

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

Last updated on FEBRUARY 03, 2019

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.