1 PACKAGE BODY GMF_GLCOMMON_DB AS
2 /* $Header: gmfglcob.pls 115.1 2002/11/11 00:37:55 rseshadr ship $ */
3 /* Returns the rate between the two currencies for a given rate*/
4 /* date and rate type.*/
5 /**/
6 /* If such a rate is not defined for the specified rate_date, it*/
7 /* searches backward for a rate defined for the same currencies and*/
8 /* conversion type. It searches backward up to GL$MAX_ROLL_DAYS prior*/
9 /* to the specified x_exchange_rate_date.*/
10 FUNCTION get_other_closest_rate (
11 x_from_currency_code VARCHAR2,
12 x_to_currency_code VARCHAR2,
13 x_exchange_rate_date Date,
14 x_rate_type_code VARCHAR2,
15 x_max_roll_days NUMBER ,
16 x_mul_div_sign OUT NOCOPY NUMBER ,
17 error_status IN OUT NOCOPY NUMBER) RETURN NUMBER;
18
19 FUNCTION get_closest_rate (
20 x_from_currency_code VARCHAR2,
21 x_to_currency_code VARCHAR2,
22 x_exchange_rate_date DATE,
23 x_rate_type_code VARCHAR2 DEFAULT NULL,
24 x_mul_div_sign OUT NOCOPY NUMBER,
25 error_status IN OUT NOCOPY NUMBER) RETURN NUMBER IS
26
27 CURSOR C_get_euro_code IS
28 SELECT currency_code
29 FROM gl_curr_mst
30 WHERE derive_type = 1 and
31 delete_mark = 0;
32
33
34 euro_code VARCHAR2(15);
35 to_type VARCHAR2(8);
36 from_type VARCHAR2(8);
37 to_rate NUMBER;
38 from_rate NUMBER;
39 other_rate NUMBER;
40 x_max_roll_days NUMBER;
41 mau NUMBER;
42 BEGIN
43 error_status := 0;
44 x_mul_div_sign := 0;
45 /* Check for the null parameters*/
46 IF (x_from_currency_code is null or x_to_currency_code is null
47 or x_exchange_rate_date is null or x_rate_type_code is null )
48 THEN
49 error_status := 100;
50 return(-1);
51 END IF;
52 /* Check if both currencies are identical*/
53 IF ( x_from_currency_code = x_to_currency_code ) THEN
54 return( 1 );
55 END IF;
56 /* Get currency information from the x_from_currency_code*/
57 get_info ( x_from_currency_code, x_exchange_rate_date, from_rate, mau,
58 from_type,error_status );
59 If (error_status = 100 ) THEN
60 return(-1);
61 END IF;
62 /* Get currency information from the x_to_currency_code*/
63 get_info ( x_to_currency_code, x_exchange_rate_date, to_rate, mau, to_type ,error_status);
64 If (error_status = 100 ) THEN
65 return(-1);
66 END IF;
67 /* Get the Max rollback days.*/
68 x_max_roll_days := nvl(fnd_profile.value('GL$MAX_ROLL_DAYS'),-1);
69
70 /* Get the euro code */
71 OPEN C_get_euro_code;
72 FETCH C_get_euro_code INTO euro_code;
73 CLOSE C_get_euro_code;
74 /* Calculate the conversion rate according to both currency types*/
75 IF ( from_type = 'EMU' ) THEN
76 IF ( to_type = 'EMU' ) THEN
77 return( to_rate / from_rate );
78 ELSIF ( to_type = 'EURO' ) THEN
79 return( 1 / from_rate );
80 ELSIF ( to_type = 'OTHER' ) THEN
81 /* Find out conversion rate from EURO to x_to_currency_code*/
82 other_rate := get_other_closest_rate( euro_code,
83 x_to_currency_code,
84 x_exchange_rate_date,
85 x_rate_type_code,
86 x_max_roll_days,
87 x_mul_div_sign,
88 error_status );
89
90 /* Get conversion rate by converting EMU -> EURO -> OTHER*/
91 return( other_rate / from_rate );
92 END IF;
93 ELSIF ( from_type = 'EURO' ) THEN
94 IF ( to_type = 'EMU' ) THEN
95 return( to_rate );
96 ELSIF ( to_type = 'EURO' ) THEN
97 /* We should never comes to this case as it should be*/
98 /* caught when we check if both to and from currency*/
99 /* is the same at the beginning of this function*/
100 return( 1 );
101 ELSIF ( to_type = 'OTHER' ) THEN
102 other_rate := get_other_closest_rate( x_from_currency_code,
103 x_to_currency_code,
104 x_exchange_rate_date,
105 x_rate_type_code,
106 x_max_roll_days,
107 x_mul_div_sign,
108 error_status );
109 return( other_rate );
110 END IF;
111 ELSIF ( from_type = 'OTHER' ) THEN
112 IF ( to_type = 'EMU' ) THEN
113 /* Find out conversion rate from x_from_currency_code to EURO*/
114 other_rate := get_other_closest_rate( x_from_currency_code,
115 euro_code,
116 x_exchange_rate_date,
117 x_rate_type_code,
118 x_max_roll_days,
119 x_mul_div_sign,
120 error_status );
121
122 /* Get conversion rate by converting OTHER -> EURO -> EMU*/
123 return( other_rate * to_rate );
124 ELSIF ( to_type = 'EURO' ) THEN
125 other_rate := get_other_closest_rate( x_from_currency_code,
126 x_to_currency_code,
127 x_exchange_rate_date,
128 x_rate_type_code,
129 x_max_roll_days,
130 x_mul_div_sign,
131 error_status );
132 return( other_rate );
133 ELSIF ( to_type = 'OTHER' ) THEN
134 other_rate := get_other_closest_rate( x_from_currency_code,
135 x_to_currency_code,
136 x_exchange_rate_date,
137 x_rate_type_code,
138 x_max_roll_days,
139 x_mul_div_sign,
140 error_status );
141 return( other_rate );
142 END IF;
143 END IF;
144 return (-1);
145 END get_closest_rate;
146
147 /* Returns conversion rate between two currencies where both currencies*/
148 /* are not the EURO, or EMU currencies.*/
149 FUNCTION get_other_closest_rate (
150 x_from_currency_code VARCHAR2,
151 x_to_currency_code VARCHAR2,
152 x_exchange_rate_date Date,
153 x_rate_type_code VARCHAR2,
154 x_max_roll_days NUMBER ,
155 x_mul_div_sign OUT NOCOPY NUMBER,
156 error_status IN OUT NOCOPY NUMBER) RETURN NUMBER IS
157 /* This cursor finds the latest rate defined between the given two*/
158 /* currencies using x_rate_type_code within the period between*/
159 /* x_max_roll_days prior to x_exchange_rate_date AND x_exchange_rate_date.*/
160 CURSOR closest_rate_curr IS
161 SELECT exchange_rate,mul_div_sign
162 FROM GL_XCHG_RTE
163 WHERE from_currency_code = x_from_currency_code
164 AND to_currency_code = x_to_currency_code
165 AND rate_type_code = x_rate_type_code
166 AND exchange_rate_date BETWEEN
167 decode( sign (x_max_roll_days),
168 -1, trunc(to_date('1000/01/01', 'YYYY/MM/DD')),
169 trunc(x_exchange_rate_date - x_max_roll_days))
170 AND x_exchange_rate_date
171 and delete_mark = 0
172 ORDER BY exchange_rate_date DESC;
173 rate NUMBER;
174 BEGIN
175 /* Search backwards for a conversion rate with the given currencies*/
176 /* and conversion type.*/
177 OPEN closest_rate_curr;
178 FETCH closest_rate_curr INTO rate,x_mul_div_sign;
179 IF NOT closest_rate_curr%FOUND THEN
180 close closest_rate_curr;
181 raise NO_RATE;
182 ELSE
183 error_status := 0;
184 close closest_rate_curr;
185 return( rate );
186 END IF;
187 EXCEPTION
188 /* No conversion rate was found on the given conversion date.*/
189 /* Try to search for the latest conversion rate with a prior conversion*/
190 /* date then x_exchange_rate_date.*/
191 WHEN NO_RATE THEN
192 error_status := 100;
193 return(-1);
194 /* raise NO_RATE;*/
195 END get_other_closest_rate;
196
197 /* Gets the currency type information about given currency.*/
198 /* Also set the x_invalid_currency flag if the given currency is invalid.*/
199 PROCEDURE get_info(
200 x_currency VARCHAR2,
201 x_eff_date DATE,
202 x_exchange_rate IN OUT NOCOPY NUMBER,
203 x_mau IN OUT NOCOPY NUMBER,
204 x_currency_type IN OUT NOCOPY VARCHAR2,
205 error_status IN OUT NOCOPY NUMBER ) IS
206 BEGIN
207 error_status := 0;
208 /* Get currency information from GL_CURR_MST table*/
209 SELECT decode( derive_type,
210 1, 'EURO',
211 2, decode( sign( trunc(x_eff_date) -
212 trunc(derive_effective)),
213 -1, 'OTHER',
214 'EMU'),
215 'OTHER' ),
216 decode( derive_type, 1, 1,
217 2, derive_factor,
218 0, -1 ),
219 nvl( decimal_precision, power( 10, (-1 * decimal_precision)))
220 INTO x_currency_type,
221 x_exchange_rate,
222 x_mau
223 FROM gl_curr_mst
224 WHERE currency_code = x_currency;
225 EXCEPTION
226 WHEN NO_DATA_FOUND THEN
227 error_status := 100;
228 /* raise INVALID_CURRENCY;*/
229 END get_info;
230
231 /* Returns if there is a fixed rate between the two currencies.*/
232 FUNCTION is_fixed_rate (
233 x_from_currency VARCHAR2,
234 x_to_currency VARCHAR2,
235 x_effective_date DATE ,
236 error_status IN OUT NOCOPY NUMBER ) RETURN VARCHAR2 IS
237
238 to_type VARCHAR2(8);
239 from_type VARCHAR2(8);
240 rate NUMBER; /* Value ignored in this function*/
241 mau NUMBER; /* Value ignored in this function*/
242 BEGIN
243 error_status := 0;
244 /* Check for the null parameters*/
245 IF (x_from_currency is null or x_to_currency is null
246 or x_effective_date is null )
247 THEN
248 error_status := 100;
249 return 'N';
250 END IF;
251 /* Check if both currencies are identical*/
252 IF ( x_from_currency = x_to_currency ) THEN
253 return 'Y';
254 END IF;
255 /* Get currency information of the x_from_currency*/
256 get_info( x_from_currency, x_effective_date, rate, mau, from_type,error_status );
257 If (error_status = 100 ) THEN
258 return 'N';
259 END IF;
260 /* Get currency information of the x_to_currency*/
261 get_info( x_to_currency, x_effective_date, rate, mau, to_type,error_status );
262 If (error_status = 100 ) THEN
263 return 'N';
264 END IF;
265 /* Check if there is a fixed rate between the two given currencies*/
266 IF (( from_type IN ('EMU', 'EURO')) AND
267 ( to_type IN ('EMU', 'EURO'))) THEN
268 return 'Y';
269 ELSE
270 return 'N';
271 END IF;
272 END is_fixed_rate;
273
274 PROCEDURE proc_get_closest_rate(
275 x_from_currency_code VARCHAR2,
276 x_to_currency_code VARCHAR2,
277 x_exchange_rate_date DATE,
278 x_rate_type_code VARCHAR2 DEFAULT NULL,
279 x_exchange_rate OUT NOCOPY NUMBER,
280 x_mul_div_sign OUT NOCOPY NUMBER,
281 error_status IN OUT NOCOPY NUMBER) is
282 BEGIN
283 x_exchange_rate := get_closest_rate(
284 x_from_currency_code,
285 x_to_currency_code,
286 x_exchange_rate_date,
287 x_rate_type_code,
288 x_mul_div_sign,
289 error_status);
290 END;
291
292 PROCEDURE proc_is_fixed_rate(
293 x_from_currency VARCHAR2,
294 x_to_currency VARCHAR2,
295 x_effective_date DATE ,
296 x_fixed_check OUT NOCOPY VARCHAR2,
297 error_status IN OUT NOCOPY NUMBER) is
298 BEGIN
299 x_fixed_check := is_fixed_rate(
300 x_from_currency,
301 x_to_currency,
302 x_effective_date,
303 error_status);
304 END;
305
306 END GMF_GLCOMMON_DB;
[Home] [Help]