NetSuite Lot Aging Saved Search: Track Stuck Inventory

When lot-tracked items sit on your shelf for 30, 60, or 90 days, the standard Inventory Aging report tells you the item has stock but not which specific lot is aging. The 21 widgets on your search may include a fresh PO receipt from last week mixed in with a lot that has been there since March. From an audit perspective, this is also where inventory valuation and slow-moving stock reserves get messy, because you cannot tie the aging bucket to the actual cost layer.

The fix is a transaction-based saved search built on the Transaction record, filtered to receipts, and grouped by lot number. Here is how to build it.

Why the Standard Inventory Aging Search Falls Short

The built-in Inventory Aging report (Reports > Inventory/Items > Inventory Aging) tracks item-level balances. It sums receipts against fulfillments at the item/location level. For non-lot items, that is fine. For lot-tracked items, the system cannot tell you which physical lot a fulfillment drew from, because NetSuite does not perform lot-level cost layering in the same way it tracks items.

A lot received 80 days ago may already appear consumed in the aging summary if other receipts of the same item have come in and gone out. The report shows net movement, not lot-by-lot age. The NetSuite Basics Guide walks through how NetSuite's standard reports aggregate inventory at the item level, which is the same aggregation that hides lot detail here.

The lot field is the join key. Without filtering and grouping on the lot number itself, you are looking at item aggregates that hide the aging you actually need.

Building the Lot Aging Saved Search

Go to Lists > Search > Saved Searches > New and select Transaction as the record type. Naming convention matters for governance: use something like "Lot Aging > 30 Days – [Subsidiary]" so reviewers know the cut-off at a glance.

Step 1: Criteria Tab

Set the following filters:

• Type is any of: Item Receipt, Inventory Adjustment (positive quantities only if you want to ignore write-offs)
• Item is Lot Numbered is true
• Transaction Date is before or on today - 30 days (use a formula or a relative date filter)
• Quantity is greater than 0
• Status is not Pending Approval, Pending Receipt, or Pending Billing

For the 30-day filter specifically, add a formula-based criteria:

Formula (Numeric): TRUNC({today}) - TRUNC({trandate})
Value: greater than 30

This computes the days since the receipt posted. You can stack additional criteria for 60, 90, and 120+ buckets later.

Step 2: Results Tab

Pull these columns in this order:

Quantity Available is the field that actually tells you whether a lot is truly stuck. A lot with high Quantity but low Quantity Available is committed, not aging.

Step 3: Group and Summarize

Click Group on the Lot Number column. This is the key step most teams miss. Without grouping on the lot, the search repeats every receipt line and dilutes the picture.

Add a Summary for Quantity (sum) and Quantity Available (sum) so each row represents a single lot at a single location. If you also want a warehouse-by-warehouse pivot for the close package, add Location as a secondary group.

Adding Aging Buckets with a Formula

For a board-ready aging view, add a Formula (Text) column on the Results tab:

CASE 
  WHEN TRUNC({today}) - TRUNC({trandate}) <= 30 THEN '0-30 Days'
  WHEN TRUNC({today}) - TRUNC({trandate}) <= 60 THEN '31-60 Days'
  WHEN TRUNC({today}) - TRUNC({trandate}) <= 90 THEN '61-90 Days'
  WHEN TRUNC({today}) - TRUNC({trandate}) <= 180 THEN '91-180 Days'
  ELSE 'Over 180 Days'
END

Then add this formula to the Group By section if you want a bucket-level pivot. Combined with a Location group, you get a clean warehouse-by-warehouse aging summary that controllers can paste into the close package.

Reconciling Receipts Against Fulfillments at the Lot Level

The technique for cross-referencing receipt data with fulfillment data using the lot field is a Joined Search:

1. On the Criteria tab, click the Transaction join dropdown and add Inventory Number. The Inventory Number record has internal ID inventorynumber and is the canonical join key for any serial or lot number on a transaction line.
2. Add a second join on the same record pointing to the fulfillment side, filtered to Item Fulfillment or Invoice line transactions.
3. Group by Lot Number across both joins.
4. Use a Summary (Minimum) on the Receipt Date and a Summary (Maximum) on the Fulfillment Date to show oldest receipt still open and most recent consumption.

SELECT T.tranid,
       T.trandate,
       I.inventorynumber,
       TL.quantity,
       TL.quantitycommitted,
       TL.quantityavailable
FROM   transaction T
JOIN   transactionline TL ON TL.transaction = T.id
JOIN   item I ON I.id = TL.item
WHERE  T.type = 'ItemRcpt'
  AND  I.istaxlot = 'T'
  AND  TL.quantityavailable > 0
  AND  T.trandate <= TRUNC(SYSDATE) - 30
ORDER  BY T.trandate ASC

This is a SuiteQL view of the same logic if you want to push it into a SuiteAnalytics workbook instead. The same fields apply: quantityavailable is the lot's still-on-hand balance, and the date filter is where the 30-day cut-off lives.

Edge Cases That Will Trip You Up

• Inventory Adjustments: A write-off zeroes the lot out but the original receipt line still shows in the search. Add a filter excluding transactions where Quantity Available = 0 if you only want true stuck inventory.
• Multi-Subsidiary: If you have intercompany lot transfers, the same lot number may appear under two subsidiaries. Filter on Subsidiary first, then Location.
• Negative Adjustments: Some companies issue negative receipts for returns-to-vendor. These can show as negative quantities that mask aging. Exclude them or filter Quantity > 0.
• Period Lock Dates: Even with the right criteria, the search still respects the posting period for the receipt. A locked period does not hide the transaction but it does affect the GL aging if you join to the ledger.

A Note on Governance

Saved searches pulling every Item Receipt across the past year with multiple joins can hit the 1,000-row preview limit and the 5,000-row export limit quickly. If the search times out, restrict the date range (last 365 days max), add a Subsidiary filter, or use the SuiteAnalytics dataset version of the same query. The dataset version is the right answer once you are publishing this as a recurring month-end deliverable to the controller team.

Once the search is stable, schedule it under Reports > Saved Searches > [your search] > Schedule, deliver it as a CSV to the inventory analyst email distribution list, and add it to the period-end close checklist. The control here is a documented aging report reviewed each month, which gives your auditors a clean tie-out between lot receipts, lot consumption, and the slow-moving reserve on the balance sheet. The Dashboards Guide covers how to surface this kind of recurring search as a portlet on a financial dashboard so controllers see it without logging into the saved search itself.