-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника Медикаменты Росздравнадзора в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS dbo.sp_rds_medicamentsofroszdravnadzor;

DROP PROCEDURE IF EXISTS aps.sp_rds_medicamentsofroszdravnadzor;

CREATE PROCEDURE aps.sp_rds_medicamentsofroszdravnadzor(
    "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) := 'MEDICAMENT_OF_ROSZDRAVNADZOR';
        _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;
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        
        IF "PATH" IS NOT NULL
        THEN
            DROP TABLE IF EXISTS tmp_t_medicaments_of_roszdravnadzor;
            CREATE TEMP TABLE tmp_t_medicaments_of_roszdravnadzor
            (
                medicamentsofroszdravnadzorid uuid,
                drugid                        INT,
                tradenmr                      VARCHAR(1000),
                innr                          VARCHAR(1000),
                packnx                        BIGINT,
                dosager                       VARCHAR(255),
                packqn                        INT,
                pack                          VARCHAR(1000),
                drugfmnmrs                    VARCHAR(1000),
                segment                       VARCHAR(255),
                year                          INT,
                month                         INT,
                series                        VARCHAR(255),
                totdrugqn                     DECIMAL,
                mnfprice                      DECIMAL,
                prcprice                      DECIMAL,
                rtlprice                      DECIMAL,
                funds                         DECIMAL,
                vendorid                      VARCHAR(255),
                remark                        VARCHAR(1000),
                srcorg                        VARCHAR(255),
                ean                           VARCHAR(14),
                maxmnfprice                   DECIMAL,
                expitermr                     VARCHAR(500),
                clnm                          VARCHAR(1000),
                mnfnm                         VARCHAR(1000),
                pcknm                         VARCHAR(1000),
                regnr                         VARCHAR(100),
                regdate                       timestamp,
                active                        boolean
            );
            
            _sql := 'INSERT INTO TMP_T_MEDICAMENTS_OF_ROSZDRAVNADZOR
        			SELECT
        				 MedicamentsOfRoszdravnadzorID
        				,DrugID
        				,TradeNmR
        				,InnR
        				,PackNx
        				,DosageR
        				,PackQn
        				,Pack
        				,DrugFmNmRS
        				,Segment
        				,Year
        				,Month
        				,Series
        				,TotDrugQn
        				,MnfPrice
        				,PrcPrice
        				,RtlPrice
        				,Funds
        				,VendorID
        				,Remark
        				,SrcOrg
        				,EAN
        				,MaxMnfPrice
        				,ExpiTermR
        				,ClNm
        				,MnfNm
        				,PckNm
        				,RegNr
        				,RegDate
        				,Active
        			FROM dblink(''dbname=' || "PATH"
                || ''', SELECT
                         mor.MedicamentsOfRoszdravnadzorID
        				,mor.DrugID
        				,mor.TradeNmR
        				,mor.InnR
        				,mor.PackNx
        				,mor.DosageR
        				,mor.PackQn
        				,mor.Pack
        				,mor.DrugFmNmRS
        				,mor.Segment
        				,mor.Year
        				,mor.Month
        				,mor.Series
        				,mor.TotDrugQn
        				,mor.MnfPrice
        				,mor.PrcPrice
        				,mor.RtlPrice
        				,mor.Funds
        				,mor.VendorID
        				,mor.Remark
        				,mor.SrcOrg
        				,mor.EAN
        				,mor.MaxMnfPrice
        				,mor.ExpiTermR
        				,mor.ClNm
        				,mor.MnfNm
        				,mor.PckNm
        				,mor.RegNr
        				,mor.RegDate
        				,mor.Active
                    FROM PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR mor
                    '') AS tt(
                    MedicamentsOfRoszdravnadzorID	uuid
        		                ,DrugID							INT
        		                ,TradeNmR						VARCHAR(1000)
        		                ,InnR							VARCHAR(1000)
        		                ,PackNx							BIGINT
        		                ,DosageR						VARCHAR(255)
        		                ,PackQn							INT
        		                ,Pack							VARCHAR(1000)
        		                ,DrugFmNmRS						VARCHAR(1000)
        		                ,Segment						VARCHAR(255)
        		                ,Year							INT
        		                ,Month							INT
        		                ,Series							VARCHAR(255)
        		                ,TotDrugQn						DECIMAL
        		                ,MnfPrice						DECIMAL
        		                ,PrcPrice						DECIMAL
        		                ,RtlPrice						DECIMAL
        		                ,Funds							DECIMAL
        		                ,VendorID						VARCHAR(255)
        		                ,Remark							VARCHAR(1000)
        		                ,SrcOrg							VARCHAR(255)
        		                ,EAN							VARCHAR(14)
        		                ,MaxMnfPrice					DECIMAL
        		                ,ExpiTermR						VARCHAR(500)
        		                ,ClNm							VARCHAR(1000)
        		                ,MnfNm							VARCHAR(1000)
        		                ,PckNm							VARCHAR(1000)
        		                ,RegNr							VARCHAR(100)
        		                ,RegDate						TIMESTAMP
        		                ,Active							boolean)';
            
            EXECUTE _sql;
            
            UPDATE pharm.t_medicaments_of_roszdravnadzor s
            SET active = '0'
            WHERE NOT EXISTS
                      (SELECT *
                       FROM tmp_t_medicaments_of_roszdravnadzor nmf
                       WHERE nmf.medicamentsofroszdravnadzorid = s.medicamentsofroszdravnadzorid
                      );
            
            -- Обновляем имеющиеся
            UPDATE pharm.t_medicaments_of_roszdravnadzor m
            SET drugid      = nmf.drugid,
                tradenmr    = nmf.tradenmr,
                innr        = nmf.innr,
                packnx      = nmf.packnx,
                dosager     = nmf.dosager,
                packqn      = nmf.packqn,
                pack        = nmf.pack,
                drugfmnmrs  = nmf.drugfmnmrs,
                segment     = nmf.segment,
                year        = nmf.year,
                month       = nmf.month,
                series      = nmf.series,
                totdrugqn   = nmf.totdrugqn,
                mnfprice    = nmf.mnfprice,
                prcprice    = nmf.prcprice,
                rtlprice    = nmf.rtlprice,
                funds       = nmf.funds,
                vendorid    = nmf.vendorid,
                remark      = nmf.remark,
                srcorg      = nmf.srcorg,
                ean         = nmf.ean,
                maxmnfprice = nmf.maxmnfprice,
                expitermr   = nmf.expitermr,
                clnm        = nmf.clnm,
                mnfnm       = nmf.mnfnm,
                pcknm       = nmf.pcknm,
                regnr       = nmf.regnr,
                regdate     = nmf.regdate,
                active      = COALESCE(nmf.active, '1')
            FROM tmp_t_medicaments_of_roszdravnadzor nmf
            WHERE nmf.medicamentsofroszdravnadzorid = m.medicamentsofroszdravnadzorid
              AND m.active = '1';
            
            -- Загружаем новые
            INSERT INTO
                pharm.t_medicaments_of_roszdravnadzor
            (medicamentsofroszdravnadzorid,
             drugid,
             tradenmr,
             innr,
             packnx,
             dosager,
             packqn,
             pack,
             drugfmnmrs,
             segment,
             year,
             month,
             series,
             totdrugqn,
             mnfprice,
             prcprice,
             rtlprice,
             funds,
             vendorid,
             remark,
             srcorg,
             ean,
             maxmnfprice,
             expitermr,
             clnm,
             mnfnm,
             pcknm,
             regnr,
             regdate,
             active)
            SELECT nmf.medicamentsofroszdravnadzorid,
                   nmf.drugid,
                   nmf.tradenmr,
                   nmf.innr,
                   nmf.packnx,
                   nmf.dosager,
                   nmf.packqn,
                   nmf.pack,
                   nmf.drugfmnmrs,
                   nmf.segment,
                   nmf.year,
                   nmf.month,
                   nmf.series,
                   nmf.totdrugqn,
                   nmf.mnfprice,
                   nmf.prcprice,
                   nmf.rtlprice,
                   nmf.funds,
                   nmf.vendorid,
                   nmf.remark,
                   nmf.srcorg,
                   nmf.ean,
                   nmf.maxmnfprice,
                   nmf.expitermr,
                   nmf.clnm,
                   nmf.mnfnm,
                   nmf.pcknm,
                   nmf.regnr,
                   nmf.regdate,
                   COALESCE(nmf.active, '1')
            FROM tmp_t_medicaments_of_roszdravnadzor nmf
            WHERE NOT EXISTS
                      (SELECT *
                       FROM pharm.t_medicaments_of_roszdravnadzor m
                       WHERE m.medicamentsofroszdravnadzorid = nmf.medicamentsofroszdravnadzorid
                      );
        
        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_medicaments_of_roszdravnadzor c
                                             WHERE c.medicamentsofroszdravnadzorid = "ID"
                                            )
                THEN
                    _operationtype := _update;
                ELSIF "IsNew" = 0 AND NOT EXISTS(SELECT*
                                                 FROM pharm.t_medicaments_of_roszdravnadzor c
                                                 WHERE c.medicamentsofroszdravnadzorid = "ID"
                                                )
                THEN
                    _operationtype := _delete;
                    _result := XMLELEMENT(NAME "MEDICAMENT_OF_ROSZDRAVNADZOR",
                                          XMLELEMENT(NAME header, XMLFOREST(_operationtype AS "TYPE", "ID" AS "ID")));
                END IF;
                
                -- Обработка операции
                IF (_operationtype = _ftload)
                THEN
                    -- Первичная загрузка
                    SELECT '<MEDICAMENT_OF_ROSZDRAVNADZOR><header><TYPE>' || _ftload ||
                           '</TYPE></header><body><MEDICAMENTS_OF_ROSZDRAVNADZOR>' ||
                           COALESCE(STRING_AGG(
                                        XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                        ''), '')
                               ||
                           '</MEDICAMENTS_OF_ROSZDRAVNADZOR></body></MEDICAMENT_OF_ROSZDRAVNADZOR>'
                    INTO _result
                    FROM (SELECT XMLELEMENT(
                                     NAME "MEDICAMENT_OF_ROSZDRAVNADZOR",
                                     XMLFOREST(
                                         m.medicamentsofroszdravnadzorid AS "ID",
                                         m.drugid AS "DrugID",
                                         m.tradenmr AS "TradeNmR",
                                         m.innr AS "InnR",
                                         m.packnx AS "PackNx",
                                         m.dosager AS "DosageR",
                                         m.packqn AS "PackQn",
                                         m.pack AS "Pack",
                                         m.drugfmnmrs AS "DrugFmNmRS",
                                         m.segment AS "Segment",
                                         m.year AS "Year",
                                         m.month AS "Month",
                                         m.series AS "Series",
                                         m.totdrugqn AS "TotDrugQn",
                                         m.mnfprice AS "MnfPrice",
                                         m.prcprice AS "PrcPrice",
                                         m.rtlprice AS "RtlPrice",
                                         m.funds AS "Funds",
                                         m.vendorid AS "VendorID",
                                         m.remark AS "Remark",
                                         m.srcorg AS "SrcOrg",
                                         m.ean AS "EAN",
                                         m.expitermr AS "ExpiTermR",
                                         m.clnm AS "ClNm",
                                         m.mnfnm AS "MnfNm",
                                         m.pcknm AS "PckNm",
                                         m.regnr AS "RegNr",
                                         m.maxmnfprice AS "MaxMnfPrice",
                                         TO_CHAR(m.regdate, 'DD.MM.YYYY') AS "RegDate",
                                         m.active AS "Active"
                                     )
                                 ) AS xml_elem
                          FROM pharm.t_medicaments_of_roszdravnadzor m
                          WHERE m.active = TRUE
                         ) tt;
                
                ELSIF (_operationtype <> _delete)
                THEN
                    -- собираем C_MNN
                    SELECT XMLELEMENT
                           (
                               NAME "MEDICAMENT_OF_ROSZDRAVNADZOR",
                               XMLELEMENT
                               (
                                   NAME "header",
                                   XMLFOREST
                                   (
                                       _operationtype AS "TYPE",
                                       "ID" AS "ID"
                                   )
                               ),
                               XMLELEMENT
                               (
                                   NAME "body",
                                   XMLFOREST
                                   (
                                       m.drugid AS "DrugID",
                                       m.tradenmr AS "TradeNmR",
                                       m.innr AS "InnR",
                                       m.packnx AS "PackNx",
                                       m.dosager AS "DosageR",
                                       m.packqn AS "PackQn",
                                       m.pack AS "Pack",
                                       m.drugfmnmrs AS "DrugFmNmRS",
                                       m.segment AS "Segment",
                                       m.year AS "Year",
                                       m.month AS "Month",
                                       m.series AS "Series",
                                       m.totdrugqn AS "TotDrugQn",
                                       m.mnfprice AS "MnfPrice",
                                       m.prcprice AS "PrcPrice",
                                       m.rtlprice AS "RtlPrice",
                                       m.funds AS "Funds",
                                       m.vendorid AS "VendorID",
                                       m.remark AS "Remark",
                                       m.srcorg AS "SrcOrg",
                                       m.ean AS "EAN",
                                       m.expitermr AS "ExpiTermR",
                                       m.clnm AS "ClNm",
                                       m.mnfnm AS "MnfNm",
                                       m.pcknm AS "PckNm",
                                       m.regnr AS "RegNr",
                                       m.maxmnfprice AS "MaxMnfPrice",
                                       TO_CHAR(m.regdate, 'DD.MM.YYYY') AS "RegDate",
                                       m.active AS "Active"
                                   )
                               )
                           )
                    INTO _result
                    FROM pharm.t_medicaments_of_roszdravnadzor m
                    WHERE m.medicamentsofroszdravnadzorid = "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
                -- Получаем тип операции
                _operationtype :=
                    (XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
                
                
                -- Обработка операции
                IF (_operationtype = _ftload)
                THEN
                    -- Generate table TMP_MEDICAMENTS_OF_ROSZDRAVNADZOR
                    DROP TABLE IF EXISTS tmp_medicaments_of_roszdravnadzor;
                    CREATE TEMP TABLE tmp_medicaments_of_roszdravnadzor
                    AS
                        SELECT (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/ID[1]/text()', x))[1]::text::uuid                    AS id,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/DrugID[1]/text()', x))[1]::text::int                 AS drugid,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/TradeNmR[1]/text()', x))[1]::varchar(1000)           AS tradenmr,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/InnR[1]/text()', x))[1]::varchar(1000)               AS innr,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/PackNx[1]/text()', x))[1]::text::bigint              AS packnx,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/DosageR[1]/text()', x))[1]::varchar(255)             AS dosager,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/PackQn[1]/text()', x))[1]::text::int                 AS packqn,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/Pack[1]/text()', x))[1]::varchar(1000)               AS pack,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/DrugFmNmRS[1]/text()', x))[1]::varchar(1000)         AS drugfmnmrs,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/Segment[1]/text()', x))[1]::varchar(255)             AS segment,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/Year[1]/text()', x))[1]::text::int                   AS year,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/Month[1]/text()', x))[1]::text::int                  AS month,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/Series[1]/text()', x))[1]::varchar(255)              AS series,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/TotDrugQn[1]/text()', x))[1]::text::decimal(12, 2)   AS totdrugqn,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/MnfPrice[1]/text()', x))[1]::text::decimal(12, 2)    AS mnfprice,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/PrcPrice[1]/text()', x))[1]::text::decimal(12, 2)    AS prcprice,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/RtlPrice[1]/text()', x))[1]::text::decimal(12, 2)    AS rtlprice,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/Funds[1]/text()', x))[1]::text::decimal(12, 2)       AS funds,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/VendorID[1]/text()', x))[1]::varchar(255)            AS vendorid,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/Remark[1]/text()', x))[1]::varchar(1000)             AS remark,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/SrcOrg[1]/text()', x))[1]::varchar(255)              AS srcorg,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/EAN[1]/text()', x))[1]::varchar(14)                  AS ean,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/MaxMnfPrice[1]/text()', x))[1]::text::decimal(12, 2) AS maxmnfprice,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/ExpiTermR[1]/text()', x))[1]::varchar(500)           AS expitermr,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/ClNm[1]/text()', x))[1]::varchar(255)                AS clnm,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/MnfNm[1]/text()', x))[1]::varchar(1000)              AS mnfnm,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/PckNm[1]/text()', x))[1]::varchar(1000)              AS pcknm,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/RegNr[1]/text()', x))[1]::varchar(100)               AS regnr,
                               TO_TIMESTAMP((XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/RegDate[1]/text()', x))[1]::text,
                                            'YYYY-MM-DD')                                                                AS regdate,
                               (XPATH('MEDICAMENT_OF_ROSZDRAVNADZOR/Active[1]/text()', x))[1]::text::boolean             AS active
                        FROM UNNEST(XPATH(
                            'MEDICAMENT_OF_ROSZDRAVNADZOR/body/MEDICAMENTS_OF_ROSZDRAVNADZOR/MEDICAMENT_OF_ROSZDRAVNADZOR',
                            "Xml"::xml)) AS x;
                    
                    ANALYZE tmp_medicaments_of_roszdravnadzor;
                    
                    UPDATE pharm.t_medicaments_of_roszdravnadzor s
                    SET active = '0'
                    WHERE NOT EXISTS
                              (SELECT *
                               FROM tmp_medicaments_of_roszdravnadzor nmf
                               WHERE nmf.id = s.medicamentsofroszdravnadzorid
                              );
                    
                    -- Обновляем имеющиеся
                    UPDATE pharm.t_medicaments_of_roszdravnadzor m
                    SET drugid      = nmf.drugid,
                        tradenmr    = nmf.tradenmr,
                        innr        = nmf.innr,
                        packnx      = nmf.packnx,
                        dosager     = nmf.dosager,
                        packqn      = nmf.packqn,
                        pack        = nmf.pack,
                        drugfmnmrs  = nmf.drugfmnmrs,
                        segment     = nmf.segment,
                        year        = nmf.year,
                        month       = nmf.month,
                        series      = nmf.series,
                        totdrugqn   = nmf.totdrugqn,
                        mnfprice    = nmf.mnfprice,
                        prcprice    = nmf.prcprice,
                        rtlprice    = nmf.rtlprice,
                        funds       = nmf.funds,
                        vendorid    = nmf.vendorid,
                        remark      = nmf.remark,
                        srcorg      = nmf.srcorg,
                        ean         = nmf.ean,
                        maxmnfprice = nmf.maxmnfprice,
                        expitermr   = nmf.expitermr,
                        clnm        = nmf.clnm,
                        mnfnm       = nmf.mnfnm,
                        pcknm       = nmf.pcknm,
                        regnr       = nmf.regnr,
                        regdate     = nmf.regdate,
                        active      = COALESCE(nmf.active, '1')
                    FROM tmp_medicaments_of_roszdravnadzor nmf
                    WHERE nmf.id = m.medicamentsofroszdravnadzorid
                      AND m.active = '1';
                    
                    -- Загружаем новые
                    INSERT INTO
                        pharm.t_medicaments_of_roszdravnadzor
                    (medicamentsofroszdravnadzorid,
                     drugid,
                     tradenmr,
                     innr,
                     packnx,
                     dosager,
                     packqn,
                     pack,
                     drugfmnmrs,
                     segment,
                     year,
                     month,
                     series,
                     totdrugqn,
                     mnfprice,
                     prcprice,
                     rtlprice,
                     funds,
                     vendorid,
                     remark,
                     srcorg,
                     ean,
                     maxmnfprice,
                     expitermr,
                     clnm,
                     mnfnm,
                     pcknm,
                     regnr,
                     regdate,
                     active)
                    SELECT nmf.id,
                           nmf.drugid,
                           nmf.tradenmr,
                           nmf.innr,
                           nmf.packnx,
                           nmf.dosager,
                           nmf.packqn,
                           nmf.pack,
                           nmf.drugfmnmrs,
                           nmf.segment,
                           nmf.year,
                           nmf.month,
                           nmf.series,
                           nmf.totdrugqn,
                           nmf.mnfprice,
                           nmf.prcprice,
                           nmf.rtlprice,
                           nmf.funds,
                           nmf.vendorid,
                           nmf.remark,
                           nmf.srcorg,
                           nmf.ean,
                           nmf.maxmnfprice,
                           nmf.expitermr,
                           nmf.clnm,
                           nmf.mnfnm,
                           nmf.pcknm,
                           nmf.regnr,
                           nmf.regdate,
                           COALESCE(nmf.active, '1')
                    FROM tmp_medicaments_of_roszdravnadzor nmf
                    WHERE NOT EXISTS
                              (SELECT *
                               FROM pharm.t_medicaments_of_roszdravnadzor m
                               WHERE m.medicamentsofroszdravnadzorid = nmf.id
                              );
                ELSE
                    -- Generate table TMP_MEDICAMENT_OF_ROSZDRAVNADZOR
                    DROP TABLE IF EXISTS tmp_medicament_of_roszdravnadzor;
                    CREATE TEMP TABLE tmp_medicament_of_roszdravnadzor
                    AS
                        SELECT (XPATH('/header[1]/ID[1]/text()', t.x1))[1]::text::uuid                  AS id,
                               (XPATH('/body[1]/DrugID[1]/text()', t.x2))[1]::text::int                 AS drugid,
                               (XPATH('/body[1]/TradeNmR[1]/text()', x2))[1]::varchar(1000)             AS tradenmr,
                               (XPATH('/body[1]/InnR[1]/text()', t.x2))[1]::varchar(1000)               AS innr,
                               (XPATH('/body[1]/PackNx[1]/text()', t.x2))[1]::text::bigint              AS packnx,
                               (XPATH('/body[1]/DosageR[1]/text()', t.x2))[1]::varchar(255)             AS dosager,
                               (XPATH('/body[1]/PackQn[1]/text()', t.x2))[1]::text::int                 AS packqn,
                               (XPATH('/body[1]/Pack[1]/text()', t.x2))[1]::varchar(1000)               AS pack,
                               (XPATH('/body[1]/DrugFmNmRS[1]/text()', t.x2))[1]::varchar(1000)         AS drugfmnmrs,
                               (XPATH('/body[1]/Segment[1]/text()', t.x2))[1]::varchar(255)             AS segment,
                               (XPATH('/body[1]/Year[1]/text()', t.x2))[1]::text::int                   AS year,
                               (XPATH('/body[1]/Month[1]/text()', t.x2))[1]::text::int                  AS month,
                               (XPATH('/body[1]/Series[1]/text()', t.x2))[1]::varchar(255)              AS series,
                               (XPATH('/body[1]/TotDrugQn[1]/text()', t.x2))[1]::text::decimal(12, 2)   AS totdrugqn,
                               (XPATH('/body[1]/MnfPrice[1]/text()', t.x2))[1]::text::decimal(12, 2)    AS mnfprice,
                               (XPATH('/body[1]/PrcPrice[1]/text()', t.x2))[1]::text::decimal(12, 2)    AS prcprice,
                               (XPATH('/body[1]/RtlPrice[1]/text()', t.x2))[1]::text::decimal(12, 2)    AS rtlprice,
                               (XPATH('/body[1]/Funds[1]/text()', t.x2))[1]::text::decimal(12, 2)       AS funds,
                               (XPATH('/body[1]/VendorID[1]/text()', t.x2))[1]::varchar(255)            AS vendorid,
                               (XPATH('/body[1]/Remark[1]/text()', t.x2))[1]::varchar(1000)             AS remark,
                               (XPATH('/body[1]/SrcOrg[1]/text()', t.x2))[1]::varchar(255)              AS srcorg,
                               (XPATH('/body[1]/EAN[1]/text()', t.x2))[1]::varchar(14)                  AS ean,
                               (XPATH('/body[1]/MaxMnfPrice[1]/text()', t.x2))[1]::text::decimal(12, 2) AS maxmnfprice,
                               (XPATH('/body[1]/ExpiTermR[1]/text()', t.x2))[1]::varchar(500)           AS expitermr,
                               (XPATH('/body[1]/ClNm[1]/text()', t.x2))[1]::varchar(255)                AS clnm,
                               (XPATH('/body[1]/MnfNm[1]/text()', t.x2))[1]::varchar(1000)              AS mnfnm,
                               (XPATH('/body[1]/PckNm[1]/text()', t.x2))[1]::varchar(1000)              AS pcknm,
                               (XPATH('/body[1]/RegNr[1]/text()', t.x2))[1]::varchar(100)               AS regnr,
                               (XPATH('/body[1]/Active[1]/text()', t.x2))[1]::text::boolean             AS active,
                               TO_TIMESTAMP((XPATH('/RegDate[1]/text()', t.x2))[1]::text, 'YYYY-MM-DD') AS regdate
                        FROM (SELECT UNNEST(XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/header', x)) AS x1,
                                     UNNEST(XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR/body', x))   AS x2
                              FROM UNNEST(XPATH('/MEDICAMENT_OF_ROSZDRAVNADZOR', "Xml"::xml)) x
                             ) t;
                    
                    IF (_operationtype = _delete)
                    THEN
                        UPDATE pharm.t_medicaments_of_roszdravnadzor m
                        SET active = '0'
                        FROM tmp_medicament_of_roszdravnadzor nmf
                        WHERE m.medicamentsofroszdravnadzorid = nmf.id;
                    ELSE
                        -- update
                        UPDATE pharm.t_medicaments_of_roszdravnadzor m
                        SET drugid      = nmf.drugid,
                            tradenmr    = nmf.tradenmr,
                            innr        = nmf.innr,
                            packnx      = nmf.packnx,
                            dosager     = nmf.dosager,
                            packqn      = nmf.packqn,
                            pack        = nmf.pack,
                            drugfmnmrs  = nmf.drugfmnmrs,
                            segment     = nmf.segment,
                            year        = nmf.year,
                            month       = nmf.month,
                            series      = nmf.series,
                            totdrugqn   = nmf.totdrugqn,
                            mnfprice    = nmf.mnfprice,
                            prcprice    = nmf.prcprice,
                            rtlprice    = nmf.rtlprice,
                            funds       = nmf.funds,
                            vendorid    = nmf.vendorid,
                            remark      = nmf.remark,
                            srcorg      = nmf.srcorg,
                            ean         = nmf.ean,
                            maxmnfprice = nmf.maxmnfprice,
                            expitermr   = nmf.expitermr,
                            clnm        = nmf.clnm,
                            mnfnm       = nmf.mnfnm,
                            pcknm       = nmf.pcknm,
                            regnr       = nmf.regnr,
                            regdate     = nmf.regdate,
                            active      = COALESCE(nmf.active, '1')
                        FROM tmp_medicament_of_roszdravnadzor nmf
                        WHERE nmf.id = m.medicamentsofroszdravnadzorid
                          AND _operationtype = _update
                          AND m.active = '1';
                        
                        -- insert
                        INSERT INTO
                            pharm.t_medicaments_of_roszdravnadzor
                        (medicamentsofroszdravnadzorid,
                         drugid,
                         tradenmr,
                         innr,
                         packnx,
                         dosager,
                         packqn,
                         pack,
                         drugfmnmrs,
                         segment,
                         year,
                         month,
                         series,
                         totdrugqn,
                         mnfprice,
                         prcprice,
                         rtlprice,
                         funds,
                         vendorid,
                         remark,
                         srcorg,
                         ean,
                         maxmnfprice,
                         expitermr,
                         clnm,
                         mnfnm,
                         pcknm,
                         regnr,
                         regdate,
                         active)
                        SELECT nmf.id,
                               nmf.drugid,
                               nmf.tradenmr,
                               nmf.innr,
                               nmf.packnx,
                               nmf.dosager,
                               nmf.packqn,
                               nmf.pack,
                               nmf.drugfmnmrs,
                               nmf.segment,
                               nmf.year,
                               nmf.month,
                               nmf.series,
                               nmf.totdrugqn,
                               nmf.mnfprice,
                               nmf.prcprice,
                               nmf.rtlprice,
                               nmf.funds,
                               nmf.vendorid,
                               nmf.remark,
                               nmf.srcorg,
                               nmf.ean,
                               nmf.maxmnfprice,
                               nmf.expitermr,
                               nmf.clnm,
                               nmf.mnfnm,
                               nmf.pcknm,
                               nmf.regnr,
                               nmf.regdate,
                               COALESCE(nmf.active, '1')
                        FROM tmp_medicament_of_roszdravnadzor nmf
                        WHERE NOT EXISTS
                            (SELECT *
                             FROM pharm.t_medicaments_of_roszdravnadzor m
                             WHERE m.medicamentsofroszdravnadzorid = nmf.id
                            )
                          AND _operationtype = _insert;
                    END IF;
                END IF;
                
                UPDATE pharm.t_bill_of_goods bg
                SET medicamentsofroszdravnadzor = NULL
                FROM pharm.t_medicaments_of_roszdravnadzor mor
                WHERE mor.medicamentsofroszdravnadzorid = bg.medicamentsofroszdravnadzor
                  AND mor.active = '0';
            
            END IF;
        END IF;
    END;
END;
$$;