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

Last updated on FEBRUARY 29, 2024

Applies to:

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')))

Changes

Cause

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