CREATE OR REPLACE PROCEDURE public.sp_rr_patientbenefits(
    xml xml DEFAULT NULL,
    id uuid DEFAULT NULL,
    queuename varchar(100) DEFAULT 'RDSSync',
    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_operationtype   varchar(255);
    v_contentencoding varchar(255);
    v_type            varchar(255) := 'RR_PATIENT_BENEFIT';
    v_ownerid         uuid ;
    c_ftload          varchar(6)   := 'FTLoad';

BEGIN
    
    IF (p_xml IS NULL) THEN
        
        IF (p_id IS NULL) THEN
            v_operationtype := c_ftload;
        END IF;
        
        IF (v_operationtype = c_ftload) THEN
            
            SELECT XMLELEMENT
                   (
                       NAME "RR_PATIENT_BENEFITS",
                       XMLFOREST
                       (
                           XMLELEMENT
                           (
                               NAME "header",
                               XMLELEMENT(NAME "TYPE", c_ftload)
                           ),
                           XMLELEMENT
                           (
                               NAME "body",
                               XMLELEMENT
                               (
                                   NAME "RR_PATIENT_BENEFITS",
                                   (SELECT XMLELEMENT
                                           (
                                               NAME "RR_PATIENT_BENEFIT",
                                               XMLFOREST
                                               (
                                                   XMLELEMENT(NAME "RrPatient", rrp.rrpatientid),
                                                   XMLELEMENT(NAME "DiagDate", TO_CHAR(rpb.diagdate, 'YYYY.MM.DD')),
                                                   XMLELEMENT(NAME "Document", rpb.document),
                                                   XMLELEMENT(NAME "DocumentDate",
                                                              TO_CHAR(rpb.documentdate, 'YYYY.MM.DD')),
                                                   XMLELEMENT(NAME "Dop", rpb.dop),
                                                   XMLELEMENT(NAME "Mkb", rpb.mkb),
                                                   XMLELEMENT(NAME "BeginDate", TO_CHAR(rpb.begindate, 'YYYY.MM.DD')),
                                                   XMLELEMENT(NAME "EndDate", TO_CHAR(rpb.enddate, 'YYYY.MM.DD')),
                                                   XMLELEMENT(NAME "BenefitCode", b.benefitcode),
                                                   XMLELEMENT(NAME "BenefitViews", ebv.code),
                                                   XMLELEMENT(NAME "Lpu", rpb.lasteditlpu),
                                                   XMLELEMENT(NAME "LastEditDate",
                                                              TO_CHAR(rpb.lasteditdate, 'YYYY.MM.DD')),
                                                   XMLELEMENT(NAME "Post", rpb.post),
                                                   XMLELEMENT(NAME "DivisionManager", rpb.divisionmanager)
                                               )
                                           )
                                    FROM rds.t_rr_patient_benefit rpb
                                    JOIN rds.t_rr_patient rrp ON rrp.id = rpb.rrpatient
                                    JOIN rds.t_benefits b ON b.benefitsid = rpb.benefits
                                    JOIN vclib.t_enums ebv ON ebv.enumid = b.benefitviews
                                   )
                               )
                           )
                       )
                   )::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(),
                   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 (p_queuename IS NOT NULL AND s.servername = p_queuename)
               OR (p_queuename IS NULL AND s.servertype IN (0, 1));
        
        END IF;
        
    END IF;

END;
$$
