[Home] [Help]
PACKAGE BODY: APPS.PQH_GENERIC_HIERARCHY_PACKAGE
Source
1 package body pqh_generic_hierarchy_package
2 /* $Header: pqghrpkg.pkb 120.2 2006/02/13 15:49:17 nsanghal noship $ */
3 as
4 --
5 g_debug boolean := hr_utility.debug_enabled;
6 g_package varchar2(72) := 'pqh_generic_hierarchy_package';
7 --
8 --
9 -- ----------------------------------------------------------------------------
10 -- |-------------------------< chk_type_context >----------------------------|
11 -- ----------------------------------------------------------------------------
12 function chk_type_context(p_type in varchar2,
13 p_flexfield_name in varchar2)
14 return varchar2
15 is
16 l_count number;
17 l_proc varchar2(72) ;
18
19 Cursor csr_type_context
20 is
21 Select '1'
22 from FND_DESCR_FLEX_CONTEXTS_VL
23 where GLOBAL_FLAG = 'N'
24 AND APPLICATION_ID = 800
25 AND DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
26 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_type
27 AND ENABLED_FLAG = 'Y';
28
29 --
30 l_return varchar2(10);
31 --
32 BEGIN
33 g_debug := hr_utility.debug_enabled;
34 if g_debug then
35 l_proc := g_package||'chk_type_context';
36 hr_utility.set_location('Entering '||l_proc,10);
37 end if;
38
39 Open csr_type_context;
40 --
41 Fetch csr_type_context into l_count;
42 --
43 If csr_type_context%NOTFOUND Then
44 l_return := 'N';
45 Else
46 l_return := 'Y';
47 End If;
48 --
49 Close csr_type_context;
50 --
51 if g_debug then
52 hr_utility.set_location('Leaving '||l_proc,20);
53 end if;
54 --
55 return l_return;
56 --
57 EXCEPTION
58 WHEN OTHERS THEN
59 return 'N';
60 End chk_type_context;
61 --
62 --
63 -- ----------------------------------------------------------------------------
64 -- |----------------------< chk_if_parent_node_type >-------------------------|
65 -- ----------------------------------------------------------------------------
66 function chk_if_parent_node_type (p_node_type in varchar2,
67 p_hierarchy_type in varchar2)
68 return varchar2
69 is
70 l_count number;
71 l_proc varchar2(72);
72 --
73 Cursor csr_child_node_types
74 is
75 Select '1'
76 from
77 per_gen_hier_node_types
78 Where
79 ( parent_node_type = p_node_type
80 Or
81 parent_node_type is null and p_node_type is null)
82 and hierarchy_type = p_hierarchy_type;
83 --
84 l_return varchar2(10);
85 --
86 BEGIN
87 g_debug := hr_utility.debug_enabled;
88 if g_debug then
89 l_proc := g_package||'chk_child_node_type_exists';
90 hr_utility.set_location('Entering '||l_proc,10);
91 end if;
92
93 Open csr_child_node_types;
94 --
95 Fetch csr_child_node_types into l_count;
96 --
97 If csr_child_node_types%NOTFOUND Then
98 l_return := 'N';
99 Else
100 l_return := 'Y';
101 End If;
102 --
103 Close csr_child_node_types;
104 --
105 if g_debug then
106 hr_utility.set_location('Leaving '||l_proc,20);
107 end if;
108 --
109 return l_return;
110 --
111 EXCEPTION
112 WHEN OTHERS THEN
113 return 'N';
114 End chk_if_parent_node_type;
115 --
116 --
117 -- ----------------------------------------------------------------------------
118 -- |----------------------< node_value_set_dyn_query >------------------------|
119 -- ----------------------------------------------------------------------------
120 Function Node_Value_Set_Dyn_Query(p_child_node_type IN Varchar2,
121 p_parent_node_id IN Number,
122 p_hierarchy_type IN Varchar2
123 )
124 Return Varchar2
125 Is
126 --
127 l_value_set_id Number(10);
128 l_dyn_query varchar2(2000);
129 l_proc varchar2(72);
130 l_valid varchar2(1);
131 --
132 Begin
133 --
134 g_debug := hr_utility.debug_enabled;
135 if g_debug then
136 l_proc := g_package||'node_value_set_dyn_query';
137 hr_utility.set_location('Entering:'||l_proc, 10);
138 end if;
139 l_value_set_id := Get_Value_Set_Id(p_child_node_type, p_parent_node_id, p_hierarchy_type);
140 --
141 if l_value_set_id = -2 then
142 l_dyn_query := 'INVALID';
143 if g_debug then
144 hr_utility.set_location('Value Set is Invalid', 20);
145 end if;
146 elsif l_value_set_id = -1 then
147 l_dyn_query := 'NULL';
148 if g_debug then
149 hr_utility.set_location('No Validation', 30);
150 end if;
151 else
152 l_dyn_query := get_sql_from_vset_id(l_value_set_id);
153 if g_debug then
154 hr_utility.set_location('Value Set Query Available, Checking Validity', 40);
155 end if;
156 l_valid := is_valid_sql(l_dyn_query);
157 if (l_valid = 'N') then
158 l_dyn_query := 'INVALID';
159 if g_debug then
160 hr_utility.set_location('Value Set Query is Invalid', 50);
161 end if;
162 end if;
163 end if;
164 --
165 if g_debug then
166 hr_utility.set_location('Leaving:'||l_proc, 50);
167 end if;
168 --
169 Return l_dyn_query;
170 End Node_Value_Set_Dyn_Query;
171 --
172 --
173 -- ----------------------------------------------------------------------------
174 -- |---------------------------< get_node_value >-----------------------------|
175 -- ----------------------------------------------------------------------------
176 Function Get_Node_Value (p_entity_id IN varchar2,
177 p_parent_node_id IN Number,
178 p_child_node_id IN Number)
179 Return varchar2
180 Is
181 --
182 cursor csr_node_type_id (p_child_node_type IN varchar2,
183 p_parent_node_type IN varchar2,
184 p_hierarchy_type IN varchar2)
185 is
186 Select hier_node_type_id
187 From per_gen_hier_node_types
188 Where hierarchy_type = p_hierarchy_type
189 And ( parent_node_type = p_parent_node_type
190 Or
191 (parent_node_type is null and p_parent_node_type is null)
192 )
193 And child_node_type = p_child_node_type;
194 --
195 l_child_node_type varchar2(30);
196 l_parent_node_type varchar2(30);
197 l_hierarchy_type varchar2(30);
198 l_Node_Value varchar2(2000);
199 l_Hier_Node_Type_Id number(15);
200 l_proc varchar2(72);
201 --
202 Begin
203 --
204 g_debug := hr_utility.debug_enabled;
205 if g_debug then
206 l_proc := g_package||'get_node_value';
207 hr_utility.set_location('Entering:'||l_proc, 10);
208 end if;
209 --
210 if p_parent_node_id is not null then
211 l_hierarchy_type := Get_Hierarchy_Type(p_parent_node_id);
212 elsif p_child_node_id is not null then
213 l_hierarchy_type := Get_Hierarchy_Type(p_child_node_id);
214 else
215 if g_debug then
216 hr_utility.set_location('Both parent and child node ids are null',20);
217 end if;
218 End if;
219
220 l_parent_node_type := Get_Node_Type(p_parent_node_id);
221 l_child_node_type := Get_Node_Type(p_child_node_id);
222 --
223 --Determine the key in the per_gen_hier_node_types table
224 --
225 Open csr_node_type_id(l_child_node_type,l_parent_node_type,l_hierarchy_type);
226 Fetch csr_node_type_id into l_hier_node_type_id;
227 close csr_node_type_id;
228 --
229 --Calling the calendar hierarchy api to get the display value of the node
230 --
231 l_Node_Value := get_display_value
232 (p_entity_id => p_entity_id,
233 p_node_type_id => l_hier_node_type_id);
234 if (l_Node_Value = 'NULL') then
235 l_Node_Value := p_entity_id;
236 if g_debug then
237 hr_utility.set_location('No validation, Value same as entity id', 30);
238 end if;
239 end if;
240 --
241 if g_debug then
242 hr_utility.set_location('Leaving:'||l_proc,40);
243 end if;
244 --
245 Return l_Node_Value;
246 --
247 Exception
248 When others then
249 if g_debug then
250 hr_utility.set_location('Unexpected Error', 50);
251 end if;
252 raise;
253 End Get_Node_Value;
254 --
255 --
256 -- ----------------------------------------------------------------------------
257 -- |----------------------------< get_node_type >-----------------------------|
258 -- ----------------------------------------------------------------------------
259 Function Get_Node_Type(p_hierarchy_node_id IN Number)
260 Return Varchar2
261 Is
262 --
263 Cursor csr_node_type
264 Is
265 Select Node_Type
266 From per_gen_hierarchy_nodes
267 Where hierarchy_node_id = p_hierarchy_node_id;
268 --
269 l_node_type Varchar2(30);
270 l_proc Varchar2(72);
271 --
272 Begin
273 --
274 g_debug := hr_utility.debug_enabled;
275 if g_debug then
276 l_proc := g_package||'get_node_type';
277 hr_utility.set_location('Entering:'||l_proc, 10);
278 end if;
279 --
280 Open csr_node_type;
281 Fetch csr_node_type into l_node_type;
282 Close csr_node_type;
283 --
284 if g_debug then
285 hr_utility.set_location('Leaving:'||l_proc, 20);
286 end if;
287 Return l_node_type;
288 End Get_Node_Type;
289 --
290 --
291 --
292 -- ----------------------------------------------------------------------------
293 -- |----------------------------< get_hierarchy_type >-----------------------|
294 -- ----------------------------------------------------------------------------
295
296 Function Get_Hierarchy_Type(p_hierarchy_node_id IN Number)
297 Return Varchar2
298 Is
299
300 Cursor csr_hierarchy_type
301 Is
302 Select ghr.type
303 from
304 per_gen_hierarchy ghr,
305 per_gen_hierarchy_versions gvr,
306 per_gen_hierarchy_nodes gnd
307 where
308 gnd.hierarchy_node_id = p_hierarchy_node_id
309 and gnd.hierarchy_version_id = gvr.hierarchy_version_id
310 and gvr.hierarchy_id = ghr.hierarchy_id;
311 --
312 l_hierarchy_type Varchar2(30);
313 l_proc Varchar2(72);
314 --
315 Begin
316 --
317 g_debug := hr_utility.debug_enabled;
318 if g_debug then
319 l_proc := g_package||'get_hierarchy_type';
320 hr_utility.set_location('Entering:'||l_proc, 10);
321 end if;
322 --
323 Open csr_hierarchy_type;
324 Fetch csr_hierarchy_type into l_hierarchy_type;
325 Close csr_hierarchy_type;
326 --
327 if g_debug then
328 hr_utility.set_location('Leaving:'||l_proc, 20);
329 end if;
330
331 Return l_hierarchy_type;
332 --
333 End Get_Hierarchy_Type;
334 --
335 --
336 --
337 -- ----------------------------------------------------------------------------
338 -- |----------------------------< get_value_set_id >--------------------------|
339 -- ----------------------------------------------------------------------------
340
341 Function Get_Value_Set_Id (p_child_node_type IN Varchar2,
342 p_parent_node_id IN Number,
343 p_hierarchy_type IN Varchar2 )
344 Return Number
345 Is
346 --
347 Cursor csr_value_set_id (p_value_set_name IN Varchar2)
348 Is
349 Select FLEX_VALUE_SET_ID
350 From FND_FLEX_VALUE_SETS
351 Where validation_type = 'F'
352 And
353 FLEX_VALUE_SET_NAME = p_value_set_name;
354
355 Cursor csr_value_set_name(p_parent_node_type IN Varchar2)
356 IS
357 Select CHILD_VALUE_SET
358 from per_gen_hier_node_types
359 where CHILD_NODE_TYPE = p_child_node_type
360 and ( (PARENT_NODE_TYPE = p_parent_node_type)
361 Or
362 (PARENT_NODE_TYPE is null And p_parent_node_type is null) )
363 and HIERARCHY_TYPE = p_hierarchy_type;
364
365 l_value_set_id Number(10);
366 l_value_set_name Varchar2(30);
367 l_parent_node_type Varchar2(30);
368 l_proc Varchar2(72);
369 --
370 Begin
371 --
372 g_debug := hr_utility.debug_enabled;
373 if g_debug then
374 l_proc := g_package||'get_value_set_id';
375 hr_utility.set_location('Entering:'||l_proc, 10);
376 end if;
377 l_parent_node_type := Get_Node_Type (p_parent_node_id);
378 --
379 Open csr_value_set_name(l_parent_node_type);
380 Fetch csr_value_set_name into l_value_set_name;
381 Close csr_value_set_name;
382
383 if (upper(l_value_set_name) = 'NULL') then
384 if g_debug then
385 hr_utility.set_location('No Validation', 20);
386 end if;
387 return -1;
388 end if;
389
390 Open csr_value_set_id(l_value_set_name);
391 Fetch csr_value_set_id into l_value_set_id;
392 If csr_value_set_id%NotFound then
393 If g_debug then
394 hr_utility.set_location('Invalid Value Set', 30);
395 end if;
396 l_value_set_id := -2;
397 End if;
398
399 Close csr_value_set_id;
400 --
401 if g_debug then
402 hr_utility.set_location('Leaving:'||l_proc, 40);
403 end if;
404 Return l_value_set_id;
405
406 End Get_Value_Set_Id;
407 --
408 -- ----------------------------------------------------------------------------
409 -- |--------------------------< get_sql_from_vset_id >------------------------|
410 -- ----------------------------------------------------------------------------
411 --
412 FUNCTION get_sql_from_vset_id(p_vset_id IN NUMBER) RETURN VARCHAR2 IS
413 --
414 l_v_r fnd_vset.valueset_r;
415 l_v_dr fnd_vset.valueset_dr;
416 l_str varchar2(4000);
417 l_whr varchar2(4000);
418 l_proc varchar2(72);
419 --
420 BEGIN
421 --
422 g_debug := hr_utility.debug_enabled;
423 if g_debug then
424 l_proc := g_package||'get_sql_from_vset_id';
425 hr_utility.set_location('Entering:'||l_proc, 10);
426 end if;
427 fnd_vset.get_valueset(valueset_id => p_vset_id ,
428 valueset => l_v_r,
429 format => l_v_dr);
430 --
431 if g_debug then
432 hr_utility.set_location('Got Valueset', 15);
433 end if;
434 --
435 l_whr := l_v_r.table_info.where_clause ;
436 l_str := rtrim('select '||l_v_r.table_info.id_column_name ||' Entityid, '
437 ||l_v_r.table_info.value_column_name ||' Nodename from '
438 ||l_v_r.table_info.table_name ||' '||l_whr);
439
440 -- substitute the BG if required.
441 l_str := REPLACE(l_str,':$PROFILES$.PER_BUSINESS_GROUP_ID',fnd_profile.value('
442 PER_BUSINESS_GROUP_ID'));
443 --
444 if g_debug then
445 hr_utility.set_location('Leaving:'||l_proc, 20);
446 end if;
447 --
448 RETURN (l_str);
449 --
450 END get_sql_from_vset_id;
451 --
452 --
453 -- ----------------------------------------------------------------------------
454 -- |-------------------------< chk_if_structure_exists >----------------------|
455 -- ----------------------------------------------------------------------------
456 Function chk_if_structure_exists(p_hierarchy_type IN varchar2)
457 Return Varchar2
458 Is
459
460 l_exists Varchar2(1);
461 l_proc Varchar2(72);
462
463 Cursor csr_chk_structure_exists
464 Is
465 Select 'x'
466 From Per_Gen_Hier_Node_Types
467 Where Hierarchy_Type = p_hierarchy_type;
468
469 Begin
470 --
471 g_debug := hr_utility.debug_enabled;
472 if g_debug then
473 l_proc := g_package||'chk_if_structure_exists';
474 hr_utility.set_location('Entering:'||l_proc, 10);
475 end if;
476 Open csr_chk_structure_exists;
477 Fetch csr_chk_structure_exists into l_exists;
478 if g_debug then
479 hr_utility.set_location('Leaving:'||l_proc, 20);
480 end if;
481 If csr_chk_structure_exists%NotFound then
482 Close csr_chk_structure_exists;
483 Return('N');
484 Else
485 Close csr_chk_structure_exists;
486 Return('Y');
487 End if;
488
489 End chk_if_structure_exists;
490 --
491 -- ----------------------------------------------------------------------------
492 -- |--------------------------< chk_version_exists >--------------------------|
493 -- ----------------------------------------------------------------------------
494 --
495 Procedure chk_version_exists(p_hierarchy_id in Number,
496 p_effective_date in Date)
497 Is
498 --
499
500 Cursor csr_version_exists
501 is
502 select '1'
503 from
504 per_gen_hierarchy_versions
505 Where
506 hierarchy_id = p_hierarchy_id
507 and p_effective_date between date_from and nvl(date_to,p_effective_date);
508
509 --
510 l_exists varchar2(1);
511 l_proc varchar2(72);
512 Begin
513 --
514 g_debug := hr_utility.debug_enabled;
515 if g_debug then
516 l_proc := g_package||'chk_version_exists';
517 hr_utility.set_location('Entering:'||l_proc, 10);
518 end if;
519 --
520 -- Initialize message pub
521 --
522 fnd_msg_pub.initialize;
523 --
524 -- check for version existance
525 --
526 Open csr_version_exists;
527 Fetch csr_version_exists into l_exists;
528 if(csr_version_exists%NotFound) then
529 -- Raise Error as we need atleast one version for the copy to happen.
530 close csr_version_exists;
531 if g_debug then
532 hr_utility.set_location('Error: No version found', 20);
533 end if;
534 fnd_message.set_name('PQH', 'PQH_GHR_EFF_DATE_NO_VERSION');
535 fnd_message.raise_error;
536 else
537 close csr_version_exists;
538 end if;
539 --
540 if g_debug then
541 hr_utility.set_location('Leaving:'||l_proc, 30);
542 end if;
543
544 --
545 Exception
546 when others
547 then
548 fnd_msg_pub.add;
549 End chk_version_exists;
550 --
551 -- ----------------------------------------------------------------------------
552 -- |----------------------< create_lookup_and_shared_type>--------------------|
553 -- ----------------------------------------------------------------------------
554 --
555 Procedure create_lookup_and_shared_type
556 ( p_lookup_code in varchar2
557 ,p_meaning in varchar2
558 ,p_description in varchar2
559 ) IS
560 --
561 -- Local Variable Declaration
562 --
563 l_proc varchar2(80) ;
564 l_return_status varchar2(1) := 'N';
565 Begin
566 --
567 g_debug := hr_utility.debug_enabled;
568 if g_debug then
569 l_proc := g_package||'create_lookup_and_shared_type';
570 hr_utility.set_location('Entering:'||l_proc, 10);
571 end if;
572
573 --
574 -- Issue a Savepoint
575 --
576 Savepoint lookup_and_shared_type;
577 --
578 -- Create the HIERARCHY_TYPE lookup value
579 --
580 create_lookup_value(p_lookup_type => 'HIERARCHY_TYPE',
581 p_lookup_code => p_lookup_code,
582 p_meaning => p_meaning,
583 p_description => p_description,
584 p_return_status => l_return_status);
585 --
586 -- Do not proceed if there are errors
587 --
588 if (l_return_status = 'Y') then
589 --
590 if g_debug then
591 hr_utility.set_location('Lookup Created', 20);
592 end if;
593 --
594 -- Create the shared type entry
595 --
596 create_shared_type(p_lookup_code => p_lookup_code,
597 p_meaning => p_meaning);
598 --
599 if g_debug then
600 hr_utility.set_location('Shared Type Created', 30);
601 end if;
602 end if;
603 --
604 if g_debug then
605 hr_utility.set_location('Leaving:'||l_proc, 40);
606 end if;
607 --
608 Exception
609 When others
610 then
611 --
612 Rollback to lookup_and_shared_type;
613 if g_debug then
614 hr_utility.set_location('An Error has occurred:'||l_proc, 50);
615 end if;
616 --
617 --
618 End create_lookup_and_shared_type;
619 --
620 -- ----------------------------------------------------------------------------
621 -- |--------------------------< create_lookup_value >-------------------------|
622 -- ----------------------------------------------------------------------------
623 --
624 Procedure create_lookup_value
625 ( p_lookup_type in varchar2
626 ,p_lookup_code in varchar2
627 ,p_meaning in varchar2
628 ,p_description in varchar2
629 ,p_return_status out NOCOPY varchar2
630 ) IS
631 --
632 -- Declare Cursors and local variables
633 --
634 --
635 -- Check if the lookup code exists already
636 -- for the lookup_type
637 --
638 CURSOR csr_lookup_code_unique IS
639 select 'x' from HR_LOOKUPS
640 where lookup_type = p_lookup_type
641 and lookup_code = p_lookup_code;
642 --
643 -- Check if the lookup meaning exists already
644 -- for the lookup_type
645 --
646 CURSOR csr_lookup_meaning_unique IS
647 select 'x' from HR_LOOKUPS
648 where lookup_type = p_lookup_type
649 and meaning = p_meaning;
650
651 --
652 l_proc varchar2(80);
653 l_rowid varchar2(255) := null;
654 l_lookup_code varchar2(30);
655 l_dummy varchar2(1);
656 --
657 Begin
658 --
659 g_debug := hr_utility.debug_enabled;
660 if g_debug then
661 l_proc := g_package||'create_lookup_value';
662 hr_utility.set_location('Entering:'|| l_proc, 10);
663 end if;
664 --
665 -- Issue a savepoint
666 --
667 savepoint create_lookup_value;
668 --
669 -- Enable multi messaging
670 --
671 if not hr_multi_message.is_message_list_enabled then
672 hr_multi_message.enable_message_list;
673 end if;
674 --
675 -- check that lookup type about to be created is either of type
676 -- HIERARCHY_TYPE or HIERARCHY_NODE_TYPE only
677 --
678 if p_lookup_type not in ('HIERARCHY_TYPE','HIERARCHY_NODE_TYPE') then
679 if g_debug then
680 hr_utility.set_location('Error: Invalid lookup Type', 20);
681 end if;
682 fnd_message.set_name('PQH','PQH_GHR_INVALID_LOOKUP_TYPE');
683 hr_multi_message.add;
684 else
685 --
686 -- Code has been supplied so lets check its unique
687 -- within the lookup type before proceeding...
688 open csr_lookup_code_unique;
689 fetch csr_lookup_code_unique into l_dummy;
690 if csr_lookup_code_unique%found then
691 close csr_lookup_code_unique;
692 if g_debug then
693 hr_utility.set_location('Error: Lookup code already exists', 30);
694 end if;
695 -- raise error as the supplied lookup_code already exists
696 fnd_message.set_name('PQH','PQH_GHR_LOOKUP_CODE_EXISTS');
697 fnd_message.set_token('TYPE',p_lookup_type);
698 fnd_message.set_token('CODE',p_lookup_code);
699 hr_multi_message.add;
700 else
701 close csr_lookup_code_unique;
702 l_lookup_code := p_lookup_code;
703 end if;
704 --
705 -- Check that the meaning for the lookup is unique
706 -- within the lookup type before proceeding...
707 open csr_lookup_meaning_unique;
708 fetch csr_lookup_meaning_unique into l_dummy;
709 if csr_lookup_meaning_unique%found then
710 close csr_lookup_meaning_unique;
711 if g_debug then
712 hr_utility.set_location('Error: Meaning Already Exists', 40);
713 end if;
714 -- raise error as the supplied meaning already exists
715 fnd_message.set_name('PQH','PQH_GHR_LOOKUP_MEANING_EXISTS');
716 fnd_message.set_token('TYPE',p_lookup_type);
717 fnd_message.set_token('MEANING',p_meaning);
718 hr_multi_message.add;
719 else
720 close csr_lookup_meaning_unique;
721 l_lookup_code := p_lookup_code;
722 end if;
723 end if;
724 --
725 -- Stop processing if errors encountered
726 --
727 hr_multi_message.end_validation_set;
728 --
729 if g_debug then
730 hr_utility.set_location('No Validation Failures so far', 50);
731 end if;
732 -- Now we attempt to create an fnd_lookup_values record for the node using the
733 -- supplied code and user supplied values for p_node_name (meaning)
734 -- and p_description (description) for p_lookup_type (lookup type)
735
736 fnd_lookup_values_pkg.INSERT_ROW(X_ROWID => l_rowid,
737 X_SECURITY_GROUP_ID => 0,
738 X_LOOKUP_TYPE => p_lookup_type,
739 X_VIEW_APPLICATION_ID => 3,
740 X_LOOKUP_CODE => l_lookup_code,
741 X_TAG => null,
742 X_ATTRIBUTE_CATEGORY => null,
743 X_ATTRIBUTE1 => null,
744 X_ATTRIBUTE2 => null,
745 X_ATTRIBUTE3 => null,
746 X_ATTRIBUTE4 => null,
747 X_ENABLED_FLAG => 'Y',
748 X_START_DATE_ACTIVE => null,
749 X_END_DATE_ACTIVE => null,
750 X_TERRITORY_CODE => null,
751 X_ATTRIBUTE5 => null,
752 X_ATTRIBUTE6 => null,
753 X_ATTRIBUTE7 => null,
754 X_ATTRIBUTE8 => null,
755 X_ATTRIBUTE9 => null,
756 X_ATTRIBUTE10 => null,
757 X_ATTRIBUTE11 => null,
758 X_ATTRIBUTE12 => null,
759 X_ATTRIBUTE13 => null,
760 X_ATTRIBUTE14 => null,
761 X_ATTRIBUTE15 => null,
762 X_MEANING => p_meaning,
763 X_DESCRIPTION => p_description,
764 X_CREATION_DATE => SYSDATE ,
765 X_CREATED_BY => fnd_global.user_id,
766 X_LAST_UPDATED_BY => fnd_global.user_id,
767 X_LAST_UPDATE_DATE => SYSDATE,
768 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
769
770 --
771 -- Set return value to 'Y'
772 --
773 p_return_status := 'Y';
774 --
775 hr_multi_message.disable_message_list;
776 if g_debug then
777 hr_utility.set_location('Leaving:'||l_proc, 60);
778 end if;
779 exception
780 when hr_multi_message.error_message_exist then
781 --
782 -- Error message(s) exist
783 --
784 rollback to create_lookup_value;
785 p_return_status := 'N';
786 hr_multi_message.disable_message_list;
787 if g_debug then
788 hr_utility.set_location('Errors Exist:'||l_proc, 70);
789 end if;
790 when others then
791 --
792 -- An unexpected error has occured
793 --
794 rollback to create_lookup_value;
795 p_return_status := 'N';
796 hr_multi_message.disable_message_list;
797 if g_debug then
798 hr_utility.set_location('Unexpected Error:'||l_proc, 80);
799 end if;
800 fnd_message.set_name('PQH','PQH_GHR_LOOKUP_INS_FAIL');
801 fnd_message.set_token('TYPE',p_lookup_type);
802 fnd_message.set_token('CODE',p_lookup_code);
803 fnd_msg_pub.add;
804 end create_lookup_value;
805 -- ----------------------------------------------------------------------------
806 -- |--------------------------< update_lookup_value >-------------------------|
807 -- ----------------------------------------------------------------------------
808 --
809 Procedure update_lookup_value
810 ( p_lookup_type in varchar2
811 ,p_lookup_code in varchar2
812 ,p_meaning in varchar2
813 ,p_description in varchar2
814 ) IS
815 --
816 -- Declare Cursors and local variables
817 --
818 l_proc varchar2(80);
819 l_dummy varchar2(1);
820 --
821 Begin
822 --
823 g_debug := hr_utility.debug_enabled;
824 if g_debug then
825 l_proc := g_package||'update_lookup_value';
826 hr_utility.set_location('Entering:'|| l_proc, 10);
827 end if;
828
829 fnd_lookup_values_pkg.UPDATE_ROW(X_LOOKUP_TYPE => p_lookup_type,
830 X_SECURITY_GROUP_ID => 0,
831 X_VIEW_APPLICATION_ID => 3,
832 X_LOOKUP_CODE => p_lookup_code,
833 X_TAG => null,
834 X_ATTRIBUTE_CATEGORY => null,
835 X_ATTRIBUTE1 => null,
836 X_ATTRIBUTE2 => null,
837 X_ATTRIBUTE3 => null,
838 X_ATTRIBUTE4 => null,
839 X_ENABLED_FLAG => 'Y',
840 X_START_DATE_ACTIVE => null,
841 X_END_DATE_ACTIVE => null,
842 X_TERRITORY_CODE => null,
843 X_ATTRIBUTE5 => null,
844 X_ATTRIBUTE6 => null,
845 X_ATTRIBUTE7 => null,
846 X_ATTRIBUTE8 => null,
847 X_ATTRIBUTE9 => null,
848 X_ATTRIBUTE10 => null,
849 X_ATTRIBUTE11 => null,
850 X_ATTRIBUTE12 => null,
851 X_ATTRIBUTE13 => null,
852 X_ATTRIBUTE14 => null,
853 X_ATTRIBUTE15 => null,
854 X_MEANING => p_meaning,
855 X_DESCRIPTION => p_description,
856 X_LAST_UPDATE_DATE => SYSDATE,
857 X_LAST_UPDATED_BY => fnd_global.user_id,
858 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
859
860 exception
861 when others then
862 if g_debug then
863 hr_utility.set_location('Leaving:'|| l_proc, 20);
864 end if;
865 end update_lookup_value;
866 --
867 -- ----------------------------------------------------------------------------
868 -- |--------------------------< create_shared_type >-------------------------|
869 -- ----------------------------------------------------------------------------
870 --
871 Procedure create_shared_type
872 (p_lookup_code in varchar2,
873 p_meaning in varchar2
874 ) IS
875 --
876 -- Declare Cursors and local variables
877 --
878 --
879 -- Check if the lookup exists already
880 -- for the current language
881 --
882 CURSOR csr_shared_type_entry_unique IS
883 select 'x' from PER_SHARED_TYPES
884 where lookup_type = 'HIERARCHY_TYPE'
885 and system_type_cd = p_lookup_code
886 and shared_type_code = p_lookup_code;
887 --
888 l_proc varchar2(72) ;
889 l_object_version_number per_shared_types.object_version_number%TYPE;
890 l_shared_type_id per_shared_types.shared_type_id%TYPE;
891 l_dummy varchar2(1);
892
893 --
894 Begin
895 --
896 g_debug := hr_utility.debug_enabled;
897 if g_debug then
898 l_proc := g_package||'create_shared_type';
899 hr_utility.set_location('Entering:'|| l_proc, 10);
900 end if;
901 --
902 -- Issue a savepoint
903 --
904 savepoint create_shared_type;
905 --
906 --
907 -- Enable multi messaging
908 --
909 if not hr_multi_message.is_message_list_enabled then
910 hr_multi_message.enable_message_list;
911 end if;
912 --
913 -- Check for duplicate shared type entry
914 --
915 open csr_shared_type_entry_unique;
916 fetch csr_shared_type_entry_unique into l_dummy;
917 if csr_shared_type_entry_unique%found then
918 close csr_shared_type_entry_unique;
919 if g_debug then
920 hr_utility.set_location('Error: Duplicate shared type', 20);
921 end if;
922 -- raise error as the supplied lookup_code already exists
923 fnd_message.set_name('PQH','PQH_GHR_SHARED_TYPE_DUP');
924 hr_multi_message.add;
925 else
926 close csr_shared_type_entry_unique;
927 end if;
928 --
929 -- Do not proceed if there are errors
930 --
931 hr_multi_message.end_validation_set;
932 --
933 if g_debug then
934 hr_utility.set_location('No Validation Errors So Far', 30);
935 end if;
936 -- Now we attempt to create a shared types entry using the
937 -- supplied code for the lookup_type 'HIERARCHY_TYPE'
938
939 per_shared_types_api.create_shared_type
940 ( p_shared_type_id => l_shared_type_id
941 ,p_shared_type_name => p_meaning
942 ,p_system_type_cd => p_lookup_code
943 ,p_shared_type_code => p_lookup_code
944 ,p_language_code => userenv('LANG')
945 ,p_information1 => 'N'
946 ,p_information2 => 'N'
947 ,p_information3 => 'N'
948 ,p_information_category => 'HIERARCHY_TYPE'
949 ,p_object_version_number => l_object_version_number
950 ,p_lookup_type => 'HIERARCHY_TYPE'
951 ,p_effective_date => sysdate
952 );
953
954 hr_multi_message.disable_message_list;
955 if g_debug then
956 hr_utility.set_location('Leaving:'||l_proc, 40);
957 end if;
958 Exception
959 when hr_multi_message.error_message_exist then
960 --
961 -- Error message exists
962 --
963 rollback to create_shared_type;
964 hr_multi_message.disable_message_list;
965 if g_debug then
966 hr_utility.set_location('Error messages exist:'||l_proc, 50);
967 end if;
968 --
969 when others then
970 --
971 -- An unexpected error has occured
972 --
973 rollback to create_shared_type;
974 if g_debug then
975 hr_utility.set_location('Unexpected Error'||l_proc, 60);
976 end if;
977 hr_multi_message.disable_message_list;
978 fnd_message.set_name('PQH','PQH_GHR_SHARED_TYPE_INS_FAIL');
979 fnd_msg_pub.add;
980 end create_shared_type;
981 --
982 -- ----------------------------------------------------------------------------
983 -- |--------------------------< update_shared_type >-------------------------|
984 -- ----------------------------------------------------------------------------
985 --
986 Procedure update_shared_type
987 (p_lookup_code in varchar2,
988 p_information2 in varchar2,
989 p_information3 in varchar2
990 ) IS
991 --
992 -- Declare Cursors and local variables
993 --
994 --
995 -- Check if the lookup exists already
996 --
997 CURSOR csr_shared_type_entry IS
998 select shared_type_id, object_version_number
999 from PER_SHARED_TYPES
1000 where lookup_type = 'HIERARCHY_TYPE'
1001 and system_type_cd = p_lookup_code
1002 and shared_type_code = p_lookup_code;
1003 --
1004 l_proc varchar2(72) ;
1005 l_object_version_number per_shared_types.object_version_number%TYPE;
1006 l_shared_type_id per_shared_types.shared_type_id%TYPE;
1007
1008 --
1009 Begin
1010 --
1011 g_debug := hr_utility.debug_enabled;
1012 if g_debug then
1013 l_proc := g_package||'update_shared_type';
1014 hr_utility.set_location('Entering:'|| l_proc, 10);
1015 end if;
1016 --
1017 -- Issue a savepoint
1018 --
1019 savepoint update_shared_type;
1020 --
1021 -- Initialize message pub
1022 fnd_msg_pub.initialize;
1023 --
1024 -- Check for duplicate shared type entry
1025 open csr_shared_type_entry;
1026 fetch csr_shared_type_entry into l_shared_type_id,l_object_version_number;
1027 if csr_shared_type_entry%notfound then
1028 close csr_shared_type_entry;
1029 if g_debug then
1030 hr_utility.set_location('Error: Lookup Code does not exist', 20);
1031 end if;
1032 -- raise error as the supplied lookup_code does not exist
1033 fnd_message.set_name('PQH','PQH_GHR_SHARED_TYPE_INV');
1034 fnd_message.raise_error;
1035 else
1036 close csr_shared_type_entry;
1037 end if;
1038 --
1039 -- Check that the information values are either 'Y' or 'N' only
1040 If ( (upper(p_information2) not in ('Y','N')) or (upper(p_information3) not in ('Y','N')) ) then
1041 if g_debug then
1042 hr_utility.set_location('Error: Info. Value is not y or n', 30);
1043 end if;
1044 --Raise error for invalid information value
1045 fnd_message.set_name('PQH','PQH_GHR_SHARED_TYPE_INV_INFO');
1046 fnd_message.raise_error;
1047 Else
1048 -- Now we attempt to update the shared types entry
1049
1050 per_shared_types_api.update_shared_type
1051 ( p_shared_type_id => l_shared_type_id
1052 ,p_language_code => userenv('LANG')
1053 ,p_information2 => p_information2
1054 ,p_information3 => p_information3
1055 ,p_object_version_number => l_object_version_number
1056 ,p_effective_date => sysdate
1057 );
1058 End if;
1059 --
1060 if g_debug then
1061 hr_utility.set_location('Leaving:'||l_proc, 40);
1062 end if;
1063 exception
1064 when others then
1065 --
1066 -- An error has occured
1067 --
1068 rollback to update_shared_type;
1069 if g_debug then
1070 hr_utility.set_location('An Error has occured:', 50);
1071 end if;
1072 fnd_msg_pub.add;
1073 end update_shared_type;
1074 --
1075 -- ----------------------------------------------------------------------------
1076 -- |--------------------------< create_node_type >---------------------------|
1077 -- ----------------------------------------------------------------------------
1078 --
1079 Procedure create_node_type (p_hierarchy_type in varchar2,
1080 p_child_value_set in varchar2,
1081 p_child_node_type in varchar2,
1082 p_parent_node_type in varchar2 )
1083 is
1084
1085 Cursor csr_chk_duplicate
1086 is
1087 Select '1'
1088 from per_gen_hier_node_types
1089 where
1090 hierarchy_type = p_hierarchy_type
1091 and ( (parent_node_type = p_parent_node_type)
1092 or
1093 (parent_node_type is null and p_parent_node_type is null))
1094 and child_node_type = p_child_node_type;
1095 --
1096
1097 Cursor Csr_chk_recursion
1098 is
1099 Select Parent_node_type
1100 From per_gen_hier_node_types
1101 Start with child_node_type = p_parent_node_type
1102 And hierarchy_type = p_hierarchy_type
1103 Connect by child_node_type = prior parent_node_type
1104 And hierarchy_type = p_hierarchy_type;
1105
1106 --
1107 Cursor csr_next_val
1108 is
1109 select per_gen_hier_node_types_s.nextval
1110 from sys.dual;
1111
1112 Cursor csr_chk_id_duplicate(p_next_id_val in number)
1113 is
1114 select '1'
1115 from per_gen_hier_node_types
1116 where hier_node_type_id = p_next_id_val;
1117
1118 --
1119 l_proc varchar2(72) ;
1120 l_exists varchar2(1) ;
1121 l_sql varchar2(4000);
1122 --
1123 Begin
1124 --
1125 g_debug := hr_utility.debug_enabled;
1126 if g_debug then
1127 l_proc := g_package||'create_node_type';
1128 hr_utility.set_location('Entering:'||l_proc, 10);
1129 end if;
1130 --
1131 -- Issue a savepoint
1132 --
1133 Savepoint create_node_type;
1134 --
1135 -- Initialize message pub
1136 fnd_msg_pub.initialize;
1137 --
1138 -- Insert Validate
1139 --
1140 Open csr_chk_duplicate;
1141 Fetch csr_chk_duplicate into l_exists;
1142 If csr_chk_duplicate%found then
1143 if g_debug then
1144 hr_utility.set_location('Error:Duplicate Node Type', 20);
1145 end if;
1146 -- This is a duplicate entry..so raise error.
1147 Close csr_chk_duplicate;
1148 fnd_message.set_name('PQH','PQH_GHR_DUPLICATE_NODE_TYPE');
1149 fnd_message.raise_error;
1150 End if;
1151 Close csr_chk_duplicate;
1152
1153 For c_rec in csr_chk_recursion
1154 loop
1155 if c_rec.parent_node_type = p_child_node_type
1156 then
1157 if g_debug then
1158 hr_utility.set_location('Error:Recursive Structure', 30);
1159 end if;
1160 -- This will cause recursion
1161 fnd_message.set_name('PQH','PQH_GHR_RECURSIVE_NODE_TYPE');
1162 fnd_message.raise_error;
1163 end if;
1164 End loop;
1165
1166 if(p_parent_node_type = p_child_node_type)
1167 then
1168 if g_debug then
1169 hr_utility.set_location('Error:Recursive Structure', 40);
1170 end if;
1171 -- Again a recursive case
1172 fnd_message.set_name('PQH','PQH_GHR_RECURSIVE_NODE_TYPE');
1173 fnd_message.raise_error;
1174 end if;
1175 --
1176 --
1177 -- All validations are passed. Insert values into the table
1178 --
1179 l_sql := 'Insert into per_gen_hier_node_types
1180 (hierarchy_type,
1181 parent_node_type,
1182 child_node_type,
1183 child_value_set,
1184 hier_node_type_id,
1185 object_version_number)
1186 Values (
1187 :p_hierarchy_type ,
1188 :p_parent_node_type ,
1189 :p_child_node_type ,
1190 :p_child_value_set ,
1191 per_gen_hier_node_types_s.nextval ,
1192 1 )';
1193 --
1194 if g_debug then
1195 hr_utility.set_location('Sql Formed', 50);
1196 end if;
1197 Execute Immediate l_sql Using p_hierarchy_type, p_parent_node_type,
1198 p_child_node_type, p_child_value_set;
1199 if g_debug then
1200 hr_utility.set_location('Leaving:'||l_proc, 60);
1201 end if;
1202 Exception
1203 when others
1204 then
1205 if g_debug then
1206 hr_utility.set_location('An Error has occured', 70);
1207 end if;
1208 fnd_msg_pub.add;
1209 rollback to create_node_type;
1210 end create_node_type;
1211 --
1212 --
1213 -- ----------------------------------------------------------------------------
1214 -- |--------------------------< update_node_type >---------------------------|
1215 -- ----------------------------------------------------------------------------
1216 --
1217 Procedure update_node_type (p_hierarchy_type in varchar2,
1218 p_child_value_set in varchar2,
1219 p_child_node_type in varchar2,
1220 p_parent_node_type in varchar2 ,
1221 p_object_version_number in out NOCOPY number)
1222 is
1223 --
1224
1225 Cursor csr_node_type_entry
1226 is
1227 Select object_version_number
1228 From
1229 per_gen_hier_node_types
1230 Where
1231 hierarchy_type = p_hierarchy_type
1232 And ( (parent_node_type = p_parent_node_type)
1233 Or
1234 (parent_node_type is null and p_parent_node_type is null))
1235 And child_node_type = p_child_node_type
1236 For update nowait;
1237
1238 --
1239 l_object_version_number Number(15);
1240 l_sql varchar2(4000);
1241 l_proc varchar2(72);
1242 --
1243 Begin
1244 --
1245 g_debug := hr_utility.debug_enabled;
1246 if g_debug then
1247 l_proc := g_package||'update_node_type';
1248 hr_utility.set_location('Entering:'||l_proc, 10);
1249 end if;
1250 --
1251 --Issue a Savepoint
1252 --
1253 Savepoint update_node_type;
1254 --
1255 --Initialize message pub
1256 --
1257 fnd_msg_pub.initialize;
1258 --
1259 --Check that the node type entry exists
1260 --
1261 Open csr_node_type_entry;
1262 Fetch csr_node_type_entry into l_object_version_number;
1263 If csr_node_type_entry%notfound
1264 then
1265 --
1266 if g_debug then
1267 hr_utility.set_location('Error: No Matching Node Type Entry', 20);
1268 end if;
1269 --
1270 Close csr_node_type_entry;
1271 fnd_message.set_name('PQH','PQH_GHR_NO_NODE_TYPE_ENTRY');
1272 fnd_message.raise_error;
1273 End if;
1274 Close csr_node_type_entry;
1275 --
1276 If (p_object_version_number
1277 <> l_object_version_number) Then
1278 --
1279 if g_debug then
1280 hr_utility.set_location('Error:Invalid Object Version', 30);
1281 end if;
1282 --
1283 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
1284 fnd_message.raise_error;
1285 End If;
1286 --
1287 -- Increment object_version_number
1288 --
1289 p_object_version_number := p_object_version_number + 1;
1290 --
1291 -- Update the value set info
1292 --
1293 l_sql := 'Update per_gen_hier_node_types
1294 Set child_value_set = :p_child_value_set,
1295 object_version_number = :p_object_version_number
1296 Where hierarchy_type = :p_hierarchy_type
1297 and ( (parent_node_type = :p_parent_node_type)
1298 Or
1299 (parent_node_type is null and :p_parent_node_type is null))
1300 and child_node_type = :p_child_node_type';
1301 --
1302 if g_debug then
1303 hr_utility.set_location('Sql Formed', 40);
1304 end if;
1305 --
1306 Execute immediate l_sql using p_child_value_set, p_object_version_number,
1307 p_hierarchy_type, p_parent_node_type,
1308 p_parent_node_type,p_child_node_type;
1309 --
1310 if g_debug then
1311 hr_utility.set_location('Leaving:'||l_proc, 50);
1312 end if;
1313 --
1314 Exception
1315 When HR_Api.Object_Locked then
1316 --
1317 -- The object is locked therefore we need to supply a meaningful
1318 -- error message.
1319 --
1320 if g_debug then
1321 hr_utility.set_location('Error: Object Locked', 60);
1322 end if;
1323 --
1324 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
1325 fnd_message.set_token('TABLE_NAME', 'per_gen_hier_node_types');
1326 fnd_msg_pub.add;
1327 When others
1328 then
1329 if g_debug then
1330 hr_utility.set_location('An Error has occured', 70);
1331 end if;
1332 rollback to update_node_type;
1333 fnd_msg_pub.add;
1334 end update_node_type;
1335 --
1336 --
1337 -- ----------------------------------------------------------------------------
1338 -- |--------------------------< delete_node_type >---------------------------|
1339 -- ----------------------------------------------------------------------------
1340 --
1341 Procedure delete_node_type (p_hierarchy_type in varchar2,
1342 p_child_node_type in varchar2,
1343 p_parent_node_type in varchar2)
1344 is
1345 --
1346 Cursor csr_node_type_entry
1347 is
1348 Select object_version_number
1349 From
1350 per_gen_hier_node_types
1351 Where
1352 hierarchy_type = p_hierarchy_type
1353 And ( (parent_node_type = p_parent_node_type)
1354 Or
1355 (parent_node_type is null and p_parent_node_type is null))
1356 And child_node_type = p_child_node_type
1357 For update nowait;
1358 --
1359 Cursor csr_node_type_hierarchy
1360 is
1361 Select child_node_type,parent_node_type
1362 from per_gen_hier_node_types
1363 start with hierarchy_type = p_hierarchy_type
1364 and ( (parent_node_type = p_parent_node_type)
1365 Or
1366 (parent_node_type is null And p_parent_node_type is null))
1367 and child_node_type = p_child_node_type
1368 Connect by
1369 prior child_node_type = parent_node_type
1370 and ((prior child_node_type <> prior parent_node_type) or(prior child_node_type is not null and prior parent_node_type is null))
1371 and hierarchy_type = p_hierarchy_type
1372 order by level desc;
1373 --
1374
1375 l_object_version_number Number(15);
1376 l_sql varchar2(4000);
1377 l_proc varchar2(72);
1378 --
1379 Begin
1380 --
1381 g_debug := hr_utility.debug_enabled;
1382 if g_debug then
1383 l_proc := g_package||'delete_node_type';
1384 hr_utility.set_location('Entering:'||l_proc, 10);
1385 end if;
1386 --
1387 --Issue a Savepoint
1388 --
1389 Savepoint delete_node_type;
1390
1391 --Initialize message pub
1392 --
1393 fnd_msg_pub.initialize;
1394 --
1395 --
1396 --Check that the node type entry exists
1397 --
1398 Open csr_node_type_entry;
1399 Fetch csr_node_type_entry into l_object_version_number;
1400 If csr_node_type_entry%notfound
1401 then
1402 --
1403 if g_debug then
1404 hr_utility.set_location('Error: No Matching Node Type Entry', 20);
1405 end if;
1406 --
1407 Close csr_node_type_entry;
1408 fnd_message.set_name('PQH','PQH_GHR_NO_NODE_TYPE_ENTRY');
1409 fnd_message.raise_error;
1410 End if;
1411 Close csr_node_type_entry;
1412 --
1413 -- Loop through to delete node and children
1414 --
1415 --
1416 --Form the delete sql
1417 --
1418 l_sql := 'Delete from per_gen_hier_node_types
1419 Where
1420 hierarchy_type = :p_hierarchy_type
1421 And ( (parent_node_type = :p_parent_node_type)
1422 Or
1423 (parent_node_type is null and :p_parent_node_type is null))
1424 And child_node_type = :p_child_node_type';
1425 if g_debug then
1426 hr_utility.set_location('Sql Formed', 30);
1427 end if;
1428
1429 For c_rec in csr_node_type_hierarchy
1430 loop
1431 -- Delete the row
1432 Execute immediate l_sql using p_hierarchy_type,
1433 c_rec.parent_node_type,c_rec.parent_node_type,
1434 c_rec.child_node_type;
1435 if g_debug then
1436 hr_utility.set_location('Deleted a Row, Trying another...', 40);
1437 end if;
1438 End loop;
1439
1440 Exception
1441 When HR_Api.Object_Locked then
1442 --
1443 -- The object is locked therefore we need to supply a meaningful
1444 -- error message.
1445 --
1446 if g_debug then
1447 hr_utility.set_location('Error: Object Locked', 50);
1448 end if;
1449 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
1450 fnd_message.set_token('TABLE_NAME', 'per_gen_hier_node_types');
1451 fnd_msg_pub.add;
1452 When others
1453 then
1454 if g_debug then
1455 hr_utility.set_location('An Error has Occured', 60);
1456 end if;
1457 rollback to delete_node_type;
1458 fnd_msg_pub.add;
1459 End delete_node_type;
1460 --
1461 --
1462 --
1463 -- ----------------------------------------------------------------------------
1464 -- |--------------------------< delete_type_structure >-----------------------|
1465 -- ----------------------------------------------------------------------------
1466 --
1467 Procedure delete_type_structure (p_hierarchy_type in varchar2)
1468 Is
1469 --
1470 Cursor csr_top_node_types
1471 is
1472 Select child_node_type
1473 From
1474 per_gen_hier_node_types
1475 where
1476 hierarchy_type = p_hierarchy_type
1477 and parent_node_type is null;
1478 --
1479 l_proc varchar2(72);
1480 --
1481 Begin
1482 --
1483 g_debug := hr_utility.debug_enabled;
1484 if g_debug then
1485 l_proc := g_package||'delete_type_structure';
1486 hr_utility.set_location('Entering:'||l_proc, 10);
1487 end if;
1488 --
1489 -- Initialize message pub
1490 --
1491 fnd_msg_pub.initialize;
1492 --
1493 -- Issue a savepoint
1494 --
1495 Savepoint delete_type_structure;
1496 --
1497 --
1498 -- Loop through the top node types and delete their children.
1499 --
1500 For c_rec in csr_top_node_types
1501 Loop
1502 --
1503 -- Call the procedure to delete the node type structure.
1504 --
1505 delete_node_type(p_hierarchy_type => p_hierarchy_type,
1506 p_parent_node_type => null,
1507 p_child_node_type => c_rec.child_node_type);
1508 if g_debug then
1509 hr_utility.set_location('Deleted a row.Trying another..', 20);
1510 end if;
1511 End loop;
1512 --
1513 if g_debug then
1514 hr_utility.set_location('Leaving:'||l_proc, 30);
1515 end if;
1516 --
1517 Exception
1518 When others
1519 then
1520 if g_debug then
1521 hr_utility.set_location('An Error has occured', 40);
1522 end if;
1523 Rollback to delete_type_structure;
1524 fnd_msg_pub.add;
1525 End delete_type_structure;
1526 --
1527 --
1528 -- ----------------------------------------------------------------------------
1529 -- |--------------------------< copy_hierarchy_version >----------------------|
1530 -- ----------------------------------------------------------------------------
1531 --
1532 Procedure copy_hierarchy_version ( p_type in varchar2
1533 ,p_name in varchar2
1534 ,p_hierarchy_id in Number
1535 ,p_hierarchy_version_id in Number
1536 ,p_version_number in Number
1537 ,p_date_from in Date
1538 ,p_date_to in Date
1539 ,p_business_group_id in Number
1540 ,p_effective_date in Date
1541 ,p_new_hierarchy_id out NoCopy Number
1542 ,p_new_hierarchy_version_id out NoCopy Number)
1543 Is
1544 /* l_new_hierarchy_id Number(15);
1545 l_new_hierarchy_version_id Number(15); */
1546 l_proc varchar2(72);
1547 /* pqh_de_opr_grp.copy_hierarchy_version( p_type => p_type
1548 ,p_name => p_name
1549 ,p_hierarchy_id => p_hierarchy_id
1550 ,p_hierarchy_version_id => p_hierarchy_version_id
1551 ,p_version_number => p_version_number
1552 ,p_date_from => p_date_from
1553 ,p_date_to => p_date_to
1554 ,p_business_group_id => p_business_group_id
1555 ,p_effective_date => p_effective_date
1556 ,p_new_hierarchy_id => l_new_hierarchy_id
1557 ,p_new_hierarchy_version_id => l_new_hierarchy_version_id); */
1558
1559 Cursor Hierarchy is
1560 Select Type, ATTRIBUTE_CATEGORY, ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 ,
1561 ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 ,
1562 ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 ,
1563 ATTRIBUTE15 , ATTRIBUTE16 , ATTRIBUTE17 , ATTRIBUTE18 ,
1564 ATTRIBUTE19 , ATTRIBUTE20 , ATTRIBUTE21 , ATTRIBUTE22 , ATTRIBUTE23 , ATTRIBUTE24 ,
1565 ATTRIBUTE25 , ATTRIBUTE26 , ATTRIBUTE27 , ATTRIBUTE28 ,
1566 ATTRIBUTE29 , ATTRIBUTE30 , INFORMATION1 , INFORMATION2 , INFORMATION3 , INFORMATION4 ,
1567 INFORMATION5 , INFORMATION6 , INFORMATION7 , INFORMATION8 ,
1568 INFORMATION9 , INFORMATION10, INFORMATION11, INFORMATION12, INFORMATION13, INFORMATION14,
1569 INFORMATION15, INFORMATION16, INFORMATION17, INFORMATION18,
1570 INFORMATION19, INFORMATION20, INFORMATION21, INFORMATION22, INFORMATION23, INFORMATION24,
1571 INFORMATION25, INFORMATION26, INFORMATION27, INFORMATION28,
1572 INFORMATION29, INFORMATION30, INFORMATION_CATEGORY
1573 From Per_Gen_Hierarchy
1574 Where Hierarchy_id = P_Hierarchy_Id;
1575
1576 Cursor Hierarchy_version is
1577 Select Hierarchy_Version_id, VERSION_NUMBER, HIERARCHY_ID , DATE_FROM , DATE_TO , STATUS
1578 , VALIDATE_FLAG, ATTRIBUTE_CATEGORY,
1579 ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 ,
1580 ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 , ATTRIBUTE16 , ATTRIBUTE17 , ATTRIBUTE18 ,
1581 ATTRIBUTE19 , ATTRIBUTE20 , ATTRIBUTE21 , ATTRIBUTE22 , ATTRIBUTE23 , ATTRIBUTE24 , ATTRIBUTE25 , ATTRIBUTE26 , ATTRIBUTE27 , ATTRIBUTE28 ,
1582 ATTRIBUTE29 , ATTRIBUTE30 , INFORMATION1 , INFORMATION2 , INFORMATION3 , INFORMATION4 , INFORMATION5 , INFORMATION6 , INFORMATION7 , INFORMATION8 ,
1583 INFORMATION9 , INFORMATION10, INFORMATION11, INFORMATION12, INFORMATION13, INFORMATION14, INFORMATION15, INFORMATION16, INFORMATION17, INFORMATION18,
1584 INFORMATION19 , INFORMATION20, INFORMATION21, INFORMATION22, INFORMATION23, INFORMATION24, INFORMATION25, INFORMATION26, INFORMATION27, INFORMATION28,
1585 INFORMATION29 , INFORMATION30, INFORMATION_CATEGORY
1586 From Per_Gen_Hierarchy_Versions
1587 Where ((P_Hierarchy_Version_Id is not NULL and Hierarchy_Version_Id = p_Hierarchy_Version_Id)
1588 or (Hierarchy_Id = P_Hierarchy_Id and P_Effective_Date between Date_From and Nvl(Date_To,p_Effective_Date)));
1589
1590 Cursor Nodes(C_Hierarchy_Version_id In NUMBER) is
1591 Select Hierarchy_Node_id
1592 From Per_Gen_Hierarchy_Nodes
1593 Where Hierarchy_Version_id = C_Hierarchy_version_id
1594 and Parent_Hierarchy_Node_Id is NULL;
1595
1596 l_hierarchy_id Per_Gen_Hierarchy.Hierarchy_id%TYPE;
1597 l_Hierarchy_version_id Per_Gen_Hierarchy_Versions.Hierarchy_Version_Id%TYPE;
1598 l_Object_version_Number Per_Gen_hierarchy.Object_Version_Number%TYPE;
1599
1600 Begin
1601 --
1602 g_debug := hr_utility.debug_enabled;
1603 if g_debug then
1604 l_proc := g_package||'copy_hierarchy_version';
1605 hr_utility.set_location('Entering:'||l_proc, 10);
1606 end if;
1607 --
1608 -- Initialize message pub
1609 --
1610 fnd_msg_pub.initialize;
1611 --
1612 if g_debug then
1613 hr_utility.set_location('Calling copy proc. with p_type='||p_type, 20);
1614 end if;
1615 --
1616 PER_HIERARCHY_NODES_API.G_MODE := 'COPY' ;
1617 --
1618 If P_Type = 'H' Then
1619
1620 For Hierarchy_Rec in Hierarchy
1621 Loop
1622 Per_hierarchy_api.CREATE_HIERARCHY
1623 (P_HIERARCHY_ID => l_Hierarchy_id ,
1624 P_BUSINESS_GROUP_ID => p_Business_group_Id,
1625 P_NAME => P_Name ,
1626 P_TYPE => Hierarchy_rec.Type ,
1627 P_OBJECT_VERSION_NUMBER => l_Object_version_Number,
1628 P_ATTRIBUTE_CATEGORY => Hierarchy_rec.Attribute_Category,
1629 P_ATTRIBUTE1 => Hierarchy_rec.Attribute1,
1630 P_ATTRIBUTE2 => Hierarchy_rec.Attribute2,
1631 P_ATTRIBUTE3 => Hierarchy_rec.Attribute3,
1632 P_ATTRIBUTE4 => Hierarchy_rec.Attribute4,
1633 P_ATTRIBUTE5 => Hierarchy_rec.Attribute5,
1634 P_ATTRIBUTE6 => Hierarchy_rec.Attribute6,
1635 P_ATTRIBUTE7 => Hierarchy_rec.Attribute7,
1636 P_ATTRIBUTE8 => Hierarchy_rec.Attribute8,
1637 P_ATTRIBUTE9 => Hierarchy_rec.Attribute9,
1638 P_ATTRIBUTE10 => Hierarchy_rec.Attribute10,
1639 P_ATTRIBUTE11 => Hierarchy_rec.Attribute11,
1640 P_ATTRIBUTE12 => Hierarchy_rec.Attribute12,
1641 P_ATTRIBUTE13 => Hierarchy_rec.Attribute13,
1642 P_ATTRIBUTE14 => Hierarchy_rec.Attribute14,
1643 P_ATTRIBUTE15 => Hierarchy_rec.Attribute15,
1644 P_ATTRIBUTE16 => Hierarchy_rec.Attribute16,
1645 P_ATTRIBUTE17 => Hierarchy_rec.Attribute17,
1646 P_ATTRIBUTE18 => Hierarchy_rec.Attribute18,
1647 P_ATTRIBUTE19 => Hierarchy_rec.Attribute19,
1648 P_ATTRIBUTE20 => Hierarchy_rec.Attribute20,
1649 P_ATTRIBUTE21 => Hierarchy_rec.Attribute21,
1650 P_ATTRIBUTE22 => Hierarchy_rec.Attribute22,
1651 P_ATTRIBUTE23 => Hierarchy_rec.Attribute23,
1652 P_ATTRIBUTE24 => Hierarchy_rec.Attribute24,
1653 P_ATTRIBUTE25 => Hierarchy_rec.Attribute25,
1654 P_ATTRIBUTE26 => Hierarchy_rec.Attribute26,
1655 P_ATTRIBUTE27 => Hierarchy_rec.Attribute27,
1656 P_ATTRIBUTE28 => Hierarchy_rec.Attribute28,
1657 P_ATTRIBUTE29 => Hierarchy_rec.Attribute29,
1658 P_ATTRIBUTE30 => Hierarchy_rec.Attribute30,
1659 P_INFORMATION_CATEGORY => Hierarchy_rec.Information_Category,
1660 P_INFORMATION1 => Hierarchy_rec.Information1,
1661 P_INFORMATION2 => Hierarchy_rec.Information2,
1662 P_INFORMATION3 => Hierarchy_rec.Information3,
1663 P_INFORMATION4 => Hierarchy_rec.Information4,
1664 P_INFORMATION5 => Hierarchy_rec.Information5,
1665 P_INFORMATION6 => Hierarchy_rec.Information6,
1666 P_INFORMATION7 => Hierarchy_rec.Information7,
1667 P_INFORMATION8 => Hierarchy_rec.Information8,
1668 P_INFORMATION9 => Hierarchy_rec.Information9,
1669 P_INFORMATION10 => Hierarchy_rec.Information10,
1670 P_INFORMATION11 => Hierarchy_rec.Information11,
1671 P_INFORMATION12 => Hierarchy_rec.Information12,
1672 P_INFORMATION13 => Hierarchy_rec.Information13,
1673 P_INFORMATION14 => Hierarchy_rec.Information14,
1674 P_INFORMATION15 => Hierarchy_rec.Information15,
1675 P_INFORMATION16 => Hierarchy_rec.Information16,
1676 P_INFORMATION17 => Hierarchy_rec.Information17,
1677 P_INFORMATION18 => Hierarchy_rec.Information18,
1678 P_INFORMATION19 => Hierarchy_rec.Information19,
1679 P_INFORMATION20 => Hierarchy_rec.Information20,
1680 P_INFORMATION21 => Hierarchy_rec.Information21,
1681 P_INFORMATION22 => Hierarchy_rec.Information22,
1682 P_INFORMATION23 => Hierarchy_rec.Information23,
1683 P_INFORMATION24 => Hierarchy_rec.Information24,
1684 P_INFORMATION25 => Hierarchy_rec.Information25,
1685 P_INFORMATION26 => Hierarchy_rec.Information26,
1686 P_INFORMATION27 => Hierarchy_rec.Information27,
1687 P_INFORMATION28 => Hierarchy_rec.Information28,
1688 P_INFORMATION29 => Hierarchy_rec.Information29,
1689 P_INFORMATION30 => Hierarchy_rec.Information30,
1690 P_EFFECTIVE_DATE => p_Effective_Date);
1691 l_Object_version_Number := NULL;
1692 End Loop;
1693 P_New_Hierarchy_Id := l_Hierarchy_id;
1694 End If;
1695
1696 If P_Type in ('H','V') then
1697
1698
1699 For Hierarchy_Ver_rec in Hierarchy_version Loop
1700 Per_hierarchy_versions_api.create_hierarchy_versions
1701 (P_HIERARCHY_VERSION_ID => l_Hierarchy_Version_id,
1702 P_BUSINESS_GROUP_ID => p_Business_group_Id,
1703 P_VERSION_NUMBER => Nvl(P_Version_Number,1),
1704 P_HIERARCHY_ID => Nvl(l_Hierarchy_id,Hierarchy_Ver_Rec.Hierarchy_Id),
1705 P_DATE_FROM => Nvl(P_Date_From,P_EFFECTIVE_DATE),
1706 P_DATE_TO => P_Date_To,
1707 P_OBJECT_VERSION_NUMBER => l_Object_version_Number ,
1708 P_STATUS => 'A',
1709 P_VALIDATE_FLAG => 'Y',
1710 P_ATTRIBUTE_CATEGORY => Hierarchy_Ver_rec.Attribute_Category,
1711 P_ATTRIBUTE1 => Hierarchy_Ver_rec.Attribute1,
1712 P_ATTRIBUTE2 => Hierarchy_Ver_rec.Attribute2,
1713 P_ATTRIBUTE3 => Hierarchy_Ver_rec.Attribute3,
1714 P_ATTRIBUTE4 => Hierarchy_Ver_rec.Attribute4,
1715 P_ATTRIBUTE5 => Hierarchy_Ver_rec.Attribute5,
1716 P_ATTRIBUTE6 => Hierarchy_Ver_rec.Attribute6,
1717 P_ATTRIBUTE7 => Hierarchy_Ver_rec.Attribute7,
1718 P_ATTRIBUTE8 => Hierarchy_Ver_rec.Attribute8,
1719 P_ATTRIBUTE9 => Hierarchy_Ver_rec.Attribute9,
1720 P_ATTRIBUTE10 => Hierarchy_Ver_rec.Attribute10,
1721 P_ATTRIBUTE11 => Hierarchy_Ver_rec.Attribute11,
1722 P_ATTRIBUTE12 => Hierarchy_Ver_rec.Attribute12,
1723 P_ATTRIBUTE13 => Hierarchy_Ver_rec.Attribute13,
1724 P_ATTRIBUTE14 => Hierarchy_Ver_rec.Attribute14,
1725 P_ATTRIBUTE15 => Hierarchy_Ver_rec.Attribute15,
1726 P_ATTRIBUTE16 => Hierarchy_Ver_rec.Attribute16,
1727 P_ATTRIBUTE17 => Hierarchy_Ver_rec.Attribute17,
1728 P_ATTRIBUTE18 => Hierarchy_Ver_rec.Attribute18,
1729 P_ATTRIBUTE19 => Hierarchy_Ver_rec.Attribute19,
1730 P_ATTRIBUTE20 => Hierarchy_Ver_rec.Attribute20,
1731 P_ATTRIBUTE21 => Hierarchy_Ver_rec.Attribute21,
1732 P_ATTRIBUTE22 => Hierarchy_Ver_rec.Attribute22,
1733 P_ATTRIBUTE23 => Hierarchy_Ver_rec.Attribute23,
1734 P_ATTRIBUTE24 => Hierarchy_Ver_rec.Attribute24,
1735 P_ATTRIBUTE25 => Hierarchy_Ver_rec.Attribute25,
1736 P_ATTRIBUTE26 => Hierarchy_Ver_rec.Attribute26,
1737 P_ATTRIBUTE27 => Hierarchy_Ver_rec.Attribute27,
1738 P_ATTRIBUTE28 => Hierarchy_Ver_rec.Attribute28,
1739 P_ATTRIBUTE29 => Hierarchy_Ver_rec.Attribute29,
1740 P_ATTRIBUTE30 => Hierarchy_Ver_rec.Attribute30,
1741 P_INFORMATION_CATEGORY => Hierarchy_Ver_rec.Information_Category,
1742 P_INFORMATION1 => Hierarchy_Ver_rec.Information1,
1743 P_INFORMATION2 => Hierarchy_Ver_rec.Information2,
1744 P_INFORMATION3 => Hierarchy_Ver_rec.Information3,
1745 P_INFORMATION4 => Hierarchy_Ver_rec.Information4,
1746 P_INFORMATION5 => Hierarchy_Ver_Rec.Information5,
1747 P_INFORMATION6 => Hierarchy_Ver_Rec.Information6,
1748 P_INFORMATION7 => Hierarchy_Ver_Rec.Information7,
1749 P_INFORMATION8 => Hierarchy_Ver_Rec.Information8,
1750 P_INFORMATION9 => Hierarchy_Ver_Rec.Information9,
1751 P_INFORMATION10 => Hierarchy_Ver_Rec.Information10,
1752 P_INFORMATION11 => Hierarchy_Ver_Rec.Information11,
1753 P_INFORMATION12 => Hierarchy_Ver_Rec.Information12,
1754 P_INFORMATION13 => Hierarchy_Ver_Rec.Information13,
1755 P_INFORMATION14 => Hierarchy_Ver_Rec.Information14,
1756 P_INFORMATION15 => Hierarchy_Ver_Rec.Information15,
1757 P_INFORMATION16 => Hierarchy_Ver_Rec.Information16,
1758 P_INFORMATION17 => Hierarchy_Ver_Rec.Information17,
1759 P_INFORMATION18 => Hierarchy_Ver_Rec.Information18,
1760 P_INFORMATION19 => Hierarchy_Ver_Rec.Information19,
1761 P_INFORMATION20 => Hierarchy_Ver_Rec.Information20,
1762 P_INFORMATION21 => Hierarchy_Ver_Rec.Information21,
1763 P_INFORMATION22 => Hierarchy_Ver_Rec.Information22,
1764 P_INFORMATION23 => Hierarchy_Ver_Rec.Information23,
1765 P_INFORMATION24 => Hierarchy_Ver_Rec.Information24,
1766 P_INFORMATION25 => Hierarchy_Ver_Rec.Information25,
1767 P_INFORMATION26 => Hierarchy_Ver_Rec.Information26,
1768 P_INFORMATION27 => Hierarchy_Ver_Rec.Information27,
1769 P_INFORMATION28 => Hierarchy_Ver_Rec.Information28,
1770 P_INFORMATION29 => Hierarchy_Ver_Rec.Information29,
1771 P_INFORMATION30 => Hierarchy_Ver_Rec.Information30,
1772 P_EFFECTIVE_DATE => p_Effective_Date);
1773 P_New_Hierarchy_Version_Id := l_Hierarchy_Version_id;
1774 For Node_Rec in Nodes(Hierarchy_Ver_Rec.Hierarchy_Version_id)
1775 Loop
1776 copy_Hierarchy
1777 (P_Hierarchy_version_id => l_Hierarchy_Version_Id,
1778 P_Parent_Hierarchy_id => NULL,
1779 P_Hierarchy_Id => Node_rec.Hierarchy_Node_id,
1780 p_Business_group_Id => P_Business_group_id,
1781 p_Effective_Date => P_Effective_Date);
1782 End Loop;
1783
1784 End Loop;
1785
1786 End If;
1787 --
1788 if g_debug then
1789 hr_utility.set_location('Leaving:'||l_proc, 30);
1790 end if;
1791 --
1792 PER_HIERARCHY_NODES_API.G_MODE := null ;
1793 --
1794 Exception
1795 When others then
1796 p_new_hierarchy_id := null;
1797 p_new_hierarchy_version_id := null;
1798 --
1799 PER_HIERARCHY_NODES_API.G_MODE := null ;
1800 --
1801 if g_debug then
1802 hr_utility.set_location('An Error has occured', 40);
1803 end if;
1804 fnd_msg_pub.add();
1805 End copy_hierarchy_version;
1806 --
1807 --
1808 --
1809 -- ----------------------------------------------------------------------------
1810 -- |------------------------------< is_valid_sql >---------------------------|
1811 -- ----------------------------------------------------------------------------
1812 --
1813 Function is_valid_sql(p_sql in varchar2)
1814 Return Varchar2
1815 Is
1816 l_sql Varchar2(4000);
1817 l_valid Varchar2(1) := 'Y';
1818 l_proc Varchar2(72);
1819 Begin
1820 if g_debug then
1821 l_proc := g_package||'l_valid_sql';
1822 hr_utility.set_location('Entering:'||l_proc, 10);
1823 end if;
1824
1825 l_sql := 'select ''Y'' from ('||p_sql||') where rownum < 1';
1826 --
1827 Begin
1828 Execute immediate l_sql into l_valid;
1829 Exception
1830 When no_data_found then
1831 l_valid := 'Y';
1832 When others then
1833 l_valid := 'N';
1834 End;
1835 --
1836 if g_debug then
1837 hr_utility.set_location('Leaving:'||l_proc, 10);
1838 end if;
1839 --
1840 Return l_valid;
1841 --
1842 End is_valid_sql;
1843 --
1844 --
1845 --
1846 --
1847 -- ----------------------------------------------------------------------------
1848 -- |-------------------------<validate_vets_hierarchy>------------------------|
1849 -- ----------------------------------------------------------------------------
1850 --
1851 Function validate_vets_hierarchy(p_hierarchy_version_id in Number)
1852 Return Varchar2
1853 is
1854 --
1855 Cursor csr_node_level is
1856 Select level, node_type
1857 From Per_Gen_Hierarchy_Nodes
1858 Where hierarchy_version_id = p_hierarchy_version_id
1859 Start With parent_hierarchy_node_id is null
1860 Connect By prior hierarchy_node_id = parent_hierarchy_node_id;
1861 --
1862 l_proc Varchar2(72);
1863 l_max_level Number(15) := 0;
1864 --
1865 Begin
1866 --
1867 if g_debug then
1868 l_proc := g_package||'validate_vets_hierarchy';
1869 hr_utility.set_location('Entering:'||l_proc, 10);
1870 end if;
1871 --
1872 for l_rec in csr_node_level loop
1873 --
1874 if l_rec.level > 3 then
1875 return 'N';
1876 end if;
1877 --
1878 if ( (l_rec.level = 1 and l_rec.node_type <> 'PAR') or
1879 (l_rec.level = 2 and l_rec.node_type <> 'EST') or
1880 (l_rec.level = 3 and l_rec.node_type <> 'LOC')
1881 )
1882 then
1883 return 'N';
1884 end if;
1885 --
1886 if (l_max_level < l_rec.level) then
1887 l_max_level := l_rec.level;
1888 end if;
1889 --
1890 end loop;
1891 --
1892 if l_max_level < 2 then
1893 return 'N';
1894 else
1895 return 'Y';
1896 end if;
1897 --
1898 End validate_vets_hierarchy;
1899 --
1900 --
1901 -- ----------------------------------------------------------------------------
1902 -- |----------------------------<get_display_value>---------------------------|
1903 -- ----------------------------------------------------------------------------
1904 --
1905 FUNCTION get_display_value(p_entity_id IN VARCHAR2,
1906 p_node_type_id IN NUMBER)
1907 RETURN VARCHAR2 IS
1908 --
1909 -- get Value Set Id
1910 CURSOR csr_value_set IS
1911 SELECT flex_value_set_id
1912 FROM fnd_flex_value_sets
1913 WHERE validation_type = 'F'
1914 AND flex_value_set_name = (SELECT pgt.child_value_set
1915 FROM per_gen_hier_node_types pgt
1916 WHERE pgt.hier_node_type_id = p_node_type_id);
1917
1918 l_value_set_id NUMBER(15) := NULL;
1919 l_id_column VARCHAR2(200);
1920 l_sql_statement VARCHAR2(2000);
1921 l_UPPER_SQL_statement VARCHAR2(2000);
1922 l_value_id VARCHAR2(255);
1923 l_name VARCHAR2(2000):= 'NULL';
1924 l_proc VARCHAR2(30) := 'get_display_value';
1925 --
1926 --
1927 FUNCTION get_sql_from_vset_id(p_vset_id IN NUMBER) RETURN VARCHAR2 IS
1928 --
1929 l_v_r fnd_vset.valueset_r;
1930 l_v_dr fnd_vset.valueset_dr;
1931 l_str varchar2(4000);
1932 l_whr varchar2(4000);
1933 --
1934 BEGIN
1935 --
1936 fnd_vset.get_valueset(valueset_id => p_vset_id ,
1937 valueset => l_v_r,
1938 format => l_v_dr);
1939 --
1940 l_whr := l_v_r.table_info.where_clause ;
1941 l_str := 'select '||substr(l_v_r.table_info.id_column_name,1,instr(l_v_r.table_info.id_column_name||' ',' '))||','
1942 ||substr(l_v_r.table_info.value_column_name,1,instr(l_v_r.table_info.value_column_name||' ',' '))
1943 ||' from '
1944 ||l_v_r.table_info.table_name||' '||l_whr;
1945 --
1946 RETURN (l_str);
1947 --
1948 END get_sql_from_vset_id;
1949 --
1950 --
1951 BEGIN
1952 --
1953 if g_debug then
1954 l_proc := g_package||'get_display_value';
1955 hr_utility.set_location('Entering:'||l_proc, 10);
1956 end if;
1957 --
1958 if (p_entity_id is not null and p_node_type_id is not null) then
1959 -- Open cursor to get the VS
1960 open csr_value_set;
1961 fetch csr_value_set into l_value_set_id;
1962 close csr_value_set;
1963 end if;
1964 --
1965
1966 if l_value_set_id is not null then
1967 --
1968 if g_debug then
1969 hr_utility.set_location('Non Null Value Set Id Retrieved', 20);
1970 end if;
1971 --
1972
1973 l_sql_statement := get_sql_from_vset_id(p_vset_id => l_value_set_id);
1974 --
1975 /*
1976 * Convert the sql statement in upper case just once for performance reasons
1977 * Remove Upper from all other places.
1978 */
1979 l_UPPER_SQL_statement := UPPER(l_sql_statement);
1980 --
1981
1982 --
1983 l_id_column := SUBSTR(l_UPPER_SQL_statement,(INSTR(l_UPPER_SQL_statement,'SELECT')
1984 +7) ,INSTR(l_UPPER_SQL_statement,',') -
1985 (INSTR(l_UPPER_SQL_statement,'SELECT')+ 7));
1986
1987 /*
1988 * Bug 4960280: Handle cases in which 'where' or 'order by' is the last word on a line and
1989 * the rest of the clause is on the other line.
1990 */
1991 if INSTR(l_UPPER_SQL_statement,'ORDER BY') > 0 then
1992 l_sql_statement := SUBSTR(l_sql_statement,1,(INSTR(l_UPPER_SQL_statement,'ORDER BY')-1));
1993 end if;
1994 --
1995 -- Append And clause if Where present, else add where clause.
1996 if INSTR(l_UPPER_SQL_statement,'WHERE') > 0 Then
1997 l_sql_statement := l_sql_statement||' and '||l_id_column||' = :id ';
1998 else
1999 l_sql_statement := l_sql_statement||' where '||l_id_column||' = :id ';
2000 end if;
2001 --
2002 l_sql_statement := REPLACE(l_sql_statement,':$PROFILES$.PER_BUSINESS_GROUP_ID'
2003 ,fnd_profile.value('PER_BUSINESS_GROUP_ID'));
2004 --
2005 if g_debug then
2006 hr_utility.set_location('Value Set Sql Retrieved and Processed', 30);
2007 end if;
2008 --
2009 BEGIN
2010 --
2011 EXECUTE IMMEDIATE l_sql_statement INTO l_value_id, l_name USING p_entity_id;
2012 --
2013 if g_debug then
2014 hr_utility.set_location('Valid Sql: Display Value Found', 40);
2015 end if;
2016 --
2017 EXCEPTION
2018 --
2019 WHEN OTHERS THEN
2020 --
2021 if g_debug then
2022 hr_utility.set_location('Invalid Entity Id or Value Set', 50);
2023 end if;
2024 --
2025 l_name := 'INVALID_VALUE_SET, vs_id :' || l_value_set_id || ', l_value_id: ' || p_entity_id;
2026 --
2027 END;
2028 --
2029 else
2030 --
2031 if g_debug then
2032 hr_utility.set_location('Value Set Id Not Found: Null', 60);
2033 end if;
2034 --
2035 end if;
2036 --
2037 if g_debug then
2038 hr_utility.set_location('Leaving:'||l_proc, 70);
2039 end if;
2040 --
2041 RETURN l_name;
2042 --
2043 END get_display_value;
2044 --
2045 -- ----------------------------------------------------------------------------
2046 -- |----------------------------<gen_hier_exists>-----------------------------|
2047 -- ----------------------------------------------------------------------------
2048 --
2049 Function gen_hier_exists (p_hierarchy_type in VARCHAR2)
2050 RETURN VARCHAR2 IS
2051 --
2052 --
2053 CURSOR csr_hexist IS
2054 select 'Y'
2055 from per_gen_hierarchy
2056 where type = p_hierarchy_type
2057 and rownum = 1;
2058 --
2059 l_return Varchar2(1) := null;
2060 l_proc Varchar2(72);
2061 --
2062 BEGIN
2063 --
2064 if g_debug then
2065 l_proc := g_package||'gen_hier_exists';
2066 hr_utility.set_location('Entering:'||l_proc, 10);
2067 end if;
2068 --
2069 open csr_hexist;
2070 fetch csr_hexist into l_return;
2071 close csr_hexist;
2072 --
2073 if g_debug then
2074 hr_utility.set_location('Leaving:'||l_proc||'with val:'||nvl(l_return,'N'),20);
2075 end if;
2076 RETURN nvl(l_return,'N');
2077 --
2078 END gen_hier_exists;
2079 --
2080 --
2081 -- ----------------------------------------------------------------------------
2082 -- |---------------------------<chk_multiple_versions>------------------------|
2083 -- ----------------------------------------------------------------------------
2084 --
2085 Function chk_multiple_versions(p_hierarchy_id in Number)
2086 Return Varchar2
2087 Is
2088 --
2089 Cursor csr_hier_versions
2090 Is
2091 Select 'Y'
2092 From per_gen_hierarchy_versions
2093 Where hierarchy_id = p_hierarchy_id
2094 And rownum < 3;
2095 --
2096 l_proc varchar2(72);
2097 --
2098 Begin
2099 --
2100 if g_debug then
2101 l_proc := g_package||'chk_multiple_versions';
2102 hr_utility.set_location('Entering:'||l_proc, 10);
2103 end if;
2104 --
2105 for l_rec in csr_hier_versions loop
2106 if csr_hier_versions%ROWCOUNT = 2 then
2107 if g_debug then
2108 hr_utility.set_location('Leaving:'||l_proc||' with val:Y', 20);
2109 end if;
2110 return 'Y';
2111 end if;
2112 end loop;
2113 --
2114 if g_debug then
2115 hr_utility.set_location('Leaving:'||l_proc||' with val:N', 30);
2116 end if;
2117 return 'N';
2118 --
2119 --
2120 End chk_multiple_versions;
2121 --
2122 Function Node_Sequence(P_Hierarchy_version_id IN Number,
2123 P_Parent_Hierarchy_Id IN Number)
2124 Return Number is
2125 Cursor Seq is
2126 Select Nvl(max(SEQ),0) + 1
2127 From Per_gen_Hierarchy_Nodes
2128 Where Hierarchy_Version_Id = p_Hierarchy_Version_id
2129 and Parent_Hierarchy_Node_Id = P_Parent_Hierarchy_Id;
2130
2131 l_Seq Per_Gen_hierarchy_nodes.Seq%TYPE;
2132
2133 Begin
2134 open Seq;
2135 Fetch Seq into l_seq;
2136 Close Seq;
2137 Return l_Seq;
2138 End;
2139 --
2140 Procedure Main
2141 (P_Type IN Varchar2,
2142 P_Trntype IN Varchar2,
2143 P_Code IN Varchar2 Default NULL,
2144 P_Description IN Varchar2 Default NULL,
2145 p_Code_Id IN Number Default NULL,
2146 P_Hierarchy_version_id IN Number Default NULL,
2147 P_Parent_Hierarchy_id IN Number Default NULL,
2148 P_Hierarchy_Id IN Number Default NULL,
2149 p_Object_Version_Number IN Number Default NULL,
2150 p_Business_group_Id IN Number ,
2151 p_Effective_Date IN Date) Is
2152
2153 l_Hierarchy_id Per_Gen_Hierarchy.Hierarchy_Id%TYPE;
2154 l_Hierarchy_Version_id Per_Gen_Hierarchy_Versions.Hierarchy_version_Id%TYPE;
2155 l_HObject_version_Number Per_Gen_Hierarchy.Object_version_Number%TYPE;
2156 l_Object_version_Number Per_Gen_Hierarchy.Object_version_Number%TYPE;
2157 l_VObject_version_Number Per_Gen_Hierarchy.Object_version_Number%TYPE;
2158 l_Hierarchy_Node_id Per_Gen_Hierarchy_Nodes.Hierarchy_Node_Id%TYPE;
2159 l_version_count Number(15);
2160
2161 Cursor C1 IS
2162 Select Hierarchy_Node_id, Object_version_number
2163 From Per_gen_Hierarchy_Nodes a
2164 Start with Hierarchy_Node_Id = P_Hierarchy_Id
2165 Connect by Parent_Hierarchy_Node_Id = Prior Hierarchy_Node_id
2166 Order By Nvl(Parent_Hierarchy_Node_Id,0) Desc;
2167
2168 Cursor C2 is
2169 Select Pgh.Hierarchy_id, pgh.Object_version_number hovn,
2170 pgv.Hierarchy_version_id, pgv.Object_Version_number vovn
2171 From Per_Gen_hierarchy_Versions pgv, Per_gen_hierarchy pgh
2172 Where Hierarchy_Version_id = P_Hierarchy_version_id
2173 and pgv.Hierarchy_id = pgh.Hierarchy_id;
2174
2175 Cursor C3 is
2176 Select Hierarchy_Node_Id, Object_Version_Number
2177 From Per_Gen_Hierarchy_Nodes
2178 Start With Hierarchy_Version_id = P_Hierarchy_Version_id
2179 and Parent_hierarchy_node_id is NULL
2180 Connect By Parent_hierarchy_Node_id = Prior Hierarchy_Node_id
2181 Order By Nvl(Parent_Hierarchy_Node_id,0) Desc;
2182
2183 Cursor C4 is
2184 Select count(*)
2185 From Per_Gen_Hierarchy_Versions pgv, Per_Gen_Hierarchy pgh
2186 Where pgh.Hierarchy_id = (Select Hierarchy_id
2187 From Per_Gen_Hierarchy_Versions
2188 Where Hierarchy_Version_Id = P_Hierarchy_Version_Id)
2189 and pgv.hierarchy_id = pgh.hierarchy_id;
2190
2191
2192 Begin
2193
2194 If p_Type = 'P' and P_Trntype = 'I' Then
2195
2196 Per_hierarchy_api.CREATE_HIERARCHY
2197 (P_HIERARCHY_ID => l_Hierarchy_id ,
2198 P_BUSINESS_GROUP_ID => p_Business_group_Id,
2199 P_NAME => P_Description ,
2200 P_TYPE => 'OPERATION_PLAN' ,
2201 P_OBJECT_VERSION_NUMBER => l_HObject_version_Number,
2202 P_EFFECTIVE_DATE => p_Effective_Date);
2203
2204 Per_hierarchy_versions_api.create_hierarchy_versions
2205 (P_HIERARCHY_VERSION_ID => l_Hierarchy_Version_id,
2206 P_BUSINESS_GROUP_ID => p_Business_group_Id,
2207 P_VERSION_NUMBER => 1,
2208 P_HIERARCHY_ID => l_Hierarchy_id,
2209 P_DATE_FROM => P_EFFECTIVE_DATE,
2210 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number ,
2211 P_STATUS => 'A',
2212 P_VALIDATE_FLAG => 'Y',
2213 P_EFFECTIVE_DATE => p_Effective_Date);
2214
2215 ElsIf P_Trntype = 'R' Then
2216 --Get the number of versions for the hierarchy
2217 Open C4;
2218 Fetch C4 into l_version_count;
2219 Close C4;
2220 For C2Rec in C2
2221 Loop
2222
2223 For C3rec in C3
2224 Loop
2225 l_Object_Version_Number := C3rec.Object_version_Number;
2226 Per_Hierarchy_Nodes_api.DELETE_HIERARCHY_NODES
2227 (P_Hierarchy_Node_Id => C3rec.Hierarchy_Node_id,
2228 P_Object_Version_Number => l_Object_Version_Number);
2229 End Loop;
2230
2231 l_object_version_number := c2rec.vovn;
2232 Per_Hierarchy_versions_api.DELETE_HIERARCHY_VERSIONS
2233 (P_HIERARCHY_VERSION_ID => C2rec.Hierarchy_version_id,
2234 P_OBJECT_VERSION_NUMBER => l_Object_version_Number,
2235 P_EFFECTIVE_DATE => P_Effective_Date);
2236
2237 if(l_version_count < 2) then
2238 l_object_version_number := c2rec.hovn;
2239 Per_Hierarchy_api.Delete_Hierarchy
2240 (P_Hierarchy_Id => C2rec.Hierarchy_id,
2241 P_Object_Version_Number => l_Object_Version_Number);
2242 End if;
2243 End Loop;
2244
2245 /* ElsIf p_Type = 'G' and P_Trntype = 'I' Then
2246
2247 PQH_DE_OPERATION_GROUPS_API.INSERT_OPERATION_GROUPS
2248 (P_EFFECTIVE_DATE => P_EFFECTIVE_DATE,
2249 P_OPERATION_GROUP_CODE => P_Code,
2250 P_DESCRIPTION => P_Description,
2251 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2252 P_OPERATION_GROUP_ID => l_Node_id,
2253 P_OBJECT_VERSION_NUMBER => l_Object_version_Number);
2254
2255 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
2256 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
2257 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2258 P_ENTITY_ID => P_Code,
2259 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
2260 P_NODE_TYPE => 'OPR_GROUP',
2261 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_Hierarchy_Id),
2262 P_PARENT_HIERARCHY_NODE_ID => P_Parent_Hierarchy_Id,
2263 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number,
2264 P_EFFECTIVE_DATE => p_Effective_Date);
2265
2266
2267 ElsIf p_Type = 'O' and P_Trntype = 'I' Then
2268
2269 PQH_DE_OPERATIONS_API.INSERT_OPERATIONS
2270 (P_EFFECTIVE_DATE => P_EFFECTIVE_DATE,
2271 P_OPERATION_NUMBER => P_Code,
2272 P_DESCRIPTION => P_Description,
2273 P_OPERATION_ID => L_Node_id,
2274 P_OBJECT_VERSION_NUMBER => l_Object_version_Number);
2275
2276 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
2277 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
2278 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2279 P_ENTITY_ID => P_Code,
2280 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
2281 P_NODE_TYPE => 'OPR_OPTS',
2282 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_Hierarchy_Id),
2283 P_PARENT_HIERARCHY_NODE_ID => P_Parent_Hierarchy_Id,
2284 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number,
2285 P_EFFECTIVE_DATE => p_Effective_Date);
2286
2287
2288 ElsIf p_Type = 'J' and P_Trntype = 'I' Then
2289
2290 PQH_DE_TKTDTLS_API.INSERT_TKT_DTLS
2291 (P_EFFECTIVE_DATE => P_EFFECTIVE_DATE,
2292 P_TATIGKEIT_NUMBER => P_Code,
2293 P_DESCRIPTION => P_Description,
2294 P_TATIGKEIT_DETAIL_ID => L_Node_id,
2295 P_OBJECT_VERSION_NUMBER => l_Object_version_Number);
2296
2297 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
2298 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
2299 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2300 P_ENTITY_ID => P_Code,
2301 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
2302 P_NODE_TYPE => 'OPR_JOB_DTLS',
2303 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_Hierarchy_Id),
2304 P_PARENT_HIERARCHY_NODE_ID => P_Parent_Hierarchy_Id,
2305 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number,
2306 P_EFFECTIVE_DATE => p_Effective_Date); */
2307
2308 ElsIf p_Type = 'F' and P_Trntype = 'I' Then
2309
2310 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
2311 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
2312 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2313 P_ENTITY_ID => P_Code,
2314 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
2315 P_NODE_TYPE => 'OPR_JOB_FTR',
2316 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_Hierarchy_Id),
2317 P_PARENT_HIERARCHY_NODE_ID => P_Parent_Hierarchy_Id,
2318 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number,
2319 P_EFFECTIVE_DATE => p_Effective_Date);
2320
2321 ElsIf P_Trntype = 'D' Then
2322
2323 For C1rec in C1
2324 Loop
2325
2326 l_Object_version_Number := C1rec.Object_version_Number;
2327
2328 Per_Hierarchy_Nodes_Api.DELETE_HIERARCHY_NODES
2329 (P_HIERARCHY_NODE_ID => C1rec.Hierarchy_Node_Id,
2330 P_OBJECT_VERSION_NUMBER => l_Object_Version_Number);
2331
2332 End Loop;
2333
2334 End If;
2335 End;
2336
2337 Procedure copy_Hierarchy
2338 (P_Hierarchy_version_id IN Number,
2339 P_Parent_Hierarchy_id IN Number,
2340 P_Hierarchy_Id IN Number,
2341 p_Business_group_Id IN Number,
2342 p_Effective_Date IN Date) Is
2343
2344 Cursor C1 IS
2345 Select Node_Type , Entity_Id , Hierarchy_Node_id , Parent_Hierarchy_Node_Id , Hierarchy_Version_Id , ATTRIBUTE_CATEGORY,
2346 ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 ,
2347 ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 , ATTRIBUTE16 , ATTRIBUTE17 , ATTRIBUTE18 ,
2348 ATTRIBUTE19 , ATTRIBUTE20 , ATTRIBUTE21 , ATTRIBUTE22 , ATTRIBUTE23 , ATTRIBUTE24 , ATTRIBUTE25 , ATTRIBUTE26 , ATTRIBUTE27 , ATTRIBUTE28 ,
2349 ATTRIBUTE29 , ATTRIBUTE30 , INFORMATION1 , INFORMATION2 , INFORMATION3 , INFORMATION4 , INFORMATION5 , INFORMATION6 , INFORMATION7 , INFORMATION8 ,
2350 INFORMATION9 , INFORMATION10, INFORMATION11, INFORMATION12, INFORMATION13, INFORMATION14, INFORMATION15, INFORMATION16, INFORMATION17, INFORMATION18,
2351 INFORMATION19 , INFORMATION20, INFORMATION21, INFORMATION22, INFORMATION23, INFORMATION24, INFORMATION25, INFORMATION26, INFORMATION27, INFORMATION28,
2352 INFORMATION29 , INFORMATION30, INFORMATION_CATEGORY
2353 From Per_gen_Hierarchy_Nodes a
2354 Start with Hierarchy_Node_Id = P_Hierarchy_Id
2355 Connect by Parent_Hierarchy_Node_Id = Prior Hierarchy_Node_id;
2356
2357 Cursor C2 (P_Parent_Hierarchy_Node_id IN Number) is
2358 Select Node_Type, Entity_id
2359 from Per_Gen_hierarchy_Nodes
2360 Where Hierarchy_Node_Id = P_Parent_Hierarchy_Node_id;
2361
2362 Cursor c3(P_Node_type IN Varchar2,
2363 P_Entity_Id Varchar2) Is
2364 Select hierarchy_Node_Id
2365 from Per_Gen_Hierarchy_Nodes
2366 Where Hierarchy_version_Id = p_Hierarchy_version_id
2367 and Node_type = P_Node_Type
2368 and Entity_Id = P_Entity_Id
2369 and Request_Id = -999;
2370
2371 l_Hierarchy_Node_id Per_Gen_Hierarchy_Nodes.Hierarchy_Node_Id%TYPE;
2372 l_Parent_hierarchy_Node_id Per_Gen_Hierarchy_Nodes.Parent_Hierarchy_Node_Id%TYPE;
2373 l_Object_version_Number Per_Gen_Hierarchy.Object_version_Number%TYPE;
2374 l_Node_type Per_Gen_Hierarchy_Nodes.Node_type%TYPE;
2375 l_Entity_Id Per_Gen_Hierarchy_Nodes.Entity_Id%TYPE;
2376
2377 Begin
2378 l_Parent_hierarchy_Node_Id := NULL;
2379 For C1rec in C1
2380 Loop
2381 If l_Parent_hierarchy_Node_Id Is NULL Then
2382 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
2383 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
2384 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2385 P_ENTITY_ID => C1rec.Entity_id,
2386 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
2387 P_NODE_TYPE => C1rec.Node_type,
2388 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_hierarchy_Id),
2389 P_PARENT_HIERARCHY_NODE_ID => P_Parent_hierarchy_Id,
2390 P_OBJECT_VERSION_NUMBER => l_Object_version_Number,
2391 P_REQUEST_ID => -999,
2392 P_ATTRIBUTE_CATEGORY => C1rec.Attribute_Category,
2393 P_ATTRIBUTE1 => C1rec.Attribute1,
2394 P_ATTRIBUTE2 => C1rec.Attribute2,
2395 P_ATTRIBUTE3 => C1rec.Attribute3,
2396 P_ATTRIBUTE4 => C1rec.Attribute4,
2397 P_ATTRIBUTE5 => C1rec.Attribute5,
2398 P_ATTRIBUTE6 => C1rec.Attribute6,
2399 P_ATTRIBUTE7 => C1rec.Attribute7,
2400 P_ATTRIBUTE8 => C1rec.Attribute8,
2401 P_ATTRIBUTE9 => C1rec.Attribute9,
2402 P_ATTRIBUTE10 => C1rec.Attribute10,
2403 P_ATTRIBUTE11 => C1rec.Attribute11,
2404 P_ATTRIBUTE12 => C1rec.Attribute12,
2405 P_ATTRIBUTE13 => C1rec.Attribute13,
2406 P_ATTRIBUTE14 => C1rec.Attribute14,
2407 P_ATTRIBUTE15 => C1rec.Attribute15,
2408 P_ATTRIBUTE16 => C1rec.Attribute16,
2409 P_ATTRIBUTE17 => C1rec.Attribute17,
2410 P_ATTRIBUTE18 => C1rec.Attribute18,
2411 P_ATTRIBUTE19 => C1rec.Attribute19,
2412 P_ATTRIBUTE20 => C1rec.Attribute20,
2413 P_ATTRIBUTE21 => C1rec.Attribute21,
2414 P_ATTRIBUTE22 => C1rec.Attribute22,
2415 P_ATTRIBUTE23 => C1rec.Attribute23,
2416 P_ATTRIBUTE24 => C1rec.Attribute24,
2417 P_ATTRIBUTE25 => C1rec.Attribute25,
2418 P_ATTRIBUTE26 => C1rec.Attribute26,
2419 P_ATTRIBUTE27 => C1rec.Attribute27,
2420 P_ATTRIBUTE28 => C1rec.Attribute28,
2421 P_ATTRIBUTE29 => C1rec.Attribute29,
2422 P_ATTRIBUTE30 => C1rec.Attribute30,
2423 P_INFORMATION_CATEGORY => C1rec.Information_Category,
2424 P_INFORMATION1 => C1rec.Information1,
2425 P_INFORMATION2 => C1rec.Information2,
2426 P_INFORMATION3 => C1rec.Information3,
2427 P_INFORMATION4 => C1rec.Information4,
2428 P_INFORMATION5 => C1rec.Information5,
2429 P_INFORMATION6 => C1rec.Information6,
2430 P_INFORMATION7 => C1rec.Information7,
2431 P_INFORMATION8 => C1rec.Information8,
2432 P_INFORMATION9 => C1rec.Information9,
2433 P_INFORMATION10 => C1rec.Information10,
2434 P_INFORMATION11 => C1rec.Information11,
2435 P_INFORMATION12 => C1rec.Information12,
2436 P_INFORMATION13 => C1rec.Information13,
2437 P_INFORMATION14 => C1rec.Information14,
2438 P_INFORMATION15 => C1rec.Information15,
2439 P_INFORMATION16 => C1rec.Information16,
2440 P_INFORMATION17 => C1rec.Information17,
2441 P_INFORMATION18 => C1rec.Information18,
2442 P_INFORMATION19 => C1rec.Information19,
2443 P_INFORMATION20 => C1rec.Information20,
2444 P_INFORMATION21 => C1rec.Information21,
2445 P_INFORMATION22 => C1rec.Information22,
2446 P_INFORMATION23 => C1rec.Information23,
2447 P_INFORMATION24 => C1rec.Information24,
2448 P_INFORMATION25 => C1rec.Information25,
2449 P_INFORMATION26 => C1rec.Information26,
2450 P_INFORMATION27 => C1rec.Information27,
2451 P_INFORMATION28 => C1rec.Information28,
2452 P_INFORMATION29 => C1rec.Information29,
2453 P_INFORMATION30 => C1rec.Information30,
2454 P_EFFECTIVE_DATE => p_Effective_Date);
2455 l_Parent_hierarchy_Node_Id := Nvl(P_Parent_hierarchy_Id, 0);
2456
2457 Else
2458 Open C2(C1rec.Parent_hierarchy_node_Id);
2459 Fetch C2 into L_Node_Type, l_Entity_Id;
2460 Close C2;
2461 Open C3(l_Node_type, l_Entity_Id);
2462 Fetch C3 into l_Parent_Hierarchy_Node_id;
2463 Close C3;
2464 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
2465 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
2466 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2467 P_ENTITY_ID => C1rec.Entity_id,
2468 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
2469 P_NODE_TYPE => C1rec.Node_type,
2470 P_SEQ => Node_Sequence(P_Hierarchy_version_id,l_Parent_Hierarchy_Node_Id),
2471 P_PARENT_HIERARCHY_NODE_ID => l_Parent_hierarchy_Node_id,
2472 P_OBJECT_VERSION_NUMBER => l_Object_version_Number,
2473 P_REQUEST_ID => -999,
2474 P_ATTRIBUTE_CATEGORY => C1rec.Attribute_Category,
2475 P_ATTRIBUTE1 => C1rec.Attribute1,
2476 P_ATTRIBUTE2 => C1rec.Attribute2,
2477 P_ATTRIBUTE3 => C1rec.Attribute3,
2478 P_ATTRIBUTE4 => C1rec.Attribute4,
2479 P_ATTRIBUTE5 => C1rec.Attribute5,
2480 P_ATTRIBUTE6 => C1rec.Attribute6,
2481 P_ATTRIBUTE7 => C1rec.Attribute7,
2482 P_ATTRIBUTE8 => C1rec.Attribute8,
2483 P_ATTRIBUTE9 => C1rec.Attribute9,
2484 P_ATTRIBUTE10 => C1rec.Attribute10,
2485 P_ATTRIBUTE11 => C1rec.Attribute11,
2486 P_ATTRIBUTE12 => C1rec.Attribute12,
2487 P_ATTRIBUTE13 => C1rec.Attribute13,
2488 P_ATTRIBUTE14 => C1rec.Attribute14,
2489 P_ATTRIBUTE15 => C1rec.Attribute15,
2490 P_ATTRIBUTE16 => C1rec.Attribute16,
2491 P_ATTRIBUTE17 => C1rec.Attribute17,
2492 P_ATTRIBUTE18 => C1rec.Attribute18,
2493 P_ATTRIBUTE19 => C1rec.Attribute19,
2494 P_ATTRIBUTE20 => C1rec.Attribute20,
2495 P_ATTRIBUTE21 => C1rec.Attribute21,
2496 P_ATTRIBUTE22 => C1rec.Attribute22,
2497 P_ATTRIBUTE23 => C1rec.Attribute23,
2498 P_ATTRIBUTE24 => C1rec.Attribute24,
2499 P_ATTRIBUTE25 => C1rec.Attribute25,
2500 P_ATTRIBUTE26 => C1rec.Attribute26,
2501 P_ATTRIBUTE27 => C1rec.Attribute27,
2502 P_ATTRIBUTE28 => C1rec.Attribute28,
2503 P_ATTRIBUTE29 => C1rec.Attribute29,
2504 P_ATTRIBUTE30 => C1rec.Attribute30,
2505 P_INFORMATION_CATEGORY => C1rec.Information_Category,
2506 P_INFORMATION1 => C1rec.Information1,
2507 P_INFORMATION2 => C1rec.Information2,
2508 P_INFORMATION3 => C1rec.Information3,
2509 P_INFORMATION4 => C1rec.Information4,
2510 P_INFORMATION5 => C1rec.Information5,
2511 P_INFORMATION6 => C1rec.Information6,
2512 P_INFORMATION7 => C1rec.Information7,
2513 P_INFORMATION8 => C1rec.Information8,
2514 P_INFORMATION9 => C1rec.Information9,
2515 P_INFORMATION10 => C1rec.Information10,
2516 P_INFORMATION11 => C1rec.Information11,
2517 P_INFORMATION12 => C1rec.Information12,
2518 P_INFORMATION13 => C1rec.Information13,
2519 P_INFORMATION14 => C1rec.Information14,
2520 P_INFORMATION15 => C1rec.Information15,
2521 P_INFORMATION16 => C1rec.Information16,
2522 P_INFORMATION17 => C1rec.Information17,
2523 P_INFORMATION18 => C1rec.Information18,
2524 P_INFORMATION19 => C1rec.Information19,
2525 P_INFORMATION20 => C1rec.Information20,
2526 P_INFORMATION21 => C1rec.Information21,
2527 P_INFORMATION22 => C1rec.Information22,
2528 P_INFORMATION23 => C1rec.Information23,
2529 P_INFORMATION24 => C1rec.Information24,
2530 P_INFORMATION25 => C1rec.Information25,
2531 P_INFORMATION26 => C1rec.Information26,
2532 P_INFORMATION27 => C1rec.Information27,
2533 P_INFORMATION28 => C1rec.Information28,
2534 P_INFORMATION29 => C1rec.Information29,
2535 P_INFORMATION30 => C1rec.Information30,
2536 P_EFFECTIVE_DATE => p_Effective_Date);
2537 End If;
2538
2539 End Loop;
2540
2541 Update Per_Gen_Hierarchy_Nodes
2542 Set REQUEST_ID = 0
2543 Where REQUEST_ID = -999;
2544 End;
2545 --
2546 End;