R12: PAVDVC: PRC: Distribute Supplier Invoice Adjustment Costs Performance Issue (Doc ID 2629043.1)

Last updated on NOVEMBER 17, 2022

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.