1 PACKAGE BODY gl_mc_currency_pkg AS
2 /* $Header: glmccurb.pls 120.16 2011/01/25 15:17:07 phmullap 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 in( 'ALC','SECONDARY') -- Bug11672688
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' AND (p_fa_book_type_code is null) -- GL Bug11672688
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
156 EXCEPTION
157 WHEN OTHERS THEN
158 BEGIN
159 SELECT include_flag
160 INTO l_conversion_flag
161 FROM gl_je_inclusion_rules
162 WHERE je_rule_set_id = l_je_conv_set_id
163 AND je_source_name = p_je_source_name
164 AND je_category_name = 'Other';
165 EXCEPTION
166 WHEN OTHERS THEN
167 BEGIN
168 SELECT include_flag
169 INTO l_conversion_flag
170 FROM gl_je_inclusion_rules
171 WHERE je_rule_set_id = l_je_conv_set_id
172 AND je_source_name = 'Other'
173 AND je_category_name = p_je_category_name;
174 EXCEPTION
175 WHEN OTHERS THEN
176 BEGIN
177 SELECT include_flag
178 INTO l_conversion_flag
179 FROM gl_je_inclusion_rules
180 WHERE je_rule_set_id = l_je_conv_set_id
181 AND je_source_name = 'Other'
182 AND je_category_name = 'Other';
183 EXCEPTION
184 WHEN OTHERS THEN
185 l_conversion_flag := 'N';
186 p_result_code := 'NO CONVERSION';
187 END;
188 END;
189 END;
190 END;
191 END IF; -- IF p_application_id = 101 AND l_conversion_flag = 'Y'
192
193 l_tmp_conversion_type := l_conversion_type;
194
195 -- The inherit option is ignored if the conversion type in the original
196 -- transaction is NULL
197 IF (l_inherit_ctype_flag = 'Y')
198 AND (l_inherited_conversion_type IS NOT NULL)
199 AND (l_inherited_conversion_type <> 'EMU FIXED')
200 AND (l_inherited_conversion_type <> 'User') THEN
201 --
202 -- The above condition was included so that when inherit
203 -- converstion type is enabled and User rate type is used,
204 -- the conversion will be done from Primary to reporting
205 -- Using conversion type from gl_mc_reporting_options instead
206 -- of User.
207 l_conversion_type := l_inherited_conversion_type;
208 END IF;
209
210 IF l_conversion_flag = 'Y'
211 THEN
212 IF p_trans_currency_code = l_target_curr OR
213 p_trans_currency_code = 'STAT'
214 THEN
215 p_trans_conversion_type := 'User';
216 p_trans_conversion_rate := 1;
217 p_denominator_rate := 1;
218 p_numerator_rate := 1;
219 ELSE
220 BEGIN
221 gl_currency_api.get_relation(p_trans_currency_code,
222 l_target_curr,
223 TRUNC(p_trans_conversion_date),
224 l_fixed_rate,
225 l_relationship);
226 EXCEPTION -- of relation
227 WHEN OTHERS THEN
228 /* No Proper Relation is found for the calculation of Conversion Rate */
229 p_result_code := 'IMPROPER RELATION';
230 END;
231
232 IF l_relationship NOT IN ('EURO-EMU','EMU-EURO','EMU-EMU','EURO-EURO')
233 THEN
234 IF p_trans_currency_code = l_src_curr
235 THEN
236 /* For Trans = Source <> Target */
237 p_trans_conversion_type := l_conversion_type;
238 l_trans_currency_code := p_trans_currency_code;
239 ELSE
240 IF NVL(p_trans_conversion_type, 'User') = 'User'
241 THEN
242 /* User defined rate is used for the calculation of the conversion rate */
243 l_trans_currency_code := l_src_Curr;
244 p_trans_conversion_type := l_conversion_type;
245 l_xrate := TRUE;
246 ELSE
247 l_trans_currency_code := p_trans_currency_code;
248 p_trans_conversion_type := l_conversion_type;
249 END IF; -- for p_trans_conversion_type.
250 END IF; -- IF p_trans_currency_code = l_src_curr
251 ELSE
252 /* Fixed Derived Factor is user for calculating the conversion rate */
253 p_trans_conversion_type := 'EMU FIXED';
254 l_trans_currency_code := p_trans_currency_code;
255 END IF; -- IF l_relationship NOT IN ('EURO-EMU', ...
256
257 BEGIN
258
259
260 gl_currency_api.get_closest_triangulation_rate(
261 l_trans_currency_code,
262 l_target_curr,
263 TRUNC(p_trans_conversion_date),
264 p_trans_conversion_type,
265 l_mrc_max_roll_rate,
266 p_denominator_rate,
267 p_numerator_rate,
268 l_rate);
269 EXCEPTION
270 WHEN OTHERS THEN
271 /* No Rate found */
272 p_result_code := 'NO RATE FOUND';
273 END;
274
275 IF l_xrate AND p_result_code <> 'NO RATE FOUND'
276 THEN
277 /*calculating the cross rates */
278 p_numerator_rate := p_trans_conversion_rate*p_numerator_rate;
279 p_trans_conversion_rate := p_trans_conversion_rate*l_rate;
280 p_trans_conversion_type := 'User';
281 ELSE -- l_xrate
282 p_trans_conversion_rate := l_rate;
283 END IF; -- IF l_xrate AND p_result_code <> 'NO RATE FOUND'
284 END IF; -- IF p_trans_currency_code = l_target_curr OR ...
285 ELSIF l_conversion_flag = 'N'
286 THEN
287 p_result_code := 'NO CONVERSION';
288 END IF; -- IF l_conversion_flag = 'Y'
289
290 IF p_application_id <> 101 AND p_result_code <> 'HEADER VALID ' -- NOT GL
291 THEN
292 IF p_result_code = 'RSOB NOT FOUND'
293 THEN
294 fnd_message.set_name('SQLGL', 'MRC_RSOB_NOT_FOUND');
295 fnd_message.set_token('RSOB', p_reporting_set_of_books_id);
296 ELSIF p_result_code = 'NO CONVERSION'
297 THEN
298 fnd_message.set_name('SQLGL', 'MRC_CONVERSION_RULE_NOT_FOUND');
299 ELSIF p_result_code = 'IMPROPER RELATION'
300 THEN
301 fnd_message.set_name('SQLGL', 'MRC_NO_RELATIONSHIP_FOUND');
302 fnd_message.set_token('TCURR', p_trans_currency_code);
303 fnd_message.set_token('RCURR', l_trans_currency_code);
304 ELSE
305 IF p_trans_conversion_type = 'User' THEN
306 p_trans_conversion_type := l_tmp_conversion_type;
307 END IF;
308
309 BEGIN
310 SELECT user_conversion_type
311 INTO l_user_conversion_type
312 FROM gl_daily_conversion_types
313 WHERE conversion_type = p_trans_conversion_type;
314 EXCEPTION
315 WHEN OTHERS THEN
316 l_user_conversion_type := p_trans_conversion_type;
317 END;
318
319 fnd_message.set_name('SQLGL', 'MRC_RATE_NOT_FOUND');
320 fnd_message.set_token('FROM', l_trans_currency_code);
321 fnd_message.set_token('TO', l_target_curr);
322 -- 11/23/03 Updated by LPOON: Changed to display 4-digit year
323 fnd_message.set_token('TRANS_DATE', TO_CHAR(p_trans_conversion_date,
324 'DD-MON-YYYY'));
325 fnd_message.set_token('TYPE', l_user_conversion_type);
326 END IF; -- IF p_result_code = 'RSOB NOT FOUND'
327
328 fnd_message.set_token('MODULE','GLMCCURB');
329 app_exception.raise_exception;
330 END IF; -- IF p_application_id <> 101 AND ...
331 EXCEPTION
332 WHEN OTHERS THEN
333 app_exception.raise_exception;
334 END get_rate;
335 --
336 -- R11i.X Changes - rename the parameters
337 --
338 PROCEDURE get_rate(p_primary_set_of_books_id IN NUMBER,
339 p_reporting_set_of_books_id IN NUMBER,
340 p_trans_date IN DATE,
341 p_trans_currency_code IN VARCHAR2,
342 p_trans_conversion_type IN OUT NOCOPY VARCHAR2,
343 p_trans_conversion_date IN OUT NOCOPY DATE,
344 p_trans_conversion_rate IN OUT NOCOPY NUMBER,
345 p_application_id IN NUMBER,
346 p_org_id IN NUMBER,
347 p_fa_book_type_code IN VARCHAR2,
348 p_je_source_name IN VARCHAR2,
349 p_je_category_name IN VARCHAR2,
350 p_result_code IN OUT NOCOPY VARCHAR2) IS
351 l_numerator_rate NUMBER;
352 l_denominator_rate NUMBER;
353 BEGIN
354 GL_MC_CURRENCY_PKG.get_rate(
355 p_primary_set_of_books_id ,
356 p_reporting_set_of_books_id,
357 p_trans_date ,
358 p_trans_currency_code ,
359 p_trans_conversion_type ,
360 p_trans_conversion_date ,
361 p_trans_conversion_rate ,
362 p_application_id ,
363 p_org_id ,
364 p_fa_book_type_code ,
365 p_je_source_name ,
366 p_je_category_name ,
367 p_result_code ,
368 l_numerator_rate ,
369 l_denominator_rate);
370 END ;
371 --
372 PROCEDURE GetCurrencyDetails( p_currency_code IN VARCHAR2,
373 p_precision OUT NOCOPY NUMBER,
374 p_mau OUT NOCOPY NUMBER ) IS
375 i BINARY_INTEGER := 0;
376 BEGIN
377 WHILE i < NextElement
378 LOOP
379 EXIT WHEN CurrencyCode(i) = p_currency_code;
380 i := i + 1;
381 END LOOP;
382
383 IF i = NextElement
384 THEN
385
386 DECLARE
387 l_Precision NUMBER;
388 l_Mau NUMBER;
389 BEGIN
390 BEGIN
391 SELECT precision,
392 minimum_accountable_unit
393 INTO l_Precision,
394 l_Mau
395 FROM fnd_currencies
396 WHERE currency_code = p_currency_code;
397 EXCEPTION
398 WHEN NO_DATA_FOUND THEN
399 fnd_message.set_name('SQLGL', 'MRC_DOCUMENT_NOT_FOUND');
400 fnd_message.set_token('MODULE','GLMCCURB');
401 fnd_message.set_token('CURRENCY', p_currency_code);
402 RAISE_APPLICATION_ERROR(-20010, fnd_message.get);
403 WHEN OTHERS THEN
404 fnd_message.set_name('SQLGL','MRC_TABLE_ERROR');
405 fnd_message.set_token('MODULE','GLMCCURB');
406 fnd_message.set_token('TABLE','FND_CURRENCIES');
407 RAISE_APPLICATION_ERROR(-20020, fnd_message.get);
408 END;
409 Precision(i) := l_Precision;
410 Mau(i) := l_Mau;
411 END;
412
413 CurrencyCode(i) := p_currency_code;
414 NextElement := i + 1;
415
416 END IF;
417 p_precision := Precision(i);
418 p_mau := Mau(i);
419
420 END GetCurrencyDetails;
421 --
422 FUNCTION get_default_rate (
423 p_from_currency VARCHAR2,
424 p_to_currency VARCHAR2,
425 p_conversion_date DATE,
426 p_conversion_type VARCHAR2 DEFAULT NULL ) RETURN NUMBER IS
427 l_rate NUMBER;
428 BEGIN
429 BEGIN
430 l_rate := gl_currency_api.get_rate(p_from_currency,
431 p_to_currency,
432 TRUNC(p_conversion_date),
433 p_conversion_type);
434 EXCEPTION
435 WHEN OTHERS THEN
436 l_rate := NULL;
437 END;
438
439 return( l_rate );
440 END get_default_rate;
441 --
442 FUNCTION CurrRound( p_amount IN NUMBER, p_currency_code IN VARCHAR2) RETURN NUMBER IS
443 l_precision NUMBER(1);
444 l_mau NUMBER;
445 BEGIN
446 GetCurrencyDetails( p_currency_code, l_precision, l_mau );
447 IF l_mau IS NOT NULL
448 THEN
449 RETURN( ROUND( p_amount / l_mau) * l_mau );
450 ELSE
451 RETURN( ROUND( p_amount, l_precision ));
452 END IF;
453 END CurrRound;
454 --
455 END gl_mc_currency_pkg;
[Home] [Help]