CREATE OR REPLACE PROCEDURE rds.sp_loadanswerfromfrlloremainsprocess(
    exportid uuid,
    xml xml
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_exportid uuid := exportid;
    p_xml      uuid := 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/stock_id/text()', x))[1]::varchar(100)          AS stock_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
        LEFT JOIN LATERAL UNNEST(XPATH('/errors/element', x)) t ON TRUE;
    
    CREATE TEMP TABLE temp_upd
    (
        id                       serial PRIMARY KEY,
        stock_id                 varchar(100),
        status                   int,
        error                    text,
        exportfrlloremains       uuid,
        exportfrlloremainsdataid int
    );
    
    CREATE TEMP TABLE temp_dt
    (
        id integer
    );
    
    INSERT INTO
        temp_upd (stock_id, status, error, exportfrlloremains, exportfrlloremainsdataid)
    SELECT a.stock_id,
           a.status,
           a.error,
           fed.exportfrlloremains,
           fed.exportfrlloremainsdataid
    FROM rds.t_export_frllo_remains_data fed
    CROSS JOIN LATERAL (
        SELECT a_inner.stock_id,
               a_inner.status,
               STRING_AGG(a2.code || '-' || a2.description || '/', '') AS error,
               a_inner.document_id
        FROM temp_answer a_inner
        LEFT JOIN temp_answer a2
            ON a2.document_id = a_inner.document_id
        WHERE fed.exportfrlloremains = p_exportId
          AND a_inner.document_id = fed.exportfrlloremainsdataid
        GROUP BY a_inner.document_id, a_inner.stock_id, a_inner.status
        ) a;
    
    WHILE (EXISTS (SELECT 1 FROM temp_upd))
        LOOP
            
            WITH cte AS (SELECT upd.stock_id,
                                upd.status,
                                upd.error,
                                upd.id,
                                upd.exportfrlloremains,
                                upd.exportfrlloremainsdataid
                         FROM rds.t_export_frllo_remains_data fed
                         JOIN temp_upd upd ON fed.exportfrlloremains = upd.exportfrlloremains AND
                                              upd.exportfrlloremainsdataid = fed.exportfrlloremainsdataid
                         LIMIT 1000
                        ),
                 cte2 AS (
                     UPDATE fed
                         SET frlloid = cte.stock_id,
                             frllostatus = cte.status,
                             frlloerror = cte.error
                         FROM rds.t_export_frllo_remains_data fed
                         WHERE fed.exportfrlloremainsdataid = cte.exportfrlloremainsdataid AND
                               fed.exportfrlloremains = cte.exportfrlloremains
                         RETURNING cte.id
                        )
            INSERT
            INTO
                temp_dt (id)
            SELECT id
            FROM cte2;
            
            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
$$