[Home] [Help]
PACKAGE BODY: APPS.IBY_AMOUNT_IN_WORDS
Source
1 PACKAGE BODY IBY_AMOUNT_IN_WORDS AS
2 /* $Header: ibyamtwb.pls 120.4.12010000.7 2008/12/02 09:24:32 bkjain ship $ */
3
4 -- code from JEES_NTW pacakge 120.3
5 FUNCTION JEES_NTW_CONV_NUM_FRAGMENT (P_NUM INTEGER, /* integer version of number */
6 P_FEM BOOLEAN) /* masculine or feminine */
7 RETURN VARCHAR2; /* word version of integer */
8
9 -- code from JEES_NTW pacakge 120.3
10 FUNCTION JEES_NTW_NUM_TO_WORDS (P_ENTERED NUMBER,
11 P_PRECISION INTEGER)
12 RETURN VARCHAR2;
13
14 -- code from APXPBFOR.rdf 115.7
15 APXPBFOR_P_UNIT_SINGULAR CONSTANT VARCHAR2(100) := 'Dollar';
16 APXPBFOR_P_UNIT_PLURAL CONSTANT VARCHAR2(100) := 'Dollars';
17 APXPBFOR_P_SUB_UNIT_SINGULAR CONSTANT VARCHAR2(100) := 'Cent';
18 APXPBFOR_P_SUB_UNIT_PLURAL CONSTANT VARCHAR2(100) := 'Cents';
19 APXPBFOR_P_UNIT_RATIO CONSTANT NUMBER := 100;
20
21 c_zero ap_lookup_codes.displayed_field%TYPE;
22 c_thousand ap_lookup_codes.displayed_field%TYPE;
23 c_million ap_lookup_codes.displayed_field%TYPE;
24 c_billion ap_lookup_codes.displayed_field%TYPE;
25 g_user_lang fnd_languages.nls_language%TYPE := 'junk';
26
27 TYPE num_lookupTab IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
28 g_numlookup num_lookupTab;
29
30 -- look up a word text for a number under 1000
31 FUNCTION Lookup_num (p_num NUMBER) RETURN VARCHAR2;
32
33 -- code from APXPBFOR.rdf 115.7
34 function APXPBFOR_C_WORD_AMOUNTFormula(p_amount IN NUMBER,
35 p_currency_code IN VARCHAR2,
36 p_precision IN NUMBER)
37 return VARCHAR2;
38
39 -- code from APXPBFOR.rdf 115.7
40 function APXPBFOR_get_word_value (
41 p_amount number,
42 p_unit_singular varchar2,
43 p_unit_plural varchar2,
44 p_sub_unit_singular varchar2,
45 p_sub_unit_plural varchar2,
46 p_unit_ratio number
47 ) return varchar2;
48
49
50 -- code from AP_AMOUNT_UTILITIES_PKG 120.3
51 function apamtutb_ap_convert_number (in_numeral IN NUMBER) return varchar2;
52
53 -- code from JLBRPCFP.rdf 115.14 2006/08/22 08:54
54 FUNCTION AMOUNT_WORDS_SPANISH (Chk_Amt number) RETURN VARCHAR2;
55
56 -- code from JLBRPCFP.rdf 115.14 2006/08/22 08:54
57 FUNCTION AMOUNT_WORDS_MEXICAN (P_ENTERED NUMBER,
58 P_PRECISION INTEGER)
59 RETURN VARCHAR2;
60
61 -- main API
62 FUNCTION Get_Amount_In_Words(p_amount IN NUMBER,
63 p_currency_code IN VARCHAR2 := NULL,
64 p_precision IN NUMBER := NULL,
65 p_country_code IN VARCHAR2 := NULL)
66 RETURN VARCHAR2
67 IS
68 l_api_name CONSTANT VARCHAR2(30) := 'Get_Amount_In_Words';
69 l_Debug_Module VARCHAR2(255):= G_DEBUG_MODULE || '.' || l_api_name;
70
71 BEGIN
72
73 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
74 debug_level => FND_LOG.LEVEL_PROCEDURE,
75 module => l_Debug_Module);
76
77 iby_debug_pub.add(debug_msg => 'p_amount: ' || p_amount,
78 debug_level => FND_LOG.LEVEL_STATEMENT,
79 module => l_Debug_Module);
80
81 iby_debug_pub.add(debug_msg => 'p_currency_code: ' || p_currency_code,
82 debug_level => FND_LOG.LEVEL_STATEMENT,
83 module => l_Debug_Module);
84
85 iby_debug_pub.add(debug_msg => 'p_precision: ' || p_precision,
86 debug_level => FND_LOG.LEVEL_STATEMENT,
87 module => l_Debug_Module);
88
89 iby_debug_pub.add(debug_msg => 'p_country_code: ' || p_country_code,
90 debug_level => FND_LOG.LEVEL_STATEMENT,
91 module => l_Debug_Module);
92
93 IF p_currency_code ='MXN' THEN
94 RETURN AMOUNT_WORDS_MEXICAN (p_amount, nvl(p_precision, 0));
95 ELSIF p_country_code = 'ES' THEN
96 RETURN JEES_NTW_NUM_TO_WORDS(p_amount, nvl(p_precision, 0));
97 -- bug 5511781: 'ARS', 'BRL', 'CLP', 'COP'
98 ELSIF p_currency_code IN ('ARS', 'BRL', 'CLP', 'COP') THEN
99 RETURN AMOUNT_WORDS_SPANISH (p_amount);
100 ELSE
101 RETURN APXPBFOR_C_WORD_AMOUNTFormula(p_amount, p_currency_code, p_precision);
102 END IF;
103
104 END Get_Amount_In_Words;
105
106
107 -- code from APXPBFOR.rdf 115.7
108 function APXPBFOR_C_WORD_AMOUNTFormula(p_amount IN NUMBER,
109 p_currency_code IN VARCHAR2,
110 p_precision IN NUMBER)
111 return VARCHAR2
112 is
113
114 l_word_text varchar2(240);
115 l_width number := 58; -- Width of word amount field
116 l_unit_singular_msg_name varchar2(30) := 'IBY_AMT_IN_WORD_US_';
117 l_unit_plural_msg_name varchar2(30) := 'IBY_AMT_IN_WORD_UP_';
118 l_unit_sub_singular_msg_name varchar2(30) := 'IBY_AMT_IN_WORD_SUS_';
119 l_unit_sub_plural_msg_name varchar2(30) := 'IBY_AMT_IN_WORD_SUP_';
120 l_unit_singular_str varchar2(80);
121 l_unit_plural_str varchar2(80);
122 l_unit_sub_singular_str varchar2(80);
123 l_unit_sub_plural_str varchar2(80);
124 l_precision NUMBER;
125 l_unit_ratio NUMBER;
126
127 l_api_name CONSTANT VARCHAR2(30) := 'Get_Amount_In_Words';
128 l_Debug_Module VARCHAR2(255):= G_DEBUG_MODULE || l_api_name;
129
130 CURSOR l_precision_csr (p_currency_code IN VARCHAR2) IS
131 SELECT precision
132 FROM fnd_currencies
133 WHERE currency_code = p_currency_code;
134
135 begin
136
137 -- 'USD', 'EUR', 'JPY', 'CNY', 'CHF', 'DKK', 'FIM', 'NOK', 'SEK'
138 l_unit_singular_msg_name := l_unit_singular_msg_name || p_currency_code;
139 l_unit_plural_msg_name := l_unit_plural_msg_name || p_currency_code;
140 l_unit_sub_singular_msg_name := l_unit_sub_singular_msg_name || p_currency_code;
141 l_unit_sub_plural_msg_name := l_unit_sub_plural_msg_name || p_currency_code;
142
143 iby_debug_pub.add(debug_msg => 'formulated message names: ',
144 debug_level => FND_LOG.LEVEL_STATEMENT,
145 module => l_Debug_Module);
146
147 iby_debug_pub.add(debug_msg => 'l_unit_singular_msg_name: ' || l_unit_singular_msg_name,
148 debug_level => FND_LOG.LEVEL_STATEMENT,
149 module => l_Debug_Module);
150
151 iby_debug_pub.add(debug_msg => 'l_unit_plural_msg_name: ' || l_unit_plural_msg_name,
152 debug_level => FND_LOG.LEVEL_STATEMENT,
153 module => l_Debug_Module);
154
155 iby_debug_pub.add(debug_msg => 'l_unit_sub_singular_msg_name: ' || l_unit_sub_singular_msg_name,
156 debug_level => FND_LOG.LEVEL_STATEMENT,
157 module => l_Debug_Module);
158
159 iby_debug_pub.add(debug_msg => 'l_unit_sub_plural_msg_name: ' || l_unit_sub_plural_msg_name,
160 debug_level => FND_LOG.LEVEL_STATEMENT,
161 module => l_Debug_Module);
162
163 BEGIN
164 fnd_message.set_name('IBY', l_unit_singular_msg_name);
165 l_unit_singular_str := fnd_message.get;
166
167 IF l_unit_singular_str = l_unit_singular_msg_name THEN
168 l_unit_singular_str := NULL;
169 END IF;
170
171 iby_debug_pub.add(debug_msg => 'Got translated text for ' || l_unit_singular_msg_name
172 || ': ' || l_unit_singular_str,
173 debug_level => FND_LOG.LEVEL_STATEMENT,
174 module => l_Debug_Module);
175
176 EXCEPTION
177 WHEN OTHERS THEN
178 NULL;
179 END;
180
181
182 BEGIN
183 fnd_message.set_name('IBY', l_unit_plural_msg_name);
184 l_unit_plural_str := fnd_message.get;
185
186 IF l_unit_plural_str = l_unit_plural_msg_name THEN
187 l_unit_plural_str := NULL;
188 END IF;
189
190 iby_debug_pub.add(debug_msg => 'Got translated text for ' || l_unit_plural_msg_name
191 || ': ' || l_unit_plural_str,
192 debug_level => FND_LOG.LEVEL_STATEMENT,
193 module => l_Debug_Module);
194
195 EXCEPTION
196 WHEN OTHERS THEN
197 NULL;
198 END;
199
200 BEGIN
201 fnd_message.set_name('IBY', l_unit_sub_singular_msg_name);
202 l_unit_sub_singular_str := fnd_message.get;
203
204 IF l_unit_sub_singular_str = l_unit_sub_singular_msg_name THEN
205 l_unit_sub_singular_str := NULL;
206 END IF;
207
208 iby_debug_pub.add(debug_msg => 'Got translated text for ' || l_unit_sub_singular_msg_name
209 || ': ' || l_unit_sub_singular_str,
210 debug_level => FND_LOG.LEVEL_STATEMENT,
211 module => l_Debug_Module);
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 NULL;
216 END;
217
218 BEGIN
219 fnd_message.set_name('IBY', l_unit_sub_plural_msg_name);
220 l_unit_sub_plural_str := fnd_message.get;
221
222 IF l_unit_sub_plural_str = l_unit_sub_plural_msg_name THEN
223 l_unit_sub_plural_str := NULL;
224 END IF;
225
226 iby_debug_pub.add(debug_msg => 'Got translated text for ' || l_unit_sub_plural_msg_name
227 || ': ' || l_unit_sub_plural_str,
228 debug_level => FND_LOG.LEVEL_STATEMENT,
229 module => l_Debug_Module);
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 NULL;
234 END;
235
236 BEGIN
237 IF p_precision IS NULL THEN
238
239 IF p_currency_code IS NULL THEN
240 iby_debug_pub.add(debug_msg => 'Warning: p_precision and p_currency_code are both null! ',
241 debug_level => FND_LOG.LEVEL_STATEMENT,
242 module => l_Debug_Module);
243 ELSE
244
245 OPEN l_precision_csr (p_currency_code);
246 FETCH l_precision_csr INTO l_precision;
247 CLOSE l_precision_csr;
248
249 END IF;
250
251 ELSE
252 l_precision := p_precision;
253 END IF;
254
255 iby_debug_pub.add(debug_msg => 'l_precision: ' || l_precision,
256 debug_level => FND_LOG.LEVEL_STATEMENT,
257 module => l_Debug_Module);
258
259 IF l_precision = 1 THEN
260 l_unit_ratio := 10;
261 ELSIF l_precision = 2 THEN
262 l_unit_ratio := 100;
263 ELSIF l_precision = 3 THEN
264 l_unit_ratio := 1000;
265 END IF;
266
267 EXCEPTION
268 WHEN OTHERS THEN
269 NULL;
270 END;
271
272 l_word_text :=
273 APXPBFOR_get_word_value (
274 p_amount => p_amount,
275 p_unit_singular => l_unit_singular_str,
276 p_unit_plural => l_unit_plural_str,
277 p_sub_unit_singular => l_unit_sub_singular_str,
278 p_sub_unit_plural => l_unit_sub_plural_str,
279 p_unit_ratio => l_unit_ratio);
280 -- Format the output to have asterisks on right-hand side
281 if NVL(length(l_word_text), 0) <= l_width then
282 l_word_text := rpad(l_word_text,l_width,'*');
283 elsif NVL(length(l_word_text), 0) <= l_width*2 then
284 -- Allow for word wrapping
285 -- Removed the logic for word wrapping for Bug 7433132
286 -- l_word_text := rpad(l_word_text,l_width*2 -
287 -- (l_width-instr(substr(l_word_text,1,l_width+1),' ',-1)),'*');
288 l_word_text := rpad(l_word_text,l_width*2,'*');
289 elsif NVL(length(l_word_text), 0) <= l_width*3 then
290 l_word_text := rpad(l_word_text,l_width*3,'*');
291 end if;
292
293 iby_debug_pub.add(debug_msg => 'Amount text: ' || l_word_text,
294 debug_level => FND_LOG.LEVEL_STATEMENT,
295 module => l_Debug_Module);
296
297 return(l_word_text);
298
299 EXCEPTION
300 WHEN OTHERS THEN
301 iby_debug_pub.add(debug_msg => 'Error in getting amount text. Returning null.',
302 debug_level => FND_LOG.LEVEL_STATEMENT,
303 module => l_Debug_Module);
304 RETURN NULL;
305
306 END APXPBFOR_C_WORD_AMOUNTFormula;
307
308
309 -- code from APXPBFOR.rdf 115.7
310 function APXPBFOR_get_word_value (
311 p_amount number,
312 p_unit_singular varchar2,
313 p_unit_plural varchar2,
314 p_sub_unit_singular varchar2,
315 p_sub_unit_plural varchar2,
316 p_unit_ratio number
317 ) return varchar2 is
318 l_word_amount varchar2(240) := apamtutb_ap_convert_number(trunc(p_amount));
319
320 -- Removed the convert_amount and added the package call ap_amount_utilities_pkg
321 -- which handles this word conversion from number.
322 -- For bug 2569922
323
324 l_currency_word varchar2(240);
325 l_part_amount_word varchar2(240);
326 l_log integer;
327
328
329 /* This is a workaround until bug #165793 is fixed */
330 function my_log (a integer, b integer) return number is
331 begin
332 if a <> 10 then return(null);
333 elsif b > 0 and b <= 10 then return(1);
334 elsif b > 10 and b <= 100 then return(2);
335 elsif b > 100 and b <= 1000 then return(3);
336 else return(null);
337 end if;
338 RETURN NULL; end;
339
340 begin
341 l_log := my_log(10,p_unit_ratio);
342
343 if p_unit_ratio in (0,1) or p_unit_ratio is null then
344 select initcap(lower(
345 l_word_amount||' '||
346 decode(trunc(p_amount),
347 1,p_unit_singular,
348 p_unit_plural)
349 ))
350 into l_currency_word
351 from dual;
352 else
353
354 -- Added the package call ap_amount_utilities_pkg
355 -- which handles the word conversion for decimal digits
356 -- For bug 2569922
357 l_part_amount_word := apamtutb_ap_convert_number(lpad(to_char(trunc((p_amount-trunc(p_amount))*p_unit_ratio)),
358 ceil(l_log),'0'));
359 select initcap(lower(
360 l_word_amount||' '||
361 decode(trunc(p_amount),
362 1,p_unit_singular,
363 p_unit_plural)||' '||displayed_field||' '||l_part_amount_word
364 ||' '||
365 decode(trunc((p_amount-trunc(p_amount))*p_unit_ratio),
366 1,p_sub_unit_singular,
367 p_sub_unit_plural)
368 ))
369 into l_currency_word
370 from ap_lookup_codes
371 where lookup_code = 'AND'
372 and lookup_type = 'NLS TRANSLATION';
373 end if;
374
375 return(l_currency_word);
376 END APXPBFOR_get_word_value;
377
378
379 FUNCTION Lookup_num (p_num NUMBER) RETURN VARCHAR2 IS
380 BEGIN
381
382 RETURN g_numlookup(p_num);
383
384 exception
385 when no_data_found then
386
387 select description
388 into g_numlookup(p_num)
389 from ap_lookup_codes
390 where lookup_code = to_char(p_num)
391 and lookup_type = 'NUMBERS';
392
393 iby_debug_pub.add(debug_msg => 'Adding to lookup cache ' || p_num || ': ' || g_numlookup(p_num),
394 debug_level => FND_LOG.LEVEL_STATEMENT,
395 module => G_DEBUG_MODULE || '.Lookup_num');
396
397 RETURN g_numlookup(p_num);
398 END;
399
400 -- code from AP_AMOUNT_UTILITIES_PKG 120.3
401 function apamtutb_ap_convert_number (in_numeral IN NUMBER) return varchar2 is
402 number_too_large exception;
403 numeral integer := abs(in_numeral);
404 max_digit integer := 12; -- for numbers less than a trillion
405 number_text varchar2(240) := '';
406 billion_segN number;
407 million_segN number;
408 thousand_segN number;
409 units_segN number;
410 thousand_lookup varchar2(80);
411 units_lookup varchar2(80);
412 session_language fnd_languages.nls_language%TYPE;
413 thousand number := power(10,3);
414 million number := power(10,6);
415 billion number := power(10,9);
416 l_Debug_Module VARCHAR2(255):= G_DEBUG_MODULE || '.apamtutb_ap_convert_number';
417
418 BEGIN
419
420 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
421 debug_level => FND_LOG.LEVEL_PROCEDURE,
422 module => l_Debug_Module);
423
424 if numeral >= power(10,max_digit) then
425 raise number_too_large;
426 end if;
427
428 select substr(userenv('LANGUAGE'),1,instr(userenv('LANGUAGE'),'_')-1)
429 into session_language
430 from dual;
431
432 iby_debug_pub.add(debug_msg => 'numeral: ' || numeral,
433 debug_level => FND_LOG.LEVEL_STATEMENT,
434 module => l_Debug_Module);
435
436 iby_debug_pub.add(debug_msg => 'session_language: ' || session_language,
437 debug_level => FND_LOG.LEVEL_STATEMENT,
438 module => l_Debug_Module);
439
440 IF g_user_lang <> session_language THEN
441
442 iby_debug_pub.add(debug_msg => 'g_user_lang <> session_language, caching lookups ',
443 debug_level => FND_LOG.LEVEL_STATEMENT,
444 module => l_Debug_Module);
445
446 g_user_lang := session_language;
447
448 select ' '||lc1.displayed_field||' ',
449 ' '||lc2.displayed_field||' ',
450 ' '||lc3.displayed_field||' ',
451 ' '||lc4.displayed_field
452 into c_billion,
453 c_million,
454 c_thousand,
455 c_zero
456 from ap_lookup_codes lc1,
457 ap_lookup_codes lc2,
458 ap_lookup_codes lc3,
459 ap_lookup_codes lc4
460 where lc1.lookup_code = 'BILLION'
461 and lc1.lookup_type = 'NLS TRANSLATION'
462 and lc2.lookup_code = 'MILLION'
463 and lc2.lookup_type = 'NLS TRANSLATION'
464 and lc3.lookup_code = 'THOUSAND'
465 and lc3.lookup_type = 'NLS TRANSLATION'
466 and lc4.lookup_code = 'ZERO'
467 and lc4.lookup_type = 'NLS TRANSLATION';
468
469 g_numlookup.DELETE;
470
471 END IF;
472
473 --For Bug459665
474 if numeral = 0 then
475 RETURN c_zero;
476 end if;
477
478
479 billion_segN := trunc(numeral/billion);
480
481 numeral := numeral - (billion_segN * billion);
482 million_segN := trunc(numeral/million);
483
484 numeral := numeral - (million_segN * million);
485 thousand_segN := trunc(numeral/thousand);
486
487 units_segN := mod(numeral,thousand);
488
489 iby_debug_pub.add(debug_msg => 'billion_segN: ' || billion_segN,
490 debug_level => FND_LOG.LEVEL_STATEMENT,
491 module => l_Debug_Module);
492
493 iby_debug_pub.add(debug_msg => 'million_segN: ' || million_segN,
494 debug_level => FND_LOG.LEVEL_STATEMENT,
495 module => l_Debug_Module);
496
497 iby_debug_pub.add(debug_msg => 'thousand_segN: ' || thousand_segN,
498 debug_level => FND_LOG.LEVEL_STATEMENT,
499 module => l_Debug_Module);
500
501 iby_debug_pub.add(debug_msg => 'units_segN: ' || units_segN,
502 debug_level => FND_LOG.LEVEL_STATEMENT,
503 module => l_Debug_Module);
504
505 if billion_segN <> 0 then
506 number_text := number_text||Lookup_num(billion_segN) ||c_billion;
507 end if;
508
509 if million_segN <> 0 then
510 number_text := number_text||Lookup_num(million_segN)||c_million;
511 end if;
512
513 if thousand_segN <> 0 then
514 --Bug 335063 fix.
515 if (session_language = 'FRENCH' or session_language = 'CANADIAN FRENCH')
516 and thousand_segN = 1 then
517 thousand_lookup := null;
518 ELSE
519 thousand_lookup := Lookup_num(thousand_segN);
520 end if;
521
522 number_text := number_text||thousand_lookup||c_thousand;
523 end if;
524
525 if units_segN <> 0 then
526 number_text := number_text||Lookup_num(units_segN);
527 end if;
528
529 number_text := ltrim(number_text);
530 number_text := upper(substr(number_text,1,1)) ||
531 rtrim(lower(substr(number_text,2,length(number_text))));
532
533 iby_debug_pub.add(debug_msg => 'returning number_text: ' || number_text,
534 debug_level => FND_LOG.LEVEL_STATEMENT,
535 module => l_Debug_Module);
536
537 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
538 debug_level => FND_LOG.LEVEL_PROCEDURE,
539 module => l_Debug_Module);
540
541 return(number_text);
542
543 exception
544 when number_too_large then
545 return(null);
546 when others then
547 return(null);
548 END apamtutb_ap_convert_number;
549
550
551 -- code from JEES_NTW pacakge 120.3
552 FUNCTION JEES_NTW_CONV_NUM_FRAGMENT (P_NUM INTEGER, /* integer version of number */
553 P_FEM BOOLEAN) /* masculine or feminine */
554 RETURN VARCHAR2 IS /* word version of integer */
555
556 LOC_LONGI NUMBER(2);
557 LOC_DIV1 NUMBER(2);
558 LOC_DIV2 NUMBER(12);
559 LOC_MOD NUMBER(2);
560 LOC_CAMPO VARCHAR2(13);
561 LOC_RESTO NUMBER(3);
562 LOC_SW1 VARCHAR2(1);
563 LOC_SW2 VARCHAR2(1);
564 LOC_PASO NUMBER(12);
565 LOC_NUMERO1 NUMBER(12);
566 LOC_UNO NUMBER(1);
567 LOC_DOS NUMBER(1);
568 LOC_TRES NUMBER(1);
569 LOC_LETRAS VARCHAR2(240);
570 LOC_NUMERO NUMBER(12);
571 LOC_FEMININE BOOLEAN;
572 BEGIN
573 LOC_CAMPO := '1000000000000';
574 LOC_NUMERO := P_NUM;
575 LOC_FEMININE := P_FEM;
576 LOC_SW1 := 'N';
577 LOC_SW2 := 'N';
578 LOC_LETRAS := NULL;
579 IF LOC_NUMERO = 0 THEN
580 LOC_LETRAS := 'CERO';
581 ELSE
582 WHILE LOC_NUMERO > 0 LOOP
583 IF LOC_SW1 = 'S' AND LOC_SW2 = 'N' AND LOC_PASO = 1
584 AND LOC_NUMERO < 999999 THEN
585 LOC_LETRAS := LOC_LETRAS||' MILLONES';
586 LOC_PASO := NULL;
587 LOC_SW1 := 'N';
588 END IF;
589 LOC_LONGI := TO_NUMBER(LENGTH(TO_CHAR(LOC_NUMERO)));
590 LOC_MOD := MOD(LOC_LONGI,3);
591 LOC_DIV1 := LOC_LONGI - LOC_MOD;
592 IF LOC_MOD = 0 THEN
593 LOC_DIV1 := LOC_DIV1 - 3;
594 END IF;
595 LOC_DIV2 := TO_NUMBER(SUBSTR(LOC_CAMPO,1,LOC_DIV1+1));
596 LOC_NUMERO1 := TRUNC(LOC_NUMERO/LOC_DIV2);
597
598 IF LOC_NUMERO >= 100 THEN
599 LOC_UNO := TRUNC(LOC_NUMERO1/100);
600 IF LOC_UNO = 1 THEN
601 IF LOC_NUMERO1 = 100 THEN
602 LOC_LETRAS := LOC_LETRAS||' CIEN';
603 ELSE
604 LOC_LETRAS := LOC_LETRAS||' CIENTO';
605 END IF;
606 ELSIF LOC_UNO = 2 THEN
607 IF LOC_NUMERO > 999999
608 /* NMR - bug 839444 */
609 OR NOT LOC_FEMININE THEN
610 LOC_LETRAS := LOC_LETRAS||' DOSCIENTOS';
611 ELSE
612 LOC_LETRAS := LOC_LETRAS||' DOSCIENTAS';
613 END IF;
614 ELSIF LOC_UNO = 3 THEN
615 IF LOC_NUMERO > 999999
616 /* NMR - bug 839444 */
617 OR NOT LOC_FEMININE THEN
618 LOC_LETRAS := LOC_LETRAS||' TRESCIENTOS';
619 ELSE
620 LOC_LETRAS := LOC_LETRAS||' TRESCIENTAS';
621 END IF;
622 ELSIF LOC_UNO = 4 THEN
623 IF LOC_NUMERO > 999999
624 /* NMR - bug 839444 */
625 OR NOT LOC_FEMININE THEN
626 LOC_LETRAS := LOC_LETRAS||' CUATROCIENTOS';
627 ELSE
628 LOC_LETRAS := LOC_LETRAS||' CUATROCIENTAS';
629 END IF;
630 ELSIF LOC_UNO = 5 THEN
631 IF LOC_NUMERO > 999999
632 /* NMR - bug 839444 */
633 OR NOT LOC_FEMININE THEN
634 LOC_LETRAS := LOC_LETRAS||' QUINIENTOS';
635 ELSE
636 LOC_LETRAS := LOC_LETRAS||' QUINIENTAS';
637 END IF;
638 ELSIF LOC_UNO = 6 THEN
639 IF LOC_NUMERO > 999999
640 /* NMR - bug 839444 */
641 OR NOT LOC_FEMININE THEN
642 LOC_LETRAS := LOC_LETRAS||' SEISCIENTOS';
643 ELSE
644 LOC_LETRAS := LOC_LETRAS||' SEISCIENTAS';
645 END IF;
646 ELSIF LOC_UNO = 7 THEN
647 IF LOC_NUMERO > 999999
648 /* NMR - bug 839444 */
649 OR NOT LOC_FEMININE THEN
650 LOC_LETRAS := LOC_LETRAS||' SETECIENTOS';
651 ELSE
652 LOC_LETRAS := LOC_LETRAS||' SETECIENTAS';
653 END IF;
654 ELSIF LOC_UNO = 8 THEN
655 IF LOC_NUMERO > 999999
656 /* NMR - bug 839444 */
657 OR NOT LOC_FEMININE THEN
658 LOC_LETRAS := LOC_LETRAS||' OCHOCIENTOS';
659 ELSE
660 LOC_LETRAS := LOC_LETRAS||' OCHOCIENTAS';
661 END IF;
662 ELSIF LOC_UNO = 9 THEN
663 IF LOC_NUMERO > 999999
664 /* NMR - bug 839444 */
665 OR NOT LOC_FEMININE THEN
666 LOC_LETRAS := LOC_LETRAS||' NOVECIENTOS';
667 ELSE
668 LOC_LETRAS := LOC_LETRAS||' NOVECIENTAS';
669 END IF;
670 END IF;
671 ELSE
672 LOC_UNO := 0;
673 END IF;
674 LOC_DOS := TRUNC((LOC_NUMERO1-(LOC_UNO*100))/10);
675
676 IF LOC_DOS < 3
677 AND (LOC_DOS > 0
678 OR LOC_NUMERO > 999999)
679 /*
680 Added by NMR because it wasn't recognising the final number under
681 certain circumtances
682 */
683 OR LOC_DOS = 0
684 THEN
685
686 LOC_RESTO := LOC_NUMERO1-(LOC_UNO*100);
687
688 IF LOC_RESTO = 1 THEN
689
690 IF LOC_FEMININE AND LOC_NUMERO < 1000000
691 THEN
692 /* NMR - bug 1135045 capture 1000000000 */
693 IF LOC_LONGI NOT IN (4,10) THEN
694 LOC_LETRAS := LOC_LETRAS||' UNA';
695 END IF;
696 ELSIF LOC_LONGI IN (1,2,3)
697 THEN
698 LOC_LETRAS := LOC_LETRAS||' UNO';
699 ELSE
700 /* NMR - bug 1135045 capture 1000000000 */
701 IF LOC_LONGI NOT IN (4,10) THEN
702 LOC_LETRAS := LOC_LETRAS||' UN';
703 END IF;
704 END IF;
705
706 ELSIF LOC_RESTO = 2 THEN
707 LOC_LETRAS := LOC_LETRAS||' DOS';
708 ELSIF LOC_RESTO = 3 THEN
709 LOC_LETRAS := LOC_LETRAS||' TRES';
710 ELSIF LOC_RESTO = 4 THEN
711 LOC_LETRAS := LOC_LETRAS||' CUATRO';
712 ELSIF LOC_RESTO = 5 THEN
713 LOC_LETRAS := LOC_LETRAS||' CINCO';
714 ELSIF LOC_RESTO = 6 THEN
715 LOC_LETRAS := LOC_LETRAS||' SEIS';
716 ELSIF LOC_RESTO = 7 THEN
717 LOC_LETRAS := LOC_LETRAS||' SIETE';
718 ELSIF LOC_RESTO = 8 THEN
719 LOC_LETRAS := LOC_LETRAS||' OCHO';
720 ELSIF LOC_RESTO = 9 THEN
721 LOC_LETRAS := LOC_LETRAS||' NUEVE';
722 ELSIF LOC_RESTO = 10 THEN
723 LOC_LETRAS := LOC_LETRAS||' DIEZ';
724 ELSIF LOC_RESTO = 11 THEN
725 LOC_LETRAS := LOC_LETRAS||' ONCE';
726 ELSIF LOC_RESTO = 12 THEN
727 LOC_LETRAS := LOC_LETRAS||' DOCE';
728 ELSIF LOC_RESTO = 13 THEN
729 LOC_LETRAS := LOC_LETRAS||' TRECE';
730 ELSIF LOC_RESTO = 14 THEN
731 LOC_LETRAS := LOC_LETRAS||' CATORCE';
732 ELSIF LOC_RESTO = 15 THEN
733 LOC_LETRAS := LOC_LETRAS||' QUINCE';
734 ELSIF LOC_RESTO = 16 THEN
735 LOC_LETRAS := LOC_LETRAS||' DIECISEIS';
736 ELSIF LOC_RESTO = 17 THEN
737 LOC_LETRAS := LOC_LETRAS||' DIECISIETE';
738 ELSIF LOC_RESTO = 18 THEN
739 LOC_LETRAS := LOC_LETRAS||' DIECIOCHO';
740 ELSIF LOC_RESTO = 19 THEN
741 LOC_LETRAS := LOC_LETRAS||' DIECINUEVE';
742 ELSIF LOC_RESTO = 20 THEN
743 LOC_LETRAS := LOC_LETRAS||' VEINTE';
744 ELSIF LOC_RESTO = 21 THEN
745 IF LOC_FEMININE AND LOC_NUMERO < 1000000
746 THEN
747 LOC_LETRAS := LOC_LETRAS||' VEINTIUNA';
748 ELSIF LOC_LONGI IN (1,2,3)
749 THEN
750 LOC_LETRAS := LOC_LETRAS||' VEINTIUNO';
751 ELSE
752 LOC_LETRAS := LOC_LETRAS||' VEINTIUN';
753 END IF;
754 ELSIF LOC_RESTO = 22 THEN
755 LOC_LETRAS := LOC_LETRAS||' VEINTIDOS';
756 ELSIF LOC_RESTO = 23 THEN
757 LOC_LETRAS := LOC_LETRAS||' VEINTITRES';
758 ELSIF LOC_RESTO = 24 THEN
759 LOC_LETRAS := LOC_LETRAS||' VEINTICUATRO';
760 ELSIF LOC_RESTO = 25 THEN
761 LOC_LETRAS := LOC_LETRAS||' VEINTICINCO';
762 ELSIF LOC_RESTO = 26 THEN
763 LOC_LETRAS := LOC_LETRAS||' VEINTISEIS';
764 ELSIF LOC_RESTO = 27 THEN
765 LOC_LETRAS := LOC_LETRAS||' VEINTISIETE';
766 ELSIF LOC_RESTO = 28 THEN
767 LOC_LETRAS := LOC_LETRAS||' VEINTIOCHO';
768 ELSIF LOC_RESTO = 29 THEN
769 LOC_LETRAS := LOC_LETRAS||' VEINTINUEVE';
770 END IF;
771
772 ELSIF LOC_DOS >= 3 THEN
773 IF LOC_DOS = 3 THEN
774 LOC_LETRAS := LOC_LETRAS||' TREINTA';
775 ELSIF LOC_DOS = 4 THEN
776 LOC_LETRAS := LOC_LETRAS||' CUARENTA';
777 ELSIF LOC_DOS = 5 THEN
778 LOC_LETRAS := LOC_LETRAS||' CINCUENTA';
779 ELSIF LOC_DOS = 6 THEN
780 LOC_LETRAS := LOC_LETRAS||' SESENTA';
781 ELSIF LOC_DOS = 7 THEN
782 LOC_LETRAS := LOC_LETRAS||' SETENTA';
783 ELSIF LOC_DOS = 8 THEN
784 LOC_LETRAS := LOC_LETRAS||' OCHENTA';
785 ELSIF LOC_DOS = 9 THEN
786 LOC_LETRAS := LOC_LETRAS||' NOVENTA';
787 END IF;
788 LOC_TRES := LOC_NUMERO1-(LOC_UNO*100) - (LOC_DOS*10);
789
790 IF LOC_TRES = 1 THEN
791 IF LOC_FEMININE AND LOC_NUMERO < 1000000
792 THEN
793 LOC_LETRAS := LOC_LETRAS||' Y UNA';
794 ELSIF LOC_LONGI IN (1,2,3)
795 THEN
796 LOC_LETRAS := LOC_LETRAS||' Y UNO';
797 ELSE
798 LOC_LETRAS := LOC_LETRAS||' Y UN';
799 END IF;
800
801 ELSIF LOC_TRES = 2 THEN
802 LOC_LETRAS := LOC_LETRAS||' Y DOS';
803 ELSIF LOC_TRES = 3 THEN
804 LOC_LETRAS := LOC_LETRAS||' Y TRES';
805 ELSIF LOC_TRES = 4 THEN
806 LOC_LETRAS := LOC_LETRAS||' Y CUATRO';
807 ELSIF LOC_TRES = 5 THEN
808 LOC_LETRAS := LOC_LETRAS||' Y CINCO';
809 ELSIF LOC_TRES = 6 THEN
810 LOC_LETRAS := LOC_LETRAS||' Y SEIS';
811 ELSIF LOC_TRES = 7 THEN
812 LOC_LETRAS := LOC_LETRAS||' Y SIETE';
813 ELSIF LOC_TRES = 8 THEN
814 LOC_LETRAS := LOC_LETRAS||' Y OCHO';
815 ELSIF LOC_TRES = 9 THEN
816 LOC_LETRAS := LOC_LETRAS||' Y NUEVE';
817 END IF;
818 END IF;
819 IF LOC_LONGI BETWEEN 4 AND 6 AND LOC_NUMERO1 > 0 THEN
820 LOC_LETRAS := LOC_LETRAS||' MIL';
821 ELSIF LOC_LONGI BETWEEN 7 AND 9 AND LOC_NUMERO1 > 0 THEN
822 IF LOC_NUMERO1 = 1 THEN
823 LOC_LETRAS := LOC_LETRAS||' MILLON';
824 ELSE
825 LOC_LETRAS := LOC_LETRAS||' MILLONES';
826 END IF;
827 LOC_SW2 := 'S';
828 ELSIF LOC_LONGI BETWEEN 10 AND 12 AND LOC_NUMERO1 > 0 THEN
829 LOC_PASO := 1;
830 LOC_SW1 := 'S';
831 LOC_LETRAS := LOC_LETRAS||' MIL';
832 END IF;
833 LOC_NUMERO := LOC_NUMERO - (LOC_NUMERO1 * LOC_DIV2);
834 END LOOP;
835 IF LOC_SW1 = 'S' AND LOC_SW2 = 'N' THEN
836 LOC_LETRAS := LOC_LETRAS||' MILLONES';
837 END IF;
838 END IF;
839 RETURN LOC_LETRAS;
840
841 END JEES_NTW_CONV_NUM_FRAGMENT;
842
843
844 -- code from JEES_NTW pacakge 120.3
845 /*
846 Public function which splits the number into two parts (the decimals and the
847 number without decimals) and calls the con_num_fragment for the two parts
848 */
849 FUNCTION JEES_NTW_NUM_TO_WORDS (P_ENTERED NUMBER,
850 P_PRECISION INTEGER)
851 RETURN VARCHAR2 IS
852 LOC_NUM NUMBER(12);
853 LOC_FEM BOOLEAN;
854 LOC_WORD1 VARCHAR2(240);
855 LOC_WORD2 VARCHAR2(240);
856 LOC_ENTERED NUMBER(14,2);
857 LOC_PRECISION NUMBER(2);
858
859 BEGIN
860 LOC_ENTERED := P_ENTERED;
861 LOC_PRECISION := P_PRECISION;
862
863 IF TRUNC(LOC_ENTERED) <> LOC_ENTERED
864 THEN
865
866 /* It has decimals - extract them */
867 LOC_NUM := 100 * (LOC_ENTERED - TRUNC(LOC_ENTERED));
868
869 /* centimos are always masculine */
870 LOC_FEM := FALSE;
871
872 LOC_WORD1 := JEES_NTW_CONV_NUM_FRAGMENT(LOC_NUM, LOC_FEM);
873 /* NMR - bug 1135045 add cero if between 1 and 9 */
874 IF LOC_NUM < 10 THEN
875 /* bug 1735767 */
876 LOC_WORD1 := ' CON CERO'||LOC_WORD1;
877 ELSE
878 /* bug 1735767 */
879 LOC_WORD1 := ' CON'||LOC_WORD1;
880 END IF;
881 END IF;
882
883 /* Convert the main part of the number */
884 LOC_NUM := TRUNC(LOC_ENTERED);
885
886 /*
887 Guess whether the currency is masculine or feminine (this information isn't
888 stored in Financials). Given that the Peseta is feminine and the Euro is
889 masculine I am going to assume that if the currency's precision is 0 that it
890 is feminine, if more (ie 2) that it is masculine
891 */
892
893 IF LOC_PRECISION = 0
894 THEN
895 LOC_FEM := TRUE;
896 ELSE
897 LOC_FEM := FALSE;
898 END IF;
899
900 LOC_WORD2 := JEES_NTW_CONV_NUM_FRAGMENT (LOC_NUM, LOC_FEM);
901
902 LOC_WORD1 := LOC_WORD2||LOC_WORD1;
903
904 RETURN LOC_WORD1;
905
906 END JEES_NTW_NUM_TO_WORDS;
907
908
909 -- code from JLBRPCFP.rdf 115.14 2006/08/22 08:54
910 FUNCTION AMOUNT_WORDS_SPANISH (Chk_Amt number) RETURN varchar2 IS
911
912 l_amt_spanish1 varchar2(200) := '';
913 l_amt_spanish2 varchar2(200) := '';
914 check_amount number;
915
916 BEGIN
917
918 SELECT
919 decode(.00000000001*(mod(abs(Chk_Amt),1000000000000)-mod(abs(Chk_Amt),100000000000)),
920 1,decode(mod(trunc(Chk_Amt/1000000000),1000)-100, 0,'Cien ','Ciento '),
921 2,'Doscientos ',
922 3,'Trescientos ',
923 4,'Cuatrocientos ',
924 5,'Quinientos ',
925 6,'Seiscientos ',
926 7,'Setecientos ',
927 8,'Ochocientos ',
928 9,'Novecientos ',
929 0,null,
930 'ERROR ') ||
931 decode(.0000000001*(mod(abs(Chk_Amt),100000000000)-mod(abs(Chk_Amt),10000000000)),
932 1,(decode(.000000001*(mod(abs(Chk_Amt),10000000000)-mod(abs(Chk_Amt),1000000000)),
933 0,'Diez ',
934 1,'Once ',
935 2,'Doce ',
936 3,'Trece ',
937 4,'Catorce ',
938 5,'Quince ',
939 6,'Dieciseis ',
940 7,'Diecisiete ',
941 8,'Dieciocho ',
942 9,'Diecinueve ',
943 'ERROR ')),
944 2,(decode(.000000001*(mod(abs(Chk_Amt),10000000000)-mod(abs(Chk_Amt),1000000000)),
945 0,'Veinte ',
946 1,'Veintiun ',
947 2,'Veintidos ',
948 3,'Veintitres ',
949 4,'Veinticuatro ',
950 5,'Veinticinco ',
951 6,'Veintiseis ',
952 7,'Veintisiete ',
953 8,'Veintiocho ',
954 9,'Veintinueve ',
955 'ERROR')),
956 3,'Treinta ',
957 4,'Cuarenta ',
958 5,'Cincuenta ',
959 6,'Sesenta ',
960 7,'Setenta ',
961 8,'Ochenta ',
962 9,'Noventa ',
963 NULL) ||
964 decode(.000000001*(mod(abs(Chk_Amt),10000000000)-mod(abs(Chk_Amt),1000000000)),
965 0,NULL,
966 (decode(.0000000001*(mod(abs(Chk_Amt),100000000000)-mod(abs(Chk_Amt),10000000000)),
967 0,NULL,
968 1,NULL,
969 2,NULL,
970 'y '))) ||
971 decode(.0000000001*(mod(abs(Chk_Amt),100000000000)-mod(abs(Chk_Amt),10000000000)),
972 1,NULL,
973 2,NULL,
974 (decode(.000000001*(mod(abs(Chk_Amt),10000000000)-mod(abs(Chk_Amt),1000000000)),
975 1,'Un ',
976 2,'Dos ',
977 3,'Tres ',
978 4,'Cuatro ',
979 5,'Cinco ',
980 6,'Seis ',
981 7,'Siete ',
982 8,'Ocho ',
983 9,'Nueve ',
984 0,NULL,
985 'ERROR '))) ||
986 decode(SIGN(abs(Chk_Amt)-999.99),
987 1,decode(.00000000001*(mod(abs(Chk_Amt),1000000000000)-mod(abs(Chk_Amt),100000000000)),
988 0,decode(.0000000001*(mod(abs(Chk_Amt),100000000000)-mod(abs(Chk_Amt),10000000000)),
989 0,decode(.000000001*(mod(abs(Chk_Amt),10000000000)-mod(abs(Chk_Amt),1000000000)),
990 0,NULL,
991 'Mil '),
992 'Mil '),
993 'Mil '),
994 NULL) ||
995 decode(.00000001*(mod(abs(Chk_Amt),1000000000)-mod(abs(Chk_Amt),100000000)),
996 1,decode(mod(trunc(Chk_Amt/1000000),1000)-100, 0,'Cien ','Ciento '),
997 2,'Doscientos ',
998 3,'Trescientos ',
999 4,'Cuatrocientos ',
1000 5,'Quinientos ',
1001 6,'Seiscientos ',
1002 7,'Setecientos ',
1003 8,'Ochocientos ',
1004 9,'Novecientos ',
1005 0,null,
1006 'ERROR ') ||
1007 decode(.0000001*(mod(abs(Chk_Amt),100000000)-mod(abs(Chk_Amt),10000000)),
1008 1,(decode(.000001*(mod(abs(Chk_Amt),10000000)-mod(abs(Chk_Amt),1000000)),
1009 0,'Diez ',
1010 1,'Once ',
1011 2,'Doce ',
1012 3,'Trece ',
1013 4,'Catorce ',
1014 5,'Quince ',
1015 6,'Dieciseis ',
1016 7,'Diecisiete ',
1017 8,'Dieciocho ',
1018 9,'Diecinueve ',
1019 'ERROR ')),
1020 2,(decode(.000001*(mod(abs(Chk_Amt),10000000)-mod(abs(Chk_Amt),1000000)),
1021 0,'Veinte ',
1022 1,'Veintiun ',
1023 2,'Veintidos ',
1024 3,'Veintitres ',
1025 4,'Veinticuatro ',
1026 5,'Veinticinco ',
1027 6,'Veintiseis ',
1028 7,'Veintisiete ',
1029 8,'Veintiocho ',
1030 9,'Veintinueve ',
1031 'ERROR ')),
1032 3,'Treinta ',
1033 4,'Cuarenta ',
1034 5,'Cincuenta ',
1035 6,'Sesenta ',
1036 7,'Setenta ',
1037 8,'Ochenta ',
1038 9,'Noventa ',
1039 NULL) ||
1040 decode(.000001*(mod(abs(Chk_Amt),10000000)-mod(abs(Chk_Amt),1000000)),
1041 0,NULL,
1042 (decode(.0000001*(mod(abs(Chk_Amt),100000000)-mod(abs(Chk_Amt),10000000)),
1043 0,NULL,
1044 1,NULL,
1045 2,NULL,
1046 'y '))) ||
1047 decode(.0000001*(mod(abs(Chk_Amt),100000000)-mod(abs(Chk_Amt),10000000)),
1048 1,NULL,
1049 2,NULL,
1050 (decode(.000001*(mod(abs(Chk_Amt),10000000)-mod(abs(Chk_Amt),1000000)),
1051 1,'Un ',
1052 2,'Dos ',
1053 3,'Tres ',
1054 4,'Cuatro ',
1055 5,'Cinco ',
1056 6,'Seis ',
1057 7,'Siete ',
1058 8,'Ocho ',
1059 9,'Nueve ',
1060 0,NULL,
1061 'ERROR '))) ||
1062 decode(trunc(Chk_amt/1000000),
1063 0,NULL,
1064 1,'Millon ',
1065 'Millones ') ||
1066 decode(.00001*(mod(abs(Chk_Amt),1000000)-mod(abs(Chk_Amt),100000)),
1067 1,decode(mod(trunc(Chk_Amt/1000),1000)-100, 0,'Cien ','Ciento '),
1068 2,'Doscientos ',
1069 3,'Trescientos ',
1070 4,'Cuatrocientos ',
1071 5,'Quinientos ',
1072 6,'Seiscientos ',
1073 7,'Setecientos ',
1074 8,'Ochocientos ',
1075 9,'Novecientos ',
1076 0,null,
1077 'ERROR ') ||
1078 decode(.0001*(mod(abs(Chk_Amt),100000)-mod(abs(Chk_amt),10000)),
1079 1,(decode(.001*(mod(abs(Chk_Amt),10000)-mod(abs(Chk_Amt),1000)),
1080 0,'Diez ',
1081 1,'Once ',
1082 2,'Doce ',
1083 3,'Trece ',
1084 4,'Catorce ',
1085 5,'Quince ',
1086 6,'Dieciseis ',
1087 7,'Diecisiete ',
1088 8,'Dieciocho ',
1089 9,'Diecinueve ',
1090 'ERROR')),
1091 2,(decode(.001*(mod(abs(Chk_Amt),10000)-mod(abs(Chk_Amt),1000)),
1092 0,'Veinte ',
1093 1,'Veintiun ',
1094 2,'Veintidos ',
1095 3,'Veintitres ',
1096 4,'Veinticuatro ',
1097 5,'Veinticinco ',
1098 6,'Veintiseis ',
1099 7,'Veintisiete ',
1100 8,'Veintiocho ',
1101 9,'Veintinueve ',
1102 'ERROR ')),
1103 3,'Treinta ',
1104 4,'Cuarenta ',
1105 5,'Cincuenta ',
1106 6,'Sesenta ',
1107 7,'Setenta ',
1108 8,'Ochenta ',
1109 9,'Noventa ',
1110 NULL) ||
1111 decode(.001*(mod(abs(Chk_Amt),10000)-mod(abs(Chk_Amt),1000)),
1112 0,NULL,
1113 (decode(.0001*(mod(abs(Chk_Amt),100000)-mod(abs(Chk_Amt),10000)),
1114 0,NULL,
1115 1,NULL,
1116 2,NULL,
1117 'y '))) ||
1118 decode(.0001*(mod(abs(Chk_Amt),100000)-mod(abs(Chk_Amt),10000)),
1119 1,NULL,
1120 2,NULL,
1121 (decode(.001*(mod(abs(Chk_Amt),10000)-mod(abs(Chk_Amt),1000)),
1122 1,'Un ',
1123 2,'Dos ',
1124 3,'Tres ',
1125 4,'Cuatro ',
1126 5,'Cinco ',
1127 6,'Seis ',
1128 7,'Siete ',
1129 8,'Ocho ',
1130 9,'Nueve ',
1131 0,NULL,
1132 'ERROR '))) ||
1133 decode(SIGN(abs(Chk_Amt)-999.99),
1134 1,decode(.00001*(mod(abs(Chk_Amt),1000000)-mod(abs(Chk_Amt),100000)),
1135 0,decode (.0001*(mod(abs(Chk_Amt),100000)-mod(abs(Chk_Amt),10000)),
1136 0,decode(.001*(mod(abs(Chk_Amt),10000)-mod(abs(Chk_Amt),1000)),
1137 0,NULL,
1138 'Mil '),
1139 'Mil '),
1140 'Mil '),
1141 NULL) ||
1142 decode(.01*(mod(abs(Chk_Amt),1000)-mod(abs(Chk_Amt),100)),
1143 1,decode(mod(trunc(Chk_Amt),1000)-100, 0,'Cien ','Ciento '),
1144 2,'Doscientos ',
1145 3,'Trescientos ',
1146 4,'Cuatrocientos ',
1147 5,'Quinientos ',
1148 6,'Seiscientos ',
1149 7,'Setecientos ',
1150 8,'Ochocientos ',
1151 9,'Novecientos ',
1152 NULL) ||
1153 decode(.1*(mod(abs(Chk_Amt),100)-mod(abs(Chk_Amt),10)),
1154 1,(decode(trunc(mod(abs(Chk_Amt),10)),
1155 0,'Diez ',
1156 1,'Once ',
1157 2,'Doce ',
1158 3,'Trece ',
1159 4,'Catorce ',
1160 5,'Quince ',
1161 6,'Dieciseis ',
1162 7,'Diecisiete ',
1163 8,'Dieciocho ',
1164 9,'Diecinueve ',
1165 'ERROR ')),
1166 2,(decode(trunc(mod(abs(Chk_Amt),10)),
1167 0,'Veinte ',
1168 1,'Veintiun ',
1169 2,'Veintidos ',
1170 3,'Veintitres ',
1171 4,'Veinticuatro ',
1172 5,'Veinticinco ',
1173 6,'Veintiseis ',
1174 7,'Veintisiete ',
1175 8,'Veintiocho ',
1176 9,'Veintinueve ',
1177 'ERROR ')),
1178 2,'Veinte ',
1179 3,'Treinta ',
1180 4,'Cuarenta ',
1181 5,'Cincuenta ',
1182 6,'Sesenta ',
1183 7,'Setenta ',
1184 8,'Ochenta ',
1185 9,'Noventa ',
1186 NULL) ||
1187 decode(trunc(mod(abs(Chk_Amt),10)),
1188 0,NULL,
1189 (decode(.1*(mod(abs(Chk_Amt),100)-mod(abs(Chk_Amt),10)),
1190 0,NULL,
1191 1,NULL,
1192 2,NULL,
1193 'y '))) ||
1194 decode(.1*(mod(abs(Chk_Amt),100)-mod(abs(Chk_Amt),10)),
1195 1,NULL,
1196 2,NULL,
1197 (decode(trunc(mod(abs(Chk_Amt),10)),
1198 1,'Un ',
1199 2,'Dos ',
1200 3,'Tres ',
1201 4,'Cuatro ',
1202 5,'Cinco ',
1203 6,'Seis ',
1204 7,'Siete ',
1205 8,'Ocho ',
1206 9,'Nueve ',
1207 0,null,
1208 'ERROR '))) ||
1209 decode(trunc(abs(Chk_Amt)),
1210 0, 'Cero ',
1211 null) ||
1212 decode(100*(abs(Chk_Amt)-trunc(abs(Chk_Amt))),
1213 0,'pesos', 'pesos con ' || TO_CHAR(ABS(100*(abs(Chk_Amt)-trunc(abs(Chk_Amt))))) || ' Centavos ')
1214 INTO l_amt_spanish1
1215 FROM dual;
1216
1217 return(l_amt_spanish1);
1218
1219 END AMOUNT_WORDS_SPANISH;
1220
1221
1222 FUNCTION AMOUNT_WORDS_MEXICAN(P_ENTERED NUMBER,
1223 P_PRECISION INTEGER)
1224 RETURN VARCHAR2 IS
1225 LOC_NUM NUMBER(12);
1226 LOC_FEM BOOLEAN;
1227 LOC_WORD1 VARCHAR2(240);
1228 LOC_WORD2 VARCHAR2(240);
1229 LOC_ENTERED NUMBER(14,2);
1230 LOC_PRECISION NUMBER(2);
1231
1232 BEGIN
1233 LOC_ENTERED := P_ENTERED;
1234 LOC_PRECISION := P_PRECISION;
1235
1236 IF TRUNC(LOC_ENTERED) <> LOC_ENTERED
1237 THEN
1238
1239 /* It has decimals - extract them */
1240 LOC_NUM := 100 * (LOC_ENTERED - TRUNC(LOC_ENTERED));
1241
1242 /* centimos are always masculine */
1243 LOC_FEM := TRUE;
1244 IF LOC_NUM < 10 THEN
1245 LOC_WORD1 := ' 0'||LOC_NUM||'/100 MXN';
1246 ELSE
1247 LOC_WORD1 := ' '||LOC_NUM||'/100 MXN';
1248 END IF;
1249 ELSE
1250
1251 LOC_WORD1 := ' 00/100 MXN';
1252
1253 END IF;
1254
1255 /* Convert the main part of the number */
1256 LOC_NUM := TRUNC(LOC_ENTERED);
1257
1258 /*
1259 Guess whether the currency is masculine or feminine (this information isn't
1260 stored in Financials). Given that the Peseta is feminine and the Euro is
1261 masculine I am going to assume that if the currency's precision is 0 that it
1262 is feminine, if more (ie 2) that it is masculine
1263 */
1264
1265 LOC_FEM := FALSE;
1266
1267
1268 LOC_WORD2 := JEES_NTW_CONV_NUM_FRAGMENT (LOC_NUM, LOC_FEM);
1269
1270 LOC_WORD1 := LOC_WORD2||' PESOS CON'||LOC_WORD1;
1271
1272 RETURN LOC_WORD1;
1273
1274 END AMOUNT_WORDS_MEXICAN;
1275 -- end of package
1276 END IBY_AMOUNT_IN_WORDS;
1277