ALTER PROCEDURE dbo.ASS_TRANSFER_MULTI_MASTER_Search
@p_TRANS_MULTI_MASTER_ID	varchar(15)  = NULL,
@p_BRANCH_ID	varchar(15)  = NULL,
@p_TRANSFER_DT	VARCHAR(20) = NULL,
@p_USER_TRANSFER	nvarchar(200)  = NULL,
@p_USER_TRANSFER_NAME	nvarchar(200)  = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_AUTH_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(100)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_CHECKER_ID	varchar(100)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS_KT	varchar(15)  = NULL,
@p_CREATE_DT_KT	varchar(100) = NULL,
@p_APPROVE_DT_KT	VARCHAR(20) = NULL,
@p_MAKER_ID_KT	varchar(100)  = NULL,
@p_CHECKER_ID_KT	varchar(100)  = NULL,
@p_REPORT_STATUS	varchar(15)  = NULL,
@p_LEVEL	VARCHAR(50) = 'UNIT',
@p_TOP	INT = 10,
@p_FromDate VARCHAR(20) = NULL,
@p_TODATE VARCHAR(20) = NULL,
@p_IS_UPDATE VARCHAR(1) = NULL,
@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
@p_REQ_CODE VARCHAR(100) = NULL --Thêm trường tìm kiếm số PYC
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)

--TÌM THÔNG TIN USER LOGIN
DECLARE  @ROLE_LOGIN TABLE(ROLE_USER VARCHAR(20), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20))
    INSERT INTO @ROLE_LOGIN
    SELECT tugr.ROLE_ID, tugr.BRANCH_ID, tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_USER_LOGIN) tugr

--TÌM KIẾM THEO QUYỀN XỬ LÝ
DECLARE @TRANS_MULTI_MASTER_ID_Temp TABLE(TRANS_MASTER_ID VARCHAR(20))
    INSERT INTO @TRANS_MULTI_MASTER_ID_Temp
    SELECT A.TRANS_MULTI_MASTER_ID FROM ASS_TRANSFER_MULTI_MASTER A
    LEFT JOIN PL_REQUEST_PROCESS B ON A.TRANS_MULTI_MASTER_ID = B.REQ_ID AND B.STATUS = 'C'
    WHERE (EXISTS(SELECT RL.ROLE_USER FROM @ROLE_LOGIN RL WHERE RL.BRANCH_ID = B.BRANCH_ID
        AND (B.BRANCH_ID = 'DV0001' AND B.DEP_ID = RL.DEP_ID OR B.BRANCH_ID <> 'DV0001')
        AND RL.ROLE_USER = B.ROLE_USER)
      OR EXISTS(SELECT * FROM @ROLE_LOGIN RL
        JOIN PL_REQUEST_PROCESS C ON C.REQ_ID = A.TRANS_MULTI_MASTER_ID AND C.STATUS = 'P' AND RL.BRANCH_ID = C.BRANCH_ID
        AND (C.BRANCH_ID = 'DV0001' AND C.DEP_ID = RL.DEP_ID OR C.BRANCH_ID <> 'DV0001')
        AND RL.ROLE_USER = C.ROLE_USER)
      OR EXISTS(SELECT * FROM @ROLE_LOGIN RL
        JOIN PL_REQUEST_PROCESS C ON C.REQ_ID = A.TRANS_MULTI_MASTER_ID AND C.PROCESS_ID = 'TGD' AND RL.BRANCH_ID = C.BRANCH_ID
        AND (C.BRANCH_ID = 'DV0001' OR C.BRANCH_ID <> 'DV0001')
        AND RL.ROLE_USER = C.ROLE_USER)
      OR A.MAKER_ID = @p_USER_LOGIN)
    GROUP BY A.TRANS_MULTI_MASTER_ID

IF(@p_TOP is NULL OR @p_TOP = '' OR @p_TOP = 0)
	-- PAGING BEGIN

		SELECT A.*,C.BRANCH_CODE, F.AUTH_STATUS_NAME,C.BRANCH_CODE +' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,
	ATKT.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_KT,T1.TLFullName AS MAKER_NAME,T2.TLFullName AS MAKER_NAME_KT,T3.TLFullName APPROVE_NAME, T4.TLFullName APPROVE_NAME_KT,
	@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH
    , A.STATUS AS STATUS_XL
    , A.REQ_ID AS REQ_PARENT_ID, A. REQ_CODE AS REQ_PARENT_CODE,
    CASE WHEN C.BRANCH_TYPE = 'HS' THEN C.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE C.BRANCH_NAME END AS BRANCH_NAME
	-- SELECT END

	FROM ASS_TRANSFER_MULTI_MASTER A
	LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
    LEFT JOIN CM_DEPARTMENT CD ON A.DEPT_CREATE = CD.DEP_ID
	LEFT JOIN CM_AUTH_STATUS F ON A.AUTH_STATUS = F.AUTH_STATUS
	LEFT JOIN CM_AUTH_STATUS ATKT ON ATKT.AUTH_STATUS = A.AUTH_STATUS_KT
	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.TRANS_MULTI_MASTER_ID LIKE '%' + @p_TRANS_MULTI_MASTER_ID + '%' OR  @p_TRANS_MULTI_MASTER_ID IS NULL OR @p_TRANS_MULTI_MASTER_ID = '')

  -- TIM KIEM TU NGAY O HC
  AND	((@p_TYPE_SEARCH = 'HC' AND (CONVERT(DATE,A.TRANSFER_DT,103) >= CONVERT(DATE, @p_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))
    OR (@p_TYPE_SEARCH = 'KT' AND (A.STATUS = 'APPROVE') OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '') 
    OR(@p_TYPE_SEARCH = 'XN' AND (A.STATUS IN ('APPROVE','XNGN')) 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.TRANSFER_DT,103) <= CONVERT(DATE, @p_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = ''))
    OR (@p_TYPE_SEARCH = 'KT' AND (A.STATUS = 'APPROVE') OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '') 
    OR(@p_TYPE_SEARCH = 'XN' AND (A.STATUS IN ('APPROVE','XNGN')) 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_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))
    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_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))
          OR (@p_AUTH_STATUS_KT = 'U' AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))
          OR (@p_AUTH_STATUS_KT = 'R' AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))
          
          OR (@p_AUTH_STATUS_KT = 'E' AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))
          OR ((@p_AUTH_STATUS_KT = '' OR @p_AUTH_STATUS_KT IS NULL) AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))))))
  OR (@p_TYPE_SEARCH = 'HC' OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '') OR (@p_TYPE_SEARCH = 'XN' 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_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = ''))
    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_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = ''))
          OR (@p_AUTH_STATUS_KT = 'U' AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = ''))
          OR (@p_AUTH_STATUS_KT = 'R' AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = ''))
          OR (@p_AUTH_STATUS_KT = 'E' AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = ''))
          OR ((@p_AUTH_STATUS_KT = '' OR @p_AUTH_STATUS_KT IS NULL) AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = '')))
          )))
  OR (@p_TYPE_SEARCH = 'HC'  AND (EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV,GDDV_QLTS,TBP_QLTS,KSV_QLTS','DV0001','DEP000000000048') US
          WHERE US.TLNANME = @p_USER_LOGIN) OR A.MAKER_ID = @p_USER_LOGIN)
          OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '') OR (@p_TYPE_SEARCH = 'XN' OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = ''))
  AND (A.USER_TRANSFER LIKE '%' + @p_USER_TRANSFER + '%' OR  @p_USER_TRANSFER IS NULL OR @p_USER_TRANSFER = '')
  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 (A.CREATE_DT =CONVERT(DATETIME, @p_CREATE_DT, 103) 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 (A.APPROVE_DT =CONVERT(DATETIME, @p_APPROVE_DT, 103) OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
  AND (A.AUTH_STATUS_KT LIKE '%' + @p_AUTH_STATUS_KT + '%' OR  @p_AUTH_STATUS_KT IS NULL OR @p_AUTH_STATUS_KT = '')
  AND (A.CREATE_DT_KT =CONVERT(DATETIME, @p_CREATE_DT_KT, 103) OR  @p_CREATE_DT_KT IS NULL OR @p_CREATE_DT_KT = '')
  AND (A.APPROVE_DT_KT =CONVERT(DATETIME, @p_APPROVE_DT_KT, 103) OR  @p_APPROVE_DT_KT IS NULL OR @p_APPROVE_DT_KT = '')
  AND (A.MAKER_ID_KT LIKE '%' + @p_MAKER_ID_KT + '%' OR  @p_MAKER_ID_KT IS NULL OR @p_MAKER_ID_KT = '')
  AND (A.CHECKER_ID_KT LIKE '%' + @p_CHECKER_ID_KT + '%' OR  @p_CHECKER_ID_KT IS NULL OR @p_CHECKER_ID_KT = '')
  AND (A.REPORT_STATUS LIKE '%' + @p_REPORT_STATUS + '%' OR  @p_REPORT_STATUS IS NULL OR @p_REPORT_STATUS = '')
--  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 (@p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)))
  AND A.RECORD_STATUS='1'
  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 IS NULL OR @p_IS_UPDATE='')

  -- 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 (EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV,GDDV_QLTS,TBP_QLTS,KSV_QLTS','DV0001','DEP000000000048') US
          WHERE US.TLNANME = @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'
            --KHIEMCHG BỔ SUNG TÌM KIẾM THEO QUYỀN XỬ LÝ
            AND EXISTS(SELECT * FROM @TRANS_MULTI_MASTER_ID_Temp RL WHERE RL.TRANS_MASTER_ID = A.TRANS_MULTI_MASTER_ID)
            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) AND (A.STATUS = 'APPROVE'))
    	OR (@p_TYPE_SEARCH='KT' AND @p_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_ID AND (A.STATUS = 'APPROVE'))
        -- KHIEMCHG BỔ SUNG TÌM KIẾM THEO MÀN HÌNH XÁC NHẬN ĐIỀU CHUYỂN
        OR (@p_TYPE_SEARCH = 'XN'
            AND (EXISTS(SELECT * FROM @TRANS_MULTI_MASTER_ID_Temp RL WHERE RL.TRANS_MASTER_ID = A.TRANS_MULTI_MASTER_ID)
            AND (A.STATUS IN ('XNGN', 'APPROVE')))
            OR A.MAKER_ID = @p_USER_LOGIN
            )
        OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH =''
    )
   AND(A.REQ_CODE like N'%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
  
	ORDER BY A.CREATE_DT DESC
	-- PAGING END

ELSE
	-- PAGING BEGIN

		SELECT A.*,C.BRANCH_CODE, F.AUTH_STATUS_NAME,C.BRANCH_CODE +' - '+ C.BRANCH_NAME AS FULL_BRANCH_NAME,
	ATKT.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_KT,T1.TLFullName AS MAKER_NAME,T2.TLFullName AS MAKER_NAME_KT,T3.TLFullName APPROVE_NAME, T4.TLFullName APPROVE_NAME_KT,
	@p_USER_LOGIN AS USER_LOGIN,@p_TYPE_SEARCH AS TYPE_SEARCH
    , A.STATUS AS STATUS_XL
    , A.REQ_ID AS REQ_PARENT_ID, A. REQ_CODE AS REQ_PARENT_CODE,
    CASE WHEN C.BRANCH_TYPE = 'HS' THEN C.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE C.BRANCH_NAME END AS BRANCH_NAME
	-- SELECT END

	FROM ASS_TRANSFER_MULTI_MASTER A
	LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
    LEFT JOIN CM_DEPARTMENT CD ON A.DEPT_CREATE = CD.DEP_ID
	LEFT JOIN CM_AUTH_STATUS F ON A.AUTH_STATUS = F.AUTH_STATUS
	LEFT JOIN CM_AUTH_STATUS ATKT ON ATKT.AUTH_STATUS = A.AUTH_STATUS_KT
	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.TRANS_MULTI_MASTER_ID LIKE '%' + @p_TRANS_MULTI_MASTER_ID + '%' OR  @p_TRANS_MULTI_MASTER_ID IS NULL OR @p_TRANS_MULTI_MASTER_ID = '')

  -- TIM KIEM TU NGAY O HC
  AND	((@p_TYPE_SEARCH = 'HC' AND (CONVERT(DATE,A.TRANSFER_DT,103) >= CONVERT(DATE, @p_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))
    OR @p_TYPE_SEARCH = 'KT' AND (A.STATUS = 'APPROVE') 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.TRANSFER_DT,103) <= CONVERT(DATE, @p_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = ''))
    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_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))
    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_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))
          OR (@p_AUTH_STATUS_KT = 'U' AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))
          OR (@p_AUTH_STATUS_KT = 'R' AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))
          OR (@p_AUTH_STATUS_KT = 'E' AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))
          OR ((@p_AUTH_STATUS_KT = '' OR @p_AUTH_STATUS_KT IS NULL) AND (CONVERT(DATE,A.CREATE_DT_KT,103) >= CONVERT(DATE, @p_FromDate, 103) OR @p_FromDate is NULL OR @p_FromDate = ''))))))
  OR (@p_TYPE_SEARCH = 'HC' OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '') OR (@p_TYPE_SEARCH = 'XN' 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_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = ''))
    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_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = ''))
          OR (@p_AUTH_STATUS_KT = 'U' AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = ''))
          OR (@p_AUTH_STATUS_KT = 'R' AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = ''))
          OR (@p_AUTH_STATUS_KT = 'E' AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = ''))
          OR ((@p_AUTH_STATUS_KT = '' OR @p_AUTH_STATUS_KT IS NULL) AND (CONVERT(DATE,A.CREATE_DT_KT,103) <= CONVERT(DATE, @p_TODATE, 103) OR @p_TODATE is NULL OR @p_TODATE = '')))
          )))
  OR (@p_TYPE_SEARCH = 'HC'  AND (EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV,GDDV_QLTS,TBP_QLTS,KSV_QLTS','DV0001','DEP000000000048') US
          WHERE US.TLNANME = @p_USER_LOGIN) OR A.MAKER_ID = @p_USER_LOGIN)
          OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = '') OR (@p_TYPE_SEARCH = 'XN' OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH = ''))
  AND (A.USER_TRANSFER LIKE '%' + @p_USER_TRANSFER + '%' OR  @p_USER_TRANSFER IS NULL OR @p_USER_TRANSFER = '')
  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 (A.CREATE_DT =CONVERT(DATETIME, @p_CREATE_DT, 103) 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 (A.APPROVE_DT =CONVERT(DATETIME, @p_APPROVE_DT, 103) OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
  AND (A.AUTH_STATUS_KT LIKE '%' + @p_AUTH_STATUS_KT + '%' OR  @p_AUTH_STATUS_KT IS NULL OR @p_AUTH_STATUS_KT = '')
  AND (A.CREATE_DT_KT =CONVERT(DATETIME, @p_CREATE_DT_KT, 103) OR  @p_CREATE_DT_KT IS NULL OR @p_CREATE_DT_KT = '')
  AND (A.APPROVE_DT_KT =CONVERT(DATETIME, @p_APPROVE_DT_KT, 103) OR  @p_APPROVE_DT_KT IS NULL OR @p_APPROVE_DT_KT = '')
  AND (A.MAKER_ID_KT LIKE '%' + @p_MAKER_ID_KT + '%' OR  @p_MAKER_ID_KT IS NULL OR @p_MAKER_ID_KT = '')
  AND (A.CHECKER_ID_KT LIKE '%' + @p_CHECKER_ID_KT + '%' OR  @p_CHECKER_ID_KT IS NULL OR @p_CHECKER_ID_KT = '')
  AND (A.REPORT_STATUS LIKE '%' + @p_REPORT_STATUS + '%' OR  @p_REPORT_STATUS IS NULL OR @p_REPORT_STATUS = '')
--  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 (@p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)))
  AND A.RECORD_STATUS='1'
  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 IS NULL OR @p_IS_UPDATE='')

  -- 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 (EXISTS(SELECT * FROM dbo.FN_GET_USER_BY_ROLE('GDDV,GDDV_QLTS,TBP_QLTS,KSV_QLTS','DV0001','DEP000000000048') US
          WHERE US.TLNANME = @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'
            --KHIEMCHG BỔ SUNG TÌM KIẾM THEO QUYỀN XỬ LÝ
            AND EXISTS(SELECT * FROM @TRANS_MULTI_MASTER_ID_Temp RL WHERE RL.TRANS_MASTER_ID = A.TRANS_MULTI_MASTER_ID)
            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) AND (A.STATUS = 'APPROVE'))
    	OR (@p_TYPE_SEARCH='KT' AND @p_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_ID AND (A.STATUS = 'APPROVE'))
        -- KHIEMCHG BỔ SUNG TÌM KIẾM THEO MÀN HÌNH XÁC NHẬN ĐIỀU CHUYỂN
        OR (@p_TYPE_SEARCH = 'XN'
            AND (EXISTS(SELECT * FROM @TRANS_MULTI_MASTER_ID_Temp RL WHERE RL.TRANS_MASTER_ID = A.TRANS_MULTI_MASTER_ID)
            AND (A.STATUS IN ('XNGN', 'APPROVE')))
            OR A.MAKER_ID = @p_USER_LOGIN
            )
        OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH =''
    )
    AND(A.REQ_CODE like N'%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
	ORDER BY A.CREATE_DT DESC
	-- PAGING END

END -- PAGING