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