1 PACKAGE BODY icx_cat_util_pkg AS
2 /* $Header: ICXCUTLB.pls 115.2 2003/08/10 02:41:55 jingyu ship $ */
3
4
5 -- Procedure
6 -- get_info
7 --
8 -- Arguments
9 -- x_currency Currency to be checked
10 -- x_mau Minimum accountable unit
11 --
12 PROCEDURE get_info(
13 x_currency VARCHAR2,
14 x_mau IN OUT NOCOPY NUMBER) IS
15
16 BEGIN
17 -- Get currency information from FND_CURRENCIES table
18 SELECT nvl( minimum_accountable_unit, power( 10, (-1 * extended_precision)))
19 INTO x_mau
20 FROM FND_CURRENCIES
21 WHERE currency_code = x_currency;
22
23 EXCEPTION
24 WHEN NO_DATA_FOUND THEN
25 raise INVALID_CURRENCY;
26
27 END get_info;
28
29 --
30
31 FUNCTION convert_amount_sql (
32 x_from_currency VARCHAR2,
33 x_to_currency VARCHAR2,
34 x_conversion_date DATE,
35 x_conversion_type VARCHAR2 DEFAULT NULL,
36 x_amount NUMBER ) RETURN NUMBER IS
37
38 to_mau NUMBER;
39 converted_amount NUMBER;
40 default_conversion_type VARCHAR2(25);
41 BEGIN
42
43 -- If from currency is null, then assume it is same as to currency
44 if ( x_from_currency = x_to_currency OR x_from_currency is null) then
45 return( x_amount);
46 END IF;
47
48 -- use default rate type from purchasing options if rate type is null
49 if ( x_conversion_type is null ) then
50 begin
51 SELECT default_rate_type into default_conversion_type FROM po_system_parameters;
52 exception
53 WHEN OTHERS THEN default_conversion_type := null;
54 end;
55 end if;
56
57 get_info( x_to_currency, to_mau);
58
59 if ( x_conversion_type is null ) then
60 converted_amount := round ( ( x_amount * gl_currency_api.get_rate(x_from_currency,x_to_currency, x_conversion_date, default_conversion_type ))/to_mau ) * to_mau ;
61 else
62 converted_amount := round ( ( x_amount * gl_currency_api.get_rate(x_from_currency,x_to_currency, x_conversion_date, x_conversion_type ))/to_mau ) * to_mau ;
63 end if;
64
65 return( converted_amount );
66
67 EXCEPTION
68 WHEN gl_currency_api.NO_RATE THEN
69 --bmunagal, 11/05/02, for FPI, return null if there is no rate
70 --converted_amount := -1;
71 converted_amount := null;
72 return( converted_amount );
73
74 WHEN INVALID_CURRENCY THEN
75 converted_amount := -2;
76 return( converted_amount );
77
78 END convert_amount_sql;
79
80
81
82
83 --
84 -- Function
85 -- convert_amount_sql
86 --
87 -- Purpose
88 --
89 -- Overloaded function: If x_conversion_rate is not null, the rate is used to convert
90 -- otherwise, rate is calculated using from and to currencies
91 --
92 -- Returns the amount converted from the from currency into the
93 -- functional currency of that set of books by calling convert_amount().
94 -- The amount returned is rounded to the precision and minimum account
95 -- unit of the to currency.
96 --
97 -- Return -1 if the NO_RATE exception is raised in convert_amount().
98 -- -2 if the INVALID_CURRENCY exception is raised in
99 -- convert_amount().
100 --
101 -- History
102 -- 30-OCT-2002 BMUNAGAL Created
103 FUNCTION convert_amount_sql (
104 x_from_currency VARCHAR2,
105 x_to_currency VARCHAR2,
106 x_conversion_date DATE,
107 x_conversion_type VARCHAR2 DEFAULT NULL,
108 x_conversion_rate NUMBER,
109 x_amount NUMBER ) RETURN NUMBER IS
110
111 to_mau NUMBER;
112 converted_amount NUMBER;
113 default_conversion_type VARCHAR2(25);
114
115 BEGIN
116
117 -- If from currency is null, then assume it is same as to currency
118 if ( x_from_currency = x_to_currency OR x_from_currency is null) then
119 return( x_amount);
120 END IF;
121
122 get_info( x_to_currency, to_mau);
123
124 if(x_conversion_rate is not null AND x_conversion_rate > 0) then
125 -- This is User rate, just use the rate
126 converted_amount := round ( ( x_amount * x_conversion_rate)/to_mau ) * to_mau ;
127 elsif (x_conversion_type is null) then
128 -- Get the rate depending on the default rate type from purchasing options
129 begin
130 SELECT default_rate_type into default_conversion_type FROM po_system_parameters;
131 exception
132 WHEN OTHERS THEN default_conversion_type := null;
133 end;
134 converted_amount := round ( ( x_amount * gl_currency_api.get_rate(x_from_currency,x_to_currency, x_conversion_date, default_conversion_type ))/to_mau ) * to_mau ;
135 else
136 -- Get the rate depending on rate type
137 converted_amount := round ( ( x_amount * gl_currency_api.get_rate(x_from_currency,x_to_currency, x_conversion_date, x_conversion_type ))/to_mau ) * to_mau ;
138 end if;
139
140 return( converted_amount );
141
142 EXCEPTION
143 WHEN gl_currency_api.NO_RATE THEN
144 --bmunagal, 11/05/02, for FPI, return null if there is no rate
145 --converted_amount := -1;
146 converted_amount := null;
147 return( converted_amount );
148
149 WHEN INVALID_CURRENCY THEN
150 converted_amount := -2;
151 return( converted_amount );
152
153 END convert_amount_sql;
154
155 end icx_cat_util_pkg;
[Home] [Help]