1 PACKAGE BODY arpcurr AS
2 /* $Header: ARPLCURB.pls 120.9.12010000.1 2008/07/24 16:50:05 appldev ship $ */
3
4 TYPE CurrencyCodeType IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
5 TYPE PrecisionType IS TABLE OF NUMBER(1) INDEX BY BINARY_INTEGER;
6 TYPE MauType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7 NextElement BINARY_INTEGER := 0;
8 CurrencyCode CurrencyCodeType;
9 Precision PrecisionType;
10 Mau MauType;
11 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
12
13 /* 5468091 - cache sob_id for getfunccurr() function */
14 g_sob_id NUMBER := -2; -- init to known value
15 g_func_currency VARCHAR2(15);
16
17 CURSOR CurrencyCursor( cp_currency_code VARCHAR2 ) IS
18 SELECT precision,
19 minimum_accountable_unit
20 FROM fnd_currencies
21 WHERE currency_code = cp_currency_code;
22 --
23 PROCEDURE GetCurrencyDetails( p_currency_code IN VARCHAR2,
24 p_precision OUT NOCOPY NUMBER,
25 p_mau OUT NOCOPY NUMBER ) IS
26 i BINARY_INTEGER := 0;
27 BEGIN
28 WHILE i < NextElement
29 LOOP
30 EXIT WHEN CurrencyCode(i) = p_currency_code;
31 i := i + 1;
32 END LOOP;
33 --
34 IF i = NextElement
35 THEN
36 OPEN CurrencyCursor( p_currency_code );
37 DECLARE
38 l_Precision NUMBER;
39 l_Mau NUMBER;
40 BEGIN
41 FETCH CurrencyCursor
42 INTO l_Precision,
43 l_Mau;
44 IF CurrencyCursor%NOTFOUND THEN
45 RAISE NO_DATA_FOUND;
46 END IF;
47 Precision(i) := l_Precision;
48 Mau(i) := l_Mau;
49 END;
50 CLOSE CurrencyCursor;
51 CurrencyCode(i) := p_currency_code;
52 NextElement := i + 1;
53 END IF;
54 p_precision := Precision(i);
55 p_mau := Mau(i);
56 EXCEPTION
57 WHEN OTHERS THEN
58 -- bug 2191876
59 IF CurrencyCursor%ISOPEN THEN
60 CLOSE CurrencyCursor;
61 END IF;
62 RAISE;
63 END;
64 --
65 FUNCTION CurrRound( p_amount IN NUMBER, p_currency_code IN VARCHAR2 := FunctionalCurrency) RETURN NUMBER IS
66 l_precision NUMBER(1);
67 l_mau NUMBER;
68 BEGIN
69 GetCurrencyDetails( p_currency_code, l_precision, l_mau );
70 IF l_mau IS NOT NULL
71 THEN
72 RETURN( ROUND( p_amount / l_mau) * l_mau );
73 ELSE
74 RETURN( ROUND( p_amount, l_precision ));
75 END IF;
76 EXCEPTION
77 WHEN OTHERS THEN
78 RAISE;
79 END;
80 --
81 /*
82 -- This procedure is designed to help in calculating acctd equivalents,
83 -- and reconciling the sum of accounted equivalents
84 --
85 -- The basis of this routine is that the accounted equivalent of a 'child'
86 -- amount is the difference in the accounted amount produced by the
87 -- child on the 'parent' record
88 --
89 -- Additionally, at any point when the sum of the 'child' amounts is
90 -- equal to a reconcile amount on the parent, the sum of 'child'
91 -- accounted amounts should be equal to the accounted reconcile
92 -- amount of the 'parent'
93 --
94 -- The routine assumes that the amounts are being converted to the
95 -- Set of Books functional currency
96 --
97 -- Sample Implementation in calling context
98 --
99 -- The procedure takes an exchange rate and currency as parameters
100 -- It converts the entered values 1,2,3 to accounted, and reconciles
101 -- the accounted amount
102 --
103 -- PROCEDURE TestReconcile( p_ExchangeRate IN NUMBER ) IS
104 l_RunningTotalAmount NUMBER := 0;
105 l_RunningTotalAcctdAmount NUMBER := 0;
106 l_ReconcileAmount NUMBER;
107 l_ReconcileAcctdAmount NUMBER;
108 BEGIN
109 l_ReconcileAmount := 6; -- 1 + 2 + 3
110 l_ReconcileAcctdAmount := CurrRound( ReconcileAmount * p_ExchangeRate );
111 dbms_output.put_line( ReconcileAcctdAmount( p_ExchangeRate,
112 l_ReconcileAmount, l_ReconcileAcctdAmount,
113 1,
114 l_RunningTotalAmount, l_RunningTotalAcctdAmount ));
115 dbms_output.put_line( ReconcileAcctdAmount( p_ExchangeRate,
116 l_ReconcileAmount, l_ReconcileAcctdAmount,
117 2,
118 l_RunningTotalAmount, l_RunningTotalAcctdAmount ));
119 dbms_output.put_line( ReconcileAcctdAmount( p_ExchangeRate,
120 l_ReconcileAmount, l_ReconcileAcctdAmount,
121 3,
122 l_RunningTotalAmount, l_RunningTotalAcctdAmount ));
123 END;
124 */
125 FUNCTION ReconcileAcctdAmounts( p_ExchangeRate IN NUMBER,
126 p_ReconcileAmount IN NUMBER,
127 p_ReconcileAcctdAmount IN NUMBER,
128 p_ChildAmount IN NUMBER,
129 p_RunningTotalAmount IN OUT NOCOPY NUMBER,
130 p_RunningTotalAcctdAmount IN OUT NOCOPY NUMBER ) RETURN NUMBER IS
131 l_AcctdChildAmount NUMBER;
132 BEGIN
133 p_RunningTotalAmount := p_RunningTotalAmount + p_ChildAmount;
134 IF p_RunningTotalAmount = p_ReconcileAmount
135 THEN
136 l_AcctdChildAmount := p_ReconcileAcctdAmount - p_RunningTotalAcctdAmount;
137 ELSE
138 l_AcctdChildAmount := CurrRound( p_RunningTotalAmount * p_ExchangeRate ) - p_RunningTotalAcctdAmount;
139 END IF;
140 --
141 p_RunningTotalAcctdAmount := p_RunningTotalAcctdAmount + l_AcctdChildAmount;
142 RETURN l_AcctdChildAmount;
143 EXCEPTION
144 WHEN OTHERS THEN
145 arp_standard.debug( 'arpcurr.ReconcileAcctdAmounts: $Revision: 120.9.12010000.1 $' );
146 arp_standard.debug( 'l_AcctdChildAmount:'||l_AcctdChildAmount );
147 RAISE;
148 END;
149
150
151 /*----------------------------------------------------------------------------*
152 | PUBLIC FUNCTION |
153 | functional_amount |
154 | |
155 | DESCRIPTION |
156 | This function returns the functional amount for a given foreign amount. |
157 | THe functional amount is rounded to the correct precision. |
158 | |
159 | REQUIRES |
160 | Amount - the foreign amount |
161 | Exchange Rate - to use when converting to functional amount |
162 | one of: |
163 | Currency Code - of the functional amount |
164 | Precision - of the functional amount |
165 | minimum accountable unit - of the functional amount |
166 | |
167 | RETURNS |
168 | amount * exchange_rate to correct rounding for currency |
169 | |
170 | EXCEPTIONS RAISED |
171 | Oracle Error If Currency Code, Precision and minimum accountable |
172 | are all NULL |
173 | |
174 | Oracle Error If can not find information for Currency Code |
175 | supplied |
176 | |
177 | KNOWN BUGS |
178 | <none> |
179 | |
180 | NOTES |
181 | |
182 | HISTORY |
183 | 2/10/93 Martin Morris Created |
184 | 7/21/95 Martin Johnson Replaced fnd_message with |
185 | user-defined exception so that|
186 | pragma restrict_references |
187 | does not fail |
188 | |
189 *----------------------------------------------------------------------------*/
190
191
192 FUNCTION functional_amount(amount IN NUMBER,
193 currency_code IN VARCHAR2,
194 exchange_rate IN NUMBER,
195 precision IN NUMBER,
196 min_acc_unit IN NUMBER) RETURN NUMBER IS
197
198 /*----------------------------------------------------------------------------*
199 | PRIVATE CURSOR |
200 | curr_info |
201 | |
202 | DESCRIPTION |
203 | Gets the precision and the minimum accountable unit for the currency |
204 | Supplied |
205 | |
206 *----------------------------------------------------------------------------*/
207
208 CURSOR curr_info (cc FND_CURRENCIES.CURRENCY_CODE%TYPE) IS
209 SELECT PRECISION,
210 MINIMUM_ACCOUNTABLE_UNIT,
211 CURRENCY_CODE
212 FROM FND_CURRENCIES
213 WHERE CURRENCY_CODE = cc;
214
215 /*---------------------------------------------------------------------------*
216 | PRIVATE DATATYPES |
217 | |
218 *---------------------------------------------------------------------------*/
219
220
221 curr curr_info%ROWTYPE;
222 loc_amount NUMBER;
223 invalid_params EXCEPTION;
224
225 BEGIN
226
227 /*--------------------------------------------------------------------*
228 | Validate Parameters |
229 *--------------------------------------------------------------------*/
230
231 IF (((currency_code IS NULL) AND
232 (precision IS NULL) AND
233 (min_acc_unit IS NULL)) OR
234 (amount IS NULL) ) THEN
235 BEGIN
236
237 /* fnd_message('STD-FUNCT-AMT-INV-PAR'); */
238
239 RAISE invalid_params;
240
241 END;
242 END IF;
243
244 /*--------------------------------------------------------------------*
245 | Only get currency info from database if not supplied as parameters |
246 *--------------------------------------------------------------------*/
247
248
249 IF ((precision IS NULL) AND (min_acc_unit IS NULL)) THEN
250 BEGIN
251 OPEN curr_info(currency_code);
252 FETCH curr_info INTO curr;
253 CLOSE curr_info;
254
255 IF (curr.currency_code IS NULL) THEN
256
257 /* fnd_message('STD-FUNCT-AMT-CURR-NF',
258 'CURR',
259 currency_code); */
260
261 RAISE invalid_params;
262
263 END IF;
264
265 END;
266 ELSE
267 BEGIN
268 curr.precision := precision;
269 curr.minimum_accountable_unit := min_acc_unit;
270 END;
271 END IF;
272
273 loc_amount := amount * NVL(exchange_rate, 1);
274
275 /*-----------------*
276 | Round correctly |
277 *-----------------*/
278
279 IF (curr.minimum_accountable_unit IS NULL) THEN
280 RETURN( ROUND(loc_amount, curr.precision));
281 ELSE
282 RETURN( ROUND((loc_amount / curr.minimum_accountable_unit)) *
283 curr.minimum_accountable_unit);
284 END IF;
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 -- Bug 2191876
289 IF curr_info%ISOPEN THEN
290 CLOSE curr_info;
291 END IF;
292
293 RAISE;
294
295 END functional_amount;
296
297 Function GetFunctCurr(p_set_of_books_id IN Number) Return Varchar2 IS
298
299 BEGIN
300
301 IF g_sob_id <> p_set_of_books_id
302 THEN
303
304 SELECT currency_code
305 INTO g_func_currency
306 FROM gl_sets_of_books
307 WHERE set_of_books_id = p_set_of_books_id;
308
309 END IF;
310
311 RETURN(g_func_currency);
312
313 EXCEPTION
314 WHEN OTHERS THEN
315 RAISE;
316 END GetFunctCurr;
317
318 Function GetConvType(p_conv_type IN varchar2) RETURN VARCHAR2 IS
319
320 l_user_conversion_type varchar2(30);
321
322 BEGIN
323
324 SELECT user_conversion_type
325 INTO l_user_conversion_type
326 FROM gl_daily_conversion_types
327 WHERE conversion_type = p_conv_type;
328
329 return(l_user_conversion_type);
330
331 EXCEPTION
332 WHEN OTHERS THEN
333 RAISE;
334 END GetConvType;
335
336
337 Function GetRate(p_from_curr_code IN varchar2,p_to_curr_code IN varchar2,p_conversion_date DATE,p_conversion_type IN varchar2) RETURN NUMBER IS
338 l_user_conversion_rate Number;
339 l_hash_value NUMBER;
340 p_concat_segments varchar2(100);
341 tab_indx BINARY_INTEGER := 0;
342 found BOOLEAN ;
343
344 BEGIN
345 /*----------------------------------------------------------------+
346 | Search the cache for the concantenated segments. |
347 | Return the conversion_rate if it is in the cache. |
348 | |
349 | If not found in cache, search the linear table (where |
350 | conversion_rate's |
351 | will go if collision on the hash table occurs). |
352 | |
353 | If not found above then get it from gl_currency_api.get_rate |
354 +----------------------------------------------------------------*/
355
356 IF PG_DEBUG in ('Y', 'C') THEN
357 arp_standard.debug('arpcurr.getrate');
358 arp_standard.debug('GetRate: ' || 'p_from_curr_code' || p_from_curr_code);
362 END IF;
359 arp_standard.debug('GetRate: ' || 'p_to_curr_code' || p_to_curr_code);
360 arp_standard.debug('GetRate: ' || 'date ' || to_char(p_conversion_date));
361 arp_standard.debug('GetRate: ' || 'type = ' || p_conversion_type);
363
364 IF (p_from_curr_code IS NOT NULL AND
365 p_to_curr_code IS NOT NULL AND
366 p_conversion_date IS NOT NULL ) THEN
367 -- Bug 2656787: EUR/EUR derived will always have null conversion type
368 -- p_conversion_type IS NOT NULL THEN
369
370 p_concat_segments := p_from_curr_code||'@*?'||p_to_curr_code||'@*?'||p_conversion_date||'@*?'||p_conversion_type;
371
372 l_hash_value := DBMS_UTILITY.get_hash_value(p_concat_segments,
373 1000,
374 25000);
375 found := FALSE;
376 IF pg_getrate_hash_seg_cache.exists(l_hash_value) THEN
377 IF pg_getrate_hash_seg_cache(l_hash_value) = p_concat_segments THEN
378 l_user_conversion_rate := pg_getrate_hash_id_cache(l_hash_value);
379 found := TRUE;
380
381 ELSE --- collision has occurred
382 tab_indx := 1; -- start at top of linear table and search for match
383
384 WHILE ((tab_indx < 25000) AND (not FOUND)) LOOP
385 IF pg_getrate_line_seg_cache(tab_indx) = p_concat_segments THEN
386 l_user_conversion_rate := pg_getrate_line_id_cache(tab_indx);
387 found := TRUE;
388 ELSE
389 tab_indx := tab_indx + 1;
390 END IF;
391 END LOOP;
392 END IF;
393 END IF;
394 IF found THEN
395 RETURN(l_user_conversion_rate);
396 ELSE
397 l_user_conversion_rate := gl_currency_api.get_rate(p_from_curr_code,p_to_curr_code,p_conversion_date,p_conversion_type);
398
399 IF PG_DEBUG in ('Y', 'C') THEN
400 arp_standard.debug('GetRate: ' || 'after call to gl_currency_api');
401 END IF;
402 IF pg_getrate_hash_seg_cache.exists(l_hash_value) then
403 tab_size := tab_size + 1;
404 pg_getrate_line_id_cache(tab_size) := l_user_conversion_rate;
405 pg_getrate_line_seg_cache(tab_size) := p_concat_segments;
406 ELSE
407 pg_getrate_hash_id_cache(l_hash_value) := l_user_conversion_rate;
408 pg_getrate_hash_seg_cache(l_hash_value) := p_concat_segments;
409 pg_getrate_line_id_cache(tab_size) := l_user_conversion_rate;
410 pg_getrate_line_seg_cache(tab_size) := p_concat_segments;
411 END IF;
412 RETURN(l_user_conversion_rate);
413 END IF;
414 END IF;
415
416 EXCEPTION
417 WHEN GL_CURRENCY_API.NO_RATE THEN
418 return -1;
419 WHEN OTHERS THEN
420 RAISE ;
421 END GetRate;
422
423 Function RateExists(p_set_of_books_id IN NUMBER,p_from_curr_code IN varchar2,p_conversion_date DATE,p_conversion_type IN varchar2) RETURN VARCHAR2 IS
424 --
425 l_user_conversion_rate Number;
426
427 BEGIN
428
429 IF (p_from_curr_code IS NULL)
430 THEN
431 return 'X';
432 END IF;
433 l_user_conversion_rate := gl_currency_api.get_rate(p_set_of_books_id,p_from_curr_code,p_conversion_date,p_conversion_type);
434
435 IF (l_user_conversion_rate IS NOT NULL)
436 THEN
437 return 'Y';
438 END IF;
439 EXCEPTION
440 WHEN GL_CURRENCY_API.NO_RATE THEN
441 return 'N';
442 WHEN OTHERS THEN
443 RAISE ;
444 END RateExists;
445
446
447 Function IsFixedRate(p_rec_curr_code IN varchar2,
448 p_funct_curr_code IN varchar2,
449 p_rec_conversion_date DATE,
450 p_trx_curr_code IN varchar2 Default NULL,
451 p_trx_conversion_date DATE Default NULL) RETURN VARCHAR2 IS
452 --
453
454 l_rec_relation varchar2(1);
455 l_trx_relation varchar2(1);
456 l_relation varchar2(1);
457
458
459 BEGIN
460
461
462 IF (p_trx_curr_code IS NOT NULL) THEN
463
464 l_rec_relation := gl_currency_api.is_fixed_rate(p_rec_curr_code,p_funct_curr_code,p_rec_conversion_date);
465 l_trx_relation := gl_currency_api.is_fixed_rate(p_trx_curr_code,p_funct_curr_code,p_trx_conversion_date);
466
467 IF (l_rec_relation = 'Y' and l_trx_relation = 'Y') Then
468 l_relation := 'Y';
469 Else
470 l_relation := 'N';
471 End if;
472
473 ELSE
474
475 l_relation := gl_currency_api.is_fixed_rate(p_rec_curr_code,p_funct_curr_code,p_rec_conversion_date);
476
477 END IF;
478
479 return(l_relation);
480
481 EXCEPTION
482 WHEN GL_CURRENCY_API.INVALID_CURRENCY THEN
483 RETURN 'N';
484 WHEN OTHERS THEN
485 RAISE;
486
487 END IsFixedRate;
488 --
489 --
490 /* Bug 3810649 */
491 PROCEDURE flush_cached_rates IS
492 BEGIN
493 pg_getrate_hash_seg_cache := pg_init_seg_cache;
494 END flush_cached_rates;
495
496 --
497 -- constructor section
498 --
499 PROCEDURE init IS
500 BEGIN
501 SELECT sob.currency_code
502 INTO FunctionalCurrency
503 FROM ar_system_parameters sp,
504 gl_sets_of_books sob
505 WHERE sob.set_of_books_id = sp.set_of_books_id;
506 EXCEPTION
507 WHEN OTHERS THEN
508 arp_standard.debug( 'arpcurr.constructor' );
509 RAISE;
510 END init;
511
512 /* 5885313 - call init in constructor code */
513 BEGIN
514 init;
515 --
516 END arpcurr;
[Home] [Help]