DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

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