ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_Search]
@p_REQ_ID	varchar(15)  = NULL,
@p_REQ_CODE	nvarchar(100)  = NULL,
@p_REQ_NAME	nvarchar(200)  = NULL,
@p_REQ_DT	DATETIME = 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	DATETIME = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	DATETIME = 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 DATETIME = NULL,
@p_TO_DATE DATETIME = 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

AS
BEGIN

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

	DECLARE @AUTHOR TABLE
	(
		ROLE_ID VARCHAR(20),
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20)
	)
	DECLARE @AUTHOR_DVDM TABLE
	(
		ROLE_ID VARCHAR(20),
		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
	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
	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
	IF(@p_TYPE='DVKD')
	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,
			   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,
			   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
			   dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY
			   
		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


		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 (DATEDIFF(DAY,A.CREATE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 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 (DATEDIFF(DAY,A.APPROVE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')	
		AND (DATEDIFF(DAY,A.REQ_DT , CAST(@p_REQ_DT AS DATE)) = 0 OR @p_REQ_DT IS NULL OR @p_REQ_DT = '')		
		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 WHERE ROLE_TYPE='PL_REQUEST_DOC_ALL' AND BRANCH_ID=@p_TLNAME_USER )
				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_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(@p_YEAR IS NULL OR @p_YEAR=0 OR YEAR(A.REQ_DT)=@p_YEAR)	
		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 = '')
		
		ORDER BY A.CREATE_DT DESC
	END
	ELSE IF(@p_TYPE='PDTT')
	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

		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
		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 (DATEDIFF(DAY,A.CREATE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 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 (DATEDIFF(DAY,A.APPROVE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')	
		AND (DATEDIFF(DAY,A.REQ_DT , CAST(@p_REQ_DT AS DATE)) = 0 OR @p_REQ_DT IS NULL OR @p_REQ_DT = '')	
		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 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(
			(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 = '')

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

		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
		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 (DATEDIFF(DAY,A.CREATE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 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 (DATEDIFF(DAY,A.APPROVE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')	
		AND (DATEDIFF(DAY,A.REQ_DT , CAST(@p_REQ_DT AS DATE)) = 0 OR @p_REQ_DT IS NULL OR @p_REQ_DT = '')	
		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 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(@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(
			(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 = '')


	ORDER BY A.CREATE_DT DESC
	END

	ELSE IF(@p_TYPE='XLTT')
	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,
			   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

		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
		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 (DATEDIFF(DAY,A.CREATE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 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 (DATEDIFF(DAY,A.APPROVE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')	
		AND (DATEDIFF(DAY,A.REQ_DT , CAST(@p_REQ_DT AS DATE)) = 0 OR @p_REQ_DT IS NULL OR @p_REQ_DT = '')	
		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 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(@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(
			(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 = '')


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

		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
		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 (DATEDIFF(DAY,A.CREATE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 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 (DATEDIFF(DAY,A.APPROVE_DT , CAST(@p_CREATE_DT AS DATE)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')	
		AND (DATEDIFF(DAY,A.REQ_DT , CAST(@p_REQ_DT AS DATE)) = 0 OR @p_REQ_DT IS NULL OR @p_REQ_DT = '')		
		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 (
		 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) )
		) OR A.IS_CHECKALL=1)

		AND A.PROCESS_ID='APPROVE'
		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(@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(
			(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 = '')


		ORDER BY A.CREATE_DT DESC
 
	END
   END