20090314
20090624
if not exists(select * from information_schema.columns where TABLE_NAME='sl_acct' and COLUMN_NAME='ds_trans')
BEGIN
ALTER TABLE sl_acct
ADD ds_trans int
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_sale_h' and COLUMN_NAME='amt_okc')
BEGIN
ALTER TABLE sl_sale_h
ADD amt_okc int
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_sale_h' and COLUMN_NAME='pnt_okc_occur')
BEGIN
ALTER TABLE sl_sale_h
ADD pnt_okc_occur int
END
GO


if not exists(select * from information_schema.columns where TABLE_NAME='ms_store' and COLUMN_NAME='card_fee')
BEGIN
ALTER TABLE ms_store
ADD card_fee numeric(3,1)
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='ms_store' and COLUMN_NAME='amt_defready')
BEGIN
ALTER TABLE ms_store
ADD amt_defready int
END
GO


update ms_store set card_fee = 0, amt_defready = 0
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_acct' and COLUMN_NAME='ds_acct')
BEGIN
ALTER TABLE sl_acct
ADD ds_acct varchar(1)
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_acct' and COLUMN_NAME='amt_dc')
BEGIN
ALTER TABLE sl_acct
ADD amt_dc int
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_acct' and COLUMN_NAME='cd_bank')
BEGIN
ALTER TABLE sl_acct
ADD cd_bank varchar(3)
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_acct' and COLUMN_NAME='cd_member')
BEGIN
ALTER TABLE sl_acct
ADD cd_member varchar(10)
END
GO


if not exists(select * from information_schema.columns where TABLE_NAME='sl_acct' and COLUMN_NAME='cd_trdpl')
BEGIN
ALTER TABLE sl_acct
ADD cd_trdpl varchar(6)
END
GO

update sl_acct set ds_acct = '0', amt_dc = 0, cd_bank='', cd_trdpl='', cd_member=''
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_buy_h' and COLUMN_NAME='amt_dc')
BEGIN
ALTER TABLE sl_buy_h
ADD amt_dc int
END
GO

update sl_buy_h set amt_dc = 0
GO

update ms_code set nm_code3 = '0' where cd_kind = '11' 
GO

update ms_code set nm_code3 = '1' where cd_kind = '11' and cd_code = '200'
GO

update ms_code set nm_code3 = '3' where cd_kind = '11' and cd_code = '100'
GO

insert into ms_code(cd_store, cd_kind, cd_code, nm_code1, nm_code2, nm_code3, ds_status)
            select cd_store, '11', dbo.lpad(convert(varchar(3),convert(int, max(cd_code))+1),3,'0'), '현금매입', '1', '2', '0'
              from ms_code
              where cd_kind = '11'
             group by cd_store
GO

if not exists(select * from information_schema.columns where TABLE_NAME='ms_menu' and COLUMN_NAME='yn_okc')
BEGIN
ALTER TABLE ms_menu
ADD yn_okc varchar(1)
END
GO

update ms_menu set yn_okc = 'Y'
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SAVE_SL_ACCT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SAVE_SL_ACCT]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE SAVE_SL_ACCT 
	@CD_STORE	 	VARCHAR(4),
	@YMD_OCCUR	 	VARCHAR(8),
	@CD_ACCT		VARCHAR(3),
	@NO_ACCT		VARCHAR(3) OUTPUT,
	@DS_ACCT		VARCHAR(1),
	@AMT_PAYIN		NUMERIC(11,0),
	@AMT_OUT		NUMERIC(11,0),
	@AMT_DC		NUMERIC(11,0),
	@CD_BANK		VARCHAR(3),
	@CD_TRDPL		VARCHAR(6),
	@CD_MEMBER		VARCHAR(10),
	@NO_POS		VARCHAR(2),
	@REMARK		VARCHAR(70),
	@CD_SAWON_CHG	VARCHAR(6),
	@WORK_KIND		VARCHAR(1)
AS
BEGIN
	IF @WORK_KIND ='I'
	BEGIN
		-- 현금출납부 일련번호 구하기
		SELECT @NO_ACCT =DBO.LPAD(ISNULL(MAX(NO_ACCT),0) + 1, 3, '0')
		  FROM   SL_ACCT
		WHERE CD_STORE    = @CD_STORE
		  AND YMD_OCCUR = @YMD_OCCUR
		
		-- 현금출납부에 입력하기
		INSERT INTO SL_ACCT (
			CD_STORE, YMD_OCCUR, CD_ACCT, NO_ACCT, DS_ACCT,  AMT_PAYIN, AMT_OUT, AMT_DC, NO_POS, CD_BANK, CD_TRDPL, CD_MEMBER,
			REMARK, DT_CHANGE, CD_SAWON_CHG, DS_TRANS)
			VALUES(
			@CD_STORE, @YMD_OCCUR, @CD_ACCT, @NO_ACCT, @DS_ACCT, @AMT_PAYIN, 
			@AMT_OUT-@AMT_DC, @AMT_DC, @NO_POS, @CD_BANK, @CD_TRDPL, @CD_MEMBER, @REMARK, GETDATE(), @CD_SAWON_CHG, 0)
	
	END
	IF @WORK_KIND ='U'
	BEGIN
		--현금출납부 업데이트
		UPDATE SL_ACCT SET	
			AMT_PAYIN 	=  @AMT_PAYIN,
			AMT_OUT    	=  @AMT_OUT-@AMT_DC,
			AMT_DC	=  @AMT_DC,
			CD_BANK	=  @CD_BANK,
			REMARK	= @REMARK,
			DS_TRANS	= 0
		WHERE CD_STORE 	= @CD_STORE
		      AND YMD_OCCUR 	= @YMD_OCCUR
		      AND NO_ACCT	= @NO_ACCT
		
		END
		ELSE IF @WORK_KIND = 'D'
		BEGIN
		-- 현금출납부 삭제
		DELETE FROM SL_ACCT
		WHERE  CD_STORE	= @CD_STORE
		      AND  YMD_OCCUR	= @YMD_OCCUR
		     AND  CD_ACCT	= @CD_ACCT
		     AND	 NO_ACCT	= @NO_ACCT
	END
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


if not exists(select * from information_schema.columns where TABLE_NAME='sl_cashier_mgm' and COLUMN_NAME='amt_okc')
BEGIN
ALTER TABLE sl_cashier_mgm
ADD amt_okc int
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_cashier_mgm' and COLUMN_NAME='_50000')
BEGIN
ALTER TABLE sl_cashier_mgm
ADD _50000 int
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_posclose' and COLUMN_NAME='amt_okc')
BEGIN
ALTER TABLE sl_posclose
ADD amt_okc int
END
GO

update sl_cashier_mgm set amt_okc=0, _50000 = 0
GO

update sl_posclose set amt_okc=0
GO

update sl_sale_h set amt_okc=0, pnt_okc_occur=0
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_day_close' and COLUMN_NAME='amt_loss')
BEGIN
ALTER TABLE sl_day_close
ADD amt_loss int
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_day_close' and COLUMN_NAME='cnt_loss')
BEGIN
ALTER TABLE sl_day_close
ADD cnt_loss int
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_day_close' and COLUMN_NAME='amt_okc')
BEGIN
ALTER TABLE sl_day_close
ADD amt_okc int
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_day_close' and COLUMN_NAME='cnt_okc')
BEGIN
ALTER TABLE sl_day_close
ADD cnt_okc int
END
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SAVE_MS_CODE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SAVE_MS_CODE]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE   PROCEDURE SAVE_MS_CODE
    @cd_store 		varchar(4),
    @cd_kind 		varchar(2),
    @cd_code 		varchar(3),
    @nm_code1 		varchar(80),
    @nm_code2 		varchar(80),
    @nm_code3 		varchar(80),
    @nm_code4 		varchar(80),
    @nm_code5 		varchar(80),
    @nm_code6 		varchar(80),
    @nm_code7 		varchar(80),
    @nm_code8 		varchar(80),
    @nm_code9 		varchar(80),
    @nm_code10 	varchar(80),
    @nm_code11 	varchar(80),
    @nm_code12 	varchar(80),
    @nm_code13 	varchar(80),
    @nm_code14 	varchar(80),
    @nm_code15 	varchar(80),
    @nm_code16 	varchar(80),
    @nm_code17 	varchar(80),
    @nm_code18 	varchar(80),
    @nm_code19 	varchar(80),
    @nm_code20		varchar(80),
    @nm_code21		varchar(80),
    @nm_code22		varchar(80),
    @nm_code23		varchar(80),
    @nm_code24		varchar(80),
    @nm_code25		varchar(80),
    @nm_code26		varchar(80),
    @nm_code27		varchar(80),
    @nm_code28		varchar(80),
    @nm_code29		varchar(80),
    @nm_code30		varchar(80),
    @cd_sawon 		varchar(6),
    @ds_status		varchar(1)
    
AS
BEGIN
	declare @cnt int

	select @cnt= count(*) from ms_code where cd_store = @cd_store and cd_kind = @cd_kind and cd_code = @cd_code
	if @cnt = 0 
	begin
		insert  into ms_code ( 	cd_store, 
					cd_kind, 
					cd_code, 
					nm_code1, 
					nm_code2, 
					nm_code3, 
					nm_code4, 
					nm_code5, 
					nm_code6, 
					nm_code7, 
					nm_code8, 
					nm_code9, 
					nm_code10, 
					nm_code11, 
					nm_code12, 
					nm_code13, 
					nm_code14, 
					nm_code15, 
					nm_code16, 
					nm_code17, 
					nm_code18, 
					nm_code19, 
					nm_code20, 
					nm_code21, 
					nm_code22, 
					nm_code23, 
					nm_code24, 
					nm_code25, 
					nm_code26, 
					nm_code27, 
					nm_code28, 
					nm_code29, 
					nm_code30, 
					ds_status,
					cd_sawon_chg)
  				values (	@cd_store, 
					@cd_kind, 
					@cd_code, 
					@nm_code1, 
					@nm_code2, 
					@nm_code3, 
					@nm_code4, 
					@nm_code5, 
					@nm_code6, 
					@nm_code7, 
					@nm_code8, 
					@nm_code9, 
					@nm_code10, 
					@nm_code11, 
					@nm_code12, 
					@nm_code13, 
					@nm_code14, 
					@nm_code15, 
					@nm_code16, 
					@nm_code17, 
					@nm_code18, 
					@nm_code19, 
					@nm_code20, 
					@nm_code21, 
					@nm_code22, 
					@nm_code23, 
					@nm_code24, 
					@nm_code25, 
					@nm_code26, 
					@nm_code27, 
					@nm_code28, 
					@nm_code29, 
					@nm_code30, 
                                        @ds_status,
					@cd_sawon)
	end
	else
	begin
		update  ms_code set       nm_code1 = @nm_code1, 
					nm_code2 = @nm_code2, 
					nm_code3 = @nm_code3, 
					nm_code4 = @nm_code4, 
					nm_code5 = @nm_code5, 
					nm_code6 = @nm_code6, 
					nm_code7 = @nm_code7, 
					nm_code8 = @nm_code8, 
					nm_code9 = @nm_code9, 
					nm_code10 = @nm_code10, 
					nm_code11 = @nm_code11, 
					nm_code12 = @nm_code12, 
					nm_code13 = @nm_code13, 
					nm_code14 = @nm_code14, 
					nm_code15 = @nm_code15, 
					nm_code16 = @nm_code16, 
					nm_code17 = @nm_code17, 
					nm_code18 = @nm_code18, 
					nm_code19 = @nm_code19, 
					nm_code20 = @nm_code20, 
					nm_code21 = @nm_code21, 
					nm_code22 = @nm_code22, 
					nm_code23 = @nm_code23, 
					nm_code24 = @nm_code24, 
					nm_code25 = @nm_code25, 
					nm_code26 = @nm_code26, 
					nm_code27 = @nm_code27, 
					nm_code28 = @nm_code28, 
					nm_code29 = @nm_code29, 
					nm_code30 = @nm_code30, 
					ds_status    = @ds_status,
					cd_sawon_chg = @cd_sawon
		 where  cd_store = @cd_store and cd_kind = @cd_kind and cd_code = @cd_code
	end


END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if not exists(select * from information_schema.columns where TABLE_NAME='ms_goods' and COLUMN_NAME='yn_okc')
BEGIN
ALTER TABLE ms_goods
ADD yn_okc varchar(1)
END
GO

update ms_goods set yn_okc = 'Y'
GO



if not exists(select * from information_schema.columns where TABLE_NAME='sl_cashbag' and COLUMN_NAME='ds_type')
BEGIN
ALTER TABLE sl_cashbag
ADD ds_type varchar(1)
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_cashbag' and COLUMN_NAME='ds_amt')
BEGIN
ALTER TABLE sl_cashbag
ADD ds_amt varchar(2)
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_cashbag' and COLUMN_NAME='chainpl_fee')
BEGIN
ALTER TABLE sl_cashbag
ADD chainpl_fee int
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POS_SAVE_SALE_CASHBAG]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SAVE_SALE_CASHBAG]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE POS_SAVE_SALE_CASHBAG
    @CD_STORE 		VARCHAR(4),
    @YMD_SALE 		VARCHAR(8),
    @NO_POS 			VARCHAR(2),
    @NO_RCP 			VARCHAR(4),
    @DS_TRD 			VARCHAR(1),
    @DS_TYPE			VARCHAR(1),
    @DS_AMT			VARCHAR(2),
    @NO_CARD 			VARCHAR(20),
    @NO_APPROVAL 		VARCHAR(10),
    @AMT_APPROVAL 		INT,
    @PNT_OCCUR		INT,
    @PNT_AVAIL		INT,
    @PNT_TOTAL		INT,
    @CHAINPL_FEE		INT,
    @TRD_DATE 		VARCHAR(8),
    @TRD_DATE_ORG 		VARCHAR(8),
    @NO_APPROVAL_ORG	VARCHAR(10)
AS
BEGIN

    INSERT  INTO SL_CASHBAG( 
				CD_STORE, 
				YMD_SALE, 
				NO_POS, 
				NO_RCP, 
				DS_TRD, 
				DS_TYPE,
				DS_AMT,
				NO_CARD, 
				NO_APPROVAL, 
				AMT_APPROVAL,
				PNT_OCCUR,
				PNT_AVAIL,
				PNT_TOTAL,
				CHAINPL_FEE,
				TRD_DATE, 
				TRD_DATE_ORG, 
				NO_APPROVAL_ORG
				)
                    VALUES(
				@CD_STORE, 
				@YMD_SALE, 
				@NO_POS, 
				@NO_RCP,  
				@DS_TRD,
				@DS_TYPE,
				@DS_AMT,
				@NO_CARD,
				@NO_APPROVAL, 
				@AMT_APPROVAL,
				@PNT_OCCUR,
				@PNT_AVAIL,
				@PNT_TOTAL,
				@CHAINPL_FEE,
				@TRD_DATE, 
				@TRD_DATE_ORG, 
				@NO_APPROVAL_ORG
			)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SAVE_SL_BUY_H]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SAVE_SL_BUY_H]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

--매입/반품 헤더 저장
CREATE   PROCEDURE SAVE_SL_BUY_H
	 @CD_STORE       		VARCHAR(4),    		-- 점포코드
	 @YMD_BUY 	 		VARCHAR(8),    		-- 매입일자
	 @NO_BUY 	 		VARCHAR(4) OUTPUT,    		-- 영수증번호
              @DS_BUY         		VARCHAR(1) ,   		-- 매입구분 (S : 매입 , B : 반품 )            
	 @AMT_DUTY        		INT,  			-- 공급가액
	 @AMT_TAX 	 		INT,  			-- 부가세
	 @CD_TRDPL 			VARCHAR(6),    		-- 거래처코드                                                           
	 @AMT_CASH  	 		NUMERIC(11,0),	-- 현금                                                                 
	 @AMT_TRUST  	 	NUMERIC(11,0),	-- 외상
	@AMT_DC			NUMERIC(11,0),	-- 할인
	 @CD_SAWON_CHG   		VARCHAR(6),    		-- 변경사원 
	@NO_ACCT_REF		VARCHAR(11),                                                              
              @WORK_KIND     		VARCHAR(1)    		-- 작업구분(N:신규, U:수정)  
AS
BEGIN                                                                                                       
	DECLARE	@CD_ACCT	VARCHAR(3)
	DECLARE	@NO_ACCT	VARCHAR(3)	
             DECLARE	@ACCT_REF	VARCHAR(11)     

	SELECT TOP 1 @CD_ACCT = CD_CODE 
	   FROM MS_CODE
	WHERE CD_STORE	=@CD_STORE
	      AND CD_KIND	='11'
	      AND NM_CODE3	='2'
	      AND DS_STATUS	='0'

	SET @CD_ACCT = ISNULL(@CD_ACCT, '')                                                                                                            
	 
	IF @WORK_KIND = 'I' 
	BEGIN

		SELECT @NO_ACCT = DBO.LPAD(ISNULL(MAX(NO_ACCT),0)+1, 3, '0')
		   FROM SL_ACCT
		 WHERE CD_STORE =@CD_STORE
		       AND YMD_OCCUR =@YMD_BUY                                                                         

		SELECT @NO_BUY = DBO.LPAD(ISNULL(MAX(NO_BUY),0)+1, 4, '0')
		   FROM SL_BUY_H
		 WHERE CD_STORE =@CD_STORE
		       AND YMD_BUY =@YMD_BUY

		IF  (@AMT_CASH <>0) AND (@CD_ACCT <> '')
			SET @NO_ACCT_REF = @YMD_BUY+@NO_ACCT
		ELSE
			SET @NO_ACCT_REF = ''
	
		INSERT INTO SL_BUY_H(
				  CD_STORE,                                                                       
		             	             	   YMD_BUY,                                                                        
	  		             	   NO_BUY,   
				   DS_BUY,
	  	            		   AMT_DUTY,
		  	            	   AMT_TAX,                                                                        
		  	             	   CD_TRDPL,                                                                       
  			                AMT_CASH,                                                                       
	  	             		   AMT_TRUST,       
				   AMT_DC,
		  	             	   CD_SAWON_CHG,
	  		                NO_ACCT_REF )                                                                     
			VALUES  (
	                             	   @CD_STORE,                                                                      
          		           		   @YMD_BUY,                                                                       
          	             			   @NO_BUY,     
				   @DS_BUY,                                                                   
	  		                @AMT_DUTY,                                                                   
		  	             	   @AMT_TAX,                                                                       
		  	                @CD_TRDPL,                                                                      
	  		                @AMT_CASH,                                                                      
		  	             	   @AMT_TRUST,
				   @AMT_DC,
		  	                @CD_SAWON_CHG,                                                                  
				   @NO_ACCT_REF )        
		
		--출납에 저장
		IF (@AMT_CASH <>0) AND (@CD_ACCT <> '')
		BEGIN

			INSERT INTO SL_ACCT(
						CD_STORE,
						YMD_OCCUR,
						NO_ACCT,
						CD_ACCT,
						DS_ACCT,
						AMT_PAYIN,
						AMT_OUT,
						CD_TRDPL,
						CD_SAWON_CHG)
				           VALUES(
						@CD_STORE,
						@YMD_BUY,
						@NO_ACCT,
						@CD_ACCT,
						'0',
						0,
						@AMT_CASH,
						@CD_TRDPL,
						@CD_SAWON_CHG)
		END

	END
	ELSE IF @WORK_KIND = 'U'
	BEGIN
		--현금매입이면서 이전에 출납전표가 없을때
		IF (@AMT_CASH <>0) AND (@CD_ACCT <> '') AND (@NO_ACCT_REF = '')
		BEGIN
		
			SELECT @NO_ACCT_REF = @YMD_BUY + DBO.LPAD(ISNULL(MAX(NO_ACCT),0)+1, 3, '0')
			   FROM SL_ACCT
			 WHERE CD_STORE =@CD_STORE
			       AND YMD_OCCUR =@YMD_BUY
		END
		ELSE IF (@AMT_CASH =0) AND (@NO_ACCT_REF <> '')
		BEGIN
			DELETE SL_ACCT 
			 WHERE CD_STORE 	=@CD_STORE 
			       AND YMD_OCCUR	=SUBSTRING(@NO_ACCT_REF,1,8) 
			       AND NO_ACCT 	=SUBSTRING(@NO_ACCT_REF,9,3)

			SET @NO_ACCT_REF = ''
		END
			

		UPDATE SL_BUY_H SET 	AMT_DUTY 	= @AMT_DUTY,
						AMT_TAX 	= @AMT_TAX, 
						AMT_TRUST 	= @AMT_TRUST, 
						AMT_CASH 	= @AMT_CASH,
						AMT_DC	= @AMT_DC,
						NO_ACCT_REF		= @NO_ACCT_REF
		 WHERE CD_STORE 	= @CD_STORE 
		       AND YMD_BUY 	= @YMD_BUY 
		       AND NO_BUY 	= @NO_BUY

		IF (@AMT_CASH <> 0) AND (@NO_ACCT_REF <> '')
		BEGIN
			UPDATE SL_ACCT SET AMT_OUT 		=@AMT_CASH-@AMT_DC, 
						CD_TRDPL 		=@CD_TRDPL,
						CD_SAWON_CHG	=@CD_SAWON_CHG
			 WHERE CD_STORE 	=@CD_STORE 
			       AND YMD_OCCUR	=SUBSTRING(@NO_ACCT_REF,1,8) 
			       AND NO_ACCT 	=SUBSTRING(@NO_ACCT_REF,9,3)
		END
		ELSE IF (@AMT_CASH <> 0) AND (@NO_ACCT_REF = '')
		BEGIN
	
			INSERT INTO SL_ACCT(
						CD_STORE,
						YMD_OCCUR,
						NO_ACCT,
						CD_ACCT,
						DS_ACCT,
						AMT_PAYIN,
						AMT_OUT,
						CD_TRDPL,
						CD_SAWON_CHG)
				           VALUES(
						@CD_STORE,
						@YMD_BUY,
						@NO_ACCT,
						@CD_ACCT,
						'0',
						0,
						@AMT_CASH,
						@CD_TRDPL,
						@CD_SAWON_CHG)			
		END

	END

 END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SAVE_MS_MENU]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SAVE_MS_MENU]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO







CREATE  PROCEDURE SAVE_MS_MENU

	@CD_STORE 	    		VARCHAR(4),
	@CD_MENU      		VARCHAR(14),
	@NM_MENU		    	VARCHAR(50),
	@NM_MENU_SHORT	    	VARCHAR(50),
	@DS_MENU_TYPE    		VARCHAR(1),
	@DS_GENDER			VARCHAR(1),
	@CD_UPJANG			VARCHAR(4),
	@PR_TIP			INTEGER,
	@PR_SALE     			INTEGER,
	@PR_SALE1			INTEGER,
	@DS_TAX 	    		VARCHAR(1),
	@YN_DC			VARCHAR(1),
	@YN_POINT			VARCHAR(1),
	@YN_OKC			VARCHAR(1),
	@CD_CLASS 	    		VARCHAR(8),
	@YN_USE			VARCHAR(1),
	@DS_STOCK			VARCHAR(1),
	@STOCK_CLASS		VARCHAR(3),
	@NM_UNIT			VARCHAR(30),
	@CD_TRDPL			VARCHAR(6),
	@PR_BUY			INT,
	@QTY_SAFETY		INT,
	@CD_SAWON_CHG   		VARCHAR(6),
	@YN_DAMDANG		VARCHAR(1),
	@I_SW           			CHAR(1)
AS
BEGIN
    DECLARE @CNT INT

    IF @I_SW = 0
    BEGIN
		INSERT INTO  MS_MENU (	CD_STORE, 	 
						CD_MENU,     
						CD_CLASS, 	
						CD_UPJANG, 
						NM_MENU,	
						NM_MENU_SHORT,	 
						DS_MENU_TYPE,
						DS_GENDER,
						PR_TIP,	
						PR_SALE,     
						PR_SALE1,
						DS_TAX, 	
						YN_DC,    
						YN_POINT,
						YN_OKC,
						DS_STOCK,
						STOCK_CLASS,
						NM_UNIT,
						CD_TRDPL,
						PR_BUY,
						QTY_SAFETY,
						YN_USE,		
						CD_SAWON_CHG,
						YN_DAMDANG
						)

                       		 	VALUES(	@CD_STORE, 	 
						@CD_MENU,     
						@CD_CLASS, 	 
						@CD_UPJANG,
						@NM_MENU,		
						@NM_MENU_SHORT, 
						@DS_MENU_TYPE,
						@DS_GENDER,
						@PR_TIP,	
						@PR_SALE,     
						@PR_SALE1,
						@DS_TAX, 	   
						@YN_DC, 
						@YN_POINT,
						@YN_OKC,
						@DS_STOCK,
						@STOCK_CLASS,
						@NM_UNIT,
						@CD_TRDPL,
						@PR_BUY,
						@QTY_SAFETY,
						@YN_USE,		
						@CD_SAWON_CHG,
						@YN_DAMDANG
						)			

    END
    ELSE 
    BEGIN
		UPDATE  MS_MENU     SET 
						CD_CLASS		=@CD_CLASS, 	 
						CD_UPJANG		=@CD_UPJANG,
						NM_MENU		=@NM_MENU, 
						NM_MENU_SHORT        =@NM_MENU_SHORT,						
						DS_MENU_TYPE	=@DS_MENU_TYPE,
						DS_GENDER		=@DS_GENDER,
						PR_TIP		=@PR_TIP,
						PR_SALE		=@PR_SALE,     
						PR_SALE1		=@PR_SALE1,
						DS_TAX 		=@DS_TAX,    
						YN_DC			=@YN_DC,
						YN_POINT		=@YN_POINT,
						YN_OKC		=@YN_OKC,
						DS_STOCK		=@DS_STOCK,
						STOCK_CLASS		=@STOCK_CLASS,
						NM_UNIT		=@NM_UNIT,
						CD_TRDPL		=@CD_TRDPL,
						PR_BUY		=@PR_BUY,
						QTY_SAFETY		=@QTY_SAFETY,
						YN_USE		=@YN_USE,
						CD_SAWON_CHG	=@CD_SAWON_CHG,
						YN_DAMDANG		=@YN_DAMDANG,
						DT_CHANGE          	=GETDATE()
				WHERE    CD_STORE 		= @CD_STORE 
				      AND    CD_MENU 		= @CD_MENU


    END
    

	
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SAVE_SL_DAY_CLOSE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SAVE_SL_DAY_CLOSE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SELECT_DAY_SALE_PR]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SELECT_DAY_SALE_PR]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE          PROCEDURE SAVE_SL_DAY_CLOSE

	@CD_STORE		VARCHAR(4),		--점포코드
	@YMD_CLOSE		VARCHAR(8)	--마감일자
AS
BEGIN	
	
	DECLARE 	@AMT_SALE_TOT	NUMERIC(11,0)		--총매출금액
	DECLARE 	@AMT_SALE		NUMERIC(11,0)		--매출금액
	DECLARE 	@AMT_DC		INT			--할인금액
	DECLARE 	@AMT_TAX		NUMERIC(11,0)		--부가세
	DECLARE 	@AMT_SOON		NUMERIC(11,0)		--순매출
	DECLARE 	@AMT_DUTY		NUMERIC(11,0)		--과세매출
	DECLARE 	@AMT_DUTYFREE	NUMERIC(11,0)		--면세매출
	DECLARE 	@CNT_SALE		INT			--매출건수
	DECLARE	@CNT_PERSON		INT			--고객수
	DECLARE 	@AMT_SALE_DANGA	NUMERIC(11,0)		--객단가(순매출/매출건수)
	DECLARE 	@AMT_GIVE_TOT		NUMERIC(11,0)		--현미수금잔액
	DECLARE 	@AMT_PAY_TOT		NUMERIC(11,0)		--미지급금잔액
	DECLARE 	@AMT_ACCT_IN		NUMERIC(11,0)		--출납(수입금액)
	DECLARE 	@AMT_ACCT_OUT	NUMERIC(11,0)		--출납(지출금액)
	DECLARE 	@AMT_CASH		INT			--현금매출
	DECLARE 	@CNT_CASH		INT			--현금매출
	DECLARE 	@AMT_CASHRCP	INT			--현금매출
	DECLARE 	@CNT_CASHRCP	INT			--현금매출
	DECLARE 	@AMT_CARD		INT			--카드승인(인터넷승인)
	DECLARE 	@CNT_CARD		INT			--카드승인건수(인터넷승인)
	DECLARE 	@AMT_TRUST		INT			--외상매출
	DECLARE 	@CNT_TRUST		INT			--외상매출
	DECLARE 	@AMT_POINT		INT			--포인트매출
	DECLARE 	@CNT_POINT		INT			--포인트매출
	DECLARE 	@AMT_LOSS		INT			--손실매출
	DECLARE 	@CNT_LOSS		INT			--손실매출
	DECLARE 	@AMT_OKC		INT			--OK캐쉬백매출
	DECLARE 	@CNT_OKC		INT			--OK캐쉬백매출
	DECLARE 	@AMT_LACK		INT			--과부족금


	--매출금액
	
	-- 현금매출,수표매출,상품권매출,외상매출,포인트매출,할인금액
	SELECT  @AMT_SALE_TOT = SUM(ISNULL(AMT_SALE+DC_TOT,0)),
		  @AMT_SALE = SUM(ISNULL(AMT_SALE,0)),
		  @AMT_CASH = ISNULL(SUM(ISNULL(AMT_CASH+AMT_CHECK-AMT_CASHRCP,0)),0), 
	               @AMT_CASHRCP = ISNULL(SUM(ISNULL(AMT_CASHRCP,0)),0),
	               @AMT_TRUST = ISNULL(SUM(ISNULL(AMT_TRUST,0)),0),
		  @AMT_POINT = ISNULL(SUM(ISNULL(AMT_POINT,0)),0),
		  @AMT_LOSS = ISNULL(SUM(ISNULL(AMT_LOSS,0)),0),
		  @AMT_OKC = ISNULL(SUM(ISNULL(AMT_OKC,0)),0),
		  @AMT_DC    = ISNULL(SUM(ISNULL(DC_TOT,0)),0)
	   FROM  SL_SALE_H
	WHERE CD_STORE = @CD_STORE
	      AND DS_SALE <> 'V'
	      AND YMD_SALE = @YMD_CLOSE
	

	--순매출금액
	SELECT @AMT_SOON = SUM(ISNULL(AMT_SALE,0))-SUM(ISNULL(AMT_TAX,0)) FROM SL_SALE_H WHERE CD_STORE = @CD_STORE AND DS_SALE <> 'V' AND YMD_SALE = @YMD_CLOSE

	--부가세금액
	SELECT @AMT_TAX = ISNULL(SUM(AMT_TAX),0) FROM SL_SALE_H WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND  DS_SALE <> 'V'

	--과세매출금액
	SELECT @AMT_DUTY= ISNULL(SUM(AMT_DUTY),0)+SUM(ISNULL(AMT_TAX,0))-SUM(ISNULL(AMT_DUTYFREE,0)) FROM SL_SALE_H WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND  DS_SALE <> 'V'
	--부가세금액
	SELECT @AMT_DUTYFREE = ISNULL(SUM(AMT_DUTYFREE),0) FROM SL_SALE_H WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND  DS_SALE <> 'V'

	--매출건수
	SET @CNT_SALE = (SELECT COUNT(*) FROM SL_SALE_H WHERE CD_STORE = @CD_STORE AND DS_SALE <> 'V' AND YMD_SALE = @YMD_CLOSE)

	--고객수
	SELECT @CNT_PERSON = COUNT(*) FROM SL_SALE_H WHERE CD_STORE = @CD_STORE AND DS_SALE <> 'V' AND YMD_SALE = @YMD_CLOSE

	--매출원가
	IF @CNT_SALE = 0 
	BEGIN 	
   		--객단가
		SELECT @AMT_SALE_DANGA =  @AMT_SOON  / 1 
	   	   FROM SL_SALE_H
		WHERE CD_STORE = @CD_STORE AND DS_SALE <> 'V' AND YMD_SALE = @YMD_CLOSE
	END
	ELSE
	BEGIN
   		--객단가
		SELECT @AMT_SALE_DANGA =  @AMT_SOON  / CASE WHEN @CNT_PERSON = 0 THEN 1 ELSE @CNT_PERSON END
	   	   FROM SL_SALE_H
		WHERE CD_STORE = @CD_STORE AND DS_SALE <> 'V' AND YMD_SALE = @YMD_CLOSE
	END	
	
	-- 현금거래건수
	SELECT @CNT_CASH = COUNT(*) FROM SL_SALE_H
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND (AMT_CASH <> 0 OR AMT_CHECK <> 0) AND (ABS(AMT_CASH+AMT_CHECK-AMT_CASHRCP) > 0 ) AND DS_SALE <> 'V'

	SELECT @CNT_CASHRCP = COUNT(*) FROM SL_SALE_H
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND AMT_CASHRCP <> 0 AND DS_SALE <> 'V'

	-- 외상거래건수
	SELECT @CNT_TRUST = COUNT(*) FROM SL_SALE_H
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND AMT_TRUST <> 0 AND DS_SALE <> 'V'

	-- 포인트거래건수
	SELECT @CNT_POINT = COUNT(*) FROM SL_SALE_H
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND AMT_POINT <> 0 AND DS_SALE <> 'V'

	-- 손실거래건수
	SELECT @CNT_LOSS = COUNT(*) FROM SL_SALE_H
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND AMT_LOSS <> 0 AND DS_SALE <> 'V'

	
	-- OK캐쉬백거래건수
	SELECT @CNT_OKC = COUNT(*) FROM SL_SALE_H
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE AND AMT_OKC <> 0 AND DS_SALE <> 'V'

	-- 카드거래건수
	SELECT @CNT_CARD = COUNT(*) FROM SL_CARD
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE 

	--카드승인취소(외부승인)
	SELECT @AMT_CARD =  SUM(CASE WHEN DS_TRD = '1' THEN AMT_APPROVAL ELSE AMT_APPROVAL*-1 END) 
	  FROM SL_CARD
	WHERE CD_STORE = @CD_STORE AND YMD_SALE = @YMD_CLOSE

	--현미수금잔액, 미지급금잔액
	SELECT @AMT_PAY_TOT  = SUM(ISNULL(AMT_TRUST,0))
	   FROM SL_BUY_H 
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_BUY <= @YMD_CLOSE
	      AND NO_ACCT_REF = ''

	SELECT @AMT_GIVE_TOT = SUM(ISNULL(AMT_TRUST,0))
	   FROM SL_SALE_H
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_SALE <= @YMD_CLOSE
	      AND CD_MEMBER 	<> ''
	      AND NO_ACCT_REF = ''
	      AND DS_SALE <> 'V'


	SELECT @AMT_ACCT_IN  	= SUM(ISNULL(AMT_PAYIN,0)),
		 @AMT_ACCT_OUT	= SUM(ISNULL(AMT_OUT,0))
	   FROM SL_ACCT 
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_OCCUR   = @YMD_CLOSE

	
	DELETE SL_DAY_CLOSE WHERE CD_STORE =@CD_STORE AND YMD_CLOSE = @YMD_CLOSE

	INSERT INTO SL_DAY_CLOSE(
					CD_STORE,
					YMD_CLOSE,
					YN_CLOSE,
					AMT_GIVE_TOT,
					AMT_PAY_TOT,
					AMT_ACCT_IN,
					AMT_ACCT_OUT,
					AMT_CASH,
					CNT_CASH,
					AMT_CASHRCP,
					CNT_CASHRCP,
					AMT_CARD,
					CNT_CARD,
					AMT_TRUST,
					CNT_TRUST,
					AMT_POINT,
					CNT_POINT,
					AMT_LOSS,
					CNT_LOSS,
					AMT_OKC,
					CNT_OKC,
					AMT_DC,
					AMT_SALE_TOT,
					AMT_SALE,
					CNT_SALE,
					CNT_PERSON,
					AMT_SALE_DANGA,
					AMT_SOON,
					AMT_DUTY,
					AMT_DUTYFREE,
					AMT_TAX
					)
			VALUES	(
					@CD_STORE,
					@YMD_CLOSE,
					'Y',
					@AMT_GIVE_TOT,
					@AMT_PAY_TOT,
					@AMT_ACCT_IN,
					@AMT_ACCT_OUT,
					@AMT_CASH,
					@CNT_CASH,
					@AMT_CASHRCP,
					@CNT_CASHRCP,
					@AMT_CARD,
					@CNT_CARD,
					@AMT_TRUST,
					@CNT_TRUST,
					@AMT_POINT,
					@CNT_POINT,
					@AMT_LOSS,
					@CNT_LOSS,
					@AMT_OKC,
					@CNT_OKC,
					@AMT_DC,
					@AMT_SALE_TOT,
					@AMT_SALE,
					@CNT_SALE,
					@CNT_PERSON,
					@AMT_SALE_DANGA,
					@AMT_SOON,
					@AMT_DUTY,
					@AMT_DUTYFREE,
					@AMT_TAX
					)
					
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE [dbo].[SELECT_DAY_SALE_PR]
 	@CD_STORE		VARCHAR(4),			--점포코드
	@YMD_CLOSE		VARCHAR(8),			--종료일
	@NM_UPJANG01	VARCHAR(20) OUTPUT,	--유형분류01
	@NM_UPJANG02	VARCHAR(20) OUTPUT,	--유형분류02
	@NM_UPJANG03	VARCHAR(20) OUTPUT,	--유형분류03
	@NM_UPJANG04	VARCHAR(20) OUTPUT,	--유형분류04
	@NM_UPJANG05	VARCHAR(20) OUTPUT,	--유형분류05
	@NM_UPJANG06	VARCHAR(20) OUTPUT,	--유형분류06
	@NM_UPJANG07	VARCHAR(20)OUTPUT		--유형분류07
	
AS
BEGIN
	
	-- 유형별 동적생성--
	DECLARE	@CNT				INT,		--카운터
			@CD_UPJANG			VARCHAR(4),	--유형
			@CD_UPJANG01		VARCHAR(4),	--유형분류01
			@CD_UPJANG02		VARCHAR(4),	--유형분류02
			@CD_UPJANG03		VARCHAR(4),	--유형분류03
			@CD_UPJANG04		VARCHAR(4),	--유형분류04
			@CD_UPJANG05		VARCHAR(4),	--유형분류05
			@CD_UPJANG06		VARCHAR(4),	--유형분류06
			@CD_UPJANG07		VARCHAR(4),	--유형분류07
			@NM_UPJANG			VARCHAR(20)	--유형

	SET NOCOUNT ON
	
	DECLARE  CUR_SALE CURSOR FAST_FORWARD
	FOR 
	  SELECT CD_UPJANG, NM_UPJANG FROM MS_UPJANG
	  WHERE CD_STORE = @CD_STORE
	  ORDER BY CD_UPJANG
	  
	  SET @CNT = 1		--커서초기값

	  OPEN CUR_SALE

	  FETCH NEXT FROM CUR_SALE INTO @CD_UPJANG,@NM_UPJANG
	  WHILE (@@FETCH_STATUS = 0)
	  BEGIN

	    IF @CNT = 1 
	    BEGIN
		SET @CD_UPJANG01 = @CD_UPJANG
		SET @NM_UPJANG01 = @NM_UPJANG
	    END
	    ELSE IF @CNT = 2 
	    BEGIN
		SET @CD_UPJANG02 = @CD_UPJANG
		SET @NM_UPJANG02 = @NM_UPJANG
   	    END
	    ELSE IF @CNT = 3 
	    BEGIN
		SET @CD_UPJANG03 = @CD_UPJANG
		SET @NM_UPJANG03 = @NM_UPJANG
	    END
	    ELSE IF @CNT = 4 
	    BEGIN
		SET @CD_UPJANG04 = @CD_UPJANG
		SET @NM_UPJANG04 = @NM_UPJANG
	    END
	    ELSE IF @CNT = 5
	    BEGIN
		SET @CD_UPJANG05 = @CD_UPJANG
		SET @NM_UPJANG05 = @NM_UPJANG
	    END
	    ELSE IF @CNT = 6 
	    BEGIN
		SET @CD_UPJANG06 = @CD_UPJANG
		SET @NM_UPJANG06 = @NM_UPJANG
	    END
	    ELSE IF @CNT = 7 
	    BEGIN
		SET @CD_UPJANG07 = @CD_UPJANG
		SET @NM_UPJANG07 = @NM_UPJANG
	    END

	    SET @CNT = @CNT + 1
	  FETCH NEXT FROM CUR_SALE INTO @CD_UPJANG,@NM_UPJANG

	  END
	CLOSE CUR_SALE
	DEALLOCATE CUR_SALE
-------------------------------------------------------------------------------------------------------------------	


SELECT 	TOP 1 
		 ISNULL(T1.AMT_SALE,0)
		+ ISNULL(T21.AMT_SALE,0)
		+ ISNULL(T5.AMT_SALE,0)
		+ ISNULL(T8.AMT_KEEP,0)
		+ ISNULL(T8.AMT_RENT,0)	
		+ ISNULL(T1.AMT_DC, 0)		AS AMT_SALE_DAY_TOT,

		 ISNULL(T2.AMT_SALE,0)
		+ ISNULL(T6.AMT_SALE,0)
		+ ISNULL(T22.AMT_SALE,0)
		+ ISNULL(T9.AMT_KEEP,0)
		+ ISNULL(T9.AMT_RENT,0)	
		+ ISNULL(T10.AMT_KEEP,0)
		+ ISNULL(T10.AMT_RENT,0)	
		+ ISNULL(T2.AMT_DC, 0)		AS AMT_SALE_DAY_TOT_M,

		 ISNULL(T1.AMT_SALE,0)
		+ ISNULL(T5.AMT_SALE,0)
		+ ISNULL(T8.AMT_KEEP,0)
		+ ISNULL(T8.AMT_RENT,0)		AS AMT_SALE_DAY,

		 ISNULL(T2.AMT_SALE,0)
		+ ISNULL(T6.AMT_SALE,0)
		+ ISNULL(T9.AMT_KEEP,0)
		+ ISNULL(T9.AMT_RENT,0)		
		+ ISNULL(T10.AMT_KEEP,0)
		+ ISNULL(T10.AMT_RENT,0)		AS AMT_SALE_DAY_M
,
		 ISNULL(T17.AMT_JIK,0)		AS AMT_JIK,		
		 ISNULL(T17.AMT_IM,0)			AS AMT_IM,		
		 ISNULL(T18.AMT_JIK,0)		AS AMT_JIK_M,		
		 ISNULL(T18.AMT_IM,0)			AS AMT_IM_M,		

		 ISNULL(T19.AMT_GAIN,0)		AS AMT_GAIN,		
		 ISNULL(T20.AMT_GAIN,0)		AS AMT_GAIN_T,		
		 ISNULL(T1.AMT_CASH,0)		AS AMT_CASH,
		 ISNULL(T1.CNT_CASH,0)		AS CNT_CASH,
		 ISNULL(T1.AMT_CASHRCP,0)		AS AMT_CASHRCP,
		 ISNULL(T1.CNT_CASHRCP,0)		AS CNT_CASHRCP,
		 ISNULL(T1.AMT_CARD,0)		AS AMT_CARD,
		 ISNULL(T1.CNT_CARD,0)		AS CNT_CARD,
		 ISNULL(T1.AMT_TRUST,0)		AS AMT_TRUST,
		 ISNULL(T1.CNT_TRUST,0)		AS CNT_TRUST,
		 ISNULL(T1.AMT_POINT,0)		AS AMT_POINT,
		 ISNULL(T1.CNT_POINT,0)		AS CNT_POINT,
		 ISNULL(T1.AMT_LOSS,0)		AS AMT_LOSS,
		 ISNULL(T1.CNT_LOSS,0)		AS CNT_LOSS,
		 ISNULL(T1.AMT_OKC,0)		AS AMT_OKC,
		 ISNULL(T1.CNT_OKC,0)		AS CNT_OKC,
		 ISNULL(T1.TOT_AMT,0)		AS TOT_AMT,
		 ISNULL(T1.AMT_ACCT_IN,0)		AS AMT_ACCT_IN,
		 ISNULL(T1.AMT_ACCT_OUT,0)		AS AMT_ACCT_OUT,
		 ISNULL(T1.AMT_ACCT,0)		AS AMT_ACCT,
		 ISNULL(T1.AMT_SALE_TOT,0)		AS AMT_SALE_TOT,
		 ISNULL(T1.AMT_SALE,0)		AS AMT_SALE,
		 ISNULL(T1.AMT_TAX, 0)		AS AMT_TAX,
		 ISNULL(T1.AMT_SOON, 0)		AS AMT_SOON,
		 ISNULL(T1.AMT_DUTY, 0)		AS AMT_DUTY,
		 ISNULL(T1.AMT_DUTYFREE, 0)	AS AMT_DUTYFREE,
		 ISNULL(T1.AMT_DC, 0)			AS AMT_DC,
		 ISNULL(T2.AMT_CASH,0)		AS AMT_CASH_M,
		 ISNULL(T2.CNT_CASH,0)		AS CNT_CASH_M,
		 ISNULL(T2.AMT_CASHRCP,0)		AS AMT_CASHRCP_M,
		 ISNULL(T2.CNT_CASHRCP,0)		AS CNT_CASHRCP_M,
		 ISNULL(T2.AMT_CARD,0)		AS AMT_CARD_M,
		 ISNULL(T2.CNT_CARD,0)		AS CNT_CARD_M,
		 ISNULL(T2.AMT_TRUST,0)		AS AMT_TRUST_M,
		 ISNULL(T2.CNT_TRUST,0)		AS CNT_TRUST_M,
		 ISNULL(T2.AMT_POINT,0)		AS AMT_POINT_M,
		 ISNULL(T2.CNT_POINT,0)		AS CNT_POINT_M,
		 ISNULL(T2.AMT_LOSS,0)		AS AMT_LOSS_M,
		 ISNULL(T2.CNT_LOSS,0)		AS CNT_LOSS_M,
		 ISNULL(T2.AMT_OKC,0)		AS AMT_OKC_M,
		 ISNULL(T2.CNT_OKC,0)		AS CNT_OKC_M,
		 ISNULL(T2.TOT_AMT,0)		AS TOT_AMT_M,
		 ISNULL(T2.AMT_ACCT_IN,0)		AS AMT_ACCT_IN_M,
		 ISNULL(T2.AMT_ACCT_OUT,0)		AS AMT_ACCT_OUT_M,
		 ISNULL(T2.AMT_ACCT,0)		AS AMT_ACCT_M,
		 ISNULL(T2.AMT_SALE_TOT,0)		AS AMT_SALE_TOT_M,
		 ISNULL(T2.AMT_SALE,0)		AS AMT_SALE_M,
		 ISNULL(T2.AMT_TAX, 0)		AS AMT_TAX_M,
		 ISNULL(T2.AMT_SOON, 0)		AS AMT_SOON_M,
		 ISNULL(T2.AMT_DUTY, 0)		AS AMT_DUTY_M,
		 ISNULL(T2.AMT_DUTYFREE, 0)	AS AMT_DUTYFREE_M,
		 ISNULL(T2.AMT_DC, 0)			AS AMT_DC_M,
		 ISNULL(T3.AMT_TOT01,0)		AS AMT_TOT01,
		 ISNULL(T3.AMT_TOT02,0)		AS AMT_TOT02,
		 ISNULL(T3.AMT_TOT03,0)		AS AMT_TOT03,
		 ISNULL(T3.AMT_TOT04,0)		AS AMT_TOT04,
		 ISNULL(T3.AMT_TOT05,0)		AS AMT_TOT05,
		 ISNULL(T3.AMT_TOT06,0)		AS AMT_TOT06,
		 ISNULL(T3.AMT_TOT07,0)		AS AMT_TOT07,
		 ISNULL(T3.AMT_TOT,0)		AS AMT_TOT,
		 ISNULL(T4.AMT_TOT01,0)		AS AMT_TOT01_M,
		 ISNULL(T4.AMT_TOT02,0)		AS AMT_TOT02_M,
		 ISNULL(T4.AMT_TOT03,0)		AS AMT_TOT03_M,
		 ISNULL(T4.AMT_TOT04,0)		AS AMT_TOT04_M,
		 ISNULL(T4.AMT_TOT05,0)		AS AMT_TOT05_M,
		 ISNULL(T4.AMT_TOT06,0)		AS AMT_TOT06_M,
		 ISNULL(T4.AMT_TOT07,0)		AS AMT_TOT07_M,
		 ISNULL(T4.AMT_TOT,0)		AS AMT_TOT_M,
		 ISNULL(T5.QTY_SALE,0)		AS QTY_SALE_MEM,
		 ISNULL(T5.AMT_SALE,0)		AS AMT_SALE_MEM,
		 ISNULL(T5.QTY_REFUND,0)		AS QTY_REFUND_MEM,
		 ISNULL(T5.AMT_REFUND,0)		AS AMT_REFUND_MEM,
		 ISNULL(T5.QTY_SALE,0)
		-ISNULL(T5.QTY_REFUND,0)		AS QTY_SALE_MEM_TOT,
		 ISNULL(T5.AMT_SALE,0)
		-ISNULL(T5.AMT_REFUND,0)		AS AMT_SALE_MEM_TOT,
		 ISNULL(T5.QTY_FINISH,0)		AS QTY_FINISH_MEM,
		 ISNULL(T5.QTY_DELAY,0)		AS QTY_DELAY_MEM,
		 ISNULL(T6.QTY_SALE,0)		AS QTY_SALE_MEM_M,
		 ISNULL(T6.AMT_SALE,0)		AS AMT_SALE_MEM_M,
		 ISNULL(T6.QTY_REFUND,0)		AS QTY_REFUND_MEM_M,
		 ISNULL(T6.AMT_REFUND,0)		AS AMT_REFUND_MEM_M,
		 ISNULL(T6.QTY_SALE,0)
		-ISNULL(T6.QTY_REFUND,0)		AS QTY_SALE_MEM_TOT_M,
		 ISNULL(T6.AMT_SALE,0)
		-ISNULL(T6.AMT_REFUND,0)		AS AMT_SALE_MEM_TOT_M,
		 ISNULL(T6.QTY_FINISH,0)		AS QTY_FINISH_MEM_M,
		 ISNULL(T7.QTY_SALE,0)		AS QTY_SALE_MEM_T,
		 ISNULL(T7.AMT_SALE,0)		AS AMT_SALE_MEM_T,
		 ISNULL(T7.QTY_REFUND,0)		AS QTY_REFUND_MEM_T,
		 ISNULL(T7.AMT_REFUND,0)		AS AMT_REFUND_MEM_T,
		 ISNULL(T7.QTY_SALE,0)
		-ISNULL(T7.QTY_REFUND,0)		AS QTY_SALE_MEM_TOT_T,
		 ISNULL(T7.AMT_SALE,0)
		-ISNULL(T7.AMT_REFUND,0)		AS AMT_SALE_MEM_TOT_T,

		 ISNULL(T7.QTY_FINISH,0)		AS QTY_FINISH_MEM_T,
		 ISNULL(T8.QTY_SALE,0)		AS QTY_SALE_LOCK,
		 ISNULL(T8.AMT_KEEP,0)		AS AMT_KEEP_LOCK,		
		 ISNULL(T8.AMT_RENT,0)		AS AMT_RENT_LOCK,		
		 ISNULL(T8.AMT_KEEP,0)	
		+ ISNULL(T8.AMT_RENT,0)		AS AMT_LOCK_TOT,		
		 ISNULL(T9.QTY_SALE,0)	
		+ ISNULL(T10.QTY_SALE,0)		AS QTY_SALE_LOCK_M,
		 ISNULL(T9.AMT_KEEP,0)		
		+ ISNULL(T10.AMT_KEEP,0)		AS AMT_KEEP_LOCK_M,		
		 ISNULL(T9.AMT_RENT,0)		
		+ ISNULL(T10.AMT_RENT,0)		AS AMT_RENT_LOCK_M,		
		 ISNULL(T9.AMT_KEEP,0)		
		+ ISNULL(T10.AMT_KEEP,0)	
		+ ISNULL(T9.AMT_RENT,0)		
		+ ISNULL(T10.AMT_RENT,0)		AS AMT_LOCK_TOT_M,
		 ISNULL(T11.QTY_SALE,0)		AS QTY_SALE_TICKET,
		 ISNULL(T11.AMT_SALE,0)		AS AMT_SALE_TICKET,	
		 ISNULL(T12.QTY_SALE,0)		AS QTY_SALE_TICKET_M,
		 ISNULL(T12.AMT_SALE,0)		AS AMT_SALE_TICKET_M,		
		 ISNULL(T13.CNT_MAN,0)		AS QTY_MAN,
		 ISNULL(T13.CNT_WOMAN,0)		AS QTY_WOMAN,
		 ISNULL(T13.CNT_MAN,0)
		+ISNULL(T13.CNT_WOMAN,0)		AS QTY_TICKET_TOT,
		 ISNULL(T14.CNT_MAN,0)		AS QTY_MAN_M,
		 ISNULL(T14.CNT_WOMAN,0)		AS QTY_WOMAN_M,
		 ISNULL(T14.CNT_MAN,0)
		+ISNULL(T14.CNT_WOMAN,0)		AS QTY_TICKET_TOT_M,
		 ISNULL(T15.AMT_TRUST,0)		AS AMT_TRUST_TOT,		
		 ISNULL(T16.AMT_BUY,0)		AS AMT_BUY_TOT,
		 ISNULL(T21.CNT_SALE,0)		AS CNT_COUPON,
		 ISNULL(T21.AMT_SALE,0)		AS AMT_COUPON,
		 ISNULL(T22.CNT_SALE,0)		AS CNT_COUPON_M,
		 ISNULL(T22.AMT_SALE,0)		AS AMT_COUPON_M		
FROM
	(
	SELECT
	             ISNULL(AMT_CASH,0) 			AS AMT_CASH,			--현금입금
	             ISNULL(CNT_CASH,0) 			AS CNT_CASH,			--현금입금
	             ISNULL(AMT_CASHRCP,0) 		AS AMT_CASHRCP,		--현금입금
	             ISNULL(CNT_CASHRCP,0) 		AS CNT_CASHRCP,		--현금입금
		ISNULL(AMT_CARD,0)			AS AMT_CARD,			--신용카드
		ISNULL(CNT_CARD,0)			AS CNT_CARD,		--신용단말기
		ISNULL(AMT_TRUST,0)			AS AMT_TRUST,			--외상
		ISNULL(CNT_TRUST,0)			AS CNT_TRUST,			--외상
		ISNULL(AMT_POINT,0)			AS AMT_POINT,			--외상
		ISNULL(CNT_POINT,0)			AS CNT_POINT,			--외상
		ISNULL(AMT_LOSS,0)			AS AMT_LOSS,			--외상
		ISNULL(CNT_LOSS,0)			AS CNT_LOSS,			--외상
		ISNULL(AMT_OKC,0)			AS AMT_OKC,			--외상
		ISNULL(CNT_OKC,0)			AS CNT_OKC,			--외상
     	            ( ISNULL(AMT_CASH , 0)
	            + ISNULL(AMT_CASHRCP, 0) 
	            + ISNULL(AMT_CARD, 0) 
       	            + ISNULL(AMT_TRUST, 0)
       	            + ISNULL(AMT_POINT, 0)
       	            + ISNULL(AMT_LOSS, 0)
	            + ISNULL(AMT_OKC , 0)) 		AS TOT_AMT,			--입금계
       		ISNULL(AMT_ACCT_IN, 0) 		AS AMT_ACCT_IN, 		--입금액 
		ISNULL(AMT_ACCT_OUT, 0	)	AS AMT_ACCT_OUT,		--출금액
		ISNULL(AMT_ACCT, 0)			AS AMT_ACCT,			--현출납액 
		ISNULL(AMT_SALE_TOT, 0)		AS AMT_SALE_TOT,		--매출액
		ISNULL(AMT_TAX, 0)			AS AMT_TAX,
		ISNULL(AMT_SALE, 0)			AS AMT_SALE,			--매출액
		ISNULL(AMT_SOON,0)			AS AMT_SOON,
		ISNULL(AMT_DUTY,0)			AS AMT_DUTY,
		ISNULL(AMT_DUTYFREE, 0)		AS AMT_DUTYFREE,
		ISNULL(AMT_DC,0)			AS AMT_DC

	   FROM SL_DAY_CLOSE
	 WHERE CD_STORE   = @CD_STORE
	      AND YMD_CLOSE = @YMD_CLOSE
	)T1,
	(
	 SELECT 
	             ISNULL(SUM(AMT_CASH),0)		AS AMT_CASH,		--현금입금
	             ISNULL(SUM(CNT_CASH),0)		AS CNT_CASH,		--현금입금
	             ISNULL(SUM(AMT_CASHRCP),0)	AS AMT_CASHRCP,	--현금입금
	             ISNULL(SUM(CNT_CASHRCP),0)	AS CNT_CASHRCP,	--현금입금
		ISNULL(SUM(AMT_CARD),0)		AS AMT_CARD,		--신용카드
	             ISNULL(SUM(CNT_CARD),0)		AS CNT_CARD,		--현금입금
		ISNULL(SUM(AMT_TRUST),0)		AS AMT_TRUST,	--외상
	             ISNULL(SUM(CNT_TRUST),0)		AS CNT_TRUST,		--현금입금
		ISNULL(SUM(AMT_POINT),0)		AS AMT_POINT,	--외상
	             ISNULL(SUM(CNT_POINT),0)		AS CNT_POINT,		--현금입금
		ISNULL(SUM(AMT_LOSS),0)		AS AMT_LOSS,	--외상
	             ISNULL(SUM(CNT_LOSS),0)		AS CNT_LOSS,		--현금입금
		ISNULL(SUM(AMT_OKC),0)		AS AMT_OKC,	--외상
	             ISNULL(SUM(CNT_OKC),0)		AS CNT_OKC,		--현금입금
     	            ( ISNULL(SUM(AMT_CASH) , 0) 
	            + ISNULL(SUM(AMT_CASHRCP), 0) 
	            + ISNULL(SUM(AMT_CARD), 0) 
       	            + ISNULL(SUM(AMT_TRUST), 0)
       	            + ISNULL(SUM(AMT_POINT), 0)
       	            + ISNULL(SUM(AMT_LOSS), 0)
	            + ISNULL(SUM(AMT_OKC) , 0)) 	AS TOT_AMT,		--입금계
       		ISNULL(SUM(AMT_ACCT_IN), 0) 	AS AMT_ACCT_IN, 	--입금액 
		ISNULL(SUM(AMT_ACCT_OUT), 0)	AS AMT_ACCT_OUT,	--출금액
		ISNULL(SUM(AMT_ACCT), 0)		AS AMT_ACCT,		--현출납액 
		ISNULL(SUM(AMT_SALE_TOT), 0)	AS AMT_SALE_TOT,	--매출액
		ISNULL(SUM(AMT_SALE), 0)		AS AMT_SALE,		--매출액
		ISNULL(SUM(AMT_TAX), 0)		AS AMT_TAX,
		ISNULL(SUM(AMT_SOON),0)		AS AMT_SOON,
		ISNULL(SUM(AMT_DUTY),0)		AS AMT_DUTY,
		ISNULL(SUM(AMT_DUTYFREE), 0)	AS AMT_DUTYFREE,
		ISNULL(SUM(AMT_DC), 0)		AS AMT_DC
	    FROM SL_DAY_CLOSE  
	  WHERE CD_STORE = @CD_STORE
	       AND YMD_CLOSE BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00'
	       AND  @YMD_CLOSE
	)T2,
	(
	 SELECT SUM(ISNULL(GU.AMT_TOT01,0))	AS AMT_TOT01,		
		SUM(ISNULL(GU.AMT_TOT02,0))	AS AMT_TOT02,
		SUM(ISNULL(GU.AMT_TOT03,0))	AS AMT_TOT03,
		SUM(ISNULL(GU.AMT_TOT04,0))	AS AMT_TOT04,
		SUM(ISNULL(GU.AMT_TOT05,0))	AS AMT_TOT05,
		SUM(ISNULL(GU.AMT_TOT06,0))	AS AMT_TOT06,
		SUM(ISNULL(GU.AMT_TOT07,0))	AS AMT_TOT07,
		SUM(ISNULL(GU.AMT_TOT,0))		AS AMT_TOT
	   FROM 
		(
		  SELECT 
			 CASE B.CD_UPJANG WHEN @CD_UPJANG01 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT01,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG02 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT02,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG03 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT03,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG04 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT04,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG05 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT05,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG06 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT06,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG07 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT07,
			 SUM(ISNULL(A.AMT_SALE,0) ) AS AMT_TOT
		    FROM SL_SALE_D A, MS_MENU B, SL_SALE_H C
		  WHERE A.CD_STORE	= @CD_STORE
		        AND A.YMD_SALE = @YMD_CLOSE
		        AND A.CD_STORE = B.CD_STORE
		        AND A.CD_MENU	= B.CD_MENU
		        AND A.CD_STORE = C.CD_STORE
                       	        AND A.YMD_SALE = C.YMD_SALE                                                 
                                   AND A.NO_POS   	= C.NO_POS
                                   AND A.NO_RCP   	= C.NO_RCP   
		        AND C.DS_SALE  <> 'V'
		  GROUP BY  B.CD_UPJANG
		)GU
   	)T3,							--유형별
	(
	 SELECT SUM(ISNULL(GU.AMT_TOT01,0))	AS AMT_TOT01,		
		SUM(ISNULL(GU.AMT_TOT02,0))	AS AMT_TOT02,
		SUM(ISNULL(GU.AMT_TOT03,0))	AS AMT_TOT03,
		SUM(ISNULL(GU.AMT_TOT04,0))	AS AMT_TOT04,
		SUM(ISNULL(GU.AMT_TOT05,0))	AS AMT_TOT05,
		SUM(ISNULL(GU.AMT_TOT06,0))	AS AMT_TOT06,
		SUM(ISNULL(GU.AMT_TOT07,0))	AS AMT_TOT07,
		SUM(ISNULL(GU.AMT_TOT,0))		AS AMT_TOT
	   FROM 
		(
		  SELECT
			 CASE B.CD_UPJANG WHEN @CD_UPJANG01 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT01,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG02 THEN SUM(ISNULL(A.AMT_SALE,0) )

			 END AMT_TOT02,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG03 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT03,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG04 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT04,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG05 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT05,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG06 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT06,
			 CASE B.CD_UPJANG WHEN @CD_UPJANG07 THEN SUM(ISNULL(A.AMT_SALE,0) )
			 END AMT_TOT07,
			 SUM( ISNULL(A.AMT_SALE,0)) AS AMT_TOT
		    FROM	 SL_SALE_D A, MS_MENU B, SL_SALE_H C
		  WHERE A.CD_STORE = @CD_STORE
	       	        AND A.YMD_SALE BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
		        AND A.CD_STORE = B.CD_STORE
		        AND A.CD_MENU = B.CD_MENU
		       AND A.CD_STORE = C.CD_STORE
		       AND A.YMD_SALE = C.YMD_SALE                                                 
		       AND A.NO_POS   = C.NO_POS
		       AND A.NO_RCP   = C.NO_RCP   
		       AND C.DS_SALE <> 'V'
		  GROUP BY  B.CD_UPJANG
		)GU
   	) T4,									
	( 					--회원가입현황 ( 일 계 )
	 SELECT A.QTY_SALE,
		  A.AMT_SALE,
		  B.QTY_REFUND,
		  B.AMT_REFUND,
		  C.QTY_FINISH,
		  D.QTY_DELAY
	    FROM 
		(
		 SELECT COUNT(*) 		AS QTY_SALE,
			  SUM(AMT_SALE)	AS AMT_SALE
		    FROM SL_MEMBER_JOIN
	 	 WHERE YMD_SALE	=@YMD_CLOSE
		) A,
		(
		 SELECT COUNT(*) 		AS QTY_REFUND,
			  SUM(AMT_REFUND)	AS AMT_REFUND
		    FROM SL_MEMBER_REFUND
	 	 WHERE YMD_REFUND	=@YMD_CLOSE 
		) B,
		(
		 SELECT COUNT(*)	 AS QTY_FINISH
		    FROM SL_MEMBER_JOIN
	 	 WHERE YMD_FINISH	=@YMD_CLOSE
		       AND DS_STATUS	='2'
		) C,
		(
		 SELECT COUNT(*)	 AS QTY_DELAY
		    FROM SL_MEMBER_DELAY
	 	 WHERE YMD_DELAY	=@YMD_CLOSE
		) D
	) T5,
	( 					--회원가입현황 ( 월 계 )
	 SELECT A.QTY_SALE,
		  A.AMT_SALE,
		  B.QTY_REFUND,
		  B.AMT_REFUND,
		  C.QTY_FINISH
	    FROM 
		(
		 SELECT COUNT(*) 		AS QTY_SALE,
			  SUM(AMT_SALE)	AS AMT_SALE
		    FROM SL_MEMBER_JOIN
	 	 WHERE  YMD_SALE BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
		) A,
		(
		 SELECT COUNT(*) 		AS QTY_REFUND,
			  SUM(AMT_REFUND)	AS AMT_REFUND
		    FROM SL_MEMBER_REFUND
	 	 WHERE YMD_REFUND	BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
		) B,
		(
		 SELECT COUNT(*)	 AS QTY_FINISH
		    FROM SL_MEMBER_JOIN
	 	 WHERE YMD_FINISH	BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
		       AND DS_STATUS	='2'
		) C
	) T6,
	( 					--회원가입현황 ( 월 계 )
	 SELECT A.QTY_SALE,
		  A.AMT_SALE,
		  B.QTY_REFUND,
		  B.AMT_REFUND,
		  C.QTY_FINISH
	    FROM 
		(
		 SELECT COUNT(*) 		AS QTY_SALE,
			  SUM(AMT_SALE)	AS AMT_SALE
		    FROM SL_MEMBER_JOIN
		) A,
		(
		 SELECT COUNT(*) 		AS QTY_REFUND,
			  SUM(AMT_REFUND)	AS AMT_REFUND
		    FROM SL_MEMBER_REFUND
		) B,
		(
		 SELECT COUNT(*)	 AS QTY_FINISH
		    FROM SL_MEMBER_JOIN
		 WHERE  DS_STATUS	='2'
		) C
	) T7,
	(
	 SELECT COUNT(*)		AS QTY_SALE,
		  SUM(AMT_KEEP)	AS AMT_KEEP,
		  SUM(AMT_RENT)	AS AMT_RENT
	    FROM SL_LOCKER
	 WHERE CD_STORE	=@CD_STORE
	       AND YMD_SALE	=@YMD_CLOSE
	) T8,
	(
	 SELECT COUNT(*)		AS QTY_SALE,
		  SUM(AMT_KEEP)	AS AMT_KEEP,
		  SUM(AMT_RENT)	AS AMT_RENT
	    FROM SL_LOCKER
	 WHERE CD_STORE	=@CD_STORE
	       AND YMD_SALE	BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
	) T9,
	(
	 SELECT COUNT(*)		AS QTY_SALE,
		  SUM(AMT_KEEP)	AS AMT_KEEP,
		  SUM(AMT_RENT)	AS AMT_RENT
	    FROM SL_LOCKER_LOG
	 WHERE CD_STORE	=@CD_STORE
	       AND YMD_SALE	BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
	) T10,
	(
	 SELECT SUM(A.QTY_SALE)	AS QTY_SALE,
		  SUM(A.AMT_SALE)	AS AMT_SALE
	    FROM SL_SALE_D A, MS_MENU B, SL_SALE_H C
	  WHERE A.CD_STORE	= @CD_STORE
	        AND A.YMD_SALE = @YMD_CLOSE
	        AND A.CD_STORE = B.CD_STORE
	        AND A.CD_MENU	= B.CD_MENU
	        AND A.CD_STORE = C.CD_STORE
                     AND A.YMD_SALE = C.YMD_SALE                                                 
                     AND A.NO_POS   	= C.NO_POS
                     AND A.NO_RCP   	= C.NO_RCP   
	        AND C.DS_SALE  <> 'V'
	        AND B.DS_MENU_TYPE = '0'
	) T11,
	(
	 SELECT SUM(A.QTY_SALE)	AS QTY_SALE,
		  SUM(A.AMT_SALE)	AS AMT_SALE
	    FROM SL_SALE_D A, MS_MENU B, SL_SALE_H C
	  WHERE A.CD_STORE	= @CD_STORE
	        AND A.CD_STORE = B.CD_STORE
	        AND A.CD_MENU	= B.CD_MENU
	        AND A.CD_STORE = C.CD_STORE
                     AND A.YMD_SALE = C.YMD_SALE                                                 
                     AND A.NO_POS   	= C.NO_POS
                     AND A.NO_RCP   	= C.NO_RCP   
	        AND C.DS_SALE  <> 'V'
	        AND B.DS_MENU_TYPE = '0'
                     AND A.YMD_SALE	BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
	) T12,
	(
	SELECT SUM(A.CNT_MAN) 	AS	CNT_MAN,
		 SUM(A.CNT_WOMAN)	AS 	CNT_WOMAN
	  FROM
		(
		SELECT CASE DS_LOCK WHEN '001' THEN 1   END AS CNT_MAN,
			 CASE DS_LOCK WHEN '002' THEN 1   END AS CNT_WOMAN
		   FROM SL_LOCK_ALLOT
		WHERE CD_STORE  =@CD_STORE
		      AND YMD_SALE  = @YMD_CLOSE
		) A
	) T13,
	(
	SELECT SUM(A.CNT_MAN) 	AS	CNT_MAN,
		 SUM(A.CNT_WOMAN)	AS 	CNT_WOMAN
	  FROM
		(
		SELECT CASE DS_LOCK WHEN '001' THEN 1   END AS CNT_MAN,
			 CASE DS_LOCK WHEN '002' THEN 1   END AS CNT_WOMAN
		   FROM SL_LOCK_ALLOT
		WHERE CD_STORE  =@CD_STORE
		      AND YMD_SALE  BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
		) A
	) T14,
	(
	 SELECT SUM(AMT_TRUST)	AS AMT_TRUST
	    FROM SL_SALE_H 
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_SALE	=@YMD_CLOSE
	      AND DS_SALE	<> 'V'
	      AND NO_ACCT_REF = ''
	) T15,
	(
	 SELECT SUM(AMT_BUY)	AS AMT_BUY
	    FROM SL_BUY_H
	WHERE CD_STORE	=@CD_STORE
	      AND DS_BUY 	= '1'
	      AND NO_ACCT_REF = ''
	) T16,
	(
	 SELECT SUM(A.AMT_JIK)	AS AMT_JIK,
		  SUM(A.AMT_IM)		AS AMT_IM
	    FROM
		(
		 SELECT CASE D.DS_UPJANG WHEN '0'	THEN A.AMT_SALE END AS AMT_JIK,
			  CASE D.DS_UPJANG WHEN '1'          	THEN A.AMT_SALE END AS AMT_IM
		    FROM SL_SALE_D A, MS_MENU B, SL_SALE_H C, MS_UPJANG D
		  WHERE A.CD_STORE	= @CD_STORE
		        AND A.YMD_SALE = @YMD_CLOSE
		        AND A.CD_STORE = B.CD_STORE
		        AND A.CD_MENU	= B.CD_MENU
		        AND A.CD_STORE = C.CD_STORE
	                     AND A.YMD_SALE = C.YMD_SALE                                                 
	                     AND A.NO_POS   	= C.NO_POS
	                     AND A.NO_RCP   	= C.NO_RCP   
		       AND B.CD_STORE = D.CD_STORE
		       AND B.CD_UPJANG = D.CD_UPJANG
		        AND C.DS_SALE  <> 'V'
		) A
	) T17,
	(
	 SELECT SUM(A.AMT_JIK)	AS AMT_JIK,
		  SUM(A.AMT_IM)		AS AMT_IM
	    FROM
		(
		 SELECT CASE D.DS_UPJANG WHEN '0'	THEN A.AMT_SALE END AS AMT_JIK,
			  CASE D.DS_UPJANG WHEN '1'          	THEN A.AMT_SALE END AS AMT_IM
		    FROM SL_SALE_D A, MS_MENU B, SL_SALE_H C, MS_UPJANG D
		  WHERE A.CD_STORE	= @CD_STORE
		        AND A.YMD_SALE BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
		        AND A.CD_STORE = B.CD_STORE
		        AND A.CD_MENU	= B.CD_MENU
		        AND A.CD_STORE = C.CD_STORE
	                     AND A.YMD_SALE = C.YMD_SALE                                                 
	                     AND A.NO_POS   	= C.NO_POS
	                     AND A.NO_RCP   	= C.NO_RCP   
		       AND B.CD_STORE = D.CD_STORE
		       AND B.CD_UPJANG = D.CD_UPJANG
		        AND C.DS_SALE  <> 'V'
		) A
	) T18,
	(
	SELECT
		ISNULL(T.AMT_CASH, 0) * (A.CASH_FEE / 100) 
	           +ISNULL(T.AMT_CARD, 0) * (A.CARD_FEE / 100) AS AMT_GAIN
	   FROM MS_UPJANG A,
		(
		SELECT T.CD_UPJANG,
			 SUM(T.AMT_CASH)	AS AMT_CASH,
			 SUM(T.AMT_CARD)	AS AMT_CARD
		  FROM 
			(
			SELECT T2.CD_UPJANG,
				T1.YMD_SALE,
				T1.NO_POS,
				T1.NO_RCP,
				( ((T1.AMT_SALE-T1.AMT_CARD) / DBO.ISZERO(T1.AMT_SALE) * 100 ) / 100 ) * T2.AMT_SALE AS AMT_CASH,
				T2.AMT_SALE 
				- (( ((T1.AMT_SALE-T1.AMT_CARD) / DBO.ISZERO(T1.AMT_SALE) * 100 ) / 100 ) * T2.AMT_SALE) AS AMT_CARD
			   FROM SL_SALE_H T1,
				(
				SELECT T1.YMD_SALE,
					T1.NO_POS,
					T1.NO_RCP,
					T2.CD_UPJANG,
					SUM(T1.AMT_SALE-T1.DC_TOT) AS AMT_SALE
				   FROM SL_SALE_D T1,
					 MS_MENU T2
				WHERE  T1.CD_STORE	=T2.CD_STORE
					AND T1.CD_MENU 	= T2.CD_MENU
					AND T1.CD_STORE = @CD_STORE
					AND T1.YMD_SALE = @YMD_CLOSE
				GROUP BY T1.YMD_SALE, T1.NO_POS, T1.NO_RCP, T2.CD_UPJANG
				) T2
			WHERE  T1.YMD_SALE      = T2.YMD_SALE
				AND T1.NO_POS    = T2.NO_POS
				AND T1.NO_RCP    = T2.NO_RCP
				AND T1.DS_SALE  <> 'V'
				AND T1.CD_STORE = @CD_STORE
				AND T1.YMD_SALE = @YMD_CLOSE
				AND T2.AMT_SALE <> 0
			) T
		GROUP BY T.CD_UPJANG		
		) T
	WHERE A.CD_STORE =  @CD_STORE
	) T19,
	(
	SELECT
		ISNULL(T.AMT_CASH, 0) * (A.CASH_FEE / 100) 
	           +ISNULL(T.AMT_CARD, 0) * (A.CARD_FEE / 100) AS AMT_GAIN
	   FROM MS_UPJANG A,
		(
		SELECT T.CD_UPJANG,
			 SUM(T.AMT_CASH)	AS AMT_CASH,
			 SUM(T.AMT_CARD)	AS AMT_CARD
		  FROM 
			(
			SELECT T2.CD_UPJANG,
				T1.YMD_SALE,
				T1.NO_POS,
				T1.NO_RCP,
				( ((T1.AMT_SALE-T1.AMT_CARD) / DBO.ISZERO(T1.AMT_SALE) * 100 ) / 100 ) * T2.AMT_SALE AS AMT_CASH,
				T2.AMT_SALE 
				- (( ((T1.AMT_SALE-T1.AMT_CARD) / DBO.ISZERO(T1.AMT_SALE) * 100 ) / 100 ) * T2.AMT_SALE) AS AMT_CARD
			   FROM SL_SALE_H T1,
				(
				SELECT T1.YMD_SALE,
					T1.NO_POS,
					T1.NO_RCP,
					T2.CD_UPJANG,
					SUM(T1.AMT_SALE-T1.DC_TOT) AS AMT_SALE
				   FROM SL_SALE_D T1,
					 MS_MENU T2
				WHERE  T1.CD_STORE	=T2.CD_STORE
					AND T1.CD_MENU 	= T2.CD_MENU
					AND T1.CD_STORE = @CD_STORE
					AND T1.YMD_SALE BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
				GROUP BY T1.YMD_SALE, T1.NO_POS, T1.NO_RCP, T2.CD_UPJANG
				) T2
			WHERE  T1.YMD_SALE      = T2.YMD_SALE
				AND T1.NO_POS    = T2.NO_POS
				AND T1.NO_RCP    = T2.NO_RCP
				AND T1.DS_SALE  <> 'V'
				AND T1.CD_STORE = @CD_STORE
				AND T1.YMD_SALE BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
				AND T2.AMT_SALE <> 0
			) T
		GROUP BY T.CD_UPJANG		
		) T
	WHERE A.CD_STORE =  @CD_STORE
	) T20,
	(
	 SELECT COUNT(*) 		AS CNT_SALE,
                             SUM(AMT_SALE)	AS AMT_SALE
	    FROM MS_COUPON
	 WHERE CD_STORE	=@CD_STORE
	       AND YMD_SALE	=@YMD_CLOSE
	) T21,
	(
	 SELECT COUNT(*) 		AS CNT_SALE,
                             SUM(AMT_SALE)	AS AMT_SALE
	    FROM MS_COUPON
	 WHERE CD_STORE	=@CD_STORE
	       AND YMD_SALE  BETWEEN SUBSTRING(@YMD_CLOSE,1,6) +'00' AND @YMD_CLOSE
	) T22
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SELECT_SALEBYRCP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SELECT_SALEBYRCP]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




/******************************************************************
* goal           : 영수증별 매출내역 조회
* used form   : AF040SM_F
******************************************************************/
CREATE     PROCEDURE [dbo].[SELECT_SALEBYRCP]
 	@CD_STORE		VARCHAR(4),		--점포코드
	@FROM_DATE		VARCHAR(8),		--
	@TO_DATE		VARCHAR(8),		--
	@CNT_SALE		INT OUTPUT,--
	@CNT_REFUND	INT OUTPUT,
	@CNT_CANCEL	INT OUTPUT,
	@AMT_SALE		INT OUTPUT,
	@AMT_CASH		INT OUTPUT,
	@AMT_CARD		INT OUTPUT,
	@AMT_CHECK		INT OUTPUT,
	@AMT_TRUST		INT OUTPUT,
	@AMT_POINT		INT OUTPUT,
	@AMT_LOSS		INT OUTPUT,
	@AMT_OKC		INT OUTPUT
	
AS
BEGIN
	SELECT @CNT_SALE = COUNT(*) FROM SL_SALE_H WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @FROM_DATE AND @TO_DATE AND DS_SALE <> 'V'

	SELECT @CNT_REFUND = COUNT(*) FROM SL_SALE_H WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @FROM_DATE AND @TO_DATE AND DS_SALE = 'B'

	SELECT @CNT_CANCEL = COUNT(*) FROM SL_SALE_H WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @FROM_DATE AND @TO_DATE AND DS_SALE = 'V'

	SELECT @AMT_SALE = ISNULL(SUM(AMT_SALE),0) FROM SL_SALE_H WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @FROM_DATE AND @TO_DATE AND DS_SALE <> 'V'

	SELECT @AMT_CASH = ISNULL(SUM(AMT_CASH),0)  FROM SL_SALE_H WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @FROM_DATE AND @TO_DATE AND DS_SALE <> 'V'
	
	SELECT @AMT_CARD = ISNULL(SUM(AMT_CARD),0)  FROM SL_SALE_H WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @FROM_DATE AND @TO_DATE AND DS_SALE <> 'V'

	SELECT @AMT_CHECK = ISNULL(SUM(AMT_CHECK),0)  FROM SL_SALE_H WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @FROM_DATE AND @TO_DATE AND DS_SALE <> 'V'

	SELECT @AMT_TRUST = ISNULL(SUM(AMT_TRUST),0)  FROM SL_SALE_H WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @FROM_DATE AND @TO_DATE AND DS_SALE <> 'V'

	SELECT @AMT_POINT = ISNULL(SUM(AMT_POINT),0)  FROM SL_SALE_H WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @FROM_DATE AND @TO_DATE AND DS_SALE <> 'V'

	SELECT @AMT_LOSS = ISNULL(SUM(AMT_LOSS),0)  FROM SL_SALE_H WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @FROM_DATE AND @TO_DATE AND DS_SALE <> 'V'

	SELECT @AMT_OKC = ISNULL(SUM(AMT_OKC),0)  FROM SL_SALE_H WHERE CD_STORE =@CD_STORE AND YMD_SALE BETWEEN @FROM_DATE AND @TO_DATE AND DS_SALE <> 'V'

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POS_SAVE_SALE_H]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SAVE_SALE_H]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE  PROCEDURE [dbo].[POS_SAVE_SALE_H]

	@WORK_KIND			VARCHAR(1),
	@CD_STORE 	    		VARCHAR(4),
	@YMD_SALE			VARCHAR(8),
	@NO_POS			VARCHAR(2),
	@NO_RCP			VARCHAR(4),
	@DS_SALE			VARCHAR(1),
	@AMT_SALE			INT,
	@AMT_DUTY			INT,
	@AMT_TAX			INT,
	@AMT_DUTYFREE		INT,
	@AMT_CASH			INT,
	@AMT_CARD			INT,
	@AMT_CHECK			INT,
	@AMT_TRUST			INT,
	@AMT_GIFT			INT,
	@AMT_POINT			INT,
	@AMT_LOSS			INT,
	@AMT_OKC			INT,
	@DC_MENU			INT,
	@DC_RECEIPT			INT,
	@DC_ENURI			INT,
	@DC_CODE			INT,
	@DC_COUPON			INT,
	@CD_CODE			VARCHAR(3),
	@DC_MEMBER			INT,	
	@DC_CUT			INT,
	@DC_VAT			INT,
	@AMT_CASHRCP		INT,
	@DS_LOCK			VARCHAR(3),
	@NO_LOCK			VARCHAR(4),
	@CD_SAWON			VARCHAR(6),	
	@CD_MEMBER			VARCHAR(10),
	@YMD_SALE_ORG		VARCHAR(8),
	@NO_RCP_ORG		VARCHAR(4),
	@CD_MENU			VARCHAR(14),
	@AMT_EXISTPOINT		INT,
	@PNT_OKC_OCCUR		INT,
	@PNT_OCCUR			INT OUT,
	@PNT_USE			INT,
	@PNT_TOTAL			INT OUT
AS
BEGIN
 
	--VOID 또는 입금변경시 사용하기위함
	DECLARE @PNT_OCCUR_ORG 	INT
	DECLARE @PNT_USE_ORG 		INT
	DECLARE @AMT_TRUST_ORG 		INT
	DECLARE @CD_MEMBER_ORG	VARCHAR(10)
	DECLARE @NO_TICKET_REF		VARCHAR(14)

	SELECT @PNT_OCCUR_ORG = PNT_OCCUR,
		  @PNT_USE_ORG	=PNT_USE,
		  @AMT_TRUST_ORG	=AMT_TRUST,
		  @CD_MEMBER_ORG	=CD_MEMBER
	   FROM SL_SALE_H
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_SALE	=@YMD_SALE
	      AND NO_POS	=@NO_POS
	      AND NO_RCP	=@NO_RCP

	/**************************************************************************************************************************************************************************************/
	/*                                               					포인트 적용   시작 								   */
	/**************************************************************************************************************************************************************************************/

	--결제변경일 경우에는 무조건 이전의 적립 및 사용내역을 삭제한다
	IF @WORK_KIND IN('C','V')
	BEGIN

		DELETE 
		   FROM SL_PNT_OCCUR
		WHERE LINK		=@YMD_SALE+@NO_POS+@NO_RCP

		DELETE 
		   FROM SL_PNT_PAY
		WHERE  LINK		=@YMD_SALE+@NO_POS+@NO_RCP	

		UPDATE MS_MEMBER SET 	AMT_SALE = AMT_SALE - ABS(@AMT_SALE)
		 WHERE CD_MEMBER	=@CD_MEMBER

		SELECT @PNT_TOTAL = PNT_TOTAL
		   FROM SL_POINT
		 WHERE CD_MEMBER	=@CD_MEMBER
		
	END
	ELSE SET @PNT_USE = @AMT_POINT

	IF (@CD_MEMBER <> '' AND @WORK_KIND <> 'V'  AND @WORK_KIND <> 'H')
	BEGIN
		DECLARE @CASHAMT_STD		INT
		DECLARE @CARDAMT_STD		INT
		DECLARE @CASHRCPAMT_STD	INT
		DECLARE @CASHPNT_STD		INT
		DECLARE @CARDPNT_STD		INT
		DECLARE @CASHRCPPNT_STD	INT

		DECLARE @CASHAMT		INT
		DECLARE @CARDAMT		INT
		DECLARE @CASHRCPAMT	INT
		DECLARE @PNT_CASH	INT
		DECLARE @PNT_CARD	INT
		DECLARE @PNT_CASHRCP	INT


		--회원이면 회원별로 포인트가 설정되어져있는지 체크한다
		IF @CD_MEMBER <> ''
		BEGIN
			
			SELECT @CASHAMT_STD 	= CONVERT(INT, ISNULL(A.NM_CODE5,'0')),
				  @CASHPNT_STD 	= CONVERT(INT, ISNULL(A.NM_CODE6,'0')),
				  @CARDAMT_STD 	= CONVERT(INT, ISNULL(A.NM_CODE7,'0')),
				  @CARDPNT_STD 	= CONVERT(INT, ISNULL(A.NM_CODE8,'0')),
				  @CASHRCPAMT_STD = CONVERT(INT, ISNULL(A.NM_CODE9,'0')),
				  @CASHRCPPNT_STD = CONVERT(INT, ISNULL(A.NM_CODE10,'0'))
			    FROM MS_CODE A, MS_MEMBER  B
			 WHERE A.CD_KIND    		= '05'
	                                  AND A.CD_CODE   		=B.DS_MEMBER
			       AND A.CD_STORE 		=@CD_STORE
			       AND B.CD_MEMBER	=@CD_MEMBER

			SET @CASHAMT_STD 		= ISNULL(@CASHAMT_STD,0)
			SET @CARDAMT_STD 		= ISNULL(@CARDAMT_STD,0)
			SET @CASHRCPAMT_STD 	= ISNULL(@CASHRCPAMT_STD,0)

			SET @CASHPNT_STD  	= ISNULL(@CASHPNT_STD,0)
			SET @CARDPNT_STD 		= ISNULL(@CARDPNT_STD,0)
			SET @CASHRCPPNT_STD 	= ISNULL(@CASHRCPPNT_STD,0)
		END

		
		IF (@CASHAMT_STD = 0) AND (@CARDAMT_STD = 0) AND (@CASHRCPAMT_STD = 0)
		BEGIN
			SELECT @CASHAMT_STD = CASHAMT_STD,
				  @CASHPNT_STD = CASH_POINT,
				  @CARDAMT_STD = CARDAMT_STD,
				  @CARDPNT_STD = CARD_POINT,
				  @CASHRCPAMT_STD = CASHRCPAMT_STD,
				  @CASHRCPPNT_STD = CASHRCP_POINT
			    FROM MS_STORE
			 WHERE CD_STORE =@CD_STORE

		END

	
		SET @CASHAMT 	=ISNULL(@AMT_CASH,0)+ISNULL(@AMT_CHECK,0)+ISNULL(@AMT_GIFT,0) - ISNULL(@AMT_CASHRCP, 0)
		SET @CARDAMT 	= @AMT_CARD
		SET @CASHRCPAMT 	= @AMT_CASHRCP

		SET @CASHAMT 	=ABS(@CASHAMT)
		SET @CARDAMT 	=ABS(@CARDAMT)
		SET @CASHRCPAMT 	=ABS(@CASHRCPAMT)
		SET @AMT_EXISTPOINT = ABS(@AMT_EXISTPOINT)


		DECLARE	@AMT_APPLY	INT
		DECLARE	@KIND		INT	--0:현금, 1:현금영수증, 2:신용카드
		SET 		@AMT_APPLY = 0
		SET 		@KIND = 0

		IF ((@CASHAMT = 0) AND (@CARDAMT = 0) AND (@CASHRCPAMT = 0) ) OR (@CASHAMT+@CARDAMT+@CASHRCPAMT <= @AMT_EXISTPOINT)
			GOTO BEGIN_RETURN
	
		WHILE @AMT_APPLY< @AMT_EXISTPOINT
		BEGIN
			IF @KIND = 0 
			BEGIN
				IF @CASHAMT >= (@AMT_EXISTPOINT-@AMT_APPLY)
				BEGIN
					SET @CASHAMT 	= @CASHAMT - @AMT_EXISTPOINT
					SET @AMT_APPLY 	= @AMT_EXISTPOINT
				END
				ELSE IF @CASHAMT > 0
				BEGIN
					SET @AMT_APPLY 	=@CASHAMT
					SET @CASHAMT 	=@CASHAMT - (@AMT_EXISTPOINT -  (@AMT_EXISTPOINT- @CASHAMT))
				END
				SET 		@KIND = 1		
			END
			ELSE IF @KIND = 1
			BEGIN
				IF @CASHRCPAMT >= (@AMT_EXISTPOINT-@AMT_APPLY)
				BEGIN
					SET @CASHRCPAMT 	= @CASHRCPAMT - (@AMT_EXISTPOINT-@AMT_APPLY)
					SET @AMT_APPLY 	= @AMT_APPLY + (@AMT_EXISTPOINT-@AMT_APPLY)
				END
				ELSE IF @CASHRCPAMT > 0
				BEGIN
					SET @AMT_APPLY 	=@AMT_APPLY + @CASHRCPAMT
					IF (@AMT_EXISTPOINT>= @AMT_APPLY) 
						SET @CASHRCPAMT 	= 0
					ELSE
						SET @CASHRCPAMT =@AMT_EXISTPOINT-@AMT_APPLY
				END
				SET 		@KIND = 2		
			END
			ELSE IF @KIND = 2
			BEGIN
				IF @CARDAMT >= (@AMT_EXISTPOINT-@AMT_APPLY)
				BEGIN
					SET @CARDAMT 	= @CARDAMT - (@AMT_EXISTPOINT-@AMT_APPLY)
					SET @AMT_APPLY 	= @AMT_APPLY + @AMT_EXISTPOINT-@AMT_APPLY
				END
				ELSE IF @CARDAMT > 0
				BEGIN
					SET @AMT_APPLY 	=@AMT_APPLY + @CARDAMT
					IF (@AMT_EXISTPOINT>= @AMT_APPLY) 
						SET @CARDAMT 	= 0
					ELSE
						SET @CARDAMT 	=@AMT_EXISTPOINT-@AMT_APPLY
				END

			END

		END

BEGIN_RETURN:

		--반품일때는 포인트 제외상품금액에 더한다
		IF @WORK_KIND = 'B'
		BEGIN
			SET @CASHAMT 	=@CASHAMT		*-1
			SET @CARDAMT 	=@CARDAMT		*-1
			SET @CASHRCPAMT 	=@CASHRCPAMT	*-1
			SET @AMT_EXISTPOINT =@AMT_EXISTPOINT *-1
		END


		--현금 포인트
		IF @CASHPNT_STD > 0
			SET	@PNT_CASH = @CASHAMT / @CASHAMT_STD * @CASHPNT_STD
		ELSE
			SET	@PNT_CASH = 0

		--카드 포인트
		IF @CARDPNT_STD > 0
			SET	@PNT_CARD = @CARDAMT / @CARDAMT_STD * @CARDPNT_STD
		ELSE
			SET	@PNT_CARD = 0

		--현금영수증 포인트
		IF @CASHRCPPNT_STD > 0
			SET	@PNT_CASHRCP = @CASHRCPAMT / @CASHRCPAMT_STD * @CASHRCPPNT_STD
		ELSE
			SET	@PNT_CASHRCP = 0

		SET 	@PNT_OCCUR = @PNT_CASH + @PNT_CARD + @PNT_CASHRCP

		--포인트를 사용한 영수증은 포인트를 적립하지 않는다
		IF (@PNT_USE <> 0)
			SET @PNT_OCCUR = 0

		--VOID일때는 발생포인트에 *-1을 곱한다
		IF @WORK_KIND = 'V'
		BEGIN
			SET @PNT_OCCUR = @PNT_OCCUR * -1
		END

		SELECT @PNT_TOTAL = ISNULL(PNT_TOTAL,0) FROM SL_POINT WHERE CD_MEMBER = @CD_MEMBER
	
		SET @PNT_TOTAL	= ISNULL(@PNT_TOTAL, 0)
		SET @PNT_OCCUR 	= ISNULL(@PNT_OCCUR, 0)
		SET @PNT_USE 	= ISNULL(@PNT_USE, 0)

		--총포인트
		SELECT @PNT_TOTAL = @PNT_OCCUR + @PNT_TOTAL - @PNT_USE
	
		SET @CASHAMT =ISNULL(@AMT_CASH,0)+ISNULL(@AMT_CHECK,0)+ISNULL(@AMT_GIFT,0) - ISNULL(@AMT_CASHRCP,0)
		SET @CARDAMT = @AMT_CARD
		SET @CASHRCPAMT = @AMT_CASHRCP

		SET @CASHAMT 	=ABS(@CASHAMT)
		SET @CARDAMT 	=ABS(@CARDAMT)
		SET @CASHRCPAMT 	=ABS(@CASHRCPAMT)
		SET @AMT_EXISTPOINT = ABS(@AMT_EXISTPOINT)	

		IF @WORK_KIND = 'B'
		BEGIN
			SET @CASHAMT 	=@CASHAMT		*-1
			SET @CARDAMT 	=@CARDAMT		*-1
			SET @CASHRCPAMT 	=@CASHRCPAMT	*-1
			SET @AMT_EXISTPOINT =@AMT_EXISTPOINT *-1
		END
	
		DECLARE @PNT_SEQ INT

		--적립에 대해 적용
		IF (@PNT_OCCUR <> 0)
		BEGIN
			SELECT @PNT_SEQ = ISNULL(MAX(SEQ),0) +1
			   FROM SL_PNT_OCCUR
			WHERE YMD_OCCUR = @YMD_SALE
		
			INSERT INTO SL_PNT_OCCUR( 
							CD_MEMBER,
							YMD_OCCUR,
							SEQ,
							CD_CODE,
							AMT_CASH,
							AMT_CARD,
							AMT_CASHRCP,
							PNT_CASH,
							PNT_CARD,

							PNT_CASHRCP,
							AMT_EXIST,
							LINK
							)
					VALUES(
							@CD_MEMBER,
							@YMD_SALE,
							@PNT_SEQ,
							'000',
							@AMT_CASH,
							@AMT_CARD,
							@AMT_CASHRCP,
							@PNT_CASH,
							@PNT_CARD,
							@PNT_CASHRCP,
							@AMT_EXISTPOINT,
							@YMD_SALE+@NO_POS+@NO_RCP	
						)
		END
		ELSE IF @PNT_USE <> 0
		BEGIN
			SELECT @PNT_SEQ = ISNULL(MAX(SEQ),0) +1
			   FROM SL_PNT_PAY
			WHERE  YMD_PAY = @YMD_SALE
		
			INSERT INTO SL_PNT_PAY( 
							CD_MEMBER,
							YMD_PAY,
							SEQ,
							CD_CODE,
							PNT_USE,
							LINK
							)
					VALUES(
							@CD_MEMBER,
							@YMD_SALE,
							@PNT_SEQ,
							'000',
							@PNT_USE,
							@YMD_SALE+@NO_POS+@NO_RCP	
						)
		END


	END

	/**************************************************************************************************************************************************************************************/
	/*                                               					포인트 적용    END 								   */
	/**************************************************************************************************************************************************************************************/

	/**************************************************************************************************************************************************************************************/
	/*                                               					회원 외상 적용  시작 								   */
	/**************************************************************************************************************************************************************************************/



	IF @WORK_KIND IN ('S','B')
	BEGIN
	
		DECLARE @COME_TIME	DATETIME

		IF @WORK_KIND = 'S'
		BEGIN
			SELECT @COME_TIME 	=COME_TIME
			   FROM SL_ORDER_H
			WHERE CD_STORE	=@CD_STORE
			      AND DS_LOCK	=@DS_LOCK
			      AND NO_LOCK	=@NO_LOCK

			SET @COME_TIME = ISNULL(@COME_TIME, GETDATE())
			EXEC SAVE_LOCKER_CANCEL @CD_STORE, @YMD_SALE, @DS_LOCK, @NO_LOCK

			IF (@CD_MEMBER <> '') AND (@CD_MENU <> '')
			BEGIN
				INSERT INTO SL_MEMBER_USE (YMD_SALE, NO_RCP, CD_MEMBER, CD_MENU)
							VALUES   (CONVERT(VARCHAR(8), GETDATE(), 112), @CD_STORE+@YMD_SALE+@NO_POS+@NO_RCP, @CD_MEMBER, @CD_MENU)
			END

			--입장권번호
			SELECT @NO_TICKET_REF = YMD_SALE+NO_POS+NO_RCP 
			   FROM SL_LOCK_ALLOT
			WHERE CD_STORE	=@CD_STORE
			      AND DS_LOCK	=@DS_LOCK
			      AND NO_LOCK	=@NO_LOCK
			      AND OUT_TIME	IS NULL
		END
		--반품이면 회원 이용내역을 삭제한다
		ELSE IF @WORK_KIND = 'B'		
		BEGIN
			SET @COME_TIME = GETDATE()
			IF @CD_MEMBER <> ''
			BEGIN

				DELETE FROM SL_MEMBER_USE WHERE NO_RCP = @CD_STORE+@YMD_SALE+@NO_POS+@NO_RCP
			END
		END

		INSERT INTO SL_SALE_H(
						CD_STORE,
						YMD_SALE,
						NO_POS,
						NO_RCP,
						DS_SALE,
						AMT_SALE,
						AMT_DUTY,
						AMT_TAX,
						AMT_DUTYFREE,
						AMT_CASH,
						AMT_CARD,
						AMT_CHECK,
						AMT_TRUST,
						AMT_GIFT,
						AMT_POINT,
						AMT_LOSS,
						AMT_OKC,
						DC_MENU,
						DC_RECEIPT,
						DC_ENURI,
						DC_CODE,
						CD_CODE,
						DC_MEMBER,
						DC_COUPON,
						DC_CUT,
						DC_VAT,
						AMT_CASHRCP,
						DS_LOCK,
						NO_LOCK,
						COME_TIME,
						DT_SALE,
						CD_SAWON,
						CD_MEMBER,
						NO_RCP_ORG,
						PNT_OKC_OCCUR,
						PNT_OCCUR,
						PNT_USE,
						AMT_EXISTPOINT,
						NO_ACCT_REF,
						NO_TICKET_REF
						)
					VALUES
						(
						@CD_STORE,
						@YMD_SALE,
						@NO_POS,
						@NO_RCP,
						@DS_SALE,
						@AMT_SALE,
						@AMT_DUTY,
						@AMT_TAX,
						@AMT_DUTYFREE,
						@AMT_CASH,
						@AMT_CARD,
						@AMT_CHECK,
						@AMT_TRUST,
						@AMT_GIFT,
						@AMT_POINT,
						@AMT_LOSS,
						@AMT_OKC,
						@DC_MENU,
						@DC_RECEIPT,
						@DC_ENURI,
						@DC_CODE,
						@CD_CODE,
						@DC_MEMBER,
						@DC_COUPON,
						@DC_CUT,
						@DC_VAT,
						@AMT_CASHRCP,
						@DS_LOCK,
						@NO_LOCK,
						@COME_TIME,
						GETDATE(),
						@CD_SAWON,
						@CD_MEMBER,
						@YMD_SALE_ORG +  @NO_POS + @NO_RCP_ORG,
						@PNT_OKC_OCCUR,
						@PNT_OCCUR,
						@PNT_USE,
						@AMT_EXISTPOINT,
						'',
						@NO_TICKET_REF	
						)

						

		IF @CD_MEMBER <> ''
		BEGIN
			UPDATE MS_MEMBER SET 	YMD_VISIT = CONVERT(VARCHAR(8), GETDATE(), 112),
							AMT_SALE = AMT_SALE + @AMT_SALE,
							CNT_VISIT = CNT_VISIT + 1
			 WHERE CD_MEMBER	=@CD_MEMBER
		END


		--쿠폰사용내역 환원
		UPDATE MS_COUPON SET CNT_USE = CNT_USE - T1.QTY_SALE
		  FROM SL_SALE_D T1
		 WHERE MS_COUPON.CD_STORE	= T1.CD_STORE
		   AND MS_COUPON.NO_COUPON+MS_COUPON.NO_SEQ	= T1.NO_COUPON
		   AND T1.CD_STORE			= @CD_STORE
		   AND T1.YMD_SALE			= @YMD_SALE
		   AND T1.NO_POS			= @NO_POS
		   AND T1.NO_RCP			= @NO_RCP
		   AND T1.DS_SALE			= 'C'

		--반품일때
		IF @WORK_KIND = 'B'
		BEGIN
			INSERT INTO SL_SALE_D(
							CD_STORE,
							YMD_SALE,
							NO_POS,
							NO_RCP,
							DS_SALE,
							CD_MENU,
							SEQ,
							QTY_SALE,	
							PR_SALE,
							AMT_SALE,
							DC_MENU,
							DC_RECEIPT,
							DC_MEMBER,
							AMT_VAT,
							DS_TAX
							)
						
					SELECT	CD_STORE,
							@YMD_SALE,
							NO_POS,
							@NO_RCP,
							DS_SALE,
							CD_MENU,
							SEQ,
							QTY_SALE *-1,
							PR_SALE,
							AMT_SALE *-1,
							DC_MENU * -1,
							DC_RECEIPT * -1,
							DC_MEMBER*-1,
							AMT_VAT * -1,
							DS_TAX
			
					FROM		SL_SALE_D
					WHERE	CD_STORE	=@CD_STORE
					      AND 	YMD_SALE	=@YMD_SALE_ORG
					      AND 	NO_POS	=@NO_POS
					      AND 	NO_RCP	=@NO_RCP_ORG

						
		END

					
	END
	--현금영수증만 발행
	ELSE IF @WORK_KIND = 'H'
	BEGIN
		UPDATE SL_SALE_H SET
						PNT_OKC_OCCUR	=@PNT_OKC_OCCUR,
						AMT_CASHRCP	=@AMT_CASHRCP,
						DT_CHANGE  		=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP

	END
	--결제변경
	ELSE IF @WORK_KIND = 'C' 
	BEGIN
		UPDATE SL_SALE_H SET	AMT_SALE		=@AMT_SALE,
						AMT_CASH		=@AMT_CASH,
						AMT_CARD		=@AMT_CARD,
						AMT_CHECK		=@AMT_CHECK,
						AMT_TRUST		=@AMT_TRUST,
						AMT_GIFT		=@AMT_GIFT,
						AMT_POINT		=@AMT_POINT,
						AMT_LOSS		=@AMT_LOSS,
						AMT_OKC		=@AMT_OKC,
						AMT_CASHRCP	=@AMT_CASHRCP,
						DC_MENU		=@DC_MENU,
						CD_MEMBER		=@CD_MEMBER,
						DC_MEMBER		=@DC_MEMBER,
						DC_RECEIPT		=@DC_RECEIPT,
						DC_ENURI		=@DC_ENURI,
						DC_CODE		=@DC_CODE,
						DC_COUPON		=@DC_COUPON,
						DC_CUT		=@DC_CUT,
						DC_VAT		=@DC_VAT,
						CD_SAWON		=@CD_SAWON,
						AMT_DUTY		=@AMT_DUTY,
						AMT_TAX		=@AMT_TAX,
						PNT_OKC_OCCUR	=@PNT_OKC_OCCUR,
						PNT_OCCUR		=@PNT_OCCUR,
						PNT_USE		=@PNT_USE,
						DT_CHANGE  		=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP

		DELETE 
		   FROM SL_SALE_D
		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP

	END
	-- VOID 처리
	ELSE IF @WORK_KIND = 'V'
	BEGIN

		UPDATE SL_SALE_H SET
						DS_SALE	='V',
						DT_CHANGE  	=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP


		--회원이용내역을 삭제한다
		IF @CD_MEMBER <> ''
		BEGIN

			DELETE FROM SL_MEMBER_USE WHERE NO_RCP = @CD_STORE+@YMD_SALE+@NO_POS+@NO_RCP
		END

		--쿠폰사용내역 환원
		UPDATE MS_COUPON SET CNT_USE = CNT_USE - T1.QTY_SALE
		  FROM SL_SALE_D T1
		 WHERE MS_COUPON.CD_STORE	= T1.CD_STORE
		   AND MS_COUPON.NO_COUPON+MS_COUPON.NO_SEQ	= T1.NO_COUPON
		   AND T1.CD_STORE			= @CD_STORE
		   AND T1.YMD_SALE			= @YMD_SALE
		   AND T1.NO_POS			= @NO_POS
		   AND T1.NO_RCP			= @NO_RCP
		   AND T1.DS_SALE			= 'C'
	

	END
	ELSE IF @WORK_KIND = 'P'
	BEGIN
		UPDATE SL_SALE_H SET
						CD_MEMBER=@CD_MEMBER,
						PNT_OCCUR = @PNT_OCCUR,
						DT_CHANGE  	=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP

		IF @CD_MEMBER <> ''
		BEGIN
			UPDATE MS_MEMBER SET 	YMD_VISIT = CONVERT(VARCHAR(8), GETDATE(), 112),
							AMT_SALE = AMT_SALE + @AMT_SALE,
							CNT_VISIT = CNT_VISIT + 1
			 WHERE CD_MEMBER	=@CD_MEMBER
		END

	END
	ELSE IF @WORK_KIND = 'O'
	BEGIN
		UPDATE SL_SALE_H SET
						PNT_OKC_OCCUR = @PNT_OKC_OCCUR,
						DT_CHANGE  	=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP		
	END

	SELECT @PNT_OCCUR = ISNULL(@PNT_OCCUR, 0)
	SELECT @PNT_TOTAL = ISNULL(@PNT_TOTAL, 0)


END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POS_SAVE_SAWONWORK]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SAVE_SAWONWORK]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO









CREATE PROCEDURE POS_SAVE_SAWONWORK

	@CD_STORE	 	VARCHAR(4),
	@CD_SAWON	 	VARCHAR(6),
	@TIME_IN		VARCHAR(8),
	@NO_POS		VARCHAR(2),
	@IP_ADDRESS		VARCHAR(15),
	@WORK_KIND		VARCHAR(1),
	@R_NM_SAWON	VARCHAR(10) 	OUTPUT,
	@R_TIME_IN		VARCHAR(5) 	OUTPUT,
	@R_TIME_OUT	VARCHAR(5) 	OUTPUT,
	@RESULT		INT 		OUTPUT

AS
BEGIN
	IF @WORK_KIND = 'S'
	BEGIN
		DECLARE	@LAST_IN	DATETIME

		SELECT @LAST_IN = TIME_IN
		   FROM SL_SAWONWORK
		WHERE CD_STORE	=@CD_STORE
		      AND CD_SAWON	=@CD_SAWON
		      AND TIME_OUT	IS NULL

		SELECT @R_NM_SAWON = ''

		IF @LAST_IN IS NULL
		BEGIN

			SELECT @R_NM_SAWON 	=B.NM_SAWON,                                                              
				 @R_TIME_IN		=ISNULL(CONVERT(VARCHAR(5), A.TIME_IN, 108), '') ,            
				 @R_TIME_OUT	=ISNULL(CONVERT(VARCHAR(5), A.TIME_OUT, 108), '')
			   FROM SL_SAWONWORK A,  MS_SAWON B
			WHERE A.CD_STORE 	= B.CD_STORE                                                 
			      AND A.CD_SAWON 	= B.CD_SAWON    
			      AND A.CD_STORE	=@CD_STORE
			      AND A.CD_SAWON	=@CD_SAWON
			      AND CONVERT(VARCHAR(8), A.SETTLE_IN, 112) = CONVERT(VARCHAR(8), GETDATE(), 112)

			IF @R_TIME_IN <> ''
				RETURN

			SELECT @R_NM_SAWON 	= NM_SAWON,
				 @R_TIME_IN		= '',
				 @R_TIME_OUT	=''
			   FROM MS_SAWON                        
			WHERE CD_STORE 	=@CD_STORE                                                    
			      AND CD_SAWON	=@CD_SAWON    
		END
		ELSE
		BEGIN
			SELECT @R_NM_SAWON 	=A.NM_SAWON,                                                              
				 @R_TIME_IN		=ISNULL(CONVERT(VARCHAR(5), B.TIME_IN, 108), '') ,            
				 @R_TIME_OUT	=ISNULL(CONVERT(VARCHAR(5), B.TIME_OUT, 108), '')
			   FROM MS_SAWON A, SL_SAWONWORK B                         
			WHERE A.CD_STORE 	*= B.CD_STORE                                                 
			      AND A.CD_SAWON 	*= B.CD_SAWON                                                 
			      AND B.TIME_IN	=@LAST_IN
			      AND A.CD_STORE 	=@CD_STORE                                                    
			      AND A.CD_SAWON	=@CD_SAWON    
		END                                               
	END
	ELSE  IF @WORK_KIND = 'I'
	BEGIN

		INSERT INTO SL_SAWONWORK(	CD_STORE,
							CD_SAWON,
							TIME_IN,
							SETTLE_IN,
							NO_POS,
							IP_ADDRESS
						   )
					VALUES  (	
							@CD_STORE,
							@CD_SAWON,
							GETDATE(),
							GETDATE(),
							@NO_POS,
							@IP_ADDRESS
						   )
					
		IF @@ERROR >0 GOTO ERROR_RETURN

	END
	ELSE IF @WORK_KIND = 'O'
	BEGIN

		DECLARE	@TEMP		INT
		DECLARE 	@TEMP_TIME		DATETIME

		SELECT @TEMP_TIME = MAX(TIME_IN)
		   FROM SL_SAWONWORK
		WHERE CD_STORE	=@CD_STORE
		      AND CD_SAWON	=@CD_SAWON		

		SELECT @TEMP = DATEDIFF(n, TIME_IN, GETDATE() )
		   FROM SL_SAWONWORK
		WHERE CD_STORE	=@CD_STORE
		      AND CD_SAWON	=@CD_SAWON
		      AND TIME_IN	=@TEMP_TIME

		IF @TEMP < 30 GOTO ERROR_RETURN

		UPDATE SL_SAWONWORK SET TIME_OUT	=GETDATE(),
						   SETTLE_OUT	=GETDATE()
		WHERE CD_STORE	=@CD_STORE
		      AND CD_SAWON	=@CD_SAWON
		      AND TIME_IN	=@TEMP_TIME

		IF @@ERROR >0 GOTO ERROR_RETURN

	END


-- 프로시저 정상 완료
	SELECT @RESULT =0
	RETURN

-- 실행도중 에러 발생
ERROR_RETURN:
	SELECT @RESULT = -1
	RETURN
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[worktime_f]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[worktime_f]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



/*******************************************************************

근무시간 계산

Result 주간(2) + 야간(2) + 심야(2)

********************************************************************/



CREATE     FUNCTION [dbo].[worktime_f](

                @start_date    		datetime,

                @end_date		datetime,

   	  @sun_time		varchar(13),

   	  @over_time		varchar(13),

   	  @night_time		varchar(13)

                )
    returns varchar(12)

AS

BEGIN

	declare @sunfrom_time	varchar(5)

	declare @sunto_time	varchar(5)

	declare @overfrom_time	varchar(5)

	declare @overto_time	varchar(5)

	declare @nightfrom_time	varchar(5)

	declare @nightto_time	varchar(5)	



	declare	@resultdata	varchar(12)

	declare @start_time	varchar(5)

	declare @end_time	varchar(5)

	declare @temp	varchar(12)



	declare @result_sun	int

	declare @result_over	int

	declare @result_night	int



	select @temp   = '000000000000'



	select @result_sun   = 0

	select @result_over  = 0

	select @result_night = 0



	select @sunfrom_time = SUBSTRING(@sun_time, 1, 5)

	select @sunto_time     = SUBSTRING(@sun_time, 9, 5)

	select @overfrom_time = SUBSTRING(@over_time, 1, 5)

	select @overto_time     = SUBSTRING(@over_time, 9, 5)

	select @nightfrom_time = SUBSTRING(@night_time, 1, 5)

	select @nightto_time     = SUBSTRING(@night_time, 9, 5)



	if @sunfrom_time > @sunto_time

	begin

		select @sunfrom_time = replace(@sunfrom_time, '24','00')

		select @night_time = replace(@night_time, '24','00')

	end



	if @overfrom_time > @overto_time

	begin

		select @overfrom_time = replace(@overfrom_time, '24','00')

		select @over_time 	= replace(@over_time, '24','00')

	end


	--24:00                      08:00
	if @nightfrom_time > @nightto_time

	begin

		select @nightfrom_time = replace(@nightfrom_time, '24','00')

		select @night_time 	= replace(@night_time, '24','00')

	end



	declare	@term	int

	declare	@i	int

	declare	@tempdate	datetime




	if (convert(varchar(8), @start_date, 112))  =  (convert(varchar(8), @end_date, 112))
		set	@term = DATEDIFF(DAY, @start_date, @end_date) + 1
	else 
		set	@term = DATEDIFF(DAY, @start_date, @end_date)

	set 	@tempdate = @start_date

	set	@i	= 0

	

	WHILE @i < @term

	BEGIN

		--출근과 퇴근일자가 같을때

		if (convert(varchar(10), @tempdate, 112) = convert(varchar(10), @start_date, 112)) and  (convert(varchar(10), @tempdate, 112) = convert(varchar(10), @end_date, 112))

		begin

			select @temp =	 dbo.worktime_sub_f(@tempdate, @end_date, @sun_time, @over_time,	@night_time, 0)

	

			select @result_sun	 = @result_sun + convert(int, substring(@temp, 1,4))

			select @result_over	 = @result_over + convert(int, substring(@temp, 5,4))

			select @result_night = @result_night + convert(int, substring(@temp, 9,4))

		

			

		end

		--출근 일자는 같고 출근일자가 퇴근일자보다 작을때

		else if (convert(varchar(10), @tempdate, 112) = convert(varchar(10), @start_date, 112)) and  (convert(varchar(10), @tempdate, 112) < convert(varchar(10), @end_date, 112))

		begin

			select @temp =	 dbo.worktime_sub_f(@tempdate, @end_date, @sun_time, @over_time,	@night_time, 1)

	

			select @result_sun	 = @result_sun + convert(int, substring(@temp, 1,4))

			select @result_over	 = @result_over + convert(int, substring(@temp, 5,4))

			select @result_night 	= @result_night + convert(int, substring(@temp, 9,4))



		end

		--출근시간이 

		else if (convert(varchar(10), @tempdate, 112) > convert(varchar(10), @start_date, 112)) and  (convert(varchar(10), @tempdate, 112) = convert(varchar(10), @end_date, 112))

		begin



			select @temp =	 dbo.worktime_sub_f(@tempdate, @end_date, @sun_time, @over_time,	@night_time, 2)

	

	

			select @result_sun	 = @result_sun + convert(int, substring(@temp, 1,4))

			select @result_over	 = @result_over + convert(int, substring(@temp, 5,4))

			select @result_night	 = @result_night + convert(int, substring(@temp, 9,4))



		end

		else if (convert(varchar(10), @tempdate, 112) <> convert(varchar(10), @start_date, 112)) and  (convert(varchar(10), @tempdate, 112) <> convert(varchar(10), 
@end_date, 112))

		begin



			select @temp =	 dbo.worktime_sub_f(@tempdate, @end_date, @sun_time, @over_time,	@night_time, 3)

	

	

			select @result_sun	 = @result_sun + convert(int, substring(@temp, 1,4))

			select @result_over	 = @result_over + convert(int, substring(@temp, 5,4))

			select @result_night	 = @result_night + convert(int, substring(@temp, 9,4))



		end



		set	@tempdate = @tempdate + 1

		set 	@i	    = @i + 1



	END



	select @resultdata	= dbo.lpad(@result_sun, 4,'0') + dbo.lpad(@result_over, 4,'0') + dbo.lpad(@result_night, 4,'0')

       	Return @resultdata

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SL_SCHEDULE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SL_SCHEDULE]
GO

CREATE TABLE [dbo].[SL_SCHEDULE] (
	[cd_store] [varchar] (4) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[cd_sawon] [varchar] (6) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[ymd_schedule] [varchar] (8) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[schedule_title1] [varchar] (30) COLLATE Korean_Wansung_CI_AS NULL ,
	[schedule_txt1] [varchar] (300) COLLATE Korean_Wansung_CI_AS NULL ,
	[schedule_title2] [varchar] (30) COLLATE Korean_Wansung_CI_AS NULL ,
	[schedule_txt2] [varchar] (300) COLLATE Korean_Wansung_CI_AS NULL ,
	[schedule_title3] [varchar] (30) COLLATE Korean_Wansung_CI_AS NULL ,
	[schedule_txt3] [varchar] (300) COLLATE Korean_Wansung_CI_AS NULL ,
	[schedule_title4] [varchar] (30) COLLATE Korean_Wansung_CI_AS NULL ,
	[schedule_txt4] [varchar] (300) COLLATE Korean_Wansung_CI_AS NULL ,
	[dt_change] [datetime] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SL_SCHEDULE] ADD 
	CONSTRAINT [DF_SL_SCHEDULE_dt_change] DEFAULT (getdate()) FOR [dt_change],
	CONSTRAINT [PK_SL_SCHEDULE] PRIMARY KEY  CLUSTERED 
	(
		[cd_store],
		[cd_sawon],
		[ymd_schedule]
	)  ON [PRIMARY] 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SELECT_SCHEDULE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SELECT_SCHEDULE]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE  PROCEDURE SELECT_SCHEDULE 

 	@CD_STORE		VARCHAR(4),
	@CD_SAWON		VARCHAR(6),
	@YMD_FROM		VARCHAR(8),
	@YMD_END		VARCHAR(8)
AS
BEGIN


	DROP TABLE DateDummyTable

	CREATE TABLE DateDummyTable
	(
		IDX 		INT IDENTITY
		, DummyDate 	CHAR(10)
		, DW 		INT
	)

	declare	@count 	int
	declare  @i		int
	declare  @ymd_date	smalldatetime

	set @ymd_date = convert(smalldatetime, dbo.stod(@YMD_FROM))

	set @i = 1
--	set @count = DBO.getmonthdays(@YMD_MONTH)

	WHILE @ymd_date <= convert(smalldatetime, dbo.stod(@YMD_END))
	BEGIN
		insert into DateDummyTable ( DummyDate, DW ) values ( convert(varchar(8), @ymd_date, 112), 1) 
		set @ymd_date = dateadd(d, 1, @ymd_date)
	END

	SELECT DummyDate,
		 B.SCHEDULE_TITLE1,
		 B.SCHEDULE_TITLE2,
		 B.SCHEDULE_TITLE3,
		 B.SCHEDULE_TITLE4,
		 (SELECT COUNT(*) FROM MS_MEMBER WHERE CD_STORE =@CD_STORE 
				  	             AND ((YN_LUNAR = 'N' AND SUBSTRING(YMD_BIRTH,5,4) = SUBSTRING(DummyDate,5,4)) OR
						        (YN_LUNAR = 'Y' AND SUBSTRING(dbo.LunarToSolar_F(SUBSTRING(A.DummyDate,1,4) + SUBSTRING(YMD_BIRTH,5,4),0) ,5,4) = SUBSTRING(DummyDate,5,4)))) AS CNT_BIRTH,
		 (SELECT COUNT(*) FROM MS_MEMBER WHERE CD_STORE =@CD_STORE  AND SUBSTRING(YMD_MARRI,5,4) = SUBSTRING(DummyDate,5,4)) AS CNT_MARRI,
		(SELECT SUM(AMT_SALE) FROM SL_SALE_H WHERE CD_STORE =@CD_STORE AND YMD_SALE = DummyDate AND DS_SALE <> 'V') AS AMT_SALE
	   FROM DateDummyTable A, SL_SCHEDULE B
	WHERE A.DummyDate *= B.YMD_SCHEDULE
	      AND B.CD_SAWON =@CD_SAWON
	      AND B.CD_STORE  =@CD_STORE

	    
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SL_ACCT_CARD]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SL_ACCT_CARD]
GO

CREATE TABLE [dbo].[SL_ACCT_CARD] (
	[cd_store] [varchar] (4) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[ymd_sale] [varchar] (8) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[no_pos] [varchar] (2) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[no_acct] [varchar] (4) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[seq] [int] NOT NULL ,
	[ds_trd] [varchar] (1) COLLATE Korean_Wansung_CI_AS NULL ,
	[no_card] [varchar] (20) COLLATE Korean_Wansung_CI_AS NULL ,
	[no_chainpl] [varchar] (15) COLLATE Korean_Wansung_CI_AS NULL ,
	[no_approval] [varchar] (12) COLLATE Korean_Wansung_CI_AS NULL ,
	[no_orgunique] [varchar] (12) COLLATE Korean_Wansung_CI_AS NULL ,
	[amt_approval] [int] NULL ,
	[amt_tip] [int] NULL ,
	[term_halbu] [varchar] (2) COLLATE Korean_Wansung_CI_AS NULL ,
	[term_valid] [varchar] (4) COLLATE Korean_Wansung_CI_AS NULL ,
	[type_trd] [varchar] (1) COLLATE Korean_Wansung_CI_AS NULL ,
	[trd_time] [varchar] (4) COLLATE Korean_Wansung_CI_AS NULL ,
	[trd_date] [varchar] (8) COLLATE Korean_Wansung_CI_AS NULL ,
	[no_approval_org] [varchar] (10) COLLATE Korean_Wansung_CI_AS NULL ,
	[trd_date_org] [varchar] (8) COLLATE Korean_Wansung_CI_AS NULL ,
	[nm_cardpl] [varchar] (20) COLLATE Korean_Wansung_CI_AS NULL ,
	[cd_card_buy] [varchar] (4) COLLATE Korean_Wansung_CI_AS NULL ,
	[nm_card_buy] [varchar] (20) COLLATE Korean_Wansung_CI_AS NULL ,
	[receipt_chk] [varchar] (1) COLLATE Korean_Wansung_CI_AS NULL ,
	[ymd_receipt] [varchar] (8) COLLATE Korean_Wansung_CI_AS NULL ,
	[note_msg] [varchar] (100) COLLATE Korean_Wansung_CI_AS NULL ,
	[realmode] [varchar] (5) COLLATE Korean_Wansung_CI_AS NULL ,
	[imgfile] [varchar] (30) COLLATE Korean_Wansung_CI_AS NULL ,
	[no_van] [int] NULL ,
	[sign_image] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[SL_ACCT_CARD] ADD 
	CONSTRAINT [DF_SL_ACCT_CARD_type_trd] DEFAULT ('S') FOR [type_trd],
	CONSTRAINT [DF_SL_ACCT_CARD_realmode] DEFAULT ('TRUE') FOR [realmode],
	CONSTRAINT [DF_SL_ACCT_CARD_no_van] DEFAULT (0) FOR [no_van],
	CONSTRAINT [PK_SL_ACCT_CARD] PRIMARY KEY  NONCLUSTERED 
	(
		[cd_store],
		[ymd_sale],
		[no_pos],
		[no_acct],
		[seq]
	)  ON [PRIMARY] 
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[zero]', N'[SL_ACCT_CARD].[amt_approval]'
GO

EXEC sp_bindefault N'[dbo].[zero]', N'[SL_ACCT_CARD].[amt_tip]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[cd_card_buy]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[cd_store]'
GO

EXEC sp_bindefault N'[dbo].[''0'']', N'[SL_ACCT_CARD].[ds_trd]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[imgfile]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[nm_card_buy]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[nm_cardpl]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[no_approval]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[no_approval_org]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[no_card]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[no_chainpl]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[no_orgunique]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[note_msg]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[receipt_chk]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[term_halbu]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[term_valid]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[trd_date]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[trd_date_org]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[trd_time]'
GO

EXEC sp_bindefault N'[dbo].[공백]', N'[SL_ACCT_CARD].[ymd_receipt]'
GO

setuser
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SAVE_SL_ACCT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SAVE_SL_ACCT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SAVE_SL_BUY_D]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SAVE_SL_BUY_D]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SAVE_SL_BUY_H]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SAVE_SL_BUY_H]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE SAVE_SL_ACCT 
	@CD_STORE	 	VARCHAR(4),
	@YMD_OCCUR	 	VARCHAR(8),
	@CD_ACCT		VARCHAR(3),
	@NO_ACCT		VARCHAR(3) OUTPUT,
	@DS_ACCT		VARCHAR(1),
	@AMT_PAYIN		NUMERIC(11,0),
	@AMT_OUT		NUMERIC(11,0),
	@AMT_DC		NUMERIC(11,0),
	@CD_BANK		VARCHAR(3),
	@CD_TRDPL		VARCHAR(6),
	@CD_MEMBER		VARCHAR(10),
	@NO_POS		VARCHAR(2),
	@REMARK		VARCHAR(70),
	@CD_SAWON_CHG	VARCHAR(6),
	@WORK_KIND		VARCHAR(1)
AS
BEGIN
	IF @WORK_KIND ='I'
	BEGIN
		-- 현금출납부 일련번호 구하기
		SELECT @NO_ACCT =DBO.LPAD(ISNULL(MAX(NO_ACCT),0) + 1, 3, '0')
		  FROM   SL_ACCT
		WHERE CD_STORE    = @CD_STORE
		  AND YMD_OCCUR = @YMD_OCCUR
		
		-- 현금출납부에 입력하기
		INSERT INTO SL_ACCT (
			CD_STORE, YMD_OCCUR, CD_ACCT, NO_ACCT, DS_ACCT,  AMT_PAYIN, AMT_OUT, AMT_DC, NO_POS, CD_BANK, CD_TRDPL, CD_MEMBER,
			REMARK, DT_CHANGE, CD_SAWON_CHG, DS_TRANS)
			VALUES(
			@CD_STORE, @YMD_OCCUR, @CD_ACCT, @NO_ACCT, @DS_ACCT, @AMT_PAYIN, 
			@AMT_OUT-@AMT_DC, @AMT_DC, @NO_POS, @CD_BANK, @CD_TRDPL, @CD_MEMBER, @REMARK, GETDATE(), @CD_SAWON_CHG, 0)
	
	END
	IF @WORK_KIND ='U'
	BEGIN
		--현금출납부 업데이트
		UPDATE SL_ACCT SET	
			AMT_PAYIN 	=  @AMT_PAYIN,
			AMT_OUT    	=  @AMT_OUT-@AMT_DC,
			AMT_DC	=  @AMT_DC,
			CD_BANK	=  @CD_BANK,
			REMARK	= @REMARK,
			DS_TRANS	= 0
		WHERE CD_STORE 	= @CD_STORE
		      AND YMD_OCCUR 	= @YMD_OCCUR
		      AND NO_ACCT	= @NO_ACCT
		
		END
		ELSE IF @WORK_KIND = 'D'
		BEGIN
		-- 현금출납부 삭제
		DELETE FROM SL_ACCT
		WHERE  CD_STORE	= @CD_STORE
		      AND  YMD_OCCUR	= @YMD_OCCUR
		     AND  CD_ACCT	= @CD_ACCT
		     AND	 NO_ACCT	= @NO_ACCT
	END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO










/****************************************************************************************/
/*                                            매입상세 저장			        */
/*     작성자 : 김환준  (2007.03.26)                                	 	        */
/****************************************************************************************/
CREATE       PROCEDURE SAVE_SL_BUY_D
(
         @CD_STORE      	VARCHAR(4) ,      	-- 점포코드                                     
         @YMD_BUY       	VARCHAR(8) ,      	-- 매입일자                                   
         @NO_BUY        	VARCHAR(4) ,      	-- 영수증번호                                 
         @CD_GOODS       	VARCHAR(20),		-- 상품코드                    
         @QTY_BUY      		INT,    	 	-- 매입수량                                   
         @PR_BUY        	INT,    			-- 매입단가                                   
         @AMT_DUTY    		INT,    		-- 공급가액                                 
         @AMT_TAX     		INT,    		-- 부가세                                     
         @WORK_KIND    		VARCHAR(1)    	-- INSERT OR UPDATE OR DELETE 구분하는 값. 
)
AS 

BEGIN

	IF @WORK_KIND = 'I'
	BEGIN
		INSERT INTO SL_BUY_D
				(CD_STORE,
				YMD_BUY,
				NO_BUY,
				CD_GOODS,
				QTY_BUY,
				PR_BUY,
				AMT_DUTY,
				AMT_TAX )
			VALUES 
				(@CD_STORE,                     -- 점코드                                    
				@YMD_BUY,                       -- 매입일자                                  
				@NO_BUY,                        -- 영수증번호                 
				@CD_GOODS,                      -- 상품코드(판매기준)                        
				@QTY_BUY,                   	-- 매입수량                                  
				@PR_BUY,                        -- 매입단가                                  
				@AMT_DUTY,                   	-- 영세율금액                                
				@AMT_TAX)                       -- 부가세  

	END
	ELSE IF @WORK_KIND = 'U'
	BEGIN
		UPDATE SL_BUY_D SET  
			             			CD_GOODS   	= @CD_GOODS,
			             			QTY_BUY	= @QTY_BUY,
			             			PR_BUY	= @PR_BUY,
			             			AMT_DUTY   	= @AMT_DUTY,
			             			AMT_TAX	= @AMT_TAX 
    		WHERE    CD_STORE      	= @CD_STORE 
     		      AND    YMD_BUY 		= @YMD_BUY 
      		      AND    NO_BUY	   	= @NO_BUY 
      		      AND    CD_GOODS   	= @CD_GOODS

	END
	ELSE IF @WORK_KIND = 'D'
	BEGIN
	
		DELETE SL_BUY_D WHERE CD_STORE =@CD_STORE AND YMD_BUY =@YMD_BUY AND NO_BUY =@NO_BUY AND CD_GOODS =@CD_GOODS


	END

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

--매입/반품 헤더 저장
CREATE   PROCEDURE SAVE_SL_BUY_H
	 @CD_STORE       		VARCHAR(4),    		-- 점포코드
	 @YMD_BUY 	 		VARCHAR(8),    		-- 매입일자
	 @NO_BUY 	 		VARCHAR(4) OUTPUT,    		-- 영수증번호
              @DS_BUY         		VARCHAR(1) ,   		-- 매입구분 (S : 매입 , B : 반품 )            
	 @AMT_DUTY        		INT,  			-- 공급가액
	 @AMT_TAX 	 		INT,  			-- 부가세
	 @CD_TRDPL 			VARCHAR(6),    		-- 거래처코드                                                           
	 @AMT_CASH  	 		NUMERIC(11,0),	-- 현금                                                                 
	 @AMT_TRUST  	 	NUMERIC(11,0),	-- 외상
	@AMT_DC			NUMERIC(11,0),	-- 할인
	 @CD_SAWON_CHG   		VARCHAR(6),    		-- 변경사원 
	@NO_ACCT_REF		VARCHAR(11),                                                              
              @WORK_KIND     		VARCHAR(1)    		-- 작업구분(N:신규, U:수정)  
AS
BEGIN                                                                                                       
	DECLARE	@CD_ACCT	VARCHAR(3)
	DECLARE	@NO_ACCT	VARCHAR(3)	
             DECLARE	@ACCT_REF	VARCHAR(11)     

	SELECT TOP 1 @CD_ACCT = CD_CODE 
	   FROM MS_CODE
	WHERE CD_STORE	=@CD_STORE
	      AND CD_KIND	='11'
	      AND NM_CODE3	='2'
	      AND DS_STATUS	='0'

	SET @CD_ACCT = ISNULL(@CD_ACCT, '')                                                                                                            
	 
	IF @WORK_KIND = 'I' 
	BEGIN

		SELECT @NO_ACCT = DBO.LPAD(ISNULL(MAX(NO_ACCT),0)+1, 3, '0')
		   FROM SL_ACCT
		 WHERE CD_STORE =@CD_STORE
		       AND YMD_OCCUR =@YMD_BUY                                                                         

		SELECT @NO_BUY = DBO.LPAD(ISNULL(MAX(NO_BUY),0)+1, 4, '0')
		   FROM SL_BUY_H
		 WHERE CD_STORE =@CD_STORE
		       AND YMD_BUY =@YMD_BUY

		IF  (@AMT_CASH <>0) AND (@CD_ACCT <> '')
			SET @NO_ACCT_REF = @YMD_BUY+@NO_ACCT
		ELSE
			SET @NO_ACCT_REF = ''
	
		INSERT INTO SL_BUY_H(
				  CD_STORE,                                                                       
		             	             	   YMD_BUY,                                                                        
	  		             	   NO_BUY,   
				   DS_BUY,
	  	            		   AMT_DUTY,
		  	            	   AMT_TAX,                                                                        
		  	             	   CD_TRDPL,                                                                       
  			                AMT_CASH,                                                                       
	  	             		   AMT_TRUST,       
				   AMT_DC,
		  	             	   CD_SAWON_CHG,
	  		                NO_ACCT_REF )                                                                     
			VALUES  (
	                             	   @CD_STORE,                                                                      
          		           		   @YMD_BUY,                                                                       
          	             			   @NO_BUY,     
				   @DS_BUY,                                                                   
	  		                @AMT_DUTY,                                                                   
		  	             	   @AMT_TAX,                                                                       
		  	                @CD_TRDPL,                                                                      
	  		                @AMT_CASH,                                                                      
		  	             	   @AMT_TRUST,
				   @AMT_DC,
		  	                @CD_SAWON_CHG,                                                                  
				   @NO_ACCT_REF )        
		
		--출납에 저장
		IF (@AMT_CASH <>0) AND (@CD_ACCT <> '')
		BEGIN

			INSERT INTO SL_ACCT(
						CD_STORE,
						YMD_OCCUR,
						NO_ACCT,
						CD_ACCT,
						DS_ACCT,
						AMT_PAYIN,
						AMT_OUT,
						CD_TRDPL,
						NO_POS,
						CD_SAWON_CHG)
				           VALUES(
						@CD_STORE,
						@YMD_BUY,
						@NO_ACCT,
						@CD_ACCT,
						'0',
						0,
						@AMT_CASH,
						@CD_TRDPL,
						'00',
						@CD_SAWON_CHG)
		END

	END
	ELSE IF @WORK_KIND = 'U'
	BEGIN
		--현금매입이면서 이전에 출납전표가 없을때
		IF (@AMT_CASH <>0) AND (@CD_ACCT <> '') AND (@NO_ACCT_REF = '')
		BEGIN
		
			SELECT @NO_ACCT_REF = @YMD_BUY + DBO.LPAD(ISNULL(MAX(NO_ACCT),0)+1, 3, '0')
			   FROM SL_ACCT
			 WHERE CD_STORE =@CD_STORE
			       AND YMD_OCCUR =@YMD_BUY
		END
		ELSE IF (@AMT_CASH =0) AND (@NO_ACCT_REF <> '')
		BEGIN
			DELETE SL_ACCT 
			 WHERE CD_STORE 	=@CD_STORE 
			       AND YMD_OCCUR	=SUBSTRING(@NO_ACCT_REF,1,8) 
			       AND NO_ACCT 	=SUBSTRING(@NO_ACCT_REF,9,3)

			SET @NO_ACCT_REF = ''
		END
			

		UPDATE SL_BUY_H SET 	AMT_DUTY 	= @AMT_DUTY,
						AMT_TAX 	= @AMT_TAX, 
						AMT_TRUST 	= @AMT_TRUST, 
						AMT_CASH 	= @AMT_CASH,
						AMT_DC	= @AMT_DC,
						NO_ACCT_REF		= @NO_ACCT_REF
		 WHERE CD_STORE 	= @CD_STORE 
		       AND YMD_BUY 	= @YMD_BUY 
		       AND NO_BUY 	= @NO_BUY

		IF (@AMT_CASH <> 0) AND (@NO_ACCT_REF <> '')
		BEGIN
			UPDATE SL_ACCT SET AMT_OUT 		=@AMT_CASH-@AMT_DC, 
						CD_TRDPL 		=@CD_TRDPL,
						CD_SAWON_CHG	=@CD_SAWON_CHG
			 WHERE CD_STORE 	=@CD_STORE 
			       AND YMD_OCCUR	=SUBSTRING(@NO_ACCT_REF,1,8) 
			       AND NO_ACCT 	=SUBSTRING(@NO_ACCT_REF,9,3)
		END
		ELSE IF (@AMT_CASH <> 0) AND (@NO_ACCT_REF = '')
		BEGIN
	
			INSERT INTO SL_ACCT(
						CD_STORE,
						YMD_OCCUR,
						NO_ACCT,
						CD_ACCT,
						DS_ACCT,
						AMT_PAYIN,
						AMT_OUT,
						CD_TRDPL,
						NO_POS,
						CD_SAWON_CHG)
				           VALUES(
						@CD_STORE,
						@YMD_BUY,
						@NO_ACCT,
						@CD_ACCT,
						'0',
						0,
						@AMT_CASH,
						@CD_TRDPL,
						'00',
						@CD_SAWON_CHG)			
		END

	END

 END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SAVE_MS_STORE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SAVE_MS_STORE]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



CREATE   PROCEDURE SAVE_MS_STORE
	@CD_STORE 		VARCHAR(4),
	@CD_SAWON_CHG 	VARCHAR(6),
	@I_SW    	  	CHAR(1),
	@NM_STORE  	 	VARCHAR(30),
	@NM_BOSS   	 	VARCHAR(10),
	@NO_BIZER  	 	VARCHAR(12),
	@NM_UPTAE  	 	VARCHAR(30),
	@NM_UPJONG 	VARCHAR(30),
	@TEL_OFFICE 	 	VARCHAR(13),
	@TEL_MOBIL1		VARCHAR(13),
	@TEL_MOBIL2		VARCHAR(13),
	@TEL_FAX   		VARCHAR(13),
	@NO_POST  	 	VARCHAR(6),
	@ADDR1     	 	VARCHAR(40),
	@ADDR2    	 	VARCHAR(40),
	@PNT_MIN_USE 	INT,
	@RCP_TITLE1	 	VARCHAR(42),
	@RCP_TITLE2	 	VARCHAR(42),
	@RCP_TITLE3 	 	VARCHAR(42),
	@RCP_TITLE4	 	VARCHAR(42),
	@CARD_TITLE1	VARCHAR(42),
	@CARD_TITLE2	VARCHAR(42),
	@CARD_TITLE3 	VARCHAR(42),
	@CARD_TITLE4	VARCHAR(42),
	@RCP_END1 		VARCHAR(42),
	@RCP_END2 		VARCHAR(42),
	@RCP_END3 		VARCHAR(42),
	@TICKET_END1	VARCHAR(42),
	@TICKET_END2	VARCHAR(42),
	@TICKET_END3	VARCHAR(42),
	@TICKET_END4	VARCHAR(42),
	@TICKET_END5	VARCHAR(42),
	@CDP_ACCOUNT 	VARCHAR(40),
	@CDP_READY  	VARCHAR(40),
	@CONF        	 	VARCHAR(200),
	@USE_MENU		VARCHAR(50),
	@REMARK    		VARCHAR(100),
	@DC_UNIT		INT,
	@LEN_CARD		INT,
	@PREFIX		VARCHAR(20),
	@NO_POS		VARCHAR(2),
	@LEN_MENU		INT,
	@CASHAMT_STD	INT,
	@CASH_POINT		INT,
	@CARDAMT_STD	INT,
	@CARD_POINT	INT,
	@CASHRCPAMT_STD	INT,
	@CASHRCP_POINT	INT,
	@POINT_MIN		INT,
	@RT_ONEVAN		INT,
	@AMT_LIMIT		INT,
	@CUST_TITLE		VARCHAR(30),
	@RPT_PWD		VARCHAR(20),
	@SIMPLE_RCP		VARCHAR(50),
	@MIN_AFTER		INT,
	@MIN_EACH		INT,
	@AMT_ADD		INT,
	@ADD_MENU		VARCHAR(14),
	@CLSTEP1		INT,
	@CLSTEP2		INT,
	@CLSTEP3		INT,
	@CLCOLOR1		VARCHAR(30),
	@CLCOLOR2		VARCHAR(30),
	@CLCOLOR3		VARCHAR(30),
	@LOCK_X		INT,
	@LOCK_Y		INT,
	@LOCKPOS_X		INT,
	@LOCKPOS_Y		INT,
	@LOCKPOS_F		INT,
	@CLIPBOARD		VARCHAR(30),
	@TICKET_TITLE	VARCHAR(20),
	@LEN_COUPON	INT,
	@DAYTIME_BEGIN	VARCHAR(5),
	@DAYTIME_END	VARCHAR(5),
	@NIGHTTIME_BEGIN	VARCHAR(5),
	@NIGHTTIME_END	VARCHAR(5),
	@BACKUP_DIR		VARCHAR(100),
	@AMT_DEFREADY	INT,
	@CARD_FEE		NUMERIC(3,1)


AS
BEGIN
	UPDATE MS_STORE SET 	NM_STORE		=@NM_STORE,
					NM_BOSS		=@NM_BOSS,
					NO_BIZER		=@NO_BIZER,
					NM_UPTAE		=@NM_UPTAE,
					NM_UPJONG 		=@NM_UPJONG,
			                  	TEL_OFFICE		=@TEL_OFFICE,
					TEL_MOBIL1		=@TEL_MOBIL1,
					TEL_MOBIL2		=@TEL_MOBIL2,
					TEL_FAX		=@TEL_FAX,
					NO_POST		=@NO_POST,
					ADDR1			=@ADDR1,
					ADDR2			=@ADDR2,
					PNT_MIN_USE		=@PNT_MIN_USE,
					RCP_TITLE1		=@RCP_TITLE1,
					RCP_TITLE2		=@RCP_TITLE2,
					RCP_TITLE3		=@RCP_TITLE3,
					RCP_TITLE4		=@RCP_TITLE4,
					CARD_TITLE1		=@CARD_TITLE1,
					CARD_TITLE2		=@CARD_TITLE2,
					CARD_TITLE3		=@CARD_TITLE3,
					CARD_TITLE4		=@CARD_TITLE4,
					RCP_END1		=@RCP_END1,
					RCP_END2		=@RCP_END2,
			                  	RCP_END3		=@RCP_END3,
					TICKET_END1		=@TICKET_END1,
					TICKET_END2		=@TICKET_END2,
			                  	TICKET_END3		=@TICKET_END3,
			                  	TICKET_END4		=@TICKET_END4,
			                  	TICKET_END5		=@TICKET_END5,
					CDP_ACCOUNT	=@CDP_ACCOUNT,
					CDP_READY		=@CDP_READY,
					CONF 			=@CONF,
					USE_MENU		=@USE_MENU,
					REMARK 		=@REMARK,
					DC_UNIT		=@DC_UNIT,
					LEN_CARD		=@LEN_CARD,
					PREFIX			=@PREFIX,
					NO_POS		=@NO_POS,
					LEN_MENU		=@LEN_MENU,
					CASHAMT_STD	=@CASHAMT_STD,
					CASH_POINT		=@CASH_POINT,
					CARDAMT_STD	=@CARDAMT_STD,
					CARD_POINT		=@CARD_POINT,
					CASHRCPAMT_STD	=@CASHRCPAMT_STD,
					CASHRCP_POINT	=@CASHRCP_POINT,
					POINT_MIN		=@POINT_MIN,
					RT_ONEVAN		=@RT_ONEVAN,
					CUST_TITLE		=@CUST_TITLE,
					POSRPT_PWD		=@RPT_PWD,
					SIMPLE_RCP		=@SIMPLE_RCP,
					AMT_LIMIT		=@AMT_LIMIT,
					MIN_AFTER		=@MIN_AFTER,
					MIN_EACH		=@MIN_EACH,
					AMT_ADD		=@AMT_ADD,
					ADD_MENU		=@ADD_MENU,
					CLSTEP1		=@CLSTEP1,
					CLSTEP2		=@CLSTEP2,
					CLSTEP3		=@CLSTEP3,
					CLCOLOR1		=@CLCOLOR1,
					CLCOLOR2		=@CLCOLOR2,
					CLCOLOR3		=@CLCOLOR3,
					LOCK_X		=@LOCK_X,
					LOCK_Y		=@LOCK_Y,
					LOCKPOS_X		=@LOCKPOS_X,
					LOCKPOS_Y		=@LOCKPOS_Y,
					LOCKPOS_F		=@LOCKPOS_F,
					CLIPBOARD		=@CLIPBOARD,
					TICKET_TITLE		=@TICKET_TITLE,
					LEN_COUPON		=@LEN_COUPON,
					DAYTIME_BEGIN	=@DAYTIME_BEGIN,
					DAYTIME_END		=@DAYTIME_END,
					NIGHTTIME_BEGIN	=@NIGHTTIME_BEGIN,
					NIGHTTIME_END	=@NIGHTTIME_END,
					BACKUP_DIR		=@BACKUP_DIR,
					AMT_DEFREADY	=@AMT_DEFREADY,
					CARD_FEE		=@CARD_FEE,
					DT_CHANGE		=GETDATE()
	                      WHERE CD_STORE=@CD_STORE

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POS_SAVE_SALE_H]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SAVE_SALE_H]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE  PROCEDURE [dbo].[POS_SAVE_SALE_H]

	@WORK_KIND			VARCHAR(1),
	@CD_STORE 	    		VARCHAR(4),
	@YMD_SALE			VARCHAR(8),
	@NO_POS			VARCHAR(2),
	@NO_RCP			VARCHAR(4),
	@DS_SALE			VARCHAR(1),
	@AMT_SALE			INT,
	@AMT_DUTY			INT,
	@AMT_TAX			INT,
	@AMT_DUTYFREE		INT,
	@AMT_CASH			INT,
	@AMT_CARD			INT,
	@AMT_CHECK			INT,
	@AMT_TRUST			INT,
	@AMT_GIFT			INT,
	@AMT_POINT			INT,
	@AMT_LOSS			INT,
	@AMT_OKC			INT,
	@DC_MENU			INT,
	@DC_RECEIPT			INT,
	@DC_ENURI			INT,
	@DC_CODE			INT,
	@DC_COUPON			INT,
	@CD_CODE			VARCHAR(3),
	@DC_MEMBER			INT,	
	@DC_CUT			INT,
	@DC_VAT			INT,
	@AMT_CASHRCP		INT,
	@DS_LOCK			VARCHAR(3),
	@NO_LOCK			VARCHAR(4),
	@CD_SAWON			VARCHAR(6),	
	@CD_MEMBER			VARCHAR(10),
	@YMD_SALE_ORG		VARCHAR(8),
	@NO_RCP_ORG		VARCHAR(4),
	@CD_MENU			VARCHAR(14),
	@AMT_EXISTPOINT		INT,
	@PNT_OKC_OCCUR		INT,
	@PNT_OCCUR			INT OUT,
	@PNT_USE			INT,
	@PNT_TOTAL			INT OUT
AS
BEGIN
 
	--VOID 또는 입금변경시 사용하기위함
	DECLARE @PNT_OCCUR_ORG 	INT
	DECLARE @PNT_USE_ORG 		INT
	DECLARE @AMT_TRUST_ORG 		INT
	DECLARE @CD_MEMBER_ORG	VARCHAR(10)
	DECLARE @NO_TICKET_REF		VARCHAR(14)

	SELECT @PNT_OCCUR_ORG = PNT_OCCUR,
		  @PNT_USE_ORG	=PNT_USE,
		  @AMT_TRUST_ORG	=AMT_TRUST,
		  @CD_MEMBER_ORG	=CD_MEMBER
	   FROM SL_SALE_H
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_SALE	=@YMD_SALE
	      AND NO_POS	=@NO_POS
	      AND NO_RCP	=@NO_RCP

	/**************************************************************************************************************************************************************************************/
	/*                                               					포인트 적용   시작 								   */
	/**************************************************************************************************************************************************************************************/

	--결제변경일 경우에는 무조건 이전의 적립 및 사용내역을 삭제한다
	IF @WORK_KIND IN('C','V')
	BEGIN

		DELETE 
		   FROM SL_PNT_OCCUR
		WHERE LINK		=@YMD_SALE+@NO_POS+@NO_RCP

		DELETE 
		   FROM SL_PNT_PAY
		WHERE  LINK		=@YMD_SALE+@NO_POS+@NO_RCP	

		UPDATE MS_MEMBER SET 	AMT_SALE = AMT_SALE - @AMT_SALE
		 WHERE CD_MEMBER	=@CD_MEMBER

		SELECT @PNT_TOTAL = PNT_TOTAL
		   FROM SL_POINT
		 WHERE CD_MEMBER	=@CD_MEMBER
		
	END
	ELSE SET @PNT_USE = @AMT_POINT

	IF (@CD_MEMBER <> '' AND @WORK_KIND <> 'V'  AND @WORK_KIND <> 'H')
	BEGIN
		DECLARE @CASHAMT_STD		INT
		DECLARE @CARDAMT_STD		INT
		DECLARE @CASHRCPAMT_STD	INT
		DECLARE @CASHPNT_STD		INT
		DECLARE @CARDPNT_STD		INT
		DECLARE @CASHRCPPNT_STD	INT

		DECLARE @CASHAMT		INT
		DECLARE @CARDAMT		INT
		DECLARE @CASHRCPAMT	INT
		DECLARE @PNT_CASH	INT
		DECLARE @PNT_CARD	INT
		DECLARE @PNT_CASHRCP	INT


		--회원이면 회원별로 포인트가 설정되어져있는지 체크한다
		IF @CD_MEMBER <> ''
		BEGIN
			
			SELECT @CASHAMT_STD 	= CONVERT(INT, ISNULL(A.NM_CODE5,'0')),
				  @CASHPNT_STD 	= CONVERT(INT, ISNULL(A.NM_CODE6,'0')),
				  @CARDAMT_STD 	= CONVERT(INT, ISNULL(A.NM_CODE7,'0')),
				  @CARDPNT_STD 	= CONVERT(INT, ISNULL(A.NM_CODE8,'0')),
				  @CASHRCPAMT_STD = CONVERT(INT, ISNULL(A.NM_CODE9,'0')),
				  @CASHRCPPNT_STD = CONVERT(INT, ISNULL(A.NM_CODE10,'0'))
			    FROM MS_CODE A, MS_MEMBER  B
			 WHERE A.CD_KIND    		= '05'
	                                  AND A.CD_CODE   		=B.DS_MEMBER
			       AND A.CD_STORE 		=@CD_STORE
			       AND B.CD_MEMBER	=@CD_MEMBER

			SET @CASHAMT_STD 		= ISNULL(@CASHAMT_STD,0)
			SET @CARDAMT_STD 		= ISNULL(@CARDAMT_STD,0)
			SET @CASHRCPAMT_STD 	= ISNULL(@CASHRCPAMT_STD,0)

			SET @CASHPNT_STD  	= ISNULL(@CASHPNT_STD,0)
			SET @CARDPNT_STD 		= ISNULL(@CARDPNT_STD,0)
			SET @CASHRCPPNT_STD 	= ISNULL(@CASHRCPPNT_STD,0)
		END

		
		IF (@CASHAMT_STD = 0) AND (@CARDAMT_STD = 0) AND (@CASHRCPAMT_STD = 0)
		BEGIN
			SELECT @CASHAMT_STD = CASHAMT_STD,
				  @CASHPNT_STD = CASH_POINT,
				  @CARDAMT_STD = CARDAMT_STD,
				  @CARDPNT_STD = CARD_POINT,
				  @CASHRCPAMT_STD = CASHRCPAMT_STD,
				  @CASHRCPPNT_STD = CASHRCP_POINT
			    FROM MS_STORE
			 WHERE CD_STORE =@CD_STORE

		END

	
		SET @CASHAMT 	=ISNULL(@AMT_CASH,0)+ISNULL(@AMT_CHECK,0)+ISNULL(@AMT_GIFT,0) - ISNULL(@AMT_CASHRCP, 0)
		SET @CARDAMT 	= @AMT_CARD
		SET @CASHRCPAMT 	= @AMT_CASHRCP

		SET @CASHAMT 	=ABS(@CASHAMT)
		SET @CARDAMT 	=ABS(@CARDAMT)
		SET @CASHRCPAMT 	=ABS(@CASHRCPAMT)
		SET @AMT_EXISTPOINT = ABS(@AMT_EXISTPOINT)


		DECLARE	@AMT_APPLY	INT
		DECLARE	@KIND		INT	--0:현금, 1:현금영수증, 2:신용카드
		SET 		@AMT_APPLY = 0
		SET 		@KIND = 0

		IF ((@CASHAMT = 0) AND (@CARDAMT = 0) AND (@CASHRCPAMT = 0) ) OR (@CASHAMT+@CARDAMT+@CASHRCPAMT <= @AMT_EXISTPOINT)
		BEGIN
			SELECT @PNT_TOTAL = ISNULL(PNT_TOTAL,0) FROM SL_POINT WHERE CD_MEMBER = @CD_MEMBER		
			IF @PNT_USE = 0 	
				GOTO PASS_1
			ELSE
				GOTO PASS
		END
	
		WHILE @AMT_APPLY< @AMT_EXISTPOINT
		BEGIN
			IF @KIND = 0 
			BEGIN
				IF @CASHAMT >= (@AMT_EXISTPOINT-@AMT_APPLY)
				BEGIN
					SET @CASHAMT 	= @CASHAMT - @AMT_EXISTPOINT
					SET @AMT_APPLY 	= @AMT_EXISTPOINT
				END
				ELSE IF @CASHAMT > 0
				BEGIN
					SET @AMT_APPLY 	=@CASHAMT
					SET @CASHAMT 	=@CASHAMT - (@AMT_EXISTPOINT -  (@AMT_EXISTPOINT- @CASHAMT))
				END
				SET 		@KIND = 1		
			END
			ELSE IF @KIND = 1
			BEGIN
				IF @CASHRCPAMT >= (@AMT_EXISTPOINT-@AMT_APPLY)
				BEGIN
					SET @CASHRCPAMT 	= @CASHRCPAMT - (@AMT_EXISTPOINT-@AMT_APPLY)
					SET @AMT_APPLY 	= @AMT_APPLY + (@AMT_EXISTPOINT-@AMT_APPLY)
				END
				ELSE IF @CASHRCPAMT > 0
				BEGIN
					SET @AMT_APPLY 	=@AMT_APPLY + @CASHRCPAMT
					IF (@AMT_EXISTPOINT>= @AMT_APPLY) 
						SET @CASHRCPAMT 	= 0
					ELSE
						SET @CASHRCPAMT =@AMT_EXISTPOINT-@AMT_APPLY
				END
				SET 		@KIND = 2		
			END
			ELSE IF @KIND = 2
			BEGIN
				IF @CARDAMT >= (@AMT_EXISTPOINT-@AMT_APPLY)
				BEGIN
					SET @CARDAMT 	= @CARDAMT - (@AMT_EXISTPOINT-@AMT_APPLY)
					SET @AMT_APPLY 	= @AMT_APPLY + @AMT_EXISTPOINT-@AMT_APPLY
				END
				ELSE IF @CARDAMT > 0
				BEGIN
					SET @AMT_APPLY 	=@AMT_APPLY + @CARDAMT
					IF (@AMT_EXISTPOINT>= @AMT_APPLY) 
						SET @CARDAMT 	= 0
					ELSE
						SET @CARDAMT 	=@AMT_EXISTPOINT-@AMT_APPLY
				END

			END

		END

PASS:

		--반품일때는 포인트 제외상품금액에 더한다
		IF @WORK_KIND = 'B'
		BEGIN
			SET @CASHAMT 	=@CASHAMT		*-1
			SET @CARDAMT 	=@CARDAMT		*-1
			SET @CASHRCPAMT 	=@CASHRCPAMT	*-1
			SET @AMT_EXISTPOINT =@AMT_EXISTPOINT *-1
		END


		--현금 포인트
		IF @CASHPNT_STD > 0
			SET	@PNT_CASH = @CASHAMT / @CASHAMT_STD * @CASHPNT_STD
		ELSE
			SET	@PNT_CASH = 0

		--카드 포인트
		IF @CARDPNT_STD > 0
			SET	@PNT_CARD = @CARDAMT / @CARDAMT_STD * @CARDPNT_STD
		ELSE
			SET	@PNT_CARD = 0

		--현금영수증 포인트
		IF @CASHRCPPNT_STD > 0
			SET	@PNT_CASHRCP = @CASHRCPAMT / @CASHRCPAMT_STD * @CASHRCPPNT_STD
		ELSE
			SET	@PNT_CASHRCP = 0

		SET 	@PNT_OCCUR = @PNT_CASH + @PNT_CARD + @PNT_CASHRCP

		--포인트를 사용한 영수증은 포인트를 적립하지 않는다
		IF (@PNT_USE <> 0)
			SET @PNT_OCCUR = 0

		--VOID일때는 발생포인트에 *-1을 곱한다
		IF @WORK_KIND = 'V'
		BEGIN
			SET @PNT_OCCUR = @PNT_OCCUR * -1
		END

		SELECT @PNT_TOTAL = ISNULL(PNT_TOTAL,0) FROM SL_POINT WHERE CD_MEMBER = @CD_MEMBER
	
		SET @PNT_TOTAL	= ISNULL(@PNT_TOTAL, 0)
		SET @PNT_OCCUR 	= ISNULL(@PNT_OCCUR, 0)
		SET @PNT_USE 	= ISNULL(@PNT_USE, 0)

		--총포인트
		SELECT @PNT_TOTAL = @PNT_OCCUR + @PNT_TOTAL - @PNT_USE
	
		SET @CASHAMT =ISNULL(@AMT_CASH,0)+ISNULL(@AMT_CHECK,0)+ISNULL(@AMT_GIFT,0) - ISNULL(@AMT_CASHRCP,0)
		SET @CARDAMT = @AMT_CARD
		SET @CASHRCPAMT = @AMT_CASHRCP

		SET @CASHAMT 	=ABS(@CASHAMT)
		SET @CARDAMT 	=ABS(@CARDAMT)
		SET @CASHRCPAMT 	=ABS(@CASHRCPAMT)
		SET @AMT_EXISTPOINT = ABS(@AMT_EXISTPOINT)	

		IF @WORK_KIND = 'B'
		BEGIN
			SET @CASHAMT 	=@CASHAMT		*-1
			SET @CARDAMT 	=@CARDAMT		*-1
			SET @CASHRCPAMT 	=@CASHRCPAMT	*-1
			SET @AMT_EXISTPOINT =@AMT_EXISTPOINT *-1
		END
	
		DECLARE @PNT_SEQ INT

		--적립에 대해 적용
		IF (@PNT_OCCUR <> 0)
		BEGIN
			SELECT @PNT_SEQ = ISNULL(MAX(SEQ),0) +1
			   FROM SL_PNT_OCCUR
			WHERE YMD_OCCUR = @YMD_SALE
		
			INSERT INTO SL_PNT_OCCUR( 
							CD_MEMBER,
							YMD_OCCUR,
							SEQ,
							CD_CODE,
							AMT_CASH,
							AMT_CARD,
							AMT_CASHRCP,
							PNT_CASH,
							PNT_CARD,

							PNT_CASHRCP,
							AMT_EXIST,
							LINK
							)
					VALUES(
							@CD_MEMBER,
							@YMD_SALE,
							@PNT_SEQ,
							'000',
							@AMT_CASH,
							@AMT_CARD,
							@AMT_CASHRCP,
							@PNT_CASH,
							@PNT_CARD,
							@PNT_CASHRCP,
							@AMT_EXISTPOINT,
							@YMD_SALE+@NO_POS+@NO_RCP	
						)
		END
		ELSE IF @PNT_USE <> 0
		BEGIN
			SELECT @PNT_SEQ = ISNULL(MAX(SEQ),0) +1
			   FROM SL_PNT_PAY
			WHERE  YMD_PAY = @YMD_SALE
		
			INSERT INTO SL_PNT_PAY( 
							CD_MEMBER,
							YMD_PAY,
							SEQ,
							CD_CODE,
							PNT_USE,
							LINK
							)
					VALUES(
							@CD_MEMBER,
							@YMD_SALE,
							@PNT_SEQ,
							'000',
							@PNT_USE,
							@YMD_SALE+@NO_POS+@NO_RCP	
						)
		END


	END

	/**************************************************************************************************************************************************************************************/
	/*                                               					포인트 적용    END 								   */
	/**************************************************************************************************************************************************************************************/

	/**************************************************************************************************************************************************************************************/
	/*                                               					회원 외상 적용  시작 								   */
	/**************************************************************************************************************************************************************************************/

PASS_1:

	IF @WORK_KIND IN ('S','B')
	BEGIN
	
		DECLARE @COME_TIME	DATETIME

		IF @WORK_KIND = 'S'
		BEGIN
			SELECT @COME_TIME 	=COME_TIME
			   FROM SL_ORDER_H
			WHERE CD_STORE	=@CD_STORE
			      AND DS_LOCK	=@DS_LOCK
			      AND NO_LOCK	=@NO_LOCK

			SET @COME_TIME = ISNULL(@COME_TIME, GETDATE())
			EXEC SAVE_LOCKER_CANCEL @CD_STORE, @YMD_SALE, @DS_LOCK, @NO_LOCK

			IF (@CD_MEMBER <> '') AND (@CD_MENU <> '')
			BEGIN
				INSERT INTO SL_MEMBER_USE (YMD_SALE, NO_RCP, CD_MEMBER, CD_MENU)
							VALUES   (CONVERT(VARCHAR(8), GETDATE(), 112), @CD_STORE+@YMD_SALE+@NO_POS+@NO_RCP, @CD_MEMBER, @CD_MENU)
			END

			--입장권번호
			SELECT @NO_TICKET_REF = YMD_SALE+NO_POS+NO_RCP 
			   FROM SL_LOCK_ALLOT
			WHERE CD_STORE	=@CD_STORE
			      AND DS_LOCK	=@DS_LOCK
			      AND NO_LOCK	=@NO_LOCK
			      AND OUT_TIME	IS NULL
		END
		--반품이면 회원 이용내역을 삭제한다
		ELSE IF @WORK_KIND = 'B'		
		BEGIN
			SET @COME_TIME = GETDATE()
			IF @CD_MEMBER <> ''
			BEGIN

				DELETE FROM SL_MEMBER_USE WHERE NO_RCP = @CD_STORE+@YMD_SALE+@NO_POS+@NO_RCP
			END
		END

		INSERT INTO SL_SALE_H(
						CD_STORE,
						YMD_SALE,
						NO_POS,
						NO_RCP,
						DS_SALE,
						AMT_SALE,
						AMT_DUTY,
						AMT_TAX,
						AMT_DUTYFREE,
						AMT_CASH,
						AMT_CARD,
						AMT_CHECK,
						AMT_TRUST,
						AMT_GIFT,
						AMT_POINT,
						AMT_LOSS,
						AMT_OKC,
						DC_MENU,
						DC_RECEIPT,
						DC_ENURI,
						DC_CODE,
						CD_CODE,
						DC_MEMBER,
						DC_COUPON,
						DC_CUT,
						DC_VAT,
						AMT_CASHRCP,
						DS_LOCK,
						NO_LOCK,
						COME_TIME,
						DT_SALE,
						CD_SAWON,
						CD_MEMBER,
						NO_RCP_ORG,
						PNT_OKC_OCCUR,
						PNT_OCCUR,
						PNT_USE,
						AMT_EXISTPOINT,
						NO_ACCT_REF,
						NO_TICKET_REF
						)
					VALUES
						(
						@CD_STORE,
						@YMD_SALE,
						@NO_POS,
						@NO_RCP,
						@DS_SALE,
						@AMT_SALE,
						@AMT_DUTY,
						@AMT_TAX,
						@AMT_DUTYFREE,
						@AMT_CASH,
						@AMT_CARD,
						@AMT_CHECK,
						@AMT_TRUST,
						@AMT_GIFT,
						@AMT_POINT,
						@AMT_LOSS,
						@AMT_OKC,
						@DC_MENU,
						@DC_RECEIPT,
						@DC_ENURI,
						@DC_CODE,
						@CD_CODE,
						@DC_MEMBER,
						@DC_COUPON,
						@DC_CUT,
						@DC_VAT,
						@AMT_CASHRCP,
						@DS_LOCK,
						@NO_LOCK,
						@COME_TIME,
						GETDATE(),
						@CD_SAWON,
						@CD_MEMBER,
						@YMD_SALE_ORG +  @NO_POS + @NO_RCP_ORG,
						@PNT_OKC_OCCUR,
						@PNT_OCCUR,
						@PNT_USE,
						@AMT_EXISTPOINT,
						'',
						@NO_TICKET_REF	
						)

						

		IF @CD_MEMBER <> ''
		BEGIN
			UPDATE MS_MEMBER SET 	YMD_VISIT = CONVERT(VARCHAR(8), GETDATE(), 112),
							AMT_SALE = AMT_SALE + @AMT_SALE,
							CNT_VISIT = CNT_VISIT + 1
			 WHERE CD_MEMBER	=@CD_MEMBER
		END


		--쿠폰사용내역 환원
		UPDATE MS_COUPON SET CNT_USE = CNT_USE - T1.QTY_SALE
		  FROM SL_SALE_D T1
		 WHERE MS_COUPON.CD_STORE	= T1.CD_STORE
		   AND MS_COUPON.NO_COUPON+MS_COUPON.NO_SEQ	= T1.NO_COUPON
		   AND T1.CD_STORE			= @CD_STORE
		   AND T1.YMD_SALE			= @YMD_SALE
		   AND T1.NO_POS			= @NO_POS
		   AND T1.NO_RCP			= @NO_RCP
		   AND T1.DS_SALE			= 'C'

		--반품일때
		IF @WORK_KIND = 'B'
		BEGIN
			INSERT INTO SL_SALE_D(
							CD_STORE,
							YMD_SALE,
							NO_POS,
							NO_RCP,
							DS_SALE,
							CD_MENU,
							SEQ,
							QTY_SALE,	
							PR_SALE,
							AMT_SALE,
							DC_MENU,
							DC_RECEIPT,
							DC_MEMBER,
							AMT_VAT,
							DS_TAX
							)
						
					SELECT	CD_STORE,
							@YMD_SALE,
							NO_POS,
							@NO_RCP,
							DS_SALE,
							CD_MENU,
							SEQ,
							QTY_SALE *-1,
							PR_SALE,
							AMT_SALE *-1,
							DC_MENU * -1,
							DC_RECEIPT * -1,
							DC_MEMBER*-1,
							AMT_VAT * -1,
							DS_TAX
			
					FROM		SL_SALE_D
					WHERE	CD_STORE	=@CD_STORE
					      AND 	YMD_SALE	=@YMD_SALE_ORG
					      AND 	NO_POS	=@NO_POS
					      AND 	NO_RCP	=@NO_RCP_ORG

						
		END

					
	END
	--현금영수증만 발행
	ELSE IF @WORK_KIND = 'H'
	BEGIN
		UPDATE SL_SALE_H SET
						PNT_OKC_OCCUR	=@PNT_OKC_OCCUR,
						AMT_CASHRCP	=@AMT_CASHRCP,
						DT_CHANGE  		=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP

	END
	--결제변경
	ELSE IF @WORK_KIND = 'C' 
	BEGIN
		UPDATE SL_SALE_H SET	AMT_SALE		=@AMT_SALE,
						AMT_CASH		=@AMT_CASH,
						AMT_CARD		=@AMT_CARD,
						AMT_CHECK		=@AMT_CHECK,
						AMT_TRUST		=@AMT_TRUST,
						AMT_GIFT		=@AMT_GIFT,
						AMT_POINT		=@AMT_POINT,
						AMT_LOSS		=@AMT_LOSS,
						AMT_OKC		=@AMT_OKC,
						AMT_CASHRCP	=@AMT_CASHRCP,
						DC_MENU		=@DC_MENU,
						CD_MEMBER		=@CD_MEMBER,
						DC_MEMBER		=@DC_MEMBER,
						DC_RECEIPT		=@DC_RECEIPT,
						DC_ENURI		=@DC_ENURI,
						DC_CODE		=@DC_CODE,
						DC_COUPON		=@DC_COUPON,
						DC_CUT		=@DC_CUT,
						DC_VAT		=@DC_VAT,
						CD_SAWON		=@CD_SAWON,
						AMT_DUTY		=@AMT_DUTY,
						AMT_TAX		=@AMT_TAX,
						PNT_OKC_OCCUR	=@PNT_OKC_OCCUR,
						PNT_OCCUR		=@PNT_OCCUR,
						PNT_USE		=@PNT_USE,
						DT_CHANGE  		=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP

		DELETE 
		   FROM SL_SALE_D
		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP

	END
	-- VOID 처리
	ELSE IF @WORK_KIND = 'V'
	BEGIN

		UPDATE SL_SALE_H SET
						DS_SALE	='V',
						DT_CHANGE  	=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP


		--회원이용내역을 삭제한다
		IF @CD_MEMBER <> ''
		BEGIN

			DELETE FROM SL_MEMBER_USE WHERE NO_RCP = @CD_STORE+@YMD_SALE+@NO_POS+@NO_RCP
		END

		--쿠폰사용내역 환원
		UPDATE MS_COUPON SET CNT_USE = CNT_USE - T1.QTY_SALE
		  FROM SL_SALE_D T1
		 WHERE MS_COUPON.CD_STORE	= T1.CD_STORE
		   AND MS_COUPON.NO_COUPON+MS_COUPON.NO_SEQ	= T1.NO_COUPON
		   AND T1.CD_STORE			= @CD_STORE
		   AND T1.YMD_SALE			= @YMD_SALE
		   AND T1.NO_POS			= @NO_POS
		   AND T1.NO_RCP			= @NO_RCP
		   AND T1.DS_SALE			= 'C'
	

	END
	ELSE IF @WORK_KIND = 'P'
	BEGIN
		UPDATE SL_SALE_H SET
						CD_MEMBER=@CD_MEMBER,
						PNT_OCCUR = @PNT_OCCUR,
						DT_CHANGE  	=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP

		IF @CD_MEMBER <> ''
		BEGIN
			UPDATE MS_MEMBER SET 	YMD_VISIT = CONVERT(VARCHAR(8), GETDATE(), 112),
							AMT_SALE = AMT_SALE + @AMT_SALE,
							CNT_VISIT = CNT_VISIT + 1
			 WHERE CD_MEMBER	=@CD_MEMBER
		END

	END
	ELSE IF @WORK_KIND = 'O'
	BEGIN
		UPDATE SL_SALE_H SET
						PNT_OKC_OCCUR = @PNT_OKC_OCCUR,
						DT_CHANGE  	=GETDATE()

		WHERE CD_STORE	=@CD_STORE
		      AND YMD_SALE	=@YMD_SALE
		      AND NO_POS	=@NO_POS
		      AND NO_RCP	=@NO_RCP		
	END

	SELECT @PNT_OCCUR = ISNULL(@PNT_OCCUR, 0)
	SELECT @PNT_TOTAL = ISNULL(@PNT_TOTAL, 0)


END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SL_ORDER_IMG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SL_ORDER_IMG]
GO

CREATE TABLE [dbo].[SL_ORDER_IMG] (
	[cd_store] [varchar] (4) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[no_lock] [varchar] (4) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[ds_lock] [varchar] (3) COLLATE Korean_Wansung_CI_AS NOT NULL ,
	[seq] [int] NOT NULL ,
	[ds_img] [varchar] (1) COLLATE Korean_Wansung_CI_AS NULL ,
	[img_image] [image] NULL ,
	[dt_change] [dt_change] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[SL_ORDER_IMG] ADD 
	CONSTRAINT [PK_SL_ORDER_IMG] PRIMARY KEY  CLUSTERED 
	(
		[cd_store],
		[no_lock],
		[ds_lock],
		[seq]
	)  ON [PRIMARY] 
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[getdate]', N'[SL_ORDER_IMG].[dt_change]'
GO

setuser
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POS_SAVE_ORDER_IMG]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SAVE_ORDER_IMG]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



-- 주문취소내역 저장
CREATE  PROCEDURE POS_SAVE_ORDER_IMG

	@CD_STORE 	    		VARCHAR(4),
	@NO_LOCK			VARCHAR(4),
	@DS_LOCK			VARCHAR(3),
	@DS_IMG			VARCHAR(1),
	@IMG_IMAGE			IMAGE
AS
BEGIN

	DECLARE @SEQ INT

	SELECT @SEQ = ISNULL( MAX(SEQ), 0 ) + 1
	   FROM SL_ORDER_IMG
	WHERE CD_STORE	=@CD_STORE
	      AND NO_LOCK	=@NO_LOCK
	      AND DS_LOCK	=@DS_LOCK


	INSERT INTO SL_ORDER_IMG(
					CD_STORE,
					NO_LOCK,
					DS_LOCK,
					SEQ,	
					DS_IMG,
					IMG_IMAGE,
					DT_CHANGE
				         )
			VALUES
				        (
					@CD_STORE,
					@NO_LOCK,
					@DS_LOCK,
					@SEQ,	
					@DS_IMG,
					@IMG_IMAGE,
					GETDATE()
				        )

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POS_SAVE_CASHIER_MGM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SAVE_CASHIER_MGM]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POS_SAVE_POS_MGM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SAVE_POS_MGM]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POS_SELECT_CASHIER_MGM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SELECT_CASHIER_MGM]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POS_SELECT_POS_MGM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[POS_SELECT_POS_MGM]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE  PROCEDURE POS_SAVE_CASHIER_MGM

	@CD_STORE 	    		VARCHAR(4),
	@YMD_CLOSE			VARCHAR(8),
	@NO_POS			VARCHAR(2),
	@CD_SAWON			VARCHAR(6),
	@AMT_ACCT_IN		INT,
	@AMT_ACCT_OUT		INT,
	@AMT_SALE			INT,
	@AMT_TAX			INT,
	@AMT_CASH			INT,
	@AMT_CARD			INT,
	@AMT_CHECK			INT,
	@AMT_TRUST			INT,
	@AMT_GIFT			INT,
	@AMT_POINT			INT,
	@AMT_LOSS			INT,
	@AMT_OKC			INT,
	@DC_MENU			INT,
	@DC_CODE			INT,
	@DC_RECEIPT			INT,
	@DC_MEMBER			INT,
	@DC_ENURI			INT,
	@DC_SPC			INT,
	@DC_COUPON			INT,
	@DC_CUT			INT,
	@DC_VAT			INT,
	@AMT_VOID			INT,
	@CNT_CUSTOMER		INT,
	@AMT_AVERAGE		INT,
	@AMT_LACK			INT,
	@AMT_CASHRCP		INT,
	@RCP_BEGIN			VARCHAR(4),
	@RCP_END			VARCHAR(4),
	@_CHECK			INT,
	@_50000			INT,
	@_10000			INT,
	@_5000			INT,
	@_1000			INT,
	@_500				INT,
	@_100				INT,
	@_50				INT,
	@_10				INT,
	@SEQ				INT OUTPUT
AS
BEGIN
	
	DECLARE @CNT INT

	SELECT @SEQ =ISNULL(MAX(SEQ),	(SELECT ISNULL(MAX(SEQ),0)+1
						   FROM SL_CASHIER_MGM
						WHERE CD_STORE 	=@CD_STORE
						      AND YMD_CLOSE	=@YMD_CLOSE
						      AND NO_POS	=@NO_POS
						      AND CD_SAWON	=@CD_SAWON
						      AND YN_CLOSE	='Y'
				)
			)
	  FROM SL_CASHIER_MGM 
	 WHERE CD_STORE 	=@CD_STORE
	   AND YMD_CLOSE	=@YMD_CLOSE
	   AND NO_POS		=@NO_POS
	   AND CD_SAWON	=@CD_SAWON
	   AND YN_CLOSE = 'N' 

	SELECT @CNT= COUNT(*)
	   FROM SL_CASHIER_MGM
	WHERE CD_STORE 	=@CD_STORE
	      AND YMD_CLOSE	=@YMD_CLOSE
	      AND NO_POS	=@NO_POS
	      AND CD_SAWON	=@CD_SAWON
	      AND SEQ		=@SEQ

	IF @CNT = 0
	BEGIN

	
		INSERT INTO SL_CASHIER_MGM(
							CD_STORE		,
							YMD_CLOSE		,
							NO_POS		,
							CD_SAWON		,
							SEQ			,
							AMT_ACCT_IN		,
							AMT_ACCT_OUT	,
							AMT_SALE		,
							AMT_TAX		,
							AMT_CASH		,
							AMT_CARD		,
							AMT_CHECK		,
							AMT_TRUST		,
							AMT_GIFT		,
							AMT_POINT		,
							AMT_LOSS		,
							AMT_OKC		,
							DC_MENU		,
							DC_CODE		,
							DC_RECEIPT		,
							DC_MEMBER		,
							DC_ENURI		,
							DC_SPC		,
							DC_COUPON		,
							DC_CUT		,
							DC_VAT		,
							AMT_VOID		,
							CNT_CUSTOMER	,
							AMT_AVERAGE	,
							AMT_LACK		,
							AMT_CASHRCP	,
							RCP_BEGIN		,
							RCP_END		,
							_CHECK,
							_50000,
							_10000,
							_5000,
							_1000,
							_500,
							_100,
							_50,
							_10,
							YN_CLOSE		
						)
					VALUES
						(
							 @CD_STORE		,
							 @YMD_CLOSE		,
							 @NO_POS		,
							 @CD_SAWON		,
							 @SEQ			,
							 @AMT_ACCT_IN	,
							 @AMT_ACCT_OUT	,
							 @AMT_SALE		,
							 @AMT_TAX		,
							 @AMT_CASH		,
							 @AMT_CARD		,
							 @AMT_CHECK		,
							 @AMT_TRUST	,
							 @AMT_GIFT		,
							 @AMT_POINT		,
							 @AMT_LOSS		,
							 @AMT_OKC		,
							 @DC_MENU		,
							 @DC_CODE		,
							 @DC_RECEIPT	,
							 @DC_MEMBER	,
							 @DC_ENURI		,
							 @DC_SPC		,
							 @DC_COUPON	,
							 @DC_CUT		,
							 @DC_VAT		,
							 @AMT_VOID		,
							 @CNT_CUSTOMER	,
							 @AMT_AVERAGE	,
							 @AMT_LACK		,
							 @AMT_CASHRCP	,
							 @RCP_BEGIN		,
							 @RCP_END		,
							 @_CHECK,
							 @_50000,
							 @_10000,
							 @_5000,
							 @_1000,
							 @_500,
							 @_100,
							 @_50,
							 @_10,
							 'Y'
						)
	END
	ELSE
	BEGIN
		UPDATE SL_CASHIER_MGM SET 
							
						AMT_SALE	 	=@AMT_SALE		,
						AMT_TAX		=@AMT_TAX		,
						AMT_CASH		=@AMT_CASH		,
						AMT_CARD	 	=@AMT_CARD		,
						AMT_CHECK	 	=@AMT_CHECK	,
						AMT_TRUST	 	=@AMT_TRUST	,
						AMT_GIFT	 	=@AMT_GIFT		,
						AMT_POINT	 	=@AMT_POINT		,
						AMT_LOSS	 	=@AMT_LOSS		,
						AMT_OKC		=@AMT_OKC		,
						DC_MENU	 	=@DC_MENU		,
						DC_CODE	 	=@DC_CODE		,
						DC_RECEIPT	 	=@DC_RECEIPT	,
						DC_MEMBER	 	=@DC_MEMBER	,
						DC_ENURI	 	=@DC_ENURI		,
						DC_SPC	 	=@DC_SPC		,
						DC_COUPON		=@DC_COUPON		,
						DC_CUT		=@DC_CUT		,
						DC_VAT		=@DC_VAT		,
						AMT_VOID	 	=@AMT_VOID		,
						CNT_CUSTOMER	=@CNT_CUSTOMER	,
						AMT_AVERAGE	=@AMT_AVERAGE	,
						AMT_LACK		=@AMT_LACK		,
						AMT_CASHRCP	=@AMT_CASHRCP	,
						RCP_BEGIN	 	=@RCP_BEGIN		,
						RCP_END	 	=@RCP_END		,
						_CHECK		=@_CHECK		,
						_50000			=@_50000		,
						_10000			=@_10000		,
						_5000			=@_5000		,
						_1000			=@_1000		,
						_500			=@_500		,
						_100			=@_100		,
						_50			=@_50			,
						_10			=@_10			,
						DT_CHANGE		=GETDATE()		,
						YN_CLOSE		= 'Y'
			WHERE CD_STORE 	=@CD_STORE
			      AND YMD_CLOSE	=@YMD_CLOSE
			      AND NO_POS	=@NO_POS
			      AND CD_SAWON	=@CD_SAWON
			      AND SEQ		=@SEQ
	END

	--매출헤더에 계산원마감 번호 셋팅
	UPDATE SL_SALE_H SET NO_CLOSE = @SEQ
	WHERE CD_STORE	=@CD_STORE	
	      AND YMD_SALE	=@YMD_CLOSE
	      AND NO_POS	=@NO_POS
	      AND CD_SAWON	=@CD_SAWON
	      AND NO_CLOSE	=0

	--현금출납에 계산원마감 번호 셋팅
	UPDATE SL_ACCT SET NO_CLOSE = @SEQ
	WHERE CD_STORE		=@CD_STORE	
	      AND YMD_OCCUR		=@YMD_CLOSE
	      AND NO_POS		=@NO_POS
	      AND CD_SAWON_CHG	=@CD_SAWON
	      AND NO_CLOSE		=0
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE  PROCEDURE POS_SAVE_POS_MGM

	@CD_STORE 	    		VARCHAR(4),
	@YMD_CLOSE			VARCHAR(8),
	@NO_POS			VARCHAR(2),
	@CD_WEATHER		VARCHAR(3),
	@WORK_KIND			VARCHAR(1),
	@RESULT			VARCHAR(1) OUTPUT,
	@YMD_LAST			VARCHAR(8) OUTPUT
AS
BEGIN
	
	IF @WORK_KIND = 'C'
	BEGIN
	
		DECLARE  	@AMT_READY		INT,
				@AMT_MID		INT,
				@AMT_SALE		INT,
				@AMT_CASH          	INT,
				@AMT_CARD         	INT,
				@AMT_CHECK        	INT,
				@AMT_GIFT          	INT,
				@AMT_POINT		INT,
				@AMT_TRUST		INT,
				@AMT_LOSS		INT,
				@AMT_OKC		INT,
				@AMT_VOID		INT,
				@AMT_CASHRCP	INT,
				@DC_MENU		INT,
				@DC_MEMBER		INT,
				@DC_RECEIPT	      	INT,
				@DC_CODE		INT,
				@DC_SPC		INT,
				@DC_ENURI		INT,
				@DC_COUPON		INT,
				@DC_CUT		INT,
				@DC_VAT		INT,
				@CNT_CUSTOMER	INT,
				@CNT_CASHIER	INT,
				@AMT_LACK		INT
	
		SELECT @AMT_READY	=SUM(AMT_READY	),
			 @AMT_MID		=SUM(AMT_MID_CASH+AMT_MID_CHECK	),
			 @AMT_SALE		=SUM(AMT_SALE	),
			 @AMT_CASH		=SUM(AMT_CASH	),
			 @AMT_CARD		=SUM(AMT_CARD	),
			 @AMT_CHECK         	=SUM(AMT_CHECK	),
			 @AMT_GIFT          	=SUM(AMT_GIFT	),
			 @AMT_POINT		=SUM(AMT_POINT	),
			 @AMT_TRUST	=SUM(AMT_TRUST	),
			 @AMT_LOSS		=SUM(AMT_LOSS	),
			 @AMT_OKC		=SUM(AMT_OKC	),
			 @AMT_VOID		=SUM(AMT_VOID	),
			 @AMT_CASHRCP	=SUM(AMT_CASHRCP 	),
			 @DC_MENU		=SUM(DC_MENU	),
			 @DC_MEMBER	=SUM(DC_MEMBER	),
			 @DC_RECEIPT	=SUM(DC_RECEIPT	),
			 @DC_CODE		=SUM(DC_CODE	),
			 @DC_SPC		=SUM(DC_SPC		),
			 @DC_ENURI		=SUM(DC_ENURI	),
			 @DC_COUPON	=SUM(DC_COUPON	),
			 @DC_CUT		=SUM(DC_CUT		),
			 @DC_VAT		=SUM(DC_VAT		),
			 @CNT_CUSTOMER     	=SUM(CNT_CUSTOMER),
			 @CNT_CASHIER	=COUNT(*),
			 @AMT_LACK		=SUM(AMT_LACK	)
		   FROM  SL_CASHIER_MGM
		WHERE  CD_STORE  	=@CD_STORE
		      AND  YMD_CLOSE 	=@YMD_CLOSE
		      AND  NO_POS    	=@NO_POS
	
		UPDATE SL_POSCLOSE SET
						AMT_READY		=@AMT_READY,
						AMT_MID		=@AMT_MID,
	         					AMT_SALE 	  	=@AMT_SALE,
						AMT_CASH		=@AMT_CASH,
						AMT_CARD		=@AMT_CARD,
						AMT_CHECK		=@AMT_CHECK,
						AMT_GIFT		=@AMT_GIFT,
						AMT_POINT		=@AMT_POINT,
						AMT_OKC		=@AMT_OKC,
						AMT_TRUST		=@AMT_TRUST,
						AMT_LOSS		=@AMT_LOSS,
						AMT_VOID		=@AMT_VOID,
						AMT_CASHRCP	=@AMT_CASHRCP,
						DC_MENU		=@DC_MENU,
						DC_MEMBER		=@DC_MEMBER,
						DC_RECEIPT	  	=@DC_RECEIPT,
						DC_CODE		=@DC_CODE,
						DC_SPC		=@DC_SPC,
						DC_ENURI		=@DC_ENURI,
						DC_COUPON		=@DC_COUPON,
						DC_CUT		=@DC_CUT,
						DC_VAT		=@DC_VAT,
						CNT_CUSTOMER	=@CNT_CUSTOMER,
						CNT_CASHIER		=@CNT_CASHIER,
						AMT_LACK		=@AMT_LACK,
						YN_DATADELETE	='N',
						DT_CHANGE		=GETDATE(),
						DS_STATUS		='C'
		    WHERE CD_STORE	  	=@CD_STORE
		          AND YMD_CLOSE	=@YMD_CLOSE
		          AND NO_POS	    	=@NO_POS
	

		--기간이 지난 회원상품의 상태를 변경한다
		DECLARE @POS_NO VARCHAR(2)
		SELECT @POS_NO = NO_POS FROM MS_STORE WHERE CD_STORE = @CD_STORE

		IF @NO_POS = @POS_NO
		BEGIN
			UPDATE SL_MEMBER_JOIN SET DS_STATUS = '2'
			WHERE YMD_FINISH < CONVERT(VARCHAR(8), GETDATE(), 112) 
			      AND DS_STATUS = '0'

			UPDATE SL_MEMBER_JOIN SET DS_STATUS = '0'
			WHERE DELAY_TO < CONVERT(VARCHAR(8), GETDATE(), 112) 
			      AND DS_STATUS = '1'
		END

	
	END
	--마감취소
	ELSE IF @WORK_KIND = 'X'
	BEGIN

		DECLARE @TEMP INT

		--포스마감이 된 일자인지 체크
		SELECT @TEMP = COUNT(*)
		   FROM SL_POSCLOSE
		 WHERE CD_STORE 	=@CD_STORE
		       AND YMD_CLOSE	=@YMD_CLOSE
		       AND NO_POS	=@NO_POS
		       AND DS_STATUS	='C'

		IF @TEMP > 0
		BEGIN

			--취소할려는 일자보다 나중에 일자에 마감된 일자가 있는지체크
			SELECT @TEMP = COUNT(*)
			   FROM SL_POSCLOSE
			 WHERE CD_STORE 	=@CD_STORE
			       AND YMD_CLOSE	>@YMD_CLOSE

			IF @TEMP = 0
			BEGIN		
				UPDATE SL_POSCLOSE SET DS_STATUS = 'O'
				 WHERE CD_STORE 	=@CD_STORE
				       AND YMD_CLOSE	=@YMD_CLOSE
				       AND NO_POS	=@NO_POS
				
				SELECT @RESULT = ' '

			END
			ELSE SELECT @RESULT = 'M'
		END
		ELSE SELECT @RESULT = 'X'

	END

	SELECT @YMD_LAST = MAX(YMD_CLOSE)
	   FROM SL_POSCLOSE
	WHERE CD_STORE	=@CD_STORE
	      AND NO_POS	=@NO_POS
	      AND DS_STATUS	='C'

	IF @YMD_LAST IS NULL SET @YMD_LAST = ''



END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE  PROCEDURE POS_SELECT_CASHIER_MGM

	@CD_STORE 	    		VARCHAR(4),
	@YMD_CLOSE			VARCHAR(8),
	@NO_POS			VARCHAR(2),
	@CD_SAWON			VARCHAR(6),
	@AMT_READY			INT OUTPUT,
	@AMT_MID			INT OUTPUT,
	@AMT_ACCT_IN		INT OUTPUT,
	@AMT_ACCT_OUT		INT OUTPUT,
	@AMT_SALE			INT OUTPUT,
	@AMT_TAX			INT OUTPUT,
	@AMT_CASH			INT OUTPUT,
	@AMT_CARD			INT OUTPUT,
	@AMT_CHECK			INT OUTPUT,
	@AMT_TRUST			INT OUTPUT,
	@AMT_GIFT			INT OUTPUT,
	@AMT_POINT			INT OUTPUT,
	@AMT_LOSS			INT OUTPUT,
	@AMT_OKC			INT OUTPUT,
	@DC_MENU			INT OUTPUT,
	@DC_CODE			INT OUTPUT,
	@DC_RECEIPT			INT OUTPUT,
	@DC_MEMBER			INT OUTPUT,
	@DC_ENURI			INT OUTPUT,
	@DC_SPC			INT OUTPUT,
	@DC_VAT			INT OUTPUT,
	@DC_CUT			INT OUTPUT,
	@DC_COUPON			INT OUTPUT,
	@DC_TOTAL			INT OUTPUT,
	@AMT_VOID			INT OUTPUT,
	@AMT_CASHRCP		INT OUTPUT,
	@CNT_CUSTOMER		INT OUTPUT,
	@AMT_AVERAGE		INT OUTPUT,
	@AMT_PRESENT		INT OUTPUT,
	@RCP_BEGIN			VARCHAR(4) OUTPUT,
	@RCP_END			VARCHAR(4) OUTPUT
AS
BEGIN
	
	DECLARE @SEQ	INT

	SELECT @SEQ = ISNULL(MAX(SEQ), 0)
	   FROM SL_CASHIER_MGM
	WHERE CD_STORE	=@CD_STORE
	      AND YMD_CLOSE	=@YMD_CLOSE
	      AND CD_SAWON	=@CD_SAWON
	      AND NO_POS	=@NO_POS
	      AND YN_CLOSE	='N'

	--준비금, 중간출금
	SELECT @AMT_READY	=ISNULL(AMT_READY,0),
		 @AMT_MID		=ISNULL(AMT_MID_CASH,0) + ISNULL(AMT_MID_CHECK,0)
	   FROM SL_CASHIER_MGM
	WHERE CD_STORE	=@CD_STORE	
	      AND YMD_CLOSE	=@YMD_CLOSE
	      AND NO_POS	=@NO_POS
	      AND CD_SAWON	=@CD_SAWON
	      AND SEQ		=@SEQ

	--준비금, 중간출금
	SELECT @AMT_ACCT_IN	=SUM(AMT_PAYIN),
		 @AMT_ACCT_OUT	=SUM(AMT_OUT)
	   FROM SL_ACCT
	WHERE CD_STORE		=@CD_STORE	
	      AND YMD_OCCUR		=@YMD_CLOSE
	      AND CD_SAWON_CHG	=@CD_SAWON
	      AND NO_POS		=@NO_POS
	      AND NO_CLOSE		=0

	SELECT @AMT_READY	=ISNULL(@AMT_READY	,0)
	SELECT @AMT_MID		=ISNULL(@AMT_MID		,0)
	SELECT @AMT_ACCT_IN	=ISNULL(@AMT_ACCT_IN	,0)
	SELECT @AMT_ACCT_OUT	=ISNULL(@AMT_ACCT_OUT	,0)


	SELECT @AMT_TAX		=SUM(AMT_TAX),
		 @AMT_CASH		=SUM(AMT_CASH),
		 @AMT_CARD		=SUM(AMT_CARD),
		 @AMT_CHECK		=SUM(AMT_CHECK),
		 @AMT_TRUST	=SUM(AMT_TRUST),
		 @AMT_GIFT		=SUM(AMT_GIFT),
		 @AMT_POINT		=SUM(AMT_POINT),
		 @AMT_LOSS		=SUM(AMT_LOSS),
		 @AMT_OKC		=SUM(AMT_OKC),
		 @DC_MENU		=SUM(DC_MENU),
		 @DC_CODE		=SUM(DC_CODE),
		 @DC_RECEIPT	=SUM(DC_RECEIPT),
		 @DC_ENURI		=SUM(DC_ENURI),
		 @DC_MEMBER	=SUM(DC_MEMBER),
		 @DC_SPC		=SUM(DC_SPC),
		 @DC_CUT		=SUM(DC_CUT),
		 @DC_VAT		=SUM(DC_VAT),
		 @DC_COUPON	=SUM(DC_COUPON),
		 @DC_TOTAL		=SUM(DC_TOT),
		 @CNT_CUSTOMER	=COUNT(*),
		 @AMT_AVERAGE	=AVG(AMT_SALE),
		 @AMT_CASHRCP	=SUM(AMT_CASHRCP),
		 @RCP_BEGIN		=MIN(NO_RCP),
		 @RCP_END		=MAX(NO_RCP)	 
	   FROM SL_SALE_H
	WHERE CD_STORE	=@CD_STORE	
	      AND YMD_SALE	=@YMD_CLOSE
	      AND NO_POS	=@NO_POS
	      AND CD_SAWON	=@CD_SAWON
	      AND DS_SALE	<>'V'
	      AND NO_CLOSE	=0
	
	SELECT	@AMT_SALE		=ISNULL(@AMT_SALE		,0)
	SELECT	@AMT_TAX		=ISNULL(@AMT_TAX		,0)
	SELECT	@AMT_CASH		=ISNULL(@AMT_CASH		,0)
	SELECT	@AMT_CARD		=ISNULL(@AMT_CARD		,0)
	SELECT	@AMT_CHECK		=ISNULL(@AMT_CHECK	,0)
	SELECT	@AMT_TRUST		=ISNULL(@AMT_TRUST	,0)
	SELECT	@AMT_GIFT		=ISNULL(@AMT_GIFT		,0)
	SELECT	@AMT_POINT		=ISNULL(@AMT_POINT	,0)
	SELECT	@AMT_LOSS		=ISNULL(@AMT_LOSS		,0)
	SELECT	@AMT_OKC		=ISNULL(@AMT_OKC		,0)
	SELECT	@DC_MENU		=ISNULL(@DC_MENU		,0)
	SELECT	@DC_CODE		=ISNULL(@DC_CODE		,0)
	SELECT	@DC_RECEIPT		=ISNULL(@DC_RECEIPT	,0)
	SELECT	@DC_ENURI		=ISNULL(@DC_ENURI		,0)
	SELECT	@DC_MEMBER		=ISNULL(@DC_MEMBER	,0)
	SELECT	@DC_SPC		=ISNULL(@DC_SPC		,0)
	SELECT	@DC_CUT		=ISNULL(@DC_CUT		,0)
	SELECT	@DC_VAT		=ISNULL(@DC_VAT		,0)
	SELECT	@DC_COUPON		=ISNULL(@DC_COUPON	,0)
	SELECT	@DC_TOTAL		=ISNULL(@DC_TOTAL		,0)
	SELECT	@CNT_CUSTOMER	=ISNULL(@CNT_CUSTOMER	,0)
	SELECT	@AMT_AVERAGE	=ISNULL(@AMT_AVERAGE	,0)
	SELECT	@AMT_CASHRCP	=ISNULL(@AMT_CASHRCP	,0)
	SELECT	@RCP_BEGIN		=ISNULL(@RCP_BEGIN		,'')
	SELECT	@RCP_END		=ISNULL(@RCP_END		,'')

	SELECT @AMT_VOID		=SUM(AMT_SALE)	 
	   FROM SL_SALE_H
	WHERE CD_STORE	=@CD_STORE	
	      AND YMD_SALE	=@YMD_CLOSE
	      AND NO_POS	=@NO_POS
	      AND CD_SAWON	=@CD_SAWON
	      AND DS_SALE	='V'
	      AND NO_CLOSE	=0
	
	SELECT @AMT_VOID		=ISNULL(@AMT_VOID,0)
	SELECT @AMT_PRESENT	= @AMT_READY - @AMT_MID + @AMT_ACCT_IN - @AMT_ACCT_OUT + @AMT_CASH + @AMT_GIFT + @AMT_CHECK 
	SELECT @AMT_SALE		= @AMT_CASH +  @AMT_CARD + @AMT_CHECK +  @AMT_TRUST + @AMT_POINT + @AMT_LOSS + @AMT_OKC

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE  PROCEDURE POS_SELECT_POS_MGM

	@CD_STORE 	    		VARCHAR(4),
	@YMD_CLOSE			VARCHAR(8),
	@NO_POS			VARCHAR(2),
	@AMT_SALE			INT OUTPUT,
	@AMT_TAX			INT OUTPUT,
	@AMT_CASH			INT OUTPUT,
	@AMT_CARD			INT OUTPUT,
	@AMT_CHECK			INT OUTPUT,
	@AMT_TRUST			INT OUTPUT,
	@AMT_GIFT			INT OUTPUT,
	@AMT_POINT			INT OUTPUT,
	@AMT_LOSS			INT OUTPUT,	--손실금액
	@AMT_OKC			INT OUTPUT,	--손실금액
	@DC_TOTAL			INT OUTPUT,	--할인금액
	@AMT_VOID			INT OUTPUT,	--매출취소
	@CNT_CUSTOMER		INT OUTPUT,	--고객수 
	@CNT_CASHIER		INT OUTPUT,	--계산원수
	@AMT_LACK			INT OUTPUT,	--과부족금액
	@AMT_CASHRCP		INT OUTPUT,   -- 현금영수증
	@NOT_CLOSE			VARCHAR(6) OUTPUT,	--마감이 계산원코드
	@DS_STATUS			VARCHAR(1) OUTPUT	--마감상태(O:오픈, C:마감)
AS
BEGIN

	SELECT 	@NOT_CLOSE		=MAX(CD_SAWON)
	   FROM	SL_SALE_H
	WHERE  CD_STORE	=@CD_STORE
	      AND  YMD_SALE	=@YMD_CLOSE
	      AND  NO_POS	=@NO_POS
	      AND  NO_CLOSE	=0
	

	SELECT	@AMT_SALE		=SUM(AMT_SALE),
			@AMT_TAX		=SUM(AMT_TAX),
			@AMT_CASH		=SUM(AMT_CASH),
			@AMT_CARD		=SUM(AMT_CARD),
			@AMT_CHECK		=SUM(AMT_CHECK),
			@AMT_TRUST		=SUM(AMT_TRUST),
			@AMT_GIFT		=SUM(AMT_GIFT),
			@AMT_POINT		=SUM(AMT_POINT),
			@AMT_LOSS		=SUM(AMT_LOSS),
			@AMT_OKC		=SUM(AMT_OKC),
			@DC_TOTAL		=SUM(DC_MENU+DC_RECEIPT+DC_MEMBER+DC_CODE+DC_SPC+DC_ENURI + DC_COUPON + DC_CUT + DC_VAT),
			@AMT_VOID		=SUM(AMT_VOID),
			@CNT_CUSTOMER	=SUM(CNT_CUSTOMER),
			@CNT_CASHIER	=COUNT(*),
			@AMT_LACK		=SUM(AMT_LACK),
			@AMT_CASHRCP	=SUM(AMT_CASHRCP)
	   FROM  SL_CASHIER_MGM
	WHERE  CD_STORE	=@CD_STORE
	      AND  YMD_CLOSE	=@YMD_CLOSE
	      AND  NO_POS	=@NO_POS

	SELECT  @DS_STATUS	=DS_STATUS
	   FROM  SL_POSCLOSE
	WHERE  CD_STORE	=@CD_STORE
	      AND  YMD_CLOSE	=@YMD_CLOSE
	      AND  NO_POS	=@NO_POS	

	SELECT @AMT_SALE		=ISNULL(@AMT_SALE,0)
	SELECT @AMT_TAX		=ISNULL(@AMT_TAX,0)
	SELECT @AMT_CASH		=ISNULL(@AMT_CASH,0)
	SELECT @AMT_CARD		=ISNULL(@AMT_CARD,0)
	SELECT @AMT_CHECK	=ISNULL(@AMT_CHECK,0)
	SELECT @AMT_TRUST	=ISNULL(@AMT_TRUST,0)
	SELECT @AMT_GIFT		=ISNULL(@AMT_GIFT,0)
	SELECT @AMT_POINT		=ISNULL(@AMT_POINT,0)
	SELECT @AMT_LOSS		=ISNULL(@AMT_LOSS,0)
	SELECT @AMT_OKC		=ISNULL(@AMT_OKC,0)
	SELECT @DC_TOTAL		=ISNULL(@DC_TOTAL,0)
	SELECT @AMT_VOID		=ISNULL(@AMT_VOID,0)
	SELECT @CNT_CUSTOMER	=ISNULL(@CNT_CUSTOMER,0)
	SELECT @CNT_CASHIER	=ISNULL(@CNT_CASHIER,0)
	SELECT @AMT_LACK		=ISNULL(@AMT_LACK,0)
	SELECT @AMT_CASHRCP	=ISNULL(@AMT_CASHRCP,0)
	SELECT @DS_STATUS		=ISNULL(@DS_STATUS,'X')
	SELECT @NOT_CLOSE		=ISNULL(@NOT_CLOSE,'X')
	

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

