[Home] [Help]
PACKAGE BODY: APPS.BEN_BPR_BUS
Source
1 Package Body ben_bpr_bus as
2 /* $Header: bebprrhi.pkb 120.1 2008/01/10 09:55:47 rtagarra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_bpr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_bnft_pool_rlovr_rqmt_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_pool_rlovr_rqmt_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_pool_rlovr_rqmt_id(p_bnft_pool_rlovr_rqmt_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_pool_rlovr_rqmt_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_bpr_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_bnft_pool_rlovr_rqmt_id,hr_api.g_number)
55 <> ben_bpr_shd.g_old_rec.bnft_pool_rlovr_rqmt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_bpr_shd.constraint_error('BEN_BNFT_POOL_RLOVR_RQMT_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_bnft_pool_rlovr_rqmt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_bpr_shd.constraint_error('BEN_BNFT_POOL_RLOVR_RQMT_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_bnft_pool_rlovr_rqmt_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |----------------------< chk_mutual_exlsv_mn_val_flg >--------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that the mutual exclusive fields
85 -- no_mn_rlovr_val_dfnd_flag and mn_rlovr_val
86 -- are set correctly.
87 --
88 -- Pre Conditions
89 -- None.
90 --
91 -- In Parameters
92 -- p_bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
93 -- p_no_mn_rlovr_val_dfnd_flag Flag
94 -- p_mn_rlovr_val Number.
95 -- p_effective_date Session date of record.
96 -- p_object_version_number Object version number of record being
97 -- inserted or updated.
98 --
99 -- Post Success
100 -- Processing continues
101 --
102 -- Post Failure
103 -- Errors handled by the procedure
104 --
105 -- Access Status
106 -- Internal table handler use only.
107 --
108 Procedure chk_mutual_exlsv_mn_val_flg(p_bnft_pool_rlovr_rqmt_id in number,
109 p_no_mn_rlovr_val_dfnd_flag in varchar2,
110 p_mn_rlovr_val in number,
111 p_effective_date in date,
112 p_object_version_number in number) is
113 --
114 l_proc varchar2(72) := g_package||'chk_mutual_exlsv_mn_val_flg';
115 l_api_updating boolean;
116 --
117 Begin
118 --
119 hr_utility.set_location('Entering:'||l_proc, 5);
120 --
121 l_api_updating := ben_bpr_shd.api_updating
122 (p_effective_date => p_effective_date,
123 p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
124 p_object_version_number => p_object_version_number);
125 --
126 if (p_no_mn_rlovr_val_dfnd_flag = 'Y' and
127 p_mn_rlovr_val is not null) then
128 --
129 -- OK fields are not mutaully exclusive so raise an error
130 --
131 fnd_message.set_name('BEN','BEN_91714_MUT_EXLSV_MN_VAL_FLG');
132 fnd_message.raise_error;
133 --
134 end if;
135 --
136 hr_utility.set_location('Leaving:'||l_proc, 10);
137 --
138 End chk_mutual_exlsv_mn_val_flg;
139 --
140 -- ----------------------------------------------------------------------------
141 -- |----------------------< chk_mutual_exlsv_mx_val_flg >--------------------|
142 -- ----------------------------------------------------------------------------
143 --
144 -- Description
145 -- This procedure is used to check that the mutual exclusive fields
146 -- no_mx_rlovr_val_dfnd_flag and mx_rlovr_val
147 -- are set correctly.
148 --
149 -- Pre Conditions
150 -- None.
151 --
152 -- In Parameters
153 -- p_bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
154 -- p_no_mx_rlovr_val_dfnd_flag Flag
155 -- p_mx_rlovr_val Number.
156 -- p_effective_date Session date of record.
157 -- p_object_version_number Object version number of record being
158 -- inserted or updated.
159 --
160 -- Post Success
161 -- Processing continues
162 --
163 -- Post Failure
164 -- Errors handled by the procedure
165 --
166 -- Access Status
167 -- Internal table handler use only.
168 --
169 Procedure chk_mutual_exlsv_mx_val_flg(p_bnft_pool_rlovr_rqmt_id in number,
170 p_no_mx_rlovr_val_dfnd_flag in varchar2,
171 p_mx_rlovr_val in number,
172 p_effective_date in date,
173 p_object_version_number in number) is
174 --
175 l_proc varchar2(72) := g_package||'chk_mutual_exlsv_mx_val_flg';
176 l_api_updating boolean;
177 --
178 Begin
179 --
180 hr_utility.set_location('Entering:'||l_proc, 5);
181 --
182 l_api_updating := ben_bpr_shd.api_updating
183 (p_effective_date => p_effective_date,
184 p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
185 p_object_version_number => p_object_version_number);
186 --
187 if (p_no_mx_rlovr_val_dfnd_flag = 'Y' and
188 p_mx_rlovr_val is not null) then
189 --
190 -- OK fields are not mutaully exclusive so raise an error
191 --
192 fnd_message.set_name('BEN','BEN_91715_MUT_EXLSV_MX_VAL_FLG');
193 fnd_message.raise_error;
194 --
195 end if;
196 --
197 hr_utility.set_location('Leaving:'||l_proc, 10);
198 --
199 End chk_mutual_exlsv_mx_val_flg;
200 --
201 -- ----------------------------------------------------------------------------
202 -- |----------------------< chk_mutual_exlsv_mn_pct_flg >--------------------|
203 -- ----------------------------------------------------------------------------
204 --
205 -- Description
206 -- This procedure is used to check that the mutual exclusive fields
207 -- no_mn_rlovr_pct_dfnd_flag and mn_rlovr_pct_num
208 -- are set correctly.
209 --
210 -- Pre Conditions
211 -- None.
212 --
213 -- In Parameters
214 -- p_bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
215 -- p_no_mn_rlovr_pct_dfnd_flag Flag
216 -- p_mn_rlovr_pct_num Number.
217 -- p_effective_date Session date of record.
218 -- p_object_version_number Object version number of record being
219 -- inserted or updated.
220 --
221 -- Post Success
222 -- Processing continues
223 --
224 -- Post Failure
225 -- Errors handled by the procedure
226 --
227 -- Access Status
228 -- Internal table handler use only.
229 --
230 Procedure chk_mutual_exlsv_mn_pct_flg(p_bnft_pool_rlovr_rqmt_id in number,
231 p_no_mn_rlovr_pct_dfnd_flag in varchar2,
232 p_mn_rlovr_pct_num in number,
233 p_effective_date in date,
234 p_object_version_number in number) is
235 --
236 l_proc varchar2(72) := g_package||'chk_mutual_exlsv_mn_pct_flg';
237 l_api_updating boolean;
238 --
239 Begin
240 --
241 hr_utility.set_location('Entering:'||l_proc, 5);
242 --
243 l_api_updating := ben_bpr_shd.api_updating
244 (p_effective_date => p_effective_date,
245 p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
246 p_object_version_number => p_object_version_number);
247 --
248 if (p_no_mn_rlovr_pct_dfnd_flag = 'Y' and
249 p_mn_rlovr_pct_num is not null) then
250 --
251 -- OK fields are not mutaully exclusive so raise an error
252 --
253 fnd_message.set_name('BEN','BEN_91716_MUT_EXLSV_MN_PCT_FLG');
254 fnd_message.raise_error;
255 --
256 end if;
257 --
258 hr_utility.set_location('Leaving:'||l_proc, 10);
259 --
260 End chk_mutual_exlsv_mn_pct_flg;
261 --
262 -- ----------------------------------------------------------------------------
263 -- |----------------------< chk_mutual_exlsv_mx_pct_flg >--------------------|
264 -- ----------------------------------------------------------------------------
265 --
266 -- Description
267 -- This procedure is used to check that the mutual exclusive fields
268 -- no_mx_rlovr_pct_dfnd_flag and mx_rlovr_pct_num
269 -- are set correctly.
270 --
271 -- Pre Conditions
272 -- None.
273 --
274 -- In Parameters
275 -- p_bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
276 -- p_no_mx_rlovr_pct_dfnd_flag Flag
277 -- p_mx_rlovr_pct_num Number.
278 -- p_effective_date Session date of record.
279 -- p_object_version_number Object version number of record being
280 -- inserted or updated.
281 --
282 -- Post Success
283 -- Processing continues
284 --
285 -- Post Failure
286 -- Errors handled by the procedure
287 --
288 -- Access Status
289 -- Internal table handler use only.
290 --
291 Procedure chk_mutual_exlsv_mx_pct_flg(p_bnft_pool_rlovr_rqmt_id in number,
292 p_no_mx_rlovr_pct_dfnd_flag in varchar2,
293 p_mx_rlovr_pct_num in number,
294 p_effective_date in date,
295 p_object_version_number in number) is
296 --
297 l_proc varchar2(72) := g_package||'chk_mutual_exlsv_mn_pct_flg';
298 l_api_updating boolean;
299 --
300 Begin
301 --
302 hr_utility.set_location('Entering:'||l_proc, 5);
303 --
304 l_api_updating := ben_bpr_shd.api_updating
305 (p_effective_date => p_effective_date,
306 p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
307 p_object_version_number => p_object_version_number);
308 --
309 if (p_no_mx_rlovr_pct_dfnd_flag = 'Y' and
310 p_mx_rlovr_pct_num is not null) then
311 --
312 -- OK fields are not mutaully exclusive so raise an error
313 --
314 fnd_message.set_name('BEN','BEN_91717_MUT_EXLSV_MX_PCT_FLG');
315 fnd_message.raise_error;
316 --
317 end if;
318 --
319 hr_utility.set_location('Leaving:'||l_proc, 10);
320 --
321 End chk_mutual_exlsv_mx_pct_flg;
322 --
323 -- ----------------------------------------------------------------------------
324 -- |------< chk_pct_rndg_cd_rl_dpndcy >------|
325 -- ----------------------------------------------------------------------------
326 --
327 -- Description
328 -- This procedure is used to check the code/rule dependency as following:
329 -- If code = 'Rule' then rule must be selected.
330 -- If code <> 'Rule' then rule must not be selected.
331 --
332 -- Pre Conditions
333 -- None.
334 --
335 -- In Parameters
336 -- p_bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
337 -- p_pct_rndg_cd lookup code.
338 -- p_pct_rndg_rl Rule
339 -- p_effective_date effective date
340 -- p_object_version_number Object version number of record being
341 -- inserted or updated.
342 --
343 -- Post Success
344 -- Processing continues
345 --
346 -- Post Failure
347 -- Error handled by procedure
348 --
349 -- Access Status
350 -- Internal table handler use only.
351 --
352 Procedure chk_pct_rndg_cd_rl_dpndcy
353 (p_bnft_pool_rlovr_rqmt_id in number,
354 p_pct_rndg_cd in varchar2,
355 p_pct_rndg_rl in number,
356 p_effective_date in date,
357 p_object_version_number in number) is
358 --
359 l_proc varchar2(72) := g_package||'chk_pct_rndg_cd_rl_dpndcy ';
360 l_api_updating boolean;
361 --
362 Begin
363 --
364 hr_utility.set_location('Entering:'||l_proc, 5);
365 --
366 l_api_updating := ben_bpr_shd.api_updating
367 (p_effective_date => p_effective_date,
368 p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
369 p_object_version_number => p_object_version_number);
370 --
371 if (l_api_updating
372 and
373 (nvl(p_pct_rndg_cd,hr_api.g_varchar2)
374 <> nvl(ben_bpr_shd.g_old_rec.pct_rndg_cd,hr_api.g_varchar2) or
375 nvl(p_pct_rndg_rl,hr_api.g_number)
376 <> nvl(ben_bpr_shd.g_old_rec.pct_rndg_rl,hr_api.g_number))
377 or not l_api_updating) then
378 --
379 if (p_pct_rndg_cd = 'RL' and p_pct_rndg_rl is null) then
380 --
381 fnd_message.set_name('BEN','BEN_91019_RULE_REQUIRED');
382 fnd_message.raise_error;
383 --
384 end if;
385 --
386 if nvl(p_pct_rndg_cd,hr_api.g_varchar2) <> 'RL' and p_pct_rndg_rl is not null then
387 --
388 fnd_message.set_name('BEN','BEN_91713_CODE_NOT_RULE');
389 fnd_message.raise_error;
390 --
391 end if;
392 --
393 end if;
394 --
395 hr_utility.set_location('Leaving:'||l_proc,10);
396 --
397 end chk_pct_rndg_cd_rl_dpndcy;
398 --
399 -- ----------------------------------------------------------------------------
400 -- |------< chk_val_rndg_cd_rl_dpndcy >------|
401 -- ----------------------------------------------------------------------------
402 --
403 -- Description
404 -- This procedure is used to check the code/rule dependency as following:
405 -- If code = 'Rule' then rule must be selected.
406 -- If code <> 'Rule' then rule must not be selected.
407 --
408 -- Pre Conditions
409 -- None.
410 --
411 -- In Parameters
412 -- p_bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
413 -- p_val_rndg_cd lookup code.
414 -- p_val_rndg_rl Rule
415 -- p_effective_date effective date
416 -- p_object_version_number Object version number of record being
417 -- inserted or updated.
418 --
419 -- Post Success
420 -- Processing continues
421 --
422 -- Post Failure
423 -- Error handled by procedure
424 --
425 -- Access Status
426 -- Internal table handler use only.
427 --
428 Procedure chk_val_rndg_cd_rl_dpndcy
429 (p_bnft_pool_rlovr_rqmt_id in number,
430 p_val_rndg_cd in varchar2,
431 p_val_rndg_rl in number,
432 p_effective_date in date,
433 p_object_version_number in number) is
434 --
435 l_proc varchar2(72) := g_package||'chk_val_rndg_cd_rl_dpndcy ';
436 l_api_updating boolean;
437 --
438 Begin
439 --
440 hr_utility.set_location('Entering:'||l_proc, 5);
441 --
442 l_api_updating := ben_bpr_shd.api_updating
443 (p_effective_date => p_effective_date,
444 p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
445 p_object_version_number => p_object_version_number);
446 --
447 if (l_api_updating
448 and
449 (nvl(p_val_rndg_cd,hr_api.g_varchar2)
450 <> nvl(ben_bpr_shd.g_old_rec.val_rndg_cd,hr_api.g_varchar2) or
451 nvl(p_val_rndg_rl,hr_api.g_number)
452 <> nvl(ben_bpr_shd.g_old_rec.val_rndg_rl,hr_api.g_number))
453 or not l_api_updating) then
454 --
455 if (p_val_rndg_cd = 'RL' and p_val_rndg_rl is null) then
456 --
457 fnd_message.set_name('BEN','BEN_91019_RULE_REQUIRED');
458 fnd_message.raise_error;
459 --
460 end if;
461 --
462 if nvl(p_val_rndg_cd,hr_api.g_varchar2) <> 'RL' and p_val_rndg_rl is not null then
463 --
464 fnd_message.set_name('BEN','BEN_91713_CODE_NOT_RULE');
465 fnd_message.raise_error;
466 --
467 end if;
468 --
469 end if;
470 --
471 hr_utility.set_location('Leaving:'||l_proc,10);
472 --
473 end chk_val_rndg_cd_rl_dpndcy;
474 --
475 -- ----------------------------------------------------------------------------
476 -- |------< chk_pct_rndg_cd >------|
477 -- ----------------------------------------------------------------------------
478 --
479 -- Description
480 -- This procedure is used to check that the lookup value is valid.
481 --
482 -- Pre Conditions
483 -- None.
484 --
485 -- In Parameters
486 -- bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
487 -- pct_rndg_cd Value of lookup code.
488 -- effective_date effective date
489 -- object_version_number Object version number of record being
490 -- inserted or updated.
491 --
492 -- Post Success
493 -- Processing continues
494 --
495 -- Post Failure
496 -- Error handled by procedure
497 --
498 -- Access Status
499 -- Internal table handler use only.
500 --
501 Procedure chk_pct_rndg_cd(p_bnft_pool_rlovr_rqmt_id in number,
502 p_pct_rndg_cd in varchar2,
503 p_effective_date in date,
504 p_object_version_number in number) is
505 --
506 l_proc varchar2(72) := g_package||'chk_pct_rndg_cd';
507 l_api_updating boolean;
508 --
509 Begin
510 --
511 hr_utility.set_location('Entering:'||l_proc, 5);
512 --
513 l_api_updating := ben_bpr_shd.api_updating
514 (p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_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 p_pct_rndg_cd
520 <> nvl(ben_bpr_shd.g_old_rec.pct_rndg_cd,hr_api.g_varchar2)
521 or not l_api_updating)
522 and p_pct_rndg_cd is not null then
523 --
524 -- check if value of lookup falls within lookup type.
525 --
526 if hr_api.not_exists_in_hr_lookups
527 (p_lookup_type => 'BEN_RNDG',
528 p_lookup_code => p_pct_rndg_cd,
529 p_effective_date => p_effective_date) then
530 --
531 -- raise error as does not exist as lookup
532 --
533 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
534 fnd_message.set_token('FIELD','p_pct_rndg_cd');
535 fnd_message.set_token('TYPE','BEN_RNDG');
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_pct_rndg_cd;
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_pool_rlovr_rqmt_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_pool_rlovr_rqmt_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
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_bpr_shd.api_updating
602 (p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_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_bpr_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_pool_rlovr_rqmt_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_pool_rlovr_rqmt_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 --
675 Begin
676 --
677 hr_utility.set_location('Entering:'||l_proc, 5);
678 --
679 l_api_updating := ben_bpr_shd.api_updating
680 (p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_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_bpr_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_no_mx_rlovr_val_dfnd_flag >------|
714 -- ----------------------------------------------------------------------------
715 --
716 -- Description
717 -- This procedure is used to check that the lookup value is valid.
718 --
719 -- Pre Conditions
720 -- None.
721 --
722 -- In Parameters
723 -- bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
724 -- no_mx_rlovr_val_dfnd_flag Value of lookup code.
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_no_mx_rlovr_val_dfnd_flag(p_bnft_pool_rlovr_rqmt_id in number,
739 p_no_mx_rlovr_val_dfnd_flag in varchar2,
740 p_effective_date in date,
741 p_object_version_number in number) is
742 --
743 l_proc varchar2(72) := g_package||'chk_no_mx_rlovr_val_dfnd_flag';
744 l_api_updating boolean;
745 --
746 Begin
747 --
748 hr_utility.set_location('Entering:'||l_proc, 5);
749 --
750 l_api_updating := ben_bpr_shd.api_updating
751 (p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
752 p_effective_date => p_effective_date,
753 p_object_version_number => p_object_version_number);
754 --
755 if (l_api_updating
756 and p_no_mx_rlovr_val_dfnd_flag
757 <> nvl(ben_bpr_shd.g_old_rec.no_mx_rlovr_val_dfnd_flag,hr_api.g_varchar2)
758 or not l_api_updating) then
759 --
760 -- check if value of lookup falls within lookup type.
761 --
762 --
763 if hr_api.not_exists_in_hr_lookups
764 (p_lookup_type => 'YES_NO',
765 p_lookup_code => p_no_mx_rlovr_val_dfnd_flag,
766 p_effective_date => p_effective_date) then
767 --
768 -- raise error as does not exist as lookup
769 --
770 fnd_message.set_name('BEN','BEN_91006_INVALID_FLAG');
771 fnd_message.raise_error;
772 --
773 end if;
774 --
775 end if;
776 --
777 hr_utility.set_location('Leaving:'||l_proc,10);
778 --
779 end chk_no_mx_rlovr_val_dfnd_flag;
780
781 -- ----------------------------------------------------------------------------
782 -- |------< chk_rlovr_val_rl >------|
783 -- ----------------------------------------------------------------------------
784 --
785 -- Description
786 -- This procedure is used to check that the Formula Rule is valid.
787 --
788 -- Pre Conditions
789 -- None.
790 --
791 -- In Parameters
792 -- bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
793 -- rlovr_val_rl Value of formula rule id.
794 -- effective_date effective date
795 -- object_version_number Object version number of record being
796 -- inserted or updated.
797 --
798 -- Post Success
799 -- Processing continues
800 --
801 -- Post Failure
802 -- Error handled by procedure
803 --
804 -- Access Status
805 -- Internal table handler use only.
806 --
807 Procedure chk_rlovr_val_rl(p_bnft_pool_rlovr_rqmt_id in number,
808 p_rlovr_val_rl in number,
809 p_business_group_id in number,
810 p_effective_date in date,
811 p_object_version_number in number) is
812 --
813 l_proc varchar2(72) := g_package||'chk_rlovr_val_rl';
814 l_api_updating boolean;
815 l_dummy varchar2(1);
816 --
817 cursor c1 is
818 select null
819 from ff_formulas_f ff ,
820 per_business_groups pbg
821 where ff.formula_id = p_rlovr_val_rl
822 and ff.formula_type_id = -532 /* */
823 and pbg.business_group_id = p_business_group_id
824 and nvl(ff.business_group_id, p_business_group_id) =
825 p_business_group_id
826 and nvl(ff.legislation_code, pbg.legislation_code) =
827 pbg.legislation_code
828 and p_effective_date
829 between ff.effective_start_date
830 and ff.effective_end_date;
831 --
832 Begin
833 --
834 hr_utility.set_location('Entering:'||l_proc, 5);
835 --
836 l_api_updating := ben_bpr_shd.api_updating
837 (p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
838 p_effective_date => p_effective_date,
839 p_object_version_number => p_object_version_number);
840 --
841 if (l_api_updating
842 and nvl(p_rlovr_val_rl,hr_api.g_number)
843 <> ben_bpr_shd.g_old_rec.rlovr_val_rl
844 or not l_api_updating)
845 and p_rlovr_val_rl is not null then
846 --
847 -- check if value of formula rule is valid.
848 --
849 open c1;
850 --
851 -- fetch value from cursor if it returns a record then the
852 -- formula is valid otherwise its invalid
853 --
854 fetch c1 into l_dummy;
855 if c1%notfound then
856 --
857 close c1;
858 --
859 -- raise error
860 --
861 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
862 fnd_message.set_token('ID',p_rlovr_val_rl);
863 fnd_message.set_token('TYPE_ID',-532);
864 fnd_message.raise_error;
865 --
866 end if;
867 --
868 close c1;
869 --
870 end if;
871 --
872 hr_utility.set_location('Leaving:'||l_proc,10);
873 --
874 end chk_rlovr_val_rl;
875
876 --
877 -- ----------------------------------------------------------------------------
878 -- |------< chk_no_mn_rlovr_val_dfnd_flag >------|
879 -- ----------------------------------------------------------------------------
880 --
881 -- Description
882 -- This procedure is used to check that the lookup value is valid.
883 --
884 -- Pre Conditions
885 -- None.
886 --
887 -- In Parameters
888 -- bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
889 -- no_mn_rlovr_val_dfnd_flag Value of lookup code.
890 -- effective_date effective date
891 -- object_version_number Object version number of record being
892 -- inserted or updated.
893 --
894 -- Post Success
895 -- Processing continues
896 --
897 -- Post Failure
898 -- Error handled by procedure
899 --
900 -- Access Status
901 -- Internal table handler use only.
902 --
903 Procedure chk_no_mn_rlovr_val_dfnd_flag(p_bnft_pool_rlovr_rqmt_id in number,
904 p_no_mn_rlovr_val_dfnd_flag in varchar2,
905 p_effective_date in date,
906 p_object_version_number in number) is
907 --
908 l_proc varchar2(72) := g_package||'chk_no_mn_rlovr_val_dfnd_flag';
909 l_api_updating boolean;
910 --
911 Begin
912 --
913 hr_utility.set_location('Entering:'||l_proc, 5);
914 --
915 l_api_updating := ben_bpr_shd.api_updating
916 (p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
917 p_effective_date => p_effective_date,
918 p_object_version_number => p_object_version_number);
919 --
920 if (l_api_updating
921 and p_no_mn_rlovr_val_dfnd_flag
922 <> nvl(ben_bpr_shd.g_old_rec.no_mn_rlovr_val_dfnd_flag,hr_api.g_varchar2)
923 or not l_api_updating) then
924 --
925 -- check if value of lookup falls within lookup type.
926 --
927 --
928 if hr_api.not_exists_in_hr_lookups
929 (p_lookup_type => 'YES_NO',
930 p_lookup_code => p_no_mn_rlovr_val_dfnd_flag,
931 p_effective_date => p_effective_date) then
932 --
933 -- raise error as does not exist as lookup
934 --
935 fnd_message.set_name('BEN','BEN_91006_INVALID_FLAG');
936 fnd_message.raise_error;
937 --
938 end if;
939 --
940 end if;
941 --
942 hr_utility.set_location('Leaving:'||l_proc,10);
943 --
944 end chk_no_mn_rlovr_val_dfnd_flag;
945 --
946 -- ----------------------------------------------------------------------------
947 -- |------< chk_no_mx_rlovr_pct_dfnd_flag >------|
948 -- ----------------------------------------------------------------------------
949 --
950 -- Description
951 -- This procedure is used to check that the lookup value is valid.
952 --
953 -- Pre Conditions
954 -- None.
955 --
956 -- In Parameters
957 -- bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
958 -- no_mx_rlovr_pct_dfnd_flag Value of lookup code.
959 -- effective_date effective date
960 -- object_version_number Object version number of record being
961 -- inserted or updated.
962 --
963 -- Post Success
964 -- Processing continues
965 --
966 -- Post Failure
967 -- Error handled by procedure
968 --
969 -- Access Status
970 -- Internal table handler use only.
971 --
972 Procedure chk_no_mx_rlovr_pct_dfnd_flag(p_bnft_pool_rlovr_rqmt_id in number,
973 p_no_mx_rlovr_pct_dfnd_flag in varchar2,
974 p_effective_date in date,
975 p_object_version_number in number) is
976 --
977 l_proc varchar2(72) := g_package||'chk_no_mx_rlovr_pct_dfnd_flag';
978 l_api_updating boolean;
979 --
980 Begin
981 --
982 hr_utility.set_location('Entering:'||l_proc, 5);
983 --
984 l_api_updating := ben_bpr_shd.api_updating
985 (p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
986 p_effective_date => p_effective_date,
987 p_object_version_number => p_object_version_number);
988 --
989 if (l_api_updating
990 and p_no_mx_rlovr_pct_dfnd_flag
991 <> nvl(ben_bpr_shd.g_old_rec.no_mx_rlovr_pct_dfnd_flag,hr_api.g_varchar2)
992 or not l_api_updating) then
993 --
994 -- check if value of lookup falls within lookup type.
995 --
996 --
997 if hr_api.not_exists_in_hr_lookups
998 (p_lookup_type => 'YES_NO',
999 p_lookup_code => p_no_mx_rlovr_pct_dfnd_flag,
1000 p_effective_date => p_effective_date) then
1001 --
1002 -- raise error as does not exist as lookup
1003 --
1004 fnd_message.set_name('BEN','BEN_91006_INVALID_FLAG');
1005 fnd_message.raise_error;
1006 --
1007 end if;
1008 --
1009 end if;
1010 --
1011 hr_utility.set_location('Leaving:'||l_proc,10);
1012 --
1013 end chk_no_mx_rlovr_pct_dfnd_flag;
1014 --
1015 -- ----------------------------------------------------------------------------
1016 -- |------< chk_no_mn_rlovr_pct_dfnd_flag >------|
1017 -- ----------------------------------------------------------------------------
1018 --
1019 -- Description
1020 -- This procedure is used to check that the lookup value is valid.
1021 --
1022 -- Pre Conditions
1023 -- None.
1024 --
1025 -- In Parameters
1026 -- bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
1027 -- no_mn_rlovr_pct_dfnd_flag Value of lookup code.
1028 -- effective_date effective date
1029 -- object_version_number Object version number of record being
1030 -- inserted or updated.
1031 --
1032 -- Post Success
1033 -- Processing continues
1034 --
1035 -- Post Failure
1036 -- Error handled by procedure
1037 --
1038 -- Access Status
1039 -- Internal table handler use only.
1040 --
1041 Procedure chk_no_mn_rlovr_pct_dfnd_flag(p_bnft_pool_rlovr_rqmt_id in number,
1042 p_no_mn_rlovr_pct_dfnd_flag in varchar2,
1043 p_effective_date in date,
1044 p_object_version_number in number) is
1045 --
1046 l_proc varchar2(72) := g_package||'chk_no_mn_rlovr_pct_dfnd_flag';
1047 l_api_updating boolean;
1048 --
1049 Begin
1050 --
1051 hr_utility.set_location('Entering:'||l_proc, 5);
1052 --
1053 l_api_updating := ben_bpr_shd.api_updating
1054 (p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
1055 p_effective_date => p_effective_date,
1056 p_object_version_number => p_object_version_number);
1057 --
1058 if (l_api_updating
1059 and p_no_mn_rlovr_pct_dfnd_flag
1060 <> nvl(ben_bpr_shd.g_old_rec.no_mn_rlovr_pct_dfnd_flag,hr_api.g_varchar2)
1061 or not l_api_updating) then
1062 --
1063 -- check if value of lookup falls within lookup type.
1064 --
1065 --
1066 if hr_api.not_exists_in_hr_lookups
1067 (p_lookup_type => 'YES_NO',
1068 p_lookup_code => p_no_mn_rlovr_pct_dfnd_flag,
1069 p_effective_date => p_effective_date) then
1070 --
1071 -- raise error as does not exist as lookup
1072 --
1073 fnd_message.set_name('BEN','BEN_91006_INVALID_FLAG');
1074 fnd_message.raise_error;
1075 --
1076 end if;
1077 --
1078 end if;
1079 --
1080 hr_utility.set_location('Leaving:'||l_proc,10);
1081 --
1082 end chk_no_mn_rlovr_pct_dfnd_flag;
1083 --
1084 -- ----------------------------------------------------------------------------
1085 -- |------< chk_crs_rlovr_procg_cd >----------------------------------|
1086 -- ----------------------------------------------------------------------------
1087 --
1088 -- Description
1089 -- This procedure is used to check that the lookup value is valid.
1090 --
1091 -- Pre Conditions
1092 -- None.
1093 --
1094 -- In Parameters
1095 -- bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
1096 -- no_mn_rlovr_val_dfnd_flag Value of lookup code.
1097 -- effective_date effective date
1098 -- object_version_number Object version number of record being
1099 -- inserted or updated.
1100 --
1101 -- Post Success
1102 -- Processing continues
1103 --
1104 -- Post Failure
1105 -- Error handled by procedure
1106 --
1107 -- Access Status
1108 -- Internal table handler use only.
1109 --
1110 Procedure chk_crs_rlovr_procg_cd
1111 (p_bnft_pool_rlovr_rqmt_id in number,
1112 p_crs_rlovr_procg_cd 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_crs_rlovr_procg_cd';
1117 l_api_updating boolean;
1118 --
1119 Begin
1120 --
1121 hr_utility.set_location('Entering:'||l_proc, 5);
1122 --
1123 l_api_updating := ben_bpr_shd.api_updating
1124 (p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
1125 p_effective_date => p_effective_date,
1126 p_object_version_number => p_object_version_number);
1127 --
1128 hr_utility.set_location('p_crs_rlovr_procg_cd:'||p_crs_rlovr_procg_cd, 5);
1129 if (l_api_updating
1130 and p_crs_rlovr_procg_cd
1131 <> nvl(ben_bpr_shd.g_old_rec.crs_rlovr_procg_cd,hr_api.g_varchar2)
1132 or not l_api_updating)
1133 and p_crs_rlovr_procg_cd is not null then
1134 --
1135 -- check if value of lookup falls within lookup type.
1136 --
1137 --
1138 if hr_api.not_exists_in_hr_lookups
1139 (p_lookup_type => 'BEN_CRS_RLOVR_PROCG',
1140 p_lookup_code => p_crs_rlovr_procg_cd,
1141 p_effective_date => p_effective_date) then
1142 --
1143 -- raise error as does not exist as lookup
1144 --
1145 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1146 fnd_message.set_token('FIELD', 'p_crs_rlovr_procg_cd');
1147 fnd_message.set_token('TYPE','BEN_CRS_RLOVR_PROCG');
1148 fnd_message.raise_error;
1149 --
1150 end if;
1151 --
1152 end if;
1153 --
1154 hr_utility.set_location('Leaving:'||l_proc,10);
1155 --
1156 end chk_crs_rlovr_procg_cd;
1157 --
1158 -- ----------------------------------------------------------------------------
1159 -- |------< chk_pct_rndg_rl >------|
1160 -- ----------------------------------------------------------------------------
1161 --
1162 -- Description
1163 -- This procedure is used to check that the Formula Rule is valid.
1164 --
1165 -- Pre Conditions
1166 -- None.
1167 --
1168 -- In Parameters
1169 -- bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
1170 -- pct_rndg_rl Value of formula rule id.
1171 -- effective_date effective date
1172 -- object_version_number Object version number of record being
1173 -- inserted or updated.
1174 --
1175 -- Post Success
1176 -- Processing continues
1177 --
1178 -- Post Failure
1179 -- Error handled by procedure
1180 --
1181 -- Access Status
1182 -- Internal table handler use only.
1183 --
1184 Procedure chk_pct_rndg_rl(p_bnft_pool_rlovr_rqmt_id in number,
1185 p_pct_rndg_rl in number,
1186 p_business_group_id in number,
1187 p_effective_date in date,
1188 p_object_version_number in number) is
1189 --
1190 l_proc varchar2(72) := g_package||'chk_pct_rndg_rl';
1191 l_api_updating boolean;
1192 l_dummy varchar2(1);
1193 --
1194 cursor c1 is
1195 select null
1196 from ff_formulas_f ff ,
1197 per_business_groups pbg
1198 where ff.formula_id = p_pct_rndg_rl
1199 and ff.formula_type_id = -169 /*default enrollment det */
1200 and pbg.business_group_id = p_business_group_id
1201 and nvl(ff.business_group_id, p_business_group_id) =
1202 p_business_group_id
1203 and nvl(ff.legislation_code, pbg.legislation_code) =
1204 pbg.legislation_code
1205 and p_effective_date
1206 between ff.effective_start_date
1207 and ff.effective_end_date;
1208 --
1209 Begin
1210 --
1211 hr_utility.set_location('Entering:'||l_proc, 5);
1212 --
1213 l_api_updating := ben_bpr_shd.api_updating
1214 (p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
1215 p_effective_date => p_effective_date,
1216 p_object_version_number => p_object_version_number);
1217 --
1218 if (l_api_updating
1219 and nvl(p_pct_rndg_rl,hr_api.g_number)
1220 <> ben_bpr_shd.g_old_rec.pct_rndg_rl
1221 or not l_api_updating)
1222 and p_pct_rndg_rl is not null then
1223 --
1224 -- check if value of formula rule is valid.
1225 --
1226 open c1;
1227 --
1228 -- fetch value from cursor if it returns a record then the
1229 -- formula is valid otherwise its invalid
1230 --
1231 fetch c1 into l_dummy;
1232 if c1%notfound then
1233 --
1234 close c1;
1235 --
1236 -- raise error
1237 --
1238 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1239 fnd_message.set_token('ID',p_pct_rndg_rl);
1240 fnd_message.set_token('TYPE_ID',-169);
1241 fnd_message.raise_error;
1242 --
1243 end if;
1244 --
1245 close c1;
1246 --
1247 end if;
1248 --
1249 hr_utility.set_location('Leaving:'||l_proc,10);
1250 --
1251 end chk_pct_rndg_rl;
1252 --
1253 -- ----------------------------------------------------------------------------
1254 -- |------< chk_prtt_elig_rlovr_rl >------|
1255 -- ----------------------------------------------------------------------------
1256 --
1257 -- Description
1258 -- This procedure is used to check that the Formula Rule is valid.
1259 --
1260 -- Pre Conditions
1261 -- None.
1262 --
1263 -- In Parameters
1264 -- bnft_pool_rlovr_rqmt_id PK of record being inserted or updated.
1265 -- prtt_elig_rlovr_rl Value of formula rule id.
1266 -- effective_date effective date
1267 -- object_version_number Object version number of record being
1268 -- inserted or updated.
1269 --
1270 -- Post Success
1271 -- Processing continues
1272 --
1273 -- Post Failure
1274 -- Error handled by procedure
1275 --
1276 -- Access Status
1277 -- Internal table handler use only.
1278 --
1279 Procedure chk_prtt_elig_rlovr_rl(p_bnft_pool_rlovr_rqmt_id in number,
1280 p_prtt_elig_rlovr_rl in number,
1281 p_business_group_id in number,
1282 p_effective_date in date,
1283 p_object_version_number in number) is
1284 --
1285 l_proc varchar2(72) := g_package||'chk_prtt_elig_rlovr_rl';
1286 l_api_updating boolean;
1287 l_dummy varchar2(1);
1288 --
1289 cursor c1 is
1290 select null
1291 from ff_formulas_f ff
1292 ,per_business_groups pbg
1293 where ff.formula_id = p_prtt_elig_rlovr_rl
1294 and ff.formula_type_id = -294
1295 and pbg.business_group_id = p_business_group_id
1296 and nvl(ff.business_group_id, p_business_group_id) =
1297 p_business_group_id
1298 and nvl(ff.legislation_code, pbg.legislation_code) =
1299 pbg.legislation_code
1300 and p_effective_date
1301 between ff.effective_start_date
1302 and ff.effective_end_date;
1303 --
1304 Begin
1305 --
1306 hr_utility.set_location('Entering:'||l_proc, 5);
1307 --
1308 l_api_updating := ben_bpr_shd.api_updating
1309 (p_bnft_pool_rlovr_rqmt_id => p_bnft_pool_rlovr_rqmt_id,
1310 p_effective_date => p_effective_date,
1311 p_object_version_number => p_object_version_number);
1312 --
1313 if (l_api_updating
1314 and nvl(p_prtt_elig_rlovr_rl,hr_api.g_number)
1315 <> ben_bpr_shd.g_old_rec.prtt_elig_rlovr_rl
1316 or not l_api_updating)
1317 and p_prtt_elig_rlovr_rl is not null then
1318 --
1319 -- check if value of formula rule is valid.
1320 --
1321 open c1;
1322 --
1323 -- fetch value from cursor if it returns a record then the
1324 -- formula is valid otherwise its invalid
1325 --
1326 fetch c1 into l_dummy;
1327 if c1%notfound then
1328 --
1329 close c1;
1330 --
1331 -- raise error
1332 --
1333 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1334 fnd_message.set_token('ID',p_prtt_elig_rlovr_rl);
1335 fnd_message.set_token('TYPE_ID',-294);
1336 fnd_message.raise_error;
1337 --
1338 end if;
1339 --
1340 close c1;
1341 --
1342 end if;
1343 --
1344 hr_utility.set_location('Leaving:'||l_proc,10);
1345 --
1346 end chk_prtt_elig_rlovr_rl;
1347
1348
1349
1350
1351
1352 -- ----------------------------------------------------------------------------
1353 -- |--------------------------< chk_abr_dups >--------------------------|
1354 -- ----------------------------------------------------------------------------
1355 Procedure chk_abr_dups
1356 (p_acty_base_rt_id in number default hr_api.g_number,
1357 p_bnft_prvdr_pool_id in number default hr_api.g_number,
1358 p_bnft_pool_rlovr_rqmt_id in number default hr_api.g_number,
1359 p_effective_date in date
1360 ) Is
1361
1362 l_proc varchar2(72) := g_package||'chk_abr_dups';
1363
1364 cursor c_abp is
1365 select 'x'
1366 from ben_bnft_pool_rlovr_rqmt_f
1367 where bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
1368 and acty_base_rt_id = p_acty_base_rt_id
1369 and bnft_pool_rlovr_rqmt_id <> nvl(p_bnft_pool_rlovr_rqmt_id,-1)
1370 and p_effective_date between effective_start_date
1371 and effective_end_date ;
1372
1373 l_dummy_var varchar2(1) ;
1374
1375 begin
1376
1377 hr_utility.set_location('Entering:'||l_proc, 5);
1378 open c_abp ;
1379 fetch c_abp into l_dummy_var ;
1380 if c_abp%found then
1381 hr_utility.set_location('dupes:'||l_proc, 5);
1382 close c_abp ;
1383 fnd_message.set_name('BEN', 'BEN_92992_DUPS_ROW');
1384 fnd_message.set_token('VAR1', 'Rollover Requirements');
1385 fnd_message.set_token('VAR2','Benefit Pool');
1386 fnd_message.raise_error;
1387 end if ;
1388 close c_abp ;
1389
1390 hr_utility.set_location('Leaving:'||l_proc, 10);
1391
1392 end chk_abr_dups ;
1393 --
1394 -- ----------------------------------------------------------------------------
1395 -- |--------------------------< dt_update_validate >--------------------------|
1396 -- ----------------------------------------------------------------------------
1397 -- {Start Of Comments}
1398 --
1399 -- Description:
1400 -- This procedure is used for referential integrity of datetracked
1401 -- parent entities when a datetrack update operation is taking place
1402 -- and where there is no cascading of update defined for this entity.
1403 --
1404 -- Prerequisites:
1405 -- This procedure is called from the update_validate.
1406 --
1407 -- In Parameters:
1408 --
1409 -- Post Success:
1410 -- Processing continues.
1411 --
1412 -- Post Failure:
1413 --
1414 -- Developer Implementation Notes:
1415 -- This procedure should not need maintenance unless the HR Schema model
1416 -- changes.
1417 --
1418 -- Access Status:
1419 -- Internal Row Handler Use Only.
1420 --
1421 -- {End Of Comments}
1422 -- ----------------------------------------------------------------------------
1423 Procedure dt_update_validate
1424 (p_acty_base_rt_id in number default hr_api.g_number,
1425 p_bnft_prvdr_pool_id in number default hr_api.g_number,
1426 p_datetrack_mode in varchar2,
1427 p_validation_start_date in date,
1428 p_validation_end_date in date) Is
1429 --
1430 l_proc varchar2(72) := g_package||'dt_update_validate';
1431 l_integrity_error Exception;
1432 l_table_name all_tables.table_name%TYPE;
1433 --
1434 Begin
1435 hr_utility.set_location('Entering:'||l_proc, 5);
1436 --
1437 -- Ensure that the p_datetrack_mode argument is not null
1438 --
1439 hr_api.mandatory_arg_error
1440 (p_api_name => l_proc,
1441 p_argument => 'datetrack_mode',
1442 p_argument_value => p_datetrack_mode);
1443 --
1444 -- Only perform the validation if the datetrack update mode is valid
1445 --
1446 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
1447 --
1448 --
1449 -- Ensure the arguments are not null
1450 --
1451 hr_api.mandatory_arg_error
1452 (p_api_name => l_proc,
1453 p_argument => 'validation_start_date',
1454 p_argument_value => p_validation_start_date);
1455 --
1456 hr_api.mandatory_arg_error
1457 (p_api_name => l_proc,
1458 p_argument => 'validation_end_date',
1459 p_argument_value => p_validation_end_date);
1460 --
1461 If ((nvl(p_acty_base_rt_id, hr_api.g_number) <> hr_api.g_number) and
1462 NOT (dt_api.check_min_max_dates
1463 (p_base_table_name => 'ben_acty_base_rt_f',
1464 p_base_key_column => 'acty_base_rt_id',
1465 p_base_key_value => p_acty_base_rt_id,
1466 p_from_date => p_validation_start_date,
1467 p_to_date => p_validation_end_date))) Then
1468 l_table_name := 'ben_acty_base_rt_f';
1469 Raise l_integrity_error;
1470 End If;
1471 If ((nvl(p_bnft_prvdr_pool_id, hr_api.g_number) <> hr_api.g_number) and
1472 NOT (dt_api.check_min_max_dates
1473 (p_base_table_name => 'ben_bnft_prvdr_pool_f',
1474 p_base_key_column => 'bnft_prvdr_pool_id',
1475 p_base_key_value => p_bnft_prvdr_pool_id,
1479 Raise l_integrity_error;
1476 p_from_date => p_validation_start_date,
1477 p_to_date => p_validation_end_date))) Then
1478 l_table_name := 'ben_bnft_prvdr_pool_f';
1480 End If;
1481 --
1482 End If;
1483 --
1484 hr_utility.set_location(' Leaving:'||l_proc, 10);
1485 Exception
1486 When l_integrity_error Then
1487 --
1488 -- A referential integrity check was violated therefore
1489 -- we must error
1490 --
1491 ben_utility.parent_integrity_error(p_table_name => l_table_name);
1492 --
1493 When Others Then
1494 --
1495 -- An unhandled or unexpected error has occurred which
1496 -- we must report
1497 --
1498 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1499 fnd_message.set_token('PROCEDURE', l_proc);
1500 fnd_message.set_token('STEP','15');
1501 fnd_message.raise_error;
1502 End dt_update_validate;
1503 --
1504 -- ----------------------------------------------------------------------------
1505 -- |--------------------------< dt_delete_validate >--------------------------|
1506 -- ----------------------------------------------------------------------------
1507 -- {Start Of Comments}
1508 --
1509 -- Description:
1510 -- This procedure is used for referential integrity of datetracked
1511 -- child entities when either a datetrack DELETE or ZAP is in operation
1512 -- and where there is no cascading of delete defined for this entity.
1513 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1514 -- datetracked child rows exist between the validation start and end
1515 -- dates.
1516 --
1517 -- Prerequisites:
1518 -- This procedure is called from the delete_validate.
1519 --
1520 -- In Parameters:
1521 --
1522 -- Post Success:
1523 -- Processing continues.
1524 --
1525 -- Post Failure:
1526 -- If a row exists by determining the returning Boolean value from the
1527 -- generic dt_api.rows_exist function then we must supply an error via
1528 -- the use of the local exception handler l_rows_exist.
1529 --
1530 -- Developer Implementation Notes:
1531 -- This procedure should not need maintenance unless the HR Schema model
1532 -- changes.
1533 --
1534 -- Access Status:
1535 -- Internal Row Handler Use Only.
1536 --
1537 -- {End Of Comments}
1538 -- ----------------------------------------------------------------------------
1539 Procedure dt_delete_validate
1540 (p_bnft_pool_rlovr_rqmt_id in number,
1541 p_datetrack_mode in varchar2,
1542 p_validation_start_date in date,
1543 p_validation_end_date in date) Is
1544 --
1545 l_proc varchar2(72) := g_package||'dt_delete_validate';
1546 l_rows_exist Exception;
1547 l_table_name all_tables.table_name%TYPE;
1548 --
1549 Begin
1550 hr_utility.set_location('Entering:'||l_proc, 5);
1551 --
1552 -- Ensure that the p_datetrack_mode argument is not null
1553 --
1554 hr_api.mandatory_arg_error
1555 (p_api_name => l_proc,
1556 p_argument => 'datetrack_mode',
1557 p_argument_value => p_datetrack_mode);
1558 --
1559 -- Only perform the validation if the datetrack mode is either
1560 -- DELETE or ZAP
1561 --
1562 If (p_datetrack_mode = 'DELETE' or
1563 p_datetrack_mode = 'ZAP') then
1564 --
1565 --
1566 -- Ensure the arguments are not null
1567 --
1568 hr_api.mandatory_arg_error
1569 (p_api_name => l_proc,
1570 p_argument => 'validation_start_date',
1571 p_argument_value => p_validation_start_date);
1572 --
1573 hr_api.mandatory_arg_error
1574 (p_api_name => l_proc,
1575 p_argument => 'validation_end_date',
1576 p_argument_value => p_validation_end_date);
1577 --
1578 hr_api.mandatory_arg_error
1579 (p_api_name => l_proc,
1580 p_argument => 'bnft_pool_rlovr_rqmt_id',
1581 p_argument_value => p_bnft_pool_rlovr_rqmt_id);
1582 --
1583 --
1584 --
1585 End If;
1586 --
1587 hr_utility.set_location(' Leaving:'||l_proc, 10);
1588 Exception
1589 When l_rows_exist Then
1590 --
1591 -- A referential integrity check was violated therefore
1592 -- we must error
1593 --
1594 ben_utility.child_exists_error(p_table_name => l_table_name);
1595 --
1596 When Others Then
1597 --
1598 -- An unhandled or unexpected error has occurred which
1599 -- we must report
1600 --
1601 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1602 fnd_message.set_token('PROCEDURE', l_proc);
1603 fnd_message.set_token('STEP','15');
1604 fnd_message.raise_error;
1605 End dt_delete_validate;
1606 --
1607 -- ----------------------------------------------------------------------------
1608 -- |---------------------------< insert_validate >----------------------------|
1609 -- ----------------------------------------------------------------------------
1610 Procedure insert_validate
1611 (p_rec in ben_bpr_shd.g_rec_type,
1612 p_effective_date in date,
1613 p_datetrack_mode in varchar2,
1614 p_validation_start_date in date,
1615 p_validation_end_date in date) is
1616 --
1617 l_proc varchar2(72) := g_package||'insert_validate';
1618 --
1619 Begin
1620 hr_utility.set_location('Entering:'||l_proc, 5);
1621 --
1622 -- Call all supporting business operations
1623 --
1624 --
1625 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1626 --
1627 chk_bnft_pool_rlovr_rqmt_id
1628 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1632 chk_mutual_exlsv_mn_val_flg
1629 p_effective_date => p_effective_date,
1630 p_object_version_number => p_rec.object_version_number);
1631 --
1633 ( p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id
1634 ,p_no_mn_rlovr_val_dfnd_flag => p_rec.no_mn_rlovr_val_dfnd_flag
1635 ,p_mn_rlovr_val => p_rec.mn_rlovr_val
1636 ,p_effective_date => p_effective_date
1637 ,p_object_version_number => p_rec.object_version_number);
1638 --
1639 chk_mutual_exlsv_mx_val_flg
1640 ( p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id
1641 ,p_no_mx_rlovr_val_dfnd_flag => p_rec.no_mx_rlovr_val_dfnd_flag
1642 ,p_mx_rlovr_val => p_rec.mx_rlovr_val
1643 ,p_effective_date => p_effective_date
1644 ,p_object_version_number => p_rec.object_version_number);
1645 --
1646 chk_mutual_exlsv_mn_pct_flg
1647 ( p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id
1648 ,p_no_mn_rlovr_pct_dfnd_flag => p_rec.no_mn_rlovr_pct_dfnd_flag
1649 ,p_mn_rlovr_pct_num => p_rec.mn_rlovr_pct_num
1650 ,p_effective_date => p_effective_date
1651 ,p_object_version_number => p_rec.object_version_number);
1652 --
1653 chk_mutual_exlsv_mx_pct_flg
1654 ( p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id
1655 ,p_no_mx_rlovr_pct_dfnd_flag => p_rec.no_mx_rlovr_pct_dfnd_flag
1656 ,p_mx_rlovr_pct_num => p_rec.mx_rlovr_pct_num
1657 ,p_effective_date => p_effective_date
1658 ,p_object_version_number => p_rec.object_version_number);
1659 --
1660 chk_pct_rndg_cd_rl_dpndcy
1661 ( p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id
1662 ,p_pct_rndg_cd => p_rec.pct_rndg_cd
1663 ,p_pct_rndg_rl => p_rec.pct_rndg_rl
1664 ,p_effective_date => p_effective_date
1665 ,p_object_version_number => p_rec.object_version_number);
1666 --
1667 chk_val_rndg_cd_rl_dpndcy
1668 ( p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id
1669 ,p_val_rndg_cd => p_rec.val_rndg_cd
1670 ,p_val_rndg_rl => p_rec.val_rndg_rl
1671 ,p_effective_date => p_effective_date
1672 ,p_object_version_number => p_rec.object_version_number);
1673 --
1674 chk_pct_rndg_cd
1675 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1676 p_pct_rndg_cd => p_rec.pct_rndg_cd,
1677 p_effective_date => p_effective_date,
1678 p_object_version_number => p_rec.object_version_number);
1679 --
1680 chk_val_rndg_rl
1681 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1682 p_val_rndg_rl => p_rec.val_rndg_rl,
1683 p_business_group_id => p_rec.business_group_id,
1684 p_effective_date => p_effective_date,
1685 p_object_version_number => p_rec.object_version_number);
1686 --
1687 chk_val_rndg_cd
1688 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1689 p_val_rndg_cd => p_rec.val_rndg_cd,
1690 p_effective_date => p_effective_date,
1691 p_object_version_number => p_rec.object_version_number);
1692 --
1693 chk_no_mx_rlovr_val_dfnd_flag
1694 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1695 p_no_mx_rlovr_val_dfnd_flag => p_rec.no_mx_rlovr_val_dfnd_flag,
1696 p_effective_date => p_effective_date,
1697 p_object_version_number => p_rec.object_version_number);
1698 --
1699 chk_no_mn_rlovr_val_dfnd_flag
1700 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1701 p_no_mn_rlovr_val_dfnd_flag => p_rec.no_mn_rlovr_val_dfnd_flag,
1702 p_effective_date => p_effective_date,
1703 p_object_version_number => p_rec.object_version_number);
1704 --
1705 chk_no_mx_rlovr_pct_dfnd_flag
1706 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1707 p_no_mx_rlovr_pct_dfnd_flag => p_rec.no_mx_rlovr_pct_dfnd_flag,
1708 p_effective_date => p_effective_date,
1709 p_object_version_number => p_rec.object_version_number);
1710 --
1711 chk_no_mn_rlovr_pct_dfnd_flag
1712 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1713 p_no_mn_rlovr_pct_dfnd_flag => p_rec.no_mn_rlovr_pct_dfnd_flag,
1714 p_effective_date => p_effective_date,
1715 p_object_version_number => p_rec.object_version_number);
1716 --
1717 chk_crs_rlovr_procg_cd
1718 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1719 p_crs_rlovr_procg_cd => p_rec.crs_rlovr_procg_cd,
1720 p_effective_date => p_effective_date,
1721 p_object_version_number => p_rec.object_version_number);
1722 --
1723 chk_pct_rndg_rl
1724 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1725 p_pct_rndg_rl => p_rec.pct_rndg_rl,
1726 p_business_group_id => p_rec.business_group_id,
1727 p_effective_date => p_effective_date,
1728 p_object_version_number => p_rec.object_version_number);
1729 --
1730 chk_prtt_elig_rlovr_rl
1731 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1732 p_prtt_elig_rlovr_rl => p_rec.prtt_elig_rlovr_rl,
1733 p_business_group_id => p_rec.business_group_id,
1734 p_effective_date => p_effective_date,
1735 p_object_version_number => p_rec.object_version_number);
1736
1737 chk_abr_dups
1738 (p_acty_base_rt_id => p_rec.acty_base_rt_id,
1739 p_bnft_prvdr_pool_id => p_rec.bnft_prvdr_pool_id,
1740 p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1741 p_effective_date => p_effective_date );
1742
1743 --
1744 hr_utility.set_location(' Leaving:'||l_proc, 10);
1745 End insert_validate;
1749 -- ----------------------------------------------------------------------------
1746 --
1747 -- ----------------------------------------------------------------------------
1748 -- |---------------------------< update_validate >----------------------------|
1750 Procedure update_validate
1751 (p_rec in ben_bpr_shd.g_rec_type,
1752 p_effective_date in date,
1753 p_datetrack_mode in varchar2,
1754 p_validation_start_date in date,
1755 p_validation_end_date in date) is
1756 --
1757 l_proc varchar2(72) := g_package||'update_validate';
1758 --
1759 Begin
1760 hr_utility.set_location('Entering:'||l_proc, 5);
1761 --
1762 -- Call all supporting business operations
1763 --
1764 --
1765 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1766 --
1767 chk_bnft_pool_rlovr_rqmt_id
1768 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1769 p_effective_date => p_effective_date,
1770 p_object_version_number => p_rec.object_version_number);
1771 --
1772 chk_mutual_exlsv_mn_val_flg
1773 ( p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id
1774 ,p_no_mn_rlovr_val_dfnd_flag => p_rec.no_mn_rlovr_val_dfnd_flag
1775 ,p_mn_rlovr_val => p_rec.mn_rlovr_val
1776 ,p_effective_date => p_effective_date
1777 ,p_object_version_number => p_rec.object_version_number);
1778 --
1779 chk_mutual_exlsv_mx_val_flg
1780 ( p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id
1781 ,p_no_mx_rlovr_val_dfnd_flag => p_rec.no_mx_rlovr_val_dfnd_flag
1782 ,p_mx_rlovr_val => p_rec.mx_rlovr_val
1783 ,p_effective_date => p_effective_date
1784 ,p_object_version_number => p_rec.object_version_number);
1785 --
1786 chk_mutual_exlsv_mn_pct_flg
1787 ( p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id
1788 ,p_no_mn_rlovr_pct_dfnd_flag => p_rec.no_mn_rlovr_pct_dfnd_flag
1789 ,p_mn_rlovr_pct_num => p_rec.mn_rlovr_pct_num
1790 ,p_effective_date => p_effective_date
1791 ,p_object_version_number => p_rec.object_version_number);
1792 --
1793 chk_mutual_exlsv_mx_pct_flg
1794 ( p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id
1795 ,p_no_mx_rlovr_pct_dfnd_flag => p_rec.no_mx_rlovr_pct_dfnd_flag
1796 ,p_mx_rlovr_pct_num => p_rec.mx_rlovr_pct_num
1797 ,p_effective_date => p_effective_date
1798 ,p_object_version_number => p_rec.object_version_number);
1799 --
1800 chk_pct_rndg_cd_rl_dpndcy
1801 ( p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id
1802 ,p_pct_rndg_cd => p_rec.pct_rndg_cd
1803 ,p_pct_rndg_rl => p_rec.pct_rndg_rl
1804 ,p_effective_date => p_effective_date
1805 ,p_object_version_number => p_rec.object_version_number);
1806 --
1807 chk_val_rndg_cd_rl_dpndcy
1808 ( p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id
1809 ,p_val_rndg_cd => p_rec.val_rndg_cd
1810 ,p_val_rndg_rl => p_rec.val_rndg_rl
1811 ,p_effective_date => p_effective_date
1812 ,p_object_version_number => p_rec.object_version_number);
1813 --
1814 chk_pct_rndg_cd
1815 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1816 p_pct_rndg_cd => p_rec.pct_rndg_cd,
1817 p_effective_date => p_effective_date,
1818 p_object_version_number => p_rec.object_version_number);
1819 --
1820 chk_val_rndg_rl
1821 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1822 p_val_rndg_rl => p_rec.val_rndg_rl,
1823 p_business_group_id => p_rec.business_group_id,
1824 p_effective_date => p_effective_date,
1825 p_object_version_number => p_rec.object_version_number);
1826 --
1827 chk_val_rndg_cd
1828 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1829 p_val_rndg_cd => p_rec.val_rndg_cd,
1830 p_effective_date => p_effective_date,
1831 p_object_version_number => p_rec.object_version_number);
1832 --
1833 chk_no_mx_rlovr_val_dfnd_flag
1834 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1835 p_no_mx_rlovr_val_dfnd_flag => p_rec.no_mx_rlovr_val_dfnd_flag,
1836 p_effective_date => p_effective_date,
1837 p_object_version_number => p_rec.object_version_number);
1838 --
1839 chk_no_mn_rlovr_val_dfnd_flag
1840 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1841 p_no_mn_rlovr_val_dfnd_flag => p_rec.no_mn_rlovr_val_dfnd_flag,
1842 p_effective_date => p_effective_date,
1843 p_object_version_number => p_rec.object_version_number);
1844 --
1845 chk_no_mx_rlovr_pct_dfnd_flag
1846 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1847 p_no_mx_rlovr_pct_dfnd_flag => p_rec.no_mx_rlovr_pct_dfnd_flag,
1848 p_effective_date => p_effective_date,
1849 p_object_version_number => p_rec.object_version_number);
1850 --
1851 chk_no_mn_rlovr_pct_dfnd_flag
1852 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1853 p_no_mn_rlovr_pct_dfnd_flag => p_rec.no_mn_rlovr_pct_dfnd_flag,
1854 p_effective_date => p_effective_date,
1855 p_object_version_number => p_rec.object_version_number);
1856 --
1857 chk_crs_rlovr_procg_cd
1858 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1859 p_crs_rlovr_procg_cd => p_rec.crs_rlovr_procg_cd,
1860 p_effective_date => p_effective_date,
1861 p_object_version_number => p_rec.object_version_number);
1862 --
1863 chk_pct_rndg_rl
1864 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1865 p_pct_rndg_rl => p_rec.pct_rndg_rl,
1866 p_business_group_id => p_rec.business_group_id,
1867 p_effective_date => p_effective_date,
1868 p_object_version_number => p_rec.object_version_number);
1869 --
1870 chk_prtt_elig_rlovr_rl
1871 (p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1872 p_prtt_elig_rlovr_rl => p_rec.prtt_elig_rlovr_rl,
1873 p_business_group_id => p_rec.business_group_id,
1874 p_effective_date => p_effective_date,
1875 p_object_version_number => p_rec.object_version_number);
1876 --
1877 -- Call the datetrack update integrity operation
1878 --
1879 dt_update_validate
1880 (p_acty_base_rt_id => p_rec.acty_base_rt_id,
1881 p_bnft_prvdr_pool_id => p_rec.bnft_prvdr_pool_id,
1882 p_datetrack_mode => p_datetrack_mode,
1883 p_validation_start_date => p_validation_start_date,
1884 p_validation_end_date => p_validation_end_date);
1885
1886
1887 chk_abr_dups
1888 (p_acty_base_rt_id => p_rec.acty_base_rt_id,
1889 p_bnft_prvdr_pool_id => p_rec.bnft_prvdr_pool_id,
1890 p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id,
1891 p_effective_date => p_effective_date );
1892
1893 --
1894 hr_utility.set_location(' Leaving:'||l_proc, 10);
1895 End update_validate;
1896 --
1897 -- ----------------------------------------------------------------------------
1898 -- |---------------------------< delete_validate >----------------------------|
1899 -- ----------------------------------------------------------------------------
1900 Procedure delete_validate
1901 (p_rec in ben_bpr_shd.g_rec_type,
1902 p_effective_date in date,
1903 p_datetrack_mode in varchar2,
1904 p_validation_start_date in date,
1905 p_validation_end_date in date) is
1906 --
1907 l_proc varchar2(72) := g_package||'delete_validate';
1908 --
1909 Begin
1910 hr_utility.set_location('Entering:'||l_proc, 5);
1911 --
1912 -- Call all supporting business operations
1913 --
1914 dt_delete_validate
1915 (p_datetrack_mode => p_datetrack_mode,
1916 p_validation_start_date => p_validation_start_date,
1917 p_validation_end_date => p_validation_end_date,
1918 p_bnft_pool_rlovr_rqmt_id => p_rec.bnft_pool_rlovr_rqmt_id);
1919 --
1920 hr_utility.set_location(' Leaving:'||l_proc, 10);
1921 End delete_validate;
1922 --
1923 --
1924 -- ---------------------------------------------------------------------------
1925 -- |---------------------< return_legislation_code >-------------------------|
1926 -- ---------------------------------------------------------------------------
1927 --
1928 function return_legislation_code
1929 (p_bnft_pool_rlovr_rqmt_id in number) return varchar2 is
1930 --
1931 -- Declare cursor
1932 --
1933 cursor csr_leg_code is
1934 select a.legislation_code
1935 from per_business_groups a,
1936 ben_bnft_pool_rlovr_rqmt_f b
1937 where b.bnft_pool_rlovr_rqmt_id = p_bnft_pool_rlovr_rqmt_id
1938 and a.business_group_id = b.business_group_id;
1939 --
1940 -- Declare local variables
1941 --
1942 l_legislation_code varchar2(150);
1943 l_proc varchar2(72) := g_package||'return_legislation_code';
1944 --
1945 begin
1946 --
1947 hr_utility.set_location('Entering:'|| l_proc, 10);
1948 --
1949 -- Ensure that all the mandatory parameter are not null
1950 --
1951 hr_api.mandatory_arg_error(p_api_name => l_proc,
1952 p_argument => 'bnft_pool_rlovr_rqmt_id',
1953 p_argument_value => p_bnft_pool_rlovr_rqmt_id);
1954 --
1955 open csr_leg_code;
1956 --
1957 fetch csr_leg_code into l_legislation_code;
1958 --
1959 if csr_leg_code%notfound then
1960 --
1961 close csr_leg_code;
1962 --
1963 -- The primary key is invalid therefore we must error
1964 --
1965 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1966 fnd_message.raise_error;
1967 --
1968 end if;
1969 --
1970 close csr_leg_code;
1971 --
1972 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1973 --
1974 return l_legislation_code;
1975 --
1976 end return_legislation_code;
1977 --
1978 end ben_bpr_bus;