My Oracle Support Banner

PAIGEN: Performance Issue With PRC: Generate Draft Invoices For A Single Project (Doc ID 2044710.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

Since upgrade to R12, the PRC: Generate Draft Invoice for a Single Project process has performance issues with the following query:

select  /*+  leading(c,p)  +*/ distinct a.agreement_id ,a.customer_id ,
  DECODE(cp1.credit_hold,null ,cp.credit_hold,'N',cp.credit_hold,
  cp1.credit_hold) ,a.invoice_limit_flag ,to_char(Decode(:b0,'Y',100,
  c.customer_bill_split)) ,p.task_id ,decode(greatest(a.expiration_date,
  to_date(:b1,'YYYY/MM/DD')),to_date(:b1,'YYYY/MM/DD'),1,0) ,
  to_char((nvl(p.invproc_baselined_amount,0)-nvl(p.invproc_billed_amount,0)))

  ,c.bill_to_customer_id ,c.ship_to_customer_id  
from
 pa_agreements_all a ,pa_summary_project_fundings p ,pa_project_customers c ,
  hz_cust_accounts hz_c ,hz_customer_profiles cp ,hz_customer_profiles cp1 ,
  hz_cust_site_uses hz_site ,hz_cust_accounts hz_c1 ,pa_tasks t ,pa_projects
  pr where (((((((((((((((((((a.agreement_id=p.agreement_id and p.project_id=
  :b3) and p.project_id=c.project_id) and pr.project_id=p.project_id) and
  a.customer_id=c.customer_id) and c.bill_to_customer_id=cp.cust_account_id)
  and c.bill_to_customer_id=hz_c1.cust_account_id) and nvl(hz_c1.status,'A')=
  'A') and c.customer_id=hz_c.cust_account_id) and Decode(:b0,'Y',100,
  Decode(:b5,1,100,NVL(c.customer_bill_split,0)))<>0) and
...

  where (((((((((rdl3.expenditure_item_id=rdl2.expenditure_item_id and
  rdl3.line_num_reversed=rdl2.line_num) and (rdl3.project_id+0)=
  inv.project_id) and rdl3.draft_invoice_num=x.draft_invoice_num) and
  rdl3.draft_invoice_item_line_num=x2.line_num) and x.project_id=
  rdl3.project_id) and x.draft_invoice_num_credited=inv.draft_invoice_num)
  and x.project_id=x2.project_id) and x.draft_invoice_num=
  x2.draft_invoice_num) and x2.draft_inv_line_num_credited=item.line_num)))
  and  not exists (select null   from pa_cust_rev_dist_lines z where
  ((z.expenditure_item_id=i.expenditure_item_id and z.draft_invoice_num is
  null ) and nvl(:b9,'N')='N') group by z.expenditure_item_id having
  sum(z.bill_trans_bill_amount)=0)) and  not exists (select null   from
  pa_cust_rev_dist_lines x where ((x.expenditure_item_id=
  i.adjusted_expenditure_item_id and nvl(x.function_code,'OTHER') not  in
  ('UBR','URL','LRB','LRL')) and x.draft_invoice_num is null ) group by
  x.expenditure_item_id having sum(x.bill_trans_bill_amount)<>0)) group by
  INV.DRAFT_INVOICE_NUM,ITEM.LINE_NUM,NVL(ITEM.TASK_ID,0),
  NVL(ITEM.EVENT_TASK_ID,0),NVL(ITEM.EVENT_NUM,0),3,ITEM.TEXT having
  decode(:b9,'Y',1,sum(RDL.bill_trans_bill_amount))<>0))))

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.