Planning Manager Is Running From Longer Time (Doc ID 1177737.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Materials Requirement Planning - Version 12.0 and later
Information in this document applies to any platform.
Executable:MRCRLF - Planning Manager


Symptoms

Find that the Planning Manager is running a long time on the statement:

SELECT DEM.INVENTORY_ITEM_ID,
DEM.LINE_ID,
DEM.SHIP_FROM_ORG_ID ORY_CODE,
'ORDER',
2,12)) IN (:B2 , :B1 ) DULE_SHIP_DATE,
DECODE(DEM.LINE_ID, DEM.ATO_LINE_ID, DEM.SCHEDULE_SHIP_DATE, MRP_CALENDAR.DATE_OFFSET (DEM.SHIP_FROM_ORG_ID, 1, DEM.SCHEDULE_SHIP_DATE, -1*(DEM.MFG_LEAD_TIME)))), DECODE(DEM.ORDERED_QUANTITY, NULL, 0, INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY( DEM.SHIP_FROM_ORG_ID, DEM.INVENTORY_ITEM_ID, DEM.ORDER_QUANTITY_UOM, DEM.ORDERED_QUANTITY)),
DEM.SOLD_TO_ORG_ID,
DEM.SHIP_TO_ORG_ID,
DEM.INVOICE_TO_ORG_ID,
NVL(VISIBLE_DEMAND_FLAG,'N'),
DEM.DEMAND_CLASS_CODE,
DECODE(DEM.SHIPPED_QUANTITY, NULL, 0, INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY( DEM.SHIP_FROM_ORG_ID, DEM.INVENTORY_ITEM_ID, DEM.ORDER_QUANTITY_UOM, DEM.SHIPPED_QUANTITY)),
DECODE(DECODE(DEM.ITEM_TYPE_CODE, 'CLASS',2, 'CONFIG',4, 'MODEL',1, 'OPTION' ,3, 'STANDARD',6, -1), 1, DEM.INVENTORY_ITEM_ID, NULL),
INV_SALESORDER.GET_SALESORDER_FOR_OEHEADER(DEM.HEADER_ID),
DECODE(DEM.ITEM_TYPE_CODE, 'CLASS',2, 'CONFIG',4, 'MODEL',1, 'OPTION' ,3, 'STANDARD',6, -1),
DEM.ATO_LINE_ID,
UPD.ROWID FROM OE_ORDER_LINES_ALL DEM,
MRP_SALES_ORDER_UPDATES UPD,
MTL_PARAMETERS PARAM WHERE NVL(UPD.PROCESS_STATUS, -1) <> 3 AND UPD.SALES_ORDER_ID(+) = DEM.LINE_ID AND PARAM.CALENDAR_CODE IS NOT NULL AND PARAM.CALENDAR_EXCEPTION_SET_ID IS NOT NULL AND PARAM.ORGANIZATION_ID = DECODE(DEM.CANCELLED_FLAG, 'Y', UPD.ORGANIZATION_ID, DEM.SHIP_FROM_ORG_ID) AND DEM.SOLD_TO_ORG_ID IS NOT NULL AND DEM.SHIP_TO_ORG_ID IS NOT NULL AND DEM.INVOICE_TO_ORG_ID IS NOT NULL AND ((DEM.SCHEDULE_SHIP_DATE IS NULL ) OR (DEM.SCHEDULE_SHIP_DATE IS NOT NULL AND DEM.SCHEDULE_SHIP_DATE >= (SYSDATE - :B4 ))) AND (NOT EXISTS
(SELECT NULL
FROM MRP_SALES_ORDER_UPDATES UPDATES
WHERE UPDATES.SALES_ORDER_ID = DEM.LINE_ID
AND ( DECODE(NVL(DEM.MFG_LEAD_TIME,0), 0,UPDATES.NEW_SCHEDULE_DATE, DECODE(DEM.LINE_ID, DEM.ATO_LINE_ID, DEM.SCHEDULE_SHIP_DATE, MRP_CALENDAR.DATE_OFFSET(UPDATES.ORGANIZATION_ID, 1, UPDATES.NEW_SCHEDULE_DATE, DEM.MFG_LEAD_TIME))) = DEM.SCHEDULE_SHIP_DATE
OR (NVL(DEM.VISIBLE_DEMAND_FLAG,'N') = 'N'
AND UPDATES.CURRENT_AVAILABLE_TO_MRP = 'N'))
AND UPDATES.NEW_SCHEDULE_QUANTITY = DECODE(DEM.ORDERED_QUANTITY, NULL, 0, INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY( DEM.SHIP_FROM_ORG_ID, DEM.INVENTORY_ITEM_ID, DEM.ORDER_QUANTITY_UOM, DEM.ORDERED_QUANTITY))
AND UPDATES.CURRENT_CUSTOMER_ID = DEM.SOLD_TO_ORG_ID
AND UPDATES.CURRENT_SHIP_ID = DEM.SHIP_TO_ORG_ID
AND UPDATES.CURRENT_BILL_ID = DEM.INVOICE_TO_ORG_ID
AND NVL(UPDATES.CURRENT_DEMAND_CLASS, 'A') = NVL(DEM.DEMAND_CLASS_CODE, 'A')
AND UPDATES.PROCESS_STATUS <> 3
)) AND (NOT EXISTS
(SELECT NULL FROM MSC_FORM_QUERY QUERY WHERE QUERY.QUERY_ID = DEM.LINE_ID
)) AND ROWNUM <= :B3 AND (DEM.VISIBLE_DEMAND_FLAG = 'Y' OR UPD.ROWID IS NOT NULL) AND DECODE(DEM.SOURCE_DOCUMENT_TYPE_ID, 10, 8, DECODE(DEM.LINE_CATEG

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