[Home] [Help]
PACKAGE BODY: APPS.PQH_GENERIC_HIERARCHY_PACKAGE
Source
1 package body pqh_generic_hierarchy_package
2 /* $Header: pqghrpkg.pkb 120.3 2011/07/14 13:19:36 lbodired ship $ */
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 l_proc VARCHAR2(80) := 'get_display_value'; -- Fix for the bug#12754824
1926 --
1927 --
1928 FUNCTION get_sql_from_vset_id(p_vset_id IN NUMBER) RETURN VARCHAR2 IS
1929 --
1930 l_v_r fnd_vset.valueset_r;
1931 l_v_dr fnd_vset.valueset_dr;
1932 l_str varchar2(4000);
1933 l_whr varchar2(4000);
1934 --
1935 BEGIN
1936 --
1937 fnd_vset.get_valueset(valueset_id => p_vset_id ,
1938 valueset => l_v_r,
1939 format => l_v_dr);
1940 --
1941 l_whr := l_v_r.table_info.where_clause ;
1942 l_str := 'select '||substr(l_v_r.table_info.id_column_name,1,instr(l_v_r.table_info.id_column_name||' ',' '))||','
1943 ||substr(l_v_r.table_info.value_column_name,1,instr(l_v_r.table_info.value_column_name||' ',' '))
1944 ||' from '
1945 ||l_v_r.table_info.table_name||' '||l_whr;
1946 --
1947 RETURN (l_str);
1948 --
1949 END get_sql_from_vset_id;
1950 --
1951 --
1952 BEGIN
1953 --
1954 if g_debug then
1955 l_proc := g_package||'get_display_value';
1956 hr_utility.set_location('Entering:'||l_proc, 10);
1957 end if;
1958 --
1959 if (p_entity_id is not null and p_node_type_id is not null) then
1960 -- Open cursor to get the VS
1961 open csr_value_set;
1962 fetch csr_value_set into l_value_set_id;
1963 close csr_value_set;
1964 end if;
1965 --
1966
1967 if l_value_set_id is not null then
1968 --
1969 if g_debug then
1970 hr_utility.set_location('Non Null Value Set Id Retrieved', 20);
1971 end if;
1972 --
1973
1974 l_sql_statement := get_sql_from_vset_id(p_vset_id => l_value_set_id);
1975 --
1976 /*
1977 * Convert the sql statement in upper case just once for performance reasons
1978 * Remove Upper from all other places.
1979 */
1980 l_UPPER_SQL_statement := UPPER(l_sql_statement);
1981 --
1982
1983 --
1984 l_id_column := SUBSTR(l_UPPER_SQL_statement,(INSTR(l_UPPER_SQL_statement,'SELECT')
1985 +7) ,INSTR(l_UPPER_SQL_statement,',') -
1986 (INSTR(l_UPPER_SQL_statement,'SELECT')+ 7));
1987
1988 /*
1989 * Bug 4960280: Handle cases in which 'where' or 'order by' is the last word on a line and
1990 * the rest of the clause is on the other line.
1991 */
1992 if INSTR(l_UPPER_SQL_statement,'ORDER BY') > 0 then
1993 l_sql_statement := SUBSTR(l_sql_statement,1,(INSTR(l_UPPER_SQL_statement,'ORDER BY')-1));
1994 end if;
1995 --
1996 -- Append And clause if Where present, else add where clause.
1997 if INSTR(l_UPPER_SQL_statement,'WHERE') > 0 Then
1998 l_sql_statement := l_sql_statement||' and '||l_id_column||' = :id ';
1999 else
2000 l_sql_statement := l_sql_statement||' where '||l_id_column||' = :id ';
2001 end if;
2002 --
2003 l_sql_statement := REPLACE(l_sql_statement,':$PROFILES$.PER_BUSINESS_GROUP_ID'
2004 ,fnd_profile.value('PER_BUSINESS_GROUP_ID'));
2005 --
2006 if g_debug then
2007 hr_utility.set_location('Value Set Sql Retrieved and Processed', 30);
2008 end if;
2009 --
2010 BEGIN
2011 --
2012 EXECUTE IMMEDIATE l_sql_statement INTO l_value_id, l_name USING p_entity_id;
2013 --
2014 if g_debug then
2015 hr_utility.set_location('Valid Sql: Display Value Found', 40);
2016 end if;
2017 --
2018 EXCEPTION
2019 --
2020 WHEN OTHERS THEN
2021 --
2022 if g_debug then
2023 hr_utility.set_location('Invalid Entity Id or Value Set', 50);
2024 end if;
2025 --
2026 l_name := 'INVALID_VALUE_SET, vs_id :' || l_value_set_id || ', l_value_id: ' || p_entity_id;
2027 --
2028 END;
2029 --
2030 else
2031 --
2032 if g_debug then
2033 hr_utility.set_location('Value Set Id Not Found: Null', 60);
2034 end if;
2035 --
2036 end if;
2037 --
2038 if g_debug then
2039 hr_utility.set_location('Leaving:'||l_proc, 70);
2040 end if;
2041 --
2042 RETURN l_name;
2043 --
2044 END get_display_value;
2045 --
2046 -- ----------------------------------------------------------------------------
2047 -- |----------------------------<gen_hier_exists>-----------------------------|
2048 -- ----------------------------------------------------------------------------
2049 --
2050 Function gen_hier_exists (p_hierarchy_type in VARCHAR2)
2051 RETURN VARCHAR2 IS
2052 --
2053 --
2054 CURSOR csr_hexist IS
2055 select 'Y'
2056 from per_gen_hierarchy
2057 where type = p_hierarchy_type
2058 and rownum = 1;
2059 --
2060 l_return Varchar2(1) := null;
2061 l_proc Varchar2(72);
2062 --
2063 BEGIN
2064 --
2065 if g_debug then
2066 l_proc := g_package||'gen_hier_exists';
2067 hr_utility.set_location('Entering:'||l_proc, 10);
2068 end if;
2069 --
2070 open csr_hexist;
2071 fetch csr_hexist into l_return;
2072 close csr_hexist;
2073 --
2074 if g_debug then
2075 hr_utility.set_location('Leaving:'||l_proc||'with val:'||nvl(l_return,'N'),20);
2076 end if;
2077 RETURN nvl(l_return,'N');
2078 --
2079 END gen_hier_exists;
2080 --
2081 --
2082 -- ----------------------------------------------------------------------------
2083 -- |---------------------------<chk_multiple_versions>------------------------|
2084 -- ----------------------------------------------------------------------------
2085 --
2086 Function chk_multiple_versions(p_hierarchy_id in Number)
2087 Return Varchar2
2088 Is
2089 --
2090 Cursor csr_hier_versions
2091 Is
2092 Select 'Y'
2093 From per_gen_hierarchy_versions
2094 Where hierarchy_id = p_hierarchy_id
2095 And rownum < 3;
2096 --
2097 l_proc varchar2(72);
2098 --
2099 Begin
2100 --
2101 if g_debug then
2102 l_proc := g_package||'chk_multiple_versions';
2103 hr_utility.set_location('Entering:'||l_proc, 10);
2104 end if;
2105 --
2106 for l_rec in csr_hier_versions loop
2107 if csr_hier_versions%ROWCOUNT = 2 then
2108 if g_debug then
2109 hr_utility.set_location('Leaving:'||l_proc||' with val:Y', 20);
2110 end if;
2111 return 'Y';
2112 end if;
2113 end loop;
2114 --
2115 if g_debug then
2116 hr_utility.set_location('Leaving:'||l_proc||' with val:N', 30);
2117 end if;
2118 return 'N';
2119 --
2120 --
2121 End chk_multiple_versions;
2122 --
2123 Function Node_Sequence(P_Hierarchy_version_id IN Number,
2124 P_Parent_Hierarchy_Id IN Number)
2125 Return Number is
2126 Cursor Seq is
2127 Select Nvl(max(SEQ),0) + 1
2128 From Per_gen_Hierarchy_Nodes
2129 Where Hierarchy_Version_Id = p_Hierarchy_Version_id
2130 and Parent_Hierarchy_Node_Id = P_Parent_Hierarchy_Id;
2131
2132 l_Seq Per_Gen_hierarchy_nodes.Seq%TYPE;
2133
2134 Begin
2135 open Seq;
2136 Fetch Seq into l_seq;
2137 Close Seq;
2138 Return l_Seq;
2139 End;
2140 --
2141 Procedure Main
2142 (P_Type IN Varchar2,
2143 P_Trntype IN Varchar2,
2144 P_Code IN Varchar2 Default NULL,
2145 P_Description IN Varchar2 Default NULL,
2146 p_Code_Id IN Number Default NULL,
2147 P_Hierarchy_version_id IN Number Default NULL,
2148 P_Parent_Hierarchy_id IN Number Default NULL,
2149 P_Hierarchy_Id IN Number Default NULL,
2150 p_Object_Version_Number IN Number Default NULL,
2151 p_Business_group_Id IN Number ,
2152 p_Effective_Date IN Date) Is
2153
2154 l_Hierarchy_id Per_Gen_Hierarchy.Hierarchy_Id%TYPE;
2155 l_Hierarchy_Version_id Per_Gen_Hierarchy_Versions.Hierarchy_version_Id%TYPE;
2156 l_HObject_version_Number Per_Gen_Hierarchy.Object_version_Number%TYPE;
2157 l_Object_version_Number Per_Gen_Hierarchy.Object_version_Number%TYPE;
2158 l_VObject_version_Number Per_Gen_Hierarchy.Object_version_Number%TYPE;
2159 l_Hierarchy_Node_id Per_Gen_Hierarchy_Nodes.Hierarchy_Node_Id%TYPE;
2160 l_version_count Number(15);
2161
2162 Cursor C1 IS
2163 Select Hierarchy_Node_id, Object_version_number
2164 From Per_gen_Hierarchy_Nodes a
2165 Start with Hierarchy_Node_Id = P_Hierarchy_Id
2166 Connect by Parent_Hierarchy_Node_Id = Prior Hierarchy_Node_id
2167 Order By Nvl(Parent_Hierarchy_Node_Id,0) Desc;
2168
2169 Cursor C2 is
2170 Select Pgh.Hierarchy_id, pgh.Object_version_number hovn,
2171 pgv.Hierarchy_version_id, pgv.Object_Version_number vovn
2172 From Per_Gen_hierarchy_Versions pgv, Per_gen_hierarchy pgh
2173 Where Hierarchy_Version_id = P_Hierarchy_version_id
2174 and pgv.Hierarchy_id = pgh.Hierarchy_id;
2175
2176 Cursor C3 is
2177 Select Hierarchy_Node_Id, Object_Version_Number
2178 From Per_Gen_Hierarchy_Nodes
2179 Start With Hierarchy_Version_id = P_Hierarchy_Version_id
2180 and Parent_hierarchy_node_id is NULL
2181 Connect By Parent_hierarchy_Node_id = Prior Hierarchy_Node_id
2182 Order By Nvl(Parent_Hierarchy_Node_id,0) Desc;
2183
2184 Cursor C4 is
2185 Select count(*)
2186 From Per_Gen_Hierarchy_Versions pgv, Per_Gen_Hierarchy pgh
2187 Where pgh.Hierarchy_id = (Select Hierarchy_id
2188 From Per_Gen_Hierarchy_Versions
2189 Where Hierarchy_Version_Id = P_Hierarchy_Version_Id)
2190 and pgv.hierarchy_id = pgh.hierarchy_id;
2191
2192
2193 Begin
2194
2195 If p_Type = 'P' and P_Trntype = 'I' Then
2196
2197 Per_hierarchy_api.CREATE_HIERARCHY
2198 (P_HIERARCHY_ID => l_Hierarchy_id ,
2199 P_BUSINESS_GROUP_ID => p_Business_group_Id,
2200 P_NAME => P_Description ,
2201 P_TYPE => 'OPERATION_PLAN' ,
2202 P_OBJECT_VERSION_NUMBER => l_HObject_version_Number,
2203 P_EFFECTIVE_DATE => p_Effective_Date);
2204
2205 Per_hierarchy_versions_api.create_hierarchy_versions
2206 (P_HIERARCHY_VERSION_ID => l_Hierarchy_Version_id,
2207 P_BUSINESS_GROUP_ID => p_Business_group_Id,
2208 P_VERSION_NUMBER => 1,
2209 P_HIERARCHY_ID => l_Hierarchy_id,
2210 P_DATE_FROM => P_EFFECTIVE_DATE,
2211 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number ,
2212 P_STATUS => 'A',
2213 P_VALIDATE_FLAG => 'Y',
2214 P_EFFECTIVE_DATE => p_Effective_Date);
2215
2216 ElsIf P_Trntype = 'R' Then
2217 --Get the number of versions for the hierarchy
2218 Open C4;
2219 Fetch C4 into l_version_count;
2220 Close C4;
2221 For C2Rec in C2
2222 Loop
2223
2224 For C3rec in C3
2225 Loop
2226 l_Object_Version_Number := C3rec.Object_version_Number;
2227 Per_Hierarchy_Nodes_api.DELETE_HIERARCHY_NODES
2228 (P_Hierarchy_Node_Id => C3rec.Hierarchy_Node_id,
2229 P_Object_Version_Number => l_Object_Version_Number);
2230 End Loop;
2231
2232 l_object_version_number := c2rec.vovn;
2233 Per_Hierarchy_versions_api.DELETE_HIERARCHY_VERSIONS
2234 (P_HIERARCHY_VERSION_ID => C2rec.Hierarchy_version_id,
2235 P_OBJECT_VERSION_NUMBER => l_Object_version_Number,
2236 P_EFFECTIVE_DATE => P_Effective_Date);
2237
2238 if(l_version_count < 2) then
2239 l_object_version_number := c2rec.hovn;
2240 Per_Hierarchy_api.Delete_Hierarchy
2241 (P_Hierarchy_Id => C2rec.Hierarchy_id,
2242 P_Object_Version_Number => l_Object_Version_Number);
2243 End if;
2244 End Loop;
2245
2246 /* ElsIf p_Type = 'G' and P_Trntype = 'I' Then
2247
2248 PQH_DE_OPERATION_GROUPS_API.INSERT_OPERATION_GROUPS
2249 (P_EFFECTIVE_DATE => P_EFFECTIVE_DATE,
2250 P_OPERATION_GROUP_CODE => P_Code,
2251 P_DESCRIPTION => P_Description,
2252 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2253 P_OPERATION_GROUP_ID => l_Node_id,
2254 P_OBJECT_VERSION_NUMBER => l_Object_version_Number);
2255
2256 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
2257 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
2258 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2259 P_ENTITY_ID => P_Code,
2260 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
2261 P_NODE_TYPE => 'OPR_GROUP',
2262 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_Hierarchy_Id),
2263 P_PARENT_HIERARCHY_NODE_ID => P_Parent_Hierarchy_Id,
2264 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number,
2265 P_EFFECTIVE_DATE => p_Effective_Date);
2266
2267
2268 ElsIf p_Type = 'O' and P_Trntype = 'I' Then
2269
2270 PQH_DE_OPERATIONS_API.INSERT_OPERATIONS
2271 (P_EFFECTIVE_DATE => P_EFFECTIVE_DATE,
2272 P_OPERATION_NUMBER => P_Code,
2273 P_DESCRIPTION => P_Description,
2274 P_OPERATION_ID => L_Node_id,
2275 P_OBJECT_VERSION_NUMBER => l_Object_version_Number);
2276
2277 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
2278 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
2279 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2280 P_ENTITY_ID => P_Code,
2281 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
2282 P_NODE_TYPE => 'OPR_OPTS',
2283 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_Hierarchy_Id),
2284 P_PARENT_HIERARCHY_NODE_ID => P_Parent_Hierarchy_Id,
2285 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number,
2286 P_EFFECTIVE_DATE => p_Effective_Date);
2287
2288
2289 ElsIf p_Type = 'J' and P_Trntype = 'I' Then
2290
2291 PQH_DE_TKTDTLS_API.INSERT_TKT_DTLS
2292 (P_EFFECTIVE_DATE => P_EFFECTIVE_DATE,
2293 P_TATIGKEIT_NUMBER => P_Code,
2294 P_DESCRIPTION => P_Description,
2295 P_TATIGKEIT_DETAIL_ID => L_Node_id,
2296 P_OBJECT_VERSION_NUMBER => l_Object_version_Number);
2297
2298 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
2299 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
2300 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2301 P_ENTITY_ID => P_Code,
2302 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
2303 P_NODE_TYPE => 'OPR_JOB_DTLS',
2304 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_Hierarchy_Id),
2305 P_PARENT_HIERARCHY_NODE_ID => P_Parent_Hierarchy_Id,
2306 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number,
2307 P_EFFECTIVE_DATE => p_Effective_Date); */
2308
2309 ElsIf p_Type = 'F' and P_Trntype = 'I' Then
2310
2311 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
2312 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
2313 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2314 P_ENTITY_ID => P_Code,
2315 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
2316 P_NODE_TYPE => 'OPR_JOB_FTR',
2317 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_Hierarchy_Id),
2318 P_PARENT_HIERARCHY_NODE_ID => P_Parent_Hierarchy_Id,
2319 P_OBJECT_VERSION_NUMBER => l_VObject_version_Number,
2320 P_EFFECTIVE_DATE => p_Effective_Date);
2321
2322 ElsIf P_Trntype = 'D' Then
2323
2324 For C1rec in C1
2325 Loop
2326
2327 l_Object_version_Number := C1rec.Object_version_Number;
2328
2329 Per_Hierarchy_Nodes_Api.DELETE_HIERARCHY_NODES
2330 (P_HIERARCHY_NODE_ID => C1rec.Hierarchy_Node_Id,
2331 P_OBJECT_VERSION_NUMBER => l_Object_Version_Number);
2332
2333 End Loop;
2334
2335 End If;
2336 End;
2337
2338 Procedure copy_Hierarchy
2339 (P_Hierarchy_version_id IN Number,
2340 P_Parent_Hierarchy_id IN Number,
2341 P_Hierarchy_Id IN Number,
2342 p_Business_group_Id IN Number,
2343 p_Effective_Date IN Date) Is
2344
2345 Cursor C1 IS
2346 Select Node_Type , Entity_Id , Hierarchy_Node_id , Parent_Hierarchy_Node_Id , Hierarchy_Version_Id , ATTRIBUTE_CATEGORY,
2347 ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 ,
2348 ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 , ATTRIBUTE16 , ATTRIBUTE17 , ATTRIBUTE18 ,
2349 ATTRIBUTE19 , ATTRIBUTE20 , ATTRIBUTE21 , ATTRIBUTE22 , ATTRIBUTE23 , ATTRIBUTE24 , ATTRIBUTE25 , ATTRIBUTE26 , ATTRIBUTE27 , ATTRIBUTE28 ,
2350 ATTRIBUTE29 , ATTRIBUTE30 , INFORMATION1 , INFORMATION2 , INFORMATION3 , INFORMATION4 , INFORMATION5 , INFORMATION6 , INFORMATION7 , INFORMATION8 ,
2351 INFORMATION9 , INFORMATION10, INFORMATION11, INFORMATION12, INFORMATION13, INFORMATION14, INFORMATION15, INFORMATION16, INFORMATION17, INFORMATION18,
2352 INFORMATION19 , INFORMATION20, INFORMATION21, INFORMATION22, INFORMATION23, INFORMATION24, INFORMATION25, INFORMATION26, INFORMATION27, INFORMATION28,
2353 INFORMATION29 , INFORMATION30, INFORMATION_CATEGORY
2354 From Per_gen_Hierarchy_Nodes a
2355 Start with Hierarchy_Node_Id = P_Hierarchy_Id
2356 Connect by Parent_Hierarchy_Node_Id = Prior Hierarchy_Node_id;
2357
2358 Cursor C2 (P_Parent_Hierarchy_Node_id IN Number) is
2359 Select Node_Type, Entity_id
2360 from Per_Gen_hierarchy_Nodes
2361 Where Hierarchy_Node_Id = P_Parent_Hierarchy_Node_id;
2362
2363 Cursor c3(P_Node_type IN Varchar2,
2364 P_Entity_Id Varchar2) Is
2365 Select hierarchy_Node_Id
2366 from Per_Gen_Hierarchy_Nodes
2367 Where Hierarchy_version_Id = p_Hierarchy_version_id
2368 and Node_type = P_Node_Type
2369 and Entity_Id = P_Entity_Id
2370 and Request_Id = -999;
2371
2372 l_Hierarchy_Node_id Per_Gen_Hierarchy_Nodes.Hierarchy_Node_Id%TYPE;
2373 l_Parent_hierarchy_Node_id Per_Gen_Hierarchy_Nodes.Parent_Hierarchy_Node_Id%TYPE;
2374 l_Object_version_Number Per_Gen_Hierarchy.Object_version_Number%TYPE;
2375 l_Node_type Per_Gen_Hierarchy_Nodes.Node_type%TYPE;
2376 l_Entity_Id Per_Gen_Hierarchy_Nodes.Entity_Id%TYPE;
2377
2378 Begin
2379 l_Parent_hierarchy_Node_Id := NULL;
2380 For C1rec in C1
2381 Loop
2382 If l_Parent_hierarchy_Node_Id Is NULL Then
2383 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
2384 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
2385 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2386 P_ENTITY_ID => C1rec.Entity_id,
2387 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
2388 P_NODE_TYPE => C1rec.Node_type,
2389 P_SEQ => Node_Sequence(P_Hierarchy_version_id,P_Parent_hierarchy_Id),
2390 P_PARENT_HIERARCHY_NODE_ID => P_Parent_hierarchy_Id,
2391 P_OBJECT_VERSION_NUMBER => l_Object_version_Number,
2392 P_REQUEST_ID => -999,
2393 P_ATTRIBUTE_CATEGORY => C1rec.Attribute_Category,
2394 P_ATTRIBUTE1 => C1rec.Attribute1,
2395 P_ATTRIBUTE2 => C1rec.Attribute2,
2396 P_ATTRIBUTE3 => C1rec.Attribute3,
2397 P_ATTRIBUTE4 => C1rec.Attribute4,
2398 P_ATTRIBUTE5 => C1rec.Attribute5,
2399 P_ATTRIBUTE6 => C1rec.Attribute6,
2400 P_ATTRIBUTE7 => C1rec.Attribute7,
2401 P_ATTRIBUTE8 => C1rec.Attribute8,
2402 P_ATTRIBUTE9 => C1rec.Attribute9,
2403 P_ATTRIBUTE10 => C1rec.Attribute10,
2404 P_ATTRIBUTE11 => C1rec.Attribute11,
2405 P_ATTRIBUTE12 => C1rec.Attribute12,
2406 P_ATTRIBUTE13 => C1rec.Attribute13,
2407 P_ATTRIBUTE14 => C1rec.Attribute14,
2408 P_ATTRIBUTE15 => C1rec.Attribute15,
2409 P_ATTRIBUTE16 => C1rec.Attribute16,
2410 P_ATTRIBUTE17 => C1rec.Attribute17,
2411 P_ATTRIBUTE18 => C1rec.Attribute18,
2412 P_ATTRIBUTE19 => C1rec.Attribute19,
2413 P_ATTRIBUTE20 => C1rec.Attribute20,
2414 P_ATTRIBUTE21 => C1rec.Attribute21,
2415 P_ATTRIBUTE22 => C1rec.Attribute22,
2416 P_ATTRIBUTE23 => C1rec.Attribute23,
2417 P_ATTRIBUTE24 => C1rec.Attribute24,
2418 P_ATTRIBUTE25 => C1rec.Attribute25,
2419 P_ATTRIBUTE26 => C1rec.Attribute26,
2420 P_ATTRIBUTE27 => C1rec.Attribute27,
2421 P_ATTRIBUTE28 => C1rec.Attribute28,
2422 P_ATTRIBUTE29 => C1rec.Attribute29,
2423 P_ATTRIBUTE30 => C1rec.Attribute30,
2424 P_INFORMATION_CATEGORY => C1rec.Information_Category,
2425 P_INFORMATION1 => C1rec.Information1,
2426 P_INFORMATION2 => C1rec.Information2,
2427 P_INFORMATION3 => C1rec.Information3,
2428 P_INFORMATION4 => C1rec.Information4,
2429 P_INFORMATION5 => C1rec.Information5,
2430 P_INFORMATION6 => C1rec.Information6,
2431 P_INFORMATION7 => C1rec.Information7,
2432 P_INFORMATION8 => C1rec.Information8,
2433 P_INFORMATION9 => C1rec.Information9,
2434 P_INFORMATION10 => C1rec.Information10,
2435 P_INFORMATION11 => C1rec.Information11,
2436 P_INFORMATION12 => C1rec.Information12,
2437 P_INFORMATION13 => C1rec.Information13,
2438 P_INFORMATION14 => C1rec.Information14,
2439 P_INFORMATION15 => C1rec.Information15,
2440 P_INFORMATION16 => C1rec.Information16,
2441 P_INFORMATION17 => C1rec.Information17,
2442 P_INFORMATION18 => C1rec.Information18,
2443 P_INFORMATION19 => C1rec.Information19,
2444 P_INFORMATION20 => C1rec.Information20,
2445 P_INFORMATION21 => C1rec.Information21,
2446 P_INFORMATION22 => C1rec.Information22,
2447 P_INFORMATION23 => C1rec.Information23,
2448 P_INFORMATION24 => C1rec.Information24,
2449 P_INFORMATION25 => C1rec.Information25,
2450 P_INFORMATION26 => C1rec.Information26,
2451 P_INFORMATION27 => C1rec.Information27,
2452 P_INFORMATION28 => C1rec.Information28,
2453 P_INFORMATION29 => C1rec.Information29,
2454 P_INFORMATION30 => C1rec.Information30,
2455 P_EFFECTIVE_DATE => p_Effective_Date);
2456 l_Parent_hierarchy_Node_Id := Nvl(P_Parent_hierarchy_Id, 0);
2457
2458 Else
2459 Open C2(C1rec.Parent_hierarchy_node_Id);
2460 Fetch C2 into L_Node_Type, l_Entity_Id;
2461 Close C2;
2462 Open C3(l_Node_type, l_Entity_Id);
2463 Fetch C3 into l_Parent_Hierarchy_Node_id;
2464 Close C3;
2465 Per_Hierarchy_Nodes_Api.create_hierarchy_nodes
2466 (P_HIERARCHY_NODE_ID => l_Hierarchy_Node_id,
2467 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
2468 P_ENTITY_ID => C1rec.Entity_id,
2469 P_HIERARCHY_VERSION_ID => P_Hierarchy_version_id,
2470 P_NODE_TYPE => C1rec.Node_type,
2471 P_SEQ => Node_Sequence(P_Hierarchy_version_id,l_Parent_Hierarchy_Node_Id),
2472 P_PARENT_HIERARCHY_NODE_ID => l_Parent_hierarchy_Node_id,
2473 P_OBJECT_VERSION_NUMBER => l_Object_version_Number,
2474 P_REQUEST_ID => -999,
2475 P_ATTRIBUTE_CATEGORY => C1rec.Attribute_Category,
2476 P_ATTRIBUTE1 => C1rec.Attribute1,
2477 P_ATTRIBUTE2 => C1rec.Attribute2,
2478 P_ATTRIBUTE3 => C1rec.Attribute3,
2479 P_ATTRIBUTE4 => C1rec.Attribute4,
2480 P_ATTRIBUTE5 => C1rec.Attribute5,
2481 P_ATTRIBUTE6 => C1rec.Attribute6,
2482 P_ATTRIBUTE7 => C1rec.Attribute7,
2483 P_ATTRIBUTE8 => C1rec.Attribute8,
2484 P_ATTRIBUTE9 => C1rec.Attribute9,
2485 P_ATTRIBUTE10 => C1rec.Attribute10,
2486 P_ATTRIBUTE11 => C1rec.Attribute11,
2487 P_ATTRIBUTE12 => C1rec.Attribute12,
2488 P_ATTRIBUTE13 => C1rec.Attribute13,
2489 P_ATTRIBUTE14 => C1rec.Attribute14,
2490 P_ATTRIBUTE15 => C1rec.Attribute15,
2491 P_ATTRIBUTE16 => C1rec.Attribute16,
2492 P_ATTRIBUTE17 => C1rec.Attribute17,
2493 P_ATTRIBUTE18 => C1rec.Attribute18,
2494 P_ATTRIBUTE19 => C1rec.Attribute19,
2495 P_ATTRIBUTE20 => C1rec.Attribute20,
2496 P_ATTRIBUTE21 => C1rec.Attribute21,
2497 P_ATTRIBUTE22 => C1rec.Attribute22,
2498 P_ATTRIBUTE23 => C1rec.Attribute23,
2499 P_ATTRIBUTE24 => C1rec.Attribute24,
2500 P_ATTRIBUTE25 => C1rec.Attribute25,
2501 P_ATTRIBUTE26 => C1rec.Attribute26,
2502 P_ATTRIBUTE27 => C1rec.Attribute27,
2503 P_ATTRIBUTE28 => C1rec.Attribute28,
2504 P_ATTRIBUTE29 => C1rec.Attribute29,
2505 P_ATTRIBUTE30 => C1rec.Attribute30,
2506 P_INFORMATION_CATEGORY => C1rec.Information_Category,
2507 P_INFORMATION1 => C1rec.Information1,
2508 P_INFORMATION2 => C1rec.Information2,
2509 P_INFORMATION3 => C1rec.Information3,
2510 P_INFORMATION4 => C1rec.Information4,
2511 P_INFORMATION5 => C1rec.Information5,
2512 P_INFORMATION6 => C1rec.Information6,
2513 P_INFORMATION7 => C1rec.Information7,
2514 P_INFORMATION8 => C1rec.Information8,
2515 P_INFORMATION9 => C1rec.Information9,
2516 P_INFORMATION10 => C1rec.Information10,
2517 P_INFORMATION11 => C1rec.Information11,
2518 P_INFORMATION12 => C1rec.Information12,
2519 P_INFORMATION13 => C1rec.Information13,
2520 P_INFORMATION14 => C1rec.Information14,
2521 P_INFORMATION15 => C1rec.Information15,
2522 P_INFORMATION16 => C1rec.Information16,
2523 P_INFORMATION17 => C1rec.Information17,
2524 P_INFORMATION18 => C1rec.Information18,
2525 P_INFORMATION19 => C1rec.Information19,
2526 P_INFORMATION20 => C1rec.Information20,
2527 P_INFORMATION21 => C1rec.Information21,
2528 P_INFORMATION22 => C1rec.Information22,
2529 P_INFORMATION23 => C1rec.Information23,
2530 P_INFORMATION24 => C1rec.Information24,
2531 P_INFORMATION25 => C1rec.Information25,
2532 P_INFORMATION26 => C1rec.Information26,
2533 P_INFORMATION27 => C1rec.Information27,
2534 P_INFORMATION28 => C1rec.Information28,
2535 P_INFORMATION29 => C1rec.Information29,
2536 P_INFORMATION30 => C1rec.Information30,
2537 P_EFFECTIVE_DATE => p_Effective_Date);
2538 End If;
2539
2540 End Loop;
2541
2542 Update Per_Gen_Hierarchy_Nodes
2543 Set REQUEST_ID = 0
2544 Where REQUEST_ID = -999;
2545 End;
2546 --
2547 End;