[Home] [Help]
PACKAGE BODY: APPS.BEN_PEL_BUS
Source
1 Package Body ben_pel_bus as
2 /* $Header: bepelrhi.pkb 120.3.12000000.2 2007/05/13 23:02:25 rtagarra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_pel_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_pil_elctbl_chc_popl_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 -- pil_elctbl_chc_popl_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_pil_elctbl_chc_popl_id(p_pil_elctbl_chc_popl_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_pil_elctbl_chc_popl_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_pel_shd.api_updating
47 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_pil_elctbl_chc_popl_id,hr_api.g_number)
52 <> ben_pel_shd.g_old_rec.pil_elctbl_chc_popl_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_pel_shd.constraint_error('BEN_PIL_ELCTBL_CHC_POPL_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_pil_elctbl_chc_popl_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_pel_shd.constraint_error('BEN_PIL_ELCTBL_CHC_POPL_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_pil_elctbl_chc_popl_id;
75
76 --
77 -- ----------------------------------------------------------------------------
78 -- |------< chk_uom >------|
79 -- ----------------------------------------------------------------------------
80 --
81 -- Description
82 -- This procedure is used to check that the lookup value is valid.
83 --
84 -- Pre Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- prtt_enrt_rslt_id PK of record being inserted or updated.
89 -- uom Value of lookup code.
90 -- effective_date effective date
91 -- object_version_number Object version number of record being
92 -- inserted or updated.
93 --
94 -- Post Success
95 -- Processing continues
96 --
97 -- Post Failure
98 -- Error handled by procedure
99 --
100 -- Access Status
101 -- Internal table handler use only.
102 --
103 Procedure chk_uom (p_pil_elctbl_chc_popl_id in number
104 ,p_uom in varchar2
105 ,p_effective_date in date
106 ,p_object_version_number in number) is
107 l_proc varchar2(72) := g_package||'chk_uom';
108 l_api_updating boolean;
109 l_dummy varchar2(1);
110 cursor c1 is
111 select null
112 from fnd_currencies_tl
113 where currency_code = p_uom ;
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc, 5);
117 --
118 l_api_updating := ben_pel_shd.api_updating
119 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and p_uom
124 <> nvl(ben_pel_shd.g_old_rec.uom,hr_api.g_varchar2)
125 or not l_api_updating)
126 and p_uom is not null then
127 --
128 -- check if value of lookup falls within lookup type.
129 --
130 open c1;
131 fetch c1 into l_dummy;
132 if c1%notfound then
133 --
134 -- raise error as does not exist as lookup
135 --
136 close c1;
137 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
138 fnd_message.set_token('FIELD', p_uom );
139 fnd_message.set_token('TYPE','FND_CURRENCY_TBL');
140 fnd_message.raise_error;
141 end if;
142 close c1;
143 --
144 end if;
145 --
146 hr_utility.set_location('Leaving:'||l_proc,10);
147 --
148 end chk_uom ;
149 --
150 -- ----------------------------------------------------------------------------
151 -- |------< chk_per_in_ler_id >------|
152 -- ----------------------------------------------------------------------------
153 --
154 -- Description
155 -- This procedure checks that a referenced foreign key actually exists
156 -- in the referenced table.
157 --
158 -- Pre-Conditions
159 -- None.
160 --
161 -- In Parameters
162 -- p_pil_elctbl_chc_popl_id PK
163 -- p_per_in_ler_id ID of FK column
164 -- p_effective_date Session Date of record
165 -- p_object_version_number object version number
166 --
167 -- Post Success
168 -- Processing continues
169 --
170 -- Post Failure
171 -- Error raised.
172 --
173 -- Access Status
174 -- Internal table handler use only.
175 --
176 Procedure chk_per_in_ler_id (p_pil_elctbl_chc_popl_id in number,
177 p_per_in_ler_id in number,
178 p_effective_date in date,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_per_in_ler_id';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 --
185 cursor c1 is
186 select null
187 from ben_per_in_ler a
188 where a.per_in_ler_id = p_per_in_ler_id
189 and a.per_in_ler_stat_cd = 'STRTD';
190 --
191 Begin
192 --
193 hr_utility.set_location('Entering:'||l_proc,5);
194 --
195 l_api_updating := ben_pel_shd.api_updating
196 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
197 p_object_version_number => p_object_version_number);
198 --
199 if (l_api_updating
200 and nvl(p_per_in_ler_id,hr_api.g_number)
201 <> nvl(ben_pel_shd.g_old_rec.per_in_ler_id,hr_api.g_number)
202 or not l_api_updating) then
203 --
204 -- check if per_in_ler_id value exists in ben_per_in_ler table
205 --
206 open c1;
207 --
208 fetch c1 into l_dummy;
209 if c1%notfound then
210 --
211 close c1;
212 --
213 -- raise error as FK does not relate to PK in ben_per_in_ler
214 -- table.
215 --
216 ben_pel_shd.constraint_error('BEN_PIL_ELCTBL_CHC_POPL_DT2');
217 --
218 end if;
219 --
220 close c1;
221 --
222 end if;
223 --
224 hr_utility.set_location('Leaving:'||l_proc,10);
225 --
226 End chk_per_in_ler_id;
227 --
228 -- ----------------------------------------------------------------------------
229 -- |------< chk_lee_rsn_id >------|
230 -- ----------------------------------------------------------------------------
231 --
232 -- Description
233 -- This procedure checks that a referenced foreign key actually exists
234 -- in the referenced table.
235 --
236 -- Pre-Conditions
237 -- None.
238 --
239 -- In Parameters
240 -- p_pil_elctbl_chc_popl_id PK
241 -- p_lee_rsn_id ID of FK column
242 -- p_effective_date Session Date of record
243 -- p_object_version_number object version number
244 --
245 -- Post Success
246 -- Processing continues
247 --
248 -- Post Failure
249 -- Error raised.
250 --
251 -- Access Status
252 -- Internal table handler use only.
253 --
254 Procedure chk_lee_rsn_id (p_pil_elctbl_chc_popl_id in number,
255 p_lee_rsn_id in number,
256 p_effective_date in date,
257 p_object_version_number in number) is
258 --
259 l_proc varchar2(72) := g_package||'chk_lee_rsn_id';
260 l_api_updating boolean;
261 l_dummy varchar2(1);
262 --
263 cursor c1 is
264 select null
265 from ben_lee_rsn_f a
266 where a.lee_rsn_id = p_lee_rsn_id
267 and p_effective_date
268 between a.effective_start_date
269 and a.effective_end_date;
270 --
271 Begin
272 --
273 hr_utility.set_location('Entering:'||l_proc,5);
274 --
275 l_api_updating := ben_pel_shd.api_updating
276 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
277 p_object_version_number => p_object_version_number);
278 --
279 if (l_api_updating
280 and nvl(p_lee_rsn_id,hr_api.g_number)
281 <> nvl(ben_pel_shd.g_old_rec.lee_rsn_id,hr_api.g_number)
282 or not l_api_updating) then
283 --
284 -- check if lee_rsn_id value exists in ben_lee_rsn_f table
285 --
286 open c1;
287 --
288 fetch c1 into l_dummy;
289 if c1%notfound then
290 --
291 close c1;
292 --
293 -- raise error as FK does not relate to PK in ben_lee_rsn_f
294 -- table.
295 --
296 ben_pel_shd.constraint_error('BEN_PIL_ELCTBL_CHC_POPL_DT1');
297 --
298 end if;
299 --
300 close c1;
301 --
302 end if;
303 --
304 hr_utility.set_location('Leaving:'||l_proc,10);
305 --
306 End chk_lee_rsn_id;
307 --
308 -- ----------------------------------------------------------------------------
309 -- |------< chk_enrt_perd_id >------|
310 -- ----------------------------------------------------------------------------
311 --
312 -- Description
313 -- This procedure checks that a referenced foreign key actually exists
314 -- in the referenced table.
315 --
316 -- Pre-Conditions
317 -- None.
318 --
319 -- In Parameters
320 -- p_pil_elctbl_chc_popl_id PK
321 -- p_enrt_perd_id ID of FK column
322 -- p_object_version_number object version number
323 --
324 -- Post Success
325 -- Processing continues
326 --
327 -- Post Failure
328 -- Error raised.
329 --
330 -- Access Status
331 -- Internal table handler use only.
332 --
333 Procedure chk_enrt_perd_id (p_pil_elctbl_chc_popl_id in number,
334 p_enrt_perd_id in number,
335 p_object_version_number in number) is
336 --
337 l_proc varchar2(72) := g_package||'chk_enrt_perd_id';
338 l_api_updating boolean;
339 l_dummy varchar2(1);
340 --
341 cursor c1 is
342 select null
343 from ben_enrt_perd a
344 where a.enrt_perd_id = p_enrt_perd_id;
345 --
346 Begin
347 --
348 hr_utility.set_location('Entering:'||l_proc,5);
349 --
350 l_api_updating := ben_pel_shd.api_updating
351 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
352 p_object_version_number => p_object_version_number);
353 --
354 if ((l_api_updating
355 and nvl(p_enrt_perd_id,hr_api.g_number)
356 <> nvl(ben_pel_shd.g_old_rec.enrt_perd_id,hr_api.g_number)) --Bug 4068639
357 or not l_api_updating) and
358 p_enrt_perd_id is not null then
359 --
360 -- check if enrt_perd_id value exists in ben_enrt_perd table
361 --
362 open c1;
363 --
364 fetch c1 into l_dummy;
365 if c1%notfound then
366 --
367 close c1;
368 --
369 -- raise error as FK does not relate to PK in ben_enrt_perd
370 -- table.
371 --
372 ben_pel_shd.constraint_error('BEN_PIL_ELCTBL_CHC_POPL_FK1');
373 --
374 end if;
375 --
376 close c1;
377 --
378 end if;
379 --
380 hr_utility.set_location('Leaving:'||l_proc,10);
381 --
382 End chk_enrt_perd_id;
383 --
384 -- ----------------------------------------------------------------------------
385 -- |------< chk_enrt_typ_cycl_cd >------|
386 -- ----------------------------------------------------------------------------
387 --
388 -- Description
389 -- This procedure is used to check that the lookup value is valid.
390 --
391 -- Pre Conditions
392 -- None.
393 --
394 -- In Parameters
395 -- pil_elctbl_chc_popl_id PK of record being inserted or updated.
396 -- enrt_typ_cycl_cd Value of lookup code.
397 -- effective_date effective date
398 -- object_version_number Object version number of record being
399 -- inserted or updated.
400 --
401 -- Post Success
402 -- Processing continues
403 --
404 -- Post Failure
408 -- Internal table handler use only.
405 -- Error handled by procedure
406 --
407 -- Access Status
409 --
410 Procedure chk_enrt_typ_cycl_cd(p_pil_elctbl_chc_popl_id in number,
411 p_enrt_typ_cycl_cd in varchar2,
412 p_effective_date in date,
413 p_object_version_number in number) is
414 --
415 l_proc varchar2(72) := g_package||'chk_enrt_typ_cycl_cd';
416 l_api_updating boolean;
417 --
418 Begin
419 --
420 hr_utility.set_location('Entering:'||l_proc, 5);
421 --
422 l_api_updating := ben_pel_shd.api_updating
423 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
424 p_object_version_number => p_object_version_number);
425 --
426 if (l_api_updating
427 and p_enrt_typ_cycl_cd
428 <> nvl(ben_pel_shd.g_old_rec.enrt_typ_cycl_cd,hr_api.g_varchar2)
429 or not l_api_updating)
430 and p_enrt_typ_cycl_cd is not null then
431 --
432 -- check if value of lookup falls within lookup type.
433 --
434 if hr_api.not_exists_in_hr_lookups
435 (p_lookup_type => 'BEN_ENRT_TYP_CYCL',
436 p_lookup_code => p_enrt_typ_cycl_cd,
437 p_effective_date => p_effective_date) then
438 --
439 -- raise error as does not exist as lookup
440 --
441 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
442 fnd_message.set_token('FIELD', p_enrt_typ_cycl_cd);
443 fnd_message.set_token('TYPE' ,'BEN_ENRT_TYP_CYCL');
444 fnd_message.raise_error;
445 --
446 end if;
447 --
448 end if;
449 --
450 hr_utility.set_location('Leaving:'||l_proc,10);
451 --
452 end chk_enrt_typ_cycl_cd;
453 --
454 -- ----------------------------------------------------------------------------
455 -- |------< chk_ws_stat_cd >------|
456 -- ----------------------------------------------------------------------------
457 --
458 -- Description
459 -- This procedure is used to check that the lookup value is valid.
460 --
461 -- Pre Conditions
462 -- None.
463 --
464 -- In Parameters
465 -- p_pil_elctbl_chc_popl_id PK of record being inserted or updated.
466 -- ws_stat_cd Value of lookup code.
467 -- effective_date effective date
468 -- object_version_number Object version number of record being
469 -- inserted or updated.
470 --
471 -- Post Success
472 -- Processing continues
473 --
474 -- Post Failure
475 -- Error handled by procedure
476 --
477 -- Access Status
478 -- Internal table handler use only.
479 --
480 Procedure chk_ws_stat_cd(p_pil_elctbl_chc_popl_id in number,
481 p_ws_stat_cd in varchar2,
482 p_effective_date in date,
483 p_object_version_number in number) is
484 --
485 l_proc varchar2(72) := g_package||'chk_ws_stat_cd';
486 l_api_updating boolean;
487 --
488 Begin
489 --
490 hr_utility.set_location('Entering:'||l_proc, 5);
491 --
492 l_api_updating := ben_pel_shd.api_updating
493 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
494 p_object_version_number => p_object_version_number);
495 --
496 if (l_api_updating
497 and p_ws_stat_cd
498 <> nvl(ben_pel_shd.g_old_rec.ws_stat_cd,hr_api.g_varchar2)
499 or not l_api_updating)
500 and p_ws_stat_cd is not null then
501 --
502 -- check if value of lookup falls within lookup type.
503 --
504 if hr_api.not_exists_in_hr_lookups
505 (p_lookup_type => 'BEN_WS_STAT',
506 p_lookup_code => p_ws_stat_cd,
507 p_effective_date => p_effective_date) then
508 --
509 -- raise error as does not exist as lookup
510 --
511 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
512 fnd_message.set_token('FIELD', p_ws_stat_cd);
513 fnd_message.set_token('TYPE' ,'BEN_WS_STAT');
514 fnd_message.raise_error;
515 --
516 end if;
517 --
518 end if;
519 --
520 hr_utility.set_location('Leaving:'||l_proc,10);
521 --
522 end chk_ws_stat_cd;
523 --
524 -- ----------------------------------------------------------------------------
525 -- |------< chk_ws_acc_cd >------|
526 -- ----------------------------------------------------------------------------
527 --
528 -- Description
529 -- This procedure is used to check that the lookup value is valid.
530 --
531 -- Pre Conditions
532 -- None.
533 --
534 -- In Parameters
535 -- p_pil_elctbl_chc_popl_id PK of record being inserted or updated.
536 -- ws_acc_cd Value of lookup code.
537 -- effective_date effective date
538 -- object_version_number Object version number of record being
539 -- inserted or updated.
540 --
541 -- Post Success
542 -- Processing continues
543 --
544 -- Post Failure
545 -- Error handled by procedure
546 --
547 -- Access Status
551 p_ws_acc_cd in varchar2,
548 -- Internal table handler use only.
549 --
550 Procedure chk_ws_acc_cd(p_pil_elctbl_chc_popl_id in number,
552 p_effective_date in date,
553 p_object_version_number in number) is
554 --
555 l_proc varchar2(72) := g_package||'chk_ws_acc_cd';
556 l_api_updating boolean;
557 --
558 Begin
559 --
560 hr_utility.set_location('Entering:'||l_proc, 5);
561 --
562 l_api_updating := ben_pel_shd.api_updating
563 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
564 p_object_version_number => p_object_version_number);
565 --
566 if (l_api_updating
567 and p_ws_acc_cd
568 <> nvl(ben_pel_shd.g_old_rec.ws_acc_cd,hr_api.g_varchar2)
569 or not l_api_updating)
570 and p_ws_acc_cd is not null then
571 --
572 -- check if value of lookup falls within lookup type.
573 --
574 if hr_api.not_exists_in_hr_lookups
575 (p_lookup_type => 'BEN_WS_ACC',
576 p_lookup_code => p_ws_acc_cd,
577 p_effective_date => p_effective_date) then
578 --
579 -- raise error as does not exist as lookup
580 --
581 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
582 fnd_message.set_token('FIELD', p_WS_ACC_CD);
583 fnd_message.set_token('TYPE' ,'BEN_ws_acc');
584 fnd_message.raise_error;
585 --
586 end if;
587 --
588 end if;
589 --
590 hr_utility.set_location('Leaving:'||l_proc,10);
591 --
592 end chk_ws_acc_cd;
593 --
594 -- ----------------------------------------------------------------------------
595 -- |------< chk_bdgt_stat_cd >------|
596 -- ----------------------------------------------------------------------------
597 --
598 -- Description
599 -- This procedure is used to check that the lookup value is valid.
600 --
601 -- Pre Conditions
602 -- None.
603 --
604 -- In Parameters
605 -- p_pil_elctbl_chc_popl_id PK of record being inserted or updated.
606 -- bdgt_stat_cd Value of lookup code.
607 -- effective_date effective date
608 -- object_version_number Object version number of record being
609 -- inserted or updated.
610 --
611 -- Post Success
612 -- Processing continues
613 --
614 -- Post Failure
615 -- Error handled by procedure
616 --
617 -- Access Status
618 -- Internal table handler use only.
619 --
620 Procedure chk_bdgt_stat_cd(p_pil_elctbl_chc_popl_id in number,
621 p_bdgt_stat_cd in varchar2,
622 p_effective_date in date,
623 p_object_version_number in number) is
624 --
625 l_proc varchar2(72) := g_package||'chk_bdgt_stat_cd';
626 l_api_updating boolean;
627 --
628 Begin
629 --
630 hr_utility.set_location('Entering:'||l_proc, 5);
631 --
632 l_api_updating := ben_pel_shd.api_updating
633 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
634 p_object_version_number => p_object_version_number);
635 --
636 if (l_api_updating
637 and p_bdgt_stat_cd
638 <> nvl(ben_pel_shd.g_old_rec.bdgt_stat_cd,hr_api.g_varchar2)
639 or not l_api_updating)
640 and p_bdgt_stat_cd is not null then
641 --
642 -- check if value of lookup falls within lookup type.
643 --
644 if hr_api.not_exists_in_hr_lookups
645 (p_lookup_type => 'BEN_BDGT_STAT',
646 p_lookup_code => p_bdgt_stat_cd,
647 p_effective_date => p_effective_date) then
648 --
649 -- raise error as does not exist as lookup
650 --
651 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
652 fnd_message.set_token('FIELD', p_bdgt_stat_cd);
653 fnd_message.set_token('TYPE' ,'BEN_BDGT_STAT');
654 fnd_message.raise_error;
655 --
656 end if;
657 --
658 end if;
659 --
660 hr_utility.set_location('Leaving:'||l_proc,10);
661 --
662 end chk_bdgt_stat_cd;
663 --
664 -- ----------------------------------------------------------------------------
665 -- |------< chk_pop_cd >------|
666 -- ----------------------------------------------------------------------------
667 --
668 -- Description
669 -- This procedure is used to check that the lookup value is valid.
670 --
671 -- Pre Conditions
672 -- None.
673 --
674 -- In Parameters
675 -- pil_elctbl_chc_popl_id PK of record being inserted or updated.
676 -- pop_cd Value of lookup code.
677 -- effective_date effective date
678 -- object_version_number Object version number of record being
679 -- inserted or updated.
680 --
681 -- Post Success
682 -- Processing continues
683 --
684 -- Post Failure
685 -- Error handled by procedure
686 --
687 -- Access Status
688 -- Internal table handler use only.
689 --
690 Procedure chk_pop_cd(p_pil_elctbl_chc_popl_id in number,
691 p_pop_cd in varchar2,
695 l_proc varchar2(72) := g_package||'chk_pop_cd';
692 p_effective_date in date,
693 p_object_version_number in number) is
694 --
696 l_api_updating boolean;
697 --
698 Begin
699 --
700 hr_utility.set_location('Entering:'||l_proc, 5);
701 --
702 l_api_updating := ben_pel_shd.api_updating
703 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
704 p_object_version_number => p_object_version_number);
705 --
706 if (l_api_updating
707 and p_pop_cd
708 <> nvl(ben_pel_shd.g_old_rec.pop_cd,hr_api.g_varchar2)
709 or not l_api_updating)
710 and p_pop_cd is not null then
711 --
712 -- check if value of lookup falls within lookup type.
713 --
714 if hr_api.not_exists_in_hr_lookups
715 (p_lookup_type => 'BEN_POP',
716 p_lookup_code => p_pop_cd,
717 p_effective_date => p_effective_date) then
718 --
719 -- raise error as does not exist as lookup
720 --
721 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
722 fnd_message.set_token('FIELD', p_pop_cd);
723 fnd_message.set_token('TYPE' ,'BEN_POP');
724 fnd_message.raise_error;
725 --
726 end if;
727 --
728 end if;
729 --
730 hr_utility.set_location('Leaving:'||l_proc,10);
731 --
732 end chk_pop_cd;
733 --
734 -- ----------------------------------------------------------------------------
735 -- |------< chk_bdgt_acc_cd >------|
736 -- ----------------------------------------------------------------------------
737 --
738 -- Description
739 -- This procedure is used to check that the lookup value is valid.
740 --
741 -- Pre Conditions
742 -- None.
743 --
744 -- In Parameters
745 -- pil_elctbl_chc_popl_id PK of record being inserted or updated.
746 -- bdgt_acc_cd Value of lookup code.
747 -- effective_date effective date
748 -- object_version_number Object version number of record being
749 -- inserted or updated.
750 --
751 -- Post Success
752 -- Processing continues
753 --
754 -- Post Failure
755 -- Error handled by procedure
756 --
757 -- Access Status
758 -- Internal table handler use only.
759 --
760 Procedure chk_bdgt_acc_cd(p_pil_elctbl_chc_popl_id in number,
761 p_bdgt_acc_cd in varchar2,
762 p_effective_date in date,
763 p_object_version_number in number) is
764 --
765 l_proc varchar2(72) := g_package||'chk_bdgt_acc_cd';
766 l_api_updating boolean;
767 --
768 Begin
769 --
770 hr_utility.set_location('Entering:'||l_proc, 5);
771 --
772 l_api_updating := ben_pel_shd.api_updating
773 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
774 p_object_version_number => p_object_version_number);
775 --
776 if (l_api_updating
777 and p_bdgt_acc_cd
778 <> nvl(ben_pel_shd.g_old_rec.bdgt_acc_cd,hr_api.g_varchar2)
779 or not l_api_updating)
780 and p_bdgt_acc_cd is not null then
781 --
782 -- check if value of lookup falls within lookup type.
783 --
784 if hr_api.not_exists_in_hr_lookups
785 (p_lookup_type => 'BEN_BDGT_ACC',
786 p_lookup_code => p_bdgt_acc_cd,
787 p_effective_date => p_effective_date) then
788 --
789 -- raise error as does not exist as lookup
790 --
791 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
792 fnd_message.set_token('FIELD', p_bdgt_acc_cd);
793 fnd_message.set_token('TYPE' ,'BEN_BDGT_ACC');
794 fnd_message.raise_error;
795 --
796 end if;
797 --
798 end if;
799 --
800 hr_utility.set_location('Leaving:'||l_proc,10);
801 --
802 end chk_bdgt_acc_cd;
803 --
804 -- ----------------------------------------------------------------------------
805 -- |------< chk_cls_enrt_dt_to_use_cd >------|
806 -- ----------------------------------------------------------------------------
807 --
808 -- Description
809 -- This procedure is used to check that the lookup value is valid.
810 --
811 -- Pre Conditions
812 -- None.
813 --
814 -- In Parameters
815 -- pil_elctbl_chc_popl_id PK of record being inserted or updated.
816 -- cls_enrt_dt_to_use_cd Value of lookup code.
817 -- effective_date effective date
818 -- object_version_number Object version number of record being
819 -- inserted or updated.
820 --
821 -- Post Success
822 -- Processing continues
823 --
824 -- Post Failure
825 -- Error handled by procedure
826 --
827 -- Access Status
828 -- Internal table handler use only.
829 --
830 Procedure chk_cls_enrt_dt_to_use_cd(p_pil_elctbl_chc_popl_id in number,
831 p_cls_enrt_dt_to_use_cd in varchar2,
832 p_effective_date in date,
833 p_object_version_number in number) is
834 --
838 Begin
835 l_proc varchar2(72) := g_package||'chk_cls_enrt_dt_to_use_cd';
836 l_api_updating boolean;
837 --
839 --
840 hr_utility.set_location('Entering:'||l_proc, 5);
841 --
842 l_api_updating := ben_pel_shd.api_updating
843 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
844 p_object_version_number => p_object_version_number);
845 --
846 if (l_api_updating
847 and p_cls_enrt_dt_to_use_cd
848 <> nvl(ben_pel_shd.g_old_rec.cls_enrt_dt_to_use_cd,hr_api.g_varchar2)
849 or not l_api_updating)
850 and p_cls_enrt_dt_to_use_cd is not null then
851 --
852 -- check if value of lookup falls within lookup type.
853 --
854 if hr_api.not_exists_in_hr_lookups
855 (p_lookup_type => 'BEN_CLS_ENRT_DT_TO_USE',
856 p_lookup_code => p_cls_enrt_dt_to_use_cd,
857 p_effective_date => p_effective_date) then
858 --
859 -- raise error as does not exist as lookup
860 --
861 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
862 fnd_message.set_token('FIELD', p_cls_enrt_dt_to_use_cd);
863 fnd_message.set_token('TYPE' ,'BEN_CLS_ENRT_DT_TO_USE');
864 fnd_message.raise_error;
865 --
866 end if;
867 --
868 end if;
869 --
870 hr_utility.set_location('Leaving:'||l_proc,10);
871 --
872 end chk_cls_enrt_dt_to_use_cd;
873 --
874 -- ----------------------------------------------------------------------------
875 -- |------< chk_acty_ref_perd_cd >------|
876 -- ----------------------------------------------------------------------------
877 --
878 -- Description
879 -- This procedure is used to check that the lookup value is valid.
880 --
881 -- Pre Conditions
882 -- None.
883 --
884 -- In Parameters
885 -- pil_elctbl_chc_popl_id PK of record being inserted or updated.
886 -- acty_ref_perd_cd Value of lookup code.
887 -- effective_date effective date
888 -- object_version_number Object version number of record being
889 -- inserted or updated.
890 --
891 -- Post Success
892 -- Processing continues
893 --
894 -- Post Failure
895 -- Error handled by procedure
896 --
897 -- Access Status
898 -- Internal table handler use only.
899 --
900 Procedure chk_acty_ref_perd_cd(p_pil_elctbl_chc_popl_id in number,
901 p_acty_ref_perd_cd in varchar2,
902 p_effective_date in date,
903 p_object_version_number in number) is
904 --
905 l_proc varchar2(72) := g_package||'chk_acty_ref_perd_cd';
906 l_api_updating boolean;
907 --
908 Begin
909 --
910 hr_utility.set_location('Entering:'||l_proc, 5);
911 --
912 l_api_updating := ben_pel_shd.api_updating
913 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
914 p_object_version_number => p_object_version_number);
915 --
916 if (l_api_updating
917 and p_acty_ref_perd_cd
918 <> nvl(ben_pel_shd.g_old_rec.acty_ref_perd_cd,hr_api.g_varchar2)
919 or not l_api_updating)
920 and p_acty_ref_perd_cd is not null then
921 --
922 -- check if value of lookup falls within lookup type.
923 --
924 if hr_api.not_exists_in_hr_lookups
925 (p_lookup_type => 'BEN_ACTY_REF_PERD',
926 p_lookup_code => p_acty_ref_perd_cd,
927 p_effective_date => p_effective_date) then
928 --
929 -- raise error as does not exist as lookup
930 --
931 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
932 fnd_message.set_token('FIELD', p_acty_ref_perd_cd);
933 fnd_message.set_token('TYPE' ,'BEN_ACTY_REF_PERD');
934 fnd_message.raise_error;
935 --
936 end if;
937 --
938 end if;
939 --
940 hr_utility.set_location('Leaving:'||l_proc,10);
941 --
942 end chk_acty_ref_perd_cd;
943 --
944 -- ----------------------------------------------------------------------------
945 -- |------< chk_pil_elctbl_popl_stat_cd >------|
946 -- ----------------------------------------------------------------------------
947 --
948 -- Description
949 -- This procedure is used to check that the lookup value is valid.
950 --
951 -- Pre Conditions
952 -- None.
953 --
954 -- In Parameters
955 -- pil_elctbl_chc_popl_id PK of record being inserted or updated.
956 -- pil_elctbl_popl_stat_cd Value of lookup code.
957 -- effective_date effective date
958 -- object_version_number Object version number of record being
959 -- inserted or updated.
960 --
961 -- Post Success
962 -- Processing continues
963 --
964 -- Post Failure
965 -- Error handled by procedure
966 --
967 -- Access Status
968 -- Internal table handler use only.
969 --
970 Procedure chk_pil_elctbl_popl_stat_cd(p_pil_elctbl_chc_popl_id in number,
971 p_pil_elctbl_popl_stat_cd in varchar2,
972 p_effective_date in date,
973 p_object_version_number in number) is
974 --
978 Begin
975 l_proc varchar2(72) := g_package||'chk_pil_elctbl_popl_stat_cd';
976 l_api_updating boolean;
977 --
979 --
980 hr_utility.set_location('Entering:'||l_proc, 5);
981 --
982 l_api_updating := ben_pel_shd.api_updating
983 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
984 p_object_version_number => p_object_version_number);
985 --
986 if (l_api_updating
987 and p_pil_elctbl_popl_stat_cd
988 <> nvl(ben_pel_shd.g_old_rec.pil_elctbl_popl_stat_cd,hr_api.g_varchar2)
989 or not l_api_updating)
990 and p_pil_elctbl_popl_stat_cd is not null then
991 --
992 -- check if value of lookup falls within lookup type.
993 --
994 if hr_api.not_exists_in_hr_lookups
995 (p_lookup_type => 'BEN_PER_IN_LER_STAT',
996 p_lookup_code => p_pil_elctbl_popl_stat_cd,
997 p_effective_date => p_effective_date) then
998 --
999 -- raise error as does not exist as lookup
1000 --
1001 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1002 fnd_message.set_token('FIELD', p_pil_elctbl_popl_stat_cd);
1003 fnd_message.set_token('TYPE' ,'BEN_PER_IN_LER_STAT');
1004 fnd_message.raise_error;
1005 --
1006 end if;
1007 --
1008 end if;
1009 --
1010 hr_utility.set_location('Leaving:'||l_proc,10);
1011 --
1012 end chk_pil_elctbl_popl_stat_cd;
1013 --
1014 --
1015 Procedure chk_reinstate_cd(p_pil_elctbl_chc_popl_id in number,
1016 p_reinstate_cd in varchar2,
1017 p_effective_date in date,
1018 p_object_version_number in number) is
1019 --
1020 l_proc varchar2(72) := g_package||'reinstate_cd';
1021 l_api_updating boolean;
1022 --
1023 Begin
1024 --
1025 hr_utility.set_location('Entering:'||l_proc, 5);
1026 --
1027 l_api_updating := ben_pel_shd.api_updating
1028 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
1029 p_object_version_number => p_object_version_number);
1030 --
1031 if (l_api_updating
1032 and p_reinstate_cd
1033 <> nvl(ben_pel_shd.g_old_rec.reinstate_cd,hr_api.g_varchar2)
1034 or not l_api_updating)
1035 and p_reinstate_cd is not null then
1036 --
1037 -- check if value of lookup falls within lookup type.
1038 --
1039 if hr_api.not_exists_in_hr_lookups
1040 (p_lookup_type => 'BEN_REINSTATE',
1041 p_lookup_code => p_reinstate_cd,
1042 p_effective_date => p_effective_date) then
1043 --
1044 -- raise error as does not exist as lookup
1045 --
1046 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1047 fnd_message.set_token('FIELD', P_REINSTATE_CD);
1048 fnd_message.set_token('TYPE' ,'BEN_REINSTATE');
1049 fnd_message.raise_error;
1050 --
1051 end if;
1052 --
1053 end if;
1054 --
1055 hr_utility.set_location('Leaving:'||l_proc,10);
1056 --
1057 end chk_reinstate_cd;
1058 --
1059 --
1060 Procedure chk_reinstate_ovrdn_cd(p_pil_elctbl_chc_popl_id in number,
1061 p_reinstate_ovrdn_cd in varchar2,
1062 p_effective_date in date,
1063 p_object_version_number in number) is
1064 --
1065 l_proc varchar2(72) := g_package||'reinstate_ovrdn_cd';
1066 l_api_updating boolean;
1067 --
1068 Begin
1069 --
1070 hr_utility.set_location('Entering:'||l_proc, 5);
1071 --
1072 l_api_updating := ben_pel_shd.api_updating
1073 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
1074 p_object_version_number => p_object_version_number);
1075 --
1076 if (l_api_updating
1077 and p_reinstate_ovrdn_cd
1078 <> nvl(ben_pel_shd.g_old_rec.reinstate_ovrdn_cd,hr_api.g_varchar2)
1079 or not l_api_updating)
1080 and p_reinstate_ovrdn_cd is not null then
1081 --
1082 -- check if value of lookup falls within lookup type.
1083 --
1084 if hr_api.not_exists_in_hr_lookups
1085 (p_lookup_type => 'BEN_REINSTATE_OVRDN',
1086 p_lookup_code => p_reinstate_ovrdn_cd,
1087 p_effective_date => p_effective_date) then
1088 --
1089 -- raise error as does not exist as lookup
1090 --
1091 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1092 fnd_message.set_token('FIELD', P_REINSTATE_OVRDN_CD);
1093 fnd_message.set_token('TYPE' ,'BEN_REINSTATE_OVRDN');
1094 fnd_message.raise_error;
1095 --
1096 end if;
1097 --
1098 end if;
1099 --
1100 hr_utility.set_location('Leaving:'||l_proc,10);
1101 --
1102 end chk_reinstate_ovrdn_cd;
1103 --
1104 -- ----------------------------------------------------------------------------
1105 -- |------< chk_Enrt_Lee_Id >------|
1106 -- ----------------------------------------------------------------------------
1107 --
1108 -- Description
1109 -- This procedure is used to check mutual exclusive relationship between
1110 -- Enrt_Perd and Lee_Rsn.
1111 --
1112 Procedure chk_Enrt_Lee_id(p_enrt_perd_id in number
1116 --
1113 ,p_lee_rsn_id in number
1114 ,p_ENRT_TYP_CYCL_CD in varchar2
1115 ) is
1117 l_proc varchar2(72) := g_package||'chk_Enrt_Lee_ID';
1118 --
1119 Begin
1120 hr_utility.set_location('Entering:'||l_proc, 5);
1121 If ( nvl(p_Enrt_Typ_Cycl_cd,'X') = 'U') then
1122 NULL;
1123 Else
1124 if (p_enrt_perd_id is null and p_lee_rsn_id is null) then
1125 fnd_message.set_name('BEN','BEN_91850_PEL_ENRT_PERD_LER_ID');
1126 fnd_message.raise_error;
1127 elsif (p_enrt_perd_id is not null and p_lee_rsn_id is not null) then
1128 fnd_message.set_name('BEN','BEN_91850_PEL_ENRT_PERD_LER_ID');
1129 fnd_message.raise_error;
1130 end if;
1131 End if;
1132 --
1133 hr_utility.set_location('Leaving:'||l_proc,10);
1134 --
1135 end chk_Enrt_Lee_id;
1136 --
1137 -- ----------------------------------------------------------------------------
1138 -- |------< chk_dflt_enrt_perd_end >------|
1139 -- ----------------------------------------------------------------------------
1140 --
1141 -- Description
1142 -- This procedure is used to check that when the user tries to override
1143 -- Enrt_Perd_end_DT or dflt_enrt_dt that the date cannot be set before the
1144 -- system date and stat_cd can only be 'STRTD'.
1145 -- Additionally if defaults were already applied (dflt_asnd_dt
1146 -- has a value) don't allow update of the dflt_enrt_dt.
1147 --
1148 Procedure chk_dflt_enrt_perd_end(p_pil_elctbl_chc_popl_id in number
1149 ,p_enrt_perd_strt_dt in date
1150 ,p_enrt_perd_end_dt in date
1151 ,p_dflt_enrt_dt in date
1152 ,p_dflt_asnd_dt in date
1153 ,p_procg_end_dt in date
1154 ,p_pil_elctbl_popl_stat_cd in varchar2
1155 ,p_object_version_number in number
1156 ) is
1157 --
1158 l_proc varchar2(72) := g_package||'chk_dflt_enrt_perd_end';
1159 l_system_date DATE;
1160 l_api_updating boolean;
1161 --
1162 /* -- Bug 3653034
1163 cursor get_system_date IS
1164 select sysdate
1165 from dual;
1166 */
1167 --
1168 Begin
1169 hr_utility.set_location('Entering:'||l_proc, 5);
1170 /* -- No need to check with system date
1171 open get_system_date;
1172 fetch get_system_date into l_system_date;
1173 close get_system_date;
1174 */ -- Bug 3653034
1175 --
1176 l_api_updating := ben_pel_shd.api_updating
1177 (p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id,
1178 p_object_version_number => p_object_version_number);
1179 --
1180 -- hr_utility.set_location('Me here :'||l_proc, 8);
1181 if (l_api_updating
1182 and p_enrt_perd_end_dt
1183 <> nvl(ben_pel_shd.g_old_rec.enrt_perd_end_dt,hr_api.g_date)) then
1184 If p_pil_elctbl_popl_stat_cd = 'STRTD' then
1185 /*
1186 if (trunc(p_enrt_perd_end_dt) < trunc(l_system_date)) then
1187 fnd_message.set_name('BEN','BEN_92298_ENRT_PEREND_LT_SYSD');
1188 fnd_message.raise_error;
1189 end if;
1190 */ -- Bug 3653034
1191 null;
1192 else
1193 fnd_message.set_name('BEN','BEN_92297_STAT_NOT_STRTD');
1194 fnd_message.raise_error;
1195 end if;
1196 End if;
1197
1198 -- hr_utility.set_location('Me here :'||l_proc, 10);
1199 --
1200 if (l_api_updating
1201 and p_dflt_enrt_dt
1202 <> nvl(ben_pel_shd.g_old_rec.dflt_enrt_dt,hr_api.g_date)) then
1203 If p_pil_elctbl_popl_stat_cd = 'STRTD' then
1204 If p_dflt_asnd_dt is not null then
1205 fnd_message.set_name('BEN','BEN_92295_DFLT_ENRT_W_ASND');
1206 fnd_message.raise_error;
1207 /*
1208 elsif (trunc(p_dflt_enrt_dt) < trunc(l_system_date)) then
1209 fnd_message.set_name('BEN','BEN_92296_DFLT_ENRT_LT_SYSD');
1210 fnd_message.raise_error;
1211 */ -- Bug 3653034
1212 end if;
1213 else
1214 fnd_message.set_name('BEN','BEN_92297_STAT_NOT_STRTD');
1215 fnd_message.raise_error;
1216 end if;
1217 End if;
1218 --
1219 -- hr_utility.set_location('Me here :'||l_proc, 12);
1220
1221 -- Added the following two validations for bug 3653034
1222 /* default enrollment date can be after processing end date per plan design
1223 If p_dflt_enrt_dt > p_procg_end_dt then
1224 fnd_message.set_name('BEN','BEN_94015_DFLT_ENRT_PROC_END');
1225 fnd_message.raise_error;
1226 End If;
1227 */
1228
1229 If p_enrt_perd_end_dt > p_procg_end_dt then
1230 fnd_message.set_name('BEN','BEN_94014_PROC_END_ENRT_END');
1231 fnd_message.raise_error;
1232 End If;
1233
1234 If p_enrt_perd_end_dt < p_enrt_perd_strt_dt then
1235 fnd_message.set_name('BEN','BEN_PDW_93536_SCH_ENRDT_CMP');
1236 fnd_message.raise_error;
1237 End If;
1238
1239 hr_utility.set_location('Leaving:'||l_proc,10);
1240 --
1241 end chk_dflt_enrt_perd_end;
1242 --
1243 -- ----------------------------------------------------------------------------
1247 -- cannot be re-assigned as the manager to A.
1244 -- |------------------------------< chk_ws_mgr_id >---------------------------|
1245 -- ----------------------------------------------------------------------------
1246 -- Validates ws_mgr_id. Any person B, below in the hierarchy to person A,
1248 --
1249 procedure chk_ws_mgr_id(
1250 p_pil_elctbl_chc_popl_id number,
1251 p_ws_mgr_id number,
1252 p_effective_date date) is
1253
1254 cursor c1 is
1255 select per1.full_name person1,
1256 per2.full_name person2
1257 from ben_cwb_hrchy cwb1,
1258 ben_pil_elctbl_chc_popl pel1,
1259 ben_per_in_ler pil1,
1260 per_all_people_f per1,
1261 per_all_people_f per2
1262 where cwb1.mgr_pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
1263 and cwb1.lvl_num > 0
1264 and pel1.pil_elctbl_chc_popl_id = cwb1.mgr_pil_elctbl_chc_popl_id
1265 and pil1.per_in_ler_id = pel1.per_in_ler_id
1266 and per1.person_id = pil1.person_id
1267 and trunc(p_effective_date) between per1.effective_start_date
1268 and per1.effective_end_date
1269 and per2.person_id = p_ws_mgr_id
1270 and trunc(p_effective_date) between per2.effective_start_date
1271 and per2.effective_end_date
1272 and exists
1273 ( select 'x'
1274 from ben_pil_elctbl_chc_popl pel2,
1275 ben_per_in_ler pil2
1276 where pil2.person_id = p_ws_mgr_id
1277 and pil2.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt
1278 and pil2.ler_id = pil1.ler_id
1279 and pel2.per_in_ler_id = pil2.per_in_ler_id
1280 and pel2.pl_id = pel1.pl_id
1281 and pel2.pil_elctbl_chc_popl_id = cwb1.emp_pil_elctbl_chc_popl_id);
1282
1283 l_person1 per_all_people_f.full_name%type;
1284 l_person2 per_all_people_f.full_name%type;
1285 l_proc varchar2(72) := g_package||'chk_ws_mgr_id';
1286
1287 begin
1288
1289 hr_utility.set_location(' Entering:'||l_proc, 10);
1290
1291 if (p_ws_mgr_id
1292 <> nvl(ben_pel_shd.g_old_rec.ws_mgr_id,hr_api.g_number)) then
1293
1294 open c1;
1295 fetch c1 into l_person1,l_person2;
1296 if c1%found then
1297 close c1;
1298 fnd_message.set_name('BEN','BEN_93251_CWB_CANNOT_REASSIGN');
1299 fnd_message.set_token('PERSON1', l_person1);
1300 fnd_message.set_token('PERSON2', l_person2);
1301 fnd_message.raise_error;
1302 end if;
1303 close c1;
1304
1305 end if;
1306
1307 hr_utility.set_location(' Leaving:'||l_proc, 20);
1308 end chk_ws_mgr_id;
1309 -- ----------------------------------------------------------------------------
1310 -- |---------------------------< insert_validate >----------------------------|
1311 -- ----------------------------------------------------------------------------
1312 Procedure insert_validate(p_rec in ben_pel_shd.g_rec_type
1313 ,p_effective_date in date) is
1314 --
1315 l_proc varchar2(72) := g_package||'insert_validate';
1316 --
1317 Begin
1318 hr_utility.set_location('Entering:'||l_proc, 5);
1319 --
1320 -- Call context sensitive validate bgp cache routine
1321 --
1322 ben_batch_dt_api.batch_validate_bgp_id
1323 (p_business_group_id => p_rec.business_group_id
1324 );
1325 --
1326 /*
1327 --
1328 -- Call all supporting business operations
1329 --
1330 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1331 */
1332 --
1333 chk_pil_elctbl_chc_popl_id
1334 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1335 p_object_version_number => p_rec.object_version_number);
1336 --
1337 chk_enrt_perd_id
1338 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1339 p_enrt_perd_id => p_rec.enrt_perd_id,
1340 p_object_version_number => p_rec.object_version_number);
1341 --
1342 chk_enrt_typ_cycl_cd
1343 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1344 p_enrt_typ_cycl_cd => p_rec.enrt_typ_cycl_cd,
1345 p_effective_date => p_effective_date,
1346 p_object_version_number => p_rec.object_version_number);
1347 --
1348 chk_cls_enrt_dt_to_use_cd
1349 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1350 p_cls_enrt_dt_to_use_cd => p_rec.cls_enrt_dt_to_use_cd,
1351 p_effective_date => p_effective_date,
1352 p_object_version_number => p_rec.object_version_number);
1353 --
1354 chk_acty_ref_perd_cd
1355 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1356 p_acty_ref_perd_cd => p_rec.acty_ref_perd_cd,
1357 p_effective_date => p_effective_date,
1358 p_object_version_number => p_rec.object_version_number);
1359 --
1360 chk_pil_elctbl_popl_stat_cd
1361 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1362 p_pil_elctbl_popl_stat_cd => p_rec.pil_elctbl_popl_stat_cd,
1363 p_effective_date => p_effective_date,
1364 p_object_version_number => p_rec.object_version_number);
1365 --
1366 chk_Enrt_Lee_id
1367 (p_enrt_perd_id => p_rec.enrt_perd_id,
1368 p_lee_rsn_id => p_rec.lee_rsn_id,
1369 p_enrt_typ_cycl_cd => p_rec.enrt_typ_cycl_cd );
1370 --
1371 chk_uom
1375 p_object_version_number => p_rec.object_version_number
1372 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1373 p_uom => p_rec.uom,
1374 p_effective_date => p_effective_date,
1376 );
1377 --
1378 chk_bdgt_acc_cd
1379 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1380 p_bdgt_acc_cd => p_rec.bdgt_acc_cd,
1381 p_effective_date => p_effective_date,
1382 p_object_version_number => p_rec.object_version_number
1383 );
1384 --
1385 chk_pop_cd
1386 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1387 p_pop_cd => p_rec.pop_cd,
1388 p_effective_date => p_effective_date,
1389 p_object_version_number => p_rec.object_version_number
1390 );
1391 --
1392 chk_bdgt_stat_cd
1393 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1394 p_bdgt_stat_cd => p_rec.bdgt_stat_cd,
1395 p_effective_date => p_effective_date,
1396 p_object_version_number => p_rec.object_version_number
1397 );
1398 --
1399 chk_ws_acc_cd
1400 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1401 p_ws_acc_cd => p_rec.ws_acc_cd,
1402 p_effective_date => p_effective_date,
1403 p_object_version_number => p_rec.object_version_number
1404 );
1405 --
1406 chk_ws_stat_cd
1407 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1408 p_ws_stat_cd => p_rec.ws_stat_cd,
1409 p_effective_date => p_effective_date,
1410 p_object_version_number => p_rec.object_version_number
1411 );
1412 --
1413 chk_reinstate_cd
1414 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1415 p_reinstate_cd => p_rec.reinstate_cd,
1416 p_effective_date => p_effective_date,
1417 p_object_version_number => p_rec.object_version_number
1418 );
1419 --
1420 chk_reinstate_ovrdn_cd
1421 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1422 p_reinstate_ovrdn_cd => p_rec.reinstate_ovrdn_cd,
1423 p_effective_date => p_effective_date,
1424 p_object_version_number => p_rec.object_version_number
1425 );
1426 --
1427 hr_utility.set_location(' Leaving:'||l_proc, 10);
1428 End insert_validate;
1429 --
1430 -- ----------------------------------------------------------------------------
1431 -- |---------------------------< update_validate >----------------------------|
1432 -- ----------------------------------------------------------------------------
1433 Procedure update_validate(p_rec in ben_pel_shd.g_rec_type
1434 ,p_effective_date in date) is
1435 --
1436 l_proc varchar2(72) := g_package||'update_validate';
1437 --
1438 Begin
1439 hr_utility.set_location('Entering:'||l_proc, 5);
1440 --
1441 -- Call context sensitive validate bgp cache routine
1442 --
1443 ben_batch_dt_api.batch_validate_bgp_id
1444 (p_business_group_id => p_rec.business_group_id
1445 );
1446 --
1447 /*
1448 --
1449 -- Call all supporting business operations
1450 --
1451 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1452 */
1453 --
1454 chk_pil_elctbl_chc_popl_id
1455 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1456 p_object_version_number => p_rec.object_version_number);
1457 --
1458 chk_enrt_perd_id
1459 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1460 p_enrt_perd_id => p_rec.enrt_perd_id,
1461 p_object_version_number => p_rec.object_version_number);
1462 --
1463 chk_enrt_typ_cycl_cd
1464 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1465 p_enrt_typ_cycl_cd => p_rec.enrt_typ_cycl_cd,
1466 p_effective_date => p_effective_date,
1467 p_object_version_number => p_rec.object_version_number);
1468 --
1469 chk_cls_enrt_dt_to_use_cd
1470 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1471 p_cls_enrt_dt_to_use_cd => p_rec.cls_enrt_dt_to_use_cd,
1472 p_effective_date => p_effective_date,
1473 p_object_version_number => p_rec.object_version_number);
1474 --
1475 chk_acty_ref_perd_cd
1476 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1477 p_acty_ref_perd_cd => p_rec.acty_ref_perd_cd,
1478 p_effective_date => p_effective_date,
1479 p_object_version_number => p_rec.object_version_number);
1480 --
1481 chk_pil_elctbl_popl_stat_cd
1482 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1483 p_pil_elctbl_popl_stat_cd => p_rec.pil_elctbl_popl_stat_cd,
1484 p_effective_date => p_effective_date,
1485 p_object_version_number => p_rec.object_version_number);
1486 --
1487 chk_Enrt_Lee_id
1488 (p_enrt_perd_id => p_rec.enrt_perd_id,
1489 p_lee_rsn_id => p_rec.lee_rsn_id,
1490 p_enrt_typ_cycl_cd => p_rec.enrt_typ_cycl_cd );
1491 --
1492 chk_uom
1493 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1494 p_uom => p_rec.uom,
1495 p_effective_date => p_effective_date,
1496 p_object_version_number => p_rec.object_version_number
1497 );
1498 --
1499 chk_dflt_enrt_perd_end
1503 p_dflt_enrt_dt => p_rec.dflt_enrt_dt,
1500 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1501 p_enrt_perd_strt_dt => p_rec.enrt_perd_strt_dt,
1502 p_enrt_perd_end_dt => p_rec.enrt_perd_end_dt,
1504 p_dflt_asnd_dt => p_rec.dflt_asnd_dt,
1505 p_procg_end_dt => p_rec.procg_end_dt,
1506 p_pil_elctbl_popl_stat_cd => p_rec.pil_elctbl_popl_stat_cd,
1507 p_object_version_number => p_rec.object_version_number
1508 );
1509 --
1510 chk_bdgt_acc_cd
1511 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1512 p_bdgt_acc_cd => p_rec.bdgt_acc_cd,
1513 p_effective_date => p_effective_date,
1514 p_object_version_number => p_rec.object_version_number
1515 );
1516 --
1517 chk_pop_cd
1518 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1519 p_pop_cd => p_rec.pop_cd,
1520 p_effective_date => p_effective_date,
1521 p_object_version_number => p_rec.object_version_number
1522 );
1523 --
1524 chk_bdgt_stat_cd
1525 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1526 p_bdgt_stat_cd => p_rec.bdgt_stat_cd,
1527 p_effective_date => p_effective_date,
1528 p_object_version_number => p_rec.object_version_number
1529 );
1530 --
1531 chk_ws_acc_cd
1532 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1533 p_ws_acc_cd => p_rec.ws_acc_cd,
1534 p_effective_date => p_effective_date,
1535 p_object_version_number => p_rec.object_version_number
1536 );
1537 --
1538 chk_ws_stat_cd
1539 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1540 p_ws_stat_cd => p_rec.ws_stat_cd,
1541 p_effective_date => p_effective_date,
1542 p_object_version_number => p_rec.object_version_number
1543 );
1544 --
1545 chk_reinstate_cd
1546 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1547 p_reinstate_cd => p_rec.reinstate_cd,
1548 p_effective_date => p_effective_date,
1549 p_object_version_number => p_rec.object_version_number
1550 );
1551 --
1552 chk_reinstate_ovrdn_cd
1553 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1554 p_reinstate_ovrdn_cd => p_rec.reinstate_ovrdn_cd,
1555 p_effective_date => p_effective_date,
1556 p_object_version_number => p_rec.object_version_number
1557 );
1558 --
1559 /* Moved to bepilrhi.pkb
1560 chk_ws_mgr_id
1561 (p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1562 p_ws_mgr_id => p_rec.ws_mgr_id ,
1563 p_effective_date => p_effective_date
1564 );
1565 */
1566 --
1567 hr_utility.set_location(' Leaving:'||l_proc, 10);
1568 End update_validate;
1569 --
1570 -- ----------------------------------------------------------------------------
1571 -- |---------------------------< delete_validate >----------------------------|
1572 -- ----------------------------------------------------------------------------
1573 Procedure delete_validate(p_rec in ben_pel_shd.g_rec_type
1574 ,p_effective_date in date) is
1575 --
1576 l_proc varchar2(72) := g_package||'delete_validate';
1577 --
1578 Begin
1579 hr_utility.set_location('Entering:'||l_proc, 5);
1580 --
1581 -- Call all supporting business operations
1582 --
1583 hr_utility.set_location(' Leaving:'||l_proc, 10);
1584 End delete_validate;
1585 --
1586 --
1587 -- ---------------------------------------------------------------------------
1588 -- |---------------------< return_legislation_code >-------------------------|
1589 -- ---------------------------------------------------------------------------
1590 --
1591 function return_legislation_code
1592 (p_pil_elctbl_chc_popl_id in number) return varchar2 is
1593 --
1594 -- Declare cursor
1595 --
1596 cursor csr_leg_code is
1597 select a.legislation_code
1598 from per_business_groups a,
1599 ben_pil_elctbl_chc_popl b
1600 where b.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
1601 and a.business_group_id = b.business_group_id;
1602 --
1603 -- Declare local variables
1604 --
1605 l_legislation_code varchar2(150);
1606 l_proc varchar2(72) := g_package||'return_legislation_code';
1607 --
1608 begin
1609 --
1610 hr_utility.set_location('Entering:'|| l_proc, 10);
1611 --
1612 -- Ensure that all the mandatory parameter are not null
1613 --
1614 hr_api.mandatory_arg_error(p_api_name => l_proc,
1618 open csr_leg_code;
1615 p_argument => 'pil_elctbl_chc_popl_id',
1616 p_argument_value => p_pil_elctbl_chc_popl_id);
1617 --
1619 --
1620 fetch csr_leg_code into l_legislation_code;
1621 --
1622 if csr_leg_code%notfound then
1623 --
1624 close csr_leg_code;
1625 --
1626 -- The primary key is invalid therefore we must error
1627 --
1628 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1629 fnd_message.raise_error;
1630 --
1631 end if;
1632 --
1633 close csr_leg_code;
1634 --
1635 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1636 --
1637 return l_legislation_code;
1638 --
1639 end return_legislation_code;
1640 --
1641 end ben_pel_bus;