El Blogcito de Ricardo Dufoo

PL/SQL para convertir un número a letra

por en Mar.25, 2011, archivado en EBS, Oracle

A veces es necesario convertir cantidades númericas a letras (para hacer cheques o facturas x ejemplo). A continuación una función para lograr esto:

CREATE OR REPLACE FUNCTION NUM_TO_WORD_SP (p_qty number)
RETURN VARCHAR
IS
num_string VARCHAR (250);
zero VARCHAR (2);
errm VARCHAR2 (150);
BEGIN
num_string := ‘ ‘;

IF (100 * (ABS (p_qty) – TRUNC (ABS (p_qty))) < 10) AND (100 * (ABS(p_qty) – TRUNC(ABS(p_qty))) > 0)
THEN
zero := ‘0’;
ELSE
zero := NULL;
END IF;

SELECT DECODE (.00000000001
* (MOD (ABS (p_qty), 1000000000000)
– MOD (ABS (p_qty), 100000000000)),
1,
DECODE (SUBSTR (TO_CHAR (TRUNC (p_qty, 0)), -12, 3),
‘100’, ‘Cien ‘,
‘Ciento ‘),
2,
‘Doscientos ‘,
3,
‘Trescientos ‘,
4,
‘Cuatrocientos ‘,
5,
‘Quinientos ‘,
6,
‘Seiscientos ‘,
7,
‘Setecientos ‘,
8,
‘Ochocientos ‘,
9,
‘Novecientos ‘,
0,
NULL,
‘error’
)
|| DECODE (.0000000001
* (MOD (ABS (p_qty), 100000000000)
– MOD (ABS (p_qty), 10000000000)),
1,
(DECODE (.000000001
* (MOD (ABS (p_qty), 10000000000)
– MOD (ABS (p_qty), 1000000000)),
0,
‘Diez ‘,
1,
‘Once ‘,
2,
‘Doce ‘,
3,
‘Trece ‘,
4,
‘Catorce ‘,
5,
‘Quince ‘,
6,
‘Dieciseis ‘,
7,
‘Diecisiete ‘,
8,
‘Dieciocho ‘,
9,
‘Diecinueve ‘,
‘error’
)),
2,
(DECODE (.000000001
* (MOD (ABS (p_qty), 10000000000)
– MOD (ABS (p_qty), 1000000000)),
0,
‘Veinte ‘,
1,
‘Ventiuno ‘,
2,
‘Veintidos ‘,
3,
‘Veintitres ‘,
4,
‘Veinticuatro ‘,
5,
‘Veinticinco ‘,
6,
‘Veintiseis ‘,
7,
‘Veintisiete ‘,
8,
‘Veintiocho ‘,
9,
‘Veintinueve ‘,
‘error’
)),
3,
‘Treinta ‘,
4,
‘Cuarenta ‘,
5,
‘Cincuenta ‘,
6,
‘Sesenta ‘,
7,
‘Setenta ‘,
8,
‘Ochenta ‘,
9,
‘Noventa ‘,
NULL
)
|| DECODE (.000000001
* (MOD (ABS (p_qty), 10000000000)
– MOD (ABS (p_qty), 1000000000)),
0,
NULL,
(DECODE (.0000000001
* (MOD (ABS (p_qty), 100000000000)
– MOD (ABS (p_qty), 10000000000)),
0,
NULL,
1,
NULL,
2,
NULL,
‘y ‘
))
)
|| DECODE (.0000000001
* (MOD (ABS (p_qty), 100000000000)
– MOD (ABS (p_qty), 10000000000)),
1,
NULL,
2,
NULL,
(DECODE (.000000001
* (MOD (ABS (p_qty), 10000000000)
– MOD (ABS (p_qty), 1000000000)),
1,
‘Un ‘,
2,
‘Dos ‘,
3,
‘Tres ‘,
4,
‘Cuatro ‘,
5,
‘Cinco ‘,
6,
‘Seis ‘,
7,
‘Siete ‘,
8,
‘Ocho ‘,
9,
‘Nueve ‘,
0,
NULL,
‘error’
))
)
|| DECODE (SIGN (ABS (p_qty) – 999.99),
1,
DECODE (.00000000001
* (MOD (ABS (p_qty), 1000000000000)
– MOD (ABS (p_qty), 100000000000)),
0,
DECODE (.0000000001
* (MOD (ABS (p_qty), 100000000000)
– MOD (ABS (p_qty), 10000000000)),
0,
DECODE (.000000001
* (MOD (ABS (p_qty), 10000000000)
– MOD (ABS (p_qty), 1000000000
)),
0,
NULL,
DECODE (SUBSTR (TO_CHAR(TRUNC (p_qty,
0
)),
-10,
1
),
‘1’,
‘Billon ‘,
‘Billones ‘
)
),
‘Billones ‘
),
‘Billones ‘
),
NULL
)
|| DECODE (.00000001
* (MOD (ABS (p_qty), 1000000000)
– MOD (ABS (p_qty), 100000000)),
1,
DECODE (SUBSTR (TO_CHAR (TRUNC (p_qty, 0)), -9, 3),
‘100’, ‘Cien ‘,
‘Ciento ‘),
2,
‘Doscientos ‘,
3,
‘Trescientos ‘,
4,
‘Cuatrocientos ‘,
5,
‘Quinientos ‘,
6,
‘Seiscientos ‘,
7,
‘Setecientos ‘,
8,
‘Ochocientos ‘,
9,
‘Novecientos ‘,
0,
NULL,
‘error’
)
|| DECODE (.0000001
* (MOD (ABS (p_qty), 100000000)
– MOD (ABS (p_qty), 10000000)),
1,
(DECODE (.000001
* (MOD (ABS (p_qty), 10000000)
– MOD (ABS (p_qty), 1000000)),
0,
‘Diez ‘,
1,
‘Once ‘,
2,
‘Doce ‘,
3,
‘Trece ‘,
4,
‘Catorce ‘,
5,
‘Quince ‘,
6,
‘Dieciseis ‘,
7,
‘Diecisiete ‘,
8,
‘Dieciocho ‘,
9,
‘Diecinueve ‘,
‘ERROR’
)),
2,
(DECODE (.000001
* (MOD (ABS (p_qty), 10000000)
– MOD (ABS (p_qty), 1000000)),
0,
‘Veinte ‘,
1,
‘Veintiun ‘,
2,
‘Veintidos ‘,
3,
‘Veintitres ‘,
4,
‘Veinticuatro ‘,
5,
‘Veinticinco ‘,
6,
‘Veintiseis ‘,
7,
‘Veintisiete ‘,
8,
‘Veintiocho ‘,
9,
‘Veintinueve ‘,
‘ERROR’
)),
3,
‘Treinta ‘,
4,
‘Cuarenta ‘,
5,
‘Cincuenta ‘,
6,
‘Sesenta ‘,
7,
‘Setenta ‘,
8,
‘Ochenta ‘,
9,
‘Noventa ‘,
NULL
)
|| DECODE (.000001
* (MOD (ABS (p_qty), 10000000)
– MOD (ABS (p_qty), 1000000)),
0,
NULL,
(DECODE (.0000001
* (MOD (ABS (p_qty), 100000000)
– MOD (ABS (p_qty), 10000000)),
0,
NULL,
1,
NULL,
2,
NULL,
‘y ‘
))
)
|| DECODE (.0000001
* (MOD (ABS (p_qty), 100000000)
– MOD (ABS (p_qty), 10000000)),
1,
NULL,
2,
NULL,
(DECODE (.000001
* (MOD (ABS (p_qty), 10000000)
– MOD (ABS (p_qty), 1000000)),
1,
‘Un ‘,
2,
‘Dos ‘,
3,
‘Tres ‘,
4,
‘Cuatro ‘,
5,
‘Cinco ‘,
6,
‘Seis ‘,
7,
‘Siete ‘,
8,
‘Ocho ‘,
9,
‘Nueve ‘,
0,
NULL,
‘error’
))
)
|| DECODE (SIGN (ABS (p_qty) – 999.99),
1,
DECODE (.00000001
* (MOD (ABS (p_qty), 1000000000)
– MOD (ABS (p_qty), 100000000)),
0,
DECODE (.0000001
* (MOD (ABS (p_qty), 100000000)
– MOD (ABS (p_qty), 10000000)),
0,
DECODE (.000001
* (MOD (ABS (p_qty), 10000000)
– MOD (ABS (p_qty), 1000000)),
0,
NULL,
DECODE (SUBSTR (TO_CHAR(TRUNC (p_qty,
0
)),
-7,
1
),
‘1’,
‘Millon ‘,
‘Millones ‘
)
),
‘Millones ‘
),
‘Millones ‘
),
NULL
)
|| DECODE (.00001
* (MOD (ABS (p_qty), 1000000) – MOD (ABS (p_qty), 100000)),
1,
DECODE (SUBSTR (TO_CHAR (TRUNC (p_qty, 0)), -6, 3),
‘100’, ‘Cien ‘,
‘Ciento ‘),
2,
‘Doscientos ‘,
3,
‘Trescientos ‘,
4,
‘Cuatrocientos ‘,
5,
‘Quinientos ‘,
6,
‘Seiscientos ‘,
7,
‘Setecientos ‘,
8,
‘Ochocientos ‘,
9,
‘Novecientos ‘,
0,
NULL,
‘error’
)
|| DECODE (.0001
* (MOD (ABS (p_qty), 100000) – MOD (ABS (p_qty), 10000)),
1,
(DECODE (.001
* (MOD (ABS (p_qty), 10000)
– MOD (ABS (p_qty), 1000)),
0,
‘Diez ‘,
1,
‘Once ‘,
2,
‘Doce ‘,
3,
‘Trece ‘,
4,
‘Catorce ‘,
5,
‘Quince ‘,
6,
‘Dieciseis ‘,
7,
‘Diecisiete ‘,
8,
‘Dieciocho ‘,
9,
‘Diecinueve ‘,
‘ERROR’
)),
2,
(DECODE (.001
* (MOD (ABS (p_qty), 10000)
– MOD (ABS (p_qty), 1000)),
0,
‘Veinte ‘,
1,
‘Ventiun ‘,
2,
‘Ventidos ‘,
3,
‘Ventitres ‘,
4,
‘Venticuatro ‘,
5,
‘Venticinco ‘,
6,
‘Ventiseis ‘,
7,
‘Ventisiete ‘,
8,
‘Ventiocho ‘,
9,
‘Ventinueve ‘,
‘ERROR’
)),
3,
‘Treinta ‘,
4,
‘Cuarenta ‘,
5,
‘Cincuenta ‘,
6,
‘Sesenta ‘,
7,
‘Setenta ‘,
8,
‘Ochenta ‘,
9,
‘Noventa ‘,
NULL
)
|| DECODE (.001
* (MOD (ABS (p_qty), 10000) – MOD (ABS (p_qty), 1000)),
0,
NULL,
(DECODE (.0001
* (MOD (ABS (p_qty), 100000)
– MOD (ABS (p_qty), 10000)),
0,
NULL,
1,
NULL,
2,
NULL,
‘y ‘
))
)
|| DECODE (.0001
* (MOD (ABS (p_qty), 100000) – MOD (ABS (p_qty), 10000)),
1,
NULL,
2,
NULL,
(DECODE (.001
* (MOD (ABS (p_qty), 10000)
– MOD (ABS (p_qty), 1000)),
1,
‘Un ‘,
2,
‘Dos ‘,
3,
‘Tres ‘,
4,
‘Cuatro ‘,
5,
‘Cinco ‘,
6,
‘Seis ‘,
7,
‘Siete ‘,
8,
‘Ocho ‘,
9,
‘Nueve ‘,
0,
NULL,
‘error’
))
)
|| DECODE (SIGN (ABS (p_qty) – 999.99),
1,
DECODE (.00001
* (MOD (ABS (p_qty), 1000000)
– MOD (ABS (p_qty), 100000)),
0,
DECODE (.0001
* (MOD (ABS (p_qty), 100000)
– MOD (ABS (p_qty), 10000)),
0,
DECODE (.001
* (MOD (ABS (p_qty), 10000)
– MOD (ABS (p_qty), 1000)),
0,
NULL,
‘Mil ‘
),
‘Mil ‘
),
‘Mil ‘
),
NULL
)
|| DECODE (.01 * (MOD (ABS (p_qty), 1000) – MOD (ABS (p_qty), 100)),
1,
DECODE (SUBSTR (TO_CHAR (TRUNC (p_qty, 0)), -3, 3),
‘100’, ‘Cien ‘,
‘Ciento ‘),
2,
‘Doscientos ‘,
3,
‘Trescientos ‘,
4,
‘Cuatrocientos ‘,
5,
‘Quinientos ‘,
6,
‘Seiscientos ‘,
7,
‘Setecientos ‘,
8,
‘Ochocientos ‘,
9,
‘Novecientos ‘,
NULL
)
|| DECODE (.1 * (MOD (ABS (p_qty), 100) – MOD (ABS (p_qty), 10)),
1,
(DECODE (TRUNC (MOD (ABS (p_qty), 10)),
0, ‘Diez ‘,
1, ‘Once ‘,
2, ‘Doce ‘,
3, ‘Trece ‘,
4, ‘Catorce ‘,
5, ‘Quince ‘,
6, ‘Dieciseis ‘,
7, ‘Diecisiete ‘,
8, ‘Dieciocho ‘,
9, ‘Diecinueve ‘,
‘ERROR’)),
2,
(DECODE (TRUNC (MOD (ABS (p_qty), 10)),
0, ‘Veinte ‘,
1, ‘Ventiun ‘,
2, ‘Ventidos ‘,
3, ‘Ventitres ‘,
4, ‘Venticuatro ‘,
5, ‘Venticinco ‘,
6, ‘Ventiseis ‘,
7, ‘Ventisiete ‘,
8, ‘Ventiocho ‘,
9, ‘Ventinueve ‘,
‘ERROR’)),
2,
‘Veinte ‘,
3,
‘Treinta ‘,
4,
‘Cuarenta ‘,
5,
‘Cincuenta ‘,
6,
‘Sesenta ‘,
7,
‘Setenta ‘,
8,
‘Ochenta ‘,
9,
‘Noventa ‘,
NULL
)
|| DECODE (TRUNC (MOD (ABS (p_qty), 10)),
0,
NULL,
(DECODE (.1
* (MOD (ABS (p_qty), 100)
– MOD (ABS (p_qty), 10)),
0,
NULL,
1,
NULL,
2,
NULL,
‘y ‘
))
)
|| DECODE (.1 * (MOD (ABS (p_qty), 100) – MOD (ABS (p_qty), 10)),
1,
NULL,
2,
NULL,
(DECODE (TRUNC (MOD (ABS (p_qty), 10)),
1, ‘Un’,
2, ‘Dos’,
3, ‘Tres’,
4, ‘Cuatro’,
5, ‘Cinco’,
6, ‘Seis’,
7, ‘Siete’,
8, ‘Ocho’,
9, ‘Nueve’,
0, NULL,
‘error’))
)
|| DECODE (MOD (TRUNC (p_qty, 0), 1000000),
0, DECODE (p_qty, 0, NULL, ‘de ‘),
NULL)
|| DECODE (TRUNC (p_qty, 0), 0, ‘Zero ‘, NULL)
|| zero
||’ ‘|| DECODE (100 * (ABS (p_qty) – TRUNC (ABS (p_qty))),
0, ’00’,
100 * (ABS (p_qty) – TRUNC (ABS (p_qty)))
)
|| ‘/100 ‘
INTO num_string
FROM DUAL;

RETURN UPPER (num_string);
RETURN NULL;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN (‘111no se encontro dato’);
WHEN OTHERS
THEN
RETURN (‘112no se encontro dato’);
END NUM_TO_WORD_SP;

No hay comentarios para esta entrada...

Escribir un Comentario

Busca algo?

Escriba una palabra para buscar en el sitio:

Todavia no encontrando lo que usted esta buscando? Deje un comentario y entraremos en contacto con ud, para poderlo ayudar!

Visita a nuestros amigos!

Amigos recomendados...

Archivos

Entradas, ordenadas por mes...