ALTER TABLE dbo.ASS_COLLECT_MULTI_DT
	ADD EMP_ID_USE VARCHAR(20) NULL 
GO
ALTER PROCEDURE dbo.ASS_COLLECT_MULTI_MASTER_Ins
@p_BRANCH_ID	varchar(15)  = NULL,
@p_COLLECT_DT	VARCHAR(20) = NULL,
@p_USER_COLLECT	nvarchar(200)  = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_AUTH_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(100)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_CHECKER_ID	varchar(100)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS_KT	varchar(15)  = NULL,
@p_CREATE_DT_KT	varchar(100) = NULL,
@p_APPROVE_DT_KT	VARCHAR(20) = NULL,
@p_MAKER_ID_KT	varchar(100)  = NULL,
@p_CHECKER_ID_KT	varchar(100)  = NULL,
@p_REPORT_STATUS	varchar(15)  = NULL,
@p_BRANCH_CREATE varchar(15)  = NULL,
@p_REQ_ID varchar(15)  = NULL,
@p_CONTENT NVARCHAR(MAX) = NULL, --Nội dung biên bản
@p_XmlData XML = NUL
AS
DECLARE 
		@ASSET_ID	varchar(15),
		--DVSD
		@BRANCH_ID varchar(15),
		@LOCATION	Nvarchar(500),
		@PURPOSE_ID	Nvarchar(500),
		@COLLECT_NOTE nvarchar(1000),
		@BRANCH_ID_RECEIVE	VARCHAR(15) = NULL,
		@DEPT_ID_RECEIVE	VARCHAR(15) = NULL,
		--PHONG BAN SU DUNG
		@DEPT_ID_USE	VARCHAR(15) = NULL,
		@IS_LIQ varchar(1)= '0',
		@l_DEPT_CREATE VARCHAR(15) = (SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME = @p_MAKER_ID),
    @NOTES NVARCHAR(MAX),
    @l_REMAIN_VALUE	DECIMAL(18,0),
    @l_BUY_PRICE	DECIMAL(18,0),
    @EMP_ID_USE VARCHAR(15)

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

	DECLARE XmlData CURSOR FOR
	SELECT *
	FROM OPENXML(@hdoc,'/Root/XmlData',2)
	WITH 
	(
		ASSET_ID	varchar(15),
		BRANCH_ID varchar(15),
		LOCATION	Nvarchar(500),
		PURPOSE_ID	Nvarchar(500),
		COLLECT_NOTE nvarchar(1000),
		BRANCH_ID_RECEIVE	VARCHAR(15),
		DEPT_ID_RECEIVE	VARCHAR(15),
		DEPT_ID_USE	VARCHAR(15),
		IS_LIQ varchar(1),
    NOTES NVARCHAR(MAX)
	)
	OPEN XmlData

BEGIN TRANSACTION
DECLARE @l_COL_MULTI_MASTER_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'ASS_COLLECT_MULTI_MASTER', @l_COL_MULTI_MASTER_ID out
		IF @l_COL_MULTI_MASTER_ID='' OR @l_COL_MULTI_MASTER_ID IS NULL GOTO ABORT
		INSERT INTO ASS_COLLECT_MULTI_MASTER([COL_MULTI_MASTER_ID],[BRANCH_ID],[COLLECT_DT],
		[USER_COLLECT],[NOTES],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],
		[APPROVE_DT],[AUTH_STATUS_KT],[CREATE_DT_KT],[APPROVE_DT_KT],[MAKER_ID_KT],[CHECKER_ID_KT],
		[REPORT_STATUS],CORE_NOTE,DEPT_CREATE,REQ_ID, CONTENT)
		VALUES(@l_COL_MULTI_MASTER_ID ,@p_BRANCH_ID ,CONVERT(DATETIME, @p_COLLECT_DT, 103) ,@p_USER_COLLECT ,@p_NOTES ,
		@p_RECORD_STATUS ,@p_AUTH_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,
		CONVERT(DATETIME, @p_APPROVE_DT, 103) ,'E' ,CONVERT(DATETIME, @p_CREATE_DT_KT, 103) ,CONVERT(DATETIME, @p_APPROVE_DT_KT, 103) 
		,NULL ,@p_CHECKER_ID_KT ,@p_REPORT_STATUS,'',@l_DEPT_CREATE,@p_REQ_ID,@p_CONTENT)
		IF @@Error <> 0 GOTO ABORT

		--Insert XmlData
		FETCH NEXT FROM XmlData INTO  @ASSET_ID,@BRANCH_ID,@LOCATION,@PURPOSE_ID,@COLLECT_NOTE,@BRANCH_ID_RECEIVE,@DEPT_ID_RECEIVE,@DEPT_ID_USE,@IS_LIQ,@NOTES
		WHILE @@FETCH_STATUS = 0
		BEGIN
			
         IF(@BRANCH_ID_RECEIVE = 'DV0001')
         BEGIN
            IF(@DEPT_ID_RECEIVE IS NULL OR @DEPT_ID_RECEIVE = '')
            BEGIN
      					CLOSE XmlData
      					DEALLOCATE XmlData
      					ROLLBACK TRANSACTION
      					SELECT '-1' as Result, @l_COL_MULTI_MASTER_ID COL_MULTI_MASTER_ID, N'Vui lòng chọn phòng ban thu hồi' ErrorDesc
      					RETURN '-1'
            END
         END

				DECLARE @ERRORSYS NVARCHAR(200) = '',@ASSET_CODE nVARCHAR(20)
--				IF ( EXISTS ( SELECT * FROM ASS_COLLECT_MULTI_DT WHERE [ASSET_ID] = @ASSET_ID AND AUTH_STATUS<>'A' AND [CHECKER_ID_KT] <> 'system'))
--				  BEGIN
--					 SET @ASSET_CODE=(SELECT ASSET_CODE FROM ASS_MASTER WHERE  [ASSET_ID] = @ASSET_ID)
--					 SET @ERRORSYS = N'Mã: '+@ASSET_CODE+N' đang được thu hồi'
--					CLOSE XmlData
--					DEALLOCATE XmlData
--					ROLLBACK TRANSACTION
--					SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, @ERRORSYS ErrorDesc
--					RETURN '-1'
--				
--				  END
--
--				  IF ( EXISTS ( SELECT * FROM ASS_COLLECT WHERE [ASSET_ID] = @ASSET_ID AND AUTH_STATUS<>'A'))
--				  BEGIN
--					 SET @ASSET_CODE=(SELECT ASSET_CODE FROM ASS_MASTER WHERE  [ASSET_ID] = @ASSET_ID)
--					 SET @ERRORSYS = N'Mã: '+@ASSET_CODE+N' đang được thu hồi'
--					CLOSE XmlData
--					DEALLOCATE XmlData
--					ROLLBACK TRANSACTION
--					SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, @ERRORSYS ErrorDesc
--					RETURN '-1'
--				
--				  END

				DECLARE @l_COLLECT_MULTI_ID VARCHAR(15)
				EXEC SYS_CodeMasters_Gen 'ASS_COLLECT_MULTI_DT', @l_COLLECT_MULTI_ID out

				--LUCTV: 27-12-2018 BO SUNG LAY DON VI SU DUNG, DON VI TAO TS TAI THOI DIEM THU HOI
				SET @p_BRANCH_ID = (SELECT BRANCH_ID FROM ASS_MASTER WHERE ASSET_ID =@ASSET_ID)
				SET @p_BRANCH_CREATE =(SELECT BRANCH_CREATE FROM ASS_MASTER WHERE ASSET_ID =@ASSET_ID)
				SET @DEPT_ID_USE = (SELECT DEPT_ID FROM ASS_MASTER WHERE ASSET_ID =@ASSET_ID)
				--END
        
        --PHUCVH 27/02/23 THÊM NGUYÊN GIÁ VÀ GIÁ TRỊ CÒN LẠI TẠI THỜI ĐIỂM TẠO PHIẾU
        SELECT @l_BUY_PRICE = A.BUY_PRICE, @l_REMAIN_VALUE = ISNULL((A.AMORT_AMT - A.AMORTIZED_AMT), ISNULL(A.AMORT_AMT,0)), @EMP_ID_USE = A.EMP_ID
        FROM ASS_MASTER A
        WHERE A.ASSET_ID = @ASSET_ID

				IF @l_COLLECT_MULTI_ID='' OR @l_COLLECT_MULTI_ID IS NULL GOTO ABORT
				INSERT INTO ASS_COLLECT_MULTI_DT([COLLECT_MULTI_ID],[COL_MULTI_MASTER_ID],[ASSET_ID],[BRANCH_ID],[PURPOSE_ID],[COLLECT_DT],[LOCATION],[COLLECT_NOTE],[RECORD_STATUS],
				[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[BRANCH_CREATE],[AUTH_STATUS_KT],[CREATE_DT_KT],[APPROVE_DT_KT],[MAKER_ID_KT],[CHECKER_ID_KT],[REPORT_STATUS],
				[BRANCH_ID_RECEIVE], [DEPT_ID_RECEIVE], [DEPT_ID_USE],[IS_LIQ],NOTES,BUY_PRICE,REMAIN_VALUE,EMP_ID_USE)

				VALUES(@l_COLLECT_MULTI_ID ,@l_COL_MULTI_MASTER_ID ,@ASSET_ID ,ISNULL(@p_BRANCH_ID,'') ,@PURPOSE_ID ,CONVERT(DATETIME, @p_COLLECT_DT, 103) ,@LOCATION ,@COLLECT_NOTE ,
				@p_RECORD_STATUS ,@p_AUTH_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103) ,@p_BRANCH_CREATE,
				@p_AUTH_STATUS_KT ,CONVERT(DATETIME, @p_CREATE_DT_KT, 103) ,CONVERT(DATETIME, @p_APPROVE_DT_KT, 103) ,@p_MAKER_ID_KT ,@p_CHECKER_ID_KT ,'N',
				ISNULL(@BRANCH_ID_RECEIVE,''), ISNULL(@DEPT_ID_RECEIVE,''), ISNULL(@DEPT_ID_USE,''),@IS_LIQ,@NOTES,@l_BUY_PRICE,@l_REMAIN_VALUE,@EMP_ID_USE)

				--hieuhm Khi thêm từ phiếu yêu cầu thì update trạng thái cho pyc
				IF(@p_REQ_ID <> '' OR @p_REQ_ID IS NOT NULL)
				BEGIN
					UPDATE TR_REQUEST_DOC_ASSET_DT SET STATUS_REQ_DOC = '1' WHERE REQ_DOC_ID = @p_REQ_ID AND ASSET_ID = @ASSET_ID
				END
				IF @@Error <> 0 GOTO ABORT

			
			FETCH NEXT FROM XmlData INTO  @ASSET_ID,@BRANCH_ID,@LOCATION,@PURPOSE_ID,@COLLECT_NOTE,@BRANCH_ID_RECEIVE,@DEPT_ID_RECEIVE,@DEPT_ID_USE,@IS_LIQ,@NOTES
		END
			CLOSE XmlData
		DEALLOCATE XmlData
		-- GIANT Insert to table PL_PROCESS
		INSERT INTO dbo.PL_PROCESS
					(
					    REQ_ID,
					    PROCESS_ID,
					    CHECKER_ID,
					    APPROVE_DT,
					    PROCESS_DESC,
					    NOTES
					)
					VALUES
					(   @l_COL_MULTI_MASTER_ID,       
						'INSERT',
					    @p_MAKER_ID,        
					    GETDATE(), 
					    N'Thêm mới phiếu thu hồi tài sản ' ,      
					    N'Thêm mới phiếu thu hồi tài sản thành công'       
					 )
COMMIT TRANSACTION
SELECT '0' as Result, @l_COL_MULTI_MASTER_ID  COL_MULTI_MASTER_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		CLOSE XmlData
		DEALLOCATE XmlData
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, '' ErrorDesc
		RETURN '-1'
End
GO

ALTER PROCEDURE dbo.ASS_COLLECT_MULTI_MASTER_Upd
@p_COL_MULTI_MASTER_ID	varchar(15) = null ,
@p_BRANCH_ID	varchar(15) = NULL ,
@p_COLLECT_DT	VARCHAR(20) = NULL,
@p_USER_COLLECT	nvarchar(200)  = NULL,
@p_NOTES	nvarchar(1000) = NULL ,
@p_RECORD_STATUS	varchar(1) = NULL ,
@p_AUTH_STATUS	varchar(1) = NULL ,
@p_MAKER_ID	varchar(100) = NULL ,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_CHECKER_ID	varchar(100) = NULL ,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS_KT	varchar(15) = NULL ,
@p_CREATE_DT_KT	varchar(100) = NULL,
@p_APPROVE_DT_KT	VARCHAR(20) = NULL,
@p_MAKER_ID_KT	varchar(100) = NULL ,
@p_CHECKER_ID_KT	varchar(100) = NULL ,
@p_REPORT_STATUS	varchar(15) = NULL,
@p_BRANCH_CREATE varchar(15)  = NULL,
@p_REQ_ID varchar(15)  = NULL,
@p_CONTENT NVARCHAR(MAX) = NULL, --Nội dung biên bản
@p_XmlData XML = NULL
AS
DECLARE 
		@COLLECT_MULTI_ID varchar(15),
		@ASSET_ID	varchar(15),
		@BRANCH_ID varchar(15),
		@LOCATION	Nvarchar(500),
		@PURPOSE_ID	Nvarchar(500),
		@COLLECT_NOTE nvarchar(1000),
		@BRANCH_ID_RECEIVE	VARCHAR(15) = NULL,
		@DEPT_ID_RECEIVE	VARCHAR(15) = NULL,
		--PHONG BAN SU DUNG
		@DEPT_ID_USE	VARCHAR(15) = NULL,
		@IS_LIQ varchar(1)= '0',
    @NOTES NVARCHAR(MAX),
    @l_REMAIN_VALUE	DECIMAL(18,0),
    @l_BUY_PRICE	DECIMAL(18,0),
    @EMP_ID_USE VARCHAR(20)
Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@p_XmlData

	DECLARE XmlData CURSOR FOR
	SELECT *
	FROM OPENXML(@hdoc,'/Root/XmlData',2)
	WITH 
	(
		COLLECT_MULTI_ID varchar(15),
		ASSET_ID	varchar(15),
		BRANCH_ID varchar(15),
		LOCATION	Nvarchar(500),
		PURPOSE_ID	Nvarchar(500),
		COLLECT_NOTE nvarchar(1000),
		BRANCH_ID_RECEIVE	VARCHAR(15),
		DEPT_ID_RECEIVE	VARCHAR(15),
		DEPT_ID_USE	VARCHAR(15),
		IS_LIQ varchar(1),
    NOTES NVARCHAR(MAX)
	)
	OPEN XmlData

BEGIN TRANSACTION
		--hieuhm cập nhật tất cả trạng thái tài sản của phiếu thu hồi trong pyc = 0
		IF(@p_REQ_ID <> '' OR @p_REQ_ID IS NOT NULL)
		BEGIN
			UPDATE TR_REQUEST_DOC_ASSET_DT SET STATUS_REQ_DOC = '0' WHERE REQ_DOC_ID = @p_REQ_ID AND ASSET_ID IN (SELECT ASSET_ID FROM ASS_COLLECT_MULTI_DT WHERE COL_MULTI_MASTER_ID = @p_COL_MULTI_MASTER_ID)
		END

		DELETE FROM ASS_COLLECT_MULTI_DT WHERE COL_MULTI_MASTER_ID = @p_COL_MULTI_MASTER_ID

		UPDATE ASS_COLLECT_MULTI_MASTER SET [REQ_ID] = @p_REQ_ID, [BRANCH_ID] = @p_BRANCH_ID,[COLLECT_DT] = CONVERT(DATETIME, @p_COLLECT_DT, 103),[USER_COLLECT] = @p_USER_COLLECT,[NOTES] = @p_NOTES,[RECORD_STATUS] = @p_RECORD_STATUS,[AUTH_STATUS] = @p_AUTH_STATUS,[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),
		[CHECKER_ID] = @p_CHECKER_ID,[REPORT_STATUS] = @p_REPORT_STATUS, MAKER_ID_KT = NULL,AUTH_STATUS_KT ='E', CONTENT = @p_CONTENT
WHERE  COL_MULTI_MASTER_ID= @p_COL_MULTI_MASTER_ID
		IF @@Error <> 0 GOTO ABORT

		--Insert XmlData
		FETCH NEXT FROM XmlData INTO  @COLLECT_MULTI_ID,@ASSET_ID,@BRANCH_ID,@LOCATION,@PURPOSE_ID,@COLLECT_NOTE,@BRANCH_ID_RECEIVE,@DEPT_ID_RECEIVE,@DEPT_ID_USE,@IS_LIQ,@NOTES
		WHILE @@FETCH_STATUS = 0
		BEGIN
			
         IF(@BRANCH_ID_RECEIVE = 'DV0001')
         BEGIN
            IF(@DEPT_ID_RECEIVE IS NULL OR @DEPT_ID_RECEIVE = '')
            BEGIN
      					CLOSE XmlData
      					DEALLOCATE XmlData
      					ROLLBACK TRANSACTION
      					SELECT '-1' as Result, @p_COL_MULTI_MASTER_ID COL_MULTI_MASTER_ID, N'Vui lòng chọn phòng ban thu hồi' ErrorDesc
      					RETURN '-1'
            END
         END

			DECLARE @ERRORSYS NVARCHAR(200) = '',@ASSET_CODE nVARCHAR(20)
--				IF ( EXISTS ( SELECT * FROM ASS_COLLECT_MULTI_DT WHERE [ASSET_ID] = @ASSET_ID AND AUTH_STATUS<>'A' AND [CHECKER_ID_KT] <> 'system'))
--				  BEGIN
--					 SET @ASSET_CODE=(SELECT ASSET_CODE FROM ASS_MASTER WHERE  [ASSET_ID] = @ASSET_ID)
--					 SET @ERRORSYS = N'Mã: '+@ASSET_CODE+N' đang được thu hồi'
--					CLOSE XmlData
--					DEALLOCATE XmlData
--					ROLLBACK TRANSACTION
--					SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, @ERRORSYS ErrorDesc
--					RETURN '-1'
--				  END
--				  IF ( EXISTS ( SELECT * FROM ASS_COLLECT WHERE [ASSET_ID] = @ASSET_ID AND AUTH_STATUS<>'A'))
--				  BEGIN
--					 SET @ASSET_CODE=(SELECT ASSET_CODE FROM ASS_MASTER WHERE  [ASSET_ID] = @ASSET_ID)
--					 SET @ERRORSYS = N'Mã: '+@ASSET_CODE+N' đang được thu hồi'
--					CLOSE XmlData
--					DEALLOCATE XmlData
--					ROLLBACK TRANSACTION
--					SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, @ERRORSYS ErrorDesc
--					RETURN '-1'
--				
--				  END
			
			IF(LEN(@COLLECT_MULTI_ID)  = 0)
			BEGIN
				EXEC SYS_CodeMasters_Gen 'ASS_COLLECT_MULTI_DT', @COLLECT_MULTI_ID out
				IF @COLLECT_MULTI_ID='' OR @COLLECT_MULTI_ID IS NULL GOTO ABORT
			END
			--LUCTV: 27-12-2018 BO SUNG LAY DON VI SU DUNG, DON VI TAO TS TAI THOI DIEM THU HOI
				SET @p_BRANCH_ID = (SELECT BRANCH_ID FROM ASS_MASTER WHERE ASSET_ID =@ASSET_ID)
				SET @p_BRANCH_CREATE =(SELECT BRANCH_CREATE FROM ASS_MASTER WHERE ASSET_ID =@ASSET_ID)
				SET @DEPT_ID_USE = (SELECT DEPT_ID FROM ASS_MASTER WHERE ASSET_ID =@ASSET_ID)
				--END

        --PHUCVH 27/02/23 THÊM NGUYÊN GIÁ VÀ GIÁ TRỊ CÒN LẠI TẠI THỜI ĐIỂM TẠO PHIẾU
        SELECT @l_BUY_PRICE = A.BUY_PRICE, @l_REMAIN_VALUE = ISNULL((A.AMORT_AMT - A.AMORTIZED_AMT), ISNULL(A.AMORT_AMT,0)), @EMP_ID_USE = A.EMP_ID
        FROM ASS_MASTER A
        WHERE A.ASSET_ID = @ASSET_ID

			INSERT INTO ASS_COLLECT_MULTI_DT([COLLECT_MULTI_ID],[COL_MULTI_MASTER_ID],[ASSET_ID],[BRANCH_ID],[PURPOSE_ID],[COLLECT_DT],[LOCATION],[COLLECT_NOTE],[RECORD_STATUS],
			[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[BRANCH_CREATE],[AUTH_STATUS_KT],[CREATE_DT_KT],[APPROVE_DT_KT],[MAKER_ID_KT],[CHECKER_ID_KT],[REPORT_STATUS]
			,[BRANCH_ID_RECEIVE], [DEPT_ID_RECEIVE], [DEPT_ID_USE],[IS_LIQ],NOTES,BUY_PRICE,REMAIN_VALUE,EMP_ID_USE)
			VALUES(@COLLECT_MULTI_ID ,@p_COL_MULTI_MASTER_ID ,@ASSET_ID ,ISNULL(@p_BRANCH_ID,'') ,@PURPOSE_ID ,CONVERT(DATETIME, @p_COLLECT_DT, 103) ,@LOCATION ,@COLLECT_NOTE ,@p_RECORD_STATUS ,
			@p_AUTH_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103) ,@p_BRANCH_CREATE,
			@p_AUTH_STATUS_KT ,CONVERT(DATETIME, @p_CREATE_DT_KT, 103) ,CONVERT(DATETIME, @p_APPROVE_DT_KT, 103) ,@p_MAKER_ID_KT ,@p_CHECKER_ID_KT ,'N' ,ISNULL(@BRANCH_ID_RECEIVE,''), ISNULL(@DEPT_ID_RECEIVE,''), ISNULL(@DEPT_ID_USE,''),@IS_LIQ,@NOTES
      ,@l_BUY_PRICE,@l_REMAIN_VALUE,@EMP_ID_USE)
			
			--hieuhm Khi thêm từ phiếu yêu cầu thì update trạng thái cho pyc
				IF(@p_REQ_ID <> '' OR @p_REQ_ID IS NOT NULL)
				BEGIN
					UPDATE TR_REQUEST_DOC_ASSET_DT SET STATUS_REQ_DOC = '1' WHERE REQ_DOC_ID = @p_REQ_ID AND ASSET_ID = @ASSET_ID
				END
			IF @@Error <> 0 GOTO ABORT

			FETCH NEXT FROM XmlData INTO  @COLLECT_MULTI_ID,@ASSET_ID,@BRANCH_ID,@LOCATION,@PURPOSE_ID,@COLLECT_NOTE,@BRANCH_ID_RECEIVE,@DEPT_ID_RECEIVE,@DEPT_ID_USE,@IS_LIQ,@NOTES
		END
			CLOSE XmlData
		DEALLOCATE XmlData

--		-- HUYHT 06/05/2022: XÓA CÁC PROCESS UPDATE CŨ
--		DELETE dbo.PL_PROCESS WHERE REQ_ID = @p_COL_MULTI_MASTER_ID AND PROCESS_ID = 'UPDATE'
		-- GIANT Insert to table PL_PROCESS
		INSERT INTO dbo.PL_PROCESS
		(
			REQ_ID,
			PROCESS_ID,
			CHECKER_ID,
			APPROVE_DT,
			PROCESS_DESC,
			NOTES
		)
		VALUES
		(   @p_COL_MULTI_MASTER_ID,       
			'UPDATE',
			@p_MAKER_ID,        
			GETDATE(), 
			N'Cập nhật phiếu thu hồi tài sản ' ,      
			N'Cập nhật phiếu thu hồi tài sản thành công'       
			)

COMMIT TRANSACTION
		SELECT '0' as Result, @p_COL_MULTI_MASTER_ID  COL_MULTI_MASTER_ID, '' ErrorDesc
		RETURN '0'
ABORT:
BEGIN
		CLOSE XmlData
		DEALLOCATE XmlData
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, '' ErrorDesc
		RETURN '-1'
End
GO
ALTER PROCEDURE dbo.ASS_COLLECT_MULTI_MASTER_KT_Upd
@p_COL_MULTI_MASTER_ID	varchar(15) = null ,
@p_BRANCH_ID	varchar(15) = NULL ,
@p_COLLECT_DT	VARCHAR(20) = NULL,
@p_USER_COLLECT	nvarchar(200)  = NULL,
@p_NOTES	nvarchar(1000) = NULL ,
@p_RECORD_STATUS	varchar(1) = NULL ,
@p_AUTH_STATUS	varchar(1) = NULL ,
@p_MAKER_ID	varchar(100) = NULL ,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_CHECKER_ID	varchar(100) = NULL ,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS_KT	varchar(15) = NULL ,
@p_CREATE_DT_KT	varchar(100) = NULL,
@p_APPROVE_DT_KT	VARCHAR(20) = NULL,
@p_MAKER_ID_KT	varchar(100) = NULL ,
@p_CHECKER_ID_KT	varchar(100) = NULL ,
@p_REPORT_STATUS	varchar(15) = NULL,
@p_BRANCH_CREATE varchar(15)  = NULL,
@p_XmlData XML = NULL,
@p_CORE_NOTE VARCHAR(1000) = NULL
AS
DECLARE 
		@COLLECT_MULTI_ID varchar(15),
		@ASSET_ID	varchar(15),
		@BRANCH_ID varchar(15),
		@LOCATION	Nvarchar(500),
		@PURPOSE_ID	Nvarchar(500),
		@COLLECT_NOTE nvarchar(1000),
		@BRANCH_ID_RECEIVE	VARCHAR(15) = NULL,
		@DEPT_ID_RECEIVE	VARCHAR(15) = NULL,
		--PHONG BAN SU DUNG
		@DEPT_ID_USE	VARCHAR(15) = NULL,
		@ASS_AMORTIZED_MONTH	DECIMAL,
		@ASS_AMORTIZED_AMT	DECIMAL,
		@IS_LIQ varchar(1)= '0',
    @l_REMAIN_VALUE	DECIMAL(18,0),
    @l_BUY_PRICE	DECIMAL(18,0),
    @EMP_ID_USE VARCHAR(15)
Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@p_XmlData

	DECLARE XmlData CURSOR FOR
	SELECT *
	FROM OPENXML(@hdoc,'/Root/XmlData',2)
	WITH 
	(
		COLLECT_MULTI_ID varchar(15),
		ASSET_ID	varchar(15),
		BRANCH_ID varchar(15),
		LOCATION	Nvarchar(500),
		PURPOSE_ID	Nvarchar(500),
		COLLECT_NOTE nvarchar(1000),
		BRANCH_ID_RECEIVE	VARCHAR(15),
		DEPT_ID_RECEIVE	VARCHAR(15),
		DEPT_ID_USE	VARCHAR(15),
		IS_LIQ varchar(1),
		ASS_AMORTIZED_MONTH	DECIMAL,
		ASS_AMORTIZED_AMT DECIMAL
	)
	OPEN XmlData
	DELETE FROM ASS_COLLECT_MULTI_DT WHERE COL_MULTI_MASTER_ID = @p_COL_MULTI_MASTER_ID

BEGIN TRANSACTION

----BO 2 KY TU DAC BIET & VA < TRONG DIEN GIAI HACH TOAN - THIEUVQ 020719 - BEGIN
SET @p_CORE_NOTE = REPLACE(@p_CORE_NOTE,'&', 'VA')
SET @p_CORE_NOTE = REPLACE(@p_CORE_NOTE,'<', ' ')
--------------END---------------
		UPDATE ASS_COLLECT_MULTI_MASTER SET [BRANCH_ID] = @p_BRANCH_ID,[COLLECT_DT] = CONVERT(DATETIME, @p_COLLECT_DT, 103),[USER_COLLECT] = @p_USER_COLLECT,[NOTES] = @p_NOTES,[RECORD_STATUS] = @p_RECORD_STATUS,
		[AUTH_STATUS_KT] = 'E',[CREATE_DT_KT] = CONVERT(DATETIME,@p_CREATE_DT_KT, 103),[APPROVE_DT_KT] = CONVERT(DATETIME, @p_APPROVE_DT_KT, 103),
		[CHECKER_ID_KT] = @p_CHECKER_ID_KT,[REPORT_STATUS] = @p_REPORT_STATUS,CORE_NOTE = @p_CORE_NOTE,
    MAKER_ID_KT = @p_MAKER_ID_KT
WHERE  COL_MULTI_MASTER_ID= @p_COL_MULTI_MASTER_ID
		IF @@Error <> 0 GOTO ABORT
		--Insert XmlData
		FETCH NEXT FROM XmlData INTO  @COLLECT_MULTI_ID,@ASSET_ID,@BRANCH_ID,@LOCATION,@PURPOSE_ID,@COLLECT_NOTE,@BRANCH_ID_RECEIVE,@DEPT_ID_RECEIVE,@DEPT_ID_USE,@IS_LIQ,@ASS_AMORTIZED_MONTH,@ASS_AMORTIZED_AMT
		WHILE @@FETCH_STATUS = 0
		BEGIN
			
			DECLARE @ERRORSYS NVARCHAR(200) = '',@ASSET_CODE nVARCHAR(20)
--				IF ( EXISTS ( SELECT * FROM ASS_COLLECT_MULTI_DT WHERE [ASSET_ID] = @ASSET_ID AND AUTH_STATUS<>'A' AND [CHECKER_ID_KT] <> 'system'))
--				  BEGIN
--					 SET @ASSET_CODE=(SELECT ASSET_CODE FROM ASS_MASTER WHERE  [ASSET_ID] = @ASSET_ID)
--					 SET @ERRORSYS = N'Mã: '+@ASSET_CODE+N' đang được thu hồi'
--					CLOSE XmlData
--					DEALLOCATE XmlData
--					ROLLBACK TRANSACTION
--					SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, @ERRORSYS ErrorDesc
--					RETURN '-1'
--				  END
--				  IF ( EXISTS ( SELECT * FROM ASS_COLLECT WHERE [ASSET_ID] = @ASSET_ID AND AUTH_STATUS<>'A'))
--				  BEGIN
--					 SET @ASSET_CODE=(SELECT ASSET_CODE FROM ASS_MASTER WHERE  [ASSET_ID] = @ASSET_ID)
--					 SET @ERRORSYS = N'Mã: '+@ASSET_CODE+N' đang được thu hồi'
--					CLOSE XmlData
--					DEALLOCATE XmlData
--					ROLLBACK TRANSACTION
--					SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, @ERRORSYS ErrorDesc
--					RETURN '-1'
--				
--				  END
			
			IF(LEN(@COLLECT_MULTI_ID)  = 0)
			BEGIN
				EXEC SYS_CodeMasters_Gen 'ASS_COLLECT_MULTI_DT', @COLLECT_MULTI_ID out
				IF @COLLECT_MULTI_ID='' OR @COLLECT_MULTI_ID IS NULL GOTO ABORT
			END
			--LUCTV: 27-12-2018 BO SUNG LAY DON VI SU DUNG, DON VI TAO TS TAI THOI DIEM THU HOI
				SET @p_BRANCH_ID = (SELECT BRANCH_ID FROM ASS_MASTER WHERE ASSET_ID =@ASSET_ID)
				SET @p_BRANCH_CREATE =(SELECT BRANCH_CREATE FROM ASS_MASTER WHERE ASSET_ID =@ASSET_ID)
				SET @DEPT_ID_USE = (SELECT DEPT_ID FROM ASS_MASTER WHERE ASSET_ID =@ASSET_ID)
				--END

        --PHUCVH 27/02/23 THÊM NGUYÊN GIÁ VÀ GIÁ TRỊ CÒN LẠI TẠI THỜI ĐIỂM TẠO PHIẾU
        SELECT @l_BUY_PRICE = A.BUY_PRICE, @l_REMAIN_VALUE = ISNULL((A.AMORT_AMT - A.AMORTIZED_AMT), ISNULL(A.AMORT_AMT,0)),@EMP_ID_USE = A.EMP_ID
        FROM ASS_MASTER A
        WHERE A.ASSET_ID = @ASSET_ID

			INSERT INTO ASS_COLLECT_MULTI_DT([COLLECT_MULTI_ID],[COL_MULTI_MASTER_ID],[ASSET_ID],[BRANCH_ID],[PURPOSE_ID],[COLLECT_DT],[LOCATION],[COLLECT_NOTE],[RECORD_STATUS],
			[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[BRANCH_CREATE],[AUTH_STATUS_KT],[CREATE_DT_KT],[APPROVE_DT_KT],[MAKER_ID_KT],[CHECKER_ID_KT],[REPORT_STATUS]
			,[BRANCH_ID_RECEIVE], [DEPT_ID_RECEIVE], [DEPT_ID_USE],[IS_LIQ], ASS_AMORTIZED_MONTH, ASS_AMORTIZED_AMT,BUY_PRICE,REMAIN_VALUE,EMP_ID_USE)
			VALUES(@COLLECT_MULTI_ID ,@p_COL_MULTI_MASTER_ID ,@ASSET_ID ,@p_BRANCH_ID ,@PURPOSE_ID ,CONVERT(DATETIME, @p_COLLECT_DT, 103) ,@LOCATION ,@COLLECT_NOTE ,@p_RECORD_STATUS ,
			@p_AUTH_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103) ,@p_BRANCH_CREATE,
			@p_AUTH_STATUS_KT ,CONVERT(DATETIME, @p_CREATE_DT_KT, 103) ,CONVERT(DATETIME, @p_APPROVE_DT_KT, 103) ,@p_MAKER_ID_KT ,@p_CHECKER_ID_KT ,'N' ,@BRANCH_ID_RECEIVE, @DEPT_ID_RECEIVE, @DEPT_ID_USE,@IS_LIQ,@ASS_AMORTIZED_MONTH,@ASS_AMORTIZED_AMT,
      @l_BUY_PRICE,@l_REMAIN_VALUE,@EMP_ID_USE)
			IF @@Error <> 0 GOTO ABORT

			FETCH NEXT FROM XmlData INTO  @COLLECT_MULTI_ID,@ASSET_ID,@BRANCH_ID,@LOCATION,@PURPOSE_ID,@COLLECT_NOTE,@BRANCH_ID_RECEIVE,@DEPT_ID_RECEIVE,@DEPT_ID_USE,@IS_LIQ,@ASS_AMORTIZED_MONTH,@ASS_AMORTIZED_AMT
		END
			CLOSE XmlData
		DEALLOCATE XmlData

		-- HUYHT 06/05/2022: XÓA CÁC PROCESS UPDATE CŨ
		DELETE dbo.PL_PROCESS WHERE REQ_ID = @p_COL_MULTI_MASTER_ID AND PROCESS_ID = 'UPDATE'

		-- GIANT Insert to table PL_PROCESS
		INSERT INTO dbo.PL_PROCESS
		(
			REQ_ID,
			PROCESS_ID,
			CHECKER_ID,
			APPROVE_DT,
			PROCESS_DESC,
			NOTES
		)
		VALUES
		(   @p_COL_MULTI_MASTER_ID,       
			'UPDATE',
			@p_MAKER_ID_KT,        
			GETDATE(), 
			N'Giao dịch viên cập nhật hạch toán' ,      
			N'Giao dịch viên cập nhật hạch toán thành công'       
			)

COMMIT TRANSACTION
		SELECT '0' as Result, @p_COL_MULTI_MASTER_ID  COL_MULTI_MASTER_ID, '' ErrorDesc
		RETURN '0'
ABORT:
BEGIN
		CLOSE XmlData
		DEALLOCATE XmlData
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, '' ErrorDesc
		RETURN '-1'
End
GO

ALTER PROC dbo.ASS_COL_MULTI_HANDOVER_RECORD
@COL_MULTI_MASTER_ID VARCHAR(15) = NULL
AS
BEGIN
--Table 0 Nội dung && BRANCH
SELECT TOP 1 B.BRANCH_NAME  + ISNULL(' - ' + C.DEP_NAME,'') AS BRANCH_NAME,
D.CONTENT AS TITLE
,(N'Hôm nay, ngày ' + CONVERT(VARCHAR(5),DAY(GETDATE())) + N' tháng ' + CONVERT(VARCHAR(5),MONTH(GETDATE())) + N' năm ' + CONVERT(VARCHAR(5),YEAR(GETDATE())) + N' tại ') AS DD_MM_YY
FROM ASS_COLLECT_MULTI_DT A
LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
LEFT JOIN CM_DEPARTMENT C ON A.DEPT_ID_USE = C.DEP_ID
LEFT JOIN ASS_COLLECT_MULTI_MASTER D ON A.COL_MULTI_MASTER_ID = D.COL_MULTI_MASTER_ID
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
--Table 1 Bên giao
--Fix tạm UAT
IF(EXISTS(SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME
          FROM  ASS_COLLECT_CONFIRM_MASTER A
          LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
          LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
          WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID))
BEGIN
--    IF(EXISTS(SELECT 1 FROM ASS_COLLECT_MULTI_MASTER acmm WHERE acmm.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID AND acmm.REQ_ID IS NOT NULL AND acmm.REQ_ID <> ''))
--    BEGIN
--        SELECT TOP 1  C.TLFullName, C.ADDRESS, C.PHONE, D.POS_NAME, ISNULL(E.BRANCH_NAME,'') + ISNULL(' - ' + G.DEP_NAME,'') AS BRANCH_NAME
--        FROM ASS_COLLECT_MULTI_MASTER A
--        LEFT JOIN TR_REQUEST_SHOP_DOC B ON A.REQ_ID = B.REQ_ID
--        LEFT JOIN TL_USER C ON B.MAKER_ID = C.TLNANME
--        LEFT JOIN CM_EMPLOYEE_LOG D ON B.MAKER_ID = D.USER_DOMAIN
--        LEFT JOIN CM_BRANCH E ON C.TLSUBBRID = E.BRANCH_ID
--        LEFT JOIN CM_DEPARTMENT G ON C.DEP_ID = G.DEP_ID
--        WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
--    END
--    ELSE
--    BEGIN
        SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(E.BRANCH_NAME,'') + ISNULL(' - ' + G.DEP_NAME,'') AS BRANCH_NAME
        FROM  ASS_COLLECT_CONFIRM_MASTER A
        LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
        LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
        LEFT JOIN CM_BRANCH E ON B.TLSUBBRID = E.BRANCH_ID
        LEFT JOIN CM_DEPARTMENT G ON B.DEP_ID = G.DEP_ID
        WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
--    END

END
ELSE
BEGIN
SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME
END


--Table 2 Bên nhận
SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME
FROM  ASS_COLLECT_MULTI_MASTER A
LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID



--TABLE 3 DANH SÁCH TÀI SẢN
SELECT CASE 
       	WHEN B.ASSET_CODE IS NOT NULL AND B.ASSET_CODE <> '' THEN B.ASSET_CODE
       	ELSE B.ASS_CODE_TMP
       END AS ASSET_CODE, B.ASSET_NAME, D.UNIT_NAME, 1 AS QUANTITY, E.STATUS_NAME, B.ASSET_SERIAL_NO AS SERIAL, REQ.REQ_CODE + ISNULL(CHAR(10) + CHAR(10) + B.PL,'') AS REQ_CODE,B.NOTES AS QUY_CACH, A.NOTES,
       --ROW_NUMBER() OVER (PARTITION BY B.ASSET_NAME ORDER BY B.ASSET_NAME) AS STT,
       ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT,
       CA.CONTENT AS COLLECT_REASON,
       CE.EMP_NAME AS EMP_USE
FROM ASS_COLLECT_MULTI_DT A
LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
LEFT JOIN ASS_GROUP C ON B.GROUP_ID = C.GROUP_ID
LEFT JOIN CM_UNIT D ON C.UNIT = D.UNIT_ID 
LEFT JOIN ASS_STATUS E ON B.ASS_STATUS = E.STATUS_ID
LEFT JOIN ASS_COLLECT_MULTI_MASTER  F ON A.COL_MULTI_MASTER_ID = F.COL_MULTI_MASTER_ID
LEFT JOIN TR_REQUEST_SHOP_DOC REQ ON F.REQ_ID = REQ.REQ_ID
LEFT JOIN CM_ALLCODE CA ON A.PURPOSE_ID = CA.CDVAL AND CA.CDNAME = 'REASON_ASS_REQ' AND CA.CDTYPE = 'REQ_ASSET'
LEFT JOIN CM_EMPLOYEE CE ON A.EMP_ID_USE = CE.EMP_ID
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID

--TABLE 4 CHỮ KÝ
--SELECT ''
SELECT TOP 1 E.TLFullName AS MAKER_NAME, --BÊN GIAO
ISNULL(C.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 
                                                                  WHERE PP.REQ_ID = A.COL_MULTI_MASTER_ID AND PP.PROCESS_ID = 'CONFIRM'
                                                                  AND PP.CHECKER_ID = B.MAKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME,--BÊN GIAO
D.TLFullName AS CHECKER_NAME, --BÊN NHẬN
--D.TLFullName AS INTERMEDIATE_UNIT_NAME, --ĐƠN VỊ TRUNG GIAN
--F.POS_NAME AS POS_NAME_1, --ĐƠN VỊ TRUNG GIAN
ISNULL(F.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 
                                                                  WHERE PP.REQ_ID = A.COL_MULTI_MASTER_ID AND PP.PROCESS_ID = 'APPROVE'
                                                                  AND PP.CHECKER_ID = A.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME_2  --BÊN NHẬN 
FROM ASS_COLLECT_MULTI_MASTER A
LEFT JOIN ASS_COLLECT_CONFIRM_MASTER B ON A.COL_MULTI_MASTER_ID = B.COL_MULTI_MASTER_ID
LEFT JOIN TL_USER E ON B.MAKER_ID = E.TLNANME --BÊN GIAO
LEFT JOIN CM_EMPLOYEE_LOG C ON B.MAKER_ID = C.USER_DOMAIN --BÊN GIAO
LEFT JOIN TL_USER D ON A.CHECKER_ID = D.TLNANME --BÊN NHẬN + ĐƠN VỊ TRUNG GIAN
LEFT JOIN CM_EMPLOYEE_LOG F ON A.CHECKER_ID = F.USER_DOMAIN --BÊN NHẬN + ĐƠN VỊ TRUNG GIAN
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID

--TABLE 5 SỐ
SELECT @COL_MULTI_MASTER_ID AS NO

--TABLE 6 ĐƠN VỊ TRUNG GIAN
SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME
--SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME,ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME
--FROM  ASS_COLLECT_MULTI_MASTER A
--LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
--LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
--LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
--LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID
--WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID

END
GO

UPDATE A SET A.EMP_ID_USE = B.EMP_ID
FROM ASS_COLLECT_MULTI_DT A
LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID