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