제품 로트 추적과 회수 대상 조회 시스템 구축 방법

특정 원자재 로트에 문제가 생겼을 때 품목별 재고표만으로는 어떤 완제품이 영향을 받았는지 알기 어렵습니다. 제품 로트 추적 시스템은 실제 투입한 원자재, 중간제품, 완제품, 출고 명세를 연결해 확인 대상 거래처를 찾도록 구축해야 합니다. 조회된 목록은 회수 검토의 출발점이며, 회수 결정이나 실물 회수 완료와는 구분합니다.

입고 번호보다 중요한 것은 실제로 사용한 로트

다음은 구축 범위를 정하기 위한 가상 업무 설계입니다. 입고 담당자는 품목 코드, 공급자 로트 번호, 사내 로트 ID, 수량과 단위, 입고 위치를 등록합니다. 공급자가 같은 번호를 다시 쓸 수 있으므로 공급자·품목·로트 번호를 함께 식별하고 사내에서는 고유 ID를 발급합니다. '부품 X 재고 20개'와 'X의 어느 로트가 20개인가'를 서로 다른 정보로 관리하는 방식입니다.

생산 화면에서는 작업자가 투입한 로트를 스캔하고 실제 사용량을 입력합니다. 계획상 R-01을 쓰기로 했더라도 현장에서 R-02로 바꿨다면 실제 투입 기록을 남겨야 합니다. 중간제품 M-01을 만든 뒤 완제품 P-01과 P-02로 나눴다면 두 연결 모두 보존합니다. 여러 원료를 섞는 공정은 완제품 하나에 여러 원료 로트를 연결하고, 재작업으로 새 번호를 붙일 때도 이전 로트와의 관계를 남깁니다.

출고 담당자는 출고 화면에서 거래처, 명세서 번호, 완제품 로트, 수량을 확정합니다. 같은 주문을 두 로트로 나눠 보냈다면 출고 행도 나눕니다. 원료의 kg와 완제품의 개수를 더한 '총수량'은 만들지 않습니다. 원료 사용량 대조와 거래처별 완제품 출고량을 각각 조회하고, 단위 변환이 필요한 공정은 환산 기준을 따로 정합니다.

R-01이 사용된 완제품 출고를 찾아보는 SQL

아래는 저장된 연결을 따라 출고 대상을 찾는 SQLite 읽기 전용 예제입니다. 실제 고객 데이터 없이 메모리 DB에서 SQLite 3.54.0으로 실행했습니다. WITH RECURSIVE는 연결된 데이터를 반복 조회할 수 있습니다.[1] 예제의 수량 단위는 모두 개이며, 원료 투입량이나 현재 재고는 계산하지 않습니다.

WITH RECURSIVE
links(parent_lot, child_lot) AS (
  VALUES ('R-01','M-01'), ('M-01','P-01'),
         ('M-01','P-02'), ('R-02','P-03')
),
shipments(lot, customer, qty_ea) AS (
  VALUES ('P-01','A',12), ('P-02','B',8),
         ('P-03','C',9)
),
affected(lot) AS (
  SELECT 'R-01'
  UNION
  SELECT l.child_lot
  FROM links l JOIN affected a
    ON l.parent_lot = a.lot
)
SELECT s.lot, s.customer, s.qty_ea
FROM shipments s JOIN affected a ON s.lot = a.lot
ORDER BY s.lot;

R-01을 입력한 결과는 P-01·거래처 A·12개와 P-02·거래처 B·8개의 두 행입니다. 시작값만 R-02로 바꾸면 P-03·거래처 C·9개가 나옵니다. affected에는 로트 ID 하나만 두고 UNION을 사용하므로 같은 로트에 여러 경로로 도달해도 로트 행을 중복시키지 않습니다.[1] 출고 수량을 경로 수만큼 부풀리지 않으려는 선택입니다. 운영 조회는 출고 행의 고유 ID도 보존해야 여러 번 보낸 물량을 구분할 수 있습니다.

존재하지 않는 R-99로 조회하면 결과는 0행입니다. 이것이 '회수 대상 없음'을 증명하지는 않습니다. M-01에서 P-02로 가는 연결을 누락시키는 별도 시험에서는 P-01만 나왔습니다. 운영 화면은 입력한 로트의 존재 여부부터 확인하고, 미입력 생산 기록과 ERP 연동 실패를 함께 표시해야 합니다. 아직 출고하지 않은 제품은 이 SQL 결과에 나오지 않으므로 보관 재고와 생산 중 물량은 별도 목록에서 확인합니다.

대상 검토부터 거래처 확인까지 이어지는 화면

품질 담당자는 조사 화면에 의심 로트, 문제 내용, 근거 자료를 등록합니다. 시스템은 연결된 완제품과 출고 내역을 모으고, 누락 가능성이 있는 공정 기록을 별도 확인함에 넣습니다. 승인자는 근거와 불확실한 범위를 검토해 조치 대상을 확정하거나 추가 조사로 돌려보냅니다. 아래 상태값은 이 설계의 예시이며 법정 절차나 업종 공통 기준이 아닙니다.

• DRAFT: 품질 담당자가 대상을 검토 중입니다. 작성자는 자기 요청을 최종 승인하지 못하도록 권한을 나눕니다.
• APPROVED: 지정 승인자가 조치 범위를 확정했습니다. 승인 시점의 대상 목록과 근거 버전을 남깁니다.
• NOTIFIED: 담당자가 거래처에 안내를 보냈습니다. 발송 기록만으로 거래처가 받았다고 판정하지 않습니다.
• CONFIRMED: 거래처의 확인자와 확인 시각, 보유 수량을 기록했습니다. 반송·폐기·해제 등의 실제 처리 결과는 별도 항목입니다.

예를 들어 A에 출고한 12개 중 거래처가 보유한 수량이 7개라고 답하면 출고 12개, 확인 보유 7개를 각각 보존합니다. 나머지는 사용·재출고 등 사유를 확인할 대상으로 남기고 임의로 회수 완료 처리하지 않습니다. 관리자 조회는 대상 수량, 안내 여부, 확인 여부, 실물 처리 증빙을 나란히 보여줍니다.

보관 중인 대상은 출고 보류 작업과 연결하되, 화면에 보류 표시만 붙이는 데서 끝내지 않습니다. 출고 확정 시 서버가 최신 보류 상태를 다시 검사하고, 승인 이후 새로 들어온 출고 기록도 추가 조사 목록에 올리도록 설계합니다. 앞의 SQL은 이런 차단이나 동시 처리까지 구현한 코드가 아닙니다.

구축 전에 한 로트로 먼저 확인할 것

실제 입고 자료 한 건을 골라 생산 투입 기록, 분할·혼합·재작업 관계, 출고 명세까지 이어지는지 점검해 보세요. 연결이 끊기는 단계에서는 필요한 입력 화면과 담당자를 먼저 정할 수 있습니다. 기존 ERP와 연동할 경우에는 외부 행 ID, 마지막 수신 시각, 중복 수신 처리, 실패 내역을 요구사항에 넣습니다. 회수 판단과 대외 안내 기준은 해당 제품의 업무 책임자가 별도로 확정해야 합니다.

공식 확인 자료

• [1] SQLite WITH 절과 재귀 조회

소프트모아의 서비스는 softmoa.com에서 확인할 수 있습니다.

소프트모아는 해당 시스템을 구축합니다. 문의하기