R11i PAIGEN PRC: Generate Draft Invoices For a Single Project Performance Is Slow Taking 1.5 - 2 Hours (Doc ID 1447490.1)

Last updated on AUGUST 01, 2016

Applies to:

Oracle Project Billing - Version 11.5.10.2 and later
Information in this document applies to any platform.

Symptoms

The PAIGEN PRC: Generate Draft Invoices process has slow performance and can take up to two hours to generate invoices for a single project. The 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(a.expiration_date,
'YYYY-MM-DD') ,Decode(:b0,'Y',100,c.customer_bill_split) ,p.task_id ,
decode(greatest(nvl(a.expiration_date,(to_date(:b1,'DD-MON-RR')+1)),
to_date(:b1,'DD-MON-RR')),to_date(:b1,'DD-MON-RR'),1,0) ,
to_char((nvl(p.invproc_baselined_amount,0)-nvl(p.invproc_billed_amount,0)))
,c.bill_to_address_id ,c.ship_to_address_id ,ras.site_use_id ,
ras1.site_use_id ,addr.language ,a.agreement_currency_code ,
c.bill_to_customer_id ,c.ship_to_customer_id
from
ar_customer_profiles cp1 ,ra_customers rc ,ar_customer_profiles cp ,
ra_site_uses ras ,pa_project_customers c ,pa_agreements_all a ,
pa_summary_project_fundings p ,pa_projects pr ,ra_site_uses ras1 ,
ra_addresses addr ,ra_customers rc1 ,pa_tasks t where
(((((((((((((((((((((((((a.agreement_id=p.agreement_id and
p.invproc_baselined_amount>0) and p.project_id=pr.project_id) and
pr.project_id=:b4) 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 t.task_id=p.task_id) and Decode(:b0,'Y',
100,c.customer_bill_split)<>0) and Decode(:b0,'Y',t.Customer_ID,
c.customer_id)=c.Customer_ID) 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
ras1.address_id=c.ship_to_address_id) and (ras1.site_use_code||'')=
'SHIP_TO') and (ras1.status||'')='A') and addr.address_id=
c.bill_to_address_id) and nvl(addr.status,'I')='A') and cp1.site_use_id(+)=
ras.site_use_id) and exists (select null from RA_Addresses raa1 where
(raa1.address_id=C.SHIP_TO_ADDRESS_ID and nvl(raa1.status,'I')='A'))) and
((exists (select /*+ LEADING (et) USE_NL(et, e) INDEX(e PA_EVENTS_N2)
+*/ null from pa_cust_event_rev_dist_lines dl2 ,pa_draft_revenues r ,
pa_tasks t ,pa_event_types et ,pa_events e ,pa_cust_event_rev_dist_lines
dl1 where ((((((((((((((((((((((((dl1.project_id=pr.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 e.project_id=pr.project_id) and
dl1.project_id=e.project_id) and dl1.event_num=e.event_num) and dl1.task_id=
e.task_id) and nvl(e.bill_hold_flag,'N')='N') and e.event_type=
et.event_type) and e.completion_date<=:b1) and e.bill_trans_bill_amount<>0)
and et.event_type_classification='WRITE ON') and e.project_id=t.project_id)
and e.task_id=t.task_id) and (t.top_task_id+0)=p.task_id) and
t.ready_to_bill_flag='Y') and dl1.project_id=dl2.project_id(+)) and
dl1.task_id=dl2.task_id(+)) and dl1.event_num=dl2.event_num(+)) and
dl1.line_num_reversed=dl2.line_num(+)) and dl2.draft_invoice_num(+) is null
) and dl1.draft_invoice_num is null ) and (dl1.line_num_reversed is null
or dl2.event_num is not null )) and nvl(e.request_id,0)=decode(:b8,1,:b9,
nvl(e.request_id,0)))) or exists (select /*+ LEADING(l) INDEX(l
PA_DRAFT_INVOICE_ITEMS_N2) USE_NL(l, e) +*/ null from pa_tasks ta ,
pa_event_types et ,pa_events e ,pa_draft_invoice_items l ,pa_draft_invoices
i ,pa_agreements_all agree where (((((((((((((((((l.project_id=
pr.project_id and (l.task_id+0)=p.task_id) and ta.ready_to_bill_flag='Y')
and ta.project_id=e.project_id) and ta.task_id=e.task_id) and e.project_id=
l.project_id) and e.task_id=l.event_task_id) and e.event_num=l.event_num)
and l.event_num is not null ) and l.draft_invoice_num=i.draft_invoice_num)
and l.project_id=i.project_id) and e.event_type=et.event_type) and
et.event_type_classification<>'WRITE ON') and nvl(e.bill_hold_flag,'N')='N')
and e.completion_date<=:b1) and agree.agreement_id=i.agreement_id) and
(agree.agreement_id+0)=a.agreement_id) and nvl(e.request_id,0)=decode(:b8,1,
:b9,nvl(e.request_id,0))) group by agree.customer_id,l.event_task_id,
l.event_num having sum(l.amount)=0)) or ((pr.distribution_rule not in
('COST/EVENT','WORK/EVENT','EVENT/EVENT') and exists (select null from
pa_expenditures_all ex ,pa_cust_rev_dist_lines rdl1 ,
pa_expenditure_items_all e ,pa_tasks t2 ,pa_tasks t where
((((((((((((((((((e.bill_hold_flag='N' and e.event_num is null ) and
(e.revenue_distributed_flag||'') in ('Y','P')) and e.expenditure_item_date<=
:b1) and e.task_id=t.task_id) and nvl(e.net_zero_adjustment_flag,'N')='N')
and t.top_task_id=t2.task_id) and t2.ready_to_bill_flag='Y') and
t.top_task_id=p.task_id) and e.expenditure_id=ex.expenditure_id) and
(ex.expenditure_status_code||'')='APPROVED') and e.expenditure_item_id=
rdl1.expenditure_item_id) and rdl1.draft_invoice_num is null ) and
rdl1.function_code not in ('LRB','LRL','URB','URL')) and
nvl(rdl1.additional_revenue_flag,'N')='N') and
nvl(rdl1.invoice_eligible_flag,'N')='N') and rdl1.line_num_reversed is null
) and rdl1.reversed_flag is null ) and nvl(e.request_id,0)=decode(:b8,1,:b9,
nvl(e.request_id,0))))) and exists (select null from pa_draft_revenues dr
where (dr.project_id=p.project_id and dr.generation_error_flag='N')))))
order by a.invoice_limit_flag desc ,to_char(a.expiration_date,'YYYY-MM-DD'),
(nvl(p.invproc_baselined_amount,0)-nvl(p.invproc_billed_amount,0)) desc ,
a.agreement_id

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