ALTER PROCEDURE [dbo].[PL_COSTCENTER_Upd]
@p_COST_ID VARCHAR(15)=NULL,
@p_COST_CODE	varchar(15)  = NULL,
@p_COST_NAME	NVARCHAR(300)  = NULL,
@p_PLAN_TYPE_ID	varchar(15)  = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(20)  = NULL,
@p_EDIT_DT	varchar(25) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(20)  = NULL,
@p_APPROVE_DT	varchar(25) = NULL,
@p_XMLData XML=NULL
AS
BEGIN TRANSACTION
--Validation is here
		--DECLARE @ERRORSYS NVARCHAR(15) = '' 
		--  IF ( EXISTS ( SELECT * FROM dbo.PL_COSTCENTER WHERE COST_CODE =@p_COST_CODE AND COST_ID<>@p_COST_ID))
		--	 SET @ERRORSYS = 'CODE-0001'
		--IF @ERRORSYS <> '' 
		--BEGIN
		--	ROLLBACK TRANSACTION
		--	SELECT '-1' as Result, @p_COST_ID  COST_ID, N'Mã đơn vị đầu mối :' +@p_COST_ID+ N' đã tồn tại trong hệ thống' ErrorDesc
		--	RETURN '-1'
		--END 
		DECLARE @DVDM_ID VARCHAR(20), @KHOI_ID VARCHAR(15),@COST_ID_MAP_KHOI_OLD VARCHAR(15)
		SET @COST_ID_MAP_KHOI_OLD =(SELECT TOP 1 COST_CODE FROM PL_COSTCENTER WHERE COST_ID =@p_COST_ID)
		SET @DVDM_ID = (SELECT TOP(1) cd.DVDM_ID FROM CM_DVDM cd WHERE cd.DVDM_CODE =@p_COST_CODE AND (cd.IS_DVDM=1 OR cd.IS_DVCM =1))
		--SET @KHOI_ID = (SELECT TOP(1) cd.DVDM_ID FROM CM_DVDM cd WHERE LEFT(cd.DVDM_CODE,5) =LEFT(@p_COST_CODE,5) AND cd.IS_KHOI=1)
		SET @KHOI_ID = (SELECT TOP(1) cd.KHOI_ID FROM CM_DEPARTMENT cd WHERE DEP_CODE =@p_COST_CODE)
		IF(@KHOI_ID IS NULL OR @KHOI_ID ='')
		BEGIN
			SET @KHOI_ID = (SELECT TOP(1) cd.DVDM_ID FROM CM_DVDM cd WHERE LEFT(cd.DVDM_CODE,5) =LEFT(@p_COST_CODE,5) AND cd.IS_KHOI=1)
		END
		IF(@DVDM_ID IS NOT NULL AND @DVDM_ID <> '')
		BEGIN
			UPDATE dbo.PL_COSTCENTER SET  
			COST_CODE=@p_COST_CODE,
			[PLAN_TYPE_ID] = @p_PLAN_TYPE_ID,
			[COST_NAME] = @p_COST_NAME,
			[NOTES] = @p_NOTES,
			[RECORD_STATUS] = @p_RECORD_STATUS,
			[EDITER_ID] = @p_MAKER_ID,
			[EDIT_DT] = CONVERT(DATETIME, @p_EDIT_DT, 103),
			[AUTH_STATUS] = @p_AUTH_STATUS,
			[CHECKER_ID] = @p_CHECKER_ID,
			[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103),
			DVDM_ID =@DVDM_ID
			WHERE COST_ID  = @p_COST_ID
		END
		ELSE
		BEGIN
			IF (@DVDM_ID IS NULL)
			BEGIN
				EXEC SYS_CodeMasters_Gen 'CM_DVDM', @DVDM_ID OUT
				IF @DVDM_ID='' OR @DVDM_ID IS NULL GOTO ABORT
				INSERT INTO CM_DVDM (DVDM_ID, DVDM_CODE, DVDM_NAME, NOTES, RECORD_STATUS, AUTH_STATUS, MAKER_ID, CREATE_DT, EDITER_ID, EDIT_DT, CHECKER_ID, APPROVE_DT, IS_DVDM, IS_KHOI, IS_DVCM, IS_PTGD, IS_GDK)
				VALUES (@DVDM_ID, @p_COST_CODE, @p_COST_NAME, NULL, 1, 'A', @p_MAKER_ID, GETDATE(), '', GETDATE(), @p_MAKER_ID, GETDATE(), 1, 0, 0, 0, 0);
				--- UPDATE LAI DVCM
				UPDATE dbo.PL_COSTCENTER SET  
				COST_CODE=@p_COST_CODE,
				[PLAN_TYPE_ID] = @p_PLAN_TYPE_ID,
				[COST_NAME] = @p_COST_NAME,
				[NOTES] = @p_NOTES,
				[RECORD_STATUS] = @p_RECORD_STATUS,
				[EDITER_ID] = @p_MAKER_ID,
				[EDIT_DT] = CONVERT(DATETIME, @p_EDIT_DT, 103),
				[AUTH_STATUS] = @p_AUTH_STATUS,
				[CHECKER_ID] = @p_CHECKER_ID,
				[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103),
				DVDM_ID =@DVDM_ID
				WHERE COST_ID  = @p_COST_ID
				--- END
			END
		END
		--- KIEM TRA XEM NEU COSTCENTER ĐÓ CHƯA ĐƯỢC MAPPING VỚI KHỐI THÌ THỰC HIỆN MAPPING
		UPDATE PL_COSTCENTER SET DVDM_ID =@KHOI_ID WHERE COST_CODE =@COST_ID_MAP_KHOI_OLD AND COST_ID <> @p_COST_ID
		--- LUCTV 11.04.2023 THÊM VÀO BẢNG CM_DVDM_KHOI
		--IF(EXISTS(SELECT * FROM CM_DVDM_KHOI WHERE DVDM_ID =@DVDM_ID))
		--BEGIN
		--	UPDATE CM_DVDM_KHOI SET KHOI_ID =@KHOI_ID WHERE DVDM_ID =@DVDM_ID
		--END
		--ELSE
		--BEGIN
		--	INSERT INTO CM_DVDM_KHOI (DVDM_ID, KHOI_ID) VALUES (@DVDM_ID, @KHOI_ID)
		--END
		--- END LUCTV 11.04.2023
		IF @@Error <> 0 GOTO ABORT

		Declare @hdoc INT
		Exec sp_xml_preparedocument @hdoc Output, @p_XMLData	

	DECLARE CostDT CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/COSTDT',2)
	WITH 
	(
		BRANCH_ID VARCHAR(15),
		DEP_ID	varchar(15),
		NOTES NVARCHAR(1000)
	)
	
	OPEN CostDT
	DECLARE @BRANCH_ID VARCHAR(15),@DEP_ID	varchar(15),@NOTES NVARCHAR(1000)

	DELETE FROM dbo.PL_COSTCENTER_DT WHERE COST_ID=@p_COST_ID

	FETCH NEXT FROM CostDT INTO @BRANCH_ID, @DEP_ID,@NOTES
	
	WHILE @@FETCH_STATUS = 0	
	BEGIN			
			DECLARE @l_COSTDT_ID VARCHAR(20)
			EXEC SYS_CodeMasters_Gen 'PL_COSTCENTER_DT', @l_COSTDT_ID out
			IF @l_COSTDT_ID='' OR @l_COSTDT_ID IS NULL GOTO ABORT		
			INSERT INTO dbo.PL_COSTCENTER_DT
			(
			    COSTDT_ID,
			    COST_ID,
			    BRANCH_ID,
			    DEP_ID,
			    NOTES,
			    RECORD_STATUS,
			    MAKER_ID,
			    CREATE_DT
			  
			)
			VALUES
			(   @l_COSTDT_ID,        -- COSTDT_ID - varchar(20)
			    @p_COST_ID,        -- COST_ID - varchar(20)
			    @BRANCH_ID,        -- BRANCH_ID - varchar(15)
			    @DEP_ID,        -- DEP_ID - varchar(15)
			    @NOTES,       -- NOTES - nvarchar(1000)
			    '1',        -- RECORD_STATUS - varchar(1)
			    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
			    NULL -- CREATE_DT - datetime --26042023_secretkey
			    )
		
		IF @@Error <> 0 GOTO ABORT	
		FETCH NEXT FROM CostDT INTO @BRANCH_ID, @DEP_ID,@NOTES	
		PRINT @l_COSTDT_ID
	END
	CLOSE CostDT
	DEALLOCATE CostDT
	---- 12.04.2023 LUCTV BỔ SUNG MAPPING CM_DVDM_KHỐI
	UPDATE CM_DVDM_KHOI SET KHOI_ID =@KHOI_ID WHERE DVDM_ID =@DVDM_ID
	---- END LUCTV 12.04.2023
COMMIT TRANSACTION
		SELECT '0' as Result, @p_COST_ID  COST_ID, '' ErrorDesc
		RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' COST_ID, '' ErrorDesc
		RETURN '-1'
End