R12 Performance Issue with PAXCPGAL PRC: Generate Asset Lines for a Single Project

(Doc ID 1960400.1)

Last updated on AUGUST 25, 2016

Applies to:

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

Symptoms

 The PAXCPGAL PRC: Generate Asset Lines for a Single Project  takes long time to complete.  

The most expensive query is:

UPDATE PA_EXPENDITURE_ITEMS_ALL EI SET CAPITAL_EVENT_ID = -1,
LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = :B6 , LAST_UPDATE_LOGIN = :B5
, REQUEST_ID = :B4 , PROGRAM_APPLICATION_ID = :B3 , PROGRAM_ID = :B2 ,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE
PROJECT_ID = :B1 AND CAPITAL_EVENT_ID IS NULL AND EXISTS (SELECT 1 FROM
PA_COST_DISTRIBUTION_LINES_ALL CDL WHERE EI.EXPENDITURE_ITEM_ID =
CDL.EXPENDITURE_ITEM_ID AND CDL.BILLABLE_FLAG ||''='Y' AND EXISTS ( SELECT
1 FROM PA_PROJECT_ASSET_LINES_ALL LINES, PA_PROJECT_ASSET_LINE_DETAILS DET
WHERE DET.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND DET.LINE_NUM =
CDL.LINE_NUM_REVERSED AND LINES.TRANSFER_STATUS_CODE = 'T' AND
DET.PROJECT_ASSET_LINE_DETAIL_ID = LINES.PROJECT_ASSET_LINE_DETAIL_ID AND
NOT EXISTS (SELECT NULL FROM PA_PROJECT_ASSET_LINES_ALL LINES,
PA_PROJECT_ASSET_LINE_DETAILS DET WHERE DET.EXPENDITURE_ITEM_ID =
CDL.EXPENDITURE_ITEM_ID AND DET.LINE_NUM = CDL.LINE_NUM AND
LINES.TRANSFER_STATUS_CODE = 'T' AND DET.PROJECT_ASSET_LINE_DETAIL_ID =
LINES.PROJECT_ASSET_LINE_DETAIL_ID ) UNION ALL SELECT 1 FROM
PA_COST_DISTRIBUTION_LINES_ALL PCDL WHERE PCDL.EXPENDITURE_ITEM_ID =
CDL.EXPENDITURE_ITEM_ID AND PCDL.LINE_NUM = CDL.LINE_NUM AND
PCDL.REVERSED_FLAG IS NULL AND PCDL.LINE_NUM_REVERSED IS NULL AND
PCDL.BILLABLE_FLAG ||''='Y' ))

 

 

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