R12: PAXCPGAL PRC: Generate Asset Lines For A Range Of Projects Performance Issue (Doc ID 2054082.1)

Last updated on MARCH 08, 2017

Applies to:

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

Symptoms

PAXCPGAL- PRC: Generate Asset Lines for a Range of Projects has a performance issue with the following statement:

SELECT /*+ INDEX(pcdl PA_COST_DISTRIBUTION_LINES_U1) */
PA_FAXFACE.GET_GROUP_LEVEL_TASK_ID(PEI.TASK_ID,PT.TOP_TASK_ID, :B1 )
GROUP_LEVEL_TASK_ID, PA_FAXFACE.GET_ASSET_CATEGORY_ID(PEI.DOCUMENT_HEADER_I
D, PEI.DOCUMENT_LINE_NUMBER, PEI.DOCUMENT_DISTRIBUTION_ID,
PEI.TRANSACTION_SOURCE) ASSET_CATEGORY_ID, PEI.SYSTEM_LINKAGE_FUNCTION,
PEI.DOCUMENT_HEADER_ID, PEI.DOCUMENT_LINE_NUMBER,
PEI.DOCUMENT_DISTRIBUTION_ID, PET.EXPENDITURE_CATEGORY,
PEI.EXPENDITURE_TYPE, PEI.NON_LABOR_RESOURCE,
...
FROM
PA_COST_DISTRIBUTION_LINES_ALL PCDL, PA_EXPENDITURE_ITEMS_ALL PEI,
PA_EXPENDITURE_TYPES PET, PA_TASKS PT WHERE PCDL.EXPENDITURE_ITEM_ID =
PEI.EXPENDITURE_ITEM_ID AND PEI.REVENUE_DISTRIBUTED_FLAG||'' = 'N' AND
PEI.COST_DISTRIBUTED_FLAG ='Y' AND PCDL.TRANSFER_STATUS_CODE IN ('P','A',
'V','T','R','G','B') AND (PCDL.LINE_TYPE = :B6 OR PCDL.LINE_TYPE =
...
PA_PROJECT_ASSET_ASSIGNMENTS PAA WHERE PAA.PROJECT_ID = :B1 AND TASK_ID =
PT.TOP_TASK_ID AND DECODE(PAA.PROJECT_ASSET_ID, 0,:B12 , 'N') = 'Y' UNION
ALL SELECT '1' FROM PA_PROJECT_ASSET_ASSIGNMENTS PAA WHERE PAA.PROJECT_ID =
:B1 AND NVL(TASK_ID,0) = 0 AND DECODE(PAA.PROJECT_ASSET_ID, 0,:B12 , 'N') =
'Y' ) AND NOT EXISTS ( SELECT 'This CDL was summarized before' FROM
PA_PROJECT_ASSET_LINE_DETAILS PALD WHERE PALD.EXPENDITURE_ITEM_ID =
PCDL.EXPENDITURE_ITEM_ID AND PALD.LINE_NUM = PCDL.LINE_NUM AND
PALD.REVERSED_FLAG||'' = 'N' ) ORDER BY 1, 2, CIP_CCID,
PEI.CAPITAL_EVENT_ID, GROUPING_METHOD

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