SELECT
			1 as dummy, 
			ed.EvnDirection_id referralId,
			ed.EvnDirection_Num referralNumber,
			case 
				when dt.DirType_Code = 9 then 4 
				when dt.DirType_Code = 3 then 6 
				else 8 
			end referralType,
			Evn.Evn_setDT referralStartDate,
			1 paymentSourceId,
			CASE 
				WHEN dt.DirType_Code = 3 and Org.Org_id is not null then extRef.toMOOID
				ELSE COALESCE(lud.LpuUnit_FRMOUnitID, dFRMOUnit.FRMOUnit_OID, dFRMOSection.FRMOUnit_OID, ptd.PassportToken_tid)
			END toMOOID,
			dPost.Post_Code toPostCode,
			dPost.Post_Name toPostName,
			dmpp.MedPersonalPost_Code toPostId,
			CASE  WHEN dt.DirType_Code = 3 AND Org.Org_id is not null then extRef.specialityId ELSE serviceInfo.specialityId end specialityId,
			CASE  WHEN dt.DirType_Code = 3 AND Org.Org_id is not null then extRef.serviceId ELSE serviceInfo.serviceId end serviceId,
			serviceInfo.serviceInnerId,
			serviceInfo.serviceInnerName,
			concat(vpsd.Person_SurName,  ' ', vpsd.Person_FirName, ' ', vpsd.Person_SecName) toResourceName,
			vpsd.Person_Snils toResourceSnils,
			d.Diag_SCode desease,
			ed.EvnDirection_Descr note,
			COALESCE(sLpuUnit.LpuUnit_FRMOUnitID, sFRMOUnit.FRMOUnit_OID, sFRMOSection.FRMOUnit_OID, pts.PassportToken_tid) fromMOOID,
			sPost.Post_Code fromPostCode,
			sPost.Post_Name fromPostName,
			smpp.MedPersonalPost_Code fromPostId,
			concat(vpss.Person_SurName, ' ', vpss.Person_FirName, ' ', vpss.Person_SecName) fromResourceName,
			vpss.Person_Snils fromResourceSnils,
			case 
			   when dt.DirType_Code = 3 and Org.Org_id is not null then 0 
			   when dt.DirType_Code = 3 then 1
			   when dt.DirType_Code = 9 and mst.MedServiceType_Code = 3 and ed.Resource_id is not null then 2
			   when dt.DirType_Code = 9 and mst.MedServiceType_Code in (1,2,60,62) then 3
			end referralSwitch,
			EvnQueue.MedStaffFact_did, 
			ed.Lpu_did,
			ed.LpuSection_did,
			ed.LpuUnit_did,
			ed.MedPersonal_did,
			ed.Resource_id,
			ed.MedService_id,
			uslms.UslugaComplexMedService_id, 
			dbo.tzGetDate() curDT
		FROM  dbo.v_EvnDirection_all ed  
		INNER JOIN dbo.Evn   ON Evn.Evn_id = ed.EvnDirection_id
		INNER JOIN dbo.EvnStatus   ON EvnStatus.EvnStatus_id = Evn.EvnStatus_id and EvnStatus.EvnStatus_SysNick = 'Queued'
		LEFT JOIN dbo.v_EvnQueue EvnQueue   ON EvnQueue.EvnQueue_id = ed.EvnQueue_id
		LEFT JOIN dbo.DirType dt   ON dt.DirType_id = ed.DirType_id 
		LEFT JOIN fed.PassportToken ptd   ON ptd.Lpu_id = ed.Lpu_did
		LEFT JOIN persis.MedWorker mwd   ON mwd.id = ed.MedPersonal_did 
		LEFT JOIN v_PersonState vpsd   ON mwd.Person_id = vpsd.Person_id 
		LEFT JOIN dbo.Diag d   ON d.Diag_id = ed.Diag_id 
		LEFT JOIN fed.PassportToken pts   ON pts.Lpu_id = ed.Lpu_sid 
		LEFT JOIN persis.MedWorker mws   ON mws.id = ed.MedPersonal_id 
		LEFT JOIN v_PersonState vpss   ON mws.Person_id = vpss.Person_id
		-- Для определения где искать бирки
		LEFT JOIN MedService ms   on ms.MedService_id = ed.MedService_id
		LEFT JOIN MedServiceType mst   on ms.MedServiceType_id = mst.MedServiceType_id 
		LEFT JOIN dbo.UslugaComplexMedService uslms   on ms.MedService_id=uslms.MedService_id and uslms.UslugaComplex_id = ed.UslugaComplex_did 
		--toMO
		LEFT JOIN v_LpuUnit lud   ON lud.LpuUnit_id = ed.LpuUnit_did 
		LEFT JOIN nsi.FRMOUnit dFRMOUnit  ON dFRMOUnit.FRMOUnit_id = lud.FRMOUnit_id
		 left join lateral  (
			SELECT   FRMOSection.FRMOUnit_OID, FRMOUnit_Name 
			FROM dbo.v_LpuSection  
			INNER JOIN nsi.FRMOSection   ON FRMOSection.FRMOSection_id = v_LpuSection.FRMOSection_id
			INNER JOIN nsi.FRMOUnit   ON FRMOUnit.FRMOUnit_OID = FRMOSection.FRMOUnit_OID
			WHERE FRMOSection.FRMOUnit_OID IS NOT NULL	AND v_LpuSection.LpuUnit_id=lud.LpuUnit_id
			 LIMIT 1 
		) AS dFRMOSection  ON true 
		--fromMO
		LEFT JOIN dbo.v_LpuSection sLpuSection   ON sLpuSection.LpuSection_id = ed.LpuSection_id
		LEFT JOIN dbo.v_LpuUnit sLpuUnit  ON sLpuUnit.LpuUnit_id = sLpuSection.LpuUnit_id
		LEFT JOIN nsi.FRMOUnit sFRMOUnit  ON sFRMOUnit.FRMOUnit_id = sLpuUnit.FRMOUnit_id
		 left join lateral  (
			SELECT   FRMOSection.FRMOUnit_OID, FRMOUnit_Name 
			FROM dbo.v_LpuSection  
			INNER JOIN nsi.FRMOSection   ON FRMOSection.FRMOSection_id = v_LpuSection.FRMOSection_id
			INNER JOIN nsi.FRMOUnit   ON FRMOUnit.FRMOUnit_OID = FRMOSection.FRMOUnit_OID
			WHERE FRMOSection.FRMOUnit_OID IS NOT NULL	AND v_LpuSection.LpuUnit_id=sLpuUnit.LpuUnit_id
			 LIMIT 1 
		) AS sFRMOSection  ON true 
		--fromPost
		LEFT JOIN nsi.v_PostLink sPostLink   on sPostLink.Post_lid = ed.Post_id
		LEFT JOIN nsi.v_Post sPost   on sPost.Post_id = sPostLink.Post_id
		LEFT JOIN persis.Post spp   on spp.id =  ed.Post_id
		LEFT JOIN persis.FRMPPost sfrp   on sfrp.id =  spp.frmpEntry_id
		LEFT JOIN nsi.MedPersonalPost smpp   on smpp.MedPersonalPost_id =  sfrp.Frmr_id
		--toPost
		LEFT JOIN dbo.v_MedStaffFact msfq   ON msfq.MedStaffFact_id = EvnQueue.MedStaffFact_did
		 left join lateral  (
			SELECT   MedStaffFact.*
			FROM dbo.v_MedStaffFact MedStaffFact  
			WHERE dbo.tzGetDate() <= COALESCE(WorkData_endDate, dbo.tzGetDate())
			  AND EvnQueue.MedStaffFact_did is null
			  AND MedStaffFact.MedPersonal_id = ed.MedPersonal_did 
	  		  AND MedStaffFact.Lpu_id = COALESCE(ed.Lpu_did, MedStaffFact.Lpu_id)
	  		  AND COALESCE(MedStaffFact.LpuUnit_id,-1) = COALESCE(ed.LpuUnit_did, MedStaffFact.LpuUnit_id, -1) 
	          AND COALESCE(MedStaffFact.LpuSection_id,-1) = COALESCE(ed.LpuSection_did, MedStaffFact.LpuSection_id, -1)
			 LIMIT 1 
		) msfp  ON true 
		LEFT JOIN nsi.v_PostLink dPostLink   ON dPostLink.Post_lid = COALESCE(msfq.Post_id, msfp.Post_id) 
		LEFT JOIN nsi.v_Post dPost   ON dPost.Post_id = dPostLink.Post_id
		LEFT JOIN persis.Post dpp   ON dpp.id =  COALESCE(msfq.Post_id, msfp.Post_id)
		LEFT JOIN persis.FRMPPost dfrp   ON dfrp.id =  dpp.frmpEntry_id
		LEFT JOIN nsi.MedPersonalPost dmpp   ON dmpp.MedPersonalPost_id =  dfrp.Frmr_id
		 left join lateral (
			 
			 
			 
			 
		SELECT  
			CASE 
				WHEN g.FRMRNomenSpeciality_Code is not null then g.FRMRNomenSpeciality_Code
				WHEN MedServiceType.MedServiceType_Code = 3 AND ed.Resource_id is not null then '35' 
				WHEN MedServiceType.MedServiceType_Code in (1,2,60,62) then '13' 
			END specialityId,
			COALESCE (u0.NomenMedUsluga_UslugaCode, u1.NomenMedUsluga_UslugaCode) serviceId,
			uc.UslugaComplex_id serviceInnerId,
			uc.UslugaComplex_Name serviceInnerName
		FROM dbo.v_UslugaComplex uc  
		LEFT JOIN nsi.v_NomenMedUsluga u0   ON u0.NomenMedUsluga_UslugaCode = 
			 
				case when SUBSTRING(uc.UslugaComplex_Code from 2 for 1) = '.' then  substring(uc.UslugaComplex_Code from 1 for 1) || SUBSTRING(uc.UslugaComplex_Code from 3) else uc.UslugaComplex_Code end
				 
		LEFT JOIN dbo.v_UslugaComplex uc1   ON uc1.UslugaComplex_id = COALESCE (uc.UslugaComplex_2011id , uc.UslugaComplex_2004id)
		LEFT JOIN nsi.v_NomenMedUsluga u1   ON u1.NomenMedUsluga_UslugaCode = 
			 
				case when SUBSTRING(uc1.UslugaComplex_Code from 2 for 1) = '.' then  substring(uc1.UslugaComplex_Code from 1 for 1) || SUBSTRING(uc1.UslugaComplex_Code from 3) else uc1.UslugaComplex_Code end
				 
		LEFT JOIN dbo.MedService MedService   on MedService.MedService_id = ed.MedService_id
		LEFT JOIN dbo.MedServiceType MedServiceType   on MedService.MedServiceType_id = MedServiceType.MedServiceType_id 
		LEFT JOIN nsi.UslugaComplexMedSpec ums   ON ums.UslugaComplex_id = CASE WHEN u0.NomenMedUsluga_UslugaCode is not null then uc.UslugaComplex_id else uc1.UslugaComplex_id END
		LEFT JOIN nsi.MedSpecLink msl   on msl.MedSpec_id = ums.MedSpec_id 
		LEFT JOIN dbo.MedSpecOms e   on msl.MedSpecOms_id = e.MedSpecOms_fedid  
  		LEFT JOIN persis.FRMRNomenSpecialityLink f   on f.MedSpecClass_fedid = e.MedSpecClass_id 
		LEFT JOIN persis.FRMRNomenSpeciality g   on g.FRMRNomenSpeciality_id=f.FRMRNomenSpeciality_id	
		WHERE uc.UslugaComplex_id = ed.UslugaComplex_did
		  AND COALESCE (u0.NomenMedUsluga_UslugaCode, u1.NomenMedUsluga_UslugaCode) is not null
		 LIMIT 1 
	 
		) serviceInfo  ON true 
		--external referral
		LEFT JOIN dbo.Org ON ed.Org_oid = Org.Org_id AND Org.Org_IsNotForSystem = 2
		 left join lateral  (
			SELECT  
				'1.2.643.5.1.13.3.25.2.999' toMOOID, 
				uc.UslugaComplex_Code serviceId,
				g.FRMRNomenSpeciality_Code specialityId
			FROM  nsi.UslugaComplexMedSpec   
			INNER JOIN dbo.UslugaComplex uc   on uc.UslugaComplex_id = UslugaComplexMedSpec.UslugaComplex_id AND uc.UslugaComplex_Code like 'B01.%'
			INNER JOIN dbo.MedSpecOms   on MedSpecOms.MedSpec_id = ed.MedSpec_fid 
			INNER JOIN persis.FRMRNomenSpecialityLink f   on f.MedSpecClass_fedid = MedSpecOms.MedSpecClass_id 
			INNER JOIN persis.FRMRNomenSpeciality g   on g.FRMRNomenSpeciality_id=f.FRMRNomenSpeciality_id	
			where UslugaComplexMedSpec.UslugaComplexMedSpec_id = ed.MedSpec_fid 
			order by case when uc.UslugaComplex_Code is null then 'ZZZZZZZ' else uc.UslugaComplex_Code end
			 LIMIT 1 
		) extRef  ON true 
		WHERE  Evn.Evn_deleted = 1 AND (
			(dt.DirType_Code = 3 AND dmpp.MedPersonalPost_Code is not null) OR
			(dt.DirType_Code = 3 AND Org.Org_id is not null) OR
			(dt.DirType_Code = 9 AND serviceInfo.specialityId is not null AND serviceInfo.serviceId is not null)
			)
	 
		AND Evn.Person_id = $1
