[Home] [Help]
PACKAGE BODY: APPS.PER_SPS_BUS
Source
1 Package Body per_sps_bus as
2 /* $Header: pespsrhi.pkb 120.5.12000000.1 2007/01/22 04:39:24 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_sps_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_step_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_step_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_spinal_point_steps_f sps
32 where sps.step_id = p_step_id
33 and pbg.business_group_id = sps.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 => 'step_id'
50 ,p_argument_value => p_step_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 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
66 => nvl(p_associated_column1,'STEP_ID')
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_step_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_spinal_point_steps_f sps
102 where sps.step_id = p_step_id
103 and pbg.business_group_id = sps.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 => 'step_id'
119 ,p_argument_value => p_step_id
120 );
121 --
122 if ( nvl(per_sps_bus.g_step_id, hr_api.g_number)
123 = p_step_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_sps_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_sps_bus.g_step_id := p_step_id;
154 per_sps_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 -- ----------------------------------------------------------------------------
162 -- |---------------------------< chk_step_id >--------------------------------|
163 -- ----------------------------------------------------------------------------
164 --
165 -- Description
166 -- This procedure is used to check that the primary key for the table
167 -- is created properly. It should be null on insert and
168 -- should not be able to be updated.
169 --
170 -- Pre Conditions
171 -- None.
172 --
173 -- In Parameters
174 -- step_id PK of record being inserted or updated.
175 -- object_version_number Object version number of record being
176 -- inserted or updated.
177 -- p_effective_date
178 --
179 -- Post Success
180 -- Processing continues
181 --
182 -- Post Failure
183 -- Errors handled by the procedure
184 --
185 -- Access Status
186 -- Internal table handler use only.
187 --
188 --
189 -- {End Of Comments}
190 -- ----------------------------------------------------------------------------
191 Procedure chk_step_id
192 ( p_step_id in per_spinal_point_steps_f.step_id%TYPE
193 ,p_object_version_number in per_spinal_point_steps_f.object_version_number%TYPE
194 ,p_effective_date in date
195 ) is
196 --
197 l_proc varchar2(72) := g_package||'chk_step_id';
198 l_api_updating boolean;
199 --
200 Begin
201 hr_utility.set_location('Entering:'||l_proc, 10);
202 --
203 l_api_updating := per_sps_shd.api_updating
204 (p_effective_date => p_effective_date
205 ,p_step_id => p_step_id
206 ,p_object_version_number => p_object_version_number
207 );
208 --
209 if (l_api_updating
210 and nvl(p_step_id,hr_api.g_number)
211 <> per_sps_shd.g_old_rec.step_id) then
212 --
213 -- raise error as PK has changed
214 --
215 per_sps_shd.constraint_error('PER_SPINAL_POINT_STEPS_F_PK');
216 --
217 elsif not l_api_updating then
218 --
219 -- check if PK is null
220 --
221 if p_step_id is not null then
222 --
223 -- Set p_step_id to gloval value for insert
224 --
225 per_sps_ins.set_base_key_value(p_step_id);
226
227 /*
228 --
229 -- raise error as PK is not null
230 --
231 per_sps_shd.constraint_error('PER_SPINAL_POINT_STEPS_F_PK');
232 --
233 */
234 end if;
235 --
236 end if;
237 --
238 hr_utility.set_location('Leaving:'||l_proc, 20);
239 --
240 End chk_step_id;
241 --
242 --
243 -- ---------------------------------------------------------------------------
244 -- |-----------------< chk_spinal_point_id >---------------------------------|
245 -- ---------------------------------------------------------------------------
246 --
247 -- Description:
248 -- Validates that a spinal_point_id is mandatory and
249 -- exists in table per_spinal_points.
250 --
251 -- Validates business_group_id in per_spinal_points talble should be
252 -- the same as business_group_id to be passed as a parameter and
253 -- parent_spine_id in per_spinal_points should be the same as parent_spine_id
254 -- in per_grade_spines_f to be refered by grade_spine_id to be passed as
255 -- a parameter.
256 --
257 -- Pre-conditions:
258 -- step_id must be valid.
259 -- business_group_id must be valid.
260 -- effective_date must be valid.
261 --
262 -- In Arguments:
263 -- p_step_id
264 -- p_spinal_point_id
265 -- p_grade_spine_id
266 -- p_business_group_id
267 -- p_object_version_number
268 -- p_effective_date
269 --
270 -- Post Success:
271 -- Processing continues
272 --
273 -- Post Failure:
274 -- Errors handled by the procedure
275 --
276 -- Access Status:
277 -- Internal Table Handler Use Only.
278 --
279 -- {End Of Comments}
280 -- ----------------------------------------------------------------------------
281 --
282 procedure chk_spinal_point_id
283 (p_step_id in per_spinal_point_steps_f.step_id%TYPE
284 ,p_spinal_point_id in per_spinal_point_steps_f.spinal_point_id%TYPE
285 ,p_grade_spine_id in per_spinal_point_steps_f.grade_spine_id%TYPE
286 ,p_business_group_id in per_spinal_point_steps_f.business_group_id%TYPE
287 ,p_object_version_number in per_spinal_point_steps_f.object_version_number%TYPE
288 ,p_effective_date in date
289 )
290 is
291 --
292 l_exists varchar2(1);
293 l_proc varchar2(72) := g_package||'chk_spinal_point_id';
294 l_api_updating boolean;
295 --
296 cursor csr_valid_spinal_point is
297 select null
298 from per_spinal_points psp
299 where psp.business_group_id = p_business_group_id
300 and psp.spinal_point_id = p_spinal_point_id;
301 --
302 cursor csr_valid_parent_spine is
303 select null
304 from per_spinal_points psp
305 ,per_grade_spines_f pgs
306 where psp.business_group_id = p_business_group_id
307 and psp.spinal_point_id = p_spinal_point_id
308 and pgs.grade_spine_id = p_grade_spine_id
309 and pgs.business_group_id = p_business_group_id
310 and p_effective_date between
311 pgs.effective_start_date and pgs.effective_end_date
312 and pgs.parent_spine_id = psp.parent_spine_id;
313 --
314 begin
315
316 hr_utility.set_location('Entering:'|| l_proc, 10);
317 --
318 -- Check mandatory parameters have been set
319 --
320 hr_api.mandatory_arg_error
321 (p_api_name => l_proc
322 ,p_argument => 'spinal_point_id'
323 ,p_argument_value => p_spinal_point_id
324 );
325
326 --
327 -- Only proceed with validation if :
328 -- a) The current g_old_rec is current and
329 -- b) The value for spinal_point_id has changed
330 --
331 l_api_updating := per_sps_shd.api_updating
332 (p_effective_date => p_effective_date
333 ,p_step_id => p_step_id
334 ,p_object_version_number => p_object_version_number);
335 --
336 if (l_api_updating and nvl(per_sps_shd.g_old_rec.spinal_point_id,
337 hr_api.g_number) = nvl(p_spinal_point_id, hr_api.g_number)) then
338 return;
339 end if;
340
341 hr_utility.set_location(l_proc, 20);
342
343 open csr_valid_spinal_point;
344 fetch csr_valid_spinal_point into l_exists;
345 if csr_valid_spinal_point%notfound then
346 close csr_valid_spinal_point;
347 --
348 per_sps_shd.constraint_error(p_constraint_name => 'PER_SPINAL_POINT_STEPS_F_FK2');
349 --
350 end if;
351 close csr_valid_spinal_point;
352
353 hr_utility.set_location(l_proc, 30);
354
355 --
356 -- check parent_spine_id in per_spinal_points is the same as
357 -- parent_spine_id in per_grade_spines_f
358 --
359 open csr_valid_parent_spine;
360 fetch csr_valid_parent_spine into l_exists;
361 if csr_valid_parent_spine%notfound then
362 close csr_valid_parent_spine;
363 --
364 hr_utility.set_message(800, 'HR_289286_PARENT_SPINE_INVALID');
365 hr_utility.raise_error;
366 --
367 end if;
368 close csr_valid_parent_spine;
369
370 hr_utility.set_location(' Leaving:'|| l_proc, 40);
371
372 end chk_spinal_point_id;
373 --
374 -- ---------------------------------------------------------------------------
378 -- Description:
375 -- |------------------< chk_grade_spine_id >---------------------------------|
376 -- ---------------------------------------------------------------------------
377 --
379 -- Validates that a grade_spine_id is mandatory and
380 -- exists in table per_grade_spines.
381 --
382 -- Validates business_group_id in per_grade_spines should be the same
383 -- as business_group_id to be passed as a parameter.
384 --
385 -- Pre-conditions:
386 -- step_id must be valid.
387 -- business_group_id must be valid.
388 -- effective_date must be valid.
389 --
390 -- In Arguments:
391 -- p_step_id
392 -- p_grade_spine_id
393 -- p_business_group_id
394 -- p_object_version_number
395 -- p_effective_date
396 --
397 -- Post Success:
398 -- Processing continues
399 --
400 -- Post Failure:
401 -- Errors handled by the procedure
402 --
403 -- Access Status:
404 -- Internal Table Handler Use Only.
405 --
406 -- {End Of Comments}
407 -- ----------------------------------------------------------------------------
408 --
409 procedure chk_grade_spine_id
410 (p_step_id in per_spinal_point_steps_f.step_id%TYPE
411 ,p_grade_spine_id in per_spinal_point_steps_f.grade_spine_id%TYPE
412 ,p_business_group_id in per_spinal_point_steps_f.business_group_id%TYPE
413 ,p_object_version_number in per_spinal_point_steps_f.object_version_number%TYPE
414 ,p_effective_date in date
415 )
416 is
417 --
418 l_exists varchar2(1);
419 l_proc varchar2(72) := g_package||'chk_grade_spine_id';
420 l_api_updating boolean;
421 --
422 cursor csr_valid_grade_spine is
423 select null
424 from per_grade_spines_f pgs
425 where pgs.business_group_id = p_business_group_id
426 and pgs.grade_spine_id = p_grade_spine_id
427 and p_effective_date between
428 pgs.effective_start_date and pgs.effective_end_date;
429 --
430 begin
431
432 hr_utility.set_location('Entering:'|| l_proc, 10);
433 --
434 -- Check mandatory parameters have been set
435 --
436 hr_api.mandatory_arg_error
437 (p_api_name => l_proc
438 ,p_argument => 'grade_spine_id'
439 ,p_argument_value => p_grade_spine_id
440 );
441
442 --
443 -- Only proceed with validation if :
444 -- a) The current g_old_rec is current and
445 -- b) The value for grade_spine_id has changed
446 --
447 l_api_updating := per_sps_shd.api_updating
448 (p_effective_date => p_effective_date
449 ,p_step_id => p_step_id
450 ,p_object_version_number => p_object_version_number);
451 --
452 if (l_api_updating and nvl(per_sps_shd.g_old_rec.grade_spine_id,
453 hr_api.g_number) = nvl(p_grade_spine_id, hr_api.g_number)) then
454 return;
455 end if;
456
457 hr_utility.set_location(l_proc, 20);
458
459 open csr_valid_grade_spine;
460 fetch csr_valid_grade_spine into l_exists;
461 if csr_valid_grade_spine%notfound then
462 close csr_valid_grade_spine;
463 --
464 per_sps_shd.constraint_error(p_constraint_name => 'PER_SPINAL_POINT_STEPS_F_N3');
465 --
466 end if;
467 close csr_valid_grade_spine;
468
469 hr_utility.set_location(' Leaving:'|| l_proc, 30);
470
471 end chk_grade_spine_id;
472 --
473 -- ---------------------------------------------------------------------------
474 -- |------------------------< chk_sequence >---------------------------------|
475 -- ---------------------------------------------------------------------------
476 --
477 -- Description:
478 -- Validates that a sequence is mandatory and exists in table per_spinal_points.
479 --
480 -- Pre-conditions:
481 -- step_id must be valid.
482 -- business_group_id must be valid.
483 -- effective_date must be valid.
484 --
485 -- In Arguments:
486 -- p_step_id
487 -- p_sequence
488 -- p_business_group_id
489 -- p_object_version_number
490 -- p_effective_date
491 --
492 -- Post Success:
493 -- Processing continues
494 --
495 -- Post Failure:
496 -- Errors handled by the procedure
497 --
498 -- Access Status:
499 -- Internal Table Handler Use Only.
500 --
501 -- {End Of Comments}
502 -- ----------------------------------------------------------------------------
503 --
504 procedure chk_sequence
505 (p_step_id in per_spinal_point_steps_f.step_id%TYPE
506 ,p_sequence in per_spinal_point_steps_f.sequence%TYPE
507 ,p_spinal_point_id in per_spinal_point_steps_f.spinal_point_id%TYPE
508 ,p_business_group_id in per_spinal_point_steps_f.business_group_id%TYPE
509 ,p_object_version_number in per_spinal_point_steps_f.object_version_number%TYPE
510 ,p_effective_date in date
511 )
512 is
513 --
514 l_exists varchar2(1);
515 l_proc varchar2(72) := g_package||'chk_sequence';
516 l_api_updating boolean;
517 --
518 cursor csr_valid_sequence is
519 select null
520 from per_spinal_points psp
524 --
521 where psp.business_group_id = p_business_group_id
522 and psp.spinal_point_id = p_spinal_point_id
523 and psp.sequence = p_sequence;
525 begin
526
527 hr_utility.set_location('Entering:'|| l_proc, 10);
528 --
529 -- Check mandatory parameters have been set
530 --
531 hr_api.mandatory_arg_error
532 (p_api_name => l_proc
533 ,p_argument => 'sequence'
534 ,p_argument_value => p_sequence
535 );
536
537 --
538 -- Only proceed with validation if :
539 -- a) The current g_old_rec is current and
540 -- b) The value for sequence has changed
541 --
542 l_api_updating := per_sps_shd.api_updating
543 (p_effective_date => p_effective_date
544 ,p_step_id => p_step_id
545 ,p_object_version_number => p_object_version_number);
546 --
547 if (l_api_updating and nvl(per_sps_shd.g_old_rec.sequence,
548 hr_api.g_number) = nvl(p_sequence, hr_api.g_number)) then
549 return;
550 end if;
551
552 hr_utility.set_location(l_proc, 20);
553
554 open csr_valid_sequence;
555 fetch csr_valid_sequence into l_exists;
556 if csr_valid_sequence%notfound then
557 close csr_valid_sequence;
558 --
559 hr_utility.set_message(800, 'HR_289568_INV_STEP_SEQUENCE');
560 hr_utility.raise_error;
561 --
562 end if;
563 close csr_valid_sequence;
564
565 hr_utility.set_location(' Leaving:'|| l_proc, 30);
566
567 end chk_sequence;
568 --
569 -- ---------------------------------------------------------------------------
570 -- |---------------------< chk_uniq_step_points >----------------------------|
571 -- ---------------------------------------------------------------------------
572 --
573 -- Description:
574 -- Validates that spinal_point_id is unique for each grade_spine_id is
575 -- unique.
576 --
577 -- Pre-conditions:
578 -- step_id must be valid.
579 -- spinal_point_id must be valid.
580 -- grade_spine_id must be valid.
581 -- effective_date must be valid.
582 --
583 -- In Arguments:
584 -- p_step_id
585 -- p_spinal_point_id
586 -- p_grade_spine_id
587 -- p_object_version_number
588 -- p_effective_date
589 --
590 -- Post Success:
591 -- Processing continues.
592 --
593 -- Post Failure:
594 -- Errors handled by the procedure
595 --
596 -- Access Status:
597 -- Internal Table Handler Use Only.
598 --
599 -- {End Of Comments}
600 -- ----------------------------------------------------------------------------
601 --
602 procedure chk_uniq_step_points
603 (p_step_id in per_spinal_point_steps_f.step_id%TYPE
604 ,p_spinal_point_id in per_spinal_point_steps_f.spinal_point_id%TYPE
605 ,p_grade_spine_id in per_spinal_point_steps_f.grade_spine_id%TYPE
606 ,p_object_version_number in per_spinal_point_steps_f.object_version_number%TYPE
607 ,p_effective_date in date
608 )
609 is
610 --
611 l_proc varchar2(72) := g_package||'chk_uniq_step_points';
612 l_api_updating boolean;
613 l_exists varchar2(1);
614 --
618 from sys.dual
615 --nvl clause added for p_step_id as part of fix for bug 3865077.
616 cursor csr_uniq_step_point is
617 select 'x'
619 where exists
620 (select null
621 from per_spinal_point_steps_f
622 where grade_spine_id = p_grade_spine_id
623 and spinal_point_id = p_spinal_point_id
624 and step_id <> nvl(p_step_id,hr_api.g_number)
625 and p_effective_date between effective_start_date
626 and effective_end_date);
627 --
628 begin
629
630 hr_utility.set_location('Entering:'|| l_proc, 10);
631 --
632 -- Only proceed with validation if :
633 -- a) The current g_old_rec is current and
634 -- b) The value for spinal_point_id and grade_spine_id have changed
635 --
636 l_api_updating := per_sps_shd.api_updating
637 (p_effective_date => p_effective_date
638 ,p_step_id => p_step_id
639 ,p_object_version_number => p_object_version_number);
640 --
641 if (l_api_updating
642 and nvl(per_sps_shd.g_old_rec.spinal_point_id,
643 hr_api.g_number) = nvl(p_spinal_point_id, hr_api.g_number)
644 and nvl(per_sps_shd.g_old_rec.grade_spine_id,hr_api.g_number)
645 = nvl(p_grade_spine_id, hr_api.g_number)
646 ) then
647 return;
648 end if;
649 hr_utility.set_location(l_proc, 20);
650
651 open csr_uniq_step_point;
652 fetch csr_uniq_step_point into l_exists;
653 if csr_uniq_step_point%found then
654 close csr_uniq_step_point;
655 --
656 hr_utility.set_message(800, 'HR_7936_GRDPSN_POINT_EXISTS');
657 hr_utility.raise_error;
658 --
659 end if;
660 close csr_uniq_step_point;
661
662 hr_utility.set_location(' Leaving:'|| l_proc, 30);
663
664 end chk_uniq_step_points;
665 --
666 --
667 -- ---------------------------------------------------------------------------
668 -- |---------------------------< chk_delete >--------------------------------|
669 -- ---------------------------------------------------------------------------
670 --
671 -- Description
672 -- This procedure is used to check that there are no values in
673 -- per_spinal_point_placement_f, per_all_assignments_f and hr_all_positions_f
674 --
675 -- Pre Conditions
676 -- None.
677 --
678 -- In Parameters
679 -- p_step_id
680 -- p_spinal_point_id
681 -- p_grade_spine_id
682 -- p_effective_date
683 -- p_datetrack_mode
684 --
685 -- Post Success
686 -- Processing continues
687 --
688 -- Post Failure
689 -- Errors handled by the procedure
690 --
691 -- Access Status
692 -- Internal table handler use only.
693 --
694 --
695 -- {End Of Comments}
696 -- ----------------------------------------------------------------------------
697 --
698 procedure chk_delete(
699 p_step_id in per_spinal_point_steps_f.step_id%Type
700 ,p_spinal_point_id in per_spinal_point_steps_f.spinal_point_id%Type
701 ,p_grade_spine_id in per_spinal_point_steps_f.grade_spine_id%Type
702 ,p_effective_date in date
703 ,p_datetrack_mode in varchar2
704 ,p_called_from_del_grd_scale in boolean --bug 4096238
705 ) is
706 --
707 -- Start of fix 3439542
708 l_proc varchar2(72) := g_package||'chk_delete';
709 l_date date;
710 l_exists1 varchar2(1) := Null;
711 l_exists2 varchar2(1) := Null;
712 l_exists3 varchar2(1) := Null;
713 --
714 cursor csr_spinal_point(p_date date) is
715 select 'X'
716 from per_spinal_point_placements_f
717 where step_id = p_step_id
718 and p_date < effective_end_date;
719 --
720 cursor csr_assignment(p_date date) is
721 select 'X'
722 from per_all_assignments_f
723 where special_ceiling_step_id = p_step_id;
724 -- and p_date < effective_end_date;
725 --
726 cursor csr_position(p_date date) is
727 select 'X'
728 from hr_all_positions_f
729 where entry_step_id = p_step_id
730 and p_date < effective_end_date;
731
732
733
734 -- start of bug fix 4096238
735
736 l_ceil_id varchar2(1) := Null;
737 l_grade_spine_id number(15,0):= Null;
738
739 /* This cusor will be called when the grade step
740 is getting purged and the cursor checks wheather the current step
741 is used as a ceiling step in its life time . */
742
743 cursor csr_ceiling_chk is
744 select 'X' from per_grade_spines_f
745 where ceiling_step_id=p_step_id
746 and grade_spine_id=l_grade_spine_id
747 and p_effective_date<>effective_start_date;
748 /* This cusor will be called when the grade step
749 is getting end dated and checks wheather the current step is
750 used as a ceiling step in future . */
751
752 cursor csr_ceiling_chk2 IS
753 select 'X' from per_grade_spines_f
754 where ceiling_step_id = p_step_id and
755 grade_spine_id=l_grade_spine_id and
756 p_effective_date <= effective_end_date;
757
758 -- end of fix for 4096238
759 --
760 begin
761 --
762 hr_utility.set_location('Entering:'||l_proc, 10);
763 --
764 -- Needs to be checked the existence of child records
768 else
765 -- based on the DT mode
766 if p_datetrack_mode = hr_api.g_delete then
767 l_date := p_effective_date;
769 l_date := hr_api.g_eot;
770 end if;
771 -- Check on per_spinal_point_placements_f
772 open csr_spinal_point(l_date);
773 fetch csr_spinal_point into l_exists1;
774 close csr_spinal_point;
775 if l_exists1 = 'X' then
776 hr_utility.set_message(801, 'PER_7938_DEL_STEP_PLACE');
777 hr_utility.raise_error;
778 end if;
779 --
780 hr_utility.set_location(l_proc, 20);
781 -- Check on per_all_assignments_f
782 open csr_assignment(l_date);
783 fetch csr_assignment into l_exists2;
784 close csr_assignment;
785 if l_exists2 = 'X' then
786 hr_utility.set_message(801, 'PER_7939_DEL_STEP_ASS');
787 hr_utility.raise_error;
788 end if;
789 --
790 hr_utility.set_location(l_proc, 30);
791 -- Check on hr_all_positions_f
792 open csr_position(l_date);
793 fetch csr_position into l_exists3;
794 close csr_position;
795 if l_exists3 = 'X' then
796 hr_utility.set_message(801, 'HR_289566_DEL_STEP_POSITION');
797 hr_utility.raise_error;
798 end if;
799
800 -- start of bug fix 4096238
801
802 select grade_spine_id into l_grade_spine_id
803 from per_spinal_point_steps_f where step_id=p_step_id;
804
805 if ( not p_called_from_del_grd_scale ) and (p_datetrack_mode = hr_api.g_zap ) then
806 open csr_ceiling_chk;
807 fetch csr_ceiling_chk into l_ceil_id;
808 close csr_ceiling_chk;
809 if l_ceil_id = 'X' then
810 hr_utility.set_location(l_proc, 40);
811 hr_utility.set_message(800, 'HR_449730_DEL_CEI_FUT_PAST');
812 hr_utility.raise_error;
813 end if;
814 end if;
815 l_ceil_id :=Null;
816 if ( not p_called_from_del_grd_scale ) and (p_datetrack_mode = hr_api.g_delete ) then
817 open csr_ceiling_chk2;
818 fetch csr_ceiling_chk2 into l_ceil_id;
819 close csr_ceiling_chk2;
820 if l_ceil_id = 'X' then
821 hr_utility.set_location(l_proc, 50);
822 hr_utility.set_message(800, 'HR_449731_END_CEI_EXISTS');
823 hr_utility.raise_error;
824 end if;
825 end if;
826
827 --
828 -- end of bug fix 4096238
829 --
830 hr_utility.set_location(' Leaving:' || l_proc, 99);
831 --
832 -- End of fix 3439542
833 end chk_delete;
834 --
835 -- ----------------------------------------------------------------------------
836 -- |-----------------------------< chk_ddf >----------------------------------|
837 -- ----------------------------------------------------------------------------
838 --
839 -- Description:
840 -- Validates all the Developer Descriptive Flexfield values.
841 --
842 -- Prerequisites:
843 -- All other columns have been validated. Must be called as the
844 -- last step from insert_validate and update_validate.
845 --
846 -- In Arguments:
847 -- p_rec
848 --
849 -- Post Success:
850 -- If the Developer Descriptive Flexfield structure column and data values
851 -- are all valid this procedure will end normally and processing will
852 -- continue.
853 --
854 -- Post Failure:
855 -- If the Developer Descriptive Flexfield structure column value or any of
856 -- the data values are invalid then an application error is raised as
857 -- a PL/SQL exception.
858 --
859 -- Access Status:
860 -- Internal Row Handler Use Only.
861 --
862 -- ----------------------------------------------------------------------------
863 procedure chk_ddf
864 (p_rec in per_sps_shd.g_rec_type
865 ) is
866 --
867 l_proc varchar2(72) := g_package || 'chk_ddf';
868 --
869 begin
870 hr_utility.set_location('Entering:'||l_proc,10);
871 --
872 if ((p_rec.step_id is not null) and (
873 nvl(per_sps_shd.g_old_rec.information1, hr_api.g_varchar2) <>
874 nvl(p_rec.information1, hr_api.g_varchar2) or
875 nvl(per_sps_shd.g_old_rec.information2, hr_api.g_varchar2) <>
876 nvl(p_rec.information2, hr_api.g_varchar2) or
877 nvl(per_sps_shd.g_old_rec.information3, hr_api.g_varchar2) <>
878 nvl(p_rec.information3, hr_api.g_varchar2) or
879 nvl(per_sps_shd.g_old_rec.information4, hr_api.g_varchar2) <>
880 nvl(p_rec.information4, hr_api.g_varchar2) or
881 nvl(per_sps_shd.g_old_rec.information5, hr_api.g_varchar2) <>
882 nvl(p_rec.information5, hr_api.g_varchar2) or
883 nvl(per_sps_shd.g_old_rec.information6, hr_api.g_varchar2) <>
884 nvl(p_rec.information6, hr_api.g_varchar2) or
885 nvl(per_sps_shd.g_old_rec.information7, hr_api.g_varchar2) <>
886 nvl(p_rec.information7, hr_api.g_varchar2) or
887 nvl(per_sps_shd.g_old_rec.information8, hr_api.g_varchar2) <>
888 nvl(p_rec.information8, hr_api.g_varchar2) or
889 nvl(per_sps_shd.g_old_rec.information9, hr_api.g_varchar2) <>
890 nvl(p_rec.information9, hr_api.g_varchar2) or
891 nvl(per_sps_shd.g_old_rec.information10, hr_api.g_varchar2) <>
892 nvl(p_rec.information10, hr_api.g_varchar2) or
893 nvl(per_sps_shd.g_old_rec.information11, hr_api.g_varchar2) <>
894 nvl(p_rec.information11, hr_api.g_varchar2) or
895 nvl(per_sps_shd.g_old_rec.information12, hr_api.g_varchar2) <>
896 nvl(p_rec.information12, hr_api.g_varchar2) or
897 nvl(per_sps_shd.g_old_rec.information13, hr_api.g_varchar2) <>
901 nvl(per_sps_shd.g_old_rec.information15, hr_api.g_varchar2) <>
898 nvl(p_rec.information13, hr_api.g_varchar2) or
899 nvl(per_sps_shd.g_old_rec.information14, hr_api.g_varchar2) <>
900 nvl(p_rec.information14, hr_api.g_varchar2) or
902 nvl(p_rec.information15, hr_api.g_varchar2) or
903 nvl(per_sps_shd.g_old_rec.information16, hr_api.g_varchar2) <>
904 nvl(p_rec.information16, hr_api.g_varchar2) or
905 nvl(per_sps_shd.g_old_rec.information17, hr_api.g_varchar2) <>
906 nvl(p_rec.information17, hr_api.g_varchar2) or
907 nvl(per_sps_shd.g_old_rec.information18, hr_api.g_varchar2) <>
908 nvl(p_rec.information18, hr_api.g_varchar2) or
909 nvl(per_sps_shd.g_old_rec.information19, hr_api.g_varchar2) <>
910 nvl(p_rec.information19, hr_api.g_varchar2) or
911 nvl(per_sps_shd.g_old_rec.information20, hr_api.g_varchar2) <>
912 nvl(p_rec.information20, hr_api.g_varchar2) or
913 nvl(per_sps_shd.g_old_rec.information21, hr_api.g_varchar2) <>
914 nvl(p_rec.information21, hr_api.g_varchar2) or
915 nvl(per_sps_shd.g_old_rec.information22, hr_api.g_varchar2) <>
916 nvl(p_rec.information22, hr_api.g_varchar2) or
917 nvl(per_sps_shd.g_old_rec.information23, hr_api.g_varchar2) <>
918 nvl(p_rec.information23, hr_api.g_varchar2) or
919 nvl(per_sps_shd.g_old_rec.information24, hr_api.g_varchar2) <>
920 nvl(p_rec.information24, hr_api.g_varchar2) or
921 nvl(per_sps_shd.g_old_rec.information25, hr_api.g_varchar2) <>
922 nvl(p_rec.information25, hr_api.g_varchar2) or
923 nvl(per_sps_shd.g_old_rec.information26, hr_api.g_varchar2) <>
924 nvl(p_rec.information26, hr_api.g_varchar2) or
925 nvl(per_sps_shd.g_old_rec.information27, hr_api.g_varchar2) <>
926 nvl(p_rec.information27, hr_api.g_varchar2) or
927 nvl(per_sps_shd.g_old_rec.information28, hr_api.g_varchar2) <>
928 nvl(p_rec.information28, hr_api.g_varchar2) or
929 nvl(per_sps_shd.g_old_rec.information29, hr_api.g_varchar2) <>
930 nvl(p_rec.information29, hr_api.g_varchar2) or
931 nvl(per_sps_shd.g_old_rec.information30, hr_api.g_varchar2) <>
932 nvl(p_rec.information30, hr_api.g_varchar2) or
933 nvl(per_sps_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
934 nvl(p_rec.information_category, hr_api.g_varchar2) ))
935 or (p_rec.step_id is null) then
936 --
937 -- Only execute the validation if absolutely necessary:
938 -- a) During update, the structure column value or any
939 -- of the attribute values have actually changed.
940 -- b) During insert.
941 --
942 hr_dflex_utility.ins_or_upd_descflex_attribs
943 (p_appl_short_name => 'PER'
944 ,p_descflex_name => 'Spinal Point Step DDF'
945 ,p_attribute_category => p_rec.information_category
946 ,p_attribute1_name => 'INFORMATION1'
947 ,p_attribute1_value => p_rec.information1
948 ,p_attribute2_name => 'INFORMATION2'
949 ,p_attribute2_value => p_rec.information2
950 ,p_attribute3_name => 'INFORMATION3'
951 ,p_attribute3_value => p_rec.information3
952 ,p_attribute4_name => 'INFORMATION4'
953 ,p_attribute4_value => p_rec.information4
954 ,p_attribute5_name => 'INFORMATION5'
955 ,p_attribute5_value => p_rec.information5
956 ,p_attribute6_name => 'INFORMATION6'
957 ,p_attribute6_value => p_rec.information6
958 ,p_attribute7_name => 'INFORMATION7'
959 ,p_attribute7_value => p_rec.information7
960 ,p_attribute8_name => 'INFORMATION8'
961 ,p_attribute8_value => p_rec.information8
962 ,p_attribute9_name => 'INFORMATION9'
963 ,p_attribute9_value => p_rec.information9
964 ,p_attribute10_name => 'INFORMATION10'
965 ,p_attribute10_value => p_rec.information10
966 ,p_attribute11_name => 'INFORMATION11'
967 ,p_attribute11_value => p_rec.information11
968 ,p_attribute12_name => 'INFORMATION12'
969 ,p_attribute12_value => p_rec.information12
970 ,p_attribute13_name => 'INFORMATION13'
971 ,p_attribute13_value => p_rec.information13
972 ,p_attribute14_name => 'INFORMATION14'
973 ,p_attribute14_value => p_rec.information14
974 ,p_attribute15_name => 'INFORMATION15'
975 ,p_attribute15_value => p_rec.information15
976 ,p_attribute16_name => 'INFORMATION16'
977 ,p_attribute16_value => p_rec.information16
978 ,p_attribute17_name => 'INFORMATION17'
979 ,p_attribute17_value => p_rec.information17
980 ,p_attribute18_name => 'INFORMATION18'
981 ,p_attribute18_value => p_rec.information18
982 ,p_attribute19_name => 'INFORMATION19'
983 ,p_attribute19_value => p_rec.information19
984 ,p_attribute20_name => 'INFORMATION20'
985 ,p_attribute20_value => p_rec.information20
986 ,p_attribute21_name => 'INFORMATION21'
990 ,p_attribute23_name => 'INFORMATION23'
987 ,p_attribute21_value => p_rec.information21
988 ,p_attribute22_name => 'INFORMATION22'
989 ,p_attribute22_value => p_rec.information22
991 ,p_attribute23_value => p_rec.information23
992 ,p_attribute24_name => 'INFORMATION24'
993 ,p_attribute24_value => p_rec.information24
994 ,p_attribute25_name => 'INFORMATION25'
995 ,p_attribute25_value => p_rec.information25
996 ,p_attribute26_name => 'INFORMATION26'
997 ,p_attribute26_value => p_rec.information26
998 ,p_attribute27_name => 'INFORMATION27'
999 ,p_attribute27_value => p_rec.information27
1000 ,p_attribute28_name => 'INFORMATION28'
1001 ,p_attribute28_value => p_rec.information28
1002 ,p_attribute29_name => 'INFORMATION29'
1003 ,p_attribute29_value => p_rec.information29
1004 ,p_attribute30_name => 'INFORMATION30'
1005 ,p_attribute30_value => p_rec.information30
1006 );
1007 end if;
1008 --
1009 hr_utility.set_location(' Leaving:'||l_proc,20);
1010 end chk_ddf;
1011 --
1012 -- ----------------------------------------------------------------------------
1013 -- |-----------------------< chk_non_updateable_args >------------------------|
1014 -- ----------------------------------------------------------------------------
1015 -- {Start Of Comments}
1016 --
1017 -- Description:
1018 -- This procedure is used to ensure that non updateable attributes have
1019 -- not been updated. If an attribute has been updated an error is generated.
1020 --
1021 -- Pre Conditions:
1022 -- g_old_rec has been populated with details of the values currently in
1023 -- the database.
1024 --
1025 -- In Arguments:
1026 -- p_rec has been populated with the updated values the user would like the
1027 -- record set to.
1028 --
1029 -- Post Success:
1030 -- Processing continues if all the non updateable attributes have not
1031 -- changed.
1032 --
1033 -- Post Failure:
1034 -- An application error is raised if any of the non updatable attributes
1035 -- have been altered.
1036 --
1037 -- {End Of Comments}
1038 -- ----------------------------------------------------------------------------
1039 Procedure chk_non_updateable_args
1040 (p_effective_date in date
1041 ,p_rec in per_sps_shd.g_rec_type
1042 ) IS
1043 --
1044 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
1045 --
1046 Begin
1047 --
1048 -- Only proceed with the validation if a row exists for the current
1049 -- record in the HR Schema.
1050 --
1051 IF NOT per_sps_shd.api_updating
1052 (p_step_id => p_rec.step_id
1053 ,p_effective_date => p_effective_date
1054 ,p_object_version_number => p_rec.object_version_number
1055 ) THEN
1056 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
1057 fnd_message.set_token('PROCEDURE ', l_proc);
1058 fnd_message.set_token('STEP ', '5');
1059 fnd_message.raise_error;
1060 END IF;
1061
1062 --
1063 -- Check business_group_id is not updated
1064 --
1065 IF nvl(p_rec.business_group_id, hr_api.g_number) <>
1066 per_sps_shd.g_old_rec.business_group_id then
1067 hr_api.argument_changed_error
1068 (p_api_name => l_proc
1069 ,p_argument => 'BUSINESS_GROUP_ID'
1070 ,p_base_table => per_sps_shd.g_tab_nam
1071 );
1072 END IF;
1073
1074 --
1075 -- Check spinal_point_id is not updated
1076 --
1077 IF nvl(p_rec.spinal_point_id, hr_api.g_number) <>
1078 per_sps_shd.g_old_rec.spinal_point_id then
1079 hr_api.argument_changed_error
1080 (p_api_name => l_proc
1081 ,p_argument => 'SPINAL_POINT_ID'
1082 ,p_base_table => per_sps_shd.g_tab_nam
1083 );
1084 END IF;
1085
1086 --
1087 -- Check grade_spine_id is not updated
1088 --
1089 IF nvl(p_rec.grade_spine_id, hr_api.g_number) <>
1090 per_sps_shd.g_old_rec.grade_spine_id then
1091 hr_api.argument_changed_error
1092 (p_api_name => l_proc
1093 ,p_argument => 'GRADE_SPINE_ID'
1094 ,p_base_table => per_sps_shd.g_tab_nam
1095 );
1096 END IF;
1097
1098 --
1099 -- Check sequence is not updated
1100 --
1101 IF nvl(p_rec.sequence, hr_api.g_number) <>
1102 per_sps_shd.g_old_rec.sequence then
1103 hr_api.argument_changed_error
1104 (p_api_name => l_proc
1105 ,p_argument => 'SEQUENCE'
1106 ,p_base_table => per_sps_shd.g_tab_nam
1107 );
1108 END IF;
1109 --
1110 End chk_non_updateable_args;
1111 --
1112 -- ----------------------------------------------------------------------------
1113 -- |--------------------------< dt_update_validate >--------------------------|
1114 -- ----------------------------------------------------------------------------
1115 -- {Start Of Comments}
1116 --
1117 -- Description:
1118 -- This procedure is used for referential integrity of datetracked
1122 -- Prerequisites:
1119 -- parent entities when a datetrack update operation is taking place
1120 -- and where there is no cascading of update defined for this entity.
1121 --
1123 -- This procedure is called from the update_validate.
1124 --
1125 -- In Parameters:
1126 --
1127 -- Post Success:
1128 -- Processing continues.
1129 --
1130 -- Post Failure:
1131 --
1132 -- Developer Implementation Notes:
1133 -- This procedure should not need maintenance unless the HR Schema model
1134 -- changes.
1135 --
1136 -- Access Status:
1137 -- Internal Row Handler Use Only.
1138 --
1139 -- {End Of Comments}
1140 -- ----------------------------------------------------------------------------
1141 Procedure dt_update_validate
1142 (p_grade_spine_id in number default hr_api.g_number
1143 ,p_datetrack_mode in varchar2
1144 ,p_validation_start_date in date
1145 ,p_validation_end_date in date
1146 ) Is
1147 --
1148 l_proc varchar2(72) := g_package||'dt_update_validate';
1149 --
1150 Begin
1151 --
1152 -- Ensure that the p_datetrack_mode argument is not null
1153 --
1154 hr_api.mandatory_arg_error
1155 (p_api_name => l_proc
1156 ,p_argument => 'datetrack_mode'
1157 ,p_argument_value => p_datetrack_mode
1158 );
1159 --
1160 -- Mode will be valid, as this is checked at the start of the upd.
1161 --
1162 -- Ensure the arguments are not null
1163 --
1164 hr_api.mandatory_arg_error
1165 (p_api_name => l_proc
1166 ,p_argument => 'validation_start_date'
1167 ,p_argument_value => p_validation_start_date
1168 );
1169 --
1170 hr_api.mandatory_arg_error
1171 (p_api_name => l_proc
1172 ,p_argument => 'validation_end_date'
1173 ,p_argument_value => p_validation_end_date
1174 );
1175 --
1176 If ((nvl(p_grade_spine_id, hr_api.g_number) <> hr_api.g_number) and
1177 NOT (dt_api.check_min_max_dates
1178 (p_base_table_name => 'per_grade_spines_f'
1179 ,p_base_key_column => 'GRADE_SPINE_ID'
1180 ,p_base_key_value => p_grade_spine_id
1181 ,p_from_date => p_validation_start_date
1182 ,p_to_date => p_validation_end_date))) Then
1183 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
1184 fnd_message.set_token('TABLE_NAME','grade spines');
1185 hr_multi_message.add
1186 (p_associated_column1 => per_sps_shd.g_tab_nam || '.GRADE_SPINE_ID');
1187 End If;
1188 --
1189 Exception
1190 When Others Then
1191 --
1192 -- An unhandled or unexpected error has occurred which
1193 -- we must report
1194 --
1195 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1196 fnd_message.set_token('PROCEDURE', l_proc);
1197 fnd_message.set_token('STEP','15');
1198 fnd_message.raise_error;
1199 End dt_update_validate;
1200 --
1201 -- ----------------------------------------------------------------------------
1202 -- |--------------------------< dt_delete_validate >--------------------------|
1203 -- ----------------------------------------------------------------------------
1204 -- {Start Of Comments}
1205 --
1206 -- Description:
1207 -- This procedure is used for referential integrity of datetracked
1208 -- child entities when either a datetrack DELETE or ZAP is in operation
1209 -- and where there is no cascading of delete defined for this entity.
1210 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1211 -- datetracked child rows exist between the validation start and end
1212 -- dates.
1213 --
1214 -- Prerequisites:
1215 -- This procedure is called from the delete_validate.
1216 --
1217 -- In Parameters:
1218 --
1219 -- Post Success:
1220 -- Processing continues.
1221 --
1222 -- Post Failure:
1223 -- If a row exists by determining the returning Boolean value from the
1224 -- generic dt_api.rows_exist function then we must supply an error via
1225 -- the use of the local exception handler l_rows_exist.
1226 --
1227 -- Developer Implementation Notes:
1228 -- This procedure should not need maintenance unless the HR Schema model
1229 -- changes.
1230 --
1231 -- Access Status:
1232 -- Internal Row Handler Use Only.
1233 --
1234 -- {End Of Comments}
1235 -- ----------------------------------------------------------------------------
1236 Procedure dt_delete_validate
1237 (p_step_id in number
1238 ,p_datetrack_mode in varchar2
1239 ,p_validation_start_date in date
1240 ,p_validation_end_date in date
1241 ) Is
1242 --
1243 l_proc varchar2(72) := g_package||'dt_delete_validate';
1244 --
1245 Begin
1246 hr_utility.set_location('Entering : ' || l_proc, 10);
1247 hr_utility.trace('p_step_id : ' || p_step_id);
1248 hr_utility.trace('p_datetrack_mode : ' || p_datetrack_mode);
1249 --
1250 -- Ensure that the p_datetrack_mode argument is not null
1251 --
1252 hr_api.mandatory_arg_error
1253 (p_api_name => l_proc
1254 ,p_argument => 'datetrack_mode'
1255 ,p_argument_value => p_datetrack_mode
1256 );
1257 --
1261 If (p_datetrack_mode = hr_api.g_delete or
1258 -- Only perform the validation if the datetrack mode is either
1259 -- DELETE or ZAP
1260 --
1262 p_datetrack_mode = hr_api.g_zap) then
1263 --
1264 --
1265 -- Ensure the arguments are not null
1266 --
1267 hr_api.mandatory_arg_error
1268 (p_api_name => l_proc
1269 ,p_argument => 'validation_start_date'
1270 ,p_argument_value => p_validation_start_date
1271 );
1272 --
1273 hr_api.mandatory_arg_error
1274 (p_api_name => l_proc
1275 ,p_argument => 'validation_end_date'
1276 ,p_argument_value => p_validation_end_date
1277 );
1278 --
1279 hr_api.mandatory_arg_error
1280 (p_api_name => l_proc
1281 ,p_argument => 'step_id'
1282 ,p_argument_value => p_step_id
1283 );
1284 --
1285 hr_utility.set_location(l_proc, 20);
1286 --
1287 If (dt_api.rows_exist
1288 (p_base_table_name => 'per_cagr_entitlement_lines_f'
1289 ,p_base_key_column => 'step_id'
1290 ,p_base_key_value => p_step_id
1291 ,p_from_date => p_validation_start_date
1292 ,p_to_date => p_validation_end_date
1293 )) Then
1294 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
1295 fnd_message.set_token('TABLE_NAME','cagr entitlement lines');
1296 hr_multi_message.add;
1297 End If;
1298
1299 hr_utility.set_location(l_proc, 30);
1300
1301 If (dt_api.rows_exist
1302 (p_base_table_name => 'hr_all_positions_f'
1303 ,p_base_key_column => 'entry_step_id'
1304 ,p_base_key_value => p_step_id
1305 ,p_from_date => p_validation_start_date
1306 ,p_to_date => p_validation_end_date
1307 )) Then
1308 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
1309 fnd_message.set_token('TABLE_NAME','all positions');
1310 hr_multi_message.add;
1311 End If;
1312 --
1313 End If;
1314 --
1315 hr_utility.set_location(' Leaving: ' || l_proc, 40);
1316 --
1317 Exception
1318 When Others Then
1319 --
1320 -- An unhandled or unexpected error has occurred which
1321 -- we must report
1322 --
1323 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1324 fnd_message.set_token('PROCEDURE', l_proc);
1325 fnd_message.set_token('STEP','15');
1326 fnd_message.raise_error;
1327 --
1328 End dt_delete_validate;
1329 --
1330 -- ----------------------------------------------------------------------------
1331 -- |---------------------------< insert_validate >----------------------------|
1332 -- ----------------------------------------------------------------------------
1333 Procedure insert_validate
1334 (p_rec in per_sps_shd.g_rec_type
1335 ,p_effective_date in date
1336 ,p_datetrack_mode in varchar2
1337 ,p_validation_start_date in date
1338 ,p_validation_end_date in date
1339 ) is
1340 --
1341 l_proc varchar2(72) := g_package||'insert_validate';
1342 --
1343 Begin
1344 hr_utility.set_location('Entering:'||l_proc, 10);
1345 --
1346 -- Call all supporting business operations
1347 --
1348 hr_api.validate_bus_grp_id
1349 (p_business_group_id => p_rec.business_group_id
1350 ,p_associated_column1 => per_sps_shd.g_tab_nam
1351 || '.BUSINESS_GROUP_ID');
1352
1353 --
1354 -- Check step id
1355 --
1356 chk_step_id
1357 ( p_step_id => p_rec.step_id
1358 ,p_object_version_number => p_rec.object_version_number
1359 ,p_effective_date => p_effective_date
1360 );
1361 --
1362 -- After validating the set of important attributes,
1363 -- if Multiple Message detection is enabled and at least
1364 -- one error has been found then abort further validation.
1365 --
1366 hr_multi_message.end_validation_set;
1367 --
1368 -- Validate Dependent Attributes
1369 --
1370 --
1371
1372 hr_utility.set_location(l_proc, 20);
1373
1374 --
1375 -- check spinal point id
1376 --
1377 chk_spinal_point_id
1378 (p_step_id => p_rec.step_id
1379 ,p_spinal_point_id => p_rec.spinal_point_id
1380 ,p_grade_spine_id => p_rec.grade_spine_id
1381 ,p_business_group_id => p_rec.business_group_id
1382 ,p_object_version_number => p_rec.object_version_number
1383 ,p_effective_date => p_effective_date
1384 );
1385
1386 hr_utility.set_location(l_proc, 30);
1387
1388 --
1389 -- Check grade spine id
1390 --
1391 chk_grade_spine_id
1392 (p_step_id => p_rec.step_id
1393 ,p_grade_spine_id => p_rec.grade_spine_id
1394 ,p_business_group_id => p_rec.business_group_id
1395 ,p_object_version_number => p_rec.object_version_number
1396 ,p_effective_date => p_effective_date
1397 );
1398
1399 hr_utility.set_location(l_proc, 40);
1400
1401 --
1402 -- Check sequence
1403 --
1404 chk_sequence
1405 (p_step_id => p_rec.step_id
1409 ,p_object_version_number => p_rec.object_version_number
1406 ,p_sequence => p_rec.sequence
1407 ,p_spinal_point_id => p_rec.spinal_point_id
1408 ,p_business_group_id => p_rec.business_group_id
1410 ,p_effective_date => p_effective_date
1411 );
1412
1413 hr_utility.set_location(l_proc, 50);
1414
1415 --
1416 -- Check the combination of spinal point id and grade spine id
1417 --
1418 chk_uniq_step_points
1419 (p_step_id => p_rec.step_id
1420 ,p_spinal_point_id => p_rec.spinal_point_id
1421 ,p_grade_spine_id => p_rec.grade_spine_id
1422 ,p_object_version_number => p_rec.object_version_number
1423 ,p_effective_date => p_effective_date
1424 );
1425
1426 hr_utility.set_location(l_proc, 60);
1427
1428 per_sps_bus.chk_ddf(p_rec);
1429 --
1430 hr_utility.set_location(' Leaving:'||l_proc, 70);
1431 End insert_validate;
1432 --
1433 -- ----------------------------------------------------------------------------
1434 -- |---------------------------< update_validate >----------------------------|
1435 -- ----------------------------------------------------------------------------
1436 Procedure update_validate
1437 (p_rec in per_sps_shd.g_rec_type
1438 ,p_effective_date in date
1439 ,p_datetrack_mode in varchar2
1440 ,p_validation_start_date in date
1441 ,p_validation_end_date in date
1442 ) is
1443 --
1444 l_proc varchar2(72) := g_package||'update_validate';
1445 --
1446 Begin
1447 hr_utility.set_location('Entering:'||l_proc, 10);
1448 --
1449 -- Call all supporting business operations
1450 --
1451 hr_api.validate_bus_grp_id
1452 (p_business_group_id => p_rec.business_group_id
1453 ,p_associated_column1 => per_sps_shd.g_tab_nam
1454 || '.BUSINESS_GROUP_ID');
1455
1456 --
1457 -- Check step id
1458 --
1459 chk_step_id
1460 ( p_step_id => p_rec.step_id
1461 ,p_object_version_number => p_rec.object_version_number
1462 ,p_effective_date => p_effective_date
1463 );
1464
1465 --
1466 -- After validating the set of important attributes,
1467 -- if Multiple Message detection is enabled and at least
1468 -- one error has been found then abort further validation.
1469 --
1470 hr_multi_message.end_validation_set;
1471
1472 --
1473 -- Validate Dependent Attributes
1474 --
1475
1476 -- Call the datetrack update integrity operation
1477 --
1478 dt_update_validate
1479 (p_grade_spine_id => p_rec.grade_spine_id
1480 ,p_datetrack_mode => p_datetrack_mode
1481 ,p_validation_start_date => p_validation_start_date
1482 ,p_validation_end_date => p_validation_end_date
1483 );
1484
1485 hr_utility.set_location(l_proc, 20);
1486
1487 --
1488 chk_non_updateable_args
1489 (p_effective_date => p_effective_date
1490 ,p_rec => p_rec
1491 );
1492 --
1493 --
1494
1495 hr_utility.set_location(l_proc, 30);
1496
1497 --
1498 -- check spinal point id
1499 --
1500 chk_spinal_point_id
1501 (p_step_id => p_rec.step_id
1502 ,p_spinal_point_id => p_rec.spinal_point_id
1503 ,p_grade_spine_id => p_rec.grade_spine_id
1504 ,p_business_group_id => p_rec.business_group_id
1505 ,p_object_version_number => p_rec.object_version_number
1506 ,p_effective_date => p_effective_date
1507 );
1508
1509 hr_utility.set_location(l_proc, 40);
1510
1511 --
1512 -- Check grade spine id
1513 --
1514 chk_grade_spine_id
1515 (p_step_id => p_rec.step_id
1516 ,p_grade_spine_id => p_rec.grade_spine_id
1517 ,p_business_group_id => p_rec.business_group_id
1518 ,p_object_version_number => p_rec.object_version_number
1519 ,p_effective_date => p_effective_date
1520 );
1521
1522 hr_utility.set_location(l_proc, 50);
1523
1524 --
1525 -- Check sequence
1526 --
1527 chk_sequence
1528 (p_step_id => p_rec.step_id
1529 ,p_sequence => p_rec.sequence
1530 ,p_spinal_point_id => p_rec.spinal_point_id
1531 ,p_business_group_id => p_rec.business_group_id
1532 ,p_object_version_number => p_rec.object_version_number
1533 ,p_effective_date => p_effective_date
1534 );
1535
1536 hr_utility.set_location(l_proc, 60);
1537
1538 --
1539 -- Check the combination of spinal point id and grade spine id
1540 --
1541 chk_uniq_step_points
1542 (p_step_id => p_rec.step_id
1543 ,p_spinal_point_id => p_rec.spinal_point_id
1544 ,p_grade_spine_id => p_rec.grade_spine_id
1545 ,p_object_version_number => p_rec.object_version_number
1546 ,p_effective_date => p_effective_date
1547 );
1548
1549 hr_utility.set_location(l_proc, 70);
1550
1551 per_sps_bus.chk_ddf(p_rec);
1552 --
1553
1554 hr_utility.set_location(' Leaving:'||l_proc, 80);
1555
1556 End update_validate;
1557 --
1558 -- ----------------------------------------------------------------------------
1559 -- |---------------------------< delete_validate >----------------------------|
1560 -- ----------------------------------------------------------------------------
1561 Procedure delete_validate
1562 (p_rec in per_sps_shd.g_rec_type
1563 ,p_effective_date in date
1564 ,p_datetrack_mode in varchar2
1565 ,p_validation_start_date in date
1566 ,p_validation_end_date in date
1567 ,p_called_from_del_grd_scale in boolean --bug 4096238
1568 ) is
1569 --
1570 l_proc varchar2(72) := g_package||'delete_validate';
1571 --
1572 Begin
1573 hr_utility.set_location('Entering:'||l_proc, 10);
1574 --
1575 -- Call all supporting business operations
1576 --
1577 dt_delete_validate
1578 (p_datetrack_mode => p_datetrack_mode
1579 ,p_validation_start_date => p_validation_start_date
1580 ,p_validation_end_date => p_validation_end_date
1581 ,p_step_id => p_rec.step_id
1582 );
1583 --
1584 hr_utility.set_location(l_proc, 20);
1585
1586 chk_delete
1587 (p_step_id => p_rec.step_id
1588 ,p_spinal_point_id => p_rec.spinal_point_id
1589 ,p_grade_spine_id => p_rec.grade_spine_id
1590 ,p_effective_date => p_effective_date
1591 ,p_datetrack_mode => p_datetrack_mode
1592 ,p_called_from_del_grd_scale => p_called_from_del_grd_scale -- bug 4096238
1593 );
1594
1595 hr_utility.set_location(' Leaving:'||l_proc, 30);
1596 End delete_validate;
1597 --
1598 end per_sps_bus;