View: WSH_OPSM_ASN_ITEMS_COMM_V

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

View Text

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
, WDD.CUSTOMER_ID SOLDTO_CUSTOMER_ID
, WDD.SOLD_TO_CONTACT_ID SOLDTO_CONTACT_ID
, WDD.SOURCE_HEADER_ID+0 ORDER_HEADER_ID
, WDD.SOURCE_LINE_ID +0 ORDER_LINE_ID
, WDD.SOURCE_HEADER_NUMBER SALES_ORDER_NUMBER
, WDD.SOURCE_HEADER_TYPE_NAME ORDERTYPE
, 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
, 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.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.SHIP_TO_CONTACT_ID DESTINATION_CONT_ID
, WDD.CUSTOMER_JOB CUSTOMER_JOB
, WDD.CUSTOMER_PRODUCTION_LINE CUSTOMER_PRODUCTION_LINE
, WDD.CUST_MODEL_SERIAL_NUMBER CUSTOMER_MODEL_SERIAL_NUMBER
, MSIBK.CONCATENATED_SEGMENTS CONCATENATED_SEGMENTS
, MSI.DESCRIPTION DESCRIPTION
, WDD.NET_WEIGHT NET_WEIGHT
, WDD.WEIGHT_UOM_CODE WEIGHT_UOM_CODE
, WDD.VOLUME VOLUME
, WDD.VOLUME_UOM_CODE VOLUME_UOM_CODE
, WDD.PACKING_INSTRUCTIONS PACKING_INSTRUCTIONS
, WDD.ITEM_DESCRIPTION ITEM_DESCRIPTION
, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, DECODE(( SELECT UPPER(MCR.CROSS_REFERENCE)
FROM MTL_CROSS_REFERENCES_VL MCR
WHERE MCR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID+0
AND MCR.ORGANIZATION_ID = MSI.ORGANIZATION_ID+0
AND TO_CHAR(MCR.LAST_UPDATE_DATE
, 'DD/MM/YYYY-HH24.MM.SS') = (SELECT MAX(TO_CHAR(MCR1.LAST_UPDATE_DATE
, 'DD/MM/YYYY-HH24.MM.SS'))
FROM MTL_CROSS_REFERENCES_VL MCR1
WHERE MCR1.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID+0
AND MCR1.ORGANIZATION_ID = MSI.ORGANIZATION_ID+0
AND MCR1.CROSS_REFERENCE_TYPE ='OPSM INTEGRATED'))
, 'NO'
, 0
, 1) CROSS_REFERENCE
, (SELECT MCR.ATTRIBUTE1
FROM MTL_CROSS_REFERENCES_VL MCR
WHERE MCR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID+0
AND MCR.ORGANIZATION_ID = (SELECT MASTER_ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID=MSI.ORGANIZATION_ID+0)
AND MCR.CROSS_REFERENCE_TYPE ='OPSM INTEGRATED'
AND MCR.CROSS_REFERENCE = 'YES')SERIAL_TYPE
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 WDA
, WSH_DELIVERY_DETAILS WDD
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
WHERE WND.DELIVERY_ID+0 = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID+0 = WDD.DELIVERY_DETAIL_ID
AND WDD.CUSTOMER_ITEM_ID+0 = MCI.CUSTOMER_ITEM_ID(+)
AND MCI.COMMODITY_CODE_ID+0 = MCC.COMMODITY_CODE_ID (+)
AND WDD.ORGANIZATION_ID+0 = MSI.ORGANIZATION_ID
AND WDD.INVENTORY_ITEM_ID+0 = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID+0 = MSIBK.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID+0 = MSIBK.INVENTORY_ITEM_ID
AND WDD.ORGANIZATION_ID+0 = MIF.ORGANIZATION_ID
AND WDD.INVENTORY_ITEM_ID+0 = MIF.INVENTORY_ITEM_ID
AND NVL(WDD.SHIPPED_QUANTITY
, 0) > 0
AND MSI.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+)
AND NVL(WDD.CONTAINER_FLAG
, 'N') ='N'
AND WDA.DELIVERY_ID IS NOT NULL