My Oracle Support Banner

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

Last updated on MARCH 08, 2017

Applies to:

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

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 = 50390
and SEGMENT6 in ('652240','652400')
order by SEGMENT6, SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5, SEGMENT7

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


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.