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

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