R12 PAXACMPT - PRC: Refresh Project Summary Amounts Performance Is Slow (Doc ID 1570738.1)

Last updated on AUGUST 01, 2016

Applies to:

Oracle Project Planning and Control - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Project Performance Reporting

The performance running the PAXACMPT PRC: Refresh Project Summary Amounts process takes hours. The most expensive query is:

SELECT CDL.ROWID CDLROWID, CDL.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID,
  CDL.LINE_NUM LINE_NUM, PE.INCURRED_BY_PERSON_ID PERSON_ID, EI.JOB_ID JOB_ID,
  NVL(EI.OVERRIDE_TO_ORGANIZATION_ID, PE.INCURRED_BY_ORGANIZATION_ID)
  ORGANIZATION_ID, DECODE(EI.SYSTEM_LINKAGE_FUNCTION,'VI',
  CDL.SYSTEM_REFERENCE1,NULL) VENDOR_ID, ET.EXPENDITURE_TYPE EXPENDITURE_TYPE,
  EI.NON_LABOR_RESOURCE NON_LABOR_RESOURCE, ET.EXPENDITURE_CATEGORY
  EXPENDITURE_CATEGORY, ET.REVENUE_CATEGORY_CODE REVENUE_CATEGORY,
  EI.ORGANIZATION_ID NON_LABOR_RESOURCE_ORG_ID, EI.SYSTEM_LINKAGE_FUNCTION
  SYSTEM_LINKAGE_FUNCTION, CDL.PROJECT_ID PROJECT_ID, CDL.TASK_ID TASK_ID,
  CDL.RECVR_PA_PERIOD_NAME PA_PERIOD, CDL.RECVR_GL_PERIOD_NAME GL_PERIOD,
  PE.EXPENDITURE_ENDING_DATE WEEK_ENDING_DATE,
  LAST_DAY(EI.EXPENDITURE_ITEM_DATE) MONTH_ENDING_DATE, NVL(CDL.AMOUNT,0)
  RAW_COST, NVL(CDL.QUANTITY,0) QUANTITY, NVL(CDL.BURDENED_COST,0)
  BURDENED_COST, DECODE(CDL.BILLABLE_FLAG,'Y',NVL(CDL.AMOUNT,0),0)
  BILLABLE_RAW_COST, DECODE(CDL.BILLABLE_FLAG,'Y',NVL(CDL.QUANTITY,0),0)
  BILLABLE_QUANTITY, DECODE(CDL.BILLABLE_FLAG,'Y',NVL(CDL.BURDENED_COST,0),0)
  BILLABLE_BURDENED_COST, DECODE(ET.UNIT_OF_MEASURE ,NULL, EI.UNIT_OF_MEASURE,
  ET.UNIT_OF_MEASURE) UNIT_OF_MEASURE, CDL.IND_COMPILED_SET_ID
  COST_IND_COMPILED_SET_ID
FROM
 PA_EXPENDITURES_ALL PE, PA_EXPENDITURE_TYPES ET, PA_EXPENDITURE_ITEMS_ALL EI,
  PA_COST_DISTRIBUTION_LINES_ALL CDL WHERE CDL.PROJECT_ID = :B5 AND
  CDL.LINE_TYPE = :B4 AND CDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
  AND NVL(CDL.ORG_ID,-99) = NVL(EI.ORG_ID,-99) AND EI.EXPENDITURE_TYPE =
  ET.EXPENDITURE_TYPE AND EI.TASK_ID = CDL.TASK_ID AND PE.EXPENDITURE_ID =
  EI.EXPENDITURE_ID AND NVL(PE.ORG_ID,-99) = NVL(EI.ORG_ID,-99) AND
  EI.SYSTEM_LINKAGE_FUNCTION||'' = NVL(:B3 ,EI.SYSTEM_LINKAGE_FUNCTION) AND
  TRUNC(CDL.PA_DATE) BETWEEN :B2 AND :B1

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