DROP PROCEDURE IF EXISTS sp_getsetpharmacy;

CREATE OR REPLACE PROCEDURE public.sp_getsetpharmacy(
    "Xml" text DEFAULT NULL,
    "IsNew" integer DEFAULT 1,
    "ID" varchar(255) DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT NULL,
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml"::xml;
    p_isnew           boolean      := "IsNew"::boolean;
    p_id              uuid         := "ID";
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    v_result          text;
    v_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'PHARMACY';
    v_operationtype   varchar(255);
    v_ownerid         uuid         := p_id;
    c_insert          varchar(6)   := 'Insert';
    c_update          varchar(6)   := 'Update';
    c_delete          varchar(6)   := 'Delete';
    c_ftload          varchar(6)   := 'FTLoad';

BEGIN


    IF (p_xml IS NULL) THEN

        IF (p_id IS NULL) THEN
            v_operationtype := c_ftload;
        ELSIF (p_isnew = TRUE) THEN
            v_operationtype := c_insert;
        ELSIF (p_isnew = FALSE AND EXISTS(SELECT 1 FROM rds.t_pharmacy p WHERE p.code = p_id)) THEN
            v_operationtype = c_update;
        ELSIF (p_isnew = FALSE AND NOT EXISTS(SELECT 1 FROM rds.t_pharmacy p WHERE p.code = p_id)) THEN
            v_operationtype = c_delete;

            SELECT XMLELEMENT(
                       NAME "PHARMACY",
                       XMLFOREST(
                           XMLELEMENT(
                               NAME "header",
                               XMLFOREST(
                                   v_operationtype AS "TYPE",
                                   p_id AS "ID"
                               )
                           )
                       )
                   )::text
            INTO v_result;
        END IF;

        IF (v_operationtype = c_ftload) THEN
            SELECT '<PHARMACY><header><TYPE>' || c_ftload || '</TYPE></header><body><PHARMACYS>' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                ''
                            ), '') ||
                   '</PHARMACYS></body></PHARMACY>'
            INTO v_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, 'DD.MM.YYYY') AS "DateBegin",
                                 TO_CHAR(p.dateend, 'DD.MM.YYYY') AS "DateEnd",
                                 TO_CHAR(p_datetime, 'DD.MM.YYYY') AS "RegisterDate",
                                 p.recipeservedtypes AS "RecipeServerTypes",
                                 p.address AS "Address",
                                 r.code AS "Rajon",
                                 p.number AS "Number",
                                 p.phone AS "Phone",
                                 p.licensenumber AS "LicenseNumber",
                                 p.licenseissue AS "LicenseIssue",
                                 TO_CHAR(p.licensedate, 'DD.MM.YYYY') AS "LicenseDate",
                                 p.dlo AS "Dlo",
                                 p.fiasaddress AS "FiasAddress",
                                 p.fiashome AS "FiasHome",
                                 p.mail AS "Mail",
                                 e1.codeehr AS "EhrRusBr9ap1s2qb",
                                 rrb1.currversion AS "EhrRusBr9ap1s2qbVer",
                                 p.zipcode AS "PostalCode",
                                 e.idehr AS "EhrRusMdr308",
                                 rrb.currversion AS "EhrRusMdr308Ver"
                             )
                         ) AS xml_elem
                  FROM rds.t_pharmacy                p
                  LEFT JOIN rds.t_rajon              r ON r.rajonid = p.rajon
                  LEFT JOIN ehr.t_ehr_rus_mdr308     e ON e.ehrrusid = p.ehrrusmdr308
                  LEFT 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
                  LEFT JOIN ehr.t_ehr_rus_ref_books  rrb1 ON rrb1.ehrrusrefbooksid = e1.ehrrusrefbooks
                 ) tt;

        ELSIF (v_operationtype <> c_delete) THEN
            SELECT XMLELEMENT(
                       NAME "PHARMACY",
                       XMLFOREST(
                           XMLELEMENT(
                               NAME "header",
                               XMLFOREST(c_ftload AS "TYPE")
                           ),
                           XMLELEMENT(
                               NAME "body",
                               (SELECT 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, 'DD.MM.YYYY') AS "DateBegin",
                                           TO_CHAR(p.dateend, 'DD.MM.YYYY') AS "DateEnd",
                                           TO_CHAR(p_datetime, 'DD.MM.YYYY') AS "RegisterDate",
                                           p.recipeservedtypes AS "RecipeServerTypes",
                                           p.address AS "Address",
                                           r.code AS "Rajon",
                                           p.number AS "Number",
                                           p.phone AS "Phone",
                                           p.licensenumber AS "LicenseNumber",
                                           p.licenseissue AS "LicenseIssue",
                                           TO_CHAR(p.licensedate, 'DD.MM.YYYY') AS "LicenseDate",
                                           p.dlo AS "Dlo",
                                           p.fiasaddress AS "FiasAddress",
                                           p.fiashome AS "FiasHome",
                                           p.mail AS "Mail",
                                           e1.codeehr AS "EhrRusBr9ap1s2qb",
                                           rrb1.currversion AS "EhrRusBr9ap1s2qbVer",
                                           p.zipcode AS "PostalCode",
                                           e.idehr AS "EhrRusMdr308",
                                           rrb.currversion AS "EhrRusMdr308Ver"
                                       )
                                FROM rds.t_pharmacy                p
                                LEFT JOIN rds.t_rajon              r ON r.rajonid = p.rajon
                                LEFT JOIN ehr.t_ehr_rus_mdr308     e ON e.ehrrusid = p.ehrrusmdr308
                                LEFT 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
                                LEFT JOIN ehr.t_ehr_rus_ref_books  rrb1 ON rrb1.ehrrusrefbooksid = e1.ehrrusrefbooks
                                WHERE p.code = p_id
                               )
                           )
                       )
                   )::text
            INTO v_result;
        END IF;

        INSERT INTO
            rds.t_rabbit_send_object (rabbitsendobjectid,
                                      queuename,
                                      content,
                                      messageid,
                                      headers,
                                      contentencoding,
                                      type,
                                      userid,
                                      state,
                                      createdatetime,
                                      ownerid)
        SELECT gen_random_uuid(),
               s.servername,
               public.converttexttobinaryzip(v_result, ''),
               NULL,
               NULL,
               v_contentencoding,
               v_type,
               p_user,
               0,
               p_datetime,
               NULL
        FROM rds.t_sync_servers s
        WHERE (p_queuename IS NOT NULL AND s.servername = p_queuename)
           OR (p_queuename IS NULL AND s.servertype IN (0, 1));
    ELSE

        SELECT (XPATH('/TYPE/text()', x))[1]::varchar(36)::uuid AS type
        FROM UNNEST(XPATH('PHARMACY/header', p_xml)) x;

        DROP TABLE IF EXISTS temp_pharmacy;
        CREATE TEMPORARY TABLE temp_pharmacy
        (
            id                  VARCHAR(255),
            name                VARCHAR(255),
            namefull            VARCHAR(255),
            ogrn                VARCHAR(255),
            terr                VARCHAR(255),
            okato               VARCHAR(255),
            datebegin           TIMESTAMP,
            dateend             TIMESTAMP,
            registerdate        TIMESTAMP,
            recipeservedtypes   INT,
            address             VARCHAR(255),
            rajon               VARCHAR(13),
            number              VARCHAR(50),
            phone               VARCHAR(40),
            licensenumber       VARCHAR(40),
            licenseissue        VARCHAR(40),
            licensedate         TIMESTAMP,
            dlo                 INT,
            fiasaddress         VARCHAR(255),
            fiashome            VARCHAR(255),
            mail                VARCHAR(200),
            ehrrusbr9ap1s2qb    VARCHAR(52),
            ehrrusbr9ap1s2qbver VARCHAR(8),
            zipcode             VARCHAR(10),
            ehrrusmdr308        INT,
            ehrrusmdr308ver     VARCHAR(8)
        );

        IF (v_operationtype = c_ftload) THEN
            INSERT INTO
                temp_pharmacy (id,
                               name,
                               namefull,
                               ogrn,
                               terr,
                               okato,
                               datebegin,
                               dateend,
                               registerdate,
                               recipeservedtypes,
                               address,
                               rajon,
                               number,
                               phone,
                               licensenumber,
                               licenseissue,
                               licensedate,
                               dlo,
                               fiasaddress,
                               fiashome,
                               mail,
                               ehrrusbr9ap1s2qb,
                               ehrrusbr9ap1s2qbver,
                               zipcode,
                               ehrrusmdr308,
                               ehrrusmdr308ver)
            SELECT
                (XPATH('/PHARMACY/ID/text()', x))[1]::VARCHAR(255)                               AS id,
                (XPATH('/PHARMACY/Name/text()', x))[1]::VARCHAR(255)                             AS name,
                (XPATH('/PHARMACY/NameFull/text()', x))[1]::VARCHAR(255)                         AS namefull,
                (XPATH('/PHARMACY/OGRN/text()', x))[1]::VARCHAR(255)                             AS ogrn,
                (XPATH('/PHARMACY/Terr/text()', x))[1]::VARCHAR(255)                             AS terr,
                (XPATH('/PHARMACY/OKATO/text()', x))[1]::VARCHAR(255)                            AS okato,
                TO_TIMESTAMP((XPATH('/PHARMACY/DateBegin/text()', x))[1]::TEXT, 'DD.MM.YYYY')    AS datebegin,
                TO_TIMESTAMP((XPATH('/PHARMACY/DateEnd/text()', x))[1]::TEXT, 'DD.MM.YYYY')      AS dateend,
                TO_TIMESTAMP((XPATH('/PHARMACY/RegisterDate/text()', x))[1]::TEXT, 'DD.MM.YYYY') AS registerdate,
                (XPATH('/PHARMACY/RecipeServerTypes/text()', x))[1]::VARCHAR(10)::INT            AS recipeservedtypes,
                (XPATH('/PHARMACY/Address/text()', x))[1]::VARCHAR(255)                          AS address,
                (XPATH('/PHARMACY/Rajon/text()', x))[1]::VARCHAR(13)                             AS rajon,
                (XPATH('/PHARMACY/Number/text()', x))[1]::VARCHAR(50)                            AS number,
                (XPATH('/PHARMACY/Phone/text()', x))[1]::VARCHAR(40)                             AS phone,
                (XPATH('/PHARMACY/LicenseNumber/text()', x))[1]::VARCHAR(40)                     AS licensenumber,
                (XPATH('/PHARMACY/LicenseIssue/text()', x))[1]::VARCHAR(40)                      AS licenseissue,
                TO_TIMESTAMP((XPATH('/PHARMACY/LicenseDate/text()', x))[1]::TEXT, 'DD.MM.YYYY')  AS licensedate,
                (XPATH('/PHARMACY/Dlo/text()', x))[1]::VARCHAR(10)::INT                          AS dlo,
                (XPATH('/PHARMACY/FiasAddress/text()', x))[1]::VARCHAR(255)                      AS fiasaddress,
                (XPATH('/PHARMACY/FiasHome/text()', x))[1]::VARCHAR(255)                         AS fiashome,
                (XPATH('/PHARMACY/Mail/text()', x))[1]::VARCHAR(200)                             AS mail,
                (XPATH('/PHARMACY/EhrRusBr9ap1s2qb/text()', x))[1]::VARCHAR(52)                  AS ehrrusbr9ap1s2qb,
                (XPATH('/PHARMACY/EhrRusBr9ap1s2qbVer/text()', x))[1]::VARCHAR(8)                AS ehrrusbr9ap1s2qbver,
                (XPATH('/PHARMACY/PostalCode/text()', x))[1]::VARCHAR(10)                        AS zipcode,
                (XPATH('/PHARMACY/EhrRusMdr308/text()', x))[1]::VARCHAR(10)::INT                 AS ehrrusmdr308,
                (XPATH('/PHARMACY/EhrRusMdr308Ver/text()', x))[1]::VARCHAR(8)                    AS ehrrusmdr308ver
            FROM UNNEST(XPATH('PHARMACY/body/PHARMACYS/PHARMACY', p_xml)) x;

        ELSE
            INSERT INTO
                temp_pharmacy (id,
                               name,
                               namefull,
                               ogrn,
                               terr,
                               okato,
                               datebegin,
                               dateend,
                               registerdate,
                               recipeservedtypes,
                               address,
                               rajon,
                               number,
                               phone,
                               licensenumber,
                               licenseissue,
                               licensedate,
                               dlo,
                               fiasaddress,
                               fiashome,
                               mail,
                               ehrrusbr9ap1s2qb,
                               ehrrusbr9ap1s2qbver,
                               zipcode,
                               ehrrusmdr308,
                               ehrrusmdr308ver)
            SELECT
                (XPATH('/body/ID/text()', x))[1]::VARCHAR(255)                               AS id,
                (XPATH('/body/Name/text()', x))[1]::VARCHAR(255)                             AS name,
                (XPATH('/body/NameFull/text()', x))[1]::VARCHAR(255)                         AS namefull,
                (XPATH('/body/OGRN/text()', x))[1]::VARCHAR(255)                             AS ogrn,
                (XPATH('/body/Terr/text()', x))[1]::VARCHAR(255)                             AS terr,
                (XPATH('/body/OKATO/text()', x))[1]::VARCHAR(255)                            AS okato,
                TO_TIMESTAMP((XPATH('/body/DateBegin/text()', x))[1]::TEXT, 'DD.MM.YYYY')    AS datebegin,
                TO_TIMESTAMP((XPATH('/body/DateEnd/text()', x))[1]::TEXT, 'DD.MM.YYYY')      AS dateend,
                TO_TIMESTAMP((XPATH('/body/RegisterDate/text()', x))[1]::TEXT, 'DD.MM.YYYY') AS registerdate,
                (XPATH('/body/RecipeServerTypes/text()', x))[1]::VARCHAR(10)::INT            AS recipeservedtypes,
                (XPATH('/body/Address/text()', x))[1]::VARCHAR(255)                          AS address,
                (XPATH('/body/Rajon/text()', x))[1]::VARCHAR(13)                             AS rajon,
                (XPATH('/body/Number/text()', x))[1]::VARCHAR(50)                            AS number,
                (XPATH('/body/Phone/text()', x))[1]::VARCHAR(40)                             AS phone,
                (XPATH('/body/LicenseNumber/text()', x))[1]::VARCHAR(40)                     AS licensenumber,
                (XPATH('/body/LicenseIssue/text()', x))[1]::VARCHAR(40)                      AS licenseissue,
                TO_TIMESTAMP((XPATH('/body/LicenseDate/text()', x))[1]::TEXT, 'DD.MM.YYYY')  AS licensedate,
                (XPATH('/body/Dlo/text()', x))[1]::VARCHAR(10)::INT                          AS dlo,
                (XPATH('/body/FiasAddress/text()', x))[1]::VARCHAR(255)                      AS fiasaddress,
                (XPATH('/body/FiasHome/text()', x))[1]::VARCHAR(255)                         AS fiashome,
                (XPATH('/body/Mail/text()', x))[1]::VARCHAR(200)                             AS mail,
                (XPATH('/body/EhrRusBr9ap1s2qb/text()', x))[1]::VARCHAR(52)                  AS ehrrusbr9ap1s2qb,
                (XPATH('/body/EhrRusBr9ap1s2qbVer/text()', x))[1]::VARCHAR(8)                AS ehrrusbr9ap1s2qbver,
                (XPATH('/body/PostalCode/text()', x))[1]::VARCHAR(10)                        AS zipcode,
                (XPATH('/body/EhrRusMdr308/text()', x))[1]::VARCHAR(10)::INT                 AS ehrrusmdr308,
                (XPATH('/body/EhrRusMdr308Ver/text()', x))[1]::VARCHAR(8)                    AS ehrrusmdr308ver
            FROM UNNEST(XPATH('PHARMACY/body', p_xml)) x;

        END IF;

        IF (v_operationtype = c_ftload) THEN

            UPDATE rds.t_pharmacy p
            SET dateend      = p_datetime,
                registerdate = p_datetime
            WHERE NOT EXISTS (SELECT 1 FROM temp_pharmacy np WHERE np.id = p.code);

            UPDATE rds.t_pharmacy p2
            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 rds.t_rajon r WHERE r.code = np.rajon LIMIT 1),
                number            = np.number,
                phone             = np.phone,
                licensenumber     = np.licensenumber,
                licenseissue      = np.licenseissue,
                licensedate       = np.licensedate,
                dlo               = COALESCE(np.dlo, 0),
                fiasaddress       = np.fiasaddress,
                fiashome          = np.fiashome,
                mail              = np.mail,
                ehrrusbr9ap1s2qb  = e1.ehrrusid,
                zipcode           = np.zipcode,
                ehrrusmdr308      = e.ehrrusid
            FROM rds.t_pharmacy     p
            JOIN      temp_pharmacy np ON np.id = p.code
            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
                          LIMIT 1)  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
                                LIMIT 1
                          )         e1 ON TRUE
            WHERE p.code = p2.code;

            INSERT INTO
                rds.t_pharmacy (code,
                                name,
                                namefull,
                                ogrn,
                                terr,
                                okato,
                                datebegin,
                                dateend,
                                registerdate,
                                recipeservedtypes,
                                address,
                                rajon,
                                number,
                                usepharmacyproduct,
                                phone,
                                licensenumber,
                                licenseissue,
                                licensedate,
                                dlo,
                                fiasaddress,
                                fiashome,
                                mail,
                                ehrrusbr9ap1s2qb,
                                zipcode,
                                ehrrusmdr308)
            SELECT np.name,
                   np.namefull,
                   np.ogrn,
                   np.terr,
                   np.okato,
                   np.datebegin,
                   np.dateend,
                   np.registerdate,
                   np.recipeservedtypes,
                   np.address,
                   (SELECT r.rajonid FROM rds.t_rajon r WHERE r.code = np.rajon LIMIT 1),
                   np.number,
                   0,
                   np.phone,
                   np.licensenumber,
                   np.licenseissue,
                   np.licensedate,
                   COALESCE(np.dlo, 0),
                   np.fiasaddress,
                   np.fiashome,
                   np.mail,
                   e1.ehrrusid,
                   np.zipcode,
                   e.ehrrusid
            FROM temp_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
                               LIMIT 1) 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
                               LIMIT 1) e1 ON TRUE
            WHERE NOT EXISTS (SELECT 1
                              FROM rds.t_pharmacy p
                              WHERE p.code = np.id
                             );

        ELSIF (v_operationtype = c_delete) THEN

            UPDATE rds.t_pharmacy p
            SET dateend      = p_datetime,
                registerdate = p_datetime
            FROM temp_pharmacy np
            WHERE np.id = p.code;

        ELSE

            UPDATE rds.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
            FROM temp_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
                               LIMIT 1) 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
                               LIMIT 1) e1 ON TRUE
            WHERE np.id = p.code
              AND v_operationtype = c_update;

            INSERT INTO
                rds.t_pharmacy(code,
                               name,
                               namefull,
                               ogrn,
                               terr,
                               okato,
                               datebegin,
                               dateend,
                               registerdate,
                               recipeservedtypes,
                               address,
                               rajon,
                               number,
                               usepharmacyproduct,
                               phone,
                               licensenumber,
                               licenseissue,
                               licensedate,
                               dlo,
                               fiasaddress,
                               fiashome,
                               mail,
                               ehrrusbr9ap1s2qb,
                               zipcode,
                               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 rds.t_rajon r WHERE r.code = np.rajon LIMIT 1),
                   np.number,
                   0,
                   phone,
                   np.licensenumber,
                   np.licenseissue,
                   np.licensedate,
                   COALESCE(dlo, 0),
                   np.fiasaddress,
                   np.fiashome,
                   np.mail,
                   e1.ehrrusid,
                   np.zipcode,
                   e.ehrrusid
            FROM temp_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
                                LIMIT 1) 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
                                LIMIT 1) e1 ON TRUE
            WHERE v_operationtype = c_insert
              AND NOT EXISTS (SELECT 1 FROM rds.t_pharmacy p WHERE p.code = np.id);

        END IF;

    END IF;
END;
$$