How to find dead stock in SAP Business One

What counts as dead stock?

Dead stock is inventory you still hold that hasn't moved — no sale, no issue to production, no receipt — for a long time. The usual threshold is 12 months. Stock that has moved, but rarely, is slow-moving: typically no movement for 6–12 months, or more than a year's worth of stock on hand at the current rate of demand.

It matters because dead stock is cash you can't use. On top of the purchase price, carrying it — space, insurance, handling, obsolescence — is commonly estimated at 20–30% of its value per year. And it hides: SAP Business One will happily show you what's in stock and what it's worth, but not what hasn't moved.

Does SAP Business One have a dead stock report?

Not as a single standard report. The information is all there, split across two places:

• What you hold and what it's worth — stock per warehouse and its value (the Inventory Audit Report, or the OITW table).
• When it last moved — every stock posting with its date (the Inventory Posting List, or the OINM inventory journal).

Dead stock is simply the join between the two. There are two practical ways to make it.

Method 1: the two standard reports

No SQL or admin rights needed — anyone with access to inventory reports can do this.

1. Run Inventory → Inventory Reports → Inventory Audit Report for all items and warehouses, up to today, and export it to Excel. This gives you quantity and value per item and warehouse.
2. Run Inventory → Inventory Reports → Inventory Posting List for the last 24 months and export it. This gives you every movement with its posting date.
3. In Excel, find the latest posting date for each item and warehouse (a pivot table with Max of Posting Date works), look it up against the audit report, and filter for lines with stock on hand whose last movement is more than 12 months ago.

It works, but the posting list gets big fast and the pivot-and-lookup step is fiddly to repeat every month. Inventory Doctor does exactly this join for you, free: drop the same two exports in and it returns dead stock, slow movers, stock ageing and the cash you could recover — and the files are analysed in your browser, never uploaded.

Method 2: a SQL query

If you can run queries (Query Manager, or SQL access for your partner or IT), this lists every item-and-warehouse line with stock that hasn't moved in 365 days, with its value — most expensive first. It only reads data; it changes nothing.

SQL Server version

/* Dead stock: stock on hand with no movement in the last 365 days */
SELECT
    W.ItemCode,
    I.ItemName,
    W.WhsCode,
    W.OnHand,
    W.StockValue,
    W.StockValue / W.OnHand AS UnitCost,
    M.LastMovement,
    M.LastIssue,
    DATEDIFF(DAY, M.LastMovement, GETDATE()) AS DaysSinceMovement
FROM OITW W
JOIN OITM I ON I.ItemCode = W.ItemCode
LEFT JOIN (
    SELECT
        ItemCode,
        Warehouse,
        MAX(DocDate) AS LastMovement,
        MAX(CASE WHEN OutQty > 0 AND TransType <> 67 THEN DocDate END) AS LastIssue
    FROM OINM
    WHERE InQty <> 0 OR OutQty <> 0
    GROUP BY ItemCode, Warehouse
) M ON M.ItemCode = W.ItemCode AND M.Warehouse = W.WhsCode
WHERE W.OnHand > 0
  AND I.InvntItem = 'Y'
  AND (M.LastMovement IS NULL OR M.LastMovement < DATEADD(DAY, -365, GETDATE()))
ORDER BY W.StockValue DESC

SAP HANA version

/* Dead stock: stock on hand with no movement in the last 365 days */
SELECT
    W."ItemCode",
    I."ItemName",
    W."WhsCode",
    W."OnHand",
    W."StockValue",
    W."StockValue" / W."OnHand" AS "UnitCost",
    M."LastMovement",
    M."LastIssue",
    DAYS_BETWEEN(M."LastMovement", CURRENT_DATE) AS "DaysSinceMovement"
FROM "OITW" W
JOIN "OITM" I ON I."ItemCode" = W."ItemCode"
LEFT JOIN (
    SELECT
        "ItemCode",
        "Warehouse",
        MAX("DocDate") AS "LastMovement",
        MAX(CASE WHEN "OutQty" > 0 AND "TransType" <> 67 THEN "DocDate" END) AS "LastIssue"
    FROM "OINM"
    WHERE "InQty" <> 0 OR "OutQty" <> 0
    GROUP BY "ItemCode", "Warehouse"
) M ON M."ItemCode" = W."ItemCode" AND M."Warehouse" = W."WhsCode"
WHERE W."OnHand" > 0
  AND I."InvntItem" = 'Y'
  AND (M."LastMovement" IS NULL OR M."LastMovement" < ADD_DAYS(CURRENT_DATE, -365))
ORDER BY W."StockValue" DESC

How it works

• OITW holds stock per item and warehouse; OITM is the item master.
• OINM is the inventory audit journal — one row per stock posting. The subquery takes the most recent posting date per item and warehouse, ignoring zero-quantity rows such as revaluations.
• LastIssue is the last time stock actually left, excluding inventory transfers (transaction type 67). Sort by it if you care about demand rather than any movement.
• OITW.StockValue is the value B1 itself carries for each item and warehouse, whatever the valuation method (moving average, standard, FIFO or batch) — it reconciles exactly with the inventory journal. On very old releases without that column, use W.OnHand * W.AvgPrice as an approximation.
• Lines with no postings at all (LastMovement empty) are included: they haven't moved in the history B1 holds.

To change the threshold, replace 365. For a slow-moving list, use 180 and add AND M.LastMovement >= DATEADD(DAY, -365, GETDATE()) (on HANA: ADD_DAYS(CURRENT_DATE, -365)) so dead lines aren't counted twice. As with any query, try it in a test company first.

Five traps that make the numbers wrong

1. Transfers reset the clock. Moving dead stock between warehouses counts as a movement, so it looks alive. Check LastIssue, not just LastMovement.
2. Receipts reset it too. If MRP or a buyer re-ordered an item that never sells, its last movement is recent — but it's still not selling. That's overstock, and it's often worth more than the dead stock itself. Compare stock on hand against the last 12 months of issues.
3. Negative stock from timing or "allow negative inventory" settings distorts totals. Fix or exclude those lines.
4. Non-inventory and inactive items. The query keeps inventory items only; also check items set to inactive or frozen that still carry stock.
5. Seasonality and spares. A spare part for a critical machine may legitimately sit for two years. Flag those rather than liquidating them.

What to do with dead stock

• Return it to the supplier — many accept returns for credit, especially on current lines.
• Transfer it to a warehouse or branch where the item still sells.
• Sell it down — bundles, discounts, trade buyers or liquidators.
• Write it down or off with your accountant — in B1 typically an inventory revaluation or a goods issue to a write-off account.
• Fix the cause — reorder points, minimum stock levels and MRP settings that keep buying things that don't sell. Otherwise next year's list looks the same.

Skip the spreadsheet

If you'd rather not maintain a query or a pivot table, Inventory Doctor takes the two standard reports above and gives you an Inventory Health Report in under five minutes: dead stock, slow movers, stock ageing buckets, transfer opportunities and an estimate of the cash you could recover. It's free, needs no integration, and your data never leaves your computer.

FAQ

How long without movement counts as dead stock?

Twelve months without any sale, issue or receipt is the most common threshold; six to twelve months is usually "slow-moving". Seasonal and spare-parts businesses often use longer windows.

Which SAP Business One table holds stock movements?

OINM, the inventory audit journal: every stock posting with its date, warehouse, quantity in and out, and transaction type. OITW holds stock per warehouse and OITM the item master.

Does the query work on SAP HANA?

Yes — use the HANA version above; it differs only in quoting and date functions.