1 PACKAGE BODY OKC_CURRENCY_API AS
2 /* $Header: OKCPCURB.pls 120.1 2006/02/27 14:17:38 hvaladip noship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5 -- Function Get_OU_Currency ( Org_ID IN NUMBER )
6 -- Returns Currency code
7 -- Parameters - If Org_ID IS NULL, then determines the functional currency
8 -- for the operating unit, using FND_Profile Org_ID.
9
10 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
11
12 FUNCTION GET_OU_CURRENCY ( p_ORG_ID IN NUMBER )
13 RETURN VARCHAR2
14 IS
15
16 l_org_id number;
17 l_curr_code varchar2(10);
18
19 Begin
20 l_org_id := p_org_id;
21
22 IF l_org_id IS NULL then
23 fnd_profile.get('ORG_ID',l_org_id);
24 END IF;
25
26 /****
27 commented to avoid OKX and performance issue
28 bug# 5030628
29
30 select gl.currency_code
31 into l_curr_code
32 from okx_set_of_books_v gl,
33 okx_organization_defs_v ou
34 where ou.id1 = l_org_id
35 and ou.organization_type = 'OPERATING_UNIT'
36 and ou.information_type = 'Operating Unit Information'
37 and gl.set_of_books_id = ou.set_of_books_id ;
38 ***/
39
40 select gl.currency_code
41 into l_curr_code
42 from HR_ORGANIZATION_INFORMATION OI2,
43 HR_ORGANIZATION_INFORMATION OI1,
44 HR_ALL_ORGANIZATION_UNITS OU,
45 GL_SETS_OF_BOOKS gl
46 where oi1.organization_id = ou.organization_id
47 and oi2.organization_id = ou.organization_id
48 and oi1.org_information_context = 'CLASS'
49 and oi1.org_information1 = 'OPERATING_UNIT'
50 and oi2.org_information_context = 'Operating Unit Information'
51 and oi2.ORG_INFORMATION3 = gl.set_of_books_id
52 and ou.organization_id = l_org_id;
53
54
55 Return l_curr_code;
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58
59 return NULL;
60
61 END GET_OU_CURRENCY;
62
63
64 -- Function Get_SOB_Currency ( SOB_ID IN NUMBER )
65 -- Returns Currency Code
66 -- Parameters - Set of Books ID needed.
67
68 FUNCTION GET_SOB_CURRENCY ( p_SOB_ID IN NUMBER )
69 RETURN VARCHAR2
70 IS
71
72 l_curr_code varchar2(10);
73
74 BEGIN
75
76 select currency_code
77 into l_curr_code
78 from okx_set_of_books_v
79 where set_of_books_id = p_sob_id;
80
81 Return l_curr_code;
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 return NULL;
85
86 END GET_SOB_CURRENCY ;
87
88
89 -- Function Get_OU_SOB ( ORG_ID IN NUMBER )
90 -- Returns SOB_ID
91 -- Parameters - If Org_ID is not provided, then determines the set of books for
92 -- the current OU.
93
94 FUNCTION GET_OU_SOB ( p_ORG_ID IN NUMBER )
95 RETURN NUMBER
96 IS
97
98 l_org_id number;
99 l_sob_id number;
100
101 BEGIN
102
103 l_org_id := p_org_id;
104
105 IF p_org_id IS NULL then
106 fnd_profile.get('ORG_ID',l_org_id);
107 END IF;
108
109 /*****
110 commented to avoid OKX and performance issue
111 bug# 5030628
112
113 select ou.set_of_books_id
114 into l_sob_id
115 from okx_organization_defs_v ou where ou.id1 = l_org_id and ou.organization_type = 'OPERATING_UNIT'
116 and ou.information_type = 'Operating Unit Information';
117 *******/
118
119 select oi2.ORG_INFORMATION3
120 into l_sob_id
121 from HR_ORGANIZATION_INFORMATION OI2,
122 HR_ORGANIZATION_INFORMATION OI1,
123 HR_ALL_ORGANIZATION_UNITS OU
124 where oi1.organization_id = ou.organization_id
125 and oi2.organization_id = ou.organization_id
126 and oi1.org_information_context = 'CLASS'
127 and oi1.org_information1 = 'OPERATING_UNIT'
128 and oi2.org_information_context = 'Operating Unit Information'
129 and ou.organization_id = l_org_id;
130
131 Return l_sob_id;
132 EXCEPTION
133 WHEN NO_DATA_FOUND THEN
134 return NULL;
135
136 END GET_OU_SOB;
137
138
139 -- Function Get_OU_SOB_Name ( ORG_ID IN NUMBER )
140 -- Returns SOB Name
141 -- Parameters - If Org_ID is not provided, then determines the set of books for
142 -- the current OU.
143
144 FUNCTION GET_OU_SOB_NAME ( p_ORG_ID IN NUMBER )
145 RETURN VARCHAR2
146 IS
147
148 l_org_id number;
149 l_sob_name varchar2(30);
150
151 BEGIN
152
153 l_org_id := p_org_id;
154
155 IF p_org_id IS NULL then
156 fnd_profile.get('ORG_ID',l_org_id);
157 END IF;
158
159 /****
160 commented to avoid OKX and performance issue
161 bug# 5030628
162
163 select gl.name
164 into l_sob_name
165 from okx_organization_defs_v ou,
166 okx_set_of_books_v gl
167 where ou.id1 = l_org_id
168 and ou.organization_type = 'OPERATING_UNIT'
169 and ou.information_type = 'Operating Unit Information'
170 and gl.set_of_books_id = ou.set_of_books_id;
171 ****/
172
173 select gl.name
174 into l_sob_name
175 from HR_ORGANIZATION_INFORMATION OI2,
176 HR_ORGANIZATION_INFORMATION OI1,
177 HR_ALL_ORGANIZATION_UNITS OU,
178 GL_SETS_OF_BOOKS gl
179 where oi1.organization_id = ou.organization_id
180 and oi2.organization_id = ou.organization_id
181 and oi1.org_information_context = 'CLASS'
182 and oi1.org_information1 = 'OPERATING_UNIT'
183 and oi2.org_information_context = 'Operating Unit Information'
184 and oi2.ORG_INFORMATION3 = gl.set_of_books_id
185 and ou.organization_id = l_org_id;
186
187
188 Return l_sob_name;
189 EXCEPTION
190 WHEN NO_DATA_FOUND THEN
191 return NULL;
192
193 END GET_OU_SOB_NAME;
194
195
196 -- Function IS_User_Rate_Allowed ( From_Currency, To_Currency, Effective_Date )`
197 -- Returns BOOLEAN
198 -- Parameters - Needs From currency, To currency and Effective date.
199 -- Effective date defaults to sysdate.
200 -- Desc If there is a fixed relationship between the two currencies,
201 -- then USER rate type is not allowed.
202
203 FUNCTION IS_USER_RATE_ALLOWED ( p_FROM_CURRENCY IN VARCHAR2,
204 p_TO_CURRENCY IN VARCHAR2,
205 p_EFFECTIVE_DATE IN DATE )
206 RETURN BOOLEAN
207 IS
208
209 BEGIN
210
211 IF gl_currency_api.is_fixed_rate( p_from_currency, p_to_currency,
212 p_effective_date ) = 'Y' then
213 Return FALSE;
214 ELSE Return TRUE;
215 END IF;
216
217 END IS_USER_RATE_ALLOWED;
218
219
220 -- Function Get_Rate ( From_Currency, To_currency, Conversion_date,
221 -- Conversion_type, Conversion_Rate )
222 -- Returns Exchage Rate between the two currencies.
223 -- Parameters - All are needed. Conversion rate is expected only if the conversion
224 -- conversion type is USER. If USER is applicable, then the
225 -- conversion rate provided is returned, else obtained from
226 -- the system.
227
228 FUNCTION GET_RATE ( p_FROM_CURRENCY IN VARCHAR2,
229 p_TO_CURRENCY IN VARCHAR2,
230 p_CONVERSION_DATE IN DATE,
231 p_CONVERSION_TYPE IN VARCHAR2,
232 p_CONVERSION_RATE IN NUMBER )
233 RETURN NUMBER
234 IS
235
236 l_rate number;
237
238 BEGIN
239
240 IF p_conversion_type = 'User' then
241 IF IS_User_Rate_Allowed(p_from_currency, p_to_currency, p_conversion_date) then
242 Return p_conversion_rate;
243 Else
244 Return -1;
245 End IF;
246 ELSE
247 Return gl_currency_api.get_rate(p_from_currency, p_to_currency,
248 p_conversion_date, p_conversion_type);
249 END IF;
250
251 END GET_RATE;
252
253 -- Procedure Get_Rate ( From_Currency, To_Currency, Conversion_Date,
254 -- Conversion_type, Conversion_Rate, Euro_Rate );
255 -- Returns Conversion_Rate and Euro_Rate.
256 -- Parameters - All parameters are needed. Conversion_rate is IN OUT,
257 -- and Euro_rate is OUT.
258 -- Desc Conversion_rate is the exchange rate between the two currencies.
259 -- If the To currency is EMU, then the From currency to EURO rate
260 -- is also obtained.
261
262 PROCEDURE GET_RATE ( p_FROM_CURRENCY IN VARCHAR2,
263 p_TO_CURRENCY IN VARCHAR2,
264 p_CONVERSION_DATE IN DATE,
265 p_CONVERSION_TYPE IN VARCHAR2,
266 x_CONVERSION_RATE IN OUT NOCOPY NUMBER,
267 x_EURO_RATE OUT NOCOPY NUMBER ,
268 x_return_status OUT NOCOPY VARCHAR2)
269 IS
270
271 l_rate number;
272 l_euro_rate number;
273 l_fixed_rate boolean;
274 l_relation varchar2(15);
275 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
276
277 BEGIN
278 l_rate := get_rate(p_from_currency, p_to_currency, p_conversion_date, p_conversion_type,x_conversion_rate);
279
280 IF l_rate = -1 then
281 l_return_status := OKC_API.G_RET_STS_ERROR; -- 'user' conversion type is not allowed
282 OKC_API.SET_MESSAGE(G_APP_NAME,'OKC_NO_USER_CONVERSION');
283 ELSE
284 gl_currency_api.get_relation(p_from_currency, p_to_currency, p_conversion_date, l_fixed_rate, l_relation);
285 IF l_relation in ('OTHER-EMU','EMU-EMU','EURO-EMU') then
286 IF p_conversion_type = 'User' then
287
288 l_euro_rate := x_conversion_rate / get_rate(gl_currency_api.get_euro_code, p_to_currency, p_conversion_date, p_conversion_type);
289 ELSE
290 l_euro_rate := get_rate(p_from_currency,gl_currency_api.get_euro_code, p_conversion_date, p_conversion_type);
291 END IF;
292 END IF;
293 END IF;
294 x_euro_rate := l_euro_rate;
295 x_conversion_rate := l_rate;
296 x_return_status := l_return_status;
297
298 EXCEPTION
299 WHEN gl_currency_api.no_rate THEN
300 x_return_status := OKC_API.G_RET_STS_ERROR;
301 OKC_API.SET_MESSAGE(G_APP_NAME,'OKC_NO_CONVERSION_RATE');
302 --RAISE no_rate;
303 WHEN gl_currency_api.invalid_currency THEN
304 x_return_status := OKC_API.G_RET_STS_ERROR;
305 OKC_API.SET_MESSAGE(G_APP_NAME,'OKC_INVALID_CURRENCY');
306 -- RAISE invalid_currency;
307 WHEN OTHERS THEN
308 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
309 END GET_RATE;
310
311 -- Procedure Validate_Conversion_Attribs ( From_Currency, To_Currency,
312 -- Conversion_date, Conversion_type,
313 -- Conversion_rate, Status, Message )
314 -- Parameters - All currency conversion parameters are needed.
315 -- Desc Validates the currency conversion attributes.
316 -- The return status and message are set accordingly.
317
318 PROCEDURE VALIDATE_CONVERSION_ATTRIBS ( p_FROM_CURRENCY IN VARCHAR2,
319 p_TO_CURRENCY IN VARCHAR2,
320 p_CONVERSION_DATE IN DATE,
321 p_CONVERSION_TYPE IN VARCHAR2,
322 p_CONVERSION_RATE IN NUMBER,
323 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
324 x_MESSAGE OUT NOCOPY VARCHAR2 )
325 IS
326
327 l_rate number;
328 l_euro_rate number;
329 l_fixed_rate boolean;
330 l_relation varchar2(15);
331
332 BEGIN
333
334 x_return_status := 'S';
335
336 gl_currency_api.get_relation(p_from_currency, p_to_currency, p_conversion_date,
337 l_fixed_rate, l_relation);
338
339 IF p_conversion_type IS NULL then
340 x_return_status := 'E';
341 x_message := 'OKC_CONV_TYPE_NEEDED';
342 return;
343 ELSIF p_conversion_date IS NULL then
344 x_return_status := 'E';
345 x_message := 'OKC_CONV_DATE_NEEDED';
346 return;
347 ELSIF p_conversion_rate IS NULL then
348 x_return_status := 'E';
349 x_message := 'OKC_CONV_RATE_NEEDED';
350 return;
351 END IF;
352
353 IF IS_User_Rate_Allowed( p_from_currency, p_to_currency, p_conversion_date) then
354 IF p_conversion_type = 'User' and p_conversion_rate IS NULL then
355 x_return_status := 'E';
356 x_message := 'OKC_CONV_RATE_NEEDED';
357 return;
358 END IF;
359 ELSE
360 IF p_conversion_type = 'User' then
361 x_return_status := 'E';
362 x_message := 'OKC_CONV_TYPE_USER_NOT_ALLOWED';
363 return;
364 END IF;
365 END IF;
366
367 END VALIDATE_CONVERSION_ATTRIBS;
368
369
370 -- Procedure Convert_Amount ( From_Currency, To_Currency, Conversion_Date,
371 -- Conversion_Type, Conversion_Rate, Converted_Amount);
372 -- Parameters - From Currency, To Currency, Rate Type, Conversion Date,
373 -- Conversion rate needed if User rate type used and Converted amount
374 -- is returned.
375 -- Desc Provides the converted amount.
376
377 PROCEDURE CONVERT_AMOUNT ( p_FROM_CURRENCY IN VARCHAR2,
378 p_TO_CURRENCY IN VARCHAR2,
379 p_CONVERSION_DATE IN DATE,
380 p_CONVERSION_TYPE IN VARCHAR2,
381 p_AMOUNT IN NUMBER,
382 x_CONVERSION_RATE IN OUT NOCOPY NUMBER,
383 x_CONVERTED_AMOUNT OUT NOCOPY NUMBER )
384 IS
385 l_converted_amount number;
386 l_conversion_rate number;
387 l_denom_rate number;
388 l_num_rate number;
389
390 BEGIN
391
392 gl_currency_api.convert_closest_amount(p_from_currency, p_to_currency,
393 p_conversion_date, p_conversion_type,
394 x_conversion_rate, p_amount, 0,
395 l_converted_amount, l_denom_rate,
396 l_num_rate, l_conversion_rate);
397
398 x_converted_amount := l_converted_amount;
399 x_conversion_rate := l_conversion_rate;
400
401 END CONVERT_AMOUNT;
402
403
404 -- Procedure Get_Info ( Currency, Effective_Date, Rate. MAU, Type )
405 -- Parameters - Currency and effective date are needed.
406 -- Returns The Derive Type, Derive Factor for Euro related currencies,
407 -- and the Minimum Accountable Unit.
408
409 PROCEDURE GET_INFO( p_currency VARCHAR2,
410 p_eff_date DATE,
411 x_conversion_rate IN OUT NOCOPY NUMBER,
412 x_mau IN OUT NOCOPY NUMBER,
413 x_currency_type IN OUT NOCOPY VARCHAR2 ) IS
414
415 BEGIN
416 -- Get currency information from FND_CURRENCIES table
417 SELECT decode( derive_type,
418 'EURO', 'EURO',
419 'EMU', decode( sign( trunc(p_eff_date) - trunc(derive_effective)),
420 -1, 'OTHER', 'EMU'),
421 'OTHER' ),
422 decode( derive_type,
423 'EURO', 1,
424 'EMU', derive_factor,
425 'OTHER', -1 ),
426 nvl( minimum_accountable_unit, power( 10, (-1 * precision)))
427 INTO x_currency_type, x_conversion_rate, x_mau
428 FROM FND_CURRENCIES
429 WHERE currency_code = p_currency;
430
431
432 EXCEPTION
433 WHEN NO_DATA_FOUND THEN
434
435 x_conversion_rate := null;
436 x_mau := null;
437 x_currency_type := null;
438 END GET_INFO;
439
440 -- Function Get_Currency_Type ( Currency, Effective Date)
441 -- Returns Derive Type for the currency
442 -- Parameters are needed.
443 -- Desc Provides the type of currency whether EURO, EMU or OTHER.
444
445 FUNCTION GET_CURRENCY_TYPE( p_currency VARCHAR2,
446 p_eff_date DATE )
447 RETURN VARCHAR2
448 IS
449 l_currency_type varchar2(10);
450
451 BEGIN
452 -- Get currency information from FND_CURRENCIES table
453
454 SELECT decode( derive_type,
455 'EURO', 'EURO',
456 'EMU', decode( sign( trunc(p_eff_date) - trunc(derive_effective)),
457 -1, 'OTHER', 'EMU'),
458 'OTHER' )
459 INTO l_currency_type
460 FROM FND_CURRENCIES
461 WHERE currency_code = p_currency;
462
463 Return l_currency_type;
464
465 EXCEPTION
466 WHEN NO_DATA_FOUND THEN
467 return NULL;
468
469
470 END GET_CURRENCY_TYPE;
471
472 -- Bug# 2155930 Euro conversion
473
474 -- Function GET_EURO_CURRENCY_CODE ( Currency )
475 -- Returns Equivalent Euro Code for the Currency, if applicable,
476 -- Else returns the Currency itslef.
477 -- Parameters are needed.
478 -- Desc Provides the Euro currency code if EMU currency.
479 -- Needed for Post-EFC scenario after 01-Jan-2002.
480 -- And if SoB is already switched to EUR, using EFC.
481 -- Assumes the OKC Context is set, so it can call the
482 -- Get_OU_Currency api using the OKC Context.
483 -- Primarily intended for OKS Billing program.
484
485 FUNCTION GET_EURO_CURRENCY_CODE( p_currency VARCHAR2 )
486 RETURN VARCHAR2
487 IS
488 l_currency_type varchar2(10);
489 l_curr_euro varchar2(5);
490 BEGIN
491
492 l_currency_type := GET_CURRENCY_TYPE( p_currency, sysdate );
493 -- l_curr_euro := gl_currency_api.get_euro_code; /* Bugfix 2256060 - This line is moved after checking the currency_type */
494
495
496 /* If the OKC Context is not set then Org_ID would be returned as NULL
497 and the fnd_profile would be used.
498 If the context returns -99, then get_ou_curr api will return NULL */
499
500 -- commented out sysdate check for testing.
501
502 IF (l_currency_type = 'EMU' ) then
503 l_curr_euro := gl_currency_api.get_euro_code; /* Bugfix 2256060 - moved this line from above */
504 -- and sysdate >= to_date('01-jan-2002','dd-mon-yyyy')
505 IF (get_ou_currency(okc_context.get_okc_org_id) = l_curr_euro ) then
506 return l_curr_euro;
507 ELSE
508 return p_currency;
509 END IF;
510 ELSE
511 return p_currency;
512 END IF;
513
514 EXCEPTION
515 /* Bugfix 2256060 - Added the exception from GL to give a proper message */
516 WHEN GL_CURRENCY_API.INVALID_CURRENCY THEN
517 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
518 p_msg_name => 'OKC_NO_EURO_CURR'); /* Bugfix 2256060 - added new message */
519 raise;
520 WHEN OTHERS THEN
521 raise;
522
523 END GET_EURO_CURRENCY_CODE;
524
525
526 -- Function IS_EURO_CONVERSION_NEEDED ( Currency )
527 -- Returns Y if Euro conversion needed - for EMU Currencies
528 -- Parameters are needed.
529 -- Desc Y if Euro conversion needed.
530 -- Needed for Post-EFC scenario after 01-Jan-2002.
531
532 FUNCTION IS_EURO_CONVERSION_NEEDED( p_currency VARCHAR2 )
533 RETURN VARCHAR2
534 IS
535 l_currency_type varchar2(10);
536
537 BEGIN
538
539 l_currency_type := GET_CURRENCY_TYPE( p_currency, sysdate );
540
541 -- commented out sysdate check for testing.
542
543 IF (l_currency_type = 'EMU' ) then
544 -- and sysdate >= to_date('01-jan-2002','dd-mon-yyyy') ) then
545 return 'Y';
546 ELSE
547 return 'N';
548 END IF;
549
550 EXCEPTION
551 WHEN OTHERS THEN
552 raise;
553
554 END IS_EURO_CONVERSION_NEEDED;
555
556 END OKC_CURRENCY_API;
[Home] [Help]