[Home] [Help]
PACKAGE BODY: APPS.HZ_HIERARCHY_V2PUB_JW
Source
1 package body hz_hierarchy_v2pub_jw as
2 /* $Header: ARH2HIJB.pls 120.2 2005/06/18 04:28:22 jhuang 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
6 function rosetta_g_miss_num_map(n number) return number as
7 a number := fnd_api.g_miss_num;
8 b number := 0-1962.0724;
9 begin
10 if n=a then return b; end if;
11 if n=b then return a; end if;
12 return n;
13 end;
14
15 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
16 function rosetta_g_miss_date_in_map(d date) return date as
17 begin
18 if d = rosetta_g_mistake_date then return fnd_api.g_miss_date; end if;
19 return d;
20 end;
21
22 procedure rosetta_table_copy_in_p1(t out nocopy hz_hierarchy_v2pub.related_nodes_list_type, a0 JTF_NUMBER_TABLE
23 , a1 JTF_VARCHAR2_TABLE_100
24 , a2 JTF_VARCHAR2_TABLE_100
25 , a3 JTF_NUMBER_TABLE
26 , a4 JTF_VARCHAR2_TABLE_100
27 , a5 JTF_VARCHAR2_TABLE_100
28 , a6 JTF_DATE_TABLE
29 , a7 JTF_DATE_TABLE
30 , a8 JTF_NUMBER_TABLE
31 ) as
32 ddindx binary_integer; indx binary_integer;
33 begin
34 if a0 is not null and a0.count > 0 then
35 if a0.count > 0 then
36 indx := a0.first;
37 ddindx := 1;
38 while true loop
39 t(ddindx).related_node_id := rosetta_g_miss_num_map(a0(indx));
40 t(ddindx).related_node_table_name := a1(indx);
41 t(ddindx).related_node_object_type := a2(indx);
42 t(ddindx).level_number := rosetta_g_miss_num_map(a3(indx));
43 t(ddindx).top_parent_flag := a4(indx);
44 t(ddindx).leaf_child_flag := a5(indx);
45 t(ddindx).effective_start_date := rosetta_g_miss_date_in_map(a6(indx));
46 t(ddindx).effective_end_date := rosetta_g_miss_date_in_map(a7(indx));
47 t(ddindx).relationship_id := rosetta_g_miss_num_map(a8(indx));
48 ddindx := ddindx+1;
49 if a0.last =indx
50 then exit;
51 end if;
52 indx := a0.next(indx);
53 end loop;
54 end if;
55 end if;
56 end rosetta_table_copy_in_p1;
57 procedure rosetta_table_copy_out_p1(t hz_hierarchy_v2pub.related_nodes_list_type, a0 out nocopy JTF_NUMBER_TABLE
58 , a1 out nocopy JTF_VARCHAR2_TABLE_100
59 , a2 out nocopy JTF_VARCHAR2_TABLE_100
60 , a3 out nocopy JTF_NUMBER_TABLE
61 , a4 out nocopy JTF_VARCHAR2_TABLE_100
62 , a5 out nocopy JTF_VARCHAR2_TABLE_100
63 , a6 out nocopy JTF_DATE_TABLE
64 , a7 out nocopy JTF_DATE_TABLE
65 , a8 out nocopy JTF_NUMBER_TABLE
66 ) as
67 ddindx binary_integer; indx binary_integer;
68 begin
69 if t is null or t.count = 0 then
70 a0 := JTF_NUMBER_TABLE();
71 a1 := JTF_VARCHAR2_TABLE_100();
72 a2 := JTF_VARCHAR2_TABLE_100();
73 a3 := JTF_NUMBER_TABLE();
74 a4 := JTF_VARCHAR2_TABLE_100();
75 a5 := JTF_VARCHAR2_TABLE_100();
76 a6 := JTF_DATE_TABLE();
77 a7 := JTF_DATE_TABLE();
78 a8 := JTF_NUMBER_TABLE();
79 else
80 a0 := JTF_NUMBER_TABLE();
81 a1 := JTF_VARCHAR2_TABLE_100();
82 a2 := JTF_VARCHAR2_TABLE_100();
83 a3 := JTF_NUMBER_TABLE();
84 a4 := JTF_VARCHAR2_TABLE_100();
85 a5 := JTF_VARCHAR2_TABLE_100();
86 a6 := JTF_DATE_TABLE();
87 a7 := JTF_DATE_TABLE();
88 a8 := JTF_NUMBER_TABLE();
89 if t.count > 0 then
90 a0.extend(t.count);
91 a1.extend(t.count);
92 a2.extend(t.count);
93 a3.extend(t.count);
94 a4.extend(t.count);
95 a5.extend(t.count);
96 a6.extend(t.count);
97 a7.extend(t.count);
98 a8.extend(t.count);
99 ddindx := t.first;
100 indx := 1;
101 while true loop
102 a0(indx) := rosetta_g_miss_num_map(t(ddindx).related_node_id);
103 a1(indx) := t(ddindx).related_node_table_name;
104 a2(indx) := t(ddindx).related_node_object_type;
105 a3(indx) := rosetta_g_miss_num_map(t(ddindx).level_number);
106 a4(indx) := t(ddindx).top_parent_flag;
107 a5(indx) := t(ddindx).leaf_child_flag;
108 a6(indx) := t(ddindx).effective_start_date;
109 a7(indx) := t(ddindx).effective_end_date;
110 a8(indx) := rosetta_g_miss_num_map(t(ddindx).relationship_id);
111 indx := indx+1;
112 if t.last =ddindx
113 then exit;
114 end if;
115 ddindx := t.next(ddindx);
116 end loop;
117 end if;
118 end if;
119 end rosetta_table_copy_out_p1;
120
121 procedure is_top_parent_1(p_init_msg_list VARCHAR2
122 , p_hierarchy_type VARCHAR2
123 , p_parent_id NUMBER
124 , p_parent_table_name VARCHAR2
125 , p_parent_object_type VARCHAR2
126 , p_effective_date date
127 , x_result out nocopy VARCHAR2
128 , x_return_status out nocopy VARCHAR2
129 , x_msg_count out nocopy NUMBER
130 , x_msg_data out nocopy VARCHAR2
131 )
132 as
133 ddp_effective_date date;
134 ddindx binary_integer; indx binary_integer;
135 begin
136
137 -- copy data to the local IN or IN-OUT args, if any
138
139
140
141
142
143 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
144
145
146
147
148
149 -- here's the delegated call to the old PL/SQL routine
150 hz_hierarchy_v2pub.is_top_parent(p_init_msg_list,
151 p_hierarchy_type,
152 p_parent_id,
153 p_parent_table_name,
154 p_parent_object_type,
155 ddp_effective_date,
156 x_result,
157 x_return_status,
158 x_msg_count,
159 x_msg_data);
160
161 -- copy data back from the local OUT or IN-OUT args, if any
162
163
164
165
166
167
168
169
170
171 end;
172
173 procedure check_parent_child_2(p_init_msg_list VARCHAR2
174 , p_hierarchy_type VARCHAR2
175 , p_parent_id NUMBER
176 , p_parent_table_name VARCHAR2
177 , p_parent_object_type VARCHAR2
178 , p_child_id NUMBER
179 , p_child_table_name VARCHAR2
180 , p_child_object_type VARCHAR2
181 , p_effective_date date
182 , x_result out nocopy VARCHAR2
183 , x_level_number out nocopy NUMBER
184 , x_return_status out nocopy VARCHAR2
185 , x_msg_count out nocopy NUMBER
186 , x_msg_data out nocopy VARCHAR2
187 )
188 as
189 ddp_effective_date date;
190 ddindx binary_integer; indx binary_integer;
191 begin
192
193 -- copy data to the local IN or IN-OUT args, if any
194
195
196
197
198
199
200
201
202 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
203
204
205
206
207
208
209 -- here's the delegated call to the old PL/SQL routine
210 hz_hierarchy_v2pub.check_parent_child(p_init_msg_list,
211 p_hierarchy_type,
212 p_parent_id,
213 p_parent_table_name,
214 p_parent_object_type,
215 p_child_id,
216 p_child_table_name,
217 p_child_object_type,
218 ddp_effective_date,
219 x_result,
220 x_level_number,
221 x_return_status,
222 x_msg_count,
223 x_msg_data);
224
225 -- copy data back from the local OUT or IN-OUT args, if any
226
227
228
229
230
231
232
233
234
235
236
237
238
239 end;
240
241 procedure get_parent_nodes_3(p_init_msg_list VARCHAR2
242 , p_hierarchy_type VARCHAR2
243 , p_child_id NUMBER
244 , p_child_table_name VARCHAR2
245 , p_child_object_type VARCHAR2
246 , p_parent_table_name VARCHAR2
247 , p_parent_object_type VARCHAR2
248 , p_include_node VARCHAR2
249 , p_effective_date date
250 , p_no_of_records NUMBER
251 , p10_a0 out nocopy JTF_NUMBER_TABLE
252 , p10_a1 out nocopy JTF_VARCHAR2_TABLE_100
253 , p10_a2 out nocopy JTF_VARCHAR2_TABLE_100
254 , p10_a3 out nocopy JTF_NUMBER_TABLE
255 , p10_a4 out nocopy JTF_VARCHAR2_TABLE_100
256 , p10_a5 out nocopy JTF_VARCHAR2_TABLE_100
257 , p10_a6 out nocopy JTF_DATE_TABLE
258 , p10_a7 out nocopy JTF_DATE_TABLE
259 , p10_a8 out nocopy JTF_NUMBER_TABLE
260 , x_return_status out nocopy VARCHAR2
261 , x_msg_count out nocopy NUMBER
262 , x_msg_data out nocopy VARCHAR2
263 )
264 as
265 ddp_effective_date date;
266 ddx_related_nodes_list hz_hierarchy_v2pub.related_nodes_list_type;
267 ddindx binary_integer; indx binary_integer;
268 begin
269
270 -- copy data to the local IN or IN-OUT args, if any
271
272
273
274
275
276
277
278
279 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
280
281
282
283
284
285
286 -- here's the delegated call to the old PL/SQL routine
287 hz_hierarchy_v2pub.get_parent_nodes(p_init_msg_list,
288 p_hierarchy_type,
289 p_child_id,
290 p_child_table_name,
291 p_child_object_type,
292 p_parent_table_name,
293 p_parent_object_type,
294 p_include_node,
295 ddp_effective_date,
296 p_no_of_records,
297 ddx_related_nodes_list,
298 x_return_status,
299 x_msg_count,
300 x_msg_data);
301
302 -- copy data back from the local OUT or IN-OUT args, if any
303
304
305
306
307
308
309
310
311
312
313 hz_hierarchy_v2pub_jw.rosetta_table_copy_out_p1(ddx_related_nodes_list, p10_a0
314 , p10_a1
315 , p10_a2
316 , p10_a3
317 , p10_a4
318 , p10_a5
319 , p10_a6
320 , p10_a7
321 , p10_a8
322 );
323
324
325
326 end;
327
328 procedure get_child_nodes_4(p_init_msg_list VARCHAR2
329 , p_hierarchy_type VARCHAR2
330 , p_parent_id NUMBER
331 , p_parent_table_name VARCHAR2
332 , p_parent_object_type VARCHAR2
333 , p_child_table_name VARCHAR2
334 , p_child_object_type VARCHAR2
335 , p_include_node VARCHAR2
336 , p_effective_date date
337 , p_no_of_records NUMBER
338 , p10_a0 out nocopy JTF_NUMBER_TABLE
339 , p10_a1 out nocopy JTF_VARCHAR2_TABLE_100
340 , p10_a2 out nocopy JTF_VARCHAR2_TABLE_100
341 , p10_a3 out nocopy JTF_NUMBER_TABLE
342 , p10_a4 out nocopy JTF_VARCHAR2_TABLE_100
343 , p10_a5 out nocopy JTF_VARCHAR2_TABLE_100
344 , p10_a6 out nocopy JTF_DATE_TABLE
345 , p10_a7 out nocopy JTF_DATE_TABLE
346 , p10_a8 out nocopy JTF_NUMBER_TABLE
347 , x_return_status out nocopy VARCHAR2
348 , x_msg_count out nocopy NUMBER
349 , x_msg_data out nocopy VARCHAR2
350 )
351 as
352 ddp_effective_date date;
353 ddx_related_nodes_list hz_hierarchy_v2pub.related_nodes_list_type;
354 ddindx binary_integer; indx binary_integer;
355 begin
356
357 -- copy data to the local IN or IN-OUT args, if any
358
359
360
361
362
363
364
365
366 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
367
368
369
370
371
372
373 -- here's the delegated call to the old PL/SQL routine
374 hz_hierarchy_v2pub.get_child_nodes(p_init_msg_list,
375 p_hierarchy_type,
376 p_parent_id,
377 p_parent_table_name,
378 p_parent_object_type,
379 p_child_table_name,
380 p_child_object_type,
381 p_include_node,
382 ddp_effective_date,
383 p_no_of_records,
384 ddx_related_nodes_list,
385 x_return_status,
386 x_msg_count,
387 x_msg_data);
388
389 -- copy data back from the local OUT or IN-OUT args, if any
390
391
392
393
394
395
396
397
398
399
400 hz_hierarchy_v2pub_jw.rosetta_table_copy_out_p1(ddx_related_nodes_list, p10_a0
401 , p10_a1
402 , p10_a2
403 , p10_a3
404 , p10_a4
405 , p10_a5
406 , p10_a6
407 , p10_a7
408 , p10_a8
409 );
410
411
412
413 end;
414
415 procedure get_top_parent_nodes_5(p_init_msg_list VARCHAR2
416 , p_hierarchy_type VARCHAR2
417 , p_parent_table_name VARCHAR2
418 , p_parent_object_type VARCHAR2
419 , p_effective_date date
420 , p_no_of_records NUMBER
421 , p6_a0 out nocopy JTF_NUMBER_TABLE
422 , p6_a1 out nocopy JTF_VARCHAR2_TABLE_100
423 , p6_a2 out nocopy JTF_VARCHAR2_TABLE_100
424 , p6_a3 out nocopy JTF_NUMBER_TABLE
425 , p6_a4 out nocopy JTF_VARCHAR2_TABLE_100
426 , p6_a5 out nocopy JTF_VARCHAR2_TABLE_100
427 , p6_a6 out nocopy JTF_DATE_TABLE
428 , p6_a7 out nocopy JTF_DATE_TABLE
429 , p6_a8 out nocopy JTF_NUMBER_TABLE
430 , x_return_status out nocopy VARCHAR2
431 , x_msg_count out nocopy NUMBER
432 , x_msg_data out nocopy VARCHAR2
433 )
434 as
435 ddp_effective_date date;
436 ddx_top_parent_list hz_hierarchy_v2pub.related_nodes_list_type;
437 ddindx binary_integer; indx binary_integer;
438 begin
439
440 -- copy data to the local IN or IN-OUT args, if any
441
442
443
444
445 ddp_effective_date := rosetta_g_miss_date_in_map(p_effective_date);
446
447
448
449
450
451
452 -- here's the delegated call to the old PL/SQL routine
453 hz_hierarchy_v2pub.get_top_parent_nodes(p_init_msg_list,
454 p_hierarchy_type,
455 p_parent_table_name,
456 p_parent_object_type,
457 ddp_effective_date,
458 p_no_of_records,
459 ddx_top_parent_list,
460 x_return_status,
461 x_msg_count,
462 x_msg_data);
463
464 -- copy data back from the local OUT or IN-OUT args, if any
465
466
467
468
469
470
471 hz_hierarchy_v2pub_jw.rosetta_table_copy_out_p1(ddx_top_parent_list, p6_a0
472 , p6_a1
473 , p6_a2
474 , p6_a3
475 , p6_a4
476 , p6_a5
477 , p6_a6
478 , p6_a7
479 , p6_a8
480 );
481
482
483
484 end;
485
486 end hz_hierarchy_v2pub_jw;