R12: PAVDVC: PRC: Distribute Supplier Invoice Adjustment Costs Performance Issue
(Doc ID 2629043.1)
Last updated on NOVEMBER 17, 2022
Applies to:
Oracle Project Costing - Version 12.1.3 and laterInformation in this document applies to any platform.
Symptoms
PRC: Distribute Supplier Invoice Adjustment Costs is running for over 17 hours.
The long running query is
SELECT /*+ leading(P) */
item.expenditure_item_id
FROM
pa_expenditures exp,
pa_expenditure_items item,
pa_tasks t,
pa_projects_all p
WHERE
exp.expenditure_status_code || '' = 'APPROVED'
AND exp.expenditure_id = item.expenditure_id
AND item.cost_distributed_flag = 'N'
AND ( nvl(item.request_id, 136666747 + 1) != 136666747
OR item.cost_dist_rejection_code IS NULL )
AND ( item.override_to_organization_id IS NULL
OR EXISTS (
SELECT
NULL
FROM
hr_organization_units
WHERE
organization_id = item.override_to_organization_id
) )
AND EXISTS (
SELECT
1
FROM
pa_cost_distribution_lines cdl
WHERE
cdl.expenditure_item_id = DECODE(item.adjusted_expenditure_item_id, NULL, DECODE(item.transferred_from_exp_item_id, NULL
, item.expenditure_item_id, item.transferred_from_exp_item_id), item.adjusted_expenditure_item_id)
AND cdl.system_reference1 IS NOT NULL
AND cdl.system_reference2 IS NOT NULL
AND cdl.system_reference3 IS NOT NULL
AND cdl.line_num = 1
)
AND t.task_id = item.task_id
AND t.project_id = p.project_id
AND p.segment1 BETWEEN :start_project_numbe r AND :end_project_number AND ITEM.System_Linkage_Function = 'VI' ORDER BY ITEM.Expenditure_Item_Date FOR UPDATE of item.expenditure_item_id
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 |