ALTER PROCEDURE dbo.ASS_LIQ_REQUEST_Upd
@p_LIQ_REQ_ID VARCHAR(15) = NULL,
@p_ASS_TYPE_ID	varchar(15)  = NULL,
@p_REQ_DT	VARCHAR(20) = NULL,
@p_BRANCH_ID	varchar(15)  = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(12)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_LiqDetails XML = NULL,
@p_ListCostCenter XML = NULL,
@p_BRANCH_CREATE	VARCHAR(15) = NULL,
@p_DEP_ID	VARCHAR(15) = NULL,
@p_TERM_ID VARCHAR(15) = NULL,
@p_USER_LOGIN VARCHAR(15)

AS
--Validation is here
	DECLARE @ERRORSYS NVARCHAR(15) = '' 
	IF ( NOT EXISTS ( SELECT * FROM ASS_LIQ_REQUEST WHERE  LIQ_REQ_ID= @p_LIQ_REQ_ID))
		SET @ERRORSYS = ''
	IF @ERRORSYS <> '' 
	BEGIN
		SELECT ErrorCode Result, ''  LIQ_REQ_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		RETURN '0'
	END 
	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@p_LiqDetails

	--Them dieu kien kiem tra lý do de xuat khong duoc de trong
	DECLARE @reason NVARCHAR(max) = ''
	SELECT @reason = COALESCE(@reason + ', ', '') + ASSET_CODE 
	FROM OPENXML(@hdoc,'/Root/LiquiDetail',2)
	WITH 
	(
		ASSET_ID	varchar(15),
		LIQ_REASON	nvarchar(1000),
		NOTES	nvarchar(1000)	,
		REQ_AMT DECIMAL(18,0),
		LIQ_RECEIVE NVARCHAR(500),
		BRANCH_USE VARCHAR(15),
		DEPT_USE VARCHAR(15),
		REMAIN_VALUE DECIMAL(18,0),
		ASSET_CODE varchar(100),
		IS_IMP varchar(15)
	)
	WHERE LIQ_REASON = '' OR LIQ_REASON IS NULL

	IF @reason <> ''
	BEGIN
		SELECT '-1' as Result, '' LIQ_REQ_ID, N'Lý do đề xuất thanh lý không được để trống! DS tài sản: '+ @reason ErrorDesc
		RETURN '-1'
	END

	-------BO SUNG PHAN IMPORT TS TU FILE
	----KIEM TRA TS IMPORT PHAI THUOC DON VI DA CHON
	declare @tmp table(BRANCH_ID varchar(15))
	insert into @tmp  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)

	DECLARE liqDetail CURSOR FOR
	SELECT B.DETAIL_ID,
	--A.ASSET_ID,
	B.ASSET_ID,
		B.LIQ_REASON,
		B.NOTES,
		B.REQ_AMT,
		B.LIQ_RECEIVE,
		--khanhnhd
		--(CASE WHEN IS_IMP = 'N' THEN B.BRANCH_USE ELSE B.ASSET_ID END) BRANCH_USE,
		--(CASE WHEN IS_IMP = 'N' THEN B.DEPT_USE ELSE B.ASSET_ID END) DEPT_USE,		
		B.BRANCH_USE,
		B.DEPT_USE,
		--end khanhnhd
		B.REMAIN_VALUE
	FROM OPENXML(@hdoc,'/Root/LiquiDetail',2)
	WITH 
	(
		DETAIL_ID VARCHAR(15),
		ASSET_ID	varchar(15),
		LIQ_REASON	nvarchar(1000),
		NOTES	nvarchar(1000)	,
		REQ_AMT DECIMAL(18,0),
		LIQ_RECEIVE NVARCHAR(500),
		BRANCH_USE VARCHAR(15),
		DEPT_USE VARCHAR(15),
		REMAIN_VALUE DECIMAL(18,0),
		ASSET_CODE varchar(100),
			IS_IMP varchar(15)
	) B
	--LAY MA TAI SAN
	--LEFT JOIN ASS_MASTER A ON A.ASSET_CODE = B.ASSET_CODE AND A.BRANCH_ID IN (SELECT C.* FROM @tmp C)
	OPEN liqDetail
	
BEGIN TRANSACTION

		IF @p_APPROVE_DT = '' SET @p_APPROVE_DT = NULL
    SET @p_MAKER_ID = (SELECT alr.MAKER_ID FROM ASS_LIQ_REQUEST alr WHERE LIQ_REQ_ID = @p_LIQ_REQ_ID)
		UPDATE ASS_LIQ_REQUEST SET [ASS_TYPE_ID] = @p_ASS_TYPE_ID,[REQ_DT] = CONVERT(DATETIME, @p_REQ_DT, 103),[BRANCH_ID] = @p_BRANCH_ID,[NOTES] = @p_NOTES,[RECORD_STATUS] = @p_RECORD_STATUS,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[AUTH_STATUS] = @p_AUTH_STATUS,[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103),
		BRANCH_CREATE= @p_BRANCH_CREATE, TERM_ID = @p_TERM_ID,DEP_ID=@p_DEP_ID
		WHERE  LIQ_REQ_ID= @p_LIQ_REQ_ID
		IF @@Error <> 0 GOTO ABORT 
		PRINT 'A'
		DELETE FROM ASS_LIQ_REQUEST_DT WHERE LIQ_REQ_ID = @p_LIQ_REQ_ID
		PRINT 'B'
		Declare @DETAIL_ID  VARCHAR(15), @ASSET_ID VARCHAR(15), @LIQ_REASON NVARCHAR(1000),@NOTES NVARCHAR(1000), 
		@REQ_AMT DECIMAL(18,0), @LIQ_RECEIVE NVARCHAR(500), @BRANCH_USE VARCHAR(15), @DEPT_USE VARCHAR(15), @REMAIN_VALUE DECIMAL(18,0)
		FETCH NEXT FROM liqDetail INTO @DETAIL_ID,@ASSET_ID, @LIQ_REASON, @NOTES, @REQ_AMT, @LIQ_RECEIVE,@BRANCH_USE, @DEPT_USE, @REMAIN_VALUE
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			IF LEN (@DETAIL_ID) =  0
			BEGIN
				PRINT 'C'
				EXEC SYS_CodeMasters_Gen 'ASS_LIQ_REQUEST_DT', @DETAIL_ID out
				IF @DETAIL_ID='' OR @DETAIL_ID IS NULL GOTO ABORT
			END
			PRINT @DETAIL_ID
			--print 'asset_id: ' + @ASSET_ID
			INSERT INTO ASS_LIQ_REQUEST_DT([LIQREQDT_ID],[LIQ_REQ_ID],[ASSET_ID],[LIQ_REASON],[NOTES],[RECORD_STATUS],
			[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT], [REQ_AMT], [LIQ_RECEIVE], [BRANCH_USE], [DEPT_USE], [REMAIN_VALUE])
			VALUES(@DETAIL_ID ,@p_LIQ_REQ_ID ,@ASSET_ID ,@LIQ_REASON ,@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,
			CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103), @REQ_AMT, @LIQ_RECEIVE,
			@BRANCH_USE, @DEPT_USE, @REMAIN_VALUE )
			IF @@Error <> 0 GOTO ABORT
			FETCH NEXT FROM liqDetail INTO @DETAIL_ID,@ASSET_ID, @LIQ_REASON, @NOTES, @REQ_AMT, @LIQ_RECEIVE,@BRANCH_USE, @DEPT_USE, @REMAIN_VALUE
		END
		CLOSE liqDetail
		DEALLOCATE liqDetail

		--khanhnhd cập nhật đơn vị chuyên môn
	Declare @hdocc INT
	Exec sp_xml_preparedocument @hdocc Output,@p_ListCostCenter

	DECLARE ConcenterDetaisls CURSOR FOR
	SELECT D.REQ_COST_ID,
		D.COST_ID,
		D.REQ_ID,
    D.ASS_STATUS,
    D.RE_CONTENT,
		D.NOTES,
		D.AUTH_STATUS
	FROM OPENXML(@hdocc,'/Root/ListCostCenter',2)
	WITH 
	(
		REQ_COST_ID	varchar(15),
		COST_ID	nvarchar(1000),
		REQ_ID	nvarchar(1000),
    ASS_STATUS NVARCHAR(100),
    RE_CONTENT NVARCHAR(100),
		NOTES nvarchar(500),
		AUTH_STATUS NVARCHAR(500)
	)D
	OPEN ConcenterDetaisls
	Declare @REQ_COST_ID VARCHAR(15), @COST_ID NVARCHAR(1000), @REQ_ID NVARCHAR(1000), @ASS_STATUS NVARCHAR(100), @RE_CONTENT NVARCHAR(100), @AUTH_STATUS NVARCHAR(500)
		
		DELETE FROM TR_REQUEST_SHOP_COSTCENTER WHERE REQ_ID =@p_LIQ_REQ_ID 

		FETCH NEXT FROM ConcenterDetaisls INTO @REQ_COST_ID, @COST_ID, @REQ_ID, @ASS_STATUS, @RE_CONTENT, @NOTES, @AUTH_STATUS
		WHILE @@FETCH_STATUS = 0	
		BEGIN
		  DECLARE @l_REQ_COST_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'TR_REQUEST_SHOP_COSTCENTER', @l_REQ_COST_ID out
			IF @l_REQ_COST_ID='' OR @l_REQ_COST_ID IS NULL GOTO ABORT
      IF((EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('DVCM',@p_BRANCH_ID,@p_DEP_ID) US WHERE US.TLNANME = @p_USER_LOGIN)) AND @RE_CONTENT <> '')-- OR A.MAKER_ID = @p_USER_LOGIN)
      BEGIN
        ROLLBACK TRANSACTION
      	SELECT '-1' as Result, '' LIQ_REQ_ID, N'Nội dung đề xuất không được để trống! DS DVCM: '+ @RE_CONTENT ErrorDesc
      	RETURN '-1'
      END
      
			INSERT INTO TR_REQUEST_SHOP_COSTCENTER([REQ_COST_ID],[COST_ID],[ASS_STATUS],[RE_CONTENT],[REQ_ID],[NOTES],[AUTH_STATUS])		
			VALUES(@l_REQ_COST_ID ,@COST_ID, @ASS_STATUS, @RE_CONTENT, @p_LIQ_REQ_ID, @NOTES, @AUTH_STATUS)
				IF @@Error <> 0 GOTO ABORT
					FETCH NEXT FROM ConcenterDetaisls INTO @REQ_COST_ID, @COST_ID, @REQ_ID, @ASS_STATUS, @RE_CONTENT, @NOTES, @AUTH_STATUS

	  END
	CLOSE ConcenterDetaisls
	DEALLOCATE ConcenterDetaisls
	--end khanhnhd
		--INSERT DETAIL 
COMMIT TRANSACTION
SELECT '0' as Result, @p_LIQ_REQ_ID  LIQ_REQ_ID,  @l_REQ_COST_ID  REQ_COST_ID,'' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' LIQ_REQ_ID, '' ErrorDesc
		RETURN '-1'
End