1 package body PER_GRADE_SPINES_PKG as
2 /* $Header: pegrs01t.pkb 120.0 2005/05/31 09:32:19 appldev noship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Grade_Spine_Id IN OUT NOCOPY NUMBER,
6 X_Effective_Start_Date DATE,
7 X_Effective_End_Date DATE,
8 X_Business_Group_Id NUMBER,
9 X_Parent_Spine_Id NUMBER,
10 X_Grade_Id NUMBER,
11 X_Ceiling_Step_Id IN OUT NOCOPY NUMBER
12 ) IS
13 CURSOR C IS SELECT rowid FROM per_grade_spines_f
14 WHERE grade_spine_id = X_Grade_Spine_Id;
15
16 CURSOR C2 IS SELECT per_grade_spines_s.nextval
17 FROM sys.dual;
18
19 CURSOR C3 is select per_spinal_point_steps_s.nextval
20 from sys.dual;
21
22 BEGIN
23 if (X_grade_spine_id is NULL) then
24 OPEN C2;
25 FETCH C2 INTO X_grade_spine_id;
26 CLOSE C2;
27
28 OPEN C3;
29 FETCH C3 into X_ceiling_step_id;
30 CLOSE C3;
31 end if;
32 INSERT INTO per_grade_spines(
33 grade_spine_id,
34 effective_start_date,
35 effective_end_date,
36 business_group_id,
37 parent_spine_id,
38 grade_id,
39 ceiling_step_id
40 ) VALUES (
41 X_Grade_Spine_Id,
42 X_Effective_Start_Date,
43 X_Effective_End_Date,
44 X_Business_Group_Id,
45 X_Parent_Spine_Id,
46 X_Grade_Id,
47 X_Ceiling_Step_Id
48
49 );
50
51 OPEN C;
52 FETCH C INTO X_Rowid;
53 if (C%NOTFOUND) then
54 CLOSE C;
55 RAISE NO_DATA_FOUND;
56 end if;
57 CLOSE C;
58 END Insert_Row;
59
60
61 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
62 X_Grade_Spine_Id NUMBER,
63 X_Effective_Start_Date DATE,
64 X_Effective_End_Date DATE,
65 X_Business_Group_Id NUMBER,
66 X_Parent_Spine_Id NUMBER,
67 X_Grade_Id NUMBER,
68 X_Ceiling_Step_Id NUMBER,
69 X_starting_step NUMBER,
70 X_request_id NUMBER,
71 X_program_application_id NUMBER,
72 X_program_id NUMBER,
73 X_program_update_date DATE
74 ) IS
75 CURSOR C IS
76 SELECT *
77 FROM per_grade_spines_f
78 WHERE rowid = chartorowid(X_Rowid)
79 FOR UPDATE of Grade_Spine_Id NOWAIT;
80
81 Recinfo C%ROWTYPE;
82
83 BEGIN
84 OPEN C;
85 FETCH C INTO Recinfo;
86 if (C%NOTFOUND) then
87 CLOSE C;
88 RAISE NO_DATA_FOUND;
89 end if;
90 CLOSE C;
91 if (
92 ( (Recinfo.grade_spine_id = X_Grade_Spine_Id)
93 OR ( (Recinfo.grade_spine_id IS NULL)
94 AND (X_Grade_Spine_Id IS NULL)))
95 AND ( (Recinfo.effective_start_date = X_Effective_Start_Date)
96 OR ( (Recinfo.effective_start_date IS NULL)
97 AND (X_Effective_Start_Date IS NULL)))
98 AND ( (Recinfo.effective_end_date = X_Effective_End_Date)
99 OR ( (Recinfo.effective_end_date IS NULL)
100 AND (X_Effective_End_Date IS NULL)))
101 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
102 OR ( (Recinfo.business_group_id IS NULL)
103 AND (X_Business_Group_Id IS NULL)))
104 AND ( (Recinfo.parent_spine_id = X_Parent_Spine_Id)
105 OR ( (Recinfo.parent_spine_id IS NULL)
106 AND (X_Parent_Spine_Id IS NULL)))
107 AND ( (Recinfo.grade_id = X_Grade_Id)
108 OR ( (Recinfo.grade_id IS NULL)
109 AND (X_Grade_Id IS NULL)))
110 AND ( (Recinfo.ceiling_step_id = X_Ceiling_Step_Id)
111 OR ( (Recinfo.ceiling_step_id IS NULL)
112 AND (X_Ceiling_Step_Id IS NULL)))
113 AND ( (Recinfo.starting_step = X_starting_step)
114 OR ( (Recinfo.starting_step IS NULL)
115 AND (X_starting_step IS NULL)))
116 AND ( (Recinfo.request_id = X_request_Id)
117 OR ( (Recinfo.request_id IS NULL)
118 AND (X_request_Id IS NULL)))
119 AND ( (Recinfo.program_application_id = X_program_application_Id)
120 OR ( (Recinfo.program_application_id IS NULL)
121 AND (X_program_application_Id IS NULL)))
122 AND ( (Recinfo.program_id = X_program_Id)
123 OR ( (Recinfo.program_id IS NULL)
124 AND (X_program_Id IS NULL)))
125 AND ( (Recinfo.program_update_date = X_program_update_date)
126 OR ( (Recinfo.program_update_date IS NULL)
127 AND (X_program_update_date IS NULL)))
128 ) then
129 return;
130 else
131 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
132 APP_EXCEPTION.RAISE_EXCEPTION;
133 end if;
134 END Lock_Row;
135
136
137
138 PROCEDURE Update_Row(X_Rowid VARCHAR2,
139 X_Grade_Spine_Id NUMBER,
140 X_Effective_Start_Date DATE,
141 X_Effective_End_Date DATE,
142 X_Business_Group_Id NUMBER,
143 X_Parent_Spine_Id NUMBER,
144 X_Grade_Id NUMBER,
145 X_Ceiling_Step_Id NUMBER
146 ) IS
147 BEGIN
148 UPDATE per_grade_spines_f
149 SET
150 grade_spine_id = X_Grade_Spine_Id,
151 effective_start_date = X_Effective_Start_Date,
152 effective_end_date = X_Effective_End_Date,
153 business_group_id = X_Business_Group_Id,
154 parent_spine_id = X_Parent_Spine_Id,
155 grade_id = X_Grade_Id,
156 ceiling_step_id = X_Ceiling_Step_Id
157 WHERE rowid = chartorowid(X_rowid);
158
159 if (SQL%NOTFOUND) then
160 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
161 hr_utility.set_message_token('PROCEDURE','update_row');
162 hr_utility.set_message_token('STEP','1');
163 hr_utility.raise_error;
164 end if;
165
166 END Update_Row;
167
168
169
170 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
171 BEGIN
172
173 -- Start of fix for Bug 2694503.
174
175 DECLARE
176 l_grade_spine_id per_grade_spines_f.grade_spine_id%Type;
177 BEGIN
178 select grade_spine_id
179 into l_grade_spine_id
180 from per_grade_spines_f
181 where rowid = chartorowid(X_Rowid);
182
183 Delete from per_spinal_point_steps_f
184 where grade_spine_id = l_grade_spine_id;
185
186 Exception
187 When NO_DATA_FOUND then
188 null;
189
190 end ;
191
192 -- End of fix for Bug 2694503.
193
194 DELETE FROM per_grade_spines_f
195 WHERE rowid = chartorowid(X_Rowid);
196
197 if (SQL%NOTFOUND) then
198 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
199 hr_utility.set_message_token('PROCEDURE','delete_row');
200 hr_utility.set_message_token('STEP','1');
201 hr_utility.raise_error;
202 end if;
203 END Delete_Row;
204
205
206
207 procedure stb_del_validation(p_pspine_id IN NUMBER,
208 p_grd_id IN NUMBER) is
209 l_exists1 VARCHAR2(1);
210 l_exists2 VARCHAR2(1);
211
212 cursor c1 is
213 select 'x'
214 from per_spinal_point_steps_f sps,
215 per_grade_spines_f gs
216 where gs.grade_spine_id = sps.grade_spine_id
217 and gs.parent_spine_id = p_pspine_id
218 and gs.grade_id = p_grd_id
219 and exists
220 (select null
221 from per_spinal_point_placements_f sp
222 where sp.step_id = sps.step_id);
223 --
224 cursor c2 is
225 select 'x'
226 from per_spinal_point_steps_f sps,
227 per_grade_spines_f gs
228 where gs.grade_spine_id = sps.grade_spine_id
229 and gs.parent_spine_id = p_pspine_id
230 and gs.grade_id = p_grd_id
231 and exists
232 (select null
233 from per_assignments_f a
234 where a.special_ceiling_step_id = sps.step_id
235 and a.special_ceiling_step_id is not null);
236 --
237 begin
238 --
239 hr_utility.set_location('per_grade_spines_pkg.stb_del_validation',1);
240 --
241 open c1;
242 --
243 fetch c1 into l_exists1;
244 IF c1%found THEN
245 hr_utility.set_message(801, 'PER_7933_DEL_GRDSPN_PLACE');
246 close c1;
247 hr_utility.raise_error;
248 END IF;
249 --
250 close c1;
251 --
252 hr_utility.set_location('per_grade_spines_pkg.stb_del_validation',2);
253 --
254 open c2;
255 --
256 fetch c2 into l_exists2;
257 IF c2%found THEN
258 hr_utility.set_message(801, 'PER_7934_DEL_GRDSPN_ASS');
259 close c2;
260 hr_utility.raise_error;
261 END IF;
262 --
263 close c2;
264 --
265 end stb_del_validation;
266
267
268
269 procedure chk_unq_grade_spine(p_grd_id IN NUMBER,
270 p_sess IN DATE) is
271 l_exists VARCHAR2(1);
272
273 cursor c3 is
274 select 'x'
275 from per_grade_spines_f
276 where grade_id = p_grd_id
277 and p_sess between effective_start_date and effective_end_date;
278 --
279 begin
280 --
281 hr_utility.set_location('per_grade_spines_pkg.stb_del_validation',1);
282 --
283 open c3;
284 --
285 fetch c3 into l_exists;
286 IF c3%found THEN
287 hr_utility.set_message(801, 'PER_7932_GRDSPN_GRD_EXISTS');
288 close c3;
289 hr_utility.raise_error;
290 END IF;
291 --
292 close c3;
293 --
294 end chk_unq_grade_spine;
295
296
297
298 procedure first_step(
299 p_step_id IN NUMBER,
300 p_grade_spine_id IN NUMBER,
301 p_spinal_point_id IN NUMBER,
302 p_sequence IN NUMBER,
303 p_effective_start_date IN DATE,
304 p_effective_end_date IN DATE,
305 p_business_group_id IN NUMBER,
306 p_last_update_date IN DATE,
307 p_last_updated_by IN NUMBER,
308 p_last_update_login IN NUMBER,
309 p_created_by IN NUMBER,
310 p_creation_date IN DATE,
311 p_information_category in VARCHAR2) IS
312
313 begin
314
315 insert into per_spinal_point_steps_f(
316 step_id,
317 grade_spine_id,
318 spinal_point_id,
319 sequence,
320 effective_start_date,
321 effective_end_date,
322 business_group_id,
323 last_update_date,
324 last_updated_by,
325 last_update_login,
326 created_by,
327 creation_date,
328 information_category)
329 values (
330 p_step_id,
331 p_grade_spine_id,
332 p_spinal_point_id,
333 p_sequence,
334 p_effective_start_date,
335 p_effective_end_date,
336 p_business_group_id,
337 p_last_update_date,
338 p_last_updated_by,
339 p_last_update_login,
340 p_created_by,
341 p_creation_date,
342 p_information_category);
343
344
345 end first_step;
346
347 --
348 -- first_step_api inserts object_verson_number into per_spinal_point_steps_f
349 -- bug2999562
350 --
351 procedure first_step_api(
352 p_step_id IN NUMBER,
353 p_grade_spine_id IN NUMBER,
354 p_spinal_point_id IN NUMBER,
355 p_sequence IN NUMBER,
356 p_effective_start_date IN DATE,
357 p_effective_end_date IN DATE,
358 p_business_group_id IN NUMBER,
359 p_last_update_date IN DATE,
360 p_last_updated_by IN NUMBER,
361 p_last_update_login IN NUMBER,
362 p_created_by IN NUMBER,
363 p_creation_date IN DATE,
364 p_information_category in VARCHAR2,
365 p_object_version_number in number,
366 p_effective_date in date) IS
367
368 --
369 -- declare local variables
370 --
371 l_proc varchar2(72) := 'per_grade_spines_pkg.first_step_api';
372 l_pay_scale_name varchar2(30);
373 l_spinal_point_name varchar2(30);
374 l_grade_id number;
375 l_return varchar2(20);
376 l_message varchar2(2000) := null;
377 --
378 cursor csr_get_names is
379 select pps.name
380 ,psp.spinal_point
381 ,pgs.grade_id
382 from per_parent_spines pps
383 ,per_grade_spines_f pgs
384 ,per_spinal_points psp
385 where pgs.grade_spine_id = p_grade_spine_id
389 and psp.spinal_point_id = p_spinal_point_id;
386 and p_effective_date between
387 pgs.effective_start_date and pgs.effective_end_date
388 and pps.parent_spine_id = pgs.parent_spine_id
390
391
392 begin
393
394 hr_utility.set_location('Entering:' ||l_proc, 10);
395
396 insert into per_spinal_point_steps_f(
397 step_id,
398 grade_spine_id,
399 spinal_point_id,
400 sequence,
401 effective_start_date,
402 effective_end_date,
403 business_group_id,
404 last_update_date,
405 last_updated_by,
406 last_update_login,
407 created_by,
408 creation_date,
409 information_category,
410 object_version_number)
411 values (
412 p_step_id,
413 p_grade_spine_id,
414 p_spinal_point_id,
415 p_sequence,
416 p_effective_start_date,
417 p_effective_end_date,
418 p_business_group_id,
419 p_last_update_date,
420 p_last_updated_by,
421 p_last_update_login,
422 p_created_by,
423 p_creation_date,
424 p_information_category,
425 p_object_version_number);
426
427 hr_utility.set_location(l_proc, 20);
428
429 --
430 -- Need to create option and option in plan for Benefit
431 --
432 --
433 open csr_get_names;
434 fetch csr_get_names into l_pay_scale_name,l_spinal_point_name,l_grade_id;
435 if csr_get_names%notfound then
436 close csr_get_names;
437 hr_utility.set_location(l_proc, 30);
438 else
439 /* Comment out for BUG3179239
440 l_return := pqh_gsp_sync_compensation_obj.create_option_for_point
441 (p_spinal_point_id => p_spinal_point_id
442 ,p_pay_scale_name => l_pay_scale_name
443 ,p_spinal_point_name => l_spinal_point_name
444 );
445 --
446 hr_utility.trace('pqh_gsp_sync_compensation_obj.create_option_for_point : '
447 || l_return);
448 --
449 if l_return <> 'SUCCESS' Then
450 l_message := fnd_message.get;
451 hr_utility.trace('error message : ' || l_message);
452 fnd_message.set_name('PER','HR_289527_CRE_OPTION_FOR_POINT');
453 if l_message is not null then
454 fnd_message.set_token('ERR_CODE',l_message);
455 else
456 fnd_message.set_token('ERR_CODE','-1');
457 end if;
458 --
459 fnd_message.raise_error;
460 End if;
461 */ -- End of BUG3179239
462
463 l_return := pqh_gsp_sync_compensation_obj.create_oipl_for_step
464 (p_grade_id => l_grade_id
465 ,p_spinal_point_id => p_spinal_point_id
466 ,p_step_id => p_step_id
467 ,p_effective_date => p_effective_date
468 ,p_datetrack_mode => 'INSERT'
469 );
470 --
471 hr_utility.trace('pqh_gsp_sync_compensation_obj.oipl_for_step : '
472 || l_return);
473 --
474 if l_return <> 'SUCCESS' Then
475 l_message := fnd_message.get;
476 hr_utility.trace('error message : ' || l_message);
477 fnd_message.set_name('PER','HR_289528_CRE_OPTION_IN_PLAN');
478 if l_message is not null then
479 fnd_message.set_token('ERR_CODE',l_message);
480 else
481 fnd_message.set_token('ERR_CODE','-1');
482 end if;
483 --
484 fnd_message.raise_error;
485 End if;
486
487 end if;
488 hr_utility.set_location(' Leaving:' || l_proc, 40);
489 --
490 end first_step_api;
491
492
493 procedure chk_low_ceiling(p_val_start IN DATE,
494 p_val_end IN DATE,
495 p_gspine_id IN NUMBER,
496 p_new_ceil IN NUMBER) is
497 l_exists VARCHAR2(1);
498
499 cursor c5 is
500 select 'x'
501 from sys.dual
502 where exists
503 (select null
504 from per_all_assignments_f a,
505 per_spinal_point_placements_f p,
506 per_spinal_point_steps_f s
507 where a.assignment_id = p.assignment_id
508 and a.effective_start_date <= p_val_start
509 and a.effective_end_date >= p_val_end
510 and p.effective_start_date <= p_val_start
511 and p.effective_end_date >= p_val_end
512 and s.effective_start_date <= p_val_start
513 and s.effective_end_date >= p_val_end
514 and p.step_id = s.step_id
515 and a.special_ceiling_step_id is null
516 and s.grade_spine_id = p_gspine_id
517 and s.sequence > p_new_ceil);
518 --
519 begin
520 --
521 hr_utility.set_location('per_grade_spines_pkg.chk_low_ceiling',1);
522 --
523 open c5;
524 --
525 fetch c5 into l_exists;
526 IF c5%found THEN
527 hr_utility.set_message(801, 'PER_7935_CEIL_PLACE_HIGH_EXIST');
528 close c5;
529 hr_utility.raise_error;
530 END IF;
531 --
532 close c5;
533 --
534 end chk_low_ceiling;
535
536 --
537 --this is the the new code required to add date effective
541 --
538 --deletes to the grade spines entity
539 --KSLIPPER 16/12/94
540 --
542 --
543 -- supporting prcoedures that perform DML or specific validation
544 -- routines for controlling procedures close_gspine and open_gspine.
545 --
546 --
547 procedure zap_placement(p_placement_id in number,
548 p_step_id in number,
549 p_eff_start_date in date) is
550 --
551 -- completely removes a placement record from the DB as it starts after
552 -- the point in time where the grade spine is being ended.
553 --
554 begin
555 --
556 hr_utility.set_location('per_grade_spines_pkg.zap_placement',1);
557 --
558 delete from per_spinal_point_placements_f
559 where placement_id = p_placement_id
560 and step_id = p_step_id
561 and effective_start_date = p_eff_start_date;
562 --
563 hr_utility.set_location('per_grade_spines_pkg.zap_placement',2);
564 --
565 end zap_placement;
566
567
568 procedure update_placement(p_placement_id in number,
569 p_step_id in number,
570 p_eff_start_date in date,
571 p_newdate in date) is
572 --
573 -- performs a date effective delete/opening of the placement record setting the
574 -- EED to the date the grade spine is being ended/opened up until.
575 --
576 begin
577 --
578 hr_utility.set_location('per_grade_spines_pkg.update_placement',1);
579 --
580 update per_spinal_point_placements_f
581 set effective_end_date = p_newdate
582 where placement_id = p_placement_id
583 and step_id = p_step_id
584 and effective_start_date = p_eff_start_date;
585 --
586 hr_utility.set_location('per_grade_spines_pkg.update_placement',2);
587 --
588 end update_placement;
589
590
591 procedure zap_step(p_step_id in number,
592 p_eff_start_date in date,
593 p_grade_spine_id in number) is
594 --
595 -- completely removes a step record from the DB as it starts after the
596 -- point in time where the grade spine is being ended.
597 --
598 begin
599 --
600 hr_utility.set_location('per_grade_spines_pkg.zap_step',1);
601 --
602 delete from per_spinal_point_steps_f
603 where step_id = p_step_id
604 and grade_spine_id = p_grade_spine_id
605 and effective_start_date = p_eff_start_date;
606 --
607 hr_utility.set_location('per_grade_spines_pkg.zap_step',2);
608 --
609 end zap_step;
610
611
612 procedure update_step(p_step_id in number,
613 p_eff_start_date in date,
614 p_grade_spine_id in number,
615 p_newdate in date) is
616 --
617 -- performs a date effective delete/opening of the step record setting the EED
618 -- to the date the grade spine is being ended/opened up until.
619 --
620 begin
621 --
622 hr_utility.set_location('per_grade_spines_pkg.update_step',1);
623 --
624 update per_spinal_point_steps_f
625 set effective_end_date = p_newdate
626 where step_id = p_step_id
627 and grade_spine_id = p_grade_spine_id
628 and effective_start_date = p_eff_start_date;
629 --
630 hr_utility.set_location('per_grade_spines_pkg.update_step',2);
631 --
632 end update_step;
633
634
635 procedure get_gspine_end(p_gspine_id in number,
636 p_grade_id in number,
637 p_eff_end_date in date,
638 p_gspine_opento_date in out nocopy date) is
639 l_end_of_time date := to_date('31-12-4712','DD-MM-YYYY');
640 --
641 -- determines the date to extend the grade spine to if the grade is used
642 -- in a future grade spine.
643 --
644 -- If the grade is not used in another grade spine in the future then
645 -- the steps can be opened to the end of time.
646 --
647 cursor get_end is
648 select effective_start_date -1
649 from per_grade_spines_f
650 where grade_id = p_grade_id
651 and effective_start_date > p_eff_end_date
652 and grade_spine_id <> p_gspine_id;
653 --
654 begin
655 --
656 hr_utility.set_location('per_grade_spines_pkg.get_gspine_end',1);
657 --
658 open get_end;
659 --
660 fetch get_end into p_gspine_opento_date;
661 --
662 close get_end;
663 --
664 IF p_gspine_opento_date is null THEN
665 p_gspine_opento_date := l_end_of_time;
666 END IF;
667 --
668 hr_utility.set_location('per_grade_spines_pkg.get_gspine_end',2);
669 --
670 end get_gspine_end;
671
672
673 --
674 -- The master or controlling procedures
675 --
676
677 procedure close_gspine(p_gspine_id IN NUMBER,
678 p_sess IN DATE) is
679 l_exists NUMBER;
680 l_step_id number;
681 l_placement_id number;
682 l_stp_eff_start DATE;
683 l_stp_eff_end DATE;
684 l_plc_eff_start DATE;
685 l_plc_eff_end DATE;
686 --
687 -- cursor to check that none of the grade spines steps are in use as
688 -- special ceiling steps in assignment records.
689 --
690 -- bug# 2556700. There should not be any asignment records with end date
691 -- greater than the propposed end date of spinal point steps
692 --
693 cursor check_ass is
694 select 1
695 from per_all_assignments_f paa,
696 per_spinal_point_steps_f psps
700 --
697 where psps.grade_spine_id = p_gspine_id
698 and paa.special_ceiling_step_id = psps.step_id
699 and p_sess < paa.effective_end_date;
701 -- cursor to locate each step belonging to the grade spine ready for
702 -- updating.
703 --
704 cursor get_steps is
705 select step_id,
706 effective_start_date,
707 effective_end_date
708 from per_spinal_point_steps_f
709 where grade_spine_id = p_gspine_id;
710 --
711 cursor get_plcmnts(p_step_id in number) is
712 select placement_id,
713 effective_start_date,
714 effective_end_date
715 from per_spinal_point_placements_f
716 where step_id = p_step_id;
717 --
718 --
719 begin
720 --
721 hr_utility.set_location('per_grade_spines_pkg.close_gspine',1);
722 --
723 open check_ass;
724 --
725 fetch check_ass into l_exists;
726 IF check_ass%found THEN
727 hr_utility.set_message(801,'PER_7939_DEL_STEP_ASS');
728 close check_ass;
729 hr_utility.raise_error;
730 END IF;
731 --
732 close check_ass;
733 --
734 hr_utility.set_location('per_grade_spines_pkg.close_gspine',2);
735 --
736 open get_steps;
737 --
738 LOOP
739 --
740 -- delete/update the step records.
741 --
742 fetch get_steps into l_step_id,
743 l_stp_eff_start,
744 l_stp_eff_end;
745 exit when get_steps%notfound;
746 IF p_sess < l_stp_eff_end THEN
747 --
748 --
749 hr_utility.set_location('per_grade_spines_pkg.close_gspine',3);
750 --
751 open get_plcmnts(l_step_id);
752 --
753 -- delete/update the placement records.
754 --
755 LOOP
756 --
757 fetch get_plcmnts into l_placement_id,
758 l_plc_eff_start,
759 l_plc_eff_end;
760 exit when get_plcmnts%notfound;
761 IF l_plc_eff_start > p_sess THEN
762 zap_placement(l_placement_id,
763 l_step_id,
764 l_plc_eff_start);
765 ELSIF l_plc_eff_start <= p_sess and l_plc_eff_end >= p_sess THEN
766 update_placement(l_placement_id,
767 l_step_id,
768 l_plc_eff_start,
769 p_sess);
770 END IF;
771 --
772 END LOOP;
773 --
774 close get_plcmnts;
775 --
776 END IF;
777 --
778 IF l_stp_eff_start > p_sess THEN
779 zap_step(l_step_id,
780 l_stp_eff_start,
781 p_gspine_id);
782 ELSIF l_stp_eff_start <= p_sess and l_stp_eff_end >= p_sess THEN
783 update_step(l_step_id,
784 l_stp_eff_start,
785 p_gspine_id,
786 p_sess);
787 END IF;
788 --
789 END LOOP;
790 --
791 close get_steps;
792 --
793 end close_gspine;
794
795
796
797
798 procedure open_gspine(p_gspine_id in number,
799 p_grade_id in number,
800 p_eff_end_date in date) is
801 l_step_id per_spinal_point_steps_f.step_id%TYPE;
802 l_stp_eff_start per_spinal_point_steps_f.effective_start_date%TYPE;
803 l_plcmnt_id per_spinal_point_placements_f.placement_id%TYPE;
804 l_plc_eff_start per_spinal_point_placements_f.effective_start_date%TYPE;
805 l_gspine_opento_date date;
806 l_stp_opento_date date;
807 l_plc_opento_date date;
808 l_eot date := to_date('31-12-4712','DD-MM-YYYY');
809 l_grdchng_date date;
810 l_ass_maxend date;
811 l_exists number;
812 --
813 -- cursor to check that the grade spine has been closed and does require
814 -- opening up. If no rows returned then records end is just a ceiling
815 -- step change.
816 --
817 cursor check_notceilchng is
818 select 1
819 from per_grade_spines_f
820 where grade_spine_id = p_gspine_id
821 and effective_start_date > p_eff_end_date;
822 --
823 -- cursor to locate the spinal point steps that require re-opening.
824 --
825 cursor get_steps is
826 select step_id,
827 effective_start_date
828 from per_spinal_point_steps_f
829 where grade_spine_id = p_gspine_id
830 and effective_end_date = p_eff_end_date;
831 --
832 -- cursor to locate the spinal point placements that require
833 -- re-opening.
834 --
835 cursor get_placements(p_step_id in number) is
836 select placement_id,
837 effective_start_date
838 from per_spinal_point_placements_f
839 where step_id = p_step_id
840 and effective_end_date = p_eff_end_date;
841 --
842 -- cursor to determine the date to extend the placement records to if
843 -- the grade of the assignment has changed.
844 --
845 cursor get_plcdate(p_plc_id in number,
846 p_grd_id in number,
847 p_plc_start in date) is
848 select a.effective_start_date -1
849 from per_spinal_point_placements_f p,
850 per_all_assignments_f a
851 where p.placement_id = p_plc_id
852 and p.effective_start_date = p_plc_start
853 and a.assignment_id = p.assignment_id
854 and a.effective_start_date > p_plc_start
855 and a.grade_id <> p_grd_id;
856 --
857 -- cursor to check if assignment has been ended in between the closing
858 -- and reopening of the placement.
859 --
860 cursor get_assend(p_plc_id in number) is
864 where p.placement_id = p_plc_id
861 select max(a.effective_end_date)
862 from per_spinal_point_placements_f p,
863 per_all_assignments_f a
865 and p.assignment_id = a.assignment_id;
866 --
867 --
868 begin
869 --
870 hr_utility.set_location('per_grade_spines_pkg.open_gspine',1);
871 --
872 open check_notceilchng;
873 --
874 fetch check_notceilchng into l_exists;
875 IF check_notceilchng%notfound THEN
876 close check_notceilchng;
877 --
878 hr_utility.set_location('per_grade_spines_pkg.open_gspine',2);
879 --
880 per_grade_spines_pkg.get_gspine_end(p_gspine_id,
881 p_grade_id,
882 p_eff_end_date,
883 l_gspine_opento_date);
884 --
885 l_stp_opento_date := nvl(l_gspine_opento_date,l_eot);
886 --
887 hr_utility.set_location('per_grade_spines_pkg.open_gspine',3);
888 --
889 FOR steps IN get_steps LOOP
890 --
891 -- re-open step records for grade spine
892 --
893 l_step_id := steps.step_id;
894 l_stp_eff_start := steps.effective_start_date;
895 --
896 hr_utility.set_location('per_grade_spines_pkg.open_gspine',4);
897 --
898 FOR placements IN get_placements(l_step_id) LOOP
899 --
900 -- re-open placement records for each step re-opened
901 --
902 l_plcmnt_id := placements.placement_id;
903 l_plc_eff_start := placements.effective_start_date;
904 --
905 -- check if assignment has been ended
906 --
907 hr_utility.set_location('per_grade_spines_pkg.open_gspine',5);
908 --
909 open get_assend(l_plcmnt_id);
910 fetch get_assend into l_ass_maxend;
911 close get_assend;
912 --
913 -- check if assignments grade has been changed
914 --
915 hr_utility.set_location('per_grade_spines_pkg.open_gspine',6);
916 --
917 open get_plcdate(l_plcmnt_id,
918 p_grade_id,
919 l_plc_eff_start);
920 fetch get_plcdate into l_grdchng_date;
921 close get_plcdate;
922 --
923 -- locate the maximum date the placement can be opened until
924 --
925 l_plc_opento_date :=
926 least(l_stp_opento_date,nvl(l_grdchng_date,l_eot),
927 nvl(l_ass_maxend,l_eot));
928 --
929 -- perform the update
930 --
931 update_placement(l_plcmnt_id,
932 l_step_id,
933 l_plc_eff_start,
934 l_plc_opento_date);
935 --
936 END LOOP;
937 --
938 -- perform the update for the step record
939 --
940 update_step(l_step_id,
941 l_stp_eff_start,
942 p_gspine_id,
943 l_stp_opento_date);
944 --
945 END LOOP;
946 --
947 ELSE close check_notceilchng;
948 --
949 END IF;
950 --
951 end open_gspine;
952
953
954 end PER_GRADE_SPINES_PKG;
[Home] [Help]