My Oracle Support Banner

R12: Performance of PACCBL: PRC: Distribute Borrowed And Lent Amounts Process (Doc ID 2384575.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.


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:

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',
,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
, 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
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
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.


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

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