[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_AMT_IN_WORDS
Source
1 package body pay_ca_amt_in_words as
2 /* $Header: pycaamtw.pkb 115.4 2003/06/03 08:39:24 sfmorris noship $*/
3 /*
4
5 ******************************************************************
6 * *
7 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
8 * Chertsey, England. *
9 * *
10 * All rights reserved. *
11 * *
12 * This material has been provided pursuant to an agreement *
13 * containing restrictions on its use. The material is also *
14 * protected by copyright law. No part of this material may *
15 * be copied or distributed, transmitted or transcribed, in *
16 * any form or by any means, electronic, mechanical, magnetic, *
17 * manual, or otherwise, or disclosed to third parties without *
18 * the express written permission of Oracle Corporation UK Ltd, *
19 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
20 * England. *
21 * *
22 ******************************************************************
23
24 Name : pay_ca_amt_in_words
25
26 Description : Package for converting amount in words using lookup type
27 for translating in Canadian French.
28
29 Change List
30 -----------
31 Date Name Vers Bug No Description
32 ----------- ---------- ----- ------- -----------------------------------
33 04-Jan-2002 vpandya 115.0 Created
34 04-Jan-2002 vpandya 115.1 One '$' sign added in indent line.
35 05-Jan-2002 vpandya 115.2 Using AP's lookup type 'NUMBERS'
36 for lookup values from '0' to '999'
37 27-MAR-2002 vpandya 115.3 Commented out the trace_on(#2285625)
38 03-JUN-2003 sfmorris 115.4 2888822 Added parameters p_denomination and
39 p_sub_denomination to support
40 output in Euros for French
41 Localisation
42 --
43 */
44
45 function pay_amount_in_words (in_numeral IN NUMBER,
46 p_language IN VARCHAR2,
47 p_denomination IN VARCHAR2,
48 p_sub_denomination IN VARCHAR2) return varchar2 is
49
50 c_zero ap_lookup_codes.displayed_field%TYPE;
51 c_thousand ap_lookup_codes.displayed_field%TYPE;
52 c_million ap_lookup_codes.displayed_field%TYPE;
53 c_billion ap_lookup_codes.displayed_field%TYPE;
54 number_too_large exception;
55 abs_num varchar2(25) := to_char(in_numeral,'999999999990.00');
56 numeral integer := abs(in_numeral);
57 max_digit integer := 12; -- for numbers less than a trillion
58 number_text varchar2(240) := '';
59 billion_seg varchar2(25);
60 million_seg varchar2(25);
61 thousand_seg varchar2(25);
62 units_seg varchar2(25);
63 cents_seg varchar2(25);
64 cents varchar2(25);
65 billion_lookup varchar2(80);
66 million_lookup varchar2(80);
67 thousand_lookup varchar2(80);
68 units_lookup varchar2(80);
69 and_lookup varchar2(25);
70 dollars_lookup varchar2(25);
71 cents_lookup varchar2(25);
72 session_language fnd_languages.nls_language%TYPE;
73 thousand number := power(10,3);
74 million number := power(10,6);
75 billion number := power(10,9);
76
77 begin
78 --hr_utility.trace_on(null,'AMT');
79
80 hr_utility.trace('LANGUAGE '||p_language);
81 hr_utility.trace('ABS_NUM '||abs_num);
82
83 select substr(abs_num,1,instr(abs_num,'.'))
84 into numeral
85 from dual;
86
87
88 hr_utility.trace('NUMERAL '||to_char(numeral));
89
90 if numeral >= power(10,max_digit) then
91 raise number_too_large;
92 end if;
93
94 if numeral = 0 and to_number(abs_num) = 0 then
95 select description
96 into c_zero
97 from fnd_lookup_types flt,
98 fnd_lookup_values flv
99 where flt.application_id = 200
100 and flt.lookup_type = 'NUMBERS'
101 and flv.lookup_type = flt.lookup_type
102 and flv.lookup_code = '0'
103 and flv.language = p_language;
104 return(c_zero);
105 end if;
106
107 cents := to_char( (abs_num - numeral),'9.99' );
108 cents_seg := substr(cents,instr(cents,'.')+1,2);
109 hr_utility.trace('CENTS NUMERAL '||cents_seg);
110 hr_utility.trace('BILLION NUMERAL '||to_char(numeral));
111 billion_seg := to_char(trunc(numeral/billion));
112 hr_utility.trace('BILLION '||billion_seg);
113 numeral := numeral - (trunc(numeral/billion) * billion);
114 hr_utility.trace('MILLION NUMERAL '||to_char(numeral));
115 million_seg := to_char(trunc(numeral/million));
116 hr_utility.trace('MILLION '||million_seg);
117 numeral := numeral - (trunc(numeral/million) * million);
118 hr_utility.trace('THOUSAND NUMERAL '||to_char(numeral));
119 thousand_seg := to_char(trunc(numeral/thousand));
120 hr_utility.trace('THOUSAND '||thousand_seg);
121 units_seg := numeral - (trunc(numeral/thousand) * thousand);
122 hr_utility.trace('UNITS NUMERAL '||units_seg);
123 hr_utility.trace('UNITS '||units_seg);
124 --units_seg := to_char(mod(numeral,thousand));
125
126
127 select flv9.meaning,
128 flv10.meaning,
129 flv11.meaning
130 into and_lookup,
131 dollars_lookup,
132 cents_lookup
133 from fnd_lookup_types flt9,
134 fnd_lookup_values flv9,
135 fnd_lookup_types flt10,
136 fnd_lookup_values flv10,
137 fnd_lookup_types flt11,
138 fnd_lookup_values flv11
139 where flt9.application_id = 800
140 and flt9.lookup_type = 'AMOUNT_IN_WORDS'
141 and flv9.lookup_type = flt9.lookup_type
142 and flv9.lookup_code = 'AND'
143 and flv9.language = p_language
144 and flt10.application_id = 800
145 and flt10.lookup_type = 'AMOUNT_IN_WORDS'
146 and flv10.lookup_type = flt9.lookup_type
147 and flv10.lookup_code = p_denomination
148 and flv10.language = p_language
149 and flt11.application_id = 800
150 and flt11.lookup_type = 'AMOUNT_IN_WORDS'
151 and flv11.lookup_type = flt9.lookup_type
152 and flv11.lookup_code = p_sub_denomination
153 and flv11.language = p_language;
154
155 if billion_seg <> '0' then
156 select ' '||flv1.meaning||' ',
157 flv2.description
158 into c_billion,
159 billion_lookup
160 from fnd_lookup_values flv1,
161 fnd_lookup_types flt1,
162 fnd_lookup_values flv2,
163 fnd_lookup_types flt2
164 where flt1.application_id = 800
165 and flt1.lookup_type = 'AMOUNT_IN_WORDS'
166 and flv1.lookup_type = flt1.lookup_type
167 and flv1.lookup_code = 'BILLION'
168 and flv1.language = p_language
169 and flt2.application_id = 200
170 and flt2.lookup_type = 'NUMBERS'
171 and flv2.lookup_type = flt2.lookup_type
172 and flv2.lookup_code = billion_seg
173 and flv2.language = p_language;
174
175 number_text := number_text||billion_lookup ||c_billion;
176 end if;
177
178 if million_seg <> '0' then
179 select ' '||flv3.meaning||' ',
180 flv4.description
181 into c_million,
182 million_lookup
183 from fnd_lookup_values flv3,
184 fnd_lookup_types flt3,
185 fnd_lookup_values flv4,
186 fnd_lookup_types flt4
187 where flt3.application_id = 800
188 and flt3.lookup_type = 'AMOUNT_IN_WORDS'
189 and flv3.lookup_type = flt3.lookup_type
190 and flv3.lookup_code = 'MILLION'
191 and flv3.language = p_language
192 and flt4.application_id = 200
193 and flt4.lookup_type = 'NUMBERS'
194 and flv4.lookup_type = flt4.lookup_type
195 and flv4.lookup_code = million_seg
196 and flv4.language = p_language;
197
198 number_text := number_text||million_lookup||c_million;
199 end if;
200
201 if thousand_seg <> '0' then
202 select ' '||flv5.meaning||' ',
203 flv6.description
204 into c_thousand,
205 thousand_lookup
206 from fnd_lookup_values flv5,
207 fnd_lookup_types flt5,
208 fnd_lookup_values flv6,
209 fnd_lookup_types flt6
210 where flt5.application_id = 800
211 and flt5.lookup_type = 'AMOUNT_IN_WORDS'
212 and flv5.lookup_type = flt5.lookup_type
213 and flv5.lookup_code = 'THOUSAND'
214 and flv5.language = p_language
215 and flt6.application_id = 200
216 and flt6.lookup_type = 'NUMBERS'
217 and flv6.lookup_type = flt6.lookup_type
218 and flv6.lookup_code = thousand_seg
219 and flv6.language = p_language;
220
221 if thousand_seg = '1' then
222 thousand_lookup := null;
223 end if;
224
225 number_text := number_text||thousand_lookup||c_thousand;
226 end if;
227
228 if units_seg <> '0' then
229 select flv7.description
230 into units_lookup
231 from fnd_lookup_values flv7,
232 fnd_lookup_types flt7
233 where flt7.application_id = 200
234 and flt7.lookup_type = 'NUMBERS'
235 and flv7.lookup_type = flt7.lookup_type
236 and flv7.lookup_code = units_seg
237 and flv7.language = p_language;
238
239 number_text := number_text||units_lookup;
240 end if;
241
242 number_text := ltrim(number_text);
243 number_text := upper(substr(number_text,1,1)) ||
244 rtrim(lower(substr(number_text,2,length(number_text))));
245
246 if number_text is null and cents_seg is not null then
247 number_text := cents_seg||' '||cents_lookup;
248 else
249 number_text := number_text || ' '||dollars_lookup||' '||and_lookup ||
250 ' '||cents_seg||' '||cents_lookup;
251 end if;
252
253 --hr_utility.trace_off;
254 return(number_text);
255
256 exception
257 when number_too_large then
258 return(null);
259 when others then
260 return(null);
261 end;
262
263 end pay_ca_amt_in_words;