ALTER PROCEDURE dbo.ASS_LIQUIDATION_BVB_Search
@p_LIQ_ID	varchar(15)  = NULL,
@p_BUY_COMPANY_ID	Nvarchar(500)  = NULL,
@p_BUY_PRICE	decimal = NULL,
@p_NOTES	Nvarchar(4000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_AUTH_STATUS	varchar(1)  = NULL,
@p_AUTH_STATUS_KT varchar(1)= NULL,
@p_APPROVE_DT_KT varchar(1)= NULL,
@p_MAKER_ID	varchar(15)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_CHECKER_ID	varchar(15)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_TOP	INT = 10,
@p_BRANCH_ID	VARCHAR(15) = NULL,
@p_LEVEL	VARCHAR(50) = 'UNIT',
@p_FR_BUY_DATE		VARCHAR(10) = NULL, --Tu ngay nhap
@p_TO_BUY_DATE		VARCHAR(10) = NULL, --Den ngay nhap
@p_FR_LIQ_AMT		DECIMAL(18) = NULL, --Tu ngay nhap
@p_TO_LIQ_AMT		DECIMAL(18) = NULL,--Den ngay nhap,
@p_IS_UPDATE VARCHAR(1) = NULL, -- DA DUOC KT CAP NHAT THONG TIN HAY CHUA
@p_USER_LOGIN   VARCHAR(15),-- 26022020 LUCTV TRUYEN XUONG USER LOGIN DE DO TIM MA PHONG BAN
@p_TYPE_SEARCH VARCHAR(10)= NULL -- 26022020 LUCTV TRUYEN XUONG DAU HIEU DE PHAN BIET TIM KIEM O MAN HINH KE TOAN HAY MAN HINH HCQT

AS
BEGIN -- PAGING
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

----
SET @p_RECORD_STATUS = '1'
------
declare @tmp table(BRANCH_ID varchar(15))
insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)

IF(@p_TOP = NULL OR @P_TOP = '' OR @P_TOP = 0)
BEGIN
  -- PAGING BEGIN
	SELECT A.*,B.CONTENT AS AUTH_STATUS_NAME, B1.CONTENT AS AUTH_STATUS_NAME_KT,
  T1.TLFullName MAKER_NAME, T2.TLFullName MAKER_NAME_KT,
  T3.TLFullName APPROVE_NAME, T4.TLFullName APPROVE_NAME_KT, BR.BRANCH_CODE, BR.BRANCH_NAME, BR.BRANCH_CODE +' - '+ BR.BRANCH_NAME AS FULL_BRANCH_NAME,
  @p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH --- TRA VE THAM SO GAN VO CURRENT SEARCH TRANH GAY LOI O CAC MAN HINH
  ,(CASE
  
      WHEN A.AUTH_STATUS='U' AND A.CHECKER_ID IS NULL AND A.PROCESS_ID='SEND' THEN N'Chờ cấp phê duyệt trung gian phê duyệt' --12062023_setretkey FIX cấp duyệt trung gian
			WHEN A.AUTH_STATUS='U' AND A.CHECKER_ID IS NULL THEN N'Chờ trưởng đơn vị phê duyệt'
     WHEN A.AUTH_STATUS='A' AND A.AUTH_STATUS_KT='E' THEN N'Chờ GDV xử lý'
			WHEN A.AUTH_STATUS='A' AND A.AUTH_STATUS_KT='U' AND A.MAKER_ID_KT IS NOT NULL AND A.CHECKER_ID_KT IS NULL THEN N'Chờ KSV phê duyệt'
		END) AS PROCESS_STATUS_NEXT,
		(CASE
      WHEN A.AUTH_STATUS='U' AND A.CHECKER_ID IS NULL AND A.PROCESS_ID='SEND' AND A.SIGN_USER IS NOT NULL  THEN A.SIGN_USER --12062023_setretkey FIX cấp duyệt trung gian
			WHEN A.AUTH_STATUS='U' AND A.CHECKER_ID IS NULL THEN (STUFF((SELECT DISTINCT ', ' + RE.TLNANME FROM FN_GET_USER_BY_ROLE('GDDV',T1.TLSUBBRID,T1.DEP_ID) RE FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''))
     WHEN A.AUTH_STATUS='A' AND A.AUTH_STATUS_KT='E' THEN (STUFF((SELECT DISTINCT ', ' + RE.TLNANME FROM FN_GET_USER_BY_ROLE('GDV','DV0001','DEP000000000022') RE FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''))
			WHEN A.AUTH_STATUS='A' AND A.AUTH_STATUS_KT='U' AND A.MAKER_ID_KT IS NOT NULL AND A.CHECKER_ID_KT IS NULL THEN (STUFF((SELECT DISTINCT ', ' + RE.TLNANME FROM FN_GET_USER_BY_ROLE('KSV','DV0001','DEP000000000022') RE FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''))
		END) NGUOIXULY
  -- SELECT END
	FROM ASS_LIQUIDATION A 
  --LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS 
  --LEFT JOIN CM_AUTH_STATUS B1 ON A.AUTH_STATUS_KT  = B1.AUTH_STATUS
  LEFT JOIN dbo.CM_ALLCODE B ON B.CDVAL = A.AUTH_STATUS AND B.CDNAME = 'ASS_ADD_AUTH' AND B.CDTYPE = 'ASS_ADDNEW'
  LEFT JOIN dbo.CM_ALLCODE B1 ON B1.CDVAL = A.AUTH_STATUS_KT AND B1.CDNAME = 'ASS_ADD_AUTH' AND B1.CDTYPE = 'ASS_ADDNEW'
  ---luctv: 09/01/2019 bo sung left join cm branch
  LEFT JOIN CM_BRANCH BR ON A.BRANCH_ID = BR.BRANCH_ID
  LEFT JOIN TL_USER T1 ON A.MAKER_ID  = T1.TLNANME
  LEFT JOIN TL_USER T2 ON A.MAKER_ID_KT  = T2.TLNANME
  LEFT JOIN TL_USER T3 ON A.CHECKER_ID  = T3.TLNANME
  LEFT JOIN TL_USER T4 ON A.CHECKER_ID_KT  = T4.TLNANME
	WHERE 1 = 1
  AND (A.LIQ_ID LIKE '%' + @p_LIQ_ID + '%' OR  @p_LIQ_ID IS NULL OR @p_LIQ_ID = '')
  AND (A.BUY_COMPANY_ID LIKE '%' + @p_BUY_COMPANY_ID + '%' OR  @p_BUY_COMPANY_ID IS NULL OR @p_BUY_COMPANY_ID = '')
  AND (A.BUY_PRICE = @p_BUY_PRICE OR  @p_BUY_PRICE IS NULL)
  AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')
  AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
  AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
  AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
  AND (DATEDIFF(DAYOFYEAR,A.CREATE_DT,CONVERT(DATETIME, @p_CREATE_DT, 103)) = 0 OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
  AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR  @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
  AND (DATEDIFF(DAYOFYEAR,A.APPROVE_DT,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
  AND A.RECORD_STATUS = '1'
  AND		(A.BUY_PRICE >=  @p_FR_LIQ_AMT OR @p_FR_LIQ_AMT is NULL OR @p_FR_LIQ_AMT = 0)
  AND		(A.BUY_PRICE <=  @p_TO_LIQ_AMT OR @p_TO_LIQ_AMT is NULL OR @p_TO_LIQ_AMT = 0)
  AND (A.AUTH_STATUS_KT LIKE '%' + @p_AUTH_STATUS_KT + '%' OR  @p_AUTH_STATUS_KT IS NULL OR @p_AUTH_STATUS_KT = '' OR A.AUTH_STATUS_KT IS NULL)
  AND		((A.MAKER_ID_KT IS NOT NULL AND @p_IS_UPDATE= 'Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
  	OR ((A.MAKER_ID_KT IS NULL AND @p_IS_UPDATE = 'N'))
    OR @p_IS_UPDATE ='' OR @p_IS_UPDATE IS NULL)

  -- NEW TRANG THAI PHIEU XUAT LA LUU NHAP HOAC TU CHOI THI CHI NGUOI TAO CO THE TIM THAY
  AND ((A.AUTH_STATUS IN ('E','R')  AND A.MAKER_ID = @p_USER_LOGIN) OR A.AUTH_STATUS NOT IN ('E','R') OR @p_USER_LOGIN IS NULL OR @p_USER_LOGIN = '')

  -- 11-03-2022 HUYHT BO SUNG DIEU KIEN TIM KIEM PHIEU XUAT THEO QUYEN KIEM NHIEM
	AND ((@p_TYPE_SEARCH='HC'
		AND ((EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV',A.BRANCH_ID,A.DEPT_ID) US WHERE US.TLNANME = @p_USER_LOGIN))
			OR (A.SIGN_USER = @p_USER_LOGIN))
		OR A.MAKER_ID = @p_USER_LOGIN)
	OR (@p_TYPE_SEARCH='KT' AND @P_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp))
	OR (@p_TYPE_SEARCH='KT' AND @P_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_ID)
	OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH ='')
	
	-- TIM KIEM TU NGAY O HC
  AND	((@p_TYPE_SEARCH = 'HC' AND (CONVERT(DATE,A.CREATE_DT,103) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = ''))
    OR @p_TYPE_SEARCH = 'KT' OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '')
  -- TIM KIEM DEN NGAY O HC
  AND	((@p_TYPE_SEARCH = 'HC' AND (CONVERT(DATE,A.CREATE_DT,103) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = ''))
    OR @p_TYPE_SEARCH = 'KT' OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '')

  -- TIM KIEM TU NGAY O KT
  AND ((@p_TYPE_SEARCH = 'KT' AND (((A.MAKER_ID_KT IS NULL OR A.MAKER_ID_KT = '')
    AND (CONVERT(DATE,A.APPROVE_DT,103) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = ''))
    OR ((A.MAKER_ID_KT IS NOT NULL AND A.MAKER_ID_KT <> '')
        AND ((@p_AUTH_STATUS_KT = 'A' AND (CONVERT(DATE,A.APPROVE_DT_KT,103) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = ''))
          OR (@p_AUTH_STATUS_KT = 'U' AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = ''))
          OR (@p_AUTH_STATUS_KT = 'R' AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = ''))
          OR ((@p_AUTH_STATUS_KT = '' OR @p_AUTH_STATUS_KT IS NULL) AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = ''))))))
  OR @p_TYPE_SEARCH = 'HC' OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '')
  -- TIM KIEM DEN NGAY O KT
  AND ((@p_TYPE_SEARCH = 'KT' AND (((A.MAKER_ID_KT IS NULL OR A.MAKER_ID_KT = '')
    AND (CONVERT(DATE,A.APPROVE_DT,103) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = ''))
    OR ((A.MAKER_ID_KT IS NOT NULL AND A.MAKER_ID_KT <> '')
        AND ((@p_AUTH_STATUS_KT = 'A' AND (CONVERT(DATE,A.APPROVE_DT_KT,103) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = ''))
          OR (@p_AUTH_STATUS_KT = 'U' AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = ''))
          OR (@p_AUTH_STATUS_KT = 'R' AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = ''))
          OR ((@p_AUTH_STATUS_KT = '' OR @p_AUTH_STATUS_KT IS NULL) AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = ''))))))
  OR @p_TYPE_SEARCH = 'HC' OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '')

  ORDER BY A.CREATE_DT DESC
-- PAGING END
END
ELSE
BEGIN
-- PAGING BEGIN
	SELECT TOP(CONVERT(INT,@P_TOP))A.*,B.CONTENT AS AUTH_STATUS_NAME, B1.CONTENT AS AUTH_STATUS_NAME_KT,T1.TLFullName MAKER_NAME, T2.TLFullName MAKER_NAME_KT,
  T3.TLFullName APPROVE_NAME, T4.TLFullName APPROVE_NAME_KT,BR.BRANCH_CODE +' - '+ BR.BRANCH_NAME AS FULL_BRANCH_NAME,
  @p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH --- TRA VE THAM SO GAN VO CURRENT SEARCH TRANH GAY LOI O CAC MAN HINH
	,(CASE
      
      WHEN A.AUTH_STATUS='U' AND A.CHECKER_ID IS NULL AND A.PROCESS_ID='SEND' THEN N'Chờ cấp phê duyệt trung gian phê duyệt' --12062023_setretkey FIX cấp duyệt trung gian
			WHEN A.AUTH_STATUS='U' AND A.CHECKER_ID IS NULL THEN N'Chờ trưởng đơn vị phê duyệt'
     WHEN A.AUTH_STATUS='A' AND A.AUTH_STATUS_KT='E' THEN N'Chờ GDV xử lý'
			WHEN A.AUTH_STATUS='A' AND A.AUTH_STATUS_KT='U' AND A.MAKER_ID_KT IS NOT NULL AND A.CHECKER_ID_KT IS NULL THEN N'Chờ KSV phê duyệt'
		END) AS PROCESS_STATUS_NEXT,
		(CASE
      
      WHEN A.AUTH_STATUS='U' AND A.CHECKER_ID IS NULL AND A.PROCESS_ID='SEND' AND A.SIGN_USER IS NOT NULL  THEN A.SIGN_USER --12062023_setretkey FIX cấp duyệt trung gian
			WHEN A.AUTH_STATUS='U' AND A.CHECKER_ID IS NULL THEN (STUFF((SELECT DISTINCT ', ' + RE.TLNANME FROM FN_GET_USER_BY_ROLE('GDDV',T1.TLSUBBRID,T1.DEP_ID) RE FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''))
     WHEN A.AUTH_STATUS='A' AND A.AUTH_STATUS_KT='E' THEN (STUFF((SELECT DISTINCT ', ' + RE.TLNANME FROM FN_GET_USER_BY_ROLE('GDV','DV0001','DEP000000000022') RE FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''))
			WHEN A.AUTH_STATUS='A' AND A.AUTH_STATUS_KT='U' AND A.MAKER_ID_KT IS NOT NULL AND A.CHECKER_ID_KT IS NULL THEN (STUFF((SELECT DISTINCT ', ' + RE.TLNANME FROM FN_GET_USER_BY_ROLE('KSV','DV0001','DEP000000000022') RE FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''))
		END) NGUOIXULY
-- SELECT END
	FROM ASS_LIQUIDATION A 
  LEFT JOIN CM_BRANCH BR ON A.BRANCH_ID = BR.BRANCH_ID
  --LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS 
  --LEFT JOIN CM_AUTH_STATUS B1 ON A.AUTH_STATUS_KT  = B1.AUTH_STATUS
  LEFT JOIN dbo.CM_ALLCODE B ON B.CDVAL = A.AUTH_STATUS AND B.CDNAME = 'ASS_ADD_AUTH' AND B.CDTYPE = 'ASS_ADDNEW'
  LEFT JOIN dbo.CM_ALLCODE B1 ON B1.CDVAL = A.AUTH_STATUS_KT AND B1.CDNAME = 'ASS_ADD_AUTH' AND B1.CDTYPE = 'ASS_ADDNEW'
  LEFT JOIN TL_USER T1 ON A.MAKER_ID  = T1.TLNANME
  LEFT JOIN TL_USER T2 ON A.MAKER_ID_KT  = T2.TLNANME
  LEFT JOIN TL_USER T3 ON A.CHECKER_ID  = T3.TLNANME
  LEFT JOIN TL_USER T4 ON A.CHECKER_ID_KT  = T4.TLNANME
	WHERE 1 = 1
  AND (A.LIQ_ID LIKE '%' + @p_LIQ_ID + '%' OR  @p_LIQ_ID IS NULL OR @p_LIQ_ID = '')
  AND (A.BUY_COMPANY_ID LIKE '%' + @p_BUY_COMPANY_ID + '%' OR  @p_BUY_COMPANY_ID IS NULL OR @p_BUY_COMPANY_ID = '')
  AND (A.BUY_PRICE = @p_BUY_PRICE OR  @p_BUY_PRICE IS NULL)
  AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')
  AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
  AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
  AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
  AND (DATEDIFF(DAYOFYEAR,A.CREATE_DT,CONVERT(DATETIME, @p_CREATE_DT, 103)) = 0 OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
  AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR  @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
  AND (DATEDIFF(DAYOFYEAR,A.APPROVE_DT,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
  AND A.RECORD_STATUS = '1'
  AND		(A.BUY_PRICE >=  @p_FR_LIQ_AMT OR @p_FR_LIQ_AMT is NULL OR @p_FR_LIQ_AMT = 0)
  AND		(A.BUY_PRICE <=  @p_TO_LIQ_AMT OR @p_TO_LIQ_AMT is NULL OR @p_TO_LIQ_AMT = 0)
  AND (A.AUTH_STATUS_KT LIKE '%' + @p_AUTH_STATUS_KT + '%' OR  @p_AUTH_STATUS_KT IS NULL OR @p_AUTH_STATUS_KT = '' OR A.AUTH_STATUS_KT IS NULL)
  AND		((A.MAKER_ID_KT IS NOT NULL AND @p_IS_UPDATE= 'Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
  	OR ((A.MAKER_ID_KT IS NULL AND @p_IS_UPDATE = 'N'))
  	OR @p_IS_UPDATE ='' OR @p_IS_UPDATE IS NULL)

  -- NEW TRANG THAI PHIEU XUAT LA LUU NHAP HOAC TU CHOI THI CHI NGUOI TAO CO THE TIM THAY
   AND ((A.AUTH_STATUS IN ('E','R')  AND A.MAKER_ID = @p_USER_LOGIN) OR A.AUTH_STATUS NOT IN ('E','R') OR @p_USER_LOGIN IS NULL OR @p_USER_LOGIN = '')
  
  -- 11-03-2022 HUYHT BO SUNG DIEU KIEN TIM KIEM PHIEU XUAT THEO QUYEN KIEM NHIEM
	AND ((@p_TYPE_SEARCH='HC'
		AND ((EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV',A.BRANCH_ID,A.DEPT_ID) US WHERE US.TLNANME = @p_USER_LOGIN))
			OR (A.SIGN_USER = @p_USER_LOGIN))
		OR A.MAKER_ID = @p_USER_LOGIN)
    OR (@p_TYPE_SEARCH='KT' AND @P_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp))
  	OR (@p_TYPE_SEARCH='KT' AND @P_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_ID)
    OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH ='')

	-- TIM KIEM TU NGAY O HC
  AND	((@p_TYPE_SEARCH = 'HC' AND (CONVERT(DATE,A.CREATE_DT,103) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = ''))
    OR @p_TYPE_SEARCH = 'KT' OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '')
  -- TIM KIEM DEN NGAY O HC
  AND	((@p_TYPE_SEARCH = 'HC' AND (CONVERT(DATE,A.CREATE_DT,103) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = ''))
    OR @p_TYPE_SEARCH = 'KT' OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '')

  -- TIM KIEM TU NGAY O KT
  AND ((@p_TYPE_SEARCH = 'KT' AND (((A.MAKER_ID_KT IS NULL OR A.MAKER_ID_KT = '')
    AND (CONVERT(DATE,A.APPROVE_DT,103) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = ''))
    OR ((A.MAKER_ID_KT IS NOT NULL AND A.MAKER_ID_KT <> '')
        AND ((@p_AUTH_STATUS_KT = 'A' AND (CONVERT(DATE,A.APPROVE_DT_KT,103) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = ''))
          OR (@p_AUTH_STATUS_KT = 'U' AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = ''))
          OR (@p_AUTH_STATUS_KT = 'R' AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = ''))
          OR ((@p_AUTH_STATUS_KT = '' OR @p_AUTH_STATUS_KT IS NULL) AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FR_BUY_DATE, 103) OR @p_FR_BUY_DATE is NULL OR @p_FR_BUY_DATE = ''))))))
  OR @p_TYPE_SEARCH = 'HC' OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '')
  -- TIM KIEM DEN NGAY O KT
  AND ((@p_TYPE_SEARCH = 'KT' AND (((A.MAKER_ID_KT IS NULL OR A.MAKER_ID_KT = '')
    AND (CONVERT(DATE,A.APPROVE_DT,103) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = ''))
    OR ((A.MAKER_ID_KT IS NOT NULL AND A.MAKER_ID_KT <> '')
        AND ((@p_AUTH_STATUS_KT = 'A' AND (CONVERT(DATE,A.APPROVE_DT_KT,103) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = ''))
          OR (@p_AUTH_STATUS_KT = 'U' AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = ''))
          OR (@p_AUTH_STATUS_KT = 'R' AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = ''))
          OR ((@p_AUTH_STATUS_KT = '' OR @p_AUTH_STATUS_KT IS NULL) AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TO_BUY_DATE, 103) OR @p_TO_BUY_DATE is NULL OR @p_TO_BUY_DATE = ''))))))
  OR @p_TYPE_SEARCH = 'HC' OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '')

	ORDER BY A.CREATE_DT DESC
-- PAGING END
END
END -- PAGING