CREATE OR REPLACE PROCEDURE rds.sp_rds_ftload_dloaccesspost(
    xml xml DEFAULT NULL,
    isnew int DEFAULT 1,
    id uuid DEFAULT NULL,
    queuename character varying DEFAULT NULL,
    datetime timestamp DEFAULT NULL,
    "user" uuid DEFAULT NULL
) AS
$$
DECLARE
    v_result          text;
    v_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'DLO_ACCESSED_POSTS_FTLOAD';
    p_xml             xml          := xml;
    p_isnew           integer      := isnew;
    p_id              uuid         := id;
    p_queuename       varchar(100) := queuename;
    p_datetime        timestamp    := datetime;
    p_user            uuid         := "user";
    c_ftload          varchar(10)  := 'FTLOAD';

BEGIN
    
    p_datetime = COALESCE(p_datetime, CURRENT_TIMESTAMP(0));
    
    SELECT XMLELEMENT
           (
               NAME "DLO_ACCESSED_POSTS",
               XMLFOREST
               (
                   XMLELEMENT
                   (
                       NAME "header",
                       XMLELEMENT(NAME "TYPE", 'FTLOAD')
                   ),
                   XMLELEMENT
                   (
                       NAME "body",
                       XMLELEMENT
                       (
                           NAME "DLO_ACCESSED_POSTS",
                           XMLFOREST(
                               (SELECT XMLAGG
                                       (
                                           XMLELEMENT
                                           (
                                               NAME "DLO_ACCESSED_POST",
                                               XMLFOREST
                                               (
                                                   XMLELEMENT(NAME "ID", p.dloaccessedpostsid),
                                                   XMLELEMENT(NAME "POST_CODE", p.postcode),
                                                   XMLELEMENT(NAME "LAST_NAME", p.lastname),
                                                   XMLELEMENT(NAME "FIRST_NAME", p.firstname),
                                                   XMLELEMENT(NAME "FATHER_NAME", p.fathername),
                                                   XMLELEMENT(NAME "CATEGORY", p.category),
                                                   XMLELEMENT(NAME "BEGIN_ACCESS_DATE",
                                                              TO_CHAR(p.beginaccessdate, 'YYYY-MM-DD')),
                                                   XMLELEMENT(NAME "END_ACCESS_DATE",
                                                              TO_CHAR(p.endaccessdate, 'YYYY-MM-DD')),
                                                   XMLELEMENT(NAME "BEGIN_WORK_DATE",
                                                              TO_CHAR(p.beginworkdate, 'YYYY-MM-DD')),
                                                   XMLELEMENT(NAME "CERTIFICATE_DATE",
                                                              TO_CHAR(p.certificatedate, 'YYYY-MM-DD')),
                                                   XMLELEMENT(NAME "MEDICAL_SPECIALITY_CODE", p.medicalspecialitycode),
                                                   XMLELEMENT(NAME "EXPULSION_DATE",
                                                              TO_CHAR(p.expulsiondate, 'YYYY-MM-DD')),
                                                   XMLELEMENT(NAME "SNILS", p.snils),
                                                   XMLELEMENT(NAME "POST_TYPE", p.posttype),
                                                   XMLELEMENT(NAME "SPECIALITY", p.speciality),
                                                   XMLELEMENT(NAME "LPU_MAIN", p.lpumain),
                                                   XMLELEMENT(NAME "EhrRus7PJSAM6GJM", e1.idehr),
                                                   XMLELEMENT(NAME "EhrRus7PJSAM6GJMVer", rrb1.currversion),
                                                   XMLELEMENT(NAME "EhrRusLS9R0Q4VR4", e2.idehr),
                                                   XMLELEMENT(NAME "EhrRusLS9R0Q4VR4Ver", rrb2.currversion)
                                               )
                                           )
                                       )
                                FROM rds.t_dlo_accessed_posts p
                                LEFT JOIN ehr.t_ehr_rus_7pjsam6gjm e1 ON p.ehrrus7pjsam6gjm = e1.ehrrusid
                                LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = e1.ehrrusrefbooks
                                LEFT JOIN ehr.t_ehr_rus_ls9r0q4vr4 e2 ON p.ehrrusls9r0q4vr4 = e2.ehrrusid
                                LEFT JOIN ehr.t_ehr_rus_ref_books rrb2 ON rrb2.ehrrusrefbooksid = e2.ehrrusrefbooks
                               )
                           )
                       )
                   )
               )
           )::text
    INTO
        v_result;
    
    INSERT INTO
        rds.t_rabbit_send_object
    (rabbitsendobjectid,
     queuename,
     content,
     messageid,
     headers,
     contentencoding,
     type,
     userid,
     state,
     createdatetime,
     ownerid)
    VALUES (gen_random_uuid(),
            p_queuename,
            converttexttobinaryzip(v_result, ''),
            NULL,
            NULL,
            v_contentencoding,
            v_type,
            p_user,
            0,
            p_datetime,
            NULL);
END;
$$
    LANGUAGE plpgsql;