11i PASDUC - PRC: Create And Distribute Burden Transaction Performance is Slow

(Doc ID 1259375.1)

Last updated on AUGUST 01, 2016

Applies to:

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

Symptoms

The PASDUC PRC: Create and Distribute Burden Transactions for a Range of Projects and also for a Single Project encounters slow performance. The most expensive query is:

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 :B3 AND :B2 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 NVL(PT.ORG_ID,-99)
= NVL(P.ORG_ID,-99) AND
PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(P.PROJECT_STATUS_CODE,
'GENERATE_BURDEN') = 'Y' AND (( :B1 IS NOT NULL AND EXISTS (SELECT 1 FROM
PA_COST_DISTRIBUTION_LINES CDL, PA_EXPENDITURE_ITEMS EI WHERE CDL.LINE_TYPE
= 'R' AND NVL(CDL.AMOUNT,0) <> 0 AND CDL.BURDEN_SUM_SOURCE_RUN_ID = :B5 AND
CDL.PROJECT_ID = P.PROJECT_ID AND EI.EXPENDITURE_ITEM_ID =
CDL.EXPENDITURE_ITEM_ID AND EI.EXPENDITURE_ITEM_DATE <= :B4 ) ) OR :B1 IS
NULL AND EXISTS (SELECT 1 FROM PA_COST_DISTRIBUTION_LINES CDL WHERE
CDL.LINE_TYPE = 'R' AND NVL(CDL.AMOUNT,0) <> 0 AND
CDL.BURDEN_SUM_SOURCE_RUN_ID = :B5 AND CDL.PROJECT_ID = P.PROJECT_ID ) )

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