CREATE OR REPLACE PROCEDURE public.sp_vzn_direction(
    xml xml,
    queuename varchar(100) DEFAULT 'RDSSync',
    datetime timestamp DEFAULT NULL,
    "user" uuid DEFAULT NULL,
    service boolean DEFAULT FALSE
)
    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_service         boolean      := service;
    v_registerid      int;
    v_changetype      int;
    v_ownerid         uuid ;
    v_operationtype   text;
    v_result          text ;
    v_contentencoding varchar(255) := 'utf-8';
    v_directiontype   int ;
    v_type            text         := 'NOSOLOGY_DIRECTION';
    v_snils           varchar(14);
    v_oid             text;
    v_bemid           uuid;
    v_bentype         varchar(100);

BEGIN
    
    SELECT (XPATH('/NOSOLOGY_DIRECTION/RegisterID/text()', x))[1]::text::integer AS registerid,
           (XPATH('/NOSOLOGY_DIRECTION/ChangeType/text()', x))[1]::text::integer AS changetype,
           (XPATH('/NOSOLOGY_DIRECTION/SNILS/text()', x))[1]::varchar(14)        AS snils,
           mdr.oid
    INTO v_registerid,
        v_changetype,
        v_snils,
        v_oid
    FROM UNNEST(XPATH('/NOSOLOGY_DIRECTION', p_xml)) x
    LEFT JOIN rds.t_lpu_main lpu ON lpu.mcod = (XPATH('/NOSOLOGY_DIRECTION/LPU/text()', x))[1]::varchar(6)
    LEFT JOIN ehr.t_ehr_rus_mdr308 mdr ON mdr.ehrrusid = lpu.ehrrusmdr308dop;
    
    
    CREATE TEMP TABLE temp_nosology_direction AS
        SELECT (XPATH('/NOSOLOGY_DIRECTION/ID/text()', x))[1]::varchar(36)::uuid                      AS id,
               (XPATH('/NOSOLOGY_DIRECTION/LPU/text()', x))[1]::varchar(6)                            AS lpu,
               (XPATH('/NOSOLOGY_DIRECTION/EnterDate/text()', x))[1]::varchar(10)::timestamp          AS enterdate,
               (XPATH('/NOSOLOGY_DIRECTION/LastName/text()', x))[1]::varchar(40)                      AS lastname,
               (XPATH('/NOSOLOGY_DIRECTION/FirstName/text()', x))[1]::varchar(40)                     AS firstname,
               (XPATH('/NOSOLOGY_DIRECTION/FatherName/text()', x))[1]::varchar(40)                    AS fathername,
               (XPATH('/NOSOLOGY_DIRECTION/BirthSurname/text()', x))[1]::varchar(40)                  AS birthsurname,
               (XPATH('/NOSOLOGY_DIRECTION/Birthday/text()', x))[1]::varchar(10)::timestamp           AS birthday,
               (XPATH('/NOSOLOGY_DIRECTION/Date/text()', x))[1]::varchar(10)::timestamp               AS date,
               (XPATH('/NOSOLOGY_DIRECTION/Sex/text()', x))[1]::varchar(10)::int                      AS sex,
               (XPATH('/NOSOLOGY_DIRECTION/Kod/text()', x))[1]::varchar(32)                           AS kod,
               (XPATH('/NOSOLOGY_DIRECTION/DocSeries/text()', x))[1]::varchar(16)                     AS docseries,
               (XPATH('/NOSOLOGY_DIRECTION/DocNumber/text()', x))[1]::varchar(16)                     AS docnumber,
               (XPATH('/NOSOLOGY_DIRECTION/DocType/text()', x))[1]::varchar(10)::int                  AS doctype,
               (XPATH('/NOSOLOGY_DIRECTION/RegisterID/text()', x))[1]::varchar(10)::int               AS registerid,
               (XPATH('/NOSOLOGY_DIRECTION/SNILS/text()', x))[1]::varchar(14)                         AS snils,
               (XPATH('/NOSOLOGY_DIRECTION/ChangeType/text()', x))[1]::varchar(10)::int               AS changetype,
               (XPATH('/NOSOLOGY_DIRECTION/ExcludeReason/text()', x))[1]::varchar(10)::int            AS excludereason,
               (XPATH('/NOSOLOGY_DIRECTION/DocIssueDate/text()', x))[1]::varchar(10)::timestamp       AS docissuedate,
               (XPATH('/NOSOLOGY_DIRECTION/DocIssueOrg/text()', x))[1]::varchar(256)                  AS docissueorg,
               (XPATH('/NOSOLOGY_DIRECTION/Enp/text()', x))[1]::varchar(16)                           AS enp,
               (XPATH('/NOSOLOGY_DIRECTION/Region/text()', x))[1]::varchar(13)                        AS region,
               (XPATH('/NOSOLOGY_DIRECTION/RealFiasAddress/text()', x))[1]::varchar(36)               AS realfiasaddress,
               (XPATH('/NOSOLOGY_DIRECTION/RealFiasHome/text()', x))[1]::varchar(36)                  AS realfiashome,
               (XPATH('/NOSOLOGY_DIRECTION/RealCity/text()', x))[1]::varchar(100)                     AS realcity,
               (XPATH('/NOSOLOGY_DIRECTION/RealCityPrefix/text()', x))[1]::varchar(10)                AS realcityprefix,
               (XPATH('/NOSOLOGY_DIRECTION/RealStreet/text()', x))[1]::varchar(100)                   AS realstreet,
               (XPATH('/NOSOLOGY_DIRECTION/RealStreetPrefix/text()', x))[1]::varchar(10)              AS realstreetprefix,
               (XPATH('/NOSOLOGY_DIRECTION/RealHouse/text()', x))[1]::varchar(100)                    AS realhouse,
               (XPATH('/NOSOLOGY_DIRECTION/RealBuild/text()', x))[1]::varchar(100)                    AS realbuild,
               (XPATH('/NOSOLOGY_DIRECTION/RealStruc/text()', x))[1]::varchar(100)                    AS realstruc,
               (XPATH('/NOSOLOGY_DIRECTION/RealRoom/text()', x))[1]::varchar(100)                     AS realroom,
               (XPATH('/NOSOLOGY_DIRECTION/RealAOIDArea/text()', x))[1]::varchar(36)                  AS realaoidarea,
               (XPATH('/NOSOLOGY_DIRECTION/RealLiveStartDate/text()', x))[1]::varchar(10)::timestamp  AS reallivestartdate,
               (XPATH('/NOSOLOGY_DIRECTION/RealLiveEndDate/text()', x))[1]::varchar(10)::timestamp    AS realliveenddate,
               (XPATH('/NOSOLOGY_DIRECTION/LegalFiasAddress/text()', x))[1]::varchar(36)              AS legalfiasaddress,
               (XPATH('/NOSOLOGY_DIRECTION/LegalFiasHome/text()', x))[1]::varchar(36)                 AS legalfiashome,
               (XPATH('/NOSOLOGY_DIRECTION/LegalCity/text()', x))[1]::varchar(100)                    AS legalcity,
               (XPATH('/NOSOLOGY_DIRECTION/LegalCityPrefix/text()', x))[1]::varchar(10)               AS legalcityprefix,
               (XPATH('/NOSOLOGY_DIRECTION/LegalStreet/text()', x))[1]::varchar(100)                  AS legalstreet,
               (XPATH('/NOSOLOGY_DIRECTION/LegalStreetPrefix/text()', x))[1]::varchar(10)             AS legalstreetprefix,
               (XPATH('/NOSOLOGY_DIRECTION/LegalHouse/text()', x))[1]::varchar(100)                   AS legalhouse,
               (XPATH('/NOSOLOGY_DIRECTION/LegalBuild/text()', x))[1]::varchar(100)                   AS legalbuild,
               (XPATH('/NOSOLOGY_DIRECTION/LegalStruc/text()', x))[1]::varchar(100)                   AS legalstruc,
               (XPATH('/NOSOLOGY_DIRECTION/LegalRoom/text()', x))[1]::varchar(100)                    AS legalroom,
               (XPATH('/NOSOLOGY_DIRECTION/LegalAOIDArea/text()', x))[1]::varchar(36)                 AS legalaoidarea,
               (XPATH('/NOSOLOGY_DIRECTION/LegalLiveStartDate/text()', x))[1]::varchar(10)::timestamp AS legallivestartdate,
               (XPATH('/NOSOLOGY_DIRECTION/LegalLiveEndDate/text()', x))[1]::varchar(10)::timestamp   AS legalliveenddate,
               (XPATH('/NOSOLOGY_DIRECTION/CitizenShip/text()', x))[1]::varchar(3)                    AS citizenship,
               (XPATH('/NOSOLOGY_DIRECTION/PolicySerial/text()', x))[1]::varchar(12)                  AS policyserial,
               (XPATH('/NOSOLOGY_DIRECTION/PolicyNumber/text()', x))[1]::varchar(32)                  AS policynumber,
               (XPATH('/NOSOLOGY_DIRECTION/SmoCod/text()', x))[1]::varchar(5)                         AS smocod,
               (XPATH('/NOSOLOGY_DIRECTION/PolicyForm/text()', x))[1]::varchar(10)::int               AS policyform,
               (XPATH('/NOSOLOGY_DIRECTION/WorkPlace/text()', x))[1]::varchar(500)                    AS workplace,
               (XPATH('/NOSOLOGY_DIRECTION/WorkPost/text()', x))[1]::varchar(500)                     AS workpost,
               (XPATH('/NOSOLOGY_DIRECTION/RationaleForDirection/text()', x))[1]::varchar(1000)       AS rationalefordirection,
               (XPATH('/NOSOLOGY_DIRECTION/Number/text()', x))[1]::varchar(10)::int                   AS number,
               (XPATH('/NOSOLOGY_DIRECTION/FedReg/text()', x))[1]::varchar(5)::boolean                AS fedreg,
               (XPATH('/NOSOLOGY_DIRECTION/FedRegCategoria/text()', x))[1]::varchar(10)::int          AS fedregcategoria,
               (XPATH('/NOSOLOGY_DIRECTION/LLOReg/text()', x))[1]::varchar(5)::boolean                AS lloreg,
               (XPATH('/NOSOLOGY_DIRECTION/Invalidity/text()', x))[1]::varchar(10)::int               AS invalidity,
               (XPATH('/NOSOLOGY_DIRECTION/PostFIO/text()', x))[1]::varchar(255)                      AS postfio,
               (XPATH('/NOSOLOGY_DIRECTION/HeadPostFIO/text()', x))[1]::varchar(255)                  AS headpostfio,
               (XPATH('/NOSOLOGY_DIRECTION/McPostFIO/text()', x))[1]::varchar(255)                    AS mcpostfio
        FROM UNNEST(XPATH('/NOSOLOGY_DIRECTION', p_xml)) AS x;
    
    CREATE TEMP TABLE temp_nosology_benefits AS
        SELECT (XPATH('/NOSOLOGY_DIRECTION_BENEFIT/DirectionID/text()', x))[1]::varchar(36)::uuid       AS directionid,
               (XPATH('/NOSOLOGY_DIRECTION_BENEFIT/DiagDate/text()', x))[1]::varchar(10)::timestamp     AS diagdate,
               (XPATH('/NOSOLOGY_DIRECTION_BENEFIT/Document/text()', x))[1]::varchar(16)                AS document,
               (XPATH('/NOSOLOGY_DIRECTION_BENEFIT/DocumentDate/text()', x))[1]::varchar(10)::timestamp AS documentdate,
               (XPATH('/NOSOLOGY_DIRECTION_BENEFIT/Document/text()', x))[1]::varchar(8)                 AS mkb,
               (XPATH('/NOSOLOGY_DIRECTION_BENEFIT/BenefitCode/text()', x))[1]::varchar(255)            AS benefitcode,
               (XPATH('/NOSOLOGY_DIRECTION_BENEFIT/BenefitViews/text()', x))[1]::varchar(10)::int       AS benefitviews,
               (XPATH('/NOSOLOGY_DIRECTION_BENEFIT/PostCode/text()', x))[1]::varchar(255)               AS postcode,
               (XPATH('/NOSOLOGY_DIRECTION_BENEFIT/ChangeType/text()', x))[1]::varchar(10)::int         AS changetype
        FROM UNNEST(XPATH('/NOSOLOGY_DIRECTION/NOSOLOGY_DIRECTION_BENEFIT', p_xml)) AS x;
    
    WITH cte AS (
        UPDATE temp_nosology_direction rd
            SET registerid = r.pac_id
            FROM rds.t_nosology r
            WHERE r.snils = rd.snils
                AND rd.registerid IS NULL
                AND r.snils IS NOT NULL
                AND r.snils <> ''
                AND r.snils <> '              '
            RETURNING r.pac_id AS pac_id, r.nosologyid AS nosology_id
                )
    SELECT pac_id, nosology_id
    INTO v_registerid, v_ownerid
    FROM cte
    LIMIT 1;
    
    IF (v_registerid IS NULL) THEN
        
        CALL public.sp_generateidforentity('NOSOLOGY_REGISTER', 'PrimaryKey', 1, v_registerid);
        
        UPDATE temp_nosology_direction rd
        SET registerid = v_registerid;
        
        v_ownerid := gen_random_uuid();
        
        IF (v_registerid = 0) THEN
            RAISE EXCEPTION 'Не верно сгенерирован идентификатор льготника в регистре!';
        END IF;
    
    
    END IF;
    
    WITH cte AS (
        UPDATE rds.t_nosology rp
            SET
                dateofchange = d.enterdate,
                omsnumber = d.policynumber,
                omsserial = d.policyserial,
                docseries = d.docseries,
                docnumber = d.docnumber,
                birthday = d.birthday,
                firstname = d.firstname,
                lastname = d.lastname,
                fathername = d.fathername,
                docissuedate = d.docissuedate,
                docissueorg = d.docissueorg,
                documenttype = edoc.enumid,
                exemptionby178 = d.fedreg, -- Льгота по 178-ФЗ
                exemptionby890 = d.lloreg, -- Льгота по 890-ПП
                inclusionlpu = d.lpu,
                sex = esex.enumid,
                snils = d.snils,
                birthdaylastname = d.birthsurname,
                f002smo = d.smocod,
                region = region.regionid,
                fiasaddress = d.realaoidarea,
                fiasstreet = d.realfiasaddress,
                fiashome = d.realfiashome,
                city = d.realcity,
                cityprefix = d.realcityprefix,
                street = d.realstreet,
                streetprefix = d.realstreetprefix,
                house = d.realhouse,
                workplace = d.workplace,
                post = d.postfio,
                medicalcommissionpost = d.mcpostfio,
                workpost = d.workpost,
                rationalefordirection = d.rationalefordirection,
                fedregcategoria = d.fedregcategoria,
                policyform = d.policyform,
                citizenship = cs.ehrrusid
            FROM temp_nosology_direction d
                JOIN rds.t_region region ON d.region = region.code
                JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.code = d.sex
                JOIN vclib.t_enums edoc ON edoc.enumtypeid = 105 AND edoc.code = d.doctype
                LEFT JOIN ehr.t_ehr_rus_o00015 cs ON cs.code = d.citizenship
            WHERE d.changetype = 2
                AND d.registerid = rp.pac_id
            RETURNING rp.nosologyid AS nosologyid
                )
    SELECT nosologyid
    INTO v_ownerid
    FROM cte
    LIMIT 1;
    
    UPDATE rds.t_nosology rp
    SET dateofdeclusion = p.enterdate,
        excludereason   = p.excludereason,
        declusionlpu    = p.lpu
    FROM temp_nosology_direction p
    WHERE p.registerid = rp.pac_id
      AND p.changetype = 3;
    
    UPDATE rds.t_nosology_benefits brd
    SET enddate = p_datetime
    FROM rds.t_nosology rp
    JOIN temp_nosology_direction p ON p.registerid = rp.pac_id
    WHERE rp.nosologyid = brd.nosology
      AND p.changetype = 3
      AND (brd.enddate IS NULL OR brd.enddate > p_datetime);
    
    INSERT INTO
        rds.t_nosology (nosologyid,
                        adress,
                        dateofchange,
                        dateofinclusion,
                        omsnumber,
                        omsserial,
                        docseries,
                        docnumber,
                        birthday,
                        firstname,
                        lastname,
                        fathername,
                        docissuedate,
                        docissueorg,
                        documenttype,
                        exemptionby178,
                        exemptionby890,
                        inclusionlpu,
                        region,
                        sex,
                        subjectofrfaccounted,
                        snils,
                        birthdaylastname,
                        f002smo,
                        fiasaddress,
                        fiasstreet,
                        fiashome,
                        city,
                        cityprefix,
                        street,
                        streetprefix,
                        house,
                        workplace,
                        workpost,
                        comment,
                        fedregcategoria,
                        policyform,
                        citizenship,
                        pac_id)
    SELECT v_ownerid,
           '',
           p_datetime,
           d.enterdate,
           d.policynumber,
           d.policyserial,
           d.docseries,
           d.docnumber,
           d.birthday,
           d.firstname,
           d.lastname,
           d.fathername,
           d.docissuedate,
           d.docissueorg,
           edoc.enumid,
           d.fedreg,
           d.lloreg,
           d.lpu,
           NULL,
           esex.enumid,
           0,
           d.snils,
           d.birthsurname,
           d.smocod,
           d.realaoidarea,
           d.realfiasaddress,
           d.realfiashome,
           d.realcity,
           d.realcityprefix,
           d.realstreet,
           d.realstreetprefix,
           d.realhouse,
           d.workplace,
           d.workpost,
           d.rationalefordirection,
           d.fedregcategoria,
           d.policyform,
           cs.ehrrusid,
           v_registerid
    FROM temp_nosology_direction d
    JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.code = d.sex
    JOIN vclib.t_enums edoc ON edoc.enumtypeid = 105 AND edoc.code = d.doctype
    LEFT JOIN ehr.t_ehr_rus_o00015 cs ON cs.code = d.citizenship
    WHERE d.changetype = 1;
    
    INSERT INTO
        rds.t_nosology_benefits(nosologybenefitsid,
                                nosology,
                                benefits,
                                mkb,
                                post,
                                head,
                                medicalcommissionpost,
                                datebegin,
                                ehrrusp360coa3ob,
                                disability,
                                therapyresistance,
                                enddate)
    SELECT gen_random_uuid(),
           r.nosologyid,
           b.benefitsid,
           mkb,
           d.postfio,
           d.headpostfio,
           d.mcpostfio,
           p_datetime,
           (SELECT er.ehrrusid FROM ehr.t_ehr_rus_p360coa3ob er WHERE er.idehr = d.invalidity),
           (CASE WHEN d.invalidity IS NOT NULL THEN 1 ELSE 0 END),
           0,
           NULL
    FROM temp_nosology_benefits bd
    JOIN temp_nosology_direction d ON d.id = bd.directionid
    JOIN rds.t_nosology r ON r.pac_id = v_registerid
    LEFT JOIN vclib.t_enums e ON e.enumtypeid = 190 AND e.code = bd.benefitviews
    JOIN rds.t_benefits b ON b.benefitcode = bd.benefitcode AND b.benefitviews = e.enumid
    WHERE bd.changetype = 1;
    
    UPDATE rds.t_nosology_benefits rpb
    SET enddate = p_datetime
    FROM temp_nosology_benefits rdb
    JOIN rds.t_nosology r ON r.pac_id = v_registerid
    LEFT JOIN vclib.t_enums e ON e.enumtypeid = 190 AND e.code = rdb.benefitviews
    LEFT JOIN rds.t_benefits b ON b.benefitcode = rdb.benefitcode AND b.benefitviews = e.enumid
    JOIN rds.t_nosology_benefits rpb ON rpb.nosology = r.nosologyid AND rpb.benefits = b.benefitsid
    WHERE rdb.changetype = 3
      AND (rpb.enddate IS NULL OR rpb.enddate > p_datetime);
    
    SELECT XMLELEMENT
           (
               NAME "NOSOLOGY_DIRECTION",
               XMLFOREST
               (
                   XMLELEMENT(NAME "ID", rd.id),
                   XMLELEMENT(NAME "RegisterID", rd.registerid)
               )
           )
    FROM temp_nosology_direction rd;
    
    SELECT XMLELEMENT
           (
               NAME "NOSOLOGY",
               XMLFOREST
               (
                   XMLELEMENT
                   (
                       NAME "header",
                       XMLELEMENT(NAME "TYPE", v_operationtype)
                   ),
                   XMLELEMENT
                   (
                       NAME "body",
                       XMLELEMENT
                       (
                           NAME "NOSOLOGY_ITEMS",
                           (SELECT XMLAGG
                                   (
                                       XMLELEMENT(NAME "ID", nos.nosologyid),
                                       XMLELEMENT(NAME "ADRESS", nos.adress),
                                       XMLELEMENT(NAME "CARD_STATUS", nb.cardstatus),
                                       XMLELEMENT(NAME "COMMENT", nos.comment),
                                       XMLELEMENT(NAME "CHANGE_DATE", TO_CHAR(nos.dateofchange, 'YYYY.MM.DD')),
                                       XMLELEMENT(NAME "DECLUSION_DATE", TO_CHAR(nos.dateofdeclusion, 'YYYY.MM.DD')),
                                       XMLELEMENT(NAME "INCLUSION_DATE", TO_CHAR(nos.dateofinclusion, 'YYYY.MM.DD')),
                                       XMLELEMENT(NAME "EXECUTIVE_AUTHORITY", nos.executiveauthority),
                                       XMLELEMENT(NAME "HEAD", nb.head),
                                       XMLELEMENT(NAME "OMS_NUMBER", nos.omsnumber),
                                       XMLELEMENT(NAME "OMS_SERIAL", nos.omsserial),
                                       XMLELEMENT(NAME "OPERATION_CODE", nos.operationcode),
                                       XMLELEMENT(NAME "UNIQUE_NUMBER", nos.pac_id),
                                       XMLELEMENT(NAME "MKB", nb.mkb),
                                       XMLELEMENT(NAME "DIAGNOSIS", nb.diagnosis),
                                       XMLELEMENT(NAME "DOC_SERIES", nos.docseries),
                                       XMLELEMENT(NAME "DOC_NUMBER", nos.docnumber),
                                       XMLELEMENT(NAME "BIRTHDAY", TO_CHAR(nos.birthday, 'YYYY.MM.DD')),
                                       XMLELEMENT(NAME "FIRST_NAME", nos.firstname),
                                       XMLELEMENT(NAME "LAST_NAME", nos.lastname),
                                       XMLELEMENT(NAME "FATHER_NAME", nos.fathername),
                                       XMLELEMENT(NAME "DOC_ISSUE_DATE", TO_CHAR(nos.docissuedate, 'YYYY.MM.DD')),
                                       XMLELEMENT(NAME "DOC_ISSUE_ORG", nos.docissueorg),
                                       XMLELEMENT(NAME "DECLUSION_LPU", nos.declusionlpu),
                                       XMLELEMENT(NAME "DOC_TYPE", nos.documenttype),
                                       XMLELEMENT(NAME "EX178", nos.exemptionby178),
                                       XMLELEMENT(NAME "EX890", nos.exemptionby890),
                                       XMLELEMENT(NAME "INCLUSION_LPU", nos.inclusionlpu),
                                       XMLELEMENT(NAME "REGION", reg.code),
                                       XMLELEMENT(NAME "SEX", nos.sex),
                                       XMLELEMENT(NAME "RF_ACCOUNT", nos.subjectofrfaccounted),
                                       XMLELEMENT(NAME "BIRTHDAY_LAST_NAME", nos.birthdaylastname),
                                       XMLELEMENT(NAME "DIASBILITY", nb.disability),
                                       XMLELEMENT(NAME "THERAPY_RESISTANCE", nb.therapyresistance),
                                       XMLELEMENT(NAME "DEAT_DATE", TO_CHAR(nos.deathdate, 'YYYY.MM.DD')),
                                       XMLELEMENT(NAME "OKATO", nos.okato),
                                       XMLELEMENT(NAME "SMO", nos.f002smo),
                                       XMLELEMENT(NAME "SNILS", CASE
                                                                    WHEN TRIM(REPLACE(REPLACE(nos.snils, '0', ''), '-', '')) = ''
                                                                        THEN NULL
                                                                    ELSE nos.snils
                                                                END),
                                       XMLELEMENT(NAME "BENIFITS", b.benefitcode)
                                   )
                            FROM rds.t_nosology_benefits nb
                            JOIN rds.t_nosology nos ON nb.nosology = nos.nosologyid
                            LEFT JOIN rds.t_region reg ON reg.regionid = nos.region
                            JOIN rds.t_benefits b ON b.benefitsid = nb.benefits
                            WHERE nos.pac_id = v_registerid
                           )
                       )
                   )
               )
           )
    INTO p_xml;
    
    
    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 s.servername = p_queuename
      AND s.servertype = 0
      AND v_result IS NOT NULL
    UNION ALL
    SELECT gen_random_uuid(),
           s.servername,
           public.converttexttobinaryzip(p_xml::text, ''),
           NULL,
           NULL,
           v_contentencoding,
           v_type,
           p_user,
           0,
           p_datetime,
           v_ownerid
    FROM rds.t_sync_servers s
    WHERE s.servertype = 1;
    
    UPDATE public.t_entity_ids e
    SET id = (SELECT MAX(pac_id) + 1 FROM rds.t_nosology)
    WHERE e.entname IN ('NOSOLOGY_REGISTER');
    
    IF (p_service = TRUE) THEN
        
        IF (v_directiontype = 1) THEN
            
            CALL sp_createvznservicemessage('patient.create', v_ownerid, v_snils, v_oid);
        
        ELSIF (v_directiontype = 2) THEN
            
            CALL sp_createvznservicemessage('patient.update', v_ownerid, v_snils, v_oid);
        
        ELSIF (v_directiontype = 3) THEN
            
            FOR v_bemid IN
                SELECT rpb.nosologybenefitsid
                FROM rds.t_nosology_benefits rpb
                LEFT JOIN rds.t_nosology r ON r.nosologyid = rpb.nosology
                WHERE r.pac_id = v_registerid
                  AND r.snils = v_snils
                  AND rpb.enddate = p_datetime
                LOOP
                    CALL sp_createvznservicemessage('vzn.update', v_bemid, v_snils, v_oid);
                    
                    PERFORM PG_SLEEP(0.5);
                END LOOP;
        
        
        END IF;
        
        IF (v_directiontype <> 3 AND
            EXISTS (SELECT 1 FROM temp_nosology_benefits d WHERE d.directionid IS NOT NULL)) THEN
            
            FOR v_bemid, v_bentype IN
                SELECT CASE WHEN rdb.changetype = 1 THEN 'van.create' ELSE 'van.update' END, rpb.nosologybenefitsid
                FROM temp_nosology_benefits rdb
                JOIN rds.t_nosology r ON r.pac_id = v_registerid
                JOIN rds.t_nosology_benefits rpb ON rpb.nosology = r.nosologyid
                LEFT JOIN vclib.t_enums e ON e.enumtypeid = 190 AND e.code = rdb.benefitviews
                LEFT JOIN rds.t_benefits b ON b.benefitcode = rdb.benefitcode AND b.benefitviews = e.enumid
                WHERE rpb.benefits = b.benefitsid
                  AND ((rdb.changetype = 3 AND rpb.enddate = p_datetime) OR
                       (rdb.changetype = 1 AND (rpb.datebegin = p_datetime)))
                LOOP
                    CALL sp_createvznservicemessage(v_bentype, v_bemid, v_snils, v_oid);
                    
                    PERFORM PG_SLEEP(0.5);
                END LOOP;
        
        END IF;
    
    END IF;

END;
$$