-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
CREATE OR REPLACE FUNCTION sp_rds_cmnn
(
	"Xml" xml DEFAULT NULL::xml
,	"IsNew" boolean DEFAULT '1'::boolean
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" varchar(100) DEFAULT 'RDSSync'::varchar(100)
,	"dateTime" timestamp DEFAULT NULL::timestamp
,	"User" uuid DEFAULT NULL::uuid
,	"PATH" text DEFAULT NULL::text
)
RETURNS void AS
$BODY$
Declare	_Result Text;
	_ContentEncoding varchar(255);
	_Type varchar(255);
	_OperationType varchar(6);
	_OwnerID UUID;
	-- Operation types
	_INSERT varchar(6);
	_UPDATE varchar(6);
	_DELETE varchar(6);
	_FTLOAD varchar(6);
	_sql text;
	_EntId int;
Begin
	"dateTime" := coalesce("dateTime", Current_TimeStamp::TIMESTAMP);
	-- Operation types
	_INSERT := 'Insert';
	_UPDATE := 'Update';
	_DELETE := 'Delete';
	_FTLOAD := 'FTLoad';
	
	IF "PATH" IS NOT NULL
	then
		DROP TABLE IF EXISTS Temp_C_MNN;
		CREATE TEMP TABLE Temp_C_MNN
		(
			CMnnID				uuid, 
			NameRus				VARCHAR(1000), 
			NameEng				VARCHAR(1000), 
			OtherID				INT, 
			Active				boolean, 
			UsedIn				INT, 
			ChemicalName		VARCHAR(600),
			BruttoFormula		VARCHAR(100),
			Characteristic		VARCHAR(4000), 
			Pharmacology		VARCHAR(MAX), 
			Practice			VARCHAR(6000), 
			Contraindications	VARCHAR(4000), 
			Restrictions		VARCHAR(MAX), 
			UseOfPregnancy		VARCHAR(MAX), 
			SideEffects			VARCHAR(MAX), 
			Interaction			VARCHAR(MAX), 
			InjectionMethod		VARCHAR(2000), 
			Caution				VARCHAR(MAX), 
			Overdose			VARCHAR(4000), 
			CASCode				VARCHAR(50), 
			Indications			VARCHAR(8000), 
			SingleDose			DECIMAL(17),
			MaxDose				DECIMAL(17), 
			SingleDoseView		INT, 
			MaxDoseView			INT, 
			NameLatR			VARCHAR(4000), 
			FedCode				VARCHAR(50), 
			RegCode				VARCHAR(50), 
			EhrRusDvl105		INT, 
			EhrRusDvl105Ver		VARCHAR(8), 
			EhrRusNsv101		INT,
			EhrRusNsv101Ver		VARCHAR(8)
		);
		_sql := 'insert into Temp_C_MNN
			(
				CMnnID, 
				NameRus, 
				NameEng, 
				OtherID, 
				Active, 
				UsedIn, 
				ChemicalName, 
				BruttoFormula, 
				Characteristic, 
				Pharmacology, 
				Practice, 
				Contraindications, 
				Restrictions, 
				UseOfPregnancy, 
				SideEffects, 
				Interaction, 
				InjectionMethod, 
				Caution, 
				Overdose, 
				CASCode, 
				Indications, 
				SingleDose, 
				MaxDose, 
				SingleDoseView, 
				MaxDoseView, 
				NameLatR, 
				FedCode, 
				RegCode, 
				EhrRusDvl105, 
				EhrRusDvl105Ver, 
				EhrRusNsv101, 
				EhrRusNsv101Ver
			) ' 
			|| ' SELECT * FROM dblink(''dbname=' || "PATH" 
			|| ''','
			|| 'SELECT CMnnID, NameRus, NameEng, OtherID, Active, UsedIn, ChemicalName, BruttoFormula, Characteristic, Pharmacology, Practice
			, Contraindications, Restrictions, UseOfPregnancy, SideEffects, Interaction, InjectionMethod, Caution, Overdose, CASCode, Indications
			, SingleDose, MaxDose, SingleDoseView, MaxDoseView, NameLatR, FedCode, RegCode, ehr.IdEHR, ehr.CurrVersion, ehr1.IdEHR, ehr1.CurrVersion
			FROM PHARM.T_C_MNN mnn
			left join lateral
				(
					SELECT er.IdEHR, rrb.CurrVersion 
					FROM EHR.T_EHR_RUS_DVL105 er
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks						
					WHERE er.EhrRusID = mnn.EhrRusDvl105
					order by rrb.CurrVersion desc
					limit 1
				) ehr on true
			left join lateral
				(
					SELECT er.IdEHR, rrb.CurrVersion 
					FROM EHR.T_EHR_RUS_NSV101 er
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks						
					WHERE er.EhrRusID = mnn.EhrRusNsv101
					order by rrb.CurrVersion desc
					limit 1
				) ehr1 on true
			'') '
			|| ' AS tt(CMnnID uuid, NameRus VARCHAR, NameEng VARCHAR, OtherID INT, Active boolean, UsedIn INT, ChemicalName VARCHAR
				, BruttoFormula VARCHAR, Characteristic VARCHAR, Pharmacology VARCHAR, Practice VARCHAR, Contraindications VARCHAR, Restrictions VARCHAR
				, UseOfPregnancy VARCHAR, SideEffects VARCHAR, Interaction VARCHAR, InjectionMethod VARCHAR, Caution VARCHAR, Overdose VARCHAR, CASCode VARCHAR
				, Indications VARCHAR, SingleDose DECIMAL, MaxDose DECIMAL, SingleDoseView INT, MaxDoseView INT, NameLatR VARCHAR, FedCode VARCHAR, RegCode VARCHAR, EhrRusDvl105 INT, EhrRusDvl105Ver VARCHAR(8), EhrRusNsv101 INT, EhrRusNsv101Ver VARCHAR(8))';

		EXECUTE _SQL;
		
		-- Первичная загрузка
		-- МНН не из справочника RDS переводим в неактивные
		UPDATE PHARM.T_C_MNN c
			SET Active = '0'
		WHERE NOT EXISTS
			(
				SELECT * 
					FROM Temp_C_MNN nc 
				WHERE nc.CMnnID = c.CMnnID
			)
			and c.UsedIn in (1, 3);

		-- Обновляем имеющиеся МНН
		update PHARM.T_C_MNN c
		set	NameEng = nc.NameEng
			,NameRus = nc.NameRus
			,NameLatR = nc.NameLatR
			,Active = nc.Active
			,OtherID = nc.OtherID
			,ChemicalName = nc.ChemicalName
			,BruttoFormula = nc.BruttoFormula
			,Characteristic = nc.Characteristic
			,Pharmacology = nc.Pharmacology
			,Practice = nc.Practice
			,Contraindications = nc.Contraindications
			,Restrictions = nc.Restrictions
			,UseOfPregnancy = nc.UseOfPregnancy
			,SideEffects = nc.SideEffects
			,Interaction = nc.Interaction
			,InjectionMethod = nc.InjectionMethod
			,Caution = nc.Caution
			,Overdose = nc.Overdose
			,CASCode = nc.CASCode
			,UsedIn = nc.UsedIn
			,RegCode = nc.RegCode
			,FedCode = nc.FedCode
			,EhrRusDvl105 = ehr.EhrRusID
			,EhrRusNsv101 = ehr1.EhrRusID
		from Temp_C_MNN nc 
			left join lateral 
			(
				select er.EhrRusID 
				from EHR.T_EHR_RUS_DVL105 er
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB 
						ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				where er.IdEHR = nc.EhrRusDvl105 and rrb.CurrVersion = nc.EhrRusDvl105Ver
				limit 1
			) ehr on true
			left join lateral
			(
				select er.EhrRusID 
				from EHR.T_EHR_RUS_NSV101 er
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB 
						ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				where er.IdEHR = nc.EhrRusNsv101 and rrb.CurrVersion = nc.EhrRusNsv101Ver
				limit 1
			) ehr1 on true
			where nc.CMnnID = c.CMnnID;
	--where c.UsedIn in (1,3)
	
		-- Загружаем новые
		INSERT INTO PHARM.T_C_MNN
		(
			CMnnID, 
			NameRus, 
			NameEng, 
			Active, 
			OtherID, 
			UsedIn, 
			ChemicalName, 
			BruttoFormula, 
			Characteristic, 
			Pharmacology, 
			Practice, 
			Contraindications, 
			Restrictions, 
			UseOfPregnancy, 
			SideEffects, 
			Interaction, 
			InjectionMethod, 
			Caution, 
			Overdose, 
			CASCode, 
			RegCode, 
			FedCode, 
			NameLatR, 
			EhrRusDvl105, 
			EhrRusNsv101
		)
		SELECT 
			nc.CMnnID, 
			nc.NameRus, 
			nc.NameEng, 
			nc.Active, 
			nc.OtherID, 
			nc.UsedIn, 
			nc.ChemicalName, 
			nc.BruttoFormula, 
			nc.Characteristic, 
			nc.Pharmacology, 
			nc.Practice, 
			nc.Contraindications, 
			nc.Restrictions, 
			nc.UseOfPregnancy, 
			nc.SideEffects, 
			nc.Interaction, 
			nc.InjectionMethod, 
			nc.Caution, 
			nc.Overdose, 
			nc.CASCode, 
			nc.RegCode, 
			nc.FedCode, 
			nc.NameLatR, 
			ehr.EhrRusID, 
			ehr1.EhrRusID
		FROM Temp_C_MNN nc
		left join lateral
		(
			select er.EhrRusID 
			from EHR.T_EHR_RUS_DVL105 er
			LEFT JOIN 
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = nc.EhrRusDvl105 and rrb.CurrVersion = nc.EhrRusDvl105Ver
			limit 1
		) ehr on true
		left join lateral
		(
			select er.EhrRusID 
			from EHR.T_EHR_RUS_NSV101 er
			LEFT JOIN 
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = nc.EhrRusNsv101 and rrb.CurrVersion = nc.EhrRusNsv101Ver
			limit 1
		) ehr1 on true
		WHERE NOT EXISTS
			(
				SELECT * 
					FROM PHARM.T_C_MNN c 
				WHERE c.CMnnID = nc.CMnnID
			);
	ELSE	
		if "Xml" is null
		then
			-- Инициализация
			_ContentEncoding := 'utf-8';
			_Type := 'C_MNN';
			_OwnerID := "ID";
			--выясняем тип операции
			if "ID" is null
			then
				_OperationType := _FTLOAD;
			elsif "IsNew" = '1'
			then
				_OperationType := _INSERT;
			elsif "IsNew" = '0' and exists(select* from Pharm.T_C_MNN c where c.CMnnID = "ID")
			then
				_OperationType := _UPDATE;
			elsif "IsNew" = '0' and not exists(select* from Pharm.T_C_MNN c where c.CMnnID = "ID")
			then
				_OperationType := _DELETE;
				_Result := XMLELEMENT(name "C_MNN", XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", "ID" as "ID")));
			end if;

			-- Обработка операции
			if _OperationType = _FTLOAD
			then	-- Первичная загрузка
				_Result :=
				XMLELEMENT
				(
					name "C_MNN"
				,	XMLELEMENT
					(
						name header
					,	XMLFOREST
						(
							_FTLOAD as "TYPE"
						)
					)
				,	XMLELEMENT
					(
						name body
					,	xmlelement
						(
							name "C_MNNS"
						,	xmlelement
							(
								name "C_MNN"
							,	(
									select	
									xmlagg
									(
										xmlforest
										(
											c.CMnnID			as "ID"
										,	c.Active			as "Active"
										,	c.NameRus			as "NameRus"
										,	c.NameEng			as "NameEng"
										,	c.OtherID			as "OtherID"
										,	c.ChemicalName		as "ChemicalName"
										,	c.BruttoFormula		as "BruttoFormula"
										,	c.Characteristic	as "Characteristic"
										,	c.Pharmacology		as "Pharmacology"
										,	c.Practice			as "Practice"
										,	c.Contraindications	as "Contraindications"
										,	c.Restrictions		as "Restrictions"
										,	c.UseOfPregnancy	as "UseOfPregnancy"
										,	c.SideEffects		as "SideEffects"
										,	c.Interaction		as "Interaction"
										,	c.InjectionMethod	as "InjectionMethod"
										,	c.Caution			as "Caution"
										,	c.Overdose			as "Overdose"
										,	c.CASCode			as "CASCode"
										,	c.UsedIn			as "UsedIn"
										,	c.RegCode			as "RegCode"
								  		,	c.FedCode			as "FedCode"
										,	er.IdEHR			as "EhrRusDvl105"
										,	RRB.CurrVersion		as "EhrRusDvl105Ver"
										,	er1.IdEHR			as "EhrRusNsv101"
										,	RRB1.CurrVersion	as "EhrRusNsv101Ver"
										)
									)
									from pharm.T_C_MNN c
									left join EHR.T_EHR_RUS_DVL105 er
										on er.EhrRusID = bg.EhrRusDvl105
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB 
										ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
									left join EHR.T_EHR_RUS_NSV101 er1
										on er1.EhrRusID = bg.EhrRusNsv101
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 
										ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
									where c.UsedIn in (1,3)
								)
							)
						)
					)
				);
			elsif _OperationType <> _DELETE
			then
				-- собираем C_MNN
				select 
				XMLELEMENT
				(
					name "C_MNN"
				,	XMLFOREST
					(
						XMLFOREST
						(
							_OperationType as "TYPE"
						,	"ID" as "ID"
						) as header
					)
				,	XMLFOREST
					(
						XMLFOREST
						(
							c.Active       		as "Active"
						,   c.NameRus      		as "NameRus"
						,   c.NameEng      		as "NameEng"
						,   c.NameLatR      	as "NameLatR"
						,   c.OtherID      		as "OtherID"
						,	c.ChemicalName		as "ChemicalName"
						,	c.BruttoFormula		as "BruttoFormula"
						,	c.Characteristic	as "Characteristic"
						,	c.Pharmacology		as "Pharmacology"
						,	c.Practice			as "Practice"
						,	c.Contraindications	as "Contraindications"
						,	c.Restrictions		as "Restrictions"
						,	c.UseOfPregnancy	as "UseOfPregnancy"
						,	c.SideEffects		as "SideEffects"
						,	c.Interaction		as "Interaction"
						,	c.InjectionMethod	as "InjectionMethod"
						,	c.Caution			as "Caution"
						,	c.Overdose			as "Overdose"
						,	c.CASCode			as "CASCode"
						,	c.UsedIn			as "UsedIn"
						,	c.RegCode			as "RegCode"
						,	c.FedCode			as "FedCode"
						,	er.IdEHR			as "EhrRusDvl105"
						,	RRB.CurrVersion		as "EhrRusDvl105Ver"
						,	er1.IdEHR			as "EhrRusNsv101"
						,	RRB1.CurrVersion	as "EhrRusNsv101Ver"
						) as body
					)
				)
				into _Result
				from pharm.T_C_MNN c
				left join EHR.T_EHR_RUS_DVL105 er
					on er.EhrRusID = bg.EhrRusDvl105
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB 
					ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				left join EHR.T_EHR_RUS_NSV101 er1
					on er1.EhrRusID = bg.EhrRusNsv101
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 
					ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
				where c.CMnnID = "ID"
				and c.UsedIn in (1,3);
			END if;
			-- Отправка
			INSERT INTO akuz.T_RABBIT_SEND_OBJECT
				(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
			select 
				uuid_generate_v4(),	
				"QueueName", 
				ConvertTextToBinaryZip(_Result, ''), 
				null, 
				null, 
				_ContentEncoding, 
				_Type, 
				"User", 
				0, 
				"dateTime", 
				_OwnerID;
		else 
			if "Xml" is not null
			then
					-- Получаем тип операции
				_OperationType := (xpath('/C_MNN/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(6);

				-- Generate table Tmp_C_MNNS
				Drop Table If Exists Tmp_C_MNNS;
				Create Temp Table Tmp_C_MNNS
				as
				SELECT
					(xpath('/C_MNN/ID[1]/text()', x))[1]::text::UUID as ID
				,	(xpath('/C_MNN/Active[1]/text()', x))[1]::text::boolean as Active
				,	(xpath('/C_MNN/NameRus[1]/text()', x))[1]::varchar(1000) as NameRus
				,	(xpath('/C_MNN/NameEng[1]/text()', x))[1]::varchar(1000) as NameEng
				,	(xpath('/C_MNN/NameLatR[1]/text()', x))[1]::varchar(4000) as NameLatR
				,	(xpath('/C_MNN/OtherID[1]/text()', x))[1]::varchar(255) as OtherId
				,	(xpath('/C_MNN/ChemicalName[1]/text()', x))[1]::varchar(600) as ChemicalName
				,	(xpath('/C_MNN/BruttoFormula[1]/text()', x))[1]::varchar(100) as BruttoFormula
				,	(xpath('/C_MNN/Characteristic[1]/text()', x))[1]::varchar(4000) as Characteristic
				,	(xpath('/C_MNN/Pharmacology[1]/text()', x))[1]::text as Pharmacology
				,	(xpath('/C_MNN/Practice[1]/text()', x))[1]::varchar(6000) as Practice
				,	(xpath('/C_MNN/Contraindications[1]/text()', x))[1]::varchar(4000) as Contraindications
				,	(xpath('/C_MNN/Restrictions[1]/text()', x))[1]::text as Restrictions
				,	(xpath('/C_MNN/UseOfPregnancy[1]/text()', x))[1]::text as UseOfPregnancy
				,	(xpath('/C_MNN/SideEffects[1]/text()', x))[1]::text as SideEffects
				,	(xpath('/C_MNN/Interaction[1]/text()', x))[1]::text as Interaction
				,	(xpath('/C_MNN/InjectionMethod[1]/text()', x))[1]::varchar(4000) as InjectionMethod
				,	(xpath('/C_MNN/Caution[1]/text()', x))[1]::text as Caution
				,	(xpath('/C_MNN/Overdose[1]/text()', x))[1]::varchar(4000) as Overdose
				,	(xpath('/C_MNN/CASCode[1]/text()', x))[1]::varchar(50) as CASCode
				,	(xpath('/C_MNN/UsedIn[1]/text()', x))[1]::text::int as UsedIn
				,	(xpath('/C_MNN/RegCode[1]/text()', x))[1]::varchar(50) as RegCode
				,	(xpath('/C_MNN/FedCode[1]/text()', x))[1]::varchar(50) as FedCode
				,	(xpath('/C_MNN/EhrRusDvl105[1]/text()', t.x))[1]::text::int as EhrRusDvl105
				,	(xpath('/C_MNN/EhrRusDvl105Ver[1]/text()', t.x))[1]::varchar(8) as EhrRusDvl105Ver
				,	(xpath('/C_MNN/EhrRusNsv101[1]/text()', t.x))[1]::text::int as EhrRusNsv101
				,	(xpath('/C_MNN/EhrRusNsv101Ver[1]/text()', t.x))[1]::varchar(8) as EhrRusNsv101Ver
				from unnest(xpath('/C_MNN/body/C_MNNS/C_MNN', "Xml")) as x
				where _OperationType = _FTLOAD;			

				-- Generate table Tmp_C_MNN
				Drop Table If Exists Tmp_C_MNN;
				Create Temp Table Tmp_C_MNN
				as
				SELECT	(xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::UUID as ID
				,	(xpath('/body[1]/Active[1]/text()', t.x2))[1]::text::boolean as Active
				,	(xpath('/body[1]/NameRus[1]/text()', t.x2))[1]::varchar(1000) as NameRus
				,	(xpath('/body[1]/NameEng[1]/text()', t.x2))[1]::varchar(1000) as NameEng
				,	(xpath('/body[1]/NameLatR[1]/text()', t.x2))[1]::varchar(4000) as NameLatR
				,	(xpath('/body[1]/OtherID[1]/text()', t.x2))[1]::varchar(255) as OtherID
				,	(xpath('/body[1]/ChemicalName[1]/text()', t.x2))[1]::varchar(600) as ChemicalName
				,	(xpath('/body[1]/BruttoFormula[1]/text()', t.x2))[1]::varchar(100) as BruttoFormula
				,	(xpath('/body[1]/Characteristic[1]/text()', t.x2))[1]::varchar(4000) as Characteristic
				,	(xpath('/body[1]/Pharmacology[1]/text()', t.x2))[1]::text as Pharmacology
				,	(xpath('/body[1]/Practice[1]/text()', t.x2))[1]::varchar(6000) as Practice
				,	(xpath('/body[1]/Contraindications[1]/text()', t.x2))[1]::varchar(4000) as Contraindications
				,	(xpath('/body[1]/Restrictions[1]/text()', t.x2))[1]::text as Restrictions
				,	(xpath('/body[1]/UseOfPregnancy[1]/text()', t.x2))[1]::text as UseOfPregnancy
				,	(xpath('/body[1]/SideEffects[1]/text()', t.x2))[1]::text as SideEffects
				,	(xpath('/body[1]/Interaction[1]/text()', t.x2))[1]::text as Interaction
				,	(xpath('/body[1]/InjectionMethod[1]/text()', t.x2))[1]::varchar(4000) as InjectionMethod
				,	(xpath('/body[1]/Caution[1]/text()', t.x2))[1]::text as Caution
				,	(xpath('/body[1]/Overdose[1]/text()', t.x2))[1]::varchar(4000) as Overdose
				,	(xpath('/body[1]/CASCode[1]/text()', t.x2))[1]::varchar(50) as CASCode
				,	(xpath('/body[1]/UsedIn[1]/text()', t.x2))[1]::text::int as UsedIn
				,	(xpath('/body[1]/RegCode[1]/text()', t.x2))[1]::varchar(50) as RegCode
				,	(xpath('/body[1]/FedCode[1]/text()', t.x2))[1]::varchar(50) as FedCode
				,	(xpath('/body[1]/EhrRusDvl105[1]/text()', t.x2))[1]::text::int as EhrRusDvl105
				,	(xpath('/body[1]/EhrRusDvl105Ver[1]/text()', t.x2))[1]::varchar(8) as EhrRusDvl105Ver
				,	(xpath('/body[1]/EhrRusNsv101[1]/text()', t.x2))[1]::text::int as EhrRusNsv101
				,	(xpath('/body[1]/EhrRusNsv101Ver[1]/text()', t.x2))[1]::varchar(8) as EhrRusNsv101Ver
				from
				(
					select	unnest(xpath('/C_MNN/header', x)) as x1
					,	unnest(xpath('/C_MNN/body', x)) as x2
					from unnest(xpath('/C_MNN', "Xml"::xml)) x
				) t
				where _OperationType <> _FTLOAD;

				-- Обработка операции
				if _OperationType = _FTLOAD
				then
					-- Первичная загрузка
					-- МНН не из справочника RDS переводим в неактивные
					update pharm.T_C_MNN c
					set Active = '0'
					where not exists
						(
							select * 
							from Tmp_C_MNNS nc 
							where nc.ID = c.CMnnID
						)
					and c.UsedIn in (1,3);

					-- Обновляем имеющиеся МНН
					update pharm.T_C_MNN c
					set	NameEng				= nc.NameEng
						,NameRus			= nc.NameRus
						,NameLatR			= nc.NameLatR
						,OtherID			= nc.OtherID
						,Active				= nc.Active				
						,ChemicalName		= nc.ChemicalName
						,BruttoFormula		= nc.BruttoFormula
						,Characteristic		= nc.Characteristic
						,Pharmacology		= nc.Pharmacology
						,Practice			= nc.Practice
						,Contraindications	= nc.Contraindications
						,Restrictions		= nc.Restrictions
						,UseOfPregnancy		= nc.UseOfPregnancy
						,SideEffects		= nc.SideEffects
						,Interaction		= nc.Interaction
						,InjectionMethod	= nc.InjectionMethod
						,Caution			= nc.Caution
						,Overdose			= nc.Overdose
						,CASCode			= nc.CASCode
						,UsedIn				= nc.UsedIn
						,RegCode			= nc.RegCode
			   			,FedCode			= nc.FedCode
			   			,EhrRusDvl105		= ehr.EhrRusID
			   			,EhrRusNsv101		= ehr1.EhrRusID
					from Tmp_C_MNNS nc 
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_DVL105 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = nc.EhrRusDvl105 and RRB.CurrVersion = nc.EhrRusDvl105Ver
						limit 1
					) ehr on true
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_NSV101 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = nc.EhrRusNsv101 and RRB.CurrVersion = nc.EhrRusNsv101Ver
						limit 1
					) ehr1 on true
					where nc.ID = c.CMnnID;

					-- Загружаем новые
					INSERT INTO pharm.T_C_MNN
						(
							CMnnID, 
							NameRus, 
							NameEng, 
							Active, 
							OtherID, 
							ChemicalName, 
							BruttoFormula, 
							Characteristic, 
							Pharmacology, 
							Practice, 
							Contraindications, 
							Restrictions, 
							UseOfPregnancy, 
							SideEffects, 
							Interaction, 
							InjectionMethod, 
							Caution, 
							Overdose, 
							CASCode, 
							UsedIn, 
							RegCode, 
							FedCode, 
							NameLatR, 
							EhrRusDvl105, 
							EhrRusNsv101
						)
					SELECT 
						nc.ID, 
						nc.NameRus, 
						nc.NameEng, 
						nc.Active, 
						nc.OtherID, 
						nc.ChemicalName, 
						nc.BruttoFormula, 
						nc.Characteristic,
						nc.Pharmacology, 
						nc.Practice, 
						nc.Contraindications, 
						nc.Restrictions, 
						nc.UseOfPregnancy, 
						nc.SideEffects, 
						nc.Interaction, 
						nc.InjectionMethod, 
						nc.Caution, 
						nc.Overdose, 
						nc.CASCode, 
						nc.UsedIn, 
						nc.RegCode, 
						nc.FedCode, 
						nc.NameLatR, 
						ehr.EhrRusID, 
						ehr1.EhrRusID
					from Tmp_C_MNNS nc
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_DVL105 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = nc.EhrRusDvl105 and RRB.CurrVersion = nc.EhrRusDvl105Ver
						limit 1
					) ehr on true
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_NSV101 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = nc.EhrRusNsv101 and RRB1.CurrVersion = nc.EhrRusNsv101Ver
						limit 1
					) ehr1 on true
					where not exists
						(
							select * 
							from pharm.T_C_MNN c 
							where c.CMnnID = nc.ID
						);
				elsif _OperationType = _DELETE
				then
					-- Удаление
					update pharm.T_C_MNN c
					set Active = '0'
					from Tmp_C_MNN nc 					
					where nc.ID = c.CMnnID;
				else
					-- update pharm.T_C_MNN
					update pharm.T_C_MNN c
					set	NameEng = nc.NameEng
						,NameRus = nc.NameRus
						,NameLatR = nc.NameLatR
						,Active = nc.Active
						,OtherID = nc.OtherID
						,ChemicalName = nc.ChemicalName
						,BruttoFormula = nc.BruttoFormula
						,Characteristic = nc.Characteristic
						,Pharmacology = nc.Pharmacology
						,Practice = nc.Practice
						,Contraindications = nc.Contraindications
						,Restrictions = nc.Restrictions
						,UseOfPregnancy = nc.UseOfPregnancy
						,SideEffects = nc.SideEffects
						,Interaction = nc.Interaction
						,InjectionMethod = nc.InjectionMethod
						,Caution = nc.Caution
						,Overdose = nc.Overdose
						,CASCode = nc.CASCode
						,UsedIn = nc.UsedIn
						,RegCode = nc.RegCode
			   			,FedCode = nc.FedCode
						,EhrRusDvl105 = ehr.EhrRusID
						,EhrRusNsv101 = ehr1.EhrRusID
					from Tmp_C_MNN nc 		
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_DVL105 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = nc.EhrRusDvl105 and RRB.CurrVersion = nc.EhrRusDvl105Ver
						limit 1
					) ehr on true
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_NSV101 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = nc.EhrRusNsv101 and RRB1.CurrVersion = nc.EhrRusNsv101Ver
						limit 1
					) ehr1 on true
					where nc.ID = c.CMnnID
					and _OperationType = _UPDATE;

					-- insert pharm.T_C_MNN
					INSERT INTO pharm.T_C_MNN
						(CMnnID, NameRus, NameEng, Active, OtherID, ChemicalName, BruttoFormula, Characteristic, Pharmacology, Practice, Contraindications, 
						   Restrictions, UseOfPregnancy, SideEffects, Interaction, InjectionMethod, Caution, Overdose, CASCode, UsedIn, RegCode, FedCode, NameLatR, EhrRusDvl105, EhrRusNsv101)
					SELECT 
						nc.ID, 
						nc.NameRus, 
						nc.NameEng, 
						nc.Active, 
						nc.OtherID, 
						nc.ChemicalName, 
						nc.BruttoFormula, 
						nc.Characteristic, 
						nc.Pharmacology, 
						nc.Practice, 
						nc.Contraindications, 
						nc.Restrictions, 
						nc.UseOfPregnancy, 
						nc.SideEffects, 
						nc.Interaction, 
						nc.InjectionMethod, 
						nc.Caution, 
						nc.Overdose, 
						nc.CASCode, 
						nc.UsedIn, 
						nc.RegCode, 
						nc.FedCode, 
						nc.NameLatR, 
						ehr.EhrRusID, 
						ehr1.EhrRusID
					from Tmp_C_MNN nc
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_DVL105 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = nc.EhrRusDvl105 and rrb.CurrVersion = nc.EhrRusDvl105Ver
						limit 1
					) ehr on true
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_NSV101 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = nc.EhrRusNsv101 and rrb.CurrVersion = nc.EhrRusNsv101Ver
						limit 1
					) ehr1 on true
					where not exists
						(
							select * 
							from pharm.T_C_MNN c 
							where c.CMnnID = nc.ID) 
					and _OperationType = _INSERT;
				END if;
				
				_EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'C_MNN' limit 1);
				if exists 
				(
					select * from AKUZ.T_GEN_NUMBERS 
						where EntityID = _EntId and NumberName = 'PHARM.C_MNN' Limit 1
				)
				then
					---- генерируем след. номер
					--perform public.sp_GenerateNumber(@EntId, 'PHARM.C_MNN');
						
					update AKUZ.T_GEN_NUMBERS
					set NextNumber = (select MAX(OtherID) from PHARM.T_C_MNN) --+ 1
					where EntityID = _EntId and NumberName = 'PHARM.C_MNN';
				else
					INSERT INTO AKUZ.T_GEN_NUMBERS
						(GenNumbersID, EntityID, NumberName, NextNumber)
					VALUES
						(uuid_generate_v4(), _EntId, 'PHARM.C_MNN', coalesce((select MAX(OtherID) from PHARM.T_C_MNN), 1));
				end if;
				-- Drop tables
				Drop Table If Exists Tmp_C_MNN;
				Drop Table If Exists Tmp_C_MNNS;
			end if;
		END if;
	end if;
End;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.sp_rds_cmnn(xml, boolean, uuid, varchar(100), timestamp, uuid, text)
  OWNER TO postgres; 