Performance issue Scheduled Payment Selection Report (APINVSEL) (Doc ID 1962812.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.
Performance issue Scheduled Payment Selection Report (APINVSEL)

Symptoms

On : 12.1.3 version, Accounting Issues in AP

SQL ID: cm7cd8x3vjmm4
SQLTEXT: -- For Performance Bug : 17346758    select x.payment_currency_code unused_credits_pmt_currency, sum(decode(sign(x.amount_remaining), -1, x.amount_remaining, 0) ) unused_credit_sum from ( select /*+ LEADING(aps) */ distinct ai.invoice_id, aps.payment_num, ai.payment_currency_code, aps.amount_remaining from ap_payment_schedules aps, ap_invoices ai where ai.invoice_id = aps.invoice_id and aps.payment_status_flag in ('P', 'N') and ai.payment_status_flag in ( 'P' , 'N' ) and ai.invoice_type_lookup_code in ('CREDIT', 'CREDIT MEM', 'DEBIT', 'MIXED') and aps.checkrun_id is null and exists ( select /*+ no_unnest push_subq*/ 11 from ap_selected_invoices asi, ap_invoices ai2 where ai2.invoice_id = asi.invoice_id and asi.checkrun_id = :p_checkrun_id and ai2.invoice_id = asi.invoice_id and ai.vendor_id = ai2.vendor_id and ai.party_id = ai2.party_id and ai.payment_currency_code = ai2.payment_currency_code ) and not exists ( select 'No' from ap_unselected_invoices ui where checkrun_id = :p_che ckrun_id and ui.invoice_id = aps.invoice_id and ui.payment_num = aps.payment_num ) AND DECODE (SIGN (aps.amount_remaining), - 1, aps.amount_remaining, 0)<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) ) ) x group by payment_currency_code -- Commented as part of Code Change for Performance Bug : 17346758   [https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=17346758] /* 5997733: hint Remove that was addded in 5859111 */ /*Bug6760738: replaced the base tables with the views*/ /*Bug10411606: Merged inner query with main query*/ /*Bug14167678: added index hint AP_INVOICES_N2 */ /*select x.payment_currency_code unused_credits_pmt_currency, sum(decode(sign(x.amount_remaining), -1, x.amount_remaining, 0) ) unused_credit_sum from ( select *//*+ LEADING(asi) use_NL(asi, ai2, ai) index(ai AP_INVOICES_N2) *//* distinct ai.invoice_id, aps.payment_num, ai.payment_currency_code, aps.amount_remaining from ap_paym ent_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', 'N') and ai.payment_status_flag in ( 'P' , 'N' ) -- 5859111 and ai.invoice_type_lookup_code in ('CREDIT', 'CREDIT MEM', 'DEBIT', 'MIXED') and aps.checkrun_id is null and asi.checkrun_id = :p_checkrun_id and ai2.invoice_id = asi.invoice_id and ai.vendor_id = ai2.vendor_id --5859111 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) 5859111 */ /*and not exists --5859111 ( select 'No' from ap_unselected_invoices ui where checkrun_id = :p_checkrun_id and ui.invoice_id = aps.invoice_id and ui.payment_num = aps.payment_num ) AND DECODE (SIGN (aps.amount_remaining), - 1, aps.amount_remaining, 0)<0 *//* Added for bug#10382651   [https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=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 payment_currency_code */ /* 5859111: Performance Issue */

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