R12 - Performance Issue With PARGDR PRC: Generate Draft Revenue For A Range Of Projects (Doc ID 1561991.1)

Last updated on MAY 22, 2017

Applies to:

Oracle Project Billing - Version 12.1.3 and later
Information in this document applies to any platform.
Reviewed for currency on 04-Dec-2015

Symptoms

The PARGDR: PRC: Generate Draft Revenue for a Range of Projects or PRC: Generate Draft Revenue for A Single Project performance is poor and does not complete even after 24 hours.

The most expensive queries are:


update pa_events v set v.revenue_distributed_flag='D',
  v.rev_dist_rejection_code=null ,v.request_id=:b0,v.program_id=:b1,
  v.program_application_id=:b2,v.program_update_date=sysdate,
  v.last_update_date=sysdate
where
 ((((((((v.revenue_distributed_flag not in ('Y','A') and v.project_id=:b3)
  and (v.task_id is null or exists (select decode(v.task_id,null ,(-1),
  t.task_id) from pa_tasks t where ((t.project_id=:b3 and t.task_id=
  v.task_id) and (t.ready_to_distribute_flag||'')='Y')))) and
  trunc(v.completion_date)<=trunc(NVL(TO_DATE(:b5,'YYYY/MM/DD'),sysdate)))
  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 v.calling_place=:b6) and exists (select
  vt.event_type from pa_event_types vt where (vt.event_type=v.event_type and
  (vt.event_type_classification||'')='AUTOMATIC'))) and
  ((v.calling_process||'')='Revenue' or ((v.calling_process||'')='Invoice'
  and exists (select 'Invoice is released' from pa_draft_invoice_items drii ,
  pa_draft_invoices dri where (((((drii.project_id=:b3 and
  nvl(drii.event_task_id,(-1))=nvl(v.task_id,(-1))) and drii.event_num=
  v.event_num) and dri.project_id=drii.project_id) and dri.draft_invoice_num=
  drii.draft_invoice_num) and dri.released_date is not null )))))


update pa_events set request_id=:b0
where
 (((((project_id=:b1 and nvl(revenue_distributed_Flag,'N')='N') and
  nvl(bill_trans_rev_amount,0)<>0) and completion_date<=TRUNC(NVL(TO_DATE(:b2,
  'YYYY/MM/DD'),sysdate))) and event_num_reversed is not null ) and exists
  (select 1 from pa_event_types where (event_type=pa_events.event_type and
  event_type_classification='AUTOMATIC')))

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms