ALTER   PROCEDURE [dbo].[rpt_TR_REQ_DOC_Inventory]
@p_REQ_PAY_CODE	varchar(50)	= NULL,
--@p_REQ_TYPE	varchar(15)	= NULL,
@p_FromDate VARCHAR(20) = NULL,
@p_ToDate VARCHAR(20) = NULL,
@p_REQ_REASON	nvarchar(MAX)	= NULL,
----@p_PO_CODE	varchar(15)	= NULL,
--@p_BRANCH_ID	varchar(15)	= NULL,
--@p_DEP_ID	varchar(15)	= NULL,
@p_BRANCH_ID	varchar(15)	= NULL,
@p_LEVEL varchar(10) = NULL,
@p_BRANCH_CREATE	varchar(15)	= NULL,
@p_DEP_CREATE VARCHAR(15) = NULL,
@p_REF_ID varchar(15) = NULL,
@p_BRANCH_LOGIN VARCHAR(15) = NULL,
@p_USER_LOGIN VARCHAR(15)= NULL,
@p_SO_TO_TRINH VARCHAR(50) = NULL,
@p_PROCESS_ID varchar(50) = NULL,
@p_MAKER_ID nvarchar(100) = NULL,
@p_USER_XL nvarchar(100) = NULL
AS

--declare @tmp table(BRANCH_ID varchar(15))
--insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
--DECLARE @DEP_ID_LOGIN VARCHAR(15), @BRANCH_TYPE_LOGIN VARCHAR(15), @ROLE_USER_LOGIN VARCHAR(15)
--SET @DEP_ID_LOGIN=(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
--SET @BRANCH_TYPE_LOGIN=(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN)
--SET @ROLE_USER_LOGIN =  (SELECT RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)

--DECLARE @SOTTCT VARCHAR(15)= NULL
--SET @SOTTCT =(SELECT TOP 1 REQ_ID FROM PL_REQUEST_DOC WHERE REQ_CODE =@p_SO_TO_TRINH)
BEGIN 
	--SELECT 
	--ROW_NUMBER() OVER (ORDER BY A.REQ_ID ASC) AS STT,
	--CASE WHEN U.TLFullName <> '' THEN U.TLFullName ELSE N'Đang chờ điều phối' END AS NV_MS,
	--A.REQ_CODE AS SP_MS,	
	--FORMAT(A.CREATE_DT,'dd/MM/yyyy') AS NHANPHIEU,
	--FORMAT(A.APPROVE_DT,'dd/MM/yyyy') AS DUYETPHIEU,
	---------
	----CMS.DMMS_NAME AS DV_YC_MS,	
	--CASE WHEN A.BRANCH_CREATE ='DV0001' THEN DP.DEP_NAME + ' - '+ N'Hội sở' ELSE BR.BRANCH_NAME END AS DV_YC_MS,
	--PLRD.REQ_CODE AS SO_TTCT,
	--PLRD.REQ_NAME AS TEN_TTCT,
	--PLRD.REQ_CONTENT AS ND_HH,
	----------------------
	--DT.QUANTITY AS SL,
	--UN.UNIT_NAME AS DVT,
	--DT.PRICE_ETM AS DG_VAT,	
	--DT.TOTAL_AMT_ETM AS THANH_TIEN_VAT,
	--DT.TOTAL_AMT AS CP_MSTT,
	--ABS(DT.TOTAL_AMT - DT.TOTAL_AMT_ETM) AS CP_MSTK,
	--S.SUP_NAME AS NCC,
	--THH.HH_TYPE_NAME AS LOAI_HH,
	--HH.HH_NAME AS TEN_HH,
	--CT.CONTRACT_NAME AS LOAI_HD,
	--'' AS NGAY_TT_DOT1, 
	--'' AS TT_DOT1,	
	--'' AS NGAY_TT_DOT2,
	--'' AS TT_DOT2,	
	--'' AS NGAY_TT_DOT3,
	--'' AS TT_DOT3,	
	--'' AS TIENDO_TT,	
	--'' AS CP_BL_BH,
	--'' AS FROMDATE,
	--'' AS TODATE,
	--A.PROCESS_ID
	--FROM TR_REQUEST_DOC  A
	--LEFT JOIN PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
	--LEFT JOIN 
	--(
	--	SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
	--	dbo.CM_DMMS 
	--	LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
	--	LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
	--	UNION ALL
	--	SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
	--	FROM dbo.CM_DVDM
	--)CMS ON CMS.DMMS_ID=A.DMMS_ID
	--LEFT JOIN TR_REQUEST_DOC_DT DT ON A.REQ_ID = DT.REQ_DOC_ID
	--LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = DT.SUP_ID
	--LEFT JOIN CM_HANGHOA HH ON HH.HH_ID = DT.HANGHOA_ID 
	--LEFT JOIN CM_HANGHOA_TYPE THH ON THH.HH_TYPE_ID = HH.HH_TYPE_ID
	--LEFT JOIN CM_UNIT UN ON UN.UNIT_ID = HH.UNIT_ID
	--LEFT JOIN TR_CONTRACT CT ON CT.REQ_DOC_ID = A.REQ_ID
	--LEFT JOIN PL_REQUEST_PROCESS_CHILD CH ON A.REQ_ID = CH.REQ_ID AND  CH.PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.REQ_ID) AND CH.TYPE_JOB ='XL'
	--LEFT JOIN TL_USER U ON U.TLNANME = CH.TLNAME
	--LEFT JOIN CM_BRANCH BR ON A.BRANCH_CREATE = BR.BRANCH_ID
	--LEFT JOIN CM_DEPARTMENT DP ON A.DEP_CREATE = DP.DEP_ID
	----LEFT JOIN TR_CONTRACT_PAYMENT P ON P.CONTRACT_ID = CT.CONTRACT_ID 
	--WHERE 1=1
	----Thiếu những field không biết lấy như nào nên em để lại 
	----AND(A.REQ_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
	--AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' or @p_REQ_TYPE='' OR @p_REQ_TYPE IS NULL)
	--AND CONVERT(DATE, A.CREATE_DT, 103) >= CONVERT(DATE, @p_FromDate, 103)
	--AND CONVERT(DATE, A.CREATE_DT, 103) <= CONVERT(DATE, @p_ToDate, 103)
	--AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp))
	--OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_ID)OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL)))
	----AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_ID + '%' OR @p_PROCESS_ID IS NULL OR @p_PROCESS_ID='')
	--AND (A.MAKER_ID LIKE '%' + @p_MAKER_NAME + '%' OR @p_MAKER_NAME IS NULL OR @p_MAKER_NAME='')
	--AND (U.TLNANME LIKE '%' + @p_USER_XL + '%' OR @p_USER_XL IS NULL OR @p_USER_XL='')
	--AND (A.PL_REQ_ID =@SOTTCT OR @SOTTCT IS NULL OR @SOTTCT ='')
	--AND ( (@p_PROCESS_ID ='TP' AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB =@p_PROCESS_ID AND STATUS_JOB ='C' AND
	
	--PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID ='DMMS'))
	--		AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='KS' AND STATUS_JOB ='P' AND
	
	--PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID ='DMMS'))
	--		AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='XL' AND STATUS_JOB ='P' AND
	
	--PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID ='DMMS')))
	--OR (@p_PROCESS_ID ='KS' AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB =@p_PROCESS_ID AND STATUS_JOB ='C' AND
	
	--PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID ='DMMS'))
	--		AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='XL' AND STATUS_JOB ='P' AND
	
	--PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID ='DMMS')))
	--OR (@p_PROCESS_ID ='XL' AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB =@p_PROCESS_ID AND STATUS_JOB ='C' AND
	
	--PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID ='DMMS')))
	--OR @p_PROCESS_ID IS NULL OR @p_PROCESS_ID ='')
	--AND(EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='XL'  AND
	--PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID ='DMMS')))
	----AND A.PROCESS_ID IN ('DMMS','DVCM','APPROVE')
	--AND EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =A.REQ_ID AND TLNAME IN (SELECT TLNANME FROM TL_USER WHERE SECUR_CODE =@DEP_ID_LOGIN ))
	--ORDER BY A.CREATE_DT ASC, STT ASC

	--------------BAODNQ 15/11/2022 : ĐIỀU CHỈNH------------------
	-------------BẢNG TẠM NGƯỜI XỬ LÝ TIẾP THEO------------
	DECLARE @lstREQUEST TABLE (
			REQ_ID VARCHAR(20),
			PROCESS_ID VARCHAR(50),
			DVDM_NAME NVARCHAR(200),
			TLNAME VARCHAR(200),
			TLFullName NVARCHAR(200),
			NOTES NVARCHAR(200)
		)
	INSERT INTO @lstREQUEST
	( REQ_ID,PROCESS_ID, DVDM_NAME,TLNAME,TLFullName, NOTES)	
	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
	(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
	WHERE EXISTS(SELECT TRD.REQ_ID FROM dbo.TR_REQUEST_DOC TRD WHERE TRD.REQ_ID=PRC.REQ_ID) 
	AND PRC.PROCESS_ID ='DMMS' AND PRC.STATUS='C'
	) PL
	LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
	LEFT JOIN dbo.TR_REQUEST_DOC TR ON TR.REQ_ID=PL.REQ_ID
	LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN 
	(
	SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC 
	LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
	WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
	)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
	LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
	LEFT JOIN 
	(
		SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID 
		FROM
		(
		SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
		dbo.TL_USER TS 
		UNION ALL
		SELECT  TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
		dbo.TL_SYS_ROLE_MAPPING TM
		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
		WHERE  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
		) TU
		LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
		LEFT JOIN(
		SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC
		UNION ALL
		SELECT DVDM.DVDM_ID AS DMMS_ID,DVDM.DEP_ID,DVDM.BRANCH_ID  FROM 
		(
			SELECT CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID FROM dbo.CM_DVDM CD
			LEFT JOIN dbo.PL_COSTCENTER PC ON PC.DVDM_ID=CD.DVDM_ID
			LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID
			WHERE CD.IS_KHOI <>1
			GROUP BY CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID
		) DVDM
		) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID
	) TempU ON (TempU.RoleName=PL.ROLE_USER  OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE TM.ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.DMMS_ID=TR.DMMS_ID) AND TempC.ID IS NULL	
	WHERE PL.STATUS='C'	
	UNION ALL
	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
	(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
	WHERE EXISTS(SELECT TRD.REQ_ID FROM dbo.TR_REQUEST_DOC TRD WHERE TRD.REQ_ID=PRC.REQ_ID) 
	AND PRC.PROCESS_ID <>'DMMS' AND PRC.PROCESS_ID <> 'NEW' AND PRC.PROCESS_ID <>'APPROVE' 
	AND PRC.STATUS='C' AND (PRC.IS_HAS_CHILD=0 OR PRC.IS_HAS_CHILD IS NULL)
	) PL
	LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
	LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN 
	(
	SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID 
	FROM (
		SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
		dbo.TL_USER TS 
		UNION ALL
		SELECT  TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
		dbo.TL_SYS_ROLE_MAPPING TM
		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
		WHERE  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
		) TU
	LEFT JOIN
		(
		SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 
		LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID
		) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
	) TempU ON (TempU.RoleName=PL.ROLE_USER  OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') 
	AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') 
	AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
	WHERE  STATUS='C'
	UNION ALL
		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 
	(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
	WHERE EXISTS(SELECT TRD.REQ_ID FROM dbo.TR_REQUEST_DOC TRD WHERE TRD.REQ_ID=PRC.REQ_ID) 
	AND PRC.PROCESS_ID <>'DMMS' AND PRC.PROCESS_ID <> 'NEW' AND PRC.PROCESS_ID <>'APPROVE' 
	AND PRC.STATUS='C' AND PRC.IS_HAS_CHILD=1 
	) PL
	LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
	LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
	LEFT JOIN 
	(
	SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC 
	LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
	WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
	)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
	LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
	LEFT JOIN 
	(
	SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID 
	FROM       (
		SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
		dbo.TL_USER TS 
		UNION ALL
		SELECT  TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
		dbo.TL_SYS_ROLE_MAPPING TM
		LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
		WHERE  CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
		)  TU 
	LEFT JOIN(
	SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC 
	LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
	) TempU ON  (TempU.RoleName=PL.ROLE_USER  OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') 
	AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='') 
	AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
	WHERE  PL.STATUS='C'

	--SELECT
	--ROW_NUMBER() OVER (ORDER BY A.REQ_ID ASC) AS STT,
	--A.REQ_CODE AS SP_MS,
	--(
	--	STUFF((
	--		SELECT DISTINCT ', ' + CH.HH_NAME  
	--		FROM TR_REQUEST_DOC_DT TRDD
	--		LEFT JOIN CM_HANGHOA CH ON TRDD.HANGHOA_ID = CH.HH_ID
	--		WHERE TRDD.REQ_DOC_ID = A.REQ_ID
	--		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
	--) AS TEN_HH,
	----CH.HH_NAME AS TEN_HH,
	--CASE 
	--	WHEN (A.USER_DVMS IS NULL OR A.USER_DVMS = '') AND RPN.PROCESS_ID = 'DMMS'
	--		THEN N'Đang chờ điều phối' 
	--	ELSE TUMS.TLFullName
	--END AS NV_MS,
	--A.REQ_REASON AS NOI_DUNG,
	--PLRD.TOTAL_AMT AS SO_TIEN_CHU_TRUONG,
	--A.TOTAL_AMT AS SO_TIEN_THUC_TE,
	--CASE 
	--	WHEN CBC.BRANCH_TYPE = 'HS' THEN CDC.DEP_NAME + ' - '+ CBC.BRANCH_NAME
	--	ELSE CBC.BRANCH_NAME
	--END AS DV_YC_MS,
	--RPN.NOTES AS TINH_TRANG_THUC_HIEN

	--FROM TR_REQUEST_DOC A
	--LEFT JOIN PL_REQUEST_DOC PLRD ON A.PL_REQ_ID = PLRD.REQ_ID
	--LEFT JOIN CM_BRANCH CBC ON A.BRANCH_CREATE = CBC.BRANCH_ID
	--LEFT JOIN CM_DEPARTMENT CDC ON A.DEP_CREATE = CDC.DEP_ID
	--LEFT JOIN TL_USER TUMS ON A.USER_DVMS = TUMS.TLNANME
	----LEFT JOIN PL_REQUEST_PROCESS RPN ON A.REQ_ID = RPN.REQ_ID AND (RPN.STATUS = 'C' OR RPN.STATUS = 'R')
	--LEFT JOIN(
	--	SELECT TMP.REQ_ID, TMP.PROCESS_ID, TMP.STATUS, TMP.NOTES
	--	FROM PL_REQUEST_PROCESS TMP
	--	WHERE TMP.STATUS = 'C' OR TMP.STATUS = 'R'
	--	GROUP BY TMP.REQ_ID, TMP.PROCESS_ID, TMP.STATUS, TMP.NOTES
	--) RPN ON A.REQ_ID = RPN.REQ_ID
	----LEFT JOIN PL_REQUEST_PROCESS PRP ON PRP.REQ_ID = A.REQ_ID AND PRP.STATUS <> 'U'
	--LEFT JOIN PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID = A.REQ_ID AND PLRP.STATUS <> 'U' AND PLRP.PROCESS_ID IN ('DMMS', 'DVCM')
	--LEFT JOIN PL_REQUEST_PROCESS_CHILD RPC ON PLRP.ID = RPC.PROCESS_ID AND (RPC.STATUS_JOB = 'C' OR RPC.STATUS_JOB = 'R')
	--WHERE 1=1
	--AND(A.REQ_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
	--AND(PLRD.REQ_CODE LIKE N'%'+@p_SO_TO_TRINH+'%' or @p_SO_TO_TRINH='' OR @p_SO_TO_TRINH IS NULL)
	--AND CONVERT(DATE, A.CREATE_DT, 103) >= CONVERT(DATE, @p_FromDate, 103)
	--AND CONVERT(DATE, A.CREATE_DT, 103) <= CONVERT(DATE, @p_ToDate, 103)
	--AND(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' or @p_REQ_REASON='' OR @p_REQ_REASON IS NULL)
	--AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '')
	--AND (A.DEP_CREATE LIKE '%' + @p_DEP_CREATE + '%' OR @p_DEP_CREATE IS NULL OR @p_DEP_CREATE = '')
	--AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
	--AND (
	--	@p_USER_XL IN (SELECT NXL.TLNAME FROM @lstREQUEST NXL WHERE NXL.REQ_ID = A.REQ_ID)
	--	OR @p_USER_XL IS NULL OR @p_USER_XL = ''
	--)
	----AND(
	----	@p_PROCESS_ID IN (
	----		SELECT TYPE_JOB FROM PL_REQUEST_PROCESS_CHILD 
	----		WHERE REQ_ID = A.REQ_ID AND STATUS_JOB = 'C'
	----		AND PROCESS_ID = (SELECT TOP 1 TMP.ID FROM PL_REQUEST_PROCESS TMP WHERE TMP.REQ_ID = A.REQ_ID AND(TMP.STATUS = 'C' OR TMP.STATUS = 'R')))
	----	OR @p_PROCESS_ID IS NULL OR @p_PROCESS_ID = ''
	----)
	--AND(
	--	RPN.PROCESS_ID = 'DMMS'
	--	AND (@p_PROCESS_ID = 'XL' AND RPC.TYPE_JOB = @p_PROCESS_ID)
	--	OR (@p_PROCESS_ID = 'KS' AND RPC.TYPE_JOB = @p_PROCESS_ID 
	--		AND EXISTS (SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD Temp WHERE Temp.REQ_ID=A.REQ_ID AND Temp.STATUS_JOB='P' AND Temp.TYPE_JOB='XL')
	--	)
	--	OR(
	--		@p_PROCESS_ID = 'TP' AND RPC.TYPE_JOB = @p_PROCESS_ID
	--		AND EXISTS (SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD Temp WHERE Temp.REQ_ID=A.REQ_ID AND Temp.STATUS_JOB='P' AND Temp.TYPE_JOB='XL')
	--		AND (
	--			EXISTS (SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD Temp WHERE Temp.REQ_ID=A.REQ_ID AND Temp.STATUS_JOB='P' AND Temp.TYPE_JOB='KS')
	--			OR NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD Temp WHERE Temp.REQ_ID=A.REQ_ID AND Temp.TYPE_JOB = 'KS')
	--		)
	--	)
	--	OR @p_PROCESS_ID IS NULL OR @p_PROCESS_ID = ''
		
	--)

	SELECT
	ROW_NUMBER() OVER (ORDER BY A.REQ_ID ASC) AS STT,
	A.REQ_CODE AS SP_MS,
	CONVERT(DATE, A.REQ_DT,103) AS NGAY_YEU_CAU,
	--CONVERT(DATE, P.TRANFER_DT,103) AS NGAY_KIEM_SOAT ,
	(
		STUFF((
			SELECT DISTINCT ', ' + CH.HH_NAME  
			FROM TR_REQUEST_DOC_DT TRDD
			LEFT JOIN CM_HANGHOA CH ON TRDD.HANGHOA_ID = CH.HH_ID
			WHERE TRDD.REQ_DOC_ID = A.REQ_ID
			FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
	) AS TEN_HH,
	(
		SELECT TLNAME FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =A.REQ_ID AND TYPE_JOB ='XL' 
		AND PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_ID AND PROCESS_ID ='DMMS') 
	) 
	AS NV_MS, 
	A.REQ_REASON AS NOI_DUNG,
	X.SUM_MS_TOTRINH AS SO_TIEN_CHU_TRUONG, 
	X.SUM_MS_THUCTE AS SO_TIEN_THUC_TE,
	CASE 
		WHEN BR.BRANCH_TYPE = 'HS' THEN DP.DEP_NAME + ' - '+ BR.BRANCH_NAME
		ELSE BR.BRANCH_NAME
	END AS DV_YC_MS,
	CASE 
		 WHEN A.PROCESS_ID ='APPROVE' THEN N'Hoàn tất' 
		 WHEN A.PROCESS_ID ='DMMS' AND EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='XL' AND STATUS_JOB ='C' AND PROCESS_ID = PLRP.ID) 
			THEN N'Đang xử lý' 
		 WHEN A.PROCESS_ID ='DMMS' AND EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='XL' AND STATUS_JOB ='P' AND PROCESS_ID = PLRP.ID)
									AND EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='KS' AND STATUS_JOB ='C' AND PROCESS_ID = PLRP.ID)
			THEN N'Đang chờ KSV phê duyệt'
		 WHEN A.PROCESS_ID ='DMMS' AND NOT EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='XL' AND PROCESS_ID = PLRP.ID)
									AND EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='KS' AND STATUS_JOB ='C' AND PROCESS_ID = PLRP.ID)
			THEN N'Đang chờ KSV điều phối'
		WHEN A.PROCESS_ID ='DMMS' AND NOT EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND PROCESS_ID = PLRP.ID)
			THEN N'Đang chờ trưởng ĐMMS điều phối'
		 WHEN A.PROCESS_ID ='DMMS'	AND EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='XL' AND STATUS_JOB ='P' AND PROCESS_ID = PLRP.ID)
									AND(
										EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='KS' AND STATUS_JOB ='P' AND PROCESS_ID = PLRP.ID)
										OR NOT EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='KS' AND PROCESS_ID = PLRP.ID)
									)
									AND EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='TP' AND STATUS_JOB ='C' AND PROCESS_ID = PLRP.ID)
			THEN N'Đang chờ trưởng ĐMMS phê duyệt'
	END
	AS TINH_TRANG_THUC_HIEN

	FROM  TR_REQUEST_DOC A
	INNER JOIN
	(
		SELECT  REQ_DOC_ID,SUM(TOTAL_AMT) AS SUM_MS_THUCTE, SUM(TOTAL_AMT_ETM) AS SUM_MS_TOTRINH FROM TR_REQUEST_DOC_DT 
		GROUP BY REQ_DOC_ID
	) X ON A.REQ_ID = X.REQ_DOC_ID
	INNER JOIN CM_BRANCH BR ON A.BRANCH_CREATE = BR.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT DP ON A.DEP_CREATE = DP.DEP_ID
	--INNER JOIN PL_REQUEST_PROCESS_CHILD P ON A.REQ_ID =P.REQ_ID
	LEFT JOIN PL_REQUEST_DOC PLRD ON A.PL_REQ_ID = PLRD.REQ_ID
	LEFT JOIN(
		SELECT TMP.REQ_ID, TMP.PROCESS_ID, TMP.STATUS, TMP.NOTES
		FROM PL_REQUEST_PROCESS TMP
		WHERE TMP.STATUS = 'C' OR TMP.STATUS = 'R'
		GROUP BY TMP.REQ_ID, TMP.PROCESS_ID, TMP.STATUS, TMP.NOTES
	) RPN ON A.REQ_ID = RPN.REQ_ID
	LEFT JOIN PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID = A.REQ_ID AND PLRP.PROCESS_ID = 'DMMS'
	LEFT JOIN PL_REQUEST_PROCESS_CHILD RPC ON PLRP.ID = RPC.PROCESS_ID AND (RPC.STATUS_JOB = 'C' OR RPC.STATUS_JOB = 'R')
	WHERE 1=1
	AND(A.REQ_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
	AND(PLRD.REQ_CODE LIKE N'%'+@p_SO_TO_TRINH+'%' or @p_SO_TO_TRINH='' OR @p_SO_TO_TRINH IS NULL)
	AND CONVERT(DATE, A.CREATE_DT, 103) >= CONVERT(DATE, @p_FromDate, 103)
	AND CONVERT(DATE, A.CREATE_DT, 103) <= CONVERT(DATE, @p_ToDate, 103)
	AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '')
	AND (A.DEP_CREATE LIKE '%' + @p_DEP_CREATE + '%' OR @p_DEP_CREATE IS NULL OR @p_DEP_CREATE = '')
	AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
	AND (
		@p_USER_XL IN (SELECT NXL.TLNAME FROM @lstREQUEST NXL WHERE NXL.REQ_ID = A.REQ_ID)
		OR @p_USER_XL IS NULL OR @p_USER_XL = ''
	)
	AND(
		RPN.PROCESS_ID = 'DMMS'
		AND (@p_PROCESS_ID = 'XL' AND RPC.TYPE_JOB = @p_PROCESS_ID)
		OR (@p_PROCESS_ID = 'KS' AND RPC.TYPE_JOB = @p_PROCESS_ID 
			AND EXISTS (SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD Temp WHERE Temp.REQ_ID=A.REQ_ID AND Temp.STATUS_JOB='P' AND Temp.TYPE_JOB='XL')
		)
		OR(
			@p_PROCESS_ID = 'TP' AND RPC.TYPE_JOB = @p_PROCESS_ID
			AND EXISTS (SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD Temp WHERE Temp.REQ_ID=A.REQ_ID AND Temp.STATUS_JOB='P' AND Temp.TYPE_JOB='XL')
			AND (
				EXISTS (SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD Temp WHERE Temp.REQ_ID=A.REQ_ID AND Temp.STATUS_JOB='P' AND Temp.TYPE_JOB='KS')
				OR NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD Temp WHERE Temp.REQ_ID=A.REQ_ID AND Temp.TYPE_JOB = 'KS')
			)
		)
		OR @p_PROCESS_ID IS NULL OR @p_PROCESS_ID = ''
		
	)
	--CONVERT(DATE, P.TRANFER_DT, 103) >='2021-01-01' AND CONVERT(DATE, P.TRANFER_DT, 103) <='2021-06-30'
	--AND P.TLNAME IN ('vanpt2','tanvt') AND TYPE_JOB IN ('KS','XL')
	--ORDER BY BR.BRANCH_NAME

END