[Home] [Help]
PACKAGE BODY: APPS.BEN_VPF_BUS
Source
1 Package Body ben_vpf_bus as
2 /* $Header: bevpfrhi.pkb 120.1.12010000.1 2008/07/29 13:07:55 appldev ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_vpf_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< chk_vrbl_rt_prfl_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 -- vrbl_rt_prfl_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_vrbl_rt_prfl_id(p_vrbl_rt_prfl_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_vrbl_rt_prfl_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_vpf_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_vrbl_rt_prfl_id,hr_api.g_number)
55 <> ben_vpf_shd.g_old_rec.vrbl_rt_prfl_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_vpf_shd.constraint_error('BEN_VRBL_RT_PRFL_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_vrbl_rt_prfl_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_vpf_shd.constraint_error('BEN_VRBL_RT_PRFL_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_vrbl_rt_prfl_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------------------------< chk_vrbl_usg_cd_dependencies >---------------------------|
81 -- ----------------------------------------------------------------------------
82 -- Bug : 3476138
83 -- Description
84 -- This procedure is used to check that if VPF.VRBL_USG_CD_NAME (Usage) is updated
85 -- then there are no corresponding records in ben_bnft_vrbl_rt_f, ben_acty_vrbl_rt_r
86 -- or ben_actl_prem_vrbl_rt_f if the VAPRO was associated with Coverage, Activity Base Rates
87 -- or Actual Premiums respectively.
88 --
89 -- Pre Conditions
90 -- None.
91 --
92 -- In Parameters
93 -- vrbl_rt_prfl_id :PK of record being updated.
94 -- validation_start_date : Validation Start Date of update
95 -- validation_end_date : Validation End Date of update
96 -- vrbl_usg_cd : Usage code (ACP, CVG or RT)
97 -- effective_date : effective date of the update.
98 -- object_version_number : OVN of record being updated.
99 --
100 -- Post Success
101 -- Processing continues
102 --
103 -- Post Failure
104 -- Errors handled by the procedure
105 --
106 -- Access Status
107 -- Internal table handler use only.
108 --
109 Procedure chk_vrbl_usg_cd_dependencies(p_vrbl_rt_prfl_id in number,
110 p_validation_start_date in date,
111 p_validation_end_date in date,
112 p_vrbl_usg_cd in varchar2,
113 p_effective_date in date,
114 p_object_version_number in number) is
115 --
116 l_proc varchar2(72) := g_package||'chk_vrbl_usg_cd_dependencies';
117 l_api_updating boolean;
118 --
119 Begin
120 --
121 hr_utility.set_location('Entering:'||l_proc, 5);
122 --
123 l_api_updating := ben_vpf_shd.api_updating
124 (p_effective_date => p_effective_date,
125 p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
126 p_object_version_number => p_object_version_number);
127 --
128 if (l_api_updating
129 and nvl(p_vrbl_usg_cd,hr_api.g_varchar2)
130 <> ben_vpf_shd.g_old_rec.vrbl_usg_cd) then
131
132 if ben_vpf_shd.g_old_rec.vrbl_usg_cd = 'RT' then
133 --
134 If (dt_api.rows_exist
135 (p_base_table_name => 'ben_acty_vrbl_rt_f',
136 p_base_key_column => 'vrbl_rt_prfl_id',
137 p_base_key_value => p_vrbl_rt_prfl_id,
138 p_from_date => p_validation_start_date,
139 p_to_date => p_validation_end_date)) Then
140 --
141 fnd_message.set_name('BEN','BEN_93902_VAPRO_CHILD_EXISTS');
142 fnd_message.set_token('RATE_TYPE','Standard Rate');
143 fnd_message.raise_error;
144 --
145 end if;
146 --
147 End If;
148 --
149 if ben_vpf_shd.g_old_rec.vrbl_usg_cd = 'CVG' then
150 --
151 If (dt_api.rows_exist
152 (p_base_table_name => 'ben_bnft_vrbl_rt_f',
153 p_base_key_column => 'vrbl_rt_prfl_id',
154 p_base_key_value => p_vrbl_rt_prfl_id,
155 p_from_date => p_validation_start_date,
156 p_to_date => p_validation_end_date)) Then
157 --
158 fnd_message.set_name ('BEN','BEN_93902_VAPRO_CHILD_EXISTS');
159 fnd_message.set_token('RATE_TYPE','Coverage');
160 fnd_message.raise_error;
161 --
162 end if;
163 --
164 End If;
165 --
166 --
167 if ben_vpf_shd.g_old_rec.vrbl_usg_cd = 'ACP' then
168 --
169 If (dt_api.rows_exist
170 (p_base_table_name => 'ben_actl_prem_vrbl_rt_f',
171 p_base_key_column => 'vrbl_rt_prfl_id',
172 p_base_key_value => p_vrbl_rt_prfl_id,
173 p_from_date => p_validation_start_date,
174 p_to_date => p_validation_end_date)) Then
175 --
176 fnd_message.set_name('BEN','BEN_93902_VAPRO_CHILD_EXISTS');
177 fnd_message.set_token('RATE_TYPE','Actual Premium');
178 fnd_message.raise_error;
179 --
180 end if;
181 hr_utility.set_location('Reaced here',9999);
182 --
183 End If;
184 --
185 end if;
186 --
187 hr_utility.set_location('Leaving:'||l_proc, 10);
188 --
189 End chk_vrbl_usg_cd_dependencies;
190 --
191 -- ----------------------------------------------------------------------------
192 -- |--------------------< chk_mn_mx_elcn_vals >-----------------------------|
193 -- ----------------------------------------------------------------------------
194 --
195 -- Description
196 -- ensure that the value given for MN_ELCN_VAL is less than the value
197 -- given for MX_ELCN_VAL
198 --
199 -- Pre Conditions
200 -- none
201 --
202 -- In Parameters
203 -- p_mn_elcn_val Minimum Election Value.
204 -- p_mx_elcn_val Maximum Election Value.
205 --
206 -- Post Success
207 -- Processing continues
208 --
209 -- Post Failure
210 -- Errors handled by the procedure
211 --
212 -- Access Status
213 -- Internal table handler use only.
214 -- ----------------------------------------------------------------------------
215 Procedure chk_mn_mx_elcn_vals
216 (p_mn_elcn_val in number,
217 p_mx_elcn_val in number) is
218 begin
219 if p_mn_elcn_val is not null and
220 p_mx_elcn_val is not null and
221 p_mx_elcn_val < p_mn_elcn_val then
222 fnd_message.set_name('BEN','BEN_91069_INVALID_MIN_MAX');
223 fnd_message.raise_error;
224 end if;
225 end chk_mn_mx_elcn_vals;
226 --
227 -- ----------------------------------------------------------------------------
228 -- |--------------------< chk_lwr_upr_lmt_vals >-----------------------------|
229 -- ----------------------------------------------------------------------------
230 --
231 -- Description
232 -- ensure that the value given for lwr_lmt_val is less than the value
233 -- given for upr_lmt_val
234 --
235 -- Pre Conditions
236 -- none
237 --
238 -- In Parameters
239 -- lwr_lmt_val
240 -- upr_lmt_val
241 -- Post Success
242 -- Processing continues
243 --
244 -- Post Failure
245 -- Errors handled by the procedure
246 --
247 -- Access Status
248 -- Internal table handler use only.
249 -- ----------------------------------------------------------------------------
250 Procedure chk_lwr_upr_lmt_vals
251 (p_lwr_lmt_val in number,
252 p_upr_lmt_val in number) is
253 begin
254 if p_lwr_lmt_val is not null and
255 p_upr_lmt_val is not null and
256 p_upr_lmt_val < p_lwr_lmt_val then
257 fnd_message.set_name('BEN','BEN_92279_LWR_LESS_NOT_EQ_UPR');
258 fnd_message.raise_error;
259 end if;
260 end chk_lwr_upr_lmt_vals;
261 --
262
263 -- ----------------------------------------------------------------------------
264 -- |------------------------< chk_lwr_upr_lmt_vals >--------------------------|
265 -- ----------------------------------------------------------------------------
266
267 Procedure chk_ultmt_lwr_upr_lmt
268 (p_ultmt_lwr_lmt in number,
269 p_ultmt_upr_lmt in number) is
270 begin
271 if p_ultmt_lwr_lmt is not null and
272 p_ultmt_upr_lmt is not null and
273 p_ultmt_upr_lmt < p_ultmt_lwr_lmt then
274 fnd_message.set_name('BEN','BEN_92279_LWR_LESS_NOT_EQ_UPR');
275 fnd_message.raise_error;
276 end if;
277 end chk_ultmt_lwr_upr_lmt;
278
279
280
281 -- ----------------------------------------------------------------------------
282 -- |------------------------< chk_name_unique >-------------------------------|
283 -- ----------------------------------------------------------------------------
284 --
285 -- Description
286 -- ensure that the Variable Rate Profile Name is unique
287 -- within business_group
288 --
289 -- Pre Conditions
290 -- None.
291 --
292 -- In Parameters
293 -- p_name is Vrbl_Rt_Prfl name
294 -- p_vrbl_rt_prfl_id is vrbl_rt_prfl_id
295 -- p_business_group_id
296 --
297 -- Post Success
298 -- Processing continues
299 --
300 -- Post Failure
301 -- Errors handled by the procedure
302 --
303 -- Access Status
304 -- Internal table handler use only.
305 --
306 -- ----------------------------------------------------------------------------
307 Procedure chk_name_unique
308 ( p_vrbl_rt_prfl_id in varchar2
309 ,p_name in varchar2
310 ,p_business_group_id in number)
311 is
312 l_proc varchar2(72) := g_package||'chk_name_unique';
313 l_dummy char(1);
314 cursor c1 is select null
315 from ben_vrbl_rt_prfl_f
316 Where vrbl_rt_prfl_id <> nvl(p_vrbl_rt_prfl_id,-1)
317 and name = p_name
318 and business_group_id = p_business_group_id;
319 --
320 Begin
321 hr_utility.set_location('Entering:'||l_proc, 5);
322 --
323 open c1;
324 fetch c1 into l_dummy;
325 if c1%found then
326 close c1;
327 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
328 fnd_message.raise_error;
329 end if;
330 --
331 hr_utility.set_location('Leaving:'||l_proc, 15);
332 End chk_name_unique;
333 --
334 -- ----------------------------------------------------------------------------
335 -- |---------------------< chk_lwr_lmt_val_and_rl >---------------------------|
336 -- ----------------------------------------------------------------------------
337 --
338 -- Description
339 -- This procedure is used to check that lwr_lmt_val and lwr_lmt_calc_rl
340 -- are mutually exclusive.
341 --
342 -- Pre Conditions
343 -- None.
344 --
345 -- In Parameters
346 -- vrbl_rt_prfl_id PK of record being inserted or updated.
347 -- lwr_lmt_val Lower Limit Value.
348 -- lwr_lmt_calc_rl Lower Limit Value Rule.
349 --
350 -- Post Success
351 -- Processing continues
352 --
353 -- Post Failure
354 -- Error handled by procedure
355 --
356 -- Access Status
357 -- Internal table handler use only.
358 --
359 Procedure chk_lwr_lmt_val_and_rl(p_vrbl_rt_prfl_id in number,
360 p_lwr_lmt_val in number,
361 p_lwr_lmt_calc_rl in number) is
362 --
363 l_proc varchar2(72) := g_package||'chk_lwr_lmt_val_and_rl';
364 --
365 Begin
366 --
367 hr_utility.set_location('Entering:'||l_proc, 5);
368 --
369 -- Lower Limit Value and Lower Limit Value Rule fields must be
370 -- mutually exclusive.
371 if (p_lwr_lmt_val is not null and p_lwr_lmt_calc_rl is not null)
372 then
373 --
374 fnd_message.set_name('BEN','BEN_91859_LWR_LMT_VAL_AND_RL');
375 fnd_message.raise_error;
376 --
377 end if;
378 end chk_lwr_lmt_val_and_rl;
379
380
381
382 -- ----------------------------------------------------------------------------
383 -- |---------------------< chk_ultmt_lwr_lmt_and_rl >----------------------|
384 -- ----------------------------------------------------------------------------
385 --
386 -- Description
387 -- This procedure is used to check that ultmt_lwr_lmt_val and
388 --- ulmt_lwr_lmt_calc_rl
389 -- are mutually exclusive.
390 --
391 -- Pre Conditions
392 -- None.
393 --
394 -- In Parameters
395 -- vrbl_rt_prfl_id PK of record being inserted or updated.
396 -- ultmt_lwr_lmt Lower Limit Value.
397 -- ultmt_lwr_lmt_calc_rl Lower Limit Value Rule.
398 --
399 -- Post Success
400 -- Processing continues
401 --
402 -- Post Failure
403 -- Error handled by procedure
404 --
405 -- Access Status
406 -- Internal table handler use only.
407 --
408 Procedure chk_ultmt_lwr_lmt_val_and_rl(p_vrbl_rt_prfl_id in number,
409 p_ultmt_lwr_lmt in number,
410 p_ultmt_lwr_lmt_calc_rl in number) is
411 --
412 l_proc varchar2(72) := g_package||'chk_ultmt_lwr_lmt_val_and_rl';
413 --
414 Begin
415 --
416 hr_utility.set_location('Entering:'||l_proc, 5);
417 --
418 -- Lower Limit Value and Lower Limit Value Rule fields must be
419 -- mutually exclusive.
420 if (p_ultmt_lwr_lmt is not null and p_ultmt_lwr_lmt_calc_rl is not null)
421 then
422 --
423 fnd_message.set_name('BEN','BEN_91859_LWR_LMT_VAL_AND_RL');
424 fnd_message.raise_error;
425 --
426 end if;
427 end chk_ultmt_lwr_lmt_val_and_rl;
428
429
430 --
431 -- ----------------------------------------------------------------------------
432 -- |---------------------< chk_upr_lmt_val_and_rl >---------------------------|
433 -- ----------------------------------------------------------------------------
434 --
435 -- Description
436 -- This procedure is used to check that upr_lmt_val and upr_lmt_calc_rl
437 -- are mutually exclusive.
438 --
439 -- Pre Conditions
440 -- None.
441 --
442 -- In Parameters
443 -- vrbl_rt_prfl_id PK of record being inserted or updated.
444 -- upr_lmt_val Upper Limit Value.
445 -- upr_lmt_calc_rl Upper Limit Value Rule.
446 --
447 -- Post Success
448 -- Processing continues
449 --
450 -- Post Failure
451 -- Error handled by procedure
452 --
453 -- Access Status
454 -- Internal table handler use only.
455 --
456 Procedure chk_upr_lmt_val_and_rl(p_vrbl_rt_prfl_id in number,
457 p_upr_lmt_val in number,
458 p_upr_lmt_calc_rl in number) is
459 --
460 l_proc varchar2(72) := g_package||'chk_upr_lmt_val_and_rl';
461 --
462 Begin
463 --
464 hr_utility.set_location('Entering:'||l_proc, 5);
465 --
466 -- Upper Limit Value and Upper Limit Value Rule fields must be
467 -- mutually exclusive.
468 if (p_upr_lmt_val is not null and p_upr_lmt_calc_rl is not null)
469 then
470 --
471 fnd_message.set_name('BEN','BEN_91860_UPR_LMT_VAL_AND_RL');
472 fnd_message.raise_error;
473 --
474 end if;
475 end chk_upr_lmt_val_and_rl;
476
477
478 --
479 -- ----------------------------------------------------------------------------
480 -- |---------------------< chk_ultmt_upr_lmt_val_and_rl >----------------------|
481 -- ----------------------------------------------------------------------------
482 --
483 -- Description
484 -- This procedure is used to check that ultmt_upr_lmt and
485 -- ultmt_upr_lmt_calc_rl
486 -- are mutually exclusive.
487 --
488 -- Pre Conditions
489 -- None.
490 --
491 -- In Parameters
492 -- vrbl_rt_prfl_id PK of record being inserted or updated.
493 -- ultmt_upr_lmt Upper Limit Value.
494 -- ultmt_upr_lmt_calc_rl Upper Limit Value Rule.
495 --
496 -- Post Success
497 -- Processing continues
498 --
499 -- Post Failure
500 -- Error handled by procedure
501 --
502 -- Access Status
503 -- Internal table handler use only.
504 --
505 Procedure chk_ultmt_upr_lmt_val_and_rl(p_vrbl_rt_prfl_id in number,
506 p_ultmt_upr_lmt in number,
507 p_ultmt_upr_lmt_calc_rl in number) is
508 --
509 l_proc varchar2(72) := g_package||'chk_ultmt_upr_lmt_val_and_rl';
510 --
511 Begin
512 --
513 hr_utility.set_location('Entering:'||l_proc, 5);
514 --
515 -- Upper Limit Value and Upper Limit Value Rule fields must be
516 -- mutually exclusive.
517 if (p_ultmt_upr_lmt is not null and p_ultmt_upr_lmt_calc_rl is not null)
518 then
519 --
520 fnd_message.set_name('BEN','BEN_91860_UPR_LMT_VAL_AND_RL');
521 fnd_message.raise_error;
522 --
523 end if;
524 end chk_ultmt_upr_lmt_val_and_rl;
525
526
527 --
528 -- ----------------------------------------------------------------------------
529 -- |-----------------------< chk_vrbl_rt_prfl_stat_cd >-----------------------|
530 -- ----------------------------------------------------------------------------
531 --
532 -- Description
533 -- This procedure is used to check that the lookup value is valid.
534 --
535 -- Pre Conditions
536 -- None.
537 --
538 -- In Parameters
539 -- vrbl_rt_prfl_id PK of record being inserted or updated.
540 -- vrbl_rt_prfl_stat_cd Value of lookup code.
541 -- effective_date effective date
542 -- object_version_number Object version number of record being
543 -- inserted or updated.
544 --
545 -- Post Success
546 -- Processing continues
547 --
548 -- Post Failure
549 -- Error handled by procedure
550 --
551 -- Access Status
552 -- Internal table handler use only.
553 --
554 Procedure chk_vrbl_rt_prfl_stat_cd(p_vrbl_rt_prfl_id in number,
555 p_vrbl_rt_prfl_stat_cd in varchar2,
556 p_effective_date in date,
557 p_object_version_number in number) is
558 --
559 l_proc varchar2(72) := g_package||'chk_vrbl_rt_prfl_stat_cd';
560 l_api_updating boolean;
561 --
562 Begin
563 --
564 hr_utility.set_location('Entering:'||l_proc, 5);
565 --
566 l_api_updating := ben_vpf_shd.api_updating
567 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
568 p_effective_date => p_effective_date,
569 p_object_version_number => p_object_version_number);
570 --
571 if (l_api_updating
572 and p_vrbl_rt_prfl_stat_cd
573 <> nvl(ben_vpf_shd.g_old_rec.vrbl_rt_prfl_stat_cd,hr_api.g_varchar2)
574 or not l_api_updating)
575 and p_vrbl_rt_prfl_stat_cd is not null then
576 --
577 -- check if value of lookup falls within lookup type.
578 --
579 if hr_api.not_exists_in_hr_lookups
580 (p_lookup_type => 'BEN_STAT',
581 p_lookup_code => p_vrbl_rt_prfl_stat_cd,
582 p_effective_date => p_effective_date) then
583 --
584 -- raise error as does not exist as lookup
585 --
586 hr_utility.set_message(801,'LOOKUP vrbl_rt_prfl_stat_EXIST');
587 hr_utility.raise_error;
588 --
589 end if;
590 --
591 end if;
592 --
593 hr_utility.set_location('Leaving:'||l_proc,10);
594 --
595 end chk_vrbl_rt_prfl_stat_cd;
596 --
597 -- ----------------------------------------------------------------------------
598 -- |------------------------< chk_val_calc_rl >-------------------------------|
599 -- ----------------------------------------------------------------------------
600 --
601 -- Description
602 -- This procedure is used to check that the Formula Rule is valid.
603 --
604 -- Pre Conditions
605 -- None.
606 --
607 -- In Parameters
608 -- vrbl_rt_prfl_id PK of record being inserted or updated.
609 -- val_calc_rl Value of formula rule id.
610 -- effective_date effective date
611 -- object_version_number Object version number of record being
612 -- inserted or updated.
613 --
614 -- Post Success
615 -- Processing continues
616 --
617 -- Post Failure
618 -- Error handled by procedure
619 --
620 -- Access Status
621 -- Internal table handler use only.
622 --
623 Procedure chk_val_calc_rl(p_vrbl_rt_prfl_id in number,
624 p_val_calc_rl in number,
625 p_business_group_id in number,
626 p_effective_date in date,
627 p_object_version_number in number) is
628 --
629 l_proc varchar2(72) := g_package||'chk_val_calc_rl';
630 l_api_updating boolean;
631 l_dummy varchar2(1);
632 --
633 cursor c1 is
634 select null
635 from ff_formulas_f ff
636 ,per_business_groups pbg
637 where ff.formula_id = p_val_calc_rl
638 and ff.formula_type_id in (-171, -49, -507)
639 and pbg.business_group_id = p_business_group_id
640 and nvl(ff.business_group_id, p_business_group_id) =
641 p_business_group_id
642 and nvl(ff.legislation_code, pbg.legislation_code) =
643 pbg.legislation_code
644 and p_effective_date
645 between ff.effective_start_date
646 and ff.effective_end_date;
647 --
648 Begin
649 --
650 hr_utility.set_location('Entering:'||l_proc, 5);
651 --
652 l_api_updating := ben_vpf_shd.api_updating
653 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
654 p_effective_date => p_effective_date,
655 p_object_version_number => p_object_version_number);
656 --
657 if (l_api_updating
658 and nvl(p_val_calc_rl,hr_api.g_number)
659 <> ben_vpf_shd.g_old_rec.val_calc_rl
660 or not l_api_updating)
661 and p_val_calc_rl is not null then
662 --
663 -- check if value of formula rule is valid.
664 --
665 open c1;
666 --
667 -- fetch value from cursor if it returns a record then the
668 -- formula is valid otherwise its invalid
669 --
670 fetch c1 into l_dummy;
671 if c1%notfound then
672 --
673 close c1;
674 --
675 -- raise error
676 --
677 hr_utility.set_message(801,'FORMULA_DOES_NOT_EXIST');
678 hr_utility.raise_error;
679 --
680 end if;
681 --
682 close c1;
683 --
684 end if;
685 --
686 hr_utility.set_location('Leaving:'||l_proc,10);
687 --
688 end chk_val_calc_rl;
689 --
690 -- ----------------------------------------------------------------------------
691 -- |----------------------------< chk_rndg_rl >-------------------------------|
692 -- ----------------------------------------------------------------------------
693 --
694 -- Description
695 -- This procedure is used to check that the Formula Rule is valid.
696 --
697 -- Pre Conditions
698 -- None.
699 --
700 -- In Parameters
701 -- vrbl_rt_prfl_id PK of record being inserted or updated.
702 -- rndg_rl Value of formula rule id.
703 -- effective_date effective date
704 -- object_version_number Object version number of record being
705 -- inserted or updated.
706 --
707 -- Post Success
708 -- Processing continues
709 --
710 -- Post Failure
711 -- Error handled by procedure
712 --
713 -- Access Status
714 -- Internal table handler use only.
715 --
716 Procedure chk_rndg_rl (p_vrbl_rt_prfl_id in number,
717 p_rndg_rl in number,
718 p_business_group_id in number,
719 p_effective_date in date,
720 p_object_version_number in number) is
721 --
722 l_proc varchar2(72) := g_package||'chk_rndg_rl';
723 l_api_updating boolean;
724 l_dummy varchar2(1);
725 --
726 cursor c1 is
727 select null
728 from ff_formulas_f ff
729 ,per_business_groups pbg
730 where ff.formula_id = p_rndg_rl
731 and ff.formula_type_id = -169
732 and pbg.business_group_id = p_business_group_id
733 and nvl(ff.business_group_id, p_business_group_id) =
734 p_business_group_id
735 and nvl(ff.legislation_code, pbg.legislation_code) =
736 pbg.legislation_code
737 and p_effective_date
738 between ff.effective_start_date
739 and ff.effective_end_date;
740 --
741 Begin
742 --
743 hr_utility.set_location('Entering:'||l_proc, 5);
744 --
745 l_api_updating := ben_vpf_shd.api_updating
746 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
747 p_effective_date => p_effective_date,
748 p_object_version_number => p_object_version_number);
749 --
750 if (l_api_updating
751 and nvl(p_rndg_rl,hr_api.g_number)
752 <> ben_vpf_shd.g_old_rec.rndg_rl
753 or not l_api_updating)
754 and p_rndg_rl is not null then
755 --
756 -- check if value of formula rule is valid.
757 --
758 open c1;
759 --
760 -- fetch value from cursor if it returns a record then the
761 -- formula is valid otherwise its invalid
762 --
763 fetch c1 into l_dummy;
764 if c1%notfound then
765 --
766 close c1;
767 --
768 -- raise error
769 --
770 hr_utility.set_message('BEN','BEN_91042_INVALID_RNDG_RL');
771 hr_utility.raise_error;
772 --
773 end if;
774 --
775 close c1;
776 --
777 end if;
778 --
779 hr_utility.set_location('Leaving:'||l_proc,10);
780 --
781 end chk_rndg_rl;
782 --
783 -- ----------------------------------------------------------------------------
784 -- |---------------------< chk_lwr_lmt_calc_rl >-------------------------------|
785 -- ----------------------------------------------------------------------------
786 --
787 -- Description
788 -- This procedure is used to check that the Formula Rule is valid.
789 --
790 -- Pre Conditions
791 -- None.
792 --
793 -- In Parameters
794 -- vrbl_rt_prfl_id PK of record being inserted or updated.
795 -- lwr_lmt_calc_rl Value of formula rule id.
796 -- effective_date effective date
797 -- object_version_number Object version number of record being
798 -- inserted or updated.
799 --
800 -- Post Success
801 -- Processing continues
802 --
803 -- Post Failure
804 -- Error handled by procedure
805 --
806 -- Access Status
807 -- Internal table handler use only.
808 --
809 Procedure chk_lwr_lmt_calc_rl (p_vrbl_rt_prfl_id in number,
810 p_lwr_lmt_calc_rl in number,
811 p_business_group_id in number,
812 p_effective_date in date,
813 p_object_version_number in number) is
814 --
815 l_proc varchar2(72) := g_package||'chk_lwr_lmt_calc_rl';
816 l_api_updating boolean;
817 l_dummy varchar2(1);
818 --
819 cursor c1 is
820 select null
821 from ff_formulas_f ff
822 ,per_business_groups pbg
823 where ff.formula_id = p_lwr_lmt_calc_rl
824 and ff.formula_type_id in (-392, -511, -512)
825 and pbg.business_group_id = p_business_group_id
826 and nvl(ff.business_group_id, p_business_group_id) =
827 p_business_group_id
828 and nvl(ff.legislation_code, pbg.legislation_code) =
829 pbg.legislation_code
830 and p_effective_date
831 between ff.effective_start_date
832 and ff.effective_end_date;
833 --
834 Begin
835 --
836 hr_utility.set_location('Entering:'||l_proc, 5);
837 --
838 l_api_updating := ben_vpf_shd.api_updating
839 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
840 p_effective_date => p_effective_date,
841 p_object_version_number => p_object_version_number);
842 --
843 if (l_api_updating
844 and nvl(p_lwr_lmt_calc_rl,hr_api.g_number)
845 <> ben_vpf_shd.g_old_rec.lwr_lmt_calc_rl
846 or not l_api_updating)
847 and p_lwr_lmt_calc_rl is not null then
848 --
849 -- check if value of formula rule is valid.
850 --
851 open c1;
852 --
853 -- fetch value from cursor if it returns a record then the
854 -- formula is valid otherwise its invalid
855 --
856 fetch c1 into l_dummy;
857 if c1%notfound then
858 --
859 close c1;
860 --
861 -- raise error
862 --
863 hr_utility.set_message('BEN','BEN_91815_INVALID_LWR_LMT_RL');
864 hr_utility.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_lwr_lmt_calc_rl;
875
876
877 -- ----------------------------------------------------------------------------
878 -- |---------------------< chk_ultmt_lwr_lmt_calc_rl >-------------------------|
879 -- ----------------------------------------------------------------------------
880 --
881 -- Description
882 -- This procedure is used to check that the Formula Rule is valid.
883 --
884 -- Pre Conditions
885 -- None.
886 --
887 -- In Parameters
888 -- vrbl_rt_prfl_id PK of record being inserted or updated.
889 -- ultmt_lwr_lmt_calc_rl Value of formula rule id.
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_ultmt_lwr_lmt_calc_rl (p_vrbl_rt_prfl_id in number,
904 p_ultmt_lwr_lmt_calc_rl in number,
905 p_business_group_id in number,
906 p_effective_date in date,
907 p_object_version_number in number) is
908 --
909 l_proc varchar2(72) := g_package||'chk_ultmt_lwr_lmt_calc_rl';
910 l_api_updating boolean;
911 l_dummy varchar2(1);
912 --
913 cursor c1 is
914 select null
915 from ff_formulas_f ff
916 ,per_business_groups pbg
917 where ff.formula_id = p_ultmt_lwr_lmt_calc_rl
918 and ff.formula_type_id in (-392, -511, -512)
919 and pbg.business_group_id = p_business_group_id
920 and nvl(ff.business_group_id, p_business_group_id) =
921 p_business_group_id
922 and nvl(ff.legislation_code, pbg.legislation_code) =
923 pbg.legislation_code
924 and p_effective_date
925 between ff.effective_start_date
926 and ff.effective_end_date;
927 --
928 Begin
929 --
930 hr_utility.set_location('Entering:'||l_proc, 5);
931 --
932 l_api_updating := ben_vpf_shd.api_updating
933 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
934 p_effective_date => p_effective_date,
935 p_object_version_number => p_object_version_number);
936 --
937 if (l_api_updating
938 and nvl(p_ultmt_lwr_lmt_calc_rl,hr_api.g_number)
939 <> ben_vpf_shd.g_old_rec.ultmt_lwr_lmt_calc_rl
940 or not l_api_updating)
941 and p_ultmt_lwr_lmt_calc_rl is not null then
942 --
943 --
944 -- check if value of formula rule is valid.
945 --
946 open c1;
947 --
948 -- fetch value from cursor if it returns a record then the
949 -- formula is valid otherwise its invalid
950 --
951 fetch c1 into l_dummy;
952 if c1%notfound then
953 --
954 close c1;
955 --
956 -- raise error
957 --
958 hr_utility.set_message('BEN','BEN_91815_INVALID_LWR_LMT_RL');
959 hr_utility.raise_error;
960 --
961 end if;
962 --
963 close c1;
964 --
965 end if;
966 --
967 hr_utility.set_location('Leaving:'||l_proc,10);
968 --
969 end chk_ultmt_lwr_lmt_calc_rl;
970
971
972
973 --
974 -- ----------------------------------------------------------------------------
975 -- |---------------------< chk_upr_lmt_calc_rl >-------------------------------|
976 -- ----------------------------------------------------------------------------
977 --
978 -- Description
979 -- This procedure is used to check that the Formula Rule is valid.
980 --
981 -- Pre Conditions
982 -- None.
983 --
984 -- In Parameters
985 -- vrbl_rt_prfl_id PK of record being inserted or updated.
986 -- upr_lmt_calc_rl Value of formula rule id.
987 -- effective_date effective date
988 -- object_version_number Object version number of record being
989 -- inserted or updated.
990 --
991 -- Post Success
992 -- Processing continues
993 --
994 -- Post Failure
995 -- Error handled by procedure
996 --
997 -- Access Status
998 -- Internal table handler use only.
999 --
1000 Procedure chk_upr_lmt_calc_rl (p_vrbl_rt_prfl_id in number,
1001 p_upr_lmt_calc_rl in number,
1002 p_business_group_id in number,
1003 p_effective_date in date,
1004 p_object_version_number in number) is
1005 --
1006 l_proc varchar2(72) := g_package||'chk_upr_lmt_calc_rl';
1007 l_api_updating boolean;
1008 l_dummy varchar2(1);
1009 --
1010 cursor c1 is
1011 select null
1012 from ff_formulas_f ff
1013 ,per_business_groups pbg
1014 where ff.formula_id = p_upr_lmt_calc_rl
1015 and ff.formula_type_id in (-293, -514, -515)
1016 and pbg.business_group_id = p_business_group_id
1017 and nvl(ff.business_group_id, p_business_group_id) =
1018 p_business_group_id
1019 and nvl(ff.legislation_code, pbg.legislation_code) =
1020 pbg.legislation_code
1021 and p_effective_date
1022 between ff.effective_start_date
1023 and ff.effective_end_date;
1024 --
1025 Begin
1026 --
1027 hr_utility.set_location('Entering:'||l_proc, 5);
1028 --
1029 l_api_updating := ben_vpf_shd.api_updating
1030 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
1031 p_effective_date => p_effective_date,
1032 p_object_version_number => p_object_version_number);
1033 --
1034 if (l_api_updating
1035 and nvl(p_upr_lmt_calc_rl,hr_api.g_number)
1036 <> ben_vpf_shd.g_old_rec.upr_lmt_calc_rl
1037 or not l_api_updating)
1038 and p_upr_lmt_calc_rl is not null then
1039 --
1040 -- check if value of formula rule is valid.
1041 --
1042 open c1;
1043 --
1044 -- fetch value from cursor if it returns a record then the
1045 -- formula is valid otherwise its invalid
1046 --
1047 fetch c1 into l_dummy;
1048 if c1%notfound then
1049 --
1050 close c1;
1051 --
1052 -- raise error
1053 --
1054 hr_utility.set_message('BEN','BEN_91823_INVALID_UPR_LMT_RL');
1055 hr_utility.raise_error;
1056 --
1057 end if;
1058 --
1059 close c1;
1060 --
1061 end if;
1062 --
1063 hr_utility.set_location('Leaving:'||l_proc,10);
1064 --
1065 end chk_upr_lmt_calc_rl;
1066
1067
1068 -- ----------------------------------------------------------------------------
1069 -- |---------------------< chk_ultmt_upr_lmt_calc_rl >--------------------------|
1070 -- ----------------------------------------------------------------------------
1071 --
1072 -- Description
1073 -- This procedure is used to check that the Formula Rule is valid.
1074 --
1075 -- Pre Conditions
1076 -- None.
1077 --
1078 -- In Parameters
1079 -- vrbl_rt_prfl_id PK of record being inserted or updated.
1080 -- upr_lmt_calc_rl Value of formula rule id.
1081 -- effective_date effective date
1082 -- object_version_number Object version number of record being
1083 -- inserted or updated.
1084 --
1085 -- Post Success
1086 -- Processing continues
1087 --
1088 -- Post Failure
1089 -- Error handled by procedure
1090 --
1091 -- Access Status
1092 -- Internal table handler use only.
1093 --
1094 Procedure chk_ultmt_upr_lmt_calc_rl(p_vrbl_rt_prfl_id in number,
1095 p_ultmt_upr_lmt_calc_rl in number,
1096 p_business_group_id in number,
1097 p_effective_date in date,
1098 p_object_version_number in number) is
1099 --
1100 l_proc varchar2(72) := g_package||'chk_ultmt_upr_lmt_calc_rl';
1101 l_api_updating boolean;
1102 l_dummy varchar2(1);
1103 --
1104 cursor c1 is
1105 select null
1106 from ff_formulas_f ff
1107 ,per_business_groups pbg
1108 where ff.formula_id = p_ultmt_upr_lmt_calc_rl
1109 and ff.formula_type_id in (-293, -514, -515)
1110 and pbg.business_group_id = p_business_group_id
1111 and nvl(ff.business_group_id, p_business_group_id) =
1112 p_business_group_id
1113 and nvl(ff.legislation_code, pbg.legislation_code) =
1114 pbg.legislation_code
1115 and p_effective_date
1116 between ff.effective_start_date
1117 and ff.effective_end_date;
1118 --
1119 Begin
1120 --
1121 hr_utility.set_location('Entering:'||l_proc, 5);
1122 --
1123 l_api_updating := ben_vpf_shd.api_updating
1124 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_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 nvl(p_ultmt_upr_lmt_calc_rl,hr_api.g_number)
1130 <> ben_vpf_shd.g_old_rec.ultmt_upr_lmt_calc_rl
1131 or not l_api_updating)
1132 and p_ultmt_upr_lmt_calc_rl is not null then
1133 --
1134
1135 --
1136 open c1;
1137 --
1138 -- fetch value from cursor if it returns a record then the
1139 -- formula is valid otherwise its invalid
1140 --
1141 fetch c1 into l_dummy;
1142 if c1%notfound then
1143 --
1144 close c1;
1145 --
1146 -- raise error
1147 --
1148 hr_utility.set_message('BEN','BEN_91823_INVALID_UPR_LMT_RL');
1149 hr_utility.raise_error;
1150 --
1151 end if;
1152 --
1153 close c1;
1154 --
1155 end if;
1156 --
1157 hr_utility.set_location('Leaving:'||l_proc,10);
1158 --
1159 end chk_ultmt_upr_lmt_calc_rl;
1160
1161
1162
1163
1164
1165 --
1166 -- ----------------------------------------------------------------------------
1167 -- |------------------------< chk_no_mx_elcn_val_dfnd_flag >------------------|
1168 -- ----------------------------------------------------------------------------
1169 --
1170 -- Description
1171 -- This procedure is used to check that the lookup value is valid.
1172 --
1173 -- Pre Conditions
1174 -- None.
1175 --
1176 -- In Parameters
1177 -- vrbl_rt_prfl_id PK of record being inserted or updated.
1178 -- no_mx_elcn_val_dfnd_flag Value of lookup code.
1179 -- effective_date effective date
1180 -- object_version_number Object version number of record being
1181 -- inserted or updated.
1182 --
1183 -- Post Success
1184 -- Processing continues
1185 --
1186 -- Post Failure
1187 -- Error handled by procedure
1188 --
1189 -- Access Status
1190 -- Internal table handler use only.
1191 --
1192 Procedure chk_no_mx_elcn_val_dfnd_flag
1193 (p_vrbl_rt_prfl_id in number,
1194 p_no_mx_elcn_val_dfnd_flag in varchar2,
1195 p_effective_date in date,
1196 p_object_version_number in number) is
1197 --
1198 l_proc varchar2(72) := g_package||'chk_no_mx_elcn_val_dfnd_flag';
1199 l_api_updating boolean;
1200 --
1201 Begin
1202 --
1203 hr_utility.set_location('Entering:'||l_proc, 5);
1204 --
1205 l_api_updating := ben_vpf_shd.api_updating
1206 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
1207 p_effective_date => p_effective_date,
1208 p_object_version_number => p_object_version_number);
1209 --
1210 if (l_api_updating
1211 and p_no_mx_elcn_val_dfnd_flag
1212 <> nvl(ben_vpf_shd.g_old_rec.no_mx_elcn_val_dfnd_flag,hr_api.g_varchar2)
1213 or not l_api_updating) then
1214 --
1215 -- check if value of lookup falls within lookup type.
1216 --
1217 --
1218 if hr_api.not_exists_in_hr_lookups
1219 (p_lookup_type => 'YES_NO',
1220 p_lookup_code => p_no_mx_elcn_val_dfnd_flag,
1221 p_effective_date => p_effective_date) then
1222 --
1223 -- raise error as does not exist as lookup
1224 --
1225 hr_utility.set_message(801,'LOOKUP_NOT_EXIST: no_mx_elcn_val');
1226 hr_utility.raise_error;
1227 --
1228 end if;
1229 --
1230 end if;
1231 --
1232 hr_utility.set_location('Leaving:'||l_proc,10);
1233 --
1234 end chk_no_mx_elcn_val_dfnd_flag;
1235 --
1236 -- ----------------------------------------------------------------------------
1237 -- |------------------------< chk_no_mn_elcn_val_dfnd_flag >------------------|
1238 -- ----------------------------------------------------------------------------
1239 --
1240 -- Description
1241 -- This procedure is used to check that the lookup value is valid.
1242 --
1243 -- Pre Conditions
1244 -- None.
1245 --
1246 -- In Parameters
1247 -- vrbl_rt_prfl_id PK of record being inserted or updated.
1248 -- no_mn_elcn_val_dfnd_flag Value of lookup code.
1249 -- effective_date effective date
1250 -- object_version_number Object version number of record being
1251 -- inserted or updated.
1252 --
1253 -- Post Success
1254 -- Processing continues
1255 --
1256 -- Post Failure
1257 -- Error handled by procedure
1258 --
1259 -- Access Status
1260 -- Internal table handler use only.
1261 --
1262 Procedure chk_no_mn_elcn_val_dfnd_flag
1263 (p_vrbl_rt_prfl_id in number,
1264 p_no_mn_elcn_val_dfnd_flag in varchar2,
1265 p_effective_date in date,
1266 p_object_version_number in number) is
1267 --
1268 l_proc varchar2(72) := g_package||'chk_no_mn_elcn_val_dfnd_flag';
1269 l_api_updating boolean;
1270 --
1271 Begin
1272 --
1273 hr_utility.set_location('Entering:'||l_proc, 5);
1274 --
1275 l_api_updating := ben_vpf_shd.api_updating
1276 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
1277 p_effective_date => p_effective_date,
1278 p_object_version_number => p_object_version_number);
1279 --
1280 if (l_api_updating
1281 and p_no_mn_elcn_val_dfnd_flag
1282 <> nvl(ben_vpf_shd.g_old_rec.no_mn_elcn_val_dfnd_flag,hr_api.g_varchar2)
1283 or not l_api_updating) then
1284 --
1285 -- check if value of lookup falls within lookup type.
1286 --
1287 --
1288 if hr_api.not_exists_in_hr_lookups
1289 (p_lookup_type => 'YES_NO',
1290 p_lookup_code => p_no_mn_elcn_val_dfnd_flag,
1291 p_effective_date => p_effective_date) then
1292 --
1293 -- raise error as does not exist as lookup
1294 --
1295 hr_utility.set_message(801,'LOOKUP_NOT_EXIST: no_mn_elcn_val');
1296 hr_utility.raise_error;
1297 --
1298 end if;
1299 --
1300 end if;
1301 --
1302 hr_utility.set_location('Leaving:'||l_proc,10);
1303 --
1304 end chk_no_mn_elcn_val_dfnd_flag;
1305 --
1306 -- ----------------------------------------------------------------------------
1307 -- |------------------------< chk_alwys_sum_all_cvg_flag >------------------|
1308 -- ----------------------------------------------------------------------------
1309 --
1310 -- Description
1311 -- This procedure is used to check that the lookup value is valid.
1312 --
1313 -- Pre Conditions
1314 -- None.
1315 --
1316 -- In Parameters
1317 -- vrbl_rt_prfl_id PK of record being inserted or updated.
1318 -- alwys_sum_all_cvg_flag Value of lookup code.
1319 -- effective_date effective date
1320 -- object_version_number Object version number of record being
1321 -- inserted or updated.
1322 --
1323 -- Post Success
1324 -- Processing continues
1325 --
1326 -- Post Failure
1327 -- Error handled by procedure
1328 --
1329 -- Access Status
1330 -- Internal table handler use only.
1331 --
1332 Procedure chk_alwys_sum_all_cvg_flag
1333 (p_vrbl_rt_prfl_id in number,
1334 p_alwys_sum_all_cvg_flag in varchar2,
1335 p_effective_date in date,
1336 p_object_version_number in number) is
1337 --
1338 l_proc varchar2(72) := g_package||'chk_alwys_sum_all_cvg_flag';
1339 l_api_updating boolean;
1340 --
1341 Begin
1342 --
1343 hr_utility.set_location('Entering:'||l_proc, 5);
1344 --
1345 l_api_updating := ben_vpf_shd.api_updating
1346 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
1347 p_effective_date => p_effective_date,
1348 p_object_version_number => p_object_version_number);
1349 --
1350 if (l_api_updating
1351 and p_alwys_sum_all_cvg_flag
1352 <> nvl(ben_vpf_shd.g_old_rec.alwys_sum_all_cvg_flag,hr_api.g_varchar2)
1353 or not l_api_updating) then
1354 --
1355 -- check if value of lookup falls within lookup type.
1356 --
1357 --
1358 if hr_api.not_exists_in_hr_lookups
1359 (p_lookup_type => 'YES_NO',
1360 p_lookup_code => p_alwys_sum_all_cvg_flag,
1361 p_effective_date => p_effective_date) then
1362 --
1363 -- raise error as does not exist as lookup
1364 --
1365 hr_utility.set_message(801,'LOOKUP_NOT_EXIST: alwys_sum_all_cvg');
1366 hr_utility.raise_error;
1367 --
1368 end if;
1369 --
1370 end if;
1371 --
1372 hr_utility.set_location('Leaving:'||l_proc,10);
1373 --
1374 end chk_alwys_sum_all_cvg_flag;
1375 --
1376 -- ----------------------------------------------------------------------------
1377 -- |------------------------< chk_alwys_cnt_all_prtts_flag >------------------|
1378 -- ----------------------------------------------------------------------------
1379 --
1380 -- Description
1381 -- This procedure is used to check that the lookup value is valid.
1382 --
1383 -- Pre Conditions
1384 -- None.
1385 --
1386 -- In Parameters
1387 -- vrbl_rt_prfl_id PK of record being inserted or updated.
1388 -- alwys_cnt_all_prtts_flag Value of lookup code.
1389 -- effective_date effective date
1390 -- object_version_number Object version number of record being
1391 -- inserted or updated.
1392 --
1393 -- Post Success
1394 -- Processing continues
1395 --
1396 -- Post Failure
1397 -- Error handled by procedure
1398 --
1399 -- Access Status
1400 -- Internal table handler use only.
1401 --
1402 Procedure chk_alwys_cnt_all_prtts_flag
1403 (p_vrbl_rt_prfl_id in number,
1404 p_alwys_cnt_all_prtts_flag in varchar2,
1405 p_effective_date in date,
1406 p_object_version_number in number) is
1407 --
1408 l_proc varchar2(72) := g_package||'chk_alwys_cnt_all_prtts_flag';
1409 l_api_updating boolean;
1410 --
1411 Begin
1412 --
1413 hr_utility.set_location('Entering:'||l_proc, 5);
1414 --
1415 l_api_updating := ben_vpf_shd.api_updating
1416 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
1417 p_effective_date => p_effective_date,
1418 p_object_version_number => p_object_version_number);
1419 --
1420 if (l_api_updating
1421 and p_alwys_cnt_all_prtts_flag
1422 <> nvl(ben_vpf_shd.g_old_rec.alwys_cnt_all_prtts_flag,hr_api.g_varchar2)
1423 or not l_api_updating) then
1424 --
1425 -- check if value of lookup falls within lookup type.
1426 --
1427 --
1428 if hr_api.not_exists_in_hr_lookups
1429 (p_lookup_type => 'YES_NO',
1430 p_lookup_code => p_alwys_cnt_all_prtts_flag,
1431 p_effective_date => p_effective_date) then
1432 --
1433 -- raise error as does not exist as lookup
1434 --
1435 hr_utility.set_message(801,'LOOKUP_NOT_EXIST: alwys_cnt_all_prt');
1436 hr_utility.raise_error;
1437 --
1438 end if;
1439 --
1440 end if;
1441 --
1442 hr_utility.set_location('Leaving:'||l_proc,10);
1443 --
1444 end chk_alwys_cnt_all_prtts_flag;
1445 --
1446 -- ----------------------------------------------------------------------------
1447 -- |---------------------------< chk_mlt_cd >---------------------------------|
1448 -- ----------------------------------------------------------------------------
1449 --
1450 -- Description
1451 -- This procedure is used to check that the lookup value is valid.
1452 --
1453 -- Pre Conditions
1454 -- None.
1455 --
1456 -- In Parameters
1457 -- vrbl_rt_prfl_id PK of record being inserted or updated.
1458 -- mlt_cd Value of lookup code.
1459 -- effective_date effective date
1460 -- object_version_number Object version number of record being
1461 -- inserted or updated.
1462 --
1463 -- Post Success
1464 -- Processing continues
1465 --
1466 -- Post Failure
1467 -- Error handled by procedure
1468 --
1469 -- Access Status
1470 -- Internal table handler use only.
1471 --
1472 Procedure chk_mlt_cd(p_vrbl_rt_prfl_id in number,
1473 p_mlt_cd in varchar2,
1474 p_vrbl_usg_cd in varchar2,
1475 p_effective_date in date,
1476 p_object_version_number in number) is
1477 --
1478 l_proc varchar2(72) := g_package||'chk_mlt_cd';
1479 l_api_updating boolean;
1480 l_lookup_code varchar2(20);
1481 --
1482 Begin
1483 --
1484 hr_utility.set_location('Entering:'||l_proc, 5);
1485 --
1486 l_api_updating := ben_vpf_shd.api_updating
1487 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
1488 p_effective_date => p_effective_date,
1489 p_object_version_number => p_object_version_number);
1490 --
1491 if (l_api_updating
1492 and p_mlt_cd
1493 <> nvl(ben_vpf_shd.g_old_rec.mlt_cd,hr_api.g_varchar2)
1494 or not l_api_updating) then
1495 --
1496 -- check if value of lookup falls within lookup type.
1497 --
1498 --
1499 if p_vrbl_usg_cd = 'RT' THEN
1500 l_lookup_code := 'BEN_MLT';
1501 elsif p_vrbl_usg_cd = 'CVG' THEN
1502 l_lookup_code := 'BEN_CVG_MLT';
1503 elsif p_vrbl_usg_cd = 'ACP' THEN
1504 l_lookup_code := 'BEN_ACTL_PREM_MLT';
1505 end if;
1506 --
1507 if hr_api.not_exists_in_hr_lookups
1508 (p_lookup_type => l_lookup_code,
1509 p_lookup_code => p_mlt_cd,
1510 p_effective_date => p_effective_date) then
1511 --
1512 -- raise error as does not exist as lookup
1513 --
1514 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1515 fnd_message.set_token('FIELD', 'p_vrbl_usg_cd');
1516 fnd_message.set_token('TYPE', 'BEN_VRBL_USG_CD');
1517 fnd_message.raise_error;
1518 --
1519 end if;
1520 --
1521 --
1522 -- if mlt_cd is 'TPLPC' then defer the action.
1523 if (p_mlt_cd = 'TPLPC') then
1524 fnd_message.set_name('BEN','BEN_92504_PREM_CALC_MTHD_DFRD');
1525 fnd_message.raise_error;
1526 end if;
1527 --
1528 end if;
1529 --
1530 hr_utility.set_location('Leaving:'||l_proc,10);
1531 --
1532 end chk_mlt_cd;
1533 --
1534 -- ----------------------------------------------------------------------------
1535 -- |----------------------< chk_vrbl_usg_cd >---------------------------------|
1536 -- ----------------------------------------------------------------------------
1537 --
1538 -- Description
1539 -- This procedure is used to check that the lookup value is valid.
1540 --
1541 -- Pre Conditions
1542 -- None.
1543 --
1544 -- In Parameters
1545 -- vrbl_rt_prfl_id PK of record being inserted or updated.
1546 -- vrbl_usg_cd Value of lookup code.
1547 -- effective_date effective date
1548 -- object_version_number Object version number of record being
1549 -- inserted or updated.
1550 --
1551 -- Post Success
1552 -- Processing continues
1553 --
1554 -- Post Failure
1555 -- Error handled by procedure
1556 --
1557 -- Access Status
1558 -- Internal table handler use only.
1559 --
1560 Procedure chk_vrbl_usg_cd(p_vrbl_rt_prfl_id in number,
1561 p_vrbl_usg_cd in varchar2,
1562 p_effective_date in date,
1563 p_object_version_number in number) is
1564 --
1565 l_proc varchar2(72) := g_package||'chk_vrbl_usg_cd';
1566 l_api_updating boolean;
1567 --
1568 Begin
1569 --
1570 hr_utility.set_location('Entering:'||l_proc, 5);
1571 --
1572 l_api_updating := ben_vpf_shd.api_updating
1573 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
1574 p_effective_date => p_effective_date,
1575 p_object_version_number => p_object_version_number);
1576 --
1577 if (l_api_updating
1578 and p_vrbl_usg_cd
1579 <> nvl(ben_vpf_shd.g_old_rec.vrbl_usg_cd,hr_api.g_varchar2)
1580 or not l_api_updating) then
1581 --
1582 -- check if value of lookup falls within lookup type.
1583 --
1584 --
1585 if hr_api.not_exists_in_hr_lookups
1586 (p_lookup_type => 'BEN_VRBL_USG',
1587 p_lookup_code => p_vrbl_usg_cd,
1588 p_effective_date => p_effective_date) then
1589 --
1590 -- raise error as does not exist as lookup
1591 --
1592 hr_utility.set_message(801,'LOOKUP_NOT_EXIST: vrbl_usg_cd');
1593 hr_utility.raise_error;
1594 --
1595 end if;
1596 --
1597 end if;
1598 --
1599 hr_utility.set_location('Leaving:'||l_proc,10);
1600 --
1601 end chk_vrbl_usg_cd;
1602 --
1603 -- ----------------------------------------------------------------------------
1604 -- |--------------------< chk_asmt_to_use_cd >-------------------------------|
1605 -- ----------------------------------------------------------------------------
1606 --
1607 -- Description
1608 -- This procedure is used to check that the lookup value is valid.
1609 --
1610 -- Pre Conditions
1611 -- None.
1612 --
1613 -- In Parameters
1614 -- vrbl_rt_prfl_id PK of record being inserted or updated.
1615 -- asmt_to_use_cd Value of lookup code.
1616 -- effective_date effective date
1617 -- object_version_number Object version number of record being
1618 -- inserted or updated.
1619 --
1620 -- Post Success
1621 -- Processing continues
1622 --
1623 -- Post Failure
1624 -- Error handled by procedure
1625 --
1626 -- Access Status
1627 -- Internal table handler use only.
1628 --
1629 Procedure chk_asmt_to_use_cd(p_vrbl_rt_prfl_id in number,
1630 p_asmt_to_use_cd in varchar2,
1631 p_effective_date in date,
1632 p_object_version_number in number) is
1633 --
1634 l_proc varchar2(72) := g_package||'chk_asmt_to_use_cd';
1635 l_api_updating boolean;
1636 --
1637 Begin
1638 --
1639 hr_utility.set_location('Entering:'||l_proc, 5);
1640 --
1641 l_api_updating := ben_vpf_shd.api_updating
1642 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
1643 p_effective_date => p_effective_date,
1644 p_object_version_number => p_object_version_number);
1645 --
1646 if (l_api_updating
1647 and p_asmt_to_use_cd
1648 <> nvl(ben_vpf_shd.g_old_rec.asmt_to_use_cd,hr_api.g_varchar2)
1649 or not l_api_updating) then
1650 --
1651 -- check if value of lookup falls within lookup type.
1652 --
1653 --
1654 if hr_api.not_exists_in_hr_lookups
1655 (p_lookup_type => 'BEN_ASMT_TO_USE',
1656 p_lookup_code => p_asmt_to_use_cd,
1657 p_effective_date => p_effective_date) then
1658 --
1659 -- raise error as does not exist as lookup
1660 --
1661 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
1662 fnd_message.set_token('FIELD', 'p_asmt_to_use_cd');
1663 fnd_message.set_token('TYPE', 'BEN_ASMT_TO_USE');
1664 fnd_message.raise_error;
1665 --
1666 end if;
1667 --
1668 end if;
1669 --
1670 hr_utility.set_location('Leaving:'||l_proc,10);
1671 --
1672 end chk_asmt_to_use_cd;
1673 --
1674 -- ----------------------------------------------------------------------------
1675 -- |-------------------------<chk_rndg_cd >----------------------------------|
1676 -- ----------------------------------------------------------------------------
1677 --
1678 -- Description
1679 -- This procedure is used to check that the lookup value is valid.
1680 --
1681 -- Pre Conditions
1682 -- None.
1683 --
1684 -- In Parameters
1685 -- vrbl_rt_prfl_id PK of record being inserted or updated.
1686 -- rndg_cd Value of lookup code.
1687 -- effective_date effective date
1688 -- object_version_number Object version number of record being
1689 -- inserted or updated.
1690 --
1691 -- Post Success
1692 -- Processing continues
1693 --
1694 -- Post Failure
1695 -- Error handled by procedure
1696 --
1697 -- Access Status
1698 -- Internal table handler use only.
1699 --
1700 Procedure chk_rndg_cd(p_vrbl_rt_prfl_id in number,
1701 p_rndg_cd in varchar2,
1702 p_effective_date in date,
1703 p_object_version_number in number) is
1704 --
1705 l_proc varchar2(72) := g_package||'chk_rndg_cd';
1706 l_api_updating boolean;
1707 --
1708 Begin
1709 --
1710 hr_utility.set_location('Entering:'||l_proc, 5);
1711 --
1712 l_api_updating := ben_vpf_shd.api_updating
1713 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
1714 p_effective_date => p_effective_date,
1715 p_object_version_number => p_object_version_number);
1716 --
1717 if p_rndg_cd is not null then
1718 if (l_api_updating
1719 and p_rndg_cd
1720 <> nvl(ben_vpf_shd.g_old_rec.rndg_cd,hr_api.g_varchar2)
1721 or not l_api_updating) then
1722 --
1723 -- check if value of lookup falls within lookup type.
1724 --
1725 --
1726 if hr_api.not_exists_in_hr_lookups
1727 (p_lookup_type => 'BEN_RNDG',
1728 p_lookup_code => p_rndg_cd,
1729 p_effective_date => p_effective_date) then
1730 --
1731 -- raise error as does not exist as lookup
1732 --
1733 hr_utility.set_message(801,'LOOKUP_NOT_EXIST: rndg_cd ');
1734 hr_utility.raise_error;
1735 --
1736 end if;
1737 --
1738 end if;
1739 end if;
1740 --
1741 hr_utility.set_location('Leaving:'||l_proc,10);
1742 --
1743 end chk_rndg_cd;
1744 --
1745 -- ----------------------------------------------------------------------------
1746 -- |----------------------< chk_rndg_cd_rl >----------------------------------|
1747 -- ----------------------------------------------------------------------------
1748 --
1749 -- Description
1750 -- This procedure is used to check that the code/rule dependency as the
1751 -- following:
1752 -- If Code = 'Rule' then rule must be selected.
1753 -- If Code <> 'Rule' thne rule must not be selected.
1754 --
1755 -- Pre Conditions
1756 -- None.
1757 --
1758 -- In Parameters
1759 -- rndg_cd Value of look up value.
1760 -- rndg_rl value of look up Value
1761 -- inserted or updated.
1762 --
1763 -- Post Success
1764 -- Processing continues
1765 --
1766 -- Post Failure
1767 -- Error handled by procedure
1768 --
1769 -- Access Status
1770 -- Internal table handler use only.
1771 --
1772 Procedure chk_rndg_cd_rl (p_rndg_cd in varchar2,
1773 p_rndg_rl in number) is
1774 --
1775 l_proc varchar2(72) := g_package||'chk_rndg_cd_rl';
1776 --
1777 Begin
1778 --
1779 hr_utility.set_location('Entering:'||l_proc, 5);
1780 --
1781 -- check dependency of Code and Rule.
1782 --
1783 if (p_rndg_cd <> 'RL' and
1784 p_rndg_rl is not null) then
1785 fnd_message.set_name('BEN','BEN_91732_NO_RNDG_RULE');
1786 fnd_message.raise_error;
1787 end if;
1788
1789 if (p_rndg_cd = 'RL' and p_rndg_rl is null) then
1790 fnd_message.set_name('BEN','BEN_91733_RNDG_RULE');
1791 fnd_message.raise_error;
1792 end if;
1793
1794 --
1795 -- Leaving Procedure.
1796 --
1797 hr_utility.set_location('Leaving:'||l_proc,10);
1798 --
1799 end chk_rndg_cd_rl;
1800 --
1801 -- ----------------------------------------------------------------------------
1802 -- |------------------------< chk_acty_ref_perd_cd >--------------------------|
1803 -- ----------------------------------------------------------------------------
1804 --
1805 -- Description
1806 -- This procedure is used to check that the lookup value is valid.
1807 --
1808 -- Pre Conditions
1809 -- None.
1810 --
1811 -- In Parameters
1812 -- vrbl_rt_prfl_id PK of record being inserted or updated.
1813 -- acty_ref_perd_cd Value of lookup code.
1814 -- effective_date effective date
1815 -- object_version_number Object version number of record being
1816 -- inserted or updated.
1817 --
1818 -- Post Success
1819 -- Processing continues
1820 --
1821 -- Post Failure
1822 -- Error handled by procedure
1823 --
1824 -- Access Status
1825 -- Internal table handler use only.
1826 --
1827 Procedure chk_acty_ref_perd_cd(p_vrbl_rt_prfl_id in number,
1828 p_acty_ref_perd_cd in varchar2,
1829 p_effective_date in date,
1830 p_object_version_number in number) is
1831 --
1832 l_proc varchar2(72) := g_package||'chk_acty_ref_perd_cd';
1833 l_api_updating boolean;
1834 --
1835 Begin
1836 --
1837 hr_utility.set_location('Entering:'||l_proc, 5);
1838 --
1839 l_api_updating := ben_vpf_shd.api_updating
1840 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
1841 p_effective_date => p_effective_date,
1842 p_object_version_number => p_object_version_number);
1843 --
1844 if (l_api_updating
1845 and p_acty_ref_perd_cd
1846 <> nvl(ben_vpf_shd.g_old_rec.acty_ref_perd_cd,hr_api.g_varchar2)
1847 or not l_api_updating) then
1848 --
1849 -- check if value of lookup falls within lookup type.
1850 --
1851 --
1852 if hr_api.not_exists_in_hr_lookups
1853 (p_lookup_type => 'BEN_ACTY_REF_PERD',
1854 p_lookup_code => p_acty_ref_perd_cd,
1855 p_effective_date => p_effective_date) then
1856 --
1857 -- raise error as does not exist as lookup
1858 --
1859 hr_utility.set_message(801,'LOOKUP_NOT_EXIST: acty_ref_perd');
1860 hr_utility.raise_error;
1861 --
1862 end if;
1863 --
1864 end if;
1865 --
1866 hr_utility.set_location('Leaving:'||l_proc,10);
1867 --
1868 end chk_acty_ref_perd_cd;
1869 --
1870 -- ----------------------------------------------------------------------------
1871 -- |------------------------< chk_ref_perd_usg_cd >--------------------------|
1872 -- ----------------------------------------------------------------------------
1873 --
1874 -- Description
1875 -- This procedure is used to check the periodicity is monthly if the usage code is actual_premium
1876 -- Bug #3377
1877 --
1878 -- Pre Conditions
1879 -- None.
1880 --
1881 -- In Parameters
1882 -- acty_ref_perd_cd Value of lookup code.
1883 -- p_vrbl_usg_cd
1884 --
1885 -- Post Success
1886 -- Processing continues
1887 --
1888 -- Post Failure
1889 -- Error handled by procedure
1890 --
1891 -- Access Status
1892 -- Internal table handler use only.
1893 --
1894 Procedure chk_ref_perd_usg_cd(p_acty_ref_perd_cd in varchar2,
1895 p_vrbl_usg_cd in varchar2 ) is
1896 --
1897 l_proc varchar2(72) := g_package||'chk_ref_perd_usg_cd';
1898 --
1899 Begin
1900 --
1901 hr_utility.set_location('Entering:'||l_proc, 5);
1902 --
1903 if p_vrbl_usg_cd = 'ACP' then
1904 if p_acty_ref_perd_cd <> 'MO' then
1905 --
1906 -- raise error as does not exist as lookup
1907 --
1908 fnd_message.set_name('BEN','BEN_92439_REF_PERD_USAGE');
1909 fnd_message.raise_error;
1910 --
1911 end if;
1912 --
1913 end if;
1914 --
1915 hr_utility.set_location('Leaving:'||l_proc,10);
1916 --
1917 end chk_ref_perd_usg_cd;
1918 --
1919 -- ----------------------------------------------------------------------------
1920 -- |------------------------< chk_val_othr_val >--------------------------|
1921 -- ----------------------------------------------------------------------------
1922 --
1923 -- Description
1924 -- This procedure is used to check if val field is not null then the other val must
1925 -- be null and if any of the other val is not null then val must be null
1926 --
1927 -- Pre Conditions
1928 -- None.
1929 --
1930 -- In Parameters
1931 -- p_val, p_mn_elcn_val, p_mx_elcn_val, p_incrmnt_elcn_val, p_dflt_elcn_val
1932 --
1933 -- Post Success
1934 -- Processing continues
1935 --
1936 -- Post Failure
1937 -- Error handled by procedure
1938 --
1939 -- Access Status
1940 -- Internal table handler use only.
1941 --
1942 Procedure chk_val_othr_val(p_val in number,
1943 p_mn_elcn_val in number,
1944 p_mx_elcn_val in number,
1945 p_incrmnt_elcn_val in number,
1946 p_dflt_elcn_val in number,
1947 p_mlt_cd in varchar2) is
1948 --
1949 l_proc varchar2(72) := g_package||'chk_val_othr_val';
1950 --
1951 Begin
1952 --
1953 hr_utility.set_location('Entering:'||l_proc, 5);
1954 --
1955 -- FLFXPCL uses both p_val and p_mn_elcn_val.
1956 --
1957 if p_val is not null and p_mlt_cd <> 'FLFXPCL' then
1958 if p_mn_elcn_val is not null OR
1959 p_mx_elcn_val is not null OR
1960 p_incrmnt_elcn_val is not null OR
1961 p_dflt_elcn_val is not null then
1962 --
1963 -- raise error as does not exist as lookup
1964 --
1965 fnd_message.set_name('BEN','BEN_92440_VAL_VS_OTHERS');
1966 fnd_message.raise_error;
1967 --
1968 end if;
1969 --
1970 end if;
1971 --
1972 if p_mn_elcn_val is not null OR p_mx_elcn_val is not null OR
1973 p_incrmnt_elcn_val is not null OR p_dflt_elcn_val is not null then
1974 if p_val is not null and p_mlt_cd <> 'FLFXPCL' then
1975 --
1976 -- raise error as does not exist as lookup
1977 --
1978 fnd_message.set_name('BEN','BEN_92440_VAL_VS_OTHERS');
1979 fnd_message.raise_error;
1980 --
1981 end if;
1982 --
1983 end if;
1984 --
1985 hr_utility.set_location('Leaving:'||l_proc,10);
1986 --
1987 end chk_val_othr_val;
1988 --
1989 -- ----------------------------------------------------------------------------
1990 -- |-----------------------< chk_vrbl_rt_trtmt_cd >---------------------------|
1991 -- ----------------------------------------------------------------------------
1992 --
1993 -- Description
1994 -- This procedure is used to check that the lookup value is valid.
1995 --
1996 -- Pre Conditions
1997 -- None.
1998 --
1999 -- In Parameters
2000 -- vrbl_rt_prfl_id PK of record being inserted or updated.
2001 -- vrbl_rt_trtmt_cd Value of lookup code.
2002 -- effective_date effective date
2003 -- object_version_number Object version number of record being
2004 -- inserted or updated.
2005 --
2006 -- Post Success
2007 -- Processing continues
2008 --
2009 -- Post Failure
2010 -- Error handled by procedure
2011 --
2012 -- Access Status
2013 -- Internal table handler use only.
2014 --
2015 Procedure chk_vrbl_rt_trtmt_cd(p_vrbl_rt_prfl_id in number,
2016 p_vrbl_rt_trtmt_cd in varchar2,
2017 p_effective_date in date,
2018 p_object_version_number in number) is
2019 --
2020 l_proc varchar2(72) := g_package||'chk_vrbl_rt_trtmt_cd';
2021 l_api_updating boolean;
2022 --
2023 Begin
2024 --
2025 hr_utility.set_location('Entering:'||l_proc, 5);
2026 --
2027 l_api_updating := ben_vpf_shd.api_updating
2028 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
2029 p_effective_date => p_effective_date,
2030 p_object_version_number => p_object_version_number);
2031 --
2032 if (l_api_updating
2033 and p_vrbl_rt_trtmt_cd
2034 <> nvl(ben_vpf_shd.g_old_rec.vrbl_rt_trtmt_cd,hr_api.g_varchar2)
2035 or not l_api_updating) then
2036 --
2037 -- check if value of lookup falls within lookup type.
2038 --
2039 --
2040 if hr_api.not_exists_in_hr_lookups
2041 (p_lookup_type => 'BEN_RT_TRTMT',
2042 p_lookup_code => p_vrbl_rt_trtmt_cd,
2043 p_effective_date => p_effective_date) then
2044 --
2045 -- raise error as does not exist as lookup
2046 --
2047 hr_utility.set_message(801,'LOOKUP_NOT_EXIST: rt_trmnt');
2048 hr_utility.raise_error;
2049 --
2050 end if;
2051 --
2052 end if;
2053 --
2054 hr_utility.set_location('Leaving:'||l_proc,10);
2055 --
2056 end chk_vrbl_rt_trtmt_cd;
2057 --
2058 -- ----------------------------------------------------------------------------
2059 -- |----------------------------< chk_tx_typ_cd >-----------------------------|
2060 -- ----------------------------------------------------------------------------
2061 --
2062 -- Description
2063 -- This procedure is used to check that the lookup value is valid.
2064 --
2065 -- Pre Conditions
2066 -- None.
2067 --
2068 -- In Parameters
2069 -- vrbl_rt_prfl_id PK of record being inserted or updated.
2070 -- tx_typ_cd Value of lookup code.
2071 -- effective_date effective date
2072 -- object_version_number Object version number of record being
2073 -- inserted or updated.
2074 --
2075 -- Post Success
2076 -- Processing continues
2077 --
2078 -- Post Failure
2079 -- Error handled by procedure
2080 --
2081 -- Access Status
2082 -- Internal table handler use only.
2083 --
2084 Procedure chk_tx_typ_cd(p_vrbl_rt_prfl_id in number,
2085 p_tx_typ_cd in varchar2,
2086 p_effective_date in date,
2087 p_object_version_number in number) is
2088 --
2089 l_proc varchar2(72) := g_package||'chk_tx_typ_cd';
2090 l_api_updating boolean;
2091 --
2092 Begin
2093 --
2094 hr_utility.set_location('Entering:'||l_proc, 5);
2095 --
2096 l_api_updating := ben_vpf_shd.api_updating
2097 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
2098 p_effective_date => p_effective_date,
2099 p_object_version_number => p_object_version_number);
2100 --
2101 if (l_api_updating
2102 and p_tx_typ_cd
2103 <> nvl(ben_vpf_shd.g_old_rec.tx_typ_cd,hr_api.g_varchar2)
2104 or not l_api_updating) then
2105 --
2106 -- check if value of lookup falls within lookup type.
2107 --
2108 --
2109 hr_utility.set_location('lookup code is'||p_tx_typ_cd,20);
2110 if hr_api.not_exists_in_hr_lookups
2111 (p_lookup_type => 'BEN_TX_TYP',
2112 p_lookup_code => p_tx_typ_cd,
2113 p_effective_date => p_effective_date) then
2114 --
2115 -- raise error as does not exist as lookup
2116 --
2117 hr_utility.set_message(801,'LOOKUP_NOT_EXIST: tx_typ');
2118 hr_utility.raise_error;
2119 --
2120 end if;
2121 --
2122 end if;
2123 --
2124 hr_utility.set_location('Leaving:'||l_proc,10);
2125 --
2126 end chk_tx_typ_cd;
2127 --
2128 -- ----------------------------------------------------------------------------
2129 -- |--------------------------< chk_bnft_rt_typ_cd >--------------------------|
2130 -- ----------------------------------------------------------------------------
2131 --
2132 -- Description
2133 -- This procedure is used to check that the lookup value is valid.
2134 --
2135 -- Pre Conditions
2136 -- None.
2137 --
2138 -- In Parameters
2139 -- vrbl_rt_prfl_id PK of record being inserted or updated.
2140 -- bnft_rt_typ_cd Value of lookup code.
2141 -- effective_date effective date
2142 -- object_version_number Object version number of record being
2143 -- inserted or updated.
2144 --
2145 -- Post Success
2146 -- Processing continues
2147 --
2148 -- Post Failure
2149 -- Error handled by procedure
2150 --
2151 -- Access Status
2152 -- Internal table handler use only.
2153 --
2154 Procedure chk_bnft_rt_typ_cd(p_vrbl_rt_prfl_id in number,
2155 p_bnft_rt_typ_cd in varchar2,
2156 p_effective_date in date,
2157 p_object_version_number in number) is
2158 --
2159 l_proc varchar2(72) := g_package||'chk_bnft_rt_typ_cd';
2160 l_api_updating boolean;
2161 --
2162 Begin
2163 --
2164 hr_utility.set_location('Entering:'||l_proc, 5);
2165 --
2166 l_api_updating := ben_vpf_shd.api_updating
2167 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
2168 p_effective_date => p_effective_date,
2169 p_object_version_number => p_object_version_number);
2170 --
2171 if (l_api_updating
2172 and p_bnft_rt_typ_cd
2173 <> nvl(ben_vpf_shd.g_old_rec.bnft_rt_typ_cd,hr_api.g_varchar2)
2174 or not l_api_updating)
2175 and p_bnft_rt_typ_cd is not null then
2176 --
2177 -- check if value of lookup falls within lookup type.
2178 --
2179 if hr_api.not_exists_in_hr_lookups
2180 (p_lookup_type => 'BEN_RT_TYP',
2181 p_lookup_code => p_bnft_rt_typ_cd,
2182 p_effective_date => p_effective_date) then
2183 --
2184 -- raise error as does not exist as lookup
2185 --
2186 hr_utility.set_message(801,'LOOKUP_NOT_EXIST: bnft_rt_typ');
2187 hr_utility.raise_error;
2188 --
2189 end if;
2190 --
2191 end if;
2192 --
2193 hr_utility.set_location('Leaving:'||l_proc,10);
2194 --
2195 end chk_bnft_rt_typ_cd;
2196 --
2197 -- ----------------------------------------------------------------------------
2198 -- |---------------------------< chk_rt_typ_cd >------------------------------|
2199 -- ----------------------------------------------------------------------------
2200 --
2201 -- Description
2202 -- This procedure is used to check that the lookup value is valid.
2203 --
2204 -- Pre Conditions
2205 -- None.
2206 --
2207 -- In Parameters
2208 -- vrbl_rt_prfl_id PK of record being inserted or updated.
2209 -- rt_typ_cd Value of lookup code.
2210 -- effective_date effective date
2211 -- object_version_number Object version number of record being
2212 -- inserted or updated.
2213 --
2214 -- Post Success
2215 -- Processing continues
2216 --
2217 -- Post Failure
2218 -- Error handled by procedure
2219 --
2220 -- Access Status
2221 -- Internal table handler use only.
2222 --
2223 Procedure chk_rt_typ_cd(p_vrbl_rt_prfl_id in number,
2224 p_rt_typ_cd in varchar2,
2225 p_effective_date in date,
2226 p_object_version_number in number) is
2227 --
2228 l_proc varchar2(72) := g_package||'chk_rt_typ_cd';
2229 l_api_updating boolean;
2230 --
2231 Begin
2232 --
2233 hr_utility.set_location('Entering:'||l_proc, 5);
2234 --
2235 l_api_updating := ben_vpf_shd.api_updating
2236 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
2237 p_effective_date => p_effective_date,
2238 p_object_version_number => p_object_version_number);
2239 --
2240 if (l_api_updating
2241 and p_rt_typ_cd
2242 <> nvl(ben_vpf_shd.g_old_rec.rt_typ_cd,hr_api.g_varchar2)
2243 or not l_api_updating)
2244 and p_rt_typ_cd is not null then
2245 --
2246 -- check if value of lookup falls within lookup type.
2247 --
2248 if hr_api.not_exists_in_hr_lookups
2249 (p_lookup_type => 'BEN_RT_TYP',
2250 p_lookup_code => p_rt_typ_cd,
2251 p_effective_date => p_effective_date) then
2252 --
2253 -- raise error as does not exist as lookup
2254 --
2255 hr_utility.set_message(801,'LOOKUP_NOT_EXIST: rt_typ');
2256 hr_utility.raise_error;
2257 --
2258 end if;
2259 --
2260 end if;
2261 --
2262 hr_utility.set_location('Leaving:'||l_proc,10);
2263 --
2264 end chk_rt_typ_cd;
2265 --
2266 -- ----------------------------------------------------------------------------
2267 -- |-------------------------< chk_acty_typ_cd >------------------------------|
2268 -- ----------------------------------------------------------------------------
2269 --
2270 -- Description
2271 -- This procedure is used to check that the lookup value is valid.
2272 --
2273 -- Pre Conditions
2274 -- None.
2275 --
2276 -- In Parameters
2277 -- vrbl_rt_prfl_id PK of record being inserted or updated.
2278 -- acty_typ_cd Value of lookup code.
2279 -- effective_date effective date
2280 -- object_version_number Object version number of record being
2281 -- inserted or updated.
2282 --
2283 -- Post Success
2284 -- Processing continues
2285 --
2286 -- Post Failure
2287 -- Error handled by procedure
2288 --
2289 -- Access Status
2290 -- Internal table handler use only.
2291 --
2292 Procedure chk_acty_typ_cd(p_vrbl_rt_prfl_id in number,
2293 p_acty_typ_cd in varchar2,
2294 p_effective_date in date,
2295 p_object_version_number in number) is
2296 --
2297 l_proc varchar2(72) := g_package||'chk_acty_typ_cd';
2298 l_api_updating boolean;
2299 --
2300 Begin
2301 --
2302 hr_utility.set_location('Entering:'||l_proc, 5);
2303 --
2304 l_api_updating := ben_vpf_shd.api_updating
2305 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
2306 p_effective_date => p_effective_date,
2307 p_object_version_number => p_object_version_number);
2308 --
2309 if (l_api_updating
2310 and p_acty_typ_cd
2311 <> nvl(ben_vpf_shd.g_old_rec.acty_typ_cd,hr_api.g_varchar2)
2312 or not l_api_updating)
2313 and p_acty_typ_cd is not null then
2314 --
2315 -- check if value of lookup falls within lookup type.
2316 --
2317 --
2318 if hr_api.not_exists_in_hr_lookups
2319 (p_lookup_type => 'BEN_ACTY_TYP',
2320 p_lookup_code => p_acty_typ_cd,
2321 p_effective_date => p_effective_date) then
2322 --
2323 -- raise error as does not exist as lookup
2324 --
2325 hr_utility.set_message(801,'LOOKUP_NOT_EXIST: acty_typ');
2326 hr_utility.raise_error;
2327 --
2328 end if;
2329 --
2330 end if;
2331 --
2332 hr_utility.set_location('Leaving:'||l_proc,10);
2333 --
2334 end chk_acty_typ_cd;
2335 --
2336 -- ----------------------------------------------------------------------------
2337 -- |-----------------------< chk_alwys_cnt_all_prtts_def >-------------------|
2338 -- ----------------------------------------------------------------------------
2339 --
2340 -- Description
2341 -- This procedure is used to check that if the Always Count All
2342 -- Participants Flag is Y then a TTL_PRTT_RT_F record must have
2343 -- been defined.
2344 --
2345 -- Pre Conditions
2346 -- None.
2347 --
2348 -- In Parameters
2349 -- vrbl_rt_prfl_id PK of record being inserted or updated.
2350 -- alwys_cnt_all_prtts_flag
2351 -- effective_date effective date
2352 --
2353 -- Post Success
2354 -- Processing continues
2355 --
2356 -- Post Failure
2357 -- Error handled by procedure
2358 --
2359 --
2360 -- Access Status
2361 -- Internal table handler use only.
2362 --
2363 Procedure chk_alwys_cnt_all_prtts_def(p_vrbl_rt_prfl_id in number,
2364 p_alwys_cnt_all_prtts_flag in varchar2,
2365 p_effective_date in date,
2366 p_business_group_id in number) is
2367 --
2368 l_proc varchar2(72) := g_package||'chk_alwys_cnt_all_prtts_def';
2369 l_dummy char(1);
2370 cursor c1 is select null
2371 from ben_ttl_prtt_rt_f
2372 Where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
2373 and business_group_id = p_business_group_id
2374 and p_effective_date between effective_start_date
2375 and effective_end_date;
2376 --
2377 Begin
2378 --
2379 hr_utility.set_location('Entering:'||l_proc, 5);
2380 --
2381 If p_alwys_cnt_all_prtts_flag = 'Y' then
2382 open c1;
2383 fetch c1 into l_dummy;
2384 if c1%notfound then
2385 close c1;
2386 fnd_message.set_name('BEN','BEN_92265_PRTT_CRIT_NOTDEF');
2387 fnd_message.raise_error;
2388 end if;
2389 --
2390 close c1;
2391 --
2392 end if;
2393 --
2394 hr_utility.set_location('Leaving:'||l_proc,10);
2395 --
2396 end chk_alwys_cnt_all_prtts_def;
2397 --
2398 -- ----------------------------------------------------------------------------
2399 -- |---------------------< chk_elig_alwys_cnt_all_prtt_df >-----------------|
2400 -- ----------------------------------------------------------------------------
2401 -- Added for checks during usage of 'Eligbility Profiles' for defining
2402 -- criteria for calculation of Variable Coverages and Variable Actual Premiums.
2403 -- Bug : 3456400
2404 --
2405 -- Description
2406 -- This procedure is used to check that if the Always Count All
2407 -- Participants Flag is Y then a BEN_ELIG_TTL_PRTT_PRTE_F record must have
2408 -- been defined with the record (ELPRO) being attached to Variable Rate Profile
2409 -- (VAPRO) identified by p_vrbl_rt_prfl_id.
2410 --
2411 -- Pre Conditions
2412 -- None.
2413 --
2414 -- In Parameters
2415 -- vrbl_rt_prfl_id PK of record being inserted or updated.
2416 -- alwys_cnt_all_prtts_flag
2417 -- effective_date
2418 -- business_group_id
2419 --
2420 -- Post Success
2421 -- Processing continues
2422 --
2423 -- Post Failure
2424 -- Error handled by procedure
2425 --
2426 --
2427 -- Access Status
2428 -- Internal table handler use only.
2429 --
2430 Procedure chk_elig_alwys_cnt_all_prtt_df(p_vrbl_rt_prfl_id in number,
2431 p_alwys_cnt_all_prtts_flag in varchar2,
2432 p_effective_date in date,
2433 p_business_group_id in number) is
2434 --
2435 l_proc varchar2(72) := g_package||'chk_elig_alwys_cnt_all_prtt_df';
2436 l_dummy char(1);
2437 cursor c1 is select null
2438 from ben_elig_ttl_prtt_prte_f etp, ben_vrbl_rt_elig_prfl_f vep
2439 where vep.eligy_prfl_id = etp.eligy_prfl_id
2440 and vep.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
2441 and vep.business_group_id = p_business_Group_id
2442 and p_effective_date between vep.effective_start_date and vep.effective_end_date
2443 and etp.business_group_id = p_business_Group_id
2444 and p_effective_date between etp.effective_start_date and etp.effective_end_date;
2445 --
2446 Begin
2447 --
2448 hr_utility.set_location('Entering:'||l_proc, 5);
2449 --
2450 If p_alwys_cnt_all_prtts_flag = 'Y' then
2451 open c1;
2452 fetch c1 into l_dummy;
2453 if c1%notfound then
2454 close c1;
2455 fnd_message.set_name('BEN','BEN_92265_PRTT_CRIT_NOTDEF');
2456 fnd_message.raise_error;
2457 end if;
2458 --
2459 close c1;
2460 --
2461 end if;
2462 --
2463 hr_utility.set_location('Leaving:'||l_proc,10);
2464 --
2465 end chk_elig_alwys_cnt_all_prtt_df;
2466
2467
2468 --
2469 -- ----------------------------------------------------------------------------
2470 -- |-----------------------< chk_alwys_sum_all_cvg_def >----------------------|
2471 -- ----------------------------------------------------------------------------
2472 --
2473 -- Description
2474 -- This procedure is used to check that if the Always Sum All
2475 -- Coverage Flag is Y then a TTL_CVG_VOL_RT_F record must have
2476 -- been defined.
2477 --
2478 -- Pre Conditions
2479 -- None.
2480 --
2481 -- In Parameters
2482 -- vrbl_rt_prfl_id PK of record being inserted or updated.
2483 -- alwys_sum_all_cvg_flag
2484 -- effective_date effective date
2485 --
2486 --
2487 -- Post Success
2488 -- Processing continues
2489 --
2490 -- Post Failure
2491 -- Error handled by procedure
2492 --
2493 -- Access Status
2494 -- Internal table handler use only.
2495 --
2496 Procedure chk_alwys_sum_all_cvg_def(p_vrbl_rt_prfl_id in number,
2497 p_alwys_sum_all_cvg_flag in varchar2,
2498 p_effective_date in date,
2499 p_business_group_id in number) is
2500 --
2501 l_proc varchar2(72) := g_package||'chk_alwys_sum_all_cvg_def';
2502 l_dummy char(1);
2503 cursor c1 is select null
2504 from ben_ttl_cvg_vol_rt_f
2505 Where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
2506 and business_group_id = p_business_group_id
2507 and p_effective_date between effective_start_date
2508 and effective_end_date;
2509 --
2510 Begin
2511 --
2512 hr_utility.set_location('Entering:'||l_proc, 5);
2513 --
2514 If p_alwys_sum_all_cvg_flag = 'Y' then
2515 open c1;
2516 fetch c1 into l_dummy;
2517 if c1%notfound then
2518 close c1;
2519 fnd_message.set_name('BEN','BEN_92266_CVG_CRIT_NOTDEF');
2520 fnd_message.raise_error;
2521 end if;
2522 --
2523 close c1;
2524 --
2525 end if;
2526 --
2527 hr_utility.set_location('Leaving:'||l_proc,10);
2528 --
2529 end chk_alwys_sum_all_cvg_def;
2530 --
2531 -- ----------------------------------------------------------------------------
2532 -- |---------------------< chk_elig_alwys_sum_all_cvg_def >-------------------|
2533 -- ----------------------------------------------------------------------------
2534 -- Added for checks during usage of 'Eligbility Profiles' for defining
2535 -- criteria for calculation of Variable Coverages and Variable Actual Premiums.
2536 -- Bug : 3456400
2537 --
2538 -- Description
2539 -- This procedure is used to check that if the Always Sum All
2540 -- Coverage Flag is Y then a BEN_ELIG_TTL_CVG_VOL_PRTE_F record must have
2541 -- been defined with the record (ELPRO) attached to Variable Rate Profile
2542 -- (VAPRO) identified by p_vrbl_rt_prfl_id.
2543 --
2544 -- Pre Conditions
2545 -- None.
2546 --
2547 -- In Parameters
2548 -- vrbl_rt_prfl_id PK of record being inserted or updated.
2549 -- alwys_sum_all_cvg_flag
2550 -- effective_date
2551 -- business_group_id
2552 --
2553 -- Post Success
2554 -- Processing continues
2555 --
2556 -- Post Failure
2557 -- Error handled by procedure
2558 --
2559 -- Access Status
2560 -- Internal table handler use only.
2561 --
2562 Procedure chk_elig_alwys_sum_all_cvg_def(p_vrbl_rt_prfl_id in number,
2563 p_alwys_sum_all_cvg_flag in varchar2,
2564 p_effective_date in date,
2565 p_business_group_id in number) is
2566 --
2567 l_proc varchar2(72) := g_package||'chk_elig_alwys_sum_all_cvg_def';
2568 l_dummy char(1);
2569 cursor c1 is select null
2570 from ben_elig_ttl_cvg_vol_prte_f etc, ben_vrbl_rt_elig_prfl_f vep
2571 where vep.eligy_prfl_id = etc.eligy_prfl_id
2572 and vep.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
2573 and vep.business_group_id = p_business_Group_id
2574 and p_effective_date between vep.effective_start_date and vep.effective_end_date
2575 and etc.business_group_id = p_business_Group_id
2576 and p_effective_date between etc.effective_start_date and etc.effective_end_date;
2577 --
2578 Begin
2579 --
2580 hr_utility.set_location('Entering:'||l_proc, 5);
2581 --
2582 If p_alwys_sum_all_cvg_flag = 'Y' then
2583 open c1;
2584 fetch c1 into l_dummy;
2585 if c1%notfound then
2586 close c1;
2587 fnd_message.set_name('BEN','BEN_92266_CVG_CRIT_NOTDEF');
2588 fnd_message.raise_error;
2589 end if;
2590 --
2591 close c1;
2592 --
2593 end if;
2594 --
2595 hr_utility.set_location('Leaving:'||l_proc,10);
2596 --
2597 end chk_elig_alwys_sum_all_cvg_def;
2598
2599 --
2600 --
2601 -- ----------------------------------------------------------------------------
2602 -- |-----------------------< chk_usg_acty_tx_for_prem >-----------------------|
2603 -- ----------------------------------------------------------------------------
2604 --
2605 -- Description
2606 -- This procedure is used to check that if vrbl_usg_cd = 'ACP' Actual Premium
2607 -- then tx_typ_cd must be 'NOTAPPLICABLE' and if vrbl_usg_cd is not = 'ACP'
2608 -- then acty_typ_cd is required.
2609 --
2610 -- Pre Conditions
2611 -- None.
2612 --
2613 -- In Parameters
2614 -- vrbl_usg_cd
2615 -- acty_typ_cd
2616 -- tx_typ_cd
2617 --
2618 -- Post Success
2619 -- Processing continues
2620 --
2621 -- Post Failure
2622 -- Error handled by procedure
2623 --
2624 -- Access Status
2625 -- Internal table handler use only.
2626 --
2627 Procedure chk_usg_acty_tx_for_prem(p_vrbl_usg_cd in varchar2,
2628 p_acty_typ_cd in varchar2,
2629 p_tx_typ_cd in varchar2) is
2630 --
2631 l_proc varchar2(72) := g_package||'chk_usg_acty_tx_for_prem';
2632 --
2633 Begin
2634 --
2635 hr_utility.set_location('Entering:'||l_proc, 5);
2636 --
2637 If p_vrbl_usg_cd = 'ACP' then
2638 if p_tx_typ_cd <> 'NOTAPPLICABLE' then
2639 fnd_message.set_name('BEN','BEN_92260_TX_TYP_MUST_NOTAPP');
2640 fnd_message.raise_error;
2641 end if;
2642 elsif p_acty_typ_cd is null then
2643 fnd_message.set_name('BEN','BEN_92261_ACTY_TYP_RQD');
2644 fnd_message.raise_error;
2645 end if;
2646 --
2647 hr_utility.set_location('Leaving:'||l_proc,10);
2648 --
2649 end chk_usg_acty_tx_for_prem;
2650 --
2651 -- ----------------------------------------------------------------------------
2652 -- |-----------------------< chk_alwys_flag_one_def >--------------------------
2653 -- ----------------------------------------------------------------------------
2654 --
2655 -- Description
2656 -- This procedure is used to check that only one of alwys_cnt_all_prtts_flag
2657 -- or alwys_sum_all_cvg_flag is set to 'Y'.
2658 --
2659 -- Pre Conditions
2660 -- None.
2661 --
2662 -- In Parameters
2663 -- alwys_cnt_all_prtts_flag
2664 -- alwys_sum_all_cvg_flag
2665 --
2666 -- Post Success
2667 -- Processing continues
2668 --
2669 -- Post Failure
2670 -- Error handled by procedure
2671 --
2672 -- Access Status
2673 -- Internal table handler use only.
2674 --
2675 Procedure chk_alwys_flag_one_def(p_alwys_cnt_all_prtts_flag in varchar2,
2676 p_alwys_sum_all_cvg_flag in varchar2) is
2677 --
2678 l_proc varchar2(72) := g_package||'chk_alwys_flag_one_def';
2679 --
2680 Begin
2681 --
2682 hr_utility.set_location('Entering:'||l_proc, 5);
2683 --
2684 If p_alwys_cnt_all_prtts_flag = 'Y' and p_alwys_sum_all_cvg_flag = 'Y' then
2685 -- Only one flag can be set to Y
2686 fnd_message.set_name('BEN','BEN_92262_ONE_FLAG_ALWD_SET');
2687 fnd_message.raise_error;
2688 --
2689 end if;
2690 --
2691 hr_utility.set_location('Leaving:'||l_proc,10);
2692 --
2693 end chk_alwys_flag_one_def;
2694 --
2695 -- ----------------------------------------------------------------------------
2696 -- |------------------------< chk_ttlprtt_mltcd >-----------------------------|
2697 -- ----------------------------------------------------------------------------
2698 --
2699 -- Description
2700 -- This procedure is used to check that if the mlt_cd = TTLPRTT or TTLCVG
2701 -- that the applicable criteria must be set up on ttl_cvg_vol_rt or
2702 -- ttl_prtt_rt.
2703 --
2704 -- Pre Conditions
2705 -- None.
2706 --
2707 -- In Parameters
2708 -- vrbl_rt_prfl_id PK of record being inserted or updated.
2709 -- mlt_cd
2710 -- effective_date Session date of record.
2711 -- business_group_id Business group id of record being inserted.
2712 --
2713 -- Post Success
2714 -- Processing continues
2715 --
2716 -- Post Failure
2717 -- Errors handled by the procedure
2718 --
2719 -- Access Status
2720 -- Internal table handler use only.
2721 --
2722 Procedure chk_ttlprtt_mtl_cd(p_vrbl_rt_prfl_id in number,
2723 p_mlt_cd in varchar2,
2724 p_effective_date in date,
2725 p_business_group_id in number) is
2726 --
2727 l_proc varchar2(72) := g_package||'chk_ttlprtt_mtl_cd';
2728 l_api_updating boolean;
2729 l_dummy varchar2(1);
2730 --
2731 cursor c1 is
2732 select null
2733 from ben_ttl_cvg_vol_rt_f a
2734
2735 where a.business_group_id +0 = p_business_group_id
2736 and a.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
2737 and p_effective_date
2738 between a.effective_start_date
2739 and a.effective_end_date;
2740 --
2741 cursor c2 is
2742 select null
2743 from ben_ttl_prtt_rt_f a
2744 where a.business_group_id +0 = p_business_group_id
2745 and a.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
2746 and p_effective_date
2747 between a.effective_start_date
2748 and a.effective_end_date;
2749 --
2750 Begin
2751 --
2752 hr_utility.set_location('Entering:'||l_proc, 5);
2753 --
2754 if p_mlt_cd <> 'TTLPRTT' then
2755 --
2756 --
2757 open c2;
2758 --
2759 fetch c2 into l_dummy;
2760 if c2%found then
2761 --
2762 --
2763 close c2;
2764 --
2765 -- raise an error as mlt_cd can't be changed when prtt criteria set up.
2766 --
2767 --
2768 fnd_message.set_name('BEN','BEN_92263_TTLPRTT_MLTCD');
2769 fnd_message.raise_error;
2770 --
2771 end if;
2772 --
2773 close c2;
2774 --
2775 end if;
2776 --
2777 if p_mlt_cd <> 'TTLCVG' then
2778 --
2779 --
2780 open c1;
2781 --
2782 fetch c1 into l_dummy;
2783 if c1%found then
2784 --
2785 --
2786 close c1;
2787 --
2788 -- raise an error as mlt_cd can't be changed when cvg criteria set up.
2789 --
2790 --
2791 fnd_message.set_name('BEN','BEN_92264_TTLCVG_MLTCD');
2792 fnd_message.raise_error;
2793 --
2794 end if;
2795 --
2796 close c1;
2797 --
2798 end if;
2799 --
2800 hr_utility.set_location('Leaving:'||l_proc, 10);
2801 --
2802 End chk_ttlprtt_mtl_cd;
2803 --
2804 --
2805 -- ----------------------------------------------------------------------------
2806 -- |---------------------< chk_elig_ttlprtt_mlt_cd >--------------------------|
2807 -- ----------------------------------------------------------------------------
2808 -- Added for checks during usage of 'Eligbility Profiles' for defining
2809 -- criteria for calculation of Variable Coverages and Variable Actual Premiums.
2810 -- Bug : 3456400
2811 --
2812 -- Description
2813 -- This procedure is used to check that if the mlt_cd = TTLPRTT or TTLCVG
2814 -- and any Eligibility Profile is defined
2815 -- then none of the Eligibility Profiles must be set up on ben_elig_ttl_cvg_vol_prte or
2816 -- ben_elig_ttl_prtt_prte_f respectively.
2817 --
2818 -- Pre Conditions
2819 -- None.
2820 --
2821 -- In Parameters
2822 -- vrbl_rt_prfl_id PK of record being inserted or updated.
2823 -- mlt_cd Calculation Method
2824 -- effective_date Session date of record.
2825 -- business_group_id Business group id of record being inserted.
2826 --
2827 -- Post Success
2828 -- Processing continues
2829 --
2830 -- Post Failure
2831 -- Errors handled by the procedure
2832 --
2833 -- Access Status
2834 -- Internal table handler use only.
2835 --
2836 Procedure chk_elig_ttlprtt_mtl_cd(p_vrbl_rt_prfl_id in number,
2837 p_mlt_cd in varchar2,
2838 p_effective_date in date,
2839 p_business_group_id in number) is
2840 --
2841 l_proc varchar2(72) := g_package||'chk_elig_ttlprtt_mtl_cd';
2842 l_api_updating boolean;
2843 l_dummy varchar2(1);
2844 --
2845 cursor c1 is select null
2846 from ben_elig_ttl_cvg_vol_prte_f etc, ben_vrbl_rt_elig_prfl_f vep
2847 where vep.eligy_prfl_id = etc.eligy_prfl_id
2848 and vep.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
2849 and vep.business_group_id = p_business_Group_id
2850 and p_effective_date between vep.effective_start_date and vep.effective_end_date
2851 and etc.business_group_id = p_business_Group_id
2852 and p_effective_date between etc.effective_start_date and etc.effective_end_date;
2853 --
2854 cursor c2 is select null
2855 from ben_elig_ttl_prtt_prte_f etp, ben_vrbl_rt_elig_prfl_f vep
2856 where vep.eligy_prfl_id = etp.eligy_prfl_id
2857 and vep.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
2858 and vep.business_group_id = p_business_Group_id
2859 and p_effective_date between vep.effective_start_date and vep.effective_end_date
2860 and etp.business_group_id = p_business_Group_id
2861 and p_effective_date between etp.effective_start_date and etp.effective_end_date;
2862 --
2863 --
2864
2865 Begin
2866 --
2867 hr_utility.set_location('Entering:'||l_proc, 5);
2868 --
2869 if p_mlt_cd <> 'TTLPRTT' then
2870 --
2871 open c2;
2872 --
2873 fetch c2 into l_dummy;
2874 if c2%found then
2875 --
2876 close c2;
2877 -- raise an error as mlt_cd can't be changed when prtt criteria set up.
2878 fnd_message.set_name('BEN','BEN_92263_TTLPRTT_MLTCD');
2879 fnd_message.raise_error;
2880 --
2881 end if;
2882 --
2883 close c2;
2884 --
2885 end if;
2886 --
2887 if p_mlt_cd <> 'TTLCVG' then
2888 --
2889 open c1;
2890 --
2891 fetch c1 into l_dummy;
2892 if c1%found then
2893 --
2894 close c1;
2895 -- raise an error as mlt_cd can't be changed when cvg criteria set up.
2896 fnd_message.set_name('BEN','BEN_92264_TTLCVG_MLTCD');
2897 fnd_message.raise_error;
2898 --
2899 end if;
2900 --
2901 close c1;
2902 --
2903 end if;
2904 --
2905 hr_utility.set_location('Leaving:'||l_proc, 10);
2906 --
2907 End chk_elig_ttlprtt_mtl_cd;
2908 --
2909 --
2910 -- ----------------------------------------------------------------------------
2911 -- |------------------------------< chk_profile_flags >----------------------|
2912 -- ----------------------------------------------------------------------------
2913 --
2914 -- Description
2915 -- This procedure is used to check that the lookup value is valid.
2916 --
2917 -- Pre Conditions
2918 -- None.
2919 --
2920 -- In Parameters
2921 -- vrbl_rt_prfl_id PK of record being inserted or updated.
2922 -- rt_hrly_slrd_flag Value of lookup code
2923 -- rt_pstl_cd_flag Value of lookup code
2924 -- rt_lbr_mmbr_flag Value of lookup code
2925 -- rt_lgl_enty_flag Value of lookup code
2926 -- rt_benfts_grp_flag Value of lookup code
2927 -- rt_wk_loc_flag Value of lookup code
2928 -- rt_brgng_unit_flag Value of lookup code
2929 -- rt_age_flag Value of lookup code
2930 -- rt_los_flag Value of lookup code
2931 -- rt_per_typ_flag Value of lookup code
2932 -- rt_fl_tm_pt_tm_flag Value of lookup code
2933 -- rt_ee_stat_flag Value of lookup code
2934 -- rt_grd_flag Value of lookup code
2935 -- rt_pct_fl_tm_flag Value of lookup code
2936 -- rt_asnt_set_flag Value of lookup code
2937 -- rt_hrs_wkd_flag Value of lookup code
2938 -- rt_comp_lvl_flag Value of lookup code
2939 -- rt_org_unit_flag Value of lookup code
2940 -- rt_loa_rsn_flag Value of lookup code
2941 -- rt_pyrl_flag Value of lookup code
2942 -- rt_schedd_hrs_flag Value of lookup code
2943 -- rt_py_bss_flag Value of lookup code
2944 -- rt_prfl_rl_flag Value of lookup code
2945 -- rt_cmbn_age_los_flag Value of lookup code
2946 -- rt_prtt_pl_flag Value of lookup code
2947 -- rt_svc_area_flag Value of lookup code
2948 -- rt_ppl_grp_flag Value of lookup code
2949 -- rt_dsbld_flag Value of lookup code
2950 -- rt_hlth_cvg_flag Value of lookup code
2951 -- rt_poe_flag Value of lookup code
2952 -- rt_ttl_cvg_vol_flag Value of lookup code
2953 -- rt_ttl_prtt_flag Value of lookup code
2954 -- rt_gndr_flag Value of lookup code
2955 -- rt_tbco_use_flag Value of lookup code
2956 -- effective_date effective date
2957 --
2958 -- Post Success
2959 -- Processing continues
2960 --
2961 -- Post Failure
2962 -- Error handled by procedure
2963 --
2964 -- Access Status
2965 -- Internal table handler use only.
2966 --
2967 Procedure chk_profile_flags
2968 (p_vrbl_rt_prfl_id in number,
2969 p_rt_hrly_slrd_flag in varchar2,
2970 p_rt_pstl_cd_flag in varchar2,
2971 p_rt_lbr_mmbr_flag in varchar2,
2972 p_rt_lgl_enty_flag in varchar2,
2973 p_rt_benfts_grp_flag in varchar2,
2974 p_rt_wk_loc_flag in varchar2,
2975 p_rt_brgng_unit_flag in varchar2,
2976 p_rt_age_flag in varchar2,
2977 p_rt_los_flag in varchar2,
2978 p_rt_per_typ_flag in varchar2,
2979 p_rt_fl_tm_pt_tm_flag in varchar2,
2980 p_rt_ee_stat_flag in varchar2,
2981 p_rt_grd_flag in varchar2,
2982 p_rt_pct_fl_tm_flag in varchar2,
2983 p_rt_asnt_set_flag in varchar2,
2984 p_rt_hrs_wkd_flag in varchar2,
2985 p_rt_comp_lvl_flag in varchar2,
2986 p_rt_org_unit_flag in varchar2,
2987 p_rt_loa_rsn_flag in varchar2,
2988 p_rt_pyrl_flag in varchar2,
2989 p_rt_schedd_hrs_flag in varchar2,
2990 p_rt_py_bss_flag in varchar2,
2991 p_rt_prfl_rl_flag in varchar2,
2992 p_rt_cmbn_age_los_flag in varchar2,
2993 p_rt_prtt_pl_flag in varchar2,
2994 p_rt_svc_area_flag in varchar2,
2995 p_rt_ppl_grp_flag in varchar2,
2996 p_rt_dsbld_flag in varchar2,
2997 p_rt_hlth_cvg_flag in varchar2,
2998 p_rt_poe_flag in varchar2,
2999 p_rt_ttl_cvg_vol_flag in varchar2,
3000 p_rt_ttl_prtt_flag in varchar2,
3001 p_rt_gndr_flag in varchar2,
3002 p_rt_tbco_use_flag in varchar2,
3003 p_effective_date in date ,
3004 p_rt_cntng_prtn_prfl_flag in varchar2,
3005 p_rt_cbr_quald_bnf_flag in varchar2,
3006 p_rt_optd_mdcr_flag in varchar2,
3007 p_rt_lvg_rsn_flag in varchar2,
3008 p_rt_pstn_flag in varchar2,
3009 p_rt_comptncy_flag in varchar2,
3010 p_rt_job_flag in varchar2,
3011 p_rt_qual_titl_flag in varchar2,
3012 p_rt_dpnt_cvrd_pl_flag in varchar2,
3013 p_rt_dpnt_cvrd_plip_flag in varchar2,
3014 p_rt_dpnt_cvrd_ptip_flag in varchar2,
3015 p_rt_dpnt_cvrd_pgm_flag in varchar2,
3016 p_rt_enrld_oipl_flag in varchar2,
3017 p_rt_enrld_pl_flag in varchar2,
3018 p_rt_enrld_plip_flag in varchar2,
3019 p_rt_enrld_ptip_flag in varchar2,
3020 p_rt_enrld_pgm_flag in varchar2,
3021 p_rt_prtt_anthr_pl_flag in varchar2,
3022 p_rt_othr_ptip_flag in varchar2,
3023 p_rt_no_othr_cvg_flag in varchar2,
3024 p_rt_dpnt_othr_ptip_flag in varchar2,
3025 p_rt_qua_in_gr_flag in varchar2,
3026 p_rt_perf_rtng_flag in varchar2,
3027 p_rt_elig_prfl_flag in varchar2
3028 ) is
3029 --
3030 l_proc varchar2(72) := g_package||'chk_profile_flags';
3031 l_api_updating boolean;
3032 --
3033 Begin
3034 --
3035 hr_utility.set_location('Entering:'||l_proc, 5);
3036 --
3037 if hr_api.not_exists_in_hr_lookups
3038 (p_lookup_type => 'YES_NO',
3039 p_lookup_code => p_rt_hrly_slrd_flag,
3040 p_effective_date => p_effective_date) then
3041 --
3042 -- raise error as does not exist as lookup
3043 --
3044 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3045 fnd_message.set_token('FIELD',p_rt_hrly_slrd_flag);
3046 fnd_message.set_token('TYPE','YES_NO');
3047 fnd_message.raise_error;
3048 --
3049 end if;
3050 --
3051 if hr_api.not_exists_in_hr_lookups
3052 (p_lookup_type => 'YES_NO',
3053 p_lookup_code => p_rt_pstl_cd_flag,
3054 p_effective_date => p_effective_date) then
3055 --
3056 -- raise error as does not exist as lookup
3057 --
3058 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3059 fnd_message.set_token('FIELD',p_rt_pstl_cd_flag);
3060 fnd_message.set_token('TYPE','YES_NO');
3061 fnd_message.raise_error;
3062 --
3063 end if;
3064 --
3065 if hr_api.not_exists_in_hr_lookups
3066 (p_lookup_type => 'YES_NO',
3067 p_lookup_code => p_rt_lbr_mmbr_flag,
3068 p_effective_date => p_effective_date) then
3069 --
3070 -- raise error as does not exist as lookup
3071 --
3072 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3073 fnd_message.set_token('FIELD',p_rt_lbr_mmbr_flag);
3074 fnd_message.set_token('TYPE','YES_NO');
3075 fnd_message.raise_error;
3076 --
3077 end if;
3078 --
3079 if hr_api.not_exists_in_hr_lookups
3080 (p_lookup_type => 'YES_NO',
3081 p_lookup_code => p_rt_lgl_enty_flag,
3082 p_effective_date => p_effective_date) then
3083 --
3084 -- raise error as does not exist as lookup
3085 --
3086 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3087 fnd_message.set_token('FIELD',p_rt_lgl_enty_flag);
3088 fnd_message.set_token('TYPE','YES_NO');
3089 fnd_message.raise_error;
3090 --
3091 end if;
3092 --
3093 if hr_api.not_exists_in_hr_lookups
3094 (p_lookup_type => 'YES_NO',
3095 p_lookup_code => p_rt_benfts_grp_flag,
3096 p_effective_date => p_effective_date) then
3097 --
3098 -- raise error as does not exist as lookup
3099 --
3100 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3101 fnd_message.set_token('FIELD',p_rt_benfts_grp_flag);
3102 fnd_message.set_token('TYPE','YES_NO');
3103 fnd_message.raise_error;
3104 --
3105 end if;
3106 --
3107 if hr_api.not_exists_in_hr_lookups
3108 (p_lookup_type => 'YES_NO',
3109 p_lookup_code => p_rt_wk_loc_flag,
3110 p_effective_date => p_effective_date) then
3111 --
3112 -- raise error as does not exist as lookup
3113 --
3114 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3115 fnd_message.set_token('FIELD',p_rt_wk_loc_flag);
3116 fnd_message.set_token('TYPE','YES_NO');
3117 fnd_message.raise_error;
3118 --
3119 end if;
3120 --
3121 if hr_api.not_exists_in_hr_lookups
3122 (p_lookup_type => 'YES_NO',
3123 p_lookup_code => p_rt_brgng_unit_flag,
3124 p_effective_date => p_effective_date) then
3125 --
3126 -- raise error as does not exist as lookup
3127 --
3128 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3129 fnd_message.set_token('FIELD',p_rt_brgng_unit_flag);
3130 fnd_message.set_token('TYPE','YES_NO');
3131 fnd_message.raise_error;
3132 --
3133 end if;
3134 --
3135 if hr_api.not_exists_in_hr_lookups
3136 (p_lookup_type => 'YES_NO',
3137 p_lookup_code => p_rt_age_flag,
3138 p_effective_date => p_effective_date) then
3139 --
3140 -- raise error as does not exist as lookup
3141 --
3142 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3143 fnd_message.set_token('FIELD',p_rt_age_flag);
3144 fnd_message.set_token('TYPE','YES_NO');
3145 fnd_message.raise_error;
3146 --
3147 end if;
3148 --
3149 if hr_api.not_exists_in_hr_lookups
3150 (p_lookup_type => 'YES_NO',
3151 p_lookup_code => p_rt_los_flag,
3152 p_effective_date => p_effective_date) then
3153 --
3154 -- raise error as does not exist as lookup
3155 --
3156 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3157 fnd_message.set_token('FIELD',p_rt_los_flag);
3158 fnd_message.set_token('TYPE','YES_NO');
3159 fnd_message.raise_error;
3160 --
3161 end if;
3162 --
3163 if hr_api.not_exists_in_hr_lookups
3164 (p_lookup_type => 'YES_NO',
3165 p_lookup_code => p_rt_per_typ_flag,
3166 p_effective_date => p_effective_date) then
3167 --
3168 -- raise error as does not exist as lookup
3169 --
3170 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3171 fnd_message.set_token('FIELD',p_rt_per_typ_flag);
3172 fnd_message.set_token('TYPE','YES_NO');
3173 fnd_message.raise_error;
3174 --
3175 end if;
3176 --
3177 if hr_api.not_exists_in_hr_lookups
3178 (p_lookup_type => 'YES_NO',
3179 p_lookup_code => p_rt_fl_tm_pt_tm_flag,
3180 p_effective_date => p_effective_date) then
3181 --
3182 -- raise error as does not exist as lookup
3183 --
3184 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3185 fnd_message.set_token('FIELD',p_rt_fl_tm_pt_tm_flag);
3186 fnd_message.set_token('TYPE','YES_NO');
3187 fnd_message.raise_error;
3188 --
3189 end if;
3190 --
3191 if hr_api.not_exists_in_hr_lookups
3192 (p_lookup_type => 'YES_NO',
3193 p_lookup_code => p_rt_ee_stat_flag,
3194 p_effective_date => p_effective_date) then
3195 --
3196 -- raise error as does not exist as lookup
3197 --
3198 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3199 fnd_message.set_token('FIELD',p_rt_ee_stat_flag);
3200 fnd_message.set_token('TYPE','YES_NO');
3201 fnd_message.raise_error;
3202 --
3203 end if;
3204 --
3205 if hr_api.not_exists_in_hr_lookups
3206 (p_lookup_type => 'YES_NO',
3207 p_lookup_code => p_rt_grd_flag,
3208 p_effective_date => p_effective_date) then
3209 --
3210 -- raise error as does not exist as lookup
3211 --
3212 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3213 fnd_message.set_token('FIELD',p_rt_grd_flag);
3214 fnd_message.set_token('TYPE','YES_NO');
3215 fnd_message.raise_error;
3216 --
3217 end if;
3218 --
3219 if hr_api.not_exists_in_hr_lookups
3220 (p_lookup_type => 'YES_NO',
3221 p_lookup_code => p_rt_pct_fl_tm_flag,
3222 p_effective_date => p_effective_date) then
3223 --
3224 -- raise error as does not exist as lookup
3225 --
3226 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3227 fnd_message.set_token('FIELD',p_rt_pct_fl_tm_flag);
3228 fnd_message.set_token('TYPE','YES_NO');
3229 fnd_message.raise_error;
3230 --
3231 end if;
3232 --
3233 if hr_api.not_exists_in_hr_lookups
3234 (p_lookup_type => 'YES_NO',
3235 p_lookup_code => p_rt_asnt_set_flag,
3236 p_effective_date => p_effective_date) then
3237 --
3238 -- raise error as does not exist as lookup
3239 --
3240 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3241 fnd_message.set_token('FIELD',p_rt_asnt_set_flag);
3242 fnd_message.set_token('TYPE','YES_NO');
3243 fnd_message.raise_error;
3244 --
3245 end if;
3246 --
3247 if hr_api.not_exists_in_hr_lookups
3248 (p_lookup_type => 'YES_NO',
3249 p_lookup_code => p_rt_hrs_wkd_flag,
3250 p_effective_date => p_effective_date) then
3251 --
3252 -- raise error as does not exist as lookup
3253 --
3254 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3255 fnd_message.set_token('FIELD',p_rt_hrs_wkd_flag);
3256 fnd_message.set_token('TYPE','YES_NO');
3257 fnd_message.raise_error;
3258 --
3259 end if;
3260 --
3261 if hr_api.not_exists_in_hr_lookups
3262 (p_lookup_type => 'YES_NO',
3263 p_lookup_code => p_rt_comp_lvl_flag,
3264 p_effective_date => p_effective_date) then
3265 --
3266 -- raise error as does not exist as lookup
3267 --
3268 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3269 fnd_message.set_token('FIELD',p_rt_comp_lvl_flag);
3270 fnd_message.set_token('TYPE','YES_NO');
3271 fnd_message.raise_error;
3272 --
3273 end if;
3274 --
3275 if hr_api.not_exists_in_hr_lookups
3276 (p_lookup_type => 'YES_NO',
3277 p_lookup_code => p_rt_org_unit_flag,
3278 p_effective_date => p_effective_date) then
3279 --
3280 -- raise error as does not exist as lookup
3281 --
3282 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3283 fnd_message.set_token('FIELD',p_rt_org_unit_flag);
3284 fnd_message.set_token('TYPE','YES_NO');
3285 fnd_message.raise_error;
3286 --
3287 end if;
3288 --
3289 if hr_api.not_exists_in_hr_lookups
3290 (p_lookup_type => 'YES_NO',
3291 p_lookup_code => p_rt_loa_rsn_flag,
3292 p_effective_date => p_effective_date) then
3293 --
3294 -- raise error as does not exist as lookup
3295 --
3296 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3297 fnd_message.set_token('FIELD',p_rt_loa_rsn_flag);
3298 fnd_message.set_token('TYPE','YES_NO');
3299 fnd_message.raise_error;
3300 --
3301 end if;
3302 --
3303 if hr_api.not_exists_in_hr_lookups
3304 (p_lookup_type => 'YES_NO',
3305 p_lookup_code => p_rt_pyrl_flag,
3306 p_effective_date => p_effective_date) then
3307 --
3308 -- raise error as does not exist as lookup
3309 --
3310 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3311 fnd_message.set_token('FIELD',p_rt_pyrl_flag);
3312 fnd_message.set_token('TYPE','YES_NO');
3313 fnd_message.raise_error;
3314 --
3315 end if;
3316 --
3317 if hr_api.not_exists_in_hr_lookups
3318 (p_lookup_type => 'YES_NO',
3319 p_lookup_code => p_rt_schedd_hrs_flag,
3320 p_effective_date => p_effective_date) then
3321 --
3322 -- raise error as does not exist as lookup
3323 --
3324 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3325 fnd_message.set_token('FIELD',p_rt_schedd_hrs_flag);
3326 fnd_message.set_token('TYPE','YES_NO');
3327 fnd_message.raise_error;
3328 --
3329 end if;
3330 --
3331 if hr_api.not_exists_in_hr_lookups
3332 (p_lookup_type => 'YES_NO',
3333 p_lookup_code => p_rt_py_bss_flag,
3334 p_effective_date => p_effective_date) then
3335 --
3336 -- raise error as does not exist as lookup
3337 --
3338 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3339 fnd_message.set_token('FIELD',p_rt_py_bss_flag);
3340 fnd_message.set_token('TYPE','YES_NO');
3341 fnd_message.raise_error;
3342 --
3343 end if;
3344 --
3345 if hr_api.not_exists_in_hr_lookups
3346 (p_lookup_type => 'YES_NO',
3347 p_lookup_code => p_rt_prfl_rl_flag,
3348 p_effective_date => p_effective_date) then
3349 --
3350 -- raise error as does not exist as lookup
3351 --
3352 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3353 fnd_message.set_token('FIELD',p_rt_prfl_rl_flag);
3354 fnd_message.set_token('TYPE','YES_NO');
3355 fnd_message.raise_error;
3356 --
3357 end if;
3358 --
3359 if hr_api.not_exists_in_hr_lookups
3360 (p_lookup_type => 'YES_NO',
3361 p_lookup_code => p_rt_cmbn_age_los_flag,
3362 p_effective_date => p_effective_date) then
3363 --
3364 -- raise error as does not exist as lookup
3365 --
3366 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3367 fnd_message.set_token('FIELD',p_rt_cmbn_age_los_flag);
3368 fnd_message.set_token('TYPE','YES_NO');
3369 fnd_message.raise_error;
3370 --
3371 end if;
3372 --
3373 if hr_api.not_exists_in_hr_lookups
3374 (p_lookup_type => 'YES_NO',
3375 p_lookup_code => p_rt_prtt_pl_flag,
3376 p_effective_date => p_effective_date) then
3377 --
3378 -- raise error as does not exist as lookup
3379 --
3380 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3381 fnd_message.set_token('FIELD',p_rt_prtt_pl_flag);
3382 fnd_message.set_token('TYPE','YES_NO');
3383 fnd_message.raise_error;
3384 --
3385 end if;
3386 --
3387 if hr_api.not_exists_in_hr_lookups
3388 (p_lookup_type => 'YES_NO',
3389 p_lookup_code => p_rt_svc_area_flag,
3390 p_effective_date => p_effective_date) then
3391 --
3392 -- raise error as does not exist as lookup
3393 --
3394 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3395 fnd_message.set_token('FIELD',p_rt_svc_area_flag);
3396 fnd_message.set_token('TYPE','YES_NO');
3397 fnd_message.raise_error;
3398 --
3399 end if;
3400 --
3401 if hr_api.not_exists_in_hr_lookups
3402 (p_lookup_type => 'YES_NO',
3403 p_lookup_code => p_rt_ppl_grp_flag,
3404 p_effective_date => p_effective_date) then
3405 --
3406 -- raise error as does not exist as lookup
3407 --
3408 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3409 fnd_message.set_token('FIELD',p_rt_ppl_grp_flag);
3410 fnd_message.set_token('TYPE','YES_NO');
3411 fnd_message.raise_error;
3412 --
3413 end if;
3414 --
3415 if hr_api.not_exists_in_hr_lookups
3416 (p_lookup_type => 'YES_NO',
3417 p_lookup_code => p_rt_dsbld_flag,
3418 p_effective_date => p_effective_date) then
3419 --
3420 -- raise error as does not exist as lookup
3421 --
3422 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3423 fnd_message.set_token('FIELD',p_rt_dsbld_flag);
3424 fnd_message.set_token('TYPE','YES_NO');
3425 fnd_message.raise_error;
3426 --
3427 end if;
3428 --
3429 if hr_api.not_exists_in_hr_lookups
3430 (p_lookup_type => 'YES_NO',
3431 p_lookup_code => p_rt_hlth_cvg_flag,
3432 p_effective_date => p_effective_date) then
3433 --
3434 -- raise error as does not exist as lookup
3435 --
3436 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3437 fnd_message.set_token('FIELD',p_rt_hlth_cvg_flag);
3438 fnd_message.set_token('TYPE','YES_NO');
3439 fnd_message.raise_error;
3440 --
3441 end if;
3442 --
3443 if hr_api.not_exists_in_hr_lookups
3444 (p_lookup_type => 'YES_NO',
3445 p_lookup_code => p_rt_poe_flag,
3446 p_effective_date => p_effective_date) then
3447 --
3448 -- raise error as does not exist as lookup
3449 --
3450 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3451 fnd_message.set_token('FIELD',p_rt_poe_flag);
3452 fnd_message.set_token('TYPE','YES_NO');
3453 fnd_message.raise_error;
3454 --
3455 end if;
3456 --
3457 if hr_api.not_exists_in_hr_lookups
3458 (p_lookup_type => 'YES_NO',
3459 p_lookup_code => p_rt_ttl_cvg_vol_flag,
3460 p_effective_date => p_effective_date) then
3461 --
3462 -- raise error as does not exist as lookup
3463 --
3464 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3465 fnd_message.set_token('FIELD',p_rt_ttl_cvg_vol_flag);
3466 fnd_message.set_token('TYPE','YES_NO');
3467 fnd_message.raise_error;
3468 --
3469 end if;
3470 --
3471 if hr_api.not_exists_in_hr_lookups
3472 (p_lookup_type => 'YES_NO',
3473 p_lookup_code => p_rt_ttl_prtt_flag,
3474 p_effective_date => p_effective_date) then
3475 --
3476 -- raise error as does not exist as lookup
3477 --
3478 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3479 fnd_message.set_token('FIELD',p_rt_ttl_prtt_flag);
3480 fnd_message.set_token('TYPE','YES_NO');
3481 fnd_message.raise_error;
3482 --
3483 end if;
3484 --
3485 if hr_api.not_exists_in_hr_lookups
3486 (p_lookup_type => 'YES_NO',
3487 p_lookup_code => p_rt_gndr_flag,
3488 p_effective_date => p_effective_date) then
3489 --
3490 -- raise error as does not exist as lookup
3491 --
3492 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3493 fnd_message.set_token('FIELD',p_rt_gndr_flag);
3494 fnd_message.set_token('TYPE','YES_NO');
3495 fnd_message.raise_error;
3496 --
3497 end if;
3498 --
3499 if hr_api.not_exists_in_hr_lookups
3500 (p_lookup_type => 'YES_NO',
3501 p_lookup_code => p_rt_tbco_use_flag,
3502 p_effective_date => p_effective_date) then
3503 --
3504 -- raise error as does not exist as lookup
3505 --
3506 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3507 fnd_message.set_token('FIELD',p_rt_tbco_use_flag);
3508 fnd_message.set_token('TYPE','YES_NO');
3509 fnd_message.raise_error;
3510 --
3511 end if;
3512
3513
3514 if hr_api.not_exists_in_hr_lookups
3515 (p_lookup_type => 'YES_NO',
3516 p_lookup_code => p_rt_cntng_prtn_prfl_flag,
3517 p_effective_date => p_effective_date) then
3518 --
3519 -- raise error as does not exist as lookup
3520 --
3521 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3522 fnd_message.set_token('FIELD',p_rt_cntng_prtn_prfl_flag);
3523 fnd_message.set_token('TYPE','YES_NO');
3524 fnd_message.raise_error;
3525 --
3526 end if;
3527
3528 if hr_api.not_exists_in_hr_lookups
3529 (p_lookup_type => 'YES_NO',
3530 p_lookup_code => p_rt_cbr_quald_bnf_flag,
3531 p_effective_date => p_effective_date) then
3532 --
3533 -- raise error as does not exist as lookup
3534 --
3535 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3536 fnd_message.set_token('FIELD',p_rt_cbr_quald_bnf_flag);
3537 fnd_message.set_token('TYPE','YES_NO');
3538 fnd_message.raise_error;
3539 --
3540 end if;
3541
3542 if hr_api.not_exists_in_hr_lookups
3543 (p_lookup_type => 'YES_NO',
3544 p_lookup_code => p_rt_optd_mdcr_flag,
3545 p_effective_date => p_effective_date) then
3546 --
3547 -- raise error as does not exist as lookup
3548 --
3549 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3550 fnd_message.set_token('FIELD',p_rt_optd_mdcr_flag);
3551 fnd_message.set_token('TYPE','YES_NO');
3552 fnd_message.raise_error;
3553 --
3554 end if;
3555
3556 if hr_api.not_exists_in_hr_lookups
3557 (p_lookup_type => 'YES_NO',
3558 p_lookup_code => p_rt_lvg_rsn_flag,
3559 p_effective_date => p_effective_date) then
3560 --
3561 -- raise error as does not exist as lookup
3562 --
3563 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3564 fnd_message.set_token('FIELD',p_rt_lvg_rsn_flag);
3565 fnd_message.set_token('TYPE','YES_NO');
3566 fnd_message.raise_error;
3567 --
3568 end if;
3569
3570 if hr_api.not_exists_in_hr_lookups
3571 (p_lookup_type => 'YES_NO',
3572 p_lookup_code => p_rt_pstn_flag,
3573 p_effective_date => p_effective_date) then
3574 --
3575 -- raise error as does not exist as lookup
3576 --
3577 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3578 fnd_message.set_token('FIELD',p_rt_pstn_flag);
3579 fnd_message.set_token('TYPE','YES_NO');
3580 fnd_message.raise_error;
3581 --
3582 end if;
3583
3584 if hr_api.not_exists_in_hr_lookups
3585 (p_lookup_type => 'YES_NO',
3586 p_lookup_code => p_rt_comptncy_flag,
3587 p_effective_date => p_effective_date) then
3588 --
3589 -- raise error as does not exist as lookup
3590 --
3591 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3592 fnd_message.set_token('FIELD',p_rt_comptncy_flag);
3593 fnd_message.set_token('TYPE','YES_NO');
3594 fnd_message.raise_error;
3595 --
3596 end if;
3597
3598 if hr_api.not_exists_in_hr_lookups
3599 (p_lookup_type => 'YES_NO',
3600 p_lookup_code => p_rt_job_flag,
3601 p_effective_date => p_effective_date) then
3602 --
3603 -- raise error as does not exist as lookup
3604 --
3605 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3606 fnd_message.set_token('FIELD',p_rt_job_flag);
3607 fnd_message.set_token('TYPE','YES_NO');
3608 fnd_message.raise_error;
3609 --
3610 end if;
3611
3612 if hr_api.not_exists_in_hr_lookups
3613 (p_lookup_type => 'YES_NO',
3614 p_lookup_code => p_rt_qual_titl_flag,
3615 p_effective_date => p_effective_date) then
3616 --
3617 -- raise error as does not exist as lookup
3618 --
3619 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3620 fnd_message.set_token('FIELD',p_rt_qual_titl_flag);
3621 fnd_message.set_token('TYPE','YES_NO');
3622 fnd_message.raise_error;
3623 --
3624 end if;
3625
3626 if hr_api.not_exists_in_hr_lookups
3627 (p_lookup_type => 'YES_NO',
3628 p_lookup_code => p_rt_dpnt_cvrd_pl_flag,
3629 p_effective_date => p_effective_date) then
3630 --
3631 -- raise error as does not exist as lookup
3632 --
3633 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3634 fnd_message.set_token('FIELD',p_rt_dpnt_cvrd_pl_flag);
3635 fnd_message.set_token('TYPE','YES_NO');
3636 fnd_message.raise_error;
3637 --
3638 end if;
3639
3640 if hr_api.not_exists_in_hr_lookups
3641 (p_lookup_type => 'YES_NO',
3642 p_lookup_code => p_rt_dpnt_cvrd_plip_flag,
3643 p_effective_date => p_effective_date) then
3644 --
3645 -- raise error as does not exist as lookup
3646 --
3647 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3648 fnd_message.set_token('FIELD',p_rt_dpnt_cvrd_plip_flag);
3649 fnd_message.set_token('TYPE','YES_NO');
3650 fnd_message.raise_error;
3651 --
3652 end if;
3653
3654 if hr_api.not_exists_in_hr_lookups
3655 (p_lookup_type => 'YES_NO',
3656 p_lookup_code => p_rt_dpnt_cvrd_ptip_flag,
3657 p_effective_date => p_effective_date) then
3658 --
3659 -- raise error as does not exist as lookup
3660 --
3661 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3662 fnd_message.set_token('FIELD',p_rt_dpnt_cvrd_ptip_flag);
3663 fnd_message.set_token('TYPE','YES_NO');
3664 fnd_message.raise_error;
3665 --
3666 end if;
3667
3668 if hr_api.not_exists_in_hr_lookups
3669 (p_lookup_type => 'YES_NO',
3670 p_lookup_code => p_rt_dpnt_cvrd_pgm_flag,
3671 p_effective_date => p_effective_date) then
3672 --
3673 -- raise error as does not exist as lookup
3674 --
3675 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3676 fnd_message.set_token('FIELD',p_rt_dpnt_cvrd_pgm_flag);
3677 fnd_message.set_token('TYPE','YES_NO');
3678 fnd_message.raise_error;
3679 --
3680 end if;
3681
3682 if hr_api.not_exists_in_hr_lookups
3683 (p_lookup_type => 'YES_NO',
3684 p_lookup_code => p_rt_enrld_oipl_flag,
3685 p_effective_date => p_effective_date) then
3686 --
3687 -- raise error as does not exist as lookup
3688 --
3689 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3690 fnd_message.set_token('FIELD',p_rt_enrld_oipl_flag);
3691 fnd_message.set_token('TYPE','YES_NO');
3692 fnd_message.raise_error;
3693 --
3694 end if;
3695
3696 if hr_api.not_exists_in_hr_lookups
3697 (p_lookup_type => 'YES_NO',
3698 p_lookup_code => p_rt_enrld_pl_flag,
3699 p_effective_date => p_effective_date) then
3700 --
3701 -- raise error as does not exist as lookup
3702 --
3703 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3704 fnd_message.set_token('FIELD',p_rt_enrld_pl_flag);
3705 fnd_message.set_token('TYPE','YES_NO');
3706 fnd_message.raise_error;
3707 --
3708 end if;
3709
3710 if hr_api.not_exists_in_hr_lookups
3711 (p_lookup_type => 'YES_NO',
3712 p_lookup_code => p_rt_enrld_plip_flag,
3713 p_effective_date => p_effective_date) then
3714 --
3715 -- raise error as does not exist as lookup
3716 --
3717 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3718 fnd_message.set_token('FIELD',p_rt_enrld_plip_flag);
3719 fnd_message.set_token('TYPE','YES_NO');
3720 fnd_message.raise_error;
3721 --
3722 end if;
3723
3724 if hr_api.not_exists_in_hr_lookups
3725 (p_lookup_type => 'YES_NO',
3726 p_lookup_code => p_rt_enrld_ptip_flag,
3727 p_effective_date => p_effective_date) then
3728 --
3729 -- raise error as does not exist as lookup
3730 --
3731 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3732 fnd_message.set_token('FIELD',p_rt_enrld_ptip_flag);
3733 fnd_message.set_token('TYPE','YES_NO');
3734 fnd_message.raise_error;
3735 --
3736 end if;
3737
3738 if hr_api.not_exists_in_hr_lookups
3739 (p_lookup_type => 'YES_NO',
3740 p_lookup_code => p_rt_enrld_pgm_flag,
3741 p_effective_date => p_effective_date) then
3742 --
3743 -- raise error as does not exist as lookup
3744 --
3745 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3746 fnd_message.set_token('FIELD',p_rt_enrld_pgm_flag);
3747 fnd_message.set_token('TYPE','YES_NO');
3748 fnd_message.raise_error;
3749 --
3750 end if;
3751
3752 if hr_api.not_exists_in_hr_lookups
3753 (p_lookup_type => 'YES_NO',
3754 p_lookup_code => p_rt_prtt_anthr_pl_flag,
3755 p_effective_date => p_effective_date) then
3756 --
3757 -- raise error as does not exist as lookup
3758 --
3759 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3760 fnd_message.set_token('FIELD',p_rt_prtt_anthr_pl_flag);
3761 fnd_message.set_token('TYPE','YES_NO');
3762 fnd_message.raise_error;
3763 --
3764 end if;
3765
3766 if hr_api.not_exists_in_hr_lookups
3767 (p_lookup_type => 'YES_NO',
3768 p_lookup_code => p_rt_othr_ptip_flag,
3769 p_effective_date => p_effective_date) then
3770 --
3771 -- raise error as does not exist as lookup
3772 --
3773 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3774 fnd_message.set_token('FIELD',p_rt_othr_ptip_flag);
3775 fnd_message.set_token('TYPE','YES_NO');
3776 fnd_message.raise_error;
3777 --
3778 end if;
3779
3780 if hr_api.not_exists_in_hr_lookups
3781 (p_lookup_type => 'YES_NO',
3782 p_lookup_code => p_rt_no_othr_cvg_flag,
3783 p_effective_date => p_effective_date) then
3784 --
3785 -- raise error as does not exist as lookup
3786 --
3787 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3788 fnd_message.set_token('FIELD',p_rt_no_othr_cvg_flag);
3789 fnd_message.set_token('TYPE','YES_NO');
3790 fnd_message.raise_error;
3791 --
3792 end if;
3793
3794 if hr_api.not_exists_in_hr_lookups
3795 (p_lookup_type => 'YES_NO',
3796 p_lookup_code => p_rt_dpnt_othr_ptip_flag,
3797 p_effective_date => p_effective_date) then
3798 --
3799 -- raise error as does not exist as lookup
3800 --
3801 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3802 fnd_message.set_token('FIELD',p_rt_dpnt_othr_ptip_flag);
3803 fnd_message.set_token('TYPE','YES_NO');
3804 fnd_message.raise_error;
3805 --
3806 end if;
3807
3808 if hr_api.not_exists_in_hr_lookups
3809 (p_lookup_type => 'YES_NO',
3810 p_lookup_code => p_rt_qua_in_gr_flag,
3811 p_effective_date => p_effective_date) then
3812 --
3813 -- raise error as does not exist as lookup
3814 --
3815 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3816 fnd_message.set_token('FIELD',p_rt_qua_in_gr_flag);
3817 fnd_message.set_token('TYPE','YES_NO');
3818 fnd_message.raise_error;
3819 --
3820 end if;
3821
3822 if hr_api.not_exists_in_hr_lookups
3823 (p_lookup_type => 'YES_NO',
3824 p_lookup_code => p_rt_perf_rtng_flag,
3825 p_effective_date => p_effective_date) then
3826 --
3827 -- raise error as does not exist as lookup
3828 --
3829 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3830 fnd_message.set_token('FIELD',p_rt_perf_rtng_flag);
3831 fnd_message.set_token('TYPE','YES_NO');
3832 fnd_message.raise_error;
3833 --
3834 end if;
3835
3836 if hr_api.not_exists_in_hr_lookups
3837 (p_lookup_type => 'YES_NO',
3838 p_lookup_code => p_rt_elig_prfl_flag,
3839 p_effective_date => p_effective_date) then
3840 --
3841 -- raise error as does not exist as lookup
3842 --
3843 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3844 fnd_message.set_token('FIELD',p_rt_elig_prfl_flag);
3845 fnd_message.set_token('TYPE','YES_NO');
3846 fnd_message.raise_error;
3847 --
3848 end if;
3849 --
3850 hr_utility.set_location('Leaving:'||l_proc,10);
3851 --
3852 end chk_profile_flags;
3853 --
3854 -- ----------------------------------------------------------------------------
3855 -- |--------------------------< dt_update_validate >--------------------------|
3856 -- ----------------------------------------------------------------------------
3857 -- {Start Of Comments}
3858 --
3859 -- Description:
3860 -- This procedure is used for referential integrity of datetracked
3861 -- parent entities when a datetrack update operation is taking place
3862 -- and where there is no cascading of update defined for this entity.
3863 --
3864 -- Prerequisites:
3865 -- This procedure is called from the update_validate.
3866 --
3867 -- In Parameters:
3868 --
3869 -- Post Success:
3870 -- Processing continues.
3871 --
3872 -- Post Failure:
3873 --
3874 -- Developer Implementation Notes:
3875 -- This procedure should not need maintenance unless the HR Schema model
3876 -- changes.
3877 --
3878 -- Access Status:
3879 -- Internal Row Handler Use Only.
3880 --
3881 -- {End Of Comments}
3882 -- ----------------------------------------------------------------------------
3883 Procedure dt_update_validate
3884 (p_oipl_id in number default hr_api.g_number,
3885 p_pl_typ_opt_typ_id in number default hr_api.g_number,
3886 p_pl_id in number default hr_api.g_number,
3887 p_datetrack_mode in varchar2,
3888 p_validation_start_date in date,
3889 p_validation_end_date in date) Is
3890 --
3891 l_proc varchar2(72) := g_package||'dt_update_validate';
3892 l_integrity_error Exception;
3893 l_table_name all_tables.table_name%TYPE;
3894 --
3895 Begin
3896 hr_utility.set_location('Entering:'||l_proc, 5);
3897 --
3898 -- Ensure that the p_datetrack_mode argument is not null
3899 --
3900 hr_api.mandatory_arg_error
3901 (p_api_name => l_proc,
3902 p_argument => 'datetrack_mode',
3903 p_argument_value => p_datetrack_mode);
3904 --
3905 -- Only perform the validation if the datetrack update mode is valid
3906 --
3907 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
3908 --
3909 --
3910 -- Ensure the arguments are not null
3911 --
3912 hr_api.mandatory_arg_error
3913 (p_api_name => l_proc,
3914 p_argument => 'validation_start_date',
3915 p_argument_value => p_validation_start_date);
3916 --
3917 hr_api.mandatory_arg_error
3918 (p_api_name => l_proc,
3919 p_argument => 'validation_end_date',
3920 p_argument_value => p_validation_end_date);
3921 --
3922 If ((nvl(p_oipl_id, hr_api.g_number) <> hr_api.g_number) and
3923 NOT (dt_api.check_min_max_dates
3924 (p_base_table_name => 'ben_oipl_f',
3925 p_base_key_column => 'oipl_id',
3926 p_base_key_value => p_oipl_id,
3927 p_from_date => p_validation_start_date,
3928 p_to_date => p_validation_end_date))) Then
3929 l_table_name := 'ben_oipl_f';
3930 Raise l_integrity_error;
3931 End If;
3932 --If ((nvl(p_pl_typ_opt_typ_id, hr_api.g_number) <> hr_api.g_number) and
3933 --NOT (dt_api.check_min_max_dates
3934 -- (p_base_table_name => 'ben_pl_typ_opt_typ_f',
3935 -- p_base_key_column => 'pl_typ_opt_typ_id',
3936 -- p_base_key_value => p_pl_typ_opt_typ_id,
3937 -- p_from_date => p_validation_start_date,
3938 -- p_to_date => p_validation_end_date))) Then
3939 --l_table_name := 'ben_pl_typ_opt_typ_f';
3940 --Raise l_integrity_error;
3941 --End If;
3942 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
3943 NOT (dt_api.check_min_max_dates
3944 (p_base_table_name => 'ben_pl_f',
3945 p_base_key_column => 'pl_id',
3946 p_base_key_value => p_pl_id,
3947 p_from_date => p_validation_start_date,
3948 p_to_date => p_validation_end_date))) Then
3949 l_table_name := 'ben_pl_f';
3950 Raise l_integrity_error;
3951 End If;
3952 --
3953 End If;
3954 --
3955 hr_utility.set_location(' Leaving:'||l_proc, 10);
3956 Exception
3957 When l_integrity_error Then
3958 --
3959 -- A referential integrity check was violated therefore
3960 -- we must error
3961 --
3962 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
3963 hr_utility.set_message_token('TABLE_NAME', l_table_name);
3964 hr_utility.raise_error;
3965 When Others Then
3966 --
3967 -- An unhandled or unexpected error has occurred which
3968 -- we must report
3969 --
3970 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
3971 hr_utility.set_message_token('PROCEDURE', l_proc);
3972 hr_utility.set_message_token('STEP','15');
3973 hr_utility.raise_error;
3974 End dt_update_validate;
3975 --
3976 -- ----------------------------------------------------------------------------
3977 -- |--------------------------< dt_delete_validate >--------------------------|
3978 -- ----------------------------------------------------------------------------
3979 -- {Start Of Comments}
3980 --
3981 -- Description:
3982 -- This procedure is used for referential integrity of datetracked
3983 -- child entities when either a datetrack DELETE or ZAP is in operation
3984 -- and where there is no cascading of delete defined for this entity.
3985 -- For the datetrack mode of DELETE or ZAP we must ensure that no
3986 -- datetracked child rows exist between the validation start and end
3987 -- dates.
3988 --
3989 -- Prerequisites:
3990 -- This procedure is called from the delete_validate.
3991 --
3992 -- In Parameters:
3993 --
3994 -- Post Success:
3995 -- Processing continues.
3996 --
3997 -- Post Failure:
3998 -- If a row exists by determining the returning Boolean value from the
3999 -- generic dt_api.rows_exist function then we must supply an error via
4000 -- the use of the local exception handler l_rows_exist.
4001 --
4002 -- Developer Implementation Notes:
4003 -- This procedure should not need maintenance unless the HR Schema model
4004 -- changes.
4005 --
4006 -- Access Status:
4007 -- Internal Row Handler Use Only.
4008 --
4009 -- {End Of Comments}
4010 -- ----------------------------------------------------------------------------
4011 Procedure dt_delete_validate
4012 (p_vrbl_rt_prfl_id in number,
4013 p_datetrack_mode in varchar2,
4014 p_validation_start_date in date,
4015 p_name in varchar2,
4016 p_validation_end_date in date) Is
4017 --
4018 l_proc varchar2(72) := g_package||'dt_delete_validate';
4019 l_rows_exist Exception;
4020 l_criteria_rows_exist EXCEPTION;
4021 l_table_name all_tables.table_name%TYPE;
4022 --
4023 Begin
4024 hr_utility.set_location('Entering:'||l_proc, 5);
4025 --
4026 -- Ensure that the p_datetrack_mode argument is not null
4027 --
4028 hr_api.mandatory_arg_error
4029 (p_api_name => l_proc,
4030 p_argument => 'datetrack_mode',
4031 p_argument_value => p_datetrack_mode);
4032 --
4033 -- Only perform the validation if the datetrack mode is either
4034 -- DELETE or ZAP
4035 --
4036 If (p_datetrack_mode = 'DELETE' or
4037 p_datetrack_mode = 'ZAP') then
4038 --
4039 --
4040 -- Ensure the arguments are not null
4041 --
4042 hr_api.mandatory_arg_error
4043 (p_api_name => l_proc,
4044 p_argument => 'validation_start_date',
4045 p_argument_value => p_validation_start_date);
4046 --
4047 hr_api.mandatory_arg_error
4048 (p_api_name => l_proc,
4049 p_argument => 'validation_end_date',
4050 p_argument_value => p_validation_end_date);
4051 --
4052 hr_api.mandatory_arg_error
4053 (p_api_name => l_proc,
4054 p_argument => 'vrbl_rt_prfl_id',
4055 p_argument_value => p_vrbl_rt_prfl_id);
4056 --
4057 If (dt_api.rows_exist
4058 (p_base_table_name => 'BEN_VRBL_RT_ELIG_PRFL_F',
4059 p_base_key_column => 'vrbl_rt_prfl_id',
4060 p_base_key_value => p_vrbl_rt_prfl_id,
4061 p_from_date => p_validation_start_date,
4062 p_to_date => p_validation_end_date)) Then
4063 l_table_name := 'BEN_VRBL_RT_ELIG_PRFL_F';
4064 Raise l_rows_exist;
4065 End If;
4066 --
4067 If (dt_api.rows_exist
4068 (p_base_table_name => 'ben_age_rt_f',
4069 p_base_key_column => 'vrbl_rt_prfl_id',
4070 p_base_key_value => p_vrbl_rt_prfl_id,
4071 p_from_date => p_validation_start_date,
4072 p_to_date => p_validation_end_date)) Then
4073 l_table_name := 'ben_age_rt_f';
4074 Raise l_criteria_rows_exist;
4075 End If;
4076 If (dt_api.rows_exist
4077 (p_base_table_name => 'ben_benfts_grp_rt_f',
4078 p_base_key_column => 'vrbl_rt_prfl_id',
4079 p_base_key_value => p_vrbl_rt_prfl_id,
4080 p_from_date => p_validation_start_date,
4081 p_to_date => p_validation_end_date)) Then
4082 l_table_name := 'ben_benfts_grp_rt_f';
4083 Raise l_criteria_rows_exist;
4084 End If;
4085 If (dt_api.rows_exist
4086 (p_base_table_name => 'ben_loa_rsn_rt_f',
4087 p_base_key_column => 'vrbl_rt_prfl_id',
4088 p_base_key_value => p_vrbl_rt_prfl_id,
4089 p_from_date => p_validation_start_date,
4090 p_to_date => p_validation_end_date)) Then
4091 l_table_name := 'ben_loa_rsn_rt_f';
4092 Raise l_criteria_rows_exist;
4093 End If;
4094 If (dt_api.rows_exist
4095 (p_base_table_name => 'ben_los_rt_f',
4096 p_base_key_column => 'vrbl_rt_prfl_id',
4097 p_base_key_value => p_vrbl_rt_prfl_id,
4098 p_from_date => p_validation_start_date,
4099 p_to_date => p_validation_end_date)) Then
4100 l_table_name := 'ben_los_rt_f';
4101 Raise l_criteria_rows_exist;
4102 End If;
4103 If (dt_api.rows_exist
4104 (p_base_table_name => 'ben_tbco_use_rt_f',
4105 p_base_key_column => 'vrbl_rt_prfl_id',
4106 p_base_key_value => p_vrbl_rt_prfl_id,
4107 p_from_date => p_validation_start_date,
4108 p_to_date => p_validation_end_date)) Then
4109 l_table_name := 'ben_tbco_use_rt_f';
4110 Raise l_criteria_rows_exist;
4111 End If;
4112 If (dt_api.rows_exist
4113 (p_base_table_name => 'ben_pct_fl_tm_rt_f',
4114 p_base_key_column => 'vrbl_rt_prfl_id',
4115 p_base_key_value => p_vrbl_rt_prfl_id,
4116 p_from_date => p_validation_start_date,
4117 p_to_date => p_validation_end_date)) Then
4118 l_table_name := 'ben_pct_fl_tm_rt_f';
4119 Raise l_criteria_rows_exist;
4120 End If;
4121 If (dt_api.rows_exist
4122 (p_base_table_name => 'ben_vrbl_rt_prfl_rl_f',
4123 p_base_key_column => 'vrbl_rt_prfl_id',
4124 p_base_key_value => p_vrbl_rt_prfl_id,
4125 p_from_date => p_validation_start_date,
4126 p_to_date => p_validation_end_date)) Then
4127 l_table_name := 'ben_vrbl_rt_prfl_rl_f';
4128 Raise l_criteria_rows_exist;
4129 End If;
4130 If (dt_api.rows_exist
4131 (p_base_table_name => 'ben_wk_loc_rt_f',
4132 p_base_key_column => 'vrbl_rt_prfl_id',
4133 p_base_key_value => p_vrbl_rt_prfl_id,
4134 p_from_date => p_validation_start_date,
4135 p_to_date => p_validation_end_date)) Then
4136 l_table_name := 'ben_wk_loc_rt_f';
4137 Raise l_criteria_rows_exist;
4138 End If;
4139 If (dt_api.rows_exist
4140 (p_base_table_name => 'ben_org_unit_rt_f',
4141 p_base_key_column => 'vrbl_rt_prfl_id',
4142 p_base_key_value => p_vrbl_rt_prfl_id,
4143 p_from_date => p_validation_start_date,
4144 p_to_date => p_validation_end_date)) Then
4145 l_table_name := 'ben_org_unit_rt_f';
4146 Raise l_criteria_rows_exist;
4147 End If;
4148 If (dt_api.rows_exist
4149 (p_base_table_name => 'ben_comp_lvl_rt_f',
4150 p_base_key_column => 'vrbl_rt_prfl_id',
4151 p_base_key_value => p_vrbl_rt_prfl_id,
4152 p_from_date => p_validation_start_date,
4153 p_to_date => p_validation_end_date)) Then
4154 l_table_name := 'ben_comp_lvl_rt_f';
4155 Raise l_criteria_rows_exist;
4156 End If;
4157 If (dt_api.rows_exist
4158 (p_base_table_name => 'ben_hrs_wkd_in_perd_rt_f',
4159 p_base_key_column => 'vrbl_rt_prfl_id',
4160 p_base_key_value => p_vrbl_rt_prfl_id,
4161 p_from_date => p_validation_start_date,
4162 p_to_date => p_validation_end_date)) Then
4163 l_table_name := 'ben_hrs_wkd_in_perd_rt_f';
4164 Raise l_criteria_rows_exist;
4165 End If;
4166 If (dt_api.rows_exist
4167 (p_base_table_name => 'ben_grade_rt_f',
4168 p_base_key_column => 'vrbl_rt_prfl_id',
4169 p_base_key_value => p_vrbl_rt_prfl_id,
4170 p_from_date => p_validation_start_date,
4171 p_to_date => p_validation_end_date)) Then
4172 l_table_name := 'ben_grade_rt_f';
4173 Raise l_criteria_rows_exist;
4174 End If;
4175 If (dt_api.rows_exist
4176 (p_base_table_name => 'ben_actl_prem_vrbl_rt_f',
4177 p_base_key_column => 'vrbl_rt_prfl_id',
4178 p_base_key_value => p_vrbl_rt_prfl_id,
4179 p_from_date => p_validation_start_date,
4180 p_to_date => p_validation_end_date)) Then
4181 l_table_name := 'ben_actl_prem_vrbl_rt_f';
4182 Raise l_criteria_rows_exist;
4183 End If;
4184 If (dt_api.rows_exist
4185 (p_base_table_name => 'ben_bnft_vrbl_rt_f',
4186 p_base_key_column => 'vrbl_rt_prfl_id',
4187 p_base_key_value => p_vrbl_rt_prfl_id,
4188 p_from_date => p_validation_start_date,
4189 p_to_date => p_validation_end_date)) Then
4190 l_table_name := 'ben_bnft_vrbl_rt_f';
4191 Raise l_rows_exist;
4192 End If;
4193 If (dt_api.rows_exist
4194 (p_base_table_name => 'ben_fl_tm_pt_tm_rt_f',
4195 p_base_key_column => 'vrbl_rt_prfl_id',
4196 p_base_key_value => p_vrbl_rt_prfl_id,
4197 p_from_date => p_validation_start_date,
4198 p_to_date => p_validation_end_date)) Then
4199 l_table_name := 'ben_fl_tm_pt_tm_rt_f';
4200 Raise l_criteria_rows_exist;
4201 End If;
4202 If (dt_api.rows_exist
4203 (p_base_table_name => 'ben_lgl_enty_rt_f',
4204 p_base_key_column => 'vrbl_rt_prfl_id',
4205 p_base_key_value => p_vrbl_rt_prfl_id,
4206 p_from_date => p_validation_start_date,
4207 p_to_date => p_validation_end_date)) Then
4208 l_table_name := 'ben_lgl_enty_rt_f';
4209 Raise l_criteria_rows_exist;
4210 End If;
4211 If (dt_api.rows_exist
4212 (p_base_table_name => 'ben_lbr_mmbr_rt_f',
4213 p_base_key_column => 'vrbl_rt_prfl_id',
4214 p_base_key_value => p_vrbl_rt_prfl_id,
4215 p_from_date => p_validation_start_date,
4216 p_to_date => p_validation_end_date)) Then
4217 l_table_name := 'ben_lbr_mmbr_rt_f';
4218 Raise l_criteria_rows_exist;
4219 End If;
4220 If (dt_api.rows_exist
4221 (p_base_table_name => 'ben_svc_area_rt_f',
4222 p_base_key_column => 'vrbl_rt_prfl_id',
4223 p_base_key_value => p_vrbl_rt_prfl_id,
4224 p_from_date => p_validation_start_date,
4225 p_to_date => p_validation_end_date)) Then
4226 l_table_name := 'ben_svc_area_rt_f';
4227 Raise l_criteria_rows_exist;
4228 End If;
4229 If (dt_api.rows_exist
4230 (p_base_table_name => 'ben_py_bss_rt_f',
4231 p_base_key_column => 'vrbl_rt_prfl_id',
4232 p_base_key_value => p_vrbl_rt_prfl_id,
4233 p_from_date => p_validation_start_date,
4234 p_to_date => p_validation_end_date)) Then
4235 l_table_name := 'ben_py_bss_rt_f';
4236 Raise l_criteria_rows_exist;
4237 End If;
4238 If (dt_api.rows_exist
4239 (p_base_table_name => 'ben_pyrl_rt_f',
4240 p_base_key_column => 'vrbl_rt_prfl_id',
4241 p_base_key_value => p_vrbl_rt_prfl_id,
4242 p_from_date => p_validation_start_date,
4243 p_to_date => p_validation_end_date)) Then
4244 l_table_name := 'ben_pyrl_rt_f';
4245 Raise l_criteria_rows_exist;
4246 End If;
4247 If (dt_api.rows_exist
4248 (p_base_table_name => 'ben_pstl_zip_rt_f',
4249 p_base_key_column => 'vrbl_rt_prfl_id',
4250 p_base_key_value => p_vrbl_rt_prfl_id,
4251 p_from_date => p_validation_start_date,
4252 p_to_date => p_validation_end_date)) Then
4253 l_table_name := 'ben_pstl_zip_rt_f';
4254 Raise l_criteria_rows_exist;
4255 End If;
4256 If (dt_api.rows_exist
4257 (p_base_table_name => 'ben_per_typ_rt_f',
4258 p_base_key_column => 'vrbl_rt_prfl_id',
4259 p_base_key_value => p_vrbl_rt_prfl_id,
4260 p_from_date => p_validation_start_date,
4261 p_to_date => p_validation_end_date)) Then
4262 l_table_name := 'ben_per_typ_rt_f';
4263 Raise l_criteria_rows_exist;
4264 End If;
4265 If (dt_api.rows_exist
4266 (p_base_table_name => 'ben_hrly_slrd_rt_f',
4267 p_base_key_column => 'vrbl_rt_prfl_id',
4268 p_base_key_value => p_vrbl_rt_prfl_id,
4269 p_from_date => p_validation_start_date,
4270 p_to_date => p_validation_end_date)) Then
4271 l_table_name := 'ben_hrly_slrd_rt_f';
4272 Raise l_criteria_rows_exist;
4273 End If;
4274 If (dt_api.rows_exist
4275 (p_base_table_name => 'ben_gndr_rt_f',
4276 p_base_key_column => 'vrbl_rt_prfl_id',
4277 p_base_key_value => p_vrbl_rt_prfl_id,
4278 p_from_date => p_validation_start_date,
4279 p_to_date => p_validation_end_date)) Then
4280 l_table_name := 'ben_gndr_rt_f';
4281 Raise l_criteria_rows_exist;
4282 End If;
4283 If (dt_api.rows_exist
4284 (p_base_table_name => 'ben_cmbn_age_los_rt_f',
4285 p_base_key_column => 'vrbl_rt_prfl_id',
4286 p_base_key_value => p_vrbl_rt_prfl_id,
4287 p_from_date => p_validation_start_date,
4288 p_to_date => p_validation_end_date)) Then
4289 l_table_name := 'ben_cmbn_age_los_rt_f';
4290 Raise l_criteria_rows_exist;
4291 End If;
4292 If (dt_api.rows_exist
4293 (p_base_table_name => 'ben_brgng_unit_rt_f',
4294 p_base_key_column => 'vrbl_rt_prfl_id',
4295 p_base_key_value => p_vrbl_rt_prfl_id,
4296 p_from_date => p_validation_start_date,
4297 p_to_date => p_validation_end_date)) Then
4298 l_table_name := 'ben_brgng_unit_rt_f';
4299 Raise l_criteria_rows_exist;
4300 End If;
4301 If (dt_api.rows_exist
4302 (p_base_table_name => 'ben_asnt_set_rt_f',
4303 p_base_key_column => 'vrbl_rt_prfl_id',
4304 p_base_key_value => p_vrbl_rt_prfl_id,
4305 p_from_date => p_validation_start_date,
4306 p_to_date => p_validation_end_date)) Then
4307 l_table_name := 'ben_asnt_set_rt_f';
4308 Raise l_criteria_rows_exist;
4309 End If;
4310 If (dt_api.rows_exist
4311 (p_base_table_name => 'ben_acty_vrbl_rt_f',
4312 p_base_key_column => 'vrbl_rt_prfl_id',
4313 p_base_key_value => p_vrbl_rt_prfl_id,
4314 p_from_date => p_validation_start_date,
4315 p_to_date => p_validation_end_date)) Then
4316 l_table_name := 'ben_acty_vrbl_rt_f';
4317 Raise l_rows_exist;
4318 End If;
4319 If (dt_api.rows_exist
4320 (p_base_table_name => 'ben_ppl_grp_rt_f',
4321 p_base_key_column => 'vrbl_rt_prfl_id',
4322 p_base_key_value => p_vrbl_rt_prfl_id,
4323 p_from_date => p_validation_start_date,
4324 p_to_date => p_validation_end_date)) Then
4325 l_table_name := 'ben_ppl_grp_rt_f';
4326 Raise l_criteria_rows_exist;
4327 End If;
4328
4329 If (dt_api.rows_exist
4330 (p_base_table_name => 'BEN_CBR_QUALD_BNF_RT_F',
4331 p_base_key_column => 'vrbl_rt_prfl_id',
4332 p_base_key_value => p_vrbl_rt_prfl_id,
4333 p_from_date => p_validation_start_date,
4334 p_to_date => p_validation_end_date)) Then
4335 l_table_name := 'ben_cbr_quald_bnf_rt_f';
4336 Raise l_criteria_rows_exist;
4337 End If;
4338
4339 If (dt_api.rows_exist
4340 (p_base_table_name => 'BEN_CNTNG_PRTN_PRFL_RT_F',
4341 p_base_key_column => 'vrbl_rt_prfl_id',
4342 p_base_key_value => p_vrbl_rt_prfl_id,
4343 p_from_date => p_validation_start_date,
4344 p_to_date => p_validation_end_date)) Then
4345 l_table_name := 'ben_cntng_prtn_prfl_rt_f';
4346 Raise l_criteria_rows_exist;
4347 End If;
4348 If (dt_api.rows_exist
4349 (p_base_table_name => 'BEN_COMPTNCY_RT_F',
4350 p_base_key_column => 'vrbl_rt_prfl_id',
4351 p_base_key_value => p_vrbl_rt_prfl_id,
4352 p_from_date => p_validation_start_date,
4353 p_to_date => p_validation_end_date)) Then
4354 l_table_name := 'ben_comptncy_rt_f';
4355 Raise l_criteria_rows_exist;
4356 End If;
4357 If (dt_api.rows_exist
4358 (p_base_table_name => 'BEN_JOB_RT_F',
4359 p_base_key_column => 'vrbl_rt_prfl_id',
4360 p_base_key_value => p_vrbl_rt_prfl_id,
4361 p_from_date => p_validation_start_date,
4362 p_to_date => p_validation_end_date)) Then
4363 l_table_name := 'ben_job_rt_f';
4364 Raise l_criteria_rows_exist;
4365 End If;
4366 If (dt_api.rows_exist
4367 (p_base_table_name => 'BEN_LVG_RSN_RT_F',
4368 p_base_key_column => 'vrbl_rt_prfl_id',
4369 p_base_key_value => p_vrbl_rt_prfl_id,
4370 p_from_date => p_validation_start_date,
4371 p_to_date => p_validation_end_date)) Then
4372 l_table_name := 'ben_lvg_rsn_rt_f';
4373 Raise l_criteria_rows_exist;
4374 End If;
4375 If (dt_api.rows_exist
4376 (p_base_table_name => 'BEN_OPTD_MDCR_RT_F',
4377 p_base_key_column => 'vrbl_rt_prfl_id',
4378 p_base_key_value => p_vrbl_rt_prfl_id,
4379 p_from_date => p_validation_start_date,
4380 p_to_date => p_validation_end_date)) Then
4381 l_table_name := 'ben_optd_mdcr_rt_f';
4382 Raise l_criteria_rows_exist;
4383 End If;
4384 If (dt_api.rows_exist
4385 (p_base_table_name => 'BEN_PSTN_RT_F',
4386 p_base_key_column => 'vrbl_rt_prfl_id',
4387 p_base_key_value => p_vrbl_rt_prfl_id,
4388 p_from_date => p_validation_start_date,
4389 p_to_date => p_validation_end_date)) Then
4390 l_table_name := 'ben_pstn_rt_f';
4391 Raise l_criteria_rows_exist;
4392 End If;
4393 If (dt_api.rows_exist
4394 (p_base_table_name => 'BEN_DPNT_CVRD_OTHR_PGM_RT_F',
4395 p_base_key_column => 'vrbl_rt_prfl_id',
4396 p_base_key_value => p_vrbl_rt_prfl_id,
4397 p_from_date => p_validation_start_date,
4398 p_to_date => p_validation_end_date)) Then
4399 l_table_name := 'ben_dpnt_cvrd_othr_pgm_rt_f';
4400 Raise l_criteria_rows_exist;
4401 End If;
4402 If (dt_api.rows_exist
4403 (p_base_table_name => 'BEN_DPNT_CVRD_OTHR_PL_RT_F',
4404 p_base_key_column => 'vrbl_rt_prfl_id',
4405 p_base_key_value => p_vrbl_rt_prfl_id,
4406 p_from_date => p_validation_start_date,
4407 p_to_date => p_validation_end_date)) Then
4408 l_table_name := 'ben_dpnt_cvrd_othr_pl_rt_f';
4409 Raise l_criteria_rows_exist;
4410 End If;
4411 If (dt_api.rows_exist
4412 (p_base_table_name => 'BEN_DPNT_CVRD_OTHR_PTIP_RT_F',
4413 p_base_key_column => 'vrbl_rt_prfl_id',
4414 p_base_key_value => p_vrbl_rt_prfl_id,
4415 p_from_date => p_validation_start_date,
4416 p_to_date => p_validation_end_date)) Then
4417 l_table_name := 'ben_dpnt_cvrd_othr_ptip_rt_f';
4418 Raise l_criteria_rows_exist;
4419 End If;
4420 If (dt_api.rows_exist
4421 (p_base_table_name => 'BEN_DPNT_CVRD_PLIP_RT_F',
4422 p_base_key_column => 'vrbl_rt_prfl_id',
4423 p_base_key_value => p_vrbl_rt_prfl_id,
4424 p_from_date => p_validation_start_date,
4425 p_to_date => p_validation_end_date)) Then
4426 l_table_name := 'ben_dpnt_cvrd_othr_plip_rt_f';
4427 Raise l_criteria_rows_exist;
4428 End If;
4429 If (dt_api.rows_exist
4430 (p_base_table_name => 'BEN_DPNT_OTHR_PTIP_RT_F',
4431 p_base_key_column => 'vrbl_rt_prfl_id',
4432 p_base_key_value => p_vrbl_rt_prfl_id,
4433 p_from_date => p_validation_start_date,
4434 p_to_date => p_validation_end_date)) Then
4435 l_table_name := 'ben_dpnt_othr_ptip_rt_f';
4436 Raise l_criteria_rows_exist;
4437 End If;
4438 If (dt_api.rows_exist
4439 (p_base_table_name => 'BEN_QUA_IN_GR_RT_F',
4440 p_base_key_column => 'vrbl_rt_prfl_id',
4441 p_base_key_value => p_vrbl_rt_prfl_id,
4442 p_from_date => p_validation_start_date,
4443 p_to_date => p_validation_end_date)) Then
4444 l_table_name := 'ben_qua_in_gr_rt_f';
4445 Raise l_criteria_rows_exist;
4446 End If;
4447 If (dt_api.rows_exist
4448 (p_base_table_name => 'BEN_PERF_RTNG_RT_F',
4449 p_base_key_column => 'vrbl_rt_prfl_id',
4450 p_base_key_value => p_vrbl_rt_prfl_id,
4451 p_from_date => p_validation_start_date,
4452 p_to_date => p_validation_end_date)) Then
4453 l_table_name := 'ben_perf_rtng_rt_f';
4454 Raise l_criteria_rows_exist;
4455 End If;
4456
4457 If (dt_api.rows_exist
4458 (p_base_table_name => 'BEN_ENRLD_ANTHR_OIPL_RT_F',
4459 p_base_key_column => 'vrbl_rt_prfl_id',
4460 p_base_key_value => p_vrbl_rt_prfl_id,
4461 p_from_date => p_validation_start_date,
4462 p_to_date => p_validation_end_date)) Then
4463 l_table_name := 'ben_enrld_anthr_oipl_rt_f';
4464 Raise l_criteria_rows_exist;
4465 End If;
4466 If (dt_api.rows_exist
4467 (p_base_table_name => 'BEN_ENRLD_ANTHR_PGM_RT_F',
4468 p_base_key_column => 'vrbl_rt_prfl_id',
4469 p_base_key_value => p_vrbl_rt_prfl_id,
4470 p_from_date => p_validation_start_date,
4471 p_to_date => p_validation_end_date)) Then
4472 l_table_name := 'ben_enrld_anthr_pgm_rt_f';
4473 Raise l_criteria_rows_exist;
4474 End If;
4475 If (dt_api.rows_exist
4476 (p_base_table_name => 'BEN_ENRLD_ANTHR_PLIP_RT_F',
4477 p_base_key_column => 'vrbl_rt_prfl_id',
4478 p_base_key_value => p_vrbl_rt_prfl_id,
4479 p_from_date => p_validation_start_date,
4480 p_to_date => p_validation_end_date)) Then
4481 l_table_name := 'ben_enrld_anthr_plip_rt_f';
4482 Raise l_criteria_rows_exist;
4483 End If;
4484 If (dt_api.rows_exist
4485 (p_base_table_name => 'BEN_ENRLD_ANTHR_PL_RT_F',
4486 p_base_key_column => 'vrbl_rt_prfl_id',
4487 p_base_key_value => p_vrbl_rt_prfl_id,
4488 p_from_date => p_validation_start_date,
4489 p_to_date => p_validation_end_date)) Then
4490 l_table_name := 'ben_enrld_anthr_pl_rt_f';
4491 Raise l_criteria_rows_exist;
4492 End If;
4493 If (dt_api.rows_exist
4494 (p_base_table_name => 'BEN_ENRLD_ANTHR_PTIP_RT_F',
4495 p_base_key_column => 'vrbl_rt_prfl_id',
4496 p_base_key_value => p_vrbl_rt_prfl_id,
4497 p_from_date => p_validation_start_date,
4498 p_to_date => p_validation_end_date)) Then
4499 l_table_name := 'ben_enrld_anthr_ptip_rt_f';
4500 Raise l_criteria_rows_exist;
4501 End If;
4502 If (dt_api.rows_exist
4503 (p_base_table_name => 'BEN_NO_OTHR_CVG_RT_F',
4504 p_base_key_column => 'vrbl_rt_prfl_id',
4505 p_base_key_value => p_vrbl_rt_prfl_id,
4506 p_from_date => p_validation_start_date,
4507 p_to_date => p_validation_end_date)) Then
4508 l_table_name := 'ben_no_othr_cvg_rt_f';
4509 Raise l_criteria_rows_exist;
4510 End If;
4511 If (dt_api.rows_exist
4512 (p_base_table_name => 'BEN_OTHR_PTIP_RT_F',
4513 p_base_key_column => 'vrbl_rt_prfl_id',
4514 p_base_key_value => p_vrbl_rt_prfl_id,
4515 p_from_date => p_validation_start_date,
4516 p_to_date => p_validation_end_date)) Then
4517 l_table_name := 'ben_othr_ptip_rt_f';
4518 Raise l_criteria_rows_exist;
4519 End If;
4520 If (dt_api.rows_exist
4521 (p_base_table_name => 'BEN_PRTT_ANTHR_PL_RT_F',
4522 p_base_key_column => 'vrbl_rt_prfl_id',
4523 p_base_key_value => p_vrbl_rt_prfl_id,
4524 p_from_date => p_validation_start_date,
4525 p_to_date => p_validation_end_date)) Then
4526 l_table_name := 'ben_prtt_anthr_pl_rt_f';
4527 Raise l_criteria_rows_exist;
4528 End If;
4529 If (dt_api.rows_exist
4530 (p_base_table_name => 'BEN_QUAL_TITL_RT_F',
4531 p_base_key_column => 'vrbl_rt_prfl_id',
4532 p_base_key_value => p_vrbl_rt_prfl_id,
4533 p_from_date => p_validation_start_date,
4534 p_to_date => p_validation_end_date)) Then
4535 l_table_name := 'ben_qual_titl_rt_f';
4536 Raise l_criteria_rows_exist;
4537 End If;
4538 --
4539 --
4540 --Bug : 3476138
4541 --
4542 If (dt_api.rows_exist
4543 (p_base_table_name => 'ben_bnft_vrbl_rt_f',
4544 p_base_key_column => 'vrbl_rt_prfl_id',
4545 p_base_key_value => p_vrbl_rt_prfl_id,
4546 p_from_date => p_validation_start_date,
4547 p_to_date => p_validation_end_date)) Then
4548 l_table_name := 'ben_bnft_vrbl_rt_f';
4549 Raise l_rows_exist;
4550 End If;
4551 --
4552 --
4553 If (dt_api.rows_exist
4554 (p_base_table_name => 'ben_acty_vrbl_rt_f',
4555 p_base_key_column => 'vrbl_rt_prfl_id',
4556 p_base_key_value => p_vrbl_rt_prfl_id,
4557 p_from_date => p_validation_start_date,
4558 p_to_date => p_validation_end_date)) Then
4559 l_table_name := 'ben_acty_vrbl_rt_f';
4560 Raise l_rows_exist;
4561 End If;
4562 --
4563 --
4564 If (dt_api.rows_exist
4565 (p_base_table_name => 'ben_actl_prem_vrbl_rt_f',
4566 p_base_key_column => 'vrbl_rt_prfl_id',
4567 p_base_key_value => p_vrbl_rt_prfl_id,
4568 p_from_date => p_validation_start_date,
4569 p_to_date => p_validation_end_date)) Then
4570 l_table_name := 'ben_actl_prem_vrbl_rt_f';
4571 Raise l_rows_exist;
4572 End If;
4573 --
4574 --
4575 If (dt_api.rows_exist
4576 (p_base_table_name => 'ben_vrbl_rt_elig_prfl_f',
4577 p_base_key_column => 'vrbl_rt_prfl_id',
4578 p_base_key_value => p_vrbl_rt_prfl_id,
4579 p_from_date => p_validation_start_date,
4580 p_to_date => p_validation_end_date)) Then
4581 l_table_name := 'ben_vrbl_rt_elig_prfl_f';
4582 Raise l_rows_exist;
4583 End If;
4584 --
4585 --Bug : 3476138
4586 --
4587 --
4588 --Bug : 6123832
4589 --
4590 --
4591 If (dt_api.rows_exist
4592 (p_base_table_name => 'ben_dsbld_rt_f',
4593 p_base_key_column => 'vrbl_rt_prfl_id',
4594 p_base_key_value => p_vrbl_rt_prfl_id,
4595 p_from_date => p_validation_start_date,
4596 p_to_date => p_validation_end_date)) Then
4597 l_table_name := 'ben_dsbld_rt_f';
4598 Raise l_rows_exist;
4599 End If;
4600 --
4601 --
4602 If (dt_api.rows_exist
4603 (p_base_table_name => 'ben_ee_stat_rt_f',
4604 p_base_key_column => 'vrbl_rt_prfl_id',
4605 p_base_key_value => p_vrbl_rt_prfl_id,
4606 p_from_date => p_validation_start_date,
4607 p_to_date => p_validation_end_date)) Then
4608 l_table_name := 'ben_ee_stat_rt_f';
4609 Raise l_rows_exist;
4610 End If;
4611 --
4612 --
4613 If (dt_api.rows_exist
4614 (p_base_table_name => 'ben_poe_rt_f',
4615 p_base_key_column => 'vrbl_rt_prfl_id',
4616 p_base_key_value => p_vrbl_rt_prfl_id,
4617 p_from_date => p_validation_start_date,
4618 p_to_date => p_validation_end_date)) Then
4619 l_table_name := 'ben_poe_rt_f';
4620 Raise l_rows_exist;
4621 End If;
4622 --
4623 --
4624 If (dt_api.rows_exist
4625 (p_base_table_name => 'ben_schedd_hrs_rt_f',
4626 p_base_key_column => 'vrbl_rt_prfl_id',
4627 p_base_key_value => p_vrbl_rt_prfl_id,
4628 p_from_date => p_validation_start_date,
4629 p_to_date => p_validation_end_date)) Then
4630 l_table_name := 'ben_schedd_hrs_rt_f';
4631 Raise l_rows_exist;
4632 End If;
4633 --
4634 --
4635 If (dt_api.rows_exist
4636 (p_base_table_name => 'ben_ttl_cvg_vol_rt_f',
4637 p_base_key_column => 'vrbl_rt_prfl_id',
4638 p_base_key_value => p_vrbl_rt_prfl_id,
4639 p_from_date => p_validation_start_date,
4640 p_to_date => p_validation_end_date)) Then
4641 l_table_name := 'ben_ttl_cvg_vol_rt_f';
4642 Raise l_rows_exist;
4643 End If;
4644 --
4645 --
4646 If (dt_api.rows_exist
4647 (p_base_table_name => 'ben_ttl_prtt_rt_f',
4648 p_base_key_column => 'vrbl_rt_prfl_id',
4649 p_base_key_value => p_vrbl_rt_prfl_id,
4650 p_from_date => p_validation_start_date,
4651 p_to_date => p_validation_end_date)) Then
4652 l_table_name := 'ben_ttl_prtt_rt_f';
4653 Raise l_rows_exist;
4654 End If;
4655 --
4656 --
4657 If (dt_api.rows_exist
4658 (p_base_table_name => 'ben_vrbl_rt_prfl_rl_f',
4659 p_base_key_column => 'vrbl_rt_prfl_id',
4660 p_base_key_value => p_vrbl_rt_prfl_id,
4661 p_from_date => p_validation_start_date,
4662 p_to_date => p_validation_end_date)) Then
4663 l_table_name := 'ben_vrbl_rt_prfl_rl_f';
4664 Raise l_rows_exist;
4665 End If;
4666 --
4667 --Bug : 6123832
4668 --
4669 --
4670 End If;
4671 --
4672 hr_utility.set_location(' Leaving:'||l_proc, 10);
4673 Exception
4674 When l_criteria_rows_exist Then
4675 --
4676 -- A referential integrity check was violated therefore
4677 -- we must error
4678 --
4679 ben_utility.child_exists_error(p_table_name => 'BEN_VRBL_RT_PRFL_CRITERIA',
4680 p_parent_table_name => 'BEN_VRBL_RT_PRFL_F',
4681 p_parent_entity_name => p_name);
4682 --
4683 When l_rows_exist Then
4684 --
4685 -- A referential integrity check was violated therefore
4686 -- we must error
4687 --
4688 ben_utility.child_exists_error(p_table_name => l_table_name,
4689 p_parent_table_name => 'BEN_VRBL_RT_PRFL_F',
4690 p_parent_entity_name => p_name);
4691 --
4692 When Others Then
4693 --
4694 -- An unhandled or unexpected error has occurred which
4695 -- we must report
4696 --
4697 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4698 hr_utility.set_message_token('PROCEDURE', l_proc);
4699 hr_utility.set_message_token('STEP','15');
4700 hr_utility.raise_error;
4701 End dt_delete_validate;
4702 --
4703 -- ----------------------------------------------------------------------------
4704 -- |------< chk_mlt_cd_dependencies >------|
4705 -- ----------------------------------------------------------------------------
4706 --
4707 -- Description
4708 --
4709 --
4710 --
4711 -- In Parameters
4712 -- mlt_cd
4713 -- val
4714 -- mn_elcn_val
4715 -- mx_elcn_val
4716 -- incrmnt_elcn_val
4717 -- .dflt_elcn_val
4718 -- rt_typ_cd
4719 -- bnft_rt_typ_cd
4720 -- val_calc_rl
4721 -- vrbl_rt_prfl_id
4722 -- effective_date
4723 -- object_version_number
4724 --
4725 --
4726 Procedure chk_mlt_cd_dependencies(p_mlt_cd in varchar2,
4727 p_val in number,
4728 p_mn_elcn_val in number,
4729 p_mx_elcn_val in number,
4730 p_incrmnt_elcn_val in number,
4731 --Bug: 4237447
4732 p_dflt_elcn_val in number,
4733 --End Bug: 4237447
4734 p_rt_typ_cd in varchar2,
4735 p_bnft_rt_typ_cd in varchar2,
4736 p_val_calc_rl in number,
4737 -- note the following is temporarily being used for
4738 -- enter value at enrollment flag. It should be renamed.
4739 p_no_mn_elcn_val_dfnd_flag in varchar2,
4740 p_comp_lvl_fctr_id in number,
4741 p_vrbl_rt_prfl_id in number,
4742 p_effective_date in date,
4743 p_object_version_number in number
4744 ) is
4745 --
4746 l_proc varchar2(72) := g_package||'chk_mlt_cd_dependencies';
4747 l_api_updating boolean;
4748 --
4749 Begin
4750 --
4751 hr_utility.set_location('Entering:'||l_proc, 5);
4752 --
4753 l_api_updating := ben_vpf_shd.api_updating
4754 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
4755 p_effective_date => p_effective_date,
4756 p_object_version_number => p_object_version_number);
4757 --
4758 if (l_api_updating
4759 and
4760 (nvl(p_mlt_cd,hr_api.g_varchar2)
4761 <> nvl(ben_vpf_shd.g_old_rec.mlt_cd,hr_api.g_varchar2) or
4762 nvl(p_val,hr_api.g_number)
4763 <> nvl(ben_vpf_shd.g_old_rec.val,hr_api.g_number) or
4764 nvl(p_mn_elcn_val,hr_api.g_number)
4765 <> nvl(ben_vpf_shd.g_old_rec.mn_elcn_val,hr_api.g_number) or
4766 nvl(p_mx_elcn_val,hr_api.g_number)
4767 <> nvl(ben_vpf_shd.g_old_rec.mx_elcn_val,hr_api.g_number) or
4768 nvl(p_incrmnt_elcn_val,hr_api.g_number)
4769 <> nvl(ben_vpf_shd.g_old_rec.incrmnt_elcn_val,hr_api.g_number) or
4770 --Bug : 4237447
4771 nvl(p_dflt_elcn_val,hr_api.g_number)
4772 <> nvl(ben_vpf_shd.g_old_rec.dflt_elcn_val,hr_api.g_number) or
4773 -- End Bug: 4237447
4774 nvl(p_rt_typ_cd,hr_api.g_varchar2)
4775 <> nvl(ben_vpf_shd.g_old_rec.rt_typ_cd,hr_api.g_varchar2) or
4776 nvl(p_bnft_rt_typ_cd,hr_api.g_varchar2)
4777 <> nvl(ben_vpf_shd.g_old_rec.bnft_rt_typ_cd,hr_api.g_varchar2) or
4778 nvl(p_val_calc_rl,hr_api.g_number)
4779 <> nvl(ben_vpf_shd.g_old_rec.val_calc_rl,hr_api.g_number) or
4780 nvl(p_comp_lvl_fctr_id ,hr_api.g_number)
4781 <> nvl(ben_vpf_shd.g_old_rec.comp_lvl_fctr_id,hr_api.g_number) or
4782 nvl(p_no_mn_elcn_val_dfnd_flag,hr_api.g_varchar2)
4783 <> nvl(ben_vpf_shd.g_old_rec.no_mn_elcn_val_dfnd_flag,hr_api.g_varchar2)
4784 ))
4785 or
4786 not l_api_updating then
4787 --
4788 if p_mlt_cd is NULL then
4789 --
4790 fnd_message.set_name('BEN','BEN_91535_MLT_CD_RQD');
4791 fnd_message.raise_error;
4792 --
4793 end if;
4794 --
4795 -- the following is just not true. you can't have a value
4796 -- for enter value at enrollment.
4797 if p_val is NULL then
4798 --
4799
4800 if p_mlt_cd in ('FLFX','FLFXPCL','CL','AP','CVG','CLANDCVG','APANDCVG')
4801 and p_no_mn_elcn_val_dfnd_flag = 'N' then
4802 --
4803 fnd_message.set_name('BEN','BEN_91536_VAL_RQD');
4804 fnd_message.raise_error;
4805 --
4806 end if;
4807 --
4808 end if;
4809 --
4810
4811 if p_mn_elcn_val is NULL then
4812 --
4813 if (p_mlt_cd in ('FLFX','CL','CVG','CLANDCVG')
4814 and p_no_mn_elcn_val_dfnd_flag = 'Y')
4815 or p_mlt_cd = 'FLFXPCL' then
4816 --
4817 fnd_message.set_name('BEN','BEN_91538_MIN_VAL_RQD');
4818 fnd_message.raise_error;
4819 --
4820 end if;
4821 --
4822 end if;
4823 --
4824 if p_mx_elcn_val is NULL then
4825 --
4826 if p_mlt_cd in ('FLFX','CL','CVG','CLANDCVG')
4827 and p_no_mn_elcn_val_dfnd_flag = 'Y' then
4828 --
4829 fnd_message.set_name('BEN','BEN_91540_MAX_VAL_REQ');
4830 fnd_message.raise_error;
4831 --
4832 end if;
4833 --
4834 end if;
4835 --
4836 if p_incrmnt_elcn_val is NULL then
4837 --
4838 if p_mlt_cd in ('FLFX','CL','CVG','CLANDCVG')
4839 and p_no_mn_elcn_val_dfnd_flag = 'Y' then
4840 --
4841 fnd_message.set_name('BEN','BEN_91542_INCRMT_VAL_RQD');
4842 fnd_message.raise_error;
4843 --
4844 end if;
4845 end if;
4846 -- Bug: 4237447
4847 if p_dflt_elcn_val is not NULL then
4848 if p_mlt_cd in ('FLFX','CL','CVG','CLANDCVG') and p_no_mn_elcn_val_dfnd_flag = 'Y' and (p_dflt_elcn_val < p_mn_elcn_val or p_dflt_elcn_val > p_mx_elcn_val) then
4849 fnd_message.set_name('PAY','HR_INPVAL_DEFAULT_INVALID');
4850 fnd_message.raise_error;
4851 end if;
4852 end if;
4853 --End Bug: 4237447
4854 if p_rt_typ_cd is NULL then
4855 --
4856 if p_mlt_cd in ('CL','AP','CLANDCVG','APANDCVG','FLFXPCL') then
4857 --
4858 fnd_message.set_name('BEN','BEN_91544_RT_TYP_CD_RQD');
4859 fnd_message.raise_error;
4860 --
4861 end if;
4862 --
4863 end if;
4864 --
4865 if p_bnft_rt_typ_cd is NULL then
4866 --
4867 if p_mlt_cd in ('CVG','CLANDCVG','APANDCVG') then
4868 --
4869 fnd_message.set_name('BEN','BEN_91546_BNFTS_TYP_CD_RQD');
4870 fnd_message.raise_error;
4871 --
4872 end if;
4873 --
4874 --
4875 end if;
4876 --
4877 if p_comp_lvl_fctr_id is NULL then
4878 --
4879 if p_mlt_cd in ('CL','CLANDCVG','FLFXPCL') then
4880 --
4881 fnd_message.set_name('BEN','BEN_92472_COMP_FCTR_RQD');
4882 fnd_message.raise_error;
4883 --
4884 end if;
4885 --
4886 --
4887 end if;
4888 --
4889 if p_val_calc_rl is NULL then
4890 --
4891 if p_mlt_cd in ('RL') then
4892 --
4893 fnd_message.set_name('BEN','BEN_91548_VAL_CALC_RL_RQD');
4894 fnd_message.raise_error;
4895 --
4896 end if;
4897 --
4898 else
4899 --
4900 if p_mlt_cd not in ('RL')
4901 then
4902 --
4903 fnd_message.set_name('BEN','BEN_91549_VAL_CALC_RL_SPEC');
4904 fnd_message.raise_error;
4905 --
4906 end if;
4907 end if;
4908 --
4909 end if;
4910
4911 --
4912 hr_utility.set_location('Leaving:'||l_proc,10);
4913 --
4914 end chk_mlt_cd_dependencies;
4915 --
4916 --
4917 -- ----------------------------------------------------------------------------
4918 -- |---------------------------< insert_validate >----------------------------|
4919 -- ----------------------------------------------------------------------------
4920 Procedure insert_validate
4921 (p_rec in ben_vpf_shd.g_rec_type,
4922 p_effective_date in date,
4923 p_datetrack_mode in varchar2,
4924 p_validation_start_date in date,
4925 p_validation_end_date in date) is
4926 --
4927 l_proc varchar2(72) := g_package||'insert_validate';
4928 --
4929 Begin
4930 hr_utility.set_location('Entering:'||l_proc, 5);
4931 --
4932 -- Call all supporting business operations
4933 --
4934 --
4935 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
4936 --
4937 chk_vrbl_rt_prfl_id
4938 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
4939 p_effective_date => p_effective_date,
4940 p_object_version_number => p_rec.object_version_number);
4941 --
4942 chk_name_unique
4943 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
4944 p_name => p_rec.name,
4945 p_business_group_id => p_rec.business_group_id);
4946 --
4947 chk_mn_mx_elcn_vals
4948 (p_mn_elcn_val => p_rec.mn_elcn_val,
4949 p_mx_elcn_val => p_rec.mx_elcn_val);
4950 --
4951 chk_lwr_upr_lmt_vals
4952 (p_lwr_lmt_val => p_rec.lwr_lmt_val,
4953 p_upr_lmt_val => p_rec.upr_lmt_val);
4954 --
4955 chk_ultmt_lwr_upr_lmt
4956 (p_ultmt_lwr_lmt => p_rec.ultmt_lwr_lmt,
4957 p_ultmt_upr_lmt => p_rec.ultmt_upr_lmt);
4958 --
4959 chk_lwr_lmt_val_and_rl
4960 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
4961 p_lwr_lmt_val => p_rec.lwr_lmt_val,
4962 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl);
4963 --
4964 chk_ultmt_lwr_lmt_val_and_rl
4965 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
4966 p_ultmt_lwr_lmt => p_rec.ultmt_lwr_lmt,
4967 p_ultmt_lwr_lmt_calc_rl => p_rec.ultmt_lwr_lmt_calc_rl);
4968 --
4969 chk_upr_lmt_val_and_rl
4970 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
4971 p_upr_lmt_val => p_rec.upr_lmt_val,
4972 p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl);
4973 --
4974 chk_ultmt_upr_lmt_val_and_rl
4975 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
4976 p_ultmt_upr_lmt => p_rec.ultmt_upr_lmt,
4977 p_ultmt_upr_lmt_calc_rl => p_rec.ultmt_upr_lmt_calc_rl);
4978 --
4979
4980 chk_vrbl_rt_prfl_stat_cd
4981 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
4982 p_vrbl_rt_prfl_stat_cd => p_rec.vrbl_rt_prfl_stat_cd,
4983 p_effective_date => p_effective_date,
4984 p_object_version_number => p_rec.object_version_number);
4985 --
4986 chk_val_calc_rl
4987 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
4988 p_val_calc_rl => p_rec.val_calc_rl,
4989 p_business_group_id => p_rec.business_group_id,
4990 p_effective_date => p_effective_date,
4991 p_object_version_number => p_rec.object_version_number);
4992 --
4993 chk_rndg_rl
4994 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
4995 p_rndg_rl => p_rec.rndg_rl,
4996 p_business_group_id => p_rec.business_group_id,
4997 p_effective_date => p_effective_date,
4998 p_object_version_number => p_rec.object_version_number);
4999 --
5000 chk_lwr_lmt_calc_rl
5001 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5002 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl,
5003 p_business_group_id => p_rec.business_group_id,
5004 p_effective_date => p_effective_date,
5005 p_object_version_number => p_rec.object_version_number);
5006 --
5007 chk_upr_lmt_calc_rl
5008 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5009 p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl,
5010 p_business_group_id => p_rec.business_group_id,
5011 p_effective_date => p_effective_date,
5012 p_object_version_number => p_rec.object_version_number);
5013 --
5014
5015 chk_ultmt_lwr_lmt_calc_rl
5016 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5017 p_ultmt_lwr_lmt_calc_rl => p_rec.ultmt_lwr_lmt_calc_rl,
5018 p_business_group_id => p_rec.business_group_id,
5019 p_effective_date => p_effective_date,
5020 p_object_version_number => p_rec.object_version_number);
5021 --
5022 chk_ultmt_upr_lmt_calc_rl
5023 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5024 p_ultmt_upr_lmt_calc_rl => p_rec.ultmt_upr_lmt_calc_rl,
5025 p_business_group_id => p_rec.business_group_id,
5026 p_effective_date => p_effective_date,
5027 p_object_version_number => p_rec.object_version_number);
5028 --
5029 chk_no_mx_elcn_val_dfnd_flag
5030 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5031 p_no_mx_elcn_val_dfnd_flag => p_rec.no_mx_elcn_val_dfnd_flag,
5032 p_effective_date => p_effective_date,
5033 p_object_version_number => p_rec.object_version_number);
5034 --
5035 chk_no_mn_elcn_val_dfnd_flag
5036 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5037 p_no_mn_elcn_val_dfnd_flag => p_rec.no_mn_elcn_val_dfnd_flag,
5038 p_effective_date => p_effective_date,
5039 p_object_version_number => p_rec.object_version_number);
5040 --
5041 chk_alwys_sum_all_cvg_flag
5042 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5043 p_alwys_sum_all_cvg_flag => p_rec.alwys_sum_all_cvg_flag,
5044 p_effective_date => p_effective_date,
5045 p_object_version_number => p_rec.object_version_number);
5046 --
5047 chk_alwys_cnt_all_prtts_flag
5048 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5049 p_alwys_cnt_all_prtts_flag => p_rec.alwys_cnt_all_prtts_flag,
5050 p_effective_date => p_effective_date,
5051 p_object_version_number => p_rec.object_version_number);
5052 --
5053 chk_mlt_cd
5054 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5055 p_mlt_cd => p_rec.mlt_cd,
5056 p_vrbl_usg_cd => p_rec.vrbl_usg_cd,
5057 p_effective_date => p_effective_date,
5058 p_object_version_number => p_rec.object_version_number);
5059 --
5060 chk_vrbl_usg_cd
5061 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5062 p_vrbl_usg_cd => p_rec.vrbl_usg_cd,
5063 p_effective_date => p_effective_date,
5064 p_object_version_number => p_rec.object_version_number);
5065 --
5066 chk_asmt_to_use_cd
5067 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5068 p_asmt_to_use_cd => p_rec.asmt_to_use_cd,
5069 p_effective_date => p_effective_date,
5070 p_object_version_number => p_rec.object_version_number);
5071 --
5072 chk_rndg_cd
5073 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5074 p_rndg_cd => p_rec.rndg_cd,
5075 p_effective_date => p_effective_date,
5076 p_object_version_number => p_rec.object_version_number);
5077 --
5078 chk_rndg_cd_rl
5079 (p_rndg_cd => p_rec.rndg_cd,
5080 p_rndg_rl => p_rec.rndg_rl);
5081 --
5082 chk_acty_ref_perd_cd
5083 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5084 p_acty_ref_perd_cd => p_rec.acty_ref_perd_cd,
5085 p_effective_date => p_effective_date,
5086 p_object_version_number => p_rec.object_version_number);
5087 --
5088 chk_ref_perd_usg_cd
5089 (p_acty_ref_perd_cd => p_rec.acty_ref_perd_cd,
5090 p_vrbl_usg_cd => p_rec.vrbl_usg_cd);
5091 --
5092 chk_val_othr_val
5093 (p_val => p_rec.val,
5094 p_mn_elcn_val => p_rec.mn_elcn_val,
5095 p_mx_elcn_val => p_rec.mx_elcn_val,
5096 p_incrmnt_elcn_val => p_rec.incrmnt_elcn_val,
5097 p_dflt_elcn_val => p_rec.dflt_elcn_val,
5098 p_mlt_cd => p_rec.mlt_cd);
5099 --
5100 chk_vrbl_rt_trtmt_cd
5101 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5102 p_vrbl_rt_trtmt_cd => p_rec.vrbl_rt_trtmt_cd,
5103 p_effective_date => p_effective_date,
5104 p_object_version_number => p_rec.object_version_number);
5105 --
5106 chk_tx_typ_cd
5107 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5108 p_tx_typ_cd => p_rec.tx_typ_cd,
5109 p_effective_date => p_effective_date,
5110 p_object_version_number => p_rec.object_version_number);
5111 --
5112 chk_bnft_rt_typ_cd
5113 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5114 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
5115 p_effective_date => p_effective_date,
5116 p_object_version_number => p_rec.object_version_number);
5117 --
5118 chk_rt_typ_cd
5119 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5120 p_rt_typ_cd => p_rec.rt_typ_cd,
5121 p_effective_date => p_effective_date,
5122 p_object_version_number => p_rec.object_version_number);
5123 --
5124 chk_acty_typ_cd
5125 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5126 p_acty_typ_cd => p_rec.acty_typ_cd,
5127 p_effective_date => p_effective_date,
5128 p_object_version_number => p_rec.object_version_number);
5129 --
5130 chk_mlt_cd_dependencies
5131 (p_mlt_cd => p_rec.mlt_cd,
5132 p_val => p_rec.val,
5133 p_mn_elcn_val => p_rec.mn_elcn_val,
5134 p_mx_elcn_val => p_rec.mx_elcn_val,
5135 p_incrmnt_elcn_val => p_rec.incrmnt_elcn_val,
5136 --Bug : 4237447
5137 p_dflt_elcn_val => p_rec.dflt_elcn_val,
5138 --End Bug: 4237447
5139 p_rt_typ_cd => p_rec.rt_typ_cd,
5140 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
5141 p_val_calc_rl => p_rec.val_calc_rl,
5142 p_no_mn_elcn_val_dfnd_flag => p_rec.no_mn_elcn_val_dfnd_flag,
5143 p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
5144 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5145 p_effective_date => p_effective_date,
5146 p_object_version_number => p_rec.object_version_number
5147 );
5148 -- Bug : 3456400
5149 if p_rec.rt_elig_prfl_flag = 'Y' then
5150 --
5151 chk_elig_alwys_cnt_all_prtt_df
5152 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5153 p_alwys_cnt_all_prtts_flag => p_rec.alwys_cnt_all_prtts_flag,
5154 p_effective_date => p_effective_date,
5155 p_business_group_id => p_rec.business_group_id);
5156 --
5157 chk_elig_alwys_sum_all_cvg_def
5158 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5159 p_alwys_sum_all_cvg_flag => p_rec.alwys_sum_all_cvg_flag,
5160 p_effective_date => p_effective_date,
5161 p_business_group_id => p_rec.business_group_id);
5162 --
5163 else
5164 --
5165 chk_alwys_cnt_all_prtts_def
5166 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5167 p_alwys_cnt_all_prtts_flag => p_rec.alwys_cnt_all_prtts_flag,
5168 p_effective_date => p_effective_date,
5169 p_business_group_id => p_rec.business_group_id);
5170 --
5171 chk_alwys_sum_all_cvg_def
5172 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5173 p_alwys_sum_all_cvg_flag => p_rec.alwys_sum_all_cvg_flag,
5174 p_effective_date => p_effective_date,
5175 p_business_group_id => p_rec.business_group_id);
5176 --
5177 end if;
5178 --
5179 chk_usg_acty_tx_for_prem
5180 (p_vrbl_usg_cd => p_rec.vrbl_usg_cd,
5181 p_acty_typ_cd => p_rec.acty_typ_cd,
5182 p_tx_typ_cd => p_rec.tx_typ_cd);
5183 --
5184 chk_alwys_flag_one_def
5185 (p_alwys_cnt_all_prtts_flag => p_rec.alwys_cnt_all_prtts_flag,
5186 p_alwys_sum_all_cvg_flag => p_rec.alwys_sum_all_cvg_flag);
5187
5188 ---- Updated for bug 2529689
5189 IF (p_rec.vrbl_usg_cd = 'ACP' ) THEN
5190 -- Bug : 3456400
5191 if p_rec.rt_elig_prfl_flag = 'Y' then
5192 --
5193 chk_elig_ttlprtt_mtl_cd
5194 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5195 p_mlt_cd => p_rec.mlt_cd,
5196 p_effective_date => p_effective_date,
5197 p_business_group_id => p_rec.business_group_id);
5198 --
5199 else
5200 --
5201 chk_ttlprtt_mtl_cd
5202 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5203 p_mlt_cd => p_rec.mlt_cd,
5204 p_effective_date => p_effective_date,
5205 p_business_group_id => p_rec.business_group_id);
5206 --
5207 end if;
5208 --
5209 END IF;
5210 --
5211
5212 hr_utility.set_location(' Leaving:'||l_proc, 10);
5213 End insert_validate;
5214 --
5215 -- ----------------------------------------------------------------------------
5216 -- |---------------------------< update_validate >----------------------------|
5217 -- ----------------------------------------------------------------------------
5218 Procedure update_validate
5219 (p_rec in ben_vpf_shd.g_rec_type,
5220 p_effective_date in date,
5221 p_datetrack_mode in varchar2,
5222 p_validation_start_date in date,
5223 p_validation_end_date in date) is
5224 --
5225 l_proc varchar2(72) := g_package||'update_validate';
5226 --
5227 Begin
5228 hr_utility.set_location('Entering:'||l_proc, 5);
5229 --
5230 -- Call all supporting business operations
5231 --
5232 --
5233 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
5234 --
5235 chk_vrbl_rt_prfl_id
5236 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5237 p_effective_date => p_effective_date,
5238 p_object_version_number => p_rec.object_version_number);
5239 --
5240 chk_name_unique
5241 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5242 p_name => p_rec.name,
5243 p_business_group_id => p_rec.business_group_id);
5244 --
5245 chk_mn_mx_elcn_vals
5246 (p_mn_elcn_val => p_rec.mn_elcn_val,
5247 p_mx_elcn_val => p_rec.mx_elcn_val);
5248 --
5249 chk_lwr_upr_lmt_vals
5250 (p_lwr_lmt_val => p_rec.lwr_lmt_val,
5251 p_upr_lmt_val => p_rec.upr_lmt_val);
5252 --
5253 chk_ultmt_lwr_upr_lmt
5254 (p_ultmt_lwr_lmt => p_rec.ultmt_lwr_lmt,
5255 p_ultmt_upr_lmt => p_rec.ultmt_upr_lmt);
5256 --
5257 chk_lwr_lmt_val_and_rl
5258 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5259 p_lwr_lmt_val => p_rec.lwr_lmt_val,
5260 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl);
5261 --
5262 chk_ultmt_lwr_lmt_val_and_rl
5263 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5264 p_ultmt_lwr_lmt => p_rec.ultmt_lwr_lmt,
5265 p_ultmt_lwr_lmt_calc_rl => p_rec.ultmt_lwr_lmt_calc_rl);
5266 --
5267 chk_ultmt_upr_lmt_val_and_rl
5268 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5269 p_ultmt_upr_lmt => p_rec.ultmt_upr_lmt,
5270 p_ultmt_upr_lmt_calc_rl => p_rec.ultmt_upr_lmt_calc_rl);
5271 --
5272
5273 chk_upr_lmt_val_and_rl
5274 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5275 p_upr_lmt_val => p_rec.upr_lmt_val,
5276 p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl);
5277 --
5278 chk_vrbl_rt_prfl_stat_cd
5279 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5280 p_vrbl_rt_prfl_stat_cd => p_rec.vrbl_rt_prfl_stat_cd,
5281 p_effective_date => p_effective_date,
5282 p_object_version_number => p_rec.object_version_number);
5283 --
5284 chk_val_calc_rl
5285 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5286 p_val_calc_rl => p_rec.val_calc_rl,
5287 p_business_group_id => p_rec.business_group_id,
5288 p_effective_date => p_effective_date,
5289 p_object_version_number => p_rec.object_version_number);
5290 --
5291 chk_rndg_rl
5292 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5293 p_rndg_rl => p_rec.rndg_rl,
5294 p_business_group_id => p_rec.business_group_id,
5295 p_effective_date => p_effective_date,
5296 p_object_version_number => p_rec.object_version_number);
5297 --
5298 chk_lwr_lmt_calc_rl
5299 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5300 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl,
5301 p_business_group_id => p_rec.business_group_id,
5302 p_effective_date => p_effective_date,
5303 p_object_version_number => p_rec.object_version_number);
5304 --
5305 chk_upr_lmt_calc_rl
5306 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5307 p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl,
5308 p_business_group_id => p_rec.business_group_id,
5309 p_effective_date => p_effective_date,
5310 p_object_version_number => p_rec.object_version_number);
5311 --
5312 chk_ultmt_lwr_lmt_calc_rl
5313 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5314 p_ultmt_lwr_lmt_calc_rl => p_rec.ultmt_lwr_lmt_calc_rl,
5315 p_business_group_id => p_rec.business_group_id,
5316 p_effective_date => p_effective_date,
5317 p_object_version_number => p_rec.object_version_number);
5318 --
5319 chk_ultmt_upr_lmt_calc_rl
5320 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5321 p_ultmt_upr_lmt_calc_rl => p_rec.ultmt_upr_lmt_calc_rl,
5322 p_business_group_id => p_rec.business_group_id,
5323 p_effective_date => p_effective_date,
5324 p_object_version_number => p_rec.object_version_number);
5325
5326 chk_no_mx_elcn_val_dfnd_flag
5327 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5328 p_no_mx_elcn_val_dfnd_flag => p_rec.no_mx_elcn_val_dfnd_flag,
5329 p_effective_date => p_effective_date,
5330 p_object_version_number => p_rec.object_version_number);
5331 --
5332 chk_no_mn_elcn_val_dfnd_flag
5333 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5334 p_no_mn_elcn_val_dfnd_flag => p_rec.no_mn_elcn_val_dfnd_flag,
5335 p_effective_date => p_effective_date,
5336 p_object_version_number => p_rec.object_version_number);
5337 --
5338 chk_alwys_sum_all_cvg_flag
5339 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5340 p_alwys_sum_all_cvg_flag => p_rec.alwys_sum_all_cvg_flag,
5341 p_effective_date => p_effective_date,
5342 p_object_version_number => p_rec.object_version_number);
5343 --
5344 chk_alwys_cnt_all_prtts_flag
5345 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5346 p_alwys_cnt_all_prtts_flag => p_rec.alwys_cnt_all_prtts_flag,
5347 p_effective_date => p_effective_date,
5348 p_object_version_number => p_rec.object_version_number);
5349 --
5350 chk_mlt_cd
5351 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5352 p_mlt_cd => p_rec.mlt_cd,
5353 p_vrbl_usg_cd => p_rec.vrbl_usg_cd,
5354 p_effective_date => p_effective_date,
5355 p_object_version_number => p_rec.object_version_number);
5356 --
5357 chk_vrbl_usg_cd
5358 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5359 p_vrbl_usg_cd => p_rec.vrbl_usg_cd,
5360 p_effective_date => p_effective_date,
5361 p_object_version_number => p_rec.object_version_number);
5362 --
5363 chk_asmt_to_use_cd
5364 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5365 p_asmt_to_use_cd => p_rec.asmt_to_use_cd,
5366 p_effective_date => p_effective_date,
5367 p_object_version_number => p_rec.object_version_number);
5368 --
5369 chk_rndg_cd
5370 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5371 p_rndg_cd => p_rec.rndg_cd,
5372 p_effective_date => p_effective_date,
5373 p_object_version_number => p_rec.object_version_number);
5374 --
5375 chk_rndg_cd_rl
5376 (p_rndg_cd => p_rec.rndg_cd,
5377 p_rndg_rl => p_rec.rndg_rl);
5378 --
5379 chk_acty_ref_perd_cd
5380 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5381 p_acty_ref_perd_cd => p_rec.acty_ref_perd_cd,
5382 p_effective_date => p_effective_date,
5383 p_object_version_number => p_rec.object_version_number);
5384 --
5385 chk_ref_perd_usg_cd
5386 (p_acty_ref_perd_cd => p_rec.acty_ref_perd_cd,
5387 p_vrbl_usg_cd => p_rec.vrbl_usg_cd);
5388 --
5389 chk_val_othr_val
5390 (p_val => p_rec.val,
5391 p_mn_elcn_val => p_rec.mn_elcn_val,
5392 p_mx_elcn_val => p_rec.mx_elcn_val,
5393 p_incrmnt_elcn_val => p_rec.incrmnt_elcn_val,
5394 p_dflt_elcn_val => p_rec.dflt_elcn_val,
5395 p_mlt_cd => p_rec.mlt_cd);
5396 --
5397 chk_vrbl_rt_trtmt_cd
5398 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5399 p_vrbl_rt_trtmt_cd => p_rec.vrbl_rt_trtmt_cd,
5400 p_effective_date => p_effective_date,
5401 p_object_version_number => p_rec.object_version_number);
5402 --
5403 chk_tx_typ_cd
5404 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5405 p_tx_typ_cd => p_rec.tx_typ_cd,
5406 p_effective_date => p_effective_date,
5407 p_object_version_number => p_rec.object_version_number);
5408 --
5409 chk_bnft_rt_typ_cd
5410 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5411 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
5412 p_effective_date => p_effective_date,
5413 p_object_version_number => p_rec.object_version_number);
5414 --
5415 chk_rt_typ_cd
5416 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5417 p_rt_typ_cd => p_rec.rt_typ_cd,
5418 p_effective_date => p_effective_date,
5419 p_object_version_number => p_rec.object_version_number);
5420 --
5421 chk_acty_typ_cd
5422 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5423 p_acty_typ_cd => p_rec.acty_typ_cd,
5424 p_effective_date => p_effective_date,
5425 p_object_version_number => p_rec.object_version_number);
5426 --
5427 -- Bug : 3456400
5428 if p_rec.rt_elig_prfl_flag = 'Y' then
5429 --
5430 chk_elig_alwys_cnt_all_prtt_df
5431 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5432 p_alwys_cnt_all_prtts_flag => p_rec.alwys_cnt_all_prtts_flag,
5433 p_effective_date => p_effective_date,
5434 p_business_group_id => p_rec.business_group_id);
5435 --
5436 chk_elig_alwys_sum_all_cvg_def
5437 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5438 p_alwys_sum_all_cvg_flag => p_rec.alwys_sum_all_cvg_flag,
5439 p_effective_date => p_effective_date,
5440 p_business_group_id => p_rec.business_group_id);
5441 --
5442 else
5443 --
5444 chk_alwys_cnt_all_prtts_def
5445 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5446 p_alwys_cnt_all_prtts_flag => p_rec.alwys_cnt_all_prtts_flag,
5447 p_effective_date => p_effective_date,
5448 p_business_group_id => p_rec.business_group_id);
5449 --
5450 chk_alwys_sum_all_cvg_def
5451 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5452 p_alwys_sum_all_cvg_flag => p_rec.alwys_sum_all_cvg_flag,
5453 p_effective_date => p_effective_date,
5454 p_business_group_id => p_rec.business_group_id);
5455 --
5456 end if;
5457 --
5458 chk_usg_acty_tx_for_prem
5459 (p_vrbl_usg_cd => p_rec.vrbl_usg_cd,
5460 p_acty_typ_cd => p_rec.acty_typ_cd,
5461 p_tx_typ_cd => p_rec.tx_typ_cd);
5462 --
5463 chk_alwys_flag_one_def
5464 (p_alwys_cnt_all_prtts_flag => p_rec.alwys_cnt_all_prtts_flag,
5465 p_alwys_sum_all_cvg_flag => p_rec.alwys_sum_all_cvg_flag);
5466 --
5467 -- Updated for bug 2529689
5468 IF (p_rec.vrbl_usg_cd = 'ACP' ) THEN
5469 if p_rec.rt_elig_prfl_flag = 'Y' then
5470 --
5471 chk_elig_ttlprtt_mtl_cd
5472 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5473 p_mlt_cd => p_rec.mlt_cd,
5474 p_effective_date => p_effective_date,
5475 p_business_group_id => p_rec.business_group_id);
5476 --
5477 else
5478 --
5479 chk_ttlprtt_mtl_cd
5480 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5481 p_mlt_cd => p_rec.mlt_cd,
5482 p_effective_date => p_effective_date,
5483 p_business_group_id => p_rec.business_group_id);
5484 --
5485 end if;
5486 --
5487 END IF;
5488 --
5489 chk_mlt_cd_dependencies
5490 (p_mlt_cd => p_rec.mlt_cd,
5491 p_val => p_rec.val,
5492 p_mn_elcn_val => p_rec.mn_elcn_val,
5493 p_mx_elcn_val => p_rec.mx_elcn_val,
5494 p_incrmnt_elcn_val => p_rec.incrmnt_elcn_val,
5495 --Bug : 4237447
5496 p_dflt_elcn_val => p_rec.dflt_elcn_val,
5497 --End Bug: 4237447
5498 p_rt_typ_cd => p_rec.rt_typ_cd,
5499 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
5500 p_val_calc_rl => p_rec.val_calc_rl,
5501 p_no_mn_elcn_val_dfnd_flag => p_rec.no_mn_elcn_val_dfnd_flag,
5502 p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
5503 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5504 p_effective_date => p_effective_date,
5505 p_object_version_number => p_rec.object_version_number
5506 );
5507 --
5508 --Bug : 3476138
5509 chk_vrbl_usg_cd_dependencies
5510 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
5511 p_validation_start_date => p_validation_start_date,
5512 p_validation_end_date => p_validation_end_date,
5513 p_vrbl_usg_cd => p_rec.vrbl_usg_cd,
5514 p_effective_date => p_effective_date,
5515 p_object_version_number => p_rec.object_version_number );
5516 --Bug : 3476138
5517 --
5518 -- Call the datetrack update integrity operation
5519 --
5520 dt_update_validate
5521 (p_oipl_id => p_rec.oipl_id,
5522 p_pl_typ_opt_typ_id => p_rec.pl_typ_opt_typ_id,
5523 p_pl_id => p_rec.pl_id,
5524 p_datetrack_mode => p_datetrack_mode,
5525 p_validation_start_date => p_validation_start_date,
5526 p_validation_end_date => p_validation_end_date);
5527 --
5528 hr_utility.set_location(' Leaving:'||l_proc, 10);
5529
5530 --
5531 End update_validate;
5532 --
5533 -- ----------------------------------------------------------------------------
5534 -- |---------------------------< delete_validate >----------------------------|
5535 -- ----------------------------------------------------------------------------
5536 Procedure delete_validate
5537 (p_rec in ben_vpf_shd.g_rec_type,
5538 p_effective_date in date,
5539 p_datetrack_mode in varchar2,
5540 p_validation_start_date in date,
5541 p_validation_end_date in date) is
5542 --
5543 l_proc varchar2(72) := g_package||'delete_validate';
5544 --
5545 CURSOR c_vpf_name
5546 IS
5547 SELECT vpf.NAME
5548 FROM ben_vrbl_rt_prfl_f vpf
5549 WHERE vpf.vrbl_rt_prfl_id = p_rec.vrbl_rt_prfl_id
5550 AND p_effective_date BETWEEN vpf.effective_start_date
5551 AND vpf.effective_end_date;
5552 --
5553 l_vpf_name ben_vrbl_rt_prfl_f.name%type;
5554 --
5555 Begin
5556 hr_utility.set_location('Entering:'||l_proc, 5);
5557 --
5558 OPEN c_vpf_name;
5559 --
5560 FETCH c_vpf_name INTO l_vpf_name;
5561 --
5562 CLOSE c_vpf_name;
5563 --
5564 -- Call all supporting business operations
5565 --
5566 dt_delete_validate
5567 (p_datetrack_mode => p_datetrack_mode,
5568 p_validation_start_date => p_validation_start_date,
5569 p_validation_end_date => p_validation_end_date,
5570 p_name => l_vpf_name,
5571 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id);
5572 --
5573 hr_utility.set_location(' Leaving:'||l_proc, 10);
5574 End delete_validate;
5575 --
5576 --
5577 -- ---------------------------------------------------------------------------
5578 -- |---------------------< return_legislation_code >-------------------------|
5579 -- ---------------------------------------------------------------------------
5580 --
5581 function return_legislation_code
5582 (p_vrbl_rt_prfl_id in number) return varchar2 is
5583 --
5584 -- Declare cursor
5585 --
5586 cursor csr_leg_code is
5587 select a.legislation_code
5588 from per_business_groups a,
5589 ben_vrbl_rt_prfl_f b
5590 where b.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
5591 and a.business_group_id = b.business_group_id;
5592 --
5593 -- Declare local variables
5594 --
5595 l_legislation_code varchar2(150);
5596 l_proc varchar2(72) := g_package||'return_legislation_code';
5597 --
5598 begin
5599 --
5600 hr_utility.set_location('Entering:'|| l_proc, 10);
5601 --
5602 -- Ensure that all the mandatory parameter are not null
5603 --
5604 hr_api.mandatory_arg_error(p_api_name => l_proc,
5605 p_argument => 'vrbl_rt_prfl_id',
5606 p_argument_value => p_vrbl_rt_prfl_id);
5607 --
5608 open csr_leg_code;
5609 --
5610 fetch csr_leg_code into l_legislation_code;
5611 --
5612 if csr_leg_code%notfound then
5613 --
5614 close csr_leg_code;
5615 --
5616 -- The primary key is invalid therefore we must error
5617 --
5618 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
5619 hr_utility.raise_error;
5620 --
5621 end if;
5622 --
5623 close csr_leg_code;
5624 --
5625 hr_utility.set_location(' Leaving:'|| l_proc, 20);
5626 --
5627 return l_legislation_code;
5628 --
5629 end return_legislation_code;
5630 --
5631 end ben_vpf_bus;