AMS - VB

ALTER PROCEDURE dbo.ASS_LIQUIDATION_BVB_Import

	@p_BRANCH_ID		varchar(15) = NULL,

	@p_BRANCH_LOGIN		varchar(15) = NULL,

	@p_LEVEL			varchar(10) = NULL,

	@p_XML XML

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

BEGIN -- PAGING

	declare @tmp table(BRANCH_ID varchar(15))

	insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)

	declare @tmpAssetCode table(

		STT				INT,

		ASSET_CODE varchar(50),

		LIQ_AMT	decimal(18,0),

		LIQ_REASON NVARCHAR(1000),

		LIQ_TYPE	nvarchar(max)

	)

	Declare @hdoc INT

	Exec sp_xml_preparedocument @hdoc Output,  @p_XML

	insert into @tmpAssetCode

--	select ROW_NUMBER() OVER(ORDER BY (SELECT 1)), ASSET_CODE,LIQ_AMT,LIQ_REASON, CASE

--                                                                                  	WHEN LIQ_TYPE LIKE N'%Toàn%' THEN '1'

--                                                                                  	ELSE '0'

--                                                                                  END LIQ_TYPE

    select ROW_NUMBER() OVER(ORDER BY (SELECT 1)), ASSET_CODE,LIQ_AMT,LIQ_REASON,LIQ_TYPE

	from OPENXML(@hdoc,'/Root/LiquiDetail',2)

	WITH 

	(

		STT				INT,

		ASSET_CODE	varchar(50),

		LIQ_AMT	decimal(18,0),

		LIQ_REASON NVARCHAR(1000),

		LIQ_TYPE	nvarchar(100)

	)

	-- validate begin

	DECLARE @ERROR_MESSAGE NVARCHAR(MAX)

    --valide dup code

    select @ERROR_MESSAGE = STUFF( (

	--select ',' + ASSET_CODE from @tmpAssetCode T 

    SELECT ASSET_CODE, COUNT(ASSET_CODE)

    FROM @tmpAssetCode

    GROUP BY ASSET_CODE

    HAVING COUNT(ASSET_CODE) > 1

	FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)

	BEGIN

		set @ERROR_MESSAGE = N'Mã tài sản trong file trùng dữ liệu: ' + @ERROR_MESSAGE

		exec sp_seterrormessage @ERROR_MESSAGE

		RETURN '-1'

	END

    ------- end dup -----

	select @ERROR_MESSAGE = STUFF( (

	select ',' + ASSET_CODE from @tmpAssetCode T 

	WHERE T.ASSET_CODE IS NOT NULL AND NOT EXISTS(

		SELECT ASSET_CODE 

		FROM ASS_MASTER A 	

		WHERE A.ASSET_CODE = T.ASSET_CODE and A.RECORD_STATUS = '1'

		AND  ( 

					((A.AMORT_STATUS <> 'VNM' AND ((@p_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp))

												OR (@p_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_ID)))

						OR

					((A.AMORT_STATUS = 'VNM' OR (A.AMORT_STATUS = 'DTL' AND (A.BRANCH_ID IS NULL OR A.BRANCH_ID = ''))) 

							--AND ((@p_LEVEL = 'ALL' AND MK.TLSUBBRID IN (SELECT BRANCH_ID from @tmp))

							--					OR (@p_LEVEL = 'UNIT' AND MK.TLSUBBRID = @p_BRANCH_ID))) ) 

							AND ((@p_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp))

												OR (@p_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @p_BRANCH_ID))) ) 

					OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = ''

				)			

				AND ( 

					((A.AMORT_STATUS <> 'VNM' AND ((@p_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp))

												OR (@p_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_LOGIN)))

						OR

					((A.AMORT_STATUS = 'VNM' OR (A.AMORT_STATUS = 'DTL' AND (A.BRANCH_ID IS NULL OR A.BRANCH_ID = '')))

							AND ((@p_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp))

												OR (@p_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @p_BRANCH_LOGIN)))) 

					OR @p_BRANCH_LOGIN IS NULL OR @p_BRANCH_LOGIN = ''

				)

		) FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	IF(LEN(@ERROR_MESSAGE) > 0)

	BEGIN

		set @ERROR_MESSAGE = N'Mã tài sản không thuộc đơn vị hoặc không tồn tại trong hệ thống: ' + @ERROR_MESSAGE

		exec sp_seterrormessage @ERROR_MESSAGE

		RETURN '-1'

	END

	select @ERROR_MESSAGE = STUFF( (

	select ',' + ASSET_CODE from @tmpAssetCode T 

	WHERE T.ASSET_CODE IS NOT NULL AND EXISTS(

		SELECT ASSET_CODE 

		FROM ASS_MASTER A 	

		WHERE A.ASSET_CODE = T.ASSET_CODE and A.RECORD_STATUS = '1' AND A.AMORT_STATUS='DTL'

		) FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

	-- validate end

	IF(LEN(@ERROR_MESSAGE) > 0)

	BEGIN

		set @ERROR_MESSAGE = N'Mã tài sản đã được thanh lý: ' + @ERROR_MESSAGE

		exec sp_seterrormessage @ERROR_MESSAGE

		RETURN '-1'

	END

---------------------------

		SELECT

			A.ASSET_ID,

			A.ASSET_CODE,

			A.ASSET_NAME,

			T.LIQ_AMT AS REQ_AMT,

			A.BUY_PRICE,

            ISNULL(ALRD.REQ_AMT, ISNULL((A.AMORT_AMT - A.AMORTIZED_AMT), A.AMORT_AMT)) AS LIQ_AMT,

			ISNULL((A.AMORT_AMT - A.AMORTIZED_AMT), A.AMORT_AMT) AS REMAIN_AMORTIZED_AMT,  --So tien khau hao con lai

			ISNULL((A.AMORT_AMT - A.AMORTIZED_AMT), A.AMORT_AMT) AS LIQ_PRICE, 

			CASE WHEN A.BUY_PRICE > 0 THEN A.BUY_PRICE ELSE 0 END AS BUY_PRICE_LIQ,

			E.STATUS_NAME ASS_STATUS_NAME, --Tinh trang tai san

			A.BRANCH_ID,

			T.LIQ_TYPE AS NOTE,	

			--(T.BUY_PRICE_LIQ / A.BUY_PRICE) * ISNULL((A.AMORT_AMT - A.AMORTIZED_AMT), A.AMORT_AMT) AS REMAIN_VALUE,

			T.LIQ_REASON,

			C.BRANCH_NAME AS BRANCH_NAME_USE,

			D.DEP_NAME AS DEPT_NAME_USE,

			F.STATUS_NAME AMORT_STATUS_NAME

		FROM ASS_MASTER A

			LEFT JOIN ASS_GROUP B ON A.GROUP_ID = B.GROUP_ID AND B.RECORD_STATUS = '1'

			LEFT JOIN dbo.CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID

			LEFT JOIN dbo.CM_DEPARTMENT D ON A.DEPT_ID = D.DEP_ID

			LEFT JOIN ASS_STATUS E ON A.ASS_STATUS = E.STATUS_ID

			LEFT JOIN ASS_AMORT_STATUS F ON A.AMORT_STATUS = F.STATUS_CODE

			INNER JOIN @tmpAssetCode T ON A.ASSET_CODE = T.ASSET_CODE

            LEFT JOIN ASS_LIQ_REQUEST_DT ALRD ON ALRD.ASSET_ID = A.ASSET_ID

		ORDER BY STT

	END