Performance Issue With Payment Workbench APXPAWKB.fmx

(Doc ID 758677.1)

Last updated on APRIL 14, 2014

Applies to:

Oracle Payables - Version 12.0.0 to 12.0.6 [Release 12.0]
Information in this document applies to any platform.
This problem can occur on any platform.
$Header: APXPAWKB.fmb 120.183.12000000.84 2008/12/10 20:43 mkmeda ship

Symptoms

When custmer clicks on Invoices LOV on APXPAWKB.fmx it takes time for the LOV to return values.

Performance Issue with the following SQL statement:
====================================
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,
apbk.bank_account_num external_bank_account_num,apbk.bank_account_name
external_bank_account_name
from
ap_invoices_ready_to_pay_v v, ap_bank_accounts_all apbk where v.party_id =
:6 and ((:7 = 'M') or (:8 = 'R' and v.invoice_type in ('CREDIT','STANDARD',
'DEBIT','EXPENSE REPORT','MIXED','AWT')) or /*Bug5948003, Bug6069211*/ (:9 =
'Q' and (v.vendor_site_id = :10 or v.invoice_type = 'PAYMENT REQUEST') and
((:11 = 'TRUE' and :12 = '1') or (NVL(v.exclusive_payment_flag,'N') = 'N'
and nvl(:13, 'N') = 'N')))) and v.currency_code = :14 and
v.payment_method_code = :15 and nvl(v.payment_function, 'PAYABLES_DISB') =
nvl(:16, 'PAYABLES_DISB') and v.set_of_books_id = :17 and
nvl(v.future_dated_payment_ccid, -1) = DECODE(:18,'Y',nvl(:19,-1),
nvl(v.future_dated_payment_ccid,-1)) and v.external_bank_account_id =
apbk.bank_account_id(+) order by upper(invoice_num)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.16 0.15 1 284 0 0
Fetch 1 70.48 76.26 2 2937951 0 77
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 70.65 76.41 3 2938235 0 77

Changes

Performance Issue started after applying  <Patch 7612785>

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