Custom ATP API Calls and Scheduling/Re-Scheduling Via Schedule Orders Program Is Taking Too Much Time With Very High Volume And Allocated ATP (Doc ID 2247503.1)

Last updated on MARCH 23, 2017

Applies to:

Oracle Global Order Promising - Version 12.1.3.9.1 and later
Information in this document applies to any platform.

Symptoms

12.1.3.9.1 and above

PROBLEM
-------------
Customer calls ATP API to process sales order in custom request

OR

Customer uses Schedule Orders Program and reschedules large number of Order Lines


This API was observed to make many calls to SQL ID - 8s02y39axx16w

SELECT DISTINCT TIME_PHASE_ID, ALLOCATION_RULE_NAME
FROM
 MSC_ITEM_HIERARCHY_MV WHERE INVENTORY_ITEM_ID = :B4 AND ORGANIZATION_ID =
  :B3 AND SR_INSTANCE_ID = :B2 AND GREATEST(:B1 ,TRUNC(SYSDATE)) BETWEEN
  EFFECTIVE_DATE AND DISABLE_DATE

After long investigation and review it appeared from internal testing that this happened when they did not submit the order line_id to the process,
So we had them implement that change to make sure this was targeted call to ATP API not just item in general but for a specific line_id

After this change was implemented - we see that most calls to API are not generating many calls to this SQL ID
and it runs very very quickly
But sometimes it does execute this same query many.many times

And when sending 140K lines to the API and you see 11+ million executions, then it takes excessive time.


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