R12: Performance of PACCBL: PRC: Distribute Borrowed And Lent Amounts Process (Doc ID 2384575.1)

Last updated on NOVEMBER 15, 2022

Applies to:

Symptoms

On 12.1.3 with R12.PJ_PF.B.DELTA.6:

Performance problem with the "PRC: Distribute Borrowed and Lent Amounts" process.
This process takes around 40 minutes to complete. Expected result is no more than 15 minutes.

Note: This is a Test environment with less data to process. In PROD, it runs up to 2 hours.

Tkprof trace shows the most expensive query:

SELECT /*+ INDEX(EI PA_EXPENDITURE_ITEMS_N20) */
ei.acct_tp_exchange_rate acct_tp_exchange_rate
,TO_CHAR(ei.acct_tp_rate_date,'YYYY/MM/DD') acct_tp_rate_date
,TO_CHAR(nvl(ei.PRVDR_ACCRUAL_DATE, ei.expenditure_item_date), 'YYYY/MM/DD') prvdr_accrual_date
,ei.acct_tp_rate_type acct_tp_rate_type
,ei.acct_transfer_price acct_transfer_price
,ei.adjusted_expenditure_item_id adjusted_exp_item_id
,ei.billable_flag billable_flag
,decode(ei.cc_cross_charge_code, 'B',
'Y','N')
calc_tp_flag
,pcls.class_code class_code
,ei.cc_cross_charge_code cross_charge_code
,ei.denom_burdened_cost denom_burdened_cost
,ei.denom_currency_code denom_currency_code
,ei.denom_raw_cost denom_raw_cost
,ei.denom_tp_currency_code denom_tp_currency_code
,ei.denom_transfer_price denom_transfer_price
,nvl(per.employee_number,per.npw_number) employee_number
,ei.expenditure_item_id expenditure_item_id
,rowidtochar(ei.rowid) exp_item_rowid
,decode(ei.override_to_organization_id, null,
exp.incurred_by_organization_id,ei.override_to_organization_id) expenditure_organization_id
,exporg.name expenditure_organization_name
,et.expenditure_category expenditure_category
,TO_CHAR(exp.expenditure_ending_date,'YYYY/MM/DD') expenditure_ending_date
,TO_CHAR(ei.expenditure_item_date, 'YYYY/MM/DD') expenditure_item_date
,ei.expenditure_type expenditure_type
.
.
FROM
pa_tasks t
,pa_projects_all p
,pa_project_classes pcls
,pa_expenditures exp
,pa_expenditure_items ei
,pa_expenditure_types et
,pa_function_transactions fntrn1
,pa_function_transactions fntrn2
,pa_system_linkages sys
,hr_all_organization_units_tl exporg
,hr_all_organization_units_tl projorg
,hr_all_organization_units_tl taskorg
,hr_all_organization_units_tl prvorg
,hr_all_organization_units_tl nlorg
,hr_all_organization_units_tl rcvorg
,per_all_people_f per
,hr_all_organization_units_tl rcvou
WHERE
exp.expenditure_status_code = 'APPROVED'
AND exp.expenditure_id = ei.expenditure_id
AND t.task_id = ei.task_id
AND t.project_id = p.project_id
AND pcls.project_id(+) = p.project_id
AND ei.cc_bl_distributed_code = 'N'
AND ei.cost_distributed_flag = 'Y'
AND ei.system_linkage_function NOT IN ('BTC')
AND et.expenditure_type = ei.expenditure_type
AND ei.system_linkage_function = sys.function
AND fntrn1.function_transaction_code = sys.cc_function_transaction_code
.
.

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.03 0 6 0 0
Execute 1 4.27 4.58 0 0 0 0
Fetch 1 140.44 1191.52 867079 985612 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 144.75 1196.14 867079 985618 0 0

Had customer enabled profile option:
"PA: Create Incremental Transactions for Cost Adjustments Resulting from a Burden Schedule Recompilation" at Site level to Yes, but that did not help.
Performance is still unchanged.

Cause

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