View: WSH_STND_SHIPITEMS_V

Product:	WSH - Shipping Execution
Description:
Implementation/DBA Data:	APPS.WSH_STND_SHIPITEMS_V

View Text

SELECT DECODE(WDD.CLIENT_ID
, NULL
, MSIK.CONCATENATED_SEGMENTS
, SUBSTR(MSIK.CONCATENATED_SEGMENTS
, 1
, INSTR(MSIK.CONCATENATED_SEGMENTS
, FFSV.CONCATENATED_SEGMENT_DELIMITER
, -1)-1)) ITEM_NUMBER
, WDD.REQUESTED_QUANTITY REQUESTED_QTY
, WDD.REQUESTED_QUANTITY_UOM REQUESTED_QTY_UOM
, 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
, 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.SUBINVENTORY SUBINVENTORY
, WDD.CUSTOMER_ID
, MCSV2.CATEGORY_SET_NAME COMMODITY_CODE_CAT
, WDD.SHIPMENT_PRIORITY_CODE SHIPMENT_PRIORITY_CODE
, WDD.LOT_NUMBER LOT_NUMBER
, WDD.PREFERRED_GRADE PREFERRED_GRADE
, NULL SERIAL_TYPE
, NULL WSN_ROWID
, NULL FROM_SERIAL_NUMBER
, NULL TO_SERIAL_NUMBER
, 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
, NVL(WDD.LINE_DIRECTION
, 'O') LINE_DIRECTION
, 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
, WDD.DATE_SCHEDULED
, WDD.REQUESTED_QUANTITY2 REQUESTED_QTY2
, WDD.REQUESTED_QUANTITY_UOM2 REQUESTED_QTY_UOM2
, WDD.SRC_REQUESTED_QUANTITY2 ORDERED_QTY2
, WDD.SRC_REQUESTED_QUANTITY_UOM2 ORDERED_QTY_UOM2
, WDD.SHIPPED_QUANTITY2 SHIPPED_QTY2
, OS.SET_NAME SHIP_SET_NAME
, SUBSTR(WDD.SOURCE_LINE_NUMBER
, 1
, INSTR(WDD.SOURCE_LINE_NUMBER
, '.')-1) LINE_NUMBER
, WDD.UNIT_PRICE UNIT_PRICE
, WDD.CURRENCY_CODE CURRENCY_CODE
, NVL(WDD.REFERENCE_NUMBER
, WDD.SOURCE_HEADER_NUMBER) REFERENCE_NUMBER
, NVL(WDD.REFERENCE_LINE_NUMBER
, SUBSTR(WDD.SOURCE_LINE_NUMBER
, 1
, INSTR(WDD.SOURCE_LINE_NUMBER
, '.')-1)) REFERENCE_LINE_NUMBER
, NVL(WDD.REFERENCE_LINE_QUANTITY
, WDD.SRC_REQUESTED_QUANTITY) REFERENCE_LINE_QUANTITY
, NVL(WDD.REFERENCE_LINE_QUANTITY_UOM
, WDD.SRC_REQUESTED_QUANTITY_UOM) REFERENCE_LINE_QUANTITY_UOM
, ROW_NUMBER( ) OVER (PARTITION BY WDD.SOURCE_LINE_ID ORDER BY WDD.DELIVERY_DETAIL_ID) DETAIL_SEQUENCE_NUMBER
, WDD.LOCATOR_ID LOCATOR_ID
, WDD.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, WDD.CUSTOMER_ITEM_ID CUSTOMER_ITEM_ID
FROM WSH_DELIVERY_DETAILS WDD
, WSH_DELIVERY_ASSIGNMENTS_V 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
, OE_SETS OS
, FND_ID_FLEX_STRUCTURES_VL FFSV
WHERE WDD.CONTAINER_FLAG = 'N'
AND WDD.SOURCE_CODE = 'OE'
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.SHIP_SET_ID = OS.SET_ID(+)
AND WND.DELIVERY_TYPE = 'STANDARD'
AND WDD.TRANSACTION_TEMP_ID IS NULL
AND FFSV.APPLICATION_ID = 401
AND FFSV.ID_FLEX_CODE = 'MSTK'
AND FFSV.ID_FLEX_NUM = 101 UNION SELECT DECODE(WDD.CLIENT_ID
, NULL
, MSIK.CONCATENATED_SEGMENTS
, SUBSTR(MSIK.CONCATENATED_SEGMENTS
, 1
, INSTR(MSIK.CONCATENATED_SEGMENTS
, FFSV.CONCATENATED_SEGMENT_DELIMITER
, -1)-1)) ITEM_NUMBER
, WDD.REQUESTED_QUANTITY REQUESTED_QTY
, WDD.REQUESTED_QUANTITY_UOM REQUESTED_QTY_UOM
, 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
, 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.SUBINVENTORY SUBINVENTORY
, WDD.CUSTOMER_ID
, MCSV2.CATEGORY_SET_NAME COMMODITY_CODE_CAT
, WDD.SHIPMENT_PRIORITY_CODE SHIPMENT_PRIORITY_CODE
, WDD.LOT_NUMBER LOT_NUMBER
, WDD.PREFERRED_GRADE PREFERRED_GRADE
, DECODE(MSNT.ROWID
, NULL
, 'WSN'
, 'MSNT') SERIAL_TYPE
, NVL(ROWIDTOCHAR(MSNT.ROWID)
, ROWIDTOCHAR(WSN.ROWID)) WSN_ROWID
, NVL(MSNT.FM_SERIAL_NUMBER
, WSN.FM_SERIAL_NUMBER) FROM_SERIAL_NUMBER
, NVL(MSNT.TO_SERIAL_NUMBER
, WSN.TO_SERIAL_NUMBER) TO_SERIAL_NUMBER
, 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
, NVL(WDD.LINE_DIRECTION
, 'O') LINE_DIRECTION
, 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
, WDD.DATE_SCHEDULED
, WDD.REQUESTED_QUANTITY2 REQUESTED_QTY2
, WDD.REQUESTED_QUANTITY_UOM2 REQUESTED_QTY_UOM2
, WDD.SRC_REQUESTED_QUANTITY2 ORDERED_QTY2
, WDD.SRC_REQUESTED_QUANTITY_UOM2 ORDERED_QTY_UOM2
, WDD.SHIPPED_QUANTITY2 SHIPPED_QTY2
, OS.SET_NAME SHIP_SET_NAME
, SUBSTR(WDD.SOURCE_LINE_NUMBER
, 1
, INSTR(WDD.SOURCE_LINE_NUMBER
, '.')-1) LINE_NUMBER
, WDD.UNIT_PRICE UNIT_PRICE
, WDD.CURRENCY_CODE CURRENCY_CODE
, NVL(WDD.REFERENCE_NUMBER
, WDD.SOURCE_HEADER_NUMBER) REFERENCE_NUMBER
, NVL(WDD.REFERENCE_LINE_NUMBER
, SUBSTR(WDD.SOURCE_LINE_NUMBER
, 1
, INSTR(WDD.SOURCE_LINE_NUMBER
, '.')-1)) REFERENCE_LINE_NUMBER
, NVL(WDD.REFERENCE_LINE_QUANTITY
, WDD.SRC_REQUESTED_QUANTITY) REFERENCE_LINE_QUANTITY
, NVL(WDD.REFERENCE_LINE_QUANTITY_UOM
, WDD.SRC_REQUESTED_QUANTITY_UOM) REFERENCE_LINE_QUANTITY_UOM
, ROW_NUMBER( ) OVER (PARTITION BY WDD.SOURCE_LINE_ID ORDER BY WDD.DELIVERY_DETAIL_ID) DETAIL_SEQUENCE_NUMBER
, WDD.LOCATOR_ID LOCATOR_ID
, WDD.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, WDD.CUSTOMER_ITEM_ID CUSTOMER_ITEM_ID
FROM WSH_DELIVERY_DETAILS WDD
, WSH_DELIVERY_ASSIGNMENTS_V 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
, OE_SETS OS
, MTL_SERIAL_NUMBERS_TEMP MSNT
, WSH_SERIAL_NUMBERS WSN
, FND_ID_FLEX_STRUCTURES_VL FFSV
WHERE WDD.CONTAINER_FLAG = 'N'
AND WDD.SOURCE_CODE = 'OE'
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.SHIP_SET_ID = OS.SET_ID(+)
AND WDD.TRANSACTION_TEMP_ID = MSNT.TRANSACTION_TEMP_ID (+)
AND WDD.DELIVERY_DETAIL_ID = WSN.DELIVERY_DETAIL_ID (+)
AND WDD.TRANSACTION_TEMP_ID IS NOT NULL
AND WND.DELIVERY_TYPE = 'STANDARD'
AND FFSV.APPLICATION_ID = 401
AND FFSV.ID_FLEX_CODE = 'MSTK'
AND FFSV.ID_FLEX_NUM = 101

Columns