View: MRP_FLP_ONHAND_V

Product:	MRP - Master Scheduling/MRP
Description:	View of on hand information
Implementation/DBA Data:	APPS.MRP_FLP_ONHAND_V

View Text

SELECT MFP2.ROWID
, MFP2.COMPILE_DESIGNATOR
, MFP2.ORGANIZATION_ID
, MFP2.PEGGING_ID
, MFP2.PREV_PEGGING_ID
, MFP2.DEMAND_ID
, MFP2.DEMAND_DATE
, MFP2.TRANSACTION_ID
, MFP2.END_ITEM_USAGE
, MFP2.INVENTORY_ITEM_ID
, MIF2.ITEM_NUMBER||'/'||MP2.ORGANIZATION_CODE
, ROUND(MOQ2.NETTABLE_QUANTITY
, 2)
, ROUND(MFP2.ALLOCATED_QUANTITY
, 2)
, TO_DATE(NULL)
, ROUND(MFP2.DEMAND_QUANTITY
, 2)
, ML2.MEANING
, MRP_GET_PROJECT.PROJECT(MOQ2.PROJECT_ID)
, MRP_GET_PROJECT.TASK(MOQ2.TASK_ID)
, MFP2.SUPPLY_TYPE
, DECODE(MFP2.END_ITEM_USAGE
, NULL
, 0
, ROUND(MFP2.ALLOCATED_QUANTITY/MFP2.END_ITEM_USAGE
, 2))
, DECODE(MFP3.DEMAND_ID
, -1
, ROUND(MFP3.ALLOCATED_QUANTITY
, 2)
, ROUND(MFP3.DEMAND_QUANTITY
, 2))
, DECODE(MFP3.DEMAND_ID
, -1
, MFP3.SUPPLY_DATE
, MFP3.DEMAND_DATE)
, MIF3.ITEM_NUMBER||'/'||MP3.ORGANIZATION_CODE
, SUBSTR(MIF3.DESCRIPTION
, 1
, 20)
, ML3.MEANING
, DECODE(MGR3.ORIGINATION_TYPE
, 2
, WE3.WIP_ENTITY_NAME
, 3
, WE3.WIP_ENTITY_NAME
, 17
, WE3.WIP_ENTITY_NAME
, 18
, MIP3.PO_NUMBER
, 19
, MIP3.PO_NUMBER
, 20
, MIP3.PO_NUMBER
, 23
, MIP3.PO_NUMBER
, 24
, MIP3.PO_NUMBER
, 25
, WE3.WIP_ENTITY_NAME
, 26
, WE3.WIP_ENTITY_NAME
, 6
, SUBSTR(MSO3.SEGMENT1
, 1
, 40)
, '')
, MRP_GET_PROJECT.PROJECT(MFP3.PROJECT_ID)
, MRP_GET_PROJECT.TASK(MFP3.TASK_ID)
, SL3.DEMAND_CLASS_CODE
, SL3.ATTRIBUTE1
, SL3.ATTRIBUTE2
, SL3.ATTRIBUTE3
, SL3.ATTRIBUTE4
, SL3.ATTRIBUTE5
, SL3.ATTRIBUTE6
, SL3.ATTRIBUTE7
, SL3.ATTRIBUTE8
, SL3.ATTRIBUTE9
, SL3.ATTRIBUTE10
, SL3.ATTRIBUTE11
, SL3.ATTRIBUTE12
, SL3.ATTRIBUTE13
, SL3.ATTRIBUTE14
, SL3.ATTRIBUTE15
, MOQ2.END_ITEM_UNIT_NUMBER
, MGR3.END_ITEM_UNIT_NUMBER
, MRP_GET_PROJECT.PROJECT(MGR3.LENDING_PROJECT_ID)
, MRP_GET_PROJECT.TASK(MGR3.LENDING_TASK_ID)
FROM MFG_LOOKUPS ML3
, MFG_LOOKUPS ML2
, MRP_ITEM_PURCHASE_ORDERS MIP3
, WIP_ENTITIES WE3
, MTL_ITEM_FLEXFIELDS MIF3
, MTL_ITEM_FLEXFIELDS MIF2
, MTL_PARAMETERS MP2
, MTL_PARAMETERS MP3
, MRP_ONHAND_QUANTITIES MOQ2
, OE_ORDER_LINES_ALL SL3
, MTL_DEMAND MD3
, MTL_SALES_ORDERS MSO3
, MRP_SCHEDULE_DATES MSD3
, MRP_GROSS_REQUIREMENTS MGR3
, MRP_FULL_PEGGING MFP3
, MRP_FULL_PEGGING MFP2
WHERE MFP3.DEMAND_ID IN (-1
, -2
, -3)
AND ML3.LOOKUP_CODE = MFP3.DEMAND_ID
AND ML3.LOOKUP_TYPE = 'MRP_FLP_SUPPLY_DEMAND_TYPE'
AND MSO3.SALES_ORDER_ID(+) = MSD3.SOURCE_SALES_ORDER_ID
AND MSD3.MPS_TRANSACTION_ID(+) = MGR3.DISPOSITION_ID
AND MSD3.SCHEDULE_LEVEL(+) = 2
AND MSD3.SUPPLY_DEMAND_TYPE(+) = 1
AND SL3.LINE_ID(+) = MD3.DEMAND_SOURCE_LINE
AND MD3.DEMAND_ID(+) = MGR3.RESERVATION_ID
AND MIP3.TRANSACTION_ID(+) = MGR3.DISPOSITION_ID
AND WE3.WIP_ENTITY_ID(+) = MGR3.DISPOSITION_ID
AND WE3.ORGANIZATION_ID(+) = MGR3.ORGANIZATION_ID
AND MGR3.DEMAND_ID(+) = MFP3.DEMAND_ID
AND MP3.ORGANIZATION_ID = MFP3.ORGANIZATION_ID
AND MIF3.INVENTORY_ITEM_ID = MFP3.INVENTORY_ITEM_ID
AND MIF3.ORGANIZATION_ID = MFP3.ORGANIZATION_ID
AND MFP3.PEGGING_ID = MFP2.END_PEGGING_ID
AND MOQ2.TRANSACTION_ID = MFP2.TRANSACTION_ID
AND ML2.LOOKUP_CODE = MFP2.SUPPLY_TYPE
AND ML2.LOOKUP_TYPE = 'MRP_FLP_SUPPLY_DEMAND_TYPE'
AND MP2.ORGANIZATION_ID = MFP2.ORGANIZATION_ID
AND MIF2.INVENTORY_ITEM_ID = MFP2.INVENTORY_ITEM_ID
AND MIF2.ORGANIZATION_ID = MFP2.ORGANIZATION_ID
AND MFP2.SUPPLY_TYPE = 18 UNION ALL SELECT MFP2.ROWID
, MFP2.COMPILE_DESIGNATOR
, MFP2.ORGANIZATION_ID
, MFP2.PEGGING_ID
, MFP2.PREV_PEGGING_ID
, MFP2.DEMAND_ID
, MFP2.DEMAND_DATE
, MFP2.TRANSACTION_ID
, MFP2.END_ITEM_USAGE
, MFP2.INVENTORY_ITEM_ID
, MIF2.ITEM_NUMBER||'/'||MP2.ORGANIZATION_CODE
, ROUND(MOQ2.NETTABLE_QUANTITY
, 2)
, ROUND(MFP2.ALLOCATED_QUANTITY
, 2)
, TO_DATE(NULL)
, ROUND(MFP2.DEMAND_QUANTITY
, 2)
, ML2.MEANING
, MRP_GET_PROJECT.PROJECT(MOQ2.PROJECT_ID)
, MRP_GET_PROJECT.TASK(MOQ2.TASK_ID)
, MFP2.SUPPLY_TYPE
, DECODE(MFP2.END_ITEM_USAGE
, NULL
, 0
, ROUND(MFP2.ALLOCATED_QUANTITY/MFP2.END_ITEM_USAGE
, 2))
, DECODE(MFP3.DEMAND_ID
, -1
, ROUND(MFP3.ALLOCATED_QUANTITY
, 2)
, ROUND(MFP3.DEMAND_QUANTITY
, 2))
, DECODE(MFP3.DEMAND_ID
, -1
, MFP3.SUPPLY_DATE
, MFP3.DEMAND_DATE)
, MIF3.ITEM_NUMBER||'/'||MP3.ORGANIZATION_CODE
, SUBSTR(MIF3.DESCRIPTION
, 1
, 20)
, ML3.MEANING
, DECODE(MGR3.ORIGINATION_TYPE
, 2
, WE3.WIP_ENTITY_NAME
, 3
, WE3.WIP_ENTITY_NAME
, 17
, WE3.WIP_ENTITY_NAME
, 18
, MIP3.PO_NUMBER
, 19
, MIP3.PO_NUMBER
, 20
, MIP3.PO_NUMBER
, 23
, MIP3.PO_NUMBER
, 24
, MIP3.PO_NUMBER
, 25
, WE3.WIP_ENTITY_NAME
, 26
, WE3.WIP_ENTITY_NAME
, 6
, SUBSTR(MSO3.SEGMENT1
, 1
, 40)
, '')
, MRP_GET_PROJECT.PROJECT(MFP3.PROJECT_ID)
, MRP_GET_PROJECT.TASK(MFP3.TASK_ID)
, SL3.DEMAND_CLASS_CODE
, SL3.ATTRIBUTE1
, SL3.ATTRIBUTE2
, SL3.ATTRIBUTE3
, SL3.ATTRIBUTE4
, SL3.ATTRIBUTE5
, SL3.ATTRIBUTE6
, SL3.ATTRIBUTE7
, SL3.ATTRIBUTE8
, SL3.ATTRIBUTE9
, SL3.ATTRIBUTE10
, SL3.ATTRIBUTE11
, SL3.ATTRIBUTE12
, SL3.ATTRIBUTE13
, SL3.ATTRIBUTE14
, SL3.ATTRIBUTE15
, MOQ2.END_ITEM_UNIT_NUMBER
, MGR3.END_ITEM_UNIT_NUMBER
, MRP_GET_PROJECT.PROJECT(MGR3.LENDING_PROJECT_ID)
, MRP_GET_PROJECT.TASK(MGR3.LENDING_TASK_ID)
FROM MFG_LOOKUPS ML3
, MFG_LOOKUPS ML2
, MRP_ITEM_PURCHASE_ORDERS MIP3
, WIP_ENTITIES WE3
, MTL_ITEM_FLEXFIELDS MIF3
, MTL_ITEM_FLEXFIELDS MIF2
, MTL_PARAMETERS MP2
, MTL_PARAMETERS MP3
, MRP_ONHAND_QUANTITIES MOQ2
, OE_ORDER_LINES_ALL SL3
, MTL_DEMAND MD3
, MTL_SALES_ORDERS MSO3
, MRP_SCHEDULE_DATES MSD3
, MRP_GROSS_REQUIREMENTS MGR3
, MRP_FULL_PEGGING MFP3
, MRP_FULL_PEGGING MFP2
WHERE MFP3.DEMAND_ID NOT IN (-1
, -2
, -3)
AND ML3.LOOKUP_CODE = MGR3.ORIGINATION_TYPE
AND ML3.LOOKUP_TYPE = 'MRP_DEMAND_ORIGINATION'
AND MSO3.SALES_ORDER_ID(+) = MSD3.SOURCE_SALES_ORDER_ID
AND MSD3.MPS_TRANSACTION_ID(+) = MGR3.DISPOSITION_ID
AND MSD3.SCHEDULE_LEVEL(+) = 2
AND MSD3.SUPPLY_DEMAND_TYPE(+) = 1
AND SL3.LINE_ID(+) = MD3.DEMAND_SOURCE_LINE
AND MD3.DEMAND_ID(+) = MGR3.RESERVATION_ID
AND MIP3.TRANSACTION_ID(+) = MGR3.DISPOSITION_ID
AND WE3.WIP_ENTITY_ID(+) = MGR3.DISPOSITION_ID
AND WE3.ORGANIZATION_ID(+) = MGR3.ORGANIZATION_ID
AND MGR3.DEMAND_ID(+) = MFP3.DEMAND_ID
AND MP3.ORGANIZATION_ID = MFP3.ORGANIZATION_ID
AND MIF3.INVENTORY_ITEM_ID = MFP3.INVENTORY_ITEM_ID
AND MIF3.ORGANIZATION_ID = MFP3.ORGANIZATION_ID
AND MFP3.PEGGING_ID = MFP2.END_PEGGING_ID
AND MOQ2.TRANSACTION_ID = MFP2.TRANSACTION_ID
AND ML2.LOOKUP_CODE = MFP2.SUPPLY_TYPE
AND ML2.LOOKUP_TYPE = 'MRP_FLP_SUPPLY_DEMAND_TYPE'
AND MP2.ORGANIZATION_ID = MFP2.ORGANIZATION_ID
AND MIF2.INVENTORY_ITEM_ID = MFP2.INVENTORY_ITEM_ID
AND MIF2.ORGANIZATION_ID = MFP2.ORGANIZATION_ID
AND MFP2.SUPPLY_TYPE = 18

Columns