Item Supply Demand Form Performance Issue (INVDVDSD form) (Doc ID 2097625.1)

Last updated on JUNE 28, 2017

Applies to:

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

Symptoms

Product Version: 12.1.3.9.1

ACTUAL BEHAVIOR
---------------
Users are experiencing performance issues in the Supply/Demand form. It is taking several minutes (2-5min) to retrieve the data.

---

<<TWO cursors are taking aprox 2 minutes>>

DDJ_STMT - WIP_DISCRETE_JOBS/WIP_OPERATIONS
-------------------------------------------

- Opening Cursor DDJ_STMT at:27-OCT-15 09:46:41
- Fetched: DDJ_STMT, rows: 0 at 27-oct-15 09:47:42

|----------------------- Supply/Demand -------------------------|
Reservation Type Source Type Souce ID Quantity Requirement Date
---------------- ----------- -------- -------- ----------------

- Closing Cursor DDJ_STMT at: 27-OCT-15 09:47:42

SDJ1_STMT - WIP_REQUIREMENT_OPERATIONS/WIP_DISCRETE_JOBS
--------------------------------------------------------

- Opening Cursor SDJ1_STMT at:27-OCT-15 09:47:42
- Fetched: SDJ1_STMT, rows: 0 at 27-oct-15 09:48:43

|----------------------- Supply/Demand -------------------------|
Reservation Type Source Type Souce ID Quantity Requirement Date
---------------- ----------- -------- -------- ----------------

- Closing Cursor SDJ1_STMT at: 27-OCT-15 09:48:43

---

SELECT 1, DECODE(D.JOB_TYPE, 1, 5, 7) , 5 , D.WIP_ENTITY_ID , 1 ,
LEAST(-1*(O.REQUIRED_QUANTITY-O.QUANTITY_ISSUED),0) ,
TO_NUMBER(TO_CHAR(C.PRIOR_DATE,'J')) , O.ROWID , V.INVENTORY_ITEM_ID ,
V.ORGANIZATION_ID , C.PRIOR_SEQ_NUM , NULL
FROM
MTL_GROUP_ITEM_ATPS_VIEW V, MTL_PARAMETERS P , MTL_ATP_RULES R ,
MTL_SYSTEM_ITEMS I , BOM_CALENDAR_DATES C , WIP_REQUIREMENT_OPERATIONS O ,
WIP_DISCRETE_JOBS D , BOM_CALENDAR_DATES C1 WHERE
R.PAST_DUE_DEMAND_CUTOFF_FENCE IS NOT NULL AND O.ORGANIZATION_ID =
D.ORGANIZATION_ID AND O.INVENTORY_ITEM_ID =V.INVENTORY_ITEM_ID AND
O.WIP_ENTITY_ID =D.WIP_ENTITY_ID AND O.ORGANIZATION_ID = V.ORGANIZATION_ID
AND O.WIP_SUPPLY_TYPE NOT IN (5, 6) AND O.REQUIRED_QUANTITY > 0 AND
O.REQUIRED_QUANTITY <> (O.QUANTITY_ISSUED) AND O.OPERATION_SEQ_NUM > 0 AND
O.DATE_REQUIRED >= C1.CALENDAR_DATE AND ( O.SUPPLY_SUBINVENTORY IS NULL OR
EXISTS ( SELECT 'X' FROM MTL_SECONDARY_INVENTORIES S WHERE
S.ORGANIZATION_ID =O.ORGANIZATION_ID AND O.SUPPLY_SUBINVENTORY=
S.SECONDARY_INVENTORY_NAME AND S.INVENTORY_ATP_CODE =
DECODE(R.DEFAULT_ATP_SOURCES,1, 1, NULL, 1, S.INVENTORY_ATP_CODE) AND
S.AVAILABILITY_TYPE =DECODE(R.DEFAULT_ATP_SOURCES, 2, 1,
S.AVAILABILITY_TYPE) ) ) AND D.STATUS_TYPE IN (1,3,4,6) AND
D.ORGANIZATION_ID =V.ORGANIZATION_ID AND P.ORGANIZATION_ID =
NVL(V.ATP_CALENDAR_ORGANIZATION_ID,V.ORGANIZATION_ID) AND
V.AVAILABLE_TO_ATP = 1 AND V.ATP_RULE_ID = R.RULE_ID AND V.ATP_GROUP_ID =
:B2 AND I.ORGANIZATION_ID = V.ORGANIZATION_ID AND I.INVENTORY_ITEM_ID =
V.INVENTORY_ITEM_ID AND C1.SEQ_NUM = GREATEST(1,:B1 -
R.PAST_DUE_DEMAND_CUTOFF_FENCE) AND P.CALENDAR_CODE = C.CALENDAR_CODE AND
P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID AND P.CALENDAR_CODE =
C1.CALENDAR_CODE AND P.CALENDAR_EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID AND
C.CALENDAR_DATE = TRUNC(O.DATE_REQUIRED)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.21 0.21 0 0 0 0
Fetch 1 72.95 73.24 9593 33191876 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 73.16 73.46 9593 33191876 0 0

 



EXPECTED BEHAVIOR
-----------------------
When searching supply/demand details, the data should be returned within second.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Navigate to Inventory > On-hand, Availability > Item Supply/Demand
2. Enter item and click Find (B)
3. Results show after several minutes

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users are experiencing delays.

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