--do $$
--begin
--	if exists 
--	(
--		select * from pg_proc p
--		where p.proname = 'sp_rds_branchxml'
--	)
--	then
--		drop function public.sp_rds_branchxml(text, xml, bit, uuid, varchar(255));
--	end if;
--end;
--$$ language plpgsql;
---- =============================================
---- Author:		Vitacore (с)
---- Create date: 2014-02-05
---- Description:	Выгрузка/загрузка справочника Филиалов в(из) RDS 
---- =============================================
--CREATE or Replace Function public.sp_RDS_BranchXml
--(
--	"LpuCode" text = null,
--	"Xml" xml = null,
--	"IsNew" bit = '1',
--	"ID" UUID = null,
--	"QueueName" varchar(255) = null
--)
--RETURNS Void As 
--$$
--Declare	Result text;
--			ContentEncodingVar varchar(255);
--			TypeVar varchar(255);
--			OperationTypeVar varchar(255);
--			OwnerIDVar UUID;
--Begin
--	IF "Xml" is null
--	then		
--		--выясняем тип операции
--		IF "IsNew" = '1'
--		then
--			OperationTypeVar := 'Insert';
--		ELSIF "IsNew" = '0' and exists (select * from akuz.T_BRANCH br  where br.LpuID = "ID")
--		then
--			OperationTypeVar := 'Update';
--		ELSIF "IsNew" = '0' and not exists (select * from akuz.T_BRANCH br where br.LpuID = "ID")
--		then
--			OperationTypeVar := 'Delete';
--			Result := XMLELEMENT
--				  (
--					name "BRANCH"
--				  ,	XMLELEMENT
--					(
--						name header
--					,	XMLFOREST
--						(
--							"LpuCode" as "LPU"
--						,	OperationTypeVar as "TYPE"
--						,	"ID" as "ID"
--						)
--					)
--				  ); 
--		END if;

--		ContentEncodingVar := 'utf-8';
--		TypeVar := 'BRANCH';
--		OwnerIDVar := "ID";
		
--		IF OperationTypeVar <> 'Delete'
--		then
--			select 	
--			XMLELEMENT
--			(
--				name "BRANCH"
--			,	XMLELEMENT
--				(
--					name header
--				,	XMLFOREST
--					(
--						"LpuCode" as "LPU"
--					,	OperationTypeVar as "TYPE"
--					,	"ID" as "ID"
--					)
--				)
--			,	XMLELEMENT
--				(
--					name body
--				,	XMLFOREST
--					(
--						br.BranchCode	as "BR_CODE"
--					,	br.Name	as "NAME"
--					,	case when br.DateAdd is null then br.DateAdd::varchar(10) else to_char(br.DateAdd::TimeStamp, 'YYYY-MM-DD') end as "DATE_ADD"
--					,	case when br.DateRemove is null then br.DateRemove::varchar(10) else to_char(br.DateRemove::TimeStamp, 'YYYY-MM-DD') end as "DATE_REMOVE"
--					,	br.AddressString as "ADDRESS"
--					,	br.NameFull as "NAMEFULL"
--					,	br.RegistryPhones as "PHONE"
--					,	br.LpuType as "LPU_TYPE"
--					,	br.Category as "CATEGORY"
--					,	br.IsActive as "ACTIVE"
--					)
--				)
--			)
--			FROM akuz.T_BRANCH br
--			into Result
--			where br.LpuID = "ID";
--		END if;

--		INSERT INTO akuz.T_RABBIT_SEND_OBJECT( RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
--		select uuid_generate_v1(), "QueueName", Result, null, null, ContentEncodingVar, TypeVar, null, 0, Current_TimeStamp::TIMESTAMP, OwnerIDVar;
--	ELSIF "Xml" is not null
--	then
--		-- Generate table #BRANCH
--		Drop Table If Exists Tmp_BRANCH;
--		Create Temp Table Tmp_BRANCH(TYPE text, LPU varchar(6), ID UUID, BR_CODE varchar(8), NAME text, DATE_ADD TimeStamp, DATE_REMOVE TimeStamp, ADDRESS text, NAMEFULL text, PHONE text
--			, LPU_TYPE int, CATEGORY int, ACTIVE boolean);	
		
--		insert into Tmp_BRANCH(TYPE, LPU, ID, BR_CODE, NAME, DATE_ADD, DATE_REMOVE, ADDRESS, NAMEFULL, PHONE, LPU_TYPE, CATEGORY, ACTIVE)	
--		SELECT	nullif(trim(xpath('/BRANCH/header[1]/TYPE[1]/text()', "Xml")::text,'{}"'),'')
--		,	nullif(trim(xpath('/BRANCH/header[1]/LPU[1]/text()', "Xml")::text,'{}"'),'')::varchar(6)
--		,	nullif(trim(xpath('/BRANCH/header[1]/ID[1]/text()', "Xml")::text,'{}"'),'')::UUID		
--		,	nullif(trim(xpath('/BRANCH/body[1]/BR_CODE[1]/text()', "Xml")::text,'{}"'),'')::varchar(16)
--		,	nullif(trim(xpath('/BRANCH/body[1]/NAME[1]/text()', "Xml")::text,'{}"'),'')
--		,	to_timestamp(nullif(trim(xpath('/BRANCH/body[1]/DATE_ADD[1]/text()', "Xml")::text,'{}"'),''),'YYYYMMDD')
--		,	to_timestamp(nullif(trim(xpath('/BRANCH/body[1]/DATE_REMOVE[1]/text()', "Xml")::text,'{}"'),''),'YYYYMMDD')
--		,	nullif(trim(xpath('/BRANCH/body[1]/ADDRESS[1]/text()', "Xml")::text,'{}"'),'')
--		,	nullif(trim(xpath('/BRANCH/body[1]/NAMEFULL[1]/text()', "Xml")::text,'{}"'),'')
--		,	nullif(trim(xpath('/BRANCH/body[1]/PHONE[1]/text()', "Xml")::text,'{}"'),'')
--		,	nullif(trim(xpath('/BRANCH/body[1]/LPU_TYPE[1]/text()', "Xml")::text,'{}"'),'')::int
--		,	nullif(trim(xpath('/BRANCH/body[1]/CATEGORY[1]/text()', "Xml")::text,'{}"'),'')::int
--		,	nullif(trim(xpath('/BRANCH/body[1]/ACTIVE[1]/text()', "Xml")::text,'{}"'),'')::boolean;
			
--		select tmp.OperationType 
--		from Tmp_Branch tmp
--		into OperationTypeVar
--		Limit 1;

--		if OperationTypeVar <> 'Delete'
--		then
--			-- update akuz.T_BRANCH
--			update akuz.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
--			from Tmp_BRANCH b 
--			where b.ID = br.OtherID;

--			-- INSERT akuz.T_BRANCH
--			INSERT INTO akuz.T_BRANCH( LpuID, BranchCode, Name, DateAdd, DateRemove, AddressString, NameFull, RegistryPhones, LpuMain, LpuType, Category, OtherID, IsActive, BranchSettings)
--			SELECT uuid_generate_v1(), b.BranchCode, b.Name, b.DateAdd, b.DateRemove, b.AddressString, b.NameFull, b.RegistryPhones
--			,	(select lpu.LpuID from akuz.T_LPU_MAIN lpu where lpu.LpuCode = b.LpuMain)
--			,	b.LpuType, b.Category, b.ID, b.Active, 0
--			from Tmp_BRANCH b
--			where not exists 
--			(
--				select * 
--				from akuz.T_BRANCH b1 
--				where b.ID = b1.OtherID
--			);
--		ELSE
--			delete from akuz.T_BRANCH br
--			where br.OtherID in
--			(
--				select b.ID from Tmp_BRANCH b
--			);
--		END if;
		
--		-- Drop table
--		DROP TABLE if exists Tmp_BRANCH;
--	END if;
--End;
--$$ Language plpgsql;