# Device Lot Genealogy and Traceability SQL Generator

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

## Short answer

Build recursive lot genealogy queries for backward trace (finished device to components) and forward trace (component lot to shipped devices) using your own table and column names.

## Category

developer

## Industry

Medical Devices

## Audience

- ERP developer
- Quality engineer
- Regulatory affairs

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Genealogy table

**Key:** genealogy_table

**Type:** text

**Default:** lot_genealogy

**Help:** Table linking a parent lot to a consumed child lot

#### Parent lot column

**Key:** parent_col

**Type:** text

**Default:** parent_lot

#### Child (component) lot column

**Key:** child_col

**Type:** text

**Default:** child_lot

#### Shipment table

**Key:** shipment_table

**Type:** text

**Default:** shipment_lines

#### Shipment lot column

**Key:** ship_lot_col

**Type:** text

**Default:** lot_number

#### Customer column

**Key:** customer_col

**Type:** text

**Default:** customer_id

#### Ship date column

**Key:** ship_date_col

**Type:** text

**Default:** ship_date

#### Lot to trace

**Key:** start_lot

**Type:** text

**Default:** LOT24A017

**Blocks:**

#### Backward trace: finished device lot to component lots

**Body:** WITH RECURSIVE backward AS (
  SELECT {{parent_col}} AS parent_lot, {{child_col}} AS child_lot, 1 AS depth
  FROM {{genealogy_table}}
  WHERE {{parent_col}} = '{{start_lot}}'
  UNION ALL
  SELECT g.{{parent_col}}, g.{{child_col}}, b.depth + 1
  FROM {{genealogy_table}} g
  JOIN backward b ON g.{{parent_col}} = b.child_lot
  WHERE b.depth < 10
)
SELECT depth, parent_lot, child_lot
FROM backward
ORDER BY depth, parent_lot, child_lot;

#### Forward trace: component lot to shipped devices and customers

**Body:** WITH RECURSIVE forward AS (
  SELECT {{parent_col}} AS parent_lot, {{child_col}} AS child_lot, 1 AS depth
  FROM {{genealogy_table}}
  WHERE {{child_col}} = '{{start_lot}}'
  UNION ALL
  SELECT g.{{parent_col}}, g.{{child_col}}, f.depth + 1
  FROM {{genealogy_table}} g
  JOIN forward f ON g.{{child_col}} = f.parent_lot
  WHERE f.depth < 10
)
SELECT DISTINCT s.{{ship_lot_col}} AS shipped_lot,
       s.{{customer_col}} AS customer,
       s.{{ship_date_col}} AS ship_date
FROM {{shipment_table}} s
WHERE s.{{ship_lot_col}} IN (SELECT parent_lot FROM forward)
   OR s.{{ship_lot_col}} = '{{start_lot}}'
ORDER BY s.{{ship_date_col}};

**Notes:** Table and column names are placeholders: replace them with your ERP or MES names. Syntax uses a recursive common table expression as supported by PostgreSQL, MySQL 8 and SQLite. SQL Server omits the RECURSIVE keyword, and Oracle supports recursive subquery factoring without it as well. Test on a copy, confirm that your genealogy table captures every consumption, and keep the exported result with the recall or complaint record.

## Content

**Intro:** When a component lot is suspect, quality needs to know every finished device lot that used it and every customer who received them. This generator creates the backward and forward trace queries against whatever genealogy and shipment tables your system uses.

**Sections:**

#### How to use it

**Body:** Enter the names of your genealogy table (parent lot to consumed child lot), the shipment table and the key columns, then the lot to trace. Copy the backward query to see which component lots went into a finished lot, or the forward query to list shipments that contain a suspect component lot. Run both in a read-only session and save the output with the investigation record.

**Bullets:**

- Backward trace answers what went into this lot
- Forward trace answers who received product with this component
- Depth limit prevents runaway recursion on bad data

#### How the query works

**Body:** A recursive common table expression starts with the immediate links of the starting lot and repeatedly joins the genealogy table to climb or descend one level at a time. The depth column keeps the output ordered by level and stops the recursion after ten levels. The forward query ends with a join to shipments, so the final output is a customer and date list suitable for a recall scope assessment.

#### Worked example

**Body:** Suppose a sterile pouch lot is found to have a seal defect. A forward trace from the pouch lot returns every finished lot packed in it, then every shipment line holding those lots, with customer and date. If one finished lot also used a second suspect component, the backward trace from that lot shows both. Compare the totals with units on hand so the field action scope is based on data rather than estimates.

#### Common mistakes

**Body:** Genealogy is only as good as the transaction capture. If operators consume material without scanning, links are missing. Other problems are mixing unit-of-measure quantities, ignoring rework and relabel operations that create new lots, and not testing the trace regularly. Run a mock recall periodically and measure how long it takes to reach a complete list.

**Cta:** If genealogy data is spread across ERP, MES and spreadsheets, Netray can discuss a read-only way to run these traces across them.

**Faq:**

#### Will this work with my ERP table names?

**Answer:** Yes, as long as your system has a table linking consumed lots to produced lots. Enter your own table and column names. Some systems store genealogy in several tables, in which case you need a view that unions them first.

#### Why limit recursion depth?

**Answer:** Bad data can contain loops, for example a lot that lists itself as a component. A depth limit stops the query from running indefinitely and shows where the data needs cleaning.

#### Does it handle serial numbers?

**Answer:** You can substitute serial numbers for lot numbers in the field values if your genealogy table stores them in the same columns. Otherwise adapt the join to your serial table.

#### Is this enough for a regulatory recall?

**Answer:** It provides the data pull. A recall also needs health hazard evaluation, regulator notification and customer communication, which follow your recall procedure and the applicable regulations.

## Search terms

lot traceability sql query, forward and backward traceability sql, recall traceability query, lot genealogy recursive cte, medical device traceability erp, serial lot where used sql, mock recall query, device history record sql

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