ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_AUTO_Search]
@p_REQ_PAY_AUTO_ID	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_CODE	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_NAME	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_DT	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_TYPE	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_SERVICE_TYPE	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_STATUS	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_EFFECTIVE_DT	VARCHAR(20)= NULL,
@p_BRANCH_ID	VARCHAR(20)= NULL,
@p_BRANCH_CODE	VARCHAR(20)= NULL,
@p_BRANCH_NAME	VARCHAR(20)= NULL,
@p_DEP_ID	VARCHAR(20)= NULL,
@p_DEP_CODE	VARCHAR(20)= NULL,
@p_DEP_NAME	VARCHAR(20)= NULL,
@p_BRANCH_CREATE	VARCHAR(20)= NULL,
@p_BRANCH_CREATE_CODE	VARCHAR(20)= NULL,
@p_BRANCH_CREATE_NAME	VARCHAR(20)= NULL,
@p_TRANSFER_USER_RECEIVE	VARCHAR(20)= NULL,
@p_TRANSFER_USER_RECEIVE_NAME	VARCHAR(20)= NULL,
@p_CONFIRM_NOTE	VARCHAR(20)= NULL,
@p_CONTRACT_ID	VARCHAR(20)= NULL,
@p_CONTRACT_CODE	VARCHAR(20)= NULL,
@p_CONTRACT_NAME	VARCHAR(20)= NULL,
@p_PROCESS	VARCHAR(20)= NULL,
@p_TRANSFER_MAKER	VARCHAR(20)= NULL,
@p_TRANSFER_DT	VARCHAR(20)= NULL,
@p_MAKER_ID	VARCHAR(20)= NULL,
@p_MAKER_NAME	VARCHAR(20)= NULL,
@p_CREATE_DT	VARCHAR(20)= NULL,
@p_EDITOR_ID	VARCHAR(20)= NULL,
@p_EDITOR_NAME	VARCHAR(20)= NULL,
@p_EDIT_DT	VARCHAR(20)= NULL,
@p_CHECKER_ID	VARCHAR(20)= NULL,
@p_CHECKER_NAME	VARCHAR(20)= NULL,
@p_APPROVE_DT	VARCHAR(20)= NULL,
@p_AUTH_STATUS	VARCHAR(20)= NULL,
@p_MAKER_ID_KT	VARCHAR(20)= NULL,
@p_MAKER_KT_NAME	VARCHAR(20)= NULL,
@p_CREATE_DT_KT	VARCHAR(20)= NULL,
@p_CHECKER_ID_KT	VARCHAR(20)= NULL,
@p_CHECKER_KT_NAME	VARCHAR(20)= NULL,
@p_APPROVE_DT_KT	VARCHAR(20)= NULL,
@p_AUTH_STATUS_KT	VARCHAR(20)= NULL,
@p_AUTH_STATUS_KT_DESC	VARCHAR(20)= NULL,
@p_RECORD_STATUS	VARCHAR(20)= NULL,
@p_FRMDATE	VARCHAR(20)= NULL,
@p_TODATE	VARCHAR(20)= NULL,
@p_LEVEL	VARCHAR(20)= NULL,
@p_USER_LOGIN	VARCHAR(20)= NULL,
@p_IS_SEND_APPR	VARCHAR(20)= NULL,
@p_TYPE_SEARCH	VARCHAR(20)= NULL,
@p_BRANCH_LOGIN	VARCHAR(20)= NULL,
@p_EXEC_USER_KT	nvarchar(20)	= NULL,
@p_IS_UPDATE_KT VARCHAR(15) = NULL,
@p_BRANCH_MANAGE_ID	VARCHAR(20)= NULL,
@p_TOP INT = NULL
AS
/*
1. Trạng thái chờ duyệt ở DVKD: Sẽ kiếm thấy các phiếu chờ người đó xử lý, hoặc do người đó gửi phê duyệt
	- Do người này gửi duyệt(U-0)
	- Do người này đề nghị tạm dừng(A-6)
	- Do người này đề nghị khôi phục(A-9)
	- Do người này đề nghị hủy phiếu(A-15)

*/
BEGIN -- PAGING
-- BEGIN KHAI BÁO
	IF(@p_TYPE_SEARCH = 'HC')
	BEGIN
		SET @p_LEVEL = 'UNIT'
	END
	IF(ISNULL(@p_BRANCH_ID, '') = '')
	BEGIN
		SET @p_BRANCH_ID = @p_BRANCH_CREATE
	END
	IF(@p_BRANCH_LOGIN <> '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_AUTO_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 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 RECORD_STATUS = '1'
	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 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 RECORD_STATUS = '1'
-- ĐƠ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 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 RECORD_STATUS = '1'
-- 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 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 RECORD_STATUS = '1'
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 ='') 
							AND RECORD_STATUS = '1'
	
-- 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_AUTO_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
-- CHECK NGUOI DUYET
	DECLARE @IS_TDV VARCHAR(1) = 'N'
	IF	(		
				(	SELECT COUNT(*) 
					FROM @TABLE_ROLE A 
					WHERE A.ROLE_AUTH IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD')
				) = 0
		)
	BEGIN
		SET @IS_TDV = 'N'
	END
	ELSE
	BEGIN
		SET @IS_TDV = 'Y'
	END
-- END KHAI BÁO
	IF(@p_TYPE_SEARCH = 'HC')
	BEGIN
		IF(@p_TOP IS NULL OR @p_TOP=0)
		BEGIN
			IF(@DEP_ID_LG = 'DEP000000000022')
			BEGIN
	-- PAGING BEGIN
				SELECT A.*,
				--B.BRANCH_CODE, B.BRANCH_NAME,
				CASE	WHEN	(	SELECT TOP 1 BRANCH_ID 
									FROM TR_CONTRACT 
									WHERE CONTRACT_ID = A.CONTRACT_ID
								) <> 'DV0001'
								THEN  B.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 = A.CONTRACT_ID
																											)
																			),''
																)
						ELSE B.BRANCH_NAME 
				END BRANCH_NAME,
				C.DEP_CODE, C.DEP_NAME, 
				D.CONTRACT_CODE, D.[CONTRACT_NAME], 
				E.TLFullName AS MAKER_NAME, 
				F.TLFullName AS MAKER_NAME_KT, 
				G.TLFullName AS CHECKER_NAME_KT,
				H.CONTENT AS REQ_PAY_AUTO_STATUS_NAME,
				I.CONTENT AS REQ_PAY_AUTO_TYPE_NAME,
				J.CONTENT AS AUTH_STATUS_NAME,
				K.CONTENT AS AUTH_STATUS_KT_NAME,
				L.TLFullName AS TRANSFER_USER_RECEIVE_NAME,
				M.BRANCH_CODE AS BRANCH_MANAGE_CODE, M.BRANCH_NAME AS BRANCH_MANAGE_NAME
		-- SELECT END
				FROM TR_REQ_PAYMENT_AUTO A
				LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
				LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
				LEFT JOIN TR_CONTRACT D ON A.CONTRACT_ID = D.CONTRACT_ID
				LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
				LEFT JOIN TL_USER F ON A.MAKER_ID_KT = F.TLNANME
				LEFT JOIN TL_USER G ON A.CHECKER_ID_KT = G.TLNANME
				LEFT JOIN CM_ALLCODE H ON A.REQ_PAY_AUTO_STATUS = H.CDVAL AND H.CDNAME = 'PAY_AUTO_STATUS' AND H.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE I ON A.REQ_PAY_AUTO_TYPE = I.CDVAL AND I.CDNAME = 'PAY_TYPE_AUTO' AND I.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE J ON A.AUTH_STATUS = J.CDVAL AND J.CDNAME = 'AUTH_STATUS' AND J.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN CM_ALLCODE K ON A.AUTH_STATUS_KT = K.CDVAL AND K.CDNAME = 'AUTH_STATUS_KT' AND K.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN TL_USER L ON A.TRANSFER_USER_RECEIVE = L.TLNANME
				LEFT JOIN CM_BRANCH M ON A.BRANCH_MANAGE_ID = M.BRANCH_ID
				WHERE 1=1 
				-- BEGIN FILTER
				AND	(	A.REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID OR ISNULL(@p_REQ_PAY_AUTO_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
				--AND	(	A.AUTH_STATUS = @p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS, '') = '')
				AND (	(@p_AUTH_STATUS = A.AUTH_STATUS)	-- dùng cho: lưu nháp, phiếu bị hủy
					OR	(@p_AUTH_STATUS = 'W' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'G' AND A.PROCESS IN ('1', '7', '10', '16')) -- cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'U' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ duyệt CTG
					OR	(@p_AUTH_STATUS = 'U' AND A.PROCESS IN ('0', '1', '6', '7', '9', '10', '15', '16')) -- chờ duyệt TDV
					OR	(@p_AUTH_STATUS = 'A' AND A.PROCESS IN ('1', '2', '7', '8', '10', '11', '16', '17')) -- đã duyệt
					OR	(@p_AUTH_STATUS = 'R' AND ISNULL(A.PROCESS, '') IN ('', '7.1', '8.1', '10.1', '11.1', '14.1', '16.1', '17.1', '20.1') AND ISNULL(A.AUTH_STATUS, '') <> 'E') -- từ chối
					OR (ISNULL(@p_AUTH_STATUS, '') = '')
					)
				AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				AND	(	A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
				AND	(	M.BRANCH_ID = @p_BRANCH_MANAGE_ID OR ISNULL(@p_BRANCH_MANAGE_ID, '') = '')
				AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_STATUS = @p_REQ_PAY_AUTO_STATUS OR ISNULL(@p_REQ_PAY_AUTO_STATUS, '') = '')
				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	(	A.BRANCH_ID = @p_BRANCH_ID 
						OR ISNULL(@p_BRANCH_ID, '') = ''
						OR A.BRANCH_ID IN	(	SELECT BRANCH_ID 
												FROM @tmp_branch
											)
					)
				AND	(	A.DEP_ID = @p_DEP_ID 
						OR ISNULL(@p_DEP_ID, '') = ''
					)
				AND	(	A.MAKER_ID = @p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
				AND	(	A.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				-- END FILTER
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
			END
			ELSE
			BEGIN-- DVKD Search
	-- PAGING BEGIN
				SELECT A.*,
				--B.BRANCH_CODE, B.BRANCH_NAME,
				CASE	WHEN	(	SELECT TOP 1 BRANCH_ID 
									FROM TR_CONTRACT 
									WHERE CONTRACT_ID = A.CONTRACT_ID
								) <> 'DV0001'
								THEN  B.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 = A.CONTRACT_ID
																											)
																			),''
																)
						ELSE B.BRANCH_NAME 
				END BRANCH_NAME,
				C.DEP_CODE, C.DEP_NAME, 
				D.CONTRACT_CODE, D.[CONTRACT_NAME], 
				E.TLFullName AS MAKER_NAME, 
				F.TLFullName AS MAKER_NAME_KT, 
				G.TLFullName AS CHECKER_NAME_KT,
				H.CONTENT AS REQ_PAY_AUTO_STATUS_NAME,
				I.CONTENT AS REQ_PAY_AUTO_TYPE_NAME,
				J.CONTENT AS AUTH_STATUS_NAME,
				K.CONTENT AS AUTH_STATUS_KT_NAME,
				L.TLFullName AS TRANSFER_USER_RECEIVE_NAME,
				M.BRANCH_CODE AS BRANCH_MANAGE_CODE, M.BRANCH_NAME AS BRANCH_MANAGE_NAME
		-- SELECT END
				FROM TR_REQ_PAYMENT_AUTO A
				LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
				LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
				LEFT JOIN TR_CONTRACT D ON A.CONTRACT_ID = D.CONTRACT_ID
				LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
				LEFT JOIN TL_USER F ON A.MAKER_ID_KT = F.TLNANME
				LEFT JOIN TL_USER G ON A.CHECKER_ID_KT = G.TLNANME
				LEFT JOIN CM_ALLCODE H ON A.REQ_PAY_AUTO_STATUS = H.CDVAL AND H.CDNAME = 'PAY_AUTO_STATUS' AND H.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE I ON A.REQ_PAY_AUTO_TYPE = I.CDVAL AND I.CDNAME = 'PAY_TYPE_AUTO' AND I.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE J ON A.AUTH_STATUS = J.CDVAL AND J.CDNAME = 'AUTH_STATUS' AND J.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN CM_ALLCODE K ON A.AUTH_STATUS_KT = K.CDVAL AND K.CDNAME = 'AUTH_STATUS_KT' AND K.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN TL_USER L ON A.TRANSFER_USER_RECEIVE = L.TLNANME
				LEFT JOIN CM_BRANCH M ON A.BRANCH_MANAGE_ID = M.BRANCH_ID
				WHERE 1=1 
				-- BEGIN FILTER
				AND	(	A.REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID OR ISNULL(@p_REQ_PAY_AUTO_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
				--AND	(	A.AUTH_STATUS = @p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS, '') = '')
				AND (	(@p_AUTH_STATUS = A.AUTH_STATUS)	-- dùng cho: lưu nháp, phiếu bị hủy
					OR	(@p_AUTH_STATUS = 'W' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'G' AND A.PROCESS IN ('1', '7', '10', '16')) -- cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'U' AND @IS_TDV = 'N' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ duyệt CTG
					OR	(@p_AUTH_STATUS = 'U' AND @IS_TDV = 'Y' AND A.PROCESS IN ('0', '1', '6', '7', '9', '10', '15', '16')) -- chờ duyệt TDV
					OR	(@p_AUTH_STATUS = 'A' AND A.PROCESS IN ('1', '2', '7', '8', '10', '11', '16', '17')) -- đã duyệt
					OR	(@p_AUTH_STATUS = 'R' AND ISNULL(A.PROCESS, '') IN ('', '7.1', '8.1', '10.1', '11.1', '14.1', '16.1', '17.1', '20.1') AND ISNULL(A.AUTH_STATUS, '') <> 'E') -- từ chối
					OR (ISNULL(@p_AUTH_STATUS, '') = '')
					)
				AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				AND	(	A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
				AND	(	M.BRANCH_ID = @p_BRANCH_MANAGE_ID OR ISNULL(@p_BRANCH_MANAGE_ID, '') = '')
				AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_STATUS = @p_REQ_PAY_AUTO_STATUS OR ISNULL(@p_REQ_PAY_AUTO_STATUS, '') = '')
				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	(	A.MAKER_ID = @p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
				AND	(	A.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				-- END FILTER
				-- BEGIN VALIDATE FLOW
				AND	(
						(
								(	A.MAKER_ID = @p_USER_LOGIN)
						)-- NGUOI TAO
					OR	(
							(	A.TRANSFER_USER_RECEIVE = @p_USER_LOGIN AND A.AUTH_STATUS IN ('U', 'R', 'A', 'N') AND PROCESS IN ('0', '6', '9', '15') )
						)-- NGUOI DUYET TRUNG GIAN
					OR	(
							(	A.AUTH_STATUS IN ('R', 'A', 'N') AND @IS_TDV = 'Y')
						OR	(	A.AUTH_STATUS = 'U' AND ISNULL(A.TRANSFER_USER_RECEIVE, '') = '' AND @IS_TDV = 'Y')
						OR	(	A.AUTH_STATUS = 'U' AND ISNULL(A.TRANSFER_USER_RECEIVE, '') <> '' AND ISNULL(A.PROCESS, '') = '1' AND @IS_TDV = 'Y')
						)-- TRUONG DON VI
					OR	(
							A.AUTH_STATUS = 'A'
							
						)-- VA NHUNG PHIEU DA DUYET
					)
				AND	(	(	@p_LEVEL='ALL' 
							AND	(	A.BRANCH_ID IN	(	SELECT BRANCH_ID 
														FROM @tmp_branch
													) -- PYC cua don vi minh va cac don vi con
									OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PYC cua don vi kiem nhiem
								)
						)
						OR	(	@p_LEVEL='UNIT' 
								AND	(	A.BRANCH_ID = @p_BRANCH_ID	-- PYC cua don vi minh
										OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PYC cua don vi kiem nhiem
									)
							)
					)
				AND	(	A.DEP_ID = @p_DEP_ID 
						OR ISNULL(@p_DEP_ID, '') = ''
					)
							
				AND	(
						A.DEP_ID IN	(
										SELECT * 
										FROM @tmp_dep_auth
									)
						OR A.BRANCH_ID <> 'DV0001'
					)
				-- END VALIDATE FLOW
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
			END
		END;
		ELSE -- TOP IS NOT NULL
		BEGIN
			IF(@DEP_ID_LG = 'DEP000000000022')
			BEGIN
	-- PAGING BEGIN
				SELECT A.*,
				--B.BRANCH_CODE, B.BRANCH_NAME,
				CASE	WHEN	(	SELECT TOP 1 BRANCH_ID 
									FROM TR_CONTRACT 
									WHERE CONTRACT_ID = A.CONTRACT_ID
								) <> 'DV0001'
								THEN  B.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 = A.CONTRACT_ID
																											)
																			),''
																)
						ELSE B.BRANCH_NAME 
				END BRANCH_NAME,
				C.DEP_CODE, C.DEP_NAME, 
				D.CONTRACT_CODE, D.[CONTRACT_NAME], 
				E.TLFullName AS MAKER_NAME, 
				F.TLFullName AS MAKER_NAME_KT, 
				G.TLFullName AS CHECKER_NAME_KT,
				H.CONTENT AS REQ_PAY_AUTO_STATUS_NAME,
				I.CONTENT AS REQ_PAY_AUTO_TYPE_NAME,
				J.CONTENT AS AUTH_STATUS_NAME,
				K.CONTENT AS AUTH_STATUS_KT_NAME,
				L.TLFullName AS TRANSFER_USER_RECEIVE_NAME,
				M.BRANCH_CODE AS BRANCH_MANAGE_CODE, M.BRANCH_NAME AS BRANCH_MANAGE_NAME
		-- SELECT END
				FROM TR_REQ_PAYMENT_AUTO A
				LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
				LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
				LEFT JOIN TR_CONTRACT D ON A.CONTRACT_ID = D.CONTRACT_ID
				LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
				LEFT JOIN TL_USER F ON A.MAKER_ID_KT = F.TLNANME
				LEFT JOIN TL_USER G ON A.CHECKER_ID_KT = G.TLNANME
				LEFT JOIN CM_ALLCODE H ON A.REQ_PAY_AUTO_STATUS = H.CDVAL AND H.CDNAME = 'PAY_AUTO_STATUS' AND H.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE I ON A.REQ_PAY_AUTO_TYPE = I.CDVAL AND I.CDNAME = 'PAY_TYPE_AUTO' AND I.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE J ON A.AUTH_STATUS = J.CDVAL AND J.CDNAME = 'AUTH_STATUS' AND J.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN CM_ALLCODE K ON A.AUTH_STATUS_KT = K.CDVAL AND K.CDNAME = 'AUTH_STATUS_KT' AND K.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN TL_USER L ON A.TRANSFER_USER_RECEIVE = L.TLNANME
				LEFT JOIN CM_BRANCH M ON A.BRANCH_MANAGE_ID = M.BRANCH_ID
				WHERE 1=1 
				-- BEGIN FILTER
				AND	(	A.REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID OR ISNULL(@p_REQ_PAY_AUTO_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
				--AND	(	A.AUTH_STATUS = @p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS, '') = '')
				AND (	(@p_AUTH_STATUS = A.AUTH_STATUS)	-- dùng cho: lưu nháp, phiếu bị hủy
					OR	(@p_AUTH_STATUS = 'W' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'G' AND A.PROCESS IN ('1', '7', '10', '16')) -- cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'U' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ duyệt CTG
					OR	(@p_AUTH_STATUS = 'U' AND A.PROCESS IN ('0', '1', '6', '7', '9', '10', '15', '16')) -- chờ duyệt TDV
					OR	(@p_AUTH_STATUS = 'A' AND A.PROCESS IN ('1', '2', '7', '8', '10', '11', '16', '17')) -- đã duyệt
					OR	(@p_AUTH_STATUS = 'R' AND ISNULL(A.PROCESS, '') IN ('', '7.1', '8.1', '10.1', '11.1', '14.1', '16.1', '17.1', '20.1') AND ISNULL(A.AUTH_STATUS, '') <> 'E') -- từ chối
					OR (ISNULL(@p_AUTH_STATUS, '') = '')
					)
				AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				AND	(	A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
				AND	(	M.BRANCH_ID = @p_BRANCH_MANAGE_ID OR ISNULL(@p_BRANCH_MANAGE_ID, '') = '')
				AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_STATUS = @p_REQ_PAY_AUTO_STATUS OR ISNULL(@p_REQ_PAY_AUTO_STATUS, '') = '')
				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	(	A.BRANCH_ID = @p_BRANCH_ID 
						OR ISNULL(@p_BRANCH_ID, '') = ''
						OR A.BRANCH_ID IN	(	SELECT BRANCH_ID 
												FROM @tmp_branch
											)
					)
				AND	(	A.DEP_ID = @p_DEP_ID 
						OR ISNULL(@p_DEP_ID, '') = ''
					)
				AND	(	A.MAKER_ID = @p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
				AND	(	A.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				-- END FILTER
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
			END--END @DEP_ID_LG = 'DEP000000000022'
			ELSE
			BEGIN
	-- PAGING BEGIN
				SELECT A.*,
				--B.BRANCH_CODE, B.BRANCH_NAME,
				CASE	WHEN	(	SELECT TOP 1 BRANCH_ID 
									FROM TR_CONTRACT 
									WHERE CONTRACT_ID = A.CONTRACT_ID
								) <> 'DV0001'
								THEN  B.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 = A.CONTRACT_ID
																											)
																			),''
																)
						ELSE B.BRANCH_NAME 
				END BRANCH_NAME,
				C.DEP_CODE, C.DEP_NAME, 
				D.CONTRACT_CODE, D.[CONTRACT_NAME], 
				E.TLFullName AS MAKER_NAME, 
				F.TLFullName AS MAKER_NAME_KT, 
				G.TLFullName AS CHECKER_NAME_KT,
				H.CONTENT AS REQ_PAY_AUTO_STATUS_NAME,
				I.CONTENT AS REQ_PAY_AUTO_TYPE_NAME,
				J.CONTENT AS AUTH_STATUS_NAME,
				K.CONTENT AS AUTH_STATUS_KT_NAME,
				L.TLFullName AS TRANSFER_USER_RECEIVE_NAME,
				M.BRANCH_CODE AS BRANCH_MANAGE_CODE, M.BRANCH_NAME AS BRANCH_MANAGE_NAME
		-- SELECT END
				FROM TR_REQ_PAYMENT_AUTO A
				LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
				LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
				LEFT JOIN TR_CONTRACT D ON A.CONTRACT_ID = D.CONTRACT_ID
				LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
				LEFT JOIN TL_USER F ON A.MAKER_ID_KT = F.TLNANME
				LEFT JOIN TL_USER G ON A.CHECKER_ID_KT = G.TLNANME
				LEFT JOIN CM_ALLCODE H ON A.REQ_PAY_AUTO_STATUS = H.CDVAL AND H.CDNAME = 'PAY_AUTO_STATUS' AND H.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE I ON A.REQ_PAY_AUTO_TYPE = I.CDVAL AND I.CDNAME = 'PAY_TYPE_AUTO' AND I.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE J ON A.AUTH_STATUS = J.CDVAL AND J.CDNAME = 'AUTH_STATUS' AND J.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN CM_ALLCODE K ON A.AUTH_STATUS_KT = K.CDVAL AND K.CDNAME = 'AUTH_STATUS_KT' AND K.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN TL_USER L ON A.TRANSFER_USER_RECEIVE = L.TLNANME
				LEFT JOIN CM_BRANCH M ON A.BRANCH_MANAGE_ID = M.BRANCH_ID
				WHERE 1=1 
				-- BEGIN FILTER
				AND	(	A.REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID OR ISNULL(@p_REQ_PAY_AUTO_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
				--AND	(	A.AUTH_STATUS = @p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS, '') = '')
				AND (	(@p_AUTH_STATUS = A.AUTH_STATUS)	-- dùng cho: lưu nháp, phiếu bị hủy
					OR	(@p_AUTH_STATUS = 'W' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'G' AND A.PROCESS IN ('1', '7', '10', '16')) -- cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'U' AND @IS_TDV = 'N' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ duyệt CTG
					OR	(@p_AUTH_STATUS = 'U' AND @IS_TDV = 'Y' AND A.PROCESS IN ('0', '1', '6', '7', '9', '10', '15', '16')) -- chờ duyệt TDV
					OR	(@p_AUTH_STATUS = 'A' AND A.PROCESS IN ('1', '2', '7', '8', '10', '11', '16', '17')) -- đã duyệt
					OR	(@p_AUTH_STATUS = 'R' AND ISNULL(A.PROCESS, '') IN ('', '7.1', '8.1', '10.1', '11.1', '14.1', '16.1', '17.1', '20.1') AND ISNULL(A.AUTH_STATUS, '') <> 'E') -- từ chối
					OR (ISNULL(@p_AUTH_STATUS, '') = '')
					)
				AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				AND	(	A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
				AND	(	M.BRANCH_ID = @p_BRANCH_MANAGE_ID OR ISNULL(@p_BRANCH_MANAGE_ID, '') = '')
				AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_STATUS = @p_REQ_PAY_AUTO_STATUS OR ISNULL(@p_REQ_PAY_AUTO_STATUS, '') = '')
				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	(	A.MAKER_ID = @p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
				AND	(	A.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				-- END FILTER
				-- BEGIN VALIDATE FLOW
				AND	(
						(
								(	A.MAKER_ID = @p_USER_LOGIN)
						)-- NGUOI TAO
					OR	(
							(	A.TRANSFER_USER_RECEIVE = @p_USER_LOGIN AND A.AUTH_STATUS IN ('U', 'R', 'A', 'N') AND PROCESS IN ('0', '6', '9', '15') )
						)-- NGUOI DUYET TRUNG GIAN
					OR	(
							(	A.AUTH_STATUS IN ('R', 'A', 'N') AND @IS_TDV = 'Y')
						OR	(	A.AUTH_STATUS = 'U' AND ISNULL(A.TRANSFER_USER_RECEIVE, '') = '' AND @IS_TDV = 'Y')
						OR	(	A.AUTH_STATUS = 'U' AND ISNULL(A.TRANSFER_USER_RECEIVE, '') <> '' AND ISNULL(A.PROCESS, '') = '1' AND @IS_TDV = 'Y')
						)-- TRUONG DON VI
					OR	(
							A.AUTH_STATUS = 'A'
							
						)-- VA NHUNG PHIEU DA DUYET
					)
				AND	(	(	@p_LEVEL='ALL' 
							AND	(	A.BRANCH_ID IN	(	SELECT BRANCH_ID 
														FROM @tmp_branch
													) -- PYC cua don vi minh va cac don vi con
									OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PYC cua don vi kiem nhiem
								)
						)
						OR	(	@p_LEVEL='UNIT' 
								AND	(	A.BRANCH_ID = @p_BRANCH_ID	-- PYC cua don vi minh
										OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PYC cua don vi kiem nhiem
									)
							)
					)
				AND	(	A.DEP_ID = @p_DEP_ID 
						OR ISNULL(@p_DEP_ID, '') = ''
					)
							
				AND	(
						A.DEP_ID IN	(
										SELECT * 
										FROM @tmp_dep_auth
									)
						OR A.BRANCH_ID <> 'DV0001'
					)
				-- END VALIDATE FLOW
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
			END
		END;
	END
	ELSE IF(@p_TYPE_SEARCH = 'KT')
	BEGIN
		IF(@p_TOP IS NULL OR @p_TOP = 0)
		BEGIN
	-- PAGING BEGIN
				SELECT A.*,
				--B.BRANCH_CODE, B.BRANCH_NAME,
				CASE	WHEN	(	SELECT TOP 1 BRANCH_ID 
									FROM TR_CONTRACT 
									WHERE CONTRACT_ID = A.CONTRACT_ID
								) <> 'DV0001'
								THEN  B.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 = A.CONTRACT_ID
																											)
																			),''
																)
						ELSE B.BRANCH_NAME 
				END BRANCH_NAME,
				C.DEP_CODE, C.DEP_NAME, 
				D.CONTRACT_CODE, D.[CONTRACT_NAME], 
				E.TLFullName AS MAKER_NAME, 
				F.TLFullName AS MAKER_NAME_KT, 
				G.TLFullName AS CHECKER_NAME_KT,
				H.CONTENT AS REQ_PAY_AUTO_STATUS_NAME,
				I.CONTENT AS REQ_PAY_AUTO_TYPE_NAME,
				J.CONTENT AS AUTH_STATUS_NAME,
				K.CONTENT AS AUTH_STATUS_KT_NAME,
				L.TLFullName AS TRANSFER_USER_RECEIVE_NAME,
				M.BRANCH_CODE AS BRANCH_MANAGE_CODE, M.BRANCH_NAME AS BRANCH_MANAGE_NAME
		-- SELECT END
				FROM TR_REQ_PAYMENT_AUTO A
				LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
				LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
				LEFT JOIN TR_CONTRACT D ON A.CONTRACT_ID = D.CONTRACT_ID
				LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
				LEFT JOIN TL_USER F ON A.MAKER_ID_KT = F.TLNANME
				LEFT JOIN TL_USER G ON A.CHECKER_ID_KT = G.TLNANME
				LEFT JOIN CM_ALLCODE H ON A.REQ_PAY_AUTO_STATUS = H.CDVAL AND H.CDNAME = 'PAY_AUTO_STATUS' AND H.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE I ON A.REQ_PAY_AUTO_TYPE = I.CDVAL AND I.CDNAME = 'PAY_TYPE_AUTO' AND I.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE J ON A.AUTH_STATUS = J.CDVAL AND J.CDNAME = 'AUTH_STATUS' AND J.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN CM_ALLCODE K ON A.AUTH_STATUS_KT = K.CDVAL AND K.CDNAME = 'AUTH_STATUS_KT' AND K.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN TL_USER L ON A.TRANSFER_USER_RECEIVE = L.TLNANME
				LEFT JOIN CM_BRANCH M ON A.BRANCH_MANAGE_ID = M.BRANCH_ID
				LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_AUTO_ID AND PC3.STATUS_JOB ='C' 
				WHERE 1=1 
				-- BEGIN FILTER
				AND A.AUTH_STATUS IN ('A', 'N', 'D')
				AND	(	A.REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID OR ISNULL(@p_REQ_PAY_AUTO_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
				--AND	(	A.AUTH_STATUS = @p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS, '') = '')
				AND (	(@p_AUTH_STATUS_KT = A.AUTH_STATUS_KT) -- dùng cho từ chối
					OR	(@p_AUTH_STATUS_KT = 'U' AND A.PROCESS IN ('3', '12', '18')) -- chờ duyệt
					OR	(@p_AUTH_STATUS_KT = 'P' AND A.PROCESS IN ('4', '13', '13.1', '19', '19.1')) -- đang xử lý
					OR	(@p_AUTH_STATUS_KT = 'S' AND A.PROCESS IN ('4', '13.1', '19.1')) -- đề xuất từ chối
					OR	(@p_AUTH_STATUS_KT = 'A' AND A.PROCESS IN ('5', '14', '20')) -- đã duyệt
					OR	(@p_AUTH_STATUS_KT = 'R' AND A.PROCESS IN ('20.2', '14.2')) -- từ chối
					OR (ISNULL(@p_AUTH_STATUS_KT, '') = '' AND A.PROCESS  IN ('2', '11', '17')) -- khi KSV chưa điều phối
					OR (ISNULL(@p_AUTH_STATUS_KT, '') = '')
					)
				AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				--AND	(	A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
				AND	(	M.BRANCH_ID = @p_BRANCH_MANAGE_ID OR ISNULL(@p_BRANCH_MANAGE_ID, '') = '')
				AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_STATUS = @p_REQ_PAY_AUTO_STATUS OR ISNULL(@p_REQ_PAY_AUTO_STATUS, '') = '')
				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	(	(	@p_LEVEL='ALL' 
							AND	(	A.BRANCH_ID IN	(	SELECT BRANCH_ID 
														FROM @tmp_branch
													) -- PYC cua don vi minh
									OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PYC cua don vi kiem nhiem
								)
						)
						OR	(	
									@p_LEVEL='UNIT' 
								AND A.BRANCH_ID = @p_BRANCH_ID
							)
					)
				AND	(	A.DEP_ID = @p_DEP_ID 
						OR ISNULL(@p_DEP_ID, '') = ''
					)
				AND	(	A.MAKER_ID = @p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
				AND	(	A.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				AND(	PC3.TLNAME = @p_EXEC_USER_KT OR @p_EXEC_USER_KT = '' OR @p_EXEC_USER_KT IS NULL OR PC3.TLNAME = '' OR PC3.TLNAME IS NULL)
				AND	(
						(
							EXISTS	(	SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD X WHERE	REQ_ID = A.REQ_PAY_AUTO_ID 
																								AND (
																										X.TLNAME= @p_USER_LOGIN 
																										OR X.TLNAME =@p_EXEC_USER_KT
																									)
									)
						) 
						OR	(A.MAKER_ID_KT = @p_MAKER_ID_KT OR PC3.TLNAME = @p_MAKER_ID_KT)
						OR (SELECT COUNT(*) FROM @TABLE_ROLE WHERE ROLE_AUTH = 'KSV') > 0
					)	
				AND	(	(	A.MAKER_ID_KT IS NOT NULL 
							AND @p_IS_UPDATE_KT='Y'
						)
						OR	(	(	A.MAKER_ID_KT IS NULL 
									AND @p_IS_UPDATE_KT='N'
								)
							)
						OR @p_IS_UPDATE_KT IS NULL 
						OR @p_IS_UPDATE_KT=''
					)
				
				-- END FILTER
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
		END;
		ELSE 
		BEGIN
	-- PAGING BEGIN
				SELECT A.*,
				--B.BRANCH_CODE, B.BRANCH_NAME,
				CASE	WHEN	(	SELECT TOP 1 BRANCH_ID 
									FROM TR_CONTRACT 
									WHERE CONTRACT_ID = A.CONTRACT_ID
								) <> 'DV0001'
								THEN  B.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 = A.CONTRACT_ID
																											)
																			),''
																)
						ELSE B.BRANCH_NAME 
				END BRANCH_NAME,
				C.DEP_CODE, C.DEP_NAME, 
				D.CONTRACT_CODE, D.[CONTRACT_NAME], 
				E.TLFullName AS MAKER_NAME, 
				F.TLFullName AS MAKER_NAME_KT, 
				G.TLFullName AS CHECKER_NAME_KT,
				H.CONTENT AS REQ_PAY_AUTO_STATUS_NAME,
				I.CONTENT AS REQ_PAY_AUTO_TYPE_NAME,
				J.CONTENT AS AUTH_STATUS_NAME,
				K.CONTENT AS AUTH_STATUS_KT_NAME,
				L.TLFullName AS TRANSFER_USER_RECEIVE_NAME,
				M.BRANCH_CODE AS BRANCH_MANAGE_CODE, M.BRANCH_NAME AS BRANCH_MANAGE_NAME
	-- SELECT END
				FROM TR_REQ_PAYMENT_AUTO A
				LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
				LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
				LEFT JOIN TR_CONTRACT D ON A.CONTRACT_ID = D.CONTRACT_ID
				LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
				LEFT JOIN TL_USER F ON A.MAKER_ID_KT = F.TLNANME
				LEFT JOIN TL_USER G ON A.CHECKER_ID_KT = G.TLNANME
				LEFT JOIN CM_ALLCODE H ON A.REQ_PAY_AUTO_STATUS = H.CDVAL AND H.CDNAME = 'PAY_AUTO_STATUS' AND H.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE I ON A.REQ_PAY_AUTO_TYPE = I.CDVAL AND I.CDNAME = 'PAY_TYPE_AUTO' AND I.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE J ON A.AUTH_STATUS = J.CDVAL AND J.CDNAME = 'AUTH_STATUS' AND J.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN CM_ALLCODE K ON A.AUTH_STATUS_KT = K.CDVAL AND K.CDNAME = 'AUTH_STATUS_KT' AND K.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN TL_USER L ON A.TRANSFER_USER_RECEIVE = L.TLNANME
				LEFT JOIN CM_BRANCH M ON A.BRANCH_MANAGE_ID = M.BRANCH_ID
				LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_AUTO_ID AND PC3.STATUS_JOB ='C' 
				WHERE 1=1 
				-- BEGIN FILTER
				AND A.AUTH_STATUS IN ('A', 'N', 'D')
				AND	(	A.REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID OR ISNULL(@p_REQ_PAY_AUTO_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
				--AND	(	A.AUTH_STATUS = @p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS, '') = '')
				AND (	(@p_AUTH_STATUS_KT = A.AUTH_STATUS_KT) -- dùng cho từ chối
					OR	(@p_AUTH_STATUS_KT = 'U' AND A.PROCESS IN ('3', '12', '18')) -- chờ duyệt
					OR	(@p_AUTH_STATUS_KT = 'P' AND A.PROCESS IN ('4', '13', '13.1', '19', '19.1')) -- đang xử lý
					OR	(@p_AUTH_STATUS_KT = 'S' AND A.PROCESS IN ('4', '13.1', '19.1')) -- đề xuất từ chối
					OR	(@p_AUTH_STATUS_KT = 'A' AND A.PROCESS IN ('5', '14', '20')) -- đã duyệt
					OR	(@p_AUTH_STATUS_KT = 'R' AND A.PROCESS IN ('20.2', '14.2')) -- từ chối
					OR (ISNULL(@p_AUTH_STATUS_KT, '') = '' AND A.PROCESS  IN ('2', '11', '17')) -- khi KSV chưa điều phối
					OR (ISNULL(@p_AUTH_STATUS_KT, '') = '')
					)
				AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				--AND	(	A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
				AND	(	M.BRANCH_ID = @p_BRANCH_MANAGE_ID OR ISNULL(@p_BRANCH_MANAGE_ID, '') = '')
				AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_STATUS = @p_REQ_PAY_AUTO_STATUS OR ISNULL(@p_REQ_PAY_AUTO_STATUS, '') = '')
				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	(	(	@p_LEVEL='ALL' 
							AND	(	A.BRANCH_ID IN	(	SELECT BRANCH_ID 
														FROM @tmp_branch
													) -- PYC cua don vi minh
									OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PYC cua don vi kiem nhiem
								)
						)
						OR	(	
									@p_LEVEL='UNIT' 
								AND A.BRANCH_ID = @p_BRANCH_ID
							)
					)
				AND	(	A.DEP_ID = @p_DEP_ID 
						OR ISNULL(@p_DEP_ID, '') = ''
					)
				AND	(	A.MAKER_ID = @p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
				AND	(	A.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				AND(	PC3.TLNAME = @p_EXEC_USER_KT OR @p_EXEC_USER_KT = '' OR @p_EXEC_USER_KT IS NULL OR PC3.TLNAME = '' OR PC3.TLNAME IS NULL)
				AND	(
						(
							EXISTS	(	SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD X WHERE	REQ_ID = A.REQ_PAY_AUTO_ID 
																								AND (
																										X.TLNAME= @p_USER_LOGIN 
																										OR X.TLNAME =@p_EXEC_USER_KT
																									)
									)
						) 
						OR	(A.MAKER_ID_KT = @p_MAKER_ID_KT OR PC3.TLNAME = @p_MAKER_ID_KT)
						OR (SELECT COUNT(*) FROM @TABLE_ROLE WHERE ROLE_AUTH = 'KSV') > 0
					)	
				AND	(	(	A.MAKER_ID_KT IS NOT NULL 
							AND @p_IS_UPDATE_KT='Y'
						)
						OR	(	(	A.MAKER_ID_KT IS NULL 
									AND @p_IS_UPDATE_KT='N'
								)
							)
						OR @p_IS_UPDATE_KT IS NULL 
						OR @p_IS_UPDATE_KT=''
					)
				
				-- END FILTER
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
	   END;
	END

END -- PAGING