ALTER PROCEDURE dbo.TR_REQUEST_SHOP_DOC_Search
@p_REQ_ID	varchar(15)  = NULL,
@p_REQ_CODE	nvarchar(100)  = NULL,
@p_REQ_NAME	nvarchar(200)  = NULL,
@p_REQ_DT	VARCHAR(20) = NULL,
@p_REQ_TYPE	varchar(20) = NULL,
@p_REQ_CONTENT NVARCHAR(1000)=NULL,
@p_TOTAL_AMT	decimal = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID NVARCHAR(100)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID VARCHAR(100)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_RECEIVE_BRANCH VARCHAR(15) = NULL,
@p_USERNAME VARCHAR(100) = NULL,
@p_BRANCH_ID VARCHAR(15)=NULL,
@p_DEP_ID VARCHAR(15)=NULL,
@p_STATUS  VARCHAR(15)=NULL,
@p_TOP	INT = 10,
@p_DVKD_MANAGE_APP_FROM	VARCHAR(20) = NULL,
@p_DVKD_MANAGE_APP_TO	VARCHAR(20) = NULL,
@p_REGION_ID varchar(15)  = NULL,
@p_CDTYPE_PYC VARCHAR(20) = NULL --Phucvh Truyền thêm CDTYPE để xác định loại PYC


AS
BEGIN -- PAGING
    DECLARE @ROLE_KT VARCHAR(50) = (SELECT TU.RoleName FROM TL_USER TU WHERE TU.TLNANME = @p_USERNAME)
	DECLARE @DEP_ID VARCHAR(20),@BRANCH_ID VARCHAR(20)
	DECLARE  @ROLE_LOGIN TABLE(ROLE_USER VARCHAR(20), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20))
--	INSERT INTO @ROLE_LOGIN SELECT DisplayName AS ROLE_USER FROM AbpRoles WHERE Id IN (SELECT A.RoleId FROM AbpUserRoles A 
--																		LEFT JOIN TL_USER B ON A.UserId = B.ID
--																		WHERE B.TLNANME = @p_USERNAME)
  INSERT INTO @ROLE_LOGIN
  SELECT tugr.ROLE_ID, tugr.BRANCH_ID, tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_USERNAME) tugr
	SET @DEP_ID= (SELECT DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME)
	SET @BRANCH_ID= (SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME)
	DECLARE @lstCOST TABLE
	(
		COST_ID VARCHAR(20)
	)
	INSERT INTO @lstCOST
	SELECT COST_ID FROM dbo.CM_COSTCENTER_DT WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID
	-- TienLee 11/14/21 --
	
	--

	DECLARE @TempSTATUS   TABLE
	(
		STATUS VARCHAR(20)
	)

	IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='QLTS' ))
	BEGIN
		INSERT INTO @TempSTATUS VALUES('DVKD')
		INSERT INTO @TempSTATUS VALUES('DVCM')
	END
	ELSE IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='DVCM' ))
	BEGIN
		INSERT INTO @TempSTATUS VALUES('QLTS_N')
		INSERT INTO @TempSTATUS VALUES('DVCM')
	END
	ELSE IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='CVMS' ))
	BEGIN
		INSERT INTO @TempSTATUS VALUES('QLTS_N')
		INSERT INTO @TempSTATUS VALUES('QLTS_XL')
		INSERT INTO @TempSTATUS VALUES('DVCM')
	END

		DECLARE @lstBRANCH_DEP TABLE
	(
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20)
	) 

--	IF(NOT EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='DVCM' OR ROLE_USER='CVMS_TBP' ))
--	BEGIN
--		INSERT INTO @lstBRANCH_DEP
--		(BRANCH_ID,DEP_ID)
--		SELECT TLSUBBRID,DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME 
--
--		INSERT INTO @lstBRANCH_DEP
--		(BRANCH_ID,DEP_ID)
--		SELECT BRANCH_ID,DEP_ID FROM dbo.CM_KHOI_DT
--		WHERE KHOI_ID IN (SELECT KHOI_ID FROM dbo.CM_KHOI_DT WHERE DEP_ID= @DEP_ID AND BRANCH_ID=@BRANCH_ID)
--	END
--
--
--
--  DECLARE @tbDep TABLE(DEP_ID VARCHAR(20))
--INSERT INTO @tbDep
--SELECT ckd.DEP_ID FROM CM_DEPARTMENT cd
----LEFT JOIN CM_KHOI ck ON cd.DEP_CODE=ck.KHOI_CODE
----LEFT JOIN CM_KHOI_DT ckd ON ck.KHOI_ID = ckd.KHOI_ID
--WHERE cd.DEP_ID=@p_DEP_ID

DECLARE @REQ_ID_Temp TABLE(REQ_ID VARCHAR(20))
INSERT INTO @REQ_ID_Temp
SELECT A.REQ_ID FROM TR_REQUEST_SHOP_DOC A
LEFT JOIN PL_REQUEST_PROCESS B ON A.REQ_ID = B.REQ_ID AND B.STATUS = 'C'
WHERE (EXISTS(SELECT RL.ROLE_USER FROM @ROLE_LOGIN RL WHERE RL.BRANCH_ID = B.BRANCH_ID
    AND (B.BRANCH_ID = 'DV0001' AND B.DEP_ID = RL.DEP_ID OR B.BRANCH_ID <> 'DV0001')
    AND RL.ROLE_USER = B.ROLE_USER)
  OR EXISTS(SELECT * FROM @ROLE_LOGIN RL
    JOIN PL_REQUEST_PROCESS C ON C.REQ_ID = A.REQ_ID AND C.STATUS = 'P' AND RL.BRANCH_ID = C.BRANCH_ID
    AND (C.BRANCH_ID = 'DV0001' AND C.DEP_ID = RL.DEP_ID OR C.BRANCH_ID <> 'DV0001')
    AND RL.ROLE_USER = C.ROLE_USER)
  OR A.MAKER_ID = @p_USERNAME)
GROUP BY A.REQ_ID

	IF((EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_SLAS' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
	BEGIN
		IF(@p_TOP IS NULL OR @p_TOP = '' OR @p_TOP = 0)
			BEGIN
			-- PAGING BEGIN
				SELECT A.REQ_ID,
					   A.REQ_CODE,
					   A.REQ_NAME,
					   --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT,
                       A.REQ_DT,
					   A.REQ_TYPE,
					   A.REQ_CONTENT,
					   A.TOTAL_AMT,
					   A.NOTES,
					   A.RECORD_STATUS,
					   A.MAKER_ID,
					   A.CREATE_DT,
					   A.AUTH_STATUS,
					   A.CHECKER_ID,
					   A.APPROVE_DT,
					   A.BRANCH_ID,
                       CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS,
					   A.DEP_ID,
					   A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME, 
                       --I.CONTENT AS REQ_STATUS_NAME,
                       CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Hoàn tất' 
                       ELSE I.CONTENT
                       END REQ_STATUS_NAME,
						 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,
             CASE 
             	WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME
             	 ELSE G.BRANCH_NAME
             END AS BRANCH_NAME,
						--CASE WHEN  RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' 
						--ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME
						D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME,
            I.CONTENT AS REQ_TYPE_NAME,
             CASE WHEN A.SEND_APP_DT IS NOT NULL AND A.SEND_APP_DT <> '' AND A.AUTH_STATUS <> 'A' AND A.AUTH_STATUS <> 'R' 
                      AND A.AUTH_STATUS <> 'E' AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE ''
            END AS COLOR
						--D.AUTH_STATUS_NAME 
				-- SELECT END
				FROM TR_REQUEST_SHOP_DOC A

				LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS 
				LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
				LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE
				LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC
--        LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C'
				--LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS
				--LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID  AND PRP1.PROCESS_ID = 'CVMS_N'
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID  AND PRP2.PROCESS_ID = 'DVKD'
				
				LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID
        LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE'
        LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID
				WHERE 1 = 1
				AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
--				AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE)  OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '')
--				AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '')
		        AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR
            	@p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) 
            	OR  @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) 
            	OR (CONVERT(DATE, A.REQ_DT, 103)) >=  (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
            	)
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))
				--AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''))		
				AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' 
            --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%'
            OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
				AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
				AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
				AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
				--AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
				
				AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
--				AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
				AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
				AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')		
				AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '')
				AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT')
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))
        AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS)
        AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID) 
              OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' ) 
              OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))

				AND A.RECORD_STATUS = '1'
				AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
				AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI  OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI 
				 OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')

				ORDER BY A.REQ_DT DESC
			-- PAGING END
			END
		   ELSE 
		   BEGIN
		   -- PAGING BEGIN
				SELECT TOP(CONVERT(INT,@p_TOP))  A.REQ_ID,
					   A.REQ_CODE,
					   A.REQ_NAME,
					   --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT,
                       A.REQ_DT,
					   A.REQ_TYPE,
					   A.REQ_CONTENT,
					   A.TOTAL_AMT,
					   A.NOTES,
					   A.RECORD_STATUS,
					   A.MAKER_ID,
					   A.CREATE_DT,
					   A.AUTH_STATUS,
					   A.CHECKER_ID,
					   A.APPROVE_DT,
					   A.BRANCH_ID,
                       CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS,
					   A.DEP_ID,
					   A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME, 
                       --I.CONTENT AS REQ_STATUS_NAME,
                       CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Hoàn tất' 
                       ELSE I.CONTENT
                       END REQ_STATUS_NAME,
						 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,
              CASE 
             	WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME
             	 ELSE G.BRANCH_NAME
              END AS BRANCH_NAME,
						--CASE WHEN  RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' 
						--ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME
						D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME,
            I.CONTENT AS REQ_TYPE_NAME,
             CASE WHEN A.SEND_APP_DT IS NOT NULL AND A.SEND_APP_DT <> '' AND A.AUTH_STATUS <> 'A' AND A.AUTH_STATUS <> 'R' 
                      AND A.AUTH_STATUS <> 'E' AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE ''
            END AS COLOR
						--D.AUTH_STATUS_NAME 
				-- SELECT END
				FROM TR_REQUEST_SHOP_DOC A

				LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS 
				LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
				LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE
				LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC
--        LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C'
				 
				--LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS
				--LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID  AND PRP1.PROCESS_ID = 'CVMS_N'
				LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID  AND PRP2.PROCESS_ID = 'DVKD'
				
				LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID
        LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE'
        LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID
				WHERE 1 = 1
				AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
			--	AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE)  OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '')
			--	AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '')
		        AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR
            	@p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) 
            	OR  @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) 
            	OR (CONVERT(DATE, A.REQ_DT, 103)) >=  (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
            	)
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))
				--AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''))		
				AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' 
            --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%'
            OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
        AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
				AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
				AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
				--AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
				
				AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
--				AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
				AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
				AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')		
				AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '')
				AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT')
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))
        AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS)
        AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID) 
              OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' ) 
              OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))
				AND A.RECORD_STATUS = '1'
				AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
				AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI  OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI 
				 OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
				ORDER BY A.REQ_DT DESC
			 -- PAGING END 
		END
	END
	ELSE
	BEGIN
		IF(@p_TOP IS NULL OR @p_TOP = '' OR @p_TOP = 0)
			BEGIN
			-- PAGING BEGIN
				SELECT  A.REQ_ID,
					   A.REQ_CODE,
					   A.REQ_NAME,
					   --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT,
                       A.REQ_DT,
					   A.REQ_TYPE,
					   A.REQ_CONTENT,
					   A.TOTAL_AMT,
					   A.NOTES,
					   A.RECORD_STATUS,
					   A.MAKER_ID,
					   A.CREATE_DT,
					   A.AUTH_STATUS,
					   A.CHECKER_ID,
					   A.APPROVE_DT,
					   A.BRANCH_ID,
                       CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS,
					   A.DEP_ID,
					   A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME, 
                       --I.CONTENT AS REQ_STATUS_NAME,
                       CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Hoàn tất' 
                       ELSE I.CONTENT
                       END REQ_STATUS_NAME,
						 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,
              CASE 
             	WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME
             	 ELSE G.BRANCH_NAME
              END AS BRANCH_NAME,
						--CASE WHEN  RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' 
						--ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME
						D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME,
            CASE WHEN A.SEND_APP_DT IS NOT NULL AND A.SEND_APP_DT <> '' AND A.AUTH_STATUS <> 'A' AND A.AUTH_STATUS <> 'R' 
                  AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE ''
            END AS COLOR
						--D.AUTH_STATUS_NAME 
				-- SELECT END
				FROM TR_REQUEST_SHOP_DOC A

				LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
				LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
				LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE
				LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC
--        LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C'		
				 
				--LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS
				--LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID  AND PRP1.PROCESS_ID = 'CVMS_N'
				LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID  AND PRP2.PROCESS_ID = 'DVKD'
				
				LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID
        LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE'
        LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID
				WHERE 1 = 1
				AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
--				AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE)  OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '')
--				AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '')
		        AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR
            	@p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) 
            	OR  @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) 
            	OR (CONVERT(DATE, A.REQ_DT, 103)) >=  (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
            	)
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))
				--AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''))		
				AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' 
            --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%'
            OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
				AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
				AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
				AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
				--AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
				
				AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
--				AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
				AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
				AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')		
				AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '')
				AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT')
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))
        AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS)
        AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID) 
              OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' ) 
              OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))

				AND A.RECORD_STATUS = '1'
				AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
				AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI  OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI 
				 OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
				ORDER BY  A.REQ_DT  DESC
			-- PAGING END
			END
		   ELSE 
		   BEGIN
		   -- PAGING BEGIN
				SELECT TOP(CONVERT(INT,@p_TOP))  A.REQ_ID,
					   A.REQ_CODE,
					   A.REQ_NAME,
					   --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT,
                       A.REQ_DT,
					   A.REQ_TYPE,
					   A.REQ_CONTENT,
					   A.TOTAL_AMT,
					   A.NOTES,
					   A.RECORD_STATUS,
					   A.MAKER_ID,
					   A.CREATE_DT,
					   A.AUTH_STATUS,
					   A.CHECKER_ID,
					   A.APPROVE_DT,
					   A.BRANCH_ID,
                       CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS,
					   A.DEP_ID,
					   A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME, 
                       --I.CONTENT AS REQ_STATUS_NAME,
                       CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Hoàn tất' 
                       ELSE I.CONTENT
                       END REQ_STATUS_NAME,
						 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,
              CASE 
             	WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME
             	 ELSE G.BRANCH_NAME
              END AS BRANCH_NAME,
						--CASE WHEN  RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' 
						--ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME
						D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME,
            CASE WHEN A.SEND_APP_DT IS NOT NULL AND A.SEND_APP_DT <> '' AND A.AUTH_STATUS <> 'A' AND A.AUTH_STATUS <> 'R' 
                  AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE ''
            END AS COLOR
						--D.AUTH_STATUS_NAME 
				-- SELECT END
				FROM TR_REQUEST_SHOP_DOC A

				LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
				LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
				LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE
				LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC
--        LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C'		
				 
				--LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS
				--LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID 
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))))
				--LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID  AND PRP1.PROCESS_ID = 'CVMS_N'
				LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID  AND PRP2.PROCESS_ID = 'DVKD'
				
				LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID
        LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE'
        LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID
				WHERE 1 = 1
				AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
				--AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE)  OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '')
				--AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '')
		        AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR
            	@p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) 
            	OR  @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) 
            	OR (CONVERT(DATE, A.REQ_DT, 103)) >=  (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103))
            	)
				--AND (((RQT.PROCESS_ID = A.STATUS AND  RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))
				--AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''))		
				AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' 
            --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%'
            OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
				AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
				AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
				AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
				--AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
				
				AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
--				AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
				AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
				AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')		
				AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '')
				AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT') 
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))
        AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS)
        AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID) 
              OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' ) 
              OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US
                WHERE US.TLNANME = @p_USERNAME))

				AND A.RECORD_STATUS = '1'
				AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID)
				AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI  OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
				AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI  OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI 
				 OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
				ORDER BY A.REQ_DT  DESC
			 -- PAGING END 
		END
	END
	
		
		
   END