IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RR_Direction]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RR_Direction]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-08-18
-- Description: Загрузка информации по направлениям в региональный регистр RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RR_Direction]
    @Xml xml,
    @QueueName varchar(100) = 'RDSSync',
    @dateTime datetime = null,
    @User uniqueidentifier = null,
	@Verification bit = 0
AS
BEGIN

    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@OperationType varchar(6)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OwnerID uniqueidentifier
           ,@RrPatient int
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)

    -- Инициализация
    select @dateTime = ISNULL(@dateTime, GETDATE())
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'

    -- Получаем тип операции
    select @OperationType = t.c.value('./TYPE[1]', 'varchar(6)') --VID_I
          ,@OwnerID = t.c.value('./header[1]/ID[1]', 'varchar(36)')
          ,@RrPatient = T.c.value('./RrPatient[1]', 'int')
          ,@ContentEncoding = 'utf-8'
          ,@Type = 'RR_DIRECTION'
    from @Xml.nodes('RR_DIRECTION') T(c)

    -- Generate table #RR_DIRECTION
    select T.c.value('./ID[1]', 'uniqueidentifier')										as [ID]
          ,T.c.value('./LPU[1]', 'varchar(6)')											as [LPU]
          ,convert(datetime, T.c.value('./EnterDate[1]', 'varchar(10)'), 104)			as [EnterDate]
          ,T.c.value('./LastName[1]', 'varchar(40)')									as [LastName] --FAM
          ,T.c.value('./FirstName[1]', 'varchar(40)')									as [FirstName] --IM
          ,T.c.value('./FatherName[1]', 'varchar(40)')									as [FatherName] --OT
          ,convert(datetime, T.c.value('./Birthday[1]', 'varchar(10)'), 104)			as [Birthday]
          ,T.c.value('./Sex[1]', 'varchar(255)')										as [Sex]
          ,T.c.value('./DocSeries[1]', 'varchar(16)')									as [DocSeries] --SDOC
          ,T.c.value('./DocNumber[1]', 'varchar(16)')									as [DocNumber] --NDOC
          ,T.c.value('./RrPatient[1]', 'int')											as [RrPatient] --ID_PAC
          ,T.c.value('./SNILS[1]', 'varchar(14)')										as [SNILS] --Snils
          ,T.c.value('./ExcludeReason[1]', 'int')										as [ExcludeReason]
		  ,T.c.value('./RrPatient[1]', 'int')											as [RealRrPatient] --REAL_ID_PAC
		  ,T.c.value('./KodAdr[1]', 'varchar(20)')										as [KODADR] --KODADR
          ,T.c.value('./DocType[1]', 'int')												as [DocType]
          ,T.c.value('./BirthSurname[1]', 'varchar(40)')								as [BirthSurname]
          ,T.c.value('./LpuFirst[1]', 'varchar(6)')										as [LpuFirst]
          ,T.c.value('./ChangeType[1]', 'int')											as [ChangeType]
          ,T.c.value('./SOST_R[1]', 'int')												as [SOST_R]
          ,T.c.value('./Kod[1]', 'varchar(32)')											as [Kod]
          ,convert(datetime, T.c.value('./DocIssueDate[1]', 'varchar(10)'), 104)		as [DocIssueDate]
          ,T.c.value('./Enp[1]', 'varchar(16)')											as [Enp]
          ,T.c.value('./DocIssueOrg[1]', 'varchar(256)')								as [DocIssueOrg]
          ,T.c.value('./RealFiasAddress[1]', 'varchar(36)')								as [RealFiasAddress]
          ,T.c.value('./RealFiasHome[1]', 'varchar(36)')								as [RealFiasHome]
          ,T.c.value('./RealCity[1]', 'varchar(100)')									as [RealCity]
          ,T.c.value('./RealCityPrefix[1]', 'varchar(10)')								as [RealCityPrefix]
          ,T.c.value('./RealStreet[1]', 'varchar(100)')									as [RealStreet]
          ,T.c.value('./RealStreetPrefix[1]', 'varchar(10)')							as [RealStreetPrefix]
          ,T.c.value('./RealHouse[1]', 'varchar(100)')									as [RealHouse]
          ,T.c.value('./RealBuild[1]', 'varchar(100)')									as [RealBuild]
          ,T.c.value('./RealStruc[1]', 'varchar(100)')									as [RealStruc]
          ,T.c.value('./RealRoom[1]', 'varchar(100)')									as [RealRoom]
          ,convert(datetime, T.c.value('./RealLiveStartDate[1]', 'varchar(10)'), 104)	as [RealLiveStartDate]
          ,convert(datetime, T.c.value('./RealLiveEndDate[1]', 'varchar(10)'), 104)		as [RealLiveEndDate]
          ,T.c.value('./LegalFiasAddress[1]', 'varchar(36)')							as [LegalFiasAddress]
          ,T.c.value('./LegalFiasHome[1]', 'varchar(36)')								as [LegalFiasHome]
          ,T.c.value('./LegalCity[1]', 'varchar(100)')									as [LegalCity]
          ,T.c.value('./LegalCityPrefix[1]', 'varchar(10)')								as [LegalCityPrefix]
          ,T.c.value('./LegalStreet[1]', 'varchar(100)')								as [LegalStreet]
          ,T.c.value('./LegalStreetPrefix[1]', 'varchar(10)')							as [LegalStreetPrefix]
          ,T.c.value('./LegalHouse[1]', 'varchar(100)')									as [LegalHouse]
          ,T.c.value('./LegalBuild[1]', 'varchar(100)')									as [LegalBuild]
          ,T.c.value('./LegalStruc[1]', 'varchar(100)')									as [LegalStruc]
          ,T.c.value('./LegalRoom[1]', 'varchar(100)')									as [LegalRoom]
          ,convert(datetime, T.c.value('./LegalLiveStartDate[1]', 'varchar(10)'), 104)  as [LegalLiveStartDate]
          ,convert(datetime, T.c.value('./LegalLiveEndDate[1]', 'varchar(10)'), 104)	as [LegalLiveEndDate]
          ,T.c.value('./CitizenShip[1]', 'varchar(3)')									as [CitizenShip]
        INTO #RR_DIRECTION
    from @Xml.nodes('RR_DIRECTION') T(c)

	
    -- Generate table #RR_DIRECTION_BENEFIT
    select
		   T.c.value('./DirectionID[1]', 'uniqueidentifier')                     as [RrDirection]
		  ,convert(datetime, T.c.value('./DiagDate[1]', 'varchar(10)'), 104)     as [DiagDate]
          ,T.c.value('./Document[1]', 'varchar(16)')                             as [Document]
          ,convert(datetime, T.c.value('./DocumentDate[1]', 'varchar(10)'), 104) as [DocumentDate]
          ,T.c.value('./Dop[1]', 'varchar(1)')                                   as [Dop]
          ,T.c.value('./Mkb[1]', 'varchar(8)')                                   as [Mkb]
          ,T.c.value('./BenefitCode[1]', 'varchar(255)')                         as [BenefitCode]
          ,T.c.value('./BenefitViews[1]', 'int')                                 as [BenefitViews]
          ,T.c.value('./PostCode[1]', 'varchar(10)')                             as [Post]
          ,T.c.value('./DivisionManager[1]', 'varchar(10)')                      as [DivisionManager]
          ,T.c.value('./ChangeType[1]', 'int')								     as [ChangeType]
		  ,0																	 as [FederalAndNos]
        INTO #RR_DIRECTION_BENEFIT
    from @Xml.nodes('RR_DIRECTION/RR_DIRECTION_BENEFIT') T(c)


    -- get RrPatientID
    -- Поиск по СНИЛС
    update rd
    set 
		rd.RealRrPatient = rp.RrPatientID
       ,@RrPatient = rp.RrPatientID
    from #RR_DIRECTION rd
        join [RDS].T_RR_PATIENT rp on rp.SNILS = rd.SNILS
    where 
		rd.RealRrPatient is null
        and rd.SNILS is not null
        and rp.SNILS <> ''
        and rp.SNILS <> '              '
		and (rp.DateEnd is null or rp.DateEnd >= GETDATE())

    -- Поиск по серии и номеру документа
    update rd
    set 
		rd.RealRrPatient = rp.RrPatientID
       ,@RrPatient = rp.RrPatientID
    from #RR_DIRECTION rd
        join [RDS].T_RR_PATIENT rp on rp.DocSeries = rd.DocSeries and rp.DocNumber = rd.DocNumber
    where 
		rd.RealRrPatient is null
        and rd.DocSeries is not null
        and rd.DocNumber is not null
        and rd.DocSeries <> ''
        and rd.DocNumber <> ''
		and (rp.DateEnd is null or rp.DateEnd >= GETDATE())
        and rp.Birthday = rd.Birthday

    -- Поиск по ФИО + ДР
    update rd
    set 
		rd.RealRrPatient = rp.RrPatientID
       ,@RrPatient = rp.RrPatientID
    from #RR_DIRECTION rd
        join [RDS].T_RR_PATIENT rp on rp.LastName = rd.LastName and rp.FirstName = rd.FirstName and (rp.FatherName = rd.FatherName or (rp.FatherName is null and rd.FatherName is null)) and rp.Birthday = rd.Birthday
    where 
		rd.RealRrPatient is null
		and (rp.DateEnd is null or rp.DateEnd >= GETDATE())

        
	--проверка льгот
	if (exists(select 1
	            from #RR_DIRECTION_BENEFIT rdb
	                join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = rdb.BenefitViews
	                join [RDS].T_BENEFITS b on b.BenefitCode = rdb.BenefitCode  and b.BenefitViews = e.EnumID
	                join #RR_DIRECTION d on d.ID = rdb.[RrDirection]
	            where rdb.ChangeType = 1 and 
	            exists (select * 
	            			from [RDS].[T_RR_PATIENT_BENEFIT] db 
	            				    join RDS.T_RR_PATIENT rrp on rrp.Id = db.RrPatient
	            				where rrp.RrPatientID = d.RrPatient 
	            				and db.Benefits = b.BenefitsID
	            				and (db.EndDate is null or db.EndDate > GETDATE()) 
	                            and (b.Private = 0 or (b.Private = 1 and (db.LPU_INF = case when Len(d.LPU) > 5 then SUBSTRING(d.LPU, 3, 4) else d.LPU end)))) ))
	begin
		RAISERROR('Льгота уже добавлена!', 16, 10) 	
        return
	end
	
	-- Проверка на ошибки в направлении
	if (@Verification = 1)
	begin
		--заполняем только если код льготы 26(трансплантация) и человек есть в ВЗН - обход фед регистра
		declare @VZN bit = 0
		if(exists(select * 
					from RDS.T_NOSOLOGY n
					join RDS.T_NOSOLOGY_BENEFITS nb on nb.Nosology = n.NosologyID
					join RDS.T_BENEFITS b on b.BenefitsID = nb.Benefits
					join #RR_DIRECTION d on d.SNILS = n.SNILS
					where b.BenefitCode = '3'
					and d.SNILS is not null and d.SNILS <> '' and d.SNILS <> '              ' and n.SNILS is not null and n.SNILS <> '' and n.SNILS <> '              '
					and n.Birthday = d.Birthday and n.LastName = d.LastName and n.FirstName = d.FirstName and ((d.FatherName is null and n.FatherName is null) or (d.FatherName = n.FatherName))))
		begin
			set @VZN = 1
		end

		declare @Error varchar(max) = null
		--проверка направлений
		update rd
			set @Error = IsNull(@Error, ' ') + ' СНИЛС обязательное поле для пациентов старше 2х месяцев!' --Dir
		from #RR_DIRECTION_BENEFIT rdb
			join #RR_DIRECTION rd on rd.ID = rdb.[RrDirection]
			join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = rdb.BenefitViews
			join [RDS].T_BENEFITS b on b.BenefitCode = rdb.BenefitCode  and b.BenefitViews = e.EnumID
		where (rd.Snils is null or rd.Snils = '')
			and DateAdd(month,2, rd.[Birthday]) <= GETDATE() 
			and rd.ChangeType in (1,2) 
			and b.BenefitCode <> '555' 
		
		update rdb
			set rdb.FederalAndNos = (case when @VZN = 1 and rdb.BenefitCode = '26' then 1 else 0 end), @Error = case when (@VZN = 1 and rdb.BenefitCode = '26') then IsNull(@Error, null) else IsNull(@Error, ' ') + ' Пациент присутствует в федеральном регистре!' end--Dir
		from #RR_DIRECTION_BENEFIT rdb
			join #RR_DIRECTION rd on rd.ID = rdb.[RrDirection]
		where rdb.ChangeType = 1  and  exists 
			(
				select * from RDS.T_FR_PATIENT fr 
							cross apply (select top 1 * from [RDS].T_FR_RECORD rec 
														where rec.Snils = fr.Snils 
														order by rec.Version desc) t
				where 
					fr.Birthday = rd.[Birthday] 
					and (fr.DE_EDV > Getdate() or fr.DE_EDV is null or fr.DE_EDV = '') 
					and t.Deleted = 0 
					and (
						(rd.Snils is not null and fr.Snils = rd.Snils) 
						or (rd.[DocSeries] is not null and rd.[DocNumber] is not null and fr.DocSeries = rd.[DocSeries] and fr.DocNumber = rd.[DocNumber]) 
						or (fr.LastName = rd.[LastName] and fr.FirstName = rd.[FirstName] and (fr.FatherName = rd.[FatherName] or (fr.FatherName is null and rd.[FirstName] is null)) and ((rd.[SNILS] is null and  fr.Snils is null) or fr.Snils = rd.[SNILS]))
						)
						)

		update rd
			set @Error = IsNull(@Error, ' ') + ' Не указан ID пациента(PAC_ID)!' -- !insert--Dir
		from #RR_DIRECTION rd
		where 
			(rd.RrPatient is null or rd.RrPatient = 0) 
			and rd.ChangeType <> 1

		update rd
			set @Error = IsNull(@Error, ' ') + ' PAC_ID должен быть пустой!' -- !insert--Dir
		from #RR_DIRECTION rd
		where 
			(rd.RrPatient is not null and rd.RrPatient <> 0) 
			and rd.ChangeType = 1
		
		-- инсерт пациент пустой код, елсе пац код !нул--Dir
		update rd
			set @Error = IsNull(@Error, ' ') + Isnull(case
														when ((rd.RealRrPatient is not null and rd.RrPatient <> rd.RealRrPatient)) 
														then ' Пациент не найден! ID пациента(PAC_ID): ' + ISNULL(Cast(rd.RrPatient as varchar(20)), '') 
													  end, '') -- !insert
		from #RR_DIRECTION rd
		where 
			rd.RealRrPatient is null 
			and rd.ChangeType <> 1
		
		update rd -- update / delete--Dir
			set @Error = IsNull(@Error, ' ') + ' Пациент не найден в базе!'
		from #RR_DIRECTION rd
		where 
			rd.RealRrPatient is null 
			and rd.ChangeType <> 1
                        		
		update rdb
			set @Error = IsNull(@Error, ' ') + ' Льгота от указаной МО не найдена !' -- update/delete benefit
		from #RR_DIRECTION_BENEFIT rdb
			join #RR_DIRECTION d on d.ID = rdb.[RrDirection]
			join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = rdb.BenefitViews
			join [RDS].T_BENEFITS b on b.BenefitCode = rdb.BenefitCode  and b.BenefitViews = e.EnumID
		where rdb.ChangeType <> 1 
			and not exists (select * from [RDS].[T_RR_PATIENT_BENEFIT] pb 
								join RDS.T_RR_PATIENT rrp on rrp.Id = pb.RrPatient
							where 
								b.BenefitsID = pb.Benefits 
								and rrp.RrPatientID = d.RrPatient
								and pb.LPU_INF =  case 
													when Len(d.LPU) > 5 
													then SUBSTRING(d.LPU, 3, 4) 
													else d.LPU 
												end)

		
		update rdb
			set @Error = IsNull(@Error, ' ') + ' Льгота 95 не должна включаться в Региональный Регистр !'
		from #RR_DIRECTION_BENEFIT rdb
		where  rdb.BenefitCode = '95'

		
		update rdb
			set @Error = IsNull(@Error, ' ') + ' Для человека старше 3х лет льгота 57(ребенок первых 3х лет жизни) не действует !'
		from #RR_DIRECTION_BENEFIT rdb
			join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = rdb.BenefitViews
			join [RDS].T_BENEFITS b on b.BenefitCode = rdb.BenefitCode  and b.BenefitViews = e.EnumID
			join #RR_DIRECTION d on d.ID = rdb.[RrDirection]
		where  
			(b.BenefitCode = '57' or b.BenefitCode = '057') 
			and e.Code = 2 
			and DateAdd(YEAR, 3, d.Birthday) <= GETDATE() 
			and d.ChangeType in (1,2)

		if(exists (select top 1 * from RDS.T_SICKNESS_GROUP s
									join [RDS].T_BENEFITS b on b.BenefitsID = s.Benefits
									join [VCLib].T_ENUMS e on e.EnumID = b.BenefitViews
									join #RR_DIRECTION_BENEFIT rdb on rdb.BenefitCode = b.BenefitCode and e.code = rdb.BenefitViews))		
		begin
		
			update rdb
				set @Error = IsNull(@Error, ' ') + ' Не правильный код МКБ для указанной льготы!'
			from #RR_DIRECTION_BENEFIT rdb
				join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = rdb.BenefitViews
				join [RDS].T_BENEFITS b on b.BenefitCode = rdb.BenefitCode  and b.BenefitViews = e.EnumID
			where  
				e.Code = 3
				and not exists (select * from RDS.T_SICKNESS_GROUP s 
											where 
												s.Benefits = b.BenefitsID
												and s.MKBK >= rdb.MKB
												and s.MKBN <= rdb.MKB)

		end

		if (@Error is not null)
		begin
			RAISERROR(@Error, 16, 10) 		
            return
		end
	end

    -- Генерируем новый идентификатор
	
	IF (@RrPatient is null)
	BEGIN
		if(exists(select * from #RR_DIRECTION where ChangeType = 3))
		begin
			raiserror ('Пациент не найден в регистре!', 16, 10)
            return
		end

	    exec @RrPatient = [dbo].sp_GenerateIdForEntity 'RR_PATIENT', 'PrimaryKey', 1
	    update rd
			set rd.RrPatient = @RrPatient
	    from #RR_DIRECTION rd

		IF(@RrPatient = 0)
        begin
		    raiserror ('Не верно сгенерирован идентификатор льготника в регистре!', 16, 10)
            return
        end
	END
	else
	begin
		update rd
		    set rd.RrPatient = @RrPatient
		from #RR_DIRECTION rd
	end
    
	-- update [RDS].T_RR_PATIENT
    update rp
    set 
		 rp.LastName			= p.LastName
		,rp.FirstName			= p.FirstName
		,rp.FatherName			= p.FatherName
		,rp.Birthday			= p.Birthday
		,rp.Sex					= eSex.EnumID
		,rp.LastEditDate		= @dateTime
		,rp.LastEditLpu			= p.LPU
		,rp.SNILS				= p.SNILS
		,rp.DocSeries			= p.DocSeries
		,rp.DocNumber			= p.DocNumber
		,rp.LPU_INF				= case when Len(p.LPU) > 5 then SUBSTRING(p.LPU, 3, 4) else p.LPU end
		,rp.KODADR				= p.KODADR
		,rp.BirthSurname		= p.BirthSurname
		,rp.LpuMainFirst		= p.LpuFirst
		,rp.Kod					= p.Kod
		,rp.TIPD				= p.DocType
		,rp.SOST_R				= p.SOST_R
		,rp.DocIssueDate		= p.DocIssueDate
		,rp.DocIssueOrg			= p.DocIssueOrg
		,rp.Enp					= p.Enp
		,rp.RealFiasAddress		= p.RealFiasAddress
		,rp.RealFiasHome		= p.RealFiasHome
		,rp.RealCity			= p.RealCity
		,rp.RealCityPrefix		= p.RealCityPrefix
		,rp.RealStreet			= p.RealStreet
		,rp.RealStreetPrefix	= p.RealStreetPrefix
		,rp.RealHouse			= p.RealHouse
		,rp.RealBuild			= p.RealBuild
		,rp.RealStruc			= p.RealStruc
		,rp.RealRoom			= p.RealRoom
		,rp.RealLiveStartDate	= p.RealLiveStartDate
		,rp.RealLiveEndDate		= p.RealLiveEndDate
		,rp.LegalFiasAddress	= p.LegalFiasAddress
		,rp.LegalFiasHome		= p.LegalFiasHome
		,rp.LegalCity			= p.LegalCity
		,rp.LegalCityPrefix		= p.LegalCityPrefix
		,rp.LegalStreet			= p.LegalStreet
		,rp.LegalStreetPrefix	= p.LegalStreetPrefix
		,rp.LegalHouse			= p.LegalHouse
		,rp.LegalBuild			= p.LegalBuild
		,rp.LegalStruc			= p.LegalStruc
		,rp.LegalRoom			= p.LegalRoom
		,rp.LegalLiveStartDate	= p.LegalLiveStartDate
		,rp.LegalLiveEndDate	= p.LegalLiveEndDate
		,rp.CitizenShip			= cs.EhrRusID
		,rp.EhrRus5o5e88vhc6	= dul.EhrRus5o5e88vhc6
    from [RDS].T_RR_PATIENT rp
        join #RR_DIRECTION p on p.RealRrPatient = rp.RrPatientID
        join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = p.Sex
        join [VCLib].T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = p.DocType
		left join EHR.T_EHR_RUS_O00015 cs on cs.CODE = p.CitizenShip
		left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
    where p.ChangeType in (1,2)

	update rp
    set rp.LastEditDate		= @dateTime
       ,rp.LastEditLpu		= p.LPU
       ,rp.DateEnd			= p.EnterDate
	   ,rp.ExcludeReason	= p.ExcludeReason
    from [RDS].T_RR_PATIENT rp
        join #RR_DIRECTION p on p.RealRrPatient = rp.RrPatientID
    where p.ChangeType = 3
	and not exists(select * 
					from RDS.T_RR_PATIENT_BENEFIT rpb
					join RDS.T_BENEFITS b on b.BenefitsID = rpb.Benefits
					where (rpb.EndDate is null or rpb.EndDate > p.EnterDate) and rpb.RrPatient = rp.Id and b.Private = 1 and rpb.LPU_INF != case when Len(p.LPU) > 5 then SUBSTRING(p.LPU, 3, 4) else p.LPU end)

    -- insert [RDS].T_RR_PATIENT
    INSERT INTO [RDS].[T_RR_PATIENT]
               (
				 [RrPatientID] 
				,[FatherName] 
				,[FirstName] 
				,[LastName] 
				,[Birthday] 
				,[Sex] 
				,[SNILS] 
				,[DateBegin] 
				,[DateEnd] 
				,[LastEditDate] 
				,[LastEditLpu] 
				,[ExcludeReason]
				,[DocSeries] 
				,[DocNumber]
				,LPU_INF,KODADR
				,[TIPD]
				,[BirthSurname]
				,[LpuMainFirst]
				,[Kod]
				,[SOST_R]
				,[id]
				,[DocIssueDate]
				,[DocIssueOrg]
				,[Enp]
				,[RealFiasAddress]
				,[RealFiasHome]
				,[RealCity]
				,[RealCityPrefix]
				,[RealStreet]
				,[RealStreetPrefix]
				,[RealHouse]
				,[RealBuild]
				,[RealStruc]
				,[RealRoom]
				,[RealLiveStartDate]
				,[RealLiveEndDate]
				,[LegalFiasAddress]
				,[LegalFiasHome]
				,[LegalCity]
				,[LegalCityPrefix]
				,[LegalStreet]
				,[LegalStreetPrefix]
				,[LegalHouse]
				,[LegalBuild]
				,[LegalStruc]
				,[LegalRoom]
				,[LegalLiveStartDate]
				,[LegalLiveEndDate]
				,[CitizenShip]
				,[EhrRus5o5e88vhc6]
				)
		SELECT
               pd.RrPatient--<RrPatientID, int,>
              ,pd.FatherName--<FatherName, varchar(40),>
              ,pd.FirstName--<FirstName, varchar(40),>
              ,pd.LastName--<LastName, varchar(40),>
              ,pd.Birthday--<Birthday, datetime,>
              ,eSex.EnumID--<Sex, int,>
              ,pd.SNILS--<SNILS, char(14),>
              ,pd.[EnterDate]--<DateBegin, datetime,>
              ,null--<DateEnd, datetime,>
              ,@dateTime--<LastEditDate, datetime,>
              ,pd.LPU--<LastEditLpu, varchar(6),>
              ,null--<ExcludeReason, int,>)
              ,pd.DocSeries--<DocSeries, varchar(8),>
              ,pd.DocNumber--<DocNumber, varchar(8),>)
			  ,case when Len(pd.LPU) > 5 then SUBSTRING(pd.LPU, 3, 4) else pd.LPU end
			  ,pd.KODADR
			  ,pd.DocType
			  ,pd.BirthSurname
			  ,pd.LpuFirst
			  ,pd.kod
			  ,pd.SOST_R
			  ,newid()
			  ,pd.DocIssueDate
			  ,pd.DocIssueOrg
			  ,pd.Enp
			  ,RealFiasAddress
			  ,RealFiasHome
			  ,RealCity
			  ,RealCityPrefix
			  ,RealStreet
			  ,RealStreetPrefix
			  ,RealHouse
			  ,RealBuild
			  ,RealStruc
			  ,RealRoom
			  ,RealLiveStartDate
			  ,RealLiveEndDate
			  ,LegalFiasAddress
			  ,LegalFiasHome
			  ,LegalCity
			  ,LegalCityPrefix
			  ,LegalStreet
			  ,LegalStreetPrefix
			  ,LegalHouse
			  ,LegalBuild
			  ,LegalStruc
			  ,LegalRoom
			  ,LegalLiveStartDate
			  ,LegalLiveEndDate
			  ,cs.EhrRusID
			  ,dul.EhrRus5o5e88vhc6
    from #RR_DIRECTION pd
        left join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = pd.Sex
        left join [VCLib].T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = pd.DocType
		left join EHR.T_EHR_RUS_O00015 cs on cs.CODE = pd.CitizenShip
		left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
    where 
		pd.ChangeType = 1
		and not exists
        (
            select *
            from [RDS].T_RR_PATIENT rp
            where rp.RrPatientID = pd.RealRrPatient
        )

    -- insert [RDS].T_RR_DIRECTION
    INSERT INTO [RDS].[T_RR_DIRECTION]
               (
				 [LastName] 
				,[FirstName] 
				,[FatherName] 
				,[Birthday] 
				,[Sex] 
				,[RrDirectionID] 
				,[RrPatient] 
				,[ExcludeReason] 
				,[EnterDate] 
				,[Date] 
				,[Lpu] 
				,[ChangeType] 
				,[Snils] 
				,[DocSeries] 
				,[DocNumber] 
				,LPU_INF
				,KODADR
				,TIPD
				,BirthSurname
				,LpuMainFirst
				,Kod
				,SOST_R
				,DocIssueDate
				,DocIssueOrg
				,Enp
				,RealFiasAddress
				,RealFiasHome
				,RealCity
				,RealCityPrefix
				,RealStreet
				,RealStreetPrefix
				,RealHouse
				,RealBuild
				,RealStruc
				,RealRoom
				,RealLiveStartDate
				,RealLiveEndDate
				,LegalFiasAddress
				,LegalFiasHome
				,LegalCity
				,LegalCityPrefix
				,LegalStreet
				,LegalStreetPrefix
				,LegalHouse
				,LegalBuild
				,LegalStruc
				,LegalRoom
				,LegalLiveStartDate
				,LegalLiveEndDate
				,CitizenShip
				)
		SELECT
				 pd.LastName--<LastName, varchar(40),>
				,pd.FirstName--<FirstName, varchar(40),>
				,pd.FatherName--<FatherName, varchar(40),>
				,pd.Birthday--<Birthday, datetime,>
				,eSex.EnumID--<Sex, int,>
				,pd.ID--<RrDirectionID, uniqueidentifier,>
				,rrp.Id
				,pd.ExcludeReason--<ExcludeReason, int,>
				,pd.EnterDate--<EnterDate, datetime,>
				,pd.[EnterDate]--<Date, datetime,>
				,pd.LPU--<Lpu, varchar(6),>
				,pd.ChangeType--<ChangeType, int,>)
				,pd.SNILS--<Snils, char(14),>
				,pd.DocSeries--<DocSeries, varchar(8),>
				,pd.DocNumber--<DocNumber, varchar(8),>)
				,case when Len(pd.LPU) > 5 then SUBSTRING(pd.LPU, 3, 4) else pd.LPU end
				,pd.KODADR
				,pd.DocType
				,pd.BirthSurname
				,pd.LpuFirst
				,pd.Kod
				,pd.SOST_R
				,pd.DocIssueDate
				,pd.DocIssueOrg
				,pd.Enp
				,pd.RealFiasAddress
				,pd.RealFiasHome
				,pd.RealCity
				,pd.RealCityPrefix
				,pd.RealStreet
				,pd.RealStreetPrefix
				,pd.RealHouse
				,pd.RealBuild
				,pd.RealStruc
				,pd.RealRoom
				,pd.RealLiveStartDate
				,pd.RealLiveEndDate
				,pd.LegalFiasAddress
				,pd.LegalFiasHome
				,pd.LegalCity
				,pd.LegalCityPrefix
				,pd.LegalStreet
				,pd.LegalStreetPrefix
				,pd.LegalHouse
				,pd.LegalBuild
				,pd.LegalStruc
				,pd.LegalRoom
				,pd.LegalLiveStartDate
				,pd.LegalLiveEndDate
				,cs.EhrRusID
    from #RR_DIRECTION pd
        left join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = pd.Sex
		left join RDS.T_RR_PATIENT rrp on rrp.RrPatientID = @RrPatient
		left join EHR.T_EHR_RUS_O00015 cs on cs.CODE = pd.CitizenShip

	-- insert [RDS].T_RR_DIRECTION_BENEFIT
    INSERT INTO [RDS].[T_RR_DIRECTION_BENEFIT]
               (
				[RrDirectionBenefitID] 
				,[RrDirection] 
				,[DiagDate] 
				,[Document] 
				,[DocumentDate] 
				,[Dop] 
				,[Mkb] 
				,[Benefits] 
				,[ChangeType]
				,Post
				,DivisionManager
				)
    SELECT
               newid()--<RrDirectionBenefitID, uniqueidentifier,>
               ,rdb.RrDirection--<RrDirection, uniqueidentifier,>
               ,rdb.DiagDate--<DiagDate, datetime,>
               ,rdb.Document--<Document, varchar(16),>
               ,rdb.DocumentDate--<DocumentDate, datetime,>
               ,rdb.Dop--<Dop, varchar(1),>
               ,rdb.Mkb--<Mkb, varchar(8),>
               ,b.BenefitsID--<Benefits, uniqueidentifier,>
               ,rdb.ChangeType--<ChangeType, int,>)
			   ,rdb.Post
			   ,rdb.DivisionManager
    from #RR_DIRECTION_BENEFIT rdb
		left join #RR_DIRECTION d on d.ID = rdb.[RrDirection] 
		left join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = rdb.BenefitViews
		left join [RDS].T_BENEFITS b on b.BenefitCode = rdb.BenefitCode  and b.BenefitViews = e.EnumID

	--update [RDS].T_RR_PATIENT_BENEFIT
    update rpb
    set rpb.EndDate =case 
						  when (@Verification = 1 and DateAdd(month, 2, d.Birthday) > GETDATE() and (rrp.Snils is null or rrp.Snils = '')) then DATEADD(day,-1,DATEADD(MONTH, 2, d.Birthday)) 
						  when (@Verification = 1 and b.BenefitCode = '57' and e.Code = 2 and rrp.Snils is not null and rrp.Snils <> '') then DATEADD(day,-1,DATEADD(year, 3, d.Birthday)) 
						  when (b.Duration is not null and b.PeriodOfAct is not null and b.CloseType is not null) then (case when b.CloseType = 0 and b.PeriodOfAct = 0 then DATEADD(DAY,		b.Duration, d.Birthday)
																															   when b.CloseType = 0 and b.PeriodOfAct = 1 then DATEADD(MONTH,	b.Duration, d.Birthday)
																															   when b.CloseType = 0 and b.PeriodOfAct = 2 then DATEADD(YEAR,	b.Duration, d.Birthday)
																															   when b.CloseType = 1 and b.PeriodOfAct = 0 then DATEADD(DAY,		b.Duration, d.[EnterDate])
																															   when b.CloseType = 1 and b.PeriodOfAct = 1 then DATEADD(MONTH,	b.Duration, d.[EnterDate])
																															   when b.CloseType = 1 and b.PeriodOfAct = 2 then DATEADD(YEAR,	b.Duration, d.[EnterDate])
																															  else null 
																														 end)
                          else null 
					 end--<EndDate, datetime,>
       ,rpb.LastEditDate = @dateTime
       ,rpb.LastEditLpu = d.LPU
    from #RR_DIRECTION_BENEFIT rdb
		join #RR_DIRECTION d on d.ID = rdb.[RrDirection] 
		join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = rdb.BenefitViews
		join [RDS].T_BENEFITS b on b.BenefitCode = rdb.BenefitCode  and b.BenefitViews = e.EnumID
		join RDS.T_RR_PATIENT rrp on rrp.RrPatientID = d.RrPatient
        join [RDS].T_RR_PATIENT_BENEFIT rpb on rpb.RrPatient = rrp.id and rpb.Benefits = b.BenefitsID
    where 
		rdb.ChangeType = 1 
		and (rpb.EndDate is null or rpb.EndDate > d.EnterDate) 
		and (b.Private = 0 or (b.Private = 1 and (rpb.LPU_INF = case when Len(d.LPU) > 5 then SUBSTRING(d.LPU, 3, 4) else d.LPU end))) 

    -- insert [RDS].T_RR_PATIENT_BENEFIT
    INSERT INTO [RDS].[T_RR_PATIENT_BENEFIT]
               (
			     [RrPatientBenefitID] 
				,[DiagDate] 
				,[Document] 
				,[DocumentDate] 
				,[Dop] 
				,[Mkb] 
				,[Benefits] 
				,[RrPatient] 
				,[BeginDate] 
				,[EndDate] 
				,[LastEditDate] 
				,[LastEditLpu]
				,LPU_INF
				,Post
				,DivisionManager
				)
    SELECT
               newid()--<RrPatientBenefitID, uniqueidentifier,>
               ,rdb.DiagDate--<DiagDate, datetime,>
               ,rdb.Document--<Document, varchar(16),>
               ,rdb.DocumentDate--<DocumentDate, datetime,>
               ,rdb.Dop--<Dop, varchar(1),>
               ,rdb.Mkb--<Mkb, varchar(8),>
               ,b.BenefitsID--<Benefits, uniqueidentifier,>
               ,rrp.id
               ,d.[EnterDate]--<BeginDate, datetime,>
               ,case 
							when (@Verification = 1 and rdb.FederalAndNos = 1) then  DATEADD(DAY, 7, d.[EnterDate])
							when (@Verification = 1 and DateAdd(month, 2, d.Birthday) > GETDATE() and (rrp.Snils is null or rrp.Snils = '')) then DATEADD(day,-1,DATEADD(MONTH, 2, d.Birthday)) 
							when (@Verification = 1 and b.BenefitCode = '57' and e.Code = 2 and rrp.Snils is not null and rrp.Snils <> '') then DATEADD(day,-1,DATEADD(year, 3, d.Birthday)) 
							when (b.Duration is not null and b.PeriodOfAct is not null and b.CloseType is not null) then (case when b.CloseType = 0 and b.PeriodOfAct = 0 then DATEADD(DAY,		b.Duration, d.Birthday)
																															   when b.CloseType = 0 and b.PeriodOfAct = 1 then DATEADD(MONTH,	b.Duration, d.Birthday)
																															   when b.CloseType = 0 and b.PeriodOfAct = 2 then DATEADD(YEAR,	b.Duration, d.Birthday)
																															   when b.CloseType = 1 and b.PeriodOfAct = 0 then DATEADD(DAY,		b.Duration, d.[EnterDate])
																															   when b.CloseType = 1 and b.PeriodOfAct = 1 then DATEADD(MONTH,	b.Duration, d.[EnterDate])
																															   when b.CloseType = 1 and b.PeriodOfAct = 2 then DATEADD(YEAR,	b.Duration, d.[EnterDate])
																															  else null end)
                            else null
				end--<EndDate, datetime,>
               ,@dateTime--<LastEditDate, datetime,>
               ,d.LPU--<LastEditLpu, varchar(6),>)
			   ,case when Len(d.LPU) > 5 then SUBSTRING(d.LPU, 3, 4) else d.LPU end
			   ,rdb.Post
			   ,rdb.DivisionManager
    from #RR_DIRECTION_BENEFIT rdb
		left join #RR_DIRECTION d on d.ID = rdb.[RrDirection] 
		left join RDS.T_RR_PATIENT rrp on rrp.RrPatientID = d.RrPatient
		left join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = rdb.BenefitViews
		left join [RDS].T_BENEFITS b on b.BenefitCode = rdb.BenefitCode  and b.BenefitViews = e.EnumID
    where 
		rdb.ChangeType = 1
		and not exists
        (
            select *
            from [RDS].T_RR_PATIENT_BENEFIT rpb
            where 
				rpb.RrPatient = rrp.id
                and  rpb.Benefits = b.BenefitsID
				and (b.Private = 0 or (b.Private = 1 and (rpb.LPU_INF = case when Len(d.LPU) > 5 then SUBSTRING(d.LPU, 3, 4) else d.LPU end))) 
				and (rpb.EndDate is null or rpb.EndDate >= GetDate())
        )

    -- close [RDS].T_RR_PATIENT_BENEFIT
    update rpb
    set rpb.EndDate = d.EnterDate
       ,rpb.LastEditDate = @dateTime
       ,rpb.LastEditLpu = d.LPU
    from #RR_DIRECTION_BENEFIT rdb
		join #RR_DIRECTION d on d.ID = rdb.[RrDirection] 
		join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = rdb.BenefitViews
		join [RDS].T_BENEFITS b on b.BenefitCode = rdb.BenefitCode  and b.BenefitViews = e.EnumID
		join RDS.T_RR_PATIENT rrp on rrp.RrPatientID = d.RrPatient
        join [RDS].T_RR_PATIENT_BENEFIT rpb on rpb.RrPatient = rrp.id and rpb.Benefits = b.BenefitsID
    where 
		rdb.ChangeType = 3 
		and (rpb.EndDate is null or rpb.EndDate > d.EnterDate) 
		and (b.Private = 0 or (b.Private = 1 and (rpb.LPU_INF = case when Len(d.LPU) > 5 then SUBSTRING(d.LPU, 3, 4) else d.LPU end))) 
	
    -- close [RDS].T_RR_PATIENT_BENEFIT
    update rpb
    set rpb.EndDate = rrd.EnterDate
       ,rpb.LastEditDate = @dateTime
       ,rpb.LastEditLpu = rrd.LPU
    from #RR_DIRECTION rrd
		join RDS.T_RR_PATIENT rrp on rrp.RrPatientID = rrd.RealRrPatient
        join [RDS].T_RR_PATIENT_BENEFIT rpb on rpb.RrPatient = rrp.id
		join RDS.T_BENEFITS b on b.BenefitsID = rpb.Benefits
    where 
		rrd.ChangeType = 3 
		and (rpb.EndDate is null or rpb.EndDate > rrd.EnterDate)
		and (b.Private = 0 or (b.Private = 1 and (rpb.LPU_INF = case when Len(rrd.LPU) > 5 then SUBSTRING(rrd.LPU, 3, 4) else rrd.LPU end))) 

	update rr
	set rr.DateEnd = t.EndDate, 
    rr.LastEditDate = getdate(), 
		rr.ExcludeReason = (case when t.EndDate is not null then rr.ExcludeReason else null end)
	from RDS.T_RR_PATIENT rr
		cross apply
		(
			select top 1 rrb.EndDate 
			from RDS.T_RR_PATIENT_BENEFIT rrb
			where rrb.RrPatient = rr.id
			order by case when rrb.EndDate is null then '99990101' else rrb.EndDate end  desc
		) t
		join #RR_DIRECTION d on d.RrPatient = rr.RrPatientID
	
	-- собираем ответ в АКУЗ, RR_DIRECTION
    SELECT @Result = 
    ( 
        select @OperationType as 'TYPE'
              ,rd.ID          as 'ID'
              ,rd.RrPatient   as 'RrPatient'
		from #RR_DIRECTION rd
		for xml path(''),
		root('RR_DIRECTION')
    ) 

	-- собираем пакет в апс
	select @Xml = 
	(
			select --@OperationType                                           as 'header/TYPE' ,
						(
								select rp.RrPatientID										as 'RrPatientID'
								      ,rp.SNILS												as 'Snils'
								      ,rp.DocSeries											as 'DocSeries'
								      ,rp.DocNumber											as 'DocNumber'
								      ,rp.LastName											as 'LastName'
								      ,rp.FirstName											as 'FirstName'
								      ,rp.FatherName										as 'FatherName'
								      ,convert(varchar(10), rp.Birthday, 104)				as 'Birthday'
								      ,convert(varchar(10), rp.DateBegin, 104)				as 'DateBegin'
								      ,convert(varchar(10), rp.DateEnd, 104)				as 'DateEnd'
								      ,convert(varchar(10), rp.LastEditDate, 104)			as 'LastEditDate'
								      ,eSex.Code											as 'Sex'
								      ,rp.LastEditLpu										as 'Lpu'
								      ,rp.ExcludeReason										as 'ExcludeReason'
								      ,rp.TIPD												as 'DocType'
								      ,rp.Kod												as 'Kod'
								      ,rp.KodAdr											as 'KodAdr'
								      ,rp.BirthSurname										as 'BirthSurname'
								      ,rp.LpuMainFirst										as 'LpuFirst'
								      ,rp.SOST_R											as 'SOST_R'
									  ,convert(varchar(10), rp.DocIssueDate, 104)			as 'DocIssueDate'
									  ,rp.DocIssueOrg										as 'DocIssueOrg'
									  ,rp.Enp												as 'Enp'
									  ,rp.RealFiasAddress									as 'RealFiasAddress'
									  ,rp.RealFiasHome										as 'RealFiasHome'
									  ,rp.RealCity											as 'RealCity'
									  ,rp.RealCityPrefix									as 'RealCityPrefix'
									  ,rp.RealStreet										as 'RealStreet'
									  ,rp.RealStreetPrefix									as 'RealStreetPrefix'
									  ,rp.RealHouse											as 'RealHouse'
									  ,rp.RealBuild											as 'RealBuild'
									  ,rp.RealStruc											as 'RealStruc'
									  ,rp.RealRoom											as 'RealRoom'
									  ,convert(varchar(10), rp.RealLiveStartDate, 104)		as 'RealLiveStartDate'
									  ,convert(varchar(10), rp.RealLiveEndDate, 104)		as 'RealLiveEndDate'
									  ,rp.LegalFiasAddress									as 'LegalFiasAddress'
									  ,rp.LegalFiasHome										as 'LegalFiasHome'
									  ,rp.LegalCity											as 'LegalCity'
									  ,rp.LegalCityPrefix									as 'LegalCityPrefix'
									  ,rp.LegalStreet										as 'LegalStreet'
									  ,rp.LegalStreetPrefix									as 'LegalStreetPrefix'
									  ,rp.LegalHouse										as 'LegalHouse'
									  ,rp.LegalBuild										as 'LegalBuild'
									  ,rp.LegalStruc										as 'LegalStruc'
									  ,rp.LegalRoom											as 'LegalRoom'
									  ,convert(varchar(10), rp.LegalLiveStartDate, 104)		as 'LegalLiveStartDate'
									  ,convert(varchar(10), rp.LegalLiveEndDate, 104)		as 'LegalLiveEndDate'
									  ,cs.CODE												as 'CitizenShip'
									  ,doc.CodeEHR											as 'EhrRus5o5e88vhc6'
									  ,RRB.CurrVersion										as 'EhrRus5o5e88vhc6Ver'
									  ,(
										      select rp.RrPatientID									as 'RrPatient'
										            ,convert(varchar(10), rpb.DiagDate, 104)		as 'DiagDate'
										            ,rpb.Document									as 'Document'
										            ,convert(varchar(10), rpb.DocumentDate, 104)	as 'DocumentDate'
										            ,rpb.Dop										as 'Dop'
										            ,rpb.Mkb										as 'Mkb'
										            ,convert(varchar(10), rpb.BeginDate, 104)		as 'BeginDate'
										            ,convert(varchar(10), rpb.EndDate, 104)			as 'EndDate'
										            ,b.BenefitCode									as 'BenefitCode'
										            ,e.Code											as 'BenefitViews'
										            ,rpb.LastEditLpu								as 'Lpu'
										            ,convert(varchar(10), rpb.LastEditDate, 104)	as 'LastEditDate'
										            ,rpb.Post										as 'Post'
										            ,rpb.DivisionManager							as 'DivisionManager'
										      from [RDS].T_RR_PATIENT_BENEFIT rpb
										          join [RDS].T_BENEFITS b on b.BenefitsID = rpb.Benefits
												  join [VCLib].T_ENUMS e on e.EnumID = b.BenefitViews
								                where rpb.RrPatient = rp.id
												for xml path('RR_DIRECTION_BENEFIT'),
												TYPE
										)
								from [RDS].T_RR_PATIENT rp
								    join [VCLib].T_ENUMS eSex on eSex.EnumID = rp.Sex
									left join EHR.T_EHR_RUS_O00015 cs on cs.EhrRusID = rp.CitizenShip	
									left join EHR.T_EHR_RUS_5O5E88VHC6 doc on doc.EhrRusID = rp.EhrRus5o5e88vhc6
										LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = doc.EhrRusRefBooks
								where rp.RrPatientID = @RrPatient
								for xml path('RR_DIRECTION'),
								type
							) as 'body'
                for xml path(''),
                root('RR_DIRECTION')
            )


	INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT]
		    ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
		select 
		       NewID()--(<RabbitSendObjectID, uniqueidentifier,>
		      ,s.ServerName--,<QueueName, varchar(255),>
		      ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
		      ,null--,<MessageID, varchar(255),>
		      ,null--,<Headers, varchar(255),>
		      ,@ContentEncoding--,<ContentEncoding, varchar(255),>
		      ,@Type--,<Type, varchar(255),>
		      ,@User--,<UserID, varchar(255),>
		      ,0--,<State, int,>)
		      ,@dateTime--,<CreateDateTime, datetime,>)
		      ,@OwnerID--,<OwnerID, varchar(255),>
		from [RDS].T_SYNC_SERVERS s
		where 
			s.ServerName = @QueueName
		    and s.ServerType = 0 -- AKUZ
		    and @Result is not null
		-- Рассылаем пакет всем подписанным серверам APS
		union all 
		select 
		       NewID()--(<RabbitSendObjectID, uniqueidentifier,>
		      ,s.ServerName--,<QueueName, varchar(255),>
		      ,dbo.ConvertTextToBinaryZip(cast(@Xml as varchar(max)), '')--,<Content, text,>
		      ,null--,<MessageID, varchar(255),>
		      ,null--,<Headers, varchar(255),>
		      ,@ContentEncoding--,<ContentEncoding, varchar(255),>
		      ,@Type--,<Type, varchar(255),>
		      ,@User--,<UserID, varchar(255),>
		      ,0--,<State, int,>)
		      ,@dateTime--,<CreateDateTime, datetime,>)
		      ,@OwnerID--,<OwnerID, varchar(255),>
		from [RDS].T_SYNC_SERVERS s
		where s.ServerType = 1 -- APS

	update e
		set id = (select MAX(RrPatientID) + 1 from RDS.T_RR_PATIENT)
	from dbo.T_ENTITY_IDS e
	where e.EntName in ('RR_PATIENT', 'RDS.RR_PATIENT')
    
	-- Drop tables
    DROP TABLE #RR_DIRECTION
    DROP TABLE #RR_DIRECTION_BENEFIT

END



