CREATE OR REPLACE PROCEDURE public.sp_createvznservicemessage(
    messagetype varchar(100),
    objid uuid,
    snils varchar(14),
    oid text,
    dopid uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_searchpreviousmessage boolean      := FALSE;
    v_xml                   xml;
    p_messagetype           varchar(100) := messagetype;
    p_objid                 uuid         := objid;
    p_snils                 varchar(14)  := snils;
    p_oid                   text         := oid;
    p_dopid                 uuid DEFAULT NULL;

BEGIN
    
    IF (p_messagetype = 'patient.create') THEN
        
        v_searchpreviousmessage := TRUE;
        
        SELECT XMLELEMENT
               (
                   NAME "patient",
                   XMLFOREST
                   (
                       XMLELEMENT(NAME "snils", REPLACE(REPLACE(nosology.snils, '-', ''), ' ', '')),
                       XMLELEMENT(NAME "firstName", nosology.firstname),
                       XMLELEMENT(NAME "lastName", nosology.lastname),
                       XMLELEMENT(NAME "patronymic", nosology.fathername),
                       XMLELEMENT(NAME "gender", TO_CHAR(nosology.birthday, 'YYYY.MM.DD')),
                       XMLELEMENT(NAME "citizenShipId", XMLELEMENT(NAME "@id", nosology.birthdaylastname)),
                       XMLELEMENT(NAME "workPlace", nosology.workplace),
                       XMLELEMENT(NAME "birthPlace", nosology.birthplace),
                       XMLELEMENT(
                           NAME "documents",
                           XMLELEMENT
                           (
                               NAME "document",
                               XMLFOREST
                               (
                                   XMLELEMENT(NAME "serial", REPLACE(REPLACE(nosology.docseries, '-', ''), ' ', '')),
                                   XMLELEMENT(NAME "number", nosology.docnumber),
                                   XMLELEMENT(NAME "passOrg", nosology.docissueorg),
                                   XMLELEMENT(NAME "documentId", XMLELEMENT(NAME "@id", ehr.idehr)),
                                   XMLELEMENT(NAME "passDate", TO_CHAR(nosology.docissuedate, 'YYYY.MM.DD'))
                               )
                           )
                       )
                   )
               )
        INTO v_xml
        FROM rds.t_nosology nosology
        LEFT JOIN vclib.t_enums esex ON esex.enumid = nosology.sex
        LEFT JOIN ehr.t_ehr_rus_o00015 eh ON eh.ehrrusid = nosology.citizenship
        LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = nosology.documenttype
        LEFT JOIN ehr.t_ehr_rus_nk0469 ehr ON dul.ehrrusnk0469 = ehr.ehrrusid
        WHERE nosology.nosologyid = p_objid;
    
    ELSIF (p_messagetype = 'patient.update') THEN
        
        v_searchpreviousmessage := TRUE;
        
        SELECT XMLELEMENT
               (
                   NAME "updatePatient",
                   XMLFOREST
                   (
                       XMLELEMENT(NAME "key",
                                  XMLELEMENT(NAME "snils", REPLACE(REPLACE(nosology.snils, '-', ''), ' ', ''))),
                       XMLELEMENT
                       (
                           NAME "patient",
                           XMLFOREST
                           (
                               XMLELEMENT(NAME "firstName", nosology.firstname),
                               XMLELEMENT(NAME "lastName", nosology.lastname),
                               XMLELEMENT(NAME "patronymic", nosology.fathername),
                               XMLELEMENT(NAME "gender", esex.code),
                               XMLELEMENT(NAME "birthDate", TO_CHAR(nosology.birthday, 'YYYY.MM.DD')),
                               XMLELEMENT(NAME "citizenShipId", XMLELEMENT(NAME "@id", eh.idehr)),
                               XMLELEMENT(NAME "birthLastName", nosology.birthdaylastname),
                               XMLELEMENT(NAME "workPlace", nosology.workplace),
                               XMLELEMENT(NAME "birthPlace", nosology.birthplace),
                               XMLELEMENT(
                                   NAME "patient",
                                   XMLELEMENT
                                   (
                                       NAME "documents",
                                       XMLELEMENT
                                       (
                                           NAME "document",
                                           XMLFOREST
                                           (
                                               XMLELEMENT(NAME "serial",
                                                          REPLACE(REPLACE(nosology.docseries, '-', ''), ' ', '')),
                                               XMLELEMENT(NAME "number", nosology.docnumber),
                                               XMLELEMENT(NAME "passOrg", nosology.docissueorg),
                                               XMLELEMENT(NAME "documentId",
                                                          XMLELEMENT(NAME "@id", nosology.docissueorg)),
                                               XMLELEMENT(NAME "passDate", TO_CHAR(nosology.docissuedate, 'YYYY.MM.DD'))
                                           )
                                       )
                                   )
                               )
                           )
                       )
                   )
               )
        INTO v_xml
        FROM rds.t_nosology nosology
        LEFT JOIN vclib.t_enums esex ON esex.enumid = nosology.sex
        LEFT JOIN ehr.t_ehr_rus_o00015 eh ON eh.ehrrusid = nosology.citizenship
        LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = nosology.documenttype
        LEFT JOIN ehr.t_ehr_rus_nk0469 ehr ON dul.ehrrusnk0469 = ehr.ehrrusid
        WHERE nosology.nosologyid = p_objid;
    
    
    ELSIF (p_messagetype = 'patient_document.update') THEN
        
        v_searchpreviousmessage := TRUE;
        
        SELECT XMLELEMENT
               (
                   NAME "updateDocument",
                   XMLFOREST
                   (
                       XMLELEMENT(NAME "documentKey",
                                  XMLELEMENT(NAME "snils", REPLACE(REPLACE(nosology.snils, '-', ''), ' ', ''))),
                       XMLELEMENT(NAME "documentId", XMLELEMENT(NAME "@id", ehr.idehr)),
                       XMLELEMENT
                       (
                           NAME "document",
                           XMLFOREST
                           (
                               XMLELEMENT(NAME "serial", nosology.docseries),
                               XMLELEMENT(NAME "number", nosology.docnumber),
                               XMLELEMENT(NAME "number", TO_CHAR(nosology.docissuedate, 'YYYY.MM.DD')),
                               XMLELEMENT(NAME "passOrg", nosology.docissueorg)
                           )
                       )
                   )
               )
        INTO v_xml
        FROM rds.t_nosology nosology
        LEFT JOIN vclib.t_enums esex ON esex.enumid = nosology.sex
        LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = nosology.documenttype
        LEFT JOIN ehr.t_ehr_rus_nk0469 ehr ON dul.ehrrusnk0469 = ehr.ehrrusid
        WHERE nosology.nosologyid = p_objid;
    
    ELSIF (p_messagetype = 'patient_document.create') THEN
        
        v_searchpreviousmessage := TRUE;
        
        SELECT XMLELEMENT
               (
                   NAME "createDocuments",
                   XMLFOREST
                   (
                       XMLELEMENT(NAME "key",
                                  XMLELEMENT(NAME "snils", REPLACE(REPLACE(nosology.snils, '-', ''), ' ', ''))),
                       XMLELEMENT
                       (
                           NAME "documents",
                           XMLELEMENT
                           (
                               NAME "document",
                               XMLFOREST
                               (
                                   XMLELEMENT(NAME "documentId", XMLELEMENT(NAME "@id", ehr.idehr)),
                                   XMLELEMENT(NAME "serial", REPLACE(REPLACE(nosology.docseries, '-', ''), ' ', '')),
                                   XMLELEMENT(NAME "number", nosology.docnumber),
                                   XMLELEMENT(NAME "passDate", TO_CHAR(nosology.docissuedate, 'YYYY.MM.DD')),
                                   XMLELEMENT(NAME "passOrg", nosology.docissueorg)
                               )
                           )
                       )
                   )
               )
        INTO v_xml
        FROM rds.t_nosology nosology
        LEFT JOIN vclib.t_enums esex ON esex.enumid = nosology.sex
        LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = nosology.documenttype
        LEFT JOIN ehr.t_ehr_rus_nk0469 ehr ON dul.ehrrusnk0469 = ehr.ehrrusid
        WHERE nosology.nosologyid = p_objid;
    
    ELSIF (p_messagetype = 'vzn.create') THEN
        
        v_searchpreviousmessage := TRUE;
        
        SELECT XMLELEMENT
               (
                   NAME "createVzn",
                   XMLFOREST
                   (
                       XMLELEMENT(NAME "key",
                                  XMLELEMENT(NAME "snils", REPLACE(REPLACE(nosology.snils, '-', ''), ' ', ''))),
                       XMLELEMENT(NAME "moId", mdrinc.oid),
                       XMLELEMENT(NAME "includeDate", TO_CHAR(nosology.dateofinclusion, 'YYYY.MM.DD')),
                       XMLELEMENT
                       (
                           NAME "registry",
                           XMLFOREST
                           (
                               XMLELEMENT(NAME "policTypeID", XMLELEMENT(NAME "@id", CASE
                                                                                         WHEN nosology.policyform = 1
                                                                                             THEN 1
                                                                                         WHEN nosology.policyform = 2
                                                                                             THEN 3
                                                                                         WHEN nosology.policyform = 3
                                                                                             THEN 3
                                                                                         WHEN nosology.policyform = 4
                                                                                             THEN 2
                                                                                     END)),
                               XMLELEMENT(NAME "policSerial", nosology.omsserial),
                               XMLELEMENT(NAME "policNumber", nosology.omsnumber),
                               XMLELEMENT(NAME "imcCode", nosology.f002smo),
                               XMLELEMENT(NAME "registryNumber", nosologybenefits.uniquenumber),
                               XMLELEMENT(NAME "isInclRegistry", nosology.exemptionby178),
                               XMLELEMENT(NAME "isDrugSupply", nosology.exemptionby890),
                               XMLELEMENT
                               (
                                   NAME "deseases",
                                   XMLELEMENT
                                   (
                                       NAME "desease",
                                       XMLFOREST
                                       (
                                           XMLELEMENT(NAME "code", nosologybenefits.mkb),
                                           XMLELEMENT(NAME "beginDate",
                                                      TO_CHAR(nosologybenefits.datebegin, 'YYYY.MM.DD')),
                                           XMLELEMENT(NAME "endDate", TO_CHAR(nosologybenefits.enddate, 'YYYY.MM.DD'))
                                       )
                                   )
                               ),
                               XMLELEMENT
                               (
                                   NAME "addresses",
                                   XMLELEMENT
                                   (
                                       NAME "patientAddress",
                                       XMLELEMENT
                                       (
                                           NAME "address",
                                           XMLFOREST
                                           (
                                               XMLELEMENT(NAME "aoidArea", LOWER(nosology.fiasaddress)),
                                               XMLELEMENT(NAME "aoidStreet", nosology.fiasstreet),
                                               XMLELEMENT(NAME "houseid", nosology.fiashome),
                                               XMLELEMENT(NAME "region",
                                                          XMLELEMENT(NAME "@id", SUBSTR(region.code, 0, 2))),
                                               XMLELEMENT(NAME "areaName", nosology.city),
                                               XMLELEMENT(NAME "prefixArea", nosology.cityprefix),
                                               XMLELEMENT(NAME "streetName", nosology.streetprefix),
                                               XMLELEMENT(NAME "house", nosology.house),
                                               XMLELEMENT(NAME "addressTypeId", XMLELEMENT(NAME "@id", 2))
                                           )
                                       )
                                   )
                               ),
                               XMLELEMENT
                               (
                                   NAME "registryOperationId",
                                   XMLELEMENT(NAME "@id", nosology.operationcode)
                               ),
                               XMLELEMENT
                               (
                                   NAME "territoryId",
                                   XMLELEMENT
                                   (
                                       NAME "@id",
                                       nosology.executiveauthority
                                   )
                               ),
                               XMLELEMENT(NAME "excludeDate", TO_CHAR(nosology.dateofinclusion, 'YYYY.MM.DD')),
                               XMLELEMENT(NAME "deathDate", TO_CHAR(nosology.deathdate, 'YYYY.MM.DD')),
                               XMLELEMENT(NAME "disabilityGroupId", ehrp360.codeehr),
                               XMLELEMENT(NAME "excludeReasonId", nosology.excludereason),
                               XMLELEMENT(NAME "excludeMoId", mdrdec.oid),
                               XMLELEMENT(NAME "note", nosology.comment)
                           )
                       )
                   )
               )
        INTO v_xml
        FROM rds.t_nosology_benefits nosologybenefits
        LEFT JOIN rds.t_nosology nosology ON nosology.nosologyid = nosologybenefits.nosology
        LEFT JOIN vclib.t_enums esex ON esex.enumid = nosology.sex
        LEFT JOIN ehr.t_ehr_rus_o00015 eh ON eh.ehrrusid = nosology.citizenship
        LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = nosology.documenttype
        LEFT JOIN ehr.t_ehr_rus_nk0469 ehr ON dul.ehrrusnk0469 = ehr.ehrrusid
        LEFT JOIN rds.t_lpu_main lpudec ON nosology.declusionlpu = lpudec.mcod
        LEFT JOIN ehr.t_ehr_rus_mdr308 mdrdec ON lpudec.ehrrusmdr308dop = mdrdec.ehrrusid
        LEFT JOIN rds.t_lpu_main lpuinc ON nosology.inclusionlpu = lpuinc.mcod
        LEFT JOIN ehr.t_ehr_rus_mdr308 mdrinc ON lpuinc.ehrrusmdr308dop = mdrinc.ehrrusid
        LEFT JOIN ehr.t_ehr_rus_fr7366 ehrfr7366 ON nosology.ehrrusfr7366 = ehrfr7366.ehrrusid
        LEFT JOIN rds.t_region region ON nosology.region = region.regionid
        LEFT JOIN ehr.t_ehr_rus_p360coa3ob ehrp360 ON nosologybenefits.ehrrusp360coa3ob = ehrp360.ehrrusid
        WHERE nosologybenefits.nosologybenefitsid = p_objid;
    
    ELSIF (p_messagetype = 'vzn.update') THEN
        
        v_searchpreviousmessage := TRUE;
        
        SELECT XMLELEMENT
               (
                   NAME "updateVzn",
                   XMLFOREST
                   (
                       XMLELEMENT(NAME "moId", mdrinc.oid),
                       XMLELEMENT(NAME "includeDate", TO_CHAR(nosology.dateofinclusion, 'YYYY.MM.DD')),
                       XMLELEMENT(NAME "exlcudeDate", TO_CHAR(nosology.dateofdeclusion, 'YYYY.MM.DD')),
                       XMLELEMENT
                       (
                           NAME "registry",
                           XMLFOREST
                           (
                               XMLELEMENT(NAME "registryNumber", nosologybenefits.uniquenumber),
                               XMLELEMENT
                               (
                                   NAME "policTypeId",
                                   XMLELEMENT(NAME "@id", CASE
                                                              WHEN nosology.policyform = 1
                                                                  THEN 1
                                                              WHEN nosology.policyform = 2
                                                                  THEN 3
                                                              WHEN nosology.policyform = 3
                                                                  THEN 3
                                                              WHEN nosology.policyform = 4
                                                                  THEN 2
                                                          END)
                               ),
                               XMLELEMENT(NAME "policSerial", nosology.omsserial),
                               XMLELEMENT(NAME "policNumber", nosology.omsnumber),
                               XMLELEMENT(NAME "imcCode", nosology.f002smo),
                               XMLELEMENT(NAME "isInclRegistry", nosology.exemptionby178),
                               XMLELEMENT(NAME "isDrugSupply", nosology.exemptionby890),
                               XMLELEMENT
                               (
                                   NAME "deseases",
                                   XMLELEMENT
                                   (
                                       NAME "desease",
                                       XMLFOREST
                                       (
                                           XMLELEMENT(NAME "code", nosologybenefits.mkb),
                                           XMLELEMENT(NAME "beginDate",
                                                      TO_CHAR(nosologybenefits.datebegin, 'YYYY.MM.DD')),
                                           XMLELEMENT(NAME "endDate", TO_CHAR(nosologybenefits.enddate, 'YYYY.MM.DD'))
                                       )
                                   )
                               ),
                               XMLELEMENT
                               (
                                   NAME "addresses",
                                   XMLELEMENT
                                   (
                                       NAME "patientAddress",
                                       XMLELEMENT
                                       (
                                           NAME "address",
                                           XMLFOREST
                                           (
                                               XMLELEMENT(NAME "aoidArea", LOWER(nosology.fiasaddress)),
                                               XMLELEMENT(NAME "aoidStreet", nosology.fiasstreet),
                                               XMLELEMENT(NAME "houseid", nosology.fiashome),
                                               XMLELEMENT(NAME "region", XMLELEMENT(NAME "@id", region.code)),
                                               XMLELEMENT(NAME "areaName", nosology.city),
                                               XMLELEMENT(NAME "prefixArea", nosology.cityprefix),
                                               XMLELEMENT(NAME "streetName", nosology.street),
                                               XMLELEMENT(NAME "prefixStreet", nosology.streetprefix),
                                               XMLELEMENT(NAME "house", nosology.house),
                                               XMLELEMENT(NAME "addressTypeId", XMLELEMENT(NAME "@id", 2))
                                           )
                                       )
                                   )
                               )
                           ),
                           XMLELEMENT(NAME "registryOperationId", XMLELEMENT(NAME "@id", nosology.operationcode)),
                           XMLELEMENT(NAME "territoryId", XMLELEMENT(NAME "@id", nosology.executiveauthority)),
                           XMLELEMENT(NAME "signedPerson", nosologybenefits.head),
                           XMLELEMENT(NAME "deathDate", TO_CHAR(nosology.deathdate, 'YYYY.MM.DD')),
                           XMLELEMENT(NAME "disabilityGroupId", ehrp360.codeehr),
                           XMLELEMENT(NAME "excludeReasonId", XMLELEMENT(NAME "@id", nosology.excludereason)),
                           XMLELEMENT(NAME "excludeMoId", mdrdec.oid),
                           XMLELEMENT(NAME "note", nosology.comment)
                       )
                   )
               )
        INTO v_xml
        FROM rds.t_nosology_benefits nosologybenefits
        LEFT JOIN rds.t_nosology nosology ON nosology.nosologyid = nosologybenefits.nosology
        LEFT JOIN vclib.t_enums esex ON esex.enumid = nosology.sex
        LEFT JOIN ehr.t_ehr_rus_o00015 eh ON eh.ehrrusid = nosology.citizenship
        LEFT JOIN ehr.t_ehr_type_matching_dul dul ON dul.doctype = nosology.documenttype
        LEFT JOIN ehr.t_ehr_rus_nk0469 ehr ON dul.ehrrusnk0469 = ehr.ehrrusid
        LEFT JOIN rds.t_lpu_main lpudec ON nosology.declusionlpu = lpudec.mcod
        LEFT JOIN ehr.t_ehr_rus_mdr308 mdrdec ON lpudec.ehrrusmdr308dop = mdrdec.ehrrusid
        LEFT JOIN rds.t_lpu_main lpuinc ON nosology.inclusionlpu = lpuinc.mcod
        LEFT JOIN ehr.t_ehr_rus_mdr308 mdrinc ON lpuinc.ehrrusmdr308dop = mdrinc.ehrrusid
        LEFT JOIN ehr.t_ehr_rus_fr7366 ehrfr7366 ON nosology.ehrrusfr7366 = ehrfr7366.ehrrusid
        LEFT JOIN rds.t_region region ON nosology.region = region.regionid
        LEFT JOIN ehr.t_ehr_rus_p360coa3ob ehrp360 ON nosologybenefits.ehrrusp360coa3ob = ehrp360.ehrrusid
        WHERE nosologybenefits.nosologybenefitsid = p_objid;
    
    ELSIF (p_messagetype = 'vzn_need.create') THEN
        
        v_searchpreviousmessage := FALSE;
        
        DELETE FROM rds.t_message_to_nosology_service s WHERE s.objid = p_objid;
        
        SELECT XMLELEMENT
               (
                   NAME "createNeed",
                   XMLFOREST
                   (
                       XMLELEMENT(NAME "registryNumber", nos.uniquenumber),
                       XMLELEMENT(NAME "appEvent", XMLELEMENT(NAME "@id", eh.idehr)),
                       XMLELEMENT
                       (
                           NAME "nrVznNeedPers",
                           XMLFOREST
                           (
                               XMLELEMENT(NAME "nosGroupId", XMLELEMENT(NAME "@id", ehben.idehr)),
                               XMLELEMENT
                               (
                                   NAME "specs",
                                   XMLELEMENT
                                   (
                                       NAME "spec",
                                       XMLFOREST
                                       (
                                           XMLELEMENT(NAME "mnnId", XMLELEMENT(NAME "@id", ehmnn.idehr)),
                                           XMLELEMENT(NAME "drugFormId", XMLELEMENT(NAME "@id", ehmf.idehr)),
                                           XMLELEMENT(NAME "dosageId", XMLELEMENT(NAME "@id", ehben / eh.idehr)),
                                           XMLELEMENT(NAME "needPerMonth", (pdd.demandonperiod * dali.dosecount) / 12),
                                           XMLELEMENT(NAME "needPerYear", (pdd.demandonperiod * dali.dosecount)),
                                           XMLELEMENT(NAME "singleDose", pdd.signa)
                                       )
                                   )
                               )
                           )
                       )
                   )
               )
        INTO v_xml
        FROM rds.t_patient_drug_demand pdd
        JOIN rds.t_pharmacy_drug_demand phdd ON phdd.pharmacydrugdemandid = pdd.pharmacydrugdemand
        JOIN rds.t_drug_demand dd ON dd.drugdemandid = phdd.drugdemand
        JOIN rds.t_drug_request dr ON dr.drugrequestid = dd.drugrequest
        LEFT JOIN ehr.t_ehr_rus_vzn17 eh ON eh.ehrrusid = dr.ehrrusvzn17
        JOIN rds.t_benefits b ON b.benefitsid = pdd.benefits
        LEFT JOIN LATERAL (
            SELECT nb.uniquenumber
            FROM rds.t_nosology_benefits nb
            JOIN rds.t_nosology n ON n.nosologyid = nb.nosology
            WHERE n.snils = pdd.snils
              AND nb.benefits = pdd.benefits
              AND nb.datebegin <= dr.beginperiod
              AND (nb.enddate IS NULL OR nb.enddate >= dr.endperiod)
              AND (n.dateofdeclusion IS NULL OR n.dateofdeclusion >= dr.beginperiod)
            LIMIT 1) nos ON TRUE
        JOIN rds.t_drug_application_list_item dali ON
            dali.drugapplicationlistitemid = dd.drugapplicationlistitem
        LEFT JOIN pharm.t_c_mnn mnn ON mnn.cmnnid = dali.cmnn
        LEFT JOIN ehr.t_ehr_rus_nsv101 ehmnn ON ehmnn.ehrrusid = mnn.ehrrusnsv101
        LEFT JOIN pharm.t_medication_forms mf ON mf.medicationformsid = dali.medicationforms
        LEFT JOIN ehr.t_ehr_rus_vzn431 ehmf ON ehmf.ehrrusid = mf.ehrrusvzn431
        LEFT JOIN ehr.t_ehr_rus_h17f8uts3n ehben ON ehben.ehrrusid = dali.ehrrush17f8uts3n
        WHERE pdd.patientdrugdemandid = p_objid;
    
    ELSIF (p_messagetype = 'vzn_recipe.create') THEN
        
        v_searchpreviousmessage := FALSE;
        
        DELETE FROM rds.t_message_to_nosology_service s WHERE s.objid = p_objid;
        
        SELECT XMLELEMENT
               (
                   NAME "createVznRecipe",
                   XMLFOREST
                   (
                       XMLELEMENT(NAME "registryNumber", nos.uniquenumber),
                       XMLELEMENT
                       (
                           NAME "recipe",
                           XMLFOREST
                           (
                               XMLELEMENT(NAME "recipeSerial", recipe.serial),
                               XMLELEMENT(NAME "recipeNumber", recipe.number),
                               XMLELEMENT(NAME "issueDate", TO_CHAR(recipe.recipedate, 'YYYY.MM.DD')),
                               XMLELEMENT(NAME "deliveryDate", TO_CHAR(recipe.issuedate, 'YYYY.MM.DD')),
                               XMLELEMENT(NAME "personId", recipe.doctorsnils),
                               XMLELEMENT(NAME "desease", recipe.mkbcode),
                               XMLELEMENT(NAME "mnnId", XMLELEMENT(NAME "@id", ehrmnn.idehr)),
                               XMLELEMENT(NAME "moId", lpu.oid),
                               XMLELEMENT(NAME "pharmacyId", mdr308.oid),
                               XMLELEMENT(NAME "drugFormId", XMLELEMENT(NAME "@id", vzn431.idehr)),
                               XMLELEMENT(NAME "signedPerson", pharmacy.fio_chief),
                               XMLELEMENT(NAME "isTheraphyResistence", nos.therapyresistance),
                               XMLELEMENT(NAME "rels",
                                          (SELECT XMLAGG
                                                  (
                                                      XMLELEMENT
                                                      (
                                                          NAME "released",
                                                          XMLFOREST
                                                          (
                                                              XMLELEMENT(NAME "dosageId", XMLELEMENT(NAME "@id", ehrdv.idehr)),
                                                              XMLELEMENT(NAME "dosageCountId",
                                                                         XMLELEMENT(NAME "@id", ehrdvcount.idehr)),
                                                              XMLELEMENT(NAME "packCount", re.amount),
                                                              XMLELEMENT(NAME "vcnDrugId", XMLELEMENT(NAME "@id", ehrbg.idehr))
                                                          )
                                                      )
                                                  )
                                           FROM rds.t_recipe_element re
                                           LEFT JOIN pharm.t_bill_of_goods bog ON re.billofgoods = bog.billofgoodsid
                                           LEFT JOIN ehr.t_ehr_rus_clp14fvirj ehrbg ON ehrbg.ehrrusid = bog.ehrrusclp14fvirj
                                           LEFT JOIN ehr.t_ehr_rus_h17f8uts3n ehrdv ON ehrbg.ehrrusid = bog.ehrrush17f8uts3n
                                           LEFT JOIN ehr.t_ehr_rus_dsg207 ehrdvcount ON ehrbg.ehrrusid = bog.ehrrusdsg207
                                           WHERE re.recipe = recipe.recipeid
                                          )
                               )
                           )
                       )
                   )
               )
        INTO v_xml
        FROM rds.t_recipe recipe
        LEFT JOIN pharm.t_medication_forms mf ON recipe.medicationforms = mf.medicationformsid
        LEFT JOIN ehr.t_ehr_rus_vzn431 vzn431 ON mf.ehrrusvzn431 = vzn431.ehrrusid
        LEFT JOIN pharm.t_c_mnn mnn ON recipe.cmnn = mnn.cmnnid
        LEFT JOIN ehr.t_ehr_rus_nsv101 ehrmnn ON ehrmnn.ehrrusid = mnn.ehrrusnsv101
        LEFT JOIN rds.t_pharmacy pharmacy ON pharmacy.code = recipe.pharmacy
        LEFT JOIN ehr.t_ehr_rus_mdr308 mdr308 ON pharmacy.ehrrusmdr308 = mdr308.ehrrusid
        LEFT JOIN LATERAL (
            SELECT eh.oid
            FROM rds.t_lpu_main lpu
            LEFT JOIN ehr.t_ehr_rus_mdr308 eh ON eh.ehrrusid = lpu.ehrrusmdr308dop
            WHERE recipe.lpucode = lpu.mcod
               OR eh.oid = recipe.lpucode
            LIMIT 1
            ) lpu ON TRUE
        LEFT JOIN LATERAL
            (
            SELECT nb.therapyresistance, nb.uniquenumber
            FROM rds.t_nosology_benefits nb
            JOIN rds.t_nosology nos ON nos.nosologyid = nb.nosology
            WHERE nos.snils = recipe.snils
              AND nb.benefits = recipe.benefit
              AND nb.datebegin <= recipe.recipedate
              AND (nb.enddate IS NULL OR nb.enddate >= recipe.recipedate)
              AND (nos.dateofdeclusion IS NULL OR nos.dateofdeclusion >= recipe.recipedate)
            LIMIT 1
            ) nos ON TRUE
        WHERE recipe.recipeid = p_objid;
    
    END IF;
    
    INSERT INTO
        rds.t_message_to_nosology_service (messagetonosologyserviceid,
                                           status,
                                           createdate,
                                           senddate,
                                           xml,
                                           idfromservice,
                                           error,
                                           objid,
                                           users,
                                           type,
                                           messagetonosologyservice,
                                           oid,
                                           snils,
                                           exportrecipevznservice)
    VALUES (gen_random_uuid(),
            0,
            CURRENT_TIMESTAMP,
            NULL,
            public.converttexttobinaryzip(v_xml, ''),
            NULL,
            NULL,
            p_objid,
            NULL,
            (CASE
                 WHEN p_messagetype = 'patient.create' THEN 1
                 WHEN p_messagetype = 'patient.update' THEN 2
                 WHEN p_messagetype = 'patient_document.create' THEN 4
                 WHEN p_messagetype = 'patient_document.update' THEN 5
                 WHEN p_messagetype = 'vzn.create' THEN 14
                 WHEN p_messagetype = 'vzn.update' THEN 15
                 WHEN p_messagetype = 'vzn_need.create' THEN 20
                 WHEN p_messagetype = 'vzn_recipe.create' THEN 18
             END),
            (CASE
                 WHEN v_searchpreviousmessage = 1 THEN (SELECT mtns.messagetonosologyserviceid
                                                        FROM rds.t_message_to_nosology_service mtns
                                                        WHERE mtns.snils = p_snils
                                                        ORDER BY mtns.createdate DESC
                                                        LIMIT 1
                 )
                 ELSE NULL
             END),
            p_oid,
            p_snils,
            p_dopid);

END;
$$