1 PACKAGE BODY FND_CURRENCY AS
2 /* $Header: AFMLCURB.pls 120.2 2006/09/18 15:06:49 pdeluna ship $ */
3
4
5 /* GET_FORMAT_MASK- get the format mask for a particular currency.
6 **
7 ** Creates a currency format mask to be used with the forms
8 ** SET_ITEM_PROPERTY(item_name, FORMAT_MASK, new_format_mask)
9 ** built-in call, or the PLSQL to_char() routine,
10 ** based on the currency code passed in.
11 **
12 */
13 function GET_FORMAT_MASK(
14 currency_code IN VARCHAR2,
15 field_length IN NUMBER
16 )
17 return VARCHAR2
18 is
19 return_mask VARCHAR2(100);
20 precision NUMBER; /* number of digits to right of decimal*/
21 ext_precision NUMBER; /* precision where more precision is needed*/
22 min_acct_unit NUMBER; /* minimum value by which amt can vary */
23
24 begin
25
26 return_mask := NULL; /* initialize return_mask */
27
28 /* Check whether field_length exceeds maximum length of return_mask
29 or if currency_code is NULL. */
30 if(field_length > 100) OR (currency_code is NULL) then
31 return return_mask;
32 end if;
33
34 /* Get the precision information for a currency code */
35 GET_INFO(currency_code, precision, ext_precision, min_acct_unit);
36
37 /* Create the format mask for the given currency value */
38 BUILD_FORMAT_MASK(return_mask, field_length, precision, min_acct_unit);
39
40 return return_mask;
41
42 end;
43
44 /* SAFE_GET_FORMAT_MASK - slower version of GET_FORMAT_MASK
45 ** without WNPS pragma restrictions.
46 **
47 ** This version of GET_FORMAT_MASK uses slower,
48 ** non-caching profiles functions to do its defaulting. It runs
49 ** about half the speed of GET_FORMAT_MASK, but it can
50 ** be used in situations, like where clauses, in views, that
51 ** GET_FORMAT_MASK cannot be used due to pragma restrictions.
52 */
53 function SAFE_GET_FORMAT_MASK(
54 currency_code IN VARCHAR2,
55 field_length IN NUMBER
56 )
57 return VARCHAR2
58 is
59 return_mask VARCHAR2(100);
60 precision NUMBER; /* number of digits to right of decimal*/
61 ext_precision NUMBER; /* precision where more precision is needed*/
62 min_acct_unit NUMBER; /* minimum value by which amt can vary */
63
64 begin
65
66 return_mask := NULL; /* initialize return_mask */
67
68 /* Check whether field_length exceeds maximum length of return_mask
69 or if currency_code is NULL. */
70 if (field_length > 100) OR (currency_code is NULL) then
71 return return_mask;
72 end if;
73
74 /* Get the precision information for a currency code */
75 GET_INFO(currency_code, precision, ext_precision, min_acct_unit);
76
77 /* Create the format mask for the given currency value */
78 SAFE_BUILD_FORMAT_MASK(return_mask, field_length, precision, min_acct_unit);
79
80 return return_mask;
81
82 end;
83
84 /*
85 ** GET_INFO- get the precision information for a currency code
86 **
87 ** returns information about a currency code, based on the
88 ** cache of currency information from the FND db table.
89 **
90 */
91 procedure GET_INFO(
92 currency_code IN VARCHAR2, /* currency code */
93 precision OUT NOCOPY NUMBER, /* number digits to right of decimal */
94 ext_precision OUT NOCOPY NUMBER, /* precision if more precision needed */
95 min_acct_unit OUT NOCOPY NUMBER /* min value by which amt can vary */
96 )
97 is
98 x_currency_code VARCHAR2(15);
99 z_precision NUMBER;
100 z_ext_precision NUMBER;
101
102 begin
103
104 x_currency_code := currency_code;
105
106 select PRECISION, EXTENDED_PRECISION, MINIMUM_ACCOUNTABLE_UNIT
107 into z_precision, z_ext_precision, min_acct_unit
108 from FND_CURRENCIES c
109 where x_currency_code = c.CURRENCY_CODE;
110
111 /* Precision should never be NULL; this is just so it works w/ bad data */
112 if (z_precision is NULL) then /* Default precision to two if necessary. */
113 precision := 2;
114 else
115 precision := z_precision;
116 end if;
117
118 /* Ext Precision should never be NULL; this is so it works w/ bad data*/
119 if (z_ext_precision is NULL) then /* Default ext_precision if necc. */
120 ext_precision := 5;
121 else
122 ext_precision := z_ext_precision;
123 end if;
124
125 exception
126 when NO_DATA_FOUND then
127 precision := 0;
128 ext_precision := 0;
129 min_acct_unit := 0;
130
131 end;
132
133
134 /* BUILD_FORMAT_MASK- create a format mask for a currency value
135 **
136 ** Creates a currency format mask to be used with forms
137 ** SET_ITEM_PROPERTY(item_name, FORMAT_MASK, new_format_mask)
138 ** built-in call, or the PLSQL to_char() routine,
139 ** based on the currency parameters passed in.
140 **
141 ** Note that if neg_format is '-XXX', then pos_format must
142 ** be '+XXX' or 'XXX'.
143 **
144 ** If the last three parameters are left off, their values will
145 ** default from the profile value system.
146 */
147 procedure BUILD_FORMAT_MASK(
148 format_mask OUT NOCOPY VARCHAR2,
149 field_length IN NUMBER, /* maximum number of char in dest field */
150 precision IN NUMBER, /* number of digits to right of decimal*/
151 min_acct_unit IN NUMBER, /* minimum value by which amt can vary */
152 disp_grp_sep IN BOOLEAN default NULL,
153 /* NULL=from profile CURRENCY:THOUSANDS_SEPARATOR */
154 neg_format IN VARCHAR2 default NULL,
155 /* '-XXX', 'XXX-', '<XXX>', '(XXX)' */
156 /* NULL=from profile CURRENCY:NEGATVE_FORMAT */
157 pos_format IN VARCHAR2 default NULL
158 /* 'XXX', '+XXX', 'XXX-', */
159 /* NULL=from profile CURRENCY:POSITIVE_FORMAT*/
160 )
161 is
162
163 mask VARCHAR2(100);
164 whole_width NUMBER; /* number of characters to left of decimal */
165 decimal_width NUMBER; /* width of decimal and numbers rt of dec */
166 sign_width NUMBER; /* width of pos/neg sign */
167 profl_val VARCHAR2(80);
168 x_disp_grp_sep BOOLEAN;
169 x_pos_format VARCHAR2(30);
170 x_neg_format VARCHAR2(30);
171
172 begin
173
174 /* process the arguments, defaulting in profile values if necessary*/
175
176 if(disp_grp_sep is NULL) then
177 profl_val:= fnd_profile.value('CURRENCY:THOUSANDS_SEPARATOR');
178 if (profl_val = 'Y') then
179 x_disp_grp_sep := TRUE;
180 else
181 x_disp_grp_sep := FALSE;
182 end if;
183 else
184 x_disp_grp_sep := disp_grp_sep;
185 end if;
186
187 /* Bug 5529158: FND_CURRENCY.BUILD_FORMAT_MASK MISMATCH IN FNDSQF.PLD AND
188 * AFMLCURB.PLS
189 *
190 * FNDSQF.pld 115.1 was changed to enable support of the (XXX) Core number
191 * formatting ability in Core 3 (4.5.7). This change was not made in the
192 * PL/SQL package AFMLCURB.pls.
193 */
194
195 -- if(neg_format is NULL) then
196 -- profl_val := fnd_profile.value('CURRENCY:NEGATIVE_FORMAT');
197 -- if(profl_val = '0' or profl_val = '1' or profl_val = '2') then
198 -- x_neg_format := '<XXX>';
199 -- elsif (profl_val = '4') then /* '4' gives trailing sign */
200 -- x_neg_format := 'XXX-';
201 --
202 -- /* Found out that the default value being set is 'XXX-', not '-XXX',
203 -- * which is documented to be the default value.
204 -- */
205 --
206 -- else /* '3' or default gives leading sign */
207 -- x_neg_format := '-XXX';
208 -- end if;
209 -- else
210 -- x_neg_format := neg_format;
211 -- end if;
212
213 if(neg_format is NULL) then
214 profl_val := fnd_profile.value('CURRENCY:NEGATIVE_FORMAT');
215 if( profl_val = '0' /* (XXX) */
216 or profl_val = '1') then /* [XXX] */
217 x_neg_format := '(XXX)'; -- Bug 5529158
218 elsif( profl_val = '2') then /* <XXX> */
219 x_neg_format := '<XXX>';
220 elsif( profl_val = '4') then /* '4' gives trailing sign*/
221 x_neg_format := 'XXX-';
222
223 /* Found out that the default value being set is 'XXX-', not '-XXX',
224 * which is documented to be the default value.
225 */
226
227 else /* '3' or default gives leading sign */
228 x_neg_format := '-XXX';
229 end if;
230 else
231 x_neg_format := neg_format;
232 end if;
233
234
235 if(pos_format is NULL) then
236 profl_val := fnd_profile.value('CURRENCY:POSITIVE_FORMAT');
237 if(profl_val = '1') then
238 x_pos_format := '+XXX';
239 elsif (profl_val = '2') then
240 x_pos_format := 'XXX+';
241 else /* '0' or default gives no pos. */
242 x_pos_format := 'XXX';
243 end if;
244 else
245 x_pos_format := pos_format;
246 end if;
247
248 /* Build the format mask */
249 SAFE_BUILD_FORMAT_MASK(mask, field_length, precision, min_acct_unit,
250 x_disp_grp_sep, x_neg_format, x_pos_format);
251
252 format_mask := mask;
253
254 end;
255
256
257 /* SAFE_BUILD_FORMAT_MASK- slower version of BUILD_FORMAT_MASK
258 ** without WNPS pragma restrictions.
259 **
260 ** This version of BUILD_FORMAT_MASK uses slower,
261 ** non-caching profiles functions to do its defaulting. It runs
262 ** about half the speed of BUILD_FORMAT_MASK, but it can
263 ** be used in situations, like views, that BUILD_FORMAT_MASK
264 ** cannot be used due to pragma restrictions.
265 ** Note, however, that if you pass values for the
266 ** disp_grp_sep, neg_format, and pos_format parameters instead
267 ** of letting them default to NULL, then this routine will
268 ** not be any slower than BUILD_FORMAT_MASK
269 */
270 procedure SAFE_BUILD_FORMAT_MASK(
271 format_mask OUT NOCOPY VARCHAR2,
272 field_length IN NUMBER, /* maximum number of char in dest field */
273 precision IN NUMBER, /* number of digits to right of decimal*/
274 min_acct_unit IN NUMBER, /* minimum value by which amt can vary */
275 disp_grp_sep IN BOOLEAN default NULL,
276 /* NULL=from profile CURRENCY:THOUSANDS_SEPARATOR */
277 neg_format IN VARCHAR2 default NULL,
278 /* '-XXX', 'XXX-', '<XXX>', '(XXX)' */
279 /* NULL=from profile CURRENCY:NEGATVE_FORMAT */
280 pos_format IN VARCHAR2 default NULL
281 /* 'XXX', '+XXX', 'XXX-', */
282 /* NULL=from profile CURRENCY:POSITIVE_FORMAT*/
283 )
284 is
285
286 mask VARCHAR2(100);
287 whole_width NUMBER; /* number of characters to left of decimal */
288 decimal_width NUMBER; /* width of decimal and numbers rt of dec */
289 sign_width NUMBER; /* width of pos/neg sign */
290 profl_val VARCHAR2(80);
291 x_disp_grp_sep BOOLEAN;
292 x_pos_format VARCHAR2(30);
293 x_neg_format VARCHAR2(30);
294
295 begin
296
297 /* process the arguments, defaulting in profile values if necessary*/
298
299 if(disp_grp_sep is NULL) then
300 profl_val:= fnd_profile.value_specific('CURRENCY:THOUSANDS_SEPARATOR');
301 if (profl_val = 'Y') then
302 x_disp_grp_sep := TRUE;
303 else
304 x_disp_grp_sep := FALSE;
305 end if;
306 else
307 x_disp_grp_sep := disp_grp_sep;
308 end if;
309
310 /* Bug 5529158: FND_CURRENCY.BUILD_FORMAT_MASK MISMATCH IN FNDSQF.PLD AND
311 * AFMLCURB.PLS
312 *
313 * FNDSQF.pld 115.1 was changed to enable support of the (XXX) Core number
314 * formatting ability in Core 3 (4.5.7). This change was not made in the
315 * PL/SQL package AFMLCURB.pls. Hence, there is an inconsistency between
316 * FNDSQF.pld and AFMLCURB.pls with regards to handling (XXX).
317 */
318
319 -- if(neg_format is NULL) then
320 -- profl_val := fnd_profile.value_specific('CURRENCY:NEGATIVE_FORMAT');
321 -- if(profl_val = '0' or profl_val = '1' or profl_val = '2') then
322 -- x_neg_format := '<XXX>';
323 -- elsif (profl_val = '4') then /* '4' gives trailing sign */
324 -- x_neg_format := 'XXX-';
325 --
326 -- /* Found out that the default value being set is 'XXX-', not '-XXX',
327 -- * which is documented to be the default value.
328 -- */
329 --
330 -- else /* '3' or default gives leading sign */
331 -- x_neg_format := '-XXX';
332 -- end if;
333 -- else
334 -- x_neg_format := neg_format;
335 -- end if;
336
337 if(neg_format is NULL) then
338 profl_val := fnd_profile.value_specific('CURRENCY:NEGATIVE_FORMAT');
339 if( profl_val = '0' /* (XXX) */
340 or profl_val = '1') then /* [XXX] */
341 x_neg_format := '(XXX)'; -- Bug 5529158
342 elsif( profl_val = '2') then /* <XXX> */
343 x_neg_format := '<XXX>';
347 /* Found out that the default value being set is 'XXX-', not '-XXX',
344 elsif( profl_val = '4') then /* '4' gives trailing sign*/
345 x_neg_format := 'XXX-';
346
348 * which is documented to be the default value.
349 */
350
351 else /* '3' or default gives leading sign */
352 x_neg_format := '-XXX';
353 end if;
354 else
355 x_neg_format := neg_format;
356 end if;
357
358 if(pos_format is NULL) then
359 profl_val := fnd_profile.value_specific('CURRENCY:POSITIVE_FORMAT');
360 if(profl_val = '1') then
361 x_pos_format := '+XXX';
362 elsif (profl_val = '2') then
363 x_pos_format := 'XXX+';
364 else /* '0' or default gives no pos. */
365 x_pos_format := 'XXX';
366 end if;
367 else
368 x_pos_format := pos_format;
369 end if;
370
371 /* NULL precision can mean that GET_INFO failed to find info for currency*/
372 if (precision is NULL) then
373 format_mask := '';
374 return;
375 end if;
376
377 if (precision > 0) then /* If there is a decimal portion */
378 decimal_width := 1 + precision;
379 else
380 decimal_width := 0;
381 end if;
382
383 /* Bug 2993411: FND_CURRENCY.GET_FORMAT_MASK:PL/SQL:NUMERIC OR VALUE
384 * ERROR:STRING BUFFER
385 *
386 * When the profile option 'Currency: Negative Format' is set to 'XXX-', the * string 'MI'is appended to the end of the format mask. This addition
387 * causes the string to be longer than the field_length. So, along with
388 * '<XXX>', 'XXX-' is also adjusted for proper sign_width to ensure that the * resulting format mask does not exceed the desired field_length.
389 */
390 if (x_neg_format = '<XXX>'
391 or x_neg_format = '(XXX)' -- Bug 5529158
392 or x_neg_format = 'XXX-') then
393 sign_width := 2;
394 else
395 sign_width := 1;
396 end if;
397
398 /* Determine the length of the portion to the left of decimal.
399 * This value has been adjusted by subtracting 1 to account for
400 * the addition of the string 'FM' which prevents leading spaces.
401 * Without the adjustment, the resulting format mask can be larger
402 * than the allotted maximum length for format_mask. This would
403 * result in ORA-6502 PL/SQL: numeric or value error: character string
404 * buffer too small. See bug 1580374.
405 */
406 whole_width := field_length - decimal_width - sign_width - 1;
407
408 if (whole_width < 0) then
409 format_mask := '';
410 return;
411 end if;
412
413 /* build up the portion to the left of decimal, e.g. 99G999G990 */
414
415 mask := '0' || mask; /* Start the format with 0 */
416
417 if (whole_width > 1) then
418
419 for i in 2..whole_width loop
420
421 /* If there is a thousands separator, need to mark it. */
422 if (x_disp_grp_sep) AND (mod(i, 4) = 0) then
423 if (i < whole_width - 1) then /* don't start with */
424 mask := 'G' || mask; /* group separator */
425 end if;
426 /* Else, add 9 to the format as long as we have not reached
427 * the maximum length of whole numbers. This was added due
428 * to bug 1580374 to ensure that ORA-6502 is not obtained.
429 */
430 elsif (i <> whole_width) then
431 mask := '9' || mask;
432 end if;
433
434 end loop;
435
436 end if;
437
438 /* build up the portion to the right of the decimal e.g. .0000 */
439 if (precision > 0) then
440 mask := mask || 'D';
441 for i in 1..precision loop
442 mask := mask || '0';
443 end loop;
444 end if;
445
446 /* Add the FM mask element to keep from getting leading spaces */
447 mask := 'FM' || mask;
448
449
450 /* Add the appropriate sign */
451
452 /*
453 Per bug 2708367, according to SQL Reference Manual. Chapter 2:"Basic
454 Elements of Oracle SQL", in the table of "Number Format Elements", MI means
455 "returns negative value with a trailing minus sign (-) and returns positive
456 value with a trailing blank". Therefore, the returned format mask is
457 incorrect if the profile options CURRENCY:NEGATIVE_FORMAT is set to XXX- and
458 CURRENCY:POSITIVE_FORMAT is set to XXX+.
459 */
460
461 if (x_neg_format = 'XXX-' and x_pos_format = 'XXX+' ) then
462 mask := mask || 'S';
463 elsif (x_neg_format = 'XXX-' and x_pos_format <> 'XXX+') then
464 mask := mask || 'MI';
465 elsif (x_neg_format = '<XXX>') then
466 mask := mask || 'PR';
467 elsif (x_neg_format = '(XXX)') then -- Bug 5529158: This is being made
468 mask := mask || 'PT'; -- consistent with FNDSQF.pld
469 elsif (x_pos_format = '+XXX') then
470 mask := 'S' || mask;
471 end if;
472
473 format_mask := mask;
474
475 end;
476
477 END FND_CURRENCY;
478
[Home] [Help]