CREATE OR REPLACE PROCEDURE rds.sp_loadanswerfromfrllorequestprocess(
    exportid uuid,
    xml xml
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_exportid uuid := exportid;
    p_xml      xml  := xml;

BEGIN
    
    CREATE TEMP TABLE temp_answer AS
        SELECT (XPATH('/element/document_id/text()', x))[1]::varchar(100)::uuid AS document_id,
               (XPATH('/element/status/text()', x))[1]::varchar(10)::int        AS status,
               (XPATH('/element/reg_num/text()', x))[1]::varchar(100)           AS reg_num,
               (XPATH('/element/pers_needs_id/text()', x))[1]::varchar(100)     AS pers_needs_id,
               (XPATH('/element/code/text()', t))[1]::text                      AS code,
               (XPATH('/element/description/text()', t))[1]::text               AS description
        FROM UNNEST(XPATH('root/result/element', p_xml)) x
        CROSS JOIN LATERAL UNNEST(XPATH('/errors/element', x)) t;
    
    CREATE TEMP TABLE temp_upd
    (
        id                           SERIAL PRIMARY KEY,
        reg_num                      varchar(100),
        status                       integer,
        pers_needs_id                varchar(100),
        error                        text,
        exportfrllodrugrequest       uuid,
        exportfrllodrugrequestitemid uuid
    );
    
    CREATE TEMP TABLE temp_dt
    (
        id int
    );
    
    INSERT INTO
        temp_upd (reg_num, status, pers_needs_id, error, exportfrllodrugrequest, exportfrllodrugrequestitemid)
    SELECT a.reg_num,
           a.status,
           a.pers_needs_id,
           a.error,
           fed.exportfrllodrugrequest,
           fed.exportfrllodrugrequestitemid
    FROM rds.t_export_frllo_drug_request_item fed
    CROSS JOIN LATERAL (
        SELECT a.reg_num,
               a.status,
               MAX(a.pers_needs_id)                                    AS pers_needs_id,
               STRING_AGG(a2.code || '-' || a2.description || '/', '') AS error
        FROM temp_answer a
        LEFT JOIN temp_answer a2 ON a2.document_id = a.document_id
        WHERE fed.exportfrllodrugrequest = p_exportid
          AND a.document_id = fed.exportfrllodrugrequestitemid
        GROUP BY a.document_id, a.reg_num, a.status
        ) a;
    
    WHILE EXISTS (SELECT 1 FROM temp_upd)
        LOOP
            
            WITH cte AS (
                UPDATE fed
                    SET
                        frlloid = u.reg_num,
                        frllostatus = u.status,
                        frllopersneedsid = u.pers_needs_id,
                        frlloerror = u.error
                    FROM rds.t_export_frllo_drug_request_item fed
                        JOIN temp_upd u ON u.exportfrllodrugrequest = fed.exportfrllodrugrequest AND
                                           u.exportfrllodrugrequestitemid = fed.exportfrllodrugrequestitemid
                    RETURNING u.id
                        )
            INSERT
            INTO
                temp_dt(id)
            SELECT id
            FROM cte;
            
            
            UPDATE pdd
            SET frlloid = fed.frlloid
            FROM rds.t_patient_drug_demand pdd
            JOIN rds.t_export_frllo_drug_request_item fed ON fed.objid = pdd.patientdrugdemandid
            JOIN temp_upd u ON u.exportfrllodrugrequest = fed.exportfrllodrugrequest
            WHERE fed.frlloid IS NOT NULL
              AND pdd.frlloid IS NULL
              AND EXISTS(SELECT 1 FROM temp_dt d WHERE d.id = u.id);
            
            DELETE
            FROM temp_upd u
            WHERE EXISTS (SELECT 1 FROM temp_dt d WHERE d.id = u.id);
            
            DELETE FROM temp_dt;
        
        END LOOP;
END;
$$