Developer ToolsFree generatorPDF, Excel, Word exportMedical Devices

Device Lot Genealogy and Traceability SQL Generator

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.

Fill in

Genealogy table

Table linking a parent lot to a consumed child lot

Parent lot column

Child (component) lot column

Shipment table

Shipment lot column

Customer column

Ship date column

Lot to trace

Generated code

Free, optional. Reopen it on any device.

Download:

Backward trace: finished device lot to component lots

WITH RECURSIVE backward AS (
  SELECT parent_lot AS parent_lot, child_lot AS child_lot, 1 AS depth
  FROM lot_genealogy
  WHERE parent_lot = 'LOT24A017'
  UNION ALL
  SELECT g.parent_lot, g.child_lot, b.depth + 1
  FROM lot_genealogy g
  JOIN backward b ON g.parent_lot = 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

WITH RECURSIVE forward AS (
  SELECT parent_lot AS parent_lot, child_lot AS child_lot, 1 AS depth
  FROM lot_genealogy
  WHERE child_lot = 'LOT24A017'
  UNION ALL
  SELECT g.parent_lot, g.child_lot, f.depth + 1
  FROM lot_genealogy g
  JOIN forward f ON g.child_lot = f.parent_lot
  WHERE f.depth < 10
)
SELECT DISTINCT s.lot_number AS shipped_lot,
       s.customer_id AS customer,
       s.ship_date AS ship_date
FROM shipment_lines s
WHERE s.lot_number IN (SELECT parent_lot FROM forward)
   OR s.lot_number = 'LOT24A017'
ORDER BY s.ship_date;

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.

Optional mock recall review

We can discuss how long a mock recall takes in your current systems and where the gaps are.

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

Was this tool useful?

How to use it

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.

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

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

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

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.

Frequently Asked Questions

Will this work with my ERP table names?

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?

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?

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?

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.

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

Talk to an Expert Explore More Free Tools