[Home] [Help]
PACKAGE BODY: APPS.HR_GRADE_SCALE_API
Source
1 Package Body hr_grade_scale_api as
2 /* $Header: pepgsapi.pkb 120.1.12000000.1 2007/01/22 01:19:48 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_grade_scale.';
7 --
8 --
9 -- ----------------------------------------------------------------------------
10 -- |-------------------< get_grade_scale_starting_step >----------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 FUNCTION get_grade_scale_starting_step
14 (p_grade_spine_id IN NUMBER
15 ,p_effective_date IN DATE)
16 RETURN NUMBER
17 IS
18 CURSOR csr_starting_step IS
19 SELECT nvl(pgs.starting_step,1)
20 FROM per_grade_spines_f pgs
21 WHERE pgs.grade_spine_id = p_grade_spine_id
22 AND p_effective_date between pgs.effective_start_date
23 and pgs.effective_end_date;
24
25 l_starting_step per_grade_spines_f.starting_step%TYPE;
26
27 BEGIN
28
29 OPEN csr_starting_step;
30 FETCH csr_starting_step into l_starting_step;
31 CLOSE csr_starting_step;
32
33 RETURN l_starting_step;
34
35 END get_grade_scale_starting_step;
36
37 --
38 -- ----------------------------------------------------------------------------
39 -- |----------------------< create_grade_scale >------------------------------|
40 -- ----------------------------------------------------------------------------
41 procedure create_grade_scale
42 (p_validate in boolean default false
43 ,p_effective_date in date
44 ,p_business_group_id in number
45 ,p_parent_spine_id in number
46 ,p_grade_id in number
47 ,p_ceiling_point_id in number default null
48 ,p_starting_step in number default 1
49 ,p_request_id in number default null
50 ,p_program_application_id in number default null
51 ,p_program_id in number default null
52 ,p_program_update_date in date default null
53 ,p_ceiling_step_id out nocopy number
54 ,p_grade_spine_id out nocopy number
55 ,p_effective_start_date out nocopy date
56 ,p_effective_end_date out nocopy date
57 ,p_object_version_number out nocopy number
58 ) is
59
60 --
61 -- Declare cursors and local variables
62 --
63 l_proc varchar2(72) := g_package||'create_grade_scale';
64 l_effective_date date;
65
66 --
67 -- Declare out parameters
68 --
69 l_grade_spine_id per_grade_spines_f.grade_spine_id%TYPE;
70 l_ceiling_step_id per_grade_spines_f.ceiling_step_id%TYPE;
71 l_object_version_number per_grade_spines_f.object_version_number%TYPE;
72 l_effective_start_date date;
73 l_effective_end_date date;
74
75 --
76 -- Declare for per_spinal_point_steps_f
77 l_sequence per_spinal_points.sequence%TYPE;
78 lv_object_version_number per_spinal_point_steps_f.object_version_number%TYPE;
79 --
80 cursor csr_get_sequence is
81 select psp.sequence
82 from per_spinal_points psp
83 where psp.business_group_id = p_business_group_id
84 and psp.spinal_point_id = p_ceiling_point_id;
85 --
86 begin
87 --
88 hr_utility.set_location('Entering:'|| l_proc, 10);
89 --
90 -- Issue a savepoint
91 --
92 savepoint create_grade_scale;
93
94 hr_utility.set_location(l_proc, 20);
95
96 --
97 -- Truncate the time portion from all IN date parameters
98 --
99 l_effective_date := trunc(p_effective_date);
100
101 --
102 -- Call Before Process User hook for create_grade_scale
103 --
104 begin
105 hr_grade_scale_bk1.create_grade_scale_b
106 (p_effective_date => l_effective_date
107 ,p_business_group_id => p_business_group_id
108 ,p_parent_spine_id => p_parent_spine_id
109 ,p_grade_id => p_grade_id
110 ,p_ceiling_point_id => p_ceiling_point_id
111 ,p_request_id => p_request_id
112 ,p_program_application_id => p_program_application_id
113 ,p_program_id => p_program_id
114 ,p_program_update_date => p_program_update_date
115 );
116 exception
117 when hr_api.cannot_find_prog_unit then
118 hr_api.cannot_find_prog_unit_error
119 (p_module_name => 'CREATE_GRADE_SCALE'
120 ,p_hook_type => 'BP'
121 );
122 --
123 -- End of before hook process (create_grade_scale)
124 --
125 end;
126
127 hr_utility.set_location(l_proc, 30);
128 --
129 -- Process Logic
130 --
131 l_ceiling_step_id := null;
132 --
133 -- Insert Grade scale
134 --
135 per_pgs_ins.ins
136 (p_effective_date => l_effective_date
137 ,p_business_group_id => p_business_group_id
138 ,p_parent_spine_id => p_parent_spine_id
139 ,p_grade_id => p_grade_id
140 ,p_request_id => p_request_id
141 ,p_program_application_id => p_program_application_id
142 ,p_program_id => p_program_id
143 ,p_program_update_date => p_program_update_date
144 ,p_ceiling_step_id => l_ceiling_step_id
145 ,p_starting_step => p_starting_step
146 ,p_grade_spine_id => l_grade_spine_id
147 ,p_object_version_number => l_object_version_number
148 ,p_effective_start_date => l_effective_start_date
149 ,p_effective_end_date => l_effective_end_date
150 );
151 --
152 hr_utility.set_location(l_proc, 40);
153 --
154 -- We need to create the Grade Step (Ceiling Step), if the
155 -- parameter p_ceiling_point_id is passed with a value
156 --
157 if p_ceiling_point_id is not null then
158 -- Needs to get the sequence of the ceiling point
159 open csr_get_sequence;
160 fetch csr_get_sequence into l_sequence;
161 close csr_get_sequence;
162 -- Call hr_grade_step_api.create_grade_step
163 --
164 hr_grade_step_api.create_grade_step
165 (p_effective_date => p_effective_date
166 ,p_validate => p_validate
167 ,p_business_group_id => p_business_group_id
168 ,p_spinal_point_id => p_ceiling_point_id
169 ,p_grade_spine_id => l_grade_spine_id
170 ,p_sequence => l_sequence
171 ,p_step_id => l_ceiling_step_id
172 ,p_effective_start_date => l_effective_start_date
173 ,p_effective_end_date => l_effective_end_date
174 ,p_object_version_number => lv_object_version_number
175 );
176 --
177 hr_utility.set_location(l_proc, 60);
178 --
179 -- Update ceiling_step_id in per_grade_spines_f
180 --
181 per_pgs_upd.upd
182 (p_effective_date => l_effective_date
183 ,p_datetrack_mode => 'CORRECTION'
184 ,p_grade_spine_id => l_grade_spine_id
185 ,p_object_version_number => l_object_version_number
186 ,p_ceiling_step_id => l_ceiling_step_id
187 ,p_effective_start_date => l_effective_start_date
188 ,p_effective_end_date => l_effective_end_date
189 );
190 --
191 hr_utility.set_location(l_proc, 70);
192 --
193 end if;
194 --
195 -- Call After Process hook for create_grade_scale
196 --
197 begin
198 hr_grade_scale_bk1.create_grade_scale_a
199 (p_effective_date => l_effective_date
200 ,p_business_group_id => p_business_group_id
201 ,p_parent_spine_id => p_parent_spine_id
202 ,p_grade_id => p_grade_id
203 ,p_ceiling_point_id => p_ceiling_point_id
204 ,p_request_id => p_request_id
205 ,p_program_application_id => p_program_application_id
206 ,p_program_id => p_program_id
207 ,p_program_update_date => p_program_update_date
208 ,p_grade_spine_id => l_grade_spine_id
209 ,p_ceiling_step_id => l_ceiling_step_id
210 ,p_object_version_number => l_object_version_number
211 ,p_effective_start_date => l_effective_start_date
212 ,p_effective_end_date => l_effective_end_date
213 );
214 exception
215 when hr_api.cannot_find_prog_unit then
216 hr_api.cannot_find_prog_unit_error
217 (p_module_name => 'CREATE_GRADE_SCALE'
218 ,p_hook_type => 'AP'
219 );
220 --
221 -- End of after hook process (create_grade_scale)
222 --
223 end;
224 --
225 -- When in validation only mode raise the Validate_Enabled exception
226 --
227 if p_validate
228 then
229 raise hr_api.validate_enabled;
230 end if;
231 --
232 hr_utility.set_location(l_proc, 80);
233 --
234 -- Set OUT parameters
235 --
236 p_grade_spine_id := l_grade_spine_id;
237 p_ceiling_step_id := l_ceiling_step_id;
238 p_object_version_number := l_object_version_number;
239 p_effective_start_date := l_effective_start_date;
240 p_effective_end_date := l_effective_end_date;
241 --
242 hr_utility.set_location(' Leaving:' ||l_proc, 90);
243 --
244 exception
245 --
246 when hr_api.validate_enabled then
247 --
248 -- As the Validate_Enabled exception has been raised
249 -- we must rollback to the savepoint
250 --
251 ROLLBACK TO create_grade_scale;
252 --
253 -- Set OUT parameters to null
254 -- Only set output warning arguments
255 -- (Any key or derived arguments must be set to null
256 -- when validation only mode is being used.)
257 --
258 p_grade_spine_id := null;
259 p_ceiling_step_id := null;
260 p_object_version_number := null;
261 p_effective_start_date := null;
262 p_effective_end_date := null;
263 --
264 hr_utility.set_location(' Leaving:'||l_proc, 100);
265 --
266 when others then
267 --
268 -- A validation or unexpected error has occurred
269 --
270 ROLLBACK TO create_grade_scale;
271 --
272 hr_utility.set_location(' Leaving:'||l_proc, 110);
273 --
274 p_grade_spine_id := null;
275 p_ceiling_step_id := null;
276 p_object_version_number := null;
277 p_effective_start_date := null;
278 p_effective_end_date := null;
279 --
280 raise;
281 --
282 end create_grade_scale;
283 --
284 -- ----------------------------------------------------------------------------
285 -- |------------------------< update_grade_scale >----------------------------|
286 -- ----------------------------------------------------------------------------
287 --
288 procedure update_grade_scale
289 (p_validate in boolean default false
290 ,p_effective_date in date default hr_api.g_date
291 ,p_datetrack_mode in varchar2
292 ,p_grade_spine_id in number
293 ,p_object_version_number in out nocopy number
294 ,p_business_group_id in number default hr_api.g_number
295 ,p_parent_spine_id in number default hr_api.g_number
296 ,p_grade_id in number default hr_api.g_number
297 ,p_ceiling_step_id in number default hr_api.g_number
298 ,p_starting_step in number default hr_api.g_number
299 ,p_request_id in number default hr_api.g_number
300 ,p_program_application_id in number default hr_api.g_number
301 ,p_program_id in number default hr_api.g_number
302 ,p_program_update_date in date default hr_api.g_date
303 ,p_effective_start_date out nocopy date
304 ,p_effective_end_date out nocopy date
305 ) is
306
307 --
308 -- Declare cursors and local variables
309 --
310 l_proc varchar2(72) := g_package||'update_grade_scale';
311 l_effective_date date;
312 lv_object_version_number per_grade_spines_f.object_version_number%TYPE;
313
314 --
315 -- Declare out parameters
316 --
317 l_object_version_number per_grade_spines_f.object_version_number%TYPE;
318 l_effective_start_date per_grade_spines_f.effective_start_date%TYPE;
319 l_effective_end_date per_grade_spines_f.effective_end_date%TYPE;
320
321 --
322 begin
323 --
324 hr_utility.set_location('Entering:'|| l_proc, 10);
325 --
326 lv_object_version_number := p_object_version_number;
327
328 --
329 -- Issue a savepoint
330 --
331 savepoint update_grade_scale;
332
333 hr_utility.set_location(l_proc, 20);
334
335 --
336 -- Truncate the time portion from all IN date parameters
337 --
338 l_effective_date := trunc(p_effective_date);
339
340 --
341 -- store object version number passed in
342 --
343 l_object_version_number := p_object_version_number;
344
345 --
346 -- Call Before Process User hook for create_grade_scale
347 --
348 begin
349 hr_grade_scale_bk2.update_grade_scale_b
350 (p_effective_date => l_effective_date
351 ,p_grade_spine_id => p_grade_spine_id
352 ,p_business_group_id => p_business_group_id
353 ,p_parent_spine_id => p_parent_spine_id
354 ,p_grade_id => p_grade_id
355 ,p_ceiling_step_id => p_ceiling_step_id
356 ,p_request_id => p_request_id
357 ,p_program_application_id => p_program_application_id
358 ,p_program_id => p_program_id
359 ,p_program_update_date => p_program_update_date
360 ,p_datetrack_mode => p_datetrack_mode
361 ,p_object_version_number => l_object_version_number
362 );
363 exception
364 when hr_api.cannot_find_prog_unit then
365 hr_api.cannot_find_prog_unit_error
366 (p_module_name => 'UPDATE_GRADE_SCALE'
367 ,p_hook_type => 'BP'
368 );
369 --
370 -- End of before hook process (update_grade_scale)
371 --
372 end;
373
374 hr_utility.set_location(l_proc, 30);
375 --
376 -- Process Logic
377 --
378
379 l_object_version_number := p_object_version_number;
380
381 --
382 -- Update Progression Point
383 --
384 --
385 per_pgs_upd.upd
386 (p_effective_date => l_effective_date
387 ,p_datetrack_mode => p_datetrack_mode
388 ,p_grade_spine_id => p_grade_spine_id
389 ,p_object_version_number => l_object_version_number
390 ,p_business_group_id => p_business_group_id
391 ,p_parent_spine_id => p_parent_spine_id
392 ,p_grade_id => p_grade_id
393 ,p_ceiling_step_id => p_ceiling_step_id
394 ,p_starting_step => p_starting_step
395 ,p_request_id => p_request_id
396 ,p_program_application_id => p_program_application_id
397 ,p_program_id => p_program_id
398 ,p_program_update_date => p_program_update_date
399 ,p_effective_start_date => l_effective_start_date
400 ,p_effective_end_date => l_effective_end_date
401 );
402
403 --
404 -- Assign the out parameters.
405 --
406 p_object_version_number := l_object_version_number;
407
408 hr_utility.set_location(l_proc, 40);
409
410 --
411 --
412 -- Call After Process hook for update_grade_scale
413 --
414
415 begin
416 hr_grade_scale_bk2.update_grade_scale_a
417 (p_effective_date => l_effective_date
418 ,p_datetrack_mode => p_datetrack_mode
419 ,p_grade_spine_id => p_grade_spine_id
420 ,p_object_version_number => l_object_version_number
421 ,p_business_group_id => p_business_group_id
422 ,p_parent_spine_id => p_parent_spine_id
423 ,p_grade_id => p_grade_id
424 ,p_ceiling_step_id => p_ceiling_step_id
425 ,p_request_id => p_request_id
426 ,p_program_application_id => p_program_application_id
427 ,p_program_id => p_program_id
428 ,p_program_update_date => p_program_update_date
429 ,p_effective_start_date => l_effective_start_date
430 ,p_effective_end_date => l_effective_end_date
431 );
432 exception
433 when hr_api.cannot_find_prog_unit then
434 hr_api.cannot_find_prog_unit_error
435 (p_module_name => 'UPDATE_GRADE_SCALE'
436 ,p_hook_type => 'AP'
437 );
438 --
439 -- End of after hook process (update_grade_scale)
440 --
441 end;
442 --
443 -- When in validation only mode raise the Validate_Enabled exception
444 --
445 if p_validate
446 then
447 raise hr_api.validate_enabled;
448 end if;
449 --
450 hr_utility.set_location(l_proc, 50);
451 --
452 -- Set OUT parameters
453 --
454 p_object_version_number := l_object_version_number;
455 p_effective_start_date := l_effective_start_date;
456 p_effective_end_date := l_effective_end_date;
457 --
458 hr_utility.set_location(' Leaving:' ||l_proc, 60);
459 --
460 exception
461 --
462 when hr_api.validate_enabled then
463 --
464 -- As the Validate_Enabled exception has been raised
465 -- we must rollback to the savepoint
466 --
467 rollback to update_grade_scale;
468 --
469 -- Set OUT parameters to null
470 -- Only set output warning arguments
471 -- (Any key or derived arguments must be set to null
472 -- when validation only mode is being used.)
473 --
474 p_object_version_number := p_object_version_number;
475 --
476 hr_utility.set_location(' Leaving:'||l_proc, 70);
477 --
478 when others then
479 --
480 -- A validation or unexpected error has occurred
481 --
482 rollback to update_grade_scale;
483 --
484 p_object_version_number := lv_object_version_number;
485 p_effective_start_date := null;
486 p_effective_end_date := null;
487 --
488 hr_utility.set_location(' Leaving:'||l_proc, 80);
489 --
490 raise;
491 --
492 end update_grade_scale;
493 --
494 -- Fix for bug 3472194 starts here.
495 --
496 ------------------ delete_child_grade_steps --------------------
497 --
498 PROCEDURE delete_child_grade_steps
499 (p_validate in boolean
500 ,p_effective_date in date
501 ,p_datetrack_mode in varchar2
502 ,p_grade_spine_id in number
503 ) IS
504 --
505 l_proc varchar2(72) := g_package||'delete_child_grade_steps';
506 --
507 cursor csr_chk_placements(p_date date) is
508 select 'x'
509 from per_spinal_point_steps_f sps
510 where sps.grade_spine_id = p_grade_spine_id
511 and exists (select null
512 from per_spinal_point_placements_f spp
513 where spp.step_id = sps.step_id
514 and p_date < spp.effective_end_date);
515 --
516 cursor csr_chk_assignments(p_date date) is
517 select 'x'
518 from per_spinal_point_steps_f sps
519 where sps.grade_spine_id = p_grade_spine_id
520 and exists (select null
521 from per_assignments_f a
522 where a.special_ceiling_step_id = sps.step_id
523 and a.special_ceiling_step_id is not null
524 and p_date < a.effective_end_date);
525 --
526 cursor csr_chk_positions(p_date date) is
527 select 'x'
528 from per_spinal_point_steps_f sps
529 where sps.grade_spine_id = p_grade_spine_id
530 and exists (select null
531 from hr_all_positions_f p
532 where p.entry_step_id = sps.step_id
533 and p_date < p.effective_end_date);
534 --
535 Cursor csr_grade_steps IS
536 select distinct step_id
537 ,min(effective_start_date) min_eff_start_date
538 ,max(effective_end_date) max_eff_end_date
539 from per_spinal_point_steps_f
540 where grade_spine_id = p_grade_spine_id
541 group by step_id;
542 --
543 Cursor csr_step_details(p_step_id number,p_eff_date date) IS
544 select object_version_number
545 from per_spinal_point_steps_f
546 where step_id = p_step_id
547 and p_eff_date between effective_start_date and effective_end_date;
548 --
549 Cursor csr_grade_spine_esd IS
550 select min(effective_start_date)
551 from per_grade_spines_f
552 where grade_spine_id = p_grade_spine_id;
553 --
554 l_object_version_number per_spinal_point_steps_f.object_version_number%TYPE;
555 l_effective_start_date per_spinal_point_steps_f.effective_start_date%TYPE;
556 l_effective_end_date per_spinal_point_steps_f.effective_end_date%TYPE;
557 l_min_eff_start_date date;
558 l_max_eff_end_date date;
559 l_date date;
560 l_effective_date date;
561 l_datetrack_mode varchar2(30);
562 l_exists VARCHAR2(1);
563 --
564 BEGIN
565 --
566 hr_utility.set_location('Entering:'|| l_proc, 10);
567 hr_utility.set_location('Datetrack mode: '||p_datetrack_mode, 15);
568 --
569 l_date := p_effective_date;
570 --
571 -- Check if the steps are used in assignments or placements or positions.
572 --
573 -- Get the minimum start date of grade scale.
574 --
575 open csr_grade_spine_esd;
576 fetch csr_grade_spine_esd into l_date;
577 close csr_grade_spine_esd;
578 --
579 -- Need to check the existence of child records based on the DT mode.
580 -- For DELETE mode, no child record should exists after the effective_date.
581 -- For ZAp mode, no child record should exists on any date.
582 --
583 if p_datetrack_mode = hr_api.g_delete then
584 l_date := p_effective_date;
585 end if;
586 --
587 hr_utility.set_location(l_proc, 20);
588 --
589 open csr_chk_placements(l_date);
590 fetch csr_chk_placements into l_exists;
591 IF csr_chk_placements%found THEN
592 close csr_chk_placements;
593 hr_utility.set_message(801, 'PER_7933_DEL_GRDSPN_PLACE');
594 hr_utility.raise_error;
595 END IF;
596 close csr_chk_placements;
597 --
598 hr_utility.set_location(l_proc, 30);
599 --
600 open csr_chk_assignments(l_date);
601 fetch csr_chk_assignments into l_exists;
602 IF csr_chk_assignments%found THEN
603 close csr_chk_assignments;
604 hr_utility.set_message(801, 'PER_7934_DEL_GRDSPN_ASS');
605 hr_utility.raise_error;
606 END IF;
607 close csr_chk_assignments;
608 --
609 hr_utility.set_location(l_proc, 40);
610 --
611 open csr_chk_positions(l_date);
612 fetch csr_chk_positions into l_exists;
613 IF csr_chk_positions%found THEN
614 close csr_chk_positions;
615 hr_utility.set_message(800, 'PER_449137_DEL_GRDSPN_POS');
616 hr_utility.raise_error;
617 END IF;
618 close csr_chk_positions;
619 --
620 hr_utility.set_location(l_proc, 50);
621 --
622 -- Delete all the child step records before deleting the Grade Scale.
623 --
624 FOR grade_step_rec in csr_grade_steps LOOP
625 --
626 -- Re-initialize the local variables with default values.
627 --
628 l_effective_date := trunc(p_effective_date);
629 l_datetrack_mode := p_datetrack_mode;
630 --
631 hr_utility.set_location(l_proc, 60);
632 IF grade_step_rec.min_eff_start_date > p_effective_date AND
633 p_datetrack_mode = hr_api.g_delete THEN
634 --
635 -- ZAP the step using min effective_start_date as effective_date.
636 --
637 hr_utility.set_location(l_proc, 70);
638 l_effective_date := grade_step_rec.min_eff_start_date;
639 l_datetrack_mode := hr_api.g_zap;
640 --
641 ELSIF grade_step_rec.min_eff_start_date > p_effective_date AND
642 p_datetrack_mode = hr_api.g_zap THEN
643 --
644 -- To ZAP the step, use the min effective_start_date as effective_date.
645 --
646 hr_utility.set_location(l_proc, 80);
647 l_effective_date := grade_step_rec.min_eff_start_date;
648 --
649 ELSIF grade_step_rec.max_eff_end_date <= p_effective_date AND
650 p_datetrack_mode = hr_api.g_zap THEN
651 --
652 -- To ZAp the step, use the min effective_start_date as effective_date.
653 --
654 hr_utility.set_location(l_proc, 90);
655 l_effective_date := grade_step_rec.min_eff_start_date;
656 --
657 END IF;
658 --
659 hr_utility.set_location('l_effective_date: '||l_effective_date, 100);
660 hr_utility.set_location('l_datetrack_mode'||l_datetrack_mode, 100);
661 --
662 IF not (grade_step_rec.max_eff_end_date <= p_effective_date AND
663 p_datetrack_mode = hr_api.g_delete) THEN
664 --
665 -- Get the step details.
666 --
667 hr_utility.set_location(l_proc, 110);
668 open csr_step_details(grade_step_rec.step_id,l_effective_date);
669 fetch csr_step_details into l_object_version_number;
670 close csr_step_details;
671 --
672 hr_utility.set_location(l_datetrack_mode||' step: '||to_char(grade_step_rec.step_id), 120);
673 --
674 BEGIN
675 hr_grade_step_api.delete_grade_step(
676 p_validate => p_validate
677 ,p_step_id => grade_step_rec.step_id
678 ,p_effective_date => l_effective_date
679 ,p_datetrack_mode => l_datetrack_mode
680 ,p_Effective_Start_Date => l_effective_start_date
681 ,p_Effective_End_Date => l_effective_end_date
682 ,p_object_version_number => l_object_version_number
683 ,p_called_from_del_grd_scale => TRUE --bug 4096238
684 );
685 hr_utility.set_location(l_proc, 130);
686 EXCEPTION
687 when others then
688 hr_utility.set_location(l_proc, 140);
689 raise;
690 END;
691 END IF;
692 --
693 hr_utility.set_location(l_proc, 150);
694 END LOOP;
695 hr_utility.set_location('Leaving :'||l_proc, 160);
696 --
697 END;
698 --
699 -- Fix for bug 3472194 ends here.
700 --
701 -- ----------------------------------------------------------------------------
702 -- |-------------------------< delete_grade_scale >---------------------------|
703 -- ----------------------------------------------------------------------------
704 --
705 -- Fix for bug 3472194. This procedure will delete the associated grade step
706 -- records for dt mode DELETE and ZAP.
707 --
708 procedure delete_grade_scale
709 (p_validate in boolean
710 ,p_effective_date in date
711 ,p_datetrack_mode in varchar2
712 ,p_grade_spine_id in number
713 ,p_object_version_number in out nocopy number
714 ,p_effective_start_date out nocopy date
715 ,p_effective_end_date out nocopy date
716 ) IS
717
718 --
719 -- Declare cursors and local variables
720 --
721 l_proc varchar2(72) := g_package||'delete_grade_scale';
722 l_effective_date date;
723 lv_object_version_number per_grade_spines_f.object_version_number%TYPE;
724
725
726 --
727 -- Declare out variables
728 --
729 l_object_version_number per_grade_spines_f.object_version_number%TYPE;
730 l_effective_start_date per_grade_spines_f.effective_start_date%TYPE;
731 l_effective_end_date per_grade_spines_f.effective_end_date%TYPE;
732 --
733 begin
734 hr_utility.set_location('Entering:'|| l_proc, 10);
735
736 l_effective_date := trunc(p_effective_date);
737 lv_object_version_number := p_object_version_number;
738
739 --
740 -- Issue a savepoint
741 --
742 savepoint delete_grade_scale;
743
744 l_object_version_number := p_object_version_number;
745
746 --
747 -- Call Before Process User Hook
748 --
749 begin
750 hr_grade_scale_bk3.delete_grade_scale_b
751 (p_effective_date => l_effective_date
752 ,p_datetrack_mode => p_datetrack_mode
753 ,p_grade_spine_id => p_grade_spine_id
754 ,p_object_version_number => l_object_version_number
755 );
756 exception
757 when hr_api.cannot_find_prog_unit then
758 hr_api.cannot_find_prog_unit_error
759 (p_module_name => 'DELETE_GRADE_SCALE'
760 ,p_hook_type => 'BP'
761 );
762 end;
763
764 hr_utility.set_location(l_proc, 20);
765
766 --
767 -- Process Logic
768 --
769 -- Fix for bug 3472194 starts here.
770 -- Delete the child grade steps before deleting the grade scale.
771 --
772 IF p_datetrack_mode in (hr_api.g_delete, hr_api.g_zap) THEN
773 hr_utility.set_location(l_proc, 25);
774 delete_child_grade_steps
775 (p_validate => p_validate
776 ,p_effective_date => l_effective_date
777 ,p_datetrack_mode => p_datetrack_mode
778 ,p_grade_spine_id => p_grade_spine_id
779 );
780 END IF;
781 --
782 -- Fix for bug 3472194 ends here.
783 --
784 per_pgs_del.del
785 (p_effective_date => l_effective_date
786 ,p_datetrack_mode => p_datetrack_mode
787 ,p_grade_spine_id => p_grade_spine_id
788 ,p_object_version_number => l_object_version_number
789 ,p_effective_start_date => l_effective_start_date
790 ,p_effective_end_date => l_effective_end_date
791 );
792
793 hr_utility.set_location(l_proc, 30);
794
795 --
796 -- Call After Process User Hook
797 --
798 begin
799 hr_grade_scale_bk3.delete_grade_scale_a
800 (p_effective_date => l_effective_date
801 ,p_datetrack_mode => p_datetrack_mode
802 ,p_grade_spine_id => p_grade_spine_id
803 ,p_object_version_number => l_object_version_number
804 ,p_effective_start_date => l_effective_start_date
805 ,p_effective_end_date => l_effective_end_date
806 );
807 exception
808 when hr_api.cannot_find_prog_unit then
809 hr_api.cannot_find_prog_unit_error
810 (p_module_name => 'DELETE_GRADE_SCALE'
811 ,p_hook_type => 'AP'
812 );
813 end;
814
815 hr_utility.set_location(l_proc, 40);
816
817 p_object_version_number := l_object_version_number;
818 --
819 -- When in validation only mode raise the Validate_Enabled exception
820 --
821 if p_validate then
822 raise hr_api.validate_enabled;
823 end if;
824
825 --
826 -- Set all output arguments (returned by some dt modes only)
827 --
828 p_object_version_number := l_object_version_number;
829
830 hr_utility.set_location(' Leaving:'||l_proc, 100);
831 exception
832 when hr_api.validate_enabled then
833 hr_utility.set_location(' Leaving...:'||l_proc, 50);
834 --
835 -- As the Validate_Enabled exception has been raised
836 -- we must rollback to the savepoint
837 --
838 rollback to delete_grade_scale;
839 --
840 -- Only set output warning arguments
841 -- (Any key or derived arguments must be set to null
842 -- when validation only mode is being used.)
843 --
844 p_effective_start_date := null;
845 p_effective_end_date := null;
846 p_object_version_number := null;
847 --
848 when others then
849 hr_utility.set_location(' Leaving...:'||l_proc, 60);
850 --
851 -- A validation or unexpected error has occured
852 --
853 rollback to delete_grade_scale;
854 --
855 -- set in out parameters and set out parameters
856 --
857 p_effective_start_date := null;
858 p_effective_end_date := null;
859 p_object_version_number := lv_object_version_number;
860 --
861 raise;
862 --
863 end delete_grade_scale;
864 --
865 end hr_grade_scale_api;