View: MRP_PO_SUPPLY_VIEW

Product:	MRP - Master Scheduling/MRP
Description:	Purchasing supply view
Implementation/DBA Data:	APPS.MRP_PO_SUPPLY_VIEW

View Text

SELECT PO.ITEM_ID
, ITEMS.COMPILE_DESIGNATOR
, PO.MRP_TO_ORGANIZATION_ID
, PO.TO_ORGANIZATION_ID
, PO.PO_HEADER_ID
, PO.VENDOR_ID
, 1
, PO.MRP_EXPECTED_DELIVERY_DATE
, PO.EXPECTED_DELIVERY_DATE
, MRP_PRIMARY_QUANTITY
, TO_ORG_PRIMARY_QUANTITY
, MRP_PRIMARY_QUANTITY * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, TO_ORG_PRIMARY_QUANTITY* DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, QUANTITY
, PO.UNIT_PRICE
, 1
, PO.LINE_NUM
, DECODE(PO.FIRM_PLANNED_STATUS_FLAG
, 'Y'
, 1
, 2)
, PO.EXPECTED_DOCK_DATE
, PO.PO_NUMBER
, PO.ITEM_REVISION
, PO.PRINTED_DATE
, UOM.UOM_CODE
, PO_LINE_ID
, PO.MRP_DESTINATION_TYPE_CODE
, PO.DESTINATION_TYPE_CODE
, PO.PROJECT_ID
, PO.TASK_ID
, PO.END_ITEM_UNIT_NUMBER
, PO.TO_SUBINVENTORY
, PO.VENDOR_SITE_ID
, TO_NUMBER(NULL)
FROM MTL_UNITS_OF_MEASURE UOM
, MRP_SYSTEM_ITEMS ITEMS
, PO_PO_SUPPLY_VIEW PO
WHERE UOM.UNIT_OF_MEASURE = PO.PRIMARY_UOM
AND ITEMS.INVENTORY_ITEM_ID = PO.ITEM_ID
AND ITEMS.ORGANIZATION_ID = PO.ORGANIZATION_ID
AND PO.PO_LINE_ID IS NOT NULL
AND PO.ITEM_ID IS NOT NULL
AND PO.LINE_NUM IS NOT NULL UNION ALL SELECT SSV.ITEM_ID
, ITEMS.COMPILE_DESIGNATOR
, SSV.MRP_TO_ORGANIZATION_ID
, SSV.TO_ORGANIZATION_ID
, SSV.SHIPMENT_HEADER_ID
, TO_NUMBER(NULL)
, 11
, SSV.MRP_EXPECTED_DELIVERY_DATE
, SSV.EXPECTED_DELIVERY_DATE
, MRP_PRIMARY_QUANTITY
, TO_ORG_PRIMARY_QUANTITY
, MRP_PRIMARY_QUANTITY * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, TO_ORG_PRIMARY_QUANTITY * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, SSV.SHIPMENT_QUANTITY
, TO_NUMBER(NULL)
, 1
, SSV.SHIPMENT_LINE_NUM
, 2
, SSV.DOCK_DATE
, SSV.SHIPMENT_HEADER_NUM
, TO_CHAR(NULL)
, TO_DATE(NULL)
, UOM.UOM_CODE
, SSV.SHIPMENT_LINE_ID
, SSV.MRP_DESTINATION_TYPE_CODE
, SSV.DESTINATION_TYPE_CODE
, SSV.PROJECT_ID
, SSV.TASK_ID
, SSV.END_ITEM_UNIT_NUMBER
, SSV.TO_SUBINVENTORY
, TO_NUMBER(NULL)
, SSV.FROM_ORGANIZATION_ID
FROM MTL_UNITS_OF_MEASURE UOM
, MRP_SYSTEM_ITEMS ITEMS
, PO_SHIP_SUPPLY_VIEW SSV
WHERE UOM.UNIT_OF_MEASURE = SSV.PRIMARY_UOM
AND ITEMS.INVENTORY_ITEM_ID = SSV.ITEM_ID
AND ITEMS.ORGANIZATION_ID = SSV.ORGANIZATION_ID
AND SSV.SHIPMENT_LINE_NUM IS NOT NULL
AND SSV.SHIPMENT_LINE_ID IS NOT NULL
AND SSV.ITEM_ID IS NOT NULL UNION ALL SELECT REQS.ITEM_ID
, ITEMS.COMPILE_DESIGNATOR
, REQS.MRP_TO_ORGANIZATION_ID
, REQS.TO_ORGANIZATION_ID
, REQS.REQUISITION_HEADER_ID
, VENDOR.VENDOR_ID
, 2
, REQS.MRP_EXPECTED_DELIVERY_DATE
, REQS.EXPECTED_DELIVERY_DATE
, REQS.MRP_PRIMARY_QUANTITY
, REQS.TO_ORG_PRIMARY_QUANTITY
, NVL(MRP_PRIMARY_QUANTITY
, 0) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, NVL(REQS.TO_ORG_PRIMARY_QUANTITY
, 0) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, REQS.QUANTITY
, REQS.UNIT_PRICE
, 1
, REQS.LINE_NUM
, DECODE (FND_PROFILE.VALUE_WNPS ('MRP_FIRM_OE_XFERRED_INTERNAL_REQ')
, 'Y'
, DECODE(REQS.TRANSFERRED_TO_OE_FLAG
, 'Y'
, 1
, 2)
, 2)
, REQS.EXPECTED_DOCK_DATE
, REQS.REQUISITION_NUMBER
, TO_CHAR(NULL)
, TO_DATE(NULL)
, UOM.UOM_CODE
, REQS.REQ_LINE_ID
, REQS.MRP_DESTINATION_TYPE_CODE
, REQS.DESTINATION_TYPE_CODE
, REQS.PROJECT_ID
, REQS.TASK_ID
, REQS.END_ITEM_UNIT_NUMBER
, REQS.TO_SUBINVENTORY
, SITES.VENDOR_SITE_ID
, REQS.FROM_ORGANIZATION_ID
FROM PO_VENDOR_SITES_ALL_V SITES
, PO_VENDORS_VIEW VENDOR
, MTL_UNITS_OF_MEASURE UOM
, MRP_SYSTEM_ITEMS ITEMS
, PO_REQ_SUPPLY_VIEW REQS
WHERE VENDOR.VENDOR_NAME(+) = REQS.SUGGESTED_VENDOR_NAME
AND SITES.VENDOR_NAME(+) = REQS.SUGGESTED_VENDOR_NAME
AND SITES.VENDOR_SITE_CODE(+) = REQS.SUGGESTED_VENDOR_LOCATION
AND UOM.UNIT_OF_MEASURE = REQS.PRIMARY_UOM
AND ITEMS.INVENTORY_ITEM_ID = REQS.ITEM_ID
AND ITEMS.ORGANIZATION_ID = REQS.ORGANIZATION_ID
AND REQS.LINE_NUM IS NOT NULL
AND REQS.REQ_LINE_ID IS NOT NULL
AND REQS.ITEM_ID IS NOT NULL UNION ALL SELECT RCV_SHIP.ITEM_ID
, ITEMS.COMPILE_DESIGNATOR
, RCV_SHIP.MRP_TO_ORGANIZATION_ID
, RCV_SHIP.TO_ORGANIZATION_ID
, RCV_SHIP.SHIPMENT_HEADER_ID
, TO_NUMBER(NULL)
, 12
, RCV_SHIP.MRP_EXPECTED_DELIVERY_DATE
, RCV_SHIP.EXPECTED_DELIVERY_DATE
, RCV_SHIP.MRP_PRIMARY_QUANTITY
, RCV_SHIP.TO_ORG_PRIMARY_QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, RCV_SHIP.RCV_TRX_QUANTITY
, TO_NUMBER(NULL)
, 1
, RCV_SHIP.SHIPMENT_LINE_NUM
, 1
, RCV_SHIP.DOCK_DATE
, RCV_SHIP.SHIPMENT_NUM
, TO_CHAR(NULL)
, TO_DATE(NULL)
, RCV_SHIP.RCV_TRX_UOM_CODE
, RCV_SHIP.SHIPMENT_LINE_ID
, RCV_SHIP.MRP_DESTINATION_TYPE_CODE
, RCV_SHIP.DESTINATION_TYPE_CODE
, RCV_SHIP.PROJECT_ID
, RCV_SHIP.TASK_ID
, RCV_SHIP.END_ITEM_UNIT_NUMBER
, RCV_SHIP.TO_SUBINVENTORY
, TO_NUMBER(NULL)
, RCV_SHIP.FROM_ORGANIZATION_ID
FROM MRP_SYSTEM_ITEMS ITEMS
, PO_SHIP_RCV_SUPPLY_VIEW RCV_SHIP
WHERE ITEMS.INVENTORY_ITEM_ID = RCV_SHIP.ITEM_ID
AND ITEMS.ORGANIZATION_ID = RCV_SHIP.ORGANIZATION_ID
AND RCV_SHIP.SHIPMENT_LINE_NUM IS NOT NULL
AND RCV_SHIP.ITEM_ID IS NOT NULL UNION ALL SELECT PO.ITEM_ID
, ITEMS.COMPILE_DESIGNATOR
, PO.MRP_TO_ORGANIZATION_ID
, PO.TO_ORGANIZATION_ID
, PO.PO_HEADER_ID
, PO.VENDOR_ID
, 8
, PO.MRP_EXPECTED_DELIVERY_DATE
, PO.EXPECTED_DELIVERY_DATE
, MRP_PRIMARY_QUANTITY
, PO.PRIMARY_UOM_QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, RCV_TRX_QUANTITY
, PO.UNIT_PRICE
, 1
, PO.LINE_NUM
, 1
, PO.DOCK_DATE
, PO.PO_NUMBER
, ITEM_REVISION
, TO_DATE(NULL)
, RCV_TRX_UOM_CODE
, PO_LINE_ID
, PO.MRP_DESTINATION_TYPE_CODE
, PO.DESTINATION_TYPE_CODE
, PO.PROJECT_ID
, PO.TASK_ID
, PO.END_ITEM_UNIT_NUMBER
, PO.TO_SUBINVENTORY
, PO.VENDOR_SITE_ID
, TO_NUMBER(NULL)
FROM MRP_SYSTEM_ITEMS ITEMS
, PO_RCV_SUPPLY_VIEW PO
WHERE ITEMS.INVENTORY_ITEM_ID = PO.ITEM_ID
AND ITEMS.ORGANIZATION_ID = PO.ORGANIZATION_ID
AND PO.LINE_NUM IS NOT NULL
AND PO.ITEM_ID IS NOT NULL

Columns