Your inventory accuracy report is lying: gross vs net variance in SAP Business One

Gross vs net, in one example

A count finds item A is £500 over what the system says, and item B is £480 short. Both get corrected.

• Net variance: +£500 − £480 = +£20. "Basically spot on."
• Gross variance: £500 + £480 = £980 of stock that wasn't where the system said it was.

Both corrections are real problems. The shortage might be loss, a mis-pick or an unrecorded issue; the overage might be a receipt booked to the wrong item or a return that was never recorded. Netting them together doesn't make either go away — it just hides them. In practitioner discussions about inventory accuracy, this is one of the most common complaints: management reporting a small net figure while thousands of items were individually wrong.

What it looks like in SAP's own demo company

We ran the query below on SAP's UK demo database (OEC Computers, SAP Business One 10.0) for the 12 months to its last posting date. It holds about £1.9M of stock.

• 85 manual stock corrections
• £332,361 corrected up and £309,223 corrected down
• Gross: £641,584 — Net: +£23,137

A net report would show a £23k adjustment on £1.9M of stock. The gross figure is almost 28 times larger: the net view hides 96% of the movement. And it isn't evenly spread. One item, a 2 TB portable hard disk, had 250 units written up and 250 written down — £35,000 of corrections with a net effect of exactly zero. Four PC items were each corrected four times in the year.

(The demo company's data is generated by SAP, so the exact amounts aren't typical of a real business — but the pattern, large gross and small net, is exactly what people describe in real warehouses.)

What counts as a stock correction in SAP Business One

Normal movements — purchase receipts, deliveries, invoices, transfers, production — are not corrections. These are:

• Inventory postings from inventory counting (B1 object 10000071).
• Goods receipts (59) and goods issues (60) that aren't linked to a production order — the manual "add some, remove some" documents.

Every one of them is recorded in OINM, the inventory audit journal, with its value. So gross vs net is a single query.

The query: total gross vs net

SQL Server version

/* Manual stock corrections in the last 12 months: gross vs net */
SELECT
    COUNT(*) AS Corrections,
    SUM(CASE WHEN N.TransValue > 0 THEN N.TransValue ELSE 0 END) AS CorrectedUp,
    SUM(CASE WHEN N.TransValue < 0 THEN -N.TransValue ELSE 0 END) AS CorrectedDown,
    SUM(ABS(N.TransValue)) AS GrossValue,
    SUM(N.TransValue) AS NetValue
FROM OINM N
WHERE N.DocDate >= DATEADD(MONTH, -12, 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)))

/* Manual stock corrections in the last 12 months: gross vs net */
SELECT
    COUNT(*) AS "Corrections",
    SUM(CASE WHEN N."TransValue" > 0 THEN N."TransValue" ELSE 0 END) AS "CorrectedUp",
    SUM(CASE WHEN N."TransValue" < 0 THEN -N."TransValue" ELSE 0 END) AS "CorrectedDown",
    SUM(ABS(N."TransValue")) AS "GrossValue",
    SUM(N."TransValue") AS "NetValue"
FROM "OINM" N
WHERE N."DocDate" >= ADD_MONTHS(CURRENT_DATE, -12)
  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)))

The query: which items drive it

SQL Server version

/* Which items drive it: gross vs net per item and warehouse, last 12 months */
SELECT TOP 50
    N.ItemCode, I.ItemName, 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
JOIN OITM I ON I.ItemCode = N.ItemCode
WHERE N.DocDate >= DATEADD(MONTH, -12, 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, I.ItemName, N.Warehouse
ORDER BY SUM(ABS(N.TransValue)) DESC

/* Which items drive it: gross vs net per item and warehouse, last 12 months */
SELECT
    N."ItemCode", I."ItemName", 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
JOIN "OITM" I ON I."ItemCode" = N."ItemCode"
WHERE N."DocDate" >= ADD_MONTHS(CURRENT_DATE, -12)
  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", I."ItemName", N."Warehouse"
ORDER BY SUM(ABS(N."TransValue")) DESC
LIMIT 50

Both queries only read data. Values are the posting values B1 recorded at the time of each correction. Change -12 to look at a different period, and try them in a test company first.

How to read the result

• Gross ÷ net. If gross is many times net, overages and shortages are cancelling out. That's the number to put in front of management, not the net.
• Gross as a share of inventory value. Track it every month or quarter. It should fall as processes improve; net can look fine while gross stays high.
• Items with matching up-and-down corrections. 250 up and 250 down, like the hard disk above, usually means stock in the wrong place, booked to the wrong item or the wrong unit of measure — not stock that's actually gone.
• Items corrected three or more times. Repeated corrections point to a process gap: receiving without a bin, picking without posting, or a transfer that's never confirmed.

What to do about it

1. Report gross alongside net. Keep net for the financial write-off; use gross to judge accuracy.
2. Keep an exception log ranked by value. Fix the ten items with the biggest gross corrections first — not the ones with the biggest net.
3. Find the cause, not the count. For each repeated item, check receiving, put-away, picking and transfers. Most "missing" stock turns out to be misplaced or mis-booked.
4. Set a tolerance. No count is perfect. Agree what variance is acceptable per item or per value band, and investigate beyond it.
5. Get warned automatically. The SAP B1 alert pack includes weekly queries for gross vs net corrections and for repeatedly corrected items.

Skip the SQL

Inventory Doctor shows gross vs net corrections from the standard Inventory Posting List export — corrected up, down, gross, net, how much the net view hides, and the items corrected three or more times — alongside dead stock and stockout risk. It values corrections at today's average cost, so its totals differ from the posting values above, but the pattern is the same. It's free, and your data never leaves your browser.

FAQ

Is net inventory variance ever the right number?

Yes — for the financial write-off, because that's what hits the P&L. It's the wrong number for judging how accurate your stock records are.

Should goods issues for internal use count as corrections?

The query counts every manual goods issue that isn't for production. If you use goods issues for samples or internal consumption, exclude those with a filter on the document's remarks, reference or a user-defined field.

Does it work on SAP HANA?

Yes — use the HANA versions above. They use the same tables and columns, with HANA's date functions.