eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.WSH_SS_SHIPITEMS_V

Source

View Text - Preformatted


SELECT MSIK.CONCATENATED_SEGMENTS ITEM_NUMBER , WDD.REQUESTED_QUANTITY REQUESTED_QTY , WDD.REQUESTED_QUANTITY_UOM REQUESTED_QTY_UOM , WDD.COUNTRY_OF_ORIGIN COUNTRY_OF_ORIGIN , MCSV1.CATEGORY_SET_NAME FREIGHT_CLASS_CAT , PHCV.HAZARD_CLASS HAZARD_CLASS , WDD.ITEM_DESCRIPTION ITEM_DESCRIPTION , WDD.REVISION REVISION , MCI.CUSTOMER_ITEM_NUMBER CUSTOMER_ITEM_NUMBER , WDD.PACKING_INSTRUCTIONS PACKING_INSTRUCTIONS , WDD.FOB_CODE OWNERSHIP_CODE , DECODE (WDD.SHIP_SET_ID, NULL, 0, 1) PARTIAL_SHIPMENT , WDD.CYCLE_COUNT_QUANTITY BACKORDERED_QTY , WDD.SRC_REQUESTED_QUANTITY ORDERED_QTY , WDD.SRC_REQUESTED_QUANTITY_UOM ORDERED_QTY_UOM , WDD.SHIPPED_QUANTITY SHIPPED_QTY , WDD.VOLUME VOLUME , WDD.VOLUME_UOM_CODE VOLUME_UOM_CODE , WDD.NET_WEIGHT NET_WEIGHT , WDD.WEIGHT_UOM_CODE WEIGHT_UOM_CODE , WDD.SHIPPING_INSTRUCTIONS SHIP_NOTES , WDD.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID , WDD.SOURCE_LINE_ID SOURCE_LINE_ID , WDA.PARENT_DELIVERY_DETAIL_ID PARENT_DELIVERY_DETAIL_ID, WDA.DELIVERY_ID DELIVERY_ID , WDD.SHIP_TOLERANCE_BELOW TOLERANCE_BELOW , WDD.SHIP_TOLERANCE_ABOVE TOLERANCE_ABOVE , WDD.LOAD_SEQ_NUMBER LOAD_SEQUENCE_NUMBER , WDD.SHIP_SET_ID SHIP_SET_ID , WDD.SUBINVENTORY SUBINVENTORY , WDD.TOP_MODEL_LINE_ID TOP_MODEL_LINE_ID , WDD.SHIP_MODEL_COMPLETE_FLAG SHIP_MODEL_COMPLETE_FLAG , WDD.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID , WDD.ORG_ID ORG_ID , WDD.CUSTOMER_ID CUSTOMER_ID , MCSV2.CATEGORY_SET_NAME COMMODITY_CODE_CAT , DECODE (WDD.SHIPMENT_PRIORITY_CODE, 'STANDARD', '01', 'NORMAL', '02', 'HIGH', '03') SHIPMENT_PRIORITY_CODE , WDD.LOT_NUMBER LOT_NUMBER , NVL(WSN.FM_SERIAL_NUMBER,WDD.SERIAL_NUMBER) FROM_SERIAL_NUMBER , NVL(WSN.TO_SERIAL_NUMBER,WDD.TO_SERIAL_NUMBER) TO_SERIAL_NUMBER , WDD.PREFERRED_GRADE PREFERRED_GRADE , WDD.ATTRIBUTE_CATEGORY SHIPITEM_ATTRIBUTE_CATEGORY, WDD.ATTRIBUTE1 SHIPITEM_ATTRIBUTE1 , WDD.ATTRIBUTE2 SHIPITEM_ATTRIBUTE2 , WDD.ATTRIBUTE3 SHIPITEM_ATTRIBUTE3 , WDD.ATTRIBUTE4 SHIPITEM_ATTRIBUTE4 , WDD.ATTRIBUTE5 SHIPITEM_ATTRIBUTE5 , WDD.ATTRIBUTE6 SHIPITEM_ATTRIBUTE6 , WDD.ATTRIBUTE7 SHIPITEM_ATTRIBUTE7 , WDD.ATTRIBUTE8 SHIPITEM_ATTRIBUTE8 , WDD.ATTRIBUTE9 SHIPITEM_ATTRIBUTE9 , WDD.ATTRIBUTE10 SHIPITEM_ATTRIBUTE10 , WDD.ATTRIBUTE11 SHIPITEM_ATTRIBUTE11 , WDD.ATTRIBUTE12 SHIPITEM_ATTRIBUTE12 , WDD.ATTRIBUTE13 SHIPITEM_ATTRIBUTE13 , WDD.ATTRIBUTE14 SHIPITEM_ATTRIBUTE14 , WDD.ATTRIBUTE15 SHIPITEM_ATTRIBUTE15 , WDD.CUST_PO_NUMBER CUST_PO_NUMBER , WDD.GROSS_WEIGHT GROSS_WEIGHT , NVL(WDD.WV_FROZEN_FLAG,'Y') MANUAL_WV , ROWIDTOCHAR(WSN.ROWID) WSN_ROWID , NVL(WDD.LINE_DIRECTION,'O') LINE_DIRECTION , WDD.REQUEST_DATE_TYPE_CODE REQUEST_DATE_TYPE_CODE , WDD.EARLIEST_PICKUP_DATE EARLIEST_PICKUP_DATE , WDD.LATEST_PICKUP_DATE LATEST_PICKUP_DATE , WDD.EARLIEST_DROPOFF_DATE EARLIEST_DROPOFF_DATE , WDD.LATEST_DROPOFF_DATE LATEST_DROPOFF_DATE , WDD.DATE_REQUESTED DATE_REQUESTED , WDD.DATE_SCHEDULED DATE_SCHEDULED , WDD.DELIVER_TO_SITE_USE_ID DELIVER_TO_SITE_USE_ID , WDD.REQUESTED_QUANTITY2 REQUESTED_QTY2 , WDD.REQUESTED_QUANTITY_UOM2 REQUESTED_QTY_UOM2 , NVL (WDD.REQUESTED_QUANTITY2,0) - NVL (WDD.SHIPPED_QUANTITY2,0) BACKORDERED_QTY2 , WDD.SRC_REQUESTED_QUANTITY2 ORDERED_QTY2 , WDD.SRC_REQUESTED_QUANTITY_UOM2 ORDERED_QTY_UOM2 , WDD.SHIPPED_QUANTITY2 SHIPPED_QTY2 , 'DLVY' ENTITY_TYPE , NULL LINE_NUMBER , NULL REFERENCE_LINE_ID , NULL SOURCE_HEADER_NUMBER FROM WSH_DELIVERY_DETAILS WDD , WSH_DELIVERY_ASSIGNMENTS WDA, WSH_NEW_DELIVERIES WND , MTL_CUSTOMER_ITEMS MCI , MTL_SYSTEM_ITEMS_KFV MSIK , PO_HAZARD_CLASSES_VL PHCV , MTL_CATEGORY_SETS_VL MCSV1 , MTL_CATEGORY_SETS_VL MCSV2 , WSH_SERIAL_NUMBERS WSN WHERE WDD.CONTAINER_FLAG = 'N' AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID AND WDA.DELIVERY_ID = WND.DELIVERY_ID AND WDD.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID(+) AND WDD.ORGANIZATION_ID = MSIK.ORGANIZATION_ID AND WDD.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID (+) AND WDD.HAZARD_CLASS_ID = PHCV.HAZARD_CLASS_ID (+) AND WDD.FREIGHT_CLASS_CAT_ID = MCSV1.CATEGORY_SET_ID (+) AND WDD.COMMODITY_CODE_CAT_ID = MCSV2.CATEGORY_SET_ID (+) AND WDD.DELIVERY_DETAIL_ID = WSN.DELIVERY_DETAIL_ID (+) AND WND.DELIVERY_TYPE = 'STANDARD' UNION SELECT NULL ITEM_NUMBER , WDD_REF.REQUESTED_QUANTITY REQUESTED_QTY , WDD_REF.REQUESTED_QUANTITY_UOM REQUESTED_QTY_UOM , NULL COUNTRY_OF_ORIGIN , NULL FREIGHT_CLASS_CAT , NULL HAZARD_CLASS , NULL ITEM_DESCRIPTION , NULL REVISION , NULL CUSTOMER_ITEM_NUMBER , NULL PACKING_INSTRUCTIONS , NULL OWNERSHIP_CODE , NULL PARTIAL_SHIPMENT , NULL BACKORDERED_QTY , NULL ORDERED_QTY , NULL ORDERED_QTY_UOM , NULL SHIPPED_QTY , NULL VOLUME , NULL VOLUME_UOM_CODE , NULL NET_WEIGHT , NULL WEIGHT_UOM_CODE , NULL SHIP_NOTES , NULL DELIVERY_DETAIL_ID , NULL SOURCE_LINE_ID , NULL PARENT_DELIVERY_DETAIL_ID, WDD_REF.SHIPMENT_BATCH_ID DELIVERY_ID , NULL TOLERANCE_BELOW , NULL TOLERANCE_ABOVE , NULL LOAD_SEQUENCE_NUMBER , NULL SHIP_SET_ID , NULL SUBINVENTORY , NULL TOP_MODEL_LINE_ID , NULL SHIP_MODEL_COMPLETE_FLAG , NULL DELIVER_TO_LOCATION_ID , NULL ORG_ID , NULL CUSTOMER_ID , NULL COMMODITY_CODE_CAT , NULL SHIPMENT_PRIORITY_CODE , NULL LOT_NUMBER , NULL FROM_SERIAL_NUMBER , NULL TO_SERIAL_NUMBER , NULL PREFERRED_GRADE , NULL SHIPITEM_ATTRIBUTE_CATEGORY, NULL SHIPITEM_ATTRIBUTE1 , NULL SHIPITEM_ATTRIBUTE2 , NULL SHIPITEM_ATTRIBUTE3 , NULL SHIPITEM_ATTRIBUTE4 , NULL SHIPITEM_ATTRIBUTE5 , NULL SHIPITEM_ATTRIBUTE6 , NULL SHIPITEM_ATTRIBUTE7 , NULL SHIPITEM_ATTRIBUTE8 , NULL SHIPITEM_ATTRIBUTE9 , NULL SHIPITEM_ATTRIBUTE10 , NULL SHIPITEM_ATTRIBUTE11 , NULL SHIPITEM_ATTRIBUTE12 , NULL SHIPITEM_ATTRIBUTE13 , NULL SHIPITEM_ATTRIBUTE14 , NULL SHIPITEM_ATTRIBUTE15 , NULL CUST_PO_NUMBER , NULL GROSS_WEIGHT , NULL MANUAL_WV , NULL WSN_ROWID , NULL LINE_DIRECTION , NULL REQUEST_DATE_TYPE_CODE , NULL EARLIEST_PICKUP_DATE , NULL LATEST_PICKUP_DATE , NULL EARLIEST_DROPOFF_DATE , NULL LATEST_DROPOFF_DATE , NULL DATE_REQUESTED , NULL DATE_SCHEDULED , NULL DELIVER_TO_SITE_USE_ID , NULL REQUESTED_QTY2 , NULL REQUESTED_QTY_UOM2 , NULL BACKORDERED_QTY2 , NULL ORDERED_QTY2 , NULL ORDERED_QTY_UOM2 , NULL SHIPPED_QTY2 , 'BATCH' ENTITY_TYPE , WDD_REF.SHIPMENT_LINE_NUMBER LINE_NUMBER , WDD_REF.REFERENCE_LINE_ID REFERENCE_LINE_ID , WDD_REF.SALES_ORDER_NUMBER SOURCE_HEADER_NUMBER FROM ( SELECT wdd.shipment_batch_id shipment_batch_id , wdd.shipment_line_number shipment_line_number , wdd.source_header_number sales_order_number , wdd.reference_line_id reference_line_id , wdd.requested_quantity_uom requested_quantity_uom, SUM(wdd.requested_quantity) requested_quantity FROM wsh_delivery_details wdd, mtl_system_items_kfv msik WHERE wdd.source_code = 'OE' AND wdd.inventory_item_id = msik.inventory_item_id AND wdd.organization_id = msik.organization_id AND wdd.shipment_batch_id IS NOT NULL GROUP BY wdd.shipment_batch_id , wdd.shipment_line_number, wdd.source_header_number, wdd.reference_line_id , wdd.requested_quantity_uom ) wdd_ref

View Text - HTML Formatted

SELECT MSIK.CONCATENATED_SEGMENTS ITEM_NUMBER
, WDD.REQUESTED_QUANTITY REQUESTED_QTY
, WDD.REQUESTED_QUANTITY_UOM REQUESTED_QTY_UOM
, WDD.COUNTRY_OF_ORIGIN COUNTRY_OF_ORIGIN
, MCSV1.CATEGORY_SET_NAME FREIGHT_CLASS_CAT
, PHCV.HAZARD_CLASS HAZARD_CLASS
, WDD.ITEM_DESCRIPTION ITEM_DESCRIPTION
, WDD.REVISION REVISION
, MCI.CUSTOMER_ITEM_NUMBER CUSTOMER_ITEM_NUMBER
, WDD.PACKING_INSTRUCTIONS PACKING_INSTRUCTIONS
, WDD.FOB_CODE OWNERSHIP_CODE
, DECODE (WDD.SHIP_SET_ID
, NULL
, 0
, 1) PARTIAL_SHIPMENT
, WDD.CYCLE_COUNT_QUANTITY BACKORDERED_QTY
, WDD.SRC_REQUESTED_QUANTITY ORDERED_QTY
, WDD.SRC_REQUESTED_QUANTITY_UOM ORDERED_QTY_UOM
, WDD.SHIPPED_QUANTITY SHIPPED_QTY
, WDD.VOLUME VOLUME
, WDD.VOLUME_UOM_CODE VOLUME_UOM_CODE
, WDD.NET_WEIGHT NET_WEIGHT
, WDD.WEIGHT_UOM_CODE WEIGHT_UOM_CODE
, WDD.SHIPPING_INSTRUCTIONS SHIP_NOTES
, WDD.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID
, WDD.SOURCE_LINE_ID SOURCE_LINE_ID
, WDA.PARENT_DELIVERY_DETAIL_ID PARENT_DELIVERY_DETAIL_ID
, WDA.DELIVERY_ID DELIVERY_ID
, WDD.SHIP_TOLERANCE_BELOW TOLERANCE_BELOW
, WDD.SHIP_TOLERANCE_ABOVE TOLERANCE_ABOVE
, WDD.LOAD_SEQ_NUMBER LOAD_SEQUENCE_NUMBER
, WDD.SHIP_SET_ID SHIP_SET_ID
, WDD.SUBINVENTORY SUBINVENTORY
, WDD.TOP_MODEL_LINE_ID TOP_MODEL_LINE_ID
, WDD.SHIP_MODEL_COMPLETE_FLAG SHIP_MODEL_COMPLETE_FLAG
, WDD.DELIVER_TO_LOCATION_ID DELIVER_TO_LOCATION_ID
, WDD.ORG_ID ORG_ID
, WDD.CUSTOMER_ID CUSTOMER_ID
, MCSV2.CATEGORY_SET_NAME COMMODITY_CODE_CAT
, DECODE (WDD.SHIPMENT_PRIORITY_CODE
, 'STANDARD'
, '01'
, 'NORMAL'
, '02'
, 'HIGH'
, '03') SHIPMENT_PRIORITY_CODE
, WDD.LOT_NUMBER LOT_NUMBER
, NVL(WSN.FM_SERIAL_NUMBER
, WDD.SERIAL_NUMBER) FROM_SERIAL_NUMBER
, NVL(WSN.TO_SERIAL_NUMBER
, WDD.TO_SERIAL_NUMBER) TO_SERIAL_NUMBER
, WDD.PREFERRED_GRADE PREFERRED_GRADE
, WDD.ATTRIBUTE_CATEGORY SHIPITEM_ATTRIBUTE_CATEGORY
, WDD.ATTRIBUTE1 SHIPITEM_ATTRIBUTE1
, WDD.ATTRIBUTE2 SHIPITEM_ATTRIBUTE2
, WDD.ATTRIBUTE3 SHIPITEM_ATTRIBUTE3
, WDD.ATTRIBUTE4 SHIPITEM_ATTRIBUTE4
, WDD.ATTRIBUTE5 SHIPITEM_ATTRIBUTE5
, WDD.ATTRIBUTE6 SHIPITEM_ATTRIBUTE6
, WDD.ATTRIBUTE7 SHIPITEM_ATTRIBUTE7
, WDD.ATTRIBUTE8 SHIPITEM_ATTRIBUTE8
, WDD.ATTRIBUTE9 SHIPITEM_ATTRIBUTE9
, WDD.ATTRIBUTE10 SHIPITEM_ATTRIBUTE10
, WDD.ATTRIBUTE11 SHIPITEM_ATTRIBUTE11
, WDD.ATTRIBUTE12 SHIPITEM_ATTRIBUTE12
, WDD.ATTRIBUTE13 SHIPITEM_ATTRIBUTE13
, WDD.ATTRIBUTE14 SHIPITEM_ATTRIBUTE14
, WDD.ATTRIBUTE15 SHIPITEM_ATTRIBUTE15
, WDD.CUST_PO_NUMBER CUST_PO_NUMBER
, WDD.GROSS_WEIGHT GROSS_WEIGHT
, NVL(WDD.WV_FROZEN_FLAG
, 'Y') MANUAL_WV
, ROWIDTOCHAR(WSN.ROWID) WSN_ROWID
, NVL(WDD.LINE_DIRECTION
, 'O') LINE_DIRECTION
, WDD.REQUEST_DATE_TYPE_CODE REQUEST_DATE_TYPE_CODE
, WDD.EARLIEST_PICKUP_DATE EARLIEST_PICKUP_DATE
, WDD.LATEST_PICKUP_DATE LATEST_PICKUP_DATE
, WDD.EARLIEST_DROPOFF_DATE EARLIEST_DROPOFF_DATE
, WDD.LATEST_DROPOFF_DATE LATEST_DROPOFF_DATE
, WDD.DATE_REQUESTED DATE_REQUESTED
, WDD.DATE_SCHEDULED DATE_SCHEDULED
, WDD.DELIVER_TO_SITE_USE_ID DELIVER_TO_SITE_USE_ID
, WDD.REQUESTED_QUANTITY2 REQUESTED_QTY2
, WDD.REQUESTED_QUANTITY_UOM2 REQUESTED_QTY_UOM2
, NVL (WDD.REQUESTED_QUANTITY2
, 0) - NVL (WDD.SHIPPED_QUANTITY2
, 0) BACKORDERED_QTY2
, WDD.SRC_REQUESTED_QUANTITY2 ORDERED_QTY2
, WDD.SRC_REQUESTED_QUANTITY_UOM2 ORDERED_QTY_UOM2
, WDD.SHIPPED_QUANTITY2 SHIPPED_QTY2
, 'DLVY' ENTITY_TYPE
, NULL LINE_NUMBER
, NULL REFERENCE_LINE_ID
, NULL SOURCE_HEADER_NUMBER
FROM WSH_DELIVERY_DETAILS WDD
, WSH_DELIVERY_ASSIGNMENTS WDA
, WSH_NEW_DELIVERIES WND
, MTL_CUSTOMER_ITEMS MCI
, MTL_SYSTEM_ITEMS_KFV MSIK
, PO_HAZARD_CLASSES_VL PHCV
, MTL_CATEGORY_SETS_VL MCSV1
, MTL_CATEGORY_SETS_VL MCSV2
, WSH_SERIAL_NUMBERS WSN
WHERE WDD.CONTAINER_FLAG = 'N'
AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID
AND WDD.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID(+)
AND WDD.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND WDD.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID (+)
AND WDD.HAZARD_CLASS_ID = PHCV.HAZARD_CLASS_ID (+)
AND WDD.FREIGHT_CLASS_CAT_ID = MCSV1.CATEGORY_SET_ID (+)
AND WDD.COMMODITY_CODE_CAT_ID = MCSV2.CATEGORY_SET_ID (+)
AND WDD.DELIVERY_DETAIL_ID = WSN.DELIVERY_DETAIL_ID (+)
AND WND.DELIVERY_TYPE = 'STANDARD' UNION SELECT NULL ITEM_NUMBER
, WDD_REF.REQUESTED_QUANTITY REQUESTED_QTY
, WDD_REF.REQUESTED_QUANTITY_UOM REQUESTED_QTY_UOM
, NULL COUNTRY_OF_ORIGIN
, NULL FREIGHT_CLASS_CAT
, NULL HAZARD_CLASS
, NULL ITEM_DESCRIPTION
, NULL REVISION
, NULL CUSTOMER_ITEM_NUMBER
, NULL PACKING_INSTRUCTIONS
, NULL OWNERSHIP_CODE
, NULL PARTIAL_SHIPMENT
, NULL BACKORDERED_QTY
, NULL ORDERED_QTY
, NULL ORDERED_QTY_UOM
, NULL SHIPPED_QTY
, NULL VOLUME
, NULL VOLUME_UOM_CODE
, NULL NET_WEIGHT
, NULL WEIGHT_UOM_CODE
, NULL SHIP_NOTES
, NULL DELIVERY_DETAIL_ID
, NULL SOURCE_LINE_ID
, NULL PARENT_DELIVERY_DETAIL_ID
, WDD_REF.SHIPMENT_BATCH_ID DELIVERY_ID
, NULL TOLERANCE_BELOW
, NULL TOLERANCE_ABOVE
, NULL LOAD_SEQUENCE_NUMBER
, NULL SHIP_SET_ID
, NULL SUBINVENTORY
, NULL TOP_MODEL_LINE_ID
, NULL SHIP_MODEL_COMPLETE_FLAG
, NULL DELIVER_TO_LOCATION_ID
, NULL ORG_ID
, NULL CUSTOMER_ID
, NULL COMMODITY_CODE_CAT
, NULL SHIPMENT_PRIORITY_CODE
, NULL LOT_NUMBER
, NULL FROM_SERIAL_NUMBER
, NULL TO_SERIAL_NUMBER
, NULL PREFERRED_GRADE
, NULL SHIPITEM_ATTRIBUTE_CATEGORY
, NULL SHIPITEM_ATTRIBUTE1
, NULL SHIPITEM_ATTRIBUTE2
, NULL SHIPITEM_ATTRIBUTE3
, NULL SHIPITEM_ATTRIBUTE4
, NULL SHIPITEM_ATTRIBUTE5
, NULL SHIPITEM_ATTRIBUTE6
, NULL SHIPITEM_ATTRIBUTE7
, NULL SHIPITEM_ATTRIBUTE8
, NULL SHIPITEM_ATTRIBUTE9
, NULL SHIPITEM_ATTRIBUTE10
, NULL SHIPITEM_ATTRIBUTE11
, NULL SHIPITEM_ATTRIBUTE12
, NULL SHIPITEM_ATTRIBUTE13
, NULL SHIPITEM_ATTRIBUTE14
, NULL SHIPITEM_ATTRIBUTE15
, NULL CUST_PO_NUMBER
, NULL GROSS_WEIGHT
, NULL MANUAL_WV
, NULL WSN_ROWID
, NULL LINE_DIRECTION
, NULL REQUEST_DATE_TYPE_CODE
, NULL EARLIEST_PICKUP_DATE
, NULL LATEST_PICKUP_DATE
, NULL EARLIEST_DROPOFF_DATE
, NULL LATEST_DROPOFF_DATE
, NULL DATE_REQUESTED
, NULL DATE_SCHEDULED
, NULL DELIVER_TO_SITE_USE_ID
, NULL REQUESTED_QTY2
, NULL REQUESTED_QTY_UOM2
, NULL BACKORDERED_QTY2
, NULL ORDERED_QTY2
, NULL ORDERED_QTY_UOM2
, NULL SHIPPED_QTY2
, 'BATCH' ENTITY_TYPE
, WDD_REF.SHIPMENT_LINE_NUMBER LINE_NUMBER
, WDD_REF.REFERENCE_LINE_ID REFERENCE_LINE_ID
, WDD_REF.SALES_ORDER_NUMBER SOURCE_HEADER_NUMBER
FROM ( SELECT WDD.SHIPMENT_BATCH_ID SHIPMENT_BATCH_ID
, WDD.SHIPMENT_LINE_NUMBER SHIPMENT_LINE_NUMBER
, WDD.SOURCE_HEADER_NUMBER SALES_ORDER_NUMBER
, WDD.REFERENCE_LINE_ID REFERENCE_LINE_ID
, WDD.REQUESTED_QUANTITY_UOM REQUESTED_QUANTITY_UOM
, SUM(WDD.REQUESTED_QUANTITY) REQUESTED_QUANTITY
FROM WSH_DELIVERY_DETAILS WDD
, MTL_SYSTEM_ITEMS_KFV MSIK
WHERE WDD.SOURCE_CODE = 'OE'
AND WDD.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND WDD.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND WDD.SHIPMENT_BATCH_ID IS NOT NULL GROUP BY WDD.SHIPMENT_BATCH_ID
, WDD.SHIPMENT_LINE_NUMBER
, WDD.SOURCE_HEADER_NUMBER
, WDD.REFERENCE_LINE_ID
, WDD.REQUESTED_QUANTITY_UOM ) WDD_REF