MSSQL 저장 프로시저 원본 — 전수 + 한국어 주석

-- =============================================
-- Author:		김정웅
-- Create date: 2016.5.27
-- Description:	실적보고 조회용
-- =============================================
CREATE PROCEDURE [dbo].[SP_SUPPLY_REPORT]
	-- Add the parameters for the stored procedure here
	@GUBUN			NVARCHAR(10) = ''
	,@search_date	NVARCHAR(10) = ''	
	,@message		NVARCHAR(500) output
	
AS
 
SET NOCOUNT ON;
 
 
DECLARE @query			nvarchar(2000) 
	  ,@IN_ORDER_NO     nvarchar(20)		    --접수번호
	  ,@IN_ORDER_SEQ    int 					--접수순번
	  ,@IN_SUPPLY_GUBUN nvarchar(1)
	  ,@Serial_cnt		int = 0
	  ,@NoSerial_cnt	int = 0
 
SET @search_date = REPLACE(@search_date,'-','')
 
 
-- [분기] @GUBUN = 'ERP' 인 경우
IF(@GUBUN='ERP')
BEGIN	
	
	IF object_id('tempdb..#SUPPLY_REPORT_TEMP') is not null 
         -- [임시 테이블 정리]
         drop table #SUPPLY_REPORT_TEMP
	
	-- [임시 테이블 생성]
	CREATE TABLE #SUPPLY_REPORT_TEMP(
		[ROW_NO] [decimal](6, 0) NULL,
		[SUPPLIER_BISNO] [nvarchar](10) NULL,
		[SUPPLIER_TYPE] [nvarchar](1) NULL,
		[CONTRACT_TYPE] [nvarchar](1) NULL,
		[SUPPLY_GUBUN] [nvarchar](1) NULL,
		[SUPPLY_TYPE] [nvarchar](1) NULL,
		[SALES_NAME] [nvarchar](100) NULL,
		[SALES_BISNO] [nvarchar](10) NULL,
		[PHARMACY_NO] [nvarchar](8) NULL,
		[ITEM_NAME] [nvarchar](300) NULL,
		[ITEM_STD_CODE] [nvarchar](13) NULL,
		[BOX_QTY] [nvarchar](8) NULL,
		[TAX_QTY] [nvarchar](8) NULL,
		[DELIV_DATE] [nvarchar](8) NULL,
		[TAX_AMT] [decimal](12, 0) NULL,
		[ITEM_PRICE] [decimal](10, 2) NULL,
		[RECEIPT_NO] [nvarchar](14) NULL,
		[RETURN_CD] [nvarchar](12) NULL,
		[LOT_NO] [nvarchar](20) NULL,
		[EXPIRE_DT] [nvarchar](6) NULL,
		[SERIAL_GUBUN] [nvarchar](1) NULL,
		[REMARK] [nvarchar](200) NULL,
		[ORDER_NO] [nvarchar](20) NULL,
		[ORDER_SEQ] [int] NULL,
		[CUST_CD] [nvarchar](20) NULL,
		[ITEM_CD] [nvarchar](20) NULL
	) ON [PRIMARY]
 
--	declare @query nvarchar(2000)
     SET @query ='INSERT INTO #SUPPLY_REPORT_TEMP (ROW_NO,SUPPLIER_BISNO,SUPPLIER_TYPE,CONTRACT_TYPE,SUPPLY_GUBUN,SUPPLY_TYPE,SALES_NAME,
				                                    SALES_BISNO,PHARMACY_NO,ITEM_NAME,ITEM_STD_CODE,BOX_QTY,TAX_QTY,DELIV_DATE,TAX_AMT,
													ITEM_PRICE,LOT_NO,EXPIRE_DT,ORDER_NO,ORDER_SEQ,CUST_CD,ITEM_CD)
							   SELECT ROW_NO,SUPPLIER_BISNO,SUPPLIER_TYPE,CONTRACT_TYPE,SUPPLY_GUBUN,SUPPLY_TYPE,SALES_NAME,
				                                    SALES_BISNO,PHARMACY_NO,ITEM_NAME,ITEM_STD_CODE,BOX_QTY,TAX_QTY,DELIV_DATE,TAX_AMT,
													ITEM_PRICE,LOT_NO,EXPIRE_DT,ORDER_NO,ORDER_SEQ,SALES_CODE,ITEM_CODE
							   -- [원격 조회] linked server UNION_ERP (Oracle UNION_ERP)
							   FROM OPENQUERY(UNION_ERP, ''SELECT ROW_NUMBER() OVER (ORDER BY ORDER_NO,ORDER_SEQ) AS ROW_NO,
													SUPPLIER_BISNO,SUPPLIER_TYPE,CONTRACT_TYPE,SUPPLY_GUBUN,SUPPLY_TYPE,SALES_NAME,
				                                    SALES_BISNO,PHARMACY_NO,ITEM_NAME,ITEM_STD_CODE,BOX_QTY,TAX_QTY,DELIV_DATE,TAX_AMT,
													ITEM_PRICE,LOT_NO,EXPIRE_DT,ORDER_NO,ORDER_SEQ,SALES_CODE,ITEM_CODE
							                        FROM SUPPLY_REPORT
							                        WHERE DELIV_DATE = ''''' + @search_date + ''''' 
								                  '')	             '						 
	 --쿼리 실행
     -- [동적 쿼리 실행]
     EXEC (@query)
	 
	 UPDATE #SUPPLY_REPORT_TEMP 
	 SET ORDER_NO = REPLACE(ORDER_NO,'-','*') 
	 WHERE SUPPLY_GUBUN='2'
 
	 --일련번호 구순 UPDATE
	 -- [커서 선언] 행별 순회 시작
	 DECLARE MYCUR CURSOR FOR					
		SELECT ORDER_NO
			, ORDER_SEQ
			, SUPPLY_GUBUN			
		FROM #SUPPLY_REPORT_TEMP									
			OPEN MYCUR
				Fetch Next From MYCUR Into @IN_ORDER_NO ,@IN_ORDER_SEQ, @IN_SUPPLY_GUBUN
				WHILE (@@FETCH_STATUS=0)
				BEGIN       
					IF @IN_SUPPLY_GUBUN='1'
					BEGIN
						SELECT @Serial_cnt = count(*) 
						FROM GD_ITEM_ISSUE_HISTORY 
						WHERE ITEM_ISSUE_ID=(SELECT ITEM_ISSUE_ID FROM GD_ITEM_ISSUE WHERE DESPATCH_ORDER_NO=@IN_ORDER_NO and ORDER_SEQ=@IN_ORDER_SEQ)
							AND BARCODE like '%⟨GS⟩21%'
 
						SELECT @NoSerial_cnt = count(*) 
						FROM GD_ITEM_ISSUE_HISTORY 
						WHERE ITEM_ISSUE_ID=(SELECT ITEM_ISSUE_ID FROM GD_ITEM_ISSUE WHERE DESPATCH_ORDER_NO=@IN_ORDER_NO and ORDER_SEQ=@IN_ORDER_SEQ)
							AND BARCODE not like '%⟨GS⟩21%'
 
						IF @Serial_cnt < 1 
							UPDATE #SUPPLY_REPORT_TEMP SET SERIAL_GUBUN='0' WHERE ORDER_NO = @IN_ORDER_NO and ORDER_SEQ = @IN_ORDER_SEQ 
						ELSE IF @NoSerial_cnt < 1 
							UPDATE #SUPPLY_REPORT_TEMP SET SERIAL_GUBUN='1' WHERE ORDER_NO = @IN_ORDER_NO and ORDER_SEQ = @IN_ORDER_SEQ 
						ELSE
							UPDATE #SUPPLY_REPORT_TEMP SET SERIAL_GUBUN='2' WHERE ORDER_NO = @IN_ORDER_NO and ORDER_SEQ = @IN_ORDER_SEQ 
							
					END
					ELSE IF @IN_SUPPLY_GUBUN='2'
					BEGIN
						SELECT @Serial_cnt = count(*) 
						FROM RETURN_GOODS 
						WHERE ITEM_ISSUE_ID=(SELECT ITEM_ISSUE_ID FROM GD_ITEM_ISSUE WHERE DESPATCH_ORDER_NO=@IN_ORDER_NO and ORDER_SEQ=@IN_ORDER_SEQ)
							AND BARCODE like '%⟨GS⟩21%'
 
						SELECT @NoSerial_cnt = count(*) 
						FROM RETURN_GOODS 
						WHERE ITEM_ISSUE_ID=(SELECT ITEM_ISSUE_ID FROM GD_ITEM_ISSUE WHERE DESPATCH_ORDER_NO=@IN_ORDER_NO and ORDER_SEQ=@IN_ORDER_SEQ)
							AND BARCODE not like '%⟨GS⟩21%'
 
						IF @Serial_cnt < 1 
							UPDATE #SUPPLY_REPORT_TEMP SET SERIAL_GUBUN='0' WHERE ORDER_NO = @IN_ORDER_NO and ORDER_SEQ = @IN_ORDER_SEQ 
						ELSE IF @NoSerial_cnt < 1 
							UPDATE #SUPPLY_REPORT_TEMP SET SERIAL_GUBUN='1' WHERE ORDER_NO = @IN_ORDER_NO and ORDER_SEQ = @IN_ORDER_SEQ 
						ELSE
							UPDATE #SUPPLY_REPORT_TEMP SET SERIAL_GUBUN='2' WHERE ORDER_NO = @IN_ORDER_NO and ORDER_SEQ = @IN_ORDER_SEQ 
					END 
											
				Fetch Next From MYCUR Into @IN_ORDER_NO ,@IN_ORDER_SEQ, @IN_SUPPLY_GUBUN
				END
			CLOSE MYCUR
			DEALLOCATE MYCUR
 
	-- [삭제] SUPPLY_REPORT
	DELETE SUPPLY_REPORT where DELIV_DATE=@search_date
 
	 -----공급내역보고 존재시 Update
	 --UPDATE A SET A.ROW_NO = B.ROW_NO	
		--		,A.SUPPLIER_BISNO = B.SUPPLIER_BISNO
		--		,A.SUPPLIER_TYPE = B.SUPPLIER_TYPE
		--		,A.CONTRACT_TYPE = B.CONTRACT_TYPE
		--		,A.SUPPLY_GUBUN = B.SUPPLY_GUBUN
		--		,A.SUPPLY_TYPE = B.SUPPLY_TYPE
		--		,A.SALES_NAME = B.SALES_NAME 
		--		,A.SALES_BISNO = B.SALES_BISNO
		--		,A.PHARMACY_NO = B.PHARMACY_NO
		--		,A.ITEM_NAME = B.ITEM_NAME
		--		,A.ITEM_STD_CODE = B.ITEM_STD_CODE
		--		,A.BOX_QTY = B.BOX_QTY
		--		,A.TAX_QTY = B.TAX_QTY
		--		,A.DELIV_DATE = B.DELIV_DATE
		--		,A.TAX_AMT = B.TAX_AMT
		--		,A.ITEM_PRICE = B.ITEM_PRICE
		--		--,''
		--		,A.LOT_NO = B.LOT_NO
		--		,A.EXPIRE_DT = B.EXPIRE_DT
		--		--,''
		--		--,''
		--		,A.ORDER_NO = B.ORDER_NO
		--		,A.ORDER_SEQ = B.ORDER_SEQ
		--		,A.CUST_CD = B.CUST_CD
		--		,A.ITEM_CD = B.ITEM_CD
 
  --   FROM SUPPLY_REPORT A 
		--INNER JOIN #SUPPLY_REPORT_TEMP B  ON (A.ORDER_NO=B.ORDER_NO AND A.ORDER_SEQ=B.ORDER_SEQ)
        
	--신규
	-- [적재] SUPPLY_REPORT
	INSERT INTO SUPPLY_REPORT 
	SELECT * FROM #SUPPLY_REPORT_TEMP A
	--WHERE NOT EXISTS ( SELECT 1 FROM SUPPLY_REPORT B WHERE A.ORDER_NO=B.ORDER_NO AND A.ORDER_SEQ=B.ORDER_SEQ )
 
	-- [임시 테이블 정리]
	DROP TABLE #SUPPLY_REPORT_TEMP
 
 	SET @message = '성공_ERP'
END
ELSE IF(@GUBUN='S')
BEGIN
	SELECT ROW_NO
		,SUPPLIER_BISNO
		,SUPPLIER_TYPE
		,CONTRACT_TYPE
		,SUPPLY_GUBUN
		,SUPPLY_TYPE
		,SALES_NAME
		,SALES_BISNO
		,PHARMACY_NO
		,ITEM_NAME
		,ITEM_STD_CODE
		,BOX_QTY
		,TAX_QTY
		,DELIV_DATE
		,TAX_AMT
		,ITEM_PRICE
		,'' AS RECEIPT_NO
		,'' AS RETURN_CD
		,LOT_NO
		,EXPIRE_DT
		,SERIAL_GUBUN
		,'' AS REMARK
	FROM SUPPLY_REPORT
	WHERE DELIV_DATE = @search_date
	ORDER BY ROW_NO
 
	SELECT AA.ROW_NO
			,RTRIM(SUBSTRING(BB.BARCODE,CHARINDEX('⟨GS⟩21',BB.BARCODE)+3,20)) AS SERIAL_NO
			--,CASE WHEN isnull(DD.MAPPING_GS1_BARCODE,'')='' THEN '(00)'+ SUBSTRING(CC.MAPPING_GS1_BARCODE,3,18)
			--	ELSE '(00)'+ SUBSTRING(CC.MAPPING_GS1_BARCODE,3,18) + '/' + '(00)'+ SUBSTRING(DD.MAPPING_GS1_BARCODE,3,18) END AS PACKING_NO
			,CASE WHEN SUBSTRING(CC.MAPPING_GS1_BARCODE,1,2)='00' THEN
				CASE WHEN isnull(DD.MAPPING_GS1_BARCODE,'')='' THEN '(00)'+ SUBSTRING(CC.MAPPING_GS1_BARCODE,3,18)
				ELSE '(00)'+ SUBSTRING(CC.MAPPING_GS1_BARCODE,3,18) + '/' + '(00)'+ SUBSTRING(DD.MAPPING_GS1_BARCODE,3,18) END
			 WHEN SUBSTRING(CC.MAPPING_GS1_BARCODE,1,2)='01' THEN
				CASE WHEN isnull(DD.MAPPING_GS1_BARCODE,'')='' THEN '(01)'+ SUBSTRING(CC.MAPPING_GS1_BARCODE,3,14)+'(21)'+SUBSTRING(CC.MAPPING_GS1_BARCODE,19,len(CC.MAPPING_GS1_BARCODE)-18)
				ELSE '(01)'+ SUBSTRING(CC.MAPPING_GS1_BARCODE,3,14)+'(21)'+SUBSTRING(CC.MAPPING_GS1_BARCODE,19,len(CC.MAPPING_GS1_BARCODE)-18) + '/' + '(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 '%⟨GS⟩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 '%⟨GS⟩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 --and len(DD.GS1_BOX_BARCODE_NO)=20
	ORDER BY ROW_NO--BB.DESPATCH_ORDER_NO,BB.ORDER_SEQ
 
	SET @message = '성공_S'
END
 
 
 
 

 
--출고전표등록
--박은용(09.10.12)
 
CREATE                    PROC	[dbo].[SP_DespatchManage2D]
 
	@GUBUN		nvarchar(50) = '',		
	
	@s_sdate 			nvarchar(10) = '',		--시작일자(조회)
	@s_edate 			nvarchar(10) = '',		--종료일자(조회)
	@s_cust_cd 			nvarchar(50) = '',		--거래처코드(조회)
	@s_issue_gb 		nvarchar(50) = '',		--출고구분(조회)
	@s_issue_status 		nvarchar(50) = '',		--출고지시상태(조회)
	@s_issue_work_Seq		nvarchar(10) =	'',			--작업차수(조회조건)
	
	@despatch_no			nvarchar(50) = '',		--전표번호
	--@issue_gb				nvarchar(5) = '',		--출고구분
	@issue_gb2				nvarchar(5) = '',		--출고구분
	@issue_date				nvarchar(10) =	'',		--출고일자
	@issue_status			nvarchar(5)	= '',		--출고지시상태
	@cust_cd				nvarchar(50) =	'',		--거래처
	@pass_cust_cd			nvarchar(50) =	'',		--간납처
	@issue_dps				nvarchar(5)	= '',		--dps체크
 
	@issue_detail_date		nvarchar(10) =	'',		--출고일자(상세)
	@receipt_date			nvarchar(10) =	'',		--접수일자
	@item_cd				nvarchar(50) = '',		--제품코드
	@issue_qty				decimal(15,4) = 0,		--출고지시수량
	@lot_no					nvarchar(50) = '',		--제조번호
	@lot_date				nvarchar(10) =	'',		--제조일자
	@end_date				nvarchar(10) =	'',		--유효일자
	@item_issue_id			int = 0,				--제품출고일련번호
	@issue_trans_gb			nvarchar(5) =	'',		--배송처구분
	@issue_trans_cust_cd	nvarchar(50) =	'',		--배송처코드
	@issue_work_date		nvarchar(10) =	'',		--작업일자
	@issue_work_seq			int = 0,				--작업차수
	@insert_user_cd 		nvarchar(50) = '',
	@update_user_cd 		nvarchar(50) = '',
	
	@order_seq				nvarchar(10) = '',
	@search_ck				nvarchar(5) = '',
	@s_Despatch_no			nvarchar(10) = '',
	@e_Despatch_no			nvarchar(10) = '',
	@temp					nvarchar(50) = '',
	@log_user_id			nvarchar(50) = '', --AUDIT TRAIL
	
	@sys_emp_cd					nvarchar(50) = '',
	@order_date					nvarchar(8) = '',
	
	@message				NVARCHAR(2000) output		
AS   
  
--audittrail용 변수  
declare	@audittrail_id int  
-- [임시 테이블 생성]
CREATE TABLE #AUDIT_TEMP(audittrail_id	varchar(10))
 
DECLARE @intErrorCode int
SET @intErrorCode = 0
 
DECLARE @issue_count int
SET @issue_count = ( select count(*) from GD_ITEM_ISSUE_HISTORY where ITEM_ISSUE_ID = @item_issue_id)  --출고수 세팅
 
--전표조회
-- [분기] @gubun = 'S1' 인 경우
IF(@gubun = 'S1') 							
	BEGIN 
 
		SELECT A.RECEIPT_DATE
			, A.DESPATCH_ORDER_NO	
			, a.CUST_CD						--거래처코드
			, c.CUST_NM						--거래처명
			, c.CUST_AD1						--거래처주소1
			, c.CUST_AD2						--거래처주소2
			
			, a.PASS_CUST_CD					--간납처코드
			, d.CUST_NM as PASS_CUST_NM		--간납처명
			, d.CUST_AD1	as PASS_CUST_AD1	--간납처주소1
			, d.CUST_AD2	as PASS_CUST_AD2	--간납처주소2
			
			,ISNULL(ISSUE_TRANS_CUST_CD,'') AS 	ISSUE_TRANS_CUST_CD			--배송처코드
			,ISNULL(ISSUE_TRANS_GB	,'') AS 	ISSUE_TRANS_GB				--배송처 구분
			,ISNULL(f.COMMON_PART_NM,'') AS  issue_trans_gb_nm				--배송처 구분명
			,CASE WHEN ISSUE_TRANS_GB = '1' THEN c.CUST_NM WHEN ISSUE_TRANS_GB = '2'  THEN d.CUST_NM ELSE ''  END AS issue_trans_cust_nm  --배송처 명
 
			, A.ISSUE_GB2 as ISSUE_GB_CD	--출고구분(코드) 유니온 공통코드
			, b.COMMON_PART_NM as ISSUE_GB	--출고구분 - 직접출고
			, e.issue_ck_cd	-- 전표 완료인지 미완료인지 0(미완료), 2(완료)
		FROM 
			GD_CUST_ISSUE A
			LEFT OUTER JOIN common b ON (a.issue_gb = b.common_part_cd AND b.common_cd = 'GD401')	-- 완제품 출고구분(GD401)
			LEFT OUTER JOIN MA_CUSTOMER c ON (a.CUST_CD = c.CUST_CD)		--거래처코드
			LEFT OUTER JOIN MA_CUSTOMER d ON (a.PASS_CUST_CD = d.CUST_CD)	--간납처코드			
			LEFT OUTER JOIN common f ON (a.ISSUE_TRANS_GB = f.common_part_cd AND f.common_cd = 'GD403')	-- 배송처 구분(GD403)				
			LEFT OUTER JOIN (SELECT distinct a1.DESPATCH_ORDER_NO,case when ISNULL(b1.ISSUE_YN,'Y')='Y' then '2' ELSE '0' END AS issue_ck_cd
							FROM GD_CUST_ISSUE a1 LEFT join 
							(Select DESPATCH_ORDER_NO, ISSUE_YN from  GD_ITEM_ISSUE where ISSUE_YN = 'N') b1 ON (a1.DESPATCH_ORDER_NO=b1.DESPATCH_ORDER_NO)) e On (a.DESPATCH_ORDER_NO = e.DESPATCH_ORDER_NO)
		WHERE 
			A.RECEIPT_DATE BETWEEN @s_sdate AND @s_edate
			AND (a.CUST_CD LIKE '%' + @s_cust_cd + '%' OR d.CUST_NM LIKE '%' + @s_cust_cd + '%')  -- 2012.12.06 신현강. 거래처 조회 조건 변경		
			AND (e.issue_ck_cd LIKE @s_issue_status)												
			AND a.ISSUE_GB2 LIKE @s_issue_gb
		ORDER BY a.RECEIPT_DATE, a.DESPATCH_ORDER_NO
 
 
		/*
		SELECT
			     a.ORDER_DATE +	a.DESPATCH_ORDER_NO	AS DESPATCH_ORDER_NO 			--전표번호
				a.ISSUE_GB2 as ISSUE_GB_CD		--출고구분(코드)
				,b.COMMON_PART_NM as ISSUE_GB	--출고구분
				,LEFT(a.ORDER_DATE,4) + '-' + SUBSTRING(a.ORDER_DATE,5,2) + '-' + RIGHT(a.ORDER_DATE,2) as ISSUE_DATE 	-- 퇴사일자							--출고일자
				,a.CUST_CD						--거래처코드
				,c.CUST_NM						--거래처명
				,c.CUST_AD1						--거래처주소1
				,c.CUST_AD2						--거래처주소2
				,a.PASS_CUST_CD					--간납처코드
				,d.CUST_NM as PASS_CUST_NM		--간납처명
				,d.CUST_AD1	as PASS_CUST_AD1	--간납처주소1
				,d.CUST_AD2	as PASS_CUST_AD2	--간납처주소2
				,a.ISSUE_CK as issue_ck_cd
				,e.COMMON_PART_NM as issue_ck	--출고지시상태
				,'N' AS issue_chk				--상태체크
				,ISNULL(ISSUE_TRANS_CUST_CD,'') AS 	ISSUE_TRANS_CUST_CD			--배송처코드
				,ISNULL(ISSUE_TRANS_GB	,'') AS 	ISSUE_TRANS_GB				--배송처 구분
				,ISNULL(f.COMMON_PART_NM,'') AS  issue_trans_gb_nm				--배송처 구분명
				,CASE WHEN ISSUE_TRANS_GB = '1' THEN c.CUST_NM  
				WHEN ISSUE_TRANS_GB = '2'  THEN d.CUST_NM
				ELSE ''  END AS issue_trans_cust_nm  --배송처 명
				--,g.issue_dps_down_ck as ISSUE_DPS_DOWN_CK	--DPS체크	
		FROM 
				GD_CUST_ISSUE a
				LEFT OUTER JOIN common b ON (a.issue_gb = b.common_part_cd AND b.common_cd = 'GD401')	-- 완제품 출고구분(GD401)
				LEFT OUTER JOIN MA_CUSTOMER c ON (a.CUST_CD = c.CUST_CD)		--거래처코드
				LEFT OUTER JOIN MA_CUSTOMER d ON (a.PASS_CUST_CD = d.CUST_CD)	--간납처코드
				LEFT OUTER JOIN common e ON (a.issue_ck = e.common_part_cd AND e.common_cd = 'GD402')	-- 완제품 출고지시상태(GD402)
				LEFT OUTER JOIN common f ON (a.ISSUE_TRANS_GB = f.common_part_cd AND f.common_cd = 'GD403')	-- 배송처 구분(GD403)
		
					
		WHERE (a.CUST_CD LIKE '%' + @s_cust_cd + '%' OR d.CUST_NM LIKE '%' + @s_cust_cd + '%')  -- 2012.12.06 신현강. 거래처 조회 조건 변경
				AND (a.ISSUE_GB2 LIKE @s_issue_gb) 
				AND (a.ISSUE_CK LIKE @s_issue_status) 
				--AND ((@search_ck = '0' AND a.ISSUE_DATE BETWEEN @s_sdate AND @s_edate)
				AND ((@search_ck = '0' AND LEFT(a.ORDER_DATE,4) + '-' + SUBSTRING(a.ORDER_DATE,5,2) + '-' + RIGHT(a.ORDER_DATE,2) BETWEEN @s_sdate AND @s_edate)
					OR (@search_ck = '1' AND a.ISSUE_WORK_DATE = @s_sdate)
				--	OR (@search_ck = '2' AND a.ISSUE_WORK_DATE = @s_sdate AND isnull(a.ISSUE_WORK_SEQ,'0') LIKE '%' + @s_issue_work_Seq + '%')
					OR (@search_ck = '3' AND a.DESPATCH_ORDER_NO >=convert(nvarchar(10),convert(datetime,@s_sdate),111)+'-'+RIGHT('0000'+@s_Despatch_no,4)
					and a.DESPATCH_ORDER_NO <=convert(nvarchar(10),convert(datetime,@s_sdate),111)+'-'+RIGHT('0000'+@e_Despatch_no,4))
					
				)
				
					GROUP BY 
					a.ORDER_DATE +	a.DESPATCH_ORDER_NO			--전표번호
				,a.ISSUE_GB2 	--출고구분(코드)
				,b.COMMON_PART_NM 	--출고구분
				,LEFT(a.ORDER_DATE,4) + '-' + SUBSTRING(a.ORDER_DATE,5,2) + '-' + RIGHT(a.ORDER_DATE,2)					--출고일자
				,a.CUST_CD						--거래처코드
				,c.CUST_NM						--거래처명
				,c.CUST_AD1						--거래처주소1
				,c.CUST_AD2						--거래처주소2
				,a.PASS_CUST_CD					--간납처코드
				,d.CUST_NM 		--간납처명
				,d.CUST_AD1		--간납처주소1
				,d.CUST_AD2		--간납처주소2
				,a.ISSUE_CK 
				,e.COMMON_PART_NM 	--출고지시상태
				--,'N' 			--상태체크
				,ISNULL(ISSUE_TRANS_CUST_CD,'') 		--배송처코드
				,ISNULL(ISSUE_TRANS_GB	,'') 				--배송처 구분
				,ISNULL(f.COMMON_PART_NM,'') 				--배송처 구분명
				,CASE WHEN ISSUE_TRANS_GB = '1' THEN c.CUST_NM  
				WHEN ISSUE_TRANS_GB = '2'  THEN d.CUST_NM
				ELSE ''  END   --배송처 명
				--,g.issue_dps_down_ck as ISSUE_DPS_DOWN_CK	--DPS체크	
				,a.issue_work_date
				
				--and a.ISSUE_CK not in ('3','4')
		ORDER BY a.ORDER_DATE +	a.DESPATCH_ORDER_NO*/
		SELECT @intErrorCode = @@ERROR
	END
 
--전표상세 조회
ELSE IF (@gubun = 'S2')
	BEGIN
		SELECT
			a.ITEM_ISSUE_ID		--제품출고일련번호
			,a.ORDER_SEQ
			,a.RECEIPT_DATE
			,a.ISSUE_DATE 		--출고일자
			,a.ITEM_CD			--제품코드
			,b.ITEM_NM			--제품명
			,dbo.fn_Pack_unit_size(a.ITEM_CD) as ITEM_PACK_SIZE	--포장단위
			,isnull(a.ISSUE_QTY,0) as ISSUE_QTY		--출고지시수량
			,a.LOT_NO			--제조번호
			,a.ITEM_STATE
 
		FROM
			GD_ITEM_ISSUE a
			LEFT OUTER JOIN item_standard b ON (a.item_cd = b.item_cd)
		WHERE
			a.DESPATCH_ORDER_NO = @despatch_no
		ORDER BY
			a.ORDER_SEQ
 
		SELECT @intErrorCode = @@ERROR
			
	END
 
 
 
ELSE IF(@gubun = 'I') -- 폐기 입력
BEGIN
 
 
	--전표입력
	INSERT INTO 
			GD_CUST_ISSUE(												
							DESPATCH_ORDER_NO
							,ISSUE_GB				--출고구분
							,ISSUE_GB2				--출고구분2
							,RECEIPT_DATE				--출고일자
							,CUST_CD				--거래처코드
							,PASS_CUST_CD			--간납처코드
							,issue_trans_gb			--배송처구분
							,issue_trans_cust_cd	--배송처코드
							,ISSUE_CK				--출고지시상태
							,insert_user_cd
							,insert_time
					  	)
	VALUES
						(
							@despatch_no							
							, 1			-- 직접출고
							,@issue_gb2
							,@issue_date
							,@cust_cd
							,@pass_cust_cd
							,@issue_trans_gb
							,@issue_trans_cust_cd
							,@issue_status
							,@insert_user_cd
							,getdate()
						)
						
	--AuditTrail 생성
	select @audittrail_id = audittrail_id from GD_CUST_ISSUE where DESPATCH_ORDER_NO = @despatch_no
	exec SP_AudiTrail_INSERT_COMMON @tableName = 'GD_CUST_ISSUE', @audittrail_id_From_value = @audittrail_id, @audittrail_id_To_value = @audittrail_id, @audit_gubun = 'I', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''	
		
 
	SELECT @intErrorCode = @@ERROR
 
END
 
ELSE IF(@gubun = 'DI') -- 폐기상세 입력
BEGIN
	--전표상세입력
	INSERT INTO 
			 GD_ITEM_ISSUE(	
							ORDER_SEQ							
							,ITEM_CD		--제품코드
							,ISSUE_QTY		--출고수량
							,LOT_NO			--제조번호
							,DESPATCH_ORDER_NO
							,RECEIPT_DATE
							,ISSUE_YN
							,ITEM_STATE
							,insert_user_cd
							,insert_time
					  	)
	VALUES
						(
							@order_seq							
							,@item_cd
							,@issue_qty
							,@lot_no
							,@despatch_no
							,@receipt_date
							,'N'
							,'SUCCESS'
							,@insert_user_cd
							,getdate()
						)
						
	--AuditTrail 생성
	select @audittrail_id = audittrail_id from GD_ITEM_ISSUE where ITEM_ISSUE_ID = @@IDENTITY
	exec SP_AudiTrail_INSERT_COMMON @tableName = 'GD_ITEM_ISSUE', @audittrail_id_From_value = @audittrail_id, @audittrail_id_To_value = @audittrail_id, @audit_gubun = 'I', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''	
		
	SELECT @intErrorCode = @@ERROR
END
 
-- 전표 수정
ELSE IF(@gubun = 'U') 
BEGIN
	-- [트랜잭션 시작]
	BEGIN TRANSACTION	--트랜잭션 시작
	
	update
			GD_ITEM_ISSUE
	set	
			--ISSUE_DPS_DOWN_CK = 'N'
			UPDATE_USER_CD = @update_user_cd
			,update_time = getdate()
	where
			DESPATCH_ORDER_NO = @despatch_no
 
	INSERT INTO #AUDIT_TEMP
	SELECT audittrail_id from GD_ITEM_ISSUE where DESPATCH_ORDER_NO = @despatch_no
	
	exec SP_AuditTrail_BULK @tableName = 'GD_ITEM_ISSUE', @audit_gubun = 'U', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''
	
	SELECT @intErrorCode = @@ERROR	--에러체크	
		
	IF (@intErrorCode = 0)	--에러가 없다면
	begin
		UPDATE 	
			GD_CUST_ISSUE
 
		SET		
				ISSUE_GB =	1					--출고구분(직접출고 고정)
				,ISSUE_GB2 =	@issue_gb2					--출고구분
				,RECEIPT_DATE = @issue_date				--출고일자
				,CUST_CD = @cust_cd						--거래처코드
				,PASS_CUST_CD = @pass_cust_cd			--간납처코드
				,issue_trans_gb	 =@issue_trans_gb		--배송처구분
				,issue_trans_cust_cd	=@issue_trans_cust_cd--배송처코드
				,ISSUE_CK = @issue_status				--출고지시상태
				,update_user_cd = @update_user_cd
				,update_time = getdate()				
		WHERE	
				DESPATCH_ORDER_NO = @despatch_no
							
 
		--AuditTrail 생성
		select @audittrail_id = audittrail_id from GD_CUST_ISSUE where DESPATCH_ORDER_NO = @despatch_no
		exec SP_AudiTrail_INSERT_COMMON @tableName = 'GD_CUST_ISSUE', @audittrail_id_From_value = @audittrail_id, @audittrail_id_To_value = @audittrail_id, @audit_gubun = 'U', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''	
		
				
		SELECT @intErrorCode = @@ERROR	--에러체크			
	end		
	
	IF (@intErrorCode = 0 AND @@trancount > 0 )	COMMIT TRANSACTION	--이상이 없다면 COMMIT, 있다면 ROLLBACK
	ELSE ROLLBACK TRANSACTION 
	
	SELECT @intErrorCode = @@ERROR
END
 
-- 전표상세 수정
ELSE IF(@gubun = 'DU') 
BEGIN
--select * from GD_ITEM_ISSUE
	if(@issue_count > 0)	--출고되었으면
		BEGIN
			SELECT @intErrorCode = '-11'		-- 경고값을 넣어준다.
		END
	ELSE
		BEGIN
			UPDATE 	
					GD_ITEM_ISSUE
 
			SET		
					ORDER_SEQ=@order_seq					
					,ITEM_CD = @item_cd		--제품코드
					,LOT_NO = @lot_no			--제조번호
					,ISSUE_QTY = @issue_qty		--출고수량					
					,DESPATCH_ORDER_NO = @despatch_no				
					,update_user_cd = @update_user_cd
					,update_time = getdate()
 
			WHERE	
					ITEM_ISSUE_ID = @item_issue_id
						
		--AuditTrail 생성
		select @audittrail_id = audittrail_id from GD_ITEM_ISSUE where ITEM_ISSUE_ID = @item_issue_id
		exec SP_AudiTrail_INSERT_COMMON @tableName = 'GD_ITEM_ISSUE', @audittrail_id_From_value = @audittrail_id, @audittrail_id_To_value = @audittrail_id, @audit_gubun = 'U', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''	
						
 
			SELECT @intErrorCode = @@ERROR	--에러체크
		END
		
		SELECT @intErrorCode = @@ERROR
END
 
-- 전표 삭제
ELSE IF(@gubun = 'D') 
BEGIN
	
  -- [트랜잭션 시작]
  BEGIN TRANSACTION   --트랜잭션 처리
 
	DECLARE @issue_num int
	set @issue_num = (SELECT count(*) 
					FROM GD_ITEM_ISSUE_HISTORY A JOIN GD_ITEM_ISSUE B ON A.item_issue_id = b.ITEM_ISSUE_ID 
					WHERE B.DESPATCH_ORDER_NO = @despatch_no)
	IF(@issue_num > 0)	--출고되었으면
		BEGIN
			SELECT @intErrorCode = '-11'		-- 경고값을 넣어준다.
		END
	ELSE
 
	IF(@intErrorCode = 0)  BEGIN  
 
		BEGIN
		
			INSERT INTO #AUDIT_TEMP
			SELECT audittrail_id from GD_ITEM_ISSUE where DESPATCH_ORDER_NO=@despatch_no
					
			-- [삭제] GD_ITEM_ISSUE
			DELETE 	GD_ITEM_ISSUE WHERE	 DESPATCH_ORDER_NO=@despatch_no
 
			exec SP_AuditTrail_BULK @tableName = 'GD_ITEM_ISSUE', @audit_gubun = 'D', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''
	
			SELECT @intErrorCode = @@ERROR	--에러체크
		END
	END
	
	IF(@intErrorCode = 0)  BEGIN  
 
		--AuditTrail 생성
		select @audittrail_id = audittrail_id from GD_CUST_ISSUE where DESPATCH_ORDER_NO = @despatch_no
		exec SP_AudiTrail_INSERT_COMMON @tableName = 'GD_CUST_ISSUE', @audittrail_id_From_value = @audittrail_id, @audittrail_id_To_value = @audittrail_id, @audit_gubun = 'D', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''	
		
 
		-- [삭제] GD_CUST_ISSUE
		DELETE 	GD_CUST_ISSUE
 
		WHERE	DESPATCH_ORDER_NO = @despatch_no
		
		SELECT @intErrorCode = @@ERROR	--에러체크
		--update t1 set ttt=@intErrorCode
	END
	
	IF @intErrorCode = 0   AND  @@trancount  > 0	 	-- COMMIT, ROLLBACK 여부
		-- [트랜잭션 커밋]
		COMMIT TRANSACTION
	ELSE BEGIN
		-- [트랜잭션 롤백]
		ROLLBACK TRANSACTION
		RAISERROR('삭제 실패-출고가 완료되었거나 진행중입니다.',16,1)
	END
	
END
 
--전표상세 삭제
ELSE IF(@gubun = 'DD') -- 폐기상세 삭제
BEGIN
	
	if(@issue_count > 0)	--출고되었으면
		BEGIN
			SELECT @intErrorCode = '-11'		-- 경고값을 넣어준다.
		END
	ELSE
		BEGIN
 
 			--AuditTrail 생성
			select @audittrail_id = audittrail_id from GD_ITEM_ISSUE where ITEM_ISSUE_ID = @item_issue_id
			exec SP_AudiTrail_INSERT_COMMON @tableName = 'GD_ITEM_ISSUE', @audittrail_id_From_value = @audittrail_id, @audittrail_id_To_value = @audittrail_id, @audit_gubun = 'D', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''	
		
			-- [삭제] GD_ITEM_ISSUE
			DELETE 	GD_ITEM_ISSUE
 
			WHERE	ITEM_ISSUE_ID = @item_issue_id
 
			SELECT @intErrorCode = @@ERROR	--에러체크
		END
END
 
IF (@intErrorCode = 0) BEGIN
	SET @message = '정상적으로 처리되었습니다. '		
END
 
ELSE BEGIN
	SELECT  @message = DESCRIPTION FROM MASTER.dbo.sysmessages WHERE ERROR =@intErrorCode AND msglangid = '1042' 
END
 
RETURN (@intErrorCode)
 
 

 
 
 
CREATE PROC	[dbo].[SP_ERP_CUST_ISSUE]
 
	@gubun			nvarchar(20) = '',		--반드시 있어야 함
	@sdate 			nvarchar(10) = '', 
	@edate 			nvarchar(10) = '',
	@emp_cd 		nvarchar(20) = '',
	@sys_emp_cd		nvarchar(50) = '',
	@message		NVARCHAR(2000) output		--반드시 있어야 함
 
with recompile
 
AS    
 
SET XACT_ABORT ON 
 
DECLARE @intErrorCode	int
 declare		@audittrail_id	int, @row int			
    
--SELECT @message = '정상', @intErrorCode = @@error 
-- ERP 출고프로시져 호출 파라미터--
Declare 	  
	   @IN_ORDER_DATE   nvarchar(8) 		  --접수일자
	  ,@IN_ORDER_NO     int 				  --접수번호
	  ,@IN_ORDER_SEQ    int 					  --접수순번
	  ,@IN_DELIV_DATE   nvarchar(8) 		  --출고일자
	  ,@IN_PRCSEQ       int 				  --차수
	  ,@IN_EMP_NO       nvarchar(6) 		  --사원번호
	  ,@IN_LOT_NO       nvarchar(10) 	  --제조번호
	  ,@OUT_ERROR       nvarchar(2000)       --OUTPUT 파라미터
	  --,@TOT_NO			nvarchar(2000)	   --접수번호(제품상태 업데이트용)
 
	--임시 테이블 생성
	--출고마스터
	IF object_id('tempdb..#CUST_ISSUE_TEMP') is not null 
        -- [임시 테이블 정리]
        drop table #CUST_ISSUE_TEMP
 
	-- [임시 테이블 생성]
	create table #CUST_ISSUE_TEMP (	
		primary key(DESPATCH_ORDER_NO)
		,DESPATCH_ORDER_NO nvarchar(50)				 --주문번호 20141230-0008(order_date-order_no) 형식		
		,ISSUE_GB2 nvarchar(5)						 --출고구분(유니온제약 기준)
		,RECEIPT_DATE nvarchar(10)					 --접수일자		
		,CUST_CD nvarchar(50)						 --거래처코드	
		,ISSUE_TRANS_GB nvarchar(5)	 			     --배송처구분(GD401)1
		,ISSUE_TRANS_CUST_CD nvarchar(50)		     --배송처코드		
		,PASS_CUST_CD nvarchar(50)					 --간납처코드
	)		
	
	--출고전표상세
	IF object_id('tempdb..#CUST_ITEM_TEMP') is not null 
			-- [임시 테이블 정리]
			drop table #CUST_ITEM_TEMP
 
	-- [임시 테이블 생성]
	create table #CUST_ITEM_TEMP (
		primary key(DESPATCH_ORDER_NO, ORDER_SEQ,LOT_NO)
		,DESPATCH_ORDER_NO nvarchar(50) --주문번호		
		,ORDER_SEQ int					--품목순번
		,RECEIPT_DATE nvarchar(10)		--접수일자/  수탁,반품에서는 주문일자가 들어감
		,ITEM_CD nvarchar(50)			--제품코드
		,LOT_NO  nvarchar(50)			--제조번호		
		,ISSUE_QTY decimal(15,4)		--출고수량		
		,LOT_YM	nvarchar(6)			    --차수		
	)
 
-- 출고오더다운(=출고처리) 인터페이스
IF @GUBUN='DW'
BEGIN	
 
	INSERT INTO #CUST_ISSUE_TEMP
	SELECT
		A.ORDER_DATE + '-' + (REPLICATE('0',4 - LEN(A.ORDER_NO)) + Convert(nvarchar(4),A.ORDER_NO)) --주문번호
		,A.SLIP_GUBUN       --출고구분(유니온제약 16개 가지 출고 유형)			
		,convert(varchar,convert(datetime,A.RCALL_PRINT_DATE),23) AS RECEIPT_DATE --접수일자			    		
		,case when A.SLIP_GUBUN = '15' then A.CUST_EMP else A.SALES_CODE end AS SALES_CODE --거래처코드     
		,'1' AS ISSUE_TRANS_GB  --배송처구분(GD401)1
		,case when A.SLIP_GUBUN = '15' then A.CUST_EMP else A.SALES_CODE end AS ISSUE_TRANS_CODE --배송처코드 
		,'' AS RSALES_CODE	  --간납처코드
	FROM   UNION_ERP..KUP.SALES_ORDER A
                   
    WHERE 
		--A.RCALL_PRINT_DATE BETWEEN '20141201'   AND '20150211'
		A.RCALL_PRINT_DATE BETWEEN replace(@sdate,'-','')   AND replace(@edate,'-','')
		AND A.SLIP_GUBUN in ('11','15','41','51') -- 순서대로 정상매출,사원판매,시공품,교품
		AND A.OK_YN = '1'			
 
	INSERT INTO #CUST_ITEM_TEMP
	SELECT
		A.ORDER_DATE + '-' + (REPLICATE('0',4 - LEN(A.ORDER_NO)) + Convert(nvarchar(4),A.ORDER_NO))	--주문번호		
		,A.ORDER_SEQ		 --품목순번     
		,convert(varchar,convert(datetime,A.RCALL_PRINT_DATE),23) AS RECEIPT_DATE		--접수일자   
		,A.ITEM_CODE				--제품코드
		--,SUBSTRING(A.LOT_NO,5,6) AS LOT_NO
		,isnull(LOT_NO,'')
		,A.TAX_QTY + A.ADD_QTY	  AS ISSUE_QTY --출고수량						
		,A.LOT_YM         --차수
	FROM UNION_ERP..KUP.SALES_ITEM A
	WHERE 
		--A.RCALL_PRINT_DATE BETWEEN '20141201'   AND '20141226'			
		A.RCALL_PRINT_DATE  BETWEEN replace(@sdate,'-','')   AND replace(@edate,'-','')				
		AND A.SLIP_GUBUN in ('11','15','41','51') -- 순서대로 정상매출,사원판매,시공품,교품
		AND A.DELETE_YN  = 'N'	-- ERP상의 출고되지 않은 품목만 가져옴
		AND ISNULL(A.RCALL_PRINT_YN, 'N') = 'Y'
			
		--DECLARE @intErrorCode	int 
	--BEGIN TRANSACTION  
	--	BEGIN
			UPDATE a				
			SET 				 
				a.ISSUE_GB2 = b.ISSUE_GB2
				,a.RECEIPT_DATE = b.RECEIPT_DATE				
				, a.cust_cd=b.cust_cd
				, a.ISSUE_TRANS_GB=b.ISSUE_TRANS_GB
				, a.ISSUE_TRANS_CUST_CD=b.ISSUE_TRANS_CUST_CD
				, a.PASS_CUST_CD = b.PASS_CUST_CD
				, a.UPDATE_TIME  =getdate()
				, a.OUTER_INTERFACE='E'
				
			FROM GD_CUST_ISSUE a 
				JOIN #CUST_ISSUE_TEMP b on a.DESPATCH_ORDER_NO=b.DESPATCH_ORDER_NO
				
				--select * from GD_CUST_ISSUE   select * from #CUST_ISSUE_TEMP where TOT_NO='20141216-0008'
		
			-- [적재] GD_CUST_ISSUE
			INSERT INTO GD_CUST_ISSUE(			 
			 DESPATCH_ORDER_NO	--주문번호			 
			 ,RECEIPT_DATE		--접	수일자
			 ,ISSUE_GB2 		--구분2
			 ,ISSUE_GB			--출고구분(GD401) 상태 1
			 ,CUST_CD			--거래처 코드
			 ,PASS_CUST_CD		 --간납처코드
			 ,ISSUE_TRANS_GB		 --배송처구분(GD401)1
			 ,ISSUE_TRANS_CUST_CD--배송처코드
			 ,ISSUE_CK			 --출고완료여부
		 	 ,INSERT_TIME	     --입력일시
			 ,OUTER_INTERFACE	 --ERP연동 	
			)			       					
			SELECT 			 
			DESPATCH_ORDER_NO	--주문번호			 
			,RECEIPT_DATE
			,ISSUE_GB2
			,'1'--직접출고
			,cust_cd
			,PASS_CUST_CD
			,ISSUE_TRANS_GB
			,ISSUE_TRANS_CUST_CD
			,'0'
			,getdate()
			,'E'		
	
			FROM #CUST_ISSUE_TEMP a 
			WHERE NOT EXISTS ( SELECT 1 FROM GD_CUST_ISSUE b WHERE a.DESPATCH_ORDER_NO=b.DESPATCH_ORDER_NO)
				AND EXISTS ( SELECT 1 FROM #CUST_ITEM_TEMP c WHERE a.DESPATCH_ORDER_NO=c.DESPATCH_ORDER_NO)
			
			UPDATE a				
			SET a.RECEIPT_DATE = b.RECEIPT_DATE
			, a.item_cd=b.item_cd
			, a.issue_qty=b.issue_qty
			, a.lot_no=b.lot_no		
			, a.LOT_YM = b.LOT_YM
			, a.UPDATE_TIME=getdate()			
			FROM GD_ITEM_ISSUE a JOIN #CUST_ITEM_TEMP b on a.DESPATCH_ORDER_NO=b.DESPATCH_ORDER_NO and a.ORDER_SEQ = b.ORDER_SEQ
			where ISSUE_YN != 'Y'
		
			-- [적재] GD_ITEM_ISSUE
			INSERT INTO GD_ITEM_ISSUE(DESPATCH_ORDER_NO, ORDER_SEQ, RECEIPT_DATE, ITEM_CD, ISSUE_QTY, LOT_NO, LOT_YM, ISSUE_YN, INSERT_TIME)
			SELECT 
			     DESPATCH_ORDER_NO
				,ORDER_SEQ         --순번		
				,RECEIPT_DATE		--접수일자	
				,ITEM_CD		   --제품코드
				,ISSUE_QTY		   --출고수량	
				,LOT_NO			   --제조번호
				,LOT_YM            --차수			
				,'N'
				,getdate()		   --입력일시							
			FROM #CUST_ITEM_TEMP a
			WHERE NOT EXISTS ( SELECT 1 FROM GD_ITEM_ISSUE b WHERE a.DESPATCH_ORDER_NO=b.DESPATCH_ORDER_NO and a.ORDER_SEQ = b.ORDER_SEQ)	
			--EXECUTE ( 'DBMS_OUTPUT.ENABLE(1000000)')  AT   UNION_ERP 
			--DBMS_OUTPUT.ENABLE(1000000);
	--	END		
	--IF @intErrorCode = 0 and @@trancount > 0 COMMIT TRANSACTION
	--ELSE IF @@trancount > 0 ROLLBACK TRANSACTION
	--DECLARE @intErrorCode	int 
	--BEGIN DISTRIBUTED TRANSACTION  		
	--	BEGIN	
			-- [커서 선언] 행별 순회 시작
			DECLARE MYCUR CURSOR FOR
				SELECT LEFT(A.DESPATCH_ORDER_NO,8)		--전표일자
					, RIGHT(A.DESPATCH_ORDER_NO,4)		--전표번호	
					, A.ORDER_SEQ         --전표순번					
					, convert(varchar,GETDATE(),112)																
					, A.LOT_YM			 --차수(일단 1고정)
					, @emp_cd			 --사원번호(INSERT)  	
					, A.LOT_NO			 --제조번호						
					, 'N'				 --OUTPUT 파라미터				 
				FROM GD_ITEM_ISSUE A					
				WHERE  --A.RECEIPT_DATE BETWEEN '20141201'   AND '20141226'
					A.RECEIPT_DATE BETWEEN replace(@sdate,'-','')   AND replace(@edate,'-','')
					AND A.ISSUE_YN = 'N' 
					AND substring(A.DESPATCH_ORDER_NO,9,1) = '-' --반품제외
					AND isnull(A.LOT_NO,'') != '' -- 제조번호없는건 나중에 STOCKOUT로 update함					
				OPEN MYCUR
					Fetch Next From MYCUR Into @IN_ORDER_DATE ,@IN_ORDER_NO ,@IN_ORDER_SEQ, @IN_DELIV_DATE , @IN_PRCSEQ , @IN_EMP_NO,  @IN_LOT_NO, @OUT_ERROR
					WHILE (@@FETCH_STATUS=0)
					BEGIN       
					EXECUTE ( 'begin SP_DELIVERY_CHECK(
									?
								,?
								,?
								,?
								,?
								,?
								,?
								,?
								); end;',					
									@IN_ORDER_DATE   
									,@IN_ORDER_NO    
									,@IN_ORDER_SEQ    
									,@IN_DELIV_DATE   
									,@IN_PRCSEQ      
									,@IN_EMP_NO     
									,@IN_LOT_NO   
									,@OUT_ERROR  output    
									)  AT   UNION_ERP 					 
					     
						--현재 제품상태결과 리턴
						UPDATE GD_ITEM_ISSUE		
						SET  ITEM_STATE = @OUT_ERROR
							,UPDATE_TIME = GETDATE() 										
						WHERE DESPATCH_ORDER_NO = @IN_ORDER_DATE + '-' +  (REPLICATE('0',4 - LEN(@IN_ORDER_NO)) + Convert(nvarchar(4),@IN_ORDER_NO))					
							AND ORDER_SEQ = @IN_ORDER_SEQ									
					Fetch Next From MYCUR Into @IN_ORDER_DATE ,@IN_ORDER_NO ,@IN_ORDER_SEQ, @IN_DELIV_DATE , @IN_PRCSEQ , @IN_EMP_NO,  @IN_LOT_NO, @OUT_ERROR
					END
				CLOSE MYCUR
				DEALLOCATE MYCUR
				
				UPDATE GD_ITEM_ISSUE
				SET ITEM_STATE = 'STOCKOUT', UPDATE_TIME = GETDATE() 	
				WHERE isnull(LOT_NO,'')=''
		
		--END
	--IF @intErrorCode = 0 and @@trancount > 0  COMMIT TRANSACTION
	--ELSE IF @@trancount > 0 BEGIN  ROLLBACK TRANSACTION
 
	--	SELECT  @message = DESCRIPTION FROM MASTER.dbo.sysmessages WHERE ERROR =@intErrorCode AND msglangid = '1042'    
		
	--END	
 
END
 
-- 수탁처리 주문 인터페이스
ELSE IF @GUBUN='DW_SUTAK'
BEGIN	
 
	INSERT INTO #CUST_ISSUE_TEMP
	SELECT
		A.ORDER_DATE + '~' + (REPLICATE('0',4 - LEN(A.ORDER_NO)) + Convert(nvarchar(4),A.ORDER_NO)) --주문번호
		,'18'       --출고구분(유니온제약 16개 가지 출고 유형)			
		,''--LEFT(A.ORDER_DATE,4) + '-' + SUBSTRING(A.ORDER_DATE,5,2) + '-' + RIGHT(A.ORDER_DATE,2) AS ORDER_DATE --주문일자			    		
		,A.SALES_CODE --거래처코드     
		,'1' AS ISSUE_TRANS_GB  --배송처구분(GD401)1
		,A.SALES_CODE AS ISSUE_TRANS_CODE --배송처코드 
		,'' AS RSALES_CODE	  --간납처코드
	FROM   UNION_ERP..KUP.SALES_ORDER_EXP A	
                  
    WHERE 		
		A.SLIP_GUBUN = '18' -- 수탁 구분값
		--AND A.ORDER_DATE BETWEEN '20141201'   AND '20141226'
		AND A.ORDER_DATE BETWEEN replace(@sdate,'-','')   AND replace(@edate,'-','')
		AND A.ACC_YN = 'N'
 
	INSERT INTO #CUST_ITEM_TEMP
	SELECT
		A.ORDER_DATE + '~' + (REPLICATE('0',4 - LEN(A.ORDER_NO)) + Convert(nvarchar(4),A.ORDER_NO))	--주문번호		
		,A.ORDER_SEQ			--품목순번     
		,'' AS RECEIPT_DATE		--접수일자   
		,A.ITEM_CODE				--제품코드
		--,SUBSTRING(A.LOT_NO,5,6) AS LOT_NO		
		,isnull(LOT_NO,'')
		,QTY + isnull(A.EXTRA_QTY,0) AS ISSUE_QTY --출고수량						
		,'' AS LOT_YM         --차수
	FROM UNION_ERP..KUP.SALES_ITEM_EXP A
	WHERE 
		A.SLIP_GUBUN = '18' -- 수탁 구분값
		--AND A.ORDER_DATE BETWEEN '20141201'   AND '20151226'			
		AND A.ORDER_DATE  BETWEEN replace(@sdate,'-','')   AND replace(@edate,'-','')						
		AND A.DELIV_YN  = 'N'	-- ERP상의 출고되지 않은 품목만 가져옴
		
 
	UPDATE a				
	SET 				 
		a.ISSUE_GB2 = b.ISSUE_GB2
		,a.RECEIPT_DATE = b.RECEIPT_DATE				
		, a.cust_cd=b.cust_cd
		, a.ISSUE_TRANS_GB=b.ISSUE_TRANS_GB
		, a.ISSUE_TRANS_CUST_CD=b.ISSUE_TRANS_CUST_CD
		, a.PASS_CUST_CD = b.PASS_CUST_CD
		, a.UPDATE_TIME  =getdate()
		, a.OUTER_INTERFACE='E'
				
	FROM GD_CUST_ISSUE a 
		JOIN #CUST_ISSUE_TEMP b on a.DESPATCH_ORDER_NO=b.DESPATCH_ORDER_NO
				
		--select * from GD_CUST_ISSUE   select * from #CUST_ISSUE_TEMP where TOT_NO='20141216-0008'
		
	-- [적재] GD_CUST_ISSUE
	INSERT INTO GD_CUST_ISSUE(			 
		DESPATCH_ORDER_NO	--주문번호			 
		,RECEIPT_DATE		--접	수일자
		,ISSUE_GB2 		--구분2
		,ISSUE_GB			--출고구분(GD401) 상태 1
		,CUST_CD			--거래처 코드
		,PASS_CUST_CD		 --간납처코드
		,ISSUE_TRANS_GB		 --배송처구분(GD401)1
		,ISSUE_TRANS_CUST_CD--배송처코드
		,ISSUE_CK			 --출고완료여부
		,INSERT_TIME	     --입력일시
		,OUTER_INTERFACE	 --ERP연동 	
	)			       					
	SELECT 			 
	DESPATCH_ORDER_NO	--주문번호			 
	,RECEIPT_DATE
	,ISSUE_GB2
	,'1'--직접출고
	,cust_cd
	,PASS_CUST_CD
	,ISSUE_TRANS_GB
	,ISSUE_TRANS_CUST_CD
	,'0'
	,getdate()
	,'E'		
	
	FROM #CUST_ISSUE_TEMP a 
	WHERE NOT EXISTS ( SELECT 1 FROM GD_CUST_ISSUE b WHERE a.DESPATCH_ORDER_NO=b.DESPATCH_ORDER_NO)
		AND EXISTS ( SELECT 1 FROM #CUST_ITEM_TEMP c WHERE a.DESPATCH_ORDER_NO=c.DESPATCH_ORDER_NO)
			
	UPDATE a				
	SET a.RECEIPT_DATE = b.RECEIPT_DATE
	, a.item_cd=b.item_cd
	, a.issue_qty=b.issue_qty
	, a.lot_no=b.lot_no		
	, a.LOT_YM = b.LOT_YM
	, a.UPDATE_TIME=getdate()			
	FROM GD_ITEM_ISSUE a JOIN #CUST_ITEM_TEMP b on a.DESPATCH_ORDER_NO=b.DESPATCH_ORDER_NO and a.ORDER_SEQ = b.ORDER_SEQ
	where ISSUE_YN != 'Y'
		
	-- [적재] GD_ITEM_ISSUE
	INSERT INTO GD_ITEM_ISSUE(DESPATCH_ORDER_NO, ORDER_SEQ, RECEIPT_DATE, ITEM_CD, ISSUE_QTY, LOT_NO, LOT_YM, ISSUE_YN, INSERT_TIME)
	SELECT 
			DESPATCH_ORDER_NO
		,ORDER_SEQ         --순번		
		,RECEIPT_DATE		--접수일자	
		,ITEM_CD		   --제품코드
		,ISSUE_QTY		   --출고수량	
		,LOT_NO			   --제조번호
		,LOT_YM            --차수			
		,'N'
		,getdate()		   --입력일시							
	FROM #CUST_ITEM_TEMP a
	WHERE NOT EXISTS ( SELECT 1 FROM GD_ITEM_ISSUE b WHERE a.DESPATCH_ORDER_NO=b.DESPATCH_ORDER_NO and a.ORDER_SEQ = b.ORDER_SEQ)		
 
END
 
-- 반품처리 주문 인터페이스
ELSE IF @GUBUN='DB'
BEGIN		
 
	INSERT INTO #CUST_ISSUE_TEMP
	SELECT  
		A.ORDER_DATE + '*' + (REPLICATE('0',4 - LEN(A.ORDER_NO)) + Convert(nvarchar(4),A.ORDER_NO)) --주문번호
		,'21'       --출고구분(유니온제약 16개 가지 출고 유형)			
		,''--LEFT(A.ORDER_DATE,4) + '-' + SUBSTRING(A.ORDER_DATE,5,2) + '-' + RIGHT(A.ORDER_DATE,2) AS ORDER_DATE --주문일자			    		
		,A.SALES_CODE		--거래처코드     
		,'1' AS ISSUE_TRANS_GB  --배송처구분(GD401)1
		,A.SALES_CODE AS ISSUE_TRANS_CODE --배송처코드 
		,'' AS RSALES_CODE	  --간납처코드
	FROM   UNION_ERP..KUP.SALES_ORDER A
		--INNER JOIN (SELECT ORDER_DATE, ORDER_NO FROM  UNION_ERP..KUP.SALES_ITEM WHERE SALES_QTY > 0 and BULK_QTY = 0 GROUP BY ORDER_DATE, ORDER_NO) B ON A.ORDER_DATE=B.ORDER_DATE and A.ORDER_NO=B.ORDER_NO
                   
    WHERE 
		A.SLIP_GUBUN = '21' -- 반품 구분값
		--AND A.ORDER_DATE BETWEEN '20141205'   AND '20141231'
		AND A.ORDER_DATE BETWEEN replace(@sdate,'-','')   AND replace(@edate,'-','')		
		AND NOT A.RETRN_REASON = '10'
		AND A.OK_YN = '1'
		AND isnull(A.QC_STATUS,'1') = '1'
		AND DELETE_YN = 'N'
 
	INSERT INTO #CUST_ITEM_TEMP
	SELECT
		A.ORDER_DATE + '*' + (REPLICATE('0',4 - LEN(A.ORDER_NO)) + Convert(nvarchar(4),A.ORDER_NO))	--주문번호		
		,A.ORDER_SEQ		 --품목순번     
		,'' AS RECEIPT_DATE		--접수일자 
		,A.ITEM_CODE				--제품코드
		--,SUBSTRING(A.LOT_NO,5,6) AS LOT_NO
		,isnull(B.QC_LOT,'') AS LOT_NO
		,B.RETRN_QTY  AS ISSUE_QTY --출고수량						
		,A.LOT_YM         --차수
	FROM UNION_ERP..KUP.SALES_ITEM A 
		INNER JOIN UNION_ERP..KUP.QC_LOT B ON A.ORDER_DATE=B.QC_DATE and A.ORDER_NO=B.QC_NO and A.ORDER_SEQ=B.QC_SEQ
	WHERE 		
		A.SLIP_GUBUN = '21'
		--AND A.ORDER_DATE BETWEEN '20121205'   AND '20141231'			
		AND A.ORDER_DATE BETWEEN replace(@sdate,'-','')   AND replace(@edate,'-','')			
		and A.SALES_QTY > 0 --and A.BULK_QTY = 0 		--BULK_QTY(낱알수량) 존재하는 반품은 일련번호 제외
				
	
	UPDATE a				
	SET 				 
		a.ISSUE_GB2 = b.ISSUE_GB2
		,a.RECEIPT_DATE = b.RECEIPT_DATE				
		, a.cust_cd=b.cust_cd
		, a.ISSUE_TRANS_GB=b.ISSUE_TRANS_GB
		, a.ISSUE_TRANS_CUST_CD=b.ISSUE_TRANS_CUST_CD
		, a.PASS_CUST_CD = b.PASS_CUST_CD
		, a.UPDATE_TIME  =getdate()
		, a.OUTER_INTERFACE='E'
				
	FROM GD_CUST_ISSUE a 
		JOIN #CUST_ISSUE_TEMP b on a.DESPATCH_ORDER_NO=b.DESPATCH_ORDER_NO
				
		--select * from GD_CUST_ISSUE   select * from #CUST_ISSUE_TEMP where TOT_NO='20141216-0008'
		
	-- [적재] GD_CUST_ISSUE
	INSERT INTO GD_CUST_ISSUE(			 
		DESPATCH_ORDER_NO	--주문번호			 
		,RECEIPT_DATE		--접	수일자
		,ISSUE_GB2 		--구분2
		,ISSUE_GB			--출고구분(GD401) 상태 1
		,CUST_CD			--거래처 코드
		,PASS_CUST_CD		 --간납처코드
		,ISSUE_TRANS_GB		 --배송처구분(GD401)1
		,ISSUE_TRANS_CUST_CD--배송처코드
		,ISSUE_CK			 --출고완료여부
		,INSERT_TIME	     --입력일시
		,OUTER_INTERFACE	 --ERP연동 	
	)			       					
	SELECT 			 
	DESPATCH_ORDER_NO	--주문번호			 
	,RECEIPT_DATE
	,ISSUE_GB2
	,'1'--직접출고
	,cust_cd
	,PASS_CUST_CD
	,ISSUE_TRANS_GB
	,ISSUE_TRANS_CUST_CD
	,'0'
	,getdate()
	,'E'		
	
	FROM #CUST_ISSUE_TEMP a 
	WHERE NOT EXISTS ( SELECT 1 FROM GD_CUST_ISSUE b WHERE a.DESPATCH_ORDER_NO=b.DESPATCH_ORDER_NO)
		AND EXISTS ( SELECT 1 FROM #CUST_ITEM_TEMP c WHERE a.DESPATCH_ORDER_NO=c.DESPATCH_ORDER_NO)
			
	UPDATE a				
	SET a.RECEIPT_DATE = b.RECEIPT_DATE
	, a.item_cd=b.item_cd
	, a.issue_qty=b.issue_qty
	, a.lot_no=b.lot_no		
	, a.LOT_YM = b.LOT_YM
	, a.UPDATE_TIME=getdate()			
	FROM GD_ITEM_ISSUE a JOIN #CUST_ITEM_TEMP b on a.DESPATCH_ORDER_NO=b.DESPATCH_ORDER_NO and a.ORDER_SEQ = b.ORDER_SEQ
	where ISSUE_YN != 'Y'
		
	-- [적재] GD_ITEM_ISSUE
	INSERT INTO GD_ITEM_ISSUE(DESPATCH_ORDER_NO, ORDER_SEQ, RECEIPT_DATE, ITEM_CD, ISSUE_QTY, LOT_NO, LOT_YM, ISSUE_YN, INSERT_TIME)
	SELECT 
			DESPATCH_ORDER_NO
		,ORDER_SEQ         --순번		
		,RECEIPT_DATE		--접수일자	
		,ITEM_CD		   --제품코드
		,ISSUE_QTY		   --출고수량	
		,LOT_NO			   --제조번호
		,LOT_YM            --차수		
		,'N'	
		,getdate()		   --입력일시							
	FROM #CUST_ITEM_TEMP a
	WHERE NOT EXISTS ( SELECT 1 FROM GD_ITEM_ISSUE b WHERE a.DESPATCH_ORDER_NO=b.DESPATCH_ORDER_NO and a.ORDER_SEQ = b.ORDER_SEQ)	
 
END
 

  
/*******************************************************************     
 
 작 성 자 : 김정웅
 수 정 자 :                                                      
 SP 명    : SP_IssueExtra
 인    수 :	
 리 턴 값 :	
 설    명 : 기타출고
 사 용 처 : IssueExtra.cs
 테 이 블 :  
 비    고 :
 예    제 :	
	
********************************************************************/  
 
CREATE PROCEDURE [dbo].[SP_IssueExtra]
	 @gubun					NVARCHAR(50)  = ''	--구분자
	,@s_sdate 				NVARCHAR(10)  = '' 
	,@s_edate 				NVARCHAR(10)  = '' 	
	,@out_date 				NVARCHAR(10)  = '' 	
	,@item_cd 				NVARCHAR(30)  = '' 
	,@out_type 				NVARCHAR(5)  = '' 
	,@gs1_barcode			NVARCHAR(60) = ''	--검증할 바코드
	,@box_type				NVARCHAR(5)  = '' 
	,@cust_cd 				NVARCHAR(30)  = '' 
	,@gtin13 				NVARCHAR(20)  = '' 
	,@lot_no 				NVARCHAR(20)  = '' 
	
	,@insert_user_cd		NVARCHAR(50)  = ''
	,@update_user_cd		NVARCHAR(50)  = ''
	,@log_user_id			NVARCHAR(50)  = '' --AUDIT TRAIL
	,@sys_emp_cd			nvarchar(50) = ''
	,@message				NVARCHAR(2000) output
	,@identity				nvarchar(50)  = '' output
	
AS
	
DECLARE @intErrorCode INT
SET @intErrorCode = 0
 
--AUDITTRAIL용 변수  
DECLARE	@audittrail_id int, @row int
-- [임시 테이블 생성]
create table #AUDIT_TEMP (audittrail_id varchar(10))
 
-- 기타출고 정보 조회
-- [분기] @gubun = 'S' 인 경우
IF (@gubun = 'S') BEGIN
	IF(@box_type = 0)
		BEGIN
			SELECT 
				OUT_DATE 
				,OUT_ITEM_CD AS ITEM_CD
				,B.ITEM_NM AS ITEM_NM
				,dbo.fn_Pack_unit_size(OUT_ITEM_CD)	AS ITEM_PACK_SIZE	-- 포장단위
				,GS1_BARCODE
				,OUT_TYPE
				,OUT_CUST_CD AS CUST_CD
				,C.CUST_NM
			FROM GD_PACKING_RESULT_PACK_OUT_HISTORY A
				LEFT JOIN ITEM_STANDARD B ON A.OUT_ITEM_CD = B.ITEM_CD
				LEFT JOIN MA_CUSTOMER C ON A.OUT_CUST_CD = C.CUST_CD
			WHERE	1=1 		
				AND A.OUT_DATE between @s_sdate and @s_edate				
				AND A.OUT_ITEM_CD like @item_cd
				AND A.OUT_TYPE like @out_type AND A.OUT_TYPE != 5 -- 샘플등록 제외
				AND GS1_BARCODE like @gs1_barcode
				
			ORDER BY 
				OUT_DATE, GS1_BARCODE
		END
	ELSE if (@box_type = 1)
		BEGIN
			SELECT 
				OUT_DATE 
				,OUT_ITEM_CD AS ITEM_CD
				,B.ITEM_NM AS ITEM_NM
				,dbo.fn_Pack_unit_size(OUT_ITEM_CD)	AS ITEM_PACK_SIZE	-- 포장단위
				,GS1_BARCODE
				,OUT_TYPE
				,OUT_CUST_CD AS CUST_CD
				,C.CUST_NM
	  	   FROM GD_PACKING_RESULT_PACK_OUT_HISTORY A
				LEFT JOIN ITEM_STANDARD B ON A.OUT_ITEM_CD = B.ITEM_CD
				LEFT JOIN MA_CUSTOMER C ON A.OUT_CUST_CD = C.CUST_CD
		  WHERE	1=1 		
				AND A.OUT_DATE between @s_sdate and @s_edate				
				AND A.OUT_ITEM_CD like @item_cd
				AND A.OUT_TYPE like @out_type AND A.OUT_TYPE != 5 -- 샘플등록 제외
				AND BOX_BARCODE_NO = (SELECT GS1_BOX_BARCODE_NO FROM GD_PACKING_RESULT_PACK WHERE GS1_BOX_BARCODE_NO = @gs1_barcode)
				AND B.ITEM_CD = @item_cd
			ORDER BY 
				OUT_DATE, GS1_BARCODE
		END
	ELSE if (@box_type = 2)
		BEGIN
			SELECT 
				OUT_DATE 
				,OUT_ITEM_CD AS ITEM_CD
				,B.ITEM_CD AS ITEM_NM
				,dbo.fn_Pack_unit_size(OUT_ITEM_CD)	AS ITEM_PACK_SIZE	-- 포장단위
				,GS1_BARCODE
				,OUT_TYPE
				,OUT_CUST_CD AS CUST_CD
				,C.CUST_NM
			FROM GD_PACKING_RESULT_PACK_OUT_HISTORY A
				LEFT JOIN ITEM_STANDARD B ON A.OUT_ITEM_CD = B.ITEM_CD
				LEFT JOIN MA_CUSTOMER C ON A.OUT_CUST_CD = C.CUST_CD
			WHERE	1=1 		
				AND A.OUT_DATE between @s_sdate and @s_edate				
				AND A.OUT_ITEM_CD like @item_cd
				AND A.OUT_TYPE like @out_type AND A.OUT_TYPE != 5 -- 샘플등록 제외
				AND BOX_BARCODE_NO in (select GS1_BOX_BARCODE_NO from GD_PACKING_RESULT_PACK where MAPPING_GS1_BARCODE = @gs1_barcode)
			ORDER BY 
				OUT_DATE, GS1_BARCODE	
		END	
	ELSE
		BEGIN
			RAISERROR ('에러발생(#101)' , 16, 1);
		END
END
 
-- 기타출고할 바코드 조회(소.중.대박스) [조건 : 입고완료된것]
-- [분기] @gubun = 'S2' 인 경우
IF (@gubun = 'S2') BEGIN
	IF(@box_type = 0)
		BEGIN
			SELECT 
				OUT_DATE = @out_date
				,b.ITEM_CD AS ITEM_CD
				,C.ITEM_NM AS ITEM_NM
				,dbo.fn_Pack_unit_size(B.ITEM_CD)	AS ITEM_PACK_SIZE	-- 포장단위
				,GS1_BOX_BARCODE_NO AS GS1_BARCODE
				,OUT_TYPE = @out_type
				,CUST_CD = @cust_cd
				,D.CUST_NM
			FROM GD_PACKING_RESULT_ITEM A 
				LEFT JOIN GD_ITEM_STOCK B ON A.ITEM_STOCK_ID = B.ITEM_STOCK_ID
				LEFT JOIN ITEM_STANDARD C ON B.ITEM_CD = C.ITEM_CD
				LEFT JOIN MA_CUSTOMER D ON D.CUST_CD = @cust_cd
			WHERE	1=1 		
				AND A.GS1_BOX_BARCODE_NO = @gs1_barcode
			--	AND C.ITEM_PACK_BARCODE = @gtin13
				AND B.RECEIPT_STATUS = 'Y'
				AND A.STOCK_QTY = 1
		END   
	ELSE if (@box_type = 1)
		BEGIN
			SELECT
				OUT_DATE = @out_date
				,B.ITEM_CD AS ITEM_CD
				,C.ITEM_NM
				,dbo.fn_Pack_unit_size(B.ITEM_CD)	AS ITEM_PACK_SIZE	-- 포장단위
				,A.GS1_BOX_BARCODE_NO AS GS1_BARCODE
				,OUT_TYPE = @out_type
				,CUST_CD = @cust_cd
				,D.CUST_NM
			FROM GD_PACKING_RESULT_ITEM A
				LEFT JOIN GD_ITEM_STOCK B ON A.ITEM_STOCK_ID = B.ITEM_STOCK_ID
				LEFT JOIN ITEM_STANDARD C ON B.ITEM_CD = C.ITEM_CD
				LEFT JOIN MA_CUSTOMER D ON D.CUST_CD = @cust_cd
			WHERE	1=1 		
				AND A.MAPPING_GS1_BARCODE = @gs1_barcode
			--	AND C.ITEM_PACK_BARCODE = @gtin13
				AND B.RECEIPT_STATUS = 'Y'
				AND A.STOCK_QTY = 1
		END
	ELSE if (@box_type = 2)
		BEGIN
			SELECT 
				OUT_DATE = @out_date
				,C.ITEM_CD AS ITEM_CD
				,C.ITEM_NM
				,dbo.fn_Pack_unit_size(D.ITEM_CD)	AS ITEM_PACK_SIZE	-- 포장단위
				,A.GS1_BOX_BARCODE_NO AS GS1_BARCODE
				,OUT_TYPE = @out_type
				,CUST_CD = @cust_cd
				,F.CUST_NM
			FROM GD_PACKING_RESULT_ITEM A
				LEFT JOIN GD_PACKING_RESULT_PACK B ON A.MAPPING_GS1_BARCODE = B.GS1_BOX_BARCODE_NO
				LEFT JOIN GD_ITEM_STOCK D ON A.ITEM_STOCK_ID = D.ITEM_STOCK_ID
				LEFT JOIN ITEM_STANDARD C ON D.ITEM_CD = C.ITEM_CD
				LEFT JOIN MA_CUSTOMER F ON F.CUST_CD = @cust_cd
			WHERE	1=1 		
				AND B.MAPPING_GS1_BARCODE = @gs1_barcode
			--	AND C.ITEM_PACK_BARCODE = @gtin13
				AND D.RECEIPT_STATUS = 'Y'
				AND A.STOCK_QTY = 1
		END	
	ELSE
		BEGIN
			RAISERROR ('에러발생(#102)' , 16, 1);
		END
END
 
-- 기타출고 정보 등록
-- [분기] @gubun = 'Insert' 인 경우
IF (@gubun = 'Insert') BEGIN
  BEGIN TRY
	-- [트랜잭션 시작]
	BEGIN TRANSACTION
	
	DECLARE @box_barcode_no nvarchar(20) = ''
	
	SELECT @box_barcode_no = MAPPING_GS1_BARCODE
	FROM GD_PACKING_RESULT_ITEM
	WHERE GS1_BOX_BARCODE_NO = @gs1_barcode
	
	INSERT GD_PACKING_RESULT_PACK_OUT_HISTORY (
		 GS1_BARCODE
		,BOX_BARCODE_NO
		,OUT_ITEM_CD
		,OUT_LOT_NO		
		,OUT_TYPE
		,OUT_QTY
		,OUT_DATE
		,OUT_CUST_CD 
		,INSERT_USER_CD
		,INSERT_TIME
		)
	VALUES (
		@gs1_barcode
		,@box_barcode_no
		,@item_cd
		,@lot_no		
		,@out_type
		,1		
		,@out_date
		,@cust_cd
		,@insert_user_cd
		,GETDATE()		
	)
	--AuditTrail 생성
	select @audittrail_id = audittrail_id from GD_PACKING_RESULT_PACK_OUT_HISTORY where PACKING_RESULT_PACK_OUT_HISTORY_ID = @@IDENTITY
	exec SP_AudiTrail_INSERT_COMMON @tableName = 'GD_PACKING_RESULT_PACK_OUT_HISTORY', @audittrail_id_From_value = @audittrail_id, @audittrail_id_To_value = @audittrail_id, @audit_gubun = 'I', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''	
		
	UPDATE GD_PACKING_RESULT_ITEM
	SET STOCK_QTY = 0
	WHERE GS1_BOX_BARCODE_NO = @gs1_barcode
	
	--AuditTrail 생성
	select @audittrail_id = audittrail_id from GD_PACKING_RESULT_ITEM where GS1_BOX_BARCODE_NO = @gs1_barcode
	exec SP_AudiTrail_INSERT_COMMON @tableName = 'GD_PACKING_RESULT_ITEM', @audittrail_id_From_value = @audittrail_id, @audittrail_id_To_value = @audittrail_id, @audit_gubun = 'U', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''	
	
	UPDATE GD_PACKING_RESULT_PACK
	SET STOCK_QTY = STOCK_QTY - 1
	WHERE GS1_BOX_BARCODE_NO = @box_barcode_no		
	
	--AuditTrail 생성
	select @audittrail_id = audittrail_id from GD_PACKING_RESULT_PACK where GS1_BOX_BARCODE_NO = @box_barcode_no	
	exec SP_AudiTrail_INSERT_COMMON @tableName = 'GD_PACKING_RESULT_PACK', @audittrail_id_From_value = @audittrail_id, @audittrail_id_To_value = @audittrail_id, @audit_gubun = 'U', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''	
		
	-- [트랜잭션 커밋]
	COMMIT TRAN
  END TRY  
  BEGIN CATCH        
		-- [트랜잭션 롤백]
		ROLLBACK TRAN
  END CATCH
END
 
-- 기타출고 정보 삭제
-- [분기] @gubun = 'Delete' 인 경우
IF (@gubun = 'Delete') BEGIN
  BEGIN TRY
	-- [트랜잭션 시작]
	BEGIN TRANSACTION
	
	UPDATE GD_PACKING_RESULT_ITEM set STOCK_QTY = STOCK_QTY + 1 WHERE GS1_BOX_BARCODE_NO = @gs1_barcode
	
	--AuditTrail 생성
	select @audittrail_id = audittrail_id from GD_PACKING_RESULT_ITEM where GS1_BOX_BARCODE_NO = @gs1_barcode	
	exec SP_AudiTrail_INSERT_COMMON @tableName = 'GD_PACKING_RESULT_ITEM', @audittrail_id_From_value = @audittrail_id, @audittrail_id_To_value = @audittrail_id, @audit_gubun = 'U', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''	
		
	UPDATE GD_PACKING_RESULT_PACK 
	set STOCK_QTY = STOCK_QTY + 1 
	WHERE GS1_BOX_BARCODE_NO = (select MAPPING_GS1_BARCODE from GD_PACKING_RESULT_ITEM where GS1_BOX_BARCODE_NO = @gs1_barcode)
	
	--AuditTrail 생성
	select @audittrail_id = audittrail_id from GD_PACKING_RESULT_PACK where GS1_BOX_BARCODE_NO = (select MAPPING_GS1_BARCODE from GD_PACKING_RESULT_ITEM where GS1_BOX_BARCODE_NO = @gs1_barcode)	
	exec SP_AudiTrail_INSERT_COMMON @tableName = 'GD_PACKING_RESULT_PACK', @audittrail_id_From_value = @audittrail_id, @audittrail_id_To_value = @audittrail_id, @audit_gubun = 'U', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''	
	
	
	--AuditTrail 생성
	select @audittrail_id = audittrail_id from GD_PACKING_RESULT_PACK_OUT_HISTORY where GS1_BARCODE = @gs1_barcode 	
	exec SP_AudiTrail_INSERT_COMMON @tableName = 'GD_PACKING_RESULT_PACK_OUT_HISTORY', @audittrail_id_From_value = @audittrail_id, @audittrail_id_To_value = @audittrail_id, @audit_gubun = 'D', @audit_emp_cd = @sys_emp_cd, @audit_Remark = ''	
			
	-- [삭제] GD_PACKING_RESULT_PACK_OUT_HISTORY
	DELETE GD_PACKING_RESULT_PACK_OUT_HISTORY where GS1_BARCODE = @gs1_barcode 
 
	-- [트랜잭션 커밋]
	COMMIT TRAN
  END TRY  
  BEGIN CATCH        
		-- [트랜잭션 롤백]
		ROLLBACK TRAN
  END CATCH
END
 
 
IF (@intErrorCode = 0) BEGIN
	SET @message = '정상적으로 처리되었습니다. '		
END
ELSE BEGIN
	SELECT  @message = DESCRIPTION FROM MASTER.dbo.sysmessages WHERE ERROR =@intErrorCode AND msglangid = '1042' 
END
 
RETURN (@intErrorCode)