El Blogcito de Ricardo Dufoo

Archivos de marzo, 2011

Consult the OPP service log for details…..

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

El día de ayer mientras ejecutaba un reporte de Oracle EBS me topé con el siguiente error:

Procesamiento posterior de solicitud 1702549 rechazado a 29-MAR-2011 19:54:45 con el mensaje de error:
One or more post-processing actions failed. Consult the OPP service log for details.

¿Cómo consultar ese archivo «OPP service log» para encontrar el error? El buen Javiruchis me pasó la siguiente consulta para identificar el archivo a consultar:

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = &&request_id

Espero les sirva….

 

 

1 Comentario : mas...

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;

Escribir un Comentario mas...

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...