DBA Data[Home] [Help]

VIEW: APPS.WSH_OPSM_ASN_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                           ,
        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
   
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
, 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