R12: AP: Performance Issue With Payments Form (APXPAWKB) (Doc ID 2117534.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Payables - Version 12.2.4 and later
Information in this document applies to any platform.
R12: AP: Performance Issue With Payments Form (APXPAWKB)

Symptoms

On : 12.2.4 version, Other-MiscIss & KeyIndReport

SQL ID: 4cfc8jjwadb6f Plan Hash: 801705594

select v.invoice_num,v.invoice_id,v.invoice_type, v.pay_alone,
 v.exclusive_payment_flag, v.payment_num, v.amount_remaining,
 to_char(v.amount_remaining,fnd_currency.get_format_mask(v.currency_code,42))
  char_amount_remaining,
 ap_payment_schedules_pkg.get_discount_available(v.invoice_id, v.payment_num,
 :1,:2) discount_available,
 to_char(ap_payment_schedules_pkg.get_discount_available(v.invoice_id,
 v.payment_num, :3,:4),fnd_currency.get_format_mask(v.currency_code,42))
 char_discount_available,
 ap_payment_schedules_pkg.get_discount_date(v.invoice_id, v.payment_num, :5)
 disc_date,v.always_take_disc_flag, v.discount_amount_available,
 v.discount_date,v.second_discount_date,v.second_disc_amt_available,
 v.third_discount_date,v.third_disc_amt_available,v.gross_amount,
 v.description, v.accts_pay_code_combi_id,v.due_date,
 v.REMIT_TO_SUPPLIER_NAME, v.REMIT_TO_SUPPLIER_ID, v.REMIT_TO_SUPPLIER_SITE,
 v.REMIT_TO_SUPPLIER_SITE_ID, v.RELATIONSHIP_ID,v.external_bank_account_id,
 ieba.bank_account_num external_bank_account_num,ieba.bank_account_name
 external_bank_account_name
from
ap_invoices_ready_to_pay_v v, iby_ext_bank_accounts ieba where (
 UPPER(INVOICE_NUM) LIKE :6 AND (INVOICE_NUM LIKE :7 OR INVOICE_NUM LIKE :8
 OR INVOICE_NUM LIKE :9 OR INVOICE_NUM LIKE :10)) AND ( v.party_id = :11 /*
 and v.invoice_num like :adj_inv_pay.invoice_num||'%' */ and ((:12 = 'M') or
 (:13 = 'R' and v.invoice_type in ('CREDIT','STANDARD', 'DEBIT','EXPENSE
 REPORT','MIXED','AWT')) or /*Bug5948003, Bug6069211*/ (:14 = 'Q' and
 (v.vendor_site_id = :15 or v.invoice_type = 'PAYMENT REQUEST') and ((:16 =
 'TRUE' and :17 = '1') or (NVL(v.exclusive_payment_flag,'N') = 'N' and
 nvl(:18, 'N') = 'N')))) and v.currency_code = :19 and v.payment_method_code
 = :20 and nvl(v.payment_function, 'PAYABLES_DISB') = nvl(:21,
 'PAYABLES_DISB') and v.set_of_books_id = :22 and
 nvl(v.future_dated_payment_ccid, -1) = DECODE(:23,'Y',nvl(:24,-1),
 nvl(v.future_dated_payment_ccid,-1)) and v.external_bank_account_id =
 ieba.ext_bank_account_id(+) ) order by upper(invoice_num)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      1.32       1.32          0          0          0           0
Fetch        2     24.45      24.47          5     228626          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6     25.77      25.80          5     228626          0           2


The issue can be reproduced at will with the following steps:

1. Navigate to Payables Responsibility.
2. Oracle Payables->Payments->Entry ->Payments
3. Enter Supplier Number
4. Enter Payment Date and Bank account
5. Enter Payment Process Profile
6. Click on Enter/Adjust Invoices
7. Give invoice number in INVOICE NUMBER column in Select invoices form and tab out.
8. Performance issue can be seen as it takes 2 minutes to fetch invoice details.



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