ALTER FUNCTION [dbo].[PL_PROCESS_CURRENT_NGUOIXULY](
	@p_REQ_ID VARCHAR(20)=NULL,
	@p_USER_LOGIN VARCHAR(20)=NULL,
	@p_TYPE VARCHAR(20)=NULL,
	@p_IS_RETURN_NAME BIT = 1)
RETURNS NVARCHAR(4000)
BEGIN

DECLARE @PROCESS_CURR VARCHAR(20),@IS_HAS_CHILD BIT, @NGUOIXULY NVARCHAR(4000), @NGUOIXULYTLNAME NVARCHAR(4000)

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
	select @NGUOIXULY = STUFF((select ', ' + RE.TLFullName  
		FROM(
		SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.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 
		(
		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
		) 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 ='')
		WHERE REQ_ID =@p_REQ_ID AND STATUS='C' ) RE
			FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''),

		@NGUOIXULYTLNAME = STUFF((select ', ' + RE.TLNAME  
		FROM(
		SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.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 (
		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
		) 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 ='')
		WHERE REQ_ID =@p_REQ_ID AND STATUS='C' ) RE
			FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

END
ELSE IF(@IS_HAS_CHILD=1)
BEGIN
	select @NGUOIXULY = STUFF((select ', ' + RE.TLFullName  
	FROM(
	SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.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 
	(
	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
		) 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 ='')
	WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C') RE
	FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''),

	@NGUOIXULYTLNAME = STUFF((select ', ' + RE.TLNAME  
	FROM(
	SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.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 
	(
	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
		) 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 ='')
	WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C') RE
	FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

END


END
ELSE IF(@p_TYPE LIKE 'PYC%')
BEGIN
IF(@PROCESS_CURR='DMMS')
BEGIN
	--SELECT @NGUOIXULY = STRING_AGG(RE.TLFullName,', '), @NGUOIXULYTLNAME = STRING_AGG(RE.TLNAME,', ')
	select @NGUOIXULY = STUFF((select ', ' + RE.TLFullName  
	FROM(
		SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.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 
		(
		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
		) 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 
		) 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') RE
	FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''),

	@NGUOIXULYTLNAME = STUFF((select ', ' + RE.TLNAME  
	FROM(
		SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.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 
		(
		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
		) 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 
		) 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') RE
	FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')


END
ELSE IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)

BEGIN
	--SELECT @NGUOIXULY = STRING_AGG(RE.TLFullName,', '), @NGUOIXULYTLNAME = STRING_AGG(RE.TLNAME,', ')
	select @NGUOIXULY = STUFF((select ', ' + RE.TLFullName
	FROM(
		SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.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 (
		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
		) 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 ='')
		WHERE REQ_ID =@p_REQ_ID AND STATUS='C') RE
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''),

	@NGUOIXULYTLNAME = STUFF((select ', ' + RE.TLNAME
	FROM(
		SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.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 (
		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
		) 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 ='')
		WHERE REQ_ID =@p_REQ_ID AND STATUS='C') RE
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
	 
END
ELSE IF(@IS_HAS_CHILD=1)
BEGIN
	--SELECT @NGUOIXULY = STRING_AGG(RE.TLFullName,', '), @NGUOIXULYTLNAME = STRING_AGG(RE.TLNAME,', ')
	select @NGUOIXULY = STUFF((select ', ' + RE.TLFullName
	FROM(
		SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.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 
		(
		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
		) 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 ='')
		WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C') RE
	FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''),

	@NGUOIXULYTLNAME = STUFF((select ', ' + RE.TLNAME
	FROM(
		SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.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 
		(
		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
		) 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 ='')
		WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C') RE
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')

END
END
	IF(@p_IS_RETURN_NAME = 1)
	RETURN @NGUOIXULYTLNAME

	RETURN @NGUOIXULY
END
--EXEC CM_GOODS_Search '','','','','',0,'','','','','','','','',100s