[Home] [Help]
PACKAGE BODY: APPS.PO_CURRENCY_SV
Source
1 PACKAGE BODY po_currency_sv AS
2 /* $Header: POXDOCUB.pls 120.1 2010/12/31 11:41:02 dashah ship $*/
3
4 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
5 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
6
7 /*===========================================================================
8
9 FUNCTION NAME : val_currency()
10
11 ===========================================================================*/
12 g_pkg_name CONSTANT VARCHAR2(20) := 'PO_CURRENCY_SV'; --<Shared Proc FPJ>
13
14 FUNCTION val_currency(X_currency_code IN VARCHAR2) return BOOLEAN IS
15
16 X_progress varchar2(3) := NULL;
17 X_currency_code_v varchar2(15) := NULL;
18
19 BEGIN
20
21 X_progress := '010';
22
23 /* Check if the given Currency is active */
24
25 SELECT currency_code
26 INTO X_currency_code_v
27 FROM fnd_currencies
28 WHERE enabled_flag = 'Y'
29 AND sysdate between nvl(start_date_active, sysdate - 1)
30 AND nvl(end_date_active, sysdate + 1)
31 AND currency_code = X_currency_code;
32
33 return (TRUE);
34
35 EXCEPTION
36
37 when no_data_found then
38 return (FALSE);
39 when others then
40 po_message_s.sql_error('val_currency',X_progress,sqlcode);
41 raise;
42
43 END val_currency;
44
45 /*===========================================================================
46
47 PROCEDURE NAME: get_rate()
48
49 ===========================================================================*/
50
51 PROCEDURE get_rate(x_set_of_books_id IN NUMBER,
52 x_currency_code IN VARCHAR2,
53 x_rate_type IN VARCHAR2,
54 x_rate_date IN DATE,
55 x_inverse_rate_display_flag IN VARCHAR2,
56 x_rate IN OUT NOCOPY NUMBER,
57 x_display_rate IN OUT NOCOPY NUMBER) IS
58
59
60 x_progress VARCHAR2(3) := NULL;
61
62 BEGIN
63
64 x_progress := '010';
65
66 -- the check for X_inverse_rate_display_flag is done here
67 -- decode if X_inverse_rate_display_flag (in parameter from the client)
68 -- Y then x_ display_rate 1/conversion_rate else x_ display_rate
69 -- conversion rate
70
71 x_rate := gl_currency_api.get_rate(x_set_of_books_id,
72 x_currency_code,
73 x_rate_date,
74 x_rate_type);
75
76 IF (x_inverse_rate_display_flag = 'Y') THEN
77
78 x_display_rate := 1/x_rate;
79
80 ELSE
81
82 x_display_rate := x_rate;
83
84 END IF;
85
86 x_rate := ROUND(x_rate, 15);
87 x_display_rate := ROUND(x_display_rate, 15);
88
89 RETURN;
90
91
92 EXCEPTION
93
94 /* DEBUG: Once no_rate is defined by gl then put this exception
95 handling back in
96 WHEN NO_RATE THEN
97 RETURN;
98 WHEN OTHERS THEN
99 po_message_s.sql_error('get_rate', x_progress, sqlcode);
100 RAISE;
101 */
102 when gl_currency_api.no_rate then
103 -- dbms_output.put_line('No Rate');
104 return;
105 when gl_currency_api.invalid_currency then
106 -- dbms_output.put_line('Invalid Currency');
107 return;
108 WHEN OTHERS THEN
109 po_message_s.sql_error('get_rate', x_progress, sqlcode);
110 RAISE;
111
112 END get_rate;
113
114 /*===========================================================================
115
116 PROCEDURE NAME: test_get_rate()
117
118 ===========================================================================*/
119 PROCEDURE test_get_rate(x_set_of_books_id IN NUMBER,
120 x_currency_code IN VARCHAR2,
121 x_rate_type IN VARCHAR2,
122 x_rate_date IN DATE,
123 x_inverse_rate_display_flag IN VARCHAR2) IS
124
125
126
127 x_progress VARCHAR2(3) := NULL;
128 x_display_rate NUMBER;
129 x_rate NUMBER;
130 xx_inverse_rate_display_flag VARCHAR2(3) := '';
131
132
133 BEGIN
134
135
136 -- DBMS_OUTPUT.PUT_LINE('x_set_of_books_id = ' || x_set_of_books_id);
137 -- DBMS_OUTPUT.PUT_LINE('x_currency_code = ' || x_currency_code );
138 -- DBMS_OUTPUT.PUT_LINE('x_rate_type = ' || x_rate_type );
139 -- DBMS_OUTPUT.PUT_LINE('x_rate_date = ' || x_rate_date );
140 -- DBMS_OUTPUT.PUT_LINE('x_inverse_rate_display_flag =' ||
141 -- Bug 155260 x_inverse_rate_display_flag);
142
143 xx_inverse_rate_display_flag := x_inverse_rate_display_flag;
144
145 po_currency_sv.get_rate (x_set_of_books_id, x_currency_code,
146 x_rate_type, x_rate_date, x_inverse_rate_display_flag,
147 x_rate, x_display_rate);
148
149
150
151 -- DBMS_OUTPUT.PUT_LINE ('X_RATE = ' || x_rate);
152 -- DBMS_OUTPUT.PUT_LINE ('X_DISPLAY_RATE = ' || x_display_rate);
153
154
155 RETURN;
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 po_message_s.sql_error('get_rate', x_progress, sqlcode);
160 RAISE;
161
162
163 END test_get_rate;
164
165
166
167 /*===========================================================================
168
169 PROCEDURE NAME: get_rate_type_disp()
170
171 ===========================================================================*/
172 PROCEDURE get_rate_type_disp(x_rate_type IN VARCHAR2,
173 x_rate_type_disp IN OUT NOCOPY VARCHAR2) IS
174
175 x_progress VARCHAR2(3) := NULL;
176
177 BEGIN
178
179 x_progress := '010';
180
181 SELECT dct.user_conversion_type
182 INTO x_rate_type_disp
183 FROM gl_daily_conversion_types dct
184 WHERE dct.conversion_type = x_rate_type;
185
186
187
188 EXCEPTION
189 when no_data_found then
190 x_rate_type_disp := null;
191 WHEN OTHERS THEN
192 po_message_s.sql_error('get_rate_type_disp', x_progress, sqlcode);
193 RAISE;
194
195
196 END get_rate_type_disp;
197
198 /*===========================================================================
199
200 PROCEDURE NAME: validate_currency_info()
201
202 ===========================================================================*/
203
204 PROCEDURE validate_currency_info(
205 p_cur_record IN OUT NOCOPY RCV_SHIPMENT_HEADER_SV.CurRecType) IS
206
207
208 cursor C is SELECT start_date_active, end_date_active,
209 enabled_flag
210 FROM fnd_currencies
211 WHERE fnd_currencies.currency_code = p_cur_record.currency_code;
212
213 X_cur_record C%ROWTYPE;
214 X_sysdate DATE := sysdate;
215
216 BEGIN
217
218 OPEN C;
219 FETCH C INTO X_cur_record;
220
221 IF C%NOTFOUND THEN
222
223 p_cur_record.error_record.error_status := 'E';
224 p_cur_record.error_record.error_message := 'CURRENCY_INVALID';
225 RETURN;
226
227 ELSE
228
229 IF not (nvl(X_cur_record.enabled_flag,'N') = 'Y' and
230 (X_sysdate between
231 nvl(X_cur_record.start_date_active, X_sysdate - 1) and
232 nvl(X_cur_record.end_date_active, X_sysdate + 1))) THEN
233
234 p_cur_record.error_record.error_status := 'E';
235 p_cur_record.error_record.error_message := 'CURRENCY_DISABLED';
236 RETURN;
237
238 END IF;
239
240 FETCH C INTO X_cur_record;
241
242 IF C%NOTFOUND THEN
243
244 p_cur_record.error_record.error_status := 'S';
245 p_cur_record.error_record.error_message := NULL;
246 return;
247
248 ELSE
249
250 p_cur_record.error_record.error_status := 'E';
251 p_cur_record.error_record.error_message := 'TOOMANYROWS';
252 RETURN;
253
254 END IF;
255
256 END IF;
257
258 EXCEPTION
259 WHEN others THEN
260
261 p_cur_record.error_record.error_status := 'U';
262 p_cur_record.error_record.error_message := sqlerrm;
263
264 END validate_currency_info;
265
266 --<Shared Proc FPJ START>
267 -------------------------------------------------------------------------------
268 --Start of Comments
269 --Name: GET_FUNCTIONAL_CURRENCY_CODE
270 --Pre-reqs:
271 -- None
272 --Modifies:
273 -- None.
274 --Locks:
275 -- None.
276 --Function:
277 -- This procedure gets the Functional Currency assocaited with an Operating Unit
278 --Parameters:
279 --IN:
280 --p_org_id
281 -- The Operating Unit Id
282 --OUT:
283 --x_functional_currency_code
284 -- The functioanl currency assocaited with an Operating Unit
285 --Testing:
286 -- None
287 --End of Comments
288 ---------------------------------------------------------------------------
289 PROCEDURE get_functional_currency_code(
290 p_org_id IN NUMBER,
291 x_functional_currency_code OUT NOCOPY VARCHAR2) IS
292
293 l_progress VARCHAR2(3) := '001';
294 BEGIN
295
296 SELECT sob.currency_code
297 INTO x_functional_currency_code
298 FROM gl_sets_of_books sob, financials_system_params_all fsp
299 WHERE nvl(fsp.org_id, -99) = nvl(p_org_id, -99)
300 AND fsp.set_of_books_id = sob.set_of_books_id;
301
302 EXCEPTION
303 WHEN OTHERS THEN
304 po_message_s.sql_error('get_functional_currency_code', l_progress, sqlcode);
305 RAISE;
306 END get_functional_currency_code;
307
308 --
309 -- Function
310 -- rate_exists
311 --
312 -- Purpose
313 -- Returns 'Y' if there is a conversion rate between the two currencies
314 -- for a given conversion date and conversion type;
315 -- 'N' otherwise.
316 --
317 -- History
318 -- 04-SEP-03 M Bhargava Created
319 --
320 -- Arguments
321 -- p_from_currency From currency
322 -- p_to_currency To currency
323 -- p_conversion_date Conversion date
324 -- p_conversion_type Conversion type
325 --
326 FUNCTION rate_exists (
327 p_from_currency VARCHAR2,
328 p_to_currency VARCHAR2,
329 p_conversion_date DATE,
330 p_conversion_type VARCHAR2 DEFAULT NULL)
331 RETURN VARCHAR2 IS
332 is_rate_defined VARCHAR2(1);
333 l_api_name CONSTANT VARCHAR2(30) := 'rate_exists';
334 l_progress VARCHAR2(3) := '000';
335 BEGIN
336 is_rate_defined := gl_currency_api.rate_exists(
337 x_from_currency => p_from_currency,
338 x_to_currency => p_to_currency,
339 x_conversion_date => p_conversion_date,
340 x_conversion_type => p_conversion_type);
341 return is_rate_defined;
342 EXCEPTION
343 WHEN OTHERS THEN
344 po_message_s.sql_error('rate_exists', l_progress, sqlcode);
345 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
346 THEN
347 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name,
348 SUBSTRB (SQLERRM , 1 , 200) || ' at location ' || l_progress);
349 END IF;
350 RAISE;
351 END rate_exists;
352
353 -------------------------------------------------------------------------------
354 --Start of Comments
355 --Name: GET_RATE
356 --Pre-reqs:
357 -- None
358 --Modifies:
359 -- None.
360 --Locks:
361 -- None.
362 --Function:
363 -- Returns the rate between the two currencies for a given conversion
364 -- date and conversion type.
365 --Parameters:
366 --IN:
367 -- p_from_currency
368 -- The currency that needs to be converted (generally PO's currency)
369 -- p_to_currency
370 -- The currency in which to convert (generally POU's functional currency)
371 -- p_rate_type
372 -- The rate type to use
373 -- p_rate_date
374 -- The rate date to use
375 -- p_inverse_rate_display_flag
376 -- Flag indicating whether the displayed value of rate is inverse of actual value
377 --OUT:
378 -- x_rate
379 -- Rate obtained from the API. Will be NULL if none is obtained
380 -- x_display_rate
381 -- Display rate depending of the value of p_inverse_rate_display_flag. Will
382 -- be NULL if none is obtained
383 --Testing:
384 -- None
385 --End of Comments
386 ---------------------------------------------------------------------------
387 PROCEDURE get_rate(p_from_currency IN VARCHAR2,
388 p_to_currency IN VARCHAR2,
389 p_rate_type IN VARCHAR2,
390 p_rate_date IN DATE,
391 p_inverse_rate_display_flag IN VARCHAR2,
392 x_rate OUT NOCOPY NUMBER,
393 x_display_rate OUT NOCOPY NUMBER,
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_error_message_name OUT NOCOPY VARCHAR2)
396 IS
397
398 l_api_name CONSTANT VARCHAR2(30) := 'get_rate';
399 l_progress VARCHAR2(3) := '000';
400
401 BEGIN
402
403 l_progress := '010';
404
405 -- the check for X_inverse_rate_display_flag is done here
406 -- decode if X_inverse_rate_display_flag (in parameter from the client)
407 -- Y then x_ display_rate 1/conversion_rate else x_ display_rate
408 -- conversion rate
409
410 x_rate := gl_currency_api.get_rate(p_from_currency,
411 p_to_currency,
412 p_rate_date,
413 p_rate_type);
414 l_progress := '020';
415
416 IF (p_inverse_rate_display_flag = 'Y') THEN
417
418 x_display_rate := 1/x_rate;
419
420 ELSE
421
422 x_display_rate := x_rate;
423
424 END IF;
425 l_progress := '030';
426
427 x_rate := ROUND(x_rate, 15);
428 x_display_rate := ROUND(x_display_rate, 15);
429 x_return_status := FND_API.G_RET_STS_SUCCESS;
430
431 EXCEPTION
432 when gl_currency_api.no_rate then
433 -- dbms_output.put_line('No Rate');
434 x_return_status := FND_API.G_RET_STS_ERROR;
435 x_error_message_name := 'PO_CPO_NO_DEFAULT_RATE';
436 return;
437 when gl_currency_api.invalid_currency then
438 -- dbms_output.put_line('Invalid Currency');
439 x_return_status := FND_API.G_RET_STS_ERROR;
440 x_error_message_name := 'PO_INVALID_CURRENCY_CODE';
441 return;
442 WHEN OTHERS THEN
443 x_return_status := fnd_api.g_ret_sts_unexp_error;
444 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
445 THEN
446 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name,
447 SUBSTRB (SQLERRM , 1 , 200) || ' at location ' || l_progress);
448 END IF;
449 END get_rate;
450 --<Shared Proc FPJ END>
451
452 -- bug3294883 START
453
454 -------------------------------------------------------------------------------
455 --Start of Comments
456 --Name: get_currency_precision
457 --Pre-reqs:
458 -- None
459 --Modifies:
460 -- None.
461 --Locks:
462 -- None.
463 --Function:
464 -- Returns currency precision given the currency.
465 --Parameters:
466 --IN:
467 --p_currency
468 -- The currency code
469 --OUT:
470 --Testing:
471 -- None
472 --End of Comments
473 ---------------------------------------------------------------------------
474 FUNCTION get_currency_precision ( p_currency IN VARCHAR2 )
475 RETURN NUMBER IS
476
477 l_precision FND_CURRENCIES.precision%TYPE;
478 l_ext_precision FND_CURRENCIES.extended_precision%TYPE;
479 l_min_acct_unit FND_CURRENCIES.minimum_accountable_unit%TYPE;
480
481 BEGIN
482 FND_CURRENCY.get_info ( p_currency,
483 l_precision,
484 l_ext_precision,
485 l_min_acct_unit );
486
487 RETURN l_precision;
488 END get_currency_precision;
489
490 -- bug3294883 END
491
492 -- <HTMLAC START>
493 -------------------------------------------------------------------------------
494 --Start of Comments
495 --Name: get_cross_ou_rate
496 --Pre-reqs:
497 -- None
498 --Modifies:
499 -- None.
500 --Locks:
501 -- None.
502 --Function:
503 -- Returns the conversion rate between the functional currencies of
504 -- 2 operating units, using the To OU's default rate type and the current
505 -- date as the rate date.
506 --Parameters:
507 --IN:
508 -- p_from_ou_id
509 -- Operating unit to convert from
510 -- p_to_ou_id
511 -- Operating unit to convert to
512 --Returns:
513 -- Conversion rate; NULL if no conversion exists between the currencies
514 --End of Comments
515 ---------------------------------------------------------------------------
516 FUNCTION get_cross_ou_rate ( p_from_ou_id IN NUMBER, p_to_ou_id IN NUMBER )
517 RETURN NUMBER IS
518 l_api_name CONSTANT varchar2(30) := 'GET_CROSS_OU_RATE';
519
520 l_from_ou_currency GL_SETS_OF_BOOKS.currency_code%TYPE;
521 l_to_ou_currency GL_SETS_OF_BOOKS.currency_code%TYPE;
522 l_rate_type PO_SYSTEM_PARAMETERS_ALL.default_rate_type%TYPE;
523 l_rate NUMBER;
524 l_display_rate NUMBER;
525 l_return_status VARCHAR2(1);
526 l_message_name VARCHAR2(100);
527 l_progress VARCHAR2(3);
528 BEGIN
529 l_progress := '000';
530
531 -- If the operating units are the same, the rate is just 1.0.
532 IF (NVL(p_from_ou_id,-1) = NVL(p_to_ou_id,-1)) THEN
533 RETURN 1.0;
534 END IF;
535
536 -- Get the From OU's functional currency.
537 get_functional_currency_code (
538 p_org_id => p_from_ou_id,
539 x_functional_currency_code => l_from_ou_currency
540 );
541
542 -- Get the To OU's functional currency.
543 get_functional_currency_code (
544 p_org_id => p_to_ou_id,
545 x_functional_currency_code => l_to_ou_currency
546 );
547
548 -- If the currencies are the same, the rate is just 1.0.
549 IF (l_from_ou_currency = l_to_ou_currency) THEN
550 RETURN 1.0;
551 END IF;
552
553 l_progress := '010';
554
555 -- Get the default rate type from the To OU.
556 select default_rate_type
557 into l_rate_type
558 from po_system_parameters_all psp
559 where nvl(psp.org_id, -99) = nvl(p_to_ou_id, -99);
560
561 l_progress := '020';
562
563 -- Get the conversion rate.
564 get_rate (
565 p_from_currency => l_from_ou_currency,
566 p_to_currency => l_to_ou_currency,
567 p_rate_type => l_rate_type,
568 p_rate_date => SYSDATE,
569 p_inverse_rate_display_flag => 'N',
570 x_rate => l_rate,
571 x_display_rate => l_display_rate,
572 x_return_status => l_return_status,
573 x_error_message_name => l_message_name
574 );
575
576 l_progress := '030';
577
578 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
579 IF (g_debug_stmt) THEN
580 PO_DEBUG.debug_stmt (
581 p_log_head => g_pkg_name||'.'||l_api_name,
582 p_token => l_progress,
583 p_message => 'Currency conversion error: '
584 ||l_from_ou_currency||','||l_to_ou_currency||': '||
585 FND_MESSAGE.get_string('PO', l_message_name) );
586 END IF;
587 RETURN null;
588 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
589 IF (g_debug_unexp) THEN
590 PO_DEBUG.debug_unexp (
591 p_log_head => g_pkg_name||'.'||l_api_name,
592 p_progress => l_progress );
593 END IF;
594 RETURN null;
595 END IF;
596
597 RETURN l_rate;
598
599 EXCEPTION
600 WHEN OTHERS THEN
601 PO_DEBUG.handle_unexp_error (
602 p_pkg_name => g_pkg_name,
603 p_proc_name => l_api_name,
604 p_progress => l_progress );
605
606 RAISE;
607 END get_cross_ou_rate;
608 -- <HTMLAC END>
609 --Bug 9929991 When Function curruncey is differnt then PO curruncey we need to convert From Function to PO curruncey
610
611 FUNCTION get_converted_unit_price
612 (
613 p_list_unit_price IN NUMBER ,
614 p_rate IN NUMBER ,
615 p_currency_code VARCHAR2
616
617
618
619 ) RETURN NUMBER IS
620
621 x_precision NUMBER := null;
622 x_ext_precision NUMBER := null;
623 x_currency_code VARCHAR2(15) := NULL;
624 x_min_acct_unit NUMBER := null;
625 x_currency_unit_price NUMBER := null;
626 BEGIN
627
628
629
630
631 fnd_currency.get_info (p_currency_code,
632 x_precision,
633 x_ext_precision,
634 x_min_acct_unit);
635
636 x_currency_unit_price := round(p_list_unit_price / nvl(p_rate,1), x_ext_precision);
637
638 RETURN x_currency_unit_price;
639
640 END get_converted_unit_price;
641
642 --Bug 9929991 When Function curruncey is differnt then PO curruncey we need to convert From Function to PO curruncey
643
644 END po_currency_sv;