DROP PROCEDURE IF EXISTS public.sp_getsetcmnn;

CREATE OR REPLACE PROCEDURE public.sp_getsetcmnn(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT NULL,
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml"::xml;
    p_isnew           boolean      := "IsNew"::boolean;
    p_id              uuid         := "ID";
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    v_result          text;
    v_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'C_MNN';
    v_operationtype   varchar(6);
    v_ownerid         uuid         := p_id;
    v_entid           int;
    c_insert          varchar(6)   := 'Insert';
    c_update          varchar(6)   := 'Update';
    c_delete          varchar(6)   := 'Delete';
    c_ftload          varchar(6)   := 'FTLoad';
BEGIN

    IF (p_xml IS NULL) THEN

        IF (p_id IS NULL) THEN
            v_operationtype := c_ftload;
        ELSIF (p_isnew = TRUE) THEN
            v_operationtype := c_insert;
        ELSIF (p_isnew = FALSE AND EXISTS (SELECT 1 FROM pharm.t_c_mnn c WHERE c.cmnnid = p_id)) THEN
            v_operationtype := c_update;
        ELSIF (p_isnew = FALSE AND NOT EXISTS (SELECT 1 FROM pharm.t_c_mnn c WHERE c.cmnnid = p_id)) THEN
            v_operationtype := c_delete;
            SELECT XMLELEMENT
                   (
                       NAME "C_MNN",
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLFOREST
                           (
                               v_operationtype AS "TYPE",
                               p_id AS "ID"
                           )
                       )
                   )
            INTO v_result;
        END IF;

        IF (v_operationtype = c_ftload) THEN

            SELECT '<C_MNN><header><TYPE>' || c_ftload || '</TYPE></header><body><C_MNNS>' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                ''), '')
                       ||
                   '</C_MNNS></body></C_MNN>'
            INTO v_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",
                                 c.IsNarcoticAndPsychotropic AS "IsNarcoticAndPsychotropic",
                                 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 (v_operationtype <> c_delete) THEN

            SELECT XMLELEMENT
                   (
                       NAME "C_MNN",
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLFOREST
                           (
                               c_ftload AS "TYPE",
                               c.cmnnid AS "ID"
                           )
                       ),
                       XMLELEMENT
                       (
                           NAME "body",
                           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",
                               c.IsNarcoticAndPsychotropic AS "IsNarcoticAndPsychotropic",
                               ehr.idehr AS "EhrRusDvl105",
                               rrb.currversion AS "EhrRusDvl105Ver",
                               ehr1.idehr AS "EhrRusNsv101",
                               rrb1.currversion AS "EhrRusNsv101Ver"
                           )
                       )
                   )
            INTO v_result
            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);

        END IF;

        INSERT INTO
            rds.t_rabbit_send_object (rabbitsendobjectid,
                                      queuename,
                                      content,
                                      messageid,
                                      headers,
                                      contentencoding,
                                      type,
                                      userid,
                                      state,
                                      createdatetime,
                                      ownerid)
        SELECT gen_random_uuid(),
               s.servername,
               public.converttexttobinaryzip(v_result, ''),
               NULL,
               NULL,
               v_contentencoding,
               v_type,
               p_user,
               0,
               p_datetime,
               v_ownerid
        FROM rds.t_sync_servers s
        WHERE (p_queuename IS NOT NULL AND s.servername = p_queuename)
           OR (p_queuename IS NULL AND s.servertype IN (0, 1));

    ELSIF (p_xml IS NOT NULL) THEN

        SELECT (XPATH('/C_MNN/header/TYPE/text()', x))[1]::varchar(6),
               (XPATH('/C_MNN/header/ID/text()', x))[1]::text::uuid,
               p_xml::text
        INTO v_operationtype, v_ownerid, v_result
        FROM UNNEST(XPATH('/C_MNN')) x;

        IF (v_operationtype = c_ftload) THEN
            DROP TABLE IF EXISTS temp_c_mnns;
            CREATE TEMP TABLE temp_c_mnns AS
            SELECT (XPATH('/C_MNN/ID/text()', x))[1]::uuid                                        AS id,
                   (XPATH('/C_MNN/Active/text()', x))[1]::varchar(10)::boolean                    AS active,
                   (XPATH('/C_MNN/IsDrug/text()', x))[1]::varchar(10)::boolean                    AS isdrug,
                   (XPATH('/C_MNN/NameRus/text()', x))[1]::varchar(1000)                          AS namerus,
                   (XPATH('/C_MNN/NameEng/text()', x))[1]::varchar(1000)                          AS nameeng,
                   (XPATH('/C_MNN/NameLatR/text()', x))[1]::varchar(4000)                         AS namelatr,
                   (XPATH('/C_MNN/OtherID/text()', x))[1]::varchar(10)::int                       AS otherid,
                   (XPATH('/C_MNN/ChemicalName/text()', x))[1]::varchar(600)                      AS chemicalname,
                   (XPATH('/C_MNN/BruttoFormula/text()', x))[1]::varchar(100)                     AS bruttoformula,
                   (XPATH('/C_MNN/Characteristic/text()', x))[1]::varchar(4000)                   AS characteristic,
                   (XPATH('/C_MNN/Pharmacology/text()', x))[1]::text                              AS pharmacology,
                   (XPATH('/C_MNN/Practice/text()', x))[1]::varchar(6000)                         AS practice,
                   (XPATH('/C_MNN/Contraindications/text()', x))[1]::varchar(4000)                AS contraindications,
                   (XPATH('/C_MNN/Restrictions/text()', x))[1]::text                              AS restrictions,
                   (XPATH('/C_MNN/UseOfPregnancy/text()', x))[1]::text                            AS useofpregnancy,
                   (XPATH('/C_MNN/SideEffects/text()', x))[1]::text                               AS sideeffects,
                   (XPATH('/C_MNN/Interaction/text()', x))[1]::text                               AS interaction,
                   (XPATH('/C_MNN/InjectionMethod/text()', x))[1]::varchar(4000)                  AS injectionmethod,
                   (XPATH('/C_MNN/Caution/text()', x))[1]::text                                   AS caution,
                   (XPATH('/C_MNN/Overdose/text()', x))[1]::varchar(4000)                         AS overdose,
                   (XPATH('/C_MNN/CASCode/text()', x))[1]::varchar(50)                            AS cascode,
                   (XPATH('/C_MNN/UsedIn/text()', x))[1]::varchar(10)::int                        AS usedin,
                   (XPATH('/C_MNN/RegCode/text()', x))[1]::varchar(50)                            AS regcode,
                   (XPATH('/C_MNN/FedCode/text()', x))[1]::varchar(50)                            AS fedcode,
                   (XPATH('/C_MNN/IsNarcoticAndPsychotropic/text()', x))[1]::varchar(10)::boolean AS IsNarcoticAndPsychotropic,
                   (XPATH('/C_MNN/EhrRusDvl105/text()', x))[1]::varchar(10)::int                  AS ehrrusdvl105,
                   (XPATH('/C_MNN/EhrRusDvl105Ver/text()', x))[1]::varchar(8)                     AS ehrrusdvl105ver,
                   (XPATH('/C_MNN/EhrRusNsv101/text()', x))[1]::varchar(10)::int                  AS ehrrusnsv101,
                   (XPATH('/C_MNN/EhrRusNsv101Ver/text()', x))[1]::varchar(8)                     AS ehrrusnsv101ver
            FROM UNNEST(XPATH('//C_MNN/body/C_MNNS/C_MNN', p_xml)) AS x;

            ANALYZE temp_c_mnns;

            UPDATE pharm.t_c_mnn c
            SET active = FALSE
            WHERE NOT EXISTS (SELECT 1 FROM temp_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,
                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,
                IsNarcoticAndPsychotropic = COALESCE(nc.IsNarcoticAndPsychotropic, c.IsNarcoticAndPsychotropic),
                ehrrusdvl105              = eh1.ehrrusid,
                ehrrusnsv101              = eh2.ehrrusid
            FROM temp_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.cmnnid
              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,
                               IsNarcoticAndPsychotropic,
                               namelatr,
                               ehrrusdvl105,
                               ehrrusnsv101)
            SELECT nc.id,
                   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,
                   COALESCE(nc.IsNarcoticAndPsychotropic, false),
                   nc.namelatr,
                   eh1.ehrrusid,
                   eh2.ehrrusid
            FROM temp_c_mnns nc
            LEFT JOIN (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 (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 1 FROM pharm.t_c_mnn c WHERE c.cmnnid = nc.id AND c.usedin IN (1, 3));

        ELSE
            DROP TABLE IF EXISTS temp_c_mnn;
            CREATE TEMP TABLE temp_c_mnn AS
            SELECT (XPATH('/headaer/ID/text()', t.x2))[1]::uuid                                     AS id,
                   (XPATH('/body/Active/text()', t.x1))[1]::varchar(10)::boolean                    AS active,
                   (XPATH('/body/IsDrug/text()', t.x1))[1]::varchar(10)::boolean                    AS isdrug,
                   (XPATH('/body/NameRus/text()', t.x1))[1]::varchar(1000)                          AS namerus,
                   (XPATH('/body/NameEng/text()', t.x1))[1]::varchar(1000)                          AS nameeng,
                   (XPATH('/body/NameLatR/text()', t.x1))[1]::varchar(4000)                         AS namelatr,
                   (XPATH('/body/OtherID/text()', t.x1))[1]::varchar(10)::int                       AS otherid,
                   (XPATH('/body/ChemicalName/text()', t.x1))[1]::varchar(600)                      AS chemicalname,
                   (XPATH('/body/BruttoFormula/text()', t.x1))[1]::varchar(100)                     AS bruttoformula,
                   (XPATH('/body/Characteristic/text()', t.x1))[1]::varchar(4000)                   AS characteristic,
                   (XPATH('/body/Pharmacology/text()', t.x1))[1]::text                              AS pharmacology,
                   (XPATH('/body/Practice/text()', t.x1))[1]::varchar(6000)                         AS practice,
                   (XPATH('/body/Contraindications/text()', t.x1))[1]::varchar(4000)                AS contraindications,
                   (XPATH('/body/Restrictions/text()', t.x1))[1]::text                              AS restrictions,
                   (XPATH('/body/UseOfPregnancy/text()', t.x1))[1]::text                            AS useofpregnancy,
                   (XPATH('/body/SideEffects/text()', t.x1))[1]::text                               AS sideeffects,
                   (XPATH('/body/Interaction/text()', t.x1))[1]::text                               AS interaction,
                   (XPATH('/body/InjectionMethod/text()', t.x1))[1]::varchar(4000)                  AS injectionmethod,
                   (XPATH('/body/Caution/text()', t.x1))[1]::text                                   AS caution,
                   (XPATH('/body/Overdose/text()', t.x1))[1]::varchar(4000)                         AS overdose,
                   (XPATH('/body/CASCode/text()', t.x1))[1]::varchar(50)                            AS cascode,
                   (XPATH('/body/UsedIn/text()', t.x1))[1]::varchar(10)::int                        AS usedin,
                   (XPATH('/body/RegCode/text()', t.x1))[1]::varchar(50)                            AS regcode,
                   (XPATH('/body/FedCode/text()', t.x1))[1]::varchar(50)                            AS fedcode,
                   (XPATH('/body/IsNarcoticAndPsychotropic/text()', t.x1))[1]::varchar(10)::boolean AS IsNarcoticAndPsychotropic,
                   (XPATH('/body/EhrRusDvl105/text()', t.x1))[1]::varchar(10)::int                  AS ehrrusdvl105,
                   (XPATH('/body/EhrRusDvl105Ver/text()', t.x1))[1]::varchar(8)                     AS ehrrusdvl105ver,
                   (XPATH('/body/EhrRusNsv101/text()', t.x1))[1]::varchar(10)::int                  AS ehrrusnsv101,
                   (XPATH('/body/EhrRusNsv101Ver/text()', t.x1))[1]::varchar(8)                     AS ehrrusnsv101ver
                FROM (SELECT UNNEST(XPATH('/C_MNN/body', x))   AS x1,
                             UNNEST(XPATH('/C_MNN/header', x)) AS x2
                      FROM UNNEST(XPATH('/C_MNN', p_xml)) AS x
                     ) AS t;

            IF (v_operationtype = c_delete) THEN

                UPDATE pharm.t_c_mnn c
                SET active = FALSE
                WHERE EXISTS (SELECT 1 FROM temp_c_mnn nc WHERE nc.id = c.cmnnid AND c.usedin IN (1, 3));

            ELSIF (v_operationtype = c_update) THEN

                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,
                    IsNarcoticAndPsychotropic = COALESCE(nc.IsNarcoticAndPsychotropic, c.IsNarcoticAndPsychotropic),
                    ehrrusdvl105              = eh1.ehrrusid,
                    ehrrusnsv101              = eh2.ehrrusid
                FROM temp_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);

            ELSE

                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,
                                   IsNarcoticAndPsychotropic,
                                   namelatr,
                                   ehrrusdvl105,
                                   ehrrusnsv101)
                SELECT nc.id,
                       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,
                       COALESCE(nc.IsNarcoticAndPsychotropic, false),
                       nc.namelatr,
                       eh1.ehrrusid,
                       eh2.ehrrusid
                FROM temp_c_mnn nc
                LEFT JOIN (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 (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 1 FROM pharm.t_c_mnn c WHERE c.cmnnid = nc.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 gen_random_uuid(),
                   s.servername,
                   public.converttexttobinaryzip(v_result, ''),
                   NULL,
                   NULL,
                   v_contentencoding,
                   v_type,
                   p_user,
                   0,
                   p_datetime,
                   v_ownerid
            FROM rds.t_sync_servers s
            WHERE s.servername <> p_queuename;

        END IF;

        SELECT entityid
        INTO v_entid
        FROM vclib.t_entities
        WHERE entname = 'C_MNN';

        IF (EXISTS (SELECT 1 FROM vclib.t_gen_numbers WHERE entityid = v_entid AND numbername = 'PHARM.C_MNN')) THEN

            UPDATE vclib.t_gen_numbers gn
            SET nextnumber = (SELECT MAX(otherid) FROM pharm.t_c_mnn)
            WHERE gn.entityid = v_entid
              AND gn.numbername = 'PHARM.C_MNN';

        ELSE

            INSERT INTO
                vclib.t_gen_numbers
                (gennumbersid, entityid, numbername, nextnumber)
            VALUES (gen_random_uuid(), v_entid, 'PHARM.C_MNN', COALESCE((SELECT MAX(otherid) FROM pharm.t_c_mnn), 1));

        END IF;


    END IF;
END;
$$