-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-22
-- Description: Выгрузка/загрузка справочника Торговое наименование препарата (ТРН) в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS DBO.sp_rds_ctrn;

DROP PROCEDURE IF EXISTS APS.sp_rds_ctrn;

CREATE OR REPLACE PROCEDURE APS.sp_rds_ctrn
(
	"Xml" text DEFAULT null
,	"IsNew" boolean DEFAULT '1'
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" character varying DEFAULT 'RDSSync'::character varying
,	"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
,	"User" uuid DEFAULT NULL::uuid
,	"PATH"  character varying DEFAULT null::character varying
)
    language plpgsql
as
$$
Begin

	Declare	_Result text;
		_ContentEncoding varchar(255) := 'utf-8';
		_Type varchar(255) := 'C_TRN';
		_OperationType varchar(255);
		_OwnerID UUID := "ID";
		-- Operation types
		_INSERT varchar(6) := 'Insert';
		_UPDATE varchar(6) := 'Update';
		_DELETE varchar(6) := 'Delete';
		_FTLOAD varchar(6) := 'FTLoad';
		_sql text;
		_EntId int;
	Begin
		"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

		IF "PATH" IS NOT NULL
		then
			DROP TABLE IF EXISTS Temp_C_TRN;
			CREATE TEMP TABLE Temp_C_TRN
				(
					CTrnID uuid, 
					OtherID					INT, 
					NameRus					VARCHAR(4000), 
					NameEng					VARCHAR(4000), 
					Active					boolean, 
					Characteristic			VARCHAR(4000), 
					PharmacologyAction		VARCHAR(4000), 
					Practice				text, 
					Contraindications		VARCHAR(5000), 
					Restrictions			text, 
					UseOfPregnancy			text, 
					SideEffects				text, 
					Interaction				text, 
					Overdose				text, 
					InjectionMethod			VARCHAR(255), 
					Caution					text, 
					Composition				VARCHAR(3000), 
					PharmDescription		text, 
					Pharmacodynamics		text, 
					Indications				VARCHAR(8000), 
					ExpirationDate			VARCHAR(300), 
					MNNName					text, 
					Manufacturer			VARCHAR(3000), 
					CMnn					uuid,
					ProductType				INT, 
					NameLatR				VARCHAR(4000), 
					FedCode					VARCHAR(50), 
					RegCode					VARCHAR(50), 
					EhrRusP08TSH954N		VARCHAR(52), 
					EhrRusP08TSH954NVer		VARCHAR(8), 
					EhrRus575VSF88OG		VARCHAR(52), 
					EhrRus575VSF88OGVer		VARCHAR(8)
					,Atc					int
					,AtcVer					VARCHAR(8)
				);
				
			_sql := 'insert into Temp_C_TRN
						(
							CTrnID, 
							OtherID, 
							NameRus, 
							NameEng, 
							Active, 
							Characteristic, 
							PharmacologyAction, 
							Practice, 
							Contraindications, 
							Restrictions, 
							UseOfPregnancy, 
							SideEffects, 
							Interaction, 
							Overdose, 
							InjectionMethod, 
							Caution, 
							Composition, 
							PharmDescription, 
							Pharmacodynamics, 
							Indications, 
							ExpirationDate, 
							MNNName, 
							Manufacturer, 
							CMnn, 
							ProductType, 
							NameLatR, 
							FedCode, 
							RegCode, 
							EhrRusP08TSH954N, 
							EhrRusP08TSH954NVer, 
							EhrRus575VSF88OG, 
							EhrRus575VSF88OGVer,
							Atc, 
							AtcVer
						); ' 
				|| ' SELECT trn.CTrnID, 
							trn.OtherID, 
							trn.NameRus, 
							trn.NameEng, 
							trn.Active, 
							trn.Characteristic, 
							trn.PharmacologyAction, 
							trn.Practice, 
							trn.Contraindications, 
							trn.Restrictions, 
							trn.UseOfPregnancy, 
							trn.SideEffects, 
							trn.Interaction, 
							trn.Overdose, 
							trn.InjectionMethod, 
							trn.Caution, 
							trn.Composition, 
							trn.PharmDescription, 
							trn.Pharmacodynamics, 
							trn.Indications, 
							trn.ExpirationDate, 
							trn.MNNName, 
							trn.Manufacturer, 
							trn.CMnn, 
							trn.ProductType, 
							trn.NameLatR, 
							trn.FedCode, 
							trn.RegCode, 
							trn.EhrRusP08TSH954N, 
							trn.EhrRusP08TSH954NVer, 
							trn.EhrRus575VSF88OG, 
							trn.EhrRus575VSF88OGVer,
							trn.Atc, 
							trn.AtcVer '
				|| 'FROM dblink(''dbname=' || "PATH" 
				|| ''','
				|| ''' SELECT nc.CTrnID, nc.OtherID, nc.NameRus, nc.NameEng, nc.Active, nc.Characteristic, nc.PharmacologyAction, nc.Practice
				, nc.Contraindications, nc.Restrictions, nc.UseOfPregnancy, nc.SideEffects, nc.Interaction, nc.Overdose, nc.InjectionMethod, nc.Caution
				, nc.Composition, nc.PharmDescription, nc.Pharmacodynamics, nc.Indications, nc.ExpirationDate, nc.MNNName, nc.Manufacturer, nc.CMnn,
				, nc.ProductType, nc.NameLatR, nc.FedCode, nc.RegCode, eh1.IdEHR, eh1.CurrVersion, eh2.IdEHR, eh2.CurrVersion, atc.IDEHR, atc.IDEHR as Atc, ac.CurrVersion as AtcVer
				FROM PHARM.T_C_TRN nc
				left join lateral
				(
					SELECT EH.IDEHR, rrb.CurrVersion 
					FROM EHR.T_EHR_RUS_P08TSH954N EH
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					WHERE NC.EHRRUSP08TSH954N = EH.EHRRUSID
					limit 1
				) EH1 on true
				left join lateral
				(
					SELECT EH.IDEHR, RRB.CurrVersion 
					FROM EHR.T_EHR_RUS_575VSF88OG EH
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					WHERE NC.EHRRUS575VSF88OG = EH.EHRRUSID
					limit 1
				) EH2 on true 
				left join lateral
				(
					SELECT EH.IDEHR, rrb.CurrVersion 
					from EHR.T_EHR_RUS_M00002 EH
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
					limit 1
				) atc on true
				'') ' 
					|| ' AS trn(CTrnID uuid, 
					OtherID					INT, 
					NameRus					VARCHAR(4000), 
					NameEng					VARCHAR(4000), 
					Active					boolean, 
					Characteristic			VARCHAR(4000), 
					PharmacologyAction		VARCHAR(4000), 
					Practice				text, 
					Contraindications		VARCHAR(5000), 
					Restrictions			text, 
					UseOfPregnancy			text, 
					SideEffects				text, 
					Interaction				text, 
					Overdose				text, 
					InjectionMethod			VARCHAR(255), 
					Caution					text, 
					Composition				VARCHAR(3000), 
					PharmDescription		text, 
					Pharmacodynamics		text, 
					Indications				VARCHAR(8000), 
					ExpirationDate			VARCHAR(300), 
					MNNName					text, 
					Manufacturer			VARCHAR(3000), 
					CMnn					uuid,
					ProductType				INT, 
					NameLatR				VARCHAR(4000), 
					FedCode					VARCHAR(50), 
					RegCode					VARCHAR(50), 
					EhrRusP08TSH954N		VARCHAR(52), 
					EhrRusP08TSH954NVer		VARCHAR(8), 
					EhrRus575VSF88OG		VARCHAR(52), 
					EhrRus575VSF88OGVer		VARCHAR(8)
					,Atc					int
					,AtcVer					VARCHAR(8)) ';

			EXECUTE _SQL;
				
			-- МНН не из справочника RDS переводим в неактивные
			UPDATE PHARM.T_C_TRN c
			SET Active = '0'
			WHERE NOT EXISTS
				(
					SELECT * 
						FROM Temp_C_TRN nc 
					WHERE nc.CTrnID = c.CTrnID
				);
		
			-- Обновляем имеющиеся TRN
			update PHARM.T_C_TRN c
			set NameEng = nc.NameEng
				,NameRus = nc.NameRus
				,NameLatR = nc.NameLatR
				,Active = nc.Active
				,OtherID = nc.OtherID
				,Characteristic = nc.Characteristic
				,PharmacologyAction = nc.PharmacologyAction
				,Practice = nc.Practice
				,Contraindications = nc.Contraindications
				,Restrictions = nc.Restrictions
				,UseOfPregnancy = nc.UseOfPregnancy
				,SideEffects = nc.SideEffects
				,Interaction = nc.Interaction
				,Overdose = nc.Overdose
				,InjectionMethod = nc.InjectionMethod
				,Caution = nc.Caution
				,Composition = nc.Composition
				,PharmDescription = nc.PharmDescription
				,Pharmacodynamics = nc.Pharmacodynamics
				,Indications = nc.Indications
				,ExpirationDate = nc.ExpirationDate
				,MNNName = nc.MNNName
				,Manufacturer = nc.Manufacturer
				,CMnn = nc.CMnn
				,c.Atc = atc.EhrRusID
				,ProductType = nc.ProductType
				,RegCode = nc.RegCode
				,FedCode = nc.FedCode
				,EhrRusP08TSH954N = eh1.EhrRusID
				,EhrRus575VSF88OG = eh2.EhrRusID
			from Temp_C_TRN nc
			left join lateral
			(
				select eh.EhrRusID 
				from EHR.T_EHR_RUS_P08TSH954N eh
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
				where eh.CodeEHR = nc.EhrRusP08TSH954N and rrb.CurrVersion = nc.EhrRusP08TSH954NVer
				limit 1
			) eh1 on true
			left join lateral
			(
				select eh.EhrRusID 
				from EHR.T_EHR_RUS_575VSF88OG eh
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
				where eh.CodeEHR = nc.EhrRus575VSF88OG and rrb.CurrVersion = nc.EhrRus575VSF88OGVer
				limit 1
			) eh2 on true
			left join lateral
			(
				select eh.EhrRusID 
				from EHR.T_EHR_RUS_M00002 eh
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
				where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
				limit 1
			) atc on true
			where nc.CTrnID = c.CTrnID;
		
			-- Загружаем новые
			INSERT INTO PHARM.T_C_TRN
				(
					CTrnID, 
					NameRus, 
					NameEng, 
					Active, 
					OtherID, 
					Characteristic, 
					PharmacologyAction, 
					Practice, 
					Contraindications, 
					Restrictions, 
					UseOfPregnancy, 
					SideEffects, 
					Interaction, 
					Overdose, 
					InjectionMethod, 
					Caution, 
					Composition, 
					PharmDescription, 
					Pharmacodynamics, 
					Indications, 
					ExpirationDate,
					MNNName, 
					Manufacturer,
					CMnn, 
					Atc, 
					ProductType, 
					RegCode, 
					FedCode, 
					EhrRusP08TSH954N, 
					EhrRus575VSF88OG, 
					NameLatR
				)
				SELECT 
					nc.CTrnID, 
					nc.NameRus, 
					nc.NameEng, 
					nc.Active, 
					nc.OtherID, 
					nc.Characteristic, 
					nc.PharmacologyAction, 
					nc.Practice, 
					nc.Contraindications,
					nc.Restrictions, 
					nc.UseOfPregnancy, 
					nc.SideEffects, 
					nc.Interaction, 
					nc.Overdose, 
					nc.InjectionMethod, 
					nc.Caution, 
					nc.Composition, 
					nc.PharmDescription, 
					nc.Pharmacodynamics, 
					nc.Indications, 
					nc.ExpirationDate, 
					nc.MNNName, 
					nc.Manufacturer, 
					nc.CMnn, 
					atc.EhrRusID, 
					nc.ProductType, 
					nc.RegCode, 
					nc.FedCode
			, eh1.EhrRusID, eh2.EhrRusID, nc.NameLatR
			from Temp_C_TRN nc
			left join lateral
			(
				select eh.EhrRusID 
					from EHR.T_EHR_RUS_P08TSH954N eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRusP08TSH954N and rrb.CurrVersion = nc.EhrRusP08TSH954NVer
				limit 1
			) eh1 on true
			left join lateral 
			(
				select eh.EhrRusID 
					from EHR.T_EHR_RUS_575VSF88OG eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRus575VSF88OG and rrb.CurrVersion = nc.EhrRus575VSF88OGVer
				limit 1
			) eh2 on true
			left join lateral 
			(
				select eh.EhrRusID 
					from EHR.T_EHR_RUS_575VSF88OG eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
				limit 1
			) atc on true
			where not exists
				(
					select * 
						from PHARM.T_C_TRN c 
					where c.CTrnID = nc.CTrnID
				);
		ELSE
			if "Xml" is null
			then
				IF "ID" is null
				then
					_OperationType := _FTLOAD;
				elsif "IsNew" = '1'
				then
					_OperationType := _INSERT;
				elsif "IsNew" = '0' and exists(select* from Pharm.T_C_TRN c where c.CTrnID = "ID")
				then
					_OperationType := _UPDATE;
				elsif "IsNew" = '0' and not exists(select* from Pharm.T_C_TRN c where c.CTrnID = "ID")
				then
					_OperationType := _DELETE;
					_Result := XMLELEMENT(name "C_TRN", XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", "ID" as "ID")));
				end if;

				-- Обработка операции
				if _OperationType = _FTLOAD
				then
					-- Первичная загрузка
					_Result := XMLELEMENT
						  (
							name "C_TRN"
						  ,	XMLELEMENT
							(
								name header
							,	XMLFOREST(_FTLOAD as "TYPE")
							)
						  ,	XMLELEMENT
							(
								name body
							,	xmlelement
								(
									name "C_TRNS"
								,	xmlelement
									(
										name "C_TRN"
									,	(
											select 
											xmlagg
											(
												XMLFOREST
												(
													c.CTrnID				as "ID"
													,c.Active				as "Active"
													,c.NameRus				as "NameRus"
													,c.NameEng				as "NameEng"
													,c.NameLatR				as "NameLatR"
													,c.OtherID				as "OtherID"
													,c.Characteristic     	as "Characteristic"
													,c.PharmacologyAction 	as "PharmacologyAction"
													,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.Overdose				as "Overdose"
													,c.InjectionMethod		as "InjectionMethod"
													,c.Caution				as "Caution"
													,c.Composition			as "Composition"
													,c.PharmDescription		as "PharmDescription"
													,c.Pharmacodynamics		as "Pharmacodynamics"
													,c.Indications			as "Indications"
													,c.ExpirationDate		as "ExpirationDate"
													,c.MNNName				as "MNNName"
													,c.Manufacturer			as "Manufacturer"
													,c.CMnn					as "CMnn"
													,c.ProductType			as "ProductType"
													,c.RegCode				as "RegCode"
									  				,c.FedCode				as "FedCode"
									  				,ehr1.CodeEHR			as "EhrRusP08TSH954N"
									  				,RRB.CurrVersion		as "EhrRusP08TSH954NVer"
									  				,ehr2.CodeEHR			as "EhrRus575VSF88OG"
									  				,RRB1.CurrVersion		as "EhrRus575VSF88OGVer"
													,atc.IdEHR				as "Atc"
													,RRBAtc.CurrVersion		as "AtcVer"
												)
											)
											from pharm.T_C_TRN c
												left join EHR.T_EHR_RUS_P08TSH954N ehr1 on ehr1.EhrRusID = c.EhrRusP08TSH954N
												left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr1.EhrRusRefBooks
												left join EHR.T_EHR_RUS_575VSF88OG ehr2 on ehr2.EhrRusID = c.EhrRus575VSF88OG
												left join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = ehr2.EhrRusRefBooks
												left join EHR.T_EHR_RUS_M00002 atc on atc.EhrRusID = c.Atc
												left join EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = atc.EhrRusRefBooks
										)
									)
								)
							)
						  );	
				elsif _OperationType <> _DELETE
				then
					-- собираем C_TRN
					select 
					XMLELEMENT
					(
						name "C_TRN"
					,	XMLELEMENT
						(
							name header
						,	XMLFOREST
							(
								_OperationType as "TYPE"
							,	"ID" as "ID"
							)
						)
					,	XMLELEMENT
						(
							name body
						,	XMLFOREST
							(
								c.Active					as "Active"
							,   c.NameRus					as "NameRus"
							,   c.NameEng					as "NameEng"
							,   c.NameLatR					as "NameLatR"
							,   c.OtherID					as "OtherID"
							,	c.Characteristic			as "Characteristic"
							,	c.PharmacologyAction		as "PharmacologyAction"
							,	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.Overdose					as "Overdose"
							,	c.InjectionMethod			as "InjectionMethod"
							,	c.Caution					as "Caution"
							,	c.Composition				as "Composition"
							,	c.PharmDescription			as "PharmDescription"
							,	c.Pharmacodynamics			as "Pharmacodynamics"
							,	c.Indications				as "Indications"
							,	c.ExpirationDate			as "ExpirationDate"
							,	c.MNNName					as "MNNName"
							,	c.Manufacturer				as "Manufacturer"
							,	c.CMnn						as "CMnn"
							,	c.ProductType				as "ProductType"
							,	c.RegCode					as "RegCode"
							,	c.FedCode					as "FedCode"
							,	ehr1.CodeEHR				as "EhrRusP08TSH954N"
							,	RRB.CurrVersion				as "EhrRusP08TSH954NVer"
							,	ehr2.CodeEHR				as "EhrRus575VSF88OG"
							,	RRB1.CurrVersion			as "EhrRus575VSF88OGVer"
							,	atc.IdEHR					as "Atc"
							,	RRBAtc.CurrVersion			as "AtcVer"
							)
						)
					)				
					from pharm.T_C_TRN c
						left join EHR.T_EHR_RUS_P08TSH954N ehr1 on ehr1.EhrRusID = c.EhrRusP08TSH954N
						left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr1.EhrRusRefBooks
						left join EHR.T_EHR_RUS_575VSF88OG ehr2 on ehr2.EhrRusID = c.EhrRus575VSF88OG
						left join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = ehr2.EhrRusRefBooks
						left join EHR.T_EHR_RUS_M00002 atc on atc.EhrRusID = c.Atc
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = atc.EhrRusRefBooks
					into _Result
					where c.CTrnID = "ID";
				end if;

				-- Отправка
				INSERT INTO APS.T_RABBIT_SEND_OBJECT
					(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
				select 
					gen_random_uuid(), "QueueName", ConvertTextToBinaryZip(_Result, ''), null, null, _ContentEncoding, _Type, "User", 0, "dateTime", _OwnerID;
			else
				if "Xml" is not null
				then      
					-- Получаем тип операции
					_OperationType := (xpath('/C_TRN/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);

					-- Generate table Tmp_C_TRNS
					Drop Table If Exists Tmp_C_TRNS;
					Create Temp Table Tmp_C_TRNS
					as 
					select	(xpath('/C_TRN/ID[1]/text()', x))[1]::text::UUID as ID
					,	(xpath('/C_TRN/Active[1]/text()', x))[1]::text::boolean as Active
					,	(xpath('/C_TRN/NameRus[1]/text()', x))[1]::varchar(4000) as NameRus
					,	(xpath('/C_TRN/NameEng[1]/text()', x))[1]::varchar(4000) as NameEng
					,	(xpath('/C_TRN/NameLatR[1]/text()', x))[1]::varchar(4000) as NameLatR
					,	(xpath('/C_TRN/OtherID[1]/text()', x))[1]::text::int as OtherId
					,	(xpath('/C_TRN/Characteristic[1]/text()', x))[1]::varchar(4000) as Characteristic
					,	(xpath('/C_TRN/PharmacologyAction[1]/text()', x))[1]::varchar(4000) as PharmacologyAction
					,	(xpath('/C_TRN/Practice[1]/text()', x))[1]::text as Practice
					,	(xpath('/C_TRN/Contraindications[1]/text()', x))[1]::varchar(4000) as Contraindications
					,	(xpath('/C_TRN/Restrictions[1]/text()', x))[1]::text as Restrictions
					,	(xpath('/C_TRN/UseOfPregnancy[1]/text()', x))[1]::text as UseOfPregnancy
					,	(xpath('/C_TRN/SideEffects[1]/text()', x))[1]::text as SideEffects
					,	(xpath('/C_TRN/Interaction[1]/text()', x))[1]::text as Interaction
					,	(xpath('/C_TRN/Overdose[1]/text()', x))[1]::varchar(1000) as Overdose
					,	(xpath('/C_TRN/InjectionMethod[1]/text()', x))[1]::varchar(255) as InjectionMethod
					,	(xpath('/C_TRN/Caution[1]/text()', x))[1]::text as Caution
					,	(xpath('/C_TRN/Composition[1]/text()', x))[1]::varchar(255) as Composition
					,	(xpath('/C_TRN/PharmDescription[1]/text()', x))[1]::text as PharmDescription
					,	(xpath('/C_TRN/Pharmacodynamics[1]/text()', x))[1]::text as Pharmacodynamics
					,	(xpath('/C_TRN/Indications[1]/text()', x))[1]::varchar(8000) as Indications
					,	(xpath('/C_TRN/ExpirationDate[1]/text()', x))[1]::varchar(300) as ExpirationDate
					,	(xpath('/C_TRN/MNNName[1]/text()', x))[1]::varchar(8000) as MNNName
					,	(xpath('/C_TRN/Manufacturer[1]/text()', x))[1]::varchar(3000) as Manufacturer
					,	(xpath('/C_TRN/CMnn[1]/text()', x))[1]::text::UUID as CMnn
					,	(xpath('/C_TRN/Atc[1]/text()', x))[1]::integer as Atc
					,	(xpath('/C_TRN/AtcVer[1]/text()', x))[1]::varchar(52) as AtcVer
					,	(xpath('/C_TRN/ProductType[1]/text()', x))[1]::text::int as ProductType
					,	(xpath('/C_TRN/RegCode[1]/text()', x))[1]::varchar(50) as RegCode
					,	(xpath('/C_TRN/FedCode[1]/text()', x))[1]::varchar(50) as FedCode
					,	(xpath('/C_TRN/EhrRusP08TSH954N[1]/text()', x))[1]::varchar(52) as EhrRusP08TSH954N
					,	(xpath('/C_TRN/EhrRusP08TSH954NVer[1]/text()', x))[1]::varchar(52) as EhrRusP08TSH954NVer
					,	(xpath('/C_TRN/EhrRus575VSF88OG[1]/text()', x))[1]::varchar(52) as EhrRus575VSF88OG
					,	(xpath('/C_TRN/EhrRus575VSF88OGVer[1]/text()', x))[1]::varchar(52) as EhrRus575VSF88OGVer
					from unnest(xpath('/C_TRN/body/C_TRNS/C_TRN', "Xml"::xml)) as x
					where _OperationType = _FTLOAD;

					-- Generate table Tmp_C_TRN
					Drop Table If Exists Tmp_C_TRN;
					Create Temp Table Tmp_C_TRN
					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(4000) as NameRus
					,	(xpath('/body[1]/NameEng[1]/text()', t.x2))[1]::varchar(4000) as NameEng
					,	(xpath('/body[1]/NameLatR[1]/text()', t.x2))[1]::varchar(4000) as NameLatR
					,	(xpath('/body[1]/OtherID[1]/text()', t.x2))[1]::text::int as OtherID
					,	(xpath('/body[1]/Characteristic[1]/text()', t.x2))[1]::varchar(4000) as Characteristic
					,	(xpath('/body[1]/PharmacologyAction[1]/text()', t.x2))[1]::varchar(4000) as PharmacologyAction
					,	(xpath('/body[1]/Practice[1]/text()', t.x2))[1]::text 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]/Overdose[1]/text()', t.x2))[1]::varchar(1000) as Overdose
					,	(xpath('/body[1]/InjectionMethod[1]/text()', t.x2))[1]::varchar(255) as InjectionMethod
					,	(xpath('/body[1]/Caution[1]/text()', t.x2))[1]::text as Caution
					,	(xpath('/body[1]/Composition[1]/text()', t.x2))[1]::varchar(255) as Composition
					,	(xpath('/body[1]/PharmDescription[1]/text()', t.x2))[1]::text as PharmDescription
					,	(xpath('/body[1]/Pharmacodynamics[1]/text()', t.x2))[1]::text as Pharmacodynamics
					,	(xpath('/body[1]/Indications[1]/text()', t.x2))[1]::varchar(8000) as Indications
					,	(xpath('/body[1]/ExpirationDate[1]/text()', t.x2))[1]::varchar(300) as ExpirationDate
					,	(xpath('/body[1]/MNNName[1]/text()', t.x2))[1]::text::varchar(8000) as MNNName
					,	(xpath('/body[1]/Manufacturer[1]/text()', t.x2))[1]::varchar(3000) as Manufacturer
					,	(xpath('/body[1]/CMnn[1]/text()', t.x2))[1]::text::UUID as CMnn
					,	(xpath('/body[1]/Atc[1]/text()', t.x2))[1]::text::int as Atc
					,	(xpath('/body[1]/AtcVer[1]/text()', t.x2))[1]::varchar(52) as AtcVer
					,	(xpath('/body[1]/ProductType[1]/text()', t.x2))[1]::text::int as ProductType
					,	(xpath('/body[1]/RegCode[1]/text()', x))[1]::varchar(50) as RegCode
					,	(xpath('/body[1]/FedCode[1]/text()', x))[1]::varchar(50) as FedCode
					,	(xpath('/body[1]/EhrRusP08TSH954N[1]/text()', x))[1]::varchar(52) as EhrRusP08TSH954N
					,	(xpath('/body[1]/EhrRusP08TSH954NVer[1]/text()', x))[1]::varchar(8) as EhrRusP08TSH954NVer
					,	(xpath('/body[1]/EhrRus575VSF88OG[1]/text()', x))[1]::varchar(52) as EhrRus575VSF88OG
					,	(xpath('/body[1]/EhrRus575VSF88OGVer[1]/text()', x))[1]::varchar(8) as EhrRus575VSF88OGVer
					from
					(
						select	unnest(xpath('/C_TRN/header', x)) as x1
						,	unnest(xpath('/C_TRN/body', x)) as x2
						from unnest(xpath('/C_TRN', "Xml"::xml)) x
					) t
					where _OperationType <> _FTLOAD;

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

						-- Обновляем имеющиеся TRN
						update pharm.T_C_TRN c		
						set	NameEng = nc.NameEng
							,NameRus = nc.NameRus
							,NameLatR = nc.NameLatR
							,Active = nc.Active
							,OtherID = nc.OtherID
							,Characteristic = nc.Characteristic
							,PharmacologyAction = nc.PharmacologyAction
							,Practice = nc.Practice
							,Contraindications = nc.Contraindications
							,Restrictions = nc.Restrictions
							,UseOfPregnancy = nc.UseOfPregnancy
							,SideEffects = nc.SideEffects
							,Interaction = nc.Interaction
							,Overdose = nc.Overdose
							,InjectionMethod = nc.InjectionMethod
							,Caution = nc.Caution
							,Composition = nc.Composition
							,PharmDescription = nc.PharmDescription
							,Pharmacodynamics = nc.Pharmacodynamics
							,Indications = nc.Indications
							,ExpirationDate = nc.ExpirationDate
							,MNNName = nc.MNNName
							,Manufacturer = nc.Manufacturer
							,CMnn = nc.CMnn
							,Atc = atc.EhrRusID
							,ProductType = nc.ProductType
							,RegCode = nc.RegCode
				   			,FedCode = nc.FedCode
				   			,EhrRusP08TSH954N = eh1.EhrRusID
				   			,EhrRus575VSF88OG = eh2.EhrRusID
						from Tmp_C_TRNS nc 
						left join lateral
						(
							select eh.EhrRusID 
							from EHR.T_EHR_RUS_P08TSH954N eh
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
							where eh.CodeEHR = nc.EhrRusP08TSH954N and rrb.CurrVersion = nc.EhrRusP08TSH954NVer
							limit 1
						) eh1 on true
						left join lateral
						(
							select eh.EhrRusID 
							from EHR.T_EHR_RUS_575VSF88OG eh
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
							where eh.CodeEHR = nc.EhrRus575VSF88OG and rrb.CurrVersion = nc.EhrRus575VSF88OGVer
							limit 1
						) eh2 on true
						left join lateral
						(
							select eh.EhrRusID 
							from EHR.T_EHR_RUS_M00002 eh
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
							where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
							limit 1
						) atc on true
						where nc.ID = c.CTrnID;

						-- Загружаем новые
						INSERT INTO pharm.T_C_TRN
							(
								CTrnID, 
								NameRus, 
								NameEng, 
								Active, 
								OtherID, 
								Characteristic, 
								PharmacologyAction, 
								Practice, 
								Contraindications, 
								Restrictions, 
								UseOfPregnancy, 
								SideEffects, 
								Interaction, 
								Overdose, 
								InjectionMethod, 
								Caution, 
								Composition, 
								PharmDescription, 
								Pharmacodynamics, 
								Indications, 
								ExpirationDate, 
								MNNName, 
								Manufacturer, 
								CMnn, 
								Atc, 
								ProductType, 
								RegCode, 
								FedCode, 
								EhrRusP08TSH954N, 
								EhrRus575VSF88OG, 
								NameLatR
							)
							SELECT 
								nc.ID, 
								nc.NameRus, 
								nc.NameEng, 
								nc.Active, 
								nc.OtherID, 
								nc.Characteristic, 
								nc.PharmacologyAction, 
								nc.Practice, 
								nc.Contraindications, 
								nc.Restrictions, 
								nc.UseOfPregnancy, 
								nc.SideEffects, 
								nc.Interaction, 
								nc.Overdose, 
								nc.InjectionMethod, 
								nc.Caution, 
								nc.Composition, 
								nc.PharmDescription, 
								nc.Pharmacodynamics, 
								nc.Indications, 
								nc.ExpirationDate, 
								nc.MNNName, 
								nc.Manufacturer, 
								nc.CMnn ,
								atc.EhrRusID,
								nc.ProductType, 
								nc.RegCode, 
								nc.FedCode, 
								eh1.EhrRusID, 
								eh2.EhrRusID, 
								nc.NameLatR
						from Tmp_C_TRNS nc
						left join lateral
						(
							select eh.EhrRusID 
							from EHR.T_EHR_RUS_P08TSH954N eh
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
							where eh.CodeEHR = nc.EhrRusP08TSH954N and rrb.CurrVersion = nc.EhrRusP08TSH954NVer
							limit 1
						) eh1 on true
						left join lateral
						(
							select eh.EhrRusID 
							from EHR.T_EHR_RUS_575VSF88OG eh
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
							where eh.CodeEHR = nc.EhrRus575VSF88OG and rrb.CurrVersion = nc.EhrRus575VSF88OGVer
							limit 1
						) eh2 on true
						left join lateral
						(
							select eh.EhrRusID 
							from EHR.T_EHR_RUS_M00002 eh
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
							where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
							limit 1
						) atc on true
						where not exists
							(
								select * 
								from Pharm.T_C_TRN c 
								where c.CTrnID = nc.ID
							);

					elsif _OperationType = _DELETE
					then
						-- Удаление
						update pharm.T_C_TRN c
						set Active = '0'
						from Tmp_C_TRN nc 
						where nc.ID = c.CTrnID;
					else
						-- update pharm.T_C_TRN
						update pharm.T_C_TRN c
						set NameEng = nc.NameEng
						,	NameRus = nc.NameRus
						,	NameLatR = nc.NameLatR
						,	OtherID = nc.OtherID
						,	Characteristic = nc.Characteristic
						,	PharmacologyAction = nc.PharmacologyAction
						,	Practice = nc.Practice
						,	Contraindications = nc.Contraindications
						,	Restrictions = nc.Restrictions
						,	UseOfPregnancy = nc.UseOfPregnancy
						,	SideEffects = nc.SideEffects
						,	Interaction = nc.Interaction
						,	Overdose = nc.Overdose
						,	InjectionMethod = nc.InjectionMethod
						,	Caution = nc.Caution
						,	Composition = nc.Composition
						,	PharmDescription = nc.PharmDescription
						,	Pharmacodynamics = nc.Pharmacodynamics
						,	Indications = nc.Indications
						,	ExpirationDate = nc.ExpirationDate
						,	MNNName = nc.MNNName
						,	Manufacturer = nc.Manufacturer
						,	CMnn = nc.CMnn
						,	Atc = atc.EhrRusID
						,	ProductType = nc.ProductType
						,	RegCode = nc.RegCode
						,	FedCode = nc.FedCode
						,	EhrRusP08TSH954N = eh1.EhrRusID
						,	EhrRus575VSF88OG = eh2.EhrRusID
						from Tmp_C_TRN nc
						left join lateral
						(
							select eh.EhrRusID 
							from EHR.T_EHR_RUS_P08TSH954N eh
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
							where eh.CodeEHR = nc.EhrRusP08TSH954N and rrb.CurrVersion = nc.EhrRusP08TSH954NVer
							limit 1
						) eh1 on true
						left join lateral
						(
							select eh.EhrRusID 
							from EHR.T_EHR_RUS_575VSF88OG eh
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
							where eh.CodeEHR = nc.EhrRus575VSF88OG and rrb.CurrVersion = nc.EhrRus575VSF88OGVer
							limit 1
						) eh2 on true
						left join lateral
						(
							select eh.EhrRusID 
							from EHR.T_EHR_RUS_M00002 eh
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
							where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
							limit 1
						) atc on true
						where nc.ID = c.CTrnID 
						and _OperationType = _UPDATE;

						-- insert pharm.T_C_TRN
						INSERT INTO pharm.T_C_TRN
							(CTrnID, NameRus, NameEng, Active, OtherID, Characteristic, PharmacologyAction
						, Practice, Contraindications, Restrictions, UseOfPregnancy, SideEffects, Interaction, Overdose, InjectionMethod
						, Caution, Composition, PharmDescription, Pharmacodynamics, Indications, ExpirationDate, MNNName, Manufacturer
						, CMnn, Atc, ProductType, RegCode, FedCode, EhrRusP08TSH954N, EhrRus575VSF88OG, NameLatR)

						SELECT 
							nc.ID, 
							nc.NameRus, 
							nc.NameEng, 
							nc.Active, 
							nc.OtherID, 
							nc.Characteristic,
							nc.PharmacologyAction, 
							nc.Practice, 
							nc.Contraindications, 
							nc.Restrictions, 
							nc.UseOfPregnancy, 
							nc.SideEffects, 
							nc.Interaction, 
							nc.Overdose, 
							nc.InjectionMethod, 
							nc.Caution, 
							nc.Composition, 
							nc.PharmDescription, 
							nc.Pharmacodynamics, 
							nc.Indications, 
							nc.ExpirationDate, 
							nc.MNNName, 
							nc.Manufacturer, 
							nc.CMnn ,
						    atc.EhrRusID,
							nc.ProductType, 
							nc.RegCode, 
							nc.FedCode, 
							eh1.EhrRusID, 
							eh2.EhrRusID, 
							nc.NameLatR
						from Tmp_C_TRN nc
						left join lateral
						(
							select eh.EhrRusID 
							from EHR.T_EHR_RUS_P08TSH954N eh
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
							where eh.CodeEHR = nc.EhrRusP08TSH954N and rrb.CurrVersion = nc.EhrRusP08TSH954NVer
							limit 1
						) eh1 on true
						left join lateral
						(
							select eh.EhrRusID 
							from EHR.T_EHR_RUS_575VSF88OG eh
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
							where eh.CodeEHR = nc.EhrRus575VSF88OG and rrb.CurrVersion = nc.EhrRus575VSF88OGVer
							limit 1
						) eh2 on true
						left join lateral
						(
							select eh.EhrRusID 
							from EHR.T_EHR_RUS_M00002 eh
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
							where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
							limit 1
						) atc on true
						where not exists(select* from pharm.T_C_TRN c where c.CTrnID = nc.ID) 
						and _OperationType = _INSERT;
					END if;
					
					_EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'C_TRN' limit 1);
					if exists 
					(
						select* from VCLib.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.C_TRN' Limit 1
					)
					then
						---- генерируем след. номер
						--perform public.sp_GenerateNumber(@EntId, 'PHARM.C_TRN');
							
						update VCLib.T_GEN_NUMBERS
						set NextNumber = (select MAX(OtherID) from PHARM.T_C_TRN) --+ 1
						where EntityID = _EntId
						and NumberName = 'PHARM.C_TRN';
					else
						INSERT INTO VCLib.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
						VALUES(gen_random_uuid(), _EntId, 'PHARM.C_TRN', coalesce((select MAX(OtherID) from PHARM.T_C_TRN), 1));
					end if;
					
					DROP TABLE if exists Tmp_C_TRN;
					DROP TABLE if exists Tmp_C_TRNS;
				end if;
			END if;
		end if;
	End;
End;
$$;