ALTER   PROC [dbo].[TR_BUDGET_YEAR_DVKD_DETAIL_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_YEAR_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
	DECLARE @hdoc INT
	EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TR_BUDGET_LIMIT_YEAR_DETAIL

	DELETE FROM TR_GOODSTYPE WHERE GD_ID = @p_GD_ID AND BUDGET_TYPE = 'nam'
	INSERT INTO TR_GOODSTYPE(GD_ID, BUDGET_TYPE, CREATE_DT, MAKER_ID, APPROVE_DT, CHECKER_ID, RECORD_STATUS, AUTH_STATUS) 
	VALUES (@p_GD_ID, 'nam', GETDATE(), @p_MAKER_ID, NULL, NULL, '1', 'A')
	
	DECLARE XmlBudgetLimitYearhDT CURSOR FOR
	SELECT * FROM OPENXML(@hdoc, '/Root/XmlBudgetLimitYearhDT', 2)
	WITH (
		BRANCH_CODE VARCHAR(15),
		BRANCH_NAME NVARCHAR(250),
		BRANCH_KIND VARCHAR(2),
		BUDGET_YEAR VARCHAR(15),
		BUDGET_LIMIT_AMT DECIMAL(18,2),
		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(2),
		@BUDGET_YEAR VARCHAR(15),
		@BUDGET_LIMIT_AMT DECIMAL(18,2),
		@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 @BRANCH_CODE, @BRANCH_NAME, @BRANCH_KIND, @BUDGET_YEAR , @BUDGET_LIMIT_AMT , @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 XmlBudgetLimitYearhDT;
			DEALLOCATE XmlBudgetLimitYearhDT;
			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 XmlBudgetLimitYearhDT;
			DEALLOCATE XmlBudgetLimitYearhDT;
			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_YEAR_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_YEAR_LIMIT SET M1  = @M1  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_YEAR_LIMIT SET M2  = @M2  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_YEAR_LIMIT SET M3  = @M3  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_YEAR_LIMIT SET M4  = @M4  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_YEAR_LIMIT SET M5  = @M5  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_YEAR_LIMIT SET M6  = @M6  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_YEAR_LIMIT SET M7  = @M7  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_YEAR_LIMIT SET M8  = @M8  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_YEAR_LIMIT SET M9  = @M9  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_YEAR_LIMIT SET M10 = @M10 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_YEAR_LIMIT SET M11 = @M11 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_YEAR_LIMIT SET M12 = @M12 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(@BUDGET_LIMIT_AMT, 0) >= 0)																	  
			BEGIN																					  
				UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET BUDGET_LIMIT_AMT = ISNULL(@BUDGET_LIMIT_AMT, 0) 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 XmlBudgetLimitYearhDT 
		INTO @BRANCH_CODE, @BRANCH_NAME, @BRANCH_KIND, @BUDGET_YEAR, @BUDGET_LIMIT_AMT , @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
		END
		ELSE
		BEGIN
			-- 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;

			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, BRANCH_KIND)
			VALUES(@BUDGET_YEAR_LIMIT_ID, @p_GD_ID, 'nam', @p_BRANCH_TYPE, @BUDGET_YEAR, @l_BRANCH_ID, NULL, ISNULL(@BUDGET_LIMIT_AMT, 0), 
			@M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12, @p_MAKER_ID, GETDATE(), '1', 'A', NULL, NULL, @BRANCH_KIND)

			IF @@error<>0 GOTO ABORT;
			FETCH NEXT FROM XmlBudgetLimitYearhDT 
			INTO @BRANCH_CODE, @BRANCH_NAME, @BRANCH_KIND, @BUDGET_YEAR, @BUDGET_LIMIT_AMT , @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