1 package body okl_accounting_util_w as
2 /* $Header: OKLEAUTB.pls 120.4 2007/01/31 07:20:51 nikshah ship $ */
3 rosetta_g_mistake_date date := to_date('01/01/+4713', 'MM/DD/SYYYY');
4 rosetta_g_miss_date date := to_date('01/01/-4712', 'MM/DD/SYYYY');
5 rosetta_g_mistake_date_high date := to_date('01/01/+4710', 'MM/DD/SYYYY');
6 rosetta_g_mistake_date_low date := to_date('01/01/-4710', 'MM/DD/SYYYY');
7
8 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
9 function rosetta_g_miss_date_in_map(d date) return date as
10 begin
11 if d > rosetta_g_mistake_date_high then return fnd_api.g_miss_date; end if;
12 if d < rosetta_g_mistake_date_low then return fnd_api.g_miss_date; end if;
13 return d;
14 end;
15
16 function rosetta_g_miss_num_map(n number) return number as
17 a number := fnd_api.g_miss_num;
18 b number := 0-1962.0724;
19 begin
20 if n=a then return b; end if;
21 if n=b then return a; end if;
22 return n;
23 end;
24
25 procedure rosetta_table_copy_in_p0(t out nocopy okl_accounting_util.seg_num_array_type, a0 JTF_NUMBER_TABLE) as
26 ddindx binary_integer; indx binary_integer;
27 begin
28 if a0 is not null and a0.count > 0 then
29 if a0.count > 0 then
30 indx := a0.first;
31 ddindx := 1;
32 while true loop
33 t(ddindx) := rosetta_g_miss_num_map(a0(indx));
34 ddindx := ddindx+1;
35 if a0.last =indx
36 then exit;
37 end if;
38 indx := a0.next(indx);
39 end loop;
40 end if;
41 end if;
42 end rosetta_table_copy_in_p0;
43 procedure rosetta_table_copy_out_p0(t okl_accounting_util.seg_num_array_type, a0 out nocopy JTF_NUMBER_TABLE) as
44 ddindx binary_integer; indx binary_integer;
45 begin
46 if t is null or t.count = 0 then
47 a0 := JTF_NUMBER_TABLE();
48 else
49 a0 := JTF_NUMBER_TABLE();
50 if t.count > 0 then
51 a0.extend(t.count);
52 ddindx := t.first;
53 indx := 1;
54 while true loop
55 a0(indx) := rosetta_g_miss_num_map(t(ddindx));
56 indx := indx+1;
57 if t.last =ddindx
58 then exit;
59 end if;
60 ddindx := t.next(ddindx);
61 end loop;
62 end if;
63 end if;
64 end rosetta_table_copy_out_p0;
65
66 procedure rosetta_table_copy_in_p1(t out nocopy okl_accounting_util.seg_array_type, a0 JTF_VARCHAR2_TABLE_100) as
67 ddindx binary_integer; indx binary_integer;
68 begin
69 if a0 is not null and a0.count > 0 then
70 if a0.count > 0 then
71 indx := a0.first;
72 ddindx := 1;
73 while true loop
74 t(ddindx) := a0(indx);
75 ddindx := ddindx+1;
76 if a0.last =indx
77 then exit;
78 end if;
79 indx := a0.next(indx);
80 end loop;
81 end if;
82 end if;
83 end rosetta_table_copy_in_p1;
84 procedure rosetta_table_copy_out_p1(t okl_accounting_util.seg_array_type, a0 out nocopy JTF_VARCHAR2_TABLE_100) as
85 ddindx binary_integer; indx binary_integer;
86 begin
87 if t is null or t.count = 0 then
88 a0 := JTF_VARCHAR2_TABLE_100();
89 else
90 a0 := JTF_VARCHAR2_TABLE_100();
91 if t.count > 0 then
92 a0.extend(t.count);
93 ddindx := t.first;
94 indx := 1;
95 while true loop
96 a0(indx) := t(ddindx);
97 indx := indx+1;
98 if t.last =ddindx
99 then exit;
100 end if;
101 ddindx := t.next(ddindx);
102 end loop;
103 end if;
104 end if;
105 end rosetta_table_copy_out_p1;
106
107 procedure rosetta_table_copy_in_p2(t out nocopy okl_accounting_util.seg_desc_array_type, a0 JTF_VARCHAR2_TABLE_100) as
108 ddindx binary_integer; indx binary_integer;
109 begin
110 if a0 is not null and a0.count > 0 then
111 if a0.count > 0 then
112 indx := a0.first;
113 ddindx := 1;
114 while true loop
115 t(ddindx) := a0(indx);
116 ddindx := ddindx+1;
117 if a0.last =indx
118 then exit;
119 end if;
120 indx := a0.next(indx);
121 end loop;
122 end if;
123 end if;
124 end rosetta_table_copy_in_p2;
125 procedure rosetta_table_copy_out_p2(t okl_accounting_util.seg_desc_array_type, a0 out nocopy JTF_VARCHAR2_TABLE_100) as
126 ddindx binary_integer; indx binary_integer;
127 begin
128 if t is null or t.count = 0 then
129 a0 := JTF_VARCHAR2_TABLE_100();
130 else
131 a0 := JTF_VARCHAR2_TABLE_100();
132 if t.count > 0 then
133 a0.extend(t.count);
134 ddindx := t.first;
135 indx := 1;
136 while true loop
137 a0(indx) := t(ddindx);
138 indx := indx+1;
139 if t.last =ddindx
140 then exit;
141 end if;
142 ddindx := t.next(ddindx);
143 end loop;
144 end if;
145 end if;
146 end rosetta_table_copy_out_p2;
147
148 procedure rosetta_table_copy_in_p3(t out nocopy okl_accounting_util.error_message_type, a0 JTF_VARCHAR2_TABLE_2000) as
149 ddindx binary_integer; indx binary_integer;
150 begin
151 if a0 is not null and a0.count > 0 then
152 if a0.count > 0 then
153 indx := a0.first;
154 ddindx := 1;
155 while true loop
156 t(ddindx) := a0(indx);
157 ddindx := ddindx+1;
158 if a0.last =indx
159 then exit;
160 end if;
161 indx := a0.next(indx);
162 end loop;
163 end if;
164 end if;
165 end rosetta_table_copy_in_p3;
166 procedure rosetta_table_copy_out_p3(t okl_accounting_util.error_message_type, a0 out nocopy JTF_VARCHAR2_TABLE_2000) as
167 ddindx binary_integer; indx binary_integer;
168 begin
169 if t is null or t.count = 0 then
170 a0 := JTF_VARCHAR2_TABLE_2000();
171 else
172 a0 := JTF_VARCHAR2_TABLE_2000();
173 if t.count > 0 then
174 a0.extend(t.count);
175 ddindx := t.first;
176 indx := 1;
177 while true loop
178 a0(indx) := t(ddindx);
179 indx := indx+1;
180 if t.last =ddindx
181 then exit;
182 end if;
183 ddindx := t.next(ddindx);
184 end loop;
185 end if;
186 end if;
187 end rosetta_table_copy_out_p3;
188
189 procedure rosetta_table_copy_in_p26(t out nocopy okl_accounting_util.overlap_attrib_tbl_type, a0 JTF_VARCHAR2_TABLE_100
190 , a1 JTF_VARCHAR2_TABLE_100
191 , a2 JTF_VARCHAR2_TABLE_200
192 ) as
193 ddindx binary_integer; indx binary_integer;
194 begin
195 if a0 is not null and a0.count > 0 then
196 if a0.count > 0 then
197 indx := a0.first;
198 ddindx := 1;
199 while true loop
200 t(ddindx).attribute := a0(indx);
201 t(ddindx).attrib_type := a1(indx);
202 t(ddindx).value := a2(indx);
203 ddindx := ddindx+1;
204 if a0.last =indx
205 then exit;
206 end if;
207 indx := a0.next(indx);
208 end loop;
209 end if;
210 end if;
211 end rosetta_table_copy_in_p26;
212 procedure rosetta_table_copy_out_p26(t okl_accounting_util.overlap_attrib_tbl_type, a0 out nocopy JTF_VARCHAR2_TABLE_100
213 , a1 out nocopy JTF_VARCHAR2_TABLE_100
214 , a2 out nocopy JTF_VARCHAR2_TABLE_200
215 ) as
216 ddindx binary_integer; indx binary_integer;
217 begin
218 if t is null or t.count = 0 then
219 a0 := JTF_VARCHAR2_TABLE_100();
220 a1 := JTF_VARCHAR2_TABLE_100();
221 a2 := JTF_VARCHAR2_TABLE_200();
222 else
223 a0 := JTF_VARCHAR2_TABLE_100();
224 a1 := JTF_VARCHAR2_TABLE_100();
225 a2 := JTF_VARCHAR2_TABLE_200();
226 if t.count > 0 then
227 a0.extend(t.count);
228 a1.extend(t.count);
229 a2.extend(t.count);
230 ddindx := t.first;
231 indx := 1;
232 while true loop
233 a0(indx) := t(ddindx).attribute;
234 a1(indx) := t(ddindx).attrib_type;
235 a2(indx) := t(ddindx).value;
236 indx := indx+1;
237 if t.last =ddindx
238 then exit;
239 end if;
240 ddindx := t.next(ddindx);
241 end loop;
242 end if;
243 end if;
244 end rosetta_table_copy_out_p26;
245
246 procedure get_segment_array(p_concate_segments VARCHAR2
247 , p_delimiter VARCHAR2
248 , p_seg_array_type out nocopy JTF_VARCHAR2_TABLE_100
249 )
250
251 as
252 ddp_seg_array_type okl_accounting_util.seg_array_type;
253 ddindx binary_integer; indx binary_integer;
254 begin
255
256 -- copy data to the local IN or IN-OUT args, if any
257
258
259
260 -- here's the delegated call to the old PL/SQL routine
261 okl_accounting_util.get_segment_array(p_concate_segments,
262 p_delimiter,
263 ddp_seg_array_type);
264
265 -- copy data back from the local variables to OUT or IN-OUT args, if any
266
267
268 okl_accounting_util_w.rosetta_table_copy_out_p1(ddp_seg_array_type, p_seg_array_type);
269 end;
270
271 procedure get_error_message(p_all_message out nocopy JTF_VARCHAR2_TABLE_2000
272 )
273
274 as
275 ddp_all_message okl_accounting_util.error_message_type;
276 ddindx binary_integer; indx binary_integer;
277 begin
278
279 -- copy data to the local IN or IN-OUT args, if any
280
281 -- here's the delegated call to the old PL/SQL routine
282 okl_accounting_util.get_error_message(ddp_all_message);
283
284 -- copy data back from the local variables to OUT or IN-OUT args, if any
285 okl_accounting_util_w.rosetta_table_copy_out_p3(ddp_all_message, p_all_message);
286 end;
287
288 procedure get_error_msg(p_all_message out nocopy JTF_VARCHAR2_TABLE_2000
289 )
290
291 as
292 ddp_all_message okl_accounting_util.error_message_type;
293 ddindx binary_integer; indx binary_integer;
294 begin
295
296 -- copy data to the local IN or IN-OUT args, if any
297
298 -- here's the delegated call to the old PL/SQL routine
299 okl_accounting_util.get_error_msg(ddp_all_message);
300
301 -- copy data back from the local variables to OUT or IN-OUT args, if any
302 okl_accounting_util_w.rosetta_table_copy_out_p3(ddp_all_message, p_all_message);
303 end;
304
305 function get_curr_con_rate(p_from_curr_code VARCHAR2
306 , p_to_curr_code VARCHAR2
307 , p_con_date date
308 , p_con_type VARCHAR2
309 ) return number
310
311 as
312 ddp_con_date date;
313 ddindx binary_integer; indx binary_integer;
314 ddrosetta_retval number;
315 begin
316
317 -- copy data to the local IN or IN-OUT args, if any
318
319
320 ddp_con_date := rosetta_g_miss_date_in_map(p_con_date);
321
322
323 -- here's the delegated call to the old PL/SQL routine
324 ddrosetta_retval := okl_accounting_util.get_curr_con_rate(p_from_curr_code,
325 p_to_curr_code,
326 ddp_con_date,
327 p_con_type);
328
329 -- copy data back from the local variables to OUT or IN-OUT args, if any
330
331
332
333
334 return ddrosetta_retval;
335 end;
336
337 procedure get_curr_con_rate(p_api_version NUMBER
338 , p_init_msg_list VARCHAR2
339 , x_return_status out nocopy VARCHAR2
340 , x_msg_count out nocopy NUMBER
341 , x_msg_data out nocopy VARCHAR2
342 , p_from_curr_code VARCHAR2
343 , p_to_curr_code VARCHAR2
344 , p_con_date date
345 , p_con_type VARCHAR2
346 , x_conv_rate out nocopy NUMBER
347 )
348
349 as
350 ddp_con_date date;
351 ddindx binary_integer; indx binary_integer;
352 begin
353
354 -- copy data to the local IN or IN-OUT args, if any
355
356
357
358
359
360
361
362 ddp_con_date := rosetta_g_miss_date_in_map(p_con_date);
363
364
365
366 -- here's the delegated call to the old PL/SQL routine
367 okl_accounting_util.get_curr_con_rate(p_api_version,
368 p_init_msg_list,
369 x_return_status,
370 x_msg_count,
371 x_msg_data,
372 p_from_curr_code,
373 p_to_curr_code,
374 ddp_con_date,
375 p_con_type,
376 x_conv_rate);
377
378 -- copy data back from the local variables to OUT or IN-OUT args, if any
379
380
381
382
383
384
385
386
387
388 end;
389
390 procedure get_accounting_segment(p0_a0 out nocopy JTF_NUMBER_TABLE
391 , p0_a1 out nocopy JTF_VARCHAR2_TABLE_100
392 , p0_a2 out nocopy JTF_VARCHAR2_TABLE_100
393 )
394
395 as
396 ddp_segment_array okl_accounting_util.seg_num_name_type;
397 ddindx binary_integer; indx binary_integer;
398 begin
399
400 -- copy data to the local IN or IN-OUT args, if any
401
402 -- here's the delegated call to the old PL/SQL routine
403 okl_accounting_util.get_accounting_segment(ddp_segment_array);
404
405 -- copy data back from the local variables to OUT or IN-OUT args, if any
406 okl_accounting_util_w.rosetta_table_copy_out_p0(ddp_segment_array.seg_num, p0_a0);
407 okl_accounting_util_w.rosetta_table_copy_out_p1(ddp_segment_array.seg_name, p0_a1);
408 okl_accounting_util_w.rosetta_table_copy_out_p2(ddp_segment_array.seg_desc, p0_a2);
409 end;
410
411 procedure get_period_info(p_date date
412 , p_period_name out nocopy VARCHAR2
413 , p_start_date out nocopy DATE
414 , p_end_date out nocopy DATE
415 , p_ledger_id NUMBER
416 )
417
418 as
419 ddp_date date;
420 ddindx binary_integer; indx binary_integer;
421 begin
422
423 -- copy data to the local IN or IN-OUT args, if any
424 ddp_date := rosetta_g_miss_date_in_map(p_date);
425
426
427
428
429
430 -- here's the delegated call to the old PL/SQL routine
431 okl_accounting_util.get_period_info(ddp_date,
432 p_period_name,
433 p_start_date,
434 p_end_date,
435 p_ledger_id);
436
437 -- copy data back from the local variables to OUT or IN-OUT args, if any
438
439
440
441
442 end;
443
444 procedure check_overlaps(p_id NUMBER
445 , p1_a0 JTF_VARCHAR2_TABLE_100
446 , p1_a1 JTF_VARCHAR2_TABLE_100
447 , p1_a2 JTF_VARCHAR2_TABLE_200
448 , p_start_date_attribute_name VARCHAR2
449 , p_start_date date
450 , p_end_date_attribute_name VARCHAR2
451 , p_end_date date
452 , p_view VARCHAR2
453 , x_return_status out nocopy VARCHAR2
454 , x_valid out nocopy number
455 )
456
457 as
458 ddp_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
459 ddp_start_date date;
460 ddp_end_date date;
461 ddx_valid boolean;
462 ddindx binary_integer; indx binary_integer;
463 begin
464
465 -- copy data to the local IN or IN-OUT args, if any
466
467 okl_accounting_util_w.rosetta_table_copy_in_p26(ddp_attrib_tbl, p1_a0
468 , p1_a1
469 , p1_a2
470 );
471
472
473 ddp_start_date := rosetta_g_miss_date_in_map(p_start_date);
474
475
476 ddp_end_date := rosetta_g_miss_date_in_map(p_end_date);
477
478
479
480
481 -- here's the delegated call to the old PL/SQL routine
482 okl_accounting_util.check_overlaps(p_id,
483 ddp_attrib_tbl,
484 p_start_date_attribute_name,
485 ddp_start_date,
486 p_end_date_attribute_name,
487 ddp_end_date,
488 p_view,
489 x_return_status,
490 ddx_valid);
491
492 -- copy data back from the local variables to OUT or IN-OUT args, if any
493
494
495
496
497
498
499
500
501 if ddx_valid is null
502 then x_valid := null;
503 elsif ddx_valid
504 then x_valid := 1;
505 else x_valid := 0;
506 end if;
507 end;
508
509 procedure get_version(p0_a0 JTF_VARCHAR2_TABLE_100
510 , p0_a1 JTF_VARCHAR2_TABLE_100
511 , p0_a2 JTF_VARCHAR2_TABLE_200
512 , p_cur_version VARCHAR2
513 , p_end_date_attribute_name VARCHAR2
514 , p_end_date date
515 , p_view VARCHAR2
516 , x_return_status out nocopy VARCHAR2
517 , x_new_version out nocopy VARCHAR2
518 )
519
520 as
521 ddp_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
522 ddp_end_date date;
523 ddindx binary_integer; indx binary_integer;
524 begin
525
526 -- copy data to the local IN or IN-OUT args, if any
527 okl_accounting_util_w.rosetta_table_copy_in_p26(ddp_attrib_tbl, p0_a0
528 , p0_a1
529 , p0_a2
530 );
531
532
533
534 ddp_end_date := rosetta_g_miss_date_in_map(p_end_date);
535
536
537
538
539 -- here's the delegated call to the old PL/SQL routine
540 okl_accounting_util.get_version(ddp_attrib_tbl,
541 p_cur_version,
542 p_end_date_attribute_name,
543 ddp_end_date,
544 p_view,
545 x_return_status,
546 x_new_version);
547
548 -- copy data back from the local variables to OUT or IN-OUT args, if any
549
550
551
552
553
554
555 end;
556
557 procedure convert_to_functional_currency(p_khr_id NUMBER
558 , p_to_currency VARCHAR2
559 , p_transaction_date date
560 , p_amount NUMBER
561 , x_contract_currency out nocopy VARCHAR2
562 , x_currency_conversion_type out nocopy VARCHAR2
563 , x_currency_conversion_rate out nocopy NUMBER
564 , x_currency_conversion_date out nocopy DATE
565 , x_converted_amount out nocopy NUMBER
566 )
567
568 as
569 ddp_transaction_date date;
570 ddindx binary_integer; indx binary_integer;
571 begin
572
573 -- copy data to the local IN or IN-OUT args, if any
574
575
576 ddp_transaction_date := rosetta_g_miss_date_in_map(p_transaction_date);
577
578
579
580
581
582
583
584 -- here's the delegated call to the old PL/SQL routine
585 okl_accounting_util.convert_to_functional_currency(p_khr_id,
586 p_to_currency,
587 ddp_transaction_date,
588 p_amount,
589 x_contract_currency,
590 x_currency_conversion_type,
591 x_currency_conversion_rate,
592 x_currency_conversion_date,
593 x_converted_amount);
594
595 -- copy data back from the local variables to OUT or IN-OUT args, if any
596
597
598
599
600
601
602
603
604 end;
605
606 procedure convert_to_functional_currency(p_khr_id NUMBER
607 , p_to_currency VARCHAR2
608 , p_transaction_date date
609 , p_amount NUMBER
610 , x_return_status out nocopy VARCHAR2
611 , x_contract_currency out nocopy VARCHAR2
612 , x_currency_conversion_type out nocopy VARCHAR2
613 , x_currency_conversion_rate out nocopy NUMBER
614 , x_currency_conversion_date out nocopy DATE
615 , x_converted_amount out nocopy NUMBER
616 )
617
618 as
619 ddp_transaction_date date;
620 ddindx binary_integer; indx binary_integer;
621 begin
622
623 -- copy data to the local IN or IN-OUT args, if any
624
625
626 ddp_transaction_date := rosetta_g_miss_date_in_map(p_transaction_date);
627
628
629
630
631
632
633
634
635 -- here's the delegated call to the old PL/SQL routine
636 okl_accounting_util.convert_to_functional_currency(p_khr_id,
637 p_to_currency,
638 ddp_transaction_date,
639 p_amount,
640 x_return_status,
641 x_contract_currency,
642 x_currency_conversion_type,
643 x_currency_conversion_rate,
644 x_currency_conversion_date,
645 x_converted_amount);
646
647 -- copy data back from the local variables to OUT or IN-OUT args, if any
648
649
650
651
652
653
654
655
656
657 end;
658
659 procedure convert_to_contract_currency(p_khr_id NUMBER
660 , p_from_currency VARCHAR2
661 , p_transaction_date date
662 , p_amount NUMBER
663 , x_contract_currency out nocopy VARCHAR2
664 , x_currency_conversion_type out nocopy VARCHAR2
665 , x_currency_conversion_rate out nocopy NUMBER
666 , x_currency_conversion_date out nocopy DATE
667 , x_converted_amount out nocopy NUMBER
668 )
669
670 as
671 ddp_transaction_date date;
672 ddindx binary_integer; indx binary_integer;
673 begin
674
675 -- copy data to the local IN or IN-OUT args, if any
676
677
678 ddp_transaction_date := rosetta_g_miss_date_in_map(p_transaction_date);
679
680
681
682
683
684
685
686 -- here's the delegated call to the old PL/SQL routine
687 okl_accounting_util.convert_to_contract_currency(p_khr_id,
688 p_from_currency,
689 ddp_transaction_date,
690 p_amount,
691 x_contract_currency,
692 x_currency_conversion_type,
693 x_currency_conversion_rate,
694 x_currency_conversion_date,
695 x_converted_amount);
696
697 -- copy data back from the local variables to OUT or IN-OUT args, if any
698
699
700
701
702
703
704
705
706 end;
707
708 procedure convert_to_contract_currency(p_khr_id NUMBER
709 , p_from_currency VARCHAR2
710 , p_transaction_date date
711 , p_amount NUMBER
712 , x_return_status out nocopy VARCHAR2
713 , x_contract_currency out nocopy VARCHAR2
714 , x_currency_conversion_type out nocopy VARCHAR2
715 , x_currency_conversion_rate out nocopy NUMBER
716 , x_currency_conversion_date out nocopy DATE
717 , x_converted_amount out nocopy NUMBER
718 )
719
720 as
721 ddp_transaction_date date;
722 ddindx binary_integer; indx binary_integer;
723 begin
724
725 -- copy data to the local IN or IN-OUT args, if any
726
727
728 ddp_transaction_date := rosetta_g_miss_date_in_map(p_transaction_date);
729
730
731
732
733
734
735
736
737 -- here's the delegated call to the old PL/SQL routine
738 okl_accounting_util.convert_to_contract_currency(p_khr_id,
739 p_from_currency,
740 ddp_transaction_date,
741 p_amount,
742 x_return_status,
743 x_contract_currency,
744 x_currency_conversion_type,
745 x_currency_conversion_rate,
746 x_currency_conversion_date,
747 x_converted_amount);
748
749 -- copy data back from the local variables to OUT or IN-OUT args, if any
750
751
752
753
754
755
756
757
758
759 end;
760
761 function get_valid_gl_date(p_gl_date date
762 , p_ledger_id NUMBER
763 ) return date
764
765 as
766 ddp_gl_date date;
767 ddindx binary_integer; indx binary_integer;
768 ddrosetta_retval date;
769 begin
770
771 -- copy data to the local IN or IN-OUT args, if any
772 ddp_gl_date := rosetta_g_miss_date_in_map(p_gl_date);
773
774
775 -- here's the delegated call to the old PL/SQL routine
776 ddrosetta_retval := okl_accounting_util.get_valid_gl_date(ddp_gl_date,
777 p_ledger_id);
778
779 -- copy data back from the local variables to OUT or IN-OUT args, if any
780
781
782 return ddrosetta_retval;
783 end;
784
785 end okl_accounting_util_w;