PRC: Create and Distribute Burden Transactions Performance is Poor (Doc ID 2024497.1)

Last updated on JUNE 26, 2017

Applies to:

Oracle Project Costing - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

There is a performance issue when running the "PRC: Create and Distribute Burden Transactions" process.

The problem SQL taking the most time is:

akv1jwt9n29dd
SELECT P.PROJECT_ID , P.SEGMENT1 , P.ORG_ID ,
UPPER(NVL(PT.BURDEN_ACCOUNT_FLAG, 'N')) BURDEN_ACCOUNT_FLAG ,
PT.BURDEN_SUM_DEST_PROJECT_ID DEST_PROJECT_ID , PT.BURDEN_SUM_DEST_TASK_ID
DEST_TASK_ID , UPPER(PT.BURDEN_AMT_DISPLAY_METHOD) BURDEN_AMT_DISPLAY_METHOD
FROM PA_PROJECTS_ALL P, PA_PROJECT_TYPES_ALL PT WHERE PT.PROJECT_TYPE =
P.PROJECT_TYPE AND P.SEGMENT1 BETWEEN :B2 AND :B1 AND (
PT.BURDEN_AMT_DISPLAY_METHOD IN ('D', 'd') OR PT.BURDEN_AMT_DISPLAY_METHOD IN
('S', 's') AND PT.BURDEN_ACCOUNT_FLAG IN ('Y', 'y')) AND PT.ORG_ID = P.ORG_ID
AND PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(P.PROJECT_STATUS_CODE,
'GENERATE_BURDEN') = 'Y' AND EXISTS (SELECT /*+ leading(cdl) */ 1 FROM
PA_COST_DISTRIBUTION_LINES CDL, PA_EXPENDITURE_ITEMS EI WHERE CDL.LINE_TYPE =
'R' AND CDL.AMOUNT <> 0 AND CDL.BURDEN_SUM_SOURCE_RUN_ID = :B4 AND
CDL.PROJECT_ID = P.PROJECT_ID AND EI.PROJECT_ID = P.PROJECT_ID AND
EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND EI.EXPENDITURE_ITEM_DATE
<= NVL(:B3 , EI.EXPENDITURE_ITEM_DATE) ) UNION SELECT P.PROJECT_ID ,
P.SEGMENT1 , P.ORG_ID , UPPER(NVL(PT.BURDEN_ACCOUNT_FLAG, 'N'))
BURDEN_ACCOUNT_FLAG , PT.BURDEN_SUM_DEST_PROJECT_ID DEST_PROJECT_ID ,
PT.BURDEN_SUM_DEST_TASK_ID DEST_TASK_ID , UPPER(PT.BURDEN_AMT_DISPLAY_METHOD)
BURDEN_AMT_DISPLAY_METHOD FROM PA_PROJECTS_ALL P, PA_PROJECT_TYPES_ALL PT
WHERE PT.PROJECT_TYPE = P.PROJECT_TYPE AND P.SEGMENT1 BETWEEN :B2 AND :B1 AND
( PT.BURDEN_AMT_DISPLAY_METHOD IN ('D', 'd') OR PT.BURDEN_AMT_DISPLAY_METHOD
IN ('S', 's') AND PT.BURDEN_ACCOUNT_FLAG IN ('Y', 'y')) AND PT.ORG_ID =
P.ORG_ID AND
PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(P.PROJECT_STATUS_CODE,
'GENERATE_BURDEN') = 'Y' AND EXISTS ( SELECT 1 FROM
PA_COST_DISTRIBUTION_LINES CDL, PA_EXPENDITURE_ITEMS EI WHERE CDL.LINE_TYPE =
'R' AND CDL.AMOUNT <> 0 AND CDL.BURDEN_SUM_SOURCE_RUN_ID >0 AND
NVL(CDL.REVERSED_FLAG, 'N') = 'N' AND CDL.LINE_NUM_REVERSED IS NULL AND
EI.ADJUSTMENT_TYPE ='BURDEN_RESUMMARIZE' AND CDL.PROJE CT_ID = P.PROJECT_ID
AND EI.PROJECT_ID = P.PROJECT_ID AND EI.EXPENDITURE_ITEM_ID =
CDL.EXPENDITURE_ITEM_ID AND EI.EXPENDITURE_ITEM_DATE <= NVL(:B3 ,
EI.EXPENDITURE_ITEM_DATE) )

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