DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

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

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)

	DECLARE @AUTHOR TABLE

	(

		ROLE_ID VARCHAR(20),

		BRANCH_ID VARCHAR(20),

		DEP_ID VARCHAR(20),

		BRANCH_TYPE VARCHAR(20)

	)

	DECLARE @AUTHOR_DVDM TABLE

	(

		ROLE_ID VARCHAR(20),

		BRANCH_ID VARCHAR(20),

		DEP_ID VARCHAR(20),

		DVDM_ID VARCHAR(20)

	)

	DECLARE @AUTHOR_DMMS TABLE

	(

		ROLE_ID VARCHAR(20),

		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)

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

		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

		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_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 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.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(

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

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

		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 

		(

			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

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

			   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(

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

		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

		FROM TR_REQUEST_DOC A 	

		INNER 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 

		(

			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

		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_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 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 (RPC.TYPE_JOB 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 (				

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

		)

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

		)

		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

		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

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

			(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='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

		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

		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_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 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 (( @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(

			(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='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 + ' - '+ CDC.DEP_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

		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

		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_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 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 (( @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

					)

					)

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

		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

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

			(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='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

		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

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

		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(

			(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

	END

   END