AMS - VB

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