1 PACKAGE BODY FA_UTILS_PKG as
2 /* $Header: FAXUTILB.pls 120.10 2009/06/02 13:30:41 deemitta ship $ */
3
4 -- Function faxrnd
5 --
6 FUNCTION faxrnd(X_amount IN OUT NOCOPY NUMBER,
7 X_book IN VARCHAR2,
8 X_set_of_books_id IN NUMBER,
9 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
10 return BOOLEAN is
11
12 l_found boolean := FALSE;
13 l_array_count number := faxcurr_table.count;
14 l_count number := 0;
15
16 BEGIN
17
18 /* look at the record and then the array and if no hit, then select */
19 IF (X_set_of_books_id = faxcurr_record.set_of_books_id) then
20 null;
21 ELSE
22 for i in 1..l_array_count loop
23
24 l_count := i;
25
26 if (faxcurr_table(i).set_of_books_id = X_set_of_books_id) then
27 l_found := TRUE;
28 exit;
29 else
30 l_found := FALSE;
31 end if;
32
33 end loop;
34
35 if l_found = TRUE then
36 faxcurr_record := faxcurr_table(l_count);
37 else
38 SELECT X_set_of_books_id,
39 curr.currency_code,
40 curr.precision
41 INTO faxcurr_record.set_of_books_id,
42 faxcurr_record.currency_code,
43 faxcurr_record.precision
44 FROM fnd_currencies curr, gl_sets_of_books sob
45 WHERE sob.set_of_books_id = X_set_of_books_id AND
46 curr.currency_code = sob.currency_code;
47
48 faxcurr_table(l_array_count + 1):= faxcurr_record;
49
50 end if;
51 END IF;
52
53 X_amount := ROUND(X_amount, faxcurr_record.precision);
54
55 return(TRUE);
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 if (p_log_level_rec.statement_level) then
60 fa_debug_pkg.add('fa_utils_pkg.faxrnd','book', X_book,
61 p_log_level_rec => p_log_level_rec);
62 fa_debug_pkg.add('fa_utils_pkg.faxceil','set_of_books_id', X_set_of_books_id,
63 p_log_level_rec => p_log_level_rec);
64
65 end if;
66
67 FA_SRVR_MSG.ADD_SQL_ERROR(CALLING_FN => 'FA_UTILS_PKG.faxrnd', p_log_level_rec => p_log_level_rec);
68 return(FALSE);
69
70 END faxrnd;
71
72
73 FUNCTION faxtru
74 (
75 X_num IN OUT NOCOPY number,
76 X_book_type_code IN VARCHAR2,
77 X_set_of_books_id IN NUMBER,
78 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
79 return boolean is
80
81 l_found boolean := FALSE;
82 l_array_count number := faxcurr_table.count;
83 l_count number := 0;
84
85 begin <<FAXTRU>>
86
87 /* look at the record and then the array and if no hit, then select */
88
89 IF (X_set_of_books_id = faxcurr_record.set_of_books_id) then
90 null;
91 ELSE
92 for i in 1..l_array_count loop
93
94 l_count := i;
95
96 if (faxcurr_table(i).set_of_books_id = X_set_of_books_id) then
97 l_found := TRUE;
98 exit;
99 else
100 l_found := FALSE;
101 end if;
102
103 end loop;
104
105 if l_found = TRUE then
106 faxcurr_record := faxcurr_table(l_count);
107 else
108
109 SELECT X_set_of_books_id,
110 curr.precision
111 INTO faxcurr_record.set_of_books_id,
112 faxcurr_record.precision
113 FROM fnd_currencies curr, gl_sets_of_books sob
114 WHERE sob.set_of_books_id = X_set_of_books_id AND
115 curr.currency_code = sob.currency_code;
116
117 faxcurr_table(l_array_count + 1):= faxcurr_record;
118
119 end if;
120 END IF;
121
122 --
123 -- Truncate in_num based on the precision
124 --
125 X_num := trunc(X_num, faxcurr_record.precision);
126
127 return (TRUE);
128
129 exception
130 when others then
131 if (p_log_level_rec.statement_level) then
132 fa_debug_pkg.add('fa_utils_pkg.faxtru','book', X_book_type_code,
133 p_log_level_rec => p_log_level_rec);
134 fa_debug_pkg.add('fa_utils_pkg.faxceil','set_of_books_id', X_set_of_books_id,
135 p_log_level_rec => p_log_level_rec);
136
137 end if;
138
139 fa_srvr_msg.add_sql_error (
140 calling_fn => 'fa_utils_pkg.faxtru', p_log_level_rec => p_log_level_rec);
141 return (FALSE);
142 end FAXTRU;
143
144
145
146 FUNCTION faxceil(X_amount IN OUT NOCOPY NUMBER,
147 X_book IN VARCHAR2,
148 X_set_of_books_id IN NUMBER,
149 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
150 return BOOLEAN is
151
152 l_found boolean := FALSE;
153 l_array_count number := faxcurr_table.count;
154 l_count number := 0;
155
156 BEGIN
157
158 /* look at the record and then the array and if no hit, then select */
159 IF (X_set_of_books_id = faxcurr_record.set_of_books_id) then
160 null;
161 ELSE
162 for i in 1..l_array_count loop
163
164 l_count := i;
165
166 if (faxcurr_table(i).set_of_books_id = X_set_of_books_id) then
167 l_found := TRUE;
168 exit;
169 else
170 l_found := FALSE;
171 end if;
172
173 end loop;
174
175 if l_found = TRUE then
176 faxcurr_record := faxcurr_table(l_count);
177 else
178 SELECT X_set_of_books_id,
179 curr.currency_code,
180 curr.precision
181 INTO faxcurr_record.set_of_books_id,
182 faxcurr_record.currency_code,
183 faxcurr_record.precision
184 FROM fnd_currencies curr, gl_sets_of_books sob
185 WHERE sob.set_of_books_id = X_set_of_books_id AND
186 curr.currency_code = sob.currency_code;
187
188 faxcurr_table(l_array_count + 1):= faxcurr_record;
189
190 end if;
191 END IF;
192
193 X_amount := ceil(X_amount * power(10,faxcurr_record.precision)) /
194 power(10,faxcurr_record.precision);
195
196 return(TRUE);
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 if (p_log_level_rec.statement_level) then
201 fa_debug_pkg.add('fa_utils_pkg.faxceil','book', X_book,
202 p_log_level_rec => p_log_level_rec);
203 fa_debug_pkg.add('fa_utils_pkg.faxceil','set_of_books_id', X_set_of_books_id,
204 p_log_level_rec => p_log_level_rec);
205 end if;
206
207 FA_SRVR_MSG.ADD_SQL_ERROR(CALLING_FN => 'FA_UTILS_PKG.faxceil', p_log_level_rec => p_log_level_rec);
208 return(FALSE);
209
210 END faxceil;
211
212
213 FUNCTION faxfloor(X_amount IN OUT NOCOPY NUMBER,
214 X_book IN VARCHAR2,
215 X_set_of_books_id IN NUMBER,
216 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
217 return BOOLEAN is
218
219 l_found boolean := FALSE;
220 l_array_count number := faxcurr_table.count;
221 l_count number := 0;
222
223 BEGIN
224
225 /* look at the record and then the array and if no hit, then select */
226 IF (X_set_of_books_id = faxcurr_record.set_of_books_id) then
227 null;
228 ELSE
229 for i in 1..l_array_count loop
230
231 l_count := i;
232
233 if (faxcurr_table(i).set_of_books_id = X_set_of_books_id) then
234 l_found := TRUE;
235 exit;
236 else
237 l_found := FALSE;
238 end if;
239
240 end loop;
241
242 if l_found = TRUE then
243 faxcurr_record := faxcurr_table(l_count);
244 else
245 SELECT X_set_of_books_id,
246 curr.currency_code,
247 curr.precision
248 INTO faxcurr_record.set_of_books_id,
249 faxcurr_record.currency_code,
250 faxcurr_record.precision
251 FROM fnd_currencies curr, gl_sets_of_books sob
252 WHERE sob.set_of_books_id = X_set_of_books_id AND
253 curr.currency_code = sob.currency_code;
254
255 faxcurr_table(l_array_count + 1):= faxcurr_record;
256
257 end if;
258 END IF;
259
260 X_amount := trunc(X_amount * power(10,faxcurr_record.precision)) /
261 power(10,faxcurr_record.precision);
262
263 return(TRUE);
264
265 EXCEPTION
266 WHEN OTHERS THEN
267 if (p_log_level_rec.statement_level) then
268 fa_debug_pkg.add('fa_utils_pkg.faxfloor','book', X_book,
269 p_log_level_rec => p_log_level_rec);
270 fa_debug_pkg.add('fa_utils_pkg.faxceil','set_of_books_id', X_set_of_books_id,
271 p_log_level_rec => p_log_level_rec);
272
273 end if;
274
275 FA_SRVR_MSG.ADD_SQL_ERROR(CALLING_FN => 'FA_UTILS_PKG.faxfloor', p_log_level_rec => p_log_level_rec);
276 return(FALSE);
277
278 END faxfloor;
279
280 -- Function faxlkp_meaning()
281 --
282 FUNCTION faxlkp_meaning(X_lookup_type IN VARCHAR2,
283 X_lookup_code IN VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
284 return VARCHAR2 is
285
286 l_meaning fa_lookups.meaning%TYPE;
287 l_hash_value NUMBER;
288
289 BEGIN
290 IF X_lookup_code IS NOT NULL AND
291 X_lookup_type IS NOT NULL THEN
292
293 l_hash_value := DBMS_UTILITY.get_hash_value(
294 X_lookup_type||'@*?'||X_lookup_code,
295 1000,
296 25000);
297
298 IF faxlkpmg_table.EXISTS(l_hash_value) THEN
299 l_meaning := faxlkpmg_table(l_hash_value);
300 ELSE
301 /*modified the following query for bug no.3876060 */
302 select T.MEANING
303 into l_meaning
304 from FA_LOOKUPS_TL T
305 where T.LANGUAGE = USERENV('LANG')
306 and T.LOOKUP_TYPE = X_lookup_type
307 and T.LOOKUP_CODE = X_lookup_code;
308
309 faxlkpmg_table(l_hash_value) := l_meaning;
310
311 END IF;
312
313 END IF;
314
315 return(l_meaning);
316
317 EXCEPTION
318 WHEN no_data_found THEN
319 return(null);
320 WHEN OTHERS THEN
321 FA_SRVR_MSG.ADD_SQL_ERROR(CALLING_FN => 'FA_UTILS_PKG.faxlkp_meaning', p_log_level_rec => p_log_level_rec);
322 raise;
323
324 END faxlkp_meaning;
325
326 FUNCTION faxlkp_code(X_lookup_type IN VARCHAR2,
327 X_meaning IN VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
328 return VARCHAR2 is
329
330 l_lookup_code fa_lookups.Lookup_code%TYPE;
331 l_hash_value NUMBER;
332
333 BEGIN
334 IF X_Meaning IS NOT NULL AND
335 X_lookup_type IS NOT NULL THEN
336
337 l_hash_value := DBMS_UTILITY.get_hash_value(
338 X_lookup_type||'@*?'||X_meaning,
339 1000,
340 25000);
341 IF faxlkpcd_table.EXISTS(l_hash_value) THEN
342 l_lookup_code := faxlkpcd_table(l_hash_value);
343 ELSE
344 /* modified the following query for bug no.3876060 */
345 SELECT T.LOOKUP_CODE
346 into l_lookup_code
347 FROM FA_LOOKUPS_TL T
348 WHERE T.LANGUAGE = USERENV('LANG')
349 AND T.LOOKUP_TYPE = X_lookup_type
350 AND T.MEANING = X_meaning;
351
352 faxlkpcd_table(l_hash_value) := l_lookup_code;
353
354 END IF;
355 END IF;
356
357 return(l_lookup_code);
358
359 EXCEPTION
360 WHEN NO_DATA_FOUND THEN
361 return(null);
362 WHEN OTHERS THEN
363 FA_SRVR_MSG.ADD_SQL_ERROR(CALLING_FN => 'FA_UTILS_PKG.faxlkp_code', p_log_level_rec => p_log_level_rec);
364 raise;
365
366 END faxlkp_code;
367
368 END FA_UTILS_PKG;