View: PO_SHIP_SUPPLY_VIEW

Product:	PO - Purchasing
Description:	Purchasing shipment supply
Implementation/DBA Data:	APPS.PO_SHIP_SUPPLY_VIEW

View Text

SELECT MS.ITEM_ID
, MS.TO_ORGANIZATION_ID
, MS.SHIPMENT_HEADER_ID
, MS.RECEIPT_DATE
, RSH.SHIPMENT_NUM
, DECODE(MS.REQ_LINE_ID
, NULL
, MS.QUANTITY
, NVL(MS.QUANTITY
, 0) * PD.REQ_LINE_QUANTITY / PL.QUANTITY)
, MUOM.UOM_CODE
, DECODE(MS.REQ_LINE_ID
, NULL
, MS.TO_ORG_PRIMARY_QUANTITY
, NVL(MS.TO_ORG_PRIMARY_QUANTITY
, 0) * PD.REQ_LINE_QUANTITY / PL.QUANTITY)
, MS.TO_ORG_PRIMARY_UOM
, RSL.LINE_NUM
, MS.DESTINATION_TYPE_CODE
, MS.MRP_EXPECTED_DELIVERY_DATE
, DECODE(MS.REQ_LINE_ID
, NULL
, NVL(MS.MRP_PRIMARY_QUANTITY
, 0)
, NVL(MS.MRP_PRIMARY_QUANTITY
, 0) * PD.REQ_LINE_QUANTITY/ PL.QUANTITY)
, MS.MRP_PRIMARY_UOM
, MS.MRP_DESTINATION_TYPE_CODE
, MS.MRP_TO_ORGANIZATION_ID
, MS.MRP_TO_SUBINVENTORY
, MS.SHIPMENT_LINE_ID
, MS.ITEM_REVISION
, MS.EXPECTED_DELIVERY_DATE
, DECODE(MS.REQ_LINE_ID
, NULL
, MS.TO_ORG_PRIMARY_QUANTITY
, NVL(MS.TO_ORG_PRIMARY_QUANTITY
, 0) * PD.REQ_LINE_QUANTITY / PL.QUANTITY)
, MS.TO_ORGANIZATION_ID
, DECODE(MS.REQ_LINE_ID
, NULL
, TO_NUMBER(NULL)
, PD.PROJECT_ID)
, DECODE(MS.REQ_LINE_ID
, NULL
, TO_NUMBER(NULL)
, PD.TASK_ID )
, DECODE(MS.REQ_LINE_ID
, NULL
, SUBSTR(PO_UNITEFF_PKG.GET_UNIT_NUMBER(MS.SHIPMENT_LINE_ID
, MS.ITEM_ID)
, 0
, 30)
, PD.END_ITEM_UNIT_NUMBER)
, MS.TO_SUBINVENTORY
, MS.FROM_ORGANIZATION_ID
FROM MTL_UNITS_OF_MEASURE MUOM
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_REQUISITION_LINES_ALL PL
, PO_REQ_DISTRIBUTIONS_ALL PD
, MTL_SUPPLY MS
WHERE MS.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND MS.REQ_LINE_ID = PL.REQUISITION_LINE_ID (+)
AND MS.REQ_LINE_ID = PD.REQUISITION_LINE_ID (+)
AND MS.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND MS.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND DECODE(REQ_LINE_ID
, NULL
, 1
, PL.QUANTITY) > 0
AND MUOM.UNIT_OF_MEASURE = MS.UNIT_OF_MEASURE
AND MS.PO_LINE_LOCATION_ID IS NULL UNION ALL SELECT MS.ITEM_ID
, MS.TO_ORGANIZATION_ID
, MS.SHIPMENT_HEADER_ID
, MS.RECEIPT_DATE
, RSH.SHIPMENT_NUM
, DECODE(MS.PO_LINE_LOCATION_ID
, NULL
, MS.QUANTITY
, NVL(MS.QUANTITY
, 0) * PD.QUANTITY_ORDERED / PL.QUANTITY) A1
, MUOM.UOM_CODE
, DECODE(MS.PO_LINE_LOCATION_ID
, NULL
, MS.TO_ORG_PRIMARY_QUANTITY
, NVL(MS.TO_ORG_PRIMARY_QUANTITY
, 0) * PD.QUANTITY_ORDERED/PL.QUANTITY) A2
, MS.TO_ORG_PRIMARY_UOM
, RSL.LINE_NUM
, MS.DESTINATION_TYPE_CODE
, MS.MRP_EXPECTED_DELIVERY_DATE
, DECODE(MS.PO_LINE_LOCATION_ID
, NULL
, NVL( MS.MRP_PRIMARY_QUANTITY
, 0)
, NVL(MS.MRP_PRIMARY_QUANTITY
, 0) * PD.QUANTITY_ORDERED/ PL.QUANTITY) A3
, MS.MRP_PRIMARY_UOM
, MS.MRP_DESTINATION_TYPE_CODE
, MS.MRP_TO_ORGANIZATION_ID
, MS.MRP_TO_SUBINVENTORY
, MS.SHIPMENT_LINE_ID
, MS.ITEM_REVISION
, MS.EXPECTED_DELIVERY_DATE
, DECODE(MS.PO_LINE_LOCATION_ID
, NULL
, MS.TO_ORG_PRIMARY_QUANTITY
, NVL(MS.TO_ORG_PRIMARY_QUANTITY
, 0) * PD.QUANTITY_ORDERED / PL.QUANTITY) A4
, MS.TO_ORGANIZATION_ID A5
, DECODE(MS.PO_LINE_LOCATION_ID
, NULL
, TO_NUMBER(NULL)
, PD.PROJECT_ID) A6
, DECODE(MS.PO_LINE_LOCATION_ID
, NULL
, TO_NUMBER(NULL)
, PD.TASK_ID) A7
, DECODE(MS.REQ_LINE_ID
, NULL
, SUBSTR(PO_UNITEFF_PKG.GET_UNIT_NUMBER(MS.SHIPMENT_LINE_ID
, MS.ITEM_ID)
, 0
, 30)
, PD.END_ITEM_UNIT_NUMBER)
, MS.TO_SUBINVENTORY
, MS.FROM_ORGANIZATION_ID
FROM MTL_UNITS_OF_MEASURE MUOM
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_LINE_LOCATIONS_ALL PL
, PO_DISTRIBUTIONS_ALL PD
, MTL_SUPPLY MS
WHERE MS.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND MS.PO_LINE_LOCATION_ID = PL.LINE_LOCATION_ID (+)
AND MS.PO_LINE_LOCATION_ID = PD.LINE_LOCATION_ID (+)
AND MS.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND MS.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND DECODE(MS.PO_LINE_LOCATION_ID
, NULL
, 1
, PL.QUANTITY) > 0
AND MUOM.UNIT_OF_MEASURE = MS.UNIT_OF_MEASURE
AND MS.PO_LINE_LOCATION_ID IS NOT NULL

Columns