Performance issue with Scheduled Payment Selection Report (APINVSEL) (Doc ID 1475631.1)

Last updated on NOVEMBER 16, 2016

Applies to:

Oracle Payables - Version 12.1.1 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.
Performance issue with Scheduled Payment Selection Report (APINVSEL)

Symptoms

ACTUAL BEHAVIOR
---------------
Scheduled Payment Selection Report ( APINVSEL ) does not finish. It had to be terminated by the user after 21 hours.

The issue can be reproduced at will with the following steps:
1. Run the Scheduled Payment Selection Report
2. The following SQL statements are expensive.

  (SQL1)

  select /*+ LEADING(asi) use_NL(asi, ai2, ai) */ distinct
         ai.payment_currency_code unused_prepays_pmt_cur ,
         hr.name unused_prepays_org_name,
         AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining(ai.invoice_id)
  unapplied_prepays,
         /* hz.party_name, Commented for bug#10382651 */
         DECODE (ap.vendor_type_lookup_code, 'EMPLOYEE', ap.vendor_name,
  hz.party_name) party_name,
  --   hzps.party_site_name,   Bug 6603397
         decode(ai.invoice_type_lookup_code,
                   'PAYMENT REQUEST' , hzps.party_site_name,
                    ass.vendor_site_code)  party_site_name3,       -- Added
  for Bug 6603397
         ai.invoice_num,
         ai.earliest_settlement_date,
         ps.payment_num
  from ap_payment_schedules ps,
       ap_invoices ai,
       ap_selected_invoices asi,
       ap_invoices ai2,
       ap_supplier_sites ass,
       hz_parties hz,
       hz_party_sites hzps ,  
       hr_operating_units hr,
       ap_suppliers ap
    where ai.invoice_id = ps.invoice_id and hr.organization_id = ai.org_id and
  ass.vendor_site_id (+) = ai.vendor_site_id and ai.party_id = hz.party_id AND
  ap.party_id (+) = hz.party_id and ai.party_site_id = hzps.party_site_id (+)
  and ps.payment_status_flag in ( 'P' , 'Y' ) and ai.invoice_type_lookup_code
  = 'PREPAYMENT' and ai.earliest_settlement_date is not null and
  ps.checkrun_id is null and asi.checkrun_id = : p_checkrun_id and
  ai2.invoice_id = asi.invoice_id and ai2.vendor_id = ai.vendor_id and
  ai.party_id = ai2.party_id and ai.payment_currency_code =
  ai2.payment_currency_code and ( ps.invoice_id , ps.payment_num ) not in (
  select invoice_id , payment_num from ap_unselected_invoices where
  checkrun_id = : p_checkrun_id ) and
  AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining ( ai.invoice_id ) > 0
  AND ( EXISTS ( SELECT 1 FROM AP_OU_GROUP aog WHERE checkrun_id = :
  p_checkrun_id AND ai.org_id = aog.org_id ) OR NOT EXISTS ( SELECT 1 FROM
  AP_OU_GROUP WHERE checkrun_id = : p_checkrun_id ) ) ORDER BY 2 ASC,1 ASC
  
  (SQL2)
  /*Bug6760738: replaced the base tables with the views*/
  /*Bug10411606: Merged inner query with main query*/
  select x.payment_currency_code unused_prepay_pmt_currency,
         sum(AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining
  (x.invoice_id) )unapplied_prepays_sum
  from
  (
  select /*+ LEADING(asi) use_NL(asi, ai2, ai) */ distinct
     ai.invoice_id, aps.payment_num, ai.payment_currency_code
  from ap_payment_schedules aps,
          ap_invoices ai,
          ap_selected_invoices asi,
          ap_invoices ai2
  where ai.invoice_id = aps.invoice_id
  and aps.payment_status_flag in ('P','Y')
  and ai.invoice_type_lookup_code = 'PREPAYMENT'
  and ai.earliest_settlement_date is not null
  and aps.checkrun_id is null
  and asi.checkrun_id = :p_checkrun_id
  and ai2.invoice_id = asi.invoice_id
  and ai2.vendor_id = ai.vendor_id     /*5997733 */
  and ai.party_id = ai2.party_id
  and ai.payment_currency_code = ai2.payment_currency_code
  and (aps.invoice_id, aps.payment_num) not in
    (select invoice_id, payment_num
      from ap_unselected_invoices
      where checkrun_id = :p_checkrun_id)
  AND AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining (ai.invoice_id)>0 /*
  Added for bug#10382651 */
     /* Added for bug#11656872 Start */
     AND ( EXISTS (SELECT 1 FROM AP_OU_GROUP aog WHERE checkrun_id =
  :p_checkrun_id AND ai.org_id = aog.org_id)
        OR NOT EXISTS (SELECT 1 FROM AP_OU_GROUP WHERE checkrun_id =
  :p_checkrun_id)
         )    
     /* Added for bug#11656872 End */
  ) x group by x.payment_currency_code

Changes

 None

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