My Oracle Support Banner

Performance Issue In Supply/Demand Form @SOL (Doc ID 2758624.1)

Last updated on MARCH 15, 2021

Applies to:

Oracle Materials Requirement Planning - Version 12.2.9 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.9 version

ACTUAL BEHAVIOR
---------------
Find there is a performance issue in the Item Supply/Demand form. it is taking 10-15 seconds for the form to return records.

The long running SQL has been identified as:

SELECT D.RESERVATION_TYPE, DECODE(D.DEMAND_SOURCE_TYPE,2,
DECODE(D.RESERVATION_TYPE,1,2,3,23,9),8,DECODE(D.RESERVATION_TYPE,1,21,22),
D.DEMAND_SOURCE_TYPE) , DECODE(D.DEMAND_SOURCE_TYPE,8,2,
D.DEMAND_SOURCE_TYPE) , D.DEMAND_SOURCE_HEADER_ID , 1 ,
-1*(D.PRIMARY_UOM_QUANTITY-D.TOTAL_RESERVATION_QUANTITY-D.COMPLETED_QUANTIT
Y) , TO_NUMBER(TO_CHAR( decode(nvl(D.mfg_lead_time,0), 0,
C.PRIOR_DATE, decode(I.BOM_Item_Type, 1, C.PRIOR_DATE,
MRP_CALENDAR.DATE_OFFSET ( D.ORGANIZATION_ID,
1, C.PRIOR_DATE,
-1*(D.mfg_lead_time)))) , 'J')) , CHARTOROWID('0') ,
V.INVENTORY_ITEM_ID , V.ORGANIZATION_ID , DECODE(D.RESERVATION_TYPE, 2,
to_number(:sys_seq_num), C.PRIOR_SEQ_NUM) , D.DEMAND_SOURCE_NAME
FROM
MTL_GROUP_ITEM_ATPS_VIEW V, MTL_PARAMETERS P , MTL_SYSTEM_ITEMS I ,
MTL_ATP_RULES R , BOM_CALENDAR_DATES C , MRP_DEMAND_OM_RESERVATIONS_V D
...


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Navigate to the On-hand Availability>Item Supply/Demand form
2. Query for an item



Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


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