VIEW: APPS.WSH_STND_SHIPITEMS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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