VIEW: APPS.PO_SHIP_SUPPLY_VIEW

Source

View Text - Preformatted

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

View Text - HTML Formatted

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