R11i AP: APXINWKB - Performance Issue When Entering Invoices (Doc ID 861822.1)

Last updated on SEPTEMBER 01, 2017

Applies to:

Oracle Payables - Version 11.5.10.0 to 11.5.10.2 [Release 11.5]
Information in this document applies to any platform.
This problem can occur on any platform.
FORM:APXINWKB.FMB - Invoice Workbench

***Checked for relevance on 25-Dec-2015***

Symptoms

There is a performance problem when entering invoices. After entering Supplier number, the screen sometimes takes longer time. <Patch 5610849> was applied However the issue still persists

Other Symptoms

  1.  Invoice Entry / Inquiry by PO NUMBER for large vendor has a very slow return back
  2.  One of the below queriers appear as expensive query in the tkprof output.


SELECT COUNT(*)
FROM
AP_INVOICES AI WHERE VENDOR_ID = :B1 AND INVOICE_TYPE_LOOKUP_CODE =
'PREPAYMENT' AND EARLIEST_SETTLEMENT_DATE IS NOT NULL AND
AP_INVOICES_UTILITY_PKG.GET_PREPAY_AMOUNT_REMAINING(AI.INVOICE_ID) > 0


SELECT INVOICE_NUM
FROM
AP_INVOICES WHERE VENDOR_ID = :B5 AND VENDOR_SITE_ID = :B4 AND
INVOICE_AMOUNT = :B3 AND INVOICE_CURRENCY_CODE = :B2 AND
INVOICE_TYPE_LOOKUP_CODE = DECODE(:B1 , 'CREDIT','DEBIT', 'DEBIT','CREDIT')


SELECT SUM(DECODE(PAYMENT_STATUS_FLAG, 'Y',
DECODE(SIGN(EARLIEST_SETTLEMENT_DATE - SYSDATE), 1,0, 1), 0))
FROM
AP_INVOICES AI WHERE VENDOR_ID = :B1 AND INVOICE_TYPE_LOOKUP_CODE =
'PREPAYMENT' AND EARLIEST_SETTLEMENT_DATE IS NOT NULL AND
AP_INVOICES_UTILITY_PKG.GET_PREPAY_AMOUNT_REMAINING(AI.INVOICE_ID) > 0


SELECT PAYMENT_STATUS_FLAG,EARLIEST_SETTLEMENT_DATE,INVOICE_ID
FROM
AP_INVOICES WHERE VENDOR_ID=:B1 AND INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT'

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