[Home] [Help]
PACKAGE BODY: APPS.BEN_PRV_BUS
Source
1 Package Body ben_prv_bus as
2 /* $Header: beprvrhi.pkb 120.0.12000000.3 2007/07/01 19:16:05 mmudigon noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_prv_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_prtt_rt_val_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 -- prtt_rt_val_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_prtt_rt_val_id(p_prtt_rt_val_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_prtt_rt_val_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_prv_shd.api_updating
47 (p_prtt_rt_val_id => p_prtt_rt_val_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_prtt_rt_val_id,hr_api.g_number)
52 <> ben_prv_shd.g_old_rec.prtt_rt_val_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_prv_shd.constraint_error('BEN_PRTT_RT_VAL_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_prtt_rt_val_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_prv_shd.constraint_error('BEN_PRTT_RT_VAL_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_prtt_rt_val_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_prtt_enrt_rslt_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_prtt_rt_val_id PK
89 -- p_prtt_enrt_rslt_id ID of FK column
90 -- p_effective_date Session Date of record
91 -- p_object_version_number object version number
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Error raised.
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 Procedure chk_prtt_enrt_rslt_id (p_prtt_rt_val_id in number,
103 p_prtt_enrt_rslt_id in number,
104 p_effective_date in date,
105 p_object_version_number in number) is
106 --
107 l_proc varchar2(72) := g_package||'chk_prtt_enrt_rslt_id';
108 l_api_updating boolean;
109 l_dummy varchar2(1);
110 --
111 cursor c1 is
112 select null
113 from ben_prtt_enrt_rslt_f a
114 where a.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
115 and p_effective_date
116 between a.effective_start_date
117 and a.effective_end_date;
118 --
119 Begin
120 --
121 hr_utility.set_location('Entering:'||l_proc,5);
122 --
123 l_api_updating := ben_prv_shd.api_updating
124 (p_prtt_rt_val_id => p_prtt_rt_val_id,
125 p_object_version_number => p_object_version_number);
126 --
127 if (l_api_updating
128 and nvl(p_prtt_enrt_rslt_id,hr_api.g_number)
129 <> nvl(ben_prv_shd.g_old_rec.prtt_enrt_rslt_id,hr_api.g_number)
130 or not l_api_updating) then
131 --
132 -- check if prtt_enrt_rslt_id value exists in ben_prtt_enrt_rslt_f table
133 --
134 open c1;
135 --
136 fetch c1 into l_dummy;
137 if c1%notfound then
138 --
139 close c1;
140 --
141 -- raise error as FK does not relate to PK in ben_prtt_enrt_rslt_f
142 -- table.
143 --
144 ben_prv_shd.constraint_error('BEN_PRTT_RT_VAL_DT1');
145 --
146 end if;
147 --
148 close c1;
149 --
150 end if;
151 --
152 hr_utility.set_location('Leaving:'||l_proc,10);
153 --
154 End chk_prtt_enrt_rslt_id;
155 --
156 -- ----------------------------------------------------------------------------
157 -- |------< chk_element_entry_value_id >------|
158 -- ----------------------------------------------------------------------------
159 --
160 -- Description
161 -- This procedure checks that a referenced foreign key actually exists
162 -- in the referenced table.
163 --
164 -- Pre-Conditions
165 -- None.
166 --
167 -- In Parameters
168 -- p_prtt_rt_val_id PK
169 -- p_element_entry_value_id ID of FK column
170 -- p_effective_date Session Date of record
171 -- p_object_version_number object version number
172 --
173 -- Post Success
174 -- Processing continues
175 --
176 -- Post Failure
177 -- Error raised.
178 --
179 -- Access Status
180 -- Internal table handler use only.
181 --
182 Procedure chk_element_entry_value_id (p_prtt_rt_val_id in number,
183 p_element_entry_value_id in number,
184 p_effective_date in date,
185 p_object_version_number in number) is
186 --
187 l_proc varchar2(72) := g_package||'chk_element_entry_value_id';
188 l_api_updating boolean;
189 l_dummy varchar2(1);
190 --
191 cursor c1 is
192 select null
193 from pay_element_entry_values_f a
194 where a.element_entry_value_id = p_element_entry_value_id
195 and p_effective_date
196 between a.effective_start_date
197 and a.effective_end_date;
198 --
199 Begin
200 --
201 hr_utility.set_location('Entering:'||l_proc,5);
202 --
203 l_api_updating := ben_prv_shd.api_updating
204 (p_prtt_rt_val_id => p_prtt_rt_val_id,
205 p_object_version_number => p_object_version_number);
206 --
207 if (l_api_updating
208 and nvl(p_element_entry_value_id,hr_api.g_number)
209 <> nvl(ben_prv_shd.g_old_rec.element_entry_value_id,hr_api.g_number)
210 or not l_api_updating) then
211 --
212 -- check if element_entry_value_id value exists in pay_element_entry_values_f table
213 --
214 open c1;
215 --
216 fetch c1 into l_dummy;
217 if c1%notfound then
218 --
219 close c1;
220 --
221 -- raise error as FK does not relate to PK in pay_element_entry_values_f
222 -- table.
223 --
224 ben_prv_shd.constraint_error('BEN_PRTT_RT_VAL_DT2');
225 --
226 end if;
227 --
228 close c1;
229 --
230 end if;
231 --
232 hr_utility.set_location('Leaving:'||l_proc,10);
233 --
234 End chk_element_entry_value_id;
235 --
236 -- ----------------------------------------------------------------------------
237 -- |------< chk_cmcd_ref_perd_cd >------|
238 -- ----------------------------------------------------------------------------
239 --
240 -- Description
241 -- This procedure is used to check that the lookup value is valid.
242 --
243 -- Pre Conditions
244 -- None.
245 --
246 -- In Parameters
247 -- prtt_rt_val_id PK of record being inserted or updated.
248 -- cmcd_ref_perd_cd Value of lookup code.
249 -- effective_date effective date
250 -- object_version_number Object version number of record being
251 -- inserted or updated.
252 --
253 -- Post Success
254 -- Processing continues
255 --
256 -- Post Failure
257 -- Error handled by procedure
258 --
259 -- Access Status
260 -- Internal table handler use only.
261 --
262 Procedure chk_cmcd_ref_perd_cd(p_prtt_rt_val_id in number,
263 p_cmcd_ref_perd_cd in varchar2,
264 p_effective_date in date,
265 p_object_version_number in number) is
266 --
267 l_proc varchar2(72) := g_package||'chk_cmcd_ref_perd_cd';
268 l_api_updating boolean;
269 --
270 Begin
271 --
272 hr_utility.set_location('Entering:'||l_proc, 5);
273 --
274 l_api_updating := ben_prv_shd.api_updating
275 (p_prtt_rt_val_id => p_prtt_rt_val_id,
276 p_object_version_number => p_object_version_number);
277 --
278 if (l_api_updating
279 and p_cmcd_ref_perd_cd
280 <> nvl(ben_prv_shd.g_old_rec.cmcd_ref_perd_cd,hr_api.g_varchar2)
281 or not l_api_updating)
282 and p_cmcd_ref_perd_cd is not null then
283 --
284 -- check if value of lookup falls within lookup type.
285 --
286 if hr_api.not_exists_in_hr_lookups
287 (p_lookup_type => 'BEN_ENRT_INFO_RT_FREQ',
288 p_lookup_code => p_cmcd_ref_perd_cd,
289 p_effective_date => p_effective_date) then
290 --
291 -- raise error as does not exist as lookup
292 --
293 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
294 fnd_message.set_token('FIELD', p_cmcd_ref_perd_cd);
295 fnd_message.set_token('TYPE','BEN_ENRT_INFO_RT_FREQ');
296 fnd_message.raise_error;
297 --
298 end if;
299 --
300 end if;
301 --
302 hr_utility.set_location('Leaving:'||l_proc,10);
303 --
304 end chk_cmcd_ref_perd_cd;
305 -- ----------------------------------------------------------------------------
306 -- |------< chk_prtt_rt_val_stat_cd >------|
307 -- ----------------------------------------------------------------------------
308 --
309 -- Description
310 -- This procedure is used to check that the lookup value is valid.
311 --
312 -- Pre Conditions
313 -- None.
314 --
315 -- In Parameters
316 -- prtt_rt_val_id PK of record being inserted or updated.
317 -- prtt_rt_val_stat_cd Value of lookup code.
318 -- effective_date effective date
319 -- object_version_number Object version number of record being
320 -- inserted or updated.
321 --
322 -- Post Success
323 -- Processing continues
324 --
325 -- Post Failure
326 -- Error handled by procedure
327 --
328 -- Access Status
329 -- Internal table handler use only.
330 --
331 Procedure chk_prtt_rt_val_stat_cd(p_prtt_rt_val_id in number,
332 p_prtt_rt_val_stat_cd in varchar2,
333 p_effective_date in date,
334 p_object_version_number in number) is
335 --
336 l_proc varchar2(72) := g_package||'chk_prtt_rt_val_stat_cd';
337 l_api_updating boolean;
338 --
339 Begin
340 --
341 hr_utility.set_location('Entering:'||l_proc, 5);
342 --
343 l_api_updating := ben_prv_shd.api_updating
344 (p_prtt_rt_val_id => p_prtt_rt_val_id,
345 p_object_version_number => p_object_version_number);
346 --
347 if (l_api_updating
348 and p_prtt_rt_val_stat_cd
349 <> nvl(ben_prv_shd.g_old_rec.prtt_rt_val_stat_cd,hr_api.g_varchar2)
350 or not l_api_updating)
351 and p_prtt_rt_val_stat_cd is not null then
352 --
353 -- check if value of lookup falls within lookup type.
354 -- The prtt-rt-val status code shares a lookup with the result status code.
355 if hr_api.not_exists_in_hr_lookups
356 (p_lookup_type => 'BEN_PRTT_ENRT_RSLT_STAT',
357 p_lookup_code => p_prtt_rt_val_stat_cd,
358 p_effective_date => p_effective_date) then
359 --
360 -- raise error as does not exist as lookup
361 --
362 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
363 fnd_message.set_token('FIELD', p_prtt_rt_val_stat_cd);
364 fnd_message.set_token('TYPE','BEN_PRTT_ENRT_RSLT_STAT');
365 fnd_message.raise_error;
366 --
367 end if;
368 --
369 end if;
370 --
371 hr_utility.set_location('Leaving:'||l_proc,10);
372 --
373 end chk_prtt_rt_val_stat_cd;
374 --
375 -- ----------------------------------------------------------------------------
376 -- |------< chk_bnft_rt_typ_cd >------|
377 -- ----------------------------------------------------------------------------
378 --
379 -- Description
380 -- This procedure is used to check that the lookup value is valid.
381 --
382 -- Pre Conditions
383 -- None.
384 --
385 -- In Parameters
386 -- prtt_rt_val_id PK of record being inserted or updated.
387 -- bnft_rt_typ_cd Value of lookup code.
388 -- effective_date effective date
389 -- object_version_number Object version number of record being
390 -- inserted or updated.
391 --
392 -- Post Success
393 -- Processing continues
394 --
395 -- Post Failure
396 -- Error handled by procedure
397 --
398 -- Access Status
399 -- Internal table handler use only.
400 --
401 Procedure chk_bnft_rt_typ_cd(p_prtt_rt_val_id in number,
402 p_bnft_rt_typ_cd in varchar2,
403 p_effective_date in date,
404 p_object_version_number in number) is
405 --
406 l_proc varchar2(72) := g_package||'chk_bnft_rt_typ_cd';
407 l_api_updating boolean;
408 --
409 Begin
410 --
411 hr_utility.set_location('Entering:'||l_proc, 5);
412 --
413 l_api_updating := ben_prv_shd.api_updating
414 (p_prtt_rt_val_id => p_prtt_rt_val_id,
415 p_object_version_number => p_object_version_number);
416 --
417 if (l_api_updating
418 and p_bnft_rt_typ_cd
419 <> nvl(ben_prv_shd.g_old_rec.bnft_rt_typ_cd,hr_api.g_varchar2)
420 or not l_api_updating)
421 and p_bnft_rt_typ_cd is not null then
422 --
423 -- check if value of lookup falls within lookup type.
424 --
425 if hr_api.not_exists_in_hr_lookups
426 (p_lookup_type => 'BEN_RT_TYP',
427 p_lookup_code => p_bnft_rt_typ_cd,
428 p_effective_date => p_effective_date) then
429 --
430 -- raise error as does not exist as lookup
431 --
432 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
433 fnd_message.set_token('FIELD', p_bnft_rt_typ_cd);
434 fnd_message.set_token('TYPE','BEN_RT_TYP');
435 fnd_message.raise_error;
436 --
437 end if;
438 --
439 end if;
440 --
441 hr_utility.set_location('Leaving:'||l_proc,10);
442 --
443 end chk_bnft_rt_typ_cd;
444 --
445 -- ----------------------------------------------------------------------------
446 -- |------< chk_acty_ref_perd_cd >------|
447 -- ----------------------------------------------------------------------------
448 --
449 -- Description
450 -- This procedure is used to check that the lookup value is valid.
451 --
452 -- Pre Conditions
453 -- None.
454 --
455 -- In Parameters
456 -- prtt_rt_val_id PK of record being inserted or updated.
457 -- acty_ref_perd_cd Value of lookup code.
458 -- effective_date effective date
459 -- object_version_number Object version number of record being
460 -- inserted or updated.
461 --
462 -- Post Success
463 -- Processing continues
464 --
465 -- Post Failure
466 -- Error handled by procedure
467 --
468 -- Access Status
469 -- Internal table handler use only.
470 --
471 Procedure chk_acty_ref_perd_cd(p_prtt_rt_val_id in number,
472 p_acty_ref_perd_cd in varchar2,
473 p_effective_date in date,
474 p_object_version_number in number) is
475 --
476 l_proc varchar2(72) := g_package||'chk_acty_ref_perd_cd';
477 l_api_updating boolean;
478 --
479 Begin
480 --
481 hr_utility.set_location('Entering:'||l_proc, 5);
482 --
483 l_api_updating := ben_prv_shd.api_updating
484 (p_prtt_rt_val_id => p_prtt_rt_val_id,
485 p_object_version_number => p_object_version_number);
486 --
487 if (l_api_updating
488 and p_acty_ref_perd_cd
489 <> nvl(ben_prv_shd.g_old_rec.acty_ref_perd_cd,hr_api.g_varchar2)
490 or not l_api_updating)
491 and p_acty_ref_perd_cd is not null then
492 --
493 -- check if value of lookup falls within lookup type.
494 --
495 if hr_api.not_exists_in_hr_lookups
496 (p_lookup_type => 'BEN_ACTY_REF_PERD',
497 p_lookup_code => p_acty_ref_perd_cd,
498 p_effective_date => p_effective_date) then
499 --
500 -- raise error as does not exist as lookup
501 --
502 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
503 fnd_message.set_token('FIELD', p_acty_ref_perd_cd);
504 fnd_message.set_token('TYPE','BEN_ACTY_REF_PERD');
505 fnd_message.raise_error;
506 --
507 end if;
508 --
509 end if;
510 --
511 hr_utility.set_location('Leaving:'||l_proc,10);
512 --
513 end chk_acty_ref_perd_cd;
514 --
515 -- ----------------------------------------------------------------------------
516 -- |------< chk_mlt_cd >------|
517 -- ----------------------------------------------------------------------------
518 --
519 -- Description
520 -- This procedure is used to check that the lookup value is valid.
521 --
522 -- Pre Conditions
523 -- None.
524 --
525 -- In Parameters
526 -- prtt_rt_val_id PK of record being inserted or updated.
527 -- mlt_cd Value of lookup code.
528 -- effective_date effective date
529 -- object_version_number Object version number of record being
530 -- inserted or updated.
531 --
532 -- Post Success
533 -- Processing continues
534 --
535 -- Post Failure
536 -- Error handled by procedure
537 --
538 -- Access Status
539 -- Internal table handler use only.
540 --
541 Procedure chk_mlt_cd(p_prtt_rt_val_id in number,
542 p_mlt_cd in varchar2,
543 p_effective_date in date,
544 p_object_version_number in number) is
545 --
546 l_proc varchar2(72) := g_package||'chk_mlt_cd';
547 l_api_updating boolean;
548 --
549 Begin
550 --
551 hr_utility.set_location('Entering:'||l_proc, 5);
552 --
553 l_api_updating := ben_prv_shd.api_updating
554 (p_prtt_rt_val_id => p_prtt_rt_val_id,
555 p_object_version_number => p_object_version_number);
556 --
557 if (l_api_updating
558 and p_mlt_cd
559 <> nvl(ben_prv_shd.g_old_rec.mlt_cd,hr_api.g_varchar2)
560 or not l_api_updating)
561 and p_mlt_cd is not null then
562 --
563 -- check if value of lookup falls within lookup type.
564 --
565 if hr_api.not_exists_in_hr_lookups
566 (p_lookup_type => 'BEN_MLT',
567 p_lookup_code => p_mlt_cd,
568 p_effective_date => p_effective_date) then
569 --
570 -- raise error as does not exist as lookup
571 --
572 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
573 fnd_message.set_token('FIELD', p_mlt_cd);
574 fnd_message.set_token('TYPE','BEN_MLT');
575 fnd_message.raise_error;
576 --
577 end if;
578 --
579 end if;
580 --
581 hr_utility.set_location('Leaving:'||l_proc,10);
582 --
583 end chk_mlt_cd;
584 --
585 -- ----------------------------------------------------------------------------
586 -- |------< chk_acty_typ_cd >------|
587 -- ----------------------------------------------------------------------------
588 --
589 -- Description
590 -- This procedure is used to check that the lookup value is valid.
591 --
592 -- Pre Conditions
593 -- None.
594 --
595 -- In Parameters
596 -- prtt_rt_val_id PK of record being inserted or updated.
597 -- acty_typ_cd Value of lookup code.
598 -- effective_date effective date
599 -- object_version_number Object version number of record being
600 -- inserted or updated.
601 --
602 -- Post Success
603 -- Processing continues
604 --
605 -- Post Failure
606 -- Error handled by procedure
607 --
608 -- Access Status
609 -- Internal table handler use only.
610 --
611 Procedure chk_acty_typ_cd(p_prtt_rt_val_id in number,
612 p_acty_typ_cd in varchar2,
613 p_effective_date in date,
614 p_object_version_number in number) is
615 --
616 l_proc varchar2(72) := g_package||'chk_acty_typ_cd';
617 l_api_updating boolean;
618 --
619 Begin
620 --
621 hr_utility.set_location('Entering:'||l_proc, 5);
622 --
623 l_api_updating := ben_prv_shd.api_updating
624 (p_prtt_rt_val_id => p_prtt_rt_val_id,
625 p_object_version_number => p_object_version_number);
626 --
627 if (l_api_updating
628 and p_acty_typ_cd
629 <> nvl(ben_prv_shd.g_old_rec.acty_typ_cd,hr_api.g_varchar2)
630 or not l_api_updating)
631 and p_acty_typ_cd is not null then
632 --
633 -- check if value of lookup falls within lookup type.
634 --
635 if hr_api.not_exists_in_hr_lookups
636 (p_lookup_type => 'BEN_ACTY_TYP',
637 p_lookup_code => p_acty_typ_cd,
638 p_effective_date => p_effective_date) then
639 --
640 -- raise error as does not exist as lookup
641 --
642 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
643 fnd_message.set_token('FIELD', p_acty_typ_cd);
644 fnd_message.set_token('TYPE','BEN_ACTY_TYP');
645 fnd_message.raise_error;
646 --
647 end if;
648 --
649 end if;
650 --
651 hr_utility.set_location('Leaving:'||l_proc,10);
652 --
653 end chk_acty_typ_cd;
654 --
655 -- ----------------------------------------------------------------------------
656 -- |------< chk_tx_typ_cd >------|
657 -- ----------------------------------------------------------------------------
658 --
659 -- Description
660 -- This procedure is used to check that the lookup value is valid.
661 --
662 -- Pre Conditions
663 -- None.
664 --
665 -- In Parameters
666 -- prtt_rt_val_id PK of record being inserted or updated.
667 -- tx_typ_cd Value of lookup code.
668 -- effective_date effective date
669 -- object_version_number Object version number of record being
670 -- inserted or updated.
671 --
672 -- Post Success
673 -- Processing continues
674 --
675 -- Post Failure
676 -- Error handled by procedure
677 --
678 -- Access Status
679 -- Internal table handler use only.
680 --
681 Procedure chk_tx_typ_cd(p_prtt_rt_val_id in number,
682 p_tx_typ_cd in varchar2,
683 p_effective_date in date,
684 p_object_version_number in number) is
685 --
686 l_proc varchar2(72) := g_package||'chk_tx_typ_cd';
687 l_api_updating boolean;
688 --
689 Begin
690 --
691 hr_utility.set_location('Entering:'||l_proc, 5);
692 --
693 l_api_updating := ben_prv_shd.api_updating
694 (p_prtt_rt_val_id => p_prtt_rt_val_id,
695 p_object_version_number => p_object_version_number);
696 --
697 if (l_api_updating
698 and p_tx_typ_cd
699 <> nvl(ben_prv_shd.g_old_rec.tx_typ_cd,hr_api.g_varchar2)
700 or not l_api_updating)
701 and p_tx_typ_cd is not null then
702 --
703 -- check if value of lookup falls within lookup type.
704 --
705 if hr_api.not_exists_in_hr_lookups
706 (p_lookup_type => 'BEN_TX_TYP',
707 p_lookup_code => p_tx_typ_cd,
708 p_effective_date => p_effective_date) then
709 --
710 -- raise error as does not exist as lookup
711 --
712 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
713 fnd_message.set_token('FIELD', p_tx_typ_cd);
714 fnd_message.set_token('TYPE','BEN_TX_TYP');
715 fnd_message.raise_error;
716 --
717 end if;
718 --
719 end if;
720 --
721 hr_utility.set_location('Leaving:'||l_proc,10);
722 --
723 end chk_tx_typ_cd;
724 --
725 -- ----------------------------------------------------------------------------
726 -- |------< chk_rt_typ_cd >------|
727 -- ----------------------------------------------------------------------------
728 --
729 -- Description
730 -- This procedure is used to check that the lookup value is valid.
731 --
732 -- Pre Conditions
733 -- None.
734 --
735 -- In Parameters
736 -- prtt_rt_val_id PK of record being inserted or updated.
737 -- rt_typ_cd Value of lookup code.
738 -- effective_date effective date
739 -- object_version_number Object version number of record being
740 -- inserted or updated.
741 --
742 -- Post Success
743 -- Processing continues
744 --
745 -- Post Failure
746 -- Error handled by procedure
747 --
748 -- Access Status
749 -- Internal table handler use only.
750 --
751 Procedure chk_rt_typ_cd(p_prtt_rt_val_id in number,
752 p_rt_typ_cd in varchar2,
753 p_effective_date in date,
754 p_object_version_number in number) is
755 --
756 l_proc varchar2(72) := g_package||'chk_rt_typ_cd';
757 l_api_updating boolean;
758 --
759 Begin
760 --
761 hr_utility.set_location('Entering:'||l_proc, 5);
762 --
763 l_api_updating := ben_prv_shd.api_updating
764 (p_prtt_rt_val_id => p_prtt_rt_val_id,
765 p_object_version_number => p_object_version_number);
766 --
767 if (l_api_updating
768 and p_rt_typ_cd
769 <> nvl(ben_prv_shd.g_old_rec.rt_typ_cd,hr_api.g_varchar2)
770 or not l_api_updating)
771 and p_rt_typ_cd is not null then
772 --
773 -- check if value of lookup falls within lookup type.
774 --
775 if hr_api.not_exists_in_hr_lookups
776 (p_lookup_type => 'BEN_RT_TYP',
777 p_lookup_code => p_rt_typ_cd,
778 p_effective_date => p_effective_date) then
779 --
780 -- raise error as does not exist as lookup
781 --
782 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
783 fnd_message.set_token('FIELD', p_rt_typ_cd);
784 fnd_message.set_token('TYPE','BEN_RT_TYP');
785 fnd_message.raise_error;
786 --
787 end if;
788 --
789 end if;
790 --
791 hr_utility.set_location('Leaving:'||l_proc,10);
792 --
793 end chk_rt_typ_cd;
794 --
795 -- ----------------------------------------------------------------------------
796 -- |------< chk_rt_ovridn_flag >------|
797 -- ----------------------------------------------------------------------------
798 --
799 -- Description
800 -- This procedure is used to check that the lookup value is valid.
801 --
802 -- Pre Conditions
803 -- None.
804 --
805 -- In Parameters
806 -- prtt_rt_val_id PK of record being inserted or updated.
807 -- rt_ovridn_flag Value of lookup code.
808 -- effective_date effective date
809 -- object_version_number Object version number of record being
810 -- inserted or updated.
811 --
812 -- Post Success
813 -- Processing continues
814 --
815 -- Post Failure
816 -- Error handled by procedure
817 --
818 -- Access Status
819 -- Internal table handler use only.
820 --
821 Procedure chk_rt_ovridn_flag(p_prtt_rt_val_id in number,
822 p_rt_ovridn_flag in varchar2,
823 p_effective_date in date,
824 p_object_version_number in number) is
825 --
826 l_proc varchar2(72) := g_package||'chk_rt_ovridn_flag';
827 l_api_updating boolean;
828 --
829 Begin
830 --
831 hr_utility.set_location('Entering:'||l_proc, 5);
832 --
833 l_api_updating := ben_prv_shd.api_updating
834 (p_prtt_rt_val_id => p_prtt_rt_val_id,
835 p_object_version_number => p_object_version_number);
836 --
837 if (l_api_updating
838 and p_rt_ovridn_flag
839 <> nvl(ben_prv_shd.g_old_rec.rt_ovridn_flag,hr_api.g_varchar2)
840 or not l_api_updating) then
841 --
842 -- check if value of lookup falls within lookup type.
843 --
844 --
845 if hr_api.not_exists_in_hr_lookups
846 (p_lookup_type => 'YES_NO',
847 p_lookup_code => p_rt_ovridn_flag,
848 p_effective_date => p_effective_date) then
849 --
850 -- raise error as does not exist as lookup
851 --
852 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
853 fnd_message.set_token('FIELD', p_rt_ovridn_flag);
854 fnd_message.set_token('TYPE','YES_NO');
855 fnd_message.raise_error;
856 --
857 end if;
858 --
859 end if;
860 --
861 hr_utility.set_location('Leaving:'||l_proc,10);
862 --
863 end chk_rt_ovridn_flag;
864 --
865 -- ----------------------------------------------------------------------------
866 -- |------< chk_dsply_on_enrt_flag >------|
867 -- ----------------------------------------------------------------------------
868 --
869 -- Description
870 -- This procedure is used to check that the lookup value is valid.
871 --
872 -- Pre Conditions
873 -- None.
874 --
875 -- In Parameters
876 -- prtt_rt_val_id PK of record being inserted or updated.
877 -- dsply_on_enrt_flag Value of lookup code.
878 -- effective_date effective date
879 -- object_version_number Object version number of record being
880 -- inserted or updated.
881 --
882 -- Post Success
883 -- Processing continues
884 --
885 -- Post Failure
886 -- Error handled by procedure
887 --
888 -- Access Status
889 -- Internal table handler use only.
890 --
891 Procedure chk_dsply_on_enrt_flag(p_prtt_rt_val_id in number,
892 p_dsply_on_enrt_flag in varchar2,
893 p_effective_date in date,
894 p_object_version_number in number) is
895 --
896 l_proc varchar2(72) := g_package||'chk_dsply_on_enrt_flag';
897 l_api_updating boolean;
898 --
899 Begin
900 --
901 hr_utility.set_location('Entering:'||l_proc, 5);
902 --
903 l_api_updating := ben_prv_shd.api_updating
904 (p_prtt_rt_val_id => p_prtt_rt_val_id,
905 p_object_version_number => p_object_version_number);
906 --
907 if (l_api_updating
908 and p_dsply_on_enrt_flag
909 <> nvl(ben_prv_shd.g_old_rec.dsply_on_enrt_flag,hr_api.g_varchar2)
910 or not l_api_updating) then
911 --
912 -- check if value of lookup falls within lookup type.
913 --
914 --
915 if hr_api.not_exists_in_hr_lookups
916 (p_lookup_type => 'YES_NO',
917 p_lookup_code => p_dsply_on_enrt_flag,
918 p_effective_date => p_effective_date) then
919 --
920 -- raise error as does not exist as lookup
921 --
922 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
923 fnd_message.set_token('FIELD', p_dsply_on_enrt_flag);
924 fnd_message.set_token('TYPE','YES_NO');
925 fnd_message.raise_error;
926 --
927 end if;
928 --
929 end if;
930 --
931 hr_utility.set_location('Leaving:'||l_proc,10);
932 --
933 end chk_dsply_on_enrt_flag;
934
935 --
936 -- ----------------------------------------------------------------------------
937 -- |------< chk_ann_min_max_val >------|
938 -- ----------------------------------------------------------------------------
939 --
940 -- Description
941 -- This procedure is used to check that the value is in the range and
942 -- checks if the min and max should be updated.
943 --
944 --
945 -- Access Status
946 -- Internal table handler use only.
947 --
948 Procedure chk_ann_min_max_val(
949 p_enrt_rt_id in number default null,
950 p_prtt_rt_val_id in number,
951 p_ann_rt_val in number,
952 p_rt_strt_dt in date,
953 p_effective_date in date,
954 p_object_version_number in number) is
955 --
956 l_proc varchar2(72) := g_package||'chk_ann_min_max_val';
957 l_api_updating boolean;
958 l_ann_mn_elcn_val number := null;
959 l_ann_mx_elcn_val number := null;
960 l_pln_name varchar2(600) :=null;
961 --
962 cursor c1 is
963 select ecr.ann_mn_elcn_val, ecr.ann_mx_elcn_val, ecr.acty_base_rt_id,
964 ecr.elig_per_elctbl_chc_id, ecr.enrt_bnft_id, ecr.object_version_number,
965 ecr.entr_ann_val_flag, dsply_mn_elcn_val, dsply_mx_elcn_val,incrmt_elcn_val
966 From ben_enrt_rt ecr
967 where ecr.enrt_rt_id=p_enrt_rt_id;
968 l_c1 c1%rowtype;
969
970 cursor c2 (p_enrt_bnft_id number) is
971 select enb.elig_per_elctbl_chc_id
972 from ben_enrt_bnft enb
973 where enb.enrt_bnft_id = p_enrt_bnft_id;
974
975 cursor c3 (p_elig_per_elctbl_chc_id number) is
976 select pil.person_id
977 from ben_elig_per_elctbl_chc epe, ben_per_in_ler pil
978 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
979 and epe.per_in_ler_id = pil.per_in_ler_id;
980 l_c3 c3%rowtype;
981
982 cursor c_chc is
983 select decode (ecr.elig_per_elctbl_chc_id ,
984 null,
985 enb.elig_per_elctbl_chc_id,
986 ecr.elig_per_elctbl_chc_id
987 )
988 from ben_enrt_rt ecr,
989 ben_enrt_bnft enb
990 where ecr.enrt_rt_id = p_enrt_rt_id and
991 enb.enrt_bnft_id(+) = ecr.enrt_bnft_id;
992
993 cursor c_pln
994 (c_elig_per_elctbl_chc_id in number )
995 is
996 select pln.name || ' '|| opt.name
997 from ben_elig_per_elctbl_chc epe,
998 ben_pl_f pln,
999 ben_oipl_f oipl,
1000 ben_opt_f opt
1001 where epe.elig_per_elctbl_chc_id =c_elig_per_elctbl_chc_id
1002 and epe.pl_id = pln.pl_id
1003 and epe.oipl_id = oipl.oipl_id(+)
1004 and oipl.opt_id = opt.opt_id(+)
1005 and p_effective_date between
1006 pln.effective_start_date and pln.effective_end_date
1007 and p_effective_date between
1008 oipl.effective_start_date(+) and oipl.effective_end_date(+)
1009 and p_effective_date between
1010 opt.effective_start_date(+) and opt.effective_end_date(+);
1011
1012 -- 4272271
1013 /*
1014 cursor c_abr(c_acty_base_rt_id number) is
1015 select abr.ann_mn_elcn_val, abr.ann_mx_elcn_val
1016 from ben_acty_base_rt_f abr
1017 where abr.acty_base_rt_id = c_acty_base_rt_id
1018 and p_effective_date between abr.effective_start_date
1019 and abr.effective_end_date;
1020 */
1021
1022 l_ptd_balance number := 0;
1023 l_clm_balance number := 0;
1024 l_elig_per_elctbl_chc_id number :=0;
1025 Begin
1026 --
1027 hr_utility.set_location('Entering:'||l_proc, 5);
1028 --
1029 l_api_updating := ben_prv_shd.api_updating
1030 (p_prtt_rt_val_id => p_prtt_rt_val_id,
1031 p_object_version_number => p_object_version_number);
1032 --
1033 If (l_api_updating
1034 and p_ann_rt_val
1035 <> nvl(ben_prv_shd.g_old_rec.ann_rt_val,hr_api.g_number)
1036 or not l_api_updating)
1037 and p_ann_rt_val is not null then
1038 --
1039 -- get the annual min max values from enrt_rt If enrt_rt_id is not NULL
1040 --
1041 If (p_enrt_rt_id is not NULL) then
1042 open c1;
1043 fetch c1 into l_c1 ;
1044 close c1;
1045 --
1046 l_ann_mn_elcn_val := l_c1.ann_mn_elcn_val;
1047 l_ann_mx_elcn_val := l_c1.ann_mx_elcn_val;
1048 --
1049 if l_c1.entr_ann_val_flag = 'Y' then
1050 -- re-prorate and check balances as these could have changed since enrt_rt
1051 -- was created.
1052 -- Bug: 4272271. Commented out the prorate_min_max as proration was happening on already prorated values.
1053 /*
1054 -- Bug: 4272271. Pick l_ann_mn_elcn_val, l_ann_mx_elcn_val from
1055 -- ben_acty_base_rt_f, rather than enrt_rt to avoid proration happening on already prorated value.
1056 open c_abr(l_c1.acty_base_rt_id);
1057 fetch c_abr into l_ann_mn_elcn_val, l_ann_mx_elcn_val;
1058 if c_abr%notfound then
1059 l_ann_mn_elcn_val := l_c1.ann_mn_elcn_val;
1060 l_ann_mx_elcn_val := l_c1.ann_mx_elcn_val;
1061 end if;
1062 close c_abr;
1063 -- End 4272271
1064 */
1065
1066 if l_c1.elig_per_elctbl_chc_id is null then
1067 -- get the chc id from the bnft row
1068 open c2(p_enrt_bnft_id => l_c1.enrt_bnft_id);
1069 fetch c2 into l_c1.elig_per_elctbl_chc_id;
1070 close c2;
1071 end if;
1072
1073 -- get the person id
1074 open c3(p_elig_per_elctbl_chc_id => l_c1.elig_per_elctbl_chc_id);
1075 fetch c3 into l_c3;
1076 close c3;
1077
1078 -- Bug: 4272271. Commented out the prorate_min_max as proration was happening on already prorated values.
1079 /* ben_distribute_rates.prorate_min_max
1080 (p_person_id => l_c3.person_id
1081 ,p_effective_date => p_effective_date
1082 ,p_elig_per_elctbl_chc_id => l_c1.elig_per_elctbl_chc_id
1083 ,p_acty_base_rt_id => l_c1.acty_base_rt_id
1084 ,p_rt_strt_dt => p_rt_strt_dt
1085 ,p_ann_mn_val => l_ann_mn_elcn_val
1086 ,p_ann_mx_val => l_ann_mx_elcn_val ) ;
1087 */
1088
1089 -- Also, check that their period-to-date payments and claims do not
1090 -- force the minimum and maximum to be different.
1091 ben_distribute_rates.compare_balances
1092 (p_person_id => l_c3.person_id
1093 ,p_effective_date => p_effective_date
1094 ,p_elig_per_elctbl_chc_id => l_c1.elig_per_elctbl_chc_id
1095 ,p_acty_base_rt_id => l_c1.acty_base_rt_id
1096 ,p_ann_mn_val => l_ann_mn_elcn_val
1097 ,p_ann_mx_val => l_ann_mx_elcn_val
1098 ,p_perform_edit_flag => 'Y'
1099 ,p_entered_ann_val => p_ann_rt_val
1100 ,p_ptd_balance => l_ptd_balance
1101 ,p_clm_balance => l_clm_balance ) ;
1102
1103 -- Bug: 4272271. Since prorate_min_max is not called, no need to call update_enrollment_rate
1104
1105 -- If the values changed, update ben_enrt_rt so that the display will be
1106 -- correct.
1107 /*
1108 if l_ann_mn_elcn_val <> l_c1.ann_mn_elcn_val or
1109 l_ann_mx_elcn_val <> l_c1.ann_mx_elcn_val then
1110 l_c1.dsply_mn_elcn_val := l_ann_mn_elcn_val;
1111 l_c1.dsply_mx_elcn_val := l_ann_mx_elcn_val;
1112
1113 ben_enrollment_rate_api.update_enrollment_rate
1114 (p_VALIDATE => false
1115 ,p_ENRT_RT_ID => p_enrt_rt_id
1116 ,p_ANN_MN_ELCN_VAL => l_ann_mn_elcn_val
1117 ,p_ANN_MX_ELCN_VAL => l_ann_mx_elcn_val
1118 ,p_DSPLY_MN_ELCN_VAL => l_c1.dsply_mn_elcn_val
1119 ,p_DSPLY_MX_ELCN_VAL => l_c1.dsply_mx_elcn_val
1120 ,p_OBJECT_VERSION_NUMBER => l_c1.object_version_number
1121 ,p_EFFECTIVE_DATE => p_EFFECTIVE_DATE);
1122 end if; */
1123
1124 end if;
1125 --
1126 -- check if the value is in range.
1127 --
1128 If (l_ann_mn_elcn_val is not NULL and p_ann_rt_val < l_ann_mn_elcn_val)
1129 or (l_ann_mx_elcn_val is not NULL and p_ann_rt_val > l_ann_mx_elcn_val) then
1130
1131 -- Bug 2385186 add pl-opt name to error message
1132 open c_chc;
1133 fetch c_chc into l_elig_per_elctbl_chc_id;
1134 close c_chc;
1135 open c_pln (c_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id);
1136 fetch c_pln into l_pln_name;
1137 close c_pln;
1138 fnd_message.set_name('BEN','BEN_91939_NOT_IN_RANGE');
1139 fnd_message.set_token('MIN',l_ann_mn_elcn_val);
1140 fnd_message.set_token('MAX',l_ann_mx_elcn_val);
1141 fnd_message.set_token('PLOPT',l_pln_name);
1142 fnd_message.raise_error;
1143 END IF;
1144 --
1145 -- Bug 2438533 Added this for checking annual increment values
1146 --
1147 IF (mod(p_ann_rt_val,l_c1.incrmt_elcn_val)<>0) then
1148 -- bug # 1699585 passing the plan name in the error message
1149 open c_chc;
1150 fetch c_chc into l_elig_per_elctbl_chc_id;
1151 close c_chc;
1152 open c_pln (c_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id);
1153 fetch c_pln into l_pln_name;
1154 close c_pln;
1155 -- end # 1699585
1156
1157 --
1158 -- raise error is not multiple of increment
1159 --
1160 fnd_message.set_name('BEN','BEN_91932_NOT_INCREMENT');
1161 fnd_message.set_token('INCREMENT', l_c1.incrmt_elcn_val);
1162 fnd_message.set_token('PLAN', l_pln_name);
1163
1164 fnd_message.raise_error;
1165 END IF;
1166 End if;
1167 End if;
1168 --
1169 hr_utility.set_location('Leaving:'||l_proc,10);
1170 --
1171 end chk_ann_min_max_val;
1172 -- ----------------------------------------------------------------------------
1173 -- |------< chk_min_max_incrt_val >------|
1174 -- ----------------------------------------------------------------------------
1175 --
1176 -- Description
1177 -- This procedure is used to check that the value is in the range and
1178 -- conforms to the increment value.
1179 --
1180 -- Pre Conditions
1181 -- None.
1182 --
1183 -- In Parameters
1184 -- enrt_rt_id FK of enrolment rate to ckeck against.
1185 -- prtt_rt_val_id PK of record being inserted or updated.
1186 -- rt_val Value of lookup code.
1187 -- effective_date effective date
1188 -- object_version_number Object version number of record being
1189 -- inserted or updated.
1190 --
1191 -- Post Success
1192 -- Processing continues
1193 --
1194 -- Post Failure
1195 -- Error handled by procedure
1196 --
1197 -- Access Status
1198 -- Internal table handler use only.
1199 --
1200 Procedure chk_min_max_incrt_val(
1201 p_enrt_rt_id in number default null,
1202 p_prtt_rt_val_id in number,
1203 p_rt_val in number,
1204 p_effective_date in date,
1205 p_object_version_number in number) is
1206 --
1207 l_proc varchar2(72) := g_package||'chk_min_max_incrt_val';
1208 l_api_updating boolean;
1209 l_min number;
1210 l_ann_min number;
1211 l_ann_max number;
1212 l_max number;
1213 l_incrt number;
1214 l_acty_base_rt_id number ;
1215 l_entr_val_at_enrt_flag varchar2(30) ;
1216 l_rt_mlt_cd varchar2(30) ;
1217 l_entr_ann_val_flag varchar2(30) ;
1218 l_pln_name varchar2(600);
1219 --
1220 cursor c_enrt_rt is
1221 select mn_elcn_val,
1222 mx_elcn_val,
1223 ann_mn_elcn_val,
1224 ann_mx_elcn_val,
1225 incrmt_elcn_val,
1226 acty_base_rt_id,
1227 rt_mlt_cd ,
1228 entr_val_at_enrt_flag,
1229 entr_ann_val_flag
1230 from ben_enrt_rt
1231 where enrt_rt_id=p_enrt_rt_id ;
1232
1233
1234 cursor c_abr (p_acty_base_rt_id number , p_effective_date date) is
1235 select use_calc_acty_bs_rt_flag
1236 from ben_acty_base_rt_f
1237 where acty_base_rt_id = p_acty_base_rt_id
1238 and p_effective_date between effective_start_date and effective_end_date ;
1239
1240 cursor c_chc is
1241 select decode (ecr.elig_per_elctbl_chc_id ,
1242 null,
1243 enb.elig_per_elctbl_chc_id,
1244 ecr.elig_per_elctbl_chc_id
1245 )
1246 from ben_enrt_rt ecr,
1247 ben_enrt_bnft enb
1248 where ecr.enrt_rt_id = p_enrt_rt_id and
1249 enb.enrt_bnft_id (+) = ecr.enrt_bnft_id;
1250
1251 cursor c_pln
1252 (c_elig_per_elctbl_chc_id in number )
1253 is
1254 select pln.name || ' '|| opt.name
1255 from ben_elig_per_elctbl_chc epe,
1256 ben_pl_f pln,
1257 ben_oipl_f oipl,
1258 ben_opt_f opt
1259 where epe.elig_per_elctbl_chc_id =c_elig_per_elctbl_chc_id
1260 and epe.pl_id = pln.pl_id
1261 and epe.oipl_id = oipl.oipl_id(+)
1262 and oipl.opt_id = opt.opt_id(+)
1263 and p_effective_date between
1264 pln.effective_start_date and pln.effective_end_date
1265 and p_effective_date between
1266 oipl.effective_start_date(+) and oipl.effective_end_date(+)
1267 and p_effective_date between
1268 opt.effective_start_date(+) and opt.effective_end_date(+);
1269
1270 l_use_calc_acty_bs_rt_flag varchar2(30) ;
1271 l_elig_per_elctbl_chc_id number :=0;
1272
1273 Begin
1274 --
1275 hr_utility.set_location('Entering:'||l_proc, 5);
1276 --
1277 l_api_updating := ben_prv_shd.api_updating
1278 (p_prtt_rt_val_id => p_prtt_rt_val_id,
1279 p_object_version_number => p_object_version_number);
1280 --
1281 if (l_api_updating
1282 and p_rt_val
1283 <> nvl(ben_prv_shd.g_old_rec.rt_val,hr_api.g_number)
1284 or not l_api_updating)
1285 and p_rt_val is not null then
1286 --
1287 -- get the min max and incrt values from enrt_rt
1288 --
1289 open c_enrt_rt;
1290 fetch c_enrt_rt into
1291 l_min,
1292 l_max,
1293 l_ann_min,
1294 l_ann_max,
1295 l_incrt,
1296 l_acty_base_rt_id,
1297 l_rt_mlt_cd ,
1298 l_entr_val_at_enrt_flag,
1299 l_entr_ann_val_flag ;
1300
1301 close c_enrt_rt;
1302
1303 open c_abr(l_acty_base_rt_id , p_effective_Date);
1304 fetch c_abr into l_use_calc_acty_bs_rt_flag;
1305 close c_abr ;
1306 --
1307 -- check if value is in range.
1308 --
1309 -- if the entr_at_enrt and calc value tne dont check the min and max
1310 --- the chekc is done at benactbr the calc result is higher than the min and max
1311 --
1312 --Bug 2438533 If enter annual flag is 'Y' then we have another check procedure
1313 -- for annual min and max. we can bipass this
1314 -- this is handled in the chk procedure chk_ann_min_max_val
1315 --
1316 If ( l_use_calc_acty_bs_rt_flag = 'Y' and l_entr_val_at_enrt_flag = 'Y'
1317 and l_rt_mlt_cd <> 'FLFX') or l_entr_ann_val_flag = 'Y' then
1318 null ;
1319 -- bug 1480407
1320 else
1321 if ((l_min is not null and p_rt_val <l_min) or
1322 (l_max is not null and p_rt_val >l_max)) then
1323 --
1324 -- raise error as is not in range
1325 -- Bug 2385186 add pl-opt name to error message
1326
1327 open c_chc;
1328 fetch c_chc into l_elig_per_elctbl_chc_id;
1329 close c_chc;
1330 open c_pln (c_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id);
1331 fetch c_pln into l_pln_name;
1332 close c_pln;
1333 fnd_message.set_name('BEN','BEN_91939_NOT_IN_RANGE');
1334 fnd_message.set_token('MIN', l_min);
1335 fnd_message.set_token('MAX', l_max);
1336 fnd_message.set_token('PLOPT', l_pln_name);
1337 fnd_message.raise_error;
1338 --
1339 elsif (mod(p_rt_val,l_incrt)<>0) then
1340 --
1341 -- raise error is not multiple of increment
1342 --
1343 open c_chc;
1344 fetch c_chc into l_elig_per_elctbl_chc_id;
1345 close c_chc;
1346 open c_pln (c_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id);
1347 fetch c_pln into l_pln_name;
1348 close c_pln;
1349 -- end # 1699585
1350
1351 --
1352 -- raise error is not multiple of increment
1353 --
1354 fnd_message.set_name('BEN','BEN_91932_NOT_INCREMENT');
1355 fnd_message.set_token('INCREMENT', l_incrt);
1356 fnd_message.set_token('PLAN', l_pln_name);
1357 fnd_message.raise_error;
1358 end if;
1359 end if ;
1360 elsif p_enrt_rt_id is not null and p_rt_val is null then
1361 --
1362 -- Bug : 3649575
1363 -- Raise an error even when p_rt_val is null and Enter Value At Enrollment is checked.
1364 --
1365 open c_enrt_rt;
1366 fetch c_enrt_rt into
1367 l_min,
1368 l_max,
1369 l_ann_min,
1370 l_ann_max,
1371 l_incrt,
1372 l_acty_base_rt_id,
1373 l_rt_mlt_cd ,
1374 l_entr_val_at_enrt_flag,
1375 l_entr_ann_val_flag ;
1376 close c_enrt_rt;
1377 --
1378 if l_entr_val_at_enrt_flag = 'Y' then
1379 --
1380 open c_chc;
1381 fetch c_chc into l_elig_per_elctbl_chc_id;
1382 close c_chc;
1383 --
1384 open c_pln (c_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id);
1385 fetch c_pln into l_pln_name;
1386 close c_pln;
1387 --
1388 fnd_message.set_name('BEN','BEN_91939_NOT_IN_RANGE');
1389 fnd_message.set_token('PLOPT', l_pln_name);
1390 --
1391 if l_entr_ann_val_flag = 'N' then
1392 --
1393 fnd_message.set_token('MIN',l_min);
1394 fnd_message.set_token('MAX',l_max);
1395 --
1396 elsif l_entr_ann_val_flag = 'Y' then
1397 --
1398 fnd_message.set_token('MIN',l_ann_min);
1399 fnd_message.set_token('MAX',l_ann_max);
1400 --
1401 end if;
1402 --
1403 fnd_message.raise_error;
1404 --
1405 end if;
1406 --
1407 end if;
1408
1409 hr_utility.set_location('Leaving:'||l_proc,10);
1410
1411 end chk_min_max_incrt_val;
1412 --
1413 -- ----------------------------------------------------------------------------
1414 -- |------< chk_acty_base_rt_id >------|
1415 -- ----------------------------------------------------------------------------
1416 --
1417 -- Description
1418 -- This procedure is used to check that abr is valid
1419 --
1420 -- Pre Conditions
1421 -- None.
1422 --
1423 -- In Parameters
1424 -- prtt_rt_val_id PK of record being inserted or updated.
1425 -- effective_date effective date
1426 -- object_version_number Object version number of record being
1427 -- inserted or updated.
1428 --
1429 -- Post Success
1430 -- Processing continues
1431 --
1432 -- Post Failure
1433 -- Error handled by procedure
1434 --
1435 -- Access Status
1436 -- Internal table handler use only.
1437 --
1438 Procedure chk_acty_base_rt_id(p_prtt_rt_val_id in number,
1439 p_object_version_number in number,
1440 p_acty_base_rt_id in number,
1441 p_effective_date in date) is
1442 --
1443 l_proc varchar2(72) := g_package||'chk_acty_base_rt_id';
1444 l_api_updating boolean;
1445 l_error boolean := false;
1446 l_dummy varchar2(1);
1447 --
1448 cursor c_abr is
1449 select null
1450 from ben_acty_base_rt_f
1451 where acty_base_rt_id = p_acty_base_rt_id
1452 and p_effective_date between effective_start_date
1453 and effective_end_date;
1454 Begin
1455 --
1456 hr_utility.set_location('Entering:'||l_proc, 5);
1457 --
1458 l_api_updating := ben_prv_shd.api_updating
1459 (p_prtt_rt_val_id => p_prtt_rt_val_id,
1460 p_object_version_number => p_object_version_number);
1461 --
1462 if (l_api_updating
1463 and nvl(p_acty_base_rt_id,-1)
1464 <> nvl(ben_prv_shd.g_old_rec.acty_base_rt_id,hr_api.g_number)
1465 or not l_api_updating) then
1466
1467 if p_acty_base_rt_id is not null then
1468 open c_abr;
1469 fetch c_abr into l_dummy;
1470 l_error := c_abr%notfound;
1471 close c_abr;
1472 else
1473 l_error := true;
1474 end if;
1475 --
1476 if l_error then
1477 fnd_message.set_name('BEN','BEN_91723_NO_ENRT_RT_ABR_FOUND');
1478 fnd_message.set_token('PROC',l_proc);
1479 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
1480 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
1481 fnd_message.raise_error;
1482 end if;
1483 end if;
1484 --
1485 hr_utility.set_location('Leaving:'||l_proc,10);
1486 --
1487 end chk_acty_base_rt_id;
1488 --
1489 /*--Bug#5088571
1490 -- ----------------------------------------------------------------------------
1491 -- |-------------< chk_rt_strt_end_dt >----------|
1492 -- ----------------------------------------------------------------------------
1493 --
1494 -- Description
1495 -- This procedure is used to check whether the Rate Start date is later than Rate End date.
1496 --
1497 procedure chk_rt_strt_end_dt
1498 (p_rt_strt_dt in date,
1499 p_rt_end_dt in date,
1500 p_prtt_enrt_rslt_id in number
1501 ) is
1502 --
1503 l_proc varchar2(72) := g_package||'chk_rt_strt_end_dt';
1504 l_person_id number;
1505 l_message_name varchar2(500) := 'BEN_94592_RT_STRT_GT_END_DT';
1506 --
1507 cursor c_person_id is
1508 select person_id
1509 from ben_prtt_enrt_rslt_f pen
1510 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
1511 --
1512 Begin
1513 --
1514 hr_utility.set_location('Entering:'||l_proc, 5);
1515 --
1516 open c_person_id;
1517 fetch c_person_id into l_person_id;
1518 close c_person_id;
1519 --
1520 if p_rt_strt_dt > p_rt_end_dt then
1521 benutils.write(p_text=>fnd_message.get);
1522 ben_warnings.load_warning
1523 (p_application_short_name => 'BEN'
1524 ,p_message_name => l_message_name
1525 ,p_parma => 'Rate End Date' || ' ' || fnd_date.date_to_displaydate(p_rt_end_dt)
1526 ,p_parmb => 'Rate Start Date' ||' '|| fnd_date.date_to_displaydate(p_rt_strt_dt)
1527 ,p_person_id => l_person_id
1528 );
1529 end if;
1530 --
1531 hr_utility.set_location('Leaving:'||l_proc,10);
1532 --
1533 end chk_rt_strt_end_dt;
1534 --
1535 --Bug#5088571 */
1536 -- ----------------------------------------------------------------------------
1537 -- |---------------------------< insert_validate >----------------------------|
1538 -- ----------------------------------------------------------------------------
1539 Procedure insert_validate(p_rec in ben_prv_shd.g_rec_type,p_effective_date in date ) is
1540
1541 l_proc varchar2(72) := g_package||'insert_validate';
1542 -- p_effective_date date := sysdate;
1543 l_global_pil_rec ben_global_enrt.g_global_pil_rec_type;
1544 l_effective_date date;
1545
1546 Begin
1547 hr_utility.set_location('Entering:'||l_proc, 5);
1548 --
1549 -- Call context sensitive validate bgp cache routine
1550 --
1551 ben_batch_dt_api.batch_validate_bgp_id
1552 (p_business_group_id => p_rec.business_group_id
1553 );
1554 --
1555 /*
1556 --
1557 -- Call all supporting business operations
1558 --
1559 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1560 */
1561
1562 if ben_manage_life_events.fonm = 'Y' then
1563 l_effective_date := ben_manage_life_events.g_fonm_rt_strt_dt;
1564 else
1565 ben_global_enrt.get_pil
1566 (p_per_in_ler_id => p_rec.per_in_ler_id
1567 ,p_global_pil_rec => l_global_pil_rec);
1568 l_effective_date := l_global_pil_rec.lf_evt_ocrd_dt;
1569 end if;
1570
1571 l_effective_date := nvl(l_effective_date,p_effective_date);
1572
1573 chk_prtt_rt_val_id
1574 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1575 p_object_version_number => p_rec.object_version_number);
1576
1577 chk_cmcd_ref_perd_cd
1578 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1579 p_cmcd_ref_perd_cd => p_rec.cmcd_ref_perd_cd,
1580 p_effective_date => l_effective_date,
1581 p_object_version_number => p_rec.object_version_number);
1582
1583 chk_prtt_rt_val_stat_cd
1584 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1585 p_prtt_rt_val_stat_cd => p_rec.prtt_rt_val_stat_cd,
1586 p_effective_date => l_effective_date,
1587 p_object_version_number => p_rec.object_version_number);
1588
1589
1590 chk_bnft_rt_typ_cd
1591 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1592 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
1593 p_effective_date => l_effective_date,
1594 p_object_version_number => p_rec.object_version_number);
1595
1596 chk_acty_ref_perd_cd
1597 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1598 p_acty_ref_perd_cd => p_rec.acty_ref_perd_cd,
1599 p_effective_date => l_effective_date,
1600 p_object_version_number => p_rec.object_version_number);
1601
1602 chk_mlt_cd
1603 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1604 p_mlt_cd => p_rec.mlt_cd,
1605 p_effective_date => l_effective_date,
1606 p_object_version_number => p_rec.object_version_number);
1607
1608 chk_acty_typ_cd
1609 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1610 p_acty_typ_cd => p_rec.acty_typ_cd,
1611 p_effective_date => l_effective_date,
1612 p_object_version_number => p_rec.object_version_number);
1613
1614 chk_tx_typ_cd
1615 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1616 p_tx_typ_cd => p_rec.tx_typ_cd,
1617 p_effective_date => l_effective_date,
1618 p_object_version_number => p_rec.object_version_number);
1619
1620 chk_rt_typ_cd
1621 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1622 p_rt_typ_cd => p_rec.rt_typ_cd,
1623 p_effective_date => l_effective_date,
1624 p_object_version_number => p_rec.object_version_number);
1625
1626 chk_min_max_incrt_val(
1627 p_enrt_rt_id =>p_rec.enrt_rt_id,
1628 p_prtt_rt_val_id =>p_rec.prtt_rt_val_id,
1629 p_rt_val =>p_rec.rt_val,
1630 p_effective_date =>l_effective_date,
1631 p_object_version_number =>p_rec.object_version_number
1632 );
1633
1634 chk_ann_min_max_val
1635 (p_enrt_rt_id => p_rec.enrt_rt_id,
1636 p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1637 p_ann_rt_val => p_rec.ann_rt_val,
1638 p_rt_strt_dt => p_rec.rt_strt_dt,
1639 p_effective_date => l_effective_date,
1640 p_object_version_number => p_rec.object_version_number
1641 );
1642
1643 chk_rt_ovridn_flag
1644 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1645 p_rt_ovridn_flag => p_rec.rt_ovridn_flag,
1646 p_effective_date => l_effective_date,
1647 p_object_version_number => p_rec.object_version_number);
1648
1649 chk_dsply_on_enrt_flag
1650 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1651 p_dsply_on_enrt_flag => p_rec.dsply_on_enrt_flag,
1652 p_effective_date => l_effective_date,
1653 p_object_version_number => p_rec.object_version_number);
1654
1655 chk_acty_base_rt_id
1656 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1657 p_acty_base_rt_id => p_rec.acty_base_rt_id,
1658 p_effective_date => l_effective_date,
1659 p_object_version_number => p_rec.object_version_number);
1660
1661 /* chk_rt_strt_end_dt
1662 (p_rt_strt_dt => p_rec.rt_strt_dt,
1663 p_rt_end_dt => p_rec.rt_end_dt,
1664 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id
1665 );*/
1666 --
1667 hr_utility.set_location(' Leaving:'||l_proc, 10);
1668 End insert_validate;
1669
1670 -- ----------------------------------------------------------------------------
1671 -- |---------------------------< update_validate >----------------------------|
1672 -- ----------------------------------------------------------------------------
1673 Procedure update_validate(p_rec in ben_prv_shd.g_rec_type,p_effective_date in date) is
1674
1675 l_proc varchar2(72) := g_package||'update_validate';
1676 -- p_effective_date date := sysdate;
1677 l_global_pil_rec ben_global_enrt.g_global_pil_rec_type;
1678 l_effective_date date;
1679
1680 Begin
1681 hr_utility.set_location('Entering:'||l_proc, 5);
1682 --
1683 -- Call context sensitive validate bgp cache routine
1684 --
1685 ben_batch_dt_api.batch_validate_bgp_id
1686 (p_business_group_id => p_rec.business_group_id
1687 );
1688 --
1689 /*
1690 --
1691 -- Call all supporting business operations
1692 --
1693 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1694 */
1695 if ben_manage_life_events.fonm = 'Y' then
1696 l_effective_date := ben_manage_life_events.g_fonm_rt_strt_dt;
1697 else
1698 ben_global_enrt.get_pil
1699 (p_per_in_ler_id => p_rec.per_in_ler_id
1700 ,p_global_pil_rec => l_global_pil_rec);
1701 l_effective_date := l_global_pil_rec.lf_evt_ocrd_dt;
1702 end if;
1703
1704 l_effective_date := nvl(l_effective_date,p_effective_date);
1705
1706 chk_prtt_rt_val_id
1707 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1708 p_object_version_number => p_rec.object_version_number);
1709
1710 chk_cmcd_ref_perd_cd
1711 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1712 p_cmcd_ref_perd_cd => p_rec.cmcd_ref_perd_cd,
1713 p_effective_date => l_effective_date,
1714 p_object_version_number => p_rec.object_version_number);
1715
1716 chk_prtt_rt_val_stat_cd
1717 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1718 p_prtt_rt_val_stat_cd => p_rec.prtt_rt_val_stat_cd,
1719 p_effective_date => l_effective_date,
1720 p_object_version_number => p_rec.object_version_number);
1721
1722 chk_bnft_rt_typ_cd
1723 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1724 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
1725 p_effective_date => l_effective_date,
1726 p_object_version_number => p_rec.object_version_number);
1727
1728 chk_acty_ref_perd_cd
1729 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1730 p_acty_ref_perd_cd => p_rec.acty_ref_perd_cd,
1731 p_effective_date => l_effective_date,
1732 p_object_version_number => p_rec.object_version_number);
1733
1734 chk_mlt_cd
1735 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1736 p_mlt_cd => p_rec.mlt_cd,
1737 p_effective_date => l_effective_date,
1738 p_object_version_number => p_rec.object_version_number);
1739
1740 chk_acty_typ_cd
1741 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1742 p_acty_typ_cd => p_rec.acty_typ_cd,
1743 p_effective_date => l_effective_date,
1744 p_object_version_number => p_rec.object_version_number);
1745
1746 chk_tx_typ_cd
1747 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1748 p_tx_typ_cd => p_rec.tx_typ_cd,
1749 p_effective_date => l_effective_date,
1750 p_object_version_number => p_rec.object_version_number);
1751
1752 chk_rt_typ_cd
1753 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1754 p_rt_typ_cd => p_rec.rt_typ_cd,
1755 p_effective_date => l_effective_date,
1756 p_object_version_number => p_rec.object_version_number);
1757
1758 chk_min_max_incrt_val(
1759 p_enrt_rt_id =>p_rec.enrt_rt_id,
1760 p_prtt_rt_val_id =>p_rec.prtt_rt_val_id,
1761 p_rt_val =>p_rec.rt_val,
1762 p_effective_date =>l_effective_date,
1763 p_object_version_number =>p_rec.object_version_number
1764 );
1765
1766 chk_ann_min_max_val
1767 (p_enrt_rt_id => p_rec.enrt_rt_id,
1768 p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1769 p_ann_rt_val => p_rec.ann_rt_val,
1770 p_rt_strt_dt => p_rec.rt_strt_dt,
1771 p_effective_date => l_effective_date,
1772 p_object_version_number => p_rec.object_version_number
1773 );
1774
1775 chk_rt_ovridn_flag
1776 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1777 p_rt_ovridn_flag => p_rec.rt_ovridn_flag,
1778 p_effective_date => l_effective_date,
1779 p_object_version_number => p_rec.object_version_number);
1780
1781 chk_dsply_on_enrt_flag
1782 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1783 p_dsply_on_enrt_flag => p_rec.dsply_on_enrt_flag,
1784 p_effective_date => l_effective_date,
1785 p_object_version_number => p_rec.object_version_number);
1786
1787 chk_acty_base_rt_id
1788 (p_prtt_rt_val_id => p_rec.prtt_rt_val_id,
1789 p_acty_base_rt_id => p_rec.acty_base_rt_id,
1790 p_effective_date => l_effective_date,
1791 p_object_version_number => p_rec.object_version_number);
1792
1793 /* chk_rt_strt_end_dt
1794 (p_rt_strt_dt => p_rec.rt_strt_dt,
1795 p_rt_end_dt => p_rec.rt_end_dt,
1796 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id
1797 );*/
1798 --
1799 hr_utility.set_location(' Leaving:'||l_proc, 10);
1800 End update_validate;
1801
1802 -- ----------------------------------------------------------------------------
1803 -- |---------------------------< delete_validate >----------------------------|
1804 -- ----------------------------------------------------------------------------
1805 Procedure delete_validate(p_rec in ben_prv_shd.g_rec_type) is
1806 --
1807 l_proc varchar2(72) := g_package||'delete_validate';
1808 --
1809 Begin
1810 hr_utility.set_location('Entering:'||l_proc, 5);
1811 --
1812 -- Call all supporting business operations
1813 --
1814 hr_utility.set_location(' Leaving:'||l_proc, 10);
1815 End delete_validate;
1816 --
1817 -- ---------------------------------------------------------------------------
1818 -- |---------------------< return_legislation_code >-------------------------|
1819 -- ---------------------------------------------------------------------------
1820 --
1821 function return_legislation_code
1822 (p_prtt_rt_val_id in number) return varchar2 is
1823 --
1824 -- Declare cursor
1825 --
1826 cursor csr_leg_code is
1827 select a.legislation_code
1828 from per_business_groups a,
1829 ben_prtt_rt_val b
1830 where b.prtt_rt_val_id = p_prtt_rt_val_id
1831 and a.business_group_id = b.business_group_id;
1832 --
1833 -- Declare local variables
1834 --
1835 l_legislation_code varchar2(150);
1836 l_proc varchar2(72) := g_package||'return_legislation_code';
1837 --
1838 begin
1839 --
1840 hr_utility.set_location('Entering:'|| l_proc, 10);
1841 --
1842 -- Ensure that all the mandatory parameter are not null
1843 --
1844 hr_api.mandatory_arg_error(p_api_name => l_proc,
1845 p_argument => 'prtt_rt_val_id',
1846 p_argument_value => p_prtt_rt_val_id);
1847 --
1848 open csr_leg_code;
1849 --
1850 fetch csr_leg_code into l_legislation_code;
1851 --
1852 if csr_leg_code%notfound then
1853 --
1854 close csr_leg_code;
1855 --
1856 -- The primary key is invalid therefore we must error
1857 --
1858 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1859 fnd_message.raise_error;
1860 --
1861 end if;
1862 --
1863 close csr_leg_code;
1864 --
1865 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1866 --
1867 return l_legislation_code;
1868 --
1869 end return_legislation_code;
1870
1871
1872 end ben_prv_bus;