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 JUNE 01, 2023

Applies to:

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 - <SQL_ID>

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.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.