11i PAIGEN- PRC: Generate Draft Invoices For A Single Project -- Performance (Doc ID 1328790.1)

Last updated on JANUARY 16, 2015

Applies to:

Oracle Project Billing - Version 11.5.10.2 and later
Information in this document applies to any platform.
*** Checked for relevance on 16-JAN-2015 ***

PAIGEN_SINGLE - PRC: Generate Draft Invoices for a Single Project
PAIGEN - PRC: Generate Draft Invoices for a Range of Projects


Symptoms

The PAIGEN PRC: Generate Draft Invoices for a Single Project process has slow performance even when running for a single project.  This most expensive query is:

select distinct a.agreement_id ,a.customer_id ,nvl(cp1.credit_hold,
cp.credit_hold) ,a.invoice_limit_flag ,to_char(c.customer_bill_split) ,0 ,
decode(greatest(a.expiration_date,:b0),:b0,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 ,
ra_customers rc ,ar_customer_profiles cp ,ar_customer_profiles cp1 ,
ra_site_uses ras ,ra_customers rc1 where (((((((((((((((((a.agreement_id=
p.agreement_id and p.invproc_baselined_amount>0) and p.project_id=:b2) and
p.project_id=c.project_id) and a.customer_id=c.customer_id) and
c.bill_to_customer_id=cp.customer_id) and c.bill_to_customer_id=
rc1.customer_id) and NVL(rc1.status,'A')='A') and c.customer_id=
rc.customer_id) and c.customer_bill_split<>0) and nvl(rc.status,'A')='A')
and cp.site_use_id is null ) and ras.address_id=c.bill_to_address_id) and
(ras.site_use_code||'')='BILL_TO') and (ras.status||'')='A') and
cp1.site_use_id(+)=ras.site_use_id) and exists (select null from
RA_Addresses raa where (raa.address_id=C.BILL_TO_ADDRESS_ID and
nvl(raa.status,'I')='A'))) and ((exists (select null from
pa_cust_event_rev_dist_lines dl1 ,pa_cust_event_rev_dist_lines dl2 ,
pa_draft_revenues r ,pa_events e ,pa_tasks t ,pa_event_types et ,
pa_draft_invoices inv where ((((((((((((((((((((((dl1.project_id=
p.project_id and dl1.draft_revenue_num=r.draft_revenue_num) and
dl1.project_id=r.project_id) and r.generation_error_flag='N') and
dl1.project_id=e.project_id) and dl1.event_num=e.event_num) and
nvl(dl1.task_id,(-1))=nvl(e.task_id,(-1))) and e.event_type=et.event_type)
and e.completion_date<=to_date(:b0,'DD-MON-RR')) and e.project_id=
t.project_id) and nvl(e.task_id,t.task_id)=t.task_id) and
nvl(e.bill_hold_flag,'N')='N') and t.ready_to_bill_flag='Y') and
(et.event_type_classification||'')='WRITE ON') and dl1.project_id=
dl2.project_id) and nvl(dl1.task_id,(-1))=nvl(dl2.task_id,(-1))) and
dl1.event_num=dl2.event_num) and dl1.line_num_reversed=dl2.line_num) and
dl1.draft_invoice_num is null ) and dl2.draft_invoice_num is not null )
and inv.draft_invoice_num=dl2.draft_invoice_num) and inv.project_id=
dl2.project_id) and (inv.agreement_id+0)=a.agreement_id)) or exists (select
/*+ LEADING (E) +*/ null from pa_expenditure_items_all e ,pa_tasks t1
where (((((((((((e.project_id=p.project_id and t1.project_id=p.project_id)
and e.task_id=t1.task_id) and exists (select 1 from pa_projects proj where
(proj.project_id=p.project_id and substr(proj.distribution_rule,
(instr(proj.distribution_rule,'/',1)+1),length(proj.distribution_rule))
<>'EVENT'))) and e.bill_hold_flag='N') and e.event_num is null ) and
e.expenditure_item_date<=to_date(:b0,'DD-MON-RR')) and exists (select 1 
from pa_tasks t2 where (t2.task_id=t1.top_task_id and t2.ready_to_bill_flag=
'Y'))) and exists (select 1 from pa_cust_rev_dist_lines rdl1 where
((((((e.expenditure_item_id=rdl1.expenditure_item_id and
nvl(rdl1.function_code,'OTHER') not in ('URB','URL','LRB','LRL')) and
nvl(rdl1.additional_revenue_flag,'N')='N') and
nvl(rdl1.invoice_eligible_flag,'N')='N') and rdl1.draft_invoice_num is null
) and exists (select 1 from pa_draft_revenues dr where
((rdl1.draft_revenue_num=dr.draft_revenue_num and rdl1.project_id=
dr.project_id) and dr.generation_error_flag='N'))) and exists (select null 
from pa_cust_rev_dist_lines rdl2 where (rdl2.expenditure_item_id=
rdl1.expenditure_item_id and rdl2.line_num=(select max(rdl5.line_num) from
pa_draft_invoices inv ,pa_draft_invoice_items item ,pa_cust_rev_dist_lines
rdl5 where (((((((((rdl5.line_num<=rdl1.line_num_reversed and
rdl5.expenditure_item_id=rdl1.expenditure_item_id) and rdl5.function_code=
rdl1.function_code) and rdl5.draft_invoice_num=inv.draft_invoice_num) and
rdl5.project_id=inv.project_id) and (inv.agreement_id+0)=a.agreement_id)
and inv.draft_invoice_num_credited is null ) and item.project_id=
inv.project_id) and item.draft_invoice_num=inv.draft_invoice_num) and
item.invoice_line_type<>'NET ZERO ADJUSTMENT'))))) having
sum(nvl(rdl1.bill_trans_bill_amount,0))<>0)) and not exists (select /*+ 
LEADING(e2) +*/ null from pa_cust_rev_dist_lines rdl4 ,
pa_expenditure_items_all e2 where (((e2.adjusted_expenditure_item_id=
e.expenditure_item_id and rdl4.expenditure_item_id in
(e2.expenditure_item_id,e2.adjusted_expenditure_item_id)) and
rdl4.draft_invoice_num is not null ) and (rdl4.project_id+0)=:b2) having
(sum(decode(rdl4.expenditure_item_id,e2.adjusted_expenditure_item_id,
nvl(rdl4.bill_trans_bill_amount,0),0))+sum(decode(rdl4.expenditure_item_id,
e2.expenditure_item_id,nvl(rdl4.bill_trans_bill_amount,0),0)))=0)) and not
exists (select /*+ index (rdl6 PA_CUST_REV_DIST_LINES_U1) +*/ null from
PA_CUST_REV_DIST_LINES rdl6 where ((rdl6.expenditure_item_id=
e.expenditure_item_id and rdl6.DRAFT_INVOICE_NUM is null ) and not exists
(select null from pa_expenditure_items_all e4 where
e4.adjusted_expenditure_item_id=e.expenditure_item_id)) having
sum(nvl(rdl6.bill_trans_bill_amount,0))=0)) and not exists (select /*+ 
index (rdl3 PA_CUST_REV_DIST_LINES_U1) +*/ null from
PA_CUST_REV_DIST_LINES rdl3 ,pa_expenditure_items_all e3 where
((e3.adjusted_expenditure_item_id=e.expenditure_item_id and
rdl3.expenditure_item_id in (e3.expenditure_item_id,
e3.adjusted_expenditure_item_id)) and rdl3.DRAFT_INVOICE_NUM is null )
having sum(nvl(rdl3.bill_trans_bill_amount,0))=0)))) or exists (select /*+
LEADING(rdl) INDEX(rdl PA_CUST_REV_DIST_LINES_N2) +*/ null from
pa_draft_invoices inv ,pa_draft_invoice_items item ,pa_draft_revenues r ,
pa_tasks t2 ,pa_tasks t1 ,pa_expenditures_all ex ,pa_cust_rev_dist_lines
rdl2 ,pa_expenditure_items_all i ,pa_cust_rev_dist_lines rdl where
(((((((((((((((((((((((((((((((rdl.project_id=p.project_id and
i.bill_hold_flag='N') and i.event_num is null ) and
(i.revenue_distributed_flag||'') in ('Y','P')) and i.task_id=t1.task_id)
and t1.top_task_id=t2.task_id) and i.expenditure_id=ex.expenditure_id) and
(ex.expenditure_status_code||'')='APPROVED') and t2.ready_to_bill_flag='Y')
and rdl.draft_revenue_num=r.draft_revenue_num) and rdl.project_id=
r.project_id) and nvl(rdl.function_code,'OTHER') not in ('LRB','LRL','URB',
'URL')) and nvl(rdl.additional_revenue_flag,'N')='N') and
nvl(rdl.invoice_eligible_flag,'N')='N') and r.generation_error_flag='N')
and rdl.expenditure_item_id=i.expenditure_item_id) and
rdl2.expenditure_item_id=i.adjusted_expenditure_item_id) and
rdl2.project_id=inv.project_id) and nvl(rdl2.reversed_flag,'N')='N') and
rdl2.draft_invoice_num=inv.draft_invoice_num) and rdl2.line_num_reversed is
null ) and inv.project_id=item.project_id) and inv.draft_invoice_num=
item.draft_invoice_num) and rdl2.draft_invoice_item_line_num=item.line_num)
and a.agreement_id=(inv.agreement_id+0)) and i.expenditure_item_date<=
to_date(:b0,'DD-MON-RR')) and rdl.draft_invoice_num is null ) and
rdl.line_num_reversed is null ) and nvl(rdl.reversed_flag,'N')='N') and 
not exists (select null from pa_cust_rev_dist_lines rdl3 ,
pa_draft_invoices x ,pa_draft_invoice_items x2 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=
inv.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 ) 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),ITEM.TEXT having sum(RDL.bill_trans_bill_amount)<>0)))

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