CREATE OR REPLACE PROCEDURE rds.sp_exportprognosisneedsprocess(
    exportid uuid,
    infosyscode varchar(100),
    region varchar(5),
    withouterror boolean,
    OUT result TEXT
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_exportid     uuid         := exportid;
    p_infosyscode  varchar(100) := infosyscode;
    p_region       varchar(5)   := region;
    p_withouterror boolean      := withouterror;
    v_filenumber   integer      := 0;
BEGIN
    
    CREATE TEMP TABLE temp_outputtable
    (
        val xml
    );
    
    WHILE ((SELECT COUNT(*)
            FROM rds.t_export_frllo_needs_element
            WHERE exportfrlloneeds = p_exportid
              AND filenumber IS NULL
           ) > 0)
        LOOP
            
            WITH cte AS
                     (SELECT el.exportfrlloneedselementid AS id,
                             ROW_NUMBER() + v_filenumber  AS filenumber
                      FROM rds.t_export_frllo_needs_element el
                      WHERE exportfrlloneeds = p_exportid
                        AND filenumber IS NULL
                      GROUP BY el.exportfrlloneedselementid
                      LIMIT 100000
                     ),
                 update_result AS
                     (
                         UPDATE rds.t_export_frllo_needs_element r
                             SET filenumber = cte.filenumber
                             FROM cte
                             WHERE r.exportfrlloneedselementid = cte.id
                             RETURNING cte.filenumber
                     )
            SELECT MAX(filenumber)
            INTO v_filenumber
            FROM update_result;
        
        END LOOP;
    
    INSERT INTO temp_outputtable(val)
    SELECT XMLELEMENT(
               NAME "root",
               XMLFOREST(
                   XMLELEMENT(NAME "info_sys_code", p_infosyscode),
                   XMLELEMENT(
                       NAME "documents",
                       (SELECT XMLAGG(
                                   XMLELEMENT(
                                       NAME "document",
                                       XMLFOREST
                                       (
                                           XMLELEMENT(NAME "document_id", need.frllodocumentid),
                                           XMLELEMENT(NAME "doc_date_time", need.docdatetime),
                                           XMLELEMENT(
                                               NAME "prognosis_needs",
                                               XMLFOREST(
                                                   XMLELEMENT(NAME "prognosis_needs_id",
                                                              COALESCE(element.prognosis_needs_id,
                                                                       element.exportfrlloneedselementid)),
                                                   XMLELEMENT(NAME "appl_prcd_code", appl.codeehr),
                                                   XMLELEMENT(NAME "region_okato5", need.region_okato5),
                                                   XMLELEMENT(NAME "benefit_code", bencat.codeehr),
                                                   XMLELEMENT(NAME "mkb10_code", mkb.mkb_code),
                                                   XMLELEMENT(NAME "qty_mkb10_code", element.qty_mkb10_code),
                                                   XMLELEMENT(NAME "qty_benefit", element.qty_benefit),
                                                   XMLELEMENT(
                                                       NAME "equips",
                                                       (SELECT XMLAGG(
                                                                   XMLELEMENT(
                                                                       NAME "equip",
                                                                       XMLFOREST(
                                                                           XMLELEMENT(NAME "equip_id",
                                                                                      COALESCE(equip.equip_id, equip.exportfrlloneedsequipid)),
                                                                           XMLELEMENT(NAME "equip_ext_id", equip.equip_ext_id),
                                                                           XMLELEMENT(NAME "drug_smnn_code", meddevice.codeehr),
                                                                           XMLELEMENT(NAME "drug_name", equip.drug_name),
                                                                           XMLELEMENT(NAME "med_equip_code", medequip.codeehr),
                                                                           XMLELEMENT(NAME "nutrition_code", specnutrition.codeehr),
                                                                           XMLELEMENT(NAME "qty_confirm", equip.qty_confirm)
                                                                       )
                                                                   )
                                                               )
                                                        FROM rds.t_export_frllo_needs_equip equip
                                                        LEFT JOIN ehr.t_ehr_rus_9a9g09e8kl meddevice
                                                            ON meddevice.ehrrusid = equip.ehrrus9a9g09e8kl
                                                        LEFT JOIN ehr.t_ehr_rus_p08tsh954n medequip
                                                            ON medequip.ehrrusid = equip.ehrrusp08tsh954n
                                                        LEFT JOIN ehr.t_ehr_rus_575vsf88og specnutrition
                                                            ON specnutrition.ehrrusid = equip.ehrrus575vsf88og
                                                        WHERE equip.exportfrlloneedselement = element.exportfrlloneedselementid
                                                       )
                                                   )
                                               )
                                           )
                                       )
                                   )
                               )
                        FROM rds.t_export_frllo_needs_element element
                        LEFT JOIN rds.t_export_frllo_needs need ON need.exportfrlloneedsid = element.exportfrlloneeds
                        LEFT JOIN ehr.t_ehr_rus_sdo8u3n5ro bencat ON bencat.ehrrusid = element.ehrrussdo8u3n5ro
                        LEFT JOIN ehr.t_ehr_rus_mkb308 mkb ON mkb.ehrrusid = element.ehrrusmkb308
                        LEFT JOIN ehr.t_ehr_rus_ej36pmtjqk appl ON appl.ehrrusid = element.ehrrusej36pmtjqk
                        WHERE need.exportfrlloneedsid = p_exportid
                          AND needselementmain.filenumber = element.filenumber
                          AND (p_withouterror = FALSE OR element.frlloerror IS NULL)
                       )
                   )
               )
           )
    FROM rds.t_export_frllo_needs_element needselementmain
    WHERE needselementmain.exportfrlloneeds = p_exportid
      AND (p_withouterror = 0 OR needselementmain.frlloerror IS NULL)
    GROUP BY needselementmain.filenumber;
    
    SELECT XMLELEMENT(NAME "root_list", XMLAGG(val))::text
    INTO result
    FROM temp_outputtable;
END;
$$;