DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

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

	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

	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)  

	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 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.*, 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

-- 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 

		(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.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 (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(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')

															) 

													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,'') ='' )

																					)

																			)

												)

										)-- 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 

																	)

															)

													)  

												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 

														)  

														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.*, 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

-- 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 

		(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.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(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')

															) 

													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,'') ='' )

																					)

																			)

												)

										)-- 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