My Oracle Support Banner

R12: PRC: Generate Intercompany Invoices for a Range of Projects Sees a Performance Hit with a Single Operating Unit (OU). (Doc ID 2494338.1)

Last updated on FEBRUARY 19, 2019

Applies to:

Oracle Project Billing - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

PAICGEN: PRC: Generate Intercompany Invoices for a Range of Projects for a certain operating unit (OU), it takes anywhere from 3-11 hours to complete whereas, other OUs complete within seconds.

The longest running query from the sorted TKPROF is:

SELECT decode(p.current_npw_flag, 'Y', p.npw_number, p.employee_number)
employee_number,
  e.incurred_by_person_id person_id,
  pc.class_code class_code,
  et.revenue_category_code revenue_category,
  et.expenditure_category expenditure_category,
  i.expenditure_type expenditure_type,
  oue.name expenditure_organization,
  oue.organization_id expenditure_organization_id,
  oup.name project_organization,
  oup.organization_id project_organization_id,
  out.name task_organization,
  out.organization_id task_organization_id,
  t.service_type_code task_service_type,
  pj.project_type project_type,
  ounlrtl.name non_labor_organization,
  ounlrtl.organization_id non_labor_organization_id,
  pj.segment1 project_number,
  pj.project_id project_id,
  t.task_number task_number,
  t.task_id task_id,
  prvdr.name provider_organization,
  i.cc_prvdr_organization_id provider_organization_id,
  recvr.name receiver_organization ,
  i.cc_recvr_organization_id receiver_organization_id,
  ouex.name expenditure_ou ,
  i.org_id expenditure_ou_id,
  prou.name project_ou,
  pj.org_id project_ou_id,
  i.expenditure_item_id expenditure_item_id,
  nvl(i.adjusted_expenditure_item_id,0) expenditure_item_id,
  i.cc_cross_charge_code cc_process_code,
  decode ( sl.labor_non_labor_flag,'Y',t.ic_labor_tp_schedule_id,
  t.ic_nl_tp_schedule_id ) schedule_id,
  decode (
sl.labor_non_labor_flag,'Y',to_char(t.ic_labor_tp_fixed_date),
  to_char(t.ic_nl_tp_fixed_date) ) fixed_date,
  pj.cc_tax_task_id cc_tax_task_id,
  i.denom_currency_code denom_curr_code,
  i.denom_raw_cost denom_raw_cost ,
  i.job_id job_id ,
  e.incurred_by_person_id incurred_by_person_id ,
  i.denom_burdened_cost denom_burden_cost ,
  i.raw_revenue raw_revenue,
  i.quantity qyantity ,
  i.non_labor_resource non_labor_resource,
  i.denom_tp_currency_code denom_tp_curr_code,
  i.denom_transfer_price denom_transfer_price ,
  i.acct_tp_rate_type acct_tp_rate_type,
  to_char(i.acct_tp_rate_date,'YYYY/MM/DD') acct_tp_rate_date ,
  substr(to_char(i.acct_tp_exchange_rate),1,30) acct_tp_exchange_rate,
  to_char(i.acct_transfer_price) acct_transfer_price,
  i.cc_markup_base_code cc_markup_base_code,
  i.tp_ind_compiled_set_id tp_ind_compiled_set_id ,
  to_char(i.tp_bill_rate) tp_bill_rate,
  i.tp_job_id tp_job_id,
  i.prov_proj_bill_job_id prov_proj_bill_job_id,
  to_char(i.tp_base_amount) tp_base_amount,
  to_char(i.tp_bill_markup_percentage) tp_bill_markup_percentage,
  to_char(i.tp_schedule_line_percentage) tp_schedule_line_percentage,
  to_char(i.tp_rule_percentage) tp_rule_percentage,
  sl.labor_non_labor_flag,
  i.revenue_distributed_flag,
  to_char(i.expenditure_item_date,'YYYY/MM/DD'),
  sl.function,
  i.project_currency_code project_currency_code,
  i.projfunc_currency_code projfunc_currency_code,
decode(i.tp_amt_type_code,'COST_TRANSFER',i.project_rate_type,NULL)
project_tp_rate_type,
decode(i.tp_amt_type_Code,'COST_TRANSFER',to_char(i.project_rate_date,'YYYY/MM
/DD'),NULL) project_tp_rate_date,
decode(i.tp_amt_type_Code,'COST_TRANSFER',decode(i.project_rate_type,'User',i.
project_exchange_rate,NULL),NULL) project_tp_exchange_rate,
decode(i.tp_amt_type_Code,'COST_TRANSFER',i.projfunc_cost_rate_type ,NULL)
projfunc_tp_rate_type,
decode(i.tp_amt_type_Code,'COST_TRANSFER',to_char(i.projfunc_cost_rate_date,'Y
YYY/MM/DD'),NULL) projfunc_tp_rate_date,
decode(i.tp_amt_type_Code,'COST_TRANSFER',decode(i.projfunc_cost_rate_type,'Us
er',i.projfunc_cost_exchange_rate,NULL),NULL) projfunc_tp_exchange_rate,
  i.tp_amt_type_code tp_amt_type_code,
  i.assignment_id assignment_id,
  pj.distribution_rule dist_rule,
  pj.multi_currency_billing_flag mcb_flag,
  i.bill_rate_multiplier bill_rate_multiplier,
  i.raw_cost raw_cost,
  t.labor_schedule_discount labor_schdl_discnt,
  to_char(t.labor_schedule_fixed_date,'YYYY/MM/DD')
labor_schdl_fixed_date,
  pj.bill_job_group_id bill_job_group_id,
  t.labor_sch_type labor_sch_type,
  pj.org_id org_id,
  i.acct_currency_code exp_func_curr_code,
  e.incurred_by_organization_id incurred_org_id,
  trim(to_char(round(i.raw_cost_rate,5))) raw_cost_rate,
  i.override_to_organization_id override_to_org_id,
  t.emp_bill_rate_schedule_id emp_bill_rate_sch_id,
  t.job_bill_rate_schedule_id job_bill_rate_sch_id,
  NULL resource_job_id ,
  acct_raw_cost exp_raw_cost ,
  pj.assign_precedes_task assignment_precedes_task ,
  i.burden_cost burdened_raw_Cost,
  pj.non_labor_schedule_discount non_labor_schdl_discnt,
  pj.non_labor_std_bill_rate_schdl non_labor_std_bill_rate_schdl,
  pj.non_labor_bill_rate_org_id non_labor_bill_rate_org_id,
  to_char(pj.non_labor_schedule_fixed_date,'YYYY/MM/DD')
non_labor_schdl_fixed_date,
  pj.non_labor_sch_type non_labor_sch_type,
  t.non_labor_schedule_discount non_labor_schdl_discnt,
  t.non_labor_std_bill_rate_schdl non_labor_std_bill_rate_schdl,
  t.non_labor_bill_rate_org_id non_labor_bill_rate_org_id,
  to_char(t.non_labor_schedule_fixed_date,'YYYY/MM/DD')
non_labor_schdl_fixed_date,
  t.non_labor_sch_type non_labor_sch_type,
  t.non_lab_std_bill_rt_sch_id,
  pj.non_lab_std_bill_rt_sch_id,
 decode(i.unit_of_measure,null,1,et.unit_of_measure,1,0),
  i.LEGAL_ENTITY_ID ,
  i.RECEIVER_LEGAL_ENTITY_ID,
  i.ADD_INV_GROUP,
  nvl(i.net_zero_adjustment_flag,'N')
 FROM pa_expenditures e,
  per_people_f p,
  hr_all_organization_units_tl oue,
  hr_all_organization_units_tl out,
  hr_all_organization_units_tl prvdr,
  hr_all_organization_units_tl recvr,
  hr_all_organization_units_tl prou,
  hr_all_organization_units_tl ouex,
  hr_all_organization_units_tl oup,
  hr_all_organization_units_tl ounlrtl,
  pa_tasks t,
  pa_projects_all pj,
  pa_project_classes pc,
  pa_expenditure_types et,
  pa_expenditure_items I,
  pa_system_linkages sl
  WHERE out.organization_id = t.carrying_out_organization_id
  AND prvdr.organization_id = i.cc_prvdr_organization_id
  AND recvr.organization_id = i.cc_recvr_organization_id
  AND ouex.organization_id = i.org_id
  AND prou. organization_id = pj.org_id
  AND oue.organization_id = NVL(i.override_to_organization_id,
  NVL(e.incurred_by_organization_id,
  pj.carrying_out_organization_id))
  AND oup.organization_id = pj.carrying_out_organization_id
  AND ounlrtl.organization_id(+) = i.organization_id
  AND i.recvr_org_id = :Precv_ous
  AND i.org_id = :Pprvdr_ous
  AND i.legal_entity_id = :Pprvdr_le_id
  AND i.receiver_legal_entity_id = :Precv_le_id
  AND i.cc_ic_processed_code IN ('N','B')
  AND i.cost_distributed_flag ='Y'
  AND i.expenditure_item_date <= to_date(:Pbill_through,'YYYY/MM/DD')
  AND e.expenditure_id = i.expenditure_id
  AND et.expenditure_type = i.expenditure_type
  AND p.person_id(+) = e.incurred_by_person_id
  AND pj.project_id = t.project_id
  AND pj.cc_tax_task_id is not null
  AND i.task_id = t.task_id
  AND pc.project_id(+) = pj.project_id
  AND oue.language ='US'
AND out.language ='US'
AND prvdr.language ='US'
AND recvr.language ='US'
AND prou.language ='US'
AND ouex.language ='US'
AND oup.language ='US'
AND ounlrtl.language(+) ='US' AND sl.function =
i.system_linkage_function
  AND pc.class_category(+) = :Paa_category
  AND ((sl.labor_non_labor_flag = 'Y'
  AND t.cc_process_labor_flag = 'Y')
  OR (sl.labor_non_labor_flag = 'N'
  AND t.cc_process_nl_flag = 'Y'))
  AND (( p.person_id > -1
  AND trunc(i.expenditure_item_date) between p.effective_start_date
  and p.effective_end_date
  AND decode(p.current_npw_flag, 'Y', p.npw_number, p.employee_number)
is not NULL)
  OR (p.person_id is NULL ))
 AND pj.project_id IN ( select bp.project_id
  from pa_projects_all bp
  where bp.allow_cross_charge_flag = 'Y'
  and bp.org_id = 121 and
not exists ( select 'x'
  from pa_draft_invoices pdi
  where pdi.project_id =7726
  and pdi.released_date is null
  and nvl(pdi.cancel_credit_memo_flag,'N') = 'N' ))
 AND i.CC_CROSS_CHARGE_CODE <> 'I' ORDER BY i.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
References


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