How to Link a CAPA to ERP Lots and Serial Numbers for Containment and Effectiveness

Scoping and tracking a CAPA with ERP data

1. 1Capture the trigger as ERP identifiers: part, lot, serial range, job or work order, PO receipt, work center and date range, not a narrative description.
2. 2Run a backward trace from the failure: serial or lot to the job, then to component lots and the receipts they came from.
3. 3Run a forward trace from the suspect lot, tool, operation or date range: to every job and serial that consumed or passed through it, then to shipments and customers.
4. 4Split the result into stock still on site (quarantine via NCR), WIP (hold the jobs), and shipped (customer notification decision).
5. 5Store the CAPA number against the affected lots and jobs in the ERP, or store the ERP list in the CAPA record, so the link works in both directions.
6. 6Define effectiveness criteria in ERP measurable terms before closing: reject rate for the operation, returns for the part, repeat NCR count for the cause code.
7. 7After the agreed period, rerun the same query and attach the result to the CAPA as the effectiveness evidence.

What the trace query looks like

-- Generic forward trace shape (table and column names vary by ERP)
SELECT j.job_no, p.lot_or_serial, s.ship_doc, s.customer, s.ship_date
FROM   material_issue mi
JOIN   job j            ON j.job_no = mi.job_no
JOIN   job_output p     ON p.job_no = j.job_no
LEFT JOIN shipment_line s ON s.lot_or_serial = p.lot_or_serial
WHERE  mi.component_lot = :suspect_lot
ORDER  BY s.ship_date;

Effectiveness checks that mean something

Common pitfalls

• !Defining the CAPA scope as a part number because lots were never captured, forcing a recall of everything.
• !Backflushing lot-controlled components with default or dummy lots, which destroys genealogy.
• !Stopping the trace at the first shipment and never checking whether the shipped part was used as a component by the customer or a downstream job.
• !Using implementation of the action as the effectiveness evidence.
• !Recording containment as one blanket decision instead of per lot and per shipment.