SAP Business One Warehouses Query

Key Concepts

WhsCode and WhsName

WhsCode is the short identifier (01, WH-MAIN, EAST). WhsName is the full description. Codes are often numeric or abbreviated — join OWHS to get readable names.

Active vs Inactive

Inactive = 'N' filters to active warehouses. Inactive warehouses can't receive new transactions but may still have historical data.

Bin Locations

BinActivat = 'Y' means the warehouse uses bin locations for detailed storage tracking. DftBinEnfd = 'Y' means a bin must be specified for transactions.

Drop Ship Warehouse

DropShip = 'Y' marks warehouses used for drop shipping — items ship directly from vendor to customer without passing through your stock.

Available Fields Reference

List All Warehouses

Get all active warehouses with their location information. Filter Inactive = 'N' for current warehouses only.

SELECT 
    WhsCode,
    WhsName,
    Street,
    City,
    State,
    ZipCode,
    Country
FROM OWHS
WHERE Inactive = 'N'
ORDER BY WhsName

Warehouse Details

Full warehouse info including bin location settings. BinActivat tells you if the warehouse tracks exact storage locations.

SELECT 
    WhsCode,
    WhsName,
    Street,
    Block,
    City,
    State,
    ZipCode,
    Country,
    CASE BinActivat WHEN 'Y' THEN 'Yes' ELSE 'No' END AS UsesBinLocations,
    CASE DftBinEnfd WHEN 'Y' THEN 'Yes' ELSE 'No' END AS BinRequired,
    CASE DropShip WHEN 'Y' THEN 'Yes' ELSE 'No' END AS DropShipWarehouse
FROM OWHS
WHERE Inactive = 'N'
ORDER BY WhsCode

Warehouses with Inventory Summary

Count how many items are stocked in each location and total units. LEFT JOIN ensures warehouses with no stock still appear. We sum only positive OnHand values.

SELECT 
    T0.WhsCode,
    T0.WhsName,
    T0.City,
    COUNT(DISTINCT CASE WHEN T1.OnHand > 0 THEN T1.ItemCode END) AS ItemsStocked,
    ISNULL(SUM(T1.OnHand), 0) AS TotalUnits
FROM OWHS T0
LEFT JOIN OITW T1 ON T0.WhsCode = T1.WhsCode AND T1.OnHand > 0
WHERE T0.Inactive = 'N'
GROUP BY T0.WhsCode, T0.WhsName, T0.City
ORDER BY TotalUnits DESC

Warehouse Inventory Valuation

Total inventory value per warehouse using average cost from OITM. This gives you a quick view of inventory investment by location.

SELECT 
    T0.WhsCode,
    T0.WhsName,
    COUNT(DISTINCT T1.ItemCode) AS ItemCount,
    SUM(T1.OnHand) AS TotalUnits,
    SUM(T1.OnHand * T2.AvgPrice) AS TotalValue
FROM OWHS T0
INNER JOIN OITW T1 ON T0.WhsCode = T1.WhsCode
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.Inactive = 'N'
  AND T1.OnHand > 0
  AND T2.validFor = 'Y'
GROUP BY T0.WhsCode, T0.WhsName
ORDER BY TotalValue DESC

Note: AvgPrice is moving average cost. If you use standard costing, replace with the appropriate cost field.