1 Package Body per_spp_bus as
2 /* $Header: pespprhi.pkb 120.1.12000000.2 2007/08/30 07:19:59 ande noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_spp_bus.'; -- Global package name
9 -- Start of 3335915
10 g_debug boolean := hr_utility.debug_enabled;
11 -- End of 3335915
12
13 --
14 -- The following two global variables are only to be
15 -- used by the return_legislation_code function.
16 --
17 g_legislation_code varchar2(150) := NULL;
18 g_placement_id number := NULL;
19 --
20 -- ---------------------------------------------------------------------------
21 -- |-------------------------< chk_assignment_id >--------------------------|
22 -- ---------------------------------------------------------------------------
23 --
24 -- Description:
25 -- - Validates that a assignment_id exists in table per_all_assignments_f.
26 -- - Checks that the assignment_id is not null
27 -- - Check if a placement has already been created for the assignment_id.
28 --
29 -- Pre-conditions:
30 -- None.
31 --
32 -- In Arguments:
33 -- p_assignment_id
34 --
35 --
36 -- Post Success:
37 -- If a row does exist in per_all_assignments for the given assignment id then
38 -- processing continues.
39 --
40 -- Post Failure:
41 -- If a row does not exist in per_all_assignments_f for the given assignment id
42 -- then an application error will be raised and processing is terminated.
43 --
44 -- Access Status:
45 -- Internal Table Handler Use Only.
46 --
47 -- {End Of Comments}
48 -- ----------------------------------------------------------------------------
49 procedure chk_assignment_id
50 (p_assignment_id in per_spinal_point_placements_f.assignment_id%TYPE
51 ,p_datetrack_mode in varchar2
52 ,p_placement_id in per_spinal_point_placements_f.placement_id%TYPE
53 ,p_object_version_number in
54 per_spinal_point_placements_f.object_version_number%TYPE
55 ,p_effective_date in date
56 )
57 -- Bug 2488727 added parameter effective date
58 is
59 --
60 l_proc varchar2(72) := g_package||'chk_assignment_id';
61 --
62 l_api_updating boolean;
63 l_assignment_id number;
64 l_placement_id number;
65 --
66 cursor csr_valid_assignment_id is
67 select assignment_id paa
68 from per_all_assignments_f paa
69 where paa.assignment_id = p_assignment_id
70 and p_effective_date between
71 effective_start_date and effective_end_date;
72
73 cursor csr_placement_exists is
74 select placement_id
75 from per_spinal_point_placements_f
76 where assignment_id = p_assignment_id
77 and p_effective_date between
78 effective_start_date and effective_end_date;
79 --
80 begin
81 hr_utility.set_location('Entering:'|| l_proc, 10);
82 --
83 -- Check mandatory parameters have been set
84 --
85 hr_api.mandatory_arg_error
86 (p_api_name => l_proc
87 ,p_argument => 'assignment_id'
88 ,p_argument_value => p_assignment_id
89 );
90 --
91 hr_utility.set_location(l_proc, 20);
92 --
93 -- Check if inserting or updating with modified values
94 --
95 l_api_updating := per_spp_shd.api_updating
96 (p_placement_id => p_placement_id
97 ,p_effective_date => p_effective_date -- Bug 2488727
98 ,p_object_version_number => p_object_version_number
99 );
100 --
101 if ((l_api_updating and per_spp_shd.g_old_rec.assignment_id <> p_assignment_id)
102 or
103 (NOT l_api_updating))
104 then
105 hr_utility.set_location(l_proc, 30);
106 --
107 -- Check that the Assignment ID is linked to a
108 -- valid assignment_id on per_all_assignments
109 --
110 open csr_valid_assignment_id;
111 fetch csr_valid_assignment_id into l_assignment_id;
112 if csr_valid_assignment_id%notfound then
113 --
114 close csr_valid_assignment_id;
115 fnd_message.set_name('PER', 'HR_289224_SPP_ASSIGNMENT_CHK');
116 hr_utility.raise_error;
117 --
118 else
119 close csr_valid_assignment_id;
120 if(p_datetrack_mode = 'INSERT') then
121 open csr_placement_exists;
122 fetch csr_placement_exists into l_placement_id;
123 if csr_placement_exists%found then
124 --
125 close csr_placement_exists;
126 fnd_message.set_name('PER', 'HR_289225_SPP_PLACEMENT_EXIST');
127 hr_utility.raise_error;
128 --
129 else
130 close csr_placement_exists;
131 end if;
132 end if;
133 end if;
134 end if;
135 --
136 hr_utility.set_location(' Leaving:'|| l_proc, 40);
137 end chk_assignment_id;
138 --
139 -- ---------------------------------------------------------------------------
140 -- |-----------------------< chk_pay_ass_ceiling >--------------------------|
141 -- ---------------------------------------------------------------------------
142 --
143 -- Description:
144 -- check if the step_id passed is greater than the step_id for
145 -- the special_ceiling_step_id or the pay scale ceiling_step_id.
146 --
147 -- Pre-conditions:
148 -- None.
149 --
150 -- In Arguments:
151 -- p_step_id
152 -- p_parent_spine_id
153 -- p_assignment_id
154 --
155 -- Post Success:
156 -- If the the step_id's sequence is less than or equal to either of the
157 -- ceilings then continue.
158 --
159 -- Post Failure:
160 -- If the the step_id's sequence is greater than or equal to either of the
161 -- ceilings then throw error.
162 --
163 -- Access Status:
164 -- Internal Table Handler Use Only.
165 --
166 -- {End Of Comments}
167 -- ----------------------------------------------------------------------------
168 procedure chk_pay_ass_ceiling
169 (p_step_id in per_spinal_point_placements_f.step_id%TYPE
170 ,p_parent_spine_id in per_spinal_point_placements_f.parent_spine_id%TYPE
171 ,p_assignment_id in per_spinal_point_placements_f.assignment_id%TYPE
172 ,p_effective_date in date
173 )
174 is
175 --
176 l_proc varchar2(72) := g_package||'chk_pay_ass_ceiling';
177 --
178 l_api_updating boolean;
179 l_step_number number;
180 l_grade_ceiling number;
181 l_assignment_ceiling number;
182 l_grade_id per_grades.grade_id%TYPE;
183 l_ceiling_to_use number;
184 max_ceiling_number number;
185 max_special_ceiling_number number;
186 --
187 Cursor csr_special_ceiling is
188 select special_ceiling_step_id,
189 grade_id
190 from per_all_assignments_f
191 where assignment_id = p_assignment_id
192 and p_effective_date between effective_start_date
193 and effective_end_date;
194 --
195 Cursor csr_grade_ceiling is
196 select pgs.ceiling_step_id
197 from per_grade_spines_f pgs
198 where pgs.parent_spine_id = p_parent_spine_id
199 and pgs.grade_id = l_grade_id
200 and p_effective_date between pgs.effective_start_date
201 and pgs.effective_end_date;
202 --
203 begin
204 --
205 hr_utility.set_location('Entering:'|| l_proc, 10);
206 --
207 -- Check mandatory parameters have been set
208 --
209 hr_api.mandatory_arg_error
210 (p_api_name => l_proc
211 ,p_argument => 'assignment_id'
212 ,p_argument_value => p_assignment_id
213 );
214 --
215 hr_api.mandatory_arg_error
216 (p_api_name => l_proc
217 ,p_argument => 'step_id'
218 ,p_argument_value => p_step_id
219 );
220 --
221 hr_api.mandatory_arg_error
222 (p_api_name => l_proc
223 ,p_argument => 'parent_spine_id'
224 ,p_argument_value => p_parent_spine_id
225 );
226 --
227 hr_utility.set_location(l_proc, 20);
228 --
229 -- First check if a special ceiling has been entered, if so then use this
230 -- ceiling instead of grade ceiling, else use the grade ceiling
231 --
232 open csr_special_ceiling;
233 fetch csr_special_ceiling into l_assignment_ceiling, l_grade_id;
234 --
235 if l_assignment_ceiling is null then
236 --
237 hr_utility.set_location(l_proc,30);
238 --
239 -- Use the grade scale ceiling
240 --
241 open csr_grade_ceiling;
242 fetch csr_grade_ceiling into l_grade_ceiling;
243 close csr_grade_ceiling;
244 --
245 hr_utility.set_location(l_proc||' Grade Ceiling - '||l_grade_ceiling,40);
246 --
247 l_ceiling_to_use := l_grade_ceiling;
248 --
249 elsif csr_special_ceiling%found then
250 --
251 hr_utility.set_location(l_proc,50);
252 --
253 l_ceiling_to_use := l_assignment_ceiling;
254 --
255 end if;
256 --
257 close csr_special_ceiling;
258 --
259 hr_utility.set_location('l_ceiling_to_use - '||l_ceiling_to_use,60);
260 -- --------------------------------------------------------------------------
261 -- Procedure done every time an update occurs as it is possible to
262 -- be on a step id and then assign a step id as the limit which is below
263 -- the current step id, in which case it needs to through an error
264 -- --------------------------------------------------------------------------
265 select sps.sequence
266 into max_ceiling_number
267 from per_spinal_point_steps_f sps,
268 per_grade_spines_f pgs
269 where sps.step_id = l_ceiling_to_use
270 and sps.grade_spine_id = pgs.grade_spine_id
271 and pgs.parent_spine_id = p_parent_spine_id
272 and p_effective_date between sps.effective_start_date
273 and sps.effective_end_date
274 and p_effective_date between pgs.effective_start_date
275 and pgs.effective_end_date;
276
277 -- --------------------------------------------------------------------------
278 -- Select the sequence number for the passed step id
279 -- --------------------------------------------------------------------------
280 select sps.sequence
281 into l_step_number
282 from per_spinal_point_steps_f sps
283 where sps.step_id = p_step_id
284 and p_effective_date between sps.effective_start_date
285 and sps.effective_end_date;
286 -- --------------------------------------------------------------------------
287 -- compare the two ceiling step_id's to the passed step_id
288 -- --------------------------------------------------------------------------
289 if (l_step_number > max_ceiling_number) then
290 fnd_message.set_name('PER', 'HR_289276_SPP_CEILING_CHK');
291 hr_utility.raise_error;
292 end if;
293 --
294 hr_utility.set_location(' Leaving:'|| l_proc, 999);
295 --
296 end chk_pay_ass_ceiling;
297 --
298 -- ---------------------------------------------------------------------------
299 -- |------------------------------< chk_reason >-----------------------------|
300 -- ---------------------------------------------------------------------------
301 --
302 -- Desciption :
303 --
304 -- Validates that the reason being passed is valid.
305 --
306 -- Pre-conditions :
307 --
308 -- In Arguments :
309 -- p_reason
310 -- p_placement_id
311 -- p_effective_date
312 --
313 -- Post Success :
314 -- Processing continues
315 --
316 -- Post Failure :
317 -- An application error will be raised and processing is
318 -- terminated
319 --
320 -- Access Status :
321 -- Internal Row Handler Use Only
322 --
323 -- {End of Comments}
324 --
325 -- ---------------------------------------------------------------------------
326 --
327 PROCEDURE chk_reason
328 (p_reason IN per_spinal_point_placements_f.reason%TYPE
329 ,p_effective_date IN date) IS
330 --
331 -- Declare Local Variables
332 --
333 l_proc varchar2(72) := g_package||'chk_reason';
334 --
335 BEGIN
336 --
337 hr_utility.set_location('Entering:'||l_proc, 10);
338 --
339 -- Only proceed with validation if :
340 -- there is a reason being passed
341 --
342 IF (p_reason is not null) then
343 --
344 hr_utility.set_location(l_proc, 20);
345 --
346 -- Check that the reason type exists in HR_LOOKUPS
347 --
348 IF hr_api.not_exists_in_hr_lookups
349 (p_effective_date => p_effective_date
350 ,p_lookup_type => 'PLACEMENT_REASON'
351 ,p_lookup_code => p_reason) THEN
352 --
353 hr_utility.set_location(l_proc, 30);
354 --
355 fnd_message.set_name('PER', 'HR_289266_SPP_INVAL_REASON');
356 hr_utility.raise_error;
357 --
358 END IF;
359 --
360 END IF;
361 --
362 hr_utility.set_location(' Leaving:'||l_proc, 40);
363 --
364 END chk_reason;
365 -- ---------------------------------------------------------------------------
366 -- |--------------------< chk_object_version_number >-----------------------|
367 -- ---------------------------------------------------------------------------
368 --
369 -- Description:
370 -- - Validates that the object version number is correct for the record
371 -- as of the effective date
372 --
373 -- Pre-conditions:
374 -- None.
375 --
376 -- In Arguments:
377 -- p_placement_id
378 -- p_effective_date
379 -- p_object_version_number
380 --
381 --
382 -- Post Success:
383 -- If object_version_number is correct then processing continues.
384 --
385 -- Post Failure:
386 -- If object_version_number is not correct
387 -- then an application error will be raised and processing is terminated.
388 --
389 -- Access Status:
390 -- Internal Table Handler Use Only.
391 --
392 -- {End Of Comments}
393 -- ----------------------------------------------------------------------------
394 procedure chk_object_version_number
395 (p_placement_id in per_spinal_point_placements_f.placement_id%TYPE
396 ,p_object_version_number in per_spinal_point_placements_f.object_version_number%TYPE
397 ,p_effective_date in date
398 )
399 is
400 --
401 l_proc varchar2(72) := g_package||'chk_object_version_number';
402 --
403 l_api_updating boolean;
404 l_object_version_number number;
405 --
406 cursor csr_valid_ovn is
407 select object_version_number spp
408 from per_spinal_point_placements_f spp
409 where spp.placement_id = p_placement_id
410 and p_effective_date between spp.effective_start_date
411 and spp.effective_end_date
412 and object_version_number = p_object_version_number;
413
414
415
416 begin
417 hr_utility.set_location('Entering:'|| l_proc, 10);
418 --
419 -- Check mandatory parameters have been set
420 --
421 hr_api.mandatory_arg_error
422 (p_api_name => l_proc
423 ,p_argument => 'object_version_number'
424 ,p_argument_value => p_object_version_number
425 );
426 --
427 hr_utility.set_location(l_proc, 20);
428 --
429 -- Check if inserting or updating with modified values
430 --
431 l_api_updating := per_spp_shd.api_updating
435 );
432 (p_placement_id => p_placement_id
433 ,p_effective_date => p_effective_date
434 ,p_object_version_number => p_object_version_number
436 --
437 hr_utility.set_location(l_proc, 30);
438 --
439 -- Check that the object version number is correct for the effective date
440 --
441 open csr_valid_ovn;
442 fetch csr_valid_ovn into l_object_version_number;
443 if csr_valid_ovn%notfound then
444 --
445 close csr_valid_ovn;
446 fnd_message.set_name('PER', 'HR_289256_SPP_OBJECT_VER_NUM');
447 hr_utility.raise_error;
448 --
449 else
450 close csr_valid_ovn;
451 end if;
452 --
453 hr_utility.set_location(' Leaving:'|| l_proc, 40);
454 end chk_object_version_number;
455 --
456 -- ---------------------------------------------------------------------------
457 -- |-------------------------< chk_auto_inc_flag >--------------------------|
458 -- ---------------------------------------------------------------------------
459 --
460 -- Description:
461 -- - Checks that the auto_increment_flag has a value of 'Y' or 'N'.
462 --
463 -- Pre-conditions:
464 -- None.
465 --
466 -- In Arguments:
467 -- p_auto_increment_flag
468 --
469 -- Post Success:
470 -- If the flag is set to either 'Y' or 'N' then the processing continues.
471 --
472 -- Post Failure:
473 -- If the flag is not set to either 'Y' or 'N' then an application error
474 -- will be raised and processing is terminated.
475 --
476 -- Access Status:
477 -- Internal Table Handler Use Only.
478 --
479 -- {End Of Comments}
480 -- ----------------------------------------------------------------------------
481 procedure chk_auto_inc_flag
482 (p_auto_increment_flag in per_spinal_point_placements_f.auto_increment_flag%TYPE
483 ,p_placement_id in per_spinal_point_placements_f.placement_id%TYPE
484 ,p_increment_number in per_spinal_point_placements_f.increment_number%TYPE
485 ,p_object_version_number in per_spinal_point_placements_f.object_version_number%TYPE
486 )
487 is
488 --
489 l_proc varchar2(72) := g_package||'chk_auto_inc_flag';
490 --
491 l_api_updating boolean;
492 l_placement_id number;
493 l_effective_date date := sysdate; -- not used
494 --
495 --
496 begin
497 hr_utility.set_location('Entering:'|| l_proc, 10);
498 --
499 -- Check mandatory parameters have been set
500 --
501 hr_api.mandatory_arg_error
502 (p_api_name => l_proc
503 ,p_argument => 'auto_increment_flag'
504 ,p_argument_value => p_auto_increment_flag
505 );
506 --
507 hr_utility.set_location(l_proc, 20);
508 hr_utility.set_location('p_increment_number:'||p_increment_number,23);
509 hr_utility.set_location('p_auto_increment_flag:'||p_auto_increment_flag, 24);
510 /*
511 -- The following section was commited out to deal with old records.
512 -- Increment Number is a new column and so there are cases where auto_inc_flag
513 -- will be 'Y' and not changed and the user does not enter a increment number
514 -- but becaudse of how this works the checks will not be done as old value
515 -- will equal new value and l_api_updating will be true.
516 --
517 -- Check if inserting or updating with modified values
518 --
519 l_api_updating := per_spp_shd.api_updating
520 (p_placement_id => p_placement_id
521 ,p_effective_date => l_effective_date not used
522 ,p_object_version_number => p_object_version_number
523 );
524 --
525 if ((l_api_updating and per_spp_shd.g_old_rec.auto_increment_flag <> p_auto_increment_flag)
526 or
527 (NOT l_api_updating))
528 then
529 */
530 --
531 --
532
533 hr_utility.set_location(l_proc, 30);
534 hr_utility.set_location('Auto Increment Flag:'||p_auto_increment_flag,31);
535 --
536 -- Check that the Auto Increment Flag is 'Y' or 'N'
537 If (p_auto_increment_flag not in ('Y','N') )
538 --
539 then
540 hr_utility.set_location(l_proc, 35);
541 fnd_message.set_name('PER', 'HR_289223_SPP_AUTO_INC_FLG_CHK');
542 hr_utility.raise_error;
543 --
544 hr_utility.set_location(l_proc, 22);
545 elsif (p_auto_increment_flag = 'Y') then
546 hr_utility.set_location(l_proc, 23);
547 if (NVL(p_increment_number,0) <= 0) then
548 hr_utility.set_location(l_proc, 36);
549 fnd_message.set_name('PER', 'HR_289243_SPP_INCREMENT_NUMBER');
550 hr_utility.raise_error;
551 end if;
552 hr_utility.set_location(l_proc, 40);
553 end if;
554 -- end if;
555 --
556 hr_utility.set_location(' Leaving:'|| l_proc, 45);
557 end chk_auto_inc_flag;
558 --
559 /*
560 -- Commented out as not going to inforce the reason for using 'REASON' to be
561 -- only for a step placement change. Thus allowing datetrack history to be
562 -- created if only the reason changes.
563 -- ---------------------------------------------------------------------------
567 -- Description:
564 -- |----------------------< chk_reason_only_update >------------------------|
565 -- ---------------------------------------------------------------------------
566 --
568 -- - Validates that if a reason is the only item changing to then change the
569 -- datetrack mode to correction.
570 -- (Reasoning - 'Meaning' refers to the reson that a step_id has been
571 -- changed so if you are updating only the reason then a new datetrack
572 -- record can not be created.)
573 --
574 -- Pre-conditions:
575 -- None.
576 --
577 -- In Arguments:
578 -- p_rec
579 --
580 -- Post Success:
581 -- If after checking the values being updated that the only column being
582 -- updated is the reason column then the datetrack mode is changed to
583 -- 'CORRECTION'.
584 --
585 -- Post Failure:
586 -- If there are other variables being changed then skip process and return
587 -- control to the calling process.
588 --
589 -- Post Success:
590 --
591 -- Post Failure:
592 --
593 -- Access Status:
594 -- Internal Table Handler Use Only.
595 --
596 -- {End Of Comments}
597 -- ----------------------------------------------------------------------------
598 procedure chk_reason_only_update
599 (p_rec in per_spp_shd.g_rec_type
600 ,p_datetrack_mode in out nocopy varchar2
601 ,p_effective_date in date
602 ,p_validation_start_date in out nocopy date
603 ,p_validation_end_date in out nocopy date
604 )
605 is
606 --
607 l_proc varchar2(72) := g_package||'chk_reason_only_update';
608 --
609 l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
610 l_business_group_id per_spinal_point_placements_f.business_group_id%TYPE;
611 l_assignment_id per_spinal_point_placements_f.assignment_id%TYPE;
612 l_step_id per_spinal_point_placements_f.step_id%TYPE;
613 l_auto_inc_flag per_spinal_point_placements_f.auto_increment_flag%TYPE;
614 l_parent_spine_id per_spinal_point_placements_f.parent_spine_id%TYPE;
615 l_reason per_spinal_point_placements_f.reason%TYPE;
616 l_old_rec per_spp_shd.g_rec_type;
617 --
618
619 cursor csr_reason_1 is
620 select placement_id,
621 business_group_id,
622 assignment_id,
623 step_id,
624 auto_increment_flag,
625 parent_spine_id,
626 reason
627 from per_spinal_point_placements_f
628 where placement_id = p_rec.placement_id
629 and p_effective_date between effective_start_date
630 and effective_end_date;
631
632
633 begin
634 hr_utility.set_location(l_proc, 10);
635 --
636 -- Check that the record matches bar the reason
637 --
638 open csr_reason_1;
639 fetch csr_reason_1 into l_placement_id,
640 l_business_group_id,
641 l_assignment_id,
642 l_step_id,
643 l_auto_inc_flag,
644 l_parent_spine_id,
645 l_reason;
646 --
647 close csr_reason_1;
648
649 if (NVL(l_reason, 'XZX') <> NVL(p_rec.reason , 'XZX')
650 and (l_placement_id <> p_rec.placement_id
651 or l_business_group_id <> p_rec.business_group_id
652 or l_assignment_id <> p_rec.assignment_id
653 or l_auto_inc_flag <> p_rec.auto_increment_flag
654 or l_step_id <> p_rec.step_id
655 or l_parent_spine_id <> p_rec.parent_spine_id)) then
656 null;
657 elsif (l_reason = p_rec.reason) then
658 null;
659
660 else
661 p_datetrack_mode := 'CORRECTION';
662 p_validation_start_date := p_rec.effective_start_date;
663 p_validation_end_date := p_rec.effective_end_date;
664
665 end if;
666
667 --
668 hr_utility.set_location(' Leaving:'|| l_proc, 40);
669 end chk_reason_only_update;
670 */
671 --
672 -- ---------------------------------------------------------------------------
673 -- |---------------------< chk_parent_spine_step_id >-----------------------|
674 -- ---------------------------------------------------------------------------
675 --
676 -- Description:
677 -- - Validates that a parent_spine_id exists in the table per_parent_spines
678 -- and that the step_id exists in table per_spinal_point_steps_f.
679 -- (When checking the parent_spine_id, it is also copared to the table
680 -- per_grade_spines_f. This is so that grade_spine_id can be extracted
681 -- so that when checking the step_id you can use the grade spine id to
682 -- check that the step_id is a valid step id for the parent spine.)
683 -- - Checks that the effective dates for the parent_spine_id being passed is
684 -- between the effective dates for the parent_spine_id in the table
685 -- per_grade_spines_f.
686 -- - Checks that the effective dates for the step_id being passed is between
687 -- the effective dates for the step_id in the table
688 -- per_spinal_point_steps_f.
689 --
690 --
691 -- Pre-conditions:
692 -- None.
693 --
694 -- In Arguments:
695 -- p_step_id
696 -- p_parent_spine_id
697 -- p_effective_start_date
701 -- Post Success:
698 -- p_effective_end_date
699 --
700 --
702 -- If a row does exist in per_spinal_point_steps_f or per_grade_spines_f for
703 -- the given parent spine id or the step id then processing continues.
704 --
705 -- Post Failure:
706 -- If a row does not exist in per_spinal_point_steps_f or per_grade_spines_f
707 -- for the given parent spine id or the step id then an application error
708 -- will be raised and processing is terminated.
709 --
710 -- Access Status:
711 -- Internal Table Handler Use Only.
712 --
713 -- {End Of Comments}
714 -- ----------------------------------------------------------------------------
715 procedure chk_parent_spine_step_id
716 (p_step_id in per_spinal_point_placements_f.step_id%TYPE
717 ,p_parent_spine_id in per_spinal_point_placements_f.parent_spine_id%TYPE
718 ,p_effective_start_date in
719 per_spinal_point_placements_f.effective_start_date%TYPE
720 ,p_effective_end_date in
721 per_spinal_point_placements_f.effective_end_date%TYPE
722 ,p_placement_id in per_spinal_point_placements_f.placement_id%TYPE
723 ,p_object_version_number in
724 per_spinal_point_placements_f.object_version_number%TYPE
725 )
726 is
727 --
728 l_proc varchar2(72) := g_package||'chk_parent_spine_step_id';
729 --
730 l_api_updating boolean;
731 l_parent_spine_id number;
732 l_step_id number;
733 --l_effective_date date := sysdate; -- bug 2491732
734 l_effective_date date := p_effective_start_date; -- Bug 2491732
735 l_grade_spine_id number;
736 --
737 -- Validate if the parent spine alone is invalid
738 --
739 cursor csr_valid_parent_spine is
740 select gs.parent_spine_id
741 from per_grade_spines_f gs
742 where gs.parent_spine_id = p_parent_spine_id
743 and p_effective_start_date between gs.effective_start_date
744 and gs.effective_end_date;
745 --
746 -- Validates the dates and that the parent spine exists and step exists
747 --
748 cursor csr_valid_parent_spine_step_id is
749 select gs.parent_spine_id,
750 gs.grade_spine_id
751 from per_grade_spines_f gs,
752 per_spinal_point_steps_f sps
753 where gs.parent_spine_id = p_parent_spine_id
754 and gs.grade_spine_id = sps.grade_spine_id
755 and sps.step_id = p_step_id
756 and p_effective_start_date between gs.effective_start_date
757 and gs.effective_end_date
758 and p_effective_start_date between sps.effective_start_date
759 and sps.effective_end_date;
760
761
762 begin
763 hr_utility.set_location('Entering:'|| l_proc, 10);
764 hr_utility.set_location('Step ID :'|| p_step_id, 10);
765 hr_utility.set_location('Parent Spine ID :'|| p_parent_spine_id,10);
766 hr_utility.set_location('Start Date in chk :'|| p_effective_start_date,10);
767 hr_utility.set_location('End Date :'|| p_effective_end_date,10);
768 hr_utility.set_location('Placement ID :'|| p_placement_id,10);
769 --
770 -- Check mandatory parameters have been set
771 --
772 hr_api.mandatory_arg_error
773 (p_api_name => l_proc
774 ,p_argument => 'step_id'
775 ,p_argument_value => p_step_id
776 );
777 --
778 hr_api.mandatory_arg_error
779 (p_api_name => l_proc
780 ,p_argument => 'parent_spine_id'
781 ,p_argument_value => p_parent_spine_id
782 );
783 --
784 hr_api.mandatory_arg_error
785 (p_api_name => l_proc
786 ,p_argument => 'effective_start_date'
787 ,p_argument_value => p_effective_start_date
788 );
789 --
790 hr_api.mandatory_arg_error
791 (p_api_name => l_proc
792 ,p_argument => 'effective_end_date'
793 ,p_argument_value => p_effective_end_date
794 );
795 --
796 hr_utility.set_location(l_proc, 20);
797 --
798 -- Check if inserting or updating with modified values
799 --
800 l_api_updating := per_spp_shd.api_updating
801 (p_placement_id => p_placement_id
802 ,p_effective_date => l_effective_date
803 ,p_object_version_number => p_object_version_number
804 );
805 --
806 hr_utility.set_location(l_proc, 25);
807 --
808 if ((l_api_updating and (per_spp_shd.g_old_rec.parent_spine_id <> p_parent_spine_id)
809 or (per_spp_shd.g_old_rec.step_id <> p_step_id))
810 or
811 (NOT l_api_updating))
812 then
813 hr_utility.set_location(l_proc, 30);
814 --
815 -- Check that the Parent Spine ID is linked to a
816 -- valid parent_spine_id on per_parent_spines table
817 -- and get the grade_spine_id from the per_grade_spines_f table
818 -- based on the parent_spine_id
819 --
820 open csr_valid_parent_spine_step_id;
821 fetch csr_valid_parent_spine_step_id into l_parent_spine_id,l_grade_spine_id;
822 if csr_valid_parent_spine_step_id%notfound then
823 --
827 --
824 close csr_valid_parent_spine_step_id;
825 --
826 -- Check to see if parent spine is invalid
828 open csr_valid_parent_spine;
829 fetch csr_valid_parent_spine into l_parent_spine_id;
830 if csr_valid_parent_spine%notfound then
831
832 fnd_message.set_name('PER', 'HR_289226_SPP_PARENT_SPINE');
833
834 else
835
836 -- If parent spine is valid the step must be invalid
837
838 fnd_message.set_name('PER', 'HR_289227_SPP_STEP_ID');
839
840 end if;
841
842 close csr_valid_parent_spine;
843
844 hr_utility.raise_error;
845 --
846 else
847 close csr_valid_parent_spine_step_id;
848 end if;
849 --
850 end if;
851 --
852 hr_utility.set_location(' Leaving:'|| l_proc, 40);
853 end chk_parent_spine_step_id;
854 --
855 -- Start of fix for Bug 3335915
856 -- ----------------------------------------------------------------------------
857 -- |----------------------< chk_future_asg_changes >--------------------------|
858 -- ----------------------------------------------------------------------------
859 --
860 -- Description:
861 -- - Validates that the parent assginment records does not have grade change
862 -- in future
863 -- - Should only be called in DELETE_NEXT_MODE, FUTURE_CHANGES and
864 -- UPDATE_OVEERRIDE datetrack mode.
865 -- - This validation not applicable if called from maintain_spp_asg
866 -- procedure.
867 --
868 -- Pre-conditions:
869 -- None.
870 --
871 -- In Arguments:
872 -- p_placement_id
873 -- p_effective_date
874 -- p_datetrack_mode
875 --
876 --
877 -- Post Success:
878 -- Continues Processing
879 --
880 -- Post Failure:
881 -- Error is raised and processing stops, if parent assigneent has future
882 -- grade changes.
883 --
884 -- Access Status:
885 -- Internal Table Handler Use Only.
886 --
887 -- {End Of Comments}
888 -- ----------------------------------------------------------------------------
889 procedure chk_future_asg_changes
890 (p_placement_id in per_spinal_point_placements_f.placement_id%TYPE
891 ,p_effective_date in date
892 ,p_datetrack_mode in varchar2
893 )
894 as
895
896 -- Define local variables
897 --
898 l_end_date date := hr_api.g_eot;
899 l_grade_id number;
900 l_dummy varchar2(1);
901 l_asg_id number;
902 l_proc varchar2(20) := 'CHK_FUTURE_ASG_CHNG';
903 --
904 --
905 --
906
907 --
908 --Cursor to select current assignemnt grade.
909 --
910 cursor c_curr_grade is
911 select paf.grade_id , paf.assignment_id
912 from per_all_assignments_f paf, per_spinal_point_placements_f psf
913 where paf.assignment_id = psf.assignment_id
914 and psf.placement_id = p_placement_id
915 and p_effective_date between psf.effective_start_Date and psf.effective_end_date
916 and p_effective_date between paf.effective_start_Date and paf.effective_end_date;
917
918 --
919 -- Cursor to select effective end date of next spp record.
920 --
921 cursor c_next_spp_eed is
922 select pspf.effective_end_date
923 from per_spinal_point_placements_f pspf
924 where pspf.placement_id = p_placement_id
925 and pspf.effective_start_date > p_effective_Date
926 order by pspf.effective_end_date ;
927
928 --
929 -- cursor to check for change in grade in future assignemnt rec.
930 --
931 cursor c_future_grade_chg (p_grade_id number, p_end_date date) is
932 select 'X'
933 from per_all_assignments_f paf
934 where paf.assignment_id = l_asg_id
935 and paf.grade_id <> p_grade_id
936 and paf.effective_end_date between p_effective_date and p_end_date;
937
938
939 begin
940 if g_debug then
941 hr_utility.set_location('Entering:'|| l_proc, 10);
942 hr_utility.set_location('p_placement_id'|| p_placement_id , 10);
943 hr_utility.set_location('p_effective_Date'|| p_effective_date , 10);
944 end if;
945 --
946 -- IF delete mode is Delete next change,get the
947 -- EED of the next record. otherwise it has to be EOT.
948 --
949 IF p_datetrack_mode = 'DELETE_NEXT_CHANGE' then
950 open c_next_spp_eed;
951 fetch c_next_spp_eed into l_end_date;
952
953 if c_next_spp_eed%notfound then
954 l_end_date := hr_api.g_eot;
955 end if;
956 close c_next_spp_eed;
957
958 if g_debug then
959 hr_utility.set_location( l_proc ||': l_end_date '|| to_char(l_end_date), 30);
960 end if;
961
962 end if; -- mode = DNC
963
964 open c_curr_grade;
965 fetch c_curr_grade into l_grade_id,l_asg_id;
966 if c_curr_grade%notfound then
967 null;
968 end if;
969 close c_curr_grade;
970
971 if g_debug then
972 hr_utility.set_location( l_proc ||': grade ID '|| to_char(l_grade_id), 40);
973 hr_utility.set_location( l_proc ||': ASG ID '|| to_char(l_asg_id), 40);
974 end if;
975
976 open c_future_grade_chg(p_grade_id =>l_grade_id
980 -- we have found grade change in future
977 ,p_end_date => l_end_date);
978 fetch c_future_grade_chg into l_dummy;
979 if c_future_grade_chg%found then
981 -- This should be avoided and error should be raised
982 --
983 close c_future_grade_chg;
984 --null;
985 if g_debug then
986 hr_utility.set_location( l_proc ||'raise error', 60);
987 end if;
988
989 fnd_message.set_name (800,'PER_449912_FUT_GRD_CHG');
990 fnd_message.raise_error;
991
992 end if ;
993
994 if c_future_grade_chg%isopen then
995 close c_future_grade_chg;
996 end if;
997
998 if g_debug then
999 hr_utility.set_location( 'leaving :'|| l_proc , 70);
1000 end if;
1001
1002 end chk_future_asg_changes;
1003 --
1004 -- End of fix for Bug 3335915
1005 -- ---------------------------------------------------------------------------
1006 -- |----------------------< set_security_group_id >--------------------------|
1007 -- ---------------------------------------------------------------------------
1008 --
1009 Procedure set_security_group_id
1010 (p_placement_id in number
1011 ) is
1012 --
1013 -- Declare cursor
1014 --
1015 cursor csr_sec_grp is
1016 select pbg.security_group_id
1017 from per_business_groups pbg
1018 , per_spinal_point_placements_f spp
1019 where spp.placement_id = p_placement_id
1020 and pbg.business_group_id = spp.business_group_id;
1021 --
1022 -- Declare local variables
1023 --
1024 l_security_group_id number;
1025 l_proc varchar2(72) := g_package||'set_security_group_id';
1026 --
1027 begin
1028 --
1029 hr_utility.set_location('Entering:'|| l_proc, 10);
1030 --
1031 -- Ensure that all the mandatory parameter are not null
1032 --
1033 hr_api.mandatory_arg_error
1034 (p_api_name => l_proc
1035 ,p_argument => 'placement_id'
1036 ,p_argument_value => p_placement_id
1037 );
1038 --
1039 open csr_sec_grp;
1040 fetch csr_sec_grp into l_security_group_id;
1041 --
1042 if csr_sec_grp%notfound then
1043 --
1044 close csr_sec_grp;
1045 --
1046 -- The primary key is invalid therefore we must error
1047 --
1048 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1049 fnd_message.raise_error;
1050 --
1051 end if;
1052 close csr_sec_grp;
1053 --
1054 -- Set the security_group_id in CLIENT_INFO
1055 --
1056 hr_api.set_security_group_id
1057 (p_security_group_id => l_security_group_id
1058 );
1059 --
1060 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1061 --
1062 end set_security_group_id;
1063 --
1064 -- ---------------------------------------------------------------------------
1065 -- |---------------------< return_legislation_code >-------------------------|
1066 -- ---------------------------------------------------------------------------
1067 --
1068 Function return_legislation_code
1069 (p_placement_id in number
1070 )
1071 Return Varchar2 Is
1072 --
1073 -- Declare cursor
1074 --
1075 cursor csr_leg_code is
1076 select pbg.legislation_code
1077 from per_business_groups pbg
1078 , per_spinal_point_placements_f spp
1079 where spp.placement_id = p_placement_id
1080 and pbg.business_group_id = spp.business_group_id;
1081 --
1082 -- Declare local variables
1083 --
1084 l_legislation_code varchar2(150);
1085 l_proc varchar2(72) := g_package||'return_legislation_code';
1086 --
1087 Begin
1088 --
1089 hr_utility.set_location('Entering:'|| l_proc, 10);
1090 --
1091 -- Ensure that all the mandatory parameter are not null
1092 --
1093 hr_api.mandatory_arg_error
1094 (p_api_name => l_proc
1095 ,p_argument => 'placement_id'
1096 ,p_argument_value => p_placement_id
1097 );
1098 --
1099 if ( nvl(per_spp_bus.g_placement_id, hr_api.g_number)
1100 = p_placement_id) then
1101 --
1102 -- The legislation code has already been found with a previous
1103 -- call to this function. Just return the value in the global
1104 -- variable.
1105 --
1106 l_legislation_code := per_spp_bus.g_legislation_code;
1107 hr_utility.set_location(l_proc, 20);
1108 else
1109 --
1110 -- The ID is different to the last call to this function
1111 -- or this is the first call to this function.
1112 --
1113 open csr_leg_code;
1114 fetch csr_leg_code into l_legislation_code;
1115 --
1116 if csr_leg_code%notfound then
1117 --
1118 -- The primary key is invalid therefore we must error
1119 --
1120 close csr_leg_code;
1121 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1122 fnd_message.raise_error;
1123 end if;
1124 hr_utility.set_location(l_proc,30);
1125 --
1126 -- Set the global variables so the values are
1127 -- available for the next call to this function.
1128 --
1129 close csr_leg_code;
1133 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1130 per_spp_bus.g_placement_id := p_placement_id;
1131 per_spp_bus.g_legislation_code := l_legislation_code;
1132 end if;
1134 return l_legislation_code;
1135 end return_legislation_code;
1136 --
1137 -- ----------------------------------------------------------------------------
1138 -- |-----------------------< chk_non_updateable_args >------------------------|
1139 -- ----------------------------------------------------------------------------
1140 -- {Start Of Comments}
1141 --
1142 -- Description:
1143 -- This procedure is used to ensure that non updateable attributes have
1144 -- not been updated. If an attribute has been updated an error is generated.
1145 --
1146 -- Pre Conditions:
1147 -- g_old_rec has been populated with details of the values currently in
1148 -- the database.
1149 --
1150 -- In Arguments:
1151 -- p_rec has been populated with the updated values the user would like the
1152 -- record set to.
1153 --
1154 -- Post Success:
1155 -- Processing continues if all the non updateable attributes have not
1156 -- changed.
1157 --
1158 -- Post Failure:
1159 -- An application error is raised if any of the non updatable attributes
1160 -- have been altered.
1161 --
1162 -- {End Of Comments}
1163 -- ----------------------------------------------------------------------------
1164 Procedure chk_non_updateable_args
1165 (p_effective_date in date
1166 ,p_rec in per_spp_shd.g_rec_type
1167 ) IS
1168 --
1169 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
1170 l_error EXCEPTION;
1171 l_argument varchar2(30);
1172 --
1173 Begin
1174 --
1175 -- Only proceed with the validation if a row exists for the current
1176 -- record in the HR Schema.
1177 --
1178 hr_utility.set_location(' Entering:'||l_proc,10);
1179 IF NOT per_spp_shd.api_updating
1180 (p_placement_id => p_rec.placement_id
1181 ,p_effective_date => p_effective_date
1182 ,p_object_version_number => p_rec.object_version_number
1183 ) THEN
1184 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
1185 fnd_message.set_token('PROCEDURE ', l_proc);
1186 fnd_message.set_token('STEP ', '5');
1187 fnd_message.raise_error;
1188 END IF;
1189 --
1190 -- EDIT_HERE: Add checks to ensure non-updateable args have
1191 -- not been updated.
1192 --
1193 EXCEPTION
1194 WHEN l_error THEN
1195 hr_api.argument_changed_error
1196 (p_api_name => l_proc
1197 ,p_argument => l_argument);
1198 WHEN OTHERS THEN
1199 RAISE;
1200 hr_utility.set_location(' Leaving:'||l_proc,20);
1201 End chk_non_updateable_args;
1202 --
1203 -- ----------------------------------------------------------------------------
1204 -- |--------------------------< dt_update_validate >--------------------------|
1205 -- ----------------------------------------------------------------------------
1206 -- {Start Of Comments}
1207 --
1208 -- Description:
1209 -- This procedure is used for referential integrity of datetracked
1210 -- parent entities when a datetrack update operation is taking place
1211 -- and where there is no cascading of update defined for this entity.
1212 --
1213 -- Prerequisites:
1214 -- This procedure is called from the update_validate.
1215 --
1216 -- In Parameters:
1217 --
1218 -- Post Success:
1219 -- Processing continues.
1220 --
1221 -- Post Failure:
1222 --
1223 -- Developer Implementation Notes:
1224 -- This procedure should not need maintenance unless the HR Schema model
1225 -- changes.
1226 --
1227 -- Access Status:
1228 -- Internal Row Handler Use Only.
1229 --
1230 -- {End Of Comments}
1231 -- ----------------------------------------------------------------------------
1232 Procedure dt_update_validate
1233 (p_step_id in number
1234 ,p_assignment_id in number
1235 ,p_datetrack_mode in varchar2
1236 ,p_validation_start_date in date
1237 ,p_validation_end_date in date
1238 ) Is
1239 --
1240 l_proc varchar2(72) := g_package||'dt_update_validate';
1241 l_integrity_error Exception;
1242 l_table_name all_tables.table_name%TYPE;
1243 --
1244 Begin
1245 --
1246 -- Ensure that the p_datetrack_mode argument is not null
1247 --
1248 hr_utility.set_location(' Entering:'||l_proc,10);
1249 --
1250 hr_api.mandatory_arg_error
1251 (p_api_name => l_proc
1252 ,p_argument => 'datetrack_mode'
1253 ,p_argument_value => p_datetrack_mode
1254 );
1255 --
1256 -- Mode will be valid, as this is checked at the start of the upd.
1257 --
1258 -- Ensure the arguments are not null
1259 --
1260 hr_api.mandatory_arg_error
1261 (p_api_name => l_proc
1262 ,p_argument => 'validation_start_date'
1263 ,p_argument_value => p_validation_start_date
1264 );
1265 --
1266 hr_utility.set_location(' Entering: More VAlidation'||l_proc,15);
1267 --
1268 hr_api.mandatory_arg_error
1269 (p_api_name => l_proc
1270 ,p_argument => 'validation_end_date'
1274 If ((nvl(p_step_id, hr_api.g_number) <> hr_api.g_number) and
1271 ,p_argument_value => p_validation_end_date
1272 );
1273 --
1275 NOT (dt_api.check_min_max_dates
1276 (p_base_table_name => 'per_spinal_point_steps_f'
1277 ,p_base_key_column => 'STEP_ID'
1278 ,p_base_key_value => p_step_id
1279 ,p_from_date => p_validation_start_date
1280 ,p_to_date => p_validation_end_date))) Then
1281 l_table_name := 'spinal point steps';
1282 raise l_integrity_error;
1283 End If;
1284 If ((nvl(p_assignment_id, hr_api.g_number) <> hr_api.g_number) and
1285 NOT (dt_api.check_min_max_dates
1286 (p_base_table_name => 'per_all_assignments_f'
1287 ,p_base_key_column => 'ASSIGNMENT_ID'
1288 ,p_base_key_value => p_assignment_id
1289 ,p_from_date => p_validation_start_date
1290 ,p_to_date => p_validation_end_date))) Then
1291 l_table_name := 'all assignments';
1292 raise l_integrity_error;
1293 End If;
1294 --
1295 hr_utility.set_location(' Leaving:'||l_proc,30);
1296 --
1297 --
1298 Exception
1299 When l_integrity_error Then
1300 --
1301 -- A referential integrity check was violated therefore
1302 -- we must error
1303 --
1304 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
1305 fnd_message.set_token('TABLE_NAME', l_table_name);
1306 fnd_message.raise_error;
1307 When Others Then
1308 --
1309 -- An unhandled or unexpected error has occurred which
1310 -- we must report
1311 --
1312 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1313 fnd_message.set_token('PROCEDURE', l_proc);
1314 fnd_message.set_token('STEP','15');
1315 fnd_message.raise_error;
1316 End dt_update_validate;
1317 --
1318 -- ----------------------------------------------------------------------------
1319 -- |--------------------------< dt_delete_validate >--------------------------|
1320 -- ----------------------------------------------------------------------------
1321 -- {Start Of Comments}
1322 --
1323 -- Description:
1324 -- This procedure is used for referential integrity of datetracked
1325 -- child entities when either a datetrack DELETE or ZAP is in operation
1326 -- and where there is no cascading of delete defined for this entity.
1327 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1328 -- datetracked child rows exist between the validation start and end
1329 -- dates.
1330 --
1331 -- Prerequisites:
1332 -- This procedure is called from the delete_validate.
1333 --
1334 -- In Parameters:
1335 --
1336 -- Post Success:
1337 -- Processing continues.
1338 --
1339 -- Post Failure:
1340 -- If a row exists by determining the returning Boolean value from the
1341 -- generic dt_api.rows_exist function then we must supply an error via
1342 -- the use of the local exception handler l_rows_exist.
1343 --
1344 -- Developer Implementation Notes:
1345 -- This procedure should not need maintenance unless the HR Schema model
1346 -- changes.
1347 --
1348 -- Access Status:
1349 -- Internal Row Handler Use Only.
1350 --
1351 -- {End Of Comments}
1352 -- ----------------------------------------------------------------------------
1353 Procedure dt_delete_validate
1354 (p_placement_id in number
1355 ,p_datetrack_mode in varchar2
1356 ,p_validation_start_date in date
1357 ,p_validation_end_date in date
1358 ) Is
1359 --
1360 l_proc varchar2(72) := g_package||'dt_delete_validate';
1361 l_rows_exist Exception;
1362 l_table_name all_tables.table_name%TYPE;
1363 --
1364 Begin
1365 --
1366 -- Ensure that the p_datetrack_mode argument is not null
1367 --
1368 hr_api.mandatory_arg_error
1369 (p_api_name => l_proc
1370 ,p_argument => 'datetrack_mode'
1371 ,p_argument_value => p_datetrack_mode
1372 );
1373 --
1374 -- Only perform the validation if the datetrack mode is either
1375 -- DELETE or ZAP
1376 --
1377 If (p_datetrack_mode = hr_api.g_delete or
1378 p_datetrack_mode = hr_api.g_zap) then
1379 --
1380 --
1381 -- Ensure the arguments are not null
1382 --
1383 hr_api.mandatory_arg_error
1384 (p_api_name => l_proc
1385 ,p_argument => 'validation_start_date'
1386 ,p_argument_value => p_validation_start_date
1387 );
1388 --
1389 hr_api.mandatory_arg_error
1390 (p_api_name => l_proc
1391 ,p_argument => 'validation_end_date'
1392 ,p_argument_value => p_validation_end_date
1393 );
1394 --
1395 hr_api.mandatory_arg_error
1396 (p_api_name => l_proc
1397 ,p_argument => 'placement_id'
1398 ,p_argument_value => p_placement_id
1399 );
1400 --
1401 --
1402 --
1403 End If;
1404 --
1405 Exception
1406 When l_rows_exist Then
1407 --
1408 -- A referential integrity check was violated therefore
1409 -- we must error
1410 --
1414 When Others Then
1411 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
1412 fnd_message.set_token('TABLE_NAME', l_table_name);
1413 fnd_message.raise_error;
1415 --
1416 -- An unhandled or unexpected error has occurred which
1417 -- we must report
1418 --
1419 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1420 fnd_message.set_token('PROCEDURE', l_proc);
1421 fnd_message.set_token('STEP','15');
1422 fnd_message.raise_error;
1423 --
1424 End dt_delete_validate;
1425 --
1426 -- ----------------------------------------------------------------------------
1427 -- |---------------------------< insert_validate >----------------------------|
1428 -- ----------------------------------------------------------------------------
1429 Procedure insert_validate
1430 (p_rec in per_spp_shd.g_rec_type
1431 ,p_effective_date in date
1432 ,p_datetrack_mode in varchar2
1433 ,p_validation_start_date in date
1434 ,p_validation_end_date in date
1435 ) is
1436 --
1437 l_proc varchar2(72) := g_package||'insert_validate';
1438 l_effective_start_date per_spinal_point_placements_f.effective_start_date%TYPE;
1439 l_effective_end_date per_spinal_point_placements_f.effective_end_date%TYPE;
1440 --
1441 Begin
1442 hr_utility.set_location('Entering:'||l_proc, 5);
1443 --
1444 -- Call all supporting business operations
1445 --
1446 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1447 --
1448 l_effective_start_date := p_effective_date; -- bug 2491732
1449 --l_effective_start_date := hr_api.g_sys commented out for bug 2491732;
1450 l_effective_end_date := hr_api.g_eot; -- do i want this?
1451 --
1452 hr_utility.set_location('Entering:chk_parent_spine_step_id : '||l_proc, 10);
1453 --
1454 hr_utility.set_location('Start Date :'|| l_effective_start_date,969);
1455 hr_utility.set_location('val Start Date :'|| p_validation_start_date,969);
1456 --
1457 chk_parent_spine_step_id
1458 (p_step_id => p_rec.step_id
1459 ,p_parent_spine_id => p_rec.parent_spine_id
1460 ,p_effective_start_date => l_effective_start_date
1461 ,p_effective_end_date => l_effective_end_date
1462 ,p_placement_id => p_rec.placement_id
1463 ,p_object_version_number => p_rec.object_version_number);
1464 --
1465 hr_utility.set_location('Entering: chk_assignment_id'||l_proc, 15);
1466 --
1467 chk_assignment_id
1468 (p_assignment_id => p_rec.assignment_id
1469 ,p_datetrack_mode => p_datetrack_mode
1470 ,p_placement_id => p_rec.placement_id
1471 ,p_object_version_number => p_rec.object_version_number
1472 ,p_effective_date => p_effective_date); -- Bug 2488727
1473 --
1474 hr_utility.set_location('Entering: chk_pay_ass_ceiling'||l_proc, 16);
1475 --
1476 chk_pay_ass_ceiling
1477 (p_step_id => p_rec.step_id
1478 ,p_parent_spine_id => p_rec.parent_spine_id
1479 ,p_assignment_id => p_rec.assignment_id
1480 ,p_effective_date => p_effective_date);
1481 --
1482 hr_utility.set_location('Entering: chk_auto_inc_flag'||l_proc, 18);
1483 --
1484 chk_auto_inc_flag
1485 (p_auto_increment_flag => p_rec.auto_increment_flag
1486 ,p_placement_id => p_rec.placement_id
1487 ,p_increment_number => p_rec.increment_number
1488 ,p_object_version_number => p_rec.object_version_number);
1489 --
1490 hr_utility.set_location('Entering: chk_reason'||l_proc, 19);
1491 --
1492 chk_reason
1493 (p_reason => p_rec.reason
1494 ,p_effective_date => p_effective_date);
1495 --
1496 hr_utility.set_location(' Leaving:'||l_proc, 20);
1497 End insert_validate;
1498 --
1499 -- ----------------------------------------------------------------------------
1500 -- |---------------------------< update_validate >----------------------------|
1501 -- ----------------------------------------------------------------------------
1502 Procedure update_validate
1503 (p_rec in out nocopy per_spp_shd.g_rec_type
1504 ,p_effective_date in date
1505 ,p_datetrack_mode in out nocopy varchar2
1506 ,p_validation_start_date in out nocopy date
1507 ,p_validation_end_date in out nocopy date
1508 ) is
1509 --
1510 l_proc varchar2(72) := g_package||'update_validate';
1511 l_effective_start_date per_spinal_point_placements_f.effective_start_date%TYPE;
1512 l_effective_end_date per_spinal_point_placements_f.effective_end_date%TYPE;
1513 --
1514 --csr_start_end_date select the start and end date for the record being modified
1515 --
1516 cursor csr_start_end_date is
1517 select spp.effective_start_date,
1518 spp.effective_end_date
1519 from per_spinal_point_placements_f spp
1520 where spp.placement_id = p_rec.placement_id
1521 and spp.assignment_id = p_rec.assignment_id
1522 and p_effective_date between spp.effective_start_date
1523 and spp.effective_end_date ;
1524 --
1525 --
1526 Begin
1527 hr_utility.set_location('Entering:'||l_proc, 5);
1528 --
1529 -- Call all supporting business operations
1530 --
1531 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1532 --
1533 open csr_start_end_date;
1534 fetch csr_start_end_date into l_effective_start_date, l_effective_end_date;
1535 if csr_start_end_date%notfound then
1539 hr_utility.raise_error;
1536 --
1537 close csr_start_end_date;
1538 fnd_message.set_name('PER', 'HR_289229_SPP_DATE_ERROR');
1540 --
1541 else
1542 close csr_start_end_date;
1543 end if;
1544 --
1545 hr_utility.set_location('Entering: chk_object_version_number'||l_proc,7);
1546 --
1547 chk_object_version_number
1548 (p_placement_id => p_rec.placement_id
1549 ,p_object_version_number => p_rec.object_version_number
1550 ,p_effective_date => p_effective_date
1551 );
1552 hr_utility.set_location('Entering: chk_parent_spine_step_id '||l_proc, 69);
1553 --
1554 hr_utility.set_location('p_eff Date :'|| p_effective_date,69);
1555 hr_utility.set_location('Start Date :'|| l_effective_start_date,69);
1556 hr_utility.set_location('End Date :'|| l_effective_end_date,69);
1557 hr_utility.set_location('val start Date :'|| p_validation_start_date,69);
1558 --
1559 chk_parent_spine_step_id
1560 (p_step_id => p_rec.step_id
1561 ,p_parent_spine_id => p_rec.parent_spine_id
1562 ,p_effective_start_date => p_effective_date -- Bug 2419723
1563 ,p_effective_end_date => l_effective_end_date
1564 ,p_placement_id => p_rec.placement_id
1565 ,p_object_version_number => p_rec.object_version_number);
1566 --
1567 hr_utility.set_location('Entering: chk_assignment_id'||l_proc, 15);
1568 --
1569 chk_assignment_id
1570 (p_assignment_id => p_rec.assignment_id
1571 ,p_datetrack_mode => p_datetrack_mode
1572 ,p_placement_id => p_rec.placement_id
1573 ,p_object_version_number => p_rec.object_version_number
1574 ,p_effective_date => p_effective_date); -- Bug 2488727
1575 --
1576 hr_utility.set_location('Entering: chk_pay_ass_ceiling'||l_proc, 16);
1577 --
1578 chk_pay_ass_ceiling
1579 (p_step_id => p_rec.step_id
1580 ,p_parent_spine_id => p_rec.parent_spine_id
1581 ,p_assignment_id => p_rec.assignment_id
1582 ,p_effective_date => p_effective_date);
1583 --
1584 hr_utility.set_location('Entering: chk_auto_inc_flag'||l_proc, 18);
1585 --
1586 chk_auto_inc_flag
1587 (p_auto_increment_flag => p_rec.auto_increment_flag
1588 ,p_placement_id => p_rec.placement_id
1589 ,p_increment_number => p_rec.increment_number
1590 ,p_object_version_number => p_rec.object_version_number);
1591 --
1592 hr_utility.set_location('Entering: chk_reason'||l_proc, 19);
1593 --
1594 chk_reason
1595 (p_reason => p_rec.reason
1596 ,p_effective_date => p_effective_date);
1597 --
1598 -- Start of fix for Bug 3335915
1599 --
1600 if (not hr_assignment_internal.g_called_from_spp_asg) and
1601 (p_datetrack_mode = 'UPDATE_OVERRIDE') then
1602 --
1603 chk_future_asg_changes
1604 (p_placement_id => p_rec.placement_id
1605 ,p_effective_date => p_effective_date
1606 ,p_datetrack_mode => p_datetrack_mode
1607 );
1608
1609 end if ;
1610 --
1611 -- End of fix for Bug 3335915
1612 /*
1613 hr_utility.set_location('Entering: chk_reason_only_update'||l_proc, 20);
1614 --
1615 -- If the only thing that changes on the record is the reason then
1616 -- the record can not be updated as thereason refers to the reason why
1617 -- a step id has changed. Therefor the datetrack mode is set to correction
1618 --
1619 chk_reason_only_update
1620 (p_rec => p_rec
1621 ,p_datetrack_mode => p_datetrack_mode
1622 ,p_effective_date => p_effective_date
1623 ,p_validation_start_date => p_validation_start_date
1624 ,p_validation_end_date => p_validation_end_date
1625 );
1626 */
1627 --
1628 hr_utility.set_location(' Entering: dt_update_validate'||l_proc, 30);
1629 --
1630 -- Call the datetrack update integrity operation
1631 --
1632 dt_update_validate
1633 (p_step_id => p_rec.step_id
1634 ,p_assignment_id => p_rec.assignment_id
1635 ,p_datetrack_mode => p_datetrack_mode
1636 ,p_validation_start_date => p_validation_start_date
1637 ,p_validation_end_date => p_validation_end_date
1638 );
1639 --
1640 hr_utility.set_location(' Entering: chk_non_updateable_args'||l_proc, 35);
1641 chk_non_updateable_args
1642 (p_effective_date => p_effective_date
1643 ,p_rec => p_rec
1644 );
1645 --
1646 --
1647 hr_utility.set_location(' Leaving:'||l_proc, 40);
1648 End update_validate;
1649 --
1650 -- ----------------------------------------------------------------------------
1651 -- |---------------------------< delete_validate >----------------------------|
1652 -- ----------------------------------------------------------------------------
1653 Procedure delete_validate
1654 (p_rec in per_spp_shd.g_rec_type
1655 ,p_effective_date in date
1656 ,p_datetrack_mode in varchar2
1657 ,p_validation_start_date in date
1658 ,p_validation_end_date in date
1659 ) is
1660 --
1661 l_proc varchar2(72) := g_package||'delete_validate';
1662 --
1663 Begin
1664 hr_utility.set_location('Entering:'||l_proc, 5);
1665 --
1666 -- Call all supporting business operations
1667 --
1668 hr_utility.set_location('Entering: chk_object_version_number'||l_proc,7);
1669 --
1670 chk_object_version_number
1671 (p_placement_id => p_rec.placement_id
1672 ,p_object_version_number => p_rec.object_version_number
1673 ,p_effective_date => p_effective_date
1674 );
1675 -- Start of fix for Bug 3335915
1676 if (not hr_assignment_internal.g_called_from_spp_asg) and
1677 (p_datetrack_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE'))then
1678 --
1679 hr_utility.set_location( l_proc ||'calling chk_fututre_asg_chng', 40);
1680 --
1681 chk_future_asg_changes
1682 (p_placement_id => p_rec.placement_id
1683 ,p_effective_date => p_effective_date
1684 ,p_datetrack_mode => p_datetrack_mode
1685 );
1686
1687 end if ;
1688 --
1689 -- End of fix for Bug 3335915
1690 --
1691 hr_utility.set_location('Entering: dt_delete_validate'||l_proc,8);
1692 --
1693 dt_delete_validate
1694 (p_datetrack_mode => p_datetrack_mode
1695 ,p_validation_start_date => p_validation_start_date
1696 ,p_validation_end_date => p_validation_end_date
1697 ,p_placement_id => p_rec.placement_id
1698 );
1699 --
1700 hr_utility.set_location(' Leaving:'||l_proc, 10);
1701 End delete_validate;
1702 --
1703 end per_spp_bus;
[Home] [Help]