ETL9.1 AE PY_PULL_COST Running Excessively Long After bundle 13 (Doc ID 2027407.1)

Last updated on SEPTEMBER 30, 2016

Applies to:

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

Symptoms

ENVIRONMENT
-----------
9.1 version, TL Integration
PeopleTools 8.54
Database : Oracle

ACTUAL BEHAVIOR
---------------
PY_PULL_COST process runs for excessive amount of time due to bad sql in
TL_PUB_TM1.DILUTION.Step30

EXPECTED BEHAVIOR
-----------------------
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.


(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'

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, the program runs for 8 or more hours processing old data
unnecessarily.

REPLICATION STEPS
=================
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
Bug 2)---19599417 - TL PULL COST PROCESS DOESN'T SET THE PUBLISH_SW ON PAYABLE TIME TO 'I

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