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_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
	SET @p_TOP = NULL  
-- 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, @COST_LG VARCHAR(15), @DVDM_ID VARCHAR(15), @BRANCH_TYPE VARCHAR(15) 
-- 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
	SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)  
-- KHAI BAO UY QUYEN KIEM NHIEM
	DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))  
	INSERT INTO @TABLE_ROLE VALUES (@ROLE_ID)
	INSERT INTO @TABLE_ROLE SELECT ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =@ROLE_ID  
	AND (CONVERT(DATE,GETDATE(),103) = CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL OR EFF_DATE ='')
	AND (CONVERT(DATE, GETDATE(),103) =CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='') AND TLNAME =@p_USER_LOGIN

	--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) 
	
	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 TL_SYS_ROLE_MAPPING WHERE TLNAME =@P_USER_LOGIN  
	AND (CONVERT(DATE,GETDATE(),103) = CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL OR EFF_DATE ='')
	AND (CONVERT(DATE, GETDATE(),103) =CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='')

	DECLARE @BRANCH_AUTH TABLE (BRN_AUTH VARCHAR(15))
	INSERT INTO @BRANCH_AUTH VALUES (@BRANCH_TYPE_LG)
	INSERT INTO @BRANCH_AUTH SELECT BRANCH_ID FROM TL_SYS_ROLE_MAPPING WHERE TLNAME =@P_USER_LOGIN 
	AND CONVERT(DATE,GETDATE(),103) = CONVERT(DATE,EFF_DATE, 103) 
	AND CONVERT(DATE, GETDATE(),103) =CONVERT(DATE,EXP_DATE, 103)

	--END
	--IF(EXISTS(SELECT  FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@ROLE_ID))  
	-- SET @ROLE_ID=(SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@ROLE_ID)  

-- LAY ROLE USER LOGIN
	SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)  
	IF(@ROLE_ID IS NOT NULL AND @ROLE_ID '' AND @ROLE_ID IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD','TKTGD','TKHDQT'))  
	BEGIN  
		PRINT @ROLE_ID  
	END  
	ELSE  
	BEGIN  
		SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_USER_LOGIN)  
		IF(@ROLE_ID IS NULL OR @ROLE_ID ='')  
		BEGIN  
			SET @ROLE_ID =(SELECT TOP 1 ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT TOP 1 ROLENAME FROM TL_USER WHERE TLNANME=@p_USER_LOGIN))  
		END  
	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.,'' 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,  
		BR.BRANCH_NAME 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--20191015-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,'ddMMyyyy Hmmss'),'') 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

-- 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--20191015-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ánNợ)  
		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ặtChuyể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  
		--LEFT JOIN PL_PROCESS PR ON A.PROCESS = PR.PROCESS_ID AND A.REQ_PAY_ID = PR.REQ_ID  
		LEFT JOIN  
		(  
		SELECT P.REQ_ID, P.PROCESS_DESC, P.APPROVE_DT AS APP_DT FROM PL_PROCESS P 
		INNER JOIN  
		(  
			SELECT MAX (PR.ID) AS ID, MAX(PR.REQ_ID) AS REQ_ID FROM PL_PROCESS PR
			GROUP BY PR.REQ_ID  
		) PR ON PR.REQ_ID = P.REQ_ID AND P.ID = PR.ID  
		--WHERE P.REQ_ID =@p_REQ_PAY_ID
		) AS PR ON A.REQ_PAY_ID = PR.REQ_ID  
		LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND 
		(PC.ROLE_USER = @ROLE_ID 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  
		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.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-20191014-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-20191017-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 (	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_TRASFER_USER_RECIVE
											)
									)
						) 
					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','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(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(PROCESS,'')='') 
															OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS =''
														)
													)
											) 
											OR	(	@ROLE_ID IN ('NVMS','NVTT','TBP') 
													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','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')
																				)
																		)
																)
												)
											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,'') ='' )
																					)
																			)
												)

										)-- 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	(	@ROLE_ID IN ('GDDV','GDK','KTT','TPTC','PP','TBP','TP','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	(	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	(	@ROLE_ID NOT IN ('GDDV','GDK','KTT','TPTC','PP','TBP','TP','TC') 
														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 (	@ROLE_ID IN ('GDDV','PGD','TPGD','PPGD') 
												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	(	@ROLE_ID IN ('GDDV','TPGD','PPGD','PGD') 
												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	@ROLE_ID IN	(	SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID)
											) 
										OR	(	A.DEP_ID IN (SELECT  FROM @TMP_DEP) 
												AND A.PROCESS IS NOT NULL 
												AND A.PROCESS ''  
												AND @ROLE_ID  'TKTGD' 
												AND @ROLE_ID IN	(	SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID)
											)
									)  
								--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	(	@ROLE_ID IN ('KSV','GDV')  
												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 ROLE_USER =@ROLE_ID 
																					AND STATUS ='C'
																				)
												)
												OR	(	ISNULL(@p_FUNCTION,'')  'TF' 
														AND EXISTS(	SELECT  
																	FROM PL_REQUEST_PROCESS 
																	WHERE REQ_ID = A.REQ_PAY_ID 
																	AND ROLE_USER =@ROLE_ID 
																	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	(	@ROLE_ID IN ('KSV','GDV')  
										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.,'' 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,  
		BR.BRANCH_NAME 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--20191015-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,'ddMMyyyy Hmmss'),'') 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
-- 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--20191015-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ánNợ)  
		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ặtChuyể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  
		--LEFT JOIN PL_PROCESS PR ON A.PROCESS = PR.PROCESS_ID AND A.REQ_PAY_ID = PR.REQ_ID  
		LEFT JOIN  
		(  
		SELECT P.REQ_ID, P.PROCESS_DESC, P.APPROVE_DT AS APP_DT FROM PL_PROCESS P 
		INNER JOIN  
		(  
			SELECT MAX (PR.ID) AS ID, MAX(PR.REQ_ID) AS REQ_ID FROM PL_PROCESS PR
			GROUP BY PR.REQ_ID  
		) PR ON PR.REQ_ID = P.REQ_ID AND P.ID = PR.ID  
		--WHERE P.REQ_ID =@p_REQ_PAY_ID
		) AS PR ON A.REQ_PAY_ID = PR.REQ_ID  
		LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND 
		(PC.ROLE_USER = @ROLE_ID 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  
		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.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-20191014-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-20191017-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 (	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_TRASFER_USER_RECIVE
											)
									)
						) 
					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','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(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(PROCESS,'')='') 
															OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS =''
														)
													)
											) 
											OR	(	@ROLE_ID IN ('NVMS','NVTT','TBP') 
													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','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')
																				)
																		)
																)
												)
											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,'') ='' )
																					)
																			)
												)

										)-- 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	(	@ROLE_ID IN ('GDDV','GDK','KTT','TPTC','PP','TBP','TP','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	(	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	(	@ROLE_ID NOT IN ('GDDV','GDK','KTT','TPTC','PP','TBP','TP','TC') 
														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 (	@ROLE_ID IN ('GDDV','PGD','TPGD','PPGD') 
												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	(	@ROLE_ID IN ('GDDV','TPGD','PPGD','PGD') 
												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	@ROLE_ID IN	(	SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID)
											) 
										OR	(	A.DEP_ID IN (SELECT  FROM @TMP_DEP) 
												AND A.PROCESS IS NOT NULL 
												AND A.PROCESS ''  
												AND @ROLE_ID  'TKTGD' 
												AND @ROLE_ID IN	(	SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID)
											)
									)  
								--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	(	@ROLE_ID IN ('KSV','GDV')  
												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 ROLE_USER =@ROLE_ID 
																					AND STATUS ='C'
																				)
												)
												OR	(	ISNULL(@p_FUNCTION,'')  'TF' 
														AND EXISTS(	SELECT  
																	FROM PL_REQUEST_PROCESS 
																	WHERE REQ_ID = A.REQ_PAY_ID 
																	AND ROLE_USER =@ROLE_ID 
																	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	(	@ROLE_ID IN ('KSV','GDV')  
										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