Untitled

 avatar
unknown
plain_text
2 years ago
2.6 kB
4
Indexable
SELECT 
    AC.Warehouse, 
    AC.COMPANY, 
    AC.ZONE Zone,
    AC.AISLE, 
    AC.BAY, 
    AC.LEVEL,
    AC.Assignment_Zone AssignmentZone, 
    CASE  
        WHEN AC.Assignment_Zone LIKE '%SM' THEN 'Small' 
        WHEN AC.Assignment_Zone LIKE '%MD' THEN 'Medium'
        WHEN AC.Assignment_Zone LIKE '%LG' THEN 'Large' 
        WHEN AC.Assignment_Zone LIKE '%XL' THEN 'Extra Large'
        WHEN AC.Assignment_Zone LIKE '%LN' THEN 'Extra Long'
        WHEN AC.Assignment_Zone LIKE '%HG' THEN 'Huge'  
        WHEN AC.Assignment_Zone LIKE '%GY' THEN 'Gaylord'
        WHEN AC.Assignment_Zone LIKE '%PL' THEN 'Pallet'
    END Size, 
    AC.MAX_VOLUME MaxVolume, 
    AC.Location_Barcode as CONT,
    COUNT(DISTINCT AC.Location_Barcode) TotalLocations, 
    COALESCE(LocationsWithInventory.LocationsWithInventory, 0) LocationsWithInventory,  
    SUM(CASE WHEN AC.Actual_Inventory = 0 THEN 1 ELSE 0 END) EmptyLocations, 
    CAST(DIV0(SUM(CASE WHEN AC.Actual_Inventory > 0 THEN 1 ELSE 0 END), CAST(COUNT(DISTINCT AC.Location_Barcode) AS DECIMAL(10,2))) AS DECIMAL(10,2)) PercentOccupiedByLocation, 
    CAST(SUM(AC.Location_Length * AC.Location_Width * AC.Location_Height) AS INT) LocationCapacityByVolume, 
    CAST(SUM(AC.Actual_Inventory * ST.UNIT_VOLUME) AS INT) VolumeOccupied, 
    CAST(DIV0(SUM(AC.Actual_Inventory * ST.UNIT_VOLUME), SUM(AC.Location_Length * AC.Location_Width * AC.Location_Height)) AS DECIMAL(10,2))  AS PercentOccupiedByVolume, 
    CAST(SUM(AC.Actual_Inventory) AS INT) Units ,
    AC.PICK_DETERMINATION_ZONE DETERMINATION_ZONE
FROM "TL2"."BI_DATA"."ACTIVE_LOCATION" AC
LEFT OUTER JOIN "TL2"."BI_DATA"."ITEM_MASTER_DETAIL" ST ON         
    ST.COMPANY = AC.COMPANY AND 
    ST.DIVISION = AC.DIVISION AND 
    ST.SKU_SEASON = AC.SKU_Season AND 
    ST.SKU_SEASON_YEAR = AC.SKU_Season_Year AND 
    ST.SKU_STYLE = AC.SKU_Style AND 
    ST.SKU_STYLE_SUFFIX = AC.SKU_Style_Suffix AND 
    ST.SKU_COLOR = AC.SKU_Color AND 
    ST.SKU_COLOR_SUFFIX = AC.SKU_Color_Suffix AND 
    ST.SKU_SEC_DIMENSION = AC.SKU_SEC_DIMENSION AND 
    ST.QUALITY = AC.SKU_Quality AND 
    ST.SKU_SIZE_RNGE_CODE = AC.SKU_Size_Range_Code AND 
    ST.SKU_SIZE_RELATIVE_POSITION_IN_TABLE = AC.SKU_Size_Relaive_Posn_In_Table AND
    CASE 
        WHEN ST.WMI_DATE_TIME_LAST_MODIFIED IS NULL THEN 'PKMS' 
        ELSE 'WMI' 
    END = CASE 
        WHEN AC.WMI_DATE_TIME_LAST_MODIFIED IS NULL THEN 'PKMS' 
        ELSE 'WMI' 
    END
LEFT OUTER JOIN (
    SELECT COUNT(DISTINCT AC.Location_Barcode)has context menuComposeParagraph
Editor is loading...