R12: PAIGEN: Performance Issue With PRC: Generate Draft Invoices For A Single Project (Doc ID 2044710.1)

Last updated on FEBRUARY 29, 2024

Applies to:

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

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.