ALTER PROCEDURE dbo.PL_REQUEST_TRANSFER_Search
@p_REQ_ID	varchar(15)  = NULL,
@p_REQ_CODE	nvarchar(100)  = NULL,
@p_REQ_NAME	nvarchar(200)  = NULL,
@p_REQ_DT	varchar(20) = NULL,
@p_REQ_TYPE	int = NULL,
@p_REQ_CONTENT NVARCHAR(1000)=NULL,
@p_REQ_REASON NVARCHAR(500)=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	varchar(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	varchar(20) = NULL,
@p_PROCESS_ID varchar(15) = NULL,
@p_BRANCH_ID VARCHAR(15)=NULL,
@p_DEP_ID VARCHAR(15) = NULL,
@p_BRANCH_LOGIN VARCHAR(15),
@p_ROLE_USER VARCHAR(20),
@p_TLNAME_USER VARCHAR(15),
@p_FR_DATE varchar(20) = NULL,
@p_TO_DATE varchar(20) = NULL,
@p_TYPE_TRANFER VARCHAR(15),
@p_TYPE VARCHAR(15),
@p_YEAR INT,
@p_TOP	INT = 10,
@p_IS_TRANSFER VARCHAR(10) = NULL,
@p_NGUOIXULY NVARCHAR(15) = NULL,

@p_TO_DEP_ID VARCHAR(15)=NULL,
@p_TO_BRANCH_ID VARCHAR(15)=NULL,
@p_FROM_DEP_ID VARCHAR(15)=NULL,
@p_FROM_BRANCH_ID VARCHAR(15)=NULL,
@p_TO_GD_NAME NVARCHAR(1000)=NULL,
@p_FROM_GD_NAME NVARCHAR(1000)=NULL

AS
BEGIN -- PAGING

	DECLARE @TABLE_ROLE TABLE 
	( ROLE_ID VARCHAR(20))
	INSERT INTO @TABLE_ROLE VALUES(@p_ROLE_USER)

	
	INSERT INTO @TABLE_ROLE
	SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER
	


	DECLARE @DEP_ID VARCHAR(15),@BRANCH_TYPE VARCHAR(15),@TO_BRANCH_TYPE VARCHAR(15),@FROM_BRANCH_TYPE VARCHAR(15)
	
	DECLARE
	@COST_ID TABLE (
		COST_ID VARCHAR(15)
	)

	DECLARE @DVDM_ID TABLE (
		DVDM_ID VARCHAR(15)
	)
	SET @DEP_ID =(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME_USER)
	SET @BRANCH_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN)
		
	-- ĐƠN VỊ CHUYỂN
	SET @FROM_BRANCH_TYPE = (SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@p_FROM_BRANCH_ID)
	-- ĐƠN VỊ NHẬN
	SET @TO_BRANCH_TYPE = (SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@p_TO_BRANCH_ID)


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


	DECLARE @AUTHOR TABLE
	(
		ROLE_ID VARCHAR(100),
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20)
	)
	DECLARE @AUTHOR_DVDM TABLE
	(
		ROLE_ID VARCHAR(100),
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20),
		DVDM_ID VARCHAR(20)
	)
	INSERT INTO @AUTHOR
	(
	    ROLE_ID,
	    BRANCH_ID,
	    DEP_ID
	)
	SELECT RoleName,TLSUBBRID,SECUR_CODE FROM dbo.TL_USER TU
	WHERE TLNANME=@p_TLNAME_USER
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TLSUBBRID,SECUR_CODE 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='')
	WHERE TLNANME=@p_TLNAME_USER AND TM.ROLE_NEW IS NOT NULL
	UNION ALL
	SELECT TM1.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE FROM dbo.TL_SYS_ROLE_MAPPING TM1
	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 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='')
	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 AND TM.ROLE_NEW IS NOT NULL
	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 @COST_ID
	SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@p_BRANCH_LOGIN
	INSERT INTO @DVDM_ID
	SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM @COST_ID) GROUP BY DVDM_ID

	---- NGUOI XU lY TIEP THEO 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,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM 
		(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC WHERE 
			EXISTS(SELECT PLRD.REQ_ID FROM dbo.PL_REQUEST_DOC PLRD 
			WHERE PLRD.REQ_ID=PRC.REQ_ID 

		) AND PRC.STATUS='C' AND PRC.PROCESS_ID <>'APPROVE' AND PRC.PROCESS_ID <>'SIGN' 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 
		(
		SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER 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
		UNION ALL
		SELECT TU.TLNAME,US.TLFullName,TU.ROLE_NEW RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.TL_SYS_ROLE_MAPPING TU
		LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME
		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.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID
		WHERE  CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
		) 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 ='')
		LEFT JOIN dbo.PL_REQUEST_DOC RD ON RD.REQ_ID = PL.REQ_ID
		LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
		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 PLRD.REQ_ID FROM dbo.PL_REQUEST_DOC PLRD 
			WHERE PLRD.REQ_ID=PRC.REQ_ID 
		)  AND PRC.STATUS='C' AND PRC.PROCESS_ID <>'APPROVE' AND PRC.PROCESS_ID ='SIGN' 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 
		(
		SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER 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 TM.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 ='')
		LEFT JOIN dbo.PL_REQUEST_DOC RD ON RD.REQ_ID = PL.REQ_ID
		LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
		WHERE  STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = RD.SIGN_USER)

		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 PLRD.REQ_ID FROM dbo.PL_REQUEST_DOC PLRD 
			WHERE PLRD.REQ_ID=PRC.REQ_ID 

		)  AND PRC.STATUS='C' AND PRC.PROCESS_ID <>'APPROVE' AND PRC.PROCESS_ID <>'SIGN' 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 dbo.TL_USER 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
				UNION ALL
				SELECT TU.TLNAME,US.TLFullName,TU.ROLE_NEW RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID  FROM dbo.TL_SYS_ROLE_MAPPING TU
				LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME
				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.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID
				WHERE  CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
			) 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_TYPE='DVKD')
	BEGIN	
		-- PAGING BEGIN
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
               A.TOTAL_AMT AS TOTAL_AMT,A.NOTES, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,
			   UDV.TLFullName AS CHECKER_NAME_DV,
			   A.APPROVE_DT,
               A.PROCESS_ID,
			   D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
			   --CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
			   --WHEN A.PROCESS_ID<> 'APPROVE' AND A.AUTH_STATUS='A' THEN N'Chờ duyệt' ELSE D.AUTH_STATUS_NAME END AS AUTH_STATUS_NAME,
			    CASE WHEN A.PROCESS_ID='APPROVE' OR A.AUTH_STATUS ='A' THEN N'Đã duyệt'
			    ELSE D.AUTH_STATUS_NAME END AS AUTH_STATUS_NAME,
			   G.BRANCH_CODE,
			   CASE WHEN A.BRANCH_CREATE <> 'DV0001' THEN G.BRANCH_NAME ELSE G.BRANCH_NAME+' - ' + DEP.DEP_NAME END AS BRANCH_NAME,
			   UP.TLFullName AS CHECKER_NAME_PROCESS,
			   RP.APPROVE_DT AS APPROVE_DT_PROCESS,
			   UC.TLFullName AS MAKER_NAME,
			   RPN.PROCESS_ID AS PROCESS_ID_NEXT,
			   RP.ROLE_USER, 
			   RP.NOTES AS PROCESS_STATUS , 
			   RPN.NOTES AS PROCESS_STATUS_NEXT,
			   A.DVDM_APP_ID,
			   CD.DVDM_NAME AS DVDM_APP_NAME,
			   A.REQ_PARENT_ID,
			   PARENT.REQ_NAME AS REQ_PARENT_NAME,
			   PARENT.REQ_CODE AS REQ_PARENT_CODE,
			   PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
			   A.BRANCH_FEE,
			   A.DEP_ID,
			   A.DEP_FEE,
			   DEP.DEP_NAME,
			   DEP.DEP_CODE,
			   BF.BRANCH_NAME AS BRANCH_FEE_NAME,
			   BF.BRANCH_CODE AS BRANCH_FEE_CODE,
			   DF.DEP_NAME AS DEP_FEE_NAME,
			   DF.DEP_CODE AS DEP_FEE_CODE,
			   '' AS BRANCH_DEP,
			   '' AS BRANCH_DEP_FEE,
			   


			   '' AS TYPE_JOB,
			   '' AS USER_JOB,
			   '' AS USER_JOB_NAME,
			   '' AS TRANSFER_MAKER,
			    A.CREATE_DT AS TRANFER_DT ,
				'' AS TRANSFER_MAKER_ID,
			   A.EFFEC_DT,A.IS_BACKDAY,
			   '' AS TYPE_JOB_XL,
			   '' AS USER_JOB_XL,
			   RP.ID AS REF_ID,
			   RPN.STATUS AS STATUS_NEXT,
			   RP.STATUS AS STATUS_CURR,
			   '' AS STATUS_JOB,
			   A.BRANCH_CREATE,
			   A.DEP_CREATE,
			   A.REQ_LINE,
			   A.TC_NOTES,
			   A.SIGN_USER,
			   TL.TLFullName AS SIGN_USER_NAME,
			   A.KT_NOTES,
			   A.IS_CHECKALL,
			   A.BASED_CONTENT,
			   A.PL_BASED_ID, PL_B.REQ_CODE AS PL_BASED_CODE, PL_B.REQ_CONTENT AS PL_BASED_CONTENT, 
			   '' AS IS_TRANSFER,
			   --NGUOI XU LY
			  NXL.NGUOIXULY AS NGUOIXULY, A.TK_HDQT_NOTES, A.TK_TGD_NOTES, A.OTHER_NOTES
			 -- SELECT END 
		FROM PL_REQUEST_DOC A 	
		--LEFT JOIN	dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID  AND PLRP.STATUS <>'U'
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
		LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_ID

		LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
		LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE

		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
	
		LEFT JOIN dbo.PL_REQUEST_PROCESS RPN 
		ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') )
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID IN(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID AND  PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID)
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
		LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_ID
		LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_ID
		LEFT JOIN dbo.PL_REQUEST_DOC PL_B ON PL_B.REQ_ID=A.PL_BASED_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.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.REQ_REASON LIKE '%' + @p_REQ_REASON + '%' OR @p_REQ_REASON IS NULL OR @p_REQ_REASON = '')
		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 (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND	(@p_CREATE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_CREATE_DT,103)) = 0) 
		AND	(@p_APPROVE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_APPROVE_DT,103)) = 0) 
		AND	(@p_REQ_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_REQ_DT,103)) = 0) 
		AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
		AND A.RECORD_STATUS = '1'
		
--		AND(
--			@p_REQ_TYPE = -1  OR @p_REQ_TYPE IS NULL
--			OR
--				(
--					@p_REQ_TYPE=1 AND EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
--				)
--				OR
--				(
--						@p_REQ_TYPE=0 AND NOT EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
--				)
--			)

		AND    (A.MAKER_ID=@p_TLNAME_USER OR 
				(A.PROCESS_ID ='APPROVE' AND A.BRANCH_CREATE=@p_BRANCH_LOGIN AND (A.DEP_CREATE=@DEP_ID OR @BRANCH_TYPE<>'HS'))
				OR EXISTS(SELECT ID FROM dbo.PL_ROLE_DATA_CONFIG PLRDC WHERE PLRDC.ROLE_TYPE='PL_REQUEST_DOC_ALL'
          AND (PLRDC.BRANCH_ID=@p_TLNAME_USER OR (PLRDC.BRANCH_ID = @p_BRANCH_LOGIN AND PLRDC.DEP_ID = @DEP_ID)))
				OR EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS PLRP WHERE PLRP.PROCESS_ID='APPNEW' AND PLRP.REQ_ID=A.REQ_ID
																			AND EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND AUTH.BRANCH_ID=PLRP.BRANCH_ID 
																														AND(PLRP.DEP_ID=AUTH.DEP_ID OR PLRP.DEP_ID IS NULL OR PLRP.DEP_ID ='') ))
			--	
				
				--OR (EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS PLRP WHERE A.REQ_ID=PLRP.REQ_ID AND PLRP.PROCESS_ID='APPNEW' 
				--												AND PLRP.ROLE_USER IN (SELECT ROLE_ID FROM @TABLE_ROLE) AND PLRP.BRANCH_ID=@p_BRANCH_LOGIN 
				--												AND (PLRP.DEP_ID=@DEP_ID OR PLRP.DEP_ID IS NULL OR PLRP.DEP_ID =''))) 
			--


		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 IN('U','R','E') AND (A.PROCESS_ID <> 'APPROVE' OR A.PROCESS_ID IS NULL)) OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		--AND ((A.AUTH_STATUS='E' AND A.MAKER_ID=@p_TLNAME_USER) OR (A.AUTH_STATUS <> 'E' AND (A.MAKER_ID=@p_TLNAME_USER OR (PLRP.PROCESS_ID='DVKD' AND PLRP.ROLE_USER=@p_ROLE_USER AND PLRP.BRANCH_ID=@p_BRANCH_LOGIN AND (PLRP.DEP_ID=@DEP_ID OR PLRP.DEP_ID IS NULL OR PLRP.DEP_ID ='')))))
		--
		--AND (A.AUTH_STATUS = @p_AUTH_STATUS OR @p_AUTH_STATUS ='' OR @p_AUTH_STATUS IS NULL)
		AND( (@p_AUTH_STATUS ='A' AND EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.REQ_ID AND PROCESS_ID ='APPNEW' AND STATUS ='P'))
			OR (@p_AUTH_STATUS ='E' AND A.AUTH_STATUS =@p_AUTH_STATUS)
			OR (@p_AUTH_STATUS ='R' AND A.AUTH_STATUS =@p_AUTH_STATUS)
			OR (@p_AUTH_STATUS ='W' AND EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.REQ_ID AND PROCESS_ID ='SIGN' AND STATUS ='C'))
			OR (@p_AUTH_STATUS ='G' AND EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.REQ_ID AND PROCESS_ID ='SIGN' AND STATUS ='P'))
			OR (@p_AUTH_STATUS ='U' AND EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.REQ_ID AND PROCESS_ID ='APPNEW' AND STATUS ='C'))
			OR (@p_AUTH_STATUS ='' OR @p_AUTH_STATUS IS NULL)
		)
		AND(@p_FR_DATE IS NULL OR @p_REQ_CODE IS NOT NULL OR CONVERT(DATETIME,A.REQ_DT,103) >= CONVERT(DATETIME,@p_FR_DATE,103))
		AND(@p_TO_DATE IS NULL OR @p_REQ_CODE IS NOT NULL OR CONVERT(DATETIME,A.REQ_DT,103) <= CONVERT(DATETIME,@p_TO_DATE,103))	
		AND(@p_YEAR IS NULL OR @p_YEAR=0 OR YEAR(A.REQ_DT)=@p_YEAR)	
		AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = ''
			OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY  + '%')

		AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
		AND (A.DEP_ID = @p_DEP_ID OR @p_DEP_ID IS NULL OR @p_DEP_ID = '')

		AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
		AND (A.REQ_ID IN( SELECT REQ_DOC_ID FROM PL_REQUEST_TRANSFER) )

		-- ĐƠN VỊ CHUYỂN
		AND ((@FROM_BRANCH_TYPE = 'HS' AND @p_FROM_BRANCH_ID <> '' AND @p_FROM_DEP_ID <> '' 
			AND EXISTS(SELECT * FROM PL_REQUEST_TRANSFER B WHERE B.FR_BRN_ID = @p_FROM_BRANCH_ID AND B.FR_DEP_ID = @p_FROM_DEP_ID AND B.REQ_DOC_ID = A.REQ_ID))
			OR (@FROM_BRANCH_TYPE <> 'HS' AND @p_FROM_BRANCH_ID <> '' AND EXISTS(SELECT * FROM PL_REQUEST_TRANSFER B WHERE B.FR_BRN_ID = @p_FROM_BRANCH_ID AND B.REQ_DOC_ID = A.REQ_ID))
			OR ( (  @p_FROM_BRANCH_ID IS NULL OR @p_FROM_BRANCH_ID = '')
				AND  @p_FROM_DEP_ID IS NULL OR @p_FROM_DEP_ID = ''))
		-- ĐƠN VỊ NHẬN
		AND ((@TO_BRANCH_TYPE = 'HS' AND @p_TO_BRANCH_ID <> '' AND @p_TO_DEP_ID <> '' 
			AND EXISTS(SELECT * FROM PL_REQUEST_TRANSFER B WHERE B.TO_BRN_ID = @p_TO_BRANCH_ID AND B.TO_DEP_ID = @p_TO_DEP_ID AND B.REQ_DOC_ID = A.REQ_ID))
			OR (@TO_BRANCH_TYPE <> 'HS' AND @p_TO_BRANCH_ID <> '' AND EXISTS(SELECT * FROM PL_REQUEST_TRANSFER B WHERE B.TO_BRN_ID = @p_TO_BRANCH_ID AND B.REQ_DOC_ID = A.REQ_ID))
			OR ( (  @p_TO_BRANCH_ID IS NULL OR @p_TO_BRANCH_ID = '')
				AND  @p_TO_DEP_ID IS NULL OR @p_TO_DEP_ID = ''))
		
		-- HẠNG MỤC NGÂN SÁCH CHUYỂN
		AND (@p_FROM_GD_NAME IS NULL OR @p_FROM_GD_NAME = '' OR  
			EXISTS(SELECT * FROM PL_REQUEST_TRANSFER B
							LEFT JOIN CM_GOODS C ON B.FR_GOOD_ID = C.GD_ID AND B.REQ_DOC_ID = A.REQ_ID
						WHERE C.GD_NAME LIKE '%' + @p_FROM_GD_NAME + '%' ))

		-- HẠNG MỤC NGÂN SÁCH NHẬN
		AND (@p_TO_GD_NAME IS NULL OR @p_TO_GD_NAME = '' OR  
			EXISTS(SELECT * FROM PL_REQUEST_TRANSFER B
							LEFT JOIN CM_GOODS C ON B.TO_GOOD_ID = C.GD_ID AND B.REQ_DOC_ID = A.REQ_ID
						WHERE C.GD_NAME LIKE '%' + @p_TO_GD_NAME + '%' ))
		AND (NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID = A.REQ_ID))
		ORDER BY A.CREATE_DT DESC
	-- PAGING END
	END
	ELSE IF(@p_TYPE='PDTT')
	BEGIN
		-- PAGING BEGIN
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
               A.TOTAL_AMT AS TOTAL_AMT,A.NOTES, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,

			   UDV.TLFullName AS CHECKER_NAME_DV,
			  
			   A.APPROVE_DT,
               A.PROCESS_ID,
			   D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
			   ELSE N'Chờ duyệt' END AS AUTH_STATUS_NAME,
			   G.BRANCH_CODE,
			   G.BRANCH_NAME,
			   UP.TLFullName AS CHECKER_NAME_PROCESS,
			   RP.APPROVE_DT AS APPROVE_DT_PROCESS,
			   UC.TLFullName AS MAKER_NAME,
			   RPN.PROCESS_ID AS PROCESS_ID_NEXT,
			   RPN.ROLE_USER, 
			   RP.NOTES AS PROCESS_STATUS , 
			   RPN.NOTES AS PROCESS_STATUS_NEXT,
			   A.DVDM_APP_ID,
			   CD.DVDM_NAME AS DVDM_APP_NAME,
			   A.REQ_PARENT_ID,
			   PARENT.REQ_NAME AS REQ_PARENT_NAME,
			   PARENT.REQ_CODE AS REQ_PARENT_CODE,
			   PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
			   A.BRANCH_FEE,
			   A.DEP_ID,
			   A.DEP_FEE,
			   DEP.DEP_NAME,
			   DEP.DEP_CODE,
			   BF.BRANCH_NAME AS BRANCH_FEE_NAME,
			   BF.BRANCH_CODE AS BRANCH_FEE_CODE,
			   DF.DEP_NAME AS DEP_FEE_NAME,
			   DF.DEP_CODE AS DEP_FEE_CODE,
			   '' AS BRANCH_DEP,
			   '' AS BRANCH_DEP_FEE,
			   


			   RPC.TYPE_JOB AS TYPE_JOB,
			   RPC.TLNAME AS USER_JOB,
			   TU.TLFullName AS USER_JOB_NAME,
			   TFM.TLNANME AS TRANSFER_MAKER,
			    RPC.TRANFER_DT AS TRANFER_DT ,
				RPC.MAKER_ID AS TRANSFER_MAKER_ID,
			   A.EFFEC_DT,A.IS_BACKDAY,
			   '' AS TYPE_JOB_XL,
			   '' AS USER_JOB_XL,
			   RP.ID AS REF_ID,
			   RPN.STATUS AS STATUS_NEXT,
			   PLRP.STATUS AS STATUS_CURR,
			    RPC.STATUS_JOB AS STATUS_JOB,
			   A.BRANCH_CREATE,
			   A.DEP_CREATE,
			   A.REQ_LINE,
			     A.TC_NOTES,
				  A.SIGN_USER,
			   TL.TLFullName  AS SIGN_USER_NAME,
			   A.KT_NOTES,
			     A.IS_CHECKALL,
			   A.BASED_CONTENT, 
			   '' AS IS_TRANSFER,
			   --NGUOI XU LY
			   --dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY
			    NXL.NGUOIXULY AS NGUOIXULY,A.TK_TGD_NOTES, A.OTHER_NOTES,A.TK_HDQT_NOTES
			-- SELECT END
		FROM PL_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_PROCESS RPN 
		ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.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 CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID

		LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_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 CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
		LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE
		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
		LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
		LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
		LEFT JOIN dbo.CM_PROCESS_STATUS PS ON PS.PROCESS_ID = A.PROCESS_ID
		LEFT JOIN dbo.CM_PROCESS_STATUS PSN ON PSN.PROCESS_ID = A.PROCESS_ID
		LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_ID
		LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_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.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.REQ_REASON LIKE '%' + @p_REQ_REASON + '%' OR @p_REQ_REASON IS NULL OR @p_REQ_REASON = '')
		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 (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND	(@p_CREATE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_CREATE_DT,103)) = 0) 
		AND	(@p_APPROVE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_APPROVE_DT,103)) = 0) 
		AND	(@p_REQ_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_REQ_DT,103)) = 0) 
		AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
		--AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH))
		AND A.RECORD_STATUS = '1'


--		AND(
--			@p_REQ_TYPE = -1  OR @p_REQ_TYPE IS NULL
--			OR
--				(
--					@p_REQ_TYPE=1 AND EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
--				)
--				OR
--				(
--						@p_REQ_TYPE=0 AND NOT EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
--				)
--			)

		AND EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND (
			(PLRP.PROCESS_ID='DVDC' AND PLRP.BRANCH_ID=AUTH.BRANCH_ID AND (PLRP.DEP_ID=AUTH.DEP_ID OR PLRP.DEP_ID IS NULL OR PLRP.DEP_ID=''))
			OR PLRP.DVDM_ID=AUTH.DVDM_ID
			OR ((PLRP.DVDM_ID ='' OR PLRP.DVDM_ID  IS NULL) AND (PLRP.BRANCH_ID IS NULL OR PLRP.BRANCH_ID=''))
		))
		--AND PLRP.ROLE_USER IN (SELECT ROLE_ID FROM @TABLE_ROLE) 
		--AND (
		--  (PLRP.PROCESS_ID='DVDC' AND PLRP.BRANCH_ID=@p_BRANCH_LOGIN AND (PLRP.DEP_ID=@DEP_ID OR PLRP.DEP_ID IS NULL OR PLRP.DEP_ID='')) 
		--OR ( PLRP.DVDM_ID IN (SELECT DVDM_ID FROM @DVDM_ID))
		--OR
		--( (PLRP.DVDM_ID ='' OR PLRP.DVDM_ID  IS NULL) AND (PLRP.BRANCH_ID IS NULL OR PLRP.BRANCH_ID=''))	
		--)
		AND(@p_YEAR IS NULL OR @p_YEAR=0 OR YEAR(A.REQ_DT)=@p_YEAR)	
		AND (PLRP.IS_HAS_CHILD=0 OR PLRP.IS_HAS_CHILD IS NULL)
		AND (( @p_AUTH_STATUS='A' AND  PLRP.STATUS='P') OR( @p_AUTH_STATUS='U' AND (PLRP.STATUS='C')) OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND(@p_FR_DATE IS NULL OR @p_REQ_CODE IS NOT NULL OR CONVERT(DATETIME,A.REQ_DT,103) >= CONVERT(DATETIME,@p_FR_DATE,103))
		AND(@p_TO_DATE IS NULL OR @p_REQ_CODE IS NOT NULL OR CONVERT(DATETIME,A.REQ_DT,103) <= CONVERT(DATETIME,@p_TO_DATE,103))	
		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,'TTCT-DVKD',0) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--	OR
		--	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',1) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--)

		--AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
		--AND (A.DEP_ID = @p_DEP_ID OR @p_DEP_ID IS NULL OR @p_DEP_ID = '')
		
		-- GiaNT 20/10/2021
		AND (A.REQ_ID IN( SELECT REQ_DOC_ID FROM PL_REQUEST_TRANSFER) )
    AND (NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID = A.REQ_ID))
		ORDER BY A.CREATE_DT DESC
	-- PAGING END
	END
	ELSE IF(@p_TYPE='TFJOB')
	BEGIN
		-- PAGING BEGIN
				SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
              A.TOTAL_AMT AS TOTAL_AMT,A.NOTES, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,

			   UDV.TLFullName AS CHECKER_NAME_DV,
			  
			   A.APPROVE_DT,
               A.PROCESS_ID,
			   D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
			   ELSE N'Chờ duyệt' END AS AUTH_STATUS_NAME,
			   G.BRANCH_CODE,
			   G.BRANCH_NAME,
			   UP.TLFullName AS CHECKER_NAME_PROCESS,
			   RP.APPROVE_DT AS APPROVE_DT_PROCESS,
			   UC.TLFullName AS MAKER_NAME,
			   RPN.PROCESS_ID AS PROCESS_ID_NEXT,
			   RP.ROLE_USER, 
			   RP.NOTES AS PROCESS_STATUS , 
			   RPN.NOTES AS PROCESS_STATUS_NEXT,
			   A.DVDM_APP_ID,
			   CD.DVDM_NAME AS DVDM_APP_NAME,
			   A.REQ_PARENT_ID,
			   PARENT.REQ_NAME AS REQ_PARENT_NAME,
			   PARENT.REQ_CODE AS REQ_PARENT_CODE,
			   PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
			   A.BRANCH_FEE,
			   A.DEP_ID,
			   A.DEP_FEE,
			   DEP.DEP_NAME,
			   DEP.DEP_CODE,
			   BF.BRANCH_NAME AS BRANCH_FEE_NAME,
			   BF.BRANCH_CODE AS BRANCH_FEE_CODE,
			   DF.DEP_NAME AS DEP_FEE_NAME,
			   DF.DEP_CODE AS DEP_FEE_CODE,
			   '' AS BRANCH_DEP,
			   '' AS BRANCH_DEP_FEE,
			  CASE WHEN RPC.TYPE_JOB IS NULL OR RPC.TYPE_JOB ='' THEN 'TP' ELSE RPC.TYPE_JOB END AS TYPE_JOB,
			   RPC.TLNAME AS USER_JOB,
			   TU.TLFullName AS USER_JOB_NAME,
			   TFM.TLNANME AS TRANSFER_MAKER,
			    RPC.TRANFER_DT AS TRANFER_DT ,
				RPC.MAKER_ID AS TRANSFER_MAKER_ID,
			   A.EFFEC_DT,A.IS_BACKDAY,
			   '' AS TYPE_JOB_XL,
			   '' AS USER_JOB_XL,
			   PLRP.ID AS REF_ID,
			   RPN.STATUS AS STATUS_NEXT,
			   PLRP.STATUS AS STATUS_CURR,
			    RPC.STATUS_JOB AS STATUS_JOB,
			   A.BRANCH_CREATE,
			   A.DEP_CREATE,
			   A.REQ_LINE,
			   A.TC_NOTES,
			   A.SIGN_USER,
			   TL.TLFULLNAME AS SIGN_USER_NAME,
			   A.KT_NOTES,
			     A.IS_CHECKALL,
			   A.BASED_CONTENT, 
			   '' AS IS_TRANSFER,
			  --NGUOI XU LY
			 -- dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY
			  NXL.NGUOIXULY AS NGUOIXULY,A.TK_TGD_NOTES,A.TK_HDQT_NOTES, A.OTHER_NOTES
			-- SELECT END
		FROM PL_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_PROCESS RPN 
		ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.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 CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
		LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_ID

		LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
		LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE
		LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
		LEFT JOIN dbo.CM_PROCESS_STATUS PS ON PS.PROCESS_ID = A.PROCESS_ID
		LEFT JOIN dbo.CM_PROCESS_STATUS PSN ON PSN.PROCESS_ID = A.PROCESS_ID
		LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_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 UDV ON UDV.TLNANME=A.CHECKER_ID
	
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
		LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
		LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_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.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.REQ_REASON LIKE '%' + @p_REQ_REASON + '%' OR @p_REQ_REASON IS NULL OR @p_REQ_REASON = '')
		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 (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND	(@p_CREATE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_CREATE_DT,103)) = 0) 
		AND	(@p_APPROVE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_APPROVE_DT,103)) = 0) 
		AND	(@p_REQ_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_REQ_DT,103)) = 0) 
		AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
		--AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH))
		AND A.RECORD_STATUS = '1'


--		AND(
--			@p_REQ_TYPE = -1  OR @p_REQ_TYPE IS NULL
--			OR
--				(
--					@p_REQ_TYPE=1 AND EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
--				)
--				OR
--				(
--						@p_REQ_TYPE=0 AND NOT EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
--				)
--			)


		
		AND(
		
		EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND (
			(PLRP.PROCESS_ID='DVDC' AND PLRP.BRANCH_ID=AUTH.BRANCH_ID AND (PLRP.DEP_ID=AUTH.DEP_ID OR PLRP.DEP_ID IS NULL OR PLRP.DEP_ID=''))
			OR PLRP.DVDM_ID=AUTH.DVDM_ID
			OR ((PLRP.DVDM_ID ='' OR PLRP.DVDM_ID  IS NULL) AND (PLRP.BRANCH_ID IS NULL OR PLRP.BRANCH_ID=''))
		))
			OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND TYPE_JOB='KS' AND PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID) ) )
		AND(@p_FR_DATE IS NULL OR @p_REQ_CODE IS NOT NULL OR CONVERT(DATETIME,A.REQ_DT,103) >= CONVERT(DATETIME,@p_FR_DATE,103))
		AND(@p_TO_DATE IS NULL OR @p_REQ_CODE IS NOT NULL OR CONVERT(DATETIME,A.REQ_DT,103) <= CONVERT(DATETIME,@p_TO_DATE,103))	
		AND(@p_YEAR IS NULL OR @p_YEAR=0 OR YEAR(A.REQ_DT)=@p_YEAR)	
		AND (( @p_AUTH_STATUS='A' AND  PLRP.STATUS='P') OR( @p_AUTH_STATUS='U' AND (PLRP.STATUS='C')) OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND (NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND PL_REQUEST_PROCESS_CHILD.STATUS_JOB='C' AND PL_REQUEST_PROCESS_CHILD.TYPE_JOB='TP'))
		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  + '%')

		-- GiaNT 20/10/2021
		AND (A.REQ_ID IN( SELECT REQ_DOC_ID FROM PL_REQUEST_TRANSFER) )
    AND (NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID = A.REQ_ID))
	ORDER BY A.CREATE_DT DESC
	-- PAGING END
	END

	ELSE IF(@p_TYPE='XLTT')
	BEGIN
	-- PAGING BEGIN		
				SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
             A.TOTAL_AMT AS TOTAL_AMT,A.NOTES, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,

			   UDV.TLFullName AS CHECKER_NAME_DV,
			  
			   A.APPROVE_DT,
               A.PROCESS_ID,
			   D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
			   ELSE N'Chờ duyệt' END AS AUTH_STATUS_NAME,
			   G.BRANCH_CODE,
			   CASE WHEN A.BRANCH_CREATE <> 'DV0001' THEN G.BRANCH_NAME ELSE G.BRANCH_NAME+' - ' + DEP.DEP_NAME END AS BRANCH_NAME,
			   UP.TLFullName AS CHECKER_NAME_PROCESS,
			   RP.APPROVE_DT AS APPROVE_DT_PROCESS,
			   UC.TLFullName AS MAKER_NAME,
			   RPN.PROCESS_ID AS PROCESS_ID_NEXT,
			   RPN.ROLE_USER, 
			   RP.NOTES AS PROCESS_STATUS , 
			   RPN.NOTES AS PROCESS_STATUS_NEXT,
			   A.DVDM_APP_ID,
			   CD.DVDM_NAME AS DVDM_APP_NAME,
			   A.REQ_PARENT_ID,
			   PARENT.REQ_NAME AS REQ_PARENT_NAME,
			   PARENT.REQ_CODE AS REQ_PARENT_CODE,
			   PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
			   A.BRANCH_FEE,
			   A.DEP_ID,
			   A.DEP_FEE,
			   DEP.DEP_NAME,
			   DEP.DEP_CODE,
			   BF.BRANCH_NAME AS BRANCH_FEE_NAME,
			   BF.BRANCH_CODE AS BRANCH_FEE_CODE,
			   DF.DEP_NAME AS DEP_FEE_NAME,
			   DF.DEP_CODE AS DEP_FEE_CODE,
			   '' AS BRANCH_DEP,
			   '' AS BRANCH_DEP_FEE,
			   


			   RPC.TYPE_JOB AS TYPE_JOB,
			   RPC.TLNAME AS USER_JOB,
			   TU.TLFullName AS USER_JOB_NAME,
			   TFM.TLNANME AS TRANSFER_MAKER,
			    RPC.TRANFER_DT AS TRANFER_DT ,
				RPC.MAKER_ID AS TRANSFER_MAKER_ID,
			   A.EFFEC_DT,A.IS_BACKDAY,
			   '' AS TYPE_JOB_XL,
			   '' AS USER_JOB_XL,
			   PLRP.ID AS REF_ID,
			   RPN.STATUS AS STATUS_NEXT,
			   PLRP.STATUS AS STATUS_CURR,
			   RPC.STATUS_JOB AS STATUS_JOB,
			   A.BRANCH_CREATE,
			   A.DEP_CREATE,
			   A.REQ_LINE,
			   A.TC_NOTES,
			   A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME,
			   A.KT_NOTES,
			     A.IS_CHECKALL,
			   A.BASED_CONTENT, 
			   '' AS IS_TRANSFER,
			   --NGUOI XU LY
			   --dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY
			    NXL.NGUOIXULY AS NGUOIXULY,A.TK_TGD_NOTES, A.OTHER_NOTES,A.TK_HDQT_NOTES
			-- SELECT END
		FROM PL_REQUEST_DOC A 	
		INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS <> 'U' AND PLRP.IS_HAS_CHILD=1
		LEFT JOIN dbo.PL_REQUEST_PROCESS RPN 
		ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.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 CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
		LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_ID

		LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
		LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE
		LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
		LEFT JOIN dbo.CM_PROCESS_STATUS PS ON PS.PROCESS_ID = A.PROCESS_ID
		LEFT JOIN dbo.CM_PROCESS_STATUS PSN ON PSN.PROCESS_ID = A.PROCESS_ID
		LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_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 UDV ON UDV.TLNANME=A.CHECKER_ID
	
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
		LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
		LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_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.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.REQ_REASON LIKE '%' + @p_REQ_REASON + '%' OR @p_REQ_REASON IS NULL OR @p_REQ_REASON = '')
		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 (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
	  AND	(@p_CREATE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_CREATE_DT,103)) = 0) 
		AND	(@p_APPROVE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_APPROVE_DT,103)) = 0) 
		AND	(@p_REQ_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_REQ_DT,103)) = 0) 
		AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
		--AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH))
		AND A.RECORD_STATUS = '1'


--		AND(
--			@p_REQ_TYPE = -1  OR @p_REQ_TYPE IS NULL
--			OR
--				(
--					@p_REQ_TYPE=1 AND EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
--				)
--				OR
--				(
--						@p_REQ_TYPE=0 AND NOT EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
--				)
--			)


		
		AND(
		EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND (
			(PLRP.PROCESS_ID='DVDC' AND PLRP.BRANCH_ID=AUTH.BRANCH_ID AND (PLRP.DEP_ID=AUTH.DEP_ID OR PLRP.DEP_ID IS NULL OR PLRP.DEP_ID=''))
			OR PLRP.DVDM_ID=AUTH.DVDM_ID
			OR ((PLRP.DVDM_ID ='' OR PLRP.DVDM_ID  IS NULL) AND (PLRP.BRANCH_ID IS NULL OR PLRP.BRANCH_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(@p_FR_DATE IS NULL OR @p_REQ_CODE IS NOT NULL OR CONVERT(DATETIME,A.REQ_DT,103) >= CONVERT(DATETIME,@p_FR_DATE,103))
  AND(@p_TO_DATE IS NULL OR @p_REQ_CODE IS NOT NULL OR CONVERT(DATETIME,A.REQ_DT,103) <= CONVERT(DATETIME,@p_TO_DATE,103))	
  AND(@p_YEAR IS NULL OR @p_YEAR=0 OR YEAR(A.REQ_DT)=@p_YEAR)		
  AND (( @p_AUTH_STATUS='A' AND  PLRP.STATUS='P') OR( @p_AUTH_STATUS='U' AND (PLRP.STATUS='C')) OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') 
	
			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,'TTCT-DVKD',0) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--	OR
		--	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',1) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--)

		AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
		AND (A.DEP_ID = @p_DEP_ID OR @p_DEP_ID IS NULL OR @p_DEP_ID = '')
		-- LUCTV 10 03 2021 BO SUNG DIEU KIENN NEU PHIEU CHUA DUOC DIEU PHOI THI SE KHONG TIM THAY O MAN HINH DVCM
		AND((EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD  C WHERE A.REQ_ID=C.REQ_ID AND C.PROCESS_ID=PLRP.ID AND C.TLNAME =@p_TLNAME_USER AND C.STATUS_JOB IN ('C','P'))) 
			OR (@p_NGUOIXULY IS NOT NULL AND @p_NGUOIXULY <> ''))

		-- GiaNT 20/10/2021
		AND (A.REQ_ID IN( SELECT REQ_DOC_ID FROM PL_REQUEST_TRANSFER) )
    AND (NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID = A.REQ_ID))
	ORDER BY A.CREATE_DT DESC
	-- PAGING END
	END
	ELSE IF(@p_TYPE='DVKD_PARENT')
	BEGIN
	-- PAGING BEGIN
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
              A.TOTAL_AMT AS TOTAL_AMT,A.NOTES, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,
			   UDV.TLFullName AS CHECKER_NAME_DV,
			   A.APPROVE_DT,
               A.PROCESS_ID,
			   D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
			   ELSE N'Chờ duyệt' END AS AUTH_STATUS_NAME,
			   G.BRANCH_CODE,
			   G.BRANCH_NAME,
			   UP.TLFullName AS CHECKER_NAME_PROCESS,
			   RP.APPROVE_DT AS APPROVE_DT_PROCESS,
			   UC.TLFullName AS MAKER_NAME,
			   RPN.PROCESS_ID AS PROCESS_ID_NEXT,
			   RP.ROLE_USER, 
			   RP.NOTES AS PROCESS_STATUS , 
			   RPN.NOTES AS PROCESS_STATUS_NEXT,
			   A.DVDM_APP_ID,
			   CD.DVDM_NAME AS DVDM_APP_NAME,
			   A.REQ_PARENT_ID,
			   PARENT.REQ_NAME AS REQ_PARENT_NAME,
			   PARENT.REQ_CODE AS REQ_PARENT_CODE,
			   PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
			   A.BRANCH_FEE,
			   A.DEP_ID,
			   A.DEP_FEE,
			   DEP.DEP_NAME,
			   DEP.DEP_CODE,
			   BF.BRANCH_NAME AS BRANCH_FEE_NAME,
			   BF.BRANCH_CODE AS BRANCH_FEE_CODE,
			   DF.DEP_NAME AS DEP_FEE_NAME,
			   DF.DEP_CODE AS DEP_FEE_CODE,
			   '' AS BRANCH_DEP,
			   '' AS BRANCH_DEP_FEE,
			   '' AS TYPE_JOB,
			   '' AS USER_JOB,
			   '' AS USER_JOB_NAME,
			   '' AS TRANSFER_MAKER,
			    A.CREATE_DT AS TRANFER_DT ,
				'' AS TRANSFER_MAKER_ID,
			   A.EFFEC_DT,A.IS_BACKDAY,
			   '' AS TYPE_JOB_XL,
			   '' AS USER_JOB_XL,
			   RP.ID AS REF_ID,
			   RPN.STATUS AS STATUS_NEXT,
			   RP.STATUS AS STATUS_CURR,
			   '' AS STATUS_JOB,
			   A.BRANCH_CREATE,
			   A.DEP_CREATE,
			   A.REQ_LINE,
			   A.TC_NOTES, A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME,
			   A.KT_NOTES,
			     A.IS_CHECKALL,
			   A.BASED_CONTENT, 
			   '' AS IS_TRANSFER,
			   --NGUOI XU LY
			   --dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY
			    NXL.NGUOIXULY AS NGUOIXULY,A.TK_TGD_NOTES, A.OTHER_NOTES,A.TK_HDQT_NOTES
		-- SELECT END
		FROM PL_REQUEST_DOC A 		
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
		LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_ID

		LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
		LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE

		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
	
		LEFT JOIN dbo.PL_REQUEST_PROCESS RPN 
		ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') )
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID IN(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID AND  PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID)
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
		LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_ID
		LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_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.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.REQ_REASON LIKE '%' + @p_REQ_REASON + '%' OR @p_REQ_REASON IS NULL OR @p_REQ_REASON = '')
		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 (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND	(@p_CREATE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_CREATE_DT,103)) = 0) 
		AND	(@p_APPROVE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_APPROVE_DT,103)) = 0) 
		AND	(@p_REQ_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_REQ_DT,103)) = 0) 
		AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
		AND A.RECORD_STATUS = '1'
		
		AND(
			@p_REQ_TYPE = -1  OR @p_REQ_TYPE IS NULL
			OR
				(
					@p_REQ_TYPE=1 AND EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
				)
				OR
				(
						@p_REQ_TYPE=0 AND NOT EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
				)
			)

		AND (  (A.BRANCH_CREATE = @p_BRANCH_LOGIN AND( A.DEP_CREATE=@DEP_ID OR @BRANCH_TYPE <>'HS')) OR A.IS_CHECKALL=1)

		AND A.PROCESS_ID='APPROVE'
		AND(@p_FR_DATE IS NULL OR CONVERT(DATETIME,A.REQ_DT,103) >= CONVERT(DATETIME,@p_FR_DATE,103))
		AND(@p_TO_DATE IS NULL OR CONVERT(DATETIME,A.REQ_DT,103) <= CONVERT(DATETIME,@p_TO_DATE,103))	
		AND(@p_YEAR IS NULL OR @p_YEAR=0 OR YEAR(A.REQ_DT)=@p_YEAR)	
		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  + '%')
		--AND(
		--	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--	OR
		--	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',1) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--)

		AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
		AND (A.DEP_ID = @p_DEP_ID OR @p_DEP_ID IS NULL OR @p_DEP_ID = '')

		-- GiaNT 20/10/2021
		AND (A.REQ_ID IN( SELECT REQ_DOC_ID FROM PL_REQUEST_TRANSFER) )
    AND (NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID = A.REQ_ID))
		ORDER BY A.CREATE_DT DESC
	-- PAGING END
	END
	ELSE IF(@p_TYPE='DVKD_ISALL')
	BEGIN	
	-- PAGING BEGIN
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
              A.TOTAL_AMT AS TOTAL_AMT,A.NOTES, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,
			   UDV.TLFullName AS CHECKER_NAME_DV,
			   A.APPROVE_DT,
               A.PROCESS_ID,
			   D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
			   ELSE N'Chờ duyệt' END AS AUTH_STATUS_NAME,
			   G.BRANCH_CODE,
			   G.BRANCH_NAME,
			   UP.TLFullName AS CHECKER_NAME_PROCESS,
			   RP.APPROVE_DT AS APPROVE_DT_PROCESS,
			   UC.TLFullName AS MAKER_NAME,
			   RPN.PROCESS_ID AS PROCESS_ID_NEXT,
			   RP.ROLE_USER, 
			   RP.NOTES AS PROCESS_STATUS , 
			   RPN.NOTES AS PROCESS_STATUS_NEXT,
			   A.DVDM_APP_ID,
			   CD.DVDM_NAME AS DVDM_APP_NAME,
			   A.REQ_PARENT_ID,
			   PARENT.REQ_NAME AS REQ_PARENT_NAME,
			   PARENT.REQ_CODE AS REQ_PARENT_CODE,
			   PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
			   A.BRANCH_FEE,
			   A.DEP_ID,
			   A.DEP_FEE,
			   DEP.DEP_NAME,
			   DEP.DEP_CODE,
			   BF.BRANCH_NAME AS BRANCH_FEE_NAME,
			   BF.BRANCH_CODE AS BRANCH_FEE_CODE,
			   DF.DEP_NAME AS DEP_FEE_NAME,
			   DF.DEP_CODE AS DEP_FEE_CODE,
			   '' AS BRANCH_DEP,
			   '' AS BRANCH_DEP_FEE,
			   


			   '' AS TYPE_JOB,
			   '' AS USER_JOB,
			   '' AS USER_JOB_NAME,
			   '' AS TRANSFER_MAKER,
			    A.CREATE_DT AS TRANFER_DT ,
				'' AS TRANSFER_MAKER_ID,
			   A.EFFEC_DT,A.IS_BACKDAY,
			   '' AS TYPE_JOB_XL,
			   '' AS USER_JOB_XL,
			   RP.ID AS REF_ID,
			   RPN.STATUS AS STATUS_NEXT,
			   RP.STATUS AS STATUS_CURR,
			   '' AS STATUS_JOB,
			   A.BRANCH_CREATE,
			   A.DEP_CREATE,
			   A.REQ_LINE,
			   A.TC_NOTES, A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME,
			   A.KT_NOTES,
			     A.IS_CHECKALL,
			   A.BASED_CONTENT, 
			   '' AS IS_TRANSFER,
			   --NGUOI XU LY
			   --dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY
			    NXL.NGUOIXULY AS NGUOIXULY,A.TK_TGD_NOTES, A.OTHER_NOTES,A.TK_HDQT_NOTES
			-- SELECT END
		FROM PL_REQUEST_DOC A 		
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
		LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_ID

		LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
		LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE

		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
	
		LEFT JOIN dbo.PL_REQUEST_PROCESS RPN 
		ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') )
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID IN(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID AND  PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID)
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
		LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_ID
		LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_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.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.REQ_REASON LIKE '%' + @p_REQ_REASON + '%' OR @p_REQ_REASON IS NULL OR @p_REQ_REASON = '')
		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 (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
	AND	(@p_CREATE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_CREATE_DT,103)) = 0) 
		AND	(@p_APPROVE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_APPROVE_DT,103)) = 0) 
		AND	(@p_REQ_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_REQ_DT,103)) = 0) 
		AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
		AND A.RECORD_STATUS = '1'
		
		AND(
			@p_REQ_TYPE = -1  OR @p_REQ_TYPE IS NULL
			OR
				(
					@p_REQ_TYPE=1 AND EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
				)
				OR
				(
						@p_REQ_TYPE=0 AND NOT EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
				)
			)

		AND A.IS_CHECKALL=1

		AND A.PROCESS_ID='APPROVE'
	AND(@p_FR_DATE IS NULL OR CONVERT(DATETIME,A.REQ_DT,103) >= CONVERT(DATETIME,@p_FR_DATE,103))
		AND(@p_TO_DATE IS NULL OR CONVERT(DATETIME,A.REQ_DT,103) <= CONVERT(DATETIME,@p_TO_DATE,103))	
		AND(@p_YEAR IS NULL OR @p_YEAR=0 OR YEAR(A.REQ_DT)=@p_YEAR)	
		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  + '%')
		--AND(
		--	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--	OR
		--	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',1) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--)

		--AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
		--AND (A.DEP_ID = @p_DEP_ID OR @p_DEP_ID IS NULL OR @p_DEP_ID = '')

		-- GiaNT 20/10/2021
		AND (A.REQ_ID IN( SELECT REQ_DOC_ID FROM PL_REQUEST_TRANSFER) )

		ORDER BY A.CREATE_DT DESC
	-- PAGING END
	END
	ELSE IF(@p_TYPE='TTCT_DVCM')
	BEGIN	
	-- PAGING BEGIN
		SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
              A.TOTAL_AMT AS TOTAL_AMT,A.NOTES, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,
			   UDV.TLFullName AS CHECKER_NAME_DV,
			   A.APPROVE_DT,
               A.PROCESS_ID,
			   D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
			   ELSE N'Chờ duyệt' END AS AUTH_STATUS_NAME,
			   G.BRANCH_CODE,
			   G.BRANCH_NAME,
			   UP.TLFullName AS CHECKER_NAME_PROCESS,
			   RP.APPROVE_DT AS APPROVE_DT_PROCESS,
			   UC.TLFullName AS MAKER_NAME,
			   RPN.PROCESS_ID AS PROCESS_ID_NEXT,
			   RP.ROLE_USER, 
			   RP.NOTES AS PROCESS_STATUS , 
			   RPN.NOTES AS PROCESS_STATUS_NEXT,
			   A.DVDM_APP_ID,
			   CD.DVDM_NAME AS DVDM_APP_NAME,
			   A.REQ_PARENT_ID,
			   PARENT.REQ_NAME AS REQ_PARENT_NAME,
			   PARENT.REQ_CODE AS REQ_PARENT_CODE,
			   PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
			   A.BRANCH_FEE,
			   A.DEP_ID,
			   A.DEP_FEE,
			   DEP.DEP_NAME,
			   DEP.DEP_CODE,
			   BF.BRANCH_NAME AS BRANCH_FEE_NAME,
			   BF.BRANCH_CODE AS BRANCH_FEE_CODE,
			   DF.DEP_NAME AS DEP_FEE_NAME,
			   DF.DEP_CODE AS DEP_FEE_CODE,
			   '' AS BRANCH_DEP,
			   '' AS BRANCH_DEP_FEE,
			   


			   '' AS TYPE_JOB,
			   '' AS USER_JOB,
			   '' AS USER_JOB_NAME,
			   '' AS TRANSFER_MAKER,
			    A.CREATE_DT AS TRANFER_DT ,
				'' AS TRANSFER_MAKER_ID,
			   A.EFFEC_DT,A.IS_BACKDAY,
			   '' AS TYPE_JOB_XL,
			   '' AS USER_JOB_XL,
			   RP.ID AS REF_ID,
			   RPN.STATUS AS STATUS_NEXT,
			   RP.STATUS AS STATUS_CURR,
			   '' AS STATUS_JOB,
			   A.BRANCH_CREATE,
			   A.DEP_CREATE,
			   A.REQ_LINE,
			   A.TC_NOTES, A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME,
			   A.KT_NOTES,
			     A.IS_CHECKALL,
			   A.BASED_CONTENT, 
			   '' AS IS_TRANSFER,
			   --NGUOI XU LY
			   --dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY
			    NXL.NGUOIXULY AS NGUOIXULY,A.TK_TGD_NOTES, A.OTHER_NOTES,A.TK_HDQT_NOTES
		-- SELECT END
		FROM PL_REQUEST_DOC A 		
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
		LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_ID

		LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
		LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE

		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
	
		LEFT JOIN dbo.PL_REQUEST_PROCESS RPN 
		ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') )
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID IN(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID AND  PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID)
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
		LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_ID
		LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_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.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.REQ_REASON LIKE '%' + @p_REQ_REASON + '%' OR @p_REQ_REASON IS NULL OR @p_REQ_REASON = '')
		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 (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND	(@p_CREATE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_CREATE_DT,103)) = 0) 
		AND	(@p_APPROVE_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_APPROVE_DT,103)) = 0) 
		AND	(@p_REQ_DT IS NULL OR DATEDIFF(DAYOFYEAR, A.CREATE_DT,CONVERT(datetime, @p_REQ_DT,103)) = 0) 	
		AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1)
		AND A.RECORD_STATUS = '1'
		
		AND(
			@p_REQ_TYPE = -1  OR @p_REQ_TYPE IS NULL
			OR
				(
					@p_REQ_TYPE=1 AND EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
				)
				OR
				(
						@p_REQ_TYPE=0 AND NOT EXISTS(SELECT D.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT D WHERE D.REQDT_TYPE='O' AND D.REQ_ID=A.REQ_ID)
				)
			)

		AND (  
		 EXISTS (SELECT * FROM dbo.PL_REQUEST_COSTCENTER PC WHERE PC.REQ_ID=A.REQ_ID AND EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.DVDM_ID=PC.COST_ID) )
		)

		AND A.PROCESS_ID='APPROVE'
		AND(@p_FR_DATE IS NULL OR CONVERT(DATETIME,A.REQ_DT,103) >= CONVERT(DATETIME,@p_FR_DATE,103))
		AND(@p_TO_DATE IS NULL OR CONVERT(DATETIME,A.REQ_DT,103) <= CONVERT(DATETIME,@p_TO_DATE,103))	
		AND(@p_YEAR IS NULL OR @p_YEAR=0 OR YEAR(A.REQ_DT)=@p_YEAR)	
		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  + '%')
		--AND(
		--	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--	OR
		--	(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',1) LIKE '%' + @p_NGUOIXULY  + '%' OR @p_NGUOIXULY  IS NULL OR @p_NGUOIXULY  = '')
		--)


		--AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
		--AND (A.DEP_ID = @p_DEP_ID OR @p_DEP_ID IS NULL OR @p_DEP_ID = '')

		-- GiaNT 20/10/2021
		AND (A.REQ_ID IN( SELECT REQ_DOC_ID FROM PL_REQUEST_TRANSFER) )
		ORDER BY A.CREATE_DT DESC
	-- PAGING END
	END
   END -- PAGING