SAP Business One Stock Check Query

Key Concepts

OITW vs OITM Stock Fields

OITM has OnHand which is the total across all warehouses. OITW has one row per item/warehouse combination with stock for that specific location. For warehouse-level queries, always use OITW.

Available vs On Hand

OnHand is physical stock. IsCommited is reserved for open sales orders. Available = OnHand - IsCommited. Always show available if you're checking if stock can be sold.

On Order

OnOrder is incoming stock from open purchase orders. Useful for planning — even if available is zero, stock may be coming.

Inventory vs Non-Inventory Items

Not all items track stock. Filter InvntItem = 'Y' on OITM to include only inventory items, or you'll get services and non-stock items in results.

Available Fields Reference

Stock by Item Across Warehouses

See where a specific item is stocked and what's available in each location. We join OITM for the item name and OWHS for the warehouse name. Filter OnHand > 0 to show only locations with stock.

SELECT 
    T0.ItemCode,
    T1.ItemName,
    T0.WhsCode,
    T2.WhsName,
    T0.OnHand AS Stock,
    T0.IsCommited AS Committed,
    T0.OnHand - T0.IsCommited AS Available,
    T0.OnOrder
FROM OITW T0
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
INNER JOIN OWHS T2 ON T0.WhsCode = T2.WhsCode
WHERE T0.ItemCode = 'A00001'
  AND T0.OnHand > 0
ORDER BY T0.OnHand DESC

All Items in a Warehouse

Full inventory list for one location. We calculate stock value using AvgPrice from OITM — this is the moving average cost. Filter active items with validFor = 'Y'.

SELECT 
    T0.ItemCode,
    T1.ItemName,
    T0.OnHand AS Stock,
    T0.IsCommited AS Committed,
    T0.OnHand - T0.IsCommited AS Available,
    T1.AvgPrice,
    T0.OnHand * T1.AvgPrice AS StockValue
FROM OITW T0
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
WHERE T0.WhsCode = '01'
  AND T0.OnHand > 0
  AND T1.validFor = 'Y'
ORDER BY T1.ItemName

Low Stock Alert

Items below their minimum level. MinLevel on OITM is the reorder point. The query calculates how far below minimum each item is. Negative means they need ordering.

SELECT 
    T0.ItemCode,
    T0.ItemName,
    T0.OnHand AS Stock,
    T0.MinLevel,
    T0.OnHand - T0.MinLevel AS BelowMinBy,
    T0.OnOrder AS OnOrder,
    T0.OnHand + T0.OnOrder AS ProjectedStock
FROM OITM T0
WHERE T0.validFor = 'Y'
  AND T0.InvntItem = 'Y'
  AND T0.OnHand < T0.MinLevel
  AND T0.MinLevel > 0
ORDER BY (T0.OnHand - T0.MinLevel)

Tip: Add AND (T0.OnHand + T0.OnOrder) < T0.MinLevel to exclude items where incoming POs will resolve the shortage.

Total Stock Value by Warehouse

Inventory valuation summary per location. Uses AvgPrice for valuation — adjust if you use different costing methods (FIFO, standard cost, etc.).

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