ETL9.2: TL Rule Template 250 Performance Issue. (Doc ID 2061853.1)

Last updated on JUNE 01, 2017

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

 ETL9.2:TAMPLATE250 Code Issue Causing Performance Issue

Changes

 

In SQL object TMPL250_S40, there are two WHERE conditions on PUNCH_TYPE that
are in conflict with one another. This results in no rows being updated by
this SQL (which also performs poorly in some batches):
UPDATE %Table(TL_IPT2)
SET PUNCH_END = (
SELECT MIN(A.PUNCH_BEGIN)
FROM %Table(TL_IPT1) A
WHERE A.PROCESS_INSTANCE = %ProcessInstance
AND A.EMPLID = %Table(TL_IPT2).EMPLID
AND A.EMPL_RCD = %Table(TL_IPT2).EMPL_RCD
AND A.PUNCH_BEGIN > %Table(TL_IPT2).PUNCH_BEGIN
AND A.RT_SOURCE <> 'SCH')
WHERE PROCESS_INSTANCE = %ProcessInstance
AND PUNCH_TYPE IN %RuleTemplate()
AND PUNCH_TYPE = '2'
AND TL_QUANTITY = 0
AND EXISTS (
SELECT 'X'
FROM %Table(TL_IPT1) A
WHERE A.PROCESS_INSTANCE = %ProcessInstance
AND A.EMPLID = %Table(TL_IPT2).EMPLID
AND A.EMPL_RCD = %Table(TL_IPT2).EMPL_RCD
AND A.PUNCH_BEGIN > %Table(TL_IPT2).PUNCH_BEGIN
AND A.RT_SOURCE <> 'SCH')


This results in below at run time (note PUNCH_TYPE IN ('1', '3') AND
PUNCH_TYPE = '2') which is conflicting


-- 09.23.11 .......(TL_TA_RULES.YHPCHTM.Step40) (SQL)
UPDATE PS_TL_IPT211 SET PUNCH_END = (SELECT MIN(A.PUNCH_BEGIN) FROM
PS_TL_IPT111 A WHERE A.PROCESS_INSTANCE = 1877558 AND A.EMPLID =
PS_TL_IPT211.EMPLID AND A.EMPL_RCD = PS_TL_IPT211.EMPL_RCD AND A.PUNCH_BEGIN
> PS_TL_IPT211.PUNCH_BEGIN AND A.RT_SOURCE <> 'SCH') WHERE PROCESS_INSTANCE =

1877558 AND PUNCH_TYPE IN ('1', '3') AND PUNCH_TYPE = '2' AND TL_QUANTITY = 0

AND EXISTS (SELECT 'X' FROM PS_TL_IPT111 A WHERE A.PROCESS_INSTANCE = 1877558

AND A.EMPLID = PS_TL_IPT211.EMPLID AND A.EMPL_RCD = PS_TL_IPT211.EMPL_RCD AND

A.PUNCH_BEGIN > PS_TL_IPT211.PUNCH_BEGIN AND A.RT_SOURCE <> 'SCH')
/

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