[Home] [Help]
PACKAGE BODY: APPS.IBY_AMOUNT_IN_WORDS
Source
1 PACKAGE BODY IBY_AMOUNT_IN_WORDS AS
2 /* $Header: ibyamtwb.pls 120.21.12020000.2 2012/07/12 14:41:43 sgogula 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_millions ap_lookup_codes.displayed_field%TYPE;
25 c_billion ap_lookup_codes.displayed_field%TYPE;
26 c_billions ap_lookup_codes.displayed_field%TYPE;
27 c_and ap_lookup_codes.displayed_field%TYPE;
28 g_user_lang fnd_languages.nls_language%TYPE := 'junk';
29
30 TYPE num_lookupTab IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
31 g_numlookup num_lookupTab;
32
33 -- look up a word text for a number under 1000
34 FUNCTION Lookup_num (p_num NUMBER) RETURN VARCHAR2;
35
36 -- code from APXPBFOR.rdf 115.7
37 function APXPBFOR_C_WORD_AMOUNTFormula(p_amount IN NUMBER,
38 p_currency_code IN VARCHAR2,
39 p_precision IN NUMBER)
40 return VARCHAR2;
41
42 -- code from APXPBFOR.rdf 115.7
43 function APXPBFOR_get_word_value (
44 p_amount number,
45 p_unit_singular varchar2,
46 p_unit_plural varchar2,
47 p_sub_unit_singular varchar2,
48 p_sub_unit_plural varchar2,
49 p_unit_ratio number,
50 p_currency_code varchar2
51 ) return varchar2;
52
53
54 -- code from AP_AMOUNT_UTILITIES_PKG 120.3
55 function apamtutb_ap_convert_number (in_numeral IN NUMBER,
56 p_currency_code varchar2) return varchar2;
57
58 -- code from JLBRPCFP.rdf 115.14 2006/08/22 08:54
59 FUNCTION AMOUNT_WORDS_SPANISH (Chk_Amt number) RETURN VARCHAR2;
60
61 -- code from JLBRPCFP.rdf 115.14 2006/08/22 08:54
62 FUNCTION AMOUNT_WORDS_MEXICAN (P_ENTERED NUMBER,
63 P_PRECISION INTEGER)
64 RETURN VARCHAR2;
65
66 -- main API
67 FUNCTION Get_Amount_In_Words(p_amount IN NUMBER,
68 p_currency_code IN VARCHAR2 := NULL,
69 p_precision IN NUMBER := NULL,
70 p_country_code IN VARCHAR2 := NULL)
71 RETURN VARCHAR2
72 IS
73 l_api_name CONSTANT VARCHAR2(30) := 'Get_Amount_In_Words';
74 l_Debug_Module VARCHAR2(255):= G_DEBUG_MODULE || '.' || l_api_name;
75
76 BEGIN
77
78 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
79 debug_level => FND_LOG.LEVEL_PROCEDURE,
80 module => l_Debug_Module);
81
82 iby_debug_pub.add(debug_msg => 'p_amount: ' || p_amount,
83 debug_level => FND_LOG.LEVEL_STATEMENT,
84 module => l_Debug_Module);
85
86 iby_debug_pub.add(debug_msg => 'p_currency_code: ' || p_currency_code,
87 debug_level => FND_LOG.LEVEL_STATEMENT,
88 module => l_Debug_Module);
89
90 iby_debug_pub.add(debug_msg => 'p_precision: ' || p_precision,
91 debug_level => FND_LOG.LEVEL_STATEMENT,
92 module => l_Debug_Module);
93
94 iby_debug_pub.add(debug_msg => 'p_country_code: ' || p_country_code,
95 debug_level => FND_LOG.LEVEL_STATEMENT,
96 module => l_Debug_Module);
97
98 IF p_currency_code ='MXN' THEN
99 RETURN AMOUNT_WORDS_MEXICAN (p_amount, nvl(p_precision, 0));
100 ELSIF p_country_code = 'ES' THEN
101 RETURN JEES_NTW_NUM_TO_WORDS(p_amount, nvl(p_precision, 0));
102 -- bug 5511781: 'ARS', 'BRL', 'CLP', 'COP'
103 ELSIF p_currency_code IN ('BRL') THEN
104 RETURN AMOUNT_WORDS_PORTUGESE(p_amount);
105 ELSIF p_currency_code IN ('ARS', 'CLP', 'COP') THEN
106 RETURN AMOUNT_WORDS_SPANISH (p_amount);
107 -- For Israel...
108 ELSIF p_currency_code IN ('ILS') THEN
109 RETURN trans_money(p_amount);
110 ELSE
111 RETURN APXPBFOR_C_WORD_AMOUNTFormula(p_amount, p_currency_code, p_precision);
112 END IF;
113
114 END Get_Amount_In_Words;
115
116
117 -- code from APXPBFOR.rdf 115.7
118 function APXPBFOR_C_WORD_AMOUNTFormula(p_amount IN NUMBER,
119 p_currency_code IN VARCHAR2,
120 p_precision IN NUMBER)
121 return VARCHAR2
122 is
123
124 l_word_text varchar2(240);
125 l_width number := 58; -- Width of word amount field
126 l_unit_singular_msg_name varchar2(30) := 'IBY_AMT_IN_WORD_US_';
127 l_unit_plural_msg_name varchar2(30) := 'IBY_AMT_IN_WORD_UP_';
128 l_unit_sub_singular_msg_name varchar2(30) := 'IBY_AMT_IN_WORD_SUS_';
129 l_unit_sub_plural_msg_name varchar2(30) := 'IBY_AMT_IN_WORD_SUP_';
130 l_unit_singular_str varchar2(80);
131 l_unit_plural_str varchar2(80);
132 l_unit_sub_singular_str varchar2(80);
133 l_unit_sub_plural_str varchar2(80);
134 l_precision NUMBER;
135 l_unit_ratio NUMBER;
136
137 l_api_name CONSTANT VARCHAR2(30) := 'Get_Amount_In_Words';
138 l_Debug_Module VARCHAR2(255):= G_DEBUG_MODULE || l_api_name;
139
140 CURSOR l_precision_csr (p_currency_code IN VARCHAR2) IS
141 SELECT precision
142 FROM fnd_currencies
143 WHERE currency_code = p_currency_code;
144
145 begin
146
147 -- 'USD', 'EUR', 'JPY', 'CNY', 'CHF', 'DKK', 'FIM', 'NOK', 'SEK'
148 l_unit_singular_msg_name := l_unit_singular_msg_name || p_currency_code;
149 l_unit_plural_msg_name := l_unit_plural_msg_name || p_currency_code;
150 l_unit_sub_singular_msg_name := l_unit_sub_singular_msg_name || p_currency_code;
151 l_unit_sub_plural_msg_name := l_unit_sub_plural_msg_name || p_currency_code;
152
153 iby_debug_pub.add(debug_msg => 'formulated message names: ',
154 debug_level => FND_LOG.LEVEL_STATEMENT,
155 module => l_Debug_Module);
156
157 iby_debug_pub.add(debug_msg => 'l_unit_singular_msg_name: ' || l_unit_singular_msg_name,
158 debug_level => FND_LOG.LEVEL_STATEMENT,
159 module => l_Debug_Module);
160
161 iby_debug_pub.add(debug_msg => 'l_unit_plural_msg_name: ' || l_unit_plural_msg_name,
162 debug_level => FND_LOG.LEVEL_STATEMENT,
163 module => l_Debug_Module);
164
165 iby_debug_pub.add(debug_msg => 'l_unit_sub_singular_msg_name: ' || l_unit_sub_singular_msg_name,
166 debug_level => FND_LOG.LEVEL_STATEMENT,
167 module => l_Debug_Module);
168
169 iby_debug_pub.add(debug_msg => 'l_unit_sub_plural_msg_name: ' || l_unit_sub_plural_msg_name,
170 debug_level => FND_LOG.LEVEL_STATEMENT,
171 module => l_Debug_Module);
172
173 BEGIN
174 fnd_message.set_name('IBY', l_unit_singular_msg_name);
175 l_unit_singular_str := fnd_message.get;
176
177 IF l_unit_singular_str = l_unit_singular_msg_name THEN
178 l_unit_singular_str := NULL;
179 END IF;
180
181 iby_debug_pub.add(debug_msg => 'Got translated text for ' || l_unit_singular_msg_name
182 || ': ' || l_unit_singular_str,
183 debug_level => FND_LOG.LEVEL_STATEMENT,
184 module => l_Debug_Module);
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 NULL;
189 END;
190
191
192 BEGIN
193 fnd_message.set_name('IBY', l_unit_plural_msg_name);
194 l_unit_plural_str := fnd_message.get;
195
196 IF l_unit_plural_str = l_unit_plural_msg_name THEN
197 l_unit_plural_str := NULL;
198 END IF;
199
200 iby_debug_pub.add(debug_msg => 'Got translated text for ' || l_unit_plural_msg_name
201 || ': ' || l_unit_plural_str,
202 debug_level => FND_LOG.LEVEL_STATEMENT,
203 module => l_Debug_Module);
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 NULL;
208 END;
209
210 BEGIN
211 fnd_message.set_name('IBY', l_unit_sub_singular_msg_name);
212 l_unit_sub_singular_str := fnd_message.get;
213
214 IF l_unit_sub_singular_str = l_unit_sub_singular_msg_name THEN
215 l_unit_sub_singular_str := NULL;
216 END IF;
217
218 iby_debug_pub.add(debug_msg => 'Got translated text for ' || l_unit_sub_singular_msg_name
219 || ': ' || l_unit_sub_singular_str,
220 debug_level => FND_LOG.LEVEL_STATEMENT,
221 module => l_Debug_Module);
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 NULL;
226 END;
227
228 BEGIN
229 fnd_message.set_name('IBY', l_unit_sub_plural_msg_name);
230 l_unit_sub_plural_str := fnd_message.get;
231
232 IF l_unit_sub_plural_str = l_unit_sub_plural_msg_name THEN
233 l_unit_sub_plural_str := NULL;
234 END IF;
235
236 iby_debug_pub.add(debug_msg => 'Got translated text for ' || l_unit_sub_plural_msg_name
237 || ': ' || l_unit_sub_plural_str,
238 debug_level => FND_LOG.LEVEL_STATEMENT,
239 module => l_Debug_Module);
240
241 EXCEPTION
242 WHEN OTHERS THEN
243 NULL;
244 END;
245
246 BEGIN
247 IF p_precision IS NULL THEN
248
249 IF p_currency_code IS NULL THEN
250 iby_debug_pub.add(debug_msg => 'Warning: p_precision and p_currency_code are both null! ',
251 debug_level => FND_LOG.LEVEL_STATEMENT,
252 module => l_Debug_Module);
253 ELSE
254
255 OPEN l_precision_csr (p_currency_code);
256 FETCH l_precision_csr INTO l_precision;
257 CLOSE l_precision_csr;
258
259 END IF;
260
261 ELSE
262 l_precision := p_precision;
263 END IF;
264
265 iby_debug_pub.add(debug_msg => 'l_precision: ' || l_precision,
266 debug_level => FND_LOG.LEVEL_STATEMENT,
267 module => l_Debug_Module);
268
269 IF l_precision = 1 THEN
270 l_unit_ratio := 10;
271 ELSIF l_precision = 2 THEN
272 l_unit_ratio := 100;
273 ELSIF l_precision = 3 THEN
274 l_unit_ratio := 1000;
275 END IF;
276
277 EXCEPTION
278 WHEN OTHERS THEN
279 NULL;
280 END;
281
282 l_word_text :=
283 APXPBFOR_get_word_value (
284 p_amount => p_amount,
285 p_unit_singular => l_unit_singular_str,
286 p_unit_plural => l_unit_plural_str,
287 p_sub_unit_singular => l_unit_sub_singular_str,
288 p_sub_unit_plural => l_unit_sub_plural_str,
289 p_unit_ratio => l_unit_ratio,
290 p_currency_code => p_currency_code);
291 -- Format the output to have asterisks on right-hand side
292 /* if NVL(length(l_word_text), 0) <= l_width then
293 l_word_text := rpad(l_word_text,l_width,'*');
294 elsif NVL(length(l_word_text), 0) <= l_width*2 then
295 -- Allow for word wrapping
296 -- Removed the logic for word wrapping for Bug 7433132
297 -- l_word_text := rpad(l_word_text,l_width*2 -
298 -- (l_width-instr(substr(l_word_text,1,l_width+1),' ',-1)),'*');
299 l_word_text := rpad(l_word_text,l_width*2,'*');
300 elsif NVL(length(l_word_text), 0) <= l_width*3 then
301 l_word_text := rpad(l_word_text,l_width*3,'*');
302 end if;
303 */ --Bug 10140424
304 l_word_text := rpad(l_word_text,length(l_word_text)+5,'*');
305
306 iby_debug_pub.add(debug_msg => 'Amount text: ' || l_word_text,
307 debug_level => FND_LOG.LEVEL_STATEMENT,
308 module => l_Debug_Module);
309
310 return(l_word_text);
311
312 EXCEPTION
313 WHEN OTHERS THEN
314 iby_debug_pub.add(debug_msg => 'Error in getting amount text. Returning null.',
315 debug_level => FND_LOG.LEVEL_STATEMENT,
316 module => l_Debug_Module);
317 RETURN NULL;
318
319 END APXPBFOR_C_WORD_AMOUNTFormula;
320
321
322 -- code from APXPBFOR.rdf 115.7
323 function APXPBFOR_get_word_value (
324 p_amount number,
325 p_unit_singular varchar2,
326 p_unit_plural varchar2,
327 p_sub_unit_singular varchar2,
328 p_sub_unit_plural varchar2,
329 p_unit_ratio number,
330 p_currency_code varchar2
331 ) return varchar2 is
332 l_word_amount varchar2(240) := apamtutb_ap_convert_number(trunc(p_amount), p_currency_code);
333
334 -- Removed the convert_amount and added the package call ap_amount_utilities_pkg
335 -- which handles this word conversion from number.
336 -- For bug 2569922
337
338 l_currency_word varchar2(240);
339 l_part_amount_word varchar2(240);
340 l_log integer;
341 session_language fnd_languages.nls_language%TYPE;
342
343 /* This is a workaround until bug #165793 is fixed */
344 function my_log (a integer, b integer) return number is
345 begin
346 if a <> 10 then return(null);
347 elsif b > 0 and b <= 10 then return(1);
348 elsif b > 10 and b <= 100 then return(2);
349 elsif b > 100 and b <= 1000 then return(3);
350 else return(null);
351 end if;
352 RETURN NULL; end;
353
354 begin
355 l_log := my_log(10,p_unit_ratio);
356
357 select substr(userenv('LANGUAGE'),1,instr(userenv('LANGUAGE'),'_')-1)
358 into session_language
359 from dual;
360
361 if p_unit_ratio in (0,1) or p_unit_ratio is null or (p_amount-trunc(p_amount) = 0 and session_language = 'ARABIC') then
362 select initcap(lower(
363 l_word_amount||' '||
364 decode(trunc(p_amount),
365 1,p_unit_singular,
366 p_unit_plural)
367 ))
368 into l_currency_word
369 from dual;
370
371 --bug 14005849
372 elsif(p_currency_code = 'AED') then
373 l_part_amount_word := apamtutb_ap_convert_number(lpad(to_char(trunc((p_amount-trunc(p_amount))*p_unit_ratio)),
374 ceil(l_log),'0'),p_currency_code);
375 select initcap(lower(
376 l_word_amount||' '||
377 decode(trunc(p_amount),
378 1,p_unit_singular,
379 p_unit_plural)||' '||displayed_field||' '||l_part_amount_word
380 ||' '||
381 decode(trunc((p_amount-trunc(p_amount))*p_unit_ratio),
382 -1,null,
383 p_sub_unit_plural)
384 ))
385 into l_currency_word
386 from ap_lookup_codes
387 where lookup_code = 'AND'
388 and lookup_type = 'NLS TRANSLATION';
389
390 else
391
392 -- Added the package call ap_amount_utilities_pkg
393 -- which handles the word conversion for decimal digits
394 -- For bug 2569922
395 l_part_amount_word := apamtutb_ap_convert_number(lpad(to_char(trunc((p_amount-trunc(p_amount))*p_unit_ratio)),
396 ceil(l_log),'0'),p_currency_code);
397 select initcap(lower(
398 l_word_amount||' '||
399 decode(trunc(p_amount),
400 1,p_unit_singular,
401 p_unit_plural)||' '||displayed_field||' '||l_part_amount_word
402 ||' '||
403 decode(trunc((p_amount-trunc(p_amount))*p_unit_ratio),
404 1,p_sub_unit_singular,
405 p_sub_unit_plural)
406 ))
407 into l_currency_word
408 from ap_lookup_codes
409 where lookup_code = 'AND'
410 and lookup_type = 'NLS TRANSLATION';
411 end if;
412
413 return(l_currency_word);
414 END APXPBFOR_get_word_value;
415
416
417 FUNCTION Lookup_num (p_num NUMBER) RETURN VARCHAR2 IS
418 BEGIN
419
420 RETURN g_numlookup(p_num);
421
422 exception
423 when no_data_found then
424
425 select description
426 into g_numlookup(p_num)
427 from ap_lookup_codes
428 where lookup_code = to_char(p_num)
429 and lookup_type = 'NUMBERS';
430
431 iby_debug_pub.add(debug_msg => 'Adding to lookup cache ' || p_num || ': ' || g_numlookup(p_num),
432 debug_level => FND_LOG.LEVEL_STATEMENT,
433 module => G_DEBUG_MODULE || '.Lookup_num');
434
435 RETURN g_numlookup(p_num);
436 END;
437
438 -- code from AP_AMOUNT_UTILITIES_PKG 120.3
439 function apamtutb_ap_convert_number (in_numeral IN NUMBER,
440 p_currency_code varchar2) return varchar2 is
441 number_too_large exception;
442 numeral integer := abs(in_numeral);
443 max_digit integer := 12; -- for numbers less than a trillion
444 number_text varchar2(240) := '';
445 billion_segN number;
446 million_segN number;
447 thousand_segN number;
448 units_segN number;
449 thousand_lookup varchar2(80);
450 units_lookup varchar2(80);
451 session_language fnd_languages.nls_language%TYPE;
452 thousand number := power(10,3);
453 million number := power(10,6);
454 billion number := power(10,9);
455 l_Debug_Module VARCHAR2(255):= G_DEBUG_MODULE || '.apamtutb_ap_convert_number';
456
457 BEGIN
458
459 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
460 debug_level => FND_LOG.LEVEL_PROCEDURE,
461 module => l_Debug_Module);
462
463 if numeral >= power(10,max_digit) then
464 raise number_too_large;
465 end if;
466
467 select substr(userenv('LANGUAGE'),1,instr(userenv('LANGUAGE'),'_')-1)
468 into session_language
469 from dual;
470
471 iby_debug_pub.add(debug_msg => 'numeral: ' || numeral,
472 debug_level => FND_LOG.LEVEL_STATEMENT,
473 module => l_Debug_Module);
474
475 iby_debug_pub.add(debug_msg => 'session_language: ' || session_language,
476 debug_level => FND_LOG.LEVEL_STATEMENT,
477 module => l_Debug_Module);
478
479 IF g_user_lang <> session_language THEN
480
481 iby_debug_pub.add(debug_msg => 'g_user_lang <> session_language, caching lookups ',
482 debug_level => FND_LOG.LEVEL_STATEMENT,
483 module => l_Debug_Module);
484
485 g_user_lang := session_language;
486
487 select ' '||lc1.displayed_field||' ',
488 ' '||lc7.displayed_field||' ',
489 ' '||lc2.displayed_field||' ',
490 ' '||lc6.displayed_field||' ',
491 ' '||lc3.displayed_field||' ',
492 lc4.displayed_field,
493 lc5.displayed_field
494 into c_billion,
495 c_billions,
496 c_million,
497 c_millions,
498 c_thousand,
499 c_zero,
500 c_and
501 from ap_lookup_codes lc1,
502 ap_lookup_codes lc2,
503 ap_lookup_codes lc3,
504 ap_lookup_codes lc4,
505 ap_lookup_codes lc5,
506 ap_lookup_codes lc6,
507 ap_lookup_codes lc7
508 where lc1.lookup_code = 'BILLION'
509 and lc1.lookup_type = 'NLS TRANSLATION'
510 and lc2.lookup_code = 'MILLION'
511 and lc2.lookup_type = 'NLS TRANSLATION'
512 and lc3.lookup_code = 'THOUSAND'
513 and lc3.lookup_type = 'NLS TRANSLATION'
514 and lc4.lookup_code = 'ZERO'
515 and lc4.lookup_type = 'NLS TRANSLATION'
516 and lc5.lookup_code = 'AND'
517 and lc5.lookup_type = 'NLS TRANSLATION'
518 and lc6.lookup_code = 'MILLIONS'
519 and lc6.lookup_type = 'NLS TRANSLATION'
520 and lc7.lookup_code = 'BILLIONS'
521 and lc7.lookup_type = 'NLS TRANSLATION';
522
523 g_numlookup.DELETE;
524
525 END IF;
526
527 --For Bug459665
528 if numeral = 0 then
529 RETURN c_zero;
530 end if;
531
532
533 billion_segN := trunc(numeral/billion);
534
535 numeral := numeral - (billion_segN * billion);
536 million_segN := trunc(numeral/million);
537
538 numeral := numeral - (million_segN * million);
539 thousand_segN := trunc(numeral/thousand);
540
541 units_segN := mod(numeral,thousand);
542
543 iby_debug_pub.add(debug_msg => 'billion_segN: ' || billion_segN,
544 debug_level => FND_LOG.LEVEL_STATEMENT,
545 module => l_Debug_Module);
546
547 iby_debug_pub.add(debug_msg => 'million_segN: ' || million_segN,
548 debug_level => FND_LOG.LEVEL_STATEMENT,
549 module => l_Debug_Module);
550
551 iby_debug_pub.add(debug_msg => 'thousand_segN: ' || thousand_segN,
552 debug_level => FND_LOG.LEVEL_STATEMENT,
553 module => l_Debug_Module);
554
555 iby_debug_pub.add(debug_msg => 'units_segN: ' || units_segN,
556 debug_level => FND_LOG.LEVEL_STATEMENT,
557 module => l_Debug_Module);
558
559 if billion_segN <> 0 then
560 if(billion_segN > 1 AND session_language = 'PORTUGUESE'
561 AND p_currency_code='MZN') THEN
562 number_text := number_text||Lookup_num(billion_segN) ||c_billions;
563 else
564 number_text := number_text||Lookup_num(billion_segN) ||c_billion;
565 end if;
566 end if;
567
568 if million_segN <> 0 then
569 if (session_language = 'ARABIC' and Length(number_text) > 0) THEN
570 number_text := number_text||c_and||' '||Lookup_num(million_segN)||c_million;
571 else
572 if(million_segN > 1 AND (session_language = 'SPANISH'
573 OR (session_language = 'PORTUGUESE' AND p_currency_code='MZN'))) THEN
574 number_text := number_text||Lookup_num(million_segN)||c_millions;
575 else
576 number_text := number_text||Lookup_num(million_segN)||c_million;
577 end if;
578 end if;
579 end if;
580
581 if (((thousand_segN = 0 AND million_segN <> 0) or (thousand_segN = 0 AND million_segN = 0 AND billion_segN <> 0))
582 AND (session_language = 'PORTUGUESE' AND p_currency_code='MZN')) then
583 number_text := number_text||'DE ';
584 end if;
585
586 if thousand_segN <> 0 then
587 --Bug 335063 fix.
588 --Portugese, SPANISH added for bug 8319904
589 if (session_language = 'FRENCH' or session_language = 'CANADIAN FRENCH' or session_language = 'PORTUGUESE'
590 or session_language = 'SPANISH' or session_language = 'ARABIC')
591 and thousand_segN = 1 then
592 thousand_lookup := null;
593 ELSE
594 thousand_lookup := Lookup_num(thousand_segN);
595 end if;
596
597 if (session_language = 'ARABIC' and Length(number_text) > 0) THEN
598 number_text := number_text||c_and||' '||thousand_lookup||c_thousand;
599 else
600 number_text := number_text||thousand_lookup||c_thousand;
601 end if;
602
603 end if;
604
605 if units_segN <> 0 then
606 if (session_language = 'ARABIC' and Length(number_text) > 0) THEN
607 number_text := number_text||c_and||' '||Lookup_num(units_segN);
608 ELSE
609 number_text := number_text||Lookup_num(units_segN);
610 END IF;
611 end if;
612
613 number_text := ltrim(number_text);
614 number_text := upper(substr(number_text,1,1)) ||
615 rtrim(lower(substr(number_text,2,length(number_text))));
616
617 iby_debug_pub.add(debug_msg => 'returning number_text: ' || number_text,
618 debug_level => FND_LOG.LEVEL_STATEMENT,
619 module => l_Debug_Module);
620
621 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
622 debug_level => FND_LOG.LEVEL_PROCEDURE,
623 module => l_Debug_Module);
624
625 return(number_text);
626
627 exception
628 when number_too_large then
629 return(null);
630 when others then
631 return(null);
632 END apamtutb_ap_convert_number;
633
634
635 -- code from JEES_NTW pacakge 120.3
636 FUNCTION JEES_NTW_CONV_NUM_FRAGMENT (P_NUM INTEGER, /* integer version of number */
637 P_FEM BOOLEAN) /* masculine or feminine */
638 RETURN VARCHAR2 IS /* word version of integer */
639
640 LOC_LONGI NUMBER(2);
641 LOC_DIV1 NUMBER(2);
642 LOC_DIV2 NUMBER(12);
643 LOC_MOD NUMBER(2);
644 LOC_CAMPO VARCHAR2(13);
645 LOC_RESTO NUMBER(3);
646 LOC_SW1 VARCHAR2(1);
647 LOC_SW2 VARCHAR2(1);
648 LOC_PASO NUMBER(12);
649 LOC_NUMERO1 NUMBER(12);
650 LOC_UNO NUMBER(1);
651 LOC_DOS NUMBER(1);
652 LOC_TRES NUMBER(1);
653 LOC_LETRAS VARCHAR2(240);
654 LOC_NUMERO NUMBER(12);
655 LOC_FEMININE BOOLEAN;
656 BEGIN
657 LOC_CAMPO := '1000000000000';
658 LOC_NUMERO := P_NUM;
659 LOC_FEMININE := P_FEM;
660 LOC_SW1 := 'N';
661 LOC_SW2 := 'N';
662 LOC_LETRAS := NULL;
663 IF LOC_NUMERO = 0 THEN
664 LOC_LETRAS := 'CERO';
665 ELSE
666 WHILE LOC_NUMERO > 0 LOOP
667 IF LOC_SW1 = 'S' AND LOC_SW2 = 'N' AND LOC_PASO = 1
668 AND LOC_NUMERO < 999999 THEN
669 LOC_LETRAS := LOC_LETRAS||' MILLONES';
670 LOC_PASO := NULL;
671 LOC_SW1 := 'N';
672 END IF;
673 LOC_LONGI := TO_NUMBER(LENGTH(TO_CHAR(LOC_NUMERO)));
674 LOC_MOD := MOD(LOC_LONGI,3);
675 LOC_DIV1 := LOC_LONGI - LOC_MOD;
676 IF LOC_MOD = 0 THEN
677 LOC_DIV1 := LOC_DIV1 - 3;
678 END IF;
679 LOC_DIV2 := TO_NUMBER(SUBSTR(LOC_CAMPO,1,LOC_DIV1+1));
680 LOC_NUMERO1 := TRUNC(LOC_NUMERO/LOC_DIV2);
681
682 IF LOC_NUMERO >= 100 THEN
683 LOC_UNO := TRUNC(LOC_NUMERO1/100);
684 IF LOC_UNO = 1 THEN
685 IF LOC_NUMERO1 = 100 THEN
686 LOC_LETRAS := LOC_LETRAS||' CIEN';
687 ELSE
688 LOC_LETRAS := LOC_LETRAS||' CIENTO';
689 END IF;
690 ELSIF LOC_UNO = 2 THEN
691 IF LOC_NUMERO > 999999
692 /* NMR - bug 839444 */
693 OR NOT LOC_FEMININE THEN
694 LOC_LETRAS := LOC_LETRAS||' DOSCIENTOS';
695 ELSE
696 LOC_LETRAS := LOC_LETRAS||' DOSCIENTAS';
697 END IF;
698 ELSIF LOC_UNO = 3 THEN
699 IF LOC_NUMERO > 999999
700 /* NMR - bug 839444 */
701 OR NOT LOC_FEMININE THEN
702 LOC_LETRAS := LOC_LETRAS||' TRESCIENTOS';
703 ELSE
704 LOC_LETRAS := LOC_LETRAS||' TRESCIENTAS';
705 END IF;
706 ELSIF LOC_UNO = 4 THEN
707 IF LOC_NUMERO > 999999
708 /* NMR - bug 839444 */
709 OR NOT LOC_FEMININE THEN
710 LOC_LETRAS := LOC_LETRAS||' CUATROCIENTOS';
711 ELSE
712 LOC_LETRAS := LOC_LETRAS||' CUATROCIENTAS';
713 END IF;
714 ELSIF LOC_UNO = 5 THEN
715 IF LOC_NUMERO > 999999
716 /* NMR - bug 839444 */
717 OR NOT LOC_FEMININE THEN
718 LOC_LETRAS := LOC_LETRAS||' QUINIENTOS';
719 ELSE
720 LOC_LETRAS := LOC_LETRAS||' QUINIENTAS';
721 END IF;
722 ELSIF LOC_UNO = 6 THEN
723 IF LOC_NUMERO > 999999
724 /* NMR - bug 839444 */
725 OR NOT LOC_FEMININE THEN
726 LOC_LETRAS := LOC_LETRAS||' SEISCIENTOS';
727 ELSE
728 LOC_LETRAS := LOC_LETRAS||' SEISCIENTAS';
729 END IF;
730 ELSIF LOC_UNO = 7 THEN
731 IF LOC_NUMERO > 999999
732 /* NMR - bug 839444 */
733 OR NOT LOC_FEMININE THEN
734 LOC_LETRAS := LOC_LETRAS||' SETECIENTOS';
735 ELSE
736 LOC_LETRAS := LOC_LETRAS||' SETECIENTAS';
737 END IF;
738 ELSIF LOC_UNO = 8 THEN
739 IF LOC_NUMERO > 999999
740 /* NMR - bug 839444 */
741 OR NOT LOC_FEMININE THEN
742 LOC_LETRAS := LOC_LETRAS||' OCHOCIENTOS';
743 ELSE
744 LOC_LETRAS := LOC_LETRAS||' OCHOCIENTAS';
745 END IF;
746 ELSIF LOC_UNO = 9 THEN
747 IF LOC_NUMERO > 999999
748 /* NMR - bug 839444 */
749 OR NOT LOC_FEMININE THEN
750 LOC_LETRAS := LOC_LETRAS||' NOVECIENTOS';
751 ELSE
752 LOC_LETRAS := LOC_LETRAS||' NOVECIENTAS';
753 END IF;
754 END IF;
755 ELSE
756 LOC_UNO := 0;
757 END IF;
758 LOC_DOS := TRUNC((LOC_NUMERO1-(LOC_UNO*100))/10);
759
760 IF LOC_DOS < 3
761 AND (LOC_DOS > 0
762 OR LOC_NUMERO > 999999)
763 /*
764 Added by NMR because it wasn't recognising the final number under
765 certain circumtances
766 */
767 OR LOC_DOS = 0
768 THEN
769
770 LOC_RESTO := LOC_NUMERO1-(LOC_UNO*100);
771
772 IF LOC_RESTO = 1 THEN
773
774 IF LOC_FEMININE AND LOC_NUMERO < 1000000
775 THEN
776 /* NMR - bug 1135045 capture 1000000000 */
777 IF LOC_LONGI NOT IN (4,10) THEN
778 LOC_LETRAS := LOC_LETRAS||' UNA';
779 END IF;
780 ELSIF LOC_LONGI IN (1,2,3)
781 THEN
782 LOC_LETRAS := LOC_LETRAS||' UNO';
783 ELSE
784 /* NMR - bug 1135045 capture 1000000000 */
785 IF LOC_LONGI NOT IN (4,10) THEN
786 LOC_LETRAS := LOC_LETRAS||' UN';
787 END IF;
788 END IF;
789
790 ELSIF LOC_RESTO = 2 THEN
791 LOC_LETRAS := LOC_LETRAS||' DOS';
792 ELSIF LOC_RESTO = 3 THEN
793 LOC_LETRAS := LOC_LETRAS||' TRES';
794 ELSIF LOC_RESTO = 4 THEN
795 LOC_LETRAS := LOC_LETRAS||' CUATRO';
796 ELSIF LOC_RESTO = 5 THEN
797 LOC_LETRAS := LOC_LETRAS||' CINCO';
798 ELSIF LOC_RESTO = 6 THEN
799 LOC_LETRAS := LOC_LETRAS||' SEIS';
800 ELSIF LOC_RESTO = 7 THEN
801 LOC_LETRAS := LOC_LETRAS||' SIETE';
802 ELSIF LOC_RESTO = 8 THEN
803 LOC_LETRAS := LOC_LETRAS||' OCHO';
804 ELSIF LOC_RESTO = 9 THEN
805 LOC_LETRAS := LOC_LETRAS||' NUEVE';
806 ELSIF LOC_RESTO = 10 THEN
807 LOC_LETRAS := LOC_LETRAS||' DIEZ';
808 ELSIF LOC_RESTO = 11 THEN
809 LOC_LETRAS := LOC_LETRAS||' ONCE';
810 ELSIF LOC_RESTO = 12 THEN
811 LOC_LETRAS := LOC_LETRAS||' DOCE';
812 ELSIF LOC_RESTO = 13 THEN
813 LOC_LETRAS := LOC_LETRAS||' TRECE';
814 ELSIF LOC_RESTO = 14 THEN
815 LOC_LETRAS := LOC_LETRAS||' CATORCE';
816 ELSIF LOC_RESTO = 15 THEN
817 LOC_LETRAS := LOC_LETRAS||' QUINCE';
818 ELSIF LOC_RESTO = 16 THEN
819 LOC_LETRAS := LOC_LETRAS||' DIECISEIS';
820 ELSIF LOC_RESTO = 17 THEN
821 LOC_LETRAS := LOC_LETRAS||' DIECISIETE';
822 ELSIF LOC_RESTO = 18 THEN
823 LOC_LETRAS := LOC_LETRAS||' DIECIOCHO';
824 ELSIF LOC_RESTO = 19 THEN
825 LOC_LETRAS := LOC_LETRAS||' DIECINUEVE';
826 ELSIF LOC_RESTO = 20 THEN
827 LOC_LETRAS := LOC_LETRAS||' VEINTE';
828 ELSIF LOC_RESTO = 21 THEN
829 IF LOC_FEMININE AND LOC_NUMERO < 1000000
830 THEN
831 LOC_LETRAS := LOC_LETRAS||' VEINTIUNA';
832 ELSIF LOC_LONGI IN (1,2,3)
833 THEN
834 LOC_LETRAS := LOC_LETRAS||' VEINTIUNO';
835 ELSE
836 LOC_LETRAS := LOC_LETRAS||' VEINTIUN';
837 END IF;
838 ELSIF LOC_RESTO = 22 THEN
839 LOC_LETRAS := LOC_LETRAS||' VEINTIDOS';
840 ELSIF LOC_RESTO = 23 THEN
841 LOC_LETRAS := LOC_LETRAS||' VEINTITRES';
842 ELSIF LOC_RESTO = 24 THEN
843 LOC_LETRAS := LOC_LETRAS||' VEINTICUATRO';
844 ELSIF LOC_RESTO = 25 THEN
845 LOC_LETRAS := LOC_LETRAS||' VEINTICINCO';
846 ELSIF LOC_RESTO = 26 THEN
847 LOC_LETRAS := LOC_LETRAS||' VEINTISEIS';
848 ELSIF LOC_RESTO = 27 THEN
849 LOC_LETRAS := LOC_LETRAS||' VEINTISIETE';
850 ELSIF LOC_RESTO = 28 THEN
851 LOC_LETRAS := LOC_LETRAS||' VEINTIOCHO';
852 ELSIF LOC_RESTO = 29 THEN
853 LOC_LETRAS := LOC_LETRAS||' VEINTINUEVE';
854 END IF;
855
856 ELSIF LOC_DOS >= 3 THEN
857 IF LOC_DOS = 3 THEN
858 LOC_LETRAS := LOC_LETRAS||' TREINTA';
859 ELSIF LOC_DOS = 4 THEN
860 LOC_LETRAS := LOC_LETRAS||' CUARENTA';
861 ELSIF LOC_DOS = 5 THEN
862 LOC_LETRAS := LOC_LETRAS||' CINCUENTA';
863 ELSIF LOC_DOS = 6 THEN
864 LOC_LETRAS := LOC_LETRAS||' SESENTA';
865 ELSIF LOC_DOS = 7 THEN
866 LOC_LETRAS := LOC_LETRAS||' SETENTA';
867 ELSIF LOC_DOS = 8 THEN
868 LOC_LETRAS := LOC_LETRAS||' OCHENTA';
869 ELSIF LOC_DOS = 9 THEN
870 LOC_LETRAS := LOC_LETRAS||' NOVENTA';
871 END IF;
872 LOC_TRES := LOC_NUMERO1-(LOC_UNO*100) - (LOC_DOS*10);
873
874 IF LOC_TRES = 1 THEN
875 IF LOC_FEMININE AND LOC_NUMERO < 1000000
876 THEN
877 LOC_LETRAS := LOC_LETRAS||' Y UNA';
878 ELSIF LOC_LONGI IN (1,2,3)
879 THEN
880 LOC_LETRAS := LOC_LETRAS||' Y UNO';
881 ELSE
882 LOC_LETRAS := LOC_LETRAS||' Y UN';
883 END IF;
884
885 ELSIF LOC_TRES = 2 THEN
886 LOC_LETRAS := LOC_LETRAS||' Y DOS';
887 ELSIF LOC_TRES = 3 THEN
888 LOC_LETRAS := LOC_LETRAS||' Y TRES';
889 ELSIF LOC_TRES = 4 THEN
890 LOC_LETRAS := LOC_LETRAS||' Y CUATRO';
891 ELSIF LOC_TRES = 5 THEN
892 LOC_LETRAS := LOC_LETRAS||' Y CINCO';
893 ELSIF LOC_TRES = 6 THEN
894 LOC_LETRAS := LOC_LETRAS||' Y SEIS';
895 ELSIF LOC_TRES = 7 THEN
896 LOC_LETRAS := LOC_LETRAS||' Y SIETE';
897 ELSIF LOC_TRES = 8 THEN
898 LOC_LETRAS := LOC_LETRAS||' Y OCHO';
899 ELSIF LOC_TRES = 9 THEN
900 LOC_LETRAS := LOC_LETRAS||' Y NUEVE';
901 END IF;
902 END IF;
903 IF LOC_LONGI BETWEEN 4 AND 6 AND LOC_NUMERO1 > 0 THEN
904 LOC_LETRAS := LOC_LETRAS||' MIL';
905 ELSIF LOC_LONGI BETWEEN 7 AND 9 AND LOC_NUMERO1 > 0 THEN
906 IF LOC_NUMERO1 = 1 THEN
907 LOC_LETRAS := LOC_LETRAS||' MILLON';
908 ELSE
909 LOC_LETRAS := LOC_LETRAS||' MILLONES';
910 END IF;
911 LOC_SW2 := 'S';
912 ELSIF LOC_LONGI BETWEEN 10 AND 12 AND LOC_NUMERO1 > 0 THEN
913 LOC_PASO := 1;
914 LOC_SW1 := 'S';
915 LOC_LETRAS := LOC_LETRAS||' MIL';
916 END IF;
917 LOC_NUMERO := LOC_NUMERO - (LOC_NUMERO1 * LOC_DIV2);
918 END LOOP;
919 IF LOC_SW1 = 'S' AND LOC_SW2 = 'N' THEN
920 LOC_LETRAS := LOC_LETRAS||' MILLONES';
921 END IF;
922 END IF;
923 RETURN LOC_LETRAS;
924
925 END JEES_NTW_CONV_NUM_FRAGMENT;
926
927
928 -- code from JEES_NTW pacakge 120.3
929 /*
930 Public function which splits the number into two parts (the decimals and the
931 number without decimals) and calls the con_num_fragment for the two parts
932 */
933 FUNCTION JEES_NTW_NUM_TO_WORDS (P_ENTERED NUMBER,
934 P_PRECISION INTEGER)
935 RETURN VARCHAR2 IS
936 LOC_NUM NUMBER(12);
937 LOC_FEM BOOLEAN;
938 LOC_WORD1 VARCHAR2(240);
939 LOC_WORD2 VARCHAR2(240);
940 LOC_ENTERED NUMBER(14,2);
941 LOC_PRECISION NUMBER(2);
942
943 BEGIN
944 LOC_ENTERED := P_ENTERED;
945 LOC_PRECISION := P_PRECISION;
946
947 IF TRUNC(LOC_ENTERED) <> LOC_ENTERED
948 THEN
949
950 /* It has decimals - extract them */
951 LOC_NUM := 100 * (LOC_ENTERED - TRUNC(LOC_ENTERED));
952
953 /* centimos are always masculine */
954 LOC_FEM := FALSE;
955
956 LOC_WORD1 := JEES_NTW_CONV_NUM_FRAGMENT(LOC_NUM, LOC_FEM);
957 /* NMR - bug 1135045 add cero if between 1 and 9 */
958 IF LOC_NUM < 10 THEN
959 /* bug 1735767 */
960 LOC_WORD1 := ' CON CERO'||LOC_WORD1;
961 ELSE
962 /* bug 1735767 */
963 LOC_WORD1 := ' CON'||LOC_WORD1;
964 END IF;
965 END IF;
966
967 /* Convert the main part of the number */
968 LOC_NUM := TRUNC(LOC_ENTERED);
969
970 /*
971 Guess whether the currency is masculine or feminine (this information isn't
972 stored in Financials). Given that the Peseta is feminine and the Euro is
973 masculine I am going to assume that if the currency's precision is 0 that it
974 is feminine, if more (ie 2) that it is masculine
975 */
976
977 IF LOC_PRECISION = 0
978 THEN
979 LOC_FEM := TRUE;
980 ELSE
981 LOC_FEM := FALSE;
982 END IF;
983
984 LOC_WORD2 := JEES_NTW_CONV_NUM_FRAGMENT (LOC_NUM, LOC_FEM);
985
986 LOC_WORD1 := LOC_WORD2||LOC_WORD1;
987
988 RETURN LOC_WORD1;
989
990 END JEES_NTW_NUM_TO_WORDS;
991
992
993 -- code from JLBRPCFP.rdf 115.14 2006/08/22 08:54
994 FUNCTION AMOUNT_WORDS_SPANISH (Chk_Amt number) RETURN varchar2 IS
995
996 l_amt_spanish1 varchar2(200) := '';
997 l_amt_spanish2 varchar2(200) := '';
998 check_amount number;
999
1000 BEGIN
1001
1002 SELECT
1003 decode(.00000000001*(mod(abs(Chk_Amt),1000000000000)-mod(abs(Chk_Amt),100000000000)),
1004 1,decode(mod(trunc(Chk_Amt/1000000000),1000)-100, 0,'Cien ','Ciento '),
1005 2,'Doscientos ',
1006 3,'Trescientos ',
1007 4,'Cuatrocientos ',
1008 5,'Quinientos ',
1009 6,'Seiscientos ',
1010 7,'Setecientos ',
1011 8,'Ochocientos ',
1012 9,'Novecientos ',
1013 0,null,
1014 'ERROR ') ||
1015 decode(.0000000001*(mod(abs(Chk_Amt),100000000000)-mod(abs(Chk_Amt),10000000000)),
1016 1,(decode(.000000001*(mod(abs(Chk_Amt),10000000000)-mod(abs(Chk_Amt),1000000000)),
1017 0,'Diez ',
1018 1,'Once ',
1019 2,'Doce ',
1020 3,'Trece ',
1021 4,'Catorce ',
1022 5,'Quince ',
1023 6,'Dieciseis ',
1024 7,'Diecisiete ',
1025 8,'Dieciocho ',
1026 9,'Diecinueve ',
1027 'ERROR ')),
1028 2,(decode(.000000001*(mod(abs(Chk_Amt),10000000000)-mod(abs(Chk_Amt),1000000000)),
1029 0,'Veinte ',
1030 1,'Veintiun ',
1031 2,'Veintidos ',
1032 3,'Veintitres ',
1033 4,'Veinticuatro ',
1034 5,'Veinticinco ',
1035 6,'Veintiseis ',
1036 7,'Veintisiete ',
1037 8,'Veintiocho ',
1038 9,'Veintinueve ',
1039 'ERROR')),
1040 3,'Treinta ',
1041 4,'Cuarenta ',
1042 5,'Cincuenta ',
1043 6,'Sesenta ',
1044 7,'Setenta ',
1045 8,'Ochenta ',
1046 9,'Noventa ',
1047 NULL) ||
1048 decode(.000000001*(mod(abs(Chk_Amt),10000000000)-mod(abs(Chk_Amt),1000000000)),
1049 0,NULL,
1050 (decode(.0000000001*(mod(abs(Chk_Amt),100000000000)-mod(abs(Chk_Amt),10000000000)),
1051 0,NULL,
1052 1,NULL,
1053 2,NULL,
1054 'y '))) ||
1055 decode(.0000000001*(mod(abs(Chk_Amt),100000000000)-mod(abs(Chk_Amt),10000000000)),
1056 1,NULL,
1057 2,NULL,
1058 (decode(.000000001*(mod(abs(Chk_Amt),10000000000)-mod(abs(Chk_Amt),1000000000)),
1059 1,'Un ',
1060 2,'Dos ',
1061 3,'Tres ',
1062 4,'Cuatro ',
1063 5,'Cinco ',
1064 6,'Seis ',
1065 7,'Siete ',
1066 8,'Ocho ',
1067 9,'Nueve ',
1068 0,NULL,
1069 'ERROR '))) ||
1070 decode(SIGN(abs(Chk_Amt)-999.99),
1071 1,decode(.00000000001*(mod(abs(Chk_Amt),1000000000000)-mod(abs(Chk_Amt),100000000000)),
1072 0,decode(.0000000001*(mod(abs(Chk_Amt),100000000000)-mod(abs(Chk_Amt),10000000000)),
1073 0,decode(.000000001*(mod(abs(Chk_Amt),10000000000)-mod(abs(Chk_Amt),1000000000)),
1074 0,NULL,
1075 'Mil '),
1076 'Mil '),
1077 'Mil '),
1078 NULL) ||
1079 decode(.00000001*(mod(abs(Chk_Amt),1000000000)-mod(abs(Chk_Amt),100000000)),
1080 1,decode(mod(trunc(Chk_Amt/1000000),1000)-100, 0,'Cien ','Ciento '),
1081 2,'Doscientos ',
1082 3,'Trescientos ',
1083 4,'Cuatrocientos ',
1084 5,'Quinientos ',
1085 6,'Seiscientos ',
1086 7,'Setecientos ',
1087 8,'Ochocientos ',
1088 9,'Novecientos ',
1089 0,null,
1090 'ERROR ') ||
1091 decode(.0000001*(mod(abs(Chk_Amt),100000000)-mod(abs(Chk_Amt),10000000)),
1092 1,(decode(.000001*(mod(abs(Chk_Amt),10000000)-mod(abs(Chk_Amt),1000000)),
1093 0,'Diez ',
1094 1,'Once ',
1095 2,'Doce ',
1096 3,'Trece ',
1097 4,'Catorce ',
1098 5,'Quince ',
1099 6,'Dieciseis ',
1100 7,'Diecisiete ',
1101 8,'Dieciocho ',
1102 9,'Diecinueve ',
1103 'ERROR ')),
1104 2,(decode(.000001*(mod(abs(Chk_Amt),10000000)-mod(abs(Chk_Amt),1000000)),
1105 0,'Veinte ',
1106 1,'Veintiun ',
1107 2,'Veintidos ',
1108 3,'Veintitres ',
1109 4,'Veinticuatro ',
1110 5,'Veinticinco ',
1111 6,'Veintiseis ',
1112 7,'Veintisiete ',
1113 8,'Veintiocho ',
1114 9,'Veintinueve ',
1115 'ERROR ')),
1116 3,'Treinta ',
1117 4,'Cuarenta ',
1118 5,'Cincuenta ',
1119 6,'Sesenta ',
1120 7,'Setenta ',
1121 8,'Ochenta ',
1122 9,'Noventa ',
1123 NULL) ||
1124 decode(.000001*(mod(abs(Chk_Amt),10000000)-mod(abs(Chk_Amt),1000000)),
1125 0,NULL,
1126 (decode(.0000001*(mod(abs(Chk_Amt),100000000)-mod(abs(Chk_Amt),10000000)),
1127 0,NULL,
1128 1,NULL,
1129 2,NULL,
1130 'y '))) ||
1131 decode(.0000001*(mod(abs(Chk_Amt),100000000)-mod(abs(Chk_Amt),10000000)),
1132 1,NULL,
1133 2,NULL,
1134 (decode(.000001*(mod(abs(Chk_Amt),10000000)-mod(abs(Chk_Amt),1000000)),
1135 1,'Un ',
1136 2,'Dos ',
1137 3,'Tres ',
1138 4,'Cuatro ',
1139 5,'Cinco ',
1140 6,'Seis ',
1141 7,'Siete ',
1142 8,'Ocho ',
1143 9,'Nueve ',
1144 0,NULL,
1145 'ERROR '))) ||
1146 decode(trunc(Chk_amt/1000000),
1147 0,NULL,
1148 1,'Millon ',
1149 'Millones ') ||
1150 decode(.00001*(mod(abs(Chk_Amt),1000000)-mod(abs(Chk_Amt),100000)),
1151 1,decode(mod(trunc(Chk_Amt/1000),1000)-100, 0,'Cien ','Ciento '),
1152 2,'Doscientos ',
1153 3,'Trescientos ',
1154 4,'Cuatrocientos ',
1155 5,'Quinientos ',
1156 6,'Seiscientos ',
1157 7,'Setecientos ',
1158 8,'Ochocientos ',
1159 9,'Novecientos ',
1160 0,null,
1161 'ERROR ') ||
1162 decode(.0001*(mod(abs(Chk_Amt),100000)-mod(abs(Chk_amt),10000)),
1163 1,(decode(.001*(mod(abs(Chk_Amt),10000)-mod(abs(Chk_Amt),1000)),
1164 0,'Diez ',
1165 1,'Once ',
1166 2,'Doce ',
1167 3,'Trece ',
1168 4,'Catorce ',
1169 5,'Quince ',
1170 6,'Dieciseis ',
1171 7,'Diecisiete ',
1172 8,'Dieciocho ',
1173 9,'Diecinueve ',
1174 'ERROR')),
1175 2,(decode(.001*(mod(abs(Chk_Amt),10000)-mod(abs(Chk_Amt),1000)),
1176 0,'Veinte ',
1177 1,'Veintiun ',
1178 2,'Veintidos ',
1179 3,'Veintitres ',
1180 4,'Veinticuatro ',
1181 5,'Veinticinco ',
1182 6,'Veintiseis ',
1183 7,'Veintisiete ',
1184 8,'Veintiocho ',
1185 9,'Veintinueve ',
1186 'ERROR ')),
1187 3,'Treinta ',
1188 4,'Cuarenta ',
1189 5,'Cincuenta ',
1190 6,'Sesenta ',
1191 7,'Setenta ',
1192 8,'Ochenta ',
1193 9,'Noventa ',
1194 NULL) ||
1195 decode(.001*(mod(abs(Chk_Amt),10000)-mod(abs(Chk_Amt),1000)),
1196 0,NULL,
1197 (decode(.0001*(mod(abs(Chk_Amt),100000)-mod(abs(Chk_Amt),10000)),
1198 0,NULL,
1199 1,NULL,
1200 2,NULL,
1201 'y '))) ||
1202 decode(.0001*(mod(abs(Chk_Amt),100000)-mod(abs(Chk_Amt),10000)),
1203 1,NULL,
1204 2,NULL,
1205 (decode(.001*(mod(abs(Chk_Amt),10000)-mod(abs(Chk_Amt),1000)),
1206 1,'Un ',
1207 2,'Dos ',
1208 3,'Tres ',
1209 4,'Cuatro ',
1210 5,'Cinco ',
1211 6,'Seis ',
1212 7,'Siete ',
1213 8,'Ocho ',
1214 9,'Nueve ',
1215 0,NULL,
1216 'ERROR '))) ||
1217 decode(SIGN(abs(Chk_Amt)-999.99),
1218 1,decode(.00001*(mod(abs(Chk_Amt),1000000)-mod(abs(Chk_Amt),100000)),
1219 0,decode (.0001*(mod(abs(Chk_Amt),100000)-mod(abs(Chk_Amt),10000)),
1220 0,decode(.001*(mod(abs(Chk_Amt),10000)-mod(abs(Chk_Amt),1000)),
1221 0,NULL,
1222 'Mil '),
1223 'Mil '),
1224 'Mil '),
1225 NULL) ||
1226 decode(.01*(mod(abs(Chk_Amt),1000)-mod(abs(Chk_Amt),100)),
1227 1,decode(mod(trunc(Chk_Amt),1000)-100, 0,'Cien ','Ciento '),
1228 2,'Doscientos ',
1229 3,'Trescientos ',
1230 4,'Cuatrocientos ',
1231 5,'Quinientos ',
1232 6,'Seiscientos ',
1233 7,'Setecientos ',
1234 8,'Ochocientos ',
1235 9,'Novecientos ',
1236 NULL) ||
1237 decode(.1*(mod(abs(Chk_Amt),100)-mod(abs(Chk_Amt),10)),
1238 1,(decode(trunc(mod(abs(Chk_Amt),10)),
1239 0,'Diez ',
1240 1,'Once ',
1241 2,'Doce ',
1242 3,'Trece ',
1243 4,'Catorce ',
1244 5,'Quince ',
1245 6,'Dieciseis ',
1246 7,'Diecisiete ',
1247 8,'Dieciocho ',
1248 9,'Diecinueve ',
1249 'ERROR ')),
1250 2,(decode(trunc(mod(abs(Chk_Amt),10)),
1251 0,'Veinte ',
1252 1,'Veintiun ',
1253 2,'Veintidos ',
1254 3,'Veintitres ',
1255 4,'Veinticuatro ',
1256 5,'Veinticinco ',
1257 6,'Veintiseis ',
1258 7,'Veintisiete ',
1259 8,'Veintiocho ',
1260 9,'Veintinueve ',
1261 'ERROR ')),
1262 2,'Veinte ',
1263 3,'Treinta ',
1264 4,'Cuarenta ',
1265 5,'Cincuenta ',
1266 6,'Sesenta ',
1267 7,'Setenta ',
1268 8,'Ochenta ',
1269 9,'Noventa ',
1270 NULL) ||
1271 decode(trunc(mod(abs(Chk_Amt),10)),
1272 0,NULL,
1273 (decode(.1*(mod(abs(Chk_Amt),100)-mod(abs(Chk_Amt),10)),
1274 0,NULL,
1275 1,NULL,
1276 2,NULL,
1277 'y '))) ||
1278 decode(.1*(mod(abs(Chk_Amt),100)-mod(abs(Chk_Amt),10)),
1279 1,NULL,
1280 2,NULL,
1281 (decode(trunc(mod(abs(Chk_Amt),10)),
1282 1,'Un ',
1283 2,'Dos ',
1284 3,'Tres ',
1285 4,'Cuatro ',
1286 5,'Cinco ',
1287 6,'Seis ',
1288 7,'Siete ',
1289 8,'Ocho ',
1290 9,'Nueve ',
1291 0,null,
1292 'ERROR '))) ||
1293 decode(trunc(abs(Chk_Amt)),
1294 0, 'Cero ',
1295 null) ||
1296 decode(100*(abs(Chk_Amt)-trunc(abs(Chk_Amt))),
1297 0,'pesos', 'pesos con ' || TO_CHAR(ABS(100*(abs(Chk_Amt)-trunc(abs(Chk_Amt))))) || ' Centavos ')
1298 INTO l_amt_spanish1
1299 FROM dual;
1300
1301 return(l_amt_spanish1);
1302
1303 END AMOUNT_WORDS_SPANISH;
1304
1305
1306 FUNCTION AMOUNT_WORDS_MEXICAN(P_ENTERED NUMBER,
1307 P_PRECISION INTEGER)
1308 RETURN VARCHAR2 IS
1309 LOC_NUM NUMBER(12);
1310 LOC_FEM BOOLEAN;
1311 LOC_WORD1 VARCHAR2(240);
1312 LOC_WORD2 VARCHAR2(240);
1313 LOC_ENTERED NUMBER(14,2);
1314 LOC_PRECISION NUMBER(2);
1315
1316 BEGIN
1317 LOC_ENTERED := P_ENTERED;
1318 LOC_PRECISION := P_PRECISION;
1319
1320 IF TRUNC(LOC_ENTERED) <> LOC_ENTERED
1321 THEN
1322
1323 /* It has decimals - extract them */
1324 LOC_NUM := 100 * (LOC_ENTERED - TRUNC(LOC_ENTERED));
1325
1326 /* centimos are always masculine */
1327 LOC_FEM := TRUE;
1328 IF LOC_NUM < 10 THEN
1329 LOC_WORD1 := ' 0'||LOC_NUM||'/100 M.N.';
1330 ELSE
1331 LOC_WORD1 := ' '||LOC_NUM||'/100 M.N.';
1332 END IF;
1333 ELSE
1334
1335 LOC_WORD1 := ' 00/100 M.N.';
1336
1337 END IF;
1338
1339 /* Convert the main part of the number */
1340 LOC_NUM := TRUNC(LOC_ENTERED);
1341
1342 /*
1343 Guess whether the currency is masculine or feminine (this information isn't
1344 stored in Financials). Given that the Peseta is feminine and the Euro is
1345 masculine I am going to assume that if the currency's precision is 0 that it
1346 is feminine, if more (ie 2) that it is masculine
1347 */
1348
1349 LOC_FEM := FALSE;
1350
1351
1352 LOC_WORD2 := JEES_NTW_CONV_NUM_FRAGMENT (LOC_NUM, LOC_FEM);
1353
1354 LOC_WORD1 := LOC_WORD2||' PESOS CON'||LOC_WORD1;
1355
1356 RETURN LOC_WORD1;
1357
1358 END AMOUNT_WORDS_MEXICAN;
1359
1360 FUNCTION amount_words_portugese(valor NUMBER) RETURN VARCHAR2 IS
1361 first_line_len constant NUMBER(2) := 55;
1362 second_line_len constant NUMBER(3) := 70;
1363 amount_total_len constant NUMBER(3) := 201;
1364 valor_extenso VARCHAR2(350) := '';
1365 valor_temp VARCHAR2(350) := '';
1366 v_first_line VARCHAR2(55);
1367 v_first_line_len NUMBER;
1368 v_i binary_integer;
1369 b1 NUMBER(1);
1370 b2 NUMBER(1);
1371 b3 NUMBER(1);
1372 b4 NUMBER(1);
1373 b5 NUMBER(1);
1374 b6 NUMBER(1);
1375 b7 NUMBER(1);
1376 b8 NUMBER(1);
1377 b9 NUMBER(1);
1378 b10 NUMBER(1);
1379 b11 NUMBER(1);
1380 b12 NUMBER(1);
1381 b13 NUMBER(1);
1382 b14 NUMBER(1);
1383 l1 VARCHAR2(12);
1384 l2 VARCHAR2(3);
1385 l3 VARCHAR2(9);
1386 l4 VARCHAR2(3);
1387 l5 VARCHAR2(6);
1388 l6 VARCHAR2(8);
1389 l7 VARCHAR2(12);
1390 l8 VARCHAR2(3);
1391 l9 VARCHAR2(9);
1392 l10 VARCHAR2(3);
1393 l11 VARCHAR2(6);
1394 l12 VARCHAR2(8);
1395 l13 VARCHAR2(12);
1396 l14 VARCHAR2(3);
1397 l15 VARCHAR2(9);
1398 l16 VARCHAR2(3);
1399 l17 VARCHAR2(6);
1400 l18 VARCHAR2(8);
1401 l19 VARCHAR2(12);
1402 l20 VARCHAR2(3);
1403 l21 VARCHAR2(9);
1404 l22 VARCHAR2(3);
1405 l23 VARCHAR2(6);
1406 l24 VARCHAR2(7);
1407 l25 VARCHAR2(3);
1408 l26 VARCHAR2(9);
1409 l27 VARCHAR2(3);
1410 l28 VARCHAR2(6);
1411 l29 VARCHAR2(10);
1412 virgula_bi VARCHAR2(4);
1413 virgula_mi VARCHAR2(4);
1414 virgula_mil VARCHAR2(4);
1415 virgula_cr VARCHAR2(4);
1416 valor1 VARCHAR2(14);
1417 --
1418 -- Table of hundreds --
1419 centenas VARCHAR2(108) := ' CENTO DUZENTOS TREZENTOS' ||
1420 'QUATROCENTOS QUINHENTOS SEISCENTOS' ||
1421 ' SETECENTOS OITOCENTOS NOVECENTOS';
1422
1423 -- Table of Dozens --
1424 dezenas VARCHAR2(81) := ' DEZ VINTE TRINTA QUARENTA' ||
1425 'CINQUENTA SESSENTA SETENTA OITENTA' ||
1426 'NOVENTA';
1427
1428 -- Table of Units --
1429 unidades VARCHAR2(54) := ' UM DOIS TRESQUATRO CINCO SEIS' ||
1430 ' SETE OITO NOVE';
1431
1432 -- Table of units of Dozens 10 --
1433 unid10 VARCHAR2(81) := ' ONZE DOZE TREZE QUATORZE' ||
1434 ' QUINZEDEZESSEISDEZESSETE DEZOITO' ||
1435 ' DEZENOVE';
1436
1437
1438 BEGIN
1439
1440 valor1 := lpad(to_char(valor * 100), 14, '0');
1441 b1 := SUBSTR(valor1, 1, 1);
1442 b2 := SUBSTR(valor1, 2, 1);
1443 b3 := SUBSTR(valor1, 3, 1);
1444 b4 := SUBSTR(valor1, 4, 1);
1445 b5 := SUBSTR(valor1, 5, 1);
1446 b6 := SUBSTR(valor1, 6, 1);
1447 b7 := SUBSTR(valor1, 7, 1);
1448 b8 := SUBSTR(valor1, 8, 1);
1449 b9 := SUBSTR(valor1, 9, 1);
1450 b10 := SUBSTR(valor1, 10, 1);
1451 b11 := SUBSTR(valor1, 11, 1);
1452 b12 := SUBSTR(valor1, 12, 1);
1453 b13 := SUBSTR(valor1, 13, 1);
1454 b14 := SUBSTR(valor1, 14, 1);
1455
1456 IF valor <> 0 THEN
1457
1458 IF b1 <> 0 THEN
1459
1460 IF b1 = 1 THEN
1461
1462 IF b2 = 0
1463 AND b3 = 0 THEN
1464 l5 := 'CEM';
1465 ELSE
1466 l1 := SUBSTR(centenas, b1 * 12 -11, 12);
1467 END IF;
1468
1469 ELSE
1470 l1 := SUBSTR(centenas, b1 * 12 -11, 12);
1471 END IF;
1472
1473 END IF;
1474
1475 IF b2 <> 0 THEN
1476
1477 IF b2 = 1 THEN
1478
1479 IF b3 = 0 THEN
1480 l5 := 'DEZ';
1481 ELSE
1482 l3 := SUBSTR(unid10, b3 * 9 -8, 9);
1483 END IF;
1484
1485 ELSE
1486 l3 := SUBSTR(dezenas, b2 * 9 -8, 9);
1487 END IF;
1488
1489 END IF;
1490
1491 IF b3 <> 0 THEN
1492
1493 IF b2 <> 1 THEN
1494 l5 := SUBSTR(unidades, b3 * 6 -5, 6);
1495 END IF;
1496
1497 END IF;
1498
1499 IF b1 <> 0 OR b2 <> 0 OR b3 <> 0 THEN
1500
1501 IF(b1 = 0
1502 AND b2 = 0)
1503 AND b3 = 1 THEN
1504 l5 := 'HUM';
1505 l6 := ' BILHAO';
1506 ELSE
1507 l6 := ' BILHOES';
1508 END IF;
1509
1510 IF valor > 999999999 THEN
1511 -- if trunc(valor,0) = 1000000000 then
1512
1513 IF SUBSTR(valor1, 4, 9) = '000000000' THEN
1514 virgula_bi := ' DE ';
1515 ELSE
1516 virgula_bi := ' E ';
1517 END IF;
1518
1519 ELSE
1520 virgula_bi := ' ';
1521 END IF;
1522
1523 l1 := LTRIM(l1);
1524 l3 := LTRIM(l3);
1525 l5 := LTRIM(l5);
1526
1527 IF b2 > 1
1528 AND b3 > 0 THEN
1529 l4 := ' E ';
1530 END IF;
1531
1532 IF b1 <> 0
1533 AND(b2 <> 0 OR b3 <> 0) THEN
1534 l2 := ' E ';
1535 END IF;
1536
1537 END IF;
1538
1539 -- ROTINA DOS MILHOES ------------
1540 amount_words_portgse_milhares(valor, b4, b5, b6, b7, b8, b9, l7, l8, l9, l10, l11, l12, virgula_mi);
1541 --
1542 -- ROTINA DAS CENTENAS --
1543 amount_words_portgse_centos(valor, b7, b8, b9, b10, b11, b12, l13, l14, l15, l16, l17, l18, virgula_mil);
1544 --
1545 -- ROTINA DAS DEZENAS --
1546 amount_words_portgse_dezena(valor, b10, b11, b12, l19, l20, l21, l22, l23, l24, virgula_cr);
1547 --
1548 -- TRATA CENTAVOS --
1549 amount_words_portgse_centavos(valor, b13, b14, l25, l26, l27, l28, l29);
1550 --
1551 -- CONCATENAR O LITERAL --
1552 valor_temp := l1 || l2 || l3 || l4 || l5 || l6 || virgula_bi || l7 || l8 || l9 || l10 || l11 || l12 || virgula_mi || l13 || l14 || l15 || l16 || l17 || l18 || virgula_mil || l19 || l20 ;
1553 valor_extenso := valor_temp || l21 || l22 || l23 || l24 || virgula_cr || l25 || l26 || l27 || l28 || l29;
1554
1555 --Commented out by Usha on 14-MAy-99 to test the layout problem
1556
1557 /* FOR v_i IN REVERSE 1..83 LOOP
1558 FOR v_i IN REVERSE 1 .. 56
1559 LOOP
1560
1561 IF SUBSTR(valor_extenso, v_i, 1) = ' ' THEN
1562 v_first_line_len := v_i -1;
1563 EXIT;
1564 END IF;
1565
1566 END LOOP;
1567 valor_extenso := rpad(SUBSTR(valor_extenso, 1, v_first_line_len), first_line_len, ' ') || rpad(' ', second_line_len + 1, ' ') || SUBSTR(valor_extenso, v_first_line_len + 1, 120);
1568 */
1569
1570 ELSE
1571 --if not setup and check_amount is 0, print 'Z E R O'
1572 valor_extenso := 'ZERO REAIS';
1573 END IF;
1574
1575 --valor_extenso := rpad(' ', 10, ' ') || valor_extenso;
1576 --valor_extenso := SUBSTR(valor_extenso, 1, amount_total_len);
1577
1578 RETURN(valor_extenso);
1579 END;
1580
1581 PROCEDURE amount_words_portgse_milhares(valor NUMBER, b4 NUMBER, b5 NUMBER, b6 NUMBER, b7 NUMBER, b8 NUMBER, b9 NUMBER, l7 OUT NOCOPY VARCHAR2, l8 OUT NOCOPY VARCHAR2, l9 OUT NOCOPY VARCHAR2, l10 OUT NOCOPY VARCHAR2,
1582 l11 OUT NOCOPY VARCHAR2, l12 OUT NOCOPY VARCHAR2, virgula_mi OUT NOCOPY VARCHAR2) IS
1583 --
1584 -- TABELA DE CENTENAS --
1585 centenas VARCHAR2(108) := ' CENTO DUZENTOS TREZENTOS' || 'QUATROCENTOS QUINHENTOS SEISCENTOS' || ' SETECENTOS OITOCENTOS NOVECENTOS';
1586 -- TABELA DE DEZENAS --
1587 dezenas VARCHAR2(81) := ' DEZ VINTE TRINTA QUARENTA' || 'CINQUENTA SESSENTA SETENTA OITENTA' || 'NOVENTA';
1588 -- TABELA DE UNIDADES --
1589 unidades VARCHAR2(54) := ' UM DOIS TRESQUATRO CINCO SEIS' || ' SETE OITO NOVE';
1590 -- TABELA DE UNIDADES DA DEZENA 10 --
1591 unid10 VARCHAR2(81) := ' ONZE DOZE TREZE QUATORZE' || ' QUINZEDEZESSEISDEZESSETE DEZOITO' || ' DEZENOVE';
1592 --
1593 valor1 VARCHAR2(14);
1594 BEGIN
1595 valor1 := lpad(to_char(valor * 100), 14, '0');
1596
1597 IF b4 <> 0 THEN
1598
1599 IF b4 = 1 THEN
1600
1601 IF b5 = 0
1602 AND b6 = 0 THEN
1603 l7 := 'CEM';
1604 ELSE
1605 l7 := SUBSTR(centenas, b4 * 12 -11, 12);
1606 END IF;
1607
1608 ELSE
1609 l7 := SUBSTR(centenas, b4 * 12 -11, 12);
1610 END IF;
1611
1612 END IF;
1613
1614 IF b5 <> 0 THEN
1615
1616 IF b5 = 1 THEN
1617
1618 IF b6 = 0 THEN
1619 l11 := 'DEZ';
1620 ELSE
1621 l9 := SUBSTR(unid10, b6 * 9 -8, 9);
1622 END IF;
1623
1624 ELSE
1625 l9 := SUBSTR(dezenas, b5 * 9 -8, 9);
1626 END IF;
1627
1628 END IF;
1629
1630 IF b6 <> 0 THEN
1631
1632 IF b5 <> 1 THEN
1633 l11 := SUBSTR(unidades, b6 * 6 -5, 6);
1634 END IF;
1635
1636 END IF;
1637
1638 IF b4 <> 0 OR b5 <> 0 OR b6 <> 0 THEN
1639
1640 IF(b4 = 0
1641 AND b5 = 0)
1642 AND b6 = 1 THEN
1643 l11 := 'HUM';
1644 l12 := ' MILHAO';
1645 ELSE
1646 l12 := ' MILHOES';
1647 END IF;
1648
1649 IF valor > 999999 THEN
1650 -- if trunc(valor,0) = 1000000 then
1651
1652 IF SUBSTR(valor1, 7, 6) = '000000' THEN
1653 virgula_mi := ' DE ';
1654 ELSE
1655 virgula_mi := ' E ';
1656 END IF;
1657
1658 ELSE
1659 virgula_mi := ' ';
1660 END IF;
1661
1662 l7 := LTRIM(l7);
1663 l9 := LTRIM(l9);
1664 l11 := LTRIM(l11);
1665
1666 IF b5 > 1
1667 AND b6 > 0 THEN
1668 l10 := ' E ';
1669 END IF;
1670
1671 IF b4 <> 0
1672 AND(b5 <> 0 OR b6 <> 0) THEN
1673 l8 := ' E ';
1674 END IF;
1675
1676 END IF;
1677
1678 END;
1679
1680 PROCEDURE amount_words_portgse_centos(valor NUMBER, b7 NUMBER, b8 NUMBER, b9 NUMBER, b10 NUMBER, b11 NUMBER, b12 NUMBER, l13 OUT NOCOPY VARCHAR2, l14 OUT NOCOPY VARCHAR2, l15 OUT NOCOPY VARCHAR2, l16 OUT NOCOPY VARCHAR2,
1681 l17 OUT NOCOPY VARCHAR2, l18 OUT NOCOPY VARCHAR2, virgula_mil OUT NOCOPY VARCHAR2) IS
1682 --
1683 -- TABELA DE CENTENAS --
1684 centenas VARCHAR2(108) := ' CENTO DUZENTOS TREZENTOS' || 'QUATROCENTOS QUINHENTOS SEISCENTOS' || ' SETECENTOS OITOCENTOS NOVECENTOS';
1685 -- TABELA DE DEZENAS --
1686 dezenas VARCHAR2(81) := ' DEZ VINTE TRINTA QUARENTA' || 'CINQUENTA SESSENTA SETENTA OITENTA' || 'NOVENTA';
1687 -- TABELA DE UNIDADES --
1688 unidades VARCHAR2(54) := ' UM DOIS TRESQUATRO CINCO SEIS' || ' SETE OITO NOVE';
1689 -- TABELA DE UNIDADES DA DEZENA 10 --
1690 unid10 VARCHAR2(81) := ' ONZE DOZE TREZE QUATORZE' || ' QUINZEDEZESSEISDEZESSETE DEZOITO' || ' DEZENOVE';
1691 --
1692
1693 BEGIN
1694
1695 IF b7 <> 0 THEN
1696
1697 IF b7 = 1 THEN
1698
1699 IF b8 = 0
1700 AND b9 = 0 THEN
1701 l17 := 'CEM';
1702 ELSE
1703 l13 := SUBSTR(centenas, b7 * 12 -11, 12);
1704 END IF;
1705
1706 ELSE
1707 l13 := SUBSTR(centenas, b7 * 12 -11, 12);
1708 END IF;
1709
1710 END IF;
1711
1712 IF b8 <> 0 THEN
1713
1714 IF b8 = 1 THEN
1715
1716 IF b9 = 0 THEN
1717 l17 := 'DEZ';
1718 ELSE
1719 l15 := SUBSTR(unid10, b9 * 9 -8, 9);
1720 END IF;
1721
1722 ELSE
1723 l15 := SUBSTR(dezenas, b8 * 9 -8, 9);
1724 END IF;
1725
1726 END IF;
1727
1728 IF b9 <> 0 THEN
1729
1730 IF b8 <> 1 THEN
1731 l17 := SUBSTR(unidades, b9 * 6 -5, 6);
1732 END IF;
1733
1734 END IF;
1735
1736 IF b7 <> 0 OR b8 <> 0 OR b9 <> 0 THEN
1737
1738 IF(b7 = 0
1739 AND b8 = 0)
1740 AND b9 = 1 THEN
1741 l17 := 'HUM';
1742 l18 := ' MIL';
1743 ELSE
1744 l18 := ' MIL';
1745 END IF;
1746
1747 IF valor > 999 THEN
1748 -- if trunc(valor,0) = 1000 then
1749
1750 IF b10 = 0
1751 AND b11 = 0
1752 AND b12 = 0 THEN
1753 -- virgula_mil := ' DE ';
1754 -- Fix for Bug 854014
1755 virgula_mil := ' ';
1756 ELSE
1757 virgula_mil := ' E ';
1758 END IF;
1759
1760 -- else
1761 -- if b10 = 0 and b11 = 0 and b12 = 0 then
1762 -- virgula_mil := ' ';
1763 -- else
1764 -- virgula_mil := ' E ';
1765 -- end if;
1766 -- end if;
1767 ELSE
1768 virgula_mil := ' ';
1769 END IF;
1770
1771 l13 := LTRIM(l13);
1772 l15 := LTRIM(l15);
1773 l17 := LTRIM(l17);
1774
1775 IF b8 > 1
1776 AND b9 > 0 THEN
1777 l16 := ' E ';
1778 END IF;
1779
1780 IF b7 <> 0
1781 AND(b8 <> 0 OR b9 <> 0) THEN
1782 l14 := ' E ';
1783 END IF;
1784
1785 END IF;
1786
1787 END;
1788
1789 PROCEDURE amount_words_portgse_dezena(valor IN NUMBER, b10 IN NUMBER, b11 IN NUMBER, b12 IN NUMBER, l19 OUT NOCOPY VARCHAR2, l20 OUT NOCOPY VARCHAR2, l21 OUT NOCOPY VARCHAR2, l22 OUT NOCOPY VARCHAR2,
1790 l23 OUT NOCOPY VARCHAR2, l24 OUT NOCOPY VARCHAR2, virgula_cr OUT NOCOPY VARCHAR2) IS
1791 --
1792 -- TABELA DE CENTENAS --
1793 centenas VARCHAR2(108) := ' CENTO DUZENTOS TREZENTOS' || 'QUATROCENTOS QUINHENTOS SEISCENTOS' || ' SETECENTOS OITOCENTOS NOVECENTOS';
1794 -- TABELA DE DEZENAS --
1795 dezenas VARCHAR2(81) := ' DEZ VINTE TRINTA QUARENTA' || 'CINQUENTA SESSENTA SETENTA OITENTA' || 'NOVENTA';
1796 -- TABELA DE UNIDADES --
1797 unidades VARCHAR2(54) := ' UM DOIS TRESQUATRO CINCO SEIS' || ' SETE OITO NOVE';
1798 -- TABELA DE UNIDADES DA DEZENA 10 --
1799 unid10 VARCHAR2(81) := ' ONZE DOZE TREZE QUATORZE' || ' QUINZEDEZESSEISDEZESSETE DEZOITO' || ' DEZENOVE';
1800 --
1801 BEGIN
1802
1803 IF b10 <> 0 THEN
1804
1805 IF b10 = 1 THEN
1806
1807 IF b11 = 0
1808 AND b12 = 0 THEN
1809 l19 := 'CEM';
1810 ELSE
1811 l19 := SUBSTR(centenas, b10 * 12 -11, 12);
1812 END IF;
1813
1814 ELSE
1815 l19 := SUBSTR(centenas, b10 * 12 -11, 12);
1816 END IF;
1817
1818 END IF;
1819
1820 IF b11 <> 0 THEN
1821
1822 IF b11 = 1 THEN
1823
1824 IF b12 = 0 THEN
1825 l23 := 'DEZ';
1826 ELSE
1827 l21 := SUBSTR(unid10, b12 * 9 -8, 9);
1828 END IF;
1829
1830 ELSE
1831 l21 := SUBSTR(dezenas, b11 * 9 -8, 9);
1832 END IF;
1833
1834 END IF;
1835
1836 IF b12 <> 0 THEN
1837
1838 IF b11 <> 1 THEN
1839 l23 := SUBSTR(unidades, b12 * 6 -5, 6);
1840 END IF;
1841
1842 END IF;
1843
1844 IF b10 <> 0 OR b11 <> 0 OR b12 <> 0 THEN
1845 -- if valor > 99 and valor < 200 then
1846 -- l23 := 'TRES';
1847 -- end if;
1848 l19 := LTRIM(l19);
1849 l21 := LTRIM(l21);
1850 l23 := LTRIM(l23);
1851
1852 IF b11 > 1
1853 AND b12 > 0 THEN
1854 l22 := ' E ';
1855 END IF;
1856
1857 IF b10 <> 0
1858 AND(b11 <> 0 OR b12 <> 0) THEN
1859 l20 := ' E ';
1860 END IF;
1861
1862 ELSE
1863 virgula_cr := ' ';
1864 END IF;
1865
1866 IF valor < 1 THEN
1867 l24 := ' ';
1868 ELSIF valor < 2 THEN
1869 l24 := ' REAL ';
1870 ELSE
1871 l24 := ' REAIS ';
1872 --else
1873 -- if valor > 199 then
1874 -- l24 := ' REAIS';
1875 -- end if;
1876 END IF;
1877
1878 END;
1879
1880 PROCEDURE amount_words_portgse_centavos(valor IN NUMBER, b13 IN NUMBER, b14 IN NUMBER, l25 OUT NOCOPY VARCHAR2, l26 OUT NOCOPY VARCHAR2, l27 OUT NOCOPY VARCHAR2, l28 OUT NOCOPY VARCHAR2, l29 OUT NOCOPY VARCHAR2) IS
1881 --
1882 -- Table of Dozens
1883 dezenas VARCHAR2(81) := ' DEZ VINTE TRINTA QUARENTA' || 'CINQUENTA SESSENTA SETENTA OITENTA' || 'NOVENTA';
1884 -- Table of Units --
1885 unidades VARCHAR2(54) := ' UM DOIS TRESQUATRO CINCO SEIS' || ' SETE OITO NOVE';
1886 -- Table of Units of Dozens 10 --
1887 unid10 VARCHAR2(81) := ' ONZE DOZE TREZE QUATORZE' || ' QUINZEDEZESSEISDEZESSETE DEZOITO' || ' DEZENOVE';
1888 --
1889 BEGIN
1890
1891 IF b13 <> 0 OR b14 <> 0 THEN
1892
1893 IF valor > 99 THEN
1894 l25 := ' E ';
1895 END IF;
1896
1897 IF b13 <> 0 THEN
1898
1899 IF b13 = 1 THEN
1900
1901 IF b14 = 0 THEN
1902 l28 := 'DEZ';
1903 ELSE
1904 l26 := SUBSTR(unid10, b14 * 9 -8, 9);
1905 END IF;
1906
1907 ELSE
1908 l26 := SUBSTR(dezenas, b13 * 9 -8, 9);
1909 END IF;
1910
1911 END IF;
1912
1913 IF b14 <> 0 THEN
1914
1915 IF b13 <> 1 THEN
1916 l28 := SUBSTR(unidades, b14 * 6 -5, 6);
1917 END IF;
1918
1919 END IF;
1920
1921 IF b13 <> 0 OR b14 <> 0 THEN
1922
1923 IF valor = 1 THEN
1924 l28 := 'HUM';
1925 END IF;
1926
1927 l26 := LTRIM(l26);
1928 l28 := LTRIM(l28);
1929
1930 IF b13 > 1
1931 AND b14 > 0 THEN
1932 l27 := ' E ';
1933 END IF;
1934
1935 END IF;
1936
1937 IF b13 = 0
1938 AND b14 = 1 THEN
1939
1940 /* Bug 1074379 included a space towards the end of the string CENTAVO */ l29 := ' CENTAVO ';
1941 ELSE
1942
1943 /* Bug 1074379 included a space towards the end of the string CENTAVOS */ l29 := ' CENTAVOS ';
1944 END IF;
1945
1946 END IF;
1947
1948 END;
1949
1950
1951 /*Israel - Hebrew: Start of Components.*/
1952 FUNCTION tran_1000(
1953 P_Num NUMBER,
1954 P_Pos NUMBER)
1955 RETURN VARCHAR2
1956 IS
1957 V_Return_Heb VARCHAR2(10000);
1958 V_Num NUMBER(15) :=P_Num;
1959 V_Temp_Num NUMBER(15);
1960 V_Temp_Heb VARCHAR2(1000);
1961 BEGIN
1962 V_Return_Heb :=get_value_set_desc(VSET_NUM_TO_HEB,TO_CHAR(V_Num));
1963 IF V_Return_Heb IS NULL THEN
1964 V_Num := V_Num / P_Pos;
1965 V_Return_Heb :=get_value_set_desc(VSET_NUM_TO_HEB,TO_CHAR(V_Num));
1966 IF V_Return_Heb IS NULL THEN
1967 IF V_Num >= 100 THEN
1968 V_Temp_Num := TRUNC(V_Num/100)*100;
1969 V_Num := V_Num - V_Temp_Num;
1970 V_Return_Heb := get_value_set_desc(VSET_NUM_TO_HEB,TO_CHAR(V_Temp_Num));
1971 IF V_Return_Heb IS NULL THEN
1972 V_Return_Heb := get_value_set_desc(VSET_NUM_TO_HEB,TO_CHAR(V_Temp_Num/100)) ||' '||get_value_set_desc(VSET_NUM_TO_HEB,'000');
1973 END IF;
1974 END IF;
1975 V_Temp_Heb := get_value_set_desc(VSET_NUM_TO_HEB,TO_CHAR(V_Num));
1976 IF V_Temp_Heb IS NULL THEN
1977 V_Temp_Num := TRUNC(V_Num/10)*10;
1978 V_Num := V_Num - V_Temp_Num;
1979 IF V_Temp_Num > 9 THEN
1980 V_Return_Heb := V_Return_Heb ||' '|| get_value_set_desc(VSET_NUM_TO_HEB,TO_CHAR(V_Temp_Num));
1981 END IF;
1982 IF V_Num > 0 THEN
1983 V_Return_Heb := V_Return_Heb||' &'|| get_value_set_desc(VSET_NUM_TO_HEB,TO_CHAR(V_Num));
1984 END IF;
1985 ELSE
1986 IF V_Return_Heb IS NULL THEN
1987 V_Return_Heb := V_Return_Heb ||' '|| V_Temp_Heb;
1988 ELSE
1989 V_Return_Heb := V_Return_Heb ||' &'|| V_Temp_Heb;
1990 END IF;
1991 END IF;
1992 END IF;
1993 IF P_Pos = 1000 THEN
1994 V_Return_Heb := V_Return_Heb ||' '|| get_value_set_desc(VSET_NUM_TO_HEB,'0000');
1995 elsif P_Pos = 1000000 THEN
1996 V_Return_Heb := V_Return_Heb ||' '|| get_value_set_desc(VSET_NUM_TO_HEB,'0000000');
1997 END IF;
1998 END IF;
1999 RETURN V_Return_Heb;
2000 END tran_1000;
2001
2002 FUNCTION trans_num2heb(
2003 P_Num IN NUMBER)
2004 RETURN VARCHAR2
2005 IS
2006 V_Heb_String VARCHAR2(1000);
2007 V_Num NUMBER(15) :=P_Num;
2008 V_Last_Ve INTEGER;
2009 BEGIN
2010 IF V_Num > 1000000 - 1 THEN
2011 V_Heb_String := tran_1000(TRUNC(V_Num/1000000)*1000000,1000000);
2012 V_num := V_num - TRUNC(V_Num/1000000)*1000000;
2013 END IF;
2014 IF V_Num > 1000 - 1 THEN
2015 V_HEB_String :=V_HEB_String||' '||tran_1000(TRUNC(V_Num/1000)*1000,1000);
2016 V_num := V_num - TRUNC(V_Num/1000)*1000;
2017 END IF;
2018 IF V_Num > 0 THEN
2019 IF P_Num > 1000 - 1 AND instr(tran_1000(V_Num,1),'&') = 0 THEN
2020 V_HEB_String :=V_HEB_String||' &'||tran_1000(V_Num,1);
2021 ELSE
2022 V_HEB_String :=V_HEB_String||' '||tran_1000(V_Num,1);
2023 END IF;
2024 END IF;
2025 RETURN REPLACE(V_HEB_String,'&',get_value_set_desc(VSET_NUM_TO_HEB,VSET_AND_SEKEL));
2026 END trans_num2heb;
2027
2028 FUNCTION get_value_set_desc(
2029 P_Vset_Name IN VARCHAR2,
2030 P_Vset_Value IN VARCHAR2)
2031 RETURN VARCHAR2
2032 IS
2033 V_Description FND_FLEX_VALUES_TL.DESCRIPTION%type;
2034 BEGIN
2035 SELECT t.DESCRIPTION
2036 INTO V_Description
2037 FROM FND_FLEX_VALUES v,
2038 FND_FLEX_VALUE_SETS s,
2039 FND_FLEX_VALUES_TL t
2040 WHERE v.FLEX_VALUE_SET_ID = s.FLEX_VALUE_SET_ID
2041 AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
2042 AND v.FLEX_VALUE = P_Vset_Value
2043 AND s.FLEX_VALUE_SET_NAME = P_Vset_Name
2044 AND rownum = 1 ;
2045 /*
2046 select v.DESCRIPTION
2047 into V_Description
2048 from FND_FLEX_VALUES v
2049 ,FND_FLEX_VALUE_SETS s
2050 where v.FLEX_VALUE_SET_ID = s.FLEX_VALUE_SET_ID
2051 and v.FLEX_VALUE = P_Vset_Value
2052 and s.FLEX_VALUE_SET_NAME = P_Vset_Name
2053 and rownum = 1;
2054 */
2055 RETURN V_Description;
2056 EXCEPTION
2057 WHEN OTHERS THEN
2058 RETURN NULL;
2059 END get_value_set_desc;
2060
2061 FUNCTION trans_money(
2062 P_Money IN NUMBER)
2063 RETURN VARCHAR2
2064 IS
2065 V_Money_Heb_String VARCHAR2(10000);
2066 BEGIN
2067 IF TRUNC(P_Money) > 0 THEN
2068 /*trans_num2heb(trunc(P_Money))*/
2069 --V_Money_Heb_String:=trans_num2heb(trunc(P_Money))||chr(13)||get_value_set_desc(VSET_NUM_TO_HEB,VSET_END_SEKEL);
2070 V_Money_Heb_String:=trans_num2heb(TRUNC(P_Money))||' '||get_value_set_desc(VSET_NUM_TO_HEB,VSET_END_SEKEL);
2071 END IF;
2072 IF P_Money -TRUNC(P_Money) > 0 THEN
2073 IF V_Money_Heb_String IS NOT NULL THEN
2074 --V_Money_Heb_String := V_Money_Heb_String
2075 -- ||chr(13)||get_value_set_desc(VSET_NUM_TO_HEB,VSET_SEKEL_AGOROT)||chr(13);
2076 V_Money_Heb_String := V_Money_Heb_String ||' '||get_value_set_desc(VSET_NUM_TO_HEB,VSET_SEKEL_AGOROT)||' ';
2077 END IF;
2078 --V_Money_Heb_String := V_Money_Heb_String
2079 --||chr(13)||to_char(trunc((P_Money -trunc(P_Money))*100));
2080 V_Money_Heb_String := V_Money_Heb_String ||' '||TO_CHAR(TRUNC((P_Money -TRUNC(P_Money))*100))||' '||get_value_set_desc(VSET_NUM_TO_HEB,VSET_END_AGOROT);
2081 END IF;
2082 RETURN --V_Money_Heb_String ||chr(13)||get_value_set_desc(VSET_NUM_TO_HEB,VSET_ONLY);
2083 V_Money_Heb_String ||' '||get_value_set_desc(VSET_NUM_TO_HEB,VSET_ONLY);
2084 END trans_money;
2085 /*Israel - Hebrew: End of Components.*/
2086
2087
2088
2089
2090 -- end of package
2091 END IBY_AMOUNT_IN_WORDS;
2092