R12 PASDUC PRC: Create And Distribute Burden Transactions For A Range Of Projects Performance Issues

Last updated on NOVEMBER 23, 2015

Applies to:

Symptoms

PASDUC PRC: Create and Distribute Burden Transactions for a Range of Projects has performance issues

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_STATU
S_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 = :B4 AND
CDL.PROJECT_ID = P.PROJECT_ID AND EI.EXPENDITURE_ITEM_ID =
CDL.EXPENDITURE_ITEM_ID AND EI.EXPENDITURE_ITEM_DATE EI.EXPENDITURE_ITEM_DATE) ) OR 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.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 EI.EXPENDITURE_ITEM_DATE)) )

Cause