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_ORDERS_V

Source

View Text - Preformatted


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 ((MGR.ORIGINATION_TYPE in (1,3,25) AND L1.LOOKUP_TYPE = 'MRP_PLANNED_ORDER_DEMAND') OR (MGR.ORIGINATION_TYPE in (2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 26, 27, 28, 29, 30, 31, 42, 45) AND L1.LOOKUP_TYPE = 'MRP_DEMAND_ORIGINATION')) AND L1.LOOKUP_CODE = MGR.ORIGINATION_TYPE

View Text - HTML Formatted

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 ((MGR.ORIGINATION_TYPE IN (1
, 3
, 25)
AND L1.LOOKUP_TYPE = 'MRP_PLANNED_ORDER_DEMAND') OR (MGR.ORIGINATION_TYPE IN (2
, 4
, 5
, 6
, 7
, 8
, 9
, 10
, 11
, 12
, 13
, 14
, 15
, 16
, 17
, 18
, 19
, 20
, 21
, 22
, 23
, 24
, 26
, 27
, 28
, 29
, 30
, 31
, 42
, 45)
AND L1.LOOKUP_TYPE = 'MRP_DEMAND_ORIGINATION'))
AND L1.LOOKUP_CODE = MGR.ORIGINATION_TYPE