CREATE OR REPLACE PROCEDURE public.sp_updatebenefitstords(
    xml xml DEFAULT NULL,
    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_queuename       varchar(100) := queuename;
    p_datetime        timestamp    := COALESCE(datetime, CURRENT_TIMESTAMP);
    p_user            uuid         := "user";
    p_id              uuid         := id;
    v_result          text ;
    v_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'BENEFITS';
    

BEGIN
    
    SELECT XMLELEMENT
           (
               NAME "BENEFITS",
               (SELECT 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 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 v_result;
    
    INSERT INTO
        rds.t_rabbit_send_object (rabbitsendobjectid,
                                  queuename,
                                  content,
                                  messageid,
                                  headers,
                                  contentencoding,
                                  type,
                                  userid,
                                  state,
                                  createdatetime,
                                  ownerid)
    SELECT gen_random_uuid(),
           p_queuename,
           public.converttexttobinaryzip(v_result, ''),
           NULL,
           NULL,
           v_contentencoding,
           v_type,
           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;
$$