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
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
|
|
|