DROP PROCEDURE IF EXISTS sp_palliativedirection;

CREATE OR REPLACE PROCEDURE public.sp_palliativedirection(
    "Xml" text,
    "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()', p_xml))[1]::varchar(10)::int AS registerid
    INTO v_registerid
    FROM UNNEST(XPATH('/PALLIATIVE_DIRECTION', p_xml)) x;
    
    CREATE TEMP TABLE temp_palliative_direction AS
        SELECT (XPATH('/PALLIATIVE_DIRECTION/ID/text()', p_xml))[1]::varchar(36)::uuid                      AS id,
               (XPATH('/PALLIATIVE_DIRECTION/LPU/text()', p_xml))[1]::varchar(6)                            AS lpu,
               (XPATH('/PALLIATIVE_DIRECTION/EnterDate/text()', p_xml))[1]::varchar(10)::timestamp          AS enterdate,
               (XPATH('/PALLIATIVE_DIRECTION/LastName/text()', p_xml))[1]::varchar(40)                      AS lastname,
               (XPATH('/PALLIATIVE_DIRECTION/FirstName/text()', p_xml))[1]::varchar(40)                     AS firstname,
               (XPATH('/PALLIATIVE_DIRECTION/FatherName/text()', p_xml))[1]::varchar(40)                    AS fathername,
               (XPATH('/PALLIATIVE_DIRECTION/Birthday/text()', p_xml))[1]::varchar(10)::timestamp           AS birthday,
               (XPATH('/PALLIATIVE_DIRECTION/Date/text()', p_xml))[1]::varchar(10)::timestamp               AS date,
               (XPATH('/PALLIATIVE_DIRECTION/Sex/text()', p_xml))[1]::varchar(10)::int                      AS sex,
               (XPATH('/PALLIATIVE_DIRECTION/Kod/text()', p_xml))[1]::varchar(32)                           AS kod,
               (XPATH('/PALLIATIVE_DIRECTION/DocSeries/text()', p_xml))[1]::varchar(16)                     AS docseries,
               (XPATH('/PALLIATIVE_DIRECTION/DocNumber/text()', p_xml))[1]::varchar(16)                     AS docnumber,
               (XPATH('/PALLIATIVE_DIRECTION/DocType/text()', p_xml))[1]::varchar(10)::int                  AS doctype,
               (XPATH('/PALLIATIVE_DIRECTION/RegisterID/text()', p_xml))[1]::varchar(10)::int               AS registerid,
               (XPATH('/PALLIATIVE_DIRECTION/SNILS/text()', p_xml))[1]::varchar(14)                         AS snils,
               (XPATH('/PALLIATIVE_DIRECTION/ChangeType/text()', p_xml))[1]::varchar(10)::int               AS changetype,
               (XPATH('/PALLIATIVE_DIRECTION/ExcludeReason/text()', p_xml))[1]::varchar(10)::int            AS excludereason,
               (XPATH('/PALLIATIVE_DIRECTION/BirthSurname/text()', p_xml))[1]::varchar(40)                  AS birthsurname,
               (XPATH('/PALLIATIVE_DIRECTION/DocIssueDate/text()', p_xml))[1]::varchar(10)::timestamp       AS docissuedate,
               (XPATH('/PALLIATIVE_DIRECTION/Enp/text()', p_xml))[1]::varchar(16)                           AS enp,
               (XPATH('/PALLIATIVE_DIRECTION/DocIssueOrg/text()', p_xml))[1]::varchar(256)                  AS docissueorg,
               (XPATH('/PALLIATIVE_DIRECTION/RealFiasAddress/text()', p_xml))[1]::varchar(36)               AS realfiasaddress,
               (XPATH('/PALLIATIVE_DIRECTION/RealFiasHome/text()', p_xml))[1]::varchar(36)                  AS realfiashome,
               (XPATH('/PALLIATIVE_DIRECTION/RealCity/text()', p_xml))[1]::varchar(100)                     AS realcity,
               (XPATH('/PALLIATIVE_DIRECTION/RealCityPrefix/text()', p_xml))[1]::varchar(10)                AS realcityprefix,
               (XPATH('/PALLIATIVE_DIRECTION/RealStreet/text()', p_xml))[1]::varchar(100)                   AS realstreet,
               (XPATH('/PALLIATIVE_DIRECTION/RealStreetPrefix/text()', p_xml))[1]::varchar(10)              AS realstreetprefix,
               (XPATH('/PALLIATIVE_DIRECTION/RealHouse/text()', p_xml))[1]::varchar(100)                    AS realhouse,
               (XPATH('/PALLIATIVE_DIRECTION/RealBuild/text()', p_xml))[1]::varchar(100)                    AS realbuild,
               (XPATH('/PALLIATIVE_DIRECTION/RealStruc/text()', p_xml))[1]::varchar(100)                    AS realstruc,
               (XPATH('/PALLIATIVE_DIRECTION/RealRoom/text()', p_xml))[1]::varchar(100)                     AS realroom,
               (XPATH('/PALLIATIVE_DIRECTION/RealLiveStartDate/text()', p_xml))[1]::varchar(10)::timestamp  AS reallivestartdate,
               (XPATH('/PALLIATIVE_DIRECTION/RealLiveEndDate/text()', p_xml))[1]::varchar(10)::timestamp    AS realliveenddate,
               (XPATH('/PALLIATIVE_DIRECTION/LegalFiasAddress/text()', p_xml))[1]::varchar(36)              AS legalfiasaddress,
               (XPATH('/PALLIATIVE_DIRECTION/LegalFiasHome/text()', p_xml))[1]::varchar(36)                 AS legalfiashome,
               (XPATH('/PALLIATIVE_DIRECTION/LegalCity/text()', p_xml))[1]::varchar(100)                    AS legalcity,
               (XPATH('/PALLIATIVE_DIRECTION/LegalCityPrefix/text()', p_xml))[1]::varchar(10)               AS legalcityprefix,
               (XPATH('/PALLIATIVE_DIRECTION/LegalStreet/text()', p_xml))[1]::varchar(100)                  AS legalstreet,
               (XPATH('/PALLIATIVE_DIRECTION/LegalStreetPrefix/text()', p_xml))[1]::varchar(10)             AS legalstreetprefix,
               (XPATH('/PALLIATIVE_DIRECTION/LegalHouse/text()', p_xml))[1]::varchar(100)                   AS legalhouse,
               (XPATH('/PALLIATIVE_DIRECTION/LegalBuild/text()', p_xml))[1]::varchar(100)                   AS legalbuild,
               (XPATH('/PALLIATIVE_DIRECTION/LegalStruc/text()', p_xml))[1]::varchar(100)                   AS legalstruc,
               (XPATH('/PALLIATIVE_DIRECTION/LegalRoom/text()', p_xml))[1]::varchar(100)                    AS legalroom,
               (XPATH('/PALLIATIVE_DIRECTION/LegalLiveStartDate/text()', p_xml))[1]::varchar(10)::timestamp AS legallivestartdate,
               (XPATH('/PALLIATIVE_DIRECTION/LegalLiveEndDate/text()', p_xml))[1]::varchar(10)::timestamp   AS legalliveenddate,
               (XPATH('/PALLIATIVE_DIRECTION/CitizenShip/text()', p_xml))[1]::varchar(3)                    AS citizenship
        FROM UNNEST(XPATH('/PALLIATIVE_DIRECTION', p_xml)) x;
    
    CREATE TEMP TABLE temp_palliative_benefits AS
        SELECT (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/DirectionID/text()', p_xml))[1]::varchar(36)::uuid       AS directionid,
               (XPATH('/PALLIATIVE_DIRECTION/PALLIATIVE_DIRECTION_BENEFIT/DiagDate/text()',
                      p_xml))[1]::varchar(10)::timestamp                                                      AS diagdate,
               (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/Document/text()', p_xml))[1]::varchar(16)                AS document,
               (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/DocumentDate/text()', p_xml))[1]::varchar(10)::timestamp AS documentdate,
               (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/Mkb/text()', p_xml))[1]::varchar(8)                      AS mkb,
               (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/BenefitCode/text()', p_xml))[1]::varchar(255)            AS benefitcode,
               (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/BenefitViews/text()', p_xml))[1]::varchar(10)::int       AS benefitviews,
               (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/PostCode/text()', p_xml))[1]::varchar(255)               AS postcode,
               (XPATH('/PALLIATIVE_DIRECTION_BENEFIT/ChangeType/text()', p_xml))[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 'Не верно сгенерирован идентификатор льготника в регистре';
        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);
    
    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, 'YYYY.MM.DD') 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, 'YYYY.MM.DD') AS "DateBegin",
                                           TO_CHAR(rp.dateend, 'YYYY.MM.DD') AS "DateEnd",
                                           TO_CHAR(rp.lasteditdate, 'YYYY.MM.DD') AS "LastEditDate",
                                           rp.lpumain AS "Lpu",
                                           rp.excludereason AS "ExcludeReason",
                                           edoc.code AS "DocType",
                                           TO_CHAR(rp.docissuedate, 'YYYY.MM.DD') 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, 'YYYY.MM.DD') AS "RealLiveStartDate",
                                           TO_CHAR(rp.realliveenddate, 'YYYY.MM.DD') 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, 'YYYY.MM.DD') AS "LegalLiveStartDate",
                                           TO_CHAR(rp.legalliveenddate, 'YYYY.MM.DD') 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, 'YYYY.MM.DD') AS "DiagDate",
                                                               rpb.document AS "Document",
                                                               rpb.mkb AS "Mkb",
                                                               b.benefitcode AS "BenefitCode",
                                                               b.benefitviews AS "BenefitViews",
                                                               TO_CHAR(rpb.begindate, 'YYYY.MM.DD') AS "BeginDate",
                                                               TO_CHAR(rpb.enddate, 'YYYY.MM.DD') 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;
$$