USE [MimsDB]
GO
/****** Object:  StoredProcedure [dbo].[Up_MSZ430_C_Insert001]    Script Date: 02/01/2016 17:33:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*===========================================================================
PROC  명: Mims.dbo.Up_MSZ060_C_Insert001
PROC기능: 지급보류 월정산
입 력 변수: 
Return  값: (상태반환 code로써 return을 사용할 경우 사용)  
작성  일자: 2011/09/23
작성자     : 
최종수정일: 2012/09/26 전체 로직 수정...
최종수정자: 
수정  이력: 
실행  예제: 


Up_MSZ430_C_Insert001 @p_JumCd=N'01',@p_YM=N'201305',@p_BrandCd=N'',@p_UserId=N'Admin'

 ============================================================================*/
ALTER PROCEDURE [dbo].[Up_MSZ430_C_Insert001]
		@p_JumCd		VARCHAR(2)		/*	점코드			varchar(2)		*/
	,	@p_YM			VARCHAR(6)		/*	년월			varchar(6)		*/
	,	@p_BrandCd		VARCHAR(6)		/*	브랜드			varchar(6)		*/	
	,	@p_USERID		NVARCHAR(13)	= ''
	,	@p_RtnCd		INT	=0  OUTPUT
	
	

AS

/*
 
 롯데몰 매출수수료  타입.
  
1)	소품(명품멀티샵)의 경우
?	기준 : 16%
?	프라다,구찌,페라가모,루이비통,샤넬 13%
2)	편의점 세븐일레븐
?	기본: 12%
?	담배: 5%
3)	월매출액에 따른 차등 적용
?	대상 브랜드: 알마마르소, 오토오아시스,북메카
?	알마마르소:  ~1천 13%, ~2천 14%, 2천초과 15%
?	오토오아시스: ~4천 8%, ~5천 9%, ~6천 10%,~8천 11%, ~1억 12%, ~1.3억 13%,1.3억초과 14%
?	북메카: 2천이하 17%,20~25 18%,25~30 19%,3천초과 20%,
4)	매출구간별 수수료 책정(구간별 계산후 합산)
?	대상브랜드: 코코이찌방야,TGIF,노스텔지어
?	코코이찌방야: ~1억 17%, 1억~ 20%
?	TGIF: ~2억 10%, 2~3억 15%, 3억~ 17%
?	노스탤지어: 17% (月216,670천원 이상~ 23%)
5)	고정매출수수료
?	그외 모든 브랜드

*/
DECLARE
		@vBrandCd			VARCHAR(06), 
		@vPmCd				VARCHAR(08), 
		@vProfMngUnit		VARCHAR(01), 
		@vSaleAmt			NUMERIC(13,0),
		@vChaSaleAmt		NUMERIC(13,0),
		@vProfAmt			NUMERIC(13,0),
		@vSaleEnuri			NUMERIC(13,0),
		@vProfRatio			NUMERIC(5,2),
		@vCustCd			VARCHAR(06),
		@vBrandClsCd		VARCHAR(06),
		@vLicenseAmt		NUMERIC(13,0),
		@vGiftLicenseAmt	NUMERIC(13,0),
		@vLotteBrandPoint	NUMERIC(13,0),
		@vSaleMinAmt		NUMERIC(13,0),
		@vSeq				INT,
		@vMBBrandCd			VARCHAR(06),
		@vMBAcceptDt		VARCHAR(08),
		@vMBSEQ				VARCHAR(08),
		@vMBSeizeAmt		NUMERIC(13,0),
		@vMBSeizeDt			VARCHAR(08),
		@vMBCancelDt        VARCHAR(08),
		@vDedAmt2			NUMERIC(13,0),
		@vDedAmt3			NUMERIC(13,0),
		@vMEBYM				VARCHAR(08),
		@vMEBJumCd			VARCHAR(02),
		@vMEBBrandCd		VARCHAR(06),
		@vMEBGubun			VARCHAR(01),
		@vDEDUCTAmt			NUMERIC(13,0),
		@vGUBUN				VARCHAR(01),
		@vSEIAcceptDt		VARCHAR(08),
		@vSEISEQ			VARCHAR(08),
		@vSEIGubun			VARCHAR(02),
		@vSEIBrandCd		VARCHAR(06),
		@vMijiAmt		    NUMERIC(13,0),
		@vDeferAmt		    NUMERIC(13,0),
		@vSEISeizeAmt		NUMERIC(13,0),
		@vSEIDeferAmt		NUMERIC(13,0);

DECLARE @v_CloseYN			VARCHAR(1); -- 마감여부
DECLARE @v_CloseYN_B		VARCHAR(1);
DECLARE @v_MaxYM			VARCHAR(6);
DECLARE @p_EndDate			VARCHAR(8);
--SET @vDedAmt2 = 0;
--SET @vDedAmt3 = 0;

--// 임시테이블 선언: 일자별 수수료율
DECLARE @BrandProfRatio TABLE
(
		YMD			VARCHAR(8)		/* 년월일		*/
	,	JumCd		VARCHAR(2)		/* 점코드		*/
	,	BrandCd		VARCHAR(6)		/* 브랜드		*/	
	,	PmCd		VARCHAR(8)		/* 품목			*/
	,	ProfRatio	NUMERIC(5,2)	/* 수수료율		*/
	,	ProfDcRatio	NUMERIC(5,2)	/* 할인수수료율	*/
	,	SaleMinAmt	NUMERIC(15,0)	/* 매출하한금액	*/
);

--// 임시테이블 선언: 매출금액별 수수료
DECLARE @BrandSaleProfRatio TABLE
(
		BrandCd		VARCHAR(6)
	,	Seq			INT
	,	SaleAmt		NUMERIC(10,0)
	,	ProfRatio	NUMERIC(4,2)
	,	JunSeq		INT	
);

BEGIN

	--// 매출구간별 수수료 계산시, 생성월의 마지막일자를 비교한다.
	SET @p_EndDate = CONVERT(VARCHAR,DATEADD(D,-1,DATEADD(M,1,@p_YM+'01')),112);

	--// 월회계마감 확인
	BEGIN 
		SELECT	@v_CloseYN = ISNULL(CloseYN,'0') -- 0: 확정된 데이타(오류), 1: 정상 , 9:확정할 데이타가 없음(오류) , 8: 월마감
		FROM	ClosingTB				
		WHERE	JumCd		= @p_JumCd
		AND		YMD			=	@p_YM		
		AND		CloseType	= 'S'			/*월마감*/
	END

	IF @v_CloseYN = '1'
		BEGIN
			SET @p_RtnCd = 8;
			return 
		END
		
	--// 지급보류월정산 확인
	BEGIN 
		SELECT	@v_CloseYN_B = ISNULL(CloseYN,'0') -- 0: 확정된 데이타(오류), 1: 정상 , 9:확정할 데이타가 없음(오류) , 8: 월마감
		FROM	ClosingTB		
		WHERE	JumCd		= @p_JumCd
		AND		YMD			=	@p_YM		
		AND		CloseType	= 'B'			/*월마감*/
	END

	IF @v_CloseYN_B = '1'
		BEGIN
			SET @p_RtnCd = 9;
			return 
		END

	--// 임시테이블 선언: 매출구간별 수수료율 적용브랜드 조회
	BEGIN
		SELECT	BrandCd
		INTO	#TB_BrandCd		--임시테이블
		FROM	JumBrandMst
		WHERE	JumCd = @p_JumCd
		AND		ProfMngUnit IN ('3','4')	/*품번(매출합계), 품번(매출차등)*/
	END

	--// 품번, 품번+품목 :브랜드의 일자별 수수료율을 구한다.
	BEGIN
		INSERT	INTO	@BrandProfRatio
		SELECT	A.YMD
		,		B.JumCd
		,		B.BrandCd
		,		B.PmCd
		,		MAX(CASE  WHEN A.YMD BETWEEN B.EvtStrDate AND B.EvtEndDate THEN B.ProfRatio ELSE 0 END)  AS ProfRatio	-- 수수료율
		,		MAX(CASE  WHEN A.YMD BETWEEN C.StrDate AND C.EndDate THEN C.ProfDcRatio ELSE 0 END) AS ProfDcRatio		-- 할인 수수료율
		,		MAX(D.SaleMinAmt) AS SaleMinAmt -- 매출하한금액	
		FROM	CALENDAR A
		CROSS	JOIN BrandPmProfMst B --ON A.YMD BETWEEN B.EvtStrDate AND B.EvtEndDate
		LEFT	OUTER JOIN BrandProfDcMst C 
		ON		A.YMD BETWEEN C.StrDate AND C.EndDate 
		AND		B.BrandCd = C.BrandCd
		LEFT	OUTER JOIN JumBrandMst D 
		ON		B.BrandCd = D.BrandCd 
		AND		B.JumCd = D.JumCd
		WHERE	A.YMD LIKE @p_YM + '%'
		--AND		B.BrandCd LIKE @p_BrandCd + '%'
		AND		B.BrandCd NOT IN (SELECT BrandCd FROM #TB_BrandCd)
		GROUP	BY A.YMD,B.JumCd,B.BrandCd,B.PmCd
		ORDER	BY B.BrandCd,A.YMD	
	END

	--// 매출금액별 수수료율 테이블을 생성한다.
	BEGIN
		INSERT	INTO @BrandSaleProfRatio
		SELECT	BrandCd
		,		Seq + 1 AS Seq 
		,		SaleAmt
		,		ProfRatio
		,		Seq AS JunSeq
		FROM	BrandSaleSecProfMst
		WHERE	JumCd = @p_JumCd
		--AND		BrandCd	LIKE	@p_BrandCd + '%'
		AND		BrandCd IN  (SELECT BrandCd FROM #TB_BrandCd)

		UNION 
		
		SELECT	BrandCd
		,		1 AS Seq 
		,		0 AS SaleAmt
		,		ProfRatio
		,		0 AS JunSeq				
		FROM	BrandPmProfMst
		WHERE	JumCd = @p_JumCd
		--AND		BrandCd	LIKE @p_BrandCd	+ '%'
		AND		BrandCd IN (SELECT BrandCd FROM #TB_BrandCd)
		AND		PmCd = '00000000'
		AND		@p_EndDate BETWEEN EvtStrDate AND EvtEndDate

	END
	
	--// 해당 월이 존재하는 경우 삭제
	BEGIN		
		IF EXISTS (
					SELECT	1
					FROM    SeizeSUM
					WHERE	YM = @p_YM        /* 년월         */
					AND		JumCd = @p_JumCd      /* 점코드       */
					--AND		BrandCd LIKE @p_BrandCd  + '%'   /* 브랜드코드   */
				 )
			BEGIN
				DELETE	SeizeSUM		--해당 월의 수수료테이블 삭제
				WHERE	YM = @p_YM			/* 년월         */
				AND		JumCd = @p_JumCd      /* 점코드       */
				--AND		BrandCd	LIKE @p_BrandCd	+ '%'   /* 브랜드코드   */
			END
	END

	BEGIN
		
		DECLARE CURSOR_001 CURSOR FOR
				
				SELECT BrandCd
					 , PmCd
					 , ProfMngUnit
					 , SUM(SaleAmt) SaleAmt
					 , SUM(ProfAmt) ProfAmt
					 , SUM(SaleEnuri) SaleEnuri
				  FROM (

						SELECT 	BrandCd
						,		'00000000' AS PmCd
						,		'0' AS ProfMngUnit		
						,		SUM(SaleAmt) AS SaleAmt
						,		SUM(ProfAmt) AS ProfAmt
						,		SUM(SaleEnuri) AS SaleEnuri
						FROM	(
								SELECT	A.BrandCd                             /* 브랜드코드   */
								,		A.PmCd                                /* 폼목코드     */
								,		ISNULL(SUM(A.SaleAmt),0) AS SaleAmt             /* 판매금액     */
								,		ISNULL(FLOOR(SUM(A.SaleAmt*((CASE WHEN B.ProfRatio IS NULL 
													 			   THEN dbo.Uf_GetBrandProfRatio(A.JumCd, A.BrandCd)
													 			   ELSE B.ProfRatio 
															  END) - ISNULL(B.ProfDcRatio,0))/100)),0) AS ProfAmt	/* 이익액 */
								,		ISNULL(SUM(SaleEnuri),0) AS SaleEnuri          /* 판매에누리   */									
								FROM	DailyBrandPmSUM	A										
								LEFT 	OUTER JOIN @BrandProfRatio B 
								ON		A.YMD = B.YMD 
								AND		A.JumCd = B.JumCd 
								AND		A.BrandCd = B.BrandCd
								AND		A.PmCd = B.PmCd
								WHERE	A.YMD LIKE  @p_YM+ '%'    /* 년월         */
								AND		A.JumCd = @p_JumCd				/* 점코드       */	
								--AND		A.BrandCd LIKE @p_BrandCd  + '%'		/* 브랜드코드   */
								AND		A.BrandCd NOT IN (SELECT BrandCd FROM #TB_BrandCd)
								GROUP	BY A.BrandCd, A.PmCd								
								) X 
						GROUP 	BY BrandCd

						UNION ALL
						SELECT	A.BrandCd
						,		'00000000' AS PmCd 
						,		B.ProfMngUnit
						, 		SUM(A.SaleAmt) AS SaleAmt
						,		0 AS ProfAmt
						,		SUM(A.SaleEnuri) AS SaleEnuri
						FROM 	DailyBrandPmSUM A
						INNER 	JOIN JumBrandMst B 
						ON		A.JumCd = B.JumCd 
						AND		A.BrandCd = B.BrandCd
						WHERE 	B.BrandCd IN (SELECT BrandCd FROM #TB_BrandCd) --// 3:월매출액 합계에 따라 수수료율적용	4:매출구간별 수수료 책정
						AND 	A.JumCd = @p_JumCd
						--AND 	A.BrandCd LIKE @p_BrandCd +'%'
						AND 	A.YMD LIKE @p_YM + '%'
						GROUP 	BY A.BrandCd,	B.ProfMngUnit
						
						-- 2016.01.28 월최저임대료 등록되어 있는 브랜드 추가 
						UNION ALL     
						SELECT	BrandCd
						,		'00000000'		AS PmCd
						,		CASE WHEN ProfMngUnit = '3' OR ProfMngUnit = '4' THEN ProfMngUnit ELSE '0' END		AS ProfMngUnit		
						,		0				AS SaleAmt
						,		0				AS ProfAmt
						,		0				AS SaleEnuri
						  FROM	JumBrandMst
						 WHERE	JumCd = @p_JumCd
						   --AND  BrandCd LIKE @p_BrandCd +'%'
						   and  ISNULL(SaleMinAmt,0) > 0
					)AA
					GROUP BY BrandCd, PmCd, ProfMngUnit
					ORDER BY BrandCd
		
		OPEN CURSOR_001

		FETCH NEXT FROM CURSOR_001 INTO
			@vBrandCd,
			@vPmCd,
			@vProfMngUnit,
			@vSaleAmt,
			@vProfAmt,
			@vSaleEnuri;

		WHILE @@FETCH_STATUS = 0 BEGIN

			IF @vSaleAmt >= 0 -- 2016.01.28 이지현 매출이 0원일 경우에도 월최저임대료가 반영되도록 수정
				BEGIN
					--// 품번/품번+품목별 매출수수료 계산 : 일자별 수수료율을 비교하여 계산한다.
					IF (@vProfMngUnit = '0') 
						BEGIN
							SET @vLicenseAmt = @vProfAmt;
						END
					--// 매출구간별 매출수수료 계산: 매출구간별 수수료율을 구한 뒤 수수료적용방신(매출합계 또는 매출차등)에 따라서 계산한다.
					ELSE
						BEGIN
							-->> 2012.08.07 LYG 매출차등 계산식 변경
							--// 품번(매출합계)
							IF @vProfMngUnit = '3'
								BEGIN
									BEGIN
										SELECT @vProfRatio = dbo.Uf_GetBrandProfRatio(@p_JumCd, @vBrandCd)
									END
									
									SET @vLicenseAmt = FLOOR(@vSaleAmt * @vProfRatio / 100);
								END
							--// 품번(매출차등)
							ELSE
								BEGIN
									-- 차등에 따라 정해진 계약수수료 적용
									SELECT @vLicenseAmt = FLOOR(SUM(CASE WHEN @vSaleAmt > A.MaxSaleAmt 
																		THEN (A.MaxSaleAmt - A.MinSaleAmt) * A.ProfRatio / 100
																		ELSE CASE WHEN @vSaleAmt <= A.MaxSaleAmt 
																					THEN CASE WHEN @vSaleAmt <= A.MinSaleAmt 
																								THEN 0
																								ELSE (@vSaleAmt - A.MinSaleAmt) * A.ProfRatio / 100
																							  END
																					ELSE (@vSaleAmt - A.MinSaleAmt) * A.ProfRatio / 100
																				  END
																	END))
									FROM 	(
												SELECT 	A.SaleAmt AS MinSaleAmt
												, 		ISNULL(B.SaleAmt, 9999999999) AS MaxSaleAmt
												, 		A.ProfRatio
												FROM 	@BrandSaleProfRatio A 
												LEFT 	OUTER JOIN @BrandSaleProfRatio B 
												ON 		A.BrandCd = B.BrandCd 
												AND 	A.Seq = B.JunSeq
												WHERE	A.BrandCd = @vBrandCd
											) A
								END
							--<< 2012.08.07 LYG 
						END	
						
					
					--// 매출수수료가 매출하한금액보다 작은 경우, 매출하한 금액으로 적용한다.
					BEGIN
						--IF @p_JumCd <> '03'  --2015.07.02 이지현 롯데월드몰도 최저임대로 적용하기로 함.
						--BEGIN
							SELECT	@vSaleMinAmt = SaleMinAmt 
							FROM	JumBrandMst
							WHERE	JumCd = @p_JumCd
							AND		BrandCd = @vBrandCd				
							
							IF @vSaleMinAmt > @vLicenseAmt
								BEGIN
									SET @vLicenseAmt = @vSaleMinAmt;
								END
						--END
					END	
				END
			--// 매출금액이 -일 경우 -금액에 대한 매출수수료율을 생성.
			ELSE
				BEGIN
					BEGIN
						SELECT @vProfRatio = dbo.Uf_GetBrandProfRatio(@p_JumCd, @vBrandCd)
					END
					
					SET @vLicenseAmt = FLOOR(@vSaleAmt * @vProfRatio / 100);
				END

			
			
			--IF @vSaleAmt <> 0
			--	BEGIN
					BEGIN
						SELECT	@vCustCd = JB.CustCd			-- 거래처코드
						,		@vBrandClsCd = JB.BrandClsCd    -- 상품군코드
						,		@vGiftLicenseAmt = ISNULL(TS.GiftLicenseAmt,0)
						,		@vLotteBrandPoint = ISNULL(PL.LotteBrandPoint,0)
						FROM	JumBrandMst JB
						LEFT	OUTER JOIN (	SELECT	a.BrandCd
												,		FLOOR(SUM(a.TradAmt *b.GiftRate/100)) GiftLicenseAmt
												FROM	DailyTradSUM a 
												INNER	JOIN GiftMst b 
												ON		a.TradSCd = b.GiftCd 
												AND		b.GiftRate > 0
												INNER	JOIN JumBrandMst C 
												ON		A.JumCd = C.JumCd 
												AND		A.BrandCd = C.BrandCd 
												AND		C.GiftYN = '1'
												WHERE	a.JumCd = @p_JumCd	
												AND		a.BrandCd = @vBrandCd
												AND		a.YMD LIKE @p_YM + '%'
												AND		a.TradCd = '04' -- 적립
												AND		A.Gbn = '10'
												GROUP	BY a.BrandCd
											) AS TS 
						ON		JB.BrandCd = TS.BrandCd
						LEFT	OUTER JOIN (	SELECT	BrandCd
												,		FLOOR(SUM(CASE WHEN AddGbn = '1' THEN EvtPoint ELSE -EvtPoint END)) LotteBrandPoint
												FROM	PointLog
												WHERE	JumCd = @p_JumCd	
												AND		BrandCd = @vBrandCd
												AND		SaleDate LIKE @p_YM + '%'
												AND		DealGbn = '10' -- 적립
												GROUP	BY BrandCd
											) AS PL 
						ON		JB.BrandCd = PL.BrandCd
						WHERE	JB.BrandCd = @vBrandCd
					END				
					BEGIN
						--사후고객예치금S
						BEGIN
							SET @vDedAmt2 = 0;
						END
						DECLARE CURSOR_002 CURSOR FOR
						SELECT  ISNULL(BrandCd,'') AS BrandCd
							   ,ISNULL(AcceptDt,'') AS AcceptDt
							   ,ISNULL(SEQ,'') AS SEQ
							   ,ISNULL(SeizeAmt,0) AS SeizeAmt
							   ,ISNULL(SeizeDt,'') AS SeizeDt
							   ,ISNULL(CancelDt,'') AS CancelDt
						  FROM MemberBalanceMst
						 WHERE JumCd = @p_JumCd
						   AND @p_YM BETWEEN SUBSTRING(SeizeDt,1,6) AND SUBSTRING(CancelDt,1,6)
						   AND BrandCd = @vBrandCd
						   AND CalGubun = '0'
						 
						OPEN CURSOR_002
						FETCH NEXT FROM CURSOR_002 INTO
							@vMBBrandCd,
							@vMBAcceptDt,
							@vMBSEQ,
							@vMBSeizeAmt,
							@vMBSeizeDt,
							@vMBCancelDt;
						WHILE @@FETCH_STATUS = 0 BEGIN					   
						IF SUBSTRING(@vMBSeizeDt,1,6) = @p_YM
						BEGIN
							SET @vDedAmt2 = @vMBSeizeAmt
						END
						ELSE-- IF @vMBSeizeDt <> '' AND ISNULL(@vMBSeizeDt,'') <> @p_YM						
						BEGIN
							SET @vDedAmt2 = 0;		
							IF EXISTS (
										SELECT	1
										FROM    MemberBalanceMove
										WHERE	YM = @p_YM        /* 년월         */
										AND		JumCd = @p_JumCd      /* 점코드       */
										AND		BrandCd = @vBrandCd   /* 브랜드코드   */
									 )
								BEGIN						
									DECLARE CURSOR_003 CURSOR FOR
										SELECT	YM,JumCd,BrandCd,Gubun
										FROM    MemberBalanceMove
										WHERE	YM = @p_YM        /* 년월         */
										AND		JumCd = @p_JumCd      /* 점코드       */
										AND		BrandCd = @vBrandCd   /* 브랜드코드   */
										
									OPEN CURSOR_003
									FETCH NEXT FROM CURSOR_003 INTO
										@vMEBYM,
										@vMEBJumCd,
										@vMEBBrandCd,
										@vMEBGubun;
									WHILE @@FETCH_STATUS = 0 BEGIN
										DELETE	MemberBalanceMove		--해당 월의 수수료테이블 삭제
										WHERE	YM = @vMEBYM			/* 년월         */
										AND		JumCd = @vMEBJumCd      /* 점코드       */
										AND		BrandCd	= @vMEBBrandCd   /* 브랜드코드   */
										AND     Gubun = @vMEBGubun
									FETCH NEXT FROM CURSOR_003 INTO
										@vMEBYM,
										@vMEBJumCd,
										@vMEBBrandCd,
										@vMEBGubun;
									END
									CLOSE CURSOR_003
									DEALLOCATE CURSOR_003							
								END					
							
							BEGIN							
								DECLARE CURSOR_004 CURSOR FOR
									SELECT A.GUBUN,A.AMT
									  FROM(
											SELECT '1' AS GUBUN,@vSaleAmt*-1     AS AMT UNION ALL
											--심재권 책임님 요청으로 원복 2014/02/05 
											--SELECT	'1' AS GUBUN,(@vSaleAmt+SUM(INAMT))*-1 AS AMT
											--FROM	ATMINFO WITH (NOLOCK)
											--WHERE	JUMCD = @p_JumCd
											--AND		INDATE LIKE @p_YM+'%'
											--AND		BRANDCD = @vBrandCd
											--AND		JUMCD IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
											--GROUP	BY JUMCD, SUBSTRING(INDATE,1,6), BRANDCD
											--UNION ALL
											SELECT '2' AS GUBUN,@vLicenseAmt*-1     AS AMT UNION ALL
											SELECT '3' AS GUBUN,ISNULL(SUM(GojiAmt),0)  AS AMT									-- 70.관리비
											  FROM GojiData WITH (NOLOCK)
											 WHERE JumCd = @p_JumCd
											   AND YM = @p_YM
											   AND BRANDCD = @vBrandCd
											   AND JumCd IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
									       ) A
				                      WHERE A.AMT <> 0
								 
								 OPEN CURSOR_004
								FETCH NEXT FROM CURSOR_004 INTO
									@vGUBUN,
									@vDEDUCTAmt;
								WHILE @@FETCH_STATUS = 0 BEGIN							 
									
									INSERT INTO MemberBalanceMove(YM,JumCd,BrandCd,Gubun,SeizeDt,CancelDt,Dt,Amt,LinceseGubun,AcceptDt,SEQ,RegUserId,RegDt,UpdUserId,UpdDt)VALUES
									(@p_YM,@p_JumCd,@vBrandCd,@vGUBUN,@vMBSeizeDt,@vMBCancelDt,CONVERT(VARCHAR, GETDATE(), 112),@vDEDUCTAmt,'1',@vMBAcceptDt,@vMBSEQ,@p_USERID,GETDATE(),@p_USERID,GETDATE())
								
								FETCH NEXT FROM CURSOR_004 INTO
									@vGUBUN,
									@vDEDUCTAmt;
							END
							CLOSE CURSOR_004
							DEALLOCATE CURSOR_004 
						  END				
						END
						FETCH NEXT FROM CURSOR_002 INTO
							@vMBBrandCd,
							@vMBAcceptDt,
							@vMBSEQ,
							@vMBSeizeAmt,
							@vMBSeizeDt,
							@vMBCancelDt;
						END
						CLOSE CURSOR_002
						DEALLOCATE CURSOR_002						
					END					
					--사후고객예치금E
					
					--채권가압류S
					BEGIN
						BEGIN
							SET @vDedAmt3 = 0;
						END
					DECLARE CURSOR_005 CURSOR FOR
					/*정산년월보다 작거나 같은 채권자중*/
					SELECT  A.AcceptDt
						   ,A.SEQ
						   ,A.Gubun
						   ,C.BrandCd
					  FROM SeizeMst A
					  LEFT OUTER JOIN SeizeDetail B ON A.AcceptDt = B.AcceptDt AND A.SEQ = B.SEQ
					  LEFT OUTER JOIN ( SELECT  A.BrandCd,B.CustCd,B.BizRegNo
										  FROM JumBrandMst A
										  LEFT OUTER JOIN CustMst B ON A.CustCd = B.CustCd
										 WHERE A.JumCd = @p_JumCd
										   AND JumCd IN (SELECT JumCd FROM dbo.[Uf_UserJum_TB](@p_USERID)))C ON B.BizRegNo = C.BizRegNo
					 WHERE SeizeCon = '1'	--압류
					   AND C.BrandCd = @vBrandCd
					   AND SUBSTRING(A.SeizeDt,1,6) <= @p_YM	
					 OPEN CURSOR_005
					FETCH NEXT FROM CURSOR_005 INTO
						@vSEIAcceptDt,
						@vSEISEQ,
						@vSEIGubun,
						@vSEIBrandCd;
					WHILE @@FETCH_STATUS = 0 BEGIN
						/*무제한압류*/
						
						--print '구분 : '+ @vSEIGubun
						IF @vSEIGubun = '1'
								BEGIN
									BEGIN		
										IF EXISTS (
													SELECT	1
													FROM    SeizeMove
													WHERE	YM = @p_YM        /* 년월         */
													AND     AcceptDt     = @vSEIAcceptDt
													AND     SEQ          = @vSEISEQ
													AND		JumCd        = @p_JumCd      /* 점코드       */
													AND		BrandCd      = @vSEIBrandCd   /* 브랜드코드   */
													AND     LinceseGubun = '1'
												 )
											BEGIN
												DELETE	SeizeMove		--해당 월의 수수료테이블 삭제
												WHERE	YM = @p_YM        /* 년월         */
												AND     AcceptDt = @vSEIAcceptDt
												AND     SEQ      = @vSEISEQ
												AND		JumCd    = @p_JumCd      /* 점코드       */
												AND		BrandCd  = @vSEIBrandCd   /* 브랜드코드   */
												AND     LinceseGubun = '1'
											END
									END
									
									SELECT	@vMijiAmt = SUM(A.CASHIAMT + A.GIFTAMT + A.CARDAMT + A.POINTAMT) - (@vLicenseAmt + @vGiftLicenseAmt) - SUM(A.GOJIAMT) - SUM(A.MiddleAmt)--03.미지급금
									FROM	(
											SELECT	A.JumCd			--점코드	
											,		A.YM			--년월
											,		A.BRANDCD					
											,		0 AS CASHIAMT
											,		0 AS GIFTAMT
											,		0 AS CARDAMT
											,		0 AS POINTAMT
											,		0									AS LICENSEAMT		--수수료금액						
											,		0								AS GIFTLICENSEAMT	--상품권수수료액	
											,		0 AS GOJIAMT
											,		0 AS DEDAMT			--기타공제금액
											,		SUM(MiddleAmt)	AS MiddleAmt		--중간지급액(2013.03.21)								
											FROM	LicenseSaleSUM_Middle A with(nolock)			--중간수수료매출집계
											WHERE	A.JUMCD = @p_JumCd
											AND		A.YM = @p_YM
											AND		A.BRANDCD = @vSEIBrandCd
											AND		A.JUMCD IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
											GROUP	BY A.JUMCD, A.YM, A.BRANDCD
											UNION ALL
											SELECT	A.JUMCD
											,		A.YM
											,		A.BRANDCD
											,		ISNULL(ATM,0) +ISNULL(MEMOIRS,0) CASHIAMT				-- 10.현금
											,		0 AS GIFTAMT
											,		0 AS CARDAMT
											,		0 AS POINTAMT
											,		0 AS LICENSEAMT
											,		0 AS GIFTLICENSEAMT
											,		0 AS GOJIAMT
											,		0 AS DEDAMT
											,		0 AS MiddleAmt
											FROM	(
													SELECT	JUMCD
													,		SUBSTRING(INDATE,1,6) AS YM
													,		BRANDCD
													,	SUM(CASE WHEN REGGBN = '0' THEN INAMT END) ATM		--ATM
													,	SUM(CASE WHEN REGGBN = '1' THEN INAMT END) MEMOIRS	--수기
													FROM	ATMINFO WITH (NOLOCK)
													WHERE	JUMCD = @p_JumCd
													AND		INDATE LIKE @p_YM+'%'
													AND		BRANDCD = @vSEIBrandCd
													AND		JUMCD IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
													GROUP	BY JUMCD, SUBSTRING(INDATE,1,6), BRANDCD
													) A
											UNION ALL
											SELECT 	JUMCD
											,		SUBSTRING(YMD,1,6) AS YM
											,		BRANDCD
											,		0 AS CASHIAMT
											,		SUM(CASE WHEN GBN = '10' THEN TRADAMT END) AS  GIFTAMT	-- 20.상품권
											,		0 AS CARDAMT
											,		0 AS POINTAMT
											,		0 AS LICENSEAMT
											,		0 AS GIFTLICENSEAMT
											,		0 AS GOJIAMT
											,		0 AS DEDAMT
											,		0 AS MiddleAmt
											FROM	DAILYTRADSUM WITH (NOLOCK)
											WHERE	TRADCD = '04'  -- 타사상품권
											AND		JUMCD = @p_JumCd
											AND		YMD LIKE @p_YM+'%'
											AND		BRANDCD = @vSEIBrandCd
											AND		JUMCD IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
											GROUP	BY JUMCD, SUBSTRING(YMD,1,6), BRANDCD
											UNION ALL
											SELECT	JUMCD
											,		SUBSTRING(SALEDATE,1,6) AS YM
											,		BRANDCD
											,		0 AS CASHIAMT
											,		0 AS GIFTAMT
											,		ISNULL(SUM(SALEAMT),0)		CARDAMT						-- 30.카드
											,		0 AS POINTAMT
											,		0 AS LICENSEAMT
											,		0 AS GIFTLICENSEAMT
											,		0 AS GOJIAMT
											,		0 AS DEDAMT
											,		0 AS MiddleAmt
											FROM	CARDSALELOG WITH (NOLOCK)
											WHERE	JUMCD = @p_JumCd
											AND		SALEDATE LIKE @p_YM+'%'
											AND		BRANDCD = @vSEIBrandCd
											AND		JUMCD IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
											GROUP	BY JUMCD, SUBSTRING(SALEDATE,1,6), BRANDCD
											UNION ALL
											SELECT	JUMCD
											,		SUBSTRING(SALEDATE,1,6) AS YM
											,		BRANDCD
											,		0 AS CASHIAMT
											,		0 AS GIFTAMT
											,		0 AS CARDAMT
											,		SUM(CASE WHEN DEALGBN='20'
														THEN (CASE WHEN ADDGBN='1' THEN ADDPOINT	
																	WHEN ADDGBN='2' THEN -ADDPOINT ELSE 0 END)	
														ELSE 0 END)	POINTAMT								-- 40.포인트
											,		0 AS LICENSEAMT
											,		0 AS GIFTLICENSEAMT
											,		0 AS GOJIAMT
											,		0 AS DEDAMT
											,		0 AS MiddleAmt
											FROM	POINTLOG WITH (NOLOCK)
											WHERE	JUMCD = @p_JumCd
											AND		SALEDATE LIKE @p_YM+'%'
											AND		BRANDCD = @vSEIBrandCd
											AND		JUMCD IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
											GROUP	BY JUMCD, SUBSTRING(SALEDATE,1,6), BRANDCD
											UNION ALL
											SELECT 	JumCd AS JUMCD
											,		YM
											,		BRANDCD
											,		0 AS CASHIAMT
											,		0 AS GIFTAMT
											,		0 AS CARDAMT
											,		0 AS POINTAMT
											,		0 AS LICENSEAMT
											,		0 AS GIFTLICENSEAMT
											,		SUM(GojiAmt) AS GOJIAMT									-- 70.관리비
											,		0 AS DEDAMT
											,		0 AS MiddleAmt
											FROM	GojiData WITH (NOLOCK)
											WHERE	JumCd = @p_JumCd
											AND		YM = @p_YM
											AND		BRANDCD = @vSEIBrandCd
											AND		JumCd IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
											GROUP	BY JumCd, YM, BRANDCD
											) A
											INNER	JOIN JumBrandMst B
											ON		A.JumCd = B.JumCd
											AND		A.BrandCd = B.BrandCd
											--AND		B.UseYn = '1'	--2012.10.09 브랜드 미사용도 포함하도록 수정
											AND		B.BrandCd <> '999999'
									group	BY A.JUMCD, A.YM, A.BRANDCD
									--print @vMijiAmt
									--print '금액제한1'
									IF @vMijiAmt > 0
										BEGIN		
																			
											INSERT INTO SeizeMove(YM,AcceptDt,SEQ,JumCd,BrandCd,Gubun,Amt,LinceseGubun,BizRegNo,RegUserId,RegDt,UpdUserId,UpdDt)VALUES
											(@p_YM,@vSEIAcceptDt,@vSEISEQ,@p_JumCd,@vSEIBrandCd,@vSEIGubun,@vMijiAmt-isnull(@vDedAmt2,0),'1','0000000000000',@p_USERID,GETDATE(),@p_USERID,GETDATE())
										END
										SET @vDedAmt3 = @vMijiAmt-@vDedAmt2
								END
								/*금액제한*/
								ELSE IF @vSEIGubun = '0'
								BEGIN
									BEGIN		
										IF EXISTS (
													SELECT	1
													FROM    SeizeMove
													WHERE	YM = @p_YM        /* 년월         */
													AND     AcceptDt     = @vSEIAcceptDt
													AND     SEQ          = @vSEISEQ
													AND		JumCd        = @p_JumCd      /* 점코드       */
													AND		BrandCd      = @vSEIBrandCd   /* 브랜드코드   */
													AND     LinceseGubun = '1'
												 )
											BEGIN
												DELETE	SeizeMove		--해당 월의 수수료테이블 삭제
												WHERE	YM = @p_YM        /* 년월         */
												AND     AcceptDt = @vSEIAcceptDt
												AND     SEQ      = @vSEISEQ
												AND		JumCd    = @p_JumCd      /* 점코드       */
												AND		BrandCd  = @vSEIBrandCd   /* 브랜드코드   */
												AND     LinceseGubun = '1'
											END
									END
									SELECT	@vMijiAmt = SUM(A.CASHIAMT + A.GIFTAMT + A.CARDAMT + A.POINTAMT) - (@vLicenseAmt + @vGiftLicenseAmt) - SUM(A.GOJIAMT) - SUM(A.MiddleAmt)--03.미지급금
									FROM	(
											SELECT	A.JumCd			--점코드	
											,		A.YM			--년월
											,		A.BRANDCD					
											,		0 AS CASHIAMT
											,		0 AS GIFTAMT
											,		0 AS CARDAMT
											,		0 AS POINTAMT
											,		0									AS LICENSEAMT		--수수료금액						
											,		0								AS GIFTLICENSEAMT	--상품권수수료액	
											,		0 AS GOJIAMT
											,		0 AS DEDAMT			--기타공제금액
											,		SUM(MiddleAmt)	AS MiddleAmt		--중간지급액(2013.03.21)								
											FROM	LicenseSaleSUM_Middle A with(nolock)			--중간수수료매출집계
											WHERE	A.JUMCD = @p_JumCd
											AND		A.YM = @p_YM
											AND		A.BRANDCD = @vSEIBrandCd
											AND		A.JUMCD IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
											GROUP	BY A.JUMCD, A.YM, A.BRANDCD
											UNION ALL
											SELECT	A.JUMCD
											,		A.YM
											,		A.BRANDCD
											,		ISNULL(ATM,0) +ISNULL(MEMOIRS,0) CASHIAMT				-- 10.현금
											,		0 AS GIFTAMT
											,		0 AS CARDAMT
											,		0 AS POINTAMT
											,		0 AS LICENSEAMT
											,		0 AS GIFTLICENSEAMT
											,		0 AS GOJIAMT
											,		0 AS DEDAMT
											,		0 AS MiddleAmt
											FROM	(
													SELECT	JUMCD
													,		SUBSTRING(INDATE,1,6) AS YM
													,		BRANDCD
													,	SUM(CASE WHEN REGGBN = '0' THEN INAMT END) ATM		--ATM
													,	SUM(CASE WHEN REGGBN = '1' THEN INAMT END) MEMOIRS	--수기
													FROM	ATMINFO WITH (NOLOCK)
													WHERE	JUMCD = @p_JumCd
													AND		INDATE LIKE @p_YM+'%'
													AND		BRANDCD = @vSEIBrandCd
													AND		JUMCD IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
													GROUP	BY JUMCD, SUBSTRING(INDATE,1,6), BRANDCD
													) A
											UNION ALL
											SELECT 	JUMCD
											,		SUBSTRING(YMD,1,6) AS YM
											,		BRANDCD
											,		0 AS CASHIAMT
											,		SUM(CASE WHEN GBN = '10' THEN TRADAMT END) AS  GIFTAMT	-- 20.상품권
											,		0 AS CARDAMT
											,		0 AS POINTAMT
											,		0 AS LICENSEAMT
											,		0 AS GIFTLICENSEAMT
											,		0 AS GOJIAMT
											,		0 AS DEDAMT
											,		0 AS MiddleAmt
											FROM	DAILYTRADSUM WITH (NOLOCK)
											WHERE	TRADCD = '04'  -- 타사상품권
											AND		JUMCD = @p_JumCd
											AND		YMD LIKE @p_YM+'%'
											AND		BRANDCD = @vSEIBrandCd
											AND		JUMCD IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
											GROUP	BY JUMCD, SUBSTRING(YMD,1,6), BRANDCD
											UNION ALL
											SELECT	JUMCD
											,		SUBSTRING(SALEDATE,1,6) AS YM
											,		BRANDCD
											,		0 AS CASHIAMT
											,		0 AS GIFTAMT
											,		ISNULL(SUM(SALEAMT),0)		CARDAMT						-- 30.카드
											,		0 AS POINTAMT
											,		0 AS LICENSEAMT
											,		0 AS GIFTLICENSEAMT
											,		0 AS GOJIAMT
											,		0 AS DEDAMT
											,		0 AS MiddleAmt
											FROM	CARDSALELOG WITH (NOLOCK)
											WHERE	JUMCD = @p_JumCd
											AND		SALEDATE LIKE @p_YM+'%'
											AND		BRANDCD = @vSEIBrandCd
											AND		JUMCD IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
											GROUP	BY JUMCD, SUBSTRING(SALEDATE,1,6), BRANDCD
											UNION ALL
											SELECT	JUMCD
											,		SUBSTRING(SALEDATE,1,6) AS YM
											,		BRANDCD
											,		0 AS CASHIAMT
											,		0 AS GIFTAMT
											,		0 AS CARDAMT
											,		SUM(CASE WHEN DEALGBN='20'
														THEN (CASE WHEN ADDGBN='1' THEN ADDPOINT	
																	WHEN ADDGBN='2' THEN -ADDPOINT ELSE 0 END)	
														ELSE 0 END)	POINTAMT								-- 40.포인트
											,		0 AS LICENSEAMT
											,		0 AS GIFTLICENSEAMT
											,		0 AS GOJIAMT
											,		0 AS DEDAMT
											,		0 AS MiddleAmt
											FROM	POINTLOG WITH (NOLOCK)
											WHERE	JUMCD = @p_JumCd
											AND		SALEDATE LIKE @p_YM+'%'
											AND		BRANDCD = @vSEIBrandCd
											AND		JUMCD IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
											GROUP	BY JUMCD, SUBSTRING(SALEDATE,1,6), BRANDCD
											UNION ALL
											SELECT 	JumCd AS JUMCD
											,		YM
											,		BRANDCD
											,		0 AS CASHIAMT
											,		0 AS GIFTAMT
											,		0 AS CARDAMT
											,		0 AS POINTAMT
											,		0 AS LICENSEAMT
											,		0 AS GIFTLICENSEAMT
											,		SUM(GojiAmt) AS GOJIAMT									-- 70.관리비
											,		0 AS DEDAMT
											,		0 AS MiddleAmt
											FROM	GojiData WITH (NOLOCK)
											WHERE	JumCd = @p_JumCd
											AND		YM = @p_YM
											AND		BRANDCD = @vSEIBrandCd
											AND		JumCd IN (SELECT JUMCD FROM dbo.[Uf_UserJum_TB](@p_USERID))
											GROUP	BY JumCd, YM, BRANDCD
											) A
											INNER	JOIN JumBrandMst B
											ON		A.JumCd = B.JumCd
											AND		A.BrandCd = B.BrandCd
											--AND		B.UseYn = '1'	--2012.10.09 브랜드 미사용도 포함하도록 수정
											AND		B.BrandCd <> '999999'
									group	BY A.JUMCD, A.YM, A.BRANDCD
									
									SELECT  @vSEISeizeAmt = SeizeAmt  --압류금액
									       ,@vSEIDeferAmt = DeferAmt  --보류금액
									  FROM SeizeMst
									 WHERE AcceptDt = @vSEIAcceptDt
									   AND SEQ = @vSEISEQ
									   
									IF @vMijiAmt-@vDedAmt2 <= @vSEISeizeAmt-@vSEIDeferAmt
										BEGIN
											IF @vMijiAmt-@vDedAmt2 > 0
												BEGIN
													SET @vDeferAmt = @vMijiAmt-@vDedAmt2
												END											
										END
									ELSE
										BEGIN
											SET @vDeferAmt = @vSEISeizeAmt-@vSEIDeferAmt
										END
									--print @vMijiAmt
									--print '무제한압류'
									IF @vDeferAmt > 0
										BEGIN   
											INSERT INTO SeizeMove(YM,AcceptDt,SEQ,JumCd,BrandCd,Gubun,Amt,LinceseGubun,BizRegNo,RegUserId,RegDt,UpdUserId,UpdDt)VALUES
											(@p_YM,@vSEIAcceptDt,@vSEISEQ,@p_JumCd,@vBrandCd,@vSEIGubun,@vDeferAmt,'1','0000000000000',@p_USERID,GETDATE(),@p_USERID,GETDATE())
										END
										SET @vDedAmt3 = @vDeferAmt
								END
						
						FETCH NEXT FROM CURSOR_005 INTO
							@vSEIAcceptDt,
							@vSEISEQ,
							@vSEIGubun,
							@vSEIBrandCd;
						END
						CLOSE CURSOR_005
						DEALLOCATE CURSOR_005  
					 
					 END
					--채권가압류E
					
					--// 월수수료매출 테이블을 생성한다.
				IF @vDedAmt2 > 0 OR @vDedAmt3 > 0
					BEGIN
						BEGIN
							INSERT	INTO SeizeSUM 
							(
									YM              -- 년월
								,	JumCd           -- 점코드
								,	BrandCd         -- 브랜드코드
								,	SaleAmt         -- 매출액
								,	MBAmt
								,	SeizeAmt
								,	DedAmt1
								,	DedAmt2
								,	RegUserId       -- 등록자Id
								,	RegDt           -- 등록일시
								,	UpdUserId       -- 수정자Id
								,	UpdDt           -- 수정일시
							)
							VALUES
							(
									@p_YM
								,	@p_JumCd
								,	@vBrandCd
								,	@vSaleAmt
								,	@vDedAmt2
								,	@vDedAmt3
								,	0
								,	0
								,	@p_UserId
								,	GETDATE()
								,	@p_UserId
								,	GETDATE()
							)
						END
					END
				--END
			
			FETCH NEXT FROM CURSOR_001 INTO
				@vBrandCd,
				@vPmCd,
				@vProfMngUnit,
				@vSaleAmt,
				@vProfAmt,
				@vSaleEnuri;

		END

		CLOSE CURSOR_001
		DEALLOCATE CURSOR_001
		
		-- 마감 처리
		IF	EXISTS	(	SELECT	1
								FROM	ClosingTB
								WHERE	JumCd = @p_JumCd
								AND		YMD = @p_YM
								AND		CloseType='B'
							)
							
			UPDATE ClosingTB
			SET			CloseYN = '1'
						,	AccNo = '지급보류월정산'
						,	AccDate = ''
			WHERE	JumCd = @p_JumCd
			AND		YMD = @p_YM
			AND		CloseType='B'
		ELSE
			INSERT	INTO ClosingTB
			(	    JumCd
				, YMD
				, CloseType
				, Gbn
				, AccDate
				, AccNo
				, CloseYN
				, RegUserId
				, RegDt
				, UpdUserId
				, UpdDt
			)	SELECT @p_JumCd	JumCd
				, @p_YM	YMD
				, 'B'			CloseType
				, ''						Gbn
				, ''	AccDate
				, '지급보류월정산'   AccNo
				, '1'						CloseYN
				, @p_USERID	RegUserId
				, GETDATE()	RegDt
				, @p_USERID	UpdUserId
				,  GETDATE()	UpdDt

		SET @p_RtnCd = 0;

	END

END