[Home] [Help]
PACKAGE BODY: APPS.PER_SIP_BUS
Source
1 Package Body per_sip_bus as
2 /* $Header: pesiprhi.pkb 120.6.12020000.2 2012/07/05 05:02:18 amnaraya ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_sip_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_successor_in_plan_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_successor_in_plan_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , per_sp_successor_in_plan sip
32 where sip.successor_in_plan_id = p_successor_in_plan_id
33 and pbg.business_group_id = sip.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 l_legislation_code varchar2(150);
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'successor_in_plan_id'
50 ,p_argument_value => p_successor_in_plan_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id
55 , l_legislation_code;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
66 => nvl(p_associated_column1,'SUCCESSOR_IN_PLAN_ID')
67 );
68 --
69 else
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 -- Set the sessions legislation context in HR_SESSION_DATA
79 --
80 hr_api.set_legislation_context(l_legislation_code);
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_successor_in_plan_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100 from per_business_groups_perf pbg
101 , per_sp_successor_in_plan sip
102 where sip.successor_in_plan_id = p_successor_in_plan_id
103 and pbg.business_group_id = sip.business_group_id;
104 --
105 -- Declare local variables
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'successor_in_plan_id'
119 ,p_argument_value => p_successor_in_plan_id
120 );
121 --
122 if ( nvl(per_sip_bus.g_successor_in_plan_id, hr_api.g_number)
123 = p_successor_in_plan_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 l_legislation_code := per_sip_bus.g_legislation_code;
130 hr_utility.set_location(l_proc, 20);
131 else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 open csr_leg_code;
137 fetch csr_leg_code into l_legislation_code;
138 --
139 if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 close csr_leg_code;
144 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 fnd_message.raise_error;
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 per_sip_bus.g_successor_in_plan_id := p_successor_in_plan_id;
154 per_sip_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |------------------------------< chk_df >----------------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- Description:
165 -- Validates all the Descriptive Flexfield values.
166 --
167 -- Prerequisites:
168 -- All other columns have been validated. Must be called as the
169 -- last step from insert_validate and update_validate.
170 --
171 -- In Arguments:
172 -- p_rec
173 --
174 -- Post Success:
175 -- If the Descriptive Flexfield structure column and data values are
176 -- all valid this procedure will end normally and processing will
177 -- continue.
178 --
179 -- Post Failure:
180 -- If the Descriptive Flexfield structure column value or any of
181 -- the data values are invalid then an application error is raised as
182 -- a PL/SQL exception.
183 --
184 -- Access Status:
185 -- Internal Row Handler Use Only.
186 --
187 -- ----------------------------------------------------------------------------
188 procedure chk_df
189 (p_rec in per_sip_shd.g_rec_type
190 ) is
191 --
192 l_proc varchar2(72) := g_package || 'chk_df';
193 --
194 begin
195 hr_utility.set_location('Entering:'||l_proc,10);
196 --
197 if ((p_rec.successor_in_plan_id is not null) and (
198 nvl(per_sip_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
199 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
200 nvl(per_sip_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
201 nvl(p_rec.attribute1, hr_api.g_varchar2) or
202 nvl(per_sip_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
203 nvl(p_rec.attribute2, hr_api.g_varchar2) or
204 nvl(per_sip_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
205 nvl(p_rec.attribute3, hr_api.g_varchar2) or
206 nvl(per_sip_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
207 nvl(p_rec.attribute4, hr_api.g_varchar2) or
208 nvl(per_sip_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
209 nvl(p_rec.attribute5, hr_api.g_varchar2) or
210 nvl(per_sip_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
211 nvl(p_rec.attribute6, hr_api.g_varchar2) or
212 nvl(per_sip_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
213 nvl(p_rec.attribute7, hr_api.g_varchar2) or
214 nvl(per_sip_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
215 nvl(p_rec.attribute8, hr_api.g_varchar2) or
216 nvl(per_sip_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute9, hr_api.g_varchar2) or
218 nvl(per_sip_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute10, hr_api.g_varchar2) or
220 nvl(per_sip_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute11, hr_api.g_varchar2) or
222 nvl(per_sip_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute12, hr_api.g_varchar2) or
224 nvl(per_sip_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute13, hr_api.g_varchar2) or
226 nvl(per_sip_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute14, hr_api.g_varchar2) or
228 nvl(per_sip_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
229 nvl(p_rec.attribute15, hr_api.g_varchar2) or
230 nvl(per_sip_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
231 nvl(p_rec.attribute16, hr_api.g_varchar2) or
232 nvl(per_sip_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
233 nvl(p_rec.attribute17, hr_api.g_varchar2) or
234 nvl(per_sip_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
235 nvl(p_rec.attribute18, hr_api.g_varchar2) or
236 nvl(per_sip_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
237 nvl(p_rec.attribute19, hr_api.g_varchar2) or
238 nvl(per_sip_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
239 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
240 or (p_rec.successor_in_plan_id is null and p_rec.attribute_category is not null) then
241 --
242 -- Only execute the validation if absolutely necessary:
243 -- a) During update, the structure column value or any
244 -- of the attribute values have actually changed.
245 -- b) During insert.
246 --
247 hr_dflex_utility.ins_or_upd_descflex_attribs
248 (p_appl_short_name => 'PER'
249 ,p_descflex_name => 'PER_SUCCESSION_PLANNING'
250 ,p_attribute_category => p_rec.attribute_category
251 ,p_attribute1_name => 'ATTRIBUTE1'
252 ,p_attribute1_value => p_rec.attribute1
253 ,p_attribute2_name => 'ATTRIBUTE2'
254 ,p_attribute2_value => p_rec.attribute2
255 ,p_attribute3_name => 'ATTRIBUTE3'
256 ,p_attribute3_value => p_rec.attribute3
257 ,p_attribute4_name => 'ATTRIBUTE4'
258 ,p_attribute4_value => p_rec.attribute4
259 ,p_attribute5_name => 'ATTRIBUTE5'
260 ,p_attribute5_value => p_rec.attribute5
261 ,p_attribute6_name => 'ATTRIBUTE6'
262 ,p_attribute6_value => p_rec.attribute6
263 ,p_attribute7_name => 'ATTRIBUTE7'
264 ,p_attribute7_value => p_rec.attribute7
265 ,p_attribute8_name => 'ATTRIBUTE8'
266 ,p_attribute8_value => p_rec.attribute8
267 ,p_attribute9_name => 'ATTRIBUTE9'
268 ,p_attribute9_value => p_rec.attribute9
269 ,p_attribute10_name => 'ATTRIBUTE10'
270 ,p_attribute10_value => p_rec.attribute10
271 ,p_attribute11_name => 'ATTRIBUTE11'
272 ,p_attribute11_value => p_rec.attribute11
273 ,p_attribute12_name => 'ATTRIBUTE12'
274 ,p_attribute12_value => p_rec.attribute12
275 ,p_attribute13_name => 'ATTRIBUTE13'
276 ,p_attribute13_value => p_rec.attribute13
277 ,p_attribute14_name => 'ATTRIBUTE14'
278 ,p_attribute14_value => p_rec.attribute14
279 ,p_attribute15_name => 'ATTRIBUTE15'
280 ,p_attribute15_value => p_rec.attribute15
281 ,p_attribute16_name => 'ATTRIBUTE16'
282 ,p_attribute16_value => p_rec.attribute16
283 ,p_attribute17_name => 'ATTRIBUTE17'
284 ,p_attribute17_value => p_rec.attribute17
285 ,p_attribute18_name => 'ATTRIBUTE18'
286 ,p_attribute18_value => p_rec.attribute18
287 ,p_attribute19_name => 'ATTRIBUTE19'
288 ,p_attribute19_value => p_rec.attribute19
289 ,p_attribute20_name => 'ATTRIBUTE20'
290 ,p_attribute20_value => p_rec.attribute20
291 );
292 end if;
293 --
294 hr_utility.set_location(' Leaving:'||l_proc,20);
295 end chk_df;
296 --
297 -- ----------------------------------------------------------------------------
298 -- |-----------------------< chk_non_updateable_args >------------------------|
299 -- ----------------------------------------------------------------------------
300 -- {Start Of Comments}
301 --
302 -- Description:
303 -- This procedure is used to ensure that non updateable attributes have
304 -- not been updated. If an attribute has been updated an error is generated.
305 --
306 -- Pre Conditions:
307 -- g_old_rec has been populated with details of the values currently in
308 -- the database.
309 --
310 -- In Arguments:
311 -- p_rec has been populated with the updated values the user would like the
312 -- record set to.
313 --
314 -- Post Success:
315 -- Processing continues if all the non updateable attributes have not
316 -- changed.
317 --
318 -- Post Failure:
319 -- An application error is raised if any of the non updatable attributes
320 -- have been altered.
321 --
322 -- {End Of Comments}
323 -- ----------------------------------------------------------------------------
324 Procedure chk_non_updateable_args
325 (p_effective_date in date
326 ,p_rec in per_sip_shd.g_rec_type
327 ) IS
328 --
329 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
330 --
331 Begin
332 --
333 -- Only proceed with the validation if a row exists for the current
334 -- record in the HR Schema.
335 --
336 IF NOT per_sip_shd.api_updating
337 (p_successor_in_plan_id => p_rec.successor_in_plan_id
338 ,p_object_version_number => p_rec.object_version_number
339 ) THEN
340 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
341 fnd_message.set_token('PROCEDURE ', l_proc);
342 fnd_message.set_token('STEP ', '5');
343 fnd_message.raise_error;
344 END IF;
345 --
346 -- EDIT_HERE: Add checks to ensure non-updateable args have
347 -- not been updated.
348 --
349 End chk_non_updateable_args;
350
351 --
352 -- ----------------------------------------------------------------------------
353 -- |---------------------------< chkMaxPlans >----------------------------|
354 -- ----------------------------------------------------------------------------
355 Procedure chkMaxPlans
356 (p_successor_id in number
357 ) is
358 --
359 l_proc varchar2(72) := g_package||'chkMaxPlans';
360 l_plan_count number;
361 l_max_plans number;
362 l_name varchar2(240);
363
364 cursor csrPersonName is
365 select full_name
366 from per_all_people_f
367 where person_id=p_successor_id
368 and trunc(sysdate) between effective_start_date and effective_end_date;
369 --
370 Begin
371 hr_utility.set_location('Entering:'||l_proc, 5);
372
373 l_max_plans := fnd_profile.value('PER_SP_MAX_PLANS_FOR_SCSR');
374
375 if l_max_plans is not null and l_max_plans >=0
376 then
377
378 hr_utility.set_location('l_max_plans :'||l_max_plans, 40);
379
380 select count(*) into l_plan_count
381 from per_sp_successor_in_plan scsr,
382 per_sp_plan pl
383 where scsr.successor_id=p_successor_id
384 and scsr.status='A'
385 and scsr.plan_id=pl.plan_id
386 and pl.status='A';
387
388 hr_utility.set_location('l_plan_count :'||l_plan_count, 50);
389
390 if l_plan_count >= l_max_plans
391 then
392 open csrPersonName;
393 fetch csrPersonName into l_name;
394 close csrPersonName;
395
396 hr_utility.set_location(l_proc, 80);
397 hr_utility.set_message (800, 'PER_33888_SP_SCSR_MAX_PLANS');
398 hr_utility.set_message_token('SCSR',l_name);
399 fnd_msg_pub.add;
400 end if;
401 end if;
402 --
403 hr_utility.set_location(' Leaving:'||l_proc, 99);
404 End chkMaxPlans;
405
406 -- ----------------------------------------------------------------------------
407 -- |---------------------------< chkPlanStatus >----------------------------|
408 -- ----------------------------------------------------------------------------
409 Procedure chkPlanStatus
410 (p_plan_id in number
411 ) is
412 --
413 l_proc varchar2(72) := g_package||'chkPlanStatus';
414 l_plan_status varchar2(1);
415
416 cursor csrPlanStatus is
417 select status
418 from per_sp_plan
419 where plan_id=p_plan_id;
420
421 --
422 Begin
423 hr_utility.set_location('Entering:'||l_proc, 5);
424
425 open csrPlanStatus;
426 fetch csrPlanStatus into l_plan_status;
427 close csrPlanStatus;
428
429 if (l_plan_status = 'I')
430 then
431 hr_utility.set_location(l_proc, 80);
432 hr_utility.set_message (800, 'PER_ADD_DEL_SCRS_INA_PLAN');
433 fnd_msg_pub.add;
434 end if;
435 --
436 hr_utility.set_location(' Leaving:'||l_proc, 99);
437 End chkPlanStatus;
438
439 --
440 -- ----------------------------------------------------------------------------
441 -- |---------------------------< chkPersonType >----------------------------|
442 -- ----------------------------------------------------------------------------
443 Procedure chkPersonType
444 (p_plan_id in Number
445 ,p_successor_id in Number
446 ) is
447 --
448 l_proc varchar2(72) := g_package||'chkPersonType';
449 l_successee_id number;
450 l_type varchar2(30);
451 l_alw_npws varchar2(1);
452 l_alw_applicants varchar2(1);
453 l_emp varchar2(1);
454 l_npw varchar2(1);
455 l_appl varchar2(1);
456 l_person_type varchar2(30);
457 l_name varchar2(240);
458
459 cursor csrPersonName is
460 select full_name
461 from per_all_people_f
462 where person_id=p_successor_id
463 and trunc(sysdate) between effective_start_date and effective_end_date;
464
465 cursor csrPlanDtls is
466 select pl.successee_id, pl.plan_type
467 from per_sp_plan pl
468 where pl.plan_id=p_plan_id;
469
470 cursor csrSuccesseeDtls(p_successee_id number, p_type varchar2) is
471 select dtls.allow_npws, dtls.allow_applicants
472 from per_sp_successee_details dtls
473 where dtls.successee_id=p_successee_id
474 and dtls.successee_type=p_type;
475
476 cursor csrPersonType is
477 SELECT
478 ppt.system_person_type
479 FROM
480 per_all_people_f papf,
481 per_person_type_usages_f ptu,
482 per_person_types ppt
483 where papf.person_id=p_successor_id
484 and papf.person_id=ptu.person_id
485 and ptu.person_type_id=ppt.person_type_id
486 and trunc(sysdate) between ptu.effective_start_date and ptu.effective_end_date
487 and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date;
488
489 --
490 Begin
491 hr_utility.set_location('Entering:'||l_proc, 5);
492
493 open csrPersonType;
494 loop
495 fetch csrPersonType into l_person_type;
496 if csrPersonType%notfound
497 then
498 exit;
499 end if;
500
501 hr_utility.set_location(l_proc, 20);
502
503 if l_person_type = 'EMP'
504 then
505 l_emp :='Y';
506 exit;
507 elsif l_person_type = 'APL'
508 then
509 l_appl := 'Y';
510 elsif l_person_type = 'CWK'
511 then
512 l_npw := 'Y';
513 end if;
514
515 end loop;
516 close csrPersonType;
517
518 if l_emp = 'Y' then
519 hr_utility.set_location(l_proc, 40);
520 return ;
521 end if;
522
523
524
525 hr_utility.set_location(l_proc, 55);
526
527 open csrPlanDtls;
528 fetch csrPlanDtls into l_successee_id, l_type;
529 close csrPlanDtls;
530
531 if l_successee_id is not null
532 then
533
534 hr_utility.set_location(l_proc, 60);
535
536 open csrSuccesseeDtls(l_successee_id, l_type);
537 fetch csrSuccesseeDtls into l_alw_npws, l_alw_applicants;
538 if csrSuccesseeDtls%notfound
539 then
540 hr_utility.set_location(l_proc, 70);
541 close csrSuccesseeDtls;
542 return;
543 end if;
544 close csrSuccesseeDtls;
545
546 if (l_alw_npws = 'Y' and l_npw = 'Y') or
547 (l_alw_applicants = 'Y' and l_appl = 'Y')
548 then
549 hr_utility.set_location(l_proc, 75);
550 return ;
551 else
552 open csrPersonName;
553 fetch csrPersonName into l_name;
554 close csrPersonName;
555 hr_utility.set_location(l_proc, 80);
556 hr_utility.set_message (800, 'PER_33881_SP_INV_PERSON_TYPE');
557 hr_utility.set_message_token('Invalid Successor Name',l_name);
558 fnd_msg_pub.add;
559 end if;
560
561 end if;
562
563
564 --
565 hr_utility.set_location(' Leaving:'||l_proc, 99);
566 End chkPersonType;
567
568 --
569 -- ----------------------------------------------------------------------------
570 -- |---------------------------< chkMaxSuccessors >----------------------------|
571 -- ----------------------------------------------------------------------------
572 Procedure chkMaxSuccessors
573 (p_plan_id in number
574 ) is
575
576 l_proc varchar2(72) := g_package||'chkMaxSuccessors';
577
578 l_scsr_count number;
579 l_max_successors number;
580 l_successee_id number;
581 l_type varchar2(30);
582
583
584 cursor csrPlanDtls is
585 select pl.successee_id, pl.plan_type
586 from per_sp_plan pl
587 where pl.plan_id=p_plan_id;
588
589 cursor csrSuccesseeDtls(p_successee_id number, p_type varchar2) is
590 select dtls.max_successors
591 from per_sp_successee_details dtls
592 where dtls.successee_id=p_successee_id
593 and dtls.successee_type=p_type;
594
595 --
596
597 begin
598
599 hr_utility.set_location('Entering:'||l_proc, 5);
600
601 open csrPlanDtls;
602 fetch csrPlanDtls into l_successee_id, l_type;
603 close csrPlanDtls;
604
605 if l_successee_id is not null
606 then
607
608 hr_utility.set_location(l_proc, 60);
609
610 open csrSuccesseeDtls(l_successee_id, l_type);
611 fetch csrSuccesseeDtls into l_max_successors;
612 if csrSuccesseeDtls%notfound
613 then
614 hr_utility.set_location(l_proc, 70);
615 close csrSuccesseeDtls;
616 return;
617 end if;
618 close csrSuccesseeDtls;
619
620 SELECT count (*) into l_scsr_count
621 FROM per_sp_successor_in_plan
622 WHERE plan_id = p_plan_id
623 AND status='A';
624 if l_scsr_count >= l_max_successors
625 then
626 hr_utility.set_location(l_proc, 80);
627 fnd_msg_pub.set_search_name(800, 'PER_33882_SP_MAX_SCSRS');
628 fnd_msg_pub.Delete_Msg;
629 hr_utility.set_message (800, 'PER_33882_SP_MAX_SCSRS');
630 fnd_msg_pub.add;
631 end if;
632
633 end if;
634
635 --
636 hr_utility.set_location(' Leaving:'||l_proc, 99);
637 End chkMaxSuccessors;
638 --
639 -- ----------------------------------------------------------------------------
640 -- |---------------------------< chkDates >----------------------------|
641 -- ----------------------------------------------------------------------------
642 Procedure chkDates
643 (p_successor_id in number
644 ,p_plan_id in number
645 ,p_start_date in date
646 ,p_end_date in date
647 ) is
648 --
649 l_proc varchar2(72) := g_package||'chkDates';
650 l_pl_strt_date date;
651 l_pl_end_date date;
652 l_name varchar2(240);
653
654 cursor csrPersonName is
655 select full_name
656 from per_all_people_f
657 where person_id=p_successor_id
658 and trunc(sysdate) between effective_start_date and effective_end_date;
659
660 cursor csrPlanDtls is
661 select pl.start_date, pl.end_date
662 from per_sp_plan pl
663 where pl.plan_id=p_plan_id;
664
665 --
666 Begin
667 hr_utility.set_location('Entering:'||l_proc, 5);
668
669 open csrPlanDtls;
670 fetch csrPlanDtls into l_pl_strt_date, l_pl_end_date;
671 close csrPlanDtls;
672
673 open csrPersonName;
674 fetch csrPersonName into l_name;
675 close csrPersonName;
676
677 if p_start_date is not null and p_end_date is not null and p_start_date > p_end_date
678 then
679 hr_utility.set_location(l_proc, 10);
680 hr_utility.set_message (800, 'PER_33883_SP_ENDDT_LESS_STRTDT');
681 hr_utility.set_message_token('SCSR',l_name);
682 fnd_msg_pub.add;
683 end if;
684
685 if (p_start_date is not null and (p_start_date < l_pl_strt_date or p_start_date > l_pl_end_date)) or
686 (p_end_date is not null and (p_end_date > l_pl_end_date or p_end_date < l_pl_strt_date))
687 then
688 hr_utility.set_location(l_proc, 10);
689 hr_utility.set_message (800, 'PER_33884_SP_SCSR_DT_BTW_PL_DT');
690 hr_utility.set_message_token('SCSR',l_name);
691 fnd_msg_pub.add;
692 end if;
693
694
695
696 --
697 hr_utility.set_location(' Leaving:'||l_proc, 10);
698 End chkDates;
699
700 --
701 -- ----------------------------------------------------------------------------
702 -- |---------------------------< insert_validate >----------------------------|
703 -- ----------------------------------------------------------------------------
704 Procedure insert_validate
705 (p_effective_date in date
706 ,p_rec in per_sip_shd.g_rec_type
707 ) is
708 --
709 l_proc varchar2(72) := g_package||'insert_validate';
710 --
711 Begin
712 hr_utility.set_location('Entering:'||l_proc, 5);
713 --
714 -- Call all supporting business operations
715 --
716 hr_api.validate_bus_grp_id
717 (p_business_group_id => p_rec.business_group_id
718 ,p_associated_column1 => per_sip_shd.g_tab_nam
719 || '.BUSINESS_GROUP_ID');
720
721 --Fix for Bug 13812132
722 chkPlanStatus(p_rec.plan_id);
723 chkMaxPlans(p_rec.successor_id);
724
725 --
726 -- After validating the set of important attributes,
727 -- if Multiple Message detection is enabled and at least
728 -- one error has been found then abort further validation.
729 --
730 hr_multi_message.end_validation_set;
731
732 chkMaxSuccessors(p_rec.plan_id);
733
734 chkPersonType(p_rec.plan_id, p_rec.successor_id);
735
736 chkDates(p_rec.successor_id, p_rec.plan_id, p_rec.earliest_succession_date, p_rec.latest_succession_date);
737
738 --
739 -- Validate Dependent Attributes
740 --
741 --
742 per_sip_bus.chk_df(p_rec);
743 --
744 hr_utility.set_location(' Leaving:'||l_proc, 10);
745 End insert_validate;
746 --
747 -- ----------------------------------------------------------------------------
748 -- |---------------------------< update_validate >----------------------------|
749 -- ----------------------------------------------------------------------------
750 Procedure update_validate
751 (p_effective_date in date
752 ,p_rec in per_sip_shd.g_rec_type
753 ) is
754 --
755 l_proc varchar2(72) := g_package||'update_validate';
756 --
757 Begin
758 hr_utility.set_location('Entering:'||l_proc, 5);
759 --
760 -- Call all supporting business operations
761 --
762 hr_api.validate_bus_grp_id
763 (p_business_group_id => p_rec.business_group_id
764 ,p_associated_column1 => per_sip_shd.g_tab_nam
765 || '.BUSINESS_GROUP_ID');
766
767 --Fix for Bug 13812132
768 chkPlanStatus(p_rec.plan_id);
769 chkDates(p_rec.successor_id, p_rec.plan_id, p_rec.earliest_succession_date, p_rec.latest_succession_date);
770
771 --
772 -- After validating the set of important attributes,
773 -- if Multiple Message detection is enabled and at least
774 -- one error has been found then abort further validation.
775 --
776 hr_multi_message.end_validation_set;
777 --
778 -- Validate Dependent Attributes
779 --
780 chk_non_updateable_args
781 (p_effective_date => p_effective_date
782 ,p_rec => p_rec
783 );
784 --
785 --
786 per_sip_bus.chk_df(p_rec);
787 --
788 hr_utility.set_location(' Leaving:'||l_proc, 10);
789 End update_validate;
790 --
791 -- ----------------------------------------------------------------------------
792 -- |---------------------------< delete_validate >----------------------------|
793 -- ----------------------------------------------------------------------------
794 Procedure delete_validate
795 (p_rec in per_sip_shd.g_rec_type
796 ) is
797 --
798 l_proc varchar2(72) := g_package||'delete_validate';
799 --
800 Begin
801 hr_utility.set_location('Entering:'||l_proc, 5);
802 --
803 -- Call all supporting business operations
804 --
805 hr_utility.set_location(' Leaving:'||l_proc, 10);
806 End delete_validate;
807 --
808 end per_sip_bus;