View: MRP_UNSCHEDULED_ORDERS_V

Product:	MRP - Master Scheduling/MRP
Description:
Implementation/DBA Data:	APPS.MRP_UNSCHEDULED_ORDERS_V

View Text

SELECT MR1.ROWID
, 2
, /* PLANNED ORDERS OPTION */ 2
, MR1.ORGANIZATION_ID
, MR1.INVENTORY_ITEM_ID
, KFV.CONCATENATED_SEGMENTS
, WL.LINE_ID
, NVL(MR1.FIRM_DATE
, MR1.NEW_SCHEDULE_DATE)
, NVL(MR1.FIRM_QUANTITY
, MR1.NEW_ORDER_QUANTITY)
, GREATEST((NVL(MR1.FIRM_QUANTITY
, MR1.NEW_ORDER_QUANTITY) - MRP_FLOW_SCHEDULE_UTIL.GET_FLOW_QUANTITY( TO_CHAR(MR1.TRANSACTION_ID)
, 100
, NULL))
, 0)
, 100
, /* 100 INDICATES PLANNED ORDER IN DEMAND_SOURCE_TYPE COLUMN */ TO_NUMBER(NULL)
, /* DEMAND_SOURCE_HEADER_ID */ MR1.COMPILE_DESIGNATOR
, TO_CHAR(MR1.TRANSACTION_ID)
, /* STORED IN DEMAND_SOURCE_LINE */ TO_NUMBER(NULL)
, /* LINE_NUMBER */ NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, MR1.PROJECT_ID
, MRP_GET_PROJECT.PROJECT(MR1.PROJECT_ID)
, MR1.TASK_ID
, MRP_GET_PROJECT.TASK(MR1.TASK_ID)
, RSI1.FIXED_LEAD_TIME
, RSI1.VARIABLE_LEAD_TIME
, MR1.FIRM_PLANNED_TYPE
, MR1.DEMAND_CLASS
, MR1.END_ITEM_UNIT_NUMBER
, KFV.REPLENISH_TO_ORDER_FLAG
, KFV.BUILD_IN_WIP_FLAG
FROM MTL_SYSTEM_ITEMS_KFV KFV
, MRP_SYSTEM_ITEMS RSI1
, MRP_PLANS MP1
, MRP_RECOMMENDATIONS MR1
, WIP_LINES WL
WHERE MP1.PLAN_COMPLETION_DATE IS NOT NULL
AND MP1.DATA_COMPLETION_DATE IS NOT NULL
AND MP1.COMPILE_DESIGNATOR = MR1.COMPILE_DESIGNATOR
AND ( MP1.ORGANIZATION_ID = MR1.ORGANIZATION_ID OR (MP1.ORGANIZATION_ID IN (SELECT ORGANIZATION_ID
FROM MRP_PLAN_ORGANIZATIONS
WHERE COMPILE_DESIGNATOR = MR1.COMPILE_DESIGNATOR
AND PLANNED_ORGANIZATION = MR1.ORGANIZATION_ID) ) )
AND MR1.ORGANIZATION_ID = MR1.SOURCE_ORGANIZATION_ID
AND KFV.INVENTORY_ITEM_ID = RSI1.INVENTORY_ITEM_ID
AND KFV.ORGANIZATION_ID = RSI1.ORGANIZATION_ID
AND NVL( KFV.RELEASE_TIME_FENCE_CODE
, -1) <> 6 /* KANBAN ITEM */
AND MR1.ORDER_TYPE = 5 /* PLANNED ORDER */
AND MR1.ORGANIZATION_ID = RSI1.ORGANIZATION_ID
AND MR1.COMPILE_DESIGNATOR = RSI1.COMPILE_DESIGNATOR
AND MR1.INVENTORY_ITEM_ID = RSI1.INVENTORY_ITEM_ID
AND MR1.COMPILE_DESIGNATOR = (SELECT DESIGNATOR
FROM MRP_DESIGNATORS_VIEW
WHERE PRODUCTION = 1
AND ORGANIZATION_ID = MP1.ORGANIZATION_ID
AND DESIGNATOR = MR1.COMPILE_DESIGNATOR)
AND RSI1.BUILD_IN_WIP_FLAG = 1 /* YES */
AND RSI1.BOM_ITEM_TYPE = 4
AND (RSI1.IN_SOURCE_PLAN = 2 OR RSI1.IN_SOURCE_PLAN IS NULL)
AND WL.ORGANIZATION_ID = MR1.ORGANIZATION_ID
AND WL.LINE_ID IN (SELECT LINE_ID
FROM BOM_OPERATIONAL_ROUTINGS BOR2
WHERE BOR2.ASSEMBLY_ITEM_ID = MR1.INVENTORY_ITEM_ID
AND BOR2.ORGANIZATION_ID = MR1.ORGANIZATION_ID
AND BOR2.CFM_ROUTING_FLAG = 1 ) UNION ALL SELECT SL1.ROWID
, 1
, /* SALES ORDERS OPTION */ 1
, /* SALES ORDER */ SL1.SHIP_FROM_ORG_ID
, SL1.INVENTORY_ITEM_ID
, MSI1.CONCATENATED_SEGMENTS
, WL.LINE_ID
, SL1.SCHEDULE_SHIP_DATE
, INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SL1.SHIP_FROM_ORG_ID
, SL1.INVENTORY_ITEM_ID
, SL1.ORDER_QUANTITY_UOM
, SL1.ORDERED_QUANTITY) ORIGINAL_ORDER_QUANTITY
, GREATEST((INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SL1.SHIP_FROM_ORG_ID
, SL1.INVENTORY_ITEM_ID
, SL1.ORDER_QUANTITY_UOM
, SL1.ORDERED_QUANTITY) - MRP_FLOW_SCHEDULE_UTIL.GET_FLOW_QUANTITY(SL1.LINE_ID
, 2
, TO_CHAR(NULL)) - MRP_FLOW_SCHEDULE_UTIL.GET_RESERVATION_QUANTITY(SL1.SHIP_FROM_ORG_ID
, SL1.INVENTORY_ITEM_ID
, SL1.LINE_ID) )
, 0)
, 2
, INV_SALESORDER.GET_SALESORDER_FOR_OEHEADER(SL1.HEADER_ID)
, NULL
, /* PLAN NAME */ TO_CHAR(SL1.LINE_ID)
, SL1.LINE_NUMBER
, TO_CHAR(NULL)
, SL1.LINE_NUMBER
, SL1.PLANNING_PRIORITY
, SL1.SOLD_TO_ORG_ID
, CUST_PARTY.PARTY_NAME
, SL1.SHIP_TO_ORG_ID
, RAD1.ADDRESS1
, SL1.PROJECT_ID
, MRP_GET_PROJECT.PROJECT(SL1.PROJECT_ID)
, SL1.TASK_ID
, MRP_GET_PROJECT.TASK(SL1.TASK_ID)
, MSI1.FIXED_LEAD_TIME
, MSI1.VARIABLE_LEAD_TIME
, TO_NUMBER(NULL)
, SL1.DEMAND_CLASS_CODE
, MRP_MANAGER_PK.GET_UNIT_NUMBER(SL1.LINE_ID)
, MSI1.REPLENISH_TO_ORDER_FLAG
, MSI1.BUILD_IN_WIP_FLAG
FROM RA_ADDRESSES RAD1
, HZ_PARTIES CUST_PARTY
, HZ_CUST_ACCOUNTS CUST_ACCNT
, RA_SITE_USES RASU1
, OE_ORDER_LINES_ALL SL1
, MTL_SYSTEM_ITEMS_KFV MSI1
, WIP_LINES WL
WHERE RAD1.ADDRESS_ID (+)= RASU1.ADDRESS_ID
AND CUST_ACCNT.CUST_ACCOUNT_ID (+)= SL1.SOLD_TO_ORG_ID
AND CUST_PARTY.PARTY_ID = CUST_ACCNT.PARTY_ID
AND RASU1.SITE_USE_ID (+)= SL1.SHIP_TO_ORG_ID
AND ( NOT EXISTS (SELECT 1
FROM OE_ORDER_HOLDS_ALL OH
WHERE OH.HEADER_ID = SL1.HEADER_ID) OR (SL1.ATO_LINE_ID IS NULL
AND 0 = MRP_FLOW_SCHEDULE_UTIL.CHECK_HOLDS(SL1.HEADER_ID
, SL1.LINE_ID
, 'OEOL'
, 'LINE_SCHEDULING') ) OR (SL1.ATO_LINE_ID IS NOT NULL
AND 0 = MRP_FLOW_SCHEDULE_UTIL.CHECK_HOLDS(SL1.HEADER_ID
, SL1.LINE_ID
, NULL
, NULL) ) )
AND 1 = DECODE(MSI1.REPLENISH_TO_ORDER_FLAG
, 'N'
, 1
, CTO_WIP_WORKFLOW_API_PK.WORKFLOW_BUILD_STATUS(SL1.LINE_ID))
AND MSI1.BUILD_IN_WIP_FLAG = 'Y'
AND MSI1.PICK_COMPONENTS_FLAG = 'N'
AND MSI1.BOM_ITEM_TYPE = 4
AND MSI1.ORGANIZATION_ID = SL1.SHIP_FROM_ORG_ID
AND MSI1.INVENTORY_ITEM_ID = SL1.INVENTORY_ITEM_ID
AND SL1.ORDERED_QUANTITY > 0
AND SL1.VISIBLE_DEMAND_FLAG = 'Y'
AND SL1.OPEN_FLAG = 'Y'
AND OE_INSTALL.GET_ACTIVE_PRODUCT ='ONT'
AND WL.ORGANIZATION_ID = SL1.SHIP_FROM_ORG_ID
AND WL.LINE_ID IN (SELECT LINE_ID
FROM BOM_OPERATIONAL_ROUTINGS BOR2
WHERE BOR2.ASSEMBLY_ITEM_ID = SL1.INVENTORY_ITEM_ID
AND BOR2.ORGANIZATION_ID = SL1.SHIP_FROM_ORG_ID
AND BOR2.CFM_ROUTING_FLAG = 1 )

Columns