Get DocumentType From FactAcct Generated By AI

 
-- select * from adempiere.fnt_getdocumenttypebyid(318, 1546039);

CREATE OR REPLACE FUNCTION fnt_getdocumenttypebyid(table_id numeric(10,0), record_id numeric(10,0))
RETURNS varchar AS $$
DECLARE
    table_name TEXT;
    select_query TEXT;
    result RECORD;
    document_type VARCHAR;
BEGIN
    -- Obtiene el nombre de la tabla a partir de ad_table_id
    SELECT t.TableName
    INTO table_name
    FROM AD_Table t
    WHERE t.AD_Table_ID = table_id;

    -- Construye la consulta SELECT
    select_query := 'SELECT C_DocType_ID FROM ' || table_name ||
        ' WHERE ' || table_name || '_ID = ' || record_id || ';';

    -- Ejecuta la consulta y obtiene el C_DocType_ID
    EXECUTE select_query INTO document_type;

    -- Obtiene el nombre del tipo de documento
    SELECT dt.Name
    INTO document_type
    FROM C_DocType dt
    WHERE dt.C_DocType_ID = document_type::int;

    RETURN document_type;
END;
$$ LANGUAGE plpgsql;