DROP PROCEDURE IF EXISTS sp_palliativedirection;

CREATE OR REPLACE PROCEDURE public.sp_palliativedirection(
    "Xml" text DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT 'RDSSync',
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml"::xml;
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    v_operationtype   varchar(6) ;
    p_user            uuid         := "User";
    v_result          text;
    v_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'PALLIATIVE_DIRECTION';
    v_ownerid         uuid;
    v_registerid      int ;
BEGIN

    SELECT (XPATH('/PALLIATIVE_DIRECTION/RegisterID/text()', x))[1]::varchar(10)::int AS registerid
    INTO v_registerid
    FROM UNNEST(XPATH('/PALLIATIVE_DIRECTION', p_xml)) x;

    DROP TABLE IF EXISTS temp_palliative_direction;
    CREATE TEMP TABLE temp_palliative_direction AS
    SELECT
        (XPATH('/PALLIATIVE_DIRECTION/ID/text()', x))[1]::VARCHAR(36)::uuid                                       AS id,
        (XPATH('/PALLIATIVE_DIRECTION/LPU/text()', x))[1]::VARCHAR(6)                                             AS lpu,
        TO_TIMESTAMP((XPATH('/PALLIATIVE_DIRECTION/EnterDate/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                                AS enterdate,
        (XPATH('/PALLIATIVE_DIRECTION/LastName/text()', x))[1]::VARCHAR(40)                                       AS lastname,
        (XPATH('/PALLIATIVE_DIRECTION/FirstName/text()', x))[1]::VARCHAR(40)                                      AS firstname,
        (XPATH('/PALLIATIVE_DIRECTION/FatherName/text()', x))[1]::VARCHAR(40)                                     AS fathername,
        TO_TIMESTAMP((XPATH('/PALLIATIVE_DIRECTION/Birthday/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                                AS birthday,
        TO_TIMESTAMP((XPATH('/PALLIATIVE_DIRECTION/Date/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                                AS date,
        (XPATH('/PALLIATIVE_DIRECTION/Sex/text()', x))[1]::VARCHAR(10)::INT                                       AS sex,
        (XPATH('/PALLIATIVE_DIRECTION/Kod/text()', x))[1]::VARCHAR(32)                                            AS kod,
        (XPATH('/PALLIATIVE_DIRECTION/DocSeries/text()', x))[1]::VARCHAR(16)                                      AS docseries,
        (XPATH('/PALLIATIVE_DIRECTION/DocNumber/text()', x))[1]::VARCHAR(16)                                      AS docnumber,
        (XPATH('/PALLIATIVE_DIRECTION/DocType/text()', x))[1]::VARCHAR(10)::INT                                   AS doctype,
        (XPATH('/PALLIATIVE_DIRECTION/RegisterID/text()', x))[1]::VARCHAR(10)::INT                                AS registerid,
        (XPATH('/PALLIATIVE_DIRECTION/SNILS/text()', x))[1]::VARCHAR(14)                                          AS snils,
        (XPATH('/PALLIATIVE_DIRECTION/ChangeType/text()', x))[1]::VARCHAR(10)::INT                                AS changetype,
        (XPATH('/PALLIATIVE_DIRECTION/ExcludeReason/text()', x))[1]::VARCHAR(10)::INT                             AS excludereason,
        (XPATH('/PALLIATIVE_DIRECTION/BirthSurname/text()', x))[1]::VARCHAR(40)                                   AS birthsurname,
        TO_TIMESTAMP((XPATH('/PALLIATIVE_DIRECTION/DocIssueDate/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                                AS docissuedate,
        (XPATH('/PALLIATIVE_DIRECTION/Enp/text()', x))[1]::VARCHAR(16)                                            AS enp,
        (XPATH('/PALLIATIVE_DIRECTION/DocIssueOrg/text()', x))[1]::VARCHAR(256)                                   AS docissueorg,
        (XPATH('/PALLIATIVE_DIRECTION/RealFiasAddress/text()', x))[1]::VARCHAR(36)                                AS realfiasaddress,
        (XPATH('/PALLIATIVE_DIRECTION/RealFiasHome/text()', x))[1]::VARCHAR(36)                                   AS realfiashome,
        (XPATH('/PALLIATIVE_DIRECTION/RealCity/text()', x))[1]::VARCHAR(100)                                      AS realcity,
        (XPATH('/PALLIATIVE_DIRECTION/RealCityPrefix/text()', x))[1]::VARCHAR(10)                                 AS realcityprefix,
        (XPATH('/PALLIATIVE_DIRECTION/RealStreet/text()', x))[1]::VARCHAR(100)                                    AS realstreet,
        (XPATH('/PALLIATIVE_DIRECTION/RealStreetPrefix/text()', x))[1]::VARCHAR(10)                               AS realstreetprefix,
        (XPATH('/PALLIATIVE_DIRECTION/RealHouse/text()', x))[1]::VARCHAR(100)                                     AS realhouse,
        (XPATH('/PALLIATIVE_DIRECTION/RealBuild/text()', x))[1]::VARCHAR(100)                                     AS realbuild,
        (XPATH('/PALLIATIVE_DIRECTION/RealStruc/text()', x))[1]::VARCHAR(100)                                     AS realstruc,
        (XPATH('/PALLIATIVE_DIRECTION/RealRoom/text()', x))[1]::VARCHAR(100)                                      AS realroom,
        TO_TIMESTAMP((XPATH('/PALLIATIVE_DIRECTION/RealLiveStartDate/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                                AS reallivestartdate,
        TO_TIMESTAMP((XPATH('/PALLIATIVE_DIRECTION/RealLiveEndDate/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                                AS realliveenddate,
        (XPATH('/PALLIATIVE_DIRECTION/LegalFiasAddress/text()', x))[1]::VARCHAR(36)                               AS legalfiasaddress,
        (XPATH('/PALLIATIVE_DIRECTION/LegalFiasHome/text()', x))[1]::VARCHAR(36)                                  AS legalfiashome,
        (XPATH('/PALLIATIVE_DIRECTION/LegalCity/text()', x))[1]::VARCHAR(100)                                     AS legalcity,
        (XPATH('/PALLIATIVE_DIRECTION/LegalCityPrefix/text()', x))[1]::VARCHAR(10)                                AS legalcityprefix,
        (XPATH('/PALLIATIVE_DIRECTION/LegalStreet/text()', x))[1]::VARCHAR(100)                                   AS legalstreet,
        (XPATH('/PALLIATIVE_DIRECTION/LegalStreetPrefix/text()', x))[1]::VARCHAR(10)                              AS legalstreetprefix,
        (XPATH('/PALLIATIVE_DIRECTION/LegalHouse/text()', x))[1]::VARCHAR(100)                                    AS legalhouse,
        (XPATH('/PALLIATIVE_DIRECTION/LegalBuild/text()', x))[1]::VARCHAR(100)                                    AS legalbuild,
        (XPATH('/PALLIATIVE_DIRECTION/LegalStruc/text()', x))[1]::VARCHAR(100)                                    AS legalstruc,
        (XPATH('/PALLIATIVE_DIRECTION/LegalRoom/text()', x))[1]::VARCHAR(100)                                     AS legalroom,
        TO_TIMESTAMP((XPATH('/PALLIATIVE_DIRECTION/LegalLiveStartDate/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                                AS legallivestartdate,
        TO_TIMESTAMP((XPATH('/PALLIATIVE_DIRECTION/LegalLiveEndDate/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                                AS legalliveenddate,
        (XPATH('/PALLIATIVE_DIRECTION/CitizenShip/text()', x))[1]::VARCHAR(3)                                     AS citizenship
    FROM UNNEST(XPATH('/PALLIATIVE_DIRECTION', p_xml)) x;

    DROP TABLE IF EXISTS temp_palliative_benefits;
    CREATE TEMP TABLE temp_palliative_benefits AS
    SELECT
        (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/DirectionID/text()', x))[1]::VARCHAR(36)::uuid                        AS directionid,
        TO_TIMESTAMP((XPATH('/PALLIATIVE_DIRECTION/PALLIATIVE_DIRECTION_BENEFIT/DiagDate/text()',
                            x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                                  AS diagdate,
        (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/Document/text()', x))[1]::VARCHAR(16)                                 AS document,
        TO_TIMESTAMP((XPATH('/PALLIATIVE_DIRECTION_BENEFIT/DocumentDate/text()', x))[1]::VARCHAR(10),
                     'DD.MM.YYYY')                                                                                  AS documentdate,
        (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/Mkb/text()', x))[1]::VARCHAR(8)                                       AS mkb,
        (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/BenefitCode/text()', x))[1]::VARCHAR(255)                             AS benefitcode,
        (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/BenefitViews/text()', x))[1]::VARCHAR(10)::INT                        AS benefitviews,
        (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/PostCode/text()', x))[1]::VARCHAR(255)                                AS postcode,
        (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/ChangeType/text()', x))[1]::VARCHAR(10)::INT                          AS changetype
    FROM UNNEST(XPATH('/PALLIATIVE_DIRECTION/PALLIATIVE_DIRECTION_BENEFIT', p_xml)) x;

    IF (v_registerid IS NULL) THEN
        CALL public.sp_generateidforentity('PALLIATIVE_REGISTER', 'PrimaryKey', 1, v_registerid);

        UPDATE temp_palliative_direction
        SET registerid = v_registerid;

        IF (v_registerid = 0) THEN
            RAISE EXCEPTION 'Не верно сгенерирован идентификатор льготника в регистре';
            RETURN;
        END IF;

    END IF;

    UPDATE rds.t_palliative_register rp
    SET lastname           = p.lastname,
        firstname          = p.firstname,
        fathername         = p.fathername,
        birthday           = p.birthday,
        kod                = p.kod,
        docseries          = p.docseries,
        docnumber          = p.docnumber,
        sex                = esex.enumid,
        snils              = p.snils,
        doctype            = edoc.enumid,
        lpumain            = p.lpu,
        lasteditdate       = p_datetime,
        docissuedate       = p.docissuedate,
        docissueorg        = p.docissueorg,
        enp                = p.enp,
        realfiasaddress    = p.realfiasaddress,
        realfiashome       = p.realfiashome,
        realcity           = p.realcity,
        realcityprefix     = p.realcityprefix,
        realstreet         = p.realstreet,
        realstreetprefix   = p.realstreetprefix,
        realhouse          = p.realhouse,
        realbuild          = p.realbuild,
        realstruc          = p.realstruc,
        realroom           = p.realroom,
        reallivestartdate  = p.reallivestartdate,
        realliveenddate    = p.realliveenddate,
        legalfiasaddress   = p.legalfiasaddress,
        legalfiashome      = p.legalfiashome,
        legalcity          = p.legalcity,
        legalcityprefix    = p.legalcityprefix,
        legalstreet        = p.legalstreet,
        legalstreetprefix  = p.legalstreetprefix,
        legalhouse         = p.legalhouse,
        legalbuild         = p.legalbuild,
        legalstruc         = p.legalstruc,
        legalroom          = p.legalroom,
        legallivestartdate = p.legallivestartdate,
        legalliveenddate   = p.legalliveenddate,
        citizenship        = cs.ehrrusid,
        ehrrus5o5e88vhc6   = dul.ehrrus5o5e88vhc6
    FROM temp_palliative_direction        p
    JOIN      vclib.t_enums               esex ON esex.enumtypeid = 103 AND esex.code = p.sex
    JOIN      vclib.t_enums               edoc ON edoc.enumtypeid = 105 AND edoc.code = p.doctype
    LEFT JOIN ehr.t_ehr_rus_o00015        cs ON cs.code = p.citizenship
    LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = edoc.enumid
    WHERE p.changetype = 2
      AND p.registerid = rp.pac_id;


    UPDATE rds.t_palliative_register rp
    SET lasteditdate  = p_datetime,
        dateend       = p.enterdate,
        excludereason = p.excludereason
    FROM temp_palliative_direction p
    WHERE p.changetype = 3
      AND p.registerid = rp.pac_id;

    UPDATE rds.t_palliative_register_data brd
    SET enddate      = p_datetime,
        lasteditdate = p_datetime
    FROM rds.t_palliative_register rp
    JOIN temp_palliative_direction p ON p.registerid = rp.pac_id
    WHERE brd.palliativeregister = rp.palliativeregisterid
      AND p.changetype = 3
      AND (brd.enddate IS NULL OR brd.enddate > p_datetime);

    INSERT INTO
        rds.t_palliative_register (palliativeregisterid,
                                   excludereason,
                                   lasteditdate,
                                   lastname,
                                   firstname,
                                   fathername,
                                   birthday,
                                   kod,
                                   docseries,
                                   docnumber,
                                   pac_id,
                                   sex,
                                   snils,
                                   doctype,
                                   lpumain,
                                   datebegin,
                                   dateend,
                                   docissuedate,
                                   docissueorg,
                                   enp,
                                   realfiasaddress,
                                   realfiashome,
                                   realcity,
                                   realcityprefix,
                                   realstreet,
                                   realstreetprefix,
                                   realhouse,
                                   realbuild,
                                   realstruc,
                                   realroom,
                                   reallivestartdate,
                                   realliveenddate,
                                   legalfiasaddress,
                                   legalfiashome,
                                   legalcity,
                                   legalcityprefix,
                                   legalstreet,
                                   legalstreetprefix,
                                   legalhouse,
                                   legalbuild,
                                   legalstruc,
                                   legalroom,
                                   legallivestartdate,
                                   legalliveenddate,
                                   citizenship,
                                   ehrrus5o5e88vhc6)
    SELECT gen_random_uuid(),
           d.excludereason,
           p_datetime,
           d.lastname,
           d.firstname,
           d.fathername,
           d.birthday,
           d.kod,
           d.docseries,
           d.docnumber,
           d.registerid,
           esex.enumid,
           d.snils,
           edoc.enumid,
           d.lpu,
           p_datetime,
           NULL,
           d.docissuedate,
           d.docissueorg,
           d.enp,
           d.realfiasaddress,
           d.realfiashome,
           d.realcity,
           d.realcityprefix,
           d.realstreet,
           d.realstreetprefix,
           d.realhouse,
           d.realbuild,
           d.realstruc,
           d.realroom,
           d.reallivestartdate,
           d.realliveenddate,
           d.legalfiasaddress,
           d.legalfiashome,
           d.legalcity,
           d.legalcityprefix,
           d.legalstreet,
           d.legalstreetprefix,
           d.legalhouse,
           d.legalbuild,
           d.legalstruc,
           d.legalroom,
           d.legallivestartdate,
           d.legalliveenddate,
           cs.ehrrusid,
           dul.ehrrus5o5e88vhc6
    FROM temp_palliative_direction        d
    JOIN      vclib.t_enums               esex ON esex.enumtypeid = 103 AND esex.code = d.sex
    JOIN      vclib.t_enums               edoc ON edoc.enumtypeid = 105 AND edoc.code = d.doctype
    LEFT JOIN ehr.t_ehr_rus_o00015        cs ON cs.code = d.citizenship
    LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = edoc.enumid
    WHERE d.changetype = 1;

    INSERT INTO
        rds.t_palliative_register_data (palliativeregisterdataid,
                                        palliativeregister,
                                        diagdate,
                                        document,
                                        documentdate,
                                        mkb,
                                        benefits,
                                        begindate,
                                        lasteditdate,
                                        enddate)
    SELECT gen_random_uuid(),
           r.palliativeregisterid,
           bd.diagdate,
           bd.document,
           bd.documentdate,
           bd.mkb,
           b.benefitsid,
           p_datetime,
           p_datetime,
           CASE
               WHEN b.duration IS NOT NULL AND b.periodofact IS NOT NULL AND b.closetype IS NOT NULL THEN
                   CASE
                       WHEN b.closetype = 0 AND b.periodofact = 0 THEN d.birthday + INTERVAL '1 day' * b.duration
                       WHEN b.closetype = 0 AND b.periodofact = 1 THEN d.birthday + INTERVAL '1 month' * b.duration
                       WHEN b.closetype = 0 AND b.periodofact = 2 THEN d.birthday + INTERVAL '1 year' * b.duration
                       WHEN b.closetype = 1 AND b.periodofact = 0 THEN d.enterdate + INTERVAL '1 day' * b.duration
                       WHEN b.closetype = 1 AND b.periodofact = 1 THEN d.enterdate + INTERVAL '1 month' * b.duration
                       WHEN b.closetype = 1 AND b.periodofact = 2 THEN d.enterdate + INTERVAL '1 year' * b.duration
                       ELSE NULL
                   END
               ELSE NULL
           END AS enddate
    FROM temp_palliative_benefits       bd
    JOIN      temp_palliative_direction d ON d.id = bd.directionid
    JOIN      rds.t_palliative_register r ON r.pac_id = v_registerid
    LEFT JOIN vclib.t_enums             e ON e.enumtypeid = 190 AND e.code = bd.benefitviews
    JOIN      rds.t_benefits            b ON b.benefitcode = bd.benefitcode AND b.benefitviews = e.enumid
    WHERE bd.changetype = 1;

    UPDATE rds.t_palliative_register_data rpb
    SET enddate      = p_datetime,
        lasteditdate = p_datetime
    FROM temp_palliative_benefits       rdb
    JOIN      rds.t_palliative_register r ON r.pac_id = v_registerid
    LEFT JOIN vclib.t_enums             e ON e.enumtypeid = 190 AND e.code = rdb.benefitviews
    LEFT JOIN rds.t_benefits            b ON b.benefitcode = rdb.benefitcode AND b.benefitviews = e.enumid
    WHERE rpb.palliativeregister = r.palliativeregisterid
      AND rpb.benefits = b.benefitsid
      AND rdb.changetype = 3
      AND (rpb.enddate IS NULL OR rpb.enddate > p_datetime);


    UPDATE rds.t_palliative_register AS rrr
    SET dateend       = t.enddate,
        lasteditdate  = CURRENT_TIMESTAMP,
        excludereason = CASE WHEN t.enddate IS NOT NULL THEN rr.excludereason ELSE NULL END
    FROM rds.t_palliative_register    rr
    JOIN temp_palliative_direction AS d ON d.registerid = rr.pac_id
    JOIN LATERAL (
             SELECT rrb.enddate
             FROM rds.t_palliative_register_data AS rrb
             WHERE rrb.palliativeregister = rr.palliativeregisterid
             ORDER BY CASE WHEN rrb.enddate IS NULL THEN '9999-01-01'::DATE ELSE rrb.enddate END DESC
             LIMIT 1
             )                     AS t ON TRUE
    WHERE rr.palliativeregisterid = rrr.palliativeregisterid;

    SELECT XMLAGG
           (
               XMLELEMENT
               (
                   NAME "PALLIATIVE_REGISTER",
                   XMLFOREST
                   (
                       rd.id AS "ID",
                       rd.registerid AS "RegisterID"
                   )
               )
           )::text
    INTO v_result
    FROM temp_palliative_direction rd;

    SELECT XMLELEMENT
           (
               NAME "PALLIATIVE_REGISTER",
               XMLFOREST
               (
                   XMLELEMENT
                   (
                       NAME "header",
                       XMLFOREST
                       (
                           v_operationtype AS "TYPE"
                       )
                   ),
                   XMLELEMENT
                   (
                       NAME "body",
                       (SELECT XMLAGG
                               (
                                   XMLELEMENT
                                   (
                                       NAME "PATIENT",
                                       XMLFOREST
                                       (
                                           rp.pac_id AS "RegisterID",
                                           rp.lastname AS "LastName",
                                           rp.firstname AS "FirstName",
                                           rp.fathername AS "FatherName",
                                           TO_CHAR(rp.birthday, 'DD.MM.YYYY') AS "Birthday",
                                           rp.kod AS "Kod",
                                           rp.docseries AS "DocSeries",
                                           rp.docnumber AS "DocNumber",
                                           esex.code AS "Sex",
                                           rp.snils AS "Snils",
                                           TO_CHAR(rp.datebegin, 'DD.MM.YYYY') AS "DateBegin",
                                           TO_CHAR(rp.dateend, 'DD.MM.YYYY') AS "DateEnd",
                                           TO_CHAR(rp.lasteditdate, 'DD.MM.YYYY') AS "LastEditDate",
                                           rp.lpumain AS "Lpu",
                                           rp.excludereason AS "ExcludeReason",
                                           edoc.code AS "DocType",
                                           TO_CHAR(rp.docissuedate, 'DD.MM.YYYY') AS "DocIssueDate",
                                           rp.docissueorg AS "DocIssueOrg",
                                           rp.enp AS "Enp",
                                           rp.realfiasaddress AS "RealFiasAddress",
                                           rp.realfiashome AS "RealFiasHome",
                                           rp.realcity AS "RealCity",
                                           rp.realcityprefix AS "RealCityPrefix",
                                           rp.realstreet AS "RealStreet",
                                           rp.realstreetprefix AS "RealStreetPrefix",
                                           rp.realhouse AS "RealHouse",
                                           rp.realbuild AS "RealBuild",
                                           rp.realstruc AS "RealStruc",
                                           rp.realroom AS "RealRoom",
                                           TO_CHAR(rp.reallivestartdate, 'DD.MM.YYYY') AS "RealLiveStartDate",
                                           TO_CHAR(rp.realliveenddate, 'DD.MM.YYYY') AS "RealLiveEndDate",
                                           rp.legalfiasaddress AS "LegalFiasAddress",
                                           rp.legalfiashome AS "LegalFiasHome",
                                           rp.legalcity AS "LegalCity",
                                           rp.legalcityprefix AS "LegalCityPrefix",
                                           rp.legalhouse AS "LegalHouse",
                                           rp.legalbuild AS "LegalBuild",
                                           rp.legalstruc AS "LegalStruc",
                                           rp.legalroom AS "LegalRoom",
                                           TO_CHAR(rp.legallivestartdate, 'DD.MM.YYYY') AS "LegalLiveStartDate",
                                           TO_CHAR(rp.legalliveenddate, 'DD.MM.YYYY') AS "LegalLiveEndDate",
                                           cs.code AS "CitizenShip",
                                           doc.codeehr AS "EhrRus5o5e88vhc6",
                                           rrb.currversion AS "EhrRus5o5e88vhc6Ver",
                                           (SELECT XMLAGG(
                                                       XMLELEMENT
                                                       (
                                                           NAME "BENEFITS",
                                                           XMLFOREST
                                                           (
                                                               rp.pac_id AS "RegisterID",
                                                               TO_CHAR(rpb.diagdate, 'DD.MM.YYYY') AS "DiagDate",
                                                               rpb.document AS "Document",
                                                               TO_CHAR(rpb.documentdate, 'DD.MM.YYYY')  AS "DocumentDate",
                                                               rpb.mkb AS "Mkb",
                                                               b.benefitcode AS "BenefitCode",
                                                               e.code AS "BenefitViews",
                                                               TO_CHAR(rpb.begindate, 'DD.MM.YYYY') AS "BeginDate",
                                                               TO_CHAR(rpb.enddate, 'DD.MM.YYYY') AS "EndDate"
                                                           )
                                                       )
                                                   )
                                            FROM rds.t_palliative_register_data rpb
                                            JOIN rds.t_benefits                 b ON b.benefitsid = rpb.benefits
                                            JOIN vclib.t_enums                  e ON e.enumid = b.benefitviews
                                            WHERE rpb.palliativeregister = rp.palliativeregisterid
                                           )
                                       )
                                   )
                               )

                        FROM rds.t_palliative_register     rp
                        JOIN      vclib.t_enums            esex ON esex.enumid = rp.sex
                        JOIN      vclib.t_enums            edoc ON edoc.enumid = rp.doctype
                        LEFT JOIN ehr.t_ehr_rus_o00015     cs ON cs.ehrrusid = rp.citizenship
                        LEFT JOIN ehr.t_ehr_rus_5o5e88vhc6 doc ON doc.ehrrusid = rp.ehrrus5o5e88vhc6
                        LEFT JOIN ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = doc.ehrrusrefbooks
                       )
                   )
               )
           )::text
    INTO p_xml
    FROM rds.t_palliative_register     rp
    JOIN      vclib.t_enums            esex ON esex.enumid = rp.sex
    JOIN      vclib.t_enums            edoc ON edoc.enumid = rp.doctype
    LEFT JOIN ehr.t_ehr_rus_o00015     cs ON cs.ehrrusid = rp.citizenship
    LEFT JOIN ehr.t_ehr_rus_5o5e88vhc6 doc ON doc.ehrrusid = rp.ehrrus5o5e88vhc6
    LEFT JOIN ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = doc.ehrrusrefbooks
    WHERE rp.pac_id = v_registerid;


    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,
           v_ownerid

    FROM rds.t_sync_servers s
    WHERE s.servername = p_queuename
      AND s.servertype = 0
      AND v_result IS NOT NULL
    UNION ALL
    SELECT gen_random_uuid(),
           s.servername,
           public.converttexttobinaryzip(p_xml::text, ''),
           NULL,
           NULL,
           v_contentencoding,
           v_type,
           p_user,
           0,
           p_datetime,
           v_ownerid
    FROM rds.t_sync_servers s
    WHERE s.servertype = 1;

    UPDATE public.t_entity_ids e
    SET id = (SELECT MAX(pac_id) + 1 FROM rds.t_palliative_register)
    WHERE e.entname IN ('PALLIATIVE_REGISTER', 'RDS.PALLIATIVE_REGISTER');


END;
$$