1 PACKAGE BODY JMF_SHIKYU_RPT_UTIL AS
2 --$Header: JMFUSKRB.pls 120.7 2006/01/17 15:10:31 vchu noship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME : JMFUSKRB.pls |
10 --| |
11 --| DESCRIPTION: Body file of the utility package for the |
12 --| Charge Based SHIKYU reports. |
13 --| |
14 --| FUNCTION/PROCEDURE: get_item_number |
15 --| get_item_primary_uom_code |
16 --| get_item_primary_quantity |
17 --| po_uom_convert_p |
18 --| get_min2 |
19 --| get_min3 |
20 --| rate_exists |
21 --| get_rate |
22 --| convert_amount |
23 --| |
24 --| HISTORY: |
25 --| 27-APR-2005 shu Created. |
26 --| 21-NOV-2005 shu add code in po_uom_convert_p as it is use the |
27 --| the 3-character UOM_CODE,not 25-character |
28 --| 29-NOV-2005 shu added uom_to_code function . |
29 --| 29-NOV-2005 shu modified convert_amount function, using |
30 --| GL_CURRENCY_API.convert_amount_sql that with |
31 --| exception handle. |
32 --| 16-DEC-2005 shu modified debug_output to save information into|
33 --| FND_LOG_MESSAGE table using fnd_log.string |
34 --+===========================================================================+
35
36 --=============================================
37 -- GLOBAL VARIABLES
38 --=============================================
39
40 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
41
42 --========================================================================
43 -- PROCEDURE : get_item_number PUBLIC
44 -- PARAMETERS: p_organization_id the organization id
45 -- : p_inventory_item_id the item id
46 -- : x_item_number the return item number
47 -- COMMENT : for getting the item flexfield number
48 -- PRE-COND :
49 -- EXCEPTIONS:
50 --========================================================================
51 PROCEDURE get_item_number
52 ( p_organization_id IN NUMBER
53 , p_inventory_item_id IN NUMBER
54 , x_item_number OUT NOCOPY VARCHAR2
55 )
56 IS
57 --l_val BOOLEAN;
58 l_nseg NUMBER;
59 l_seglist fnd_flex_key_api.segment_list;
60 l_segs1 fnd_flex_ext.segmentarray;
61 l_segs2 fnd_flex_ext.segmentarray;
62 l_fftype fnd_flex_key_api.flexfield_type;
63 l_ffstru fnd_flex_key_api.structure_type;
64 l_segment_type fnd_flex_key_api.segment_type;
65 l_item_number VARCHAR2(32000);
66 l_delim VARCHAR2(1);
67 l_index NUMBER;
68 BEGIN
69
70 fnd_flex_key_api.set_session_mode('customer_data');
71 -- find flex field type
72 l_fftype := fnd_flex_key_api.find_flexfield('INV'
73 ,'MSTK');
74 -- find flex structure type
75 l_ffstru := fnd_flex_key_api.find_structure(l_fftype
76 ,101);
77 -- find segment list for the key flex field
78 fnd_flex_key_api.get_segments(l_fftype
79 ,l_ffstru
80 ,TRUE
81 ,l_nseg
82 ,l_seglist);
83 -- get the corresponding clolumn for all segments
84 FOR l_loop IN 1 .. l_nseg
85 LOOP
86 l_segment_type := fnd_flex_key_api.find_segment(l_fftype
87 ,l_ffstru
88 ,l_seglist(l_loop));
89 l_segs2(l_loop) := l_segment_type.column_name;
90 END LOOP;
91
92 -- get all segments from the item table
93 SELECT segment1
94 ,segment2
95 ,segment3
96 ,segment4
97 ,segment5
98 ,segment6
99 ,segment7
100 ,segment8
101 ,segment9
102 ,segment10
103 ,segment11
104 ,segment12
105 ,segment13
106 ,segment14
107 ,segment15
108 ,segment16
109 ,segment17
110 ,segment18
111 ,segment19
112 ,segment20
113 INTO l_segs1(1)
114 ,l_segs1(2)
115 ,l_segs1(3)
116 ,l_segs1(4)
117 ,l_segs1(5)
118 ,l_segs1(6)
119 ,l_segs1(7)
120 ,l_segs1(8)
121 ,l_segs1(9)
122 ,l_segs1(10)
123 ,l_segs1(11)
124 ,l_segs1(12)
125 ,l_segs1(13)
126 ,l_segs1(14)
127 ,l_segs1(15)
128 ,l_segs1(16)
129 ,l_segs1(17)
130 ,l_segs1(18)
131 ,l_segs1(19)
132 ,l_segs1(20)
133 FROM mtl_system_items
134 WHERE organization_id = p_organization_id
135 AND inventory_item_id = p_inventory_item_id;
136
137 -- get delimiter for segment concatenation
138 l_delim := fnd_flex_ext.get_delimiter('INV'
139 ,'MSTK'
140 ,101);
141
142 -- concatenate segments based on the order defined by the flex
143 -- field structure
144 FOR l_loop IN 1 .. l_nseg
145 LOOP
146 l_index := To_number(Substr(l_segs2(l_loop)
147 ,8
148 ,1));
149 IF l_loop = 1
150 THEN
151 l_item_number := l_segs1(l_index);
152 ELSE
153 l_item_number := l_item_number || l_delim || l_segs1(l_index);
154 END IF;
155 END LOOP;
156
157 x_item_number := l_item_number;
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 x_item_number := NULL;
162
163 END get_item_number;
164
165 --========================================================================
166 -- FUNCTION : get_item_number PUBLIC
167 -- PARAMETERS: p_organization_id the organization id
168 -- : p_inventory_item_id the item id
169 -- RETURN : will return the item number
170 -- COMMENT : for getting the item flexfield number
171 -- PRE-COND :
172 -- EXCEPTIONS:
173 --========================================================================
174 FUNCTION get_item_number
175 ( p_organization_id IN NUMBER
176 , p_inventory_item_id IN NUMBER
177 )
178 RETURN VARCHAR2
179 IS
180 --l_val BOOLEAN;
181 l_nseg NUMBER;
182 l_seglist fnd_flex_key_api.segment_list;
183 l_segs1 fnd_flex_ext.segmentarray;
184 l_segs2 fnd_flex_ext.segmentarray;
185 l_fftype fnd_flex_key_api.flexfield_type;
186 l_ffstru fnd_flex_key_api.structure_type;
187 l_segment_type fnd_flex_key_api.segment_type;
188 l_item_number VARCHAR2(32000);
189 l_delim VARCHAR2(1);
190 l_index NUMBER;
191 BEGIN
192 fnd_flex_key_api.set_session_mode('customer_data');
193 -- find flex field type
194 l_fftype := fnd_flex_key_api.find_flexfield('INV'
195 ,'MSTK');
196 -- find flex structure type
197 l_ffstru := fnd_flex_key_api.find_structure(l_fftype
198 ,101);
199 -- find segment list for the key flex field
200 fnd_flex_key_api.get_segments(l_fftype
201 ,l_ffstru
202 ,TRUE
203 ,l_nseg
204 ,l_seglist);
205 -- get the corresponding clolumn for all segments
206 FOR l_loop IN 1 .. l_nseg
207 LOOP
208 l_segment_type := fnd_flex_key_api.find_segment(l_fftype
209 ,l_ffstru
210 ,l_seglist(l_loop));
211 l_segs2(l_loop) := l_segment_type.column_name;
212 END LOOP;
213
214 -- get all segments from the item table
215 SELECT segment1
216 ,segment2
217 ,segment3
218 ,segment4
219 ,segment5
220 ,segment6
221 ,segment7
222 ,segment8
223 ,segment9
224 ,segment10
225 ,segment11
226 ,segment12
227 ,segment13
228 ,segment14
229 ,segment15
230 ,segment16
231 ,segment17
232 ,segment18
233 ,segment19
234 ,segment20
235 INTO l_segs1(1)
236 ,l_segs1(2)
237 ,l_segs1(3)
238 ,l_segs1(4)
239 ,l_segs1(5)
240 ,l_segs1(6)
241 ,l_segs1(7)
242 ,l_segs1(8)
243 ,l_segs1(9)
244 ,l_segs1(10)
245 ,l_segs1(11)
246 ,l_segs1(12)
247 ,l_segs1(13)
248 ,l_segs1(14)
249 ,l_segs1(15)
250 ,l_segs1(16)
251 ,l_segs1(17)
252 ,l_segs1(18)
253 ,l_segs1(19)
254 ,l_segs1(20)
255 FROM mtl_system_items
256 WHERE organization_id = p_organization_id
257 AND inventory_item_id = p_inventory_item_id;
258
259 -- get delimiter for segment concatenation
260 l_delim := fnd_flex_ext.get_delimiter('INV'
261 ,'MSTK'
262 ,101);
263
264 -- concatenate segments based on the order defined by the flex
265 -- field structure
266 FOR l_loop IN 1 .. l_nseg
267 LOOP
268 l_index := To_number(Substr(l_segs2(l_loop)
269 ,8
270 ,1));
271 IF l_loop = 1
272 THEN
273 l_item_number := l_segs1(l_index);
274 ELSE
275 l_item_number := l_item_number || l_delim || l_segs1(l_index);
276 END IF;
277 END LOOP;
278
279 RETURN l_item_number;
280
281 EXCEPTION
282 WHEN OTHERS THEN
283 RETURN NULL;
284
285 END get_item_number;
286
287 --========================================================================
288 -- FUNCTION : uom_to_code PUBLIC
289 -- PARAMETERS: p_unit_of_measure the 25-character unit of measure
290 -- RETURN : will return the 3-character uom code in jmf_shikyu_% tables
291 -- COMMENT : getting the UOM code
292 -- PRE-COND :
293 -- EXCEPTIONS:
294 --========================================================================
295 FUNCTION uom_to_code(p_unit_of_measure IN VARCHAR2) RETURN VARCHAR2 IS
296 l_uom_code mtl_units_of_measure_tl.uom_code%TYPE; -- or mtl_units_of_measure_tl.uom_code???
297 BEGIN
298 --get the uom_code
299 BEGIN
300 -- get l_from_unit_name
301 SELECT UOM_CODE
305 AND UNIT_OF_MEASURE = p_unit_of_measure;
302 INTO l_uom_code
303 FROM MTL_UNITS_OF_MEASURE_TL
304 WHERE LANGUAGE = USERENV('LANG')
306
307
308 EXCEPTION
309 WHEN no_data_found THEN
310 -- get l_from_unit_name
311 SELECT UOM_CODE
312 INTO l_uom_code
313 FROM MTL_UNITS_OF_MEASURE_TL
314 WHERE LANGUAGE = 'US'
315 AND UNIT_OF_MEASURE = p_unit_of_measure;
316 END;
317
318 RETURN l_uom_code;
319
320 END uom_to_code;
321
322 --========================================================================
323 -- FUNCTION : get_item_primary_uom_code PUBLIC
324 -- PARAMETERS: p_org_id the organization id
325 -- : p_item_id the item id
326 -- RETURN : will return the primary uom code
327 -- COMMENT : getting the primary UOM code
328 -- PRE-COND :
329 -- EXCEPTIONS:
330 --========================================================================
331 FUNCTION get_item_primary_uom_code
332 ( p_org_id IN NUMBER
333 , p_item_id IN NUMBER
334 )
335 RETURN VARCHAR2
336 IS
337 BEGIN
338
339 RETURN get_item_primary_uom_code(p_org_id => p_org_id
340 ,p_item_id => p_item_id
341 ,p_current_uom_code => NULL);
342
343 END get_item_primary_uom_code;
344
345 --========================================================================
346 -- FUNCTION : get_item_primary_uom_code PUBLIC
347 -- PARAMETERS: p_org_id the organization id
348 -- : p_item_id the item id
349 -- : p_current_uom_Code current uom_code
350 -- RETURN : will return the primary uom code
351 -- COMMENT : getting the primary UOM code
352 -- PRE-COND :
353 -- EXCEPTIONS:
354 --========================================================================
355 FUNCTION get_item_primary_uom_code
356 ( p_org_id IN NUMBER
357 , p_item_id IN NUMBER
358 , p_current_uom_code IN VARCHAR2
359 )
360 RETURN VARCHAR2
361 IS
362 l_current_uom mtl_units_of_measure_tl.unit_of_measure%TYPE; -- or mtl_units_of_measure_tl.uom_code???
363 l_primary_uom mtl_units_of_measure_tl.unit_of_measure%TYPE; -- or mtl_units_of_measure_tl.uom_code???
364 l_primary_uom_code mtl_units_of_measure_tl.uom_code%TYPE; -- or mtl_units_of_measure_tl.uom_code???
365 BEGIN
366
367 IF p_item_id IS NULL
368 THEN
369 --get the current unit of measure from the given uom_code;
370 SELECT unit_of_measure
371 INTO l_current_uom
372 FROM mtl_units_of_measure_vl
373 WHERE uom_code = p_current_uom_code;
374 END IF;
375
376 --get the primary UOM
377 l_primary_uom := po_uom_s.get_primary_uom(p_item_id
378 ,p_org_id
379 ,l_current_uom);
380
381 --get the primary uom_code
382 SELECT uom_code
383 INTO l_primary_uom_code
384 FROM mtl_units_of_measure_vl
385 WHERE unit_of_measure = l_primary_uom;
386
387 RETURN l_primary_uom_code;
388
389 END get_item_primary_uom_code;
390
391 --========================================================================
392 -- FUNCTION : get_item_primary_quantity PUBLIC
393 -- PARAMETERS: p_org_id the organization id
394 -- : p_item_id the item id
395 -- : p_current_uom_Code current uom_code
396 -- : p_current_qty current item quantity
397 -- RETURN : will return the quantity for the item using the primary uom
398 -- COMMENT : getting the item quantity using primary UOM
399 -- PRE-COND :
400 -- EXCEPTIONS:
401 --========================================================================
402 FUNCTION get_item_primary_quantity
403 ( p_org_id IN NUMBER
404 , p_item_id IN NUMBER
405 , p_current_uom_code IN VARCHAR2
406 , p_current_qty IN NUMBER
407 )
408 RETURN NUMBER
409 IS
410 l_current_uom mtl_units_of_measure_tl.unit_of_measure%TYPE; -- or mtl_units_of_measure_tl.uom_code???
411 l_primary_uom mtl_units_of_measure_tl.unit_of_measure%TYPE; -- or mtl_units_of_measure_tl.uom_code???
412 l_primary_quantity NUMBER;
413 BEGIN
414 --get the current unit of measure from the given uom_code;
415 SELECT unit_of_measure
416 INTO l_current_uom
417 FROM mtl_units_of_measure_vl
418 WHERE uom_code = p_current_uom_code;
419
420 --get the primary UOM
421 l_primary_uom := po_uom_s.get_primary_uom(p_item_id
422 ,p_org_id
423 ,l_current_uom);
424
425 /*l_primary_uom := get_item_primary_uom_code
426 ( p_org_id => p_org_id
427 , p_item_id => p_item_id
428 , p_current_uom_code => p_current_uom_code
429 ) ;*/
430
431 --get the primary quantity
432 --po_uom_s.uom_convert(from_quantity, from_uom, item_id, to_uom, to_quantity)
433 po_uom_s.uom_convert(p_current_qty
434 ,l_current_uom
435 ,p_item_id
436 ,l_primary_uom
437 ,l_primary_quantity);
438
439 RETURN l_primary_quantity; --if l_primary_quantity = -999 means can not find the UOM conversion
440
441 END get_item_primary_quantity;
442
443 --========================================================================
444 -- FUNCTION : po_uom_convert_p PUBLIC
445 -- PARAMETERS: p_from_unit the 3-character UOM_CODE of INV.MTL_UNITS_OF_MEASURE_TL
446 -- : p_to_unit the 3-character UOM_CODE of INV.MTL_UNITS_OF_MEASURE_TL
447 -- : p_item_id the item id
448 -- RETURN :
449 -- Created a function po_uom_convert_p which is pure function to be used in
450 -- the where and select clauses of a SQL
451 -- COMMENT :
452 -- PRE-COND :
453 -- EXCEPTIONS:
454 --========================================================================
455 FUNCTION po_uom_convert_p
456 ( p_from_unit IN VARCHAR2
457 , p_to_unit IN VARCHAR2
458 , p_item_id IN NUMBER
459 )
460 RETURN NUMBER
461 IS
462 l_from_unit_name MTL_UNITS_OF_MEASURE_TL.UNIT_OF_MEASURE%TYPE;
463 l_to_unit_name MTL_UNITS_OF_MEASURE_TL.UNIT_OF_MEASURE%TYPE;
464 BEGIN
465 BEGIN
466 -- get l_from_unit_name
467 SELECT UNIT_OF_MEASURE
468 INTO l_from_unit_name
469 FROM MTL_UNITS_OF_MEASURE_TL
470 WHERE LANGUAGE = USERENV('LANG')
471 AND UOM_CODE = p_from_unit;
472 -- get l_to_unit_name
473 SELECT UNIT_OF_MEASURE
474 INTO l_to_unit_name
475 FROM MTL_UNITS_OF_MEASURE_TL
476 WHERE LANGUAGE = USERENV('LANG')
477 AND UOM_CODE = p_to_unit;
478
479
480 EXCEPTION
481 WHEN no_data_found THEN
482 -- get l_from_unit_name
483 SELECT UNIT_OF_MEASURE
484 INTO l_from_unit_name
485 FROM MTL_UNITS_OF_MEASURE_TL
486 WHERE LANGUAGE = 'US'
487 AND UOM_CODE = p_from_unit;
488 -- get l_to_unit_name
489 SELECT UNIT_OF_MEASURE
490 INTO l_to_unit_name
491 FROM MTL_UNITS_OF_MEASURE_TL
492 WHERE LANGUAGE = 'US'
493 AND UOM_CODE = p_to_unit;
494 END;
495 --get the current unit of measure from the given uom_code;
499
496 RETURN PO_UOM_S.po_uom_convert_p(from_unit => l_from_unit_name --the 25-character UNIT_OF_MEASURE
497 ,to_unit => l_to_unit_name --the 25-character UNIT_OF_MEASURE
498 ,item_id => p_item_id);
500 END po_uom_convert_p;
501
502 --========================================================================
503 -- FUNCTION : get_min2 PUBLIC
504 -- PARAMETERS: p_number1 the number1
505 -- : p_number2 the number2
506 -- RETURN : return the less one
507 -- COMMENT : getting the less number
508 -- PRE-COND :
509 -- EXCEPTIONS:
510 --========================================================================
511 FUNCTION get_min2
512 ( p_number1 IN NUMBER
513 , p_number2 IN NUMBER
514 )
515 RETURN NUMBER
516 IS
517 BEGIN
518 IF p_number1 < p_number2
519 THEN
520 RETURN p_number1;
521 ELSE
522 RETURN p_number2;
523 END IF;
524
525 END get_min2;
526
527 --========================================================================
528 -- FUNCTION : get_min3 PUBLIC
529 -- PARAMETERS: p_number1 the number1
530 -- : p_number2 the number2
531 -- : p_number3 the number3
532 -- RETURN : return the less one
533 -- COMMENT : getting the less number
534 -- PRE-COND :
535 -- EXCEPTIONS:
536 --========================================================================
537 FUNCTION get_min3
538 ( p_number1 IN NUMBER
539 , p_number2 IN NUMBER
540 , p_number3 IN NUMBER
541 )
542 RETURN NUMBER
543 IS
544 BEGIN
545
546 RETURN get_min2(get_min2(p_number1
547 ,p_number2)
548 ,p_number3);
549
550 END get_min3;
551
552 --========================================================================
553 -- FUNCTION : rate_exists PUBLIC
554 -- PARAMETERS: p_from_currency From currency
555 -- : p_to_currency To currency
556 -- : p_conversion_date Conversion date
557 -- : p_conversion_type Conversion type
558 -- RETURN : return the sonversion rate
559 -- COMMENT : reference to the APPS.GL_CURRENCY_API,
560 -- Returns 'Y' if there is a conversion rate between the two currencies
561 -- for a given conversion date and conversion type;
562 -- 'N' otherwise.
563
564 -- PRE-COND :
565 -- EXCEPTIONS:
566 --========================================================================
567 FUNCTION rate_exists
568 (
572 ,p_conversion_type IN VARCHAR2 DEFAULT NULL
569 p_from_currency IN VARCHAR2 -- FND_CURRENCIES.currency_code
570 ,p_to_currency IN VARCHAR2
571 ,p_conversion_date IN DATE
573 ) RETURN VARCHAR2 IS
574 BEGIN
575
576 RETURN GL_CURRENCY_API.rate_exists(x_from_currency => p_from_currency
577 ,x_to_currency => p_to_currency
578 ,x_conversion_date => p_conversion_date
579 ,x_conversion_type => p_conversion_type);
580
581 END rate_exists;
582
583 --========================================================================
584 -- FUNCTION : get_rate PUBLIC
585 -- PARAMETERS: p_from_currency From currency
586 -- : p_to_currency To currency
587 -- : p_conversion_date Conversion date
588 -- : p_conversion_type Conversion type
589 -- RETURN : return the sonversion rate
590 -- COMMENT : reference to the APPS.GL_CURRENCY_API, to get the currency conversion rate
591 -- PRE-COND :
592 -- EXCEPTIONS:
593 --========================================================================
594 FUNCTION get_rate
595 ( p_from_currency IN VARCHAR2 -- FND_CURRENCIES.currency_code
596 , p_to_currency IN VARCHAR2
597 , p_conversion_date IN DATE
598 , p_conversion_type IN VARCHAR2 DEFAULT NULL
599 )
600 RETURN NUMBER
601 IS
602 BEGIN
603
604 RETURN GL_CURRENCY_API.get_rate(x_from_currency => p_from_currency
605 ,x_to_currency => p_to_currency
606 ,x_conversion_date => p_conversion_date
607 ,x_conversion_type => p_conversion_type);
608
609 END get_rate;
610
611 --========================================================================
612 -- FUNCTION : get_rate PUBLIC
613 -- PARAMETERS: p_ledger_id Set of books id (in R12 set of book will be replaced by ledger)
614 -- : p_from_currency From currency
615 -- : p_conversion_date Conversion date
616 -- : p_conversion_type Conversion type
617 -- RETURN : return the sonversion rate
618 -- COMMENT : reference to the APPS.GL_CURRENCY_API,
619 -- Returns the rate between the from currency and the functional
620 -- currency of the set of books.
621 -- PRE-COND :
622 -- EXCEPTIONS:
623 --========================================================================
624 FUNCTION get_rate
625 ( p_ledger_id IN NUMBER
626 , p_from_currency IN VARCHAR2 -- FND_CURRENCIES.currency_code
627 , p_conversion_date IN DATE
628 , p_conversion_type IN VARCHAR2 DEFAULT NULL
629 )
630 RETURN NUMBER
631 IS
632 BEGIN
633
634 RETURN GL_CURRENCY_API.get_rate(x_set_of_books_id => p_ledger_id
635 ,x_from_currency => p_from_currency
636 ,x_conversion_date => p_conversion_date
637 ,x_conversion_type => p_conversion_type);
638
639 END get_rate;
640
641 --========================================================================
642 -- FUNCTION : convert_amount PUBLIC
643 -- PARAMETERS: p_from_currency From currency
644 -- : p_to_currency To currency
645 -- : p_conversion_date Conversion date
646 -- : p_conversion_type Conversion type
647 -- : p_amount Amount to be converted from the from currency
648 -- into the to currency
649 -- RETURN : return the sonversion rate
650 -- COMMENT : reference to the APPS.GL_CURRENCY_API,
651 -- Returns the amount converted from the from currency into the
652 -- to currency for a given conversion date and conversion type.
653 -- The amount returned is rounded to the precision and minimum
654 -- account unit of the to currency.
655 -- PRE-COND :
656 -- EXCEPTIONS:
657 --========================================================================
658 FUNCTION convert_amount
659 ( p_from_currency IN VARCHAR2 -- FND_CURRENCIES.currency_code
660 , p_to_currency IN VARCHAR2
661 , p_conversion_date IN DATE
662 , p_conversion_type IN VARCHAR2 DEFAULT NULL
663 , p_amount IN NUMBER
664 )
665 RETURN NUMBER
666 IS
667 l_converted_amount NUMBER;
668
669 BEGIN
670
671 /*RETURN GL_CURRENCY_API.convert_amount(x_from_currency => p_from_currency
672 ,x_to_currency => p_to_currency
676 l_converted_amount := GL_CURRENCY_API.convert_amount_sql(x_from_currency => p_from_currency
673 ,x_conversion_date => p_conversion_date
674 ,x_conversion_type => p_conversion_type
675 ,x_amount => p_amount);*/
677 ,x_to_currency => p_to_currency
678 ,x_conversion_date => p_conversion_date
679 ,x_conversion_type => p_conversion_type
680 ,x_amount => p_amount);
681 RETURN l_converted_amount;
682 END convert_amount;
683
684 --========================================================================
685 -- FUNCTION : convert_amount PUBLIC
686 -- PARAMETERS: p_ledger_id Set of books id (in R12 set of book will be replaced by ledger)
687 -- : p_from_currency From currency
688 -- : p_conversion_date Conversion date
689 -- : p_conversion_type Conversion type
690 -- : p_amount Amount to be converted from the from currency
691 -- into the to currency
692 -- RETURN : return the sonversion rate
693 -- COMMENT : reference to the APPS.GL_CURRENCY_API,
694 -- Returns the amount converted from the from currency into the
695 -- functional currency of that set of books. The amount returned is
696 -- rounded to the precision and minimum account unit of the to currency.
697
698 -- PRE-COND :
699 -- EXCEPTIONS:
700 --========================================================================
701 FUNCTION convert_amount
702 ( p_ledger_id IN NUMBER
703 , p_from_currency IN VARCHAR2 -- FND_CURRENCIES.currency_code
704 , p_conversion_date IN DATE
705 , p_conversion_type IN VARCHAR2 DEFAULT NULL
706 , p_amount IN NUMBER
707 )
708 RETURN NUMBER
709 IS
710 l_converted_amount NUMBER;
711
712 BEGIN
713
714 /*RETURN GL_CURRENCY_API.convert_amount(x_set_of_books_id => p_ledger_id
715 ,x_from_currency => p_from_currency
716 ,x_conversion_date => p_conversion_date
717 ,x_conversion_type => p_conversion_type
718 ,x_amount => p_amount);*/
719 l_converted_amount := GL_CURRENCY_API.convert_amount_sql(x_set_of_books_id => p_ledger_id
720 ,x_from_currency => p_from_currency
721 ,x_conversion_date => p_conversion_date
722 ,x_conversion_type => p_conversion_type
723 ,x_amount => p_amount);
724 /*EXCEPTION
725 WHEN NO_RATE THEN
726 converted_amount := -1;
727 return( converted_amount );
728
729 WHEN INVALID_CURRENCY THEN
730 converted_amount := -2;
731 return( converted_amount );*/
732 RETURN l_converted_amount;
733 END convert_amount;
734
735 -- moved to JMF_SHIKYU_UTIL by Vincent as it is generic for all SHIKYU
736 --========================================================================
737 -- PROCEDURE : debug_output PUBLIC
738 -- PARAMETERS: p_output_to Identifier of where to output to
739 -- p_api_name the called api name
740 -- p_message the message that need to be output
741 -- COMMENT : the debug output, for using in readonly UT environment
742 -- PRE-COND :
743 -- EXCEPTIONS:
744 --========================================================================
745 PROCEDURE debug_output
746 (
747 p_output_to IN VARCHAR2
748 ,p_api_name IN VARCHAR2
749 ,p_message IN VARCHAR2
750 ) IS
751 BEGIN
752 --RETURN ; -- hide the display for hand off demo
753 CASE p_output_to
754 WHEN 'FND_LOG.STRING' THEN
755
756 IF g_fnd_debug = 'Y' AND
757 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE
758 THEN
759 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
760 ,p_api_name
761 ,p_message);
762 END IF;
763
764 WHEN 'FND_FILE.OUTPUT' THEN
765 fnd_file.put_line(fnd_file.OUTPUT
766 ,p_api_name || '.debug_output' || ': ' ||
767 p_message);
768 WHEN 'FND_FILE.LOG' THEN
769 /*IF g_fnd_debug = 'Y' AND
770 FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE
771 THEN
772 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
773 ,p_api_name || '.debug_output'
774 ,p_message);
775 END IF;*/
776 fnd_file.put_line(fnd_file.LOG
777 ,p_api_name || '.debug_output' || ': ' ||
778 p_message);
779 /*--insert into table,for debug only--
780 INSERT INTO jmf_shikyu_cfr_rpt_temp
781 (rpt_mode
782 ,rpt_data_type
783 ,attribute10
784 ,attribute11
785 ,attribute12)
786 VALUES
787 ('FND_FILE.LOG'
788 ,-999
789 ,p_api_name
790 ,p_message
791 ,to_char(SYSDATE,'YYYY-MM-DD HH:MM:SS'));
792 COMMIT; */
793 ELSE
794 NULL;
795 END CASE;
796
797 END debug_output;
798
799 END JMF_SHIKYU_RPT_UTIL;
800
[Home] [Help]