1 PACKAGE BODY hr_currency_pkg AS
2 /* $Header: pyemucnv.pkb 120.3 2008/07/14 08:46:05 ckesanap noship $ */
3 --
4 -- The following type and global declarations are only to be used by
5 -- the add_to_efc_currency_list procedure, efc_convert_number_amount,
6 -- efc_convert_varchar2_amount and efc_is_ncu_currency functions. This
7 -- provides a cache of values required by the EFC conversion process.
8 --
9 TYPE tl_efc_currency_code IS TABLE OF fnd_currencies.currency_code%TYPE
10 INDEX BY BINARY_INTEGER;
11 g_efc_currency_code tl_efc_currency_code;
12 g_rate_currency_code_tab tl_efc_currency_code;
13 g_rate_empty_currency_tab tl_efc_currency_code;
14 --
15 TYPE tl_efc_derive_factor IS TABLE OF fnd_currencies.derive_factor%TYPE
16 INDEX BY BINARY_INTEGER;
17 g_efc_derive_factor tl_efc_derive_factor;
18 --
19 TYPE tl_efc_is_ncu_sysdate IS TABLE OF boolean INDEX BY BINARY_INTEGER;
20 g_efc_is_ncu_sysdate tl_efc_is_ncu_sysdate;
21 --
22 TYPE tl_conversion_rate IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
23 g_rate_conversion_tab tl_conversion_rate;
24 g_rate_empty_rate_tab tl_conversion_rate;
25 --
26 g_efc_eur_mau number default null;
27 --
28 -- The following global variables are used like local variables in
29 -- the add_to_efc_currency_list procedure, efc_convert_number_amount
30 -- and efc_is_ncu_currency functions. Defined as global variables for
31 -- performance reasons.
32 --
33 g_hash_number binary_integer; -- Value used to hash on the currency cache
34 g_rounding number; -- Factor to use in rounding the EUR amount
35 g_derive_effective fnd_currencies.derive_effective%TYPE; -- Value from table
36 g_derive_type fnd_currencies.derive_type%TYPE; -- Value from table
37 g_derive_factor fnd_currencies.derive_factor%TYPE; -- Value from table
38 --
39 -- The following global variables are used for chaching in function
40 -- get_other_rate
41 --
42 g_from_currency gl_daily_rates.from_currency%TYPE := null;
43 g_to_currency gl_daily_rates.to_currency%TYPE := null;
44 g_conversion_date gl_daily_rates.conversion_date%TYPE := null;
45 g_conversion_type gl_daily_rates.conversion_type%TYPE := null;
46 g_conversion_rate gl_daily_rates.conversion_rate%TYPE := null;
47 --
48 -- The following global variables are used for caching in function
49 -- convert_amount
50 --
51 g_rate_conversion_date gl_daily_rates.conversion_date%TYPE;
52 g_rate_to_currency gl_daily_rates.to_currency%TYPE;
53 g_rate_rate_type gl_daily_rates.conversion_type%TYPE;
54 g_rate_rounding NUMBER;
55 g_rate_to_rate NUMBER;
56 g_rate_to_mau NUMBER;
57 g_rate_to_type VARCHAR2(8);
58 --
59 -- --------------------------------------------------------------------------
60 -- |----------------------------< get_info >--------------------------------|
61 -- --------------------------------------------------------------------------
62 --
63 -- Purpose
64 -- Gets the currency type information about given currency.
65 -- Also set the p_invalid_currency flag if the given currency is invalid.
66 --
67 -- Arguments
68 -- p_currency Currency to be checked
69 -- p_eff_date Effective date
70 -- p_conversion_rate Fixed rate for conversion
71 -- p_mau Minimum accountable unit
72 -- p_currency_type Type of currency specified in p_currency
73 --
74 -- ----------------------------------------------------------------------------
75 PROCEDURE get_info(
76 p_currency VARCHAR2,
77 p_eff_date DATE,
78 p_conversion_rate IN OUT NOCOPY NUMBER,
79 p_mau IN OUT NOCOPY NUMBER,
80 p_currency_type IN OUT NOCOPY VARCHAR2) IS
81 --
82 BEGIN
83 -- Get currency information from FND_CURRENCIES table
84 SELECT DECODE(
85 fc.derive_type,
86 'EURO', 'EURO',
87 'EMU', DECODE(
88 SIGN(TRUNC(p_eff_date) - TRUNC(fc.derive_effective)),
89 -1, 'OTHER',
90 'EMU'),
91 'OTHER'),
92 DECODE(
93 fc.derive_type,
94 'EURO', 1,
95 'EMU', fc.derive_factor,
96 'OTHER', -1),
97 NVL(fc.minimum_accountable_unit,
98 POWER(10, (-1 * fc.precision)))
99 INTO p_currency_type,
100 p_conversion_rate,
101 p_mau
102 FROM fnd_currencies fc
103 WHERE fc.currency_code = p_currency;
104 EXCEPTION
105 WHEN NO_DATA_FOUND THEN
106 RAISE invalid_currency;
107 END get_info;
108
109 -- -------------------------------------------------------------------------
110 -- |--------------------------< is_fixed_rate >----------------------------|
111 -- -------------------------------------------------------------------------
112 -- History
113 -- 15/11/00 Kev Koh created
114 --
115 -- Purpose
116 -- Returns if there is a fixed rate between the two currencies.
117 --
118 -- Arguments
119 -- p_from_currency From currency
120 -- p_to_currency To currency
121 -- p_effective_date Effective date
122 --
123 -- -------------------------------------------------------------------------
124 FUNCTION is_fixed_rate(
125 p_from_currency VARCHAR2,
126 p_to_currency VARCHAR2,
127 p_effective_date DATE)
128 RETURN VARCHAR2 IS
129 --
130 --
131 BEGIN
132 -- Check if both currencies are identical
133 IF (p_from_currency = p_to_currency) THEN
134 RETURN 'Y';
135 END IF;
136 --
137 RETURN gl_currency_api.is_fixed_rate(p_from_currency,
138 p_to_currency,
139 p_effective_date
140 ) ;
141 --
142 END is_fixed_rate;
143
144 --
145 -- FUNCTION
146 -- get_euro_code
147 --
148 -- Purpose
149 -- Returns the currency code for the EURO currency. We need to
150 -- select this currency code from fnd_currencies table because
151 -- the currency code for EURO has not been fixed at this time.
152 --
153 -- History
154 -- 24-JUL-97 W Wong Created
155 --
156 -- Arguments
157 -- None.
158 --
159 FUNCTION get_euro_code RETURN VARCHAR2 IS
160 euro_code VARCHAR2(15);
161
162 BEGIN
163 -- Get currency code of the EURO currency
164 SELECT currency_code
165 INTO euro_code
166 FROM FND_CURRENCIES
167 WHERE derive_type = 'EURO';
168
169 return( euro_code );
170
171 EXCEPTION
172 WHEN NO_DATA_FOUND THEN
173 raise INVALID_CURRENCY;
174
175 END get_euro_code;
176
177 -- --------------------------------------------------------------------------
178 -- |------------------------< check_rate_type >-----------------------------|
179 -- --------------------------------------------------------------------------
180 --
181 -- Purpose
182 -- checks that rate type exists in gl_daily_conversion_types
183 --
184 -- returns -2 if User rate (which we won't to disallow)
185 -- -1 if rate type not found
186 -- 1 if record exists
187 --
188 -- History
189 -- 02/02/99 wkerr.uk created
190 --
191 -- Arguments
192 -- p_rate_type The rate type to check
193 --
194 Function check_rate_type(
195 p_rate_type VARCHAR2) RETURN NUMBER IS
196 l_conversion_type gl_daily_conversion_types.conversion_type%type;
197 l_return_count NUMBER;
198 BEGIN
199 --
200 select conversion_type
201 into l_conversion_type
202 from gl_daily_conversion_types
203 where user_conversion_type = p_rate_type;
204 --
205 if l_conversion_type = 'User' then
206 l_return_count := -2;
207 else
208 l_return_count := 1;
209 end if;
210
211 RETURN l_return_count;
212 --
213 EXCEPTION
214 WHEN OTHERS THEN
215 RETURN -1;
216 --
217 END check_rate_type;
218 --
219 -- --------------------------------------------------------------------------
220 -- |-----------------------< get_rate_type >--------------------------------|
221 -- --------------------------------------------------------------------------
222 --
223 -- Purpose
224 -- Returns the rate type given the business group, effective date and
225 -- processing type
226 --
227 -- Current processing types are:-
228 -- P - Payroll Processing
229 -- R - General HRMS reporting
230 -- I - Business Intelligence System
231 --
232 -- History
233 -- 22/01/99 wkerr.uk Created
234 --
235 -- Arguments
236 -- p_business_group_id The business group
237 -- p_effective_date The date for which to return the rate type
238 -- p_processing_type The processing type of which to return the rate
239 --
240 -- Returns null if no rate type found
241 --
242 --
243 FUNCTION get_rate_type (
244 p_business_group_id NUMBER,
245 p_conversion_date DATE,
246 p_processing_type VARCHAR2 ) RETURN VARCHAR2 IS
247 --
248 l_row_name varchar2(30);
249 l_value pay_user_column_instances_f.value%type;
250 l_conversion_type varchar2(30);
251 BEGIN
252 --
253 if p_processing_type = 'P' then
254 l_row_name := 'PAY' ;
255 elsif p_processing_type = 'R' then
256 l_row_name := 'HRMS';
257 elsif p_processing_type = 'I' then
258 l_row_name := 'BIS';
259 else
260 return null;
261 end if;
262 --
263 l_value := hruserdt.get_table_value(p_business_group_id,
264 'EXCHANGE_RATE_TYPES',
265 'Conversion Rate Type' ,
266 l_row_name ,
267 p_conversion_date) ;
268 --
269 -- l_value is a user_conversion_type
270 -- we want to return the conversion_type, hence:
271 --
272 select conversion_type
273 into l_conversion_type
274 from gl_daily_conversion_types
275 where user_conversion_type = l_value;
276 --
277 return l_conversion_type;
278 --
279 EXCEPTION
280 WHEN OTHERS THEN
281 RETURN null;
282 -- Don't know what the problem was with the user the table.
283 -- However don't want to percolate an exception from get_table_value
284 -- Request from payroll team for this to be put in.
285 END get_rate_type;
286
287 -- --------------------------------------------------------------------------
288 -- |-----------------------< get_other_rate >-------------------------------|
289 -- --------------------------------------------------------------------------
290 --
291 -- Purpose
292 -- Returns conversion rate between two currencies where both currencies
293 -- are not the EURO, or EMU currencies.
294 --
295 -- History
296 -- 16-NOV-00 Kev Koh Created
297 -- 11-DEC-02 J Barker Added caching, bug 2678944
298 -- 11-MAY-04 mkataria Trapped NO_DATA_FOUND exception.
299 -- Bug No 3464343.
300 -- Arguments
301 -- p_from_currency From currency
302 -- p_to_currency To currency
303 -- p_conversion_date Conversion date
304 -- p_conversion_type Conversion type
305 --
306 FUNCTION get_other_rate (
307 p_from_currency VARCHAR2,
308 p_to_currency VARCHAR2,
309 p_conversion_date DATE,
310 p_conversion_type VARCHAR2 ) RETURN NUMBER IS
311
312 rate NUMBER;
313
314 BEGIN
315 --
316 if ( g_conversion_date is not null )
317 and ( p_conversion_date = g_conversion_date )
318 and ( p_to_currency = g_to_currency )
319 and ( p_from_currency = g_from_currency )
320 and ( p_conversion_type = g_conversion_type ) then
321 --
322 rate := g_conversion_rate;
323 hr_utility.trace(' cache '||rate);
324 return(rate);
325 --
326 else
327 -- Get conversion rate between the two currencies from GL_DAILY_RATES
328 SELECT conversion_rate
329 INTO rate
330 FROM GL_DAILY_RATES
331 WHERE from_currency = p_from_currency
332 AND to_currency = p_to_currency
333 AND conversion_date = trunc(p_conversion_date)
334 AND conversion_type = p_conversion_type;
335 hr_utility.trace('not cache '||rate);
336 --
337 -- set global cache values
338 --
339 g_from_currency := p_from_currency;
340 g_to_currency := p_to_currency;
341 g_conversion_date := p_conversion_date;
342 g_conversion_type := p_conversion_type;
343 g_conversion_rate := rate;
344 --
345 return(rate);
346 --
347 end if;
348
349 EXCEPTION
350 -- Removed exception gl_currency_api.NO_RATE and trapped
351 -- NO_DATA_FOUND. Bug No 3464343.
352 WHEN NO_DATA_FOUND THEN
353 RAISE gl_currency_api.NO_RATE;
354 END get_other_rate;
355
356 -- ------------------------------------------------------------------------
357 -- |----------------------< get_rate >------------------------------------|
358 -- ------------------------------------------------------------------------
359 --
360 -- Purpose
361 -- Returns the rate between the two currencies for a given conversion
362 -- date and rate type.
363 --
364 -- History
365 -- 22-APR-1998 wkerr.uk Created
366 --
367 --
368 --
369 -- Arguments
370 -- p_from_currency From currency
371 -- p_to_currency To currency
372 -- p_conversion_date Conversion date
373 -- p_rate_type Rate Type
374 --
375 FUNCTION get_rate (
376 p_from_currency VARCHAR2,
377 p_to_currency VARCHAR2,
378 p_conversion_date DATE,
379 p_rate_type VARCHAR2) RETURN NUMBER IS
380
381 BEGIN
382 -- Check if both currencies are identical
383 IF ( p_from_currency = p_to_currency ) THEN
384 return(1);
385 END IF;
386
387 RETURN gl_currency_api.get_rate(p_from_currency,
388 p_to_currency,
389 p_conversion_date,
390 p_rate_type) ;
391
392 END get_rate;
393
394 -- ----------------------------------------------------------------------
395 -- |--------------------< get_rate_sql >--------------------------------|
396 -- ----------------------------------------------------------------------
397 --
398 -- Purpose
399 -- Returns the rate between the two currencies for a given conversion
400 -- date and conversion type by calling get_rate().
401 --
402 -- Return -1 if the NO_RATE exception is raised in get_rate().
403 -- -2 if the INVALID_CURRENCY exception is raised in get_rate().
404 --
405 -- History
406 -- 02-Jun-1998 wkerr.uk Created
407 --
408 --
409 -- Arguments
410 -- p_from_currency From currency
411 -- p_to_currency To currency
412 -- p_conversion_date Conversion date
413 -- p_rate_type Rate Type
414 --
415 FUNCTION get_rate_sql (
416 p_from_currency VARCHAR2,
417 p_to_currency VARCHAR2,
418 p_conversion_date DATE,
419 p_rate_type VARCHAR2)
420 RETURN NUMBER IS
421 rate NUMBER;
422
423 BEGIN
424 -- Call get_rate() using the given parameters
425 rate := get_rate(p_from_currency,
426 p_to_currency,
427 p_conversion_date,
428 p_rate_type );
429 return(rate);
430
431 EXCEPTION
432 WHEN gl_currency_api.NO_RATE THEN
433 rate := -1;
434 return( rate );
435
436 WHEN gl_currency_api.INVALID_CURRENCY THEN
437 rate := -2;
438 return( rate );
439
440 END get_rate_sql;
441
442 -- --------------------------------------------------------------------------
443 -- |------------------------< is_ncu_currency >-----------------------------|
444 -- --------------------------------------------------------------------------
445 -- Purpose
446 -- Returns EMU if currency is a valid NCU code
447
448 FUNCTION is_ncu_currency(
449 p_currency VARCHAR2,
450 p_date DATE)
451 RETURN VARCHAR2 IS
452 --
453 l_from_rate NUMBER;
454 l_from_type VARCHAR2(8);
455 l_from_mau NUMBER;
456 --
457 BEGIN
458 -- Get currency information from the from_currency
459 get_info(p_currency,
460 p_date,
461 l_from_rate,
462 l_from_mau,
463 l_from_type);
464 RETURN l_from_type;
465 END is_ncu_currency;
466 --
467 -- --------------------------------------------------------------------------
468 -- |------------------------< convert_amount >------------------------------|
469 -- --------------------------------------------------------------------------
470 --
471 -- Purpose
472 -- Returns the amount converted from the from currency into the
473 -- to currency for a given conversion date and conversion type.
474 -- The amount returned is rounded to the precision and minimum
475 -- account unit of the to currency.
476 --
477 -- History
478 -- 02-Jun-1998 wkerr.uk Created
479 -- 16-Nov-00 Kev Koh Added round argument
480 -- 11-MAY-04 mkataria Added exception block.Bug No 3464343.
481 -- 14-JUN-04 JTitmas Added caching.
482 -- 06-DEC-05 pgongada Raised the exceptions again Bug # 4530561.
483 --
484 -- Arguments
485 -- p_from_currency From currency
486 -- p_to_currency To currency
487 -- p_conversion_date Conversion date
488 -- p_amount Amount to be converted from the from currency
489 -- into the to currency
490 -- p_rate_type Rate type
491 -- p_round Rounding to decimal places
492
493 FUNCTION convert_amount (
494 p_from_currency VARCHAR2,
495 p_to_currency VARCHAR2,
496 p_conversion_date DATE,
497 p_amount NUMBER,
498 p_rate_type VARCHAR2 DEFAULT NULL,
499 p_round NUMBER DEFAULT NULL)
500 RETURN NUMBER IS
501 --
502 euro_code VARCHAR2(15);
503 to_type VARCHAR2(8);
504 from_type VARCHAR2(8);
505 to_rate NUMBER;
506 from_rate NUMBER;
507 other_rate NUMBER;
508 from_mau NUMBER;
509 to_mau NUMBER;
510 converted_amount NUMBER;
511 rounding NUMBER;
512 hash_number PLS_INTEGER;
513
514 BEGIN
515
516 hash_number := dbms_utility.get_hash_value
517 (p_from_currency
518 ,1
519 ,32768);
520
521 BEGIN
522 /* Can the cache be used? */
523 /* Check cache parameters match */
524 IF (g_rate_conversion_date = p_conversion_date AND
525 g_rate_to_currency = p_to_currency AND
526 g_rate_rate_type = p_rate_type) THEN
527 to_rate := g_rate_to_rate;
528 to_mau := g_rate_to_mau;
529 to_type := g_rate_to_type;
530 /* Check cache hit */
531 IF (g_rate_currency_code_tab(hash_number) = p_from_currency) THEN
532 IF (g_rate_conversion_tab(hash_number) > 0) THEN
533 converted_amount := g_rate_conversion_tab(hash_number) * p_amount;
534 rounding := g_rate_rounding;
535 /* Exception raised previously - return exception code -1 or -2 */
536 ELSE
537 RETURN g_rate_conversion_tab(hash_number);
538 END IF;
539 /* Otherwise refresh cache */
540 ELSE
541 RAISE NO_DATA_FOUND;
542 END IF;
543 /* If cache parameters do not match then reset cache parameters */
544 /* and refresh cache */
545 ELSE
546 -- set parameters
547 g_rate_conversion_tab := g_rate_empty_rate_tab;
548 g_rate_currency_code_tab := g_rate_empty_currency_tab;
549 g_rate_conversion_date := p_conversion_date;
550 g_rate_to_currency := p_to_currency;
551 g_rate_rate_type := p_rate_type;
552 -- get currency information from the to_currency
553 get_info ( p_to_currency, p_conversion_date, to_rate, to_mau, to_type );
554 g_rate_to_rate := to_rate;
555 g_rate_to_mau := to_mau;
556 g_rate_to_type := to_type;
557 -- refresh cache
558 RAISE NO_DATA_FOUND;
559 END IF;
560 EXCEPTION WHEN OTHERS THEN
561 --
562 -- Store currency code in hash table
563 --
564 g_rate_currency_code_tab(hash_number) := p_from_currency;
565 --
566 -- Deal with null amounts
567 IF (p_amount IS NULL) THEN
568 RETURN (NULL);
569 END IF;
570 -- Check if both currencies are identical
571 IF ( p_from_currency = p_to_currency ) THEN
572 RETURN( p_amount );
573 END IF;
574
575 -- Get currency information from the from_currency
576 get_info ( p_from_currency, p_conversion_date, from_rate, from_mau,
577 from_type );
578
579 -- Calculate the conversion rate according to both currency types
580 IF ( from_type = 'EMU' ) THEN
581 IF ( to_type = 'EMU' ) THEN
582 converted_amount := ( p_amount / from_rate ) * to_rate;
583 g_rate_conversion_tab(hash_number) := to_rate / from_rate;
584
585 ELSIF ( to_type = 'EURO' ) THEN
586 converted_amount := p_amount / from_rate;
587 g_rate_conversion_tab(hash_number) := 1 / from_rate;
588
589 ELSIF ( to_type = 'OTHER' ) THEN
590 -- Find out conversion rate from EURO to p_to_currency
591 euro_code := get_euro_code;
592 other_rate := get_other_rate( euro_code, p_to_currency,
593 p_conversion_date,
594 p_rate_type );
595
596 -- Get conversion amt by converting EMU -> EURO -> OTHER
597 converted_amount := ( p_amount / from_rate ) * other_rate;
598 g_rate_conversion_tab(hash_number) := other_rate / from_rate;
599 END IF;
600
601 ELSIF ( from_type = 'EURO' ) THEN
602 IF ( to_type = 'EMU' ) THEN
603 converted_amount := p_amount * to_rate;
604 g_rate_conversion_tab(hash_number) := to_rate;
605
606 ELSIF ( to_type = 'EURO' ) THEN
607 -- We should never comes to this case as it should be
608 -- caught when we check if both to and from currency
609 -- is the same at the beginning of this function
610 converted_amount := p_amount;
611 g_rate_conversion_tab(hash_number) := 1;
612
613 ELSIF ( to_type = 'OTHER' ) THEN
614 other_rate := get_other_rate( p_from_currency,
615 p_to_currency,
616 p_conversion_date,
617 p_rate_type );
618 converted_amount := p_amount * other_rate;
619 g_rate_conversion_tab(hash_number) := other_rate;
620 END IF;
621
622 ELSIF ( from_type = 'OTHER' ) THEN
623 IF ( to_type = 'EMU' ) THEN
624 -- Find out conversion rate from p_from_currency to EURO
625 euro_code := get_euro_code;
626 other_rate := get_other_rate( p_from_currency, euro_code,
627 p_conversion_date,
628 p_rate_type );
629
630 -- Get conversion amt by converting OTHER -> EURO -> EMU
631 converted_amount := ( p_amount * other_rate ) * to_rate;
632 g_rate_conversion_tab(hash_number) := other_rate * to_rate;
633
634 ELSIF ( to_type = 'EURO' ) THEN
635 other_rate := get_other_rate( p_from_currency, p_to_currency,
636 p_conversion_date,
637 p_rate_type );
638 converted_amount := p_amount * other_rate;
639 g_rate_conversion_tab(hash_number) := other_rate;
640
641 ELSIF ( to_type = 'OTHER' ) THEN
642 other_rate := get_other_rate( p_from_currency, p_to_currency,
643 p_conversion_date,
644 p_rate_type );
645 converted_amount := p_amount * other_rate;
646 g_rate_conversion_tab(hash_number) := other_rate;
647 END IF;
648 END IF;
649
650 -- Check if we are rounding to a certain no. of DP
651 IF p_round IS NOT NULL THEN
652 -- hr_utility.set_location('Not null', 10);
653 rounding := POWER(10, (-1 * p_round));
654 ELSE
655 -- hr_utility.set_location('Null', 20);
656 -- Use std no. of decimal places
657 rounding := to_mau;
658 END IF;
659
660 g_rate_rounding := rounding;
661
662 END;
663
664 -- Rounding to the correct precision and minumum accountable units
665 RETURN (ROUND(converted_amount / rounding) * rounding);
666
667 -- Added exception block. Bug No 3464343.
668 EXCEPTION
669 -- Bug#4530561. Instead of returning the values raised the
670 -- same exceptions.
671 WHEN invalid_currency THEN
672 g_rate_conversion_tab(hash_number) := -2;
673 RAISE gl_currency_api.invalid_currency;
674
675 WHEN gl_currency_api.NO_RATE THEN
676 g_rate_conversion_tab(hash_number) := -1;
677 RAISE gl_currency_api.NO_RATE;
678
679 END convert_amount;
680
681 -- --------------------------------------------------------------------------
682 -- |------------------------< convert_amount_sql >--------------------------|
683 -- --------------------------------------------------------------------------
684 --
685 -- Purpose
686 -- Returns the amount converted from the from currency into the
687 -- to currency for a given conversion date and conversion type by
688 -- calling convert_amount().
689 -- The amount returned is rounded to the precision and minimum
690 -- account unit of the to currency.
691 --
692 -- Return -1 if the NO_RATE exception is raised in get_rate().
693 -- -2 if the INVALID_CURRENCY exception is raised in get_rate().
694 --
695 -- History
696 -- 02-Jun-1998 wkerr.uk Created
697 -- 16-NOV-00 Kev Koh Added rounding argument
698 --
699 --
700 -- Arguments
701 -- p_from_currency From currency
702 -- p_to_currency To currency
703 -- p_conversion_date Conversion date
704 -- p_amount Amount to be converted from the from currency
705 -- into the to currency
706 -- p_rate_type Rate type
707 -- p_round Rounding decimal places
708 --
709 FUNCTION convert_amount_sql (
710 p_from_currency VARCHAR2,
711 p_to_currency VARCHAR2,
712 p_conversion_date DATE,
713 p_amount NUMBER,
714 p_rate_type VARCHAR2 DEFAULT NULL,
715 p_round NUMBER DEFAULT NULL) RETURN NUMBER IS
716
717 converted_amount NUMBER;
718 BEGIN
719 converted_amount := convert_amount( p_from_currency, p_to_currency,
720 p_conversion_date,
721 p_amount, p_rate_type, null);
722
723 -- Bug 7111120
724 IF converted_amount = -1 THEN
725 converted_amount := -1.000001;
726 ELSIF converted_amount = -2 THEN
727 converted_amount := -2.000001;
728 END if;
729
730 return(converted_amount);
731
732 EXCEPTION
733 WHEN gl_currency_api.NO_RATE THEN
734 converted_amount := -1;
735 return( converted_amount );
736
737 WHEN gl_currency_api.INVALID_CURRENCY THEN
738 converted_amount := -2;
739 return( converted_amount );
740
741 END convert_amount_sql;
742
743 -- --------------------------------------------------------------------------
744 -- |-----------------------< is_ncu_currency_sql >--------------------------|
745 -- --------------------------------------------------------------------------
746 -- Purpose
747 -- Returns EMU if currency is a valid NCU code
748
749 FUNCTION is_ncu_currency_sql(
750 p_currency VARCHAR2,
751 p_date DATE)
752 RETURN VARCHAR2 IS
753 BEGIN
754 RETURN (is_ncu_currency(p_currency, p_date));
755 EXCEPTION
756 WHEN invalid_currency THEN
757 RETURN (-2);
758 END is_ncu_currency_sql;
759 --
760 -- -----------------------------------------------------------------------------
761 -- |-------------------------< add_to_efc_currency_list >----------------------|
762 -- -----------------------------------------------------------------------------
763 --
764 procedure add_to_efc_currency_list
765 (p_from_currency in varchar2
766 ,p_hash_number in number
767 ) is
768 --
769 cursor csr_fnd_cur is
770 select fcu.derive_type
771 , fcu.derive_effective
772 , fcu.derive_factor
773 from fnd_currencies fcu
774 where fcu.currency_code = p_from_currency;
775 --
776 cursor csr_fnd_eur is
777 select NVL(fcu.minimum_accountable_unit, POWER(10, (-1 * fcu.precision)))
778 from fnd_currencies fcu
779 where fcu.currency_code = 'EUR';
780 --
781 -- This function uses the g_derive_effective, g_derive_type
782 -- and g_derive_factor variables as if they were local variables.
783 -- These variables are defined globally to reduce PL/SQL
784 -- calling overheads.
785 --
786 begin
787 open csr_fnd_cur;
788 fetch csr_fnd_cur into g_derive_type
789 , g_derive_effective
790 , g_derive_factor;
791 if csr_fnd_cur%notfound then
792 close csr_fnd_cur;
793 raise invalid_currency;
794 else
795 close csr_fnd_cur;
796 -- Add details to currency list PL/SQL table
797 g_efc_currency_code(p_hash_number) := p_from_currency;
798 g_efc_derive_factor(p_hash_number) := g_derive_factor;
799 -- Work out if the currency is an NCU as of sysdate
800 if g_derive_type = 'EMU' and
801 trunc(sysdate) >= trunc(g_derive_effective) then
802 g_efc_is_ncu_sysdate(p_hash_number) := TRUE;
803 else
804 g_efc_is_ncu_sysdate(p_hash_number) := FALSE;
805 end if;
806 --
807 end if;
808 --
809 -- If this procedure has been called for the first time during this database
810 -- session the standard number of decimal places for the Euro currency will
811 -- be unknown. Set the global variable with these details so the value is
812 -- available for use by the efc_convert_number_amount function.
813 --
814 if g_efc_eur_mau is null then
815 open csr_fnd_eur;
816 fetch csr_fnd_eur into g_efc_eur_mau;
817 if csr_fnd_eur%notfound then
818 close csr_fnd_eur;
819 raise invalid_currency;
820 end if;
821 close csr_fnd_eur;
822 end if;
823 end add_to_efc_currency_list;
824 --
825 -- -----------------------------------------------------------------------------
826 -- |------------------------< efc_convert_number_amount >----------------------|
827 -- -----------------------------------------------------------------------------
828 --
829 function efc_convert_number_amount
830 (p_from_currency in varchar2
831 ,p_amount in number
832 ,p_round in number default null
833 ) return number is
834 --
835 -- This function uses the g_hash_number and g_rounding global
836 -- variables as if they were local variables. These variables
837 -- are defined globally to reduce PL/SQL calling overheads.
838 --
839 begin
840 if p_amount is null then
841 return null;
842 end if;
843 --
844 -- The cache searching logic in this function must be kept in sync
845 -- with the corresponding logic in the efc_is_ncu_currency
846 -- function. The code has been duplicated to avoid the overhead
847 -- of a second PL/SQL function call.
848 --
849 -- Search for p_from_currency in the currency cache in this package
850 --
851 g_hash_number := dbms_utility.get_hash_value
852 (p_from_currency
853 ,1
854 ,32768
855 );
856 --
857 begin
858 if g_efc_currency_code(g_hash_number) <> p_from_currency then
859 -- Hash value has mapped onto another currency which was in the cache.
860 -- Re-populate the cache with details required for this currency.
861 add_to_efc_currency_list
862 (p_from_currency => p_from_currency
863 ,p_hash_number => g_hash_number
864 );
865 end if;
866 exception
867 when no_data_found then
868 -- Hash value did not map onto an entry in the currency list, so
869 -- the currency cannot be in the cache. Populate the cache, so
870 -- the values are available for the next call to this function
871 -- or efc_is_ncu_currency.
872 add_to_efc_currency_list
873 (p_from_currency => p_from_currency
874 ,p_hash_number => g_hash_number
875 );
876 end;
877 --
878 -- If the p_from_currency is not an NCU as of sysdate then
879 -- return the amount passed into this function.
880 --
881 if not g_efc_is_ncu_sysdate(g_hash_number) then
882 return p_amount;
883 end if;
884 --
885 -- Otherwise the p_from_currency is an NCU as of sysdate and the
886 -- p_amount value should be converted to EUR using the derived
887 -- factor from the FND_CURRENCIES table. Also round the converted
888 -- amount to the number of decimal places passed into this function
889 -- or the standard number of decimal places for the EUR currency
890 -- and minumum accountable units.
891 --
892 if p_round is not null then
893 g_rounding := POWER(10, (-1 * p_round));
894 return (round((p_amount / g_efc_derive_factor(g_hash_number))
895 / g_rounding) * g_rounding);
896 else
897 return (round((p_amount / g_efc_derive_factor(g_hash_number))
898 / g_efc_eur_mau) * g_efc_eur_mau);
899 end if;
900 --
901 end efc_convert_number_amount;
902 --
903 -- -----------------------------------------------------------------------------
904 -- |-----------------------< efc_get_derived_factor >--------------------------|
905 -- -----------------------------------------------------------------------------
906 --
907 function efc_get_derived_factor
908 (p_from_currency in varchar2
909 ) return number is
910 begin
911 --
912 -- The cache searching logic in this function must be kept in sync
913 -- with the corresponding logic in the efc_is_ncu_currency
914 -- function. The code has been duplicated to avoid the overhead
915 -- of a second PL/SQL function call.
916 --
917 -- Search for p_from_currency in the currency cache in this package
918 --
919 g_hash_number := dbms_utility.get_hash_value
920 (p_from_currency
921 ,1
922 ,32768
923 );
924 --
925 begin
926 if g_efc_currency_code(g_hash_number) <> p_from_currency then
927 -- Hash value has mapped onto another currency which was in the cache.
928 -- Re-populate the cache with details required for this currency.
929 add_to_efc_currency_list
930 (p_from_currency => p_from_currency
931 ,p_hash_number => g_hash_number
932 );
933 end if;
934 exception
935 when no_data_found then
936 -- Hash value did not map onto an entry in the currency list, so
937 -- the currency cannot be in the cache. Populate the cache, so
938 -- the values are available for the next call to this function
939 -- or efc_is_ncu_currency.
940 add_to_efc_currency_list
941 (p_from_currency => p_from_currency
942 ,p_hash_number => g_hash_number
943 );
944 end;
945 --
946
947 return(g_efc_derive_factor(g_hash_number));
948 --
949 end efc_get_derived_factor;
950 --
951 -- -----------------------------------------------------------------------------
952 -- |-----------------------< efc_convert_varchar2_amount >---------------------|
953 -- -----------------------------------------------------------------------------
954 --
955 function efc_convert_varchar2_amount
956 (p_from_currency in varchar2
957 ,p_amount in varchar2
958 ,p_round in number default null
959 ) return varchar2 is
960 --
961 begin
962 -- Note: This function does not call changeformat or checkformat because
963 -- it can be assumed the values selected from a table have already
964 -- been validated and are known to be valid money numbers.
965 --
966 -- Convert amount to EUR currency
967 --
968 return to_char(efc_convert_number_amount
969 (p_from_currency => p_from_currency
970 ,p_amount => to_number(p_amount)
971 ,p_round => p_round
972 ));
973 --
974 end efc_convert_varchar2_amount;
975 --
976 -- -----------------------------------------------------------------------------
977 -- |---------------------------< efc_is_ncu_currency >-------------------------|
978 -- -----------------------------------------------------------------------------
979 --
980 function efc_is_ncu_currency
981 (p_currency in varchar2
982 ) return boolean is
983 --
984 -- This function uses the g_hash_number variable as if it was
985 -- a local variable. This variable is defined globally to
986 -- reduce PL/SQL calling overheads.
987 --
988 begin
989 --
990 -- The cache searching logic in this function must be kept in sync
991 -- with the corresponding logic in the efc_convert_number_amount
992 -- function. The code has been duplicated to avoid the overhead
993 -- of a second PL/SQL function call.
994 --
995 -- Search for p_currency in the currency cache in this package
996 --
997 g_hash_number := dbms_utility.get_hash_value
998 (p_currency
999 ,1
1000 ,32768
1001 );
1002 --
1003 begin
1004 if g_efc_currency_code(g_hash_number) <> p_currency then
1005 -- Hash value has mapped onto another currency which was in the cache.
1006 -- Re-populate the cache with details required for this currency.
1007 add_to_efc_currency_list
1008 (p_from_currency => p_currency
1009 ,p_hash_number => g_hash_number
1010 );
1011 end if;
1012 exception
1013 when no_data_found then
1014 -- Hash value did not map onto an entry in the currency list, so
1015 -- the currency cannot be in the cache. Populate the cache, so
1016 -- the values are available for the next call to this function
1017 -- or efc_convert_number_amount.
1018 add_to_efc_currency_list
1019 (p_from_currency => p_currency
1020 ,p_hash_number => g_hash_number
1021 );
1022 end;
1023 --
1024 -- Return the Is NCU status flag from the cache
1025 --
1026 return g_efc_is_ncu_sysdate(g_hash_number);
1027 --
1028 end efc_is_ncu_currency;
1029 --
1030 END hr_currency_pkg;
[Home] [Help]