View: MRP_ORDERS_V

Product:	MRP - Master Scheduling/MRP
Description:	MRP supply and demand view
Implementation/DBA Data:	APPS.MRP_ORDERS_V

View Text

SELECT 'MRP_RECOMMENDATIONS'
, REC.ROWID
, REC.TRANSACTION_ID
, REC.LAST_UPDATE_DATE
, REC.LAST_UPDATED_BY
, REC.CREATION_DATE
, REC.CREATED_BY
, REC.LAST_UPDATE_LOGIN
, MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MSI.COMPILE_DESIGNATOR
, REC.NEW_SCHEDULE_DATE
, REC.OLD_SCHEDULE_DATE
, REC.NEW_WIP_START_DATE
, REC.DISPOSITION_ID
, DECODE(REC.ORDER_TYPE
, 1
, IPO.PO_NUMBER
, 2
, IPO.PO_NUMBER
, 3
, IWE.WIP_ENTITY_NAME
, 7
, IWE.WIP_ENTITY_NAME
, 8
, IPO.PO_NUMBER
, 11
, IPO.PO_NUMBER
, 12
, IPO.PO_NUMBER
, NULL)
, REC.DISPOSITION_STATUS_TYPE
, REC.ORDER_TYPE
, L1.MEANING
, REC.VENDOR_ID
, NULL
, REC.NEW_ORDER_QUANTITY
, REC.OLD_ORDER_QUANTITY
, REC.NEW_ORDER_PLACEMENT_DATE
, REC.FIRM_PLANNED_TYPE
, REC.RESCHEDULED_FLAG
, REC.NEW_PROCESSING_DAYS
, REC.IMPLEMENTED_QUANTITY
, REC.LAST_UNIT_COMPLETION_DATE
, REC.FIRST_UNIT_START_DATE
, REC.LAST_UNIT_START_DATE
, REC.NEW_DOCK_DATE
, REC.QUANTITY_IN_PROCESS
, REC.FIRM_QUANTITY
, REC.FIRM_DATE
, TO_NUMBER(NULL)
, NULL
, REC.IMPLEMENT_DEMAND_CLASS
, FLEX.ITEM_NUMBER
, REC.IMPLEMENT_DATE
, REC.IMPLEMENT_QUANTITY
, REC.IMPLEMENT_FIRM
, TO_NUMBER(NULL)
, REC.IMPLEMENT_WIP_CLASS_CODE
, REC.IMPLEMENT_JOB_NAME
, TO_NUMBER(NULL)
, REC.IMPLEMENT_DOCK_DATE
, REC.IMPLEMENT_STATUS_CODE
, REC.IMPLEMENT_EMPLOYEE_ID
, REC.IMPLEMENT_UOM_CODE
, REC.IMPLEMENT_LOCATION_ID
, REC.RELEASE_STATUS
, REC.LOAD_TYPE
, REC.IMPLEMENT_AS
, L4.MEANING
, MSI.PLANNER_CODE
, TO_NUMBER(NULL)
, MSI.CATEGORY_ID
, MSI.MRP_PLANNING_CODE
, MSI.REPETITIVE_TYPE
, MSI.BUILD_IN_WIP_FLAG
, MSI.PURCHASING_ENABLED_FLAG
, MSI.PLANNING_MAKE_BUY_CODE
, GREATEST(0
, NEW_ORDER_PLACEMENT_DATE - (TRUNC(SYSDATE) ))
, MSI.BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID
, MSI.WIP_SUPPLY_TYPE
, NULL
, IWE.STATUS_CODE
, TO_CHAR(NULL)
, MSI.FULL_PEGGING
, REC.SCHEDULE_COMPRESSION_DAYS
FROM MRP_ITEM_WIP_ENTITIES IWE
, MRP_ITEM_PURCHASE_ORDERS IPO
, MFG_LOOKUPS L1
, MFG_LOOKUPS L4
, MRP_RECOMMENDATIONS REC
, MTL_ITEM_FLEXFIELDS FLEX
, MRP_SYSTEM_ITEMS MSI
WHERE IWE.WIP_ENTITY_ID(+) = REC.DISPOSITION_ID
AND IWE.COMPILE_DESIGNATOR(+) = REC.COMPILE_DESIGNATOR
AND IWE.ORGANIZATION_ID(+) = REC.ORGANIZATION_ID
AND IWE.INVENTORY_ITEM_ID(+) = REC.INVENTORY_ITEM_ID
AND IPO.TRANSACTION_ID(+) = REC.DISPOSITION_ID
AND IPO.COMPILE_DESIGNATOR(+) = REC.COMPILE_DESIGNATOR
AND IPO.ORGANIZATION_ID(+) = REC.ORGANIZATION_ID
AND IPO.INVENTORY_ITEM_ID(+) = REC.INVENTORY_ITEM_ID
AND REC.COMPILE_DESIGNATOR = MSI.COMPILE_DESIGNATOR
AND REC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND FLEX.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND FLEX.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND REC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND L4.LOOKUP_TYPE(+) = 'MRP_WORKBENCH_IMPLEMENT_AS'
AND L4.LOOKUP_CODE(+) = REC.IMPLEMENT_AS
AND L1.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND L1.LOOKUP_CODE = REC.ORDER_TYPE
AND REC.ORDER_TYPE != 4 UNION ALL SELECT 'MRP_SUGG_REP_SCHEDULES'
, SCHED.ROWID
, SCHED.TRANSACTION_ID
, SCHED.LAST_UPDATE_DATE
, SCHED.LAST_UPDATED_BY
, SCHED.CREATION_DATE
, SCHED.CREATED_BY
, SCHED.LAST_UPDATE_LOGIN
, MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MSI.COMPILE_DESIGNATOR
, SCHED.FIRST_UNIT_COMPLETION_DATE
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, L1.LOOKUP_CODE
, L1.MEANING
, TO_NUMBER(NULL)
, NULL
, SCHED.DAILY_RATE
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, NVL(SCHED.FIRM_PLANNED_STATUS_TYPE
, 2)
, TO_NUMBER(NULL)
, SCHED.PROCESSING_DAYS
, TO_NUMBER(NULL)
, SCHED.LAST_UNIT_COMPLETION_DATE
, SCHED.FIRST_UNIT_START_DATE
, SCHED.LAST_UNIT_START_DATE
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, SCHED.REPETITIVE_LINE
, WL.LINE_CODE
, SCHED.IMPLEMENT_DEMAND_CLASS
, FLEX.ITEM_NUMBER
, SCHED.IMPLEMENT_DATE
, SCHED.IMPLEMENT_DAILY_RATE
, SCHED.IMPLEMENT_FIRM
, SCHED.IMPLEMENT_PROCESSING_DAYS
, SCHED.IMPLEMENT_WIP_CLASS_CODE
, NULL
, SCHED.IMPLEMENT_LINE_ID
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, SCHED.RELEASE_STATUS
, SCHED.LOAD_TYPE
, L2.LOOKUP_CODE
, L2.MEANING
, MSI.PLANNER_CODE
, TO_NUMBER(NULL)
, MSI.CATEGORY_ID
, MSI.MRP_PLANNING_CODE
, MSI.REPETITIVE_TYPE
, MSI.BUILD_IN_WIP_FLAG
, MSI.PURCHASING_ENABLED_FLAG
, MSI.PLANNING_MAKE_BUY_CODE
, GREATEST(0
, SCHED.FIRST_UNIT_START_DATE - TRUNC(SYSDATE))
, MSI.BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID
, MSI.WIP_SUPPLY_TYPE
, NULL
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, MSI.FULL_PEGGING
, TO_NUMBER(NULL)
FROM WIP_LINES WL
, MFG_LOOKUPS L2
, MFG_LOOKUPS L1
, MRP_SUGG_REP_SCHEDULES SCHED
, MTL_ITEM_FLEXFIELDS FLEX
, MRP_SYSTEM_ITEMS MSI
WHERE WL.LINE_ID = SCHED.REPETITIVE_LINE
AND FLEX.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND FLEX.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND SCHED.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SCHED.COMPILE_DESIGNATOR = MSI.COMPILE_DESIGNATOR
AND SCHED.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND L2.LOOKUP_TYPE (+) = 'MRP_WORKBENCH_IMPLEMENT_AS'
AND L2.LOOKUP_CODE (+) = DECODE(SCHED.RELEASE_STATUS
, 1
, 4)
AND L1.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND L1.LOOKUP_CODE = DECODE(MSI.INVENTORY_ITEM_ID
, NULL
, NULL
, 13) UNION ALL SELECT 'MRP_GROSS_REQUIREMENTS'
, MGR.ROWID
, MGR.DEMAND_ID
, MGR.LAST_UPDATE_DATE
, MGR.LAST_UPDATED_BY
, MGR.CREATION_DATE
, MGR.CREATED_BY
, MGR.LAST_UPDATE_LOGIN
, MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MSI.COMPILE_DESIGNATOR
, MGR.USING_ASSEMBLY_DEMAND_DATE
, MGR.OLD_DEMAND_DATE
, TO_DATE(NULL)
, MGR.DISPOSITION_ID
, DECODE(MGR.ORIGINATION_TYPE
, 2
, WE.WIP_ENTITY_NAME
, 3
, WE.WIP_ENTITY_NAME
, 17
, WE.WIP_ENTITY_NAME
, 18
, IPO.PO_NUMBER
, 19
, IPO.PO_NUMBER
, 20
, IPO.PO_NUMBER
, 23
, IPO.PO_NUMBER
, 24
, IPO.PO_NUMBER
, 6
, SO.SEGMENT1 || ':' || SO.SEGMENT2 || ':' || SO.SEGMENT3
, NULL)
, TO_NUMBER(NULL)
, MGR.ORIGINATION_TYPE
, L1.MEANING
, TO_NUMBER(NULL)
, NULL
, - NVL(MGR.DAILY_DEMAND_RATE
, MGR.USING_REQUIREMENTS_QUANTITY)
, MGR.OLD_DEMAND_QUANTITY
, TO_DATE(NULL)
, TO_NUMBER(NVL(NULL
, 2))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MGR.ASSEMBLY_DEMAND_COMP_DATE
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, MGR.FIRM_QUANTITY
, MGR.FIRM_DATE
, TO_NUMBER(NULL)
, NULL
, NULL
, FLEX.ITEM_NUMBER
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, MSI.PLANNER_CODE
, MGR.USING_ASSEMBLY_ITEM_ID
, MSI.CATEGORY_ID
, MSI.MRP_PLANNING_CODE
, MSI.REPETITIVE_TYPE
, MSI.BUILD_IN_WIP_FLAG
, MSI.PURCHASING_ENABLED_FLAG
, MSI.PLANNING_MAKE_BUY_CODE
, GREATEST(0
, USING_ASSEMBLY_DEMAND_DATE - (TRUNC(SYSDATE) ))
, MSI.BOM_ITEM_TYPE
, MSI.BASE_ITEM_ID
, MSI.WIP_SUPPLY_TYPE
, MTL.ORGANIZATION_CODE
, TO_NUMBER(NULL)
, FLEX2.ITEM_NUMBER
, MSI.FULL_PEGGING
, TO_NUMBER(NULL)
FROM MTL_SALES_ORDERS SO
, MRP_SCHEDULE_DATES DATES
, MFG_LOOKUPS L1
, MRP_ITEM_PURCHASE_ORDERS IPO
, WIP_ENTITIES WE
, MTL_PARAMETERS MTL
, MTL_ITEM_FLEXFIELDS FLEX2
, MRP_GROSS_REQUIREMENTS MGR
, MTL_ITEM_FLEXFIELDS FLEX
, MRP_SYSTEM_ITEMS MSI
WHERE IPO.PURCHASE_ORDER_ID(+) = MGR.DISPOSITION_ID
AND IPO.INVENTORY_ITEM_ID(+) = MGR.INVENTORY_ITEM_ID
AND IPO.COMPILE_DESIGNATOR(+) = MGR.COMPILE_DESIGNATOR
AND IPO.ORGANIZATION_ID(+) = MGR.ORGANIZATION_ID
AND FLEX2.ORGANIZATION_ID = MGR.ORGANIZATION_ID
AND FLEX2.ITEM_ID = MGR.USING_ASSEMBLY_ITEM_ID
AND FLEX.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND FLEX.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WE.ORGANIZATION_ID(+) = MGR.ORGANIZATION_ID
AND WE.WIP_ENTITY_ID(+) = MGR.DISPOSITION_ID
AND DATES.SOURCE_SALES_ORDER_ID = SO.SALES_ORDER_ID (+)
AND DATES.SCHEDULE_LEVEL (+) = 3
AND DATES.MPS_TRANSACTION_ID (+) = MGR.DISPOSITION_ID
AND MTL.ORGANIZATION_ID = MGR.ORGANIZATION_ID
AND MGR.COMPILE_DESIGNATOR = MSI.COMPILE_DESIGNATOR
AND MGR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MGR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND DECODE(MGR.ORIGINATION_TYPE
, 1
, 'MRP_PLANNED_ORDER_DEMAND'
, 3
, 'MRP_PLANNED_ORDER_DEMAND'
, 'MRP_DEMAND_ORIGINATION') = L1.LOOKUP_TYPE
AND L1.LOOKUP_CODE = MGR.ORIGINATION_TYPE

Columns