VIEW: APPS.WSH_OPSM_ASN_ITEM_DETAILS_V

Source

View Text - Preformatted

SELECT /*+ RULE */
    WDA.DELIVERY_ID DELIVERY_ID                                                                         ,
    WDD.ORGANIZATION_ID ORGANIZATION_ID                                                                 ,
    WDA.DELIVERY_ASSIGNMENT_ID DELIVERY_ASSIGNMENT_ID                                                   ,
    WDA.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.LOT_NUMBER LOT_NUMBER                                                                           ,
    MLN.PARENT_LOT_NUMBER                                                                               ,
    NVL(WSN.QUANTITY,WDD.SHIPPED_QUANTITY) SHIPPED_QUANTITY                                             ,
    WDD.REQUESTED_QUANTITY_UOM SHIPPING_QUANTITY_UOM                                                    ,  
    MLN.GEN_OBJECT_ID GEN_OBJECT_ID                                                                     ,
    MLN.ORIGINATION_DATE                                                                                ,
    MLN.BEST_BY_DATE                                                                                    ,
    MLN.RETEST_DATE                                                                                     ,
    MLN.EXPIRATION_DATE                                                                                 ,    
    NVL(NVL(MSNT.FM_SERIAL_NUMBER,WSN.FM_SERIAL_NUMBER),WDD.SERIAL_NUMBER) FROM_SERIAL_NUMBER           ,
    NVL(NVL(MSNT.TO_SERIAL_NUMBER,WSN.TO_SERIAL_NUMBER),WDD.SERIAL_NUMBER) TO_SERIAL_NUMBER         ,                                                                            
    MP.ORGANIZATION_CODE               
    FROM  WSH_DELIVERY_ASSIGNMENTS WDA      ,
          WSH_DELIVERY_DETAILS WDD          ,
          WSH_SERIAL_NUMBERS WSN            ,
          MTL_SERIAL_NUMBERS_TEMP MSNT      ,
          MTL_LOT_NUMBERS MLN               ,
          MTL_PARAMETERS MP    
  WHERE WDD.DELIVERY_DETAIL_ID     = WDA.DELIVERY_DETAIL_ID+0
  AND WDD.DELIVERY_DETAIL_ID+0     = WSN.DELIVERY_DETAIL_ID(+) 
  AND WDD.TRANSACTION_TEMP_ID+0    = MSNT.TRANSACTION_TEMP_ID(+)
  AND NVL(WDD.LOT_NUMBER,NULL)     = MLN.LOT_NUMBER(+)
  AND WDD.ORGANIZATION_ID+0        = MLN.ORGANIZATION_ID(+)
  AND WDD.INVENTORY_ITEM_ID+0      = MLN.INVENTORY_ITEM_ID(+)
  AND WDD.ORGANIZATION_ID+0        = MP.ORGANIZATION_ID
  AND WDD.CONTAINER_FLAG||''       = 'N'
  AND NVL(WDD.SHIPPED_QUANTITY, 0) > 0 
   

View Text - HTML Formatted

SELECT /*+ RULE */ WDA.DELIVERY_ID DELIVERY_ID
, WDD.ORGANIZATION_ID ORGANIZATION_ID
, WDA.DELIVERY_ASSIGNMENT_ID DELIVERY_ASSIGNMENT_ID
, WDA.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.LOT_NUMBER LOT_NUMBER
, MLN.PARENT_LOT_NUMBER
, NVL(WSN.QUANTITY
, WDD.SHIPPED_QUANTITY) SHIPPED_QUANTITY
, WDD.REQUESTED_QUANTITY_UOM SHIPPING_QUANTITY_UOM
, MLN.GEN_OBJECT_ID GEN_OBJECT_ID
, MLN.ORIGINATION_DATE
, MLN.BEST_BY_DATE
, MLN.RETEST_DATE
, MLN.EXPIRATION_DATE
, NVL(NVL(MSNT.FM_SERIAL_NUMBER
, WSN.FM_SERIAL_NUMBER)
, WDD.SERIAL_NUMBER) FROM_SERIAL_NUMBER
, NVL(NVL(MSNT.TO_SERIAL_NUMBER
, WSN.TO_SERIAL_NUMBER)
, WDD.SERIAL_NUMBER) TO_SERIAL_NUMBER
, MP.ORGANIZATION_CODE
FROM WSH_DELIVERY_ASSIGNMENTS WDA
, WSH_DELIVERY_DETAILS WDD
, WSH_SERIAL_NUMBERS WSN
, MTL_SERIAL_NUMBERS_TEMP MSNT
, MTL_LOT_NUMBERS MLN
, MTL_PARAMETERS MP
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID+0
AND WDD.DELIVERY_DETAIL_ID+0 = WSN.DELIVERY_DETAIL_ID(+)
AND WDD.TRANSACTION_TEMP_ID+0 = MSNT.TRANSACTION_TEMP_ID(+)
AND NVL(WDD.LOT_NUMBER
, NULL) = MLN.LOT_NUMBER(+)
AND WDD.ORGANIZATION_ID+0 = MLN.ORGANIZATION_ID(+)
AND WDD.INVENTORY_ITEM_ID+0 = MLN.INVENTORY_ITEM_ID(+)
AND WDD.ORGANIZATION_ID+0 = MP.ORGANIZATION_ID
AND WDD.CONTAINER_FLAG||'' = 'N'
AND NVL(WDD.SHIPPED_QUANTITY
, 0) > 0