[Home] [Help]
PACKAGE BODY: APPS.AP_AMOUNT_UTILITIES_PKG
Source
1 PACKAGE BODY AP_AMOUNT_UTILITIES_PKG AS
2 /* $Header: apamtutb.pls 120.3 2004/10/27 01:26:08 pjena noship $ */
3
4 function ap_convert_number (in_numeral IN NUMBER) return varchar2 is
5 c_zero ap_lookup_codes.displayed_field%TYPE;
6 c_thousand ap_lookup_codes.displayed_field%TYPE;
7 c_million ap_lookup_codes.displayed_field%TYPE;
8 c_billion ap_lookup_codes.displayed_field%TYPE;
9 number_too_large exception;
10 numeral integer := abs(in_numeral);
11 max_digit integer := 12; -- for numbers less than a trillion
12 number_text varchar2(240) := '';
13 billion_seg varchar2(25);
14 million_seg varchar2(25);
15 thousand_seg varchar2(25);
16 units_seg varchar2(25);
17 billion_lookup varchar2(80);
18 million_lookup varchar2(80);
19 thousand_lookup varchar2(80);
20 units_lookup varchar2(80);
21 session_language fnd_languages.nls_language%TYPE;
22 thousand number := power(10,3);
23 million number := power(10,6);
24 billion number := power(10,9);
25
26 begin
27 if numeral >= power(10,max_digit) then
28 raise number_too_large;
29 end if;
30
31 --For Bug459665
32 if numeral = 0 then
33 select ' '||displayed_field||' '
34 into
35 c_zero
36 from ap_lookup_codes
37 where lookup_code = 'ZERO';
38 return(c_zero);
39 end if;
40
41 billion_seg := to_char(trunc(numeral/billion));
42 numeral := numeral - (trunc(numeral/billion) * billion);
43 million_seg := to_char(trunc(numeral/million));
44 numeral := numeral - (trunc(numeral/million) * million);
45 thousand_seg := to_char(trunc(numeral/thousand));
46 units_seg := to_char(mod(numeral,thousand));
47
48 select ' '||lc1.displayed_field||' ',
49 ' '||lc2.displayed_field||' ',
50 ' '||lc3.displayed_field||' ',
51 ' '||lc4.displayed_field,
52 lc5.description,
53 lc6.description,
54 lc7.description,
55 lc8.description
56 into c_billion,
57 c_million,
58 c_thousand,
59 c_zero,
60 billion_lookup,
61 million_lookup,
62 thousand_lookup,
63 units_lookup
64 from ap_lookup_codes lc1,
65 ap_lookup_codes lc2,
66 ap_lookup_codes lc3,
67 ap_lookup_codes lc4,
68 ap_lookup_codes lc5,
69 ap_lookup_codes lc6,
70 ap_lookup_codes lc7,
71 ap_lookup_codes lc8
72 where lc1.lookup_code = 'BILLION'
73 and lc1.lookup_type = 'NLS TRANSLATION'
74 and lc2.lookup_code = 'MILLION'
75 and lc2.lookup_type = 'NLS TRANSLATION'
76 and lc3.lookup_code = 'THOUSAND'
77 and lc3.lookup_type = 'NLS TRANSLATION'
78 and lc4.lookup_code = 'ZERO'
79 and lc4.lookup_type = 'NLS TRANSLATION'
80 and lc5.lookup_code = billion_seg
81 and lc5.lookup_type = 'NUMBERS'
82 and lc6.lookup_code = million_seg
83 and lc6.lookup_type = 'NUMBERS'
84 and lc7.lookup_code = thousand_seg
85 and lc7.lookup_type = 'NUMBERS'
86 and lc8.lookup_code = units_seg
87 and lc8.lookup_type = 'NUMBERS';
88
89 --Commented For Bug459665
90 /*
91 if numeral = 0 then
92 return(c_zero);
93 end if;
94 */
95 select substr(userenv('LANGUAGE'),1,instr(userenv('LANGUAGE'),'_')-1)
96 into session_language
97 from dual;
98
99 --Bug 335063 fix.
100
101 if (session_language = 'FRENCH' or session_language = 'CANADIAN FRENCH')
102 and thousand_seg = '1' then
103 thousand_lookup := null;
104 end if;
105 --
106
107 if billion_seg <> '0' then
108 number_text := number_text||billion_lookup ||c_billion;
109 end if;
110
111 if million_seg <> '0' then
112 number_text := number_text||million_lookup||c_million;
113 end if;
114
115 if thousand_seg <> '0' then
116 number_text := number_text||thousand_lookup||c_thousand;
117 end if;
118
119 if units_seg <> '0' then
120 number_text := number_text||units_lookup;
121 end if;
122
123 number_text := ltrim(number_text);
124 number_text := upper(substr(number_text,1,1)) ||
125 rtrim(lower(substr(number_text,2,length(number_text))));
126
127 return(number_text);
128
129 exception
130 when number_too_large then
131 return(null);
132 when others then
133 return(null);
134 end;
135
136 END AP_AMOUNT_UTILITIES_PKG;