[Home] [Help]
PACKAGE BODY: APPS.HZ_HIERARCHY_V2PUB
Source
1 PACKAGE BODY HZ_HIERARCHY_V2PUB AS
2 /*$Header: ARH2HISB.pls 120.3 2005/10/28 13:45:36 vravicha noship $ */
3
4 -----------------------------------------
5 -----------------------------------------
6 -- declaration of private global varibles
7 -----------------------------------------
8 -----------------------------------------
9
10 --G_DEBUG BOOLEAN := FALSE;
11
12 --------------------------------------------------
13 --------------------------------------------------
14 -- declaration of private procedures and functions
15 --------------------------------------------------
16 --------------------------------------------------
17
18 /*PROCEDURE enable_debug;
19
20 PROCEDURE disable_debug;
21 */
22
23 PROCEDURE validate_input(
24 p_hierarchy_type IN VARCHAR2,
25 p_parent_id IN NUMBER := NULL,
26 p_parent_table_name IN VARCHAR2 := NULL,
27 p_parent_object_type IN VARCHAR2 := NULL,
28 p_child_id IN NUMBER := NULL,
29 p_child_table_name IN VARCHAR2 := NULL,
30 p_child_object_type IN VARCHAR2 := NULL,
31 p_no_of_records IN NUMBER := NULL,
32 x_return_status IN OUT NOCOPY VARCHAR2
33 );
34
35 -----------------------------------
36 -----------------------------------
37 -- private procedures and functions
38 -----------------------------------
39 -----------------------------------
40
41 /**
42 * PRIVATE PROCEDURE enable_debug
43 *
44 * DESCRIPTION
45 * Turn on debug mode.
46 *
47 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
48 * HZ_UTILITY_V2PUB.enable_debug
49 *
50 * MODIFICATION HISTORY
51 *
52 * 31-Oct-2001 Anupam Bordia o Created.
53 *
54 */
55
56 /*PROCEDURE enable_debug IS
57
58 BEGIN
59
60 IF FND_PROFILE.value( 'HZ_API_FILE_DEBUG_ON' ) = 'Y' OR
61 FND_PROFILE.value( 'HZ_API_DBMS_DEBUG_ON' ) = 'Y'
62 THEN
63 HZ_UTILITY_V2PUB.enable_debug;
64 G_DEBUG := TRUE;
65 END IF;
66
67 END enable_debug;
68 */
69
70 /**
71 * PRIVATE PROCEDURE disable_debug
72 *
73 * DESCRIPTION
74 * Turn off debug mode.
75 *
76 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
77 * HZ_UTILITY_V2PUB.disable_debug
78 *
79 * MODIFICATION HISTORY
80 *
81 * 31-Oct-2001 Anupam Bordia o Created.
82 *
83 */
84
85 /*PROCEDURE disable_debug IS
86
87 BEGIN
88
89 IF G_DEBUG THEN
90 HZ_UTILITY_V2PUB.disable_debug;
91 G_DEBUG := FALSE;
92 END IF;
93
94 END disable_debug;
95 */
96
97 /**
98 * PRIVATE PROCEDURE validate_input
99 *
100 * DESCRIPTION
101 * Validates the input to different procedures in this API.
102 *
103 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
104 *
105 *
106 * MODIFICATION HISTORY
107 *
108 * 31-Oct-2001 Anupam Bordia o Created.
109 *
110 */
111
112 PROCEDURE validate_input(
113 p_hierarchy_type IN VARCHAR2,
114 p_parent_id IN NUMBER := NULL,
115 p_parent_table_name IN VARCHAR2 := NULL,
116 p_parent_object_type IN VARCHAR2 := NULL,
117 p_child_id IN NUMBER := NULL,
118 p_child_table_name IN VARCHAR2 := NULL,
119 p_child_object_type IN VARCHAR2 := NULL,
120 p_no_of_records IN NUMBER := NULL,
121 x_return_status IN OUT NOCOPY VARCHAR2
122 ) IS
123
124 l_dummy VARCHAR2(30);
125 l_debug_prefix VARCHAR2(30) := '';
126
127 BEGIN
128
129 --------------------------
130 -- validate hierarchy type
131 --------------------------
132
133 BEGIN
134 SELECT HIERARCHICAL_FLAG INTO l_dummy
135 FROM HZ_RELATIONSHIP_TYPES
136 WHERE RELATIONSHIP_TYPE = p_hierarchy_type
137 AND ROWNUM = 1;
138
139 IF l_dummy <> 'Y' THEN
140 -- relationship type is not hierarchical, give error
141 FND_MESSAGE.SET_NAME('AR', 'HZ_NON_HIER_REL_TYPE');
142 FND_MSG_PUB.ADD;
143 x_return_status := fnd_api.g_ret_sts_error;
144 END IF;
145
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 -- no relationship type record found, so error out NOCOPY
149 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
150 FND_MESSAGE.SET_TOKEN('FK', 'hierarchy_type');
151 FND_MESSAGE.SET_TOKEN('COLUMN', 'relationship_type');
152 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_relationship_types');
153 FND_MSG_PUB.ADD;
154 x_return_status := fnd_api.g_ret_sts_error;
155 END;
156
157 -----------------------------
158 -- validate parent_table_name
159 -----------------------------
160 -- subject_table_name has foreign key fnd_objects.obj_name
161 IF p_parent_table_name IS NOT NULL THEN
162 BEGIN
163 SELECT 'Y'
164 INTO l_dummy
165 FROM fnd_objects fo
166 WHERE fo.obj_name = p_parent_table_name;
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
170 fnd_message.set_token('FK', 'parent_table_name');
171 fnd_message.set_token('COLUMN', 'obj_name');
172 fnd_message.set_token('TABLE', 'fnd_objects');
173 fnd_msg_pub.add;
174 x_return_status := fnd_api.g_ret_sts_error;
175 END;
176 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
177 hz_utility_v2pub.debug(p_message=>'parent_table_name has foreign key fnd_objects.obj_name. ' ||
178 'x_return_status = ' || x_return_status,
179 p_prefix =>l_debug_prefix,
180 p_msg_level=>fnd_log.level_statement);
181 END IF;
182 END IF;
183
184 ------------------------------
185 -- validate parent_object_type
186 ------------------------------
187
188 -- parent_object_type has foreign key fnd_object_instance_sets.instance_set_name
189 IF p_parent_object_type IS NOT NULL
190 THEN
191 BEGIN
192 SELECT 'Y'
193 INTO l_dummy
194 FROM FND_OBJECT_INSTANCE_SETS
195 WHERE INSTANCE_SET_NAME = p_parent_object_type;
196 EXCEPTION
197 WHEN NO_DATA_FOUND THEN
198 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
199 fnd_message.set_token('FK', 'parent_object_type');
200 fnd_message.set_token('COLUMN', 'instance_set_name');
201 fnd_message.set_token('TABLE', 'fnd_object_instance_sets');
202 fnd_msg_pub.add;
203 x_return_status := fnd_api.g_ret_sts_error;
204 END;
205
206 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
207 hz_utility_v2pub.debug(p_message=>'parent_object_type has foreign key fnd_object_instance_sets.instance_set_name. ' ||
208 'x_return_status = ' || x_return_status,
209 p_prefix =>l_debug_prefix,
210 p_msg_level=>fnd_log.level_statement);
211 END IF;
212
213 END IF;
214
215 -----------------------------
216 -- validate child_table_name
217 -----------------------------
218 -- subject_table_name has foreign key fnd_objects.obj_name
219 IF p_child_table_name IS NOT NULL THEN
220 BEGIN
221 SELECT 'Y'
222 INTO l_dummy
223 FROM fnd_objects fo
224 WHERE fo.obj_name = p_child_table_name;
225 EXCEPTION
226 WHEN NO_DATA_FOUND THEN
227 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
228 fnd_message.set_token('FK', 'child_table_name');
229 fnd_message.set_token('COLUMN', 'obj_name');
230 fnd_message.set_token('TABLE', 'fnd_objects');
231 fnd_msg_pub.add;
232 x_return_status := fnd_api.g_ret_sts_error;
233 END;
234
235 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
236 hz_utility_v2pub.debug(p_message=>'child_table_name has foreign key fnd_objects.obj_name. ' ||
237 'x_return_status = ' || x_return_status,
238 p_prefix =>l_debug_prefix,
239 p_msg_level=>fnd_log.level_statement);
240 END IF;
241 END IF;
242
243 ------------------------------
244 -- validate child_object_type
245 ------------------------------
246
247 -- child_object_type has foreign key fnd_object_instance_sets.instance_set_name
248 IF p_child_object_type IS NOT NULL
249 THEN
250 BEGIN
251 SELECT 'Y'
252 INTO l_dummy
253 FROM FND_OBJECT_INSTANCE_SETS
254 WHERE INSTANCE_SET_NAME = p_child_object_type;
255 EXCEPTION
256 WHEN NO_DATA_FOUND THEN
257 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
258 fnd_message.set_token('FK', 'child_object_type');
259 fnd_message.set_token('COLUMN', 'instance_set_name');
260 fnd_message.set_token('TABLE', 'fnd_object_instance_sets');
261 fnd_msg_pub.add;
262 x_return_status := fnd_api.g_ret_sts_error;
263 END;
264 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
265 hz_utility_v2pub.debug(p_message=>'child_object_type has foreign key fnd_object_instance_sets.instance_set_name. ' ||
266 'x_return_status = ' || x_return_status,
267 p_prefix =>l_debug_prefix,
268 p_msg_level=>fnd_log.level_statement);
269 END IF;
270
271 END IF;
272
273 ---------------------------
274 -- validate p_no_of_records
275 ---------------------------
276
277 IF p_no_of_records > 100 OR
278 p_no_of_records < 1 OR
279 p_no_of_records IS NULL
280 THEN
281 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VALUE_BETWEEN' );
282 FND_MESSAGE.SET_TOKEN( 'COLUMN','p_no_of_records');
283 FND_MESSAGE.SET_TOKEN( 'VALUE1', '1' );
284 FND_MESSAGE.SET_TOKEN( 'VALUE2', '100' );
285 FND_MSG_PUB.ADD;
286 x_return_status := FND_API.G_RET_STS_ERROR;
287
288 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
289 hz_utility_v2pub.debug(p_message=>'p_no_of_records should be between 1 and 100 .' ||
290 'x_return_status = ' || x_return_status,
291 p_prefix =>l_debug_prefix,
292 p_msg_level=>fnd_log.level_statement);
293 END IF;
294 END IF;
295
296
297
298 END validate_input;
299
300 ----------------------------------
301 ----------------------------------
302 -- Public procedures and functions
303 ----------------------------------
304 ----------------------------------
305
306 PROCEDURE is_top_parent(
307 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
308 p_hierarchy_type IN VARCHAR2,
309 p_parent_id IN NUMBER,
310 p_parent_table_name IN VARCHAR2 := 'HZ_PARTIES',
311 p_parent_object_type IN VARCHAR2 := 'ORGANIZATION',
312 p_effective_date IN DATE := SYSDATE,
313 x_result OUT NOCOPY VARCHAR2,
314 x_return_status OUT NOCOPY VARCHAR2,
315 x_msg_count OUT NOCOPY NUMBER,
316 x_msg_data OUT NOCOPY VARCHAR2
317 )
318 IS
319
320 l_result VARCHAR2(1) := 'N';
321 l_incl_unrelated VARCHAR2(1);
322 l_debug_prefix VARCHAR2(30) := '';
323
324 CURSOR c_top_in_hierarchy IS
325 SELECT TOP_PARENT_FLAG
326 FROM HZ_HIERARCHY_NODES
327 WHERE PARENT_ID = p_parent_id
328 AND PARENT_TABLE_NAME = p_parent_table_name
329 AND PARENT_OBJECT_TYPE = p_parent_object_type
330 AND HIERARCHY_TYPE = p_hierarchy_type
331 AND p_effective_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
332 AND LEVEL_NUMBER = 0;
333
334
335 BEGIN
336
337 -- Check if API is called in debug mode. If yes, enable debug.
338 --enable_debug;
339
340 -- Debug info.
341 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
342 hz_utility_v2pub.debug(p_message=>'is_top_parent (+)',
343 p_prefix=>l_debug_prefix,
344 p_msg_level=>fnd_log.level_procedure);
345 END IF;
346
350 END IF;
347 --Initialize message list if p_init_msg_list is set to TRUE.
348 IF FND_API.to_Boolean(p_init_msg_list) THEN
349 FND_MSG_PUB.initialize;
351
352 --Initialize API return status to success.
353 x_return_status := FND_API.G_RET_STS_SUCCESS;
354
355 validate_input (
356 p_hierarchy_type => p_hierarchy_type,
357 p_parent_table_name => p_parent_table_name,
358 p_parent_object_type => p_parent_object_type,
359 p_no_of_records => 1,
360 x_return_status => x_return_status
361 );
362
363 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
364 RAISE FND_API.G_EXC_ERROR;
365 END IF;
366
367 -- check whether it is a top parent in the given hierarchy
368 OPEN c_top_in_hierarchy;
369 FETCH c_top_in_hierarchy INTO l_result;
370 CLOSE c_top_in_hierarchy;
371
372 -- if the parent is not a top parent in hierarchy, it can still be returned
373 -- provided hierarchy_type is set up with incl_unrelated_entities = 'Y' and
374 -- the parent is a valid entity of the subject type
375
376 IF l_result = 'N' THEN
377 l_incl_unrelated := HZ_UTILITY_V2PUB.incl_unrelated_entities(p_hierarchy_type);
378 IF l_incl_unrelated = 'Y' THEN
379 -- include the entity if it is valid
380 l_result := HZ_RELATIONSHIP_TYPE_V2PUB.in_instance_sets
381 (p_parent_object_type,
382 p_parent_id);
383 END IF;
384 END IF;
385
386 -- return the result
387 x_result := l_result;
388
389 --Standard call to get message count and if count is 1, get message info.
390 FND_MSG_PUB.Count_And_Get(
391 p_encoded => FND_API.G_FALSE,
392 p_count => x_msg_count,
393 p_data => x_msg_data );
394
395 -- Debug info.
396 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
397 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
398 p_msg_data=>x_msg_data,
399 p_msg_type=>'WARNING',
400 p_msg_level=>fnd_log.level_exception);
401 END IF;
402 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
403 hz_utility_v2pub.debug(p_message=>'is_top_parent (-)',
404 p_prefix=>l_debug_prefix,
405 p_msg_level=>fnd_log.level_procedure);
406 END IF;
407
408 -- Check if API is called in debug mode. If yes, disable debug.
409 --disable_debug;
410
411 EXCEPTION
412 WHEN FND_API.G_EXC_ERROR THEN
413 x_return_status := FND_API.G_RET_STS_ERROR;
414 x_result := 'N';
415
416 FND_MSG_PUB.Count_And_Get(
417 p_encoded => FND_API.G_FALSE,
418 p_count => x_msg_count,
419 p_data => x_msg_data );
420
421 -- Debug info.
422 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
423 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
424 p_msg_data=>x_msg_data,
425 p_msg_type=>'ERROR',
426 p_msg_level=>fnd_log.level_error);
427 END IF;
428 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
429 hz_utility_v2pub.debug(p_message=>'is_top_parent (-)',
430 p_prefix=>l_debug_prefix,
431 p_msg_level=>fnd_log.level_procedure);
432 END IF;
433
434 -- Check if API is called in debug mode. If yes, disable debug.
435 --disable_debug;
436
437 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
439 x_result := 'N';
440
441 FND_MSG_PUB.Count_And_Get(
442 p_encoded => FND_API.G_FALSE,
443 p_count => x_msg_count,
444 p_data => x_msg_data );
445
446 -- Debug info.
447 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
448 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
449 p_msg_data=>x_msg_data,
450 p_msg_type=>'UNEXPECTED ERROR',
451 p_msg_level=>fnd_log.level_error);
452 END IF;
453 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
454 hz_utility_v2pub.debug(p_message=>'is_top_parent (-)',
455 p_prefix=>l_debug_prefix,
456 p_msg_level=>fnd_log.level_procedure);
457 END IF;
458
459 -- Check if API is called in debug mode. If yes, disable debug.
460 --disable_debug;
461
462 WHEN OTHERS THEN
463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
464 x_result := 'N';
465
466 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
467 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
468 FND_MSG_PUB.ADD;
469
470 FND_MSG_PUB.Count_And_Get(
471 p_encoded => FND_API.G_FALSE,
472 p_count => x_msg_count,
473 p_data => x_msg_data );
474
475 -- Debug info.
476 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
477 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
478 p_msg_data=>x_msg_data,
479 p_msg_type=>'SQL ERROR',
483 hz_utility_v2pub.debug(p_message=>'is_top_parent (-)',
480 p_msg_level=>fnd_log.level_error);
481 END IF;
482 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
484 p_prefix=>l_debug_prefix,
485 p_msg_level=>fnd_log.level_procedure);
486 END IF;
487
488 -- Check if API is called in debug mode. If yes, disable debug.
489 --disable_debug;
490
491 END is_top_parent;
492
493
494 PROCEDURE check_parent_child(
495 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
496 p_hierarchy_type IN VARCHAR2,
497 p_parent_id IN NUMBER,
498 p_parent_table_name IN VARCHAR2 := 'HZ_PARTIES',
499 p_parent_object_type IN VARCHAR2 := 'ORGANIZATION',
500 p_child_id IN NUMBER,
501 p_child_table_name IN VARCHAR2 := 'HZ_PARTIES',
502 p_child_object_type IN VARCHAR2 := 'ORGANIZATION',
503 p_effective_date IN DATE := SYSDATE,
504 x_result OUT NOCOPY VARCHAR2,
505 x_level_number OUT NOCOPY NUMBER,
506 x_return_status OUT NOCOPY VARCHAR2,
507 x_msg_count OUT NOCOPY NUMBER,
508 x_msg_data OUT NOCOPY VARCHAR2
509 ) IS
510
511 l_result VARCHAR2(1) := 'N';
512 l_level_number NUMBER := -1;
513 l_debug_prefix VARCHAR2(30) := '';
514
515 BEGIN
516
517 -- Check if API is called in debug mode. If yes, enable debug.
518 --enable_debug;
519
520 -- Debug info.
521 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
522 hz_utility_v2pub.debug(p_message=>'check_parent_child (+)',
523 p_prefix=>l_debug_prefix,
524 p_msg_level=>fnd_log.level_procedure);
525 END IF;
526
527 --Initialize message list if p_init_msg_list is set to TRUE.
528 IF FND_API.to_Boolean(p_init_msg_list) THEN
529 FND_MSG_PUB.initialize;
530 END IF;
531
532 --Initialize API return status to success.
533 x_return_status := FND_API.G_RET_STS_SUCCESS;
534
535 validate_input (
536 p_hierarchy_type => p_hierarchy_type,
537 p_parent_table_name => p_parent_table_name,
538 p_parent_object_type => p_parent_object_type,
539 p_child_table_name => p_child_table_name,
540 p_child_object_type => p_child_object_type,
541 p_no_of_records => 1,
542 x_return_status => x_return_status
543 );
544
545 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
546 RAISE FND_API.G_EXC_ERROR;
547 END IF;
548
549 BEGIN
550 SELECT 'Y', LEVEL_NUMBER
551 INTO l_result, l_level_number
552 FROM HZ_HIERARCHY_NODES
553 WHERE PARENT_ID = p_parent_id
554 AND PARENT_TABLE_NAME = p_parent_table_name
555 AND PARENT_OBJECT_TYPE = p_parent_object_type
556 AND HIERARCHY_TYPE = p_hierarchy_type
557 AND CHILD_ID = p_child_id
558 AND CHILD_TABLE_NAME = p_child_table_name
559 AND CHILD_OBJECT_TYPE = p_child_object_type
560 AND p_effective_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
561 AND LEVEL_NUMBER > 0
562 AND ROWNUM = 1;
563 EXCEPTION
564 WHEN NO_DATA_FOUND THEN
565 x_result := 'N';
566 x_level_number := -1;
567 END;
568
569 IF l_result = 'Y' THEN
570 x_result := 'Y';
571 x_level_number := l_level_number;
572 ELSE
573 x_result := 'N';
574 x_level_number := -1;
575 END IF;
576
577 --Standard call to get message count and if count is 1, get message info.
578 FND_MSG_PUB.Count_And_Get(
579 p_encoded => FND_API.G_FALSE,
580 p_count => x_msg_count,
581 p_data => x_msg_data );
582
583 -- Debug info.
584 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
585 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
586 p_msg_data=>x_msg_data,
587 p_msg_type=>'WARNING',
588 p_msg_level=>fnd_log.level_exception);
589 END IF;
590 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
591 hz_utility_v2pub.debug(p_message=>'check_parent_child (-)',
592 p_prefix=>l_debug_prefix,
593 p_msg_level=>fnd_log.level_procedure);
594 END IF;
595
596 -- Check if API is called in debug mode. If yes, disable debug.
597 --disable_debug;
598
599 EXCEPTION
600 WHEN FND_API.G_EXC_ERROR THEN
601 x_return_status := FND_API.G_RET_STS_ERROR;
602 x_result := 'N';
603 x_level_number := -1;
604
605 FND_MSG_PUB.Count_And_Get(
606 p_encoded => FND_API.G_FALSE,
607 p_count => x_msg_count,
608 p_data => x_msg_data );
609
613 p_msg_data=>x_msg_data,
610 -- Debug info.
611 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
612 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
614 p_msg_type=>'ERROR',
615 p_msg_level=>fnd_log.level_error);
616 END IF;
617 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
618 hz_utility_v2pub.debug(p_message=>'check_parent_child (-)',
619 p_prefix=>l_debug_prefix,
620 p_msg_level=>fnd_log.level_procedure);
621 END IF;
622
623 -- Check if API is called in debug mode. If yes, disable debug.
624 --disable_debug;
625
626 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
628 x_result := 'N';
629 x_level_number := -1;
630
631 FND_MSG_PUB.Count_And_Get(
632 p_encoded => FND_API.G_FALSE,
633 p_count => x_msg_count,
634 p_data => x_msg_data );
635
636 -- Debug info.
637 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
638 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
639 p_msg_data=>x_msg_data,
640 p_msg_type=>'UNEXPECTED ERROR',
641 p_msg_level=>fnd_log.level_error);
642 END IF;
643 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
644 hz_utility_v2pub.debug(p_message=>'check_parent_child (-)',
645 p_prefix=>l_debug_prefix,
646 p_msg_level=>fnd_log.level_procedure);
647 END IF;
648
649 -- Check if API is called in debug mode. If yes, disable debug.
650 --disable_debug;
651
652 WHEN OTHERS THEN
653 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
654 x_result := 'N';
655 x_level_number := -1;
656
657 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
658 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
659 FND_MSG_PUB.ADD;
660
661 FND_MSG_PUB.Count_And_Get(
662 p_encoded => FND_API.G_FALSE,
663 p_count => x_msg_count,
664 p_data => x_msg_data );
665
666 -- Debug info.
667 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
668 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
669 p_msg_data=>x_msg_data,
670 p_msg_type=>'SQL ERROR',
671 p_msg_level=>fnd_log.level_error);
672 END IF;
673 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
674 hz_utility_v2pub.debug(p_message=>'check_parent_child (-)',
675 p_prefix=>l_debug_prefix,
676 p_msg_level=>fnd_log.level_procedure);
677 END IF;
678
679 -- Check if API is called in debug mode. If yes, disable debug.
680 --disable_debug;
681
682 END check_parent_child;
683
684 PROCEDURE get_parent_nodes(
685 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
686 p_hierarchy_type IN VARCHAR2,
687 p_child_id IN NUMBER,
688 p_child_table_name IN VARCHAR2,
689 p_child_object_type IN VARCHAR2,
690 p_parent_table_name IN VARCHAR2,
691 p_parent_object_type IN VARCHAR2,
692 p_include_node IN VARCHAR2 := 'Y',
693 p_effective_date IN DATE := SYSDATE,
694 p_no_of_records IN NUMBER := 100,
695 x_related_nodes_list OUT NOCOPY RELATED_NODES_LIST_TYPE,
696 x_return_status OUT NOCOPY VARCHAR2,
697 x_msg_count OUT NOCOPY NUMBER,
698 x_msg_data OUT NOCOPY VARCHAR2
699 ) IS
700
701 TYPE parent_id_type IS TABLE OF hz_hierarchy_nodes.parent_id%TYPE;
702 TYPE parent_table_name_type IS TABLE OF hz_hierarchy_nodes.parent_table_name%TYPE;
703 TYPE parent_object_type_type IS TABLE OF hz_hierarchy_nodes.parent_object_type%TYPE;
704 TYPE level_number_type IS TABLE OF hz_hierarchy_nodes.level_number%TYPE;
705 TYPE top_parent_flag_type IS TABLE OF hz_hierarchy_nodes.top_parent_flag%TYPE;
706 TYPE leaf_child_flag_type IS TABLE OF hz_hierarchy_nodes.leaf_child_flag%TYPE;
707 TYPE effective_start_date_type IS TABLE OF hz_hierarchy_nodes.effective_start_date%TYPE;
708 TYPE effective_end_date_type IS TABLE OF hz_hierarchy_nodes.effective_end_date%TYPE;
709 TYPE relationship_id_type IS TABLE OF hz_hierarchy_nodes.relationship_id%TYPE;
710
711 parent_id_list parent_id_type;
712 parent_table_name_list parent_table_name_type;
713 parent_object_type_list parent_object_type_type;
714 level_number_list level_number_type;
715 top_parent_flag_list top_parent_flag_type;
716 leaf_child_flag_list leaf_child_flag_type;
717 effective_start_date_list effective_start_date_type;
718 effective_end_date_list effective_end_date_type;
722 -- cursor to get all the parent nodes (optionally including
719 relationship_id_list relationship_id_type;
720 l_debug_prefix VARCHAR2(30) := '';
721
723 -- the self node as well) for the given hierarchy
724 CURSOR c1 is
725 SELECT PARENT_ID,
726 PARENT_TABLE_NAME,
727 PARENT_OBJECT_TYPE,
728 LEVEL_NUMBER,
729 TOP_PARENT_FLAG,
730 LEAF_CHILD_FLAG,
731 EFFECTIVE_START_DATE,
732 EFFECTIVE_END_DATE,
733 RELATIONSHIP_ID
734 FROM HZ_HIERARCHY_NODES
735 WHERE CHILD_ID = p_child_id
736 AND CHILD_TABLE_NAME = p_child_table_name
737 AND CHILD_OBJECT_TYPE = p_child_object_type
738 AND PARENT_TABLE_NAME LIKE p_parent_table_name||'%'
739 AND PARENT_OBJECT_TYPE LIKE p_parent_object_type||'%'
740 AND HIERARCHY_TYPE = p_hierarchy_type
741 AND p_effective_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
742 AND LEVEL_NUMBER <> DECODE(p_include_node, 'N', 0, 'Y', -1, NULL, -1);
743
744 BEGIN
745
746 -- Check if API is called in debug mode. If yes, enable debug.
747 --enable_debug;
748
749 -- Debug info.
750 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
751 hz_utility_v2pub.debug(p_message=>'get_parent_nodes (+)',
752 p_prefix=>l_debug_prefix,
753 p_msg_level=>fnd_log.level_procedure);
754 END IF;
755
756 --Initialize message list if p_init_msg_list is set to TRUE.
757 IF FND_API.to_Boolean(p_init_msg_list) THEN
758 FND_MSG_PUB.initialize;
759 END IF;
760
761 --Initialize API return status to success.
762 x_return_status := FND_API.G_RET_STS_SUCCESS;
763
764 HZ_UTILITY_V2PUB.validate_mandatory (
765 p_create_update_flag => 'C',
766 p_column => 'p_child_id',
767 p_column_value => p_child_id,
768 x_return_status => x_return_status);
769
770 HZ_UTILITY_V2PUB.validate_mandatory (
771 p_create_update_flag => 'C',
772 p_column => 'p_child_table_name',
773 p_column_value => p_child_table_name,
774 x_return_status => x_return_status);
775
776 HZ_UTILITY_V2PUB.validate_mandatory (
777 p_create_update_flag => 'C',
778 p_column => 'p_child_object_type',
779 p_column_value => p_child_object_type,
780 x_return_status => x_return_status);
781
782 validate_input (
783 p_hierarchy_type => p_hierarchy_type,
784 p_child_table_name => p_child_table_name,
785 p_child_object_type => p_child_object_type,
786 p_parent_table_name => p_parent_table_name,
787 p_parent_object_type => p_parent_object_type,
788 p_no_of_records => p_no_of_records,
789 x_return_status => x_return_status
790 );
791
792 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
793 RAISE FND_API.G_EXC_ERROR;
794 END IF;
795
796 OPEN c1;
797
798 -- do a bulk fetch of the parent records
799 FETCH c1 BULK COLLECT INTO
800 parent_id_list,
801 parent_table_name_list,
802 parent_object_type_list,
803 level_number_list,
804 top_parent_flag_list,
805 leaf_child_flag_list,
806 effective_start_date_list,
807 effective_end_date_list,
808 relationship_id_list LIMIT p_no_of_records;
809
810 FOR i IN 1..parent_id_list.COUNT LOOP
811 x_related_nodes_list(i).related_node_id := parent_id_list(i);
812 x_related_nodes_list(i).related_node_table_name := parent_table_name_list(i);
813 x_related_nodes_list(i).related_node_object_type := parent_object_type_list(i);
814 x_related_nodes_list(i).level_number := level_number_list(i);
815 x_related_nodes_list(i).top_parent_flag := top_parent_flag_list(i);
816 x_related_nodes_list(i).leaf_child_flag := leaf_child_flag_list(i);
817 x_related_nodes_list(i).effective_start_date := effective_start_date_list(i);
818 x_related_nodes_list(i).effective_end_date := effective_end_date_list(i);
819 x_related_nodes_list(i).relationship_id := relationship_id_list(i);
820 END LOOP;
821
822 CLOSE c1;
823
824 --Standard call to get message count and if count is 1, get message info.
825 FND_MSG_PUB.Count_And_Get(
826 p_encoded => FND_API.G_FALSE,
827 p_count => x_msg_count,
828 p_data => x_msg_data );
829
830 -- Debug info.
831 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
832 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
833 p_msg_data=>x_msg_data,
834 p_msg_type=>'WARNING',
835 p_msg_level=>fnd_log.level_exception);
836 END IF;
837 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
838 hz_utility_v2pub.debug(p_message=>'get_parent_nodes (-)',
839 p_prefix=>l_debug_prefix,
840 p_msg_level=>fnd_log.level_procedure);
841 END IF;
842
846 EXCEPTION
843 -- Check if API is called in debug mode. If yes, disable debug.
844 --disable_debug;
845
847 WHEN FND_API.G_EXC_ERROR THEN
848 x_return_status := FND_API.G_RET_STS_ERROR;
849
850 FND_MSG_PUB.Count_And_Get(
851 p_encoded => FND_API.G_FALSE,
852 p_count => x_msg_count,
853 p_data => x_msg_data );
854
855 -- Debug info.
856 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
857 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
858 p_msg_data=>x_msg_data,
859 p_msg_type=>'ERROR',
860 p_msg_level=>fnd_log.level_error);
861 END IF;
862 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
863 hz_utility_v2pub.debug(p_message=>'get_parent_nodes (-)',
864 p_prefix=>l_debug_prefix,
865 p_msg_level=>fnd_log.level_procedure);
866 END IF;
867
868 -- Check if API is called in debug mode. If yes, disable debug.
869 --disable_debug;
870
871 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
873
874 FND_MSG_PUB.Count_And_Get(
875 p_encoded => FND_API.G_FALSE,
876 p_count => x_msg_count,
877 p_data => x_msg_data );
878
879 -- Debug info.
880 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
881 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
882 p_msg_data=>x_msg_data,
883 p_msg_type=>'UNEXPECTED ERROR',
884 p_msg_level=>fnd_log.level_error);
885 END IF;
886 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
887 hz_utility_v2pub.debug(p_message=> 'get_parent_nodes (-)' ,
888 p_prefix=>l_debug_prefix,
889 p_msg_level=>fnd_log.level_procedure);
890 END IF;
891
892 -- Check if API is called in debug mode. If yes, disable debug.
893 --disable_debug;
894
895 WHEN OTHERS THEN
896 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
897
898 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
899 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
900 FND_MSG_PUB.ADD;
901
902 FND_MSG_PUB.Count_And_Get(
903 p_encoded => FND_API.G_FALSE,
904 p_count => x_msg_count,
905 p_data => x_msg_data );
906
907 -- Debug info.
908 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
909 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
910 p_msg_data=>x_msg_data,
911 p_msg_type=>'SQL ERROR',
912 p_msg_level=>fnd_log.level_error);
913 END IF;
914 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
915 hz_utility_v2pub.debug(p_message=>'get_parent_nodes (-)',
916 p_prefix=>l_debug_prefix,
917 p_msg_level=>fnd_log.level_procedure);
918 END IF;
919
920 -- Check if API is called in debug mode. If yes, disable debug.
921 --disable_debug;
922
923 END get_parent_nodes;
924
925
926 PROCEDURE get_child_nodes(
927 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
928 p_hierarchy_type IN VARCHAR2,
929 p_parent_id IN NUMBER,
930 p_parent_table_name IN VARCHAR2,
931 p_parent_object_type IN VARCHAR2,
932 p_child_table_name IN VARCHAR2,
933 p_child_object_type IN VARCHAR2,
934 p_include_node IN VARCHAR2 := 'Y',
935 p_effective_date IN DATE := SYSDATE,
936 p_no_of_records IN NUMBER := 100,
937 x_related_nodes_list OUT NOCOPY RELATED_NODES_LIST_TYPE,
938 x_return_status OUT NOCOPY VARCHAR2,
939 x_msg_count OUT NOCOPY NUMBER,
940 x_msg_data OUT NOCOPY VARCHAR2
941 ) IS
942
943
944 TYPE child_id_type IS TABLE OF hz_hierarchy_nodes.child_id%TYPE;
945 TYPE child_table_name_type IS TABLE OF hz_hierarchy_nodes.child_table_name%TYPE;
946 TYPE child_object_type_type IS TABLE OF hz_hierarchy_nodes.child_object_type%TYPE;
947 TYPE level_number_type IS TABLE OF hz_hierarchy_nodes.level_number%TYPE;
948 TYPE top_parent_flag_type IS TABLE OF hz_hierarchy_nodes.top_parent_flag%TYPE;
949 TYPE leaf_child_flag_type IS TABLE OF hz_hierarchy_nodes.leaf_child_flag%TYPE;
950 TYPE effective_start_date_type IS TABLE OF hz_hierarchy_nodes.effective_start_date%TYPE;
951 TYPE effective_end_date_type IS TABLE OF hz_hierarchy_nodes.effective_end_date%TYPE;
952 TYPE relationship_id_type IS TABLE OF hz_hierarchy_nodes.relationship_id%TYPE;
953
954 child_id_list child_id_type;
955 child_table_name_list child_table_name_type;
956 child_object_type_list child_object_type_type;
957 level_number_list level_number_type;
961 effective_end_date_list effective_end_date_type;
958 top_parent_flag_list top_parent_flag_type;
959 leaf_child_flag_list leaf_child_flag_type;
960 effective_start_date_list effective_start_date_type;
962 relationship_id_list relationship_id_type;
963 l_debug_prefix VARCHAR2(30) := '';
964
965 CURSOR c1 IS
966 SELECT CHILD_ID,
967 CHILD_TABLE_NAME,
968 CHILD_OBJECT_TYPE,
969 LEVEL_NUMBER,
970 TOP_PARENT_FLAG,
971 LEAF_CHILD_FLAG,
972 EFFECTIVE_START_DATE,
973 EFFECTIVE_END_DATE,
974 RELATIONSHIP_ID
975 FROM HZ_HIERARCHY_NODES
976 WHERE PARENT_ID = p_parent_id
977 AND PARENT_TABLE_NAME = p_parent_table_name
978 AND PARENT_OBJECT_TYPE = p_parent_object_type
979 AND CHILD_TABLE_NAME LIKE p_child_table_name||'%'
980 AND CHILD_OBJECT_TYPE LIKE p_child_object_type||'%'
981 AND HIERARCHY_TYPE = p_hierarchy_type
982 AND p_effective_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
983 AND LEVEL_NUMBER <> DECODE(p_include_node, 'N', 0, 'Y', -1, NULL, -1);
984
985 BEGIN
986
987 -- Check if API is called in debug mode. If yes, enable debug.
988 --enable_debug;
989
990 -- Debug info.
991 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
992 hz_utility_v2pub.debug(p_message=>'get_child_nodes (+)',
993 p_prefix=>l_debug_prefix,
994 p_msg_level=>fnd_log.level_procedure);
995 END IF;
996
997 --Initialize message list if p_init_msg_list is set to TRUE.
998 IF FND_API.to_Boolean(p_init_msg_list) THEN
999 FND_MSG_PUB.initialize;
1000 END IF;
1001
1002 --Initialize API return status to success.
1003 x_return_status := FND_API.G_RET_STS_SUCCESS;
1004
1005 HZ_UTILITY_V2PUB.validate_mandatory (
1006 p_create_update_flag => 'C',
1007 p_column => 'p_parent_id',
1008 p_column_value => p_parent_id,
1009 x_return_status => x_return_status);
1010
1011 HZ_UTILITY_V2PUB.validate_mandatory (
1012 p_create_update_flag => 'C',
1013 p_column => 'p_parent_table_name',
1014 p_column_value => p_parent_table_name,
1015 x_return_status => x_return_status);
1016
1017 HZ_UTILITY_V2PUB.validate_mandatory (
1018 p_create_update_flag => 'C',
1019 p_column => 'p_parent_object_type',
1020 p_column_value => p_parent_object_type,
1021 x_return_status => x_return_status);
1022
1023 validate_input (
1024 p_hierarchy_type => p_hierarchy_type,
1025 p_child_table_name => p_child_table_name,
1026 p_child_object_type => p_child_object_type,
1027 p_parent_table_name => p_parent_table_name,
1028 p_parent_object_type => p_parent_object_type,
1029 p_no_of_records => p_no_of_records,
1030 x_return_status => x_return_status
1031 );
1032
1033 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1034 RAISE FND_API.G_EXC_ERROR;
1035 END IF;
1036
1037 OPEN c1;
1038
1039 -- do a bulk fetch of the parent records
1040 FETCH c1 BULK COLLECT INTO
1041 child_id_list,
1042 child_table_name_list,
1043 child_object_type_list,
1044 level_number_list,
1045 top_parent_flag_list,
1046 leaf_child_flag_list,
1047 effective_start_date_list,
1048 effective_end_date_list,
1049 relationship_id_list LIMIT p_no_of_records;
1050
1051 FOR i IN 1..child_id_list.COUNT LOOP
1052 x_related_nodes_list(i).related_node_id := child_id_list(i);
1053 x_related_nodes_list(i).related_node_table_name := child_table_name_list(i);
1054 x_related_nodes_list(i).related_node_object_type := child_object_type_list(i);
1055 x_related_nodes_list(i).level_number := level_number_list(i);
1056 x_related_nodes_list(i).top_parent_flag := top_parent_flag_list(i);
1057 x_related_nodes_list(i).leaf_child_flag := leaf_child_flag_list(i);
1058 x_related_nodes_list(i).effective_start_date := effective_start_date_list(i);
1059 x_related_nodes_list(i).effective_end_date := effective_end_date_list(i);
1060 x_related_nodes_list(i).relationship_id := relationship_id_list(i);
1061 END LOOP;
1062
1063 CLOSE c1;
1064
1065 --Standard call to get message count and if count is 1, get message info.
1066 FND_MSG_PUB.Count_And_Get(
1067 p_encoded => FND_API.G_FALSE,
1068 p_count => x_msg_count,
1069 p_data => x_msg_data );
1070
1071 -- Debug info.
1072 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1073 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1074 p_msg_data=>x_msg_data,
1075 p_msg_type=>'WARNING',
1076 p_msg_level=>fnd_log.level_exception);
1077 END IF;
1078 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1079 hz_utility_v2pub.debug(p_message=>'get_child_nodes (-)',
1080 p_prefix=>l_debug_prefix,
1084 -- Check if API is called in debug mode. If yes, disable debug.
1081 p_msg_level=>fnd_log.level_procedure);
1082 END IF;
1083
1085 --disable_debug;
1086
1087 EXCEPTION
1088 WHEN FND_API.G_EXC_ERROR THEN
1089 x_return_status := FND_API.G_RET_STS_ERROR;
1090
1091 FND_MSG_PUB.Count_And_Get(
1092 p_encoded => FND_API.G_FALSE,
1093 p_count => x_msg_count,
1094 p_data => x_msg_data );
1095
1096 -- Debug info.
1097 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1098 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1099 p_msg_data=>x_msg_data,
1100 p_msg_type=>'ERROR',
1101 p_msg_level=>fnd_log.level_error);
1102 END IF;
1103 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1104 hz_utility_v2pub.debug(p_message=>'get_child_nodes (-)',
1105 p_prefix=>l_debug_prefix,
1106 p_msg_level=>fnd_log.level_procedure);
1107 END IF;
1108
1109 -- Check if API is called in debug mode. If yes, disable debug.
1110 --disable_debug;
1111
1112 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1114
1115 FND_MSG_PUB.Count_And_Get(
1116 p_encoded => FND_API.G_FALSE,
1117 p_count => x_msg_count,
1118 p_data => x_msg_data );
1119
1120 -- Debug info.
1121 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1122 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1123 p_msg_data=>x_msg_data,
1124 p_msg_type=>'UNEXPECTED ERROR',
1125 p_msg_level=>fnd_log.level_error);
1126 END IF;
1127 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1128 hz_utility_v2pub.debug(p_message=>'get_child_nodes (-)',
1129 p_prefix=>l_debug_prefix,
1130 p_msg_level=>fnd_log.level_procedure);
1131 END IF;
1132
1133 -- Check if API is called in debug mode. If yes, disable debug.
1134 --disable_debug;
1135
1136 WHEN OTHERS THEN
1137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1138
1139 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
1140 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
1141 FND_MSG_PUB.ADD;
1142
1143 FND_MSG_PUB.Count_And_Get(
1144 p_encoded => FND_API.G_FALSE,
1145 p_count => x_msg_count,
1146 p_data => x_msg_data );
1147
1148 -- Debug info.
1149 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1150 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1151 p_msg_data=>x_msg_data,
1152 p_msg_type=>'SQL ERROR',
1153 p_msg_level=>fnd_log.level_error);
1154 END IF;
1155 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1156 hz_utility_v2pub.debug(p_message=>'get_child_nodes (-)',
1157 p_prefix=>l_debug_prefix,
1158 p_msg_level=>fnd_log.level_procedure);
1159 END IF;
1160
1161 -- Check if API is called in debug mode. If yes, disable debug.
1162 --disable_debug;
1163
1164 END get_child_nodes;
1165
1166 PROCEDURE get_top_parent_nodes(
1167 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1168 p_hierarchy_type IN VARCHAR2,
1169 p_parent_table_name IN VARCHAR2 := 'HZ_PARTIES',
1170 p_parent_object_type IN VARCHAR2 := 'ALL',
1171 p_effective_date IN DATE := SYSDATE,
1172 p_no_of_records IN NUMBER := 100,
1173 x_top_parent_list OUT NOCOPY RELATED_NODES_LIST_TYPE,
1174 x_return_status OUT NOCOPY VARCHAR2,
1175 x_msg_count OUT NOCOPY NUMBER,
1176 x_msg_data OUT NOCOPY VARCHAR2
1177 ) IS
1178
1179 TYPE parent_id_type IS TABLE OF hz_hierarchy_nodes.parent_id%TYPE;
1180 TYPE parent_table_name_type IS TABLE OF hz_hierarchy_nodes.parent_table_name%TYPE;
1181 TYPE parent_object_type_type IS TABLE OF hz_hierarchy_nodes.parent_object_type%TYPE;
1182 TYPE level_number_type IS TABLE OF hz_hierarchy_nodes.level_number%TYPE;
1183 TYPE top_parent_flag_type IS TABLE OF hz_hierarchy_nodes.top_parent_flag%TYPE;
1184 TYPE leaf_child_flag_type IS TABLE OF hz_hierarchy_nodes.leaf_child_flag%TYPE;
1185 TYPE effective_start_date_type IS TABLE OF hz_hierarchy_nodes.effective_start_date%TYPE;
1186 TYPE effective_end_date_type IS TABLE OF hz_hierarchy_nodes.effective_end_date%TYPE;
1187 TYPE relationship_id_type IS TABLE OF hz_hierarchy_nodes.relationship_id%TYPE;
1188 TYPE entity_id_type IS TABLE OF NUMBER;
1189
1190 parent_id_list parent_id_type;
1191 parent_table_name_list parent_table_name_type;
1192 parent_object_type_list parent_object_type_type;
1193 level_number_list level_number_type;
1194 top_parent_flag_list top_parent_flag_type;
1195 leaf_child_flag_list leaf_child_flag_type;
1196 effective_start_date_list effective_start_date_type;
1197 effective_end_date_list effective_end_date_type;
1198 relationship_id_list relationship_id_type;
1199 entity_id_list entity_id_type;
1200
1201 l_incl_unrelated_entities VARCHAR2(1);
1202 l_count NUMBER := 0;
1203 l_ret VARCHAR2(1) := 'N';
1204 l_object_name VARCHAR2(80);
1205 l_column_name VARCHAR2(80);
1206 l_predicate VARCHAR2(80);
1207 l_str VARCHAR2(2000);
1208 l_limit NUMBER := p_no_of_records;
1209
1210 -- cursor to get all the parent nodes from given hierarchy
1211 CURSOR c2 IS
1212 SELECT PARENT_ID,
1213 PARENT_TABLE_NAME,
1214 PARENT_OBJECT_TYPE,
1215 LEVEL_NUMBER,
1216 TOP_PARENT_FLAG,
1217 LEAF_CHILD_FLAG,
1218 EFFECTIVE_START_DATE,
1219 EFFECTIVE_END_DATE,
1220 RELATIONSHIP_ID
1221 FROM HZ_HIERARCHY_NODES
1222 WHERE HIERARCHY_TYPE = p_hierarchy_type
1223 AND PARENT_TABLE_NAME LIKE p_parent_table_name||'%'
1224 AND PARENT_OBJECT_TYPE LIKE p_parent_object_type||'%'
1225 AND TOP_PARENT_FLAG = 'Y'
1226 AND p_effective_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
1227 AND LEVEL_NUMBER = 0;
1228
1229 -- cursor to read object type setup
1230 CURSOR c_obj_inst IS
1231 SELECT OBJ_NAME,
1232 PK1_COLUMN_NAME,
1233 PREDICATE
1234 FROM FND_OBJECTS FO,
1235 FND_OBJECT_INSTANCE_SETS FOIS
1236 WHERE FOIS.INSTANCE_SET_NAME = p_parent_object_type
1237 AND FOIS.OBJECT_ID = FO.OBJECT_ID;
1238
1239 TYPE ref_cur_type IS REF CURSOR;
1240 ref_cur ref_cur_type;
1241 l_debug_prefix VARCHAR2(30) := '';
1242
1243 BEGIN
1244
1245 -- Check if API is called in debug mode. If yes, enable debug.
1246 --enable_debug;
1247
1248 -- Debug info.
1249 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1250 hz_utility_v2pub.debug(p_message=>'get_top_parent_nodes (+)',
1251 p_prefix=>l_debug_prefix,
1252 p_msg_level=>fnd_log.level_procedure);
1253 END IF;
1254
1255 --Initialize message list if p_init_msg_list is set to TRUE.
1256 IF FND_API.to_Boolean(p_init_msg_list) THEN
1257 FND_MSG_PUB.initialize;
1258 END IF;
1259
1260 --Initialize API return status to success.
1261 x_return_status := FND_API.G_RET_STS_SUCCESS;
1262
1263 validate_input (
1264 p_hierarchy_type => p_hierarchy_type,
1265 p_parent_table_name => p_parent_table_name,
1266 p_parent_object_type => p_parent_object_type,
1267 p_no_of_records => p_no_of_records,
1268 x_return_status => x_return_status
1269 );
1270
1271 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1272 RAISE FND_API.G_EXC_ERROR;
1273 END IF;
1274
1275 OPEN c2;
1276
1277 FETCH c2 BULK COLLECT INTO
1278 parent_id_list,
1279 parent_table_name_list,
1280 parent_object_type_list,
1281 level_number_list,
1282 top_parent_flag_list,
1283 leaf_child_flag_list,
1284 effective_start_date_list,
1285 effective_end_date_list,
1286 relationship_id_list LIMIT l_limit;
1287
1288 CLOSE c2;
1289
1290 FOR i IN 1..parent_id_list.COUNT LOOP
1291 x_top_parent_list(i).related_node_id := parent_id_list(i);
1292 x_top_parent_list(i).related_node_table_name := parent_table_name_list(i);
1293 x_top_parent_list(i).related_node_object_type := parent_object_type_list(i);
1294 x_top_parent_list(i).level_number := level_number_list(i);
1295 x_top_parent_list(i).top_parent_flag := top_parent_flag_list(i);
1296 x_top_parent_list(i).leaf_child_flag := leaf_child_flag_list(i);
1300 l_count := i;
1297 x_top_parent_list(i).effective_start_date := effective_start_date_list(i);
1298 x_top_parent_list(i).effective_end_date := effective_end_date_list(i);
1299 x_top_parent_list(i).relationship_id := relationship_id_list(i);
1301 END LOOP;
1302
1303 -- now check if the relationship type tells to include unrelated entities
1304 l_incl_unrelated_entities := HZ_UTILITY_V2PUB.incl_unrelated_entities(p_hierarchy_type);
1305
1306 IF l_incl_unrelated_entities = 'Y' THEN
1307 IF l_count < 100 THEN
1308 l_limit := l_limit - l_count;
1309 END IF;
1310 -- build the string
1311 OPEN c_obj_inst;
1312 FETCH c_obj_inst INTO l_object_name, l_column_name, l_predicate;
1313 CLOSE c_obj_inst;
1314
1315 -- Bug 4673713.
1316 IF l_predicate IS NOT NULL THEN
1317 l_str := 'select '||l_column_name||' from '||l_object_name||' where '||l_predicate||' and rownum <= :1';
1318 l_count := l_count + 1;
1319 OPEN ref_cur FOR l_str using l_limit;
1320 LOOP
1321 FETCH ref_cur INTO x_top_parent_list(l_count).related_node_id;
1322 EXIT WHEN ref_cur%NOTFOUND;
1323 l_count := l_count + 1;
1324 END LOOP;
1325
1326 ELSE
1327 l_str := 'select '||l_column_name||' from '||l_object_name||' where rownum <= :1';
1328
1329 l_count := l_count + 1;
1330 OPEN ref_cur FOR l_str using l_limit;
1331 LOOP
1332 FETCH ref_cur INTO x_top_parent_list(l_count).related_node_id;
1333 EXIT WHEN ref_cur%NOTFOUND;
1334 l_count := l_count + 1;
1335 END LOOP;
1336 END IF;
1337
1338
1339 END IF;
1340
1341 --Standard call to get message count and if count is 1, get message info.
1342 FND_MSG_PUB.Count_And_Get(
1343 p_encoded => FND_API.G_FALSE,
1344 p_count => x_msg_count,
1345 p_data => x_msg_data );
1346
1347 -- Debug info.
1348 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1349 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1353 END IF;
1350 p_msg_data=>x_msg_data,
1351 p_msg_type=>'WARNING',
1352 p_msg_level=>fnd_log.level_exception);
1354 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1355 hz_utility_v2pub.debug(p_message=>'get_top_parent_nodes (-)',
1356 p_prefix=>l_debug_prefix,
1357 p_msg_level=>fnd_log.level_procedure);
1358 END IF;
1359
1360 -- Check if API is called in debug mode. If yes, disable debug.
1361 --disable_debug;
1362
1363 EXCEPTION
1364 WHEN FND_API.G_EXC_ERROR THEN
1365 x_return_status := FND_API.G_RET_STS_ERROR;
1366
1367 FND_MSG_PUB.Count_And_Get(
1368 p_encoded => FND_API.G_FALSE,
1369 p_count => x_msg_count,
1370 p_data => x_msg_data );
1371
1372 -- Debug info.
1373 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1374 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1375 p_msg_data=>x_msg_data,
1376 p_msg_type=>'ERROR',
1377 p_msg_level=>fnd_log.level_error);
1378 END IF;
1379 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1380 hz_utility_v2pub.debug(p_message=>'get_top_parent_nodes (-)',
1381 p_prefix=>l_debug_prefix,
1382 p_msg_level=>fnd_log.level_procedure);
1383 END IF;
1384
1385 -- Check if API is called in debug mode. If yes, disable debug.
1386 --disable_debug;
1387
1388 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1390
1391 FND_MSG_PUB.Count_And_Get(
1392 p_encoded => FND_API.G_FALSE,
1393 p_count => x_msg_count,
1394 p_data => x_msg_data );
1395
1396 -- Debug info.
1397 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1398 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1399 p_msg_data=>x_msg_data,
1400 p_msg_type=>'UNEXPECTED ERROR',
1401 p_msg_level=>fnd_log.level_error);
1402 END IF;
1403 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1404 hz_utility_v2pub.debug(p_message=>'get_top_parent_nodes (-)',
1405 p_prefix=>l_debug_prefix,
1406 p_msg_level=>fnd_log.level_procedure);
1407 END IF;
1408
1409 -- Check if API is called in debug mode. If yes, disable debug.
1410 --disable_debug;
1411
1412 WHEN OTHERS THEN
1413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1414
1415 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
1416 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
1417 FND_MSG_PUB.ADD;
1418
1419 FND_MSG_PUB.Count_And_Get(
1420 p_encoded => FND_API.G_FALSE,
1421 p_count => x_msg_count,
1422 p_data => x_msg_data );
1423
1424 -- Debug info.
1425 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1426 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1427 p_msg_data=>x_msg_data,
1428 p_msg_type=>'SQL ERROR',
1429 p_msg_level=>fnd_log.level_error);
1430 END IF;
1431 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1432 hz_utility_v2pub.debug(p_message=>'get_top_parent_nodes (-)',
1433 p_prefix=>l_debug_prefix,
1434 p_msg_level=>fnd_log.level_procedure);
1435 END IF;
1436 -- Check if API is called in debug mode. If yes, disable debug.
1437 --disable_debug;
1438
1439 END get_top_parent_nodes;
1440
1441 END HZ_HIERARCHY_V2PUB;