Hi all,
This is a known issue with the Stock Ledger Report for serial/lot-numbered items, and I'm hoping someone here has landed on an accurate, reliable workaround.
The issue (quick recap)
When you run the Stock Ledger Report for a historical date:
- The Ending Inventory Qty on Hand for that date is correct.
- But the Item: Serial/Lot Number column shows the current per-lot on-hand ("as of today") — it ignores the report's date/location filters.
- So the sum of per-lot quantities does not reconcile to the historical Ending Qty on Hand.
The result: correct total for a past date, but no trustworthy per-lot breakdown for that same date.
Why it's hard to reconstruct
From what I can tell, NetSuite only stores a current running per-lot balance (inventorynumberlocation.quantityonhand) — not dated per-lot history. On top of that, in our environment inbound for lot items doesn't generate a dated lot movement (we have a 3PL/middleware integration that appears to write balances directly), while outbound (Item Fulfillments) is dated per lot. So there's no forward source to rebuild historical lot composition — you can only work backward from the current balance.
What I'm asking
Since this is a known limitation with no native fix, has anyone found a solution/workaround that produces historically accurate per-lot balances that reconcile to the Ending Qty on Hand? Specifically:
- Has anyone built a SuiteQL / saved search / SuiteAnalytics workbook approach that reconciles exactly for a chosen historical date?
- If you roll backward from the current balance over post-date movements — which outbound stream did you use (Item Fulfillment vs Invoice), and did it reconcile to the unit? Any double-counting gotchas?
- Anyone gone the nightly lot-balance snapshot route (custom record + scheduled Map/Reduce) instead? How's it held up, and how do you handle dates before the snapshot went live?
Really keen to hear what's actually worked in production. Thanks!