Performance Issues AMDPREPT DPRPT110 STEP10 SQL (Doc ID 2125486.1)

Last updated on MARCH 09, 2017

Applies to:

PeopleSoft Enterprise FIN Asset Management - Version 9.1 and later
Information in this document applies to any platform.

Symptoms

Performance at AMDPREPT DPRPT110 STEP10 SQL. This is new step that was added via Bug 21090512 - EAM: DEPR_RPT IS NOT UPDATING FUTURE YEAR'S SUMMARY

-- 14.11.18 ...(AMDPREPT.DPRPT110.Step10) (SQL)
INSERT INTO PS_DEPR_RP5_TMP4 (PROCESS_INSTANCE , BUSINESS_UNIT , BOOK ,
FISCAL_YEAR , OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE,
BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1, AFFILIATE_INTRA2, CHARTFIELD1,
CHARTFIELD2, CHARTFIELD3 , BUSINESS_UNIT_PC, ACTIVITY_ID, RESOURCE_TYPE,
RESOURCE_CATEGORY, RESOURCE_SUB_CAT, ANALYSIS_TYPE , PROJECT_ID , DEPTID ,
CATEGORY , COST_TYPE , PROFILE_ID , GROUP_ASSET_FLAG) SELECT 9939864 ,
RPT.BUSINESS_UNIT , RPT.BOOK , RPT.FISCAL_YEAR , RPT.OPERATING_UNIT,
RPT.PRODUCT, RPT.FUND_CODE, RPT.CLASS_FLD, RPT.PROGRAM_CODE, RPT.BUDGET_REF,
RPT.AFFILIATE, RPT.AFFILIATE_INTRA1, RPT.AFFILIATE_INTRA2, RPT.CHARTFIELD1,
RPT.CHARTFIELD2, RPT.CHARTFIELD3 , RPT.BUSINESS_UNIT_PC, RPT.ACTIVITY_ID,
RPT.RESOURCE_TYPE, RPT.RESOURCE_CATEGORY, RPT.RESOURCE_SUB_CAT,
RPT.ANALYSIS_TYPE , RPT.PROJECT_ID , RPT.DEPTID , RPT.CATEGORY ,
RPT.COST_TYPE , RPT.PROFILE_ID , RPT.GROUP_ASSET_FLAG FROM PS_DEPR_RPT RPT
WHERE RPT.BUSINESS_UNIT = 'UTMBG' AND RPT.BOOK = 'CAPITAL' AND
RPT.FISCAL_YEAR > ( SELECT MAX(TMP.FISCAL_YEAR) FROM PS_DEPR_RP1_TMP4 TMP
WHERE TMP.BUSINESS_UNIT = RPT.BUSINESS_UNIT AND TMP.ASSET_ID = RPT.ASSET_ID
AND TMP.BOOK = RPT.BOOK AND TMP.CF_SEQNO = RPT.CF_SEQNO) GROUP BY
RPT.BUSINESS_UNIT , RPT.BOOK , RPT.FISCAL_YEAR, RPT.OPERATING_UNIT,
RPT.PRODUCT, RPT.FUND_CODE, RPT.CLASS_FLD, RPT.PROGRAM_CODE, RPT.BUDGET_REF,
RPT.AFFILIATE, RPT.AFFILIATE_INTRA1, RPT.AFFILIATE_INTRA2, RPT.CHARTFIELD1,
RPT.CHARTFIELD2, RPT.CHARTFIELD3 , RPT.BUSINESS_UNIT_PC, RPT.ACTIVITY_ID,
RPT.RESOURCE_TYPE, RPT.RESOURCE_CATEGORY, RPT.RESOURCE_SUB_CAT,
RPT.ANALYSIS_TYPE , RPT.PROJECT_ID , RPT.DEPTID , RPT.CATEGORY ,
RPT.COST_TYPE , RPT.PROFILE_ID , RPT.GROUP_ASSET_FLAG
/
-- Row(s) affected: 22087
COMMIT
-- 16.02.24 ...(AMDPREPT.DPRPT110.Step04) (Call Section AMDPREPT.DPRPT200)

DPRPT110.Step10.S 4 0.0 4 7852.8 0 0.0 7852.8

==
Replication steps:
1 Prepare run control with 4 business units.. it will loop through this step 4 times

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