ALTER   PROCEDURE [dbo].[TR_REQ_PAYMENT_Appr]
--Luanlt 2019/17/10 - Sửa params
@p_REQ_PAY_ID	varchar(15)= NULL,
@p_CHECKER_ID	varchar(15)	= NULL
AS

-- BEGIN VALIDATE
	IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE MAKER_ID =@p_CHECKER_ID AND REQ_PAY_ID = @p_REQ_PAY_ID)
	BEGIN
		SELECT '-1' as Result, ''  REQ_PAY_ID, N'Bạn không được phép duyệt phiếu đề nghị thanh toán ứng này! Người phê duyệt phiếu phải khác với người tạo phiếu' ErrorDesc
		RETURN '-1'
	END
	IF ( EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE ISNULL(TRASFER_USER_RECIVE, '') <> '' AND TRASFER_USER_RECIVE = @p_CHECKER_ID AND REQ_PAY_ID =@p_REQ_PAY_ID))
	BEGIN
		SELECT '-1' as Result, ''  REQ_PAY_ID, N'Bạn không được phép duyệt phiếu đề nghị thanh toán ứng này! Người phê duyệt phiếu phải khác với người phê duyệt trung gian' ErrorDesc
		RETURN '-1'
	END
	IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phê duyệt phiếu đề nghị thanh toán thất bại! Phiếu đề nghị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đã được phê duyệt trước đó' ErrorDesc
		RETURN '-1'
	END
	IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đang được trả về bộ phận yêu cầu. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc
		RETURN '-1'
	END
	IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đã được kiểm soát viên phê duyệt trước đó' ErrorDesc
		RETURN '-1'
	END
	IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đang được trả về giao dịch viên. Vui lòng kiểm tra lại thông tin' ErrorDesc
		RETURN '-1'
	END
	IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT ='S' AND REQ_PAY_ID =@p_REQ_PAY_ID)
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đang được đề xuất từ chối. Vui lòng kiểm tra lại thông tin' ErrorDesc
		RETURN '-1'
	END
-- CHAN CUOI
	IF (EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS <> 'U' AND REQ_PAY_ID =@p_REQ_PAY_ID))
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' chưa được gửi phê duyệt. Vui lòng đợi người tạo gửi phê duyệt trước khi phê duyệt phiếu' ErrorDesc
		RETURN '-1'
	END
-- FETCH DATA TU TABLE
			Declare @t_BUDG_ID varchar(15)
			Declare @t_GD_ID varchar(15)
			Declare @t_REQ_PAY_ID varchar(15)
			Declare @t_TRADE_ID varchar(15)
			Declare @t_AMT_APP decimal
			Declare @t_AMT_EXE decimal
			Declare @t_AMT_REMAIN decimal
			Declare @t_MAKER_ID varchar(15)
			Declare @t_CREATE_DT varchar(50)
			Declare @t_CURRENCY varchar(15)
			Declare @t_RATE decimal
			Declare @t_AMT_DO decimal
			Declare @t_REASON nvarchar(1000)
			Declare @t_NOTES nvarchar(1000)
			Declare @t_TYPE_COST varchar(15)
			Declare @t_FR_LEVEL int
			Declare @t_TO_LEVEL int
			Declare @t_MONTH_RATE varchar(4)
			Declare @t_YEAR_RATE varchar(4)
			Declare @t_BUDGET_TYPE varchar(20)
			Declare @t_BRANCH_ID varchar(20)
			Declare @t_DEP_ID varchar(20)
			Declare @t_TLNAME varchar(20)
			Declare @t_ROLENAME varchar(20)
			Declare @t_BRANCH_TAKE_COST_ID varchar(20)
			Declare @t_DEP_TAKE_COST_ID varchar(20)
			Declare @t_KHOI_TAKE_COST_ID varchar(20)
			Declare @t_BRANCH_KIND varchar(20) 
		DECLARE reader_cursor CURSOR FOR 
		SELECT [BUDG_ID], [GD_ID], [REQ_PAY_ID], [TRADE_ID], [AMT_APP], [AMT_EXE], [AMT_REMAIN], [MAKER_ID], [CREATE_DT], [CURRENCY], [RATE], [AMT_DO], [REASON], [NOTES], [TYPE_COST], [FR_LEVEL], [TO_LEVEL], [MONTH_RATE], [YEAR_RATE], [BUDGET_TYPE], [BRANCH_ID], [DEP_ID], [TLNAME], [ROLENAME], [BRANCH_TAKE_COST_ID], [DEP_TAKE_COST_ID], [KHOI_TAKE_COST_ID], [BRANCH_KIND] 
		FROM TR_REQ_PAY_BUDGET 
		WHERE REQ_PAY_ID = @p_REQ_PAY_ID
		OPEN reader_cursor;
		FETCH NEXT FROM reader_cursor INTO @t_BUDG_ID, @t_GD_ID, @t_REQ_PAY_ID, @t_TRADE_ID, @t_AMT_APP, @t_AMT_EXE, @t_AMT_REMAIN, @t_MAKER_ID, @t_CREATE_DT, @t_CURRENCY, @t_RATE, @t_AMT_DO, @t_REASON, @t_NOTES, @t_TYPE_COST, @t_FR_LEVEL, @t_TO_LEVEL, @t_MONTH_RATE, @t_YEAR_RATE, @t_BUDGET_TYPE, @t_BRANCH_ID, @t_DEP_ID, @t_TLNAME, @t_ROLENAME, @t_BRANCH_TAKE_COST_ID, @t_DEP_TAKE_COST_ID, @t_KHOI_TAKE_COST_ID, @t_BRANCH_KIND
		WHILE @@FETCH_STATUS = 0
		BEGIN
			DECLARE @TenCot NVARCHAR(128) = @t_MONTH_RATE; -- Tham số tên cột
			DECLARE @GiaTriMoi DECIMAL = @t_AMT_EXE; -- Tham số giá trị mới
			DECLARE @DieuKien NVARCHAR(MAX) = 'BUDG_ID = ' + '''' + @t_BUDG_ID + ''''; -- Tham số điều kiện WHERE

			IF(ISNULL(@t_BUDGET_TYPE, '') = 'thang')
			BEGIN
				IF (NOT EXISTS (SELECT * FROM TR_REQ_BUDGET_MONTH_LIMIT_DETAIL WHERE BUDG_ID = @t_BUDG_ID))
				BEGIN
					DECLARE @l_BUDGET_LIMIT_MONTH_DETAIL_ID VARCHAR(15);
					EXEC SYS_CodeMasters_Gen 'BUDGET_LIMIT_MONTH_DETAIL', @l_BUDGET_LIMIT_MONTH_DETAIL_ID OUT;

					INSERT INTO TR_REQ_BUDGET_MONTH_LIMIT_DETAIL(BUDGET_MONTH_LIMIT_DETAIL_ID, REQ_PAY_ID, GD_ID , BRANCH_ID,DEP_ID, BUDGET_YEAR, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID , APPROVE_DT, BRANCH_KIND,BUDG_ID, KHOI_ID, TLNAME, ROLENAME)
					VALUES(@l_BUDGET_LIMIT_MONTH_DETAIL_ID, @p_REQ_PAY_ID, @t_GD_ID, @t_BRANCH_TAKE_COST_ID, @t_DEP_TAKE_COST_ID, @t_YEAR_RATE, @p_CHECKER_ID, GETDATE(), '1', 'A', NULL, NULL, @t_BRANCH_KIND, @t_BUDG_ID, @t_KHOI_TAKE_COST_ID, @t_TLNAME, @t_ROLENAME)
					
					DECLARE @sql_thang NVARCHAR(MAX);
					SET @sql_thang = N'UPDATE TR_REQ_BUDGET_MONTH_LIMIT_DETAIL SET ' + QUOTENAME(@TenCot) + N' = @GiaTriMoi WHERE ' + @DieuKien;
					EXEC sp_executesql @sql_thang, N'@GiaTriMoi DECIMAL', @GiaTriMoi = @GiaTriMoi;				
				END
			END

			IF(ISNULL(@t_BUDGET_TYPE, '') = 'nam')
			BEGIN
				IF (NOT EXISTS (SELECT * FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE BUDG_ID = @t_BUDG_ID))
				BEGIN
				print @TenCot
				print @GiaTriMoi
				print @DieuKien

					DECLARE @l_BUDGET_LIMIT_YEAR_DETAIL_ID VARCHAR(15);
					EXEC SYS_CodeMasters_Gen 'BUDGET_LIMIT_YEAR_DETAIL', @l_BUDGET_LIMIT_YEAR_DETAIL_ID OUT;

					INSERT INTO TR_REQ_BUDGET_YEAR_LIMIT_DETAIL(BUDGET_YEAR_LIMIT_DETAIL_ID,REQ_PAY_ID, GD_ID , BRANCH_ID, DEP_ID, KHOI_ID, TLNAME, ROLENAME, BUDGET_YEAR, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID , APPROVE_DT, BRANCH_KIND, BUDG_ID)
					VALUES(@l_BUDGET_LIMIT_YEAR_DETAIL_ID, @p_REQ_PAY_ID, @t_GD_ID, @t_BRANCH_TAKE_COST_ID, @t_DEP_TAKE_COST_ID, @t_KHOI_TAKE_COST_ID, @t_TLNAME, @t_RoleName, @t_YEAR_RATE, @p_CHECKER_ID, GETDATE(), '1', 'A', NULL, NULL, @t_BRANCH_KIND, @t_BUDG_ID)
					
					DECLARE @sql_nam NVARCHAR(MAX);
					SET @sql_nam = N'UPDATE TR_REQ_BUDGET_YEAR_LIMIT_DETAIL SET ' + QUOTENAME(@TenCot) + N' = @GiaTriMoi WHERE ' + @DieuKien;
					EXEC sp_executesql @sql_nam, N'@GiaTriMoi DECIMAL', @GiaTriMoi = @GiaTriMoi;				
				END
			END
		FETCH NEXT FROM reader_cursor INTO @t_BUDG_ID, @t_GD_ID, @t_REQ_PAY_ID, @t_TRADE_ID, @t_AMT_APP, @t_AMT_EXE, @t_AMT_REMAIN, @t_MAKER_ID, @t_CREATE_DT, @t_CURRENCY, @t_RATE, @t_AMT_DO, @t_REASON, @t_NOTES, @t_TYPE_COST, @t_FR_LEVEL, @t_TO_LEVEL, @t_MONTH_RATE, @t_YEAR_RATE, @t_BUDGET_TYPE, @t_BRANCH_ID, @t_DEP_ID, @t_TLNAME, @t_ROLENAME, @t_BRANCH_TAKE_COST_ID, @t_DEP_TAKE_COST_ID, @t_KHOI_TAKE_COST_ID, @t_BRANCH_KIND
		END
		CLOSE reader_cursor;  
		DEALLOCATE reader_cursor;  
-- FETCH DATA TU TABLE
-- END VALIDATE

BEGIN TRANSACTION
		DECLARE @ROLE_ID VARCHAR(15)
		SET  @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_CHECKER_ID)
		IF(@ROLE_ID IS NOT NULL AND @ROLE_ID <>'' AND @ROLE_ID IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD'))
		BEGIN
			PRINT @ROLE_ID
		END
		ELSE
		BEGIN
			SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_CHECKER_ID)
			IF(@ROLE_ID IS NULL OR @ROLE_ID ='')
			BEGIN
					SET @ROLE_ID =(SELECT TOP 1 ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT TOP 1 ROLENAME FROM TL_USER WHERE TLNANME=@p_CHECKER_ID))
			END
		END

		DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))  
		INSERT INTO @TABLE_ROLE SELECT ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE	TLNAME =@p_CHECKER_ID
																				AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL OR EFF_DATE ='')
																				AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='') 
																				AND RECORD_STATUS = '1'

		IF(@ROLE_ID NOT IN ('TPGD','PPGD','PP','TP','GDDV','PGD','KTT','TP','PP','TPTC','TC','GDK') AND 
			NOT EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH IN ('TPGD','PPGD','PP','TP','GDDV','PGD','KTT','TP','PP','TPTC','TC','GDK')))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' REQ_PAY_ID, N'Bạn không nằm trong nhóm quyền được phép phê duyệt phiếu đề nghị thanh toán' ErrorDesc
			RETURN '-1'
		END
		-- KIEM TRA NGUOI TAO PHAI KHAC NGUOI DUYET
		IF(EXISTS(SELECT RoleName FROM TL_USER_V2 WHERE TLNANME =@p_CHECKER_ID AND RoleName NOT IN ('GDK','GDDV','PTGD','TGD','HDQT','TBP')))
		BEGIN
			IF EXISTS (SELECT TOP 1 REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE MAKER_ID = @p_CHECKER_ID AND REQ_PAY_ID =@p_REQ_PAY_ID)
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' REQ_PAY_ID, N'Bạn là người tạo phiếu đề nghị thanh toán. Bạn không được phép duyệt phiếu này' ErrorDesc
				RETURN '-1'
			END
		END
		-- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET
		IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đang được trả về bộ phận yêu cầu. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc
			RETURN '-1'
		END
		-- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET
		IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đang được trả về bộ phận yêu cầu. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc
			RETURN '-1'
		END
		-- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET
		IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đã được KSV phê duyệt trước đó' ErrorDesc
			RETURN '-1'
		END
		IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đã được phê duyệt trước đó' ErrorDesc
			RETURN '-1'
		END
		-- CHECK XEM CO CAP DUYET TRUNG GIAN HAY KHONG
		IF((SELECT ISNULL(PROCESS, '') FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) = '')
		BEGIN
			DECLARE @USER_SIGN VARCHAR(15)
			SET @USER_SIGN =(SELECT ISNULL(TRASFER_USER_RECIVE,'') FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)
			IF(@p_CHECKER_ID <>  @USER_SIGN AND @USER_SIGN <> '' AND (SELECT TOP 1 PROCESS_ID FROM PL_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID ORDER BY ID DESC ) = 'SEND')
			BEGIN
				ROLLBACK TRANSACTION		
				SELECT '-1' as Result, @p_REQ_PAY_ID  REQ_PAY_ID ,N'Phiếu đề nghị thanh toán đang chờ cấp phê duyệt trung gian xác nhận. User domain: '+ISNULL(@USER_SIGN,'') ErrorDesc
				RETURN '-1'
			END
		END
		

		--END - CAP NHAT CODE TRONG QUA TRINH TEST UAT 19 11 2019
		DECLARE @INDEX INT =0
		DECLARE @PAY_ID VARCHAR(15),@PAY_PHASE VARCHAR(15)
		DECLARE @REQ_TYPE VARCHAR(15),@PAY_ADVANCE_ID VARCHAR(15),@AMT_PAY DECIMAL(18,2), @AMT_USE DECIMAL(18,2), @AMT_REVERT DECIMAL(12)
		SET @REQ_TYPE =(SELECT REQ_TYPE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
		IF(@REQ_TYPE ='I')
		BEGIN
		DECLARE CURS CURSOR FOR SELECT A.PAY_ADV_ID,A.AMT_REMAIN,A.AMT_USE,A.AMT_REVERT FROM TR_REQ_PAYMENT_DT A WHERE A.PAY_ID =@p_REQ_PAY_ID
		OPEN CURS
			FETCH NEXT FROM CURS INTO @PAY_ADVANCE_ID,@AMT_PAY,@AMT_USE, @AMT_REVERT
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @INDEX = @INDEX +1
				-- KIEM TRA NEU SO PHIEU TAM UNG NAY DA THANH TOAN XONG THI KHONG CHO PHEP DUYET
				IF((SELECT A.REQ_AMT - A.PAY_AMT FROM TR_REQ_ADVANCE_PAYMENT A WHERE A.REQ_PAY_ID = @PAY_ADVANCE_ID)=0)
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' Result,'' REQ_PAY_ID,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Phiếu tạm ứng này đã được hoàn tạm ứng xong. Vui lòng xóa khỏi danh sách hoàn tạm ứng' ErrorDesc
					RETURN '-1'
				END
				--UPDATE TR_REQ_ADVANCE_PAYMENT SET PAY_AMT = ISNULL(PAY_AMT,0) + ISNULL(@AMT_PAY,0.00) +ISNULL(@AMT_REVERT,0.00) WHERE REQ_PAY_ID =@PAY_ADVANCE_ID
			FETCH NEXT FROM CURS INTO @PAY_ADVANCE_ID,@AMT_PAY,@AMT_USE, @AMT_REVERT
			END
		CLOSE CURS
		DEALLOCATE CURS
		END
		-- NEU LOAI TAM UNG LA THANH TOAN PO
		ELSE IF(@REQ_TYPE ='P')
		BEGIN
		DECLARE CURS CURSOR FOR SELECT A.PAY_ID, A.PAY_PHASE FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
		OPEN CURS
			FETCH NEXT FROM CURS INTO @PAY_ID,@PAY_PHASE
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @INDEX = @INDEX +1
				---- KIEM TRA NEU SO PHIEU TAM UNG NAY DA THANH TOAN XONG THI KHONG CHO PHEP DUYET
				--IF((SELECT MIN(A.AMT_REMAIN)FROM TR_REQ_PAY_SCHEDULE A WHERE A.PAY_ID =@PAY_ID GROUP BY A.PAY_ID)=0)
				--BEGIN
				--	ROLLBACK TRANSACTION
				--	SELECT '-1' Result,'' REQ_PAY_ID,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N':Kì '+ @PAY_PHASE+N' đã thanh toán xong'  ErrorDesc
				--	RETURN '-1'
				--END
			FETCH NEXT FROM CURS INTO @PAY_ID,@PAY_PHASE
			END
		CLOSE CURS
		DEALLOCATE CURS
		END
		UPDATE TR_REQ_PAYMENT
	    SET    AUTH_STATUS='A', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT =  GETDATE(), AUTH_STATUS_KT='U'
	    WHERE  REQ_PAY_ID = @p_REQ_PAY_ID
		--UPDATE DETAIL
		UPDATE TR_REQ_PAYMENT_DT SET AUTH_STATUS='A',CHECKER_ID =@p_CHECKER_ID,APPROVE_DT = GETDATE()
		WHERE PAY_ID =@p_REQ_PAY_ID
		-- INSERT VAO PL_PROCESS
	   INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
	   VALUES(@p_REQ_PAY_ID,'APP',@p_CHECKER_ID,GETDATE(), N'Trưởng đơn vị phê duyệt phiếu',N'Phê duyệt cấp đơn vị')
		IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
SELECT '0' as Result, @p_REQ_PAY_ID  REQ_PAY_ID ,'' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc
		RETURN '-1'
END
GO
ALTER PROC [dbo].[TR_BUDGET_MONTH_DETAIL_DVKD_Import] (
	@p_GD_ID VARCHAR(20), 
	@p_BRANCH_ID VARCHAR(20), 
	@p_BUDGET_TYPE VARCHAR(20), 
	@p_BRANCH_TYPE VARCHAR(20), 
	@p_MAKER_ID VARCHAR(20), 
	@p_BRANCH_KIND VARCHAR(20), 
	@p_XMP_TR_BUDGET_LIMIT_MONTH_DETAIL XML = NULL
)
AS
	IF(@p_GD_ID IS NULL OR @p_GD_ID = '')
	BEGIN
		SELECT '-1' as Result, N'Mã ngân sách chi phí không được để trống' AS ErrorDesc
		RETURN '-1'
	END
	IF(@p_BUDGET_TYPE IS NULL OR @p_BUDGET_TYPE = '')
	BEGIN
		SELECT '-1' as Result, N'Loại ngân sách chi phí không được để trống' AS ErrorDesc
		RETURN '-1'
	END
	IF(@p_BRANCH_TYPE IS NULL OR @p_BRANCH_TYPE = '')
	BEGIN
		SELECT '-1' as Result, N'Loại đơn vị không được để trống' AS ErrorDesc
		RETURN '-1'
	END
BEGIN TRANSACTION
-- Đảm bảo ID ngân sách luôn được khai báo. Mõi ID ngân sách chỉ được dùng cho tháng hoặc cho năm.
-- CMT LAI DO CHO PHEP THAY DOI
	--IF(EXISTS(SELECT * FROM TR_GOODSTYPE WHERE GD_ID = @p_GD_ID AND ISNULL(BUDGET_TYPE, '') <> 'thang'))
	--BEGIN
	--	ROLLBACK TRANSACTION
	--	SELECT '-1' as Result, N'Import thất bại, Mã ngân sách đang được quản lý định mức theo loại khác' AS ErrorDesc
	--	RETURN '-1'
	--END
	
	DELETE FROM TR_GOODSTYPE WHERE GD_ID = @p_GD_ID
	INSERT INTO TR_GOODSTYPE(GD_ID, BUDGET_TYPE, CREATE_DT, MAKER_ID, APPROVE_DT, CHECKER_ID, RECORD_STATUS, AUTH_STATUS) 
	VALUES (@p_GD_ID, 'thang', GETDATE(), @p_MAKER_ID, NULL, NULL, '1', 'A')
	
	DECLARE @hdoc INT
	EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TR_BUDGET_LIMIT_MONTH_DETAIL
	DECLARE XmlBudgetLimitMonthDT CURSOR FOR
	SELECT * FROM OPENXML(@hdoc, '/Root/XmlBudgetLimitMonthDT', 2)
	WITH (
		BRANCH_CODE		VARCHAR(15),
		BRANCH_NAME		NVARCHAR(250),
		BRANCH_KIND		VARCHAR(20),
		BRANCH_LEVEL	NVARCHAR(50),
		BUDGET_YEAR		VARCHAR(15),
		M1				DECIMAL(18, 0),
		M2				DECIMAL(18, 0),
		M3				DECIMAL(18, 0),
		M4				DECIMAL(18, 0),
		M5				DECIMAL(18, 0),
		M6				DECIMAL(18, 0),
		M7				DECIMAL(18, 0),
		M8				DECIMAL(18, 0),
		M9				DECIMAL(18, 0),
		M10				DECIMAL(18, 0),
		M11				DECIMAL(18, 0),
		M12				DECIMAL(18, 0)
	)

	DECLARE @BRANCH_CODE	VARCHAR(15),
		@BRANCH_NAME		NVARCHAR(250),
		@BRANCH_KIND		VARCHAR(20),
		@BRANCH_LEVEL		NVARCHAR(50),
		@BUDGET_YEAR		VARCHAR(15),
		@M1					DECIMAL(18, 0),
		@M2					DECIMAL(18, 0),
		@M3					DECIMAL(18, 0),
		@M4					DECIMAL(18, 0),
		@M5					DECIMAL(18, 0),
		@M6					DECIMAL(18, 0),
		@M7					DECIMAL(18, 0),
		@M8					DECIMAL(18, 0),
		@M9					DECIMAL(18, 0),
		@M10				DECIMAL(18, 0),
		@M11				DECIMAL(18, 0),
		@M12				DECIMAL(18, 0)
	
	------------------------------------------------ validate begin ------------------------------------------------

	DECLARE @ERROR_MESSAGE nvarchar(MAX)
	DECLARE @INDEX INT
	SET @INDEX = 2

	OPEN XmlBudgetLimitMonthDT;

	FETCH NEXT FROM XmlBudgetLimitMonthDT 
	INTO @BRANCH_CODE, @BRANCH_NAME, @BRANCH_KIND, @BRANCH_LEVEL, @BUDGET_YEAR  , @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
	WHILE @@fetch_status=0 
	BEGIN
		SET @INDEX = @INDEX +1
		DECLARE @l_BRANCH_ID VARCHAR(20);
		DECLARE @l_DEP_ID VARCHAR(20);
		
		SET @l_BRANCH_ID = (SELECT TOP 1 BRANCH_ID FROM CM_BRANCH WHERE BRANCH_CODE = @BRANCH_CODE)

		IF(@BRANCH_KIND IS NULL OR @BRANCH_KIND = '')
		BEGIN
			ROLLBACK TRANSACTION
			CLOSE XmlBudgetLimitMonthDT;
			DEALLOCATE XmlBudgetLimitMonthDT;
			SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Hình thức đơn vị không được để trống' AS ErrorDesc
			RETURN '-1'
		END

		IF(ISNULL(@l_BRANCH_ID,'') = '')
		BEGIN
			ROLLBACK TRANSACTION
			CLOSE XmlBudgetLimitMonthDT;
			DEALLOCATE XmlBudgetLimitMonthDT;
			SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Đơn vị ' + @BRANCH_CODE + N' không có trong hệ thống' AS ErrorDesc
			RETURN '-1'
		END
	-- NEU DA TON TAI NGAN SACH THI CHI UPDATE
		IF(EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_MONTH_LIMIT WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND))
		BEGIN
			IF(ISNULL(@M1, 0) >= 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M1  = @M1, BRANCH_LEVEL = @BRANCH_LEVEL  WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
			END
			IF(ISNULL(@M2, 0) >= 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M2  = @M2, BRANCH_LEVEL = @BRANCH_LEVEL  WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
			END																						  
			IF(ISNULL(@M3, 0) >= 0)																	  
			BEGIN																					  
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M3  = @M3, BRANCH_LEVEL = @BRANCH_LEVEL  WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
			END																						  
			IF(ISNULL(@M4, 0) >= 0)																	  
			BEGIN																					  
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M4  = @M4, BRANCH_LEVEL = @BRANCH_LEVEL  WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
			END																						  
			IF(ISNULL(@M5, 0) >= 0)																	  
			BEGIN																					  
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M5  = @M5, BRANCH_LEVEL = @BRANCH_LEVEL  WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
			END																						  
			IF(ISNULL(@M6, 0) >= 0)																	  
			BEGIN																					  
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M6  = @M6, BRANCH_LEVEL = @BRANCH_LEVEL  WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
			END																						  
			IF(ISNULL(@M7, 0) >= 0)																	  
			BEGIN																					  
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M7  = @M7, BRANCH_LEVEL = @BRANCH_LEVEL  WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
			END																						  
			IF(ISNULL(@M8, 0) >= 0)																	  
			BEGIN																					  
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M8  = @M8, BRANCH_LEVEL = @BRANCH_LEVEL  WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
			END																						  
			IF(ISNULL(@M9, 0) >= 0)																	  
			BEGIN																					  
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M9  = @M9, BRANCH_LEVEL = @BRANCH_LEVEL  WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
			END																						  
			IF(ISNULL(@M10, 0) >= 0)																	  
			BEGIN																					  
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M10 = @M10, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
			END																						  
			IF(ISNULL(@M11, 0) >= 0)																	  
			BEGIN																					  
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M11 = @M11, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
			END																						  
			IF(ISNULL(@M12, 0) >= 0)																	  
			BEGIN																					  
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M12 = @M12, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
			END
		IF @@error<>0 GOTO ABORT;
		FETCH NEXT FROM XmlBudgetLimitMonthDT 
		INTO @BRANCH_CODE, @BRANCH_NAME, @BRANCH_KIND, @BRANCH_LEVEL, @BUDGET_YEAR , @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
		END
		ELSE
		BEGIN
			-- Tự động sinh mã code
			DECLARE @BUDGET_MONTH_LIMIT_ID VARCHAR(15);
			EXEC SYS_CodeMasters_Gen 'BUDGET_LIMIT_MONTH', @BUDGET_MONTH_LIMIT_ID OUT;
			IF @BUDGET_MONTH_LIMIT_ID = '' OR @BUDGET_MONTH_LIMIT_ID IS NULL GOTO ABORT;

			INSERT INTO TR_REQ_BUDGET_MONTH_LIMIT(BUDGET_MONTH_LIMIT_ID, GD_ID, BUDGET_TYPE, BRANCH_TYPE, BUDGET_YEAR, BRANCH_ID, DEP_ID, 
			M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID, APPROVE_DT, BRANCH_KIND, BRANCH_LEVEL)
			VALUES(@BUDGET_MONTH_LIMIT_ID, @p_GD_ID, 'thang', @p_BRANCH_TYPE, @BUDGET_YEAR, @l_BRANCH_ID, NULL, 
			@M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12, @p_MAKER_ID, GETDATE(), '1', 'A', NULL, NULL, @BRANCH_KIND, @BRANCH_LEVEL)

			IF @@error<>0 GOTO ABORT;
			FETCH NEXT FROM XmlBudgetLimitMonthDT 
			INTO @BRANCH_CODE, @BRANCH_NAME, @BRANCH_KIND, @BRANCH_LEVEL, @BUDGET_YEAR , @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
		END
	END
	CLOSE XmlBudgetLimitMonthDT;
	DEALLOCATE XmlBudgetLimitMonthDT
COMMIT TRANSACTION

SELECT '0' AS Result, ''  REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thêm mới thành công' ErrorDesc
RETURN '0'

ABORT:
BEGIN
	ROLLBACK TRANSACTION
	SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, '' ErrorDesc
	RETURN '-1'
END
GO
ALTER PROC [dbo].[TR_BUDGET_MONTH_DETAIL_Import] (
	@GD_ID VARCHAR(20), 
	@BUDGET_TYPE VARCHAR(20), 
	@BRANCH_TYPE VARCHAR(20), 
	@p_MAKER_ID VARCHAR(20), 
	@p_MONTH_IMPORT VARCHAR(20), 
	@p_XMP_TR_BUDGET_LIMIT_MONTH_DETAIL XML = NULL
)
AS
-- Validation
	IF(ISNULL(@GD_ID, '') = '')
	BEGIN
		SELECT '-1' as Result, N'Mã ngân sách chi phí không được để trống' AS ErrorDesc
		RETURN '-1'
	END
	IF(ISNULL(@BUDGET_TYPE, '') = '')
	BEGIN
		SELECT '-1' as Result, N'Loại ngân sách chi phí không được để trống' AS ErrorDesc
		RETURN '-1'
	END
	IF(ISNULL(@BRANCH_TYPE, '') = '')
	BEGIN
		SELECT '-1' as Result, N'Loại đơn vị không được để trống' AS ErrorDesc
		RETURN '-1'
	END

BEGIN TRANSACTION
-- Đảm bảo ID ngân sách luôn được khai báo. Mõi ID ngân sách chỉ được dùng cho tháng hoặc cho năm
	--IF(EXISTS(SELECT * FROM TR_GOODSTYPE WHERE GD_ID = @GD_ID AND ISNULL(BUDGET_TYPE, '') <> 'thang'))
	--BEGIN
	--	ROLLBACK TRANSACTION
	--	SELECT '-1' as Result, N'Import thất bại, Mã ngân sách đang được quản lý định mức theo loại khác' AS ErrorDesc
	--	RETURN '-1'
	--END
	DELETE FROM TR_GOODSTYPE WHERE GD_ID = @GD_ID
	INSERT INTO TR_GOODSTYPE(GD_ID, BUDGET_TYPE, CREATE_DT, MAKER_ID, APPROVE_DT, CHECKER_ID, RECORD_STATUS, AUTH_STATUS) 
	VALUES (@GD_ID, 'thang', GETDATE(), @p_MAKER_ID, NULL, NULL, '1', 'A')

-- KHAI BÁO CURSOR VÀ CÁC BIẾN LIÊN QUAN CHO VIỆC LẶP FILE IMPORT
	DECLARE @ERROR_MESSAGE nvarchar(MAX)
	DECLARE @INDEX INT = 2, @l_DEP_ID VARCHAR(20), @l_TLNAME VARCHAR(20), @l_KHOI_ID VARCHAR(20), @l_ROLENAME VARCHAR(20), @l_KHOI_NAME NVARCHAR(250);

	DECLARE
		@DEP_CODE VARCHAR(25),
		@DEP_NAME NVARCHAR(500),
		@ROLENAME NVARCHAR(20),
		@BUDGET_YEAR VARCHAR(15),
		@M1 DECIMAL(18, 0),
		@M2 DECIMAL(18, 0),
		@M3 DECIMAL(18, 0),
		@M4 DECIMAL(18, 0),
		@M5 DECIMAL(18, 0),
		@M6 DECIMAL(18, 0),
		@M7 DECIMAL(18, 0),
		@M8 DECIMAL(18, 0),
		@M9 DECIMAL(18, 0),
		@M10 DECIMAL(18, 0),
		@M11 DECIMAL(18, 0),
		@M12 DECIMAL(18, 0)

	DECLARE @hdoc INT
	EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TR_BUDGET_LIMIT_MONTH_DETAIL
	DECLARE XmlBudgetLimitMonthDT CURSOR FOR
	SELECT * FROM OPENXML(@hdoc, '/Root/XmlBudgetLimitMonthDT', 2)
	WITH (
		DEP_CODE VARCHAR(25),
		DEP_NAME NVARCHAR(500),
		ROLENAME NVARCHAR(20),
		BUDGET_YEAR VARCHAR(15),
		M1 DECIMAL(18, 0),
		M2 DECIMAL(18, 0),
		M3 DECIMAL(18, 0),
		M4 DECIMAL(18, 0),
		M5 DECIMAL(18, 0),
		M6 DECIMAL(18, 0),
		M7 DECIMAL(18, 0),
		M8 DECIMAL(18, 0),
		M9 DECIMAL(18, 0),
		M10 DECIMAL(18, 0),
		M11 DECIMAL(18, 0),
		M12 DECIMAL(18, 0)
	)

	OPEN XmlBudgetLimitMonthDT;
	FETCH NEXT FROM XmlBudgetLimitMonthDT INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR, @M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12
	WHILE @@fetch_status=0 
	BEGIN
		SET @INDEX = @INDEX +1
		--------------------------------------------------------------------------------- VALIDATE BEGIN ---------------------------------------------------------------------------------
	-- NẾU ĐƠN VỊ CHỊU CHI PHÍ LÀ ĐƠN VỊ
		SET @l_DEP_ID = (SELECT TOP 1 DEP_ID FROM CM_DEPARTMENT WHERE DEP_CODE = @DEP_CODE AND BRANCH_ID = 'DV0001')
	-- NẾU ĐƠN VỊ CHỊU CHI PHÍ LÀ USER ĐẶC BIỆT
		SET @l_TLNAME = (SELECT TOP 1 TLNANME FROM TL_USER WHERE RoleName = @ROLENAME AND TLSUBBRID = 'DV0001' AND DEP_ID = @l_DEP_ID)
		IF(ISNULL(@l_DEP_ID,'') <> '' AND ISNULL(@ROLENAME, '') <> '')
		BEGIN
			SET @l_KHOI_ID = (	SELECT TOP 1 A.KHOI_ID
								FROM CM_DEPARTMENT A
								WHERE A.DEP_ID = @l_DEP_ID
			)
			SET @l_KHOI_NAME = (SELECT TOP 1 C.DVDM_NAME
								FROM CM_DEPARTMENT B
								LEFT JOIN CM_DVDM C ON B.KHOI_ID = C.DVDM_ID
								WHERE B.DEP_ID = @l_DEP_ID
			)
			SET @l_ROLENAME = @ROLENAME
		END
		ELSE
		BEGIN
			SET @l_KHOI_ID = NULL
			SET @l_KHOI_NAME = NULL
			SET @l_ROLENAME = NULL
		END

		IF(ISNULL(@l_DEP_ID,'') = '')
		BEGIN
			ROLLBACK TRANSACTION
			CLOSE XmlBudgetLimitMonthDT;
			DEALLOCATE XmlBudgetLimitMonthDT;
			SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Phòng ban ' + @DEP_CODE + N' không có trong hệ thống' AS ErrorDesc
			RETURN '-1'
		END
		IF(ISNULL(@ROLENAME,'') <> '' AND ISNULL(@l_DEP_ID,'') = '')
		BEGIN
			ROLLBACK TRANSACTION
			CLOSE XmlBudgetLimitMonthDT;
			DEALLOCATE XmlBudgetLimitMonthDT;
			SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Khối ' + @l_KHOI_NAME + ' hiện tại không có ' + @ROLENAME  AS ErrorDesc
			RETURN '-1'
		END
		--------------------------------------------------------------------------------- VALIDATE END -------------------------------------------------------------------------------------------
		-- NEU DA TON TAI NGAN SACH CHI PHI NAY THI CHI UPDATE
		-- NEU DON VI CHIU CHI PHI LA DON VI
		IF	(	EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_MONTH_LIMIT WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR) 
				AND ISNULL(@l_DEP_ID,'') <> ''  
				AND ISNULL(@l_ROLENAME,'') = ''
			)
		BEGIN
			print 'DON VI'
			print '@GD_ID: ' + @GD_ID;
			print '@l_DEP_ID: ' + @l_DEP_ID;
			print '@BUDGET_YEAR: ' + CONVERT(VARCHAR(20), @BUDGET_YEAR);
			print '@l_ROLENAME: ' + @l_ROLENAME;
			print '@l_KHOI_ID: ' + @l_KHOI_ID;
			print '@l_KHOI_NAME: ' + @l_KHOI_NAME;
			print '@l_TLNAME: ' + @l_TLNAME;
			IF(ISNULL(@M1, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M1 = @M1	WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M2, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M2 = @M2	WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M3, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M3 = @M3	WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M4, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M4 = @M4	WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M5, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M5 = @M5	WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M6, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M6 = @M6	WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M7, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M7 = @M7	WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M8, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M8 = @M8	WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M9, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M9 = @M9	WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M10, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M10 = @M10 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M11, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M11 = @M11 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M12, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M12 = @M12 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END

			IF @@error<>0 GOTO ABORT;
			FETCH NEXT FROM XmlBudgetLimitMonthDT INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR, @M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12
		END
		ELSE IF	(	EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_MONTH_LIMIT WHERE GD_ID = @GD_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND KHOI_ID = @l_KHOI_ID AND BUDGET_YEAR = @BUDGET_YEAR) 
					AND ISNULL(@l_DEP_ID,'') <> '' 
					AND ISNULL(@l_KHOI_ID,'') <> ''
				)
		BEGIN
			print 'USER'
			print '@GD_ID: ' + @GD_ID;
			print '@l_DEP_ID: ' + @l_DEP_ID;
			print '@BUDGET_YEAR: ' + CONVERT(VARCHAR(20), @BUDGET_YEAR);
			print '@l_ROLENAME: ' + @l_ROLENAME;
			print '@l_KHOI_ID: ' + @l_KHOI_ID;
			print '@l_KHOI_NAME: ' + @l_KHOI_NAME;
			print '@l_TLNAME: ' + @l_TLNAME;
			IF(ISNULL(@M1, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M1 = @M1 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M2, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M2 = @M2 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M3, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M3 = @M3 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M4, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M4 = @M4 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M5, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M5 = @M5 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M6, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M6 = @M6 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M7, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M7 = @M7 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M8, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M8 = @M8 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M9, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M9 = @M9 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M10, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M10 = @M10 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M11, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M11 = @M11 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M12, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M12 = @M12 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			

			IF @@error<>0 GOTO ABORT;
			FETCH NEXT FROM XmlBudgetLimitMonthDT INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR, @M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12
		END
		ELSE
		BEGIN
			print 'THEM MOI'
			print '@GD_ID: ' + @GD_ID;
			print '@l_DEP_ID: ' + @l_DEP_ID;
			print '@BUDGET_YEAR: ' + CONVERT(VARCHAR(20), @BUDGET_YEAR);
			print '@l_ROLENAME: ' + @l_ROLENAME;
			print '@l_KHOI_ID: ' + @l_KHOI_ID;
			print '@l_KHOI_NAME: ' + @l_KHOI_NAME;
			print '@l_TLNAME: ' + @l_TLNAME;
			-- Tự động sinh mã code
			DECLARE @BUDGET_MONTH_LIMIT_ID VARCHAR(15);
			EXEC SYS_CodeMasters_Gen 'BUDGET_LIMIT_MONTH', @BUDGET_MONTH_LIMIT_ID OUT;
			IF ISNULL(@BUDGET_MONTH_LIMIT_ID,'') = '' GOTO ABORT;

			-- NEU LA IMPORT USER CHIU CHI PHI
			IF(ISNULL(@l_ROLENAME, '') <> '')
			BEGIN
				INSERT INTO TR_REQ_BUDGET_MONTH_LIMIT(BUDGET_MONTH_LIMIT_ID, GD_ID, BUDGET_TYPE, BRANCH_TYPE, BUDGET_YEAR, BRANCH_ID, DEP_ID, KHOI_ID, TLNAME, ROLENAME,
				M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID, APPROVE_DT)
				VALUES(@BUDGET_MONTH_LIMIT_ID, @GD_ID, 'thang', 'ho', @BUDGET_YEAR, 'DV0001', @l_DEP_ID, @l_KHOI_ID, @l_TLNAME, @l_ROLENAME,
				@M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12, @p_MAKER_ID, GETDATE(), '1', 'A', NULL, NULL)
			END
			ELSE
			BEGIN
				INSERT INTO TR_REQ_BUDGET_MONTH_LIMIT(BUDGET_MONTH_LIMIT_ID, GD_ID, BUDGET_TYPE, BRANCH_TYPE, BUDGET_YEAR, BRANCH_ID, DEP_ID,
				M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID, APPROVE_DT)
				VALUES(@BUDGET_MONTH_LIMIT_ID, @GD_ID, 'thang', 'ho', @BUDGET_YEAR, 'DV0001', @l_DEP_ID,
				@M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12, @p_MAKER_ID, GETDATE(), '1', 'A', NULL, NULL)
			END
			

			IF @@error<>0 GOTO ABORT;
			FETCH NEXT FROM XmlBudgetLimitMonthDT INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR, @M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12
		END
	END
	CLOSE XmlBudgetLimitMonthDT;
	DEALLOCATE XmlBudgetLimitMonthDT

COMMIT TRANSACTION

SELECT '0' AS Result, ''  REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thêm mới thành công' ErrorDesc
RETURN '0'

ABORT:
BEGIN
	ROLLBACK TRANSACTION
	CLOSE XmlBudgetLimitMonthDT;
	DEALLOCATE XmlBudgetLimitMonthDT
	SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, '' ErrorDesc
	RETURN '-1'
END
GO
ALTER   PROC [dbo].[TR_BUDGET_YEAR_DETAIL_IMPORT] (
	@GD_ID VARCHAR(20), 
	@BUDGET_TYPE VARCHAR(20), 
	@BRANCH_TYPE VARCHAR(20), 
	@p_MAKER_ID VARCHAR(20), 
	@p_MONTH_IMPORT VARCHAR(20), 
	@p_XMP_TR_BUDGET_LIMIT_YEAR_DETAIL XML = NULL
)
AS
	IF(@GD_ID IS NULL OR @GD_ID = '')
	BEGIN
		SELECT '-1' as Result, N'Mã ngân sách chi phí không được để trống' AS ErrorDesc
		RETURN '-1'
	END
	IF(@BUDGET_TYPE IS NULL OR @BUDGET_TYPE = '')
	BEGIN
		SELECT '-1' as Result, N'Loại ngân sách chi phí không được để trống' AS ErrorDesc
		RETURN '-1'
	END
	IF(@BRANCH_TYPE IS NULL OR @BRANCH_TYPE = '')
	BEGIN
		SELECT '-1' as Result, N'Loại đơn vị không được để trống' AS ErrorDesc
		RETURN '-1'
	END
BEGIN TRANSACTION

	DECLARE @hdoc INT
	EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TR_BUDGET_LIMIT_YEAR_DETAIL

	DELETE FROM TR_GOODSTYPE WHERE GD_ID = @GD_ID
	INSERT INTO TR_GOODSTYPE(GD_ID, BUDGET_TYPE, CREATE_DT, MAKER_ID, APPROVE_DT, CHECKER_ID, RECORD_STATUS, AUTH_STATUS) 
	VALUES (@GD_ID, 'nam', GETDATE(), @p_MAKER_ID, NULL, NULL, '1', 'A')

	DECLARE XmlBudgetLimitYearhDT CURSOR FOR
	SELECT * FROM OPENXML(@hdoc, '/Root/XmlBudgetLimitYearhDT', 2)
	WITH (
		DEP_CODE VARCHAR(15),
		DEP_NAME NVARCHAR(250),
		ROLENAME NVARCHAR(20),
		BUDGET_YEAR VARCHAR(15),
		BUDGET_LIMIT_AMT DECIMAL(18,2),
		BUDGET_MONTH VARCHAR(50),
		M1 DECIMAL(18, 0),
		M2 DECIMAL(18, 0),
		M3 DECIMAL(18, 0),
		M4 DECIMAL(18, 0),
		M5 DECIMAL(18, 0),
		M6 DECIMAL(18, 0),
		M7 DECIMAL(18, 0),
		M8 DECIMAL(18, 0),
		M9 DECIMAL(18, 0),
		M10 DECIMAL(18, 0),
		M11 DECIMAL(18, 0),
		M12 DECIMAL(18, 0)
	)

	DECLARE 
		@DEP_CODE VARCHAR(15),
		@DEP_NAME NVARCHAR(250),
		@ROLENAME NVARCHAR(20),
		@BUDGET_YEAR VARCHAR(15),
		@BUDGET_LIMIT_AMT DECIMAL(18,2),
		@BUDGET_MONTH VARCHAR(50),
		@M1 DECIMAL(18, 0),
		@M2 DECIMAL(18, 0),
		@M3 DECIMAL(18, 0),
		@M4 DECIMAL(18, 0),
		@M5 DECIMAL(18, 0),
		@M6 DECIMAL(18, 0),
		@M7 DECIMAL(18, 0),
		@M8 DECIMAL(18, 0),
		@M9 DECIMAL(18, 0),
		@M10 DECIMAL(18, 0),
		@M11 DECIMAL(18, 0),
		@M12 DECIMAL(18, 0)
	------------------------------------------------ validate begin ------------------------------------------------

	DECLARE @ERROR_MESSAGE nvarchar(MAX)
	DECLARE @INDEX INT
	SET @INDEX = 2

	OPEN XmlBudgetLimitYearhDT;

	FETCH NEXT FROM XmlBudgetLimitYearhDT 
	INTO @DEP_CODE, @DEP_NAME , @ROLENAME, @BUDGET_YEAR , @BUDGET_LIMIT_AMT, @BUDGET_MONTH, @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
	WHILE @@fetch_status=0 
	BEGIN
		SET @INDEX = @INDEX +1
		----------------------------------------- validate begin -----------------------------------------
		DECLARE @l_DEP_ID VARCHAR(20), @l_TLNAME VARCHAR(20);
		-- NẾU ĐƠN VỊ CHỊU CHI PHÍ LÀ ĐƠN VỊ
		SET @l_DEP_ID = (SELECT TOP 1 DEP_ID FROM CM_DEPARTMENT WHERE DEP_CODE = @DEP_CODE AND BRANCH_ID = 'DV0001')
		-- NẾU ĐƠN VỊ CHỊU CHI PHÍ LÀ USER
		SET @l_TLNAME = (SELECT TOP 1 TLNANME FROM TL_USER WHERE RoleName = @ROLENAME AND TLSUBBRID = 'DV0001' AND DEP_ID = @l_DEP_ID)
		DECLARE @l_KHOI_ID VARCHAR(20), @l_ROLENAME VARCHAR(20), @l_KHOI_NAME NVARCHAR(250);
		IF(ISNULL(@l_DEP_ID,'') <> '' AND ISNULL(@ROLENAME, '') <> '')
		BEGIN
			SET @l_KHOI_ID = (	SELECT TOP 1 A.KHOI_ID
								FROM CM_DEPARTMENT A
								WHERE A.DEP_ID = @l_DEP_ID
			)
			SET @l_KHOI_NAME = (SELECT TOP 1 C.DVDM_NAME
								FROM CM_DEPARTMENT B
								LEFT JOIN CM_DVDM C ON B.KHOI_ID = C.DVDM_ID
								WHERE B.DEP_ID = @l_DEP_ID
			)
			SET @l_ROLENAME = @ROLENAME
		END
		ELSE
		BEGIN
			SET @l_KHOI_ID = NULL
			SET @l_KHOI_NAME = NULL
			SET @l_ROLENAME = NULL
		END

		IF(ISNULL(@l_DEP_ID,'') = '')
		BEGIN
			ROLLBACK TRANSACTION
			CLOSE XmlBudgetLimitYearhDT;
			DEALLOCATE XmlBudgetLimitYearhDT;
			SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Phòng ban ' + @DEP_CODE + N' không có trong hệ thống' AS ErrorDesc
			RETURN '-1'
		END
		IF(ISNULL(@ROLENAME,'') <> '' AND ISNULL(@l_DEP_ID,'') = '')
		BEGIN
			ROLLBACK TRANSACTION
			CLOSE XmlBudgetLimitYearhDT;
			DEALLOCATE XmlBudgetLimitYearhDT;
			SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Khối ' + @l_KHOI_NAME + ' hiện tại không có ' + @ROLENAME  AS ErrorDesc
			RETURN '-1'
		END
		----------------------------------------- validate end -----------------------------------------
		-- NEU DA TON TAI NGAN SACH CHI PHI NAY THI CHI UPDATE
		-- NEU DON VI CHIU CHI PHI LA DON VI
		IF(EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_YEAR_LIMIT where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR) AND ISNULL(@l_DEP_ID,'') <> ''  AND ISNULL(@l_KHOI_ID,'') = '')
		BEGIN
			print 'DON VI'
			print '@GD_ID: ' + @GD_ID;
			print '@l_DEP_ID: ' + @l_DEP_ID;
			print '@BUDGET_YEAR: ' + CONVERT(VARCHAR(20), @BUDGET_YEAR);
			print '@l_ROLENAME: ' + @l_ROLENAME;
			print '@l_KHOI_ID: ' + @l_KHOI_ID;
			print '@l_KHOI_NAME: ' + @l_KHOI_NAME;
			print '@l_TLNAME: ' + @l_TLNAME;
			IF(ISNULL(@M1, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M1 = @M1 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M2, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M2 = @M2 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M3, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M3 = @M3 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M4, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M4 = @M4 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M5, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M5 = @M5 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M6, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M6 = @M6 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M7, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M7 = @M7 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M8, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M8 = @M8 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M9, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M9 = @M9 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M10, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M10 = @M10 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M11, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M11 = @M11 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@M12, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M12 = @M12 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END
			IF(ISNULL(@BUDGET_LIMIT_AMT, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET BUDGET_LIMIT_AMT = @BUDGET_LIMIT_AMT where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
			END

			IF @@error<>0 GOTO ABORT;
			FETCH NEXT FROM XmlBudgetLimitYearhDT 
			INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR , @BUDGET_LIMIT_AMT, @BUDGET_MONTH, @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
		END
		ELSE IF(EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_YEAR_LIMIT where GD_ID = @GD_ID and ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND KHOI_ID = @l_KHOI_ID AND BUDGET_YEAR = @BUDGET_YEAR) AND ISNULL(@l_DEP_ID,'') <> '' AND ISNULL(@l_KHOI_ID,'') <> '')
		BEGIN
			print 'USER'
			print '@GD_ID: ' + @GD_ID;
			print '@l_DEP_ID: ' + @l_DEP_ID;
			print '@BUDGET_YEAR: ' + CONVERT(VARCHAR(20), @BUDGET_YEAR);
			print '@l_ROLENAME: ' + @l_ROLENAME;
			print '@l_KHOI_ID: ' + @l_KHOI_ID;
			print '@l_KHOI_NAME: ' + @l_KHOI_NAME;
			print '@l_TLNAME: ' + @l_TLNAME;
			IF(ISNULL(@M1, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M1 = @M1 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M2, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M2 = @M2 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M3, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M3 = @M3 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M4, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M4 = @M4 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M5, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M5 = @M5 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M6, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M6 = @M6 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M7, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M7 = @M7 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M8, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M8 = @M8 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M9, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M9 = @M9 WHERE GD_ID = @GD_ID	AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M10, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M10 = @M10 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M11, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M11 = @M11 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@M12, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M12 = @M12 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			IF(ISNULL(@BUDGET_LIMIT_AMT, 0) > 0)
			BEGIN
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET TLNAME = @l_TLNAME, BUDGET_LIMIT_AMT = @BUDGET_LIMIT_AMT WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
			END
			

			IF @@error<>0 GOTO ABORT;
			FETCH NEXT FROM XmlBudgetLimitYearhDT 
			INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR , @BUDGET_LIMIT_AMT, @BUDGET_MONTH, @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
		END
		ELSE
		BEGIN
			print 'THEM MOI'
			print '@GD_ID: ' + @GD_ID;
			print '@l_DEP_ID: ' + @l_DEP_ID;
			print '@BUDGET_YEAR: ' + CONVERT(VARCHAR(20), @BUDGET_YEAR);
			print '@l_ROLENAME: ' + @l_ROLENAME;
			print '@l_KHOI_ID: ' + @l_KHOI_ID;
			print '@l_KHOI_NAME: ' + @l_KHOI_NAME;
			print '@l_TLNAME: ' + @l_TLNAME;
			-- Tự động sinh mã code
			DECLARE @BUDGET_YEAR_LIMIT_ID VARCHAR(15);
			EXEC SYS_CodeMasters_Gen 'BUDGET_LIMIT_YEAR', @BUDGET_YEAR_LIMIT_ID OUT;
			IF @BUDGET_YEAR_LIMIT_ID='' OR @BUDGET_YEAR_LIMIT_ID IS NULL GOTO ABORT;

			-- NEU LA IMPORT USER CHIU CHI PHI
			IF(ISNULL(@l_TLNAME, '') <> '')
			BEGIN
				INSERT INTO TR_REQ_BUDGET_YEAR_LIMIT(BUDGET_YEAR_LIMIT_ID, GD_ID, BUDGET_TYPE, BRANCH_TYPE, BUDGET_YEAR, BRANCH_ID, DEP_ID, BUDGET_LIMIT_AMT, KHOI_ID, TLNAME, ROLENAME,
				M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID, APPROVE_DT)
				VALUES(@BUDGET_YEAR_LIMIT_ID, @GD_ID, 'nam', 'ho', @BUDGET_YEAR, 'DV0001', @l_DEP_ID, @BUDGET_LIMIT_AMT, @l_KHOI_ID, @l_TLNAME, @l_ROLENAME,
				@M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12, @p_MAKER_ID, GETDATE(), '1', 'A', NULL, NULL)
			END
			ELSE
			BEGIN
				INSERT INTO TR_REQ_BUDGET_YEAR_LIMIT(BUDGET_YEAR_LIMIT_ID, GD_ID, BUDGET_TYPE, BRANCH_TYPE, BUDGET_YEAR, BRANCH_ID, DEP_ID, BUDGET_LIMIT_AMT,
				M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID, APPROVE_DT)
				VALUES(@BUDGET_YEAR_LIMIT_ID, @GD_ID, 'nam', 'ho', @BUDGET_YEAR, 'DV0001', @l_DEP_ID, @BUDGET_LIMIT_AMT,
				@M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12, @p_MAKER_ID, GETDATE(), '1', 'A', NULL, NULL)
			END
			

			IF @@error<>0 GOTO ABORT;
			FETCH NEXT FROM XmlBudgetLimitYearhDT 
			INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR , @BUDGET_LIMIT_AMT, @BUDGET_MONTH, @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
		END
	
	END
	CLOSE XmlBudgetLimitYearhDT;
	DEALLOCATE XmlBudgetLimitYearhDT
COMMIT TRANSACTION

SELECT '0' as Result, ''  REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thêm mới thành công' ErrorDesc
RETURN '0'

ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, '' ErrorDesc
		RETURN '-1'
End
GO
--04102024_SECRETKEY