Convert amount to letters in iDempiere

 /**************************************************************/

 --METHOD # 1: JAVA METHOD

 /**************************************************************/
 http://www.adempiere.com/ADempiere/Compiere_JasperReports_Integration_HowTo#Amount_In_Words

 /**************************************************************/

 --METHOD # 2: POSTGRESQL FUNCTION

 /**************************************************************/

-- SELECT MAX(UPPER(F_CONVNL(100.00, 'C$'))) AS amount_in_letters

-- SELECT MAX(UPPER(F_CONVNL(100.00::NUMERIC(10, 2), 'US$'))) AS amount_in_letters
 
-- FUNCTION: adempiere.f_convnl(numeric, character varying)

-- DROP FUNCTION adempiere.f_convnl(numeric, character varying);

CREATE OR REPLACE FUNCTION adempiere.f_convnl(
    num numeric,
    moneda character varying)
    RETURNS character varying
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
-- Función que devuelve la cadena de texto en castellano que corresponde a un número.
-- Parámetros: número con 2 decimales, máximo 999.999.999,99.

DECLARE
    d VARCHAR[];
    f VARCHAR[];
    g VARCHAR[];
    numt VARCHAR;
    txt VARCHAR;
    a INTEGER;
    a1 INTEGER;
    a2 INTEGER;
    n INTEGER;
    p INTEGER;
    negativo BOOLEAN;
    tipo_moneda character varying;
    numero numeric(10,2);
BEGIN
    -- Máximo 999.999.999,99
    IF num > 999999999.99 THEN
        RETURN '---';
    END IF;
    txt = '';
    d = ARRAY[' un',' dos',' tres',' cuatro',' cinco',' seis',' siete','
ocho',' nueve',' diez',' once',' doce',' trece',' catorce',' quince',
        ' dieciseis',' diecisiete',' dieciocho',' diecinueve',' veinte','
veintiun',' veintidos', ' veintitres', ' veinticuatro', ' veinticinco',
        ' veintiseis',' veintisiete',' veintiocho',' veintinueve'];
    f = ARRAY ['','',' treinta',' cuarenta',' cincuenta',' sesenta','
setenta',' ochenta', ' noventa'];
    g= ARRAY [' ciento',' doscientos',' trescientos',' cuatrocientos','
quinientos',' seiscientos',' setecientos',' ochocientos','
novecientos'];

    numt = LPAD((num::numeric(12,2))::text,12,'0');
    IF strpos(numt,'-') > 0 THEN
       negativo = TRUE;
    ELSE
       negativo = FALSE;
    END IF;
    numt = TRANSLATE(numt,'-','0');
    numt = TRANSLATE(numt,'.,','');
    -- Trato 4 grupos: millones, miles, unidades y decimales
    p = 1;
    FOR i IN 1..4 LOOP
        IF i < 4 THEN
            n = substring(numt::text FROM p FOR 3);
        ELSE
            n = substring(numt::text FROM p FOR 2);
        END IF;
        p = p + 3;
        IF i = 4 THEN
            IF txt = '' THEN
                txt = ' cero';
            END IF;
            IF n > 0 THEN
            -- Empieza con los decimales
                txt = txt || ' con';
            END IF;
        END IF;
        -- Centenas
        IF n > 99 THEN
            a = substring(n::text FROM 1 FOR 1);
            a1 = substring(n::text FROM 2 FOR 2);
            IF a = 1 THEN
                IF a1 = 0 THEN
                    txt = txt || ' cien';
                ELSE
                    txt = txt || ' ciento';
                END IF;
            ELSE
                txt = txt || g[a];
            END IF;
        ELSE
            a1 = n;
        END IF;
        -- Decenas
        a = a1;
        IF a > 0 THEN
            IF a < 30 THEN
                IF a = 21 AND (i = 3 OR i = 4) THEN
                    txt = txt || ' veintiuno';
                ELSIF n = 1 AND i = 2 THEN
                    txt = txt;
                ELSIF a = 1 AND (i = 3 OR i = 4)THEN
                    txt = txt || ' uno';
                ELSE
                    txt = txt || d[a];
                END IF;
            ELSE
                a1 = substring(a::text FROM 1 FOR 1);
                a2 = substring(a::text FROM 2 FOR 1);
                IF a2 = 1 AND (i = 3 OR i = 4) THEN
                        txt = txt || f[a1] || ' y' || ' uno';
                ELSE
                    IF a2 <> 0 THEN
                        txt = txt || f[a1] || ' y' || d[a2];
                    ELSE
                        txt = txt || f[a1];
                    END IF;
                END IF;
            END IF;
        END IF;
        IF n > 0 THEN
            IF i = 1 THEN
                IF n = 1 THEN
                    txt = txt || ' millón';
                ELSE
                    txt = txt || ' millones';
                END IF;
            ELSIF i = 2 THEN
                txt = txt || ' mil';
            END IF;
        END IF;
    END LOOP;

    txt = LTRIM(txt);

    IF negativo = TRUE THEN
       txt= '-' || txt;
    END IF;

    /* AQUI COMIENZA LA MODIFICACION DE LA FUNCION */
    /* FECHA 15/12/2011 13:01 PM BY jfrancisco.fullstackdev@gmail.com*/

    numero = cast(num as numeric(10,2));

    tipo_moneda = case when moneda = 'C$' then 'cordobas' when moneda = 'US$' then 'dolares' end;

    if position(' con ' in txt) > 0 then
        txt = substring(txt, 0, position(' con ' in txt)) || ' ' || tipo_moneda || ' con ' ||
        (select substring(cast(numero as text), position('.' in cast(numero as
text)) + 1, length(cast(numero as text)))) || '/100';
    else
        txt = txt || ' ' || tipo_moneda || ' netos';
    end if;

    /* ************************************************************ */

     RETURN upper(regexp_replace(txt, '\r|\n', '', 'g'));
END;
$BODY$;

ALTER FUNCTION adempiere.f_convnl(numeric, character varying)
    OWNER TO adempiere;