Statement Generation Process Has Poor Performance After Upgrading to Financials Family Pack G, 11i.AR.O

Last updated on JULY 31, 2017

Applies to:

Symptoms

Statement Generation Process has Performance Issues after upgrading to Financials Family Pack G, 11i.AR.O
- The process used to complete in 6 hours when executed for all customers.
- After Upgrading to Financials Family Pack G, 11i.AR.O, the process executes for 12 hours
- Poor Performing code identified in the trace process:

insert into ar_statement_headers(line_cluster_id,send_to_address_id,
request_id,site_use_id,bill_to_location,statement_type,customer_id,
remit_to_address_id,statement_date,currency_code,bucket1,bucket2,bucket3,
bucket4,bucket5,finance_charges,total_amount_due,error_no_send_to,
error_amount_different,error_no_remit_to,error_no_lines)select 0 ,
min(send_to_address_id) ,:b0 ,(-1) ,:b1 ,'ONACC_UNAPP' ,min(ps.customer_id)
,(-1) ,TO_DATE(:b2,'DD-MON-YYYY') ,min(ps.invoice_currency_code) ,0 ,0 ,0 ,
0 ,0 ,0 ,0 ,'N' ,'N' ,'N' ,'N' from ar_statement_headers sh ,
ar_payment_schedules ps where (((((((sh.statement_type='STMTS' and
sh.customer_id=ps.customer_id) and sh.currency_code=
ps.invoice_currency_code) and sh.request_id=:b0) and ps.actual_date_closed>=
TO_DATE(:b4,'DD-MON-YYYY')) and ps.trx_date<=TO_DATE(:b2,'DD-MON-YYYY'))
and (ps.class||'')='PMT') and ps.customer_site_use_id is null ) group by
sh.currency_code,sh.customer_id

Changes

Recently Upgraded to Financials Family Pack G, 11i.AR.O

Cause