-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS dbo.sp_rds_cmnn;

DROP PROCEDURE IF EXISTS aps.sp_rds_cmnn;

CREATE OR REPLACE PROCEDURE aps.sp_rds_cmnn(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 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_MNN';
        _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_mnn;
            CREATE TEMP TABLE temp_c_mnn
            (
                cmnnid            uuid,
                namerus           VARCHAR(1000),
                nameeng           VARCHAR(1000),
                otherid           INT,
                active            boolean,
                isdrug            boolean,
                usedin            INT,
                chemicalname      VARCHAR(600),
                bruttoformula     VARCHAR(100),
                characteristic    VARCHAR(4000),
                pharmacology      text,
                practice          VARCHAR(6000),
                contraindications VARCHAR(4000),
                restrictions      text,
                useofpregnancy    text,
                sideeffects       text,
                interaction       text,
                injectionmethod   VARCHAR(2000),
                caution           text,
                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,
					IsDrug,
					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, IsDrug, 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, IsDrug 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,
                isdrug            = nc.isdrug,
                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,
             isdrug,
             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.isdrug,
                   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
                --выясняем тип операции
                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 -- Первичная загрузка
                    SELECT '<C_MNN><header><TYPE>' || _ftload || '</TYPE></header><body><C_MNNS>' ||
                           COALESCE(STRING_AGG(
                                        XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                        ''), '')
                               ||
                           '</C_MNNS></body></C_MNN>'
                    INTO _result
                    FROM (SELECT 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.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",
                                         ehr.idehr AS "EhrRusDvl105",
                                         rrb.currversion AS "EhrRusDvl105Ver",
                                         ehr1.idehr AS "EhrRusNsv101",
                                         rrb1.currversion AS "EhrRusNsv101Ver"
                                     )
                                 ) AS xml_elem
                          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)
                         ) tt;
                
                ELSIF _operationtype <> _delete
                THEN
                    -- собираем C_MNN
                    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 = c.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 = c.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
                    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_MNN/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
                    
                    
                    -- Обработка операции
                    IF _operationtype = _ftload
                    THEN
                        -- 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/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_MNM/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()', x))[1]::text::int          AS ehrrusdvl105,
                                   (XPATH('/C_MNN/EhrRusDvl105Ver[1]/text()', x))[1]::varchar(8)      AS ehrrusdvl105ver,
                                   (XPATH('/C_MNN/EhrRusNsv101[1]/text()', x))[1]::text::int          AS ehrrusnsv101,
                                   (XPATH('/C_MNN/EhrRusNsv101Ver[1]/text()', x))[1]::varchar(8)      AS ehrrusnsv101ver
                            FROM UNNEST(XPATH('/C_MNN/body/C_MNNS/C_MNN', "Xml"::xml)) AS x;
                        
                        ANALYZE tmp_c_mnns;
                        
                        -- Первичная загрузка
                        -- МНН не из справочника 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,
                            isdrug            = nc.isdrug,
                            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,
                         isdrug,
                         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.isdrug,
                               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 rrb1.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
                                  );
                    ELSE
                        -- 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]/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;
                        
                        IF _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,
                                isdrug            = nc.isdrug,
                                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 rrb1.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,
                             isdrug,
                             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.isdrug,
                                   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 vclib.t_gen_numbers
                             WHERE entityid = _entid
                               AND numbername = 'PHARM.C_MNN'
                            )
                        THEN
                            ---- генерируем след. номер
                            --perform public.sp_GenerateNumber(@EntId, 'PHARM.C_MNN');
                            
                            UPDATE vclib.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
                                vclib.t_gen_numbers
                                (gennumbersid, entityid, numbername, nextnumber)
                            VALUES (gen_random_uuid(),
                                    _entid,
                                    'PHARM.C_MNN',
                                    COALESCE((SELECT MAX(otherid) FROM pharm.t_c_mnn), 1));
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;