[오라클_어려움 上] 자재 투입 시간 으로 LOT 에 사용된 자재 정보 (라벨+수량) 구하기 0 6 840

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 




 

by 모래가흙흙 [2021.09.17 14:04:36]

혹시 결과의 LOT_NO = '001' 의 행들 중 '2-D-001' 바코드가 나온  기준이 뭔가요??


by 재래개 [2021.09.17 14:15:59]

LOT  수량이 110 일 경우에

1-A, 1-B, 1-C, 1-D 중에 한개 또는 합쳐서 자재 110 개 와 2-A, 2-B, 2-C, 2-D 중에 한개 또는 합쳐서 자재 110 개 가

있어야 합니다..

 

2-D-001 자재 라벨이 들어간 이유는 자재코드 2-A, 2-B, 2-C, 2-D 중 [자재 투입 이력] 에서

INPUT_TIME 이 2021-10-01 14:22:23 가장 빨리 투입 되어

 투입 시간 기준 선입선출로 차감 되어서 저렇게 나왔습니다.. 


by 요쏘라네 [2021.09.18 18:38:35]

현재 결과 테이블의 값을 보면,  LotID에 사용된 자재별 투입수량, 투입시간으로 표현되었네요. 

현재 생산중인 곳에 쿼리를 짜는게 문제신지, 테이블 구조를 만들고 계시는건지 잘 모르겠네요

간단히 예를 들어볼게요)

자재 투입 이력 (당연히 .. 자재는 창고에 재고를 잡을시에 별도 테이블에 단일정보가 생성 되어 있어야 합니다..  )

  - Main_Create_Time = (자재의 최초 생성 시간 )

  - Sub_Create_Time = (설비에 자재 투입 시간)

Lot 생산실적 테이블 

   - 실적 생성시간

   - Sub_Create_Time (투입시간 -> 자재 투입 테이블 )

이런식 으로 테이블 구조가 구성 되어 있다면

모델별, Lot별, 선입선출 모두 찾을 수 있는데요. 


by 재래개 [2021.09.23 08:47:28]

쿼리를 작성 하고 있습니다.. 

원하는 결과가 쿼리로 힘들 경우 테이블 구조를 바꿔야 하는데 

어떻게 바꿔야 원하는 결과가 나올지 몰라서 문의 드렸어요...  


by 마농 [2021.09.23 10:40:40]
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
;

 


by 재래개 [2021.09.23 11:59:28]

감사합니다 마농님. 

예전에 마농님께서 올려주신 http://www.gurubee.net/lecture/2837

다대다 수량배분 내용을 보면서 했었는데.. 잘 안풀려 문의 올렸었거든요.. 감사합니다!!!!!!!!!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입