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