---- DROP FUNCTION sp_GetSetDictionaryXml(text, xml, boolean, uuid);
--CREATE OR REPLACE FUNCTION sp_GetSetDictionaryXml
--(
--	"LpuCode" text DEFAULT NULL::text
--,	"Xml" xml DEFAULT NULL::xml
--,	"IsNew" boolean DEFAULT '1'::boolean
--,	"ID" uuid DEFAULT NULL::uuid
--)
--RETURNS void AS
--$BODY$
--Declare	_Result text;
--	_ContentEncoding text;
--	_Type text;
--	_OperationType text;
--	_OwnerID uuid;
--begin
--	if "Xml" is null
--	then
--		_ContentEncoding := 'utf-8';
--		_Type := 'Lpu';
--		_OwnerID := "ID";

--		select
--		XMLELEMENT
--		(
--			name "LPU"
--		,	XMLELEMENT
--			(
--				name header
--			,	XMLFOREST
--				(
--					lp.MCOD as "LPU"
--				,	_Type	as "TYPE"
--				,	"ID"	as "ID"
--				)
--			)
--		,	(
--				select 
--				XMLAGG
--				(
--					XMLELEMENT
--					(
--						name "BRANCH"
--					,	XMLFOREST
--						(
--							br.BranchCode					as "BR_CODE"
--						,	br.Name						as "NAME"
--						,	to_char(br.DateAdd, 'yyyy-mm-dd hh:mi:ss')	as "DATE_ADD"
--						,	to_char(br.DateRemove, 'yyyy-mm-dd hh:mi:ss')	as "DATE_REMOVE"
--						,	br.AddressString				as "ADDRESS"
--						,	br.NameFull					as "NAMEFULL"
--						,	br.RegistryPhones				as "PHONE"
--						,	lp.MCOD						as "LPU"
--						,	br.LpuType					as "LPU_TYPE"
--						,	br.Category					as "CATEGORY"
--						,	br.BranchID					as "ID"
--						,	br.IsActive					as "ACTIVE"
--						)
--					,	(
--							select
--							XMLAGG
--							(
--								XMLELEMENT
--								(
--									name "DIVISION"
--								,	XMLFOREST
--									(
--										d.Code		as "DV_CODE"
--									,	d.Name		as "NAME"
--									,	d.FullName	as "FULLNAME"
--									,	d.Level		as "LEVEL"
--									,	d.CodeMz	as "CODE_MZ"
--									,	d.Nofilet	as "PHONE"
--									,	d.Adress	as "ADDRESS"
--									,	d.Branch	as "BRANCH_ID"
--									,	d.DivisionType	as "DIVISION_TYPE"
--									,	d.RevenueType	as "REVENUE_TYPE"
--									,	lp.MCOD		as "LPU"
--									,	d.DivisionID	as "ID"
--									,	d.IDNumberCard	as "ID_NUMBER_CARD"
--									,	d.IsActive	as "ACTIVE"
--									)
--								,	(
--										select
--										XMLAGG
--										(
--											XMLELEMENT
--											(
--												name "DIVISION_LINK_STRUCTURE_BED"
--											,	XMLFOREST
--												(
--													sb.Code				as "STRUCTURE_BED"
--												,	sbb.IDK_PR			as "V020_STRUCTURE_BED"
--												,	lp.MCOD				as "LPU" 
--												,	dlsb.DivisionLinkStructureBedID	as "ID"
--												,	dlsb.Division			as "DIVISION_ID"
--												)
--											,	(
--													select
--													XMLAGG
--													(
--														XMLELEMENT
--														(
--															name "AMOUNT_BED"
--														,	XMLFOREST
--															(
--																sb.Code						as "STRUCTURE_BED"
--															,	sbb.IDK_PR					as "V020_STRUCTURE_BED"
--															,	lp.MCOD						as "LPU"
--															,	ab.DivisionLinkStructureBed			as "DLSB"
--															,	to_char(ab.DateBegin, 'yyyy-mm-dd hh:mi:ss')	as "DATE_BEGIN"
--															,	to_char(ab.DateEnd, 'yyyy-mm-dd hh:mi:ss')	as "DATE_END"
--															,	ab.FemaleBed					as "FEMALE_BED"
--															,	ab.MaleBed					as "MALE_BED"
--															,	ab.BedsCount					as "BEDS_COUNT"
--															,	ab.RepairBed					as "REPAIR_BED"
--															,	ab.PlannedBedsCount				as "PLANNED_BEDS_COUNT"
--															,	ab.PlannedMaleBed				as "PLANNED_MALE_BED"
--															,	ab.PlannedFemaleBed				as "PLANNED_FEMALE_BED"
--															,	ab.AmountBedID					as "ID"
--															)
--														)
--													)
--													from RDS.T_AMOUNT_BED ab 
--													where dlsb.DivisionLinkStructureBedID = ab.DivisionLinkStructureBed
--												)
--											)
--										)
--										from RDS.T_DIVISION_LINK_STRUCTURE_BED dlsb
--										inner join 
--											RDS.T_STRUCTURE_BED sb 
--											on sb.Code = dlsb.StructureBed 
--										left join 
--											RDS.T_V020_STRUCTURE_BED sbb 
--											on sbb.IDK_PR = dlsb.V020StructureBed
--										where dlsb.Division = d.DivisionID
--									)
--								)
--							)
--							from RDS.T_DIVISION d
--							where d.Branch = br.BranchID
--						)
--					)		
--				)
--				from RDS.T_BRANCH br
--				where lp.MCOD = br.LpuMain
--			)
--		)       	
--		from	RDS.T_LPU_MAIN lp
--		into _Result
--		where lp.MCOD = "LpuCode";

--		-- Отправка всем остальным подписанным серверам AKUZ-а и APS
--		INSERT INTO RDS.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
--		select uuid_generate_v4(), s.ServerName, _Result, null, null, _ContentEncoding, _Type, null, 0, current_timestamp, _OwnerID
--		from RDS.T_SYNC_SERVERS s
--		where s.ServerType = 0;
--	elsif "Xml" is not null
--	then
--		-- Generate table #BRANCH
--		Drop Table If Exists TmpBRANCH;
--		Create Temp Table TmpBRANCH
--		As
--		select  (xpath('/BRANCH/BR_CODE[1]/text()', x))[1]::text						as BranchCode
--		,	(xpath('/BRANCH/NAME[1]/text()', x))[1]::text							as Name
--		,	to_timestamp((xpath('/BRANCH/DATE_ADD[1]/text()', x))[1]::text, 'DD.MM.YYYY')::timestamp	as DateAdd
--		,	to_timestamp((xpath('/BRANCH/DATE_REMOVE[1]/text()', x))[1]::text, 'DD.MM.YYYY')::timestamp	as DateRemove
--		,	(xpath('/BRANCH/ADDRESS[1]/text()', x))[1]::text						as AddressString
--		,	(xpath('/BRANCH/NAMEFULL[1]/text()', x))[1]::text						as NameFull
--		,	(xpath('/BRANCH/PHONE[1]/text()', x))[1]::text							as RegistryPhones
--		,	(xpath('/BRANCH/LPU[1]/text()', x))[1]::text							as LpuMain
--		,	(xpath('/BRANCH/LPU_TYPE[1]/text()', x))[1]::text::int						as LpuType
--		,	(xpath('/BRANCH/CATEGORY[1]/text()', x))[1]::text::int						as Category
--		,	(xpath('/BRANCH/ID[1]/text()', x))[1]::text::uuid						as ID
--		,	(xpath('/BRANCH/ACTIVE[1]/text()', x))[1]::text::boolean					as Active
--		from
--		(
--			select unnest(xpath('/LPU/BRANCH', x)) as x
--			from
--			(
--				select "Xml" as x
--			) root
--		) t;

--		update RDS.T_BRANCH br
--		set	BranchCode = b.BranchCode
--		,	Name = b.Name
--		,	DateAdd = b.DateAdd
--		,	DateRemove = b.DateRemove
--		,	AddressString = b.AddressString
--		,	NameFull = b.NameFull
--		,	RegistryPhones = b.RegistryPhones
--		,	LpuType = b.LpuType
--		,	Category = b.Category
--		,	IsActive = b.Active
--		,	ID = b.ID
--		,	LpuMain = b.LpuMain
--		from TmpBRANCH b 
--		where b.ID = br.ID or br.BranchID = b.ID;

--		update RDS.T_BRANCH br
--		set br.IsActive = '0'
--		from TmpBRANCH bb 
--		where bb.LpuMain = br.LpuMain
--		and not exists (select * from TmpBRANCH b where b.ID = br.ID or br.BranchID = b.ID);

--		-- INSERT [RDS].T_BRANCH
--		insert into RDS.T_BRANCH(BranchID, BranchCode, Name, DateAdd, DateRemove, AddressString, NameFull, RegistryPhones, LpuMain, LpuType, Category, IsActive, ID)
--		select uuid_generate_v4(), b.BranchCode, b.Name, b.DateAdd, b.DateRemove, b.AddressString, b.NameFull, b.RegistryPhones, b.LpuMain, b.LpuType, b.Category, b.Active, b.ID
--		from TmpBRANCH b
--		where not exists (select * from RDS.T_BRANCH b1 where b.ID = b1.ID or b1.BranchID = b.ID);
			
--		-- Generate table #DIVISION
--		Drop Table If Exists TmpDIVISION;
--		Create Temp Table TmpDIVISION
--		As
--		select  (xpath('/DIVISION/DV_CODE[1]/text()', x))[1]::text						as Code
--		,	(xpath('/DIVISION/NAME[1]/text()', x))[1]::text							as Name
--		,	(xpath('/DIVISION/FULLNAME[1]/text()', x))[1]::text						as FullName
--		,	(xpath('/DIVISION/LEVEL[1]/text()', x))[1]::text::int						as Level
--		,	(xpath('/DIVISION/CODE_MZ[1]/text()', x))[1]::text						as CodeMz
--		,	(xpath('/DIVISION/PHONE[1]/text()', x))[1]::text						as Nofilet
--		,	(xpath('/DIVISION/ADDRESS[1]/text()', x))[1]::text						as Adress
--		,	(xpath('/DIVISION/BRANCH_ID[1]/text()', x))[1]::text::uuid					as Branch
--		,	(xpath('/DIVISION/DIVISION_TYPE[1]/text()', x))[1]::text::int					as DivisionType
--		,	(xpath('/DIVISION/REVENUE_TYPE[1]/text()', x))[1]::text::int					as RevenueType
--		,	(xpath('/DIVISION/LPU[1]/text()', x))[1]::text							as Lpu
--		,	(xpath('/DIVISION/ID[1]/text()', x))[1]::text::uuid						as ID
--		,	(xpath('/DIVISION/ID_NUMBER_CARD[1]/text()', x))[1]::text::int					as IDNumberCard
--		,	(xpath('/DIVISION/ACTIVE[1]/text()', x))[1]::text::boolean					as Active			
--		from
--		(
--			select unnest(xpath('/LPU/BRANCH/DIVISION', x)) as x
--			from
--			(
--				select "Xml" as x
--			) root
--		) t;

--		-- update [RDS].T_DIVISION
--		update RDS.T_DIVISION d
--		set	Code = div.Code
--		,	Name = div.Name
--		,	FullName = div.FullName
--		,	Level = div.Level
--		,	CodeMz = div.CodeMz
--		,	Nofilet = div.Nofilet
--		,	Adress = div.Adress
--		,	DivisionType = div.DivisionType
--		,	IDNumberCard = div.IDNumberCard
--		,	RevenueType = CASE d.RevenueType WHEN 0 THEN 12900 WHEN 1 THEN 12901 WHEN 2 THEN 12902 ELSE div.RevenueType END
--		,	IsActive = div.Active
--		,	ID = div.ID
--		,	Branch = (select b.BranchId from RDS.T_BRANCH b where b.BranchId = div.Branch or b.ID = div.Branch limit 1)
--		from TmpDIVISION div 
--		where div.ID = d.ID or div.ID = d.DivisionID;

--		update RDS.T_DIVISION dd
--		set IsActive = '0'
--		from RDS.T_BRANCH b 
--		where b.BranchID = dd.Branch
--		and exists(select* from TmpDIVISION ddd where ddd.Lpu = b.LpuMain)
--		and not exists(select * from TmpDIVISION div where div.ID = dd.ID or dd.DivisionID = div.ID);

--		-- INSERT [RDS].T_DIVISION
--		insert into RDS.T_DIVISION(DivisionID, Code, Name, FullName, Level, CodeMz, Nofilet, Adress, Branch, DivisionType, RevenueType, IsActive, ID, IDNumberCard)
--		select uuid_generate_v4(), d.Code, d.Name, d.FullName, d.Level, d.CodeMz, d.Nofilet, d.Adress, b.BranchID, d.DivisionType
--		, CASE d.RevenueType WHEN 0 THEN 12900 WHEN 1 THEN 12901 WHEN 2 THEN 12902 ELSE d.RevenueType END, d.Active, d.id, d.IDNumberCard
--		from TmpDIVISION d
--		inner join 
--			RDS.T_BRANCH b 
--			on b.ID = d.Branch or b.BranchID = d.Branch
--		where b.IsActive = '1' 
--		and not exists 
--		(
--			select * from RDS.T_DIVISION d1 where d1.ID = d.ID or d.ID = d1.DivisionID
--		);

--		-- Generate table #DIVISION_LINK_STRUCTURE_BED
--		Drop Table If Exists TmpDIVISION_LINK_STRUCTURE_BED;
--		Create Temp Table TmpDIVISION_LINK_STRUCTURE_BED
--		As
--		select  (xpath('/DIVISION_LINK_STRUCTURE_BED/STRUCTURE_BED[1]/text()', x))[1]::text::int	as CodeSB
--		,	(xpath('/DIVISION_LINK_STRUCTURE_BED/V020_STRUCTURE_BED[1]/text()', x))[1]::text::int	as V020StructureBed
--		,	(xpath('/DIVISION_LINK_STRUCTURE_BED/LPU[1]/text()', x))[1]::text			as Lpu
--		,	(xpath('/DIVISION_LINK_STRUCTURE_BED/ID[1]/text()', x))[1]::text::uuid			as ID
--		,	(xpath('/DIVISION_LINK_STRUCTURE_BED/DIVISION_ID[1]/text()', x))[1]::text::uuid		as Div
--		from
--		(
--			select unnest(xpath('/LPU/BRANCH/DIVISION/DIVISION_LINK_STRUCTURE_BED', x)) as x
--			from
--			(
--				select "Xml" as x
--			) root
--		) t;

--		-- Generate table #AMOUNT_BED
--		Drop Table If Exists TmpAMOUNT_BED;
--		Create Temp Table TmpAMOUNT_BED
--		As
--		select  (xpath('/AMOUNT_BED/STRUCTURE_BED[1]/text()', x))[1]::text::int					as CodeSB
--		,	(xpath('/AMOUNT_BED/V020_STRUCTURE_BED[1]/text()', x))[1]::text::int				as V020StructureBed
--		,	(xpath('/AMOUNT_BED/LPU[1]/text()', x))[1]::text						as Lpu		
--		,	to_timestamp((xpath('/AMOUNT_BED/DATE_BEGIN[1]/text()', x))[1]::text, 'DD.MM.YYYY')::timestamp	as DateBegin
--		,	to_timestamp((xpath('/DATE_END/DATE_BEGIN[1]/text()', x))[1]::text, 'DD.MM.YYYY')::timestamp	as DateEnd
--		,	(xpath('/AMOUNT_BED/FEMALE_BED[1]/text()', x))[1]::text::int					as FemaleBed
--		,	(xpath('/AMOUNT_BED/MALE_BED[1]/text()', x))[1]::text::int					as MALE_BED
--		,	(xpath('/AMOUNT_BED/BEDS_COUNT[1]/text()', x))[1]::text::int					as BedsCount
--		,	(xpath('/AMOUNT_BED/REPAIR_BED[1]/text()', x))[1]::text::int					as RepairBed		
--		,	(xpath('/AMOUNT_BED/ID[1]/text()', x))[1]::text::uuid						as ID
--		,	(xpath('/AMOUNT_BED/DLSB[1]/text()', x))[1]::text::uuid						as Dlsb
--		,	(xpath('/AMOUNT_BED/PLANNED_BEDS_COUNT[1]/text()', x))[1]::text::int				as PlannedBedsCount
--		,	(xpath('/AMOUNT_BED/PLANNED_MALE_BED[1]/text()', x))[1]::text::int				as PlannedMaleBed
--		,	(xpath('/AMOUNT_BED/PLANNED_FEMALE_BED[1]/text()', x))[1]::text::int				as PlannedFemaleBed
--		from
--		(
--			select unnest(xpath('/LPU/BRANCH/DIVISION/DIVISION_LINK_STRUCTURE_BED/AMOUNT_BED', x)) as x
--			from
--			(
--				select "Xml" as x
--			) root
--		) t;

--		-- INSERT [RDS].T_DIVISION_LINK_STRUCTURE_BED
--		INSERT INTO RDS.T_DIVISION_LINK_STRUCTURE_BED(DivisionLinkStructureBedID, Division, StructureBed, ID, V020StructureBed)
--		SELECT uuid_generate_v4(), d.DivisionID, dsl.CodeSB, dsl.ID, dsl.V020StructureBed
--		from TmpDIVISION_LINK_STRUCTURE_BED dsl
--		inner join 
--			RDS.T_DIVISION d 
--			on d.ID = dsl.Div or d.DivisionID = dsl.Div
--		where d.IsActive = '1'
--		and not exists 
--		(
--			select * from RDS.T_DIVISION_LINK_STRUCTURE_BED dd where dd.ID = dsl.ID or dd.ID = dd.DivisionLinkStructureBedID
--		);
  
--		-- update [RDS].T_AMOUNT_BED
--		update RDS.T_AMOUNT_BED ab
--		set	DateEnd = am.DateEnd
--		,	FemaleBed = coalesce(am.FemaleBed, 0)
--		,	MaleBed = coalesce(am.MaleBed, 0)
--		,	BedsCount = coalesce(am.BedsCount, 0)
--		,	RepairBed = coalesce(am.RepairBed, 0)
--		,	PlannedFemaleBed = coalesce(am.PlannedFemaleBed, 0)
--		,	PlannedMaleBed =  coalesce(am.PlannedMaleBed, 0)
--		,	PlannedBedsCount = coalesce(am.PlannedBedsCount, 0)
--		,	DivisionLinkStructureBed = (select dl.DivisionLinkStructureBedID from RDS.T_DIVISION_LINK_STRUCTURE_BED dl where dl.DivisionLinkStructureBedID = am.Dlsb or dl.ID = am.Dlsb limit 1)
--		from TmpAMOUNT_BED am 
--		where am.ID = ab.ID or am.ID = ab.AmountBedID;

--		update RDS.T_AMOUNT_BED ab
--		set DateEnd = CURRENT_DATE - INTERVAL '1 day'
--		from RDS.T_DIVISION_LINK_STRUCTURE_BED dlsb 
--		where dlsb.DivisionLinkStructureBedID = ab.DivisionLinkStructureBed
--		and exists
--		(
--			select* 
--			from RDS.T_DIVISION d 
--			inner join 
--				RDS.T_BRANCH b 
--				on b.BranchID = d.Branch
--			inner join 
--				TmpAMOUNT_BED abb 
--				on abb.Lpu = b.LpuMain
--			where dlsb.Division =  d.DivisionID
--		)
--		and not exists 
--		(
--			select * from TmpAMOUNT_BED abi where abi.ID = ab.ID or abi.ID = ab.AmountBedID
--		);

--		-- INSERT [RDS].T_AMOUNT_BED
--		insert into RDS.T_AMOUNT_BED(AmountBedID, DateBegin, DateEnd, DivisionLinkStructureBed, FemaleBed, MaleBed, BedsCount, RepairBed, ID, PlannedFemaleBed, PlannedMaleBed, PlannedBedsCount)
--		select uuid_generate_v4(), ab.DateBegin, ab.DateEnd, dlsb.DivisionLinkStructureBedID, coalesce(ab.FemaleBed, 0), coalesce(ab.MaleBed, 0), coalesce(ab.BedsCount, 0), coalesce(ab.RepairBed, 0)
--		, ab.ID, coalesce(ab.PlannedFemaleBed, 0), coalesce(ab.PlannedMaleBed, 0), coalesce(ab.PlannedBedsCount, 0)
--		from TmpAMOUNT_BED ab
--		inner join 
--			RDS.T_DIVISION_LINK_STRUCTURE_BED dlsb 
--			on dlsb.ID = ab.Dlsb or dlsb.DivisionLinkStructureBedID = ab.Dlsb
--		inner join 
--			RDS.T_DIVISION d 
--			on d.ID = dlsb.Division or d.DivisionID = dlsb.Division
--		where d.IsActive = '1'
--		and not exists 
--		(
--			select * from RDS.T_AMOUNT_BED ab1 where ab1.ID = ab.ID or ab.ID = ab1.AmountBedID
--		);

--		-- Drop table
--		drop table if exists TmpBRANCH;
--		drop table if exists TmpDIVISION;
--		drop table if exists TmpDIVISION_LINK_STRUCTURE_BED;
--		drop table if exists TmpAMOUNT_BED;
--	end if;
--end;
--$BODY$
--  LANGUAGE plpgsql VOLATILE
--  COST 100;
--ALTER FUNCTION sp_GetSetDictionaryXml(text, xml, boolean, uuid)
--  OWNER TO postgres;


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

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

--SET NOCOUNT ON;
--	IF (@Xml is null)
--	BEGIN

--	select @QueueName = 'RDS263'
--   ,@ContentEncoding = 'utf-8'
--   ,@Type = 'Lpu'
--   ,@OwnerID = @ID
--	-- Add the T-SQL statements to compute the return value here
--	SELECT @Result = ( 
--			select
--			@LpuCode											as 'header/LPU'
--			,@Type												as 'header/TYPE'
--			,@ID												as 'header/ID'
--			,(
--			--собираем BRANCH
--			select 
--					br.[BranchCode] as 'BR_CODE'
--					,br.[Name] as 'NAME'
--					,convert(varchar(10), br.[DateAdd], 20) as 'DATE_ADD'
--					,convert(varchar(10), br.[DateRemove], 20) as 'DATE_REMOVE'
--					,br.[AddressString] as 'ADDRESS'
--					,br.[NameFull] as 'NAMEFULL'
--					,br.[RegistryPhones] as 'PHONE'
--					,lp.[MCOD] as 'LPU'
--					,br.[LpuType] as 'LPU_TYPE'
--					,br.[Category] as 'CATEGORY'
--					,br.[BranchID] as 'ID'
--					,br.[IsActive] as 'ACTIVE'
--					--собираем DIVISION
--					, ( 
--					select 
--						d.[Code] as 'DV_CODE'
--						,d.[Name] as 'NAME'
--						,d.[FullName] as 'FULLNAME'
--						,d.[Level] as 'LEVEL'
--						,d.[CodeMz] as 'CODE_MZ'
--						,d.[Nofilet] as 'PHONE'
--						,d.[Adress] as 'ADDRESS'
--						,d.[Branch]			as 'BRANCH_ID'
--						,d.[DivisionType] as 'DIVISION_TYPE'
--						,d.[RevenueType] as 'REVENUE_TYPE'
--						,lp.[MCOD] as 'LPU'
--						,d.[DivisionID] as 'ID'
--						,d.[IDNumberCard]   as 'ID_NUMBER_CARD'
--					    ,d.[IsActive]								 as 'ACTIVE'
--                      --собираем DIVISION_LINK_STRUCTURE_BED
--    					, (
--    					select 
--    						sb.Code as 'STRUCTURE_BED'
--							,sbb.IDK_PR as 'V020_STRUCTURE_BED'
--    						,lp.[MCOD] as 'LPU' 
--							,dlsb.[DivisionLinkStructureBedID] as 'ID'
--							,dlsb.Division as 'DIVISION_ID'
--							--собираем AMOUNT_BED
--							, (
--							select 
--								sb.Code as 'STRUCTURE_BED'
--								,sbb.IDK_PR as 'V020_STRUCTURE_BED'
--								,lp.[MCOD] as 'LPU'
--								,ab.DivisionLinkStructureBed as 'DLSB'
--								,convert(varchar(10), ab.[DateBegin], 20) as 'DATE_BEGIN'
--							    ,convert(varchar(10), ab.[DateEnd], 20) as 'DATE_END'
--							    ,ab.[FemaleBed] as 'FEMALE_BED'
--							    ,ab.[MaleBed] as 'MALE_BED'
--							    ,ab.[BedsCount] as 'BEDS_COUNT'
--							    ,ab.[RepairBed] as 'REPAIR_BED'
--								,ab.[PlannedBedsCount]						as 'PLANNED_BEDS_COUNT'
--								,ab.[PlannedMaleBed]						as 'PLANNED_MALE_BED'
--								,ab.[PlannedFemaleBed]						as 'PLANNED_FEMALE_BED'
--								,ab.AmountBedID								as 'ID'
--							from [RDS].T_AMOUNT_BED ab 
--							where dlsb.DivisionLinkStructureBedID = ab.DivisionLinkStructureBed
--							FOR XML PATH('AMOUNT_BED'),
--							TYPE
--						)
--    					from [RDS].T_DIVISION_LINK_STRUCTURE_BED dlsb
--    					join [RDS].T_STRUCTURE_BED sb on sb.Code = dlsb.StructureBed 
--						left join [RDS].T_V020_STRUCTURE_BED sbb on sbb.IDK_PR = dlsb.V020StructureBed 
--    					where dlsb.Division = d.DivisionID
--    					FOR XML PATH('DIVISION_LINK_STRUCTURE_BED'),
--    					TYPE
--                      )
--					FROM [RDS].T_DIVISION d
--					where d.Branch = br.BranchID
--					FOR XML PATH('DIVISION'),
--					TYPE
--					)
--			FROM [RDS].T_BRANCH br
--			join [RDS].T_LPU_MAIN lp on lp.MCOD = br.LpuMain
--			where lp.MCOD = @LpuCode
--			FOR XML PATH('BRANCH'),
--			TYPE
--			)
--			FOR XML PATH(''),
--			ROOT('LPU')
--	) 

--	-- Отправка всем остальным подписанным серверам AKUZ-а и APS
--        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),>
--              ,@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()--,<CreateDateTime, datetime,>)
--              ,@OwnerID--,<OwnerID, varchar(255),>
--        from [RDS].T_SYNC_SERVERS s
--        where s.ServerType = 0 -- Рассылаем пакет всем подписанным серверам AKUZ-а 

--		END
--	ELSE 
--	IF (@Xml is not null)
--		BEGIN
--		-- Generate table #BRANCH
--		select
--				T.c.value('./BR_CODE[1]', 'varchar(16)') as [BranchCode]
--				,T.c.value('./NAME[1]', 'varchar(max)') as [Name]
--				,T.c.value('./DATE_ADD[1]', 'datetime') as [DateAdd]
--				,T.c.value('./DATE_REMOVE[1]', 'datetime') as [DateRemove]
--				,T.c.value('./ADDRESS[1]', 'varchar(max)') as [AddressString]
--				,T.c.value('./NAMEFULL[1]', 'varchar(max)') as [NameFull]
--				,T.c.value('./PHONE[1]', 'varchar(max)') as [RegistryPhones]
--				,T.c.value('./LPU[1]', 'varchar(6)') as [LpuMain]
--				,T.c.value('./LPU_TYPE[1]', 'int') as [LpuType]
--				,T.c.value('./CATEGORY[1]', 'int') as [Category]
--				,T.c.value('./ID[1]', 'uniqueidentifier') as [ID]
--				,T.c.value('./ACTIVE[1]', 'bit') as [Active]
--			INTO #BRANCH
--		from @Xml.nodes('LPU/BRANCH') T(c)

--		-- update [RDS].T_BRANCH
--		update br
--		set 
--		br.[BranchCode] = b.[BranchCode], 
--		br.[Name] = b.[Name],
--		br.[DateAdd] = b.[DateAdd],
--		br.[DateRemove] = b.[DateRemove],
--		br.[AddressString] = b.[AddressString],
--		br.[NameFull] = b.[NameFull],
--		br.[RegistryPhones] = b.[RegistryPhones],
--		br.[LpuType] = b.[LpuType],
--		br.[Category] = b.[Category],
--		br.[IsActive] = b.[Active],
--		br.ID = b.ID,
--		br.LpuMain = b.LpuMain
--		from [RDS].T_BRANCH br
--		join #BRANCH b on (b.[ID] = br.ID or br.[BranchID] = b.ID)

--		update br
--		set br.IsActive = 0
--		from [RDS].T_BRANCH br
--		join #BRANCH bb on bb.[LpuMain] = br.LpuMain
--		where not exists (select * from #BRANCH b where b.[ID] = br.ID or br.[BranchID] = b.ID)

--		-- INSERT [RDS].T_BRANCH
--			INSERT INTO [RDS].[T_BRANCH] 
--			([BranchID],[BranchCode], [Name], [DateAdd], [DateRemove], [AddressString], [NameFull], [RegistryPhones], [LpuMain], [LpuType], [Category], [IsActive], ID)
--			SELECT
--				newID()
--				,b.[BranchCode]
--				,b.[Name]
--				,b.[DateAdd]
--				,b.[DateRemove]
--				,b.[AddressString]
--				,b.[NameFull]
--				,b.[RegistryPhones]
--				,b.[LpuMain]
--				,b.[LpuType]
--				,b.[Category]
--				,b.[Active]
--				,b.[ID]
--			from #BRANCH b
--		        where not exists (
--				select * 
--				from [RDS].T_BRANCH b1 
--				where (b.[ID] = b1.ID or b1.[BranchID] = b.ID)
--			)

--		-- Generate table #DIVISION
--		select
--				T.c.value('./DV_CODE[1]', 'varchar(16)') as [Code]
--				,T.c.value('./NAME[1]', 'varchar(max)') as [Name]
--				,T.c.value('./FULLNAME[1]', 'varchar(max)') as [FullName]
--				,T.c.value('./LEVEL[1]', 'int') as [Level]
--				,T.c.value('./CODE_MZ[1]', 'varchar(16)') as [CodeMz]
--				,T.c.value('./PHONE[1]', 'varchar(max)') as [Nofilet]
--				,T.c.value('./ADDRESS[1]', 'varchar(max)') as [Adress]
--				,T.c.value('./BRANCH_ID[1]', 'uniqueidentifier') as [Branch]
--				,T.c.value('./DIVISION_TYPE[1]', 'int') as [DivisionType]
--				,T.c.value('./REVENUE_TYPE[1]', 'int') as [RevenueType]
--				,T.c.value('./LPU[1]', 'varchar(6)') as [Lpu]
--				,T.c.value('./ID[1]', 'uniqueidentifier') as [ID]
--				,T.c.value('./ID_NUMBER_CARD[1]', 'int') as [IDNumberCard]
--				,T.c.value('./ACTIVE[1]', 'bit') as [Active]
--			INTO #DIVISION
--		from @Xml.nodes('LPU/BRANCH/DIVISION') T(c)

--		-- update [RDS].T_DIVISION
--		update d
--		set 
--		d.[Code] = div.[Code], 
--		d.[Name] = div.[Name],
--		d.[FullName] = div.[FullName],
--		d.[Level] = div.[Level],
--		d.[CodeMz] = div.[CodeMz],
--		d.[Nofilet] = div.[Nofilet],
--		d.[Adress] = div.[Adress],
--		d.[DivisionType] = div.[DivisionType],
--		d.[IDNumberCard] = div.[IDNumberCard],
--		d.[RevenueType] = ( 
--		CASE d.[RevenueType]
--		WHEN 0 THEN 12900
--		WHEN 1 THEN 12901
--		WHEN 2 THEN 12902
--		ELSE div.[RevenueType] END),
--		d.[IsActive] = div.[Active],
--		d.ID = div.ID,
--		d.Branch = (select top 1 b.BranchId from [RDS].T_BRANCH b where b.BranchId = div.Branch or b.ID = div.Branch)
--		from [RDS].T_DIVISION d
--		join #DIVISION div on (div.ID = d.ID or div.ID = d.[DivisionID])

--		update dd
--		set dd.IsActive = 0
--		from [RDS].T_DIVISION dd
--		join [RDS].T_BRANCH b on b.BranchID = dd.Branch
--		join #DIVISION ddd on ddd.[Lpu] = b.[LpuMain]
--		where not exists (select * from #DIVISION div where div.[ID] = dd.ID or dd.[DivisionID] = div.ID)

--		-- INSERT [RDS].T_DIVISION
--		INSERT INTO [RDS].[T_DIVISION] 
--		([DivisionID], [Code], [Name], [FullName], [Level], [CodeMz], [Nofilet], [Adress], [Branch], [DivisionType], [RevenueType], [IsActive], ID,[IDNumberCard])
--		SELECT
--					newID()
--					,d.[Code]
--					,d.[Name]
--					,d.[FullName]
--					,d.[Level]
--					,d.[CodeMz]
--					,d.[Nofilet]
--					,d.[Adress]
--					,b.BranchID
--					,d.[DivisionType]
--					,(CASE d.[RevenueType]
--					   WHEN 0 THEN 12900
--					   WHEN 1 THEN 12901
--					   WHEN 2 THEN 12902
--					   ELSE d.[RevenueType] END)
--					,d.[Active]
--					, d.id
--					,d.[IDNumberCard]
--		from #DIVISION d
--		join [RDS].T_BRANCH b on (b.ID = d.Branch or b.BranchID = d.Branch) 
--		where b.[IsActive] = 1 and not exists (
--			select * 
--			from [RDS].T_DIVISION d1 
--			where (d1.ID = d.ID or d.ID = d1.[DivisionID])
--		)


--		-- Generate table #DIVISION_LINK_STRUCTURE_BED
--		select
--				T.c.value('./STRUCTURE_BED[1]', 'int') as [CodeSB]
--				,T.c.value('./V020_STRUCTURE_BED[1]', 'int') as [V020StructureBed]
--				,T.c.value('./LPU[1]', 'varchar(6)') as [Lpu]
--				,T.c.value('./ID[1]', 'uniqueidentifier') as [ID]
--				,T.c.value('./DIVISION_ID[1]', 'uniqueidentifier') as [Div]
--			INTO #DIVISION_LINK_STRUCTURE_BED
--		from @Xml.nodes('LPU/BRANCH/DIVISION/DIVISION_LINK_STRUCTURE_BED') T(c)		


--		-- Generate table #AMOUNT_BED
--		select
--				T.c.value('./STRUCTURE_BED[1]', 'int') as [CodeSB]
--				,T.c.value('./V020_STRUCTURE_BED[1]', 'int') as [V020StructureBed]
--				,T.c.value('./LPU[1]', 'varchar(6)') as [Lpu]
--				,T.c.value('./DATE_BEGIN[1]', 'datetime') as [DateBegin]
--				,T.c.value('./DATE_END[1]', 'datetime') as [DateEnd] 
--				,T.c.value('./FEMALE_BED[1]', 'int') as [FemaleBed]
--				,T.c.value('./MALE_BED[1]', 'int') as [MaleBed] 
--				,T.c.value('./BEDS_COUNT[1]', 'int') as [BedsCount]
--				,T.c.value('./REPAIR_BED[1]', 'int') as [RepairBed]
--				,T.c.value('./ID[1]', 'uniqueidentifier') as [ID]
--				,T.c.value('./DLSB[1]', 'uniqueidentifier') as [Dlsb]
--				,T.c.value('./PLANNED_BEDS_COUNT[1]', 'int') as [PlannedBedsCount] 
--				,T.c.value('./PLANNED_MALE_BED[1]', 'int') as [PlannedMaleBed]
--				,T.c.value('./PLANNED_FEMALE_BED[1]', 'int') as [PlannedFemaleBed]
--			INTO #AMOUNT_BED
--		from @Xml.nodes('LPU/BRANCH/DIVISION/DIVISION_LINK_STRUCTURE_BED/AMOUNT_BED') T(c)

--		-- INSERT [RDS].T_DIVISION_LINK_STRUCTURE_BED
--		INSERT INTO [RDS].[T_DIVISION_LINK_STRUCTURE_BED]
--		(DivisionLinkStructureBedID ,[Division],[StructureBed], [ID],V020StructureBed)
--		SELECT
--					newID()
--					,d.DivisionID
--					,dsl.CodeSB
--					,dsl.ID
--					,dsl.V020StructureBed
--		from #DIVISION_LINK_STRUCTURE_BED dsl
--		join [RDS].T_DIVISION d on (d.ID = dsl.Div or d.DivisionID = dsl.Div)
--		where d.IsActive = 1 and not exists (select * from [RDS].T_DIVISION_LINK_STRUCTURE_BED dd where dd.ID = dsl.ID or dd.ID = dd.DivisionLinkStructureBedID) 
  
--		-- update [RDS].T_AMOUNT_BED
--		update ab
--		set
--			ab.[DateEnd] = am.DateEnd
--			,ab.[FemaleBed] = ISNULL(am.FemaleBed, 0)
--			,ab.[MaleBed] = ISNULL(am.MaleBed, 0)
--			,ab.[BedsCount] = ISNULL(am.BedsCount, 0)
--			,ab.[RepairBed] = ISNULL(am.RepairBed, 0)
--			,ab.[PlannedFemaleBed] = ISNULL(am.[PlannedFemaleBed], 0)
--			,ab.[PlannedMaleBed] =  ISNULL(am.[PlannedMaleBed], 0)
--			,ab.[PlannedBedsCount] = ISNULL(am.[PlannedBedsCount], 0)
--			,ab.DivisionLinkStructureBed = (select top 1 dl.DivisionLinkStructureBedID from [RDS].T_DIVISION_LINK_STRUCTURE_BED dl where dl.DivisionLinkStructureBedID = am.Dlsb or dl.ID = am.Dlsb)
--		from [RDS].T_AMOUNT_BED ab
--		join #AMOUNT_BED am on (am.ID = ab.ID  or am.ID = ab.[AmountBedID])

--		update ab
--		set ab.DateEnd = DATEADD(DAY,-1, Getdate())
--		from [RDS].T_AMOUNT_BED ab
--		Join [RDS].[T_DIVISION_LINK_STRUCTURE_BED] dlsb on dlsb.DivisionLinkStructureBedID = ab.DivisionLinkStructureBed
--		join [RDS].T_DIVISION d on dlsb.Division =  d.DivisionID
--		join [RDS].T_BRANCH b on b.BranchID = d.Branch
--		join #AMOUNT_BED abb on abb.[Lpu] = b.[LpuMain]
--		where not exists (select * from #AMOUNT_BED abi where abi.[ID] = ab.ID or abi.[ID] = ab.AmountBedID)

--		-- INSERT [RDS].T_AMOUNT_BED
--		INSERT INTO [RDS].[T_AMOUNT_BED]
--		([AmountBedID], [DateBegin], [DateEnd], [DivisionLinkStructureBed], [FemaleBed], [MaleBed], [BedsCount], [RepairBed], [ID], [PlannedFemaleBed], [PlannedMaleBed], [PlannedBedsCount])
--		SELECT
--				    newID()
--				    ,ab.[DateBegin]
--				    ,ab.[DateEnd]
--				    ,dlsb.DivisionLinkStructureBedID
--				    ,ISNULL(ab.[FemaleBed], 0)
--				    ,ISNULL(ab.[MaleBed], 0)
--				    ,ISNULL(ab.[BedsCount], 0)
--				    ,ISNULL(ab.[RepairBed], 0)
--					,ab.ID
--					,ISNULL(ab.[PlannedFemaleBed], 0)
--					,ISNULL(ab.[PlannedMaleBed], 0)
--					,ISNULL(ab.[PlannedBedsCount], 0)
--		from #AMOUNT_BED ab
--		join [RDS].T_DIVISION_LINK_STRUCTURE_BED dlsb on (dlsb.ID = ab.Dlsb or dlsb.DivisionLinkStructureBedID = ab.Dlsb)
--		join [RDS].T_DIVISION d on (d.ID = dlsb.Division or d.DivisionID = dlsb.Division)
--		where d.IsActive = 1 and not exists (
--			select * 
--			from [RDS].[T_AMOUNT_BED] ab1 
--			where (ab1.ID = ab.ID  or ab.ID = ab1.[AmountBedID])
--		)

--		-- Drop table
		
--		DROP TABLE #BRANCH
--		DROP TABLE #DIVISION
--		DROP TABLE #DIVISION_LINK_STRUCTURE_BED
--		DROP TABLE #AMOUNT_BED

--		END
--END
--GO
--*/