DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

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