[Home] [Help]
PACKAGE BODY: APPS.PA_CURRENCY
Source
1 PACKAGE BODY PA_CURRENCY AS
2 /* $Header: PAXGCURB.pls 120.5 2008/03/17 13:58:29 rvelusam ship $ */
3
4 -- ==========================================================================
5 -- = PRIVATE PROCEDURE Get_Currency_Info
6 -- ==========================================================================
7
8 PROCEDURE Get_Currency_Info (l_curr_code out NOCOPY varchar2,
9 l_mau out NOCOPY number,
10 l_sp out NOCOPY number,
11 l_ep out NOCOPY number) IS
12 BEGIN
13
14 IF G_curr_code IS NULL THEN
15
16 If G_org_id is NULL then
17 SELECT FC.Currency_Code,
18 FC.Minimum_Accountable_Unit,
19 FC.Precision,
20 FC.Extended_Precision
21 INTO l_curr_code,
22 l_mau,
23 l_sp,
24 l_ep
25 FROM FND_CURRENCIES FC,
26 GL_SETS_OF_BOOKS GB,
27 PA_IMPLEMENTATIONS IMP
28 WHERE FC.Currency_Code =
29 DECODE(IMP.Set_Of_Books_ID, Null, Null,GB.CURRENCY_CODE)
30 AND GB.Set_Of_Books_ID = IMP.Set_Of_Books_ID;
31 Else
32 SELECT FC.Currency_Code,
33 FC.Minimum_Accountable_Unit,
34 FC.Precision,
35 FC.Extended_Precision
36 INTO l_curr_code,
37 l_mau,
38 l_sp,
39 l_ep
40 FROM FND_CURRENCIES FC,
41 GL_SETS_OF_BOOKS GB,
42 PA_IMPLEMENTATIONS_ALL IMP
43 WHERE FC.Currency_Code =
44 DECODE(IMP.Set_Of_Books_ID, Null, Null,GB.CURRENCY_CODE)
45 AND GB.Set_Of_Books_ID = IMP.Set_Of_Books_ID
46 AND IMP.org_id = G_org_id;
47 END IF;
48
49 ELSE
50
51 l_curr_code := G_curr_code;
52 l_mau := G_mau;
53 l_sp := G_sp;
54 l_ep := G_ep;
55
56 END IF;
57
58 EXCEPTION
59
60 WHEN OTHERS THEN
61 l_curr_code := Null;
62 l_mau := Null;
63 l_sp := Null;
64 l_ep := Null;
65 RAISE;
66
67 END Get_Currency_Info;
68
69 -- ===========================================================================
70 -- PROCEDURE Set_Currency_Info
71 -- ===========================================================================
72
73 PROCEDURE Set_Currency_Info IS
74 BEGIN
75
76 SELECT FC.Currency_Code,
77 FC.Minimum_Accountable_Unit,
78 FC.Precision,
79 FC.Extended_Precision
80 INTO G_curr_code,
81 G_mau,
82 G_sp,
83 G_ep
84 FROM FND_CURRENCIES FC,
85 GL_SETS_OF_BOOKS GB,
86 PA_IMPLEMENTATIONS IMP
87 WHERE FC.Currency_Code =
88 DECODE(IMP.Set_Of_Books_ID, Null, Null,GB.CURRENCY_CODE)
89 AND GB.Set_Of_Books_ID = IMP.Set_Of_Books_ID;
90
91 EXCEPTION
92 WHEN OTHERS THEN RAISE;
93
94 END Set_Currency_Info;
95
96 -- ==========================================================================
97 -- = PRIVATE PROCEDURE Get_Trans_Currency_Info
98 -- ==========================================================================
99 PROCEDURE Get_Trans_Currency_Info (l_curr_code IN varchar2,
100 l_mau out NOCOPY number,
101 l_sp out NOCOPY number,
102 l_ep out NOCOPY number) IS
103 BEGIN
104
105 /* Modified for the bug 4292770 (Basebug# 3848201) */
106 /* Bug#4428414 */
107 IF (nvl(G_curr_code,'*') <> l_curr_code) THEN
108 SELECT FC.Minimum_Accountable_Unit,
109 FC.Precision,
110 FC.Extended_Precision
111 INTO l_mau,
112 l_sp,
113 l_ep
114 FROM FND_CURRENCIES FC
115 WHERE FC.Currency_Code = l_curr_code;
116 ELSIF G_curr_code IS NOT NULL THEN
117 l_mau := G_mau;
118 l_sp := G_sp;
119 l_ep := G_ep;
120 END IF;
121
122
123 Exception
124 When Others then
125 l_mau := Null;
126 l_sp := Null;
127 l_ep := Null;
128 Raise;
129
130 END Get_Trans_Currency_Info;
131 -- ==========================================================================
132 -- = FUNCTION get_currency_code
133 -- ==========================================================================
134
135 FUNCTION get_currency_code RETURN VARCHAR2
136 IS
137 l_curr_code fnd_currencies.currency_code%TYPE;
138 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
139 l_sp fnd_currencies.precision%TYPE;
140 l_ep fnd_currencies.extended_precision%TYPE;
141 BEGIN
142 Get_Currency_Info(l_curr_code, l_mau, l_sp, l_ep);
143 return(l_curr_code);
144 END get_currency_code;
145
146 -- ==========================================================================
147 -- = FUNCTION round_currency_amt
148 -- ==========================================================================
149
150 FUNCTION round_currency_amt ( X_amount IN NUMBER ) RETURN NUMBER
151 IS
152 l_curr_code fnd_currencies.currency_code%TYPE;
153 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
154 l_sp fnd_currencies.precision%TYPE;
155 l_ep fnd_currencies.extended_precision%TYPE;
156 BEGIN
157
158 Get_Currency_Info(l_curr_code, l_mau, l_sp, l_ep);
159
160 IF l_mau IS NOT NULL THEN
161
162 IF l_mau < 0.00001 THEN
163 RETURN( round(X_Amount, 5));
164 ELSE
165 RETURN( round(X_Amount/l_mau) * l_mau );
166 END IF;
167
168 ELSIF l_sp IS NOT NULL THEN
169
170 IF l_sp > 5 THEN
171 RETURN( round(X_Amount, 5));
172 ELSE
173 RETURN( round(X_Amount, l_sp));
174 END IF;
175
176 ELSE
177
178 RETURN( round(X_Amount, 5));
179
180 END IF;
181
182 END round_currency_amt;
183 -- ==========================================================================
184 -- = FUNCTION round_trans_currency_amt
185 -- ==========================================================================
186
187 FUNCTION round_trans_currency_amt ( X_amount IN NUMBER,
188 X_Curr_Code IN VARCHAR2 ) RETURN NUMBER
189 IS
190 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
191 l_sp fnd_currencies.precision%TYPE;
192 l_ep fnd_currencies.extended_precision%TYPE;
193 BEGIN
194
195 Get_Trans_Currency_Info(X_curr_code, l_mau, l_sp, l_ep);
196
197 IF l_mau IS NOT NULL THEN
198
199 IF l_mau < 0.00001 THEN
200 RETURN( round(X_Amount, 5));
201 ELSE
202 RETURN( round(X_Amount/l_mau) * l_mau );
203 END IF;
204
205 ELSIF l_sp IS NOT NULL THEN
206
207 IF l_sp > 5 THEN
208 RETURN( round(X_Amount, 5));
209 ELSE
210 RETURN( round(X_Amount, l_sp));
211 END IF;
212
213 ELSE
214
215 RETURN( round(X_Amount, 5));
216
217 END IF;
218
219 END round_trans_currency_amt;
220
221
222 -- ==========================================================================
223 -- = FUNCTION currency_fmt_mask
224 -- ==========================================================================
225
226 FUNCTION currency_fmt_mask(X_length IN NUMBER) RETURN VARCHAR2
227 IS
228 l_curr_code fnd_currencies.currency_code%TYPE;
229 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
230 l_sp fnd_currencies.precision%TYPE;
231 l_ep fnd_currencies.extended_precision%TYPE;
232
233 fmt_mask VARCHAR2(80);
234 len number;
235 BEGIN
236
237 -- Maximum Length Allowed is 80 characters
238
239 IF X_length > 80 THEN
240 return(NULL);
241 END IF;
242
243 Get_Currency_Info(l_curr_code, l_mau, l_sp, l_ep);
244
245 len := 0;
246 fmt_mask := NULL;
247
248 IF l_sp > 0 THEN
249 /**Bug#1142122
250 **The mask dot (.) was replaced with 'D' to handle grouping and decimal delimiters accordingly.
251 **/
252 fmt_mask := 'D';
253 len := 1;
254
255 FOR counter in 1..l_sp LOOP
256 fmt_mask := fmt_mask || '9';
257 len := len + 1;
258 END LOOP;
259
260 -- Length of the field should at least be equal to std precision
261 IF len > X_Length THEN
262 return (NULL);
263 END IF;
264
265 ELSE
266 fmt_mask := '9';
267 END IF;
268
269 -- X-length - 1 : for the minus sign, in case of negative values
270 return(lpad(fmt_mask, X_length - 1, '9'));
271
272
273 END currency_fmt_mask;
274
275 -- ==========================================================================
276 -- = FUNCTION rpt_currency_fmt_mask
277 -- ==========================================================================
278
279 FUNCTION rpt_currency_fmt_mask(X_org_id IN NUMBER, X_length IN NUMBER) RETURN VARCHAR2
280 IS
281 l_curr_code fnd_currencies.currency_code%TYPE;
282 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
283 l_sp fnd_currencies.precision%TYPE;
284 l_ep fnd_currencies.extended_precision%TYPE;
285
286 fmt_mask VARCHAR2(80);
287 len number;
288 BEGIN
289
290 -- Maximum Length Allowed is 80 characters
291
292 IF X_length > 80 THEN
293 return(NULL);
294 END IF;
295
296 SELECT FC.Currency_Code,
297 FC.Minimum_Accountable_Unit,
298 FC.Precision,
299 FC.Extended_Precision
300 INTO l_curr_code,
301 l_mau,
302 l_sp,
303 l_ep
304 FROM FND_CURRENCIES FC,
305 GL_SETS_OF_BOOKS GB,
306 PA_IMPLEMENTATIONS_ALL IMP
307 WHERE FC.Currency_Code =
308 DECODE(IMP.Set_Of_Books_ID, Null, Null,GB.CURRENCY_CODE)
309 AND GB.Set_Of_Books_ID = IMP.Set_Of_Books_ID
310 --AND nvl(IMP.Org_ID,-99) = nvl(X_Org_ID, -99);
311 AND IMP.org_id = nvl(X_Org_ID, -99);
312
313 len := 0;
314 fmt_mask := NULL;
315
316 IF l_sp > 0 THEN
317 fmt_mask := '.';
318 len := 1;
319
320 FOR counter in 1..l_sp LOOP
321 fmt_mask := fmt_mask || '9';
322 len := len + 1;
323 END LOOP;
324
325 -- Length of the field should at least be equal to std precision
326 IF len > X_Length THEN
327 return (NULL);
328 END IF;
329
330 ELSE
331 fmt_mask := '9';
332 END IF;
333
334 -- X-length - 1 : for the minus sign, in case of negative values
335 return(lpad(fmt_mask, X_length - 1, '9'));
336
337
338 END rpt_currency_fmt_mask;
339
340
341 FUNCTION trans_currency_fmt_mask(X_Curr_Code IN VARCHAR2,
342 X_length IN NUMBER) RETURN VARCHAR2
343 IS
344 l_curr_code fnd_currencies.currency_code%TYPE;
345 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
346 l_sp fnd_currencies.precision%TYPE;
347 l_ep fnd_currencies.extended_precision%TYPE;
348
349 fmt_mask VARCHAR2(80);
350 len number;
351 BEGIN
352
353 -- Maximum Length Allowed is 80 characters
354
355 IF X_length > 80 THEN
356 return(NULL);
357 END IF;
358
359 Get_Trans_Currency_Info(X_Curr_Code, l_mau, l_sp, l_ep);
360
361 len := 0;
362 fmt_mask := NULL;
363
364 IF l_sp > 0 THEN
365 fmt_mask := '.';
366 len := 1;
367
368 FOR counter in 1..l_sp LOOP
369 fmt_mask := fmt_mask || '9';
370 len := len + 1;
371 END LOOP;
372
373 -- Length of the field should at least be equal to std precision
374 IF len > X_Length THEN
375 return (NULL);
376 END IF;
377
378 ELSE
379 fmt_mask := '9';
380 END IF;
381
382 -- X-length - 1 : for the minus sign, in case of negative values
383 return(lpad(fmt_mask, X_length - 1, '9'));
384
385
386 END trans_currency_fmt_mask;
387
388 FUNCTION get_mau ( X_Curr_Code IN VARCHAR2 ) RETURN VARCHAR2
389 IS
390 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
391 l_sp fnd_currencies.precision%TYPE;
392 l_ep fnd_currencies.extended_precision%TYPE;
393 l_div NUMBER;
394 BEGIN
395
396 IF (G_proj_curr_code IS NULL) OR
397 (G_proj_curr_code <> X_curr_code) THEN
398
399 G_proj_curr_code := X_curr_code;
400 Get_Trans_Currency_Info(X_curr_code, l_mau, l_sp, l_ep);
401
402 IF l_mau IS NOT NULL THEN
403 G_mau_chr := to_char(l_mau);
404 RETURN( G_mau_chr );
405 ELSIF l_sp IS NOT NULL THEN
406 l_div := 1;
407 FOR counter in 1..l_sp LOOP
408 l_div := l_div* 10;
412 ELSE
409 END LOOP;
410 G_mau_chr := to_char(1/l_div);
411 return(G_mau_chr);
413 G_mau_chr := '0.01';
414 return(G_mau_chr);
415 END IF;
416 ELSE
417 return(G_mau_chr);
418 END IF;
419
420 END get_mau;
421
422
423 /*
424 --Pa-K Changes: Transaction Import Enhancements
425 --Added for better performance as the existing Round_Currency_Amt that calls
426 --Get_Currency_Code does not use caching. Changing the existing functions
427 --will result in removing the PRAGMA constraint that has a lot of impact on
428 --other functions.
429 --Duplicated 4 functions, new ones are:
430 --Get_Currency_Info1, round_currency_amt1, Get_Trans_Currency_Info1 and
431 --round_currency_amt1
432 --These functions will be removed when the division wide the PRAGMA RESTRICT
433 --constraint will be removed from all functions.
434 --Till then any changes to the above functions will have to be made here also.
435 */
436
437 PROCEDURE Get_Currency_Info1 (l_curr_code out nocopy varchar2,
438 l_mau out nocopy number,
439 l_sp out nocopy number,
440 l_ep out nocopy number) IS
441 BEGIN
442
443 --Bug 3112441
444 --IF G_CurrCode1 <> l_curr_code THEN
445 IF G_CurrCode1 IS NULL THEN
446
447 SELECT FC.Currency_Code,
448 FC.Minimum_Accountable_Unit,
449 FC.Precision,
450 FC.Extended_Precision
451 INTO l_curr_code,
452 l_mau,
453 l_sp,
454 l_ep
455 FROM FND_CURRENCIES FC,
456 GL_SETS_OF_BOOKS GB,
457 PA_IMPLEMENTATIONS IMP
458 WHERE FC.Currency_Code =
459 DECODE(IMP.Set_Of_Books_ID, Null, Null,GB.CURRENCY_CODE)
460 AND GB.Set_Of_Books_ID = IMP.Set_Of_Books_ID;
461
462 G_CurrCode1 := l_curr_code;
463 G_Mau1 := l_mau;
464 G_Sp1 := l_sp;
465 G_Ep1 := l_ep;
466
467 ELSE
468
469 l_curr_code := G_CurrCode1;
470 l_mau := G_mau1;
471 l_sp := G_sp1;
472 l_ep := G_ep1;
473
474 END IF;
475
476 EXCEPTION
477
478 WHEN OTHERS THEN
479 l_curr_code := Null;
480 l_mau := Null;
481 l_sp := Null;
482 l_ep := Null;
483 RAISE;
484
485 END Get_Currency_Info1;
486
487 FUNCTION round_currency_amt1 ( X_amount IN NUMBER ) RETURN NUMBER
488 IS
489 l_curr_code fnd_currencies.currency_code%TYPE;
490 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
491 l_sp fnd_currencies.precision%TYPE;
492 l_ep fnd_currencies.extended_precision%TYPE;
493 BEGIN
494
495 Get_Currency_Info1(l_curr_code, l_mau, l_sp, l_ep);
496
497 IF l_mau IS NOT NULL THEN
498
499 IF l_mau < 0.00001 THEN
500 RETURN( round(X_Amount, 5));
501 ELSE
502 RETURN( round(X_Amount/l_mau) * l_mau );
503 END IF;
504
505 ELSIF l_sp IS NOT NULL THEN
506
507 IF l_sp > 5 THEN
508 RETURN( round(X_Amount, 5));
509 ELSE
510 RETURN( round(X_Amount, l_sp));
511 END IF;
512
513 ELSE
514
515 RETURN( round(X_Amount, 5));
516
517 END IF;
518
519 END round_currency_amt1;
520
521 PROCEDURE Get_Trans_Currency_Info1 (l_curr_code IN varchar2,
522 l_mau out NOCOPY number,
523 l_sp out NOCOPY number,
524 l_ep out NOCOPY number) IS
525 BEGIN
526
527 If G_TransCurrCode = l_curr_code Then
528
529 l_mau := G_TransMau;
530 l_sp := G_TransSp;
531 l_ep := G_TransEp;
532
533 Else
534
535 SELECT FC.Minimum_Accountable_Unit,
536 FC.Precision,
537 FC.Extended_Precision
538 INTO l_mau,
539 l_sp,
540 l_ep
541 FROM FND_CURRENCIES FC
542 WHERE FC.Currency_Code = l_curr_code;
543
544 G_TransCurrCode := l_curr_code;
545 G_TransMau := l_mau;
546 G_TransSp := l_sp;
547 G_TransEp := l_ep;
548
549 End If;
550
551 Exception
552 When Others Then
553 l_mau := Null;
554 l_sp := Null;
555 l_ep := Null;
556 Raise;
557
558 END Get_Trans_Currency_Info1;
559
560 FUNCTION round_trans_currency_amt1 ( X_amount IN NUMBER,
561 X_Curr_Code IN VARCHAR2 ) RETURN NUMBER
562 IS
563 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
564 l_sp fnd_currencies.precision%TYPE;
565 l_ep fnd_currencies.extended_precision%TYPE;
566 BEGIN
567
571
568 Get_Trans_Currency_Info1(X_curr_code, l_mau, l_sp, l_ep);
569
570 IF l_mau IS NOT NULL THEN
572 IF l_mau < 0.00001 THEN
573 RETURN( round(X_Amount, 5));
574 ELSE
575 RETURN( round(X_Amount/l_mau) * l_mau );
576 END IF;
577
578 ELSIF l_sp IS NOT NULL THEN
579
580 IF l_sp > 5 THEN
581 RETURN( round(X_Amount, 5));
582 ELSE
583 RETURN( round(X_Amount, l_sp));
584 END IF;
585
586 ELSE
587
588 RETURN( round(X_Amount, 5));
589
590 END IF;
591
592 END round_trans_currency_amt1;
593
594 FUNCTION round_currency_amt_blk ( p_amount_tab PA_PLSQL_DATATYPES.NumTabTyp
595 ,p_currency_tab PA_PLSQL_DATATYPES.Char30TabTyp
596 )
597 RETURN PA_PLSQL_DATATYPES.NumTabTyp
598 IS
599 x_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
600 l_prev_curr_code VARCHAR2(30);
601
602 l_mau fnd_currencies.minimum_accountable_unit%TYPE;
603 l_sp fnd_currencies.precision%TYPE;
604 l_ep fnd_currencies.extended_precision%TYPE;
605 BEGIN
606 FOR i IN p_amount_tab.FIRST .. p_amount_tab.LAST
607 LOOP
608 IF ( l_prev_curr_code IS NULL OR l_prev_curr_code <> p_currency_tab(i) )
609 THEN
610 PA_CURRENCY.GET_TRANS_CURRENCY_INFO1(p_currency_tab(i), l_mau, l_sp, l_ep);
611 l_prev_curr_code := p_currency_tab(i);
612 END IF;
613
614 IF ( l_mau IS NOT NULL )
615 THEN
616 IF l_mau < 0.00001
620 x_amount_tab(i):= ROUND(p_amount_tab(i)/l_mau) * l_mau;
617 THEN
618 x_amount_tab(i):= ROUND(p_amount_tab(i), 5);
619 ELSE
621 END IF;
622 ELSIF ( l_sp IS NOT NULL )
623 THEN
624 IF ( l_sp > 5 )
625 THEN
626 x_amount_tab(i):= ROUND(p_amount_tab(i), 5);
627 ELSE
628 x_amount_tab(i):= ROUND(p_amount_tab(i), l_sp);
629 END IF;
630 ELSE
631 x_amount_tab(i):= ROUND(p_amount_tab(i), 5);
632 END IF;
633 END LOOP;
634 RETURN x_amount_tab;
635 END round_currency_amt_blk;
636
637 FUNCTION round_currency_amt_nested_blk ( p_amount_tbl SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
638 ,p_currency_tbl SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type()
639 ) RETURN SYSTEM.pa_num_tbl_type
640 IS
641 l_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
642 l_output_amount_tab PA_PLSQL_DATATYPES.NumTabTyp;
643 l_currency_tab PA_PLSQL_DATATYPES.Char30TabTyp;
644
645 l_output_amount_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
646 BEGIN
647 FOR i IN p_amount_tbl.FIRST .. p_amount_tbl.LAST
648 LOOP
649 l_amount_tab(i) := p_amount_tbl(i);
650 l_currency_tab(i) := p_currency_tbl(i);
651 END LOOP;
652 l_output_amount_tab := PA_CURRENCY.round_currency_amt_blk( p_amount_tab => l_amount_tab
653 ,p_currency_tab => l_currency_tab
654 );
655 FOR i IN l_output_amount_tab.FIRST .. l_output_amount_tab.LAST
656 LOOP
657 l_output_amount_tbl.EXTEND;
658 l_output_amount_tbl(i) := l_output_amount_tab(i);
659 END LOOP;
660 RETURN l_output_amount_tbl;
661 END round_currency_amt_nested_blk;
662 END pa_currency;