VIEW: APPS.WSH_DSNO_ITEMS_COMM_V

Source

View Text - Preformatted

SELECT WDD.SOURCE_CODE SOURCE_CODE, WND.DELIVERY_ID DELIVERY_ID, WDA.PARENT_DELIVERY_DETAIL_ID CONTAINER_INSTANCE_ID, WDD.INVENTORY_ITEM_ID ITEM_ID, MSI.DESCRIPTION ITEM_DESCRIPTION, WDD.SOURCE_HEADER_ID ORDER_HEADER_ID, WDD.SOURCE_LINE_ID ORDER_LINE_ID, WDD.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID, WDD.CUSTOMER_ITEM_ID CUSTOMER_ITEM_ID, WDD.ATO_LINE_ID ATO_LINE_ID, MCI.CUSTOMER_ITEM_NUMBER CUSTOMER_PART_NUMBER, MIF.ITEM_NUMBER SUPPLIER_PART_NUMBER, WDD.DATE_REQUESTED REQUESTED_DATE, WDD.SHIPPED_QUANTITY SHIPPED_QUANTITY, WDD.REQUESTED_QUANTITY_UOM ITEM_UOM_CODE_INT, MCC.COMMODITY_CODE COMMODITY_CODE_INT, MSI.CONTAINER_ITEM_FLAG CONTAINER_ITEM_FLAG, MSI.CONTAINER_TYPE_CODE CONTAINER_TYPE_CODE_INT , MSI.CUSTOMER_ORDER_ENABLED_FLAG CUSTOMER_ORDER_FLAG, WDD.SRC_REQUESTED_QUANTITY ORDERED_QUANTITY, NVL(WDD.CANCELLED_QUANTITY,0) CANCELLED_QUANTITY, DECODE(WDA.PARENT_DELIVERY_DETAIL_ID , null ,0 , (SELECT EDPQ.PACKED_QUANTITY FROM WSH_DSNO_PACKED_QUANTITY_V EDPQ WHERE WDA.DELIVERY_ID = EDPQ.DELIVERY_ID AND NVL(WDA.PARENT_DELIVERY_DETAIL_ID,0) = NVL(EDPQ.CONTAINER_ID ,0) AND WDD.INVENTORY_ITEM_ID = EDPQ.INVENTORY_ITEM_ID AND WDD.SOURCE_LINE_ID = EDPQ.SOURCE_LINE_ID)) PACKED_QUANTITY, WDD.UNIT_PRICE UNIT_LIST_PRICE, WDD.SHIP_MODEL_COMPLETE_FLAG SHIP_MODEL_COMPLETE_FLAG, WDD.CUSTOMER_DOCK_CODE CUSTOMER_DOCK_CODE, WDD.SHIPMENT_PRIORITY_CODE SHIPMENT_PRIORITY_CODE_INT, WND.CONFIRM_DATE SHIPMENT_CONFIRMED_DATE, MCI.CUSTOMER_ITEM_DESC CUSTOMER_ITEM_DESCRIPTION, 'UN' HAZARDOUS_MATERIAL_CODE_INT, PHC.HAZARD_CLASS HAZARD_CLASS_INT, PHC.DESCRIPTION HAZARD_CLASS_DESCRIPTION, WDD.ATTRIBUTE_CATEGORY DEL_DETAIL_ATTRIB_CATEGORY, WDD.ATTRIBUTE1 DELIVERY_DETAIL_ATTRIBUTE1, WDD.ATTRIBUTE2 DELIVERY_DETAIL_ATTRIBUTE2, WDD.ATTRIBUTE3 DELIVERY_DETAIL_ATTRIBUTE3, WDD.ATTRIBUTE4 DELIVERY_DETAIL_ATTRIBUTE4, WDD.ATTRIBUTE5 DELIVERY_DETAIL_ATTRIBUTE5, WDD.ATTRIBUTE6 DELIVERY_DETAIL_ATTRIBUTE6, WDD.ATTRIBUTE7 DELIVERY_DETAIL_ATTRIBUTE7, WDD.ATTRIBUTE8 DELIVERY_DETAIL_ATTRIBUTE8, WDD.ATTRIBUTE9 DELIVERY_DETAIL_ATTRIBUTE9, WDD.ATTRIBUTE10 DELIVERY_DETAIL_ATTRIBUTE10, WDD.ATTRIBUTE11 DELIVERY_DETAIL_ATTRIBUTE11, WDD.ATTRIBUTE12 DELIVERY_DETAIL_ATTRIBUTE12, WDD.ATTRIBUTE13 DELIVERY_DETAIL_ATTRIBUTE13, WDD.ATTRIBUTE14 DELIVERY_DETAIL_ATTRIBUTE14, WDD.ATTRIBUTE15 DELIVERY_DETAIL_ATTRIBUTE15, MIF.ATTRIBUTE_CATEGORY LINE_PART_CATEGORY, MIF.ATTRIBUTE1 LINE_PART_ATTRIBUTE1, MIF.ATTRIBUTE2 LINE_PART_ATTRIBUTE2, MIF.ATTRIBUTE3 LINE_PART_ATTRIBUTE3, MIF.ATTRIBUTE4 LINE_PART_ATTRIBUTE4, MIF.ATTRIBUTE5 LINE_PART_ATTRIBUTE5, MIF.ATTRIBUTE6 LINE_PART_ATTRIBUTE6, MIF.ATTRIBUTE7 LINE_PART_ATTRIBUTE7, MIF.ATTRIBUTE8 LINE_PART_ATTRIBUTE8, MIF.ATTRIBUTE9 LINE_PART_ATTRIBUTE9, MIF.ATTRIBUTE10 LINE_PART_ATTRIBUTE10, MIF.ATTRIBUTE11 LINE_PART_ATTRIBUTE11, MIF.ATTRIBUTE12 LINE_PART_ATTRIBUTE12, MIF.ATTRIBUTE13 LINE_PART_ATTRIBUTE13, MIF.ATTRIBUTE14 LINE_PART_ATTRIBUTE14, MIF.ATTRIBUTE15 LINE_PART_ATTRIBUTE15, WDD.SRC_REQUESTED_QUANTITY_UOM ORDER_QUANTITY_UOM_INT, WDD.CUSTOMER_PROD_SEQ CUST_PRODUCTION_SEQ_NUM, WDD.CUST_PO_NUMBER CUST_PO_NUMBER, WDD.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE_INT, WDD.FOB_CODE FOB_POINT_CODE_INT, WDD.ORGANIZATION_ID ORGANIZATION_ID, WDD.CUSTOMER_JOB CUSTOMER_JOB, WDD.CUSTOMER_PRODUCTION_LINE CUSTOMER_PRODUCTION_LINE, WDD.CUST_MODEL_SERIAL_NUMBER CUSTOMER_MODEL_SERIAL_NUMBER FROM PO_HAZARD_CLASSES PHC, MTL_SYSTEM_ITEMS MSI, MTL_CUSTOMER_ITEMS MCI, MTL_COMMODITY_CODES MCC, MTL_ITEM_FLEXFIELDS MIF, WSH_NEW_DELIVERIES WND, WSH_DELIVERY_ASSIGNMENTS_V WDA, WSH_DELIVERY_DETAILS WDD WHERE NVL(WDD.CONTAINER_FLAG,'N')='N' AND MSI.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+) AND WDD.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID(+) and mci.commodity_code_id = mcc.commodity_code_id (+) AND WDD.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND WDD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND WDD.ORGANIZATION_ID = MIF.ORGANIZATION_ID AND WDD.INVENTORY_ITEM_ID = MIF.INVENTORY_ITEM_ID AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND NVL(WDD.SHIPPED_QUANTITY,0) > 0 AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WDA.DELIVERY_ID IS NOT NULL AND WND.DELIVERY_TYPE = 'STANDARD'

View Text - HTML Formatted

SELECT WDD.SOURCE_CODE SOURCE_CODE
, WND.DELIVERY_ID DELIVERY_ID
, WDA.PARENT_DELIVERY_DETAIL_ID CONTAINER_INSTANCE_ID
, WDD.INVENTORY_ITEM_ID ITEM_ID
, MSI.DESCRIPTION ITEM_DESCRIPTION
, WDD.SOURCE_HEADER_ID ORDER_HEADER_ID
, WDD.SOURCE_LINE_ID ORDER_LINE_ID
, WDD.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID
, WDD.CUSTOMER_ITEM_ID CUSTOMER_ITEM_ID
, WDD.ATO_LINE_ID ATO_LINE_ID
, MCI.CUSTOMER_ITEM_NUMBER CUSTOMER_PART_NUMBER
, MIF.ITEM_NUMBER SUPPLIER_PART_NUMBER
, WDD.DATE_REQUESTED REQUESTED_DATE
, WDD.SHIPPED_QUANTITY SHIPPED_QUANTITY
, WDD.REQUESTED_QUANTITY_UOM ITEM_UOM_CODE_INT
, MCC.COMMODITY_CODE COMMODITY_CODE_INT
, MSI.CONTAINER_ITEM_FLAG CONTAINER_ITEM_FLAG
, MSI.CONTAINER_TYPE_CODE CONTAINER_TYPE_CODE_INT
, MSI.CUSTOMER_ORDER_ENABLED_FLAG CUSTOMER_ORDER_FLAG
, WDD.SRC_REQUESTED_QUANTITY ORDERED_QUANTITY
, NVL(WDD.CANCELLED_QUANTITY
, 0) CANCELLED_QUANTITY
, DECODE(WDA.PARENT_DELIVERY_DETAIL_ID
, NULL
, 0
, (SELECT EDPQ.PACKED_QUANTITY
FROM WSH_DSNO_PACKED_QUANTITY_V EDPQ
WHERE WDA.DELIVERY_ID = EDPQ.DELIVERY_ID
AND NVL(WDA.PARENT_DELIVERY_DETAIL_ID
, 0) = NVL(EDPQ.CONTAINER_ID
, 0)
AND WDD.INVENTORY_ITEM_ID = EDPQ.INVENTORY_ITEM_ID
AND WDD.SOURCE_LINE_ID = EDPQ.SOURCE_LINE_ID)) PACKED_QUANTITY
, WDD.UNIT_PRICE UNIT_LIST_PRICE
, WDD.SHIP_MODEL_COMPLETE_FLAG SHIP_MODEL_COMPLETE_FLAG
, WDD.CUSTOMER_DOCK_CODE CUSTOMER_DOCK_CODE
, WDD.SHIPMENT_PRIORITY_CODE SHIPMENT_PRIORITY_CODE_INT
, WND.CONFIRM_DATE SHIPMENT_CONFIRMED_DATE
, MCI.CUSTOMER_ITEM_DESC CUSTOMER_ITEM_DESCRIPTION
, 'UN' HAZARDOUS_MATERIAL_CODE_INT
, PHC.HAZARD_CLASS HAZARD_CLASS_INT
, PHC.DESCRIPTION HAZARD_CLASS_DESCRIPTION
, WDD.ATTRIBUTE_CATEGORY DEL_DETAIL_ATTRIB_CATEGORY
, WDD.ATTRIBUTE1 DELIVERY_DETAIL_ATTRIBUTE1
, WDD.ATTRIBUTE2 DELIVERY_DETAIL_ATTRIBUTE2
, WDD.ATTRIBUTE3 DELIVERY_DETAIL_ATTRIBUTE3
, WDD.ATTRIBUTE4 DELIVERY_DETAIL_ATTRIBUTE4
, WDD.ATTRIBUTE5 DELIVERY_DETAIL_ATTRIBUTE5
, WDD.ATTRIBUTE6 DELIVERY_DETAIL_ATTRIBUTE6
, WDD.ATTRIBUTE7 DELIVERY_DETAIL_ATTRIBUTE7
, WDD.ATTRIBUTE8 DELIVERY_DETAIL_ATTRIBUTE8
, WDD.ATTRIBUTE9 DELIVERY_DETAIL_ATTRIBUTE9
, WDD.ATTRIBUTE10 DELIVERY_DETAIL_ATTRIBUTE10
, WDD.ATTRIBUTE11 DELIVERY_DETAIL_ATTRIBUTE11
, WDD.ATTRIBUTE12 DELIVERY_DETAIL_ATTRIBUTE12
, WDD.ATTRIBUTE13 DELIVERY_DETAIL_ATTRIBUTE13
, WDD.ATTRIBUTE14 DELIVERY_DETAIL_ATTRIBUTE14
, WDD.ATTRIBUTE15 DELIVERY_DETAIL_ATTRIBUTE15
, MIF.ATTRIBUTE_CATEGORY LINE_PART_CATEGORY
, MIF.ATTRIBUTE1 LINE_PART_ATTRIBUTE1
, MIF.ATTRIBUTE2 LINE_PART_ATTRIBUTE2
, MIF.ATTRIBUTE3 LINE_PART_ATTRIBUTE3
, MIF.ATTRIBUTE4 LINE_PART_ATTRIBUTE4
, MIF.ATTRIBUTE5 LINE_PART_ATTRIBUTE5
, MIF.ATTRIBUTE6 LINE_PART_ATTRIBUTE6
, MIF.ATTRIBUTE7 LINE_PART_ATTRIBUTE7
, MIF.ATTRIBUTE8 LINE_PART_ATTRIBUTE8
, MIF.ATTRIBUTE9 LINE_PART_ATTRIBUTE9
, MIF.ATTRIBUTE10 LINE_PART_ATTRIBUTE10
, MIF.ATTRIBUTE11 LINE_PART_ATTRIBUTE11
, MIF.ATTRIBUTE12 LINE_PART_ATTRIBUTE12
, MIF.ATTRIBUTE13 LINE_PART_ATTRIBUTE13
, MIF.ATTRIBUTE14 LINE_PART_ATTRIBUTE14
, MIF.ATTRIBUTE15 LINE_PART_ATTRIBUTE15
, WDD.SRC_REQUESTED_QUANTITY_UOM ORDER_QUANTITY_UOM_INT
, WDD.CUSTOMER_PROD_SEQ CUST_PRODUCTION_SEQ_NUM
, WDD.CUST_PO_NUMBER CUST_PO_NUMBER
, WDD.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE_INT
, WDD.FOB_CODE FOB_POINT_CODE_INT
, WDD.ORGANIZATION_ID ORGANIZATION_ID
, WDD.CUSTOMER_JOB CUSTOMER_JOB
, WDD.CUSTOMER_PRODUCTION_LINE CUSTOMER_PRODUCTION_LINE
, WDD.CUST_MODEL_SERIAL_NUMBER CUSTOMER_MODEL_SERIAL_NUMBER
FROM PO_HAZARD_CLASSES PHC
, MTL_SYSTEM_ITEMS MSI
, MTL_CUSTOMER_ITEMS MCI
, MTL_COMMODITY_CODES MCC
, MTL_ITEM_FLEXFIELDS MIF
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_ASSIGNMENTS_V WDA
, WSH_DELIVERY_DETAILS WDD
WHERE NVL(WDD.CONTAINER_FLAG
, 'N')='N'
AND MSI.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+)
AND WDD.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID(+)
AND MCI.COMMODITY_CODE_ID = MCC.COMMODITY_CODE_ID (+)
AND WDD.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND WDD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WDD.ORGANIZATION_ID = MIF.ORGANIZATION_ID
AND WDD.INVENTORY_ITEM_ID = MIF.INVENTORY_ITEM_ID
AND WND.DELIVERY_ID = WDA.DELIVERY_ID
AND NVL(WDD.SHIPPED_QUANTITY
, 0) > 0
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID IS NOT NULL
AND WND.DELIVERY_TYPE = 'STANDARD'