USE [gAMSPro_BVB_v3]
GO
/****** Object:  StoredProcedure [dbo].[PL_PROCESS_CURRENT_SEARCH]    Script Date: 12/05/2022 16:20:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[PL_PROCESS_CURRENT_SEARCH]
	@p_REQ_ID VARCHAR(20)=NULL,
	@p_USER_LOGIN VARCHAR(20)=NULL,
	@p_TYPE VARCHAR(20)=NULL
AS
BEGIN

DECLARE @PROCESS_CURR VARCHAR(20),@IS_HAS_CHILD BIT

SELECT TOP 1 @PROCESS_CURR = PROCESS_ID,@IS_HAS_CHILD=IS_HAS_CHILD FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C'

IF(@p_TYPE LIKE 'TTCT%')
BEGIN
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
BEGIN
	
	DECLARE @SIGN_USER VARCHAR(15), @CURENT_PROCESS VARCHAR(50)
	SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID)
	SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C')

	-- Nếu có cấp phê duyệt trung gian
	IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN')
	BEGIN
	    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 dbo.PL_REQUEST_PROCESS 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 PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = RD.SIGN_USER)

	END
	ELSE
	BEGIN
		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 dbo.PL_REQUEST_PROCESS 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)
		AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') 
		AND (((SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) = 'HS' AND TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
		OR (SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) <> 'HS') 
		AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
		LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
		WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C'
	END

END

ELSE IF(@IS_HAS_CHILD=1)
BEGIN
	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 dbo.PL_REQUEST_PROCESS 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.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
END
END
ELSE IF(@p_TYPE LIKE 'PYC-XE')
BEGIN
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)

BEGIN
		
	SELECT distinct PL.REQ_ID,PL.PROCESS_ID, BR.BRANCH_NAME  AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES 
	FROM dbo.PL_REQUEST_PROCESS PL
	LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN 
	(
	SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,TU.SECUR_CODE,TU.TLSUBBRID 
	FROM (
		SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
		dbo.TL_USER TS 
		UNION ALL
		SELECT  TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
		dbo.TL_SYS_ROLE_MAPPING TM
		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
		WHERE  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
		) TU
	) TempU ON (TempU.RoleName =PL.ROLE_USER  OR PL.ROLE_USER=(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='') 
	WHERE REQ_ID =@p_REQ_ID AND STATUS='C'
	 
END
ELSE IF(@IS_HAS_CHILD=1)
BEGIN
	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 dbo.PL_REQUEST_PROCESS PL
	LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
	LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN 
	(
	SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC 
	LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
	WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
	)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
	LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
	LEFT JOIN 
	(
	SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID 
	FROM       (
		SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
		dbo.TL_USER TS 
		UNION ALL
		SELECT  TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
		dbo.TL_SYS_ROLE_MAPPING TM
		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
		WHERE  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
		)  TU 
	LEFT JOIN(
	SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 
	LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
	) TempU ON (TempU.RoleName IN (SELECT VALUE FROM WSISPLIT(PL.ROLE_USER,'-'))  OR EXISTS (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_NEW IN (SELECT VALUE FROM WSISPLIT(PL.ROLE_USER,'-')) AND 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='')

	WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
END
END

ELSE IF(@p_TYPE LIKE 'PYC%')
BEGIN
IF(@PROCESS_CURR='DMMS')
BEGIN
	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 dbo.PL_REQUEST_PROCESS PL
	LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
	LEFT JOIN dbo.TR_REQUEST_DOC TR ON TR.REQ_ID=PL.REQ_ID
	LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN 
	(
	SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC 
	LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
	WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
	)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
	LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
	LEFT JOIN 
	(
		SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID 
		FROM
		(
		SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
		dbo.TL_USER TS 
		UNION ALL
		SELECT  TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
		dbo.TL_SYS_ROLE_MAPPING TM
		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
		WHERE  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
		) TU
		LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
		LEFT JOIN(
		SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC
		UNION ALL
		SELECT DVDM.DVDM_ID AS DMMS_ID,DVDM.DEP_ID,DVDM.BRANCH_ID  FROM 
		(
			SELECT CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID FROM dbo.CM_DVDM CD
			LEFT JOIN dbo.PL_COSTCENTER PC ON PC.DVDM_ID=CD.DVDM_ID
			LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID
			WHERE CD.IS_KHOI <>1
			GROUP BY CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID
		) DVDM
		) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID

	) TempU ON (TempU.RoleName=PL.ROLE_USER  OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE TM.ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.DMMS_ID=TR.DMMS_ID) AND TempC.ID IS NULL
	
	WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'	
END
ELSE IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)

BEGIN
	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 dbo.PL_REQUEST_PROCESS PL
	LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
	LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN 
	(
	SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID 
	FROM (
		SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
		dbo.TL_USER TS 
		UNION ALL
		SELECT  TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
		dbo.TL_SYS_ROLE_MAPPING TM
		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
		WHERE  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
		) TU
	LEFT JOIN
		(
		SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 
		LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID
		) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
	) TempU ON (TempU.RoleName=PL.ROLE_USER  OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') 
	AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') 
	AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
	WHERE REQ_ID =@p_REQ_ID AND STATUS='C'
	 
END
ELSE IF(@IS_HAS_CHILD=1)
BEGIN
	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 dbo.PL_REQUEST_PROCESS PL
	LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
	LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN 
	(
	SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC 
	LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
	WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
	)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
	LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
	LEFT JOIN 
	(
	SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID 
	FROM       (
		SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
		dbo.TL_USER TS 
		UNION ALL
		SELECT  TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
		dbo.TL_SYS_ROLE_MAPPING TM
		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
		WHERE  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
		)  TU 
	LEFT JOIN(
	SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 
	LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
	) TempU ON  (TempU.RoleName=PL.ROLE_USER  OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') 
	AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') 
	AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
	WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'		
END
END

END
--EXEC CM_GOODS_Search '','','','','',0,'','','','','','','','',100