DECLARE @KHOI_ID VARCHAR(20),@AMT_APP DECIMAL(18,2),@LIMIT_PERCENT DECIMAL(18,2),@LIMIT_ID VARCHAR(20),@BRANCH_ID VARCHAR(20)

--SET @LIMIT_PERCENT =(SELECT LIMIT_PERCENT FROM dbo.TL_SYSROLE_LIMIT WHERE TL_SYSROLE_LIMIT.ROLE_ID=LIMIT_ACCUMULATE.ROLE_ID AND LIMIT_TYPE='DCNS')/100

DECLARE lstDATA CURSOR FOR
SELECT CDK.KHOI_ID,SUM(PT.AMT_APP) AMT_APP FROM dbo.PL_MASTER PM
LEFT JOIN dbo.PL_TRADEDETAIL PT ON PM.PLAN_ID = PT.PLAN_ID
LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=PT.GOODS_ID
LEFT JOIN dbo.CM_DVDM_KHOI CDK ON CDK.DVDM_ID=PM.COST_ID
WHERE PM.YEAR=2020 AND PT.RECORD_STATUS=1 AND CG.GD_TYPE_ID='NS'
GROUP BY CDK.KHOI_ID

OPEN lstDATA
FETCH NEXT FROM lstDATA INTO @KHOI_ID,@AMT_APP
WHILE @@FETCH_STATUS=0
BEGIN
SET @LIMIT_PERCENT =(SELECT LIMIT_PERCENT FROM dbo.TL_SYSROLE_LIMIT WHERE TL_SYSROLE_LIMIT.ROLE_ID='GDK' AND LIMIT_TYPE='DCNS')/100
IF(EXISTS(SELECT * FROM dbo.LIMIT_ACCUMULATE WHERE DVDM_ID=@KHOI_ID AND ROLE_ID='GDK'))
	UPDATE dbo.LIMIT_ACCUMULATE SET MAX_AMT=@AMT_APP*@LIMIT_PERCENT WHERE ROLE_ID='GDK' AND DVDM_ID=@KHOI_ID
ELSE
	BEGIN	  
	    EXEC dbo.SYS_CodeMasters_Gen @p_Kind = 'LIMIT_ACCUMULATE',                -- varchar(100)
	                                 @p_KeyGen = @LIMIT_ID OUTPUT -- varchar(15)
		INSERT INTO dbo.LIMIT_ACCUMULATE
		(
		    LIMIT_ID,
		    ROLE_ID,
		    TOTAL_APP_AMT,
		    MAX_AMT,
		    DVDM_ID,
		    BRANCH_ID
		)
		VALUES
		(   @LIMIT_ID,   -- LIMIT_ID - varchar(20)
		    'GDK',   -- ROLE_ID - varchar(20)
		    NULL, -- TOTAL_APP_AMT - decimal(18, 2)
		    (@AMT_APP*@LIMIT_PERCENT), -- MAX_AMT - decimal(18, 2)
		    @KHOI_ID,   -- DVDM_ID - varchar(20)
		    ''    -- BRANCH_ID - varchar(20)
		    )
	    
	END
IF(EXISTS(SELECT * FROM dbo.LIMIT_ACCUMULATE WHERE DVDM_ID=@KHOI_ID AND ROLE_ID='PTGD'))
	UPDATE dbo.LIMIT_ACCUMULATE SET MAX_AMT=@AMT_APP*@LIMIT_PERCENT WHERE ROLE_ID='PTGD' AND DVDM_ID=@KHOI_ID
ELSE
	BEGIN	  
	    EXEC dbo.SYS_CodeMasters_Gen @p_Kind = 'LIMIT_ACCUMULATE',                -- varchar(100)
	                                 @p_KeyGen = @LIMIT_ID OUTPUT -- varchar(15)
		INSERT INTO dbo.LIMIT_ACCUMULATE
		(
		    LIMIT_ID,
		    ROLE_ID,
		    TOTAL_APP_AMT,
		    MAX_AMT,
		    DVDM_ID,
		    BRANCH_ID
		)
		VALUES
		(   @LIMIT_ID,   -- LIMIT_ID - varchar(20)
		    'PTGD',   -- ROLE_ID - varchar(20)
		    NULL, -- TOTAL_APP_AMT - decimal(18, 2)
		    (@AMT_APP*@LIMIT_PERCENT), -- MAX_AMT - decimal(18, 2)
		    @KHOI_ID,   -- DVDM_ID - varchar(20)
		    ''    -- BRANCH_ID - varchar(20)
		    )
	    
	END


FETCH NEXT FROM lstDATA INTO @KHOI_ID,@AMT_APP
END
CLOSE lstDATA
DEALLOCATE lstDATA



DECLARE lstDATA CURSOR FOR
SELECT PM.BRANCH_ID,SUM(PT.AMT_APP) AMT_APP FROM dbo.PL_MASTER PM
LEFT JOIN dbo.PL_TRADEDETAIL PT ON PM.PLAN_ID = PT.PLAN_ID
LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=PT.GOODS_ID
WHERE PM.YEAR=2020 AND PT.RECORD_STATUS=1 AND CG.GD_TYPE_ID='NS'
GROUP BY PM.BRANCH_ID

OPEN lstDATA
FETCH NEXT FROM lstDATA INTO @BRANCH_ID,@AMT_APP
WHILE @@FETCH_STATUS=0
BEGIN
SET @LIMIT_PERCENT =(SELECT LIMIT_PERCENT FROM dbo.TL_SYSROLE_LIMIT WHERE TL_SYSROLE_LIMIT.ROLE_ID='GDDV' AND LIMIT_TYPE='DCNS')/100
IF(EXISTS(SELECT * FROM dbo.LIMIT_ACCUMULATE WHERE BRANCH_ID=@BRANCH_ID AND ROLE_ID='GDDV'))
	UPDATE dbo.LIMIT_ACCUMULATE SET MAX_AMT=@AMT_APP*@LIMIT_PERCENT WHERE ROLE_ID='GDDV' AND BRANCH_ID=@BRANCH_ID
ELSE
	BEGIN	  
	    EXEC dbo.SYS_CodeMasters_Gen @p_Kind = 'LIMIT_ACCUMULATE',                -- varchar(100)
	                                 @p_KeyGen = @LIMIT_ID OUTPUT -- varchar(15)
		INSERT INTO dbo.LIMIT_ACCUMULATE
		(
		    LIMIT_ID,
		    ROLE_ID,
		    TOTAL_APP_AMT,
		    MAX_AMT,
		    DVDM_ID,
		    BRANCH_ID
		)
		VALUES
		(   @LIMIT_ID,   -- LIMIT_ID - varchar(20)
		    'GDDV',   -- ROLE_ID - varchar(20)
		    NULL, -- TOTAL_APP_AMT - decimal(18, 2)
		    (@AMT_APP*@LIMIT_PERCENT), -- MAX_AMT - decimal(18, 2)
		    '',   -- DVDM_ID - varchar(20)
		    @BRANCH_ID    -- BRANCH_ID - varchar(20)
		    )
	    
	END

FETCH NEXT FROM lstDATA INTO @BRANCH_ID,@AMT_APP
END
CLOSE lstDATA
DEALLOCATE lstDATA