IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RDS_DLSBXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_DLSBXml]
GO
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:		Vitacore (с)
---- Create date: 2014-02-05
---- Description:	Выгрузка/загрузка связки профилей и отделений в(из) RDS 
---- =============================================
--CREATE PROCEDURE [dbo].[sp_RDS_DLSBXml]
--	@LpuCode varchar(max) = null,
--	@Xml xml = null,
--	@IsNew bit = 1,
--	@ID uniqueidentifier = null,
--	@QueueName varchar(255) = null
--AS
--BEGIN

--	declare @Result varchar(max)
--			,@ContentEncoding varchar(255)
--			,@Type varchar(255)
--			,@OperationType varchar(255)
--			,@OwnerID uniqueidentifier

--SET NOCOUNT ON;
--	IF (@Xml is null)
--	BEGIN
--	--выясняем тип операции
--		IF(@IsNew = 1)
--		BEGIN
--			select @OperationType = 'Insert'
--		END
--		ELSE IF (@IsNew = 0 and exists (select * from [AKUZ].T_DIVISION_LINK_STRUCTURE_BED d  where d.DivisionLinkStructureBedID = @ID))
--		BEGIN
--			select @OperationType = 'Update'
--		END
--		ELSE IF (@IsNew = 0 and not exists (select * from [AKUZ].T_DIVISION_LINK_STRUCTURE_BED d where d.DivisionLinkStructureBedID = @ID))
--		BEGIN
--			select @OperationType = 'Delete'
--				select @Result = (select 
--				@LpuCode				as 'header/LPU'
--				,@OperationType			as 'header/TYPE'
--				,@ID					as 'header/ID'
--				FOR XML PATH(''),
--				ROOT('DIVISION_LINK_STRUCTURE_BED')
--			) 
--		END

--select @ContentEncoding = 'utf-8'
--   ,@Type = 'DIVISION_LINK_STRUCTURE_BED'
--   ,@OwnerID = @ID

--		IF(@OperationType <> 'Delete')
--		BEGIN

--	SELECT @Result = ( 
--		--собираем DIVISION_LINK_STRUCTURE_BED
--		select 
--				@LpuCode			as 'header/LPU'
--				,@OperationType		as 'header/TYPE'
--				,@ID				as 'header/ID'

--				,dlsb.Division		as 'body/DIVISION_ID'
--				,sb.Code			as 'body/V020_STRUCTURE_BED'
--				,v0.Id				as 'body/STRUCTURE_BED'
--			from [AKUZ].T_DIVISION_LINK_STRUCTURE_BED dlsb
--			join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = dlsb.StructureBed
--			join [AKUZ].T_V002_PROFILE v0 on v0.Id = sb.V002Profile
--			where dlsb.DivisionLinkStructureBedID = @ID
--			FOR XML PATH(''),
--		ROOT('DIVISION_LINK_STRUCTURE_BED')
--	) END

--		INSERT INTO [AKUZ].[T_RABBIT_SEND_OBJECT]
--		   ([RabbitSendObjectID]
--		   ,[QueueName]
--		   ,[Content]
--		   ,[MessageID]
--		   ,[Headers]
--		   ,[ContentEncoding]
--		   ,[Type]
--		   ,[UserID]
--		   ,[State]
--		   ,[CreateDateTime]
--		   ,[OwnerID])
--		select
--		   NewID()--(<RabbitSendObjectID, uniqueidentifier,>
--		   ,@QueueName--,<QueueName, varchar(255),>
--		   ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
--		   ,null--,<MessageID, varchar(255),>
--		   ,null--,<Headers, varchar(255),>
--		   ,@ContentEncoding--,<ContentEncoding, varchar(255),>
--		   ,@Type--,<Type, varchar(255),>
--		   ,null--,<UserID, varchar(255),>
--		   ,0--,<State, int,>)
--		   ,GETDATE()
--		   ,@OwnerID
--		END
--	ELSE 
--	IF (@Xml is not null)
--		BEGIN
--		-- Generate table #DIVISION_LINK_STRUCTURE_BED
--		select
--				T.c.value('./header[1]/TYPE[1]', 'varchar(max)') as [OperationType]
--				,T.c.value('./header[1]/LPU[1]', 'varchar(6)') as [LpuMain]
--				,T.c.value('./header[1]/ID[1]', 'uniqueidentifier') as [ID]
--				,T.c.value('./body[1]/DIVISION_ID[1]', 'uniqueidentifier') as [Div]
--				,T.c.value('./body[1]/STRUCTURE_BED[1]', 'int') as [CodeSB]
--				,T.c.value('./body[1]/V020_STRUCTURE_BED[1]', 'int') as [V020StructureBed]
--			INTO #DIVISION_LINK_STRUCTURE_BED
--		from @Xml.nodes('DIVISION_LINK_STRUCTURE_BED') T(c)
--		select @OperationType = (select OperationType from #DIVISION_LINK_STRUCTURE_BED)

--		if(@OperationType <> 'Delete')
--		BEGIN
--		-- INSERT [AKUZ].T_DIVISION_LINK_STRUCTURE_BED
--		INSERT INTO [AKUZ].[T_DIVISION_LINK_STRUCTURE_BED]
--		([DivisionLinkStructureBedID] ,[Division],[StructureBed], [OtherID])
--		SELECT
--					newID()
--					,(select d.DivisionID from [AKUZ].T_DIVISION d where d.OtherID = dsl.Div)
--					,case when V020StructureBed = null then (select TOP 1 sb.StructureBedID from [AKUZ].T_STRUCTURE_BED sb
--																join [AKUZ].T_V002_PROFILE v0 on v0.Id = sb.V002Profile
--																	where v0.Id = dsl.CodeSB)
--						 else (select TOP 1 sb.StructureBedID from [AKUZ].T_STRUCTURE_BED sb
--																join [AKUZ].T_V002_PROFILE v0 on v0.Id = sb.V002Profile
--																	where v0.Id = dsl.CodeSB
--																	and sb.Code = dsl.[V020StructureBed] 
--																	and sb.DateBegin <= GETDATE()
--																	and (sb.DateEnd is null or
--																			sb.DateEnd >= GETDATE())) end 
--					,dsl.ID
--		from #DIVISION_LINK_STRUCTURE_BED dsl
--		where not exists (select * from [AKUZ].T_DIVISION_LINK_STRUCTURE_BED dl where dl.OtherID = dsl.ID)		
--		END
--		ELSE
--		BEGIN
--			delete dsl
--			from [AKUZ].[T_DIVISION_LINK_STRUCTURE_BED] dsl
--			join #DIVISION_LINK_STRUCTURE_BED b on b.[ID] = dsl.OtherID
--		END

--		-- Drop table
--		DROP TABLE #DIVISION_LINK_STRUCTURE_BED

--		END
--END
--GO