ALTER PROCEDURE dbo.PL_REQUEST_COSTCENTER_ByID
@P_REQ_ID varchar(15),
@p_TYPE VARCHAR(15),
@p_TLNAME VARCHAR(20)
AS

DECLARE @DEP_ID VARCHAR(15),@BRANCH_ID  VARCHAR(15),@ROLE VARCHAR(15),@IS_ALL BIT

	
	SET @IS_ALL=0

DECLARE
@COST_ID TABLE (
	COST_ID VARCHAR(15)
)

DECLARE @DVDM_ID TABLE (
	DVDM_ID VARCHAR(15)
)

SET @DEP_ID=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)

SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)
SET @ROLE=(SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)
IF(EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_MASTER' AND BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID))
		SET @IS_ALL=1


	INSERT INTO @COST_ID
	SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@BRANCH_ID

	INSERT INTO @DVDM_ID
	SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM @COST_ID) GROUP BY DVDM_ID


IF(@p_TYPE='DVKD')
BEGIN
	SELECT A.*,CC.DVDM_NAME AS COST_NAME,
		CC.DVDM_CODE AS COST_CODE,
		CASE  
			WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' 
			ELSE N'Chưa xác nhận' 
		END AS STATUS_NAME,
		--------BAODNQ 22/6/2022 : LẤY THÊM DEP_ID----------
    STUFF((select ', ' + cd.DEP_ID  
		  FROM CM_DEPARTMENT cd
		  WHERE cd.FATHER_ID = PCD.DEP_ID OR cd.DEP_ID = PCD.DEP_ID
		  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''
    ) AS DEP_ID
	FROM dbo.PL_REQUEST_COSTCENTER A
	LEFT JOIN dbo.CM_DVDM CC ON A.COST_ID=CC.DVDM_ID
	LEFT JOIN PL_COSTCENTER PC ON PC.DVDM_ID = CC.DVDM_ID
	LEFT JOIN PL_COSTCENTER_DT PCD ON PC.COST_ID = PCD.COST_ID
	 WHERE A.REQ_ID=@P_REQ_ID
END
ELSE IF(@p_TYPE='PDTT')
BEGIN
	SELECT A.*,CC.DVDM_NAME AS COST_NAME,CC.DVDM_CODE AS COST_CODE,  CASE  WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' ELSE N'Chưa xác nhận' END AS STATUS_NAME
FROM dbo.PL_REQUEST_COSTCENTER A
	LEFT JOIN dbo.CM_DVDM CC ON A.COST_ID=CC.DVDM_ID
 WHERE A.REQ_ID=@P_REQ_ID 
 --AND (
 --( A.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID) OR @ROLE ='TGD' OR @IS_ALL=1 OR @ROLE='HDQT'))

END
ELSE IF(@p_TYPE='XLTT' OR @p_TYPE='TFJOB')
BEGIN
	SELECT A.*,CC.DVDM_NAME AS COST_NAME,CC.DVDM_CODE AS COST_CODE,  CASE  WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' ELSE N'Chưa xác nhận' END AS STATUS_NAME
	FROM dbo.PL_REQUEST_COSTCENTER A
	LEFT JOIN dbo.CM_DVDM CC ON A.COST_ID=CC.DVDM_ID
	 WHERE A.REQ_ID=@P_REQ_ID  
	-- AND (
	-- ( A.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID))
 --OR @ROLE ='TGD' OR @IS_ALL=1  OR @ROLE='HDQT' )
 
END
GO