R12 PAXALGAT: After Upgrade to 12.2.5, PRC: Generate Allocations Transactions has Performance Issues (Doc ID 2285737.1)

Last updated on JULY 12, 2017

Applies to:

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

Symptoms

After upgrade to 12.2.5 from 11i, there is a performance issue with the following statement:

INSERT INTO PA_ALLOC_RUN_BASIS_DET ( RUN_ID , RULE_ID , LINE_NUM , PROJECT_ID
, TASK_ID , RESOURCE_LIST_MEMBER_ID , AMOUNT , LINE_PERCENT , CREATION_DATE
, CREATED_BY , LAST_UPDATE_DATE , LAST_UPDATED_BY , LAST_UPDATE_LOGIN ) (
SELECT PART.RUN_ID ,PART.RULE_ID ,PART.LINE_NUM ,PART.PROJECT_ID ,
PART.TASK_ID ,PARR.RESOURCE_LIST_MEMBER_ID ,NVL(SUM( DECODE (:B8 ,
'TOT_RAW_COST', NVL(PTA.TOT_RAW_COST,0) +NVL( PTA.I_TOT_RAW_COST,0),
'TOT_BILLABLE_RAW_COST', NVL(PTA.TOT_BILLABLE_RAW_COST,0) +NVL(
PTA.I_TOT_BILLABLE_RAW_COST,0), 'TOT_BILLABLE_BURDENED_COST',
NVL(PTA.TOT_BILLABLE_BURDENED_COST,0) +NVL(
PTA.I_TOT_BILLABLE_BURDENED_COST,0), 'TOT_BURDENED_COST',
NVL(PTA.TOT_BURDENED_COST,0) +NVL(PTA.I_TOT_BURDENED_COST,0),
'TOT_LABOR_HOURS', NVL(PTA.TOT_LABOR_HOURS,0) +NVL(PTA.I_TOT_LABOR_HOURS,0),
'TOT_QUANTITY', NVL(PTA.TOT_QUANTITY,0) +NVL(PTA.I_TOT_QUANTITY,0), 0 )),0)
AMOUNT ,PART.LINE_PERCENT ,:B5 ,:B4 ,:B3 ,:B2 ,:B1 FROM
PA_ALLOC_RUN_TARGETS PART, PA_ALLOC_RUN_RESOURCE_DET PARR,
PA_RESOURCE_ACCUM_DETAILS PRAD, PA_TXN_ACCUM PTA WHERE PTA.TXN_ACCUM_ID =
PRAD.TXN_ACCUM_ID AND PRAD.RESOURCE_LIST_MEMBER_ID =
PARR.RESOURCE_LIST_MEMBER_ID AND PRAD.PROJECT_ID = PART.PROJECT_ID AND
PRAD.TASK_ID = PART.TASK_ID AND PART.RUN_ID = :B7 AND PARR.RUN_ID =
PART.RUN_ID AND PARR.MEMBER_TYPE = 'B' AND PART.PROJECT_ID = :B6 AND
PART.EXCLUDE_FLAG = 'N' AND EXISTS (SELECT /*+ NO_UNNEST */ GL.PERIOD_NAME
FROM GL_PERIOD_STATUSES GL, PA_IMPLEMENTATIONS IMP WHERE PTA.GL_PERIOD =
GL.PERIOD_NAME AND GL.SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID AND
GL.APPLICATION_ID = PA_PERIOD_PROCESS_PKG.APPLICATION_ID AND
GL.ADJUSTMENT_PERIOD_FLAG = 'N' AND GL.CLOSING_STATUS IN ('C','F','O','P')
AND GL.END_DATE BETWEEN :B10 AND :B9 ) GROUP BY PART.RUN_ID ,PART.RULE_ID ,
PART.LINE_NUM ,PART.PROJECT_ID ,PART.TASK_ID ,PARR.RESOURCE_LIST_MEMBER_ID ,
PART.LINE_PERCENT ,:B5 ,:B4 ,:B3 ,:B2 ,:B1 )

The process does not complete. In 11i it took 30 mins to process this same number of projects.



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