VIEW: APPS.WSH_DSNO_ITEM_DETAILS_V

Source

View Text - Preformatted

SELECT /*+ INDEX(WEPC wms_epc_n1) */ WDA.DELIVERY_ID DELIVERY_ID, WDA.DELIVERY_ASSIGNMENT_ID DELIVERY_ASSIGNMENT_ID, WDD.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID, WDA.PARENT_DELIVERY_DETAIL_ID CONTAINER_INSTANCE_ID, WDD.MASTER_CONTAINER_ITEM_ID MASTER_CONTAINER_ITEM_ID, WDD.DETAIL_CONTAINER_ITEM_ID DETAIL_CONTAINER_ITEM_ID, WDD.LOAD_SEQ_NUMBER LOAD_SEQUENCE_NUMBER, WDD.CUSTOMER_REQUESTED_LOT_FLAG CUSTOMER_REQUESTED_LOT_FLAG, WDD.REVISION REVISION, WDD.LOT_NUMBER LOT_NUMBER, nvl(wsn.fm_serial_number,WDD.SERIAL_NUMBER) FROM_SERIAL_NUMBER, nvl(wsn.quantity,WDD.SHIPPED_QUANTITY) SHIPPED_QUANTITY, WDD.REQUESTED_QUANTITY_UOM SHIPPING_QUANTITY_UOM, WDD.TP_ATTRIBUTE_CATEGORY DD_TP_ATTRIBUTE_CATEGORY, WDD.TP_ATTRIBUTE1 DD_TP_ATTRIBUTE1, WDD.TP_ATTRIBUTE2 DD_TP_ATTRIBUTE2, WDD.TP_ATTRIBUTE3 DD_TP_ATTRIBUTE3, WDD.TP_ATTRIBUTE4 DD_TP_ATTRIBUTE4, WDD.TP_ATTRIBUTE5 DD_TP_ATTRIBUTE5, WDD.TP_ATTRIBUTE6 DD_TP_ATTRIBUTE6, WDD.TP_ATTRIBUTE7 DD_TP_ATTRIBUTE7, WDD.TP_ATTRIBUTE8 DD_TP_ATTRIBUTE8, WDD.TP_ATTRIBUTE9 DD_TP_ATTRIBUTE9, WDD.TP_ATTRIBUTE10 DD_TP_ATTRIBUTE10, WDD.TP_ATTRIBUTE11 DD_TP_ATTRIBUTE11, WDD.TP_ATTRIBUTE12 DD_TP_ATTRIBUTE12, WDD.TP_ATTRIBUTE13 DD_TP_ATTRIBUTE13, WDD.TP_ATTRIBUTE14 DD_TP_ATTRIBUTE14, WDD.TP_ATTRIBUTE15 DD_TP_ATTRIBUTE15, WDD.ATTRIBUTE_CATEGORY DD_ATTRIBUTE_CATEGORY, WDD.ATTRIBUTE1 DD_ATTRIBUTE1, WDD.ATTRIBUTE2 DD_ATTRIBUTE2, WDD.ATTRIBUTE3 DD_ATTRIBUTE3, WDD.ATTRIBUTE4 DD_ATTRIBUTE4, WDD.ATTRIBUTE5 DD_ATTRIBUTE5, WDD.ATTRIBUTE6 DD_ATTRIBUTE6, WDD.ATTRIBUTE7 DD_ATTRIBUTE7, WDD.ATTRIBUTE8 DD_ATTRIBUTE8, WDD.ATTRIBUTE9 DD_ATTRIBUTE9, WDD.ATTRIBUTE10 DD_ATTRIBUTE10, WDD.ATTRIBUTE11 DD_ATTRIBUTE11, WDD.ATTRIBUTE12 DD_ATTRIBUTE12, WDD.ATTRIBUTE13 DD_ATTRIBUTE13, WDD.ATTRIBUTE14 DD_ATTRIBUTE14, WDD.ATTRIBUTE15 DD_ATTRIBUTE15, nvl(wsn.to_serial_number,WDD.TO_SERIAL_NUMBER) TO_SERIAL_NUMBER, WEPC.EPC EPC FROM WSH_DELIVERY_ASSIGNMENTS_V WDA, WSH_DELIVERY_DETAILS WDD, WSH_SERIAL_NUMBERS WSN, WMS_EPC WEPC WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID AND WDD.CONTAINER_FLAG = 'N' AND NVL(WDD.SHIPPED_QUANTITY, 0) > 0 AND WDD.DELIVERY_DETAIL_ID = WSN.DELIVERY_DETAIL_ID (+) AND WEPC.SERIAL_NUMBER(+) BETWEEN WSN.FM_SERIAL_NUMBER AND NVL(WSN.TO_SERIAL_NUMBER, WSN.FM_SERIAL_NUMBER) AND WEPC.CROSS_REF_TYPE(+) = 2 AND NVL(WDD.INVENTORY_ITEM_ID,0) = NVL(NVL(WEPC.INVENTORY_ITEM_ID, WDD.INVENTORY_ITEM_ID ),0) AND LENGTH(WEPC.SERIAL_NUMBER(+)) = LENGTH(WSN.FM_SERIAL_NUMBER)

View Text - HTML Formatted

SELECT /*+ INDEX(WEPC WMS_EPC_N1) */ WDA.DELIVERY_ID DELIVERY_ID
, WDA.DELIVERY_ASSIGNMENT_ID DELIVERY_ASSIGNMENT_ID
, WDD.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID
, WDA.PARENT_DELIVERY_DETAIL_ID CONTAINER_INSTANCE_ID
, WDD.MASTER_CONTAINER_ITEM_ID MASTER_CONTAINER_ITEM_ID
, WDD.DETAIL_CONTAINER_ITEM_ID DETAIL_CONTAINER_ITEM_ID
, WDD.LOAD_SEQ_NUMBER LOAD_SEQUENCE_NUMBER
, WDD.CUSTOMER_REQUESTED_LOT_FLAG CUSTOMER_REQUESTED_LOT_FLAG
, WDD.REVISION REVISION
, WDD.LOT_NUMBER LOT_NUMBER
, NVL(WSN.FM_SERIAL_NUMBER
, WDD.SERIAL_NUMBER) FROM_SERIAL_NUMBER
, NVL(WSN.QUANTITY
, WDD.SHIPPED_QUANTITY) SHIPPED_QUANTITY
, WDD.REQUESTED_QUANTITY_UOM SHIPPING_QUANTITY_UOM
, WDD.TP_ATTRIBUTE_CATEGORY DD_TP_ATTRIBUTE_CATEGORY
, WDD.TP_ATTRIBUTE1 DD_TP_ATTRIBUTE1
, WDD.TP_ATTRIBUTE2 DD_TP_ATTRIBUTE2
, WDD.TP_ATTRIBUTE3 DD_TP_ATTRIBUTE3
, WDD.TP_ATTRIBUTE4 DD_TP_ATTRIBUTE4
, WDD.TP_ATTRIBUTE5 DD_TP_ATTRIBUTE5
, WDD.TP_ATTRIBUTE6 DD_TP_ATTRIBUTE6
, WDD.TP_ATTRIBUTE7 DD_TP_ATTRIBUTE7
, WDD.TP_ATTRIBUTE8 DD_TP_ATTRIBUTE8
, WDD.TP_ATTRIBUTE9 DD_TP_ATTRIBUTE9
, WDD.TP_ATTRIBUTE10 DD_TP_ATTRIBUTE10
, WDD.TP_ATTRIBUTE11 DD_TP_ATTRIBUTE11
, WDD.TP_ATTRIBUTE12 DD_TP_ATTRIBUTE12
, WDD.TP_ATTRIBUTE13 DD_TP_ATTRIBUTE13
, WDD.TP_ATTRIBUTE14 DD_TP_ATTRIBUTE14
, WDD.TP_ATTRIBUTE15 DD_TP_ATTRIBUTE15
, WDD.ATTRIBUTE_CATEGORY DD_ATTRIBUTE_CATEGORY
, WDD.ATTRIBUTE1 DD_ATTRIBUTE1
, WDD.ATTRIBUTE2 DD_ATTRIBUTE2
, WDD.ATTRIBUTE3 DD_ATTRIBUTE3
, WDD.ATTRIBUTE4 DD_ATTRIBUTE4
, WDD.ATTRIBUTE5 DD_ATTRIBUTE5
, WDD.ATTRIBUTE6 DD_ATTRIBUTE6
, WDD.ATTRIBUTE7 DD_ATTRIBUTE7
, WDD.ATTRIBUTE8 DD_ATTRIBUTE8
, WDD.ATTRIBUTE9 DD_ATTRIBUTE9
, WDD.ATTRIBUTE10 DD_ATTRIBUTE10
, WDD.ATTRIBUTE11 DD_ATTRIBUTE11
, WDD.ATTRIBUTE12 DD_ATTRIBUTE12
, WDD.ATTRIBUTE13 DD_ATTRIBUTE13
, WDD.ATTRIBUTE14 DD_ATTRIBUTE14
, WDD.ATTRIBUTE15 DD_ATTRIBUTE15
, NVL(WSN.TO_SERIAL_NUMBER
, WDD.TO_SERIAL_NUMBER) TO_SERIAL_NUMBER
, WEPC.EPC EPC
FROM WSH_DELIVERY_ASSIGNMENTS_V WDA
, WSH_DELIVERY_DETAILS WDD
, WSH_SERIAL_NUMBERS WSN
, WMS_EPC WEPC
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDD.CONTAINER_FLAG = 'N'
AND NVL(WDD.SHIPPED_QUANTITY
, 0) > 0
AND WDD.DELIVERY_DETAIL_ID = WSN.DELIVERY_DETAIL_ID (+)
AND WEPC.SERIAL_NUMBER(+) BETWEEN WSN.FM_SERIAL_NUMBER
AND NVL(WSN.TO_SERIAL_NUMBER
, WSN.FM_SERIAL_NUMBER)
AND WEPC.CROSS_REF_TYPE(+) = 2
AND NVL(WDD.INVENTORY_ITEM_ID
, 0) = NVL(NVL(WEPC.INVENTORY_ITEM_ID
, WDD.INVENTORY_ITEM_ID )
, 0)
AND LENGTH(WEPC.SERIAL_NUMBER(+)) = LENGTH(WSN.FM_SERIAL_NUMBER)