1 PACKAGE BODY AS_MC_PKG AS
2 /* $Header: asxmcmcb.pls 115.10 2002/12/13 11:01:06 nkamble ship $ */
3 -- Package
4 -- AS_MC_PKG
5 --
6 -- PURPOSE
7 -- Creates package body for multi-currency api
8 -- HISTORY
9 -- 22-Sep-98 J. Shang Created
10 -- 13-Nov-98 J. Shang Add three new functions : get_euro_info
11 -- format_amount
12 -- unformat_amount
13 -- 14-APR-00 SAGHOSH changed the code to match open and close cursor
14 -- BUG# 1270192
15
16 --G_NEG_MAX CONSTANT NUMBER := -9.99E125; -- -9.99999E125;
17
18 -- Start of Comments
19 --
20 -- API name : convert_amount_daily
21 -- Type :
22 -- Pre-reqs :
23 -- Function :
24 -- This api takes from and to currencies, conversion date, amount and Max Roll
25 -- days. It returns the amount converted into the appropriate currency. The
26 -- converted amount is calculated in compliance with the triangulation tule.
27 --
28 -- Parameters :
29 -- IN :
30 -- p_from_currency IN VARCHAR2 -- From currency
31 -- p_to_currency IN VARCHAR2 -- To currency
32 -- p_conversion_date IN DATE -- Conversion date
33 -- p_amount IN NUMBER -- Amount to be converted
34 -- p_max_roll_days IN NUMBER -- Maximum days to roll back for a rate
35 --
36 -- Version :
37 --
38 -- History :
39 -- 22-Sep-98 J. Shang Created
40 -- Note :
41 -- Using of p_max_roll_days in daily conversion:
42 -- When a rate for the conversion date is undefined, p_max_roll_days will be
43 -- used to find an alternitive rate to do the conversion.
44 -- 1. If it is a positive number, the function will look backward from the
45 -- conversion date for the most recent date on which a rate is defined.
46 -- 2. If it is a negative number, the function will look backward without any
47 -- date limit to find the most recent date on which a rate is defined.
48 -- 3. If it is zero, the funtion doesn't look backward. This is the default value.
49 -- The above definition follows rules defined in GL_CURRENCY_API.
50 -- End of Comments
51 --
52 FUNCTION convert_amount_daily (
53 p_from_currency IN VARCHAR2,
54 p_to_currency IN VARCHAR2,
55 p_conversion_date IN DATE,
56 p_amount IN NUMBER,
57 p_max_roll_days IN NUMBER DEFAULT 0) RETURN NUMBER IS
58
59 l_converted_amount NUMBER;
60 l_conversion_type VARCHAR2(30);
61 BEGIN
62 -- Check if both currencies are identical
63 IF (p_from_currency = p_to_currency) THEN
64 return(p_amount);
65 END IF;
66 -- Decide the value of AS_MC_DAILY_CONVERSION_TYPE is valid
67 l_conversion_type := FND_PROFILE.value('AS_MC_DAILY_CONVERSION_TYPE');
68 IF (l_conversion_type IS NULL) THEN
69 raise INVALID_DAILY_CONVERSION_TYPE;
70 END IF;
71 -- Call GL_CURRENCY_API to do conversion
72 l_converted_amount := gl_currency_api.convert_closest_amount_sql(p_from_currency,
73 p_to_currency,p_conversion_date,l_conversion_type,0,p_amount,p_max_roll_days);
74 IF l_converted_amount = -1 THEN
75 raise gl_currency_api.NO_RATE;
76 ELSIF l_converted_amount = -2 THEN
77 raise gl_currency_api.INVALID_CURRENCY;
78 END IF;
79 return(l_converted_amount);
80
81 END convert_amount_daily;
82
83 -- Start of Comments
84 --
85 -- API name : convert_amount_daily_sql
86 -- Type :
87 -- Pre-reqs :
88 -- Function :
89 -- This api takes from and to currencies, conversion date, amount and Max Roll
90 -- days. It returns the amount converted into the appropriate currency. The
91 -- converted amount is calculated in compliance with the triangulation tule.
92 -- This api has the same function as the above api but is used in SQL statement.
93 --
94 -- Parameters :
95 -- IN :
96 -- p_from_currency IN VARCHAR2 -- From currency
97 -- p_to_currency IN VARCHAR2 -- To currency
98 -- p_conversion_date IN DATE -- Conversion date
99 -- p_amount IN NUMBER -- Amount to be converted
100 -- p_max_roll_days IN NUMBER -- Maximum days to roll back for a rate
101 -- History :
102 -- 22-Sep-98 J. Shang Created
103 -- Version :
104 --
105 -- Note :
106 -- The using of p_max_roll_days follows the rules described in convert_amount_daily
107 -- End of Comments
108 --
109 FUNCTION convert_amount_daily_sql (
110 p_from_currency IN VARCHAR2,
111 p_to_currency IN VARCHAR2,
112 p_conversion_date IN DATE,
113 p_amount IN NUMBER,
114 p_max_roll_days IN NUMBER DEFAULT 0) RETURN NUMBER IS
115
116 l_converted_amount NUMBER;
117 l_conversion_type VARCHAR2(30);
118 BEGIN
119 -- Check if both currencies are identical
120 IF (p_from_currency = p_to_currency) THEN
121 return(p_amount);
122 END IF;
123 -- Decide the value of AS_MC_DAILY_CONVERSION_TYPE is valid
124 l_conversion_type := fnd_profile.value_WNPS('AS_MC_DAILY_CONVERSION_TYPE');
125 IF (l_conversion_type IS NULL) THEN
126 raise INVALID_DAILY_CONVERSION_TYPE;
127 END IF;
128 -- Call GL_CURRENCY_API to do conversion
129 l_converted_amount := gl_currency_api.convert_closest_amount_sql(p_from_currency,
130 p_to_currency,p_conversion_date,l_conversion_type,0,p_amount,p_max_roll_days);
131 IF l_converted_amount = -1 THEN
132 l_converted_amount := G_NEG_MAX;
133 ELSIF l_converted_amount = -2 THEN
134 l_converted_amount := G_NEG_MAX;
135 END IF;
136 RETURN(l_converted_amount);
137
138 EXCEPTION
139 WHEN INVALID_DAILY_CONVERSION_TYPE THEN
140 return(G_NEG_MAX);
141 WHEN OTHERS THEN
142 return(G_NEG_MAX);
143 END convert_amount_daily_sql;
144
145 -- Start of Comments
146 --
147 -- API name : convert_amount_daily_sql
148 -- Type :
149 -- Pre-reqs :
150 -- Function :
151 -- This api takes from and to currencies, conversion date, amount and Max Roll
152 -- days. It returns the amount converted into the appropriate currency. The
153 -- converted amount is calculated in compliance with the triangulation tule.
154 -- This api has the same function as the above api but is used in SQL statement.
155 --
156 -- Parameters :
157 -- IN :
158 -- p_from_currency IN VARCHAR2 -- From currency
159 -- p_to_currency IN VARCHAR2 -- To currency
160 -- p_conversion_date IN DATE -- Conversion date
161 -- p_amount IN NUMBER -- Amount to be converted
162 -- p_max_roll_days IN NUMBER -- Maximum days to roll back for a rate
163 -- p_conversion_type IN VARCHAR2 -- Conversion Type
164 -- History :
165 -- 14-MAR-00 XDING Created
166 -- Version :
167 --
168 -- Note :
169 -- The using of p_max_roll_days follows the rules described in convert_amount_daily
170 -- End of Comments
171 --
172 FUNCTION convert_amount_daily_sql (
173 p_from_currency IN VARCHAR2,
174 p_to_currency IN VARCHAR2,
175 p_conversion_date IN DATE,
176 p_amount IN NUMBER,
177 p_max_roll_days IN NUMBER DEFAULT 0,
178 p_conversion_type IN VARCHAR2 ) RETURN NUMBER IS
179
180 l_converted_amount NUMBER;
181 BEGIN
182 -- Check if both currencies are identical
183 IF (p_from_currency = p_to_currency) THEN
184 return(p_amount);
185 END IF;
186
187 IF (p_conversion_type IS NULL) THEN
188 raise INVALID_DAILY_CONVERSION_TYPE;
189 END IF;
190 -- Call GL_CURRENCY_API to do conversion
191 l_converted_amount := gl_currency_api.convert_closest_amount_sql(p_from_currency,
192 p_to_currency,p_conversion_date,p_conversion_type,0,p_amount,p_max_roll_days);
193 IF l_converted_amount = -1 THEN
194 l_converted_amount := G_NEG_MAX;
195 ELSIF l_converted_amount = -2 THEN
196 l_converted_amount := G_NEG_MAX;
197 END IF;
198 RETURN(l_converted_amount);
199
200 EXCEPTION
201 WHEN INVALID_DAILY_CONVERSION_TYPE THEN
202 return(G_NEG_MAX);
203 WHEN OTHERS THEN
204 return(G_NEG_MAX);
205 END convert_amount_daily_sql;
206
207 -- Start of Comments
208 --
209 -- API name : get_period_info
210 -- Type :
211 -- Pre-reqs :
212 -- Function :
213 -- This api takes conversion period and returns the information of this period,
214 -- such as, period type, start date and end date.
215 --
216 -- Parameters :
217 -- IN :
218 -- p_period IN VARCHAR2 -- Conversion period
219 -- OUT
220 -- x_period_type OUT VARCHAR2 -- Conversion period type
221 -- x_period_date OUT DATE -- Converion date
222 -- Version :
223 --
224 -- History :
225 -- 22-Sep-98 J. Shang Created
226 -- End of Comments
227 --
228 PROCEDURE get_period_info(
229 p_period IN VARCHAR2,
230 x_period_type OUT NOCOPY VARCHAR2,
231 x_period_date OUT NOCOPY DATE) IS
232
233 l_start_date DATE;
234 l_end_date DATE;
235 l_calendar VARCHAR2(15);
236 l_date_mapping VARCHAR2(1);
237 l_period VARCHAR2(15);
238 CURSOR l_period_csr IS
239 SELECT PERIOD_TYPE, START_DATE, END_DATE
240 FROM GL_PERIODS
241 WHERE PERIOD_SET_NAME = l_calendar
242 AND PERIOD_NAME = l_period;
243 BEGIN
244 l_calendar := FND_PROFILE.value_WNPS('AS_FORECAST_CALENDAR');
245 IF (l_calendar IS NULL) THEN
246 raise INVALID_FORECAST_CALENDAR;
247 END IF;
248 l_date_mapping := FND_PROFILE.value_WNPS('AS_MC_DATE_MAPPING_TYPE');
249 l_period := p_period;
250 OPEN l_period_csr;
251 FETCH l_period_csr INTO x_period_type, l_start_date, l_end_date;
252 IF NOT l_period_csr%FOUND THEN
253 --SAGHOSH 4/14/00
254 CLOSE l_period_csr ;
255 raise INVALID_PERIOD;
256 END IF;
257 --SAGHOSH 4/14/00
258 CLOSE l_period_csr ;
259 IF l_date_mapping = 'E' THEN
260 x_period_date := l_end_date;
261 ELSE
262 x_period_date := l_start_date;
263 END IF;
264 END get_period_info;
265
266 -- Start of Comments
267 --
268 -- API name : get_conversion_type
269 -- Type :
270 -- Pre-reqs :
271 -- Function :
272 -- This api takes conversion period type and returns the conversion type for
273 -- this period type.
274 --
275 -- Parameters :
276 -- IN :
277 -- p_period_type IN VARCHAR2 -- Conversion period type
278 -- Version :
279 --
280 -- History :
281 -- 22-Sep-98 J. Shang Created
282 -- 22-Oct-98 J. Shang Change name of the rate mapping table
283 -- End of Comments
284 --
285
286 FUNCTION get_conversion_type(
287 p_period_type IN VARCHAR2 ) RETURN VARCHAR2 IS
288
289 l_conversion_type VARCHAR2(30);
290 l_calendar VARCHAR2(15);
291 l_period_type VARCHAR2(15);
292 CURSOR l_conversion_csr IS
293 SELECT CONVERSION_TYPE
294 FROM AS_MC_TYPE_MAPPINGS
295 WHERE PERIOD_SET_NAME = l_calendar
296 AND PERIOD_TYPE = l_period_type;
297 BEGIN
298 l_calendar := FND_PROFILE.value_WNPS('AS_FORECAST_CALENDAR');
299 IF (l_calendar IS NULL) THEN
300 raise INVALID_FORECAST_CALENDAR;
301 END IF;
302 l_period_type := p_period_type;
303 OPEN l_conversion_csr;
304 FETCH l_conversion_csr INTO l_conversion_type;
305 IF NOT l_conversion_csr%FOUND THEN
306 --SAGHOSH 4/14/00
307 CLOSE l_conversion_csr;
308 raise INVALID_PERIOD;
309 END IF;
310 CLOSE l_conversion_csr;
311 return(l_conversion_type);
312 END get_conversion_type;
313
314 -- Start of Comments
315 --
316 -- API name : convert_amount_period
317 -- Type :
318 -- Pre-reqs :
319 -- Function :
320 -- This api takes from and to currencies, conversion period and amount
321 -- It returns the amount converted into the appropriate currency based on
322 -- pseudo period. The converted amount is calculated in compliance with
323 -- the triangulation tule.
324 --
325 -- Parameters :
326 -- IN :
327 -- p_from_currency IN VARCHAR2 -- From currency
328 -- p_to_currency IN VARCHAR2 -- To currency
329 -- p_conversion_period IN VARCHAR2 -- Conversion period
330 -- p_amount IN NUMBER -- Amount to be converted
331 --
332 -- Version :
333 --
334 -- History :
335 -- 22-Sep-98 J. Shang Created
336 -- End of Comments
337 --
338 FUNCTION convert_amount_period (
339 p_from_currency IN VARCHAR2,
340 p_to_currency IN VARCHAR2,
341 p_conversion_period IN VARCHAR2,
342 p_amount IN NUMBER) RETURN NUMBER IS
343
344 l_conversion_type VARCHAR2(30);
345 l_period_type VARCHAR2(15);
346 l_conversion_date DATE;
347 l_converted_amount NUMBER;
348 BEGIN
349 -- Check if both currencies are identical
350 IF (p_from_currency = p_to_currency) THEN
351 RETURN(p_amount);
352 END IF;
353 get_period_info(p_conversion_period, l_period_type, l_conversion_date);
354 l_conversion_type := get_conversion_type(l_period_type);
355 -- Call GL_CURRENCY_API to do conversion
356 l_converted_amount := gl_currency_api.convert_amount(p_from_currency,p_to_currency,
357 l_conversion_date, l_conversion_type, p_amount);
358 RETURN(l_converted_amount);
359 END convert_amount_period;
360
361 -- Start of Comments
362 --
363 -- API name : convert_amount_period_sql
364 -- Type :
365 -- Pre-reqs :
366 -- Function :
367 -- This api takes from and to currencies, conversion period and amount
368 -- It returns the amount converted into the appropriate currency based on
369 -- pseudo period. The converted amount is calculated in compliance with
370 -- the triangulation tule. This api has the same function as the above
371 -- api but is used in SQL statement.
372 --
373 -- Parameters :
374 -- IN :
375 -- p_from_currency IN VARCHAR2 -- From currency
376 -- p_to_currency IN VARCHAR2 -- To currency
377 -- p_conversion_period IN VARCHAR2 -- Conversion period
378 -- p_amount IN NUMBER -- Amount to be converted
379 --
380 -- Version :
381 --
382 -- History :
383 -- 22-Sep-98 J. Shang Created
384 -- End of Comments
385 --
386 FUNCTION convert_amount_period_sql (
387 p_from_currency IN VARCHAR2,
388 p_to_currency IN VARCHAR2,
389 p_conversion_period IN VARCHAR2,
390 p_amount IN NUMBER) RETURN NUMBER IS
391
392 l_conversion_type VARCHAR2(30);
393 l_period_type VARCHAR2(15);
394 l_conversion_date DATE;
395 l_converted_amount NUMBER;
396 BEGIN
397 -- Check if both currencies are identical
398 IF (p_from_currency = p_to_currency) THEN
399 RETURN(p_amount);
400 END IF;
401 -- API Body
402 get_period_info(p_conversion_period, l_period_type, l_conversion_date);
403 l_conversion_type := get_conversion_type(l_period_type);
404 l_converted_amount := gl_currency_api.convert_amount(p_from_currency,p_to_currency,
405 l_conversion_date, l_conversion_type, p_amount);
406 RETURN(l_converted_amount);
407 EXCEPTION
408 WHEN gl_currency_api.NO_RATE THEN
409 return(G_NEG_MAX);
410 WHEN gl_currency_api.INVALID_CURRENCY THEN
411 return(G_NEG_MAX);
412 WHEN INVALID_PERIOD THEN
413 return(G_NEG_MAX);
414 WHEN INVALID_FORECAST_CALENDAR THEN
415 return(G_NEG_MAX);
416 WHEN OTHERS THEN
417 return(G_NEG_MAX);
418 END convert_amount_period_sql;
419
420 -- Start of Comments
421 --
422 -- API name : convert_amount_period_sql
423 -- Type :
424 -- Pre-reqs :
425 -- Function :
426 -- This api takes from and to currencies, conversion date,type and amount
427 -- It returns the amount converted into the appropriate currency based on
428 -- pseudo period. The converted amount is calculated in compliance with
429 -- the triangulation tule. This api has the same function as the above
430 -- api but is used in SQL statement.
431 --
432 -- Parameters :
433 -- IN :
434 -- p_from_currency IN VARCHAR2 -- From currency
435 -- p_to_currency IN VARCHAR2 -- To currency
436 -- p_conversion_date IN DATE -- Conversion date
437 -- p_conversion_type IN VARCHAR2 -- Conversion type
438 -- p_amount IN NUMBER -- Amount to be converted
439 --
440 -- Version :
441 --
442 -- History :
443 -- 14-MAR-00 XDING Created
444 -- End of Comments
445 --
449 p_conversion_date IN DATE,
446 FUNCTION convert_amount_period_sql (
447 p_from_currency IN VARCHAR2,
448 p_to_currency IN VARCHAR2,
450 p_conversion_type IN VARCHAR2,
451 p_amount IN NUMBER) RETURN NUMBER IS
452
453 l_converted_amount NUMBER;
454 BEGIN
455 -- Check if both currencies are identical
456 IF (p_from_currency = p_to_currency) THEN
457 RETURN(p_amount);
458 END IF;
459 -- API Body
460 l_converted_amount := gl_currency_api.convert_amount(p_from_currency,p_to_currency,
461 p_conversion_date, p_conversion_type, p_amount);
462 RETURN(l_converted_amount);
463 EXCEPTION
464 WHEN gl_currency_api.NO_RATE THEN
465 return(G_NEG_MAX);
466 WHEN gl_currency_api.INVALID_CURRENCY THEN
467 return(G_NEG_MAX);
468 WHEN INVALID_PERIOD THEN
469 return(G_NEG_MAX);
470 WHEN INVALID_FORECAST_CALENDAR THEN
471 return(G_NEG_MAX);
472 WHEN OTHERS THEN
473 return(G_NEG_MAX);
474 END convert_amount_period_sql;
475
476 -- Start of Comments
477 --
478 -- API name : get_daily_rate
479 -- Type :
480 -- Pre-reqs :
481 -- Function :
482 -- This api takes from and to currencies, conversion date, and Max Roll
483 -- days. It returns the rate from the from currency to the to currency.
484 --
485 -- Parameters :
486 -- IN :
487 -- p_from_currency IN VARCHAR2 -- From currency
488 -- p_to_currency IN VARCHAR2 -- To currency
489 -- p_conversion_date IN DATE -- Conversion date
490 -- p_max_roll_days IN NUMBER -- Maximum days to roll back for a rate
491 --
492 -- Version :
493 --
494 -- Note :
495 -- The using of p_max_roll_days follows the rules described in convert_amount_daily
496 --
497 -- History :
498 -- 22-Sep-98 J. Shang Created
499 -- End of Comments
500 --
501 FUNCTION get_daily_rate (
502 p_from_currency IN VARCHAR2,
503 p_to_currency IN VARCHAR2,
504 p_conversion_date IN DATE,
505 p_max_roll_days IN NUMBER DEFAULT 0) RETURN NUMBER IS
506
507 l_conversion_rate NUMBER;
508 l_conversion_type VARCHAR2(30);
509
510 BEGIN
511 -- Check if both currencies are identical
512 IF (p_from_currency = p_to_currency) THEN
513 RETURN(1);
514 END IF;
515 -- API Body
516 l_conversion_type := FND_PROFILE.value('AS_MC_DAILY_CONVERSION_TYPE');
517 IF (l_conversion_type IS NULL) THEN
518 raise INVALID_DAILY_CONVERSION_TYPE;
519 END IF;
520 l_conversion_rate := gl_currency_api.get_closest_rate(p_from_currency, p_to_currency,
521 p_conversion_date, l_conversion_type, p_max_roll_days);
522 return(l_conversion_rate);
523 END get_daily_rate;
524
525 -- Start of Comments
526 --
527 -- API name : get_daily_rate_sql
528 -- Type :
529 -- Pre-reqs :
530 -- Function :
531 -- This api takes from and to currencies, conversion date, and Max Roll
532 -- days. It returns the rate from the from currency to the to currency.
533 -- This api has the same function as the above api but is used in SQL
534 -- statement.
535 --
536 -- Parameters :
537 -- IN :
538 -- p_from_currency IN VARCHAR2 -- From currency
539 -- p_to_currency IN VARCHAR2 -- To currency
540 -- p_conversion_date IN DATE --Conversion date
541 -- p_max_roll_days IN NUMBER --Maximum days to roll back for a rate
542 -- Version :
543 --
544 -- Note :
545 -- The using of p_max_roll_days follows the rules described in convert_amount_daily
546 -- History :
547 -- 22-Sep-98 J. Shang Created
548 -- End of Comments
549 --
550 FUNCTION get_daily_rate_sql (
551 p_from_currency IN VARCHAR2,
552 p_to_currency IN VARCHAR2,
553 p_conversion_date IN DATE,
554 p_max_roll_days IN NUMBER DEFAULT 0) RETURN NUMBER IS
555
556 l_conversion_rate NUMBER;
557 l_conversion_type VARCHAR2(30);
558 BEGIN
559 -- Check if both currencies are identical
560 IF (p_from_currency = p_to_currency) THEN
561 RETURN(1);
562 END IF;
563 -- API Body
564 l_conversion_type := FND_PROFILE.value('AS_MC_DAILY_CONVERSION_TYPE');
565 IF (l_conversion_type IS NULL) THEN
566 raise INVALID_DAILY_CONVERSION_TYPE;
567 END IF;
568 l_conversion_rate := gl_currency_api.get_closest_rate(p_from_currency, p_to_currency,
569 p_conversion_date, l_conversion_type, p_max_roll_days);
570 return(l_conversion_rate);
571 EXCEPTION
572 WHEN gl_currency_api.NO_RATE THEN
573 return(G_NEG_MAX);
574 WHEN gl_currency_api.INVALID_CURRENCY THEN
575 return(G_NEG_MAX);
576 WHEN INVALID_DAILY_CONVERSION_TYPE THEN
577 return(G_NEG_MAX);
578 WHEN OTHERS THEN
579 return(G_NEG_MAX);
580 END get_daily_rate_sql;
581
582 -- Start of Comments
583 --
584 -- API name : get_period_rate
585 -- Type :
586 -- Pre-reqs :
587 -- Function :
588 -- This api takes from and to currencies, conversion period. It returns the
589 -- rate from the from currency to the to currency based on pseudo period rate.
590 --
591 -- Parameters :
592 -- IN :
593 -- p_from_currency IN VARCHAR2 -- From currency
594 -- p_to_currency IN VARCHAR2 -- To currency
595 -- p_conversion_period IN VARCHAR2 -- Conversion period
596 -- Version :
597 --
598 -- History :
599 -- 22-Sep-98 J. Shang Created
600 -- End of Comments
601 --
602 FUNCTION get_period_rate (
603 p_from_currency IN VARCHAR2,
604 p_to_currency IN VARCHAR2,
605 p_conversion_period IN VARCHAR2) RETURN NUMBER IS
606
610 l_conversion_rate NUMBER;
607 l_period_type VARCHAR2(15);
608 l_conversion_date DATE;
609 l_conversion_type VARCHAR2(30);
611 BEGIN
612 -- Check if both currencies are identical
613 IF (p_from_currency = p_to_currency) THEN
614 RETURN(1);
615 END IF;
616 -- API Body
617 get_period_info(p_conversion_period, l_period_type, l_conversion_date);
618 l_conversion_type := get_conversion_type(l_period_type);
619 l_conversion_rate := gl_currency_api.get_rate(p_from_currency, p_to_currency,
620 l_conversion_date, l_conversion_type);
621
622 return(l_conversion_rate);
623 END get_period_rate;
624
625 -- Start of Comments
626 --
627 -- API name : get_period_rate_sql
628 -- Type :
629 -- Pre-reqs :
630 -- Function :
631 -- This api takes from and to currencies, conversion period. It returns the
632 -- rate from the from currency to the to currency based on pseudo period rate.
633 -- This api has the same function as the above api but is used in SQL
634 -- statement.
635 -- Parameters :
636 -- IN :
637 -- p_from_currency IN VARCHAR2 -- From currency
638 -- p_to_currency IN VARCHAR2 -- To currency
639 -- p_conversion_period IN VARCHAR2 -- Conversion period
640 -- Version :
641 --
642 -- History :
643 -- 22-Sep-98 J. Shang Created
644 -- End of Comments
645 --
646
647 FUNCTION get_period_rate_sql (
648 p_from_currency IN VARCHAR2,
649 p_to_currency IN VARCHAR2,
650 p_conversion_period IN VARCHAR2) RETURN NUMBER IS
651
652 l_period_type VARCHAR2(15);
653 l_conversion_date DATE;
654 l_conversion_type VARCHAR2(30);
655 l_conversion_rate NUMBER;
656 BEGIN
657 -- Check if both currencies are identical
658 IF (p_from_currency = p_to_currency) THEN
659 RETURN(1);
660 END IF;
661 -- API Body
662 get_period_info(p_conversion_period, l_period_type, l_conversion_date);
663 l_conversion_type := get_conversion_type(l_period_type);
664 l_conversion_rate := gl_currency_api.get_rate(p_from_currency, p_to_currency,
665 l_conversion_date, l_conversion_type);
666
667 return(l_conversion_rate);
668 EXCEPTION
669 WHEN gl_currency_api.NO_RATE THEN
670 return(G_NEG_MAX);
671 WHEN gl_currency_api.INVALID_CURRENCY THEN
672 return(G_NEG_MAX);
673 WHEN INVALID_PERIOD THEN
674 return(G_NEG_MAX);
675 WHEN INVALID_FORECAST_CALENDAR THEN
676 return(G_NEG_MAX);
677 WHEN OTHERS THEN
678 return(G_NEG_MAX);
679 END get_period_rate_sql;
680
681 -- Start of Comments
682 --
683 -- API name : daily_rate_exists
684 -- Type :
685 -- Pre-reqs :
686 -- Function :
687 -- This api takes from and to currencies, conversion date, and Max Roll
688 -- days. It checks if there is the rate from the from currency to the to
689 -- currency.
690 --
691 -- Parameters :
692 -- IN :
693 -- p_from_currency IN VARCHAR2 -- From currency
694 -- p_to_currency IN VARCHAR2 -- To currency
695 -- p_conversion_date IN DATE -- Conversion date
696 -- p_max_roll_days IN NUMBER -- Maximum days to roll back for a rate
697 --
698 -- Version :
699 --
700 -- Note :
701 -- The using of p_max_roll_days follows the rules described in convert_amount_daily
702 -- History :
703 -- 22-Sep-98 J. Shang Created
704 -- Note :
705 -- In this function, a cursor is defined to search a closest rate. The query
706 -- follows the searching rule defined in GL_CURRENCY_API. So, if the rule is
707 -- changed in GL_CURRENCY_API, this cursor has to be changed also.
708 -- End of Comments
709 --
710 FUNCTION daily_rate_exists (
711 p_from_currency IN VARCHAR2, -- From currency
712 p_to_currency IN VARCHAR2, -- To currency
713 p_conversion_date IN DATE, -- Conversion date
714 p_max_roll_days IN NUMBER DEFAULT 0, -- Maximum days to roll back for a rate
715 x_rate_date OUT NOCOPY DATE) RETURN VARCHAR2 IS -- The date on which there is rate defined
716 l_conversion_rate NUMBER;
717 l_conversion_type VARCHAR2(30);
718 l_exist_flag VARCHAR2(1);
719 l_closest_date DATE;
720 l_fix_rate BOOLEAN;
721 l_relationship VARCHAR2(18);
722 l_from_currency VARCHAR2(15);
723 l_to_currency VARCHAR2(15);
724 l_max_roll_days NUMBER;
725 l_conversion_date DATE;
726 CURSOR l_closest_rate_csr IS
727 SELECT conversion_date
728 FROM GL_DAILY_RATES
729 WHERE from_currency = l_from_currency
730 AND to_currency = l_to_currency
731 AND conversion_type = l_conversion_type
732 AND conversion_date BETWEEN
733 (decode(sign(l_max_roll_days), 1,
734 trunc(l_conversion_date)-l_max_roll_days,
735 -1, trunc(to_date('1000/01/01','YYYY/MM/DD'))))
736 AND trunc(l_conversion_date)
737 ORDER BY conversion_date DESC;
738 BEGIN
739 -- Check if both currencies are identical
740 IF (p_from_currency = p_to_currency) THEN
741 x_rate_date := p_conversion_date;
742 RETURN('Y');
743 END IF;
744 -- API Body
745 l_conversion_type := FND_PROFILE.value('AS_MC_DAILY_CONVERSION_TYPE');
746 IF (l_conversion_type IS NULL) THEN
747 raise INVALID_DAILY_CONVERSION_TYPE;
748 END IF;
749 l_exist_flag := gl_currency_api.rate_exists(p_from_currency, p_to_currency,
750 p_conversion_date, l_conversion_type);
751 IF (l_exist_flag = 'Y') THEN
752 x_rate_date := p_conversion_date;
753 ELSE
757 gl_currency_api.get_relation(p_from_currency, p_to_currency,
754 IF (p_max_roll_days = 0) THEN
755 l_exist_flag := 'N';
756 ELSE
758 p_conversion_date, l_fix_rate, l_relationship);
759 IF (INSTR(l_relationship,'OTHER') <> 0) THEN
760 l_from_currency := p_from_currency;
761 l_to_currency := p_to_currency;
762 l_max_roll_days := p_max_roll_days;
763 l_conversion_date := p_conversion_date;
764 OPEN l_closest_rate_csr;
765 FETCH l_closest_rate_csr INTO l_closest_date;
766 IF NOT l_closest_rate_csr%FOUND THEN
767 l_exist_flag := 'N';
768 ELSE
769 x_rate_date := l_closest_date;
770 l_exist_flag := 'Y';
771 END IF;
772 --SAGHOSH 4/14/00
773 CLOSE l_closest_rate_csr ;
774 ELSE
775 l_exist_flag := 'N';
776 END IF;
777 END IF;
778 END IF;
779 RETURN(l_exist_flag);
780 END daily_rate_exists;
781
782 -- Start of Comments
783 --
784 -- API name : period_rate_exists
785 -- Type :
786 -- Pre-reqs :
787 -- Function :
788 -- This api takes from and to currencies, conversion period. It checks if there
789 -- is the rate from the from currency to the to currency based on pseudo period rate.
790 -- Parameters :
791 -- IN :
792 -- p_from_currency IN VARCHAR2 -- From currency
793 -- p_to_currency IN VARCHAR2 -- To currency
794 -- p_conversion_period IN VARCHAR2 -- Conversion period
795 -- Version :
796 --
797 -- History :
798 -- 22-Sep-98 J. Shang Created
799 -- End of Comments
800 --
801 FUNCTION period_rate_exists (
802 p_from_currency IN VARCHAR2, -- From currency
803 p_to_currency IN VARCHAR2, -- To currency
804 p_conversion_period IN VARCHAR2) RETURN VARCHAR2 IS -- Conversion period
805
806 l_period_type VARCHAR2(15);
807 l_conversion_date DATE;
808 l_conversion_type VARCHAR2(30);
809 l_conversion_rate NUMBER;
810 l_exist_flag VARCHAR2(1);
811 BEGIN
812 -- Check if both currencies are identical
813 IF (p_from_currency = p_to_currency) THEN
814 RETURN('Y');
815 END IF;
816 -- API Body
817 get_period_info(p_conversion_period, l_period_type, l_conversion_date);
818 l_conversion_type := get_conversion_type(l_period_type);
819 l_exist_flag := gl_currency_api.rate_exists(p_from_currency, p_to_currency,
820 l_conversion_date, l_conversion_type);
821 return(l_exist_flag);
822 END period_rate_exists;
823
824 -- Start of Comments
825 --
826 -- API name : get_euro_info
827 -- Type :
828 -- Pre-reqs :
829 -- Function :
830 -- This api takes currency code and the effective date, it will get the currency
831 -- type information about the given currency
832 --
833 -- Parameters :
834 -- IN :
835 -- p_currency_code IN VARCHAR2 -- Currency to be checked
836 -- p_effective_date IN DATE -- Effective date
837 -- x_currency_type OUT VARCHAR2 -- Type of the currency. Euro currency, set to 'EURO'
838 -- Emu currency, set to 'EMU'
839 -- Other currencies,set to 'OTHER'
840 -- Invalid currency, set to NULL
841 -- x_conversion_rate OUT NUMBER -- Fixed rate for conversion
842 -- Version :
843 --
844 -- History :
845 -- 03-Nov-98 J. Shang Created
846 -- Note :
847 -- This is temporary solution for OSM, and the procedure follows the similar privatre
848 -- procedure defined in GL.
849 -- End of Comments
850 --
851 PROCEDURE get_euro_info(p_currency_code IN VARCHAR2,
852 p_effective_date IN DATE,
853 x_currency_type OUT NOCOPY VARCHAR2,
854 x_conversion_rate OUT NOCOPY NUMBER) IS
855 CURSOR l_currency_info_csr(l_currency_code VARCHAR2,l_effective_date DATE) IS
856 SELECT decode(derive_type,
857 'EURO', 'EURO',
858 'EMU', decode( sign( trunc(l_effective_date) -
859 trunc(derive_effective)),
860 -1, 'OTHER',
861 'EMU'),
862 'OTHER' ),
863 decode(derive_type, 'EURO', 1,
864 'EMU', derive_factor,
865 'OTHER',-1)
866 FROM FND_CURRENCIES
867 WHERE currency_code = l_currency_code;
868 BEGIN
869 OPEN l_currency_info_csr(p_currency_code,p_effective_date);
870 FETCH l_currency_info_csr INTO x_currency_type,x_conversion_rate;
871 IF l_currency_info_csr%NOTFOUND THEN
872 x_currency_type := NULL;
873 x_conversion_rate := -1;
874 END IF;
875 CLOSE l_currency_info_csr;
876 END get_euro_info;
877
878 -- Start of Comments
879 --
880 -- API name : format_amount
881 -- Type :
882 -- Pre-reqs :
883 -- Function :
884 -- This api takes currency code and amount to be formatted. It returns the amount
885 -- converted into the appropriate format. The format mask is get from foundation
886 -- library.
887 --
888 -- Parameters :
889 -- IN :
890 -- p_currency_code IN VARCHAR2 -- Currency code to get format mask
891 -- p_amount IN NUMBER -- Amount to be formatted
892 -- p_length IN NUMBER -- The maximum number of characters available to hold
893 -- the formatted value
894 --
895 -- Output:
896 -- If the p_length to hold the formatted value is not enough to hold the formatted
897 -- amount, p_length number of '#' will be returned. NULL will be returned when
898 -- p_length is negative or less than the length of p_amount.
899 -- Version :
900 --
901 -- History :
902 -- 2-Nov-98 J. Shang Created
903 -- Note :
907 FUNCTION format_amount(p_currency_code IN VARCHAR2,
904 --
905 -- End of Comments
906 --
908 p_amount IN NUMBER,
909 p_length IN NUMBER) RETURN VARCHAR2 IS
910 l_formatted_amount VARCHAR2(2000);
911 BEGIN
912 IF (p_length <= 0) OR (p_length < length(to_char(p_amount))) THEN
913 RETURN NULL;
914 END IF;
915 l_formatted_amount := to_char(p_amount,FND_CURRENCY.GET_FORMAT_MASK(p_currency_code,p_length));
916 RETURN l_formatted_amount;
917 END format_amount;
918
919 -- Start of Comments
920 --
921 -- API name : unformat_amount
922 -- Type :
923 -- Pre-reqs :
924 -- Function :
925 -- This api takes currency code and amount to be unformatted. It returns the amount
926 -- converted into a number. The format mask is get from foundation library. If the
927 -- number is using invalid format, NEG_MAX_NUM will be returned
928 -- Parameters :
929 -- IN :
930 -- p_currency_code IN VARCHAR2 -- Currency code to get format mask
931 -- p_amount IN NUMBER -- Amount to be formatted
932 --
933 -- Version :
934 --
935 -- History :
936 -- 12-Nov-98 J. Shang Created
937 -- Note :
938 --
939 -- End of Comments
940 --
941 FUNCTION unformat_amount(p_currency_code IN VARCHAR2,
942 p_amount IN VARCHAR2) RETURN NUMBER IS
943 l_unformatted_num NUMBER := G_NEG_MAX;
944 l_length NUMBER;
945 l_mask VARCHAR2(2000);
946 BEGIN
947 l_length := LENGTH(p_amount) * 2;
948 l_mask := FND_CURRENCY.GET_FORMAT_MASK(p_currency_code,l_length);
949 BEGIN
950 l_unformatted_num := to_number(p_amount);
951 l_unformatted_num := to_number(to_char(l_unformatted_num,l_mask),l_mask);
952 EXCEPTION
953 WHEN INVALID_NUMBER THEN
954 l_unformatted_num := G_NEG_MAX;
955 WHEN VALUE_ERROR THEN
956 l_unformatted_num := G_NEG_MAX;
957 END;
958 IF l_unformatted_num < 0 THEN
959 l_unformatted_num := to_number(p_amount,l_mask);
960 END IF;
961 RETURN l_unformatted_num;
962 EXCEPTION
963 WHEN INVALID_NUMBER THEN
964 l_unformatted_num := G_NEG_MAX;
965 RETURN l_unformatted_num;
966 WHEN VALUE_ERROR THEN
967 l_unformatted_num := G_NEG_MAX;
968 RETURN l_unformatted_num;
969 END unformat_amount;
970
971 -- Start of Comments
972 --
973 -- API name : convert_group_amounts_daily
974 -- Type :
975 -- Pre-reqs :
976 -- Function :
977 -- This api takes from currency, to currency, conversion date and a group of amount
978 -- to be converted. It returns the converted amount. This procedure is mainly to save
979 -- communication time between form and database
980 -- Parameters :
981 -- IN :
982 -- p_from_currency IN VARCHAR2
983 -- p_to_currency IN VARCHAR2
984 -- p_conversion_date IN DATE
985 -- p_amount1 IN NUMBER
986 -- p_amount2 IN NUMBER
987 -- p_amount3 IN NUMBER
988 -- p_amount4 IN NUMBER
989 -- p_amount5 IN NUMBER
990 -- OUT
991 -- p_out_amount1 OUT NUMBER
992 -- p_out_amount2 OUT NUMBER
993 -- p_out_amount3 OUT NUMBER
994 -- p_out_amount4 OUT NUMBER
995 -- p_out_amount5 OUT NUMBER
996 --
997 -- Version :
998 --
999 -- History :
1000 -- 11-Dec-98 J. Shang Created
1001 -- Note :
1002 -- The possible exceptions generated from this procedure are
1003 -- gl_currency_api.NO_RATE
1004 -- gl_currency_api.INVALID_CURRENCY
1005 -- as_mc_pkg.INVALID_DAILY_CONVERSION_TYPE
1006 -- End of Comments
1007 --
1008 PROCEDURE convert_group_amounts_daily (
1009 p_from_currency IN VARCHAR2,
1010 p_to_currency IN VARCHAR2,
1011 p_conversion_date IN DATE,
1012 p_amount1 IN NUMBER,
1013 p_amount2 IN NUMBER,
1014 p_amount3 IN NUMBER,
1015 p_amount4 IN NUMBER,
1016 p_amount5 IN NUMBER,
1017 p_out_amount1 OUT NOCOPY NUMBER,
1018 p_out_amount2 OUT NOCOPY NUMBER,
1019 p_out_amount3 OUT NOCOPY NUMBER,
1020 p_out_amount4 OUT NOCOPY NUMBER,
1021 p_out_amount5 OUT NOCOPY NUMBER) IS
1022 BEGIN
1023
1024 if p_amount1 is not NULL then
1025 p_out_amount1 := AS_MC_PKG.convert_amount_daily
1026 (p_from_currency,
1027 p_to_currency,
1028 p_conversion_date,
1029 p_amount1);
1030 end if;
1031
1032 if p_amount2 is not NULL then
1033 p_out_amount2 := AS_MC_PKG.convert_amount_daily
1034 (p_from_currency,
1035 p_to_currency,
1036 p_conversion_date,
1037 p_amount2);
1038 end if;
1039
1040 if p_amount3 is not NULL then
1041 p_out_amount3 := AS_MC_PKG.convert_amount_daily
1042 (p_from_currency,
1043 p_to_currency,
1044 p_conversion_date,
1045 p_amount3);
1046 end if;
1047
1048 if p_amount4 is not NULL then
1049 p_out_amount4 := AS_MC_PKG.convert_amount_daily
1050 (p_from_currency,
1051 p_to_currency,
1052 p_conversion_date,
1053 p_amount4);
1054 end if;
1055
1056 if p_amount5 is not NULL then
1057 p_out_amount5 := AS_MC_PKG.convert_amount_daily
1058 (p_from_currency,
1059 p_to_currency,
1063
1060 p_conversion_date,
1061 p_amount5);
1062 end if;
1064 END convert_group_amounts_daily;
1065
1066 END AS_MC_PKG;
[Home] [Help]