DROP PROCEDURE IF EXISTS public.sp_getsetcmnn;

CREATE OR REPLACE PROCEDURE public.sp_getsetcmnn(
    "Xml" text DEFAULT NULL,
    "IsNew" boolean DEFAULT TRUE,
    "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";
    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 XMLELEMENT
                   (
                       NAME "C_MNN",
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLFOREST
                           (
                               c_ftload AS "TYPE"
                           )
                       ),
                       XMLELEMENT
                       (
                           NAME "body",
                           XMLELEMENT
                           (
                               NAME "C_MNNS",
                               (SELECT XMLAGG(
                                           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"
                                               )
                                           )
                                       )
                                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 v_result;
        
        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",
                               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
            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/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;
            
            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,
                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,
                               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,
                   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
            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/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,
                    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,
                                   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,
                       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;
$$