1 Package Body ben_pln_bus as
2 /* $Header: beplnrhi.pkb 120.8.12010000.2 2008/08/18 09:47:19 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_pln_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------------< chk_pl_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 -- pl_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_pl_id(p_pl_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_pl_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_pln_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_pl_id => p_pl_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_pl_id,hr_api.g_number)
55 <> ben_pln_shd.g_old_rec.pl_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_pln_shd.constraint_error('BEN_PL_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_pl_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_pln_shd.constraint_error('BEN_PL_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_pl_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------------------------< chk_irec_pln_in_rptg_grp >----------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to ensure that change of plan type from "Individual
85 -- Compensation Distribution" plan type to plan type that is not of option
86 -- "Individual Compensation Distribution", is not allowed unless
87 -- the plan is not associated with the any reporting groups of type "iRecruitment"
88 -- Called from update_validate.
89 --
90 -- Pre Conditions
91 -- None.
92 --
93 -- In Parameters
94 -- pl_id PK of record being inserted or updated.
95 -- pl_typ_id Plan Type ID
96 -- effective_date effective date
97 -- validation_start_date Start date of the record
98 -- validation_end_date End date of the record
99 -- business_group_id Business group of the plan (null allowed in ben_pl_f)
100 -- object_version_number Object version number of record being
101 -- inserted or updated.
102 --
103 -- Post Success
104 -- Processing continues
105 --
106 -- Post Failure
107 -- Error handled by procedure
108 --
109 -- Access Status
110 -- Internal table handler use only.
111 --
112 Procedure chk_irec_pln_in_rptg_grp(p_pl_id in number,
113 p_pl_typ_id in number,
114 p_effective_date in date,
115 p_validation_start_date in date,
116 p_validation_end_date in date,
117 p_business_group_id in number,
118 p_object_version_number in number) is
119 --
120 l_proc varchar2(72) := g_package||'chk_irec_pln_in_rptg_grp';
121 l_api_updating boolean;
122 l_dummy varchar2(1);
123 l_old_opt_typ_cd varchar2(30);
124 l_new_opt_typ_cd varchar2(30);
125 --
126 cursor c1 is
127 select null
128 from ben_popl_rptg_grp_f rgr, ben_rptg_grp bnr
129 where rgr.pl_id = p_pl_id
130 and ( rgr.business_group_id = p_business_group_id or p_business_group_id is null)
131 and p_validation_start_date <= rgr.effective_end_date
132 and p_validation_end_date >= rgr.effective_Start_date
133 and rgr.rptg_grp_id = bnr.rptg_grp_id
134 and bnr.rptg_prps_cd = 'IREC'
135 and ( bnr.business_group_id = p_business_group_id or p_business_group_id is null);
136 --
137 cursor c_get_ptp_opt_typ_cd (p_pl_typ_id in number) is
138 select opt_typ_cd
139 from ben_pl_typ_f ptp
140 where ptp.pl_typ_id = p_pl_typ_id
141 and p_effective_date between ptp.effective_start_date and ptp.effective_end_date;
142 --
143
144 Begin
145 --
146 hr_utility.set_location('Entering:'||l_proc, 5);
147 --
148 l_api_updating := ben_pln_shd.api_updating
149 (p_pl_id => p_pl_id,
150 p_effective_date => p_effective_date,
151 p_object_version_number => p_object_version_number);
152 --
153 if (l_api_updating
154 and p_pl_typ_id <> nvl(ben_pln_shd.g_old_rec.pl_typ_id,hr_api.g_number) )
155 then
156 --
157 -- Get Option Code for old plan type
158 --
159 open c_get_ptp_opt_typ_cd(ben_pln_shd.g_old_rec.pl_typ_id);
160 fetch c_get_ptp_opt_typ_cd into l_old_opt_typ_cd;
161 --
162 if c_get_ptp_opt_typ_cd%found and l_old_opt_typ_cd = 'COMP'
163 then
164 --
165 close c_get_ptp_opt_typ_cd;
166 --
167 -- Get Option code for new plan type
168 --
169 open c_get_ptp_opt_typ_cd(p_pl_typ_id);
170 fetch c_get_ptp_opt_typ_cd into l_new_opt_typ_cd;
171 --
172 if c_get_ptp_opt_typ_cd%found and l_new_opt_typ_cd <> 'COMP'
173 then
174 --
175 --
176 -- Plan type has been changed from ICD to non ICD
177 --
178 open c1;
179 fetch c1 into l_dummy;
180 --
181 if c1%found
182 then
183 --
184 close c_get_ptp_opt_typ_cd;
185 close c1;
186 --
187 -- Raise error : as there is plan associated with the plan type being updated - which
188 -- is also associated to iRecruitment Reporting Group
189 --
190 fnd_message.set_name('BEN','BEN_93921_PL_RPTG_GRP_IREC');
191 fnd_message.raise_error;
192 --
193 end if;
194 --
195 close c1;
196 --
197 end if;
198 --close c_get_ptp_opt_typ_cd;
199 --
200 end if;
201 close c_get_ptp_opt_typ_cd;
202 --
203 end if;
204 --
205 hr_utility.set_location('Leaving:'||l_proc,10);
206 --
207 end chk_irec_pln_in_rptg_grp;
208 --
209 Procedure chk_compare_claims_cd (p_pl_id in number ,
210 p_effective_date in date,
211 p_business_group_id in number,
212 p_cmpr_clms_to_cvg_or_bal_cd in varchar2,
213 p_object_version_number in number) is
214 --
215 l_proc varchar2(300) := g_package||'chk_compare_claims_cd';
216 l_api_updating boolean;
217 l_dummy varchar2(1);
218 --
219 cursor c1 is
220 select null
221 from ben_prtt_reimbmt_rqst_f a
222 where a.business_group_id +0 = p_business_group_id
223 and a.pl_id = p_pl_id
224 and a.prtt_reimbmt_rqst_stat_cd <> 'VOIDD';
225 --
226 Begin
227 --
228 hr_utility.set_location('Entering:'||l_proc, 5);
229 --
230 l_api_updating := ben_pln_shd.api_updating
231 (p_effective_date => p_effective_date,
232 p_pl_id => p_pl_id,
233 p_object_version_number => p_object_version_number);
234 --
235 if (l_api_updating
236 and ben_pln_shd.g_old_rec.cmpr_clms_to_cvg_or_bal_cd is not null
237 and nvl(p_cmpr_clms_to_cvg_or_bal_cd,'zzz') <>
238 ben_pln_shd.g_old_rec.cmpr_clms_to_cvg_or_bal_cd )
239 then
240 --
241 open c1;
242 fetch c1 into l_dummy;
243 if c1%found then
244 --
245 close c1;
246 fnd_message.set_name('BEN','BEN_94621_CMPR_CLMS_CD');
247 fnd_message.raise_error;
248 --
249 end if;
250 close c1;
251 end if;
252 end;
253
254
255 -- ----------------------------------------------------------------------------
256
257 Procedure chk_pl_group_child(p_pl_id in number ,
258 p_opt_typ_cd in varchar2 ,
259 p_effective_date in date,
260 p_name in varchar2) is
261
262
263
264 cursor c_pl_cwb is
265 select plt.opt_typ_cd
266 from ben_pl_typ_f plt,
267 ben_pl_f pl
268 where pl.pl_id = p_pl_id
269 and plt.pl_typ_id = pl.pl_typ_id
270 and p_effective_date between plt.effective_start_date
271 and plt.effective_end_Date
272 and p_effective_date between pl.effective_start_date
273 and pl.effective_end_Date ;
274
275 cursor c_child_exist is
276 select 'x'
277 from ben_pl_f
278 where group_pl_id = p_pl_id
279 and pl_id <> p_pl_id
280 and effective_end_date > p_effective_date
281 ;
282 -- dont validate the date
283 l_dummy varchar2(1) ;
284 l_opt_typ_cd ben_pl_typ_f.opt_typ_cd%type ;
285 l_proc varchar2(72) := g_package||'chk_pl_group_child';
286
287 Begin
288
289 hr_utility.set_location('Entering:'||l_proc, 5);
290 l_opt_typ_cd := p_opt_typ_cd ;
291 if p_opt_typ_cd is null then
292 open c_pl_cwb ;
293 fetch c_pl_cwb into l_opt_typ_cd;
294 close c_pl_cwb ;
295 end if ;
296 if l_opt_typ_cd = 'CWB' then
297 open c_child_exist ;
298 fetch c_child_exist into l_dummy ;
299 if c_child_exist%found then
300 close c_child_exist ;
301 fnd_message.set_name('BEN','BEN_93724_CWB_CHILD_EXIST');
302 fnd_message.set_token('NAME', p_name); /* Bug 4057566 */
303 fnd_message.raise_error;
304 end if ;
305 close c_child_exist ;
306 end if ;
307 --
308 hr_utility.set_location('Leaving:'||l_proc, 10);
309 --
310
311 end chk_pl_group_child ;
312
313
314
315 Procedure chk_pl_group_id(p_pl_id in number,
316 p_group_pl_id in number,
317 p_pl_typ_id in number,
318 p_effective_date in date,
319 p_name in varchar2
320 ) is
321 --
322 l_proc varchar2(72) := g_package||'chk_pl_group_id';
323 l_api_updating boolean;
324 --
325 cursor c_parent_pl is
326 select 'x'
327 from ben_pl_f
328 where pl_id = p_group_pl_id
329 and pl_id = group_pl_id
330 and p_effective_date between effective_start_date
331 and effective_end_Date ;
332
333
334 cursor c_pl_cwb is
335 select opt_typ_cd
336 from ben_pl_typ_f plt
337 where plt.pl_typ_id = p_pl_typ_id
338 and plt.opt_typ_cd = 'CWB'
339 and p_effective_date between plt.effective_start_date
340 and plt.effective_end_Date ;
341
342
343
344 l_dummy varchar2(1) ;
345 l_opt_typ_cd ben_pl_typ_f.opt_typ_cd%type ;
346
347 --
348 Begin
349 --
350 hr_utility.set_location('Entering:'||l_proc, 5);
351 hr_utility.set_location('p_group_pl_id:'||p_group_pl_id, 5);
352
353 -- if the plan type is cwb and group_pl_id null throw the error
354 open c_pl_cwb ;
355 fetch c_pl_cwb into l_opt_typ_cd ;
356 if c_pl_cwb%found then
357 if p_group_pl_id is null then
358 close c_pl_cwb ;
359 fnd_message.set_name('BEN','BEN_93725_CWB_GROUP_PLN_NULL');
360 fnd_message.raise_error;
361 end if ;
362 end if ;
363 close c_pl_cwb ;
364
365 if p_group_pl_id is not null then
366 --check whether the plan belongs to CWB if not throw the error
367 open c_pl_cwb ;
368 fetch c_pl_cwb into l_opt_typ_cd ;
369 if c_pl_cwb%notfound then
370 close c_pl_cwb ;
371 fnd_message.set_name('BEN','BEN_93725_CWB_GROUP_PLN_NULL');
372 fnd_message.raise_error;
373 end if ;
374 close c_pl_cwb ;
375
376 -- when the plan is child check the parent is real parent
377 if p_pl_id <> p_group_pl_id then
378 open c_parent_pl ;
379 fetch c_parent_pl into l_dummy ;
380 if c_parent_pl%notfound then
381 close c_parent_pl ;
382 fnd_message.set_name('BEN','BEN_93726_CWB_PRTN_PLN_ERROR');
383 fnd_message.raise_error;
384 end if ;
385 close c_parent_pl ;
386
387 chk_pl_group_child(p_pl_id => p_pl_id ,
388 p_opt_typ_cd => l_opt_typ_cd ,
389 p_effective_date => p_effective_date,
390 p_name => p_name) ;
391 end if ;
392
393 end if ;
394 -- if the type got changed from cwb to non cwb validate the child
395 if ben_pln_shd.g_old_rec.group_pl_id is not null and p_group_pl_id is null then
396 chk_pl_group_child(p_pl_id => p_pl_id ,
397 p_opt_typ_cd => 'CWB' ,
398 p_effective_date => p_effective_date,
399 p_name => p_name) ;
400 end if ;
401 --
402 hr_utility.set_location('Leaving:'||l_proc, 10);
403 --
404 End chk_pl_group_id;
405 -- |------------------------< chk_plan_name_unique >--------------------------|
406 -- ----------------------------------------------------------------------------
407 --
408 -- Description
409 -- This procedure is used to check that the plan name is unique within a
410 -- business group.
411 --
412 -- Pre Conditions
413 -- None.
414 --
415 -- In Parameters
416 -- pl_id PK of record being inserted or updated.
417 -- name Plan name.
418 -- effective_date Session date of record.
419 -- business_group_id Business group id of record being inserted.
420 -- object_version_number Object version number of record being
421 -- inserted or updated.
422 --
423 -- Post Success
424 -- Processing continues
425 --
426 -- Post Failure
427 -- Errors handled by the procedure
428 --
429 -- Access Status
430 -- Internal table handler use only.
431 --
432 Procedure chk_plan_name_unique(p_pl_id in number,
433 p_name in varchar2,
434 p_effective_date in date,
435 p_business_group_id in number,
436 p_object_version_number in number,
437 p_validation_start_date in date,
438 p_validation_end_date in date) is
439 --
440 l_proc varchar2(72) := g_package||'chk_plan_name_unique';
441 l_api_updating boolean;
442 l_dummy varchar2(1);
443 --
444 cursor c1 is
445 select null
446 from ben_pl_f a
447 where a.business_group_id +0 = p_business_group_id
448 and a.pl_id <> nvl(p_pl_id,-1)
449 and a.name = p_name
450 and p_validation_start_date <= effective_end_date
451 and p_validation_end_date >= effective_start_date;
452 --
453 Begin
454 --
455 hr_utility.set_location('Entering:'||l_proc, 5);
456 --
457 l_api_updating := ben_pln_shd.api_updating
458 (p_effective_date => p_effective_date,
459 p_pl_id => p_pl_id,
460 p_object_version_number => p_object_version_number);
461 --
462 if (l_api_updating
463 and nvl(p_name,hr_api.g_varchar2)
464 <> ben_pln_shd.g_old_rec.name
465 or not l_api_updating) then
466 --
467 -- Check if plan name is unique.
468 --
469 open c1;
470 --
471 fetch c1 into l_dummy;
472 if c1%found then
473 --
474 close c1;
475 --
476 -- raise an error as this plan name has already been used
477 --
478 fnd_message.set_name('BEN','BEN_93570_PDW_PLAN_NAME_UNIQ');
479 fnd_message.raise_error;
480 --
481 end if;
482 --
483 close c1;
484 --
485 end if;
486 --
487 hr_utility.set_location('Leaving:'||l_proc, 10);
488 --
489 End chk_plan_name_unique;
490
491 --
492
493 -- ----------------------------------------------------------------------------
494 -- |-----------------------< chk_auto_enrt_and_mthd >--------------------------|
495 -- ----------------------------------------------------------------------------
496 --
497 -- Description
498 -- This procedure is used to check that if the automatic enrollment flag
499 -- flag is checked at OIPL, the plan record for the option must have the
500 -- enrollment method = "Automatic".
501
502 /* Bug# 4990426 */
503
504 Procedure chk_auto_enrt_and_mthd(p_pl_id in number,
505 p_effective_date in date,
506 p_enrt_mthd_cd in varchar2,
507 p_business_group_id in number) is
508 --
509 l_proc varchar2(72) := g_package||'chk_auto_enrt_and_mthd';
510 l_dummy_c1 varchar2(1);
511 --
512 cursor c1 is
513 select null
514 from ben_oipl_f cop
515 where cop.pl_id=p_pl_id
516 and cop.auto_enrt_flag = 'Y'
517 and p_effective_date between cop.effective_start_date
518 and cop.effective_end_date
519 and cop.business_group_id(+) = p_business_group_id;
520
521 --
522 Begin
523 --
524 hr_utility.set_location('Entering:'||l_proc, 5555);
525 --
526 if nvl(p_enrt_mthd_cd,'E') <> 'A' then
527 open c1;
528 --
529 fetch c1 into l_dummy_c1;
530 if c1%found then
531
532 -- raise an error as the Enrollment Method Code has a value of
533 -- "automatic".
534
535 close c1;
536 --
537 fnd_message.set_name('BEN','BEN_91967_AUTO_ENRT_AND_MTHD');
538 fnd_message.raise_error;
539 --
540 else
541 --
542 close c1;
543 --
544 end if;
545
546 end if;
547 --
548 hr_utility.set_location('Leaving:'||l_proc, 10);
549 --
550 End chk_auto_enrt_and_mthd;
551
552 -- Bug# 5710248
553 -- ----------------------------------------------------------------------------
554 -- |-----------------------< chk_pl_typ_change >--------------------------|
555 -- ----------------------------------------------------------------------------
556 --
557 -- Description
558 -- This procedure is used to check that if enrollments exist for a plan then
559 -- the plan type cant be changed.
560
561 procedure chk_pl_typ_change
562 (p_pl_id in number,
563 p_business_group_id in number,
564 p_pl_typ_id in number,
565 p_effective_date in date ,
566 p_object_version_number in number) is
567 --
568 l_proc varchar2(72) := g_package||'chk_pl_typ_change';
569 l_dummy varchar2(1);
570 l_api_updating boolean;
571 --
572 cursor c_epe_exist is
573 select null
574 from ben_elig_per_elctbl_chc epe,
575 ben_per_in_ler pil
576 where epe.pl_id = p_pl_id
577 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
578 and epe.per_in_ler_id = pil.per_in_ler_id
579 and epe.business_group_id = p_business_group_id
580 and epe.business_group_id = pil.business_group_id;
581 --
582 Begin
583 --
584 hr_utility.set_location('Entering:'||l_proc, 5555);
585 --
586 l_api_updating := ben_pln_shd.api_updating
587 (p_effective_date => p_effective_date,
588 p_pl_id => p_pl_id,
589 p_object_version_number => p_object_version_number);
590 --
591 if (l_api_updating
592 and p_pl_typ_id <> nvl(ben_pln_shd.g_old_rec.pl_typ_id,hr_api.g_number) ) then
596 fetch c_epe_exist into l_dummy;
593 --
594 open c_epe_exist;
595 --
597 --
598 if c_epe_exist%found then
599 --
600 close c_epe_exist;
601 --
602 fnd_message.set_name('BEN','BEN_91024_PEP_EXISTS');
603 fnd_message.raise_error;
604 --
605 else
606 --
607 close c_epe_exist;
608 --
609 end if;
610 --
611 end if;
612 --
613 hr_utility.set_location('Leaving:'||l_proc, 10);
614 --
615 end chk_pl_typ_change;
616
617 -- Bug# 5710248
618 -- ----------------------------------------------------------------------------
619 -- |--------------------< chk_cwb_plan_type_uniq_plan >------------------------|
620 -- ----------------------------------------------------------------------------
621 --
622 -- Description
623 -- This procedure is used to check that the plan name is unique across all
624 -- business groups if the associated plan type has option type "Compensation Workbench"
625 -- and it is a group plan.
626 --
627 -- Bug : 3475996
628 --
629 -- Pre Conditions
630 -- None.
631 --
632 -- In Parameters
633 -- pl_id PK of record being inserted or updated.
634 -- name Plan name.
635 -- pl_typ_id Id of Plan Type selected.
636 -- group_pl_id Id of Group Plan to which the plan is associated.
637 -- effective_date Session date of record.
638 -- business_group_id Business group id of record being inserted.
639 -- object_version_number Object version number of record being
640 -- inserted or updated.
641 -- validation_start_date Validation start date of the record.
642 -- validation_end_date Validation end date of the record.
643 --
644 -- Post Success
645 -- Processing continues
646 --
647 -- Post Failure
648 -- Errors handled by the procedure
649 --
650 -- Access Status
651 -- Internal table handler use only.
652 --
653 Procedure chk_cwb_plan_type_uniq_plan(p_pl_id in number,
654 p_name in varchar2,
655 p_pl_typ_id in number,
656 p_group_pl_id in number,
657 p_effective_date in date,
658 p_business_group_id in number,
659 p_object_version_number in number,
660 p_validation_start_date in date,
661 p_validation_end_date in date) is
662 --
663 l_proc varchar2(72) := g_package||'chk_cwb_plan_type_uniq_plan';
664 l_api_updating boolean;
665 l_dummy varchar2(1);
666 l_opt_typ_cd varchar2(30);
667 l_exist_pl_id number;
668 l_exist_group_pl_id number;
669 --
670 cursor c1 is
671 select opt_typ_cd
672 from ben_pl_typ_f
673 where pl_typ_id = p_pl_typ_id
674 and business_group_id = p_business_group_id
675 and p_effective_date between effective_start_date and effective_end_date;
676 --
677 cursor c2 is
678 select null
679 from ben_pl_f pln, ben_pl_typ_f ptp
680 where pln.pl_typ_id = ptp.pl_typ_id
681 and ptp.opt_typ_cd = 'CWB'
682 and pln.pl_id <> nvl(p_pl_id,-1)
683 and pln.name = p_name
684 and p_validation_start_date <= pln.effective_end_date
685 and p_validation_end_date >= pln.effective_start_date
686 and p_effective_date between ptp.effective_start_date and ptp.effective_end_date;
687 --
688 cursor c3 is
689 select null
690 from ben_pl_f pln, ben_pl_typ_f ptp
691 where pln.pl_typ_id = ptp.pl_typ_id
692 and ptp.opt_typ_cd = 'CWB'
693 and pln.pl_id <> nvl(p_pl_id,-1)
694 and pln.name = p_name
695 and pln.pl_id = nvl(pln.group_pl_id, pl_id)
696 and p_validation_start_date <= pln.effective_end_date
697 and p_validation_end_date >= pln.effective_start_date
698 and p_effective_date between ptp.effective_start_date and ptp.effective_end_date;
699 --
700 Begin
701 --
702 hr_utility.set_location('Entering:'||l_proc, 5);
703 --
704 --
705 -- Get the Option Type of the Plan Type
706 --
707 open c1;
708 fetch c1 into l_opt_typ_cd;
709 if ( c1%found )
710 and (l_opt_typ_cd = 'CWB') then
711 --
712 if nvl(p_pl_id,-1) = nvl(p_group_pl_id,nvl(p_pl_id,-1)) then
713 open c2;
714 fetch c2 into l_dummy;
715 if c2%found then
716 --
717 close c1;
718 close c2;
719 --
720 fnd_message.set_name('BEN','BEN_93905_GRP_PLN_NAME_UNIQUE');
721 fnd_message.raise_error;
722 --
723 end if;
724 close c2;
725 --
726 else
727 --
728 open c3;
729 fetch c3 into l_dummy;
730 if c3%found then
731 --
732 close c1;
733 close c3;
734 --
735 fnd_message.set_name('BEN','BEN_93905_GRP_PLN_NAME_UNIQUE');
736 fnd_message.raise_error;
737 --
738 --
739 end if;
740 close c3;
741 --
742 end if;
743 --
744 end if;
745 --
746 close c1;
747 --
748 hr_utility.set_location('Leaving:'||l_proc, 10);
749 --
750 End chk_cwb_plan_type_uniq_plan;
754 -- |------------------------< chk_measures_allowed >--------------------------|
751
752 --
753 -- ----------------------------------------------------------------------------
755 -- ----------------------------------------------------------------------------
756 --
757 -- Description
758 -- This procedure is used to check that if the Measures Allowed Code value is
759 -- 'Percent Allowed Only', only the Beneficiary Increment Percent and Minimum
760 -- Designatable Percent should be allowed to be saved.
761 --
762 -- Pre Conditions
763 -- None.
764 --
765 -- In Parameters
766 -- bnf_pct_amt_alwd_cd Measures Allowed Code
767 -- bnf_incrmt_amt Increment Amount Allowed
768 -- bnf_pct_incrmt_val Percent Increment Allowed
769 -- bnf_mn_dsgntbl_amt Minimum Designatable Percent
770 --
771 -- Post Success
772 -- Processing continues
773 --
774 -- Post Failure
775 -- Errors handled by the procedure
776 --
777 -- Access Status
778 -- Internal table handler use only.
779 Procedure chk_measures_allowed(p_bnf_pct_amt_alwd_cd in varchar2,
780 p_bnf_incrmt_amt in number,
781 p_bnf_pct_incrmt_val in number,
782 p_bnf_mn_dsgntbl_amt in number) is
783 --
784 l_proc varchar2(72) := g_package||'chk_measures_allowed';
785 --
786 --
787 Begin
788 --
789 hr_utility.set_location('Entering:'||l_proc, 5);
790 --
791 if (p_bnf_pct_amt_alwd_cd = 'PCTO' and
792 p_bnf_incrmt_amt is not null) then
793 --
794 -- raise an error as this measures amount code only allows percent
795 -- increments
796 --
797 fnd_message.set_name('BEN','BEN_91617_INCR_AMT_NOT_ALWD');
798 fnd_message.raise_error;
799 --
800 --
801 end if;
802 --
803 if (p_bnf_pct_amt_alwd_cd = 'PCTO' and
804 p_bnf_mn_dsgntbl_amt is not null) then
805 --
806 -- raise an error as this measures amount code only allows percent
807 -- increments
808 --
809 fnd_message.set_name('BEN','BEN_91617_INCR_AMT_NOT_ALWD');
810 fnd_message.raise_error;
811 --
812 end if;
813 --
814 hr_utility.set_location('Leaving:'||l_proc, 10);
815 --
816 End chk_measures_allowed;
817 --
818 -- Code Appended for Bug# 2334297
819 -- ----------------------------------------------------------------------------
820 -- |---------------------------------< chk_dflt_to_asn_pndg_ctfn_cd >----------------------------|
821 -- ----------------------------------------------------------------------------
822 --
823 -- Description
824 -- This procedure is used to check the Interim Assign Code.
825 -- If the Coverage Calculation is set to Flat Amount (FLFX)
826 -- and Enter Value at Enrollment is checked in the Coverages Form,
827 -- this procedure will not allow to set the interim assign code to
828 -- Next Lower
829 --
830 -- Pre Conditions
831 -- None.
832 --
833 -- In Parameters
834 -- pl_id PK of record being inserted or updated.
835 -- effective_date Effective Date of session
836 --
837 -- Post Success
838 -- Processing continues
839 --
840 -- Post Failure
841 -- Errors handled by the procedure
842 --
843 -- Access Status
844 -- Internal table handler use only.
845 --
846 Procedure chk_dflt_to_asn_pndg_ctfn_cd(
847 p_dflt_to_asn_pndg_ctfn_cd in varchar2,
848 p_pl_id in number,
849 p_effective_date in date,
850 p_business_group_id in number,
851 p_object_version_number in number) is
852 --
853 l_proc varchar2(72) := g_package||'chk_dflt_to_asn_pndg_ctfn_cd';
854 l_api_updating boolean;
855 l_dummy varchar2(1);
856 --
857 cursor c1 is
858 select null from BEN_CVG_AMT_CALC_MTHD_F cvg
859 where nvl(cvg.pl_id,-1) = p_pl_id
860 and cvg.cvg_mlt_cd = 'FLFX'
861 and cvg.entr_val_at_enrt_flag = 'Y'
862 and cvg.business_group_id = p_business_group_id
863 and p_effective_date between cvg.effective_start_date and cvg.effective_end_date;
864 --
865 Begin
866 --
867 hr_utility.set_location('Entering:'||l_proc, 5);
868 --
869 l_api_updating := ben_pln_shd.api_updating
870 (p_effective_date => p_effective_date,
871 p_pl_id => p_pl_id,
872 p_object_version_number => p_object_version_number);
873 --
874 if ( (l_api_updating
875 and nvl(p_dflt_to_asn_pndg_ctfn_cd,hr_api.g_varchar2)
876 <> nvl(ben_pln_shd.g_old_rec.dflt_to_asn_pndg_ctfn_cd, '***'))
877 or not l_api_updating)
878 and p_dflt_to_asn_pndg_ctfn_cd is not null then
879 --
880 if (instr(p_dflt_to_asn_pndg_ctfn_cd,'NL'))>0 then
881 --
882 open c1;
883 fetch c1 into l_dummy;
884 if c1%found then
885 --
886 close c1;
887 hr_utility.set_location('Inside :'||l_proc, 8);
888 fnd_message.set_name('BEN', 'BEN_93113_CD_CANNOT_NEXTLOWER');
889 fnd_message.raise_error;
890 --
891 else
892 --
893 close c1;
894 --
895 end if;
899 end if;
896 --
897 end if; -- End of instr end if
898 --
900 --
901 hr_utility.set_location('Leaving:'||l_proc, 10);
902 --
903 End chk_dflt_to_asn_pndg_ctfn_cd;
904
905 -- End of code Append Bug# 2334297
906 -- ----------------------------------------------------------------------------
907 --
908 --
909 -- ----------------------------------------------------------------------------
910 -- |------< chk_flag_and_val >------|
911 -- ----------------------------------------------------------------------------
912 --
913 -- Description
914 -- This procedure is used to check if the flag is turned on then
915 -- the value must be null
916 --
917 -- Pre Conditions
918 -- None.
919 --
920 -- In Parameters
921 -- P_FLAG value of flag item.
922 -- P_VAL value of value item
923 -- P_MSG message name to dispaly if validation fails
924 --
925 -- Post Success
926 -- Processing continues
927 --
928 -- Post Failure
929 -- Error handled by procedure
930 --
931 -- Access Status
932 -- Internal table handler use only.
933 --
934 Procedure chk_flag_and_val(p_flag in varchar2,
935 p_val in number,
936 p_msg in varchar2) is
937 --
938 l_proc varchar2(72) := g_package||'chk_flag_and_val';
939 --
940 Begin
941 --
942 hr_utility.set_location('Entering:'||l_proc, 5);
943 --
944 if p_flag = 'Y' and p_val is not null then
945 fnd_message.set_name('BEN', p_msg);
946 fnd_message.raise_error;
947 end if;
948 --
949 hr_utility.set_location('Leaving:'||l_proc,10);
950 --
951 end chk_flag_and_val;
952 --
953 --
954 -- ----------------------------------------------------------------------------
955 -- |------< chk_code_rule_dpnd >------|
956 -- ----------------------------------------------------------------------------
957 --
958 -- Description
959 -- This procedure is used to check that the Rule is only allowed to
960 -- have a value if the value of the Code = 'Rule', and if code is
961 -- = RL then p_rule must have a value.
962 --
963 -- Pre Conditions
964 -- None.
965 --
966 -- In Parameters
967 -- P_CODE value of code item.
968 -- P_RULE value of rule item
969 --
970 -- Post Success
971 -- Processing continues
972 --
973 -- Post Failure
974 -- Error handled by procedure
975 --
976 -- Access Status
977 -- Internal table handler use only.
978 --
979 Procedure chk_code_rule_dpnd(p_code in varchar2,
980 p_rule in number) is
981 --
982 l_proc varchar2(72) := g_package||'chk_code_rule_dpnd';
983 --
984 Begin
985 --
986 hr_utility.set_location('Entering:'||l_proc, 5);
987 --
988 if p_code <> 'RL' and p_rule is not null then
989 --
990 fnd_message.set_name('BEN','BEN_91624_CD_RL_2');
991 fnd_message.raise_error;
992 --
993 elsif p_code = 'RL' and p_rule is null then
994 --
995 fnd_message.set_name('BEN','BEN_91623_CD_RL_1');
996 fnd_message.raise_error;
997 --
998 end if;
999 --
1000 hr_utility.set_location('Leaving:'||l_proc,10);
1001 --
1002 end chk_code_rule_dpnd;
1003 --
1004 -- ----------------------------------------------------------------------------
1005 -- |------< chk_mn_val_mn_flag_mn_rule >------|
1006 -- ----------------------------------------------------------------------------
1007 --
1008 -- Description
1009 -- This procedure is used to check that either the minimum value, no
1010 -- minimum flag, or the minimum rule is entered. More than one of the
1011 -- above mentioned may not have be entered.
1012 --
1013 -- Pre Conditions
1014 -- None.
1015 --
1016 -- In Parameters
1017 -- p_mn_cvg_rqd_amt value of Minimum Value
1018 -- p_no_mn_cvg_amt_apls_flag value of No Minimum Flag
1019 -- p_mn_cvg_rl value of Minimum Rule
1020 --
1021 -- Post Success
1022 -- Processing continues
1023 --
1024 -- Post Failure
1025 -- Error handled by procedure
1026 --
1027 -- Access Status
1028 -- Internal table handler use only.
1029 --
1030 Procedure chk_mn_val_mn_flag_mn_rule(p_mn_cvg_rqd_amt in number,
1031 p_no_mn_cvg_amt_apls_flag in varchar2,
1032 p_mn_cvg_rl in number) is
1033 --
1034 l_proc varchar2(72) := g_package||'chk_mn_val_mn_flag_mn_rule';
1035 --
1036 Begin
1037 --
1038 hr_utility.set_location('Entering:'||l_proc, 5);
1039 --
1040 if p_mn_cvg_rqd_amt is not null and (p_no_mn_cvg_amt_apls_flag = 'Y' or
1041 p_mn_cvg_rl is not null) then
1042 --
1043 fnd_message.set_name('BEN','BEN_91945_MN_VAL_FLAG_RULE');
1044 fnd_message.raise_error;
1045 --
1046 elsif p_mn_cvg_rl is not null and (p_no_mn_cvg_amt_apls_flag = 'Y' or
1047 p_mn_cvg_rqd_amt is not null) then
1048 --
1049 fnd_message.set_name('BEN','BEN_91945_MN_VAL_FLAG_RULE');
1050 fnd_message.raise_error;
1051 --
1055 fnd_message.set_name('BEN','BEN_91945_MN_VAL_FLAG_RULE');
1052 elsif p_no_mn_cvg_amt_apls_flag = 'Y' and (p_mn_cvg_rqd_amt is not null or
1053 p_mn_cvg_rl is not null) then
1054 --
1056 fnd_message.raise_error;
1057 --
1058 end if;
1059 --
1060 hr_utility.set_location('Leaving:'||l_proc,10);
1061 --
1062 end chk_mn_val_mn_flag_mn_rule;
1063
1064
1065 --
1066 --
1067 --
1068 Procedure chk_vrfy_fmly_mmbr_rl
1069 (p_pl_id in number
1070 ,p_vrfy_fmly_mmbr_rl in number
1071 ,p_business_group_id in number
1072 ,p_effective_date in date
1073 ,p_object_version_number in number)
1074 is
1075 --
1076 l_proc varchar2(72) := g_package||'chk_vrfy_fmly_mmbr_rl';
1077 l_api_updating boolean;
1078 --
1079 Begin
1080 --
1081 hr_utility.set_location('Entering:'||l_proc, 5);
1082 --
1083 l_api_updating := ben_pln_shd.api_updating
1084 (p_effective_date => p_effective_date,
1085 p_pl_id => p_pl_id,
1086 p_object_version_number => p_object_version_number);
1087 --
1088 if (l_api_updating
1089 and nvl(p_vrfy_fmly_mmbr_rl,hr_api.g_number)
1090 <> ben_pln_shd.g_old_rec.vrfy_fmly_mmbr_rl
1091 or not l_api_updating)
1092 and p_vrfy_fmly_mmbr_rl is not null then
1093 --
1094 -- check if value of formula rule is valid.
1095 --
1096 if not benutils.formula_exists
1097 (p_formula_id => p_vrfy_fmly_mmbr_rl,
1098 p_formula_type_id => -21,
1099 p_business_group_id => p_business_group_id,
1100 p_effective_date => p_effective_date) then
1101 --
1102 -- raise error
1103
1104 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1105 fnd_message.set_token('ID',p_vrfy_fmly_mmbr_rl);
1106 fnd_message.set_token('TYPE_ID',-21);
1107 fnd_message.raise_error;
1108 --
1109 end if;
1110 --
1111 end if;
1112 --
1113 hr_utility.set_location('Leaving:'||l_proc,10);
1114 --
1115 end chk_vrfy_fmly_mmbr_rl;
1116 --
1117
1118 -- ----------------------------------------------------------------------------
1119 -- |------< chk_dpnt_dsgn_cd >------------------------------------------------|
1120 -- ----------------------------------------------------------------------------
1121 --
1122 -- Description
1123 -- This procedure is used to check that
1124 -- 1. if p_dpnt_dsgn_cd is not null then p_dpnt_cvg_strt_dt_cd and
1125 -- p_dpnt_cvg_end_dt_cd should also be not null.
1126 -- 2. if p_dpnt_dsgn_cd is null then p_dpnt_cvg_strt_dt_cd and
1127 -- p_dpnt_cvg_end_dt_cd should also be null.
1128 --
1129 -- Pre Conditions
1130 -- None.
1131 --
1132 -- In Parameters
1133 -- p_dpnt_dsgn_cd value of Plan Dependent Designation
1134 -- p_dpnt_cvg_strt_dt_cd value of Dependent Coverage Start Date
1135 -- p_dpnt_cvg_end_dt_cd value of Dependent Coverage End Date
1136 --
1137 -- Post Success
1138 -- Processing continues
1139 --
1140 -- Post Failure
1141 -- Error handled by procedure
1142 --
1143 -- Access Status
1144 -- Internal table handler use only.
1145 --
1146 --
1147 Procedure chk_dpnt_dsgn_cd(p_dpnt_dsgn_cd in varchar2,
1148 p_dpnt_cvg_strt_dt_cd in varchar2,
1149 p_dpnt_cvg_end_dt_cd in varchar2) is
1150 --
1151 l_proc varchar2(72) := g_package||'chk_dpnt_dsgn_cd';
1152 --
1153 Begin
1154 --
1155 hr_utility.set_location('Entering:'||l_proc, 5);
1156 --
1157 if (p_dpnt_dsgn_cd is not null) and
1158 (p_dpnt_cvg_strt_dt_cd is null or p_dpnt_cvg_end_dt_cd is null) then
1159
1160 fnd_message.set_name('BEN','BEN_92512_DPNDNT_CVRG_DT_RQD');
1161 fnd_message.raise_error;
1162 end if;
1163 --
1164
1165 if (p_dpnt_dsgn_cd is null) and
1166 (p_dpnt_cvg_strt_dt_cd is not null or p_dpnt_cvg_end_dt_cd is not null) then
1167 fnd_message.set_name('BEN','BEN_91375_PGM_DPNT_DSGN_RQD');
1168 fnd_message.raise_error;
1169 end if;
1170
1171 --
1172 hr_utility.set_location('Leaving:'||l_proc,10);
1173 --
1174 --
1175 end chk_dpnt_dsgn_cd;
1176 --
1177 --
1178
1179 Procedure chk_vrfy_fmly_mmbr_cd(p_pl_id in number,
1180 p_vrfy_fmly_mmbr_cd in varchar2,
1181 p_effective_date in date,
1182 p_object_version_number in number) is
1183 --
1184 l_proc varchar2(72) := g_package||'chk_vrfy_fmly_mmbr_cd';
1185 l_api_updating boolean;
1186 --
1187 Begin
1188 --
1189 hr_utility.set_location('Entering:'||l_proc, 5);
1190 --
1191 l_api_updating := ben_pln_shd.api_updating
1192 (p_pl_id => p_pl_id,
1193 p_effective_date => p_effective_date,
1194 p_object_version_number => p_object_version_number);
1195 --
1196 if (l_api_updating
1197 and p_vrfy_fmly_mmbr_cd
1198 <> nvl(ben_pln_shd.g_old_rec.vrfy_fmly_mmbr_cd,hr_api.g_varchar2)
1199 or not l_api_updating)
1200 and p_vrfy_fmly_mmbr_cd is not null
1201 then
1202 --
1206 if hr_api.not_exists_in_hr_lookups
1203 -- check if value of lookup falls within lookup type.
1204 --
1205
1207 (p_lookup_type => 'BEN_FMLY_MMBR',
1208 p_lookup_code => p_vrfy_fmly_mmbr_cd,
1209 p_effective_date => p_effective_date) then
1210 --
1211 -- raise error as does not exist as lookup
1212 --
1213 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1214 fnd_message.set_token('FIELD', 'p_vrfy_fmly_mmbr_cd');
1215 fnd_message.set_token('TYPE','BEN_FMLY_MMBR');
1216 fnd_message.raise_error;
1217 --
1218 end if;
1219 --
1220 end if;
1221 --
1222 hr_utility.set_location('Leaving:'||l_proc,10);
1223 --
1224 end chk_vrfy_fmly_mmbr_cd;
1225 --
1226
1227 Procedure chk_use_csd_rsd_prccng_cd(p_pl_id in number,
1228 p_use_csd_rsd_prccng_cd in varchar2,
1229 p_effective_date in date,
1230 p_pl_cd in varchar2,
1231 p_object_version_number in number) is
1232 --
1233 l_proc varchar2(72) := g_package||'chk_use_csd_rsd_prccng_cd';
1234 l_api_updating boolean;
1235 --
1236 --
1237 begin
1238 hr_utility.set_location('Entering:'||l_proc, 5);
1239 l_api_updating := ben_pln_shd.api_updating
1240 (p_pl_id => p_pl_id,
1241 p_effective_date => p_effective_date,
1242 p_object_version_number => p_object_version_number);
1243 --
1244 if (l_api_updating
1245 and p_use_csd_rsd_prccng_cd
1246 <> nvl(ben_pln_shd.g_old_rec.use_csd_rsd_prccng_cd,hr_api.g_varchar2)
1247 or not l_api_updating)
1248 and p_use_csd_rsd_prccng_cd is not null
1249 then
1250 --
1251 -- check if value of lookup falls within lookup type.
1252 --
1253
1254 if hr_api.not_exists_in_hr_lookups
1255 (p_lookup_type => 'BEN_USE_CSD_RSD_PRCCNG',
1256 p_lookup_code => p_use_csd_rsd_prccng_cd,
1257 p_effective_date => p_effective_date) then
1258 --
1259 -- raise error as does not exist as lookup
1260 --
1261 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1262 fnd_message.set_token('FIELD', 'p_use_csd_rsd_prccng_cd');
1263 fnd_message.set_token('TYPE','BEN_USE_CSD_RSD_PRCCNG');
1264 fnd_message.raise_error;
1265 --
1266 end if ;
1267 --
1268 end if;
1269 --
1270 if p_use_csd_rsd_prccng_cd is not null and p_pl_cd <> 'MYNTBPGM' then
1271 fnd_message.set_name('BEN','BEN_93948_CSD_RSD_PLIP');
1272 fnd_message.raise_error;
1273 end if ;
1274
1275 hr_utility.set_location('Leaving:'||l_proc,10);
1276 --
1277 --
1278 end chk_use_csd_rsd_prccng_cd;
1279 --
1280
1281
1282 -- ----------------------------------------------------------------------------
1283 -- |------< chk_dpnt_dsgn_cd_detail >------|
1284 -- ----------------------------------------------------------------------------
1285 --
1286 -- Description
1287 -- If dependent designation code is null then the following tables must
1288 -- contain no records for that program: BEN_LER_CHG_DPNT_CVG_F,
1289 -- BEN_APLD_DPNT_CVG_ELIG_PRFL_F, BEN_PL_DPNT_CVG_CTFN_F.
1290 --
1291 -- Pre Conditions
1292 -- None.
1293 --
1294 -- In Parameters
1295 -- pl_id PK of record being inserted or updated.
1296 -- dpnt_dsgn_cd Value of lookup code.
1297 -- pl_id
1298 -- business_group_id
1299 -- effective_date effective date
1300 -- object_version_number Object version number of record being
1301 -- inserted or updated.
1302 --
1303 -- Post Success
1304 -- Processing continues
1305 --
1306 -- Post Failure
1307 -- Error handled by procedure
1308 --
1309 -- Access Status
1310 -- Internal table handler use only.
1311 --
1312 Procedure chk_dpnt_dsgn_cd_detail(p_pl_id in number,
1313 p_dpnt_dsgn_cd in varchar2,
1314 p_bnf_dsgn_cd in varchar2,
1315 p_business_group_id in number,
1316 p_effective_date in date,
1317 p_validation_start_date in date,-- bug 3981774
1318 p_validation_end_date in date,-- bug 3981774
1319 p_object_version_number in number) is
1320 --
1321 l_proc varchar2(72) := g_package || 'chk_dpnt_dsgn_cd_detail';
1322 l_api_updating boolean;
1323 l_dummy varchar2(1);
1324 --
1325 cursor c1 is select 'x'
1326 from ben_ler_chg_dpnt_cvg_f ldc1
1327 where ldc1.pl_id = p_pl_id
1328 and ldc1.business_group_id + 0 = p_business_group_id
1329 /* start of bug 3981774
1330 and p_effective_date between ldc1.effective_start_date
1331 and ldc1.effective_end_date; */
1332 and p_validation_start_date <= ldc1.effective_end_date
1333 and p_validation_end_date >= ldc1.effective_start_date ; /* end bug 3981774 */
1334
1335 --bug 3981774 fix similar to c1 is done for c2,c3,c4
1336 cursor c2 is select 'x'
1340 /*and p_effective_date between ade1.effective_start_date
1337 from ben_apld_dpnt_cvg_elig_prfl_f ade1
1338 where ade1.pl_id = p_pl_id
1339 and ade1.business_group_id + 0 = p_business_group_id
1341 and ade1.effective_end_date;*/
1342 and p_validation_start_date <= ade1.effective_end_date
1343 and p_validation_end_date >= ade1.effective_start_date ; /* end bug 3981774 */
1344
1345 --
1346 cursor c3 is select 'x'
1347 from ben_pl_dpnt_cvg_ctfn_f pnd
1348 where pnd.pl_id = p_pl_id
1349 and pnd.business_group_id + 0 = p_business_group_id
1350 /* and p_effective_date between pnd.effective_start_date
1351 and pnd.effective_end_date;*/
1352 and p_validation_start_date <= pnd.effective_end_date
1353 and p_validation_end_date >= pnd.effective_start_date ; /* end bug 3981774 */
1354
1355 --
1356 cursor c4 is select 'x'
1357 from ben_pl_bnf_ctfn_f pcx
1358 where pcx.pl_id = p_pl_id
1359 and pcx.business_group_id + 0 = p_business_group_id
1360 /*and p_effective_date between pcx.effective_start_date
1361 and pcx.effective_end_date;*/
1362 and p_validation_start_date <= pcx.effective_end_date
1363 and p_validation_end_date >= pcx.effective_start_date ; /* end bug 3981774 */
1364
1365 --
1366 Begin
1367 --
1368 hr_utility.set_location('Entering:'||l_proc, 5);
1369 --
1370 -- If ldc1 records exists and designation is null then error
1371 --
1372 if (p_dpnt_dsgn_cd is null) then
1373 open c1;
1374 fetch c1 into l_dummy;
1375 if c1%found then
1376 --
1377 close c1;
1378 fnd_message.set_name('BEN','BEN_92524_DELETE_LDC2');
1379 fnd_message.raise_error;
1380 --
1381 else
1382 close c1;
1383 end if;
1384 --
1385 end if;
1386 --
1387 -- If ade1 records exists and designation is null then error
1388 --
1389 if (p_dpnt_dsgn_cd is null) then
1390 open c2;
1391 fetch c2 into l_dummy;
1392 if c2%found then
1393 --
1394 close c2;
1395 fnd_message.set_name('BEN','BEN_92523_DELETE_ADE2');
1396 fnd_message.raise_error;
1397 --
1398 else
1399 close c2;
1400 end if;
1401 --
1402 end if;
1403 --
1404 -- If pnd records exists and designation is null then error
1405 --
1406 if (p_dpnt_dsgn_cd is null) then
1407 open c3;
1408 fetch c3 into l_dummy;
1409 if c3%found then
1410 --
1411 close c3;
1412 fnd_message.set_name('BEN','BEN_92522_DELETE_PND');
1413 fnd_message.raise_error;
1414 --
1415 else
1416 close c3;
1417 end if;
1418 --
1419 end if;
1420 --
1421 -- If pcx records exists and designation is null then error
1422 --
1423 if (p_bnf_dsgn_cd is null) then
1424 open c4;
1425 fetch c4 into l_dummy;
1426 if c4%found then
1427 --
1428 close c4;
1429 fnd_message.set_name('BEN','BEN_92525_DELETE_PCX');
1430 fnd_message.raise_error;
1431 --
1432 else
1433 close c4;
1434 end if;
1435 --
1436 end if;
1437 --
1438 --
1439 --
1440 hr_utility.set_location('Leaving:'||l_proc,10);
1441 --
1442 end chk_dpnt_dsgn_cd_detail;
1443 --
1444 -- ----------------------------------------------------------------------------
1445 -- |------< chk_mx_val_mx_flag_mx_rule >------|
1446 -- ----------------------------------------------------------------------------
1447 --
1448 -- Description
1449 -- This procedure is used to check that either the maximum value, no
1450 -- maximum flag, or the maximum rule is entered. More than one of the
1451 -- above mentioned may not have be entered.
1452 --
1453 -- Pre Conditions
1454 -- None.
1455 --
1456 -- In Parameters
1457 -- p_mx_cvg_alwd_amt value of Minimum Value
1458 -- p_no_mx_cvg_amt_apls_flag value of No Minimum Flag
1459 -- p_mx_cvg_rl value of Minimum Rule
1460 --
1461 -- Post Success
1462 -- Processing continues
1463 --
1464 -- Post Failure
1465 -- Error handled by procedure
1466 --
1467 -- Access Status
1468 -- Internal table handler use only.
1469 --
1470 Procedure chk_mx_val_mx_flag_mx_rule(p_mx_cvg_alwd_amt in number,
1471 p_no_mx_cvg_amt_apls_flag in varchar2,
1472 p_mx_cvg_rl in number) is
1473 --
1474 l_proc varchar2(72) := g_package||'chk_mx_val_mx_flag_mx_rule';
1475 --
1476 Begin
1477 --
1478 hr_utility.set_location('Entering:'||l_proc, 5);
1479 --
1480 if p_mx_cvg_alwd_amt is not null and (p_no_mx_cvg_amt_apls_flag = 'Y' or
1481 p_mx_cvg_rl is not null) then
1482 --
1486 elsif p_mx_cvg_rl is not null and (p_no_mx_cvg_amt_apls_flag = 'Y' or
1483 fnd_message.set_name('BEN','BEN_91946_MX_VAL_FLAG_RULE');
1484 fnd_message.raise_error;
1485 --
1487 p_mx_cvg_alwd_amt is not null) then
1488 --
1489 fnd_message.set_name('BEN','BEN_91946_MX_VAL_FLAG_RULE');
1490 fnd_message.raise_error;
1491 --
1492 elsif p_no_mx_cvg_amt_apls_flag = 'Y' and (p_mx_cvg_alwd_amt is not null or
1493 p_mx_cvg_rl is not null) then
1494 --
1495 fnd_message.set_name('BEN','BEN_91946_MX_VAL_FLAG_RULE');
1496 fnd_message.raise_error;
1497 --
1498 end if;
1499 --
1500 hr_utility.set_location('Leaving:'||l_proc,10);
1501 --
1502 end chk_mx_val_mx_flag_mx_rule;
1503 --
1504 -- ----------------------------------------------------------------------------
1505 -- |------------------------< chk_mutual_exclusive_flags >--------------------|
1506 -- ----------------------------------------------------------------------------
1507 --
1508 -- Description
1509 -- This procedure is used to check that the mutual exclusive flags
1510 -- invk_dcln_elig_pl_flag
1511 -- invk_flx_cr_pl_flag
1512 -- svgs_pl_flag
1513 -- are set correctly. Either one may be equal to yes or they may all be no.
1514 --
1515 -- Pre Conditions
1516 -- None.
1517 --
1518 -- In Parameters
1519 -- pl_id PK of record being inserted or updated.
1520 -- invk_dcln_prtn_pl_flag flag.
1521 -- invk_flx_cr_pl_flag flag.
1522 -- svgs_pl_flag flag.
1523 -- effective_date Session date of record.
1524 -- object_version_number Object version number of record being
1525 -- inserted or updated.
1526 --
1527 -- Post Success
1528 -- Processing continues
1529 --
1530 -- Post Failure
1531 -- Errors handled by the procedure
1532 --
1533 -- Access Status
1534 -- Internal table handler use only.
1535 --
1536 Procedure chk_mutual_exclusive_flags(p_pl_id in number,
1537 p_invk_dcln_prtn_pl_flag in varchar2,
1538 p_invk_flx_cr_pl_flag in varchar2,
1539 p_svgs_pl_flag in varchar2,
1540 p_effective_date in date,
1541 p_object_version_number in number) is
1542 --
1543 l_proc varchar2(72) := g_package||'chk_mutual_exclusive_flags';
1544 l_api_updating boolean;
1545 --
1546 Begin
1547 --
1548 hr_utility.set_location('Entering:'||l_proc, 5);
1549 --
1550 l_api_updating := ben_pln_shd.api_updating
1551 (p_effective_date => p_effective_date,
1552 p_pl_id => p_pl_id,
1553 p_object_version_number => p_object_version_number);
1554 --
1555 if p_invk_dcln_prtn_pl_flag = 'Y' or
1556 p_invk_flx_cr_pl_flag = 'Y' or
1557 p_svgs_pl_flag = 'Y' then
1558 --
1559 -- OK at least one of our flags holds a true state, lets make sure it
1560 -- is just one by using a series of if statements
1561 --
1562 if (p_invk_dcln_prtn_pl_flag = 'Y' and
1563 (p_invk_flx_cr_pl_flag = 'Y' or
1564 p_svgs_pl_flag = 'Y')
1565 or
1566 p_invk_flx_cr_pl_flag = 'Y' and
1567 (p_invk_dcln_prtn_pl_flag = 'Y' or
1568 p_svgs_pl_flag = 'Y')
1569 or
1570 p_svgs_pl_flag = 'Y' and
1571 (p_invk_flx_cr_pl_flag = 'Y' or
1572 p_invk_dcln_prtn_pl_flag = 'Y')) then
1573 --
1574 -- OK flags are not mutaully exclusive so raise an error
1575 --
1576 fnd_message.set_name('BEN','BEN_93882_PLN_FLGS_MUTUAL_EXCL'); --Bug 3424424
1577 fnd_message.raise_error;
1578 --
1579 end if;
1580 --
1581 end if;
1582 --
1583 hr_utility.set_location('Leaving:'||l_proc, 10);
1584 --
1585 End chk_mutual_exclusive_flags;
1586 -- ----------------------------------------------------------------------------
1587 -- |----------------------< chk_mutual_exlsv_rule_num_uom >--------------------|
1588 -- ----------------------------------------------------------------------------
1589 --
1590 -- Description
1591 -- This procedure is used to check that the mutual exclusive fields
1592 -- mx_wtg_perd_prte_det_rl and mx_wtg_perd_prte_val/mx_wtg_perd_prte_uom
1593 -- are set correctly.
1594 --
1595 -- Pre Conditions
1596 -- None.
1597 --
1598 -- In Parameters
1599 -- pl_id PK of record being inserted or updated.
1600 -- mx_wtg_perd_prte_det_rl Rule.
1601 -- mx_wtg_perd_prte_val Number.
1602 -- mx_wtg_perd_prte_uom UOM.
1603 -- effective_date Session date of record.
1604 -- object_version_number Object version number of record being
1605 -- inserted or updated.
1606 --
1607 -- Post Success
1608 -- Processing continues
1609 --
1610 -- Post Failure
1611 -- Errors handled by the procedure
1612 --
1613 -- Access Status
1614 -- Internal table handler use only.
1615 --
1616 Procedure chk_mutual_exlsv_rule_num_uom(p_pl_id in number,
1617 p_mx_wtg_perd_rl in number,
1618 p_mx_wtg_perd_prte_val in number,
1622 --
1619 p_mx_wtg_perd_prte_uom in varchar2,
1620 p_effective_date in date,
1621 p_object_version_number in number) is
1623 l_proc varchar2(72) := g_package||'chk_mutual_exlsv_rule_num_uom';
1624 l_api_updating boolean;
1625 --
1626 Begin
1627 --
1628 hr_utility.set_location('Entering:'||l_proc, 5);
1629 --
1630 l_api_updating := ben_pln_shd.api_updating
1631 (p_effective_date => p_effective_date,
1632 p_pl_id => p_pl_id,
1633 p_object_version_number => p_object_version_number);
1634 --
1635 if (p_mx_wtg_perd_rl is not null and
1636 (p_mx_wtg_perd_prte_val is not null or
1637 p_mx_wtg_perd_prte_uom is not null)) then
1638 --
1639 -- OK fields are not mutaully exclusive so raise an error
1640 --
1641 fnd_message.set_name('PAY','PLN_RULE_NUM_MUTUAL_EXCLUSIVE');
1642 fnd_message.raise_error;
1643 --
1644 end if;
1645 --
1646 hr_utility.set_location('Leaving:'||l_proc, 10);
1647 --
1648 End chk_mutual_exlsv_rule_num_uom;
1649 --
1650 -- ----------------------------------------------------------------------------
1651 -- |----------------------------< chk_all_flags >-----------------------------|
1652 -- ----------------------------------------------------------------------------
1653 --
1654 -- Description
1655 -- This procedure is used to check that the flag lookup values are valid.
1656 --
1657 -- Pre Conditions
1658 -- None.
1659 --
1660 -- In Parameters
1661 -- pl_id PK of record being inserted or updated.
1662 -- alws_qdro_flag Value of lookup code.
1663 -- alws_qmcso_flag Value of lookup code.
1664 -- aply_pgm_elig_flag Value of lookup code.
1665 -- bnf_addl_instn_txt_alwd_flag Value of lookup code.
1666 -- bnf_adrs_rqd_flag Value of lookup code.
1667 -- bnf_cntngt_bnfs_alwd_flag Value of lookup code.
1668 -- bnf_ctfn_rqd_flag Value of lookup code.
1669 -- bnf_dob_rqd_flag Value of lookup code.
1670 -- bnf_dsge_mnr_ttee_rqd_flag Value of lookup code.
1671 -- bnf_legv_id_rqd_flag Value of lookup code.
1672 -- bnf_may_dsgt_org_flag Value of lookup code.
1673 -- bnf_qdro_rl_apls_flag Value of lookup code.
1674 -- drvbl_fctr_apls_rts_flag Value of lookup code.
1675 -- drvbl_fctr_prtn_elig_flag Value of lookup code.
1676 -- elig_apls_flag Value of lookup code.
1677 -- invk_dcln_prtn_pl_flag Value of lookup code.
1678 -- invk_flx_cr_pl_flag Value of lookup code.
1679 -- nip_drvbl_dpnt_elig_flag Value of lookup code.
1680 -- nip_tmprl_dpnt_elig_flag Value of lookup code.
1681 -- trk_inelig_per_flag Value of lookup code.
1682 -- use_opt_ordr_num_flag Value of lookup code.
1683 -- dpnt_cvd_by_othr_apls_flag Value of lookup code.
1684 -- frfs_aply_flag Value of lookup code.
1685 -- hc_pl_subj_hcfa_aprvl_flag Value of lookup code.
1686 -- hghly_cmpd_rl_apls_flag Value of lookup code.
1687 -- nip_dpnt_adrs_rqd_flag Value of lookup code.
1688 -- nip_dpnt_dob_rqd_flag Value of lookup code.
1689 -- nip_dpnt_leg_id_rqd_flag Value of lookup code.
1690 -- nip_dpnt_no_ctfn_rqd_flag Value of lookup code.
1691 -- nip_mt_one_dpnt_elig_flag Value of lookup code.
1692 -- no_mn_cvg_amt_apls_flag Value of lookup code.
1693 -- no_mn_cvg_incr_apls_flag Value of lookup code.
1694 -- no_mn_opts_num_apls_flag Value of lookup code.
1695 -- no_mx_cvg_amt_apls_flag Value of lookup code.
1696 -- no_mx_cvg_incr_apls_flag Value of lookup code.
1697 -- no_mx_opts_num_apls_flag Value of lookup code.
1698 -- prtn_elig_ovrid_alwd_flag Value of lookup code.
1699 -- subj_to_imptd_incm_typ_cd Value of lookup code.
1700 -- use_all_asnts_elig_flag Value of lookup code.
1701 -- use_all_asnts_for_rt_flag Value of lookup code.
1702 -- vstg_apls_flag Value of lookup code.
1703 -- wvbl_flag Value of lookup code.
1704 -- effective_date effective date
1705 -- object_version_number Object version number of record being
1706 -- inserted or updated.
1707 --
1708 -- Post Success
1709 -- Processing continues
1710 --
1711 -- Post Failure
1712 -- Error handled by procedure
1713 --
1714 -- Access Status
1715 -- Internal table handler use only.
1716 --
1717 Procedure chk_all_flags(p_pl_id in number,
1718 p_alws_qdro_flag in varchar2,
1719 p_alws_qmcso_flag in varchar2,
1720 p_bnf_addl_instn_txt_alwd_flag in varchar2,
1721 p_bnf_adrs_rqd_flag in varchar2,
1722 p_bnf_cntngt_bnfs_alwd_flag in varchar2,
1723 p_bnf_ctfn_rqd_flag in varchar2,
1724 p_bnf_dob_rqd_flag in varchar2,
1725 p_bnf_dsge_mnr_ttee_rqd_flag in varchar2,
1726 p_bnf_legv_id_rqd_flag in varchar2,
1727 p_bnf_may_dsgt_org_flag in varchar2,
1728 p_bnf_qdro_rl_apls_flag in varchar2,
1729 p_drvbl_fctr_apls_rts_flag in varchar2,
1730 p_drvbl_fctr_prtn_elig_flag in varchar2,
1734 p_drvbl_dpnt_elig_flag in varchar2,
1731 p_elig_apls_flag in varchar2,
1732 p_invk_dcln_prtn_pl_flag in varchar2,
1733 p_invk_flx_cr_pl_flag in varchar2,
1735 p_trk_inelig_per_flag in varchar2,
1736 p_dpnt_cvd_by_othr_apls_flag in varchar2,
1737 p_frfs_aply_flag in varchar2,
1738 p_hc_pl_subj_hcfa_aprvl_flag in varchar2,
1739 p_hghly_cmpd_rl_apls_flag in varchar2,
1740 p_no_mn_cvg_amt_apls_flag in varchar2,
1741 p_no_mn_cvg_incr_apls_flag in varchar2,
1742 p_no_mn_opts_num_apls_flag in varchar2,
1743 p_no_mx_cvg_amt_apls_flag in varchar2,
1744 p_no_mx_cvg_incr_apls_flag in varchar2,
1745 p_no_mx_opts_num_apls_flag in varchar2,
1746 p_prtn_elig_ovrid_alwd_flag in varchar2,
1747 p_subj_to_imptd_incm_typ_cd in varchar2,
1748 p_use_all_asnts_elig_flag in varchar2,
1749 p_use_all_asnts_for_rt_flag in varchar2,
1750 p_vstg_apls_flag in varchar2,
1751 p_wvbl_flag in varchar2,
1752 p_alws_reimbmts_flag in varchar2,
1753 p_dpnt_adrs_rqd_flag in varchar2,
1754 p_dpnt_dob_rqd_flag in varchar2,
1755 p_dpnt_leg_id_rqd_flag in varchar2,
1756 p_dpnt_no_ctfn_rqd_flag in varchar2,
1757 p_svgs_pl_flag in varchar2,
1758 p_enrt_pl_opt_flag in varchar2,
1759 p_MAY_ENRL_PL_N_OIPL_FLAG in varchar2,
1760 p_ALWS_UNRSTRCTD_ENRT_FLAG in varchar2,
1761 p_ALWS_TMPRY_ID_CRD_FLAG in varchar2,
1762 p_nip_dflt_flag in varchar2,
1763 p_post_to_gl_flag in varchar2,
1764 p_effective_date in date,
1765 p_object_version_number in number) is
1766 --
1767 l_proc varchar2(72) := g_package||'chk_all_flags';
1768 l_api_updating boolean;
1769 --
1770 Begin
1771 --
1772 hr_utility.set_location('Entering:'||l_proc, 5);
1773 --
1774 l_api_updating := ben_pln_shd.api_updating
1775 (p_pl_id => p_pl_id,
1776 p_effective_date => p_effective_date,
1777 p_object_version_number => p_object_version_number);
1778 --
1779 if (l_api_updating
1780 and p_alws_qdro_flag
1781 <> nvl(ben_pln_shd.g_old_rec.alws_qdro_flag,hr_api.g_varchar2)
1782 or not l_api_updating)
1783 and p_alws_qdro_flag is not null then
1784 --
1785 -- check if value of lookup falls within lookup type.
1786 --
1787 if hr_api.not_exists_in_hr_lookups
1788 (p_lookup_type => 'YES_NO',
1789 p_lookup_code => p_alws_qdro_flag,
1790 p_effective_date => p_effective_date) then
1791 --
1792 -- raise error as does not exist as lookup
1793 --
1794 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
1795 fnd_message.raise_error;
1796 --
1797 end if;
1798 --
1799 end if;
1800 --
1801 if (l_api_updating
1802 and p_alws_qmcso_flag
1803 <> nvl(ben_pln_shd.g_old_rec.alws_qmcso_flag,hr_api.g_varchar2)
1804 or not l_api_updating)
1805 and p_alws_qmcso_flag is not null then
1806 --
1807 -- check if value of lookup falls within lookup type.
1808 --
1809 if hr_api.not_exists_in_hr_lookups
1810 (p_lookup_type => 'YES_NO',
1811 p_lookup_code => p_alws_qmcso_flag,
1812 p_effective_date => p_effective_date) then
1813 --
1814 -- raise error as does not exist as lookup
1815 --
1816 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
1817 fnd_message.raise_error;
1818 --
1819 end if;
1820 --
1821 end if;
1822 --
1823 if (l_api_updating
1824 and p_enrt_pl_opt_flag
1825 <> nvl(ben_pln_shd.g_old_rec.enrt_pl_opt_flag,hr_api.g_varchar2)
1826 or not l_api_updating)
1827 and p_enrt_pl_opt_flag is not null then
1828 --
1829 -- check if value of lookup falls within lookup type.
1830 --
1831 if hr_api.not_exists_in_hr_lookups
1832 (p_lookup_type => 'YES_NO',
1833 p_lookup_code => p_enrt_pl_opt_flag,
1834 p_effective_date => p_effective_date) then
1835 --
1836 -- raise error as does not exist as lookup
1837 --
1838 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
1839 fnd_message.raise_error;
1840 --
1841 end if;
1842 --
1843 end if;
1844 --
1845 if (l_api_updating
1846 and p_alws_reimbmts_flag
1847 <> nvl(ben_pln_shd.g_old_rec.alws_reimbmts_flag,hr_api.g_varchar2)
1848 or not l_api_updating)
1849 and p_alws_reimbmts_flag is not null then
1850 --
1854 (p_lookup_type => 'YES_NO',
1851 -- check if value of lookup falls within lookup type.
1852 --
1853 if hr_api.not_exists_in_hr_lookups
1855 p_lookup_code => p_alws_reimbmts_flag,
1856 p_effective_date => p_effective_date) then
1857 --
1858 -- raise error as does not exist as lookup
1859 --
1860 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
1861 fnd_message.raise_error;
1862 --
1863 end if;
1864 --
1865 end if;
1866 --
1867 if (l_api_updating
1868 and p_bnf_addl_instn_txt_alwd_flag
1869 <> nvl(ben_pln_shd.g_old_rec.bnf_addl_instn_txt_alwd_flag,hr_api.g_varchar2)
1870 or not l_api_updating)
1871 and p_bnf_addl_instn_txt_alwd_flag is not null then
1872 --
1873 -- check if value of lookup falls within lookup type.
1874 --
1875 if hr_api.not_exists_in_hr_lookups
1876 (p_lookup_type => 'YES_NO',
1877 p_lookup_code => p_bnf_addl_instn_txt_alwd_flag,
1878 p_effective_date => p_effective_date) then
1879 --
1880 -- raise error as does not exist as lookup
1881 --
1882 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
1883 fnd_message.raise_error;
1884 --
1885 end if;
1886 --
1887 end if;
1888 --
1889 if (l_api_updating
1890 and p_bnf_adrs_rqd_flag
1891 <> nvl(ben_pln_shd.g_old_rec.bnf_adrs_rqd_flag,hr_api.g_varchar2)
1892 or not l_api_updating)
1893 and p_bnf_adrs_rqd_flag is not null then
1894 --
1895 -- check if value of lookup falls within lookup type.
1896 --
1897 if hr_api.not_exists_in_hr_lookups
1898 (p_lookup_type => 'YES_NO',
1899 p_lookup_code => p_bnf_adrs_rqd_flag,
1900 p_effective_date => p_effective_date) then
1901 --
1902 -- raise error as does not exist as lookup
1903 --
1904 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
1905 fnd_message.raise_error;
1906 --
1907 end if;
1908 --
1909 end if;
1910 --
1911 if (l_api_updating
1912 and p_bnf_cntngt_bnfs_alwd_flag
1913 <> nvl(ben_pln_shd.g_old_rec.bnf_cntngt_bnfs_alwd_flag,hr_api.g_varchar2)
1914 or not l_api_updating)
1915 and p_bnf_cntngt_bnfs_alwd_flag is not null then
1916 --
1917 -- check if value of lookup falls within lookup type.
1918 --
1919 if hr_api.not_exists_in_hr_lookups
1920 (p_lookup_type => 'YES_NO',
1921 p_lookup_code => p_bnf_cntngt_bnfs_alwd_flag,
1922 p_effective_date => p_effective_date) then
1923 --
1924 -- raise error as does not exist as lookup
1925 --
1926 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
1927 fnd_message.raise_error;
1928 --
1929 end if;
1930 --
1931 end if;
1932 --
1933 if (l_api_updating
1934 and p_bnf_ctfn_rqd_flag
1935 <> nvl(ben_pln_shd.g_old_rec.bnf_ctfn_rqd_flag,hr_api.g_varchar2)
1936 or not l_api_updating)
1937 and p_bnf_ctfn_rqd_flag is not null then
1938 --
1939 -- check if value of lookup falls within lookup type.
1940 --
1941 if hr_api.not_exists_in_hr_lookups
1942 (p_lookup_type => 'YES_NO',
1943 p_lookup_code => p_bnf_ctfn_rqd_flag,
1944 p_effective_date => p_effective_date) then
1945 --
1946 -- raise error as does not exist as lookup
1947 --
1948 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
1949 fnd_message.raise_error;
1950 --
1951 end if;
1952 --
1953 end if;
1954 --
1955 if (l_api_updating
1956 and p_bnf_dob_rqd_flag
1957 <> nvl(ben_pln_shd.g_old_rec.bnf_dob_rqd_flag,hr_api.g_varchar2)
1958 or not l_api_updating)
1959 and p_bnf_dob_rqd_flag is not null then
1960 --
1961 -- check if value of lookup falls within lookup type.
1962 --
1963 if hr_api.not_exists_in_hr_lookups
1964 (p_lookup_type => 'YES_NO',
1965 p_lookup_code => p_bnf_dob_rqd_flag,
1966 p_effective_date => p_effective_date) then
1967 --
1968 -- raise error as does not exist as lookup
1969 --
1970 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
1971 fnd_message.raise_error;
1972 --
1973 end if;
1974 --
1975 end if;
1976 --
1977 if (l_api_updating
1978 and p_bnf_dsge_mnr_ttee_rqd_flag
1979 <> nvl(ben_pln_shd.g_old_rec.bnf_dsge_mnr_ttee_rqd_flag,hr_api.g_varchar2)
1980 or not l_api_updating)
1981 and p_bnf_dsge_mnr_ttee_rqd_flag is not null then
1982 --
1983 -- check if value of lookup falls within lookup type.
1984 --
1985 if hr_api.not_exists_in_hr_lookups
1986 (p_lookup_type => 'YES_NO',
1987 p_lookup_code => p_bnf_dsge_mnr_ttee_rqd_flag,
1988 p_effective_date => p_effective_date) then
1989 --
1990 -- raise error as does not exist as lookup
1991 --
1992 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
1993 fnd_message.raise_error;
1994 --
1995 end if;
1996 --
2000 and p_bnf_legv_id_rqd_flag
1997 end if;
1998 --
1999 if (l_api_updating
2001 <> nvl(ben_pln_shd.g_old_rec.bnf_legv_id_rqd_flag,hr_api.g_varchar2)
2002 or not l_api_updating)
2003 and p_bnf_legv_id_rqd_flag is not null then
2004 --
2005 -- check if value of lookup falls within lookup type.
2006 --
2007 if hr_api.not_exists_in_hr_lookups
2008 (p_lookup_type => 'YES_NO',
2009 p_lookup_code => p_bnf_legv_id_rqd_flag,
2010 p_effective_date => p_effective_date) then
2011 --
2012 -- raise error as does not exist as lookup
2013 --
2014 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2015 fnd_message.raise_error;
2016 --
2017 end if;
2018 --
2019 end if;
2020 --
2021 if (l_api_updating
2022 and p_bnf_may_dsgt_org_flag
2023 <> nvl(ben_pln_shd.g_old_rec.bnf_may_dsgt_org_flag,hr_api.g_varchar2)
2024 or not l_api_updating)
2025 and p_bnf_may_dsgt_org_flag is not null then
2026 --
2027 -- check if value of lookup falls within lookup type.
2028 --
2029 if hr_api.not_exists_in_hr_lookups
2030 (p_lookup_type => 'YES_NO',
2031 p_lookup_code => p_bnf_may_dsgt_org_flag,
2032 p_effective_date => p_effective_date) then
2033 --
2034 -- raise error as does not exist as lookup
2035 --
2036 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2037 fnd_message.raise_error;
2038 --
2039 end if;
2040 --
2041 end if;
2042 --
2043 if (l_api_updating
2044 and p_bnf_qdro_rl_apls_flag
2045 <> nvl(ben_pln_shd.g_old_rec.bnf_qdro_rl_apls_flag,hr_api.g_varchar2)
2046 or not l_api_updating)
2047 and p_bnf_qdro_rl_apls_flag is not null then
2048 --
2049 -- check if value of lookup falls within lookup type.
2050 --
2051 if hr_api.not_exists_in_hr_lookups
2052 (p_lookup_type => 'YES_NO',
2053 p_lookup_code => p_bnf_qdro_rl_apls_flag,
2054 p_effective_date => p_effective_date) then
2055 --
2056 -- raise error as does not exist as lookup
2057 --
2058 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2059 fnd_message.raise_error;
2060 --
2061 end if;
2062 --
2063 end if;
2064 --
2065 if (l_api_updating
2066 and p_drvbl_fctr_apls_rts_flag
2067 <> nvl(ben_pln_shd.g_old_rec.drvbl_fctr_apls_rts_flag,hr_api.g_varchar2)
2068 or not l_api_updating)
2069 and p_drvbl_fctr_apls_rts_flag is not null then
2070 --
2071 -- check if value of lookup falls within lookup type.
2072 --
2073 if hr_api.not_exists_in_hr_lookups
2074 (p_lookup_type => 'YES_NO',
2075 p_lookup_code => p_drvbl_fctr_apls_rts_flag,
2076 p_effective_date => p_effective_date) then
2077 --
2078 -- raise error as does not exist as lookup
2079 --
2080 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2081 fnd_message.raise_error;
2082 --
2083 end if;
2084 --
2085 end if;
2086 --
2087 if (l_api_updating
2088 and p_drvbl_fctr_prtn_elig_flag
2089 <> nvl(ben_pln_shd.g_old_rec.drvbl_fctr_prtn_elig_flag,hr_api.g_varchar2)
2090 or not l_api_updating)
2091 and p_drvbl_fctr_prtn_elig_flag is not null then
2092 --
2093 -- check if value of lookup falls within lookup type.
2094 --
2095 if hr_api.not_exists_in_hr_lookups
2096 (p_lookup_type => 'YES_NO',
2097 p_lookup_code => p_drvbl_fctr_prtn_elig_flag,
2098 p_effective_date => p_effective_date) then
2099 --
2100 -- raise error as does not exist as lookup
2101 --
2102 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2103 fnd_message.raise_error;
2104 --
2105 end if;
2106 --
2107 end if;
2108 --
2109 if (l_api_updating
2110 and p_elig_apls_flag
2111 <> nvl(ben_pln_shd.g_old_rec.elig_apls_flag,hr_api.g_varchar2)
2112 or not l_api_updating)
2113 and p_elig_apls_flag is not null then
2114 --
2115 -- check if value of lookup falls within lookup type.
2116 --
2117 if hr_api.not_exists_in_hr_lookups
2118 (p_lookup_type => 'YES_NO',
2119 p_lookup_code => p_elig_apls_flag,
2120 p_effective_date => p_effective_date) then
2121 --
2122 -- raise error as does not exist as lookup
2123 --
2124 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2125 fnd_message.raise_error;
2126 --
2127 end if;
2128 --
2129 end if;
2130 --
2131 if (l_api_updating
2132 and p_invk_dcln_prtn_pl_flag
2133 <> nvl(ben_pln_shd.g_old_rec.invk_dcln_prtn_pl_flag,hr_api.g_varchar2)
2134 or not l_api_updating)
2135 and p_invk_dcln_prtn_pl_flag is not null then
2136 --
2137 -- check if value of lookup falls within lookup type.
2138 --
2139 if hr_api.not_exists_in_hr_lookups
2140 (p_lookup_type => 'YES_NO',
2141 p_lookup_code => p_invk_dcln_prtn_pl_flag,
2145 --
2142 p_effective_date => p_effective_date) then
2143 --
2144 -- raise error as does not exist as lookup
2146 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2147 fnd_message.raise_error;
2148 --
2149 end if;
2150 --
2151 end if;
2152 --
2153 if (l_api_updating
2154 and p_invk_flx_cr_pl_flag
2155 <> nvl(ben_pln_shd.g_old_rec.invk_flx_cr_pl_flag,hr_api.g_varchar2)
2156 or not l_api_updating)
2157 and p_invk_flx_cr_pl_flag is not null then
2158 --
2159 -- check if value of lookup falls within lookup type.
2160 --
2161 if hr_api.not_exists_in_hr_lookups
2162 (p_lookup_type => 'YES_NO',
2163 p_lookup_code => p_invk_flx_cr_pl_flag,
2164 p_effective_date => p_effective_date) then
2165 --
2166 -- raise error as does not exist as lookup
2167 --
2168 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2169 fnd_message.raise_error;
2170 --
2171 end if;
2172 --
2173 end if;
2174 --
2175 if (l_api_updating
2176 and p_drvbl_dpnt_elig_flag
2177 <> nvl(ben_pln_shd.g_old_rec.drvbl_dpnt_elig_flag,hr_api.g_varchar2)
2178 or not l_api_updating)
2179 and p_drvbl_dpnt_elig_flag is not null then
2180 --
2181 -- check if value of lookup falls within lookup type.
2182 --
2183 if hr_api.not_exists_in_hr_lookups
2184 (p_lookup_type => 'YES_NO',
2185 p_lookup_code => p_drvbl_dpnt_elig_flag,
2186 p_effective_date => p_effective_date) then
2187 --
2188 -- raise error as does not exist as lookup
2189 --
2190 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2191 fnd_message.raise_error;
2192 --
2193 end if;
2194 --
2195 end if;
2196 --
2197 if (l_api_updating
2198 and p_dpnt_adrs_rqd_flag
2199 <> nvl(ben_pln_shd.g_old_rec.dpnt_adrs_rqd_flag,hr_api.g_varchar2)
2200 or not l_api_updating)
2201 and p_dpnt_adrs_rqd_flag is not null then
2202 --
2203 -- check if value of lookup falls within lookup type.
2204 --
2205 if hr_api.not_exists_in_hr_lookups
2206 (p_lookup_type => 'YES_NO',
2207 p_lookup_code => p_dpnt_adrs_rqd_flag,
2208 p_effective_date => p_effective_date) then
2209 --
2210 -- raise error as does not exist as lookup
2211 --
2212 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2213 fnd_message.raise_error;
2214 --
2215 end if;
2216 --
2217 end if;
2218 --
2219 if (l_api_updating
2220 and p_trk_inelig_per_flag
2221 <> nvl(ben_pln_shd.g_old_rec.trk_inelig_per_flag,hr_api.g_varchar2)
2222 or not l_api_updating)
2223 and p_trk_inelig_per_flag is not null then
2224 --
2225 -- check if value of lookup falls within lookup type.
2226 --
2227 if hr_api.not_exists_in_hr_lookups
2228 (p_lookup_type => 'YES_NO',
2229 p_lookup_code => p_trk_inelig_per_flag,
2230 p_effective_date => p_effective_date) then
2231 --
2232 -- raise error as does not exist as lookup
2233 --
2234 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2235 fnd_message.raise_error;
2236 --
2237 end if;
2238 --
2239 end if;
2240 --
2241 if (l_api_updating
2242 and p_svgs_pl_flag
2243 <> nvl(ben_pln_shd.g_old_rec.svgs_pl_flag,hr_api.g_varchar2)
2244 or not l_api_updating)
2245 and p_svgs_pl_flag is not null then
2246 --
2247 -- check if value of lookup falls within lookup type.
2248 --
2249 if hr_api.not_exists_in_hr_lookups
2250 (p_lookup_type => 'YES_NO',
2251 p_lookup_code => p_svgs_pl_flag,
2252 p_effective_date => p_effective_date) then
2253 --
2254 -- raise error as does not exist as lookup
2255 --
2256 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2257 fnd_message.raise_error;
2258 --
2259 end if;
2260 --
2261 end if;
2262 --
2263 if (l_api_updating
2264 and p_dpnt_cvd_by_othr_apls_flag
2265 <> nvl(ben_pln_shd.g_old_rec.dpnt_cvd_by_othr_apls_flag,hr_api.g_varchar2)
2266 or not l_api_updating)
2267 and p_dpnt_cvd_by_othr_apls_flag is not null then
2268 --
2269 -- check if value of lookup falls within lookup type.
2270 --
2271 if hr_api.not_exists_in_hr_lookups
2272 (p_lookup_type => 'YES_NO',
2273 p_lookup_code => p_dpnt_cvd_by_othr_apls_flag,
2274 p_effective_date => p_effective_date) then
2275 --
2276 -- raise error as does not exist as lookup
2277 --
2278 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2279 fnd_message.raise_error;
2280 --
2281 end if;
2282 --
2283 end if;
2284 --
2285 if (l_api_updating
2286 and p_frfs_aply_flag
2287 <> nvl(ben_pln_shd.g_old_rec.frfs_aply_flag,hr_api.g_varchar2)
2288 or not l_api_updating)
2289 and p_frfs_aply_flag is not null then
2290 --
2294 (p_lookup_type => 'YES_NO',
2291 -- check if value of lookup falls within lookup type.
2292 --
2293 if hr_api.not_exists_in_hr_lookups
2295 p_lookup_code => p_frfs_aply_flag,
2296 p_effective_date => p_effective_date) then
2297 --
2298 -- raise error as does not exist as lookup
2299 --
2300 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2301 fnd_message.raise_error;
2302 --
2303 end if;
2304 --
2305 end if;
2306 --
2307 if (l_api_updating
2308 and p_hc_pl_subj_hcfa_aprvl_flag
2309 <> nvl(ben_pln_shd.g_old_rec.hc_pl_subj_hcfa_aprvl_flag,hr_api.g_varchar2)
2310 or not l_api_updating)
2311 and p_hc_pl_subj_hcfa_aprvl_flag is not null then
2312 --
2313 -- check if value of lookup falls within lookup type.
2314 --
2315 if hr_api.not_exists_in_hr_lookups
2316 (p_lookup_type => 'YES_NO',
2317 p_lookup_code => p_hc_pl_subj_hcfa_aprvl_flag,
2318 p_effective_date => p_effective_date) then
2319 --
2320 -- raise error as does not exist as lookup
2321 --
2322 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2323 fnd_message.raise_error;
2324 --
2325 end if;
2326 --
2327 end if;
2328 --
2329 if (l_api_updating
2330 and p_hghly_cmpd_rl_apls_flag
2331 <> nvl(ben_pln_shd.g_old_rec.hghly_cmpd_rl_apls_flag,hr_api.g_varchar2)
2332 or not l_api_updating)
2333 and p_hghly_cmpd_rl_apls_flag is not null then
2334 --
2335 -- check if value of lookup falls within lookup type.
2336 --
2337 if hr_api.not_exists_in_hr_lookups
2338 (p_lookup_type => 'YES_NO',
2339 p_lookup_code => p_hghly_cmpd_rl_apls_flag,
2340 p_effective_date => p_effective_date) then
2341 --
2342 -- raise error as does not exist as lookup
2343 --
2344 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2345 fnd_message.raise_error;
2346 --
2347 end if;
2348 --
2349 end if;
2350 --
2351 if (l_api_updating
2352 and p_dpnt_leg_id_rqd_flag
2353 <> nvl(ben_pln_shd.g_old_rec.dpnt_leg_id_rqd_flag,hr_api.g_varchar2)
2354 or not l_api_updating)
2355 and p_dpnt_leg_id_rqd_flag is not null then
2356 --
2357 -- check if value of lookup falls within lookup type.
2358 --
2359 if hr_api.not_exists_in_hr_lookups
2360 (p_lookup_type => 'YES_NO',
2361 p_lookup_code => p_dpnt_leg_id_rqd_flag,
2362 p_effective_date => p_effective_date) then
2363 --
2364 -- raise error as does not exist as lookup
2365 --
2366 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2367 fnd_message.raise_error;
2368 --
2369 end if;
2370 --
2371 end if;
2372 --
2373 if (l_api_updating
2374 and p_dpnt_dob_rqd_flag
2375 <> nvl(ben_pln_shd.g_old_rec.dpnt_dob_rqd_flag,hr_api.g_varchar2)
2376 or not l_api_updating)
2377 and p_dpnt_dob_rqd_flag is not null then
2378 --
2379 -- check if value of lookup falls within lookup type.
2380 --
2381 if hr_api.not_exists_in_hr_lookups
2382 (p_lookup_type => 'YES_NO',
2383 p_lookup_code => p_dpnt_dob_rqd_flag,
2384 p_effective_date => p_effective_date) then
2385 --
2386 -- raise error as does not exist as lookup
2387 --
2388 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2389 fnd_message.raise_error;
2390 --
2391 end if;
2392 --
2393 end if;
2394 --
2395 if (l_api_updating
2396 and p_dpnt_no_ctfn_rqd_flag
2397 <> nvl(ben_pln_shd.g_old_rec.dpnt_no_ctfn_rqd_flag,hr_api.g_varchar2)
2398 or not l_api_updating)
2399 and p_dpnt_no_ctfn_rqd_flag is not null then
2400 --
2401 -- check if value of lookup falls within lookup type.
2402 --
2403 if hr_api.not_exists_in_hr_lookups
2404 (p_lookup_type => 'YES_NO',
2405 p_lookup_code => p_dpnt_no_ctfn_rqd_flag,
2406 p_effective_date => p_effective_date) then
2407 --
2408 -- raise error as does not exist as lookup
2409 --
2410 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2411 fnd_message.raise_error;
2412 --
2413 end if;
2414 --
2415 end if;
2416 --
2417 if (l_api_updating
2418 and p_no_mn_cvg_amt_apls_flag
2419 <> nvl(ben_pln_shd.g_old_rec.no_mn_cvg_amt_apls_flag,hr_api.g_varchar2)
2420 or not l_api_updating)
2421 and p_no_mn_cvg_amt_apls_flag is not null then
2422 --
2423 -- check if value of lookup falls within lookup type.
2424 --
2425 if hr_api.not_exists_in_hr_lookups
2426 (p_lookup_type => 'YES_NO',
2427 p_lookup_code => p_no_mn_cvg_amt_apls_flag,
2428 p_effective_date => p_effective_date) then
2429 --
2430 -- raise error as does not exist as lookup
2431 --
2432 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2433 fnd_message.raise_error;
2434 --
2435 end if;
2439 if (l_api_updating
2436 --
2437 end if;
2438 --
2440 and p_no_mn_cvg_incr_apls_flag
2441 <> nvl(ben_pln_shd.g_old_rec.no_mn_cvg_incr_apls_flag,hr_api.g_varchar2)
2442 or not l_api_updating)
2443 and p_no_mn_cvg_incr_apls_flag is not null then
2444 --
2445 -- check if value of lookup falls within lookup type.
2446 --
2447 if hr_api.not_exists_in_hr_lookups
2448 (p_lookup_type => 'YES_NO',
2449 p_lookup_code => p_no_mn_cvg_incr_apls_flag,
2450 p_effective_date => p_effective_date) then
2451 --
2452 -- raise error as does not exist as lookup
2453 --
2454 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2455 fnd_message.raise_error;
2456 --
2457 end if;
2458 --
2459 end if;
2460 --
2461 if (l_api_updating
2462 and p_no_mn_opts_num_apls_flag
2463 <> nvl(ben_pln_shd.g_old_rec.no_mn_opts_num_apls_flag,hr_api.g_varchar2)
2464 or not l_api_updating)
2465 and p_no_mn_opts_num_apls_flag is not null then
2466 --
2467 -- check if value of lookup falls within lookup type.
2468 --
2469 if hr_api.not_exists_in_hr_lookups
2470 (p_lookup_type => 'YES_NO',
2471 p_lookup_code => p_no_mn_opts_num_apls_flag,
2472 p_effective_date => p_effective_date) then
2473 --
2474 -- raise error as does not exist as lookup
2475 --
2476 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2477 fnd_message.raise_error;
2478 --
2479 end if;
2480 --
2481 end if;
2482 --
2483 if (l_api_updating
2484 and p_no_mx_cvg_amt_apls_flag
2485 <> nvl(ben_pln_shd.g_old_rec.no_mx_cvg_amt_apls_flag,hr_api.g_varchar2)
2486 or not l_api_updating)
2487 and p_no_mx_cvg_amt_apls_flag is not null then
2488 --
2489 -- check if value of lookup falls within lookup type.
2490 --
2491 if hr_api.not_exists_in_hr_lookups
2492 (p_lookup_type => 'YES_NO',
2493 p_lookup_code => p_no_mx_cvg_amt_apls_flag,
2494 p_effective_date => p_effective_date) then
2495 --
2496 -- raise error as does not exist as lookup
2497 --
2498 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2499 fnd_message.raise_error;
2500 --
2501 end if;
2502 --
2503 end if;
2504 --
2505 if (l_api_updating
2506 and p_no_mx_cvg_incr_apls_flag
2507 <> nvl(ben_pln_shd.g_old_rec.no_mx_cvg_incr_apls_flag,hr_api.g_varchar2)
2508 or not l_api_updating)
2509 and p_no_mx_cvg_incr_apls_flag is not null then
2510 --
2511 -- check if value of lookup falls within lookup type.
2512 --
2513 if hr_api.not_exists_in_hr_lookups
2514 (p_lookup_type => 'YES_NO',
2515 p_lookup_code => p_no_mx_cvg_incr_apls_flag,
2516 p_effective_date => p_effective_date) then
2517 --
2518 -- raise error as does not exist as lookup
2519 --
2520 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2521 fnd_message.raise_error;
2522 --
2523 end if;
2524 --
2525 end if;
2526 --
2527 if (l_api_updating
2528 and p_no_mx_opts_num_apls_flag
2529 <> nvl(ben_pln_shd.g_old_rec.no_mx_opts_num_apls_flag,hr_api.g_varchar2)
2530 or not l_api_updating)
2531 and p_no_mx_opts_num_apls_flag is not null then
2532 --
2533 -- check if value of lookup falls within lookup type.
2534 --
2535 if hr_api.not_exists_in_hr_lookups
2536 (p_lookup_type => 'YES_NO',
2537 p_lookup_code => p_no_mx_opts_num_apls_flag,
2538 p_effective_date => p_effective_date) then
2539 --
2540 -- raise error as does not exist as lookup
2541 --
2542 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2543 fnd_message.raise_error;
2544 --
2545 end if;
2546 --
2547 end if;
2548 --
2549 if (l_api_updating
2550 and p_prtn_elig_ovrid_alwd_flag
2551 <> nvl(ben_pln_shd.g_old_rec.prtn_elig_ovrid_alwd_flag,hr_api.g_varchar2)
2552 or not l_api_updating)
2553 and p_prtn_elig_ovrid_alwd_flag is not null then
2554 --
2555 -- check if value of lookup falls within lookup type.
2556 --
2557 if hr_api.not_exists_in_hr_lookups
2558 (p_lookup_type => 'YES_NO',
2559 p_lookup_code => p_prtn_elig_ovrid_alwd_flag,
2560 p_effective_date => p_effective_date) then
2561 --
2562 -- raise error as does not exist as lookup
2563 --
2564 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2565 fnd_message.raise_error;
2566 --
2567 end if;
2568 --
2569 end if;
2570 --
2571 if (l_api_updating
2572 and p_subj_to_imptd_incm_typ_cd
2573 <> nvl(ben_pln_shd.g_old_rec.subj_to_imptd_incm_typ_cd,hr_api.g_varchar2)
2574 or not l_api_updating)
2575 and p_subj_to_imptd_incm_typ_cd is not null then
2576 --
2577 -- check if value of lookup falls within lookup type.
2578 --
2579 if hr_api.not_exists_in_hr_lookups
2583 --
2580 (p_lookup_type => 'BEN_IMPTD_INCM_TYP',
2581 p_lookup_code => p_subj_to_imptd_incm_typ_cd,
2582 p_effective_date => p_effective_date) then
2584 -- raise error as does not exist as lookup
2585 --
2586 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
2587 fnd_message.set_token('FIELD', 'p_subj_to_imptd_incm_typ_cd');
2588 fnd_message.set_token('TYPE', 'BEN_IMPTD_INCM_TYP');
2589 fnd_message.raise_error;
2590 --
2591 end if;
2592 --
2593 end if;
2594 --
2595 if (l_api_updating
2596 and p_use_all_asnts_elig_flag
2597 <> nvl(ben_pln_shd.g_old_rec.use_all_asnts_elig_flag,hr_api.g_varchar2)
2598 or not l_api_updating)
2599 and p_use_all_asnts_elig_flag is not null then
2600 --
2601 -- check if value of lookup falls within lookup type.
2602 --
2603 if hr_api.not_exists_in_hr_lookups
2604 (p_lookup_type => 'YES_NO',
2605 p_lookup_code => p_use_all_asnts_elig_flag,
2606 p_effective_date => p_effective_date) then
2607 --
2608 -- raise error as does not exist as lookup
2609 --
2610 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2611 fnd_message.raise_error;
2612 --
2613 end if;
2614 --
2615 end if;
2616 --
2617 if (l_api_updating
2618 and p_use_all_asnts_for_rt_flag
2619 <> nvl(ben_pln_shd.g_old_rec.use_all_asnts_for_rt_flag,hr_api.g_varchar2)
2620 or not l_api_updating)
2621 and p_use_all_asnts_for_rt_flag is not null then
2622 --
2623 -- check if value of lookup falls within lookup type.
2624 --
2625 if hr_api.not_exists_in_hr_lookups
2626 (p_lookup_type => 'YES_NO',
2627 p_lookup_code => p_use_all_asnts_for_rt_flag,
2628 p_effective_date => p_effective_date) then
2629 --
2630 -- raise error as does not exist as lookup
2631 --
2632 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2633 fnd_message.raise_error;
2634 --
2635 end if;
2636 --
2637 end if;
2638 --
2639 if (l_api_updating
2640 and p_vstg_apls_flag
2641 <> nvl(ben_pln_shd.g_old_rec.vstg_apls_flag,hr_api.g_varchar2)
2642 or not l_api_updating)
2643 and p_vstg_apls_flag is not null then
2644 --
2645 -- check if value of lookup falls within lookup type.
2646 --
2647 if hr_api.not_exists_in_hr_lookups
2648 (p_lookup_type => 'YES_NO',
2649 p_lookup_code => p_vstg_apls_flag,
2650 p_effective_date => p_effective_date) then
2651 --
2652 -- raise error as does not exist as lookup
2653 --
2654 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2655 fnd_message.raise_error;
2656 --
2657 end if;
2658 --
2659 end if;
2660 --
2661 if (l_api_updating
2662 and p_wvbl_flag
2663 <> nvl(ben_pln_shd.g_old_rec.wvbl_flag,hr_api.g_varchar2)
2664 or not l_api_updating)
2665 and p_wvbl_flag is not null then
2666 --
2667 -- check if value of lookup falls within lookup type.
2668 --
2669 if hr_api.not_exists_in_hr_lookups
2670 (p_lookup_type => 'YES_NO',
2671 p_lookup_code => p_wvbl_flag,
2672 p_effective_date => p_effective_date) then
2673 --
2674 -- raise error as does not exist as lookup
2675 --
2676 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2677 fnd_message.raise_error;
2678 --
2679 end if;
2680 --
2681 --
2682 -- check if value of lookup falls within lookup type.
2683 --
2684 if hr_api.not_exists_in_hr_lookups
2685 (p_lookup_type => 'YES_NO',
2686 p_lookup_code => p_MAY_ENRL_PL_N_OIPL_flag,
2687 p_effective_date => p_effective_date) then
2688 --
2689 -- raise error as does not exist as lookup
2690 --
2691 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2692 fnd_message.raise_error;
2693 --
2694 end if;
2695 --
2696 --
2697 -- check if value of lookup falls within lookup type.
2698 --
2699 if hr_api.not_exists_in_hr_lookups
2700 (p_lookup_type => 'YES_NO',
2701 p_lookup_code => p_ALWS_UNRSTRCTD_ENRT_flag,
2702 p_effective_date => p_effective_date) then
2703 --
2704 -- raise error as does not exist as lookup
2705 --
2706 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2707 fnd_message.raise_error;
2708 --
2709 end if;
2710 --
2711 --
2712 --
2713 -- check if value of lookup falls within lookup type.
2714 --
2715 if hr_api.not_exists_in_hr_lookups
2716 (p_lookup_type => 'YES_NO',
2717 p_lookup_code => p_ALWS_TMPRY_ID_CRD_flag,
2718 p_effective_date => p_effective_date) then
2719 --
2720 -- raise error as does not exist as lookup
2721 --
2722 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2723 fnd_message.raise_error;
2724 --
2728 --
2725 end if;
2726 --
2727 end if;
2729 hr_utility.set_location('Leaving:'||l_proc,10);
2730 --
2731 end chk_all_flags;
2732 -- ----------------------------------------------------------------------------
2733 -- |----------------------------< chk_all_lookups >---------------------------|
2734 -- ----------------------------------------------------------------------------
2735 --
2736 -- Description
2737 -- This procedure is used to check that the lookup values are valid.
2738 --
2739 -- Pre Conditions
2740 -- None.
2741 --
2742 -- In Parameters
2743 -- pl_id PK of record being inserted or updated.
2744 -- bnf_dflt_bnf_cd Value of lookup code.
2745 -- bnf_pct_amt_alwd_cd Value of lookup code.
2746 -- dpnt_dsgn_cd Value of lookup code.
2747 -- pl_cd Value of lookup code.
2748 -- cmpr_clms_to_cvg_or_bal_cd Value of lookup code.
2749 -- cobra_pymt_due_dy_num Value of lookup code.
2750 -- enrt_mthd_cd Value of lookup code.
2751 -- enrt_cd Value of lookup code.
2752 -- enrt_strt_dt_cd Value of lookup code.
2753 -- deenrt_end_dt_cd Value of lookup code.
2754 -- mx_wtg_perd_prte_uom Value of lookup code.
2755 -- nip_dflt_enrt_cd Value of lookup code.
2756 -- nip_dpnt_cvg_end_dt_cd Value of lookup code.
2757 -- nip_dpnt_cvg_strt_dt_cd Value of lookup code.
2758 -- nip_pl_uom Value of lookup code.
2759 -- rqd_perd_enrt_nenrt_uom Value of lookup code.
2760 -- nip_acty_ref_perd_cd Value of lookup code.
2761 -- nip_enrt_info_rt_freq_cd Value of lookup code.
2762 -- prort_prtl_yr_cvg_rstrn_cd Value of lookup code.
2763 -- hc_svc_typ_cd Value of lookup code.
2764 -- pl_stat_cd Value of lookup code.
2765 -- prmry_fndg_mthd_cd Value of lookup code.
2766 -- prtn_end_dt_cd Value of lookup code.
2767 -- prtn_strt_dt_cd Value of lookup code.
2768 -- bnf_dsgn_cd Value of lookup code.
2769 -- mx_wtg_dt_to_use_cd Value of lookup code.
2770 -- unsspnd_enrt_cd Value of lookup code.
2771 -- imptd_incm_calc_cd Value of lookup code.
2772 -- effective_date effective date
2773 -- object_version_number Object version number of record being
2774 -- inserted or updated.
2775 --
2776 -- Post Success
2777 -- Processing continues
2778 --
2779 -- Post Failure
2780 -- Error handled by procedure
2781 --
2782 -- Access Status
2783 -- Internal table handler use only.
2784 --
2785 Procedure chk_all_lookups(p_pl_id in number,
2786 p_bnf_dflt_bnf_cd in varchar2,
2787 p_bnf_pct_amt_alwd_cd in varchar2,
2788 p_pl_cd in varchar2,
2789 p_cmpr_clms_to_cvg_or_bal_cd in varchar2,
2790 p_enrt_mthd_cd in varchar2,
2791 p_enrt_cd in varchar2,
2792 p_mx_wtg_perd_prte_uom in varchar2,
2793 p_nip_dflt_enrt_cd in varchar2,
2794 p_nip_pl_uom in varchar2,
2795 p_rqd_perd_enrt_nenrt_uom in varchar2,
2796 p_nip_acty_ref_perd_cd in varchar2,
2797 p_nip_enrt_info_rt_freq_cd in varchar2,
2798 p_prort_prtl_yr_cvg_rstrn_cd in varchar2,
2799 p_hc_svc_typ_cd in varchar2,
2800 p_pl_stat_cd in varchar2,
2801 p_prmry_fndg_mthd_cd in varchar2,
2802 p_bnf_dsgn_cd in varchar2,
2803 p_mx_wtg_dt_to_use_cd in varchar2,
2804 p_dflt_to_asn_pndg_ctfn_cd in varchar2,
2805 p_dpnt_dsgn_cd in varchar2,
2806 p_enrt_cvg_strt_dt_cd in varchar2,
2807 p_enrt_cvg_end_dt_cd in varchar2,
2808 p_dpnt_cvg_strt_dt_cd in varchar2,
2809 p_dpnt_cvg_end_dt_cd in varchar2,
2810 p_per_cvrd_cd in varchar2,
2811 p_rt_end_dt_cd in varchar2,
2812 p_rt_strt_dt_cd in varchar2,
2813 p_BNFT_OR_OPTION_RSTRCTN_CD in varchar2,
2814 p_CVG_INCR_R_DECR_ONLY_CD in varchar2,
2815 p_unsspnd_enrt_cd in varchar2,
2816 p_imptd_incm_calc_cd in varchar2,
2817 p_effective_date in date,
2818 p_object_version_number in number) is
2819 --
2820 --
2821 cursor c1 is select currency_code
2822 from fnd_currencies_vl
2823 where currency_code = p_nip_pl_uom
2824 and enabled_flag = 'Y';
2825
2826 l_proc varchar2(72) := g_package||'chk_all_lookups';
2827 l_api_updating boolean;
2831 --
2828 l_dummy varchar2(30);
2829 --
2830 Begin
2832 hr_utility.set_location('Entering:'||l_proc, 5);
2833 --
2834 l_api_updating := ben_pln_shd.api_updating
2835 (p_pl_id => p_pl_id,
2836 p_effective_date => p_effective_date,
2837 p_object_version_number => p_object_version_number);
2838 --
2839 if (l_api_updating
2840 and p_bnf_dflt_bnf_cd
2841 <> nvl(ben_pln_shd.g_old_rec.bnf_dflt_bnf_cd,hr_api.g_varchar2)
2842 or not l_api_updating)
2843 and p_bnf_dflt_bnf_cd is not null then
2844 --
2845 -- check if value of lookup falls within lookup type.
2846 --
2847 if hr_api.not_exists_in_hr_lookups
2848 (p_lookup_type => 'BEN_BNF_DFLT',
2849 p_lookup_code => p_bnf_dflt_bnf_cd,
2850 p_effective_date => p_effective_date) then
2851 --
2852 -- raise error as does not exist as lookup
2853 --
2854 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2855 fnd_message.set_token('FIELD', 'p_bnf_dflt_bnf_cd');
2856 fnd_message.set_token('TYPE', 'BEN_BNF_DFLT');
2857 fnd_message.raise_error;
2858 --
2859 end if;
2860 --
2861 end if;
2862 --
2863 if (l_api_updating
2864 and p_bnf_pct_amt_alwd_cd
2865 <> nvl(ben_pln_shd.g_old_rec.bnf_pct_amt_alwd_cd,hr_api.g_varchar2)
2866 or not l_api_updating)
2867 and p_bnf_pct_amt_alwd_cd is not null then
2868 --
2869 -- check if value of lookup falls within lookup type.
2870 --
2871 if hr_api.not_exists_in_hr_lookups
2872 (p_lookup_type => 'BEN_BNF_PCT_AMT_ALWD',
2873 p_lookup_code => p_bnf_pct_amt_alwd_cd,
2874 p_effective_date => p_effective_date) then
2875 --
2876 -- raise error as does not exist as lookup
2877 --
2878 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2879 fnd_message.set_token('FIELD', 'p_bnf_pct_amt_alwd_cd');
2880 fnd_message.set_token('TYPE', 'BEN_BNF_PCT_AMT_ALWD');
2881 fnd_message.raise_error;
2882 --
2883 end if;
2884 --
2885 end if;
2886 --
2887 if (l_api_updating
2888 and p_dpnt_dsgn_cd
2889 <> nvl(ben_pln_shd.g_old_rec.dpnt_dsgn_cd,hr_api.g_varchar2)
2890 or not l_api_updating)
2891 and p_dpnt_dsgn_cd is not null then
2892 --
2893 -- check if value of lookup falls within lookup type.
2894 --
2895 if hr_api.not_exists_in_hr_lookups
2896 (p_lookup_type => 'BEN_DPNT_DSGN',
2897 p_lookup_code => p_dpnt_dsgn_cd,
2898 p_effective_date => p_effective_date) then
2899 --
2900 -- raise error as does not exist as lookup
2901 --
2902 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2903 fnd_message.set_token('FIELD', 'p_dpnt_dsgn_cd');
2904 fnd_message.set_token('TYPE', 'BEN_DPNT_DSGN');
2905 fnd_message.raise_error;
2906 --
2907 end if;
2908 --
2909 end if;
2910 --
2911 if (l_api_updating
2912 and p_pl_cd
2913 <> nvl(ben_pln_shd.g_old_rec.pl_cd,hr_api.g_varchar2)
2914 or not l_api_updating)
2915 and p_pl_cd is not null then
2916 --
2917 -- check if value of lookup falls within lookup type.
2918 --
2919 if hr_api.not_exists_in_hr_lookups
2920 (p_lookup_type => 'BEN_PL',
2921 p_lookup_code => p_pl_cd,
2922 p_effective_date => p_effective_date) then
2923 --
2924 -- raise error as does not exist as lookup
2925 --
2926 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2927 fnd_message.set_token('FIELD', 'p_pl_cd');
2928 fnd_message.set_token('TYPE', 'BEN_PL');
2929 fnd_message.raise_error;
2930 --
2931 end if;
2932 --
2933 end if;
2934 --
2935 if (l_api_updating
2936 and p_cmpr_clms_to_cvg_or_bal_cd
2937 <> nvl(ben_pln_shd.g_old_rec.cmpr_clms_to_cvg_or_bal_cd,hr_api.g_varchar2)
2938 or not l_api_updating)
2939 and p_cmpr_clms_to_cvg_or_bal_cd is not null then
2940 --
2941 -- check if value of lookup falls within lookup type.
2942 --
2943 if hr_api.not_exists_in_hr_lookups
2944 (p_lookup_type => 'BEN_CMPR_CLMS_TO_CVG_OR_BAL',
2945 p_lookup_code => p_cmpr_clms_to_cvg_or_bal_cd,
2946 p_effective_date => p_effective_date) then
2947 --
2948 -- raise error as does not exist as lookup
2949 --
2950 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2951 fnd_message.set_token('FIELD', 'p_cmpr_clms_to_cvg_or_bal_cd');
2952 fnd_message.set_token('TYPE', 'BEN_CMPR_CLMS_TO_CVG_OR_BAL');
2953 fnd_message.raise_error;
2954 --
2955 end if;
2956 --
2957 end if;
2958 --
2959 if (l_api_updating
2960 and p_enrt_mthd_cd
2961 <> nvl(ben_pln_shd.g_old_rec.enrt_mthd_cd,hr_api.g_varchar2)
2962 or not l_api_updating)
2963 and p_enrt_mthd_cd is not null then
2964 --
2965 -- check if value of lookup falls within lookup type.
2966 --
2967 if hr_api.not_exists_in_hr_lookups
2968 (p_lookup_type => 'BEN_ENRT_MTHD',
2969 p_lookup_code => p_enrt_mthd_cd,
2973 --
2970 p_effective_date => p_effective_date) then
2971 --
2972 -- raise error as does not exist as lookup
2974 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2975 fnd_message.set_token('FIELD', 'p_enrt_mthd_cd');
2976 fnd_message.set_token('TYPE', 'BEN_ENRT_MTHD');
2977 fnd_message.raise_error;
2978 --
2979 end if;
2980 --
2981 end if;
2982 --
2983 if (l_api_updating
2984 and p_enrt_cd
2985 <> nvl(ben_pln_shd.g_old_rec.enrt_cd,hr_api.g_varchar2)
2986 or not l_api_updating)
2987 and p_enrt_cd is not null then
2988 --
2989 -- check if value of lookup falls within lookup type.
2990 --
2991 if hr_api.not_exists_in_hr_lookups
2992 (p_lookup_type => 'BEN_ENRT',
2993 p_lookup_code => p_enrt_cd,
2994 p_effective_date => p_effective_date) then
2995 --
2996 -- raise error as does not exist as lookup
2997 --
2998 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2999 fnd_message.set_token('FIELD', 'p_enrt_cd');
3000 fnd_message.set_token('TYPE', 'BEN_ENRT');
3001 fnd_message.raise_error;
3002 --
3003 end if;
3004 --
3005 end if;
3006 --
3007 if (l_api_updating
3008 and p_dflt_to_asn_pndg_ctfn_cd
3009 <> nvl(ben_pln_shd.g_old_rec.dflt_to_asn_pndg_ctfn_cd,hr_api.g_varchar2)
3010 or not l_api_updating)
3011 and p_dflt_to_asn_pndg_ctfn_cd is not null then
3012 --
3013 -- check if value of lookup falls within lookup type.
3014 --
3015 if hr_api.not_exists_in_hr_lookups
3016 (p_lookup_type => 'BEN_DFLT_TO_ASN_PNDG_CTFN',
3017 p_lookup_code => p_dflt_to_asn_pndg_ctfn_cd,
3018 p_effective_date => p_effective_date) then
3019 --
3020 -- raise error as does not exist as lookup
3021 --
3022 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3023 fnd_message.set_token('FIELD', 'p_dflt_to_asn_pndg_ctfn_cd');
3024 fnd_message.set_token('TYPE', 'BEN_DFLT_TO_ASN_PNDG_CTFN');
3025 fnd_message.raise_error;
3026 --
3027 end if;
3028 --
3029 end if;
3030 --
3031 if (l_api_updating
3032 and p_enrt_cvg_strt_dt_cd
3033 <> nvl(ben_pln_shd.g_old_rec.enrt_cvg_strt_dt_cd,hr_api.g_varchar2)
3034 or not l_api_updating)
3035 and p_enrt_cvg_strt_dt_cd is not null then
3036 --
3037 -- check if value of lookup falls within lookup type.
3038 --
3039 if hr_api.not_exists_in_hr_lookups
3040 (p_lookup_type => 'BEN_ENRT_CVG_STRT',
3041 p_lookup_code => p_enrt_cvg_strt_dt_cd,
3042 p_effective_date => p_effective_date) then
3043 --
3044 -- raise error as does not exist as lookup
3045 --
3046 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3047 fnd_message.set_token('FIELD', 'p_enrt_cvg_strt_dt_cd');
3048 fnd_message.set_token('TYPE', 'BEN_ENRT_CVG_STRT');
3049 fnd_message.raise_error;
3050 --
3051 end if;
3052 --
3053 end if;
3054 --
3055 if (l_api_updating
3056 and p_enrt_cvg_end_dt_cd
3057 <> nvl(ben_pln_shd.g_old_rec.enrt_cvg_end_dt_cd,hr_api.g_varchar2)
3058 or not l_api_updating)
3059 and p_enrt_cvg_end_dt_cd is not null then
3060 --
3061 -- check if value of lookup falls within lookup type.
3062 --
3063 if hr_api.not_exists_in_hr_lookups
3064 (p_lookup_type => 'BEN_ENRT_CVG_END',
3065 p_lookup_code => p_enrt_cvg_end_dt_cd,
3066 p_effective_date => p_effective_date) then
3067 --
3068 -- raise error as does not exist as lookup
3069 --
3070 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3071 fnd_message.set_token('FIELD', 'p_enrt_cvg_end_dt_cd');
3072 fnd_message.set_token('TYPE', 'BEN_ENRT_CVG_END');
3073 fnd_message.raise_error;
3074 --
3075 end if;
3076 --
3077 end if;
3078 --
3079 if (l_api_updating
3080 and p_mx_wtg_perd_prte_uom
3081 <> nvl(ben_pln_shd.g_old_rec.mx_wtg_perd_prte_uom,hr_api.g_varchar2)
3082 or not l_api_updating)
3083 and p_mx_wtg_perd_prte_uom is not null then
3084 --
3085 -- check if value of lookup falls within lookup type.
3086 --
3087 if hr_api.not_exists_in_hr_lookups
3088 (p_lookup_type => 'BEN_TM_UOM',
3089 p_lookup_code => p_mx_wtg_perd_prte_uom,
3090 p_effective_date => p_effective_date) then
3091 --
3092 -- raise error as does not exist as lookup
3093 --
3094 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3095 fnd_message.set_token('FIELD', 'p_mx_wtg_perd_prte_uom');
3096 fnd_message.set_token('TYPE', 'BEN_TM_UOM');
3097 fnd_message.raise_error;
3098 --
3099 end if;
3100 --
3101 end if;
3102 --
3103 if (l_api_updating
3104 and p_nip_dflt_enrt_cd
3105 <> nvl(ben_pln_shd.g_old_rec.nip_dflt_enrt_cd,hr_api.g_varchar2)
3106 or not l_api_updating)
3107 and p_nip_dflt_enrt_cd is not null then
3108 --
3109 -- check if value of lookup falls within lookup type.
3110 --
3114 p_effective_date => p_effective_date) then
3111 if hr_api.not_exists_in_hr_lookups
3112 (p_lookup_type => 'BEN_DFLT_ENRT',
3113 p_lookup_code => p_nip_dflt_enrt_cd,
3115 --
3116 -- raise error as does not exist as lookup
3117 --
3118 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3119 fnd_message.set_token('FIELD', 'p_nip_dflt_enrt_cd');
3120 fnd_message.set_token('TYPE', 'BEN_DFLT_ENRT');
3121 fnd_message.raise_error;
3122 --
3123 end if;
3124 --
3125 end if;
3126 --
3127 if (l_api_updating
3128 and p_dpnt_cvg_end_dt_cd
3129 <> nvl(ben_pln_shd.g_old_rec.dpnt_cvg_end_dt_cd,hr_api.g_varchar2)
3130 or not l_api_updating)
3131 and p_dpnt_cvg_end_dt_cd is not null then
3132 --
3133 -- check if value of lookup falls within lookup type.
3134 --
3135 if hr_api.not_exists_in_hr_lookups
3136 (p_lookup_type => 'BEN_DPNT_CVG_END',
3137 p_lookup_code => p_dpnt_cvg_end_dt_cd,
3138 p_effective_date => p_effective_date) then
3139 --
3140 -- raise error as does not exist as lookup
3141 --
3142 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3143 fnd_message.set_token('FIELD', 'p_dpnt_cvg_end_dt_cd');
3144 fnd_message.set_token('TYPE', 'BEN_DPNT_CVG_END');
3145 fnd_message.raise_error;
3146 --
3147 end if;
3148 --
3149 end if;
3150 --
3151 if (l_api_updating
3152 and p_dpnt_cvg_strt_dt_cd
3153 <> nvl(ben_pln_shd.g_old_rec.dpnt_cvg_strt_dt_cd,hr_api.g_varchar2)
3154 or not l_api_updating)
3155 and p_dpnt_cvg_strt_dt_cd is not null then
3156 --
3157 -- check if value of lookup falls within lookup type.
3158 --
3159 if hr_api.not_exists_in_hr_lookups
3160 (p_lookup_type => 'BEN_DPNT_CVG_STRT',
3161 p_lookup_code => p_dpnt_cvg_strt_dt_cd,
3162 p_effective_date => p_effective_date) then
3163 --
3164 -- raise error as does not exist as lookup
3165 --
3166 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3167 fnd_message.set_token('FIELD', 'p_dpnt_cvg_strt_dt_cd');
3168 fnd_message.set_token('TYPE', 'BEN_DPNT_CVG_STRT');
3169 fnd_message.raise_error;
3170 --
3171 end if;
3172 --
3173 end if;
3174 --
3175 if (l_api_updating
3176 and p_nip_pl_uom
3177 <> nvl(ben_pln_shd.g_old_rec.nip_pl_uom,hr_api.g_varchar2)
3178 or not l_api_updating)
3179 and p_nip_pl_uom is not null then
3180 --
3181 -- check if value of lookup falls within fnd_currencies.
3182 --
3183 open c1;
3184 fetch c1 into l_dummy;
3185 if c1%notfound then
3186 close c1;
3187 -- raise error as currency not found
3188 --
3189 fnd_message.set_name('BEN','BEN_91306_INV_PGM_UOM'); -- same msg for plan
3190 fnd_message.raise_error;
3191 end if;
3192 close c1;
3193 --
3194 end if;
3195 --
3196
3197 if (l_api_updating
3198 and p_nip_acty_ref_perd_cd
3199 <> nvl(ben_pln_shd.g_old_rec.nip_acty_ref_perd_cd,hr_api.g_varchar2)
3200 or not l_api_updating)
3201 and p_nip_acty_ref_perd_cd is not null then
3202 --
3203 -- check if value of lookup falls within lookup type.
3204 --
3205 if hr_api.not_exists_in_hr_lookups
3206 (p_lookup_type => 'BEN_ACTY_REF_PERD',
3207 p_lookup_code => p_nip_acty_ref_perd_cd,
3208 p_effective_date => p_effective_date) then
3209 --
3210 -- raise error as does not exist as lookup
3211 --
3212 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3213 fnd_message.set_token('FIELD', 'p_nip_acty_ref_perd_cd');
3214 fnd_message.set_token('TYPE', 'BEN_ACTY_REF_PERD');
3215 fnd_message.raise_error;
3216 --
3217 end if;
3218 --
3219 end if;
3220 --
3221 if (l_api_updating
3222 and p_nip_enrt_info_rt_freq_cd
3223 <> nvl(ben_pln_shd.g_old_rec.nip_enrt_info_rt_freq_cd,hr_api.g_varchar2)
3224 or not l_api_updating)
3225 and p_nip_enrt_info_rt_freq_cd is not null then
3226 --
3227 -- check if value of lookup falls within lookup type.
3228 --
3229 if hr_api.not_exists_in_hr_lookups
3230 (p_lookup_type => 'BEN_ENRT_INFO_RT_FREQ',
3231 p_lookup_code => p_nip_enrt_info_rt_freq_cd,
3232 p_effective_date => p_effective_date) then
3233 --
3234 -- raise error as does not exist as lookup
3235 --
3236 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3237 fnd_message.set_token('FIELD', 'p_nip_enrt_info_rt_freq_cd');
3238 fnd_message.set_token('TYPE', 'BEN_ENRT_INFO_RT_FREQ');
3239 fnd_message.raise_error;
3240 --
3241 end if;
3242 --
3243 end if;
3244 --
3245 if (l_api_updating
3246 and p_prort_prtl_yr_cvg_rstrn_cd
3247 <> nvl(ben_pln_shd.g_old_rec.prort_prtl_yr_cvg_rstrn_cd,hr_api.g_varchar2)
3248 or not l_api_updating)
3249 and p_prort_prtl_yr_cvg_rstrn_cd is not null then
3250 --
3254 (p_lookup_type => 'BEN_PRORT_PRTL_YR_CVG_RSTRN',
3251 -- check if value of lookup falls within lookup type.
3252 --
3253 if hr_api.not_exists_in_hr_lookups
3255 p_lookup_code => p_prort_prtl_yr_cvg_rstrn_cd,
3256 p_effective_date => p_effective_date) then
3257 --
3258 -- raise error as does not exist as lookup
3259 --
3260 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3261 fnd_message.set_token('FIELD', 'p_prort_prtl_yr_cvg_rstrn_cd');
3262 fnd_message.set_token('TYPE', 'BEN_PRORT_PRTL_YR_CVG_RSTRN');
3263 fnd_message.raise_error;
3264 --
3265 end if;
3266 --
3267 end if;
3268 --
3269 if (l_api_updating
3270 and p_hc_svc_typ_cd
3271 <> nvl(ben_pln_shd.g_old_rec.hc_svc_typ_cd,hr_api.g_varchar2)
3272 or not l_api_updating)
3273 and p_hc_svc_typ_cd is not null then
3274 --
3275 -- check if value of lookup falls within lookup type.
3276 --
3277 if hr_api.not_exists_in_hr_lookups
3278 (p_lookup_type => 'BEN_HLTH_CARE_SVC_TYP',
3279 p_lookup_code => p_hc_svc_typ_cd,
3280 p_effective_date => p_effective_date) then
3281 --
3282 -- raise error as does not exist as lookup
3283 --
3284 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3285 fnd_message.set_token('FIELD', 'p_hc_svc_typ_cd');
3286 fnd_message.set_token('TYPE', 'BEN_HLTH_CARE_SVC_TYP');
3287 fnd_message.raise_error;
3288 --
3289 end if;
3290 --
3291 end if;
3292 --
3293 if (l_api_updating
3294 and p_pl_stat_cd
3295 <> nvl(ben_pln_shd.g_old_rec.pl_stat_cd,hr_api.g_varchar2)
3296 or not l_api_updating)
3297 and p_pl_stat_cd is not null then
3298 --
3299 -- check if value of lookup falls within lookup type.
3300 --
3301 if hr_api.not_exists_in_hr_lookups
3302 (p_lookup_type => 'BEN_STAT',
3303 p_lookup_code => p_pl_stat_cd,
3304 p_effective_date => p_effective_date) then
3305 --
3306 -- raise error as does not exist as lookup
3307 --
3308 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3309 fnd_message.set_token('FIELD', 'p_pl_stat_cd');
3310 fnd_message.set_token('TYPE', 'BEN_STAT');
3311 fnd_message.raise_error;
3312 --
3313 end if;
3314 --
3315 end if;
3316 --
3317 if (l_api_updating
3318 and p_prmry_fndg_mthd_cd
3319 <> nvl(ben_pln_shd.g_old_rec.prmry_fndg_mthd_cd,hr_api.g_varchar2)
3320 or not l_api_updating)
3321 and p_prmry_fndg_mthd_cd is not null then
3322 --
3323 -- check if value of lookup falls within lookup type.
3324 --
3325 if hr_api.not_exists_in_hr_lookups
3326 (p_lookup_type => 'BEN_PRMRY_FNDG_MTHD',
3327 p_lookup_code => p_prmry_fndg_mthd_cd,
3328 p_effective_date => p_effective_date) then
3329 --
3330 -- raise error as does not exist as lookup
3331 --
3332 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3333 fnd_message.set_token('FIELD', 'p_prmry_fndg_mthd_cd');
3334 fnd_message.set_token('TYPE', 'BEN_PRMRY_FNDG_MTHD');
3335 fnd_message.raise_error;
3336 --
3337 end if;
3338 --
3339 end if;
3340 --
3341 if (l_api_updating
3342 and p_rt_end_dt_cd
3343 <> nvl(ben_pln_shd.g_old_rec.rt_end_dt_cd,hr_api.g_varchar2)
3344 or not l_api_updating)
3345 and p_rt_end_dt_cd is not null then
3346 --
3347 -- check if value of lookup falls within lookup type.
3348 --
3349 if hr_api.not_exists_in_hr_lookups
3350 (p_lookup_type => 'BEN_RT_END',
3351 p_lookup_code => p_rt_end_dt_cd,
3352 p_effective_date => p_effective_date) then
3353 --
3354 -- raise error as does not exist as lookup
3355 --
3356 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3357 fnd_message.set_token('FIELD', 'p_rt_end_dt_cd');
3358 fnd_message.set_token('TYPE', 'BEN_RT_END');
3359 fnd_message.raise_error;
3360 --
3361 end if;
3362 --
3363 end if;
3364 --
3365 if (l_api_updating
3366 and p_rt_strt_dt_cd
3367 <> nvl(ben_pln_shd.g_old_rec.rt_strt_dt_cd,hr_api.g_varchar2)
3368 or not l_api_updating)
3369 and p_rt_strt_dt_cd is not null then
3370 --
3371 -- check if value of lookup falls within lookup type.
3372 --
3373 if hr_api.not_exists_in_hr_lookups
3374 (p_lookup_type => 'BEN_RT_STRT',
3375 p_lookup_code => p_rt_strt_dt_cd,
3376 p_effective_date => p_effective_date) then
3377 --
3378 -- raise error as does not exist as lookup
3379 --
3380 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3381 fnd_message.set_token('FIELD', 'p_rt_strt_dt_cd');
3382 fnd_message.set_token('TYPE', 'BEN_RT_STRT');
3383 fnd_message.raise_error;
3384 --
3385 end if;
3386 --
3387 end if;
3388 --
3389 if (l_api_updating
3390 and p_bnf_dsgn_cd
3391 <> nvl(ben_pln_shd.g_old_rec.bnf_dsgn_cd,hr_api.g_varchar2)
3395 -- check if value of lookup falls within lookup type.
3392 or not l_api_updating)
3393 and p_bnf_dsgn_cd is not null then
3394 --
3396 --
3397 if hr_api.not_exists_in_hr_lookups
3398 (p_lookup_type => 'BEN_BNF_DSGN',
3399 p_lookup_code => p_bnf_dsgn_cd,
3400 p_effective_date => p_effective_date) then
3401 --
3402 -- raise error as does not exist as lookup
3403 --
3404 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3405 fnd_message.set_token('FIELD', 'p_bnf_dsgn_cd');
3406 fnd_message.set_token('TYPE', 'BEN_BNF_DSGN');
3407 fnd_message.raise_error;
3408 --
3409 end if;
3410 --
3411 end if;
3412 --
3413 if (l_api_updating
3414 and p_per_cvrd_cd
3415 <> nvl(ben_pln_shd.g_old_rec.per_cvrd_cd,hr_api.g_varchar2)
3416 or not l_api_updating)
3417 and p_per_cvrd_cd is not null then
3418 --
3419 -- check if value of lookup falls within lookup type.
3420 --
3421 if hr_api.not_exists_in_hr_lookups
3422 (p_lookup_type => 'BEN_PER_CVRD',
3423 p_lookup_code => p_per_cvrd_cd,
3424 p_effective_date => p_effective_date) then
3425 --
3426 -- raise error as does not exist as lookup
3427 --
3428 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3429 fnd_message.set_token('FIELD', 'p_per_cvrd_cd');
3430 fnd_message.set_token('TYPE', 'BEN_PER_CVRD');
3431 fnd_message.raise_error;
3432 --
3433 end if;
3434 --
3435 end if;
3436 --
3437 if (l_api_updating
3438 and p_mx_wtg_dt_to_use_cd
3439 <> nvl(ben_pln_shd.g_old_rec.mx_wtg_dt_to_use_cd,hr_api.g_varchar2)
3440 or not l_api_updating)
3441 and p_mx_wtg_dt_to_use_cd is not null then
3442 --
3443 -- check if value of lookup falls within lookup type.
3444 --
3445 if hr_api.not_exists_in_hr_lookups
3446 (p_lookup_type => 'BEN_MX_WTG_DT_TO_USE',
3447 p_lookup_code => p_mx_wtg_dt_to_use_cd,
3448 p_effective_date => p_effective_date) then
3449 --
3450 -- raise error as does not exist as lookup
3451 --
3452 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3453 fnd_message.set_token('FIELD', 'p_mx_wtg_dt_to_use_cd');
3454 fnd_message.set_token('TYPE', 'BEN_MX_WTG_DT_TO_USE');
3455 fnd_message.raise_error;
3456 --
3457 end if;
3458 --
3459 end if;
3460 --
3461 if (l_api_updating
3462 and p_BNFT_OR_OPTION_RSTRCTN_cd
3463 <> nvl(ben_pln_shd.g_old_rec.BNFT_OR_OPTION_RSTRCTN_cd,hr_api.g_varchar2)
3464 or not l_api_updating)
3465 and p_BNFT_OR_OPTION_RSTRCTN_cd is not null then
3466 --
3467 -- check if value of lookup falls within lookup type.
3468 --
3469 if hr_api.not_exists_in_hr_lookups
3470 (p_lookup_type => 'BEN_BNFT_R_OPT_RSTRN',
3471 p_lookup_code => p_BNFT_OR_OPTION_RSTRCTN_cd,
3472 p_effective_date => p_effective_date) then
3473 --
3474 -- raise error as does not exist as lookup
3475 --
3476 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3477 fnd_message.set_token('FIELD', 'p_bnft_or_option_rstrctn_cd');
3478 fnd_message.set_token('TYPE', 'BEN_BNFT_R_OPT_RSTRN');
3479 fnd_message.raise_error;
3480 --
3481 end if;
3482 --
3483 end if;
3484 if (l_api_updating
3485 and p_CVG_INCR_R_DECR_ONLY_cd
3486 <> nvl(ben_pln_shd.g_old_rec.CVG_INCR_R_DECR_ONLY_cd,hr_api.g_varchar2)
3487 or not l_api_updating)
3488 and p_CVG_INCR_R_DECR_ONLY_cd is not null then
3489 --
3490 -- check if value of lookup falls within lookup type.
3491 --
3492 if hr_api.not_exists_in_hr_lookups
3493 (p_lookup_type => 'BEN_CVG_INCR_R_DECR_ONLY',
3494 p_lookup_code => p_CVG_INCR_R_DECR_ONLY_cd,
3495 p_effective_date => p_effective_date) then
3496 --
3497 -- raise error as does not exist as lookup
3498 --
3499 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3500 fnd_message.set_token('FIELD', 'p_cvg_incr_r_decr_only_cd');
3501 fnd_message.set_token('TYPE', 'BEN_CVG_INCR_R_DECR_ONLY');
3502 fnd_message.raise_error;
3503 --
3504 end if;
3505 --
3506 end if;
3507 if (l_api_updating
3508 and p_unsspnd_enrt_cd
3509 <> nvl(ben_pln_shd.g_old_rec.unsspnd_enrt_cd,hr_api.g_varchar2)
3510 or not l_api_updating)
3511 and p_unsspnd_enrt_cd is not null then
3512 --
3513 -- check if value of lookup falls within lookup type.
3514 --
3515 if hr_api.not_exists_in_hr_lookups
3516 (p_lookup_type => 'BEN_UNSSPND_ENRT',
3517 p_lookup_code => p_unsspnd_enrt_cd,
3518 p_effective_date => p_effective_date) then
3519 --
3520 -- raise error as does not exist as lookup
3521 --
3522 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3523 fnd_message.set_token('FIELD', 'p_unsspnd_enrt_cd');
3524 fnd_message.set_token('TYPE', 'BEN_UNSSPND_ENRT');
3525 fnd_message.raise_error;
3526 --
3527 end if;
3528 --
3529 end if;
3533 <> nvl(ben_pln_shd.g_old_rec.imptd_incm_calc_cd,hr_api.g_varchar2)
3530 --
3531 if (l_api_updating
3532 and p_imptd_incm_calc_cd
3534 or not l_api_updating)
3535 and p_imptd_incm_calc_cd is not null then
3536 --
3537 -- check if value of lookup falls within lookup type.
3538 --
3539 if hr_api.not_exists_in_hr_lookups
3540 (p_lookup_type => 'BEN_IMPTD_INCM_TYP',
3541 p_lookup_code => p_imptd_incm_calc_cd,
3542 p_effective_date => p_effective_date) then
3543 --
3544 -- raise error as does not exist as lookup
3545 --
3546 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3547 fnd_message.set_token('FIELD', 'p_imptd_incm_calc_cd');
3548 fnd_message.set_token('TYPE', 'BEN_IMPTD_INCM_TYP');
3549 fnd_message.raise_error;
3550 --
3551 end if;
3552 --
3553 end if;
3554 hr_utility.set_location('Leaving:'||l_proc,10);
3555 --
3556 end chk_all_lookups;
3557 -- ----------------------------------------------------------------------------
3558 -- |--------------------------< chk_all_rules >-------------------------------|
3559 -- ----------------------------------------------------------------------------
3560 --
3561 -- Description
3562 -- This procedure is used to check that the Formula Rules are valid.
3563 --
3564 -- Pre Conditions
3565 -- None.
3566 --
3567 -- In Parameters
3568 -- pl_id PK of record being inserted or updated.
3569 -- auto_enrt_mthd_rl Value of formula rule id.
3570 -- mn_cvg_rl Value of formula rule id.
3571 -- mx_cvg_rl Value of formula rule id.
3572 -- mx_wtg_perd_rl Value of formula rule id.
3573 -- nip_dflt_enrt_det_rl Value of formula rule id.
3574 -- prort_prtl_yr_cvg_rstrn_rl Value of formula rule id.
3575 -- mx_wtg_dt_to_use_rl Value of formula rule id.
3576 -- effective_date effective date
3577 -- object_version_number Object version number of record being
3578 -- inserted or updated.
3579 --
3580 -- Post Success
3581 -- Processing continues
3582 --
3583 -- Post Failure
3584 -- Error handled by procedure
3585 --
3586 -- Access Status
3587 -- Internal table handler use only.
3588 --
3589 Procedure chk_all_rules(p_pl_id in number,
3590 p_business_group_id in number,
3591 p_auto_enrt_mthd_rl in number,
3592 p_mn_cvg_rl in number,
3593 p_mx_cvg_rl in number,
3594 p_mx_wtg_perd_rl in number,
3595 p_nip_dflt_enrt_det_rl in number,
3596 p_dpnt_cvg_end_dt_rl in number,
3597 p_dpnt_cvg_strt_dt_rl in number,
3598 p_prort_prtl_yr_cvg_rstrn_rl in number,
3599 p_mx_wtg_dt_to_use_rl in number,
3600 p_dflt_to_asn_pndg_ctfn_rl in number,
3601 p_enrt_cvg_end_dt_rl in number,
3602 p_postelcn_edit_rl in number,
3603 p_enrt_cvg_strt_dt_rl in number,
3604 p_rt_end_dt_rl in number,
3605 p_rt_strt_dt_rl in number,
3606 p_enrt_rl in number,
3607 p_rqd_perd_enrt_nenrt_rl in number,
3608 p_effective_date in date,
3609 p_object_version_number in number) is
3610 --
3611 l_proc varchar2(72) := g_package||'chk_all_rules';
3612 l_api_updating boolean;
3613 l_dummy varchar2(1);
3614 --
3615 cursor c1(p_rule number,p_rule_type_id number) is
3616 select null
3617 from ff_formulas_f ff,
3618 per_business_groups pbg
3619 where ff.formula_id = p_rule
3620 and ff.formula_type_id = p_rule_type_id
3621 and pbg.business_group_id = p_business_group_id
3622 and nvl(ff.business_group_id,p_business_group_id) =
3623 p_business_group_id
3624 and nvl(ff.legislation_code,pbg.legislation_code) =
3625 pbg.legislation_code
3626 and p_effective_date
3627 between ff.effective_start_date
3628 and ff.effective_end_date;
3629 --
3630 Begin
3631 --
3632 hr_utility.set_location('Entering:'||l_proc, 5);
3633 --
3634 l_api_updating := ben_pln_shd.api_updating
3635 (p_pl_id => p_pl_id,
3636 p_effective_date => p_effective_date,
3637 p_object_version_number => p_object_version_number);
3638 --
3639 if (l_api_updating
3640 and nvl(p_auto_enrt_mthd_rl,hr_api.g_number)
3641 <> ben_pln_shd.g_old_rec.auto_enrt_mthd_rl
3642 or not l_api_updating)
3643 and p_auto_enrt_mthd_rl is not null then
3644 --
3645 -- check if value of formula rule is valid.
3646 --
3647 open c1(p_auto_enrt_mthd_rl,-146); -- BEN_AUTO_ENRT_MTHD
3648 --
3649 -- fetch value from cursor if it returns a record then the
3650 -- formula is valid otherwise its invalid
3651 --
3652 fetch c1 into l_dummy;
3656 --
3653 if c1%notfound then
3654 --
3655 close c1;
3657 -- raise error
3658 --
3659 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3660 fnd_message.set_token('ID',p_auto_enrt_mthd_rl);
3661 fnd_message.set_token('TYPE_ID',-146);
3662 fnd_message.raise_error;
3663 --
3664 end if;
3665 --
3666 close c1;
3667 --
3668 end if;
3669 --
3670 if (l_api_updating
3671 and nvl(p_mn_cvg_rl,hr_api.g_number)
3672 <> ben_pln_shd.g_old_rec.mn_cvg_rl
3673 or not l_api_updating)
3674 and p_mn_cvg_rl is not null then
3675 --
3676 -- check if value of formula rule is valid.
3677 --
3678 open c1(p_mn_cvg_rl,-164); -- BEN_MN_CVG_CALC
3679 --
3680 -- fetch value from cursor if it returns a record then the
3681 -- formula is valid otherwise its invalid
3682 --
3683 fetch c1 into l_dummy;
3684 if c1%notfound then
3685 --
3686 close c1;
3687 --
3688 -- raise error
3689 --
3690 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3691 fnd_message.set_token('ID',p_mn_cvg_rl);
3692 fnd_message.set_token('TYPE_ID',-164);
3693 fnd_message.raise_error;
3694 --
3695 end if;
3696 --
3697 close c1;
3698 --
3699 end if;
3700 --
3701 if (l_api_updating
3702 and nvl(p_mx_cvg_rl,hr_api.g_number)
3703 <> ben_pln_shd.g_old_rec.mx_cvg_rl
3704 or not l_api_updating)
3705 and p_mx_cvg_rl is not null then
3706 --
3707 -- check if value of formula rule is valid.
3708 --
3709 open c1(p_mx_cvg_rl,-161); -- BEN_MX_CVG_CALC
3710 --
3711 -- fetch value from cursor if it returns a record then the
3712 -- formula is valid otherwise its invalid
3713 --
3714 fetch c1 into l_dummy;
3715 if c1%notfound then
3716 --
3717 close c1;
3718 --
3719 -- raise error
3720 --
3721 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3722 fnd_message.set_token('ID',p_mx_cvg_rl);
3723 fnd_message.set_token('TYPE_ID',-161);
3724 fnd_message.raise_error;
3725 --
3726 end if;
3727 --
3728 close c1;
3729 --
3730 end if;
3731 --
3732 if (l_api_updating
3733 and nvl(p_mx_wtg_perd_rl,hr_api.g_number)
3734 <> ben_pln_shd.g_old_rec.mx_wtg_perd_rl
3735 or not l_api_updating)
3736 and p_mx_wtg_perd_rl is not null then
3737 --
3738 -- check if value of formula rule is valid.
3739 --
3740 open c1(p_mx_wtg_perd_rl,-518); --BEN_MX_WTG_PERD_RL
3741 --
3742 -- fetch value from cursor if it returns a record then the
3743 -- formula is valid otherwise its invalid
3744 --
3745 fetch c1 into l_dummy;
3746 if c1%notfound then
3747 --
3748 close c1;
3749 --
3750 -- raise error
3751 --
3752 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3753 fnd_message.set_token('ID',p_mx_wtg_perd_rl);
3754 fnd_message.set_token('TYPE_ID',-518);
3755 fnd_message.raise_error;
3756 --
3757 end if;
3758 --
3759 close c1;
3760 --
3761 end if;
3762 --
3763 if (l_api_updating
3764 and nvl(p_rqd_perd_enrt_nenrt_rl,hr_api.g_number)
3765 <> ben_pln_shd.g_old_rec.rqd_perd_enrt_nenrt_rl
3766 or not l_api_updating)
3767 and p_rqd_perd_enrt_nenrt_rl is not null then
3768 --
3769 -- check if value of formula rule is valid.
3770 --
3771 open c1(p_rqd_perd_enrt_nenrt_rl,-513);
3772 --
3773 -- fetch value from cursor if it returns a record then the
3774 -- formula is valid otherwise its invalid
3775 --
3776 fetch c1 into l_dummy;
3777 if c1%notfound then
3778 --
3779 close c1;
3780 --
3781 -- raise error
3782 --
3783 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3784 fnd_message.set_token('ID',p_rqd_perd_enrt_nenrt_rl);
3785 fnd_message.set_token('TYPE_ID',-513);
3786 fnd_message.raise_error;
3787 --
3788 end if;
3789 --
3790 close c1;
3791 --
3792 end if;
3793 --
3794 if (l_api_updating
3795 and nvl(p_nip_dflt_enrt_det_rl,hr_api.g_number)
3796 <> ben_pln_shd.g_old_rec.nip_dflt_enrt_det_rl
3797 or not l_api_updating)
3798 and p_nip_dflt_enrt_det_rl is not null then
3799 --
3800 -- check if value of formula rule is valid.
3801 --
3802 open c1(p_nip_dflt_enrt_det_rl,-32); --BEN_DFLT_ENRT_DET
3803 --
3804 -- fetch value from cursor if it returns a record then the
3805 -- formula is valid otherwise its invalid
3806 --
3807 fetch c1 into l_dummy;
3808 if c1%notfound then
3809 --
3810 close c1;
3811 --
3812 -- raise error
3813 --
3814 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3818 --
3815 fnd_message.set_token('ID',p_nip_dflt_enrt_det_rl);
3816 fnd_message.set_token('TYPE_ID',-32);
3817 fnd_message.raise_error;
3819 end if;
3820 --
3821 close c1;
3822 --
3823 end if;
3824 --
3825 if (l_api_updating
3826 and nvl(p_dpnt_cvg_end_dt_rl,hr_api.g_number)
3827 <> ben_pln_shd.g_old_rec.dpnt_cvg_end_dt_rl
3828 or not l_api_updating)
3829 and p_dpnt_cvg_end_dt_rl is not null then
3830 --
3831 -- check if value of formula rule is valid.
3832 --
3833 open c1(p_dpnt_cvg_end_dt_rl,-28); --BEN_DPNT_CVG_END
3834 --
3835 -- fetch value from cursor if it returns a record then the
3836 -- formula is valid otherwise its invalid
3837 --
3838 fetch c1 into l_dummy;
3839 if c1%notfound then
3840 --
3841 close c1;
3842 --
3843 -- raise error
3844 --
3845 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3846 fnd_message.set_token('ID',p_dpnt_cvg_end_dt_rl);
3847 fnd_message.set_token('TYPE_ID',-28);
3848 fnd_message.raise_error;
3849 --
3850 end if;
3851 --
3852 close c1;
3853 --
3854 end if;
3855 --
3856 if (l_api_updating
3857 and nvl(p_dpnt_cvg_strt_dt_rl,hr_api.g_number)
3858 <> ben_pln_shd.g_old_rec.dpnt_cvg_strt_dt_rl
3859 or not l_api_updating)
3860 and p_dpnt_cvg_strt_dt_rl is not null then
3861 --
3862 -- check if value of formula rule is valid.
3863 --
3864 open c1(p_dpnt_cvg_strt_dt_rl,-27); --BEN_DPNT_CVG_STRT
3865 --
3866 -- fetch value from cursor if it returns a record then the
3867 -- formula is valid otherwise its invalid
3868 --
3869 fetch c1 into l_dummy;
3870 if c1%notfound then
3871 --
3872 close c1;
3873 --
3874 -- raise error
3875 --
3876 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3877 fnd_message.set_token('ID',p_dpnt_cvg_strt_dt_rl);
3878 fnd_message.set_token('TYPE_ID',-27);
3879 fnd_message.raise_error;
3880 --
3881 end if;
3882 --
3883 close c1;
3884 --
3885 end if;
3886 --
3887 if (l_api_updating
3888 and nvl(p_dflt_to_asn_pndg_ctfn_rl,hr_api.g_number)
3889 <> ben_pln_shd.g_old_rec.dflt_to_asn_pndg_ctfn_rl
3890 or not l_api_updating)
3891 and p_dflt_to_asn_pndg_ctfn_rl is not null then
3892 --
3893 -- check if value of formula rule is valid.
3894 --
3895 open c1(p_dflt_to_asn_pndg_ctfn_rl,-454);
3896 --
3897 -- fetch value from cursor if it returns a record then the
3898 -- formula is valid otherwise its invalid
3899 --
3900 fetch c1 into l_dummy;
3901 if c1%notfound then
3902 --
3903 close c1;
3904 --
3905 -- raise error
3906 --
3907 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3908 fnd_message.set_token('ID',p_dflt_to_asn_pndg_ctfn_rl);
3909 fnd_message.set_token('TYPE_ID',-454);
3910 fnd_message.raise_error;
3911 --
3912 end if;
3913 --
3914 close c1;
3915 --
3916 end if;
3917 --
3918 if (l_api_updating
3919 and nvl(p_postelcn_edit_rl,hr_api.g_number)
3920 <> ben_pln_shd.g_old_rec.postelcn_edit_rl
3921 or not l_api_updating)
3922 and p_postelcn_edit_rl is not null then
3923 --
3924 -- check if value of formula rule is valid.
3925 --
3926 open c1(p_postelcn_edit_rl,-215);
3927 --
3928 -- fetch value from cursor if it returns a record then the
3929 -- formula is valid otherwise its invalid
3930 --
3931 fetch c1 into l_dummy;
3932 if c1%notfound then
3933 --
3934 close c1;
3935 --
3936 -- raise error
3937 --
3938 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3939 fnd_message.set_token('ID',p_postelcn_edit_rl);
3940 fnd_message.set_token('TYPE_ID',-215);
3941 fnd_message.raise_error;
3942 --
3943 end if;
3944 --
3945 close c1;
3946 --
3947 end if;
3948 --
3949 if (l_api_updating
3950 and nvl(p_prort_prtl_yr_cvg_rstrn_rl,hr_api.g_number)
3951 <> ben_pln_shd.g_old_rec.prort_prtl_yr_cvg_rstrn_rl
3952 or not l_api_updating)
3953 and p_prort_prtl_yr_cvg_rstrn_rl is not null then
3954 --
3955 -- check if value of formula rule is valid.
3956 --
3957 open c1(p_prort_prtl_yr_cvg_rstrn_rl,-166); --BEN_PRTL_YR_CVG_RSTRN
3958 --
3959 -- fetch value from cursor if it returns a record then the
3960 -- formula is valid otherwise its invalid
3961 --
3962 fetch c1 into l_dummy;
3963 if c1%notfound then
3964 --
3965 close c1;
3966 --
3967 -- raise error
3968 --
3969 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3973 --
3970 fnd_message.set_token('ID',p_prort_prtl_yr_cvg_rstrn_rl);
3971 fnd_message.set_token('TYPE_ID',-166);
3972 fnd_message.raise_error;
3974 end if;
3975 --
3976 close c1;
3977 --
3978 end if;
3979 --
3980 if (l_api_updating
3981 and nvl(p_rt_end_dt_rl,hr_api.g_number)
3982 <> ben_pln_shd.g_old_rec.rt_end_dt_rl
3983 or not l_api_updating)
3984 and p_rt_end_dt_rl is not null then
3985 --
3986 -- check if value of formula rule is valid.
3987 --
3988 open c1(p_rt_end_dt_rl,-67);
3989 --
3990 -- fetch value from cursor if it returns a record then the
3991 -- formula is valid otherwise its invalid
3992 --
3993 fetch c1 into l_dummy;
3994 if c1%notfound then
3995 --
3996 close c1;
3997 --
3998 -- raise error
3999 --
4000 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
4001 fnd_message.set_token('ID',p_rt_end_dt_rl);
4002 fnd_message.set_token('TYPE_ID',-67);
4003 fnd_message.raise_error;
4004 --
4005 end if;
4006 --
4007 close c1;
4008 --
4009 end if;
4010 --
4011 if (l_api_updating
4012 and nvl(p_rt_strt_dt_rl,hr_api.g_number)
4013 <> ben_pln_shd.g_old_rec.rt_strt_dt_rl
4014 or not l_api_updating)
4015 and p_rt_strt_dt_rl is not null then
4016 --
4017 -- check if value of formula rule is valid.
4018 --
4019 open c1(p_rt_strt_dt_rl,-66);
4020 --
4021 -- fetch value from cursor if it returns a record then the
4022 -- formula is valid otherwise its invalid
4023 --
4024 fetch c1 into l_dummy;
4025 if c1%notfound then
4026 --
4027 close c1;
4028 --
4029 -- raise error
4030 --
4031 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
4032 fnd_message.set_token('ID',p_rt_strt_dt_rl);
4033 fnd_message.set_token('TYPE_ID',-66);
4034 fnd_message.raise_error;
4035 --
4036 end if;
4037 --
4038 close c1;
4039 --
4040 end if;
4041 --
4042 if (l_api_updating
4043 and nvl(p_mx_wtg_dt_to_use_rl,hr_api.g_number)
4044 <> ben_pln_shd.g_old_rec.mx_wtg_dt_to_use_rl
4045 or not l_api_updating)
4046 and p_mx_wtg_dt_to_use_rl is not null then
4047 --
4048 -- check if value of formula rule is valid.
4049 --
4050 open c1(p_mx_wtg_dt_to_use_rl,-162);
4051 --
4052 -- fetch value from cursor if it returns a record then the
4053 -- formula is valid otherwise its invalid
4054 --
4055 fetch c1 into l_dummy;
4056 if c1%notfound then
4057 --
4058 close c1;
4059 --
4060 -- raise error
4061 --
4062 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
4063 fnd_message.set_token('ID',p_mx_wtg_dt_to_use_rl);
4064 fnd_message.set_token('TYPE_ID',-162);
4065 fnd_message.raise_error;
4066 --
4067 end if;
4068 --
4069 close c1;
4070 --
4071 end if;
4072 --
4073 if (l_api_updating
4074 and nvl(p_enrt_cvg_strt_dt_rl,hr_api.g_number)
4075 <> ben_pln_shd.g_old_rec.enrt_cvg_strt_dt_rl
4076 or not l_api_updating)
4077 and p_enrt_cvg_strt_dt_rl is not null then
4078 --
4079 -- check if value of formula rule is valid.
4080 --
4081 open c1(p_enrt_cvg_strt_dt_rl,-29);
4082 --
4083 -- fetch value from cursor if it returns a record then the
4084 -- formula is valid otherwise its invalid
4085 --
4086 fetch c1 into l_dummy;
4087 if c1%notfound then
4088 --
4089 close c1;
4090 --
4091 -- raise error
4092 --
4093 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
4094 fnd_message.set_token('ID',p_enrt_cvg_strt_dt_rl);
4095 fnd_message.set_token('TYPE_ID',-29);
4096 fnd_message.raise_error;
4097 --
4098 end if;
4099 --
4100 close c1;
4101 --
4102 end if;
4103 --
4104 if (l_api_updating
4105 and nvl(p_enrt_cvg_end_dt_rl,hr_api.g_number)
4106 <> ben_pln_shd.g_old_rec.enrt_cvg_end_dt_rl
4107 or not l_api_updating)
4108 and p_enrt_cvg_end_dt_rl is not null then
4109 --
4110 -- check if value of formula rule is valid.
4111 --
4112 open c1(p_enrt_cvg_end_dt_rl,-30);
4113 --
4114 -- fetch value from cursor if it returns a record then the
4115 -- formula is valid otherwise its invalid
4116 --
4117 fetch c1 into l_dummy;
4118 if c1%notfound then
4119 --
4120 close c1;
4121 --
4122 -- raise error
4123 --
4124 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
4125 fnd_message.set_token('ID',p_enrt_cvg_end_dt_rl);
4126 fnd_message.set_token('TYPE_ID',-30);
4127 fnd_message.raise_error;
4128 --
4129 end if;
4133 end if;
4130 --
4131 close c1;
4132 --
4134 --
4135 if (l_api_updating
4136 and nvl(p_enrt_rl,hr_api.g_number)
4137 <> ben_pln_shd.g_old_rec.enrt_rl
4138 or not l_api_updating)
4139 and p_enrt_rl is not null then
4140 --
4141 -- check if value of formula rule is valid.
4142 --
4143 open c1(p_enrt_rl,-393);
4144 --
4145 -- fetch value from cursor if it returns a record then the
4146 -- formula is valid otherwise its invalid
4147 --
4148 fetch c1 into l_dummy;
4149 if c1%notfound then
4150 --
4151 close c1;
4152 --
4153 -- raise error
4154 --
4155 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
4156 fnd_message.set_token('ID',p_enrt_rl);
4157 fnd_message.set_token('TYPE_ID',-393);
4158 fnd_message.raise_error;
4159 --
4160 end if;
4161 --
4162 close c1;
4163 --
4164 end if;
4165 hr_utility.set_location('Leaving:'||l_proc,10);
4166 --
4167 end chk_all_rules;
4168 --
4169 -- ----------------------------------------------------------------------------
4170 -- |--------------------------< chk_cd_rl_combination >-------------------------------|
4171 -- ----------------------------------------------------------------------------
4172 --
4173 -- Description
4174 -- This procedure is used to check the code is RULE then the rule must be
4175 -- defined else it should not be.
4176 --
4177 -- Pre Conditions
4178 -- None.
4179 --
4180 -- In Parameters
4181 -- p_dpnt_cvg_end_dt_cd in varchar2
4182 -- p_dpnt_cvg_end_dt_rl in number,
4183 -- p_vrfy_fmly_mmbr_cd in varchar2,
4184 -- p_vrfy_fmly_mmbr_rl in number,
4185 -- p_dpnt_cvg_strt_dt_cd in varchar2,
4186 -- p_dpnt_cvg_strt_dt_rl in number,
4187 -- p_enrt_cvg_end_dt_cd in varchar2
4188 -- p_enrt_cvg_end_dt_rl in number,
4189 -- p_enrt_cvg_strt_dt_cd in varchar2,
4190 -- p_enrt_cvg_strt_dt_rl in number,
4191 -- p_rt_strt_dt_cd in varchar2,
4192 -- p_rt_strt_dt_rl in number,
4193 -- p_rt_end_dt_cd in varchar2,
4194 -- p_rt_end_dt_rl in number
4195 --
4196 -- object_version_number Object version number of record being
4197 -- inserted or updated.
4198 --
4199 -- Post Success
4200 -- Processing continues
4201 --
4202 -- Post Failure
4203 -- Error handled by procedure
4204 --
4205 -- Access Status
4206 -- Internal table handler use only.
4207 --
4208 procedure chk_cd_rl_combination
4209 (
4210 p_dpnt_cvg_end_dt_cd in varchar2,
4211 p_dpnt_cvg_end_dt_rl in number,
4212 p_vrfy_fmly_mmbr_cd in varchar2,
4213 p_vrfy_fmly_mmbr_rl in number,
4214 p_dpnt_cvg_strt_dt_cd in varchar2,
4215 p_dpnt_cvg_strt_dt_rl in number,
4216 p_enrt_cvg_end_dt_cd in varchar2,
4217 p_enrt_cvg_end_dt_rl in number,
4218 p_enrt_cvg_strt_dt_cd in varchar2,
4219 p_enrt_cvg_strt_dt_rl in number,
4220 p_rt_strt_dt_cd in varchar2,
4221 p_rt_strt_dt_rl in number,
4222 p_rt_end_dt_cd in varchar2,
4223 p_rt_end_dt_rl in number ) IS
4224 l_proc varchar2(72) := g_package||'chk_cd_rl_combination';
4225 BEGIN
4226 hr_utility.set_location('Entering:'||l_proc, 5);
4227 --
4228
4229 if (p_vrfy_fmly_mmbr_cd <> 'RL' and p_vrfy_fmly_mmbr_rl is not null)
4230 then
4231 fnd_message.set_name('BEN','BEN_91730_NO_RULE');
4232 fnd_message.raise_error;
4233 end if;
4234
4235 if (p_vrfy_fmly_mmbr_cd = 'RL' and p_vrfy_fmly_mmbr_rl is null)
4236 then
4237 fnd_message.set_name('BEN','BEN_91731_RULE');
4238 fnd_message.raise_error;
4239 end if;
4240 --
4241 if (p_dpnt_cvg_end_dt_cd <> 'RL' and p_dpnt_cvg_end_dt_rl is not null)
4242 then
4243 fnd_message.set_name('BEN','BEN_91730_NO_RULE');
4244 fnd_message.raise_error;
4245 end if;
4246
4247 if (p_dpnt_cvg_end_dt_cd = 'RL' and p_dpnt_cvg_end_dt_rl is null)
4248 then
4249 fnd_message.set_name('BEN','BEN_91731_RULE');
4250 fnd_message.raise_error;
4251 end if;
4252 --
4253 if (p_dpnt_cvg_strt_dt_cd <> 'RL' and p_dpnt_cvg_strt_dt_rl is not null)
4254 then
4255 fnd_message.set_name('BEN','BEN_91730_NO_RULE');
4256 fnd_message.raise_error;
4257 end if;
4258
4259 if (p_dpnt_cvg_strt_dt_cd = 'RL' and p_dpnt_cvg_strt_dt_rl is null)
4260 then
4261 fnd_message.set_name('BEN','BEN_91731_RULE');
4262 fnd_message.raise_error;
4263 end if;
4264 --
4265 if (p_enrt_cvg_end_dt_cd <> 'RL' and p_enrt_cvg_end_dt_rl is not null)
4266 then
4267 fnd_message.set_name('BEN','BEN_91730_NO_RULE');
4268 fnd_message.raise_error;
4269 end if;
4270
4271 if (p_enrt_cvg_end_dt_cd = 'RL' and p_enrt_cvg_end_dt_rl is null)
4272 then
4276 --
4273 fnd_message.set_name('BEN','BEN_91731_RULE');
4274 fnd_message.raise_error;
4275 end if;
4277 if (p_enrt_cvg_strt_dt_cd <> 'RL' and p_enrt_cvg_strt_dt_rl is not null)
4278 then
4279 fnd_message.set_name('BEN','BEN_91730_NO_RULE');
4280 fnd_message.raise_error;
4281 end if;
4282
4283 if (p_enrt_cvg_strt_dt_cd = 'RL' and p_enrt_cvg_strt_dt_rl is null)
4284 then
4285 fnd_message.set_name('BEN','BEN_91731_RULE');
4286 fnd_message.raise_error;
4287 end if;
4288 if (p_rt_strt_dt_cd <> 'RL' and p_rt_strt_dt_rl is not null)
4289 then
4290 fnd_message.set_name('BEN','BEN_91730_NO_RULE');
4291 fnd_message.raise_error;
4292 end if;
4293
4294 if (p_rt_strt_dt_cd = 'RL' and p_rt_strt_dt_rl is null)
4295 then
4296 fnd_message.set_name('BEN','BEN_91731_RULE');
4297 fnd_message.raise_error;
4298 end if;
4299 --
4300 if (p_rt_end_dt_cd <> 'RL' and p_rt_end_dt_rl is not null)
4301 then
4302 fnd_message.set_name('BEN','BEN_91730_NO_RULE');
4303 fnd_message.raise_error;
4304 end if;
4305
4306 if (p_rt_end_dt_cd = 'RL' and p_rt_end_dt_rl is null)
4307 then
4308 fnd_message.set_name('BEN','BEN_91731_RULE');
4309 fnd_message.raise_error;
4310 end if;
4311 -- Leaving Procedure.
4312 --
4313 hr_utility.set_location('Leaving:'||l_proc,10);
4314 --
4315 end chk_cd_rl_combination;
4316
4317 -- ----------------------------------------------------------------------------
4318 -- |-------------------------< chk_all_no_amount_flags >----------------------|
4319 -- ----------------------------------------------------------------------------
4320 --
4321 -- Description
4322 -- This procedure is used to check if the combination of the
4323 -- "no amount" flags and the "amount" values is valid.
4324 --
4325 -- Pre Conditions
4326 -- None.
4327 --
4328 -- In Parameters
4329 -- no_mn_cvg_dfnd_flag
4330 -- mn_cvg_rqd_amt
4331 -- no_mx_cvg_dfnd_flag
4332 -- mx_cvg_rqd_amt
4333 --
4334 -- Post Success
4335 -- Processing continues
4336 --
4337 -- Post Failure
4338 -- Error handled by procedure
4339 --
4340 -- Access Status
4341 -- Internal table handler use only.
4342 --
4343 Procedure chk_all_no_amount_flags
4344 (p_no_mn_cvg_amt_apls_flag in varchar2,
4345 p_mn_cvg_rqd_amt in number,
4346 p_no_mx_cvg_amt_apls_flag in varchar2,
4347 p_mx_cvg_alwd_amt in number) is
4348 --
4349 l_proc varchar2(72) := g_package||'chk_all_no_amount_flags';
4350 --
4351 Begin
4352 --
4353 hr_utility.set_location('Entering:'||l_proc, 5);
4354 --
4355 -- check if it is a valid combination
4356 --
4357 if ((p_no_mn_cvg_amt_apls_flag='Y' and p_mn_cvg_rqd_amt>0) or
4358 (p_no_mn_cvg_amt_apls_flag='N' and p_mn_cvg_rqd_amt=0)) then
4359 --
4360 -- raise error as is not a valid combination
4361 --
4362 fnd_message.set_name('BEN','BEN_91150_NO_MIN_CVG_APLS_FLAG');
4363 fnd_message.raise_error;
4364 --
4365 end if;
4366 --
4367 -- check if it is a valid combination
4368 --
4369 if ((p_no_mx_cvg_amt_apls_flag='Y' and p_mx_cvg_alwd_amt>0) or
4370 (p_no_mx_cvg_amt_apls_flag='N' and p_mx_cvg_alwd_amt=0)) then
4371 --
4372 -- raise error as is not a valid combination
4373 --
4374 fnd_message.set_name('BEN','BEN_91149_NO_MAX_CVG_APLS_FLAG');
4375 fnd_message.raise_error;
4376 --
4377 end if;
4378 --
4379 hr_utility.set_location('Leaving:'||l_proc,10);
4380 --
4381 end chk_all_no_amount_flags;
4382 --
4383 -- ----------------------------------------------------------------------------
4384 -- |---------------------------< chk_mn_val_mx_val >--------------------------|
4385 -- ----------------------------------------------------------------------------
4386 --
4387 -- Description
4388 -- This procedure is used to check if the min value is
4389 -- less than the max value.
4390 --
4391 -- Pre Conditions
4392 -- None.
4393 --
4394 -- In Parameters
4395 -- p_mn_val minimum value
4396 -- p_mx_val maximum value
4397 --
4398 -- Post Success
4399 -- Processing continues
4400 --
4401 -- Post Failure
4402 -- Error handled by procedure
4403 --
4404 -- Access Status
4405 -- Internal table handler use only.
4406 --
4407 Procedure chk_mn_val_mx_val(p_mn_val in number,
4408 p_mx_val in number) is
4409 --
4410 l_proc varchar2(72) := g_package||'chk_mn_val_mx_val';
4411 --
4412 Begin
4413 --
4414 hr_utility.set_location('Entering:'||l_proc, 5);
4415 --
4416 -- check the values
4417 -- note: Don't want an error if either one is null
4418 --
4419 if (p_mn_val >= p_mx_val) then
4420 --
4421 -- raise error as is not a valid combination
4422 --
4423 fnd_message.set_name('BEN','BEN_91142_MIN_LESS_NOT_EQ_MAX');
4424 fnd_message.raise_error;
4425 --
4426 end if;
4427 --
4431 -- ----------------------------------------------------------------------------
4428 hr_utility.set_location('Leaving:'||l_proc, 5);
4429 --
4430 end chk_mn_val_mx_val;
4432 -- |------------------------< chk_plan_oipl_mutexcl >--------------------------|
4433 -- ----------------------------------------------------------------------------
4434 --
4435 -- Description
4436 -- This procedure is used to check that the plan is mutually exclusive for the
4437 -- actl_prem_id. An oipl cannot exist with this actl_prem_id due to the ARC
4438 -- relationship on ben_actl_prem_f.
4439 --
4440 -- Pre Conditions
4441 -- None.
4442 --
4443 -- In Parameters
4444 -- pl_id PK of record being inserted or updated.
4445 -- actl_prem_id actl_prem_id.
4446 -- effective_date Session date of record.
4447 -- business_group_id Business group id of record being inserted.
4448 -- object_version_number Object version number of record being
4449 -- inserted or updated.
4450 --
4451 -- Post Success
4452 -- Processing continues
4453 --
4454 -- Post Failure
4455 -- Errors handled by the procedure
4456 --
4457 -- Access Status
4458 -- Internal table handler use only.
4459 --
4460 Procedure chk_plan_oipl_mutexcl(p_pl_id in number,
4461 p_actl_prem_id in number,
4462 p_effective_date in date,
4463 p_business_group_id in number,
4464 p_object_version_number in number) is
4465 --
4466 l_proc varchar2(72) := g_package||'chk_plan_oipl_mutexcl';
4467 l_api_updating boolean;
4468 l_dummy varchar2(1);
4469 --
4470 cursor c1 is
4471 select null
4472 from ben_oipl_f a
4473 where a.business_group_id +0 = p_business_group_id
4474 and a.actl_prem_id = p_actl_prem_id
4475 and p_effective_date
4476 between a.effective_start_date
4477 and a.effective_end_date;
4478 --
4479 Begin
4480 --
4481 hr_utility.set_location('Entering:'||l_proc, 5);
4482 --
4483 l_api_updating := ben_pln_shd.api_updating
4484 (p_effective_date => p_effective_date,
4485 p_pl_id => p_pl_id,
4486 p_object_version_number => p_object_version_number);
4487 --
4488 if (l_api_updating
4489 and p_actl_prem_id is not null) then
4490 --
4491 -- Check if actl_prem_id is mutually exclusive.
4492 --
4493 open c1;
4494 --
4495 fetch c1 into l_dummy;
4496 if c1%found then
4497 --
4498 close c1;
4499 --
4500 -- raise an error as this actl_prem_id has been assigned to oipl(s).
4501 --
4502 fnd_message.set_name('BEN','BEN_91610_PLAN_OPTION_EXCL1');
4503 fnd_message.raise_error;
4504 --
4505 end if;
4506 --
4507 close c1;
4508 --
4509 end if;
4510 --
4511 hr_utility.set_location('Leaving:'||l_proc, 10);
4512 --
4513 End chk_plan_oipl_mutexcl;
4514 -- ----------------------------------------------------------------------------
4515 -- |--------------------------< chk_nip_pln_uom >-----------------------------|
4516 -- ----------------------------------------------------------------------------
4517 --
4518 -- Description -- Procedure Added for Bug 2447647
4519 -- This procedure is used to check that the Currency field on
4520 -- the Not in Program tab is not null if the Plan Type linked
4521 -- to the Plan has the option type of 'Compensation Work Bench'
4522 --
4523 -- Pre Conditions
4524 -- None.
4525 --
4526 -- In Parameters
4527 -- p_pl_typ_id Id of the Plan type being linked to the plan
4528 -- p_pl_cd Plan Usage code
4529 -- p_nip_pl_uom Not in Program plan Currency
4530 -- p_effective_date Session date of record
4531 -- p_business_group_id Business group id of record being inserted/updated.
4532 --
4533 -- Post Success
4534 -- Processing continues
4535 --
4536 -- Post Failure
4537 -- Errors handled by the procedure
4538 --
4539 -- Access Status
4540 -- Internal table handler use only.
4541 --
4542 Procedure chk_nip_pln_uom(p_pl_typ_id in number,
4543 p_pl_cd in varchar2,
4544 p_nip_pl_uom in varchar2,
4545 p_effective_date in date,
4546 p_business_group_id in number) is
4547 --
4548 l_proc varchar2(72) := g_package||'chk_nip_pln_uom';
4549 l_opt_typ_cd varchar2(30);
4550 l_comp_typ_cd varchar2(30);
4551 --
4552 cursor c_pln_typ_opt_typ_cd is
4553 select opt_typ_cd ,comp_typ_cd
4554 from ben_pl_typ_f
4555 where pl_typ_id = p_pl_typ_id
4556 and business_group_id = p_business_group_id
4557 and p_effective_date
4558 between effective_start_date
4559 and effective_end_date;
4560 --
4561 Begin
4562 --
4563 hr_utility.set_location('Entering:'||l_proc, 5);
4564 --
4565 if (p_pl_typ_id is not null) then
4566 --
4567 -- Get the option type code of the plan type linked to the plan
4571 fetch c_pln_typ_opt_typ_cd into l_opt_typ_cd,l_comp_typ_cd;
4568 --
4569 open c_pln_typ_opt_typ_cd;
4570 --
4572 --
4573 if c_pln_typ_opt_typ_cd%found then
4574 --
4575 close c_pln_typ_opt_typ_cd;
4576 --
4577 -- Bug2808250: raising error only if the compensation category
4578 -- is not the "CWB - Other"
4579 --
4580 if l_opt_typ_cd is not null and ( l_opt_typ_cd = 'CWB'
4581 and l_comp_typ_cd <> 'ICM5' ) and p_pl_cd = 'MYNTBPGM' then
4582 --
4583 if p_nip_pl_uom is null then
4584 --
4585 -- raise an error as Currency field is null when Option Type code is CWB
4586 --
4587 fnd_message.set_name('BEN','BEN_93115_NIP_UOM_RQD');
4588 fnd_message.raise_error;
4589 null;
4590 end if;
4591 --
4592 end if;
4593 --
4594 else
4595 --
4596 close c_pln_typ_opt_typ_cd;
4597 --
4598 end if;
4599 end if;
4600 --
4601 hr_utility.set_location('Leaving:'||l_proc, 10);
4602 --
4603 End chk_nip_pln_uom;
4604 --
4605 -- bug 3876692
4606 --
4607 -- ----------------------------------------------------------------------------
4608 -- |------< chk_prfl_rule_extnce >------|
4609 -- ----------------------------------------------------------------------------
4610 --
4611 -- Description
4612 -- This procedure checks if they are already attached profiles or rules
4613 -- to the current plan and if yes, then it won't allow the plan to be of type
4614 -- flex shell or imputed income.
4615 --
4616 -- Pre-Conditions
4617 -- None.
4618 --
4619 -- In Parameters
4620 -- p_pl_id PK
4621 -- p_imptd_incm_calc_cd
4622 -- p_invk_flx_cr_pl_flag
4623 -- p_effective_date session date
4624 -- p_object_version_number number
4625 -- p_validation_start_date date
4626 -- p_validation_end_date date
4627 -- Post Success
4628 -- Processing continues
4629 --
4630 -- Post Failure
4631 -- Error raised.
4632 --
4633 -- Access Status
4634 -- Internal table handler use only.
4635 --
4636 Procedure chk_prfl_rule_extnce(
4637 p_pl_id in number,
4638 p_imptd_incm_calc_cd in varchar2,
4639 p_invk_flx_cr_pl_flag in varchar2,
4640 p_effective_date in date,
4641 p_object_version_number in number,
4642 p_validation_start_date in date,
4643 p_validation_end_date in date) is
4644 --
4645 l_proc varchar2(72) := g_package||'chk_prfl_rule_extnce';
4646 l_api_updating boolean;
4647 l_exists varchar2(1);
4648 --
4649 cursor c1 is
4650 select null
4651 from ben_prtn_elig_f epa, ben_prtn_elig_prfl_f cep, ben_prtn_eligy_rl_f cer
4652 where epa.pl_id = p_pl_id
4653 and ((epa.prtn_elig_id = cep.prtn_elig_id
4654 and p_validation_start_date <= cep.effective_end_date
4655 and p_validation_end_date >= cep.effective_start_date)
4656 or
4657 (epa.prtn_elig_id = cer.prtn_elig_id
4658 and p_validation_start_date <= cer.effective_end_date
4659 and p_validation_end_date >= cer.effective_start_date));
4660 --
4661 cursor c2 is
4662 select null
4663 from ben_prtn_elig_f epa, ben_prtn_elig_prfl_f cep,
4664 ben_prtn_eligy_rl_f cer, ben_plip_f plip
4665 where plip.pl_id = p_pl_id
4666 and epa.plip_id = plip.plip_id
4667 and p_effective_date between plip.effective_start_date and plip.effective_end_date
4668 and ((p_validation_start_date <= cep.effective_end_date
4669 and p_validation_end_date >= cep.effective_start_date
4670 and epa.prtn_elig_id = cep.prtn_elig_id)
4671 or
4672 (p_validation_start_date <= cer.effective_end_date
4673 and p_validation_end_date >= cer.effective_start_date
4674 and epa.prtn_elig_id = cer.prtn_elig_id));
4675 --
4676 Begin
4677 --
4678 hr_utility.set_location('Entering:'||l_proc,5);
4679 --
4680 l_api_updating := ben_pln_shd.api_updating
4681 (p_effective_date => p_effective_date,
4682 p_pl_id => p_pl_id,
4683 p_object_version_number => p_object_version_number);
4684 --
4685 if (l_api_updating
4686 and ((p_imptd_incm_calc_cd is not null and
4687 ben_pln_shd.g_old_rec.imptd_incm_calc_cd is null) or
4688 (p_invk_flx_cr_pl_flag = 'Y' and
4689 ben_pln_shd.g_old_rec.invk_flx_cr_pl_flag = 'N'))) then
4690 --
4691 open c1;
4692 fetch c1 into l_exists;
4693 if c1%found then
4694 close c1;
4695 --
4696 -- raise error as plan can't be made flex credit or imputed shell
4697 -- once eligibility profiles are attached to it
4698 --
4699 fnd_message.set_name('BEN','BEN_94066_PLN_UPD_VAL');
4700 fnd_message.raise_error;
4701 --
4702 else
4703 close c1;
4704 open c2;
4705 fetch c2 into l_exists;
4706 if c2%found then
4707 close c2;
4708 --
4712 fnd_message.set_name('BEN','BEN_94066_PLN_UPD_VAL');
4709 -- raise error as plan can't be made flex credit or imputed shell
4710 -- once eligibility rule are attached to it
4711 --
4713 fnd_message.raise_error;
4714 --
4715 end if;
4716 close c2;
4717 --
4718 end if;
4719 --
4720 end if;
4721 --
4722 hr_utility.set_location('Leaving:'||l_proc,10);
4723 --
4724 End chk_prfl_rule_extnce;
4725 --
4726 --
4727 -- ----------------------------------------------------------------------------
4728 -- |--------------------------< dt_update_validate >--------------------------|
4729 -- ----------------------------------------------------------------------------
4730 -- {Start Of Comments}
4731 --
4732 -- Description:
4733 -- This procedure is used for referential integrity of datetracked
4734 -- parent entities when a datetrack update operation is taking place
4735 -- and where there is no cascading of update defined for this entity.
4736 --
4737 -- Prerequisites:
4738 -- This procedure is called from the update_validate.
4739 --
4740 -- In Parameters:
4741 --
4742 -- Post Success:
4743 -- Processing continues.
4744 --
4745 -- Post Failure:
4746 --
4747 -- Developer Implementation Notes:
4748 -- This procedure should not need maintenance unless the HR Schema model
4749 -- changes.
4750 --
4751 -- Access Status:
4752 -- Internal Row Handler Use Only.
4753 --
4754 -- {End Of Comments}
4755 -- ----------------------------------------------------------------------------
4756 Procedure dt_update_validate
4757 (p_auto_enrt_mthd_rl in number default hr_api.g_number,
4758 p_mn_cvg_rl in number default hr_api.g_number,
4759 p_mx_cvg_rl in number default hr_api.g_number,
4760 p_mx_wtg_perd_rl in number default hr_api.g_number,
4761 p_nip_dflt_enrt_det_rl in number default hr_api.g_number,
4762 p_prort_prtl_yr_cvg_rstrn_rl in number default hr_api.g_number,
4763 p_pl_typ_id in number default hr_api.g_number,
4764 p_actl_prem_id in number default hr_api.g_number,
4765 p_mx_wtg_dt_to_use_rl in number default hr_api.g_number,
4766 p_dflt_to_asn_pndg_ctfn_rl in number default hr_api.g_number,
4767 p_dpnt_cvg_end_dt_rl in number default hr_api.g_number,
4768 p_dpnt_cvg_strt_dt_rl in number default hr_api.g_number,
4769 p_enrt_cvg_end_dt_rl in number default hr_api.g_number,
4770 p_enrt_cvg_strt_dt_rl in number default hr_api.g_number,
4771 p_postelcn_edit_rl in number default hr_api.g_number,
4772 p_rt_end_dt_rl in number default hr_api.g_number,
4773 p_rt_strt_dt_rl in number default hr_api.g_number,
4774 p_ENRT_RL in NUMBER default hr_api.g_NUMBER,
4775 p_rqd_perd_enrt_nenrt_rl in NUMBER default hr_api.g_NUMBER,
4776 p_bnft_prvdr_pool_id in number default hr_api.g_NUMBER,
4777 p_datetrack_mode in varchar2,
4778 p_validation_start_date in date,
4779 p_validation_end_date in date) Is
4780 --
4781 l_proc varchar2(72) := g_package||'dt_update_validate';
4782 l_integrity_error Exception;
4783 l_table_name all_tables.table_name%TYPE;
4784 --
4785 Begin
4786 hr_utility.set_location('Entering:'||l_proc, 5);
4787 --
4788 -- Ensure that the p_datetrack_mode argument is not null
4789 --
4790 hr_api.mandatory_arg_error
4791 (p_api_name => l_proc,
4792 p_argument => 'datetrack_mode',
4793 p_argument_value => p_datetrack_mode);
4794 --
4795 -- Only perform the validation if the datetrack update mode is valid
4796 --
4797 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
4798 --
4799 --
4800 -- Ensure the arguments are not null
4801 --
4802 hr_api.mandatory_arg_error
4803 (p_api_name => l_proc,
4804 p_argument => 'validation_start_date',
4805 p_argument_value => p_validation_start_date);
4806 --
4807 hr_api.mandatory_arg_error
4808 (p_api_name => l_proc,
4809 p_argument => 'validation_end_date',
4810 p_argument_value => p_validation_end_date);
4811 --
4812 If ((nvl(p_auto_enrt_mthd_rl, hr_api.g_number) <> hr_api.g_number) and
4813 NOT (dt_api.check_min_max_dates
4814 (p_base_table_name => 'ff_formulas_f',
4815 p_base_key_column => 'formula_id',
4816 p_base_key_value => p_auto_enrt_mthd_rl,
4817 p_from_date => p_validation_start_date,
4818 p_to_date => p_validation_end_date))) Then
4819 l_table_name := 'ff_formulas_f';
4820 Raise l_integrity_error;
4821 End If;
4822 If ((nvl(p_mn_cvg_rl, hr_api.g_number) <> hr_api.g_number) and
4823 NOT (dt_api.check_min_max_dates
4824 (p_base_table_name => 'ff_formulas_f',
4825 p_base_key_column => 'formula_id',
4826 p_base_key_value => p_mn_cvg_rl,
4827 p_from_date => p_validation_start_date,
4828 p_to_date => p_validation_end_date))) Then
4829 l_table_name := 'ff_formulas_f';
4830 Raise l_integrity_error;
4831 End If;
4835 p_base_key_column => 'formula_id',
4832 If ((nvl(p_mx_cvg_rl, hr_api.g_number) <> hr_api.g_number) and
4833 NOT (dt_api.check_min_max_dates
4834 (p_base_table_name => 'ff_formulas_f',
4836 p_base_key_value => p_mx_cvg_rl,
4837 p_from_date => p_validation_start_date,
4838 p_to_date => p_validation_end_date))) Then
4839 l_table_name := 'ff_formulas_f';
4840 Raise l_integrity_error;
4841 End If;
4842 If ((nvl(p_bnft_prvdr_pool_id, hr_api.g_number) <> hr_api.g_number) and
4843 NOT (dt_api.check_min_max_dates
4844 (p_base_table_name => 'ben_bnft_prvdr_pool_f',
4845 p_base_key_column => 'bnft_prvdr_pool_id',
4846 p_base_key_value => p_bnft_prvdr_pool_id,
4847 p_from_date => p_validation_start_date,
4848 p_to_date => p_validation_end_date))) Then
4849 l_table_name := 'ff_formulas_f';
4850 Raise l_integrity_error;
4851 End If;
4852 If ((nvl(p_enrt_rl, hr_api.g_number) <> hr_api.g_number) and
4853 NOT (dt_api.check_min_max_dates
4854 (p_base_table_name => 'ff_formulas_f',
4855 p_base_key_column => 'formula_id',
4856 p_base_key_value => p_enrt_rl,
4857 p_from_date => p_validation_start_date,
4858 p_to_date => p_validation_end_date))) Then
4859 l_table_name := 'ff_formulas_f';
4860 Raise l_integrity_error;
4861 End If;
4862 If ((nvl(p_rqd_perd_enrt_nenrt_rl, hr_api.g_number) <> hr_api.g_number) and
4863 NOT (dt_api.check_min_max_dates
4864 (p_base_table_name => 'ff_formulas_f',
4865 p_base_key_column => 'formula_id',
4866 p_base_key_value => p_rqd_perd_enrt_nenrt_rl,
4867 p_from_date => p_validation_start_date,
4868 p_to_date => p_validation_end_date))) Then
4869 l_table_name := 'ff_formulas_f';
4870 Raise l_integrity_error;
4871 End If;
4872 If ((nvl(p_mx_wtg_perd_rl, hr_api.g_number) <> hr_api.g_number) and
4873 NOT (dt_api.check_min_max_dates
4874 (p_base_table_name => 'ff_formulas_f',
4875 p_base_key_column => 'formula_id',
4876 p_base_key_value => p_mx_wtg_perd_rl,
4877 p_from_date => p_validation_start_date,
4878 p_to_date => p_validation_end_date))) Then
4879 l_table_name := 'ff_formulas_f';
4880 Raise l_integrity_error;
4881 End If;
4882 If ((nvl(p_nip_dflt_enrt_det_rl, hr_api.g_number) <> hr_api.g_number) and
4883 NOT (dt_api.check_min_max_dates
4884 (p_base_table_name => 'ff_formulas_f',
4885 p_base_key_column => 'formula_id',
4886 p_base_key_value => p_nip_dflt_enrt_det_rl,
4887 p_from_date => p_validation_start_date,
4888 p_to_date => p_validation_end_date))) Then
4889 l_table_name := 'ff_formulas_f';
4890 Raise l_integrity_error;
4891 End If;
4892 If ((nvl(p_dpnt_cvg_end_dt_rl, hr_api.g_number) <> hr_api.g_number) and
4893 NOT (dt_api.check_min_max_dates
4894 (p_base_table_name => 'ff_formulas_f',
4895 p_base_key_column => 'formula_id',
4896 p_base_key_value => p_dpnt_cvg_end_dt_rl,
4897 p_from_date => p_validation_start_date,
4898 p_to_date => p_validation_end_date))) Then
4899 l_table_name := 'ff_formulas_f';
4900 Raise l_integrity_error;
4901 End If;
4902 If ((nvl(p_dpnt_cvg_strt_dt_rl, hr_api.g_number) <> hr_api.g_number) and
4903 NOT (dt_api.check_min_max_dates
4904 (p_base_table_name => 'ff_formulas_f',
4905 p_base_key_column => 'formula_id',
4906 p_base_key_value => p_dpnt_cvg_strt_dt_rl,
4907 p_from_date => p_validation_start_date,
4908 p_to_date => p_validation_end_date))) Then
4909 l_table_name := 'ff_formulas_f';
4910 Raise l_integrity_error;
4911 End If;
4912 If ((nvl(p_prort_prtl_yr_cvg_rstrn_rl, hr_api.g_number) <> hr_api.g_number) and
4913 NOT (dt_api.check_min_max_dates
4914 (p_base_table_name => 'ff_formulas_f',
4915 p_base_key_column => 'formula_id',
4916 p_base_key_value => p_prort_prtl_yr_cvg_rstrn_rl,
4917 p_from_date => p_validation_start_date,
4918 p_to_date => p_validation_end_date))) Then
4919 l_table_name := 'ff_formulas_f';
4920 Raise l_integrity_error;
4921 End If;
4922 If ((nvl(p_pl_typ_id, hr_api.g_number) <> hr_api.g_number) and
4923 NOT (dt_api.check_min_max_dates
4924 (p_base_table_name => 'ben_pl_typ_f',
4925 p_base_key_column => 'pl_typ_id',
4926 p_base_key_value => p_pl_typ_id,
4927 p_from_date => p_validation_start_date,
4928 p_to_date => p_validation_end_date))) Then
4929 l_table_name := 'ben_pl_typ_f';
4930 Raise l_integrity_error;
4931 End If;
4932 --
4933
4934 -- Commented out because actl_prem_f is parent of plan!!!!!!!!!
4935 --
4936 -- If ((nvl(p_actl_prem_id, hr_api.g_number) <> hr_api.g_number) and
4937 -- NOT (dt_api.check_min_max_dates
4938 -- (p_base_table_name => 'ben_actl_prem_f',
4939 -- p_base_key_column => 'actl_prem_id',
4940 -- p_base_key_value => p_actl_prem_id,
4944 -- Raise l_integrity_error;
4941 -- p_from_date => p_validation_start_date,
4942 -- p_to_date => p_validation_end_date))) Then
4943 -- l_table_name := 'ben_actl_prem_f';
4945 -- End If;
4946 --
4947 If ((nvl(p_dflt_to_asn_pndg_ctfn_rl, hr_api.g_number) <> hr_api.g_number) and
4948 NOT (dt_api.check_min_max_dates
4949 (p_base_table_name => 'ff_formulas_f',
4950 p_base_key_column => 'formula_id',
4951 p_base_key_value => p_dflt_to_asn_pndg_ctfn_rl,
4952 p_from_date => p_validation_start_date,
4953 p_to_date => p_validation_end_date))) Then
4954 l_table_name := 'ff_formulas_f';
4955 Raise l_integrity_error;
4956 End If;
4957 --
4958 If ((nvl(p_dpnt_cvg_end_dt_rl, hr_api.g_number) <> hr_api.g_number) and
4959 NOT (dt_api.check_min_max_dates
4960 (p_base_table_name => 'ff_formulas_f',
4961 p_base_key_column => 'formula_id',
4962 p_base_key_value => p_dpnt_cvg_end_dt_rl,
4963 p_from_date => p_validation_start_date,
4964 p_to_date => p_validation_end_date))) Then
4965 l_table_name := 'ff_formulas_f';
4966 Raise l_integrity_error;
4967 End If;
4968 --
4969 If ((nvl(p_dpnt_cvg_strt_dt_rl, hr_api.g_number) <> hr_api.g_number) and
4970 NOT (dt_api.check_min_max_dates
4971 (p_base_table_name => 'ff_formulas_f',
4972 p_base_key_column => 'formula_id',
4973 p_base_key_value => p_dpnt_cvg_strt_dt_rl,
4974 p_from_date => p_validation_start_date,
4975 p_to_date => p_validation_end_date))) Then
4976 l_table_name := 'ff_formulas_f';
4977 Raise l_integrity_error;
4978 End If;
4979 --
4980 If ((nvl(p_enrt_cvg_end_dt_rl, hr_api.g_number) <> hr_api.g_number) and
4981 NOT (dt_api.check_min_max_dates
4982 (p_base_table_name => 'ff_formulas_f',
4983 p_base_key_column => 'formula_id',
4984 p_base_key_value => p_enrt_cvg_end_dt_rl,
4985 p_from_date => p_validation_start_date,
4986 p_to_date => p_validation_end_date))) Then
4987 l_table_name := 'ff_formulas_f';
4988 Raise l_integrity_error;
4989 End If;
4990 --
4991 If ((nvl(p_enrt_cvg_strt_dt_rl, hr_api.g_number) <> hr_api.g_number) and
4992 NOT (dt_api.check_min_max_dates
4993 (p_base_table_name => 'ff_formulas_f',
4994 p_base_key_column => 'formula_id',
4995 p_base_key_value => p_enrt_cvg_strt_dt_rl,
4996 p_from_date => p_validation_start_date,
4997 p_to_date => p_validation_end_date))) Then
4998 l_table_name := 'ff_formulas_f';
4999 Raise l_integrity_error;
5000 End If;
5001 --
5002 If ((nvl(p_postelcn_edit_rl, hr_api.g_number) <> hr_api.g_number) and
5003 NOT (dt_api.check_min_max_dates
5004 (p_base_table_name => 'ff_formulas_f',
5005 p_base_key_column => 'formula_id',
5006 p_base_key_value => p_postelcn_edit_rl,
5007 p_from_date => p_validation_start_date,
5008 p_to_date => p_validation_end_date))) Then
5009 l_table_name := 'ff_formulas_f';
5010 Raise l_integrity_error;
5011 End If;
5012 --
5013 If ((nvl(p_rt_end_dt_rl, hr_api.g_number) <> hr_api.g_number) and
5014 NOT (dt_api.check_min_max_dates
5015 (p_base_table_name => 'ff_formulas_f',
5016 p_base_key_column => 'formula_id',
5017 p_base_key_value => p_rt_end_dt_rl,
5018 p_from_date => p_validation_start_date,
5019 p_to_date => p_validation_end_date))) Then
5020 l_table_name := 'ff_formulas_f';
5021 Raise l_integrity_error;
5022 End If;
5023 --
5024 If ((nvl(p_rt_strt_dt_rl, hr_api.g_number) <> hr_api.g_number) and
5025 NOT (dt_api.check_min_max_dates
5026 (p_base_table_name => 'ff_formulas_f',
5027 p_base_key_column => 'formula_id',
5028 p_base_key_value => p_rt_strt_dt_rl,
5029 p_from_date => p_validation_start_date,
5030 p_to_date => p_validation_end_date))) Then
5031 l_table_name := 'ff_formulas_f';
5032 Raise l_integrity_error;
5033 End If;
5034 --
5035 End If;
5036 --
5037 hr_utility.set_location(' Leaving:'||l_proc, 10);
5038 Exception
5039 When l_integrity_error Then
5040 --
5041 -- A referential integrity check was violated therefore
5042 -- we must error
5043 --
5044 ben_utility.parent_integrity_error(p_table_name => l_table_name);
5045 --
5046 When Others Then
5047 --
5048 -- An unhandled or unexpected error has occurred which
5049 -- we must report
5050 --
5051 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
5052 fnd_message.set_token('PROCEDURE', l_proc);
5053 fnd_message.set_token('STEP','15');
5054 fnd_message.raise_error;
5055 End dt_update_validate;
5056 --
5057 -- ----------------------------------------------------------------------------
5058 -- |--------------------------< dt_delete_validate >--------------------------|
5059 -- ----------------------------------------------------------------------------
5063 -- This procedure is used for referential integrity of datetracked
5060 -- {Start Of Comments}
5061 --
5062 -- Description:
5064 -- child entities when either a datetrack DELETE or ZAP is in operation
5065 -- and where there is no cascading of delete defined for this entity.
5066 -- For the datetrack mode of DELETE or ZAP we must ensure that no
5067 -- datetracked child rows exist between the validation start and end
5068 -- dates.
5069 --
5070 -- Prerequisites:
5071 -- This procedure is called from the delete_validate.
5072 --
5073 -- In Parameters:
5074 --
5075 -- Post Success:
5076 -- Processing continues.
5077 --
5078 -- Post Failure:
5079 -- If a row exists by determining the returning Boolean value from the
5080 -- generic dt_api.rows_exist function then we must supply an error via
5081 -- the use of the local exception handler l_rows_exist.
5082 --
5083 -- Developer Implementation Notes:
5084 -- This procedure should not need maintenance unless the HR Schema model
5085 -- changes.
5086 --
5087 -- Access Status:
5088 -- Internal Row Handler Use Only.
5089 --
5090 -- {End Of Comments}
5091 -- ----------------------------------------------------------------------------
5092 Procedure dt_delete_validate
5093 (p_pl_id in number,
5094 p_datetrack_mode in varchar2,
5095 p_validation_start_date in date,
5096 p_validation_end_date in date,
5097 p_name in varchar2) Is
5098 --
5099 l_proc varchar2(72) := g_package||'dt_delete_validate';
5100 l_rows_exist Exception;
5101 l_table_name all_tables.table_name%TYPE;
5102 --
5103 l_val number;
5104 --
5105 Cursor c_yr_perd_exists(p_pl_id in number ) Is
5106 select 1
5107 from ben_popl_yr_perd t
5108 where t.pl_id = p_pl_id ;
5109 --
5110 Cursor c_cwb_exists(p_pl_id in number ) Is
5111 select 1
5112 from ben_cwb_wksht_grp t
5113 where t.pl_id = p_pl_id ;
5114 --
5115 Begin
5116 hr_utility.set_location('Entering:'||l_proc, 5);
5117 --
5118 -- Ensure that the p_datetrack_mode argument is not null
5119 --
5120 hr_api.mandatory_arg_error
5121 (p_api_name => l_proc,
5122 p_argument => 'datetrack_mode',
5123 p_argument_value => p_datetrack_mode);
5124 --
5125 -- Only perform the validation if the datetrack mode is either
5126 -- DELETE or ZAP
5127 --
5128 If (p_datetrack_mode = 'DELETE' or
5129 p_datetrack_mode = 'ZAP') then
5130 --
5131 --
5132 -- Ensure the arguments are not null
5133 --
5134 hr_api.mandatory_arg_error
5135 (p_api_name => l_proc,
5136 p_argument => 'validation_start_date',
5137 p_argument_value => p_validation_start_date);
5138 --
5139 hr_api.mandatory_arg_error
5140 (p_api_name => l_proc,
5141 p_argument => 'validation_end_date',
5142 p_argument_value => p_validation_end_date);
5143 --
5144 hr_api.mandatory_arg_error
5145 (p_api_name => l_proc,
5146 p_argument => 'pl_id',
5147 p_argument_value => p_pl_id);
5148 --
5149 If (dt_api.rows_exist
5150 (p_base_table_name => 'ben_pl_regy_bod_f',
5151 p_base_key_column => 'pl_id',
5152 p_base_key_value => p_pl_id,
5153 p_from_date => p_validation_start_date,
5154 p_to_date => p_validation_end_date)) Then
5155 l_table_name := 'ben_pl_regy_bod_f';
5156 Raise l_rows_exist;
5157 End If;
5158 If (dt_api.rows_exist
5159 (p_base_table_name => 'ben_oipl_f',
5160 p_base_key_column => 'pl_id',
5161 p_base_key_value => p_pl_id,
5162 p_from_date => p_validation_start_date,
5163 p_to_date => p_validation_end_date)) Then
5164 l_table_name := 'ben_oipl_f';
5165 Raise l_rows_exist;
5166 End If;
5167 If (dt_api.rows_exist
5168 (p_base_table_name => 'ben_popl_enrt_typ_cycl_f',
5169 p_base_key_column => 'pl_id',
5170 p_base_key_value => p_pl_id,
5171 p_from_date => p_validation_start_date,
5172 p_to_date => p_validation_end_date)) Then
5173 l_table_name := 'ben_popl_enrt_typ_cycl_f';
5174 Raise l_rows_exist;
5175 End If;
5176 If (dt_api.rows_exist
5177 (p_base_table_name => 'ben_vald_rlshp_for_reimb_f',
5178 p_base_key_column => 'pl_id',
5179 p_base_key_value => p_pl_id,
5180 p_from_date => p_validation_start_date,
5181 p_to_date => p_validation_end_date)) Then
5182 l_table_name := 'ben_vald_rlshp_for_reimb_f';
5183 Raise l_rows_exist;
5184 End If;
5185 If (dt_api.rows_exist
5186 (p_base_table_name => 'ben_ler_chg_pl_nip_enrt_f',
5187 p_base_key_column => 'pl_id',
5188 p_base_key_value => p_pl_id,
5189 p_from_date => p_validation_start_date,
5190 p_to_date => p_validation_end_date)) Then
5191 l_table_name := 'ben_ler_chg_pl_nip_enrt_f';
5192 Raise l_rows_exist;
5193 End If;
5194 If (dt_api.rows_exist
5195 (p_base_table_name => 'ben_pl_gd_or_svc_f',
5199 p_to_date => p_validation_end_date)) Then
5196 p_base_key_column => 'pl_id',
5197 p_base_key_value => p_pl_id,
5198 p_from_date => p_validation_start_date,
5200 l_table_name := 'ben_pl_gd_or_svc_f';
5201 Raise l_rows_exist;
5202 End If;
5203 If (dt_api.rows_exist
5204 (p_base_table_name => 'ben_plip_f',
5205 p_base_key_column => 'pl_id',
5206 p_base_key_value => p_pl_id,
5207 p_from_date => p_validation_start_date,
5208 p_to_date => p_validation_end_date)) Then
5209 l_table_name := 'ben_plip_f';
5210 Raise l_rows_exist;
5211 End If;
5212 If (dt_api.rows_exist
5213 (p_base_table_name => 'ben_pl_regn_f',
5214 p_base_key_column => 'pl_id',
5215 p_base_key_value => p_pl_id,
5216 p_from_date => p_validation_start_date,
5217 p_to_date => p_validation_end_date)) Then
5218 l_table_name := 'ben_pl_regn_f';
5219 Raise l_rows_exist;
5220 End If;
5221 If (dt_api.rows_exist
5222 (p_base_table_name => 'ben_elig_to_prte_rsn_f',
5223 p_base_key_column => 'pl_id',
5224 p_base_key_value => p_pl_id,
5225 p_from_date => p_validation_start_date,
5226 p_to_date => p_validation_end_date)) Then
5227 l_table_name := 'ben_elig_to_prte_rsn_f';
5228 Raise l_rows_exist;
5229 End If;
5230 If (dt_api.rows_exist
5231 (p_base_table_name => 'ben_prtn_elig_f',
5232 p_base_key_column => 'pl_id',
5233 p_base_key_value => p_pl_id,
5234 p_from_date => p_validation_start_date,
5235 p_to_date => p_validation_end_date)) Then
5236 l_table_name := 'ben_prtn_elig_f';
5237 Raise l_rows_exist;
5238 End If;
5239 If (dt_api.rows_exist
5240 (p_base_table_name => 'ben_cvg_amt_calc_mthd_f',
5241 p_base_key_column => 'pl_id',
5242 p_base_key_value => p_pl_id,
5243 p_from_date => p_validation_start_date,
5244 p_to_date => p_validation_end_date)) Then
5245 l_table_name := 'ben_cvg_amt_calc_mthd_f';
5246 Raise l_rows_exist;
5247 End If;
5248 If (dt_api.rows_exist
5249 (p_base_table_name => 'ben_ler_chg_dpnt_cvg_f',
5250 p_base_key_column => 'pl_id',
5251 p_base_key_value => p_pl_id,
5252 p_from_date => p_validation_start_date,
5253 p_to_date => p_validation_end_date)) Then
5254 l_table_name := 'ben_ler_chg_dpnt_cvg_f';
5255 Raise l_rows_exist;
5256 End If;
5257 If (dt_api.rows_exist
5258 (p_base_table_name => 'ben_popl_org_f',
5259 p_base_key_column => 'pl_id',
5260 p_base_key_value => p_pl_id,
5261 p_from_date => p_validation_start_date,
5262 p_to_date => p_validation_end_date)) Then
5263 l_table_name := 'ben_popl_org_f';
5264 Raise l_rows_exist;
5265 End If;
5266 If (dt_api.rows_exist
5267 (p_base_table_name => 'ben_elig_per_f',
5268 p_base_key_column => 'pl_id',
5269 p_base_key_value => p_pl_id,
5270 p_from_date => p_validation_start_date,
5271 p_to_date => p_validation_end_date)) Then
5272 l_table_name := 'ben_elig_per_f';
5273 Raise l_rows_exist;
5274 End If;
5275 If (dt_api.rows_exist
5276 (p_base_table_name => 'ben_acty_base_rt_f',
5277 p_base_key_column => 'pl_id',
5278 p_base_key_value => p_pl_id,
5279 p_from_date => p_validation_start_date,
5280 p_to_date => p_validation_end_date)) Then
5281 l_table_name := 'ben_acty_base_rt_f';
5282 Raise l_rows_exist;
5283 End If;
5284 If (dt_api.rows_exist
5285 (p_base_table_name => 'ben_pl_dpnt_cvg_ctfn_f',
5286 p_base_key_column => 'pl_id',
5287 p_base_key_value => p_pl_id,
5288 p_from_date => p_validation_start_date,
5289 p_to_date => p_validation_end_date)) Then
5290 l_table_name := 'ben_pl_dpnt_cvg_ctfn_f';
5291 Raise l_rows_exist;
5292 End If;
5293 If (dt_api.rows_exist
5294 (p_base_table_name => 'ben_pl_bnf_ctfn_f',
5295 p_base_key_column => 'pl_id',
5296 p_base_key_value => p_pl_id,
5297 p_from_date => p_validation_start_date,
5298 p_to_date => p_validation_end_date)) Then
5299 l_table_name := 'ben_pl_bnf_ctfn_f';
5300 Raise l_rows_exist;
5301 End If;
5302 If (dt_api.rows_exist
5303 (p_base_table_name => 'ben_popl_rptg_grp_f',
5304 p_base_key_column => 'pl_id',
5305 p_base_key_value => p_pl_id,
5306 p_from_date => p_validation_start_date,
5307 p_to_date => p_validation_end_date)) Then
5308 l_table_name := 'ben_popl_rptg_grp_f';
5309 Raise l_rows_exist;
5310 End If;
5311 If (dt_api.rows_exist
5312 (p_base_table_name => 'ben_prtt_reimbmt_rqst_f',
5313 p_base_key_column => 'pl_id',
5314 p_base_key_value => p_pl_id,
5315 p_from_date => p_validation_start_date,
5316 p_to_date => p_validation_end_date)) Then
5320 If (dt_api.rows_exist
5317 l_table_name := 'ben_prtt_reimbmt_rqst_f';
5318 Raise l_rows_exist;
5319 End If;
5321 (p_base_table_name => 'ben_apld_dpnt_cvg_elig_prfl_f',
5322 p_base_key_column => 'pl_id',
5323 p_base_key_value => p_pl_id,
5324 p_from_date => p_validation_start_date,
5325 p_to_date => p_validation_end_date)) Then
5326 l_table_name := 'ben_apld_dpnt_cvg_elig_prfl_f';
5327 Raise l_rows_exist;
5328 End If;
5329 If (dt_api.rows_exist
5330 (p_base_table_name => 'ben_dsgn_rqmt_f',
5331 p_base_key_column => 'pl_id',
5332 p_base_key_value => p_pl_id,
5333 p_from_date => p_validation_start_date,
5334 p_to_date => p_validation_end_date)) Then
5335 l_table_name := 'ben_dsgn_rqmt_f';
5336 Raise l_rows_exist;
5337 End If;
5338 If (dt_api.rows_exist
5339 (p_base_table_name => 'ben_elig_prtt_anthr_pl_prte_f',
5340 p_base_key_column => 'pl_id',
5341 p_base_key_value => p_pl_id,
5342 p_from_date => p_validation_start_date,
5343 p_to_date => p_validation_end_date)) Then
5344 l_table_name := 'ben_elig_prtt_anthr_pl_prte_f';
5345 Raise l_rows_exist;
5346 End If;
5347 If (dt_api.rows_exist
5348 (p_base_table_name => 'ben_pl_r_oipl_asset_f',
5349 p_base_key_column => 'pl_id',
5350 p_base_key_value => p_pl_id,
5351 p_from_date => p_validation_start_date,
5352 p_to_date => p_validation_end_date)) Then
5353 l_table_name := 'ben_pl_r_oipl_asset_f';
5354 Raise l_rows_exist;
5355 End If;
5356 If (dt_api.rows_exist
5357 (p_base_table_name => 'ben_prtt_enrt_rslt_f',
5358 p_base_key_column => 'pl_id',
5359 p_base_key_value => p_pl_id,
5360 p_from_date => p_validation_start_date,
5361 p_to_date => p_validation_end_date)) Then
5362 l_table_name := 'ben_prtt_enrt_rslt_f';
5363 Raise l_rows_exist;
5364 End If;
5365 If (dt_api.rows_exist
5366 (p_base_table_name => 'ben_vrbl_rt_prfl_f',
5367 p_base_key_column => 'pl_id',
5368 p_base_key_value => p_pl_id,
5369 p_from_date => p_validation_start_date,
5370 p_to_date => p_validation_end_date)) Then
5371 l_table_name := 'ben_vrbl_rt_prfl_f';
5372 Raise l_rows_exist;
5373 End If;
5374 If (dt_api.rows_exist
5375 (p_base_table_name => 'ben_wv_prtn_rsn_pl_f',
5376 p_base_key_column => 'pl_id',
5377 p_base_key_value => p_pl_id,
5378 p_from_date => p_validation_start_date,
5379 p_to_date => p_validation_end_date)) Then
5380 l_table_name := 'ben_wv_prtn_rsn_pl_f';
5381 Raise l_rows_exist;
5382 End If;
5383 If (dt_api.rows_exist
5384 (p_base_table_name => 'ben_bnft_rstrn_ctfn_f',
5385 p_base_key_column => 'pl_id',
5386 p_base_key_value => p_pl_id,
5387 p_from_date => p_validation_start_date,
5388 p_to_date => p_validation_end_date)) Then
5389 l_table_name := 'ben_bnft_rstrn_ctfn_f';
5390 Raise l_rows_exist;
5391 End If;
5392 /* If (dt_api.rows_exist -- Bug 4304937
5393 (p_base_table_name => 'ben_popl_yr_perd',
5394 p_base_key_column => 'pl_id',
5395 p_base_key_value => p_pl_id,
5396 p_from_date => p_validation_start_date,
5397 p_to_date => p_validation_end_date)) Then
5398 l_table_name := 'ben_popl_yr_perd';
5399 Raise l_rows_exist;
5400 End If;
5401 If (dt_api.rows_exist -- Bug 4304937
5402 (p_base_table_name => 'ben_cwb_wksht_grp',
5403 p_base_key_column => 'pl_id',
5404 p_base_key_value => p_pl_id,
5405 p_from_date => p_validation_start_date,
5406 p_to_date => p_validation_end_date)) Then
5407 l_table_name := 'ben_cwb_wksht_grp';
5408 Raise l_rows_exist;
5409 End If;
5410 */
5411 --
5412 open c_yr_perd_exists(p_pl_id );
5413 fetch c_yr_perd_exists into l_val ;
5414 if c_yr_perd_exists%found
5415 then
5416 close c_yr_perd_exists;
5417 l_table_name := 'ben_popl_yr_perd';
5418 Raise l_rows_exist;
5419 end if;
5420 close c_yr_perd_exists;
5421 --
5422 open c_cwb_exists(p_pl_id );
5423 fetch c_cwb_exists into l_val ;
5424 if c_cwb_exists%found
5425 then
5426 close c_cwb_exists;
5427 l_table_name := 'ben_cwb_wksht_grp';
5428 Raise l_rows_exist;
5429 end if;
5430 close c_cwb_exists;
5431 --
5432 --
5433 End If;
5434 --
5435 hr_utility.set_location(' Leaving:'||l_proc, 10);
5436 Exception
5437 When l_rows_exist Then
5438 --
5439 -- A referential integrity check was violated therefore
5440 -- we must error
5441 --
5442 ben_utility.child_exists_error(p_table_name => l_table_name,
5443 p_parent_table_name => 'BEN_PL_F', /* Bug 4057566 */
5447 --
5444 p_parent_entity_name => p_name); /* Bug 4057566 */
5445 --
5446 When Others Then
5448 -- An unhandled or unexpected error has occurred which
5449 -- we must report
5450 --
5451 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
5452 fnd_message.set_token('PROCEDURE', l_proc);
5453 fnd_message.set_token('STEP','15');
5454 fnd_message.raise_error;
5455 End dt_delete_validate;
5456 --
5457 -- ----------------------------------------------------------------------------
5458 -- |---------------------------< insert_validate >----------------------------|
5459 -- ----------------------------------------------------------------------------
5460 Procedure insert_validate
5461 (p_rec in ben_pln_shd.g_rec_type,
5462 p_effective_date in date,
5463 p_datetrack_mode in varchar2,
5464 p_validation_start_date in date,
5465 p_validation_end_date in date) is
5466 --
5467 l_proc varchar2(72) := g_package||'insert_validate';
5468 --
5469 Begin
5470 hr_utility.set_location('Entering:'||l_proc, 5);
5471 --
5472 -- Call all supporting business operations
5473 --
5474 if p_rec.business_group_id is not null and p_rec.legislation_code is null then
5475 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
5476 end if;
5477 --
5478 chk_pl_id
5479 (p_pl_id => p_rec.pl_id,
5480 p_effective_date => p_effective_date,
5481 p_object_version_number => p_rec.object_version_number);
5482 --
5483
5484 chk_plan_name_unique
5485 (p_pl_id => p_rec.pl_id,
5486 p_name => p_rec.name,
5487 p_effective_date => p_effective_date,
5488 p_business_group_id => p_rec.business_group_id,
5489 p_object_version_number => p_rec.object_version_number,
5490 p_validation_start_date => p_validation_start_date,
5491 p_validation_end_date => p_validation_end_date);
5492 --
5493 --Bug : 3475996
5494 chk_cwb_plan_type_uniq_plan(p_pl_id => p_rec.pl_id,
5495 p_name => p_rec.name,
5496 p_pl_typ_id => p_rec.pl_typ_id,
5497 p_group_pl_id => p_rec.group_pl_id,
5498 p_effective_date => p_effective_date,
5499 p_business_group_id => p_rec.business_group_id,
5500 p_object_version_number => p_rec.object_version_number,
5501 p_validation_start_date => p_validation_start_date,
5502 p_validation_end_date => p_validation_end_date);
5503 --
5504 chk_vrfy_fmly_mmbr_cd
5505 (p_pl_id => p_rec.pl_id,
5506 p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd,
5507 p_effective_date => p_effective_date,
5508 p_object_version_number => p_rec.object_version_number);
5509 --
5510
5511 chk_use_csd_rsd_prccng_cd
5512 (p_pl_id => p_rec.pl_id,
5513 p_use_csd_rsd_prccng_cd => p_rec.use_csd_rsd_prccng_cd,
5514 p_effective_date => p_effective_date,
5515 p_pl_cd => p_rec.pl_cd,
5516 p_object_version_number => p_rec.object_version_number);
5517
5518 /* -- Bug 2562196
5519 chk_dflt_to_asn_pndg_ctfn_cd
5520 (p_dflt_to_asn_pndg_ctfn_cd => p_rec.dflt_to_asn_pndg_ctfn_cd,
5521 p_pl_id => p_rec.pl_id,
5522 p_effective_date => p_effective_date,
5523 p_business_group_id => p_rec.business_group_id,
5524 p_object_version_number => p_rec.object_version_number);
5525 --
5526 */
5527 chk_vrfy_fmly_mmbr_rl
5528 (p_pl_id => p_rec.pl_id,
5529 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl,
5530 p_business_group_id => p_rec.business_group_id,
5531 p_effective_date => p_effective_date,
5532 p_object_version_number => p_rec.object_version_number);
5533 --
5534
5535 chk_measures_allowed
5536 (p_bnf_pct_amt_alwd_cd => p_rec.bnf_pct_amt_alwd_cd,
5537 p_bnf_incrmt_amt => p_rec.bnf_incrmt_amt,
5538 p_bnf_pct_incrmt_val => p_rec.bnf_pct_incrmt_val,
5539 p_bnf_mn_dsgntbl_amt => p_rec.bnf_mn_dsgntbl_amt);
5540 --
5541 chk_code_rule_dpnd
5542 (p_code => p_rec.mx_wtg_dt_to_use_cd,
5543 p_rule => p_rec.mx_wtg_dt_to_use_rl);
5544 --
5545 chk_mn_val_mn_flag_mn_rule
5546 (p_mn_cvg_rqd_amt => p_rec.mn_cvg_rqd_amt,
5547 p_no_mn_cvg_amt_apls_flag => p_rec.no_mn_cvg_amt_apls_flag,
5548 p_mn_cvg_rl => p_rec.mn_cvg_rl);
5549 --
5550 chk_dpnt_dsgn_cd
5551 (p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
5552 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
5553 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd);
5554 --
5555 chk_dpnt_dsgn_cd_detail
5556 (p_pl_id => p_rec.pl_id,
5557 p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
5558 p_bnf_dsgn_cd => p_rec.bnf_dsgn_cd,
5559 p_business_group_id => p_rec.business_group_id,
5560 p_effective_date => p_effective_date,
5561 p_validation_start_date => p_validation_start_date,
5562 p_validation_end_date => p_validation_end_date,
5563 p_object_version_number => p_rec.object_version_number);
5564 --
5565 chk_mx_val_mx_flag_mx_rule
5569 --
5566 (p_mx_cvg_alwd_amt => p_rec.mx_cvg_alwd_amt,
5567 p_no_mx_cvg_amt_apls_flag => p_rec.no_mx_cvg_amt_apls_flag,
5568 p_mx_cvg_rl => p_rec.mx_cvg_rl);
5570 chk_mutual_exclusive_flags
5571 (p_pl_id => p_rec.pl_id,
5572 p_invk_dcln_prtn_pl_flag => p_rec.invk_dcln_prtn_pl_flag,
5573 p_invk_flx_cr_pl_flag => p_rec.invk_flx_cr_pl_flag,
5574 p_svgs_pl_flag => p_rec.svgs_pl_flag,
5575 p_effective_date => p_effective_date,
5576 p_object_version_number => p_rec.object_version_number);
5577 --
5578 chk_mutual_exlsv_rule_num_uom
5579 (p_pl_id => p_rec.pl_id,
5580 p_mx_wtg_perd_rl => p_rec.mx_wtg_perd_rl,
5581 p_mx_wtg_perd_prte_val => p_rec.mx_wtg_perd_prte_val,
5582 p_mx_wtg_perd_prte_uom => p_rec.mx_wtg_perd_prte_uom,
5583 p_effective_date => p_effective_date,
5584 p_object_version_number => p_rec.object_version_number);
5585 --
5586 chk_all_flags
5587 (p_pl_id => p_rec.pl_id,
5588 p_alws_qdro_flag => p_rec.alws_qdro_flag,
5589 p_alws_qmcso_flag => p_rec.alws_qmcso_flag,
5590 p_bnf_addl_instn_txt_alwd_flag => p_rec.bnf_addl_instn_txt_alwd_flag,
5591 p_bnf_adrs_rqd_flag => p_rec.bnf_adrs_rqd_flag,
5592 p_bnf_cntngt_bnfs_alwd_flag => p_rec.bnf_cntngt_bnfs_alwd_flag,
5593 p_bnf_ctfn_rqd_flag => p_rec.bnf_ctfn_rqd_flag,
5594 p_bnf_dob_rqd_flag => p_rec.bnf_dob_rqd_flag,
5595 p_bnf_dsge_mnr_ttee_rqd_flag => p_rec.bnf_dsge_mnr_ttee_rqd_flag,
5596 p_bnf_legv_id_rqd_flag => p_rec.bnf_legv_id_rqd_flag,
5597 p_bnf_may_dsgt_org_flag => p_rec.bnf_may_dsgt_org_flag,
5598 p_bnf_qdro_rl_apls_flag => p_rec.bnf_qdro_rl_apls_flag,
5599 p_drvbl_fctr_apls_rts_flag => p_rec.drvbl_fctr_apls_rts_flag,
5600 p_drvbl_fctr_prtn_elig_flag => p_rec.drvbl_fctr_prtn_elig_flag,
5601 p_elig_apls_flag => p_rec.elig_apls_flag,
5602 p_invk_dcln_prtn_pl_flag => p_rec.invk_dcln_prtn_pl_flag,
5603 p_invk_flx_cr_pl_flag => p_rec.invk_flx_cr_pl_flag,
5604 p_drvbl_dpnt_elig_flag => p_rec.drvbl_dpnt_elig_flag,
5605 p_trk_inelig_per_flag => p_rec.trk_inelig_per_flag,
5606 p_dpnt_cvd_by_othr_apls_flag => p_rec.dpnt_cvd_by_othr_apls_flag,
5607 p_frfs_aply_flag => p_rec.frfs_aply_flag,
5608 p_hc_pl_subj_hcfa_aprvl_flag => p_rec.hc_pl_subj_hcfa_aprvl_flag,
5609 p_hghly_cmpd_rl_apls_flag => p_rec.hghly_cmpd_rl_apls_flag,
5610 p_no_mn_cvg_amt_apls_flag => p_rec.no_mn_cvg_amt_apls_flag,
5611 p_no_mn_cvg_incr_apls_flag => p_rec.no_mn_cvg_incr_apls_flag,
5612 p_no_mn_opts_num_apls_flag => p_rec.no_mn_opts_num_apls_flag,
5613 p_no_mx_cvg_amt_apls_flag => p_rec.no_mx_cvg_amt_apls_flag,
5614 p_no_mx_cvg_incr_apls_flag => p_rec.no_mx_cvg_incr_apls_flag,
5615 p_no_mx_opts_num_apls_flag => p_rec.no_mx_opts_num_apls_flag,
5616 p_prtn_elig_ovrid_alwd_flag => p_rec.prtn_elig_ovrid_alwd_flag,
5617 p_subj_to_imptd_incm_typ_cd => p_rec.subj_to_imptd_incm_typ_cd,
5618 p_use_all_asnts_elig_flag => p_rec.use_all_asnts_elig_flag,
5619 p_use_all_asnts_for_rt_flag => p_rec.use_all_asnts_for_rt_flag,
5620 p_vstg_apls_flag => p_rec.vstg_apls_flag,
5621 p_wvbl_flag => p_rec.wvbl_flag,
5622 p_alws_reimbmts_flag => p_rec.alws_reimbmts_flag,
5623 p_dpnt_adrs_rqd_flag => p_rec.dpnt_adrs_rqd_flag,
5624 p_dpnt_dob_rqd_flag => p_rec.dpnt_dob_rqd_flag,
5625 p_dpnt_leg_id_rqd_flag => p_rec.dpnt_leg_id_rqd_flag,
5626 p_dpnt_no_ctfn_rqd_flag => p_rec.dpnt_no_ctfn_rqd_flag,
5627 p_svgs_pl_flag => p_rec.svgs_pl_flag,
5628 p_enrt_pl_opt_flag => p_rec.enrt_pl_opt_flag,
5629 p_MAY_ENRL_PL_N_OIPL_FLAG => p_rec.may_enrl_pl_n_oipl_flag,
5630 p_ALWS_UNRSTRCTD_ENRT_FLAG => p_rec.alws_unrstrctd_enrt_flag,
5631 p_ALWS_TMPRY_ID_CRD_FLAG => p_rec.alws_tmpry_id_crd_flag,
5632 p_nip_dflt_flag => p_rec.nip_dflt_flag,
5633 p_post_to_gl_flag => p_rec.post_to_gl_flag,
5634 p_effective_date => p_effective_date,
5635 p_object_version_number => p_rec.object_version_number);
5636 --
5637 chk_all_lookups
5638 (p_pl_id => p_rec.pl_id,
5639 p_bnf_dflt_bnf_cd => p_rec.bnf_dflt_bnf_cd,
5640 p_bnf_pct_amt_alwd_cd => p_rec.bnf_pct_amt_alwd_cd,
5641 p_pl_cd => p_rec.pl_cd,
5642 p_cmpr_clms_to_cvg_or_bal_cd => p_rec.cmpr_clms_to_cvg_or_bal_cd,
5643 p_enrt_mthd_cd => p_rec.enrt_mthd_cd,
5644 p_enrt_cd => p_rec.enrt_cd,
5645 p_mx_wtg_perd_prte_uom => p_rec.mx_wtg_perd_prte_uom,
5646 p_nip_dflt_enrt_cd => p_rec.nip_dflt_enrt_cd,
5647 p_nip_pl_uom => p_rec.nip_pl_uom,
5648 p_rqd_perd_enrt_nenrt_uom => p_rec.rqd_perd_enrt_nenrt_uom,
5649 p_nip_acty_ref_perd_cd => p_rec.nip_acty_ref_perd_cd,
5650 p_nip_enrt_info_rt_freq_cd => p_rec.nip_enrt_info_rt_freq_cd,
5651 p_prort_prtl_yr_cvg_rstrn_cd => p_rec.prort_prtl_yr_cvg_rstrn_cd,
5652 p_hc_svc_typ_cd => p_rec.hc_svc_typ_cd,
5653 p_pl_stat_cd => p_rec.pl_stat_cd,
5654 p_prmry_fndg_mthd_cd => p_rec.prmry_fndg_mthd_cd,
5655 p_bnf_dsgn_cd => p_rec.bnf_dsgn_cd,
5659 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
5656 p_mx_wtg_dt_to_use_cd => p_rec.mx_wtg_dt_to_use_cd,
5657 p_dflt_to_asn_pndg_ctfn_cd => p_rec.dflt_to_asn_pndg_ctfn_cd,
5658 p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
5660 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
5661 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
5662 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
5663 p_per_cvrd_cd => p_rec.per_cvrd_cd,
5664 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
5665 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
5666 p_BNFT_OR_OPTION_RSTRCTN_CD => p_rec.BNFT_OR_OPTION_RSTRCTN_CD,
5667 p_CVG_INCR_R_DECR_ONLY_CD => p_rec.CVG_INCR_R_DECR_ONLY_CD,
5668 p_unsspnd_enrt_cd => p_rec.unsspnd_enrt_cd,
5669 p_imptd_incm_calc_cd => p_rec.imptd_incm_calc_cd,
5670 p_effective_date => p_effective_date,
5671 p_object_version_number => p_rec.object_version_number);
5672 --
5673 chk_all_rules
5674 (p_pl_id => p_rec.pl_id,
5675 p_business_group_id => p_rec.business_group_id,
5676 p_auto_enrt_mthd_rl => p_rec.auto_enrt_mthd_rl,
5677 p_mn_cvg_rl => p_rec.mn_cvg_rl,
5678 p_mx_cvg_rl => p_rec.mx_cvg_rl,
5679 p_mx_wtg_perd_rl => p_rec.mx_wtg_perd_rl,
5680 p_nip_dflt_enrt_det_rl => p_rec.nip_dflt_enrt_det_rl,
5681 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl ,
5682 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
5683 p_prort_prtl_yr_cvg_rstrn_rl => p_rec.prort_prtl_yr_cvg_rstrn_rl,
5684 p_mx_wtg_dt_to_use_rl => p_rec.mx_wtg_dt_to_use_rl,
5685 p_dflt_to_asn_pndg_ctfn_rl => p_rec.dflt_to_asn_pndg_ctfn_rl,
5686 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
5687 p_postelcn_edit_rl => p_rec.postelcn_edit_rl,
5688 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
5689 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
5690 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
5691 p_enrt_rl => p_rec.enrt_rl,
5692 p_rqd_perd_enrt_nenrt_rl => p_rec.rqd_perd_enrt_nenrt_rl,
5693 p_effective_date => p_effective_date,
5694 p_object_version_number => p_rec.object_version_number);
5695 --
5696 --
5697 chk_cd_rl_combination
5698 (p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
5699 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl,
5700 p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd,
5701 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl,
5702 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
5703 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
5704 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
5705 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
5706 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
5707 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
5708 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
5709 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
5710 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
5711 p_rt_end_dt_rl => p_rec.rt_end_dt_rl);
5712 --
5713 chk_all_no_amount_flags
5714 (p_no_mn_cvg_amt_apls_flag => p_rec.no_mn_cvg_amt_apls_flag,
5715 p_mn_cvg_rqd_amt => p_rec.mn_cvg_rqd_amt,
5716 p_no_mx_cvg_amt_apls_flag => p_rec.no_mx_cvg_amt_apls_flag,
5717 p_mx_cvg_alwd_amt => p_rec.mx_cvg_alwd_amt);
5718 --
5719 chk_mn_val_mx_val
5720 (p_mn_val => p_rec.mn_cvg_rqd_amt,
5721 p_mx_val => p_rec.mx_cvg_alwd_amt);
5722 --
5723 chk_flag_and_val
5724 (p_flag => p_rec.no_mn_opts_num_apls_flag,
5725 p_val => p_rec.mn_opts_rqd_num,
5726 p_msg => 'BEN_91695_MIN_VAL_FLAG_EXCLSV');
5727 --
5728 chk_flag_and_val
5729 (p_flag => p_rec.no_mx_opts_num_apls_flag,
5730 p_val => p_rec.mx_opts_alwd_num,
5731 p_msg => 'BEN_91696_MAX_VAL_FLAG_EXCLSV');
5732 --
5733 chk_nip_pln_uom -- Added for Bug 2447647
5734 (p_pl_typ_id => p_rec.pl_typ_id,
5735 p_pl_cd => p_rec.pl_cd,
5736 p_nip_pl_uom => p_rec.nip_pl_uom,
5737 p_effective_date => p_effective_date,
5738 p_business_group_id => p_rec.business_group_id);
5739 --
5740 /* Bug : 3460429
5741 chk_pl_group_id(p_pl_id => p_rec.pl_id,
5742 p_group_pl_id => p_rec.group_pl_id,
5743 p_pl_typ_id => p_rec.pl_typ_id,
5744 p_effective_date => p_effective_date
5745 ) ;
5746 */
5747 --
5748 hr_utility.set_location(' Leaving:'||l_proc, 10);
5749 End insert_validate;
5750 --
5751 -- ----------------------------------------------------------------------------
5752 -- |---------------------------< update_validate >----------------------------|
5753 -- ----------------------------------------------------------------------------
5754 Procedure update_validate
5755 (p_rec in ben_pln_shd.g_rec_type,
5756 p_effective_date in date,
5757 p_datetrack_mode in varchar2,
5758 p_validation_start_date in date,
5759 p_validation_end_date in date) is
5760 --
5761 l_proc varchar2(72) := g_package||'update_validate';
5762 --
5763 Begin
5767 --
5764 hr_utility.set_location('Entering:'||l_proc, 5);
5765 --
5766 -- Call all supporting business operations
5768 if p_rec.business_group_id is not null and p_rec.legislation_code is null then
5769 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
5770 end if;
5771 --
5772 chk_pl_id
5773 (p_pl_id => p_rec.pl_id,
5774 p_effective_date => p_effective_date,
5775 p_object_version_number => p_rec.object_version_number);
5776 --
5777 chk_plan_name_unique
5778 (p_pl_id => p_rec.pl_id,
5779 p_name => p_rec.name,
5780 p_effective_date => p_effective_date,
5781 p_business_group_id => p_rec.business_group_id,
5782 p_object_version_number => p_rec.object_version_number,
5783 p_validation_start_date => p_validation_start_date,
5784 p_validation_end_date => p_validation_end_date);
5785 --
5786 chk_auto_enrt_and_mthd
5787 (p_pl_id => p_rec.pl_id,
5788 p_effective_date => p_effective_date,
5789 p_enrt_mthd_cd => p_rec.enrt_mthd_cd,
5790 p_business_group_id => p_rec.business_group_id);
5791 --
5792 --Bug 5710248
5793 chk_pl_typ_change
5794 (p_pl_id => p_rec.pl_id,
5795 p_business_group_id => p_rec.business_group_id,
5796 p_pl_typ_id => p_rec.pl_typ_id,
5797 p_effective_date => p_effective_date,
5798 p_object_version_number => p_rec.object_version_number);
5799 --Bug 5710248
5800 --Bug : 3475996
5801 chk_cwb_plan_type_uniq_plan(p_pl_id => p_rec.pl_id,
5802 p_name => p_rec.name,
5803 p_pl_typ_id => p_rec.pl_typ_id,
5804 p_group_pl_id => p_rec.group_pl_id,
5805 p_effective_date => p_effective_date,
5806 p_business_group_id => p_rec.business_group_id,
5807 p_object_version_number => p_rec.object_version_number,
5808 p_validation_start_date => p_validation_start_date,
5809 p_validation_end_date => p_validation_end_date);
5810 --
5811 --iRec
5812 chk_irec_pln_in_rptg_grp
5813 (p_pl_id => p_rec.pl_id,
5814 p_pl_typ_id => p_rec.pl_typ_id,
5815 p_effective_date => p_effective_date,
5816 p_validation_start_date => p_validation_start_date,
5817 p_validation_end_date => p_validation_end_date,
5818 p_business_group_id => p_rec.business_group_id,
5819 p_object_version_number => p_rec.object_version_number);
5820 --iRec
5821 --
5822 chk_measures_allowed
5823 (p_bnf_pct_amt_alwd_cd => p_rec.bnf_pct_amt_alwd_cd,
5824 p_bnf_incrmt_amt => p_rec.bnf_incrmt_amt,
5825 p_bnf_pct_incrmt_val => p_rec.bnf_pct_incrmt_val,
5826 p_bnf_mn_dsgntbl_amt => p_rec.bnf_mn_dsgntbl_amt);
5827 --
5828 chk_code_rule_dpnd
5829 (p_code => p_rec.mx_wtg_dt_to_use_cd,
5830 p_rule => p_rec.mx_wtg_dt_to_use_rl);
5831 --
5832 chk_mn_val_mn_flag_mn_rule
5833 (p_mn_cvg_rqd_amt => p_rec.mn_cvg_rqd_amt,
5834 p_no_mn_cvg_amt_apls_flag => p_rec.no_mn_cvg_amt_apls_flag,
5835 p_mn_cvg_rl => p_rec.mn_cvg_rl);
5836 --
5837 chk_mx_val_mx_flag_mx_rule
5838 (p_mx_cvg_alwd_amt => p_rec.mx_cvg_alwd_amt,
5839 p_no_mx_cvg_amt_apls_flag => p_rec.no_mx_cvg_amt_apls_flag,
5840 p_mx_cvg_rl => p_rec.mx_cvg_rl);
5841 --
5842 chk_vrfy_fmly_mmbr_cd
5843 (p_pl_id => p_rec.pl_id,
5844 p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd,
5845 p_effective_date => p_effective_date,
5846 p_object_version_number => p_rec.object_version_number);
5847 --
5848 chk_use_csd_rsd_prccng_cd
5849 (p_pl_id => p_rec.pl_id,
5850 p_use_csd_rsd_prccng_cd => p_rec.use_csd_rsd_prccng_cd,
5851 p_effective_date => p_effective_date,
5852 p_pl_cd => p_rec.pl_cd,
5853 p_object_version_number => p_rec.object_version_number);
5854 --
5855 chk_vrfy_fmly_mmbr_rl
5856 (p_pl_id => p_rec.pl_id,
5857 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl,
5858 p_business_group_id => p_rec.business_group_id,
5859 p_effective_date => p_effective_date,
5860 p_object_version_number => p_rec.object_version_number);
5861 --
5862
5863 chk_dpnt_dsgn_cd
5864 (p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
5865 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
5866 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd);
5867 --
5868 chk_dpnt_dsgn_cd_detail
5869 (p_pl_id => p_rec.pl_id,
5870 p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
5871 p_bnf_dsgn_cd => p_rec.bnf_dsgn_cd,
5872 p_business_group_id => p_rec.business_group_id,
5873 p_effective_date => p_effective_date,
5874 p_validation_start_date => p_validation_start_date,
5875 p_validation_end_date => p_validation_end_date,
5876 p_object_version_number => p_rec.object_version_number);
5877 --
5878 chk_mutual_exclusive_flags
5879 (p_pl_id => p_rec.pl_id,
5880 p_invk_dcln_prtn_pl_flag => p_rec.invk_dcln_prtn_pl_flag,
5881 p_invk_flx_cr_pl_flag => p_rec.invk_flx_cr_pl_flag,
5885 --
5882 p_svgs_pl_flag => p_rec.svgs_pl_flag,
5883 p_effective_date => p_effective_date,
5884 p_object_version_number => p_rec.object_version_number);
5886 chk_mutual_exlsv_rule_num_uom
5887 (p_pl_id => p_rec.pl_id,
5888 p_mx_wtg_perd_rl => p_rec.mx_wtg_perd_rl,
5889 p_mx_wtg_perd_prte_val => p_rec.mx_wtg_perd_prte_val,
5890 p_mx_wtg_perd_prte_uom => p_rec.mx_wtg_perd_prte_uom,
5891 p_effective_date => p_effective_date,
5892 p_object_version_number => p_rec.object_version_number);
5893 --
5894 chk_all_flags
5895 (p_pl_id => p_rec.pl_id,
5896 p_alws_qdro_flag => p_rec.alws_qdro_flag,
5897 p_alws_qmcso_flag => p_rec.alws_qmcso_flag,
5898 p_bnf_addl_instn_txt_alwd_flag => p_rec.bnf_addl_instn_txt_alwd_flag,
5899 p_bnf_adrs_rqd_flag => p_rec.bnf_adrs_rqd_flag,
5900 p_bnf_cntngt_bnfs_alwd_flag => p_rec.bnf_cntngt_bnfs_alwd_flag,
5901 p_bnf_ctfn_rqd_flag => p_rec.bnf_ctfn_rqd_flag,
5902 p_bnf_dob_rqd_flag => p_rec.bnf_dob_rqd_flag,
5903 p_bnf_dsge_mnr_ttee_rqd_flag => p_rec.bnf_dsge_mnr_ttee_rqd_flag,
5904 p_bnf_legv_id_rqd_flag => p_rec.bnf_legv_id_rqd_flag,
5905 p_bnf_may_dsgt_org_flag => p_rec.bnf_may_dsgt_org_flag,
5906 p_bnf_qdro_rl_apls_flag => p_rec.bnf_qdro_rl_apls_flag,
5907 p_drvbl_fctr_apls_rts_flag => p_rec.drvbl_fctr_apls_rts_flag,
5908 p_drvbl_fctr_prtn_elig_flag => p_rec.drvbl_fctr_prtn_elig_flag,
5909 p_elig_apls_flag => p_rec.elig_apls_flag,
5910 p_invk_dcln_prtn_pl_flag => p_rec.invk_dcln_prtn_pl_flag,
5911 p_invk_flx_cr_pl_flag => p_rec.invk_flx_cr_pl_flag,
5912 p_drvbl_dpnt_elig_flag => p_rec.drvbl_dpnt_elig_flag,
5913 p_trk_inelig_per_flag => p_rec.trk_inelig_per_flag,
5914 p_dpnt_cvd_by_othr_apls_flag => p_rec.dpnt_cvd_by_othr_apls_flag,
5915 p_frfs_aply_flag => p_rec.frfs_aply_flag,
5916 p_hc_pl_subj_hcfa_aprvl_flag => p_rec.hc_pl_subj_hcfa_aprvl_flag,
5917 p_hghly_cmpd_rl_apls_flag => p_rec.hghly_cmpd_rl_apls_flag,
5918 p_no_mn_cvg_amt_apls_flag => p_rec.no_mn_cvg_amt_apls_flag,
5919 p_no_mn_cvg_incr_apls_flag => p_rec.no_mn_cvg_incr_apls_flag,
5920 p_no_mn_opts_num_apls_flag => p_rec.no_mn_opts_num_apls_flag,
5921 p_no_mx_cvg_amt_apls_flag => p_rec.no_mx_cvg_amt_apls_flag,
5922 p_no_mx_cvg_incr_apls_flag => p_rec.no_mx_cvg_incr_apls_flag,
5923 p_no_mx_opts_num_apls_flag => p_rec.no_mx_opts_num_apls_flag,
5924 p_prtn_elig_ovrid_alwd_flag => p_rec.prtn_elig_ovrid_alwd_flag,
5925 p_subj_to_imptd_incm_typ_cd => p_rec.subj_to_imptd_incm_typ_cd,
5926 p_use_all_asnts_elig_flag => p_rec.use_all_asnts_elig_flag,
5927 p_use_all_asnts_for_rt_flag => p_rec.use_all_asnts_for_rt_flag,
5928 p_vstg_apls_flag => p_rec.vstg_apls_flag,
5929 p_wvbl_flag => p_rec.wvbl_flag,
5930 p_alws_reimbmts_flag => p_rec.alws_reimbmts_flag,
5931 p_dpnt_adrs_rqd_flag => p_rec.dpnt_adrs_rqd_flag,
5932 p_dpnt_dob_rqd_flag => p_rec.dpnt_dob_rqd_flag,
5933 p_dpnt_leg_id_rqd_flag => p_rec.dpnt_leg_id_rqd_flag,
5934 p_dpnt_no_ctfn_rqd_flag => p_rec.dpnt_no_ctfn_rqd_flag,
5935 p_svgs_pl_flag => p_rec.svgs_pl_flag,
5936 p_enrt_pl_opt_flag => p_rec.enrt_pl_opt_flag,
5937 p_MAY_ENRL_PL_N_OIPL_FLAG => p_rec.MAY_ENRL_PL_N_OIPL_FLAG,
5938 p_ALWS_UNRSTRCTD_ENRT_FLAG => p_rec.ALWS_UNRSTRCTD_ENRT_FLAG,
5939 p_ALWS_TMPRY_ID_CRD_FLAG => p_rec.ALWS_TMPRY_ID_CRD_FLAG,
5940 p_nip_dflt_flag => p_rec.nip_dflt_flag,
5941 p_post_to_gl_flag => p_rec.post_to_gl_flag,
5942 p_effective_date => p_effective_date,
5943 p_object_version_number => p_rec.object_version_number);
5944 --
5945 chk_all_lookups
5946 (p_pl_id => p_rec.pl_id,
5947 p_bnf_dflt_bnf_cd => p_rec.bnf_dflt_bnf_cd,
5948 p_bnf_pct_amt_alwd_cd => p_rec.bnf_pct_amt_alwd_cd,
5949 p_pl_cd => p_rec.pl_cd,
5950 p_cmpr_clms_to_cvg_or_bal_cd => p_rec.cmpr_clms_to_cvg_or_bal_cd,
5951 p_enrt_mthd_cd => p_rec.enrt_mthd_cd,
5952 p_enrt_cd => p_rec.enrt_cd,
5953 p_mx_wtg_perd_prte_uom => p_rec.mx_wtg_perd_prte_uom,
5954 p_nip_dflt_enrt_cd => p_rec.nip_dflt_enrt_cd,
5955 p_nip_pl_uom => p_rec.nip_pl_uom,
5956 p_rqd_perd_enrt_nenrt_uom => p_rec.rqd_perd_enrt_nenrt_uom,
5957 p_nip_acty_ref_perd_cd => p_rec.nip_acty_ref_perd_cd,
5958 p_nip_enrt_info_rt_freq_cd => p_rec.nip_enrt_info_rt_freq_cd,
5959 p_prort_prtl_yr_cvg_rstrn_cd => p_rec.prort_prtl_yr_cvg_rstrn_cd,
5960 p_hc_svc_typ_cd => p_rec.hc_svc_typ_cd,
5961 p_pl_stat_cd => p_rec.pl_stat_cd,
5962 p_prmry_fndg_mthd_cd => p_rec.prmry_fndg_mthd_cd,
5963 p_bnf_dsgn_cd => p_rec.bnf_dsgn_cd,
5964 p_mx_wtg_dt_to_use_cd => p_rec.mx_wtg_dt_to_use_cd,
5965 p_dflt_to_asn_pndg_ctfn_cd => p_rec.dflt_to_asn_pndg_ctfn_cd,
5966 p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
5967 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
5968 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
5969 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
5970 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
5971 p_per_cvrd_cd => p_rec.per_cvrd_cd,
5972 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
5973 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
5974 p_BNFT_OR_OPTION_RSTRCTN_CD => p_rec.BNFT_OR_OPTION_RSTRCTN_CD,
5975 p_CVG_INCR_R_DECR_ONLY_CD => p_rec.CVG_INCR_R_DECR_ONLY_CD,
5976 p_unsspnd_enrt_cd => p_rec.unsspnd_enrt_cd,
5977 p_imptd_incm_calc_cd => p_rec.imptd_incm_calc_cd,
5978 p_effective_date => p_effective_date,
5979 p_object_version_number => p_rec.object_version_number);
5980 --
5981 chk_all_rules
5982 (p_pl_id => p_rec.pl_id,
5983 p_business_group_id => p_rec.business_group_id,
5984 p_auto_enrt_mthd_rl => p_rec.auto_enrt_mthd_rl,
5985 p_mn_cvg_rl => p_rec.mn_cvg_rl,
5986 p_mx_cvg_rl => p_rec.mx_cvg_rl,
5987 p_mx_wtg_perd_rl => p_rec.mx_wtg_perd_rl,
5991 p_prort_prtl_yr_cvg_rstrn_rl => p_rec.prort_prtl_yr_cvg_rstrn_rl,
5988 p_nip_dflt_enrt_det_rl => p_rec.nip_dflt_enrt_det_rl,
5989 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl ,
5990 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
5992 p_mx_wtg_dt_to_use_rl => p_rec.mx_wtg_dt_to_use_rl,
5993 p_dflt_to_asn_pndg_ctfn_rl => p_rec.dflt_to_asn_pndg_ctfn_rl,
5994 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
5995 p_postelcn_edit_rl => p_rec.postelcn_edit_rl,
5996 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
5997 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
5998 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
5999 p_ENRT_RL => p_rec.ENRT_RL,
6000 p_rqd_perd_enrt_nenrt_rl => p_rec.rqd_perd_enrt_nENRT_RL,
6001 p_effective_date => p_effective_date,
6002 p_object_version_number => p_rec.object_version_number);
6003 --
6004 --
6005 chk_cd_rl_combination
6006 (
6007 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
6008 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl,
6009 p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd,
6010 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl,
6011 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
6012 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
6013 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
6014 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
6015 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
6016 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
6017 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
6018 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
6019 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
6020 p_rt_end_dt_rl => p_rec.rt_end_dt_rl
6021 );
6022
6023 --
6024 chk_all_no_amount_flags
6025 (p_no_mn_cvg_amt_apls_flag => p_rec.no_mn_cvg_amt_apls_flag,
6026 p_mn_cvg_rqd_amt => p_rec.mn_cvg_rqd_amt,
6027 p_no_mx_cvg_amt_apls_flag => p_rec.no_mx_cvg_amt_apls_flag,
6028 p_mx_cvg_alwd_amt => p_rec.mx_cvg_alwd_amt);
6029 --
6030 chk_mn_val_mx_val
6031 (p_mn_val => p_rec.mn_cvg_rqd_amt,
6032 p_mx_val => p_rec.mx_cvg_alwd_amt);
6033 --
6034 chk_plan_oipl_mutexcl
6035 (p_pl_id => p_rec.pl_id,
6036 p_actl_prem_id => p_rec.actl_prem_id,
6037 p_effective_date => p_effective_date,
6038 p_business_group_id => p_rec.business_group_id,
6039 p_object_version_number => p_rec.object_version_number);
6040 --
6041 -- Call the datetrack update integrity operation
6042 --
6043 dt_update_validate
6044 (p_auto_enrt_mthd_rl => p_rec.auto_enrt_mthd_rl,
6045 p_mn_cvg_rl => p_rec.mn_cvg_rl,
6046 p_mx_cvg_rl => p_rec.mx_cvg_rl,
6047 p_mx_wtg_perd_rl => p_rec.mx_wtg_perd_rl,
6048 p_nip_dflt_enrt_det_rl => p_rec.nip_dflt_enrt_det_rl,
6049 p_prort_prtl_yr_cvg_rstrn_rl => p_rec.prort_prtl_yr_cvg_rstrn_rl,
6050 p_pl_typ_id => p_rec.pl_typ_id,
6051 p_actl_prem_id => p_rec.actl_prem_id,
6052 p_mx_wtg_dt_to_use_rl => p_rec.mx_wtg_dt_to_use_rl,
6053 p_dflt_to_asn_pndg_ctfn_rl => p_rec.dflt_to_asn_pndg_ctfn_rl,
6054 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl,
6055 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
6056 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
6057 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
6058 p_postelcn_edit_rl => p_rec.postelcn_edit_rl,
6059 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
6060 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
6061 p_ENRT_RL => p_rec.enrt_rl,
6062 p_rqd_perd_enrt_nenrt_rl => p_rec.rqd_perd_enrt_nenrt_rl,
6063 p_datetrack_mode => p_datetrack_mode,
6064 p_validation_start_date => p_validation_start_date,
6065 p_validation_end_date => p_validation_end_date);
6066 --
6067 chk_flag_and_val
6068 (p_flag => p_rec.no_mn_opts_num_apls_flag,
6069 p_val => p_rec.mn_opts_rqd_num,
6070 p_msg => 'BEN_91695_MIN_VAL_FLAG_EXCLSV');
6071 --
6072 chk_flag_and_val
6073 (p_flag => p_rec.no_mx_opts_num_apls_flag,
6074 p_val => p_rec.mx_opts_alwd_num,
6075 p_msg => 'BEN_91696_MAX_VAL_FLAG_EXCLSV');
6076 --
6077 chk_nip_pln_uom -- Added for Bug 2447647
6078 (p_pl_typ_id => p_rec.pl_typ_id,
6079 p_pl_cd => p_rec.pl_cd,
6080 p_nip_pl_uom => p_rec.nip_pl_uom,
6081 p_effective_date => p_effective_date,
6082 p_business_group_id => p_rec.business_group_id);
6083 --
6084 /*
6085 --Bug 3460429
6086 chk_pl_group_id(p_pl_id => p_rec.pl_id,
6087 p_group_pl_id => p_rec.group_pl_id,
6088 p_pl_typ_id => p_rec.pl_typ_id,
6089 p_effective_date => p_effective_date
6090 ) ;
6091 --Bug 3460429
6092 */
6093 --
6094 -- Bug No: 3876692
6095 --
6096 chk_prfl_rule_extnce
6097 (p_pl_id => p_rec.pl_id,
6098 p_imptd_incm_calc_cd => p_rec.imptd_incm_calc_cd,
6099 p_invk_flx_cr_pl_flag => p_rec.invk_flx_cr_pl_flag,
6100 p_effective_date => p_effective_date,
6101 p_object_version_number => p_rec.object_version_number,
6102 p_validation_start_date => p_validation_start_date,
6103 p_validation_end_date => p_validation_end_date);
6104 --
6105 chk_compare_claims_cd (p_pl_id => p_rec.pl_id,
6106 p_effective_date => p_effective_date,
6107 p_business_group_id => p_rec.business_group_id,
6108 p_cmpr_clms_to_cvg_or_bal_cd => p_rec.cmpr_clms_to_cvg_or_bal_cd,
6109 p_object_version_number => p_rec.object_version_number);
6110
6111 --
6112 hr_utility.set_location(' Leaving:'||l_proc, 10);
6113 End update_validate;
6114 --
6115 -- ----------------------------------------------------------------------------
6116 -- |---------------------------< delete_validate >----------------------------|
6117 -- ----------------------------------------------------------------------------
6118 Procedure delete_validate
6119 (p_rec in ben_pln_shd.g_rec_type,
6120 p_effective_date in date,
6121 p_datetrack_mode in varchar2,
6122 p_validation_start_date in date,
6123 p_validation_end_date in date) is
6124 --
6125 l_proc varchar2(72) := g_package||'delete_validate';
6126 --
6127 -- Bug 4057566
6128 --
6129 CURSOR c_plan_name
6130 IS
6131 SELECT pln.NAME
6132 FROM ben_pl_f pln
6133 WHERE pln.pl_id = p_rec.pl_id
6134 AND p_validation_start_date BETWEEN pln.effective_start_date
6135 AND pln.effective_end_date;
6136 --
6137 l_plan_name varchar2(240);
6138 --
6139 --
6140 Begin
6141 hr_utility.set_location('Entering:'||l_proc, 5);
6142 --
6143 -- Bug 4057566
6144 --
6145 open c_plan_name;
6146 --
6147 fetch c_plan_name into l_plan_name;
6148 --
6149 close c_plan_name;
6150 --
6151 -- Call all supporting business operations
6152 --
6153
6154 chk_pl_group_child(p_pl_id => p_rec.pl_id ,
6155 p_opt_typ_cd => null ,
6156 p_effective_date => p_effective_date,
6157 p_name => l_plan_name) ;
6158
6159 dt_delete_validate
6160 (p_datetrack_mode => p_datetrack_mode,
6161 p_validation_start_date => p_validation_start_date,
6162 p_validation_end_date => p_validation_end_date,
6163 p_pl_id => p_rec.pl_id,
6164 p_name => l_plan_name );
6165 --
6166 hr_utility.set_location(' Leaving:'||l_proc, 10);
6167 End delete_validate;
6168 --
6169
6170
6171 -- ---------------------------------------------------------------------------
6172 -- |---------------------< return_legislation_code >-------------------------|
6173 -- ---------------------------------------------------------------------------
6174 --
6175 function return_legislation_code
6176 (p_pl_id in number) return varchar2 is
6177 --
6178 -- Declare cursor
6179 --
6180 cursor csr_leg_code is
6181 select a.legislation_code
6182 from per_business_groups a,
6183 ben_pl_f b
6184 where b.pl_id = p_pl_id
6185 and a.business_group_id = b.business_group_id;
6186 --
6187 -- Declare local variables
6188 --
6189 l_legislation_code varchar2(150);
6190 l_proc varchar2(72) := g_package||'return_legislation_code';
6191
6192 --
6193 begin
6194 --
6195 hr_utility.set_location('Entering:'|| l_proc, 10);
6196 --
6197 -- Ensure that all the mandatory parameter are not null
6198 --
6199 hr_api.mandatory_arg_error(p_api_name => l_proc,
6200 p_argument => 'pl_id',
6201 p_argument_value => p_pl_id);
6202 --
6203 open csr_leg_code;
6204 --
6205 fetch csr_leg_code into l_legislation_code;
6206 --
6207 if csr_leg_code%notfound then
6208 --
6209 close csr_leg_code;
6210 --
6211 -- The primary key is invalid therefore we must error
6212 --
6213 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
6214 fnd_message.raise_error;
6215 --
6216 end if;
6217 --
6218 close csr_leg_code;
6219 --
6220 hr_utility.set_location(' Leaving:'|| l_proc, 20);
6221 --
6222 return l_legislation_code;
6223
6224 --
6225 end return_legislation_code;
6226
6227 --
6228 end ben_pln_bus;
[Home] [Help]