[Home] [Help]
PACKAGE BODY: APPS.BEN_EAC_BUS
Source
1 Package Body ben_eac_bus as
2 /* $Header: beeacrhi.pkb 115.8 2002/12/09 12:51:17 lakrish ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_eac_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_elig_age_cvg_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 -- elig_age_cvg_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_elig_age_cvg_id(p_elig_age_cvg_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_elig_age_cvg_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_eac_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_elig_age_cvg_id => p_elig_age_cvg_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_elig_age_cvg_id,hr_api.g_number)
55 <> ben_eac_shd.g_old_rec.elig_age_cvg_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_eac_shd.constraint_error('BEN_ELIG_AGE_CVG_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_elig_age_cvg_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_eac_shd.constraint_error('BEN_ELIG_AGE_CVG_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_elig_age_cvg_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_age_fctr_id >------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure checks that a referenced foreign key actually exists
85 -- in the referenced table.
86 --
87 -- Pre-Conditions
88 -- None.
89 --
90 -- In Parameters
91 -- p_elig_age_cvg_id PK
92 -- p_age_fctr_id ID of FK column
93 -- p_effective_date session date
94 -- p_object_version_number object version number
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Error raised.
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_age_fctr_id (p_elig_age_cvg_id in number,
106 p_age_fctr_id in number,
107 p_effective_date in date,
108 p_object_version_number in number) is
109 --
110 l_proc varchar2(72) := g_package||'chk_age_fctr_id';
111 l_api_updating boolean;
112 l_dummy varchar2(1);
113 --
114 cursor c1 is
115 select null
116 from ben_age_fctr a
117 where a.age_fctr_id = p_age_fctr_id;
118 --
119 Begin
120 --
121 hr_utility.set_location('Entering:'||l_proc,5);
122 --
123 l_api_updating := ben_eac_shd.api_updating
124 (p_elig_age_cvg_id => p_elig_age_cvg_id,
125 p_effective_date => p_effective_date,
126 p_object_version_number => p_object_version_number);
127 --
128 if (l_api_updating
129 and nvl(p_age_fctr_id,hr_api.g_number)
130 <> nvl(ben_eac_shd.g_old_rec.age_fctr_id,hr_api.g_number)
131 or not l_api_updating) and
132 p_age_fctr_id is not null then
133 --
134 -- check if age_fctr_id value exists in ben_age_fctr table
135 --
136 open c1;
137 --
138 fetch c1 into l_dummy;
139 if c1%notfound then
140 --
141 close c1;
142 --
143 -- raise error as FK does not relate to PK in ben_age_fctr
144 -- table.
145 --
146 ben_eac_shd.constraint_error('BEN_ELIG_AGE_CVG_FK2');
147 --
148 end if;
149 --
150 close c1;
151 --
152 end if;
153 --
154 hr_utility.set_location('Leaving:'||l_proc,10);
155 --
156 End chk_age_fctr_id;
157 --
158 -- ----------------------------------------------------------------------------
159 -- |------< chk_cvg_thru_rl >------|
160 -- ----------------------------------------------------------------------------
161 --
162 -- Description
163 -- This procedure is used to check that the Formula Rule is valid.
164 --
165 -- Pre Conditions
166 -- None.
167 --
168 -- In Parameters
169 -- elig_age_cvg_id PK of record being inserted or updated.
170 -- cvg_thru_rl Value of formula rule id.
171 -- effective_date effective date
172 -- object_version_number Object version number of record being
173 -- inserted or updated.
174 --
175 -- Post Success
176 -- Processing continues
177 --
178 -- Post Failure
179 -- Error handled by procedure
180 --
181 -- Access Status
182 -- Internal table handler use only.
183 --
184 Procedure chk_cvg_thru_rl(p_elig_age_cvg_id in number,
185 p_cvg_thru_rl in number,
186 p_effective_date in date,
187 p_object_version_number in number) is
188 --
189 l_proc varchar2(72) := g_package||'chk_cvg_thru_rl';
190 l_api_updating boolean;
191 l_dummy varchar2(1);
192 --
193 cursor c1 is
194 select null
195 from ff_formulas_f ff
196 where ff.formula_id = p_cvg_thru_rl
197 and p_effective_date
198 between ff.effective_start_date
199 and ff.effective_end_date;
200 --
201 Begin
202 --
203 hr_utility.set_location('Entering:'||l_proc, 5);
204 --
205 l_api_updating := ben_eac_shd.api_updating
206 (p_elig_age_cvg_id => p_elig_age_cvg_id,
207 p_effective_date => p_effective_date,
208 p_object_version_number => p_object_version_number);
209 --
210 if (l_api_updating
211 and nvl(p_cvg_thru_rl,hr_api.g_number)
212 <> ben_eac_shd.g_old_rec.cvg_thru_rl
213 or not l_api_updating)
214 and p_cvg_thru_rl is not null then
215 --
216 -- check if value of formula rule is valid.
217 --
218 open c1;
219 --
220 -- fetch value from cursor if it returns a record then the
221 -- formula is valid otherwise its invalid
222 --
223 fetch c1 into l_dummy;
224 if c1%notfound then
225 --
226 close c1;
227 --
228 -- raise error
229 --
230 fnd_message.set_name('PAY','FORMULA_DOES_NOT_EXIST');
231 fnd_message.raise_error;
232 --
233 end if;
234 --
235 close c1;
236 --
237 end if;
238 --
239 hr_utility.set_location('Leaving:'||l_proc,10);
240 --
241 end chk_cvg_thru_rl;
242 --
243 -- ----------------------------------------------------------------------------
244 -- |------< chk_cvg_thru_cd >------|
245 -- ----------------------------------------------------------------------------
246 --
247 -- Description
248 -- This procedure is used to check that the lookup value is valid.
249 --
250 -- Pre Conditions
251 -- None.
252 --
253 -- In Parameters
254 -- elig_age_cvg_id PK of record being inserted or updated.
255 -- cvg_thru_cd Value of lookup code.
256 -- effective_date effective date
257 -- object_version_number Object version number of record being
258 -- inserted or updated.
259 --
260 -- Post Success
261 -- Processing continues
262 --
263 -- Post Failure
264 -- Error handled by procedure
265 --
266 -- Access Status
267 -- Internal table handler use only.
268 --
269 Procedure chk_cvg_thru_cd(p_elig_age_cvg_id in number,
270 p_cvg_thru_cd in varchar2,
271 p_effective_date in date,
272 p_object_version_number in number) is
273 --
274 l_proc varchar2(72) := g_package||'chk_cvg_thru_cd';
275 l_api_updating boolean;
276 --
277 Begin
278 --
279 hr_utility.set_location('Entering:'||l_proc, 5);
280 --
281 l_api_updating := ben_eac_shd.api_updating
282 (p_elig_age_cvg_id => p_elig_age_cvg_id,
283 p_effective_date => p_effective_date,
284 p_object_version_number => p_object_version_number);
285 --
286 if (l_api_updating
287 and p_cvg_thru_cd
288 <> nvl(ben_eac_shd.g_old_rec.cvg_thru_cd,hr_api.g_varchar2)
289 or not l_api_updating)
290 and p_cvg_thru_cd is not null then
291 --
292 -- check if value of lookup falls within lookup type.
293 --
294 if hr_api.not_exists_in_hr_lookups
295 (p_lookup_type => 'BEN_DPNT_CVG_END',
296 p_lookup_code => p_cvg_thru_cd,
297 p_effective_date => p_effective_date) then
298 --
299 -- raise error as does not exist as lookup
300 --
301 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
302 fnd_message.raise_error;
303 --
304 end if;
305 --
306 end if;
307 --
308 hr_utility.set_location('Leaving:'||l_proc,10);
309 --
310 end chk_cvg_thru_cd;
311 --
312 -- ----------------------------------------------------------------------------
313 -- |------< chk_cvg_strt_rl >------|
314 -- ----------------------------------------------------------------------------
315 --
316 -- Description
317 -- This procedure is used to check that the Formula Rule is valid.
318 --
319 -- Pre Conditions
320 -- None.
321 --
322 -- In Parameters
323 -- elig_age_cvg_id PK of record being inserted or updated.
324 -- cvg_strt_rl Value of formula rule id.
325 -- effective_date effective date
326 -- object_version_number Object version number of record being
327 -- inserted or updated.
328 --
329 -- Post Success
330 -- Processing continues
331 --
332 -- Post Failure
333 -- Error handled by procedure
334 --
335 -- Access Status
336 -- Internal table handler use only.
337 --
338 Procedure chk_cvg_strt_rl(p_elig_age_cvg_id in number,
339 p_cvg_strt_rl in number,
340 p_effective_date in date,
341 p_object_version_number in number) is
342 --
343 l_proc varchar2(72) := g_package||'chk_cvg_strt_rl';
344 l_api_updating boolean;
345 l_dummy varchar2(1);
346 --
347 cursor c1 is
348 select null
349 from ff_formulas_f ff
350 where ff.formula_id = p_cvg_strt_rl
351 and p_effective_date
352 between ff.effective_start_date
353 and ff.effective_end_date;
354 --
355 Begin
356 --
357 hr_utility.set_location('Entering:'||l_proc, 5);
358 --
359 l_api_updating := ben_eac_shd.api_updating
360 (p_elig_age_cvg_id => p_elig_age_cvg_id,
361 p_effective_date => p_effective_date,
362 p_object_version_number => p_object_version_number);
363 --
364 if (l_api_updating
365 and nvl(p_cvg_strt_rl,hr_api.g_number)
366 <> ben_eac_shd.g_old_rec.cvg_strt_rl
367 or not l_api_updating)
368 and p_cvg_strt_rl is not null then
369 --
370 -- check if value of formula rule is valid.
371 --
372 open c1;
373 --
374 -- fetch value from cursor if it returns a record then the
375 -- formula is valid otherwise its invalid
376 --
377 fetch c1 into l_dummy;
378 if c1%notfound then
379 --
380 close c1;
381 --
382 -- raise error
383 --
384 fnd_message.set_name('PAY','FORMULA_DOES_NOT_EXIST');
385 fnd_message.raise_error;
386 --
387 end if;
388 --
389 close c1;
390 --
391 end if;
392 --
393 hr_utility.set_location('Leaving:'||l_proc,10);
394 --
395 end chk_cvg_strt_rl;
396 --
397 -- ----------------------------------------------------------------------------
398 -- |------< chk_cvg_strt_cd >------|
399 -- ----------------------------------------------------------------------------
400 --
401 -- Description
402 -- This procedure is used to check that the lookup value is valid.
403 --
404 -- Pre Conditions
405 -- None.
406 --
407 -- In Parameters
408 -- elig_age_cvg_id PK of record being inserted or updated.
409 -- cvg_strt_cd Value of lookup code.
410 -- effective_date effective date
411 -- object_version_number Object version number of record being
412 -- inserted or updated.
413 --
414 -- Post Success
415 -- Processing continues
416 --
417 -- Post Failure
418 -- Error handled by procedure
419 --
420 -- Access Status
421 -- Internal table handler use only.
422 --
423 Procedure chk_cvg_strt_cd(p_elig_age_cvg_id in number,
424 p_cvg_strt_cd in varchar2,
425 p_effective_date in date,
426 p_object_version_number in number) is
427 --
428 l_proc varchar2(72) := g_package||'chk_cvg_strt_cd';
429 l_api_updating boolean;
430 --
431 Begin
432 --
433 hr_utility.set_location('Entering:'||l_proc, 5);
434 --
435 l_api_updating := ben_eac_shd.api_updating
436 (p_elig_age_cvg_id => p_elig_age_cvg_id,
437 p_effective_date => p_effective_date,
438 p_object_version_number => p_object_version_number);
439 --
440 if (l_api_updating
441 and p_cvg_strt_cd
442 <> nvl(ben_eac_shd.g_old_rec.cvg_strt_cd,hr_api.g_varchar2)
443 or not l_api_updating)
444 and p_cvg_strt_cd is not null then
445 --
446 -- check if value of lookup falls within lookup type.
447 --
448 if hr_api.not_exists_in_hr_lookups
449 (p_lookup_type => 'BEN_DPNT_CVG_STRT',
450 p_lookup_code => p_cvg_strt_cd,
451 p_effective_date => p_effective_date) then
452 --
453 -- raise error as does not exist as lookup
454 --
455 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
456 fnd_message.raise_error;
457 --
458 end if;
459 --
460 end if;
461 --
462 hr_utility.set_location('Leaving:'||l_proc,10);
463 --
464 end chk_cvg_strt_cd;
465 --
466 -- ----------------------------------------------------------------------------
467 -- |------< chk_cvg_strt_dt_dependency >------|
468 -- ----------------------------------------------------------------------------
469 --
470 -- Description
471 -- This procedure is used to check that the code/rule dependency as the
472 -- following:
473 -- If Code = 'Rule' then rule must be selected.
474 -- If Code <> 'Rule' thne rule must not be selected.
475 --
476 -- Pre Conditions
477 -- None.
478 --
479 -- In Parameters
480 -- elig_mrtl_stat_cvg_id PK of record being inserted or updated.
481 -- cvg_strt_cd Value of look up value.
482 -- cvg_strt_rl value of look up Value
483 -- effective_date effective date
484 -- object_version_number Object version number of record being
485 -- inserted or updated.
486 --
487 -- Post Success
488 -- Processing continues
489 --
490 -- Post Failure
491 -- Error handled by procedure
492 --
493 -- Access Status
494 -- Internal table handler use only.
495 --
496 Procedure chk_cvg_strt_dt_dependency(p_elig_age_cvg_id in number,
497 p_cvg_strt_cd in varchar2,
498 p_cvg_strt_rl in number,
499 p_effective_date in date,
500 p_object_version_number in number) is
501 --
502 l_proc varchar2(72) := g_package||'chk_cvg_strt_dependency';
503 l_api_updating boolean;
504 --
505 Begin
506 --
507 hr_utility.set_location('Entering:'||l_proc, 5);
508 --
509 l_api_updating := ben_eac_shd.api_updating
510 (p_elig_age_cvg_id => p_elig_age_cvg_id,
511 p_effective_date => p_effective_date,
512 p_object_version_number => p_object_version_number);
513 --
514 if (l_api_updating
515 and (nvl(p_cvg_strt_cd, hr_api.g_varchar2) <>
516 nvl(ben_eac_shd.g_old_rec.cvg_strt_cd,hr_api.g_varchar2) or
517 nvl(p_cvg_strt_rl, hr_api.g_number) <>
518 nvl(ben_eac_shd.g_old_rec.cvg_strt_rl,hr_api.g_number))
519 or not l_api_updating) then
520 --
521 -- check dependency of Code and Rule.
522 --
523 if ( nvl(p_cvg_strt_cd, hr_api.g_varchar2) <> 'RL' and
524 p_cvg_strt_rl is not null) then
525 fnd_message.set_name('BEN', 'BEN_67890_field_CWOR');
526 fnd_message.raise_error;
527 end if;
528
529 if ( nvl(p_cvg_strt_cd, hr_api.g_varchar2) = 'RL' and p_cvg_strt_rl is null) then
530 fnd_message.set_name('BEN', 'BEN_12345_field_CWOR');
531 fnd_message.raise_error;
532 end if;
533 end if;
534
535 --
536 -- Leaving Procedure.
537 --
538 hr_utility.set_location('Leaving:'||l_proc,10);
539 --
540 end chk_cvg_strt_dt_dependency;
541 --
542 -- ----------------------------------------------------------------------------
543 -- |------< chk_cvg_thru_dependency >------|
544 -- ----------------------------------------------------------------------------
545 --
546 -- Description
547 -- This procedure is used to check that the code/rule dependency as the
548 -- following:
549 -- If Code = 'Rule' then rule must be selected.
550 -- If Code <> 'Rule' thne rule must not be selected.
551 --
552 -- Pre Conditions
553 -- None.
554 --
555 -- In Parameters
556 -- elig_mrtl_stat_cvg_idPK of record being inserted or updated.
557 -- cvg_thru_cd Value of look up value.
558 -- cvg_thru_rl value of look up Value
559 -- effective_date effective date
560 -- object_version_number Object version number of record being
561 -- inserted or updated.
562 --
563 -- Post Success
564 -- Processing continues
565 --
566 -- Post Failure
567 -- Error handled by procedure
568 --
569 -- Access Status
570 -- Internal table handler use only.
571 --
572 Procedure chk_cvg_thru_dt_dependency(p_elig_age_cvg_id in number,
573 p_cvg_thru_cd in varchar2,
574 p_cvg_thru_rl in number,
575 p_effective_date in date,
576 p_object_version_number in number) is
577 --
578 l_proc varchar2(72) := g_package||'chk_cvg_thru_dt_dependency';
579 l_api_updating boolean;
580 --
581 Begin
582 --
583 hr_utility.set_location('Entering:'||l_proc, 5);
584 --
585 l_api_updating := ben_eac_shd.api_updating
586 (p_elig_age_cvg_id => p_elig_age_cvg_id,
587 p_effective_date => p_effective_date,
588 p_object_version_number => p_object_version_number);
589 --
590 if (l_api_updating
591 and (nvl(p_cvg_thru_cd, hr_api.g_varchar2) <>
592 nvl(ben_eac_shd.g_old_rec.cvg_thru_cd,hr_api.g_varchar2) or
593 nvl(p_cvg_thru_rl, hr_api.g_number) <>
594 nvl(ben_eac_shd.g_old_rec.cvg_thru_rl,hr_api.g_number))
595 or not l_api_updating) then
596
597 --
598 -- check dependency of Code and Rule.
599 --
600 if ( nvl(p_cvg_thru_cd, hr_api.g_varchar2) <> 'RL' and
601 p_cvg_thru_rl is not null) then
602 fnd_message.set_name('BEN', 'BEN_67890_field_CWOR');
603 fnd_message.raise_error;
604 end if;
605
606 if ( nvl(p_cvg_thru_cd, hr_api.g_varchar2) = 'RL' and p_cvg_thru_rl is null) then
607 fnd_message.set_name('BEN', 'BEN_12345_field_CWOR');
608 fnd_message.raise_error;
609 end if;
610 end if;
611 --
612 -- Leaving Procedure.
613 --
614 hr_utility.set_location('Leaving:'|| l_proc, 10);
615 --
616 end chk_cvg_thru_dt_dependency;
617 --
618 -- ----------------------------------------------------------------------------
619 -- |------< chk_duplicate_rows >------|
620 -- ----------------------------------------------------------------------------
621 --
622 -- Description
623 -- This procedure is used to check if duplicate rows exist
624 --
625 -- Pre Conditions
626 -- None.
627 --
628 -- In Parameters
629
630 -- p_elig_age_cvg_id - primary key of the table
631 -- p_age_fctr_id - duplicate value to be checked
632 -- p_dpnt_cvg_eligy_prfl_id - master's id
633 -- p_effective_date
634 -- p_business_group_id
635
636 -- Post Success
637 -- Processing continues
638 --
639 -- Post Failure
640 -- Errors handled by the procedure
641 --
642 -- Access Status
643 -- Internal table handler use only.
644 --
645 Procedure chk_duplicate_rows(p_elig_age_cvg_id in number,
646 p_age_fctr_id in number,
647 p_dpnt_cvg_eligy_prfl_id in number,
648 p_business_group_id in varchar2,
649 p_effective_date in date) is
650 --
651 l_proc varchar2(72) := g_package||'chk_duplicate_rows';
652 l_api_updating boolean;
653
654 dummy varchar2(1);
655 cursor c1 is select null from ben_elig_age_cvg_f
656 where (elig_age_cvg_id <> p_elig_age_cvg_id or p_elig_age_cvg_id is null) and
657 dpnt_cvg_eligy_prfl_id = p_dpnt_cvg_eligy_prfl_id and
658 age_fctr_id = p_age_fctr_id and
659 business_group_id = p_business_group_id and
660 p_effective_date between effective_start_date and effective_end_date;
661
662
663 --
664 Begin
665 --
666 hr_utility.set_location('Entering:'||l_proc, 6);
667 --
668 open c1;
669 fetch c1 into dummy;
670 if c1%found then
671 close c1;
672 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
673 fnd_message.raise_error;
674 end if;
675 close c1;
676 --
677 hr_utility.set_location('Leaving:'||l_proc, 10);
678 --
679 End chk_duplicate_rows;
680
681
682
683 --
684 -- ----------------------------------------------------------------------------
685 -- |--------------------------< dt_update_validate >--------------------------|
686 -- ----------------------------------------------------------------------------
687 -- {Start Of Comments}
688 --
689 -- Description:
690 -- This procedure is used for referential integrity of datetracked
691 -- parent entities when a datetrack update operation is taking place
692 -- and where there is no cascading of update defined for this entity.
693 --
694 -- Prerequisites:
695 -- This procedure is called from the update_validate.
696 --
697 -- In Parameters:
698 --
699 -- Post Success:
700 -- Processing continues.
701 --
702 -- Post Failure:
703 --
704 -- Developer Implementation Notes:
705 -- This procedure should not need maintenance unless the HR Schema model
706 -- changes.
707 --
708 -- Access Status:
709 -- Internal Row Handler Use Only.
710 --
711 -- {End Of Comments}
712 -- ----------------------------------------------------------------------------
713 Procedure dt_update_validate
714 (p_dpnt_cvg_eligy_prfl_id in number default hr_api.g_number,
715 p_datetrack_mode in varchar2,
716 p_validation_start_date in date,
717 p_validation_end_date in date) Is
718 --
719 l_proc varchar2(72) := g_package||'dt_update_validate';
720 l_integrity_error Exception;
721 l_table_name all_tables.table_name%TYPE;
722 --
723 Begin
724 hr_utility.set_location('Entering:'||l_proc, 5);
725 --
726 -- Ensure that the p_datetrack_mode argument is not null
727 --
728 hr_api.mandatory_arg_error
729 (p_api_name => l_proc,
730 p_argument => 'datetrack_mode',
731 p_argument_value => p_datetrack_mode);
732 --
733 -- Only perform the validation if the datetrack update mode is valid
734 --
735 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
736 --
737 --
738 -- Ensure the arguments are not null
739 --
740 hr_api.mandatory_arg_error
741 (p_api_name => l_proc,
742 p_argument => 'validation_start_date',
743 p_argument_value => p_validation_start_date);
744 --
745 hr_api.mandatory_arg_error
746 (p_api_name => l_proc,
747 p_argument => 'validation_end_date',
748 p_argument_value => p_validation_end_date);
749 --
750 If ((nvl(p_dpnt_cvg_eligy_prfl_id, hr_api.g_number) <> hr_api.g_number) and
751 NOT (dt_api.check_min_max_dates
752 (p_base_table_name => 'ben_dpnt_cvg_eligy_prfl_f',
753 p_base_key_column => 'dpnt_cvg_eligy_prfl_id',
754 p_base_key_value => p_dpnt_cvg_eligy_prfl_id,
755 p_from_date => p_validation_start_date,
756 p_to_date => p_validation_end_date))) Then
757 l_table_name := 'ben_dpnt_cvg_eligy_prfl_f';
758 Raise l_integrity_error;
759 End If;
760 --
761 End If;
762 --
763 hr_utility.set_location(' Leaving:'||l_proc, 10);
764 Exception
765 When l_integrity_error Then
766 --
767 -- A referential integrity check was violated therefore
768 -- we must error
769 --
770 ben_utility.parent_integrity_error(p_table_name => l_table_name);
771
772 When Others Then
773 --
774 -- An unhandled or unexpected error has occurred which
775 -- we must report
776 --
777 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
778 fnd_message.set_token('PROCEDURE', l_proc);
779 fnd_message.set_token('STEP','15');
780 fnd_message.raise_error;
781 End dt_update_validate;
782 --
783 -- ----------------------------------------------------------------------------
784 -- |--------------------------< dt_delete_validate >--------------------------|
785 -- ----------------------------------------------------------------------------
786 -- {Start Of Comments}
787 --
788 -- Description:
789 -- This procedure is used for referential integrity of datetracked
790 -- child entities when either a datetrack DELETE or ZAP is in operation
791 -- and where there is no cascading of delete defined for this entity.
792 -- For the datetrack mode of DELETE or ZAP we must ensure that no
793 -- datetracked child rows exist between the validation start and end
794 -- dates.
795 --
796 -- Prerequisites:
797 -- This procedure is called from the delete_validate.
798 --
799 -- In Parameters:
800 --
801 -- Post Success:
802 -- Processing continues.
803 --
804 -- Post Failure:
805 -- If a row exists by determining the returning Boolean value from the
806 -- generic dt_api.rows_exist function then we must supply an error via
807 -- the use of the local exception handler l_rows_exist.
808 --
809 -- Developer Implementation Notes:
810 -- This procedure should not need maintenance unless the HR Schema model
811 -- changes.
812 --
813 -- Access Status:
814 -- Internal Row Handler Use Only.
815 --
816 -- {End Of Comments}
817 -- ----------------------------------------------------------------------------
818 Procedure dt_delete_validate
819 (p_elig_age_cvg_id in number,
820 p_datetrack_mode in varchar2,
821 p_validation_start_date in date,
822 p_validation_end_date in date) Is
823 --
824 l_proc varchar2(72) := g_package||'dt_delete_validate';
825 l_rows_exist Exception;
826 l_table_name all_tables.table_name%TYPE;
827 --
828 Begin
829 hr_utility.set_location('Entering:'||l_proc, 5);
830 --
831 -- Ensure that the p_datetrack_mode argument is not null
832 --
833 hr_api.mandatory_arg_error
834 (p_api_name => l_proc,
835 p_argument => 'datetrack_mode',
836 p_argument_value => p_datetrack_mode);
837 --
838 -- Only perform the validation if the datetrack mode is either
839 -- DELETE or ZAP
840 --
841 If (p_datetrack_mode = 'DELETE' or
842 p_datetrack_mode = 'ZAP') then
843 --
844 --
845 -- Ensure the arguments are not null
846 --
847 hr_api.mandatory_arg_error
848 (p_api_name => l_proc,
849 p_argument => 'validation_start_date',
850 p_argument_value => p_validation_start_date);
851 --
852 hr_api.mandatory_arg_error
853 (p_api_name => l_proc,
854 p_argument => 'validation_end_date',
855 p_argument_value => p_validation_end_date);
856 --
857 hr_api.mandatory_arg_error
858 (p_api_name => l_proc,
859 p_argument => 'elig_age_cvg_id',
860 p_argument_value => p_elig_age_cvg_id);
861 --
862 --
863 --
864 End If;
865 --
866 hr_utility.set_location(' Leaving:'||l_proc, 10);
867 Exception
868 When l_rows_exist Then
869 --
870 -- A referential integrity check was violated therefore
871 -- we must error
872 --
873 ben_utility.child_exists_error(p_table_name => l_table_name);
874
875 When Others Then
876 --
877 -- An unhandled or unexpected error has occurred which
878 -- we must report
879 --
880 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
881 fnd_message.set_token('PROCEDURE', l_proc);
882 fnd_message.set_token('STEP','15');
883 fnd_message.raise_error;
884 End dt_delete_validate;
885 --
886 -- ----------------------------------------------------------------------------
887 -- |---------------------------< insert_validate >----------------------------|
888 -- ----------------------------------------------------------------------------
889 Procedure insert_validate
890 (p_rec in ben_eac_shd.g_rec_type,
891 p_effective_date in date,
892 p_datetrack_mode in varchar2,
893 p_validation_start_date in date,
894 p_validation_end_date in date) is
895 --
896 l_proc varchar2(72) := g_package||'insert_validate';
897 --
898 Begin
899 hr_utility.set_location('Entering:'||l_proc, 5);
900 --
901 -- Call all supporting business operations
902 --
903 --
904 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
905 --
906
907 chk_duplicate_rows
908 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
909 p_age_fctr_id => p_rec.age_fctr_id,
910 p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
911 p_business_group_id => p_rec.business_group_id,
912 p_effective_date => p_effective_date);
913 --
914 chk_elig_age_cvg_id
915 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
916 p_effective_date => p_effective_date,
917 p_object_version_number => p_rec.object_version_number);
918 --
919 chk_age_fctr_id
920 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
921 p_age_fctr_id => p_rec.age_fctr_id,
922 p_effective_date => p_effective_date,
923 p_object_version_number => p_rec.object_version_number);
924 --
925 chk_cvg_thru_rl
926 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
927 p_cvg_thru_rl => p_rec.cvg_thru_rl,
928 p_effective_date => p_effective_date,
929 p_object_version_number => p_rec.object_version_number);
930 --
931 chk_cvg_thru_cd
932 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
933 p_cvg_thru_cd => p_rec.cvg_thru_cd,
934 p_effective_date => p_effective_date,
935 p_object_version_number => p_rec.object_version_number);
936 --
937 chk_cvg_strt_rl
938 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
939 p_cvg_strt_rl => p_rec.cvg_strt_rl,
940 p_effective_date => p_effective_date,
941 p_object_version_number => p_rec.object_version_number);
942 --
943 chk_cvg_strt_cd
944 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
945 p_cvg_strt_cd => p_rec.cvg_strt_cd,
946 p_effective_date => p_effective_date,
947 p_object_version_number => p_rec.object_version_number);
948 --
949 chk_cvg_strt_dt_dependency
950 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
951 p_cvg_strt_cd => p_rec.cvg_strt_cd,
952 p_cvg_strt_rl => p_rec.cvg_strt_rl,
953 p_effective_date => p_effective_date,
954 p_object_version_number => p_rec.object_version_number);
955 --
956 chk_cvg_thru_dt_dependency
957 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
958 p_cvg_thru_cd => p_rec.cvg_thru_cd,
959 p_cvg_thru_rl => p_rec.cvg_thru_rl,
960 p_effective_date => p_effective_date,
961 p_object_version_number => p_rec.object_version_number);
962
963 hr_utility.set_location(' Leaving:'||l_proc, 10);
964 End insert_validate;
965 --
966 -- ----------------------------------------------------------------------------
967 -- |---------------------------< update_validate >----------------------------|
968 -- ----------------------------------------------------------------------------
969 Procedure update_validate
970 (p_rec in ben_eac_shd.g_rec_type,
971 p_effective_date in date,
972 p_datetrack_mode in varchar2,
973 p_validation_start_date in date,
974 p_validation_end_date in date) is
975 --
976 l_proc varchar2(72) := g_package||'update_validate';
977 --
978 Begin
979 hr_utility.set_location('Entering:'||l_proc, 5);
980 --
981 -- Call all supporting business operations
982 --
983 --
984 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
985 --
986 chk_duplicate_rows
987 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
988 p_age_fctr_id => p_rec.age_fctr_id,
989 p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
990 p_business_group_id => p_rec.business_group_id,
991 p_effective_date => p_effective_date);
992 --
993 chk_elig_age_cvg_id
994 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
995 p_effective_date => p_effective_date,
996 p_object_version_number => p_rec.object_version_number);
997 --
998 chk_age_fctr_id
999 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
1000 p_age_fctr_id => p_rec.age_fctr_id,
1001 p_effective_date => p_effective_date,
1002 p_object_version_number => p_rec.object_version_number);
1003 --
1004 chk_cvg_thru_rl
1005 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
1006 p_cvg_thru_rl => p_rec.cvg_thru_rl,
1007 p_effective_date => p_effective_date,
1008 p_object_version_number => p_rec.object_version_number);
1009 --
1010 chk_cvg_thru_cd
1011 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
1012 p_cvg_thru_cd => p_rec.cvg_thru_cd,
1013 p_effective_date => p_effective_date,
1014 p_object_version_number => p_rec.object_version_number);
1015 --
1016 chk_cvg_strt_rl
1017 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
1018 p_cvg_strt_rl => p_rec.cvg_strt_rl,
1019 p_effective_date => p_effective_date,
1020 p_object_version_number => p_rec.object_version_number);
1021 --
1022 chk_cvg_strt_cd
1023 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
1024 p_cvg_strt_cd => p_rec.cvg_strt_cd,
1025 p_effective_date => p_effective_date,
1026 p_object_version_number => p_rec.object_version_number);
1027 --
1028 chk_cvg_strt_dt_dependency
1029 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
1030 p_cvg_strt_cd => p_rec.cvg_strt_cd,
1031 p_cvg_strt_rl => p_rec.cvg_strt_rl,
1032 p_effective_date => p_effective_date,
1033 p_object_version_number => p_rec.object_version_number);
1034 --
1035 chk_cvg_thru_dt_dependency
1036 (p_elig_age_cvg_id => p_rec.elig_age_cvg_id,
1037 p_cvg_thru_cd => p_rec.cvg_thru_cd,
1038 p_cvg_thru_rl => p_rec.cvg_thru_rl,
1039 p_effective_date => p_effective_date,
1040 p_object_version_number => p_rec.object_version_number);
1041 --
1042 -- Call the datetrack update integrity operation
1043 --
1044 dt_update_validate
1045 (p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
1046 p_datetrack_mode => p_datetrack_mode,
1047 p_validation_start_date => p_validation_start_date,
1048 p_validation_end_date => p_validation_end_date);
1049 --
1050 hr_utility.set_location(' Leaving:'||l_proc, 10);
1051 End update_validate;
1052 --
1053 -- ----------------------------------------------------------------------------
1054 -- |---------------------------< delete_validate >----------------------------|
1055 -- ----------------------------------------------------------------------------
1056 Procedure delete_validate
1057 (p_rec in ben_eac_shd.g_rec_type,
1058 p_effective_date in date,
1059 p_datetrack_mode in varchar2,
1060 p_validation_start_date in date,
1061 p_validation_end_date in date) is
1062 --
1063 l_proc varchar2(72) := g_package||'delete_validate';
1064 --
1065 Begin
1066 hr_utility.set_location('Entering:'||l_proc, 5);
1067 --
1068 -- Call all supporting business operations
1069 --
1070 dt_delete_validate
1071 (p_datetrack_mode => p_datetrack_mode,
1072 p_validation_start_date => p_validation_start_date,
1073 p_validation_end_date => p_validation_end_date,
1074 p_elig_age_cvg_id => p_rec.elig_age_cvg_id);
1075 --
1076 hr_utility.set_location(' Leaving:'||l_proc, 10);
1077 End delete_validate;
1078 --
1079 --
1080 -- ---------------------------------------------------------------------------
1081 -- |---------------------< return_legislation_code >-------------------------|
1082 -- ---------------------------------------------------------------------------
1083 --
1084 function return_legislation_code
1085 (p_elig_age_cvg_id in number) return varchar2 is
1086 --
1087 -- Declare cursor
1088 --
1089 cursor csr_leg_code is
1090 select a.legislation_code
1091 from per_business_groups a,
1092 ben_elig_age_cvg_f b
1093 where b.elig_age_cvg_id = p_elig_age_cvg_id
1094 and a.business_group_id = b.business_group_id;
1095 --
1096 -- Declare local variables
1097 --
1098 l_legislation_code varchar2(150);
1099 l_proc varchar2(72) := g_package||'return_legislation_code';
1100 --
1101 begin
1102 --
1103 hr_utility.set_location('Entering:'|| l_proc, 10);
1104 --
1105 -- Ensure that all the mandatory parameter are not null
1106 --
1107 hr_api.mandatory_arg_error(p_api_name => l_proc,
1108 p_argument => 'elig_age_cvg_id',
1109 p_argument_value => p_elig_age_cvg_id);
1110 --
1111 open csr_leg_code;
1112 --
1113 fetch csr_leg_code into l_legislation_code;
1114 --
1115 if csr_leg_code%notfound then
1116 --
1117 close csr_leg_code;
1118 --
1119 -- The primary key is invalid therefore we must error
1120 --
1121 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1122 fnd_message.raise_error;
1123 --
1124 end if;
1125 --
1126 close csr_leg_code;
1127 --
1128 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1129 --
1130 return l_legislation_code;
1131 --
1132 end return_legislation_code;
1133 --
1134 end ben_eac_bus;