DROP PROCEDURE IF EXISTS public.sp_getsetbillofgoods;

CREATE OR REPLACE PROCEDURE public.sp_getsetbillofgoods(
    "Xml" text DEFAULT NULL,
    "IsNew" boolean DEFAULT TRUE,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT NULL,
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml"::xml;
    p_isnew           boolean      := "IsNew";
    p_id              uuid         := "ID";
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := "dateTime";
    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 XMLELEMENT(NAME "BILL_OF_GOODS",
                              XMLELEMENT
                              (
                                  NAME "header",
                                  XMLFOREST(c_ftload AS "TYPE")
                              ),
                              XMLELEMENT
                              (
                                  NAME "body",
                                  XMLELEMENT
                                  (
                                      NAME "BILL_OF_GOODS_LIST",
                                      (SELECT XMLAGG
                                              (
                                                  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.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"
                                                      )
                                                  )
                                              )
                                       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
                                      )
                                  )
                              )
                   )
            INTO v_result;
        
        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;
        
        
        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;
$$