DROP PROCEDURE IF EXISTS public.sp_getsetmedicamentsofroszdravnadzor;

CREATE OR REPLACE PROCEDURE public.sp_getsetmedicamentsofroszdravnadzor(
    "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) := 'MEDICAMENT_OF_ROSZDRAVNADZOR';
    v_operationtype   varchar(6);
    v_ownerid         uuid;
    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
        v_ownerid := p_id;
        
        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_medicaments_of_roszdravnadzor WHERE medicamentsofroszdravnadzorid = p_id
                     )) THEN
            v_operationtype := c_update;
        ELSIF (p_isnew = FALSE AND
               NOT EXISTS (SELECT 1
                           FROM pharm.t_medicaments_of_roszdravnadzor
                           WHERE medicamentsofroszdravnadzorid = p_id
                          )) THEN
            v_operationtype := c_delete;
            SELECT XMLELEMENT(
                       NAME "MEDICAMENT_OF_ROSZDRAVNADZOR",
                       XMLELEMENT(
                           NAME "header",
                           XMLFOREST(
                               v_operationtype AS "TYPE",
                               p_id AS "ID"
                           )
                       )
                   )::text
            INTO v_result;
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            
            SELECT '<MEDICAMENT_OF_ROSZDRAVNADZOR><header><TYPE>' || c_ftload ||
                   '</TYPE></header><body><MEDICAMENTS_OF_ROSZDRAVNADZOR>' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                ''), '')
                       ||
                   '</MEDICAMENTS_OF_ROSZDRAVNADZOR></body></MEDICAMENT_OF_ROSZDRAVNADZOR>'
            INTO v_result
            FROM (SELECT XMLELEMENT(
                             NAME "MEDICAMENT_OF_ROSZDRAVNADZOR",
                             XMLFOREST(
                                 m.medicamentsofroszdravnadzorid AS "ID",
                                 m.drugid AS "DrugID",
                                 m.tradenmr AS "TradeNmR",
                                 m.innr AS "InnR",
                                 m.packnx AS "PackNx",
                                 m.dosager AS "DosageR",
                                 m.packqn AS "PackQn",
                                 m.pack AS "Pack",
                                 m.drugfmnmrs AS "DrugFmNmRS",
                                 m.segment AS "Segment",
                                 m.year AS "Year",
                                 m.month AS "Month",
                                 m.series AS "Series",
                                 m.totdrugqn AS "TotDrugQn",
                                 m.mnfprice AS "MnfPrice",
                                 m.prcprice AS "PrcPrice",
                                 m.rtlprice AS "RtlPrice",
                                 m.funds AS "Funds",
                                 m.vendorid AS "VendorID",
                                 m.remark AS "Remark",
                                 m.srcorg AS "SrcOrg",
                                 m.ean AS "EAN",
                                 m.expitermr AS "ExpiTermR",
                                 m.clnm AS "ClNm",
                                 m.mnfnm AS "MnfNm",
                                 m.pcknm AS "PckNm",
                                 m.regnr AS "RegNr",
                                 m.maxmnfprice AS "MaxMnfPrice",
                                 TO_CHAR(m.regdate, 'DD.MM.YYYY') AS "RegDate",
                                 m.active AS "Active"
                             )
                         ) AS xml_elem
                  FROM pharm.t_medicaments_of_roszdravnadzor m
                  WHERE m.active = TRUE
                 ) tt;
        
        ELSIF (v_operationtype <> c_delete) THEN
            SELECT XMLELEMENT(
                       NAME "MEDICAMENT_OF_ROSZDRAVNADZOR",
                       XMLELEMENT(
                           NAME "header",
                           XMLFOREST(
                               v_operationtype AS "TYPE",
                               p_id AS "ID"
                           )
                       ),
                       XMLELEMENT(
                           NAME "body",
                           XMLFOREST(
                               m.drugid AS "DrugID",
                               m.tradenmr AS "TradeNmR",
                               m.innr AS "InnR",
                               m.packnx AS "PackNx",
                               m.dosager AS "DosageR",
                               m.packqn AS "PackQn",
                               m.pack AS "Pack",
                               m.drugfmnmrs AS "DrugFmNmRS",
                               m.segment AS "Segment",
                               m.year AS "Year",
                               m.month AS "Month",
                               m.series AS "Series",
                               m.totdrugqn AS "TotDrugQn",
                               m.mnfprice AS "MnfPrice",
                               m.prcprice AS "PrcPrice",
                               m.rtlprice AS "RtlPrice",
                               m.funds AS "Funds",
                               m.vendorid AS "VendorID",
                               m.remark AS "Remark",
                               m.srcorg AS "SrcOrg",
                               m.ean AS "EAN",
                               m.maxmnfprice AS "MaxMnfPrice",
                               m.expitermr AS "ExpiTermR",
                               m.clnm AS "ClNm",
                               m.mnfnm AS "MnfNm",
                               m.pcknm AS "PckNm",
                               m.regnr AS "RegNr",
                               TO_CHAR(m.regdate, 'DD.MM.YYYY') AS "RegDate",
                               m.active AS "Active"
                           )
                       )
                   )::text
            INTO v_result
            FROM pharm.t_medicaments_of_roszdravnadzor m
            WHERE m.medicamentsofroszdravnadzorid = 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('/MEDICAMENT_OF_ROSZDRAVNADZOR/header/TYPE/text()', p_xml))[1]::text,
               (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/header/ID/text()', p_xml))[1]::text::uuid,
               p_xml::text
        INTO v_operationtype, v_ownerid, v_result;
        
        IF (v_operationtype = c_ftload) THEN
            DROP TABLE IF EXISTS temp_medicaments_of_rozdravnadzor;
            CREATE TEMP TABLE temp_medicaments_of_rozdravnadzor AS
                SELECT (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/ID/text()', x))[1]::text::uuid                    AS id,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/DrugID/text()', x))[1]::text::integer             AS drugid,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/TradeNmR/text()', x))[1]::text                    AS tradenmr,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/InnR/text()', x))[1]::text                        AS innr,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/PackNx/text()', x))[1]::text::bigint              AS packnx,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/DosageR/text()', x))[1]::text                     AS dosager,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/PackQn/text()', x))[1]::text::integer             AS packqn,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/Pack/text()', x))[1]::text                        AS pack,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/DrugFmNmRS/text()', x))[1]::text                  AS drugfmnmrs,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/Segment/text()', x))[1]::text                     AS segment,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/Year/text()', x))[1]::text::integer               AS year,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/Month/text()', x))[1]::text::integer              AS month,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/Series/text()', x))[1]::text                      AS series,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/TotDrugQn/text()', x))[1]::text::numeric(12, 2)   AS totdrugqn,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/MnfPrice/text()', x))[1]::text::numeric(12, 2)    AS mnfprice,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/PrcPrice/text()', x))[1]::text::numeric(12, 2)    AS prcprice,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/RtlPrice/text()', x))[1]::text::numeric(12, 2)    AS rtlprice,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/Funds/text()', x))[1]::text::numeric(12, 2)       AS funds,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/VendorID/text()', x))[1]::text                    AS vendorid,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/Remark/text()', x))[1]::text                      AS remark,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/SrcOrg/text()', x))[1]::text                      AS srcorg,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/EAN/text()', x))[1]::text                         AS ean,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/MaxMnfPrice/text()', x))[1]::text::numeric(12, 2) AS maxmnfprice,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/ExpiTermR/text()', x))[1]::text                   AS expitermr,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/ClNm/text()', x))[1]::text                        AS clnm,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/MnfNm/text()', x))[1]::text                       AS mnfnm,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/PckNm/text()', x))[1]::text                       AS pcknm,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/RegNr/text()', x))[1]::text                       AS regnr,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/RegDate/text()', x))[1]::text::timestamp          AS regdate,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/Active/text()', x))[1]::text::boolean             AS active
                FROM UNNEST(XPATH(
                    '/MEDICAMENT_OF_ROSZDRAVNADZOR/body/MEDICAMENTS_OF_ROSZDRAVNADZOR/MEDICAMENT_OF_ROSZDRAVNADZOR',
                    p_xml)) x;
            
            ANALYZE temp_medicaments_of_rozdravnadzor;
            
            UPDATE pharm.t_medicaments_of_roszdravnadzor m
            SET active = FALSE
            WHERE NOT EXISTS (SELECT 1
                              FROM temp_medicaments_of_rozdravnadzor nmf
                              WHERE nmf.id = m.medicamentsofroszdravnadzorid
                             );
            
            UPDATE pharm.t_medicaments_of_roszdravnadzor m
            SET drugid      = nmf.drugid,
                tradenmr    = nmf.tradenmr,
                innr        = nmf.innr,
                packnx      = nmf.packnx,
                dosager     = nmf.dosager,
                packqn      = nmf.packqn,
                pack        = nmf.pack,
                drugfmnmrs  = nmf.drugfmnmrs,
                segment     = nmf.segment,
                year        = nmf.year,
                month       = nmf.month,
                series      = nmf.series,
                totdrugqn   = nmf.totdrugqn,
                mnfprice    = nmf.mnfprice,
                prcprice    = nmf.prcprice,
                rtlprice    = nmf.rtlprice,
                funds       = nmf.funds,
                vendorid    = nmf.vendorid,
                remark      = nmf.remark,
                srcorg      = nmf.srcorg,
                ean         = nmf.ean,
                maxmnfprice = nmf.maxmnfprice,
                expitermr   = nmf.expitermr,
                clnm        = nmf.clnm,
                mnfnm       = nmf.mnfnm,
                pcknm       = nmf.pcknm,
                regnr       = nmf.regnr,
                regdate     = nmf.regdate,
                active      = COALESCE(nmf.active, TRUE)
            FROM temp_medicaments_of_rozdravnadzor nmf
            WHERE nmf.id = m.medicamentsofroszdravnadzorid
              AND m.active = TRUE;
            
            INSERT INTO
                pharm.t_medicaments_of_roszdravnadzor (medicamentsofroszdravnadzorid,
                                                       drugid,
                                                       tradenmr,
                                                       innr,
                                                       packnx,
                                                       dosager,
                                                       packqn,
                                                       pack,
                                                       drugfmnmrs,
                                                       segment,
                                                       year,
                                                       month,
                                                       series,
                                                       totdrugqn,
                                                       mnfprice,
                                                       prcprice,
                                                       rtlprice,
                                                       funds,
                                                       vendorid,
                                                       remark,
                                                       srcorg,
                                                       ean,
                                                       maxmnfprice,
                                                       expitermr,
                                                       clnm,
                                                       mnfnm,
                                                       pcknm,
                                                       regnr,
                                                       regdate,
                                                       active)
            SELECT nmf.id,
                   nmf.drugid,
                   nmf.tradenmr,
                   nmf.innr,
                   nmf.packnx,
                   nmf.dosager,
                   nmf.packqn,
                   nmf.pack,
                   nmf.drugfmnmrs,
                   nmf.segment,
                   nmf.year,
                   nmf.month,
                   nmf.series,
                   nmf.totdrugqn,
                   nmf.mnfprice,
                   nmf.prcprice,
                   nmf.rtlprice,
                   nmf.funds,
                   nmf.vendorid,
                   nmf.remark,
                   nmf.srcorg,
                   nmf.ean,
                   nmf.maxmnfprice,
                   nmf.expitermr,
                   nmf.clnm,
                   nmf.mnfnm,
                   nmf.pcknm,
                   nmf.regnr,
                   nmf.regdate,
                   COALESCE(nmf.active, TRUE)
            FROM temp_medicaments_of_rozdravnadzor nmf
            WHERE NOT EXISTS (SELECT 1
                              FROM pharm.t_medicaments_of_roszdravnadzor m
                              WHERE m.medicamentsofroszdravnadzorid = nmf.id
                             );
        ELSE
            DROP TABLE IF EXISTS temp_medicament_of_rozdravnadzor;
            CREATE TEMP TABLE temp_medicament_of_rozdravnadzor AS
                SELECT (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/header/ID/text()', x))[1]::text::uuid                  AS id,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/DrugID/text()', x))[1]::text::integer             AS drugid,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/TradeNmR/text()', x))[1]::text                    AS tradenmr,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/InnR/text()', x))[1]::text                        AS innr,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/PackNx/text()', x))[1]::text::bigint              AS packnx,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/DosageR/text()', x))[1]::text                     AS dosager,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/PackQn/text()', x))[1]::text::integer             AS packqn,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/Pack/text()', x))[1]::text                        AS pack,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/DrugFmNmRS/text()', x))[1]::text                  AS drugfmnmrs,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/Segment/text()', x))[1]::text                     AS segment,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/Year/text()', x))[1]::text::integer               AS year,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/Month/text()', x))[1]::text::integer              AS month,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/Series/text()', x))[1]::text                      AS series,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/TotDrugQn/text()', x))[1]::text::numeric(12, 2)   AS totdrugqn,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/MnfPrice/text()', x))[1]::text::numeric(12, 2)    AS mnfprice,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/PrcPrice/text()', x))[1]::text::numeric(12, 2)    AS prcprice,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/RtlPrice/text()', x))[1]::text::numeric(12, 2)    AS rtlprice,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/Funds/text()', x))[1]::text::numeric(12, 2)       AS funds,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/VendorID/text()', x))[1]::text                    AS vendorid,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/Remark/text()', x))[1]::text                      AS remark,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/SrcOrg/text()', x))[1]::text                      AS srcorg,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/EAN/text()', x))[1]::text                         AS ean,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/MaxMnfPrice/text()', x))[1]::text::numeric(12, 2) AS maxmnfprice,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/ExpiTermR/text()', x))[1]::text                   AS expitermr,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/ClNm/text()', x))[1]::text                        AS clnm,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/MnfNm/text()', x))[1]::text                       AS mnfnm,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/PckNm/text()', x))[1]::text                       AS pcknm,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/RegNr/text()', x))[1]::text                       AS regnr,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/RegDate/text()', x))[1]::text::timestamp
                                                                                                                    AS regdate,
                       (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/Active/text()', x))[1]::text::boolean             AS active
                FROM UNNEST(XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR', p_xml)) AS x;
            
            IF (v_operationtype = c_delete) THEN
                UPDATE pharm.t_medicaments_of_roszdravnadzor m
                SET active = FALSE
                WHERE m.medicamentsofroszdravnadzorid = (SELECT id FROM temp_medicament_of_rozdravnadzor);
            ELSIF (v_operationtype = c_update) THEN
                UPDATE pharm.t_medicaments_of_roszdravnadzor m
                SET drugid      = nmf.drugid,
                    tradenmr    = nmf.tradenmr,
                    innr        = nmf.innr,
                    packnx      = nmf.packnx,
                    dosager     = nmf.dosager,
                    packqn      = nmf.packqn,
                    pack        = nmf.pack,
                    drugfmnmrs  = nmf.drugfmnmrs,
                    segment     = nmf.segment,
                    year        = nmf.year,
                    month       = nmf.month,
                    series      = nmf.series,
                    totdrugqn   = nmf.totdrugqn,
                    mnfprice    = nmf.mnfprice,
                    prcprice    = nmf.prcprice,
                    rtlprice    = nmf.rtlprice,
                    funds       = nmf.funds,
                    vendorid    = nmf.vendorid,
                    remark      = nmf.remark,
                    srcorg      = nmf.srcorg,
                    ean         = nmf.ean,
                    maxmnfprice = nmf.maxmnfprice,
                    expitermr   = nmf.expitermr,
                    clnm        = nmf.clnm,
                    mnfnm       = nmf.mnfnm,
                    pcknm       = nmf.pcknm,
                    regnr       = nmf.regnr,
                    regdate     = nmf.regdate,
                    active      = COALESCE(nmf.active, TRUE)
                FROM temp_medicament_of_rozdravnadzor nmf
                WHERE m.medicamentsofroszdravnadzorid = nmf.id
                  AND m.active = TRUE;
            ELSIF (v_operationtype = c_insert) THEN
                INSERT INTO
                    pharm.t_medicaments_of_roszdravnadzor (medicamentsofroszdravnadzorid,
                                                           drugid,
                                                           tradenmr,
                                                           innr,
                                                           packnx,
                                                           dosager,
                                                           packqn,
                                                           pack,
                                                           drugfmnmrs,
                                                           segment,
                                                           year,
                                                           month,
                                                           series,
                                                           totdrugqn,
                                                           mnfprice,
                                                           prcprice,
                                                           rtlprice,
                                                           funds,
                                                           vendorid,
                                                           remark,
                                                           srcorg,
                                                           ean,
                                                           maxmnfprice,
                                                           expitermr,
                                                           clnm,
                                                           mnfnm,
                                                           pcknm,
                                                           regnr,
                                                           regdate,
                                                           active)
                SELECT nmf.id,
                       nmf.drugid,
                       nmf.tradenmr,
                       nmf.innr,
                       nmf.packnx,
                       nmf.dosager,
                       nmf.packqn,
                       nmf.pack,
                       nmf.drugfmnmrs,
                       nmf.segment,
                       nmf.year,
                       nmf.month,
                       nmf.series,
                       nmf.totdrugqn,
                       nmf.mnfprice,
                       nmf.prcprice,
                       nmf.rtlprice,
                       nmf.funds,
                       nmf.vendorid,
                       nmf.remark,
                       nmf.srcorg,
                       nmf.ean,
                       nmf.maxmnfprice,
                       nmf.expitermr,
                       nmf.clnm,
                       nmf.mnfnm,
                       nmf.pcknm,
                       nmf.regnr,
                       nmf.regdate,
                       COALESCE(nmf.active, TRUE)
                FROM temp_medicament_of_rozdravnadzor nmf
                WHERE NOT EXISTS (SELECT 1
                                  FROM pharm.t_medicaments_of_roszdravnadzor m
                                  WHERE m.medicamentsofroszdravnadzorid = nmf.id
                                 );
            END IF;
        END IF;
        
        IF (v_operationtype <> c_ftload) THEN
            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;
        
        UPDATE pharm.t_bill_of_goods bg
        SET medicamentsofroszdravnadzor = NULL
        FROM pharm.t_medicaments_of_roszdravnadzor mor
        WHERE mor.medicamentsofroszdravnadzorid = bg.medicamentsofroszdravnadzor
          AND mor.active = FALSE;
    END IF;
END;
$$;