CREATE OR REPLACE PROCEDURE rds.sp_loadanswerfromfrlloprocess(
    exportid uuid,
    xml xml
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml      xml := xml;
    p_exportid int := exportid;
    v_entityid int;
    v_palid    int;
    v_rrid     int;
    v_specid   int;
    v_bskid    int;

BEGIN
    
    SELECT e.entities
    INTO v_entityid
    FROM rds.t_export_frllo e
    WHERE e.exportfrlloid = p_exportid;
    
    SELECT e.entityid
    INTO v_palid
    FROM vclib.t_entities e
    WHERE e.entname = 'PALLIATIVE_REGISTER';
    
    SELECT e.entityid
    INTO v_rrid
    FROM vclib.t_entities e
    WHERE e.entname = 'RR_PATIENT';
    
    SELECT e.entityid
    INTO v_specid
    FROM vclib.t_entities e
    WHERE e.entname = 'SPECIAL_BENEFITS_REGISTR';
    
    SELECT e.entityid
    INTO v_bskid
    FROM vclib.t_entities e
    WHERE e.entname = 'BSK_REGISTER';
    
    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)::integer    AS status,
               (XPATH('/element/reg_num/text()', x))[1]::varchar(100)           AS reg_num,
               (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;
    
    IF ((SELECT COUNT(*) FROM temp_answer) > 0) THEN
        RAISE EXCEPTION 'Структура загружаемого файла не соответствует документации, либо Вы загружаете пустой файл!';
    END IF;
    
    UPDATE rds.t_export_frllo_data fed
    SET frlloid     = a.reg_num,
        frllostatus = a.status,
        frlloerror  = a.error
    FROM (SELECT an.document_id,
                 an.reg_num,
                 an.status,
                 STRING_AGG(a2.code || '-' || a2.description, '/') AS error
          FROM temp_answer an
          LEFT JOIN temp_answer a2
              ON a2.document_id = an.document_id
          WHERE fed.exportfrllo = p_exportid
            AND an.document_id = fed.exportfrllodataid
          GROUP BY an.document_id, an.reg_num, an.status
         ) a
    WHERE fed.exportfrllo = p_exportid
      AND fed.exportfrllodataid = a.document_id;
    
    IF (v_entityid = v_palid) THEN
        UPDATE pal
        SET frlloid = fed.frlloid
        FROM rds.t_palliative_register pal
        JOIN rds.t_export_frllo_data fed ON fed.objid = pal.palliativeregisterid
        WHERE fed.exportfrllo = p_exportid
          AND fed.frlloid IS NOT NULL
          AND pal.frlloid IS NULL;
    END IF;
    
    IF (v_entityid = v_rrid) THEN
        UPDATE rr
        SET frlloid = fed.frlloid
        FROM rds.t_rr_patient rr
        JOIN rds.t_export_frllo_data fed ON fed.objid = rr.id
        WHERE fed.exportfrllo = p_exportid
          AND fed.frlloid IS NOT NULL
          AND rr.frlloid IS NULL;
    END IF;
    
    IF (v_entityid = v_specid) THEN
        UPDATE spec
        SET frlloid = fed.frlloid
        FROM rds.t_special_benefits_registr spec
        JOIN rds.t_export_frllo_data fed ON fed.objid = spec.specialbenefitsregistrid
        WHERE fed.exportfrllo = p_exportid
          AND fed.frlloid IS NOT NULL
          AND spec.frlloid IS NULL;
    END IF;
    
    IF (v_entityid = v_bskid) THEN
        UPDATE bsk
        SET frlloid = frlloid
        FROM rds.t_bsk_register bsk
        JOIN rds.t_export_frllo_data fed ON fed.objid = bsk.bskregisterid
        WHERE fed.exportfrllo = p_exportid
          AND fed.frlloid IS NOT NULL
          AND bsk.frlloid IS NULL;
    END IF;
END;
$$