ALTER PROCEDURE dbo.PL_IMPORT_Upd
@p_IMPORT_ID VARCHAR(20),
@p_PLAN_CODE VARCHAR(20),
@p_IMP_YEAR	INT  = NULL,
@p_IMP_NAME NVARCHAR(200),
@p_FILE_NAME	NVARCHAR(200)  = NULL,
@p_TOTAL_QTY	NUMERIC(18,0)  = NULL,
@p_TOTAL_ATM	NUMERIC(18,0)  = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(20)  = NULL,
@p_CREATE_DT	varchar(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(20)  = NULL,
@p_APPROVE_DT	varchar(20) = NULL,
@p_XMLData XML=NULL
AS
BEGIN TRANSACTION
DECLARE @TableTmp TABLE(
		IMP_YEAR VARCHAR(4),
		PLAN_TYPE_ID VARCHAR(15),
		PLAN_TYPE_CODE VARCHAR(15),
		PLAN_TYPE_NAME NVARCHAR(200), 
		COST_ID VARCHAR(15),
		COST_CODE VARCHAR(100),
		COST_NAME NVARCHAR(200),
		BRANCH_ID VARCHAR(15),
		BRANCH_CODE VARCHAR(15),
		BRANCH_NAME NVARCHAR(200),
		DEP_ID	varchar(15),
		DEP_CODE VARCHAR(15),
		DEP_NAME NVARCHAR(200),
		REF_ID INT,
		REF_CODE VARCHAR(20),
		REF_NAME NVARCHAR(200),
		GOOD_ID varchar(15),
		GOOD_CODE VARCHAR(20),
		GOOD_NAME NVARCHAR(200),
		QTY INT,
		TOTAL_AMT NUMERIC(18,2),
		NOTES NVARCHAR(1000)
);
Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output, @p_XMLData	

INSERT INTO @TableTmp
 SELECT *
	FROM OPENXML(@hDoc,'/Root/XmlData',2)
	WITH 
	(
	    IMP_YEAR VARCHAR(4),
		PLAN_TYPE_ID VARCHAR(15),
		PLAN_TYPE_CODE VARCHAR(15),
		PLAN_TYPE_NAME NVARCHAR(200), 
		COST_ID VARCHAR(15),
		COST_CODE VARCHAR(100),
		COST_NAME NVARCHAR(200),
		BRANCH_ID VARCHAR(15),
		BRANCH_CODE VARCHAR(15),
		BRANCH_NAME NVARCHAR(200),
		DEP_ID	varchar(15),
		DEP_CODE VARCHAR(15),
		DEP_NAME NVARCHAR(200),
		REF_ID INT,
		REF_CODE VARCHAR(20),
		REF_NAME NVARCHAR(200),
		GOOD_ID varchar(15),
		GOOD_CODE VARCHAR(20),
		GOOD_NAME NVARCHAR(200),
		QTY INT,
		TOTAL_AMT NUMERIC(18,2),
		NOTES NVARCHAR(1000)
	)

	DECLARE @YEAR_CURR VARCHAR(4)
	SET @YEAR_CURR = (SELECT IMP_YEAR FROM dbo.PL_IMPORT WHERE IMPORT_ID=@p_IMPORT_ID)
	IF(EXISTS(SELECT * FROM @TableTmp WHERE  IMP_YEAR <> @YEAR_CURR))
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, N'Cập nhật thất bại, sai năm kế hoạch' ErrorDesc
		RETURN '-1'
	END

	--Update ID
UPDATE @TableTmp  SET BRANCH_ID=(SELECT  TOP 1 BRANCH_ID FROM dbo.CM_BRANCH WHERE dbo.CM_BRANCH.BRANCH_CODE=[@TableTmp].BRANCH_CODE),
DEP_ID=(SELECT  TOP 1 DEP_ID FROM dbo.CM_DEPARTMENT WHERE dbo.CM_DEPARTMENT.DEP_CODE=[@TableTmp].DEP_CODE),
COST_ID=(SELECT  TOP 1 DVDM_ID FROM dbo.CM_DVDM WHERE dbo.CM_DVDM.DVDM_CODE=[@TableTmp].COST_CODE AND IS_DVDM=1),
PLAN_TYPE_ID=(SELECT  TOP 1 PLAN_TYPE_ID FROM dbo.CM_PLAN_TYPE WHERE dbo.CM_PLAN_TYPE.PLAN_TYPE_CODE=[@TableTmp].PLAN_TYPE_CODE),
GOOD_ID=(SELECT  TOP 1 GD_ID FROM dbo.CM_GOODS WHERE dbo.CM_GOODS.GD_CODE=[@TableTmp].GOOD_CODE)

-- GIANT UPDATE NAME
--Update ID
UPDATE @TableTmp  SET BRANCH_NAME=(SELECT TOP 1 BRANCH_NAME FROM dbo.CM_BRANCH WHERE dbo.CM_BRANCH.BRANCH_CODE=[@TableTmp].BRANCH_CODE),
DEP_NAME=(SELECT TOP 1 DEP_NAME FROM dbo.CM_DEPARTMENT WHERE dbo.CM_DEPARTMENT.DEP_CODE=[@TableTmp].DEP_CODE),
COST_NAME=(SELECT TOP 1 DVDM_NAME FROM dbo.CM_DVDM WHERE dbo.CM_DVDM.DVDM_CODE=[@TableTmp].COST_CODE AND IS_DVDM=1 ),
PLAN_TYPE_NAME=(SELECT TOP 1 PLAN_TYPE_NAME FROM dbo.CM_PLAN_TYPE WHERE dbo.CM_PLAN_TYPE.PLAN_TYPE_CODE=[@TableTmp].PLAN_TYPE_CODE),
GOOD_NAME=(SELECT TOP 1 GD_NAME FROM dbo.CM_GOODS WHERE dbo.CM_GOODS.GD_CODE=[@TableTmp].GOOD_CODE)




DECLARE @Error NVARCHAR(MAX)
--- Validate dữ liệu
IF(EXISTS(SELECT BRANCH_ID FROM @TableTmp WHERE BRANCH_ID IS NULL OR BRANCH_ID=''))
BEGIN

SET @Error=(select STUFF( (select ';' + BRANCH_CODE from @TableTmp  WHERE BRANCH_ID IS NULL OR BRANCH_ID='' FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, ''))	
	

		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, N'Mã đơn vị chưa tồn tại trên hệ thống: '+ @Error ErrorDesc
		RETURN '-1'



END



IF(EXISTS(SELECT DEP_ID FROM @TableTmp WHERE DEP_ID IS NULL OR DEP_ID=''))
BEGIN

		
		SET @Error=(select STUFF( (select ';' + DEP_CODE from @TableTmp  WHERE DEP_ID IS NULL OR DEP_ID='' FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, ''))

		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, N'Mã phòng ban chưa tồn tại trên hệ thống: '+@Error ErrorDesc
		RETURN '-1'
END

IF(EXISTS(SELECT COST_ID FROM @TableTmp WHERE (COST_ID IS NULL OR COST_ID='') AND COST_CODE IS NOT NULL AND COST_CODE <>''))
BEGIN

			SET @Error=(select STUFF( (select ';' + COST_CODE from @TableTmp  WHERE COST_ID IS NULL OR COST_ID='' FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, ''))	
		
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, N'Mã đơn vị đầu mối chưa tồn tại trên hệ thống: '+@Error ErrorDesc
		RETURN '-1'
END

IF(EXISTS(SELECT PLAN_TYPE_ID FROM @TableTmp WHERE PLAN_TYPE_ID IS NULL OR PLAN_TYPE_ID=''))
BEGIN


		SET @Error=(select STUFF( (select ';' + PLAN_TYPE_CODE from @TableTmp  WHERE PLAN_TYPE_ID IS NULL OR PLAN_TYPE_ID='' FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, ''))	

		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, N'Mã loại ngân sách chưa tồn tại trên hệ thống: '+@Error ErrorDesc
		RETURN '-1'
END

IF(EXISTS(SELECT GOOD_ID FROM @TableTmp WHERE GOOD_ID IS NULL OR GOOD_ID=''))
BEGIN

		SET @Error=(select STUFF( (select ';' + GOOD_CODE from @TableTmp  WHERE GOOD_ID IS NULL OR GOOD_ID='' FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, ''))	

		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, N'Mã hạn mục chưa tồn tại trên hệ thống: '+@Error ErrorDesc
		RETURN '-1'
END





	--IF(EXISTS(
	--SELECT DT.TRADE_ID FROM dbo.PL_MASTER PM 
	--LEFT JOIN dbo.PL_TRADEDETAIL DT ON PM.PLAN_ID=DT.PLAN_ID
	--WHERE EXISTS()
	--BEGIN

	--		ROLLBACK TRANSACTION
	--		SELECT '-1' as Result, '' IMPORT_ID, N'Hạn mục chỉnh sửa đã sử dụng vượt hạn mức chỉnh sửa' ErrorDesc
	--		RETURN '-1'
	--END
  SET @p_AUTH_STATUS = 'U'
	UPDATE dbo.PL_IMPORT_DT SET IS_UPDATE=0 WHERE IMPORT_ID=@p_IMPORT_ID

	UPDATE dbo.PL_IMPORT SET PLAN_CODE=@p_PLAN_CODE,IMP_NAME=@p_IMP_NAME,FILE_NAME=@p_FILE_NAME,IMP_QTY=@p_TOTAL_QTY,
	IMP_ATM=@p_TOTAL_ATM,NOTES=@p_NOTES,EDITER_ID=@p_MAKER_ID,EDIT_DT=convert(datetime,@p_CREATE_DT,103),CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=convert(datetime,@p_APPROVE_DT,103),AUTH_STATUS=@p_AUTH_STATUS,IS_UPDATE=1
	WHERE IMPORT_ID=@p_IMPORT_ID


	
	DECLARE ImportDT CURSOR FOR
	SELECT IMP_YEAR,
           PLAN_TYPE_ID,
           PLAN_TYPE_CODE,
           PLAN_TYPE_NAME,
           COST_ID,
           COST_CODE,
           COST_NAME,
           BRANCH_ID,
           BRANCH_CODE,
           BRANCH_NAME,
           DEP_ID,
           DEP_CODE,
           DEP_NAME,
		  REF_ID,
		  REF_CODE,
		  REF_NAME,
           GOOD_ID,
           GOOD_CODE,
           GOOD_NAME,
           QTY,
           TOTAL_AMT,
           NOTES FROM @TableTmp
	
	OPEN ImportDT


	DECLARE 
	    @PLAN_TYPE_ID VARCHAR(15),
		@PLAN_TYPE_CODE VARCHAR(15),
		@PLAN_TYPE_NAME NVARCHAR(200), 
		@COST_ID VARCHAR(15),
		@COST_CODE VARCHAR(15),
		@COST_NAME NVARCHAR(200),
		@BRANCH_ID VARCHAR(15),
		@BRANCH_CODE VARCHAR(15),
		@BRANCH_NAME NVARCHAR(200),
		@DEP_ID	varchar(15),
		@DEP_CODE VARCHAR(15),
		@DEP_NAME NVARCHAR(200),
		@REF_ID INT,
		@REF_CODE VARCHAR(20),
		@REF_NAME NVARCHAR(200),
		@GOOD_ID varchar(15),
		@GODD_CODE VARCHAR(20),
		@GOOD_NAME NVARCHAR(200),@QTY INT,@TOTAL_AMT NUMERIC(18,2),@NOTES NVARCHAR(1000),
		@IMP_YEAR VARCHAR(4)

		,@IMP_LOG_CODE VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'IMP_LOG_CODE', @IMP_LOG_CODE out
		IF @IMP_LOG_CODE='' OR @IMP_LOG_CODE IS NULL GOTO ABORT		

	FETCH NEXT FROM ImportDT INTO @IMP_YEAR, @PLAN_TYPE_ID, @PLAN_TYPE_CODE,@PLAN_TYPE_NAME,@COST_ID,@COST_CODE,@COST_NAME,@BRANCH_ID,@BRANCH_CODE,
	@BRANCH_NAME,@DEP_ID,@DEP_CODE,@DEP_NAME,@REF_ID,@REF_CODE,@REF_NAME,@GOOD_ID,@GODD_CODE,@GOOD_NAME,@QTY,@TOTAL_AMT,@NOTES
	
	WHILE @@FETCH_STATUS = 0	
	BEGIN		
	
		DECLARE @l_IMPORTDT_ID VARCHAR(20)

		SET @l_IMPORTDT_ID= (SELECT TOP 1 IMP_DT_ID FROM  PL_IMPORT_DT WHERE IMPORT_ID=@p_IMPORT_ID AND ISNULL(PLAN_TYPE_ID,'')=ISNULL(@PLAN_TYPE_ID,'') AND ISNULL(COST_ID,'')=ISNULL(@COST_ID,'') AND BRANCH_ID=@BRANCH_ID AND ISNULL(REF_CODE,'')= ISNULL(@REF_CODE,'')
		AND ISNULL(DEP_ID,'')=ISNULL(@DEP_ID,'') AND ISNULL(GOOD_ID,'')=ISNULL(@GOOD_ID,''))
		IF(@l_IMPORTDT_ID IS NULL OR @l_IMPORTDT_ID='')
		BEGIN
			EXEC SYS_CodeMasters_Gen 'IMPORT_DT', @l_IMPORTDT_ID out
			IF @l_IMPORTDT_ID='' OR @l_IMPORTDT_ID IS NULL GOTO ABORT	
				INSERT INTO dbo.PL_IMPORT_DT
			(
		    IMP_DT_ID,
		    IMPORT_ID,
		    PLAN_TYPE_ID,
			PLAN_TYPE_CODE,
			PLAN_TYPE_NAME,
		    COST_ID,
			COST_CODE,
			COST_NAME,
		    BRANCH_ID,
			BRANCH_CODE,
			BRANCH_NAME,
		    DEP_ID,
			DEP_CODE,
			DEP_NAME,
			REF_CODE,
			REF_NAME,
		    GOOD_ID,
			GOOD_CODE,
			GOOD_NAME,
		    QTY,
		    TOTAL_AMT,
		    NOTES,
			IMP_YEAR,
			IS_UPDATE
		)
		VALUES
		(   @l_IMPORTDT_ID,   -- IMP_DT_ID - varchar(20)
		    @p_IMPORT_ID,   -- IMPORT_ID - varchar(20)
		   @PLAN_TYPE_ID, @PLAN_TYPE_CODE,@PLAN_TYPE_NAME,@COST_ID,@COST_CODE,@COST_NAME,@BRANCH_ID,@BRANCH_CODE,
	        @BRANCH_NAME,@DEP_ID,@DEP_CODE,@DEP_NAME,@REF_CODE,@REF_NAME,@GOOD_ID,@GODD_CODE,@GOOD_NAME,@QTY,@TOTAL_AMT,@NOTES,@IMP_YEAR,1
		  )
		END
	
		ELSE
		BEGIN
			UPDATE dbo.PL_IMPORT_DT SET QTY=@QTY,TOTAL_AMT=@TOTAL_AMT,REF_NAME=@REF_NAME,IS_UPDATE=1
			WHERE IMP_DT_ID=@l_IMPORTDT_ID
		END
		  INSERT INTO dbo.PL_IMPORT_LOG
		  (
		     IMP_LOG_CODE,
		      IMP_ID,
		      FILE_NAME,
		      IMP_YEAR,
		      NOTES,
		    RECORD_STATUS,
		    AUTH_STATUS,
		    MAKER_ID,
		    CREATE_DT,
		    EDITER_ID,
		    EDIT_DT,
		    CHECKER_ID,
		    APPROVE_DT,
			PLAN_TYPE_ID,
			PLAN_TYPE_CODE,
			PLAN_TYPE_NAME,
		    COST_ID,
			COST_CODE,
			COST_NAME,
		    BRANCH_ID,
			BRANCH_CODE,
			BRANCH_NAME,
		    DEP_ID,
			DEP_CODE,
			DEP_NAME,
			REF_CODE,
			REF_NAME,
		    GOOD_ID,
			GOOD_CODE,
			GOOD_NAME,
		      QTY,
		      TOTAL_AMT
		  )
		  VALUES
		  (   
				@IMP_LOG_CODE,
		      @p_IMPORT_ID,        -- IMP_ID_OLD - varchar(20)
		      @p_FILE_NAME,       -- FILE_NAME - nvarchar(200)
		      @p_IMP_YEAR,         -- IMP_YEAR - int
		      @p_NOTES,       -- NOTES - nvarchar(1000)
		      @p_RECORD_STATUS,        -- RECORD_STATUS - varchar(1)
		      @p_AUTH_STATUS,        -- AUTH_STATUS - varchar(1)
		      @p_MAKER_ID,        -- MAKER_ID - varchar(15)
		      convert(datetime,@p_CREATE_DT,103), -- CREATE_DT - datetime
			  @p_MAKER_ID,        -- EDITER_ID - varchar(15)
		      convert(datetime,@p_CREATE_DT,103), -- EDIT_DT - datetime
		      @p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
		      convert(datetime,@p_APPROVE_DT,103),  -- APPROVE_DT - datetime
		      @PLAN_TYPE_ID, @PLAN_TYPE_CODE,@PLAN_TYPE_NAME,@COST_ID,@COST_CODE,@COST_NAME,@BRANCH_ID,@BRANCH_CODE,
				@BRANCH_NAME,@DEP_ID,@DEP_CODE,@DEP_NAME,@REF_CODE,@REF_NAME,@GOOD_ID,@GODD_CODE,@GOOD_NAME,
		      @QTY,    -- QTY - int
		      @TOTAL_AMT -- TOTAL_AMT - numeric(18, 2)
		      )
		  	
		
		IF @@Error <> 0 GOTO ABORT	
	
		FETCH NEXT FROM ImportDT INTO  @IMP_YEAR,@PLAN_TYPE_ID, @PLAN_TYPE_CODE,@PLAN_TYPE_NAME,@COST_ID,@COST_CODE,@COST_NAME,@BRANCH_ID,@BRANCH_CODE,
	@BRANCH_NAME,@DEP_ID,@DEP_CODE,@DEP_NAME,@REF_ID,@REF_CODE,@REF_NAME,@GOOD_ID,@GODD_CODE,@GOOD_NAME	,@QTY,@TOTAL_AMT,@NOTES
	
	END
	CLOSE ImportDT
	DEALLOCATE ImportDT

	SET @p_TOTAL_ATM=(SELECT SUM(ISNULL(TOTAL_AMT,0)) FROM dbo.PL_IMPORT_DT WHERE IMPORT_ID=@p_IMPORT_ID)
	SELECT @p_TOTAL_QTY=(SELECT SUM(ISNULL(QTY,0)) FROM dbo.PL_IMPORT_DT WHERE IMPORT_ID=@p_IMPORT_ID)

	UPDATE dbo.PL_IMPORT SET IMP_QTY=@p_TOTAL_QTY,IMP_ATM=@p_TOTAL_ATM WHERE IMPORT_ID=@p_IMPORT_ID
		IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
SELECT '0' as Result, @p_IMPORT_ID  IMPORT_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' IMPORT_ID, '' ErrorDesc
		RETURN '-1'
End