How-toOracle NetSuiteInventory / Lot and Serial Tracking

NetSuite Lot and Serial Traceability: Forward and Backward Trace

Question

how to trace lot numbers forward and backward in NetSuite

Also searched as

NetSuite lot traceability report recall
NetSuite serial number where used
NetSuite lot numbered assembly inventory detail
NetSuite find which customers received a lot

Short answer

NetSuite stores lot and serial numbers as inventory number records linked to transactions through the Inventory Detail subrecord. A recall trace works by starting from the inventory number, finding every transaction line that touched it, and following work order issues and assembly builds to the finished lots they went into. It is fully possible with saved searches or SuiteQL, though it takes setup.

Applies to: NetSuite with Lot Tracking or Serialized Inventory and Advanced Bin / Numbered Inventory Management as needed; lot numbered and serialized assemblies

How to run a lot trace

1Confirm items are flagged Lot Numbered or Serialized on the item record and that the Lot Tracking or Serialized Inventory feature is enabled.
2Find the lot: Lists > Accounting > Inventory Numbers (or the Inventory Number search) and open the record to see on-hand by location and the expiration date.
3Run a Transaction saved search, joined to Inventory Detail, filtered on the inventory number, to list every receipt, issue, build, transfer and sale for that lot.
4Add columns for transaction type, number, date, entity, item, location and quantity so the result reads as a movement history.
5For a backward trace of a finished lot, open the assembly build or work order completion that created it and list the component lots on the matching work order issue.
6For a forward trace of a component lot, find the work order issues that consumed it, then find the finished lots built under those work orders, then search sales for those finished lots.
7Export the customer list from the sales and shipment lines and compare against the recall scope.
8Save the searches and add the lot expiration date and inventory status as columns so the same report works for the next incident.

Where the data lives

Each lot or serial is an Inventory Number record, keyed by item, number and sometimes location. Quantity movement is stored on the Inventory Detail subrecord of each transaction line, which holds the assigned number, quantity and bin. Item receipts, item fulfillments, inventory adjustments, transfers, work order issues and completions, and assembly builds all carry inventory detail.

That means a lot has no single history table. The history is the set of transaction lines whose inventory detail references the number. Searching on the number through the Inventory Detail join is therefore the correct way to rebuild it, and it is also why a transaction posted without an assignment leaves a gap in the trace.

-- Transaction saved search (Lists > Search > Saved Searches > New)
-- Type: Transaction
-- Criteria: Inventory Detail : Inventory Number : Number  contains  LOT-2026-0412
-- Results: Type, Document Number, Date, Name, Item, Location,
--          Inventory Detail : Inventory Number, Inventory Detail : Quantity

Backward and forward trace through production

The trace breaks at the manufacturing step unless you follow the work order. A finished lot is created by a work order completion or assembly build. The components it consumed are on the work order issue or the build's component list, each with its own lot. Moving from finished lot to component lot means joining on the created-from work order.

Do it in two searches rather than one clever one. First list the work orders that produced the finished lot. Second, list the issue lines on those work orders and their lots. For a forward trace reverse the order: component lot, to work orders that issued it, to finished lots, to sales. Writing the two legs separately makes the logic checkable.

Inventory status, expiry and quarantine

With the Inventory Status feature, each lot quantity carries a status such as available or on hold, and the status determines whether stock can be sold. During a recall, moving remaining quantity to a non-available status stops further shipments while you investigate. That is cleaner than marking items inactive, which affects every lot.

Expiration dates live on the inventory number, so a saved search on inventory number with an expiry filter gives a near-expiry list for the warehouse. Pair it with location and bin to give pickers something usable.

-- SuiteQL: lots expiring in 60 days (verify column names in the Records Catalog)
SELECT i.itemid, n.inventorynumber, n.expirationdate
FROM inventorynumber n
JOIN item i ON i.id = n.item
WHERE n.expirationdate <= CURRENT_DATE + 60
ORDER BY n.expirationdate

Making the trace reliable

A trace is only as good as receiving discipline. If receipts are posted with a generic lot like TEMP, or work orders are completed without assigning component lots, the chain is broken before anyone needs it. Use mandatory inventory detail, validate lot formats with a user event script, and prefer vendor lot capture at the dock.

Run a mock recall each quarter. Pick a finished lot, time how long it takes to list affected customers, and note where the data was missing. That is the cheapest audit you can do.

Try it here, free

Food Lot Traceability SQL Query Generator (Forward and Backward)

Open full tool

Fill in your table and column names to get recursive SQL that traces a lot back to suppliers or forward to customers.

Fill in

Lot number to trace

Genealogy table

Table with one row per consumption of a lot into another lot

Input lot column

The ingredient or component lot that was consumed

Output lot column

The lot produced from it

Quantity column

Shipment table

Shipped lot column

Customer column

Ship date column

Maximum depth

Generated code

Free, optional. Reopen it on any device.

Download:

Backward trace: which ingredient lots went into this lot

WITH RECURSIVE back_trace AS (
  SELECT input_lot AS lot_no, output_lot AS used_in, qty_consumed AS qty, 1 AS depth
  FROM lot_genealogy
  WHERE output_lot = 'FG-240918-03'
  UNION ALL
  SELECT g.input_lot, g.output_lot, g.qty_consumed, b.depth + 1
  FROM lot_genealogy g
  JOIN back_trace b ON g.output_lot = b.lot_no
  WHERE b.depth < 10
)
SELECT lot_no, used_in, qty, depth
FROM back_trace
ORDER BY depth, lot_no;

Forward trace: where did this lot go

WITH RECURSIVE fwd_trace AS (
  SELECT output_lot AS lot_no, 1 AS depth
  FROM lot_genealogy
  WHERE input_lot = 'FG-240918-03'
  UNION ALL
  SELECT g.output_lot, f.depth + 1
  FROM lot_genealogy g
  JOIN fwd_trace f ON g.input_lot = f.lot_no
  WHERE f.depth < 10
)
SELECT DISTINCT s.customer_id, s.lot_no, s.ship_date
FROM shipment_lines s
WHERE s.lot_no = 'FG-240918-03'
   OR s.lot_no IN (SELECT lot_no FROM fwd_trace)
ORDER BY s.ship_date, s.customer_id;

Mock recall timing record

-- Start the clock when you run the queries, stop when the customer list is verified.
-- Record: lot traced = FG-240918-03, query run time, rows returned, reconciliation of produced vs shipped vs on hand.

Table and column names above are generic placeholders, not the schema of any specific ERP. Map them to your own genealogy and shipping tables. The WITH RECURSIVE form works in PostgreSQL, MySQL 8, SQLite and others; SQL Server uses WITH without the RECURSIVE keyword and Oracle accepts it from 11gR2. Test against a copy of the data and watch for circular lot references, which the depth limit guards against.

Run a trace against your ERP data

Optional session to map this query to your lot tables and test it on a copy of your data.

Optional. The tool and every export work without this. No spam.

Common pitfalls

!Receiving with placeholder lot numbers, which breaks the backward trace to vendor lots.
!Completing work orders without component lot assignments, so the production link is missing.
!Using one saved search for both directions, which hides logic errors.
!Marking items inactive during a recall instead of putting the affected lot on hold.
!Not testing the trace until a real incident occurs.

How an ERP-grounded AI assistant handles this

An assistant grounded in NetSuite can take a lot number, run the inventory-detail queries and assemble the forward and backward chain, then show the searches so a quality manager can check them. ERPray stays read-only and respects the user's role, so it only sees what that person could search. For a single regular recall report, a well-built saved search needs no AI at all.

See ERPray

Frequently asked questions

Can NetSuite do a full forward and backward lot trace?

Yes, when lots are captured at receipt, on work order issues and on completions. The trace is built from Inventory Detail on transactions, joined through work orders. It needs saved searches or SuiteQL rather than a single built-in report in most accounts.

Where do I see all transactions for one serial number?

Open the Inventory Number record or run a transaction search with the Inventory Detail join filtered on the serial. Receipts, sales, transfers, adjustments, builds and returns that touched it will all appear, provided they carried inventory detail.

How do I block a lot from shipping during a recall?

With Inventory Status enabled, change the status of the affected lot quantity to a non-available status using an Inventory Status Change. Available stock excludes it, so it cannot be sold or fulfilled until released.

Does a lot numbered assembly keep component lots?

The finished assembly gets its own lot, and component lots are recorded on the build or work order issue. The link between them is the work order, so queries must join through it to connect finished lots to component lots.

How-to

NetSuite Quality Management: Incoming Inspection and Quarantine

Core NetSuite does not ship a full quality management system with inspection plans, sampling rules and certificates. You build incoming inspection from Inventory Status, a quarantine status or location, a workflow and a custom record for inspection results and nonconformances, or you add a quality SuiteApp. Which route is right depends on how formal your audit requirements are.

How-to

How to Set Up Routings and WIP in NetSuite Manufacturing

In NetSuite Advanced Manufacturing, a routing defines the ordered operations, work centers and labor or machine resources an assembly passes through. When a work order with a routing is released, NetSuite creates manufacturing operation tasks, and costs move into WIP through work order issues and operation completions before the final work order completion relieves WIP into finished inventory.

How-to

How to write and run SuiteQL queries in NetSuite

SuiteQL is NetSuite's read-only SQL dialect over the underlying record tables, run either interactively from Analytics > SuiteQL Query Tool (or the older /app/suiteanalytics query page), through REST at /services/rest/query/v1/suiteql, or programmatically via the N/query module in SuiteScript 2.x. It supports standard SELECT, JOIN, WHERE, GROUP BY and window functions against table names that mostly match record type IDs (transaction, transactionline, item, customer).

How-to

How to use formula fields in a NetSuite saved search

In the saved search Results tab, add a column, set Field to "Formula (Text)", "Formula (Numeric)", "Formula (Date)" or "Formula (Currency)", then type an Oracle SQL expression into the Formula box using curly braces around field IDs, e.g. {trandate} or {item.custitem_weight}. Formula fields can also go on the Criteria tab so you can filter on the calculated value itself.

Error fix

Fix NetSuite SSS_USAGE_LIMIT_EXCEEDED Error

SSS_USAGE_LIMIT_EXCEEDED fires when a SuiteScript execution consumes all the governance units (usage points) allotted to its script type before it finishes. Fix it by checking runtime.getCurrentScript().getRemainingUsage() before expensive calls, yielding or rescheduling in Scheduled scripts, and moving heavy record-count work into Map/Reduce, which yields automatically across stages.

Error fix

Fix NetSuite INVALID_FLD_VALUE Error

INVALID_FLD_VALUE means NetSuite rejected a value you tried to set on a field because it does not match the field's expected type, list option, or reference record. Fix it by confirming the internal ID or text value actually exists on that field's source list and matches the field's value type (text versus list versus record reference) before setting it.

AI for ERP

AI agents for NetSuite manufacturing operations

AI agents for NetSuite manufacturing: WIP tracking, routing exceptions, and work order status grounded in SuiteQL, with human approval on anything that writes back.

AI for ERP

NetSuite AI for Medical Device Manufacturers: Traceability, DHR Context and QMSR Readiness

AI on NetSuite for medical device makers: UDI, lot and serial trace, device history record questions and CAPA context, run privately, read-only by default.

Stuck on Oracle NetSuite?

Talk to engineers who work inside Oracle NetSuite every week, and who build private AI that answers these questions from your own ERP data.

Talk to an ERP engineer Browse ERP Help

All ERP Help articles

ArchRay builder

Design the on-prem AI stack for NetSuite: model, GPUs, RAG pipeline, guardrails

See memory, speed and cost as you swap parts, then export the diagram. Free, in your browser.

Open the builder

New to NetSuite? Start with the NetSuite guide and toolkit, or plan with NetSuite Cost Calculator, Training Path Builder, ERP Certification Study Planner.

Keep exploring

Free tools

ERP fixes and how-tos

Runs with your ERP

Guides

AI on your ERP

Integrations

Comparisons

Infor SyteLine vs Oracle NetSuite for Discrete ManufacturersInfor SyteLine vs Oracle NetSuite: compare shop floor depth, cloud architecture, cost model and customization limits for discrete manufacturers.

Software

Oracle NetSuiteOracle NetSuite explained for manufacturers: modules, SuiteScript, SuiteTalk and SuiteQL, cloud deployment, limits, alternatives and where AI helps.

TermsProduct Genealogy Traceability NetSuite Advanced Manufacturing NetSuite Saved Search NetSuite SuiteFlow

NetSuite Lot and Serial Traceability: Forward and Backward Trace

How to run a lot trace

Where the data lives

Backward and forward trace through production

Inventory status, expiry and quarantine

Making the trace reliable

Fill in

Backward trace: which ingredient lots went into this lot

Forward trace: where did this lot go

Mock recall timing record

Run a trace against your ERP data

Common pitfalls

How an ERP-grounded AI assistant handles this

Frequently asked questions

Related

NetSuite Quality Management: Incoming Inspection and Quarantine

How to Set Up Routings and WIP in NetSuite Manufacturing

How to write and run SuiteQL queries in NetSuite

How to use formula fields in a NetSuite saved search

Fix NetSuite SSS_USAGE_LIMIT_EXCEEDED Error

Fix NetSuite INVALID_FLD_VALUE Error

AI agents for NetSuite manufacturing operations

NetSuite AI for Medical Device Manufacturers: Traceability, DHR Context and QMSR Readiness

Stuck on Oracle NetSuite?

Keep exploring

Free tools

ERP fixes and how-tos

Runs with your ERP

Guides

AI on your ERP

Integrations

Comparisons

Software

Fill in

Backward trace: which ingredient lots went into this lot

Forward trace: where did this lot go

Mock recall timing record

Run a trace against your ERP data