My Oracle Support Banner

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

Last updated on FEBRUARY 03, 2019

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.