ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_Search]
@p_REQ_PAY_ID	varchar(15)= NULL,
@p_REQ_PAY_CODE	varchar(50)	= NULL,
@p_REQ_DT VARCHAR(20)= NULL,
@p_BRANCH_ID	varchar(15)	= NULL,
@p_DEP_ID	varchar(15)	= NULL,
@p_REQ_REASON	nvarchar(MAX)	= NULL,
@p_REQ_TYPE	varchar(15)	= NULL,
@P_REQ_ENTRIES nvarchar(MAX)	= NULL,
@p_REQ_DESCRIPTION	nvarchar(MAX)	= NULL,
@p_REF_ID	varchar(15)	= NULL,
@p_RECEIVER_PO	nvarchar(250)	= NULL,
@p_REQ_PAY_TYPE	varchar(15)	= NULL,
@p_REQ_TYPE_CURRENCY	nvarchar(50)	= NULL,
@p_REQ_AMT	decimal(18, 0)	= NULL,
@p_REQ_TEMP_AMT	decimal(18, 0)	= NULL,
@p_MAKER_ID	varchar(15)	= NULL,
@p_CREATE_DT	varchar(25)	= NULL,
@p_EDITOR_ID	varchar(15)	= NULL,
@p_AUTH_STATUS	varchar(1)	= NULL,
@p_CHECKER_ID	varchar(15)	= NULL,
@p_APPROVE_DT	varchar(25)	= NULL,
@p_CREATE_DT_KT	varchar(25)	= NULL,
@p_MAKER_ID_KT	varchar(15)	= NULL,
@p_AUTH_STATUS_KT	varchar(1)	= NULL,
@p_CHECKER_ID_KT	nvarchar(20)	= NULL,
@p_EXEC_USER_KT	nvarchar(20)	= NULL,
@p_APPROVE_DT_KT  varchar(25)= null,
@p_CONFIRM_NOTE	nvarchar(500)	= NULL,
@p_BRANCH_CREATE	varchar(15)	= NULL,
@p_NOTES	varchar(15)	= NULL,
@p_RECORD_STATUS	varchar(1)	= NULL,
@p_TRANSFER_MAKER	nvarchar(50)	= NULL,
@p_TRANSFER_DT	varchar(25)	= NULL,
@p_TRASFER_USER_RECIVE	varchar(15)	= NULL,
@p_PROCESS	varchar(15)	= NULL,
@p_PAY_PHASE VARCHAR(15) = NULL,
@p_TOP INT = 300,
@p_LEVEL varchar(10) = NULL,
@p_FRMDATE VARCHAR(20)= NULL,
@p_TODATE VARCHAR(20) = NULL,
@p_BRANCH_LOGIN VARCHAR(15) = NULL,
@p_IS_UPDATE_KT VARCHAR(15) = NULL,
@P_IS_TRANSFER VARCHAR(15) = NULL,
@p_TERM_ID VARCHAR(15) = NULL,
@P_USER_LOGIN VARCHAR(15)= NULL,
@p_FUNCTION VARCHAR(15) = NULL,
@p_IS_CREATE_AUTO VARCHAR(15) = NULL,
@p_TYPE_SEARCH VARCHAR(15) = NULL
AS
BEGIN -- PAGING
--SET @p_TOP = NULL
	IF(ISNULL(@p_REQ_PAY_ID, '') <> '')
	BEGIN
		SET @p_DEP_ID = NULL
	END

-- BRANCH TRUYEN TU UI
	DECLARE @tmp TABLE(BRANCH_ID VARCHAR(15))
	INSERT INTO @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)

-- BRANCH LOGIN
	DECLARE @tmp_login TABLE(BRANCH_ID varchar(15))
	DECLARE @MENU_PERMISSON NVARCHAR(500) = 'Pages.Administration.ReqPayment'
	IF(@p_TYPE_SEARCH = 'KT')
	BEGIN
	 SET @MENU_PERMISSON = 'Pages.Administration.ReqPaymentKT'
	END
	IF(@p_TYPE_SEARCH = 'KT' AND EXISTS(SELECT TLNAME 
										FROM SYS_PERMISSIONS_PAGE_FOR_USER 
										WHERE TLNAME = @p_USER_LOGIN 
										AND (DATEDIFF(DAY, CONVERT(DATE, EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR EffectiveDate IS NULL OR EffectiveDate = '')
										AND (DATEDIFF(DAY, CONVERT(DATE, ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR ExpirationDate IS NULL OR ExpirationDate = '')
										AND AUTH_STATUS = 'A' AND RECORD_STATUS = '1'
										AND RoleDisplayName IN ('KSV','KSV_DVKD', 'GDV', 'GDV_DVKD')))-- NẾU SEARCH KẾ TOÁN
	BEGIN
		INSERT INTO @tmp_login SELECT BRANCH_ID FROM [dbo].[FN_GET_BRANCH_USER_BY_TLNAME_ROLE_USER]('KSV,KSV_DVKD,GDV,GDV_DVKD', @p_USER_LOGIN, @MENU_PERMISSON)
	END
	ELSE
	BEGIN
		INSERT INTO @tmp_login  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
	END
-- USER LOGIN
	DECLARE @ROLE_ID VARCHAR(500) , @DEP_ID_LG VARCHAR(15) = NULL, @COST_LG VARCHAR(15), @DVDM_ID VARCHAR(15), @BRANCH_TYPE VARCHAR(15), @BRANCH_TYPE_LG VARCHAR(15)
	SET @BRANCH_TYPE_LG = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN)
	SET @DEP_ID_LG =(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)
	SET @COST_LG =(SELECT TOP 1 COST_ID FROM PL_COSTCENTER_DT WHERE DEP_ID =@DEP_ID_LG)
	SET @DVDM_ID =(SELECT TOP 1 DVDM_ID FROM PL_COSTCENTER WHERE COST_ID =@COST_LG)

-- TABLE ROLE USER
	DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))  

	INSERT INTO @TABLE_ROLE(ROLE_AUTH)
	SELECT C.DisplayName
	FROM TL_USER A
	LEFT JOIN AbpUserRoles B ON A.ID = B.UserId
	LEFT JOIN AbpRoles C ON B.RoleId = C.Id
	WHERE A.TLNANME = @p_USER_LOGIN
	--SET DANH SÁCH ROLE VÀO BIẾN CÁCH NHAU DẤU ,

	SET @ROLE_ID = (SELECT STRING_AGG(CONVERT(NVARCHAR (MAX), A.ROLE_AUTH), ',') FROM @TABLE_ROLE A) + ','

	INSERT INTO @TABLE_ROLE SELECT RoleDisplayName 
							FROM SYS_PERMISSIONS_PAGE_FOR_USER A 
							WHERE TLNAME = @P_USER_LOGIN  
							AND (DATEDIFF(DAY, CONVERT(DATE, EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR EffectiveDate IS NULL OR EffectiveDate = '')
							AND (DATEDIFF(DAY, CONVERT(DATE, ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR ExpirationDate IS NULL OR ExpirationDate = '')
							AND AUTH_STATUS = 'A' AND RECORD_STATUS = '1'
							AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = A.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
-- PHÒNG BAN ỦY QUYỀN KIÊM NHIỆM
	DECLARE @DEP_AUTH TABLE (DEP_AUTH VARCHAR(15))
	INSERT INTO @DEP_AUTH VALUES (@DEP_ID_LG)
	-- LUCTV 19.10.2022 BO SUNG THEM PHONG BAN CHA SE THAY DANH SACH PHONG BAN CON
	INSERT INTO @DEP_AUTH SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID = @DEP_ID_LG
	INSERT INTO @DEP_AUTH	SELECT DEP_ID 
							FROM SYS_PERMISSIONS_PAGE_FOR_USER A 
							WHERE TLNAME = @P_USER_LOGIN 
							AND (DATEDIFF(DAY, CONVERT(DATE, EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR EffectiveDate IS NULL OR EffectiveDate = '')
							AND (DATEDIFF(DAY, CONVERT(DATE, ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR ExpirationDate IS NULL OR ExpirationDate = '')
							AND AUTH_STATUS = 'A' AND RECORD_STATUS = '1'
							AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = A.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
-- PHÒNG BAN ỦY QUYỀN KIÊM NHIỆM CÓ QUYỀN DUYỆT PHIẾU
	DECLARE @DEP_IS_TDV TABLE (DEP_AUTH VARCHAR(15))
	INSERT INTO @DEP_IS_TDV	SELECT DEP_ID 
							FROM SYS_PERMISSIONS_PAGE_FOR_USER A 
							WHERE TLNAME = @P_USER_LOGIN 
							AND RoleDisplayName IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD')
							AND (DATEDIFF(DAY, CONVERT(DATE, EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR EffectiveDate IS NULL OR EffectiveDate = '')
							AND (DATEDIFF(DAY, CONVERT(DATE, ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR ExpirationDate IS NULL OR ExpirationDate = '')
							AND AUTH_STATUS = 'A' AND RECORD_STATUS = '1'
							AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = A.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)



-- ĐƠN VỊ ỦY QUYỀN KIÊM NHIỆM
	DECLARE @BRANCH_AUTH TABLE (BRN_AUTH VARCHAR(15))
	INSERT INTO @BRANCH_AUTH VALUES (@p_BRANCH_LOGIN)
	INSERT INTO @BRANCH_AUTH SELECT BRANCH_ID 
							 FROM SYS_PERMISSIONS_PAGE_FOR_USER A  
							 WHERE TLNAME = @P_USER_LOGIN 
							 AND (DATEDIFF(DAY, CONVERT(DATE, EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR EffectiveDate IS NULL OR EffectiveDate = '')
							 AND (DATEDIFF(DAY, CONVERT(DATE, ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR ExpirationDate IS NULL OR ExpirationDate = '')
							 AND AUTH_STATUS = 'A' AND RECORD_STATUS = '1'
							 AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = A.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
-- ĐƠN VỊ ỦY QUYỀN KIÊM NHIỆM CÓ QUYỀN DUYỆT
	DECLARE @BRANCH_IS_TDV TABLE (BRN_AUTH VARCHAR(15))
	IF(EXISTS(SELECT TOP 1 A.ROLE_NAME FROM TMP_ROLE_PAYMENT_SEARCH A WHERE CHARINDEX(A.ROLE_NAME + ',', @ROLE_ID) > 0))
	BEGIN
		INSERT INTO @BRANCH_IS_TDV(BRN_AUTH) VALUES (@p_BRANCH_LOGIN)
	END
	INSERT INTO @BRANCH_IS_TDV SELECT BRANCH_ID 
							   FROM SYS_PERMISSIONS_PAGE_FOR_USER A 
							   WHERE TLNAME = @P_USER_LOGIN 
							   AND RoleDisplayName IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD')
							   AND (DATEDIFF(DAY, CONVERT(DATE, EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR EffectiveDate IS NULL OR EffectiveDate = '')
							   AND (DATEDIFF(DAY, CONVERT(DATE, ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR ExpirationDate IS NULL OR ExpirationDate = '')
							   AND AUTH_STATUS = 'A' AND RECORD_STATUS = '1'
							   AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = A.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

--- NEU USER KHONG CHON TU NGAY THI TU NGAY BANG NGAY 1 1 HANG THANG 20211116
	DECLARE @DATE DATE
	IF ((@p_FRMDATE IS NULL OR @p_FRMDATE ='') AND ISNULL(@p_REQ_PAY_ID,'')='' AND @p_AUTH_STATUS ='') -- MỤC ĐÍCH NẾU NHƯ XEM CHI TIẾT 1 PĐN THANH TOÁN DẠNG POPUP THÌ BỎ QUA ĐIỀU KIỆN NÀY, TỪ NGÀY VẪN LÀ NULL
	BEGIN
		SET @DATE = CONVERT(DATE,GETDATE(),103)
		SET @DATE = DATEADD(MONTH,-2,@DATE)
	END
	ELSE
	BEGIN
		SET @DATE = CONVERT(DATE,@p_FRMDATE,103)
	END

--DOANPTT: XÁC ĐỊNH USER XEM TOÀN BỘ PHIẾU
	DECLARE @IS_VIEW_ALL VARCHAR(1) 
	IF((SELECT COUNT(*) FROM @TABLE_ROLE WHERE ROLE_AUTH IN ('KSV', 'GDV', 'NVTC')) > 0)		-- ROLE CHỈ ĐỊNH
	BEGIN
		SET @IS_VIEW_ALL = 'Y'
	END
	ELSE IF(@DEP_ID_LG = 'DEP000000000022')														-- PHÒNG CHỈ ĐỊNH
	BEGIN
		SET @IS_VIEW_ALL = 'Y'
	END
	ELSE IF(@P_USER_LOGIN = 'baotq')															-- USER CHỈ ĐỊNH
	BEGIN
		SET @IS_VIEW_ALL = 'Y'
	END
	ELSE
	BEGIN
		SET @IS_VIEW_ALL = 'N'
	END

--DOANPTT: XÁC ĐỊNH USER CÓ ĐƯỢC XEM HẾT PHIẾU CỦA PHÒNG MÌNH HAY KHÔNG
	DECLARE @IS_SEE_ALL_HC VARCHAR(1) 
	IF((SELECT COUNT(*) FROM PL_ROLE_DATA_CONFIG WHERE BRANCH_ID = @P_USER_LOGIN AND ROLE_TYPE = 'TR_REQ_PAYMENT') > 0)
	BEGIN
		SET @IS_SEE_ALL_HC = 'Y'
	END
	ELSE
	BEGIN
		SET @IS_SEE_ALL_HC = 'N'
	END

-- RoleName Rỗng sẽ cập nhật lại
	DECLARE @ROLE_ID_TL_USER_V2 VARCHAR(50)
	IF(@ROLE_ID IS NOT NULL AND @ROLE_ID <>'' AND EXISTS(SELECT TOP 1 A.ROLE_NAME FROM TMP_ROLE_PAYMENT_SEARCH A WHERE CHARINDEX(A.ROLE_NAME + ',', @ROLE_ID) > 0))
	BEGIN
		PRINT @ROLE_ID
	END
	ELSE
	BEGIN
		SET @ROLE_ID_TL_USER_V2 = (SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME = @p_USER_LOGIN)
		SET @ROLE_ID = @ROLE_ID + @ROLE_ID_TL_USER_V2 + ','
	END

	IF(@ROLE_ID_TL_USER_V2 IS NOT NULL AND @ROLE_ID_TL_USER_V2 <>'')
	BEGIN
		INSERT INTO @TABLE_ROLE VALUES (@ROLE_ID_TL_USER_V2)
	END

-- CHECK NGUOI DUYET
	DECLARE @IS_TDV VARCHAR(1) = 'N'
	IF	(		
				(	SELECT COUNT(*) 
					FROM @TABLE_ROLE A 
					WHERE A.ROLE_AUTH IN (SELECT B.ROLE_NAME FROM TMP_ROLE_PAYMENT_SEARCH B)
				) = 0
		)
	BEGIN
		SET @IS_TDV = 'N'
	END
	ELSE
	BEGIN
		SET @IS_TDV = 'Y'
	END

IF('NVTC_KT' IN (SELECT ROLE_AUTH FROM @TABLE_ROLE) AND @p_TYPE_SEARCH = 'KT')
BEGIN
	IF(@p_TOP IS NULL OR @p_TOP=0)
	BEGIN
	-- PAGING BEGIN
		SELECT A.*,B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME, C.AUTH_STATUS_KT_DESC,
		TL1.TLFullName APPROVE_FULLNAME,
		CASE WHEN ISNULL(TL2.TLFullName,'') <>'' THEN TL2.TLFullName ELSE TL8.TLFullName END AS CREATE_FULLNAME_KT,
		TL3.TLFullName APPROVE_FULLNAME_KT,
		CASE WHEN ISNULL(A.IS_PERIOD,'') <> '' AND A.IS_PERIOD ='Y' AND  EXISTS (SELECT TOP 1 X.* FROM TR_REQ_ADVANCE_DT X 
										INNER JOIN TR_CONTRACT CT ON X.REF_ID = CT.CONTRACT_ID
										WHERE X.REQ_PAY_ID = A.REQ_PAY_ID AND CT.BRANCH_ID <>  @p_BRANCH_LOGIN AND CT.BRANCH_ID IS NOT NULL AND CT.BRANCH_ID <> '') THEN 
										BR.BRANCH_NAME + ISNULL(' - '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT 
										WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
										ELSE BR.BRANCH_NAME END BRANCH_NAME_REQ,
		BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
		BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, ISNULL((A.REQ_AMT -H.SOTIEN_TT),0) AS TOTAL_AMT_TEMP, 
		ISNULL(H.SOTIEN_TT,0) TOTAL_AMT_PAY_HIS,
		TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,'' EMP_FULLNAME,PO.PO_CODE, PO.PO_NAME, S.SUP_NAME,S.TAX_NO SUP_TAX_NO,
		PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,
		@p_TYPE_SEARCH AS TYPE_SEARCH,SR.ROLE_ID AS ROLE_ID_CRE,CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR,
		BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE,'' AS BRANCH_CODE_CONTRACT,
		ISNULL((SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
		AS BRANCH_NAME_CONTRACT,
		--doanptt 300622
		CASE WHEN PC3.TLNAME IS NOT NULL THEN PC3.TLNAME 
				WHEN PC3.TLNAME <>'' THEN PC3.TLNAME 
				ELSE TL2.TLNANME END AS EXEC_USER,
		dbo.FN_DATEDIFF_WITHOUT_WEEKEND(A.TRANSFER_DT, GETDATE()) AS NUMBER_OF_TRANSFER,
		PC3.TLNAME AS EXEC_USER_KT,
		CASE WHEN ISNULL(A.CREATE_DT_KT, '') <> '' AND A.AUTH_STATUS_KT IN('P', 'S')  THEN dbo.FN_DATEDIFF_WITHOUT_WEEKEND(A.CREATE_DT_KT, GETDATE())
			ELSE 0 END AS NUMBER_OF_SEND_APPR,
		L.REQ_PAY_AUTO_ID, L.REQ_PAY_AUTO_CODE,
		dbo.FN_GET_NEXT_USER_PAYMENT(A.REQ_PAY_ID, A.MAKER_ID, A.BRANCH_ID, A.DEP_ID, A.AUTH_STATUS, A.AUTH_STATUS_KT, A.TRASFER_USER_RECIVE, A.PROCESS) AS NEXT_USER
	-- SELECT END
		FROM TR_REQ_PAYMENT A
			LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
			LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
			LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
			LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
			LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
			LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
			LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
			LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
			--Luanlt--2019/10/15-Sửa AL,AL1
			LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE' AND AL.CDTYPE = 'REQ'
			LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE' AND AL1.CDTYPE ='REQ'
			LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
			LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
			LEFT JOIN 
			(
			SELECT PAY_ADV_ID, SUM(AMT_PAY) SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
			) H ON A.REQ_PAY_ID =H.PAY_ADV_ID
			LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
			LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = PO.SUP_ID AND A.REQ_TYPE='P'
			LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C' 
			LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
			LEFT JOIN PL_REQUEST_PROCESS_CHILD PC5 ON PC5.REQ_ID = A.REQ_PAY_ID AND PC5.TYPE_JOB ='XL'
			LEFT JOIN TL_USER TL8 ON PC5.TLNAME = TL8.TLNANME
			LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
			LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID
			LEFT JOIN TR_REQ_PAY_AUTO_PAYMENTS K ON A.REQ_PAY_ID = K.REQ_PAY_ID
			LEFT JOIN TR_REQ_PAYMENT_AUTO L ON K.REQ_PAY_AUTO_ID = L.REQ_PAY_AUTO_ID
		WHERE 1=1 
			AND A.AUTH_STATUS = 'A'
			AND A.AUTH_STATUS_KT = 'A'
			AND (A.IS_CREATE_AUTO = @p_IS_CREATE_AUTO OR ISNULL(@p_IS_CREATE_AUTO, '') = '')
			AND(A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
			AND(A.REQ_PAY_ID = @p_REQ_PAY_ID OR ISNULL(@p_REQ_PAY_ID, '') = '')
			AND(A.REQ_PAY_CODE = @p_REQ_PAY_CODE OR ISNULL(@p_REQ_PAY_CODE, '') = '')
			AND(A.REQ_TYPE = @p_REQ_TYPE OR ISNULL(@p_REQ_TYPE, '') = '')
			AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @DATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='' OR A.CREATE_DT IS NULL)
			AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='' OR A.CREATE_DT IS NULL)
			AND(A.REQ_REASON LIKE '%' + @p_REQ_TYPE + '%' OR ISNULL(@p_REQ_TYPE, '') = '')
			AND(A.MAKER_ID =@p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
			AND(A.MAKER_ID_KT =@p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
			AND(A.DEP_ID=@p_DEP_ID OR @p_DEP_ID='' OR @p_DEP_ID IS NULL)
			AND	(	(	@p_LEVEL='ALL' AND	(	A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp) 
												OR A.BRANCH_CREATE IN (SELECT * FROM @BRANCH_AUTH)
											)
					)
					OR	(	(	@p_LEVEL='UNIT' 
								AND A.BRANCH_ID=@p_BRANCH_ID
							)
							OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL) 
							OR (	@BRANCH_TYPE_LG <> 'HS' AND
									EXISTS	(	SELECT * 
												FROM TR_REQ_ADVANCE_DT 
												WHERE REQ_PAY_ID = A.REQ_PAY_ID
												AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) 
												AND A.AUTH_STATUS_KT ='A'
											)
								)
						)
				)
				  
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
		END;
    ELSE 
	BEGIN
-- PAGING BEGIN
        SELECT TOP(CONVERT(INT,@p_TOP)) A.*,B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME, C.AUTH_STATUS_KT_DESC,
		TL1.TLFullName APPROVE_FULLNAME,
		CASE WHEN ISNULL(TL2.TLFullName,'') <>'' THEN TL2.TLFullName ELSE TL8.TLFullName END AS CREATE_FULLNAME_KT,
		TL3.TLFullName APPROVE_FULLNAME_KT,
		CASE WHEN ISNULL(A.IS_PERIOD,'') <> '' AND A.IS_PERIOD ='Y' AND  EXISTS (SELECT TOP 1 X.* FROM TR_REQ_ADVANCE_DT X 
										INNER JOIN TR_CONTRACT CT ON X.REF_ID = CT.CONTRACT_ID
										WHERE X.REQ_PAY_ID = A.REQ_PAY_ID AND CT.BRANCH_ID <>  @p_BRANCH_LOGIN AND CT.BRANCH_ID IS NOT NULL AND CT.BRANCH_ID <> '') THEN 
										BR.BRANCH_NAME + ISNULL(' - '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT 
										WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
										ELSE BR.BRANCH_NAME END BRANCH_NAME_REQ,
		BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
		BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, ISNULL((A.REQ_AMT -H.SOTIEN_TT),0) AS TOTAL_AMT_TEMP, 
		ISNULL(H.SOTIEN_TT,0) TOTAL_AMT_PAY_HIS,
		TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,'' EMP_FULLNAME,PO.PO_CODE, PO.PO_NAME, S.SUP_NAME,S.TAX_NO SUP_TAX_NO,
		PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,
		@p_TYPE_SEARCH AS TYPE_SEARCH,SR.ROLE_ID AS ROLE_ID_CRE,CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR,
		BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE,'' AS BRANCH_CODE_CONTRACT,
		ISNULL((SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
		AS BRANCH_NAME_CONTRACT,
		--doanptt 300622
		CASE WHEN PC3.TLNAME IS NOT NULL THEN PC3.TLNAME 
			 WHEN PC3.TLNAME <>'' THEN PC3.TLNAME 
			 ELSE TL2.TLNANME END AS EXEC_USER,
		dbo.FN_DATEDIFF_WITHOUT_WEEKEND(A.TRANSFER_DT, GETDATE()) AS NUMBER_OF_TRANSFER,
		PC3.TLNAME AS EXEC_USER_KT,
		CASE WHEN ISNULL(A.CREATE_DT_KT, '') <> '' AND A.AUTH_STATUS_KT IN('P', 'S')  THEN dbo.FN_DATEDIFF_WITHOUT_WEEKEND(A.CREATE_DT_KT, GETDATE())
			 ELSE 0 END AS NUMBER_OF_SEND_APPR,
		L.REQ_PAY_AUTO_ID, L.REQ_PAY_AUTO_CODE,
		dbo.FN_GET_NEXT_USER_PAYMENT(A.REQ_PAY_ID, A.MAKER_ID, A.BRANCH_ID, A.DEP_ID, A.AUTH_STATUS, A.AUTH_STATUS_KT, A.TRASFER_USER_RECIVE, A.PROCESS) AS NEXT_USER
-- SELECT END
        FROM TR_REQ_PAYMENT A
			LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
			LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
			LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
			LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
			LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
			LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
			LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
			LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
			--Luanlt--2019/10/15-Sửa AL,AL1
			LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE' AND AL.CDTYPE = 'REQ'
			LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE' AND AL1.CDTYPE = 'REQ'
			LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
			LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
			LEFT JOIN 
			(
			SELECT PAY_ADV_ID, SUM(AMT_PAY) SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
			) H ON A.REQ_PAY_ID =H.PAY_ADV_ID
			LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
			LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = PO.SUP_ID AND A.REQ_TYPE='P'
			LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C' 
			LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
			LEFT JOIN PL_REQUEST_PROCESS_CHILD PC5 ON PC5.REQ_ID = A.REQ_PAY_ID AND PC5.TYPE_JOB ='XL'
			LEFT JOIN TL_USER TL8 ON PC5.TLNAME = TL8.TLNANME
			LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
			LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID
			LEFT JOIN TR_REQ_PAY_AUTO_PAYMENTS K ON A.REQ_PAY_ID = K.REQ_PAY_ID
			LEFT JOIN TR_REQ_PAYMENT_AUTO L ON K.REQ_PAY_AUTO_ID = L.REQ_PAY_AUTO_ID
        WHERE 1=1  
			AND A.AUTH_STATUS = 'A'
			AND A.AUTH_STATUS_KT = 'A'
			AND (A.IS_CREATE_AUTO = @p_IS_CREATE_AUTO OR ISNULL(@p_IS_CREATE_AUTO, '') = '')
			AND(A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
			AND(A.REQ_PAY_ID = @p_REQ_PAY_ID OR ISNULL(@p_REQ_PAY_ID, '') = '')
			AND(A.REQ_PAY_CODE = @p_REQ_PAY_CODE OR ISNULL(@p_REQ_PAY_CODE, '') = '')
			AND(A.REQ_TYPE = @p_REQ_TYPE OR ISNULL(@p_REQ_TYPE, '') = '')
			AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @DATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='' OR A.CREATE_DT IS NULL)
			AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='' OR A.CREATE_DT IS NULL)
			AND(A.REQ_REASON LIKE '%' + @p_REQ_TYPE + '%' OR ISNULL(@p_REQ_TYPE, '') = '')
			AND(A.MAKER_ID =@p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
			AND(A.MAKER_ID_KT =@p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
			AND(A.DEP_ID=@p_DEP_ID OR @p_DEP_ID='' OR @p_DEP_ID IS NULL)
			AND	(	(	@p_LEVEL='ALL' AND	(	A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp) 
												OR A.BRANCH_CREATE IN (SELECT * FROM @BRANCH_AUTH)
											)
					)
					OR	(	(	@p_LEVEL='UNIT' 
								AND A.BRANCH_ID=@p_BRANCH_ID
							)
							OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL) 
							OR (	@BRANCH_TYPE_LG <> 'HS' AND
									EXISTS	(	SELECT * 
												FROM TR_REQ_ADVANCE_DT 
												WHERE REQ_PAY_ID = A.REQ_PAY_ID
												AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) 
												AND A.AUTH_STATUS_KT ='A'
											)
								)
						)
				)
		ORDER BY A.CREATE_DT DESC
-- PAGING END
    END;
END
ELSE
BEGIN
	IF(@p_TOP IS NULL OR @p_TOP=0)
	BEGIN
	-- PAGING BEGIN
			SELECT A.*,B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME, C.AUTH_STATUS_KT_DESC,
			TL1.TLFullName APPROVE_FULLNAME,
			CASE WHEN ISNULL(TL2.TLFullName,'') <>'' THEN TL2.TLFullName ELSE TL8.TLFullName END AS CREATE_FULLNAME_KT,
			TL3.TLFullName APPROVE_FULLNAME_KT,
			CASE WHEN ISNULL(A.IS_PERIOD,'') <> '' AND A.IS_PERIOD ='Y' AND  EXISTS (SELECT TOP 1 X.* FROM TR_REQ_ADVANCE_DT X 
											INNER JOIN TR_CONTRACT CT ON X.REF_ID = CT.CONTRACT_ID
											WHERE X.REQ_PAY_ID = A.REQ_PAY_ID AND CT.BRANCH_ID <>  @p_BRANCH_LOGIN AND CT.BRANCH_ID IS NOT NULL AND CT.BRANCH_ID <> '') THEN 
											BR.BRANCH_NAME + ISNULL(' - '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT 
											WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
											ELSE BR.BRANCH_NAME END BRANCH_NAME_REQ,
			--BR.BRANCH_NAME AS BRANCH_NAME_REQ,
			BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
			--Luanlt--2019/10/15-Sửa AL,AL1
			BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, ISNULL((A.REQ_AMT -H.SOTIEN_TT),0) AS TOTAL_AMT_TEMP, 
			ISNULL(H.SOTIEN_TT,0) TOTAL_AMT_PAY_HIS,
			TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,'' EMP_FULLNAME,PO.PO_CODE, PO.PO_NAME, S.SUP_NAME,S.TAX_NO SUP_TAX_NO,
			PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,
			--CASE WHEN PC3.TLNAME IS NOT NULL AND PC3.TLNAME <>'' THEN @P_USER_LOGIN ELSE PC3.TLNAME END AS EXEC_USER,
			@p_TYPE_SEARCH AS TYPE_SEARCH,SR.ROLE_ID AS ROLE_ID_CRE,CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR,
			BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE,'' AS BRANCH_CODE_CONTRACT,
			ISNULL((SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
			AS BRANCH_NAME_CONTRACT,
			--doanptt 300622
			CASE WHEN PC3.TLNAME IS NOT NULL THEN PC3.TLNAME 
				 WHEN PC3.TLNAME <>'' THEN PC3.TLNAME 
				 ELSE TL2.TLNANME END AS EXEC_USER,
			dbo.FN_DATEDIFF_WITHOUT_WEEKEND(A.TRANSFER_DT, GETDATE()) AS NUMBER_OF_TRANSFER,
			PC3.TLNAME AS EXEC_USER_KT,
			CASE WHEN ISNULL(A.CREATE_DT_KT, '') <> '' AND A.AUTH_STATUS_KT IN('P', 'S')  THEN dbo.FN_DATEDIFF_WITHOUT_WEEKEND(A.CREATE_DT_KT, GETDATE())
				 ELSE 0 END AS NUMBER_OF_SEND_APPR,
			L.REQ_PAY_AUTO_ID, L.REQ_PAY_AUTO_CODE,
		dbo.FN_GET_NEXT_USER_PAYMENT(A.REQ_PAY_ID, A.MAKER_ID, A.BRANCH_ID, A.DEP_ID, A.AUTH_STATUS, A.AUTH_STATUS_KT, A.TRASFER_USER_RECIVE, A.PROCESS) AS NEXT_USER
	-- SELECT END
			FROM TR_REQ_PAYMENT A
				LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
				LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
				LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
				LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
				LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
				LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
				LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
				LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
				LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
				--Luanlt--2019/10/15-Sửa AL,AL1
				LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE' AND AL.CDTYPE = 'REQ'
				LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE' AND AL1.CDTYPE ='REQ'
				LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
				LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
				LEFT JOIN 
				(
				SELECT PAY_ADV_ID, SUM(AMT_PAY) SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
				) H ON A.REQ_PAY_ID =H.PAY_ADV_ID
				LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
				LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = PO.SUP_ID AND A.REQ_TYPE='P'
				LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C' 
				LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
				LEFT JOIN PL_REQUEST_PROCESS_CHILD PC5 ON PC5.REQ_ID = A.REQ_PAY_ID AND PC5.TYPE_JOB ='XL'
				LEFT JOIN TL_USER TL8 ON PC5.TLNAME = TL8.TLNANME
				LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
				LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID
				LEFT JOIN TR_REQ_PAY_AUTO_PAYMENTS K ON A.REQ_PAY_ID = K.REQ_PAY_ID
				LEFT JOIN TR_REQ_PAYMENT_AUTO L ON K.REQ_PAY_AUTO_ID = L.REQ_PAY_AUTO_ID
			WHERE 1=1 
			-- BEGIN FILTER
				AND	(A.REQ_PAY_ID = @p_REQ_PAY_ID OR ISNULL(@p_REQ_PAY_ID, '') = '')
				AND	(A.MAKER_ID =@p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
				AND (A.IS_CREATE_AUTO = @p_IS_CREATE_AUTO OR ISNULL(@p_IS_CREATE_AUTO, '') = '')
				AND	(A.REF_ID =@p_REF_ID OR ISNULL(@p_REF_ID, '') = '')
				AND	(A.BRANCH_ID = @p_BRANCH_ID  OR ISNULL(@p_BRANCH_ID, '') = '' OR @p_BRANCH_ID = 'DV0001')
				AND	(A.DEP_ID = @p_DEP_ID  OR ISNULL(@p_DEP_ID, '') = '')
				AND	(ISNULL(@p_MAKER_ID_KT, '') = '' OR A.MAKER_ID_KT = @p_MAKER_ID_KT OR PC3.TLNAME = @p_MAKER_ID_KT)
				AND	(ISNULL(@p_EXEC_USER_KT, '') = '' OR ISNULL(PC3.TLNAME, '') = '' OR PC3.TLNAME = @p_EXEC_USER_KT)
				AND(PC4.TLNAME = @p_TRASFER_USER_RECIVE OR ISNULL(@p_TRASFER_USER_RECIVE, '') = '' OR A.MAKER_ID_KT =@p_TRASFER_USER_RECIVE)	
				AND	(	A.AUTH_STATUS = @p_AUTH_STATUS 
						OR @p_AUTH_STATUS = '' 
						OR @p_AUTH_STATUS IS NULL 
						OR	(	@p_AUTH_STATUS = 'W' 
								AND ISNULL(A.PROCESS, '') = '' 
								AND A.AUTH_STATUS = 'U'
								AND @P_USER_LOGIN = A.TRASFER_USER_RECIVE
							)-- CHỜ DUYỆT TRUNG GIAN
						OR	(	@p_AUTH_STATUS = 'G' 
								AND ISNULL(A.PROCESS, '') = '0' 
								AND A.AUTH_STATUS = 'U'
								AND @P_USER_LOGIN = A.TRASFER_USER_RECIVE
							) -- ĐÃ DUYỆT TRUNG GIAN
					)
				AND	(A.NOTES LIKE N'%'+@p_NOTES+'%' OR ISNULL(@p_NOTES, '') = '')
				AND	(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR ISNULL(@p_REQ_TYPE, '') = '')
				AND	(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' OR ISNULL(@p_REQ_PAY_CODE, '') = '')
				AND	(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' OR ISNULL(@p_REQ_REASON, '') = '')
				AND	(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @DATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='' OR A.CREATE_DT IS NULL)
				AND	(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='' OR A.CREATE_DT IS NULL)
				-- KT
				AND	(	
						(	A.MAKER_ID_KT IS NOT NULL 
							AND @p_IS_UPDATE_KT='Y'
						) 
						OR	(	A.MAKER_ID_KT IS NULL 
								AND @p_IS_UPDATE_KT='N'
							)
						OR  ISNULL(@p_IS_UPDATE_KT, '') = ''
					)	-- TINH TRANG CAP NHAT - GDV
				AND	(	A.AUTH_STATUS_KT=@p_AUTH_STATUS_KT 
						OR (@p_AUTH_STATUS_KT ='P' AND A.AUTH_STATUS_KT IN ('P','S')) 
						OR ISNULL(@p_AUTH_STATUS_KT, '') = ''
					)	-- TRANG THAI DUYET KT
				AND	(	(	@p_IS_TRANSFER='Y' 
							AND	(	EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID) 
									OR A.AUTH_STATUS_KT ='A'
								)
						)
						OR	(	(	@p_IS_TRANSFER='N' 
									AND (	NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID )) 
											AND A.AUTH_STATUS_KT <>'A'
								)
							)
						OR  ISNULL(@p_IS_TRANSFER, '') = ''
					)	-- TINH TRANG DIEU CHUYEN KT
				AND	(
						(
							@p_FUNCTION ='KT' 
							AND (
									EXISTS(SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD X WHERE REQ_ID = A.REQ_PAY_ID 
									AND (
											X.TLNAME= @p_USER_LOGIN 
											OR X.TLNAME =@p_EXEC_USER_KT)
										)
								) 
							OR	(A.MAKER_ID_KT = @p_MAKER_ID_KT OR PC3.TLNAME = @p_MAKER_ID_KT)
							OR A.CHECKER_ID_KT ='admin' 
							OR A.AUTH_STATUS_KT='A' 
						)
						OR ISNULL(@p_FUNCTION, '') = '' 
						OR @p_FUNCTION ='TF' 
					)
			-- END FILTER
				AND	(	(@p_BRANCH_ID <> @p_BRANCH_LOGIN AND A.BRANCH_ID = @p_BRANCH_ID) 
						OR @p_BRANCH_ID = @p_BRANCH_LOGIN 
						OR @p_BRANCH_ID='' 
						OR @p_BRANCH_ID IS NULL
					)
			-- VALIDATE BRANCH
				AND	(	A.BRANCH_ID = @p_BRANCH_LOGIN	-- PHIEU CUA DON VI MINH
						OR A.BRANCH_ID IN (SELECT * FROM @BRANCH_AUTH) -- PHIEU CUA DON VI KIEM NHIEM
						OR	(	@BRANCH_TYPE_LG <> 'HS' 
								AND EXISTS	(	SELECT * 
												FROM TR_REQ_ADVANCE_DT 
												WHERE REQ_PAY_ID = A.REQ_PAY_ID 
												AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) 
												AND A.AUTH_STATUS_KT ='A'
											)
							)	-- PHIEU CUA HOI SO TAO
					-- TRUONG HOP VIEW ALL
						OR @IS_VIEW_ALL = 'Y'
						OR A.MAKER_ID = @p_USER_LOGIN
					)
			-- VALIDATE DEP
				AND	(
						A.DEP_ID IN	(SELECT * FROM @DEP_AUTH)
						OR A.BRANCH_ID <> 'DV0001'
						OR	(	@BRANCH_TYPE_LG <> 'HS' 
								AND EXISTS	(	SELECT * 
												FROM TR_REQ_ADVANCE_DT 
												WHERE REQ_PAY_ID = A.REQ_PAY_ID 
												AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) 
												AND A.AUTH_STATUS_KT ='A'
											)
							)	-- PHIEU CUA HOI SO TAO
					-- TRUONG HOP VIEW ALL
						OR @IS_VIEW_ALL = 'Y'
						OR A.MAKER_ID = @p_USER_LOGIN	
					)
			-- VALIDATE FLOW
				AND	(
				-- TRUONG HOP VIEW ALL
						 @IS_VIEW_ALL = 'Y'	
				-- BEGIN TRUONG HOP DAC BIET
						OR	(	@IS_SEE_ALL_HC = 'Y'										-- DOANPTT 20/12/22: CHO USER XEM TOAN BO PHIEU THUOC PHONG HANH CHINH
								AND A.DEP_ID = @DEP_ID_LG
								AND A.BRANCH_ID = 'DV0001'
								AND A.AUTH_STATUS IN ('U','R', 'A')
								AND @p_TYPE_SEARCH ='HC'
							)
				-- END TRUONG HOP DAC BIET
						OR A.MAKER_ID = @p_USER_LOGIN									-- NGUOI TAO
						OR	(	A.AUTH_STATUS ='A')										-- PHIEU DA DUYET
						OR	(	A.TRASFER_USER_RECIVE = @P_USER_LOGIN					
								AND A.AUTH_STATUS NOT IN ('E','R')
							)															-- TRUNG GIAN													
						OR	(	@p_TYPE_SEARCH ='HC'									
								AND A.BRANCH_ID <> 'DV0001'							
								AND A.BRANCH_ID IN (SELECT * FROM @BRANCH_IS_TDV)
								AND (A.AUTH_STATUS = 'U' OR A.AUTH_STATUS = 'A')
								AND @IS_TDV = 'Y'
							)															-- TDV - DVKD
						OR	(	@p_TYPE_SEARCH ='HC'									
								AND A.BRANCH_ID = 'DV0001'							
								AND A.DEP_ID IN (SELECT * FROM @DEP_IS_TDV)
								AND (A.AUTH_STATUS = 'U' OR A.AUTH_STATUS = 'A')
								AND @IS_TDV = 'Y'
							)															-- TDV - HOI SO
						OR	(
								@p_TYPE_SEARCH ='HC'									
								AND @BRANCH_TYPE_LG <> 'HS' 
								AND EXISTS (
												SELECT * 
												FROM TR_REQ_ADVANCE_DT 
												WHERE REQ_PAY_ID = A.REQ_PAY_ID 
												AND REF_ID IN	(
																	SELECT CONTRACT_ID 
																	FROM TR_CONTRACT
																	WHERE BRANCH_ID =@p_BRANCH_LOGIN
																) 
												AND A.AUTH_STATUS_KT ='A'
											)
							)															-- LUCTV 25 05 BO SUNG CAU HINH CHO PHEP DVKD DUOC THAY NHUNG PDN THANH TOAN DO HO TẠO

						-- HOI SO
						OR	(	@p_TYPE_SEARCH ='HC'
								AND @BRANCH_TYPE_LG ='HS'  
								AND (	A.TRASFER_USER_RECIVE = @p_USER_LOGIN 
										OR	(	@IS_TDV = 'Y'
												AND (	A.BRANCH_ID = @p_BRANCH_LOGIN 
														OR A.BRANCH_ID IN (SELECT * FROM @BRANCH_AUTH)
													)
												AND (	A.DEP_ID = @DEP_ID_LG 
														OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH)
													)
												AND (	ISNULL(A.TRASFER_USER_RECIVE, '') = '' 
														OR	(	ISNULL(A.TRASFER_USER_RECIVE, '') <> '' 
																AND ISNULL(A.PROCESS, '') <> ''
															)
													)
											)
								)
							)
						-- CN
						OR	(	@p_TYPE_SEARCH ='HC' 
								AND @BRANCH_TYPE_LG = 'CN' 
								AND @IS_TDV = 'Y'
								AND A.BRANCH_ID IN (SELECT * FROM @BRANCH_IS_TDV)
							)
						-- PGD
						OR	(	@p_TYPE_SEARCH ='HC' 
								AND @BRANCH_TYPE_LG = 'PGD' 
								AND (
										(CHARINDEX('GDDV,', @ROLE_ID) > 0
										OR CHARINDEX('TPGD,', @ROLE_ID) > 0
										OR CHARINDEX('PPGD,', @ROLE_ID) > 0
										OR CHARINDEX('PGD,', @ROLE_ID) > 0
										)
										AND A.BRANCH_ID =@p_BRANCH_LOGIN
									)
							)
						-- KT
						OR	(	@p_TYPE_SEARCH='KT' 
								AND @p_BRANCH_LOGIN ='DV0001' 
								AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
							)
						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.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME, C.AUTH_STATUS_KT_DESC,
		TL1.TLFullName APPROVE_FULLNAME,
		CASE WHEN ISNULL(TL2.TLFullName,'') <>'' THEN TL2.TLFullName ELSE TL8.TLFullName END AS CREATE_FULLNAME_KT,
		TL3.TLFullName APPROVE_FULLNAME_KT,
		CASE WHEN ISNULL(A.IS_PERIOD,'') <> '' AND A.IS_PERIOD ='Y' AND  EXISTS (SELECT TOP 1 X.* FROM TR_REQ_ADVANCE_DT X 
										INNER JOIN TR_CONTRACT CT ON X.REF_ID = CT.CONTRACT_ID
										WHERE X.REQ_PAY_ID = A.REQ_PAY_ID AND CT.BRANCH_ID <>  @p_BRANCH_LOGIN AND CT.BRANCH_ID IS NOT NULL AND CT.BRANCH_ID <> '') THEN 
										BR.BRANCH_NAME + ISNULL(' - '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT 
										WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
										ELSE BR.BRANCH_NAME END BRANCH_NAME_REQ,
		--BR.BRANCH_NAME AS BRANCH_NAME_REQ,
		BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
		--Luanlt--2019/10/15-Sửa AL,AL1
		BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, ISNULL((A.REQ_AMT -H.SOTIEN_TT),0) AS TOTAL_AMT_TEMP, 
		ISNULL(H.SOTIEN_TT,0) TOTAL_AMT_PAY_HIS,
		TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,'' EMP_FULLNAME,PO.PO_CODE, PO.PO_NAME, S.SUP_NAME,S.TAX_NO SUP_TAX_NO,
		PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,
		--CASE WHEN PC3.TLNAME IS NOT NULL AND PC3.TLNAME <>'' THEN @P_USER_LOGIN ELSE PC3.TLNAME END AS EXEC_USER,
		@p_TYPE_SEARCH AS TYPE_SEARCH,SR.ROLE_ID AS ROLE_ID_CRE,CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR,
		BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE,'' AS BRANCH_CODE_CONTRACT,
		ISNULL((SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
		AS BRANCH_NAME_CONTRACT,
		--doanptt 300622
		CASE WHEN PC3.TLNAME IS NOT NULL THEN PC3.TLNAME 
			 WHEN PC3.TLNAME <>'' THEN PC3.TLNAME 
			 ELSE TL2.TLNANME END AS EXEC_USER,
		dbo.FN_DATEDIFF_WITHOUT_WEEKEND(A.TRANSFER_DT, GETDATE()) AS NUMBER_OF_TRANSFER,
		PC3.TLNAME AS EXEC_USER_KT,
		CASE WHEN ISNULL(A.CREATE_DT_KT, '') <> '' AND A.AUTH_STATUS_KT IN('P', 'S')  THEN dbo.FN_DATEDIFF_WITHOUT_WEEKEND(A.CREATE_DT_KT, GETDATE())
			 ELSE 0 END AS NUMBER_OF_SEND_APPR,
		L.REQ_PAY_AUTO_ID, L.REQ_PAY_AUTO_CODE,
		dbo.FN_GET_NEXT_USER_PAYMENT(A.REQ_PAY_ID, A.MAKER_ID, A.BRANCH_ID, A.DEP_ID, A.AUTH_STATUS, A.AUTH_STATUS_KT, A.TRASFER_USER_RECIVE, A.PROCESS) AS NEXT_USER
-- SELECT END
        FROM TR_REQ_PAYMENT A
             LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
             LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
             LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
             LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
             LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
             LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
             LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
			 LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
             LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
			 --Luanlt--2019/10/15-Sửa AL,AL1
			 LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE' AND AL.CDTYPE = 'REQ'
			 LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE' AND AL1.CDTYPE = 'REQ'
			 LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
			 LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
			 LEFT JOIN 
			 (
			 SELECT PAY_ADV_ID, SUM(AMT_PAY) SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
			 ) H ON A.REQ_PAY_ID =H.PAY_ADV_ID
			 LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
			 LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = PO.SUP_ID AND A.REQ_TYPE='P'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C' 
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC5 ON PC5.REQ_ID = A.REQ_PAY_ID AND PC5.TYPE_JOB ='XL'
			 LEFT JOIN TL_USER TL8 ON PC5.TLNAME = TL8.TLNANME
			 LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
			 LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID
			LEFT JOIN TR_REQ_PAY_AUTO_PAYMENTS K ON A.REQ_PAY_ID = K.REQ_PAY_ID
			LEFT JOIN TR_REQ_PAYMENT_AUTO L ON K.REQ_PAY_AUTO_ID = L.REQ_PAY_AUTO_ID
        WHERE 1=1 
			  AND(A.REQ_PAY_ID =@p_REQ_PAY_ID OR @p_REQ_PAY_ID='' OR @p_REQ_PAY_ID IS NULL)
			  AND (A.IS_CREATE_AUTO = @p_IS_CREATE_AUTO OR ISNULL(@p_IS_CREATE_AUTO, '') = '')
			  AND(	A.AUTH_STATUS=@p_AUTH_STATUS 
					OR @p_AUTH_STATUS='' 
					OR @p_AUTH_STATUS IS NULL 
					OR	(	@p_AUTH_STATUS = 'G' 
							AND ISNULL(A.PROCESS, '') = '0' 
							AND A.AUTH_STATUS = 'U'
							AND @P_USER_LOGIN = A.TRASFER_USER_RECIVE
						) 
					OR	(	@p_AUTH_STATUS = 'W' 
							AND ISNULL(A.PROCESS, '') = '' 
							AND A.AUTH_STATUS = 'U'
							AND @P_USER_LOGIN = A.TRASFER_USER_RECIVE
						)
				)
			  AND(A.MAKER_ID =@p_MAKER_ID OR @p_MAKER_ID='' OR @p_MAKER_ID IS NULL)
			  AND(A.MAKER_ID_KT = @p_MAKER_ID_KT OR @p_MAKER_ID_KT = '' OR @p_MAKER_ID_KT IS NULL OR PC3.TLNAME = @p_MAKER_ID_KT)
			  AND(PC3.TLNAME = @p_EXEC_USER_KT OR @p_EXEC_USER_KT = '' OR @p_EXEC_USER_KT IS NULL OR PC3.TLNAME = '' OR PC3.TLNAME IS NULL)
			  AND(A.DEP_ID=@p_DEP_ID OR @p_DEP_ID='' OR @p_DEP_ID IS NULL)
			  AND	(	(	@p_LEVEL='ALL' AND	(	A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp) 
													OR A.BRANCH_CREATE IN (SELECT * FROM @BRANCH_AUTH)
												)
						) -- LUCTV 20211012: BO SUNG CAU HINH DE XU LY UY QUYEN KIEM NHIEM
						OR	(	(	@p_LEVEL='UNIT' 
									AND A.BRANCH_ID=@p_BRANCH_ID
								)
								OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL) 
								OR (	@BRANCH_TYPE_LG <> 'HS' AND
										EXISTS	(	SELECT * 
													FROM TR_REQ_ADVANCE_DT 
													WHERE REQ_PAY_ID = A.REQ_PAY_ID
													AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) 
													AND A.AUTH_STATUS_KT ='A'
												)
									)
							)
					)
			  AND	(	(	@p_LEVEL='ALL' 
							AND (	A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp) 
									OR A.BRANCH_CREATE IN (SELECT * FROM @BRANCH_AUTH)
								)
						) -- - LUCTV 20211012: BO SUNG CAU HINH DE XU LY UY QUYEN KIEM NHIEM
						OR	(	(	@p_LEVEL='UNIT' 
									AND A.BRANCH_ID=@p_BRANCH_ID
								)
								OR	(	@p_BRANCH_ID='' 
										OR @p_BRANCH_ID IS NULL
									)
																 OR	(	@BRANCH_TYPE_LG <> 'HS' 
																		AND EXISTS	(	SELECT * 
																						FROM TR_REQ_ADVANCE_DT 
																						WHERE REQ_PAY_ID = A.REQ_PAY_ID 
																						AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) 
																						AND A.AUTH_STATUS_KT ='A'
																					)
																	)
							)
					)
			  AND	(	(	A.MAKER_ID_KT IS NOT NULL 
							AND @p_IS_UPDATE_KT='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_KT='N'
								)
							)
							OR @p_IS_UPDATE_KT IS NULL 
							OR @p_IS_UPDATE_KT='')
			  AND(A.NOTES LIKE N'%'+@p_NOTES+'%' OR @p_NOTES='' OR @p_NOTES IS NULL)
			  --Luanlt-2019/10/14-Thêm Filter  REQ_TYPE,FROMDATE TODATE REF_ID
			  AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
			  AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @DATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='' OR A.CREATE_DT IS NULL)
              AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='' OR A.CREATE_DT IS NULL)
			  --Luanlt-2019/10/17-Thêm Filter REQ_PAY_CODE REQ_PAY_NAME  BRANCH_ID DEP_ID KT_AUTH
			  AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
			  AND(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' or @p_REQ_REASON='' OR @p_REQ_REASON IS NULL)
			  --AND(A.TRASFER_USER_RECIVE =@p_TRASFER_USER_RECIVE or @p_TRASFER_USER_RECIVE='' OR @p_TRASFER_USER_RECIVE IS NULL OR A.MAKER_ID_KT IS NOT NULL OR A.MAKER_ID_KT <>'')
			  AND	(	(@p_BRANCH_ID <> @p_BRANCH_LOGIN AND A.BRANCH_ID = @p_BRANCH_ID) 
						OR @p_BRANCH_ID = @p_BRANCH_LOGIN 
						OR @p_BRANCH_ID='' 
						OR @p_BRANCH_ID IS NULL
					)
			  AND(A.DEP_ID = @p_DEP_ID  or @p_DEP_ID='' OR @p_DEP_ID IS NULL)
			  AND	(	(	A.AUTH_STATUS_KT=@p_AUTH_STATUS_KT 
							OR @p_AUTH_STATUS_KT='' 
							OR @p_AUTH_STATUS_KT IS NULL
						) 
						OR (@p_AUTH_STATUS_KT ='P' AND A.AUTH_STATUS_KT IN ('P','S'))
					)
			  AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
			  --AND((A.TRANSFER_MAKER IS NOT NULL AND @p_IS_TRANSFER='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
              --OR((A.TRANSFER_MAKER IS NULL AND @p_IS_TRANSFER='N'))OR @p_IS_TRANSFER IS NULL OR @p_IS_TRANSFER='')
			  -- Neu ben phan he ke toan thi chi lay nhung user co maker_id khac null
			  AND  (
						(
							@p_FUNCTION ='KT' 
							AND (
									EXISTS(SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD X WHERE REQ_ID = A.REQ_PAY_ID 
									AND (
											X.TLNAME= @p_USER_LOGIN 
											OR X.TLNAME =@p_EXEC_USER_KT)
										)
								) 
							OR	(A.MAKER_ID_KT = @p_MAKER_ID_KT OR PC3.TLNAME = @p_MAKER_ID_KT)
							OR A.CHECKER_ID_KT ='admin' 
							OR A.AUTH_STATUS_KT='A' 
						)
						OR @p_FUNCTION ='' 
						OR @p_FUNCTION IS NULL 
						OR @p_FUNCTION ='TF' 
					)	  
				AND(PC4.TLNAME = @p_TRASFER_USER_RECIVE OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='' OR A.MAKER_ID_KT =@p_TRASFER_USER_RECIVE)
			 -- AND ((@p_FUNCTION ='KT' AND A.MAKER_ID_KT IS NOT NULL AND A.MAKER_ID_KT ='') OR @p_FUNCTION IS NULL OR @p_FUNCTION ='')
				AND	(	(	@p_IS_TRANSFER='Y' 
							AND	(	EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID) 
									OR A.AUTH_STATUS_KT ='A'
								)
						) --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
						OR	(	(	@p_IS_TRANSFER='N' 
									AND (	NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID )) 
											AND A.AUTH_STATUS_KT <>'A'
								)
							)
						OR @p_IS_TRANSFER IS NULL 
						OR @p_IS_TRANSFER=''
					)	
				AND
				( /*0*/
				  A.MAKER_ID =@p_USER_LOGIN  
				  OR ( A.TRASFER_USER_RECIVE = @P_USER_LOGIN 
						AND A.AUTH_STATUS NOT IN ('E','R')
					 )
				  OR	(	(	@IS_SEE_ALL_HC = 'Y'			--DOANPTT 20/12/22: CHO A XUAN XEM TOAN BO PHIEU THUOC PHONG HANH CHINH
								AND A.DEP_ID = @DEP_ID_LG
								AND A.BRANCH_ID = 'DV0001'
								AND A.AUTH_STATUS IN ('U','R', 'A')
								AND @p_TYPE_SEARCH ='HC'
							)
						)
				  OR (@P_USER_LOGIN = 'baotq')					-- DOANPTT 08/05/2023: CHO A BẢO XEM TẤT CẢ PHIẾU CỦA HỆ THỐNG
				  OR (	A.AUTH_STATUS <>'E' /*1*/
						AND
						(/*2*/
							(	@p_TYPE_SEARCH ='HC'		/*3*/ 
								AND @BRANCH_TYPE_LG ='HS'  
								AND (/*4*/A.TRASFER_USER_RECIVE = @p_USER_LOGIN 
										OR (/*5*/
												EXISTS (	SELECT * 
															FROM @TABLE_ROLE 
															WHERE ROLE_AUTH IN ('GDDV','GDK','KTT','TPTC','TP','PP','TC')
													   )
												AND    (
															(	A.BRANCH_ID =@p_BRANCH_LOGIN 
																OR A.BRANCH_ID IN (SELECT * FROM @BRANCH_AUTH)
															) 
															AND (	A.DEP_ID = @DEP_ID_LG 
																	OR  ( 
																			A.DEP_ID  IN ('DEP000000000051','DEP000000000733') 
																			AND @p_USER_LOGIN ='cuongpv2'
																		) 
																	OR A.DEP_ID IN (
																						SELECT * 
																						FROM @DEP_AUTH
																					)
																)
														)
												AND ( 
														A.TRASFER_USER_RECIVE IS NULL 
														OR A.TRASFER_USER_RECIVE ='' 
														OR (
																A.TRASFER_USER_RECIVE IS NOT NULL 
																AND A.TRASFER_USER_RECIVE <>'' 
																AND A.PROCESS IS NOT NULL 
																AND A.PROCESS <>''
															)
													)
											)/*5*/
										)/*4*/
								)/*3*/
							OR(/*trong 2*/
									@p_TYPE_SEARCH ='HC' 
									AND @BRANCH_TYPE_LG = 'CN' 
									AND (
											(CHARINDEX('GDDV,', @ROLE_ID) > 0
											OR CHARINDEX('TPGD,', @ROLE_ID) > 0
											OR CHARINDEX('PPGD,', @ROLE_ID) > 0
											OR CHARINDEX('PGD,', @ROLE_ID) > 0
											)
											AND A.BRANCH_ID =@p_BRANCH_LOGIN 
											AND
												(
													(
														A.TRASFER_USER_RECIVE IS NOT NULL 
														AND A.TRASFER_USER_RECIVE <>'' 
														AND A.PROCESS IS NOT NULL 
														AND A.PROCESS <>''
													 ) 
													OR A.TRASFER_USER_RECIVE ='' 
													OR A.TRASFER_USER_RECIVE IS NULL
												 )
											OR (
													(
														A.BRANCH_CREATE <> @p_BRANCH_LOGIN 
														AND A.TRASFER_USER_RECIVE IS NOT NULL 
															AND A.TRASFER_USER_RECIVE <>''
													) 
													OR A.BRANCH_CREATE = @p_BRANCH_LOGIN
												)
											AND A.BRANCH_ID =@p_BRANCH_ID
										)
								)
							 OR
								(
									@p_TYPE_SEARCH ='HC' 
									AND @BRANCH_TYPE_LG = 'PGD' 
									AND (
											(CHARINDEX('GDDV,', @ROLE_ID) > 0
											OR CHARINDEX('TPGD,', @ROLE_ID) > 0
											OR CHARINDEX('PPGD,', @ROLE_ID) > 0
											OR CHARINDEX('PGD,', @ROLE_ID) > 0
											)
											AND A.BRANCH_ID =@p_BRANCH_LOGIN
										)
								)
							 OR
								(
									@p_TYPE_SEARCH ='HC' 
									AND(
											(CHARINDEX('KSV,', @ROLE_ID) > 0
											OR CHARINDEX('GDV,', @ROLE_ID) > 0
											OR CHARINDEX('NVTC,', @ROLE_ID) > 0
											)
											OR @DEP_ID_LG ='DEP000000000022'
										) 
									AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
								)
							OR	(
									@p_TYPE_SEARCH='KT' 
									AND @p_BRANCH_LOGIN ='DV0001' 
									AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
								)
							OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH =''
						)/*2*/
				)/*1*/
				  OR
				  (
						(
							(CHARINDEX('KSV,', @ROLE_ID) > 0
							OR CHARINDEX('GDV,', @ROLE_ID) > 0
							OR CHARINDEX('NVTC,', @ROLE_ID) > 0
							)
							OR @DEP_ID_LG ='DEP000000000022'
						) 
						AND @p_BRANCH_LOGIN ='DV0001' 
						AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
				  )
				  -- NEU DUOC UY QUYEN DUYỆT CHO 1 ĐƠN VỊ KHÁC HỘI SỞ THÌ CHỈ SETUP ĐƠN VỊ, KHÔNG CẦN SETUP PHÒNG BAN
				  OR(	@p_TYPE_SEARCH ='HC' 
						AND A.BRANCH_CREATE <> 'DV0001' 
						AND A.BRANCH_ID IN (SELECT * FROM @BRANCH_AUTH)
					)
				  -- LUCTV 25 05 BO SUNG CAU HINH CHO PHEP DVKD DUOC THAY NHUNG PDN THANH TOAN DO HO TẠO
				  OR (
						@p_TYPE_SEARCH ='HC' 
						AND @BRANCH_TYPE_LG <> 'HS' 
						AND
						EXISTS (
									SELECT * 
									FROM TR_REQ_ADVANCE_DT 
									WHERE REQ_PAY_ID = A.REQ_PAY_ID AND REF_ID IN (
																					SELECT CONTRACT_ID 
																					FROM TR_CONTRACT
																					WHERE BRANCH_ID =@p_BRANCH_LOGIN
																				  ) 
									AND A.AUTH_STATUS_KT ='A'
								)
					)
			 )/*0*/
		ORDER BY A.CREATE_DT DESC
-- PAGING END
    END;
END

END -- PAGING