ALTER  PROCEDURE [dbo].[TR_REQ_PAYMENT_2_Search]
@p_REQ_PAY_ID					VARCHAR(15)= NULL,
@p_REQ_PAY_CODE					VARCHAR(50)	= NULL,
@p_CONTRACT_ID					VARCHAR(15)= NULL,
@p_REQ_DT						VARCHAR(20)= NULL,
@p_BRANCH_ID					VARCHAR(15)	= NULL,
@p_DEP_ID						VARCHAR(15)	= NULL,
@p_REQ_REASON					NVARCHAR(MAX)	= NULL,
@p_REQ_TYPE						VARCHAR(15)	= NULL,
@P_REQ_ENTRIES					NVARCHAR(MAX)	= NULL,
@p_REQ_DESCRIPTION				NVARCHAR(MAX)	= NULL,
@p_REF_ID						VARCHAR(15)	= NULL,
@p_RECEIVER_PO					NVARCHAR(250)	= NULL,
@p_REQ_PAY_TYPE					VARCHAR(15)	= NULL,
@p_REQ_TYPE_CURRENCY			NVARCHAR(50)	= NULL,
@p_REQ_AMT						DECIMAL(18, 0)	= NULL,
@p_REQ_TEMP_AMT					DECIMAL(18, 0)	= NULL,
@p_MAKER_ID						VARCHAR(15)	= NULL,
@p_CREATE_DT					VARCHAR(25)	= NULL,
@p_EDITOR_ID					VARCHAR(15)	= NULL,
@p_AUTH_STATUS					VARCHAR(1)	= NULL,
@p_CHECKER_ID					VARCHAR(15)	= NULL,
@p_APPROVE_DT					VARCHAR(25)	= NULL,
@p_CREATE_DT_KT					VARCHAR(25)	= NULL,
@p_MAKER_ID_KT					VARCHAR(15)	= NULL,
@p_AUTH_STATUS_KT				VARCHAR(1)	= NULL,
@p_CHECKER_ID_KT				NVARCHAR(20)	= NULL,
@p_EXEC_USER_KT					NVARCHAR(20)	= NULL,
@p_APPROVE_DT_KT				VARCHAR(25)= NULL,
@p_CONFIRM_NOTE					NVARCHAR(500)	= NULL,
@p_BRANCH_CREATE				VARCHAR(15)	= NULL,
@p_NOTES						VARCHAR(15)	= NULL,
@p_RECORD_STATUS				VARCHAR(1)	= NULL,
@p_TRANSFER_MAKER				NVARCHAR(50)= NULL,
@p_TRANSFER_DT					VARCHAR(25)	= NULL,
@p_TRASFER_USER_RECIVE			VARCHAR(15)	= NULL,
@p_PROCESS						VARCHAR(15)	= NULL,
@p_PAY_PHASE					VARCHAR(15) = NULL,
@p_TOP							INT = 300,
@p_LEVEL						VARCHAR(10) = NULL,
@p_FRMDATE						VARCHAR(20)= NULL,
@p_TODATE						VARCHAR(20) = NULL,
@p_BRANCH_LOGIN					VARCHAR(15) = NULL,
@p_IS_UPDATE_KT					VARCHAR(15) = NULL,
@P_IS_TRANSFER					VARCHAR(15) = NULL,
@p_TERM_ID						VARCHAR(15) = NULL,
@P_USER_LOGIN					VARCHAR(15)= NULL,
@p_FUNCTION						VARCHAR(15) = NULL,
@p_TYPE_SEARCH					VARCHAR(15) = NULL,
@p_REQ_PAY_AUTO_TYPE			VARCHAR(15) = NULL,
@p_REQ_PAY_AUTO_SERVICE_TYPE	VARCHAR(15) = NULL,
@p_TR_REQ_PAY_AUTO_CHECK_STATUS	VARCHAR(15) = NULL
AS
BEGIN -- PAGING
-- BEGIN KHAI BÁO
	SET @p_LEVEL = 'UNIT'
	IF(ISNULL(@p_BRANCH_ID, '') = '')
	BEGIN
		SET @p_BRANCH_ID = @p_BRANCH_CREATE
	END
	IF(ISNULL(@p_BRANCH_ID, '') <> 'DV0001')
	BEGIN
		SET @p_DEP_ID = NULL
	END
-- ĐƠN VỊ YÊU CẦU
	DECLARE @tmp_branch TABLE(BRANCH_ID VARCHAR(15))
	DECLARE @tmp_dep TABLE(DEP_ID VARCHAR(15))
	DECLARE @DEP_ID VARCHAR(15) = NULL
	INSERT INTO @tmp_branch  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
	DECLARE @BRANCH_TYPE VARCHAR(15)
	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))

-- ĐƠN VỊ ĐĂNG NHẬP
	DECLARE @tmp_branch_login TABLE(BRANCH_ID VARCHAR(15))
	DECLARE @tmp_dep_login TABLE(DEP_ID VARCHAR(15))
	INSERT INTO @tmp_branch_login  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
	INSERT INTO @tmp_branch_login VALUES (@p_BRANCH_LOGIN)
	INSERT INTO @tmp_branch_login	SELECT BRANCH_ID 
									FROM TL_SYS_ROLE_MAPPING 
									WHERE TLNAME =@P_USER_LOGIN 
									AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103)  OR ISNULL(EFF_DATE, '') = '')
									AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103 )  OR ISNULL(EXP_DATE, '') = '')
	DECLARE @BRANCH_TYPE_LG VARCHAR(15)
	SET @BRANCH_TYPE_LG = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN)
-- PHÒNG BAN ĐĂNG NHẬP
	DECLARE @DEP_ID_LG VARCHAR(15) = NULL
	SET @DEP_ID_LG =(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)
	INSERT INTO @tmp_dep_login VALUES (@DEP_ID_LG)
	INSERT INTO @tmp_dep_login SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID =@DEP_ID_LG
	INSERT INTO @tmp_dep_login	SELECT DEP_ID 
								FROM TL_SYS_ROLE_MAPPING 
								WHERE TLNAME =@P_USER_LOGIN 
								AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103)  OR ISNULL(EFF_DATE, '') = '')
								AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103 )  OR ISNULL(EXP_DATE, '') = '')
-- ĐƠN VỊ ỦY QUYỀN KIÊM NHIỆM
DECLARE @tmp_branch_auth TABLE (BRANCH_ID VARCHAR(15))
INSERT INTO @tmp_branch_auth VALUES (@p_BRANCH_LOGIN)
INSERT INTO @tmp_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)  OR ISNULL(EFF_DATE, '') = '')
								AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103 )  OR ISNULL(EXP_DATE, '') = '')
-- PHÒNG BAN ỦY QUYỀN KIÊM NHIỆM
DECLARE @tmp_dep_auth TABLE (DEP_ID VARCHAR(15))
INSERT INTO @tmp_dep_auth VALUES (@DEP_ID_LG)
INSERT INTO @tmp_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 ISNULL(EFF_DATE, '') = '')
							AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103 )  OR ISNULL(EXP_DATE, '') = '')
INSERT INTO @tmp_dep_auth SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID =@DEP_ID_LG

-- ROLE
	DECLARE @ROLE_ID VARCHAR(20)
	SET @ROLE_ID = (SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)
	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 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 ='') 
	
-- NEU USER KHONG CHON TU NGAY THI TU NGAY BANG NGAY 1 1 HANG THANG 20211116
	DECLARE @DATE DATE
	IF ((@p_FRMDATE IS NULL OR @p_FRMDATE ='') AND ISNULL(@p_REQ_PAY_ID,'')='' AND @p_AUTH_STATUS ='') -- MỤC ĐÍCH NẾU NHƯ XEM CHI TIẾT 1 PĐN THANH TOÁN DẠNG POPUP THÌ BỎ QUA ĐIỀU KIỆN NÀY, TỪ NGÀY VẪN LÀ NULL
	BEGIN
		SET @DATE = CONVERT(DATE,GETDATE(),103)
		SET @DATE = DATEADD(MONTH,-2,@DATE)
	END
	ELSE
	BEGIN
		SET @DATE = CONVERT(DATE,@p_FRMDATE,103)
	END
--DOANPTT: XÁC ĐỊNH USER XEM TOÀN BỘ PHIẾU
	DECLARE @IS_VIEW_ALL VARCHAR(1) 
	IF((SELECT COUNT(*) FROM @TABLE_ROLE WHERE ROLE_AUTH IN ('KSV', 'GDV', 'NVTC')) > 0)		-- ROLE CHỈ ĐỊNH
	BEGIN
		SET @IS_VIEW_ALL = 'Y'
	END
	ELSE IF(@DEP_ID_LG = 'DEP000000000022')														-- PHÒNG CHỈ ĐỊNH
	BEGIN
		SET @IS_VIEW_ALL = 'Y'
	END
	ELSE IF(@P_USER_LOGIN = 'baotq')															-- USER CHỈ ĐỊNH
	BEGIN
		SET @IS_VIEW_ALL = 'Y'
	END
	ELSE
	BEGIN
		SET @IS_VIEW_ALL = 'N'
	END

-- END KHAI BÁO

IF(@p_DEP_ID = 'DEP000000000022' OR @DEP_ID_LG = 'DEP000000000022')
BEGIN
	IF(@p_TOP IS NULL OR @p_TOP=0)
	BEGIN
	-- PAGING BEGIN
			SELECT A.*,
			BR.BRANCH_CODE BRANCH_CODE_REQ,
			CASE WHEN ISNULL(A.IS_PERIOD,'') <> '' AND A.IS_PERIOD ='Y' AND  EXISTS (SELECT TOP 1 X.* FROM TR_REQ_ADVANCE_DT X 
											INNER JOIN TR_CONTRACT CT ON X.REF_ID = CT.CONTRACT_ID
											WHERE X.REQ_PAY_ID = A.REQ_PAY_ID AND CT.BRANCH_ID <>  @p_BRANCH_LOGIN AND CT.BRANCH_ID IS NOT NULL AND CT.BRANCH_ID <> '') THEN 
											BR.BRANCH_NAME + ISNULL(' - '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT 
											WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
											ELSE BR.BRANCH_NAME END BRANCH_NAME_REQ,
			BR1.BRANCH_NAME BRANCH_NAME_CRE, BR1.BRANCH_CODE BRANCH_CODE_CRE, CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR,
			DP.DEP_NAME, DP.DEP_CODE, 
			B.AUTH_STATUS_NAME, 
			C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, C.AUTH_STATUS_KT_DESC, 
			TL.TLFullName CREATE_FULLNAME,
			TL1.TLFullName APPROVE_FULLNAME,
			CASE WHEN ISNULL(TL2.TLFullName,'') <> '' THEN TL2.TLFullName ELSE TL6.TLFullName END AS CREATE_FULLNAME_KT,
			TL3.TLFullName APPROVE_FULLNAME_KT,
			TL4.TLFullName AS TRANSFER_FULLNAME,
			TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,
			AL.CONTENT REQ_TYPE_NAME, 
			AL1.CONTENT REQ_PAY_TYPE_NAME,
			PO.PO_CODE, PO.PO_NAME, 
			S.SUP_NAME, S.TAX_NO SUP_TAX_NO,
			PC3.LEVEL_JOB, PC3.STATUS_JOB, PC3.TYPE_JOB, PC3.TLNAME AS EXEC_USER_KT,
			CASE WHEN PC3.TLNAME IS NOT NULL THEN PC3.TLNAME 
				 WHEN PC3.TLNAME <>'' THEN PC3.TLNAME 
				 ELSE TL2.TLNANME END AS EXEC_USER,
			SR.ROLE_ID AS ROLE_ID_CRE,
			BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE,
			'' AS BRANCH_CODE_CONTRACT,
			ISNULL((SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
			AS BRANCH_NAME_CONTRACT,
			L.REQ_PAY_AUTO_ID, L.REQ_PAY_AUTO_CODE
	-- SELECT END
			FROM TR_REQ_PAYMENT A
				LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
				LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
				LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
				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	-- NGUOI TAO
				LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME	-- NGUOI DUYET
				LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME	-- GDV
				LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME	-- KSV
				LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME	-- KSV DIEU PHOI
				LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME	-- DUYET TRUNG GIAN
				LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE' AND AL.CDTYPE = 'REQ'
				LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE' AND AL1.CDTYPE ='REQ'
				LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
				LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = PO.SUP_ID AND A.REQ_TYPE='P'
				LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C' -- GDV
				LEFT JOIN PL_REQUEST_PROCESS_CHILD PC5 ON PC5.REQ_ID = A.REQ_PAY_ID AND PC5.TYPE_JOB ='XL'	-- KSV
				LEFT JOIN TL_USER TL6 ON PC5.TLNAME = TL6.TLNANME
				LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
				LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID
				LEFT JOIN TR_REQ_PAY_AUTO_PAYMENTS K ON A.REQ_PAY_ID = K.REQ_PAY_ID
				LEFT JOIN TR_REQ_PAYMENT_AUTO L ON K.REQ_PAY_AUTO_ID = L.REQ_PAY_AUTO_ID
			WHERE 1=1 
				AND ISNULL(A.IS_CREATE_AUTO, '') = 'Y'
			-- BEGIN FILTER
				AND	(	A.REQ_PAY_ID = @p_REQ_PAY_ID OR ISNULL(@p_REQ_PAY_ID, '') = '')
				AND	(	A.REQ_PAY_CODE LIKE N'%'+ @p_REQ_PAY_CODE + '%' OR ISNULL(@p_REQ_PAY_CODE, '') = '')
				AND	(	L.CONTRACT_ID LIKE N'%'+ @p_CONTRACT_ID + '%' OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_REASON LIKE N'%'+ @p_REQ_REASON + '%' OR ISNULL(@p_REQ_REASON, '') = '')
				AND	(	L.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				AND	(	L.REQ_PAY_AUTO_SERVICE_TYPE = @p_REQ_PAY_AUTO_SERVICE_TYPE OR ISNULL(@p_REQ_PAY_AUTO_SERVICE_TYPE, '') = '')
				AND(	CONVERT(DATE, A.CREATE_DT) >= CONVERT(DATE, @p_FRMDATE, 103) OR ISNULL(@p_FRMDATE, '')= '' OR A.CREATE_DT IS NULL)
				AND(	CONVERT(DATE, A.CREATE_DT) <= CONVERT(DATE, @p_TODATE, 103) OR ISNULL(@p_TODATE, '')= '' OR A.CREATE_DT IS NULL)
				AND	(	L.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				AND	(	A.TR_REQ_PAY_AUTO_CHECK_STATUS = @p_TR_REQ_PAY_AUTO_CHECK_STATUS OR ISNULL(@p_TR_REQ_PAY_AUTO_CHECK_STATUS, '') = '')
				AND	(	A.BRANCH_ID = @p_BRANCH_ID  
						OR EXISTS	(	SELECT * 
											FROM TR_REQ_ADVANCE_DT 
											WHERE REQ_PAY_ID = A.REQ_PAY_ID
											AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID = @p_BRANCH_ID) 
											AND A.AUTH_STATUS_KT ='A'
										)
						OR ISNULL(@p_BRANCH_ID, '') = '' 
						OR @p_BRANCH_ID = 'DV0001' 
					)
				AND	(	A.BRANCH_ID = @p_BRANCH_LOGIN	-- PHIEU CUA DON VI MINH
						OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PHIEU CUA DON VI KIEM NHIEM
						OR @IS_VIEW_ALL = 'Y'
					)
				AND	(	A.DEP_ID IN	(SELECT * FROM @tmp_dep_auth)
						OR A.BRANCH_ID <> 'DV0001'
						OR @IS_VIEW_ALL = 'Y'
					)
			-- END FILTER
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
		END;
	ELSE 
	BEGIN
	-- PAGING BEGIN
		SELECT TOP(CONVERT(INT,@p_TOP)) A.*,
		BR.BRANCH_CODE BRANCH_CODE_REQ,
		CASE WHEN ISNULL(A.IS_PERIOD,'') <> '' AND A.IS_PERIOD ='Y' AND  EXISTS (SELECT TOP 1 X.* FROM TR_REQ_ADVANCE_DT X 
										INNER JOIN TR_CONTRACT CT ON X.REF_ID = CT.CONTRACT_ID
										WHERE X.REQ_PAY_ID = A.REQ_PAY_ID AND CT.BRANCH_ID <>  @p_BRANCH_LOGIN AND CT.BRANCH_ID IS NOT NULL AND CT.BRANCH_ID <> '') THEN 
										BR.BRANCH_NAME + ISNULL(' - '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT 
										WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
										ELSE BR.BRANCH_NAME END BRANCH_NAME_REQ,
		BR1.BRANCH_NAME BRANCH_NAME_CRE, BR1.BRANCH_CODE BRANCH_CODE_CRE, CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR,
		DP.DEP_NAME, DP.DEP_CODE, 
		B.AUTH_STATUS_NAME, 
		C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, C.AUTH_STATUS_KT_DESC, 
		TL.TLFullName CREATE_FULLNAME,
		TL1.TLFullName APPROVE_FULLNAME,
		CASE WHEN ISNULL(TL2.TLFullName,'') <>'' THEN TL2.TLFullName ELSE TL6.TLFullName END AS CREATE_FULLNAME_KT,
		TL3.TLFullName APPROVE_FULLNAME_KT,
		TL4.TLFullName AS TRANSFER_FULLNAME,
		TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,
		AL.CONTENT REQ_TYPE_NAME, 
		AL1.CONTENT REQ_PAY_TYPE_NAME,
		PO.PO_CODE, PO.PO_NAME, 
		S.SUP_NAME, S.TAX_NO SUP_TAX_NO,
		PC3.LEVEL_JOB, PC3.STATUS_JOB, PC3.TYPE_JOB, PC3.TLNAME AS EXEC_USER_KT,
		CASE WHEN PC3.TLNAME IS NOT NULL THEN PC3.TLNAME 
				WHEN PC3.TLNAME <>'' THEN PC3.TLNAME 
				ELSE TL2.TLNANME END AS EXEC_USER,
		SR.ROLE_ID AS ROLE_ID_CRE,
		BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE,'' AS BRANCH_CODE_CONTRACT,

		ISNULL((SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
		AS BRANCH_NAME_CONTRACT,
		L.REQ_PAY_AUTO_ID, L.REQ_PAY_AUTO_CODE
	-- SELECT END
		FROM TR_REQ_PAYMENT A
			LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
			LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
			LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
			LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
			LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
			LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
			LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
			LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
			--Luanlt--2019/10/15-Sửa AL,AL1
			LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE' AND AL.CDTYPE = 'REQ'
			LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE' AND AL1.CDTYPE = 'REQ'
			LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
			LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
			LEFT JOIN 
			(
			SELECT PAY_ADV_ID, SUM(AMT_PAY) SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
			) H ON A.REQ_PAY_ID =H.PAY_ADV_ID
			LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
			LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = PO.SUP_ID AND A.REQ_TYPE='P'
			LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C' 
			LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
			LEFT JOIN PL_REQUEST_PROCESS_CHILD PC5 ON PC5.REQ_ID = A.REQ_PAY_ID AND PC5.TYPE_JOB ='XL'
			LEFT JOIN TL_USER TL6 ON PC5.TLNAME = TL6.TLNANME
			LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
			LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID
			LEFT JOIN TR_REQ_PAY_AUTO_PAYMENTS K ON A.REQ_PAY_ID = K.REQ_PAY_ID
			LEFT JOIN TR_REQ_PAYMENT_AUTO L ON K.REQ_PAY_AUTO_ID = L.REQ_PAY_AUTO_ID
		WHERE 1=1 
			AND ISNULL(A.IS_CREATE_AUTO, '') = 'Y'
		-- BEGIN FILTER
			AND	(	A.REQ_PAY_ID = @p_REQ_PAY_ID OR ISNULL(@p_REQ_PAY_ID, '') = '')
			AND	(	A.REQ_PAY_CODE LIKE N'%'+ @p_REQ_PAY_CODE + '%' OR ISNULL(@p_REQ_PAY_CODE, '') = '')
			AND	(	L.CONTRACT_ID LIKE N'%'+ @p_CONTRACT_ID + '%' OR ISNULL(@p_CONTRACT_ID, '') = '')
			AND	(	A.REQ_REASON LIKE N'%'+ @p_REQ_REASON + '%' OR ISNULL(@p_REQ_REASON, '') = '')
			AND	(	L.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
			AND	(	L.REQ_PAY_AUTO_SERVICE_TYPE = @p_REQ_PAY_AUTO_SERVICE_TYPE OR ISNULL(@p_REQ_PAY_AUTO_SERVICE_TYPE, '') = '')
			AND(	CONVERT(DATE, A.CREATE_DT) >= CONVERT(DATE, @p_FRMDATE, 103) OR ISNULL(@p_FRMDATE, '')= '' OR A.CREATE_DT IS NULL)
			AND(	CONVERT(DATE, A.CREATE_DT) <= CONVERT(DATE, @p_TODATE, 103) OR ISNULL(@p_TODATE, '')= '' OR A.CREATE_DT IS NULL)
			AND	(	L.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
			AND	(	A.TR_REQ_PAY_AUTO_CHECK_STATUS = @p_TR_REQ_PAY_AUTO_CHECK_STATUS OR ISNULL(@p_TR_REQ_PAY_AUTO_CHECK_STATUS, '') = '')
			AND	(	A.BRANCH_ID = @p_BRANCH_ID  
					OR ISNULL((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)),'') = @p_BRANCH_ID
					OR ISNULL(@p_BRANCH_ID, '') = '' 
					OR @p_BRANCH_ID = 'DV0001' 
				)
			AND	(	A.BRANCH_ID = @p_BRANCH_LOGIN	-- PHIEU CUA DON VI MINH
					OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PHIEU CUA DON VI KIEM NHIEM
					OR @IS_VIEW_ALL = 'Y'
				)
			AND	(	A.DEP_ID IN	(SELECT * FROM @tmp_dep_auth)
					OR A.BRANCH_ID <> 'DV0001'
					OR @IS_VIEW_ALL = 'Y'
				)
		-- END FILTER
			  
		ORDER BY A.CREATE_DT DESC
	-- PAGING END
	END;

END
ELSE
BEGIN
	IF(@p_TOP IS NULL OR @p_TOP=0)
	BEGIN
	-- PAGING BEGIN
			SELECT A.*,
			BR.BRANCH_CODE BRANCH_CODE_REQ,
			CASE WHEN ISNULL(A.IS_PERIOD,'') <> '' AND A.IS_PERIOD ='Y' AND  EXISTS (SELECT TOP 1 X.* FROM TR_REQ_ADVANCE_DT X 
											INNER JOIN TR_CONTRACT CT ON X.REF_ID = CT.CONTRACT_ID
											WHERE X.REQ_PAY_ID = A.REQ_PAY_ID AND CT.BRANCH_ID <>  @p_BRANCH_LOGIN AND CT.BRANCH_ID IS NOT NULL AND CT.BRANCH_ID <> '') THEN 
											BR.BRANCH_NAME + ISNULL(' - '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT 
											WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
											ELSE BR.BRANCH_NAME END BRANCH_NAME_REQ,
			BR1.BRANCH_NAME BRANCH_NAME_CRE, BR1.BRANCH_CODE BRANCH_CODE_CRE, CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR,
			DP.DEP_NAME, DP.DEP_CODE, 
			B.AUTH_STATUS_NAME, 
			C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, C.AUTH_STATUS_KT_DESC, 
			TL.TLFullName CREATE_FULLNAME,
			TL1.TLFullName APPROVE_FULLNAME,
			CASE WHEN ISNULL(TL2.TLFullName,'') <> '' THEN TL2.TLFullName ELSE TL6.TLFullName END AS CREATE_FULLNAME_KT,
			TL3.TLFullName APPROVE_FULLNAME_KT,
			TL4.TLFullName AS TRANSFER_FULLNAME,
			TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,
			AL.CONTENT REQ_TYPE_NAME, 
			AL1.CONTENT REQ_PAY_TYPE_NAME,
			PO.PO_CODE, PO.PO_NAME, 
			S.SUP_NAME, S.TAX_NO SUP_TAX_NO,
			PC3.LEVEL_JOB, PC3.STATUS_JOB, PC3.TYPE_JOB, PC3.TLNAME AS EXEC_USER_KT,
			CASE WHEN PC3.TLNAME IS NOT NULL THEN PC3.TLNAME 
				 WHEN PC3.TLNAME <>'' THEN PC3.TLNAME 
				 ELSE TL2.TLNANME END AS EXEC_USER,
			SR.ROLE_ID AS ROLE_ID_CRE,
			BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE,
			'' AS BRANCH_CODE_CONTRACT,
			ISNULL((SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
			AS BRANCH_NAME_CONTRACT,
			L.REQ_PAY_AUTO_ID, L.REQ_PAY_AUTO_CODE
	-- SELECT END
			FROM TR_REQ_PAYMENT A
				LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
				LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
				LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
				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	-- NGUOI TAO
				LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME	-- NGUOI DUYET
				LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME	-- GDV
				LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME	-- KSV
				LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME	-- KSV DIEU PHOI
				LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME	-- DUYET TRUNG GIAN
				LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE' AND AL.CDTYPE = 'REQ'
				LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE' AND AL1.CDTYPE ='REQ'
				LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
				LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = PO.SUP_ID AND A.REQ_TYPE='P'
				LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C' -- GDV
				LEFT JOIN PL_REQUEST_PROCESS_CHILD PC5 ON PC5.REQ_ID = A.REQ_PAY_ID AND PC5.TYPE_JOB ='XL'	-- KSV
				LEFT JOIN TL_USER TL6 ON PC5.TLNAME = TL6.TLNANME
				LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
				LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID
				LEFT JOIN TR_REQ_PAY_AUTO_PAYMENTS K ON A.REQ_PAY_ID = K.REQ_PAY_ID
				LEFT JOIN TR_REQ_PAYMENT_AUTO L ON K.REQ_PAY_AUTO_ID = L.REQ_PAY_AUTO_ID
			WHERE 1=1 
				AND ISNULL(A.IS_CREATE_AUTO, '') = 'Y'
			-- BEGIN FILTER
				AND	(	A.REQ_PAY_ID = @p_REQ_PAY_ID OR ISNULL(@p_REQ_PAY_ID, '') = '')
				AND	(	A.REQ_PAY_CODE LIKE N'%'+ @p_REQ_PAY_CODE + '%' OR ISNULL(@p_REQ_PAY_CODE, '') = '')
				AND	(	L.CONTRACT_ID LIKE N'%'+ @p_CONTRACT_ID + '%' OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_REASON LIKE N'%'+ @p_REQ_REASON + '%' OR ISNULL(@p_REQ_REASON, '') = '')
				AND	(	L.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				AND	(	L.REQ_PAY_AUTO_SERVICE_TYPE = @p_REQ_PAY_AUTO_SERVICE_TYPE OR ISNULL(@p_REQ_PAY_AUTO_SERVICE_TYPE, '') = '')
				AND(	CONVERT(DATE, A.CREATE_DT) >= CONVERT(DATE, @p_FRMDATE, 103) OR ISNULL(@p_FRMDATE, '')= '' OR A.CREATE_DT IS NULL)
				AND(	CONVERT(DATE, A.CREATE_DT) <= CONVERT(DATE, @p_TODATE, 103) OR ISNULL(@p_TODATE, '')= '' OR A.CREATE_DT IS NULL)
				AND	(	L.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				AND	(	A.TR_REQ_PAY_AUTO_CHECK_STATUS = @p_TR_REQ_PAY_AUTO_CHECK_STATUS OR ISNULL(@p_TR_REQ_PAY_AUTO_CHECK_STATUS, '') = '')
				AND	(	(	@p_LEVEL='ALL' 
							AND A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp_branch) 
						)
						OR	(	@p_LEVEL='UNIT' 
								AND A.BRANCH_ID = @p_BRANCH_ID
							)
						OR	(	@BRANCH_TYPE_LG <> 'HS' AND
								EXISTS	(	SELECT * 
											FROM TR_REQ_ADVANCE_DT 
											WHERE REQ_PAY_ID = A.REQ_PAY_ID
											AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID = @p_BRANCH_LOGIN) 
											AND A.AUTH_STATUS_KT ='A'
										)
							)
						OR A.BRANCH_CREATE IN (SELECT * FROM @tmp_branch_auth)
						OR (@p_BRANCH_ID= '' OR @p_BRANCH_ID IS NULL) 
					)
				AND	(	ISNULL(@p_DEP_ID, '') = ''	-- NEU KHONG CHON PHONG BAN THI THOI
						OR A.DEP_ID = @p_DEP_ID		-- PDN PHONG BAN CUA MINH
						OR A.DEP_ID IN	(	SELECT * FROM @tmp_dep_auth	)	-- PDN PHONG BAN KIEM NHIEM
					)
			-- END FILTER
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
		END;
	ELSE 
	BEGIN
	-- PAGING BEGIN
		SELECT TOP(CONVERT(INT,@p_TOP)) A.*,
		BR.BRANCH_CODE BRANCH_CODE_REQ,
		CASE WHEN ISNULL(A.IS_PERIOD,'') <> '' AND A.IS_PERIOD ='Y' AND  EXISTS (SELECT TOP 1 X.* FROM TR_REQ_ADVANCE_DT X 
										INNER JOIN TR_CONTRACT CT ON X.REF_ID = CT.CONTRACT_ID
										WHERE X.REQ_PAY_ID = A.REQ_PAY_ID AND CT.BRANCH_ID <>  @p_BRANCH_LOGIN AND CT.BRANCH_ID IS NOT NULL AND CT.BRANCH_ID <> '') THEN 
										BR.BRANCH_NAME + ISNULL(' - '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT 
										WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
										ELSE BR.BRANCH_NAME END BRANCH_NAME_REQ,
		BR1.BRANCH_NAME BRANCH_NAME_CRE, BR1.BRANCH_CODE BRANCH_CODE_CRE, CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR,
		DP.DEP_NAME, DP.DEP_CODE, 
		B.AUTH_STATUS_NAME, 
		C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, C.AUTH_STATUS_KT_DESC, 
		TL.TLFullName CREATE_FULLNAME,
		TL1.TLFullName APPROVE_FULLNAME,
		CASE WHEN ISNULL(TL2.TLFullName,'') <>'' THEN TL2.TLFullName ELSE TL6.TLFullName END AS CREATE_FULLNAME_KT,
		TL3.TLFullName APPROVE_FULLNAME_KT,
		TL4.TLFullName AS TRANSFER_FULLNAME,
		TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,
		AL.CONTENT REQ_TYPE_NAME, 
		AL1.CONTENT REQ_PAY_TYPE_NAME,
		PO.PO_CODE, PO.PO_NAME, 
		S.SUP_NAME, S.TAX_NO SUP_TAX_NO,
		PC3.LEVEL_JOB, PC3.STATUS_JOB, PC3.TYPE_JOB, PC3.TLNAME AS EXEC_USER_KT,
		CASE WHEN PC3.TLNAME IS NOT NULL THEN PC3.TLNAME 
				WHEN PC3.TLNAME <>'' THEN PC3.TLNAME 
				ELSE TL2.TLNANME END AS EXEC_USER,
		SR.ROLE_ID AS ROLE_ID_CRE,
		BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE,'' AS BRANCH_CODE_CONTRACT,

		ISNULL((SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
		AS BRANCH_NAME_CONTRACT,
		L.REQ_PAY_AUTO_ID, L.REQ_PAY_AUTO_CODE
	-- SELECT END
		FROM TR_REQ_PAYMENT A
			LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
			LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
			LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
			LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
			LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
			LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
			LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
			LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
			--Luanlt--2019/10/15-Sửa AL,AL1
			LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE' AND AL.CDTYPE = 'REQ'
			LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE' AND AL1.CDTYPE = 'REQ'
			LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
			LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
			LEFT JOIN 
			(
			SELECT PAY_ADV_ID, SUM(AMT_PAY) SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
			) H ON A.REQ_PAY_ID =H.PAY_ADV_ID
			LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
			LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = PO.SUP_ID AND A.REQ_TYPE='P'
			LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C' 
			LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
			LEFT JOIN PL_REQUEST_PROCESS_CHILD PC5 ON PC5.REQ_ID = A.REQ_PAY_ID AND PC5.TYPE_JOB ='XL'
			LEFT JOIN TL_USER TL6 ON PC5.TLNAME = TL6.TLNANME
			LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
			LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID
			LEFT JOIN TR_REQ_PAY_AUTO_PAYMENTS K ON A.REQ_PAY_ID = K.REQ_PAY_ID
			LEFT JOIN TR_REQ_PAYMENT_AUTO L ON K.REQ_PAY_AUTO_ID = L.REQ_PAY_AUTO_ID
		WHERE 1=1 
			AND ISNULL(A.IS_CREATE_AUTO, '') = 'Y'
		-- BEGIN FILTER
			AND	(	A.REQ_PAY_ID = @p_REQ_PAY_ID OR ISNULL(@p_REQ_PAY_ID, '') = '')
			AND	(	A.REQ_PAY_CODE LIKE N'%'+ @p_REQ_PAY_CODE + '%' OR ISNULL(@p_REQ_PAY_CODE, '') = '')
			AND	(	L.CONTRACT_ID LIKE N'%'+ @p_CONTRACT_ID + '%' OR ISNULL(@p_CONTRACT_ID, '') = '')
			AND	(	A.REQ_REASON LIKE N'%'+ @p_REQ_REASON + '%' OR ISNULL(@p_REQ_REASON, '') = '')
			AND(	CONVERT(DATE, A.CREATE_DT) >= CONVERT(DATE, @p_FRMDATE, 103) OR ISNULL(@p_FRMDATE, '')= '' OR A.CREATE_DT IS NULL)
			AND(	CONVERT(DATE, A.CREATE_DT) <= CONVERT(DATE, @p_TODATE, 103) OR ISNULL(@p_TODATE, '')= '' OR A.CREATE_DT IS NULL)
			AND	(	L.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
			AND	(	A.TR_REQ_PAY_AUTO_CHECK_STATUS = @p_TR_REQ_PAY_AUTO_CHECK_STATUS OR ISNULL(@p_TR_REQ_PAY_AUTO_CHECK_STATUS, '') = '')
			AND	(	(	@p_LEVEL='ALL' 
						AND A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp_branch) 
					)
					OR	(	@p_LEVEL='UNIT' 
							AND A.BRANCH_ID = @p_BRANCH_ID
						)
					OR	(	@BRANCH_TYPE_LG <> 'HS' AND
							EXISTS	(	SELECT * 
										FROM TR_REQ_ADVANCE_DT 
										WHERE REQ_PAY_ID = A.REQ_PAY_ID
										AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) 
										AND A.AUTH_STATUS_KT ='A'
									)
						)
					OR A.BRANCH_CREATE IN (SELECT * FROM @tmp_branch_auth)
					OR (@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL) 
				)
			AND	(	ISNULL(@p_DEP_ID, '') = ''	-- NEU KHONG CHON PHONG BAN THI THOI
					OR A.DEP_ID = @p_DEP_ID		-- PDN PHONG BAN CUA MINH
					OR A.DEP_ID IN	(	SELECT * FROM @tmp_dep_auth	)	-- PDN PHONG BAN KIEM NHIEM
				)
		-- END FILTER
			  
		ORDER BY A.CREATE_DT DESC
	-- PAGING END
	END;

END

END -- PAGING
GO
update TR_REQ_PAYMENT 
SET TR_REQ_PAY_AUTO_CHECK_STATUS = 'U' 
where IS_CREATE_AUTO = 'Y' AND ISNULL(TR_REQ_PAY_AUTO_CHECK_STATUS , '') = ''
GO
--21092023_secretkey