drop procedure if exists sp_GetSetCmnn;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-24
-- Description: Выгрузка/загрузка справочника Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
CREATE PROCEDURE sp_GetSetCmnn
(
   	"Xml" xml DEFAULT NULL::xml
    ,"IsNew" boolean DEFAULT '1'
    ,"ID" uuid DEFAULT NULL::uuid
    ,"QueueName" character varying DEFAULT null::character varying
    ,"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
    ,"User" uuid DEFAULT NULL::uuid
)
language plpgsql
as
$$
begin
    declare 
        _Result text;
		_ContentEncoding varchar(255) := 'utf-8';
		_Type varchar(255) := 'C_MNN';
		_OperationType varchar(255);
		_OwnerID UUID := "ID";
    begin
        "dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);
    
        if ("Xml" is null)
        then 
            -- Инициализация
            _OwnerID := "ID";
            if ("ID" is null)
            then
                _OperationType := 'FTLoad';
            end if;
            elsif ("IsNew" = '1')
            then
                _OperationType = 'Insert';
            end if;
            elsif ("IsNew" = '0' and exists (select * from PHARM.T_C_MNN c where c.MnnID = "ID"))
            then
                _OperationType = 'Update';
            end if;
            elsif ("IsNew" = '0' and not exists (select * from PHARM.T_C_MNN c where c.MnnID = "ID"))
            then
                _OperationType = 'Delete';
                select 
                xmlelement 
                (
                    name "C_MNN",
                    xmlelement
                    (
                        name header,
                        xmlforest 
                        (
                            _OperationType as "TYPE",
                            "ID" as "ID"
                        )
                    )
                );
            end if;

            if (_OperationType = 'FTLoad')
            then
                select 
                xmlelement
                (
                    name "C_MNN",
                    xmlelement
                    (
                        name header,
                        xmlforest ('FTLoad' as "TYPE")
                    ),
                    xmlelement
                    (
                        name body,
                        xmlelement
                        (
                            name "C_MNNS",
                            xmlelement
                            (
                                name "C_MNN",
                                xmlforest
                                (
                                     c.CMnnID			    as "ID"
	    						    ,c.Active			    as "Active"
	    						    ,c.IsDrug			    as "IsDrug"
	    						    ,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 ehr on ehr.EhrRusID = c.EhrRusDvl105
	    			LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr.EhrRusRefBooks
	    		left join EHR.T_EHR_RUS_NSV101 ehr1 on ehr1.EhrRusID = c.EhrRusNsv101
	    			LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = ehr1.EhrRusRefBooks
                 where c.UsedIn in (1,3)
                into _Result;
            end if;
            elsif (_OperationType <> 'Delete')
            then 
                select 
                xmlelement
                (
                    name "C_MNN",
                    xmlelement
                    (
                        name header,
                        xmlforest
                        (
                            _OperationType as "TYPE",
                            "ID" as "ID"
                        )
                    ),
                    xmlelement
                    (
                        name body,
                        xmlforest
                        (
                        c.Active       		        as "Active"
	    				    ,c.IsDrug      		    as "IsDrug"
	    				    ,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"
                        )
                    )
                )
                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 RDS.T_RABBIT_SEND_OBJECT
                (RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
            select uuid_generate_v4()--(<RabbitSendObjectID, uniqueidentifier,>
                  ,s.ServerName--,<QueueName, varchar(255),>
                  ,ConvertTextToBinaryZip(_Result, '')--,<Content, text,>
                  ,null--,<MessageID, varchar(255),>
                  ,null--,<Headers, varchar(255),>
                  ,_ContentEncoding--,<ContentEncoding, varchar(255),>
                  ,_Type--,<Type, varchar(255),>
                  ,"User"--,<UserID, varchar(255),>
                  ,0--,<State, int,>)
                  ,"dateTime"--,<CreateDateTime, datetime,>)
                  ,_OwnerID--,<OwnerID, varchar(255),>
            from RDS.T_SYNC_SERVERS s
            where ("QueueName" is not null and s.ServerName = "QueueName") 
                or ("QueueName" is null and s.ServerType in (0, 1)); -- Рассылаем пакет всем подписанным серверам AKUZ-а и APS, если не указана очередь
        end if;
        elsif ("Xml" is not null)
        then
            _OperationType := (xpath('/C_MNN/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(6);
            _OwnerID := (xpath('/C_MNN/header[1]/ID[1]/text()', "Xml"))[1]::text::uuid;
            _Result := cast("Xml" as text);

            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/IsDrug[1]/text()', x))[1]::text::boolean                    as IsDrug
	    	    ,(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';

            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]/IsDrug[1]/text()', t.x2))[1]::text::boolean               as IsDrug
	    	    ,(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 c.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  c.NameEng				= nc.NameEng
                    ,c.NameRus				= nc.NameRus
                    ,c.NameLatR				= nc.NameLatR
                    ,c.Active				= nc.Active
                    ,c.IsDrug				= nc.IsDrug
                    ,c.OtherID				= nc.OtherID
	    		    ,c.ChemicalName			= nc.ChemicalName
	    		    ,c.BruttoFormula		= nc.BruttoFormula
	    		    ,c.Characteristic		= nc.Characteristic
	    		    ,c.Pharmacology			= nc.Pharmacology
	    		    ,c.Practice				= nc.Practice
	    		    ,c.Contraindications	= nc.Contraindications
	    		    ,c.Restrictions			= nc.Restrictions
	    		    ,c.UseOfPregnancy		= nc.UseOfPregnancy
	    		    ,c.SideEffects			= nc.SideEffects
	    		    ,c.Interaction			= nc.Interaction
	    		    ,c.InjectionMethod		= nc.InjectionMethod
	    		    ,c.Caution				= nc.Caution
	    		    ,c.Overdose				= nc.Overdose
	    		    ,c.CASCode				= nc.CASCode
	    		    ,c.UsedIn				= nc.UsedIn
	    		    ,c.RegCode				= nc.RegCode
	    		    ,c.FedCode				= nc.FedCode
	    		    ,c.EhrRusDvl105			= eh1.EhrRusID
	    		    ,c.EhrRusNsv101			= eh2.EhrRusID
                from Tmp_C_MNNS nc 
                left join lateral
                (
                    select er.EhrRusID 
	    			from EHR.T_EHR_RUS_DVL105 er
	    				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
                ) eh1 on true
                left join lateral
                (
                    select er.EhrRusID 
	    			from EHR.T_EHR_RUS_NSV101 er
	    				join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er.EhrRusRefBooks
	    			where er.IdEHR = nc.EhrRusNsv101 and RRB1.CurrVersion = nc.EhrRusNsv101Ver limit 1
                ) eh2 on true
                where nc.ID = c.MnnID and c.UsedIn in (1,3);
                
                -- Загружаем новые
                INSERT INTO PHARM.T_C_MNN
                           (CMnnID, NameRus, NameEng, Active,IsDrug , OtherID, UsedIn, ChemicalName, BruttoFormula, Characteristic, Pharmacology, Practice, Contraindications, 
	    				   Restrictions, UseOfPregnancy, SideEffects, Interaction, InjectionMethod, 
                           Caution, Overdose, CASCode, RegCode, FedCode, NameLatR, EhrRusDvl105, EhrRusNsv101)
                SELECT
                           nc.ID--<CMnnID, uniqueidentifier,>
                           ,nc.NameRus--<NameRus, varchar(255),>
                           ,nc.NameEng--<NameEng, varchar(255),>
                           ,nc.Active--<Active, bit,>
                           ,nc.IsDrug--<IsDrug, bit,>
                           ,nc.OtherID--<OtherID, int,>
                           ,nc.UsedIn--<UsedIn, bit,>
	    				   ,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
                           ,eh1.EhrRusID
                           ,eh2.EhrRusID
                from Tmp_C_MNNS nc
	    			left join lateral
                    (
	    				select er.EhrRusID 
	    				from EHR.T_EHR_RUS_DVL105 er
	    					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

	    			) eh1 on true
                    left join lateral
                    (
	    				select er.EhrRusID 
	    				from EHR.T_EHR_RUS_NSV101 er
	    					join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er.EhrRusRefBooks
	    				where er.IdEHR = nc.EhrRusNsv101 and RRB1.CurrVersion = nc.EhrRusNsv101Ver limit 1

	    			) eh2 on true
                where not exists
                    (
                        select *
                        from PHARM.T_C_MNN c
                        where c.CMnnID = nc.ID and c.UsedIn in (1,3)
                    );
            end if;
            elsif (_OperationType = 'Delete')
            then
                update PHARM.T_C_MNN c
                set c.Active = '0'
                from Tmp_C_MNN nc 
                where  nc.ID = c.CMnnID
                amd c.UsedIn in (1,3);
            end if;
            else
            then
                update PHARM.T_C_MNN
                set 
                    c.NameEng				= nc.NameEng
                   ,c.NameRus				= nc.NameRus
                   ,c.NameLatR				= nc.NameLatR
                   ,c.Active				= nc.Active
                   ,c.IsDrug				= nc.IsDrug
                   ,c.OtherID				= nc.OtherID
	    		   ,c.ChemicalName			= nc.ChemicalName
	    		   ,c.BruttoFormula			= nc.BruttoFormula
	    		   ,c.Characteristic		= nc.Characteristic
	    		   ,c.Pharmacology			= nc.Pharmacology
	    		   ,c.Practice				= nc.Practice
	    		   ,c.Contraindications		= nc.Contraindications
	    		   ,c.Restrictions			= nc.Restrictions
	    		   ,c.UseOfPregnancy		= nc.UseOfPregnancy
	    		   ,c.SideEffects			= nc.SideEffects
	    		   ,c.Interaction			= nc.Interaction
	    		   ,c.InjectionMethod		= nc.InjectionMethod
	    		   ,c.Caution				= nc.Caution
	    		   ,c.Overdose				= nc.Overdose
	    		   ,c.CASCode				= nc.CASCode
	    		   ,c.UsedIn				= nc.UsedIn
	    		   ,c.RegCode				= nc.RegCode
	    		   ,c.FedCode				= nc.FedCode
	    		   ,c.EhrRusDvl105			= eh1.EhrRusID
	    		   ,c.EhrRusNsv101			= eh2.EhrRusID
                from Tmp_C_MNN nc 
                left join lateral
                (
                    select er.EhrRusID
                    from EHR.T_EHR_RUS_DVL105 er
                        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
                ) eh1 on true
                left join lateral
                (
                    select er.EhrRusID 
	    		    from EHR.T_EHR_RUS_NSV101 er
	    		    	join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er.EhrRusRefBooks
	    		    where er.IdEHR = nc.EhrRusNsv101 and RRB1.CurrVersion = nc.EhrRusNsv101Ver limit 1
                ) eh2 on true
                where nc.ID = c.CMnnID and c.UsedIn in (1,3) and _OperationType = 'Update';
                    
            
                -- insert [PHARM].T_C_MNN
                INSERT INTO PHARM.T_C_MNN
                    (tCMnnID, NameRus, NameEng, Active, IsDrug, OtherID, UsedIn
	    				   ,ChemicalName, BruttoFormula, Characteristic, Pharmacology, Practice, Contraindications, 
	    				   Restrictions, UseOfPregnancy, SideEffects, Interaction, InjectionMethod, 
                           Caution, Overdose, CASCode, RegCode, FedCode, NameLatR, EhrRusDvl105, EhrRusNsv101)
                SELECT
                     nc.ID--<CMnnID, uniqueidentifier,>
                    ,nc.NameRus--<NameRus, varchar(255),>
                    ,nc.NameEng--<NameEng, varchar(255),>
                    ,nc.IsDrug--<IsDrug, bit,>
                    ,nc.OtherID--<OtherID, int,>
                    ,UsedIn--<UsedIn, bit,>
	    		    ,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
                    ,eh1.EhrRusID
                    ,eh2.EhrRusID
                from Tmp_C_MNN nc
	    			left join lateral
                    (
	    				select er.EhrRusID 
	    				from EHR.T_EHR_RUS_DVL105 er
	    					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
	    			) eh1 on true
                    left join lateral
                    (
	    				select er.EhrRusID 
	    				from EHR.T_EHR_RUS_NSV101 er
	    					join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er.EhrRusRefBooks
	    				where er.IdEHR = nc.EhrRusNsv101 and RRB1.CurrVersion = nc.EhrRusNsv101Ver limit 1
	    			) eh2 on true
                where _OperationType = 'Insert' and not exists
                (
                    select *
                    from PHARM.T_C_MNN c
                    where c.CMnnID = nc.ID and c.UsedIn in (1,3)-- RDS
                );
            end if;

            INSERT INTO RDS.T_RABBIT_SEND_OBJECT
                (RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
            select uuid_generate_v4()--(<RabbitSendObjectID, uniqueidentifier,>
                  ,s.ServerName--,<QueueName, varchar(255),>
                  ,ConvertTextToBinaryZip(_Result, '')--,<Content, text,>
                  ,null--,<MessageID, varchar(255),>
                  ,null--,<Headers, varchar(255),>
                  ,_ContentEncoding--,<ContentEncoding, varchar(255),>
                  ,_Type--,<Type, varchar(255),>
                  ,"User"--,<UserID, varchar(255),>
                  ,0--,<State, int,>)
                  ,"dateTime"--,<CreateDateTime, datetime,>)
                  ,_OwnerID--,<OwnerID, varchar(255),>
            from RDS.T_SYNC_SERVERS s
            where s.ServerName <> "QueueName" and _OperationType <> 'FTLoad';

            _EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'C_MNN' limit 1);
            if exists (select * from VCLIB.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.C_MNN' limit 1)
            then 
                update VCLIB.T_GEN_NUMBERS gn
                set gn.NextNumber = (select max(OtherID) from PHARM.T_C_MNN)
                where gn.EntityID = _EntId and gn.NumberName = 'PHARM.C_MNN';
            end if;
            else
            then
                INSERT INTO VCLIB.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 TABLE if exists Tmp_C_MNN;
            DROP TABLE if exists Tmp_C_MNNS;
        end if;
    End;
End;
$$;