CREATE OR REPLACE PROCEDURE rds.sp_exportfrllorequestdataprocess(
    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 TEMP 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", efd.exportfrllodrugrequestitemid),
                                           XMLELEMENT(NAME "doc_date_time", CURRENT_TIMESTAMP::text),
                                           (SELECT XMLELEMENT
                                                   (
                                                       NAME "citizen",
                                                       XMLFOREST
                                                       (
                                                           XMLELEMENT(NAME "name", pdd.firstname),
                                                           XMLELEMENT(NAME "surname", pdd.lastname),
                                                           XMLELEMENT(NAME "patronymic", pdd.fathername),
                                                           XMLELEMENT(NAME "birthdate", TO_CHAR(pdd.birthday, 'YYYY-MM-DD')),
                                                           XMLELEMENT(NAME "sex", esex.code),
                                                           XMLELEMENT(NAME "snils",
                                                                      REPLACE(REPLACE(pdd.snils, '-', ''), ' ', '')),
                                                           XMLELEMENT(NAME "policy_sn",
                                                                      CASE
                                                                          WHEN LENGTH(pdd.omsnumber) = 16
                                                                              THEN pdd.omsnumber
                                                                      END),
                                                           XMLELEMENT
                                                           (
                                                               NAME "identify_docs",
                                                               XMLELEMENT
                                                               (
                                                                   NAME "doc",
                                                                   XMLFOREST
                                                                   (
                                                                       XMLELEMENT
                                                                       (
                                                                           NAME "doc_type",
                                                                           CASE
                                                                               WHEN
                                                                                   er.codeehr IS NOT NULL AND
                                                                                   pdd.docnumber IS NOT NULL AND
                                                                                   pdd.docissuedate IS NOT NULL
                                                                                   THEN er.codeehr
                                                                               ELSE NULL
                                                                           END
                                                                       ),
                                                                       XMLELEMENT
                                                                       (
                                                                           NAME "serial",
                                                                           CASE
                                                                               WHEN er.codeehr IS NOT NULL AND
                                                                                    pdd.docnumber IS NOT NULL AND
                                                                                    pdd.docissuedate IS NOT NULL
                                                                                   THEN pdd.docseries
                                                                               ELSE NULL
                                                                           END
                                                                       ),
                                                                       XMLELEMENT
                                                                       (
                                                                           NAME "num",
                                                                           CASE
                                                                               WHEN er.codeehr IS NOT NULL AND
                                                                                    pdd.docnumber IS NOT NULL AND
                                                                                    pdd.docissuedate IS NOT NULL
                                                                                   THEN pdd.docnumber
                                                                               ELSE NULL
                                                                           END
                                                                       ),
                                                                       XMLELEMENT
                                                                       (
                                                                           NAME "date_issue",
                                                                           CASE
                                                                               WHEN er.codeehr IS NOT NULL AND
                                                                                    pdd.docnumber IS NOT NULL AND
                                                                                    pdd.docissuedate IS NOT NULL
                                                                                   THEN TO_CHAR(pdd.docissuedate, 'YYYY-MM-DD')
                                                                               ELSE NULL
                                                                           END
                                                                       ),
                                                                       XMLELEMENT
                                                                       (
                                                                           NAME "authority",
                                                                           CASE
                                                                               WHEN er.codeehr IS NOT NULL
                                                                                   THEN pdd.docissueorg
                                                                               ELSE NULL
                                                                           END
                                                                       )
                                                                   )
                                                               )
                                                           ),
                                                           XMLELEMENT(NAME "region", p_region)
                                                       )
                                                   ),
                                                   XMLELEMENT
                                                   (
                                                       NAME "plan",
                                                       XMLFOREST
                                                       (
                                                           XMLELEMENT(NAME "ext_id", pdd.patientdrugdemandid),
                                                           XMLELEMENT(NAME "appl_prcd_code", erzk.codeehr),
                                                           XMLELEMENT(NAME "benefit_code", benehr.codeehr),
                                                           XMLELEMENT
                                                           (
                                                               NAME "needs",
                                                               XMLELEMENT(
                                                                   NAME "need",
                                                                   XMLFOREST
                                                                   (
                                                                       XMLELEMENT(NAME "drug_smnn_code", cmnn.codeehr),
                                                                       XMLELEMENT(NAME "drug_name", trn.namerus),
                                                                       XMLELEMENT(NAME "med_equip_code", medprod.codeehr),
                                                                       XMLELEMENT(NAME "nutrition_code", mednutr.codeehr),
                                                                       XMLELEMENT(NAME "qty", CASE
                                                                                                  WHEN medprod.codeehr IS NOT NULL OR mednutr.codeehr IS NOT NULL
                                                                                                      THEN pdd.requestquantity
                                                                                                  ELSE pdd.requestquantity * dali.dosecount
                                                                                              END)
                                                                   )
                                                               )
                                                           )
                                                       )
                                                   )
                                            FROM rds.t_patient_drug_demand pdd
                                            LEFT JOIN vclib.t_enums esex ON esex.enumid = pdd.sex
                                            LEFT JOIN ehr.t_ehr_rus_5o5e88vhc6 er
                                                ON er.ehrrusid = pdd.ehrrus5o5e88vhc6 AND er.is_dul = 1
                                            LEFT JOIN rds.t_benefits b ON b.benefitsid = pdd.benefits
                                            LEFT JOIN ehr.t_ehr_rus_sdo8u3n5ro benehr ON benehr.ehrrusid = b.ehrrussdo8u3n5ro
                                            LEFT JOIN pharm.t_c_trn trn ON trn.ctrnid = dali.ctrn
                                            LEFT JOIN ehr.t_ehr_rus_9a9g09e8kl cmnn ON cmnn.ehrrusid = dali.ehrrus9a9g09e8kl
                                            LEFT JOIN ehr.t_ehr_rus_p08tsh954n medprod
                                                ON medprod.ehrrusid = trn.ehrrusp08tsh954n
                                            LEFT JOIN ehr.t_ehr_rus_575vsf88og mednutr
                                                ON mednutr.ehrrusid = trn.ehrrus575vsf88og
                                            WHERE pddmain.patientdrugdemandid = pdd.patientdrugdemandid
                                           
                                           )
                                       )
                                   )
                               )
                        FROM rds.t_patient_drug_demand pddmain
                        JOIN rds.t_pharmacy_drug_demand phdd
                            ON phdd.pharmacydrugdemandid = pddmain.pharmacydrugdemand
                        JOIN rds.t_drug_demand dd ON dd.drugdemandid = phdd.drugdemand
                        JOIN rds.t_drug_application_list_item dali
                            ON dali.drugapplicationlistitemid = dd.drugapplicationlistitem
                        JOIN rds.t_export_frllo_drug_request_item efd ON
                            efd.objid = pddmain.patientdrugdemandid
                        JOIN rds.t_export_frllo_drug_request ef
                            ON ef.exportfrllodrugrequestid = efd.exportfrllodrugrequest
                        JOIN rds.t_drug_request dr ON dr.drugrequestid = ef.drugrequest
                        LEFT JOIN ehr.t_ehr_rus_ej36pmtjqk erzk ON erzk.ehrrusid = dr.ehrrusej36pmtjqk
                       )
                   )
               )
           )
    FROM rds.t_export_frllo_drug_request_item ex
    WHERE ex.exportfrllodrugrequest = p_exportid
      AND (p_withouterror = FALSE OR ex.frlloerror IS NULL);
    
    
    SELECT XMLELEMENT(NAME "root_list", XMLAGG(val))::text
    INTO result
    FROM temp_outputtable;
END
$$;
