DROP PROCEDURE IF EXISTS public.sp_getsetctrn;

CREATE OR REPLACE PROCEDURE public.sp_getsetctrn(
    "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_TRN';
    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_trn c WHERE c.ctrnid = p_id)) THEN
            v_operationtype = c_update;
        ELSIF (p_isnew = FALSE AND NOT EXISTS (SELECT 1 FROM pharm.t_c_trn c WHERE c.ctrnid = p_id)) THEN
            v_operationtype = c_delete;
            SELECT 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_TRN",
                       XMLELEMENT(NAME "header", XMLFOREST(c_ftload AS "TYPE")),
                       XMLELEMENT(
                           NAME "body",
                           XMLELEMENT
                           (
                               NAME "C_TRNS",
                               (SELECT XMLAGG(
                                           XMLELEMENT
                                           (
                                               NAME "C_TRN",
                                               XMLFOREST
                                               (
                                                   c.ctrnid AS "ID",
                                                   c.active AS "Active",
                                                   c.namerus AS "NameRus",
                                                   c.nameeng AS "NameEng",
                                                   c.namelatr AS "NameLatR",
                                                   c.otherid AS "OtherID",
                                                   c.characteristic AS "Characteristic",
                                                   c.pharmacologyaction AS "PharmacologyAction",
                                                   c.practice AS "Practice",
                                                   c.contraindications AS "Contraindications",
                                                   c.restrictions AS "Restrictions",
                                                   c.useofpregnancy AS "UseOfPregnancy",
                                                   c.sideeffects AS "SideEffects",
                                                   c.interaction AS "Interaction",
                                                   c.overdose AS "Overdose",
                                                   c.injectionmethod AS "InjectionMethod",
                                                   c.caution AS "Caution",
                                                   c.composition AS "Composition",
                                                   c.pharmdescription AS "PharmDescription",
                                                   c.pharmacodynamics AS "Pharmacodynamics",
                                                   c.indications AS "Indications",
                                                   c.expirationdate AS "ExpirationDate",
                                                   c.mnnname AS "MNNName",
                                                   c.manufacturer AS "Manufacturer",
                                                   c.cmnn AS "CMnn",
                                                   c.producttype AS "ProductType",
                                                   c.regcode AS "RegCode",
                                                   c.fedcode AS "FedCode",
                                                   ehr1.codeehr AS "EhrRusP08TSH954N",
                                                   rrb.currversion AS "EhrRusP08TSH954NVer",
                                                   ehr2.codeehr AS "EhrRus575VSF88OG",
                                                   rrb1.currversion AS "EhrRus575VSF88OGVer",
                                                   atc.idehr AS "Atc",
                                                   rrbatc.currversion AS "AtcVer"
                                               )
                                           )
                                       )
                                FROM pharm.t_c_trn c
                                LEFT JOIN ehr.t_ehr_rus_p08tsh954n ehr1 ON ehr1.ehrrusid = c.ehrrusp08tsh954n
                                LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = ehr1.ehrrusrefbooks
                                LEFT JOIN ehr.t_ehr_rus_575vsf88og ehr2 ON ehr2.ehrrusid = c.ehrrus575vsf88og
                                LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = ehr2.ehrrusrefbooks
                                LEFT JOIN ehr.t_ehr_rus_m00002 atc ON atc.ehrrusid = c.atc
                                LEFT JOIN ehr.t_ehr_rus_ref_books rrbatc ON rrbatc.ehrrusrefbooksid = atc.ehrrusrefbooks
                               )
                           )
                       )
                   )
            INTO v_result;
        
        ELSIF (v_operationtype <> c_delete) THEN
            
            SELECT XMLELEMENT
                   (
                       NAME "C_TRN",
                       XMLELEMENT(NAME "header", XMLFOREST(v_operationtype AS "TYPE", p_id AS "ID")),
                       XMLELEMENT
                       (
                           NAME "body",
                           XMLFOREST
                           (
                               c.ctrnid AS "ID",
                               c.active AS "Active",
                               c.namerus AS "NameRus",
                               c.nameeng AS "NameEng",
                               c.namelatr AS "NameLatR",
                               c.otherid AS "OtherID",
                               c.characteristic AS "Characteristic",
                               c.pharmacologyaction AS "PharmacologyAction",
                               c.practice AS "Practice",
                               c.contraindications AS "Contraindications",
                               c.restrictions AS "Restrictions",
                               c.useofpregnancy AS "UseOfPregnancy",
                               c.sideeffects AS "SideEffects",
                               c.interaction AS "Interaction",
                               c.overdose AS "Overdose",
                               c.injectionmethod AS "InjectionMethod",
                               c.caution AS "Caution",
                               c.composition AS "Composition",
                               c.pharmdescription AS "PharmDescription",
                               c.pharmacodynamics AS "Pharmacodynamics",
                               c.indications AS "Indications",
                               c.expirationdate AS "ExpirationDate",
                               c.mnnname AS "MNNName",
                               c.manufacturer AS "Manufacturer",
                               c.cmnn AS "CMnn",
                               c.producttype AS "ProductType",
                               c.regcode AS "RegCode",
                               c.fedcode AS "FedCode",
                               ehr1.codeehr AS "EhrRusP08TSH954N",
                               rrb.currversion AS "EhrRusP08TSH954NVer",
                               ehr2.codeehr AS "EhrRus575VSF88OG",
                               rrb1.currversion AS "EhrRus575VSF88OGVer",
                               atc.idehr AS "Atc",
                               rrbatc.currversion AS "AtcVer"
                           )
                       )
                   )
            INTO v_result
            FROM pharm.t_c_trn c
            LEFT JOIN ehr.t_ehr_rus_p08tsh954n ehr1 ON ehr1.ehrrusid = c.ehrrusp08tsh954n
            LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = ehr1.ehrrusrefbooks
            LEFT JOIN ehr.t_ehr_rus_575vsf88og ehr2 ON ehr2.ehrrusid = c.ehrrus575vsf88og
            LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = ehr2.ehrrusrefbooks
            LEFT JOIN ehr.t_ehr_rus_m00002 atc ON atc.ehrrusid = c.atc
            LEFT JOIN ehr.t_ehr_rus_ref_books rrbatc ON rrbatc.ehrrusrefbooksid = atc.ehrrusrefbooks
            WHERE c.ctrnid = p_id;
        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));
    
    ELSE
        
        SELECT (XPATH('/C_TRN/header/TYPE/text()', x))[1]::varchar(6),
               (XPATH('/C_TRN/header/ID/text()', x))[1]::text::uuid,
               p_xml::text
        INTO v_operationtype, v_ownerid, v_result
        FROM UNNEST(XPATH('/C_TRN', p_xml)) x;
        
        IF (v_operationtype = c_ftload) THEN
            CREATE TEMP TABLE temp_c_trns AS
                SELECT (XPATH('/C_TRN/ID[1]/text()', x))[1]::text::uuid                    AS id,
                       (XPATH('/C_TRN/Active[1]/text()', x))[1]::text::boolean             AS active,
                       (XPATH('/C_TRN/NameRus[1]/text()', x))[1]::varchar(4000)            AS namerus,
                       (XPATH('/C_TRN/NameEng[1]/text()', x))[1]::varchar(4000)            AS nameeng,
                       (XPATH('/C_TRN/NameLatR[1]/text()', x))[1]::varchar(4000)           AS namelatr,
                       (XPATH('/C_TRN/OtherID[1]/text()', x))[1]::text::int                AS otherid,
                       (XPATH('/C_TRN/IsDrug[1]/text()', x))[1]::text::boolean             AS isdrug,
                       (XPATH('/C_TRN/Characteristic[1]/text()', x))[1]::varchar(4000)     AS characteristic,
                       (XPATH('/C_TRN/PharmacologyAction[1]/text()', x))[1]::varchar(4000) AS pharmacologyaction,
                       (XPATH('/C_TRN/Practice[1]/text()', x))[1]::text                    AS practice,
                       (XPATH('/C_TRN/Contraindications[1]/text()', x))[1]::varchar(4000)  AS contraindications,
                       (XPATH('/C_TRN/Restrictions[1]/text()', x))[1]::text                AS restrictions,
                       (XPATH('/C_TRN/UseOfPregnancy[1]/text()', x))[1]::text              AS useofpregnancy,
                       (XPATH('/C_TRN/SideEffects[1]/text()', x))[1]::text                 AS sideeffects,
                       (XPATH('/C_TRN/Interaction[1]/text()', x))[1]::text                 AS interaction,
                       (XPATH('/C_TRN/Overdose[1]/text()', x))[1]::varchar(1000)           AS overdose,
                       (XPATH('/C_TRN/InjectionMethod[1]/text()', x))[1]::varchar(255)     AS injectionmethod,
                       (XPATH('/C_TRN/Caution[1]/text()', x))[1]::text                     AS caution,
                       (XPATH('/C_TRN/Composition[1]/text()', x))[1]::varchar(255)         AS composition,
                       (XPATH('/C_TRN/PharmDescription[1]/text()', x))[1]::text            AS pharmdescription,
                       (XPATH('/C_TRN/Pharmacodynamics[1]/text()', x))[1]::text            AS pharmacodynamics,
                       (XPATH('/C_TRN/Indications[1]/text()', x))[1]::varchar(8000)        AS indications,
                       (XPATH('/C_TRN/ExpirationDate[1]/text()', x))[1]::varchar(300)      AS expirationdate,
                       (XPATH('/C_TRN/MNNName[1]/text()', x))[1]::varchar(8000)            AS mnnname,
                       (XPATH('/C_TRN/Manufacturer[1]/text()', x))[1]::varchar(3000)       AS manufacturer,
                       (XPATH('/C_TRN/CMnn[1]/text()', x))[1]::text::uuid                  AS cmnn,
                       (XPATH('/C_TRN/Atc[1]/text()', x))[1]::integer                      AS atc,
                       (XPATH('/C_TRN/AtcVer[1]/text()', x))[1]::varchar(52)               AS atcver,
                       (XPATH('/C_TRN/ProductType[1]/text()', x))[1]::text::int            AS producttype,
                       (XPATH('/C_TRN/RegCode[1]/text()', x))[1]::varchar(50)              AS regcode,
                       (XPATH('/C_TRN/FedCode[1]/text()', x))[1]::varchar(50)              AS fedcode,
                       (XPATH('/C_TRN/EhrRusP08TSH954N[1]/text()', x))[1]::varchar(52)     AS ehrrusp08tsh954n,
                       (XPATH('/C_TRN/EhrRusP08TSH954NVer[1]/text()', x))[1]::varchar(52)  AS ehrrusp08tsh954nver,
                       (XPATH('/C_TRN/EhrRus575VSF88OG[1]/text()', x))[1]::varchar(52)     AS ehrrus575vsf88og,
                       (XPATH('/C_TRN/EhrRus575VSF88OGVer[1]/text()', x))[1]::varchar(52)  AS ehrrus575vsf88ogver
                FROM UNNEST(XPATH('/C_TRN/body/C_TRNS/C_TRN', p_xml)) AS x;
            
            UPDATE pharm.t_c_trn c
            SET active = FALSE
            WHERE NOT EXISTS(SELECT 1 FROM temp_c_trns nc WHERE nc.id = c.ctrnid);
            
            UPDATE pharm.t_c_trn c
            SET nameeng            = nc.nameeng,
                namerus            = nc.namerus,
                namelatr           = nc.namelatr,
                active             = nc.active,
                otherid            = nc.otherid,
                characteristic     = nc.characteristic,
                pharmacologyaction = nc.pharmacologyaction,
                practice           = nc.practice,
                contraindications  = nc.contraindications,
                restrictions       = nc.restrictions,
                useofpregnancy     = nc.useofpregnancy,
                sideeffects        = nc.sideeffects,
                interaction        = nc.interaction,
                overdose           = nc.overdose,
                injectionmethod    = nc.injectionmethod,
                caution            = nc.caution,
                composition        = nc.composition,
                pharmdescription   = nc.pharmdescription,
                pharmacodynamics   = nc.pharmacodynamics,
                indications        = nc.indications,
                expirationdate     = nc.expirationdate,
                mnnname            = nc.mnnname,
                manufacturer       = nc.manufacturer,
                cmnn               = nc.cmnn,
                atc                = atc.ehrrusid,
                producttype        = nc.producttype,
                regcode            = nc.regcode,
                fedcode            = nc.fedcode,
                ehrrusp08tsh954n   = eh1.ehrrusid,
                ehrrus575vsf88og   = eh2.ehrrusid
            FROM temp_c_trns nc
            LEFT JOIN LATERAL (SELECT eh.ehrrusid
                               FROM ehr.t_ehr_rus_p08tsh954n eh
                               LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                               WHERE eh.codeehr = nc.ehrrusp08tsh954n
                                 AND rrb.currversion = nc.ehrrusp08tsh954nver
                               LIMIT 1) eh1 ON TRUE
            LEFT JOIN LATERAL ( SELECT eh.ehrrusid
                                FROM ehr.t_ehr_rus_575vsf88og eh
                                LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = eh.ehrrusrefbooks
                                WHERE eh.codeehr = nc.ehrrus575vsf88og
                                  AND rrb1.currversion = nc.ehrrus575vsf88ogver
                                LIMIT 1
                ) eh2 ON TRUE
            LEFT JOIN LATERAL (SELECT eh.ehrrusid
                               FROM ehr.t_ehr_rus_m00002 eh
                               LEFT JOIN ehr.t_ehr_rus_ref_books rrbatc ON rrbatc.ehrrusrefbooksid = eh.ehrrusrefbooks
                               WHERE eh.idehr = nc.atc
                                 AND rrbatc.currversion = nc.atcver
                               LIMIT 1) atc ON TRUE
            WHERE nc.id = c.ctrnid;
            
            INSERT INTO pharm.t_c_trn
            (ctrnid,
             namerus,
             nameeng,
             active,
             otherid,
             characteristic,
             pharmacologyaction,
             practice,
             contraindications,
             restrictions,
             useofpregnancy,
             sideeffects,
             interaction,
             overdose,
             injectionmethod,
             caution,
             composition,
             pharmdescription,
             pharmacodynamics,
             indications,
             expirationdate,
             mnnname,
             manufacturer,
             cmnn,
             atc,
             producttype,
             regcode,
             fedcode,
             ehrrusp08tsh954n,
             ehrrus575vsf88og,
             namelatr)
            SELECT nc.id
                 , nc.namerus
                 , nc.nameeng
                 , nc.active
                 , nc.otherid
                 , nc.characteristic
                 , nc.pharmacologyaction
                 , nc.practice
                 , nc.contraindications
                 , nc.restrictions
                 , nc.useofpregnancy
                 , nc.sideeffects
                 , nc.interaction
                 , nc.overdose
                 , nc.injectionmethod
                 , nc.caution
                 , nc.composition
                 , nc.pharmdescription
                 , nc.pharmacodynamics
                 , nc.indications
                 , nc.expirationdate
                 , nc.mnnname
                 , nc.manufacturer
                 , nc.cmnn
                 , atc.ehrrusid
                 , nc.producttype
                 , nc.regcode
                 , nc.fedcode
                 , eh1.ehrrusid
                 , eh2.ehrrusid
                 , nc.namelatr
            FROM temp_c_trns nc
            LEFT JOIN LATERAL (SELECT eh.ehrrusid
                               FROM ehr.t_ehr_rus_p08tsh954n eh
                               LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                               WHERE eh.codeehr = nc.ehrrusp08tsh954n
                                 AND rrb.currversion = nc.ehrrusp08tsh954nver
                               LIMIT 1) eh1 ON TRUE
            LEFT JOIN LATERAL (SELECT eh.ehrrusid
                               FROM ehr.t_ehr_rus_575vsf88og eh
                               LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = eh.ehrrusrefbooks
                               WHERE eh.codeehr = nc.ehrrus575vsf88og
                                 AND rrb1.currversion = nc.ehrrus575vsf88ogver
                               LIMIT 1) eh2 ON TRUE
            LEFT JOIN LATERAL (SELECT eh.ehrrusid
                               FROM ehr.t_ehr_rus_m00002 eh
                               LEFT JOIN ehr.t_ehr_rus_ref_books rrbatc ON rrbatc.ehrrusrefbooksid = eh.ehrrusrefbooks
                               WHERE eh.idehr = nc.atc
                                 AND rrbatc.currversion = nc.atcver
                               LIMIT 1) atc ON TRUE
            WHERE NOT EXISTS (SELECT 1 FROM pharm.t_c_trn c WHERE c.ctrnid = nc.id);
            
            DELETE
            FROM pharm.t_c_trn c
            WHERE c.active = FALSE
              AND NOT EXISTS (SELECT 1
                              FROM pharm.t_bill_of_goods bg
                              WHERE bg.ctrn = c.ctrnid
                             );
        
        ELSE
            CREATE TEMP TABLE temp_c_trn AS
                SELECT (XPATH('/header/C_TRN/ID[1]/text()', x))[1]::text::uuid                  AS id,
                       (XPATH('/body/C_TRN/Active[1]/text()', x))[1]::text::boolean             AS active,
                       (XPATH('/body/C_TRN/NameRus[1]/text()', x))[1]::varchar(4000)            AS namerus,
                       (XPATH('/body/C_TRN/NameEng[1]/text()', x))[1]::varchar(4000)            AS nameeng,
                       (XPATH('/body/C_TRN/NameLatR[1]/text()', x))[1]::varchar(4000)           AS namelatr,
                       (XPATH('/body/C_TRN/OtherID[1]/text()', x))[1]::text::int                AS otherid,
                       (XPATH('/body/C_TRN/IsDrug[1]/text()', x))[1]::text::boolean             AS isdrug,
                       (XPATH('/body/C_TRN/Characteristic[1]/text()', x))[1]::varchar(4000)     AS characteristic,
                       (XPATH('/body/C_TRN/PharmacologyAction[1]/text()', x))[1]::varchar(4000) AS pharmacologyaction,
                       (XPATH('/body/C_TRN/Practice[1]/text()', x))[1]::text                    AS practice,
                       (XPATH('/body/C_TRN/Contraindications[1]/text()', x))[1]::varchar(4000)  AS contraindications,
                       (XPATH('/body/C_TRN/Restrictions[1]/text()', x))[1]::text                AS restrictions,
                       (XPATH('/body/C_TRN/UseOfPregnancy[1]/text()', x))[1]::text              AS useofpregnancy,
                       (XPATH('/body/C_TRN/SideEffects[1]/text()', x))[1]::text                 AS sideeffects,
                       (XPATH('/body/C_TRN/Interaction[1]/text()', x))[1]::text                 AS interaction,
                       (XPATH('/body/C_TRN/Overdose[1]/text()', x))[1]::varchar(1000)           AS overdose,
                       (XPATH('/body/C_TRN/InjectionMethod[1]/text()', x))[1]::varchar(255)     AS injectionmethod,
                       (XPATH('/body/C_TRN/Caution[1]/text()', x))[1]::text                     AS caution,
                       (XPATH('/body/C_TRN/Composition[1]/text()', x))[1]::varchar(255)         AS composition,
                       (XPATH('/body/C_TRN/PharmDescription[1]/text()', x))[1]::text            AS pharmdescription,
                       (XPATH('/body/C_TRN/Pharmacodynamics[1]/text()', x))[1]::text            AS pharmacodynamics,
                       (XPATH('/body/C_TRN/Indications[1]/text()', x))[1]::varchar(8000)        AS indications,
                       (XPATH('/body/C_TRN/ExpirationDate[1]/text()', x))[1]::varchar(300)      AS expirationdate,
                       (XPATH('/body/C_TRN/MNNName[1]/text()', x))[1]::varchar(8000)            AS mnnname,
                       (XPATH('/body/C_TRN/Manufacturer[1]/text()', x))[1]::varchar(3000)       AS manufacturer,
                       (XPATH('/body/C_TRN/CMnn[1]/text()', x))[1]::text::uuid                  AS cmnn,
                       (XPATH('/body/C_TRN/Atc[1]/text()', x))[1]::integer                      AS atc,
                       (XPATH('/body/C_TRN/AtcVer[1]/text()', x))[1]::varchar(52)               AS atcver,
                       (XPATH('/body/C_TRN/ProductType[1]/text()', x))[1]::text::int            AS producttype,
                       (XPATH('/body/C_TRN/RegCode[1]/text()', x))[1]::varchar(50)              AS regcode,
                       (XPATH('/body/C_TRN/FedCode[1]/text()', x))[1]::varchar(50)              AS fedcode,
                       (XPATH('/body/C_TRN/EhrRusP08TSH954N[1]/text()', x))[1]::varchar(52)     AS ehrrusp08tsh954n,
                       (XPATH('/body/C_TRN/EhrRusP08TSH954NVer[1]/text()', x))[1]::varchar(52)  AS ehrrusp08tsh954nver,
                       (XPATH('/body/C_TRN/EhrRus575VSF88OG[1]/text()', x))[1]::varchar(52)     AS ehrrus575vsf88og,
                       (XPATH('/body/C_TRN/EhrRus575VSF88OGVer[1]/text()', x))[1]::varchar(52)  AS ehrrus575vsf88ogver
                FROM UNNEST(XPATH('/C_TRN', p_xml)) AS x;
            
            IF (v_operationtype = c_delete) THEN
                
                UPDATE pharm.t_c_trn c
                SET active = FALSE
                FROM pharm.t_c_trn c
                JOIN temp_c_trn nc ON nc.id = c.ctrnid;
            
            ELSE
                
                UPDATE pharm.t_c_trn c
                SET nameeng            = nc.nameeng,
                    namerus            = nc.namerus,
                    namelatr           = nc.namelatr,
                    otherid            = nc.otherid,
                    characteristic     = nc.characteristic,
                    pharmacologyaction = nc.pharmacologyaction,
                    practice           = nc.practice,
                    contraindications  = nc.contraindications,
                    restrictions       = nc.restrictions,
                    useofpregnancy     = nc.useofpregnancy,
                    sideeffects        = nc.sideeffects,
                    interaction        = nc.interaction,
                    overdose           = nc.overdose,
                    injectionmethod    = nc.injectionmethod,
                    caution            = nc.caution,
                    composition        = nc.composition,
                    pharmdescription   = nc.pharmdescription,
                    pharmacodynamics   = nc.pharmacodynamics,
                    indications        = nc.indications,
                    expirationdate     = nc.expirationdate,
                    mnnname            = nc.mnnname,
                    manufacturer       = nc.manufacturer,
                    cmnn               = nc.cmnn,
                    atc                = atc.ehrrusid,
                    producttype        = nc.producttype,
                    regcode            = nc.regcode,
                    fedcode            = nc.fedcode,
                    ehrrusp08tsh954n   = eh1.ehrrusid,
                    ehrrus575vsf88og   = eh2.ehrrusid
                FROM pharm.t_c_trn c
                JOIN temp_c_trn nc ON nc.id = c.ctrnid
                LEFT JOIN LATERAL (SELECT eh.ehrrusid
                                   FROM ehr.t_ehr_rus_p08tsh954n eh
                                   LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                                   WHERE eh.codeehr = nc.ehrrusp08tsh954n
                                     AND rrb.currversion = nc.ehrrusp08tsh954nver) eh1 ON TRUE
                LEFT JOIN LATERAL (SELECT eh.ehrrusid
                                   FROM ehr.t_ehr_rus_575vsf88og eh
                                   LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = eh.ehrrusrefbooks
                                   WHERE eh.codeehr = nc.ehrrus575vsf88og
                                     AND rrb1.currversion = nc.ehrrus575vsf88ogver
                                   LIMIT 1) eh2 ON TRUE
                LEFT JOIN LATERAL (SELECT eh.ehrrusid
                                   FROM ehr.t_ehr_rus_m00002 eh
                                   LEFT JOIN ehr.t_ehr_rus_ref_books rrbatc
                                       ON rrbatc.ehrrusrefbooksid = eh.ehrrusrefbooks
                                   WHERE eh.idehr = nc.atc
                                     AND rrbatc.currversion = nc.atcver
                                   LIMIT 1) atc ON TRUE;
            
            END IF;
            
            INSERT INTO
                pharm.t_c_trn (ctrnid,
                               namerus,
                               nameeng,
                               active,
                               otherid,
                               characteristic,
                               pharmacologyaction,
                               practice,
                               contraindications,
                               restrictions,
                               useofpregnancy,
                               sideeffects,
                               interaction,
                               overdose,
                               injectionmethod,
                               caution,
                               composition,
                               pharmdescription,
                               pharmacodynamics,
                               indications,
                               expirationdate,
                               mnnname,
                               manufacturer,
                               cmnn,
                               atc,
                               producttype,
                               regcode,
                               fedcode,
                               ehrrusp08tsh954n,
                               ehrrus575vsf88og,
                               namelatr)
            SELECT nc.id
                 , nc.namerus
                 , nc.nameeng
                 , nc.active
                 , nc.otherid
                 , nc.characteristic
                 , nc.pharmacologyaction
                 , nc.practice
                 , nc.contraindications
                 , nc.restrictions
                 , nc.useofpregnancy
                 , nc.sideeffects
                 , nc.interaction
                 , nc.overdose
                 , nc.injectionmethod
                 , nc.caution
                 , nc.composition
                 , nc.pharmdescription
                 , nc.pharmacodynamics
                 , nc.indications
                 , nc.expirationdate
                 , nc.mnnname
                 , nc.manufacturer
                 , nc.cmnn
                 , atc.ehrrusid
                 , nc.producttype
                 , nc.regcode
                 , nc.fedcode
                 , eh1.ehrrusid
                 , eh2.ehrrusid
                 , nc.namelatr
            FROM temp_c_trn nc
            LEFT JOIN LATERAL (SELECT eh.ehrrusid
                               FROM ehr.t_ehr_rus_p08tsh954n eh
                               LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                               WHERE eh.codeehr = nc.ehrrusp08tsh954n
                                 AND rrb.currversion = nc.ehrrusp08tsh954nver) eh1 ON TRUE
            LEFT JOIN LATERAL (SELECT eh.ehrrusid
                               FROM ehr.t_ehr_rus_575vsf88og eh
                               LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = eh.ehrrusrefbooks
                               WHERE eh.codeehr = nc.ehrrus575vsf88og
                                 AND rrb1.currversion = nc.ehrrus575vsf88ogver
                               LIMIT 1) eh2 ON TRUE
            LEFT JOIN LATERAL (SELECT eh.ehrrusid
                               FROM ehr.t_ehr_rus_m00002 eh
                               LEFT JOIN ehr.t_ehr_rus_ref_books rrbatc
                                   ON rrbatc.ehrrusrefbooksid = eh.ehrrusrefbooks
                               WHERE eh.idehr = nc.atc
                                 AND rrbatc.currversion = nc.atcver
                               LIMIT 1) atc ON TRUE
            WHERE NOT EXISTS (SELECT 1 FROM pharm.t_c_trn c WHERE c.ctrnid = nc.id);
            
            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_TRN';
        
        IF (EXISTS (SELECT 1 FROM vclib.t_gen_numbers WHERE entityid = v_entid AND numbername = 'PHARM.C_TRN')) THEN
            
            UPDATE vclib.t_gen_numbers gn
            SET nextnumber = (SELECT MAX(otherid) FROM pharm.t_c_trn)
            WHERE gn.entityid = v_entid
              AND gn.nextnumber = 'PHARM.C_TRN';
        
        ELSE
            
            INSERT INTO
                vclib.t_gen_numbers (gennumbersid, entityid, numbername, nextnumber)
            VALUES (gen_random_uuid(), v_entid, 'PHARM.C_TRN', COALESCE((SELECT MAX(otherid) FROM pharm.t_c_trn), 1));
        
        END IF;
    
    END IF;
END;
$$
