
			
			
            
            
           	
            -- для специальностей
			
			select
				msf.MedSpecOms_id as "MedSpecOms_id",
				mso.MedSpecOms_Code as "MedSpecOms_Code",
				0 as "LpuSectionProfile_id",
				rtrim(mso.MedSpecOms_PortalName) as "ProfileSpec_Name",
				null as "LpuSectionProfile_Code",
				null as "LpuSectionProfile_IsArea"
			from
				v_MedStaffFact msf
				left join persis.WorkPlace wp on msf.MedStaffFact_id = wp.id
				inner join v_Lpu l on l.Lpu_id = msf.Lpu_id
				left join v_MedSpecOms mso on mso.MedSpecOms_id = msf.MedSpecOms_id
				
			where
				mso.MedSpecOms_id is not null
				and mso.MedSpecOms_PortalName is not null
				and COALESCE(MedSpecOms_IsPortalPost, 2) = 2
				and COALESCE(msf.RecType_id, 6) not in (2,5,6,8)
				and (cast(coalesce(msf.WorkData_endDate, '2030-01-01') as date) >= dbo.tzGetDate())
				and COALESCE(l.Lpu_IsTest, 1) = 1
				
				
				
				and COALESCE(msf.MedStaffFactCache_IsNotShown, 0) != 2
				and mso.MedSpecOms_mainid is NULL
			group by
				msf.MedSpecOms_id,
				mso.MedSpecOms_Code,
				mso.MedSpecOms_PortalName
        

  			order by
				"ProfileSpec_Name"
		
