AMS - VB

ALTER PROCEDURE dbo.rpt_INVENTORY_Search

	@p_FromDate varchar(20) = null,

	@p_ToDate varchar(20) = null,

	@p_BRANCH_ID varchar(15) = null,

	@p_ASS_STATUS NVARCHAR(20)=NULL,

	@p_BRANCH_LOGIN VARCHAR(15) = NULL,

	@p_LEVEL VARCHAR(15) = 'ALL',

	@p_DEP_ID VARCHAR(15) = NULL,

	@p_TYPE_ID VARCHAR(15) = NULL,

	@p_IGNORE VARCHAR(10) = NULL,--KHONG TINH DON VI DANG XUAT

	@l_NGAYSAOKE VARCHAR(25)=NULL,--NGAY KIEM KE

	@p_TERM_ID VARCHAR(25)=NULL, --DOT KIEM KE

	@p_IS_REDUNDANT VARCHAR(1)=NULL -- XUAT DU LiEU THUA THIEU

AS

BEGIN

	DECLARE

	@lp_Fromdate varchar(20) = null,

	@lp_Todate varchar(20) = null,

	@lp_BRANCH_ID varchar(15) = null,

	@lp_ASS_STATUS NVARCHAR(20)=NULL,

	@lp_BRANCH_LOGIN VARCHAR(15) = NULL,

	@lP_LEVEL VARCHAR(15) = 'ALL',

	@lp_DEP_ID VARCHAR(15) = NULL,

	@lp_TYPE_ID VARCHAR(15) = NULL,

	@lp_IGNORE VARCHAR(10) = NULL,--KHONG TINH DON VI DANG XUAT

	@ll_NGAYSAOKE VARCHAR(25)=NULL,--NGAY KIEM KE

	@lp_TERM_ID VARCHAR(25)=NULL --DOT KIEM KE

	set @lp_Fromdate         = @p_FromDate

	set @lp_Todate			 = @p_ToDate

	set @lp_BRANCH_ID 		 = @p_BRANCH_ID

	set @lp_ASS_STATUS 		 = @p_ASS_STATUS

	set @lp_BRANCH_LOGIN 	 = @p_BRANCH_LOGIN

	set @lP_LEVEL 			 = @p_LEVEL

	set @lp_DEP_ID			 = @p_DEP_ID

	set @lp_TYPE_ID 		 = @p_TYPE_ID

	set @lp_IGNORE 			 = @p_IGNORE

	set @ll_NGAYSAOKE 		 = @l_NGAYSAOKE

	set @lp_TERM_ID 		 = @p_TERM_ID

--thieuvq 19/7/2016 - DOC DU LIEU BO QUA COMMIT TRANSACTION 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

--

declare --@ll_NGAYSAOKE varchar(25) = '30/11/2014',

@lp_FromdateSK varchar(25) = @ll_NGAYSAOKE

declare @ltmp table(BRANCH_ID varchar(15))

insert into @ltmp  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@lp_BRANCH_ID)

declare @ltmp_login table(BRANCH_ID varchar(15))

insert into @ltmp_login  SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@lp_BRANCH_LOGIN)

declare @statusHistTmp table(

	ASSET_ID VARCHAR(15),

	STATUS_ID VARCHAR(15),

	[ASS_STATUS] [nvarchar](500) NULL,

	[ASSET_DESC] [nvarchar](500) NULL,

	NOTES [nvarchar](1000) NULL

)

insert into @statusHistTmp(ASSET_ID, STATUS_ID,ASS_STATUS, ASSET_DESC,NOTES)

SELECT HIS.ASSET_ID, STAT.STATUS_ID,HIS.ASS_STATUS, HIS.ASSET_DESC, NOTES FROM dbo.GetLatestAssetHis(NULL, @p_ToDate) HIS

LEFT JOIN ASS_STATUS STAT ON STAT.STATUS_NAME = HIS.ASS_STATUS

DECLARE @tmp TABLE(GROUP_ID VARCHAR(15), LEVEL_CODE NVARCHAR(MAX), ASSET_CODE NVARCHAR(MAX))

INSERT INTO @tmp

(

    GROUP_ID,

    LEVEL_CODE,

    ASSET_CODE

)

SELECT GROUP_ID, CASE WHEN TYPE_ID = 'CCLD' THEN [dbo].[FN_GET_GROUPCODE](GROUP_ID,'1') 

ELSE [dbo].[FN_GET_GROUPCODE](GROUP_ID,'2') END AS LEVEL_CODE,

CASE WHEN TYPE_ID = 'CCLD' THEN [dbo].[FN_GET_GROUPNAME](GROUP_ID,'1') ELSE

[dbo].[FN_GET_GROUPNAME](GROUP_ID,'2') END AS ASSET_CODE

FROM dbo.ASS_GROUP

--

	SELECT 

	Row_number() over(PARTITION BY T.LEVEL_CODE order by A.ASSET_NAME)  AS STT,

	(CASE C.BRANCH_TYPE  WHEN 'PGD' then D.BRANCH_NAME 		

		ELSE C.BRANCH_NAME END) CHI_NHANH,

	(CASE C.BRANCH_TYPE when 'HS' Then F.DEP_NAME 	

		ELSE C.BRANCH_NAME END) PGD_PHONG_BAN, 

	AG.GROUP_NAME AS ASSET_GROUP_NAME,

	A.ASSET_CODE, --MA SO TAI SAN

	A.ASSET_NAME, --TEN TAI SAN

	A.ASSET_SERIAL_NO,

	A.BUY_PRICE, -- NGUYÊN GIÁ

	(ISNULL(CC.PHAT_SINH_DAU,0) + ISNULL(EE.PHAT_SINH_TANG_TRONG_KHOANG,0) - ISNULL(FF.PHAT_SINH_GIAM_TRONG_KHOANG,0)) AS NGUYEN_GIA,

	(ISNULL(DD.PHAT_SINH_CUOI,0) - ISNULL(AA.TONG_KH_TRONG_KHOANG,0) - ISNULL(BB.TONG_KH_DAU,0) + ISNULL(DR.TONG_KH_TRONG_KHOANG,0)) AS GTCL, --GIA TRI CON LAI	

	A.USE_DATE,--NGAY SU DUNG

	A.ASSET_DESC, --MO TA	

	PQ.ASSET_DESC AS HIEN_TRANG, --HIEN TRANG CCLD	

	A.NOTES, 

	'' NKSC,--NHAT KY SUA CHUA

	--'' NQL, -- NGUOI QUAN LY

	A.USE_STATUS NQL, -- NGUOI QUAN LY

	ISNULL(KK.ASS_STATUS,N'Chưa kiểm kê') AS TINHTRANGKIEMKE,

	A.AMORT_RATE AS TLKH,

  A.AMORT_END_DATE,

  DD.PHAT_SINH_CUOI - BB.TONG_KH_DAU AS REMAIN_VALUE,

  '1' AS SL_SS, '' AS SL_TT, '' AS SL_THUA, '' AS SL_THIEU, AST.STATUS_NAME AS ASS_STATUS,

  CE.EMP_NAME, CE.EMP_CODE, CB.BRANCH_NAME,AG.GROUP_CODE AS ASSET_GROUP_CODE,

  CASE WHEN CB.BRANCH_TYPE = 'HS' THEN CD.DEP_CODE ELSE CB.BRANCH_CODE END AS BRANCH_DEP_CODE

	FROM ASS_MASTER  A

	LEFT JOIN @tmp T ON A.GROUP_ID = T.GROUP_ID

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

  LEFT JOIN ASS_GROUP AG ON A.GROUP_ID = AG.GROUP_ID

  LEFT JOIN CM_EMPLOYEE CE ON A.EMP_ID = CE.EMP_ID

  LEFT JOIN CM_BRANCH CB ON A.BRANCH_ID = CB.BRANCH_ID

  LEFT JOIN CM_DEPARTMENT CD ON A.DEPT_ID = CD.DEP_ID

	--LEFT JOIN 

	--(

	--	SELECT M.ASSET_ID, N.APPROVE_DT, N.ASSET_STATUS, N.INVENT_DESC, N.ASS_STATUS, N.AUTH_STATUS, N.RECORD_STATUS, N.NOTES

	--	FROM 

	--	(

	--	SELECT A.ASSET_ID, MAX(A.INVENTDT_ID) AS INVENTDT_ID

	--	FROM 

	--	(

	--	SELECT A.INVENTDT_ID, A.ASSET_ID, A.ASSET_STATUS, B.APPROVE_DT FROM ASS_INVENTORY_DT A

	--	LEFT JOIN ASS_INVENTORY_MASTER B ON A.INVENT_ID = B.INVENT_ID

	--	WHERE

	--		(CONVERT(DATE,B.APPROVE_DT) >= CONVERT(DATE,@lp_Fromdate,103) OR @lp_Fromdate IS NULL OR @lp_Fromdate = '')

	--		AND (CONVERT(DATE,B.APPROVE_DT) < CONVERT(DATE,@lp_Todate,103) OR @lp_Todate IS NULL OR @lp_Todate = '')

	--		AND B.AUTH_STATUS = 'A'

	--		AND B.RECORD_STATUS = '1'

	--		--THIEUVQ THEM DIEU KIEN THONG KE THEO DOT KIEM KE

	--			AND B.TERM = @lp_TERM_ID 

	--			AND CONVERT(DATE,B.INVENTORY_DT) = CONVERT(DATE,@ll_NGAYSAOKE,103)

	--		--THIEUVQ THEM DIEU KIEN KHONG LAY TAI SAN THUA LEN SAO KE - 02122016 TANPN

	--		AND A.ASSET_STATUS <> '6'

	--	GROUP BY A.INVENTDT_ID, A.ASSET_ID, A.ASSET_STATUS, B.APPROVE_DT

	--	--ORDER BY A.ASSET_ID, B.APPROVE_DT DESC

	--	) AS A

	--	GROUP BY A.ASSET_ID

	--	) AS M

	--	INNER JOIN

	--	(

	--	SELECT A.INVENTDT_ID, A.ASSET_ID, A.ASSET_STATUS, B.APPROVE_DT, A.INVENT_DESC, C.ASS_STATUS, B.AUTH_STATUS, B.RECORD_STATUS , A.NOTES

	--	FROM ASS_INVENTORY_DT A

	--	LEFT JOIN ASS_INVENTORY_MASTER B ON A.INVENT_ID = B.INVENT_ID

	--	--LEFT JOIN ASS_STATUS C ON A.ASSET_STATUS = C.STATUS_ID

	--	LEFT JOIN 

	--		( 

	--			SELECT * FROM GetLatestAssetHis(NULL, @p_ToDate)

	--		) C ON C.ASSET_ID = A.ASSET_ID

	--	WHERE 

	--		(CONVERT(DATE,B.APPROVE_DT) >= CONVERT(DATE,@lp_Fromdate,103) OR @lp_Fromdate IS NULL OR @lp_Fromdate = '')

	--		AND (CONVERT(DATE,B.APPROVE_DT) < CONVERT(DATE,@lp_Todate,103) OR @lp_Todate IS NULL OR @lp_Todate = '')

	--		AND B.AUTH_STATUS = 'A'

	--		AND B.RECORD_STATUS = '1'

	--		--THIEUVQ THEM DIEU KIEN THONG KE THEO DOT KIEM KE

	--			AND B.TERM = @lp_TERM_ID 

	--			AND CONVERT(DATE,B.INVENTORY_DT) = CONVERT(DATE,@ll_NGAYSAOKE,103)

	--		--THIEUVQ THEM DIEU KIEN KHONG LAY TAI SAN THUA LEN SAO KE - 02122016 TANPN

	--		AND A.ASSET_STATUS <> '6'

	--	GROUP BY A.INVENTDT_ID, A.ASSET_ID, A.ASSET_STATUS, B.APPROVE_DT, A.INVENT_DESC, C.ASS_STATUS, B.AUTH_STATUS, B.RECORD_STATUS, A.NOTES

	--	) AS N ON M.ASSET_ID = N.ASSET_ID

	--	WHERE M.INVENTDT_ID = N.INVENTDT_ID

	--	--ORDER BY M.ASSET_ID

	--) AS KK ON A.ASSET_ID = KK.ASSET_ID

		LEFT JOIN @statusHistTmp KK ON KK.ASSET_ID = A.ASSET_ID

		LEFT JOIN ASS_STATUS ST ON KK.STATUS_ID = ST.STATUS_ID

	LEFT JOIN 

	(

		SELECT A.ASSET_ID,		

			ISNULL(TR.BRANCH_ID, TRN.BRANCH_ID) AS BRANCH_ID,

			CASE WHEN TR.BRANCH_ID IS NULL THEN TRN.DEPT_ID ELSE TR.DEPT_ID END AS DEPT_ID,

			CASE WHEN TR.BRANCH_ID IS NULL THEN TRN.EMP_ID ELSE TR.EMP_ID END AS EMP_ID				

		FROM ASS_MASTER A

		LEFT JOIN --LAY DON VI MOI NHAT 

		(

			SELECT A.ASSET_ID, A.BRANCH_ID, A.DEPT_ID, A.EMP_ID		

			FROM ASS_LOCATION_HIST A		

			INNER JOIN

			(

				SELECT MAX(B.LOCHIST_ID) AS LOCHIST_ID, B.ASSET_ID

				FROM ASS_LOCATION_HIST B 

				WHERE B.USE_START_DT <= CONVERT(DATE,@ll_NGAYSAOKE,103)

				GROUP BY B.ASSET_ID

			) AS TRN ON TRN.LOCHIST_ID = A.LOCHIST_ID		

		) AS TR ON TR.ASSET_ID = A.ASSET_ID

		LEFT JOIN --LAY DON VI CU NHAT

		(

			SELECT A.ASSET_ID, A.BRANCH_ID, A.DEPT_ID, A.EMP_ID		

			FROM ASS_LOCATION_HIST A		

			INNER JOIN

			(

				SELECT MIN(B.LOCHIST_ID) AS LOCHIST_ID, B.ASSET_ID

				FROM ASS_LOCATION_HIST B 

				WHERE B.USE_START_DT > CONVERT(DATE,@ll_NGAYSAOKE,103)

				GROUP BY B.ASSET_ID

			) AS TRN ON TRN.LOCHIST_ID = A.LOCHIST_ID		

		) AS TRN ON TRN.ASSET_ID = A.ASSET_ID

	) AS BRN_HIST ON BRN_HIST.ASSET_ID = A.ASSET_ID

	--LEFT JOIN ASS_INVENTORY_DT IDT ON IDT.ASSET_ID = A.ASSET_ID

	LEFT JOIN ASS_GROUP B ON A.GROUP_ID = B.GROUP_ID

	LEFT JOIN CM_BRANCH C ON BRN_HIST.BRANCH_ID = C.BRANCH_ID

	LEFT JOIN CM_BRANCH D ON D.BRANCH_ID = C.FATHER_ID

	LEFT JOIN CM_EMPLOYEE E ON BRN_HIST.EMP_ID = E.EMP_ID

	LEFT JOIN CM_DEPARTMENT F ON BRN_HIST.DEPT_ID = F.DEP_ID	

	LEFT JOIN

	(

		SELECT X.ASSET_ID,ISNULL(SUM(X.AMORT_AMT),0) AS TONG_KH_TRONG_KHOANG

		FROM ASS_AMORT_DT X

		--INNER JOIN ASS_AMORT Y ON X.AMORT_ID = Y.AMORT_ID

		WHERE 

		(X.AMORT_DT >= CONVERT(DATE,@lp_FromdateSK,103) OR @lp_FromdateSK IS NULL OR @lp_FromdateSK = '') 

		AND 

		(X.AMORT_DT <= CONVERT(DATE,@ll_NGAYSAOKE,103)OR @ll_NGAYSAOKE IS NULL OR @ll_NGAYSAOKE = '')

		AND CRDR = 'C'

		GROUP BY X.ASSET_ID

	) AA ON A.ASSET_ID = AA.ASSET_ID

	LEFT JOIN

	(

		SELECT X.ASSET_ID,ISNULL(SUM(X.AMORT_AMT),0) AS TONG_KH_TRONG_KHOANG

		FROM ASS_AMORT_DT X

		--INNER JOIN ASS_AMORT Y ON X.AMORT_ID = Y.AMORT_ID

		WHERE 

		(X.AMORT_DT >= CONVERT(DATE,@lp_FromdateSK,103) OR @lp_FromdateSK IS NULL OR @lp_FromdateSK = '') 

		AND (X.AMORT_DT <= CONVERT(DATE,@ll_NGAYSAOKE,103)OR @ll_NGAYSAOKE IS NULL OR @ll_NGAYSAOKE = '')

		AND CRDR = 'D'

		GROUP BY X.ASSET_ID

	) DR ON A.ASSET_ID = DR.ASSET_ID

	-- SELECT * FROM ASS_AMORT_DT

	LEFT JOIN

	(

		--SELECT X.ASSET_ID,ISNULL(SUM(X.AMORT_AMT),0) AS TONG_KH_DAU

		SELECT X.ASSET_ID,ISNULL(SUM(CASE X.CRDR  WHEN 'C' THEN X.AMORT_AMT WHEN 'D' THEN -X.AMORT_AMT END),0) AS TONG_KH_DAU

		FROM ASS_AMORT_DT X

		--INNER JOIN ASS_AMORT Y ON X.AMORT_ID = Y.AMORT_ID

		INNER JOIN ASS_MASTER Z ON X.ASSET_ID = Z.ASSET_ID

		WHERE 

		(X.AMORT_DT >= CONVERT(DATE,Z.AMORT_START_DATE)) AND (CONVERT(DATE,Z.LIQUIDATION_DT) > CONVERT(DATE,@lp_FromdateSK,103) OR Z.LIQUIDATION_DT IS NULL OR Z.LIQUIDATION_DT = '')

		AND (X.AMORT_DT <= CONVERT(DATE,@ll_NGAYSAOKE,103) OR @ll_NGAYSAOKE IS NULL OR @ll_NGAYSAOKE = '')

		GROUP BY X.ASSET_ID

	) BB ON A.ASSET_ID = BB.ASSET_ID

	-- SELECT TOP 200 * FROM ASS_MASTER

	-- PHAT SINH TANG VA GIAM TRONG KHOANG THOI GIAN TU BAT DAU KHAU HAO DEN THOI DIEM FROM

	-- PHAT SINH TANG VA GIAM TRONG KHOANG THOI GIAN TU THỜI ĐIỂM BAT DAU ĐẾN THỜI ĐIỂM TO

	-- SELECT * FROM ASS_MASTER

	LEFT JOIN

	(

		SELECT X.ASSET_ID,ISNULL(SUM(CASE X.CRDR  WHEN 'C' THEN X.ASSET_AMT WHEN 'D' THEN -X.ASSET_AMT END),0) AS PHAT_SINH_CUOI

		FROM ASS_VALUES X

		INNER JOIN ASS_MASTER Y ON X.ASSET_ID = Y.ASSET_ID

		WHERE --X.CREATE_DT >= Y.CREATE_DT 

			 (X.CREATE_DT <= CONVERT(DATE,@ll_NGAYSAOKE,103) OR @ll_NGAYSAOKE IS NULL OR @ll_NGAYSAOKE = '')

		GROUP BY X.ASSET_ID

	) DD ON A.ASSET_ID = DD.ASSET_ID

	-- PHAT SINH TANG TRONG KHOANG THOI GIAN TU THỜI ĐIỂM FROM ĐẾN TO

	-- select * from ass_values where trn_type = 'ass_use'	

	LEFT JOIN

	(

		SELECT X.ASSET_ID,ISNULL(SUM(CASE X.CRDR  WHEN 'C' THEN X.ASSET_AMT WHEN 'D' THEN -X.ASSET_AMT END),0) AS PHAT_SINH_DAU 

		FROM ASS_VALUES X

		INNER JOIN ASS_MASTER Y ON X.ASSET_ID = Y.ASSET_ID

		WHERE (X.TRN_DT < CONVERT(DATE,@lp_FromdateSK,103) OR @lp_FromdateSK IS NULL OR @lp_FromdateSK = '') 

		--(X.CREATE_DT >= Y.CREATE_DT) 

		--AND (X.CREATE_DT <= CONVERT(DATETIME,@lp_Fromdate,103) OR @lp_Fromdate IS NULL OR @lp_Fromdate = '')

		GROUP BY X.ASSET_ID

	) CC ON A.ASSET_ID = CC.ASSET_ID

	-- PHAT SINH TANG TRONG KHOANG THOI GIAN TU THỜI ĐIỂM FROM ĐẾN TO

	-- select * from ass_values where trn_type = 'ass_use'

	LEFT JOIN

	(

		SELECT X.ASSET_ID,ISNULL(SUM(X.ASSET_AMT),0) AS PHAT_SINH_TANG_TRONG_KHOANG

		FROM ASS_VALUES X

		--INNER JOIN ASS_MASTER Y ON X.ASSET_ID = Y.ASSET_ID

		WHERE (X.CREATE_DT >= CONVERT(DATE,@lp_FromdateSK,103) OR @lp_FromdateSK IS NULL OR @lp_FromdateSK = '') 

		AND (X.CREATE_DT <= CONVERT(DATE,@ll_NGAYSAOKE,103) OR @ll_NGAYSAOKE IS NULL OR @ll_NGAYSAOKE = '')

		AND X.CRDR = 'C'

		GROUP BY X.ASSET_ID

	) EE ON A.ASSET_ID = EE.ASSET_ID

	-- PHAT SINH GIAM TRONG KHOANG THOI GIAN TU THỜI ĐIỂM FROM ĐẾN TO

	LEFT JOIN

	(

		SELECT X.ASSET_ID,ISNULL(SUM(X.ASSET_AMT),0) AS PHAT_SINH_GIAM_TRONG_KHOANG

		FROM ASS_VALUES X

		--INNER JOIN ASS_MASTER Y ON X.ASSET_ID = Y.ASSET_ID

		WHERE (X.CREATE_DT >= CONVERT(DATE,@lp_FromdateSK,103) OR @lp_FromdateSK IS NULL OR @lp_FromdateSK = '') 

		AND (X.CREATE_DT <= CONVERT(DATE,@ll_NGAYSAOKE,103) OR @ll_NGAYSAOKE IS NULL OR @ll_NGAYSAOKE = '')

		AND X.CRDR = 'D'

		GROUP BY X.ASSET_ID

	) FF ON A.ASSET_ID = FF.ASSET_ID

	LEFT JOIN  -- vietpq join theo ass_status_hist 09-01-20

	(

		SELECT DISTINCT ASSET_ID, MAX(STATUS_DT) AS STATUS_DT FROM dbo.ASS_STATUS_HIST

		GROUP BY ASSET_ID

	) HST_TEMP ON HST_TEMP.ASSET_ID = A.ASSET_ID

	LEFT JOIN  -- vietpq join theo ass_status_hist 09-01-20

	(

		SELECT * FROM ASS_STATUS_HIST

	) PQ ON PQ.ASSET_ID = HST_TEMP.ASSET_ID AND PQ.STATUS_DT = HST_TEMP.STATUS_DT

	-- SELECT * FROM ASS_VALUES

	WHERE  A.RECORD_STATUS = '1' AND A.AUTH_STATUS = 'A'

	AND A.ENTRY_BOOKED = 'Y'

		-- NHOM TAI SAN			

	AND (A.USE_DATE <= CONVERT(DATE,@ll_NGAYSAOKE,103) OR @ll_NGAYSAOKE IS NULL OR @ll_NGAYSAOKE = '') --THEM 15/04 THIEUVQ

	AND A.TYPE_ID = 'TSCD' --THEM 15/04 THIEUVQ	

	AND (BRN_HIST.DEPT_ID = @lp_DEP_ID OR @lp_DEP_ID IS NULL OR @lp_DEP_ID = '')

	AND ((@lP_LEVEL = 'ALL' AND BRN_HIST.BRANCH_ID IN (SELECT BRANCH_ID from @ltmp))

		OR (@lP_LEVEL = 'UNIT' AND BRN_HIST.BRANCH_ID = @lp_BRANCH_ID) OR @lp_BRANCH_ID = '' OR @lp_BRANCH_ID IS NULL)

	AND ((@lP_LEVEL = 'ALL' AND BRN_HIST.BRANCH_ID IN (SELECT BRANCH_ID from @ltmp_login))

		OR (@lP_LEVEL = 'UNIT' AND BRN_HIST.BRANCH_ID = @lp_BRANCH_LOGIN) OR @lp_BRANCH_LOGIN = '' OR @lp_BRANCH_LOGIN IS NULL 

		OR @lp_BRANCH_ID IS NOT NULL OR @lp_BRANCH_ID <> '')

	AND (CONVERT(DATE,A.LIQUIDATION_DT) > CONVERT(DATE,@lp_FromdateSK,103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT = '')

	AND A.AMORT_STATUS <> 'VNM'

	AND ((@p_IS_REDUNDANT = '1' AND (ST.STATUS_CODE = '05' OR ST.STATUS_CODE = '06')) OR @p_IS_REDUNDANT IS NULL OR @p_IS_REDUNDANT = '' OR @p_IS_REDUNDANT <> '1')

	ORDER BY A.ASSET_NAME

END