DROP PROCEDURE IF EXISTS public.sp_getsetbillofgoods;

CREATE OR REPLACE PROCEDURE public.sp_getsetbillofgoods(
    "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) := 'BILL_OF_GOODS';
    v_operationtype   varchar(255);
    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_bill_of_goods bg WHERE bg.billofgoodsid = p_id)) THEN
            v_operationtype := c_update;
        ELSIF (p_isnew = FALSE AND
               NOT EXISTS (SELECT 1 FROM pharm.t_bill_of_goods bg WHERE bg.billofgoodsid = p_id)) THEN
            v_operationtype := c_delete;
            SELECT XMLELEMENT(NAME "BILL_OF_GOODS",
                              XMLELEMENT
                              (
                                  NAME "header",
                                  XMLFOREST
                                  (
                                      v_operationtype AS "TYPE",
                                      p_id AS "ID"
                                  )
                              )
                   )
            INTO v_result;
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            SELECT '<BILL_OF_GOODS><header><TYPE>' || c_ftload || '</TYPE></header><body><BILL_OF_GOODS_LIST>' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                ''
                            ), '') ||
                   '</BILL_OF_GOODS_LIST></body></BILL_OF_GOODS>'
            INTO v_result
            FROM (SELECT XMLELEMENT(
                             NAME "BILL_OF_GOODS",
                             XMLFOREST(
                                 bg.billofgoodsid AS "ID",
                                 dv.value AS "DoseView",
                                 bg.otherid AS "OtherID",
                                 bg.isvital AS "IsVital",
                                 bg.active AS "Active",
                                 bg.rate AS "Rate",
                                 bg.exportcode AS "ExportCode",
                                 bg.packquantity AS "PackQuantity",
                                 bg.federalid AS "FederalID",
                                 bg.medicamentproducer AS "MedicamentProducer",
                                 bg.ctrn AS "CTrn",
                                 bg.medicationforms AS "MedicationForms",
                                 bg.productinfo AS "ProductInfo",
                                 bg.dlo AS "Dlo",
                                 bg.gtin AS "Gtin",
                                 bg.ean AS "EAN",
                                 bg.medicamentsofroszdravnadzor AS "MedicamentsOfRoszdravnadzor",
                                 bg.regcode AS "RegCode",
                                 bg.fedcode AS "FedCode",
                                 er.idehr AS "EhrRus2m69i03gtd",
                                 rrb.currversion AS "EhrRus2m69i03gtdVer",
                                 er1.codeehr AS "EhrRus9a9g09e8kl",
                                 rrb1.currversion AS "EhrRus9a9g09e8klVer",
                                 er2.idehr AS "EhrRusClp14fvirj",
                                 rrb2.currversion AS "EhrRusClp14fvirjVer",
                                 er3.idehr AS "EhrRusH17F8UTS3N",
                                 rrb3.currversion AS "EhrRusH17F8UTS3NVer",
                                 er4.idehr AS "EhrRusDsg207",
                                 rrb4.currversion AS "EhrRusDsg207Ver"
                             )
                         ) AS xml_elem
                  FROM pharm.t_bill_of_goods         bg
                  LEFT JOIN pharm.s_dlo_doseview     dv ON dv.id = bg.doseview
                  LEFT JOIN ehr.t_ehr_rus_2m69i03gtd er ON er.ehrrusid = bg.ehrrus2m69i03gtd
                  LEFT JOIN ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                  LEFT JOIN ehr.t_ehr_rus_9a9g09e8kl er1 ON er1.ehrrusid = bg.ehrrus9a9g09e8kl
                  LEFT JOIN ehr.t_ehr_rus_ref_books  rrb1 ON rrb1.ehrrusrefbooksid = er1.ehrrusrefbooks
                  LEFT JOIN ehr.t_ehr_rus_clp14fvirj er2 ON er2.ehrrusid = bg.ehrrusclp14fvirj
                  LEFT JOIN ehr.t_ehr_rus_ref_books  rrb2 ON rrb2.ehrrusrefbooksid = er2.ehrrusrefbooks
                  LEFT JOIN ehr.t_ehr_rus_h17f8uts3n er3 ON er3.ehrrusid = bg.ehrrush17f8uts3n
                  LEFT JOIN ehr.t_ehr_rus_ref_books  rrb3 ON rrb3.ehrrusrefbooksid = er3.ehrrusrefbooks
                  LEFT JOIN ehr.t_ehr_rus_dsg207     er4 ON er4.ehrrusid = bg.ehrrusdsg207
                  LEFT JOIN ehr.t_ehr_rus_ref_books  rrb4 ON rrb4.ehrrusrefbooksid = er4.ehrrusrefbooks
                 ) tt;
        ELSIF (v_operationtype <> c_delete) THEN
            
            SELECT XMLELEMENT
                   (
                       NAME "BILL_OF_GOODS",
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLFOREST
                           (
                               v_operationtype AS "TYPE",
                               p_id AS "ID"
                           )
                       ),
                       XMLELEMENT
                       (
                           NAME "body",
                           XMLFOREST
                           (
                               dv.value AS "DoseView",
                               bg.otherid AS "OtherID",
                               bg.isvital AS "IsVital",
                               bg.active AS "Active",
                               bg.rate AS "Rate",
                               bg.exportcode AS "ExportCode",
                               bg.packquantity AS "PackQuantity",
                               bg.federalid AS "FederalID",
                               bg.medicamentproducer AS "MedicamentProducer",
                               bg.ctrn AS "CTrn",
                               bg.medicationforms AS "MedicationForms",
                               bg.productinfo AS "ProductInfo",
                               bg.dlo AS "Dlo",
                               bg.roundtowriteoff AS "RoundToWriteOff",
                               bg.gtin AS "Gtin",
                               bg.ean AS "EAN",
                               bg.medicamentsofroszdravnadzor AS
                               "MedicamentsOfRoszdravnadzor",
                               bg.regcode AS "RegCode",
                               bg.fedcode AS "FedCode",
                               er.idehr AS "EhrRus2m69i03gtd",
                               rrb.currversion AS "EhrRus2m69i03gtdVer",
                               er1.codeehr AS "EhrRus9a9g09e8kl",
                               rrb1.currversion AS "EhrRus9a9g09e8klVer",
                               er2.idehr AS "EhrRusClp14fvirj",
                               rrb2.currversion AS "EhrRusClp14fvirjVer",
                               er3.idehr AS "EhrRusH17F8UTS3N",
                               rrb3.currversion AS "EhrRusH17F8UTS3NVer",
                               er4.idehr AS "EhrRusDsg207",
                               rrb4.currversion AS "EhrRusDsg207Ver"
                           )
                       )
                   )
            INTO v_result
            FROM pharm.t_bill_of_goods         bg
            LEFT JOIN pharm.s_dlo_doseview     dv ON dv.id = bg.doseview
            LEFT JOIN ehr.t_ehr_rus_2m69i03gtd er ON er.ehrrusid = bg.ehrrus2m69i03gtd
            LEFT JOIN ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
            LEFT JOIN ehr.t_ehr_rus_9a9g09e8kl er1 ON er1.ehrrusid = bg.ehrrus9a9g09e8kl
            LEFT JOIN ehr.t_ehr_rus_ref_books  rrb1
                          ON rrb1.ehrrusrefbooksid = er1.ehrrusrefbooks
            LEFT JOIN ehr.t_ehr_rus_clp14fvirj er2 ON er2.ehrrusid = bg.ehrrusclp14fvirj
            LEFT JOIN ehr.t_ehr_rus_ref_books  rrb2
                          ON rrb2.ehrrusrefbooksid = er2.ehrrusrefbooks
            LEFT JOIN ehr.t_ehr_rus_h17f8uts3n er3 ON er3.ehrrusid = bg.ehrrush17f8uts3n
            LEFT JOIN ehr.t_ehr_rus_ref_books  rrb3
                          ON rrb3.ehrrusrefbooksid = er3.ehrrusrefbooks
            LEFT JOIN ehr.t_ehr_rus_dsg207     er4 ON er4.ehrrusid = bg.ehrrusdsg207
            LEFT JOIN ehr.t_ehr_rus_ref_books  rrb4
                          ON rrb4.ehrrusrefbooksid = er4.ehrrusrefbooks
            WHERE bg.billofgoodsid = 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));
    
    ELSIF (p_xml IS NOT NULL) THEN
        CREATE TEMP TABLE temp_pharmdv AS
            SELECT REPLACE(dv.value, ' ', '') AS value,
                   MAX(dv.id)                 AS id
            FROM pharm.s_dlo_doseview dv
            GROUP BY REPLACE(dv.value, ' ', '');
        
        SELECT (XPATH('/header/TYPE/text()', x))[1]::text,
               (XPATH('/header/ID/text()', x))[1]::text::uuid,
               p_xml :: text
        INTO v_operationtype, v_ownerid, v_result
        FROM UNNEST(XPATH('/BILL_OF_GOODS/header', p_xml)) x;
        
        IF (v_operationtype = c_ftload) THEN
            CREATE TEMP TABLE temp_bill_of_goods_list AS
                SELECT (XPATH('BILL_OF_GOODS/ID/text()', t.x1))[1]::text::uuid                           AS id,
                       (XPATH('/BILL_OF_GOODS/DoseView/text()', t.x2))[1]::varchar(255)                  AS doseview,
                       REPLACE((XPATH('/BILL_OF_GOODS/DoseViewRep/text()', t.x2))[1]::varchar(1000), ' ',
                               '')                                                                       AS doseviewrep,
                       (XPATH('/BILL_OF_GOODS/OtherID/text()', t.x2))[1]::text::int                      AS otherid,
                       (XPATH('/BILL_OF_GOODS/IsVital/text()', t.x2))[1]::text::boolean                  AS isvital,
                       (XPATH('/BILL_OF_GOODS/Active/text()', t.x2))[1]::text::boolean                   AS active,
                       (XPATH('/BILL_OF_GOODS/Rate/text()', t.x2))[1]::text::int                         AS rate,
                       (XPATH('/BILL_OF_GOODS/ExportCode/text()', t.x2))[1]::text::int                   AS exportcode,
                       (XPATH('/BILL_OF_GOODS/PackQuantity/text()', t.x2))[1]::text::int                 AS packquantity,
                       (XPATH('/BILL_OF_GOODS/FederalID/text()', t.x2))[1]::text::int                    AS federalid,
                       (XPATH('/BILL_OF_GOODS/MedicamentProducer/text()', t.x2))[1]::text::int           AS medicamentproducer,
                       (XPATH('/BILL_OF_GOODS/CTrn/text()', t.x2))[1]::text::uuid                        AS ctrn,
                       (XPATH('/BILL_OF_GOODS/MedicationForms/text()', t.x2))[1]::text::int              AS medicationforms,
                       (XPATH('/BILL_OF_GOODS/ProductInfo/text()', t.x2))[1]::text                       AS productinfo,
                       (XPATH('/BILL_OF_GOODS/Dlo/text()', t.x2))[1]::text::boolean                      AS dlo,
                       (XPATH('/BILL_OF_GOODS/RoundToWriteOff/text()', t.x2))[1]::text::boolean          AS roundtowriteoff,
                       (XPATH('/BILL_OF_GOODS/Gtin/text()', t.x2))[1]::varchar(4)                        AS gtin,
                       (XPATH('/BILL_OF_GOODS/EAN/text()', t.x2))[1]::varchar(255)                       AS ean,
                       (XPATH('/BILL_OF_GOODS/MedicamentsOfRoszdravnadzor/text()', t.x2))[1]::text::uuid AS medicamentsofroszdravnadzor,
                       (XPATH('/BILL_OF_GOODS/RegCode/text()', t.x2))[1]::varchar(50)                    AS regcode,
                       (XPATH('/BILL_OF_GOODS/FedCode/text()', t.x2))[1]::varchar(50)                    AS fedcode,
                       (XPATH('/BILL_OF_GOODS/EhrRus2m69i03gtd/text()', t.x2))[1]::text::int             AS ehrrus2m69i03gtd,
                       (XPATH('/BILL_OF_GOODS/EhrRus2m69i03gtdVer/text()', t.x2))[1]::varchar(8)         AS ehrrus2m69i03gtdver,
                       (XPATH('/BILL_OF_GOODS/EhrRus9a9g09e8kl/text()', t.x2))[1]::varchar(52)           AS ehrrus9a9g09e8kl,
                       (XPATH('/BILL_OF_GOODS/EhrRus9a9g09e8klVer/text()', t.x2))[1]::varchar(8)         AS ehrrus9a9g09e8klver,
                       (XPATH('/BILL_OF_GOODS/EhrRusClp14fvirj/text()', t.x2))[1]::text::int             AS ehrrusclp14fvirj,
                       (XPATH('/BILL_OF_GOODS/EhrRusClp14fvirjVer/text()', t.x2))[1]::varchar(8)         AS ehrrusclp14fvirjver,
                       (XPATH('/BILL_OF_GOODS/EhrRusH17F8UTS3N/text()', t.x2))[1]::text::int             AS ehrrush17f8uts3n,
                       (XPATH('/BILL_OF_GOODS/EhrRusH17F8UTS3NVer/text()', t.x2))[1]::varchar(8)         AS ehrrush17f8uts3nver,
                       (XPATH('/BILL_OF_GOODS/EhrRusDsg207/text()', t.x2))[1]::text::int                 AS ehrrusdsg207,
                       (XPATH('/BILL_OF_GOODS/EhrRusDsg207Ver/text()', t.x2))[1]::varchar(8)             AS ehrrusdsg207ver
                FROM (SELECT UNNEST(XPATH('/BILL_OF_GOODS/header', x))                                AS x1,
                             UNNEST(XPATH('/BILL_OF_GOODS/body/BILL_OF_GOODS_LIST/BILL_OF_GOODS', x)) AS x2
                      FROM UNNEST(XPATH('/BILL_OF_GOODS', p_xml)) x
                     ) t;
            
            INSERT INTO pharm.s_dlo_doseview (value)
            SELECT bgl.doseview
            FROM temp_bill_of_goods bgl
            WHERE bgl.doseview IS NOT NULL
              AND NOT EXISTS (SELECT 1
                              FROM temp_pharmdv dv
                              WHERE dv.value = bgl.doseviewrep
                             )
            GROUP BY bgl.doseview;
            
            TRUNCATE TABLE temp_pharmdv;
            
            INSERT INTO temp_pharmdv (value, id)
            SELECT REPLACE(dv.value, ' ', '') AS value, MAX(dv.id) AS id
            FROM pharm.s_dlo_doseview dv
            GROUP BY REPLACE(dv.value, ' ', '');
            
            UPDATE pharm.t_bill_of_goods bf
            SET active = FALSE
            WHERE NOT EXISTS (SELECT 1 FROM temp_bill_of_goods_list bgl WHERE bgl.id = bf.billofgoodsid);
            
            UPDATE pharm.t_bill_of_goods bf
            SET doseview                    = dv.id,
                otherid                     = bgl.otherid,
                isvital                     = COALESCE(bgl.isvital, 0),
                active                      = bgl.active,
                rate                        = COALESCE(bgl.rate, 1),
                exportcode                  = bgl.exportcode,
                packquantity                = bgl.packquantity,
                federalid                   = bgl.federalid,
                medicamentproducer          = bgl.medicamentproducer,
                ctrn                        = bgl.ctrn,
                medicationforms             = bgl.medicationforms,
                productinfo                 = bgl.productinfo,
                dlo                         = COALESCE(bgl.dlo, 0),
                roundtowriteoff             = COALESCE(bgl.roundtowriteoff, 0),
                gtin                        = bgl.gtin,
                ean                         = bgl.ean,
                medicamentsofroszdravnadzor = bgl.medicamentsofroszdravnadzor,
                regcode                     = bgl.regcode,
                fedcode                     = bgl.fedcode,
                ehrrus2m69i03gtd            = ehr.ehrrusid,
                ehrrus9a9g09e8kl            = ehr1.ehrrusid,
                ehrrusclp14fvirj            = ehr2.ehrrusid,
                ehrrush17f8uts3n            = ehr3.ehrrusid,
                ehrrusdsg207                = ehr4.ehrrusid
            FROM temp_bill_of_goods_list bgl
            LEFT JOIN temp_pharmdv       dv ON dv.value = bgl.doseviewrep
            LEFT JOIN LATERAL (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_2m69i03gtd er
                          JOIN ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = bgl.ehrrus2m69i03gtd
                            AND rrb.currversion = bgl.ehrrus2m69i03gtdver
                          LIMIT 1
                          )              ehr ON TRUE
            LEFT JOIN LATERAL (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_9a9g09e8kl er
                          JOIN ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.codeehr = bgl.ehrrus9a9g09e8kl
                            AND rrb.currversion = bgl.ehrrus9a9g09e8klver
                          LIMIT 1
                          )              ehr1 ON TRUE
            LEFT JOIN LATERAL (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_clp14fvirj er
                          JOIN ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = bgl.ehrrusclp14fvirj
                            AND rrb.currversion = bgl.ehrrusclp14fvirjver
                          LIMIT 1
                          )              ehr2 ON TRUE
            LEFT JOIN LATERAL (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_h17f8uts3n er
                          JOIN ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = bgl.ehrrush17f8uts3n
                            AND rrb.currversion = bgl.ehrrush17f8uts3nver
                          LIMIT 1
                          )              ehr3 ON TRUE
            LEFT JOIN LATERAL (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_dsg207    er
                          JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          )              ehr4 ON TRUE
            WHERE bgl.id = bf.billofgoodsid;
            
            INSERT INTO
                pharm.t_bill_of_goods
            (billofgoodsid,
             ctrn,
             active,
             medicamentproducer,
             otherid,
             medicationforms,
             doseview,
             rate,
             isvital,
             packquantity,
             exportcode,
             federalid,
             productinfo,
             dlo,
             roundtowriteoff,
             gtin,
             ean,
             medicamentsofroszdravnadzor,
             regcode,
             fedcode,
             ehrrus2m69i03gtd,
             ehrrus9a9g09e8kl,
             ehrrusclp14fvirj,
             ehrrush17f8uts3n,
             ehrrusdsg207)
            SELECT bgl.id,
                   bgl.ctrn,
                   bgl.active,
                   bgl.medicamentproducer,
                   bgl.otherid,
                   bgl.medicationforms,
                   dv.id,
                   COALESCE(bgl.rate, 1),
                   COALESCE(bgl.isvital, '0'),
                   bgl.packquantity,
                   bgl.exportcode,
                   bgl.federalid,
                   bgl.productinfo,
                   COALESCE(bgl.dlo, '0'),
                   COALESCE(bgl.roundtowriteoff, '0'),
                   bgl.gtin,
                   bgl.ean,
                   bgl.medicamentsofroszdravnadzor,
                   bgl.regcode,
                   bgl.fedcode,
                   ehr.ehrrusid,
                   ehr1.ehrrusid,
                   ehr2.ehrrusid,
                   ehr3.ehrrusid,
                   ehr4.ehrrusid
            FROM temp_bill_of_goods_list bgl
            LEFT JOIN temp_pharmdv       dv ON dv.value = bgl.doseviewrep
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_2m69i03gtd     er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = bgl.ehrrus2m69i03gtd
                            AND rrb.currversion = bgl.ehrrus2m69i03gtdver
                          LIMIT 1
                          )              ehr ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_9a9g09e8kl     er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.codeehr = bgl.ehrrus9a9g09e8kl
                            AND rrb.currversion = bgl.ehrrus9a9g09e8klver
                          LIMIT 1
                          )              ehr1 ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_clp14fvirj     er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = bgl.ehrrusclp14fvirj
                            AND rrb.currversion = bgl.ehrrusclp14fvirjver
                          LIMIT 1
                          )              ehr2 ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_h17f8uts3n     er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = bgl.ehrrush17f8uts3n
                            AND rrb.currversion = bgl.ehrrush17f8uts3nver
                          LIMIT 1
                          )              ehr3 ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_dsg207         er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = bgl.ehrrusdsg207
                            AND rrb.currversion = bgl.ehrrusdsg207ver
                          LIMIT 1
                          )              ehr4 ON TRUE
            WHERE NOT EXISTS
                      (SELECT *
                       FROM pharm.t_bill_of_goods bg
                       WHERE bg.billofgoodsid = bgl.id
                      );
        ELSE
            CREATE TEMP TABLE temp_bill_of_goods AS
                SELECT (XPATH('/header/ID/text()', t.x1))[1]::text::uuid                             AS id,
                       (XPATH('/body/DoseView/text()', t.x2))[1]::varchar(255)                       AS doseview,
                       REPLACE((XPATH('/body/DoseViewRep/text()', t.x2))[1]::varchar(1000), ' ', '') AS doseviewrep,
                       (XPATH('/body/OtherID/text()', t.x2))[1]::text::int                           AS otherid,
                       (XPATH('/body/IsVital/text()', t.x2))[1]::text::boolean                       AS isvital,
                       (XPATH('/body/Active/text()', t.x2))[1]::text::boolean                        AS active,
                       (XPATH('/body/Rate/text()', t.x2))[1]::text::int                              AS rate,
                       (XPATH('/body/ExportCode/text()', t.x2))[1]::text::int                        AS exportcode,
                       (XPATH('/body/PackQuantity/text()', t.x2))[1]::text::int                      AS packquantity,
                       (XPATH('/body/FederalID/text()', t.x2))[1]::text::int                         AS federalid,
                       (XPATH('/body/MedicamentProducer/text()', t.x2))[1]::text::int                AS medicamentproducer,
                       (XPATH('/body/CTrn/text()', t.x2))[1]::text::uuid                             AS ctrn,
                       (XPATH('/body/MedicationForms/text()', t.x2))[1]::text::int                   AS medicationforms,
                       (XPATH('/body/ProductInfo/text()', t.x2))[1]::text                            AS productinfo,
                       (XPATH('/body/Dlo/text()', t.x2))[1]::text::boolean                           AS dlo,
                       (XPATH('/body/RoundToWriteOff/text()', t.x2))[1]::text::boolean               AS roundtowriteoff,
                       (XPATH('/body/Gtin/text()', t.x2))[1]::varchar(4)                             AS gtin,
                       (XPATH('/body/EAN/text()', t.x2))[1]::varchar(255)                            AS ean,
                       (XPATH('/body/MedicamentsOfRoszdravnadzor/text()', t.x2))[1]::text::uuid      AS medicamentsofroszdravnadzor,
                       (XPATH('/body/RegCode/text()', t.x2))[1]::varchar(50)                         AS regcode,
                       (XPATH('/body/FedCode/text()', t.x2))[1]::varchar(50)                         AS fedcode,
                       (XPATH('/body/EhrRus2m69i03gtd/text()', t.x2))[1]::text::int                  AS ehrrus2m69i03gtd,
                       (XPATH('/body/EhrRus2m69i03gtdVer/text()', t.x2))[1]::varchar(8)              AS ehrrus2m69i03gtdver,
                       (XPATH('/body/EhrRus9a9g09e8kl/text()', t.x2))[1]::varchar(52)                AS ehrrus9a9g09e8kl,
                       (XPATH('/body/EhrRus9a9g09e8klVer/text()', t.x2))[1]::varchar(8)              AS ehrrus9a9g09e8klver,
                       (XPATH('/body/EhrRusClp14fvirj/text()', t.x2))[1]::text::int                  AS ehrrusclp14fvirj,
                       (XPATH('/body/EhrRusClp14fvirjVer/text()', t.x2))[1]::varchar(8)              AS ehrrusclp14fvirjver,
                       (XPATH('/body/EhrRusH17F8UTS3N/text()', t.x2))[1]::text::int                  AS ehrrush17f8uts3n,
                       (XPATH('/body/EhrRusH17F8UTS3NVer/text()', t.x2))[1]::varchar(8)              AS ehrrush17f8uts3nver,
                       (XPATH('/body/EhrRusDsg207/text()', t.x2))[1]::text::int                      AS ehrrusdsg207,
                       (XPATH('/body/EhrRusDsg207Ver/text()', t.x2))[1]::varchar(8)                  AS ehrrusdsg207ver
                FROM (SELECT UNNEST(XPATH('/BILL_OF_GOODS/header', x)) AS x1,
                             UNNEST(XPATH('/BILL_OF_GOODS/body', x))   AS x2
                      FROM UNNEST(XPATH('/BILL_OF_GOODS', p_xml)) x
                     ) t;
            
            IF (v_operationtype = c_delete) THEN
                
                UPDATE pharm.t_bill_of_goods bf
                SET active = FALSE
                WHERE EXISTS (SELECT 1 FROM temp_bill_of_goods nbg WHERE nbg.id = bf.billofgoodsid);
            
            ELSE
                
                INSERT INTO pharm.s_dlo_doseview (value)
                SELECT bgl.doseview
                FROM temp_bill_of_goods bgl
                WHERE bgl.doseview IS NOT NULL
                  AND NOT EXISTS (SELECT 1
                                  FROM temp_pharmdv dv
                                  WHERE dv.value = bgl.doseviewrep
                                 )
                GROUP BY bgl.doseview;
                
                TRUNCATE TABLE temp_pharmdv;
                
                INSERT INTO temp_pharmdv (value, id)
                SELECT REPLACE(dv.value, ' ', '') AS value,
                       MAX(dv.id)                 AS id
                FROM pharm.s_dlo_doseview dv
                GROUP BY REPLACE(dv.value, ' ', '');
                
                UPDATE pharm.t_bill_of_goods bf
                SET doseview                    = dv.id,
                    otherid                     = bgl.otherid,
                    isvital                     = COALESCE(bgl.isvital, '0'),
                    active                      = bgl.active,
                    rate                        = COALESCE(bgl.rate, 1),
                    exportcode                  = bgl.exportcode,
                    packquantity                = bgl.packquantity,
                    federalid                   = bgl.federalid,
                    medicamentproducer          = bgl.medicamentproducer,
                    ctrn                        = bgl.ctrn,
                    medicationforms             = bgl.medicationforms,
                    productinfo                 = bgl.productinfo,
                    dlo                         = COALESCE(bgl.dlo, '0'),
                    roundtowriteoff             = COALESCE(bgl.roundtowriteoff, '0'),
                    gtin                        = bgl.gtin,
                    ean                         = bgl.ean,
                    medicamentsofroszdravnadzor = bgl.medicamentsofroszdravnadzor,
                    regcode                     = bgl.regcode,
                    fedcode                     = bgl.fedcode,
                    ehrrus2m69i03gtd            = ehr.ehrrusid,
                    ehrrus9a9g09e8kl            = ehr1.ehrrusid,
                    ehrrusclp14fvirj            = ehr2.ehrrusid,
                    ehrrush17f8uts3n            = ehr3.ehrrusid,
                    ehrrusdsg207                = ehr4.ehrrusid
                FROM temp_bill_of_goods bgl
                LEFT JOIN temp_pharmdv  dv ON dv.value = bgl.doseviewrep
                LEFT JOIN LATERAL (
                              SELECT er.ehrrusid
                              FROM ehr.t_ehr_rus_2m69i03gtd     er
                              LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                              WHERE er.idehr = bgl.ehrrus2m69i03gtd
                                AND rrb.currversion = bgl.ehrrus2m69i03gtdver
                              LIMIT 1
                              )         ehr ON TRUE
                LEFT JOIN LATERAL (
                              SELECT er.ehrrusid
                              FROM ehr.t_ehr_rus_9a9g09e8kl     er
                              LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                              WHERE er.codeehr = bgl.ehrrus9a9g09e8kl
                                AND rrb.currversion = bgl.ehrrus9a9g09e8klver
                              LIMIT 1
                              )         ehr1 ON TRUE
                LEFT JOIN LATERAL (
                              SELECT er.ehrrusid
                              FROM ehr.t_ehr_rus_clp14fvirj     er
                              LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                              WHERE er.idehr = bgl.ehrrusclp14fvirj
                                AND rrb.currversion = bgl.ehrrusclp14fvirjver
                              LIMIT 1
                              )         ehr2 ON TRUE
                LEFT JOIN LATERAL (
                              SELECT er.ehrrusid
                              FROM ehr.t_ehr_rus_h17f8uts3n     er
                              LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                              WHERE er.idehr = bgl.ehrrush17f8uts3n
                                AND rrb.currversion = bgl.ehrrush17f8uts3nver
                              LIMIT 1
                              )         ehr3 ON TRUE
                LEFT JOIN LATERAL (
                              SELECT er.ehrrusid
                              FROM ehr.t_ehr_rus_dsg207         er
                              LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                              WHERE er.idehr = bgl.ehrrusdsg207
                                AND rrb.currversion = bgl.ehrrusdsg207ver
                              LIMIT 1
                              )         ehr4 ON TRUE
                WHERE v_operationtype = c_update
                  AND bgl.id = bf.billofgoodsid;
                
                INSERT INTO
                    pharm.t_bill_of_goods
                (billofgoodsid,
                 ctrn,
                 active,
                 medicamentproducer,
                 otherid,
                 medicationforms,
                 doseview,
                 rate,
                 isvital,
                 packquantity,
                 exportcode,
                 federalid,
                 productinfo,
                 dlo,
                 roundtowriteoff,
                 gtin,
                 ean,
                 medicamentsofroszdravnadzor,
                 regcode,
                 fedcode,
                 ehrrus2m69i03gtd,
                 ehrrus9a9g09e8kl,
                 ehrrusclp14fvirj,
                 ehrrush17f8uts3n,
                 ehrrusdsg207)
                SELECT bgl.id,
                       bgl.ctrn,
                       bgl.active,
                       bgl.medicamentproducer,
                       bgl.otherid,
                       bgl.medicationforms,
                       dv.id,
                       COALESCE(bgl.rate, 1),
                       COALESCE(bgl.isvital, 0),
                       bgl.packquantity,
                       bgl.exportcode,
                       bgl.federalid,
                       bgl.productinfo,
                       COALESCE(bgl.dlo, 0),
                       COALESCE(bgl.roundtowriteoff, 0),
                       bgl.gtin,
                       bgl.ean,
                       medicamentsofroszdravnadzor,
                       bgl.regcode,
                       bgl.fedcode,
                       ehr.ehrrusid,
                       ehr1.ehrrusid,
                       ehr2.ehrrusid,
                       ehr3.ehrrusid,
                       ehr4.ehrrusid
                FROM temp_bill_of_goods bgl
                LEFT JOIN temp_pharmdv  dv ON dv.value = bgl.doseviewrep
                LEFT JOIN LATERAL
                              (
                              SELECT er.ehrrusid
                              FROM ehr.t_ehr_rus_2m69i03gtd     er
                              LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                              WHERE er.idehr = bgl.ehrrus2m69i03gtd
                                AND rrb.currversion = bgl.ehrrus2m69i03gtdver
                              LIMIT 1
                              )         ehr ON TRUE
                LEFT JOIN LATERAL
                              (
                              SELECT er.ehrrusid
                              FROM ehr.t_ehr_rus_9a9g09e8kl     er
                              LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                              WHERE er.codeehr = bgl.ehrrus9a9g09e8kl
                                AND rrb.currversion = bgl.ehrrus9a9g09e8klver
                              LIMIT 1
                              )         ehr1 ON TRUE
                LEFT JOIN LATERAL
                              (
                              SELECT er.ehrrusid
                              FROM ehr.t_ehr_rus_clp14fvirj     er
                              LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                              WHERE er.idehr = bgl.ehrrusclp14fvirj
                                AND rrb.currversion = bgl.ehrrusclp14fvirjver
                              LIMIT 1
                              )         ehr2 ON TRUE
                LEFT JOIN LATERAL
                              (
                              SELECT er.ehrrusid
                              FROM ehr.t_ehr_rus_h17f8uts3n     er
                              LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                              WHERE er.idehr = bgl.ehrrush17f8uts3n
                                AND rrb.currversion = bgl.ehrrush17f8uts3nver
                              LIMIT 1
                              )         ehr3 ON TRUE
                LEFT JOIN LATERAL
                              (
                              SELECT er.ehrrusid
                              FROM ehr.t_ehr_rus_dsg207         er
                              LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                              WHERE er.idehr = bgl.ehrrusdsg207
                                AND rrb.currversion = bgl.ehrrusdsg207ver
                              LIMIT 1
                              )         ehr4 ON TRUE
                WHERE v_operationtype = c_insert
                  AND NOT EXISTS
                    (SELECT *
                     FROM pharm.t_bill_of_goods bg
                     WHERE bg.billofgoodsid = bgl.id
                    );
            
            END IF;
        
        
        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
          AND v_operationtype <> c_ftload;
        
        SELECT entityid
        INTO v_entid
        FROM vclib.t_entities
        WHERE entname = 'BILL_OF_GOODS';
        
        IF (EXISTS (SELECT 1 FROM vclib.t_gen_numbers WHERE entityid = v_entid AND numbername = 'PHARM.BILL_OF_GOODS'
                   )) THEN
            
            UPDATE vclib.t_gen_numbers gn
            SET nextnumber = (SELECT MAX(otherid) FROM pharm.t_bill_of_goods)
            FROM vclib.t_gen_numbers gn
            WHERE gn.entityid = v_entid
              AND gn.numbername = 'PHARM.BILL_OF_GOODS';
        
        ELSE
            
            INSERT INTO
                vclib.t_gen_numbers (gennumbersid, entityid, numbername, nextnumber)
            VALUES (gen_random_uuid(),
                    v_entid,
                    'PHARM.BILL_OF_GOODS',
                    COALESCE((SELECT MAX(otherid) FROM pharm.t_bill_of_goods), 1));
        
        END IF;
    
    END IF;
END;
$$