[Home] [Help]
PACKAGE BODY: APPS.PER_GENERIC_HIERARCHY_PKG
Source
1 package body per_generic_hierarchy_pkg as
2 /* $Header: peghrval.pkb 115.7 2002/12/05 16:47:34 pkakar noship $ */
3 --
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Generic Hierarchy Package
13 Purpose
14 This package is used to perform operations for the generic hierarchy
15 form such as validating hierarchies and copying hierarchies.
16 History
17 Version Date Who What?
18 --------- --------- ---------- --------------------------------------------
19 115.0 25-Jan-01 gperry Created
20 115.1 30-Jan-01 gperry Changed min levels to 2 for VETS hierarchy.
21 115.2 02-Jan-01 gperry Changed level default value.
22 115.3 13-Feb-01 gperry Fixed copy_hierarchy procedure so that the
23 l_parent_hierarchy_node_id is set to null
24 before looping through the nodes for a
25 copy. This fixes the invalid parent node
26 problem.
27 115.4 25-May-01 vshukhat Changed c1 cursor for validate_number_of_levels
28 115.5 17-Feb-02 nsinghal Add DDF(Informations) Column
29 115.7 05-Dec-02 pkakar Added nocopy to parameters
30 */
31 --
32 g_package varchar2(30) := 'per_generic_hierarchy_pkg.';
33 --
34 -- Validate level node types Routine
35 --
36 procedure validate_level_node_type(p_hierarchy_node_id in number,
37 p_node_type in varchar2,
38 p_level_number in number,
39 p_iteration in number default 2) is
40 --
41 l_proc varchar2(80) := g_package||'validate_level_node_type';
42 l_type varchar2(30);
43 --
44 cursor c1 is
45 select hierarchy_node_id,
46 node_type
47 from per_gen_hierarchy_nodes
48 where parent_hierarchy_node_id = p_hierarchy_node_id;
49 --
50 cursor c2 is
51 select node_type
52 from per_gen_hierarchy_nodes
53 where hierarchy_node_id = p_hierarchy_node_id;
54 --
55 begin
56 --
57 if p_level_number = 1 then
58 --
59 -- We are looking at the parent node so we don't have to recurse through
60 -- the whole tree.
61 --
62 open c2;
63 --
64 fetch c2 into l_type;
65 if l_type <> p_node_type then
66 --
67 close c2;
68 fnd_message.set_name('PER','HR_289050_LEVEL_NODE_MISMATCH');
69 fnd_message.set_token('LEVEL',p_level_number);
70 fnd_message.set_token('TYPE',p_node_type);
71 fnd_message.raise_error;
72 --
73 end if;
74 --
75 close c2;
76 --
77 return;
78 --
79 end if;
80 --
81 hr_utility.set_location('Entering '||l_proc,10);
82 --
83 for l_count in c1 loop
84 --
85 exit when c1%notfound;
86 --
87 -- Recursively search down tree and keep count of levels
88 --
89 if p_iteration = p_level_number and
90 l_count.node_type <> p_node_type then
91 --
92 fnd_message.set_name('PER','HR_289050_LEVEL_NODE_MISMATCH');
93 fnd_message.set_token('LEVEL',p_level_number);
94 fnd_message.set_token('TYPE',p_node_type);
95 fnd_message.raise_error;
96 --
97 end if;
98 --
99 validate_level_node_type
100 (p_hierarchy_node_id => l_count.hierarchy_node_id,
101 p_node_type => p_node_type,
102 p_level_number => p_level_number,
103 p_iteration => p_iteration+1);
104 --
105 end loop;
106 --
107 hr_utility.set_location('Leaving '||l_proc,10);
108 --
109 end validate_level_node_type;
110 --
111 -- Get parent node for the hierarchy version routine
112 --
113 function get_parent_node(p_hierarchy_version_id in number) return number is
114 --
115 cursor c1 is
116 select hierarchy_node_id
117 from per_gen_hierarchy_nodes
118 where hierarchy_version_id = p_hierarchy_version_id
119 and parent_hierarchy_node_id is null;
120 --
121 l_hierarchy_node_id number;
122 l_proc varchar2(80) := g_package||'get_parent_node';
123 --
124 begin
125 --
126 hr_utility.set_location('Entering '||l_proc,10);
127 --
128 open c1;
129 --
130 fetch c1 into l_hierarchy_node_id;
131 --
132 close c1;
133 --
134 hr_utility.set_location('Leaving '||l_proc,10);
135 --
136 return l_hierarchy_node_id;
137 --
138 end get_parent_node;
139 --
140 -- Validate number of levels routine
141 --
142 procedure validate_number_of_levels
143 (p_hierarchy_version_id in number,
144 p_min_levels in number,
145 p_max_levels in number) is
146 --
147 l_proc varchar2(80) := g_package||'validate_number_of_levels';
148 l_count number;
149 --
150 -- Bug 1802107 VS 25-MAY-2001
151 --
152 cursor c1 is
153 select max(level)
154 from per_gen_hierarchy_nodes
155 start with parent_hierarchy_node_id is null
156 and hierarchy_version_id = p_hierarchy_version_id
157 connect by prior hierarchy_node_id = parent_hierarchy_node_id
158 and hierarchy_version_id = p_hierarchy_version_id;
159 --
160 /*
161 cursor c1 is
162 select count(distinct nvl(parent_hierarchy_node_id,-1))
163 from per_gen_hierarchy_nodes
164 where hierarchy_version_id = p_hierarchy_version_id;
165 */
166 --
167 begin
168 --
169 hr_utility.set_location('Entering '||l_proc,10);
170 --
171 open c1;
172 --
173 fetch c1 into l_count;
174 --
175 close c1;
176 --
177 if l_count < p_min_levels or
178 l_count > p_max_levels then
179 --
180 fnd_message.set_name('PER','HR_289051_LEVEL_ERROR');
181 fnd_message.set_token('NUM_LEVELS',l_count);
182 fnd_message.set_token('MIN',p_min_levels);
183 fnd_message.set_token('MAX',p_max_levels);
184 fnd_message.raise_error;
185 --
186 end if;
187 --
188 hr_utility.set_location('Leaving '||l_proc,10);
189 --
190 end validate_number_of_levels;
191 --
192 -- Reporting checks for the VETS reports
193 --
194 procedure vets_reporting_checks(p_hierarchy_version_id in number) is
195 --
196 l_proc varchar2(80) := g_package||'vets_reporting_checks';
197 l_hierarchy_node_id number;
198 --
199 begin
200 --
201 hr_utility.set_location('Entering '||l_proc,10);
202 --
203 -- Vets Validation Checks include
204 --
205 -- 1) Must be Three node levels maximum
206 -- 2) Parent level must be Org
207 -- 3) Second , Third level must be Locations
208 --
209 -- Get parent node
210 --
211 l_hierarchy_node_id := get_parent_node
212 (p_hierarchy_version_id => p_hierarchy_version_id);
213 --
214 validate_number_of_levels(p_hierarchy_version_id => p_hierarchy_version_id,
215 p_min_levels => 2,
216 p_max_levels => 3);
217 --
218 validate_level_node_type(p_hierarchy_node_id => l_hierarchy_node_id,
219 p_node_type => 'PAR',
220 p_level_number => 1);
221 --
222 validate_level_node_type(p_hierarchy_node_id => l_hierarchy_node_id,
223 p_node_type => 'EST',
224 p_level_number => 2);
225 --
226 validate_level_node_type(p_hierarchy_node_id => l_hierarchy_node_id,
227 p_node_type => 'LOC',
228 p_level_number => 3);
229 --
230 hr_utility.set_location('Leaving '||l_proc,10);
231 --
232 end vets_reporting_checks;
233 --
234 -- Main routine to validate a hierarchy
235 --
236 procedure validate_hierarchy(p_hierarchy_version_id in number) is
237 --
238 l_proc varchar2(80) := g_package||'validate_hierarchy';
239 l_type varchar2(30);
240 --
241 cursor c1 is
242 select a.type
243 from per_gen_hierarchy a,
244 per_gen_hierarchy_versions b
245 where a.hierarchy_id = b.hierarchy_id
246 and b.hierarchy_version_id = p_hierarchy_version_id;
247 --
248 begin
249 --
250 hr_utility.set_location('Entering '||l_proc,10);
251 --
252 -- Get Hierarchy Type
253 --
254 open c1;
255 --
256 fetch c1 into l_type;
257 --
258 if c1%notfound then
259 --
260 close c1;
261 fnd_message.set_name('PER','HR_289052_HIER_NOT_FOUND');
262 fnd_message.raise_error;
263 --
264 end if;
265 --
266 close c1;
267 --
268 if l_type = 'FEDREP' then
269 --
270 vets_reporting_checks(p_hierarchy_version_id => p_hierarchy_version_id);
271 --
272 else
273 --
274 fnd_message.set_name('PER','HR_289053_HIER_TYPE_UNKNOWN');
275 fnd_message.raise_error;
276 --
277 end if;
278 --
279 hr_utility.set_location('Leaving '||l_proc,10);
280 --
281 end validate_hierarchy;
282 --
283 procedure copy_hierarchy(p_hierarchy_id in number,
284 p_name in varchar2,
285 p_effective_date in date,
286 p_out_hierarchy_id out nocopy number) is
287 --
288 l_proc varchar2(80) := g_package||'copy_hierarchy';
289 l_object_version_number number;
290 l_hierarchy_id number;
291 l_hierarchy_version_id number;
292 l_hierarchy_node_id number;
293 l_parent_hierarchy_node_id number := null;
294 --
295 cursor c1 is
296 select *
297 from per_gen_hierarchy
298 where hierarchy_id = p_hierarchy_id;
299 --
300 l_c1 c1%rowtype;
301 --
302 cursor c2 is
303 select *
304 from per_gen_hierarchy_versions
305 where hierarchy_id = p_hierarchy_id;
306 --
307 l_c2 c2%rowtype;
308 --
309 cursor c3(p_hierarchy_version_id number) is
310 select *
311 from per_gen_hierarchy_nodes a
312 where hierarchy_version_id = p_hierarchy_version_id
313 order by decode(parent_hierarchy_node_id,null,1,2);
314 --
315 l_c3 c3%rowtype;
316 --
317 cursor c4 is
318 select d.hierarchy_node_id parent_hierarchy_node_id,
319 a.hierarchy_node_id
320 from per_gen_hierarchy_nodes a,
321 per_gen_hierarchy_nodes b,
322 per_gen_hierarchy_nodes c,
323 per_gen_hierarchy_nodes d
324 where a.hierarchy_version_id = l_hierarchy_version_id
325 and a.entity_id = b.entity_id
326 and a.node_type = b.node_type
327 and b.hierarchy_version_id = l_c2.hierarchy_version_id
328 and a.parent_hierarchy_node_id is not null
329 and b.parent_hierarchy_node_id = c.hierarchy_node_id
330 and c.entity_id = d.entity_id
331 and c.node_type = d.node_type
332 and c.hierarchy_version_id = l_c2.hierarchy_version_id
333 and d.hierarchy_version_id = l_hierarchy_version_id;
334 --
335 l_c4 c4%rowtype;
336 --
337 begin
338 --
339 hr_utility.set_location('Entering '||l_proc,10);
340 --
341 -- Basically to copy a hierarchy do the following
342 --
343 -- 1) Attempt to create the hierarchy.
344 -- 2) loop through all versions and create them.
345 -- 3) loop through all nodes and create them
346 --
347 open c1;
348 --
349 fetch c1 into l_c1;
350 --
351 if c1%notfound then
352 --
353 fnd_message.set_name('PER','HR_289054_NO_HIERARCHY');
354 fnd_message.raise_error;
355 --
356 end if;
357 --
358 close c1;
359 --
360 per_hierarchy_api.create_hierarchy
361 (p_hierarchy_id => l_hierarchy_id
362 ,p_business_group_id => l_c1.business_group_id
363 ,p_name => p_name
364 ,p_type => l_c1.type
365 ,p_request_id => l_c1.request_id
366 ,p_program_application_id => l_c1.request_id
367 ,p_program_id => l_c1.request_id
368 ,p_program_update_date => l_c1.program_update_date
369 ,p_object_version_number => l_object_version_number
370 ,p_attribute_category => l_c1.attribute_category
371 ,p_attribute1 => l_c1.attribute1
372 ,p_attribute2 => l_c1.attribute2
373 ,p_attribute3 => l_c1.attribute3
374 ,p_attribute4 => l_c1.attribute4
375 ,p_attribute5 => l_c1.attribute5
376 ,p_attribute6 => l_c1.attribute6
377 ,p_attribute7 => l_c1.attribute7
378 ,p_attribute8 => l_c1.attribute8
379 ,p_attribute9 => l_c1.attribute9
380 ,p_attribute10 => l_c1.attribute10
381 ,p_attribute11 => l_c1.attribute11
382 ,p_attribute12 => l_c1.attribute12
383 ,p_attribute13 => l_c1.attribute13
384 ,p_attribute14 => l_c1.attribute14
385 ,p_attribute15 => l_c1.attribute15
386 ,p_attribute16 => l_c1.attribute16
387 ,p_attribute17 => l_c1.attribute17
388 ,p_attribute18 => l_c1.attribute18
389 ,p_attribute19 => l_c1.attribute19
390 ,p_attribute20 => l_c1.attribute20
391 ,p_attribute21 => l_c1.attribute21
392 ,p_attribute22 => l_c1.attribute22
393 ,p_attribute23 => l_c1.attribute23
394 ,p_attribute24 => l_c1.attribute24
395 ,p_attribute25 => l_c1.attribute25
396 ,p_attribute26 => l_c1.attribute26
397 ,p_attribute27 => l_c1.attribute27
398 ,p_attribute28 => l_c1.attribute28
399 ,p_attribute29 => l_c1.attribute29
400 ,p_attribute30 => l_c1.attribute30
401 ,p_information_category => l_c1.information_category
402 ,p_information1 => l_c1.information1
403 ,p_information2 => l_c1.information2
407 ,p_information6 => l_c1.information6
404 ,p_information3 => l_c1.information3
405 ,p_information4 => l_c1.information4
406 ,p_information5 => l_c1.information5
408 ,p_information7 => l_c1.information7
409 ,p_information8 => l_c1.information8
410 ,p_information9 => l_c1.information9
411 ,p_information10 => l_c1.information10
412 ,p_information11 => l_c1.information11
413 ,p_information12 => l_c1.information12
414 ,p_information13 => l_c1.information13
415 ,p_information14 => l_c1.information14
416 ,p_information15 => l_c1.information15
417 ,p_information16 => l_c1.information16
418 ,p_information17 => l_c1.information17
419 ,p_information18 => l_c1.information18
420 ,p_information19 => l_c1.information19
421 ,p_information20 => l_c1.information20
422 ,p_information21 => l_c1.information21
423 ,p_information22 => l_c1.information22
424 ,p_information23 => l_c1.information23
425 ,p_information24 => l_c1.information24
426 ,p_information25 => l_c1.information25
427 ,p_information26 => l_c1.information26
428 ,p_information27 => l_c1.information27
429 ,p_information28 => l_c1.information28
430 ,p_information29 => l_c1.information29
431 ,p_information30 => l_c1.information30
432 ,p_effective_date => p_effective_date);
433 --
434 open c2;
435 --
436 loop
437 --
438 fetch c2 into l_c2;
439 exit when c2%notfound;
440 --
441 per_hierarchy_versions_api.create_hierarchy_versions
442 (p_hierarchy_version_id => l_hierarchy_version_id
443 ,p_business_group_id => l_c2.business_group_id
444 ,p_version_number => l_c2.version_number
445 ,p_hierarchy_id => l_hierarchy_id
446 ,p_date_from => l_c2.date_from
447 ,p_date_to => l_c2.date_to
448 ,p_status => l_c2.status
449 ,p_validate_flag => l_c2.validate_flag
450 ,p_request_id => l_c2.request_id
451 ,p_program_application_id => l_c2.program_application_id
452 ,p_program_id => l_c2.program_id
453 ,p_program_update_date => l_c2.program_update_date
454 ,p_object_version_number => l_object_version_number
455 ,p_attribute_category => l_c2.attribute_category
456 ,p_attribute1 => l_c2.attribute1
457 ,p_attribute2 => l_c2.attribute2
458 ,p_attribute3 => l_c2.attribute3
459 ,p_attribute4 => l_c2.attribute4
460 ,p_attribute5 => l_c2.attribute5
461 ,p_attribute6 => l_c2.attribute6
462 ,p_attribute7 => l_c2.attribute7
463 ,p_attribute8 => l_c2.attribute8
464 ,p_attribute9 => l_c2.attribute9
465 ,p_attribute10 => l_c2.attribute10
466 ,p_attribute11 => l_c2.attribute11
467 ,p_attribute12 => l_c2.attribute12
468 ,p_attribute13 => l_c2.attribute13
469 ,p_attribute14 => l_c2.attribute14
470 ,p_attribute15 => l_c2.attribute15
471 ,p_attribute16 => l_c2.attribute16
472 ,p_attribute17 => l_c2.attribute17
473 ,p_attribute18 => l_c2.attribute18
474 ,p_attribute19 => l_c2.attribute19
475 ,p_attribute20 => l_c2.attribute20
476 ,p_attribute21 => l_c2.attribute21
477 ,p_attribute22 => l_c2.attribute22
478 ,p_attribute23 => l_c2.attribute23
479 ,p_attribute24 => l_c2.attribute24
480 ,p_attribute25 => l_c2.attribute25
481 ,p_attribute26 => l_c2.attribute26
482 ,p_attribute27 => l_c2.attribute27
483 ,p_attribute28 => l_c2.attribute28
484 ,p_attribute29 => l_c2.attribute29
485 ,p_attribute30 => l_c2.attribute30
486 ,p_information_category => l_c2.information_category
487 ,p_information1 => l_c2.information1
488 ,p_information2 => l_c2.information2
489 ,p_information3 => l_c2.information3
490 ,p_information4 => l_c2.information4
491 ,p_information5 => l_c2.information5
492 ,p_information6 => l_c2.information6
493 ,p_information7 => l_c2.information7
494 ,p_information8 => l_c2.information8
495 ,p_information9 => l_c2.information9
496 ,p_information10 => l_c2.information10
497 ,p_information11 => l_c2.information11
498 ,p_information12 => l_c2.information12
502 ,p_information16 => l_c2.information16
499 ,p_information13 => l_c2.information13
500 ,p_information14 => l_c2.information14
501 ,p_information15 => l_c2.information15
503 ,p_information17 => l_c2.information17
504 ,p_information18 => l_c2.information18
505 ,p_information19 => l_c2.information19
506 ,p_information20 => l_c2.information20
507 ,p_information21 => l_c2.information21
508 ,p_information22 => l_c2.information22
509 ,p_information23 => l_c2.information23
510 ,p_information24 => l_c2.information24
511 ,p_information25 => l_c2.information25
512 ,p_information26 => l_c2.information26
513 ,p_information27 => l_c2.information27
514 ,p_information28 => l_c2.information28
515 ,p_information29 => l_c2.information29
516 ,p_information30 => l_c2.information30
517 ,p_effective_date => p_effective_date);
518 --
519 l_parent_hierarchy_node_id := null;
520 --
521 open c3(l_c2.hierarchy_version_id);
522 --
523 loop
524 --
525 fetch c3 into l_c3;
526 exit when c3%notfound;
527 --
528 per_hierarchy_nodes_api.create_hierarchy_nodes
529 (p_hierarchy_node_id => l_hierarchy_node_id
530 ,p_business_group_id => l_c3.business_group_id
531 ,p_entity_id => l_c3.entity_id
532 ,p_hierarchy_version_id => l_hierarchy_version_id
533 ,p_node_type => l_c3.node_type
534 ,p_seq => l_c3.seq
535 ,p_parent_hierarchy_node_id => l_parent_hierarchy_node_id
536 ,p_request_id => l_c3.request_id
537 ,p_program_application_id => l_c3.program_application_id
538 ,p_program_id => l_c3.program_id
539 ,p_program_update_date => l_c3.program_update_date
540 ,p_object_version_number => l_object_version_number
541 ,p_attribute_category => l_c3.attribute_category
542 ,p_attribute1 => l_c3.attribute1
543 ,p_attribute2 => l_c3.attribute2
544 ,p_attribute3 => l_c3.attribute3
545 ,p_attribute4 => l_c3.attribute4
546 ,p_attribute5 => l_c3.attribute5
547 ,p_attribute6 => l_c3.attribute6
548 ,p_attribute7 => l_c3.attribute7
549 ,p_attribute8 => l_c3.attribute8
550 ,p_attribute9 => l_c3.attribute9
551 ,p_attribute10 => l_c3.attribute10
552 ,p_attribute11 => l_c3.attribute11
553 ,p_attribute12 => l_c3.attribute12
554 ,p_attribute13 => l_c3.attribute13
555 ,p_attribute14 => l_c3.attribute14
556 ,p_attribute15 => l_c3.attribute15
557 ,p_attribute16 => l_c3.attribute16
558 ,p_attribute17 => l_c3.attribute17
559 ,p_attribute18 => l_c3.attribute18
560 ,p_attribute19 => l_c3.attribute19
561 ,p_attribute20 => l_c3.attribute20
562 ,p_attribute21 => l_c3.attribute21
563 ,p_attribute22 => l_c3.attribute22
564 ,p_attribute23 => l_c3.attribute23
565 ,p_attribute24 => l_c3.attribute24
566 ,p_attribute25 => l_c3.attribute25
567 ,p_attribute26 => l_c3.attribute26
568 ,p_attribute27 => l_c3.attribute27
569 ,p_attribute28 => l_c3.attribute28
570 ,p_attribute29 => l_c3.attribute29
571 ,p_attribute30 => l_c3.attribute30
572 ,p_information_category => l_c3.information_category
573 ,p_information1 => l_c3.information1
574 ,p_information2 => l_c3.information2
575 ,p_information3 => l_c3.information3
576 ,p_information4 => l_c3.information4
577 ,p_information5 => l_c3.information5
578 ,p_information6 => l_c3.information6
579 ,p_information7 => l_c3.information7
580 ,p_information8 => l_c3.information8
581 ,p_information9 => l_c3.information9
582 ,p_information10 => l_c3.information10
583 ,p_information11 => l_c3.information11
584 ,p_information12 => l_c3.information12
585 ,p_information13 => l_c3.information13
589 ,p_information17 => l_c3.information17
586 ,p_information14 => l_c3.information14
587 ,p_information15 => l_c3.information15
588 ,p_information16 => l_c3.information16
590 ,p_information18 => l_c3.information18
591 ,p_information19 => l_c3.information19
592 ,p_information20 => l_c3.information20
593 ,p_information21 => l_c3.information21
594 ,p_information22 => l_c3.information22
595 ,p_information23 => l_c3.information23
596 ,p_information24 => l_c3.information24
597 ,p_information25 => l_c3.information25
598 ,p_information26 => l_c3.information26
599 ,p_information27 => l_c3.information27
600 ,p_information28 => l_c3.information28
601 ,p_information29 => l_c3.information29
602 ,p_information30 => l_c3.information30
603 ,p_effective_date => p_effective_date);
604 --
605 l_parent_hierarchy_node_id := l_hierarchy_node_id;
606 --
607 end loop;
608 --
609 -- Now update each of the rows with its correct parent based on
610 -- the parent definitions of the original version.
611 -- Remember within a hierarchy the node type and entity id must be
612 -- unique.
613 --
614 open c4;
615 --
616 loop
617 --
618 fetch c4 into l_c4;
619 exit when c4%notfound;
620 --
621 update per_gen_hierarchy_nodes
622 set parent_hierarchy_node_id = l_c4.parent_hierarchy_node_id
623 where hierarchy_node_id = l_c4.hierarchy_node_id;
624 --
625 end loop;
626 --
627 close c4;
628 --
629 close c3;
630 --
631 end loop;
632 --
633 close c2;
634 --
635 hr_utility.set_location('Leaving '||l_proc,10);
636 --
637 p_out_hierarchy_id := l_hierarchy_id;
638 --
639 commit;
640 --
641 end copy_hierarchy;
642 --
643 procedure copy_hierarchy_version(p_hierarchy_version_id in number,
644 p_new_version_number in number,
645 p_date_from in date,
646 p_date_to in date,
647 p_effective_date in date,
648 p_out_hierarchy_version_id out nocopy number) is
649 --
650 l_proc varchar2(80) := g_package||'copy_hierarchy_version';
651 l_hierarchy_version_id number;
652 l_hierarchy_node_id number;
653 l_parent_hierarchy_node_id number;
654 l_object_version_number number;
655 --
656 cursor c1 is
657 select *
658 from per_gen_hierarchy_versions
659 where hierarchy_version_id = p_hierarchy_version_id;
660 --
661 l_c1 c1%rowtype;
662 --
663 cursor c2 is
664 select *
665 from per_gen_hierarchy_nodes a
666 where hierarchy_version_id = p_hierarchy_version_id
667 order by decode(parent_hierarchy_node_id,null,1,2);
668 --
669 l_c2 c2%rowtype;
670 --
671 cursor c3 is
672 select d.hierarchy_node_id parent_hierarchy_node_id,
673 a.hierarchy_node_id
674 from per_gen_hierarchy_nodes a,
675 per_gen_hierarchy_nodes b,
676 per_gen_hierarchy_nodes c,
677 per_gen_hierarchy_nodes d
678 where a.hierarchy_version_id = l_hierarchy_version_id
679 and a.entity_id = b.entity_id
680 and a.node_type = b.node_type
681 and b.hierarchy_version_id = p_hierarchy_version_id
682 and a.parent_hierarchy_node_id is not null
683 and b.parent_hierarchy_node_id = c.hierarchy_node_id
684 and c.entity_id = d.entity_id
685 and c.node_type = d.node_type
686 and c.hierarchy_version_id = p_hierarchy_version_id
687 and d.hierarchy_version_id = l_hierarchy_version_id;
688 --
689 l_c3 c3%rowtype;
690 --
691 begin
692 --
693 hr_utility.set_location('Entering '||l_proc,10);
694 --
695 -- 1) Create hierarchy version
696 -- 2) Create all nodes for hierarchy version
697 -- 3) Link the nodes to the correct parents
698 --
699 open c1;
700 --
701 fetch c1 into l_c1;
702 if c1%notfound then
703 --
704 fnd_message.set_name('PER','HR_289055_NO_VERSION');
705 fnd_message.raise_error;
706 --
707 end if;
708 --
709 close c1;
710 --
714 ,p_version_number => p_new_version_number
711 per_hierarchy_versions_api.create_hierarchy_versions
712 (p_hierarchy_version_id => l_hierarchy_version_id
713 ,p_business_group_id => l_c1.business_group_id
715 ,p_hierarchy_id => l_c1.hierarchy_id
716 ,p_date_from => p_date_from
717 ,p_date_to => p_date_to
718 ,p_status => l_c1.status
719 ,p_validate_flag => l_c1.validate_flag
720 ,p_request_id => l_c1.request_id
721 ,p_program_application_id => l_c1.program_application_id
722 ,p_program_id => l_c1.program_id
723 ,p_program_update_date => l_c1.program_update_date
724 ,p_object_version_number => l_object_version_number
725 ,p_attribute_category => l_c1.attribute_category
726 ,p_attribute1 => l_c1.attribute1
727 ,p_attribute2 => l_c1.attribute2
728 ,p_attribute3 => l_c1.attribute3
729 ,p_attribute4 => l_c1.attribute4
730 ,p_attribute5 => l_c1.attribute5
731 ,p_attribute6 => l_c1.attribute6
732 ,p_attribute7 => l_c1.attribute7
733 ,p_attribute8 => l_c1.attribute8
734 ,p_attribute9 => l_c1.attribute9
735 ,p_attribute10 => l_c1.attribute10
736 ,p_attribute11 => l_c1.attribute11
737 ,p_attribute12 => l_c1.attribute12
738 ,p_attribute13 => l_c1.attribute13
739 ,p_attribute14 => l_c1.attribute14
740 ,p_attribute15 => l_c1.attribute15
741 ,p_attribute16 => l_c1.attribute16
742 ,p_attribute17 => l_c1.attribute17
743 ,p_attribute18 => l_c1.attribute18
744 ,p_attribute19 => l_c1.attribute19
745 ,p_attribute20 => l_c1.attribute20
746 ,p_attribute21 => l_c1.attribute21
747 ,p_attribute22 => l_c1.attribute22
748 ,p_attribute23 => l_c1.attribute23
749 ,p_attribute24 => l_c1.attribute24
750 ,p_attribute25 => l_c1.attribute25
751 ,p_attribute26 => l_c1.attribute26
752 ,p_attribute27 => l_c1.attribute27
753 ,p_attribute28 => l_c1.attribute28
754 ,p_attribute29 => l_c1.attribute29
755 ,p_attribute30 => l_c1.attribute30
756 ,p_information_category => l_c1.information_category
757 ,p_information1 => l_c1.information1
758 ,p_information2 => l_c1.information2
759 ,p_information3 => l_c1.information3
760 ,p_information4 => l_c1.information4
761 ,p_information5 => l_c1.information5
762 ,p_information6 => l_c1.information6
763 ,p_information7 => l_c1.information7
764 ,p_information8 => l_c1.information8
765 ,p_information9 => l_c1.information9
766 ,p_information10 => l_c1.information10
767 ,p_information11 => l_c1.information11
768 ,p_information12 => l_c1.information12
769 ,p_information13 => l_c1.information13
770 ,p_information14 => l_c1.information14
771 ,p_information15 => l_c1.information15
772 ,p_information16 => l_c1.information16
773 ,p_information17 => l_c1.information17
774 ,p_information18 => l_c1.information18
775 ,p_information19 => l_c1.information19
776 ,p_information20 => l_c1.information20
777 ,p_information21 => l_c1.information21
778 ,p_information22 => l_c1.information22
779 ,p_information23 => l_c1.information23
780 ,p_information24 => l_c1.information24
781 ,p_information25 => l_c1.information25
782 ,p_information26 => l_c1.information26
783 ,p_information27 => l_c1.information27
784 ,p_information28 => l_c1.information28
785 ,p_information29 => l_c1.information29
786 ,p_information30 => l_c1.information30
787 ,p_effective_date => p_effective_date);
788 --
789 --
790 l_parent_hierarchy_node_id := null;
791 --
792 open c2;
793 --
794 loop
795 --
796 fetch c2 into l_c2;
797 exit when c2%notfound;
798 --
799 per_hierarchy_nodes_api.create_hierarchy_nodes
800 (p_hierarchy_node_id => l_hierarchy_node_id
801 ,p_business_group_id => l_c2.business_group_id
802 ,p_entity_id => l_c2.entity_id
803 ,p_hierarchy_version_id => l_hierarchy_version_id
804 ,p_node_type => l_c2.node_type
805 ,p_seq => l_c2.seq
806 ,p_parent_hierarchy_node_id => l_parent_hierarchy_node_id
807 ,p_request_id => l_c2.request_id
808 ,p_program_application_id => l_c2.program_application_id
809 ,p_program_id => l_c2.program_id
810 ,p_program_update_date => l_c2.program_update_date
814 ,p_attribute2 => l_c2.attribute2
811 ,p_object_version_number => l_object_version_number
812 ,p_attribute_category => l_c2.attribute_category
813 ,p_attribute1 => l_c2.attribute1
815 ,p_attribute3 => l_c2.attribute3
816 ,p_attribute4 => l_c2.attribute4
817 ,p_attribute5 => l_c2.attribute5
818 ,p_attribute6 => l_c2.attribute6
819 ,p_attribute7 => l_c2.attribute7
820 ,p_attribute8 => l_c2.attribute8
821 ,p_attribute9 => l_c2.attribute9
822 ,p_attribute10 => l_c2.attribute10
823 ,p_attribute11 => l_c2.attribute11
824 ,p_attribute12 => l_c2.attribute12
825 ,p_attribute13 => l_c2.attribute13
826 ,p_attribute14 => l_c2.attribute14
827 ,p_attribute15 => l_c2.attribute15
828 ,p_attribute16 => l_c2.attribute16
829 ,p_attribute17 => l_c2.attribute17
830 ,p_attribute18 => l_c2.attribute18
831 ,p_attribute19 => l_c2.attribute19
832 ,p_attribute20 => l_c2.attribute20
833 ,p_attribute21 => l_c2.attribute21
834 ,p_attribute22 => l_c2.attribute22
835 ,p_attribute23 => l_c2.attribute23
836 ,p_attribute24 => l_c2.attribute24
837 ,p_attribute25 => l_c2.attribute25
838 ,p_attribute26 => l_c2.attribute26
839 ,p_attribute27 => l_c2.attribute27
840 ,p_attribute28 => l_c2.attribute28
841 ,p_attribute29 => l_c2.attribute29
842 ,p_attribute30 => l_c2.attribute30
843 ,p_information_category => l_c2.information_category
844 ,p_information1 => l_c2.information1
845 ,p_information2 => l_c2.information2
846 ,p_information3 => l_c2.information3
847 ,p_information4 => l_c2.information4
848 ,p_information5 => l_c2.information5
849 ,p_information6 => l_c2.information6
850 ,p_information7 => l_c2.information7
851 ,p_information8 => l_c2.information8
852 ,p_information9 => l_c2.information9
853 ,p_information10 => l_c2.information10
854 ,p_information11 => l_c2.information11
855 ,p_information12 => l_c2.information12
856 ,p_information13 => l_c2.information13
857 ,p_information14 => l_c2.information14
858 ,p_information15 => l_c2.information15
859 ,p_information16 => l_c2.information16
860 ,p_information17 => l_c2.information17
861 ,p_information18 => l_c2.information18
862 ,p_information19 => l_c2.information19
863 ,p_information20 => l_c2.information20
864 ,p_information21 => l_c2.information21
865 ,p_information22 => l_c2.information22
866 ,p_information23 => l_c2.information23
867 ,p_information24 => l_c2.information24
868 ,p_information25 => l_c2.information25
869 ,p_information26 => l_c2.information26
870 ,p_information27 => l_c2.information27
871 ,p_information28 => l_c2.information28
872 ,p_information29 => l_c2.information29
873 ,p_information30 => l_c2.information30
874 ,p_effective_date => p_effective_date);
875 --
876 l_parent_hierarchy_node_id := l_hierarchy_node_id;
877 --
878 end loop;
879 --
880 close c2;
881 --
882 -- Now update each of the rows with its correct parent based on
883 -- the parent definitions of the original version.
884 -- Remember within a hierarchy the node type and entity id must be
885 -- unique.
886 --
887 open c3;
888 --
889 loop
890 --
891 fetch c3 into l_c3;
892 exit when c3%notfound;
893 --
894 update per_gen_hierarchy_nodes
895 set parent_hierarchy_node_id = l_c3.parent_hierarchy_node_id
896 where hierarchy_node_id = l_c3.hierarchy_node_id;
897 --
898 end loop;
899 --
900 close c3;
901 --
902 p_out_hierarchy_version_id := l_hierarchy_version_id;
903 --
904 hr_utility.set_location('Leaving '||l_proc,10);
905 --
906 end copy_hierarchy_version;
907 --
908 end per_generic_hierarchy_pkg;