[Home] [Help]
PACKAGE BODY: APPS.PER_SUC_BUS
Source
1 Package Body per_suc_bus as
2 /* $Header: pesucrhi.pkb 120.1 2008/02/05 07:07:45 schowdhu noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_suc_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-----------------------< chk_succession_plan_id >-------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the succession
16 -- planning table is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- succession_plan_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal and External use.
35 --
36 Procedure chk_succession_plan_id(p_succession_plan_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_succession_plan_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := per_suc_shd.api_updating
47 (p_succession_plan_id => p_succession_plan_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_succession_plan_id,hr_api.g_number)
52 <> per_suc_shd.g_old_rec.succession_plan_id) then
53 --
54 -- raise error as PK has changed
55 --
56 per_suc_shd.constraint_error('PER_SUCCESSION_PLANNING_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_succession_plan_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 per_suc_shd.constraint_error('PER_SUCCESSION_PLANNING_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location(' Leaving:'||l_proc, 10);
73 --
74 End chk_succession_plan_id;
75 -- ----------------------------------------------------------------------------
76 -- |----------------------------< chk_person_id >-----------------------------|
77 -- ----------------------------------------------------------------------------
78 --
79 -- Description
80 -- This procedure is used to check that the person_id exists as of effective
81 -- date.
82 --
83 -- Pre Conditions
84 -- None.
85 --
86 -- In Parameters
87 -- effective_date effective date
88 -- succession_plan_id PK of record being inserted or updated.
89 -- person_id id of person being inserted.
90 -- object_version_number Object version number of record being
91 -- inserted or updated.
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Errors handled by the procedure
98 --
99 -- Access Status
100 -- Internal and External use.
101 Procedure chk_person_id(p_effective_date in date,
102 p_succession_plan_id in number,
103 p_person_id in number,
104 p_object_version_number in number) is
105 --
106 l_proc varchar2(72) := g_package||'chk_person_id';
107 l_api_updating boolean;
108 l_dummy varchar2(1);
109 --
110 cursor c1 is
111 SELECT NULL
112 FROM per_people_f ppf,
113 per_person_type_usages_f ptu,
114 per_person_types ppt
115 WHERE ppf.person_id = p_person_id
116 AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
117 AND ppf.person_id = ptu.person_id
118 AND TRUNC (SYSDATE) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
119 AND ptu.person_type_id = ppt.person_type_id
120 AND ppt.system_person_type = 'EMP';
121 --
122 Begin
123 --
124 hr_utility.set_location('Entering:'||l_proc, 5);
125 --
126 l_api_updating := per_suc_shd.api_updating
127 (p_succession_plan_id => p_succession_plan_id,
128 p_object_version_number => p_object_version_number);
129 --
130 if (l_api_updating
131 and nvl(p_person_id,hr_api.g_number)
132 <> nvl(per_suc_shd.g_old_rec.person_id,hr_api.g_number)
133 or not l_api_updating) then
134 --
135 -- check if person_id is null
136 --
137 if p_person_id is null then
138 --
139 -- raise error as this a mandatory requirement
140 --
141 hr_utility.set_message(801,'HR_52784_SUC_CHK_PERSON_ID');
142 hr_utility.raise_error;
143 --
144 end if;
145 --
146 /*if l_api_updating then
147 --
148 -- raise error as the person can not be updated.
149 --
150 hr_utility.set_message(801,'HR_52785_SUC_CHK_PERSON_UPDATE');
151 hr_utility.raise_error;
152 --
153 end if;*/
154 --
155 -- check if the person_id exists as of effective date.
156 --
157 open c1;
158 --
159 fetch c1 into l_dummy;
160 --
161 if c1%notfound then
162 --
163 close c1;
164 --
165 -- raise error as person does not exist.
166 --
167 hr_utility.set_message(801,'HR_52786_SUC_CHK_PERSON_EXISTS');
168 hr_utility.raise_error;
169 --
170 end if;
171 --
172 close c1;
173 --
174 end if;
175 --
176 hr_utility.set_location(' Leaving:'||l_proc, 10);
177 --
178 End chk_person_id;
179 --
180 -- ----------------------------------------------------------------------------
181 -- |----------------------------< chk_position_id >---------------------------|
182 -- ----------------------------------------------------------------------------
183 --
184 -- Description
185 -- This procedure is used to check that the position_id exists as of
186 -- effective date.
187 --
188 -- Pre Conditions
189 -- None.
190 --
191 -- In Parameters
192 -- effective_date effective date
193 -- succession_plan_id PK of record being inserted or updated.
194 -- position_id id of position being inserted.
195 -- object_version_number Object version number of record being
196 -- inserted or updated.
197 -- Post Success
198 -- Processing continues
199 --
200 -- Post Failure
201 -- Errors handled by the procedure
202 --
203 -- Access Status
204 -- Internal and External use.
205 Procedure chk_position_id(p_effective_date in date,
206 p_succession_plan_id in number,
207 p_position_id in number,
208 p_object_version_number in number) is
209 --
210 l_proc varchar2(72) := g_package||'chk_position_id';
211 l_api_updating boolean;
212 l_dummy varchar2(1);
213 --
214 -- Changes 12-Oct-99 SCNair (per_positions to hr_positions_f) Date tracked positions req.
215 --
216 cursor c1 is
217 select null
218 from hr_positions_f per
219 where per.position_id = nvl(p_position_id,-1)
220 and p_effective_date
221 between per.effective_start_date
222 and per.effective_end_date
223 and p_effective_date
224 between per.date_effective
225 and nvl(hr_general.get_position_date_end(per.position_id),hr_api.g_eot);
226 --
227 Begin
228 --
229 hr_utility.set_location('Entering:'||l_proc, 5);
230 --
231 l_api_updating := per_suc_shd.api_updating
232 (p_succession_plan_id => p_succession_plan_id,
233 p_object_version_number => p_object_version_number);
234 --
235 if (l_api_updating
236 and nvl(p_position_id,hr_api.g_number)
237 <> nvl(per_suc_shd.g_old_rec.position_id,hr_api.g_number)
238 or not l_api_updating) then
239 --
240 -- check if position_id is null
241 --
242 /* if p_position_id is null then
243 --
244 -- raise error as this a mandatory requirement
245 --
246 hr_utility.set_message(801,'HR_51998_SUC_CHK_POSITION_ID');
247 hr_utility.raise_error;
248 --
249 end if;*/
250 --
251 if l_api_updating then
252 --
253 -- raise error as the position_id can not be updated.
254 --
255 hr_utility.set_message(801,'HR_51999_SUC_CHK_POS_UPDATE');
256 hr_utility.raise_error;
257 --
258 end if;
259 --
260 -- check if the position_id exists as of effective_date.
261 --
262 open c1;
263 --
264 fetch c1 into l_dummy;
265 --
266 if c1%notfound then
267 --
268 close c1;
269 --
270 -- raise error as position does not exist as of effective date.
271 --
272 per_suc_shd.constraint_error('PER_SUCCESSION_PLANNING_FK1');
273 --
274 end if;
275 --
276 close c1;
277 --
278 end if;
279 --
280 hr_utility.set_location(' Leaving:'||l_proc, 10);
281 --
282 End chk_position_id;
283 --
284 -- ----------------------------------------------------------------------------
285 -- |----------------------------< chk_job_id >---------------------------|
286 -- ----------------------------------------------------------------------------
287 --
288 -- Description
289 -- This procedure is used to check that the job_id exists as of
290 -- effective date.
291 --
292 -- Pre Conditions
293 -- None.
294 --
295 -- In Parameters
296 -- effective_date effective date
297 -- succession_plan_id PK of record being inserted or updated.
298 -- job_id id of job being inserted.
299 -- object_version_number Object version number of record being
300 -- inserted or updated.
301 -- Post Success
302 -- Processing continues
303 --
304 -- Post Failure
305 -- Errors handled by the procedure
306 --
307 -- Access Status
308 -- Internal and External use.
309 Procedure chk_job_id(p_effective_date in date,
310 p_succession_plan_id in number,
311 p_job_id in number,
312 p_object_version_number in number) is
313 --
314 l_proc varchar2(72) := g_package||'chk_job_id';
315 l_api_updating boolean;
316 l_dummy varchar2(1);
317 --
318 --
319 cursor c1 is
320 select null
321 from per_jobs_vl per
322 where per.job_id = nvl(p_job_id,-1)
323 and p_effective_date
324 between per.DATE_FROM
325 and nvl(date_to,trunc(sysdate));
326
327 --
328 Begin
329 --
330 hr_utility.set_location('Entering:'||l_proc, 5);
331 --
332 l_api_updating := per_suc_shd.api_updating
333 (p_succession_plan_id => p_succession_plan_id,
334 p_object_version_number => p_object_version_number);
335 --
336 if (l_api_updating
337 and nvl(p_job_id,hr_api.g_number)
338 <> nvl(per_suc_shd.g_old_rec.job_id,hr_api.g_number)
339 or not l_api_updating) then
340 --
341 -- check if job_id is null
342 --
343 /* if p_job_id is null then
344 --
345 -- raise error as this a mandatory requirement
346 --
347 hr_utility.set_message(801,'HR_XXXXX_SUC_CHK_JOB_ID');
348 hr_utility.raise_error;
349 --
350 end if;*/
351 --
352 if l_api_updating then
353 --
354 -- raise error as the job_id can not be updated.
355 --
356 hr_utility.set_message(801,'HR_50493_SUC_CHK_JOB_UPDATE');
357 hr_utility.raise_error;
358 --
359 end if;
360 --
361 -- check if the job_id exists as of effective_date.
362 --
363 open c1;
364 --
365 fetch c1 into l_dummy;
366 --
367 if c1%notfound then
368 --
369 close c1;
370 --
371 -- raise error as job does not exist as of effective date.
372 --
373 per_suc_shd.constraint_error('PER_SUCCESSION_PLANNING_FK2');
374 --
375 end if;
376 --
377 close c1;
378 --
379 end if;
380 --
381 hr_utility.set_location(' Leaving:'||l_proc, 10);
382 --
383 End chk_job_id;
384 --
385 -- ----------------------------------------------------------------------------
386 -- |----------------------------< chk_successee >---------------------------|
387 -- ----------------------------------------------------------------------------
388 --
389 -- Description
393 -- Pre Conditions
390 -- This procedure is used to check that the successee_id exists as of
391 -- effective date.
392 --
394 -- None.
395 --
396 -- In Parameters
397 -- effective_date effective date
398 -- succession_plan_id PK of record being inserted or updated.
399 -- job_id id of job being inserted.
400 -- position_id id of position being inserted
401 -- successee_person_id id of person being inserted (successee)
402 -- object_version_number Object version number of record being
403 -- inserted or updated.
404 -- Post Success
405 -- Processing continues
406 --
407 -- Post Failure
408 -- Errors handled by the procedure
409 --
410 -- Access Status
411 -- Internal and External use.
412 Procedure chk_successee(p_effective_date in date,
413 p_succession_plan_id in number,
414 p_job_id in number,
415 p_position_id in number,
416 p_successee_person_id in number,
417 p_object_version_number in number) is
418 --
419 l_proc varchar2(72) := g_package||'chk_successee';
420 l_api_updating boolean;
421 l_dummy varchar2(1);
422 l_type varchar2(3); --succession type
423 l_notnulls number :=0 ;
424 --
425 --
426
427 --
428 Begin
429 --
430 hr_utility.set_location('Entering:'||l_proc, 5);
431 --
432 l_api_updating := per_suc_shd.api_updating
433 (p_succession_plan_id => p_succession_plan_id,
434 p_object_version_number => p_object_version_number);
435 --
436 if (l_api_updating
437 and nvl(p_successee_person_id,hr_api.g_number)
438 <> nvl(per_suc_shd.g_old_rec.successee_person_id,hr_api.g_number) )
439 then
440 --
441 -- raise error as the person can not be updated.
442 --
443 hr_utility.set_message(801,'HR_50494_SUC_CHK_SUCC_UPDATE');
444 hr_utility.raise_error;
445 --
446 end if;
447 --
448 if(p_job_id is not null)
449 then l_type := 'JOB';
450 -- CHK_JOB_ID
451 --
452 chk_job_id(p_effective_date,
453 p_succession_plan_id,
454 p_job_id,
455 p_object_version_number);
456 l_notnulls := l_notnulls + 1;
457 end if;
458
459 if (p_position_id is not null)
460 then l_type := 'POS';
461 -- CHK_POSITION_ID
462 --
463 chk_position_id(p_effective_date,
464 p_succession_plan_id,
465 p_position_id,
466 p_object_version_number);
467 l_notnulls := l_notnulls + 1;
468 end if;
469
470 if (p_successee_person_id is not null)
471 then l_type := 'EMP';
472 -- CHK_SUCCESSEE_PERSON_ID
473 --
474 chk_person_id(p_effective_date,
475 p_succession_plan_id,
476 p_successee_person_id,
477 p_object_version_number);
478 l_notnulls := l_notnulls + 1;
479 end if;
480
481 if( l_notnulls = 0 )
482 then
483 --
484 -- raise error as JOB OR POSITION OR SUCCESSEE PERSON ID IS MANDATORY
485 --
486 per_suc_shd.constraint_error('HR_50495_SUC_CHK_SUC_MISSING');
487 elsif ( l_notnulls > 1 )
488 then
489 --
490 -- raise error as MORE THAN ONE SUCCESSEE ID IS ENTERED
491 --
492 per_suc_shd.constraint_error('HR_50496_SUC_CHK_SUC_EXIST');
493 end if;
494
495
496 --
497 hr_utility.set_location(' Leaving:'||l_proc, 10);
498 --
499 End chk_successee;
500 --
501 -- ----------------------------------------------------------------------------
502 -- |----------------------------< chk_successor >---------------------------|
503 -- ----------------------------------------------------------------------------
504 --
505 -- Description
506 -- This procedure is used to check that the successor exists as of
507 -- effective date and whether successee and successor are the same or login user is a successor
508 --
509 -- Pre Conditions
510 -- None.
511 --
512 -- In Parameters
513 -- effective_date effective date
514 -- succession_plan_id PK of record being inserted or updated.
515 -- job_id id of job being inserted.
516 -- object_version_number Object version number of record being
517 -- inserted or updated.
518 -- Post Success
519 -- Processing continues
520 --
521 -- Post Failure
522 -- Errors handled by the procedure
523 --
524 -- Access Status
525 -- Internal and External use.
526 Procedure chk_successor(p_effective_date in date,
527 p_succession_plan_id in number,
528 p_person_id in number,
529 p_successee_person_id in number,
530 p_object_version_number in number) is
531 --
532 l_proc varchar2(72) := g_package||'chk_successor';
533 l_api_updating boolean;
534 l_dummy varchar2(1);
538 --
535 --
536 --
537
539 Begin
540 --
541 hr_utility.set_location('Entering:'||l_proc, 5);
542 --
543 l_api_updating := per_suc_shd.api_updating
544 (p_succession_plan_id => p_succession_plan_id,
545 p_object_version_number => p_object_version_number);
546 --
547 if (l_api_updating
548 and nvl(p_person_id,hr_api.g_number)
549 <> nvl(per_suc_shd.g_old_rec.person_id,hr_api.g_number)
550 or not l_api_updating) then
551 --
552
553 --
554 if l_api_updating then
555 --
556 -- raise error as the person_id can not be updated.
557 --
558 hr_utility.set_message(801,'HR_50497_SUC_CHK_SUC_UPDATE');
559 hr_utility.raise_error;
560 --
561 end if;
562 -- CHK_SUCCESSOR_ID
563 --
564 chk_person_id(p_effective_date,
565 p_succession_plan_id,
566 p_person_id,
567 p_object_version_number);
568
569 if( p_person_id = nvl(p_successee_person_id,-1) )
570 then
571 --
572 -- raise error as successee and successor are the same person
573 --
574 per_suc_shd.constraint_error('HR_50498_SUC_CHK_SAME_PERSON');
575 end if;
576
577 if ( p_person_id = fnd_global.employee_id )
578 then
579 --
580 -- raise error as login user cannot make himself a successor
581 --
582 per_suc_shd.constraint_error('HR_50499_SUC_CHK_LOGIN_USE');
583 end if;
584 --
585 end if;
586 --
587 hr_utility.set_location(' Leaving:'||l_proc, 10);
588 --
589 End chk_successor;
590 -- ----------------------------------------------------------------------------
591 -- |-----------------------------< chk_time_scale >---------------------------|
592 -- ----------------------------------------------------------------------------
593 --
594 -- Description
595 -- This procedure is used to check that the time_scale lookup falles within
596 -- the per_time_scales lookup.
597 --
598 -- Pre Conditions
599 -- None.
600 --
601 -- In Parameters
602 -- succession_plan_id PK of record being inserted or updated.
603 -- time_scale time_scale lookup.
604 -- object_version_number Object version number of record being
605 -- inserted or updated.
606 -- effective_date effective date
607 --
608 -- Post Success
609 -- Processing continues
610 --
611 -- Post Failure
612 -- Error handled by procedure
613 --
614 -- Access Status
615 -- Internal and External use.
616 --
617 Procedure chk_time_scale(p_succession_plan_id in number,
618 p_time_scale in varchar2,
619 p_object_version_number in number,
620 p_effective_date in date) is
621 --
622 l_proc varchar2(72) := g_package||'chk_time_scale';
623 l_api_updating boolean;
624 --
625 Begin
626 --
627 hr_utility.set_location('Entering:'||l_proc, 5);
628 --
629 l_api_updating := per_suc_shd.api_updating
630 (p_succession_plan_id => p_succession_plan_id,
631 p_object_version_number => p_object_version_number);
632 --
633 if (l_api_updating
634 and nvl(p_time_scale,hr_api.g_varchar2)
635 <> nvl(per_suc_shd.g_old_rec.time_scale,hr_api.g_varchar2)
636 or not l_api_updating) then
637 --
638 -- check if value of time scale lookup falls within lookup.
639 --
640 if hr_api.not_exists_in_hr_lookups(p_lookup_type => 'PER_TIME_SCALES',
641 p_lookup_code => p_time_scale,
642 p_effective_date => p_effective_date) then
643 --
644 -- raise error as does not exist as lookup
645 --
646 hr_utility.set_message(801,'HR_52001_SUC_CHK_TIME_SCALE');
647 hr_utility.raise_error;
648 --
649 end if;
650 --
651 end if;
652 --
653 hr_utility.set_location('Leaving:'||l_proc,10);
654 --
655 end chk_time_scale;
656 -- ----------------------------------------------------------------------------
657 -- |----------------------------< chk_start_date >----------------------------|
658 -- ----------------------------------------------------------------------------
659 --
660 -- Description
661 -- This procedure is used to check that the start_date has been populated.
662 --
663 -- Pre Conditions
664 -- None.
665 --
666 -- In Parameters
667 -- succession_plan_id PK of record being inserted or updated.
668 -- start_date start date of succession plan record.
669 -- object_version_number Object version number of record being
670 -- inserted or updated.
671 -- Post Success
672 -- Processing continues
673 --
674 -- Post Failure
675 -- Errors handled by the procedure
676 --
677 -- Access Status
678 -- Internal and External use.
679 Procedure chk_start_date(p_succession_plan_id in number,
680 p_start_date in date,
681 p_object_version_number in number) is
682 --
686 Begin
683 l_proc varchar2(72) := g_package||'chk_start_date';
684 l_api_updating boolean;
685 --
687 --
688 hr_utility.set_location('Entering:'||l_proc, 5);
689 --
690 l_api_updating := per_suc_shd.api_updating
691 (p_succession_plan_id => p_succession_plan_id,
692 p_object_version_number => p_object_version_number);
693 --
694 if (l_api_updating
695 and nvl(p_start_date,hr_api.g_date)
696 <> nvl(per_suc_shd.g_old_rec.start_date,hr_api.g_date)
697 or not l_api_updating) then
698 --
699 -- check if start date is null
700 --
701 if p_start_date is null then
702 --
703 -- raise error as this a mandatory requirement
704 --
705 hr_utility.set_message(801,'HR_52002_SUC_CHK_START_DATE');
706 hr_utility.raise_error;
707 --
708 end if;
709 --
710 end if;
711 --
712 hr_utility.set_location(' Leaving:'||l_proc, 10);
713 --
714 End chk_start_date;
715 -- ----------------------------------------------------------------------------
716 -- |----------------------------< chk_end_date >------------------------------|
717 -- ----------------------------------------------------------------------------
718 --
719 -- Description
720 -- This procedure is used to check that the end date is later than the
721 -- start date.
722 --
723 -- Pre Conditions
724 -- None.
725 --
726 -- In Parameters
727 -- succession_plan_id PK of record being inserted or updated.
728 -- start_date start date of succession plan record.
729 -- end_date end date of succession plan record.
730 -- object_version_number Object version number of record being
731 -- inserted or updated.
732 -- Post Success
733 -- Processing continues
734 --
735 -- Post Failure
736 -- Errors handled by the procedure
737 --
738 -- Access Status
739 -- Internal and External use.
740 Procedure chk_end_date(p_succession_plan_id in number,
741 p_start_date in date,
742 p_end_date in date,
743 p_object_version_number in number) is
744 --
745 l_proc varchar2(72) := g_package||'chk_end_date';
746 l_api_updating boolean;
747 --
748 Begin
749 --
750 hr_utility.set_location('Entering:'||l_proc, 5);
751 --
752 l_api_updating := per_suc_shd.api_updating
753 (p_succession_plan_id => p_succession_plan_id,
754 p_object_version_number => p_object_version_number);
755 --
756 if (l_api_updating
757 and (nvl(p_start_date,hr_api.g_date)
758 <> nvl(per_suc_shd.g_old_rec.start_date,hr_api.g_date)
759 or nvl(p_end_date,hr_api.g_date)
760 <> nvl(per_suc_shd.g_old_rec.end_date,hr_api.g_date))
761 or not l_api_updating) then
762 --
763 -- check if end date is greater than start date
764 --
765 if p_start_date > nvl(p_end_date,hr_api.g_eot) then
766 --
767 -- raise error as start date should be less than or equal to end date.
768 --
769 hr_utility.set_message(801,'HR_52003_SUC_CHK_END_DATE');
770 hr_utility.raise_error;
771 --
772 end if;
773 --
774 end if;
775 --
776 hr_utility.set_location(' Leaving:'||l_proc, 10);
777 --
778 End chk_end_date;
779 -- ----------------------------------------------------------------------------
780 -- |----------------------< chk_available_for_promotion >---------------------|
781 -- ----------------------------------------------------------------------------
782 --
783 -- Description
784 -- This procedure is used to check that the available_for_promotion field
785 -- falls within the 'YES_NO' lookup.
786 --
787 -- Pre Conditions
788 -- None.
789 --
790 -- In Parameters
791 -- succession_plan_id PK of record being inserted or updated.
792 -- available_for_promotion available_for_promotion lookup.
793 -- object_version_number Object version number of record being
794 -- inserted or updated.
795 -- effective_date effective date
796 --
797 -- Post Success
798 -- Processing continues
799 --
800 -- Post Failure
801 -- Error handled by procedure
802 --
803 -- Access Status
804 -- Internal and External use.
805 --
806 Procedure chk_available_for_promotion(p_succession_plan_id in number,
807 p_available_for_promotion in varchar2,
808 p_object_version_number in number,
809 p_effective_date in date) is
810 --
811 l_proc varchar2(72) := g_package||'chk_available_for_promotion';
812 l_api_updating boolean;
813 --
814 Begin
815 --
816 hr_utility.set_location('Entering:'||l_proc, 5);
817 --
818 l_api_updating := per_suc_shd.api_updating
819 (p_succession_plan_id => p_succession_plan_id,
820 p_object_version_number => p_object_version_number);
821 --
822 if (l_api_updating
823 and nvl(p_available_for_promotion,hr_api.g_varchar2)
827 -- check if value of available for promotion scale lookup falls within
824 <> per_suc_shd.g_old_rec.available_for_promotion
825 or not l_api_updating) then
826 --
828 -- lookup.
829 --
830 if p_available_for_promotion is not null then
831 --
832 if hr_api.not_exists_in_hr_lookups
833 (p_lookup_type => 'YES_NO',
834 p_lookup_code => p_available_for_promotion,
835 p_effective_date => p_effective_date) then
836 --
837 -- raise error as does not exist as lookup
838 --
839 per_suc_shd.constraint_error('PER_SUC_AVAIL_FOR_PROMOTION');
840 --
841 end if;
842 --
843 end if;
844 --
845 end if;
846 --
847 hr_utility.set_location('Leaving:'||l_proc,10);
848 --
849 end chk_available_for_promotion;
850
851
852
853 -- ----------------------------------------------------------------------------
854 -- |--------------------------< chk_person_start_date >------------------------|
855 -- ----------------------------------------------------------------------------
856 --
857 --- Bug#3207986 start
858 -- Description
859 -- This procedure is used to check that the earliest date entered is later
860 -- than the start date of the employee for the current position
861 --
862 --
863 -- Pre Conditions
864 -- None.
865 --
866 -- In Parameters
867 -- p_person_id person_id of the person on which
868 -- the transaction is being done.
869 -- p_start_date the effective date entered
870 --
871 -- Post Success
872 -- Processing continues
873 --
874 -- Post Failure
875 -- Error handled by procedure
876 --
877 -- Access Status
878 -- Internal and External use.
879 --
880 ------------------------------------------------------------------------
881
882
883 procedure chk_person_start_date( p_person_id per_people_f.person_id%TYPE,
884 p_start_date per_assignments_f.effective_start_date%TYPE) IS
885
886 l_curr_position_id hr_positions.position_id%TYPE;
887
888 CURSOR person_start_details (p_person_id per_people_f.person_id%TYPE,p_start_date per_assignments_f.effective_start_date%TYPE) IS
889 SELECT paf.position_id
890 FROM per_assignments_f paf
891 WHERE paf.person_id=p_person_id
892 AND (SYSDATE BETWEEN paf.effective_start_date
893 AND NVL(paf.effective_end_date, SYSDATE))
894 AND paf.effective_start_date <= p_start_date;
895 BEGIN
896 OPEN person_start_details(p_person_id => p_person_id,p_start_date=>p_start_date);
897
898 FETCH person_start_details INTO l_curr_position_id;
899
900 IF (person_start_details%NOTFOUND) THEN
901 CLOSE person_start_details;
902 hr_utility.set_message(801,'HR_52005_SUC_CHK_DATE');
903 hr_utility.raise_error;
904 ELSE
905 CLOSE person_start_details;
906 END IF;
907 END;
908 --- Bug#3207986 end
909 ------------------------------------------------------------------------------
910
911
912
913 -- ----------------------------------------------------------------------------
914 -- |----------------------------< chk_date >----------------------------------|
915 -- ----------------------------------------------------------------------------
916 --
917 -- Description
918 -- This procedure is used to check that the record being inserted or
919 -- updated is unique.
920 --
921 -- Pre Conditions
922 -- None.
923 --
924 -- In Parameters
925 -- succession_plan_id PK of record being inserted or updated.
926 -- person_id id of person being inserted.
927 -- position_id id of position being inserted.
928 -- start_date start date of succession plan record
929 -- end_date end date of succession plan record
930 -- object_version_number Object version number of record being
931 -- inserted or updated.
932 -- Post Success
933 -- Processing continues
934 --
935 -- Post Failure
936 -- Errors handled by the procedure
937 --
938 -- Access Status
939 -- Internal and External use.
940 Procedure chk_date(p_succession_plan_id in number,
941 p_position_id in number,
942 p_person_id in number,
943 p_start_date in date,
944 p_end_date in date,
945 p_object_version_number in number,
946 p_job_id in number,
947 p_successee_person_id in number) is
948 --
949 l_proc varchar2(72) := g_package||'chk_date';
950 l_api_updating boolean;
951 l_dummy varchar2(1);
952 --
953 cursor c1 is
954 select null
955 from per_succession_planning per
956 where per.person_id = p_person_id
957 and per.succession_plan_id <> nvl(p_succession_plan_id,-1)
958 and ( (p_position_id is not null and per.position_id = p_position_id) or
962 and (per.start_date
959 (p_job_id is not null and per.job_id = p_job_id) or
960 (p_successee_person_id is not null and per.successee_person_id = p_successee_person_id)
961 )
963 between p_start_date
964 and nvl(p_end_date,hr_api.g_eot)
965 or nvl(per.end_date,hr_api.g_eot)
966 between p_start_date
967 and nvl(p_end_date,hr_api.g_eot));
968 --
969 Begin
970 --
971 hr_utility.set_location('Entering:'||l_proc, 5);
972 --
973 l_api_updating := per_suc_shd.api_updating
974 (p_succession_plan_id => p_succession_plan_id,
975 p_object_version_number => p_object_version_number);
976 --
977 if (l_api_updating
978 and (nvl(p_start_date,hr_api.g_date)
979 <> nvl(per_suc_shd.g_old_rec.start_date,hr_api.g_date)
980 or nvl(p_end_date,hr_api.g_date)
981 <> nvl(per_suc_shd.g_old_rec.end_date,hr_api.g_date))
982 or not l_api_updating) then
983 --
984 -- check if succession plans overlap
985 --
986 open c1;
987 --
988 fetch c1 into l_dummy;
989 --
990 if c1%found then
991 --
992 close c1;
993 --
994 -- raise error as succession plan records overlap.
995 --
996 per_suc_shd.constraint_error('PER_SUCCESSION_PLANNING_UK');
997 --
998 end if;
999 --
1000 close c1;
1001 --
1002 end if;
1003 --
1004 hr_utility.set_location(' Leaving:'||l_proc, 10);
1005 --
1006 End chk_date;
1007 --
1008 -- -----------------------------------------------------------------------
1009 -- |------------------------------< chk_df >-----------------------------|
1010 -- -----------------------------------------------------------------------
1011 --
1012 -- Description:
1013 -- Validates the all Descriptive Flexfield values.
1014 --
1015 -- Pre-conditions:
1016 -- All other columns have been validated. Must be called as the
1017 -- last step from insert_validate and update_validate.
1018 --
1019 -- In Arguments:
1020 -- p_rec
1021 --
1022 -- Post Success:
1023 -- If the Descriptive Flexfield structure column and data values are
1024 -- all valid this procedure will end normally and processing will
1025 -- continue.
1026 --
1027 -- Post Failure:
1028 -- If the Descriptive Flexfield structure column value or any of
1029 -- the data values are invalid then an application error is raised as
1030 -- a PL/SQL exception.
1031 --
1032 -- Access Status:
1033 -- Internal Row Handler Use Only.
1034 --
1035 -- {End Of Comments}
1036 -- ----------------------------------------------------------------------------
1037 --
1038 procedure chk_df
1039 (p_rec in per_suc_shd.g_rec_type) is
1040 --
1041 l_proc varchar2(72) := g_package||'chk_df';
1042 --
1043 begin
1044 hr_utility.set_location('Entering:'||l_proc, 10);
1045 --
1046 if ((p_rec.succession_plan_id is not null) and (
1047 nvl(per_suc_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1048 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1049 nvl(per_suc_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1050 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1051 nvl(per_suc_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1052 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1053 nvl(per_suc_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1054 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1055 nvl(per_suc_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1056 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1057 nvl(per_suc_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1058 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1059 nvl(per_suc_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1060 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1061 nvl(per_suc_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1062 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1063 nvl(per_suc_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1064 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1065 nvl(per_suc_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1066 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1067 nvl(per_suc_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1068 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1069 nvl(per_suc_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1070 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1071 nvl(per_suc_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1072 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1073 nvl(per_suc_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1074 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1075 nvl(per_suc_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1076 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1077 nvl(per_suc_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1078 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1079 nvl(per_suc_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1080 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1081 nvl(per_suc_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1082 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1083 nvl(per_suc_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1084 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1085 nvl(per_suc_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1086 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1087 nvl(per_suc_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1088 nvl(p_rec.attribute20, hr_api.g_varchar2)))
1089 or
1090 (p_rec.succession_plan_id is null) then
1091 --
1092 -- Only execute the validation if absolutely necessary:
1093 -- a) During update, the structure column value or any
1094 -- of the attribute values have actually changed.
1095 -- b) During insert.
1096 --
1097 hr_dflex_utility.ins_or_upd_descflex_attribs
1098 (p_appl_short_name => 'PER'
1102 ,p_attribute1_value => p_rec.attribute1
1099 ,p_descflex_name => 'PER_SUCCESSION_PLANNING'
1100 ,p_attribute_category => p_rec.attribute_category
1101 ,p_attribute1_name => 'ATTRIBUTE1'
1103 ,p_attribute2_name => 'ATTRIBUTE2'
1104 ,p_attribute2_value => p_rec.attribute2
1105 ,p_attribute3_name => 'ATTRIBUTE3'
1106 ,p_attribute3_value => p_rec.attribute3
1107 ,p_attribute4_name => 'ATTRIBUTE4'
1108 ,p_attribute4_value => p_rec.attribute4
1109 ,p_attribute5_name => 'ATTRIBUTE5'
1110 ,p_attribute5_value => p_rec.attribute5
1111 ,p_attribute6_name => 'ATTRIBUTE6'
1112 ,p_attribute6_value => p_rec.attribute6
1113 ,p_attribute7_name => 'ATTRIBUTE7'
1114 ,p_attribute7_value => p_rec.attribute7
1115 ,p_attribute8_name => 'ATTRIBUTE8'
1116 ,p_attribute8_value => p_rec.attribute8
1117 ,p_attribute9_name => 'ATTRIBUTE9'
1118 ,p_attribute9_value => p_rec.attribute9
1119 ,p_attribute10_name => 'ATTRIBUTE10'
1120 ,p_attribute10_value => p_rec.attribute10
1121 ,p_attribute11_name => 'ATTRIBUTE11'
1122 ,p_attribute11_value => p_rec.attribute11
1123 ,p_attribute12_name => 'ATTRIBUTE12'
1124 ,p_attribute12_value => p_rec.attribute12
1125 ,p_attribute13_name => 'ATTRIBUTE13'
1126 ,p_attribute13_value => p_rec.attribute13
1127 ,p_attribute14_name => 'ATTRIBUTE14'
1128 ,p_attribute14_value => p_rec.attribute14
1129 ,p_attribute15_name => 'ATTRIBUTE15'
1130 ,p_attribute15_value => p_rec.attribute15
1131 ,p_attribute16_name => 'ATTRIBUTE16'
1132 ,p_attribute16_value => p_rec.attribute16
1133 ,p_attribute17_name => 'ATTRIBUTE17'
1134 ,p_attribute17_value => p_rec.attribute17
1135 ,p_attribute18_name => 'ATTRIBUTE18'
1136 ,p_attribute18_value => p_rec.attribute18
1137 ,p_attribute19_name => 'ATTRIBUTE19'
1138 ,p_attribute19_value => p_rec.attribute19
1139 ,p_attribute20_name => 'ATTRIBUTE20'
1140 ,p_attribute20_value => p_rec.attribute20);
1141 end if;
1142 --
1143 hr_utility.set_location(' Leaving:'||l_proc, 20);
1144 end chk_df;
1145 --
1146 -- ----------------------------------------------------------------------------
1147 -- |---------------------------< insert_validate >----------------------------|
1148 -- ----------------------------------------------------------------------------
1149 Procedure insert_validate(p_rec in per_suc_shd.g_rec_type,
1150 p_effective_date in date) is
1151 --
1152 l_proc varchar2(72) := g_package||'insert_validate';
1153 --
1154 Begin
1155 hr_utility.set_location('Entering:'||l_proc, 5);
1156 --
1157 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1158 --
1159 -- Call all supporting business operations
1160 --
1161 -- Business Rule Mapping
1162 -- =====================
1163 -- CHK_SUCCESSION_PLANN_ID
1164 --
1165 chk_succession_plan_id(p_rec.succession_plan_id,
1166 p_rec.object_version_number);
1167 --
1168 -- Business Rule Mapping
1169 -- =====================
1170 -- CHK_PERSON_ID
1171 --
1172 chk_successor(p_effective_date,
1173 p_rec.succession_plan_id,
1174 p_rec.person_id,
1175 p_rec.successee_person_id,
1176 p_rec.object_version_number);
1177 --
1178 -- Business Rule Mapping
1179 -- =====================
1180 -- CHK_SUCCESSEE
1181 --
1182 chk_successee( p_effective_date,
1183 p_rec.succession_plan_id,
1184 p_rec.job_id,
1185 p_rec.position_id,
1186 p_rec.successee_person_id,
1187 p_rec.object_version_number);
1188 --
1189 -- Business Rule Mapping
1190 -- =====================
1191 -- CHK_TIME_SCALE
1192 --
1193 chk_time_scale(p_rec.succession_plan_id,
1194 p_rec.time_scale,
1195 p_rec.object_version_number,
1196 p_effective_date);
1197 --
1198 -- Business Rule Mapping
1199 -- =====================
1200 -- CHK_START_DATE
1201 --
1202 chk_start_date(p_rec.succession_plan_id,
1203 p_rec.start_date,
1204 p_rec.object_version_number);
1205
1206 --
1207 -- Business Rule Mapping
1208 -- =====================
1209 -- CHK_END_DATE
1210 --
1211 chk_end_date(p_rec.succession_plan_id,
1212 p_rec.start_date,
1213 p_rec.end_date,
1214 p_rec.object_version_number);
1215 --
1216 -- Business Rule Mapping
1217 -- =====================
1218 -- CHK_AVAILABLE_FOR_PROMOTION
1219 --
1220 chk_available_for_promotion(p_rec.succession_plan_id,
1221 p_rec.available_for_promotion,
1222 p_rec.object_version_number,
1223 p_effective_date);
1224
1225 --
1226 -- Business Rule Mapping
1227 -- =====================
1228 -- CHK_PERSON_START_DATE
1229 --
1230 -- Bug#3207986
1231 chk_person_start_date(p_rec.person_id,p_rec.start_date);
1232
1233 --
1234 -- Business Rule Mapping
1235 -- =====================
1236 -- CHK_DATE
1237 --
1238 chk_date(p_rec.succession_plan_id,
1239 p_rec.position_id,
1240 p_rec.person_id,
1241 p_rec.start_date,
1242 p_rec.end_date,
1243 p_rec.object_version_number,
1244 p_rec.job_id,
1245 p_rec.successee_person_id);
1246 --
1247 -- Descriptive flex check
1248 -- ======================
1249 --
1250 /*
1251 IF hr_general.get_calling_context <>'FORMS' THEN
1252 per_suc_flex.df(p_rec => p_rec);
1253 END IF;
1254 */
1258 per_suc_bus.chk_df(p_rec => p_rec);
1255 --
1256 -- call descriptive flexfield validation routines
1257 --
1259 --
1260 hr_utility.set_location(' Leaving:'||l_proc, 10);
1261 End insert_validate;
1262 --
1263 -- ----------------------------------------------------------------------------
1264 -- |---------------------------< update_validate >----------------------------|
1265 -- ----------------------------------------------------------------------------
1266 Procedure update_validate(p_rec in per_suc_shd.g_rec_type,
1267 p_effective_date in date) is
1268 --
1269 l_proc varchar2(72) := g_package||'update_validate';
1270 --
1271 Begin
1272 hr_utility.set_location('Entering:'||l_proc, 5);
1273 --
1274 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1275 --
1276 -- Call all supporting business operations
1277 --
1278 -- Business Rule Mapping
1279 -- =====================
1280 -- CHK_SUCCESSION_PLANN_ID
1281 --
1282 chk_succession_plan_id(p_rec.succession_plan_id,
1283 p_rec.object_version_number);
1284 --
1285 -- Business Rule Mapping
1286 -- =====================
1287 -- CHK_PERSON_ID
1288 --
1289 chk_successor(p_effective_date,
1290 p_rec.succession_plan_id,
1291 p_rec.person_id,
1292 p_rec.successee_person_id,
1293 p_rec.object_version_number);
1294 --
1295 -- Business Rule Mapping
1296 -- =====================
1297 -- CHK_SUCCESSEE
1298 --
1299 chk_successee( p_effective_date,
1300 p_rec.succession_plan_id,
1301 p_rec.job_id,
1302 p_rec.position_id,
1303 p_rec.successee_person_id,
1304 p_rec.object_version_number);
1305 --
1306 --
1307 --
1308 -- Business Rule Mapping
1309 -- =====================
1310 -- CHK_TIME_SCALE
1311 --
1312 chk_time_scale(p_rec.succession_plan_id,
1313 p_rec.time_scale,
1314 p_rec.object_version_number,
1315 p_effective_date);
1316 --
1317 -- Business Rule Mapping
1318 -- =====================
1319 -- CHK_START_DATE
1320 --
1321 chk_start_date(p_rec.succession_plan_id,
1322 p_rec.start_date,
1323 p_rec.object_version_number);
1324
1325 --
1326 -- Business Rule Mapping
1327 -- =====================
1328 -- CHK_END_DATE
1329 --
1330 chk_end_date(p_rec.succession_plan_id,
1331 p_rec.start_date,
1332 p_rec.end_date,
1333 p_rec.object_version_number);
1334 --
1335 -- Business Rule Mapping
1336 -- =====================
1337 -- CHK_AVAILABLE_FOR_PROMOTION
1338 --
1339 chk_available_for_promotion(p_rec.succession_plan_id,
1340 p_rec.available_for_promotion,
1341 p_rec.object_version_number,
1342 p_effective_date);
1343
1344 --
1345 -- Business Rule Mapping
1346 -- =====================
1347 -- CHK_PERSON_START_DATE
1348 --
1349 -- Bug#3207986
1350 chk_person_start_date(p_rec.person_id,p_rec.start_date);
1351
1352 --
1353 -- Business Rule Mapping
1354 -- =====================
1355 -- CHK_DATE
1356 --
1357 chk_date(p_rec.succession_plan_id,
1358 p_rec.position_id,
1359 p_rec.person_id,
1360 p_rec.start_date,
1361 p_rec.end_date,
1362 p_rec.object_version_number,
1363 p_rec.job_id,
1364 p_rec.successee_person_id);
1365 --
1366 -- Descriptive flex check
1367 -- ======================
1368 --
1369 /*
1370 IF hr_general.get_calling_context <>'FORMS' THEN
1371 per_suc_flex.df(p_rec => p_rec);
1372 END IF;
1373 */
1374 --
1375 -- call descriptive flexfield validation routines
1376 --
1377 per_suc_bus.chk_df(p_rec => p_rec);
1378 --
1379 hr_utility.set_location(' Leaving:'||l_proc, 10);
1380 End update_validate;
1381 --
1382 -- ----------------------------------------------------------------------------
1383 -- |---------------------------< delete_validate >----------------------------|
1384 -- ----------------------------------------------------------------------------
1385 Procedure delete_validate(p_rec in per_suc_shd.g_rec_type) is
1386 --
1387 l_proc varchar2(72) := g_package||'delete_validate';
1388 --
1389 Begin
1390 hr_utility.set_location('Entering:'||l_proc, 5);
1391 --
1392 -- Call all supporting business operations
1393 --
1394 hr_utility.set_location(' Leaving:'||l_proc, 10);
1395 End delete_validate;
1396 --
1397 end per_suc_bus;