CREATE OR REPLACE PROCEDURE public.sp_updatebenefitstords(
    xml xml,
    isnew boolean DEFAULT TRUE,
    id uuid DEFAULT NULL,
    queuename varchar(100) DEFAULT NULL,
    datetime timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml       xml          := xml;
    p_isnew     boolean      := isnew;
    p_id        uuid         := id;
    p_queuename varchar(100) := queuename;
    p_datetime  timestamp    := datetime;
    p_user      uuid         := "User";
    p_parsedxml text;

BEGIN
    
    SELECT XMLELEMENT(
               NAME "BENEFITS",
               (SELECT XMLAGG
                       (
                           XMLELEMENT(
                               NAME "BENEFIT",
                               XMLFOREST(
                                   XMLELEMENT(NAME "BenefitsID", benefitsid),
                                   XMLELEMENT(NAME "BenefitCode", benefitcode),
                                   XMLELEMENT(NAME "BenefitName", benefitname),
                                   XMLELEMENT(NAME "Actual", actual),
                                   XMLELEMENT(NAME "PercentPay", percentpay),
                                   XMLELEMENT(NAME "Private", private),
                                   XMLELEMENT(NAME "PeriodOfAct", periodofact),
                                   XMLELEMENT(NAME "Duration", duration),
                                   XMLELEMENT(NAME "BenefitViews", vte.code),
                                   XMLELEMENT(NAME "BenefitType", vte2.code),
                                   XMLELEMENT(NAME "CloseType", closetype),
                                   XMLELEMENT(NAME "EHRRUS1U0GD15H2K", eter1.codeehr),
                                   XMLELEMENT(NAME "EHRRUS1U0GD15H2KVer", rrb.currversion),
                                   XMLELEMENT(NAME "EHRRUSSDO8U3N5RO", eters.codeehr),
                                   XMLELEMENT(NAME "EHRRUSSDO8U3N5ROVer", rrb1.currversion),
                                   XMLELEMENT(NAME "EhrRusVzn438", eter2.idehr),
                                   XMLELEMENT(NAME "EhrRusVzn438Ver", rrb2.currversion),
                                   XMLELEMENT(
                                       NAME "SICKNESS",
                                       (SELECT XMLAGG
                                               (
                                                   XMLELEMENT(
                                                       NAME "SICKNESS_GROUP",
                                                       XMLFOREST
                                                       (
                                                           XMLELEMENT(NAME "MKBK", mkbk),
                                                           XMLELEMENT(NAME "MKBN", mkbn),
                                                           XMLELEMENT(NAME "SicknessGroupID", sicknessgroupid),
                                                           XMLELEMENT(NAME "Benefits", benefits)
                                                       )
                                                   )
                                               )
                                        FROM rds.t_sickness_group atsg
                                        WHERE atsg.benefits = atb.benefitsid
                                       )
                                   )
                               )
                           )
                       )
                FROM rds.t_benefits atb
                JOIN vclib.t_enums vte ON vte.enumid = atb.benefitviews
                JOIN vclib.t_enums vte2 ON vte2.enumid = atb.benefittype
                LEFT JOIN ehr.t_ehr_rus_1u0gd15h2k eter1 ON eter1.ehrrusid = atb.ehrrus1u0gd15h2k
                LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eter1.ehrrusrefbooks
                LEFT JOIN ehr.t_ehr_rus_sdo8u3n5ro eters ON eters.ehrrusid = atb.ehrrussdo8u3n5ro
                LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = eters.ehrrusrefbooks
                LEFT JOIN ehr.t_ehr_rus_vzn438 eter2 ON eter2.ehrrusid = atb.ehrrusvzn438
                LEFT JOIN ehr.t_ehr_rus_ref_books rrb2 ON rrb2.ehrrusrefbooksid = eter2.ehrrusrefbooks
               )
           )::text
    INTO p_parsedxml;
    
    INSERT INTO
        rds.t_rabbit_send_object(rabbitsendobjectid,
                                 queuename,
                                 content,
                                 messageid,
                                 headers,
                                 contentencoding,
                                 type,
                                 userid,
                                 state,
                                 createdatetime,
                                 ownerid)
    SELECT gen_random_uuid(),
           public.converttexttobinaryzip(p_parsedxml, ''),
           NULL,
           NULL,
           'utf-8',
           'BENEFITS',
           NULL,
           0,
           CURRENT_TIMESTAMP,
           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;
$$