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

CREATE OR REPLACE PROCEDURE APS.sp_rds_billofgoods(
    "Xml" xml DEFAULT NULL::xml
, "IsNew" BOOLEAN DEFAULT '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,
                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
							,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
				,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.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
				,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'),
                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,
             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'),
                bgl.Gtin,
                bgl.EAN,
                bgl.MedicamentsOfRoszdravnadzor,
                RegCode,
                FedCode,
                ehr.EhrRusID,
                ehr1.EhrRusID,
                ehr2.EhrRusID,
                ehr3.EhrRusID,
                ehr4.EhrRusID,
                bgl.Gtin,
                bgl.EAN,
                bgl.MedicamentsOfRoszdravnadzor,
                bgl.RegCode,
                bgl.FedCode,
                ehr.EhrRusID,
                ehr1.EhrRusID,
                ehr2.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
                        XMLELEMENT
                        (
                            NAME "BILL_OF_GOODS",
                            XMLFOREST(
                                XMLELEMENT(
                                    NAME "header",
                                    XMLELEMENT(NAME "TYPE", _FTLOAD)
                                ),
                                XMLELEMENT(
                                    NAME "body",
                                    (SELECT
                                         XMLELEMENT(
                                             NAME "BILL_OF_GOODS_LIST",
                                             XMLAGG(
                                                 XMLELEMENT
                                                 (
                                                     NAME "BILL_OF_GOODS",
                                                     XMLFOREST
                                                     (
                                                         XMLELEMENT(NAME "ID", bg.BillOfGoodsID),
                                                         XMLELEMENT(NAME "DoseView", dv.Value),
                                                         XMLELEMENT(NAME "OtherID", bg.OtherID),
                                                         XMLELEMENT(NAME "IsVital", bg.IsVital),
                                                         XMLELEMENT(NAME "Active", bg.Active),
                                                         XMLELEMENT(NAME "Rate", bg.Rate),
                                                         XMLELEMENT(NAME "ExportCode", bg.ExportCode),
                                                         XMLELEMENT(NAME "PackQuantity", bg.PackQuantity),
                                                         XMLELEMENT(NAME "FederalID", bg.FederalID),
                                                         XMLELEMENT(NAME "MedicamentProducer", bg.MedicamentProducer),
                                                         XMLELEMENT(NAME "CTrn", bg.CTrn),
                                                         XMLELEMENT(NAME "MedicationForms", bg.MedicationForms),
                                                         XMLELEMENT(NAME "ProductInfo", bg.ProductInfo),
                                                         XMLELEMENT(NAME "Dlo", bg.Dlo),
                                                         XMLELEMENT(NAME "Gtin", bg.Gtin),
                                                         XMLELEMENT(NAME "EAN", bg.EAN),
                                                         XMLELEMENT(NAME "MedicamentsOfRoszdravnadzor",
                                                                    bg.MedicamentsOfRoszdravnadzor),
                                                         XMLELEMENT(NAME "RegCode", bg.RegCode),
                                                         XMLELEMENT(NAME "FedCode", bg.FedCode),
                                                         XMLELEMENT(NAME "EhrRus2m69i03gtd", er.IdEHR),
                                                         XMLELEMENT(NAME "EhrRus2m69i03gtdVer", RRB.CurrVersion),
                                                         XMLELEMENT(NAME "EhrRus9a9g09e8kl", er1.CodeEHR),
                                                         XMLELEMENT(NAME "EhrRus9a9g09e8klVer", RRB1.CurrVersion),
                                                         XMLELEMENT(NAME "EhrRusClp14fvirj", er2.IdEHR),
                                                         XMLELEMENT(NAME "EhrRusClp14fvirjVer", RRB2.CurrVersion),
                                                         XMLELEMENT(NAME "EhrRusH17F8UTS3N", er3.IdEHR),
                                                         XMLELEMENT(NAME "EhrRusH17F8UTS3NVer", RRB3.CurrVersion),
                                                         XMLELEMENT(NAME "EhrRusDsg207", er4.IdEHR),
                                                         XMLELEMENT(NAME "EhrRusDsg207Ver", RRB4.CurrVersion)
                                                     )
                                                 )
                                             )
                                         )
                                     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)
                                )
                            )
                        )::TEXT
                    INTO _Result;

                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(
                                    XMLELEMENT(NAME "DoseView", dv.Value),
                                    XMLELEMENT(NAME "OtherID", bg.OtherID),
                                    XMLELEMENT(NAME "IsVital", bg.IsVital),
                                    XMLELEMENT(NAME "Active", bg.Active),
                                    XMLELEMENT(NAME "Rate", bg.Rate),
                                    XMLELEMENT(NAME "ExportCode", bg.ExportCode),
                                    XMLELEMENT(NAME "PackQuantity", bg.PackQuantity),
                                    XMLELEMENT(NAME "FederalID", bg.FederalID),
                                    XMLELEMENT(NAME "MedicamentProducer", bg.MedicamentProducer),
                                    XMLELEMENT(NAME "CTrn", bg.CTrn),
                                    XMLELEMENT(NAME "MedicationForms", bg.MedicationForms),
                                    XMLELEMENT(NAME "ProductInfo", bg.ProductInfo),
                                    XMLELEMENT(NAME "Dlo", bg.Dlo),
                                    XMLELEMENT(NAME "Gtin", bg.Gtin),
                                    XMLELEMENT(NAME "EAN", bg.EAN),
                                    XMLELEMENT(NAME "MedicamentsOfRoszdravnadzor", bg.MedicamentsOfRoszdravnadzor),
                                    XMLELEMENT(NAME "RegCode", RegCode),
                                    XMLELEMENT(NAME "FedCode", FedCode),
                                    XMLELEMENT(NAME "EhrRus2m69i03gtd", er.IdEHR),
                                    XMLELEMENT(NAME "EhrRus2m69i03gtdVer", RRB.CurrVersion),
                                    XMLELEMENT(NAME "EhrRus9a9g09e8kl", er1.CodeEHR),
                                    XMLELEMENT(NAME "EhrRus9a9g09e8klVer", RRB1.CurrVersion),
                                    XMLELEMENT(NAME "EhrRusClp14fvirj", er2.IdEHR),
                                    XMLELEMENT(NAME "EhrRusClp14fvirjVer", RRB2.CurrVersion),
                                    XMLELEMENT(NAME "EhrRusH17F8UTS3N", er3.IdEHR),
                                    XMLELEMENT(NAME "EhrRusH17F8UTS3NVer", RRB3.CurrVersion),
                                    XMLELEMENT(NAME "EhrRusDsg207", er4.IdEHR),
                                    XMLELEMENT(NAME "EhrRusDsg207Ver", RRB4.CurrVersion)
                                )
                            )
                        )
                    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"))[1]::TEXT::VARCHAR(36);

                    -- 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::INT           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::INT              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/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]::TEXT::INT             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
                    WHERE
                        _OperationType = _FTLOAD;

                    -- Generate table Tmp_BILL_OF_GOODS
                    DROP TABLE IF EXISTS Tmp_BILL_OF_GOODS;
                    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/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
                          WHERE
                              _OperationType <> _FTLOAD) t;

                    -- Обработка операции
                    IF _OperationType = _FTLOAD
                    THEN
                        -- Первичная загрузка
                        -- Загружаем новые 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 не из справочника 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'),
                            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,
                         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')
                            ,
                            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);

                    ELSIF _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')
                            ,
                            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,
                                                  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')
                            ,
                            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;

                    _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'
                         LIMIT 1)
                    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;

                    -- Drop tables
                    DROP TABLE IF EXISTS Tmp_BILL_OF_GOODS;
                    DROP TABLE IF EXISTS Tmp_BILL_OF_GOODS_LIST;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;