Performance Issue With Placing Orders From APS Planning System to EBS source system (Doc ID 1674804.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

VCP 12.1.3.9 Patch 16974659

PROBLEM
-------------
After applying 12.1.3.9 on EBS and APS (was on base 12.1.3 release)
Plus many patches on EBS source - we find that release new purchase req is taking two mins.
Used to take just seconds.

Below query is the culprit. It is running in R12 EBS
PO RUP patch has modified the PO_VENDOR_SITES_ALL view which is restricting it from merging to PO_REQUISITIONS_INTERFACE_ALL or pushing the predicate and doing full table scans on AP_SUPPLIER_SITES_ALL & HZ_PARTY_SITES tables.
I tried to force some hints but the OUTER JOINS are not allowing it(seems like a restriction).
If this is a custom code we could have modified it as shown below(which is completing in But since it is a seeded form, please open SR and work with ORACLE.

Original Query:
SELECT PVSA.VENDOR_SITE_ID,
  PRI.ROWID,
  PRI.ITEM_ID,
  PRI.DESTINATION_ORGANIZATION_ID,
  PRI.CHARGE_ACCOUNT_ID,
  PRI.PROJECT_ID
  FROM PO_VENDOR_SITES_ALL PVSA, PO_REQUISITIONS_INTERFACE_ALL PRI
WHERE PRI.SUGGESTED_VENDOR_ID = PVSA.VENDOR_ID(+)
  AND PRI.SUGGESTED_VENDOR_SITE = PVSA.VENDOR_SITE_CODE(+)
  AND NVL (PRI.ORG_ID, -99) = NVL (PVSA.ORG_ID(+), -99)
  AND PRI.INTERFACE_SOURCE_CODE = 'MSC'
  AND PRI.BATCH_ID = :B1;


EXPECTED BEHAVIOR
----------------------------
It will take just a few seconds for the screen to return showing results screen with Req Import request ID and number of reqs released.

BUSINESS IMPACT
----------------------
Rolled out in production and now it takes unacceptable time to complete.

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