CREATE OR REPLACE PROCEDURE public.sp_updatebenefitstords(
	"Xml" text DEFAULT NULL::text,
	"IsNew" boolean DEFAULT true,
	"ID" uuid DEFAULT NULL::uuid,
	"QueueName" character varying DEFAULT 'RDSSync'::character varying,
	"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone,
	"User" uuid DEFAULT NULL::uuid
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             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 XMLAGG
                       (
                           XMLELEMENT
                           (
                               NAME "BENEFIT",
                               XMLFOREST
                               (
                                   benefitsid AS "BenefitsID",
                                   benefitcode AS "BenefitCode",
                                   benefitname AS "BenefitName",
                                   actual AS "Actual",
                                   percentpay AS "PercentPay",
                                   private AS "Private",
                                   periodofact AS "PeriodOfAct",
                                   duration AS "Duration",
                                   vte.code AS "BenefitViews",
                                   vte2.code AS "BenefitType",
                                   closetype AS "CloseType",
                                   eter1.codeehr AS "EHRRUS1U0GD15H2K",
                                   rrb.currversion AS "EHRRUS1U0GD15H2KVer",
                                   eters.codeehr AS "EHRRUSSDO8U3N5RO",
                                   rrb1.currversion AS "EHRRUSSDO8U3N5ROVer",
                                   eter2.idehr AS "EhrRusVzn438",
                                   rrb2.currversion AS "EhrRusVzn438Ver",
                                   (SELECT XMLAGG
                                           (
                                               XMLELEMENT
                                               (
                                                   NAME "SICKNESS_GROUP",
                                                   XMLFOREST
                                                   (
                                                       mkbk AS "MKBK",
                                                       mkbn AS "MKBN",
                                                       sicknessgroupid AS "SicknessGroupID",
                                                       benefits AS "Benefits"
                                                   )
                                               )
                                           )
                                    FROM rds.t_sickness_group atsg
                                    WHERE atsg.benefits = atb.benefitsid
                                   ) AS "SICKNESS"
                               )
                           )
                       )
                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;
$$