eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MRP_UNSCHEDULED_ORDERS_V

Source

View Text - Preformatted


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, 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, MRP_FLOW_SCHEDULE_UTIL.GET_ROUTING_DESIGNATOR(MR1.INVENTORY_ITEM_ID, MR1.ORGANIZATION_ID,WL.LINE_ID), MR1.LINE_ID 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), MSI1.REPLENISH_TO_ORDER_FLAG) - MRP_FLOW_SCHEDULE_UTIL.GET_RESERVATION_QUANTITY( SL1.SHIP_FROM_ORG_ID, SL1.INVENTORY_ITEM_ID, SL1.LINE_ID, MSI1.REPLENISH_TO_ORDER_FLAG) ), 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 , LOC.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, MRP_FLOW_SCHEDULE_UTIL.GET_ROUTING_DESIGNATOR(SL1.INVENTORY_ITEM_ID, SL1.SHIP_FROM_ORG_ID,WL.LINE_ID), TO_NUMBER(NULL) FROM HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE, HZ_PARTIES CUST_PARTY, HZ_CUST_ACCOUNTS CUST_ACCNT, HZ_PARTY_SITES PARTY_SITE, HZ_CUST_SITE_USES_ALL RASU1, OE_ORDER_LINES_ALL SL1, MTL_SYSTEM_ITEMS_KFV MSI1, WIP_LINES WL, ( select sl2.line_id, decode ( (select 1 from oe_order_holds_all oh where oh.header_id = sl2.header_id and rownum = 1 and oh.released_flag='N'), null , 0, decode(sl2.ato_line_id, null, mrp_flow_schedule_util.check_holds(sl2.header_id, sl2.line_id, 'OEOL', 'LINE_SCHEDULING'), mrp_flow_schedule_util.check_holds(sl2.header_id, sl2.line_id, null, null) )) hold from oe_order_lines_all sl2 ) line_holds, ( select sl2.line_id, CTO_WIP_WORKFLOW_API_PK.workflow_build_status(sl2.LINE_ID) status from oe_order_lines_all sl2 ) line_build, (select distinct bor2.line_id, bor2.assembly_item_id, bor2.organization_id from bom_operational_routings bor2 where bor2.cfm_routing_flag = 1 ) line_items WHERE RASU1.SITE_USE_ID (+)= SL1.SHIP_TO_ORG_ID and acct_site.cust_acct_site_id(+) = rasu1.cust_acct_site_id and ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID AND CUST_ACCNT.CUST_ACCOUNT_ID (+)= SL1.SOLD_TO_ORG_ID AND CUST_PARTY.PARTY_ID = CUST_ACCNT.PARTY_ID and line_build.line_id = sl1.line_id AND 1 = decode(MSI1.REPLENISH_TO_ORDER_FLAG, 'N',1, line_build.status) 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 SL1.ITEM_TYPE_CODE in ('STANDARD', 'CONFIG', 'INCLUDED','OPTION') AND OE_INSTALL.GET_ACTIVE_PRODUCT ='ONT' AND wl.organization_id = sl1.ship_from_org_id and wl.line_id = line_items.line_id and sl1.inventory_item_id = line_items.assembly_item_id and sl1.ship_from_org_id = line_items.organization_id AND SL1.SHIPPED_QUANTITY is NULL and sl1.line_id = line_holds.line_id and line_holds.hold = 0 AND NVL(SL1.FULFILLED_FLAG,'N') <> 'Y'

View Text - HTML Formatted

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
, 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
, MRP_FLOW_SCHEDULE_UTIL.GET_ROUTING_DESIGNATOR(MR1.INVENTORY_ITEM_ID
, MR1.ORGANIZATION_ID
, WL.LINE_ID)
, MR1.LINE_ID
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)
, MSI1.REPLENISH_TO_ORDER_FLAG) - MRP_FLOW_SCHEDULE_UTIL.GET_RESERVATION_QUANTITY( SL1.SHIP_FROM_ORG_ID
, SL1.INVENTORY_ITEM_ID
, SL1.LINE_ID
, MSI1.REPLENISH_TO_ORDER_FLAG) )
, 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
, LOC.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
, MRP_FLOW_SCHEDULE_UTIL.GET_ROUTING_DESIGNATOR(SL1.INVENTORY_ITEM_ID
, SL1.SHIP_FROM_ORG_ID
, WL.LINE_ID)
, TO_NUMBER(NULL)
FROM HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTIES CUST_PARTY
, HZ_CUST_ACCOUNTS CUST_ACCNT
, HZ_PARTY_SITES PARTY_SITE
, HZ_CUST_SITE_USES_ALL RASU1
, OE_ORDER_LINES_ALL SL1
, MTL_SYSTEM_ITEMS_KFV MSI1
, WIP_LINES WL
, ( SELECT SL2.LINE_ID
, DECODE ( (SELECT 1
FROM OE_ORDER_HOLDS_ALL OH
WHERE OH.HEADER_ID = SL2.HEADER_ID
AND ROWNUM = 1
AND OH.RELEASED_FLAG='N')
, NULL
, 0
, DECODE(SL2.ATO_LINE_ID
, NULL
, MRP_FLOW_SCHEDULE_UTIL.CHECK_HOLDS(SL2.HEADER_ID
, SL2.LINE_ID
, 'OEOL'
, 'LINE_SCHEDULING')
, MRP_FLOW_SCHEDULE_UTIL.CHECK_HOLDS(SL2.HEADER_ID
, SL2.LINE_ID
, NULL
, NULL) )) HOLD
FROM OE_ORDER_LINES_ALL SL2 ) LINE_HOLDS
, ( SELECT SL2.LINE_ID
, CTO_WIP_WORKFLOW_API_PK.WORKFLOW_BUILD_STATUS(SL2.LINE_ID) STATUS
FROM OE_ORDER_LINES_ALL SL2 ) LINE_BUILD
, (SELECT DISTINCT BOR2.LINE_ID
, BOR2.ASSEMBLY_ITEM_ID
, BOR2.ORGANIZATION_ID
FROM BOM_OPERATIONAL_ROUTINGS BOR2
WHERE BOR2.CFM_ROUTING_FLAG = 1 ) LINE_ITEMS
WHERE RASU1.SITE_USE_ID (+)= SL1.SHIP_TO_ORG_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID(+) = RASU1.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID
AND CUST_ACCNT.CUST_ACCOUNT_ID (+)= SL1.SOLD_TO_ORG_ID
AND CUST_PARTY.PARTY_ID = CUST_ACCNT.PARTY_ID
AND LINE_BUILD.LINE_ID = SL1.LINE_ID
AND 1 = DECODE(MSI1.REPLENISH_TO_ORDER_FLAG
, 'N'
, 1
, LINE_BUILD.STATUS)
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 SL1.ITEM_TYPE_CODE IN ('STANDARD'
, 'CONFIG'
, 'INCLUDED'
, 'OPTION')
AND OE_INSTALL.GET_ACTIVE_PRODUCT ='ONT'
AND WL.ORGANIZATION_ID = SL1.SHIP_FROM_ORG_ID
AND WL.LINE_ID = LINE_ITEMS.LINE_ID
AND SL1.INVENTORY_ITEM_ID = LINE_ITEMS.ASSEMBLY_ITEM_ID
AND SL1.SHIP_FROM_ORG_ID = LINE_ITEMS.ORGANIZATION_ID
AND SL1.SHIPPED_QUANTITY IS NULL
AND SL1.LINE_ID = LINE_HOLDS.LINE_ID
AND LINE_HOLDS.HOLD = 0
AND NVL(SL1.FULFILLED_FLAG
, 'N') <> 'Y'