[Home] [Help]
PACKAGE BODY: APPS.PER_PGS_BUS
Source
1 Package Body per_pgs_bus as
2 /* $Header: pepgsrhi.pkb 120.0 2005/05/31 14:12:49 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pgs_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_grade_spine_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_grade_spine_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , per_grade_spines_f pgs
32 where pgs.grade_spine_id = p_grade_spine_id
33 and pbg.business_group_id = pgs.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 l_legislation_code varchar2(150);
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'grade_spine_id'
50 ,p_argument_value => p_grade_spine_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id
55 , l_legislation_code;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
66 => nvl(p_associated_column1,'GRADE_SPINE_ID')
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
67 );
68 --
69 else
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 -- Set the sessions legislation context in HR_SESSION_DATA
79 --
80 hr_api.set_legislation_context(l_legislation_code);
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_grade_spine_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100 from per_business_groups_perf pbg
101 , per_grade_spines_f pgs
102 where pgs.grade_spine_id = p_grade_spine_id
103 and pbg.business_group_id = pgs.business_group_id;
104 --
105 -- Declare local variables
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'grade_spine_id'
119 ,p_argument_value => p_grade_spine_id
120 );
121 --
122 if ( nvl(per_pgs_bus.g_grade_spine_id, hr_api.g_number)
123 = p_grade_spine_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 l_legislation_code := per_pgs_bus.g_legislation_code;
130 hr_utility.set_location(l_proc, 20);
131 else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 open csr_leg_code;
137 fetch csr_leg_code into l_legislation_code;
138 --
139 if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 close csr_leg_code;
144 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 fnd_message.raise_error;
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 per_pgs_bus.g_grade_spine_id := p_grade_spine_id;
154 per_pgs_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |---------------------------< chk_grade_spine_id >-------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- Description
165 -- This procedure is used to check that the primary key for the table
166 -- is created properly. It should be null on insert and
167 -- should not be able to be updated.
168 --
169 -- Pre Conditions
170 -- None.
171 --
172 -- In Parameters
173 -- grade_spine_id PK of record being inserted or updated.
174 -- object_version_number Object version number of record being
175 -- inserted or updated.
176 -- p_effective_date
177 --
178 -- Post Success
179 -- Processing continues
180 --
181 -- Post Failure
182 -- Errors handled by the procedure
183 --
184 -- Access Status
185 -- Internal table handler use only.
186 --
187 --
188 -- {End Of Comments}
189 -- ----------------------------------------------------------------------------
190 Procedure chk_grade_spine_id
191 ( p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
192 ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
193 ,p_effective_date in date
194 ) is
195 --
196 l_proc varchar2(72) := g_package||'chk_grade_spine_id';
197 l_api_updating boolean;
198 --
199 Begin
200 hr_utility.set_location('Entering:'||l_proc, 10);
201 --
202 l_api_updating := per_pgs_shd.api_updating
203 (p_effective_date => p_effective_date
204 ,p_grade_spine_id => p_grade_spine_id
205 ,p_object_version_number => p_object_version_number
206 );
207 --
208 if (l_api_updating
209 and nvl(p_grade_spine_id,hr_api.g_number)
210 <> per_pgs_shd.g_old_rec.grade_spine_id) then
211 --
212 -- raise error as PK has changed
213 --
214 per_pgs_shd.constraint_error('PER_GRADE_SPINES_F_PK');
215 --
216 elsif not l_api_updating then
220 if p_grade_spine_id is not null then
217 --
218 -- check if PK is null
219 --
221 --
222 -- raise error as PK is not null
223 --
224 per_pgs_shd.constraint_error('PER_GRADE_SPINES_F_PK');
225 --
226 end if;
227 --
228 end if;
229 --
230 hr_utility.set_location('Leaving:'||l_proc, 20);
231 --
232 End chk_grade_spine_id;
233 --
234 --
235 -- ---------------------------------------------------------------------------
236 -- |-----------------< chk_parent_spine_id >---------------------------------|
237 -- ---------------------------------------------------------------------------
238 --
239 -- Description:
240 -- Validates that a parent_spine_id is mandatory and
241 -- exists in table per_parent_spines.
242 --
243 -- Pre-conditions:
244 -- parent_spine_id must be valid.
245 -- business_group_id must be valid.
246 -- effective_date must be valid.
247 --
248 -- In Arguments:
249 -- p_grade_spine_id
250 -- p_parent_spine_id
251 -- p_business_group_id
252 -- p_object_version_number
253 -- p_effective_date
254 --
255 -- Post Success:
256 -- Processing continues
257 --
258 -- Post Failure:
259 -- Errors handled by the procedure
260 --
261 -- Access Status:
262 -- Internal Table Handler Use Only.
263 --
264 -- {End Of Comments}
265 -- ----------------------------------------------------------------------------
266 --
267 procedure chk_parent_spine_id
268 (p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
269 ,p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
270 ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
271 ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
272 ,p_effective_date in date
273 )
274 is
275 --
276 l_exists varchar2(1);
277 l_proc varchar2(72) := g_package||'chk_parent_spine_id';
278 l_api_updating boolean;
279 --
280 cursor csr_valid_parent_spines is
281 select null
282 from per_parent_spines pps
283 where pps.business_group_id = p_business_group_id
284 and pps.parent_spine_id = p_parent_spine_id;
285 --
286 begin
287
288 hr_utility.set_location('Entering:'|| l_proc, 10);
289 --
290 -- Check mandatory parameters have been set
291 --
292 hr_api.mandatory_arg_error
293 (p_api_name => l_proc
294 ,p_argument => 'parent_spine_id'
295 ,p_argument_value => p_parent_spine_id
296 );
297
298 --
299 -- Only proceed with validation if :
300 -- a) The current g_old_rec is current and
301 -- b) The value for parent_spine_id has changed
302 --
303 l_api_updating := per_pgs_shd.api_updating
304 (p_effective_date => p_effective_date
305 ,p_grade_spine_id => p_grade_spine_id
306 ,p_object_version_number => p_object_version_number);
307 --
308 if (l_api_updating and nvl(per_pgs_shd.g_old_rec.parent_spine_id,
309 hr_api.g_number) = nvl(p_parent_spine_id, hr_api.g_number)) then
310 return;
311 end if;
312
313 open csr_valid_parent_spines;
314 fetch csr_valid_parent_spines into l_exists;
315 if csr_valid_parent_spines%notfound then
316 --
317 per_pgs_shd.constraint_error(p_constraint_name => 'PER_GRADE_SPINES_F_FK2');
318 --
319 end if;
320 close csr_valid_parent_spines;
321
322 hr_utility.set_location(' Leaving:'|| l_proc, 20);
323
324 end chk_parent_spine_id;
325 --
326 -- ----------------------------------------------------------------------------
327 -- |-------------------------< chk_grade_id >---------------------------------|
328 -- ----------------------------------------------------------------------------
329 --
330 -- Description
331 -- This procedure is used to check that the grade_id is mandatory,
332 -- exists in per_grades table
333 -- and effective_date between date_from and date_to.
334 --
335 -- Pre Conditions
336 -- None.
337 --
338 -- In Parameters
339 -- grade_id
340 -- grade_spine_id
341 -- business_group_id
342 -- object_version_number
343 -- effective_date
344 --
345 -- Post Success
346 -- Processing continues
347 --
348 -- Post Failure
349 -- Errors handled by the procedure
350 --
351 -- Access Status
352 -- Internal table handler use only.
353 --
354 --
355 -- {End Of Comments}
356 -- ----------------------------------------------------------------------------
357 Procedure chk_grade_id(
358 p_grade_id in per_grade_spines_f.grade_id%TYPE
359 ,p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
360 ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
361 ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
362 ,p_effective_date in date
363 ) is
364 --
365 l_proc varchar2(72) := g_package||'chk_grade_id';
366 l_api_updating boolean;
367 l_exists varchar2(1);
368 --
369
370 cursor csr_valid_grade_id is
371 select null
375 and p_effective_date between pg.date_from
372 from per_grades pg
373 where pg.grade_id = p_grade_id
374 and business_group_id = p_business_group_id
376 and nvl(pg.date_to, hr_api.g_eot);
377 --
378 Begin
379 --
380 hr_utility.set_location('Entering:'||l_proc, 10);
381
382 --
383 --
384 -- Check mandatory parameters have been set
385 --
386 hr_api.mandatory_arg_error
387 (p_api_name => l_proc
388 ,p_argument => 'grade_id'
389 ,p_argument_value => p_grade_id
390 );
391 --
392 -- Only proceed with validation if :
393 -- a) The current g_old_rec is current and
394 -- b) The value for grade_id has changed
395 --
396 l_api_updating := per_pgs_shd.api_updating
397 (p_effective_date => p_effective_date
398 ,p_grade_spine_id => p_grade_spine_id
399 ,p_object_version_number => p_object_version_number
400 );
401 --
402 if (l_api_updating and nvl(per_pgs_shd.g_old_rec.grade_id,
403 hr_api.g_number) = nvl(p_grade_id, hr_api.g_number)) then
404 return;
405 end if;
406
407 hr_utility.set_location(l_proc, 20);
408
409 --
410 -- Check that the grade_id should exist in per_grades and has the business group
411 --
412 open csr_valid_grade_id;
413 fetch csr_valid_grade_id into l_exists;
414 if csr_valid_grade_id%notfound then
415 close csr_valid_grade_id;
416 per_pgs_shd.constraint_error(p_constraint_name => 'PER_GRADE_SPINES_F_FK3');
417 hr_utility.raise_error;
418 end if;
419 close csr_valid_grade_id;
420 --
421 hr_utility.set_location(' Leaving:'||l_proc, 30);
422 --
423 End chk_grade_id;
424 --
425 -- ---------------------------------------------------------------------------
426 -- |-----------------< chk_ceiling_step_id >---------------------------------|
427 -- ---------------------------------------------------------------------------
428 --
429 -- Description:
430 -- If ceiling_step_id is not NULL, it must
431 -- exist in table per_spinal_point_steps_f.
432 --
433 -- Pre-conditions:
434 -- parent_spine_id must be valid.
435 -- business_group_id must be valid.
436 -- effective_date must be valid.
437 --
438 -- In Arguments:
439 -- p_ceiling_step_id
440 -- p_grade_spine_id
441 -- p_business_group_id
442 -- p_object_version_number
443 -- p_effective_date
444 --
445 -- Post Success:
446 -- Processing continues
447 --
448 -- Post Failure:
449 -- Errors handled by the procedure
450 --
451 -- Access Status:
452 -- Internal Table Handler Use Only.
453 --
454 -- {End Of Comments}
455 -- ----------------------------------------------------------------------------
456 --
457 procedure chk_ceiling_step_id
458 (p_ceiling_step_id in per_grade_spines_f.ceiling_step_id%TYPE
459 ,p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
460 ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
461 ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
462 ,p_effective_date in date
463 )
464 is
465 --
466 l_proc varchar2(72) := g_package||'chk_ceiling_step_id';
467 l_api_updating boolean;
468 l_exists varchar2(1);
469 l_ceiling_step_id number;
470 --
471 /*
472 cursor csr_valid_parent_spine is
473 select null
474 from per_spinal_points psp
475 where psp.business_group_id = p_business_group_id
476 and psp.spinal_point = p_ceiling_step_id
477 and psp.parent_spine_id = p_rec.parent_spine_id;
478 */
479
480 cursor csr_valid_spinal_point_step is
481 select null
482 from per_spinal_point_steps_f
483 where business_group_id = p_business_group_id
484 and step_id = p_ceiling_step_id
485 and p_effective_date between effective_start_date
486 and effective_end_date;
487
488 cursor csr_next_ceiling_step_id is
489 select per_spinal_point_steps_s.nextval
490 from sys.dual;
491 --
492 begin
493 hr_utility.set_location('Entering:'|| l_proc, 10);
494
495 if p_ceiling_step_id is not NULL then
496 --
497 -- Only proceed with validation if :
498 -- a) The current g_old_rec is current and
499 -- b) The value for ceiling_step_id has changed
500 --
501 l_api_updating := per_pgs_shd.api_updating
502 (p_effective_date => p_effective_date
503 ,p_grade_spine_id => p_grade_spine_id
504 ,p_object_version_number => p_object_version_number);
505 --
506 if (l_api_updating) then
507 if nvl(per_pgs_shd.g_old_rec.ceiling_step_id, hr_api.g_number)
508 = nvl(p_ceiling_step_id, hr_api.g_number) then
509 return;
510 else
511
512 hr_utility.set_location(l_proc, 20);
513
514 /* skip mandatory parameter check BUG3389808
515 --
516 -- Check mandatory parameters have been set
517 --
518 hr_api.mandatory_arg_error
519 (p_api_name => l_proc
520 ,p_argument => 'ceiling_step_id'
521 ,p_argument_value => p_ceiling_step_id
525 /*
522 );
523 */
524
526 --
527 -- This validation was replaced by csr_valid_spinal_point_step
528 --
529 open csr_valid_parent_spine;
530 fetch csr_valid_parent_spine into l_exists;
531 if csr_valid_parent_spine%notfound then
532 close csr_valid_parent_spine;
533 --
534 hr_utility.set_message(800, 'HR_289687_SPINAL_POINT_INV');
535 hr_utility.raise_error;
536 --
537 end if;
538 close csr_valid_parent_spine;
539 */
540
541 open csr_valid_spinal_point_step;
542 fetch csr_valid_spinal_point_step into l_exists;
543 if csr_valid_spinal_point_step%notfound then
544 close csr_valid_spinal_point_step;
545 --
546 hr_utility.set_message(800, 'HR_289567_CEILING_STEP_INVALID');
547 hr_utility.raise_error;
548 --
549 end if;
550 close csr_valid_spinal_point_step;
551 end if;
552 else
553
554 hr_utility.set_location(l_proc, 30);
555
556 --
557 hr_utility.set_location(l_proc, 40);
558 --
559 /*
560 --
561 -- This validation was replaced by csr_valid_spinal_point_step
562 --
563 open csr_valid_parent_spine;
564 fetch csr_valid_parent_spine into l_exists;
565 if csr_valid_parent_spine%notfound then
566 close csr_valid_parent_spine;
567 --
568 hr_utility.set_message(800, 'HR_289687_SPINAL_POINT_INV');
569 hr_utility.raise_error;
570 --
571 end if;
572 close csr_valid_parent_spine;
573 */
574
575 open csr_valid_spinal_point_step;
576 fetch csr_valid_spinal_point_step into l_exists;
577 if csr_valid_spinal_point_step%notfound then
578 close csr_valid_spinal_point_step;
579 --
580 hr_utility.set_message(800, 'HR_289567_CEILING_STEP_INVALID');
581 hr_utility.raise_error;
582 --
583 end if;
584 close csr_valid_spinal_point_step;
585 end if;
586 end if;
587
588 hr_utility.set_location(' Leaving:'|| l_proc, 60);
589
590 end chk_ceiling_step_id;
591
592 --
593 -- ---------------------------------------------------------------------------
594 -- |-----------------< chk_uniq_grade_pay_scale >----------------------------|
595 -- ---------------------------------------------------------------------------
596 --
597 -- Description:
598 -- Validates that combination fo grade_id and parent_spine_id is unique.
599 --
600 -- Pre-conditions:
601 -- parent_spine_id must be valid.
602 -- business_group_id must be valid.
603 -- effective_date must be valid.
604 --
605 -- In Arguments:
606 -- p_grade_spine_id
607 -- p_grade_id
608 -- p_parent_spine_id
609 -- p_business_group_id
610 -- p_object_version_number
611 -- p_effective_date
612 --
613 -- Post Success:
614 -- If the combination is unique; processing continues.
615 --
616 -- Post Failure:
617 -- If the combination exists in per_grade_spines_f,
618 -- then an error will be raised and processing terminated.
619 --
620 -- Access Status:
621 -- Internal Table Handler Use Only.
622 --
623 -- {End Of Comments}
624 -- ----------------------------------------------------------------------------
625 --
626 procedure chk_uniq_grade_pay_scale
627 (p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
628 ,p_grade_id in per_grade_spines_f.grade_id%TYPE
629 ,p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
630 ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
631 ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
632 ,p_effective_date in date
633 )
634 is
635 --
636 l_proc varchar2(72) := g_package||'chk_uniq_grade_pay_scale';
637 l_api_updating boolean;
638 l_exists varchar2(1);
639 --
640 cursor csr_uniq_grade_pay_scale is
641 select null
642 from per_grade_spines_f
643 where business_group_id = p_business_group_id
644 and grade_id = p_grade_id
645 and parent_spine_id = p_parent_spine_id
646 and p_effective_date
647 between effective_start_date and
648 effective_end_date ;
649 --
650 begin
651
652 hr_utility.set_location('Entering:'|| l_proc, 10);
653 --
654 -- Only proceed with validation if :
655 -- a) The current g_old_rec is current and
656 -- b) The value for grade_id and parent_spine_id have changed
657 --
658 l_api_updating := per_pgs_shd.api_updating
659 (p_effective_date => p_effective_date
660 ,p_grade_spine_id => p_grade_spine_id
661 ,p_object_version_number => p_object_version_number);
662 --
663 if (l_api_updating
664 and nvl(per_pgs_shd.g_old_rec.grade_id,
665 hr_api.g_number) = nvl(p_grade_id, hr_api.g_number)
666 and nvl(per_pgs_shd.g_old_rec.parent_spine_id,hr_api.g_number)
667 = nvl(p_parent_spine_id, hr_api.g_number)
668 ) then
669 return;
670 end if;
671
672 hr_utility.set_location(l_proc, 20);
673
674 open csr_uniq_grade_pay_scale;
675 fetch csr_uniq_grade_pay_scale into l_exists;
676 if csr_uniq_grade_pay_scale%found then
680 hr_utility.raise_error;
677 close csr_uniq_grade_pay_scale;
678 --
679 hr_utility.set_message(800, 'PER_7932_GRDSPN_GRD_EXISTS');
681 --
682 end if;
683 close csr_uniq_grade_pay_scale;
684
685 hr_utility.set_location(' Leaving:'|| l_proc, 30);
686
687 end chk_uniq_grade_pay_scale;
688
689 --
690 --
691 -- ---------------------------------------------------------------------------
692 -- |---------------------------< chk_delete >--------------------------------|
693 -- ---------------------------------------------------------------------------
694 --
695 -- Description
696 -- This procedure is used to check that there are no values in
697 -- per_spinal_point_steps_f, per_spinal_point_placement_f and
698 -- per_all_assignments_f.
699 --
700 -- Pre Conditions
701 -- None.
702 --
703 -- In Parameters
704 -- p_parent_spine_id
705 -- p_grade_id
706 --
707 -- Post Success
708 -- Processing continues
709 --
710 -- Post Failure
711 -- Errors handled by the procedure
712 --
713 -- Access Status
714 -- Internal table handler use only.
715 --
716 --
717 -- {End Of Comments}
718 -- ----------------------------------------------------------------------------
719 --
720 PROCEDURE chk_delete(
721 p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
722 ,p_grade_id in per_grade_spines_f.grade_id%TYPE
723 ) is
724 --
725 l_proc varchar2(72) := g_package||'chk_delete';
726 l_exists varchar2(1);
727 --
728 cursor csr_spinal_point_placements is
729 select 'x'
730 from per_spinal_point_steps_f sps
731 ,per_grade_spines_f gs
732 where gs.grade_spine_id = sps.grade_spine_id
733 and gs.parent_spine_id = p_parent_spine_id
734 and gs.grade_id = p_grade_id
735 and exists
736 (select null
737 from per_spinal_point_placements_f sp
738 where sp.step_id = sps.step_id);
739
740 cursor csr_assignments is
741 select 'x'
742 from per_spinal_point_steps_f sps
743 ,per_grade_spines_f gs
744 where gs.grade_spine_id = sps.grade_spine_id
745 and gs.parent_spine_id = p_parent_spine_id
746 and gs.grade_id = p_grade_id
747 and exists
748 (select null
749 from per_assignments_f a
750 where a.special_ceiling_step_id = sps.step_id
751 and a.special_ceiling_step_id is not null);
752
753 --
754 Begin
755 --
756 hr_utility.set_location('Entering:'||l_proc, 10);
757 --
758 -- Check there are no values in per_spinal_point_steps_f
759 -- and per_spinal_point_placements_f
760 --
761 open csr_spinal_point_placements;
762 --
763 fetch csr_spinal_point_placements into l_exists;
764 --
765 If csr_spinal_point_placements%found Then
766 --
767 close csr_spinal_point_placements;
768 --
769 hr_utility.set_message(801, 'PER_7933_DEL_GRDSPN_PLACE');
770 hr_utility.raise_error;
771 --
772 End If;
773 --
774 close csr_spinal_point_placements;
775
776 hr_utility.set_location(l_proc, 20);
777
778 --
779 -- Check there are no values in per_all_assignments_f
780 --
781 open csr_assignments;
782 fetch csr_assignments into l_exists;
783
784 If csr_assignments%found Then
785 --
786 close csr_assignments;
787 --
788 hr_utility.set_message(801, 'PER_7934_DEL_GRDSPN_ASS');
789 hr_utility.raise_error;
790 --
791 End If;
792 --
793 close csr_assignments;
794 --
795 hr_utility.set_location(' Leaving:' || l_proc, 30);
796 --
797 end chk_delete;
798 --
799 -- ----------------------------------------------------------------------------
800 -- |-----------------------< chk_non_updateable_args >------------------------|
801 -- ----------------------------------------------------------------------------
802 -- {Start Of Comments}
803 --
804 -- Description:
805 -- This procedure is used to ensure that non updateable attributes have
806 -- not been updated. If an attribute has been updated an error is generated.
807 --
808 -- Pre Conditions:
809 -- g_old_rec has been populated with details of the values currently in
810 -- the database.
811 --
812 -- In Arguments:
813 -- p_rec has been populated with the updated values the user would like the
814 -- record set to.
815 --
816 -- Post Success:
817 -- Processing continues if all the non updateable attributes have not
818 -- changed.
819 --
820 -- Post Failure:
821 -- An application error is raised if any of the non updatable attributes
822 -- have been altered.
823 --
824 -- {End Of Comments}
825 -- ----------------------------------------------------------------------------
826 Procedure chk_non_updateable_args
827 (p_effective_date in date
828 ,p_rec in per_pgs_shd.g_rec_type
829 ) IS
830 --
831 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
832 --
833 Begin
834 --
835 -- Only proceed with the validation if a row exists for the current
836 -- record in the HR Schema.
837 --
838 IF NOT per_pgs_shd.api_updating
839 (p_grade_spine_id => p_rec.grade_spine_id
840 ,p_effective_date => p_effective_date
844 fnd_message.set_token('PROCEDURE ', l_proc);
841 ,p_object_version_number => p_rec.object_version_number
842 ) THEN
843 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
845 fnd_message.set_token('STEP ', '5');
846 fnd_message.raise_error;
847 END IF;
848
849 --
850 -- Check business_group_id is not updated
851 --
852 IF nvl(p_rec.business_group_id, hr_api.g_number) <>
853 per_pgs_shd.g_old_rec.business_group_id then
854 hr_api.argument_changed_error
855 (p_api_name => l_proc
856 ,p_argument => 'BUSINESS_GROUP_ID'
857 ,p_base_table => per_pgs_shd.g_tab_nam
858 );
859 END IF;
860
861 --
862 -- Check parent_spine_id is not updated
863 --
864 IF nvl(p_rec.parent_spine_id, hr_api.g_number) <>
865 per_pgs_shd.g_old_rec.parent_spine_id then
866 hr_api.argument_changed_error
867 (p_api_name => l_proc
868 ,p_argument => 'PARENT_SPINE_ID'
869 ,p_base_table => per_pgs_shd.g_tab_nam
870 );
871 END IF;
872
873 --
874 -- Check grade_id is not updated
875 --
876 IF nvl(p_rec.grade_id, hr_api.g_number) <>
877 per_pgs_shd.g_old_rec.grade_id then
878 hr_api.argument_changed_error
879 (p_api_name => l_proc
880 ,p_argument => 'GRADE_ID'
881 ,p_base_table => per_pgs_shd.g_tab_nam
882 );
883 end if;
884
885 End chk_non_updateable_args;
886 --
887 -- ----------------------------------------------------------------------------
888 -- |--------------------------< dt_update_validate >--------------------------|
889 -- ----------------------------------------------------------------------------
890 -- {Start Of Comments}
891 --
892 -- Description:
893 -- This procedure is used for referential integrity of datetracked
894 -- parent entities when a datetrack update operation is taking place
895 -- and where there is no cascading of update defined for this entity.
896 --
897 -- Prerequisites:
898 -- This procedure is called from the update_validate.
899 --
900 -- In Parameters:
901 --
902 -- Post Success:
903 -- Processing continues.
904 --
905 -- Post Failure:
906 --
907 -- Developer Implementation Notes:
908 -- This procedure should not need maintenance unless the HR Schema model
909 -- changes.
910 --
911 -- Access Status:
912 -- Internal Row Handler Use Only.
913 --
914 -- {End Of Comments}
915 -- ----------------------------------------------------------------------------
916 Procedure dt_update_validate
917 (p_datetrack_mode in varchar2
918 ,p_validation_start_date in date
919 ,p_validation_end_date in date
920 ) Is
921 --
922 l_proc varchar2(72) := g_package||'dt_update_validate';
923 --
924 Begin
925 --
926 -- Ensure that the p_datetrack_mode argument is not null
927 --
928 hr_api.mandatory_arg_error
929 (p_api_name => l_proc
930 ,p_argument => 'datetrack_mode'
931 ,p_argument_value => p_datetrack_mode
932 );
933 --
934 -- Mode will be valid, as this is checked at the start of the upd.
935 --
936 -- Ensure the arguments are not null
937 --
938 hr_api.mandatory_arg_error
939 (p_api_name => l_proc
940 ,p_argument => 'validation_start_date'
941 ,p_argument_value => p_validation_start_date
942 );
943 --
944 hr_api.mandatory_arg_error
945 (p_api_name => l_proc
946 ,p_argument => 'validation_end_date'
947 ,p_argument_value => p_validation_end_date
948 );
949 --
950 --
951 --
952 Exception
953 When Others Then
954 --
955 -- An unhandled or unexpected error has occurred which
956 -- we must report
957 --
958 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
959 fnd_message.set_token('PROCEDURE', l_proc);
960 fnd_message.set_token('STEP','15');
961 fnd_message.raise_error;
962 End dt_update_validate;
963 --
964 -- ----------------------------------------------------------------------------
965 -- |--------------------------< dt_delete_validate >--------------------------|
966 -- ----------------------------------------------------------------------------
967 -- {Start Of Comments}
968 --
969 -- Description:
970 -- This procedure is used for referential integrity of datetracked
971 -- child entities when either a datetrack DELETE or ZAP is in operation
972 -- and where there is no cascading of delete defined for this entity.
973 -- For the datetrack mode of DELETE or ZAP we must ensure that no
974 -- datetracked child rows exist between the validation start and end
975 -- dates.
976 --
977 -- Prerequisites:
978 -- This procedure is called from the delete_validate.
979 --
980 -- In Parameters:
981 --
982 -- Post Success:
983 -- Processing continues.
984 --
985 -- Post Failure:
986 -- If a row exists by determining the returning Boolean value from the
987 -- generic dt_api.rows_exist function then we must supply an error via
988 -- the use of the local exception handler l_rows_exist.
989 --
990 -- Developer Implementation Notes:
991 -- This procedure should not need maintenance unless the HR Schema model
992 -- changes.
993 --
994 -- Access Status:
995 -- Internal Row Handler Use Only.
996 --
1000 (p_grade_spine_id in number
997 -- {End Of Comments}
998 -- ----------------------------------------------------------------------------
999 Procedure dt_delete_validate
1001 ,p_datetrack_mode in varchar2
1002 ,p_validation_start_date in date
1003 ,p_validation_end_date in date
1004 ) Is
1005 --
1006 l_proc varchar2(72) := g_package||'dt_delete_validate';
1007 --
1008 Begin
1009 --
1010 -- Ensure that the p_datetrack_mode argument is not null
1011 --
1012 hr_api.mandatory_arg_error
1013 (p_api_name => l_proc
1014 ,p_argument => 'datetrack_mode'
1015 ,p_argument_value => p_datetrack_mode
1016 );
1017 --
1018 -- Only perform the validation if the datetrack mode is either
1019 -- DELETE or ZAP
1020 --
1021 If (p_datetrack_mode = hr_api.g_delete or
1022 p_datetrack_mode = hr_api.g_zap) then
1023 --
1024 --
1025 -- Ensure the arguments are not null
1026 --
1027 hr_api.mandatory_arg_error
1028 (p_api_name => l_proc
1029 ,p_argument => 'validation_start_date'
1030 ,p_argument_value => p_validation_start_date
1031 );
1032 --
1033 hr_api.mandatory_arg_error
1034 (p_api_name => l_proc
1035 ,p_argument => 'validation_end_date'
1036 ,p_argument_value => p_validation_end_date
1037 );
1038 --
1039 hr_api.mandatory_arg_error
1040 (p_api_name => l_proc
1041 ,p_argument => 'grade_spine_id'
1042 ,p_argument_value => p_grade_spine_id
1043 );
1044
1045 --
1046 -- when validate_mode is ZAP, the following validation isn't required.
1047 --
1048 /*
1049 If (dt_api.rows_exist
1050 (p_base_table_name => 'per_spinal_point_steps_f'
1051 ,p_base_key_column => 'grade_spine_id'
1052 ,p_base_key_value => p_grade_spine_id
1053 ,p_from_date => p_validation_start_date
1054 ,p_to_date => p_validation_end_date
1055 )) Then
1056 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
1057 fnd_message.set_token('TABLE_NAME','spinal point steps');
1058 hr_multi_message.add;
1059 End If;
1060 If (dt_api.rows_exist
1061 (p_base_table_name => 'per_cagr_entitlement_lines_f'
1062 ,p_base_key_column => 'grade_spine_id'
1063 ,p_base_key_value => p_grade_spine_id
1064 ,p_from_date => p_validation_start_date
1065 ,p_to_date => p_validation_end_date
1066 )) Then
1067 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
1068 fnd_message.set_token('TABLE_NAME','cagr entitlement lines');
1069 hr_multi_message.add;
1070 End If;
1071 */
1072 --
1073 End If;
1074 --
1075 Exception
1076 When Others Then
1077 --
1078 -- An unhandled or unexpected error has occurred which
1079 -- we must report
1080 --
1081 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1082 fnd_message.set_token('PROCEDURE', l_proc);
1083 fnd_message.set_token('STEP','15');
1084 fnd_message.raise_error;
1085 --
1086 End dt_delete_validate;
1087 --
1088 -- ----------------------------------------------------------------------------
1089 -- |---------------------------< insert_validate >----------------------------|
1090 -- ----------------------------------------------------------------------------
1091 Procedure insert_validate
1092 (p_rec in out nocopy per_pgs_shd.g_rec_type
1093 ,p_effective_date in date
1094 ,p_datetrack_mode in varchar2
1095 ,p_validation_start_date in date
1096 ,p_validation_end_date in date
1097 ) is
1098 --
1099 l_proc varchar2(72) := g_package||'insert_validate';
1100 --
1101 Begin
1102 hr_utility.set_location('Entering:'||l_proc, 10);
1103 --
1104 -- Call all supporting business operations
1105 --
1106 hr_api.validate_bus_grp_id
1107 (p_business_group_id => p_rec.business_group_id
1108 ,p_associated_column1 => per_pgs_shd.g_tab_nam
1109 || '.BUSINESS_GROUP_ID');
1110
1111 --
1112 -- Validate grade spine id
1113 --
1114 chk_grade_spine_id
1115 (p_grade_spine_id => p_rec.grade_spine_id
1116 ,p_object_version_number => p_rec.object_version_number
1117 ,p_effective_date => p_effective_date
1118 ) ;
1119
1120 hr_utility.set_location(l_proc, 20);
1121
1122 --
1123 -- After validating the set of important attributes,
1124 -- if Multiple Message detection is enabled and at least
1125 -- one error has been found then abort further validation.
1126 --
1127 hr_multi_message.end_validation_set;
1128
1129 --
1130 -- Validate Dependent Attributes
1131 --
1132 --
1133
1134 --
1135 -- Validate parent spine id
1136 --
1137 chk_parent_spine_id
1138 (p_grade_spine_id => p_rec.grade_spine_id
1139 ,p_parent_spine_id => p_rec.parent_spine_id
1140 ,p_business_group_id => p_rec.business_group_id
1141 ,p_object_version_number => p_rec.object_version_number
1142 ,p_effective_date => p_effective_date
1143 );
1144
1145 hr_utility.set_location(l_proc, 30);
1146
1147 --
1148 -- Validate grade id
1149 --
1150 chk_grade_id
1154 ,p_object_version_number => p_rec.object_version_number
1151 (p_grade_id => p_rec.grade_id
1152 ,p_grade_spine_id => p_rec.grade_spine_id
1153 ,p_business_group_id => p_rec.business_group_id
1155 ,p_effective_date => p_effective_date
1156 ) ;
1157
1158 hr_utility.set_location(l_proc, 40);
1159
1160 --
1161 -- Validate ceiling step id
1162 --
1163 chk_ceiling_step_id
1164 (p_ceiling_step_id => p_rec.ceiling_step_id
1165 ,p_grade_spine_id => p_rec.grade_spine_id
1166 ,p_business_group_id => p_rec.business_group_id
1167 ,p_object_version_number => p_rec.object_version_number
1168 ,p_effective_date => p_effective_date
1169 );
1170
1171
1172 hr_utility.set_location(l_proc, 50);
1173
1174 --
1175 -- Validate the combination of grade id and parent spine id
1176 --
1177 chk_uniq_grade_pay_scale
1178 (p_grade_spine_id => p_rec.grade_spine_id
1179 ,p_grade_id => p_rec.grade_id
1180 ,p_parent_spine_id => p_rec.parent_spine_id
1181 ,p_business_group_id => p_rec.business_group_id
1182 ,p_object_version_number => p_rec.object_version_number
1183 ,p_effective_date => p_effective_date
1184 );
1185
1186 hr_utility.set_location(' Leaving:'||l_proc, 100);
1187 End insert_validate;
1188 --
1189 -- ----------------------------------------------------------------------------
1190 -- |---------------------------< update_validate >----------------------------|
1191 -- ----------------------------------------------------------------------------
1192 Procedure update_validate
1193 (p_rec in out nocopy per_pgs_shd.g_rec_type
1194 ,p_effective_date in date
1195 ,p_datetrack_mode in varchar2
1196 ,p_validation_start_date in date
1197 ,p_validation_end_date in date
1198 ) is
1199 --
1200 l_proc varchar2(72) := g_package||'update_validate';
1201 --
1202 Begin
1203 hr_utility.set_location('Entering:'||l_proc, 10);
1204 --
1205 -- Call all supporting business operations
1206 --
1207 hr_api.validate_bus_grp_id
1208 (p_business_group_id => p_rec.business_group_id
1209 ,p_associated_column1 => per_pgs_shd.g_tab_nam
1210 || '.BUSINESS_GROUP_ID');
1211
1212 --
1213 -- Validate grade spine id
1214 --
1215 chk_grade_spine_id
1216 (p_grade_spine_id => p_rec.grade_spine_id
1217 ,p_object_version_number => p_rec.object_version_number
1218 ,p_effective_date => p_effective_date
1219 ) ;
1220
1221 hr_utility.set_location(l_proc, 20);
1222
1223 --
1224 -- After validating the set of important attributes,
1225 -- if Multiple Message detection is enabled and at least
1226 -- one error has been found then abort further validation.
1227 --
1228 hr_multi_message.end_validation_set;
1229 --
1230 -- Validate Dependent Attributes
1231 --
1232 -- Call the datetrack update integrity operation
1233 --
1234 dt_update_validate
1235 (p_datetrack_mode => p_datetrack_mode
1236 ,p_validation_start_date => p_validation_start_date
1237 ,p_validation_end_date => p_validation_end_date
1238 );
1239 --
1240 chk_non_updateable_args
1241 (p_effective_date => p_effective_date
1242 ,p_rec => p_rec
1243 );
1244
1245 hr_utility.set_location(l_proc, 30);
1246
1247 --
1248 -- After validating the set of important attributes,
1249 -- if Multiple Message detection is enabled and at least
1250 -- one error has been found then abort further validation.
1251 --
1252 hr_multi_message.end_validation_set;
1253
1254 --
1255 -- Validate Dependent Attributes
1256 --
1257 --
1258
1259 --
1260 -- Validate parent spine id
1261 --
1262 chk_parent_spine_id
1263 (p_grade_spine_id => p_rec.grade_spine_id
1264 ,p_parent_spine_id => p_rec.parent_spine_id
1265 ,p_business_group_id => p_rec.business_group_id
1266 ,p_object_version_number => p_rec.object_version_number
1267 ,p_effective_date => p_effective_date
1268 );
1269
1270 hr_utility.set_location(l_proc, 40);
1271
1272 --
1273 -- Validate grade id
1274 --
1275 chk_grade_id
1276 (p_grade_id => p_rec.grade_id
1277 ,p_grade_spine_id => p_rec.grade_spine_id
1278 ,p_business_group_id => p_rec.business_group_id
1279 ,p_object_version_number => p_rec.object_version_number
1280 ,p_effective_date => p_effective_date
1281 ) ;
1282
1283 hr_utility.set_location(l_proc, 50);
1284
1285 --
1286 -- Validate ceiling step id
1287 --
1288 chk_ceiling_step_id
1289 (p_ceiling_step_id => p_rec.ceiling_step_id
1290 ,p_grade_spine_id => p_rec.grade_spine_id
1291 ,p_business_group_id => p_rec.business_group_id
1292 ,p_object_version_number => p_rec.object_version_number
1293 ,p_effective_date => p_effective_date
1294 );
1295
1296
1297 hr_utility.set_location(l_proc, 60);
1298
1299 --
1300 -- Validate the combination of grade id and parent spine id
1301 --
1302 chk_uniq_grade_pay_scale
1303 (p_grade_spine_id => p_rec.grade_spine_id
1304 ,p_grade_id => p_rec.grade_id
1305 ,p_parent_spine_id => p_rec.parent_spine_id
1306 ,p_business_group_id => p_rec.business_group_id
1307 ,p_object_version_number => p_rec.object_version_number
1308 ,p_effective_date => p_effective_date
1309 );
1310 --
1311 hr_utility.set_location(' Leaving:'||l_proc, 100);
1312 End update_validate;
1313 --
1314 -- ----------------------------------------------------------------------------
1315 -- |---------------------------< delete_validate >----------------------------|
1316 -- ----------------------------------------------------------------------------
1317 Procedure delete_validate
1318 (p_rec in per_pgs_shd.g_rec_type
1319 ,p_effective_date in date
1320 ,p_datetrack_mode in varchar2
1321 ,p_validation_start_date in date
1322 ,p_validation_end_date in date
1323 ) is
1324 --
1325 l_proc varchar2(72) := g_package||'delete_validate';
1326 --
1327 Begin
1328 hr_utility.set_location('Entering:'||l_proc, 5);
1329 --
1330 -- Call all supporting business operations
1331 --
1332 dt_delete_validate
1333 (p_datetrack_mode => p_datetrack_mode
1334 ,p_validation_start_date => p_validation_start_date
1335 ,p_validation_end_date => p_validation_end_date
1336 ,p_grade_spine_id => p_rec.grade_spine_id
1337 );
1338 --
1339
1340 hr_utility.set_location(l_proc, 20);
1341
1342 chk_delete(p_parent_spine_id => p_rec.parent_spine_id
1343 ,p_grade_id => p_rec.grade_id);
1344 --
1345 --
1346 hr_utility.set_location(' Leaving:'||l_proc, 30);
1347 End delete_validate;
1348 --
1349 end per_pgs_bus;