EOFFICE - GD4

alter PROCEDURE [dbo].[PL_PROCESS_CURRENT_SEARCH]

	@p_REQ_ID VARCHAR(20)=NULL,

	@p_USER_LOGIN VARCHAR(20)=NULL,

	@p_TYPE VARCHAR(20)=NULL,

	@MENU_PERMISSON NVARCHAR(500),

	@PROCESS_ID VARCHAR(10) = NULL

AS

BEGIN

DECLARE @PROCESS_CURR VARCHAR(20),@IS_HAS_CHILD BIT, @AUTH_STATUS VARCHAR(10),@BRANCH_NAME_HS NVARCHAR(250)

SELECT TOP 1 @BRANCH_NAME_HS = BRANCH_NAME FROM dbo.CM_BRANCH WHERE BRANCH_ID = 'DV0001'

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'

DECLARE @SIGN_USER VARCHAR(15), @CURENT_PROCESS VARCHAR(50)

IF(@p_TYPE LIKE 'TTCT%')

BEGIN

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

BEGIN

	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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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)

		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

		LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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.RoleDisplayName RoleName, Temp.DVDM_ID, TU.DEP_ID SECUR_CODE, TU.BRANCH_ID TLSUBBRID

		FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER 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 (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')

		AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')

		AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

		AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'

		---- LUCTV 20062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

		UNION ALL

		SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU

		INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

		INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

		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 RL.DisplayName <>'DISABLE'

		--- END LUCTV 19062023

		) 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

		LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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 dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_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 TempC.REQ_ID = PL.REQ_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.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER 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 (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')

		AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')

		AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

		AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'

		---- LUCTV 24102023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

		UNION ALL

		SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU

		INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

		INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

		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 RL.DisplayName <>'DISABLE'

		--- END LUCTV 19062023

	) TempU ON (TempU.RoleName=PL.ROLE_USER) 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

	DECLARE @Role VARCHAR(50)

  DECLARE @BR VARCHAR(50)

	SELECT TOP 1 @Role = ROLE_USER, @BR = BRANCH_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C'

	SET @Role= REPLACE(@Role,'-',',')

  SET @BR= REPLACE(@BR,'-',',')

	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 dbo.FN_GET_USER_BY_ROLE('','','') TempU ON (TempU.ROLE_OLD IN ( SELECT value from wsiSplit(@Role,',')) OR TempU.ROLE_NEW IN (SELECT value from wsiSplit(@Role,',')) OR TempU.TLNANME IN (SELECT value from wsiSplit(@Role,','))) AND (TempU.BRANCH_ID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.DEP_ID=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')

	LEFT JOIN dbo.FN_GET_USER_BY_ROLE('', @BR, '', @MENU_PERMISSON) TempU ON (TempU.ROLE_OLD IN ( SELECT value from wsiSplit(@Role,',')) OR TempU.ROLE_NEW IN (SELECT value from wsiSplit(@Role,',')) OR TempU.TLNANME IN (SELECT value from wsiSplit(@Role,','))) AND (TempU.BRANCH_ID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.DEP_ID=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')-- NGUYENTD 10102023_SECRETKEY: FIX LỖI NGƯỜI XỬ LÝ TIẾP THEO KHÔNG LẤY ĐƯỢC USER CÓ ROLE GDDV

	WHERE REQ_ID =@p_REQ_ID AND STATUS='C'

--  UNION

--  SELECT distinct PL.REQ_ID,PL.PROCESS_ID, BR.BRANCH_NAME  AS DVDM_NAME,tmp.CDVAL AS TLNAME,tmp.CONTENT,PL.NOTES

--	FROM dbo.PL_REQUEST_PROCESS PL

--	LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID

--  LEFT JOIN (SELECT ca.CDVAL,ca.CONTENT,tu.TLSUBBRID FROM CM_ALLCODE ca LEFT JOIN TL_USER tu ON ca.CDVAL= tu.TLNANME WHERE ca.CDTYPE ='TR' AND ca.CDNAME='REQCAR') tmp ON (PL.ROLE_USER='CVDDX' AND pl.BRANCH_ID=tmp.TLSUBBRID)

--  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, BR.BRANCH_NAME AS DVDM_NAME,TempC.TLNAME AS TLNANME , TU.TLFullName 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 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='CAR_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

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

END

END

ELSE IF(@p_TYPE LIKE 'PYC%' AND @p_TYPE NOT LIKE 'PYC-KVL' AND @p_TYPE NOT LIKE 'PYCQLX')

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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME 

		WHEN CD1.DMMS_ID IS NOT NULL AND CD1.DMMS_ID <> '' THEN IIF(BR1.BRANCH_TYPE = 'HS',ISNULL(BR1.BRANCH_NAME,'') + ' - ' + ISNULL(DEP1.DEP_NAME,''),BR.BRANCH_NAME )

		ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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.CM_DMMS CD1 ON CD1.DMMS_ID = PL.DVDM_ID

	LEFT JOIN dbo.CM_BRANCH BR1 ON CD1.BRANCH_ID = BR1.BRANCH_ID

	LEFT JOIN dbo.CM_DEPARTMENT DEP1 ON CD1.DEP_ID = DEP1.DEP_ID

	LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_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.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM

		dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM

		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME

		WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')

		AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')

		AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

		AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'

		---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

		UNION ALL

		SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU

		INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

		INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

		WHERE RL.DisplayName <>'DISABLE'

		--- END LUCTV 19062023

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

	SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.TR_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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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 dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_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  

		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.TR_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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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 dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_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.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM

			dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM

			LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME

			WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')

			AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')

			AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

			AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'

			---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

			UNION ALL

			SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU

			INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

			INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

			WHERE RL.DisplayName <>'DISABLE'

		--- END LUCTV 19062023

			) 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

    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

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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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 dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_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.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM

		dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM

		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME

		WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')

		AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')

		AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

		AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'

		---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

		UNION ALL

		SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU

		INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

		INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

		WHERE RL.DisplayName <>'DISABLE'

		--- END LUCTV 19062023

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

BEGIN

	SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.ASS_INVENTORY_MASTER WHERE INVENT_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, BR.BRANCH_NAME AS DVDM_NAME,TU.TLNANME AS TLNAME,TU.TLFullName,PL.NOTES

		FROM dbo.PL_REQUEST_PROCESS PL

		LEFT JOIN dbo.ASS_INVENTORY_MASTER AI ON AI.INVENT_ID = PL.REQ_ID

		LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID

		LEFT JOIN dbo.TL_USER TU ON TU.TLNANME = AI.SIGN_USER

		WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND PL.PROCESS_ID = 'SIGN'

	END

	ELSE IF(@CURENT_PROCESS = 'APPNEW')

	BEGIN

		SELECT PL.REQ_ID, Pl.PROCESS_ID,BR.BRANCH_NAME AS DVDM_NAME, TmpU.TLNANME, TmpU.TLFullName, PL.NOTES

		FROM dbo.PL_REQUEST_PROCESS PL

		LEFT JOIN (

			SELECT * FROM [dbo].[FN_GET_USER_BY_ROLE] ('', '', '', @MENU_PERMISSON)

		) TmpU ON 1=1

		LEFT JOIN dbo.CM_BRANCH BR ON BR.BRANCH_ID = PL.BRANCH_ID

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

		AND ((TmpU.ROLE_OLD = PL.ROLE_USER OR TmpU.ROLE_NEW = PL.ROLE_USER) OR PL.ROLE_USER IS NULL OR PL.ROLE_USER = '')

		AND ((BR.BRANCH_TYPE = 'HS' AND TmpU.BRANCH_ID = PL.BRANCH_ID AND TmpU.DEP_ID = PL.DEP_ID)

			OR (BR.BRANCH_TYPE <> 'HS' AND TmpU.BRANCH_ID = PL.BRANCH_ID)

			OR (PL.BRANCH_ID IS NULL OR PL.BRANCH_ID = '')

		)

	END

	ELSE IF(@CURENT_PROCESS <> 'APPNEW')

	BEGIN

		 SELECT PL.REQ_ID, Pl.PROCESS_ID,BR.BRANCH_NAME AS DVDM_NAME, TU.TLNANME, TU.TLFullName, PL.NOTES

		 FROM dbo.PL_REQUEST_PROCESS PL

		 LEFT JOIN dbo.TL_USER TU ON TU.TLNANME = PL.ROLE_USER

		 LEFT JOIN dbo.CM_BRANCH BR ON BR.BRANCH_ID = TU.TLSUBBRID

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

	END

END

----------------BAODNQ 6/6/2022 TỜ TRÌNH CHỈ ĐỊNH THẦU--------------

ELSE IF (@p_TYPE = 'TT_CDT')

BEGIN

	SET @SIGN_USER = (SELECT SIGN_USER FROM PL_APPOINT_CONTRACTOR 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'

	)

	-------------CÓ CẤP DUYỆT TRUNG GIAN-----------

	IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN')

	BEGIN

		SELECT DISTINCT

		PRP.REQ_ID,

		PRP.PROCESS_ID,

		CASE

			WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME

			ELSE CB.BRANCH_NAME

		END AS DVDM_NAME,

		TLUSER.TLNANME AS TLNAME, TLUSER.TLFullName, PRP.NOTES

		FROM PL_REQUEST_PROCESS PRP

		LEFT JOIN CM_DVDM CD ON PRP.DVDM_ID = CD.DVDM_ID

		LEFT JOIN(

			SELECT

			TU.TLNANME, TU.TLFullName, TU.RoleName, PCOST.DVDM_ID, TU.SECUR_CODE,

			TU.DEP_ID, TU.TLSUBBRID

			FROM TL_USER TU

			LEFT JOIN (

				SELECT PC.DVDM_ID, PD.BRANCH_ID, PD.DEP_ID

				FROM PL_COSTCENTER PC

				LEFT JOIN PL_COSTCENTER_DT PD ON PD.COST_ID = PC.COST_ID

			) PCOST ON TU.TLSUBBRID = PCOST.BRANCH_ID AND TU.DEP_ID = PCOST.DEP_ID

		) TLUSER ON TLUSER.RoleName = PRP.ROLE_USER

		AND (TLUSER.TLSUBBRID = PRP.BRANCH_ID OR PRP.BRANCH_ID IS NULL OR PRP.BRANCH_ID = '')

		AND (TLUSER.DEP_ID = PRP.DEP_ID OR PRP.DEP_ID IS NULL OR PRP.DEP_ID = '')

		AND (TLUSER.DVDM_ID = PRP.DVDM_ID OR PRP.DVDM_ID IS NULL OR PRP.DVDM_ID = '')

		LEFT JOIN PL_APPOINT_CONTRACTOR PAC ON PRP.REQ_ID = PAC.REQ_ID

		LEFT JOIN CM_BRANCH CB ON PRP.BRANCH_ID = CB.BRANCH_ID

		WHERE PRP.REQ_ID = @p_REQ_ID

		AND STATUS = 'C'

		AND (PRP.PROCESS_ID = 'SIGN' AND TLUSER.TLNANME = PAC.SIGN_USER)

	END

	ELSE

	----------KO CÓ CẤP DUYỆT TRUNG GIAN-------------

	BEGIN

		SELECT DISTINCT

		PRP.REQ_ID,

		PRP.PROCESS_ID,

		CASE

			WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME

			ELSE CB.BRANCH_NAME

		END AS DVDM_NAME,

		TLUSER.TLNANME AS TLNAME, TLUSER.TLFullName, PRP.NOTES

		FROM PL_REQUEST_PROCESS PRP

		LEFT JOIN CM_DVDM CD ON CD.DVDM_ID = PRP.DVDM_ID

		LEFT JOIN 

		(

			SELECT TU.TLNANME, TU.TLFullName, TU.RoleName, PCOST.DVDM_ID, TU.SECUR_CODE,

			TU.DEP_ID, TU.TLSUBBRID

			FROM TL_USER TU

			LEFT JOIN (

				SELECT PC.DVDM_ID, PD.BRANCH_ID, PD.DEP_ID

				FROM PL_COSTCENTER PC

				LEFT JOIN PL_COSTCENTER_DT PD ON PD.COST_ID = PC.COST_ID

			) PCOST ON TU.TLSUBBRID = PCOST.BRANCH_ID AND TU.DEP_ID = PCOST.DEP_ID

			UNION ALL

			SELECT TM.TLNAME, TLU.TLFullName, TM.RoleDisplayName AS RoleName, PCOST.DVDM_ID, 

			TM.DEP_ID AS SECUR_CODE, TM.DEP_ID AS DEP_ID, TM.BRANCH_ID AS TLSUBBRID

			FROM SYS_PERMISSIONS_PAGE_FOR_USER TM

			LEFT JOIN TL_USER TLU ON TM.TLNAME = TLU.TLNANME

			LEFT JOIN (

				SELECT PC.DVDM_ID, PD.BRANCH_ID, PD.DEP_ID

				FROM PL_COSTCENTER PC

				LEFT JOIN PL_COSTCENTER_DT PD ON PD.COST_ID = PC.COST_ID

			) PCOST ON TM.BRANCH_ID = PCOST.BRANCH_ID AND TM.DEP_ID = PCOST.DEP_ID

			WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')

			AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')

			AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

			AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'

			---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

			UNION ALL

			SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE, TU.DEP_ID,TU.TLSUBBRID FROM TL_USER TU

			INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

			INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

			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 RL.DisplayName <>'DISABLE'

			--- END LUCTV 19062023

		) TLUSER ON (TLUSER.RoleName = PRP.ROLE_USER)

		AND (TLUSER.TLSUBBRID = PRP.BRANCH_ID OR PRP.BRANCH_ID IS NULL OR PRP.BRANCH_ID = '')

		AND (

			((SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE PRP.BRANCH_ID = BRANCH_ID) = 'HS'

			AND TLUSER.SECUR_CODE = PRP.DEP_ID OR PRP.DEP_ID IS NULL OR PRP.DEP_ID = '')

			OR (SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE PRP.BRANCH_ID = BRANCH_ID) <> 'HS'

		)

		AND (TLUSER.DVDM_ID = PRP.DVDM_ID OR PRP.DVDM_ID IS NULL OR PRP.DVDM_ID = '')

		LEFT JOIN CM_BRANCH CB ON PRP.BRANCH_ID = CB.BRANCH_ID

		WHERE PRP.REQ_ID = @p_REQ_ID

		AND STATUS = 'C'

	END

END

-----------------------BAODNQ 13/7/2022 ĐÁNH GIÁ NCC---------------------------

ELSE IF (@p_TYPE = 'RATE_SUP')

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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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 TR_RATE_SUPPLIER_MASTER TR ON TR.RATE_ID=PL.REQ_ID

        LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID

		    LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID

        LEFT JOIN 

        (

        ------------BAODNQ 11/10/2022 : Ở bước điều phối lần đầu tiên cả GDDV và KSV cùng thấy phiếu--------------

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

        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' 

            OR (PRC.STATUS_JOB = 'U' AND EXISTS(

                        SELECT PC.ID FROM PL_REQUEST_PROCESS_CHILD PC

                        WHERE PC.REQ_ID = PRC.REQ_ID AND PC.TYPE_JOB = 'KS' 

                        AND PC.MAKER_ID IS NULL AND PC.TRANFER_DT IS NULL 

                    )

                    AND NOT EXISTS(SELECT PC_2.ID FROM PL_REQUEST_PROCESS_CHILD PC_2 WHERE PC_2.REQ_ID = PRC.REQ_ID AND PC_2.TYPE_JOB = 'XL')

            )

        )

        --------------------END BAODNQ 11/10/2022----------------

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

            AR.DisplayName AS RoleName,

            TS.TLSUBBRID,TS.SECUR_CODE FROM

            dbo.TL_USER TS

            JOIN AbpUserRoles AU ON TS.ID = AU.UserId

            JOIN AbpRoles AR ON AU.RoleId = AR.Id 

            UNION ALL

            SELECT  TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM

            dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM

            LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME

            WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')

			AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')

			AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

			AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'

            ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

            UNION ALL

            SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU

            INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

            INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

            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 RL.DisplayName <>'DISABLE'

            --- END LUCTV 19062023

            ) 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 AND TempU.DMMS_ID = TR.DMMS_ID AND TempC.ID IS NULL

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

    END

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

	BEGIN

		SET @SIGN_USER = (SELECT SIGN_USER FROM TR_RATE_SUPPLIER_MASTER WHERE RATE_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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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

			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.TR_RATE_SUPPLIER_MASTER TRS ON TRS.RATE_ID = PL.REQ_ID

			LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID

		  LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID

			WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = TRS.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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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,

				AR.DisplayName AS RoleName,

				Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID 

				FROM dbo.TL_USER TU

				JOIN AbpUserRoles AU ON TU.ID = AU.UserId

				JOIN AbpRoles AR ON AU.RoleId = AR.Id 

				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.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID

				FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER 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 (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')

				AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')

				AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

				AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'

				---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

				UNION ALL

				SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU

				INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

				INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

				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 RL.DisplayName <>'DISABLE'

			--- END LUCTV 19062023

				) 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

		    LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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 dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_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.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM

			dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM

			LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME

			WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')

			AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')

			AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

			AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'

			---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

			UNION ALL

			SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID, TU.SECUR_CODE FROM TL_USER TU

			INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

			INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

			WHERE RL.DisplayName <>'DISABLE'

			--- END LUCTV 19062023

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

-----------HIEUHM 15/11/2022 LAYOUT BẢN VẼ---

ELSE IF(@p_TYPE LIKE 'LAYOUT_BV')

BEGIN

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

    BEGIN

        SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.CON_LAYOUT_BLUEPRINT WHERE CON_LAYOUT_BLUEPRINT_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 

            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.CON_LAYOUT_BLUEPRINT RD ON RD.CON_LAYOUT_BLUEPRINT_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.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID

            FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER 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 (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')

			AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')

			AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

			AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'

            ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

            UNION ALL

            SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU

            INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

            INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

            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 RL.DisplayName <>'DISABLE'

            --- END LUCTV 19062023

            ) 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 TempC.REQ_ID = PL.REQ_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.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID 

			FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER 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 (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')

			AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')

			AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

			AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'

            ---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

            UNION ALL

            SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU

            INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

            INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

            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 RL.DisplayName <>'DISABLE'

            --- END LUCTV 19062023

        ) TempU ON TempU.RoleName = PL.ROLE_USER 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

-----------HIEUHM 15/11/2022 LAYOUT BẢN VẼ---

--NGUYENTD 20/07/2024 PHIẾU YÊU CẦU QUẢN LÝ XE

ELSE IF(@p_TYPE LIKE 'PYCQLX')

BEGIN

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

BEGIN

	SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.TR_REQUEST_CAR_COST WHERE REQ_COST_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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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 

		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.TR_REQUEST_CAR_COST RD ON RD.REQ_COST_ID = PL.REQ_ID

		LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID

		LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID = PL.DEP_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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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 dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID = PL.DEP_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.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM

			dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM

			LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME

			WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')

			AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')

			AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

			AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'

			---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

			UNION ALL

			SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU

			INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

			INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

			WHERE RL.DisplayName <>'DISABLE'

		--- END LUCTV 19062023

			) 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

		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

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 ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, 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 dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID = PL.DEP_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.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM

		dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM

		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME

		WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')

		AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')

		AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

		AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'

		---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

		UNION ALL

		SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU

		INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

		INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

		WHERE RL.DisplayName <>'DISABLE'

		--- END LUCTV 19062023

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

--NGUYENTD 20/07/2024 PHIẾU YÊU CẦU QUẢN LÝ XE

-- PHIẾU YÊU CẦU KHO VẬT LIỆU --

ELSE IF (@p_TYPE = 'PYC-KVL')

BEGIN

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

    BEGIN

    	SET @SIGN_USER = (SELECT USER_APPROVAL FROM dbo.MW_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')

		DECLARE @ID_KSV_PROCESS VARCHAR(20) = (SELECT TOP 1 ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = @p_REQ_ID AND TYPE_JOB='KS')

		DECLARE @ID_KSV_CHILD VARCHAR(20) = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID=@ID_KSV_PROCESS)

    	-- Nếu có cấp phê duyệt trung gian

    	IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'MED_APP')

    	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 ISNULL(BR.BRANCH_NAME + ' - ' + CD1.DEP_NAME, 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 IS NULL OR PL.ROLE_USER = '')

    		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 CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID

    		LEFT JOIN CM_DEPARTMENT CD1 ON PL.DEP_ID = CD1.DEP_ID

    		WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'MED_APP' AND TempU.TLNANME = @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.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID

    		FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER 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 (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')

			AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')

			AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

			AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'

    		---- LUCTV 20062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

    		UNION ALL

    		SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU

    		INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

    		INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

    		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 RL.DisplayName <>'DISABLE'

    		--- END LUCTV 19062023

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

        CASE WHEN TempC.LEVEL_JOB = 1 OR (TempC.TYPE_JOB = 'KS' AND EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = PL.REQ_ID AND MAKER_ID=TempC.TLNAME AND TYPE_JOB='XL' AND STATUS_JOB='P'))

			 THEN 'Approve' ELSE NULL END AS STATUS 

        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, PRC.LEVEL_JOB, PRC.TYPE_JOB 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 TempC.REQ_ID = PL.REQ_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.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER 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 (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')

			AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')

			AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

			AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'

    		---- LUCTV 24102023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

    		UNION ALL

    		SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU

    		INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

    		INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

    		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 RL.DisplayName <>'DISABLE'

    		--- END LUCTV 19062023

    	) TempU ON (TempU.RoleName=PL.ROLE_USER) 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 PHIẾU YÊU CẦU KHO VẬT LIỆU --

-- TỜ TRÌNH THANH LÝ TÀI SẢN --

ELSE IF (@p_TYPE LIKE 'TTTL')

BEGIN

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

    BEGIN

    	SET @SIGN_USER = (SELECT USER_APPROVAL FROM dbo.ASS_PLAN_REQUEST_LIQUID_MASTER 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')

		IF(@CURENT_PROCESS IN ('NEW', 'HC_APP'))--ĐANG Ở BƯỚC ĐƠN VỊ TẠO VÀ GDDV DUYỆT

		BEGIN

		  SET @MENU_PERMISSON = 'Pages.Administration.AssReqDocLiquidation'

		END

		ELSE IF(@CURENT_PROCESS IN ('DVCM_DP'))--ĐANG Ở BƯỚC DVCM ĐIỀU PHỐI

		BEGIN

		  SET @MENU_PERMISSON = 'Pages.Administration.PlanTransfer'

		END

		ELSE IF(@CURENT_PROCESS IN ('GDK_APP', 'TKTGD_APP', 'TGD_APP', 'TKHDQT_APP', 'TKHDQT_APP'))--ĐANG ĐẾN BƯỚC PHÊ DUYỆT TỜ TRÌNH

		BEGIN

		  SET @MENU_PERMISSON = 'Pages.Administration.AssReqDocLiqApprove'

		END

    	-- Nếu có cấp phê duyệt trung gian

    	IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'MED_APP')

    	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 ISNULL(BR.BRANCH_NAME + ' - ' + CD1.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES, PL.ROLE_USER 

            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 IS NULL OR PL.ROLE_USER = '')

    		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 CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID

    		LEFT JOIN CM_DEPARTMENT CD1 ON PL.DEP_ID = CD1.DEP_ID

    		WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'MED_APP' AND TempU.TLNANME = @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, PL.ROLE_USER

    		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.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID

    		FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER 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 (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')

			AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')

			AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

			AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'

    		---- LUCTV 20062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

    		UNION ALL

    		SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU

    		INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

    		INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

    		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 RL.DisplayName <>'DISABLE'

    		--- END LUCTV 19062023

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

			AND (PL.ID = @PROCESS_ID OR @PROCESS_ID = '' OR @PROCESS_ID IS NULL)

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

        CASE WHEN TempC.LEVEL_JOB = 1  THEN 'Approve'

			 WHEN (TempC.TYPE_JOB = 'KS' AND EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = PL.REQ_ID AND MAKER_ID=TempC.TLNAME AND TYPE_JOB='XL' AND STATUS_JOB='P')) THEN 'Confirm' 

			 ELSE NULL END AS STATUS

        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, PRC.LEVEL_JOB, PRC.TYPE_JOB 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 TempC.REQ_ID = PL.REQ_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.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER 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 (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')

			AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')

			AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)

			AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'

    		---- LUCTV 24102023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES

    		UNION ALL

    		SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU

    		INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId

    		INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id

    		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 RL.DisplayName <>'DISABLE'

    		--- END LUCTV 19062023

    	) TempU ON (TempU.RoleName=PL.ROLE_USER) 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 TỜ TRÌNH THANH LÝ TS --

-- BEGIN PHIẾU YÊU CẦU DỊCH VỤ VĂN PHÒNG HO --

ELSE IF (@P_TYPE = 'SHO')

BEGIN 

    SELECT @CURENT_PROCESS=PROCESS_ID, @AUTH_STATUS=AUTH_STATUS

    FROM SHO_REQ_SERVICE 

    WHERE REQ_ID=@p_REQ_ID 

    -- LƯU NHÁP

    IF(@CURENT_PROCESS IS NULL AND @AUTH_STATUS='E')

    BEGIN

        SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME

        FROM SHO_REQ_SERVICE

        WHERE REQ_ID=@p_REQ_ID

    END

    -- TỪ CHỐI

    ELSE IF(@AUTH_STATUS='R')

    BEGIN

        SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME

        FROM SHO_REQ_SERVICE A

        WHERE A.REQ_ID=@p_REQ_ID AND A.AUTH_STATUS='R'

    END

    -- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT

    ELSE IF(@CURENT_PROCESS='SIGN')

    BEGIN

        SELECT A.PROCESS_ID, CHECKER_ID AS TLNAME

        FROM SHO_REQUEST_PROCESS A

        WHERE A.REQ_ID=@p_REQ_ID AND A.[STATUS]='C' AND A.PROCESS_ID=@CURENT_PROCESS AND A.CHECKER_ID=@p_USER_LOGIN

    END

    -- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT

    ELSE IF(@CURENT_PROCESS='CONFIRM')

    BEGIN

        SELECT 'CONFIRM' AS PROCESS_ID, ROLE_USER, A.CHECKER_ID AS TLNAME

        FROM SHO_REQUEST_PROCESS A 

        WHERE A.REQ_ID=@p_REQ_ID 

            AND A.PROCESS_ID='APPNEW'

            AND A.CHECKER_ID IS NOT NULL 

            AND A.CHECKER_ID=@p_USER_LOGIN

    END

    -- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT

    ELSE IF(@CURENT_PROCESS='APPNEW')

    BEGIN

        -- TRƯỞNG ĐƠN VỊ MẶC ĐỊNH

        SELECT A.PROCESS_ID, ROLE_USER, U.TLNANME AS TLNAME

        FROM SHO_REQUEST_PROCESS A

            INNER JOIN AbpRoles R ON A.ROLE_USER=R.DisplayName

            INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId

            INNER JOIN TL_USER U ON UR.UserId=U.ID AND A.DEP_ID=U.SECUR_CODE AND A.BRANCH_ID=U.TLSUBBRID

        WHERE A.REQ_ID=@p_REQ_ID 

            AND A.[STATUS]='C' 

            AND A.CHECKER_ID IS NULL

            AND A.PROCESS_ID=@CURENT_PROCESS

            AND U.TLNANME=@p_USER_LOGIN

        -- ỦY QUYỀN TRƯỞNG ĐƠN VỊ

        UNION

        SELECT A.PROCESS_ID, ROLE_USER, U.TLNANME AS TLNAME

        FROM SHO_REQUEST_PROCESS A

            INNER JOIN FN_GET_USER_BY_ROLE(NULL,NULL,NULL, @MENU_PERMISSON) U ON A.ROLE_USER=U.ROLE_NEW AND A.DEP_ID=U.DEP_ID AND A.BRANCH_ID=U.BRANCH_ID

        WHERE A.REQ_ID=@p_REQ_ID 

            AND A.[STATUS]='C' 

            AND A.CHECKER_ID IS NULL

            AND A.PROCESS_ID=@CURENT_PROCESS

            AND U.TLNANME=@p_USER_LOGIN

    END

    ELSE IF(@CURENT_PROCESS='DVCM')

    BEGIN

        -- TRƯỞNG ĐƠN VỊ CHUYÊN MÔN PHÊ DUYỆT

        SELECT A.DVDM_ID, A.BRANCH_ID, A.DEP_ID, 'TRUONG_DVCM' AS PROCESS_ID, ROLE_USER, A.CHECKER_ID AS TLNAME, NULL AS NOTES

        FROM SHO_REQ_SERVICE_DVCM D 

            INNER JOIN SHO_REQUEST_PROCESS A ON D.REQ_ID=A.REQ_ID AND D.BRANCH_ID=A.BRANCH_ID AND D.DVCM_ID=A.DEP_ID 

        WHERE A.REQ_ID=@p_REQ_ID 

            AND A.[STATUS]='C'  

            AND A.CHECKER_ID=@p_USER_LOGIN

            AND A.PROCESS_ID=@CURENT_PROCESS

            AND D.STEP_APPROVE=1

        UNION 

        -- KIỂM SOÁT VIÊN ĐƠN VỊ CHUYÊN MÔN PHÊ DUYỆT

        SELECT A.DVDM_ID, A.BRANCH_ID, A.DEP_ID, 'KSV_DVCM' AS PROCESS_ID, ROLE_USER, C.TLNAME AS TLNAME, NULL AS NOTES

        FROM SHO_REQ_SERVICE_DVCM D 

            INNER JOIN SHO_REQUEST_PROCESS A ON D.REQ_ID=A.REQ_ID AND D.BRANCH_ID=A.BRANCH_ID AND D.DVCM_ID=A.DEP_ID

            INNER JOIN SHO_REQUEST_PROCESS_CHILD C ON D.REQ_ID=C.REQ_ID AND A.ID=C.PROCESS_ID

        WHERE D.REQ_ID=@p_REQ_ID

            AND A.PROCESS_ID=@CURENT_PROCESS

            AND C.TLNAME=@p_USER_LOGIN

            AND A.CHECKER_ID IS NOT NULL

            AND D.STEP_APPROVE IS NOT NULL

            AND D.STEP_APPROVE=C.LEVEL_JOB+1

        UNION

        -- NHÂN VIÊN XỬ LÝ ĐƠN VỊ CHUYÊN MÔN

        SELECT A.DVDM_ID, A.BRANCH_ID, A.DEP_ID, 'NVXL_DVCM' AS PROCESS_ID, C.TYPE_JOB AS ROLE_USER, C.TLNAME AS TLNAME, D.REF_CONTENT AS NOTES 

        FROM SHO_REQ_SERVICE_DVCM D 

            INNER JOIN SHO_REQUEST_PROCESS A ON D.REQ_ID=A.REQ_ID AND D.BRANCH_ID=A.BRANCH_ID AND D.DVCM_ID=A.DEP_ID

            INNER JOIN SHO_REQUEST_PROCESS_CHILD C ON D.REQ_ID=C.REQ_ID AND A.ID=C.PROCESS_ID AND C.TYPE_JOB='XL'

        WHERE D.REQ_ID=@p_REQ_ID

            AND A.PROCESS_ID=@CURENT_PROCESS

            AND A.CHECKER_ID IS NOT NULL

            AND C.TLNAME=@p_USER_LOGIN

            AND D.REQ_STATUS='E'

    END

END

-- END PHIẾU YÊU CẦU DỊCH VỤ VĂN PHÒNG HO --

-- BEGIN PHIẾU YÊU CẦU Nhập kho tập trung --

DECLARE @BRN VARCHAR(15) = '', @DEP VARCHAR(15) = ''

IF (@P_TYPE = 'IN_DOC_QLK')

BEGIN 

    SELECT @AUTH_STATUS=AUTH_STATUS

    FROM REQ_IN_DOCUMENT_GATHER ridg 

    WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID 

    -- LƯU NHÁP

    IF(@AUTH_STATUS ='E')

    BEGIN

        SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME

        FROM REQ_IN_DOCUMENT_GATHER ridg

        WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID

    END

    -- TỪ CHỐI

    ELSE IF(@AUTH_STATUS='R')

    BEGIN

        SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME

         FROM REQ_IN_DOCUMENT_GATHER ridg

        WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'R'

    END

    -- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT

    ELSE IF(@AUTH_STATUS='P')

    BEGIN

        SELECT 'MID_APP' AS PROCESS_ID, ridg.CHECKER_MID_ID AS TLNAME

      FROM REQ_IN_DOCUMENT_GATHER ridg

        WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'P'

    END

    -- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT

    ELSE IF(@AUTH_STATUS IN ('U'))

    BEGIN

        SELECT @DEP = DEP_ID FROM REQ_IN_DOCUMENT_GATHER WHERE REQ_IN_DOC_GATHER_ID = @p_REQ_ID 

    		SELECT @BRN = BRANCH_ID FROM REQ_IN_DOCUMENT_GATHER WHERE REQ_IN_DOC_GATHER_ID = @p_REQ_ID 

--    		IF (ISNULL(@BRN, '') = '')

--    		BEGIN

--    			SET @BRN = @DEP

--    			SET @DEP = ''

--    		END

    		SELECT 'APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME

    		FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubReqAddDocument')

    		WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') 

    		AND (DEP_ID = @DEP OR BRANCH_ID = @BRN)

    END

      ELSE IF(@AUTH_STATUS IN ('A'))

    BEGIN

    		SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_IN_DOCUMENT_GATHER WHERE REQ_IN_DOC_GATHER_ID = @p_REQ_ID 

    		SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP

    		IF (ISNULL(@BRN, '') = '')

    		BEGIN

    			SET @BRN = @DEP

    			SET @DEP = ''

    		END

    		SELECT 'DPXL' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME

    		FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubReqAddDocument')

    		WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') 

    		AND (DEP_ID = @DEP OR BRANCH_ID = @BRN)

    END

      ELSE IF(@AUTH_STATUS IN ('N'))

    BEGIN

    		SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_IN_DOCUMENT_GATHER WHERE REQ_IN_DOC_GATHER_ID = @p_REQ_ID 

    		SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP

    		IF (ISNULL(@BRN, '') = '')

    		BEGIN

    			SET @BRN = @DEP

    			SET @DEP = ''

    		END

    		SELECT 'GDDV_APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME

    		FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubReqAddDocument')

    		WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') 

    		AND (DEP_ID = @DEP OR BRANCH_ID = @BRN)

    END

    ELSE IF(@AUTH_STATUS IN ('S','C','M','T'))

    BEGIN

        -- Xử lý điều phối

      SELECT 'XL' AS PROCESS_ID, ridg.CHECKER_ID_XL AS TLNAME

      FROM REQ_IN_DOCUMENT_GATHER ridg

        WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID AND  @AUTH_STATUS IN ('S','C','M','T') AND @p_USER_LOGIN = ridg.CHECKER_ID_XL

    END

END

-- END PYC Nhập kho tập trung --

-- BEGIN PHIẾU YÊU CẦU mượn kho tập trung --

ELSE IF (@P_TYPE = 'BOR_DOC_QLK')

BEGIN 

    SELECT @AUTH_STATUS=AUTH_STATUS

    FROM REQ_BOR_DOCUMENT_GATHER ridg 

    WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID 

    -- LƯU NHÁP

    IF(@AUTH_STATUS ='E')

    BEGIN

        SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME

        FROM REQ_BOR_DOCUMENT_GATHER ridg

        WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID

    END

    -- TỪ CHỐI

    ELSE IF(@AUTH_STATUS='R')

    BEGIN

        SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME

         FROM REQ_BOR_DOCUMENT_GATHER ridg

        WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'R'

    END

    -- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT

    ELSE IF(@AUTH_STATUS='P')

    BEGIN

        SELECT 'MID_APP' AS PROCESS_ID, ridg.CHECKER_MID_ID AS TLNAME

      FROM REQ_BOR_DOCUMENT_GATHER ridg

        WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'P'

    END

    -- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT

    ELSE IF(@AUTH_STATUS IN ('U'))

    BEGIN

        SELECT @DEP = DEP_ID FROM REQ_BOR_DOCUMENT_GATHER WHERE REQ_BOR_DOC_GATHER_ID = @p_REQ_ID 

    		SELECT @BRN = BRANCH_ID FROM REQ_BOR_DOCUMENT_GATHER WHERE REQ_BOR_DOC_GATHER_ID = @p_REQ_ID 

--    		IF (ISNULL(@BRN, '') = '')

--    		BEGIN

--    			SET @BRN = @DEP

--    			SET @DEP = ''

--    		END

    		SELECT 'APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME

    		FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubBorrDocument')

    		WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') 

    		AND (DEP_ID = @DEP OR BRANCH_ID = @BRN)

    END

      ELSE IF(@AUTH_STATUS IN ('A'))

    BEGIN

    		SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_BOR_DOCUMENT_GATHER WHERE REQ_BOR_DOC_GATHER_ID = @p_REQ_ID 

    		SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP

    		IF (ISNULL(@BRN, '') = '')

    		BEGIN

    			SET @BRN = @DEP

    			SET @DEP = ''

    		END

    		SELECT 'DPXL' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME

    		FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubBorrDocument')

    		WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') 

    		AND (DEP_ID = @DEP OR BRANCH_ID = @BRN)

    END

      ELSE IF(@AUTH_STATUS IN ('N'))

    BEGIN

    		SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_BOR_DOCUMENT_GATHER WHERE REQ_BOR_DOC_GATHER_ID = @p_REQ_ID 

    		SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP

    		IF (ISNULL(@BRN, '') = '')

    		BEGIN

    			SET @BRN = @DEP

    			SET @DEP = ''

    		END

    		SELECT 'GDDV_APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME

    		FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubBorrDocument')

    		WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') 

    		AND (DEP_ID = @DEP OR BRANCH_ID = @BRN)

    END

    ELSE IF(@AUTH_STATUS IN ('S','C','M','T'))

    BEGIN

        -- Xử lý điều phối

      SELECT 'XL' AS PROCESS_ID, ridg.CHECKER_ID_XL AS TLNAME

      FROM REQ_BOR_DOCUMENT_GATHER ridg

        WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS IN ('S','C','M','T') AND @p_USER_LOGIN = ridg.CHECKER_ID_XL

    END

END

-- END PYC mượn kho tập trung --

-- BEGIN PHIẾU YÊU CẦU xuất kho tập trung --

ELSE IF (@P_TYPE = 'OUT_DOC_QLK')

BEGIN 

    SELECT @AUTH_STATUS=AUTH_STATUS

    FROM REQ_OUT_DOCUMENT_GATHER ridg 

    WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID 

    -- LƯU NHÁP

    IF(@AUTH_STATUS ='E')

    BEGIN

        SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME

        FROM REQ_OUT_DOCUMENT_GATHER ridg

        WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID

    END

    -- TỪ CHỐI

    ELSE IF(@AUTH_STATUS='R')

    BEGIN

        SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME

         FROM REQ_OUT_DOCUMENT_GATHER ridg

        WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'R'

    END

    -- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT

    ELSE IF(@AUTH_STATUS='P')

    BEGIN

        SELECT 'MID_APP' AS PROCESS_ID, ridg.CHECKER_MID_ID AS TLNAME

      FROM REQ_OUT_DOCUMENT_GATHER ridg

        WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'P'

    END

    -- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT

    ELSE IF(@AUTH_STATUS IN ('U'))

    BEGIN

        SELECT @DEP = DEP_ID FROM REQ_OUT_DOCUMENT_GATHER WHERE REQ_OUT_DOC_GATHER_ID = @p_REQ_ID 

    		SELECT @BRN = BRANCH_ID FROM REQ_OUT_DOCUMENT_GATHER WHERE REQ_OUT_DOC_GATHER_ID = @p_REQ_ID 

--    		IF (ISNULL(@BRN, '') = '')

--    		BEGIN

--    			SET @BRN = @DEP

--    			SET @DEP = ''

--    		END

    		SELECT 'APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME

    		FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubExportDocument')

    		WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') 

    		AND (DEP_ID = @DEP OR BRANCH_ID = @BRN)

    END

      ELSE IF(@AUTH_STATUS IN ('A'))

    BEGIN

    		SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_OUT_DOCUMENT_GATHER WHERE REQ_OUT_DOC_GATHER_ID = @p_REQ_ID 

    		SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP

    		IF (ISNULL(@BRN, '') = '')

    		BEGIN

    			SET @BRN = @DEP

    			SET @DEP = ''

    		END

    		SELECT 'DPXL' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME

    		FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubExportDocument')

    		WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') 

    		AND (DEP_ID = @DEP OR BRANCH_ID = @BRN)

    END

      ELSE IF(@AUTH_STATUS IN ('N'))

    BEGIN

    		SELECT @DEP = BRANCH_MANAGER_ID FROM REQ_OUT_DOCUMENT_GATHER WHERE REQ_OUT_DOC_GATHER_ID = @p_REQ_ID 

    		SELECT @BRN = BRANCH_ID FROM CM_DEPARTMENT WHERE DEP_ID = @DEP

    		IF (ISNULL(@BRN, '') = '')

    		BEGIN

    			SET @BRN = @DEP

    			SET @DEP = ''

    		END

    		SELECT 'GDDV_APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, @p_USER_LOGIN AS TLNAME

    		FROM FN_GET_ROLE_USER_BY_TLNAME(@p_USER_LOGIN, 'Pages.Administration.HubExportDocument')

    		WHERE ROLE_DISPLAYNAME IN ('GDDV','TBP','KSV') 

    		AND (DEP_ID = @DEP OR BRANCH_ID = @BRN)