ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_Search]
@p_REQ_ID	varchar(15)  = NULL,
@p_REQ_CODE	varchar(50)  = NULL,
@p_PL_REQ_CODE	varchar(50)  = NULL,
@p_REQ_NAME	nvarchar(200)  = NULL,
@p_REQ_DT	DATETIME = 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	varchar(12)  = NULL,
@p_CREATE_DT	DATETIME = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	DATETIME = NULL,
@p_BRANCH_DO VARCHAR(15) = NULL,
@p_BRANCH_CREATE VARCHAR(15) = NULL,
@p_USER_REQUEST VARCHAR(15) = NULL,
@p_BRANCH_LOGIN VARCHAR(15)=NULL,
@p_TLNAME_USER VARCHAR(20)=NULL,
@p_ROLE_USER VARCHAR(20),
@p_TOP	INT = 10,
@p_PROCESS_STATUS varchar(50) = NULL,
@p_FR_DATE DATETIME = NULL,
@p_TO_DATE DATETIME = NULL,
@p_TYPE VARCHAR(15),
@p_TYPE_TRANFER VARCHAR(15)=NULL,
@p_YEAR INT = NULL,
@p_IS_TRANSFER VARCHAR(10) = NULL,
@p_NGUOIXULY NVARCHAR(15) = NULL,
@p_IS_KT bit = NULL,
@p_PL_REQ_ID VARCHAR(20)=NULL

AS
BEGIN
	

	IF(@p_ROLE_USER ='KTT')
		SET @p_ROLE_USER ='GDDV'
	DECLARE @DEP_ID VARCHAR(15),@BRANCH_TYPE VARCHAR(20)
	DECLARE
	@COST_ID TABLE (
		COST_ID VARCHAR(15),
		DVDM_ID VARCHAR(15)
	)
	SET @DEP_ID =(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME_USER)
	INSERT INTO @COST_ID
	SELECT DT.COST_ID,PC.DVDM_ID FROM dbo.PL_COSTCENTER_DT DT
	LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
	WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@p_BRANCH_LOGIN

	SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE  BRANCH_ID=@p_BRANCH_LOGIN)
	IF(EXISTS(SELECT DEP_ID FROM dbo.CM_DEPARTMENT WHERE DEP_ID=@DEP_ID AND DEP_CODE LIKE '069%'))
		SET @BRANCH_TYPE='HS'

	DECLARE @AUTHOR TABLE
	(
		ROLE_ID VARCHAR(100),
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20),
		BRANCH_TYPE VARCHAR(20)
	)

	DECLARE @AUTHOR_DVDM TABLE
	(
		ROLE_ID VARCHAR(100),
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20),
		DVDM_ID VARCHAR(20)
	)
	DECLARE @AUTHOR_DMMS TABLE
	(
		ROLE_ID VARCHAR(100),
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20),
		DMMS_ID VARCHAR(20)
	)
	INSERT INTO @AUTHOR
	(
	    ROLE_ID,
	    BRANCH_ID,
	    DEP_ID,
		BRANCH_TYPE
	)
	SELECT RoleName,TLSUBBRID,SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_USER TU
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
	WHERE TLNANME=@p_TLNAME_USER
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TLSUBBRID,SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_USER TU
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
	WHERE TLNANME=@p_TLNAME_USER
	UNION ALL
	SELECT TM1.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_SYS_ROLE_MAPPING TM1
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
	WHERE TM1.TLNAME=@p_TLNAME_USER AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_SYS_ROLE_MAPPING TM1
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TM1.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
	WHERE TM1.TLNAME=@p_TLNAME_USER AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)

	INSERT INTO @AUTHOR_DVDM
	(
	    ROLE_ID,
	    BRANCH_ID,
	    DEP_ID,
	    DVDM_ID
	)
	SELECT TU.RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU 
	LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
	LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
	WHERE TU.TLNANME=@p_TLNAME_USER
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU 
	LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
	LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	WHERE TU.TLNANME=@p_TLNAME_USER
	UNION ALL
	SELECT TU.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU 
	LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
	LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
	WHERE TU.TLNAME=@p_TLNAME_USER AND CAST(EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU 
	LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
	LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	WHERE TU.TLNAME=@p_TLNAME_USER AND CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)

	INSERT INTO @AUTHOR_DMMS
	(
	    ROLE_ID,
	    BRANCH_ID,
	    DEP_ID,
		DMMS_ID
	)
	SELECT RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
	LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TU.SECUR_CODE=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
	WHERE TLNANME=@p_TLNAME_USER
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
	LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TU.SECUR_CODE=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
	WHERE TLNANME=@p_TLNAME_USER
	UNION ALL
	SELECT TM1.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,MS.DMMS_ID FROM dbo.TL_SYS_ROLE_MAPPING TM1
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
	LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TM1.DEP_ID=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
	WHERE TM1.TLNAME=@p_TLNAME_USER AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,MS.DMMS_ID FROM dbo.TL_SYS_ROLE_MAPPING TM1
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TM1.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
	LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TM1.DEP_ID=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
	WHERE TM1.TLNAME=@p_TLNAME_USER AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)


	----NGUOI XU LY 18022021
		DECLARE @lstREQUEST TABLE (
			REQ_ID VARCHAR(20),
			PROCESS_ID VARCHAR(50),
			DVDM_NAME NVARCHAR(200),
			TLNAME VARCHAR(200),
			TLFullName NVARCHAR(200),
			NOTES NVARCHAR(200)
		)
	INSERT INTO @lstREQUEST
	( REQ_ID,PROCESS_ID, DVDM_NAME,TLNAME,TLFullName, NOTES)	
	SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES 	
	FROM
	(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
	WHERE EXISTS(SELECT TRD.REQ_ID FROM dbo.TR_REQUEST_DOC TRD WHERE TRD.REQ_ID=PRC.REQ_ID) 
	AND PRC.PROCESS_ID ='DMMS' AND PRC.STATUS='C'
	) PL
	LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
	LEFT JOIN dbo.TR_REQUEST_DOC TR ON TR.REQ_ID=PL.REQ_ID
	LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN 
	(
	SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC 
	LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
	WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
	)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
	LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
	LEFT JOIN 
	(
		SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID 
		FROM
		(
		SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
		dbo.TL_USER TS 
		UNION ALL
		SELECT  TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
		dbo.TL_SYS_ROLE_MAPPING TM
		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
		WHERE  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
		) TU
		LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
		LEFT JOIN(
		SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC
		UNION ALL
		SELECT DVDM.DVDM_ID AS DMMS_ID,DVDM.DEP_ID,DVDM.BRANCH_ID  FROM 
		(
			SELECT CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID FROM dbo.CM_DVDM CD
			LEFT JOIN dbo.PL_COSTCENTER PC ON PC.DVDM_ID=CD.DVDM_ID
			LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID
			WHERE CD.IS_KHOI <>1
			GROUP BY CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID
		) DVDM
		) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID
	) TempU ON (TempU.RoleName=PL.ROLE_USER  OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE TM.ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.DMMS_ID=TR.DMMS_ID) AND TempC.ID IS NULL	
	WHERE PL.STATUS='C'	
	UNION ALL
	SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES 
	FROM
	(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
	WHERE EXISTS(SELECT TRD.REQ_ID FROM dbo.TR_REQUEST_DOC TRD WHERE TRD.REQ_ID=PRC.REQ_ID) 
	AND PRC.PROCESS_ID <>'DMMS' AND PRC.PROCESS_ID <> 'NEW' AND PRC.PROCESS_ID <>'APPROVE' 
	AND PRC.STATUS='C' AND (PRC.IS_HAS_CHILD=0 OR PRC.IS_HAS_CHILD IS NULL)
	) PL
	LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
	LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN 
	(
	SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID 
	FROM (
		SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
		dbo.TL_USER TS 
		UNION ALL
		SELECT  TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
		dbo.TL_SYS_ROLE_MAPPING TM
		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
		WHERE  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
		) TU
	LEFT JOIN
		(
		SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 
		LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID
		) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
	) TempU ON (TempU.RoleName=PL.ROLE_USER  OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') 
	AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') 
	AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
	WHERE  STATUS='C'
	UNION ALL
		SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES 
		FROM 
	(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
	WHERE EXISTS(SELECT TRD.REQ_ID FROM dbo.TR_REQUEST_DOC TRD WHERE TRD.REQ_ID=PRC.REQ_ID) 
	AND PRC.PROCESS_ID <>'DMMS' AND PRC.PROCESS_ID <> 'NEW' AND PRC.PROCESS_ID <>'APPROVE' 
	AND PRC.STATUS='C' AND PRC.IS_HAS_CHILD=1 
	) PL
	LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
	LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN 
	(
	SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC 
	LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
	WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
	)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
	LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
	LEFT JOIN 
	(
	SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID 
	FROM       (
		SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
		dbo.TL_USER TS 
		UNION ALL
		SELECT  TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
		dbo.TL_SYS_ROLE_MAPPING TM
		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
		WHERE  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
		)  TU 
	LEFT JOIN(
	SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 
	LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
	) TempU ON  (TempU.RoleName=PL.ROLE_USER  OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') 
	AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') 
	AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
	WHERE  PL.STATUS='C'
	----

	IF(@P_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0 OR @P_TOP>0)
	BEGIN
		IF(@p_TYPE='DVKD' )
		BEGIN
			
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE, A.IS_KT,
               A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
               A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
			   D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
			   CMS.DMMS_NAME,A.DMMS_ID,
			   RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
			   RP.ROLE_USER,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' 
				ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
				RPN.NOTES AS PROCESS_STATUS_NEXT
		,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
		DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
		DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
		PLRD.REQ_NAME AS PL_REQ_NAME,'' TYPE_JOB,'' AS USER_JOB,'' AS USER_JOB_NAME,'' AS TRANSFER_MAKER,CAST(NULL AS DATETIME)  AS TRANFER_DT ,
		'' AS TRANSFER_MAKER_ID,'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, FORMAT(PLRD.TOTAL_AMT,'#,###') AS NCC_NAME, '' AS NCC_ADDR,RP.ID AS REF_ID,
		  RPN.STATUS AS STATUS_NEXT,
		  RP.STATUS AS STATUS_CURR,
		'' AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,
		A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
		--NGUOI XU LY
		--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
		 NXL.NGUOIXULY AS NGUOIXULY
		FROM TR_REQUEST_DOC A
		LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
		LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
		LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
		LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
		LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
		LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
		LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
		LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
		LEFT JOIN 
		(
			SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
			dbo.PL_REQUEST_PROCESS
			GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
		) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)	
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
		LEFT JOIN 
		(
			SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
			dbo.CM_DMMS 
			LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
			UNION ALL
			SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
			FROM dbo.CM_DVDM
		)CMS ON CMS.DMMS_ID=A.DMMS_ID
		LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
		LEFT JOIN
		(		
		SELECT	Results.REQ_ID,STUFF((select ', ' + RE.TLFullName  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
		STUFF((select ', ' + RE.TLNAME  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME	
		FROM @lstREQUEST Results
		GROUP BY REQ_ID
		) NXL ON NXL.REQ_ID=A.REQ_ID

		WHERE 1 = 1
		AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
	    AND (A.REQ_CODE 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_REASON 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 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 (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')		
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')	
		AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')	
		 --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
		AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
		AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
		AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = ''
		OR A.DEP_CREATE LIKE '%' + @p_BRANCH_CREATE + '%'
		)
		AND (PLRD.REQ_CODE LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
		AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_ID + '%' OR @p_PL_REQ_ID IS NULL OR @p_PL_REQ_ID = '')
		AND A.RECORD_STATUS = '1'
		AND	(A.MAKER_ID=@p_TLNAME_USER OR 		
				(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS PLRP WHERE A.REQ_ID=PLRP.REQ_ID AND PLRP.PROCESS_ID='APPNEW' 
					AND EXISTS(SELECT * FROM @AUTHOR AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND AUTH.BRANCH_ID=PLRP.BRANCH_ID 
					AND (AUTH.DEP_ID=PLRP.DEP_ID OR AUTH.BRANCH_TYPE <>'HS'))  )) 		
		OR (A.SIGN_USER IS NOT NULL AND A.SIGN_USER <>'' AND A.SIGN_USER =@p_TLNAME_USER AND A.AUTH_STATUS <>'E'))

		AND (( @p_AUTH_STATUS='A' AND  A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)	
		AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = ''
			OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY  + '%')
		AND(
			(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
			OR
			(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',1) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		)

		ORDER BY A.CREATE_DT DESC
		END
		ELSE IF(@p_TYPE='TFJOB')
		BEGIN			
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
               A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
               A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
			   D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
			   CMS.DMMS_NAME,A.DMMS_ID,
			   RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
			   RP.ROLE_USER,
				CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' 
				ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
				RPN.NOTES AS PROCESS_STATUS_NEXT
		,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
		DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
		CASE WHEN DVC.BRANCH_TYPE ='HS' THEN DVC.BRANCH_NAME ELSE DEP.DEP_NAME END AS BRANCH_CREATE_NAME,
		--DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
		PLRD.REQ_NAME AS PL_REQ_NAME,ISNULL(RPC.TYPE_JOB,'KS') AS TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID,
			 '' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, FORMAT(PLRD.TOTAL_AMT,'#,###') AS NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID,
		  RPN.STATUS AS STATUS_NEXT,
		  PLRP.STATUS AS STATUS_CURR,
		'' AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,
		--CDC.DEP_NAME AS DEP_CREATE_NAME,
		CASE WHEN DVC.BRANCH_TYPE ='HS' THEN CDC.DEP_NAME ELSE DVC.BRANCH_NAME END AS DEP_CREATE_NAME,
		CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
		
		--NGUOI XU LY
		--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
		 NXL.NGUOIXULY AS NGUOIXULY
		FROM TR_REQUEST_DOC A
		INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS = 'C' AND PLRP.IS_HAS_CHILD=1 		
		LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
		LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
		LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
		LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
		LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
		LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
		LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
		LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
		LEFT JOIN CM_DEPARTMENT DEP ON UM.SECUR_CODE = DEP.DEP_ID
		LEFT JOIN 
		(
			SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
			dbo.PL_REQUEST_PROCESS
			GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
		) RPN ON RPN.REQ_ID=A.REQ_ID AND ( [RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)	
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
		LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
		LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
		LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
		LEFT JOIN 
		(
			SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
			dbo.CM_DMMS 
			LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
			UNION ALL
			SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
			FROM dbo.CM_DVDM
		)CMS ON CMS.DMMS_ID=A.DMMS_ID

		

		LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
			LEFT JOIN
		(		
		SELECT	Results.REQ_ID,STUFF((select ', ' + RE.TLFullName  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
		STUFF((select ', ' + RE.TLNAME  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME	
		FROM @lstREQUEST Results
		GROUP BY REQ_ID
		) NXL ON NXL.REQ_ID=A.REQ_ID
		WHERE 1 = 1
		AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
	    AND (A.REQ_CODE 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_REASON 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 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 (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')	
		AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')	
		 --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
		AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
		AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
		--AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = ''
		--OR A.DEP_CREATE LIKE '%' + @p_BRANCH_CREATE + '%'
		--)
		AND (PLRD.REQ_CODE LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
		AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_ID + '%' OR @p_PL_REQ_ID IS NULL OR @p_PL_REQ_ID = '')
		AND A.RECORD_STATUS = '1'
		AND (	
			   EXISTS(SELECT  AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND PLRP.DVDM_ID=AUTH.DVDM_ID)
			OR EXISTS(SELECT  AUTH.ROLE_ID FROM @AUTHOR_DMMS AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND A.DMMS_ID=AUTH.DMMS_ID AND A.PROCESS_ID='DMMS')
			OR EXISTS(SELECT  AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND A.DMMS_ID=AUTH.DVDM_ID AND A.PROCESS_ID='DMMS')
			OR EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND  TLNAME=@p_TLNAME_USER AND TYPE_JOB='KS')
		)

		--AND ( ( ( (PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND ((PLRP.DVDM_ID  IN (SELECT DVDM_ID FROM @COST_ID) ) 
		--OR ((A.DMMS_ID IN (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID =@p_BRANCH_LOGIN AND (DEP_ID=@DEP_ID OR @BRANCH_TYPE <>'HS')) 
		--OR A.DMMS_ID IN (SELECT DVDM_ID FROM @COST_ID)) AND A.PROCESS_ID='DMMS'))))
		--OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND  TLNAME=@p_TLNAME_USER AND TYPE_JOB='KS') ))
		
		AND (( @p_AUTH_STATUS='A' AND  A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)
		AND (RPC.TYPE_JOB <>'TP' OR RPC.TYPE_JOB IS NULL OR RPC.TYPE_JOB ='')
		AND (
			((RPC.REQ_ID IS NOT NULL OR RPC.REQ_ID <> '') AND @p_IS_TRANSFER = 'Y')
			OR ((RPC.REQ_ID IS NULL OR RPC.REQ_ID = '') AND @p_IS_TRANSFER = 'N')
			OR @p_IS_TRANSFER = '' OR @p_IS_TRANSFER IS NULL
			)
		AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = ''
			OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY  + '%')
		--AND(
		--	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--	OR
		--	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',1) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--)
		--LUCTV 10-03-2021 Ơ MAN HINH DIEU PHOI PYCMS NEU PHIEU DA DUOC NHAN VIEN XU LY GUI PHE DUYET THI KHONG CAN HIEN THI
		AND ((NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =A.REQ_ID AND TYPE_JOB ='XL'  AND STATUS_JOB ='P' AND PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_ID))) OR A.PROCESS_ID ='DVCM')
		ORDER BY A.CREATE_DT DESC
		END
		ELSE IF(@p_TYPE='DMMS')
		BEGIN
			
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
               A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
               A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
			   D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
			   CMS.DMMS_NAME,A.DMMS_ID,
			   RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
			   RP.ROLE_USER,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' 
				ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
				RPN.NOTES AS PROCESS_STATUS_NEXT
		,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
		DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
		CASE WHEN DVC.BRANCH_TYPE ='HS' THEN DVC.BRANCH_NAME ELSE DEP.DEP_NAME END AS BRANCH_CREATE_NAME,
		--DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
		PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID,
			 '' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, FORMAT(PLRD.TOTAL_AMT,'#,###') AS NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID,
		   RPN.STATUS AS STATUS_NEXT,
		  PLRP.STATUS AS STATUS_CURR,
		RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,
		CASE WHEN DVC.BRANCH_TYPE ='HS' THEN CDC.DEP_NAME ELSE DVC.BRANCH_NAME END AS DEP_CREATE_NAME,
		CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
		
		--NGUOI XU LY
		--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
		NXL.NGUOIXULY AS NGUOIXULY
		FROM TR_REQUEST_DOC A 	
		LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND	PLRP.STATUS <>'U' AND PLRP.PROCESS_ID = 'DMMS'
		LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID 
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
		LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
		LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
		LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
		LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
		LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
		LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
		LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
		LEFT JOIN CM_DEPARTMENT DEP ON UM.SECUR_CODE = DEP.DEP_ID
		LEFT JOIN 
		(
			SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
			dbo.PL_REQUEST_PROCESS
			GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
		) RPN ON RPN.REQ_ID=A.REQ_ID AND( [RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)	
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
		LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R') AND A.PROCESS_ID <>'PDHT'
		LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
		LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
		LEFT JOIN 
		(
			SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
			dbo.CM_DMMS 
			LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
			UNION ALL
			SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
			FROM dbo.CM_DVDM
		)CMS ON CMS.DMMS_ID=A.DMMS_ID
		LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
		LEFT JOIN
		(		
		SELECT	Results.REQ_ID,STUFF((select ', ' + RE.TLFullName  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
		STUFF((select ', ' + RE.TLNAME  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME	
		FROM @lstREQUEST Results
		GROUP BY REQ_ID
		) NXL ON NXL.REQ_ID=A.REQ_ID
		

		WHERE 1 = 1
		AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
	    AND (A.REQ_CODE 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_REASON 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 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 (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
		
		AND (@p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='' 
		OR (RPC.TYPE_JOB LIKE '%' + @p_PROCESS_STATUS + '%'  AND (@p_PROCESS_STATUS='KS' OR @p_PROCESS_STATUS='TP') 
				AND EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD Temp   WHERE Temp.REQ_ID=A.REQ_ID AND Temp.STATUS_JOB='P' AND Temp.TYPE_JOB='XL')
		OR 	 (RPC.TYPE_JOB LIKE '%' + @p_PROCESS_STATUS + '%'  AND @p_PROCESS_STATUS='XL') 	
		OR A.USER_DVMS =@p_TLNAME_USER
		)
		)
		
		 --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
		AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
		AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
		AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = ''
		OR A.DEP_CREATE LIKE '%' + @p_BRANCH_CREATE + '%'
		)
		AND (PLRD.REQ_CODE LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
		AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_ID + '%' OR @p_PL_REQ_ID IS NULL OR @p_PL_REQ_ID = '')
		AND A.RECORD_STATUS = '1'
		
		AND (				
			   EXISTS(SELECT  AUTH.ROLE_ID FROM @AUTHOR_DMMS AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND A.DMMS_ID=AUTH.DMMS_ID AND A.PROCESS_ID='DMMS')
			OR EXISTS(SELECT  AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND A.DMMS_ID=AUTH.DVDM_ID AND A.PROCESS_ID='DMMS')
			OR EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND PROCESS_ID=PLRP.ID AND PLRP.PROCESS_ID='DMMS')
			OR(A.USER_DVMS =@p_TLNAME_USER)
		)

		--AND ( ((PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) 
		
		--AND ( ( A.DMMS_ID IN (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID =@p_BRANCH_LOGIN AND (DEP_ID=@DEP_ID  OR @BRANCH_TYPE <>'HS')) 
		--OR A.DMMS_ID IN (SELECT DVDM_ID FROM @COST_ID) )))
		
		--OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND PROCESS_ID=PLRP.ID AND PLRP.PROCESS_ID='DMMS')))
		
		AND (( @p_AUTH_STATUS='A' AND  A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)
		--AND(
		--	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--	OR
		--	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',1) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--)
		AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = ''
			OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY  + '%')
		-- LUCTV: 10-03-2021 BO SUNG DIEU KIEN NEU CHUA DIEU PHOI THI KHONG TIM THAY O MAN HINH DMMS
		AND ((EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =A.REQ_ID AND TLNAME = @p_TLNAME_USER AND PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_ID)) OR LEN(A.REQ_CODE) >15))
		--AND (A.USER_DVMS =@p_TLNAME_USER)
		ORDER BY A.CREATE_DT DESC
		END

		ELSE IF(@p_TYPE='PDYC')
		BEGIN
			
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
               A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
               A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
			   D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
			   CMS.DMMS_NAME,A.DMMS_ID,
			   RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
			   PLRP.ROLE_USER,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' 
				ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
				RPN.NOTES AS PROCESS_STATUS_NEXT
		,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
		DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
		DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
		PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID,
			 '' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID,
		  RPN.STATUS AS STATUS_NEXT,
		  PLRP.STATUS AS STATUS_CURR,
		RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,
		A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,

		--NGUOI XU LY
		--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
		NXL.NGUOIXULY AS NGUOIXULY
		FROM TR_REQUEST_DOC A 	
		INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS<>'U'		
		LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
		LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
		LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
		LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
		LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
		LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
		LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
		LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
		LEFT JOIN 
		(
			SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
			dbo.PL_REQUEST_PROCESS
			GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
		) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)	
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
		LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.REQ_ID AND  (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
		LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
		LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
		LEFT JOIN 
		(
			SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
			dbo.CM_DMMS 
			LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
			UNION ALL
			SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
			FROM dbo.CM_DVDM
		)CMS ON CMS.DMMS_ID=A.DMMS_ID
		LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
		LEFT JOIN
		(		
		SELECT	Results.REQ_ID,STUFF((select ', ' + RE.TLFullName  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
		STUFF((select ', ' + RE.TLNAME  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME	
		FROM @lstREQUEST Results
		GROUP BY REQ_ID
		) NXL ON NXL.REQ_ID=A.REQ_ID
		
		WHERE 1 = 1
		AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
	    AND (A.REQ_CODE 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_REASON 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 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 (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')	
		AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')	
		 --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
		AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
		AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
		AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = ''
		OR A.DEP_CREATE LIKE '%' + @p_BRANCH_CREATE + '%'
		)
		AND (PLRD.REQ_CODE LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
		AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_ID + '%' OR @p_PL_REQ_ID IS NULL OR @p_PL_REQ_ID = '')
		AND A.RECORD_STATUS = '1'
		--AND (PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND  PLRP.PROCESS_ID <>'DMMS' AND PLRP.PROCESS_ID<>'APPNEW' AND (
		-- (PLRP.DVDM_ID IN (SELECT DVDM_ID FROM @COST_ID) OR PLRP.DVDM_ID ='' OR PLRP.DVDM_ID  IS NULL))

		 AND (EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND (PLRP.DVDM_ID=AUTH.DVDM_ID OR PLRP.DVDM_ID ='' OR PLRP.DVDM_ID  IS NULL) AND  PLRP.PROCESS_ID <>'DMMS' AND PLRP.PROCESS_ID<>'APPNEW'))

		AND (( @p_AUTH_STATUS='A' AND  A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)

		AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = ''
			OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY  + '%')

		ORDER BY A.CREATE_DT DESC
		END
		ELSE IF(@p_TYPE='DVCM')
		BEGIN
			
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
               A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,ISNULL(A.TOTAL_AMT,0.00) AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
               A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
			   D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
			   CMS.DMMS_NAME,A.DMMS_ID,
			   RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
			   PLRP.ROLE_USER,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' 
				ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
				RPN.NOTES AS PROCESS_STATUS_NEXT
		,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
		DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
		DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
		PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID,
			 '' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID,
		  RPN.STATUS AS STATUS_NEXT,
		  PLRP.STATUS AS STATUS_CURR,
		RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
		--NGUOI XU LY
		--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
		NXL.NGUOIXULY AS NGUOIXULY
		FROM TR_REQUEST_DOC A 	
		INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS<>'U'	
		LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
		LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
		LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
		LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
		LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
		LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
		LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
		LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
		LEFT JOIN 
		(
			SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
			dbo.PL_REQUEST_PROCESS
			GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
		) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)	
		
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
		LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')  AND A.PROCESS_ID <>'PDHT'
		LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
		LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
		LEFT JOIN 
		(
			SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
			dbo.CM_DMMS 
			LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
			UNION ALL
			SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
			FROM dbo.CM_DVDM
		)CMS ON CMS.DMMS_ID=A.DMMS_ID
		LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
		LEFT JOIN
		(		
		SELECT	Results.REQ_ID,STUFF((select ', ' + RE.TLFullName  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
		STUFF((select ', ' + RE.TLNAME  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME	
		FROM @lstREQUEST Results
		GROUP BY REQ_ID
		) NXL ON NXL.REQ_ID=A.REQ_ID
		

		WHERE 1 = 1
		AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
	    AND (A.REQ_CODE 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_REASON 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 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 (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')	
		AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')	
		 --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
		AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
		AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
		AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = ''
		OR A.DEP_CREATE LIKE '%' + @p_BRANCH_CREATE + '%'
		)
		AND (PLRD.REQ_CODE LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
		AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_ID + '%' OR @p_PL_REQ_ID IS NULL OR @p_PL_REQ_ID = '')
		AND A.RECORD_STATUS = '1'
		
		AND (( @p_AUTH_STATUS='A' AND  A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		

		AND(((PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND ((PLRP.DVDM_ID  IN (SELECT DVDM_ID FROM @COST_ID) )))
		OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER  AND PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND PLRP.PROCESS_ID='DVCM') ))

		AND (	
			   EXISTS(SELECT  AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND PLRP.DVDM_ID=AUTH.DVDM_ID)
			
			OR EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER  AND PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND PLRP.PROCESS_ID='DVCM')
		)



		AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)

		AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = ''
			OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY  + '%')
		ORDER BY A.CREATE_DT DESC
		END
		ELSE IF(@p_TYPE='PLDVCM')
		BEGIN
			
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
               A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,ISNULL(A.TOTAL_AMT,0.00) AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
               A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
			   D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
			   CMS.DMMS_NAME,A.DMMS_ID,
			   RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
			   --PLRP.ROLE_USER,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' 
				ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
				RPN.NOTES AS PROCESS_STATUS_NEXT
		,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
		DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
		DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
		PLRD.REQ_NAME AS PL_REQ_NAME,
		--RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID,
			 '' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,
			 --PLRP.ID AS REF_ID,
		  RPN.STATUS AS STATUS_NEXT,
		  --PLRP.STATUS AS STATUS_CURR,
		--RPC.STATUS_JOB AS STATUS_JOB,
		A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
		--NGUOI XU LY
		--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
		NXL.NGUOIXULY AS NGUOIXULY
		FROM TR_REQUEST_DOC A 	
		--INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS<>'U'	
		LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
		LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
		LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
		LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
		LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
		LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
		LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
		LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
		LEFT JOIN 
		(
			SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
			dbo.PL_REQUEST_PROCESS
			GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
		) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)	
		
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
		--LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')  AND A.PROCESS_ID <>'PDHT'
		--LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
		--LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
		LEFT JOIN 
		(
			SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
			dbo.CM_DMMS 
			LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
			UNION ALL
			SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
			FROM dbo.CM_DVDM
		)CMS ON CMS.DMMS_ID=A.DMMS_ID
		LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
		LEFT JOIN
		(		
		SELECT	Results.REQ_ID,STUFF((select ', ' + RE.TLFullName  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
		STUFF((select ', ' + RE.TLNAME  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME	
		FROM @lstREQUEST Results
		GROUP BY REQ_ID
		) NXL ON NXL.REQ_ID=A.REQ_ID
		

		WHERE 1 = 1
		AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
	    AND (A.REQ_CODE 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_REASON 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 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 (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')	
		AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')	
		 --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
		AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
		AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
		AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = ''
		OR A.DEP_CREATE LIKE '%' + @p_BRANCH_CREATE + '%'
		)
		AND (PLRD.REQ_CODE LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
		AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_ID + '%' OR @p_PL_REQ_ID IS NULL OR @p_PL_REQ_ID = '')
		AND A.RECORD_STATUS = '1'
		
		AND (( @p_AUTH_STATUS='A' AND  A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		

		AND(	EXISTS (
					SELECT * FROM PL_REQUEST_COSTCENTER  PRC
					INNER JOIN @COST_ID COST ON PRC.COST_ID = COST.DVDM_ID
					WHERE PRC.REQ_ID = A.PL_REQ_ID
					)
					OR @DEP_ID='DEP000000000022'
										
			)

		AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)

		AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = ''
			OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY  + '%')
		--AND(((PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND ((PLRP.DVDM_ID  IN (SELECT DVDM_ID FROM @COST_ID) )))
		--	OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER  AND PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID) ))

		ORDER BY A.CREATE_DT DESC
		END

		ELSE	IF(@p_TYPE='DVKD_PARENT' )
		BEGIN
			
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
               A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
               A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
			   D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
			   CMS.DMMS_NAME,A.DMMS_ID,
			   RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
			   RP.ROLE_USER,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' 
				ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
				RPN.NOTES AS PROCESS_STATUS_NEXT
		,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
		DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
		DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
		PLRD.REQ_NAME AS PL_REQ_NAME,'' TYPE_JOB,'' AS USER_JOB,'' AS USER_JOB_NAME,'' AS TRANSFER_MAKER,CAST(NULL AS DATETIME) AS TRANFER_DT ,
		'' AS TRANSFER_MAKER_ID,'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,RP.ID AS REF_ID,
		  RPN.STATUS AS STATUS_NEXT,
		  RP.STATUS AS STATUS_CURR,
		'' AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
		--NGUOI XU LY
		--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
		NXL.NGUOIXULY AS NGUOIXULY
		FROM TR_REQUEST_DOC A
		LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
		LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
		LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
		LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
		LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
		LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
		LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
		LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
		LEFT JOIN 
		(
			SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
			dbo.PL_REQUEST_PROCESS
			GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
		) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)	
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
		LEFT JOIN 
		(
			SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
			dbo.CM_DMMS 
			LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
			UNION ALL
			SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
			FROM dbo.CM_DVDM
		)CMS ON CMS.DMMS_ID=A.DMMS_ID
		LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
		LEFT JOIN
		(		
		SELECT	Results.REQ_ID,STUFF((select ', ' + RE.TLFullName  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
		STUFF((select ', ' + RE.TLNAME  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME	
		FROM @lstREQUEST Results
		GROUP BY REQ_ID
		) NXL ON NXL.REQ_ID=A.REQ_ID
		

		WHERE 1 = 1
		AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
	    AND (A.REQ_CODE 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_REASON 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 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 (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')	
		AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')	
		 --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
		AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
		AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
		AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '')
		AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
		
		AND A.RECORD_STATUS = '1'
		AND (A.BRANCH_CREATE =@p_BRANCH_LOGIN AND (A.DEP_CREATE=@DEP_ID OR A.DEP_CREATE ='' OR A.DEP_CREATE IS NULL))
		AND (( @p_AUTH_STATUS='A' AND  A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)
		AND (A.REQ_PARENT_ID IS NULL  OR A.REQ_PARENT_ID='')

		AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = ''
			OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY  + '%')

		ORDER BY A.CREATE_DT DESC
		END
		ELSE IF(@p_TYPE='DMMS_PARENT')
		BEGIN
			
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
               A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
               A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
			   D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
			   CMS.DMMS_NAME,A.DMMS_ID,
			   RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
			   RP.ROLE_USER,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' 
			   ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
				RPN.NOTES AS PROCESS_STATUS_NEXT
		,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
		DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
		DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
		PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID,
			 '' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID,
		   RPN.STATUS AS STATUS_NEXT,
		  PLRP.STATUS AS STATUS_CURR,
		RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
		--NGUOI XU LY
		--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
		NXL.NGUOIXULY AS NGUOIXULY
		FROM TR_REQUEST_DOC A 	
		INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND	PLRP.STATUS <>'U'
		LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID 
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
		LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
		LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
		LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
		LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
		LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
		LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
			LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
		LEFT JOIN 
		(
			SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
			dbo.PL_REQUEST_PROCESS
			GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
		) RPN ON RPN.REQ_ID=A.REQ_ID AND( [RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)	
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
		LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R') AND A.PROCESS_ID <>'PDHT'
		LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
		LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
		LEFT JOIN 
		(
			SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
			dbo.CM_DMMS 
			LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
			UNION ALL
			SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
			FROM dbo.CM_DVDM
		)CMS ON CMS.DMMS_ID=A.DMMS_ID
		LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
		LEFT JOIN
		(		
		SELECT	Results.REQ_ID,STUFF((select ', ' + RE.TLFullName  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
		STUFF((select ', ' + RE.TLNAME  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME	
		FROM @lstREQUEST Results
		GROUP BY REQ_ID
		) NXL ON NXL.REQ_ID=A.REQ_ID
		

		WHERE 1 = 1
		AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
	    AND (A.REQ_CODE 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_REASON 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 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 (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')	
		AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')	
		 --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
		AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
		AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
		AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '')
		AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
		
		AND A.RECORD_STATUS = '1'
		AND (((PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND ( A.DMMS_ID IN (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID =@p_BRANCH_LOGIN AND (DEP_ID=@DEP_ID  OR @BRANCH_TYPE <>'HS')) OR A.DMMS_ID IN (SELECT DVDM_ID FROM @COST_ID) )OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND PROCESS_ID=PLRP.ID) )) OR A.USER_DVMS=@p_TLNAME_USER)
		AND (( @p_AUTH_STATUS='A' AND  A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)
		AND (A.REQ_PARENT_ID IS NULL  OR A.REQ_PARENT_ID='')

		AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = ''
			OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY  + '%')

		ORDER BY A.CREATE_DT DESC
		END
		ELSE	IF(@p_TYPE='TTCT' )
		BEGIN
			
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
               A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
               A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
			   D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
			   CMS.DMMS_NAME,A.DMMS_ID,
			   RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
			   RP.ROLE_USER,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' 
				ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
				RPN.NOTES AS PROCESS_STATUS_NEXT
		,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
		DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
		DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
		PLRD.REQ_NAME AS PL_REQ_NAME,'' TYPE_JOB,'' AS USER_JOB,'' AS USER_JOB_NAME,'' AS TRANSFER_MAKER,CAST(NULL AS DATETIME) AS TRANFER_DT ,
		'' AS TRANSFER_MAKER_ID,'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,RP.ID AS REF_ID,
		  RPN.STATUS AS STATUS_NEXT,
		  RP.STATUS AS STATUS_CURR,
		'' AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
		--NGUOI XU LY
		--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
		NXL.NGUOIXULY AS NGUOIXULY
		FROM TR_REQUEST_DOC A
		LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
		LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
		LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
		LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
		LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
		LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
		LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
		LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
		LEFT JOIN 
		(
			SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
			dbo.PL_REQUEST_PROCESS
			GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
		) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)	
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
		LEFT JOIN 
		(
			SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
			dbo.CM_DMMS 
			LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
			UNION ALL
			SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
			FROM dbo.CM_DVDM
		)CMS ON CMS.DMMS_ID=A.DMMS_ID
		LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
		LEFT JOIN
		(		
		SELECT	Results.REQ_ID,STUFF((select ', ' + RE.TLFullName  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
		STUFF((select ', ' + RE.TLNAME  
		FROM @lstREQUEST RE
		WHERE RE.REQ_ID=Results.REQ_ID
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME	
		FROM @lstREQUEST Results
		GROUP BY REQ_ID
		) NXL ON NXL.REQ_ID=A.REQ_ID
		

		WHERE 1 = 1
		AND A.PL_REQ_ID=@p_PL_REQ_ID

		ORDER BY A.CREATE_DT DESC
		END
	END
   END