# Food Lot Traceability SQL Generator: One Step Forward, One Step Back

> Source: https://netray.co/tools/food-lot-traceability-sql-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

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

## Category

developer

## Industry

Food and Beverage Manufacturing

## Audience

- ERP developer
- Food safety manager
- Data analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Lot number to trace

**Key:** start_lot

**Type:** text

**Default:** FG-240918-03

#### Genealogy table

**Key:** genealogy_table

**Type:** text

**Default:** lot_genealogy

**Help:** Table with one row per consumption of a lot into another lot

#### Input lot column

**Key:** parent_col

**Type:** text

**Default:** input_lot

**Help:** The ingredient or component lot that was consumed

#### Output lot column

**Key:** child_col

**Type:** text

**Default:** output_lot

**Help:** The lot produced from it

#### Quantity column

**Key:** qty_col

**Type:** text

**Default:** qty_consumed

#### Shipment table

**Key:** ship_table

**Type:** text

**Default:** shipment_lines

#### Shipped lot column

**Key:** ship_lot_col

**Type:** text

**Default:** lot_no

#### Customer column

**Key:** customer_col

**Type:** text

**Default:** customer_id

#### Ship date column

**Key:** ship_date_col

**Type:** text

**Default:** ship_date

#### Maximum depth

**Key:** max_depth

**Type:** number

**Default:** 10

**Blocks:**

#### Backward trace: which ingredient lots went into this lot

**Body:** WITH RECURSIVE back_trace AS (
  SELECT {{parent_col}} AS lot_no, {{child_col}} AS used_in, {{qty_col}} AS qty, 1 AS depth
  FROM {{genealogy_table}}
  WHERE {{child_col}} = '{{start_lot}}'
  UNION ALL
  SELECT g.{{parent_col}}, g.{{child_col}}, g.{{qty_col}}, b.depth + 1
  FROM {{genealogy_table}} g
  JOIN back_trace b ON g.{{child_col}} = b.lot_no
  WHERE b.depth < {{max_depth}}
)
SELECT lot_no, used_in, qty, depth
FROM back_trace
ORDER BY depth, lot_no;

#### Forward trace: where did this lot go

**Body:** WITH RECURSIVE fwd_trace AS (
  SELECT {{child_col}} AS lot_no, 1 AS depth
  FROM {{genealogy_table}}
  WHERE {{parent_col}} = '{{start_lot}}'
  UNION ALL
  SELECT g.{{child_col}}, f.depth + 1
  FROM {{genealogy_table}} g
  JOIN fwd_trace f ON g.{{parent_col}} = f.lot_no
  WHERE f.depth < {{max_depth}}
)
SELECT DISTINCT s.{{customer_col}}, s.{{ship_lot_col}}, s.{{ship_date_col}}
FROM {{ship_table}} s
WHERE s.{{ship_lot_col}} = '{{start_lot}}'
   OR s.{{ship_lot_col}} IN (SELECT lot_no FROM fwd_trace)
ORDER BY s.{{ship_date_col}}, s.{{customer_col}};

#### Mock recall timing record

**Body:** -- Start the clock when you run the queries, stop when the customer list is verified.
-- Record: lot traced = {{start_lot}}, query run time, rows returned, reconciliation of produced vs shipped vs on hand.

**Notes:** 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.

## Content

**Intro:** A mock recall is only as fast as your lot genealogy query. This generator produces forward and backward trace SQL for a genealogy table that records which input lots were consumed into which output lots.

**Sections:**

#### How to use the generator

**Body:** Enter the lot you want to trace and map the field names to your own tables. Copy the backward query to find every ingredient and packaging lot that went into a finished lot. Copy the forward query to find every downstream lot and every customer shipment that received it. Run both on a copy of production data, review the row counts, and save the output as evidence for your mock recall record.

**Bullets:**

- Trace in both directions for every mock recall
- Keep the depth limit as a guard against circular records
- Reconcile produced, shipped, on hand and scrapped quantities

#### Why recursion is needed

**Body:** A finished lot may consume a blend lot, which consumed a premix lot, which consumed a raw material lot. A single join finds only one level. A recursive common table expression walks the genealogy until no new lots appear, which is how you catch rework loops, carryover blends and multi-stage production.

#### Fit with FSMA 204 records

**Body:** Food Traceability Rule records tie key data elements to critical tracking events such as receiving, transformation and shipping for foods on the Food Traceability List. The query here uses the genealogy link you already keep. Make sure your tables also store the traceability lot code, location and date for each event, and confirm which events and dates apply to you against the current FDA rule and its compliance date.

#### Common mistakes

**Body:** Tracing only forward, missing rework that re-enters a lot, joining on lot number alone when lot numbers repeat across items or plants, and forgetting packaging and processing aid lots. In an ERP lot numbers often are unique only within an item, so add the item key to joins if your schema needs it.

**Cta:** Netray can map this logic to your real lot tables in SyteLine, NetSuite, M3 or LN and run it read-only. The generator itself is free.

**Faq:**

#### Does this work in every ERP?

**Answer:** It works on any relational schema once you map the table and column names. Different ERPs store lot genealogy in different tables, so treat the names as placeholders and verify with your data dictionary.

#### What is a recursive CTE?

**Answer:** A query that references itself to walk a hierarchy level by level. For lot trace it follows each lot to the lots it fed or was made from until nothing new is found.

#### Why is there a depth limit?

**Answer:** It stops runaway loops if bad data creates a circular reference, for example a lot recorded as consuming itself through rework. Raise it if your process truly has more stages.

#### Is this enough to meet FSMA 204?

**Answer:** No. It supports the trace step. The rule also requires specific key data elements per tracking event and the ability to provide an electronic sortable spreadsheet on request, so review the current rule text.

## Search terms

lot traceability sql, recursive cte lot genealogy, forward and backward trace query, mock recall sql query, food traceability database query, lot genealogy sql, FSMA 204 traceability query, ERP lot trace report

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json