1 PACKAGE BODY gl_currency_api AS
2 /* $Header: glustcrb.pls 120.7 2005/05/05 01:44:15 kvora ship $ */
3
4 ---
5 --- PRIVATE VARIABLES
6 ---
7
8 ---
9 --- PRIVATE FUNCTIONS
10 ---
11
12 --
13 -- Procedure
14 -- get_info
15 --
16 -- Purpose
17 -- Gets the currency type information about given currency.
18 -- Also set the x_invalid_currency flag if the given currency is invalid.
19 --
20 -- History
21 -- 15-JUL-97 W Wong Created
22 --
23 -- Arguments
24 -- x_currency Currency to be checked
25 -- x_eff_date Effecitve date
26 -- x_conversion_rate Fixed rate for conversion
27 -- x_mau Minimum accountable unit
28 -- x_currency_type Type of currency specified in x_currency
29 --
30 PROCEDURE get_info(
31 x_currency VARCHAR2,
32 x_eff_date DATE,
33 x_conversion_rate IN OUT NOCOPY NUMBER,
34 x_mau IN OUT NOCOPY NUMBER,
35 x_currency_type IN OUT NOCOPY VARCHAR2 ) IS
36
37 BEGIN
38 -- Get currency information from FND_CURRENCIES table
39 SELECT decode( derive_type,
40 'EURO', 'EURO',
41 'EMU', decode( sign( trunc(x_eff_date) -
42 trunc(derive_effective)),
43 -1, 'OTHER',
44 'EMU'),
45 'OTHER' ),
46 decode( derive_type, 'EURO', 1,
47 'EMU', derive_factor,
48 'OTHER', -1 ),
49 nvl( minimum_accountable_unit, power( 10, (-1 * precision)))
50 INTO x_currency_type,
51 x_conversion_rate,
52 x_mau
53 FROM FND_CURRENCIES
54 WHERE currency_code = x_currency;
55
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58 raise INVALID_CURRENCY;
59
60 END get_info;
61
62 --
63 -- Function
64 -- get_fixed_conv_rate
65 --
66 -- Purpose
67 -- Returns Fixed conversion rate between two currencies
68 -- where both currencies are not the EURO, or EMU currencies.
69 -- This routine hits the GL_FIXED_CONV_RATES.
70 --
71 -- History
72 -- 01-Mar-2005 Srini Pala Created
73 --
74 -- Arguments
75 -- x_from_currency From currency
76 -- x_to_currency To currency
77 -- x_conversion_date Conversion date
78 -- x_conversion_type Conversion type
79 -- l_fixed_conv_rate Fixed conversion rate.
80 --
81
82 PROCEDURE get_fixed_conv_rate(x_from_currency IN OUT NOCOPY VARCHAR2,
83 x_to_currency IN OUT NOCOPY VARCHAR2,
84 x_conversion_date Date,
85 x_conversion_type VARCHAR2 ,
86 x_fixed_conv_rate OUT NOCOPY NUMBER) IS
87
88 direct_from_fix_rate NUMBER;
89 l_fix_rate NUMBER;
90
91 inverse_to_fix_rate NUMBER;
92 new_from_currency VARCHAR2(15);
93 new_to_currency VARCHAR2(15);
94 l_fixed_relation VARCHAR2(15);
95 l_currency_from VARCHAR2(15);
96 l_currency_to VARCHAR2(15);
97 l_continue BOOLEAN := FALSE;
98 l_final_rate NUMBER;
99
100 CURSOR is_there_fixed_rate(l_from_curr VARCHAR2,
101 l_to_curr VARCHAR2,
102 l_eff_date DATE) IS
103 SELECT old_currency,
104 replacement_currency,
105 fixed_conversion_rate
106 FROM GL_FIXED_CONV_RATES
107 WHERE old_currency IN (l_from_curr,l_to_curr)
108 AND effective_start_date <= trunc(l_eff_date)
109 ORDER BY DECODE(old_currency, l_from_curr,0,1);
110 BEGIN
111 l_currency_from := x_from_currency;
112 l_currency_to := x_to_currency;
113 direct_from_fix_rate := 1;
114 inverse_to_fix_rate := 1;
115
116 IF(x_from_currency = x_to_currency) THEN
117 x_fixed_conv_rate := 1;
118 END IF;
119
120 /*-----------------------------------------------------------------------+
121 This routine should check whether there is a fixed rate relationship
122 exist between the from currency and the to_currency in the
123 GL_FIXED_CONV_RATES table.
124 Some EUROPEAN countries are getting rid of ending zero's
125 from their currency. In this case those countries define
126 a fixed rate relationship between the old currency and the new
127 replacement currency starting from an effective date.
128 For example Turky replaced thier old currency with the new currency
129 and effective from 01-01-2005.
130 If we want to find a conversion between old Turky currency and
131 USD, then we need to follow the following rule after 01-01-2005.
132 First find rate between
133 OLD Turky Currency -> New Turky Currency in GL_FIXED_CONV_RATES
134 Then
135 New Turky Currency -> USD in GL_DAILY_RATES table.
136 The following code added to do this job.
137 +-----------------------------------------------------------------------*/
138
139 /* ********************************************************************+
140 | A few possible different scenarios |
141 | The rate may be calculated between two currencies as follows |
142 | |
143 | 1) Old Currency to French Franks |
144 | Old Currency -> New Currency from the GL_FIXED_CONV_RATES table |
145 | New Currency -> EURO from the GL_DAILY_RATES |
146 | EURO -> FRENCH FRANK fixed rate from the FND_CURRENCIES |
147 | |
148 | 2) USD to New Currency |
149 | USD -> New CURRENCY from the GL Daily Rates Table. |
150 | |
151 | 3) USD to Old Currency |
152 | USD -> New CURRENCY from the GL Daily Rates Table. |
153 | New curency -> Old Currency fixed rate |
154 | from GL Fixed Conv Rates |
155 | |
156 | 4) Old Currency to CAD |
157 | Old Currency -> New Currency from |
158 | the GL_FIXED_CONV_RATES table |
159 | New Currency -> CAD from the GL_DAILY_RATES |
160 | |
161 +######################################################################*/
162 -- Check the passed currencies have any fixed relationships exist or not.
163 -- This check avoids unnecssary processing if there is no fixed
164 -- rate relationship.
165
166 OPEN is_there_fixed_rate(l_currency_from,
167 l_currency_to,
168 x_conversion_date);
169
170 FETCH is_there_fixed_rate INTO new_from_currency,
171 new_to_currency,
172 l_fix_rate;
173
174 IF (is_there_fixed_rate%FOUND) THEN
175 l_continue := TRUE;
176 ELSE
177 l_continue := FALSE;
178 direct_from_fix_rate := 1;
179 inverse_to_fix_rate := 1;
180 x_fixed_conv_rate := 1;
181 CLOSE is_there_fixed_rate;
182 END IF;
183
184 /*---------------------------------------------------------------------+
185 | First try finding a fixed conversion from the old currency column. |
186 | If there is a rate, then it is fine, otherwise try to find the |
187 | inverse rate by querying on replacement currency. |
188 | If there exists a fixed conversion relationship, then set the |
189 | x_from_currency to retrive the rate between the new currency to the |
190 | x_to_currency from GL_DAILY_RATES table. |
191 | |
192 | ************THIS PART HANDLES ON THE FROM CURRENCY SIDE.********* |
193 | |
194 | IF x_from_currency = OLD TURKY currency |
195 | Then it finds OLD TURKY CURRENCY to NEW TURKEY CURRENCY |
196 | If x_to_currency = NEW TURKY CURRENCY then this part |
197 | alone returns the final rate. |
198 | Else It will find the other part of the rate from |
199 | GL Daily Rates table. |
200 | New Turky Currency to USD |
201 +---------------------------------------------------------------------*/
202
203 IF ( l_continue) THEN
204
205 IF (l_currency_from = new_from_currency) THEN
206 direct_from_fix_rate:= l_fix_rate;
207 x_from_currency := new_to_currency;
208
209 ELSIF (l_currency_to = new_from_currency) THEN
210 inverse_to_fix_rate := 1/l_fix_rate;
211 x_to_currency := new_to_currency;
212
213 END IF;
214
215 /*---------------------------------------------------------------------+
216 | This part handles when we are trying to find a rate from |
217 | one coutries old currency to another coutries old currency. |
218 | For example Find a rate between Old1->Old2 |
219 | Then Old1-> NEW1 from GL_FIXED_CONV_RATES, |
220 | New1->New2 from GL_DAILY_RATE |
221 | New2->Old2 from GL_FIXED_CONV_RATES The following code |
222 | handles this calculation. |
223 +---------------------------------------------------------------------*/
224 -- Fetch the second row if there exists one.
225
226 FETCH is_there_fixed_rate INTO new_from_currency,
227 new_to_currency,
228 l_fix_rate;
229
230 If (is_there_fixed_rate%FOUND) THEN
231
232
233 IF (l_currency_to = new_from_currency) THEN
234 inverse_to_fix_rate :=1/l_fix_rate;
235 x_to_currency := new_to_currency;
236
237 END IF;
238
239 End If;
240
241
242 CLOSE is_there_fixed_rate;
243
244 l_final_rate := (direct_from_fix_rate* inverse_to_fix_rate);
245 x_fixed_conv_rate := l_final_rate;
246
247 END IF; -- End of if (l_continue)
248 END;
249
250
251 --
252 -- Function
253 -- get_other_rate
254 --
255 -- Purpose
256 -- Returns conversion rate between two currencies where both currencies
257 -- are not the EURO, or EMU currencies.
258 --
259 -- History
260 -- 15-JUL-97 W Wong Created
261 --
262 -- 14-Mar-2005 Srini Pala Fixed rate relationship enhancements
263 -- are made.
264 -- Arguments
265 -- x_from_currency From currency
266 -- x_to_currency To currency
267 -- x_conversion_date Conversion date
268 -- x_conversion_type Conversion type
269 --
270 FUNCTION get_other_rate (
271 x_from_currency VARCHAR2,
272 x_to_currency VARCHAR2,
273 x_conversion_date Date,
274 x_conversion_type VARCHAR2 ) RETURN NUMBER IS
275
276 rate NUMBER;
277 l_from_currency VARCHAR2(15);
278 l_to_currency VARCHAR2(15);
279 l_fix_rate NUMBER;
280
281 BEGIN
282
283 l_from_currency := x_from_currency;
284 l_to_currency := x_to_currency;
285
286 IF(x_from_currency = x_to_currency) THEN
287 return (1);
288 END IF;
289
290 -- Get the Fixed conversion rate if there exists one.
291
292 get_fixed_conv_rate(l_from_currency,
293 l_to_currency,
294 x_conversion_date,
295 x_conversion_type,
296 l_fix_rate);
297
298
299 IF (l_from_currency = l_to_currency) THEN
300
301 rate := 1;
302
303 ELSE
304 -- Get conversion rate between the two currencies from GL_DAILY_RATES
305 SELECT conversion_rate
306 INTO rate
307 FROM GL_DAILY_RATES
308 WHERE from_currency = l_from_currency
309 AND to_currency = l_to_currency
310 AND conversion_date = trunc(x_conversion_date)
311 AND conversion_type = x_conversion_type;
312 END IF;
313
314 return(l_fix_rate*rate );
315
316 EXCEPTION
317 WHEN NO_DATA_FOUND THEN
318 raise NO_RATE;
319 END get_other_rate;
320
321
322 --
323 -- Function
324 -- get_other_closest_rate
325 --
326 -- Purpose
327 -- Returns conversion rate between two currencies where both currencies
328 -- are not the EURO, or EMU currencies.
329 --
330 -- History
331 -- 15-JUL-97 W Wong Created
332 --
333 -- 14-MAR-2005 Srini Pala Added Fixed conversion rate relationship
334 -- logic.
338 -- x_from_currency From currency
335 -- More detailed explanation is in the
336 -- get_fixed_conv_rate() routine.
337 -- Arguments
339 -- x_to_currency To currency
340 -- x_conversion_date Conversion date
341 -- x_conversion_type Conversion type
342 -- x_max_roll_days Number of days to rollback for a rate
343 --
344 FUNCTION get_other_closest_rate (
345 x_from_currency VARCHAR2,
346 x_to_currency VARCHAR2,
347 x_conversion_date Date,
348 x_conversion_type VARCHAR2,
349 x_max_roll_days NUMBER ) RETURN NUMBER IS
350
351 -- This cursor finds the latest rate defined between the given two
352 -- currencies using x_conversion_type within the period between
353 -- x_max_roll_days prior to x_conversion_date AND x_conversion_date.
354 CURSOR closest_rate_curr(g_from_currency VARCHAR2,
355 g_to_currency VARCHAR2) IS
356 SELECT conversion_rate
357 FROM GL_DAILY_RATES
358 WHERE from_currency = g_from_currency
359 AND to_currency = g_to_currency
360 AND conversion_type = x_conversion_type
361 AND conversion_date BETWEEN
362 ( decode( sign (x_max_roll_days),
363 1, trunc(x_conversion_date) - x_max_roll_days,
364 -1, trunc(to_date('1000/01/01', 'YYYY/MM/DD'))))
365 AND trunc(x_conversion_date)
366 ORDER BY conversion_date DESC;
367
368 rate NUMBER;
369 l_from_curr VARCHAR2(15);
370 l_to_curr VARCHAR2(15);
371 l_fixed_conv_rate NUMBER;
372
373 BEGIN
374
375 l_from_curr := x_from_currency;
376 l_to_curr := x_to_currency;
377
378 -- Try to search for a rate on the given conversion date
379 rate := get_other_rate( x_from_currency,
380 x_to_currency,
381 x_conversion_date,
382 x_conversion_type );
383
384 return( rate );
385
386 EXCEPTION
387 -- No conversion rate was found on the given conversion date.
388 -- Try to search for the latest conversion rate with a prior conversion
389 -- date then x_conversion_date.
390 WHEN NO_RATE THEN
391 IF ( x_max_roll_days = 0 ) THEN
392 -- Do not search backwards for the conversion rate.
393 raise NO_RATE;
394
395 ELSE
396 -- Get the Fixed conversion rate if there exists one.
397
398 get_fixed_conv_rate(l_from_curr,
399 l_to_curr,
400 x_conversion_date,
401 x_conversion_type,
402 l_fixed_conv_rate);
403
404 IF (l_from_curr <> l_to_curr) THEN
405 -- Search backwards for a conversion rate with the given currencies
406 -- and conversion type.
407 OPEN closest_rate_curr(l_from_curr, l_to_curr);
408 FETCH closest_rate_curr INTO rate;
409
410 IF NOT closest_rate_curr%FOUND THEN
411 raise NO_RATE;
412 ELSE
413 return( rate * l_fixed_conv_rate);
414 END IF;
415 ELSE
416 return (l_fixed_conv_rate);
417 END IF;
418
419 END IF;
420
421 END get_other_closest_rate;
422
423
424 ---
425 --- PUBLIC FUNCTIONS
426 ---
427
428 --
429 -- Function
430 -- is_fixed_rate
431 --
432 -- Purpose
433 -- Returns if there is a fixed rate between the two currencies.
434 --
435 -- History
436 -- 15-JUL-97 W Wong Created
437 -- 14-MAR-2005 Srini Pala Added Fixed conversion rate relationship
438 -- logic.
439 -- More detailed explanation is in
440 -- the get_fixed_conv_rate() routine.
441 --
442 -- Arguments
443 -- x_from_currency From currency
444 -- x_to_currency To currency
445 -- x_effective_date Effective date
446 --
447 FUNCTION is_fixed_rate (
448 x_from_currency VARCHAR2,
449 x_to_currency VARCHAR2,
450 x_effective_date DATE ) RETURN VARCHAR2 IS
451
452 to_type VARCHAR2(8);
453 from_type VARCHAR2(8);
454 rate NUMBER; -- Value ignored in this function
455 mau NUMBER; -- Value ignored in this function
456
457 /* Fixed conversion relationships enhancements start */
458 CURSOR is_there_fix_rate(l_from_curr VARCHAR2,
459 l_to_curr VARCHAR2,
460 l_eff_date DATE) IS
461 SELECT 'EXIST'
462 FROM GL_FIXED_CONV_RATES
463 WHERE old_currency IN (l_from_curr,l_to_curr)
464 AND ((old_currency = l_from_curr
465 AND replacement_currency = l_to_curr)
466 OR (replacement_currency = l_from_curr
467 AND old_currency = l_to_curr))
468 AND effective_start_date <= trunc(l_eff_date)
469 AND ROWNUM =1;
470 l_fix_rate VARCHAR2(10);
471
472 /* Fixed conversion relationships enhancements End */
473
474
475 BEGIN
479 END IF;
476 -- Check if both currencies are identical
477 IF ( x_from_currency = x_to_currency ) THEN
478 return 'Y';
480
481 -- Get currency information of the x_from_currency
482 get_info( x_from_currency, x_effective_date, rate, mau, from_type );
483
484 -- Get currency information of the x_to_currency
485 get_info( x_to_currency, x_effective_date, rate, mau, to_type );
486
487 -- Check if there is a fixed rate between the two given currencies
488 IF (( from_type IN ('EMU', 'EURO')) AND
489 ( to_type IN ('EMU', 'EURO'))) THEN
490 return 'Y';
491
492 ELSE
493 /* Fixed conversion relationships enhancements start */
494
495 OPEN is_there_fix_rate (x_from_currency,
496 x_to_currency,
497 x_effective_date);
498 FETCH is_there_fix_rate INTO l_fix_rate;
499
500 IF (is_there_fix_rate%FOUND) THEN
501
502 return 'Y';
503 /* Fixed conversion relationships enhancements end */
504 ELSE
505 return 'N';
506 END IF;
507 END IF;
508
509 END is_fixed_rate;
510
511
512 --
513 -- Procedure
514 -- get_relation
515 --
516 -- Purpose
517 -- Returns the relationship between the two currencies given.
518 -- Also check if there is a fixed rate between the two currencies
519 -- on the effective date.
520 --
521 -- History
522 -- 15-JUL-97 W Wong Created
523 --
524 -- 14-MAR-2005 Srini Pala Added Fixed conversion rate relationship
525 -- logic.
526 -- More detailed explanation is in the
527 -- get_fixed_conv_rate() routine.
528 -- Arguments
529 -- x_from_currency From currency
530 -- x_to_currency To currency
531 -- x_effective_date Effective date
532 -- x_fixed_rate TRUE if there is a fixed rate between the
533 -- currencies on the effective date;
534 -- FALSE otherwise
535 -- x_relationship Relationship between the two currencies
536 --
537 PROCEDURE get_relation(
538 x_from_currency VARCHAR2,
539 x_to_currency VARCHAR2,
540 x_effective_date DATE,
541 x_fixed_rate IN OUT NOCOPY BOOLEAN,
542 x_relationship IN OUT NOCOPY VARCHAR2 ) IS
543
544 to_type VARCHAR2(8);
545 from_type VARCHAR2(8);
546 rate NUMBER; -- Value ignored in this function
547 mau NUMBER; -- Value ignored in this function
548
549 /* Fixed conversion relationships enhancements start */
550
551 CURSOR is_there_fix_relation(l_from_curr VARCHAR2,
552 l_to_curr VARCHAR2,
553 l_eff_date DATE) IS
554 SELECT 'EXIST'
555 FROM GL_FIXED_CONV_RATES
556 WHERE old_currency IN (l_from_curr,l_to_curr)
557 AND ((old_currency = l_from_curr
558 AND replacement_currency = l_to_curr)
559 OR (replacement_currency = l_from_curr
560 AND old_currency = l_to_curr))
561 AND effective_start_date <= trunc(l_eff_date)
562 AND ROWNUM =1;
563 l_fix_relationship VARCHAR2(10);
564
565 /* Fixed conversion relationships enhancements End */
566
567 BEGIN
568 -- Get currency information from the x_from_currency
569 get_info ( x_from_currency, x_effective_date, rate, mau, from_type );
570
571 -- Get currency information from the x_to_currency
572 get_info ( x_to_currency, x_effective_date, rate, mau, to_type );
573
574 -- Check if there is a fixed rate between the two given currencies
575 IF ( x_from_currency = x_to_currency ) THEN
576 x_fixed_rate := TRUE;
577
578 ELSE
579 IF (( from_type IN ('EMU', 'EURO')) AND
580 ( to_type IN ('EMU', 'EURO'))) THEN
581 x_fixed_rate := TRUE;
582
583 ELSE
584 /* Fixed conversion relationships enhancements start */
585
586 OPEN is_there_fix_relation(x_from_currency,
587 x_to_currency,
588 x_effective_date);
589 FETCH is_there_fix_relation INTO l_fix_relationship;
590
591 IF (is_there_fix_relation%FOUND) THEN
592 x_fixed_rate := TRUE;
593 /* Fixed conversion relationships enhancements end */
594
595 ELSE
596 x_fixed_rate := FALSE;
597 END IF;
598 END IF;
599 END IF;
600
601
602 -- Get the relationship between the currencies
603 x_relationship := from_type || '-' || to_type;
604
605 END get_relation;
606
607
608 --
609 -- FUNCTION
610 -- get_euro_code
611 --
612 -- Purpose
613 -- Returns the currency code for the EURO currency. We need to
614 -- select this currency code from fnd_currencies table because
615 -- the currency code for EURO has not been fixed at this time.
616 --
620 -- Arguments
617 -- History
618 -- 24-JUL-97 W Wong Created
619 --
621 -- None.
622 --
623 FUNCTION get_euro_code RETURN VARCHAR2 IS
624 euro_code VARCHAR2(15);
625
626 BEGIN
627 -- Get currency code of the EURO currency
628 SELECT currency_code
629 INTO euro_code
630 FROM FND_CURRENCIES
631 WHERE derive_type = 'EURO';
632
633 return( euro_code );
634
635 EXCEPTION
636 WHEN NO_DATA_FOUND THEN
637 raise INVALID_CURRENCY;
638
639 END get_euro_code;
640
641 --
642 -- Function
643 -- get_rate
644 --
645 -- Purpose
646 -- Returns the rate between the two currencies for a given conversion
647 -- date and conversion type.
648 --
649 -- History
650 -- 15-JUL-97 W Wong Created
651 --
652 -- Arguments
653 -- x_from_currency From currency
654 -- x_to_currency To currency
655 -- x_conversion_date Conversion date
656 -- x_conversion_type Conversion type
657 --
658 FUNCTION get_rate (
659 x_from_currency VARCHAR2,
660 x_to_currency VARCHAR2,
661 x_conversion_date DATE,
662 x_conversion_type VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
663
664 euro_code VARCHAR2(15);
665 to_type VARCHAR2(8);
666 from_type VARCHAR2(8);
667 to_rate NUMBER;
668 from_rate NUMBER;
669 other_rate NUMBER;
670 mau NUMBER; -- Value ignored in this function
671
672 BEGIN
673 -- Check if both currencies are identical
674 IF ( x_from_currency = x_to_currency ) THEN
675 return( 1 );
676 END IF;
677
678 -- Get currency information from the x_from_currency
679 get_info ( x_from_currency, x_conversion_date, from_rate, mau,
680 from_type );
681
682 -- Get currency information from the x_to_currency
683 get_info ( x_to_currency, x_conversion_date, to_rate, mau, to_type );
684
685 -- Calculate the conversion rate according to both currency types
686 IF ( from_type = 'EMU' ) THEN
687 IF ( to_type = 'EMU' ) THEN
688 return( to_rate / from_rate );
689
690 ELSIF ( to_type = 'EURO' ) THEN
691 return( 1 / from_rate );
692
693 ELSIF ( to_type = 'OTHER' ) THEN
694 -- Find out conversion rate from EURO to x_to_currency
695 euro_code := get_euro_code;
696 other_rate := get_other_rate( euro_code, x_to_currency,
697 x_conversion_date,
698 x_conversion_type );
699
700 -- Get conversion rate by converting EMU -> EURO -> OTHER
701 return( other_rate / from_rate );
702 END IF;
703
704 ELSIF ( from_type = 'EURO' ) THEN
705 IF ( to_type = 'EMU' ) THEN
706 return( to_rate );
707
708 ELSIF ( to_type = 'EURO' ) THEN
709 -- We should never comes to this case as it should be
710 -- caught when we check if both to and from currency
711 -- is the same at the beginning of this function
712 return( 1 );
713
714 ELSIF ( to_type = 'OTHER' ) THEN
715 other_rate := get_other_rate( x_from_currency, x_to_currency,
716 x_conversion_date,
717 x_conversion_type );
718 return( other_rate );
719 END IF;
720
721 ELSIF ( from_type = 'OTHER' ) THEN
722 IF ( to_type = 'EMU' ) THEN
723 -- Find out conversion rate from x_from_currency to EURO
724 euro_code := get_euro_code;
725 other_rate := get_other_rate( x_from_currency, euro_code,
726 x_conversion_date,
727 x_conversion_type );
728
729 -- Get conversion rate by converting OTHER -> EURO -> EMU
730 return( other_rate * to_rate );
731
732 ELSIF ( to_type = 'EURO' ) THEN
733 other_rate := get_other_rate( x_from_currency, x_to_currency,
734 x_conversion_date,
735 x_conversion_type );
736 return( other_rate );
737
738 ELSIF ( to_type = 'OTHER' ) THEN
739 other_rate := get_other_rate( x_from_currency, x_to_currency,
740 x_conversion_date,
741 x_conversion_type );
742 return( other_rate );
743 END IF;
744 END IF;
745
746 END get_rate;
747
748
749 --
750 -- Function
751 -- get_rate
752 --
753 -- Purpose
754 -- Returns the rate between the from currency and the functional
755 -- currency of the ledgers.
756 --
757 -- History
758 -- 15-JUL-97 W Wong Created
759 --
760 -- Arguments
761 -- x_set_of_books_id Ledger id
762 -- x_from_currency From currency
763 -- x_conversion_date Conversion date
764 -- x_conversion_type Conversion type
765 --
766 FUNCTION get_rate (
767 x_set_of_books_id NUMBER,
768 x_from_currency VARCHAR2,
769 x_conversion_date DATE,
770 x_conversion_type VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
771
772 to_currency VARCHAR2(15);
773 rate NUMBER;
774
775 BEGIN
776 -- Get to_currency from GL_LEDGERS, i.e. the functional currency
777 SELECT currency_code
781
778 INTO to_currency
779 FROM GL_LEDGERS
780 WHERE ledger_id = x_set_of_books_id;
782 -- Call get_rate() with the to_currency we get from the ledger
783 rate := get_rate( x_from_currency, to_currency, x_conversion_date,
784 x_conversion_type );
785
786 return( rate );
787
788 END get_rate;
789
790 --
791 -- Function
792 -- get_rate_sql
793 --
794 -- Purpose
795 -- Returns the rate between the two currencies for a given conversion
796 -- date and conversion type by calling get_rate().
797 --
798 -- Return -1 if the NO_RATE exception is raised in get_rate().
799 -- -2 if the INVALID_CURRENCY exception is raised in get_rate().
800 --
801 -- History
802 -- 04-DEC-97 W Wong Created
803 --
804 -- Arguments
805 -- x_from_currency From currency
806 -- x_to_currency To currency
807 -- x_conversion_date Conversion date
808 -- x_conversion_type Conversion type
809 --
810 FUNCTION get_rate_sql (
811 x_from_currency VARCHAR2,
812 x_to_currency VARCHAR2,
813 x_conversion_date DATE,
814 x_conversion_type VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
815
816 rate NUMBER;
817 BEGIN
818 -- Call get_rate() using the given parameters
819 rate := get_rate( x_from_currency, x_to_currency, x_conversion_date,
820 x_conversion_type );
821 return( rate );
822
823 EXCEPTION
824 WHEN NO_RATE THEN
825 rate := -1;
826 return( rate );
827
828 WHEN INVALID_CURRENCY THEN
829 rate := -2;
830 return( rate );
831
832 END get_rate_sql;
833
834
835 --
836 -- Function
837 -- get_rate_sql
838 --
839 -- Purpose
840 -- Returns the rate between the from currency and the functional
841 -- currency of the ledgers by calling get_rate().
842 --
843 -- Return -1 if the NO_RATE exception is raised in get_rate().
844 -- -2 if the INVALID_CURRENCY exception is raised in get_rate().
845 --
846 -- History
847 -- 04-DEC-97 W Wong Created
848 --
849 -- Arguments
850 -- x_set_of_books_id Ledger id
851 -- x_from_currency From currency
852 -- x_conversion_date Conversion date
853 -- x_conversion_type Conversion type
854 --
855 FUNCTION get_rate_sql (
856 x_set_of_books_id NUMBER,
857 x_from_currency VARCHAR2,
858 x_conversion_date DATE,
859 x_conversion_type VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
860
861 rate NUMBER;
862 BEGIN
863
864 -- Call get_rate() using the given parameters
865 rate := get_rate( x_set_of_books_id, x_from_currency, x_conversion_date,
866 x_conversion_type );
867 return( rate );
868
869 EXCEPTION
870 WHEN NO_RATE THEN
871 rate := -1;
872 return( rate );
873
874 WHEN INVALID_CURRENCY THEN
875 rate := -2;
876 return( rate );
877
878 END get_rate_sql;
879
880 --
881 -- Function
882 -- get_closest_rate
883 --
884 -- Purpose
885 -- Returns the rate between the two currencies for a given conversion
886 -- date and conversion type.
887 --
888 -- If such a rate is not defined for the specified conversion_date, it
889 -- searches backward for a rate defined for the same currencies and
890 -- conversion type. It searches backward up to x_max_roll_days prior
891 -- to the specified x_conversion_date.
892 --
893 -- History
894 -- 04-DEC-97 W Wong Created
895 --
896 -- Arguments
897 -- x_from_currency From currency
898 -- x_to_currency To currency
899 -- x_conversion_date Conversion date
900 -- x_conversion_type Conversion type
901 -- x_max_roll_days Number of days to rollback for a rate
902 --
903 FUNCTION get_closest_rate (
904 x_from_currency VARCHAR2,
905 x_to_currency VARCHAR2,
906 x_conversion_date DATE,
907 x_conversion_type VARCHAR2 DEFAULT NULL,
908 x_max_roll_days NUMBER ) RETURN NUMBER IS
909
910 euro_code VARCHAR2(15);
911 to_type VARCHAR2(8);
912 from_type VARCHAR2(8);
913 to_rate NUMBER;
914 from_rate NUMBER;
915 other_rate NUMBER;
916 mau NUMBER; -- Value ignored in this function
917
918 BEGIN
919 -- Check if both currencies are identical
920 IF ( x_from_currency = x_to_currency ) THEN
921 return( 1 );
922 END IF;
923
924 -- Get currency information from the x_from_currency
925 get_info ( x_from_currency, x_conversion_date, from_rate, mau,
926 from_type );
927
928 -- Get currency information from the x_to_currency
929 get_info ( x_to_currency, x_conversion_date, to_rate, mau, to_type );
930
931 -- Calculate the conversion rate according to both currency types
932 IF ( from_type = 'EMU' ) THEN
933 IF ( to_type = 'EMU' ) THEN
934 return( to_rate / from_rate );
935
936 ELSIF ( to_type = 'EURO' ) THEN
937 return( 1 / from_rate );
941 euro_code := get_euro_code;
938
939 ELSIF ( to_type = 'OTHER' ) THEN
940 -- Find out conversion rate from EURO to x_to_currency
942 other_rate := get_other_closest_rate( euro_code,
943 x_to_currency,
944 x_conversion_date,
945 x_conversion_type,
946 x_max_roll_days );
947
948 -- Get conversion rate by converting EMU -> EURO -> OTHER
949 return( other_rate / from_rate );
950 END IF;
951
952 ELSIF ( from_type = 'EURO' ) THEN
953 IF ( to_type = 'EMU' ) THEN
954 return( to_rate );
955
956 ELSIF ( to_type = 'EURO' ) THEN
957 -- We should never comes to this case as it should be
958 -- caught when we check if both to and from currency
959 -- is the same at the beginning of this function
960 return( 1 );
961
962 ELSIF ( to_type = 'OTHER' ) THEN
963 other_rate := get_other_closest_rate( x_from_currency,
964 x_to_currency,
965 x_conversion_date,
966 x_conversion_type,
967 x_max_roll_days );
968 return( other_rate );
969 END IF;
970
971 ELSIF ( from_type = 'OTHER' ) THEN
972 IF ( to_type = 'EMU' ) THEN
973 -- Find out conversion rate from x_from_currency to EURO
974 euro_code := get_euro_code;
975 other_rate := get_other_closest_rate( x_from_currency,
976 euro_code,
977 x_conversion_date,
978 x_conversion_type,
979 x_max_roll_days );
980
981 -- Get conversion rate by converting OTHER -> EURO -> EMU
982 return( other_rate * to_rate );
983
984 ELSIF ( to_type = 'EURO' ) THEN
985 other_rate := get_other_closest_rate( x_from_currency,
986 x_to_currency,
987 x_conversion_date,
988 x_conversion_type,
989 x_max_roll_days );
990 return( other_rate );
991
992 ELSIF ( to_type = 'OTHER' ) THEN
993 other_rate := get_other_closest_rate( x_from_currency,
994 x_to_currency,
995 x_conversion_date,
996 x_conversion_type,
997 x_max_roll_days );
998 return( other_rate );
999 END IF;
1000 END IF;
1001
1002 END get_closest_rate;
1003
1004
1005 -- Function
1006 -- get_closest_rate
1007 --
1008 -- Purpose
1009 -- Returns the rate between the from currency and the functional currency
1010 -- of the ledgers, for a given conversion date and conversion type.
1011 --
1012 -- If such a rate is not defined for the specified conversion_date, it
1013 -- searches backward for a rate defined for the same currencies and
1014 -- conversion type. It searches backward up to x_max_roll_days prior
1015 -- to the specified x_conversion_date.
1016 --
1017 -- History
1018 -- 04-DEC-97 W Wong Created
1019 --
1020 -- Arguments
1021 -- x_set_of_books_id Ledger ID
1022 -- x_from__currency From currency
1023 -- x_conversion_date Conversion date
1024 -- x_conversion_type Conversion type
1025 -- x_max_roll_days Number of days to rollback for a rate
1026 --
1027 FUNCTION get_closest_rate (
1028 x_set_of_books_id NUMBER,
1029 x_from_currency VARCHAR2,
1030 x_conversion_date DATE,
1031 x_conversion_type VARCHAR2 DEFAULT NULL,
1032 x_max_roll_days NUMBER ) RETURN NUMBER IS
1033 to_currency VARCHAR2(15);
1034 rate NUMBER;
1035
1036 BEGIN
1037 -- Get to_currency from GL_LEDGERS, i.e. the functional currency
1038 SELECT currency_code
1039 INTO to_currency
1040 FROM GL_LEDGERS
1041 WHERE ledger_id = x_set_of_books_id;
1042
1043 -- Call get_closest_rate() with the to_currency we get from the Ledgers
1044 rate := get_closest_rate( x_from_currency,
1045 to_currency,
1046 x_conversion_date,
1047 x_conversion_type,
1048 x_max_roll_days );
1049
1050 return( rate );
1051
1052 END get_closest_rate;
1053
1054
1055 --
1056 -- Function
1057 -- get_closest_rate_sql
1058 --
1059 -- Purpose
1060 -- Returns the rate between the two currencies for a given conversion
1061 -- date and conversion type by calling get_closest_rate().
1062 --
1063 -- If such a rate is not defined for the specified conversion_date, it
1064 -- searches backward for a rate defined for the same currencies and
1065 -- conversion type. It searches backward up to x_max_roll_days prior
1066 -- to the specified x_conversion_date.
1067 --
1068 -- Return -1 if the NO_RATE exception is raised in get_closest_rate().
1069 -- -2 if the INVALID_CURRENCY exception is raised in
1070 -- get_closest_rate().
1071 --
1072 -- History
1073 -- 04-DEC-97 W Wong Created
1074 --
1075 -- Arguments
1076 -- x_from_currency From currency
1077 -- x_to_currency To currency
1078 -- x_conversion_date Conversion date
1079 -- x_conversion_type Conversion type
1083 x_from_currency VARCHAR2,
1080 -- x_max_roll_days Number of days to rollback for a rate
1081 --
1082 FUNCTION get_closest_rate_sql (
1084 x_to_currency VARCHAR2,
1085 x_conversion_date DATE,
1086 x_conversion_type VARCHAR2 DEFAULT NULL,
1087 x_max_roll_days NUMBER ) RETURN NUMBER IS
1088
1089 rate NUMBER;
1090 BEGIN
1091 rate := get_closest_rate( x_from_currency, x_to_currency,
1092 x_conversion_date, x_conversion_type,
1093 x_max_roll_days );
1094 return( rate );
1095
1096 EXCEPTION
1097 WHEN NO_RATE THEN
1098 rate := -1;
1099 return( rate );
1100
1101 WHEN INVALID_CURRENCY THEN
1102 rate := -2;
1103 return( rate );
1104
1105 END get_closest_rate_sql;
1106
1107
1108 --
1109 -- Function
1110 -- get_closest_rate_sql
1111 --
1112 -- Purpose
1113 -- Returns the rate between the from currency and the functional currency
1114 -- of the ledgers, for a given conversion date and conversion type
1115 -- by calling get_closest_rate().
1116 --
1117 -- If such a rate is not defined for the specified conversion_date, it
1118 -- searches backward for a rate defined for the same currencies and
1119 -- conversion type. It searches backward up to x_max_roll_days prior
1120 -- to the specified x_conversion_date.
1121 --
1122 -- Return -1 if the NO_RATE exception is raised in get_closest_rate().
1123 -- -2 if the INVALID_CURRENCY exception is raised in
1124 -- get_closest_rate().
1125 --
1126 -- History
1127 -- 04-DEC-97 W Wong Created
1128 --
1129 -- Arguments
1130 -- x_set_of_books_id Ledger id
1131 -- x_from_currency From currency
1132 -- x_conversion_date Conversion date
1133 -- x_conversion_type Conversion type
1134 -- x_max_roll_days Number of days to rollback for a rate
1135 --
1136 FUNCTION get_closest_rate_sql (
1137 x_set_of_books_id NUMBER,
1138 x_from_currency VARCHAR2,
1139 x_conversion_date DATE,
1140 x_conversion_type VARCHAR2 DEFAULT NULL,
1141 x_max_roll_days NUMBER ) RETURN NUMBER IS
1142
1143 rate NUMBER;
1144 BEGIN
1145 rate := get_closest_rate( x_set_of_books_id, x_from_currency,
1146 x_conversion_date, x_conversion_type,
1147 x_max_roll_days );
1148 return( rate );
1149
1150 EXCEPTION
1151 WHEN NO_RATE THEN
1152 rate := -1;
1153 return( rate );
1154
1155 WHEN INVALID_CURRENCY THEN
1156 rate := -2;
1157 return( rate );
1158
1159 END get_closest_rate_sql;
1160
1161
1162 --
1163 -- Function
1164 -- convert_amount
1165 --
1166 -- Purpose
1167 -- Returns the amount converted from the from currency into the
1168 -- to currency for a given conversion date and conversion type.
1169 -- The amount returned is rounded to the precision and minimum
1170 -- account unit of the to currency.
1171 --
1172 -- History
1173 -- 15-JUL-97 W Wong Created
1174 --
1175 -- Arguments
1176 -- x_from_currency From currency
1177 -- x_to_currency To currency
1178 -- x_conversion_date Conversion date
1179 -- x_conversion_type Conversion type
1180 -- x_amount Amount to be converted from the from currency
1181 -- into the to currency
1182 --
1183 FUNCTION convert_amount (
1184 x_from_currency VARCHAR2,
1185 x_to_currency VARCHAR2,
1186 x_conversion_date DATE,
1187 x_conversion_type VARCHAR2 DEFAULT NULL,
1188 x_amount NUMBER ) RETURN NUMBER IS
1189
1190 euro_code VARCHAR2(15);
1191 to_type VARCHAR2(8);
1192 from_type VARCHAR2(8);
1193 to_rate NUMBER;
1194 from_rate NUMBER;
1195 other_rate NUMBER;
1196 from_mau NUMBER;
1197 to_mau NUMBER;
1198 converted_amount NUMBER;
1199
1200 BEGIN
1201 -- Check if both currencies are identical
1202 IF ( x_from_currency = x_to_currency ) THEN
1203 return( x_amount );
1204 END IF;
1205
1206 -- Get currency information from the from_currency
1207 get_info ( x_from_currency, x_conversion_date, from_rate, from_mau,
1208 from_type );
1209
1210 -- Get currency information from the to_currency
1211 get_info ( x_to_currency, x_conversion_date, to_rate, to_mau, to_type );
1212
1213 -- Calculate the conversion rate according to both currency types
1214 IF ( from_type = 'EMU' ) THEN
1215 IF ( to_type = 'EMU' ) THEN
1216 converted_amount := ( x_amount / from_rate ) * to_rate;
1217
1218 ELSIF ( to_type = 'EURO' ) THEN
1219 converted_amount := x_amount / from_rate;
1220
1221 ELSIF ( to_type = 'OTHER' ) THEN
1222 -- Find out conversion rate from EURO to x_to_currency
1223 euro_code := get_euro_code;
1224 other_rate := get_other_rate( euro_code, x_to_currency,
1225 x_conversion_date,
1226 x_conversion_type );
1227
1228 -- Get conversion amt by converting EMU -> EURO -> OTHER
1229 converted_amount := ( x_amount / from_rate ) * other_rate;
1230 END IF;
1234 converted_amount := x_amount * to_rate;
1231
1232 ELSIF ( from_type = 'EURO' ) THEN
1233 IF ( to_type = 'EMU' ) THEN
1235
1236 ELSIF ( to_type = 'EURO' ) THEN
1237 -- We should never comes to this case as it should be
1238 -- caught when we check if both to and from currency
1239 -- is the same at the beginning of this function
1240 converted_amount := x_amount;
1241
1242 ELSIF ( to_type = 'OTHER' ) THEN
1243 other_rate := get_other_rate( x_from_currency, x_to_currency,
1244 x_conversion_date,
1245 x_conversion_type );
1246 converted_amount := x_amount * other_rate;
1247 END IF;
1248
1249 ELSIF ( from_type = 'OTHER' ) THEN
1250 IF ( to_type = 'EMU' ) THEN
1251 -- Find out conversion rate from x_from_currency to EURO
1252 euro_code := get_euro_code;
1253 other_rate := get_other_rate( x_from_currency, euro_code,
1254 x_conversion_date,
1255 x_conversion_type );
1256
1257 -- Get conversion amt by converting OTHER -> EURO -> EMU
1258 converted_amount := ( x_amount * other_rate ) * to_rate;
1259
1260 ELSIF ( to_type = 'EURO' ) THEN
1261 other_rate := get_other_rate( x_from_currency, x_to_currency,
1262 x_conversion_date,
1263 x_conversion_type );
1264 converted_amount := x_amount * other_rate;
1265
1266 ELSIF ( to_type = 'OTHER' ) THEN
1267 other_rate := get_other_rate( x_from_currency, x_to_currency,
1268 x_conversion_date,
1269 x_conversion_type );
1270 converted_amount := x_amount * other_rate;
1271 END IF;
1272 END IF;
1273
1274 -- Rounding to the correct precision and minumum accountable units
1275 return( round( converted_amount / to_mau ) * to_mau );
1276
1277 END convert_amount;
1278
1279 --
1280 -- Function
1281 -- convert_amount
1282 --
1283 -- Purpose
1284 -- Returns the amount converted from the from currency into the
1285 -- functional currency of that ledgers. The amount returned is
1286 -- rounded to the precision and minimum account unit of the to currency.
1287 --
1288 -- History
1289 -- 15-JUL-97 W Wong Created
1290 --
1291 -- Arguments
1292 -- x_set_of_books_id Ledger id
1293 -- x_from_currency From currency
1294 -- x_conversion_date Conversion date
1295 -- x_conversion_type Conversion type
1296 -- x_amount Amount to be converted from the from currency
1297 -- into the functional currency of the ledgers
1298 --
1299 --
1300 FUNCTION convert_amount (
1301 x_set_of_books_id NUMBER,
1302 x_from_currency VARCHAR2,
1303 x_conversion_date DATE,
1304 x_conversion_type VARCHAR2 DEFAULT NULL,
1305 x_amount NUMBER ) RETURN NUMBER IS
1306 to_currency VARCHAR2(15);
1307 converted_amount NUMBER;
1308
1309 BEGIN
1310 -- Get to_currency from GL_LEDGERS, i.e. the functional currency
1311 SELECT currency_code
1312 INTO to_currency
1313 FROM GL_LEDGERS
1314 WHERE ledger_id = x_set_of_books_id;
1315
1316 -- Call convert_amount() with the to_currency we get from the Ledger
1317 converted_amount := convert_amount( x_from_currency, to_currency,
1318 x_conversion_date, x_conversion_type,
1319 x_amount );
1320
1321 return( converted_amount );
1322
1323 END convert_amount;
1324
1325
1326 --
1327 -- Function
1328 -- convert_amount_sql
1329 --
1330 -- Purpose
1331 -- Returns the amount converted from the from currency into the
1332 -- to currency for a given conversion date and conversion type by
1333 -- calling convert_amount().
1334 -- The amount returned is rounded to the precision and minimum
1335 -- account unit of the to currency.
1336 --
1337 -- Return -1 if the NO_RATE exception is raised in get_rate().
1338 -- -2 if the INVALID_CURRENCY exception is raised in get_rate().
1339 --
1340 -- History
1341 -- 04-DEC-97 W Wong Created
1342 --
1343 -- Arguments
1344 -- x_from_currency From currency
1345 -- x_to_currency To currency
1346 -- x_conversion_date Conversion date
1347 -- x_conversion_type Conversion type
1348 -- x_amount Amount to be converted from the from currency
1349 -- into the to currency
1350 --
1351 FUNCTION convert_amount_sql (
1352 x_from_currency VARCHAR2,
1353 x_to_currency VARCHAR2,
1354 x_conversion_date DATE,
1355 x_conversion_type VARCHAR2 DEFAULT NULL,
1356 x_amount NUMBER ) RETURN NUMBER IS
1357
1358 converted_amount NUMBER;
1359 BEGIN
1360 converted_amount := convert_amount( x_from_currency, x_to_currency,
1361 x_conversion_date, x_conversion_type,
1362 x_amount );
1363 return( converted_amount );
1364
1365 EXCEPTION
1366 WHEN NO_RATE THEN
1367 converted_amount := -1;
1368 return( converted_amount );
1369
1370 WHEN INVALID_CURRENCY THEN
1371 converted_amount := -2;
1372 return( converted_amount );
1373
1374 END convert_amount_sql;
1375
1376 --
1377 -- Function
1378 -- convert_amount_sql
1379 --
1383 -- The amount returned is rounded to the precision and minimum account
1380 -- Purpose
1381 -- Returns the amount converted from the from currency into the
1382 -- functional currency of that ledgers by calling convert_amount().
1384 -- unit of the to currency.
1385 --
1386 -- Return -1 if the NO_RATE exception is raised in get_rate().
1387 -- -2 if the INVALID_CURRENCY exception is raised in get_rate().
1388 --
1389 -- History
1390 -- 04-DEC-97 W Wong Created
1391 --
1392 -- Arguments
1393 -- x_set_of_books_id Ledger id
1394 -- x_from_currency From currency
1395 -- x_conversion_date Conversion date
1396 -- x_conversion_type Conversion type
1397 -- x_amount Amount to be converted from the from currency
1398 -- into the functional currency of the ledgers
1399 --
1400 --
1401 FUNCTION convert_amount_sql (
1402 x_set_of_books_id NUMBER,
1403 x_from_currency VARCHAR2,
1404 x_conversion_date DATE,
1405 x_conversion_type VARCHAR2 DEFAULT NULL,
1406 x_amount NUMBER ) RETURN NUMBER IS
1407
1408 converted_amount NUMBER;
1409 BEGIN
1410 converted_amount := convert_amount( x_set_of_books_id, x_from_currency,
1411 x_conversion_date, x_conversion_type,
1412 x_amount );
1413 return( converted_amount );
1414
1415 EXCEPTION
1416 WHEN NO_RATE THEN
1417 converted_amount := -1;
1418 return( converted_amount );
1419
1420 WHEN INVALID_CURRENCY THEN
1421 converted_amount := -2;
1422 return( converted_amount );
1423
1424 END convert_amount_sql;
1425
1426 --
1427 -- Function
1428 -- get_derive_type
1429 -- Purpose
1430 -- Gets derive type for a currency.
1431 --
1432 -- NOTE: This function is for GL ONLY!
1433 -- It'll returns GL specific derive type.
1434 --
1435 -- History
1436 -- 08/07/97 K Chen Created
1437 -- Arguments
1438 -- ledger_id NUMBER
1439 -- period VARCHAR2
1440 -- curr_code VARCHAR2
1441 -- Example
1442 -- :Parameter.derive_type := glxrvsub_pkg.get_derive_type
1443 -- (:Parameter.access_set_id, :OPTIONS.period_name,
1444 -- :Parameter.func_curr_code);
1445 -- Notes
1446 --
1447 FUNCTION get_derive_type (ledger_id NUMBER, period VARCHAR2, curr_code VARCHAR2)
1448 RETURN VARCHAR2 IS
1449 derive_type VARCHAR2(8);
1450 derive_effective DATE;
1451 BEGIN
1452
1453 SELECT derive_type, derive_effective
1454 INTO derive_type, derive_effective
1455 FROM fnd_currencies
1456 WHERE currency_code = curr_code;
1457
1458 IF (derive_type IS NULL or
1459 derive_type = 'OTHER') THEN
1460 RETURN 'OTHER';
1461 END IF;
1462
1463 IF (derive_type = 'EURO') THEN
1464 RETURN 'EURO';
1465 END IF;
1466
1467 IF (derive_effective IS NOT NULL) THEN
1468 SELECT DECODE(SIGN(trunc(derive_effective) -
1469 trunc(GPS.end_date)), 1,
1470 'OTHER',
1471 DECODE (SIGN(trunc(GPS.start_date) -
1472 trunc(derive_effective)), 1,
1473 'EMU',
1474 'INTER'))
1475 INTO derive_type
1476 FROM gl_period_statuses GPS
1477 WHERE GPS.application_id = 101
1478 AND GPS.ledger_id = ledger_id
1479 AND GPS.period_name = period;
1480
1481 RETURN derive_type;
1482 ELSE
1483 RETURN 'ERROR';
1484 END IF;
1485
1486 EXCEPTION
1487 WHEN NO_DATA_FOUND THEN
1488 raise NO_DERIVE_TYPE;
1489 END get_derive_type;
1490
1491 --
1492 -- Function
1493 -- rate_exists
1494 --
1495 -- Purpose
1496 -- Returns 'Y' if there is a conversion rate between the two currencies
1497 -- for a given conversion date and conversion type;
1498 -- 'N' otherwise.
1499 --
1500 -- History
1501 -- 03-SEP-97 W Wong Created
1502 --
1503 -- Arguments
1504 -- x_from_currency From currency
1505 -- x_to_currency To currency
1506 -- x_conversion_date Conversion date
1507 -- x_conversion_type Conversion type
1508 --
1509 FUNCTION rate_exists (
1510 x_from_currency VARCHAR2,
1511 x_to_currency VARCHAR2,
1512 x_conversion_date DATE,
1513 x_conversion_type VARCHAR2 DEFAULT NULL)
1514 RETURN VARCHAR2 IS
1515 rate NUMBER;
1516 BEGIN
1517 rate := get_rate( x_from_currency, x_to_currency,
1518 x_conversion_date, x_conversion_type );
1519
1520 -- Conversion rates exists between these two currencies for the given
1521 -- conversion rate and conversion date.
1522 return( 'Y' );
1523
1524 EXCEPTION
1525 WHEN NO_RATE THEN
1526 -- Conversion rates does not exist between these two currencies for the
1527 -- given conversion rate and conversion date.
1528 return( 'N' );
1529
1530 END rate_exists;
1531
1532 --
1533 -- Function
1534 -- get_rate_numerator_sql
1535 --
1536 -- Purpose
1537 -- Returns the numerator we should use to calculate the conversion
1538 -- rate between the two currencies for a given conversion date and
1539 -- conversion type.
1540 --
1544 -- History
1541 -- Return -1 if the NO_RATE exception is raised.
1542 -- -2 if the INVALID_CURRENCY exception is raised.
1543 --
1545 -- 11-MAY-98 W Wong Created
1546 --
1547 -- Arguments
1548 -- x_from_currency From currency
1549 -- x_to_currency To currency
1550 -- x_conversion_date Conversion date
1551 -- x_conversion_type Conversion type
1552 --
1553 FUNCTION get_rate_numerator_sql (
1554 x_from_currency VARCHAR2,
1555 x_to_currency VARCHAR2,
1556 x_conversion_date DATE,
1557 x_conversion_type VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
1558
1559 euro_code VARCHAR2(15);
1560 to_type VARCHAR2(8);
1561 from_type VARCHAR2(8);
1562 to_rate NUMBER;
1563 from_rate NUMBER;
1564 other_rate NUMBER;
1565 mau NUMBER; -- Value ignored in this function
1566
1567 BEGIN
1568
1569 -- Check if both currencies are identical
1570 IF ( x_from_currency = x_to_currency ) THEN
1571 return( 1 );
1572 END IF;
1573
1574 -- Get currency information from the x_from_currency
1575 get_info ( x_from_currency, x_conversion_date, from_rate, mau,
1576 from_type );
1577
1578 -- Get currency information from the x_to_currency
1579 get_info ( x_to_currency, x_conversion_date, to_rate, mau, to_type );
1580
1581 -- Calculate the conversion rate according to both currency types
1582 IF ( from_type = 'EMU' ) THEN
1583 IF ( to_type = 'EMU' ) THEN
1584 return( to_rate );
1585
1586 ELSIF ( to_type = 'EURO' ) THEN
1587 return( 1 );
1588
1589 ELSIF ( to_type = 'OTHER' ) THEN
1590 -- Find out conversion rate from EURO to x_to_currency
1591 euro_code := get_euro_code;
1592 other_rate := get_other_rate( euro_code, x_to_currency,
1593 x_conversion_date,
1594 x_conversion_type );
1595
1596 -- Get conversion rate by converting EMU -> EURO -> OTHER
1597 return( other_rate );
1598 END IF;
1599
1600 ELSIF ( from_type = 'EURO' ) THEN
1601 IF ( to_type = 'EMU' ) THEN
1602 return( to_rate );
1603
1604 ELSIF ( to_type = 'EURO' ) THEN
1605 -- We should never comes to this case as it should be
1606 -- caught when we check if both to and from currency
1607 -- is the same at the beginning of this function
1608 return( 1 );
1609
1610 ELSIF ( to_type = 'OTHER' ) THEN
1611 other_rate := get_other_rate( x_from_currency, x_to_currency,
1612 x_conversion_date,
1613 x_conversion_type );
1614 return( other_rate );
1615 END IF;
1616
1617 ELSIF ( from_type = 'OTHER' ) THEN
1618 IF ( to_type = 'EMU' ) THEN
1619 -- Find out conversion rate from x_from_currency to EURO
1620 euro_code := get_euro_code;
1621 other_rate := get_other_rate( x_from_currency, euro_code,
1622 x_conversion_date,
1623 x_conversion_type );
1624
1625 -- Get conversion rate by converting OTHER -> EURO -> EMU
1626 return( other_rate * to_rate );
1627
1628 ELSIF ( to_type = 'EURO' ) THEN
1629 other_rate := get_other_rate( x_from_currency, x_to_currency,
1630 x_conversion_date,
1631 x_conversion_type );
1632 return( other_rate );
1633
1634 ELSIF ( to_type = 'OTHER' ) THEN
1635 other_rate := get_other_rate( x_from_currency, x_to_currency,
1636 x_conversion_date,
1637 x_conversion_type );
1638 return( other_rate );
1639 END IF;
1640 END IF;
1641
1642 EXCEPTION
1643 WHEN NO_RATE THEN
1644 return( -1 );
1645
1646 WHEN INVALID_CURRENCY THEN
1647 return( -2 );
1648
1649 END get_rate_numerator_sql;
1650
1651 --
1652 -- Function
1653 -- get_rate_numerator_sql
1654 --
1655 -- Purpose
1656 -- Returns the numerator we should use to calculate the conversion rate
1657 -- between the from currency and the functional currency of the
1658 -- ledgers.
1659 --
1660 -- History
1661 -- 11-MAY-98 W Wong Created
1662 --
1663 -- Arguments
1664 -- x_set_of_books_id Ledger_id
1665 -- x_from_currency From currency
1666 -- x_conversion_date Conversion date
1667 -- x_conversion_type Conversion type
1668 --
1669 FUNCTION get_rate_numerator_sql (
1670 x_set_of_books_id NUMBER,
1671 x_from_currency VARCHAR2,
1672 x_conversion_date DATE,
1673 x_conversion_type VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
1674
1675 to_currency VARCHAR2(15);
1676 numerator NUMBER;
1677
1678 BEGIN
1679 -- Get to_currency from GL_LEDGERS, i.e. the functional currency
1680 SELECT currency_code
1681 INTO to_currency
1682 FROM GL_LEDGERS
1683 WHERE ledger_id = x_set_of_books_id;
1684
1685 -- Call get_rate_numerator_sql() with the to_currency we get from the Ledger
1686 numerator := get_rate_numerator_sql( x_from_currency,
1687 to_currency,
1688 x_conversion_date,
1689 x_conversion_type );
1690 return( numerator );
1691
1692 END get_rate_numerator_sql;
1693
1694 --
1695 -- Function
1699 -- Returns the denominator we should use to calculate the conversion
1696 -- get_rate_denominator_sql
1697 --
1698 -- Purpose
1700 -- rate between the two currencies for a given conversion date and
1701 -- conversion type.
1702 --
1703 -- Return -1 if the NO_RATE exception is raised.
1704 -- -2 if the INVALID_CURRENCY exception is raised.
1705 --
1706 -- History
1707 -- 11-MAY-98 W Wong Created
1708 --
1709 -- Arguments
1710 -- x_from_currency From currency
1711 -- x_to_currency To currency
1712 -- x_conversion_date Conversion date
1713 -- x_conversion_type Conversion type
1714 --
1715 FUNCTION get_rate_denominator_sql (
1716 x_from_currency VARCHAR2,
1717 x_to_currency VARCHAR2,
1718 x_conversion_date DATE,
1719 x_conversion_type VARCHAR2 DEFAULT NULL )
1720 RETURN NUMBER IS
1721
1722 euro_code VARCHAR2(15);
1723 to_type VARCHAR2(8);
1724 from_type VARCHAR2(8);
1725 to_rate NUMBER;
1726 from_rate NUMBER;
1727 other_rate NUMBER;
1728 mau NUMBER; -- Value ignored in this function
1729
1730 BEGIN
1731 -- Check if both currencies are identical
1732 IF ( x_from_currency = x_to_currency ) THEN
1733 return( 1 );
1734 END IF;
1735
1736 -- Get currency information from the x_from_currency
1737 get_info ( x_from_currency, x_conversion_date, from_rate, mau,
1738 from_type );
1739
1740 -- Calculate the conversion rate according to both currency types
1741 IF ( from_type = 'EMU' ) THEN
1742 return( from_rate );
1743
1744 ELSE
1745
1746 return( 1 );
1747 END IF;
1748
1749 EXCEPTION
1750 WHEN NO_RATE THEN
1751 return( -1 );
1752
1753 WHEN INVALID_CURRENCY THEN
1754 return( -2 );
1755
1756 END get_rate_denominator_sql;
1757
1758
1759 --
1760 -- Function
1761 -- get_rate_denominator_sql
1762 --
1763 -- Purpose
1764 -- Returns the denominator we should use to calculate the conversion rate
1765 -- between the from currency and the functional currency of the
1766 -- ledgers.
1767 --
1768 -- History
1769 -- 11-MAY-98 W Wong Created
1770 --
1771 -- Arguments
1772 -- x_set_of_books_id Ledger id
1773 -- x_from_currency From currency
1774 -- x_conversion_date Conversion date
1775 -- x_conversion_type Conversion type
1776 --
1777 FUNCTION get_rate_denominator_sql (
1778 x_set_of_books_id NUMBER,
1779 x_from_currency VARCHAR2,
1780 x_conversion_date DATE,
1781 x_conversion_type VARCHAR2 DEFAULT NULL )
1782 RETURN NUMBER IS
1783
1784 to_currency VARCHAR2(15);
1785 denominator NUMBER;
1786
1787 BEGIN
1788 -- Get to_currency from GL_LEDGERS, i.e. the functional currency
1789 SELECT currency_code
1790 INTO to_currency
1791 FROM GL_LEDGERS
1792 WHERE ledger_id = x_set_of_books_id;
1793
1794 -- Call get_rate_denominator_sql() with the to_currency we get from Ledger
1795 denominator := get_rate_denominator_sql( x_from_currency,
1796 to_currency,
1797 x_conversion_date,
1798 x_conversion_type );
1799 return( denominator );
1800 END get_rate_denominator_sql;
1801
1802 --
1803 -- Procedure
1804 -- get_triangulation_rate
1805 --
1806 -- Purpose
1807 -- Returns the numerator and denominator we should use to calculate
1808 -- the conversion rate between the two currencies, and the actual
1809 -- conversion rate for a given conversion date and conversion type.
1810 --
1811 -- Note: When you are calculating the triangulation rate, you should
1812 -- always divide by the x_denominator before you multiply by
1813 -- the x_numerator.
1814 --
1815 -- History
1816 -- 11-MAY-98 W Wong Created
1817 --
1818 -- Arguments
1819 -- x_from_currency From currency
1820 -- x_to_currency To currency
1821 -- x_conversion_date Conversion date
1822 -- x_conversion_type Conversion type
1823 -- x_denominator Denominator to get conversion rate
1824 -- x_numerator Numerator to get conversion rate
1825 -- x_rate Conversion rate
1826 --
1827 PROCEDURE get_triangulation_rate (
1828 x_from_currency VARCHAR2,
1829 x_to_currency VARCHAR2,
1830 x_conversion_date DATE,
1831 x_conversion_type VARCHAR2 DEFAULT NULL,
1832 x_denominator IN OUT NOCOPY NUMBER,
1833 x_numerator IN OUT NOCOPY NUMBER,
1834 x_rate IN OUT NOCOPY NUMBER ) IS
1835 BEGIN
1836 -- Get value of numerator and denominator
1837 x_numerator := get_rate_numerator_sql( x_from_currency,
1838 x_to_currency,
1839 x_conversion_date,
1840 x_conversion_type );
1841
1842 x_denominator := get_rate_denominator_sql ( x_from_currency,
1843 x_to_currency,
1844 x_conversion_date,
1845 x_conversion_type );
1846
1850
1847 -- Get conversion rate by using the x_numerator and x_denominator
1848 IF (( x_numerator > 0 ) AND ( x_denominator > 0 )) THEN
1849 x_rate := x_numerator / x_denominator;
1851 ELSE
1852 IF (( x_numerator = -2 ) OR (x_denominator = -2 )) THEN
1853 raise INVALID_CURRENCY;
1854
1855 ELSE
1856 raise NO_RATE;
1857 END IF;
1858 END IF;
1859
1860 END get_triangulation_rate;
1861
1862 --
1863 -- Procedure
1864 -- get_triangulation_rate
1865 --
1866 -- Purpose
1867 -- Returns the numerator, denominator and the conversion rate
1868 -- between the from currency and the functional currency of the
1869 -- ledgers.
1870 --
1871 -- Note: When you are calculating the triangulation rate, you should
1872 -- always divide by the x_denominator before you multiply by
1873 -- the x_numerator.
1874 --
1875 -- History
1876 -- 11-MAY-98 W Wong Created
1877 --
1878 -- Arguments
1879 -- x_set_of_books_id Ledger id
1880 -- x_from_currency From currency
1881 -- x_conversion_date Conversion date
1882 -- x_conversion_type Conversion type
1883 -- x_denominator Denominator to get conversion rate
1884 -- x_numerator Numerator to get conversion rate
1885 -- x_rate Conversion rate
1886 --
1887 PROCEDURE get_triangulation_rate (
1888 x_set_of_books_id NUMBER,
1889 x_from_currency VARCHAR2,
1890 x_conversion_date DATE,
1891 x_conversion_type VARCHAR2 DEFAULT NULL,
1892 x_denominator IN OUT NOCOPY NUMBER,
1893 x_numerator IN OUT NOCOPY NUMBER,
1894 x_rate IN OUT NOCOPY NUMBER) IS
1895
1896 to_currency VARCHAR2(15);
1897
1898 BEGIN
1899 -- Get to_currency from GL_LEDGERS, i.e. the functional currency
1900 SELECT currency_code
1901 INTO to_currency
1902 FROM GL_LEDGERS
1903 WHERE ledger_id = x_set_of_books_id;
1904
1905 -- Get value of numerator and denominator
1906 x_numerator := get_rate_numerator_sql( x_from_currency,
1907 to_currency,
1908 x_conversion_date,
1909 x_conversion_type );
1910
1911 x_denominator := get_rate_denominator_sql( x_from_currency,
1912 to_currency,
1913 x_conversion_date,
1914 x_conversion_type );
1915
1916 -- Get conversion rate by using the x_numerator and x_denominator
1917 IF (( x_numerator > 0 ) AND ( x_denominator > 0 )) THEN
1918 x_rate := x_numerator / x_denominator;
1919
1920 ELSE
1921 IF (( x_numerator = -2 ) OR (x_denominator = -2 )) THEN
1922 raise INVALID_CURRENCY;
1923 ELSE
1924 raise NO_RATE;
1925 END IF;
1926 END IF;
1927
1928 END get_triangulation_rate;
1929
1930 --
1931 -- Function
1932 -- get_closest_rate_numerator_sql
1933 --
1934 -- Purpose
1935 -- Returns the numerator we should use between the two currencies for
1936 -- a given conversion date and conversion type.
1937 --
1938 -- Return -1 if the NO_RATE exception is raised.
1939 -- -2 if the INVALID_CURRENCY exception is raised.
1940 --
1941 -- History
1942 -- 11-MAY-98 W Wong Created
1943 --
1944 -- Arguments
1945 -- x_from_currency From currency
1946 -- x_to_currency To currency
1947 -- x_conversion_date Conversion date
1948 -- x_conversion_type Conversion type
1949 -- x_max_roll_days Number of days to rollback for a rate
1950 --
1951 FUNCTION get_closest_rate_numerator_sql (
1952 x_from_currency VARCHAR2,
1953 x_to_currency VARCHAR2,
1954 x_conversion_date DATE,
1955 x_conversion_type VARCHAR2 DEFAULT NULL,
1956 x_max_roll_days NUMBER) RETURN NUMBER IS
1957
1958 euro_code VARCHAR2(15);
1959 to_type VARCHAR2(8);
1960 from_type VARCHAR2(8);
1961 to_rate NUMBER;
1962 from_rate NUMBER;
1963 other_rate NUMBER;
1964 mau NUMBER; -- Value ignored in this function
1965
1966 BEGIN
1967 -- Check if both currencies are identical
1968 IF ( x_from_currency = x_to_currency ) THEN
1969 return( 1 );
1970 END IF;
1971
1972 -- Get currency information from the x_from_currency
1973 get_info ( x_from_currency, x_conversion_date, from_rate, mau,
1974 from_type );
1975
1976 -- Get currency information from the x_to_currency
1977 get_info ( x_to_currency, x_conversion_date, to_rate, mau, to_type );
1978
1979 -- Calculate the conversion rate according to both currency types
1980 IF ( from_type = 'EMU' ) THEN
1981 IF ( to_type = 'EMU' ) THEN
1982 return( to_rate );
1983
1984 ELSIF ( to_type = 'EURO' ) THEN
1985 return( 1 );
1986
1987 ELSIF ( to_type = 'OTHER' ) THEN
1988 -- Find out conversion rate from EURO to x_to_currency
1989 euro_code := get_euro_code;
1990 other_rate := get_other_closest_rate( euro_code,
1991 x_to_currency,
1992 x_conversion_date,
1993 x_conversion_type,
1994 x_max_roll_days );
1998 ELSIF ( from_type = 'EURO' ) THEN
1995 return( other_rate );
1996 END IF;
1997
1999 IF ( to_type = 'EMU' ) THEN
2000 return( to_rate );
2001
2002 ELSIF ( to_type = 'EURO' ) THEN
2003 -- We should never comes to this case as it should be
2004 -- caught when we check if both to and from currency
2005 -- is the same at the beginning of this function
2006 return( 1 );
2007
2008 ELSIF ( to_type = 'OTHER' ) THEN
2009 other_rate := get_other_closest_rate( x_from_currency,
2010 x_to_currency,
2011 x_conversion_date,
2012 x_conversion_type,
2013 x_max_roll_days );
2014 return( other_rate );
2015 END IF;
2016
2017 ELSIF ( from_type = 'OTHER' ) THEN
2018 IF ( to_type = 'EMU' ) THEN
2019 -- Find out conversion rate from x_from_currency to EURO
2020 euro_code := get_euro_code;
2021 other_rate := get_other_closest_rate( x_from_currency,
2022 euro_code,
2023 x_conversion_date,
2024 x_conversion_type,
2025 x_max_roll_days );
2026
2027 -- Get conversion rate by converting OTHER -> EURO -> EMU
2028 return( other_rate * to_rate );
2029
2030 ELSIF ( to_type = 'EURO' ) THEN
2031 other_rate := get_other_closest_rate( x_from_currency,
2032 x_to_currency,
2033 x_conversion_date,
2034 x_conversion_type,
2035 x_max_roll_days );
2036 return( other_rate );
2037
2038 ELSIF ( to_type = 'OTHER' ) THEN
2039 other_rate := get_other_closest_rate( x_from_currency,
2040 x_to_currency,
2041 x_conversion_date,
2042 x_conversion_type,
2043 x_max_roll_days );
2044 return( other_rate );
2045 END IF;
2046 END IF;
2047
2048 EXCEPTION
2049 WHEN NO_RATE THEN
2050 return( -1 );
2051
2052 WHEN INVALID_CURRENCY THEN
2053 return( -2 );
2054
2055 END get_closest_rate_numerator_sql;
2056
2057 --
2058 -- Function
2059 -- get_closest_rate_numerator_sql
2060 --
2061 -- Purpose
2062 -- Returns the numerator we should use to get the conversion rate
2063 -- between the from currency and the functional currency of the
2064 -- ledgers.
2065 --
2066 -- History
2067 -- 11-MAY-98 W Wong Created
2068 --
2069 -- Arguments
2070 -- x_set_of_books_id Ledger id
2071 -- x_from_currency From currency
2072 -- x_conversion_date Conversion date
2073 -- x_conversion_type Conversion type
2074 -- x_max_roll_days Number of days to rollback for a rate
2075 --
2076 FUNCTION get_closest_rate_numerator_sql (
2077 x_set_of_books_id NUMBER,
2078 x_from_currency VARCHAR2,
2079 x_conversion_date DATE,
2080 x_conversion_type VARCHAR2 DEFAULT NULL,
2081 x_max_roll_days NUMBER) RETURN NUMBER IS
2082
2083 to_currency VARCHAR2(15);
2084 numerator NUMBER;
2085
2086 BEGIN
2087 -- Get to_currency from GL_LEDGERS, i.e. the functional currency
2088 SELECT currency_code
2089 INTO to_currency
2090 FROM GL_LEDGERS
2091 WHERE ledger_id = x_set_of_books_id;
2092
2093 -- Call get_closest_rate() with the to_currency we get from the Ledger
2094 numerator := get_closest_rate_numerator_sql( x_from_currency,
2095 to_currency,
2096 x_conversion_date,
2097 x_conversion_type,
2098 x_max_roll_days );
2099 return( numerator );
2100
2101 EXCEPTION
2102 WHEN NO_RATE THEN
2103 return( -1 );
2104
2105 WHEN INVALID_CURRENCY THEN
2106 return( -2 );
2107
2108 END get_closest_rate_numerator_sql;
2109
2110 --
2111 -- Function
2112 -- get_closest_rate_denom_sql
2113 --
2114 -- Purpose
2115 -- Returns the denominator we should use between the two currencies for
2116 -- a given conversion date and conversion type.
2117 --
2118 -- Return -1 if the NO_RATE exception is raised.
2119 -- -2 if the INVALID_CURRENCY exception is raised.
2120 --
2121 -- History
2122 -- 11-MAY-98 W Wong Created
2123 --
2124 -- Arguments
2125 -- x_from_currency From currency
2126 -- x_to_currency To currency
2127 -- x_conversion_date Conversion date
2128 -- x_conversion_type Conversion type
2129 -- x_max_roll_days Number of days to rollback for a rate
2130 --
2131 FUNCTION get_closest_rate_denom_sql (
2132 x_from_currency VARCHAR2,
2133 x_to_currency VARCHAR2,
2134 x_conversion_date DATE,
2135 x_conversion_type VARCHAR2 DEFAULT NULL,
2136 x_max_roll_days NUMBER) RETURN NUMBER IS
2137
2138 euro_code VARCHAR2(15);
2139 to_type VARCHAR2(8);
2140 from_type VARCHAR2(8);
2141 to_rate NUMBER;
2142 from_rate NUMBER;
2143 other_rate NUMBER;
2144 mau NUMBER; -- Value ignored in this function
2145
2146 BEGIN
2150 END IF;
2147 -- Check if both currencies are identical
2148 IF ( x_from_currency = x_to_currency ) THEN
2149 return( 1 );
2151
2152 -- Get currency information from the x_from_currency
2153 get_info ( x_from_currency, x_conversion_date, from_rate, mau,
2154 from_type );
2155
2156 -- Calculate the conversion rate according to both currency types
2157 IF ( from_type = 'EMU' ) THEN
2158 return( from_rate );
2159
2160 ELSE
2161
2162 return( 1 );
2163 END IF;
2164
2165 EXCEPTION
2166 WHEN NO_RATE THEN
2167 return( -1 );
2168
2169 WHEN INVALID_CURRENCY THEN
2170 return( -2 );
2171
2172 END get_closest_rate_denom_sql;
2173
2174 --
2175 -- Function
2176 -- get_closest_rate_denom_sql
2177 --
2178 -- Purpose
2179 -- Returns the denominator we should use to get the conversion rate
2180 -- between the from currency and the functional currency of the
2181 -- ledgers.
2182 --
2183 -- History
2184 -- 11-MAY-98 W Wong Created
2185 --
2186 -- Arguments
2187 -- x_set_of_books_id ledger id
2188 -- x_from_currency From currency
2189 -- x_conversion_date Conversion date
2190 -- x_conversion_type Conversion type
2191 -- x_max_roll_days Number of days to rollback for a rate
2192 --
2193 FUNCTION get_closest_rate_denom_sql (
2194 x_set_of_books_id NUMBER,
2195 x_from_currency VARCHAR2,
2196 x_conversion_date DATE,
2197 x_conversion_type VARCHAR2 DEFAULT NULL,
2198 x_max_roll_days NUMBER) RETURN NUMBER IS
2199
2200 to_currency VARCHAR2(15);
2201 denominator NUMBER;
2202
2203 BEGIN
2204 -- Get to_currency from GL_LEDGERS, i.e. the functional currency
2205 SELECT currency_code
2206 INTO to_currency
2207 FROM GL_LEDGERS
2208 WHERE ledger_id = x_set_of_books_id;
2209
2210 -- Call get_closest_rate_denom_sql() with the to_currency
2211 -- we get from the Ledger
2212 denominator := get_closest_rate_denom_sql( x_from_currency,
2213 to_currency,
2214 x_conversion_date,
2215 x_conversion_type,
2216 x_max_roll_days );
2217
2218 return( denominator );
2219
2220 END get_closest_rate_denom_sql;
2221
2222 --
2223 -- Procedure
2224 -- get_closest_triangulation_rate
2225 --
2226 -- Purpose
2227 -- Returns the numerator, denominator and the conversion rate between
2228 -- the two currencies for a given conversion date and conversion type.
2229 --
2230 -- History
2231 -- 11-MAY-98 W Wong Created
2232 --
2233 -- Arguments
2234 -- x_from_currency From currency
2235 -- x_to_currency To currency
2236 -- x_conversion_date Conversion date
2237 -- x_conversion_type Conversion type
2238 -- x_max_roll_days Number of days to rollback for a rate
2239 -- x_denominator Denominator to get conversion rate
2240 -- x_numerator Numerator to get conversion rate
2241 -- x_rate Conversion rate
2242 --
2243 PROCEDURE get_closest_triangulation_rate (
2244 x_from_currency VARCHAR2,
2245 x_to_currency VARCHAR2,
2246 x_conversion_date DATE,
2247 x_conversion_type VARCHAR2 DEFAULT NULL,
2248 x_max_roll_days NUMBER,
2249 x_denominator IN OUT NOCOPY NUMBER,
2250 x_numerator IN OUT NOCOPY NUMBER,
2251 x_rate IN OUT NOCOPY NUMBER ) IS
2252 BEGIN
2253 -- Get value of numerator and denominator
2254 x_numerator := get_closest_rate_numerator_sql( x_from_currency,
2255 x_to_currency,
2256 x_conversion_date,
2257 x_conversion_type,
2258 x_max_roll_days );
2259
2260 x_denominator := get_closest_rate_denom_sql ( x_from_currency,
2261 x_to_currency,
2262 x_conversion_date,
2263 x_conversion_type,
2264 x_max_roll_days );
2265
2266 -- Get conversion rate by using the x_numerator and x_denominator
2267 IF (( x_numerator > 0 ) AND ( x_denominator > 0 )) THEN
2268 x_rate := x_numerator / x_denominator;
2269
2270 ELSE
2271 IF (( x_numerator = -2 ) OR (x_denominator = -2 )) THEN
2272 raise INVALID_CURRENCY;
2273 ELSE
2274 raise NO_RATE;
2275 END IF;
2276 END IF;
2277 END get_closest_triangulation_rate;
2278
2279 --
2280 -- Procedure
2281 -- get_closest_triangulation_rate
2282 --
2283 -- Purpose
2284 -- Returns the numerator, denominator and the conversion rate
2285 -- between the from currency and the functional currency of the
2286 -- ledgers.
2287 --
2288 -- History
2289 -- 11-MAY-98 W Wong Created
2290 --
2291 -- Arguments
2292 -- x_set_of_books_id Ledger id
2293 -- x_from_currency From currency
2294 -- x_conversion_date Conversion date
2295 -- x_conversion_type Conversion type
2296 -- x_max_roll_days Number of days to rollback for a rate
2297 -- x_denominator Denominator to get conversion rate
2301 PROCEDURE get_closest_triangulation_rate (
2298 -- x_numerator Numerator to get conversion rate
2299 -- x_rate Conversion rate
2300 --
2302 x_set_of_books_id NUMBER,
2303 x_from_currency VARCHAR2,
2304 x_conversion_date DATE,
2305 x_conversion_type VARCHAR2 DEFAULT NULL,
2306 x_max_roll_days NUMBER,
2307 x_denominator IN OUT NOCOPY NUMBER,
2308 x_numerator IN OUT NOCOPY NUMBER,
2309 x_rate IN OUT NOCOPY NUMBER) IS
2310
2311 to_currency VARCHAR2(15);
2312
2313 BEGIN
2314 -- Get to_currency from GL_LEDGERS, i.e. the functional currency
2315 SELECT currency_code
2316 INTO to_currency
2317 FROM GL_LEDGERS
2318 WHERE ledger_id = x_set_of_books_id;
2319
2320 -- Get value of numerator and denominator
2321 x_numerator := get_closest_rate_numerator_sql( x_from_currency,
2322 to_currency,
2323 x_conversion_date,
2324 x_conversion_type,
2325 x_max_roll_days );
2326
2327 x_denominator := get_closest_rate_denom_sql( x_from_currency,
2328 to_currency,
2329 x_conversion_date,
2330 x_conversion_type,
2331 x_max_roll_days );
2332
2333 -- Get conversion rate by using the x_numerator and x_denominator
2334 IF (( x_numerator > 0 ) AND ( x_denominator > 0 )) THEN
2335 x_rate := x_numerator / x_denominator;
2336
2337 ELSE
2338 IF (( x_numerator = -2 ) OR (x_denominator = -2 )) THEN
2339 raise INVALID_CURRENCY;
2340 ELSE
2341 raise NO_RATE;
2342 END IF;
2343 END IF;
2344
2345 END get_closest_triangulation_rate;
2346
2347
2348 --
2349 -- Procedure
2350 -- convert_amount
2351 --
2352 -- Purpose
2353 -- Returns the numerator and denominator we should use to calculate
2354 -- the conversion rate between the two currencies, the actual
2355 -- conversion rate for a given conversion date and conversion type,
2356 -- and the amount converted from the from currency into the
2357 -- to currency for a given conversion date and conversion type.
2358 --
2359 -- History
2360 -- 02-JUN-98 W Wong Created
2361 --
2362 -- Arguments
2363 -- x_from_currency From currency
2364 -- x_to_currency To currency
2365 -- x_conversion_date Conversion date
2366 -- x_conversion_type Conversion type
2367 -- x_amount Amount to be converted from the from currency
2371 -- x_rate Conversion rate
2368 -- into the to currency
2369 -- x_denominator Denominator to get conversion rate
2370 -- x_numerator Numerator to get conversion rate
2372 -- x_converted_amount Converted amount
2373 --
2374 PROCEDURE convert_amount(
2375 x_from_currency VARCHAR2,
2376 x_to_currency VARCHAR2,
2377 x_conversion_date DATE,
2378 x_conversion_type VARCHAR2 DEFAULT NULL,
2379 x_amount NUMBER,
2380 x_converted_amount IN OUT NOCOPY NUMBER,
2381 x_denominator IN OUT NOCOPY NUMBER,
2382 x_numerator IN OUT NOCOPY NUMBER,
2383 x_rate IN OUT NOCOPY NUMBER ) IS
2384
2385 to_rate NUMBER;
2386 to_mau NUMBER;
2387 to_type VARCHAR2(8);
2388
2389 BEGIN
2390 -- Get currency information from the to_currency ( for use in rounding )
2391 get_info ( x_to_currency, x_conversion_date, to_rate, to_mau, to_type );
2392
2393 -- Get the conversion information
2394 get_triangulation_rate( x_from_currency,
2395 x_to_currency,
2396 x_conversion_date,
2397 x_conversion_type,
2398 x_denominator,
2399 x_numerator,
2400 x_rate );
2401
2402 -- Calculate the converted amount using triangulation method
2403 x_converted_amount := ( x_amount / x_denominator ) * x_numerator;
2404
2405 -- Rounding to the correct precision and minumum accountable units
2406 x_converted_amount := round( x_converted_amount / to_mau ) * to_mau;
2407
2408 END convert_amount;
2409
2410
2411 --
2412 -- Procedure
2413 -- convert_amount
2414 --
2415 -- Purpose
2416 -- Returns the numerator and denominator we should use to calculate
2417 -- the conversion rate, and the actual conversion rate between the
2418 -- from currency and the functional currency of the ledgers,
2419 -- and the amount converted from the from currency into the
2420 -- functional currency of that ledgers.
2421 --
2422 -- History
2423 -- 02-JUN-98 W Wong Created
2424 --
2425 -- Arguments
2426 -- x_set_of_books_id Ledger id
2427 -- x_from_currency From currency
2428 -- x_conversion_date Conversion date
2429 -- x_conversion_type Conversion type
2430 -- x_amount Amount to be converted from the from currency
2431 -- into the to currency
2432 -- x_denominator Denominator to get conversion rate
2433 -- x_numerator Numerator to get conversion rate
2434 -- x_rate Conversion rate
2435 -- x_converted_amount Converted amount
2436 --
2437 PROCEDURE convert_amount(
2438 x_set_of_books_id NUMBER,
2439 x_from_currency VARCHAR2,
2440 x_conversion_date DATE,
2441 x_conversion_type VARCHAR2 DEFAULT NULL,
2442 x_amount NUMBER,
2443 x_converted_amount IN OUT NOCOPY NUMBER,
2444 x_denominator IN OUT NOCOPY NUMBER,
2445 x_numerator IN OUT NOCOPY NUMBER,
2446 x_rate IN OUT NOCOPY NUMBER ) IS
2447
2448 to_currency VARCHAR2(15);
2449 to_rate NUMBER;
2450 to_mau NUMBER;
2451 to_type VARCHAR2(8);
2452
2453 BEGIN
2454 -- Get to_currency from GL_LEDGERS, i.e. the functional currency
2455 SELECT currency_code
2456 INTO to_currency
2457 FROM GL_LEDGERS
2458 WHERE ledger_id = x_set_of_books_id;
2459
2460 -- Get currency information from the to_currency ( for use in rounding )
2461 get_info ( to_currency, x_conversion_date, to_rate, to_mau, to_type );
2462
2463 -- Get the conversion information
2464 get_triangulation_rate( x_from_currency,
2465 to_currency,
2466 x_conversion_date,
2467 x_conversion_type,
2468 x_denominator,
2469 x_numerator,
2470 x_rate );
2471
2472 -- Calculate the converted amount using triangulation method
2473 x_converted_amount := ( x_amount / x_denominator ) * x_numerator;
2474
2475 -- Rounding to the correct precision and minumum accountable units
2476 x_converted_amount := round( x_converted_amount / to_mau ) * to_mau;
2477
2478 END convert_amount;
2479
2480
2481 --
2482 -- Function
2483 -- convert_closest_amount_sql
2484 --
2485 -- Purpose
2486 -- Returns the amount converted from the from currency into the
2487 -- to currency for a given conversion date and conversion type.
2488 -- The amount returned is rounded to the precision and minimum
2489 -- account unit of the to currency.
2490 --
2491 -- If x_conversion_type = 'User', and the relationship between the
2492 -- two currencies is not fixed, x_user_rate will be used as the
2493 -- conversion rate to convert the amount.
2494 --
2495 -- If there is a fixed relationship between the two currencies,
2496 -- the fixed rate will be used instead of the x_user_rate.
2497 --
2498 -- If x_convserion_type is not 'User', the routine will try to
2499 -- find the conversion rate using the given x_conversion_date and
2500 -- x_conversion_type.
2501 --
2502 -- If such a rate is not defined for the specified conversion_date, it
2503 -- searches backward for a rate defined for the same currencies and
2504 -- conversion type. It searches backward up to x_max_roll_days prior
2505 -- to the specified x_conversion_date.
2506 --
2507 -- Return -1 if the NO_RATE exception is raised.
2508 -- -2 if the INVALID_CURRENCY exception is raised.
2509 --
2510 -- History
2511 -- 10-SEP-98 W Wong Created
2512 --
2513 -- Arguments
2514 -- x_from_currency From currency
2515 -- x_to_currency To currency
2516 -- x_conversion_date Conversion date
2520 -- into the to currency
2517 -- x_conversion_type Conversion type
2518 -- x_user_rate User conversion rate
2519 -- x_amount Amount to be converted from the from currency
2521 -- x_max_roll_days Number of days to rollback for a rate
2522 --
2523 FUNCTION convert_closest_amount_sql (
2524 x_from_currency VARCHAR2,
2525 x_to_currency VARCHAR2,
2526 x_conversion_date DATE,
2527 x_conversion_type VARCHAR2 DEFAULT NULL,
2528 x_user_rate NUMBER,
2529 x_amount NUMBER,
2530 x_max_roll_days NUMBER ) RETURN NUMBER IS
2531
2532 to_rate NUMBER;
2533 to_mau NUMBER;
2534 to_type VARCHAR2(8);
2535 denominator NUMBER;
2536 numerator NUMBER;
2537 converted_amount NUMBER;
2538
2539 BEGIN
2540
2541 -- Check if both currencies are identical
2542 IF ( x_from_currency = x_to_currency ) THEN
2543 converted_amount := x_amount;
2544 return( converted_amount );
2545 END IF;
2546
2547 -- Get currency information from the to_currency ( for use in rounding )
2548 get_info ( x_to_currency, x_conversion_date, to_rate, to_mau, to_type );
2549
2550 --
2551 -- Find out the conversion rate that should be used.
2552 --
2553 IF ( x_conversion_type = 'User' ) THEN
2554 IF ( is_fixed_rate( x_from_currency, x_to_currency,
2555 x_conversion_date ) = 'N' ) THEN
2556 --
2557 -- Conversion type is 'User' and the relationship between both
2558 -- currencies is not a fixed relationship. The given user rate
2559 -- is used for the conversion.
2560 --
2561 denominator := 1;
2562 numerator := x_user_rate;
2563
2564 -- Calculate the converted amount using triangulation method
2565 converted_amount := ( x_amount / denominator ) * numerator;
2566
2567 -- Rounding to the correct precision and minumum accountable units
2568 converted_amount := round( converted_amount / to_mau ) * to_mau;
2569 return( converted_amount );
2570
2571 END IF;
2572 END IF;
2573
2574 --
2575 -- Conversion type is not 'User', or
2576 -- there is a fixed relationship between the currencies.
2577 -- Find out the conversion rate using the given conversion type
2578 -- and conversion date.
2579 --
2580 denominator := get_closest_rate_denom_sql( x_from_currency,
2581 x_to_currency,
2582 x_conversion_date,
2583 x_conversion_type,
2584 x_max_roll_days );
2585
2586 numerator := get_closest_rate_numerator_sql( x_from_currency,
2587 x_to_currency,
2588 x_conversion_date,
2589 x_conversion_type,
2590 x_max_roll_days );
2591
2592 IF (( numerator > 0 ) AND ( denominator > 0 )) THEN
2593 --
2594 -- We have a conversion rate to convert the amount
2595 --
2596
2597 -- Calculate the converted amount using triangulation method
2598 converted_amount := ( x_amount / denominator ) * numerator;
2599
2600 -- Rounding to the correct precision and minumum accountable units
2601 converted_amount := round( converted_amount / to_mau ) * to_mau;
2602 return( converted_amount );
2603
2604 ELSE
2605 IF (( numerator = -2 ) OR ( denominator = -2 )) THEN
2606 -- Either the x_from_currency or x_to_currency is invalid
2607 converted_amount := -2;
2608 return( converted_amount );
2609
2610 ELSE
2611 -- No conversion rate was found between the two currencies with
2612 -- the given conversion type and conversion date
2613 converted_amount := -1;
2614 return( converted_amount );
2615 END IF;
2616 END IF;
2617
2618 EXCEPTION
2619 WHEN INVALID_CURRENCY THEN
2620 converted_amount := -2;
2621 return( converted_amount );
2622
2623 END convert_closest_amount_sql;
2624
2625
2626 --
2627 -- Procedure
2628 -- convert_closest_amount
2629 --
2630 -- Purpose
2631 -- Returns the rate denominator, rate numerator and conversion rate
2632 -- the routine has used to convert the given amount. Also returns
2633 -- the converted amount.
2634 --
2635 -- If x_conversion_type = 'User', and the relationship between the
2636 -- two currencies is not fixed, x_user_rate will be used as the
2637 -- conversion rate to convert the amount.
2638 --
2639 -- If there is a fixed relationship between the two currencies,
2640 -- the fixed rate will be used instead of the x_user_rate.
2641 --
2642 -- If x_convserion_type is not 'User', the routine will try to
2643 -- find the conversion rate using the given x_conversion_date and
2644 -- x_conversion_type.
2645 --
2646 -- If such a rate is not defined for the specified conversion_date, it
2647 -- searches backward for a rate defined for the same currencies and
2648 -- conversion type. It searches backward up to x_max_roll_days prior
2649 -- to the specified x_conversion_date.
2650 --
2651 -- History
2652 -- 10-SEP-98 W Wong Created
2653 --
2654 -- Arguments
2655 -- x_from_currency From currency
2656 -- x_to_currency To currency
2657 -- x_conversion_date Conversion date
2658 -- x_conversion_type Conversion type
2659 -- x_user_rate User conversion rate
2660 -- x_amount Amount to be converted from the from currency
2661 -- into the to currency
2662 -- x_max_roll_days Number of days to rollback for a rate
2663 -- x_converted_amount Converted amount
2664 -- x_denominator Denominator to get conversion rate
2665 -- x_numerator Numerator to get conversion rate
2666 -- x_rate Conversion rate
2667 --
2668 PROCEDURE convert_closest_amount(
2669 x_from_currency VARCHAR2,
2670 x_to_currency VARCHAR2,
2671 x_conversion_date DATE,
2672 x_conversion_type VARCHAR2 DEFAULT NULL,
2673 x_user_rate NUMBER,
2674 x_amount NUMBER,
2675 x_max_roll_days NUMBER,
2676 x_converted_amount IN OUT NOCOPY NUMBER,
2677 x_denominator IN OUT NOCOPY NUMBER,
2678 x_numerator IN OUT NOCOPY NUMBER,
2679 x_rate IN OUT NOCOPY NUMBER ) IS
2680 to_rate NUMBER;
2681 to_mau NUMBER;
2682 to_type VARCHAR2(8);
2683 denominator NUMBER;
2684 numerator NUMBER;
2685 rate NUMBER;
2686 converted_amount NUMBER;
2687
2688 BEGIN
2689
2690 -- Check if both currencies are identical
2691 IF ( x_from_currency = x_to_currency ) THEN
2692 x_converted_amount := x_amount;
2693 x_denominator := 1;
2694 x_numerator := 1;
2695 x_rate := 1;
2696 return;
2697 END IF;
2698
2699 -- Get currency information from the to_currency ( for use in rounding )
2700 get_info ( x_to_currency, x_conversion_date, to_rate, to_mau, to_type );
2701
2702 --
2703 -- Find out the conversion rate that should be used.
2704 --
2705 IF ( x_conversion_type = 'User' ) THEN
2706 IF ( is_fixed_rate( x_from_currency, x_to_currency,
2707 x_conversion_date ) = 'N' ) THEN
2708 --
2709 -- Conversion type is 'USER' and the relationship between both
2710 -- currencies is not a fixed relationship. The given user rate
2711 -- is used for the conversion.
2712 --
2713 x_denominator := 1;
2714 x_numerator := x_user_rate;
2715 x_rate := x_user_rate;
2716
2717 -- Calculate the converted amount using triangulation method
2718 x_converted_amount := ( x_amount / x_denominator ) * x_numerator;
2719
2720 -- Rounding to the correct precision and minumum accountable units
2721 x_converted_amount := round( x_converted_amount / to_mau ) * to_mau;
2722 return;
2723
2724 END IF;
2725 END IF;
2726
2727 --
2728 -- Conversion type is not 'User', or
2729 -- there is a fixed relationship between the currencies.
2730 -- Find out the conversion rate using the given conversion type
2731 -- and conversion date.
2732 --
2733 get_closest_triangulation_rate( x_from_currency,
2734 x_to_currency,
2735 x_conversion_date,
2736 x_conversion_type,
2737 x_max_roll_days,
2738 denominator,
2739 numerator,
2740 rate );
2741
2742 -- Assign conversion info to output variables
2743 x_denominator := denominator;
2744 x_numerator := numerator;
2745 x_rate := rate;
2746
2747 -- Calculate the converted amount using triangulation method
2748 x_converted_amount := ( x_amount / x_denominator ) * x_numerator;
2749
2750 -- Rounding to the correct precision and minumum accountable units
2751 x_converted_amount := round( x_converted_amount / to_mau ) * to_mau;
2752
2753 END convert_closest_amount;
2754
2755 END gl_currency_api;
2756
[Home] [Help]