1 package body FND_CURRENCY_CACHE as
2 /* $Header: AFMLCUCB.pls 120.2 2005/11/02 14:05:46 fskinner noship $ */
3
4
5
6 /* These variables are used to cache the last format mask so */
7 /* that multiple requests for the same format mask won't require */
8 /* it to be recomputed. */
9
10 /* Cache for BUILD_FORMAT_MASK */
11 bfm_old_format_mask VARCHAR2(100) := NULL;
12 bfm_old_field_length NUMBER := NULL;
13 bfm_old_precision NUMBER := NULL;
14 bfm_old_min_acct_unit NUMBER := NULL;
15 bfm_old_disp_grp_sep BOOLEAN := NULL;
16 bfm_old_neg_format VARCHAR2(30) := NULL;
17 bfm_old_pos_format VARCHAR2(30) := NULL;
18
19 /* Cache for GET_FORMAT_MASK */
20 gfm_old_format_mask VARCHAR2(100) := NULL;
21 gfm_old_currency_code VARCHAR2(30) := NULL;
22 gfm_old_field_length NUMBER := NULL;
23
24 /* Cache for GET_INFO */
25 gi_old_precision NUMBER := NULL;
26 gi_old_ext_precision NUMBER := NULL;
27 gi_old_min_acct_unit NUMBER := NULL;
28 gi_old_currency_code VARCHAR2(15) := NULL;
29
30 /* GET_FORMAT_MASK- get the format mask for a particular currency.
31 **
32 ** Creates a currency format mask to be used with the forms
33 ** SET_ITEM_PROPERTY(item_name, FORMAT_MASK, new_format_mask)
34 ** built-in call, or the PLSQL to_char() routine,
35 ** based on the currency code passed in.
36 **
37 */
38 function GET_FORMAT_MASK( currency_code IN VARCHAR2,
39 field_length IN NUMBER)
40 return VARCHAR2
41 is
42 return_mask VARCHAR2(100);
43 precision NUMBER; /* number of digits to right of decimal*/
44 ext_precision NUMBER; /* precision where more precision is needed*/
45 min_acct_unit NUMBER; /* minimum value by which amt can vary */
46
47 begin
48 /* Check to see if the values are already cached */
49 if (((gfm_old_currency_code = currency_code)
50 OR ((gfm_old_currency_code is NULL)
51 AND (currency_code IS NULL)))
52 AND ((gfm_old_field_length = field_length)
53 OR ((gfm_old_field_length is NULL)
54 AND (field_length IS NULL)))
55 AND (gfm_old_format_mask is not NULL)) then
56 return gfm_old_format_mask;
57 end if;
58
59 return_mask := NULL; /* initialize return_mask */
60
61 /* Check whether field_length exceeds maximum length of return_mask
62 or if currency_code is NULL. */
63 if (field_length > 100) OR (currency_code is NULL) then
64 return gfm_old_format_mask;
65 end if;
66 /* Get the precision information for a currency code */
67 get_info(currency_code, precision, ext_precision, min_acct_unit);
68
69 /* Create the format mask for the given currency value */
70 build_format_mask(return_mask, field_length, precision, min_acct_unit);
71
72 gfm_old_currency_code := currency_code;
73 gfm_old_field_length := field_length;
74 gfm_old_format_mask := return_mask;
75 return return_mask;
76
77 end;
78
79 /*
80 ** GET_INFO- get the precision information for a currency code
81 **
82 ** returns information about a currency code, based on the
83 ** cache of currency information from the FND db table.
84 **
85 */
86 procedure GET_INFO(
87 currency_code IN VARCHAR2, /* currency code */
88 precision OUT nocopy NUMBER, /* number of digits to right of decimal */
89 ext_precision OUT nocopy NUMBER, /* precision where more precision is needed */
90 min_acct_unit OUT nocopy NUMBER /* minimum value by which amt can vary */
91 ) is
92 begin
93 if gi_old_currency_code = currency_code then
94 precision := gi_old_precision;
95 ext_precision := gi_old_ext_precision;
96 min_acct_unit := gi_old_min_acct_unit;
97 else
98 gi_old_currency_code := currency_code;
99
100 select PRECISION, EXTENDED_PRECISION, MINIMUM_ACCOUNTABLE_UNIT
101 into gi_old_precision, gi_old_ext_precision, gi_old_min_acct_unit
102 from FND_CURRENCIES c
103 where gi_old_currency_code = c.CURRENCY_CODE;
104
105 /* Precision should never be NULL; this is just so it works w/ bad data*/
106 if (gi_old_precision is NULL) then /* Default precision to 2 if nec. */
107 precision := 2;
108 else
109 precision := gi_old_precision;
110 end if;
111
112 /* Ext Precision should never be NULL; this is so it works w/ bad data*/
113 if (gi_old_ext_precision is NULL) then /* Default ext_precision if nec. */
114 ext_precision := 5;
115 else
116 ext_precision := gi_old_ext_precision;
117 end if;
118 end if;
119
120 exception
121 when NO_DATA_FOUND then
122 gi_old_precision := 0;
123 gi_old_ext_precision := 0;
124 gi_old_min_acct_unit := 0;
125 end;
126
127
128 /* BUILD_FORMAT_MASK- create a format mask for a currency value
129 **
130 ** Creates a currency format mask to be used with forms
131 ** SET_ITEM_PROPERTY(item_name, FORMAT_MASK, new_format_mask)
132 ** built-in call, or the PLSQL to_char() routine,
133 ** based on the currency parameters passed in.
134 **
135 ** Note that if neg_format is '-XXX', then pos_format must
136 ** be '+XXX' or 'XXX'.
137 **
138 ** If the last three parameters are left off, their values will
139 ** default from the profile value system.
140 */
141 procedure BUILD_FORMAT_MASK(
142 format_mask OUT nocopy VARCHAR2,
143 field_length IN NUMBER, /* maximum number of char in dest field */
144 precision IN NUMBER, /* number of digits to right of decimal*/
145 min_acct_unit IN NUMBER, /* minimum value by which amt can vary */
146 disp_grp_sep IN BOOLEAN default NULL,
147 /* NULL=from profile CURRENCY:THOUSANDS_SEPARATOR */
148 neg_format IN VARCHAR2 default NULL,
149 /* '-XXX', 'XXX-', '<XXX>', */
150 /* NULL=from profile CURRENCY:NEGATVE_FORMAT */
151 pos_format IN VARCHAR2 default NULL
152 /* 'XXX', '+XXX', 'XXX-', */
153 /* NULL=from profile CURRENCY:POSITIVE_FORMAT*/
154 ) is
155
156 mask VARCHAR2(100);
157 whole_width NUMBER; /* number of characters to left of decimal */
158 decimal_width NUMBER; /* width of decimal and numbers rt of dec */
159 sign_width NUMBER; /* width of pos/neg sign */
160 profl_val VARCHAR2(80);
161 x_disp_grp_sep BOOLEAN;
162 x_pos_format VARCHAR2(30);
163 x_neg_format VARCHAR2(30);
164
165 begin
166 /* Check to see if values are already cached */
167 if ((bfm_old_precision = precision)
168 AND ((bfm_old_field_length = field_length)
169 OR( (bfm_old_field_length is NULL)
170 AND(field_length IS NULL)))
171 AND ((bfm_old_min_acct_unit = min_acct_unit)
172 OR( (bfm_old_min_acct_unit is NULL)
173 AND(min_acct_unit IS NULL)))
174 AND ((bfm_old_disp_grp_sep = disp_grp_sep)
175 OR( (bfm_old_disp_grp_sep is NULL)
176 AND(disp_grp_sep IS NULL)))
177 AND ((bfm_old_neg_format = neg_format)
178 OR( (bfm_old_neg_format is NULL)
179 AND(neg_format IS NULL)))
180 AND ((bfm_old_pos_format = pos_format)
181 OR( (bfm_old_pos_format is NULL)
182 AND(pos_format IS NULL)))
183 AND (bfm_old_format_mask is not NULL)) then
184 format_mask := bfm_old_format_mask;
185 return;
186 end if;
187 format_mask := '';
188
189 /* process the arguments, defaulting in profile values if necessary*/
190
191 if(disp_grp_sep is NULL) then
192 profl_val:= fnd_profile.value_specific('CURRENCY:THOUSANDS_SEPARATOR');
193 if (profl_val = 'Y') then
194 x_disp_grp_sep := TRUE;
195 else
196 x_disp_grp_sep := FALSE;
197 end if;
198 else
199 x_disp_grp_sep := disp_grp_sep;
200 end if;
201
202 if(neg_format is NULL) then
203 profl_val := fnd_profile.value_specific('CURRENCY:NEGATIVE_FORMAT');
204 if(profl_val = '0' or profl_val = '1' or profl_val = '2') then
205 x_neg_format := '<XXX>';
206 elsif (profl_val <> '4') then /* '3' or default gives leading sign*/
207 x_neg_format := '-XXX';
208 else /* '4' gives trailing sign */
209 x_neg_format := 'XXX-';
210 end if;
211 else
212 x_neg_format := neg_format;
213 end if;
214
215 if(pos_format is NULL) then
216 profl_val := fnd_profile.value_specific('CURRENCY:POSITIVE_FORMAT');
217 if(profl_val = '1') then
218 x_pos_format := '+XXX';
219 elsif (profl_val = '2') then
220 x_pos_format := 'XXX+';
221 else /* '0' or default gives no pos. */
222 x_pos_format := 'XXX';
223 end if;
224 else
225 x_pos_format := pos_format;
226 end if;
227
228 /* NULL precision can mean that GET_INFO failed to find info for currency*/
229 if (precision is NULL) then
230 format_mask := '';
231 return;
232 end if;
233
234 if (precision > 0) then /* If there is a decimal portion */
235 decimal_width := 1 + precision;
236 else
237 decimal_width := 0;
238 end if;
239
240 if (x_neg_format = '<XXX>') then
241 sign_width := 2;
242 else
243 sign_width := 1;
244 end if;
245
246 /* Determine the length of the portion to the left of decimal.
247 * This value has been adjusted by subtracting 1 to account for
248 * the addition of the string 'FM' which prevents leading spaces.
249 * Without the adjustment, the resulting format mask can be larger
250 * than the alotted maximum length for format_mask. This would
251 * result in ORA-6502 PL/SQL: numeric or value error: character string
252 * buffer too small. See bug 1580374.
253 */
254 whole_width := field_length - decimal_width - sign_width - 1;
255
256 if (whole_width < 0) then
257 format_mask := '';
258 return;
259 end if;
260
261 /* build up the portion to the left of decimal, e.g. 99G999G990 */
262
263 mask := '0' || mask; /* Start the format with 0 */
264
265 if (whole_width > 1) then
266
267 for i in 2..whole_width loop
268
269 /* If there is a thousands separator, need to mark it. */
270 if (x_disp_grp_sep) AND (mod(i, 4) = 0) then
271 if (i < whole_width - 1) then /* don't start with */
272 mask := 'G' || mask; /* group separator */
273 end if;
274 /* Else, add 9 to the format as long as we have not reached
275 * the maximum length of whole numbers. This was added due
276 * to bug 1580374 to ensure that ORA-6502 is not obtained.
277 */
278 elsif (i <> whole_width) then
279 mask := '9' || mask;
280 end if;
281
282 end loop;
283
284 end if;
285
286 /* build up the portion to the right of the decimal e.g. .0000 */
287 if (precision > 0) then
288 mask := mask || 'D';
289 for i in 1..precision loop
290 mask := mask || '0';
291 end loop;
292 end if;
293
294 -- Add the FM mask element to keep from getting leading spaces
295 mask := 'FM' || mask;
296
297
298 /* Add the appropriate sign */
299 if (x_neg_format = 'XXX-') then
300 mask := mask || 'MI';
301 elsif (x_neg_format = '<XXX>') then
302 mask := mask || 'PR';
303 elsif (x_pos_format = '+XXX') then
304 mask := 'S' || mask;
305 end if;
306
307 format_mask := mask;
308
309 bfm_old_precision := precision;
310 bfm_old_field_length := field_length;
311 bfm_old_min_acct_unit := min_acct_unit;
312 bfm_old_disp_grp_sep := disp_grp_sep;
313 bfm_old_neg_format := neg_format;
314 bfm_old_pos_format := pos_format;
315 bfm_old_format_mask := format_mask;
316
317 end;
318
319
320 END FND_CURRENCY_CACHE;
321
[Home] [Help]