[Home] [Help]
PACKAGE BODY: APPS.BEN_CCM_BUS
Source
1 Package Body ben_ccm_bus as
2 /* $Header: beccmrhi.pkb 120.5 2006/03/22 02:53:46 rgajula noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ccm_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_cvg_amt_calc_mthd_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 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
24 -- effective_date Effective Date of session
25 -- object_version_number Object version number of record being
26 -- inserted or updated.
27 --
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Errors handled by the procedure
33 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_cvg_amt_calc_mthd_id(p_cvg_amt_calc_mthd_id in number,
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_cvg_amt_calc_mthd_id';
42 l_api_updating boolean;
43 --
44 Begin
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 --
48 l_api_updating := ben_ccm_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_cvg_amt_calc_mthd_id,hr_api.g_number)
55 <> ben_ccm_shd.g_old_rec.cvg_amt_calc_mthd_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_ccm_shd.constraint_error('BEN_CVG_AMT_CALC_MTHD_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_cvg_amt_calc_mthd_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_ccm_shd.constraint_error('BEN_CVG_AMT_CALC_MTHD_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_cvg_amt_calc_mthd_id;
78
79 -- ----------------------------------------------------------------------------
80 -- |-------------------------< chk_pen_rows_exist >----------------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- If user wants to NULLIFY a coverage provided through a plan, there is no
84 -- way other than deleting the coverage and re-electing the plan. So we want
85 -- to allow user to end date the coverage. In any case PURGE would not be allowed
86 -- for coverage. If we end-date the coverage, we want to check that the new
87 -- effective end date is not before GREATEST enrollment coverage start date
88 -- in the plan
89 --
90 procedure chk_pen_rows_exist ( p_cvg_amt_calc_mthd_id in number,
91 p_effective_date in date,
92 p_pl_id in number,
93 p_oipl_id in number,
94 p_business_group_id in number,
95 p_datetrack_mode in varchar2 )
96 is
97 --
98 l_dummy varchar2(1) ;
99 l_proc varchar2(72) := g_package || '.chk_pen_rows_exist';
100 l_max_cvg_strt_dt date;
101 l_creation_date date;
102 --
103 cursor c_max_cvg_strt_dt
104 is
105 select max(pen.ENRT_CVG_STRT_DT)
106 from ben_prtt_enrt_rslt_f pen
107 where ( pl_id = nvl(p_pl_id, -9999) OR
108 oipl_id = nvl(p_oipl_id, -9999)
109 )
110 and pen.prtt_enrt_rslt_stat_cd is null
111 and pen.business_group_id = p_business_group_id
112 and pen.bnft_amt is not null
113 and (pen.creation_date) >= (l_creation_date);
114 --
115 cursor c_creation_date
116 is
117 select min(creation_date)
118 from ben_cvg_amt_calc_mthd_F
119 where cvg_amt_calc_mthd_id = p_cvg_amt_calc_mthd_id;
120 --
121 begin
122 --
123 hr_utility.set_location('Entering:'||l_proc, 5);
124 --
125 open c_creation_date;
126 --
127 fetch c_creation_date into l_creation_date;
128 --
129 close c_creation_date;
130 --
131 if l_creation_date is not null
132 then
133 --
134 open c_max_cvg_strt_dt;
135 --
136 fetch c_max_cvg_strt_dt into l_max_cvg_strt_dt;
137 --
138 if l_max_cvg_strt_dt is not null
139 then
140 --
141 hr_utility.set_location('l_max_cvg_strt_dt = ' || l_max_cvg_strt_dt, 8888);
142 if p_datetrack_mode = 'ZAP'
143 then
144 --
145 close c_max_cvg_strt_dt;
146 --
147 --Bug 5109636 : Change of token from END_DATE to START_DATE
148 fnd_message.set_name('BEN', 'BEN_94992_PEN_ROWS_EXIST');
149 fnd_message.set_token('START_DATE', fnd_date.date_to_displaydate(l_max_cvg_strt_dt));
150 fnd_message.raise_error;
151 --
152 elsif p_datetrack_mode = 'DELETE' AND
153 p_effective_date < l_max_cvg_strt_dt
154 then
155 --
156 close c_max_cvg_strt_dt;
157 --
158 --Bug 5109636 : Change of token from END_DATE to START_DATE
159 fnd_message.set_name('BEN', 'BEN_94993_DT_PEN_ROWS_EXIST');
160 fnd_message.set_token('START_DATE', fnd_date.date_to_displaydate(l_max_cvg_strt_dt));
161 fnd_message.raise_error;
162 --
163 end if;
164 --
165 end if;
166 --
167 close c_max_cvg_strt_dt;
168 --
169 end if;
170 --
171 hr_utility.set_location('Leaving:'||l_proc, 10);
172 --
173 end chk_pen_rows_exist;
174
175 -- ----------------------------------------------------------------------------
176 -- |------< chk_entr_at_enrt_with_rate >------|
177 -- ----------------------------------------------------------------------------
178 --
179 -- Description
180 --
181 --
182 --
183 -- when the coverage defined as enter rate at entrollment on , this should be validated against
184 -- standard rate ,if the rate is defined for the same plan or plop with muliple coverage and
185 -- enter and entrolment on then error is to be thrown
186
187
188 Procedure chk_entr_at_enrt_with_rate( p_entr_val_at_enrt_flag in varchar2,
189 p_pl_id in number,
190 p_oipl_id in number,
191 p_effective_date in date ) is
192
193 l_dummy varchar2(1) ;
194 l_proc varchar2(72) := g_package||'chk_entr_at_enrt_with_rate';
195
196 cursor c_pl is select 'x' from
197 ben_acty_base_rt_f where pl_id = p_pl_id and
198 rt_mlt_cd = 'CVG' and entr_val_at_enrt_flag = 'Y' and
199 p_effective_date between effective_start_date and effective_end_date ;
200
201 cursor c_plip is select 'x' from ben_acty_base_rt_f a , ben_plip_f b where
202 a.plip_id = b.plip_id and a.rt_mlt_cd = 'CVG' and a.entr_val_at_enrt_flag = 'Y' and
203 p_effective_date between a.effective_start_date and a.effective_end_date and
204 b.pl_id = p_pl_id and p_effective_date between b.effective_start_date and b.effective_end_date;
205
206 cursor c_oipl is select 'x' from
207 ben_acty_base_rt_f where oipl_id = p_oipl_id and
208 rt_mlt_cd = 'CVG' and entr_val_at_enrt_flag = 'Y' and
209 p_effective_date between effective_start_date and effective_end_date ;
210
211 cursor c_oiplip is select 'x' from
212 ben_acty_base_rt_f a ,ben_oiplip_f b where a.oiplip_id = b.oiplip_id and
213 a.rt_mlt_cd = 'CVG' and a.entr_val_at_enrt_flag = 'Y' and
214 p_effective_date between a.effective_start_date and a.effective_end_date and
215 b.oipl_id = p_oipl_id and p_effective_date between b.effective_start_date and b.effective_end_date ;
216
217 begin
218 hr_utility.set_location('Entering:'||l_proc, 5);
219 if p_entr_val_at_enrt_flag = 'Y' then
220 -- decide the level - plan
221 If p_pl_id is not null then
222 -- plan level check in rate
223 open c_pl ;
224 fetch c_pl into l_dummy ;
225 If c_pl%notfound then
226 -- plan in program level check in rate
227 open c_plip ;
228 fetch c_plip into l_dummy ;
229 close c_plip ;
230 end if ;
231 close c_pl ;
232 -- whne the any row found then throw the error
233 if l_dummy is not null then
234 fnd_message.set_name('BEN','BEN_92653_ENTR_VAL_RATE_CVG');
235 fnd_message.raise_error;
236 end if;
237 elsif p_oipl_id is not null then
238 -- plan in option levele
239 -- check option in plan in rate
240 open c_oipl ;
241 fetch c_oipl into l_dummy ;
242 If c_oipl%notfound then
243 -- check in option in plan in program in rate
244 open c_oiplip ;
245 fetch c_oiplip into l_dummy ;
246 close c_oiplip ;
247 end if ;
248 close c_oipl ;
249 if l_dummy is not null then
250 fnd_message.set_name('BEN','BEN_92653_ENTR_VAL_RATE_CVG');
251 fnd_message.raise_error;
252 end if ;
253 else
254 --if any other level added
255 --if any other level added
256 null;
257 end if ;
258 end if ;
259 hr_utility.set_location('Leaving:'||l_proc,10);
260 end chk_entr_at_enrt_with_rate;
261 --
262 -- ----------------------------------------------------------------------------
263 -- |------< chk_comp_lvl_fctr_id >------|
264 -- ----------------------------------------------------------------------------
265 --
266 -- Description
267 -- This procedure checks that a referenced foreign key actually exists
268 -- in the referenced table.
269 --
270 -- Pre-Conditions
271 -- None.
272 --
273 -- In Parameters
274 -- p_cvg_amt_calc_mthd_id PK
275 -- p_comp_lvl_fctr_id ID of FK column
276 -- p_effective_date session date
277 -- p_object_version_number object version number
278 --
279 -- Post Success
280 -- Processing continues
281 --
282 -- Post Failure
283 -- Error raised.
284 --
285 -- Access Status
286 -- Internal table handler use only.
287 --
288 Procedure chk_comp_lvl_fctr_id (p_cvg_amt_calc_mthd_id in number,
289 p_comp_lvl_fctr_id in number,
290 p_effective_date in date,
291 p_object_version_number in number) is
292 --
293 l_proc varchar2(72) := g_package||'chk_comp_lvl_fctr_id';
294 l_api_updating boolean;
295 l_dummy varchar2(1);
296 --
297 cursor c1 is
298 select null
299 from ben_comp_lvl_fctr a
300 where a.comp_lvl_fctr_id = p_comp_lvl_fctr_id;
301 --
302 Begin
303 --
304 hr_utility.set_location('Entering:'||l_proc,5);
305 --
306 l_api_updating := ben_ccm_shd.api_updating
307 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
308 p_effective_date => p_effective_date,
309 p_object_version_number => p_object_version_number);
310 --
311 if (l_api_updating
312 and nvl(p_comp_lvl_fctr_id,hr_api.g_number)
313 <> nvl(ben_ccm_shd.g_old_rec.comp_lvl_fctr_id,hr_api.g_number)
314 or not l_api_updating) and
315 p_comp_lvl_fctr_id is not null then
316 --
317 -- check if comp_lvl_fctr_id value exists in ben_comp_lvl_fctr table
318 --
319 open c1;
320 --
321 fetch c1 into l_dummy;
322 if c1%notfound then
323 --
324 close c1;
325 --
326 -- raise error as FK does not relate to PK in ben_comp_lvl_fctr
327 -- table.
328 --
329 ben_ccm_shd.constraint_error('BEN_CVG_AMT_CALC_MTHD_FK2');
330 --
331 end if;
332 --
333 close c1;
334 --
335 end if;
336 --
337 hr_utility.set_location('Leaving:'||l_proc,10);
338 --
339 End chk_comp_lvl_fctr_id;
340 --
341 -- ----------------------------------------------------------------------------
342 -- |----------------------------< chk_only_one_fk >---------------------------|
343 -- ----------------------------------------------------------------------------
344 --
345 -- Description
346 -- This procedure is used to check that the form stores either the plan id
347 -- or the option in plan id, or the plan in program id, only one.
348 --
349 -- Pre Conditions
350 -- None.
351 --
352 -- In Parameters
353 -- pl_id Plan ID
354 -- oipl_id Option In Plan ID
355 --
356 --
357 -- Post Success
358 -- Processing continues
359 --
360 -- Post Failure
361 -- Errors handled by the procedure
362 --
363 -- Access Status
364 -- Internal table handler use only.
365 --
366 Procedure chk_only_one_fk
367 (p_pl_id in number,
368 p_oipl_id in number,
369 p_plip_id in number) is
370 --
371 l_proc varchar2(72) := g_package||'chk_only_one_fk';
372 --
373 function count_them(p_id in number) return number is
374 --
375 begin
376 --
377 if p_id is not null then
378 --
379 return 1;
380 --
381 else
382 --
383 return 0;
384 --
385 end if;
386 --
387 end count_them;
388 --
389 Begin
390 --
391 hr_utility.set_location('Entering:'||l_proc, 5);
392 --
393 if count_them(p_id => p_pl_id)+
394 count_them(p_id => p_oipl_id)+
395 count_them(p_id => p_plip_id) <> 1 then
396 --
397 -- raise error if both arguments are not null
398 --
399 fnd_message.set_name('BEN','BEN_92462_ONE_FK_ONLY');
400 fnd_message.raise_error;
401 --
402 end if;
403 --
404 hr_utility.set_location('Leaving:'||l_proc, 10);
405 --
406 End chk_only_one_fk;
407 --
408 -- ----------------------------------------------------------------------------
409 -- |------< chk_rt_typ_cd >------|
410 -- ----------------------------------------------------------------------------
411 --
412 -- Description
413 -- This procedure is used to check that the lookup value is valid.
414 --
415 -- Pre Conditions
416 -- None.
417 --
418 -- In Parameters
419 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
420 -- rt_typ_cd Value of lookup code.
421 -- effective_date effective date
422 -- object_version_number Object version number of record being
423 -- inserted or updated.
424 --
425 -- Post Success
426 -- Processing continues
427 --
428 -- Post Failure
429 -- Error handled by procedure
430 --
431 -- Access Status
432 -- Internal table handler use only.
433 --
434 Procedure chk_rt_typ_cd(p_cvg_amt_calc_mthd_id in number,
438 --
435 p_rt_typ_cd in varchar2,
436 p_effective_date in date,
437 p_object_version_number in number) is
439 l_proc varchar2(72) := g_package||'chk_rt_typ_cd';
440 l_api_updating boolean;
441 --
442 Begin
443 --
444 hr_utility.set_location('Entering:'||l_proc, 5);
445 --
446 l_api_updating := ben_ccm_shd.api_updating
447 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
448 p_effective_date => p_effective_date,
449 p_object_version_number => p_object_version_number);
450 --
451 if (l_api_updating
452 and p_rt_typ_cd
453 <> nvl(ben_ccm_shd.g_old_rec.rt_typ_cd,hr_api.g_varchar2)
454 or not l_api_updating)
455 and p_rt_typ_cd is not null then
456 --
457 -- check if value of lookup falls within lookup type.
458 --
459 if hr_api.not_exists_in_hr_lookups
460 (p_lookup_type => 'BEN_RT_TYP',
461 p_lookup_code => p_rt_typ_cd,
462 p_effective_date => p_effective_date) then
463 --
464 -- raise error as does not exist as lookup
465 --
466 hr_utility.set_message(801,'RT_TYP_DOES_NOT_EXIST');
467 hr_utility.raise_error;
468 --
469 end if;
470 --
471 end if;
472 --
473 hr_utility.set_location('Leaving:'||l_proc,10);
474 --
475 end chk_rt_typ_cd;
476 --
477 -------------------------------------------------------------------------------
478 -- |------< chk_entr_val_at_enrt_flag >------|
479 -- ----------------------------------------------------------------------------
480 --
481 -- Description
482 -- This procedure is used to check that the lookup value is valid.
483 --
484 -- Pre Conditions
485 -- None.
486 --
487 -- In Parameters
488 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
489 -- entr_val_at_enrt_flag Value of lookup code.
490 -- effective_date effective date
491 -- object_version_number Object version number of record being
492 -- inserted or updated.
493 --
494 -- Post Success
495 -- Processing continues
496 --
497 -- Post Failure
498 -- Error handled by procedure
499 --
500 -- Access Status
501 -- Internal table handler use only.
502 --
503 Procedure chk_entr_val_at_enrt_flag(p_cvg_amt_calc_mthd_id in number,
504 p_entr_val_at_enrt_flag in varchar2,
505 p_effective_date in date,
506 p_object_version_number in number) is
507 --
508 l_proc varchar2(72) := g_package||'chk_entr_val_at_enrt_flag';
509 l_api_updating boolean;
510 --
511 Begin
512 --
513 hr_utility.set_location('Entering:'||l_proc, 5);
514 --
515 l_api_updating := ben_ccm_shd.api_updating
516 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
517 p_effective_date => p_effective_date,
518 p_object_version_number => p_object_version_number);
519 --
520 if (l_api_updating
521 and p_entr_val_at_enrt_flag
522 <> nvl(ben_ccm_shd.g_old_rec.entr_val_at_enrt_flag,hr_api.g_varchar2)
523 or not l_api_updating) then
524 --
525 -- check if value of lookup falls within lookup type.
526 --
527 --
528 if hr_api.not_exists_in_hr_lookups
529 (p_lookup_type => 'YES_NO',
530 p_lookup_code => p_entr_val_at_enrt_flag,
531 p_effective_date => p_effective_date) then
532 --
533 -- raise error as does not exist as lookup
534 --
535 fnd_message.set_name('BEN','BEN_91006_INVALID_FLAG');
536 fnd_message.raise_error;
537 --
538 end if;
539 --
540 end if;
541 --
542 hr_utility.set_location('Leaving:'||l_proc,10);
543 --
544 end chk_entr_val_at_enrt_flag;
545 --
546 ------------------------------------------------------------------------------
547 -- |------< chk_dflt_flag >------|
548 -- ----------------------------------------------------------------------------
549 --
550 -- Description
551 -- This procedure is used to check that the lookup value is valid.
552 --
553 -- Pre Conditions
554 -- None.
555 --
556 -- In Parameters
557 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
558 -- dflt_flag Value of lookup code.
559 -- effective_date effective date
560 -- object_version_number Object version number of record being
561 -- inserted or updated.
562 --
563 -- Post Success
564 -- Processing continues
565 --
566 -- Post Failure
567 -- Error handled by procedure
568 --
569 -- Access Status
570 -- Internal table handler use only.
571 --
572 Procedure chk_dflt_flag(p_cvg_amt_calc_mthd_id in number,
573 p_dflt_flag in varchar2,
574 p_effective_date in date,
575 p_object_version_number in number) is
576 --
580 Begin
577 l_proc varchar2(72) := g_package||'chk_dflt_flag';
578 l_api_updating boolean;
579 --
581 --
582 hr_utility.set_location('Entering:'||l_proc, 5);
583 --
584 l_api_updating := ben_ccm_shd.api_updating
585 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
586 p_effective_date => p_effective_date,
587 p_object_version_number => p_object_version_number);
588 --
589 if (l_api_updating
590 and p_dflt_flag
591 <> nvl(ben_ccm_shd.g_old_rec.dflt_flag,hr_api.g_varchar2)
592 or not l_api_updating) then
593 --
594 -- check if value of lookup falls within lookup type.
595 --
596 --
597 if hr_api.not_exists_in_hr_lookups
598 (p_lookup_type => 'YES_NO',
599 p_lookup_code => p_dflt_flag,
600 p_effective_date => p_effective_date) then
601 --
602 -- raise error as does not exist as lookup
603 --
604 fnd_message.set_name('BEN','BEN_91210_INVLD_DFLT_FLAG');
605 fnd_message.raise_error;
606 --
607 end if;
608 --
609 end if;
610 --
611 hr_utility.set_location('Leaving:'||l_proc,10);
612 --
613 end chk_dflt_flag;
614 --
615 -- ----------------------------------------------------------------------------
616 -- |------< chk_cvg_mlt_cd >------|
617 -- ----------------------------------------------------------------------------
618 --
619 -- Description
620 -- This procedure is used to check that the lookup value is valid.
621 --
622 -- Pre Conditions
623 -- None.
624 --
625 -- In Parameters
626 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
627 -- cvg_mlt_cd Value of lookup code.
628 -- effective_date effective date
629 -- object_version_number Object version number of record being
630 -- inserted or updated.
631 --
632 -- Post Success
633 -- Processing continues
634 --
635 -- Post Failure
636 -- Error handled by procedure
637 --
638 -- Access Status
639 -- Internal table handler use only.
640 --
641 Procedure chk_cvg_mlt_cd(p_cvg_amt_calc_mthd_id in number,
642 p_cvg_mlt_cd in varchar2,
643 p_effective_date in date,
644 p_object_version_number in number) is
645 --
646 l_proc varchar2(72) := g_package||'chk_cvg_mlt_cd';
647 l_api_updating boolean;
648 --
649 Begin
650 --
651 hr_utility.set_location('Entering:'||l_proc, 5);
652 --
653 l_api_updating := ben_ccm_shd.api_updating
654 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
655 p_effective_date => p_effective_date,
656 p_object_version_number => p_object_version_number);
657 --
658 if (l_api_updating
659 and p_cvg_mlt_cd
660 <> nvl(ben_ccm_shd.g_old_rec.cvg_mlt_cd,hr_api.g_varchar2)
661 or not l_api_updating)
662 and p_cvg_mlt_cd is not null then
663 --
664 -- check if value of lookup falls within lookup type.
665 --
666 if hr_api.not_exists_in_hr_lookups
667 (p_lookup_type => 'BEN_CVG_MLT',
668 p_lookup_code => p_cvg_mlt_cd,
669 p_effective_date => p_effective_date) then
670 --
671 -- raise error as does not exist as lookup
672 --
673 hr_utility.set_message(801,'CVG_MLT_DOES_NOT_EXIST');
674 hr_utility.raise_error;
675 --
676 end if;
677 --
678 end if;
679 --
680 hr_utility.set_location('Leaving:'||l_proc,10);
681 --
682 end chk_cvg_mlt_cd;
683 --
684 -- ----------------------------------------------------------------------------
685 -- |------< chk_bnft_typ_cd >------|
686 -- ----------------------------------------------------------------------------
687 --
688 -- Description
689 -- This procedure is used to check that the lookup value is valid.
690 --
691 -- Pre Conditions
692 -- None.
693 --
694 -- In Parameters
695 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
696 -- bnft_typ_cd Value of lookup code.
697 -- effective_date effective date
698 -- object_version_number Object version number of record being
699 -- inserted or updated.
700 --
701 -- Post Success
702 -- Processing continues
703 --
704 -- Post Failure
705 -- Error handled by procedure
706 --
707 -- Access Status
708 -- Internal table handler use only.
709 --
710 Procedure chk_bnft_typ_cd(p_cvg_amt_calc_mthd_id in number,
711 p_bnft_typ_cd in varchar2,
712 p_effective_date in date,
713 p_object_version_number in number) is
714 --
715 l_proc varchar2(72) := g_package||'chk_bnft_typ_cd';
716 l_api_updating boolean;
717 --
718 Begin
719 --
720 hr_utility.set_location('Entering:'||l_proc, 5);
721 --
722 l_api_updating := ben_ccm_shd.api_updating
723 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
727 if (l_api_updating
724 p_effective_date => p_effective_date,
725 p_object_version_number => p_object_version_number);
726 --
728 and p_bnft_typ_cd
729 <> nvl(ben_ccm_shd.g_old_rec.bnft_typ_cd,hr_api.g_varchar2)
730 or not l_api_updating)
731 and p_bnft_typ_cd is not null then
732 --
733 -- check if value of lookup falls within lookup type.
734 --
735 if hr_api.not_exists_in_hr_lookups
736 (p_lookup_type => 'BEN_BNFT_TYP',
737 p_lookup_code => p_bnft_typ_cd,
738 p_effective_date => p_effective_date) then
739 --
740 -- raise error as does not exist as lookup
741 --
742 hr_utility.set_message(801,'BNFT_TYP_DOES_NOT_EXIST');
743 hr_utility.raise_error;
744 --
745 end if;
746 --
747 end if;
748 --
749 hr_utility.set_location('Leaving:'||l_proc,10);
750 --
751 end chk_bnft_typ_cd;
752 --
753 -- ----------------------------------------------------------------------------
754 -- |------< chk_bndry_perd_cd >------|
755 -- ----------------------------------------------------------------------------
756 --
757 -- Description
758 -- This procedure is used to check that the lookup value is valid.
759 --
760 -- Pre Conditions
761 -- None.
762 --
763 -- In Parameters
764 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
765 -- bndry_perd_cd Value of lookup code.
766 -- effective_date effective date
767 -- object_version_number Object version number of record being
768 -- inserted or updated.
769 --
770 -- Post Success
771 -- Processing continues
772 --
773 -- Post Failure
774 -- Error handled by procedure
775 --
776 -- Access Status
777 -- Internal table handler use only.
778 --
779 Procedure chk_bndry_perd_cd(p_cvg_amt_calc_mthd_id in number,
780 p_bndry_perd_cd in varchar2,
781 p_effective_date in date,
782 p_object_version_number in number) is
783 --
784 l_proc varchar2(72) := g_package||'chk_bndry_perd_cd';
785 l_api_updating boolean;
786 --
787 Begin
788 --
789 hr_utility.set_location('Entering:'||l_proc, 5);
790 --
791 l_api_updating := ben_ccm_shd.api_updating
792 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
793 p_effective_date => p_effective_date,
794 p_object_version_number => p_object_version_number);
795 --
796 if (l_api_updating
797 and p_bndry_perd_cd
798 <> nvl(ben_ccm_shd.g_old_rec.bndry_perd_cd,hr_api.g_varchar2)
799 or not l_api_updating)
800 and p_bndry_perd_cd is not null then
801 --
802 -- check if value of lookup falls within lookup type.
803 --
804 if hr_api.not_exists_in_hr_lookups
805 (p_lookup_type => 'BEN_BNDRY_PERD',
806 p_lookup_code => p_bndry_perd_cd,
807 p_effective_date => p_effective_date) then
808 --
809 -- raise error as does not exist as lookup
810 --
811 hr_utility.set_message(801,'BNDRY_PD_DOES_NOT_EXIST');
812 hr_utility.raise_error;
813 --
814 end if;
815 --
816 end if;
817 --
818 hr_utility.set_location('Leaving:'||l_proc,10);
819 --
820 end chk_bndry_perd_cd;
821 --
822 -- ----------------------------------------------------------------------------
823 -- |------< chk_nnmntry_uom >------|
824 -- ----------------------------------------------------------------------------
825 --
826 -- Description
827 -- This procedure is used to check that the lookup value is valid.
828 --
829 -- Pre Conditions
830 -- None.
831 --
832 -- In Parameters
833 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
834 -- nnmntry_uom Value of lookup code.
835 -- effective_date effective date
836 -- object_version_number Object version number of record being
837 -- inserted or updated.
838 --
839 -- Post Success
840 -- Processing continues
841 --
842 -- Post Failure
843 -- Error handled by procedure
844 --
845 -- Access Status
846 -- Internal table handler use only.
847 --
848 Procedure chk_nnmntry_uom(p_cvg_amt_calc_mthd_id in number,
849 p_nnmntry_uom in varchar2,
850 p_effective_date in date,
851 p_object_version_number in number) is
852 --
853 l_proc varchar2(72) := g_package||'chk_nnmntry_uom';
854 l_api_updating boolean;
855 --
856 Begin
857 --
858 hr_utility.set_location('Entering:'||l_proc, 5);
859 --
860 l_api_updating := ben_ccm_shd.api_updating
861 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
862 p_effective_date => p_effective_date,
863 p_object_version_number => p_object_version_number);
864 --
865 if (l_api_updating
866 and p_nnmntry_uom
867 <> nvl(ben_ccm_shd.g_old_rec.nnmntry_uom,hr_api.g_varchar2)
871 -- check if value of lookup falls within lookup type.
868 or not l_api_updating)
869 and p_nnmntry_uom is not null then
870 --
872 --
873 if hr_api.not_exists_in_hr_lookups
874 (p_lookup_type => 'BEN_NNMNTRY_UOM',
875 p_lookup_code => p_nnmntry_uom,
876 p_effective_date => p_effective_date) then
877 --
878 -- raise error as does not exist as lookup
879 --
880 hr_utility.set_message(801,'NNMNTRY_UOM_DOES_NOT_EXIST');
881 hr_utility.raise_error;
882 --
883 end if;
884 --
885 end if;
886 --
887 hr_utility.set_location('Leaving:'||l_proc,10);
888 --
889 end chk_nnmntry_uom;
890 --
891 -- ----------------------------------------------------------------------------
892 -- |------< chk_val_calc_rl >------|
893 -- ----------------------------------------------------------------------------
894 --
895 -- Description
896 -- This procedure is used to check that the Formula Rule is valid.
897 --
898 -- Pre Conditions
899 -- None.
900 --
901 -- In Parameters
902 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
903 -- val_calc_rl Value of formula rule id.
904 -- effective_date effective date
905 -- object_version_number Object version number of record being
906 -- inserted or updated.
907 --
908 -- Post Success
909 -- Processing continues
910 --
911 -- Post Failure
912 -- Error handled by procedure
913 --
914 -- Access Status
915 -- Internal table handler use only.
916 --
917 Procedure chk_val_calc_rl(p_cvg_amt_calc_mthd_id in number,
918 p_val_calc_rl in number,
919 p_business_group_id in number,
920 p_effective_date in date,
921 p_object_version_number in number) is
922 --
923 l_proc varchar2(72) := g_package||'chk_val_calc_rl';
924 l_api_updating boolean;
925 l_dummy varchar2(1);
926 --
927 cursor c1 is
928 select null
929 from ff_formulas_f ff
930 ,per_business_groups pbg
931 where ff.formula_id = p_val_calc_rl
932 and ff.formula_type_id = -49
933 and pbg.business_group_id = p_business_group_id
934 and nvl(ff.business_group_id, p_business_group_id) =
935 p_business_group_id
936 and nvl(ff.legislation_code, pbg.legislation_code) =
937 pbg.legislation_code
938 and p_effective_date
939 between ff.effective_start_date
940 and ff.effective_end_date;
941 --
942 Begin
943 --
944 hr_utility.set_location('Entering:'||l_proc, 5);
945 --
946 l_api_updating := ben_ccm_shd.api_updating
947 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
948 p_effective_date => p_effective_date,
949 p_object_version_number => p_object_version_number);
950 --
951 if (l_api_updating
952 and nvl(p_val_calc_rl,hr_api.g_number)
953 <> ben_ccm_shd.g_old_rec.val_calc_rl
954 or not l_api_updating)
955 and p_val_calc_rl is not null then
956 --
957 -- check if value of formula rule is valid.
958 --
959 open c1;
960 --
961 -- fetch value from cursor if it returns a record then the
962 -- formula is valid otherwise its invalid
963 --
964 fetch c1 into l_dummy;
965 if c1%notfound then
966 --
967 close c1;
968 --
969 -- raise error
970 --
971 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
972 fnd_message.set_token('ID',p_val_calc_rl);
973 fnd_message.set_token('TYPE_ID',-49);
974 fnd_message.raise_error;
975 --
976 end if;
977 --
978 close c1;
979 --
980 end if;
981 --
982 hr_utility.set_location('Leaving:'||l_proc,10);
983 --
984 end chk_val_calc_rl;
985 --
986 -- ----------------------------------------------------------------------------
987 -- |------< chk_val_ovrid_alwd_flag >------|
988 -- ----------------------------------------------------------------------------
989 --
990 -- Description
991 -- This procedure is used to check that the lookup value is valid.
992 --
993 -- Pre Conditions
994 -- None.
995 --
996 -- In Parameters
997 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
998 -- val_ovrid_alwd_flag Value of lookup code.
999 -- effective_date effective date
1000 -- object_version_number Object version number of record being
1001 -- inserted or updated.
1002 --
1003 -- Post Success
1004 -- Processing continues
1005 --
1006 -- Post Failure
1007 -- Error handled by procedure
1008 --
1009 -- Access Status
1010 -- Internal table handler use only.
1011 --
1012 Procedure chk_val_ovrid_alwd_flag(p_cvg_amt_calc_mthd_id in number,
1013 p_val_ovrid_alwd_flag in varchar2,
1014 p_effective_date in date,
1018 l_api_updating boolean;
1015 p_object_version_number in number) is
1016 --
1017 l_proc varchar2(72) := g_package||'chk_val_ovrid_alwd_flag';
1019 --
1020 Begin
1021 --
1022 hr_utility.set_location('Entering:'||l_proc, 5);
1023 --
1024 l_api_updating := ben_ccm_shd.api_updating
1025 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
1026 p_effective_date => p_effective_date,
1027 p_object_version_number => p_object_version_number);
1028 --
1029 if (l_api_updating
1030 and p_val_ovrid_alwd_flag
1031 <> nvl(ben_ccm_shd.g_old_rec.val_ovrid_alwd_flag,hr_api.g_varchar2)
1032 or not l_api_updating) then
1033 --
1034 -- check if value of lookup falls within lookup type.
1035 --
1036 --
1037 if hr_api.not_exists_in_hr_lookups
1038 (p_lookup_type => 'YES_NO',
1039 p_lookup_code => p_val_ovrid_alwd_flag,
1040 p_effective_date => p_effective_date) then
1041 --
1042 -- raise error as does not exist as lookup
1043 --
1044 hr_utility.set_message(801,'VAL_OVRD_FLG_DOES_NOT_EXIST');
1045 hr_utility.raise_error;
1046 --
1047 end if;
1048 --
1049 end if;
1050 --
1051 hr_utility.set_location('Leaving:'||l_proc,10);
1052 --
1053 end chk_val_ovrid_alwd_flag;
1054 --
1055 -- ----------------------------------------------------------------------------
1056 -- |------< chk_rndg_rl >------|
1057 -- ----------------------------------------------------------------------------
1058 --
1059 -- Description
1060 -- This procedure is used to check that the Formula Rule is valid.
1061 --
1062 -- Pre Conditions
1063 -- None.
1064 --
1065 -- In Parameters
1066 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
1067 -- rndg_rl Value of formula rule id.
1068 -- effective_date effective date
1069 -- object_version_number Object version number of record being
1070 -- inserted or updated.
1071 --
1072 -- Post Success
1073 -- Processing continues
1074 --
1075 -- Post Failure
1076 -- Error handled by procedure
1077 --
1078 -- Access Status
1079 -- Internal table handler use only.
1080 --
1081 Procedure chk_rndg_rl(p_cvg_amt_calc_mthd_id in number,
1082 p_rndg_rl in number,
1083 p_business_group_id in number,
1084 p_effective_date in date,
1085 p_object_version_number in number) is
1086 --
1087 l_proc varchar2(72) := g_package||'chk_rndg_rl';
1088 l_api_updating boolean;
1089 l_dummy varchar2(1);
1090 --
1091 cursor c1 is
1092 select null
1093 from ff_formulas_f ff
1094 ,per_business_groups pbg
1095 where ff.formula_id = p_rndg_rl
1096 and ff.formula_type_id = -169
1097 and pbg.business_group_id = p_business_group_id
1098 and nvl(ff.business_group_id, p_business_group_id) =
1099 p_business_group_id
1100 and nvl(ff.legislation_code, pbg.legislation_code) =
1101 pbg.legislation_code
1102 and p_effective_date
1103 between ff.effective_start_date
1104 and ff.effective_end_date;
1105 --
1106 Begin
1107 --
1108 hr_utility.set_location('Entering:'||l_proc, 5);
1109 --
1110 l_api_updating := ben_ccm_shd.api_updating
1111 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
1112 p_effective_date => p_effective_date,
1113 p_object_version_number => p_object_version_number);
1114 --
1115 if (l_api_updating
1116 and nvl(p_rndg_rl,hr_api.g_number)
1117 <> ben_ccm_shd.g_old_rec.rndg_rl
1118 or not l_api_updating)
1119 and p_rndg_rl is not null then
1120 --
1121 -- check if value of formula rule is valid.
1122 --
1123 open c1;
1124 --
1125 -- fetch value from cursor if it returns a record then the
1126 -- formula is valid otherwise its invalid
1127 --
1128 fetch c1 into l_dummy;
1129 if c1%notfound then
1130 --
1131 close c1;
1132 --
1133 -- raise error
1134 --
1135 fnd_message.set_name('BEN','BEN_91042_INVALID_RNDG_RL');
1136 fnd_message.raise_error;
1137 --
1138 end if;
1139 --
1140 close c1;
1141 --
1142 end if;
1143 --
1144 hr_utility.set_location('Leaving:'||l_proc,10);
1145 --
1146 end chk_rndg_rl;
1147 --
1148 -- ----------------------------------------------------------------------------
1149 -- |------< chk_lwr_lmt_calc_rl >------|
1150 -- ----------------------------------------------------------------------------
1151 --
1152 -- Description
1153 -- This procedure is used to check that the Formula Rule is valid.
1154 --
1155 -- Pre Conditions
1156 -- None.
1157 --
1158 -- In Parameters
1159 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
1160 -- lwr_lmt_calc_rl Value of formula rule id.
1161 -- effective_date effective date
1165 -- Post Success
1162 -- object_version_number Object version number of record being
1163 -- inserted or updated.
1164 --
1166 -- Processing continues
1167 --
1168 -- Post Failure
1169 -- Error handled by procedure
1170 --
1171 -- Access Status
1172 -- Internal table handler use only.
1173 --
1174 Procedure chk_lwr_lmt_calc_rl(p_cvg_amt_calc_mthd_id in number,
1175 p_lwr_lmt_calc_rl in number,
1176 p_business_group_id in number,
1177 p_effective_date in date,
1178 p_object_version_number in number) is
1179 --
1180 l_proc varchar2(72) := g_package||'chk_lwr_lmt_calc_rl';
1181 l_api_updating boolean;
1182 l_dummy varchar2(1);
1183 --
1184 cursor c1 is
1185 select null
1186 from ff_formulas_f ff
1187 ,per_business_groups pbg
1188 where ff.formula_id = p_lwr_lmt_calc_rl
1189 and ff.formula_type_id = -511
1190 and pbg.business_group_id = p_business_group_id
1191 and nvl(ff.business_group_id, p_business_group_id) =
1192 p_business_group_id
1193 and nvl(ff.legislation_code, pbg.legislation_code) =
1194 pbg.legislation_code
1195 and p_effective_date
1196 between ff.effective_start_date
1197 and ff.effective_end_date;
1198 --
1199 Begin
1200 --
1201 hr_utility.set_location('Entering:'||l_proc, 5);
1202 --
1203 l_api_updating := ben_ccm_shd.api_updating
1204 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
1205 p_effective_date => p_effective_date,
1206 p_object_version_number => p_object_version_number);
1207 --
1208 if (l_api_updating
1209 and nvl(p_lwr_lmt_calc_rl,hr_api.g_number)
1210 <> ben_ccm_shd.g_old_rec.lwr_lmt_calc_rl
1211 or not l_api_updating)
1212 and p_lwr_lmt_calc_rl is not null then
1213 --
1214 -- check if value of formula rule is valid.
1215 --
1216 open c1;
1217 --
1218 -- fetch value from cursor if it returns a record then the
1219 -- formula is valid otherwise its invalid
1220 --
1221 fetch c1 into l_dummy;
1222 if c1%notfound then
1223 --
1224 close c1;
1225 --
1226 -- raise error
1227 --
1228 hr_utility.set_message('BEN','BEN_91815_INVALID_LWR_LMT_RL');
1229 hr_utility.raise_error;
1230 --
1231 end if;
1232 --
1233 close c1;
1234 --
1235 end if;
1236 --
1237 hr_utility.set_location('Leaving:'||l_proc,10);
1238 --
1239 end chk_lwr_lmt_calc_rl;
1240 --
1241 -- ----------------------------------------------------------------------------
1242 -- |------< chk_upr_lmt_calc_rl >------|
1243 -- ----------------------------------------------------------------------------
1244 --
1245 -- Description
1246 -- This procedure is used to check that the Formula Rule is valid.
1247 --
1248 -- Pre Conditions
1249 -- None.
1250 --
1251 -- In Parameters
1252 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
1253 -- upr_lmt_calc_rl Value of formula rule id.
1254 -- effective_date effective date
1255 -- object_version_number Object version number of record being
1256 -- inserted or updated.
1257 --
1258 -- Post Success
1259 -- Processing continues
1260 --
1261 -- Post Failure
1262 -- Error handled by procedure
1263 --
1264 -- Access Status
1265 -- Internal table handler use only.
1266 --
1267 Procedure chk_upr_lmt_calc_rl(p_cvg_amt_calc_mthd_id in number,
1268 p_upr_lmt_calc_rl in number,
1269 p_business_group_id in number,
1270 p_effective_date in date,
1271 p_object_version_number in number) is
1272 --
1273 l_proc varchar2(72) := g_package||'chk_upr_lmt_calc_rl';
1274 l_api_updating boolean;
1275 l_dummy varchar2(1);
1276 --
1277 cursor c1 is
1278 select null
1279 from ff_formulas_f ff
1280 ,per_business_groups pbg
1281 where ff.formula_id = p_upr_lmt_calc_rl
1282 and ff.formula_type_id = -514
1283 and pbg.business_group_id = p_business_group_id
1284 and nvl(ff.business_group_id, p_business_group_id) =
1285 p_business_group_id
1286 and nvl(ff.legislation_code, pbg.legislation_code) =
1287 pbg.legislation_code
1288 and p_effective_date
1289 between ff.effective_start_date
1290 and ff.effective_end_date;
1291 --
1292 Begin
1293 --
1294 hr_utility.set_location('Entering:'||l_proc, 5);
1295 --
1296 l_api_updating := ben_ccm_shd.api_updating
1297 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
1298 p_effective_date => p_effective_date,
1299 p_object_version_number => p_object_version_number);
1300 --
1301 if (l_api_updating
1302 and nvl(p_upr_lmt_calc_rl,hr_api.g_number)
1306 --
1303 <> ben_ccm_shd.g_old_rec.upr_lmt_calc_rl
1304 or not l_api_updating)
1305 and p_upr_lmt_calc_rl is not null then
1307 -- check if value of formula rule is valid.
1308 --
1309 open c1;
1310 --
1311 -- fetch value from cursor if it returns a record then the
1312 -- formula is valid otherwise its invalid
1313 --
1314 fetch c1 into l_dummy;
1315 if c1%notfound then
1316 --
1317 close c1;
1318 --
1319 -- raise error
1320 --
1321 hr_utility.set_message('BEN','BEN_91823_INVALID_UPR_LMT_RL');
1322 hr_utility.raise_error;
1323 --
1324 end if;
1325 --
1326 close c1;
1327 --
1328 end if;
1329 --
1330 hr_utility.set_location('Leaving:'||l_proc,10);
1331 --
1332 end chk_upr_lmt_calc_rl;
1333 --
1334 -- ----------------------------------------------------------------------------
1335 -- |------< chk_rndg_cd >------|
1336 -- ----------------------------------------------------------------------------
1337 --
1338 -- Description
1339 -- This procedure is used to check that the lookup value is valid.
1340 --
1341 -- Pre Conditions
1342 -- None.
1343 --
1344 -- In Parameters
1345 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
1346 -- rndg_cd Value of lookup code.
1347 -- effective_date effective date
1348 -- object_version_number Object version number of record being
1349 -- inserted or updated.
1350 --
1351 -- Post Success
1352 -- Processing continues
1353 --
1354 -- Post Failure
1355 -- Error handled by procedure
1356 --
1357 -- Access Status
1358 -- Internal table handler use only.
1359 --
1360 Procedure chk_rndg_cd(p_cvg_amt_calc_mthd_id in number,
1361 p_rndg_cd in varchar2,
1362 p_effective_date in date,
1363 p_object_version_number in number) is
1364 --
1365 l_proc varchar2(72) := g_package||'chk_rndg_cd';
1366 l_api_updating boolean;
1367 --
1368 Begin
1369 --
1370 hr_utility.set_location('Entering:'||l_proc, 5);
1371 --
1372 l_api_updating := ben_ccm_shd.api_updating
1373 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
1374 p_effective_date => p_effective_date,
1375 p_object_version_number => p_object_version_number);
1376 --
1377 if (l_api_updating
1378 and p_rndg_cd
1379 <> nvl(ben_ccm_shd.g_old_rec.rndg_cd,hr_api.g_varchar2)
1380 or not l_api_updating)
1381 and p_rndg_cd is not null then
1382 --
1383 -- check if value of lookup falls within lookup type.
1384 --
1385 if hr_api.not_exists_in_hr_lookups
1386 (p_lookup_type => 'BEN_RNDG',
1387 p_lookup_code => p_rndg_cd,
1388 p_effective_date => p_effective_date) then
1389 --
1390 -- raise error as does not exist as lookup
1391 --
1392 hr_utility.set_message(801,'RNDG_CD_DOES_NOT_EXIST');
1393 hr_utility.raise_error;
1394 --
1395 end if;
1396 --
1397 end if;
1398 --
1399 hr_utility.set_location('Leaving:'||l_proc,10);
1400 --
1401 end chk_rndg_cd;
1402 --
1403 -- ----------------------------------------------------------------------------
1404 -- |------< chk_no_mn_val_dfnd_flag >------|
1405 -- ----------------------------------------------------------------------------
1406 --
1407 -- Description
1408 -- This procedure is used to check that the lookup value is valid.
1409 --
1410 -- Pre Conditions
1411 -- None.
1412 --
1413 -- In Parameters
1414 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
1415 -- no_mn_val_dfnd_flag Value of lookup code.
1416 -- effective_date effective date
1417 -- object_version_number Object version number of record being
1418 -- inserted or updated.
1419 --
1420 -- Post Success
1421 -- Processing continues
1422 --
1423 -- Post Failure
1424 -- Error handled by procedure
1425 --
1426 -- Access Status
1427 -- Internal table handler use only.
1428 --
1429 Procedure chk_no_mn_val_dfnd_flag(p_cvg_amt_calc_mthd_id in number,
1430 p_no_mn_val_dfnd_flag in varchar2,
1431 p_effective_date in date,
1432 p_object_version_number in number) is
1433 --
1434 l_proc varchar2(72) := g_package||'chk_no_mn_val_dfnd_flag';
1435 l_api_updating boolean;
1436 --
1437 Begin
1438 --
1439 hr_utility.set_location('Entering:'||l_proc, 5);
1440 --
1441 l_api_updating := ben_ccm_shd.api_updating
1442 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
1443 p_effective_date => p_effective_date,
1444 p_object_version_number => p_object_version_number);
1445 --
1446 if (l_api_updating
1447 and p_no_mn_val_dfnd_flag
1448 <> nvl(ben_ccm_shd.g_old_rec.no_mn_val_dfnd_flag,hr_api.g_varchar2)
1452 --
1449 or not l_api_updating) then
1450 --
1451 -- check if value of lookup falls within lookup type.
1453 --
1454 if hr_api.not_exists_in_hr_lookups
1455 (p_lookup_type => 'YES_NO',
1456 p_lookup_code => p_no_mn_val_dfnd_flag,
1457 p_effective_date => p_effective_date) then
1458 --
1459 -- raise error as does not exist as lookup
1460 --
1461 hr_utility.set_message(801,'NO_MN_VAL_DOES_NOT_EXIST');
1462 hr_utility.raise_error;
1463 --
1464 end if;
1465 --
1466 end if;
1467 --
1468 hr_utility.set_location('Leaving:'||l_proc,10);
1469 --
1470 end chk_no_mn_val_dfnd_flag;
1471 --
1472 -- ----------------------------------------------------------------------------
1473 -- |------< chk_no_mx_val_dfnd_flag >------|
1474 -- ----------------------------------------------------------------------------
1475 --
1476 -- Description
1477 -- This procedure is used to check that the lookup value is valid.
1478 --
1479 -- Pre Conditions
1480 -- None.
1481 --
1482 -- In Parameters
1483 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
1484 -- no_mx_val_dfnd_flag Value of lookup code.
1485 -- effective_date effective date
1486 -- object_version_number Object version number of record being
1487 -- inserted or updated.
1488 --
1489 -- Post Success
1490 -- Processing continues
1491 --
1492 -- Post Failure
1493 -- Error handled by procedure
1494 --
1495 -- Access Status
1496 -- Internal table handler use only.
1497 --
1498 Procedure chk_no_mx_val_dfnd_flag(p_cvg_amt_calc_mthd_id in number,
1499 p_no_mx_val_dfnd_flag in varchar2,
1500 p_effective_date in date,
1501 p_object_version_number in number) is
1502 --
1503 l_proc varchar2(72) := g_package||'chk_no_mx_val_dfnd_flag';
1504 l_api_updating boolean;
1505 --
1506 Begin
1507 --
1508 hr_utility.set_location('Entering:'||l_proc, 5);
1509 --
1510 l_api_updating := ben_ccm_shd.api_updating
1511 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
1512 p_effective_date => p_effective_date,
1513 p_object_version_number => p_object_version_number);
1514 --
1515 if (l_api_updating
1516 and p_no_mx_val_dfnd_flag
1517 <> nvl(ben_ccm_shd.g_old_rec.no_mx_val_dfnd_flag,hr_api.g_varchar2)
1518 or not l_api_updating) then
1519 --
1520 -- check if value of lookup falls within lookup type.
1521 --
1522 --
1523 if hr_api.not_exists_in_hr_lookups
1524 (p_lookup_type => 'YES_NO',
1525 p_lookup_code => p_no_mx_val_dfnd_flag,
1526 p_effective_date => p_effective_date) then
1527 --
1528 -- raise error as does not exist as lookup
1529 --
1530 hr_utility.set_message(801,'NO_MX_VAL_DOES_NOT_EXIST');
1531 hr_utility.raise_error;
1532 --
1533 end if;
1534 --
1535 end if;
1536 --
1537 hr_utility.set_location('Leaving:'||l_proc,10);
1538 --
1539 end chk_no_mx_val_dfnd_flag;
1540
1541 ------------------------------------------------------------------------
1542 ----
1543 -- |------< chk_mn_mx_val >------|
1544 --
1545 ------------------------------------------------------------------------
1546 ----
1547 --
1548 -- Description
1549 -- This procedure is used to check that minimum value is always
1550 -- less than max
1551 --
1552 -- Pre Conditions
1553 -- None.
1554 --
1555 -- In Parameters
1556 -- p_cvg_amt_calc_mthd_id PK of record being inserted or updated.
1557 -- min_val Value of Minimum.
1558 -- max_val Value of Maximum.
1559 -- effective_date effective date
1560 -- object_version_number Object version number of record being
1561 -- inserted or updated.
1562 --
1563 -- Post Success
1564 -- Processing continues
1565 --
1566 -- Post Failure
1567 -- Error handled by procedure
1568 --
1569 -- Access Status
1570 -- Internal table handler use only.
1571 --
1572 Procedure chk_mn_mx_val( p_cvg_amt_calc_mthd_id in number,
1573 p_min_val in number,
1574 p_max_val in number,
1575 p_object_version_number in number) is
1576 --
1577 l_proc varchar2(72) := g_package || 'chk_mn_mx_val';
1578 l_api_updating boolean;
1579 l_dummy varchar2(1);
1580 --
1581 Begin
1582 --
1583 hr_utility.set_location('Entering:'||l_proc, 5);
1584 --
1585 -- min_val must be < max_val,
1586 -- if both are used.
1587 --
1588 if p_min_val is not null and p_max_val is not null then
1589 --
1590 -- raise error if max value not greater than min value
1591 --
1592 if (p_max_val < p_min_val) then
1593 fnd_message.set_name('BEN','BEN_91069_INVALID_MIN_MAX');
1594 fnd_message.raise_error;
1595 end if;
1596 --
1597 --
1598 end if;
1599 --
1603 --
1600 hr_utility.set_location('Leaving:'||l_proc,10);
1601 --
1602 end chk_mn_mx_val;
1604 --
1605 -- ----------------------------------------------------------------------------
1606 -- |---------------------< chk_upr_lwr_lmt_val >-------------------------------|
1607 -- ----------------------------------------------------------------------------
1608 --
1609 -- Description
1610 -- This procedure is used to check that if upr_lmt_val is not null then it
1611 -- should be greater to lwr_lmt_val
1612 --
1613 -- Pre Conditions
1614 -- None.
1615 --
1616 -- In Parameters
1617 -- upr_lmt_val Upper Limit Value.
1618 -- lwr_lmt_val Lower Limit Value Rule.
1619 --
1620 -- Post Success
1621 -- Processing continues
1622 --
1623 -- Post Failure
1624 -- Error handled by procedure
1625 --
1626 -- Access Status
1627 -- Internal table handler use only.
1628 --
1629 --
1630 Procedure chk_upr_lwr_lmt_val( p_upr_lmt_val in number,
1631 p_lwr_lmt_val in number) is
1632 --
1633 l_proc varchar2(72) := g_package||'chk_upr_lwr_lmt_val';
1634 --
1635 Begin
1636 --
1637 hr_utility.set_location('Entering:'||l_proc, 5);
1638 --
1639 -- Upper Limit Value should not be less than Lower Limit Value
1640 -- mutually exclusive.
1641 if (p_upr_lmt_val is not null and p_lwr_lmt_val is not null) and
1642 (p_upr_lmt_val < p_lwr_lmt_val)
1643 then
1644 --
1645 fnd_message.set_name('BEN','BEN_92505_HIGH_LOW_LMT_VAL');
1646 fnd_message.raise_error;
1647 null;
1648 --
1649 end if;
1650 end chk_upr_lwr_lmt_val;
1651 --
1652 --
1653
1654
1655 --
1656 -- ----------------------------------------------------------------------------
1657 -- |------< chk_cvg_det_cd >------|
1658 -- ----------------------------------------------------------------------------
1659 --
1660 -- Description
1661 -- This procedure is used to check that the lookup value is valid.
1662 --
1663 -- Pre Conditions
1664 -- None.
1665 --
1666 -- In Parameters
1667 -- cvg_amt_calc_mthd_id PK of record being inserted or updated.
1668 -- cvg_det_cd Value of lookup code.
1669 -- effective_date effective date
1670 -- object_version_number Object version number of record being
1671 -- inserted or updated.
1672 --
1673 -- Post Success
1674 -- Processing continues
1675 --
1676 -- Post Failure
1677 -- Error handled by procedure
1678 --
1679 -- Access Status
1680 -- Internal table handler use only.
1681 --
1682 --Procedure chk_cvg_det_cd(p_cvg_amt_calc_mthd_id in number,
1683 -- p_cvg_det_cd in varchar2,
1684 -- p_effective_date in date,
1685 -- p_object_version_number in number) is
1686 --
1687 -- l_proc varchar2(72) := g_package||'chk_cvg_det_cd';
1688 -- l_api_updating boolean;
1689 --
1690 --Begin
1691 --
1692 -- hr_utility.set_location('Entering:'||l_proc, 5);
1693 --
1694 -- l_api_updating := ben_ccm_shd.api_updating
1695 -- (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
1696 -- p_effective_date => p_effective_date,
1697 -- p_object_version_number => p_object_version_number);
1698 --
1699 -- if (l_api_updating
1700 -- and p_cvg_det_cd
1701 -- <> nvl(ben_ccm_shd.g_old_rec.cvg_det_cd,hr_api.g_varchar2)
1702 -- or not l_api_updating)
1703 -- and p_cvg_det_cd is not null then
1704 --
1705 -- check if value of lookup falls within lookup type.
1706 --
1707 -- if hr_api.not_exists_in_hr_lookups
1708 -- (p_lookup_type => 'BEN_CVG_DET',
1709 -- p_lookup_code => p_cvg_det_cd,
1710 -- p_effective_date => p_effective_date) then
1711 --
1712 -- raise error as does not exist as lookup
1713 --
1714 -- hr_utility.set_message(801,'CVG_DET_DOES_NOT_EXIST');
1715 -- hr_utility.raise_error;
1716 --
1717 -- end if;
1718 --
1719 -- end if;
1720 --
1721 -- hr_utility.set_location('Leaving:'||l_proc,10);
1722 --
1723 --end chk_cvg_det_cd;
1724 --
1725 -- ----------------------------------------------------------------------------
1726 -- |---------------------< chk_oipl_id_unique >--------------------------------|
1727 -- ----------------------------------------------------------------------------
1728 --
1729 -- Description
1730 -- ensure that the Option in Plan ID is unique
1731 -- within business_group
1732 --
1733 -- Pre Conditions
1734 -- None.
1735 --
1736 -- In Parameters
1737 -- p_oipl_id is Option in Plan ID
1738 -- p_cvg_amt_calc_mthd_id is cvg_amt_calc_mthd_id
1739 -- p_effective_date is the transactions effective_date
1740 -- p_business_group_id
1741 --
1742 -- Post Success
1743 -- Processing continues
1744 --
1745 -- Post Failure
1746 -- Errors handled by the procedure
1747 --
1748 -- Access Status
1749 -- Internal table handler use only.
1750 --
1751 -- ----------------------------------------------------------------------------
1755 ,p_effective_date in date
1752 Procedure chk_oipl_id_unique
1753 ( p_cvg_amt_calc_mthd_id in number
1754 ,p_oipl_id in number
1756 ,p_business_group_id in number)
1757 is
1758 l_proc varchar2(72) := g_package||'chk_oipl_id_unique';
1759 l_dummy char(1);
1760 cursor c1 is select null
1761 from ben_cvg_amt_calc_mthd_f
1762 Where cvg_amt_calc_mthd_id <> nvl(p_cvg_amt_calc_mthd_id,-1)
1763 and oipl_id = p_oipl_id
1764 and business_group_id = p_business_group_id
1765 and p_effective_date between effective_start_date
1766 and effective_end_date;
1767 --
1768 Begin
1769 hr_utility.set_location('Entering:'||l_proc, 5);
1770 --
1771 open c1;
1772 fetch c1 into l_dummy;
1773 if c1%found then
1774 close c1;
1775 fnd_message.set_name('BEN','BEN_91615_OIPL_ID_NOT_UNIQUE');
1776 fnd_message.raise_error;
1777 end if;
1778 --
1779 hr_utility.set_location('Leaving:'||l_proc, 15);
1780 End chk_oipl_id_unique;
1781 --
1782 -- ----------------------------------------------------------------------------
1783 -- |------------------------< chk_pl_id_unique >------------------------------|
1784 -- ----------------------------------------------------------------------------
1785 --
1786 -- Description
1787 -- ensure that the Plan ID is unique within business_group. Also check that
1788 -- if plan has certain interim codes that cvg cannot be entered at enrollment.
1789 --
1790 -- Pre Conditions
1791 -- None.
1792 --
1793 -- In Parameters
1794 -- p_pl_id is Plan ID
1795 -- p_cvg_amt_calc_mthd_id is cvg_amt_calc_mthd_id
1796 -- p_effective_date is the transactions effective_date
1797 -- p_business_group_id
1798 --
1799 -- Post Success
1800 -- Processing continues
1801 --
1802 -- Post Failure
1803 -- Errors handled by the procedure
1804 --
1805 -- Access Status
1806 -- Internal table handler use only.
1807 --
1808 -- ----------------------------------------------------------------------------
1809 Procedure chk_pl_id_unique
1810 ( p_cvg_amt_calc_mthd_id in number
1811 ,p_pl_id in number
1812 ,p_entr_val_at_enrt_flag in varchar2
1813 ,p_effective_date in date
1814 ,p_business_group_id in number)
1815 is
1816 l_proc varchar2(72) := g_package||'chk_pl_id_unique';
1817 l_dummy char(1);
1818 cursor c1 is select null
1819 from ben_cvg_amt_calc_mthd_f
1820 Where cvg_amt_calc_mthd_id <> nvl(p_cvg_amt_calc_mthd_id,-1)
1821 and pl_id = p_pl_id
1822 and business_group_id = p_business_group_id
1823 and p_effective_date between effective_start_date
1824 and effective_end_date;
1825
1826 cursor c2 is select 'x'
1827 from ben_pl_f pl
1828 Where pl.pl_id = p_pl_id
1829 and pl.dflt_to_asn_pndg_ctfn_cd like '%NL%' -- Next Lower
1830 and pl.business_group_id = p_business_group_id
1831 and p_effective_date between pl.effective_start_date
1832 and pl.effective_end_date;
1833
1834 cursor c3 is select 'x'
1835 from ben_ler_bnft_rstrn_f lbr
1836 Where lbr.dflt_to_asn_pndg_ctfn_cd like '%NL%'
1837 and lbr.pl_id = p_pl_id
1838 and p_effective_date between lbr.effective_start_date
1839 and lbr.effective_end_date;
1840
1841 cursor c4 is select 'x'
1842 from ben_plip_f pip
1843 Where pip.dflt_to_asn_pndg_ctfn_cd like '%NL%'
1844 and pip.pl_id = p_pl_id
1845 and p_effective_date between pip.effective_start_date
1846 and pip.effective_end_date;
1847
1848 --
1849 Begin
1850 hr_utility.set_location('Entering:'||l_proc, 5);
1851 --
1852 open c1;
1853 fetch c1 into l_dummy;
1854 if c1%found then
1855 close c1;
1856 fnd_message.set_name('BEN','BEN_91614_PL_ID_NOT_UNIQUE');
1857 fnd_message.raise_error;
1858 end if;
1859 close c1;
1860
1861 -- if the coverage value is entered at enrollment, you cannot have the
1862 -- interim code 'next lower' - it makes no sense.
1863 /*ENH This will go away with the interim enhancements
1864 if p_entr_val_at_enrt_flag = 'Y' then
1865 open c2;
1866 fetch c2 into l_dummy;
1867 if c2%found then
1868 close c2;
1869 fnd_message.set_name('BEN','BEN_92553_CVG_CANNOT_ENTR');
1870 fnd_message.raise_error;
1871 end if;
1872 close c2;
1873
1874 open c3;
1875 fetch c3 into l_dummy;
1876 if c3%found then
1877 close c3;
1878 fnd_message.set_name('BEN','BEN_92553_CVG_CANNOT_ENTR');
1879 fnd_message.raise_error;
1880 end if;
1881 close c3;
1882
1883 open c4;
1884 fetch c4 into l_dummy;
1885 if c4%found then
1886 close c4;
1887 fnd_message.set_name('BEN','BEN_92553_CVG_CANNOT_ENTR');
1888 fnd_message.raise_error;
1889 end if;
1890 close c4;
1891
1892 end if;
1896 End chk_pl_id_unique;
1893 */
1894 --
1895 hr_utility.set_location('Leaving:'||l_proc, 15);
1897 --
1898 -- ----------------------------------------------------------------------------
1899 -- |----------------------< chk_plan_not_savings >----------------------------|
1900 -- ----------------------------------------------------------------------------
1901 --
1902 -- Description
1903 -- Check that Compensation Object - Plan, to which Coverage is being attached
1904 -- is not Savings Plan : Bug 3841981
1905 --
1906 -- Pre Conditions
1907 -- None.
1908 --
1909 -- In Parameters
1910 -- p_pl_id Plan ID
1911 -- p_oipl_id Option in Plan ID
1912 -- p_cvg_amt_calc_mthd_id Primary Key
1913 -- p_validation_start_date Transactions Start Date
1914 -- p_validation_end_date Transactions End Date
1915 --
1916 -- Post Success
1917 -- Processing continues
1918 --
1919 -- Post Failure
1920 -- Errors handled by the procedure
1921 --
1922 -- Access Status
1923 -- Internal table handler use only.
1924 --
1925 -- ----------------------------------------------------------------------------
1926 Procedure chk_plan_not_savings
1927 ( p_cvg_amt_calc_mthd_id in number
1928 ,p_pl_id in number
1929 ,p_oipl_id in number
1930 ,p_validation_start_date in date
1931 ,p_validation_end_date in date)
1932 is
1933 --
1934 l_proc varchar2(72) := g_package||'.chk_plan_not_savings';
1935 l_dummy varchar2(1);
1936 l_svgs_pl_flag ben_pl_f.svgs_pl_flag%type;
1937 --
1938 cursor c_pln is
1939 select null
1940 from ben_pl_f
1941 Where pl_id = p_pl_id
1942 and svgs_pl_flag = 'Y'
1943 and effective_start_date <= p_validation_end_date
1944 and effective_end_date >= p_validation_start_date;
1945
1946 cursor c_oipl is
1947 select null
1948 from ben_pl_f pln, ben_oipl_f oipl
1949 Where oipl_id = p_oipl_id
1950 and pln.pl_id = oipl.pl_id
1951 and pln.svgs_pl_flag = 'Y'
1952 and pln.effective_start_date <= p_validation_end_date
1953 and pln.effective_end_date >= p_validation_start_date
1954 and oipl.effective_start_date <= p_validation_end_date
1955 and oipl.effective_end_date >= p_validation_start_date;
1956 --
1957 Begin
1958 --
1959 hr_utility.set_location('Entering:'||l_proc, 5);
1960 --
1961 if p_pl_id is not null
1962 then
1963 --
1964 open c_pln;
1965 fetch c_pln into l_dummy;
1966 if c_pln%found
1967 then
1968 --
1969 close c_pln;
1970 fnd_message.set_name('BEN', 'BEN_94035_CVG_ATTACH_SVGS_PLAN');
1971 fnd_message.raise_error;
1972 --
1973 end if;
1974 close c_pln;
1975 --
1976 end if;
1977 --
1978 if p_oipl_id is not null
1979 then
1980 --
1981 open c_oipl;
1982 fetch c_oipl into l_dummy;
1983 if c_oipl%found
1984 then
1985 --
1986 close c_oipl;
1987 fnd_message.set_name('BEN', 'BEN_94035_CVG_ATTACH_SVGS_PLAN');
1988 fnd_message.raise_error;
1989 --
1990 end if;
1991 close c_oipl;
1992 --
1993 end if;
1994 --
1995 hr_utility.set_location('Leaving:'||l_proc, 15);
1996 --
1997 End chk_plan_not_savings;
1998 --
1999 -- ----------------------------------------------------------------------------
2000 -- |--------------------------< dt_update_validate >--------------------------|
2001 -- ----------------------------------------------------------------------------
2002 -- {Start Of Comments}
2003 --
2004 -- Description:
2005 -- This procedure is used for referential integrity of datetracked
2006 -- parent entities when a datetrack update operation is taking place
2007 -- and where there is no cascading of update defined for this entity.
2008 --
2009 -- Prerequisites:
2010 -- This procedure is called from the update_validate.
2011 --
2012 -- In Parameters:
2013 --
2014 -- Post Success:
2015 -- Processing continues.
2016 --
2017 -- Post Failure:
2018 --
2019 -- Developer Implementation Notes:
2020 -- This procedure should not need maintenance unless the HR Schema model
2021 -- changes.
2022 --
2023 -- Access Status:
2024 -- Internal Row Handler Use Only.
2025 --
2026 -- {End Of Comments}
2027 -- ----------------------------------------------------------------------------
2028 Procedure dt_update_validate
2029 (p_oipl_id in number default hr_api.g_number,
2030 p_pl_id in number default hr_api.g_number,
2031 p_plip_id in number default hr_api.g_number,
2032 p_datetrack_mode in varchar2,
2033 p_validation_start_date in date,
2034 p_validation_end_date in date) Is
2035 --
2036 l_proc varchar2(72) := g_package||'dt_update_validate';
2037 l_integrity_error Exception;
2038 l_table_name all_tables.table_name%TYPE;
2039 --
2040 Begin
2041 hr_utility.set_location('Entering:'||l_proc, 5);
2042 --
2046 (p_api_name => l_proc,
2043 -- Ensure that the p_datetrack_mode argument is not null
2044 --
2045 hr_api.mandatory_arg_error
2047 p_argument => 'datetrack_mode',
2048 p_argument_value => p_datetrack_mode);
2049 --
2050 -- Only perform the validation if the datetrack update mode is valid
2051 --
2052 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
2053 --
2054 --
2055 -- Ensure the arguments are not null
2056 --
2057 hr_api.mandatory_arg_error
2058 (p_api_name => l_proc,
2059 p_argument => 'validation_start_date',
2060 p_argument_value => p_validation_start_date);
2061 --
2062 hr_api.mandatory_arg_error
2063 (p_api_name => l_proc,
2064 p_argument => 'validation_end_date',
2065 p_argument_value => p_validation_end_date);
2066 --
2067 If ((nvl(p_oipl_id, hr_api.g_number) <> hr_api.g_number) and
2068 NOT (dt_api.check_min_max_dates
2069 (p_base_table_name => 'ben_oipl_f',
2070 p_base_key_column => 'oipl_id',
2071 p_base_key_value => p_oipl_id,
2072 p_from_date => p_validation_start_date,
2073 p_to_date => p_validation_end_date))) Then
2074 l_table_name := 'ben_oipl_f';
2075 Raise l_integrity_error;
2076 End If;
2077 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
2078 NOT (dt_api.check_min_max_dates
2079 (p_base_table_name => 'ben_pl_f',
2080 p_base_key_column => 'pl_id',
2081 p_base_key_value => p_pl_id,
2082 p_from_date => p_validation_start_date,
2083 p_to_date => p_validation_end_date))) Then
2084 l_table_name := 'ben_pl_f';
2085 Raise l_integrity_error;
2086 End If;
2087 If ((nvl(p_plip_id, hr_api.g_number) <> hr_api.g_number) and
2088 NOT (dt_api.check_min_max_dates
2089 (p_base_table_name => 'ben_plip_f',
2090 p_base_key_column => 'plip_id',
2091 p_base_key_value => p_plip_id,
2092 p_from_date => p_validation_start_date,
2093 p_to_date => p_validation_end_date))) Then
2094 l_table_name := 'ben_plip_f';
2095 Raise l_integrity_error;
2096 End If;
2097 --
2098 End If;
2099 --
2100 hr_utility.set_location(' Leaving:'||l_proc, 10);
2101 Exception
2102 When l_integrity_error Then
2103 --
2104 -- A referential integrity check was violated therefore
2105 -- we must error
2106 --
2107 ben_utility.parent_integrity_error(p_table_name => l_table_name);
2108 --
2109 When Others Then
2110 --
2111 -- An unhandled or unexpected error has occurred which
2112 -- we must report
2113 --
2114 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2115 hr_utility.set_message_token('PROCEDURE', l_proc);
2116 hr_utility.set_message_token('STEP','15');
2117 hr_utility.raise_error;
2118 End dt_update_validate;
2119 --
2120 -- ----------------------------------------------------------------------------
2121 -- |--------------------------< dt_delete_validate >--------------------------|
2122 -- ----------------------------------------------------------------------------
2123 -- {Start Of Comments}
2124 --
2125 -- Description:
2126 -- This procedure is used for referential integrity of datetracked
2127 -- child entities when either a datetrack DELETE or ZAP is in operation
2128 -- and where there is no cascading of delete defined for this entity.
2129 -- For the datetrack mode of DELETE or ZAP we must ensure that no
2130 -- datetracked child rows exist between the validation start and end
2131 -- dates.
2132 --
2133 -- Prerequisites:
2134 -- This procedure is called from the delete_validate.
2135 --
2136 -- In Parameters:
2137 --
2138 -- Post Success:
2139 -- Processing continues.
2140 --
2141 -- Post Failure:
2142 -- If a row exists by determining the returning Boolean value from the
2143 -- generic dt_api.rows_exist function then we must supply an error via
2144 -- the use of the local exception handler l_rows_exist.
2145 --
2146 -- Developer Implementation Notes:
2147 -- This procedure should not need maintenance unless the HR Schema model
2148 -- changes.
2149 --
2150 -- Access Status:
2151 -- Internal Row Handler Use Only.
2152 --
2153 -- {End Of Comments}
2154 -- ----------------------------------------------------------------------------
2155 Procedure dt_delete_validate
2156 (p_cvg_amt_calc_mthd_id in number,
2157 p_datetrack_mode in varchar2,
2158 p_validation_start_date in date,
2159 p_validation_end_date in date) Is
2160 --
2161 l_proc varchar2(72) := g_package||'dt_delete_validate';
2162 l_rows_exist Exception;
2163 l_table_name all_tables.table_name%TYPE;
2164 --
2165 Begin
2166 hr_utility.set_location('Entering:'||l_proc, 5);
2167 --
2168 -- Ensure that the p_datetrack_mode argument is not null
2169 --
2170 hr_api.mandatory_arg_error
2171 (p_api_name => l_proc,
2172 p_argument => 'datetrack_mode',
2173 p_argument_value => p_datetrack_mode);
2174 --
2178 If (p_datetrack_mode = 'DELETE' or
2175 -- Only perform the validation if the datetrack mode is either
2176 -- DELETE or ZAP
2177 --
2179 p_datetrack_mode = 'ZAP') then
2180 --
2181 --
2182 -- Ensure the arguments are not null
2183 --
2184 hr_api.mandatory_arg_error
2185 (p_api_name => l_proc,
2186 p_argument => 'validation_start_date',
2187 p_argument_value => p_validation_start_date);
2188 --
2189 hr_api.mandatory_arg_error
2190 (p_api_name => l_proc,
2191 p_argument => 'validation_end_date',
2192 p_argument_value => p_validation_end_date);
2193 --
2194 hr_api.mandatory_arg_error
2195 (p_api_name => l_proc,
2196 p_argument => 'cvg_amt_calc_mthd_id',
2197 p_argument_value => p_cvg_amt_calc_mthd_id);
2198 --
2199 If (dt_api.rows_exist
2200 (p_base_table_name => 'ben_bnft_vrbl_rt_f',
2201 p_base_key_column => 'cvg_amt_calc_mthd_id',
2202 p_base_key_value => p_cvg_amt_calc_mthd_id,
2203 p_from_date => p_validation_start_date,
2204 p_to_date => p_validation_end_date)) Then
2205 l_table_name := 'ben_bnft_vrbl_rt_f';
2206 Raise l_rows_exist;
2207 End If;
2208 If (dt_api.rows_exist
2209 (p_base_table_name => 'ben_bnft_vrbl_rt_rl_f',
2210 p_base_key_column => 'cvg_amt_calc_mthd_id',
2211 p_base_key_value => p_cvg_amt_calc_mthd_id,
2212 p_from_date => p_validation_start_date,
2213 p_to_date => p_validation_end_date)) Then
2214 l_table_name := 'ben_bnft_vrbl_rt_rl_f';
2215 Raise l_rows_exist;
2216 End If;
2217 If (dt_api.rows_exist
2218 (p_base_table_name => 'ben_bnft_vrbl_rt_rl_f',
2219 p_base_key_column => 'cvg_amt_calc_mthd_id',
2220 p_base_key_value => p_cvg_amt_calc_mthd_id,
2221 p_from_date => p_validation_start_date,
2222 p_to_date => p_validation_end_date)) Then
2223 l_table_name := 'ben_bnft_vrbl_rt_rl_f';
2224 Raise l_rows_exist;
2225 End If;
2226 If (dt_api.rows_exist
2227 (p_base_table_name => 'ben_bnft_vrbl_rt_rl_f',
2228 p_base_key_column => 'cvg_amt_calc_mthd_id',
2229 p_base_key_value => p_cvg_amt_calc_mthd_id,
2230 p_from_date => p_validation_start_date,
2231 p_to_date => p_validation_end_date)) Then
2232 l_table_name := 'ben_bnft_vrbl_rt_rl_f';
2233 Raise l_rows_exist;
2234 End If;
2235 --
2236 End If;
2237 --
2238 hr_utility.set_location(' Leaving:'||l_proc, 10);
2239 Exception
2240 When l_rows_exist Then
2241 --
2242 -- A referential integrity check was violated therefore
2243 -- we must error
2244 --
2245 ben_utility.child_exists_error(p_table_name => l_table_name);
2246 --
2247 When Others Then
2248 --
2249 -- An unhandled or unexpected error has occurred which
2250 -- we must report
2251 --
2252 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2253 hr_utility.set_message_token('PROCEDURE', l_proc);
2254 hr_utility.set_message_token('STEP','15');
2255 hr_utility.raise_error;
2256 End dt_delete_validate;
2257 --
2258 -- ----------------------------------------------------------------------------
2259 -- |------< chk_mlt_cd_dependencies >------|
2260 -- ----------------------------------------------------------------------------
2261 --
2262 -- Description
2263 --
2264 --
2265 --
2266 -- In Parameters
2267 -- cvg_mlt_cd
2268 -- val
2269 -- mn_val
2270 -- mx_val
2271 -- incrmt_val
2272 -- dflt_val
2273 -- rt_typ_cd
2274 -- val_calc_rl
2275 -- comp_lvl_fctr_id
2276 -- entr_val_at_enrt_flag
2277 -- acty_base_rt_id
2278 -- effective_date
2279 -- object_version_number
2280 --
2281 Procedure chk_mlt_cd_dependencies(p_cvg_mlt_cd in varchar2,
2282 p_val in number,
2283 p_mn_val in number,
2284 p_mx_val in number,
2285 p_incrmt_val in number,
2286 p_dflt_val in number,
2287 p_rt_typ_cd in varchar2,
2288 p_val_calc_rl in number,
2289 p_comp_lvl_fctr_id in number,
2290 p_entr_val_at_enrt_flag in varchar2,
2291 p_cvg_amt_calc_mthd_id in number,
2292 p_effective_date in date,
2293 p_object_version_number in number
2294 ) is
2295 --
2296 l_proc varchar2(72) := g_package||'chk_mlt_cd_dependencies';
2297 l_api_updating boolean;
2298 --
2299 Begin
2300 --
2301 hr_utility.set_location('Entering:'||l_proc, 5);
2302 --
2303 l_api_updating := ben_ccm_shd.api_updating
2307 --
2304 (p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id,
2305 p_effective_date => p_effective_date,
2306 p_object_version_number => p_object_version_number);
2308 if (l_api_updating
2309 and
2310 (nvl(p_cvg_mlt_cd,hr_api.g_varchar2)
2311 <> nvl(ben_ccm_shd.g_old_rec.cvg_mlt_cd,hr_api.g_varchar2) or
2312 nvl(p_val,hr_api.g_number)
2313 <> nvl(ben_ccm_shd.g_old_rec.val,hr_api.g_number) or
2314 nvl(p_mn_val,hr_api.g_number)
2315 <> nvl(ben_ccm_shd.g_old_rec.mn_val,hr_api.g_number) or
2316 nvl(p_mx_val,hr_api.g_number)
2317 <> nvl(ben_ccm_shd.g_old_rec.mx_val,hr_api.g_number) or
2318 nvl(p_incrmt_val,hr_api.g_number)
2319 <> nvl(ben_ccm_shd.g_old_rec.incrmt_val,hr_api.g_number) or
2320 nvl(p_dflt_val,hr_api.g_number)
2321 <> nvl(ben_ccm_shd.g_old_rec.dflt_val,hr_api.g_number) or
2322 nvl(p_rt_typ_cd,hr_api.g_varchar2)
2323 <> nvl(ben_ccm_shd.g_old_rec.rt_typ_cd,hr_api.g_varchar2) or
2324 nvl(p_val_calc_rl,hr_api.g_number)
2325 <> nvl(ben_ccm_shd.g_old_rec.val_calc_rl,hr_api.g_number) or
2326 nvl(p_comp_lvl_fctr_id,hr_api.g_number)
2327 <> nvl(ben_ccm_shd.g_old_rec.comp_lvl_fctr_id,hr_api.g_number) or
2328 nvl(p_entr_val_at_enrt_flag,hr_api.g_varchar2)
2329 <> nvl(ben_ccm_shd.g_old_rec.entr_val_at_enrt_flag,hr_api.g_varchar2)
2330 )
2331 or
2332 not l_api_updating)
2333 then
2334 --
2335 if p_entr_val_at_enrt_flag = 'N' and p_cvg_mlt_cd = 'FLFX' then
2336 --
2337 if p_mn_val is not null then
2338 --
2339 fnd_message.set_name('BEN','BEN_91539_MIN_VAL_SPEC');
2340 fnd_message.raise_error;
2341 --
2342 elsif p_mx_val is not null then
2343 --
2344 fnd_message.set_name('BEN','BEN_91541_MAX_VAL_SPEC');
2345 fnd_message.raise_error;
2346 --
2347 elsif p_incrmt_val is not null then
2348 --
2349 fnd_message.set_name('BEN','BEN_91543_INCRMT_VAL_SPEC');
2350 fnd_message.raise_error;
2351 --
2352 elsif p_dflt_val is not null then
2353 --
2354 fnd_message.set_name('BEN','BEN_91545_DFLT_VAL_SPEC');
2355 fnd_message.raise_error;
2356 --
2357 end if;
2358 --
2359 elsif p_entr_val_at_enrt_flag = 'Y' and p_cvg_mlt_cd = 'FLFX' then
2360 --
2361 if p_val is not null then
2362 --
2363 fnd_message.set_name('BEN','BEN_91537_VAL_SPEC');
2364 fnd_message.raise_error;
2365 --
2366 end if;
2367 --
2368 elsif p_entr_val_at_enrt_flag = 'Y' and p_cvg_mlt_cd <> 'FLFX' then
2369 --
2370 fnd_message.set_name('BEN','BEN_91941_ENTR_AT_ENRT_FLAG');
2371 fnd_message.raise_error;
2372 --
2373 end if;
2374 if p_cvg_mlt_cd is NULL then
2375 --
2376 fnd_message.set_name('BEN','BEN_91535_MLT_CD_RQD');
2377 fnd_message.raise_error;
2378 --
2379 end if;
2380 --
2381 if p_val is NULL then
2382 --
2383 if p_cvg_mlt_cd in ('CL','FLFXPCL','FLPCLRNG','CLPFLRNG') then
2384 --
2385 fnd_message.set_name('BEN','BEN_91536_VAL_RQD');
2386 fnd_message.raise_error;
2387 --
2388 end if;
2389 --
2390 else
2391 --
2392 if p_cvg_mlt_cd in ('FLRNG','CLRNG','RL','NSVU') then
2393 --
2394 fnd_message.set_name('BEN','BEN_91537_VAL_SPEC');
2395 fnd_message.raise_error;
2396 --
2397 end if;
2398 --
2399 end if;
2400 --
2401 if p_mn_val is NULL then
2402 --
2403 if p_cvg_mlt_cd in ('FLRNG','CLRNG','FLFXPCL','FLPCLRNG','CLPFLRNG') then
2404 --
2405 fnd_message.set_name('BEN','BEN_91538_MIN_VAL_RQD');
2406 fnd_message.raise_error;
2407 --
2408 end if;
2409 --
2410 else
2411 --
2412 if p_cvg_mlt_cd in ('CL','RL','NSVU') then
2413 --
2414 fnd_message.set_name('BEN','BEN_91539_MIN_VAL_SPEC');
2415 fnd_message.raise_error;
2416 --
2417 end if;
2418 --
2419 end if;
2420 --
2421 if p_mx_val is NULL then
2422 --
2423 if p_cvg_mlt_cd in ('FLRNG','CLRNG','FLPCLRNG','CLPFLRNG') then
2424 --
2425 fnd_message.set_name('BEN','BEN_91540_MAX_VAL_RQD');
2426 fnd_message.raise_error;
2427 --
2428 end if;
2429 --
2430 else
2431 --
2432 if p_cvg_mlt_cd in ('CL','FLFXPCL','RL','NSVU') then
2433 --
2434 fnd_message.set_name('BEN','BEN_91541_MAX_VAL_SPEC');
2435 fnd_message.raise_error;
2436 --
2437 end if;
2438 --
2439 end if;
2440 --
2441 if p_incrmt_val is NULL then
2442 --
2443 if p_cvg_mlt_cd in ('FLRNG','CLRNG','FLPCLRNG','CLPFLRNG') then
2444 --
2448 end if;
2445 fnd_message.set_name('BEN','BEN_91542_INCRMT_VAL_RQD');
2446 fnd_message.raise_error;
2447 --
2449 --
2450 else
2451 --
2452 if p_cvg_mlt_cd in ('CL','FLFXPCL','RL','NSVU') then
2453 --
2454 fnd_message.set_name('BEN','BEN_91543_INCRMT_VAL_SPEC');
2455 fnd_message.raise_error;
2456 --
2457 end if;
2458 --
2459 end if;
2460 --
2461 --
2462 if p_dflt_val is NULL then
2463 --
2464 if p_cvg_mlt_cd in ('FLRNG','CLRNG','FLPCLRNG','CLPFLRNG') then
2465 --
2466 fnd_message.set_name('BEN','BEN_91544_DFLT_VAL_RQD');
2467 fnd_message.raise_error;
2468 --
2469 end if;
2470 --
2471 else
2472 --
2473 if p_cvg_mlt_cd in ('CL','FLFXPCL','RL','NSVU') then
2474 --
2475 fnd_message.set_name('BEN','BEN_91545_DFLT_VAL_SPEC');
2476 fnd_message.raise_error;
2477 --
2478 end if;
2479 --
2480 end if;
2481 --
2482 if p_rt_typ_cd is NULL then
2483 --
2484 if p_cvg_mlt_cd in ('CL','CLRNG','FLFXPCL','FLPCLRNG','CLPFLRNG') then
2485 --
2486 fnd_message.set_name('BEN','BEN_91546_RT_TYP_CD_RQD');
2487 fnd_message.raise_error;
2488 --
2489 end if;
2490 else
2491 --
2492 if p_cvg_mlt_cd in ('FLFX','FLRNG','RL','NSVU') then
2493 --
2494 fnd_message.set_name('BEN','BEN_91547_RT_TYP_CD_SPEC');
2495 fnd_message.raise_error;
2496 --
2497 end if;
2498 --
2499 end if;
2500 --
2501 if p_val_calc_rl is NULL then
2502 --
2503 if p_cvg_mlt_cd in ('RL') then
2504 --
2505 fnd_message.set_name('BEN','BEN_91548_VAL_CALC_RL_RQD');
2506 fnd_message.raise_error;
2507 --
2508 end if;
2509 --
2510 else
2511 --
2512 if p_cvg_mlt_cd in ('FLFX','FLRNG','CL','CLRNG','FLFXPCL','FLPCLRNG','CLPFLRNG','NSVU') then
2513 --
2514 fnd_message.set_name('BEN','BEN_91549_VAL_CALC_RL_SPEC');
2515 fnd_message.raise_error;
2516 --
2517 end if;
2518 end if;
2519 -- begin bug 3191595
2520 if p_dflt_val is not null and p_mn_val is not null and p_mx_val is not null then
2521 if p_dflt_val < p_mn_val or p_dflt_val > p_mx_val then
2522 --
2523 fnd_message.set_name('PAY','HR_INPVAL_DEFAULT_INVALID');
2524 fnd_message.raise_error;
2525 --
2526 end if;
2527 end if;
2528 -- end bug 3191595
2529 --
2530 end if;
2531 --
2532 hr_utility.set_location('Leaving:'||l_proc,10);
2533 --
2534 end chk_mlt_cd_dependencies;
2535 --
2536
2537 --Bug#5043123
2538 -- ----------------------------------------------------------------------------
2539 -- |------< check_rnd_cd_rl_null >------|
2540 -- ----------------------------------------------------------------------------
2541 --
2542 -- Description
2543
2544 -- This procedure checks if the Rounding Code is selected as 'Rule'
2545 -- then Rounding Rule has to be entered.
2546 --
2547 -- In Parameters
2548
2549 -- cvg_amt_calc_mthd_id
2550 -- effective_date
2551 -- object_version_number
2552 -- rndg_cd
2553 -- rndg_rl
2554
2555 --
2556
2557 procedure check_rnd_cd_rl_null( p_cvg_amt_calc_mthd_id in number,
2558 p_effective_date in date,
2559 p_object_version_number in number,
2560 p_rndg_cd varchar2,
2561 p_rndg_rl varchar2
2562 ) is
2563 --
2564 l_proc varchar2(72) := g_package||'check_rnd_cd_rl_null';
2565 --
2566 Begin
2567 --
2568 hr_utility.set_location('Entering:'||l_proc, 5);
2569 --
2570 if p_rndg_cd='RL' and p_rndg_rl is null then
2571 --
2572 fnd_message.set_name('BEN', 'BEN_91733_RNDG_RULE');
2573 fnd_message.raise_error;
2574 --
2575 end if;
2576 --
2577 hr_utility.set_location('Leaving:'||l_proc,10);
2578 --
2579 end check_rnd_cd_rl_null;
2580
2581 --Bug#5043123
2582 -- ----------------------------------------------------------------------------
2583 -- |---------------------------< insert_validate >----------------------------|
2584 -- ----------------------------------------------------------------------------
2585 Procedure insert_validate
2586 (p_rec in ben_ccm_shd.g_rec_type,
2587 p_effective_date in date,
2588 p_datetrack_mode in varchar2,
2589 p_validation_start_date in date,
2590 p_validation_end_date in date) is
2591 --
2592 l_proc varchar2(72) := g_package||'insert_validate';
2593 --
2594 Begin
2595 hr_utility.set_location('Entering:'||l_proc, 5);
2596 --
2597 -- Call all supporting business operations
2598 --
2599 --
2600 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2601 --
2602 chk_cvg_amt_calc_mthd_id
2606 --
2603 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2604 p_effective_date => p_effective_date,
2605 p_object_version_number => p_rec.object_version_number);
2607
2608 chk_comp_lvl_fctr_id
2609 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2610 p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
2611 p_effective_date => p_effective_date,
2612 p_object_version_number => p_rec.object_version_number);
2613 --
2614 chk_only_one_fk
2615 (p_pl_id => p_rec.pl_id,
2616 p_oipl_id => p_rec.oipl_id,
2617 p_plip_id => p_rec.plip_id);
2618 --
2619 chk_plan_not_savings
2620 ( p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id
2621 ,p_pl_id => p_rec.pl_id
2622 ,p_oipl_id => p_rec.oipl_id
2623 ,p_validation_start_date => p_validation_start_date
2624 ,p_validation_end_date => p_validation_end_date );
2625 --
2626 chk_rt_typ_cd
2627 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2628 p_rt_typ_cd => p_rec.rt_typ_cd,
2629 p_effective_date => p_effective_date,
2630 p_object_version_number => p_rec.object_version_number);
2631 --
2632 chk_entr_val_at_enrt_flag
2633 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2634 p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag,
2635 p_effective_date => p_effective_date,
2636 p_object_version_number => p_rec.object_version_number);
2637 --
2638 chk_dflt_flag
2639 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2640 p_dflt_flag => p_rec.dflt_flag,
2641 p_effective_date => p_effective_date,
2642 p_object_version_number => p_rec.object_version_number);
2643 --
2644 chk_cvg_mlt_cd
2645 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2646 p_cvg_mlt_cd => p_rec.cvg_mlt_cd,
2647 p_effective_date => p_effective_date,
2648 p_object_version_number => p_rec.object_version_number);
2649 --
2650 chk_bnft_typ_cd
2651 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2652 p_bnft_typ_cd => p_rec.bnft_typ_cd,
2653 p_effective_date => p_effective_date,
2654 p_object_version_number => p_rec.object_version_number);
2655 --
2656 chk_bndry_perd_cd
2657 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2658 p_bndry_perd_cd => p_rec.bndry_perd_cd,
2659 p_effective_date => p_effective_date,
2660 p_object_version_number => p_rec.object_version_number);
2661 --
2662 chk_nnmntry_uom
2663 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2664 p_nnmntry_uom => p_rec.nnmntry_uom,
2665 p_effective_date => p_effective_date,
2666 p_object_version_number => p_rec.object_version_number);
2667 --
2668 chk_val_calc_rl
2669 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2670 p_val_calc_rl => p_rec.val_calc_rl,
2671 p_business_group_id => p_rec.business_group_id,
2672 p_effective_date => p_effective_date,
2673 p_object_version_number => p_rec.object_version_number);
2674 --
2675 chk_val_ovrid_alwd_flag
2676 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2677 p_val_ovrid_alwd_flag => p_rec.val_ovrid_alwd_flag,
2678 p_effective_date => p_effective_date,
2679 p_object_version_number => p_rec.object_version_number);
2680 --
2681 chk_rndg_rl
2682 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2683 p_rndg_rl => p_rec.rndg_rl,
2684 p_business_group_id => p_rec.business_group_id,
2685 p_effective_date => p_effective_date,
2686 p_object_version_number => p_rec.object_version_number);
2687 --
2688 chk_lwr_lmt_calc_rl
2689 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2690 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl,
2691 p_business_group_id => p_rec.business_group_id,
2692 p_effective_date => p_effective_date,
2693 p_object_version_number => p_rec.object_version_number);
2694 --
2695 chk_upr_lmt_calc_rl
2696 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2697 p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl,
2698 p_business_group_id => p_rec.business_group_id,
2699 p_effective_date => p_effective_date,
2700 p_object_version_number => p_rec.object_version_number);
2701 --
2702 chk_rndg_cd
2703 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2704 p_rndg_cd => p_rec.rndg_cd,
2705 p_effective_date => p_effective_date,
2706 p_object_version_number => p_rec.object_version_number);
2707 --
2708 chk_no_mn_val_dfnd_flag
2709 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2710 p_no_mn_val_dfnd_flag => p_rec.no_mn_val_dfnd_flag,
2711 p_effective_date => p_effective_date,
2712 p_object_version_number => p_rec.object_version_number);
2713 --
2714 chk_no_mx_val_dfnd_flag
2715 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2716 p_no_mx_val_dfnd_flag => p_rec.no_mx_val_dfnd_flag,
2717 p_effective_date => p_effective_date,
2718 p_object_version_number => p_rec.object_version_number);
2719 --
2720 chk_mn_mx_val
2721 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2725 --
2722 p_min_val =>p_rec.mn_val,
2723 p_max_val =>p_rec.mx_val,
2724 p_object_version_number => p_rec.object_version_number);
2726 chk_upr_lwr_lmt_val
2727 (p_upr_lmt_val => p_rec.upr_lmt_val,
2728 p_lwr_lmt_val => p_rec.lwr_lmt_val);
2729
2730 --chk_cvg_det_cd
2731 --(p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2732 -- p_cvg_det_cd => p_rec.cvg_det_cd,
2733 -- p_effective_date => p_effective_date,
2734 -- p_object_version_number => p_rec.object_version_number);
2735 --
2736 chk_oipl_id_unique
2737 ( p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id
2738 ,p_oipl_id => p_rec.oipl_id
2739 ,p_effective_date => p_effective_date
2740 ,p_business_group_id => p_rec.business_group_id);
2741 --
2742 chk_pl_id_unique
2743 ( p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id
2744 ,p_pl_id => p_rec.pl_id
2745 ,p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag
2746 ,p_effective_date => p_effective_date
2747 ,p_business_group_id => p_rec.business_group_id);
2748 --
2749 chk_mlt_cd_dependencies
2750 (p_cvg_mlt_cd => p_rec.cvg_mlt_cd,
2751 p_val => p_rec.val,
2752 p_mn_val => p_rec.mn_val,
2753 p_mx_val => p_rec.mx_val,
2754 p_incrmt_val => p_rec.incrmt_val,
2755 p_dflt_val => p_rec.dflt_val,
2756 p_rt_typ_cd => p_rec.rt_typ_cd,
2757 p_val_calc_rl => p_rec.val_calc_rl,
2758 p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
2759 p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag,
2760 p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2761 p_effective_date => p_effective_date,
2762 p_object_version_number => p_rec.object_version_number
2763 );
2764
2765 chk_entr_at_enrt_with_rate( p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag,
2766 p_pl_id => p_rec.pl_id,
2767 p_oipl_id => p_rec.oipl_id,
2768 p_effective_date => p_effective_date ) ;
2769
2770 check_rnd_cd_rl_null ( p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id
2771 ,p_effective_date => p_effective_date
2772 ,p_object_version_number => p_rec.object_version_number
2773 ,p_rndg_cd => p_rec.rndg_cd
2774 ,p_rndg_rl => p_rec.rndg_rl);
2775
2776 --
2777 hr_utility.set_location(' Leaving:'||l_proc, 10);
2778 End insert_validate;
2779 --
2780 -- ----------------------------------------------------------------------------
2781 -- |---------------------------< update_validate >----------------------------|
2782 -- ----------------------------------------------------------------------------
2783 Procedure update_validate
2784 (p_rec in ben_ccm_shd.g_rec_type,
2785 p_effective_date in date,
2786 p_datetrack_mode in varchar2,
2787 p_validation_start_date in date,
2788 p_validation_end_date in date) is
2789 --
2790 l_proc varchar2(72) := g_package||'update_validate';
2791 --
2792 Begin
2793 hr_utility.set_location('Entering:'||l_proc, 5);
2794 --
2795 -- Call all supporting business operations
2796 --
2797 --
2798 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2799 --
2800 chk_cvg_amt_calc_mthd_id
2801 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2802 p_effective_date => p_effective_date,
2803 p_object_version_number => p_rec.object_version_number);
2804 --
2805 chk_comp_lvl_fctr_id
2806 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2807 p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
2808 p_effective_date => p_effective_date,
2809 p_object_version_number => p_rec.object_version_number);
2810 --
2811 chk_only_one_fk
2812 (p_pl_id => p_rec.pl_id,
2813 p_oipl_id => p_rec.oipl_id,
2814 p_plip_id => p_rec.plip_id);
2815 --
2816 chk_plan_not_savings
2817 ( p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id
2818 ,p_pl_id => p_rec.pl_id
2819 ,p_oipl_id => p_rec.oipl_id
2820 ,p_validation_start_date => p_validation_start_date
2821 ,p_validation_end_date => p_validation_end_date );
2822 --
2823 chk_rt_typ_cd
2824 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2828 --
2825 p_rt_typ_cd => p_rec.rt_typ_cd,
2826 p_effective_date => p_effective_date,
2827 p_object_version_number => p_rec.object_version_number);
2829 chk_entr_val_at_enrt_flag
2830 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2831 p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag,
2832 p_effective_date => p_effective_date,
2833 p_object_version_number => p_rec.object_version_number);
2834 --
2835 chk_dflt_flag
2836 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2837 p_dflt_flag => p_rec.dflt_flag,
2838 p_effective_date => p_effective_date,
2839 p_object_version_number => p_rec.object_version_number);
2840 --
2841 chk_cvg_mlt_cd
2842 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2843 p_cvg_mlt_cd => p_rec.cvg_mlt_cd,
2844 p_effective_date => p_effective_date,
2845 p_object_version_number => p_rec.object_version_number);
2846 --
2847 chk_bnft_typ_cd
2848 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2849 p_bnft_typ_cd => p_rec.bnft_typ_cd,
2850 p_effective_date => p_effective_date,
2851 p_object_version_number => p_rec.object_version_number);
2852 --
2853 chk_bndry_perd_cd
2854 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2855 p_bndry_perd_cd => p_rec.bndry_perd_cd,
2856 p_effective_date => p_effective_date,
2857 p_object_version_number => p_rec.object_version_number);
2858 --
2859 chk_nnmntry_uom
2860 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2861 p_nnmntry_uom => p_rec.nnmntry_uom,
2862 p_effective_date => p_effective_date,
2863 p_object_version_number => p_rec.object_version_number);
2864 --
2865 chk_val_calc_rl
2866 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2867 p_val_calc_rl => p_rec.val_calc_rl,
2868 p_business_group_id => p_rec.business_group_id,
2869 p_effective_date => p_effective_date,
2870 p_object_version_number => p_rec.object_version_number);
2871 --
2872 chk_val_ovrid_alwd_flag
2873 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2874 p_val_ovrid_alwd_flag => p_rec.val_ovrid_alwd_flag,
2875 p_effective_date => p_effective_date,
2876 p_object_version_number => p_rec.object_version_number);
2877 --
2878 chk_rndg_rl
2879 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2880 p_rndg_rl => p_rec.rndg_rl,
2881 p_business_group_id => p_rec.business_group_id,
2882 p_effective_date => p_effective_date,
2883 p_object_version_number => p_rec.object_version_number);
2884 --
2885 chk_lwr_lmt_calc_rl
2886 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2887 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl,
2888 p_business_group_id => p_rec.business_group_id,
2889 p_effective_date => p_effective_date,
2890 p_object_version_number => p_rec.object_version_number);
2891 --
2892 chk_upr_lmt_calc_rl
2893 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2894 p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl,
2895 p_business_group_id => p_rec.business_group_id,
2896 p_effective_date => p_effective_date,
2897 p_object_version_number => p_rec.object_version_number);
2898 --
2899 chk_rndg_cd
2900 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2901 p_rndg_cd => p_rec.rndg_cd,
2902 p_effective_date => p_effective_date,
2903 p_object_version_number => p_rec.object_version_number);
2904 --
2905 chk_no_mn_val_dfnd_flag
2906 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2907 p_no_mn_val_dfnd_flag => p_rec.no_mn_val_dfnd_flag,
2908 p_effective_date => p_effective_date,
2909 p_object_version_number => p_rec.object_version_number);
2910 --
2911 chk_no_mx_val_dfnd_flag
2912 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2913 p_no_mx_val_dfnd_flag => p_rec.no_mx_val_dfnd_flag,
2914 p_effective_date => p_effective_date,
2915 p_object_version_number => p_rec.object_version_number);
2916
2917 chk_mn_mx_val
2918 (p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2919 p_min_val =>p_rec.mn_val,
2920 p_max_val =>p_rec.mx_val,
2921 p_object_version_number => p_rec.object_version_number);
2922 --
2923 chk_upr_lwr_lmt_val
2924 (p_upr_lmt_val => p_rec.upr_lmt_val,
2925 p_lwr_lmt_val => p_rec.lwr_lmt_val);
2926
2927 --
2928 --chk_cvg_det_cd
2929 --(p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2930 -- p_cvg_det_cd => p_rec.cvg_det_cd,
2931 -- p_effective_date => p_effective_date,
2932 -- p_object_version_number => p_rec.object_version_number);
2933 --
2934 chk_oipl_id_unique
2935 ( p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id
2936 ,p_oipl_id => p_rec.oipl_id
2937 ,p_effective_date => p_effective_date
2938 ,p_business_group_id => p_rec.business_group_id);
2939 --
2940 chk_pl_id_unique
2941 ( p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id
2942 ,p_pl_id => p_rec.pl_id
2943 ,p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag
2944 ,p_effective_date => p_effective_date
2945 ,p_business_group_id => p_rec.business_group_id);
2946 --
2947 -- Call the datetrack update integrity operation
2948 --
2949 dt_update_validate
2950 (p_oipl_id => p_rec.oipl_id,
2951 p_pl_id => p_rec.pl_id,
2952 p_plip_id => p_rec.plip_id,
2953 p_datetrack_mode => p_datetrack_mode,
2954 p_validation_start_date => p_validation_start_date,
2955 p_validation_end_date => p_validation_end_date);
2956 --
2957 chk_mlt_cd_dependencies
2958 (p_cvg_mlt_cd => p_rec.cvg_mlt_cd,
2959 p_val => p_rec.val,
2960 p_mn_val => p_rec.mn_val,
2961 p_mx_val => p_rec.mx_val,
2962 p_incrmt_val => p_rec.incrmt_val,
2963 p_dflt_val => p_rec.dflt_val,
2964 p_rt_typ_cd => p_rec.rt_typ_cd,
2965 p_val_calc_rl => p_rec.val_calc_rl,
2966 p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
2967 p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag,
2968 p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
2969 p_effective_date => p_effective_date,
2970 p_object_version_number => p_rec.object_version_number
2971 );
2972
2973 chk_entr_at_enrt_with_rate( p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag,
2974 p_pl_id => p_rec.pl_id,
2975 p_oipl_id => p_rec.oipl_id,
2976 p_effective_date => p_effective_date ) ;
2977
2978 check_rnd_cd_rl_null ( p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id
2979 ,p_effective_date => p_effective_date
2980 ,p_object_version_number => p_rec.object_version_number
2981 ,p_rndg_cd => p_rec.rndg_cd
2982 ,p_rndg_rl => p_rec.rndg_rl);
2983 --
2984 hr_utility.set_location(' Leaving:'||l_proc, 10);
2985 End update_validate;
2986 --
2987 -- ----------------------------------------------------------------------------
2988 -- |---------------------------< delete_validate >----------------------------|
2989 -- ----------------------------------------------------------------------------
2990 Procedure delete_validate
2991 (p_rec in ben_ccm_shd.g_rec_type,
2992 p_effective_date in date,
2993 p_datetrack_mode in varchar2,
2994 p_validation_start_date in date,
2995 p_validation_end_date in date) is
2996 --
2997 l_proc varchar2(72) := g_package||'delete_validate';
2998 --
2999 Begin
3000 hr_utility.set_location('Entering:'||l_proc, 5);
3001 --
3002 -- Call all supporting business operations
3003 --
3004 dt_delete_validate
3005 (p_datetrack_mode => p_datetrack_mode,
3006 p_validation_start_date => p_validation_start_date,
3007 p_validation_end_date => p_validation_end_date,
3011 ( p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
3008 p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id);
3009 --
3010 chk_pen_rows_exist
3012 p_effective_date => p_effective_date,
3013 p_pl_id => ben_ccm_shd.g_old_rec.pl_id,
3014 p_oipl_id => ben_ccm_shd.g_old_rec.oipl_id,
3015 p_business_group_id => ben_ccm_shd.g_old_rec.business_group_id,
3016 p_datetrack_mode => p_datetrack_mode );
3017 --
3018 hr_utility.set_location(' Leaving:'||l_proc, 10);
3019 End delete_validate;
3020 --
3021 --
3022 -- ---------------------------------------------------------------------------
3023 -- |---------------------< return_legislation_code >-------------------------|
3024 -- ---------------------------------------------------------------------------
3025 --
3026 function return_legislation_code
3027 (p_cvg_amt_calc_mthd_id in number) return varchar2 is
3028 --
3029 -- Declare cursor
3030 --
3031 cursor csr_leg_code is
3032 select a.legislation_code
3033 from per_business_groups a,
3034 ben_cvg_amt_calc_mthd_f b
3035 where b.cvg_amt_calc_mthd_id = p_cvg_amt_calc_mthd_id
3036 and a.business_group_id = b.business_group_id;
3037 --
3038 -- Declare local variables
3039 --
3040 l_legislation_code varchar2(150);
3041 l_proc varchar2(72) := g_package||'return_legislation_code';
3042 --
3043 begin
3044 --
3045 hr_utility.set_location('Entering:'|| l_proc, 10);
3046 --
3047 -- Ensure that all the mandatory parameter are not null
3048 --
3049 hr_api.mandatory_arg_error(p_api_name => l_proc,
3050 p_argument => 'cvg_amt_calc_mthd_id',
3051 p_argument_value => p_cvg_amt_calc_mthd_id);
3052 --
3053 open csr_leg_code;
3054 --
3055 fetch csr_leg_code into l_legislation_code;
3056 --
3057 if csr_leg_code%notfound then
3058 --
3059 close csr_leg_code;
3060 --
3061 -- The primary key is invalid therefore we must error
3062 --
3063 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
3064 hr_utility.raise_error;
3065 --
3066 end if;
3067 --
3068 close csr_leg_code;
3069 --
3070 hr_utility.set_location(' Leaving:'|| l_proc, 20);
3071 --
3072 return l_legislation_code;
3073 --
3074 end return_legislation_code;
3075 --
3076 end ben_ccm_bus;