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

DROP PROCEDURE IF EXISTS aps.sp_rds_pharmacy;

CREATE OR REPLACE PROCEDURE aps.sp_rds_pharmacy(
    "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
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    
    DECLARE
        _xml             xml          := "Xml"::xml;
        _result          text;
        _contentencoding varchar(255) := 'utf-8';
        _type            varchar(255) := 'PHARMACY';
        _operationtype   varchar(255);
        _ownerid         uuid         := "ID";
        -- Operation types
        _insert          varchar(6)   := 'Insert';
        _update          varchar(6)   := 'Update';
        _delete          varchar(6)   := 'Delete';
        _ftload          varchar(6)   := 'FTLoad';
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        
        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 aps.t_pharmacy p WHERE p.code = "ID")
            THEN
                _operationtype := _update;
            ELSIF "IsNew" = 0 AND NOT EXISTS (SELECT * FROM aps.t_pharmacy p WHERE p.code = "ID")
            THEN
                _operationtype := _delete;
                _result := XMLELEMENT
                           (
                    NAME "PHARMACY",
                    XMLELEMENT
                    (
                        NAME "header",
                        XMLFOREST
                        (
                            _operationtype AS "TYPE",
                            "ID" AS "ID"
                        )
                    )
                           );
            END IF;
            -- Обработка операции
            IF _operationtype = _ftload
            THEN
                -- Первичная загрузка
                SELECT '<PHARMACY><header><TYPE>' || _ftload || '</TYPE></header><body><PHARMACYS>' ||
                       COALESCE(STRING_AGG(
                                    XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                    ''
                                ), '') ||
                       '</PHARMACYS></body></PHARMACY>'
                INTO _result
                FROM (SELECT XMLELEMENT(
                                 NAME "PHARMACY",
                                 XMLFOREST(
                                     p.code AS "ID",
                                     p.name AS "Name",
                                     p.namefull AS "NameFull",
                                     p.ogrn AS "OGRN",
                                     p.terr AS "Terr",
                                     p.okato AS "OKATO",
                                     TO_CHAR(p.datebegin, 'YYYY.MM.DD') AS
                                     "DateBegin",
                                     TO_CHAR(p.dateend, 'YYYY.MM.DD') AS
                                     "DateEnd",
                                     TO_CHAR("dateTime", 'YYYY.MM.DD') AS
                                     "RegisterDate",
                                     p.recipeservedtypes AS
                                     "RecipeServedTypes",
                                     p.address AS "Address",
                                     r.code AS "Rajon",
                                     p.number AS "Number",
                                     p.phone AS "Phone",
                                     p.dlo AS "Dlo",
                                     p.licensenumber AS "LicenseNumber",
                                     p.licenseissue AS "LicenseIssue",
                                     p.licensedate AS "LicenseDate",
                                     p.fiasaddress AS "FiasAddress",
                                     p.fiashome AS "FiasHome",
                                     p.mail AS "Mail",
                                     e1.codeehr AS "EhrRusBr9ap1s2qb",
                                     rrb1.currversion AS
                                     "EhrRusBr9ap1s2qbVer",
                                     p.postalcode AS "PostalCode",
                                     e.idehr AS "EhrRusMdr308",
                                     rrb.currversion AS "EhrRusMdr308Ver"
                                 )
                             ) AS xml_elem
                      FROM aps.t_pharmacy                p
                      LEFT JOIN aps.t_rajon              r ON r.rajonid = p.rajon
                      LEFT JOIN ehr.t_ehr_rus_mdr308     e ON e.ehrrusid = p.ehrrusmdr308
                      JOIN      ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = e.ehrrusrefbooks
                      LEFT JOIN ehr.t_ehr_rus_br9ap1s2qb e1 ON e1.ehrrusid = p.ehrrusbr9ap1s2qb
                      JOIN      ehr.t_ehr_rus_ref_books  rrb1
                                    ON rrb1.ehrrusrefbooksid = e1.ehrrusrefbooks
                     ) tt;
            ELSIF _operationtype <> _delete
            THEN
                -- собираем PHARMACY
                SELECT XMLELEMENT
                       (
                           NAME "PHARMACY",
                           XMLELEMENT
                           (
                               NAME "header",
                               XMLFOREST
                               (
                                   _operationtype AS "TYPE",
                                   "ID" AS "ID"
                               )
                           ),
                           XMLELEMENT
                           (
                               NAME "body",
                               XMLFOREST
                               (
                                   p.name AS "Name",
                                   p.namefull AS "NameFull",
                                   p.ogrn AS "OGRN",
                                   p.terr AS "Terr",
                                   p.okato AS "OKATO",
                                   TO_CHAR(p.datebegin, 'YYYY.MM.DD') AS "DateBegin",
                                   TO_CHAR(p.dateend, 'YYYY.MM.DD') AS "DateEnd",
                                   TO_CHAR("dateTime", 'YYYY.MM.DD') AS "RegisterDate",
                                   p.recipeservedtypes AS "RecipeServedTypes",
                                   p.address AS "Address",
                                   r.code AS "Rajon",
                                   p.number AS "Number",
                                   p.phone AS "Phone",
                                   p.dlo AS "Dlo",
                                   p.licensenumber AS "LicenseNumber",
                                   p.licenseissue AS "LicenseIssue",
                                   p.licensedate AS "LicenseDate",
                                   p.fiasaddress AS "FiasAddress",
                                   p.fiashome AS "FiasHome",
                                   p.mail AS "Mail",
                                   e1.codeehr AS "EhrRusBr9ap1s2qb",
                                   rrb1.currversion AS "EhrRusBr9ap1s2qbVer",
                                   p.postalcode AS "PostalCode",
                                   e.idehr AS "EhrRusMdr308",
                                   rrb.currversion AS "EhrRusMdr308Ver"
                               )
                           )
                       )
                INTO _result
                FROM aps.t_pharmacy                p
                LEFT JOIN aps.t_rajon              r ON r.rajonid = p.rajon
                LEFT JOIN ehr.t_ehr_rus_mdr308     e ON e.ehrrusid = p.ehrrusmdr308
                JOIN      ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = e.ehrrusrefbooks
                LEFT JOIN ehr.t_ehr_rus_br9ap1s2qb e1 ON e1.ehrrusid = p.ehrrusbr9ap1s2qb
                JOIN      ehr.t_ehr_rus_ref_books  rrb1 ON rrb1.ehrrusrefbooksid = e1.ehrrusrefbooks
                WHERE p.code = "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 := NULLIF(TRIM(XPATH('/PHARMACY/header[1]/TYPE[1]/text()', _xml)::text, '{}'),
                                         '')::varchar(36);
                
                
                -- Обработка операции
                IF _operationtype = _ftload
                THEN
                    -- Generate table Tmp_PHARMACYS
                    DROP TABLE IF EXISTS tmp_pharmacys;
                    CREATE TEMP TABLE tmp_pharmacys
                    AS
                        SELECT NULLIF(TRIM(XPATH('/PHARMACY/ID[1]/text()', x)::text, '{}'), '')::int  AS id,
                               NULLIF(TRIM(XPATH('/PHARMACY/Name[1]/text()', x)::text, '{}'),
                                      '')::varchar(255)                                               AS name,
                               NULLIF(TRIM(XPATH('/PHARMACY/NameFull[1]/text()', x)::text, '{}'),
                                      '')::varchar(255)                                               AS namefull,
                               NULLIF(TRIM(XPATH('/PHARMACY/OGRN[1]/text()', x)::text, '{}'),
                                      '')::varchar(255)                                               AS ogrn,
                               NULLIF(TRIM(XPATH('/PHARMACY/Terr[1]/text()', x)::text, '{}'),
                                      '')::varchar(255)                                               AS terr,
                               NULLIF(TRIM(XPATH('/PHARMACY/OKATO[1]/text()', x)::text, '{}'),
                                      '')::varchar(255)                                               AS okato,
                               TO_TIMESTAMP(NULLIF(TRIM(XPATH('/PHARMACY/DateBegin[1]/text()', x)::text, '{}'), ''),
                                            'YYYY.MM.DD')                                             AS datebegin,
                               TO_TIMESTAMP(NULLIF(TRIM(XPATH('/PHARMACY/DateEnd[1]/text()', x)::text, '{}'), ''),
                                            'YYYY.MM.DD')                                             AS dateend,
                               TO_TIMESTAMP(NULLIF(TRIM(XPATH('/PHARMACY/RegisterDate[1]/text()', x)::text, '{}'), ''),
                                            'YYYY.MM.DD')                                             AS registerdate,
                               NULLIF(TRIM(XPATH('/PHARMACY/RecipeServerTypes[1]/text()', x)::text, '{}'),
                                      '')::int                                                        AS recipeservedtypes,
                               NULLIF(TRIM(XPATH('/PHARMACY/Address[1]/text()', x)::text, '{}'),
                                      '')::varchar(255)                                               AS address,
                               NULLIF(TRIM(XPATH('/PHARMACY/Rajon[1]/text()', x)::text, '{}'),
                                      '')::varchar(13)                                                AS rajon,
                               NULLIF(TRIM(XPATH('/PHARMACY/Number[1]/text()', x)::text, '{}'),
                                      '')::varchar(50)                                                AS number,
                               NULLIF(TRIM(XPATH('/PHARMACY/Phone[1]/text()', x)::text, '{}'),
                                      '')::varchar(40)                                                AS phone,
                               NULLIF(TRIM(XPATH('/PHARMACY/Dlo[1]/text()', x)::text, '{}'), '')::boolean AS dlo,
                               NULLIF(TRIM(XPATH('/PHARMACY/LicenseNumber[1]/text()', x)::text, '{}'),
                                      '')::varchar(255)                                               AS licensenumber,
                               NULLIF(TRIM(XPATH('/PHARMACY/LicenseIssue[1]/text()', x)::text, '{}'),
                                      '')::varchar(255)                                               AS licenseissue,
                               TO_TIMESTAMP(NULLIF(TRIM(XPATH('/PHARMACY/LicenseDate[1]/text()', x)::text, '{}'), ''),
                                            'YYYY.MM.DD')                                             AS licensedate,
                               NULLIF(TRIM(XPATH('/PHARMACY/FiasAddress[1]/text()', x)::text, '{}'),
                                      '')::varchar(255)                                               AS fiasaddress,
                               NULLIF(TRIM(XPATH('/PHARMACY/FiasHome[1]/text()', x)::text, '{}'),
                                      '')::varchar(255)                                               AS fiashome,
                               NULLIF(TRIM(XPATH('/PHARMACY/Mail[1]/text()', x)::text, '{}'),
                                      '')::varchar(200)                                               AS mail,
                               NULLIF(TRIM(XPATH('/PHARMACY/EhrRusBr9ap1s2qb[1]/text()', x)::text, '{}'),
                                      '')::varchar(52)                                                AS ehrrusbr9ap1s2qb,
                               NULLIF(TRIM(XPATH('/PHARMACY/EhrRusBr9ap1s2qbVer[1]/text()', x)::text, '{}'),
                                      '')::varchar(8)                                                 AS ehrrusbr9ap1s2qbver,
                               NULLIF(TRIM(XPATH('/PHARMACY/PostalCode[1]/text()', x)::text, '{}'),
                                      '')::varchar(255)                                               AS postalcode,
                               NULLIF(TRIM(XPATH('/PHARMACY/EhrRusMdr308[1]/text()', x)::text, '{}'),
                                      '')::int                                                        AS ehrrusmdr308,
                               NULLIF(TRIM(XPATH('/PHARMACY/EhrRusMdr308Ver[1]/text()', x)::text, '{}'),
                                      '')::varchar(8)                                                 AS ehrrusmdr308ver
                        FROM (SELECT UNNEST(XPATH('/PHARMACY/body/PHARMACYS/PHARMACY', x)) AS x
                              FROM (SELECT "Xml"::xml AS x) root
                             ) pharmacy;
                    
                    -- Первичная загрузка
                    -- Аптеки не из справочника RDS переводим в неактивные
                    UPDATE aps.t_pharmacy p
                    SET dateend      = "dateTime",
                        registerdate = "dateTime"
                    WHERE NOT EXISTS
                              (SELECT *
                               FROM tmp_pharmacys np
                               WHERE np.id = p.code
                              );
                    -- Обновляем имеющиеся Аптеки
                    UPDATE aps.t_pharmacy p
                    SET name              = np.name,
                        namefull          = np.namefull,
                        ogrn              = np.ogrn,
                        terr              = np.terr,
                        okato             = np.okato,
                        datebegin         = np.datebegin,
                        dateend           = np.dateend,
                        registerdate      = np.registerdate,
                        recipeservedtypes = np.recipeservedtypes,
                        address           = np.address,
                        rajon             = (SELECT r.rajonid FROM aps.t_rajon r WHERE r.code = np.rajon LIMIT 1),
                        number            = np.number,
                        phone             = np.phone,
                        dlo               = COALESCE(np.dlo, '0'),
                        licensenumber     = np.licensenumber,
                        licenseissue      = np.licenseissue,
                        licensedate       = np.licensedate,
                        fiasaddress       = np.fiasaddress,
                        fiashome          = np.fiashome,
                        mail              = np.mail,
                        ehrrusbr9ap1s2qb  = e1.ehrrusid,
                        postalcode        = np.postalcode,
                        ehrrusmdr308      = e.ehrrusid
                    FROM tmp_pharmacys np
                    LEFT JOIN LATERAL (
                                  SELECT e.ehrrusid
                                  FROM ehr.t_ehr_rus_mdr308         e
                                  LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = e.ehrrusrefbooks
                                  WHERE e.idehr = np.ehrrusmdr308
                                    AND rrb.currversion = np.ehrrusmdr308ver
                                  )    e ON TRUE
                    LEFT JOIN LATERAL (
                                  SELECT e1.ehrrusid
                                  FROM ehr.t_ehr_rus_br9ap1s2qb     e1
                                  LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = e1.ehrrusrefbooks
                                  WHERE e1.codeehr = np.ehrrusbr9ap1s2qb
                                    AND rrb1.currversion = np.ehrrusbr9ap1s2qbver
                                  )    e1 ON TRUE
                    WHERE np.id = p.code;
                    
                    -- Загружаем новые
                    INSERT INTO
                        aps.t_pharmacy(code,
                                       name,
                                       namefull,
                                       ogrn,
                                       terr,
                                       okato,
                                       datebegin,
                                       dateend,
                                       registerdate,
                                       recipeservedtypes,
                                       address,
                                       rajon,
                                       number,
                                       phone,
                                       dlo,
                                       licensenumber,
                                       licenseissue,
                                       licensedate,
                                       fiasaddress,
                                       fiashome,
                                       mail,
                                       ehrrusbr9ap1s2qb,
                                       postalcode,
                                       ehrrusmdr308)
                    SELECT np.id,
                           np.name,
                           np.namefull,
                           np.ogrn,
                           np.terr,
                           np.okato,
                           np.datebegin,
                           np.dateend,
                           np.registerdate,
                           np.recipeservedtypes,
                           np.address,
                           (SELECT r.rajonid FROM aps.t_rajon r WHERE r.code = np.rajon LIMIT 1)/*<Rajon, varchar(255),> */,
                           np.number/*<Number, varchar(50),>*/,
                           phone,
                           COALESCE(np.dlo, '0'),
                           np.licensenumber,
                           np.licenseissue,
                           np.licensedate,
                           np.fiasaddress,
                           np.fiashome,
                           np.mail,
                           e1.ehrrusid,
                           np.postalcode,
                           e.ehrrusid
                    FROM tmp_pharmacys np
                    LEFT JOIN LATERAL (
                                  SELECT e.ehrrusid
                                  FROM ehr.t_ehr_rus_mdr308         e
                                  LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = e.ehrrusrefbooks
                                  WHERE e.idehr = np.ehrrusmdr308
                                    AND rrb.currversion = np.ehrrusmdr308ver
                                  )    e ON TRUE
                    LEFT JOIN LATERAL (
                                  SELECT e1.ehrrusid
                                  FROM ehr.t_ehr_rus_br9ap1s2qb     e1
                                  LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = e1.ehrrusrefbooks
                                  WHERE e1.codeehr = np.ehrrusbr9ap1s2qb
                                    AND rrb1.currversion = np.ehrrusbr9ap1s2qbver
                                  )    e1 ON TRUE
                    WHERE NOT EXISTS
                              (SELECT *
                               FROM aps.t_pharmacy p
                               WHERE p.code = np.id
                              );
                ELSE
                    -- Generate table Tmp_PHARMACY
                    DROP TABLE IF EXISTS tmp_pharmacy;
                    CREATE TEMP TABLE tmp_pharmacy
                    AS
                        SELECT NULLIF(TRIM(XPATH('/PHARMACY/header[1]/ID[1]/text()', _xml)::text, '{}'),
                                      '')::int          AS id,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/Name[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(255) AS name,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/NameFull[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(255) AS namefull,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/OGRN[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(255) AS ogrn,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/Terr[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(255) AS terr,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/OKATO[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(255) AS okato,
                               TO_TIMESTAMP(
                                   NULLIF(TRIM(XPATH('/PHARMACY/body[1]/DateBegin[1]/text()', _xml)::text, '{}'),
                                          ''),
                                   'YYYY.MM.DD')        AS datebegin,
                               TO_TIMESTAMP(
                                   NULLIF(TRIM(XPATH('/PHARMACY/body[1]/DateEnd[1]/text()', _xml)::text, '{}'),
                                          ''),
                                   'YYYY.MM.DD')        AS dateend,
                               TO_TIMESTAMP(
                                   NULLIF(TRIM(XPATH('/PHARMACY/body[1]/RegisterDate[1]/text()', _xml)::text,
                                               '{}'), ''),
                                   'YYYY.MM.DD')        AS registerdate,
                               NULLIF(
                                   TRIM(XPATH('/PHARMACY/body[1]/RecipeServedTypes[1]/text()', _xml)::text, '{}'),
                                   '')::int             AS recipeservedtypes,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/Address[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(255) AS address,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/Rajon[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(13)  AS rajon,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/Number[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(50)  AS number,
                               NULLIF(TRIM(XPATH('/PHARMACY/Phone[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(40)  AS phone,
                               NULLIF(TRIM(XPATH('/PHARMACY/header[1]/Dlo[1]/text()', _xml)::text, '{}'),
                                      '')::boolean          AS dlo,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/LicenseNumber[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(255) AS licensenumber,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/LicenseIssue[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(255) AS licenseissue,
                               TO_TIMESTAMP(
                                   NULLIF(TRIM(XPATH('/PHARMACY/body[1]/LicenseDate[1]/text()', _xml)::text, '{}'), ''),
                                   'YYYY.MM.DD')        AS licensedate,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/FiasAddress[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(255) AS fiasaddress,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/FiasHome[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(255) AS fiashome,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/Mail[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(200) AS mail,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/EhrRusBr9ap1s2qb[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(52)  AS ehrrusbr9ap1s2qb,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/EhrRusBr9ap1s2qbVer[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(8)   AS ehrrusbr9ap1s2qbver,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/PostalCode[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(255) AS postalcode,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/EhrRusMdr308[1]/text()', _xml)::text, '{}'),
                                      '')::int          AS ehrrusmdr308,
                               NULLIF(TRIM(XPATH('/PHARMACY/body[1]/EhrRusMdr308Ver[1]/text()', _xml)::text, '{}'),
                                      '')::varchar(8)   AS ehrrusmdr308ver;
                    
                    IF _operationtype = _delete
                    THEN
                        -- Удаление
                        UPDATE aps.t_pharmacy p
                        SET dateend      = "dateTime",
                            registerdate = "dateTime"
                        FROM tmp_pharmacy np
                        WHERE np.id = p.code;
                    ELSE
                        -- update APS.T_PHARMACY
                        UPDATE aps.t_pharmacy p
                        SET name              = np.name,
                            namefull          = np.namefull,
                            ogrn              = np.ogrn,
                            terr              = np.terr,
                            okato             = np.okato,
                            datebegin         = np.datebegin,
                            dateend           = np.dateend,
                            registerdate      = np.registerdate,
                            recipeservedtypes = np.recipeservedtypes,
                            address           = np.address,
                            rajon             = (SELECT r.rajonid FROM aps.t_rajon r WHERE r.code = np.rajon LIMIT 1),
                            number            = np.number,
                            phone             = np.phone,
                            dlo               = COALESCE(np.dlo, '0'),
                            licensenumber     = np.licensenumber,
                            licenseissue      = np.licenseissue,
                            licensedate       = np.licensedate,
                            fiasaddress       = np.fiasaddress,
                            fiashome          = np.fiashome,
                            mail              = np.mail,
                            ehrrusbr9ap1s2qb  = e1.ehrrusid,
                            postalcode        = np.postalcode,
                            ehrrusmdr308      = e.ehrrusid
                        FROM tmp_pharmacy np
                        LEFT JOIN LATERAL (
                                      SELECT e.ehrrusid
                                      FROM ehr.t_ehr_rus_mdr308         e
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = e.ehrrusrefbooks
                                      WHERE e.idehr = np.ehrrusmdr308
                                        AND rrb.currversion = np.ehrrusmdr308ver
                                      )   e ON TRUE
                        LEFT JOIN LATERAL (
                                      SELECT e1.ehrrusid
                                      FROM ehr.t_ehr_rus_br9ap1s2qb     e1
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrb1
                                                    ON rrb1.ehrrusrefbooksid = e1.ehrrusrefbooks
                                      WHERE e1.codeehr = np.ehrrusbr9ap1s2qb
                                        AND rrb1.currversion = np.ehrrusbr9ap1s2qbver
                                      )   e1 ON TRUE
                        WHERE np.id = p.code
                          AND _operationtype = _update;
                        
                        -- insert APS.T_PHARMACY
                        INSERT INTO
                            aps.t_pharmacy(code,
                                           name,
                                           namefull,
                                           ogrn,
                                           terr,
                                           okato,
                                           datebegin,
                                           dateend,
                                           registerdate,
                                           recipeservedtypes,
                                           address,
                                           rajon,
                                           number,
                                           phone,
                                           dlo,
                                           licensenumber,
                                           licenseissue,
                                           licensedate,
                                           fiasaddress,
                                           fiashome,
                                           mail,
                                           ehrrusbr9ap1s2qb,
                                           postalcode,
                                           ehrrusmdr308)
                        SELECT np.id,
                               np.name,
                               np.namefull,
                               np.ogrn,
                               np.terr,
                               np.okato,
                               np.datebegin,
                               np.dateend,
                               np.registerdate,
                               np.recipeservedtypes,
                               np.address,
                               (SELECT r.rajonid FROM aps.t_rajon r WHERE r.code = np.rajon LIMIT 1),--<Rajon, varchar(255),>
                               np.number,--<Number, varchar(50),>
                               phone,
                               COALESCE(np.dlo, '0'),
                               np.licensenumber,
                               np.licenseissue,
                               np.licensedate,
                               np.fiasaddress,
                               np.fiashome,
                               np.mail,
                               e1.ehrrusid,
                               np.postalcode,
                               e.ehrrusid
                        FROM tmp_pharmacy np
                        LEFT JOIN LATERAL (
                                      SELECT e.ehrrusid
                                      FROM ehr.t_ehr_rus_mdr308         e
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = e.ehrrusrefbooks
                                      WHERE e.idehr = np.ehrrusmdr308
                                        AND rrb.currversion = np.ehrrusmdr308ver
                                      )   e ON TRUE
                        LEFT JOIN LATERAL (
                                      SELECT e1.ehrrusid
                                      FROM ehr.t_ehr_rus_br9ap1s2qb     e1
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrb1
                                                    ON rrb1.ehrrusrefbooksid = e1.ehrrusrefbooks
                                      WHERE e1.codeehr = np.ehrrusbr9ap1s2qb
                                        AND rrb1.currversion = np.ehrrusbr9ap1s2qbver
                                      )   e1 ON TRUE
                        WHERE NOT EXISTS
                            (SELECT *
                             FROM aps.t_pharmacy p
                             WHERE p.code = np.id
                            )
                          AND _operationtype = _insert;
                    END IF;
                END IF;
                
                -- генерируем след. номер
                --call dbo.sp_GenerateIdForEntity 'PHARMACY'
                UPDATE t_entity_ids ei
                SET id = (SELECT MAX(code) FROM aps.t_pharmacy) + 1
                WHERE ei.entname = 'PHARMACY'
                  AND ei.fieldname = 'PrimaryKey';
            
            END IF;
        END IF;
    END;
END;
$$;