ALTER PROCEDURE [dbo].[TR_REQ_ADVANCE_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_RECEIVER_DEBIT		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		VARCHAR(1) = NULL,
@p_EXEC_USER_KT			NVARCHAR(20)	= NULL,
@p_APPROVE_DT_KT		VARCHAR(25)= NULL,  
@p_CORE_NOTE			NVARCHAR(500) = NULL,  
@p_BRANCH_CREATE		VARCHAR(15) = NULL,  
@p_NOTES				NVARCHAR(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_XMP_TEMP				XML = NULL,  
@p_TOP					INT = 10,  
@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_DVDM_ID				VARCHAR(15)= NULL,  
@p_USER_LOGIN			VARCHAR(15) = NULL,  
@p_RATE					DECIMAL(18,0) =0,  
@p_FUNCTION				VARCHAR(15) = NULL,  
@p_TYPE_SEARCH			VARCHAR(15) = NULL,  
@p_TEMP_PAY_STATUS		VARCHAR(10) = NULL  
--select * from CM_ALLCODE where CDTYPE = 'TEMPPAYSTATUS'  
--HT Hoàn tất  
--DCHU Đang chờ hoàn ứng  
--DHU Đã hoàn ứng  
--'' tất cả  
AS
BEGIN -- PAGING
	DECLARE @MENU_PERMISSON NVARCHAR(500) = 'Pages.Administration.ReqTempPayment'
	IF(@p_TYPE_SEARCH = 'KT' AND @p_FUNCTION = 'TF')
	BEGIN
		SET @MENU_PERMISSON = 'Pages.Administration.ReqTempPayTransfer'
	END
	ELSE IF(@p_TYPE_SEARCH = 'KT' AND @p_FUNCTION = 'KT')
	BEGIN
		SET @MENU_PERMISSON = 'Pages.Administration.ReqTempPaymentKT'
	END

	SET @p_TOP = NULL  
	IF(ISNULL(@p_BRANCH_ID, '') = '')
	BEGIN
		SET @p_BRANCH_ID = @p_BRANCH_LOGIN
	END
	IF(ISNULL(@p_BRANCH_CREATE, '') = '')
	BEGIN
		SET @p_BRANCH_CREATE = @p_BRANCH_LOGIN
	END
-- BRANCH TYPE CUA USER LOGIN
	DECLARE @TB_TYPE TABLE (BRANCH_TYPE VARCHAR(15))  
	IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID= @p_BRANCH_LOGIN)='HS')  
	BEGIN  
		INSERT INTO @TB_TYPE VALUES('HS')  
	END  
	ELSE  
	BEGIN  
		INSERT INTO @TB_TYPE VALUES('PGD')  
		INSERT INTO @TB_TYPE VALUES('CN')  
	END  

-- DANH SACH PHONG BAN CON CUA NGUOI TAO PHIEU
	IF(NOT EXISTS (SELECT * FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID))
	BEGIN
		SET @p_BRANCH_ID = (SELECT TOP 1 BRANCH_ID FROM CM_BRANCH WHERE BRANCH_CODE = @p_BRANCH_ID)
	END
	DECLARE @tmp TABLE(BRANCH_ID VARCHAR(15))  
	INSERT INTO @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)  
-- DANH SACH PHONG BAN CON CUA USER LOGIN
	declare @tmp_Login table(BRANCH_ID varchar(15))  
	insert into @tmp_Login SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)  

	DECLARE @ROLE_ID VARCHAR(20) , @DEP_ID_LG VARCHAR(15) = NULL, @DEP_ID VARCHAR(15) = NULL, @COST_LG VARCHAR(15), @DVDM_ID VARCHAR(15), @BRANCH_TYPE VARCHAR(15) , @KHOI_ID_LG VARCHAR(15) = NULL, @KHOI_ID VARCHAR(15) = NULL
-- BRANCH TYPE CUA PHIEU DE NGHI TAM UNG
	SET @BRANCH_TYPE =(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID))  
-- ROLE CUA USER LOGIN

-- KHAI BAO UY QUYEN KIEM NHIEM
	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 @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 A.TLNAME = @p_USER_LOGIN
							AND (DATEDIFF(DAY, CONVERT(DATE, A.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR A.EffectiveDate IS NULL OR A.EffectiveDate = '')
							AND (DATEDIFF(DAY, CONVERT(DATE, A.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR A.ExpirationDate IS NULL OR A.ExpirationDate = '')
							AND A.AUTH_STATUS = 'A' AND A.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)

	--AND CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)  

-- PHONG BAN CUA USER LOGIN
	SET @DEP_ID_LG =(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)  
	SET @DEP_ID =(SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID)  
	SET @KHOI_ID_LG =(SELECT KHOI_ID FROM CM_DEPARTMENT WHERE DEP_ID=@DEP_ID_LG) 
	SET @KHOI_ID =(SELECT KHOI_ID FROM CM_DEPARTMENT WHERE DEP_ID	= (SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID))
	
	DECLARE @TMP_DEP TABLE(DEP_ID VARCHAR(15))  
	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)  
	INSERT INTO @TMP_DEP SELECT B.DEP_ID FROM PL_COSTCENTER_DT B WHERE COST_ID =@COST_LG  
-- BRANCH_TYPE USER LOGIN
	DECLARE @BRANCH_TYPE_LG VARCHAR(15)  
	SET @BRANCH_TYPE_LG = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN)  
-- KHAI BAO NHUNG PHONG BAN MA 1 USER KIEM NHIEM  
	DECLARE @DEP_AUTH TABLE (DEP_AUTH VARCHAR(15))  
	INSERT INTO @DEP_AUTH VALUES (@DEP_ID_LG)
	INSERT INTO @DEP_AUTH SELECT DEP_ID 
						  FROM SYS_PERMISSIONS_PAGE_FOR_USER A 
						  WHERE A.TLNAME = @P_USER_LOGIN  
						  AND (DATEDIFF(DAY, CONVERT(DATE, A.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR A.EffectiveDate IS NULL OR A.EffectiveDate = '')
						  AND (DATEDIFF(DAY, CONVERT(DATE, A.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR A.ExpirationDate IS NULL OR A.ExpirationDate = '')
						  AND A.AUTH_STATUS = 'A' AND A.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)

	DECLARE @BRANCH_AUTH TABLE (BRN_AUTH VARCHAR(15))
	INSERT INTO @BRANCH_AUTH VALUES (@BRANCH_TYPE_LG)
	INSERT INTO @BRANCH_AUTH SELECT BRANCH_ID 
							 FROM SYS_PERMISSIONS_PAGE_FOR_USER A 
							 WHERE A.TLNAME = @P_USER_LOGIN 
							 AND (DATEDIFF(DAY, CONVERT(DATE, A.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR A.EffectiveDate IS NULL OR A.EffectiveDate = '')
							 AND (DATEDIFF(DAY, CONVERT(DATE, A.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR A.ExpirationDate IS NULL OR A.ExpirationDate = '')
							 AND A.AUTH_STATUS = 'A' AND A.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)

-- LAY ROLE USER LOGIN
	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_ADVANCE_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(1=1)
	--begin
	--print 'role: ' + @ROLE_ID
	--return;
	--end
	IF(@p_TOP IS NULL OR @p_TOP=0)
	BEGIN
-- PAGING BEGIN 
		SELECT A.*, A.CONFIRM_NOTES AS CONFIRM_NOTE, '' as TEMP_PAY_STATUS ,ISNULL(B.AUTH_STATUS_NAME,N'Chờ duyệt') AS AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME,  
		TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName 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, DP.DEP_NAME AS DEP_NAME, DP.DEP_CODE AS DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,  
		--Luanlt--2019/10/15-Sửa AL,AL1  
		BR1.BRANCH_CODE BRANCH_CODE_CRE, BR1.BRANCH_NAME AS BRANCH_NAME_CRE , AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME,  
		A.REQ_AMT AS TOTAL_AMT_TEMP,CASE WHEN H.SOTIEN_TT IS NULL THEN ISNULL(A.PAY_AMT,0) ELSE ISNULL(A.PAY_AMT,0) END AS TOTAL_AMT_PAY_HIS,  
		TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,  
		TL6.TLFullName AS REC_DEBIT_NAME, TL6.TLNANME AS REC_DEBIT_CODE, TL7.TLFullName AS EMP_NAME, TL7.TLNANME AS EMP_CODE,  
		PO.PO_CODE, PO.PO_NAME,SUP.SUP_NAME,SUP.TAX_NO AS SUP_TAX_NO,CUS.CUSTOMER_CODE AS CUST_CODE, CUS.CUSTOMER_NAME AS CUST_NAME,PM.PAY_PHASE AS KY_TAM_UNG,  
		DV.DVDM_CODE, DV.DVDM_NAME, CASE WHEN PC.ROLE_USER ='GDDV' THEN (SELECT RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN) ELSE PC.ROLE_USER END AS ROLE_CONF, 
		--PR.PROCESS_DESC +N' vào lúc '+ CONVERT(VARCHAR(30), CONVERT(DATETIME, PR.APP_DT,103)) AS CONF_STATUS, 
		PR.PROCESS_DESC +N' vào lúc '+ ISNULL(FORMAT(PR.APP_DT,'dd/MM/yyyy H:mm:ss'),'') AS CONF_STATUS,
		--PC1.NOTES AS NEXT_STEP,  
		---2021112 CAU HINH BUOC XU LY TIEP THEO PDN TAM UNG NOI BO
		CASE WHEN A.REQ_TYPE='I' AND A.AUTH_STATUS ='E' THEN N'Phiếu đang chờ gửi phê duyệt' 
		WHEN A.REQ_TYPE='I' AND A.AUTH_STATUS_KT ='R' THEN N'Phiếu đang chờ GDV xử lý'
		WHEN A.REQ_TYPE='I' AND A.AUTH_STATUS_KT ='P' THEN N'Phiếu đang chờ KSV xử lý'
		WHEN A.REQ_TYPE='I' AND A.AUTH_STATUS ='R' THEN N'Phiếu bị trả về đơn vị' 
		WHEN A.REQ_TYPE='I' AND A.BRANCH_CREATE='DV0001' AND  (A.PROCESS IS NULL OR A.PROCESS ='') AND ISNULL(A.TRASFER_USER_RECIVE,'') <> '' AND ISNULL(A.NOTES,'')='' THEN N'Chờ cấp duyệt trung gian xác nhận'
		WHEN A.REQ_TYPE='I' AND A.BRANCH_CREATE='DV0001' AND  (A.PROCESS IS NULL OR A.PROCESS ='') AND ISNULL(A.TRASFER_USER_RECIVE,'') <> '' AND ISNULL(A.NOTES,'') <> '' THEN N'Chờ trưởng đơn vị xác nhận'
		WHEN A.REQ_TYPE='I' AND A.BRANCH_CREATE='DV0001' AND   ISNULL(A.TRASFER_USER_RECIVE,'') = ''  AND ISNULL(A.PROCESS,'') = '' THEN N'Chờ trưởng đơn vị xác nhận'
		WHEN A.REQ_TYPE='I' AND A.BRANCH_CREATE='DV0001' AND (A.PROCESS IS NOT NULL OR A.PROCESS ='0') THEN PC1.NOTES
		WHEN A.REQ_TYPE='I' AND  A.BRANCH_CREATE <> 'DV0001' AND ISNULL(A.TRASFER_USER_RECIVE,'') <> '' AND (A.PROCESS IS NULL OR A.PROCESS ='') THEN N'Chờ cấp duyệt trung gian xác nhận'
		WHEN A.REQ_TYPE='I' AND  A.BRANCH_CREATE<>'DV0001' AND (ISNULL(A.TRASFER_USER_RECIVE,'') ='' OR A.PROCESS ='0') THEN PC1.NOTES END AS NEXT_STEP,  
		PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,TL8.TLFullName AS RECIVER_MONEY_FULLNAME, CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR, BR.BRANCH_TYPE,@p_TYPE_SEARCH AS TYPE_SEARCH,  
		SR.ROLE_ID AS ROLE_ID_CRE, ISNULL(H.SOTIEN_TT,0) AS STTT, A.REQ_AMT - ISNULL(H.SOTIEN_TT,0) AS SL_CL,BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE, '' AS BRANCH_CODE_CONTRACT, '' AS BRANCH_NAME_CONTRACT,
		EXEC_USER = CASE WHEN PC3.TLNAME IS NOT NULL THEN PC3.TLNAME 
						 WHEN PC3.TLNAME <>'' THEN PC3.TLNAME
						 ELSE TL2.TLNANME  
						 END
		-- doanptt 120522
		, C.AUTH_STATUS_KT_DESC,
		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, 
        -- NÂNG CẤP GD3: BO SUNG THÔNG TIN HOÀN ỨNG
		D.REQ_PAY_ID ID_HOAN_UNG, E.REQ_PAY_CODE SO_PHIEU_HOAN_UNG, E.APPROVE_DT_KT NGAY_HOAN_UNG

-- SELECT END
		FROM TR_REQ_ADVANCE_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 A.DEP_ID= DP.DEP_ID  
		--Luanlt--2019/10/15-Sửa AL,AL1  
		LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='REQ_TYPE'  AND AL.CDTYPE = 'REQ_PAY_ADVANCE'--Loại thanh toán (Nội bộ/Thanh toán/Nợ)  
		LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'  AND AL1.CDTYPE = 'REQ_PAY_METHOD' --Hình thức thanh toán( Tiền mặt/Chuyển khoản)
		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 TL_USER TL6 ON A.RECEIVER_DEBIT = TL6.TLNANME AND A.REQ_TYPE='D'  
		LEFT JOIN  
		(  
		SELECT PAY_ADV_ID,(ISNULL(SUM(AMT_USE),0) - ISNULL(SUM(AMT_ADD),0) + ISNULL(SUM(AMT_REVERT),0)) AS 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 TL_USER TL7 ON TL7.TLNANME = A.REF_ID AND A.REQ_TYPE='I'  
		LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'  
		LEFT JOIN CM_SUPPLIER SUP ON PO.SUP_ID = SUP.SUP_ID AND A.REQ_TYPE='P'  
		LEFT JOIN LS_CUSTOMER CUS ON A.REF_ID = CUS.CUSTOMER_ID AND A.REQ_TYPE='D'  
		LEFT JOIN TR_PO_PAYMENT PM ON A.PAY_PHASE = PM.PAY_ID AND A.REQ_TYPE='P'  
		LEFT JOIN CM_DVDM DV ON A.DVDM_ID = DV.DVDM_ID  
		OUTER APPLY ( SELECT TOP 1 P.REQ_ID, P.PROCESS_DESC, P.APPROVE_DT AS APP_DT FROM PL_PROCESS P WHERE P.REQ_ID = A.REQ_PAY_ID ORDER BY ID DESC ) AS PR 
		LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND 
		(CHARINDEX(PC.ROLE_USER + ',', @ROLE_ID) > 0  OR EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =A.REQ_PAY_ID AND STATUS_JOB ='C' AND TLNAME =@p_USER_LOGIN))
		AND PC.STATUS='C' 
		--LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND PC.ROLE_USER IN (SELECT * FROM @TABLE_ROLE) AND PC.STATUS='C'  
		LEFT JOIN PL_REQUEST_PROCESS PC1 ON PC1.REQ_ID= A.REQ_PAY_ID AND PC1.STATUS='C'  
		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 TL_USER TL8 ON A.RECIVER_MONEY = 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  
        -- NÂNG CẤP GD3: BO SUNG THÔNG TIN HOÀN ỨNG
		LEFT JOIN TR_REQ_PAY_PERIOD B1 ON A.REQ_PAY_ID = B1.REQ_PAY_ID
		LEFT JOIN TR_REQ_PAY_PERIOD D ON B1.REQ_PAY_ID = D.AD_PAY_ID AND D.AUTH_STATUS_KT ='A' AND D.PAY_TYPE ='PAY' AND B1.PAY_PHASE = D.PAY_PHASE AND B1.OLD_INDEX = D.OLD_INDEX AND B1.NEW_INDEX = D.NEW_INDEX AND B1.CONTRACT_ID = D.CONTRACT_ID
		LEFT JOIN TR_REQ_PAYMENT E ON D.REQ_PAY_ID = E.REQ_PAY_ID
		WHERE 1=1  
		AND (A.REQ_PAY_ID =@p_REQ_PAY_ID OR @p_REQ_PAY_ID IS NULL OR @p_REQ_PAY_ID ='') 
		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)
		AND(PC3.TLNAME = @p_EXEC_USER_KT OR @p_EXEC_USER_KT = '' OR @p_EXEC_USER_KT IS NULL)
		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.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, @p_FRMDATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='' OR A.CREATE_DT IS NULL OR A.REQ_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 OR A.REQ_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.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(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.AUTH_STATUS=@p_AUTH_STATUS 
				OR @p_AUTH_STATUS='' 
				OR @p_AUTH_STATUS IS NULL 
				OR (@p_AUTH_STATUS = 'W' AND ISNULL(A.AUTH_STATUS, '') = 'U' AND A.PROCESS IS NULL)
				OR (@p_AUTH_STATUS = 'G' AND ISNULL(A.AUTH_STATUS, '') = 'U' AND A.PROCESS = '')
				OR (	A.REQ_TYPE ='I' 
						AND A.AUTH_STATUS <>'A' 
						AND EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH IN ('GDDV','PP','GDK','KTT','PTGD','TGD','HDQT','TKTGD','TKHDQT'))
					)
			)
		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
		(
			(	@p_IS_TRANSFER='Y' 
				 AND(
						@p_TYPE_SEARCH ='TKTGD' AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND PROCESS_ID ='1')
			
					 OR (
							@p_TYPE_SEARCH ='TKHDQT' AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND PROCESS_ID ='2')
						)
					 OR (
							@p_TYPE_SEARCH ='KT' 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 (
						(@p_TYPE_SEARCH ='TKTGD' AND NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND  PROCESS_ID ='1') AND ISNULL(A.AUTH_STATUS_KT,'') <> 'A') 
				
						OR  (
								@p_TYPE_SEARCH ='TKHDQT' AND NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND PROCESS_ID ='2' AND ISNULL(A.AUTH_STATUS_KT,'') <> 'A')
							)
						OR  (
								@p_TYPE_SEARCH ='KT' AND NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND (PROCESS_ID IS NULL OR PROCESS_ID ='' OR PROCESS_ID ='3')) AND ISNULL(A.AUTH_STATUS_KT,'') <> 'A' 
							)
					)
			)
			OR @p_IS_TRANSFER IS NULL 
			OR @p_IS_TRANSFER=''
		) 

		AND	(	(	(	@p_PROCESS='PM' 
						AND (A.REQ_AMT- ISNULL(A.PAY_AMT,0.00)) >0
					)  
					AND (	A.REQ_TYPE IN('I','D','P') 
							OR	(	@DEP_ID_LG ='DEP000000000014' 
									AND A.REQ_TYPE IN ('I','P','D')
								)
						)	
					AND	(	(	A.DEP_ID = @DEP_ID_LG 
								OR A.MAKER_ID =@p_USER_LOGIN 
								OR A.MAKER_ID IS NULL
							) 
								OR	(	@BRANCH_TYPE_LG <>'HS' 
										AND A.BRANCH_ID =@p_BRANCH_LOGIN
									) 
										OR (	@p_REQ_PAY_CODE IS NOT NULL 
												AND @p_REQ_PAY_CODE <> '' 
												AND A.REQ_PAY_CODE =@p_REQ_PAY_CODE
											)
						)
				)  
				OR @p_PROCESS IS NULL OR @p_PROCESS =''
			)  
		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
							) 
						OR	(	@p_REQ_PAY_CODE IS NOT NULL 
									AND @p_REQ_PAY_CODE <> '' 
									AND A.REQ_PAY_CODE =@p_REQ_PAY_CODE
							)
					)
			)    
		AND (	(	@p_FUNCTION ='KT' 
					AND (	EXISTS	(	SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD X 
										WHERE X.REQ_ID = A.REQ_PAY_ID 
										AND (	X.TLNAME= @p_USER_LOGIN 
												OR X.TLNAME =@p_EXEC_USER_KT
											)
									)
						) 
					OR A.CHECKER_ID_KT ='admin' OR A.AUTH_STATUS_KT ='A'
				)  
				OR @p_FUNCTION IS NULL 
				OR @p_FUNCTION ='' 
				OR (@p_FUNCTION ='TF' AND A.AUTH_STATUS='A') 
				OR @p_FUNCTION ='SIGN'
			)
		AND(A.MAKER_ID LIKE '%'+@p_MAKER_ID+'%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID ='')    
		AND	(	EXISTS	(	SELECT * FROM PL_REQUEST_PROCESS_CHILD 
							WHERE TLNAME =@p_TRASFER_USER_RECIVE 
							AND TYPE_JOB IN ('XL','KS','TP') 
							AND REQ_ID = A.REQ_PAY_ID
						) 
				OR @p_TRASFER_USER_RECIVE IS NULL 
				OR @p_TRASFER_USER_RECIVE=''
			)  
		AND	(	A.MAKER_ID =@p_USER_LOGIN OR @p_USER_LOGIN = 'baotq'
				OR (A.TRASFER_USER_RECIVE = @p_USER_LOGIN AND A.AUTH_STATUS NOT IN ('E','R'))  
				OR	(	A.AUTH_STATUS <>'E' AND A.AUTH_STATUS <> 'R'
						AND	(	(	@p_TYPE_SEARCH ='HC'	-- NEU LA PHIEU DE NGHI TAM UNG NOI BO
									AND @BRANCH_TYPE_LG ='HS' 
									AND A.REQ_TYPE ='I'  
									AND (	(	EXISTS	(	SELECT * FROM @TABLE_ROLE 
															WHERE ROLE_AUTH IN ('GDDV','TP', 'PP', 'KTT','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 (SELECT * FROM @DEP_AUTH))
												AND	(	(	@p_AUTH_STATUS ='A' 
															AND ISNULL(A.PROCESS,'') ='0' 
															AND	EXISTS	(	SELECT * FROM PL_PROCESS 
																			WHERE REQ_ID =A.REQ_PAY_ID 
																			AND PROCESS_DESC IN (N'Trưởng đơn vị xác nhận phiếu',N'Cấp phê duyệt trung gian xác nhận phiếu')
																		)
															OR (@p_AUTH_STATUS ='U' AND (ISNULL(A.PROCESS,'')='')) 
															OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS =''
														)
													)
											) 
											OR	(	(CHARINDEX('NVMS,', @ROLE_ID) > 0 OR CHARINDEX('NVTT,', @ROLE_ID) > 0 OR CHARINDEX('TBP,', @ROLE_ID) > 0)
													AND	(	A.TRASFER_USER_RECIVE = @p_USER_LOGIN  
															OR A.MAKER_ID =@p_MAKER_ID 
															OR A.DEP_ID=@p_DEP_ID
														)
													AND (A.AUTH_STATUS =@p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS,'') ='')
												)
											OR	(	EXISTS	(	SELECT * FROM @TABLE_ROLE 
																WHERE ROLE_AUTH  IN ('GDK')
															) 
													AND	(	DP.KHOI_ID =@DVDM_ID 
															OR A.DVDM_ID = @DVDM_ID
														) 
													AND EXISTS	(	SELECT * FROM PL_REQUEST_PROCESS 
																	WHERE REQ_ID = A.REQ_PAY_ID
																	AND ROLE_USER IN (	SELECT * FROM @TABLE_ROLE) 
																	-- AND A.BRANCH_CREATE ='DV0001' 
																	AND A.PROCESS<>'' 
																	AND A.PROCESS IS NOT NULL
																	AND	(	(	@p_AUTH_STATUS ='A' 
																				AND STATUS IN ('P','A')
																			) 
																			OR	(	@p_AUTH_STATUS = 'U' AND STATUS ='C') 
																			OR	(	ISNULL(@p_AUTH_STATUS,'') ='' 
																					AND STATUS IN ('C','P','A')
																				)
																		)
																)
												)
											OR	(	EXISTS	(	SELECT * FROM @TABLE_ROLE 
																WHERE ROLE_AUTH  IN ('PTGD')
															) 
													AND	(	A.DVDM_ID =@DVDM_ID 
															OR (	A.DVDM_ID IS NULL 
																	OR A.DVDM_ID =''
																)
														) 
													AND EXISTS	(	SELECT * FROM PL_REQUEST_PROCESS 
																	WHERE REQ_ID = A.REQ_PAY_ID
																	AND ROLE_USER IN (	SELECT * FROM @TABLE_ROLE) 
																	-- AND A.BRANCH_CREATE ='DV0001' 
																	AND A.PROCESS<>'' 
																	AND A.PROCESS IS NOT NULL
																	AND	(	(	@p_AUTH_STATUS ='A' 
																				AND STATUS IN ('P','A')
																			) 
																			OR	(	@p_AUTH_STATUS = 'U' AND STATUS ='C') 
																			OR	(	ISNULL(@p_AUTH_STATUS,'') ='' 
																					AND STATUS IN ('C','P','A')
																				)
																		)
																)
													AND (	@KHOI_ID_LG = (SELECT KHOI_ID FROM CM_DEPARTMENT WHERE DEP_ID	= (SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = A.REQ_PAY_ID))
															OR	(	@p_USER_LOGIN = 'tupa' AND (SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = A.REQ_PAY_ID) = 'DEP000000000015')
															OR EXISTS	(	SELECT TOP 1 DVDM_ID FROM PL_REQUEST_PROCESS 
																			WHERE REQ_ID = A.REQ_PAY_ID	AND DVDM_ID = @KHOI_ID_LG
																		)
														)
												)
											OR	(	EXISTS	(	SELECT * 
																FROM @TABLE_ROLE 
																WHERE ROLE_AUTH IN ('TKTGD', 'TKHDQT','TGD','HDQT')) 
																AND EXISTS	(	SELECT * 
																				FROM PL_REQUEST_PROCESS	
																				WHERE REQ_ID = A.REQ_PAY_ID
																				AND ROLE_USER IN(SELECT * FROM @TABLE_ROLE) 
																				--AND A.BRANCH_CREATE ='DV0001' 
																				AND A.PROCESS<>'' 
																				AND A.PROCESS IS NOT NULL
																				AND (	(	@p_AUTH_STATUS ='A' 
																							AND STATUS IN ('P','A')
																						) 
																						OR (@p_AUTH_STATUS = 'U' AND STATUS ='C') 
																						OR(ISNULL(@p_AUTH_STATUS,'') ='' )
																					)
																			)
												)
											-- USER HOI SO KIEM NHIEM GDDV TAI CHI NHANH
											OR	(	EXISTS	(	SELECT * 
																FROM SYS_PERMISSIONS_PAGE_FOR_USER TLS 
																WHERE 1=1
																AND ISNULL(A.BRANCH_ID, '') <> '' AND ISNULL(A.BRANCH_ID, '') <> 'DV0001'
																AND TLS.TLNAME = @p_USER_LOGIN
																AND TLS.RoleDisplayName IN ('GDDV')
																AND TLS.BRANCH_ID = A.BRANCH_ID
																AND (DATEDIFF(DAY, CONVERT(DATE, TLS.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TLS.EffectiveDate IS NULL OR TLS.EffectiveDate = '')
																AND (DATEDIFF(DAY, CONVERT(DATE, TLS.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TLS.ExpirationDate IS NULL OR TLS.ExpirationDate = '')
																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 = TLS.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
																AND TLS.AUTH_STATUS = 'A'
																AND TLS.RECORD_STATUS = '1'
															)
												)

										)-- NEU ROLE NAM TRONG DS CAC ROLE WORKFLOW 1
								)	-- ngoac so 2, line 347
-- NGUOC LAI NEU LA TAM UNG KHAC
								OR	(	@p_TYPE_SEARCH ='HC' 
										AND @BRANCH_TYPE_LG ='HS' 
										AND A.REQ_TYPE <> 'I' 
										AND	(	A.TRASFER_USER_RECIVE = @p_USER_LOGIN 
												OR	(	EXISTS(SELECT TOP 1 A.ROLE_NAME FROM TMP_ROLE_ADVANCE_PAYMENT_SEARCH A WHERE A.ROLE_NAME IN ('GDDV','GDK','KTT','TPTC','PP','TBP','TP','TC') AND CHARINDEX(A.ROLE_NAME + ',', @ROLE_ID) > 0)
														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	(	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 
																	)
															)
													)  
												OR	(	EXISTS(SELECT TOP 1 A.ROLE_NAME FROM TMP_ROLE_ADVANCE_PAYMENT_SEARCH A WHERE A.ROLE_NAME NOT IN ('GDDV','GDK','KTT','TPTC','PP','TBP','TP','TC') AND CHARINDEX(A.ROLE_NAME + ',', @ROLE_ID) > 0) 
														AND (A.DEP_ID =@DEP_ID_LG OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH))
													)  
											)
									)
									-- HẾT KIỂM TRA LIÊN QUAN ĐẾN LOẠI TẠM ỨNG
								OR	(	@p_TYPE_SEARCH ='HC' 
										AND @BRANCH_TYPE_LG = 'CN' 
										AND (	EXISTS(SELECT TOP 1 A.ROLE_NAME FROM TMP_ROLE_ADVANCE_PAYMENT_SEARCH A WHERE A.ROLE_NAME IN ('GDDV','PGD','TPGD','PPGD') AND CHARINDEX(A.ROLE_NAME + ',', @ROLE_ID) > 0) 
												AND	(	(	A.TRASFER_USER_RECIVE IS NOT NULL 
															AND A.TRASFER_USER_RECIVE <>'' 
															AND A.PROCESS IS NOT NULL 
														)  
														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 IN (SELECT * FROM @tmp)
											)
									)  
								OR	(	@p_TYPE_SEARCH ='HC' 
										AND @BRANCH_TYPE_LG = 'CN' 
										AND A.BRANCH_ID IN (SELECT * FROM @tmp)
									)  
								OR	(	@p_TYPE_SEARCH ='HC' 
										AND @BRANCH_TYPE_LG = 'PGD' 
										AND	( EXISTS(SELECT TOP 1 A.ROLE_NAME FROM TMP_ROLE_ADVANCE_PAYMENT_SEARCH A WHERE A.ROLE_NAME IN ('GDDV','TPGD','PPGD','PGD') AND CHARINDEX(A.ROLE_NAME + ',', @ROLE_ID) > 0) 
												AND A.BRANCH_ID =@p_BRANCH_LOGIN
											)
									)  
								OR	(	@p_TYPE_SEARCH ='HC' 
										AND	(	A.DVDM_ID IS NOT NULL 
												AND A.DVDM_ID <>'' 
												AND A.DVDM_ID =@DVDM_ID 
												AND A.PROCESS IS NOT NULL 
												AND A.PROCESS <>'' 
												AND	EXISTS(SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.REQ_PAY_ID AND CHARINDEX(ROLE_USER + ',', @ROLE_ID) > 0)
											) 
										OR	(	A.DEP_ID IN (SELECT * FROM @TMP_DEP) 
												AND A.PROCESS IS NOT NULL 
												AND A.PROCESS <>''  
												AND CHARINDEX('TKTGD,', @ROLE_ID) = 0
												AND	EXISTS(SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.REQ_PAY_ID AND CHARINDEX(ROLE_USER + ',', @ROLE_ID) > 0)
											)
									)  
								--OR(@p_TYPE_SEARCH ='HC' AND (@ROLE_ID IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID= A.REQ_PAY_ID AND ROLE_USER IN ('TGD','HDQT','TKTGD','TKHDQT') AND STATUS ='C')))  
								OR	(	@p_TYPE_SEARCH='KT' 
										AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
									)  
								OR	(	@p_TYPE_SEARCH='HC' 
										AND	((CHARINDEX('KSV,', @ROLE_ID) > 0 OR CHARINDEX('GDV,', @ROLE_ID) > 0)  
												OR @DEP_ID_LG ='DEP000000000022'
											) 
										AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
									)  
								OR	(	@p_TYPE_SEARCH='HC' AND (	EXISTS	(	SELECT * FROM PL_REQUEST_PROCESS_CHILD 
																				WHERE REQ_ID = A.REQ_PAY_ID 
																				AND TLNAME =@p_USER_LOGIN 
																				AND TYPE_JOB ='XL' 
																				AND STATUS_JOB = 'C' 
																				AND ISNULL(A.AUTH_STATUS, '') <> 'A'
																			) 
																	OR EXISTS	(	SELECT * FROM PL_REQUEST_PROCESS_CHILD 
																					WHERE REQ_ID = A.REQ_PAY_ID 
																					AND TLNAME =@p_USER_LOGIN 
																					AND TYPE_JOB ='XL' 
																					AND STATUS_JOB = 'P' 
																					AND	(	ISNULL(A.AUTH_STATUS, '') = 'A'
																																																								OR EXISTS	(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND TYPE_JOB ='KS' AND STATUS_JOB = 'C')
																																																							)
																				)
																)
									) 
								OR	(	@p_TYPE_SEARCH IN ('TK','TKTGD','TKHDQT') 
										AND A.PROCESS<>'' 
										AND A.PROCESS IS NOT NULL
										AND (	(	@p_FUNCTION ='TF' AND  EXISTS	(	SELECT * FROM PL_REQUEST_PROCESS 
																					WHERE REQ_ID = A.REQ_PAY_ID 
																					AND CHARINDEX(ROLE_USER + ',', @ROLE_ID) > 0
																					AND STATUS ='C'
																				)
												)
												OR	(	ISNULL(@p_FUNCTION,'') <> 'TF' 
														AND EXISTS(	SELECT * 
																	FROM PL_REQUEST_PROCESS 
																	WHERE REQ_ID = A.REQ_PAY_ID 
																	AND CHARINDEX(ROLE_USER + ',', @ROLE_ID) > 0 
																	AND (	(@p_IS_TRANSFER = N'N' AND STATUS ='C') 
																			OR	(	@p_IS_TRANSFER <>'N' 
																					AND STATUS IN ('C','P')
																				)
																		)
																	)
													)
											)
									)
								OR	(	@p_TYPE_SEARCH='PAY' 
										AND (	A.REQ_AMT -ISNULL(A.PAY_AMT,0)) >0 
										AND	(	(	@BRANCH_TYPE_LG ='HS' 
													AND	(	A.DEP_ID =@DEP_ID_LG 
															OR A.MAKER_ID =@p_USER_LOGIN 
															OR A.MAKER_ID IS NULL 
															OR 1=1
														)
												) 
												OR	(	@BRANCH_TYPE_LG <> 'HS' 
														AND A.BRANCH_ID = @p_BRANCH_LOGIN
													)
												OR	(	@p_REQ_PAY_CODE IS NOT NULL 
														AND @p_REQ_PAY_CODE <> '' 
														AND A.REQ_PAY_CODE =@p_REQ_PAY_CODE
													)
											)
									)  
							) -- ngoac so 1, line 347
						OR	(	@p_TYPE_SEARCH='HC' 
								AND	((CHARINDEX('KSV,', @ROLE_ID) > 0 OR CHARINDEX('GDV,', @ROLE_ID) > 0)
										OR @DEP_ID_LG ='DEP000000000022'
									) AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
							)
					) -- line 346
			) -- line 344
		AND	(	(	A.PAY_AMT = A.REQ_AMT 
					AND (@p_TEMP_PAY_STATUS = 'HT')
				)  
				OR (A.PAY_AMT = 0 AND (@p_TEMP_PAY_STATUS = 'DCHU') )  
				OR (A.PAY_AMT <> 0 and A.PAY_AMT < A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'DHU'))  
				OR @p_TEMP_PAY_STATUS IS NULL OR @p_TEMP_PAY_STATUS = ''  
			)  
		AND	(	A.RECORD_STATUS ='1' AND A.AUTH_STATUS <>'X')
		ORDER BY A.CREATE_DT DESC   
-- PAGING END
	END;
	ELSE 
	BEGIN
-- PAGING BEGIN 
		SELECT A.*, A.CONFIRM_NOTES AS CONFIRM_NOTE,'' as TEMP_PAY_STATUS ,ISNULL(B.AUTH_STATUS_NAME,N'Chờ duyệt') AS AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME,  
		TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName 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, DP.DEP_NAME AS DEP_NAME, DP.DEP_CODE AS DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,  
		--Luanlt--2019/10/15-Sửa AL,AL1  
		BR1.BRANCH_CODE BRANCH_CODE_CRE, BR1.BRANCH_NAME AS BRANCH_NAME_CRE , AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME,  
		A.REQ_AMT AS TOTAL_AMT_TEMP,CASE WHEN H.SOTIEN_TT IS NULL THEN ISNULL(A.PAY_AMT,0) ELSE ISNULL(A.PAY_AMT,0) END AS TOTAL_AMT_PAY_HIS,  
		TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,  
		TL6.TLFullName AS REC_DEBIT_NAME, TL6.TLNANME AS REC_DEBIT_CODE, TL7.TLFullName AS EMP_NAME, TL7.TLNANME AS EMP_CODE,  
		PO.PO_CODE, PO.PO_NAME,SUP.SUP_NAME,SUP.TAX_NO AS SUP_TAX_NO,CUS.CUSTOMER_CODE AS CUST_CODE, CUS.CUSTOMER_NAME AS CUST_NAME,PM.PAY_PHASE AS KY_TAM_UNG,  
		DV.DVDM_CODE, DV.DVDM_NAME, CASE WHEN PC.ROLE_USER ='GDDV' THEN (SELECT RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN) ELSE PC.ROLE_USER END AS ROLE_CONF, 
		--PR.PROCESS_DESC +N' vào lúc '+ CONVERT(VARCHAR(30), CONVERT(DATETIME, PR.APP_DT,103)) AS CONF_STATUS, 
		PR.PROCESS_DESC +N' vào lúc '+ ISNULL(FORMAT(PR.APP_DT,'dd/MM/yyyy H:mm:ss'),'') AS CONF_STATUS,
		--PC1.NOTES AS NEXT_STEP,  
		---2021112 CAU HINH BUOC XU LY TIEP THEO PDN TAM UNG NOI BO
		CASE WHEN A.REQ_TYPE='I' AND A.AUTH_STATUS ='E' THEN N'Phiếu đang chờ gửi phê duyệt' 
		WHEN A.REQ_TYPE='I' AND A.AUTH_STATUS_KT ='R' THEN N'Phiếu đang chờ GDV xử lý'
		WHEN A.REQ_TYPE='I' AND A.AUTH_STATUS_KT ='P' THEN N'Phiếu đang chờ KSV xử lý'
		WHEN A.REQ_TYPE='I' AND A.AUTH_STATUS ='R' THEN N'Phiếu bị trả về đơn vị' 
		WHEN A.REQ_TYPE='I' AND A.BRANCH_CREATE='DV0001' AND  (A.PROCESS IS NULL OR A.PROCESS ='') AND ISNULL(A.TRASFER_USER_RECIVE,'') <> '' AND ISNULL(A.NOTES,'')='' THEN N'Chờ cấp duyệt trung gian xác nhận'
		WHEN A.REQ_TYPE='I' AND A.BRANCH_CREATE='DV0001' AND  (A.PROCESS IS NULL OR A.PROCESS ='') AND ISNULL(A.TRASFER_USER_RECIVE,'') <> '' AND ISNULL(A.NOTES,'') <> '' THEN N'Chờ trưởng đơn vị xác nhận'
		WHEN A.REQ_TYPE='I' AND A.BRANCH_CREATE='DV0001' AND   ISNULL(A.TRASFER_USER_RECIVE,'') = ''  AND ISNULL(A.PROCESS,'') = '' THEN N'Chờ trưởng đơn vị xác nhận'
		WHEN A.REQ_TYPE='I' AND A.BRANCH_CREATE='DV0001' AND (A.PROCESS IS NOT NULL OR A.PROCESS ='0') THEN PC1.NOTES
		WHEN A.REQ_TYPE='I' AND  A.BRANCH_CREATE <> 'DV0001' AND ISNULL(A.TRASFER_USER_RECIVE,'') <> '' AND (A.PROCESS IS NULL OR A.PROCESS ='') THEN N'Chờ cấp duyệt trung gian xác nhận'
		WHEN A.REQ_TYPE='I' AND  A.BRANCH_CREATE<>'DV0001' AND (ISNULL(A.TRASFER_USER_RECIVE,'') ='' OR A.PROCESS ='0') THEN PC1.NOTES END AS NEXT_STEP,  
		PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB, TL8.TLFullName AS RECIVER_MONEY_FULLNAME, CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR, BR.BRANCH_TYPE,@p_TYPE_SEARCH AS TYPE_SEARCH,  
		SR.ROLE_ID AS ROLE_ID_CRE, ISNULL(H.SOTIEN_TT,0) AS STTT, A.REQ_AMT - ISNULL(H.SOTIEN_TT,0) AS SL_CL,BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE, '' AS BRANCH_CODE_CONTRACT, '' AS BRANCH_NAME_CONTRACT,
		EXEC_USER = CASE WHEN PC3.TLNAME IS NOT NULL THEN PC3.TLNAME 
						 WHEN PC3.TLNAME <>'' THEN PC3.TLNAME
						 ELSE TL2.TLNANME  
						 END
		-- doanptt 120522
		, C.AUTH_STATUS_KT_DESC,
		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
        -- NÂNG CẤP GD3: BO SUNG THÔNG TIN HOÀN ỨNG
        ,D.REQ_PAY_ID ID_HOAN_UNG, E.REQ_PAY_CODE SO_PHIEU_HOAN_UNG, E.APPROVE_DT_KT NGAY_HOAN_UNG
-- SELECT END
		FROM TR_REQ_ADVANCE_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 A.DEP_ID= DP.DEP_ID  
		--Luanlt--2019/10/15-Sửa AL,AL1  
		LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='REQ_TYPE'  AND AL.CDTYPE = 'REQ_PAY_ADVANCE'--Loại thanh toán (Nội bộ/Thanh toán/Nợ)  
		LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'  AND AL1.CDTYPE = 'REQ_PAY_METHOD' --Hình thức thanh toán( Tiền mặt/Chuyển khoản)
		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 TL_USER TL6 ON A.RECEIVER_DEBIT = TL6.TLNANME AND A.REQ_TYPE='D'  
		LEFT JOIN  
		(  
		SELECT PAY_ADV_ID,(ISNULL(SUM(AMT_USE),0) - ISNULL(SUM(AMT_ADD),0) + ISNULL(SUM(AMT_REVERT),0)) AS 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 TL_USER TL7 ON TL7.TLNANME = A.REF_ID AND A.REQ_TYPE='I'  
		LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'  
		LEFT JOIN CM_SUPPLIER SUP ON PO.SUP_ID = SUP.SUP_ID AND A.REQ_TYPE='P'  
		LEFT JOIN LS_CUSTOMER CUS ON A.REF_ID = CUS.CUSTOMER_ID AND A.REQ_TYPE='D'  
		LEFT JOIN TR_PO_PAYMENT PM ON A.PAY_PHASE = PM.PAY_ID AND A.REQ_TYPE='P'  
		LEFT JOIN CM_DVDM DV ON A.DVDM_ID = DV.DVDM_ID  
		OUTER APPLY ( SELECT TOP 1 P.REQ_ID, P.PROCESS_DESC, P.APPROVE_DT AS APP_DT FROM PL_PROCESS P WHERE P.REQ_ID = A.REQ_PAY_ID ORDER BY ID DESC ) AS PR
		LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND 
		(CHARINDEX(PC.ROLE_USER + ',', @ROLE_ID) > 0 OR EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =A.REQ_PAY_ID AND STATUS_JOB ='C' AND TLNAME =@p_USER_LOGIN))
		AND PC.STATUS='C'  
		--LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND PC.ROLE_USER IN (SELECT * FROM @TABLE_ROLE) AND PC.STATUS='C'  
		LEFT JOIN PL_REQUEST_PROCESS PC1 ON PC1.REQ_ID= A.REQ_PAY_ID AND PC1.STATUS='C'  
		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 TL_USER TL8 ON A.RECIVER_MONEY = 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_SCHEDULE D ON A.REQ_PAY_ID = D.REQ_ADV_ID AND D.TRN_TYPE ='PAY' AND D.AUTH_STATUS_KT ='A'  AND A.PAY_PHASE = D.PAY_PHASE AND A.REF_ID = D.REF_ID AND D.PROCESS = '2'	--PDN THANH TOAN
		LEFT JOIN TR_REQ_PAYMENT E ON D.REQ_PAY_ID = E.REQ_PAY_ID
		WHERE 1=1  
		AND (A.REQ_PAY_ID =@p_REQ_PAY_ID OR @p_REQ_PAY_ID IS NULL OR @p_REQ_PAY_ID ='')  
		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.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)  
		AND(PC3.TLNAME = @p_EXEC_USER_KT OR @p_EXEC_USER_KT = '' OR @p_EXEC_USER_KT IS NULL)
		AND(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' OR @p_REQ_REASON='' OR @p_REQ_REASON IS NULL)    
		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, @p_FRMDATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='' OR A.CREATE_DT IS NULL OR A.REQ_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 OR A.REQ_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.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(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.AUTH_STATUS=@p_AUTH_STATUS 
				OR @p_AUTH_STATUS='' 
				OR @p_AUTH_STATUS IS NULL 
				OR (@p_AUTH_STATUS = 'W' AND ISNULL(A.AUTH_STATUS, '') = 'U' AND A.PROCESS IS NULL)
				OR (@p_AUTH_STATUS = 'G' AND ISNULL(A.AUTH_STATUS, '') = 'U' AND A.PROCESS = '')
				OR (	A.REQ_TYPE ='I' 
						AND A.AUTH_STATUS <>'A' 
						AND EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH IN ('GDDV','PP','GDK','KTT','PTGD','TGD','HDQT','TKTGD','TKHDQT'))
					)
			)
		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
		(
			(	@p_IS_TRANSFER='Y' 
				 AND(
						@p_TYPE_SEARCH ='TKTGD' AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND PROCESS_ID ='1')
			
					 OR (
							@p_TYPE_SEARCH ='TKHDQT' AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND PROCESS_ID ='2')
						)
					 OR (
							@p_TYPE_SEARCH ='KT' 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 (
						(@p_TYPE_SEARCH ='TKTGD' AND NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND  PROCESS_ID ='1') AND ISNULL(A.AUTH_STATUS_KT,'') <> 'A') 
				
						OR  (
								@p_TYPE_SEARCH ='TKHDQT' AND NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND PROCESS_ID ='2' AND ISNULL(A.AUTH_STATUS_KT,'') <> 'A')
							)
						OR  (
								@p_TYPE_SEARCH ='KT' AND NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND (PROCESS_ID IS NULL OR PROCESS_ID ='' OR PROCESS_ID ='3')) AND ISNULL(A.AUTH_STATUS_KT,'') <> 'A' 
							)
					)
			)
			OR @p_IS_TRANSFER IS NULL 
			OR @p_IS_TRANSFER=''
		) 

		AND	(	(	(	@p_PROCESS='PM' 
						AND (A.REQ_AMT- ISNULL(A.PAY_AMT,0.00)) >0
					)  
					AND (	A.REQ_TYPE IN('I','D','P') 
							OR	(	@DEP_ID_LG ='DEP000000000014' 
									AND A.REQ_TYPE IN ('I','P','D')
								)
						)	
					AND	(	(	A.DEP_ID = @DEP_ID_LG 
								OR A.MAKER_ID =@p_USER_LOGIN 
								OR A.MAKER_ID IS NULL
							) 
								OR	(	@BRANCH_TYPE_LG <>'HS' 
										AND A.BRANCH_ID =@p_BRANCH_LOGIN
									) 
										OR (	@p_REQ_PAY_CODE IS NOT NULL 
												AND @p_REQ_PAY_CODE <> '' 
												AND A.REQ_PAY_CODE =@p_REQ_PAY_CODE
											)
						)
				)  
				OR @p_PROCESS IS NULL OR @p_PROCESS =''
			)  
		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
							) 
						OR	(	@p_REQ_PAY_CODE IS NOT NULL 
									AND @p_REQ_PAY_CODE <> '' 
									AND A.REQ_PAY_CODE =@p_REQ_PAY_CODE
							)
					)
			)    
		AND (	(	@p_FUNCTION ='KT' 
					AND (	EXISTS	(	SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD X 
										WHERE X.REQ_ID = A.REQ_PAY_ID 
										AND (	X.TLNAME= @p_USER_LOGIN 
												OR X.TLNAME =@p_EXEC_USER_KT
											)
									)
						) 
					OR A.CHECKER_ID_KT ='admin' OR A.AUTH_STATUS_KT ='A'
				)  
				OR @p_FUNCTION IS NULL 
				OR @p_FUNCTION ='' 
				OR (@p_FUNCTION ='TF' AND A.AUTH_STATUS='A') 
				OR @p_FUNCTION ='SIGN'
			)
		AND(A.MAKER_ID LIKE '%'+@p_MAKER_ID+'%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID ='')    
		AND	(	EXISTS	(	SELECT * FROM PL_REQUEST_PROCESS_CHILD 
							WHERE TLNAME =@p_TRASFER_USER_RECIVE 
							AND TYPE_JOB IN ('XL','KS','TP') 
							AND REQ_ID = A.REQ_PAY_ID
						) 
				OR @p_TRASFER_USER_RECIVE IS NULL 
				OR @p_TRASFER_USER_RECIVE=''
			)  
		AND	(	A.MAKER_ID =@p_USER_LOGIN 
				OR (A.TRASFER_USER_RECIVE = @p_USER_LOGIN AND A.AUTH_STATUS NOT IN ('E','R'))  
				OR	(	A.AUTH_STATUS <>'E' AND A.AUTH_STATUS <> 'R'
						AND	(	(	@p_TYPE_SEARCH ='HC'	-- NEU LA PHIEU DE NGHI TAM UNG NOI BO
									AND @BRANCH_TYPE_LG ='HS' 
									AND A.REQ_TYPE ='I'  
									AND (	(	EXISTS	(	SELECT * FROM @TABLE_ROLE 
															WHERE ROLE_AUTH IN ('GDDV','TP', 'PP', 'KTT','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 (SELECT * FROM @DEP_AUTH))
												AND	(	(	@p_AUTH_STATUS ='A' 
															AND ISNULL(A.PROCESS,'') ='0' 
															AND	EXISTS	(	SELECT * FROM PL_PROCESS 
																			WHERE REQ_ID =A.REQ_PAY_ID 
																			AND PROCESS_DESC IN (N'Trưởng đơn vị xác nhận phiếu',N'Cấp phê duyệt trung gian xác nhận phiếu')
																		)
															OR (@p_AUTH_STATUS ='U' AND ISNULL(A.PROCESS,'')='') 
															OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS =''
														)
													)
											) 
											OR	((CHARINDEX('NVMS,', @ROLE_ID) > 0 OR CHARINDEX('NVTT,', @ROLE_ID) > 0 OR CHARINDEX('TBP,', @ROLE_ID) > 0)
													AND	(	A.TRASFER_USER_RECIVE = @p_USER_LOGIN  
															OR A.MAKER_ID =@p_MAKER_ID 
															OR A.DEP_ID=@p_DEP_ID
														)
													AND (A.AUTH_STATUS =@p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS,'') ='')
												)
											OR	(	EXISTS	(	SELECT * FROM @TABLE_ROLE 
																WHERE ROLE_AUTH  IN ('GDK')
															) 
													AND	(	DP.KHOI_ID =@DVDM_ID 
															OR A.DVDM_ID = @DVDM_ID
														)
													AND EXISTS	(	SELECT * FROM PL_REQUEST_PROCESS 
																	WHERE REQ_ID = A.REQ_PAY_ID
																	AND ROLE_USER IN (	SELECT * FROM @TABLE_ROLE) 
																	-- AND A.BRANCH_CREATE ='DV0001' 
																	AND A.PROCESS<>'' 
																	AND A.PROCESS IS NOT NULL
																	AND	(	(	@p_AUTH_STATUS ='A' 
																				AND STATUS IN ('P','A')
																			) 
																			OR	(	@p_AUTH_STATUS = 'U' AND STATUS ='C') 
																			OR	(	ISNULL(@p_AUTH_STATUS,'') ='' 
																					AND STATUS IN ('C','P','A')
																				)
																		)
																)
												)
											OR	(	EXISTS	(	SELECT * FROM @TABLE_ROLE 
																WHERE ROLE_AUTH  IN ('PTGD')
															) 
													AND	(	A.DVDM_ID =@DVDM_ID 
															OR (	A.DVDM_ID IS NULL 
																	OR A.DVDM_ID =''
																)
														) 
													AND EXISTS	(	SELECT * FROM PL_REQUEST_PROCESS 
																	WHERE REQ_ID = A.REQ_PAY_ID
																	AND ROLE_USER IN (	SELECT * FROM @TABLE_ROLE) 
																	-- AND A.BRANCH_CREATE ='DV0001' 
																	AND A.PROCESS<>'' 
																	AND A.PROCESS IS NOT NULL
																	AND	(	(	@p_AUTH_STATUS ='A' 
																				AND STATUS IN ('P','A')
																			) 
																			OR	(	@p_AUTH_STATUS = 'U' AND STATUS ='C') 
																			OR	(	ISNULL(@p_AUTH_STATUS,'') ='' 
																					AND STATUS IN ('C','P','A')
																				)
																		)
																)
													AND (	@KHOI_ID_LG = (SELECT KHOI_ID FROM CM_DEPARTMENT WHERE DEP_ID	= (SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = A.REQ_PAY_ID))
															OR	(	@p_USER_LOGIN = 'tupa' AND (SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = A.REQ_PAY_ID) = 'DEP000000000015')
															OR EXISTS	(	SELECT TOP 1 DVDM_ID FROM PL_REQUEST_PROCESS 
																			WHERE REQ_ID = A.REQ_PAY_ID	AND DVDM_ID = @KHOI_ID_LG
																		)
														)
												)
											OR	(	EXISTS	(	SELECT * 
																FROM @TABLE_ROLE 
																WHERE ROLE_AUTH IN ('TKTGD', 'TKHDQT','TGD','HDQT')) 
																AND EXISTS	(	SELECT * 
																				FROM PL_REQUEST_PROCESS	
																				WHERE REQ_ID = A.REQ_PAY_ID
																				AND ROLE_USER IN(SELECT * FROM @TABLE_ROLE) 
																				--AND A.BRANCH_CREATE ='DV0001' 
																				AND A.PROCESS<>'' 
																				AND A.PROCESS IS NOT NULL
																				AND (	(	@p_AUTH_STATUS ='A' 
																							AND STATUS IN ('P','A')
																						) 
																						OR (@p_AUTH_STATUS = 'U' AND STATUS ='C') 
																						OR(ISNULL(@p_AUTH_STATUS,'') ='' )
																					)
																			)
												)
											-- USER HOI SO KIEM NHIEM GDDV TAI CHI NHANH
											OR	(	EXISTS	(	SELECT * 
																FROM SYS_PERMISSIONS_PAGE_FOR_USER TLS 
																WHERE 1=1
																AND ISNULL(A.BRANCH_ID, '') <> '' AND ISNULL(A.BRANCH_ID, '') <> 'DV0001'
																AND TLS.TLNAME = @p_USER_LOGIN
																AND TLS.RoleDisplayName IN ('GDDV')
																AND TLS.BRANCH_ID = A.BRANCH_ID
																AND (DATEDIFF(DAY, CONVERT(DATE, TLS.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TLS.EffectiveDate IS NULL OR TLS.EffectiveDate = '')
																AND (DATEDIFF(DAY, CONVERT(DATE, TLS.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TLS.ExpirationDate IS NULL OR TLS.ExpirationDate = '')
																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 = TLS.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
																AND TLS.AUTH_STATUS = 'A'
																AND TLS.RECORD_STATUS = '1'
															)
												)

										)-- NEU ROLE NAM TRONG DS CAC ROLE WORKFLOW 1
								)	-- ngoac so 2, line 347
-- NGUOC LAI NEU LA TAM UNG KHAC
								OR	(	@p_TYPE_SEARCH ='HC' 
										AND @BRANCH_TYPE_LG ='HS' 
										AND A.REQ_TYPE <> 'I' 
										AND	(	A.TRASFER_USER_RECIVE = @p_USER_LOGIN 
												OR	(	EXISTS(SELECT TOP 1 A.ROLE_NAME FROM TMP_ROLE_ADVANCE_PAYMENT_SEARCH A WHERE A.ROLE_NAME IN ('GDDV','GDK','KTT','TPTC','PP','TBP','TP','TC') AND CHARINDEX(A.ROLE_NAME + ',', @ROLE_ID) > 0) 
														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	(	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 <>''
																	)
															)
													)  
												OR	(	EXISTS(SELECT TOP 1 A.ROLE_NAME FROM TMP_ROLE_ADVANCE_PAYMENT_SEARCH A WHERE A.ROLE_NAME NOT IN ('GDDV','GDK','KTT','TPTC','PP','TBP','TP','TC') AND CHARINDEX(A.ROLE_NAME + ',', @ROLE_ID) > 0) 
														AND (A.DEP_ID =@DEP_ID_LG OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH))
													)  
											)
									)
									-- HẾT KIỂM TRA LIÊN QUAN ĐẾN LOẠI TẠM ỨNG
								OR	(	@p_TYPE_SEARCH ='HC' 
										AND @BRANCH_TYPE_LG = 'CN' 
										AND (	EXISTS(SELECT TOP 1 A.ROLE_NAME FROM TMP_ROLE_ADVANCE_PAYMENT_SEARCH A WHERE A.ROLE_NAME IN ('GDDV','PGD','TPGD','PPGD') AND CHARINDEX(A.ROLE_NAME + ',', @ROLE_ID) > 0) 
												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 IN (SELECT * FROM @tmp)
											)
									)  
								OR	(	@p_TYPE_SEARCH ='HC' 
										AND @BRANCH_TYPE_LG = 'CN' 
										AND A.BRANCH_ID IN (SELECT * FROM @tmp)
									)  
								OR	(	@p_TYPE_SEARCH ='HC' 
										AND @BRANCH_TYPE_LG = 'PGD' 
										AND	(	EXISTS(SELECT TOP 1 A.ROLE_NAME FROM TMP_ROLE_ADVANCE_PAYMENT_SEARCH A WHERE A.ROLE_NAME IN ('GDDV','TPGD','PPGD','PGD') AND CHARINDEX(A.ROLE_NAME + ',', @ROLE_ID) > 0) 
												AND A.BRANCH_ID =@p_BRANCH_LOGIN
											)
									)  
								OR	(	@p_TYPE_SEARCH ='HC' 
										AND	(	A.DVDM_ID IS NOT NULL 
												AND A.DVDM_ID <>'' 
												AND A.DVDM_ID =@DVDM_ID 
												AND A.PROCESS IS NOT NULL 
												AND A.PROCESS <>'' 
												AND	EXISTS(SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.REQ_PAY_ID AND CHARINDEX(ROLE_USER + ',', @ROLE_ID) > 0)
											) 
										OR	(	A.DEP_ID IN (SELECT * FROM @TMP_DEP) 
												AND A.PROCESS IS NOT NULL 
												AND A.PROCESS <>''  
												AND CHARINDEX('TKTGD,', @ROLE_ID) = 0 
												AND EXISTS(SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.REQ_PAY_ID AND CHARINDEX(ROLE_USER + ',', @ROLE_ID) > 0)
											)
									)  
								--OR(@p_TYPE_SEARCH ='HC' AND (@ROLE_ID IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID= A.REQ_PAY_ID AND ROLE_USER IN ('TGD','HDQT','TKTGD','TKHDQT') AND STATUS ='C')))  
								OR	(	@p_TYPE_SEARCH='KT' 
										AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
									)  
								OR	(	@p_TYPE_SEARCH='HC' 
										AND	(	(CHARINDEX('KSV,', @ROLE_ID) > 0 OR CHARINDEX('GDV,', @ROLE_ID) > 0)  
												OR @DEP_ID_LG ='DEP000000000022'
											) 
										AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
									)  
								OR	(	@p_TYPE_SEARCH='HC' AND (	EXISTS	(	SELECT * FROM PL_REQUEST_PROCESS_CHILD 
																				WHERE REQ_ID = A.REQ_PAY_ID 
																				AND TLNAME =@p_USER_LOGIN 
																				AND TYPE_JOB ='XL' 
																				AND STATUS_JOB = 'C' 
																				AND ISNULL(A.AUTH_STATUS, '') <> 'A'
																			) 
																	OR EXISTS	(	SELECT * FROM PL_REQUEST_PROCESS_CHILD 
																					WHERE REQ_ID = A.REQ_PAY_ID 
																					AND TLNAME =@p_USER_LOGIN 
																					AND TYPE_JOB ='XL' 
																					AND STATUS_JOB = 'P' 
																					AND	(	ISNULL(A.AUTH_STATUS, '') = 'A'
																																																								OR EXISTS	(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND TYPE_JOB ='KS' AND STATUS_JOB = 'C')
																																																							)
																				)
																)
									) 
								OR	(	@p_TYPE_SEARCH IN ('TK','TKTGD','TKHDQT') 
										AND A.PROCESS<>'' 
										AND A.PROCESS IS NOT NULL
										AND (	(	@p_FUNCTION ='TF' AND  EXISTS	(	SELECT * FROM PL_REQUEST_PROCESS 
																					WHERE REQ_ID = A.REQ_PAY_ID 
																					AND CHARINDEX(ROLE_USER + ',', @ROLE_ID) > 0 
																					AND STATUS ='C'
																				)
												)
												OR	(	ISNULL(@p_FUNCTION,'') <> 'TF' 
														AND EXISTS(	SELECT * 
																	FROM PL_REQUEST_PROCESS 
																	WHERE REQ_ID = A.REQ_PAY_ID 
																	AND CHARINDEX(ROLE_USER + ',', @ROLE_ID) > 0 
																	AND (	(@p_IS_TRANSFER = N'N' AND STATUS ='C') 
																			OR	(	@p_IS_TRANSFER <>'N' 
																					AND STATUS IN ('C','P')
																				)
																		)
																	)
													)
											)
									)
								OR	(	@p_TYPE_SEARCH='PAY' 
										AND (	A.REQ_AMT -ISNULL(A.PAY_AMT,0)) >0 
										AND	(	(	@BRANCH_TYPE_LG ='HS' 
													AND	(	A.DEP_ID =@DEP_ID_LG 
															OR A.MAKER_ID =@p_USER_LOGIN 
															OR A.MAKER_ID IS NULL 
															OR 1=1
														)
												) 
												OR	(	@BRANCH_TYPE_LG <> 'HS' 
														AND A.BRANCH_ID = @p_BRANCH_LOGIN
													)
												OR	(	@p_REQ_PAY_CODE IS NOT NULL 
														AND @p_REQ_PAY_CODE <> '' 
														AND A.REQ_PAY_CODE =@p_REQ_PAY_CODE
													)
											)
									)  
							) -- ngoac so 1, line 347
						OR	(	@p_TYPE_SEARCH='HC' 
								AND	((CHARINDEX('KSV,', @ROLE_ID) > 0 OR CHARINDEX('GDV,', @ROLE_ID) > 0)  
										OR @DEP_ID_LG ='DEP000000000022'
									) AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
							)
					) -- line 346
			) -- line 344
		AND	(	(	A.PAY_AMT = A.REQ_AMT 
					AND (@p_TEMP_PAY_STATUS = 'HT')
				)  
				OR (A.PAY_AMT = 0 AND (@p_TEMP_PAY_STATUS = 'DCHU') )  
				OR (A.PAY_AMT <> 0 and A.PAY_AMT < A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'DHU'))  
				OR @p_TEMP_PAY_STATUS IS NULL OR @p_TEMP_PAY_STATUS = ''  
			)  
		AND	(	A.RECORD_STATUS ='1' AND A.AUTH_STATUS <>'X')
		ORDER BY A.CREATE_DT DESC  
-- PAGING END
	END;
END -- PAGING