Create Expense Report Job Running Very Slow Post Patching Due to apwdbapb.pls (Doc ID 1948661.1)

Last updated on AUGUST 21, 2015

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Performance issue on General Information screen.
Navigate to IExpenses > click on Create Expense Report
In General Information Page select the reimbursement currency and site click on Next and there is performance issue where costly SQL ID is 10b5d62yn1h23 and take  approximative 35 seconds to execute.

SELECT VDR.VENDOR_ID, SITE.PAYMENT_CURRENCY_CODE, C.NAME
FROM PO_VENDORS VDR, PO_VENDOR_SITES SITE, FND_CURRENCIES_VL C
WHERE SITE.VENDOR_ID = VDR.VENDOR_ID AND ((VDR.EMPLOYEE_ID = :B2 AND :B1 IS NULL) OR (:B1 IS NOT NULL AND :B1 = VDR.VENDOR_ID)) AND SYSDATE < NVL(SITE.INACTIVE_DATE, SYSDATE + 1) AND C.CURRENCY_CODE(+) = SITE.PAYMENT_CURRENCY_CODE AND UPPER(SITE.VENDOR_SITE_CODE) IN
(SELECT UPPER(MEANING)
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_CODE = :B3 AND LOOKUP_TYPE = 'HOME_OFFICE')

It is contained in the procedure GetCurrFromVendorSite in package AP_WEB_DB_AP_INT_PKG

To fetch vendor related attributes OIE uses po_vendors and po_vendor_sites, these views join with HZ tables for additional data. The join is costly when there are more than a million suppliers.

 

Changes

General Information page performs good independent of the number of supplier records, can be more than a million supplier records

Change OIE query to use ap_suppliers and ap_supplier_sites

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