을지 파이프라인 — 3열 엑셀 조립

 MARKING_ORDER.PUBLISH_GS1_128 ──(복사)──▶ GD_ITEM_ISSUE_HISTORY.BARCODE
                                                      │  ITEM_ISSUE_ID (FK)
                                                      ▼
                                               GD_ITEM_ISSUE (출고 헤더)
                                                      │  또는  RETURN_GOODS (반품, 같은 FK)
                                                      │
       BARCODE = GS1_BOX_BARCODE_NO                   │
       ▼                                              │
 GD_PACKING_RESULT_ITEM ── MAPPING_GS1_BARCODE ─▶ GD_PACKING_RESULT_PACK
       (낱개→중박스)                                 (중박스→대박스, 같은 SSCC 체인)

 SUPPLY_REPORT (갑지 사본) ── ORDER_NO/ORDER_SEQ 로 GD_ITEM_ISSUE 와 조인

EXEC dbo.SP_SUPPLY_REPORT
  @GUBUN       nvarchar(20),     -- 'ERP' = 갑지 복제, 'S' = 을지 조회
  @search_date nvarchar(20),     -- DELIV_DATE, 'YYYYMMDD'
  @message     nvarchar(1000) OUTPUT;

-- Oracle UNION_ERP.SUPPLY_REPORT 21열 → MSSQL dbo.SUPPLY_REPORT 로 날짜 단위 복제
EXEC ('INSERT INTO #SUPPLY_REPORT_TEMP (...) SELECT ..., SALES_CODE AS CUST_CD, ITEM_CODE AS ITEM_CD
       FROM OPENQUERY(UNION_ERP, ''SELECT ROW_NUMBER() OVER (ORDER BY ORDER_NO,ORDER_SEQ) AS ROW_NO,
              SUPPLIER_BISNO, ..., SALES_CODE, ITEM_CODE
            FROM SUPPLY_REPORT WHERE DELIV_DATE = <search_date>'')')
DELETE SUPPLY_REPORT WHERE DELIV_DATE = @search_date
INSERT INTO SUPPLY_REPORT SELECT * FROM #SUPPLY_REPORT_TEMP

SELECT
  AA.ROW_NO,                                                          -- A
  RTRIM(SUBSTRING(BB.BARCODE, CHARINDEX('21', BB.BARCODE)+3, 20))     -- B
    AS SERIAL_NO,
 
  CASE WHEN SUBSTRING(CC.MAPPING_GS1_BARCODE,1,2)='00' THEN           -- C
    '(00)'+SUBSTRING(CC.MAPPING_GS1_BARCODE,3,18)
    + CASE WHEN ISNULL(DD.MAPPING_GS1_BARCODE,'')='' THEN ''
           ELSE '/(00)'+SUBSTRING(DD.MAPPING_GS1_BARCODE,3,18) END
  WHEN SUBSTRING(CC.MAPPING_GS1_BARCODE,1,2)='01' THEN
    '(01)'+SUBSTRING(CC.MAPPING_GS1_BARCODE,3,14)
    +'(21)'+SUBSTRING(CC.MAPPING_GS1_BARCODE,19,LEN(CC.MAPPING_GS1_BARCODE)-18)
    + CASE WHEN ISNULL(DD.MAPPING_GS1_BARCODE,'')='' THEN ''
           ELSE '/(01)'+SUBSTRING(DD.MAPPING_GS1_BARCODE,3,14)
                +'(21)'+SUBSTRING(DD.MAPPING_GS1_BARCODE,19,LEN(DD.MAPPING_GS1_BARCODE)-18) END
  END AS PACKING_NO,
 
  '' AS RFID_CODE, '' AS REMARK,
  BB.DESPATCH_ORDER_NO, BB.ORDER_SEQ
 
FROM SUPPLY_REPORT AA
  INNER JOIN (
    SELECT DESPATCH_ORDER_NO, ORDER_SEQ, BARCODE
      FROM GD_ITEM_ISSUE A INNER JOIN GD_ITEM_ISSUE_HISTORY B ON A.ITEM_ISSUE_ID=B.ITEM_ISSUE_ID
     WHERE B.BARCODE LIKE '%21%'
    UNION ALL
    SELECT DESPATCH_ORDER_NO, ORDER_SEQ, BARCODE
      FROM GD_ITEM_ISSUE A INNER JOIN RETURN_GOODS B       ON A.ITEM_ISSUE_ID=B.ITEM_ISSUE_ID
     WHERE B.BARCODE LIKE '%21%'
  ) BB ON AA.ORDER_NO=BB.DESPATCH_ORDER_NO AND AA.ORDER_SEQ=BB.ORDER_SEQ
  LEFT JOIN GD_PACKING_RESULT_ITEM CC ON BB.BARCODE             = CC.GS1_BOX_BARCODE_NO
  LEFT JOIN GD_PACKING_RESULT_PACK DD ON CC.MAPPING_GS1_BARCODE = DD.GS1_BOX_BARCODE_NO
WHERE AA.DELIV_DATE = @search_date
ORDER BY ROW_NO

EXEC dbo.SP_SUPPLY_REPORT @GUBUN='S', @search_date='20260423', @message=NULL;
 
-- 낱개 BARCODE 하나로 박스 체인 역추적
DECLARE @bc nvarchar(300) =
  '010880665502133717290111102603' + CHAR(29) + '212603000000007249';
SELECT
  LEFT(ci.MAPPING_GS1_BARCODE,30) AS 중박스_SSCC,
  ci.MAPPING_BOX_TYPE              AS 중박스_TYPE,
  pk.BOX_TYPE                       AS 중박스_자신_TYPE,
  LEFT(pk.MAPPING_GS1_BARCODE,30) AS 대박스_SSCC,
  pk.MAPPING_BOX_TYPE               AS 대박스_TYPE
FROM dbo.GD_PACKING_RESULT_ITEM ci
LEFT JOIN dbo.GD_PACKING_RESULT_PACK pk
  ON pk.GS1_BOX_BARCODE_NO = ci.MAPPING_GS1_BARCODE
WHERE ci.GS1_BOX_BARCODE_NO = @bc;