1 Package Body FII_CURRENCY AS
2 /* $Header: FIICACUB.pls 120.14 2005/10/30 05:07:46 appldev noship $ */
3
4
5 g_prim_currency_code constant varchar2(15) := bis_common_parameters.get_currency_code;
6 g_prim_rate_type constant varchar2(30) := bis_common_parameters.get_rate_type;
7 g_sec_currency_code constant varchar2(15) := bis_common_parameters.get_secondary_currency_code;
8 g_sec_rate_type constant varchar2(30) := bis_common_parameters.get_secondary_rate_type;
9 g_treasury_rate_type constant Varchar2(30) := bis_common_parameters.get_treasury_rate_type;
10
11 /* Below mentioned three variable are for Oracle Internal only */
12 g_fii_fixed_curr_it constant varchar2(40) := fnd_profile.value('FII_FIXED_CURRENCY_IT');
13 g_fii_left_curr constant varchar2(15) := substrb(g_fii_fixed_curr_it,1,INSTR(g_fii_fixed_curr_it,'=')-1);
14 g_fii_right_curr constant varchar2(15):=substrb(g_fii_fixed_curr_it,INSTR(g_fii_fixed_curr_it,'=')+1);
15 /* Below mentioned three variables for maintaining the cahce */
16 g_stack_count number:=0;
17 g_stack_count_sec number:=0;
18 g_max_stack_size constant number:=20;
19
20 TYPE g_primary_cache_Rate IS RECORD
21 (from_currency varchar2(15),
22 exchange_date Date,
23 rate number);
24
25 TYPE g_secondary_cache_Rate IS RECORD
26 (from_currency varchar2(15),
27 exchange_date Date,
28 rate number);
29
30 TYPE g_prim_cache_rate1 IS VARRAY(20) OF g_primary_cache_rate;
31
32 TYPE g_sec_cache_rate1 IS VARRAY(20) OF g_secondary_cache_rate;
33
34
35 g_prim_cache_rate g_prim_cache_rate1;
36
37 g_sec_cache_rate g_sec_cache_rate1;
38
39 -- --------------------------------------------------------------------------
40 -- Name : return_prim_rate_if_in_cache
41 -- Type : Function
42 -- Description : Returns rate from the PL/SQL table if the given combination
43 -- of from currency, exchange date
44 -- exists in the PL/SQL table.
45 -- Returns 0 if the combination doesn't exist.
46 -----------------------------------------------------------------------------
47
48 function return_prim_rate_if_in_cache(p_from_currency varchar2,
49 p_exchange_date date)
50 return number is
51
52 begin
53
54 if g_prim_cache_rate.exists(1) then
55 -- check whether the rate is present
56 FOR i in g_prim_cache_rate.FIRST..g_prim_cache_rate.LAST LOOP
57 if ((g_prim_cache_rate(i).from_currency=p_from_currency)
58 AND (g_prim_cache_rate(i).exchange_date=p_exchange_date)) then
59 --combination exists return the rate.
60 return (g_prim_cache_rate(i).rate);
61
62 end if;
63 END LOOP;
64 return 0;
65 else
66 --pl/sql is empty
67 return 0;
68 end if;
69 EXCEPTION
70 WHEN OTHERS THEN
71 return 0;
72 end;
73
74 -- --------------------------------------------------------------------------
75 -- Name : cache_prim_rate
76 -- Type : Procedure
77 -- Description : Caches rate information
78 -----------------------------------------------------------------------------
79 procedure cache_prim_rate(p_from_currency varchar2,
80 p_exchange_date date,
81 p_rate number) IS
82
83 begin
84 if (p_rate <0) then
85 null; -- do nothing
86 else
87
88
89 if (g_stack_count >= g_max_stack_size) then
90 g_stack_count:=0;
91 end if;
92
93 g_stack_count:=g_stack_count+1;
94
95 g_prim_cache_rate(g_stack_count).from_currency:=p_from_currency;
96 g_prim_cache_rate(g_stack_count).exchange_date:=p_exchange_date;
97 g_prim_cache_rate(g_stack_count).rate:=p_rate;
98
99 end if;
100 EXCEPTION
101 WHEN OTHERS THEN
102 null;
103 end;
104
105 -- --------------------------------------------------------------------------
106 -- Name : return_sec_rate_if_in_cache
107 -- Type : Function
108 -- Description : Returns rate from the PL/SQL table if the given combination
109 -- of from currency, exchange date
110 -- exists in the PL/SQL table.
111 -- Returns 0 if the combination doesn't exist.
112 -----------------------------------------------------------------------------
113
114 function return_sec_rate_if_in_cache(p_from_currency varchar2,
115 p_exchange_date date)
116 return number is
117
118 begin
119
120 if g_sec_cache_rate.exists(1) then
121 -- check whether the rate is present
122 FOR i in g_sec_cache_rate.FIRST..g_sec_cache_rate.LAST LOOP
123 if ((g_sec_cache_rate(i).from_currency=p_from_currency)
124 AND (g_sec_cache_rate(i).exchange_date=p_exchange_date)) then
125 --combination exists return the rate.
126 return (g_sec_cache_rate(i).rate);
127
128 end if;
129 END LOOP;
130 return 0;
131 else
132 --pl/sql is empty
133 return 0;
134 end if;
135 EXCEPTION
136 WHEN OTHERS THEN
137 return 0;
138
139 end;
140
141 -- --------------------------------------------------------------------------
142 -- Name : cache_sec_rate
143 -- Type : Procedure
144 -- Description : Caches rate information
145 -----------------------------------------------------------------------------
146 procedure cache_sec_rate(p_from_currency varchar2,
147 p_exchange_date date,
148 p_rate number) IS
149
150 begin
151 if (p_rate<0) then
152 null;
153 else
154
155 if (g_stack_count_sec >= g_max_stack_size) then
156 g_stack_count_sec:=0;
157 end if;
158
159 g_stack_count_sec:=g_stack_count_sec+1;
160
161 g_sec_cache_rate(g_stack_count_sec).from_currency:=p_from_currency;
162 g_sec_cache_rate(g_stack_count_sec).exchange_date:=p_exchange_date;
163 g_sec_cache_rate(g_stack_count_sec).rate:=p_rate;
164
165 end if;
166 EXCEPTION
167 WHEN OTHERS THEN
168 null;
169 end;
170
171
172 Function get_global_rate_primary(
173 p_from_currency_code VARCHAR2,
174 p_exchange_date DATE) RETURN NUMBER
175 PARALLEL_ENABLE IS
176
177 l_global_currency_code VARCHAR2(30);
178 l_global_rate_type VARCHAR2(15);
179 l_max_roll_days NUMBER;
180 l_exchange_date DATE;
181 rate NUMBER;
182
183 begin
184
185 IF (compare_currency_codes(p_from_currency_code,g_prim_currency_code)=1) THEN
186 return 1;
187 END IF;
188
189
190 l_max_roll_days := 32;
191
192 l_exchange_date := p_exchange_date;
193
194
195
196 IF (p_from_currency_code = 'EUR' AND l_exchange_date < to_date('01/01/1999','DD/MM/RRRR') )
197 THEN l_exchange_date := to_date('01/01/1999','DD/MM/RRRR');
198 ELSIF (g_prim_currency_code = 'EUR' AND l_exchange_date < to_date('01/01/1999','DD/MM/RRRR') )
199 THEN l_exchange_date := to_date('01/01/1999','DD/MM/RRRR');
200 END IF;
201
202
203
204 rate := GL_CURRENCY_API.get_closest_rate_sql (
205 p_from_currency_code,
206 g_prim_currency_code,
207 l_exchange_date,
208 g_prim_rate_type,
209 l_max_roll_days);
210
211
212
213 IF (p_from_currency_code = 'EUR'
214 AND p_exchange_date < to_date('01/01/1999','DD/MM/RRRR')
215 AND rate = -1 )
216 THEN rate := -3;
217 ELSIF (g_prim_currency_code = 'EUR'
218 AND p_exchange_date < to_date('01/01/1999','DD/MM/RRRR')
219 AND rate = -1 )
220 THEN rate := -3;
221 END IF;
222
223 RETURN (rate);
224
225
226 EXCEPTION
227 WHEN OTHERS THEN
228 return null;
229
230
231 END get_global_rate_primary;
232
233
234 Function get_global_rate_secondary(
235 p_from_currency_code VARCHAR2,
236 p_exchange_date DATE) RETURN NUMBER
237 PARALLEL_ENABLE IS
238
239 l_global_currency_code VARCHAR2(30);
240 l_global_rate_type VARCHAR2(15);
241 l_max_roll_days NUMBER;
242 l_exchange_date DATE;
243 rate NUMBER;
244
245 begin
246
247 IF (compare_currency_codes(p_from_currency_code,g_sec_currency_code)=1) THEN
248 return 1;
249 END IF;
250
251 l_max_roll_days := 32;
252
253 l_exchange_date := p_exchange_date;
254
255
256 IF (p_from_currency_code = 'EUR' AND l_exchange_date < to_date('01/01/1999','DD/MM/RRRR') )
257 THEN l_exchange_date := to_date('01/01/1999','DD/MM/RRRR');
258 ELSIF (g_sec_currency_code = 'EUR' AND l_exchange_date < to_date('01/01/1999','DD/MM/RRRR') )
259 THEN l_exchange_date := to_date('01/01/1999','DD/MM/RRRR');
260 END IF;
261
262 IF (g_sec_currency_code IS NULL) THEN
263 rate := 1;
264
265 ELSE rate := GL_CURRENCY_API.get_closest_rate_sql (
266 p_from_currency_code,
267 g_sec_currency_code,
268 l_exchange_date,
269 g_sec_rate_type,
270 l_max_roll_days);
271
272 END IF;
273
274 IF (p_from_currency_code = 'EUR'
275 AND p_exchange_date < to_date('01/01/1999','DD/MM/RRRR')
276 AND rate = -1 )
277 THEN rate := -3;
278 ELSIF (g_sec_currency_code = 'EUR'
279 AND p_exchange_date < to_date('01/01/1999','DD/MM/RRRR')
280 AND rate = -1 )
281 THEN rate := -3;
282 END IF;
283
284 RETURN (rate);
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 return null;
289
290
291 END get_global_rate_secondary;
292
293 --*************************************************
294 Function convert_global_amt_primary(
295 p_from_currency_code VARCHAR2,
296 p_from_amount NUMBER,
297 p_exchange_date DATE) RETURN NUMBER
298 PARALLEL_ENABLE IS
299
300 l_converted_amount NUMBER := -1;
301 l_global_currency_code VARCHAR2(30);
302 l_global_rate_type VARCHAR2(15);
303 l_max_roll_days NUMBER;
304
305 BEGIN
306
307 l_global_currency_code := g_prim_currency_code;
308 l_global_rate_type := g_prim_rate_type;
309 l_max_roll_days := 32;
310
311 IF (l_global_currency_code IS NULL) THEN
312 l_converted_amount := p_from_amount;
313 ELSIF (p_from_amount is not NULL and
314 compare_currency_codes(p_from_currency_code,l_global_currency_code)=1) then
315 l_converted_amount := p_from_amount;
316 ELSIF (p_from_amount IS NULL) then
317 l_converted_amount := to_number(NULL);
318 ELSIF (p_exchange_date IS NULL OR
319 p_from_currency_code IS NULL) THEN
320 l_converted_amount := -1;
321 ELSE
322 l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql (
323 p_from_currency_code,
324 l_global_currency_code,
325 p_exchange_date,
326 l_global_rate_type,
327 1,
328 p_from_amount,
329 l_max_roll_days);
330 END IF;
331
332 return (l_converted_amount);
333
334 EXCEPTION
335 WHEN NO_DATA_FOUND THEN
336 raise_application_error(-20000,
337 'No data found,' ||
338 'from_amount='||p_from_amount||','||
339 'from_curr='||p_from_currency_code||','||
340 'date='||to_char(p_exchange_date)||','||
341 'rate_type='||l_global_rate_type);
342 WHEN OTHERS THEN
343 raise_application_error(-20000,
344 'Other error,' ||
345 'from_amount='||p_from_amount||','||
346 'from_curr='||p_from_currency_code||','||
347 'date='||to_char(p_exchange_date)||','||
348 'rate_type='||l_global_rate_type);
349
350 END convert_global_amt_primary;
351
352 --*************************************************
353 Function convert_global_amt_secondary(
354 p_from_currency_code VARCHAR2,
355 p_from_amount NUMBER,
356 p_exchange_date DATE) RETURN NUMBER
357 PARALLEL_ENABLE IS
358
359 l_converted_amount NUMBER := -1;
360 l_global_currency_code VARCHAR2(30);
361 l_global_rate_type VARCHAR2(15);
362 l_max_roll_days NUMBER;
363
364 BEGIN
365
366 l_global_currency_code := g_sec_currency_code;
367 l_global_rate_type := g_sec_rate_type;
368 l_max_roll_days := 32;
369
370 IF (l_global_currency_code IS NULL) THEN
371 l_converted_amount := p_from_amount;
372 ELSIF (p_from_amount is not NULL and
373 compare_currency_codes(p_from_currency_code,l_global_currency_code)=1) then
374 l_converted_amount := p_from_amount;
375 ELSIF (p_from_amount IS NULL) then
376 l_converted_amount := to_number(NULL);
377 ELSIF (p_exchange_date IS NULL OR
378 p_from_currency_code IS NULL) THEN
379 l_converted_amount := -1;
380 ELSE
381 l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql (
382 p_from_currency_code,
383 l_global_currency_code,
384 p_exchange_date,
385 l_global_rate_type,
386 1,
387 p_from_amount,
388 l_max_roll_days);
389 END IF;
390
391 return (l_converted_amount);
392 EXCEPTION
393 WHEN NO_DATA_FOUND THEN
394 raise_application_error(-20000,
395 'No data found,' ||
396 'from_amount='||p_from_amount||','||
397 'from_curr='||p_from_currency_code||','||
398 'date='||to_char(p_exchange_date)||','||
399 'rate_type='||l_global_rate_type);
400 WHEN OTHERS THEN
401 raise_application_error(-20000,
402 'Other error,' ||
403 'from_amount='||p_from_amount||','||
404 'from_curr='||p_from_currency_code||','||
405 'date='||to_char(p_exchange_date)||','||
406 'rate_type='||l_global_rate_type);
407
408 END convert_global_amt_secondary;
409
410 --**********************************************************
411 FUNCTION get_mau_primary RETURN NUMBER PARALLEL_ENABLE IS
412 l_mau NUMBER;
413 l_warehouse_currency_code VARCHAR2(15);
414 l_exchange_date DATE;
415 rate NUMBER;
416
417 BEGIN
418
419 l_warehouse_currency_code := bis_common_parameters.get_currency_code;
420
421 select nvl( curr.minimum_accountable_unit, power( 10, (-1 * curr.precision)))
422 into l_mau
423 from gl_currencies curr
424 where curr.currency_code = l_warehouse_currency_code;
425
426 if l_mau is null then
427 l_mau := 0.01; -- assign default value if null;
431
428 elsif l_mau = 0 then
429 l_mau := 1;
430 end if;
432 return l_mau;
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 return null;
437
438 END get_mau_primary;
439
440 --**********************************************************
441 FUNCTION get_mau_secondary RETURN NUMBER PARALLEL_ENABLE IS
442 l_mau NUMBER;
443 l_warehouse_currency_code VARCHAR2(15);
444 BEGIN
445
446 l_warehouse_currency_code := bis_common_parameters.get_secondary_currency_code;
447
448 select nvl( curr.minimum_accountable_unit, power( 10, (-1 * curr.precision)))
449 into l_mau
450 from gl_currencies curr
451 where curr.currency_code = l_warehouse_currency_code;
452
453 if l_mau is null then
454 l_mau := 0.01; -- assign default value if null;
455 elsif l_mau = 0 then
456 l_mau := 1;
457 end if;
458
459 return l_mau;
460
461 EXCEPTION
462 WHEN OTHERS THEN
463 return null;
464
465 END get_mau_secondary;
466
467 --**********************************************************
468
469 -- -------------------------------------------------------------------
470 -- Name: get_rate
471 -- Parameters: From Currency
472 -- To Currency
473 -- Exchange Date
474 -- Exchange Rate Type
475 -- Desc: Given the from currency, to currency, exchange date and rate type,
476 -- this API will call the GL_CURRENCY_API.get_closest_rate_sql API to
477 -- get the currency conversion rate.
478 -- Output: Conversion rate, data type: NUMBER
479 -- Returns -1 if no rate exists
480 -- Returns -2 if the From Currency is an invalid currency
481 -- Returns -3 when one of the currency is EUR and the
482 -- exchange date is before Jan 1,1999 and no rate exists
483 -- on Jan 1,1999 between the two currencies.
484 -- Returns -4 for other exceptions.
485 -- Error: If any sql errors occurs, an exception is raised.
486 -- --------------------------------------------------------------------
487 Function get_rate(
488 p_from_currency_code VARCHAR2,
489 p_to_currency_code VARCHAR2,
490 p_exchange_date DATE,
491 p_exchange_rate_type VARCHAR2) RETURN NUMBER
492 PARALLEL_ENABLE IS
493
494 l_exchange_date DATE;
495 rate NUMBER;
496
497 l_max_roll_days NUMBER := 32;
498
499 begin
500
501 l_exchange_date := p_exchange_date;
505 rate := 1;
502
503
504 IF (compare_currency_codes(p_from_currency_code,p_to_currency_code)=1) THEN
506
507 ELSE
508
509 IF (p_from_currency_code = 'EUR' AND l_exchange_date < to_date('01/01/1999','DD/MM/RRRR') )
510 THEN l_exchange_date := to_date('01/01/1999','DD/MM/RRRR');
511 ELSIF (p_to_currency_code = 'EUR' AND l_exchange_date < to_date('01/01/1999','DD/MM/RRRR') )
512 THEN l_exchange_date := to_date('01/01/1999','DD/MM/RRRR');
513 END IF;
514
515 rate := GL_CURRENCY_API.get_closest_rate_sql (
516 p_from_currency_code,
517 p_to_currency_code,
518 l_exchange_date,
519 p_exchange_rate_type,
520 l_max_roll_days);
521
522 END IF;
523
524 IF (p_from_currency_code = 'EUR'
525 AND p_exchange_date < to_date('01/01/1999','DD/MM/RRRR')
526 AND rate = -1 )
527 THEN rate := -3;
528 ELSIF (p_to_currency_code = 'EUR'
529 AND p_exchange_date < to_date('01/01/1999','DD/MM/RRRR')
530 AND rate = -1 )
531 THEN rate := -3;
532 END IF;
533
534 RETURN (rate);
535
536 EXCEPTION
537 WHEN OTHERS THEN
538 RETURN -4; -- Could we return -4 instead of NULL???
539
540 END get_rate;
541
542 -----------------------------
543 -- Rate Conversion API's
544 -----------------------------
545
546
547 -- --------------------------------------------------------------------------
548 -- Name : compare_currency_codes
549 -- Type : Function
550 -- Description : Returns 1 if the given currency codes are the same
551 -- else returns 0;
552 -- This function also takes care of fixed currency for Oracle IT.
553 -- If USD is treated as CD , then if one of the parameters is CD
554 -- and the other is USD then the function returns 1.
555 -----------------------------------------------------------------------------
556 FUNCTION compare_currency_codes(
557 p_currency_code1 IN VARCHAR2,
558 p_currency_code2 IN VARCHAR2) RETURN NUMBER
559 PARALLEL_ENABLE IS
560
561 BEGIN
562 IF (p_currency_code1=p_currency_code2) THEN
563 return 1;
564 ELSE
568 if (g_fii_right_curr=p_currency_code2) then
565 if (g_fii_fixed_curr_it is not null) then
566 CASE g_fii_left_curr
567 WHEN p_currency_code1 THEN
569 return 1;
570 else
571 return 0;
572 end if;
573 WHEN p_currency_code2 THEN
574 if (g_fii_right_curr=p_currency_code1) then
575 return 1;
576 else
577 return 0;
578 end if;
579 ELSE return 0;
580 END CASE;
581
582 end if;
583
584 return 0;
585 END IF;
586 END;
587
588 -- -----------------------------------------------------------------------
589 -- Name : get_fc_to_pgc_rate
590 -- Type : Function
591 -- Description : Returns rate to convert amounts from functional currency
592 -- to primary global currency. If the transactional currency
593 -- is the same as primary global currency , functional amts
594 -- are not converted and transactional amounts are used.
595 -- Output : Returns 0 if transactional currency and the primary global
596 -- currency is the same.
597 -- Returns 1 if the functional currency is the same as primary
598 -- global currency
599 -- Returns the rate between the functional currency and the
600 -- primary global currency.
601 -- Exceptions : Returns -1 when no rate exists
602 -- Returns -2 when invalid currency
603 -- Retunrs -3 when one of the currency is EUR and the
604 -- exchange date is before Jan 1 ,1999
605 -- Return -4 for any other exception.
606 -- How the exceptions are handled :
607 -- Other exceptions are handled by get_fc_to_pgc_rate
608 -- No Rate and Invalid Currency are handled in gl_currency_api.get_closest_rate_sql
609 -- When one of the currency is EUR and exchange date is before Jan 1,1999
610 -- -3 is returned from get_rate.
611 ---------------------------------------------------------------------------
612 FUNCTION get_fc_to_pgc_rate(p_tc_code IN VARCHAR2,
613 p_fc_code IN VARCHAR2,
614 p_exchange_date IN DATE) RETURN NUMBER PARALLEL_ENABLE IS
615 l_rate NUMBER;
616 BEGIN
617
618
619 IF (compare_currency_codes(p_tc_code,g_prim_currency_code)=1) THEN
620 return 0;
621 ELSE
622 l_rate:=return_prim_rate_if_in_cache(p_fc_code,p_exchange_date);
623 if (l_rate=0) then
624 l_rate:=get_rate(p_fc_code,g_prim_currency_code,p_exchange_date,g_prim_rate_type);
625 cache_prim_rate(p_fc_code,p_exchange_date,l_rate);
626 end if;
627 return l_rate;
628 END IF;
629
630 EXCEPTION
631 WHEN OTHERS THEN
632 return -4;
633 END;
634
635
636 -- -----------------------------------------------------------------------
637 -- Name : get_fc_to_sgc_rate
638 -- Type : Function
639 -- Description : Returns rate to convert amounts from functional currency
640 -- to secondary global currency. If the transactional currency
641 -- is the same as secondary global currency , functional amts
642 -- are not converted and transactional amounts are used.
643 -- Output : Returns 0 if transactional currency and the secondary global
644 -- currency is the same.
645 -- Returns 1 if the functional currency is the same as secondary
646 -- global currency or secondary global currency is not defined.
647 -- Returns the rate between the functional currency and the
648 -- secondary global currency.
649 -- Exceptions : Returns -1 when no rate exists
650 -- Returns -2 when invalid currency
651 -- Retunrs -3 when one of the currency is EUR and the
652 -- exchange date is before Jan 1 ,1999
653 -- Return -4 for any other exception.
654 -- How the exceptions are handled :
655 -- Other exceptions are handled by get_fc_to_sgc_rate
656 -- No Rate and Invalid Currency are handled in gl_currency_api.get_closest_rate_sql
657 -- When one of the currency is EUR and exchange date is before Jan 1,1999
658 -- -3 is returned from get_rate.
659 ---------------------------------------------------------------------------
660 FUNCTION get_fc_to_sgc_rate(p_tc_code IN VARCHAR2,
661 p_fc_code IN VARCHAR2,
662 p_exchange_date IN DATE) RETURN NUMBER PARALLEL_ENABLE IS
663 l_rate number;
664
665 BEGIN
666
667
668 IF (g_sec_currency_code is not null) THEN
669 IF (compare_currency_codes(p_tc_code,g_sec_currency_code)=1) THEN
670 return 0;
671 ELSE
672 l_rate:=return_sec_rate_if_in_cache(p_fc_code,p_exchange_date);
673 if (l_rate=0) then
674 l_rate:=get_rate(p_fc_code,g_sec_currency_code,p_exchange_date,g_sec_rate_type);
675 cache_sec_rate(p_fc_code,p_exchange_date,l_rate);
676 end if;
677 return l_rate;
678 END IF;
679 ELSE
680 return to_number(null); -- No secondary Global Currency .No need for conversion
681
682 END IF;
683
684 EXCEPTION
685 WHEN OTHERS THEN
686 return -4;
687 END;
688
689 ------------------------------------------
690 --Modules Not Storing Functional Currency
691 ------------------------------------------
692
693 -- --------------------------------------------------------------------------------
694 -- Name : get_tc_to_pgc_rate
695 -- Type : Function
696 -- Description : This api is to be used for modules not storing functional currency.
697 -- Returns rate to convert amounts from transactional currency to
698 -- primary global currency.
699 -- Output :
703 -- global currency is the same then return the user defined rate
700 -- o If transactional currency and primary global currency is the same
701 -- then return 1. user-defined rate is ignored.
702 -- o If user defined rate is given and functional currency and primary
704 -- else returns the product of the user defined rate and the retrieved
705 -- rate between the functional currency and the primary global currency.
706 -- o In all other cases, it returns the product of rate between transactional
707 -- currency and functional currency and rate between functional currency
708 -- and primary global currency.
709 -- Exceptions : Returns -2 when either of transactional currency and functional currency
710 -- is invalid.
711 -- Returns -3 when transactional or functional currency is EUR and the exchange date
712 -- is before Jan 1 ,1999 and no rate exists on Jan 1,1999.
713 -- Returns -4 for any other exception
714 -- Returns -5 when no rate exists between transactional currency and functional
715 -- currency.
716 -- Returns -6 when no rate exists between functional currency and primary
717 -- global currency.
718 -- Returns -7 when functional or primary global currency is EUR and the
719 -- exchange date is before Jan 1 ,1999 and no rate exists on Jan 1,1999.
720 -- Returns -8 when treasury rate type is null and p_rate is null and exchange
721 -- rate type is null.
722 -- How the exceptions are handled :
723 -- Other exceptions are handled by get_tc_to_pgc_rate
724 -- Invalid Currency (-2) is handled in gl_currency_api.get_closest_rate_sql
725 -- When one of the currency is EUR and exchange date is before Jan 1,1999
726 -- and no rate exists on Jan 1,1999 then -3 is returned from FII_CURRENCY.get_rate
727 -- -5,-6,-7 ,-8 are handled in get_tc_pgc_rate
728 ----------------------------------------------------------------------------------------------
729
730 FUNCTION get_tc_to_pgc_rate(p_tc_code IN VARCHAR2,
731 p_exchange_date1 IN DATE,
732 p_exchange_rate_type IN VARCHAR2,
733 p_fc_code IN VARCHAR2,
734 p_exchange_date2 IN DATE,
735 p_rate IN NUMBER DEFAULT NULL) RETURN NUMBER PARALLEL_ENABLE IS
736
737 l_rate number;
738 l_rate1 number;
739 BEGIN
740
741
742 IF (compare_currency_codes(p_tc_code,g_prim_currency_code)=1) THEN
743 return 1;
744 END IF;
745
746 IF (p_rate is not null and p_rate > 0) THEN
747 IF (compare_currency_codes(p_fc_code,g_prim_currency_code)=1) THEN
748 return p_rate;
749 ELSE
750 l_rate:=return_prim_rate_if_in_cache(p_fc_code,p_exchange_date2);
751 if (l_rate=0) then
752 l_rate:=get_rate(p_fc_code,g_prim_currency_code,p_exchange_date2,g_prim_rate_type);
753
754 if (l_rate < 0) then
755 if (l_rate=-1) then
756 return -6;
757 elsif (l_rate=-3) then
758 return -7;
759 end if;
760 return l_rate;
761 end if;
762 cache_prim_rate(p_fc_code,p_exchange_date2,l_rate);
763 end if;
764
765 return l_rate * p_rate;
766
767 END IF;
768 ELSIF (p_rate < 0) THEN
769 if (p_rate = -1) then
770 return -5;
771 else
772 return p_rate;
773 end if;
774 ELSE
775 /* Covert from tc to fc */
776
777 IF (p_exchange_rate_type is not null) THEN
778 l_rate:=get_rate(p_tc_code,p_fc_code,p_exchange_date1,p_exchange_rate_type);
779 ELSE
780 if (g_treasury_rate_type is null) then
781 return -8;
782 else
783 l_rate:=get_rate(p_tc_code,p_fc_code,p_exchange_date1,g_treasury_rate_type);
784 end if;
785 END IF;
786
787 if (l_rate < 0) then
788 if (l_rate = -1 ) then
789 l_rate:= -5;
790 else
791 return l_rate;
792 end if;
793 end if;
794
795 /* Now convert from fc to gc */
796 IF (compare_currency_codes(p_fc_code,g_prim_currency_code)=1) THEN
797 return l_rate*1;
798 ELSE
799 l_rate1:=return_prim_rate_if_in_cache(p_fc_code,p_exchange_date2);
800 if (l_rate1=0) then
801 l_rate1:=get_rate(p_fc_code,g_prim_currency_code,p_exchange_date2,g_prim_rate_type);
802 if (l_rate1 < 0) then
803 if (l_rate1 = -1 ) then
804 l_rate1:= -6;
805 elsif (l_rate1 = -3) then
806 return -7;
807 else
808 return l_rate1;
809 end if;
810 else
811 cache_prim_rate(p_fc_code,p_exchange_date2,l_rate);
812 end if;
813 end if;
814
815 if (l_rate=-5 and l_rate1=-6) then
816 return -6;
817 elsif (l_rate > 0 and l_rate1=-6) then
818 return l_rate1;
819 elsif (l_rate1 > 0 and l_rate=-5) then
820 return l_rate;
821 end if;
822
823 return l_rate*l_rate1;
824
825 END IF;
826 END IF;
827 EXCEPTION
828 WHEN OTHERS THEN
829 return -4;
830 END;
831
832 -- --------------------------------------------------------------------------------
833 -- Name : get_tc_to_sgc_rate
834 -- Type : Function
838 -- Output :
835 -- Description : This api is to be used for modules not storing functional currency.
836 -- Returns rate to convert amounts from transactional currency to
837 -- secondary global currency.
839 -- o If transactional currency and secondary global currency is the same
840 -- then return 1. user-defined rate is ignored.
841 -- o If secondary global currency is not defined then it returns 1.
842 -- o If user defined rate is given and functional currency and secondary
843 -- global currency is the same then return the user defined rate
844 -- else returns the product of the user defined rate and the retrieved
845 -- rate between the functional currency and the secondary global currency.
846 -- o In all other cases, it returns the product of rate between transactional
847 -- currency and functional currency and rate between functional currency
848 -- and secondary global currency.
849 -- Exceptions : Returns -2 when either of transactional currency and functional currency
850 -- is invalid.
851 -- Returns -3 when transactional or functional currency is EUR and the exchange date
852 -- is before Jan 1 ,1999 and no rate exists on Jan 1,1999.
853 -- Returns -4 for any other exception
854 -- Returns -5 when no rate exists between transactional currency and functional
855 -- currency.
856 -- Returns -6 when no rate exists between functional currency and secondary
857 -- global currency.
858 -- Returns -7 when functional or secondary global currency is EUR and the
859 -- exchange date is before Jan 1 ,1999 and no rate exists on Jan 1,1999.
860 -- Returns -8 when treasury rate type is null and p_rate is null and exchange
861 -- rate type is null.
862 -- How the exceptions are handled :
863 -- Other exceptions are handled by get_tc_to_pgc_rate
864 -- Invalid Currency (-2) is handled in gl_currency_api.get_closest_rate_sql
865 -- When one of the currency is EUR and exchange date is before Jan 1,1999
866 -- and no rate exists on Jan 1,1999 then -3 is returned from FII_CURRENCY.get_rate
867 -- -5,-6,-7 ,-8 are handled in get_tc_sgc_rate
868 ----------------------------------------------------------------------------------------------
869
870
871 FUNCTION get_tc_to_sgc_rate(p_tc_code IN VARCHAR2,
872 p_exchange_date1 IN DATE,
873 p_exchange_rate_type IN VARCHAR2,
874 p_fc_code IN VARCHAR2,
875 p_exchange_date2 IN DATE,
876 p_rate IN NUMBER DEFAULT NULL) RETURN NUMBER PARALLEL_ENABLE IS
877
878 l_rate number;
879 l_rate1 number;
880 BEGIN
881
882 IF (g_sec_currency_code is not null ) then
883 IF (compare_currency_codes(p_tc_code,g_sec_currency_code)=1) THEN
884 return 1;
885 END IF;
886
887 IF (p_rate is not null and p_rate > 0) THEN
888 IF (compare_currency_codes(p_fc_code,g_sec_currency_code)=1) THEN
889 return p_rate;
890 ELSE
891 l_rate:=return_sec_rate_if_in_cache(p_fc_code,p_exchange_date2);
892 if (l_rate=0) then
893 l_rate:=get_rate(p_fc_code,g_sec_currency_code,p_exchange_date2,g_sec_rate_type);
894 if (l_rate < 0) then
895 if (l_rate=-1) then
896 return -6;
897 elsif (l_rate=-3) then
898 return -7;
899 end if;
900 return l_rate;
901 end if;
902 cache_sec_rate(p_fc_code,p_exchange_date2,l_rate);
903 end if;
904 return l_rate * p_rate;
905
906 END IF;
907 ELSIF (p_rate < 0) THEN
908 if (p_rate = -1) then
909 return -5;
910 else
911 return p_rate;
912 end if;
913 ELSE
914 /* Covert from tc to fc */
915 IF (p_exchange_rate_type is not null) THEN
916 l_rate:=get_rate(p_tc_code,p_fc_code,p_exchange_date1,p_exchange_rate_type);
917 ELSE
918 if (g_treasury_rate_type is null) then
919 return -8;
920 else
921 l_rate:=get_rate(p_tc_code,p_fc_code,p_exchange_date1,g_treasury_rate_type);
922 -- l_rate:=get_rate(p_tc_code,p_fc_code,p_exchange_date1,'Corporate');
923 end if;
924 END IF;
925
926 if (l_rate < 0) then
927 if (l_rate = -1 ) then
928 l_rate:= -5;
929 else
930 return l_rate;
931 end if;
932 end if;
933
934 /* Now convert from fc to gc */
935 IF (compare_currency_codes(p_fc_code,g_sec_currency_code)=1) THEN
936 return l_rate*1;
937 ELSE
938 l_rate1:=return_sec_rate_if_in_cache(p_fc_code,p_exchange_date2);
939 if (l_rate1=0) then
940 l_rate1:=get_rate(p_fc_code,g_sec_currency_code,p_exchange_date2,g_sec_rate_type);
941 if (l_rate1 < 0) then
942 if (l_rate1 = -1 ) then
943 l_rate1 := -6;
944 elsif (l_rate1 = -3) then
945 return -7;
946 else
947 return l_rate1;
948 end if;
949 else
950 cache_sec_rate(p_fc_code,p_exchange_date2,l_rate1);
951 end if;
952 end if;
953
954 if (l_rate=-5 and l_rate1=-6) then
955 return -6;
956 elsif (l_rate > 0 and l_rate1=-6) then
957 return l_rate1;
958 elsif (l_rate1 > 0 and l_rate=-5) then
959 return l_rate;
960 end if;
961
962
963 return l_rate*l_rate1;
964
965 END IF;
966 END IF;
967 ELSE
968 return to_number(null); -- No gobal secondary currency exists.
969 END IF;
970 EXCEPTION
971 WHEN OTHERS THEN
972 return -4;
973 END;
974
975
976 END FII_CURRENCY;
[Home] [Help]