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

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 AS TLNANME,US.TLFullName,TU.ROLE_NEW RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID

		FROM dbo.TL_SYS_ROLE_MAPPING 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  CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)

		) 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 (((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 = (SELECT prd.REQ_ID FROM PL_REQUEST_DOC prd WHERE prd.REQ_CODE = '0002/2023/TTr-0690405') AND STATUS='C'