1 PACKAGE BODY gl_mc_currency_pkg AS
2 /* $Header: glmccurb.pls 120.15 2006/03/29 19:52:50 mgowda ship $ */
3
4
5 TYPE CurrencyCodeType IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
6 TYPE PrecisionType IS TABLE OF NUMBER(1) INDEX BY BINARY_INTEGER;
7 TYPE MauType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
8 NextElement BINARY_INTEGER := 0;
9 CurrencyCode CurrencyCodeType;
10 Precision PrecisionType;
11 Mau MauType;
12
13 G_PKG_NAME CONSTANT VARCHAR2(30) :='GL_MC_CURRENCY_PKG';
14 G_DEBUG_LEVEL CONSTANT NUMBER :=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
15 G_STATEMENT_LEVEL CONSTANT NUMBER :=FND_LOG.LEVEL_STATEMENT;
16 G_PROC_LEVEL CONSTANT NUMBER :=FND_LOG.LEVEL_PROCEDURE;
17 G_EVENT_LEVEL CONSTANT NUMBER :=FND_LOG.LEVEL_EVENT;
18 G_EXCEPTION_LEVEL CONSTANT NUMBER :=FND_LOG.LEVEL_EXCEPTION;
19 G_ERROR_LEVEL CONSTANT NUMBER :=FND_LOG.LEVEL_ERROR;
20 G_UNEXPECTED_LEVEL CONSTANT NUMBER :=FND_LOG.LEVEL_UNEXPECTED;
21 G_DEBUG_PKG_HDR CONSTANT VARCHAR2(100) := 'gl.sql.GLMCCURB.';
22 --
23 -- R11i.X Changes - Call gl_mc_info instead (merged the code)
24 --
25 FUNCTION get_currency_code (p_set_of_books_id NUMBER) RETURN VARCHAR2 IS
26 l_currency_code VARCHAR2(15);
27 BEGIN
28 gl_mc_info.get_ledger_currency(p_set_of_books_id, l_currency_code);
29
30 RETURN(l_currency_code);
31 END get_currency_code;
32 --
33 -- R11i.X Changes - Call gl_mc_info instead (merged the code)
34 --
35 FUNCTION get_mrc_sob_type_code (p_set_of_books_id NUMBER) RETURN VARCHAR2 IS
36 l_mrc_sob_type_code VARCHAR2(1);
37 BEGIN
38 gl_mc_info.get_sob_type(p_set_of_books_id, l_mrc_sob_type_code);
39
40 RETURN(l_mrc_sob_type_code);
41 END get_mrc_sob_type_code;
42 --
43 -- R11i.X Changes - modified to refer to the new data model
44 --
45 PROCEDURE get_rate(p_primary_set_of_books_id IN NUMBER,
46 p_reporting_set_of_books_id IN NUMBER,
47 p_trans_date IN DATE,
48 p_trans_currency_code IN VARCHAR2,
49 p_trans_conversion_type IN OUT NOCOPY VARCHAR2,
50 p_trans_conversion_date IN OUT NOCOPY DATE,
51 p_trans_conversion_rate IN OUT NOCOPY NUMBER,
52 p_application_id IN NUMBER,
53 p_org_id IN NUMBER,
54 p_fa_book_type_code IN VARCHAR2,
55 p_je_source_name IN VARCHAR2,
56 p_je_category_name IN VARCHAR2,
57 p_result_code IN OUT NOCOPY VARCHAR2,
58 p_denominator_rate OUT NOCOPY NUMBER,
59 p_numerator_rate OUT NOCOPY NUMBER ) IS
60 l_je_conv_set_id NUMBER;
61 l_target_curr VARCHAR2(15);
62 l_src_curr VARCHAR2(15);
63 l_conversion_type VARCHAR2(30);
64 l_user_conversion_type VARCHAR2(30);
65 l_no_rate_action VARCHAR2(30);
66 l_inherit_ctype_Flag VARCHAR2(1);
67 l_conversion_flag VARCHAR2(1);
68
69 l_tmp_conversion_type VARCHAR2(30);
70 l_inherited_conversion_type VARCHAR2(30);
71
72 l_fixed_rate BOOLEAN;
73 l_relationship VARCHAR2(15);
74 l_xrate BOOLEAN := FALSE;
75 l_trans_currency_code VARCHAR2(15) := NULL;
76 l_rate NUMBER := NULL;
77 l_mrc_max_roll_rate NUMBER := -1;
78 l_debug_proc_hdr VARCHAR2(100);
79
80 BEGIN
81 l_debug_proc_hdr := G_DEBUG_PKG_HDR || 'Get_Rate.';
82
83 IF (G_PROC_LEVEL >= G_DEBUG_LEVEL )
84 THEN
85 FND_LOG.STRING(G_PROC_LEVEL, l_debug_proc_hdr||'.BEGIN'
86 , 'Entering Get_rate' );
87 END IF;
88
89 -- Bug fix 3975695: Moved the codes to assign default values from
90 -- declaration to here
91 l_inherit_ctype_Flag := 'N';
92 l_conversion_flag := 'Y';
93 l_inherited_conversion_type := p_trans_conversion_type;
94 p_result_code := 'HEADER VALID ';
95
96 BEGIN
97
98 IF (G_PROC_LEVEL >= G_STATEMENT_LEVEL )
99 THEN
100 FND_LOG.STRING(G_PROC_LEVEL, l_debug_proc_hdr||'.BEGIN'
101 , 'retrieving setup data from GL ledger relationships' );
102 END IF;
103 -- Retrieve setup from GL ledger relationships
104 SELECT glr.gl_je_conversion_set_id
105 , glr.target_currency_code
106 , glr.alc_default_conv_rate_type
107 , glr.alc_no_rate_action_code
108 , glr.alc_inherit_conversion_type
109 , DECODE(glr.alc_no_rate_action_code,'REPORT_ERROR',0,nvl(glr.alc_max_days_roll_rate,-1))
110 INTO l_je_conv_set_id
111 , l_target_curr
112 , l_conversion_type
113 , l_no_rate_action
114 , l_inherit_ctype_flag
115 , l_mrc_max_roll_rate
116 FROM gl_ledger_relationships glr
117 WHERE glr.source_ledger_id = p_primary_set_of_books_id
118 AND glr.target_ledger_id = p_reporting_set_of_books_id
119 AND glr.relationship_type_code IN ('JOURNAL', 'SUBLEDGER')
120 AND glr.target_ledger_category_code = 'ALC'
121 AND glr.application_id = p_application_id
122 AND glr.relationship_enabled_flag = 'Y'
123 AND (p_org_id IS NULL
124 OR glr.org_id = -99
125 OR glr.org_id = NVL(p_org_id, -99))
126 AND (NVL(p_fa_book_type_code, '-99') = '-99'
127 OR EXISTS
128 (SELECT 'FA book type is enabled'
129 FROM FA_MC_BOOK_CONTROLS MC
130 WHERE MC.set_of_books_id = glr.target_ledger_id
131 AND MC.book_type_code = p_fa_book_type_code
132 AND MC.primary_set_of_books_id = glr.source_ledger_id
133 AND MC.enabled_flag = 'Y'))
134 AND rownum = 1;
135
136 -- Get the source ledger currency
137 gl_mc_info.get_ledger_currency(p_primary_set_of_books_id, l_src_curr);
138 EXCEPTION
139 WHEN OTHERS THEN
140 p_result_code := 'RSOB NOT FOUND';
141 l_conversion_flag := 'E';
142 END;
143
144 IF p_application_id = 101 AND l_conversion_flag = 'Y' -- GL
145 THEN
146 -- CHeck if it is converted for the passed journal source and category
147 -- based on the conversion set rules defined
148 BEGIN
149 SELECT include_flag
150 INTO l_conversion_flag
151 FROM gl_je_inclusion_rules
152 WHERE je_rule_set_id = l_je_conv_set_id
153 AND je_source_name = p_je_source_name
154 AND je_category_name = p_je_category_name;
155 EXCEPTION
156 WHEN OTHERS THEN
157 BEGIN
158 SELECT include_flag
159 INTO l_conversion_flag
160 FROM gl_je_inclusion_rules
161 WHERE je_rule_set_id = l_je_conv_set_id
162 AND je_source_name = p_je_source_name
163 AND je_category_name = 'Other';
164 EXCEPTION
165 WHEN OTHERS THEN
166 BEGIN
167 SELECT include_flag
168 INTO l_conversion_flag
169 FROM gl_je_inclusion_rules
170 WHERE je_rule_set_id = l_je_conv_set_id
171 AND je_source_name = 'Other'
172 AND je_category_name = p_je_category_name;
173 EXCEPTION
174 WHEN OTHERS THEN
175 BEGIN
176 SELECT include_flag
177 INTO l_conversion_flag
178 FROM gl_je_inclusion_rules
179 WHERE je_rule_set_id = l_je_conv_set_id
180 AND je_source_name = 'Other'
181 AND je_category_name = 'Other';
182 EXCEPTION
183 WHEN OTHERS THEN
184 l_conversion_flag := 'N';
185 p_result_code := 'NO CONVERSION';
186 END;
187 END;
188 END;
189 END;
190 END IF; -- IF p_application_id = 101 AND l_conversion_flag = 'Y'
191
192 l_tmp_conversion_type := l_conversion_type;
193
194 -- The inherit option is ignored if the conversion type in the original
195 -- transaction is NULL
196 IF (l_inherit_ctype_flag = 'Y')
197 AND (l_inherited_conversion_type IS NOT NULL)
198 AND (l_inherited_conversion_type <> 'EMU FIXED')
199 AND (l_inherited_conversion_type <> 'User') THEN
200 --
201 -- The above condition was included so that when inherit
202 -- converstion type is enabled and User rate type is used,
203 -- the conversion will be done from Primary to reporting
204 -- Using conversion type from gl_mc_reporting_options instead
205 -- of User.
206 l_conversion_type := l_inherited_conversion_type;
207 END IF;
208
209 IF l_conversion_flag = 'Y'
210 THEN
211 IF p_trans_currency_code = l_target_curr OR
212 p_trans_currency_code = 'STAT'
213 THEN
214 p_trans_conversion_type := 'User';
215 p_trans_conversion_rate := 1;
216 p_denominator_rate := 1;
217 p_numerator_rate := 1;
218 ELSE
219 BEGIN
220 gl_currency_api.get_relation(p_trans_currency_code,
221 l_target_curr,
222 TRUNC(p_trans_conversion_date),
223 l_fixed_rate,
224 l_relationship);
225 EXCEPTION -- of relation
226 WHEN OTHERS THEN
227 /* No Proper Relation is found for the calculation of Conversion Rate */
228 p_result_code := 'IMPROPER RELATION';
229 END;
230
231 IF l_relationship NOT IN ('EURO-EMU','EMU-EURO','EMU-EMU','EURO-EURO')
232 THEN
233 IF p_trans_currency_code = l_src_curr
234 THEN
235 /* For Trans = Source <> Target */
236 p_trans_conversion_type := l_conversion_type;
237 l_trans_currency_code := p_trans_currency_code;
238 ELSE
239 IF NVL(p_trans_conversion_type, 'User') = 'User'
240 THEN
241 /* User defined rate is used for the calculation of the conversion rate */
242 l_trans_currency_code := l_src_Curr;
243 p_trans_conversion_type := l_conversion_type;
244 l_xrate := TRUE;
245 ELSE
246 l_trans_currency_code := p_trans_currency_code;
247 p_trans_conversion_type := l_conversion_type;
248 END IF; -- for p_trans_conversion_type.
249 END IF; -- IF p_trans_currency_code = l_src_curr
250 ELSE
251 /* Fixed Derived Factor is user for calculating the conversion rate */
252 p_trans_conversion_type := 'EMU FIXED';
253 l_trans_currency_code := p_trans_currency_code;
254 END IF; -- IF l_relationship NOT IN ('EURO-EMU', ...
255
256 BEGIN
257 gl_currency_api.get_closest_triangulation_rate(
258 l_trans_currency_code,
259 l_target_curr,
260 TRUNC(p_trans_conversion_date),
261 p_trans_conversion_type,
262 l_mrc_max_roll_rate,
263 p_denominator_rate,
264 p_numerator_rate,
265 l_rate);
266 EXCEPTION
267 WHEN OTHERS THEN
268 /* No Rate found */
269 p_result_code := 'NO RATE FOUND';
270 END;
271
272 IF l_xrate AND p_result_code <> 'NO RATE FOUND'
273 THEN
274 /*calculating the cross rates */
275 p_numerator_rate := p_trans_conversion_rate*p_numerator_rate;
276 p_trans_conversion_rate := p_trans_conversion_rate*l_rate;
277 p_trans_conversion_type := 'User';
278 ELSE -- l_xrate
279 p_trans_conversion_rate := l_rate;
280 END IF; -- IF l_xrate AND p_result_code <> 'NO RATE FOUND'
281 END IF; -- IF p_trans_currency_code = l_target_curr OR ...
282 ELSIF l_conversion_flag = 'N'
283 THEN
284 p_result_code := 'NO CONVERSION';
285 END IF; -- IF l_conversion_flag = 'Y'
286
287 IF p_application_id <> 101 AND p_result_code <> 'HEADER VALID ' -- NOT GL
288 THEN
289 IF p_result_code = 'RSOB NOT FOUND'
290 THEN
291 fnd_message.set_name('SQLGL', 'MRC_RSOB_NOT_FOUND');
292 fnd_message.set_token('RSOB', p_reporting_set_of_books_id);
293 ELSIF p_result_code = 'NO CONVERSION'
294 THEN
295 fnd_message.set_name('SQLGL', 'MRC_CONVERSION_RULE_NOT_FOUND');
296 ELSIF p_result_code = 'IMPROPER RELATION'
297 THEN
298 fnd_message.set_name('SQLGL', 'MRC_NO_RELATIONSHIP_FOUND');
299 fnd_message.set_token('TCURR', p_trans_currency_code);
300 fnd_message.set_token('RCURR', l_trans_currency_code);
301 ELSE
302 IF p_trans_conversion_type = 'User' THEN
303 p_trans_conversion_type := l_tmp_conversion_type;
304 END IF;
305
306 BEGIN
307 SELECT user_conversion_type
308 INTO l_user_conversion_type
309 FROM gl_daily_conversion_types
310 WHERE conversion_type = p_trans_conversion_type;
311 EXCEPTION
312 WHEN OTHERS THEN
313 l_user_conversion_type := p_trans_conversion_type;
314 END;
315
316 fnd_message.set_name('SQLGL', 'MRC_RATE_NOT_FOUND');
317 fnd_message.set_token('FROM', l_trans_currency_code);
318 fnd_message.set_token('TO', l_target_curr);
319 -- 11/23/03 Updated by LPOON: Changed to display 4-digit year
320 fnd_message.set_token('TRANS_DATE', TO_CHAR(p_trans_conversion_date,
321 'DD-MON-YYYY'));
322 fnd_message.set_token('TYPE', l_user_conversion_type);
323 END IF; -- IF p_result_code = 'RSOB NOT FOUND'
324
325 fnd_message.set_token('MODULE','GLMCCURB');
326 app_exception.raise_exception;
327 END IF; -- IF p_application_id <> 101 AND ...
328 EXCEPTION
329 WHEN OTHERS THEN
330 app_exception.raise_exception;
331 END get_rate;
332 --
333 -- R11i.X Changes - rename the parameters
334 --
335 PROCEDURE get_rate(p_primary_set_of_books_id IN NUMBER,
336 p_reporting_set_of_books_id IN NUMBER,
337 p_trans_date IN DATE,
338 p_trans_currency_code IN VARCHAR2,
339 p_trans_conversion_type IN OUT NOCOPY VARCHAR2,
340 p_trans_conversion_date IN OUT NOCOPY DATE,
341 p_trans_conversion_rate IN OUT NOCOPY NUMBER,
342 p_application_id IN NUMBER,
343 p_org_id IN NUMBER,
344 p_fa_book_type_code IN VARCHAR2,
345 p_je_source_name IN VARCHAR2,
346 p_je_category_name IN VARCHAR2,
347 p_result_code IN OUT NOCOPY VARCHAR2) IS
348 l_numerator_rate NUMBER;
349 l_denominator_rate NUMBER;
350 BEGIN
351 GL_MC_CURRENCY_PKG.get_rate(
352 p_primary_set_of_books_id ,
353 p_reporting_set_of_books_id,
354 p_trans_date ,
355 p_trans_currency_code ,
356 p_trans_conversion_type ,
357 p_trans_conversion_date ,
358 p_trans_conversion_rate ,
359 p_application_id ,
360 p_org_id ,
361 p_fa_book_type_code ,
362 p_je_source_name ,
363 p_je_category_name ,
364 p_result_code ,
365 l_numerator_rate ,
366 l_denominator_rate);
367 END ;
368 --
369 PROCEDURE GetCurrencyDetails( p_currency_code IN VARCHAR2,
370 p_precision OUT NOCOPY NUMBER,
371 p_mau OUT NOCOPY NUMBER ) IS
372 i BINARY_INTEGER := 0;
373 BEGIN
374 WHILE i < NextElement
375 LOOP
376 EXIT WHEN CurrencyCode(i) = p_currency_code;
377 i := i + 1;
378 END LOOP;
379
380 IF i = NextElement
381 THEN
382
383 DECLARE
384 l_Precision NUMBER;
385 l_Mau NUMBER;
386 BEGIN
387 BEGIN
388 SELECT precision,
389 minimum_accountable_unit
390 INTO l_Precision,
391 l_Mau
392 FROM fnd_currencies
393 WHERE currency_code = p_currency_code;
394 EXCEPTION
395 WHEN NO_DATA_FOUND THEN
396 fnd_message.set_name('SQLGL', 'MRC_DOCUMENT_NOT_FOUND');
397 fnd_message.set_token('MODULE','GLMCCURB');
398 fnd_message.set_token('CURRENCY', p_currency_code);
399 RAISE_APPLICATION_ERROR(-20010, fnd_message.get);
400 WHEN OTHERS THEN
401 fnd_message.set_name('SQLGL','MRC_TABLE_ERROR');
402 fnd_message.set_token('MODULE','GLMCCURB');
403 fnd_message.set_token('TABLE','FND_CURRENCIES');
404 RAISE_APPLICATION_ERROR(-20020, fnd_message.get);
405 END;
406 Precision(i) := l_Precision;
407 Mau(i) := l_Mau;
408 END;
409
410 CurrencyCode(i) := p_currency_code;
411 NextElement := i + 1;
412
413 END IF;
414 p_precision := Precision(i);
415 p_mau := Mau(i);
416
417 END GetCurrencyDetails;
418 --
419 FUNCTION get_default_rate (
420 p_from_currency VARCHAR2,
421 p_to_currency VARCHAR2,
422 p_conversion_date DATE,
423 p_conversion_type VARCHAR2 DEFAULT NULL ) RETURN NUMBER IS
424 l_rate NUMBER;
425 BEGIN
426 BEGIN
427 l_rate := gl_currency_api.get_rate(p_from_currency,
428 p_to_currency,
429 TRUNC(p_conversion_date),
430 p_conversion_type);
431 EXCEPTION
432 WHEN OTHERS THEN
433 l_rate := NULL;
434 END;
435
436 return( l_rate );
437 END get_default_rate;
438 --
439 FUNCTION CurrRound( p_amount IN NUMBER, p_currency_code IN VARCHAR2) RETURN NUMBER IS
440 l_precision NUMBER(1);
441 l_mau NUMBER;
442 BEGIN
443 GetCurrencyDetails( p_currency_code, l_precision, l_mau );
444 IF l_mau IS NOT NULL
445 THEN
446 RETURN( ROUND( p_amount / l_mau) * l_mau );
447 ELSE
448 RETURN( ROUND( p_amount, l_precision ));
449 END IF;
450 END CurrRound;
451 --
452 END gl_mc_currency_pkg;
[Home] [Help]