VIEW: APPS.FLM_SEQ_DEMAND_PLAN_ORDERS_V

Source

View Text - Preformatted

SELECT MR.ROWID , MR.ORGANIZATION_ID, MR.INVENTORY_ITEM_ID, WL.LINE_ID, MRP_FLOW_SCHEDULE_UTIL.GET_ROUTING_DESIGNATOR(MR.INVENTORY_ITEM_ID, MR.ORGANIZATION_ID,WL.LINE_ID), MR.TRANSACTION_ID, MSI.CONCATENATED_SEGMENTS, TO_CHAR(NULL), TO_CHAR(NULL), MR.DEMAND_CLASS, NVL(MR.FIRM_DATE,MR.NEW_SCHEDULE_DATE), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), NVL(MR.FIRM_QUANTITY,MR.NEW_ORDER_QUANTITY), MR.COMPILE_DESIGNATOR, MRP_GET_PROJECT.PROJECT(MR.PROJECT_ID), MRP_GET_PROJECT.TASK(MR.TASK_ID), TO_NUMBER(NULL), GREATEST((NVL(MR.FIRM_QUANTITY,MR.NEW_ORDER_QUANTITY) - MRP_FLOW_SCHEDULE_UTIL.GET_FLOW_QUANTITY(TO_CHAR(MR.TRANSACTION_ID),100,NULL,NULL)),0), MR.LINE_ID FROM MTL_SYSTEM_ITEMS_KFV MSI, MRP_SYSTEM_ITEMS RSI, MRP_PLANS MP, MRP_RECOMMENDATIONS MR, WIP_LINES WL WHERE MP.PLAN_COMPLETION_DATE IS NOT NULL AND MP.DATA_COMPLETION_DATE IS NOT NULL AND MP.COMPILE_DESIGNATOR = MR.COMPILE_DESIGNATOR AND ( MP.ORGANIZATION_ID = MR.ORGANIZATION_ID OR (MP.ORGANIZATION_ID IN (SELECT ORGANIZATION_ID FROM MRP_PLAN_ORGANIZATIONS WHERE COMPILE_DESIGNATOR = MR.COMPILE_DESIGNATOR AND PLANNED_ORGANIZATION = MR.ORGANIZATION_ID) ) ) AND MR.ORGANIZATION_ID = MR.SOURCE_ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = RSI.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = RSI.ORGANIZATION_ID AND NVL( MSI.RELEASE_TIME_FENCE_CODE,-1) <> 6 /* KANBAN ITEM */ AND MR.ORDER_TYPE = 5 /* PLANNED ORDER */ AND MR.ORGANIZATION_ID = RSI.ORGANIZATION_ID AND MR.COMPILE_DESIGNATOR = RSI.COMPILE_DESIGNATOR AND MR.INVENTORY_ITEM_ID = RSI.INVENTORY_ITEM_ID AND MR.COMPILE_DESIGNATOR = (SELECT DESIGNATOR FROM MRP_DESIGNATORS_VIEW WHERE PRODUCTION = 1 AND ORGANIZATION_ID = MP.ORGANIZATION_ID AND DESIGNATOR = MR.COMPILE_DESIGNATOR) AND RSI.BUILD_IN_WIP_FLAG = 1 /* YES */ AND RSI.BOM_ITEM_TYPE = 4 AND (RSI.IN_SOURCE_PLAN = 2 OR RSI.IN_SOURCE_PLAN IS NULL) AND WL.ORGANIZATION_ID = MR.ORGANIZATION_ID AND WL.LINE_ID IN (SELECT LINE_ID FROM BOM_OPERATIONAL_ROUTINGS BOR2 WHERE BOR2.ASSEMBLY_ITEM_ID = MR.INVENTORY_ITEM_ID AND BOR2.ORGANIZATION_ID = MR.ORGANIZATION_ID AND BOR2.CFM_ROUTING_FLAG = 1)

View Text - HTML Formatted

SELECT MR.ROWID
, MR.ORGANIZATION_ID
, MR.INVENTORY_ITEM_ID
, WL.LINE_ID
, MRP_FLOW_SCHEDULE_UTIL.GET_ROUTING_DESIGNATOR(MR.INVENTORY_ITEM_ID
, MR.ORGANIZATION_ID
, WL.LINE_ID)
, MR.TRANSACTION_ID
, MSI.CONCATENATED_SEGMENTS
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, MR.DEMAND_CLASS
, NVL(MR.FIRM_DATE
, MR.NEW_SCHEDULE_DATE)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NVL(MR.FIRM_QUANTITY
, MR.NEW_ORDER_QUANTITY)
, MR.COMPILE_DESIGNATOR
, MRP_GET_PROJECT.PROJECT(MR.PROJECT_ID)
, MRP_GET_PROJECT.TASK(MR.TASK_ID)
, TO_NUMBER(NULL)
, GREATEST((NVL(MR.FIRM_QUANTITY
, MR.NEW_ORDER_QUANTITY) - MRP_FLOW_SCHEDULE_UTIL.GET_FLOW_QUANTITY(TO_CHAR(MR.TRANSACTION_ID)
, 100
, NULL
, NULL))
, 0)
, MR.LINE_ID
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MRP_SYSTEM_ITEMS RSI
, MRP_PLANS MP
, MRP_RECOMMENDATIONS MR
, WIP_LINES WL
WHERE MP.PLAN_COMPLETION_DATE IS NOT NULL
AND MP.DATA_COMPLETION_DATE IS NOT NULL
AND MP.COMPILE_DESIGNATOR = MR.COMPILE_DESIGNATOR
AND ( MP.ORGANIZATION_ID = MR.ORGANIZATION_ID OR (MP.ORGANIZATION_ID IN (SELECT ORGANIZATION_ID
FROM MRP_PLAN_ORGANIZATIONS
WHERE COMPILE_DESIGNATOR = MR.COMPILE_DESIGNATOR
AND PLANNED_ORGANIZATION = MR.ORGANIZATION_ID) ) )
AND MR.ORGANIZATION_ID = MR.SOURCE_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = RSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = RSI.ORGANIZATION_ID
AND NVL( MSI.RELEASE_TIME_FENCE_CODE
, -1) <> 6 /* KANBAN ITEM */
AND MR.ORDER_TYPE = 5 /* PLANNED ORDER */
AND MR.ORGANIZATION_ID = RSI.ORGANIZATION_ID
AND MR.COMPILE_DESIGNATOR = RSI.COMPILE_DESIGNATOR
AND MR.INVENTORY_ITEM_ID = RSI.INVENTORY_ITEM_ID
AND MR.COMPILE_DESIGNATOR = (SELECT DESIGNATOR
FROM MRP_DESIGNATORS_VIEW
WHERE PRODUCTION = 1
AND ORGANIZATION_ID = MP.ORGANIZATION_ID
AND DESIGNATOR = MR.COMPILE_DESIGNATOR)
AND RSI.BUILD_IN_WIP_FLAG = 1 /* YES */
AND RSI.BOM_ITEM_TYPE = 4
AND (RSI.IN_SOURCE_PLAN = 2 OR RSI.IN_SOURCE_PLAN IS NULL)
AND WL.ORGANIZATION_ID = MR.ORGANIZATION_ID
AND WL.LINE_ID IN (SELECT LINE_ID
FROM BOM_OPERATIONAL_ROUTINGS BOR2
WHERE BOR2.ASSEMBLY_ITEM_ID = MR.INVENTORY_ITEM_ID
AND BOR2.ORGANIZATION_ID = MR.ORGANIZATION_ID
AND BOR2.CFM_ROUTING_FLAG = 1)