-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника Форм выпуска препарата(TRN) в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS dbo.sp_rds_billofgoods;

DROP PROCEDURE IF EXISTS aps.sp_rds_billofgoods;

CREATE OR REPLACE PROCEDURE aps.sp_rds_billofgoods(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "ID" uuid DEFAULT NULL::uuid,
    "QueueName" CHARACTER VARYING DEFAULT 'RDSSync'::CHARACTER VARYING,
    "dateTime" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL::TIMESTAMP WITHOUT TIME ZONE,
    "User" uuid DEFAULT NULL::uuid,
    "PATH" CHARACTER VARYING DEFAULT NULL::CHARACTER VARYING)
    LANGUAGE plpgsql
AS
$$
BEGIN
    
    DECLARE
        _result          TEXT;
        _contentencoding VARCHAR(255) := 'utf-8';
        _type            VARCHAR(255) := 'BILL_OF_GOODS';
        _operationtype   VARCHAR(255);
        _ownerid         uuid         := "ID";
        -- Operation types
        _insert          VARCHAR(6)   := 'Insert';
        _update          VARCHAR(6)   := 'Update';
        _delete          VARCHAR(6)   := 'Delete';
        _ftload          VARCHAR(6)   := 'FTLoad';
        _sql             TEXT;
        _entid           INT;
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        
        DROP TABLE IF EXISTS temp_dose_view;
        CREATE TEMP TABLE temp_dose_view
        (
            value VARCHAR(1000),
            id    INT
        );
        
        INSERT INTO temp_dose_view(value, id)
        SELECT REPLACE(dv.value, ' ', '') AS value,
               MAX(dv.id)                 AS id
        FROM pharm.s_dlo_doseview dv
        GROUP BY REPLACE(dv.value, ' ', '');
        
        IF "PATH" IS NOT NULL
        THEN
            DROP TABLE IF EXISTS temp_bill_of_goods_dose;
            CREATE TEMP TABLE temp_bill_of_goods_dose
            (
                billofgoodsid               uuid,
                otherid                     INT,
                active                      BIT,
                ctrn                        uuid,
                doseview                    VARCHAR(1000),
                doseviewrep                 VARCHAR(1000),
                rate                        INT,
                packquantity                INT,
                isvital                     BIT,
                federalid                   INT,
                exportcode                  INT,
                ean                         VARCHAR(255),
                productinfo                 VARCHAR(512),
                dlo                         BIT,
                roundtowriteoff             BIT,
                fedcode                     VARCHAR(50),
                regcode                     VARCHAR(50),
                gtin                        VARCHAR(14),
                medicamentsofroszdravnadzor uuid,
                ehrrus2m69i03gtd            INT,
                ehrrus2m69i03gtdver         VARCHAR(8),
                ehrrus9a9g09e8kl            VARCHAR(52),
                ehrrus9a9g09e8klver         VARCHAR(8),
                medicationforms             uuid,
                medicamentproducer          uuid,
                ehrrusclp14fvirj            INT,
                ehrrusclp14fvirjver         VARCHAR(8),
                ehrrush17f8uts3n            INT,
                ehrrush17f8uts3nver         VARCHAR(8),
                ehrrusdsg207                INT,
                ehrrusdsg207ver             VARCHAR(8)
            );
            
            _sql := 'insert into TEMP_BILL_OF_GOODS_DOSE(
							BillOfGoodsID
							,OtherID
							,Active
							,CTrn
							,DoseView
							,DoseViewRep
							,Rate
							,PackQuantity
							,IsVital
							,FederalID
							,ExportCode
							,EAN
							,ProductInfo
							,Dlo
							,RoundToWriteOff
							,FedCode
							,RegCode
							,Gtin
							,MedicamentsOfRoszdravnadzor
							,EhrRus2m69i03gtd
							,EhrRus2m69i03gtdVer
							,EhrRus9a9g09e8kl
							,EhrRus9a9g09e8klVer
							,MedicationForms
							,MedicamentProducer
							,EhrRusClp14fvirj
							,EhrRusClp14fvirjVer
							,EhrRusH17F8UTS3N
							,EhrRusH17F8UTS3NVer
							,EhrRusDsg207
							,EhrRusDsg207Ver)
				 SELECT  BillOfGoodsID
				,OtherID
				,Active
				,CTrn
				,dv.Value
				,REPLACE(dv.Value, '' '', '''')
				,Rate
				,PackQuantity
				,IsVital
				,FederalID
				,ExportCode
				,EAN
				,ProductInfo
				,Dlo
				,RoundToWriteOff
				,FedCode
				,RegCode
				,Gtin
				,MedicamentsOfRoszdravnadzor
				,ehr.IdEHR
				,ehr.CurrVersion
				,ehr1.CodeEHR
				,ehr1.CurrVersion
				,MedicationForms
				,MedicamentProducer
				,ehr2.IdEHR
				,ehr2.CurrVersion
				,ehr3.IdEHR
				,ehr3.CurrVersion
				,ehr4.IdEHR
				,ehr4.CurrVersion
				FROM dblink(''dbname=' || "PATH"
                        || ''','
                || ''' SELECT bgl.BillOfGoodsID
				,bgl.OtherID
				,bgl.Active
				,bgl.CTrn
				,bgl.dv.Value
				,bgl.REPLACE(dv.Value, '' '', '''')
				,bgl.Rate
				,bgl.PackQuantity
				,bgl.IsVital
				,bgl.FederalID
				,bgl.ExportCode
				,bgl.EAN
				,bgl.ProductInfo
				,bgl.Dlo
				,bgl.RoundToWriteOff
				,bgl.FedCode
				,bgl.RegCode
				,bgl.Gtin
				,bgl.MedicamentsOfRoszdravnadzor
				,ehr.IdEHR
				,ehr.CurrVersion
				,ehr1.CodeEHR
				,ehr1.CurrVersion
				,bgl.MedicationForms
				,bgl.MedicamentProducer
				,ehr2.IdEHR
				,ehr2.CurrVersion
				,ehr3.IdEHR
				,ehr3.CurrVersion
				,ehr4.IdEHR
				,ehr4.CurrVersion
					FROM PHARM.T_BILL_OF_GOODS bgl
					left join PHARM.S_DLO_DoseView dv on dv.id = bgl.DoseView
					left join lateral
					(
						SELECT er.IdEHR, rrb.CurrVersion
						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.IdEHR, rrb.CurrVersion
						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.IdEHR, rrb.CurrVersion
						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.IdEHR, rrb.CurrVersion
						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.IdEHR, rrb.CurrVersion
						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
					'')
					AS bg(
				BillOfGoodsID					uuid
				,OtherID						INT
				,Active							BIT
				,CTrn							uuid
				,DoseView						varchar(1000)
				,DoseViewRep					varchar(1000)
				,Rate							INT
				,PackQuantity					INT
				,IsVital						BIT
				,FederalID						INT
				,ExportCode						INT
				,EAN							VARCHAR(255)
				,ProductInfo					VARCHAR(512)
				,Dlo							BIT
				,RoundToWriteOff				BIT
				,FedCode						VARCHAR(50)
				,RegCode						VARCHAR(50)
				,Gtin							VARCHAR(14)
				,MedicamentsOfRoszdravnadzor	uuid
				,EhrRus2m69i03gtd				INT
				,EhrRus2m69i03gtdVer			VARCHAR(8)
				,EhrRus9a9g09e8kl				VARCHAR(52)
				,EhrRus9a9g09e8klVer			VARCHAR(8)
				,MedicationForms				uuid
				,MedicamentProducer				uuid
				,EhrRusClp14fvirj				INT
				,EhrRusClp14fvirjVer			VARCHAR(8)
				,EhrRusH17F8UTS3N				INT
				,EhrRusH17F8UTS3NVer			VARCHAR(8)
				,EhrRusDsg207					INT
				,EhrRusDsg207Ver				VARCHAR(8)) ';
            
            EXECUTE _sql;
            
            -- Первичная загрузка
            -- Загружаем новые DoseView
            INSERT INTO pharm.s_dlo_doseview(value)
            SELECT MAX(bgl.doseview)
            FROM temp_bill_of_goods_dose bgl
            WHERE bgl.doseview IS NOT NULL
              AND NOT EXISTS(SELECT * FROM temp_dose_view dv WHERE dv.value = bgl.doseviewrep)
            GROUP BY bgl.doseviewrep;
            
            --пересоздаем #PharmDV
            TRUNCATE TABLE temp_dose_view;
            INSERT INTO temp_dose_view(value, id)
            SELECT REPLACE(dv.value, ' ', '') AS value,
                   MAX(dv.id)                 AS id
            FROM pharm.s_dlo_doseview dv
            GROUP BY REPLACE(dv.value, ' ', '');
            
            -- TRN не из справочника RDS переводим в неактивные
            UPDATE pharm.t_bill_of_goods bf
            SET active = '0'
            WHERE NOT EXISTS(SELECT* FROM temp_bill_of_goods_dose bgl WHERE bgl.billofgoodsid = bf.billofgoodsid);
            
            -- Обновляем имеющиеся TRN
            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_dose   bgl
            LEFT JOIN
                      pharm.s_dlo_doseview dv
                          ON dv.value = bgl.doseview
            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 prep.ehrrusid
                          FROM ehr.t_ehr_rus_h17f8uts3n prep
                          JOIN ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = prep.ehrrusrefbooks
                          WHERE prep.idehr = bgl.ehrrush17f8uts3n
                            AND rrb.currversion = bgl.ehrrush17f8uts3nver
                          LIMIT 1
                          )                ehr3 ON TRUE
            LEFT JOIN LATERAL (
                          SELECT prep.ehrrusid
                          FROM ehr.t_ehr_rus_dsg207    prep
                          JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = prep.ehrrusrefbooks
                          WHERE prep.idehr = bgl.ehrrusdsg207
                            AND rrb.currversion = bgl.ehrrusdsg207ver
                          LIMIT 1
                          )                ehr4 ON TRUE
            WHERE bgl.billofgoodsid = bf.billofgoodsid;
            
            -- Загружаем новые TRN
            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.billofgoodsid,
                   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,
                   regcode,
                   fedcode,
                   ehr.ehrrusid,
                   ehr1.ehrrusid,
                   ehr2.ehrrusid,
                   ehr3.ehrrusid,
                   ehr4.ehrrusid
            FROM temp_bill_of_goods_dose   bgl
            LEFT JOIN pharm.s_dlo_doseview dv ON dv.value = bgl.doseview
            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 prep.ehrrusid
                          FROM ehr.t_ehr_rus_h17f8uts3n prep
                          JOIN ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = prep.ehrrusrefbooks
                          WHERE prep.idehr = bgl.ehrrush17f8uts3n
                            AND rrb.currversion = bgl.ehrrush17f8uts3nver
                          LIMIT 1
                          )                ehr3 ON TRUE
            LEFT JOIN LATERAL (
                          SELECT prep.ehrrusid
                          FROM ehr.t_ehr_rus_dsg207    prep
                          JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = prep.ehrrusrefbooks
                          WHERE prep.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.billofgoodsid);
        ELSE
            IF "Xml" IS NULL
            THEN
                --выясняем тип операции
                IF "ID" IS NULL
                THEN
                    _operationtype := _ftload;
                ELSIF "IsNew" = 1
                THEN
                    _operationtype := _insert;
                ELSIF "IsNew" = 0 AND EXISTS(SELECT* FROM pharm.t_bill_of_goods bg WHERE bg.billofgoodsid = "ID")
                THEN
                    _operationtype := _update;
                ELSIF "IsNew" = 0 AND NOT EXISTS(SELECT* FROM pharm.t_bill_of_goods bg WHERE bg.billofgoodsid = "ID")
                THEN
                    _operationtype := _delete;
                    _result := XMLELEMENT(NAME "BILL_OF_GOODS",
                                          XMLELEMENT(NAME header, XMLFOREST(_operationtype AS "TYPE", "ID" AS "ID")));
                END IF;
                
                -- Обработка операции
                IF _operationtype = _ftload
                THEN
                    
                    -- Первичная загрузка
                    SELECT '<BILL_OF_GOODS><header><TYPE>' || _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 _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 _operationtype <> _delete
                THEN
                    -- собираем BILL_OF_GOODS
                    SELECT XMLELEMENT(
                               NAME "BILL_OF_GOODS",
                               XMLELEMENT(
                                   NAME header,
                                   XMLFOREST(
                                       _operationtype AS "TYPE",
                                       "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",
                                       regcode AS "RegCode",
                                       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 _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 = "ID";
                
                END IF;
                
                -- Отправка
                INSERT INTO
                    aps.t_rabbit_send_object(rabbitsendobjectid,
                                             queuename,
                                             content,
                                             messageid,
                                             headers,
                                             contentencoding,
                                             type,
                                             userid,
                                             state,
                                             createdatetime,
                                             ownerid)
                SELECT gen_random_uuid(),
                       "QueueName",
                       converttexttobinaryzip(_result, ''),
                       NULL,
                       NULL,
                       _contentencoding,
                       _type,
                       "User",
                       0,
                       "dateTime",
                       _ownerid;
            ELSE
                IF ("Xml" IS NOT NULL)
                THEN
                    -- Получаем тип операции
                    _operationtype :=
                        (XPATH('/BILL_OF_GOODS/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::TEXT::VARCHAR(36);
                    
                    
                    -- Обработка операции
                    IF _operationtype = _ftload
                    THEN
                        -- Generate table Tmp_BILL_OF_GOODS_LIST
                        DROP TABLE IF EXISTS tmp_bill_of_goods_list;
                        CREATE TEMP TABLE tmp_bill_of_goods_list
                        AS
                            SELECT (XPATH('/BILL_OF_GOODS/ID[1]/text()', t.x))[1]::TEXT::uuid                          AS id,
                                   (XPATH('/BILL_OF_GOODS/DoseView[1]/text()', t.x))[1]::VARCHAR(1000)                 AS doseview,
                                   REPLACE((XPATH('/BILL_OF_GOODS/DoseViewRep[1]/text()', t.x))[1]::VARCHAR(1000), ' ',
                                           '')                                                                         AS doseviewrep,
                                   (XPATH('/BILL_OF_GOODS/OtherID[1]/text()', t.x))[1]::TEXT::INT                      AS otherid,
                                   (XPATH('/BILL_OF_GOODS/IsVital[1]/text()', t.x))[1]::TEXT::BOOLEAN                  AS isvital,
                                   (XPATH('/BILL_OF_GOODS/Active[1]/text()', t.x))[1]::TEXT::BOOLEAN                   AS active,
                                   (XPATH('/BILL_OF_GOODS/Rate[1]/text()', t.x))[1]::TEXT::INT                         AS rate,
                                   (XPATH('/BILL_OF_GOODS/ExportCode[1]/text()', t.x))[1]::TEXT::INT                   AS exportcode,
                                   (XPATH('/BILL_OF_GOODS/PackQuantity[1]/text()', t.x))[1]::TEXT::INT                 AS packquantity,
                                   (XPATH('/BILL_OF_GOODS/Name[1]/text()', t.x))[1]::VARCHAR(1000)                     AS name,
                                   (XPATH('/BILL_OF_GOODS/FederalID[1]/text()', t.x))[1]::TEXT::INT                    AS federalid,
                                   (XPATH('/BILL_OF_GOODS/MedicamentProducer[1]/text()', t.x))[1]::TEXT::uuid          AS medicamentproducer,
                                   (XPATH('/BILL_OF_GOODS/CTrn[1]/text()', t.x))[1]::TEXT::uuid                        AS ctrn,
                                   (XPATH('/BILL_OF_GOODS/MedicationForms[1]/text()', t.x))[1]::TEXT::uuid             AS medicationforms,
                                   (XPATH('/BILL_OF_GOODS/ProductInfo[1]/text()', t.x))[1]::TEXT                       AS productinfo,
                                   (XPATH('/BILL_OF_GOODS/Dlo[1]/text()', t.x))[1]::TEXT::BOOLEAN                      AS dlo,
                                   (XPATH('/BILL_OF_GOODS/RoundToWriteOff[1]/text()', t.x))[1]::TEXT::BOOLEAN          AS roundtowriteoff,
                                   (XPATH('/BILL_OF_GOODS/Gtin[1]/text()', t.x))[1]::VARCHAR(4)                        AS gtin,
                                   (XPATH('/BILL_OF_GOODS/EAN[1]/text()', t.x))[1]::VARCHAR(255)                       AS ean,
                                   (XPATH('/BILL_OF_GOODS/MedicamentsOfRoszdravnadzor[1]/text()', t.x))[1]::TEXT::uuid AS medicamentsofroszdravnadzor,
                                   (XPATH('/BILL_OF_GOODS/RegCode[1]/text()', t.x))[1]::VARCHAR(50)                    AS regcode,
                                   (XPATH('/BILL_OF_GOODS/FedCode[1]/text()', t.x))[1]::VARCHAR(50)                    AS fedcode,
                                   (XPATH('/BILL_OF_GOODS/EhrRus2m69i03gtd[1]/text()', t.x))[1]::TEXT::INT             AS ehrrus2m69i03gtd,
                                   (XPATH('/BILL_OF_GOODS/EhrRus2m69i03gtdVer[1]/text()', t.x))[1]::VARCHAR(8)         AS ehrrus2m69i03gtdver,
                                   (XPATH('/BILL_OF_GOODS/EhrRus9a9g09e8kl[1]/text()', t.x))[1]::VARCHAR(52)           AS ehrrus9a9g09e8kl,
                                   (XPATH('/BILL_OF_GOODS/EhrRus9a9g09e8klVer[1]/text()', t.x))[1]::VARCHAR(8)         AS ehrrus9a9g09e8klver,
                                   (XPATH('/BILL_OF_GOODS/EhrRusClp14fvirj[1]/text()', t.x))[1]::TEXT::INT             AS ehrrusclp14fvirj,
                                   (XPATH('/BILL_OF_GOODS/EhrRusClp14fvirjVer[1]/text()', t.x))[1]::VARCHAR(8)         AS ehrrusclp14fvirjver,
                                   (XPATH('/BILL_OF_GOODS/EhrRusH17F8UTS3N[1]/text()', t.x))[1]::TEXT::INT             AS ehrrush17f8uts3n,
                                   (XPATH('/BILL_OF_GOODS/EhrRusH17F8UTS3NVer[1]/text()', t.x))[1]::VARCHAR(8)         AS ehrrush17f8uts3nver,
                                   (XPATH('/BILL_OF_GOODS/EhrRusDsg207[1]/text()', t.x))[1]::TEXT::INT                 AS ehrrusdsg207,
                                   (XPATH('/BILL_OF_GOODS/EhrRusDsg207Ver[1]/text()', t.x))[1]::VARCHAR(8)             AS ehrrusdsg207ver
                            FROM (SELECT UNNEST(XPATH('/BILL_OF_GOODS/body/BILL_OF_GOODS_LIST/BILL_OF_GOODS',
                                                      "Xml"::xml)) AS x
                                 ) t;
                        
                        -- Первичная загрузка
                        -- Загружаем новые DoseView
                        INSERT INTO pharm.s_dlo_doseview(value)
                        SELECT MAX(bgl.doseview)
                        FROM tmp_bill_of_goods_list bgl
                        WHERE bgl.doseview IS NOT NULL
                          AND NOT EXISTS
                            (SELECT*
                             FROM temp_dose_view dv
                             WHERE dv.value = bgl.doseviewrep
                            )
                        GROUP BY bgl.doseviewrep;
                        
                        TRUNCATE TABLE temp_dose_view;
                        INSERT INTO temp_dose_view(value, id)
                        SELECT REPLACE(dv.value, ' ', '') AS value,
                               MAX(dv.id)                 AS id
                        FROM pharm.s_dlo_doseview dv
                        GROUP BY REPLACE(dv.value, ' ', '');
                        
                        ANALYZE tmp_bill_of_goods_list;
                        ANALYZE temp_dose_view;
                        
                        -- TRN не из справочника RDS переводим в неактивные
                        UPDATE pharm.t_bill_of_goods bf
                        SET active = '0'
                        WHERE NOT EXISTS
                                  (SELECT*
                                   FROM tmp_bill_of_goods_list bgl
                                   WHERE bgl.id = bf.billofgoodsid
                                  );
                        
                        -- Обновляем имеющиеся TRN
                        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 tmp_bill_of_goods_list bgl
                        LEFT JOIN temp_dose_view    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
                                      WHERE er.idehr = bgl.ehrrusdsg207
                                        AND rrb.currversion = bgl.ehrrusdsg207ver
                                      LIMIT 1
                                      )             ehr4 ON TRUE
                        WHERE bgl.id = bf.billofgoodsid;
                        
                        -- Загружаем новые TRN
                        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,
                               regcode,
                               fedcode,
                               ehr.ehrrusid,
                               ehr1.ehrrusid,
                               ehr2.ehrrusid,
                               ehr3.ehrrusid,
                               ehr4.ehrrusid
                        FROM tmp_bill_of_goods_list bgl
                        LEFT JOIN temp_dose_view    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
                                      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
                        -- Generate table Tmp_BILL_OF_GOODS
                        DROP TABLE IF EXISTS tmp_bill_of_goods;
                        CREATE TEMP TABLE tmp_bill_of_goods
                        AS
                            SELECT (XPATH('/header/ID/text()', t.x1))::TEXT::uuid                        AS id,
                                   (XPATH('/body/DoseView/text()', t.x2))::VARCHAR(255)                  AS doseview,
                                   REPLACE((XPATH('/body/DoseViewRep/text()', t.x2))::VARCHAR(1000), ' ',
                                           '')                                                           AS doseviewrep,
                                   (XPATH('/body/OtherID/text()', t.x2))::TEXT::INT                      AS otherid,
                                   (XPATH('/body/IsVital/text()', t.x2))::TEXT::BOOLEAN                  AS isvital,
                                   (XPATH('/body/Active/text()', t.x2))::TEXT::BOOLEAN                   AS active,
                                   (XPATH('/body/Rate/text()', t.x2))::TEXT::INT                         AS rate,
                                   (XPATH('/body/ExportCode/text()', t.x2))::TEXT::INT                   AS exportcode,
                                   (XPATH('/body/PackQuantity/text()', t.x2))::TEXT::INT                 AS packquantity,
                                   (XPATH('/body/FederalID/text()', t.x2))::TEXT::INT                    AS federalid,
                                   (XPATH('/body/MedicamentProducer/text()', t.x2))::TEXT::INT           AS medicamentproducer,
                                   (XPATH('/body/CTrn/text()', t.x2))::TEXT::uuid                        AS ctrn,
                                   (XPATH('/body/MedicationForms/text()', t.x2))::TEXT::INT              AS medicationforms,
                                   (XPATH('/body/ProductInfo/text()', t.x2))::TEXT                       AS productinfo,
                                   (XPATH('/body/Dlo/text()', t.x2))::TEXT::BOOLEAN                      AS dlo,
                                   (XPATH('/body/RoundToWriteOff/text()', t.x2))::TEXT::BOOLEAN          AS roundtowriteoff,
                                   (XPATH('/body/Gtin/text()', t.x2))::VARCHAR(4)                        AS gtin,
                                   (XPATH('/body/EAN/text()', t.x2))::VARCHAR(255)                       AS ean,
                                   (XPATH('/body/MedicamentsOfRoszdravnadzor/text()', t.x2))::TEXT::uuid AS medicamentsofroszdravnadzor,
                                   (XPATH('/body/RegCode/text()', t.x2))::VARCHAR(50)                    AS regcode,
                                   (XPATH('/body/FedCode/text()', t.x2))::VARCHAR(50)                    AS fedcode,
                                   (XPATH('/body/EhrRus2m69i03gtd/text()', t.x2))::TEXT::INT             AS ehrrus2m69i03gtd,
                                   (XPATH('/body/EhrRus2m69i03gtdVer/text()', t.x2))::VARCHAR(8)         AS ehrrus2m69i03gtdver,
                                   (XPATH('/body/EhrRus9a9g09e8kl/text()', t.x2))::TEXT::INT             AS ehrrus9a9g09e8kl,
                                   (XPATH('/body/EhrRus9a9g09e8klVer/text()', t.x2))::VARCHAR(8)         AS ehrrus9a9g09e8klver,
                                   (XPATH('/body/EhrRusClp14fvirj/text()', t.x2))::TEXT::INT             AS ehrrusclp14fvirj,
                                   (XPATH('/body/EhrRusClp14fvirjVer/text()', t.x2))::VARCHAR(8)         AS ehrrusclp14fvirjver,
                                   (XPATH('/body/EhrRusH17F8UTS3N/text()', t.x2))::TEXT::INT             AS ehrrush17f8uts3n,
                                   (XPATH('/body/EhrRusH17F8UTS3NVer/text()', t.x2))::VARCHAR(8)         AS ehrrush17f8uts3nver,
                                   (XPATH('/body/EhrRusDsg207/text()', t.x2))::TEXT::INT                 AS ehrrusdsg207,
                                   (XPATH('/body/EhrRusDsg207Ver/text()', t.x2))::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', "Xml"::xml)) x
                                 ) t;
                        
                        IF _operationtype = _delete
                        THEN
                            -- Удаление
                            UPDATE pharm.t_bill_of_goods bf
                            SET active = '0'
                            FROM tmp_bill_of_goods_list nbg
                            WHERE nbg.id = bf.billofgoodsid;
                        ELSE
                            -- Загружаем новые DoseView
                            INSERT INTO pharm.s_dlo_doseview (value)
                            SELECT MAX(bgl.doseview)
                            FROM tmp_bill_of_goods_list bgl
                            WHERE bgl.doseview IS NOT NULL
                              AND NOT EXISTS
                                (SELECT *
                                 FROM temp_dose_view dv
                                 WHERE dv.value = bgl.doseviewrep
                                )
                            GROUP BY bgl.doseviewrep;
                            
                            TRUNCATE TABLE temp_dose_view;
                            INSERT INTO temp_dose_view(value, id)
                            SELECT REPLACE(dv.value, ' ', '') AS value,
                                   MAX(dv.id)                 AS id
                            FROM pharm.s_dlo_doseview dv
                            GROUP BY REPLACE(dv.value, ' ', '');
                            
                            
                            -- Обновляем TRN
                            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'),
                                dlo                         = 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 tmp_bill_of_goods_list bgl
                            LEFT JOIN temp_dose_view    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
                                          WHERE er.idehr = bgl.ehrrusdsg207
                                            AND rrb.currversion = bgl.ehrrusdsg207ver
                                          LIMIT 1
                                          )             ehr4 ON TRUE
                            WHERE _operationtype = _update;
                            
                            -- Загружаем новый TRN
                            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,
                                   regcode,
                                   fedcode,
                                   ehr.ehrrusid,
                                   ehr1.ehrrusid,
                                   ehr2.ehrrusid,
                                   ehr3.ehrrusid,
                                   ehr4.ehrrusid
                            FROM tmp_bill_of_goods_list bgl
                            LEFT JOIN temp_dose_view    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
                                          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)
                              AND _operationtype = _insert;
                        END IF;
                    END IF;
                    
                    _entid := (SELECT entityid FROM vclib.t_entities WHERE entname = 'BILL_OF_GOODS' LIMIT 1);
                    IF EXISTS
                        (SELECT*
                         FROM vclib.t_gen_numbers
                         WHERE entityid = _entid
                           AND numbername = 'PHARM.BILL_OF_GOODS'
                        )
                    THEN
                        ---- генерируем след. номер
                        --perform public.sp_GenerateNumber(1916, 'PHARM.BILL_OF_GOODS');
                        
                        UPDATE vclib.t_gen_numbers
                        SET nextnumber = (SELECT MAX(otherid) FROM pharm.t_bill_of_goods) --+ 1
                        WHERE entityid = _entid
                          AND numbername = 'PHARM.BILL_OF_GOODS';
                    ELSE
                        INSERT INTO
                            vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
                        VALUES (gen_random_uuid(),
                                _entid,
                                'PHARM.BILL_OF_GOODS',
                                COALESCE((SELECT MAX(otherid) FROM pharm.t_bill_of_goods), 1));
                    END IF;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;