DROP PROCEDURE IF EXISTS rds.sp_rds_dloaccesspostdirection;
CREATE OR REPLACE PROCEDURE rds.sp_rds_dloaccesspostdirection(
    "Xml" TEXT DEFAULT NULL,
    "IsNew" BOOLEAN DEFAULT TRUE,
    "ID" uuid DEFAULT NULL,
    "QueueName" CHARACTER VARYING DEFAULT NULL,
    "dateTime" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
    "User" uuid DEFAULT NULL)
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_result             TEXT;
    v_contentencoding    VARCHAR(255) := 'utf-8';
    v_type               VARCHAR(255) := 'DLO_ACCESSED_POSTS_DIRECTION';
    v_operationtype      INTEGER;
    v_insert             INTEGER      := 0;
    v_directionstate     INTEGER;
    v_ds_accept          INTEGER      = 1;
    v_true               VARCHAR(4)   := 'true';
    v_false              VARCHAR(5)   := 'false';
    v_postid             uuid;
    v_postcode           VARCHAR(255);
    v_snils              VARCHAR(14);
    v_speciality         VARCHAR(3);
    v_posttype           VARCHAR(4);
    v_lastname           VARCHAR(40);
    v_firstname          VARCHAR(40);
    v_beginworkdate      TIMESTAMP WITHOUT TIME ZONE;
    v_needconfirmation   VARCHAR(6);
    v_opendirectioncount INTEGER;
    p_xml                xml          := "Xml"::xml;
    p_isnew              BOOLEAN      := "IsNew"::BOOLEAN;
    p_id                 uuid         := "ID";
    p_queuename          VARCHAR(100) := "QueueName";
    p_datetime           TIMESTAMP    := "dateTime";
    p_user               uuid         := "User";

BEGIN

    p_datetime := COALESCE(p_datetime, CURRENT_TIMESTAMP(0));

    IF p_xml IS NOT NULL THEN
        DROP TABLE IF EXISTS temp_dlo_accessed_posts_direction;
        CREATE TEMPORARY TABLE temp_dlo_accessed_posts_direction AS
        SELECT
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/DLO_ACCESSED_POSTS_DIRECTION_ID/text()',
                   x))[1]::TEXT::uuid                                                                AS id,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/DIRECTION_TYPE/text()', x))[1]::TEXT::INTEGER      AS directiontype,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/DIRECTION_STATE/text()', x))[1]::TEXT::INTEGER     AS directionstate,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/LPU_MAIN/text()', x))[1]::VARCHAR(6)               AS lpumain,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/SNILS/text()', x))[1]::VARCHAR(14)                 AS snils,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/POST_CODE/text()', x))[1]::VARCHAR(255)            AS postcode,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/DOC_CODE/text()', x))[1]::VARCHAR(11)              AS doccode,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/LAST_NAME/text()', x))[1]::VARCHAR(40)             AS lastname,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/FIRST_NAME/text()', x))[1]::VARCHAR(40)            AS firstname,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/FATHER_NAME/text()', x))[1]::VARCHAR(40)           AS fathername,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/SPECIALITY/text()', x))[1]::VARCHAR(3)             AS speciality,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/POST_TYPE/text()', x))[1]::VARCHAR(4)              AS posttype,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/CATEGORY/text()', x))[1]::VARCHAR(4)::INTEGER      AS category,
            TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS_DIRECTION/BEGIN_ACCESS_DATE/text()', x))[1]::TEXT,
                         'YYYY-MM-DD')                                                               AS beginaccessdate,
            TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS_DIRECTION/END_ACCESS_DATE/text()', x))[1]::TEXT,
                         'YYYY-MM-DD')                                                               AS endaccessdate,
            TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS_DIRECTION/BEGIN_ACCESS_DATE/text()', x))[1]::TEXT,
                         'YYYY-MM-DD')                                                               AS beginworkdate,
            TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS_DIRECTION/CERTIFICATE_DATE/text()', x))[1]::TEXT,
                         'YYYY-MM-DD')                                                               AS certificatedate,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/MEDICAL_SPECIALITY_CODE/text()',
                   x))[1]::VARCHAR(10)                                                               AS medicalspecialitycode,
            TO_TIMESTAMP((XPATH('/DLO_ACCESSED_POSTS_DIRECTION/EXPULSION_DATE/text()', x))[1]::TEXT,
                         'YYYY-MM-DD')                                                               AS expulsiondate,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/EhrRus7PJSAM6GJM/text()', x))[1]::VARCHAR(10)::INT AS ehrrus7pjsam6gjm,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/EhrRus7PJSAM6GJMVer/text()', x))[1]::VARCHAR(8)    AS ehrrus7pjsam6gjmver,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/EhrRusLS9R0Q4VR4/text()', x))[1]::VARCHAR(10)::INT AS ehrrusls9r0q4vr4,
            (XPATH('/DLO_ACCESSED_POSTS_DIRECTION/EhrRusLS9R0Q4VR4Ver/text()', x))[1]::VARCHAR(8)    AS ehrrusls9r0q4vr4ver
        FROM UNNEST(XPATH('/DLO_ACCESSED_POSTS_DIRECTION/body/DLO_ACCESSED_POSTS_DIRECTION', p_xml)) AS x;

        IF (EXISTS
        (
            SELECT
                1
            FROM temp_dlo_accessed_posts_direction dir
            LEFT JOIN LATERAL (
                SELECT
                    ehr.idehr,
                    rrb.currversion
                FROM ehr.t_ehr_rus_7pjsam6gjm ehr
                JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = ehr.ehrrusrefbooks
                WHERE
                      ehr.idehr = dir.ehrrus7pjsam6gjm
                  AND rrb.currversion = dir.ehrrus7pjsam6gjmver
                LIMIT 1
                ) e ON TRUE
            WHERE
                  dir.ehrrus7pjsam6gjm IS NOT NULL
              AND dir.ehrrus7pjsam6gjmver IS NOT NULL
              AND e.idehr IS NULL
              AND e.currversion IS NULL
        ))
        THEN
            RAISE EXCEPTION 'Установлены некорректные значения EhrRus7pjsam6gjm и EhrRus7pjsam6gjmVer ';
            RETURN;
        END IF;

        IF (EXISTS
        (
            SELECT
                1
            FROM temp_dlo_accessed_posts_direction dir
            LEFT JOIN LATERAL (
                SELECT
                    ehr.idehr,
                    rrb.currversion
                FROM ehr.t_ehr_rus_ls9r0q4vr4 ehr
                JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = ehr.ehrrusrefbooks
                WHERE
                      ehr.idehr = dir.ehrrusls9r0q4vr4
                  AND rrb.currversion = dir.ehrrusls9r0q4vr4ver
                LIMIT 1
                ) e ON TRUE
            WHERE
                  dir.ehrrusls9r0q4vr4 IS NOT NULL
              AND dir.ehrrusls9r0q4vr4ver IS NOT NULL
              AND e.idehr IS NULL
              AND e.currversion IS NULL
        ))
        THEN
            RAISE EXCEPTION 'Установлены некорректные значения EhrRusLS9R0Q4VR4 и EhrRusLS9R0Q4VR4Ver ';
            RETURN;
        END IF;

        SELECT
            p.parametervalue
        INTO
            v_needconfirmation
        FROM vclib.t_holder_parameter p
        JOIN vclib.t_holder h ON p.holder = h.holderid
        WHERE
              h.name = 'RegistryUpdateHolder'
          AND p.parametername = 'NeedСonfirmation';

        IF v_needconfirmation NOT IN (v_true, v_false) THEN
            RAISE EXCEPTION 'Значение параметра v_NeedConfirmation не установлено';
            RETURN;
        END IF;

        SELECT
            d.snils,
            d.speciality,
            d.posttype,
            d.lastname,
            d.firstname,
            d.beginworkdate,
            d.postcode,
            d.directiontype
        INTO
            v_snils,
            v_speciality,
            v_posttype,
            v_lastname,
            v_firstname,
            v_beginworkdate,
            v_postcode,
            v_operationtype
        FROM temp_dlo_accessed_posts_direction d;

        IF v_snils IS NULL THEN
            RAISE EXCEPTION 'Не установлен снилс SNILS';
            RETURN;
        END IF;

        IF v_postcode IS NULL THEN
            RAISE EXCEPTION 'Не установлен код врача POST_CODE';
            RETURN;
        END IF;

        IF v_needconfirmation = v_true THEN
            SELECT
                COUNT(*)::INTEGER
            INTO v_opendirectioncount
            FROM rds.t_dlo_accessed_posts_direction d
            JOIN temp_dlo_accessed_posts_direction dlo
            ON d.postcode = d.postcode AND d.snils = dlo.snils AND d.directionstate = 0
            JOIN rds.t_lpu_main lpu ON lpu.mcod = dlo.lpumain;

            IF v_opendirectioncount > 0 THEN
                RAISE EXCEPTION 'Для данного врача уже существует открытое направление';
                RETURN;
            END IF;
        END IF;
        IF (v_needconfirmation = v_false) THEN

            INSERT INTO
                rds.t_dlo_accessed_posts_direction (dloaccessedpostsdirectionid,
                                                    directiontype,
                                                    directionstate,
                                                    lpumain,
                                                    snils,
                                                    postcode,
                                                    doccode,
                                                    lastname,
                                                    firstname,
                                                    fathername,
                                                    speciality,
                                                    posttype,
                                                    category,
                                                    beginaccessdate,
                                                    endaccessdate,
                                                    beginworkdate,
                                                    medicalspecialitycode,
                                                    expulsiondate,
                                                    createdatetime,
                                                    ehrrus7pjsam6gjm,
                                                    ehrrusls9r0q4vr4)
            SELECT
                dlo.id,
                dlo.directiontype,
                1::INTEGER, -- принято
                dlo.lpumain,
                dlo.snils,
                dlo.postcode,
                dlo.doccode,
                dlo.lastname,
                dlo.firstname,
                dlo.fathername,
                dlo.speciality,
                dlo.posttype,
                dlo.category,
                dlo.beginaccessdate,
                dlo.endaccessdate,
                dlo.beginworkdate,
                dlo.medicalspecialitycode,
                dlo.expulsiondate,
                p_datetime,
                er1.ehrrusid,
                er2.ehrrusid
            FROM temp_dlo_accessed_posts_direction dlo
            LEFT JOIN LATERAL (
                SELECT
                    er.ehrrusid
                FROM ehr.t_ehr_rus_7pjsam6gjm er
                JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE
                      er.idehr = dlo.ehrrus7pjsam6gjm
                  AND rrb.currversion = dlo.ehrrus7pjsam6gjmver
                LIMIT 1) er1 ON TRUE
            LEFT JOIN LATERAL (
                SELECT
                    er.ehrrusid
                FROM ehr.t_ehr_rus_ls9r0q4vr4 er
                JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE
                      er.idehr = dlo.ehrrusls9r0q4vr4
                  AND rrb.currversion = dlo.ehrrusls9r0q4vr4ver
                LIMIT 1) er2 ON TRUE;

            IF v_operationtype = v_insert THEN
                v_postid := gen_random_uuid();
                p_id := v_postid;

                INSERT INTO
                    rds.t_dlo_accessed_posts (dloaccessedpostsid,
                                              beginaccessdate,
                                              beginworkdate,
                                              category,
                                              certificatedate,
                                              doccode,
                                              endaccessdate,
                                              fathername,
                                              firstname,
                                              lastname,
                                              lpumain,
                                              medicalspecialitycode,
                                              postcode,
                                              posttype,
                                              snils,
                                              speciality,
                                              ehrrus7pjsam6gjm,
                                              ehrrusls9r0q4vr4)
                SELECT
                    v_postid,
                    dir.beginaccessdate,
                    dir.beginworkdate,
                    dir.category,
                    p_datetime,
                    dir.doccode,
                    dir.endaccessdate,
                    dir.fathername,
                    dir.firstname,
                    dir.lastname,
                    dir.lpumain,
                    dir.medicalspecialitycode,
                    dir.postcode,
                    dir.posttype,
                    dir.snils,
                    dir.speciality,
                    dir.ehrrus7pjsam6gjm,
                    dir.ehrrusls9r0q4vr4
                FROM rds.t_dlo_accessed_posts_direction dir
                JOIN temp_dlo_accessed_posts_direction dlo ON dlo.id = dir.dloaccessedpostsdirectionid;

            ELSE

                SELECT
                    p.dloaccessedpostsid,
                    dir.id
                INTO
                    v_postid,
                    p_id
                FROM rds.t_dlo_accessed_posts          p
                JOIN temp_dlo_accessed_posts_direction dir ON p.postcode = dir.postcode
                WHERE p.expulsiondate IS NULL;

                UPDATE p
                SET expulsiondate = dir.expulsiondate
                FROM rds.t_dlo_accessed_posts           p
                JOIN rds.t_dlo_accessed_posts_direction dir ON dir.dloaccessedpostsdirectionid = p_id
                    AND p.dloaccessedpostsid = v_postid;

            END IF;

            SELECT
                XMLELEMENT
                (
                    NAME "DLO_ACCESSED_POSTS",
                    XMLELEMENT(NAME "header", XMLFOREST('Update' AS "TYPE")),
                    XMLELEMENT
                    (
                        NAME "body",
                        XMLELEMENT
                        (
                            NAME "DLO_ACCESSED_POSTS",
                            XMLFOREST
                            (
                                p.dloaccessedpostsid AS "ID",
                                p.postcode AS "POST_CODE",
                                p.doccode AS "DOC_CODE",
                                p.lastname AS "LAST_NAME",
                                p.firstname AS "FIRST_NAME",
                                p.fathername AS "FATHER_NAME",
                                p.category AS "CATEGORY",
                                TO_CHAR(p.beginaccessdate, 'YYYY-MM-DD') AS "BEGIN_ACCESS_DATE",
                                TO_CHAR(p.endaccessdate, 'YYYY-MM-DD') AS "END_ACCESS_DATE",
                                TO_CHAR(p.beginworkdate, 'YYYY-MM-DD') AS "BEGIN_WORK_DATE",
                                TO_CHAR(p.certificatedate, 'YYYY-MM-DD') AS "CERTIFICATE_DATE",
                                p.medicalspecialitycode AS "MEDICAL_SPECIALITY_CODE",
                                TO_CHAR(p.expulsiondate, 'YYYY-MM-DD') AS "EXPULSION_DATE",
                                p.snils AS "SNILS",
                                p.posttype AS "POST_TYPE",
                                p.speciality AS "SPECIALITY",
                                p.lpumain AS "LPU_MAIN",
                                e1.idehr AS "EhrRus7PJSAM6GJM",
                                rrb1.currversion AS "EhrRus7PJSAM6GJMVer",
                                e2.idehr AS "EhrRusLS9R0Q4VR4",
                                rrb2.currversion AS "EhrRusLS9R0Q4VR4Ver"
                            )
                        )
                    )
                )::TEXT
            INTO v_result
            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
            WHERE p.dloaccessedpostsid = v_postid;

            INSERT INTO
                rds.t_rabbit_send_object
            (rabbitsendobjectid,
             queuename,
             content,
             messageid,
             headers,
             contentencoding,
             type,
             userid,
             state,
             createdatetime,
             ownerid)
            SELECT
                gen_random_uuid(),
                s.servername,
                converttexttobinaryzip(v_result::TEXT, ''::TEXT),
                NULL,
                NULL,
                v_contentencoding,
                'DLO_ACCESSED_POSTS',
                p_user,
                0::INT,
                p_datetime,
                p_id
            FROM rds.t_sync_servers s
            WHERE s.servertype = 1::INTEGER;

            SELECT
                XMLELEMENT
                (
                    NAME "DLO_ACCESSED_POSTS_DIRECTION",
                    XMLELEMENT(NAME "header", XMLFOREST('Update' AS "TYPE")),
                    XMLELEMENT
                    (
                        NAME "body",
                        XMLELEMENT
                        (
                            NAME "DLO_ACCESSED_POSTS_DIRECTION",
                            XMLFOREST
                            (
                                dap.dloaccessedpostsdirectionid AS
                                "DLO_ACCESSED_POSTS_DIRECTION_ID",
                                dap.directiontype AS "DIRECTION_TYPE",
                                dap.directionstate AS "DIRECTION_STATE",
                                dap.postcode AS "POST_CODE",
                                dap.expulsiondate AS "EXPULSION_DATE",
                                dap.refusingreason AS "REFUSING_REASON"
                            )
                        )
                    )
                )::TEXT
            INTO v_result
            FROM rds.t_dlo_accessed_posts_direction dap
            JOIN temp_dlo_accessed_posts_direction dlo ON dap.dloaccessedpostsdirectionid = dlo.id;

            INSERT INTO
                rds.t_rabbit_send_object
            (rabbitsendobjectid,
             queuename,
             content,
             messageid,
             headers,
             contentencoding,
             type,
             userid,
             state,
             createdatetime,
             ownerid)
            SELECT
                gen_random_uuid(),
                s.servername,
                converttexttobinaryzip(v_result::TEXT, ''::TEXT),
                NULL,
                NULL,
                v_contentencoding,
                v_type,
                p_user,
                0::INT,
                p_datetime,
                p_id
            FROM rds.t_sync_servers s
            WHERE s.servertype = 0::INTEGER;

        ELSE

            INSERT INTO
                rds.t_dlo_accessed_posts_direction (dloaccessedpostsdirectionid,
                                                    directiontype,
                                                    directionstate,
                                                    lpumain,
                                                    snils,
                                                    postcode,
                                                    doccode,
                                                    lastname,
                                                    firstname,
                                                    fathername,
                                                    speciality,
                                                    posttype,
                                                    category,
                                                    beginaccessdate,
                                                    endaccessdate,
                                                    beginworkdate,
                                                    medicalspecialitycode,
                                                    expulsiondate,
                                                    createdatetime,
                                                    ehrrus7pjsam6gjm,
                                                    ehrrusls9r0q4vr4)
            SELECT
                dlo.id,
                dlo.directiontype,
                0::INTEGER, -- принято
                dlo.lpumain,
                dlo.snils,
                dlo.postcode,
                dlo.doccode,
                dlo.lastname,
                dlo.firstname,
                dlo.fathername,
                dlo.speciality,
                dlo.posttype,
                dlo.category,
                dlo.beginaccessdate,
                dlo.endaccessdate,
                dlo.beginworkdate,
                dlo.medicalspecialitycode,
                dlo.expulsiondate,
                p_datetime,
                er1.ehrrusid,
                er2.ehrrusid
            FROM temp_dlo_accessed_posts_direction dlo
            LEFT JOIN LATERAL (
                SELECT
                    er.ehrrusid
                FROM ehr.t_ehr_rus_7pjsam6gjm er
                JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE
                      er.idehr = dlo.ehrrus7pjsam6gjm
                  AND rrb.currversion = dlo.ehrrus7pjsam6gjmver
                LIMIT 1) er1 ON TRUE
            LEFT JOIN LATERAL (
                SELECT
                    er.ehrrusid
                FROM ehr.t_ehr_rus_ls9r0q4vr4 er
                JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE
                      er.idehr = dlo.ehrrusls9r0q4vr4
                  AND rrb.currversion = dlo.ehrrusls9r0q4vr4ver
                LIMIT 1) er2 ON TRUE;

        END IF;


    ELSE

        SELECT
            dir.directiontype,
            dir.directionstate
        INTO
            v_operationtype,
            v_directionstate
        FROM rds.t_dlo_accessed_posts_direction dir
        WHERE dir.dloaccessedpostsdirectionid = p_id;

        IF v_directionstate = v_ds_accept THEN
            IF v_operationtype = v_insert THEN
                v_postid = gen_random_uuid();

                INSERT INTO
                    rds.t_dlo_accessed_posts (dloaccessedpostsid,
                                              beginaccessdate,
                                              beginworkdate,
                                              category,
                                              certificatedate,
                                              doccode,
                                              endaccessdate,
                                              fathername,
                                              firstname,
                                              lastname,
                                              lpumain,
                                              medicalspecialitycode,
                                              postcode,
                                              posttype,
                                              snils,
                                              speciality,
                                              ehrrus7pjsam6gjm,
                                              ehrrusls9r0q4vr4)
                SELECT
                    v_postid,
                    dir.beginaccessdate,
                    dir.beginworkdate,
                    dir.category,
                    p_datetime,
                    dir.doccode,
                    dir.endaccessdate,
                    dir.fathername,
                    dir.firstname,
                    dir.lastname,
                    dir.lpumain,
                    dir.medicalspecialitycode,
                    dir.postcode,
                    dir.posttype,
                    dir.snils,
                    dir.speciality,
                    dir.ehrrus7pjsam6gjm,
                    dir.ehrrusls9r0q4vr4

                FROM rds.t_dlo_accessed_posts_direction dir
                WHERE dir.dloaccessedpostsdirectionid = p_id;
            ELSE

                SELECT
                    p.dloaccessedpostsid
                INTO
                    v_postid
                FROM rds.t_dlo_accessed_posts           p
                JOIN rds.t_dlo_accessed_posts_direction dir ON p.postcode = dir.postcode
                    AND p.expulsiondate IS NULL
                    AND dir.dloaccessedpostsdirectionid = p_id;

                UPDATE rds.t_dlo_accessed_posts p
                SET expulsiondate = dir.expulsiondate
                FROM rds.t_dlo_accessed_posts_direction dir
                WHERE
                      p.postcode = dir.postcode
                  AND p.expulsiondate IS NULL
                  AND dir.dloaccessedpostsdirectionid = p_id;


            END IF;


            SELECT
                XMLELEMENT(
                    NAME "DLO_ACCESSED_POSTS",
                    XMLELEMENT(NAME "header", XMLFOREST('Update' AS "TYPE")),
                    XMLELEMENT
                    (
                        NAME "body",
                        XMLELEMENT
                        (
                            NAME "DLO_ACCESSED_POSTS",
                            XMLFOREST
                            (
                                p.dloaccessedpostsid AS "ID",
                                p.postcode AS "POST_CODE",
                                p.doccode AS "DOC_CODE",
                                p.lastname AS "LAST_NAME",
                                p.firstname AS "FIRST_NAME",
                                p.fathername AS "FATHER_NAME",
                                p.category AS "CATEGORY",
                                TO_CHAR(p.beginaccessdate, 'YYYY-MM-DD') AS "BEGIN_ACCESS_DATE",
                                TO_CHAR(p.endaccessdate, 'YYYY-MM-DD') AS "END_ACCESS_DATE",
                                TO_CHAR(p.beginworkdate, 'YYYY-MM-DD') AS "BEGIN_WORK_DATE",
                                TO_CHAR(p.certificatedate, 'YYYY-MM-DD') AS "CERTIFICATE_DATE",
                                p.medicalspecialitycode AS "MEDICAL_SPECIALITY_CODE",
                                TO_CHAR(p.expulsiondate, 'YYYY-MM-DD') AS "EXPULSION_DATE",
                                p.snils AS "SNILS",
                                p.posttype AS "POST_TYPE",
                                p.speciality AS "SPECIALITY",
                                p.lpumain AS "LPU_MAIN",
                                e1.idehr AS "EhrRus7PJSAM6GJM",
                                rrb1.currversion AS "EhrRus7PJSAM6GJMVer",
                                e2.idehr AS "EhrRusLS9R0Q4VR4",
                                rrb2.currversion AS "EhrRusLS9R0Q4VR4Ver"
                            )
                        )
                    )
                )::TEXT
            INTO v_result
            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
            WHERE p.dloaccessedpostsid = v_postid;

            INSERT INTO
                rds.t_rabbit_send_object
            (rabbitsendobjectid,
             queuename,
             content,
             messageid,
             headers,
             contentencoding,
             type,
             userid,
             state,
             createdatetime,
             ownerid)
            SELECT
                gen_random_uuid(),
                s.servername,
                converttexttobinaryzip(v_result, ''),
                NULL,
                NULL,
                v_contentencoding,
                'DLO_ACCESSED_POSTS',
                p_user,
                0,
                p_datetime,
                p_id
            FROM rds.t_sync_servers s
            WHERE s.servertype = 1;

            SELECT
                XMLELEMENT(
                    NAME "DLO_ACCESSED_POSTS_DIRECTION",
                    XMLELEMENT(NAME "header", XMLFOREST('Update' AS "TYPE")),
                    XMLELEMENT
                    (
                        NAME "body",
                        XMLELEMENT
                        (
                            NAME "DLO_ACCESSED_POSTS_DIRECTION",
                            XMLFOREST
                            (
                                dap.dloaccessedpostsdirectionid AS "DLO_ACCESSED_POSTS_DIRECTION_ID",
                                dap.directiontype AS "DIRECTION_TYPE",
                                dap.directionstate AS "DIRECTION_STATE",
                                dap.postcode AS "POST_CODE",
                                TO_CHAR(dap.expulsiondate, 'YYYY-MM-DD') AS "EXPULSION_DATE",
                                dap.refusingreason AS "REFUSING_REASON"
                            )
                        )
                    )
                )::TEXT
            INTO v_result
            FROM rds.t_dlo_accessed_posts_direction dap
            WHERE dap.dloaccessedpostsdirectionid = p_id;

            INSERT INTO
                rds.t_rabbit_send_object
            (rabbitsendobjectid,
             queuename,
             content,
             messageid,
             headers,
             contentencoding,
             type,
             userid,
             state,
             createdatetime,
             ownerid)
            SELECT
                gen_random_uuid(),
                s.servername,
                converttexttobinaryzip(v_result, ''),
                NULL,
                NULL,
                v_contentencoding,
                v_type,
                p_user,
                0,
                p_datetime,
                p_id
            FROM rds.t_sync_servers s
            WHERE s.servertype = 0;

        END IF;
    END IF;
END
$$
