Last updated on AUGUST 21, 2015
Applies to:Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.
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
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.
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
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms