Restore Closed Record on iDempiere

 
-- select * from adempiere.fnt_restore_closed_record(224, 1029515);

-- select * from adempiere.fnt_restore_closed_record(321, 1016989);

drop function fnt_restore_closed_record(p_table varchar, p_record_id numeric(10,0));

create or replace function fnt_restore_closed_record(p_table_id numeric(10,0), p_record_id numeric(10,0))
returns varchar
as $$
declare
    v_tablename varchar;
    v_docstatus varchar;
    stmt varchar;
begin
    select
        t.tablename
    into
        v_tablename
    from ad_table t
    where t.ad_table_id = p_table_id;

    stmt = 'SELECT DocStatus FROM ' || v_tablename || ' WHERE ' || v_tablename || '_ID = ' || p_record_id || ';';
    
    -- RAISE NOTICE 'Printed Query %', stmt;
    
    EXECUTE stmt INTO v_docstatus;
    
    if(v_docstatus = 'CL')then
        stmt = 'UPDATE ' || v_tablename || ' SET DocStatus = ''CO'', DocAction = ''CL'' WHERE ' || v_tablename || '_ID = ' || p_record_id || ';';
        
        EXECUTE stmt;
    end if;
    
    RETURN true;
end;
$$ language plpgsql;