View: MRP_RHX_PURCHASING_SUPPLY_V

View Text

SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ITEM_NUMBER
, PO.MRP_TO_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID
, PO.PO_HEADER_ID
, PO.VENDOR_ID
, VENDOR.VENDOR_NAME
, 1
, PO.MRP_EXPECTED_DELIVERY_DATE
, PO.EXPECTED_DELIVERY_DATE
, PO.MRP_PRIMARY_QUANTITY
, PO.TO_ORG_PRIMARY_QUANTITY
, PO.MRP_PRIMARY_QUANTITY * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, PO.TO_ORG_PRIMARY_QUANTITY* DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, PO.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.PO_LINE_ID
, PO.MRP_DESTINATION_TYPE_CODE
, PO.DESTINATION_TYPE_CODE
, PL.ATTRIBUTE_CATEGORY
, PL.ATTRIBUTE1
, PL.ATTRIBUTE2
, PL.ATTRIBUTE3
, PL.ATTRIBUTE4
, PL.ATTRIBUTE5
, PL.ATTRIBUTE6
, PL.ATTRIBUTE7
, PL.ATTRIBUTE8
, PL.ATTRIBUTE9
, PL.ATTRIBUTE10
, PL.ATTRIBUTE11
, PL.ATTRIBUTE12
, PL.ATTRIBUTE13
, PL.ATTRIBUTE14
, PL.ATTRIBUTE15
FROM PO_VENDORS VENDOR
, MTL_UNITS_OF_MEASURE UOM
, PO_PO_SUPPLY_VIEW PO
, MRP_RHX_PLANNED_ITEMS_V ITEMS
, PO_LINES_ALL PL
WHERE UOM.UNIT_OF_MEASURE = PO.PRIMARY_UOM
AND VENDOR.VENDOR_ID = PO.VENDOR_ID
AND ITEMS.INVENTORY_ITEM_ID = PO.ITEM_ID
AND ITEMS.ORGANIZATION_ID = PO.TO_ORGANIZATION_ID
AND PO.PO_LINE_ID IS NOT NULL
AND PO.TO_ORG_PRIMARY_QUANTITY > 0
AND PO.DESTINATION_TYPE_CODE = 'INVENTORY'
AND PO.ITEM_ID IS NOT NULL
AND PO.LINE_NUM IS NOT NULL
AND PL.PO_LINE_ID = PO.PO_LINE_ID UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ITEM_NUMBER
, SSV.MRP_TO_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID
, SSV.SHIPMENT_HEADER_ID
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, 11
, SSV.MRP_EXPECTED_DELIVERY_DATE
, SSV.EXPECTED_DELIVERY_DATE
, SSV.MRP_PRIMARY_QUANTITY
, SSV.TO_ORG_PRIMARY_QUANTITY
, SSV.MRP_PRIMARY_QUANTITY * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, (NVL(ITEMS.SHRINKAGE_RATE
, 0)))
, SSV.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
, RT.ATTRIBUTE_CATEGORY
, RT.ATTRIBUTE1
, RT.ATTRIBUTE2
, RT.ATTRIBUTE3
, RT.ATTRIBUTE4
, RT.ATTRIBUTE5
, RT.ATTRIBUTE6
, RT.ATTRIBUTE7
, RT.ATTRIBUTE8
, RT.ATTRIBUTE9
, RT.ATTRIBUTE10
, RT.ATTRIBUTE11
, RT.ATTRIBUTE12
, RT.ATTRIBUTE13
, RT.ATTRIBUTE14
, RT.ATTRIBUTE15
FROM MTL_UNITS_OF_MEASURE UOM
, PO_SHIP_SUPPLY_VIEW SSV
, MRP_RHX_PLANNED_ITEMS_V ITEMS
, MTL_SUPPLY MS
, RCV_TRANSACTIONS RT
WHERE UOM.UNIT_OF_MEASURE = SSV.PRIMARY_UOM
AND ITEMS.INVENTORY_ITEM_ID = SSV.ITEM_ID
AND ITEMS.ORGANIZATION_ID = SSV.TO_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
AND SSV.TO_ORG_PRIMARY_QUANTITY > 0
AND SSV.DESTINATION_TYPE_CODE = 'INVENTORY'
AND MS.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND MS.SHIPMENT_HEADER_ID = SSV.SHIPMENT_HEADER_ID
AND MS.SHIPMENT_LINE_ID = SSV.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID = MS.RCV_TRANSACTION_ID UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ITEM_NUMBER
, REQS.MRP_TO_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID
, REQS.REQUISITION_HEADER_ID
, VENDOR.VENDOR_ID
, REQS.SUGGESTED_VENDOR_NAME
, 2
, REQS.MRP_EXPECTED_DELIVERY_DATE
, REQS.EXPECTED_DELIVERY_DATE
, REQS.MRP_PRIMARY_QUANTITY
, REQS.TO_ORG_PRIMARY_QUANTITY
, NVL(REQS.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
, 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
, PRL.ATTRIBUTE_CATEGORY
, PRL.ATTRIBUTE1
, PRL.ATTRIBUTE2
, PRL.ATTRIBUTE3
, PRL.ATTRIBUTE4
, PRL.ATTRIBUTE5
, PRL.ATTRIBUTE6
, PRL.ATTRIBUTE7
, PRL.ATTRIBUTE8
, PRL.ATTRIBUTE9
, PRL.ATTRIBUTE10
, PRL.ATTRIBUTE11
, PRL.ATTRIBUTE12
, PRL.ATTRIBUTE13
, PRL.ATTRIBUTE14
, PRL.ATTRIBUTE15
FROM PO_VENDORS VENDOR
, MTL_UNITS_OF_MEASURE UOM
, PO_REQ_SUPPLY_VIEW REQS
, MRP_RHX_PLANNED_ITEMS_V ITEMS
, PO_REQUISITION_LINES_ALL PRL
WHERE VENDOR.VENDOR_NAME(+) = REQS.SUGGESTED_VENDOR_NAME
AND UOM.UNIT_OF_MEASURE = REQS.PRIMARY_UOM
AND ITEMS.INVENTORY_ITEM_ID = REQS.ITEM_ID
AND ITEMS.ORGANIZATION_ID = REQS.TO_ORGANIZATION_ID
AND REQS.LINE_NUM IS NOT NULL
AND REQS.REQ_LINE_ID IS NOT NULL
AND REQS.ITEM_ID IS NOT NULL
AND REQS.TO_ORG_PRIMARY_QUANTITY > 0
AND REQS.DESTINATION_TYPE_CODE = 'INVENTORY'
AND PRL.REQUISITION_LINE_ID = REQS.REQ_LINE_ID UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ITEM_NUMBER
, RCV_SHIP.MRP_TO_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID
, RCV_SHIP.SHIPMENT_HEADER_ID
, TO_NUMBER(NULL)
, TO_CHAR(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
, RT.ATTRIBUTE_CATEGORY
, RT.ATTRIBUTE1
, RT.ATTRIBUTE2
, RT.ATTRIBUTE3
, RT.ATTRIBUTE4
, RT.ATTRIBUTE5
, RT.ATTRIBUTE6
, RT.ATTRIBUTE7
, RT.ATTRIBUTE8
, RT.ATTRIBUTE9
, RT.ATTRIBUTE10
, RT.ATTRIBUTE11
, RT.ATTRIBUTE12
, RT.ATTRIBUTE13
, RT.ATTRIBUTE14
, RT.ATTRIBUTE15
FROM PO_SHIP_RCV_SUPPLY_VIEW RCV_SHIP
, MRP_RHX_PLANNED_ITEMS_V ITEMS
, MTL_SUPPLY MS
, RCV_TRANSACTIONS RT
WHERE ITEMS.INVENTORY_ITEM_ID = RCV_SHIP.ITEM_ID
AND ITEMS.ORGANIZATION_ID = RCV_SHIP.TO_ORGANIZATION_ID
AND RCV_SHIP.SHIPMENT_LINE_NUM IS NOT NULL
AND RCV_SHIP.ITEM_ID IS NOT NULL
AND RCV_SHIP.TO_ORG_PRIMARY_QUANTITY > 0
AND RCV_SHIP.DESTINATION_TYPE_CODE = 'INVENTORY'
AND MS.SUPPLY_TYPE_CODE = 'RECEIVING'
AND MS.SHIPMENT_HEADER_ID = RCV_SHIP.SHIPMENT_HEADER_ID
AND MS.SHIPMENT_LINE_ID = RCV_SHIP.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID = MS.RCV_TRANSACTION_ID UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ITEM_NUMBER
, PO.MRP_TO_ORGANIZATION_ID
, ITEMS.ORGANIZATION_ID
, PO.PO_HEADER_ID
, PO.VENDOR_ID
, VENDOR.VENDOR_NAME
, 8
, PO.MRP_EXPECTED_DELIVERY_DATE
, PO.EXPECTED_DELIVERY_DATE
, PO.MRP_PRIMARY_QUANTITY
, PO.TO_ORG_PRIMARY_QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PO.RCV_TRX_QUANTITY
, PO.UNIT_PRICE
, 1
, PO.LINE_NUM
, 1
, PO.DOCK_DATE
, PO.PO_NUMBER
, PO.ITEM_REVISION
, TO_DATE(NULL)
, PO.RCV_TRX_UOM_CODE
, PO.PO_LINE_ID
, PO.MRP_DESTINATION_TYPE_CODE
, PO.DESTINATION_TYPE_CODE
, PL.ATTRIBUTE_CATEGORY
, PL.ATTRIBUTE1
, PL.ATTRIBUTE2
, PL.ATTRIBUTE3
, PL.ATTRIBUTE4
, PL.ATTRIBUTE5
, PL.ATTRIBUTE6
, PL.ATTRIBUTE7
, PL.ATTRIBUTE8
, PL.ATTRIBUTE9
, PL.ATTRIBUTE10
, PL.ATTRIBUTE11
, PL.ATTRIBUTE12
, PL.ATTRIBUTE13
, PL.ATTRIBUTE14
, PL.ATTRIBUTE15
FROM PO_VENDORS VENDOR
, PO_RCV_SUPPLY_VIEW PO
, MRP_RHX_PLANNED_ITEMS_V ITEMS
, PO_LINES_ALL PL
WHERE ITEMS.INVENTORY_ITEM_ID = PO.ITEM_ID
AND VENDOR.VENDOR_ID = PO.VENDOR_ID
AND ITEMS.ORGANIZATION_ID = PO.TO_ORGANIZATION_ID
AND PO.LINE_NUM IS NOT NULL
AND PO.ITEM_ID IS NOT NULL
AND PO.PRIMARY_UOM_QUANTITY > 0
AND PO.DESTINATION_TYPE_CODE = 'INVENTORY'
AND PL.PO_LINE_ID = PO.PO_LINE_ID

Columns