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