[Home] [Help]
PACKAGE BODY: APPS.BEN_PPL_BUS
Source
1 Package Body ben_ppl_bus as
2 /* $Header: bepplrhi.pkb 120.0.12000000.3 2007/02/08 07:41:23 vborkar noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ppl_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------< chk_ptnl_ler_for_per_id >--------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- 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 -- ptnl_ler_for_per_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 table handler use only.
35 --
36 Procedure chk_ptnl_ler_for_per_id(p_ptnl_ler_for_per_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_ptnl_ler_for_per_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := ben_ppl_shd.api_updating
47 (p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_ptnl_ler_for_per_id,hr_api.g_number)
52 <> ben_ppl_shd.g_old_rec.ptnl_ler_for_per_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_ppl_shd.constraint_error('BEN_PTNL_LER_FOR_PER_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_ptnl_ler_for_per_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_ppl_shd.constraint_error('BEN_PTNL_LER_FOR_PER_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_ptnl_ler_for_per_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |--------------------------------< chk_ler_id >----------------------------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table.
83 --
84 -- Pre-Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_ptnl_ler_for_per_id PK
89 -- p_ler_id ID of FK column
90 -- p_effective_date Session Date of record
91 -- p_object_version_number object version number
92 -- p_enrt_perd_id ID of FK column
93 --
94 -- Post Success
95 -- Processing continues
96 --
97 -- Post Failure
98 -- Error raised.
99 --
100 -- Access Status
101 -- Internal table handler use only.
102 --
103 Procedure chk_ler_id (p_ptnl_ler_for_per_id in number,
104 p_ler_id in number,
105 p_enrt_perd_id in number,
106 p_effective_date in date,
107 p_object_version_number in number) is
108 --
109 l_proc varchar2(72) := g_package||'chk_ler_id';
110 l_api_updating boolean;
111 l_dummy varchar2(1);
112 l_typ_cd ben_ler_f.typ_cd%type;
113 --
114 cursor c1 is
115 select a.typ_cd
116 from ben_ler_f a
117 where a.ler_id = p_ler_id
118 and p_effective_date
119 between a.effective_start_date
120 and a.effective_end_date;
121 --
122 Begin
123 --
124 hr_utility.set_location('Entering:'||l_proc,5);
125 --
126 l_api_updating := ben_ppl_shd.api_updating
127 (p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id,
128 p_object_version_number => p_object_version_number);
129 --
130 if (l_api_updating
131 and nvl(p_ler_id,hr_api.g_number)
132 <> nvl(ben_ppl_shd.g_old_rec.ler_id,hr_api.g_number)
133 or not l_api_updating) then
134 --
135 -- check if ler_id value exists in ben_ler_f table
136 --
137 open c1;
138 --
139 fetch c1 into l_typ_cd;
140 if c1%notfound then
141 --
142 close c1;
143 --
144 -- raise error as FK does not relate to PK in ben_ler_f
145 -- table.
146 --
147 ben_ppl_shd.constraint_error('BEN_PTNL_LER_FOR_PER_DT1');
148 --
149 else
150 --
151 if l_typ_cd = 'CHECKLIST' then
152 --
153 fnd_message.set_name('BEN','BEN_94161_CHKLST_IN_PTNL_LE');
154 fnd_message.raise_error;
155 end if ;
156 null;
157 /*
158 -- PB : 5422 :
159 if (l_api_updating
160 and nvl(p_enrt_perd_id,hr_api.g_number)
161 = nvl(ben_ppl_shd.g_old_rec.enrt_perd_id,hr_api.g_number)
162 or (not l_api_updating and p_enrt_perd_id is null )) and
163 (l_typ_cd like 'SCHEDD%' and l_typ_cd <> 'SCHEDDU')
164 then
165 --
166 -- if enrt_perd_id value supplied then life event must
167 -- be of schedule type
168 --
169 fnd_message.set_name('BEN','BEN_91249_ENRT_PERD_ID_NULL');
170 fnd_message.raise_error;
171 --
172 end if;
173 */
174 --
175 end if;
176 --
177 close c1;
178 --
179 end if;
180 --
181 hr_utility.set_location('Leaving:'||l_proc,10);
182 --
183 End chk_ler_id;
184 /*
185 --
186 -- ----------------------------------------------------------------------------
187 -- |---------------------------< chk_unique_ler >---------------------------|
188 -- ----------------------------------------------------------------------------
189 --
190 -- Description
191 -- This procedure checks that for a given person no two records can have
192 -- same occured on date and same ler id and same status code.
193 --
194 -- Pre-Conditions
195 -- None.
196 --
197 -- In Parameters
198 --
199 -- p_business_group_id in number
200 --p_person_id in number
201 --p_ler_id in number
202 --p_lf_evt_ocrd_dt in date
203 --p_ptnl_ler_for_per_stat_cd in char
204 -- Post Success
205 -- Processing continues
206 --
207 -- Post Failure
208 -- Error raised.
209 --
210 -- Access Status
211 procedure chk_unique_ler(p_business_group_id in number
212 ,p_person_id in number
213 ,p_ler_id in number
214 ,p_lf_evt_ocrd_dt in date
215 ,p_ptnl_ler_for_per_stat_cd in varchar2
216 ,p_object_version_number in number
217 ,p_ptnl_ler_for_per_id in number)
218 is
219 l_proc varchar2(72) := g_package||' chk_unique_ler ';
220 l_dummy char(1);
221 l_api_updating boolean;
222 --
223 cursor c1 is
224 select null from ben_ptnl_ler_for_per
225 where person_id = p_person_id
226 and ler_id = p_ler_id
227 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
228 and ptnl_ler_for_per_stat_cd = p_ptnl_ler_for_per_stat_cd
229 and ptnl_ler_for_per_stat_cd not in ('VOIDD', 'BCKDT')
230 and business_group_id = p_business_group_id;
231
232 begin
233 hr_utility.set_location('Entering' || l_proc,5);
234 --
235 l_api_updating := ben_ppl_shd.api_updating
236 (p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id,
237 p_object_version_number => p_object_version_number);
238 --
239 if ((l_api_updating
240 and (nvl(p_ler_id,hr_api.g_number)
241 <> nvl(ben_ppl_shd.g_old_rec.ler_id,hr_api.g_number)
242 or nvl(p_lf_evt_ocrd_dt,hr_api.g_date)
243 <> nvl(ben_ppl_shd.g_old_rec.lf_evt_ocrd_dt,hr_api.g_date)
244 or nvl(p_ptnl_ler_for_per_stat_cd,hr_api.g_varchar2)
245 <> nvl(ben_ppl_shd.g_old_rec.ptnl_ler_for_per_stat_cd,hr_api.g_varchar2)
246 )
247 )
248 or not l_api_updating) then
249 --
250 open c1;
251 fetch c1 into l_dummy;
252 if (c1%found) then
253 fnd_message.set_name('BEN', 'BEN_92495_NOT_UNQ_PER_PTNL_LER');
254 fnd_message.raise_error;
255 end if;
256 --
257 end if;
258 hr_utility.set_location('Leaving' || l_proc,15);
259 close c1;
260
261 end chk_unique_ler;
262 */
263 --
264 -- ----------------------------------------------------------------------------
265 -- |---------------------------< chk_csd_by_ptnl_ler_for_per_id >---------------------------|
266 -- ----------------------------------------------------------------------------
267 --
268 -- Description
269 -- This procedure checks that a referenced foreign key actually exists
270 -- in the referenced table.
271 --
272 -- Pre-Conditions
273 -- None.
274 --
275 -- In Parameters
276 -- p_ptnl_ler_for_per_id PK
277 -- p_csd_by_ptnl_ler_for_per_id ID of FK column
278 -- p_object_version_number object version number
279 --
280 -- Post Success
281 -- Processing continues
282 --
283 -- Post Failure
284 -- Error raised.
285 --
286 -- Access Status
287 -- Internal table handler use only.
288 --
289 Procedure chk_csd_by_ptnl_ler_for_per_id (p_ptnl_ler_for_per_id in number,
290 p_csd_by_ptnl_ler_for_per_id in number,
291 p_object_version_number in number) is
292 --
293 l_proc varchar2(72) := g_package||'chk_csd_by_ptnl_ler_for_per_id';
294 l_api_updating boolean;
295 l_dummy varchar2(1);
296 --
297 cursor c1 is
298 select null
299 from ben_ptnl_ler_for_per a
300 where a.ptnl_ler_for_per_id = p_csd_by_ptnl_ler_for_per_id;
301 --
302 Begin
303 --
304 hr_utility.set_location('Entering:'||l_proc,5);
305 --
306 l_api_updating := ben_ppl_shd.api_updating
307 (p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id,
308 p_object_version_number => p_object_version_number);
309 --
310 if (l_api_updating
311 and nvl(p_csd_by_ptnl_ler_for_per_id,hr_api.g_number)
312 <> nvl(ben_ppl_shd.g_old_rec.csd_by_ptnl_ler_for_per_id,hr_api.g_number)
313 or (not l_api_updating and p_csd_by_ptnl_ler_for_per_id is not null )) then
314 --
315 -- check if csd_by_ptnl_ler_for_per_id value exists in ben_enrt_perd table
316 --
317 open c1;
318 --
319 fetch c1 into l_dummy;
320 if c1%notfound then
321 --
322 close c1;
323 --
324 -- raise error as FK does not relate to PK in ben_enrt_perd
325 -- table.
326 --
327 ben_ppl_shd.constraint_error('BEN_PTNL_LER_FOR_PER_FK2');
328 --
329 end if;
330 --
331 close c1;
332 --
333 end if;
334 --
335 hr_utility.set_location('Leaving:'||l_proc,10);
336 --
337 End chk_csd_by_ptnl_ler_for_per_id;
338 --
339 --
340 -- ----------------------------------------------------------------------------
341 -- |---------------------------< chk_enrt_perd_id >---------------------------|
342 -- ----------------------------------------------------------------------------
343 --
344 -- Description
345 -- This procedure checks that a referenced foreign key actually exists
346 -- in the referenced table.
347 --
348 -- Pre-Conditions
349 -- None.
350 --
351 -- In Parameters
352 -- p_ptnl_ler_for_per_id PK
353 -- p_enrt_perd_id ID of FK column
354 -- p_object_version_number object version number
355 --
356 -- Post Success
357 -- Processing continues
358 --
359 -- Post Failure
360 -- Error raised.
361 --
362 -- Access Status
363 -- Internal table handler use only.
364 --
365 Procedure chk_enrt_perd_id (p_ptnl_ler_for_per_id in number,
366 p_enrt_perd_id in number,
367 p_object_version_number in number) is
368 --
369 l_proc varchar2(72) := g_package||'chk_enrt_perd_id';
370 l_api_updating boolean;
371 l_dummy varchar2(1);
372 --
373 cursor c1 is
374 select null
375 from ben_enrt_perd a
376 where a.enrt_perd_id = p_enrt_perd_id;
377 --
378 Begin
379 --
380 hr_utility.set_location('Entering:'||l_proc,5);
381 --
382 l_api_updating := ben_ppl_shd.api_updating
383 (p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id,
384 p_object_version_number => p_object_version_number);
385 --
386 if (l_api_updating
387 and nvl(p_enrt_perd_id,hr_api.g_number)
388 <> nvl(ben_ppl_shd.g_old_rec.enrt_perd_id,hr_api.g_number)
389 or (not l_api_updating and p_enrt_perd_id is not null )) then
390 --
391 -- check if enrt_perd_id value exists in ben_enrt_perd table
392 --
393 open c1;
394 --
395 fetch c1 into l_dummy;
396 if c1%notfound then
397 --
398 close c1;
399 --
400 -- raise error as FK does not relate to PK in ben_enrt_perd
401 -- table.
402 --
403 ben_ppl_shd.constraint_error('BEN_PTNL_LER_FOR_PER_FK1');
404 --
405 end if;
406 --
407 close c1;
408 --
409 end if;
410 --
411 hr_utility.set_location('Leaving:'||l_proc,10);
412 --
413 End chk_enrt_perd_id;
414 --
415 -- ----------------------------------------------------------------------------
416 -- |---------------------------< chk_person_id >------------------------------|
417 -- ----------------------------------------------------------------------------
418 --
419 -- Description
420 -- This procedure checks that a referenced foreign key actually exists
421 -- in the referenced table.
422 --
423 -- Pre-Conditions
424 -- None.
425 --
426 -- In Parameters
427 -- p_ptnl_ler_for_per_id PK
428 -- p_person_id ID of FK column
429 -- p_effective_date Session Date of record
430 -- p_object_version_number object version number
431 --
432 -- Post Success
433 -- Processing continues
434 --
435 -- Post Failure
436 -- Error raised.
437 --
438 -- Access Status
439 -- Internal table handler use only.
440 --
441 Procedure chk_person_id (p_ptnl_ler_for_per_id in number,
442 p_person_id in number,
443 p_effective_date in date,
444 p_lf_evt_ocrd_dt in date, /* Bug 5672925 */
445 p_ler_id in number, --5747460
446 p_object_version_number in number) is
447 --
448 l_proc varchar2(72) := g_package||'chk_person_id';
449 l_api_updating boolean;
450 l_dummy varchar2(1);
451 l_date date;
452 l_person_id number;
453
454 --5747460
455 cursor c_cobra_evt_flag is
456 select ler.qualg_evt_flag
457 from ben_ler_f ler
458 where ler.ler_id = p_ler_id
459 and p_effective_date between ler.effective_start_date and ler.effective_end_date;
460 l_cobra_evt_flag varchar2(30);
461 --
462 --
463 -- Bug 5672925 : Modified cursor C1 and C2 to check existence of PER_ALL_PEOPLE_F record
464 -- as of LF_EVT_OCRD_DT instead of EFFECTIVE_DATE. The problem is, if a person
465 -- is created on 01-Jan-2002 and the latest start date on Person form is changed
466 -- to 06-Jan-2002, then before we create PPL record, the EFFECTIVE_START_DATE
467 -- of PER_ALL_PEOPLE_F record is already changed to 06-Jan-2002, and hence C1, C2
468 -- would fail on EFFECTIVE_DATE
469 --
470 cursor c1(l_person_id number,
471 l_cobra_flag varchar2) is
472 select null
473 from per_all_people_f a
474 where a.person_id = l_person_id
475 and decode(l_cobra_flag, 'Y', p_effective_date, p_lf_evt_ocrd_dt) /* Bug 5672925 + 5747460*/
476 between a.effective_start_date
477 and a.effective_end_date;
478 -- Added cursor for bug 3652731
479 cursor c2(l_cobra_flag varchar2) is
480 select contact_person_id
481 from per_contact_relationships a
482 where a.person_id = p_person_id
483 and decode(l_cobra_flag, 'Y', p_effective_date, p_lf_evt_ocrd_dt) /* Bug 5672925 + 5747460*/
484 between a.date_start
485 and nvl(a.date_end,to_date('31-12-4712','DD-MM-YYYY'));
486 --
487 cursor c3 is
488 select effective_start_date from per_all_people_f
489 where person_id = p_person_id;
490
491 --
492 Begin
493 --
494 hr_utility.set_location('Entering:'||l_proc,5);
495 /*
496 hr_utility.set_location('p_person_id: '||p_person_id,5);
497 hr_utility.set_location('p_ptnl_ler_for_per_id: '||p_ptnl_ler_for_per_id,5);
498 hr_utility.set_location('p_effective_date: '||p_effective_date,5);
499 hr_utility.set_location('p_object_version_number: '||p_object_version_number,5);
500 */
501 --
502 l_api_updating := ben_ppl_shd.api_updating
503 (p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id,
504 p_object_version_number => p_object_version_number);
505 --
506 if (l_api_updating
507 and nvl(p_person_id,hr_api.g_number)
508 <> nvl(ben_ppl_shd.g_old_rec.person_id,hr_api.g_number)
509 or not l_api_updating) then
510 --
511 -- check if person_id value exists in per_all_people_f table
512 hr_utility.set_location('ace p_lf_evt_ocrd_dt = ' || p_lf_evt_ocrd_dt, 9999);
513 hr_utility.set_location('p_effective_date = ' || p_effective_date, 9999);
514 l_person_id := p_person_id;
515
516 -- 5747460: Get COBRA Qualifying Event flag
517 -- If this flag is 'Y' then person record will be checked against effective date
518 -- and not lf_evt_dt. This is because COBRA events are generally created prior to person record.
519 open c_cobra_evt_flag;
520 fetch c_cobra_evt_flag into l_cobra_evt_flag;
521 close c_cobra_evt_flag;
522 hr_utility.set_location('l_cobra_evt_flag = ' || l_cobra_evt_flag, 9999);
523 --
524 open c1(l_person_id, l_cobra_evt_flag);
525 --
526 fetch c1 into l_dummy;
527 if c1%notfound then
528 --
529 close c1;
530 --
531 -- If the given person_id is not there in per_all_people_f
532 -- in the given date, then it could be a contact id
533 -- check if the id exists in per_contact_relationships table
534 -- cursor c3 is for debug purpose
535 open c3;
536 fetch c3 into l_date;
537 close c3;
538 hr_utility.set_location('ESD of contact at this point : '||l_date,5.5);
539 open c2(l_cobra_evt_flag);
540 --
541 fetch c2 into l_person_id;
542 if c2%notfound then
543 -- raise error as FK does not relate to PK in per_all_people_f
544 -- or per_contact_relationships
545 -- table.
546 --
547 close c2;
548 hr_utility.set_location('p_person_id: '||p_person_id,5.5);
549 ben_ppl_shd.constraint_error('BEN_PTNL_LER_FOR_PER_DT2');
550 --
551 else
552 open c1(l_person_id, l_cobra_evt_flag);
553 fetch c1 into l_dummy;
554 if c1%notfound then
555 close c1;
556 hr_utility.set_location('p_person_id: '||p_person_id,5.5);
557 ben_ppl_shd.constraint_error('BEN_PTNL_LER_FOR_PER_DT2');
558 else
559 close c1;
560 end if;
561 end if; --end c2
562 close c2;
563 else
564 close c1;
565 end if; -- end c1
566 --
567 end if; --end l_api_updating
568 --
569 hr_utility.set_location('Leaving:'||l_proc,10);
570 --
571 End chk_person_id;
572 --
573 -- ----------------------------------------------------------------------------
574 -- |-----------------------< chk_ptnl_ler_for_per_src_cd >--------------------|
575 -- ----------------------------------------------------------------------------
576 --
577 -- Description
578 -- This procedure is used to check that the lookup value is valid.
579 --
580 -- Pre Conditions
581 -- None.
582 --
583 -- In Parameters
584 -- ptnl_ler_for_per_id PK of record being inserted or updated.
585 -- ptnl_ler_for_per_src_cd Value of lookup code.
586 -- effective_date effective date
587 -- object_version_number Object version number of record being
588 -- inserted or updated.
589 --
590 -- Post Success
591 -- Processing continues
592 --
593 -- Post Failure
594 -- Error handled by procedure
595 --
596 -- Access Status
597 -- Internal table handler use only.
598 --
599 Procedure chk_ptnl_ler_for_per_src_cd(p_ptnl_ler_for_per_id in number,
600 p_ptnl_ler_for_per_src_cd in varchar2,
601 p_effective_date in date,
602 p_object_version_number in number) is
603 --
604 l_proc varchar2(72) := g_package||'chk_ptnl_ler_for_per_src_cd';
605 l_api_updating boolean;
606 --
607 Begin
608 --
609 hr_utility.set_location('Entering:'||l_proc, 5);
610 --
611 l_api_updating := ben_ppl_shd.api_updating
612 (p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id,
613 p_object_version_number => p_object_version_number);
614 --
615 if (l_api_updating
616 and p_ptnl_ler_for_per_src_cd
617 <> nvl(ben_ppl_shd.g_old_rec.ptnl_ler_for_per_src_cd,hr_api.g_varchar2)
618 or not l_api_updating)
619 and p_ptnl_ler_for_per_src_cd is not null then
620 --
621 -- check if value of lookup falls within lookup type.
622 --
623 if hr_api.not_exists_in_hr_lookups
624 (p_lookup_type => 'BEN_PTNL_LER_FOR_PER_SRC',
625 p_lookup_code => p_ptnl_ler_for_per_src_cd,
626 p_effective_date => p_effective_date) then
627 --
628 -- raise error as does not exist as lookup
629 --
630 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
631 fnd_message.set_token('FIELD','p_ptnl_ler_for_per_src_cd');
632 fnd_message.set_token('TYPE','BEN_PTNL_LER_FOR_PER_SRC');
633 fnd_message.raise_error;
634 --
635 end if;
636 --
637 end if;
638 --
639 hr_utility.set_location('Leaving:'||l_proc,10);
640 --
641 end chk_ptnl_ler_for_per_src_cd;
642 --
643 -- ----------------------------------------------------------------------------
644 -- |----------------------< chk_ptnl_ler_for_per_stat_cd >--------------------|
645 -- ----------------------------------------------------------------------------
646 --
647 -- Description
648 -- This procedure is used to check that the lookup value is valid.
649 --
650 -- Pre Conditions
651 -- None.
652 --
653 -- In Parameters
654 -- ptnl_ler_for_per_id PK of record being inserted or updated.
655 -- ptnl_ler_for_per_stat_cd Value of lookup code.
656 -- effective_date effective date
657 -- object_version_number Object version number of record being
658 -- inserted or updated.
659 --
660 -- Post Success
661 -- Processing continues
662 --
663 -- Post Failure
664 -- Error handled by procedure
665 --
666 -- Access Status
667 -- Internal table handler use only.
668 --
669 Procedure chk_ptnl_ler_for_per_stat_cd(p_ptnl_ler_for_per_id in number,
670 p_ptnl_ler_for_per_stat_cd in varchar2,
671 p_effective_date in date,
672 p_object_version_number in number) is
673 --
674 l_proc varchar2(72) := g_package||'chk_ptnl_ler_for_per_stat_cd';
675 l_api_updating boolean;
676 --
677 Begin
678 --
679 hr_utility.set_location('Entering:'||l_proc, 5);
680 --
681 l_api_updating := ben_ppl_shd.api_updating
682 (p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id,
683 p_object_version_number => p_object_version_number);
684 --
685 if (l_api_updating
686 and p_ptnl_ler_for_per_stat_cd
687 <> nvl(ben_ppl_shd.g_old_rec.ptnl_ler_for_per_stat_cd,hr_api.g_varchar2)
688 or not l_api_updating) then
689 --
690 -- check if value of lookup falls within lookup type.
691 --
692 if hr_api.not_exists_in_hr_lookups
693 (p_lookup_type => 'BEN_PTNL_LER_FOR_PER_STAT',
694 p_lookup_code => p_ptnl_ler_for_per_stat_cd,
695 p_effective_date => p_effective_date) then
696 --
697 -- raise error as does not exist as lookup
698 --
699 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
700 fnd_message.set_token('FIELD','p_ptnl_ler_for_per_stat_cd');
701 fnd_message.set_token('TYPE','BEN_PTNL_LER_FOR_PER_STAT');
702 fnd_message.raise_error;
703 --
704 end if;
705 --
706 -- Check the PPL status code transitions
707 --
708 if not l_api_updating
709 and p_ptnl_ler_for_per_stat_cd not in ('DTCTD', 'UNPROCD') then
710 --
711 hr_utility.set_location('Creation: '||l_proc, 10);
712 fnd_message.set_name('BEN','BEN_92162_INV_PPL_STCD_TRANS');
713 fnd_message.raise_error;
714 --
715 elsif l_api_updating
716 and ben_ppl_shd.g_old_rec.ptnl_ler_for_per_stat_cd = 'DTCTD'
717 and p_ptnl_ler_for_per_stat_cd not in ('PROCD', 'VOIDD','MNL') then
718 --
719 hr_utility.set_location('DTCTD: '||l_proc, 10);
720 fnd_message.set_name('BEN','BEN_92162_INV_PPL_STCD_TRANS');
721 fnd_message.raise_error;
722 --
723 elsif l_api_updating
724 and ben_ppl_shd.g_old_rec.ptnl_ler_for_per_stat_cd = 'UNPROCD'
725 and p_ptnl_ler_for_per_stat_cd not in ('PROCD', 'VOIDD','MNL') then
726 --
727 hr_utility.set_location('UNPROCD: '||l_proc, 10);
728 fnd_message.set_name('BEN','BEN_92162_INV_PPL_STCD_TRANS');
729 fnd_message.raise_error;
730 --
731 elsif l_api_updating
732 and ben_ppl_shd.g_old_rec.ptnl_ler_for_per_stat_cd = 'VOIDD'
733 and p_ptnl_ler_for_per_stat_cd not in ('UNPROCD','MNL') then
734 --
735 hr_utility.set_location('VOIDD: '||l_proc, 10);
736 fnd_message.set_name('BEN','BEN_92162_INV_PPL_STCD_TRANS');
737 fnd_message.raise_error;
738 --
739 elsif l_api_updating
740 and ben_ppl_shd.g_old_rec.ptnl_ler_for_per_stat_cd = 'PROCD'
741 and p_ptnl_ler_for_per_stat_cd not in ('UNPROCD','MNL','VOIDD') then
742 --
743 hr_utility.set_location('PROCD: '||l_proc, 10);
744 fnd_message.set_name('BEN','BEN_92162_INV_PPL_STCD_TRANS');
745 fnd_message.raise_error;
746 --
747 elsif l_api_updating
748 and ben_ppl_shd.g_old_rec.ptnl_ler_for_per_stat_cd = 'MNL'
749 and p_ptnl_ler_for_per_stat_cd not in ('UNPROCD','VOIDD','MNLO') then
750 --
751 hr_utility.set_location('MNL: '||l_proc, 10);
752 fnd_message.set_name('BEN','BEN_92162_INV_PPL_STCD_TRANS');
753 fnd_message.raise_error;
754 --
755 end if;
756 --
757 end if;
758 --
759 hr_utility.set_location('Leaving:'||l_proc,100);
760 --
761 end chk_ptnl_ler_for_per_stat_cd;
762 --
763 -- ----------------------------------------------------------------------------
764 -- |---------------------------< chk_delete_allowed >-------------------------|
765 -- ----------------------------------------------------------------------------
766 --
767 -- Description
768 -- This procedure is used to check that the record can be deleted if there
769 -- is no corresponding real life event out there for that record.
770 --
771 -- Pre Conditions
772 -- None.
773 --
774 -- In Parameters
775 -- ptnl_ler_for_per_id PK of record being inserted or updated.
776 -- ptnl_ler_for_per_stat_cd Value of lookup code.
777 -- person_id FK of person.
778 -- ler_id FK of ler.
779 -- lf_evt_ocrd_dt Life event occured date.
780 -- effective_date effective date
781 -- object_version_number Object version number of record being
782 -- inserted or updated.
783 --
784 -- Post Success
785 -- Processing continues
786 --
787 -- Post Failure
788 -- Error handled by procedure
789 --
790 -- Access Status
791 -- Internal table handler use only.
792 --
793 function chk_delete_allowed
794 (p_ptnl_ler_for_per_id in number,
795 p_ptnl_ler_for_per_stat_cd in varchar2,
796 p_business_group_id in number,
797 p_person_id in number,
798 p_ler_id in number,
799 p_lf_evt_ocrd_dt in date) return boolean is
800 --
801 cursor c1 is
802 select null
803 from ben_per_in_ler pil
804 where pil.person_id = p_person_id
805 and pil.business_group_id+0 = p_business_group_id
806 and pil.ler_id = p_ler_id
807 and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
808 --
809 l_proc varchar2(72) := g_package||'chk_delete_allowed';
810 l_dummy varchar2(1);
811 l_api_updating boolean;
812 --
813 Begin
814 --
815 hr_utility.set_location('Entering:'||l_proc, 5);
816 --
817 if p_ptnl_ler_for_per_stat_cd = 'PROCD' then
818 --
819 -- Check if we can delete the record.
820 --
821 open c1;
822 --
823 fetch c1 into l_dummy;
824 --
825 if c1%found then
826 --
827 -- per for ler exists and we are trying to delete potential
828 -- bad move so error.
829 --
830 close c1;
831 return false;
832 --
833 end if;
834 --
835 close c1;
836 --
837 end if;
838 --
839 hr_utility.set_location('Leaving:'||l_proc,10);
840 --
841 return true;
842 --
843 end chk_delete_allowed;
844 --
845 -- ----------------------------------------------------------------------------
846 -- |---------------------------< chk_date_validity >--------------------------|
847 -- ----------------------------------------------------------------------------
848 --
849 -- Description
850 -- This procedure is used to check that the date is filled in for the current
851 -- status code that has been passed in.
852 --
853 -- Pre Conditions
854 -- None.
855 --
856 -- In Parameters
857 -- ptnl_ler_for_per_stat_cd Value of lookup code.
858 -- dtctd_dt date
859 -- unprocd_dt date
860 -- procd_dt date
861 -- voidd_dt date
862 -- mnl_dt date
863 -- mnlo_dt date
864 --
865 -- Post Success
866 -- Processing continues
867 --
868 -- Post Failure
869 -- Error handled by procedure
870 --
871 -- Access Status
872 -- Internal table handler use only.
873 --
874 procedure chk_date_validity
875 (p_ptnl_ler_for_per_stat_cd in varchar2,
876 p_dtctd_dt in date,
877 p_unprocd_dt in date,
878 p_procd_dt in date,
879 p_voidd_dt in date,
880 p_mnl_dt in date,
881 p_mnlo_dt in date) is
882 --
883 l_proc varchar2(72) := g_package||'chk_delete_allowed';
884 --
885 Begin
886 --
887 hr_utility.set_location('Entering:'||l_proc, 5);
888 --
889 if p_ptnl_ler_for_per_stat_cd = 'PROCD' and
890 p_procd_dt is null then
891 --
892 fnd_message.set_name('BEN','BEN_92329_PROCD_DATE_NULL');
893 fnd_message.raise_error;
894 --
895 elsif p_ptnl_ler_for_per_stat_cd = 'VOIDD' and
896 p_voidd_dt is null then
897 --
898 fnd_message.set_name('BEN','BEN_92330_VOIDD_DATE_NULL');
899 fnd_message.raise_error;
900 --
901 elsif p_ptnl_ler_for_per_stat_cd = 'UNPROCD' and
902 p_unprocd_dt is null then
903 --
904 fnd_message.set_name('BEN','BEN_92331_UNPROCD_DATE_NULL');
905 fnd_message.raise_error;
906 --
907 elsif p_ptnl_ler_for_per_stat_cd = 'DTCTD' and
908 p_dtctd_dt is null then
909 --
910 fnd_message.set_name('BEN','BEN_92332_DTCTD_DATE_NULL');
911 fnd_message.raise_error;
912 --
913 elsif p_ptnl_ler_for_per_stat_cd = 'MNL' and
914 p_mnl_dt is null then
915 --
916 fnd_message.set_name('BEN','BEN_92333_MNL_DATE_NULL');
917 fnd_message.raise_error;
918 --
919 elsif p_ptnl_ler_for_per_stat_cd = 'MNLO' and
920 p_mnlo_dt is null then
921 --
922 fnd_message.set_name('BEN','BEN_92334_MNLO_DATE_NULL');
923 fnd_message.raise_error;
924 --
925 end if;
926 --
927 hr_utility.set_location('Leaving:'||l_proc,10);
928 --
929 end chk_date_validity;
930 --
931 -- ----------------------------------------------------------------------------
932 -- |---------------------------< insert_validate >----------------------------|
933 -- ----------------------------------------------------------------------------
934 Procedure insert_validate(p_rec in ben_ppl_shd.g_rec_type
935 ,p_effective_date in date) is
936 --
937 l_proc varchar2(72) := g_package||'insert_validate';
938 --
939 Begin
940 hr_utility.set_location('Entering:'||l_proc, 5);
941 --
942 -- Call all supporting business operations
943 --
944 --
945 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
946 --
947 chk_ptnl_ler_for_per_id
948 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
949 p_object_version_number => p_rec.object_version_number);
950 --
951 chk_enrt_perd_id
952 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
953 p_enrt_perd_id => p_rec.enrt_perd_id,
954 p_object_version_number => p_rec.object_version_number);
955 --
956 chk_csd_by_ptnl_ler_for_per_id
957 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
958 p_csd_by_ptnl_ler_for_per_id => p_rec.csd_by_ptnl_ler_for_per_id,
959 p_object_version_number => p_rec.object_version_number);
960 --
961 chk_ler_id
962 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
963 p_ler_id => p_rec.ler_id,
964 p_enrt_perd_id => p_rec.enrt_perd_id,
965 p_effective_date => p_effective_date,
966 p_object_version_number => p_rec.object_version_number);
967 --
968
969 chk_person_id
970 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
971 p_person_id => p_rec.person_id,
972 p_effective_date => p_effective_date,
973 p_lf_evt_ocrd_dt => p_rec.lf_evt_ocrd_dt, /* Bug 5672925 */
974 p_ler_id => p_rec.ler_id, --5747460
975 p_object_version_number => p_rec.object_version_number);
976
977 --
978 chk_ptnl_ler_for_per_stat_cd
979 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
980 p_ptnl_ler_for_per_stat_cd => p_rec.ptnl_ler_for_per_stat_cd,
981 p_effective_date => p_effective_date,
982 p_object_version_number => p_rec.object_version_number);
983 --
984 chk_ptnl_ler_for_per_src_cd
985 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
986 p_ptnl_ler_for_per_src_cd => p_rec.ptnl_ler_for_per_src_cd,
987 p_effective_date => p_effective_date,
988 p_object_version_number => p_rec.object_version_number);
989 --
990 chk_date_validity
991 (p_ptnl_ler_for_per_stat_cd => p_rec.ptnl_ler_for_per_stat_cd,
992 p_dtctd_dt => p_rec.dtctd_dt,
993 p_unprocd_dt => p_rec.unprocd_dt,
994 p_procd_dt => p_rec.procd_dt,
995 p_voidd_dt => p_rec.voidd_dt,
996 p_mnl_dt => p_rec.mnl_dt,
997 p_mnlo_dt => p_rec.mnlo_dt);
998 --
999 /*chk_unique_ler
1000 (p_business_group_id => p_rec.business_group_id,
1001 p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
1002 p_person_id => p_rec.person_id,
1003 p_ler_id => p_rec.ler_id,
1004 p_lf_evt_ocrd_dt => p_rec.lf_evt_ocrd_dt,
1005 p_object_version_number => p_rec.object_version_number,
1006 p_ptnl_ler_for_per_stat_cd => p_rec.ptnl_ler_for_per_stat_cd); */
1007
1008 hr_utility.set_location(' Leaving:'||l_proc, 10);
1009 End insert_validate;
1010 --
1011 -- ----------------------------------------------------------------------------
1012 -- |---------------------------< update_validate >----------------------------|
1013 -- ----------------------------------------------------------------------------
1014 Procedure update_validate(p_rec in ben_ppl_shd.g_rec_type
1015 ,p_effective_date in date) is
1016 --
1017 l_proc varchar2(72) := g_package||'update_validate';
1018 --
1019 Begin
1020 hr_utility.set_location('Entering:'||l_proc, 5);
1021 --
1022 -- Call all supporting business operations
1023 --
1024 --
1025 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1026 --
1027 chk_ptnl_ler_for_per_id
1028 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
1029 p_object_version_number => p_rec.object_version_number);
1030 --
1031 chk_enrt_perd_id
1032 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
1033 p_enrt_perd_id => p_rec.enrt_perd_id,
1034 p_object_version_number => p_rec.object_version_number);
1035 --
1036 chk_csd_by_ptnl_ler_for_per_id
1037 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
1038 p_csd_by_ptnl_ler_for_per_id => p_rec.csd_by_ptnl_ler_for_per_id,
1039 p_object_version_number => p_rec.object_version_number);
1040 --
1041 chk_ler_id
1042 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
1043 p_ler_id => p_rec.ler_id,
1044 p_enrt_perd_id => p_rec.enrt_perd_id,
1045 p_effective_date => p_effective_date,
1046 p_object_version_number => p_rec.object_version_number);
1047 --
1048 chk_person_id
1049 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
1050 p_person_id => p_rec.person_id,
1051 p_effective_date => p_effective_date,
1052 p_lf_evt_ocrd_dt => p_rec.lf_evt_ocrd_dt, /* Bug 5672925 */
1053 p_ler_id => p_rec.ler_id, --5747460
1054 p_object_version_number => p_rec.object_version_number);
1055 --
1056 chk_ptnl_ler_for_per_stat_cd
1057 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
1058 p_ptnl_ler_for_per_stat_cd => p_rec.ptnl_ler_for_per_stat_cd,
1059 p_effective_date => p_effective_date,
1060 p_object_version_number => p_rec.object_version_number);
1061 --
1062 chk_ptnl_ler_for_per_src_cd
1063 (p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
1064 p_ptnl_ler_for_per_src_cd => p_rec.ptnl_ler_for_per_src_cd,
1065 p_effective_date => p_effective_date,
1066 p_object_version_number => p_rec.object_version_number);
1067 --
1068 chk_date_validity
1069 (p_ptnl_ler_for_per_stat_cd => p_rec.ptnl_ler_for_per_stat_cd,
1070 p_dtctd_dt => p_rec.dtctd_dt,
1071 p_unprocd_dt => p_rec.unprocd_dt,
1072 p_procd_dt => p_rec.procd_dt,
1073 p_voidd_dt => p_rec.voidd_dt,
1074 p_mnl_dt => p_rec.mnl_dt,
1075 p_mnlo_dt => p_rec.mnlo_dt);
1076 --
1077 /* chk_unique_ler
1078 (p_business_group_id => p_rec.business_group_id,
1079 p_ptnl_ler_for_per_id => p_rec.ptnl_ler_for_per_id,
1080 p_person_id => p_rec.person_id,
1081 p_ler_id => p_rec.ler_id,
1082 p_lf_evt_ocrd_dt => p_rec.lf_evt_ocrd_dt,
1083 p_object_version_number => p_rec.object_version_number,
1084 p_ptnl_ler_for_per_stat_cd => p_rec.ptnl_ler_for_per_stat_cd); */
1085 --
1086 hr_utility.set_location(' Leaving:'||l_proc, 10);
1087 End update_validate;
1088 --
1089 -- ----------------------------------------------------------------------------
1090 -- |---------------------------< delete_validate >----------------------------|
1091 -- ----------------------------------------------------------------------------
1092 Procedure delete_validate(p_rec in ben_ppl_shd.g_rec_type
1093 ,p_effective_date in date) is
1094 --
1095 l_proc varchar2(72) := g_package||'delete_validate';
1096 --
1097 Begin
1098 hr_utility.set_location('Entering:'||l_proc, 5);
1099 --
1100 -- Call all supporting business operations
1101 --
1102 hr_utility.set_location(' Leaving:'||l_proc, 10);
1103 End delete_validate;
1104 --
1105 --
1106 -- ---------------------------------------------------------------------------
1107 -- |---------------------< return_legislation_code >-------------------------|
1108 -- ---------------------------------------------------------------------------
1109 --
1110 function return_legislation_code
1111 (p_ptnl_ler_for_per_id in number) return varchar2 is
1112 --
1113 -- Declare cursor
1114 --
1115 cursor csr_leg_code is
1116 select a.legislation_code
1117 from per_business_groups a,
1118 ben_ptnl_ler_for_per b
1119 where b.ptnl_ler_for_per_id = p_ptnl_ler_for_per_id
1120 and a.business_group_id = b.business_group_id;
1121 --
1122 -- Declare local variables
1123 --
1124 l_legislation_code varchar2(150);
1125 l_proc varchar2(72) := g_package||'return_legislation_code';
1126 --
1127 begin
1128 --
1129 hr_utility.set_location('Entering:'|| l_proc, 10);
1130 --
1131 -- Ensure that all the mandatory parameter are not null
1132 --
1133 hr_api.mandatory_arg_error(p_api_name => l_proc,
1134 p_argument => 'ptnl_ler_for_per_id',
1135 p_argument_value => p_ptnl_ler_for_per_id);
1136 --
1137 open csr_leg_code;
1138 --
1139 fetch csr_leg_code into l_legislation_code;
1140 --
1141 if csr_leg_code%notfound then
1142 --
1143 close csr_leg_code;
1144 --
1145 -- The primary key is invalid therefore we must error
1146 --
1147 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1148 hr_utility.raise_error;
1149 --
1150 end if;
1151 --
1152 close csr_leg_code;
1153 --
1154 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1155 --
1156 return l_legislation_code;
1157 --
1158 end return_legislation_code;
1159 --
1160 end ben_ppl_bus;