PC_TL_TO_PC Performance At CA_LIMITS.TempTblB.LMTDPR (Doc ID 2280622.1)

Last updated on JUNE 26, 2017

Applies to:

PeopleSoft Enterprise FIN Contracts - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

On :  9.2 PUM 15 version, Time and Labor Integration

ACTUAL BEHAVIOR  
---------------
From the timing trace, I found this 1 step that takes a long time to execute
CA_LIMITS
TempTblB.LMTDPR.S 1 0.000 1 4932.610 0 0.000 4932.610 --> 82 minutes

-- 15:22:05.006 ......(CA_LIMITS.TempTblB.LMTDPR) (SQL)
INSERT INTO PS_CA_LMTDPR_TAO4 (PROCESS_INSTANCE, BUSINESS_UNIT, PROJECT_ID,
ACTIVITY_ID, RESOURCE_ID) SELECT LMT.PROCESS_INSTANCE, PR.BUSINESS_UNIT,
PR.PROJECT_ID, PR.ACTIVITY_ID, PR.RESOURCE_ID FROM PS_CA_LMT_TAO4 LMT ,
PS_CA_CONTR_HDR HDR , PS_CA_DETAIL CD , PS_PROJ_RES_TMP PR WHERE
LMT.PROCESS_INSTANCE = 120078 AND HDR.CONTRACT_NUM = LMT.CONTRACT_NUM AND
CD.CONTRACT_NUM = LMT.CONTRACT_NUM AND CD.CONTRACT_LINE_NUM =
LMT.CONTRACT_LINE_NUM AND CD.PRICING_STRUCTURE = 'RATE' AND
PR.PROCESS_INSTANCE = 120078 AND PR.BUSINESS_UNIT = CD.BUSINESS_UNIT_PC AND
PR.CONTRACT_NUM = CD.CONTRACT_NUM AND PR.CONTRACT_LINE_NUM =
CD.CONTRACT_LINE_NUM AND ( ( PR.ANALYSIS_TYPE IN ('BIL', 'OLT', 'PMR', 'ROL')
AND PR.BI_DISTRIB_STATUS <> 'I' ) OR ( PR.ANALYSIS_TYPE = 'REV' AND
PR.GL_DISTRIB_STATUS IN ('C', 'N') ) ) AND PR.CA_FEE_STATUS NOT IN ('2', '3',
'4', '5') AND 0 >= ( SELECT COUNT(*) FROM PS_CA_LMT4_TAO4 LMT4 WHERE
LMT4.BUSINESS_UNIT = PR.BUSINESS_UNIT AND LMT4.PROJECT_ID = PR.PROJECT_ID AND
LMT4.ACTIVITY_ID = PR.ACTIVITY_ID AND LMT4.RESOURCE_ID = PR.RESOURCE_ID)
/
-- Row(s) affected: 0

-- 16:44:18.373 ......(CA_LIMITS.TempTblB.LMT4) (SQL)

 EXPECTED BEHAVIOR
-----------------------
PC_TL_TO_PC should complete in a reasonable time.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run PC_TL_TO_PC

Changes

 

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