CREATE OR REPLACE PROCEDURE RDS.sp_exportfrlloremainsdataprocess(
    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;
BEGIN
    
    CREATE TEMPORARY TABLE temp_outputtable
    (
        val xml
    );
    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", efrd.exportfrlloremainsdataid),
                                           XMLELEMENT(NAME "doc_date_time", CURRENT_TIMESTAMP::text),
                                           XMLELEMENT(
                                               NAME "stock",
                                               XMLFOREST(
                                                   XMLELEMENT(NAME "is_deleted", efrd.isdelete),
                                                   XMLELEMENT(NAME "stock_ext_id", efrd.frlloid),
                                                   XMLELEMENT(NAME "med_org_oid", eh.oid),
                                                   XMLELEMENT(NAME "region", p_region),
                                                   XMLELEMENT(NAME "stock_date", TO_CHAR(efr.createdate, 'mm.yyyy')),
                                                   XMLELEMENT(NAME "drug_klp_code", efrd.klpcode),
                                                   XMLELEMENT(NAME "med_equip_code", efrd.micode),
                                                   XMLELEMENT(NAME "nutrition_code", efrd.speccode),
                                                   XMLELEMENT(NAME "pack_qty", efrd.packqty),
                                                   XMLELEMENT(NAME "item_qty", efrd.itemqty),
                                                   XMLELEMENT(NAME "drug_expiration_dt",
                                                              TO_CHAR(efrd.drugexpirationdt, 'YYYY-MM-DD')),
                                                   XMLELEMENT(NAME "drug_seria", efrd.drugseria)
                                               )
                                           )
                                       )
                                   )
                               )
                        FROM rds.t_export_frllo_remains_data efrd
                        JOIN rds.t_export_frllo_remains efr ON efr.exportfrlloremainsid = efrd.exportfrlloremains
                        JOIN rds.t_pharmacy p ON p.code = efrd.pharmacy
                        LEFT JOIN ehr.t_ehr_rus_mdr308 eh ON eh.ehrrusid = p.ehrrusmdr308
                        WHERE efrd.exportfrlloremains = p_exportid
                          AND ex.filenumber = efrd.filenumber
                          AND (p_withouterror = FALSE OR efrd.frlloerror IS NULL)
                       ))
               )
           )
    FROM rds.t_export_frllo_remains_data ex
    WHERE ex.exportfrlloremains = p_exportid
      AND (p_withouterror = FALSE OR ex.frlloerror IS NULL)
    GROUP BY ex.filenumber;
    
    SELECT XMLELEMENT(NAME "root_list", XMLAGG(val))::text
    INTO result
    FROM temp_outputtable;
END;
$$;