1 package body ozf_utility_pvt_w as
2 /* $Header: ozfwutlb.pls 120.1 2006/04/04 08:30:06 sshivali noship $ */
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 procedure rosetta_table_copy_in_p12(t out nocopy ozf_utility_pvt.operating_units_tbl, a0 JTF_NUMBER_TABLE) as
17 ddindx binary_integer; indx binary_integer;
18 begin
19 if a0 is not null and a0.count > 0 then
20 if a0.count > 0 then
21 indx := a0.first;
22 ddindx := 1;
23 while true loop
24 t(ddindx) := a0(indx);
25 ddindx := ddindx+1;
26 if a0.last =indx
27 then exit;
28 end if;
29 indx := a0.next(indx);
30 end loop;
31 end if;
32 end if;
33 end rosetta_table_copy_in_p12;
34 procedure rosetta_table_copy_out_p12(t ozf_utility_pvt.operating_units_tbl, a0 out nocopy JTF_NUMBER_TABLE) as
35 ddindx binary_integer; indx binary_integer;
36 begin
37 if t is null or t.count = 0 then
38 a0 := JTF_NUMBER_TABLE();
39 else
40 a0 := JTF_NUMBER_TABLE();
41 if t.count > 0 then
42 a0.extend(t.count);
43 ddindx := t.first;
44 indx := 1;
45 while true loop
46 a0(indx) := t(ddindx);
47 indx := indx+1;
48 if t.last =ddindx
49 then exit;
50 end if;
51 ddindx := t.next(ddindx);
52 end loop;
53 end if;
54 end if;
55 end rosetta_table_copy_out_p12;
56
57 procedure rosetta_table_copy_in_p59(t out nocopy ozf_utility_pvt.dependent_objects_tbl_type, a0 JTF_VARCHAR2_TABLE_300
58 , a1 JTF_VARCHAR2_TABLE_100
59 , a2 JTF_VARCHAR2_TABLE_100
60 , a3 JTF_VARCHAR2_TABLE_300
61 , a4 JTF_VARCHAR2_TABLE_100
62 ) as
63 ddindx binary_integer; indx binary_integer;
64 begin
65 if a0 is not null and a0.count > 0 then
66 if a0.count > 0 then
67 indx := a0.first;
68 ddindx := 1;
69 while true loop
70 t(ddindx).name := a0(indx);
71 t(ddindx).type := a1(indx);
72 t(ddindx).status := a2(indx);
73 t(ddindx).owner := a3(indx);
74 t(ddindx).deletable_flag := a4(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_p59;
84 procedure rosetta_table_copy_out_p59(t ozf_utility_pvt.dependent_objects_tbl_type, a0 out nocopy JTF_VARCHAR2_TABLE_300
85 , a1 out nocopy JTF_VARCHAR2_TABLE_100
86 , a2 out nocopy JTF_VARCHAR2_TABLE_100
87 , a3 out nocopy JTF_VARCHAR2_TABLE_300
88 , a4 out nocopy JTF_VARCHAR2_TABLE_100
89 ) as
90 ddindx binary_integer; indx binary_integer;
91 begin
92 if t is null or t.count = 0 then
93 a0 := JTF_VARCHAR2_TABLE_300();
94 a1 := JTF_VARCHAR2_TABLE_100();
95 a2 := JTF_VARCHAR2_TABLE_100();
96 a3 := JTF_VARCHAR2_TABLE_300();
97 a4 := JTF_VARCHAR2_TABLE_100();
98 else
99 a0 := JTF_VARCHAR2_TABLE_300();
100 a1 := JTF_VARCHAR2_TABLE_100();
101 a2 := JTF_VARCHAR2_TABLE_100();
102 a3 := JTF_VARCHAR2_TABLE_300();
103 a4 := JTF_VARCHAR2_TABLE_100();
104 if t.count > 0 then
105 a0.extend(t.count);
106 a1.extend(t.count);
107 a2.extend(t.count);
108 a3.extend(t.count);
109 a4.extend(t.count);
110 ddindx := t.first;
111 indx := 1;
112 while true loop
113 a0(indx) := t(ddindx).name;
114 a1(indx) := t(ddindx).type;
115 a2(indx) := t(ddindx).status;
116 a3(indx) := t(ddindx).owner;
117 a4(indx) := t(ddindx).deletable_flag;
118 indx := indx+1;
119 if t.last =ddindx
120 then exit;
121 end if;
122 ddindx := t.next(ddindx);
123 end loop;
124 end if;
125 end if;
126 end rosetta_table_copy_out_p59;
127
128 procedure log_message(p_log_level NUMBER
129 , p_module_name VARCHAR2
130 , p_rcs_id VARCHAR2
131 , p_pop_message number
132 )
133
134 as
135 ddp_pop_message boolean;
136 ddindx binary_integer; indx binary_integer;
137 begin
138
139 -- copy data to the local IN or IN-OUT args, if any
140
141
142
143 if p_pop_message is null
144 then ddp_pop_message := null;
145 elsif p_pop_message = 0
146 then ddp_pop_message := false;
147 else ddp_pop_message := true;
148 end if;
149
150 -- here's the delegated call to the old PL/SQL routine
151 ozf_utility_pvt.log_message(p_log_level,
152 p_module_name,
153 p_rcs_id,
154 ddp_pop_message);
155
156 -- copy data back from the local variables to OUT or IN-OUT args, if any
157
158
159
160 end;
161
162 procedure logging_enabled(p_log_level NUMBER
163 , ddrosetta_retval_bool OUT NOCOPY NUMBER
164 )
165
166 as
167 ddindx binary_integer; indx binary_integer;
168 ddrosetta_retval boolean;
169 begin
170
171 -- copy data to the local IN or IN-OUT args, if any
172
173 -- here's the delegated call to the old PL/SQL routine
174 ddrosetta_retval := ozf_utility_pvt.logging_enabled(p_log_level);
175
176 -- copy data back from the local variables to OUT or IN-OUT args, if any
177 if ddrosetta_retval is null
178 then ddrosetta_retval_bool := null;
179 elsif ddrosetta_retval
180 then ddrosetta_retval_bool := 1;
181 else ddrosetta_retval_bool := 0;
182 end if;
183 end;
184
185 procedure create_log(x_return_status out nocopy VARCHAR2
186 , p_arc_log_used_by VARCHAR2
187 , p_log_used_by_id VARCHAR2
188 , p_msg_data VARCHAR2
189 , p_msg_level NUMBER
190 , p_msg_type VARCHAR2
191 , p_desc VARCHAR2
192 , p_budget_id NUMBER
193 , p_threshold_id NUMBER
194 , p_transaction_id NUMBER
195 , p_notification_creat_date date
196 , p_activity_log_id NUMBER
197 )
198
199 as
200 ddp_notification_creat_date date;
201 ddindx binary_integer; indx binary_integer;
202 begin
203
204 -- copy data to the local IN or IN-OUT args, if any
205
206
207
208
209
210
211
212
213
214
215 ddp_notification_creat_date := rosetta_g_miss_date_in_map(p_notification_creat_date);
216
217
218 -- here's the delegated call to the old PL/SQL routine
219 ozf_utility_pvt.create_log(x_return_status,
220 p_arc_log_used_by,
221 p_log_used_by_id,
222 p_msg_data,
223 p_msg_level,
224 p_msg_type,
225 p_desc,
226 p_budget_id,
227 p_threshold_id,
228 p_transaction_id,
229 ddp_notification_creat_date,
230 p_activity_log_id);
231
232 -- copy data back from the local variables to OUT or IN-OUT args, if any
233
234
235
236
237
238
239
240
241
242
243
244 end;
245
246 procedure convert_currency(x_return_status out nocopy VARCHAR2
247 , p_from_currency VARCHAR2
248 , p_to_currency VARCHAR2
249 , p_conv_date date
250 , p_from_amount NUMBER
251 , x_to_amount out nocopy NUMBER
252 )
253
254 as
255 ddp_conv_date date;
256 ddindx binary_integer; indx binary_integer;
257 begin
258
259 -- copy data to the local IN or IN-OUT args, if any
260
261
262
263 ddp_conv_date := rosetta_g_miss_date_in_map(p_conv_date);
264
265
266
267 -- here's the delegated call to the old PL/SQL routine
268 ozf_utility_pvt.convert_currency(x_return_status,
269 p_from_currency,
270 p_to_currency,
271 ddp_conv_date,
272 p_from_amount,
273 x_to_amount);
274
275 -- copy data back from the local variables to OUT or IN-OUT args, if any
276
277
278
279
280
281 end;
282
283 procedure convert_timezone(p_init_msg_list VARCHAR2
284 , x_return_status out nocopy VARCHAR2
285 , x_msg_count out nocopy NUMBER
286 , x_msg_data out nocopy VARCHAR2
287 , p_user_tz_id NUMBER
288 , p_in_time date
289 , p_convert_type VARCHAR2
290 , x_out_time out nocopy DATE
291 )
292
293 as
294 ddp_in_time date;
295 ddindx binary_integer; indx binary_integer;
296 begin
297
298 -- copy data to the local IN or IN-OUT args, if any
299
300
301
302
303
304 ddp_in_time := rosetta_g_miss_date_in_map(p_in_time);
305
306
307
308 -- here's the delegated call to the old PL/SQL routine
309 ozf_utility_pvt.convert_timezone(p_init_msg_list,
310 x_return_status,
311 x_msg_count,
312 x_msg_data,
313 p_user_tz_id,
314 ddp_in_time,
315 p_convert_type,
316 x_out_time);
317
318 -- copy data back from the local variables to OUT or IN-OUT args, if any
319
320
321
322
323
324
325
326 end;
327
328 procedure convert_currency(p_set_of_books_id NUMBER
329 , p_from_currency VARCHAR2
330 , p_conversion_date date
331 , p_conversion_type VARCHAR2
332 , p_conversion_rate NUMBER
333 , p_amount NUMBER
334 , x_return_status out nocopy VARCHAR2
335 , x_acc_amount out nocopy NUMBER
336 , x_rate out nocopy NUMBER
337 )
338
339 as
340 ddp_conversion_date date;
341 ddindx binary_integer; indx binary_integer;
342 begin
343
344 -- copy data to the local IN or IN-OUT args, if any
345
346
347 ddp_conversion_date := rosetta_g_miss_date_in_map(p_conversion_date);
348
349
350
351
352
353
354
355 -- here's the delegated call to the old PL/SQL routine
356 ozf_utility_pvt.convert_currency(p_set_of_books_id,
357 p_from_currency,
358 ddp_conversion_date,
359 p_conversion_type,
360 p_conversion_rate,
361 p_amount,
362 x_return_status,
363 x_acc_amount,
364 x_rate);
365
366 -- copy data back from the local variables to OUT or IN-OUT args, if any
367
368
369
370
371
372
373
374
375 end;
376
377 procedure calculate_functional_curr(p_from_amount NUMBER
378 , p_conv_date date
379 , p_tc_currency_code VARCHAR2
380 , p_org_id NUMBER
381 , x_to_amount out nocopy NUMBER
382 , x_set_of_books_id out nocopy NUMBER
383 , x_mrc_sob_type_code out nocopy VARCHAR2
384 , x_fc_currency_code out nocopy VARCHAR2
385 , x_exchange_rate_type in out nocopy VARCHAR2
386 , x_exchange_rate in out nocopy NUMBER
387 , x_return_status out nocopy VARCHAR2
388 )
389
390 as
391 ddp_conv_date date;
392 ddindx binary_integer; indx binary_integer;
393 begin
394
395 -- copy data to the local IN or IN-OUT args, if any
396
397 ddp_conv_date := rosetta_g_miss_date_in_map(p_conv_date);
398
399
400
401
402
403
404
405
406
407
408 -- here's the delegated call to the old PL/SQL routine
409 ozf_utility_pvt.calculate_functional_curr(p_from_amount,
410 ddp_conv_date,
411 p_tc_currency_code,
412 p_org_id,
413 x_to_amount,
414 x_set_of_books_id,
415 x_mrc_sob_type_code,
416 x_fc_currency_code,
417 x_exchange_rate_type,
418 x_exchange_rate,
419 x_return_status);
420
421 -- copy data back from the local variables to OUT or IN-OUT args, if any
422
423
424
425
426
427
428
429
430
431
432 end;
433
434 procedure calculate_functional_currency(p_from_amount NUMBER
435 , p_conv_date date
436 , p_tc_currency_code VARCHAR2
437 , p_ledger_id NUMBER
438 , x_to_amount out nocopy NUMBER
439 , x_mrc_sob_type_code out nocopy VARCHAR2
440 , x_fc_currency_code out nocopy VARCHAR2
441 , x_exchange_rate_type in out nocopy VARCHAR2
442 , x_exchange_rate in out nocopy NUMBER
443 , x_return_status out nocopy VARCHAR2
444 )
445
446 as
447 ddp_conv_date date;
448 ddindx binary_integer; indx binary_integer;
449 begin
450
451 -- copy data to the local IN or IN-OUT args, if any
452
453 ddp_conv_date := rosetta_g_miss_date_in_map(p_conv_date);
454
455
456
457
458
459
460
461
462
463 -- here's the delegated call to the old PL/SQL routine
464 ozf_utility_pvt.calculate_functional_currency(p_from_amount,
465 ddp_conv_date,
466 p_tc_currency_code,
467 p_ledger_id,
468 x_to_amount,
469 x_mrc_sob_type_code,
470 x_fc_currency_code,
471 x_exchange_rate_type,
472 x_exchange_rate,
473 x_return_status);
474
475 -- copy data back from the local variables to OUT or IN-OUT args, if any
476
477
478
479
480
481
482
483
484
485 end;
486
487 procedure convert_currency(p_from_currency VARCHAR2
488 , p_to_currency VARCHAR2
489 , p_conv_type VARCHAR2
490 , p_conv_rate NUMBER
491 , p_conv_date date
492 , p_from_amount NUMBER
493 , x_return_status out nocopy VARCHAR2
494 , x_to_amount out nocopy NUMBER
495 , x_rate out nocopy NUMBER
496 )
497
498 as
499 ddp_conv_date date;
500 ddindx binary_integer; indx binary_integer;
501 begin
502
503 -- copy data to the local IN or IN-OUT args, if any
504
505
506
507
508 ddp_conv_date := rosetta_g_miss_date_in_map(p_conv_date);
509
510
511
512
513
514 -- here's the delegated call to the old PL/SQL routine
515 ozf_utility_pvt.convert_currency(p_from_currency,
516 p_to_currency,
517 p_conv_type,
518 p_conv_rate,
519 ddp_conv_date,
520 p_from_amount,
521 x_return_status,
522 x_to_amount,
523 x_rate);
524
525 -- copy data back from the local variables to OUT or IN-OUT args, if any
526
527
528
529
530
531
532
533
534 end;
535
536 end ozf_utility_pvt_w;