[Home] [Help]
PACKAGE BODY: APPS.BEN_APR_BUS
Source
1 Package Body ben_apr_bus as
2 /* $Header: beaprrhi.pkb 120.0 2005/05/28 00:26:53 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_apr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_actl_prem_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 -- actl_prem_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_actl_prem_id(p_actl_prem_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_actl_prem_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_apr_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_actl_prem_id => p_actl_prem_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_actl_prem_id,hr_api.g_number)
55 <> ben_apr_shd.g_old_rec.actl_prem_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_apr_shd.constraint_error('BEN_ACTL_PREM_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_actl_prem_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_apr_shd.constraint_error('BEN_ACTL_PREM_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_actl_prem_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_organization_id >------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure checks that a referenced foreign key actually exists
85 -- in the referenced table.
86 --
87 -- Pre-Conditions
88 -- None.
89 --
90 -- In Parameters
91 -- p_actl_prem_id PK
92 -- p_organization_id ID of FK column
93 -- p_effective_date session date
94 -- p_object_version_number object version number
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Error raised.
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_organization_id (p_actl_prem_id in number,
106 p_organization_id in number,
107 p_pl_id in number,
108 p_oipl_id in number,
109 p_effective_date in date,
110 p_business_group_id in number,
111 p_object_version_number in number) is
112 --
113 l_proc varchar2(72) := g_package||'chk_organization_id';
114 l_api_updating boolean;
115 l_dummy varchar2(1);
116 --
117 cursor c1 is
118 select null
119 from hr_all_organization_units org,
120 ben_popl_org_f pop
121 where org.organization_id = p_organization_id
122 and pop.organization_id = org.organization_id
123 and pop.pl_id = p_pl_id
124 and p_effective_date between
125 nvl(org.date_from, p_effective_date) and
126 nvl(org.date_to, p_effective_date)
127 and p_effective_date between
128 pop.effective_start_date and pop.effective_end_date
129 and pop.business_group_id = p_business_group_id
130 and org.business_group_id = p_business_group_id;
131 --
132 cursor c2 is
133 select null
134 from hr_all_organization_units org,
135 ben_popl_org_f pop,
136 ben_oipl_f cop
137 where org.organization_id = p_organization_id
138 and pop.organization_id = org.organization_id
139 and cop.oipl_id = p_oipl_id
140 and cop.pl_id = pop.pl_id
141 and p_effective_date between
142 nvl(org.date_from, p_effective_date) and
143 nvl(org.date_to, p_effective_date)
144 and p_effective_date between
145 pop.effective_start_date and pop.effective_end_date
146 and p_effective_date between
147 cop.effective_start_date and cop.effective_end_date
148 and pop.business_group_id = p_business_group_id
149 and cop.business_group_id = p_business_group_id
150 and org.business_group_id = p_business_group_id;
151 --
152 Begin
153 --
154 hr_utility.set_location('Entering:'||l_proc,5);
155 --
156 l_api_updating := ben_apr_shd.api_updating
157 (p_actl_prem_id => p_actl_prem_id,
158 p_effective_date => p_effective_date,
159 p_object_version_number => p_object_version_number);
160 --
161 if (l_api_updating
162 and nvl(p_organization_id,hr_api.g_number)
163 <> nvl(ben_apr_shd.g_old_rec.organization_id,hr_api.g_number)
164 or not l_api_updating) and p_organization_id is not null then
165 --
166 -- check if organization_id value exists in hr_all_organization_units
167 -- table
168 --
169 if p_pl_id is not null then
170 --
171 open c1;
172 --
173 fetch c1 into l_dummy;
174 if c1%notfound then
175 --
176 close c1;
177 --
178 -- raise error as FK does not relate to PK in
179 -- hr_all_organization_units table.
180 --
181 fnd_message.set_name('BEN','BEN_92549_NO_ORG_EXIST_PLN');
182 fnd_message.set_token('PL_ID',to_char(p_pl_id));
183 fnd_message.set_token('BG_ID',to_char(p_business_group_id));
184 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
185 fnd_message.raise_error;
186 --
187 end if;
188 --
189 close c1;
190 --
191 elsif p_oipl_id is not null then
192 --
193 open c2;
194 --
195 fetch c2 into l_dummy;
196 if c2%notfound then
197 --
198 close c2;
199 --
200 -- raise error as FK does not relate to PK in
201 -- hr_all_organization_units table.
202 --
203 fnd_message.set_name('BEN','BEN_92550_NO_ORG_EXIST_OIPL');
204 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
205 fnd_message.set_token('BG_ID',to_char(p_business_group_id));
206 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
207 fnd_message.raise_error;
208 --
209 end if;
210 --
211 close c2;
212 --
213 end if;
214 --
215 end if;
216 --
217 hr_utility.set_location('Leaving:'||l_proc,10);
218 --
219 End chk_organization_id;
220
221 --
222 -- ----------------------------------------------------------------------------
223 -- |------< chk_rndg_rl >------|
224 -- ----------------------------------------------------------------------------
225 --
226 -- Description
227 -- This procedure is used to check that the Formula Rule is valid.
228 --
229 -- Pre Conditions
230 -- None.
231 --
232 -- In Parameters
233 -- actl_prem_id PK of record being inserted or updated.
234 -- rndg_rl Value of formula rule id.
235 -- effective_date effective date
236 -- object_version_number Object version number of record being
237 -- inserted or updated.
238 --
239 -- Post Success
240 -- Processing continues
241 --
242 -- Post Failure
243 -- Error handled by procedure
244 --
245 -- Access Status
246 -- Internal table handler use only.
247 --
248 Procedure chk_rndg_rl(p_actl_prem_id in number,
249 p_rndg_rl in number,
250 p_business_group_id in number,
251 p_effective_date in date,
252 p_object_version_number in number) is
253 --
254 l_proc varchar2(72) := g_package||'chk_rndg_rl';
255 l_api_updating boolean;
256 l_dummy varchar2(1);
257 --
258 cursor c1 is
259 select null
260 from ff_formulas_f ff
261 ,per_business_groups pbg
262 where ff.formula_id = p_rndg_rl
263 and ff.formula_type_id = -169
264 and pbg.business_group_id = p_business_group_id
265 and nvl(ff.business_group_id, p_business_group_id) =
266 p_business_group_id
267 and nvl(ff.legislation_code, pbg.legislation_code) =
268 pbg.legislation_code
269 and p_effective_date
270 between ff.effective_start_date
271 and ff.effective_end_date;
272 --
273 Begin
274 --
275 hr_utility.set_location('Entering:'||l_proc, 5);
276 --
277 l_api_updating := ben_apr_shd.api_updating
278 (p_actl_prem_id => p_actl_prem_id,
279 p_effective_date => p_effective_date,
280 p_object_version_number => p_object_version_number);
281 --
282 if (l_api_updating
283 and nvl(p_rndg_rl,hr_api.g_number)
284 <> ben_apr_shd.g_old_rec.rndg_rl
285 or not l_api_updating)
286 and p_rndg_rl is not null then
287 --
288 -- check if value of formula rule is valid.
289 --
290 open c1;
291 --
292 -- fetch value from cursor if it returns a record then the
293 -- formula is valid otherwise its invalid
294 --
295 fetch c1 into l_dummy;
296 if c1%notfound then
297 --
298 close c1;
299 --
300 -- raise error
301 --
302 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
303 fnd_message.set_token('ID',p_rndg_rl);
304 fnd_message.set_token('TYPE_ID',-169);
305 fnd_message.raise_error;
306 --
307 end if;
308 --
309 close c1;
310 --
311 end if;
312 --
313 hr_utility.set_location('Leaving:'||l_proc,10);
314 --
315 end chk_rndg_rl;
316 --
317 -- ----------------------------------------------------------------------------
318 -- |------< chk_val_calc_rl >------|
319 -- ----------------------------------------------------------------------------
320 --
321 -- Description
322 -- This procedure is used to check that the Formula Rule is valid.
323 --
324 -- Pre Conditions
325 -- None.
326 --
327 -- In Parameters
328 -- actl_prem_id PK of record being inserted or updated.
329 -- val_calc_rl Value of formula rule id.
330 -- effective_date effective date
331 -- object_version_number Object version number of record being
332 -- inserted or updated.
333 --
334 -- Post Success
335 -- Processing continues
336 --
337 -- Post Failure
338 -- Error handled by procedure
339 --
340 -- Access Status
341 -- Internal table handler use only.
342 --
343 Procedure chk_val_calc_rl(p_actl_prem_id in number,
344 p_val_calc_rl in number,
345 p_business_group_id in number,
346 p_effective_date in date,
347 p_object_version_number in number) is
348 --
349 l_proc varchar2(72) := g_package||'chk_val_calc_rl';
350 l_api_updating boolean;
351 l_dummy varchar2(1);
352 --
353 cursor c1 is
354 select null
355 from ff_formulas_f ff
356 ,per_business_groups pbg
357 where ff.formula_id = p_val_calc_rl
358 and ff.formula_type_id = -507
359 and pbg.business_group_id = p_business_group_id
360 and nvl(ff.business_group_id, p_business_group_id) =
361 p_business_group_id
362 and nvl(ff.legislation_code, pbg.legislation_code) =
363 pbg.legislation_code
364 and p_effective_date
365 between ff.effective_start_date
366 and ff.effective_end_date;
367 --
368 Begin
369 --
370 hr_utility.set_location('Entering:'||l_proc, 5);
371 --
372 l_api_updating := ben_apr_shd.api_updating
373 (p_actl_prem_id => p_actl_prem_id,
374 p_effective_date => p_effective_date,
375 p_object_version_number => p_object_version_number);
376 --
377 if (l_api_updating
378 and nvl(p_val_calc_rl,hr_api.g_number)
379 <> ben_apr_shd.g_old_rec.val_calc_rl
380 or not l_api_updating)
381 and p_val_calc_rl is not null then
382 --
383 -- check if value of formula rule is valid.
384 --
385 open c1;
386 --
387 -- fetch value from cursor if it returns a record then the
388 -- formula is valid otherwise its invalid
389 --
390 fetch c1 into l_dummy;
391 if c1%notfound then
392 --
393 close c1;
394 --
395 -- raise error
396 --
397 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
398 fnd_message.set_token('ID',p_val_calc_rl);
399 fnd_message.set_token('TYPE_ID',-507);
400 fnd_message.raise_error;
401 --
402 end if;
403 --
404 close c1;
405 --
406 end if;
407 --
408 hr_utility.set_location('Leaving:'||l_proc,10);
409 --
410 end chk_val_calc_rl;
411 --
412 -- ----------------------------------------------------------------------------
413 -- |------< chk_vrbl_rt_add_on_calc_rl >------|
414 -- ----------------------------------------------------------------------------
415 --
416 -- Description
417 -- This procedure is used to check that the Formula Rule is valid.
418 --
419 -- Pre Conditions
420 -- None.
421 --
422 -- In Parameters
423 -- actl_prem_id PK of record being inserted or updated.
424 -- vrbl_rt_add_on_calc_rl Value of formula rule id.
425 -- effective_date effective date
426 -- object_version_number Object version number of record being
427 -- inserted or updated.
428 --
429 -- Post Success
430 -- Processing continues
431 --
432 -- Post Failure
433 -- Error handled by procedure
434 --
435 -- Access Status
436 -- Internal table handler use only.
437 --
438 Procedure chk_vrbl_rt_add_on_calc_rl(p_actl_prem_id in number,
439 p_vrbl_rt_add_on_calc_rl in number,
440 p_business_group_id in number,
444 l_proc varchar2(72) := g_package||'chk_vrbl_rt_add_on_calc_rl';
441 p_effective_date in date,
442 p_object_version_number in number) is
443 --
445 l_api_updating boolean;
446 l_dummy varchar2(1);
447 --
448 cursor c1 is
449 select null
450 from ff_formulas_f ff
451 ,per_business_groups pbg
452 where ff.formula_id = p_vrbl_rt_add_on_calc_rl
453 and ff.formula_type_id = -529
454 and pbg.business_group_id = p_business_group_id
455 and nvl(ff.business_group_id, p_business_group_id) =
456 p_business_group_id
457 and nvl(ff.legislation_code, pbg.legislation_code) =
458 pbg.legislation_code
459 and p_effective_date
460 between ff.effective_start_date
461 and ff.effective_end_date;
462 --
463 Begin
464 --
465 hr_utility.set_location('Entering:'||l_proc, 5);
466 --
467 l_api_updating := ben_apr_shd.api_updating
468 (p_actl_prem_id => p_actl_prem_id,
469 p_effective_date => p_effective_date,
470 p_object_version_number => p_object_version_number);
471 --
472 if (l_api_updating
473 and nvl(p_vrbl_rt_add_on_calc_rl,hr_api.g_number)
474 <> ben_apr_shd.g_old_rec.vrbl_rt_add_on_calc_rl
475 or not l_api_updating)
476 and p_vrbl_rt_add_on_calc_rl is not null then
477 --
478 -- check if value of formula rule is valid.
479 --
480 open c1;
481 --
482 -- fetch value from cursor if it returns a record then the
483 -- formula is valid otherwise its invalid
484 --
485 fetch c1 into l_dummy;
486 if c1%notfound then
487 --
488 close c1;
489 --
490 -- raise error
491 --
492 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
493 fnd_message.set_token('ID',p_vrbl_rt_add_on_calc_rl);
494 fnd_message.set_token('TYPE_ID',-529);
495 fnd_message.raise_error;
496 --
497 end if;
498 --
499 close c1;
500 --
501 end if;
502 --
503 hr_utility.set_location('Leaving:'||l_proc,10);
504 --
505 end chk_vrbl_rt_add_on_calc_rl;
506 --
507 -- ----------------------------------------------------------------------------
508 -- |------< chk_rndg_cd >------|
509 -- ----------------------------------------------------------------------------
510 --
511 -- Description
512 -- This procedure is used to check that the lookup value is valid.
513 --
514 -- Pre Conditions
515 -- None.
516 --
517 -- In Parameters
518 -- actl_prem_id PK of record being inserted or updated.
519 -- rndg_cd Value of lookup code.
520 -- effective_date effective date
521 -- object_version_number Object version number of record being
522 -- inserted or updated.
523 --
524 -- Post Success
525 -- Processing continues
526 --
527 -- Post Failure
528 -- Error handled by procedure
529 --
530 -- Access Status
531 -- Internal table handler use only.
532 --
533 Procedure chk_rndg_cd(p_actl_prem_id in number,
534 p_rndg_cd in varchar2,
535 p_effective_date in date,
536 p_object_version_number in number) is
537 --
538 l_proc varchar2(72) := g_package||'chk_rndg_cd';
539 l_api_updating boolean;
540 --
541 Begin
542 --
543 hr_utility.set_location('Entering:'||l_proc, 5);
544 --
545 l_api_updating := ben_apr_shd.api_updating
546 (p_actl_prem_id => p_actl_prem_id,
547 p_effective_date => p_effective_date,
548 p_object_version_number => p_object_version_number);
549 --
550 if (l_api_updating
551 and p_rndg_cd
552 <> nvl(ben_apr_shd.g_old_rec.rndg_cd,hr_api.g_varchar2)
553 or not l_api_updating)
554 and p_rndg_cd is not null then
555 --
556 -- check if value of lookup falls within lookup type.
557 --
558 if hr_api.not_exists_in_hr_lookups
559 (p_lookup_type => 'BEN_RNDG',
560 p_lookup_code => p_rndg_cd,
561 p_effective_date => p_effective_date) then
562 --
563 -- raise error as does not exist as lookup
564 --
565 fnd_message.set_name('BEN','BEN_91041_INVALID_RNDG_CD');
566 fnd_message.raise_error;
567 --
568 end if;
569 --
570 end if;
571 --
572 hr_utility.set_location('Leaving:'||l_proc,10);
573 --
574 end chk_rndg_cd;
575 --
576 -- ----------------------------------------------------------------------------
577 -- |------< chk_prdct_cd >------|
578 -- ----------------------------------------------------------------------------
579 --
580 -- Description
581 -- This procedure is used to check that the lookup value is valid.
582 --
583 -- Pre Conditions
584 -- None.
585 --
586 -- In Parameters
587 -- actl_prem_id PK of record being inserted or updated.
588 -- prdct_cd Value of lookup code.
592 --
589 -- effective_date effective date
590 -- object_version_number Object version number of record being
591 -- inserted or updated.
593 -- Post Success
594 -- Processing continues
595 --
596 -- Post Failure
597 -- Error handled by procedure
598 --
599 -- Access Status
600 -- Internal table handler use only.
601 --
602 Procedure chk_prdct_cd(p_actl_prem_id in number,
603 p_prdct_cd in varchar2,
604 p_effective_date in date,
605 p_object_version_number in number) is
606 --
607 l_proc varchar2(72) := g_package||'chk_prdct_cd';
608 l_api_updating boolean;
609 --
610 Begin
611 --
612 hr_utility.set_location('Entering:'||l_proc, 5);
613 --
614 l_api_updating := ben_apr_shd.api_updating
615 (p_actl_prem_id => p_actl_prem_id,
616 p_effective_date => p_effective_date,
617 p_object_version_number => p_object_version_number);
618 --
619 if (l_api_updating
620 and p_prdct_cd
621 <> nvl(ben_apr_shd.g_old_rec.prdct_cd,hr_api.g_varchar2)
622 or not l_api_updating)
623 and p_prdct_cd is not null then
624 --
625 -- check if value of lookup falls within lookup type.
626 --
627 if hr_api.not_exists_in_hr_lookups
628 (p_lookup_type => 'BEN_PRDCT',
629 p_lookup_code => p_prdct_cd,
630 p_effective_date => p_effective_date) then
631 --
632 -- raise error as does not exist as lookup
633 --
634 fnd_message.set_name('BEN','BEN_91606_INVALID_PRODUCT_CD');
635 fnd_message.raise_error;
636 --
637 end if;
638 --
639 end if;
640 --
641 hr_utility.set_location('Leaving:'||l_proc,10);
642 --
643 end chk_prdct_cd;
644 --
645 -- ----------------------------------------------------------------------------
646 -- |------< chk_mlt_cd >------|
647 -- ----------------------------------------------------------------------------
648 --
649 -- Description
650 -- This procedure is used to check that the lookup value is valid.
651 --
652 -- Pre Conditions
653 -- None.
654 --
655 -- In Parameters
656 -- actl_prem_id PK of record being inserted or updated.
657 -- mlt_cd Value of lookup code.
658 -- effective_date effective date
659 -- object_version_number Object version number of record being
660 -- inserted or updated.
661 --
662 -- Post Success
663 -- Processing continues
664 --
665 -- Post Failure
666 -- Error handled by procedure
667 --
668 -- Access Status
669 -- Internal table handler use only.
670 --
671 Procedure chk_mlt_cd(p_actl_prem_id in number,
672 p_mlt_cd in varchar2,
673 p_effective_date in date,
674 p_object_version_number in number) is
675 --
676 l_proc varchar2(72) := g_package||'chk_mlt_cd';
677 l_api_updating boolean;
678 --
679 Begin
680 --
681 hr_utility.set_location('Entering:'||l_proc, 5);
682 --
683 l_api_updating := ben_apr_shd.api_updating
684 (p_actl_prem_id => p_actl_prem_id,
685 p_effective_date => p_effective_date,
686 p_object_version_number => p_object_version_number);
687 --
688 if (l_api_updating
689 and p_mlt_cd
690 <> nvl(ben_apr_shd.g_old_rec.mlt_cd,hr_api.g_varchar2)
691 or not l_api_updating)
692 and p_mlt_cd is not null then
693 --
694 -- check if value of lookup falls within lookup type.
695 --
696 if hr_api.not_exists_in_hr_lookups
697 (p_lookup_type => 'BEN_ACTL_PREM_MLT',
698 p_lookup_code => p_mlt_cd,
699 p_effective_date => p_effective_date) then
700 --
701 -- raise error as does not exist as lookup
702 --
703 fnd_message.set_name('BEN','BEN_91607_INVALID_MLT_CD');
704 fnd_message.raise_error;
705 --
706 end if;
707 --
708 -- if mlt_cd is 'TPLPC' then defer the action.
709 if (p_mlt_cd = 'TPLPC') then
710 fnd_message.set_name('BEN','BEN_92504_PREM_CALC_MTHD_DFRD');
711 fnd_message.raise_error;
712 end if;
713 --
714 end if;
715 --
716 hr_utility.set_location('Leaving:'||l_proc,10);
717 --
718 end chk_mlt_cd;
719 --
720 -- ----------------------------------------------------------------------------
721 -- |------< chk_prtl_mo_det_mthd_cd >------|
722 -- ----------------------------------------------------------------------------
723 --
724 -- Description
725 -- This procedure is used to check that the lookup value is valid.
726 --
727 -- Pre Conditions
728 -- None.
729 --
730 -- In Parameters
731 -- actl_prem_id PK of record being inserted or updated.
732 -- prtl_mo_det_mthd_cd Value of lookup code.
733 -- effective_date effective date
734 -- object_version_number Object version number of record being
735 -- inserted or updated.
739 --
736 --
737 -- Post Success
738 -- Processing continues
740 -- Post Failure
741 -- Error handled by procedure
742 --
743 -- Access Status
744 -- Internal table handler use only.
745 --
746 Procedure chk_prtl_mo_det_mthd_cd(p_actl_prem_id in number,
747 p_prtl_mo_det_mthd_cd in varchar2,
748 p_effective_date in date,
749 p_object_version_number in number) is
750 --
751 l_proc varchar2(72) := g_package||'chk_prtl_mo_det_mthd_cd';
752 l_api_updating boolean;
753 --
754 Begin
755 --
756 hr_utility.set_location('Entering:'||l_proc, 5);
757 --
758 l_api_updating := ben_apr_shd.api_updating
759 (p_actl_prem_id => p_actl_prem_id,
760 p_effective_date => p_effective_date,
761 p_object_version_number => p_object_version_number);
762 --
763 if (l_api_updating
764 and p_prtl_mo_det_mthd_cd
765 <> nvl(ben_apr_shd.g_old_rec.prtl_mo_det_mthd_cd,hr_api.g_varchar2)
766 or not l_api_updating)
767 and p_prtl_mo_det_mthd_cd is not null then
768 --
769 -- check if value of lookup falls within lookup type.
770 --
771 if hr_api.not_exists_in_hr_lookups
772 (p_lookup_type => 'BEN_PRTL_MO_DET_MTHD',
773 p_lookup_code => p_prtl_mo_det_mthd_cd,
774 p_effective_date => p_effective_date) then
775 --
776 -- raise error as does not exist as lookup
777 --
778 fnd_message.set_name('BEN','BEN_92239_INVLD_PRTL_MO_CD');
779 fnd_message.raise_error;
780 --
781 end if;
782 --
783 end if;
784 --
785 hr_utility.set_location('Leaving:'||l_proc,10);
786 --
787 end chk_prtl_mo_det_mthd_cd;
788 --
789 -- ----------------------------------------------------------------------------
790 -- |------< chk_rt_typ_cd >------|
791 -- ----------------------------------------------------------------------------
792 --
793 -- Description
794 -- This procedure is used to check that the lookup value is valid.
795 --
796 -- Pre Conditions
797 -- None.
798 --
799 -- In Parameters
800 -- actl_prem_id PK of record being inserted or updated.
801 -- rt_typ_cd Value of lookup code.
802 -- effective_date effective date
803 -- object_version_number Object version number of record being
804 -- inserted or updated.
805 --
806 -- Post Success
807 -- Processing continues
808 --
809 -- Post Failure
810 -- Error handled by procedure
811 --
812 -- Access Status
813 -- Internal table handler use only.
814 --
815 Procedure chk_rt_typ_cd(p_actl_prem_id in number,
816 p_rt_typ_cd in varchar2,
817 p_effective_date in date,
818 p_object_version_number in number) is
819 --
820 l_proc varchar2(72) := g_package||'chk_rt_typ_cd';
821 l_api_updating boolean;
822 --
823 Begin
824 --
825 hr_utility.set_location('Entering:'||l_proc, 5);
826 --
827 l_api_updating := ben_apr_shd.api_updating
828 (p_actl_prem_id => p_actl_prem_id,
829 p_effective_date => p_effective_date,
830 p_object_version_number => p_object_version_number);
831 --
832 if (l_api_updating
833 and p_rt_typ_cd
834 <> nvl(ben_apr_shd.g_old_rec.rt_typ_cd,hr_api.g_varchar2)
835 or not l_api_updating)
836 and p_rt_typ_cd is not null then
837 --
838 -- check if value of lookup falls within lookup type.
839 --
840 --
841 if hr_api.not_exists_in_hr_lookups
842 (p_lookup_type => 'BEN_RT_TYP',
843 p_lookup_code => p_rt_typ_cd,
844 p_effective_date => p_effective_date) then
845 --
846 -- raise error as does not exist as lookup
847 --
848 fnd_message.set_name('BEN','BEN_91192_RT_TYP_CD');
849 fnd_message.raise_error;
850 --
851 end if;
852 --
853 end if;
854 --
855 hr_utility.set_location('Leaving:'||l_proc,10);
856 --
857 end chk_rt_typ_cd;
858 --
859 -- ----------------------------------------------------------------------------
860 -- |------< chk_bnft_rt_typ_cd >------|
861 -- ----------------------------------------------------------------------------
862 --
863 -- Description
864 -- This procedure is used to check that the lookup value is valid.
865 --
866 -- Pre Conditions
867 -- None.
868 --
869 -- In Parameters
870 -- actl_prem_id PK of record being inserted or updated.
871 -- bnft_rt_typ_cd Value of lookup code.
872 -- effective_date effective date
873 -- object_version_number Object version number of record being
874 -- inserted or updated.
875 --
876 -- Post Success
877 -- Processing continues
878 --
879 -- Post Failure
880 -- Error handled by procedure
881 --
882 -- Access Status
883 -- Internal table handler use only.
884 --
888 p_object_version_number in number) is
885 Procedure chk_bnft_rt_typ_cd(p_actl_prem_id in number,
886 p_bnft_rt_typ_cd in varchar2,
887 p_effective_date in date,
889 --
890 l_proc varchar2(72) := g_package||'chk_bnft_rt_typ_cd';
891 l_api_updating boolean;
892 --
893 Begin
894 --
895 hr_utility.set_location('Entering:'||l_proc, 5);
896 --
897 l_api_updating := ben_apr_shd.api_updating
898 (p_actl_prem_id => p_actl_prem_id,
899 p_effective_date => p_effective_date,
900 p_object_version_number => p_object_version_number);
901 --
902 if (l_api_updating
903 and p_bnft_rt_typ_cd
904 <> nvl(ben_apr_shd.g_old_rec.bnft_rt_typ_cd,hr_api.g_varchar2)
905 or not l_api_updating)
906 and p_bnft_rt_typ_cd is not null then
907 --
908 -- check if value of lookup falls within lookup type.
909 --
910 --
911 if hr_api.not_exists_in_hr_lookups
912 (p_lookup_type => 'BEN_BNFT_RT_TYP',
913 p_lookup_code => p_bnft_rt_typ_cd,
914 p_effective_date => p_effective_date) then
915 --
916 -- raise error as does not exist as lookup
917 --
918 fnd_message.set_name('BEN','BEN_91608_INVALID_BNFT_RT_TYP');
919 fnd_message.raise_error;
920 --
921 end if;
922 --
923 end if;
924 --
925 hr_utility.set_location('Leaving:'||l_proc,10);
926 --
927 end chk_bnft_rt_typ_cd;
928 --
929 -- ----------------------------------------------------------------------------
930 -- |------< chk_acty_ref_perd_cd >------|
931 -- ----------------------------------------------------------------------------
932 --
933 -- Description
934 -- This procedure is used to check that the lookup value is valid.
935 --
936 -- Pre Conditions
937 -- None.
938 --
939 -- In Parameters
940 -- actl_prem_id PK of record being inserted or updated.
941 -- acty_ref_perd_cd Value of lookup code.
942 -- effective_date effective date
943 -- object_version_number Object version number of record being
944 -- inserted or updated.
945 --
946 -- Post Success
947 -- Processing continues
948 --
949 -- Post Failure
950 -- Error handled by procedure
951 --
952 -- Access Status
953 -- Internal table handler use only.
954 --
955 Procedure chk_acty_ref_perd_cd(p_actl_prem_id in number,
956 p_acty_ref_perd_cd in varchar2,
957 p_effective_date in date,
958 p_object_version_number in number) is
959 --
960 l_proc varchar2(72) := g_package||'chk_acty_ref_perd_cd';
961 l_api_updating boolean;
962 --
963 Begin
964 --
965 hr_utility.set_location('Entering:'||l_proc, 5);
966 --
967 l_api_updating := ben_apr_shd.api_updating
968 (p_actl_prem_id => p_actl_prem_id,
969 p_effective_date => p_effective_date,
970 p_object_version_number => p_object_version_number);
971 --
972 if (l_api_updating
973 and p_acty_ref_perd_cd
974 <> nvl(ben_apr_shd.g_old_rec.acty_ref_perd_cd,hr_api.g_varchar2)
975 or not l_api_updating)
976 and p_acty_ref_perd_cd is not null then
977 --
978 -- check if value of lookup falls within lookup type.
979 --
980 --
981 if hr_api.not_exists_in_hr_lookups
982 (p_lookup_type => 'BEN_ACTY_REF_PERD',
983 p_lookup_code => p_acty_ref_perd_cd,
984 p_effective_date => p_effective_date) then
985 --
986 -- raise error as does not exist as lookup
987 --
988 fnd_message.set_name('BEN','BEN_91299_INV_ACTY_REF_PERD_CD');
989 fnd_message.raise_error;
990 --
991 end if;
992 --
993 end if;
994 --
995 hr_utility.set_location('Leaving:'||l_proc,10);
996 --
997 end chk_acty_ref_perd_cd;
998 --
999 --
1000 -- ----------------------------------------------------------------------------
1001 -- |------< chk_name >------|
1002 -- ----------------------------------------------------------------------------
1003 --
1004 -- Description
1005 -- This procedure is used to check that the name field is unique
1006 -- on insert and on update.
1007 --
1008 -- Pre Conditions
1009 -- None.
1010 --
1011 -- In Parameters
1012 -- actl_prem_id PK of record being inserted or updated.
1013 -- name that is beeing inserted ot updated to.
1014 -- effective_date Effective Date of session
1015 -- business group ID
1016 -- object_version_number Object version number of record being
1017 -- inserted or updated.
1018 --
1019 -- Post Success
1020 -- Processing continues
1021 --
1022 -- Post Failure
1023 -- Errors handled by the procedure
1024 --
1025 -- Access Status
1026 -- HR Development Internal use only.
1027 --
1028 Procedure chk_name(p_actl_prem_id in number,
1032 p_validation_end_date in date,
1029 p_name in varchar2,
1030 p_effective_date in date,
1031 p_validation_start_date in date,
1033 p_business_group_id in number,
1034 p_object_version_number in number) is
1035 --
1036 l_proc varchar2(72) := g_package||'chk_name';
1037 l_api_updating boolean;
1038 l_exists varchar2(1);
1039 --
1040 --
1041 cursor csr_name is
1042 select null
1043 from ben_actl_prem_f
1044 where name = p_name
1045 and actl_prem_id <> nvl(p_actl_prem_id, hr_api.g_number)
1046 and business_group_id + 0 = p_business_group_id
1047 and p_validation_start_date <= effective_end_date
1048 and p_validation_end_date >= effective_start_date;
1049 --
1050 --
1051 Begin
1052 --
1053 hr_utility.set_location('Entering:'||l_proc, 5);
1054 --
1055 l_api_updating := ben_apr_shd.api_updating
1056 (p_actl_prem_id => p_actl_prem_id,
1057 p_effective_date => p_effective_date,
1058 p_object_version_number => p_object_version_number);
1059 --
1060 if (l_api_updating
1061 and p_name <> ben_apr_shd.g_old_rec.name) or
1062 not l_api_updating then
1063 --
1064 hr_utility.set_location('name is :'||p_name, 10);
1065 --
1066 -- check if this name already exist
1067 --
1068 open csr_name;
1069 fetch csr_name into l_exists;
1070 if csr_name%found then
1071 close csr_name;
1072 --
1073 -- raise error as UK1 is violated
1074 --
1075 hr_utility.set_location('before message'||p_name,10);
1076 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
1077 fnd_message.raise_error;
1078 --
1079 end if;
1080 --
1081 end if;
1082 --
1083 hr_utility.set_location('Leaving:'||l_proc, 20);
1084 --
1085 End chk_name;
1086 --
1087 -- ----------------------------------------------------------------------------
1088 -- |------< chk_mlt_cd_rt_typ_cd >------|
1089 -- ----------------------------------------------------------------------------
1090 --
1091 -- Description
1092 -- This procedure is used to check that the mlt_cd and rt_typ_cd
1093 -- items are conditionally dependent. The VALUE of mlt_cd can only
1094 -- be CVG for Coverage or null and if rt_typ_cd = 'FLAT' then
1095 -- mlt_cd must be null.
1096 --
1097 -- Pre Conditions
1098 -- None.
1099 --
1100 -- In Parameters
1101 -- actl_prem_id PK of record being inserted or updated.
1102 -- mlt_cd.
1103 -- rt_typ_cd.
1104 -- effective_date effective date
1105 -- object_version_number Object version number of record being
1106 -- inserted or updated.
1107 --
1108 -- Post Success
1109 -- Processing continues
1110 --
1111 -- Post Failure
1112 -- Error handled by procedure
1113 --
1114 -- Access Status
1115 -- Internal table handler use only.
1116 --
1117 --Procedure chk_mlt_cd_rt_typ_cd(p_actl_prem_id in number,
1118 -- p_mlt_cd in varchar2,
1119 -- p_rt_typ_cd in varchar2,
1120 -- p_effective_date in date,
1121 -- p_object_version_number in number) is
1122 --
1123 -- l_proc varchar2(72) := g_package||'chk_mlt_cd_rt_typ_cd';
1124 -- l_api_updating boolean;
1125 --
1126 --Begin
1127 --
1128 -- hr_utility.set_location('Entering:'||l_proc, 5);
1129 --
1130 -- l_api_updating := ben_apr_shd.api_updating
1131 -- (p_actl_prem_id => p_actl_prem_id,
1132 -- p_effective_date => p_effective_date,
1133 -- p_object_version_number => p_object_version_number);
1134 --
1135 -- Multiple Code must be equal to CVG or null.
1136 -- If P_MLT_CD <> 'CVG' and P_MLT_CD is not null then
1137 -- fnd_message.set_name('BEN','BEN_91602_APR_MLT_CD');
1138 -- fnd_message.raise_error;
1139 -- end if;
1140 -- If rate type code is flat then multiple code must be null.
1141 -- If P_RT_TYP_CD = 'FLAT' and P_MLT_CD is not null then
1142 -- fnd_message.set_name('BEN','BEN_91603_APR_RT_TYP_MLT_CD');
1143 -- fnd_message.raise_error;
1144 -- end if;
1145 --
1146 -- end if;
1147 --
1148 -- hr_utility.set_location('Leaving:'||l_proc,10);
1149 --
1150 --end chk_mlt_cd_rt_typ_cd;
1151
1152 --
1153 -- ----------------------------------------------------------------------------
1154 -- |------< chk_code_rule_num >------|
1155 -- ----------------------------------------------------------------------------
1156 --
1157 -- Description
1158 -- This procedure is used to check that the Rule is only allowed to
1159 -- have a value if the value of the Code = 'Rule', and if code is
1160 -- = RL then p_rule must have a value. If cd = 'WASHRULE' then num
1161 -- must have a value otherwise num must be null.
1162 --
1163 -- Pre Conditions
1164 -- None.
1165 --
1166 -- In Parameters
1167 -- P_CODE value of code item.
1168 -- P_RULE value of rule item
1169 -- P_NUM value of rule item
1173 --
1170 --
1171 -- Post Success
1172 -- Processing continues
1174 -- Post Failure
1175 -- Error handled by procedure
1176 --
1177 -- Access Status
1178 -- Internal table handler use only.
1179 --
1180 Procedure chk_code_rule_num(p_code in varchar2,
1181 p_num in number,
1182 p_rule in number) is
1183 --
1184 l_proc varchar2(72) := g_package||'chk_code_rule_num';
1185 --
1186 Begin
1187 --
1188 hr_utility.set_location('Entering:'||l_proc, 5);
1189 --
1190 if p_code <> 'RL' and p_rule is not null then
1191 --
1192 fnd_message.set_name('BEN','BEN_91624_CD_RL_2');
1193 fnd_message.raise_error;
1194 --
1195 elsif p_code = 'RL' and p_rule is null then
1196 --
1197 fnd_message.set_name('BEN','BEN_91623_CD_RL_1');
1198 fnd_message.raise_error;
1199 --
1200 end if;
1201 --
1202 if p_code <> 'WASHRULE' and p_num is not null then
1203 --
1204 fnd_message.set_name('BEN','BEN_92270_NTWSHRL_NUM_NTNULL');
1205 fnd_message.raise_error;
1206 --
1207 elsif p_code = 'WASHRULE' and p_num is null then
1208 --
1209 fnd_message.set_name('BEN','BEN_92271_NTWSHRL_NUM_NULL');
1210 fnd_message.raise_error;
1211 --
1212 end if;
1213 --
1214 hr_utility.set_location('Leaving:'||l_proc,10);
1215 --
1216 end chk_code_rule_num;
1217 --
1218 -- ----------------------------------------------------------------------------
1219 -- |--------------------< chk_asgnmt_cd_lvl_mlt_pyr >-------------------------|
1220 -- ----------------------------------------------------------------------------
1221 --
1222 -- Description
1223 -- This procedure is used to check the:
1224 -- prem_asnmt_cd = ENRT then prem_asnmt_lvl_cd must be PRTT
1225 -- prem_asnmt_cd = ENRT then mlt_cd can be FLFX,CVG,NSVU,RL
1226 -- prem_asnmt_cd = PROC then prem_asnmt_lvl_cd cannot be PRTT
1227 -- prem_asnmt_cd = PROC then mlt_cd can be NSVU, TPLPC, TTLPRTT or TTLCVG
1228 -- mlt_cd = TPLC then prem_pyr_cd must be ER
1229 -- prem_asnmt_cd <> ENRT then cr_lkbk_val must be null
1230 --
1231 -- Pre Conditions
1232 -- None.
1233 --
1234 -- In Parameters
1235 -- p_prem_asnmt_cd
1236 -- p_prem_asnmt_lvl_cd
1237 -- p_mlt_cd
1238 -- p_cr_lkbk_val
1239 -- p_prem_pyr_cd
1240 --
1241 -- Post Success
1242 -- Processing continues
1243 --
1244 --
1245 -- Post Failure
1246 -- Error handled by procedure
1247 --
1248 -- Access Status
1249 -- Internal table handler use only.
1250 --
1251 Procedure chk_asgnmt_cd_lvl_mlt_pyr(p_prem_asnmt_cd in varchar2,
1252 p_prem_asnmt_lvl_cd in varchar2,
1253 p_mlt_cd in varchar2,
1254 p_cr_lkbk_val in number,
1255 p_prem_pyr_cd in varchar2) is
1256 --
1257 l_proc varchar2(72) := g_package||'chk_asgnmt_cd_lvl_mlt_pyr' ;
1258 --
1259 Begin
1260 --
1261 hr_utility.set_location('Entering:'||l_proc, 5);
1262 --
1263 if (p_prem_asnmt_cd = 'ENRT' and p_prem_asnmt_lvl_cd <> 'PRTT')
1264 then
1265 fnd_message.set_name('BEN', 'BEN_92272_ASGN_ENRT_LVL_NPRTT');
1266 fnd_message.raise_error;
1267 end if;
1268 if (p_prem_asnmt_cd = 'ENRT'
1269 and p_mlt_cd not in ('NSVU', 'RL', 'FLFX','CVG')) then
1270 fnd_message.set_name('BEN', 'BEN_92274_ASGN_PROC_MLT_BAD');
1271 fnd_message.raise_error;
1272 end if;
1273
1274 if (p_prem_asnmt_cd = 'PROC' and p_prem_asnmt_lvl_cd = 'PRTT') then
1275 fnd_message.set_name('BEN', 'BEN_92273_ASGN_PROC_LVL_PRTT');
1276 fnd_message.raise_error;
1277 end if;
1278
1279 if (p_prem_asnmt_cd = 'PROC'
1280 and p_mlt_cd not in ('NSVU', 'TPLPC', 'TTLPRTT','TTLCVG')) then
1281 fnd_message.set_name('BEN', 'BEN_92274_ASGN_PROC_MLT_BAD');
1282 fnd_message.raise_error;
1283 end if;
1284
1285 if (p_mlt_cd = 'TPLC' and p_prem_pyr_cd <> 'ER')
1286 then
1287 fnd_message.set_name('BEN', 'BEN_92275_MLT_TPLC_PYR_NOTER');
1288 fnd_message.raise_error;
1289 end if;
1290
1291 if (p_prem_asnmt_cd <> 'ENRT' and p_cr_lkbk_val is not null)
1292 then
1293 fnd_message.set_name('BEN', 'BEN_92276_ASGN_ENRT_LKBK_VAL');
1294 fnd_message.raise_error;
1295 end if;
1296 --
1297 -- Leaving Procedure.
1298 --
1299 hr_utility.set_location('Leaving:'||l_proc,10);
1300 --
1301 end chk_asgnmt_cd_lvl_mlt_pyr;
1302 --
1303 -- ----------------------------------------------------------------------------
1304 -- |--------------------< chk_lmt_rl_val >------------------------------------|
1305 -- ----------------------------------------------------------------------------
1306 --
1307 -- Description
1308 -- This procedure is used to check that the code/rule dependency as the
1309 -- following:
1310 -- If lwr_RL not null then lwr_val must be null.
1311 -- If upr_RL not null then upr_val must be null.
1312 --
1313 -- Pre Conditions
1314 -- None.
1315 --
1319 -- upr_lmt_calc_rl
1316 -- In Parameters
1317 -- lwr_lmt_calc_rl
1318 -- lwr_lmt_val
1320 -- upr_lmt_val
1321 --
1322 --
1323 -- Post Success
1324 -- Processing continues
1325 --
1326 --
1327 -- Post Failure
1328 -- Error handled by procedure
1329 --
1330 -- Access Status
1331 -- Internal table handler use only.
1332 --
1333 Procedure chk_lwr_lmt_rl_val(p_upr_lmt_calc_rl in number,
1334 p_upr_lmt_val in number,
1335 p_lwr_lmt_calc_rl in number,
1336 p_lwr_lmt_val in number) is
1337 --
1338 l_proc varchar2(72) := g_package||'chk_lwr_lmt_rl_val';
1339 --
1340 Begin
1341 --
1342 hr_utility.set_location('Entering:'||l_proc, 5);
1343 --
1344 if (p_upr_lmt_calc_rl is not null and p_upr_lmt_val is not null)
1345 then
1346 fnd_message.set_name('BEN', 'BEN_92277_UPR_LMT_RL_VAL');
1347 fnd_message.raise_error;
1348 end if;
1349
1350 if (p_lwr_lmt_calc_rl is not null and p_lwr_lmt_val is not null)
1351 then
1352 fnd_message.set_name('BEN', 'BEN_92278_LWR_LMT_RL_VAL');
1353 fnd_message.raise_error;
1354 end if;
1355 --
1356 -- Leaving Procedure.
1357 --
1358 hr_utility.set_location('Leaving:'||l_proc,10);
1359 --
1360 end chk_lwr_lmt_rl_val;
1361 --
1362 -- ----------------------------------------------------------------------------
1363 -- |---------------------------< chk_lwr_lmt_upr_lmt >------------------------|
1364 -- ----------------------------------------------------------------------------
1365 --
1366 -- Description
1367 -- This procedure is used to check if the lwr_lmt value is
1368 -- less than the upr_lmt value.
1369 --
1370 -- Pre Conditions
1371 -- None.
1372 --
1373 -- In Parameters
1374 -- p_lwr_lmt_val minimum value
1375 -- p_upr_lmt_val maximum value
1376 --
1377 -- Post Success
1378 -- Processing continues
1379 --
1380 -- Post Failure
1381 -- Error handled by procedure
1382 --
1383 -- Access Status
1384 -- Internal table handler use only.
1385 --
1386 Procedure chk_lwr_lmt_upr_lmt(p_lwr_lmt_val in number,
1387 p_upr_lmt_val in number) is
1388 --
1389 l_proc varchar2(72) := g_package||'chk_lwr_lmt_upr_lmt';
1390 --
1391 Begin
1392 --
1393 hr_utility.set_location('Entering:'||l_proc, 5);
1394 --
1395 -- check the values
1396 -- note: Don't want an error if either one is null
1397 --
1398 if (p_lwr_lmt_val is not null and p_upr_lmt_val is not null) and
1399 (p_lwr_lmt_val >= p_upr_lmt_val) then
1400 --
1401 -- raise error as is not a valid combination
1402 --
1403 fnd_message.set_name('BEN','BEN_92279_LWR_LESS_NOT_EQ_UPR');
1404 fnd_message.raise_error;
1405 --
1406 end if;
1407 --
1408 hr_utility.set_location('Leaving:'||l_proc, 5);
1409 --
1410 end chk_lwr_lmt_upr_lmt;
1411 -- ----------------------------------------------------------------------------
1412 -- |------------------------< chk_asg_vrbl_prfl_mlt_tmt >---------------------|
1413 -- ----------------------------------------------------------------------------
1414 --
1415 -- Description
1416 -- This procedure is used to check the mld_cd and vrbl_rt_trtmt_cd for
1417 -- vrbl_rt_prfl assigned to this premium are valid values.
1418 --
1419 -- Pre Conditions
1420 -- None.
1421 --
1422 -- In Parameters
1423 -- actl_prem_id PK of record being inserted or updated.
1424 -- prem_asnmt_cd
1425 -- effective_date Session date of record.
1426 -- business_group_id Business group id of record being inserted.
1427 --
1428 -- Post Success
1429 -- Processing continues
1430 --
1431 -- Post Failure
1432 -- Errors handled by the procedure
1433 --
1434 -- Access Status
1435 -- Internal table handler use only.
1436 --
1437 Procedure chk_asg_vrbl_prfl_mlt_tmt(p_actl_prem_id in number,
1438 p_prem_asnmt_cd in varchar2,
1439 p_effective_date in date,
1440 p_business_group_id in number) is
1441 --
1442 l_proc varchar2(72) := g_package||'chk_asg_vrbl_prfl_mlt_tmt';
1443 l_api_updating boolean;
1444 l_mlt_cd hr_lookups.lookup_code%TYPE; -- UTF varchar2(30);
1445 l_vrbl_rt_trtmt_cd hr_lookups.lookup_code%TYPE; -- UTF varchar2(30);
1446 --
1447 cursor c1 is
1448 select vpf.mlt_cd, vpf.vrbl_rt_trtmt_cd
1449 from ben_vrbl_rt_prfl_f vpf,
1450 ben_actl_prem_vrbl_rt_f apv
1451 where apv.business_group_id +0 = p_business_group_id
1452 and vpf.business_group_id +0 = p_business_group_id
1453 and apv.actl_prem_id = p_actl_prem_id
1454 and vpf.vrbl_rt_prfl_id = apv.vrbl_rt_prfl_id
1455 and p_effective_date
1456 between vpf.effective_start_date
1457 and vpf.effective_end_date
1458 and p_effective_date + 1
1459 between apv.effective_start_date
1460 and apv.effective_end_date;
1461 --
1462 Begin
1463 --
1464 hr_utility.set_location('Entering:'||l_proc, 5);
1465 --
1469 --
1466 if p_prem_asnmt_cd is not null then
1467 --
1468 -- Check if vrbl_rt_prfl is assigned to premium and do checks.
1470 open c1;
1471 --
1472 fetch c1 into l_mlt_cd, l_vrbl_rt_trtmt_cd;
1473 if c1%found then
1474 --
1475 --
1476 --close c1;
1477 if p_prem_asnmt_cd = 'PROC' then
1478 if l_vrbl_rt_trtmt_cd <> 'RPLC' then
1479 --
1480 -- raise an error as invalid values.
1481 --
1482 fnd_message.set_name('BEN','BEN_92280_ASGNCD_VRBL_PRFL_TMT');
1483 fnd_message.raise_error;
1484 end if;
1485 if l_mlt_cd <> 'TTLPRTT' then
1486 --
1487 -- raise an error as invalid values.
1488 --
1489 fnd_message.set_name('BEN','BEN_92281_ASGNCD_VRBL_PRFL_ML1');
1490 fnd_message.raise_error;
1491 end if;
1492 elsif (p_prem_asnmt_cd = 'ENRT' and
1493 l_mlt_cd not in ('FLFX', 'CVG', 'NSVU', 'TPLPC', 'RL')) then
1494 --
1495 -- raise an error as invalid values.
1496 --
1497 fnd_message.set_name('BEN','BEN_92282_ASGNCD_VRBL_PRFL_ML2');
1498 fnd_message.raise_error;
1499
1500 end if;
1501 --
1502 end if;
1503 --
1504 close c1;
1505 --
1506 end if;
1507 --
1508 hr_utility.set_location('Leaving:'||l_proc, 10);
1509 --
1510 End chk_asg_vrbl_prfl_mlt_tmt;
1511 -- ----------------------------------------------------------------------------
1512 -- |----------------------------< chk_lookups >-------------------------------|
1513 -- ----------------------------------------------------------------------------
1514 --
1515 -- Description
1516 -- This procedure is used to check that the lookup values are valid.
1517 --
1518 -- Pre Conditions
1519 -- None.
1520 --
1521 -- In Parameters
1522 -- actl_prem_id PK of record being inserted or updated.
1523 -- p_prem_asnmt_cd Value of lookup code.
1524 -- p_prem_asnmt_lvl_cd Value of lookup code.
1525 -- p_actl_prem_typ_cd Value of lookup code.
1526 -- p_prem_pyr_cd Value of lookup code.
1527 -- p_prsptv_r_rtsptv_cd Value of lookup code.
1528 -- effective_date effective date
1529 -- object_version_number Object version number of record being
1530 -- inserted or updated.
1531 --
1532 -- Post Success
1533 -- Processing continues
1534 --
1535 -- Post Failure
1536 -- Error handled by procedure
1537 --
1538 -- Access Status
1539 -- Internal table handler use only.
1540 --
1541 Procedure chk_lookups(p_actl_prem_id in number,
1542 p_prem_asnmt_cd in varchar2,
1543 p_prem_asnmt_lvl_cd in varchar2,
1544 p_actl_prem_typ_cd in varchar2,
1545 p_prem_pyr_cd in varchar2,
1546 p_prsptv_r_rtsptv_cd in varchar2,
1547 p_effective_date in date,
1548 p_object_version_number in number) is
1549 --
1550 l_proc varchar2(72) := g_package||'chk_lookups';
1551 l_api_updating boolean;
1552 l_dummy varchar2(30);
1553 --
1554 Begin
1555 --
1556 hr_utility.set_location('Entering:'||l_proc, 5);
1557 --
1558 l_api_updating := ben_apr_shd.api_updating
1559 (p_actl_prem_id => p_actl_prem_id,
1560 p_effective_date => p_effective_date,
1561 p_object_version_number => p_object_version_number);
1562 --
1563 if (l_api_updating
1564 and p_prem_asnmt_cd
1565 <> nvl(ben_apr_shd.g_old_rec.prem_asnmt_cd,hr_api.g_varchar2)
1566 or not l_api_updating)
1567 and p_prem_asnmt_cd is not null then
1568 --
1569 -- check if value of lookup falls within lookup type.
1570 --
1571 if hr_api.not_exists_in_hr_lookups
1572 (p_lookup_type => 'BEN_PREM_ASNMT',
1573 p_lookup_code => p_prem_asnmt_cd,
1574 p_effective_date => p_effective_date) then
1575 --
1576 -- raise error as does not exist as lookup
1577 --
1578 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1579 fnd_message.set_token('FIELD', 'p_prem_asnmt_cd');
1580 fnd_message.set_token('TYPE', 'BEN_PREM_ASNMT');
1581 fnd_message.raise_error;
1582 --
1583 --
1584 end if;
1585 --
1586 end if;
1587 --
1588 if (l_api_updating
1589 and p_prem_asnmt_lvl_cd
1590 <> nvl(ben_apr_shd.g_old_rec.prem_asnmt_lvl_cd,hr_api.g_varchar2)
1591 or not l_api_updating)
1592 and p_prem_asnmt_lvl_cd is not null then
1593 --
1594 -- check if value of lookup falls within lookup type.
1595 --
1596 if hr_api.not_exists_in_hr_lookups
1597 (p_lookup_type => 'BEN_PREM_ASNMT_LVL',
1598 p_lookup_code => p_prem_asnmt_lvl_cd,
1599 p_effective_date => p_effective_date) then
1600 --
1601 -- raise error as does not exist as lookup
1602 --
1606 fnd_message.raise_error;
1603 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1604 fnd_message.set_token('FIELD', 'p_prem_asnmt_lvl_cd');
1605 fnd_message.set_token('TYPE', 'BEN_PREM_ASNMT_LVL');
1607 --
1608 --
1609 end if;
1610 --
1611 end if;
1612 --
1613 if (l_api_updating
1614 and p_actl_prem_typ_cd
1615 <> nvl(ben_apr_shd.g_old_rec.actl_prem_typ_cd,hr_api.g_varchar2)
1616 or not l_api_updating)
1617 and p_actl_prem_typ_cd is not null then
1618 --
1619 -- check if value of lookup falls within lookup type.
1620 --
1621 if hr_api.not_exists_in_hr_lookups
1622 (p_lookup_type => 'BEN_PREM_TYP',
1623 p_lookup_code => p_actl_prem_typ_cd,
1624 p_effective_date => p_effective_date) then
1625 --
1626 -- raise error as does not exist as lookup
1627 --
1628 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1629 fnd_message.set_token('FIELD', 'p_actl_prem_typ_cd');
1630 fnd_message.set_token('TYPE', 'BEN_PREM_TYP');
1631 fnd_message.raise_error;
1632 --
1633 --
1634 end if;
1635 --
1636 end if;
1637 --
1638 if (l_api_updating
1639 and p_prem_pyr_cd
1640 <> nvl(ben_apr_shd.g_old_rec.prem_pyr_cd,hr_api.g_varchar2)
1641 or not l_api_updating)
1642 and p_prem_pyr_cd is not null then
1643 --
1644 -- check if value of lookup falls within lookup type.
1645 --
1646 if hr_api.not_exists_in_hr_lookups
1647 (p_lookup_type => 'BEN_PREM_PYR',
1648 p_lookup_code => p_prem_pyr_cd,
1649 p_effective_date => p_effective_date) then
1650 --
1651 -- raise error as does not exist as lookup
1652 --
1653 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1654 fnd_message.set_token('FIELD', 'p_prem_pyr_cd');
1655 fnd_message.set_token('TYPE', 'BEN_PREM_PYR');
1656 fnd_message.raise_error;
1657 --
1658 --
1659 end if;
1660 --
1661 end if;
1662 --
1663 if (l_api_updating
1664 and p_prsptv_r_rtsptv_cd
1665 <> nvl(ben_apr_shd.g_old_rec.prsptv_r_rtsptv_cd,hr_api.g_varchar2)
1666 or not l_api_updating)
1667 and p_prsptv_r_rtsptv_cd is not null then
1668 --
1669 -- check if value of lookup falls within lookup type.
1670 --
1671 if hr_api.not_exists_in_hr_lookups
1672 (p_lookup_type => 'BEN_PRSPCTV_R_RTSPCTV',
1673 p_lookup_code => p_prsptv_r_rtsptv_cd,
1674 p_effective_date => p_effective_date) then
1675 --
1676 -- raise error as does not exist as lookup
1677 --
1678 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1679 fnd_message.set_token('FIELD', 'p_prsptv_r_rtsptv_cd');
1680 fnd_message.set_token('TYPE', 'BEN_PRSPCTV_R_RTSPCTV');
1681 fnd_message.raise_error;
1682 --
1683 --
1684 end if;
1685 --
1686 end if;
1687 --
1688 hr_utility.set_location('Leaving:'||l_proc,10);
1689 --
1690 end chk_lookups;
1691 --
1692 -- ----------------------------------------------------------------------------
1693 -- |--------------------------< chk_rules >-----------------------------------|
1694 -- ----------------------------------------------------------------------------
1695 --
1696 -- Description
1697 -- This procedure is used to check that the Formula Rules are valid.
1698 --
1699 -- Pre Conditions
1700 -- None.
1701 --
1702 -- In Parameters
1703 -- actl_prem_id PK of record being inserted or updated.
1704 -- lwr_lmt_calc_rl Value of formula rule id.
1705 -- upr_lmt_calc_rl Value of formula rule id.
1706 -- prtl_mo_det_mthd_rl Value of formula rule id.
1707 -- vrbl_rt_add_on_calc_rl Value of formula rule id.
1708 -- effective_date effective date
1709 -- object_version_number Object version number of record being
1710 -- inserted or updated.
1711 --
1712 -- Post Success
1713 -- Processing continues
1714 --
1715 -- Post Failure
1716 -- Error handled by procedure
1717 --
1718 -- Access Status
1719 -- Internal table handler use only.
1720 --
1721 Procedure chk_rules(p_actl_prem_id in number,
1722 p_business_group_id in number,
1723 p_lwr_lmt_calc_rl in number,
1724 p_upr_lmt_calc_rl in number,
1725 p_prtl_mo_det_mthd_rl in number,
1726 -- p_vrbl_rt_add_on_calc_rl in number,
1727 p_effective_date in date,
1728 p_object_version_number in number) is
1729 --
1730 l_proc varchar2(72) := g_package||'chk_rules';
1731 l_api_updating boolean;
1732 l_dummy varchar2(1);
1733 --
1734 cursor c1(p_rule number,p_rule_type_id number) is
1735 select null
1736 from ff_formulas_f ff,
1737 per_business_groups pbg
1738 where ff.formula_id = p_rule
1739 and ff.formula_type_id = p_rule_type_id
1740 and pbg.business_group_id = p_business_group_id
1744 pbg.legislation_code
1741 and nvl(ff.business_group_id,p_business_group_id) =
1742 p_business_group_id
1743 and nvl(ff.legislation_code,pbg.legislation_code) =
1745 and p_effective_date
1746 between ff.effective_start_date
1747 and ff.effective_end_date;
1748 --
1749 Begin
1750 --
1751 hr_utility.set_location('Entering:'||l_proc, 5);
1752 --
1753 l_api_updating := ben_apr_shd.api_updating
1754 (p_actl_prem_id => p_actl_prem_id,
1755 p_effective_date => p_effective_date,
1756 p_object_version_number => p_object_version_number);
1757 --
1758 if (l_api_updating
1759 and nvl(p_lwr_lmt_calc_rl,hr_api.g_number)
1760 <> ben_apr_shd.g_old_rec.lwr_lmt_calc_rl
1761 or not l_api_updating)
1762 and p_lwr_lmt_calc_rl is not null then
1763 --
1764 -- check if value of formula rule is valid.
1765 --
1766 open c1(p_lwr_lmt_calc_rl,-515);
1767 --
1768 -- fetch value from cursor if it returns a record then the
1769 -- formula is valid otherwise its invalid
1770 --
1771 fetch c1 into l_dummy;
1772 if c1%notfound then
1773 --
1774 close c1;
1775 --
1776 -- raise error
1777 --
1778 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1779 fnd_message.set_token('ID',p_lwr_lmt_calc_rl);
1780 fnd_message.set_token('TYPE_ID',-515);
1781 fnd_message.raise_error;
1782 --
1783 end if;
1784 --
1785 close c1;
1786 --
1787 end if;
1788 --
1789 if (l_api_updating
1790 and nvl(p_upr_lmt_calc_rl,hr_api.g_number)
1791 <> ben_apr_shd.g_old_rec.upr_lmt_calc_rl
1792 or not l_api_updating)
1793 and p_upr_lmt_calc_rl is not null then
1794 --
1795 -- check if value of formula rule is valid.
1796 --
1797 open c1(p_upr_lmt_calc_rl,-512);
1798 --
1799 -- fetch value from cursor if it returns a record then the
1800 -- formula is valid otherwise its invalid
1801 --
1802 fetch c1 into l_dummy;
1803 if c1%notfound then
1804 --
1805 close c1;
1806 --
1807 -- raise error
1808 --
1809 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1810 fnd_message.set_token('ID',p_upr_lmt_calc_rl);
1811 fnd_message.set_token('TYPE_ID',-512);
1812 fnd_message.raise_error;
1813 --
1814 end if;
1815 --
1816 close c1;
1817 --
1818 end if;
1819 --
1820 --
1821 if (l_api_updating
1822 and nvl(p_prtl_mo_det_mthd_rl,hr_api.g_number)
1823 <> ben_apr_shd.g_old_rec.prtl_mo_det_mthd_rl
1824 or not l_api_updating)
1825 and p_prtl_mo_det_mthd_rl is not null then
1826 --
1827 -- check if value of formula rule is valid.
1828 --
1829 open c1(p_prtl_mo_det_mthd_rl,-165);
1830 --
1831 -- fetch value from cursor if it returns a record then the
1832 -- formula is valid otherwise its invalid
1833 --
1834 fetch c1 into l_dummy;
1835 if c1%notfound then
1836 --
1837 close c1;
1838 --
1839 -- raise error
1840 --
1841 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1842 fnd_message.set_token('ID',p_prtl_mo_det_mthd_rl);
1843 fnd_message.set_token('TYPE_ID',-165);
1844 fnd_message.raise_error;
1845 --
1846 end if;
1847 --
1848 close c1;
1849 --
1850 end if;
1851 --
1852 -- hr_utility.set_location('Leaving:'||l_proc,10);
1853 --
1854 end chk_rules;
1855 --
1856 -- ----------------------------------------------------------------------------
1857 -- |------------------------< chk_child_data >-------------------------------|
1858 -- ----------------------------------------------------------------------------
1859 --
1860 -- Description
1861 -- This procedure is used to check that
1862 -- 1) If parent actual premium has PREM_ASNMT_CD=PROC, then there should not
1863 -- be any row in the child table(costing table).
1864 --
1865 -- 2) There should not be any row in the child table (costing table ),
1866 -- if default costing in parent is null.
1867 --
1868 -- Pre Conditions
1869 -- None.
1870 --
1871 -- In Parameters
1872 -- actl_prem_id actl_prem_id.
1873 -- business_group_id Business group id of record being inserted.
1874 -- prem_asnmt_cd Premium assignment code
1875 -- cost_allocation_keyflex_id Default costing
1876 --
1877 -- Post Success
1878 -- Processing continues
1879 --
1880 -- Post Failure
1881 -- Errors handled by the procedure
1882 --
1883 -- Access Status
1884 -- Internal table handler use only.
1885 --
1886 --
1887 Procedure chk_child_data(p_actl_prem_id in number,
1888 p_business_group_id in number,
1889 p_prem_asnmt_cd in varchar2,
1890 p_cost_allocation_keyflex_id in number) is
1891
1895 cursor c1 is
1892 l_proc varchar2(72) := g_package||'chk_child_data' ;
1893 l_actl_prem_id number;
1894 --
1896 select a.actl_prem_id from ben_prem_cstg_by_sgmt_f a
1897 where a.business_group_id +0 = p_business_group_id
1898 and a.actl_prem_id = p_actl_prem_id;
1899 --
1900 Begin
1901 --
1902 hr_utility.set_location('Entering:'||l_proc, 5);
1903 --
1904 --
1905 -- Check if prem_asnmt_cd is 'PROC'
1906 --
1907 if p_prem_asnmt_cd = 'PROC'or p_cost_allocation_keyflex_id is null then
1908 --
1909 open c1;
1910 fetch c1 into l_actl_prem_id ;
1911 --
1912 if c1%found then
1913 close c1;
1914 --
1915 -- raise an error
1916 --
1917 if p_prem_asnmt_cd = 'PROC' then
1918 fnd_message.set_name('BEN','BEN_92529_NO_COST');
1919 fnd_message.raise_error;
1920 else
1921 fnd_message.set_name('BEN','BEN_92530_DFLT_RQD');
1922 fnd_message.raise_error;
1923 end if;
1924 --
1925 end if;
1926 --
1927 close c1;
1928 end if;
1929 --
1930
1931 hr_utility.set_location('Leaving:'||l_proc, 10);
1932 --
1933 End chk_child_data;
1934
1935 -- ----------------------------------------------------------------------------
1936 -- |--------------------------< dt_update_validate >--------------------------|
1937 -- ----------------------------------------------------------------------------
1938 -- {Start Of Comments}
1939 --
1940 -- Description:
1941 -- This procedure is used for referential integrity of datetracked
1942 -- parent entities when a datetrack update operation is taking place
1943 -- and where there is no cascading of update defined for this entity.
1944 --
1945 -- Prerequisites:
1946 -- This procedure is called from the update_validate.
1947 --
1948 -- In Parameters:
1949 --
1950 -- Post Success:
1951 -- Processing continues.
1952 --
1953 -- Post Failure:
1954 --
1955 -- Developer Implementation Notes:
1956 -- This procedure should not need maintenance unless the HR Schema model
1957 -- changes.
1958 --
1959 -- Access Status:
1960 -- Internal Row Handler Use Only.
1961 --
1962 -- {End Of Comments}
1963 -- ----------------------------------------------------------------------------
1964 Procedure dt_update_validate
1965 (p_comp_lvl_fctr_id in number default hr_api.g_number,
1966 p_datetrack_mode in varchar2,
1967 p_validation_start_date in date,
1968 p_validation_end_date in date) Is
1969 --
1970 l_proc varchar2(72) := g_package||'dt_update_validate';
1971 l_integrity_error Exception;
1972 l_table_name all_tables.table_name%TYPE;
1973 --
1974 Begin
1975 hr_utility.set_location('Entering:'||l_proc, 5);
1976 --
1977 -- Ensure that the p_datetrack_mode argument is not null
1978 --
1979 hr_api.mandatory_arg_error
1980 (p_api_name => l_proc,
1981 p_argument => 'datetrack_mode',
1982 p_argument_value => p_datetrack_mode);
1983 --
1984 -- Only perform the validation if the datetrack update mode is valid
1985 --
1986 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
1987 --
1988 --
1989 -- Ensure the arguments are not null
1990 --
1991 hr_api.mandatory_arg_error
1992 (p_api_name => l_proc,
1993 p_argument => 'validation_start_date',
1994 p_argument_value => p_validation_start_date);
1995 --
1996 hr_api.mandatory_arg_error
1997 (p_api_name => l_proc,
1998 p_argument => 'validation_end_date',
1999 p_argument_value => p_validation_end_date);
2000 --
2001 /*
2002 If ((nvl(p_comp_lvl_fctr_id, hr_api.g_number) <> hr_api.g_number) and
2003 NOT (dt_api.check_min_max_dates
2004 (p_base_table_name => 'ben_comp_lvl_fctr',
2005 p_base_key_column => 'comp_lvl_fctr_id',
2006 p_base_key_value => p_comp_lvl_fctr_id,
2007 p_from_date => p_validation_start_date,
2008 p_to_date => p_validation_end_date))) Then
2009 l_table_name := 'ben_comp_lvl_fctr';
2010 Raise l_integrity_error;
2011 End If;
2012 */
2013 --
2014 End If;
2015 --
2016 hr_utility.set_location(' Leaving:'||l_proc, 10);
2017 Exception
2018 When l_integrity_error Then
2019 --
2020 -- A referential integrity check was violated therefore
2021 -- we must error
2022 --
2023 ben_utility.parent_integrity_error(p_table_name => l_table_name);
2024 When Others Then
2025 --
2026 -- An unhandled or unexpected error has occurred which
2027 -- we must report
2028 --
2029 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2030 fnd_message.set_token('PROCEDURE', l_proc);
2031 fnd_message.set_token('STEP','15');
2032 fnd_message.raise_error;
2033 End dt_update_validate;
2034 --
2035 -- ----------------------------------------------------------------------------
2036 -- |--------------------------< dt_delete_validate >--------------------------|
2040 -- Description:
2037 -- ----------------------------------------------------------------------------
2038 -- {Start Of Comments}
2039 --
2041 -- This procedure is used for referential integrity of datetracked
2042 -- child entities when either a datetrack DELETE or ZAP is in operation
2043 -- and where there is no cascading of delete defined for this entity.
2044 -- For the datetrack mode of DELETE or ZAP we must ensure that no
2045 -- datetracked child rows exist between the validation start and end
2046 -- dates.
2047 --
2048 -- Prerequisites:
2049 -- This procedure is called from the delete_validate.
2050 --
2051 -- In Parameters:
2052 --
2053 -- Post Success:
2054 -- Processing continues.
2055 --
2056 -- Post Failure:
2057 -- If a row exists by determining the returning Boolean value from the
2058 -- generic dt_api.rows_exist function then we must supply an error via
2059 -- the use of the local exception handler l_rows_exist.
2060 --
2061 -- Developer Implementation Notes:
2062 -- This procedure should not need maintenance unless the HR Schema model
2063 -- changes.
2064 --
2065 -- Access Status:
2066 -- Internal Row Handler Use Only.
2067 --
2068 -- {End Of Comments}
2069 -- ----------------------------------------------------------------------------
2070 Procedure dt_delete_validate
2071 (p_actl_prem_id in number,
2072 p_datetrack_mode in varchar2,
2073 p_validation_start_date in date,
2074 p_validation_end_date in date) Is
2075 --
2076 l_proc varchar2(72) := g_package||'dt_delete_validate';
2077 l_rows_exist Exception;
2078 l_table_name all_tables.table_name%TYPE;
2079 --
2080 Begin
2081 hr_utility.set_location('Entering:'||l_proc, 5);
2082 --
2083 -- Ensure that the p_datetrack_mode argument is not null
2084 --
2085 hr_api.mandatory_arg_error
2086 (p_api_name => l_proc,
2087 p_argument => 'datetrack_mode',
2088 p_argument_value => p_datetrack_mode);
2089 --
2090 -- Only perform the validation if the datetrack mode is either
2091 -- DELETE or ZAP
2092 --
2093 If (p_datetrack_mode = 'DELETE' or
2094 p_datetrack_mode = 'ZAP') then
2095 --
2096 --
2097 -- Ensure the arguments are not null
2098 --
2099 hr_api.mandatory_arg_error
2100 (p_api_name => l_proc,
2101 p_argument => 'validation_start_date',
2102 p_argument_value => p_validation_start_date);
2103 --
2104 hr_api.mandatory_arg_error
2105 (p_api_name => l_proc,
2106 p_argument => 'validation_end_date',
2107 p_argument_value => p_validation_end_date);
2108 --
2109 hr_api.mandatory_arg_error
2110 (p_api_name => l_proc,
2111 p_argument => 'actl_prem_id',
2112 p_argument_value => p_actl_prem_id);
2113 --
2114 If (dt_api.rows_exist
2115 (p_base_table_name => 'ben_oipl_f',
2116 p_base_key_column => 'actl_prem_id',
2117 p_base_key_value => p_actl_prem_id,
2118 p_from_date => p_validation_start_date,
2119 p_to_date => p_validation_end_date)) Then
2120 l_table_name := 'ben_oipl_f';
2121 Raise l_rows_exist;
2122 End If;
2123 If (dt_api.rows_exist
2124 (p_base_table_name => 'ben_actl_prem_vrbl_rt_f',
2125 p_base_key_column => 'actl_prem_id',
2126 p_base_key_value => p_actl_prem_id,
2127 p_from_date => p_validation_start_date,
2128 p_to_date => p_validation_end_date)) Then
2129 l_table_name := 'ben_actl_prem_vrbl_rt_f';
2130 Raise l_rows_exist;
2131 End If;
2132 If (dt_api.rows_exist
2133 (p_base_table_name => 'ben_prtt_prem_f',
2134 p_base_key_column => 'actl_prem_id',
2135 p_base_key_value => p_actl_prem_id,
2136 p_from_date => p_validation_start_date,
2137 p_to_date => p_validation_end_date)) Then
2138 l_table_name := 'ben_prtt_prem_f';
2139 Raise l_rows_exist;
2140 End If;
2141
2142 -- If (dt_api.rows_exist
2143 -- (p_base_table_name => 'ben_actl_prem_vrbl_rt_rl_f',
2144 -- p_base_key_column => 'actl_prem_id',
2145 -- p_base_key_value => p_actl_prem_id,
2146 -- p_from_date => p_validation_start_date,
2147 -- p_to_date => p_validation_end_date)) Then
2148 -- l_table_name := 'ben_actl_prem_vrbl_rt_rl_f';
2149 -- Raise l_rows_exist;
2150 -- End If;
2151 --
2152 End If;
2153 --
2154 hr_utility.set_location(' Leaving:'||l_proc, 10);
2155 Exception
2156 When l_rows_exist Then
2157 --
2158 -- A referential integrity check was violated therefore
2159 -- we must error
2160 --
2161 ben_utility.child_exists_error(p_table_name => l_table_name);
2162
2163 When Others Then
2164 --
2165 -- An unhandled or unexpected error has occurred which
2166 -- we must report
2167 --
2168 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2169 fnd_message.set_token('PROCEDURE', l_proc);
2170 fnd_message.set_token('STEP','15');
2171 fnd_message.raise_error;
2172 end dt_delete_validate;
2173 --
2174 --
2178 --
2175 -- ----------------------------------------------------------------------------
2176 -- |------< chk_mlt_cd_dependencies >------|
2177 -- ----------------------------------------------------------------------------
2179 -- Description
2180 --
2181 --
2182 -- In Parameters
2183 -- mlt_cd
2184 -- val
2185 -- mn_val
2186 -- mx_val
2187 -- incrmt_val
2188 -- dflt_val
2189 -- rt_typ_cd
2190 -- bnfts_rt_typ_cd
2191 -- val_calc_rl
2192 -- comp_lvl_fctr_id
2193 -- acty_base_rt_id
2194 -- effective_date
2195 -- object_version_number
2196 --
2197 Procedure chk_mlt_cd_dependencies(p_mlt_cd in varchar2,
2198 p_val in number,
2199 p_pl_id in number,
2200 p_oipl_id in number,
2201 p_rt_typ_cd in varchar2,
2202 p_bnft_rt_typ_cd in varchar2,
2203 p_actl_prem_id in number,
2204 p_comp_lvl_fctr_id in number,
2205 p_business_group_id in number,
2206 p_val_calc_rl in number,
2207 p_effective_date in date,
2208 p_object_version_number in number
2209 ) is
2210 l_proc varchar2(72) := g_package||'chk_mlt_cd_dependencies';
2211 l_api_updating boolean;
2212 l_dummy ben_cvg_amt_calc_mthd_f.name%type := null; --UTF8 Change Bug 2254683
2213 --
2214 -- Bug 2695254 changed the ben_pl to ben_pl_f and ben_oipl to ben_oipl_f
2215 --
2216 cursor c1 is
2217 select cvg.name
2218 from ben_pl_f coa,ben_cvg_amt_calc_mthd_f cvg
2219 where coa.pl_id = p_pl_id
2220 and coa.pl_id = cvg.pl_id (+)
2221 and coa.business_group_id = p_business_group_id
2222 union
2223 select cvg.name
2224 from ben_oipl_f coa,ben_cvg_amt_calc_mthd_f cvg
2225 where coa.oipl_id = p_oipl_id
2226 and coa.oipl_id = cvg.oipl_id (+)
2227 and coa.business_group_id = p_business_group_id;
2228
2229 Begin
2230 hr_utility.set_location('Entering:'||l_proc, 5);
2231 l_api_updating := ben_apr_shd.api_updating
2232 (p_actl_prem_id => p_actl_prem_id,
2233 p_effective_date => p_effective_date,
2234 p_object_version_number => p_object_version_number);
2235 if (l_api_updating and
2236 (nvl(p_mlt_cd,hr_api.g_varchar2)
2237 <> nvl(ben_apr_shd.g_old_rec.mlt_cd,hr_api.g_varchar2) or
2238 nvl(p_val,hr_api.g_number)
2239 <> nvl(ben_apr_shd.g_old_rec.val,hr_api.g_number) or
2240 nvl(p_pl_id,hr_api.g_number)
2241 <> nvl(ben_apr_shd.g_old_rec.pl_id,hr_api.g_number) or
2242 nvl(p_oipl_id,hr_api.g_number)
2243 <> nvl(ben_apr_shd.g_old_rec.oipl_id,hr_api.g_number) or
2244 nvl(p_rt_typ_cd,hr_api.g_varchar2)
2245 <> nvl(ben_apr_shd.g_old_rec.rt_typ_cd,hr_api.g_varchar2) or
2246 nvl(p_bnft_rt_typ_cd,hr_api.g_varchar2)
2247 <> nvl(ben_apr_shd.g_old_rec.bnft_rt_typ_cd,hr_api.g_varchar2)
2248 ) or not l_api_updating)
2249 then
2250 if p_mlt_cd is NULL then
2251 fnd_message.set_name('BEN','BEN_91535_MLT_CD_RQD');
2252 fnd_message.raise_error;
2253 end if;
2254 if p_val is NULL then
2255 if p_mlt_cd in ('FLFX','CVG','TTLCVG','TTLPRTT') then
2256 fnd_message.set_name('BEN','BEN_91536_VAL_RQD');
2257 fnd_message.raise_error;
2258 end if;
2259 end if;
2260 if p_rt_typ_cd is not NULL then
2261 if p_mlt_cd in ('FLFX','CVG','RL') then
2262 fnd_message.set_name('BEN','BEN_91545_RT_TYP_CD_SPEC');
2263 fnd_message.raise_error;
2264 end if;
2265 end if;
2266 if p_bnft_rt_typ_cd is NULL then
2267 if p_mlt_cd in ('CVG','TTLCVG','TTLPRTT') then
2268 fnd_message.set_name('BEN','BEN_91546_BNFTS_TYP_CD_RQD');
2269 fnd_message.raise_error;
2270 end if;
2271 else
2272 if p_mlt_cd in ('FLFX','RL') then
2273 fnd_message.set_name('BEN','BEN_91547_BNFTS_TYP_CD_SPEC');
2274 fnd_message.raise_error;
2275 end if;
2276 end if;
2277 if p_mlt_cd = 'CVG' then
2278 open c1;
2279 fetch c1 into l_dummy;
2280 if l_dummy is null then
2281 fnd_message.set_name('BEN','BEN_92473_COVERAGE_REQUIRED');
2282 fnd_message.raise_error;
2283 end if;
2284 close c1;
2285 end if;
2286 if p_mlt_cd = 'RL' and p_val_calc_rl is null then
2287 fnd_message.set_name('BEN','BEN_91548_VAL_CALC_RL_RQD');
2288 fnd_message.raise_error;
2289 end if;
2290 end if;
2291 hr_utility.set_location('Leaving:'||l_proc,10);
2292 end chk_mlt_cd_dependencies;
2293 --
2294 --
2295 -- ----------------------------------------------------------------------------
2296 -- |---------------------------< insert_validate >----------------------------|
2297 -- ----------------------------------------------------------------------------
2298 Procedure insert_validate
2299 (p_rec in ben_apr_shd.g_rec_type,
2300 p_effective_date in date,
2304 --
2301 p_datetrack_mode in varchar2,
2302 p_validation_start_date in date,
2303 p_validation_end_date in date) is
2305 l_proc varchar2(72) := g_package||'insert_validate';
2306 --
2307 Begin
2308 hr_utility.set_location('Entering:'||l_proc, 5);
2309 --
2310 -- Call all supporting business operations
2311 --
2312 --
2313 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2314 --
2315 chk_actl_prem_id
2316 (p_actl_prem_id => p_rec.actl_prem_id,
2317 p_effective_date => p_effective_date,
2318 p_object_version_number => p_rec.object_version_number);
2319 --
2320 chk_organization_id
2321 (p_actl_prem_id => p_rec.actl_prem_id,
2322 p_organization_id => p_rec.organization_id,
2323 p_pl_id => p_rec.pl_id,
2324 p_oipl_id => p_rec.oipl_id,
2325 p_effective_date => p_effective_date,
2326 p_business_group_id => p_rec.business_group_id,
2327 p_object_version_number => p_rec.object_version_number);
2328 --
2329 chk_rndg_rl
2330 (p_actl_prem_id => p_rec.actl_prem_id,
2331 p_rndg_rl => p_rec.rndg_rl,
2332 p_business_group_id => p_rec.business_group_id,
2333 p_effective_date => p_effective_date,
2334 p_object_version_number => p_rec.object_version_number);
2335 --
2336 chk_val_calc_rl
2337 (p_actl_prem_id => p_rec.actl_prem_id,
2338 p_val_calc_rl => p_rec.val_calc_rl,
2339 p_business_group_id => p_rec.business_group_id,
2340 p_effective_date => p_effective_date,
2341 p_object_version_number => p_rec.object_version_number);
2342 --
2343 chk_vrbl_rt_add_on_calc_rl
2344 (p_actl_prem_id => p_rec.actl_prem_id,
2345 p_vrbl_rt_add_on_calc_rl => p_rec.vrbl_rt_add_on_calc_rl,
2346 p_business_group_id => p_rec.business_group_id,
2347 p_effective_date => p_effective_date,
2348 p_object_version_number => p_rec.object_version_number);
2349 --
2350 chk_rndg_cd
2351 (p_actl_prem_id => p_rec.actl_prem_id,
2352 p_rndg_cd => p_rec.rndg_cd,
2353 p_effective_date => p_effective_date,
2354 p_object_version_number => p_rec.object_version_number);
2355 --
2356 chk_prdct_cd
2357 (p_actl_prem_id => p_rec.actl_prem_id,
2358 p_prdct_cd => p_rec.prdct_cd,
2359 p_effective_date => p_effective_date,
2360 p_object_version_number => p_rec.object_version_number);
2361 --
2362 chk_mlt_cd
2363 (p_actl_prem_id => p_rec.actl_prem_id,
2364 p_mlt_cd => p_rec.mlt_cd,
2365 p_effective_date => p_effective_date,
2366 p_object_version_number => p_rec.object_version_number);
2367 --
2368 chk_prtl_mo_det_mthd_cd
2369 (p_actl_prem_id => p_rec.actl_prem_id,
2370 p_prtl_mo_det_mthd_cd => p_rec.prtl_mo_det_mthd_cd,
2371 p_effective_date => p_effective_date,
2372 p_object_version_number => p_rec.object_version_number);
2373 --
2374 chk_rt_typ_cd
2375 (p_actl_prem_id => p_rec.actl_prem_id,
2376 p_rt_typ_cd => p_rec.rt_typ_cd,
2377 p_effective_date => p_effective_date,
2378 p_object_version_number => p_rec.object_version_number);
2379 --
2380 chk_bnft_rt_typ_cd
2381 (p_actl_prem_id => p_rec.actl_prem_id,
2382 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
2383 p_effective_date => p_effective_date,
2384 p_object_version_number => p_rec.object_version_number);
2385 --
2386 chk_acty_ref_perd_cd
2387 (p_actl_prem_id => p_rec.actl_prem_id,
2388 p_acty_ref_perd_cd => p_rec.acty_ref_perd_cd,
2389 p_effective_date => p_effective_date,
2390 p_object_version_number => p_rec.object_version_number);
2391 --
2392 chk_lookups(
2393 p_actl_prem_id => p_rec.actl_prem_id,
2394 p_prem_asnmt_cd => p_rec.prem_asnmt_cd,
2395 p_prem_asnmt_lvl_cd => p_rec.prem_asnmt_lvl_cd,
2396 p_actl_prem_typ_cd => p_rec.actl_prem_typ_cd,
2397 p_prem_pyr_cd => p_rec.prem_pyr_cd,
2398 p_prsptv_r_rtsptv_cd => p_rec.prsptv_r_rtsptv_cd,
2399 p_effective_date => p_effective_date,
2400 p_object_version_number => p_rec.object_version_number
2401 );
2402 --
2403 chk_rules(
2404 p_actl_prem_id => p_rec.actl_prem_id,
2405 p_business_group_id => p_rec.business_group_id,
2406 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl,
2407 p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl,
2408 p_prtl_mo_det_mthd_rl => p_rec.prtl_mo_det_mthd_rl,
2409 p_effective_date => p_effective_date,
2410 p_object_version_number => p_rec.object_version_number
2411 );
2412 --
2413 chk_name
2414 (p_actl_prem_id => p_rec.actl_prem_id,
2415 p_name => p_rec.name,
2416 p_effective_date => p_effective_date,
2417 p_validation_start_date => p_validation_start_date,
2418 p_validation_end_date => p_validation_end_date,
2419 p_business_group_id => p_rec.business_group_id,
2420 p_object_version_number => p_rec.object_version_number);
2421 --
2422 chk_mlt_cd_dependencies
2423 (p_mlt_cd => p_rec.mlt_cd,
2424 p_val => p_rec.val,
2425 p_pl_id => p_rec.pl_id,
2426 p_oipl_id => p_rec.oipl_id,
2430 p_business_group_id => p_rec.business_group_id,
2427 p_rt_typ_cd => p_rec.rt_typ_cd,
2428 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
2429 p_actl_prem_id => p_rec.actl_prem_id,
2431 p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
2432 p_val_calc_rl => p_rec.val_calc_rl,
2433 p_effective_date => p_effective_date,
2434 p_object_version_number => p_rec.object_version_number
2435 );
2436 --
2437 chk_code_rule_num
2438 (p_code => p_rec.prtl_mo_det_mthd_cd,
2439 p_num => p_rec.wsh_rl_dy_mo_num,
2440 p_rule => p_rec.prtl_mo_det_mthd_rl);
2441 --
2442 chk_asgnmt_cd_lvl_mlt_pyr
2443 (p_prem_asnmt_cd => p_rec.prem_asnmt_cd,
2444 p_prem_asnmt_lvl_cd => p_rec.prem_asnmt_lvl_cd,
2445 p_mlt_cd => p_rec.mlt_cd,
2446 p_cr_lkbk_val => p_rec.cr_lkbk_val,
2447 p_prem_pyr_cd => p_rec.prem_pyr_cd);
2448 --
2449 chk_lwr_lmt_rl_val
2450 (p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl,
2451 p_upr_lmt_val => p_rec.upr_lmt_val,
2452 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl,
2453 p_lwr_lmt_val => p_rec.lwr_lmt_val);
2454 --
2455 chk_lwr_lmt_upr_lmt
2456 (p_lwr_lmt_val => p_rec.lwr_lmt_val,
2457 p_upr_lmt_val => p_rec.upr_lmt_val);
2458 --
2459 chk_asg_vrbl_prfl_mlt_tmt
2460 (p_actl_prem_id => p_rec.actl_prem_id,
2461 p_prem_asnmt_cd => p_rec.prem_asnmt_cd,
2462 p_effective_date => p_effective_date,
2463 p_business_group_id => p_rec.business_group_id);
2464 --
2465 hr_utility.set_location(' Leaving:'||l_proc, 10);
2466 End insert_validate;
2467 --
2468 -- ----------------------------------------------------------------------------
2469 -- |---------------------------< update_validate >----------------------------|
2470 -- ----------------------------------------------------------------------------
2471 Procedure update_validate
2472 (p_rec in ben_apr_shd.g_rec_type,
2473 p_effective_date in date,
2474 p_datetrack_mode in varchar2,
2475 p_validation_start_date in date,
2476 p_validation_end_date in date) is
2477 --
2478 l_proc varchar2(72) := g_package||'update_validate';
2479 --
2480 Begin
2481 hr_utility.set_location('Entering:'||l_proc, 5);
2482 --
2483 -- Call all supporting business operations
2484 --
2485 --
2486 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2487 --
2488 chk_actl_prem_id
2489 (p_actl_prem_id => p_rec.actl_prem_id,
2490 p_effective_date => p_effective_date,
2491 p_object_version_number => p_rec.object_version_number);
2492 --
2493 chk_organization_id
2494 (p_actl_prem_id => p_rec.actl_prem_id,
2495 p_organization_id => p_rec.organization_id,
2496 p_pl_id => p_rec.pl_id,
2497 p_oipl_id => p_rec.oipl_id,
2498 p_effective_date => p_effective_date,
2499 p_business_group_id => p_rec.business_group_id,
2500 p_object_version_number => p_rec.object_version_number);
2501 --
2502 chk_rndg_rl
2503 (p_actl_prem_id => p_rec.actl_prem_id,
2504 p_rndg_rl => p_rec.rndg_rl,
2505 p_business_group_id => p_rec.business_group_id,
2506 p_effective_date => p_effective_date,
2507 p_object_version_number => p_rec.object_version_number);
2508 --
2509 chk_val_calc_rl
2510 (p_actl_prem_id => p_rec.actl_prem_id,
2511 p_val_calc_rl => p_rec.val_calc_rl,
2512 p_business_group_id => p_rec.business_group_id,
2513 p_effective_date => p_effective_date,
2514 p_object_version_number => p_rec.object_version_number);
2515 --
2516 chk_vrbl_rt_add_on_calc_rl
2517 (p_actl_prem_id => p_rec.actl_prem_id,
2518 p_vrbl_rt_add_on_calc_rl => p_rec.vrbl_rt_add_on_calc_rl,
2519 p_business_group_id => p_rec.business_group_id,
2520 p_effective_date => p_effective_date,
2521 p_object_version_number => p_rec.object_version_number);
2522 --
2523 chk_rndg_cd
2524 (p_actl_prem_id => p_rec.actl_prem_id,
2525 p_rndg_cd => p_rec.rndg_cd,
2526 p_effective_date => p_effective_date,
2527 p_object_version_number => p_rec.object_version_number);
2528 --
2529 chk_prdct_cd
2530 (p_actl_prem_id => p_rec.actl_prem_id,
2531 p_prdct_cd => p_rec.prdct_cd,
2532 p_effective_date => p_effective_date,
2533 p_object_version_number => p_rec.object_version_number);
2534 --
2535 chk_mlt_cd
2536 (p_actl_prem_id => p_rec.actl_prem_id,
2537 p_mlt_cd => p_rec.mlt_cd,
2538 p_effective_date => p_effective_date,
2539 p_object_version_number => p_rec.object_version_number);
2540 --
2541 chk_prtl_mo_det_mthd_cd
2542 (p_actl_prem_id => p_rec.actl_prem_id,
2543 p_prtl_mo_det_mthd_cd => p_rec.prtl_mo_det_mthd_cd,
2544 p_effective_date => p_effective_date,
2545 p_object_version_number => p_rec.object_version_number);
2546 --
2547 chk_rt_typ_cd
2548 (p_actl_prem_id => p_rec.actl_prem_id,
2549 p_rt_typ_cd => p_rec.rt_typ_cd,
2553 chk_bnft_rt_typ_cd
2550 p_effective_date => p_effective_date,
2551 p_object_version_number => p_rec.object_version_number);
2552 --
2554 (p_actl_prem_id => p_rec.actl_prem_id,
2555 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
2556 p_effective_date => p_effective_date,
2557 p_object_version_number => p_rec.object_version_number);
2558 --
2559 chk_acty_ref_perd_cd
2560 (p_actl_prem_id => p_rec.actl_prem_id,
2561 p_acty_ref_perd_cd => p_rec.acty_ref_perd_cd,
2562 p_effective_date => p_effective_date,
2563 p_object_version_number => p_rec.object_version_number);
2564 --
2565 chk_lookups(
2566 p_actl_prem_id => p_rec.actl_prem_id,
2567 p_prem_asnmt_cd => p_rec.prem_asnmt_cd,
2568 p_prem_asnmt_lvl_cd => p_rec.prem_asnmt_lvl_cd,
2569 p_actl_prem_typ_cd => p_rec.actl_prem_typ_cd,
2570 p_prem_pyr_cd => p_rec.prem_pyr_cd,
2571 p_prsptv_r_rtsptv_cd => p_rec.prsptv_r_rtsptv_cd,
2572 p_effective_date => p_effective_date,
2573 p_object_version_number => p_rec.object_version_number
2574 );
2575 --
2576 chk_rules(
2577 p_actl_prem_id => p_rec.actl_prem_id,
2578 p_business_group_id => p_rec.business_group_id,
2579 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl,
2580 p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl,
2581 p_prtl_mo_det_mthd_rl => p_rec.prtl_mo_det_mthd_rl,
2582 p_effective_date => p_effective_date,
2583 p_object_version_number => p_rec.object_version_number
2584 );
2585 --
2586 chk_name
2587 (p_actl_prem_id => p_rec.actl_prem_id,
2588 p_name => p_rec.name,
2589 p_effective_date => p_effective_date,
2590 p_validation_start_date => p_validation_start_date,
2591 p_validation_end_date => p_validation_end_date,
2592 p_business_group_id => p_rec.business_group_id,
2593 p_object_version_number => p_rec.object_version_number);
2594 --
2595 -- chk_mlt_cd_rt_typ_cd
2596 -- (p_actl_prem_id => p_rec.actl_prem_id,
2597 -- p_mlt_cd => p_rec.mlt_cd,
2598 -- p_rt_typ_cd => p_rec.rt_typ_cd,
2599 -- p_effective_date => p_effective_date,
2600 -- p_object_version_number => p_rec.object_version_number);
2601 --
2602 chk_code_rule_num
2603 (p_code => p_rec.prtl_mo_det_mthd_cd,
2604 p_num => p_rec.wsh_rl_dy_mo_num,
2605 p_rule => p_rec.prtl_mo_det_mthd_rl);
2606 --
2607 chk_asgnmt_cd_lvl_mlt_pyr
2608 (p_prem_asnmt_cd => p_rec.prem_asnmt_cd,
2609 p_prem_asnmt_lvl_cd => p_rec.prem_asnmt_lvl_cd,
2610 p_mlt_cd => p_rec.mlt_cd,
2611 p_cr_lkbk_val => p_rec.cr_lkbk_val,
2612 p_prem_pyr_cd => p_rec.prem_pyr_cd);
2613 --
2614 chk_lwr_lmt_rl_val
2615 (p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl,
2616 p_upr_lmt_val => p_rec.upr_lmt_val,
2617 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl,
2618 p_lwr_lmt_val => p_rec.lwr_lmt_val);
2619 --
2620 chk_lwr_lmt_upr_lmt
2621 (p_lwr_lmt_val => p_rec.lwr_lmt_val,
2622 p_upr_lmt_val => p_rec.upr_lmt_val);
2623 --
2624 chk_asg_vrbl_prfl_mlt_tmt
2625 (p_actl_prem_id => p_rec.actl_prem_id,
2626 p_prem_asnmt_cd => p_rec.prem_asnmt_cd,
2627 p_effective_date => p_effective_date,
2628 p_business_group_id => p_rec.business_group_id);
2629 --
2630 -- Call the datetrack update integrity operation
2631 --
2632 dt_update_validate
2633 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
2634 p_datetrack_mode => p_datetrack_mode,
2635 p_validation_start_date => p_validation_start_date,
2636 p_validation_end_date => p_validation_end_date);
2637 --
2638 chk_mlt_cd_dependencies
2639 (p_mlt_cd => p_rec.mlt_cd,
2640 p_val => p_rec.val,
2641 p_pl_id => p_rec.pl_id,
2642 p_oipl_id => p_rec.oipl_id,
2643 p_rt_typ_cd => p_rec.rt_typ_cd,
2644 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
2645 p_actl_prem_id => p_rec.actl_prem_id,
2646 p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
2647 p_business_group_id => p_rec.business_group_id,
2648 p_val_calc_rl => p_rec.val_calc_rl,
2649 p_effective_date => p_effective_date,
2650 p_object_version_number => p_rec.object_version_number
2651 );
2652 --
2653 chk_child_data
2654 (p_actl_prem_id =>p_rec.actl_prem_id,
2655 p_business_group_id => p_rec.business_group_id,
2656 p_prem_asnmt_cd => p_rec.prem_asnmt_cd,
2657 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id);
2658 --
2659 hr_utility.set_location(' Leaving:'||l_proc, 10);
2660 End update_validate;
2661 --
2662 -- ----------------------------------------------------------------------------
2663 -- |---------------------------< delete_validate >----------------------------|
2664 -- ----------------------------------------------------------------------------
2665 Procedure delete_validate
2666 (p_rec in ben_apr_shd.g_rec_type,
2670 p_validation_end_date in date) is
2667 p_effective_date in date,
2668 p_datetrack_mode in varchar2,
2669 p_validation_start_date in date,
2671 --
2672 l_proc varchar2(72) := g_package||'delete_validate';
2673 --
2674 Begin
2675 hr_utility.set_location('Entering:'||l_proc, 5);
2676 --
2677 -- Call all supporting business operations
2678 --
2679 dt_delete_validate
2680 (p_datetrack_mode => p_datetrack_mode,
2681 p_validation_start_date => p_validation_start_date,
2682 p_validation_end_date => p_validation_end_date,
2683 p_actl_prem_id => p_rec.actl_prem_id);
2684 --
2685 hr_utility.set_location(' Leaving:'||l_proc, 10);
2686 End delete_validate;
2687 --
2688 --
2689 -- ---------------------------------------------------------------------------
2690 -- |---------------------< return_legislation_code >-------------------------|
2691 -- ---------------------------------------------------------------------------
2692 --
2693 function return_legislation_code
2694 (p_actl_prem_id in number) return varchar2 is
2695 --
2696 -- Declare cursor
2697 --
2698 cursor csr_leg_code is
2699 select a.legislation_code
2700 from per_business_groups a,
2701 ben_actl_prem_f b
2702 where b.actl_prem_id = p_actl_prem_id
2703 and a.business_group_id = b.business_group_id;
2704 --
2705 -- Declare local variables
2706 --
2707 l_legislation_code per_business_groups.legislation_code%TYPE; -- UTF8 varchar2(150);
2708 l_proc varchar2(72) := g_package||'return_legislation_code';
2709 --
2710 begin
2711 --
2712 hr_utility.set_location('Entering:'|| l_proc, 10);
2713 --
2714 -- Ensure that all the mandatory parameter are not null
2715 --
2716 hr_api.mandatory_arg_error(p_api_name => l_proc,
2717 p_argument => 'actl_prem_id',
2718 p_argument_value => p_actl_prem_id);
2719 --
2720 open csr_leg_code;
2721 --
2722 fetch csr_leg_code into l_legislation_code;
2723 --
2724 if csr_leg_code%notfound then
2725 --
2726 close csr_leg_code;
2727 --
2728 -- The primary key is invalid therefore we must error
2729 --
2730 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
2731 fnd_message.raise_error;
2732 --
2733 end if;
2734 --
2735 close csr_leg_code;
2736 --
2737 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2738 --
2739 return l_legislation_code;
2740 --
2741 end return_legislation_code;
2742 --
2743 end ben_apr_bus;