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 XMLELEMENT(
                       NAME "PHARMACY",
                       XMLFOREST(
                           XMLELEMENT(
                               NAME "header",
                               XMLFOREST(c_ftload AS "TYPE")
                           ),
                           XMLELEMENT(
                               NAME "body",
                               XMLELEMENT(
                                   NAME "PHARMACYS",
                                   (SELECT XMLAGG(
                                               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(p_datetime, 'YYYY.MM.DD') 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, 'YYYY.MM.DD') 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
                                   )
                               )
                           )
                       )
                   )::text
            INTO v_result;
        
        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, 'YYYY.MM.DD') AS "DateBegin",
                                           TO_CHAR(p.dateend, 'YYYY.MM.DD') AS "DateEnd",
                                           TO_CHAR(p_datetime, 'YYYY.MM.DD') 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, 'YYYY.MM.DD') 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;
            
            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));
        END IF;
    ELSE
        
        SELECT (XPATH('/TYPE/text()', x))[1]::varchar(36)::uuid AS type
        FROM UNNEST(XPATH('PHARMACY/header', p_xml)) x;
        
        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,
                   (XPATH('/PHARMACY/DateBegin/text()', x))[1]::text::timestamp          AS datebegin,
                   (XPATH('/PHARMACY/DateEnd/text()', x))[1]::text::timestamp            AS dateend,
                   (XPATH('/PHARMACY/RegisterDate/text()', x))[1]::text::timestamp       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,
                   (XPATH('/PHARMACY/LicenseDate/text()', x))[1]::text::timestamp        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,
                   (XPATH('/body/DateBegin/text()', x))[1]::text::timestamp          AS datebegin,
                   (XPATH('/body/DateEnd/text()', x))[1]::text::timestamp            AS dateend,
                   (XPATH('/body/RegisterDate/text()', x))[1]::text::timestamp       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,
                   (XPATH('/body/LicenseDate/text()', x))[1]::text::timestamp        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;
$$