R12: Performance Issue with PARGDR PRC: Generate Draft Revenue for a Range of Projects (Doc ID 1932040.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

Performance issue with PARGDR PRC: Generate Draft Revenue for a Range of Projects. Problem statements are:

SELECT /*+ LEADING(p) INDEX(p PA_PROJECTS_U2) */ p.project_id, p.segment1, p.distribution_rule,
  p.project_type,
  pc.class_code, p.carrying_out_organization_id,
  p.public_sector_flag, p.project_level_funding_flag,
  p.project_currency_code,
  p.project_bil_rate_date_code,
  p.project_bil_rate_type,
  to_char(p.project_bil_rate_date,'YYYY/MM/DD'),
  p.project_bil_exchange_rate,
  p.projfunc_currency_code,
  p.projfunc_bil_rate_date_code,
  p.projfunc_bil_rate_type,
  to_char(p.projfunc_bil_rate_date,'YYYY/MM/DD'),
  p.projfunc_bil_exchange_rate,
  p.revproc_currency_code,
  p.funding_rate_date_code,
  p.funding_rate_type,
  to_char(p.funding_rate_date,'YYYY/MM/DD'),
  p.funding_exchange_rate,
  p.multi_currency_billing_flag,
  p.assign_precedes_task,
  p.enable_top_task_customer_flag,
  p.enable_top_task_inv_mth_flag,
  nvl(p.inv_by_bill_trans_curr_flag, 'N'),
  nvl(p.revtrans_currency_type, 'PROJ_FUNC_CURR_ONLY'),
  nvl(p.date_eff_funds_consumption,'N')
  FROM pa_project_classes pc, pa_project_types t, pa_projects p
 WHERE pa_project_utils.check_prj_stus_action_allowed(p.project_status_code,'GENERATE_REV') = 'Y'
  AND p.segment1 BETWEEN :start_project_number
  AND :end_project_number
  AND t.project_type = p.project_type
  AND t.direct_flag = 'Y'
  AND pc.project_id(+) = p.project_id
  AND pc.class_category(+) = :aa_category
  AND EXISTS (SELECT NULL
  FROM pa_summary_project_fundings pf
  WHERE pf.project_id = p.project_id
  AND nvl(pf.revproc_baselined_amount, 0) != 0)
  AND ( NOT EXISTS (SELECT NULL
  FROM pa_draft_revenues r
  WHERE r.project_id = p.project_id
  AND r.released_date||'' is null
  AND r.generation_error_flag||'' =
  decode(:mass_gen, 1, 'Y', r.generation_error_flag)
  AND r.request_id != :current_request_id
  AND r.draft_revenue_num_credited IS NULL
  AND nvl(r.adjusting_revenue_flag,'N')=decode(:adj_rev_mode,1,'Y','N')
  )
  AND ( EXISTS (SELECT NULL
  FROM pa_expenditure_items_all i
  WHERE i.project_id = p.project_id
  AND i.cost_distributed_flag ||'' = 'Y'
  AND i.revenue_distributed_flag not IN
  ('Y', 'A')
  AND ( i.revenue_distributed_flag != 'N'
  OR
  ( i.revenue_distributed_flag = 'N'
  AND nvl(i.accrued_revenue,0) = 0))
  AND ( i.adjusted_expenditure_item_id is null
  OR
  ( i.adjusted_expenditure_item_id is not null
  AND EXISTS (SELECT 'x'
  FROM pa_expenditure_items_all adj1
  WHERE adj1.expenditure_item_id = i.adjusted_expenditure_item_id
  AND nvl(adj1.accrued_revenue,0) = 0
  AND adj1.revenue_distributed_flag || '' = 'N' )))
  AND i.billable_flag ||'' = 'Y'
  AND i.expenditure_item_date
    AND EXISTS ( select null from pa_tasks top, pa_tasks t
  where top.task_id = t.top_task_id
  and top.ready_to_distribute_flag ||''= 'Y'
  AND t.task_id = i.task_id)
  AND :adj_rev_mode=0
  UNION ALL
  SELECT NULL
  FROM pa_event_types vt,
  pa_events v, pa_tasks t
  WHERE t.project_id = p.project_id
  AND t.ready_to_distribute_flag ||'' = 'Y'
  AND v.project_id = t.project_id
  AND nvl(v.task_id, t.task_id) = t.task_id
  AND v.revenue_distributed_flag = 'N'
  AND nvl(v.adjusting_revenue_flag,'N')= decode(:adj_rev_mode,1,'Y','N')
  AND TRUNC(v.completion_date)
    AND (DECODE(NVL(v.bill_trans_rev_amount, 0), 0 ,
  DECODE(NVL(v.zero_revenue_amount_flag, 'N'), 'Y', 1, 0),1) = 1)
  AND NVL(revenue_hold_flag, 'N') = 'N'
  AND vt.event_type = v.event_type
  AND vt.event_type_classification ||'' IN
  ('WRITE ON', 'WRITE OFF', 'MANUAL', 'AUTOMATIC','REALIZED_GAINS',
  'REALIZED_LOSSES')
  UNION ALL
  SELECT /*+ USE_CONCAT*/
  NULL
  FROM PA_Billing_Extensions be, PA_Billing_Assignments bea
  WHERE bea.active_flag = 'Y'
  AND bea.billing_extension_id = be.billing_extension_id
  AND (bea.project_id = p.project_id
  OR bea.project_type = p.project_type
  OR bea.distribution_rule = p.distribution_rule)
  AND be.calling_process in ('Revenue','Both')
  AND (nvl(be.call_after_reg_flag, 'N') = 'Y'
  OR nvl(be.call_post_reg_flag, 'N') = 'Y')
  AND :adj_rev_mode =0
  AND be.trx_independent_flag = 'Y')))

and

select CODE_COMBINATION_ID,
enabled_flag,
nvl(to_char(start_date_active, 'J'), -1),
nvl(to_char(end_date_active, 'J'), -1),
SEGMENT6||'.'||SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT7,detail_posting_allowed_flag,summary_flag
from GL_CODE_COMBINATIONS
where CHART_OF_ACCOUNTS_ID = 11111
and SEGMENT6 in ('VAL1','VAL2')
order by SEGMENT6, SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5, SEGMENT7

Cause

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