Sales order form hangs when scheduling sales order (Doc ID 1927097.1)

Last updated on MARCH 08, 2017

Applies to:

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

Symptoms

On : 12.1.3 version, ATP based on collected data

ACTUAL BEHAVIOR
---------------
When entering sales orders the systems hangs.
Trace file shows

SELECT SD_DATE, SUM(SD_QTY)
FROM
( SELECT C.CALENDAR_DATE SD_DATE, -1* DECODE(D.ORIGINATION_TYPE, 4,
 D.DAILY_DEMAND_RATE, (D.USING_REQUIREMENT_QUANTITY -
 NVL(D.RESERVED_QUANTITY, 0))) SD_QTY FROM MSC_CALENDAR_DATES C, MSC_DEMANDS
 D, MSC_SYSTEM_ITEMS I WHERE I.SR_INVENTORY_ITEM_ID = :B8 AND
 I.ORGANIZATION_ID = :B7 AND I.SR_INSTANCE_ID = :B6 AND I.PLAN_ID = :B5 AND
 D.PLAN_ID = I.PLAN_ID AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID AND
 D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID AND D.ORGANIZATION_ID =
 I.ORGANIZATION_ID AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31,
 52,70) AND C.CALENDAR_CODE = :B4 AND C.EXCEPTION_SET_ID = :B3 AND
 C.SR_INSTANCE_ID = I.SR_INSTANCE_ID AND D.USING_REQUIREMENT_QUANTITY <> 0
 AND C.CALENDAR_DATE BETWEEN TRUNC(DECODE(D.RECORD_SOURCE, 2,
 NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE), DECODE(:B2 , 2,
 NVL(D.IMPLEMENT_SHIP_DATE,NVL(D.FIRM_DATE,NVL(D.PLANNED_SHIP_DATE,
 D.USING_ASSEMBLY_DEMAND_DATE))), NVL(D.SCHEDULE_SHIP_DATE,
 D.USING_ASSEMBLY_DEMAND_DATE)))) AND TRUNC(NVL(D.ASSEMBLY_DEMAND_COMP_DATE,
 DECODE(D.RECORD_SOURCE, 2, NVL(D.SCHEDULE_SHIP_DATE,
 D.USING_ASSEMBLY_DEMAND_DATE), DECODE(:B2 , 2, NVL(D.IMPLEMENT_SHIP_DATE,
 NVL(D.FIRM_DATE,NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
 NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))))) AND (C.SEQ_NUM
 IS NOT NULL OR D.ORIGINATION_TYPE <> 4) AND C.CALENDAR_DATE < NVL(:B1 ,
 C.CALENDAR_DATE + 1) UNION ALL SELECT C.CALENDAR_DATE SD_DATE,
 NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY FROM MSC_CALENDAR_DATES C,
 MSC_SUPPLIES S, MSC_SYSTEM_ITEMS I WHERE I.SR_INVENTORY_ITEM_ID = :B8 AND
 I.ORGANIZATION_ID = :B7 AND I.SR_INSTANCE_ID = :B6 AND I.PLAN_ID = :B5 AND
 S.PLAN_ID = I.PLAN_ID AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID AND
 S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID AND S.ORGANIZATION_ID =
 I.ORGANIZATION_ID AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2 AND
 NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0 AND C.CALENDAR_CODE = :B4
 AND C.EXCEPTION_SET_ID = :B3 AND C.SR_INSTANCE_ID = I.SR_INSTANCE_ID AND
 C.CALENDAR_DATE BETWEEN TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) AND
 TRUNC(NVL(S.LAST_UNIT_COMPLETION_DATE, NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE))
 ) AND DECODE(S.LAST_UNIT_COMPLETION_DATE, NULL, 1, C.SEQ_NUM) IS NOT NULL
 AND C.CALENDAR_DATE < NVL(:B1 , C.CALENDAR_DATE + 1) ) GROUP BY SD_DATE
 ORDER BY SD_DATE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2259 0.39 0.38 0 0 4 0
Fetch 2258 4715.78 4717.19 33 37378919 0 664340
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4518 4716.19 4717.57 33 37378919 4 664340

EXPECTED BEHAVIOR
-----------------------
system should not hangs

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
Order management
1. orders - enter sales orders or add order line




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