PAXACMPT: Performance Issue With PRC: Update Project Summary Amounts (Doc ID 1319308.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Project Management - Version 11.5.10.2 and later
Information in this document applies to any platform.
Report:PAXACMPT.RDF - PRC: Update Project Summary Amounts

***Checked for relevance on 06-FEB-2013***



Symptoms

PRC: Update Project Summary Amounts shows a performance issue on the following statements:

SELECT PTA.TXN_ACCUM_ID, NVL(PT.PARENT_TASK_ID,PT.TASK_ID) PARENT_TASK_ID,
PTA.TASK_ID TASK_ID, PTA.PA_PERIOD, PTA.GL_PERIOD, DECODE(:B2 ,'RL',
NVL(PTA.TOT_REVENUE,0),NVL(PTA.I_TOT_REVENUE,0)) I_TOT_REVENUE, DECODE(:B2 ,
'RL',NVL(PTA.TOT_RAW_COST,0),NVL(PTA.I_TOT_RAW_COST,0)) I_TOT_RAW_COST,
DECODE(:B2 ,'RL',NVL(PTA.TOT_BURDENED_COST,0),NVL(PTA.I_TOT_BURDENED_COST,0)
) I_TOT_BURDENED_COST, DECODE(:B2 ,'RL',NVL(PTA.TOT_QUANTITY,0),
NVL(PTA.I_TOT_QUANTITY,0)) I_TOT_QUANTITY, DECODE(:B2 ,'RL',
NVL(PTA.TOT_LABOR_HOURS,0),NVL(PTA.I_TOT_LABOR_HOURS,0)) I_TOT_LABOR_HOURS,
DECODE(:B2 ,'RL',NVL(PTA.TOT_BILLABLE_RAW_COST,0),
NVL(PTA.I_TOT_BILLABLE_RAW_COST,0)) I_TOT_BILLABLE_RAW_COST , DECODE(:B2 ,
'RL',NVL(PTA.TOT_BILLABLE_BURDENED_COST,0),
NVL(PTA.I_TOT_BILLABLE_BURDENED_COST,0)) I_TOT_BILLABLE_BURDENED_COST,
DECODE(:B2 ,'RL',NVL(PTA.TOT_BILLABLE_QUANTITY,0),
NVL(PTA.I_TOT_BILLABLE_QUANTITY,0))I_TOT_BILLABLE_QUANTITY, DECODE(:B2 ,
'RL',NVL(PTA.TOT_BILLABLE_LABOR_HOURS,0),NVL(PTA.I_TOT_BILLABLE_LABOR_HOURS,
0)) I_TOT_BILLABLE_LABOR_HOURS, NVL(PTA.TOT_CMT_RAW_COST,0)
TOT_CMT_RAW_COST, NVL(PTA.TOT_CMT_BURDENED_COST,0) TOT_CMT_BURDENED_COST,
NVL(PTA.TOT_CMT_QUANTITY,0) TOT_CMT_QUANTITY, PTA.ACTUAL_COST_ROLLUP_FLAG,
PTA.REVENUE_ROLLUP_FLAG, PTA.CMT_ROLLUP_FLAG, GPS.PERIOD_YEAR,
PARA.RESOURCE_LIST_ASSIGNMENT_ID, PARA.RESOURCE_LIST_ID,
PARA.RESOURCE_LIST_MEMBER_ID, PARA.RESOURCE_ID , PARL.TRACK_AS_LABOR_FLAG,
PAR.ROLLUP_QUANTITY_FLAG , PAR.UNIT_OF_MEASURE, PT.WBS_LEVEL
FROM
PA_TXN_ACCUM PTA, PA_TASKS PT, GL_PERIODS GPS, PA_IMPLEMENTATIONS PI,
PA_RESOURCE_ACCUM_DETAILS PARA, PA_RESOURCES PAR, PA_RESOURCE_LIST_MEMBERS
PARL WHERE PARL.RESOURCE_LIST_ID = NVL(:B7 ,PARL.RESOURCE_LIST_ID) AND
PARA.RESOURCE_LIST_ID = PARL.RESOURCE_LIST_ID AND
PARA.RESOURCE_LIST_MEMBER_ID = PARL.RESOURCE_LIST_MEMBER_ID AND
PARA.RESOURCE_ID = PAR.RESOURCE_ID AND PTA.PROJECT_ID = :B6 AND
NVL(PARL.MIGRATION_CODE,'-99') <> 'N' AND (PTA.ACTUAL_COST_ROLLUP_FLAG =
DECODE(:B5 ,'Y','Y','X') OR PTA.REVENUE_ROLLUP_FLAG = DECODE(:B4 ,'Y','Y',
'X') OR PTA.CMT_ROLLUP_FLAG = DECODE(:B3 ,'Y','Y','X') OR :B2 = 'RL') AND
GPS.PERIOD_SET_NAME = PI.PERIOD_SET_NAME AND GPS.ADJUSTMENT_PERIOD_FLAG =
'N' AND GPS.PERIOD_NAME = PTA.PA_PERIOD AND TRUNC(GPS.END_DATE) <= :B1 AND
PT.TASK_ID = PTA.TASK_ID AND PARA.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID UNION
SELECT PTA.TXN_ACCUM_ID, 0 PARENT_TASK_ID, 0 TASK_ID, PTA.PA_PERIOD,
PTA.GL_PERIOD, DECODE(:B2 ,'RL',NVL(PTA.TOT_REVENUE,0),
NVL(PTA.I_TOT_REVENUE,0)) I_TOT_REVENUE, DECODE(:B2 ,'RL',
NVL(PTA.TOT_RAW_COST,0),NVL(PTA.I_TOT_RAW_COST,0)) I_TOT_RAW_COST,
DECODE(:B2 ,'RL',NVL(PTA.TOT_BURDENED_COST,0),NVL(PTA.I_TOT_BURDENED_COST,0)
) I_TOT_BURDENED_COST, DECODE(:B2 ,'RL',NVL(PTA.TOT_QUANTITY,0),
NVL(PTA.I_TOT_QUANTITY,0)) I_TOT_QUANTITY, DECODE(:B2 ,'RL',
NVL(PTA.TOT_LABOR_HOURS,0),NVL(PTA.I_TOT_LABOR_HOURS,0)) I_TOT_LABOR_HOURS,
DECODE(:B2 ,'RL',NVL(PTA.TOT_BILLABLE_RAW_COST,0),
NVL(PTA.I_TOT_BILLABLE_RAW_COST,0)) I_TOT_BILLABLE_RAW_COST , DECODE(:B2 ,
'RL',NVL(PTA.TOT_BILLABLE_BURDENED_COST,0),
NVL(PTA.I_TOT_BILLABLE_BURDENED_COST,0)) I_TOT_BILLABLE_BURDENED_COST,
DECODE(:B2 ,'RL',NVL(PTA.TOT_BILLABLE_QUANTITY,0),
NVL(PTA.I_TOT_BILLABLE_QUANTITY,0))I_TOT_BILLABLE_QUANTITY, DECODE(:B2 ,
'RL',NVL(PTA.TOT_BILLABLE_LABOR_HOURS,0),NVL(PTA.I_TOT_BILLABLE_LABOR_HOURS,
0)) I_TOT_BILLABLE_LABOR_HOURS, NVL(PTA.TOT_CMT_RAW_COST,0)
TOT_CMT_RAW_COST, NVL(PTA.TOT_CMT_BURDENED_COST,0) TOT_CMT_BURDENED_COST,
NVL(PTA.TOT_CMT_QUANTITY,0) TOT_CMT_QUANTITY, PTA.ACTUAL_COST_ROLLUP_FLAG,
PTA.REVENUE_ROLLUP_FLAG, PTA.CMT_ROLLUP_FLAG, GPS.PERIOD_YEAR,
PARA.RESOURCE_LIST_ASSIGNMENT_ID, PARA.RESOURCE_LIST_ID,
PARA.RESOURCE_LIST_MEMBER_ID, PARA.RESOURCE_ID , PARL.TRACK_AS_LABOR_FLAG,
PAR.ROLLUP_QUANTITY_FLAG , PAR.UNIT_OF_MEASURE, 0 WBS_LEVEL FROM
PA_TXN_ACCUM PTA, GL_PERIODS GPS, PA_IMPLEMENTATIONS PI,
PA_RESOURCE_ACCUM_DETAILS PARA, PA_RESOURCES PAR, PA_RESOURCE_LIST_MEMBERS
PARL WHERE PARL.RESOURCE_LIST_ID = NVL(:B7 ,PARL.RESOURCE_LIST_ID) AND
PARA.RESOURCE_LIST_ID = PARL.RESOURCE_LIST_ID AND
PARA.RESOURCE_LIST_MEMBER_ID = PARL.RESOURCE_LIST_MEMBER_ID AND
PARA.RESOURCE_ID = PAR.RESOURCE_ID AND PTA.PROJECT_ID = :B6 AND
NVL(PARL.MIGRATION_CODE,'-99') <> 'N' AND (PTA.ACTUAL_COST_ROLLUP_FLAG =
DECODE(:B5 ,'Y','Y','X') OR PTA.REVENUE_ROLLUP_FLAG = DECODE(:B4 ,'Y','Y',
'X') OR PTA.CMT_ROLLUP_FLAG = DECODE(:B3 ,'Y','Y','X') OR :B2 = 'RL') AND
GPS.PERIOD_SET_NAME = PI.PERIOD_SET_NAME AND GPS.ADJUSTMENT_PERIOD_FLAG =
'N' AND GPS.PERIOD_NAME = PTA.PA_PERIOD AND TRUNC(GPS.END_DATE) <= :B1 AND
PARA.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID ORDER BY 2,29,3,24

and

SELECT DISTINCT PTA.TXN_ACCUM_ID, NVL(PT.PARENT_TASK_ID,NVL(PT.TASK_ID,0))
TOP_TASK_ID, PTA.TASK_ID, PTA.PA_PERIOD, PTA.GL_PERIOD,
NVL(PTA.I_TOT_REVENUE,0) I_TOT_REVENUE, NVL(PTA.I_TOT_RAW_COST,0)
I_TOT_RAW_COST, NVL(PTA.I_TOT_BURDENED_COST,0) I_TOT_BURDENED_COST,
NVL(PTA.I_TOT_QUANTITY,0) I_TOT_QUANTITY, NVL(PTA.I_TOT_LABOR_HOURS,0)
I_TOT_LABOR_HOURS, NVL(PTA.I_TOT_BILLABLE_RAW_COST,0)
I_TOT_BILLABLE_RAW_COST , NVL(PTA.I_TOT_BILLABLE_BURDENED_COST,0)
I_TOT_BILLABLE_BURDENED_COST, NVL(PTA.I_TOT_BILLABLE_QUANTITY,0)
I_TOT_BILLABLE_QUANTITY, NVL(PTA.I_TOT_BILLABLE_LABOR_HOURS,0)
I_TOT_BILLABLE_LABOR_HOURS, NVL(PTA.TOT_CMT_RAW_COST,0) TOT_CMT_RAW_COST,
NVL(PTA.TOT_CMT_BURDENED_COST,0) TOT_CMT_BURDENED_COST,
NVL(PTA.TOT_CMT_QUANTITY,0) TOT_CMT_QUANTITY, PTA.ACTUAL_COST_ROLLUP_FLAG,
PTA.REVENUE_ROLLUP_FLAG, PTA.CMT_ROLLUP_FLAG, PTA.UNIT_OF_MEASURE,
GPS.PERIOD_YEAR, NVL(PT.WBS_LEVEL,0)
FROM
PA_TXN_ACCUM PTA, PA_TASKS PT, GL_PERIODS GPS, PA_IMPLEMENTATIONS PI WHERE
PTA.PROJECT_ID = :B6 AND PTA.TASK_ID = PT.TASK_ID(+) AND
(PTA.ACTUAL_COST_ROLLUP_FLAG = DECODE(:B5 ,'Y','Y','X') OR
PTA.REVENUE_ROLLUP_FLAG = DECODE(:B4 ,'Y','Y','X') OR PTA.CMT_ROLLUP_FLAG =
DECODE(:B3 ,'Y','Y','X') OR :B2 = 'RL') AND GPS.PERIOD_SET_NAME =
PI.PERIOD_SET_NAME AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N' AND GPS.PERIOD_NAME
= PTA.PA_PERIOD AND TRUNC(GPS.END_DATE) <= :B1 ORDER BY 2,23,3

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