1 PACKAGE gl_currency_api AS
2 /* $Header: glustcrs.pls 120.5 2005/05/05 01:44:22 kvora ship $ */
3 --
4 -- Package
5 -- gl_currency_api
6 --
7 -- Purpose
8 --
9 -- This package will provide PL/SQL APIs for the following purposes:
10 -- o Determine if there is a fixed conversion rate between any two currencies
11 -- o Determine relationship between any two currencies
12 -- o Determine the currency code for the EURO currency
13 -- o Determine daily rate based on any two currencies, conversion type,
14 -- and conversion date information
15 -- o Determine the closest daily rate based on any two currencies,
16 -- conversion type, conversion date, and maximum number of days to roll
17 -- back
18 -- o Convert an amount to a different currency based on any two currencies,
19 -- conversion type, and conversion date information
20 --
21 -- History
22 -- 15-JUL-97 W Wong Created
23 --
24
25 --
26 -- Exceptions
27 --
28 -- User defined exceptions for gl_currency_api:
29 -- o INVALID_CURRENCY - One of the two currencies is invalid.
30 -- o NO_RATE - No rate exists between the two currencies for the
31 -- given date and conversion type.
32 -- o NO_DERIVE_TYPE - No derive type found for the specified currency
33 -- during the specified period.
34 --
35 INVALID_CURRENCY EXCEPTION;
36 NO_RATE EXCEPTION;
37 NO_DERIVE_TYPE EXCEPTION;
38
39 --
40 -- Function
41 -- is_fixed_rate
42 --
43 -- Purpose
44 -- Returns 'Y' if there is a fixed rate between the two currencies;
45 -- 'N' otherwise.
46 --
47 -- History
48 -- 15-JUL-97 W Wong Created
49 --
50 -- Arguments
51 -- x_from_currency From currency
52 -- x_to_currency To currency
53 -- x_effective_date Effective date
54 --
55 FUNCTION is_fixed_rate (
56 x_from_currency VARCHAR2,
57 x_to_currency VARCHAR2,
58 x_effective_date DATE ) RETURN VARCHAR2;
59 PRAGMA RESTRICT_REFERENCES(is_fixed_rate,WNDS,WNPS,RNPS);
60
61 --
62 -- Procedure
63 -- get_relation
64 --
65 -- Purpose
66 -- Returns the relationship between the two currencies given.
67 -- Also check if there is a fixed rate between the two currencies
68 -- on the effective date.
69 --
70 -- History
71 -- 15-JUL-97 W Wong Created
72 --
73 -- Arguments
74 -- x_from_currency From currency
75 -- x_to_currency To currency
76 -- x_effective_date Effective date
77 -- x_fixed_rate TRUE if there is a fixed rate between the
78 -- currencies on the effective date;
79 -- FALSE otherwise
80 -- x_relationship Relationship between the two currencies
81 --
82 PROCEDURE get_relation(
83 x_from_currency VARCHAR2,
84 x_to_currency VARCHAR2,
85 x_effective_date DATE,
86 x_fixed_rate IN OUT NOCOPY BOOLEAN,
87 x_relationship IN OUT NOCOPY VARCHAR2 );
88
89 --
90 -- FUNCTION
91 -- get_euro_code
92 --
93 -- Purpose
94 -- Returns the currency code for the EURO currency. We need to
95 -- select this currency code from fnd_currencies table because
96 -- the currency code for EURO has not been fixed at this time.
97 --
98 -- History
99 -- 24-JUL-97 W Wong Created
100 --
101 -- Arguments
102 -- None.
103 --
104 FUNCTION get_euro_code RETURN VARCHAR2;
105 PRAGMA RESTRICT_REFERENCES(get_euro_code,WNDS,WNPS,RNPS);
106
107 --
108 -- Function
109 -- get_rate
110 --
111 -- Purpose
112 -- Returns the rate between the two currencies for a given conversion
113 -- date and conversion type.
114 --
115 -- History
116 -- 15-JUL-97 W Wong Created
117 --
118 -- Arguments
119 -- x_from_currency From currency
120 -- x_to_currency To currency
121 -- x_conversion_date Conversion date
122 -- x_conversion_type Conversion type
123 --
124 FUNCTION get_rate (
125 x_from_currency VARCHAR2,
126 x_to_currency VARCHAR2,
127 x_conversion_date DATE,
128 x_conversion_type VARCHAR2 DEFAULT NULL ) RETURN NUMBER;
129 PRAGMA RESTRICT_REFERENCES(get_rate,WNDS,WNPS,RNPS);
130
131 --
132 -- Function
133 -- get_rate
134 --
135 -- Purpose
136 -- Returns the rate between the from currency and the functional
137 -- currency of the ledgers.
138 --
139 -- History
140 -- 15-JUL-97 W Wong Created
141 --
142 -- Arguments
143 -- x_set_of_books_id Ledger id
144 -- x_from_currency From currency
145 -- x_conversion_date Conversion date
146 -- x_conversion_type Conversion type
147 --
148 FUNCTION get_rate (
149 x_set_of_books_id NUMBER,
150 x_from_currency VARCHAR2,
151 x_conversion_date DATE,
152 x_conversion_type VARCHAR2 DEFAULT NULL ) RETURN NUMBER;
153 PRAGMA RESTRICT_REFERENCES(get_rate,WNDS,WNPS,RNPS);
154
155
156 --
157 -- Function
158 -- get_rate_sql
159 --
160 -- Purpose
161 -- Returns the rate between the two currencies for a given conversion
162 -- date and conversion type by calling get_rate().
163 --
164 -- Return -1 if the NO_RATE exception is raised in get_rate().
165 -- -2 if the INVALID_CURRENCY exception is raised in get_rate().
166 --
167 -- History
168 -- 04-DEC-97 W Wong Created
169 --
170 -- Arguments
171 -- x_from_currency From currency
172 -- x_to_currency To currency
173 -- x_conversion_date Conversion date
174 -- x_conversion_type Conversion type
175 --
176 FUNCTION get_rate_sql (
177 x_from_currency VARCHAR2,
178 x_to_currency VARCHAR2,
179 x_conversion_date DATE,
180 x_conversion_type VARCHAR2 DEFAULT NULL ) RETURN NUMBER;
181 PRAGMA RESTRICT_REFERENCES(get_rate_sql,WNDS,WNPS,RNPS);
182
183 --
184 -- Function
185 -- get_rate_sql
186 --
187 -- Purpose
188 -- Returns the rate between the from currency and the functional
189 -- currency of the ledger by calling get_rate().
190 --
191 -- Return -1 if the NO_RATE exception is raised in get_rate().
192 -- -2 if the INVALID_CURRENCY exception is raised in get_rate().
193 --
194 -- History
195 -- 04-DEC-97 W Wong Created
196 --
197 -- Arguments
198 -- x_set_of_books_id Ledger id
199 -- x_from_currency From currency
200 -- x_conversion_date Conversion date
201 -- x_conversion_type Conversion type
202 --
203 FUNCTION get_rate_sql (
204 x_set_of_books_id NUMBER,
205 x_from_currency VARCHAR2,
206 x_conversion_date DATE,
207 x_conversion_type VARCHAR2 DEFAULT NULL ) RETURN NUMBER;
208 PRAGMA RESTRICT_REFERENCES(get_rate_sql,WNDS,WNPS,RNPS);
209
210 --
211 -- Function
212 -- get_closest_rate
213 --
214 -- Purpose
215 -- Returns the rate between the two currencies for a given conversion
216 -- date and conversion type.
217 --
218 -- If such a rate is not defined for the specified conversion_date, it
219 -- searches backward for a rate defined for the same currencies and
220 -- conversion type. It searches backward up to x_max_roll_days prior
221 -- to the specified x_conversion_date.
222 --
223 -- History
224 -- 04-DEC-97 W Wong Created
225 --
226 -- Arguments
227 -- x_from_currency From currency
228 -- x_to_currency To currency
229 -- x_conversion_date Conversion date
230 -- x_conversion_type Conversion type
231 -- x_max_roll_days Number of days to rollback for a rate
232 --
233 FUNCTION get_closest_rate (
234 x_from_currency VARCHAR2,
235 x_to_currency VARCHAR2,
236 x_conversion_date DATE,
237 x_conversion_type VARCHAR2 DEFAULT NULL,
238 x_max_roll_days NUMBER ) RETURN NUMBER;
239 PRAGMA RESTRICT_REFERENCES(get_closest_rate,WNDS,WNPS,RNPS);
240
241 --
242 -- Function
243 -- get_closest_rate
244 --
245 -- Purpose
246 -- Returns the rate between the from currency and the functional currency
247 -- of the ledgers, for a given conversion date and conversion type.
248 --
249 -- If such a rate is not defined for the specified conversion_date, it
250 -- searches backward for a rate defined for the same currencies and
251 -- conversion type. It searches backward up to x_max_roll_days prior
252 -- to the specified x_conversion_date.
253 --
254 -- History
255 -- 04-DEC-97 W Wong Created
256 --
257 -- Arguments
258 -- x_set_of_books_id Ledger id
259 -- x_from_currency From currency
260 -- x_conversion_date Conversion date
261 -- x_conversion_type Conversion type
262 -- x_max_roll_days Number of days to rollback for a rate
263 --
264 FUNCTION get_closest_rate (
265 x_set_of_books_id NUMBER,
266 x_from_currency VARCHAR2,
267 x_conversion_date DATE,
268 x_conversion_type VARCHAR2 DEFAULT NULL,
269 x_max_roll_days NUMBER ) RETURN NUMBER;
270 PRAGMA RESTRICT_REFERENCES(get_closest_rate,WNDS,WNPS,RNPS);
271
272
273 --
274 -- Function
275 -- get_closest_rate_sql
276 --
277 -- Purpose
278 -- Returns the rate between the two currencies for a given conversion
279 -- date and conversion type by calling get_closest_rate().
280 --
281 -- If such a rate is not defined for the specified conversion_date, it
282 -- searches backward for a rate defined for the same currencies and
283 -- conversion type. It searches backward up to x_max_roll_days prior
284 -- to the specified x_conversion_date.
285 --
286 -- Return -1 if the NO_RATE exception is raised in get_closest_rate().
287 -- -2 if the INVALID_CURRENCY exception is raised in
288 -- get_closest_rate().
289 --
290 -- History
291 -- 04-DEC-97 W Wong Created
292 --
293 -- Arguments
294 -- x_from_currency From currency
295 -- x_to_currency To currency
296 -- x_conversion_date Conversion date
297 -- x_conversion_type Conversion type
298 -- x_max_roll_days Number of days to rollback for a rate
299 --
300 FUNCTION get_closest_rate_sql (
301 x_from_currency VARCHAR2,
302 x_to_currency VARCHAR2,
303 x_conversion_date DATE,
304 x_conversion_type VARCHAR2 DEFAULT NULL,
305 x_max_roll_days NUMBER ) RETURN NUMBER;
306 PRAGMA RESTRICT_REFERENCES(get_closest_rate_sql,WNDS,WNPS,RNPS);
307
308
309 --
310 -- Function
311 -- get_closest_rate_sql
312 --
313 -- Purpose
314 -- Returns the rate between the from currency and the functional currency
315 -- of the ledgers, for a given conversion date and conversion type
316 -- by calling get_closest_rate().
317 --
318 -- If such a rate is not defined for the specified conversion_date, it
319 -- searches backward for a rate defined for the same currencies and
320 -- conversion type. It searches backward up to x_max_roll_days prior
321 -- to the specified x_conversion_date.
322 --
323 -- Return -1 if the NO_RATE exception is raised in get_closest_rate().
324 -- -2 if the INVALID_CURRENCY exception is raised in
325 -- get_closest_rate().
326 --
327 -- History
328 -- 04-DEC-97 W Wong Created
329 --
330 -- Arguments
331 -- x_set_of_books_id Ledger id
332 -- x_from_currency From currency
333 -- x_conversion_date Conversion date
334 -- x_conversion_type Conversion type
335 -- x_max_roll_days Number of days to rollback for a rate
336 --
337 FUNCTION get_closest_rate_sql (
338 x_set_of_books_id NUMBER,
339 x_from_currency VARCHAR2,
340 x_conversion_date DATE,
341 x_conversion_type VARCHAR2 DEFAULT NULL,
342 x_max_roll_days NUMBER ) RETURN NUMBER;
343 PRAGMA RESTRICT_REFERENCES(get_closest_rate_sql,WNDS,WNPS,RNPS);
344
345 --
346 -- Function
347 -- convert_amount
348 --
349 -- Purpose
350 -- Returns the amount converted from the from currency into the
351 -- to currency for a given conversion date and conversion type.
352 -- The amount returned is rounded to the precision and minimum
353 -- account unit of the to currency.
354 --
355 -- History
356 -- 15-JUL-97 W Wong Created
357 --
358 -- Arguments
359 -- x_from_currency From currency
360 -- x_to_currency To currency
361 -- x_conversion_date Conversion date
362 -- x_conversion_type Conversion type
363 -- x_amount Amount to be converted from the from currency
364 -- into the to currency
365 --
366 FUNCTION convert_amount (
367 x_from_currency VARCHAR2,
368 x_to_currency VARCHAR2,
369 x_conversion_date DATE,
370 x_conversion_type VARCHAR2 DEFAULT NULL,
371 x_amount NUMBER ) RETURN NUMBER;
372 PRAGMA RESTRICT_REFERENCES(convert_amount,WNDS,WNPS,RNPS);
373
374 --
375 -- Function
376 -- convert_amount
377 --
378 -- Purpose
379 -- Returns the amount converted from the from currency into the
380 -- functional currency of that ledgers. The amount returned is
381 -- rounded to the precision and minimum account unit of the to currency.
382 --
383 -- History
384 -- 15-JUL-97 W Wong Created
385 --
386 -- Arguments
387 -- x_set_of_books_id Ledger id
388 -- x_from_currency From currency
389 -- x_conversion_date Conversion date
390 -- x_conversion_type Conversion type
394 --
391 -- x_amount Amount to be converted from the from currency
392 -- into the functional currency of the ledgers
393 --
395 FUNCTION convert_amount (
396 x_set_of_books_id NUMBER,
397 x_from_currency VARCHAR2,
398 x_conversion_date DATE,
399 x_conversion_type VARCHAR2 DEFAULT NULL,
400 x_amount NUMBER ) RETURN NUMBER;
401 PRAGMA RESTRICT_REFERENCES(convert_amount,WNDS,WNPS,RNPS);
402
403
404 --
405 -- Function
406 -- convert_amount_sql
407 --
408 -- Purpose
409 -- Returns the amount converted from the from currency into the
410 -- to currency for a given conversion date and conversion type by
411 -- calling convert_amount().
412 -- The amount returned is rounded to the precision and minimum
413 -- account unit of the to currency.
414 --
415 -- Return -1 if the NO_RATE exception is raised in convert_amount().
416 -- -2 if the INVALID_CURRENCY exception is raised in
417 -- convert_amount().
418 --
419 -- History
420 -- 04-DEC-97 W Wong Created
421 --
422 -- Arguments
423 -- x_from_currency From currency
424 -- x_to_currency To currency
425 -- x_conversion_date Conversion date
426 -- x_conversion_type Conversion type
427 -- x_amount Amount to be converted from the from currency
428 -- into the to currency
429 --
430 FUNCTION convert_amount_sql (
431 x_from_currency VARCHAR2,
432 x_to_currency VARCHAR2,
433 x_conversion_date DATE,
434 x_conversion_type VARCHAR2 DEFAULT NULL,
435 x_amount NUMBER ) RETURN NUMBER;
436 PRAGMA RESTRICT_REFERENCES(convert_amount_sql,WNDS,WNPS,RNPS);
437
438 --
439 -- Function
440 -- convert_amount_sql
441 --
442 -- Purpose
443 -- Returns the amount converted from the from currency into the
444 -- functional currency of that ledgers by calling convert_amount().
445 -- The amount returned is rounded to the precision and minimum account
446 -- unit of the to currency.
447 --
448 -- Return -1 if the NO_RATE exception is raised in convert_amount().
449 -- -2 if the INVALID_CURRENCY exception is raised in
450 -- convert_amount().
451 --
452 -- History
453 -- 04-DEC-97 W Wong Created
454 --
455 -- Arguments
456 -- x_set_of_books_id Ledger id
457 -- x_from_currency From currency
458 -- x_conversion_date Conversion date
459 -- x_conversion_type Conversion type
460 -- x_amount Amount to be converted from the from currency
461 -- into the functional currency of the ledgers
462 --
463 --
464 FUNCTION convert_amount_sql (
465 x_set_of_books_id NUMBER,
466 x_from_currency VARCHAR2,
467 x_conversion_date DATE,
468 x_conversion_type VARCHAR2 DEFAULT NULL,
469 x_amount NUMBER ) RETURN NUMBER;
470 PRAGMA RESTRICT_REFERENCES(convert_amount_sql,WNDS,WNPS,RNPS);
471
472
473 --
474 -- Function
475 -- get_derive_type
476 -- Purpose
477 -- Gets derive type for a currency.
478 --
479 -- NOTE: This function is for GL ONLY!
480 -- It'll returns GL specific derive type.
481 --
482 -- History
483 -- 08/07/97 K Chen Created
484 -- Arguments
485 -- ledger_id NUMBER
486 -- period VARCHAR2
487 -- curr_code VARCHAR2
488 -- Example
489 -- :Parameter.derive_type := glxrvsub_pkg.get_derive_type
490 -- (:Parameter.ledger_id, :OPTIONS.period_name,
491 -- :Parameter.func_curr_code);
492 -- Notes
493 --
494 FUNCTION get_derive_type (ledger_id NUMBER, period VARCHAR2, curr_code VARCHAR2)
495 RETURN VARCHAR2;
496 PRAGMA RESTRICT_REFERENCES(get_derive_type,WNDS,WNPS,RNPS);
497
498
499 --
500 -- Function
501 -- rate_exists
502 --
503 -- Purpose
504 -- Returns 'Y' if there is a conversion rate between the two currencies
505 -- for a given conversion date and conversion type;
506 -- 'N' otherwise.
507 --
508 -- History
509 -- 03-SEP-97 W Wong Created
510 --
511 -- Arguments
512 -- x_from_currency From currency
513 -- x_to_currency To currency
514 -- x_conversion_date Conversion date
515 -- x_conversion_type Conversion type
516 --
517 FUNCTION rate_exists (
518 x_from_currency VARCHAR2,
519 x_to_currency VARCHAR2,
520 x_conversion_date DATE,
521 x_conversion_type VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
522 PRAGMA RESTRICT_REFERENCES(rate_exists,WNDS,WNPS,RNPS);
523
524 --
525 -- Function
526 -- get_rate_numerator_sql
527 --
528 -- Purpose
529 -- Returns the numerator we should use to calculate the conversion
530 -- rate between the two currencies for a given conversion date and
531 -- conversion type.
532 --
533 -- Return -1 if the NO_RATE exception is raised.
534 -- -2 if the INVALID_CURRENCY exception is raised.
535 --
536 -- History
537 -- 11-MAY-98 W Wong Created
538 --
539 -- Arguments
540 -- x_from_currency From currency
544 --
541 -- x_to_currency To currency
542 -- x_conversion_date Conversion date
543 -- x_conversion_type Conversion type
545 FUNCTION get_rate_numerator_sql (
546 x_from_currency VARCHAR2,
547 x_to_currency VARCHAR2,
548 x_conversion_date DATE,
549 x_conversion_type VARCHAR2 DEFAULT NULL ) RETURN NUMBER;
550 PRAGMA RESTRICT_REFERENCES(get_rate_numerator_sql,WNDS,WNPS,RNPS);
551
552 --
553 -- Function
554 -- get_rate_numerator_sql
555 --
556 -- Purpose
557 -- Returns the numerator we should use to calculate the conversion rate
558 -- between the from currency and the functional currency of the
559 -- ledgers.
560 --
561 -- History
562 -- 11-MAY-98 W Wong Created
563 --
564 -- Arguments
565 -- x_set_of_books_id Ledger id
566 -- x_from_currency From currency
567 -- x_conversion_date Conversion date
568 -- x_conversion_type Conversion type
569 --
570 FUNCTION get_rate_numerator_sql (
571 x_set_of_books_id NUMBER,
572 x_from_currency VARCHAR2,
573 x_conversion_date DATE,
574 x_conversion_type VARCHAR2 DEFAULT NULL ) RETURN NUMBER;
575 PRAGMA RESTRICT_REFERENCES(get_rate_numerator_sql,WNDS,WNPS,RNPS);
576
577 --
578 -- Function
579 -- get_rate_denominator_sql
580 --
581 -- Purpose
582 -- Returns the denominator we should use to calculate the conversion
583 -- rate between the two currencies for a given conversion date and
584 -- conversion type.
585 --
586 -- Return -1 if the NO_RATE exception is raised.
587 -- -2 if the INVALID_CURRENCY exception is raised.
588 --
589 -- History
590 -- 11-MAY-98 W Wong Created
591 --
592 -- Arguments
593 -- x_from_currency From currency
594 -- x_to_currency To currency
595 -- x_conversion_date Conversion date
596 -- x_conversion_type Conversion type
597 --
598 FUNCTION get_rate_denominator_sql (
599 x_from_currency VARCHAR2,
600 x_to_currency VARCHAR2,
601 x_conversion_date DATE,
602 x_conversion_type VARCHAR2 DEFAULT NULL ) RETURN NUMBER;
603 PRAGMA RESTRICT_REFERENCES(get_rate_denominator_sql,WNDS,WNPS,RNPS);
604
605 --
606 -- Function
607 -- get_rate_denominator_sql
608 --
609 -- Purpose
610 -- Returns the denominator we should use to calculate the conversion rate
611 -- between the from currency and the functional currency of the
612 -- Ledgers.
613 --
614 -- History
615 -- 11-MAY-98 W Wong Created
616 --
617 -- Arguments
618 -- x_set_of_books_id Ledger id
619 -- x_from_currency From currency
620 -- x_conversion_date Conversion date
621 -- x_conversion_type Conversion type
622 --
623 FUNCTION get_rate_denominator_sql (
624 x_set_of_books_id NUMBER,
625 x_from_currency VARCHAR2,
626 x_conversion_date DATE,
627 x_conversion_type VARCHAR2 DEFAULT NULL ) RETURN NUMBER;
628 PRAGMA RESTRICT_REFERENCES(get_rate_denominator_sql,WNDS,WNPS,RNPS);
629
630 --
631 -- Procedure
632 -- get_triangulation_rate
633 --
634 -- Purpose
635 -- Returns the numerator and denominator we should use to calculate
636 -- the conversion rate between the two currencies, and the actual
637 -- conversion rate for a given conversion date and conversion type.
638 --
639 -- Note: When you are calculating the triangulation rate, you should
640 -- always divide by the x_denominator before you multiply by
641 -- the x_numerator.
642 --
643 -- History
644 -- 11-MAY-98 W Wong Created
645 --
646 -- Arguments
647 -- x_from_currency From currency
648 -- x_to_currency To currency
649 -- x_conversion_date Conversion date
650 -- x_conversion_type Conversion type
651 -- x_denominator Denominator to get conversion rate
652 -- x_numerator Numerator to get conversion rate
653 -- x_rate Conversion rate
654 --
655 Procedure get_triangulation_rate (
656 x_from_currency VARCHAR2,
657 x_to_currency VARCHAR2,
658 x_conversion_date DATE,
659 x_conversion_type VARCHAR2 DEFAULT NULL,
660 x_denominator IN OUT NOCOPY NUMBER,
661 x_numerator IN OUT NOCOPY NUMBER,
662 x_rate IN OUT NOCOPY NUMBER );
663
664 --
665 -- Procedure
666 -- get_triangulation_rate
667 --
668 -- Purpose
669 -- Returns the numerator and denominator we should use to calculate
670 -- the conversion rate, and the actual conversion rate between the
671 -- from currency and the functional currency of the Ledgers.
672 --
673 -- Note: When you are calculating the triangulation rate, you should
674 -- always divide by the x_denominator before you multiply by
675 -- the x_numerator.
676 --
677 -- History
678 -- 11-MAY-98 W Wong Created
679 --
680 -- Arguments
681 -- x_set_of_books_id Ledger id
682 -- x_from_currency From currency
683 -- x_conversion_date Conversion date
687 -- x_rate Conversion rate
684 -- x_conversion_type Conversion type
685 -- x_denominator Denominator to get conversion rate
686 -- x_numerator Numerator to get conversion rate
688 --
689 Procedure get_triangulation_rate (
690 x_set_of_books_id NUMBER,
691 x_from_currency VARCHAR2,
692 x_conversion_date DATE,
693 x_conversion_type VARCHAR2 DEFAULT NULL,
694 x_denominator IN OUT NOCOPY NUMBER,
695 x_numerator IN OUT NOCOPY NUMBER,
696 x_rate IN OUT NOCOPY NUMBER );
697
698 --
699 -- Function
700 -- get_closest_rate_numerator_sql
701 --
702 -- Purpose
703 -- Returns the numerator we should use between the two currencies for
704 -- a given conversion date and conversion type.
705 --
706 -- If such a rate is not defined for the specified conversion_date, it
707 -- searches backward for a rate defined for the same currencies and
708 -- conversion type. It searches backward up to x_max_roll_days prior
709 -- to the specified x_conversion_date.
710 --
711 -- Return -1 if the NO_RATE exception is raised.
712 -- -2 if the INVALID_CURRENCY exception is raised.
713 --
714 -- History
715 -- 11-MAY-98 W Wong Created
716 --
717 -- Arguments
718 -- x_from_currency From currency
719 -- x_to_currency To currency
720 -- x_conversion_date Conversion date
721 -- x_conversion_type Conversion type
722 -- x_max_roll_days Number of days to rollback for a rate
723 --
724 FUNCTION get_closest_rate_numerator_sql (
725 x_from_currency VARCHAR2,
726 x_to_currency VARCHAR2,
727 x_conversion_date DATE,
728 x_conversion_type VARCHAR2 DEFAULT NULL,
729 x_max_roll_days NUMBER) RETURN NUMBER;
730 PRAGMA RESTRICT_REFERENCES(get_closest_rate_numerator_sql,WNDS,WNPS,RNPS);
731
732 --
733 -- Function
734 -- get_closest_rate_numerator_sql
735 --
736 -- Purpose
737 -- Returns the numerator we should use to get the conversion rate
738 -- between the from currency and the functional currency of the
739 -- Ledgers.
740 --
741 -- If such a rate is not defined for the specified conversion_date, it
742 -- searches backward for a rate defined for the same currencies and
743 -- conversion type. It searches backward up to x_max_roll_days prior
744 -- to the specified x_conversion_date.
745 --
746 -- History
747 -- 11-MAY-98 W Wong Created
748 --
749 -- Arguments
750 -- x_set_of_books_id Ledger id
751 -- x_from_currency From currency
752 -- x_conversion_date Conversion date
753 -- x_conversion_type Conversion type
754 -- x_max_roll_days Number of days to rollback for a rate
755 --
756 FUNCTION get_closest_rate_numerator_sql (
757 x_set_of_books_id NUMBER,
758 x_from_currency VARCHAR2,
759 x_conversion_date DATE,
760 x_conversion_type VARCHAR2 DEFAULT NULL,
761 x_max_roll_days NUMBER) RETURN NUMBER;
762 PRAGMA RESTRICT_REFERENCES(get_closest_rate_numerator_sql,WNDS,WNPS,RNPS);
763
764 --
765 -- Function
766 -- get_closest_rate_denom_sql
767 --
768 -- Purpose
769 -- Returns the denominator we should use between the two currencies for
770 -- a given conversion date and conversion type.
771 --
772 -- If such a rate is not defined for the specified conversion_date, it
773 -- searches backward for a rate defined for the same currencies and
774 -- conversion type. It searches backward up to x_max_roll_days prior
775 -- to the specified x_conversion_date.
776 --
777 -- Return -1 if the NO_RATE exception is raised.
778 -- -2 if the INVALID_CURRENCY exception is raised.
779 --
780 -- History
781 -- 11-MAY-98 W Wong Created
782 --
783 -- Arguments
784 -- x_from_currency From currency
785 -- x_to_currency To currency
786 -- x_conversion_date Conversion date
787 -- x_conversion_type Conversion type
788 -- x_max_roll_days Number of days to rollback for a rate
789 --
790 FUNCTION get_closest_rate_denom_sql (
791 x_from_currency VARCHAR2,
792 x_to_currency VARCHAR2,
793 x_conversion_date DATE,
794 x_conversion_type VARCHAR2 DEFAULT NULL,
795 x_max_roll_days NUMBER) RETURN NUMBER;
796 PRAGMA RESTRICT_REFERENCES(get_closest_rate_denom_sql,WNDS,WNPS,RNPS);
797
798 --
799 -- Function
800 -- get_closest_rate_denom_sql
801 --
802 -- Purpose
803 -- Returns the denominator we should use to get the conversion rate
804 -- between the from currency and the functional currency of the
805 -- Ledgers.
806 --
807 -- If such a rate is not defined for the specified conversion_date, it
808 -- searches backward for a rate defined for the same currencies and
809 -- conversion type. It searches backward up to x_max_roll_days prior
810 -- to the specified x_conversion_date.
811 --
812 -- History
813 -- 11-MAY-98 W Wong Created
814 --
815 -- Arguments
816 -- x_set_of_books_id Ledger id
817 -- x_from_currency From currency
821 --
818 -- x_conversion_date Conversion date
819 -- x_conversion_type Conversion type
820 -- x_max_roll_days Number of days to rollback for a rate
822 FUNCTION get_closest_rate_denom_sql (
823 x_set_of_books_id NUMBER,
824 x_from_currency VARCHAR2,
825 x_conversion_date DATE,
826 x_conversion_type VARCHAR2 DEFAULT NULL,
827 x_max_roll_days NUMBER) RETURN NUMBER;
828 PRAGMA RESTRICT_REFERENCES(get_closest_rate_denom_sql,WNDS,WNPS,RNPS);
829
830 --
831 -- Procedure
832 -- get_closest_triangulation_rate
833 --
834 -- Purpose
835 -- Returns the numerator, denominator and the conversion rate between
836 -- the two currencies for a given conversion date and conversion type.
837 --
838 -- If such a rate is not defined for the specified conversion_date, it
839 -- searches backward for a rate defined for the same currencies and
840 -- conversion type. It searches backward up to x_max_roll_days prior
841 -- to the specified x_conversion_date.
842 --
843 -- History
844 -- 11-MAY-98 W Wong Created
845 --
846 -- Arguments
847 -- x_from_currency From currency
848 -- x_to_currency To currency
849 -- x_conversion_date Conversion date
850 -- x_conversion_type Conversion type
851 -- x_max_roll_days Number of days to rollback for a rate
852 -- x_denominator Denominator to get conversion rate
853 -- x_numerator Numerator to get conversion rate
854 -- x_rate Conversion rate
855 --
856 PROCEDURE get_closest_triangulation_rate (
857 x_from_currency VARCHAR2,
858 x_to_currency VARCHAR2,
859 x_conversion_date DATE,
860 x_conversion_type VARCHAR2 DEFAULT NULL,
861 x_max_roll_days NUMBER,
862 x_denominator IN OUT NOCOPY NUMBER,
863 x_numerator IN OUT NOCOPY NUMBER,
864 x_rate IN OUT NOCOPY NUMBER );
865
866 --
867 -- Procedure
868 -- get_closest_triangulation_rate
869 --
870 -- Purpose
871 -- Returns the numerator, denominator and the conversion rate
872 -- between the from currency and the functional currency of the
873 -- Ledgers.
874 --
875 -- If such a rate is not defined for the specified conversion_date, it
876 -- searches backward for a rate defined for the same currencies and
877 -- conversion type. It searches backward up to x_max_roll_days prior
878 -- to the specified x_conversion_date.
879 --
880 -- History
881 -- 11-MAY-98 W Wong Created
882 --
883 -- Arguments
884 -- x_set_of_books_id Ledger id
885 -- x_from_currency From currency
886 -- x_conversion_date Conversion date
887 -- x_conversion_type Conversion type
888 -- x_max_roll_days Number of days to rollback for a rate
889 -- x_denominator Denominator to get conversion rate
890 -- x_numerator Numerator to get conversion rate
891 -- x_rate Conversion rate
892 --
893 PROCEDURE get_closest_triangulation_rate (
894 x_set_of_books_id NUMBER,
895 x_from_currency VARCHAR2,
896 x_conversion_date DATE,
897 x_conversion_type VARCHAR2 DEFAULT NULL,
898 x_max_roll_days NUMBER,
899 x_denominator IN OUT NOCOPY NUMBER,
900 x_numerator IN OUT NOCOPY NUMBER,
901 x_rate IN OUT NOCOPY NUMBER );
902
903 --
904 -- Procedure
905 -- convert_amount
906 --
907 -- Purpose
908 -- Returns the numerator and denominator we should use to calculate
909 -- the conversion rate between the two currencies, the actual
910 -- conversion rate for a given conversion date and conversion type,
911 -- and the amount converted from the from currency into the
912 -- to currency for a given conversion date and conversion type.
913 --
914 -- History
915 -- 02-JUN-98 W Wong Created
916 --
917 -- Arguments
918 -- x_from_currency From currency
919 -- x_to_currency To currency
920 -- x_conversion_date Conversion date
921 -- x_conversion_type Conversion type
922 -- x_amount Amount to be converted from the from currency
923 -- into the to currency
924 -- x_denominator Denominator to get conversion rate
925 -- x_numerator Numerator to get conversion rate
926 -- x_rate Conversion rate
927 -- x_converted_amount Converted amount
928 --
929 PROCEDURE convert_amount(
930 x_from_currency VARCHAR2,
931 x_to_currency VARCHAR2,
932 x_conversion_date DATE,
933 x_conversion_type VARCHAR2 DEFAULT NULL,
934 x_amount NUMBER,
935 x_converted_amount IN OUT NOCOPY NUMBER,
936 x_denominator IN OUT NOCOPY NUMBER,
937 x_numerator IN OUT NOCOPY NUMBER,
938 x_rate IN OUT NOCOPY NUMBER );
939
940 --
941 -- Procedure
942 -- convert_amount
943 --
944 -- Purpose
945 -- Returns the numerator and denominator we should use to calculate
946 -- the conversion rate, and the actual conversion rate between the
947 -- from currency and the functional currency of the Ledgers,
948 -- and the amount converted from the from currency into the
952 -- 02-JUN-98 W Wong Created
949 -- functional currency of that Ledgers.
950 --
951 -- History
953 --
954 -- Arguments
955 -- x_set_of_books_id Ledger id
956 -- x_from_currency From currency
957 -- x_conversion_date Conversion date
958 -- x_conversion_type Conversion type
959 -- x_amount Amount to be converted from the from currency
960 -- into the to currency
961 -- x_denominator Denominator to get conversion rate
962 -- x_numerator Numerator to get conversion rate
963 -- x_rate Conversion rate
964 -- x_converted_amount Converted amount
965 --
966 PROCEDURE convert_amount(
967 x_set_of_books_id NUMBER,
968 x_from_currency VARCHAR2,
969 x_conversion_date DATE,
970 x_conversion_type VARCHAR2 DEFAULT NULL,
971 x_amount NUMBER,
972 x_converted_amount IN OUT NOCOPY NUMBER,
973 x_denominator IN OUT NOCOPY NUMBER,
974 x_numerator IN OUT NOCOPY NUMBER,
975 x_rate IN OUT NOCOPY NUMBER );
976
977 --
978 -- Function
979 -- convert_closest_amount_sql
980 --
981 -- Purpose
982 -- Returns the amount converted from the from currency into the
983 -- to currency for a given conversion date and conversion type.
984 -- The amount returned is rounded to the precision and minimum
985 -- account unit of the to currency.
986 --
987 -- If x_conversion_type = 'User', and the relationship between the
988 -- two currencies is not fixed, x_user_rate will be used as the
989 -- conversion rate to convert the amount.
990 --
991 -- If there is a fixed relationship between the two currencies,
992 -- the fixed rate will be used instead of the x_user_rate.
993 --
994 -- If x_convserion_type is not 'User', the routine will try to
995 -- find the conversion rate using the given x_conversion_date and
996 -- x_conversion_type.
997 --
998 -- If such a rate is not defined for the specified conversion_date, it
999 -- searches backward for a rate defined for the same currencies and
1000 -- conversion type. It searches backward up to x_max_roll_days prior
1001 -- to the specified x_conversion_date.
1002 --
1003 -- Return -1 if the NO_RATE exception is raised.
1004 -- -2 if the INVALID_CURRENCY exception is raised.
1005 --
1006 -- History
1007 -- 10-SEP-98 W Wong Created
1008 --
1009 -- Arguments
1010 -- x_from_currency From currency
1011 -- x_to_currency To currency
1012 -- x_conversion_date Conversion date
1013 -- x_conversion_type Conversion type
1014 -- x_user_rate User conversion rate
1015 -- x_amount Amount to be converted from the from currency
1016 -- into the to currency
1017 -- x_max_roll_days Number of days to rollback for a rate
1018 --
1019 FUNCTION convert_closest_amount_sql (
1020 x_from_currency VARCHAR2,
1021 x_to_currency VARCHAR2,
1022 x_conversion_date DATE,
1023 x_conversion_type VARCHAR2 DEFAULT NULL,
1024 x_user_rate NUMBER,
1025 x_amount NUMBER,
1026 x_max_roll_days NUMBER ) RETURN NUMBER;
1027 PRAGMA RESTRICT_REFERENCES(convert_closest_amount_sql,WNDS,WNPS,RNPS);
1028
1029 --
1030 -- Procedure
1031 -- convert_closest_amount
1032 --
1033 -- Purpose
1034 -- Returns the rate denominator, rate numerator and conversion rate
1035 -- the routine has used to convert the given amount. Also returns
1036 -- the converted amount.
1037 --
1038 -- If x_conversion_type = 'User', and the relationship between the
1039 -- two currencies is not fixed, x_user_rate will be used as the
1040 -- conversion rate to convert the amount.
1041 --
1042 -- If there is a fixed relationship between the two currencies,
1043 -- the fixed rate will be used instead of the x_user_rate.
1044 --
1045 -- If x_convserion_type is not 'User', the routine will try to
1046 -- find the conversion rate using the given x_conversion_date and
1047 -- x_conversion_type.
1048 --
1049 -- If such a rate is not defined for the specified conversion_date, it
1050 -- searches backward for a rate defined for the same currencies and
1051 -- conversion type. It searches backward up to x_max_roll_days prior
1052 -- to the specified x_conversion_date.
1053 --
1054 -- History
1055 -- 10-SEP-98 W Wong Created
1056 --
1057 -- Arguments
1058 -- x_from_currency From currency
1059 -- x_to_currency To currency
1060 -- x_conversion_date Conversion date
1061 -- x_conversion_type Conversion type
1062 -- x_user_rate User conversion rate
1063 -- x_amount Amount to be converted from the from currency
1064 -- into the to currency
1065 -- x_max_roll_days Number of days to rollback for a rate
1066 -- x_converted_amount Converted amount
1067 -- x_denominator Denominator to get conversion rate
1068 -- x_numerator Numerator to get conversion rate
1069 -- x_rate Conversion rate
1070 --
1071 PROCEDURE convert_closest_amount(
1072 x_from_currency VARCHAR2,
1073 x_to_currency VARCHAR2,
1074 x_conversion_date DATE,
1078 x_max_roll_days NUMBER,
1075 x_conversion_type VARCHAR2 DEFAULT NULL,
1076 x_user_rate NUMBER,
1077 x_amount NUMBER,
1079 x_converted_amount IN OUT NOCOPY NUMBER,
1080 x_denominator IN OUT NOCOPY NUMBER,
1081 x_numerator IN OUT NOCOPY NUMBER,
1085
1082 x_rate IN OUT NOCOPY NUMBER );
1083
1084
1086 END gl_currency_api;