by 재래개 [SQL Query] [2021.09.17 11:24:08]
안녕하십니까 선배님들
저에겐 너무 어려운 내용이라 도움 이 필요로 하여 질문 을 올립니다..
4일 정도 고민 하다가 머리카락이 빠져 글을 올립니다 ㅜㅜ 부탁드립니다.
총 3개의 테이블이 있습니다.
BOM 테이블 / 투입 자재 라벨 이력 테이블 / LOT 생산 실적 이력 테이블
목적 은 BOM 기준으로, 어떤 자재 라벨들이 LOT 에 사용이 되었는지 자재 투입 시간으로 기준으로 (선입선출) 확인하는 쿼리 입니다..
BOM 테이블 에서 PARENT 와 CHIED 는 자재코드는 다르나 동일 한 자재로 봅니다.. ( 부모 BOM_ID = 자식 PARENT_ID , 부모 PARENT_ID 는 0 )
자재 투입 시간 순으로 LOT 수량 만큼 가져와야 하는데 ㅜㅜ 어렵습니다
※ 결과 테이블 쿼리가 좀 안맞아서 수정 했습니다 2021-09-17 11:45:30초 수정완료
[BOM 테이블] SELECT '0' AS PARENT_ID , 100 AS BOM_ID , 1 AS USE_QTY , '1-A' AS ITEM_CODE , 'ABC' AS MODEL_CODE FROM DUAL UNION ALL SELECT '100' AS PARENT_ID , 101 AS BOM_ID , 1 AS USE_QTY , '1-B' AS ITEM_CODE , 'ABC' AS MODEL_CODE FROM DUAL UNION ALL SELECT '100' AS PARENT_ID , 102 AS BOM_ID , 1 AS USE_QTY , '1-C' AS ITEM_CODE , 'ABC' AS MODEL_CODE FROM DUAL UNION ALL SELECT '100' AS PARENT_ID , 103 AS BOM_ID , 1 AS USE_QTY , '1-D' AS ITEM_CODE , 'ABC' AS MODEL_CODE FROM DUAL UNION ALL SELECT '0' AS PARENT_ID , 200 AS BOM_ID , 1 AS USE_QTY , '2-A' AS ITEM_CODE , 'ABC' AS MODEL_CODE FROM DUAL UNION ALL SELECT '200' AS PARENT_ID , 201 AS BOM_ID , 1 AS USE_QTY , '2-B' AS ITEM_CODE , 'ABC' AS MODEL_CODE FROM DUAL UNION ALL SELECT '200' AS PARENT_ID , 202 AS BOM_ID , 1 AS USE_QTY , '2-C' AS ITEM_CODE , 'ABC' AS MODEL_CODE FROM DUAL UNION ALL SELECT '200' AS PARENT_ID , 203 AS BOM_ID , 1 AS USE_QTY , '2-D' AS ITEM_CODE , 'ABC' AS MODEL_CODE FROM DUAL [자재 투입 이력] SELECT '00001' AS WORK_NO, '1-A' AS ITEM_CODE, '1-A-001' AS BARCODE_NO , 200 AS WORK_QTY , TO_DATE('2021-10-01 14:30:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '00001' AS WORK_NO, '1-A' AS ITEM_CODE, '1-A-002' AS BARCODE_NO , 100 AS WORK_QTY , TO_DATE('2021-10-01 14:20:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '00001' AS WORK_NO, '1-B' AS ITEM_CODE, '1-B-001' AS BARCODE_NO , 100 AS WORK_QTY , TO_DATE('2021-10-01 14:35:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '00001' AS WORK_NO, '2-B' AS ITEM_CODE, '2-B-001' AS BARCODE_NO , 100 AS WORK_QTY , TO_DATE('2021-10-01 14:40:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '00001' AS WORK_NO, '2-B' AS ITEM_CODE, '2-B-002' AS BARCODE_NO , 100 AS WORK_QTY , TO_DATE('2021-10-01 14:34:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '00001' AS WORK_NO, '2-C' AS ITEM_CODE, '2-C-001' AS BARCODE_NO , 200 AS WORK_QTY , TO_DATE('2021-10-01 14:50:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '00001' AS WORK_NO, '2-D' AS ITEM_CODE, '2-D-001' AS BARCODE_NO , 200 AS WORK_QTY , TO_DATE('2021-10-01 14:22:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL [LOT 생산 완료 이력] SELECT '00001' AS WORK_NO, '001' AS LOT_NO , 110 AS INPUT_QTY, TO_DATE('2021-10-01 15:10:23', 'YYYY-MM-DD HH24:MI:SS') AS WORK_TIME FROM DUAL UNION ALL SELECT '00001' AS WORK_NO, '002' AS LOT_NO , 110 AS INPUT_QTY, TO_DATE('2021-10-01 15:20:23', 'YYYY-MM-DD HH24:MI:SS') AS WORK_TIME FROM DUAL UNION ALL SELECT '00001' AS WORK_NO, '003' AS LOT_NO , 110 AS INPUT_QTY, TO_DATE('2021-10-01 15:21:23', 'YYYY-MM-DD HH24:MI:SS') AS WORK_TIME FROM DUAL UNION ALL SELECT '00001' AS WORK_NO, '004' AS LOT_NO , 110 AS INPUT_QTY, TO_DATE('2021-10-01 15:30:23', 'YYYY-MM-DD HH24:MI:SS') AS WORK_TIME FROM DUAL UNION ALL SELECT '00001' AS WORK_NO, '005' AS LOT_NO , 110 AS INPUT_QTY, TO_DATE('2021-10-01 15:40:23', 'YYYY-MM-DD HH24:MI:SS') AS WORK_TIME FROM DUAL UNION ALL SELECT '00001' AS WORK_NO, '006' AS LOT_NO , 110 AS INPUT_QTY, TO_DATE('2021-10-01 15:35:23', 'YYYY-MM-DD HH24:MI:SS') AS WORK_TIME FROM DUAL UNION ALL SELECT '00001' AS WORK_NO, '007' AS LOT_NO , 110 AS INPUT_QTY, TO_DATE('2021-10-01 15:55:23', 'YYYY-MM-DD HH24:MI:SS') AS WORK_TIME FROM DUAL [결과] SELECT '001' AS LOT_NO, 110 AS INPUT_QTY , '1-A-002' AS BARCODE_NO , 100 AS WORK_QTY , TO_DATE('2021-10-01 14:20:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '001' AS LOT_NO, 110 AS INPUT_QTY , '1-A-001' AS BARCODE_NO , 10 AS WORK_QTY , TO_DATE('2021-10-01 14:30:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '001' AS LOT_NO, 110 AS INPUT_QTY , '2-D-001' AS BARCODE_NO , 110 AS WORK_QTY , TO_DATE('2021-10-01 14:22:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '002' AS LOT_NO, 110 AS INPUT_QTY , '1-A-001' AS BARCODE_NO , 110 AS WORK_QTY , TO_DATE('2021-10-01 14:30:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '002' AS LOT_NO, 110 AS INPUT_QTY , '2-D-001' AS BARCODE_NO , 90 AS WORK_QTY , TO_DATE('2021-10-01 14:22:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '002' AS LOT_NO, 110 AS INPUT_QTY , '2-B-002' AS BARCODE_NO , 20 AS WORK_QTY , TO_DATE('2021-10-01 14:34:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '003' AS LOT_NO, 110 AS INPUT_QTY , '1-A-001' AS BARCODE_NO , 80 AS WORK_QTY , TO_DATE('2021-10-01 14:30:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '003' AS LOT_NO, 110 AS INPUT_QTY , '1-B-001' AS BARCODE_NO , 30 AS WORK_QTY , TO_DATE('2021-10-01 14:35:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '003' AS LOT_NO, 110 AS INPUT_QTY , '2-B-002' AS BARCODE_NO , 80 AS WORK_QTY , TO_DATE('2021-10-01 14:34:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL UNION ALL SELECT '003' AS LOT_NO, 110 AS INPUT_QTY , '2-B-001' AS BARCODE_NO , 30 AS WORK_QTY , TO_DATE('2021-10-01 14:40:23', 'YYYY-MM-DD HH24:MI:SS') AS INPUT_TIME FROM DUAL
현재 결과 테이블의 값을 보면, LotID에 사용된 자재별 투입수량, 투입시간으로 표현되었네요.
현재 생산중인 곳에 쿼리를 짜는게 문제신지, 테이블 구조를 만들고 계시는건지 잘 모르겠네요
간단히 예를 들어볼게요)
자재 투입 이력 (당연히 .. 자재는 창고에 재고를 잡을시에 별도 테이블에 단일정보가 생성 되어 있어야 합니다.. )
- Main_Create_Time = (자재의 최초 생성 시간 )
- Sub_Create_Time = (설비에 자재 투입 시간)
Lot 생산실적 테이블
- 실적 생성시간
- Sub_Create_Time (투입시간 -> 자재 투입 테이블 )
이런식 으로 테이블 구조가 구성 되어 있다면
모델별, Lot별, 선입선출 모두 찾을 수 있는데요.
WITH BOM_테이블 AS ( SELECT 0 parent_id, 100 bom_id, 1 use_qty, '1-A' item_code, 'ABC' model_code FROM dual UNION ALL SELECT 100, 101, 1, '1-B', 'ABC' FROM dual UNION ALL SELECT 100, 102, 1, '1-C', 'ABC' FROM dual UNION ALL SELECT 100, 103, 1, '1-D', 'ABC' FROM dual UNION ALL SELECT 0, 200, 1, '2-A', 'ABC' FROM dual UNION ALL SELECT 200, 201, 1, '2-B', 'ABC' FROM dual UNION ALL SELECT 200, 202, 1, '2-C', 'ABC' FROM dual UNION ALL SELECT 200, 203, 1, '2-D', 'ABC' FROM dual ) , 자재_투입_이력 AS ( SELECT '00001' work_no, '1-A' item_code, '1-A-001' barcode_no, 200 work_qty, '2021-10-01 14:30:23' input_time FROM dual UNION ALL SELECT '00001', '1-A', '1-A-002', 100, '2021-10-01 14:20:23' FROM dual UNION ALL SELECT '00001', '1-B', '1-B-001', 100, '2021-10-01 14:35:23' FROM dual UNION ALL SELECT '00001', '2-B', '2-B-001', 100, '2021-10-01 14:40:23' FROM dual UNION ALL SELECT '00001', '2-B', '2-B-002', 100, '2021-10-01 14:34:23' FROM dual UNION ALL SELECT '00001', '2-C', '2-C-001', 200, '2021-10-01 14:50:23' FROM dual UNION ALL SELECT '00001', '2-D', '2-D-001', 200, '2021-10-01 14:22:23' FROM dual ) , LOT_생산_완료_이력 AS ( SELECT '00001' work_no, '001' lot_no, 110 input_qty, '2021-10-01 15:10:23' work_time FROM dual UNION ALL SELECT '00001', '002', 110, '2021-10-01 15:20:23' FROM dual UNION ALL SELECT '00001', '003', 110, '2021-10-01 15:21:23' FROM dual UNION ALL SELECT '00001', '004', 110, '2021-10-01 15:30:23' FROM dual UNION ALL SELECT '00001', '005', 110, '2021-10-01 15:40:23' FROM dual UNION ALL SELECT '00001', '006', 110, '2021-10-01 15:35:23' FROM dual UNION ALL SELECT '00001', '007', 110, '2021-10-01 15:55:23' FROM dual ) SELECT a.work_no , a.lot_no , a.input_qty , a.work_time , b.item_code , b.barcode_no , b.bom_id , b.work_qty , b.input_time , LEAST( a.input_qty , b.work_qty , a.input_qty_s - b.work_qty_s + b.work_qty , b.work_qty_s - a.input_qty_s + a.input_qty ) qty FROM (SELECT work_no, lot_no, input_qty, work_time , SUM(input_qty) OVER(ORDER BY work_time) input_qty_s FROM LOT_생산_완료_이력 WHERE work_no = '00001' ) a , (SELECT work_no, item_code, barcode_no, work_qty, input_time, bom_id , SUM(work_qty) OVER(PARTITION BY bom_id ORDER BY input_time) work_qty_s FROM (SELECT work_no, item_code, barcode_no, work_qty, input_time , (SELECT bom_id FROM BOM_테이블 WHERE parent_id = 0 START WITH item_code = b.item_code CONNECT BY bom_id = PRIOR parent_id ) bom_id FROM 자재_투입_이력 b WHERE work_no = '00001' ) b ) b WHERE a.work_no = b.work_no AND b.work_qty_s - b.work_qty < a.input_qty_s AND b.work_qty_s > a.input_qty_s - a.input_qty ORDER BY work_time, bom_id, input_time ;