VIEW: APPS.WSH_OPSM_ASN_CONTAINERS_V

Source

View Text - Preformatted

SELECT  DISTINCT WCI.DELIVERY_DETAIL_ID CONTAINER_INSTANCE_ID   ,
        WCI.CONTAINER_NAME LPN                                     ,
        WCI.INVENTORY_ITEM_ID CONTAINER_ITEM_ID                    ,
        WCI.MASTER_SERIAL_NUMBER MASTER_SERIAL_NUMBER              ,
        WCI.SERIAL_NUMBER CONTAINER_SERIAL_NUMBER                  ,
        WCI.LOT_NUMBER LOT_NUMBER                                  ,
        WDA.PARENT_DELIVERY_DETAIL_ID PARENT_CONTAINER_INSTANCE_ID ,
        WCIP.CONTAINER_NAME PARENT_LPN                             ,
        WCI.CONTAINER_TYPE_CODE CONTAINER_TYPE_INT                 ,
        WCI.GROSS_WEIGHT CONTAINER_GROSS_WEIGHT                    ,
        WCI.WEIGHT_UOM_CODE CONTAINER_GROSS_WEIGHT_UOM_INT         ,
        WCI.VOLUME CONTAINER_VOLUME                                ,
        WCI.VOLUME_UOM_CODE CONTAINER_VOLUME_UOM_CODE_INT          ,
        WCI.NET_WEIGHT CONTAINER_NET_WEIGHT                        ,
        WCI.FILL_PERCENT CONTAINER_FILL_PERCENT                    ,
        WCI.MAXIMUM_VOLUME CONTAINER_MAXIMUM_VOLUME                ,
        WCI.MAXIMUM_LOAD_WEIGHT CONTAINER_MAXIMUM_LOAD_WEIGHT      ,
        WCI.MINIMUM_FILL_PERCENT CONTAINER_MINIMUM_FILL_PERCENT    ,
        WCI.GROSS_WEIGHT - WCI.NET_WEIGHT CONTAINER_TARE_WEIGHT    ,
        WCI.SEAL_CODE CONTAINER_SEAL_CODE                          ,
        WCI.TRACKING_NUMBER CONTAINER_TRACKING_NUMBER              ,
        WCI.ITEM_DESCRIPTION ITEM_DESCRIPTION                      ,
        WDA.DELIVERY_ID DELIVERY_ID                                ,
        (SELECT 'SSCC-18'  
          FROM  mtl_parameters mp
         WHERE  mp.organization_id=wci.organization_id
           AND  mp.TOTAL_LPN_LENGTH=18
           AND  mp.UCC_128_SUFFIX_FLAG='Y')LPN_TYPE
  FROM  WSH_DELIVERY_DETAILS WCIP       ,
        WSH_DELIVERY_ASSIGNMENTS WDA    ,
        WSH_DELIVERY_DETAILS WCI        ,
        MTL_SYSTEM_ITEMS MTI
 WHERE  NVL(WDA.PARENT_DELIVERY_DETAIL_ID, '-99999')    = WCIP.DELIVERY_DETAIL_ID (+)
   AND WDA.DELIVERY_DETAIL_ID                           = WCI.DELIVERY_DETAIL_ID
   AND WCI.CONTAINER_FLAG                               = 'Y'
   AND WCI.INVENTORY_ITEM_ID                            = MTI.INVENTORY_ITEM_ID(+)
   AND WCI.ORGANIZATION_ID                              = MTI.ORGANIZATION_ID(+)
   

View Text - HTML Formatted

SELECT DISTINCT WCI.DELIVERY_DETAIL_ID CONTAINER_INSTANCE_ID
, WCI.CONTAINER_NAME LPN
, WCI.INVENTORY_ITEM_ID CONTAINER_ITEM_ID
, WCI.MASTER_SERIAL_NUMBER MASTER_SERIAL_NUMBER
, WCI.SERIAL_NUMBER CONTAINER_SERIAL_NUMBER
, WCI.LOT_NUMBER LOT_NUMBER
, WDA.PARENT_DELIVERY_DETAIL_ID PARENT_CONTAINER_INSTANCE_ID
, WCIP.CONTAINER_NAME PARENT_LPN
, WCI.CONTAINER_TYPE_CODE CONTAINER_TYPE_INT
, WCI.GROSS_WEIGHT CONTAINER_GROSS_WEIGHT
, WCI.WEIGHT_UOM_CODE CONTAINER_GROSS_WEIGHT_UOM_INT
, WCI.VOLUME CONTAINER_VOLUME
, WCI.VOLUME_UOM_CODE CONTAINER_VOLUME_UOM_CODE_INT
, WCI.NET_WEIGHT CONTAINER_NET_WEIGHT
, WCI.FILL_PERCENT CONTAINER_FILL_PERCENT
, WCI.MAXIMUM_VOLUME CONTAINER_MAXIMUM_VOLUME
, WCI.MAXIMUM_LOAD_WEIGHT CONTAINER_MAXIMUM_LOAD_WEIGHT
, WCI.MINIMUM_FILL_PERCENT CONTAINER_MINIMUM_FILL_PERCENT
, WCI.GROSS_WEIGHT - WCI.NET_WEIGHT CONTAINER_TARE_WEIGHT
, WCI.SEAL_CODE CONTAINER_SEAL_CODE
, WCI.TRACKING_NUMBER CONTAINER_TRACKING_NUMBER
, WCI.ITEM_DESCRIPTION ITEM_DESCRIPTION
, WDA.DELIVERY_ID DELIVERY_ID
, (SELECT 'SSCC-18'
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID=WCI.ORGANIZATION_ID
AND MP.TOTAL_LPN_LENGTH=18
AND MP.UCC_128_SUFFIX_FLAG='Y')LPN_TYPE
FROM WSH_DELIVERY_DETAILS WCIP
, WSH_DELIVERY_ASSIGNMENTS WDA
, WSH_DELIVERY_DETAILS WCI
, MTL_SYSTEM_ITEMS MTI
WHERE NVL(WDA.PARENT_DELIVERY_DETAIL_ID
, '-99999') = WCIP.DELIVERY_DETAIL_ID (+)
AND WDA.DELIVERY_DETAIL_ID = WCI.DELIVERY_DETAIL_ID
AND WCI.CONTAINER_FLAG = 'Y'
AND WCI.INVENTORY_ITEM_ID = MTI.INVENTORY_ITEM_ID(+)
AND WCI.ORGANIZATION_ID = MTI.ORGANIZATION_ID(+)