DROP PROCEDURE IF EXISTS RDS.sp_exportfrlloremainsdataprocess;

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",
                XMLELEMENT(NAME "info_sys_code", p_infosyscode),
                XMLELEMENT(
                       NAME "documents",
                       (SELECT XMLAGG(
                                   XMLELEMENT(
                                       NAME "document",
                                       XMLELEMENT(NAME "document_id", ex.exportfrlloremainsdataid),
                                       XMLELEMENT(NAME "doc_date_time", TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DDTHH24:MI:SS')),
                                       (
                                            SELECT XMLAGG
                                            (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;
$$;