SpanMind Get a walkthrough
Home › Inventory Doctor › SAP B1 alert pack

The SAP Business One alert pack

Eleven ready-made queries that turn SAP Business One's built-in Alerts Management into an early-warning system for inventory: dead stock, over-promised orders, repeated corrections, late postings, stock that was never put away and more. Each one is read-only, free, and tested on SAP's own demo company.

By SpanMind · October 2026 · Tested on the SAP Business One 10.0 UK demo database (SQL Server) · Download all queries (.zip)

How to set up an alert in SAP Business One

  1. Open the Query Generator (Tools → Queries → Query Generator), switch to editing the SQL, paste a query from this page and save it to a category in Query Manager.
  2. Go to Administration → Alerts Management and create a new user-defined alert.
  3. Choose the saved query, set the frequency (suggestions below), the priority and the users who should receive it, and mark it Active.

B1 runs the query on schedule and alerts the users only when it returns rows — so a quiet day means nothing needs attention. The queries use fixed thresholds (7, 90, 365 days and so on) because alerts can't prompt for values; change the numbers to suit you. As with any query, try it in a test company first.

The queries

  1. Dead stock
  2. Promised beyond stock, even after open purchase orders
  3. Below minimum stock after open orders
  4. Manual stock corrections this week — gross vs net
  5. Items corrected again and again
  6. Movements recorded late
  7. Goods receipts that always match the purchase order
  8. Stock not put away
  9. Negative stock
  10. Transfer requests left open
  11. Bin stock that hasn't moved in six months

1. Dead stock

Stock with no movement for a year is cash you can't use — and SAP Business One has no standard report that lists it.

Returns: Item, warehouse, quantity and value of every line with no movement in 365 days, most valuable first.
Suggested alert frequency: Weekly · On SAP's UK demo company: 24 lines

/* Dead stock: items with stock and no movement in the last 365 days */
SELECT TOP 100
    W.ItemCode, I.ItemName, W.WhsCode, W.OnHand, W.StockValue,
    M.LastMovement, 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
    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: items with stock and no movement in the last 365 days */
SELECT
    W."ItemCode", I."ItemName", W."WhsCode", W."OnHand", W."StockValue",
    M."LastMovement", 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"
    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
LIMIT 100

2. Promised beyond stock, even after open purchase orders

Customers have been promised more than you have — and what's on order won't cover it. These are tomorrow's late deliveries.

Returns: Lines where in stock − committed + ordered is below zero.
Suggested alert frequency: Daily · On SAP's UK demo company: 13 lines

/* Promised to customers beyond stock, even after open purchase orders */
SELECT
    W.ItemCode, I.ItemName, W.WhsCode, W.OnHand, W.IsCommited AS Committed, W.OnOrder AS Ordered,
    W.OnHand - W.IsCommited + W.OnOrder AS Available
FROM OITW W
JOIN OITM I ON I.ItemCode = W.ItemCode
WHERE I.InvntItem = 'Y' AND W.OnHand - W.IsCommited + W.OnOrder < 0
ORDER BY W.OnHand - W.IsCommited + W.OnOrder
SAP HANA version
/* Promised to customers beyond stock, even after open purchase orders */
SELECT
    W."ItemCode", I."ItemName", W."WhsCode", W."OnHand", W."IsCommited" AS "Committed", W."OnOrder" AS "Ordered",
    W."OnHand" - W."IsCommited" + W."OnOrder" AS "Available"
FROM "OITW" W
JOIN "OITM" I ON I."ItemCode" = W."ItemCode"
WHERE I."InvntItem" = 'Y' AND W."OnHand" - W."IsCommited" + W."OnOrder" < 0
ORDER BY W."OnHand" - W."IsCommited" + W."OnOrder"

3. Below minimum stock after open orders

Checks open purchase orders before flagging a reorder, so you don't double-order. Works whether you set minimums per warehouse or per item.

Returns: Lines whose available quantity (after open orders) is under the minimum.
Suggested alert frequency: Daily · On SAP's UK demo company: 0 lines (the demo company sets no minimums)

/* Below minimum stock after open purchase orders (warehouse or item level) */
SELECT W.ItemCode, I.ItemName, W.WhsCode, W.OnHand - W.IsCommited + W.OnOrder AS Available, W.MinStock AS Minimum, 'Warehouse' AS Level
FROM OITW W JOIN OITM I ON I.ItemCode = W.ItemCode
WHERE I.InvntItem = 'Y' AND W.MinStock > 0 AND W.OnHand - W.IsCommited + W.OnOrder < W.MinStock
UNION ALL
SELECT I.ItemCode, I.ItemName, '*', I.OnHand - I.IsCommited + I.OnOrder, I.MinLevel, 'Item'
FROM OITM I
WHERE I.InvntItem = 'Y' AND I.MinLevel > 0 AND I.OnHand - I.IsCommited + I.OnOrder < I.MinLevel
SAP HANA version
/* Below minimum stock after open purchase orders (warehouse or item level) */
SELECT W."ItemCode", I."ItemName", W."WhsCode", W."OnHand" - W."IsCommited" + W."OnOrder" AS "Available", W."MinStock" AS "Minimum", 'Warehouse' AS "Level"
FROM "OITW" W JOIN "OITM" I ON I."ItemCode" = W."ItemCode"
WHERE I."InvntItem" = 'Y' AND W."MinStock" > 0 AND W."OnHand" - W."IsCommited" + W."OnOrder" < W."MinStock"
UNION ALL
SELECT I."ItemCode", I."ItemName", '*', I."OnHand" - I."IsCommited" + I."OnOrder", I."MinLevel", 'Item'
FROM "OITM" I
WHERE I."InvntItem" = 'Y' AND I."MinLevel" > 0 AND I."OnHand" - I."IsCommited" + I."OnOrder" < I."MinLevel"

4. Manual stock corrections this week — gross vs net

A net figure lets overages hide shortages. This shows how much was corrected up and down, per item.

Returns: Manual goods receipts and issues (not from production) and inventory counting postings in the last 7 days, with gross and net value.
Suggested alert frequency: Weekly · On SAP's UK demo company: 32 lines over 12 months

/* Manual stock corrections in the last 7 days: gross vs net per item */
SELECT
    N.ItemCode, N.Warehouse, COUNT(*) AS Corrections,
    SUM(N.InQty) AS QtyUp, SUM(N.OutQty) AS QtyDown,
    SUM(ABS(N.TransValue)) AS GrossValue, SUM(N.TransValue) AS NetValue
FROM OINM N
WHERE N.DocDate >= DATEADD(DAY, -7, GETDATE())
  AND (N.TransType = 10000071
    OR (N.TransType = 59 AND NOT EXISTS (SELECT 1 FROM IGN1 L WHERE L.DocEntry = N.CreatedBy AND L.LineNum = N.DocLineNum AND L.BaseType = 202))
    OR (N.TransType = 60 AND NOT EXISTS (SELECT 1 FROM IGE1 L WHERE L.DocEntry = N.CreatedBy AND L.LineNum = N.DocLineNum AND L.BaseType = 202)))
GROUP BY N.ItemCode, N.Warehouse
ORDER BY SUM(ABS(N.TransValue)) DESC
SAP HANA version
/* Manual stock corrections in the last 7 days: gross vs net per item */
SELECT
    N."ItemCode", N."Warehouse", COUNT(*) AS "Corrections",
    SUM(N."InQty") AS "QtyUp", SUM(N."OutQty") AS "QtyDown",
    SUM(ABS(N."TransValue")) AS "GrossValue", SUM(N."TransValue") AS "NetValue"
FROM "OINM" N
WHERE N."DocDate" >= ADD_DAYS(CURRENT_DATE, -7)
  AND (N."TransType" = 10000071
    OR (N."TransType" = 59 AND NOT EXISTS (SELECT 1 FROM "IGN1" L WHERE L."DocEntry" = N."CreatedBy" AND L."LineNum" = N."DocLineNum" AND L."BaseType" = 202))
    OR (N."TransType" = 60 AND NOT EXISTS (SELECT 1 FROM "IGE1" L WHERE L."DocEntry" = N."CreatedBy" AND L."LineNum" = N."DocLineNum" AND L."BaseType" = 202)))
GROUP BY N."ItemCode", N."Warehouse"
ORDER BY SUM(ABS(N."TransValue")) DESC

5. Items corrected again and again

An item that needs correcting three times in 90 days usually has a location or process problem — the classic "phantom stock".

Returns: Items with 3 or more manual corrections in 90 days.
Suggested alert frequency: Weekly · On SAP's UK demo company: 11 lines over 12 months

/* Items corrected 3 or more times in 90 days: likely phantom stock or a process gap */
SELECT
    N.ItemCode, N.Warehouse, COUNT(*) AS Corrections,
    SUM(N.InQty) AS QtyUp, SUM(N.OutQty) AS QtyDown, MAX(N.DocDate) AS LastCorrection
FROM OINM N
WHERE N.DocDate >= DATEADD(DAY, -90, GETDATE())
  AND (N.TransType = 10000071
    OR (N.TransType = 59 AND NOT EXISTS (SELECT 1 FROM IGN1 L WHERE L.DocEntry = N.CreatedBy AND L.LineNum = N.DocLineNum AND L.BaseType = 202))
    OR (N.TransType = 60 AND NOT EXISTS (SELECT 1 FROM IGE1 L WHERE L.DocEntry = N.CreatedBy AND L.LineNum = N.DocLineNum AND L.BaseType = 202)))
GROUP BY N.ItemCode, N.Warehouse
HAVING COUNT(*) >= 3
ORDER BY COUNT(*) DESC
SAP HANA version
/* Items corrected 3 or more times in 90 days: likely phantom stock or a process gap */
SELECT
    N."ItemCode", N."Warehouse", COUNT(*) AS "Corrections",
    SUM(N."InQty") AS "QtyUp", SUM(N."OutQty") AS "QtyDown", MAX(N."DocDate") AS "LastCorrection"
FROM "OINM" N
WHERE N."DocDate" >= ADD_DAYS(CURRENT_DATE, -90)
  AND (N."TransType" = 10000071
    OR (N."TransType" = 59 AND NOT EXISTS (SELECT 1 FROM "IGN1" L WHERE L."DocEntry" = N."CreatedBy" AND L."LineNum" = N."DocLineNum" AND L."BaseType" = 202))
    OR (N."TransType" = 60 AND NOT EXISTS (SELECT 1 FROM "IGE1" L WHERE L."DocEntry" = N."CreatedBy" AND L."LineNum" = N."DocLineNum" AND L."BaseType" = 202)))
GROUP BY N."ItemCode", N."Warehouse"
HAVING COUNT(*) >= 3
ORDER BY COUNT(*) DESC

6. Movements recorded late

When receipts or issues are entered days after they happened, the system "learns late" — the main cause of stock that's there on screen but not on the shelf.

Returns: Stock movements entered more than 3 days after their posting date, with who entered them.
Suggested alert frequency: Daily · On SAP's UK demo company: 100+ rows over 12 months — the demo's entry dates are generated, so its delays aren't realistic

/* Stock movements entered more than 3 days after their posting date (last 7 days of entries) */
SELECT TOP 100
    N.TransType, N.BASE_REF AS DocNum, N.ItemCode, N.Warehouse, N.InQty - N.OutQty AS Qty,
    N.DocDate AS PostingDate, N.CreateDate AS EnteredOn,
    DATEDIFF(DAY, N.DocDate, N.CreateDate) AS DaysLate, U.USER_CODE AS EnteredBy
FROM OINM N
LEFT JOIN OUSR U ON U.USERID = N.UserSign
WHERE N.CreateDate >= DATEADD(DAY, -7, GETDATE())
  AND (N.InQty <> 0 OR N.OutQty <> 0)
  AND DATEDIFF(DAY, N.DocDate, N.CreateDate) > 3
ORDER BY DATEDIFF(DAY, N.DocDate, N.CreateDate) DESC
SAP HANA version
/* Stock movements entered more than 3 days after their posting date (last 7 days of entries) */
SELECT
    N."TransType", N."BASE_REF" AS "DocNum", N."ItemCode", N."Warehouse", N."InQty" - N."OutQty" AS "Qty",
    N."DocDate" AS "PostingDate", N."CreateDate" AS "EnteredOn",
    DAYS_BETWEEN(N."DocDate", N."CreateDate") AS "DaysLate", U."USER_CODE" AS "EnteredBy"
FROM "OINM" N
LEFT JOIN "OUSR" U ON U."USERID" = N."UserSign"
WHERE N."CreateDate" >= ADD_DAYS(CURRENT_DATE, -7)
  AND (N."InQty" <> 0 OR N."OutQty" <> 0)
  AND DAYS_BETWEEN(N."DocDate", N."CreateDate") > 3
ORDER BY DAYS_BETWEEN(N."DocDate", N."CreateDate") DESC
LIMIT 100

7. Goods receipts that always match the purchase order

If every receipt equals the PO quantity exactly, goods are probably being received by copying the order, not by counting.

Returns: Per user, the share of goods receipt lines (last 30 days) that exactly equal the PO line.
Suggested alert frequency: Monthly · On SAP's UK demo company: 100% for the demo's one user — expected for generated data

/* Goods receipts (last 30 days) where every line equals the open PO quantity, by user: counted or copied? */
SELECT
    U.USER_CODE AS ReceivedBy, COUNT(*) AS ReceiptLines,
    SUM(CASE WHEN G.Quantity = P.Quantity THEN 1 ELSE 0 END) AS ExactlyPOQty,
    CAST(100.0 * SUM(CASE WHEN G.Quantity = P.Quantity THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(5,1)) AS ExactPct
FROM OPDN H
JOIN PDN1 G ON G.DocEntry = H.DocEntry
JOIN POR1 P ON G.BaseType = 22 AND P.DocEntry = G.BaseEntry AND P.LineNum = G.BaseLine
LEFT JOIN OUSR U ON U.USERID = H.UserSign
WHERE H.DocDate >= DATEADD(DAY, -30, GETDATE())
GROUP BY U.USER_CODE
HAVING COUNT(*) >= 10
ORDER BY ExactPct DESC
SAP HANA version
/* Goods receipts (last 30 days) where every line equals the open PO quantity, by user: counted or copied? */
SELECT
    U."USER_CODE" AS "ReceivedBy", COUNT(*) AS "ReceiptLines",
    SUM(CASE WHEN G."Quantity" = P."Quantity" THEN 1 ELSE 0 END) AS "ExactlyPOQty",
    CAST(100.0 * SUM(CASE WHEN G."Quantity" = P."Quantity" THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(5,1)) AS "ExactPct"
FROM "OPDN" H
JOIN "PDN1" G ON G."DocEntry" = H."DocEntry"
JOIN "POR1" P ON G."BaseType" = 22 AND P."DocEntry" = G."BaseEntry" AND P."LineNum" = G."BaseLine"
LEFT JOIN "OUSR" U ON U."USERID" = H."UserSign"
WHERE H."DocDate" >= ADD_DAYS(CURRENT_DATE, -30)
GROUP BY U."USER_CODE"
HAVING COUNT(*) >= 10
ORDER BY 4 DESC

8. Stock not put away

Goods left in a receiving or system bin are in stock on screen but nowhere a picker will look.

Returns: Stock in receiving or system bins, with days since it arrived there. Needs bin locations (B1 9.0 or later).
Suggested alert frequency: Daily · On SAP's UK demo company: 6 lines

/* Stock still in receiving or system bins (not put away), with days since it arrived there */
SELECT
    Q.WhsCode, B.BinCode, Q.ItemCode, I.ItemName, Q.OnHandQty,
    DATEDIFF(DAY, A.LastIn, GETDATE()) AS DaysInBin
FROM OIBQ Q
JOIN OBIN B ON B.AbsEntry = Q.BinAbs
JOIN OITM I ON I.ItemCode = Q.ItemCode
LEFT JOIN (
    SELECT T.BinAbs, L.ItemCode, MAX(L.DocDate) AS LastIn
    FROM OBTL T JOIN OILM L ON L.MessageID = T.MessageID
    WHERE T.Quantity > 0
    GROUP BY T.BinAbs, L.ItemCode
) A ON A.BinAbs = Q.BinAbs AND A.ItemCode = Q.ItemCode
WHERE Q.OnHandQty > 0 AND (B.SysBin = 'Y' OR B.ReceiveBin = 'Y')
ORDER BY DaysInBin DESC
SAP HANA version
/* Stock still in receiving or system bins (not put away), with days since it arrived there */
SELECT
    Q."WhsCode", B."BinCode", Q."ItemCode", I."ItemName", Q."OnHandQty",
    DAYS_BETWEEN(A."LastIn", CURRENT_DATE) AS "DaysInBin"
FROM "OIBQ" Q
JOIN "OBIN" B ON B."AbsEntry" = Q."BinAbs"
JOIN "OITM" I ON I."ItemCode" = Q."ItemCode"
LEFT JOIN (
    SELECT T."BinAbs", L."ItemCode", MAX(L."DocDate") AS "LastIn"
    FROM "OBTL" T JOIN "OILM" L ON L."MessageID" = T."MessageID"
    WHERE T."Quantity" > 0
    GROUP BY T."BinAbs", L."ItemCode"
) A ON A."BinAbs" = Q."BinAbs" AND A."ItemCode" = Q."ItemCode"
WHERE Q."OnHandQty" > 0 AND (B."SysBin" = 'Y' OR B."ReceiveBin" = 'Y')
ORDER BY 6 DESC

9. Negative stock

Negative stock means something was issued before it was received — usually a posting-order or timing problem that distorts costs.

Returns: Item and warehouse lines with stock below zero.
Suggested alert frequency: Daily · On SAP's UK demo company: 0 lines

/* Negative stock right now */
SELECT W.ItemCode, I.ItemName, W.WhsCode, W.OnHand
FROM OITW W JOIN OITM I ON I.ItemCode = W.ItemCode
WHERE W.OnHand < 0
ORDER BY W.OnHand
SAP HANA version
/* Negative stock right now */
SELECT W."ItemCode", I."ItemName", W."WhsCode", W."OnHand"
FROM "OITW" W JOIN "OITM" I ON I."ItemCode" = W."ItemCode"
WHERE W."OnHand" < 0
ORDER BY W."OnHand"

10. Transfer requests left open

A transfer that was requested but never completed leaves one warehouse short and the other confused.

Returns: Inventory transfer request lines still open after 14 days.
Suggested alert frequency: Weekly · On SAP's UK demo company: 0 lines (the demo company has no transfer requests)

/* Inventory transfer requests still open after 14 days */
SELECT H.DocNum, H.DocDate, DATEDIFF(DAY, H.DocDate, GETDATE()) AS DaysOpen, H.Filler AS FromWhs, H.ToWhsCode AS ToWhs,
    L.ItemCode, L.OpenQty
FROM OWTQ H JOIN WTQ1 L ON L.DocEntry = H.DocEntry
WHERE H.DocStatus = 'O' AND L.LineStatus = 'O' AND H.DocDate < DATEADD(DAY, -14, GETDATE())
ORDER BY H.DocDate
SAP HANA version
/* Inventory transfer requests still open after 14 days */
SELECT H."DocNum", H."DocDate", DAYS_BETWEEN(H."DocDate", CURRENT_DATE) AS "DaysOpen", H."Filler" AS "FromWhs", H."ToWhsCode" AS "ToWhs",
    L."ItemCode", L."OpenQty"
FROM "OWTQ" H JOIN "WTQ1" L ON L."DocEntry" = H."DocEntry"
WHERE H."DocStatus" = 'O' AND L."LineStatus" = 'O' AND H."DocDate" < ADD_DAYS(CURRENT_DATE, -14)
ORDER BY H."DocDate"

11. Bin stock that hasn't moved in six months

Stock that sits untouched in a bin is a candidate for a recount, a move to overflow storage, or a dead-stock review.

Returns: Bin lines with no movement in or out for 180 days. Needs bin locations.
Suggested alert frequency: Monthly · On SAP's UK demo company: 6 lines

/* Bin stock that hasn't moved in or out of its bin for 180 days */
SELECT Q.WhsCode, B.BinCode, Q.ItemCode, I.ItemName, Q.OnHandQty, M.LastMove,
    DATEDIFF(DAY, M.LastMove, GETDATE()) AS DaysIdle
FROM OIBQ Q
JOIN OBIN B ON B.AbsEntry = Q.BinAbs
JOIN OITM I ON I.ItemCode = Q.ItemCode
LEFT JOIN (
    SELECT T.BinAbs, L.ItemCode, MAX(L.DocDate) AS LastMove
    FROM OBTL T JOIN OILM L ON L.MessageID = T.MessageID
    GROUP BY T.BinAbs, L.ItemCode
) M ON M.BinAbs = Q.BinAbs AND M.ItemCode = Q.ItemCode
WHERE Q.OnHandQty > 0 AND (M.LastMove IS NULL OR M.LastMove < DATEADD(DAY, -180, GETDATE()))
ORDER BY M.LastMove
SAP HANA version
/* Bin stock that hasn't moved in or out of its bin for 180 days */
SELECT Q."WhsCode", B."BinCode", Q."ItemCode", I."ItemName", Q."OnHandQty", M."LastMove",
    DAYS_BETWEEN(M."LastMove", CURRENT_DATE) AS "DaysIdle"
FROM "OIBQ" Q
JOIN "OBIN" B ON B."AbsEntry" = Q."BinAbs"
JOIN "OITM" I ON I."ItemCode" = Q."ItemCode"
LEFT JOIN (
    SELECT T."BinAbs", L."ItemCode", MAX(L."DocDate") AS "LastMove"
    FROM "OBTL" T JOIN "OILM" L ON L."MessageID" = T."MessageID"
    GROUP BY T."BinAbs", L."ItemCode"
) M ON M."BinAbs" = Q."BinAbs" AND M."ItemCode" = Q."ItemCode"
WHERE Q."OnHandQty" > 0 AND (M."LastMove" IS NULL OR M."LastMove" < ADD_DAYS(CURRENT_DATE, -180))
ORDER BY M."LastMove"

Notes

  • All queries only read data. They use standard B1 tables: OITW, OITM, OINM, OPDN/PDN1, POR1, IGN1/IGE1, OWTQ/WTQ1, and for bins OIBQ, OBIN, OBTL and OILM (B1 9.0 and later).
  • Manual corrections exclude receipts and issues linked to production orders; numeric transaction types 59 (goods receipt), 60 (goods issue) and 10000071 (inventory posting) are B1's own object codes.
  • The SQL Server versions were run against SAP's UK demo company. The HANA versions use the same tables and columns, translated to HANA syntax; they haven't been run on a HANA system.

Prefer not to touch SQL?

Inventory Doctor runs most of these checks from standard B1 exports — dead stock, gross vs net corrections, stockout risk after open orders, posting discipline and bin put-away — in your browser, with nothing uploaded. Click any item to see why its number is what it is.

See all of it in five minutes

Free inventory health check for SAP Business One. Runs in your browser — nothing is uploaded.

Run the free check