DROP PROCEDURE IF EXISTS RDS.sp_exportfrlloremainsdataprocess;

CREATE OR REPLACE PROCEDURE RDS.sp_exportfrlloremainsdataprocess(
    "ExportId" uuid,
    "InfoSysCode" varchar(100),
    "Region" varchar(5),
    "WithouError" 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      := "WithouError";
BEGIN

    CREATE TEMPORARY TABLE temp_outputtable
    (
        val xml
    );

    INSERT INTO temp_outputtable (val)
    SELECT XMLELEMENT(
               NAME "root",
               XMLFOREST(
                   p_infosyscode as "info_sys_code",
                   XMLELEMENT(
                       NAME "documents",
                       (SELECT XMLAGG(
                                   XMLELEMENT(
                                       NAME "document",
                                       XMLFOREST(
                                           efrd.exportfrlloremainsdataid as "document_id",
                                           TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD') as "doc_date_time",
                                           XMLELEMENT(
                                               NAME "stock",
                                               XMLFOREST(
                                                   efrd.isdelete as "is_delete",
                                                   efrd.frlloid as "stock_ext_id",
                                                   eh.oid as "med_org_oid",
                                                   p_region as "region",
                                                   TO_CHAR(efr.createdate, 'mm.yyyy') as "stock_date",
                                                   efrd.klpcode as "drug_klp_code",
                                                   efrd.micode as "med_equip_code",
                                                   efrd.speccode as "nutrition_code",
                                                   efrd.packqty as "pack_qty",
                                                   efrd.itemqty as "item_qty",
                                                   TO_CHAR(efrd.drugexpirationdt, 'YYYY-MM-DD') as "drug_expiration_dt",
                                                   efrd.drugseria as "drug_seria"
                                               )
                                           )
                                       )
                                   )
                               )
                        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;
$$;