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

    SET NOCOUNT ON;

    declare @Result varchar(max)
           ,@OperationType varchar(6)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OwnerID uniqueidentifier
           ,@RegisterID 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 @RegisterID = T.c.value('./RegisterID[1]', 'int')
          ,@ContentEncoding = 'utf-8'
          ,@Type = 'PALLIATIVE_DIRECTION'
    from @Xml.nodes('PALLIATIVE_DIRECTION') T(c)

    -- Generate table #PALLIATIVE_REGISTER
        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]
              ,t.c.value('./FirstName[1]', 'varchar(40)')									as [FirstName]
              ,t.c.value('./FatherName[1]', 'varchar(40)')									as [FatherName]
              ,convert(datetime, t.c.value('./Birthday[1]', 'varchar(10)'), 104)			as [Birthday]
              ,convert(datetime, t.c.value('./Date[1]', 'varchar(10)'), 104)				as [Date]
              ,t.c.value('./Sex[1]', 'int')													as [Sex]
              ,t.c.value('./Kod[1]', 'varchar(32)')											as [Kod]
              ,t.c.value('./DocSeries[1]', 'varchar(16)')									as [DocSeries]
              ,t.c.value('./DocNumber[1]', 'varchar(16)')									as [DocNumber]
              ,t.c.value('./DocType[1]', 'int')												as [DocType]
			  ,t.c.value('./RegisterID[1]', 'int')											as [RegisterID]
              ,t.c.value('./SNILS[1]', 'varchar(14)')										as [Snils]
              ,t.c.value('./ChangeType[1]', 'int')											as [ChangeType]
              ,t.c.value('./ExcludeReason[1]', 'int')										as [ExcludeReason]
			  ,T.c.value('./BirthSurname[1]', 'varchar(40)')								as [BirthSurname]
			  ,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 #PALLIATIVE_DIRECTION
        from @Xml.nodes('PALLIATIVE_DIRECTION') T(c)

		
        -- Generate table #PALLIATIVE_BENEFITS
        select t.c.value('./DirectionID[1]', 'uniqueidentifier')						as [DirectionID]
              ,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('./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(255)')								as [PostCode]
              ,t.c.value('./ChangeType[1]', 'int')										as [ChangeType]
            INTO #PALLIATIVE_BENEFITS
        from @Xml.nodes('PALLIATIVE_DIRECTION/PALLIATIVE_DIRECTION_BENEFIT') T(c)


    -- Генерируем новый идентификатор
    IF (@RegisterID is null)
    BEGIN
        exec @RegisterID = [dbo].sp_GenerateIdForEntity 'PALLIATIVE_REGISTER', 'PrimaryKey', 1
        update rd
			set rd.RegisterID = @RegisterID
        from #PALLIATIVE_DIRECTION rd

		IF(@RegisterID = 0)
		raiserror ('Не верно сгенерирован идентификатор льготника в регистре!', 16, 10)
    END

    update rp
    set rp.LastName				= p.LastName
       ,rp.FirstName			= p.FirstName
       ,rp.FatherName			= p.FatherName
       ,rp.Birthday				= p.Birthday
	   ,rp.Kod					= p.Kod
       ,rp.DocSeries			= p.DocSeries
       ,rp.DocNumber			= p.DocNumber
       ,rp.Sex					= eSex.EnumID
       ,rp.SNILS				= p.SNILS
	   ,rp.DocType				= eDoc.EnumID
       ,rp.LpuMain				= p.LPU
       ,rp.LastEditDate			= @dateTime
	   ,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_PALLIATIVE_REGISTER rp
        join #PALLIATIVE_DIRECTION p on p.RegisterID = rp.PAC_ID
        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 = 2

	update rp
    set rp.LastEditDate = @dateTime
       ,rp.DateEnd = p.EnterDate
	   ,rp.ExcludeReason = p.ExcludeReason
    from [RDS].T_PALLIATIVE_REGISTER rp
        join #PALLIATIVE_DIRECTION p on p.RegisterID = rp.PAC_ID
    where p.ChangeType = 3
    
	update brd
    set brd.EndDate = @dateTime,
    brd.LastEditDate = @dateTime
    from [RDS].[T_PALLIATIVE_REGISTER_DATA] brd
		join [RDS].T_PALLIATIVE_REGISTER rp on rp.PalliativeRegisterId = brd.PalliativeRegister
        join #PALLIATIVE_DIRECTION p on p.RegisterID = rp.PAC_ID
    where 
		p.ChangeType = 3 
		and (brd.EndDate is null or brd.EndDate > @dateTime)

	INSERT INTO [RDS].[T_PALLIATIVE_REGISTER]
	           (
				 [PalliativeRegisterID]
				,[ExcludeReason]
				,[LastEditDate]
				,[LastName]
				,[FirstName]
				,[FatherName]
				,[Birthday]
				,[Kod]
				,[DocSeries]
				,[DocNumber]
				,[PAC_ID]
				,[Sex]
				,[SNILS]
				,[DocType]
				,[LpuMain]
				,[DateBegin]
				,[DateEnd]
				,[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 
		 newid()
		,[ExcludeReason]
		,@dateTime
		,[LastName]
		,[FirstName]
		,[FatherName]
		,[Birthday]
		,[Kod]
		,[DocSeries]
		,[DocNumber]
		,RegisterID
		,eSex.EnumID
		,[SNILS]
		,eDoc.EnumID
		,[Lpu]
		,@dateTime
		,null
		,d.DocIssueDate
		,d.DocIssueOrg
		,d.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 #PALLIATIVE_DIRECTION d
        join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = d.Sex
        join [VCLib].T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = d.DocType
		left join EHR.T_EHR_RUS_O00015 cs on cs.CODE = d.CitizenShip
		left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
	where d.ChangeType = 1


	INSERT INTO [RDS].[T_PALLIATIVE_REGISTER_DATA]
	           ([PalliativeRegisterDataID]
	           ,[PalliativeRegister]
	           ,[DiagDate]
	           ,[Document]
	           ,[DocumentDate]
	           ,[Mkb]
	           ,[Benefits]
			   ,[BeginDate]
               ,[LastEditDate]
			   ,[EndDate])
	 select		
			 newid()
			,r.[PalliativeRegisterId]
			,[DiagDate]
			,[Document]
			,[DocumentDate]
			,[Mkb]
			,b.[BenefitsID]
			,@dateTime
			,@dateTime
			,case 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,>
	from #PALLIATIVE_BENEFITS bd
		join #PALLIATIVE_DIRECTION d on d.ID = bd.DirectionID
		join [RDS].[T_PALLIATIVE_REGISTER] r on r.PAC_ID = @RegisterID
		left join [VCLib].T_ENUMS e on e.EnumTypeID = 190 and e.Code = bd.BenefitViews
		join [RDS].T_BENEFITS b on b.BenefitCode = bd.BenefitCode  and b.BenefitViews = e.EnumID
	where bd.ChangeType = 1

    update rpb
    set rpb.EndDate = @dateTime,
     rpb.LastEditDate = @dateTime
    from #PALLIATIVE_BENEFITS rdb
		join [RDS].[T_PALLIATIVE_REGISTER] r on r.PAC_ID = @RegisterID
		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
        join [RDS].T_PALLIATIVE_REGISTER_DATA rpb on rpb.PalliativeRegister = r.PalliativeRegisterID and rpb.Benefits = b.BenefitsID
    where rdb.ChangeType = 3 and (rpb.EndDate is null or rpb.EndDate > @dateTime)
	
	-- собираем ответ в АКУЗ, PALLIATIVE_DIRECTION
    SELECT @Result = 
    ( 
        select 
			 rd.ID           as 'ID'
			,rd.RegisterID   as 'RegisterID'
		from #PALLIATIVE_DIRECTION rd
		for xml path(''),
		root('PALLIATIVE_REGISTER')
    ) 

	-- собираем пакет в апс
	select @Xml = 
	(
                select @OperationType					as 'header/TYPE'
                      ,(
                            select rp.PAC_ID										as 'RegisterID'
                                  ,rp.LastName										as 'LastName'
                                  ,rp.FirstName										as 'FirstName'
                                  ,rp.FatherName									as 'FatherName'
                                  ,convert(varchar(10), rp.Birthday, 104)			as 'Birthday'
                                  ,rp.Kod											as 'Kod'
                                  ,rp.DocSeries										as 'DocSeries'
                                  ,rp.DocNumber										as 'DocNumber'
                                  ,eSex.Code										as 'Sex'
                                  ,rp.Snils											as 'Snils'
                                  ,convert(varchar(10), rp.DateBegin, 104)			as 'DateBegin'
                                  ,convert(varchar(10), rp.DateEnd, 104)			as 'DateEnd'
                                  ,convert(varchar(10), rp.LastEditDate, 104)		as 'LastEditDate'
                                  ,rp.LpuMain										as 'Lpu'
                                  ,rp.ExcludeReason									as 'ExcludeReason'
                                  ,eDoc.Code										as 'DocType'
								  ,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.PAC_ID                                   as 'RegisterID'
									            ,convert(varchar(10), rpb.DiagDate, 104)     as 'DiagDate'
									            ,rpb.Document                                as 'Document'
									            ,convert(varchar(10), rpb.DocumentDate, 104) as 'DocumentDate'
									            ,rpb.Mkb                                     as 'Mkb'
									            ,b.BenefitCode                               as 'BenefitCode'
									            ,e.Code     								 as 'BenefitViews'
									            ,convert(varchar(10), rpb.BeginDate, 104)    as 'BeginDate'
									            ,convert(varchar(10), rpb.EndDate, 104)      as 'EndDate'
									      from [RDS].[T_PALLIATIVE_REGISTER_DATA] rpb
									          join [RDS].T_BENEFITS b on b.BenefitsID = rpb.Benefits
											  join [VCLib].T_ENUMS e on e.EnumID = b.BenefitViews
											  where rpb.PalliativeRegister = rp.PalliativeRegisterID
										for xml path('BENEFITS'),
										TYPE
									)
                            from [RDS].[T_PALLIATIVE_REGISTER] rp
                                join [VCLib].T_ENUMS eSex on eSex.EnumID = rp.Sex
                                join [VCLib].T_ENUMS eDoc on eDoc.EnumID = rp.DocType
								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.PAC_ID = @RegisterID
                            for xml path('PATIENT'),
                            type
                      ) as 'body'
                for xml path(''),
                root('PALLIATIVE_REGISTER')
            )


	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(PAC_ID) + 1 from RDS.T_PALLIATIVE_REGISTER)
	from dbo.T_ENTITY_IDS e
	where e.EntName in ('PALLIATIVE_REGISTER', 'RDS.PALLIATIVE_REGISTER')
    
	-- Drop tables

    DROP TABLE #PALLIATIVE_DIRECTION
    DROP TABLE #PALLIATIVE_BENEFITS

END



