1 PACKAGE BODY HZ_HIERARCHY_PUB AS
2 /*$Header: ARHHINSB.pls 120.14.12010000.2 2008/09/18 10:27:39 rgokavar ship $ */
3
4 -----------------------------------------
5 -- declaration of private global varibles
6 -----------------------------------------
7
8 --G_DEBUG BOOLEAN := FALSE;
9
10 TYPE start_date_list is table of date index by binary_integer;
11 TYPE end_date_list is table of date index by binary_integer;
12 TYPE parent_child_list is table of VARCHAR2(1) index by binary_integer;
13
14 l_bool BOOLEAN;
15 l_status_owner VARCHAR2(255);
16 l_table_owner VARCHAR2(255);
17 l_tmp VARCHAR2(2000);
18 l_line_number NUMBER;
19
20 l_module_prefix CONSTANT VARCHAR2(30) := 'HZ:ARHHINSB:HZ_HIERARCHY_PUB ';
21 l_module CONSTANT VARCHAR2(30) := 'HIERARCHY_NODE';
22 l_debug_prefix VARCHAR2(30) ;
23
24 --------------------------------------------------
25 -- declaration of private procedures and functions
26 --------------------------------------------------
27
28 procedure get_table_owner
29 is
30 begin
31 l_bool := fnd_installation.GET_APP_INFO('AR',l_status_owner,l_tmp,l_table_owner);
32 end;
33
34 /*PROCEDURE enable_debug;
35
36 PROCEDURE disable_debug;
37 */
38
39 PROCEDURE do_create_link(
40 p_hierarchy_node_rec IN HIERARCHY_NODE_REC_TYPE,
41 x_return_status IN OUT NOCOPY VARCHAR2
42 );
43
44 PROCEDURE do_update_link(
45 p_hierarchy_node_rec IN HIERARCHY_NODE_REC_TYPE,
46 x_return_status IN OUT NOCOPY VARCHAR2
47 );
48
49 PROCEDURE do_update_link_pvt(
50 p_hierarchy_node_rec IN HIERARCHY_NODE_REC_TYPE
51 );
52
53 -----------------------------------
54 -- private procedures and functions
55 -----------------------------------
56
57 /**
58 * PRIVATE PROCEDURE enable_debug
59 *
60 * DESCRIPTION
61 * Turn on debug mode.
62 *
63 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
64 * HZ_UTILITY_V2PUB.enable_debug
65 *
66 * MODIFICATION HISTORY
67 *
68 * 31-Oct-2001 Anupam Bordia o Created.
69 *
70 */
71
72 /*PROCEDURE enable_debug IS
73
74 BEGIN
75
76 IF FND_PROFILE.value( 'HZ_API_FILE_DEBUG_ON' ) = 'Y' OR
77 FND_PROFILE.value( 'HZ_API_DBMS_DEBUG_ON' ) = 'Y'
78 THEN
79 HZ_UTILITY_V2PUB.enable_debug;
80 G_DEBUG := TRUE;
81 END IF;
82
83 END enable_debug;
84 */
85
86 /**
87 * PRIVATE PROCEDURE disable_debug
88 *
89 * DESCRIPTION
90 * Turn off debug mode.
91 *
92 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
93 * HZ_UTILITY_V2PUB.disable_debug
94 *
95 * MODIFICATION HISTORY
96 *
97 * 31-Oct-2001 Anupam Bordia o Created.
98 *
99 */
100
101 /*PROCEDURE disable_debug IS
102
103 BEGIN
104
105 IF G_DEBUG THEN
106 HZ_UTILITY_V2PUB.disable_debug;
107 G_DEBUG := FALSE;
108 END IF;
109
110 END disable_debug;
111 */
112
113
114 /**
115 * Table sort routine
116 **/
117 PROCEDURE sort (
118 p_d1 in out nocopy start_date_list,
119 p_d2 in out nocopy end_date_list,
120 p_pc in out nocopy parent_child_list
121 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
122
123 j number :=1;
124
125 CURSOR c IS
126 SELECT *
127 FROM hz_temp_rel_gt
128 ORDER BY date1,date2;
129
130 BEGIN
131
132 if l_bool is null or l_bool=false then
133 get_table_owner;
134 l_line_number := 1;
135 end if;
136
137 if l_bool then
138 execute immediate 'truncate table '||l_table_owner||'.hz_temp_rel_gt';
139 l_line_number := 2;
140
141 forall i in 1..p_d1.count
142 insert into hz_temp_rel_gt(date1,date2,pc_flag) values(p_d1(i),p_d2(i),p_pc(i));
143
144 l_line_number := 3;
145
146
147 open c;
148 fetch c bulk collect into p_d1, p_d2, p_pc;
149 close c;
150
151 l_line_number := 4;
152 commit;
153 end if;
154
155 END sort;
156
157
158 /**
159 * MAINTAIN SELF NODES PROCEDURE
160 **/
161
162 procedure maintain_self_node
163 ( p_node_id number,
164 p_hierarchy_type varchar2,
165 p_table_name VARCHAR2,
166 p_object_type VARCHAR2,
167 p_actual_content_source VARCHAR2
168 )
169
170 is
171
172 l_rowid rowid;
173 l_tp VARCHAR2(1) := 'N';
174 l_lc VARCHAR2(1) := 'N';
175 l_upper_date date;
176 l_lower_date date;
177 j number :=1;
178 i number :=1;
179 process_flag boolean :=true;
180 l_temp_flag VARCHAR2(1);
181 l_const number := 1/(24*3600);
182 l_debug_prefix VARCHAR2(30) := '';
183
184 l_start_date start_date_list;
185 l_end_date end_date_list;
186 l_pc_flag parent_child_list;
187
188 -- Bug 7260677
189 /*
190 cursor c_dates(p_id number,p_table_name varchar2,p_object_type varchar2,p_hierarchy_type varchar2) is
191 select effective_start_date,effective_end_date,'P' parent_child_flag
192 from hz_hierarchy_nodes
193 where parent_id=p_id
194 and parent_table_name=p_table_name
195 and parent_object_type=p_object_type
196 and hierarchy_type=p_hierarchy_type
197 and level_number=1
198 union
199 select effective_start_date,effective_end_date,'C' parent_child_flag
200 from hz_hierarchy_nodes
201 where child_id=p_id
202 and child_table_name=p_table_name
203 and child_object_type=p_object_type
204 and hierarchy_type=p_hierarchy_type
205 and level_number=1;
206 */
207 cursor c_dates(p_id number,p_table_name varchar2,p_object_type varchar2,p_hierarchy_type varchar2) is
208 select to_date(to_char(effective_start_date,'dd/mm/yyyy')||'12:00:00 AM','dd/mm/yyyy hh:mi:ss AM'),
209 to_date(to_char(effective_end_date, 'dd/mm/yyyy')||'11:59:59 PM','dd/mm/yyyy hh:mi:ss PM'),
210 'P' parent_child_flag
211 from hz_hierarchy_nodes
212 where parent_id=p_id
213 and parent_table_name=p_table_name
214 and parent_object_type=p_object_type
215 and hierarchy_type=p_hierarchy_type
216 and level_number=1
217 union
218 select to_date(to_char(effective_start_date,'dd/mm/yyyy')||'00:00:00','dd/mm/yyyy hh24:mi:ss'),
219 to_date(to_char(effective_end_date, 'dd/mm/yyyy')||'23:59:59','dd/mm/yyyy hh24:mi:ss'),
220 'C' parent_child_flag
221 from hz_hierarchy_nodes
222 where child_id=p_id
223 and child_table_name=p_table_name
224 and child_object_type=p_object_type
225 and hierarchy_type=p_hierarchy_type
226 and level_number=1;
227 -- End of Bug 7260677
228
229 begin
230
231 -- Debug info.
232 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
233 hz_utility_v2pub.debug(p_message=>'maintain_self_node (+) node_id = '||p_node_id||' p_hierarchy_type = '||p_hierarchy_type ,
234 p_prefix=>l_debug_prefix,
235 p_msg_level=>fnd_log.level_procedure);
236 END IF;
237
238 delete from hz_hierarchy_nodes
239 where parent_id=p_node_id
240 and parent_table_name=p_table_name
241 and parent_object_type=p_object_type
242 and hierarchy_type=p_hierarchy_type
243 and level_number=0;
244
245 open c_dates(p_node_id,p_table_name,p_object_type,p_hierarchy_type);
246 fetch c_dates bulk collect into l_start_date,l_end_date,l_pc_flag;
247 close c_dates;
248
249 -- Debug info.
250 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
251 hz_utility_v2pub.debug(p_message=>'l_start_date.count = '||l_start_date.count ,
252 p_prefix=>l_debug_prefix,
253 p_msg_level=>fnd_log.level_procedure);
254 END IF;
255
256
257 if l_start_date.count=1
258 then
259 -- Since the node is being inserted for the first time in the hierarchy it can
260 -- just be a parent or a child and cant be both.
261
262 if l_pc_flag(1)='P'
263 then l_tp:='Y';
264 else l_lc:='Y';
265 end if;
266
267 -- Debug info.
268 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
269 hz_utility_v2pub.debug(p_message=>'HZ_HIERARCHY_NODES_PKG.insert_row (+) l_tp = '||l_tp||' l_lc = '||l_lc ,
270 p_prefix=>l_debug_prefix,
271 p_msg_level=>fnd_log.level_procedure);
272 END IF;
273
274 HZ_HIERARCHY_NODES_PKG.Insert_Row(
275 X_ROWID => l_rowid,
276 X_HIERARCHY_TYPE => p_hierarchy_type,
277 X_PARENT_ID => p_node_id,
278 X_PARENT_TABLE_NAME => p_table_name,
279 X_PARENT_OBJECT_TYPE => p_object_type,
280 X_CHILD_ID => p_node_id,
281 X_CHILD_TABLE_NAME => p_table_name,
282 X_CHILD_OBJECT_TYPE => p_object_type,
283 X_LEVEL_NUMBER => 0,
284 X_TOP_PARENT_FLAG => l_tp,
285 X_LEAF_CHILD_FLAG => l_lc,
286 X_EFFECTIVE_START_DATE => l_start_date(1),
287 X_EFFECTIVE_END_DATE => l_end_date(1),
288 X_STATUS => NULL,
289 X_RELATIONSHIP_ID => NULL,
290 X_ACTUAL_CONTENT_SOURCE => p_actual_content_source
291 );
292 else -- multiple nodes present
293
294
295 -- Debug info.
296 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
297 hz_utility_v2pub.debug(p_message=> 'before the initial sort ...' ,
298 p_prefix=>l_debug_prefix,
299 p_msg_level=>fnd_log.level_procedure);
300 END IF;
301
302
303 l_line_number := 5;
304
305 -- sort the nodes
306 sort(l_start_date,l_end_date,l_pc_flag);
307
308 -- Debug info.
309 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
310 for n in 1..l_start_date.count loop
311 hz_utility_v2pub.debug(p_message=>'l_start_date = '||l_start_date(n)||' '||
312 'l_end_date = '||l_end_date(n)||' '||
313 'l_pc_flag = '||l_pc_flag(n),
314 p_prefix=>l_debug_prefix,
315 p_msg_level=>fnd_log.level_procedure);
316 end loop;
317 END IF;
318
319 l_line_number := 6;
320
321 -- 4 values of pc_flag are possible
322 -- 'B' indicates that the node is both a child and a parent during a period
323 -- 'C' indicates that the node is just a child during a period
324 -- 'P' indicates that the node is just a parent during the period
325 -- 'D' indicates that the node is not to be inserted.
326
327 while (process_flag = true)
328 loop
329
330 if l_pc_flag(i)='B'
331 and l_end_date(i) between l_start_date(i+1) and l_end_date(i+1)
332 then
333 l_line_number := 7;
334
335 -- Debug info.
336 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
337 hz_utility_v2pub.debug(p_message=> 'l_pc_flag(i) = B' ,
338 p_prefix=>l_debug_prefix,
339 p_msg_level=>fnd_log.level_procedure);
340 END IF;
341
342 l_start_date(i+1):=l_end_date(i);
343 if l_start_date.exists(i+2) and l_start_date(i+1)>l_start_date(i+2)
344 then
345 l_line_number := 8;
346
347 sort(l_start_date,l_end_date,l_pc_flag);
348
349 l_line_number := 9;
350 end if;
351
352 elsif l_pc_flag(i)=l_pc_flag(i+1)
353 then
354 l_line_number := 10;
355
356 -- Debug info.
357 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
358 hz_utility_v2pub.debug(p_message=> 'l_pc_flag(i)=l_pc_flag(i+1)' ,
359 p_prefix=>l_debug_prefix,
360 p_msg_level=>fnd_log.level_procedure);
361 END IF;
362
363
364 if (l_end_date(i) between l_start_date(i+1) and l_end_date(i+1))
365 then
366 l_line_number := 11;
367
368 l_end_date(i) := l_start_date(i+1);
369 elsif l_end_date(i+1)<l_end_date(i)
370 then
371 l_line_number := 12;
372
373 l_start_date(i+1):=l_start_date(i); l_end_date(i+1) := l_end_date(i);
374 l_pc_flag(i+1):=l_pc_flag(i); l_pc_flag(i):='D';
375 end if;
376 elsif (l_start_date(i)=l_start_date(i+1)
377 and l_end_date(i)=l_end_date(i+1)
378 and l_pc_flag(i)<>l_pc_flag(i+1))
379 then
380 l_line_number := 13;
381
382 l_pc_flag(i):='D';
383 l_pc_flag(i+1) :='B';
384 elsif (l_end_date(i) between l_start_date(i+1) and l_end_date(i+1))
385 OR (l_end_date(i)>l_end_date(i+1))
386 then
387 l_line_number := 14;
388
389 -- Debug info.
390 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
391 hz_utility_v2pub.debug(p_message=> 'l_end_date(i) between ...' ,
392 p_prefix=>l_debug_prefix,
393 p_msg_level=>fnd_log.level_procedure);
394 END IF;
395
396
397 if l_end_date(i+1)>l_end_date(i)
398 then l_upper_date:=l_end_date(i+1); l_lower_date:=l_end_date(i); l_temp_flag:=l_pc_flag(i+1);
399 else l_upper_date:=l_end_date(i); l_lower_date:=l_end_date(i+1); l_temp_flag:=l_pc_flag(i);
400 end if;
401
402 j:=i+2;
403
404 l_end_date(i):=l_start_date(i+1);
405 l_end_date(i+1) := l_lower_date; l_pc_flag(i+1):='B';
406
407 l_line_number := 15;
408
409 if l_start_date.exists(i+2) then
410 for k in reverse j..l_end_date.last
411 loop
412 l_start_date(k+1):=l_start_date(k);
413 l_end_date(k+1):=l_end_date(k);
414 l_pc_flag(k+1):=l_pc_flag(k);
415 end loop;
416 end if;
417
418 l_line_number := 16;
419
420 l_start_date(i+2):=l_lower_date;
421 l_end_Date(i+2) := l_upper_Date;
422 l_pc_flag(i+2) := l_temp_flag;
423
424 if l_start_date.exists(i+3) then
425 if(l_start_date(i+3)<l_start_date(i+2) )
426 or (l_start_date(i+3)=l_start_date(i+2)
427 and l_end_date(i+3)<l_end_date(i+2))
428 then
429 sort(l_start_date,l_end_date,l_pc_flag);
430 end if;
431 end if;
432
433 l_line_number := 17;
434 end if;
435 i:=i+1;
439
436 if (i=l_start_date.count) then process_flag:=false;
437 end if;
438 end loop;
440 l_line_number := 18;
441
442 for i in l_start_date.first..l_start_date.last
443 loop
444 if (l_pc_flag(i)<>'D' and l_start_date(i)<>l_end_date(i))
445 then
446 l_tp:='N'; l_lc:='N';
447
448 if l_pc_flag(i)='P'
449 then l_tp:='Y';
450 elsif l_pc_flag(i)='C'
451 then l_lc:='Y';
452 end if;
453
454 l_line_number := 19;
455
456 -- Debug info.
457 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
458 hz_utility_v2pub.debug(p_message=> 'HZ_HIERARCHY_NODES_PKG.insert_row (+) l_tp = '||l_tp||' l_lc = '||l_lc,
459 p_prefix=>l_debug_prefix,
460 p_msg_level=>fnd_log.level_procedure);
461 END IF;
462
463
464 HZ_HIERARCHY_NODES_PKG.Insert_Row(
465 X_ROWID => l_rowid,
466 X_HIERARCHY_TYPE => p_hierarchy_type,
467 X_PARENT_ID => p_node_id,
468 X_PARENT_TABLE_NAME => p_table_name,
469 X_PARENT_OBJECT_TYPE => p_object_type,
470 X_CHILD_ID => p_node_id,
471 X_CHILD_TABLE_NAME => p_table_name,
472 X_CHILD_OBJECT_TYPE => p_object_type,
473 X_LEVEL_NUMBER => 0,
474 X_TOP_PARENT_FLAG => l_tp,
475 X_LEAF_CHILD_FLAG => l_lc,
476 X_EFFECTIVE_START_DATE => l_start_date(i)+l_const,
477 X_EFFECTIVE_END_DATE => l_end_date(i),
478 X_STATUS => NULL,
479 X_RELATIONSHIP_ID => NULL,
480 X_ACTUAL_CONTENT_SOURCE => p_actual_content_source
481 );
482 end if;
483 end loop;
484
485 end if;
486
487 -- Debug info.
488 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
489 hz_utility_v2pub.debug(p_message=>'maintain_self_node (-)' ,
490 p_prefix=>l_debug_prefix,
491 p_msg_level=>fnd_log.level_procedure);
492 END IF;
493
494 end maintain_self_node;
495
496 /*===========================================================================+
497 | PROCEDURE
498 | do_create_link
499 |
500 | DESCRIPTION
501 | Creates hierarchial link
502 |
503 | SCOPE - PRIVATE
504 |
505 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
506 |
507 | ARGUMENTS : IN:
508 | OUT:
509 |
510 | IN/ OUT:
511 | p_hierarchy_node_rec
512 | x_return_status
513 |
514 | RETURNS : NONE
515 |
516 | NOTES
517 |
518 | MODIFICATION HISTORY
519 |
520 +===========================================================================*/
521
522 PROCEDURE do_create_link(
523 p_hierarchy_node_rec IN HIERARCHY_NODE_REC_TYPE,
524 x_return_status IN OUT NOCOPY VARCHAR2
525 ) IS
526 tmp_child_id NUMBER(15);
527 tmp_child_level_number NUMBER(3);
528 tmp_parent_id NUMBER(15);
529 tmp_parent_level_number NUMBER(3);
530 tmp_top_parent_flag VARCHAR2(1);
531 tmp_leaf_child_flag VARCHAR2(1);
532 parent_exists NUMBER(5);
533 child_exists NUMBER(5);
534 l_level_number NUMBER := 0;
535 l_top_parent_flag VARCHAR2(1) := 'N';
536 l_leaf_child_flag VARCHAR2(1) := 'N';
537 l_effective_start_date DATE := p_hierarchy_node_rec.effective_start_date;
538 l_effective_end_date DATE := p_hierarchy_node_rec.effective_end_date;
539 l_effective_start_date_tp DATE;
540 l_effective_end_date_tp DATE;
541 l_effective_start_date_lc DATE;
542 l_effective_end_date_lc DATE;
543 l_temp_start_date DATE;
544 l_temp_end_date DATE;
545 l_status VARCHAR2(1) := p_hierarchy_node_rec.status;
546 l_relationship_id NUMBER;
547 l_parent_in_hierarchy VARCHAR2(1);
548 l_child_in_hierarchy VARCHAR2(1);
549 l_parent_is_top_parent VARCHAR2(1) := 'Y';
550 l_child_is_top_parent VARCHAR2(1) := 'N';
551 l_parent_is_leaf_child VARCHAR2(1) := 'N';
552 l_child_is_leaf_child VARCHAR2(1) := 'Y';
553 l_return NUMBER;
554 l_dummy VARCHAR2(1);
555 l_direct_link_rowid ROWID;
556 l_rowid ROWID;
557 l_parent_sr_rowid ROWID;
558 l_child_sr_rowid ROWID;
559
560 l_existing_start_date DATE;
561 l_existing_end_date DATE;
562
563 -- this cursor retrieves all the parents of the parent node passed.
564 -- the or condition for the where clause is such to cover any parent
568 SELECT UNIQUE PARENT_ID,
565 -- existance with the current relationships time period and any parent's
566 -- time period.
567 CURSOR c_get_all_parents IS
569 PARENT_TABLE_NAME,
570 PARENT_OBJECT_TYPE,
571 LEVEL_NUMBER
572 /*
573 TOP_PARENT_FLAG,
574 EFFECTIVE_START_DATE,
575 EFFECTIVE_END_DATE
576 */
577 FROM HZ_HIERARCHY_NODES
578 WHERE CHILD_ID = p_hierarchy_node_rec.parent_id
579 AND CHILD_TABLE_NAME = p_hierarchy_node_rec.parent_table_name
580 AND CHILD_OBJECT_TYPE = p_hierarchy_node_rec.parent_object_type
581 AND HIERARCHY_TYPE = p_hierarchy_node_rec.hierarchy_type
582
583 AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
584 OR
585 EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
586 OR
587 l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
588 OR
589 l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
590 )
591 AND NVL(status,'A') = 'A'
592 ORDER BY LEVEL_NUMBER ASC;
593
594 r_get_all_parents c_get_all_parents%ROWTYPE;
595
596 -- this cursor retrieves all the children of the child node passed.
597 -- the or condition for the where clause is such to cover any parent
598 -- existance with the current relationships time period and any parent's
599 -- time period.
600 CURSOR c_get_all_children IS
601 SELECT UNIQUE CHILD_ID,
602 CHILD_TABLE_NAME,
603 CHILD_OBJECT_TYPE,
604 LEVEL_NUMBER
605 /*
606 LEAF_CHILD_FLAG,
607 EFFECTIVE_START_DATE,
608 EFFECTIVE_END_DATE
609 */
610 FROM HZ_HIERARCHY_NODES
611 WHERE PARENT_ID = p_hierarchy_node_rec.child_id
612 AND PARENT_TABLE_NAME = p_hierarchy_node_rec.child_table_name
613 AND PARENT_OBJECT_TYPE = p_hierarchy_node_rec.child_object_type
614 AND HIERARCHY_TYPE = p_hierarchy_node_rec.hierarchy_type
615
616 AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
617 OR
618 EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
619 OR
620 l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
621 OR
622 l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
623 )
624 AND NVL(status,'A') = 'A'
625 ORDER BY LEVEL_NUMBER ASC;
626
627 r_get_all_children c_get_all_children%ROWTYPE;
628
629 -- this cursor returns the immediate parent information
630 CURSOR c_immediate_parent (p_child_id NUMBER,
631 p_child_table_name VARCHAR2,
632 p_child_object_type VARCHAR2) IS
633 SELECT PARENT_ID,
634 PARENT_TABLE_NAME,
635 PARENT_OBJECT_TYPE,
636 EFFECTIVE_START_DATE,
637 EFFECTIVE_END_DATE
638 FROM HZ_HIERARCHY_NODES a
639 WHERE CHILD_ID = p_child_id
640 AND CHILD_TABLE_NAME = p_child_table_name
641 AND CHILD_OBJECT_TYPE = p_child_object_type
642 AND HIERARCHY_TYPE = p_hierarchy_node_rec.hierarchy_type
643 AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
644 OR
645 EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
646 OR
647 l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
648 OR
649 l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
650 )
651 AND NVL(status,'A') = 'A'
652 AND LEVEL_NUMBER = 1
653 -- Fix for Bug 5662272
654 -- Filter out end dated records, if there is a future record existing
655 AND NOT EXISTS (SELECT NULL FROM HZ_HIERARCHY_NODES b
656 WHERE b.PARENT_ID = a.PARENT_ID
657 AND b.PARENT_TABLE_NAME = a.PARENT_TABLE_NAME
658 AND b.PARENT_OBJECT_TYPE = a.PARENT_OBJECT_TYPE
659 AND b.CHILD_ID = a.CHILD_ID
660 AND b.CHILD_TABLE_NAME = a.CHILD_TABLE_NAME
661 AND b.CHILD_OBJECT_TYPE = a.CHILD_OBJECT_TYPE
662 AND b.HIERARCHY_TYPE = a.HIERARCHY_TYPE
663 AND b.LEVEL_NUMBER = a.LEVEL_NUMBER
664 AND NVL(b.status,'A') = 'A'
665 AND b.EFFECTIVE_END_DATE > a.EFFECTIVE_END_DATE
666 )
667 ORDER BY RELATIONSHIP_ID DESC;
668
669 r_immediate_parent c_immediate_parent%ROWTYPE;
670
671 CURSOR c_get_link_info (p_parent_id NUMBER,
672 p_parent_table_name VARCHAR2,
673 p_parent_object_type VARCHAR2,
674 p_child_id NUMBER,
675 p_child_table_name VARCHAR2,
676 p_child_object_type VARCHAR2) IS
680 EFFECTIVE_START_DATE,
677 SELECT LEVEL_NUMBER,
678 TOP_PARENT_FLAG,
679 LEAF_CHILD_FLAG,
681 EFFECTIVE_END_DATE
682 FROM HZ_HIERARCHY_NODES a
683 WHERE PARENT_ID = p_parent_id
684 AND PARENT_TABLE_NAME = p_parent_table_name
685 AND PARENT_OBJECT_TYPE = p_parent_object_type
686 AND CHILD_ID = p_child_id
687 AND CHILD_TABLE_NAME = p_child_table_name
688 AND CHILD_OBJECT_TYPE = p_child_object_type
689 AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
690 OR
691 EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
692 OR
693 l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
694 OR
695 l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
696 )
697 AND NVL(status,'A') = 'A'
698 -- Fix for Bug 5662272
699 -- For the same level, if there are 2 records, then take the latest one and filter out
700 -- record with lower end date. That record may have been end dated and a new one is created
701 AND NOT EXISTS (SELECT NULL FROM HZ_HIERARCHY_NODES b
702 WHERE b.PARENT_ID = a.PARENT_ID
703 AND b.PARENT_TABLE_NAME = a.PARENT_TABLE_NAME
704 AND b.PARENT_OBJECT_TYPE = a.PARENT_OBJECT_TYPE
705 AND b.CHILD_ID = a.CHILD_ID
706 AND b.CHILD_TABLE_NAME = a.CHILD_TABLE_NAME
707 AND b.CHILD_OBJECT_TYPE = a.CHILD_OBJECT_TYPE
708 AND b.HIERARCHY_TYPE = a.HIERARCHY_TYPE
709 AND b.LEVEL_NUMBER = a.LEVEL_NUMBER
710 AND NVL(b.status,'A') = 'A'
711 AND b.EFFECTIVE_END_DATE > a.EFFECTIVE_END_DATE
712 )
713 ;
714
715 r_get_link_info c_get_link_info%ROWTYPE;
716
717 BEGIN
718
719 -- Debug info.
720 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
721 hz_utility_v2pub.debug(p_message=>'do_create_link (+)',
722 p_prefix=> '',
723 p_msg_level=>fnd_log.level_procedure);
724 END IF;
725
726 -- when this procedure is called, the request is to incorporate a relation
727 -- between AAA and BBB in a hierarchy (defined by relationship type) where
728 -- AAA is parent of BBB
729 -- AAA (Parent)
730 -- |
731 -- |
732 -- |
733 -- \|/
734 -- BBB (Child)
735
736 -------------------------
737 -- INSERT THE DIRECT LINK
738 -------------------------
739
740 -- Insert the direct link between AAA->BBB.
741 -- Since no duplicate record will be sent to this API, there is no need to check for the
742 -- existance of a record with same parent/child, even if there is one, it will have a
743 -- different period of time.
744 HZ_HIERARCHY_NODES_PKG.Insert_Row(
745 X_ROWID => l_direct_link_rowid,
746 X_HIERARCHY_TYPE => p_hierarchy_node_rec.hierarchy_type,
747 X_PARENT_ID => p_hierarchy_node_rec.parent_id,
748 X_PARENT_TABLE_NAME => p_hierarchy_node_rec.parent_table_name,
749 X_PARENT_OBJECT_TYPE => p_hierarchy_node_rec.parent_object_type,
750 X_CHILD_ID => p_hierarchy_node_rec.child_id,
751 X_CHILD_TABLE_NAME => p_hierarchy_node_rec.child_table_name,
752 X_CHILD_OBJECT_TYPE => p_hierarchy_node_rec.child_object_type,
753 X_LEVEL_NUMBER => 1,
754 X_TOP_PARENT_FLAG => NULL,
755 X_LEAF_CHILD_FLAG => NULL,
756 X_EFFECTIVE_START_DATE => p_hierarchy_node_rec.effective_start_date,
757 X_EFFECTIVE_END_DATE => p_hierarchy_node_rec.effective_end_date,
758 X_STATUS => p_hierarchy_node_rec.status,
759 X_RELATIONSHIP_ID => p_hierarchy_node_rec.relationship_id,
760 X_ACTUAL_CONTENT_SOURCE => p_hierarchy_node_rec.actual_content_source
761 );
762
763 ---------------------------------
764 -- PARENT'S SELF NODE MAINTENANCE
765 ---------------------------------
766 -- Debug info.
767 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
768 hz_utility_v2pub.debug(p_message=>'maintain_self_node for Parent(+)',
769 p_prefix=> '',
770 p_msg_level=>fnd_log.level_procedure);
771 END IF;
772
773 maintain_self_node (
774 p_node_id => p_hierarchy_node_rec.parent_id,
775 p_hierarchy_type => p_hierarchy_node_rec.hierarchy_type,
776 p_table_name => p_hierarchy_node_rec.parent_table_name,
777 p_object_type => p_hierarchy_node_rec.parent_object_type,
778 p_actual_content_source => p_hierarchy_node_rec.actual_content_source
779 );
780
781 -- Debug info.
782 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
783 hz_utility_v2pub.debug(p_message=>'maintain_self_node for Parent(-)',
784 p_prefix=> '',
788 --------------------------------
785 p_msg_level=>fnd_log.level_procedure);
786 END IF;
787
789 -- CHILD'S SELF NODE MAINTENANCE
790 --------------------------------
791 -- Debug info.
792 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
793 hz_utility_v2pub.debug(p_message=>'maintain_self_node for Child(+)',
794 p_prefix=> '',
795 p_msg_level=>fnd_log.level_procedure);
796 END IF;
797
798 maintain_self_node (
799 p_node_id => p_hierarchy_node_rec.child_id,
800 p_hierarchy_type => p_hierarchy_node_rec.hierarchy_type,
801 p_table_name => p_hierarchy_node_rec.child_table_name,
802 p_object_type => p_hierarchy_node_rec.child_object_type,
803 p_actual_content_source => p_hierarchy_node_rec.actual_content_source
804 );
805
806 -- Debug info.
807 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
808 hz_utility_v2pub.debug(p_message=>'maintain_self_node for child (-)',
809 p_prefix=> '',
810 p_msg_level=>fnd_log.level_procedure);
811 END IF;
812
813 -- Bug 4902909.
814 -- we'll get the parents and children of the relationship,
815 -- based on the existing start dates.
816 -- This is because, if the new dates are used, the data will be picked up
817 -- in to the cursor only if there is some overlap between the old and new dates.
818 -- If the old and new date ranges are mutually exclusive, then the where
819 -- used for the cursors will prevent any data from being picked up.
820 -- Also the relationship API will not pass the existing dates.
821 -- these are picked up from the HZ_HIERARCHY_NODES table itself,
822 -- from the level 1 recordusing the parent_id, child_i and relationship_id
823 -- passed through the p_hierarchy_node_rec.
824 -- once obtained, these current dates will be used to pickup data in to the cursors.
825
826 -- fetch existing effective start and end dates for relationship id
827 BEGIN
828 SELECT EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
829 INTO l_existing_start_date, l_existing_end_date
830 FROM HZ_HIERARCHY_NODES
831 WHERE PARENT_ID = p_hierarchy_node_rec.parent_id
832 AND CHILD_ID = p_hierarchy_node_rec.child_id
833 AND LEVEL_NUMBER = 1
834 AND RELATIONSHIP_ID = p_hierarchy_node_rec.relationship_id ;
835 EXCEPTION WHEN OTHERS THEN
836 l_existing_start_date := p_hierarchy_node_rec.effective_start_date;
837 l_existing_end_date := p_hierarchy_node_rec.effective_end_date;
838
839 -- Debug info.
840 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
841 hz_utility_v2pub.debug(p_message=>'Local Exception:'||SQLERRM,
842 p_prefix=> '',
843 p_msg_level=>fnd_log.level_procedure);
844 END IF;
845
846 END;
847
848
849 --------------------------------
850 -- MAINTENANCE OF INDIRECT LINKS
851 --------------------------------
852 -- Debug info.
853 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
854 hz_utility_v2pub.debug(p_message=>'maintain indirect links (+)',
855 p_prefix=> '',
856 p_msg_level=>fnd_log.level_procedure);
857 END IF;
858
859 -- first we'll get all the parents of AAA (this will include AAA itself)
860 OPEN c_get_all_parents;
861 FETCH c_get_all_parents INTO r_get_all_parents;
862
863
864 WHILE c_get_all_parents%FOUND
865 -- at this point we have got at least one parent for AAA, let's call it XXX
866 LOOP
867 -- we open the cursor to get all the children of BBB (this will include BBB itself)
868 OPEN c_get_all_children;
869 FETCH c_get_all_children INTO r_get_all_children;
870
871 WHILE c_get_all_children%FOUND
872 -- let's call it YYY
873 LOOP
874 -- we need to create a link XXX --> YYY
875 -- get the immediate parent of YYY, let's call it III
876 OPEN c_immediate_parent (r_get_all_children.child_id,
877 r_get_all_children.child_table_name,
878 r_get_all_children.child_object_type);
879 FETCH c_immediate_parent INTO r_immediate_parent;
880 CLOSE c_immediate_parent;
881
882 -- get the link information between XXX and III
883 OPEN c_get_link_info (r_get_all_parents.parent_id,
884 r_get_all_parents.parent_table_name,
885 r_get_all_parents.parent_object_type,
886 r_immediate_parent.parent_id,
887 r_immediate_parent.parent_table_name,
888 r_immediate_parent.parent_object_type);
889 FETCH c_get_link_info INTO r_get_link_info;
890 CLOSE c_get_link_info;
891
892 -- we need to find out
893 -- level : level of XXX->YYY is level of XXX->III plus 1
894 l_level_number := r_get_link_info.level_number + 1;
895
896 -- effective dates : that would be
897 -- 1. start date later of the two start dates of XXX->III and III->YYY
901 ELSE
898 -- 2. end date earlier of the two end dates of XXX->III and III->YYY
899 IF r_get_link_info.effective_start_date <= r_immediate_parent.effective_start_date THEN
900 l_effective_start_date := r_immediate_parent.effective_start_date;
902 l_effective_start_date := r_get_link_info.effective_start_date;
903 END IF;
904
905 IF r_get_link_info.effective_end_date <= r_immediate_parent.effective_end_date THEN
906 l_effective_end_date := r_get_link_info.effective_end_date;
907 ELSE
908 l_effective_end_date := r_immediate_parent.effective_end_date;
909 END IF;
910
911 -- top parent : top parent status of XXX remains as it is
912 -- l_top_parent_flag := r_get_all_parents.top_parent_flag;
913
914 IF l_level_number > 1 THEN
915 -- now insert the XXX->YYY link
916 HZ_HIERARCHY_NODES_PKG.Insert_Row(
917 X_ROWID => l_rowid,
918 X_HIERARCHY_TYPE => p_hierarchy_node_rec.hierarchy_type,
919 X_PARENT_ID => r_get_all_parents.parent_id,
920 X_PARENT_TABLE_NAME => r_get_all_parents.parent_table_name,
921 X_PARENT_OBJECT_TYPE => r_get_all_parents.parent_object_type,
922 X_CHILD_ID => r_get_all_children.child_id,
923 X_CHILD_TABLE_NAME => r_get_all_children.child_table_name,
924 X_CHILD_OBJECT_TYPE => r_get_all_children.child_object_type,
925 X_LEVEL_NUMBER => l_level_number,
926 X_TOP_PARENT_FLAG => NULL,
927 X_LEAF_CHILD_FLAG => NULL,
928 X_EFFECTIVE_START_DATE => l_effective_start_date,
929 X_EFFECTIVE_END_DATE => l_effective_end_date,
930 X_STATUS => NULL,
931 X_RELATIONSHIP_ID => NULL,
932 X_ACTUAL_CONTENT_SOURCE => p_hierarchy_node_rec.actual_content_source
933 );
934 END IF;
935
936 -- get the next child
937 FETCH c_get_all_children INTO r_get_all_children;
938 END LOOP;
939 CLOSE c_get_all_children;
940 -- get the next parent of AAA
941 FETCH c_get_all_parents INTO r_get_all_parents;
942
943 END LOOP;
944 CLOSE c_get_all_parents;
945
946
947 -- Debug info.
948 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
949 hz_utility_v2pub.debug(p_message=>'maintain indirect links (-)',
950 p_prefix=> '',
951 p_msg_level=>fnd_log.level_procedure);
952 END IF;
953
954 l_temp_start_date := l_effective_start_date_lc;
955 l_temp_end_date := l_effective_end_date_lc;
956
957 -- Debug info.
958 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
959 hz_utility_v2pub.debug(p_message=>'do_create_link (-)',
960 p_prefix=> '',
961 p_msg_level=>fnd_log.level_procedure);
962 END IF;
963
964 END do_create_link;
965
966 /*------------------------------------------------------------------------------+
967 Created By Nishant on 03-Apr-2006 for Bug 4662744. Modified logic of
968 Do_update_link procedure to traverse the tree recursively to figure out parents
969 at each step and then update the hierarchy nodes dates
970 ------------------------------------------------------------------------------+*/
971 PROCEDURE do_update_link_pvt(
972 p_hierarchy_node_rec IN HIERARCHY_NODE_REC_TYPE
973 ) IS
974
975 l_debug_prefix CONSTANT VARCHAR2(30) := '';
976
977 l_parent_id NUMBER;
978 l_parent_type VARCHAR2(100);
979 l_child_id NUMBER;
980 l_child_type VARCHAR2(100);
981 l_level NUMBER;
982 l_hierarchy_type VARCHAR2(30);
983 l_parent_table VARCHAR2(30);
984 l_child_table VARCHAR2(30);
985
986 -- Bug 4902909.
987 -- we'll get the parents and children of the relationship,
988 -- based on the existing start dates.
989 -- This is because, if the new dates are used, the data will be picked up
990 -- in to the cursor only if there is some overlap between the old and new dates.
991 -- If the old and new date ranges are mutually exclusive, then the where
992 -- used for the cursors will prevent any data from being picked up.
993 -- Also the relationship API will not pass the existing dates.
994 -- these are picked up from the HZ_HIERARCHY_NODES table itself,
995 -- from the level 1 recordusing the parent_id, child_i and relationship_id
996 -- passed through the p_hierarchy_node_rec.
997 -- once obtained, these current dates will be used to pickup data in to the cursors.
998 l_existing_start_date DATE;
999 l_existing_end_date DATE;
1000 l_LAST_UPDATED_BY NUMBER;
1001 l_LAST_UPDATE_DATE DATE;
1002 l_LAST_UPDATE_LOGIN NUMBER;
1003
1004 TYPE parent_list_rec_type IS RECORD
1005 ( parent_id NUMBER,
1006 parent_type VARCHAR2(100),
1010
1007 parent_table VARCHAR2(100),
1008 level_number NUMBER
1009 );
1011 TYPE parent_list_tbl_type IS TABLE OF parent_list_rec_type INDEX BY BINARY_INTEGER;
1012
1013 parent_list_tbl parent_list_tbl_type;
1014
1015 CURSOR c_get_all_children (ll_child_id NUMBER, ll_child_object_type IN VARCHAR2,
1016 ll_child_table IN VARCHAR2)
1017 IS
1018 SELECT CHILD_ID,
1019 CHILD_OBJECT_TYPE,
1020 CHILD_TABLE_NAME
1021 FROM HZ_HIERARCHY_NODES
1022 WHERE PARENT_ID = ll_child_id
1023 AND PARENT_TABLE_NAME = ll_child_table
1024 AND PARENT_OBJECT_TYPE = ll_child_object_type
1025 AND HIERARCHY_TYPE = l_hierarchy_type
1026 AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
1027 OR
1028 EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
1029 OR
1030 l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
1031 OR
1032 l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
1033 )
1034 AND NVL(status,'A') = 'A'
1035 ORDER BY LEVEL_NUMBER ASC;
1036
1037 CURSOR c_match_par_child (l_parent_id IN NUMBER,l_parent_object_type IN VARCHAR2,
1038 l_parent_table IN VARCHAR2,
1039 l_child_id IN NUMBER, l_child_object_type IN VARCHAR2,
1040 l_child_table IN VARCHAR2)
1041 IS
1042 SELECT PARENT_ID, PARENT_OBJECT_TYPE, CHILD_ID, CHILD_OBJECT_TYPE, rowid
1043 FROM HZ_HIERARCHY_NODES a
1044 WHERE PARENT_ID = l_parent_id
1045 AND PARENT_TABLE_NAME = l_parent_table
1046 AND PARENT_OBJECT_TYPE = l_parent_object_type
1047 AND CHILD_ID = l_child_id
1048 AND CHILD_TABLE_NAME = l_child_table
1049 AND CHILD_OBJECT_TYPE = l_child_object_type
1050 AND HIERARCHY_TYPE = l_hierarchy_type
1051 AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
1052 OR
1053 EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
1054 OR
1055 l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
1056 OR
1057 l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
1058 )
1059 AND NVL(status,'A') = 'A'
1060 -- Fix for Bug 5662272
1061 -- Here problem is, it will pick up any 1 record in random and update end date for that
1062 -- This way it may inactivae end dated record and leave active record unchanged.
1063 -- Change it pick up the records for which start date and end date for level 1 relationship
1064 -- matches (most likely that will be the date for this link also.). If No such record
1065 -- exists, then pick up the greatest effective_end_date rec (most linkely will be the active one)
1066 -- and execute cursor only once
1067 AND NOT EXISTS (SELECT NULL FROM HZ_HIERARCHY_NODES b
1068 WHERE b.PARENT_ID = a.PARENT_ID
1069 AND b.PARENT_TABLE_NAME = a.PARENT_TABLE_NAME
1070 AND b.PARENT_OBJECT_TYPE = a.PARENT_OBJECT_TYPE
1071 AND b.CHILD_ID = a.CHILD_ID
1072 AND b.CHILD_TABLE_NAME = a.CHILD_TABLE_NAME
1073 AND b.CHILD_OBJECT_TYPE = a.CHILD_OBJECT_TYPE
1074 AND b.HIERARCHY_TYPE = a.HIERARCHY_TYPE
1075 AND b.LEVEL_NUMBER = a.LEVEL_NUMBER
1076 AND NVL(b.status,'A') = 'A'
1077 AND b.EFFECTIVE_START_DATE = l_existing_start_date
1078 AND b.EFFECTIVE_END_DATE = l_existing_end_date
1079 AND a.ROWID <> b.ROWID
1080 )
1081 -- Move this condition inside cursor loop
1082 --AND ROWNUM = 1
1083 ORDER BY effective_end_date desc;
1084
1085
1086 PROCEDURE do_recursive_parent_fetch (p_child_id IN NUMBER,
1087 p_child_type IN VARCHAR2,
1088 p_child_table IN VARCHAR2,
1089 p_child_level IN NUMBER) IS
1090
1091
1092 CURSOR get_parent_id_cur (l_child_id IN NUMBER, l_child_object_type IN VARCHAR2,
1093 l_child_table IN VARCHAR2) IS
1094 SELECT parent_id, parent_object_type, parent_table_name
1095 FROM hz_hierarchy_nodes
1096 WHERE hierarchy_type = l_hierarchy_type
1097 AND child_table_name = l_child_table
1098 AND child_object_type = l_child_object_type
1099 AND child_id = l_child_id
1100 AND level_number = 1
1101 AND SYSDATE+0.0001 BETWEEN effective_start_date AND effective_end_date
1102 AND NVL(status,'A') = 'A'
1103 ;
1104
1105 l_parent_id NUMBER;
1106 l_parent_type VARCHAR2(100);
1107 l_parent_level NUMBER;
1108 l_parent_table VARCHAR2(100);
1109 l_counter NUMBER;
1110 BEGIN
1111 -- FND Logging for debug purpose
1112 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1113 hz_utility_v2pub.debug
1114 (p_message => 'Input : '||p_child_id||' : '||p_child_type||' : '||p_child_level,
1115 p_prefix => l_debug_prefix,
1119 );
1116 p_msg_level => fnd_log.level_statement,
1117 p_module_prefix => l_module_prefix,
1118 p_module => l_module
1120 END IF;
1121
1122 OPEN get_parent_id_cur (p_child_id, p_child_type, p_child_table);
1123
1124 -- FND Logging for debug purpose
1125 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1126 hz_utility_v2pub.debug
1127 (p_message => 'Open get_parent_id_cur for id : '||p_child_id,
1128 p_prefix => l_debug_prefix,
1129 p_msg_level => fnd_log.level_statement,
1130 p_module_prefix => l_module_prefix,
1131 p_module => l_module
1132 );
1133 END IF;
1134
1135 LOOP
1136 FETCH get_parent_id_cur INTO l_parent_id, l_parent_type, l_parent_table;
1137 IF (get_parent_id_cur%FOUND) THEN
1138
1139 l_counter := parent_list_tbl.COUNT+1;
1140 parent_list_tbl(l_counter).parent_id := l_parent_id;
1141 parent_list_tbl(l_counter).parent_type := l_parent_type;
1142 parent_list_tbl(l_counter).parent_table := l_parent_table;
1143 parent_list_tbl(l_counter).level_number := p_child_level;
1144
1145 -- Fix for Bug 5204188 (Parent Id and Child Id same at level =1, so it goes
1146 -- infinite recursion). Break out if parent and child id are same.
1147 IF (p_child_id = l_parent_id) THEN
1148 -- FND Logging for debug purpose
1149 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1150 hz_utility_v2pub.debug
1151 (p_message => 'Exit loop because parent child matched for level 1',
1152 p_prefix => l_debug_prefix,
1153 p_msg_level => fnd_log.level_statement,
1154 p_module_prefix => l_module_prefix,
1155 p_module => l_module
1156 );
1157 END IF;
1158
1159 EXIT;
1160
1161 ELSE
1162
1163 -- FND Logging for debug purpose
1164 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1165 hz_utility_v2pub.debug
1166 (p_message => 'Fetched for recursion : '||l_parent_id||' : '||l_parent_type||' : '||TO_CHAR(TO_NUMBER(p_child_level+1)),
1167 p_prefix => l_debug_prefix,
1168 p_msg_level => fnd_log.level_statement,
1169 p_module_prefix => l_module_prefix,
1170 p_module => l_module
1171 );
1172 END IF;
1173
1174 do_recursive_parent_fetch(l_parent_id, l_parent_type,l_parent_table, p_child_level+1);
1175 END IF;
1176
1177 ELSE
1178
1179 -- FND Logging for debug purpose
1180 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1181 hz_utility_v2pub.debug
1182 (p_message => 'Exit Loop...',
1183 p_prefix => l_debug_prefix,
1184 p_msg_level => fnd_log.level_statement,
1185 p_module_prefix => l_module_prefix,
1186 p_module => l_module
1187 );
1188 END IF;
1189
1190 EXIT;
1191 END IF;
1192
1193 END LOOP;
1194
1195 -- FND Logging for debug purpose
1196 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1197 hz_utility_v2pub.debug
1198 (p_message => 'Close get_parent_id_cur for id : '||p_child_id,
1199 p_prefix => l_debug_prefix,
1200 p_msg_level => fnd_log.level_statement,
1201 p_module_prefix => l_module_prefix,
1202 p_module => l_module
1203 );
1204 END IF;
1205
1206 CLOSE get_parent_id_cur;
1207
1208 END do_recursive_parent_fetch;
1209
1210 BEGIN
1211 -- Debug info.
1212 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1213 hz_utility_v2pub.debug(p_message=>'do_update_link_pvt (+)',
1214 p_prefix=>l_debug_prefix,
1215 p_msg_level=>fnd_log.level_procedure);
1216 END IF;
1217
1218 -- initialize variables
1219 l_parent_id := p_hierarchy_node_rec.parent_id;
1220 l_parent_type := p_hierarchy_node_rec.parent_object_type;
1221 l_child_id := p_hierarchy_node_rec.child_id;
1222 l_child_type := p_hierarchy_node_rec.child_object_type;
1223 l_level := 1;
1224 l_hierarchy_type := p_hierarchy_node_rec.hierarchy_type;
1225 l_parent_table := p_hierarchy_node_rec.parent_table_name;
1226 l_child_table := p_hierarchy_node_rec.child_table_name;
1227
1228 l_LAST_UPDATED_BY := HZ_UTILITY_V2PUB.last_updated_by;
1229 l_LAST_UPDATE_DATE := HZ_UTILITY_V2PUB.last_update_date;
1230 l_LAST_UPDATE_LOGIN := HZ_UTILITY_V2PUB.last_update_login;
1231
1232
1233 -- fetch existing effective start and end dates for relationship id
1234 BEGIN
1235 SELECT EFFECTIVE_START_DATE,EFFECTIVE_END_DATE
1236 INTO l_existing_start_date,l_existing_end_date
1237 FROM HZ_HIERARCHY_NODES
1238 WHERE PARENT_ID = p_hierarchy_node_rec.parent_id
1239 AND CHILD_ID = p_hierarchy_node_rec.child_id
1240 AND LEVEL_NUMBER = 1
1244 l_existing_end_date := p_hierarchy_node_rec.effective_end_date;
1241 AND RELATIONSHIP_ID = p_hierarchy_node_rec.relationship_id ;
1242 EXCEPTION WHEN OTHERS THEN
1243 l_existing_start_date := p_hierarchy_node_rec.effective_start_date;
1245
1246 -- Debug info.
1247 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1248 hz_utility_v2pub.debug(p_message=>'Local Exception:'||SQLERRM,
1249 p_prefix=>l_debug_prefix,
1250 p_msg_level=>fnd_log.level_procedure);
1251 END IF;
1252
1253 END;
1254
1255 -- Add self record for parent
1256 parent_list_tbl(1).parent_id := l_parent_id;
1257 parent_list_tbl(1).parent_type := l_parent_type;
1258 parent_list_tbl(1).parent_table := l_parent_table;
1259 parent_list_tbl(1).level_number := 0;
1260
1261 -- Debug info.
1262 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1263 hz_utility_v2pub.debug(p_message=>'do_recursive_parent_fetch (+)',
1264 p_prefix=>l_debug_prefix,
1265 p_msg_level=>fnd_log.level_procedure);
1266 END IF;
1267
1268 do_recursive_parent_fetch(l_parent_id, l_parent_type, l_parent_table, l_level);
1269
1270 -- Debug info.
1271 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1272 hz_utility_v2pub.debug(p_message=>'do_recursive_parent_fetch (-)',
1273 p_prefix=>l_debug_prefix,
1274 p_msg_level=>fnd_log.level_procedure);
1275 END IF;
1276
1277 -- dbms_output.put_line('Table data:');
1278 IF (parent_list_tbl IS NOT NULL) THEN
1279 IF (parent_list_tbl.COUNT > 0) THEN
1280 FOR i IN parent_list_tbl.FIRST..parent_list_tbl.LAST LOOP
1281 /* dbms_output.put_line(i||':'||parent_list_tbl(i).parent_id
1282 ||':'||parent_list_tbl(i).parent_type
1283 ||':'||parent_list_tbl(i).level_number);
1284 */
1285 -- put the child cursor here
1286 FOR c_child_rec IN c_get_all_children (l_child_id, l_child_type, l_child_table) LOOP
1287 FOR c_par_child_rec IN c_match_par_child (parent_list_tbl(i).parent_id, parent_list_tbl(i).parent_type,
1288 parent_list_tbl(i).parent_table,
1289 c_child_rec.child_id, c_child_rec.CHILD_OBJECT_TYPE, c_child_rec.CHILD_TABLE_NAME
1290 ) LOOP
1291 /* dbms_output.put_line(c_par_child_rec.parent_id||':'||c_par_child_rec.parent_object_type||':'||
1292 c_par_child_rec.child_id||':'||c_par_child_rec.child_object_type||':'||
1293 c_par_child_rec.rowid);
1294 */
1295 UPDATE HZ_HIERARCHY_NODES
1296 SET EFFECTIVE_START_DATE = NVL(p_hierarchy_node_rec.effective_start_date, EFFECTIVE_START_DATE),
1297 EFFECTIVE_END_DATE = NVL(p_hierarchy_node_rec.effective_end_date, EFFECTIVE_END_DATE),
1298 -- added for Bug 5662272 (This will update Status values for I which help in
1299 -- eleminating deleted rows from all the cursors
1300 STATUS = NVL(p_hierarchy_node_rec.status, STATUS),
1301 LAST_UPDATED_BY = l_last_updated_by,
1302 LAST_UPDATE_DATE = l_last_update_date,
1303 LAST_UPDATE_LOGIN = l_last_update_login
1304 WHERE ROWID = c_par_child_rec.ROWID;
1305
1306 -- exit after 1 execution for each parent child pair for 1 level
1307 -- proviously rownum = 1 logic in cursor but because of sorting req. (latest rec)
1308 -- moved 1 rec logic here (Fix for Bug 5662272)
1309 EXIT;
1310
1311 END LOOP;
1312 END LOOP;
1313 END LOOP;
1314 END IF;
1315 END IF;
1316
1317 -- Debug info.
1318 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1319 hz_utility_v2pub.debug(p_message=>'do_update_link_pvt (-)',
1320 p_prefix=>l_debug_prefix,
1321 p_msg_level=>fnd_log.level_procedure);
1322 END IF;
1323
1324 END do_update_link_pvt;
1325 ------------------------------------------------------------------------------+
1326
1327 /*===========================================================================+
1328 | PROCEDURE
1329 | do_update_link
1330 |
1331 | DESCRIPTION
1332 | updates hierarchial relationship between two nodes and corrosponding links.
1333 |
1334 | SCOPE - PRIVATE
1335 |
1336 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1337 |
1338 | ARGUMENTS : IN:
1339 | OUT:
1340 | IN/ OUT:
1341 | p_hierarchy_node_rec
1342 | x_return_status
1343 |
1344 | RETURNS : NONE
1345 |
1346 | NOTES
1347 |
1348 | MODIFICATION HISTORY
1349 |
1350 +===========================================================================*/
1351
1352 PROCEDURE do_update_link(
1353 p_hierarchy_node_rec IN HIERARCHY_NODE_REC_TYPE,
1354 x_return_status IN OUT NOCOPY VARCHAR2
1355 ) IS
1356 /* -- Moved below logic into procedure do_update_link_pvt for Bug 4662744 (Nishant 30-Mar-2006)
1357 -- End dating of hierarchy in case of multiple parents was not correct
1358
1359 -- Bug 4902909.
1360 -- we'll get the parents and children of the relationship,
1361 -- based on the existing start dates.
1362 -- This is because, if the new dates are used, the data will be picked up
1363 -- in to the cursor only if there is some overlap between the old and new dates.
1367 -- these are picked up from the HZ_HIERARCHY_NODES table itself,
1364 -- If the old and new date ranges are mutually exclusive, then the where
1365 -- used for the cursors will prevent any data from being picked up.
1366 -- Also the relationship API will not pass the existing dates.
1368 -- from the level 1 recordusing the parent_id, child_i and relationship_id
1369 -- passed through the p_hierarchy_node_rec.
1370 -- once obtained, these current dates will be used to pickup data in to the cursors.
1371 l_existing_start_date DATE;
1372 l_existing_end_date DATE;
1373 -- this cursor retrieves all the parents of the parent node passed.
1374 -- the or condition for the where clause is such to cover any parent
1375 -- existance with the current relationships time period and any parent's
1376 -- time period.
1377 CURSOR c_get_all_parents IS
1378 SELECT PARENT_ID,
1379 PARENT_TABLE_NAME,
1380 PARENT_OBJECT_TYPE,
1381 LEVEL_NUMBER,
1382 TOP_PARENT_FLAG,
1383 EFFECTIVE_START_DATE,
1384 EFFECTIVE_END_DATE
1385 FROM HZ_HIERARCHY_NODES
1386 WHERE CHILD_ID = p_hierarchy_node_rec.parent_id
1387 AND CHILD_TABLE_NAME = p_hierarchy_node_rec.parent_table_name
1388 AND CHILD_OBJECT_TYPE = p_hierarchy_node_rec.parent_object_type
1389 AND HIERARCHY_TYPE = p_hierarchy_node_rec.hierarchy_type
1390 AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
1391 OR
1392 EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
1393 OR
1394 l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
1395 OR
1396 l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
1397 )
1398 -- AND NVL(RELATIONSHIP_ID, p_hierarchy_node_rec.relationship_id) = p_hierarchy_node_rec.relationship_id
1399 ORDER BY LEVEL_NUMBER ASC;
1400
1401 r_get_all_parents c_get_all_parents%ROWTYPE;
1402
1403 -- this cursor retrieves all the children of the child node passed.
1404 -- the or condition for the where clause is such to cover any parent
1405 -- existance with the current relationships time period and any parent's
1406 -- time period.
1407 CURSOR c_get_all_children IS
1408 SELECT CHILD_ID,
1409 CHILD_TABLE_NAME,
1410 CHILD_OBJECT_TYPE,
1411 LEVEL_NUMBER,
1412 LEAF_CHILD_FLAG,
1413 EFFECTIVE_START_DATE,
1414 EFFECTIVE_END_DATE
1415 FROM HZ_HIERARCHY_NODES
1416 WHERE PARENT_ID = p_hierarchy_node_rec.child_id
1417 AND PARENT_TABLE_NAME = p_hierarchy_node_rec.child_table_name
1418 AND PARENT_OBJECT_TYPE = p_hierarchy_node_rec.child_object_type
1419 AND HIERARCHY_TYPE = p_hierarchy_node_rec.hierarchy_type
1420 AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
1421 OR
1422 EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
1423 OR
1424 l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
1425 OR
1426 l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
1427 )
1428 -- AND NVL(RELATIONSHIP_ID, p_hierarchy_node_rec.relationship_id) = p_hierarchy_node_rec.relationship_id
1429 ORDER BY LEVEL_NUMBER ASC;
1430
1431 r_get_all_children c_get_all_children%ROWTYPE;
1432 l_child_rec_count_p NUMBER;
1433 l_child_rec_count_c NUMBER;
1434 l_parent_rec_count_p NUMBER;
1435 l_parent_rec_count_c NUMBER;
1436 */
1437
1438 BEGIN
1439
1440 /* -- Moved below logic into procedure do_update_link_pvt for Bug 4662744 (Nishant 30-Mar-2006)
1441 -- End dating of hierarchy in case of multiple parents was not correct
1442
1443 Select EFFECTIVE_START_DATE,EFFECTIVE_END_DATE into l_existing_start_date,l_existing_end_date
1444 from HZ_HIERARCHY_NODES
1445 where PARENT_ID=p_hierarchy_node_rec.parent_id
1446 AND CHILD_ID=p_hierarchy_node_rec.child_id
1447 AND LEVEL_NUMBER=1
1448 AND RELATIONSHIP_ID=p_hierarchy_node_rec.relationship_id ;
1449
1450 -- let's assume the effective dates of AAA->BBB are being updated
1451 -- assumption : start_date and end_date is always passed by relationship api
1452
1453 -------------------
1454 -- LINK MAINTENANCE
1455 -------------------
1456
1457 -- get all the parents of AAA including itself
1458 OPEN c_get_all_parents;
1459 FETCH c_get_all_parents INTO r_get_all_parents;
1460
1461 -- loop through all the parents
1462 WHILE c_get_all_parents%FOUND
1463 LOOP
1464 -- we open the cursor to get all the children of BBB (this will include BBB itself)
1465 OPEN c_get_all_children;
1466 FETCH c_get_all_children INTO r_get_all_children;
1467
1468 WHILE c_get_all_children%FOUND
1469 -- let's call it YYY
1470 LOOP
1471 UPDATE HZ_HIERARCHY_NODES
1472 SET EFFECTIVE_START_DATE = NVL(p_hierarchy_node_rec.effective_start_date, EFFECTIVE_START_DATE),
1473 EFFECTIVE_END_DATE = NVL(p_hierarchy_node_rec.effective_end_date, EFFECTIVE_END_DATE),
1474 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.last_updated_by,
1478 AND PARENT_TABLE_NAME = r_get_all_parents.parent_table_name
1475 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.last_update_date,
1476 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.last_update_login
1477 WHERE PARENT_ID = r_get_all_parents.parent_id
1479 AND PARENT_OBJECT_TYPE = r_get_all_parents.parent_object_type
1480 AND CHILD_ID = r_get_all_children.child_id
1481 AND CHILD_TABLE_NAME = r_get_all_children.child_table_name
1482 AND CHILD_OBJECT_TYPE = r_get_all_children.child_object_type
1483 AND HIERARCHY_TYPE = p_hierarchy_node_rec.hierarchy_type
1484 AND (EFFECTIVE_START_DATE BETWEEN l_existing_start_date AND l_existing_end_date
1485 OR
1486 EFFECTIVE_END_DATE BETWEEN l_existing_start_date AND l_existing_end_date
1487 OR
1488 l_existing_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
1489 OR
1490 l_existing_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
1491 );
1492
1493 FETCH c_get_all_children INTO r_get_all_children;
1494 END LOOP;
1495 CLOSE c_get_all_children;
1496 FETCH c_get_all_parents INTO r_get_all_parents;
1497 END LOOP;
1498 CLOSE c_get_all_parents;
1499 */
1500
1501 -------------------
1502 -- LINK MAINTENANCE
1503 -- Replaced above code with procedure do_update_link_pvt (Bug 4662744 (Nishant 30-Mar-2006))
1504 -------------------
1505 do_update_link_pvt(p_hierarchy_node_rec);
1506
1507
1508 ---------------------------------
1509 -- PARENT'S SELF NODE MAINTENANCE
1510 ---------------------------------
1511 maintain_self_node (
1512 p_node_id => p_hierarchy_node_rec.parent_id,
1513 p_hierarchy_type => p_hierarchy_node_rec.hierarchy_type,
1514 p_table_name => p_hierarchy_node_rec.parent_table_name,
1515 p_object_type => p_hierarchy_node_rec.parent_object_type,
1516 p_actual_content_source => p_hierarchy_node_rec.actual_content_source
1517 );
1518
1519
1520 --------------------------------
1521 -- CHILD'S SELF NODE MAINTENANCE
1522 --------------------------------
1523 maintain_self_node (
1524 p_node_id => p_hierarchy_node_rec.child_id,
1525 p_hierarchy_type => p_hierarchy_node_rec.hierarchy_type,
1526 p_table_name => p_hierarchy_node_rec.child_table_name,
1527 p_object_type => p_hierarchy_node_rec.child_object_type,
1528 p_actual_content_source => p_hierarchy_node_rec.actual_content_source
1529 );
1530
1531
1532 END do_update_link;
1533
1534
1535 /**********************************
1536 ****** Public Procedures ********
1537 ***********************************/
1538
1539 /**
1540 * PROCEDURE create_link
1541 *
1542 * DESCRIPTION
1543 * Creates a hierarchial relationship between two nodes.
1544 *
1545 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1546 *
1547 * ARGUMENTS
1548 * IN:
1549 * p_init_msg_list Initialize message stack if it is set to
1550 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1551 * p_hierarchy_node_rec Hierarchy node record.
1552 * IN/OUT:
1553 * OUT:
1554 * x_return_status Return status after the call. The status can
1555 * be FND_API.G_RET_STS_SUCCESS (success),
1556 * FND_API.G_RET_STS_ERROR (error),
1557 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1558 * x_msg_count Number of messages in message stack.
1559 * x_msg_data Message text if x_msg_count is 1.
1560 *
1561 * NOTES
1562 *
1563 * MODIFICATION HISTORY
1564 *
1565 * 31-JAN-00 Indrajit Sen o Created
1566 *
1567 */
1568
1569 PROCEDURE create_link(
1570 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1571 p_hierarchy_node_rec IN HIERARCHY_NODE_REC_TYPE,
1572 x_return_status OUT NOCOPY VARCHAR2,
1573 x_msg_count OUT NOCOPY NUMBER,
1574 x_msg_data OUT NOCOPY VARCHAR2
1575 ) IS
1576 l_debug_prefix VARCHAR2(30) := '';
1577 BEGIN
1578 -- standard start of API savepoint
1579 SAVEPOINT create_link;
1580 -- Check if API is called in debug mode. If yes, enable debug.
1581 --enable_debug;
1582
1583 -- Debug info.
1584 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1585 hz_utility_v2pub.debug(p_message=>'create_link (+)',
1586 p_prefix=>l_debug_prefix,
1587 p_msg_level=>fnd_log.level_procedure);
1588 END IF;
1589
1590 -- initialize message list if p_init_msg_list is set to TRUE.
1591 IF FND_API.to_Boolean(p_init_msg_list) THEN
1592 FND_MSG_PUB.initialize;
1593 END IF;
1594
1595 -- initialize API return status to success.
1596 x_return_status := FND_API.G_RET_STS_SUCCESS;
1597
1598 do_create_link(p_hierarchy_node_rec,
1599 x_return_status);
1600
1601 --Standard call to get message count and if count is 1, get message info.
1602 FND_MSG_PUB.Count_And_Get(
1606
1603 p_encoded => FND_API.G_FALSE,
1604 p_count => x_msg_count,
1605 p_data => x_msg_data);
1607 -- Debug info.
1608 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1609 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1610 p_msg_data=>x_msg_data,
1611 p_msg_type=>'WARNING',
1612 p_msg_level=>fnd_log.level_exception);
1613 END IF;
1614 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1615 hz_utility_v2pub.debug(p_message=>'create_link (-)',
1616 p_prefix=>l_debug_prefix,
1617 p_msg_level=>fnd_log.level_procedure);
1618 END IF;
1619
1620
1621 -- Check if API is called in debug mode. If yes, disable debug.
1622 --disable_debug;
1623
1624 EXCEPTION
1625 WHEN FND_API.G_EXC_ERROR THEN
1626 ROLLBACK TO create_link;
1627 x_return_status := FND_API.G_RET_STS_ERROR;
1628 FND_MSG_PUB.Count_And_Get(
1629 p_encoded => FND_API.G_FALSE,
1630 p_count => x_msg_count,
1631 p_data => x_msg_data);
1632
1633 -- Debug info.
1634 IF fnd_log.level_error >= fnd_log.g_current_runtime_level THEN
1635 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1636 p_msg_data=>x_msg_data,
1637 p_msg_type=>'ERROR',
1638 p_msg_level=>fnd_log.level_error);
1639 END IF;
1640
1641 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1642 hz_utility_v2pub.debug(p_message=> 'l_line_number = '||l_line_number||' create_link (-)' ,
1643 p_prefix=>l_debug_prefix,
1644 p_msg_level=>fnd_log.level_procedure);
1645 END IF;
1646
1647
1648 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1649 ROLLBACK TO create_link;
1650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1651 FND_MSG_PUB.Count_And_Get(
1652 p_encoded => FND_API.G_FALSE,
1653 p_count => x_msg_count,
1654 p_data => x_msg_data);
1655
1656 -- Debug info.
1657 IF fnd_log.level_error >= fnd_log.g_current_runtime_level THEN
1658 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1659 p_msg_data=>x_msg_data,
1660 p_msg_type=>'ERROR',
1661 p_msg_level=>fnd_log.level_error);
1662 END IF;
1663
1664 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1665 hz_utility_v2pub.debug(p_message=> 'l_line_number = '||l_line_number||' create_link (-)' ,
1666 p_prefix=>l_debug_prefix,
1667 p_msg_level=>fnd_log.level_procedure);
1668 END IF;
1669
1670
1671 WHEN OTHERS THEN
1672 ROLLBACK TO create_link;
1673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1674 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1675 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1676 FND_MSG_PUB.ADD;
1677 FND_MSG_PUB.Count_And_Get(
1678 p_encoded => FND_API.G_FALSE,
1679 p_count => x_msg_count,
1680 p_data => x_msg_data);
1681
1682 -- Debug info.
1683 IF fnd_log.level_error >= fnd_log.g_current_runtime_level THEN
1684 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1685 p_msg_data=>x_msg_data,
1686 p_msg_type=>'ERROR',
1687 p_msg_level=>fnd_log.level_error);
1688 END IF;
1689
1690 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1691 hz_utility_v2pub.debug(p_message=> 'l_line_number = '||l_line_number||' create_link (-)' ,
1692 p_prefix=>l_debug_prefix,
1693 p_msg_level=>fnd_log.level_procedure);
1694 END IF;
1695
1696
1697 END create_link;
1698
1699
1700 /**
1701 * PROCEDURE update_link
1702 *
1703 * DESCRIPTION
1704 * Updates a hierarchial relationship between two nodes.
1705 *
1706 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1707 *
1708 * ARGUMENTS
1709 * IN:
1710 * p_init_msg_list Initialize message stack if it is set to
1711 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1712 * p_hierarchy_node_rec Hierarchy node record.
1713 * IN/OUT:
1714 * OUT:
1715 * x_return_status Return status after the call. The status can
1716 * be FND_API.G_RET_STS_SUCCESS (success),
1717 * FND_API.G_RET_STS_ERROR (error),
1718 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1719 * x_msg_count Number of messages in message stack.
1720 * x_msg_data Message text if x_msg_count is 1.
1721 *
1722 * NOTES
1723 *
1724 * MODIFICATION HISTORY
1725 *
1726 * 31-JAN-00 Indrajit Sen o Created
1727 *
1728 */
1729
1730 PROCEDURE update_link(
1731 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1732 p_hierarchy_node_rec IN HIERARCHY_NODE_REC_TYPE,
1736 ) IS
1733 x_return_status OUT NOCOPY VARCHAR2,
1734 x_msg_count OUT NOCOPY NUMBER,
1735 x_msg_data OUT NOCOPY VARCHAR2
1737 l_debug_prefix VARCHAR2(30) := '';
1738 BEGIN
1739 -- standard start of API savepoint
1740 SAVEPOINT update_link;
1741
1742 -- Check if API is called in debug mode. If yes, enable debug.
1743 --enable_debug;
1744
1745 -- Debug info.
1746 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1747 hz_utility_v2pub.debug(p_message=>'update_link (+)',
1748 p_prefix=>l_debug_prefix,
1749 p_msg_level=>fnd_log.level_procedure);
1750 END IF;
1751
1752 -- initialize message list if p_init_msg_list is set to TRUE.
1753 IF FND_API.to_Boolean(p_init_msg_list) THEN
1754 FND_MSG_PUB.initialize;
1755 END IF;
1756
1757 -- initialize API return status to success.
1758 x_return_status := FND_API.G_RET_STS_SUCCESS;
1759
1760 do_update_link(p_hierarchy_node_rec,
1761 x_return_status);
1762
1763 --Standard call to get message count and if count is 1, get message info.
1764 FND_MSG_PUB.Count_And_Get(
1765 p_encoded => FND_API.G_FALSE,
1766 p_count => x_msg_count,
1767 p_data => x_msg_data);
1768
1769 -- Debug info.
1770 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1771 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1772 p_msg_data=>x_msg_data,
1773 p_msg_type=>'WARNING',
1774 p_msg_level=>fnd_log.level_exception);
1775 END IF;
1776 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1777 hz_utility_v2pub.debug(p_message=>'update_link (-)',
1778 p_prefix=>l_debug_prefix,
1779 p_msg_level=>fnd_log.level_procedure);
1780 END IF;
1781
1782 -- Check if API is called in debug mode. If yes, disable debug.
1783 --disable_debug;
1784
1785 EXCEPTION
1786 WHEN FND_API.G_EXC_ERROR THEN
1787 ROLLBACK TO update_link;
1788 x_return_status := FND_API.G_RET_STS_ERROR;
1789 FND_MSG_PUB.Count_And_Get(
1790 p_encoded => FND_API.G_FALSE,
1791 p_count => x_msg_count,
1792 p_data => x_msg_data);
1793
1794 -- Debug info.
1795 IF fnd_log.level_error >= fnd_log.g_current_runtime_level THEN
1796 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1797 p_msg_data=>x_msg_data,
1798 p_msg_type=>'ERROR',
1799 p_msg_level=>fnd_log.level_error);
1800 END IF;
1801
1802 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1803 hz_utility_v2pub.debug(p_message=> 'l_line_number = '||l_line_number||' update_link (-)' ,
1804 p_prefix=>l_debug_prefix,
1805 p_msg_level=>fnd_log.level_procedure);
1806
1807 END IF;
1808
1809 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1810 ROLLBACK TO update_link;
1811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1812 FND_MSG_PUB.Count_And_Get(
1813 p_encoded => FND_API.G_FALSE,
1814 p_count => x_msg_count,
1815 p_data => x_msg_data);
1816
1817 -- Debug info.
1818 IF fnd_log.level_error >= fnd_log.g_current_runtime_level THEN
1819 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1820 p_msg_data=>x_msg_data,
1821 p_msg_type=>'ERROR',
1822 p_msg_level=>fnd_log.level_error);
1823 END IF;
1824
1825 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1826 hz_utility_v2pub.debug(p_message=> 'l_line_number = '||l_line_number||' update_link (-)' ,
1827 p_prefix=>l_debug_prefix,
1828 p_msg_level=>fnd_log.level_procedure);
1829 END IF;
1830
1831
1832 WHEN OTHERS THEN
1833 ROLLBACK TO update_link;
1834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1835 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1836 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1837 FND_MSG_PUB.ADD;
1838 FND_MSG_PUB.Count_And_Get(
1839 p_encoded => FND_API.G_FALSE,
1840 p_count => x_msg_count,
1841 p_data => x_msg_data);
1842
1843 -- Debug info.
1844 IF fnd_log.level_error >= fnd_log.g_current_runtime_level THEN
1845 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1846 p_msg_data=>x_msg_data,
1847 p_msg_type=>'ERROR',
1848 p_msg_level=>fnd_log.level_error);
1849 END IF;
1850
1851 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1852 hz_utility_v2pub.debug(p_message=> 'l_line_number = '||l_line_number||' update_link (-)' ,
1853 p_prefix=>l_debug_prefix,
1854 p_msg_level=>fnd_log.level_procedure);
1855
1856 END IF;
1857
1858 END update_link;
1859
1860 /**
1861 * PROCEDURE
1865 * Procedure to convert a particular relationship type
1862 * convert_rel_type
1863 *
1864 * DESCRIPTION
1866 * to a hierarchical relationship type
1867 *
1868 * SCOPE - Public
1869 *
1870 * ARGUMENTS : IN:
1871 * p_rel_type
1872 * p_multi_parent_allowed
1873 * p_incl_unrelated_entities
1874 * OUT:
1875 * IN/ OUT:
1876 *
1877 * RETURNS :
1878 * Errbuf
1879 * Retcode
1880 *
1881 * NOTES : p_rel_type can be non-hierarchical relationship type
1882 * p_multi_parent_allowed is Y/N
1883 * p_incl_unrelated_entities is Y/N
1884 *
1885 * MODIFICATION HISTORY
1886 *
1887 * 31-JAN-00 Indrajit Sen o Created
1888
1889 *
1890 */
1891
1892 PROCEDURE convert_rel_type(
1893 errbuf OUT NOCOPY VARCHAR2,
1894 Retcode OUT NOCOPY VARCHAR2,
1895 p_rel_type IN VARCHAR2,
1896 p_multi_parent_allowed IN VARCHAR2,
1897 p_incl_unrelated_entities IN VARCHAR2
1898 )
1899 IS
1900 CURSOR c1 IS
1901 SELECT a.ROWID row_id, a.*
1902 FROM HZ_RELATIONSHIPS a
1903 WHERE RELATIONSHIP_TYPE = p_rel_type
1904 ORDER BY RELATIONSHIP_ID;
1905
1906 CURSOR c2 (p_rel_type VARCHAR2,
1907 p_forward_rel_code VARCHAR2,
1908 p_subject_type VARCHAR2,
1909 p_object_type VARCHAR2)
1910 IS
1911 SELECT *
1912 FROM HZ_RELATIONSHIP_TYPES
1913 WHERE RELATIONSHIP_TYPE = p_rel_type
1914 AND FORWARD_REL_CODE = p_forward_rel_code
1915 AND SUBJECT_TYPE = p_subject_type
1916 AND OBJECT_TYPE = p_object_type;
1917
1918 -- this cursor retrieves a parent for a given child in a particular hierarchy.
1919 -- it will be used for circularity check.
1920 CURSOR c_parent (p_child_id NUMBER, p_child_table_name VARCHAR2, p_child_object_type VARCHAR2,
1921 p_rel_type VARCHAR2, p_start_date DATE, p_end_date DATE)
1922 IS
1923 SELECT SUBJECT_ID,
1924 SUBJECT_TABLE_NAME,
1925 SUBJECT_TYPE
1926 FROM HZ_RELATIONSHIPS
1927 WHERE OBJECT_ID = p_child_id
1928 AND OBJECT_TABLE_NAME = p_child_table_name
1929 AND OBJECT_TYPE = p_child_object_type
1930 AND RELATIONSHIP_TYPE = p_rel_type
1931 AND DIRECTION_CODE = 'P'
1932 AND (START_DATE BETWEEN NVL(p_start_date, SYSDATE)
1933 AND NVL(p_end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
1934 OR
1935 END_DATE BETWEEN NVL(p_start_date, SYSDATE)
1936 AND NVL(p_end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
1937 OR
1938 NVL(p_start_date, SYSDATE) BETWEEN START_DATE AND END_DATE
1939 OR
1940 NVL(p_end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS')) BETWEEN START_DATE AND END_DATE
1941 );
1942
1943 r1 c1%ROWTYPE;
1944 r2 c2%ROWTYPE;
1945 r_parent c_parent%ROWTYPE;
1946 l_hierarchy_rec HZ_HIERARCHY_PUB.HIERARCHY_NODE_REC_TYPE;
1947 l_return_status VARCHAR2(1);
1948 l_msg_count NUMBER;
1949 l_msg_data VARCHAR2(2000);
1950 l_count NUMBER;
1951 l_parent_id NUMBER;
1952 l_parent_object_type VARCHAR2(30);
1953 l_parent_table_name VARCHAR2(30);
1954 l_child_id NUMBER;
1955 l_child_object_type VARCHAR2(30);
1956 l_child_table_name VARCHAR2(30);
1957 l_temp_parent_id NUMBER;
1958 l_temp_parent_table_name VARCHAR2(30);
1959 l_temp_parent_object_type VARCHAR2(30);
1960 l_parent_flag VARCHAR2(1);
1961 l_conc_status VARCHAR2(1) := 'S';
1962
1963
1964
1965 BEGIN
1966
1967 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Concurrent program ARHCRTHI - Convert relationship type to hierarchical.');
1968 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1969 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Options - ');
1970 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Relationship type : '||p_rel_type);
1971 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Multiple parent allowed : '||p_multi_parent_allowed);
1972 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Include unrelated entities : '||p_incl_unrelated_entities);
1973 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1974
1975 -- get all the relationships for this relationship type
1976 OPEN c1;
1977 FETCH c1 INTO r1;
1978
1979 WHILE c1%FOUND LOOP
1980 -- get the relationship type to determine parent/child
1981 OPEN c2(r1.relationship_type, r1.relationship_code, r1.subject_type, r1.object_type);
1982 FETCH c2 INTO r2;
1983
1984 -- decide who is parent and who is child in this relationship.
1985 -- if relationship type record is 'P' type, then subject is parent, else object
1986 IF r2.direction_code = 'P' THEN
1990 l_child_id := r1.object_id;
1987 l_parent_id := r1.subject_id;
1988 l_parent_table_name := r1.subject_table_name;
1989 l_parent_object_type := r1.subject_type;
1991 l_child_table_name := r1.object_table_name;
1992 l_child_object_type := r1.object_type;
1993 ELSIF r2.direction_code = 'C' THEN
1994 l_parent_id := r1.object_id;
1995 l_parent_table_name := r1.object_table_name;
1996 l_parent_object_type := r1.object_type;
1997 l_child_id := r1.subject_id;
1998 l_child_table_name := r1.subject_table_name;
1999 l_child_object_type := r1.subject_type;
2000 END IF;
2001
2002 IF p_multi_parent_allowed = 'N' THEN
2003 BEGIN
2004 SELECT 1 INTO l_count
2005 FROM HZ_RELATIONSHIPS
2006 WHERE OBJECT_ID = l_child_id
2007 AND OBJECT_TABLE_NAME = l_child_table_name
2008 AND OBJECT_TYPE = l_child_object_type
2009 AND RELATIONSHIP_TYPE = r1.relationship_type
2010 AND DIRECTION_CODE = 'P'
2011 AND (START_DATE BETWEEN NVL(r1.start_date, SYSDATE)
2012 AND NVL(r1.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
2013 OR
2014 END_DATE BETWEEN NVL(r1.start_date, SYSDATE)
2015 AND NVL(r1.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
2016 OR
2017 NVL(r1.start_date, SYSDATE) BETWEEN START_DATE AND END_DATE
2018 OR
2019 NVL(r1.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS')) BETWEEN START_DATE AND END_DATE
2020 );
2021
2022 EXCEPTION
2023 WHEN NO_DATA_FOUND THEN
2024 -- no parent found, proceed
2025 NULL;
2026 WHEN TOO_MANY_ROWS then
2027 -- there is already a parent, so raise error
2028 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
2029 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Multiple parent found for the following child :');
2030 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Child ID : '||l_child_id);
2031 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Chiild Type : '||l_child_object_type);
2032 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
2033 l_conc_status := 'E';
2034 EXIT;
2035 END;
2036 END IF;
2037
2038 -- Check for circular relationships in the hierarchy.
2039 -- If circularity is found, reported as error.
2040 l_parent_flag := 'Y';
2041 l_temp_parent_id := l_parent_id;
2042 l_temp_parent_table_name := l_parent_table_name;
2043 l_temp_parent_object_type := l_parent_object_type;
2044 WHILE l_parent_flag <> 'N' LOOP
2045 OPEN c_parent (l_temp_parent_id, l_temp_parent_table_name, l_temp_parent_object_type, r1.relationship_type, r1.start_date, r1.end_date);
2046 FETCH c_parent INTO r_parent;
2047 IF c_parent%NOTFOUND THEN
2048 l_parent_flag := 'N';
2049 ELSE
2050 l_temp_parent_id := r_parent.subject_id;
2051 l_temp_parent_table_name := r_parent.subject_table_name;
2052 l_temp_parent_object_type := r_parent.subject_type;
2053 END IF;
2054 IF l_temp_parent_id = l_child_id THEN
2055 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
2056 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Circularity exists for the following relationship record : ');
2057 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Subject ID : '||r1.subject_id);
2058 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Subject Type : '||r1.subject_type);
2059 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Object ID : '||r1.object_id);
2060 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Object Type : '||r1.object_type);
2061 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Relationship Code : '||r1.relationship_code);
2062 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
2063 l_conc_status := 'E';
2064 CLOSE c_parent;
2065 EXIT;
2066 END IF;
2067 CLOSE c_parent;
2068 END LOOP;
2069
2070 IF r2.direction_code = 'P' THEN
2071 -- record is the parent record
2072 -- assign the subject to parent for hierarchy
2073 l_hierarchy_rec.hierarchy_type := r1.relationship_type;
2074 l_hierarchy_rec.parent_id := r1.subject_id;
2075 l_hierarchy_rec.parent_table_name := r1.subject_table_name;
2076 l_hierarchy_rec.parent_object_type := r1.subject_type;
2077 l_hierarchy_rec.child_id := r1.object_id;
2078 l_hierarchy_rec.child_table_name := r1.object_table_name;
2079 l_hierarchy_rec.child_object_type := r1.object_type;
2080 l_hierarchy_rec.effective_start_date := r1.start_date;
2081 l_hierarchy_rec.effective_end_date := NVL(r1.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'));
2082 l_hierarchy_rec.relationship_id := r1.relationship_id;
2083 l_hierarchy_rec.status := NVL(r1.status, 'A');
2084 ELSE
2085 -- record is the child record
2086 -- assign the object to parent
2087 l_hierarchy_rec.hierarchy_type := r1.relationship_type;
2088 l_hierarchy_rec.parent_id := r1.object_id;
2089 l_hierarchy_rec.parent_table_name := r1.object_table_name;
2090 l_hierarchy_rec.parent_object_type := r1.object_type;
2091 l_hierarchy_rec.child_id := r1.subject_id;
2092 l_hierarchy_rec.child_table_name := r1.subject_table_name;
2093 l_hierarchy_rec.child_object_type := r1.subject_type;
2094 l_hierarchy_rec.effective_start_date := r1.start_date;
2095 l_hierarchy_rec.effective_end_date := NVL(r1.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'));
2096 l_hierarchy_rec.relationship_id := r1.relationship_id;
2097 l_hierarchy_rec.status := NVL(r1.status, 'A');
2098 END IF;
2099
2100 HZ_HIERARCHY_PUB.create_link(
2101 p_init_msg_list => FND_API.G_FALSE,
2102 p_hierarchy_node_rec => l_hierarchy_rec,
2103 x_return_status => l_return_status,
2104 x_msg_count => l_msg_count,
2105 x_msg_data => l_msg_data
2106 );
2107
2108 CLOSE c2;
2109
2110 IF r2.direction_code = 'P' THEN
2111 UPDATE HZ_RELATIONSHIPS SET DIRECTION_CODE = 'P' WHERE ROWID = r1.row_id;
2112 ELSE
2113 UPDATE HZ_RELATIONSHIPS SET DIRECTION_CODE = 'C' WHERE ROWID = r1.row_id;
2114 END IF;
2115
2116 -- one call is to by-pass the second record which is identical but reverse one.
2117 FETCH c1 INTO r1;
2118 IF r2.direction_code = 'P' THEN
2119 UPDATE HZ_RELATIONSHIPS SET DIRECTION_CODE = 'C' WHERE ROWID = r1.row_id;
2120 ELSE
2121 UPDATE HZ_RELATIONSHIPS SET DIRECTION_CODE = 'P' WHERE ROWID = r1.row_id;
2122 END IF;
2123
2124 FETCH c1 INTO r1;
2125
2126 END LOOP;
2127
2128 CLOSE c1;
2129
2130 IF l_conc_status = 'E' THEN
2131 ROLLBACK;
2132 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
2133 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Conversion of relationship type to hierarchical failed. ');
2134 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
2135 ELSE
2136 UPDATE HZ_RELATIONSHIP_TYPES
2137 SET HIERARCHICAL_FLAG = 'Y',
2138 MULTIPLE_PARENT_ALLOWED = NVL(p_multi_parent_allowed, 'N'),
2139 INCL_UNRELATED_ENTITIES = NVL(p_incl_unrelated_entities, 'N')
2140 WHERE RELATIONSHIP_TYPE = p_rel_type;
2141
2142 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
2143 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Successfully converted relationship type to hierarchical. ');
2144 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
2145 COMMIT;
2146 END IF;
2147
2148 END convert_rel_type;
2149
2150 END HZ_HIERARCHY_PUB;
[Home] [Help]