[Home] [Help]
PACKAGE BODY: APPS.HR_CALENDAR_NODE_TYPE_API
Source
1 Package Body HR_CALENDAR_NODE_TYPE_API as
2 /* $Header: pepgtapi.pkb 115.4 2003/05/21 15:15:12 cxsimpso noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'HR_CALENDAR_NODE_TYPE_API';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |---------------------------< create_node_type >---------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_node_type
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_hierarchy_type in varchar2
16 ,p_child_node_name in varchar2
17 ,p_child_value_set in varchar2
18 ,p_child_node_type in varchar2 default null
19 ,p_parent_node_type in varchar2 default null
20 ,p_description in varchar2 default null
21 ,p_hier_node_type_id out nocopy number
22 ,p_object_version_number out nocopy number
23 ) IS
24 --
25 -- Declare cursors and local variables
26 --
27 CURSOR csr_sequence IS
28 select per_gen_hier_node_types_s.nextval
29 from sys.dual;
30
31 --
32 -- Check if the lookup exists already
33 -- for the current language
34 --
35 CURSOR csr_lookup_unique IS
36 select 'x' from HR_LOOKUPS
37 where lookup_type = 'HIERARCHY_NODE_TYPE'
38 and lookup_code = p_child_node_type;
39
40 l_proc varchar2(80) := g_package||'create_node_type';
41 l_hier_node_type_id per_gen_hier_node_types.hier_node_type_id%TYPE;
42 l_object_version_number per_gen_hier_node_types.object_version_number%TYPE;
43 l_rowid varchar2(255) := null;
44 l_dummy varchar2(1);
45 l_lookup_code varchar2(30);
46 l_effective_date date;
47 --
48 begin
49 hr_utility.set_location('Entering:'|| l_proc, 10);
50 --
51 -- Issue a savepoint
52 --
53 savepoint create_node_type;
54 --
55 -- Truncate the time portion from all IN date parameters
56 --
57 l_effective_date := trunc(p_effective_date);
58
59 -- check that node name has been supplied (mandatory)
60 if p_child_node_name is null then
61 fnd_message.set_name('PER','HR_289891_PGT_NAME_NULL');
62 fnd_message.raise_error;
63 end if;
64
65 -- First generate a lookup_code from sequence, if none supplied
66 if p_child_node_type is null then
67 open csr_sequence;
68 fetch csr_sequence into l_lookup_code;
69 close csr_sequence;
70 else
71 -- code has been supplied so lets check its unique
72 -- within the lookup type before proceeding...
73 open csr_lookup_unique;
74 fetch csr_lookup_unique into l_dummy;
75 if csr_lookup_unique%found then
76 close csr_lookup_unique;
77 -- raise error as the supplied lookup_code already exists
78 -- for the HIERARCHY_NODE_TYPE lookup
79 fnd_message.set_name('PER','HR_289892_PGT_LOOKUP_EXISTS');
80 fnd_message.set_token('NODE',p_child_node_type);
81 fnd_message.raise_error;
82 else
83 close csr_lookup_unique;
84 l_lookup_code := p_child_node_type;
85 end if;
86 end if;
87
88 -- Now we attempt to create an fnd_lookup_values record for the node using the
89 -- (supplied or generated) code and user supplied values for p_child_node_name (meaning)
90 -- and p_description (description)
91
92 BEGIN
93
94 fnd_lookup_values_pkg.INSERT_ROW(X_ROWID => l_rowid,
95 X_SECURITY_GROUP_ID => 0,
96 X_LOOKUP_TYPE => 'HIERARCHY_NODE_TYPE',
97 X_VIEW_APPLICATION_ID => 3,
98 X_LOOKUP_CODE => l_lookup_code,
99 X_TAG => null,
100 X_ATTRIBUTE_CATEGORY => null,
101 X_ATTRIBUTE1 => null,
102 X_ATTRIBUTE2 => null,
103 X_ATTRIBUTE3 => null,
104 X_ATTRIBUTE4 => null,
105 X_ENABLED_FLAG => 'Y',
106 X_START_DATE_ACTIVE => null,
107 X_END_DATE_ACTIVE => null,
108 X_TERRITORY_CODE => null,
109 X_ATTRIBUTE5 => null,
110 X_ATTRIBUTE6 => null,
111 X_ATTRIBUTE7 => null,
112 X_ATTRIBUTE8 => null,
113 X_ATTRIBUTE9 => null,
114 X_ATTRIBUTE10 => null,
115 X_ATTRIBUTE11 => null,
116 X_ATTRIBUTE12 => null,
117 X_ATTRIBUTE13 => null,
118 X_ATTRIBUTE14 => null,
119 X_ATTRIBUTE15 => null,
120 X_MEANING => p_child_node_name,
121 X_DESCRIPTION => p_description,
122 X_CREATION_DATE => SYSDATE ,
123 X_CREATED_BY => fnd_global.user_id,
124 X_LAST_UPDATED_BY => fnd_global.user_id,
125 X_LAST_UPDATE_DATE => SYSDATE,
126 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
127
128
129 EXCEPTION
130 WHEN OTHERS THEN
131 fnd_message.set_name('PER','HR_289893_PGT_LOOKUP_INS_FAIL');
132 fnd_message.raise_error;
133 END;
134
135 --
136 -- Call Before Process User Hook
137 --
138 begin
139 HR_CALENDAR_NODE_TYPE_BK1.create_node_type_b
140 (p_effective_date => l_effective_date
141 ,p_child_node_type => p_child_node_type
142 ,p_child_node_name => p_child_node_name
143 ,p_hierarchy_type => p_hierarchy_type
144 ,p_child_value_set => p_child_value_set
145 ,p_parent_node_type => p_parent_node_type
146 ,p_identifier_key => NULL
147 ,p_description => p_description);
148
149 exception
150 when hr_api.cannot_find_prog_unit then
151 hr_api.cannot_find_prog_unit_error
152 (p_module_name => 'create_node_type_b'
153 ,p_hook_type => 'BP'
154 );
155 end;
156 --
157 -- Validation in addition to Row Handlers
158 --
159 --
160 -- Process Logic
161 --
162 per_pgt_ins.ins
163 (p_effective_date => l_effective_date
164 ,p_child_node_type => p_child_node_type
165 ,p_child_value_set => p_child_value_set
166 ,p_hierarchy_type => p_hierarchy_type
167 ,p_business_group_id => NULL
168 ,p_parent_node_type => p_parent_node_type
169 ,p_identifier_key => NULL
170 ,p_hier_node_type_id => l_hier_node_type_id
171 ,p_object_version_number => l_object_version_number);
172 --
173 -- Call After Process User Hook
174 --
175 begin
176 HR_CALENDAR_NODE_TYPE_BK1.create_node_type_a
177 (p_effective_date => l_effective_date
178 ,p_child_node_type => p_child_node_type
179 ,p_child_node_name => p_child_node_name
180 ,p_hierarchy_type => p_hierarchy_type
181 ,p_child_value_set => p_child_value_set
182 ,p_parent_node_type => p_parent_node_type
183 ,p_identifier_key => NULL
184 ,p_description => p_description
185 ,p_hier_node_type_id => l_hier_node_type_id
186 ,p_object_version_number => l_object_version_number
187 );
188 exception
189 when hr_api.cannot_find_prog_unit then
190 hr_api.cannot_find_prog_unit_error
191 (p_module_name => 'create_node_type_a'
192 ,p_hook_type => 'AP'
193 );
194 end;
195 --
196 -- When in validation only mode raise the Validate_Enabled exception
197 --
198 if p_validate then
199 raise hr_api.validate_enabled;
200 end if;
201 --
202 -- Set all output arguments
203 --
204 p_hier_node_type_id := l_hier_node_type_id;
205 p_object_version_number := l_object_version_number;
206 --
207 hr_utility.set_location(' Leaving:'||l_proc, 70);
208 exception
209 when hr_api.validate_enabled then
210 --
211 -- As the Validate_Enabled exception has been raised
212 -- we must rollback to the savepoint
213 --
214 rollback to create_node_type;
215 --
216 -- Only set output warning arguments
217 -- (Any key or derived arguments must be set to null
218 -- when validation only mode is being used.)
219 --
220 p_hier_node_type_id := null;
221 p_object_version_number := null;
222 hr_utility.set_location(' Leaving:'||l_proc, 80);
223 when others then
224 --
225 -- A validation or unexpected error has occured
226 --
227 rollback to create_node_type;
228 hr_utility.set_location(' Leaving:'||l_proc, 90);
229 raise;
230 end create_node_type;
231 --
232 -- ----------------------------------------------------------------------------
233 -- |---------------------------< update_node_type >---------------------------|
234 -- ----------------------------------------------------------------------------
235 --
236 procedure update_node_type
237 (p_validate in boolean default false
238 ,p_effective_date in date
239 ,p_hier_node_type_id in number
240 ,p_object_version_number in out nocopy number
241 ,p_child_node_name in varchar2 default hr_api.g_varchar2
242 ,p_child_value_set in varchar2 default hr_api.g_varchar2
243 ,p_parent_node_type in varchar2 default hr_api.g_varchar2
244 ,p_description in varchar2 default hr_api.g_varchar2
245 ) is
246 --
247 -- Declare cursors and local variables
248 --
249 -- return the current lookup value for the current lang, etc
250 -- for the child node type
251 CURSOR csr_lookup IS
252 SELECT lookup_code, created_by, meaning, description
253 FROM hr_lookups
254 WHERE lookup_type = 'HIERARCHY_NODE_TYPE'
255 AND lookup_code = (SELECT child_node_type
256 FROM per_gen_hier_node_types
257 WHERE hier_node_type_id = p_hier_node_type_id);
258 --
259 --
260 l_lookup_code fnd_lookup_values.lookup_code%type := null;
261 l_created_by fnd_lookup_values.created_by%type := null;
262 l_meaning fnd_lookup_values.meaning%type := null;
263 l_desc fnd_lookup_values.description%type := null;
264 l_update_attempted boolean := false;
265 l_proc varchar2(80) := g_package||'update_node_type';
266 l_hier_node_type_id per_gen_hier_node_types.hier_node_type_id%TYPE;
267 l_object_version_number per_gen_hier_node_types.object_version_number%TYPE;
268 l_effective_date date;
269 --
270 begin
271 hr_utility.set_location('Entering:'|| l_proc, 10);
272 --
273 hr_api.mandatory_arg_error(P_API_NAME => l_proc
274 ,P_ARGUMENT => 'p_hier_node_type_id'
275 ,P_ARGUMENT_VALUE => p_hier_node_type_id );
276 --
277 -- Issue a savepoint
278 --
279 savepoint update_node_type;
280 --
281 -- Store initial values for IN OUT parameters
282 --
283 l_object_version_number := p_object_version_number;
284 --
285 -- Truncate the time portion from all IN date parameters
286 --
287 l_effective_date := trunc(p_effective_date);
288 --
289 -- Param p_child_node_name is updateable for a custom lookup code only
290 -- (not seeded), so we process the accociated
291 -- lookup value (meaning and desc) for p_child_node_type first i.e.
292 -- 1) see if the meaning or description is being updated (for current language)
293 -- 2) see if the update is allowed for this lookup
294 --
295
296 If p_child_node_name is not null then
297 open csr_lookup;
298 fetch csr_lookup into l_lookup_code, l_created_by, l_meaning, l_desc;
299 close csr_lookup;
300
301 If l_meaning is null then
302 -- The lookup code does not exist so error
303 fnd_message.set_name('PER','HR_289894_PGT_LOOKUP_NOT_FOUND');
304 fnd_message.set_token('ID',to_char(p_hier_node_type_id));
305 fnd_message.raise_error;
306 End if;
307
308 If l_meaning <> p_child_node_name or nvl(l_desc,'1') <> nvl(p_description,'1') then
309 l_update_attempted := TRUE;
310 End if;
311
312 If l_created_by = 1 and l_update_attempted then
313 -- cannot update a seeded lookup code (auto-install)
314 l_update_attempted := false;
315 fnd_message.set_name('PER','HR_289895_PGT_LOOKUP_NO_UPDATE');
316 fnd_message.raise_error;
317 End if;
318
319
320 If l_update_attempted then
321 BEGIN
322 -- lock the fnd_lookup_values record first....
323
324 -- Now try the update of lookup meaning and desc
325 -- for the child_node_type
326
327 fnd_lookup_values_pkg.UPDATE_ROW(X_LOOKUP_TYPE => 'HIERARCHY_NODE_TYPE',
328 X_SECURITY_GROUP_ID => 0,
329 X_VIEW_APPLICATION_ID => 3,
330 X_LOOKUP_CODE => l_lookup_code,
331 X_TAG => null,
332 X_ATTRIBUTE_CATEGORY => null,
333 X_ATTRIBUTE1 => null,
334 X_ATTRIBUTE2 => null,
335 X_ATTRIBUTE3 => null,
336 X_ATTRIBUTE4 => null,
337 X_ENABLED_FLAG => 'Y',
338 X_START_DATE_ACTIVE => null,
339 X_END_DATE_ACTIVE => null,
340 X_TERRITORY_CODE => null,
341 X_ATTRIBUTE5 => null,
342 X_ATTRIBUTE6 => null,
343 X_ATTRIBUTE7 => null,
344 X_ATTRIBUTE8 => null,
345 X_ATTRIBUTE9 => null,
346 X_ATTRIBUTE10 => null,
347 X_ATTRIBUTE11 => null,
348 X_ATTRIBUTE12 => null,
349 X_ATTRIBUTE13 => null,
350 X_ATTRIBUTE14 => null,
351 X_ATTRIBUTE15 => null,
352 X_MEANING => p_child_node_name,
353 X_DESCRIPTION => p_description,
354 X_LAST_UPDATED_BY => fnd_global.user_id,
355 X_LAST_UPDATE_DATE => SYSDATE,
356 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
357
358 EXCEPTION
359 WHEN OTHERS THEN
360 fnd_message.set_name('PER','HR_289896_PGT_LOOKUP_UPD_FAIL');
361 fnd_message.raise_error;
362 END;
363 end if;
364 else -- cannot update p_child_node_name to null
365 fnd_message.set_name('PER','HR_289897_PGT_LOOKUP_NULL');
366 fnd_message.raise_error;
367 end if;
368
369 --
370 -- Call Before Process User Hook
371 --
372 begin
373 HR_CALENDAR_NODE_TYPE_BK2.update_node_type_b
374 (p_effective_date => l_effective_date
375 ,p_hier_node_type_id => p_hier_node_type_id
376 ,p_child_node_name => p_child_node_name
377 ,p_object_version_number => l_object_version_number
378 ,p_child_value_set => p_child_value_set
379 ,p_parent_node_type => p_parent_node_type
380 ,p_description => p_description
381 );
382 exception
383 when hr_api.cannot_find_prog_unit then
384 hr_api.cannot_find_prog_unit_error
385 (p_module_name => 'update_node_type_b'
386 ,p_hook_type => 'BP'
387 );
388 end;
389 --
390 -- Validation in addition to Row Handlers
391 --
392 --
393 -- Process Logic
394 --
395 per_pgt_upd.upd
396 (p_effective_date => l_effective_date
397 ,p_hier_node_type_id => p_hier_node_type_id
398 ,p_object_version_number => l_object_version_number
399 ,p_child_value_set => p_child_value_set
400 ,p_parent_node_type => p_parent_node_type
401 ,p_identifier_key => NULL);
402
403 --
404 --
405 begin
406 HR_CALENDAR_NODE_TYPE_BK2.update_node_type_a
407 (p_effective_date => l_effective_date
408 ,p_hier_node_type_id => p_hier_node_type_id
409 ,p_child_node_name => p_child_node_name
410 ,p_object_version_number => l_object_version_number
411 ,p_child_value_set => p_child_value_set
412 ,p_parent_node_type => p_parent_node_type
413 ,p_description => p_description
414 );
415 exception
416 when hr_api.cannot_find_prog_unit then
417 hr_api.cannot_find_prog_unit_error
418 (p_module_name => 'update_node_type_a'
419 ,p_hook_type => 'AP'
420 );
421 end;
422 --
423 -- When in validation only mode raise the Validate_Enabled exception
424 --
425 if p_validate then
426 raise hr_api.validate_enabled;
427 end if;
428 --
429 -- Set all output arguments
430 --
431 p_object_version_number := l_object_version_number;
432 --
433 hr_utility.set_location(' Leaving:'||l_proc, 70);
434 exception
435 when hr_api.validate_enabled then
436 --
437 -- As the Validate_Enabled exception has been raised
438 -- we must rollback to the savepoint
439 --
440 rollback to update_node_type;
441 --
442 -- Only set output warning arguments
443 -- (Any key or derived arguments must be set to null
444 -- when validation only mode is being used.)
445 --
446 hr_utility.set_location(' Leaving:'||l_proc, 80);
447 when others then
448 --
449 -- A validation or unexpected error has occured
450 --
451 rollback to update_node_type;
452 hr_utility.set_location(' Leaving:'||l_proc, 90);
453 raise;
454 end update_node_type;
455 --
456 --
457 -- ----------------------------------------------------------------------------
458 -- |-----------------------------< delete_node_type >-------------------------|
459 -- ----------------------------------------------------------------------------
460 --
461 procedure delete_node_type
462 (p_validate in boolean default false
463 ,p_hier_node_type_id in number
464 ,p_object_version_number in number
465 ) is
466 --
467 -- Declare cursors and local variables
468 --
469 -- Fetch the lookup to be deleted
470 CURSOR csr_get_child_code IS
471 SELECT child_node_type
472 FROM per_gen_hier_node_types
473 WHERE hier_node_type_id = p_hier_node_type_id;
474 --
475 l_proc varchar2(72) := g_package||'delete_node_type';
476 l_child_node_type per_gen_hier_node_types.child_node_type%Type;
477 --
478 begin
479 hr_utility.set_location('Entering:'|| l_proc, 5);
480 --
481
482 hr_api.mandatory_arg_error(P_API_NAME => l_proc
483 ,P_ARGUMENT => 'p_hier_node_type_id'
484 ,P_ARGUMENT_VALUE => p_hier_node_type_id );
485 --
486 -- Issue a savepoint if operating in validation only mode.
487 --
488 savepoint delete_node_type;
489 --
490 -- get the lookup code prior to deleting the node type
491 -- for use later...
492 --
493 open csr_get_child_code;
494 fetch csr_get_child_code into l_child_node_type;
495 If csr_get_child_code%notfound then
496 close csr_get_child_code;
497 fnd_message.set_name('PER','HR_289894_PGT_LOOKUP_NOT_FOUND');
498 fnd_message.set_token('ID',to_char(p_hier_node_type_id));
499 fnd_message.raise_error;
500 else
501 close csr_get_child_code;
502
503 -- lock the fnd_lookup_values record before deleting the node type
504 end if;
505
506 --
507 -- Call Before Process User Hook
508 --
509 begin
510 HR_CALENDAR_NODE_TYPE_BK3.delete_node_type_b
511 (p_hier_node_type_id => p_hier_node_type_id,
512 p_object_version_number => p_object_version_number
513 );
514 exception
515 when hr_api.cannot_find_prog_unit then
516 hr_api.cannot_find_prog_unit_error
517 (p_module_name => 'delete_node_type_b',
518 p_hook_type => 'BP'
519 );
520 end;
521 --
522 --
523 hr_utility.set_location(l_proc, 7);
524 --
525 -- Process Logic
526 --
527 per_pgt_del.del
528 (p_hier_node_type_id => p_hier_node_type_id
529 ,p_object_version_number => p_object_version_number
530 );
531 --
532 hr_utility.set_location(l_proc, 8);
533 --
534 --
535 BEGIN
536 -- Delete the lookup code now the node_type has gone
537 fnd_lookup_values_pkg.DELETE_ROW
538 (X_LOOKUP_TYPE => 'HIERARCHY_NODE_TYPE'
539 ,X_SECURITY_GROUP_ID => 0
540 ,X_VIEW_APPLICATION_ID => 3
541 ,X_LOOKUP_CODE => l_child_node_type);
542
543 EXCEPTION
544 WHEN OTHERS THEN
545 fnd_message.set_name('PER','HR_289898_PGT_LOOKUP_DEL_FAIL');
546 fnd_message.raise_error;
547 END;
548 --
549 --
550 -- Call After Process User Hook
551 begin
552 HR_CALENDAR_NODE_TYPE_BK3.delete_node_type_a
553 (p_hier_node_type_id => p_hier_node_type_id,
554 p_object_version_number => p_object_version_number
555 );
556 exception
557 when hr_api.cannot_find_prog_unit then
558 hr_api.cannot_find_prog_unit_error
559 (p_module_name => 'delete_node_type_a',
560 p_hook_type => 'AP'
561 );
562 end;
563 --
564 -- When in validation only mode raise the Validate_Enabled exception
565 --
566 if p_validate then
567 raise hr_api.validate_enabled;
568 end if;
569 --
570 hr_utility.set_location(' Leaving:'||l_proc, 11);
571 exception
572 when hr_api.validate_enabled then
573 --
574 -- As the Validate_Enabled exception has been raised
575 -- we must rollback to the savepoint
576 --
577 ROLLBACK TO delete_node_type;
578 --
579 hr_utility.set_location(' Leaving:'||l_proc, 12);
580 --
581 when others then
582 --
583 --
584 ROLLBACK TO delete_node_type;
585 --
586 raise;
587 --
588 end delete_node_type;
589 --
590 --
591 --
592 Function get_node_level (p_hierarchy_type in VARCHAR2
593 ,p_child_node_type IN VARCHAR2) RETURN NUMBER IS
594
595 -- tree walk from supplied child node to top level
596 -- to obtain level of child within the hierarchy
597
598 CURSOR csr_get_level IS
599 select max(level) from per_gen_hier_node_types
600 where hierarchy_type = p_hierarchy_type
601 connect by child_node_type = prior parent_node_type
602 start with child_node_type = p_child_node_type;
603
604 l_level NUMBER := null;
605
606 BEGIN
607
608 open csr_get_level;
609 fetch csr_get_level into l_level;
610 close csr_get_level;
611
612 RETURN nvl(l_level,0);
613
614 END get_node_level;
615 --
616 --
617 --
618 Function child_exists (p_hierarchy_type in VARCHAR2
619 ,p_child_node_type IN VARCHAR2) RETURN VARCHAR2 IS
620
621 -- Check if there are any children for the supplied child node
622 -- within the supplied hierarchy.
623
624 CURSOR csr_exist IS
625 select 'Y'
626 from per_gen_hier_node_types
627 where hierarchy_type = p_hierarchy_type
628 and parent_node_type = p_child_node_type
629 and rownum = 1;
630
631 l_DeleteCode Varchar2(1) := null;
632
633 BEGIN
634
635 open csr_exist;
636 fetch csr_exist into l_DeleteCode;
637 close csr_exist;
638
639 RETURN nvl(l_DeleteCode,'N');
640
641 END child_exists;
642 --
643 --
644 Function gen_hier_exists (p_hierarchy_type in VARCHAR2) RETURN VARCHAR2 IS
645 --
646 -- Check if there is a generic hierarchy based on the supplied node_type
647 -- (scope) hierarchy.
648 --
649 CURSOR csr_hexist IS
650 select 'Y'
651 from per_gen_hierarchy
652 where type = p_hierarchy_type
653 and rownum = 1;
654
655 l_return Varchar2(1) := null;
656
657 BEGIN
658
659 open csr_hexist;
660 fetch csr_hexist into l_return;
661 close csr_hexist;
662
663 RETURN nvl(l_return,'N');
664 --
665 END gen_hier_exists;
666
667 end HR_CALENDAR_NODE_TYPE_API;