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