[Home] [Help]
PACKAGE BODY: APPS.BEN_DCE_BUS
Source
1 Package Body ben_dce_bus as
2 /* $Header: bedcerhi.pkb 120.0.12010000.2 2010/04/07 06:40:25 pvelvano ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_dce_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_dpnt_cvg_eligy_prfl_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- dpnt_cvg_eligy_prfl_id PK of record being inserted or updated.
24 -- effective_date Effective Date of session
25 -- object_version_number Object version number of record being
26 -- inserted or updated.
27 --
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Errors handled by the procedure
33 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_dpnt_cvg_eligy_prfl_id(p_dpnt_cvg_eligy_prfl_id in number,
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_dpnt_cvg_eligy_prfl_id';
42 l_api_updating boolean;
43 --
44 Begin
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 --
48 l_api_updating := ben_dce_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_dpnt_cvg_eligy_prfl_id => p_dpnt_cvg_eligy_prfl_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_dpnt_cvg_eligy_prfl_id,hr_api.g_number)
55 <> ben_dce_shd.g_old_rec.dpnt_cvg_eligy_prfl_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_dce_shd.constraint_error('BEN_DPNT_CVG_ELG_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_dpnt_cvg_eligy_prfl_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_dce_shd.constraint_error('BEN_DPNT_CVG_ELG_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_dpnt_cvg_eligy_prfl_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_dpnt_cvg_elig_det_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 -- dpnt_cvg_eligy_prfl_id PK of record being inserted or updated.
91 -- dpnt_cvg_elig_det_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_dpnt_cvg_elig_det_rl(p_dpnt_cvg_eligy_prfl_id in number,
106 p_dpnt_cvg_elig_det_rl in number,
107 p_business_group_id in number,
108 p_effective_date in date,
109 p_object_version_number in number) is
110 --
111 l_proc varchar2(72) := g_package||'chk_dpnt_cvg_elig_det_rl';
112 l_api_updating boolean;
113 l_dummy varchar2(1);
114 --
115 cursor c1 is
116 select null
117 from ff_formulas_f ff
118 ,per_business_groups pbg
119 where ff.formula_id = p_dpnt_cvg_elig_det_rl
120 and ff.formula_type_id = -35
121 and pbg.business_group_id = p_business_group_id
122 and nvl(ff.business_group_id, p_business_group_id) =
123 p_business_group_id
124 and nvl(ff.legislation_code, pbg.legislation_code) =
125 pbg.legislation_code
126 and p_effective_date
127 between ff.effective_start_date
128 and ff.effective_end_date;
129 --
130 Begin
131 --
132 hr_utility.set_location('Entering:'||l_proc, 5);
133 --
134 l_api_updating := ben_dce_shd.api_updating
135 (p_dpnt_cvg_eligy_prfl_id => p_dpnt_cvg_eligy_prfl_id,
136 p_effective_date => p_effective_date,
137 p_object_version_number => p_object_version_number);
138 --
139 if (l_api_updating
140 and nvl(p_dpnt_cvg_elig_det_rl,hr_api.g_number)
141 <> ben_dce_shd.g_old_rec.dpnt_cvg_elig_det_rl
142 or not l_api_updating)
143 and p_dpnt_cvg_elig_det_rl is not null then
144 --
145 -- check if value of formula rule is valid.
146 --
147 open c1;
148 --
149 -- fetch value from cursor if it returns a record then the
150 -- formula is valid otherwise its invalid
151 --
152 fetch c1 into l_dummy;
153 if c1%notfound then
154 --
155 close c1;
156 --
157 -- raise error
158 --
159 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
160 fnd_message.set_token('ID',p_dpnt_cvg_elig_det_rl);
161 fnd_message.set_token('TYPE_ID',-35);
162 fnd_message.raise_error;
163 --
164 end if;
165 --
166 close c1;
167 --
168 end if;
169 --
170 hr_utility.set_location('Leaving:'||l_proc,10);
171 --
172 end chk_dpnt_cvg_elig_det_rl;
173 --
174 -- ----------------------------------------------------------------------------
175 -- |------< chk_dpnt_cvg_eligy_prfl_stat >------|
176 -- ----------------------------------------------------------------------------
177 --
178 -- Description
179 -- This procedure is used to check that the lookup value is valid.
180 --
181 -- Pre Conditions
182 -- None.
183 --
184 -- In Parameters
185 -- dpnt_cvg_eligy_prfl_id PK of record being inserted or updated.
186 -- dpnt_cvg_eligy_prfl_stat_cd Value of lookup code.
187 -- effective_date effective date
188 -- object_version_number Object version number of record being
189 -- inserted or updated.
190 --
191 -- Post Success
192 -- Processing continues
193 --
194 -- Post Failure
195 -- Error handled by procedure
196 --
197 -- Access Status
198 -- Internal table handler use only.
199 --
200 Procedure chk_dpnt_cvg_eligy_prfl_stat(p_dpnt_cvg_eligy_prfl_id in number,
201 p_dpnt_cvg_eligy_prfl_stat_cd in varchar2,
202 p_effective_date in date,
203 p_object_version_number in number) is
204 --
205 l_proc varchar2(72) := g_package||'chk_dpnt_cvg_eligy_prfl_stat_cd';
206 l_api_updating boolean;
207 --
208 Begin
209 --
210 hr_utility.set_location('Entering:'||l_proc, 5);
211 --
212 l_api_updating := ben_dce_shd.api_updating
213 (p_dpnt_cvg_eligy_prfl_id => p_dpnt_cvg_eligy_prfl_id,
214 p_effective_date => p_effective_date,
215 p_object_version_number => p_object_version_number);
216 --
217 if (l_api_updating
218 and p_dpnt_cvg_eligy_prfl_stat_cd
219 <> nvl(ben_dce_shd.g_old_rec.dpnt_cvg_eligy_prfl_stat_cd,hr_api.g_varchar2)
220 or not l_api_updating)
221 and p_dpnt_cvg_eligy_prfl_stat_cd is not null then
222 --
223 -- check if value of lookup falls within lookup type.
224 --
225 if hr_api.not_exists_in_hr_lookups
226 (p_lookup_type => 'BEN_STAT',
227 p_lookup_code => p_dpnt_cvg_eligy_prfl_stat_cd,
228 p_effective_date => p_effective_date) then
229 --
230 -- raise error as does not exist as lookup
231 --
232 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
233 fnd_message.raise_error;
234 --
235 end if;
236 --
237 end if;
238 --
239 hr_utility.set_location('Leaving:'||l_proc,10);
240 --
241 end chk_dpnt_cvg_eligy_prfl_stat;
242
243 --
244 -- ----------------------------------------------------------------------------
245 -- |------< chk_name >------|
246 -- ----------------------------------------------------------------------------
247 --
248 -- Description
249 -- This procedure is used to make sure the name is unique..
250 --
251 -- Pre Conditions
252 -- None.
253 --
254 -- In Parameters
255 -- dpnt_cvg_eligy_prfl_id PK of record being inserted or updated.
256 -- name Value of name.
257 -- effective_date effective date
258 -- validate_start_date Validation start date.
259 -- validate_end_date Validation End Date.
260 -- business_group_id Group ID
261 -- object_version_number Object version number of record being
262 -- inserted or updated.
263 --
264 -- Post Success
265 -- Processing continues
266 --
267 -- Post Failure
268 -- Error handled by procedure
269 --
270 -- Access Status
271 -- Internal table handler use only.
272 --
273 Procedure chk_name( p_dpnt_cvg_eligy_prfl_id in number,
274 p_name in varchar2,
275 p_effective_date in date,
276 p_validate_start_date in date,
277 p_validate_end_date in date,
278 p_business_group_id in number,
279 p_object_version_number in number) is
280 --
281 l_proc varchar2(72) := g_package||'chk_name';
282 l_api_updating boolean;
283 l_exists varchar2(1);
284 --
285 cursor csr_name is
286 select null
287 from BEN_DPNT_CVG_ELIGY_PRFL_F
288 where name = p_name
289 and dpnt_cvg_eligy_prfl_id <> nvl(p_dpnt_cvg_eligy_prfl_id, hr_api.g_number)
290 and business_group_id + 0 = p_business_group_id
291 and p_validate_start_date <= effective_end_date
292 and p_validate_end_date >= effective_start_date;
293 --
294 Begin
295 --
296 hr_utility.set_location('Entering:'||l_proc, 5);
297 --
298 l_api_updating := ben_dce_shd.api_updating
299 (p_dpnt_cvg_eligy_prfl_id => p_dpnt_cvg_eligy_prfl_id,
300 p_effective_date => p_effective_date,
301 p_object_version_number => p_object_version_number);
302 --
303 if (l_api_updating
304 and p_name <> ben_dce_shd.g_old_rec.name)
305 or not l_api_updating then
306 hr_utility.set_location('Entering:' || l_proc,10);
307 --
308 -- Check if this name already exists.
309 --
310 open csr_name;
311 fetch csr_name into l_exists;
312 if csr_name%found then
313 close csr_name;
314 --
315 -- raise error as UK1 is violated.
316 --
317 ben_dce_shd.constraint_error('BEN_DPNT_CVG_ELG_UK1');
318 end if;
319 close csr_name;
320 --
321 end if;
322 --
323 hr_utility.set_location('Leaving:'||l_proc,20);
324 --
325 end chk_name;
326
327 --
328 -- ----------------------------------------------------------------------------
329 -- ----------------------------------------------------------------------------
330 -- |-------------------------------< chk_lookups >----------------------------|
331 -- ----------------------------------------------------------------------------
332 --
333 -- Description
334 -- This procedure is used to check that the lookup value is valid.
335 --
336 -- Pre Conditions
337 -- None.
338 --
339 -- In Parameters
340 -- p_dpnt_cvg_eligy_prfl_id
341 -- p_dpnt_rlshp_flag
342 -- p_dpnt_age_flag
343 -- p_dpnt_stud_flag
344 -- p_dpnt_dsbld_flag
345 -- p_dpnt_mrtl_flag
346 -- p_dpnt_mltry_flag
347 -- p_dpnt_pstl_flag
348 -- p_dpnt_cvrd_in_anthr_pl_flag
349 -- p_dpnt_dsgnt_crntly_enrld_flag
350 -- p_effective_date
351 -- Post Success
352 -- Processing continues
353 --
354 -- Post Failure
355 -- Error handled by procedure
356 --
357 -- Access Status
358 -- Internal table handler use only.
359 --
360 Procedure chk_lookups(p_dpnt_cvg_eligy_prfl_id in number,
361 p_dpnt_rlshp_flag in varchar2,
362 p_dpnt_age_flag in varchar2,
363 p_dpnt_stud_flag in varchar2,
364 p_dpnt_dsbld_flag in varchar2,
365 p_dpnt_mrtl_flag in varchar2,
366 p_dpnt_mltry_flag in varchar2,
367 p_dpnt_pstl_flag in varchar2,
368 p_dpnt_cvrd_in_anthr_pl_flag in varchar2,
369 p_dpnt_dsgnt_crntly_enrld_flag in varchar2,
370 p_effective_date in date) is
371 --
372 l_proc varchar2(72) := g_package||'chk_lookups';
373 l_api_updating boolean;
374 --
375 Begin
376 --
377 hr_utility.set_location('Entering:'||l_proc, 5);
378 --
379 --
380 if hr_api.not_exists_in_hr_lookups
381 (p_lookup_type => 'YES_NO',
382 p_lookup_code =>p_dpnt_rlshp_flag,
383 p_effective_date => p_effective_date) then
384 --
385 -- raise error as does not exist as lookup
386 --
387 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
388 fnd_message.set_token('TYPE','YES_NO');
389 fnd_message.set_token('FIELD','dpnt_rlshp_flag ');
390 fnd_message.raise_error;
391 --
392 end if;
393 --
394
395 if hr_api.not_exists_in_hr_lookups
396 (p_lookup_type => 'YES_NO',
397 p_lookup_code =>p_dpnt_age_flag,
398 p_effective_date => p_effective_date) then
399 --
400 -- raise error as does not exist as lookup
401 --
402 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
403 fnd_message.set_token('TYPE','YES_NO');
404 fnd_message.set_token('FIELD','dpnt_age_flag ');
405 fnd_message.raise_error;
406 --
407 end if;
408 if hr_api.not_exists_in_hr_lookups
409 (p_lookup_type => 'YES_NO',
410 p_lookup_code =>p_dpnt_stud_flag,
411 p_effective_date => p_effective_date) then
412 --
413 -- raise error as does not exist as lookup
414 --
415 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
416 fnd_message.set_token('TYPE','YES_NO');
417 fnd_message.set_token('FIELD','dpnt_stud_flag ');
418 fnd_message.raise_error;
419 --
420 end if;
421 if hr_api.not_exists_in_hr_lookups
422 (p_lookup_type => 'YES_NO',
423 p_lookup_code =>p_dpnt_dsbld_flag,
424 p_effective_date => p_effective_date) then
425 --
426 -- raise error as does not exist as lookup
427 --
428 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
429 fnd_message.set_token('TYPE','YES_NO');
430 fnd_message.set_token('FIELD','dpnt_dsbld_flag ');
431 fnd_message.raise_error;
432 --
433 end if;
434 if hr_api.not_exists_in_hr_lookups
435 (p_lookup_type => 'YES_NO',
436 p_lookup_code =>p_dpnt_mrtl_flag,
437 p_effective_date => p_effective_date) then
438 --
439 -- raise error as does not exist as lookup
440 --
441 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
442 fnd_message.set_token('TYPE','YES_NO');
443 fnd_message.set_token('FIELD','dpnt_mrtl_flag ');
444 fnd_message.raise_error;
445 --
446 end if;
447 if hr_api.not_exists_in_hr_lookups
448 (p_lookup_type => 'YES_NO',
449 p_lookup_code =>p_dpnt_mltry_flag,
450 p_effective_date => p_effective_date) then
451 --
452 -- raise error as does not exist as lookup
453 --
454 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
455 fnd_message.set_token('TYPE','YES_NO');
456 fnd_message.set_token('FIELD','dpnt_mltry_flag ');
457 fnd_message.raise_error;
458 --
459 end if;
460 if hr_api.not_exists_in_hr_lookups
461 (p_lookup_type => 'YES_NO',
462 p_lookup_code =>p_dpnt_pstl_flag,
463 p_effective_date => p_effective_date) then
464 --
465 -- raise error as does not exist as lookup
466 --
467 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
468 fnd_message.set_token('TYPE','YES_NO');
469 fnd_message.set_token('FIELD','dpnt_pstl_flag ');
470 fnd_message.raise_error;
471 --
472 end if;
473 if hr_api.not_exists_in_hr_lookups
474 (p_lookup_type => 'YES_NO',
475 p_lookup_code =>p_dpnt_cvrd_in_anthr_pl_flag,
476 p_effective_date => p_effective_date) then
477 --
478 -- raise error as does not exist as lookup
479 --
480 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
481 fnd_message.set_token('TYPE','YES_NO');
482 fnd_message.set_token('FIELD','dpnt_cvrd_in_anthr_pl_flag ');
483 fnd_message.raise_error;
484 --
485 end if;
486 if hr_api.not_exists_in_hr_lookups
487 (p_lookup_type => 'YES_NO',
488 p_lookup_code =>p_dpnt_dsgnt_crntly_enrld_flag,
489 p_effective_date => p_effective_date) then
490 --
491 -- raise error as does not exist as lookup
492 --
493 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
494 fnd_message.set_token('TYPE','YES_NO');
495 fnd_message.set_token('FIELD','dpnt_dsgnt_crntly_enrld_flag ');
496 fnd_message.raise_error;
497 --
498 end if;
499
500 hr_utility.set_location(' Leaving:'||l_proc, 5);
501 End Chk_lookups;
502 -- |--------------------------< dt_update_validate >--------------------------|
503 -- ----------------------------------------------------------------------------
504 -- {Start Of Comments}
505 --
506 -- Description:
507 -- This procedure is used for referential integrity of datetracked
508 -- parent entities when a datetrack update operation is taking place
509 -- and where there is no cascading of update defined for this entity.
510 --
511 -- Prerequisites:
512 -- This procedure is called from the update_validate.
513 --
514 -- In Parameters:
515 --
516 -- Post Success:
517 -- Processing continues.
518 --
519 -- Post Failure:
520 --
521 -- Developer Implementation Notes:
522 -- This procedure should not need maintenance unless the HR Schema model
523 -- changes.
524 --
525 -- Access Status:
526 -- Internal Row Handler Use Only.
527 --
528 -- {End Of Comments}
529 -- ----------------------------------------------------------------------------
530 Procedure dt_update_validate
531 (p_regn_id in number default hr_api.g_number,
532 p_datetrack_mode in varchar2,
533 p_validation_start_date in date,
534 p_validation_end_date in date) Is
535 --
536 l_proc varchar2(72) := g_package||'dt_update_validate';
537 l_integrity_error Exception;
538 l_table_name all_tables.table_name%TYPE;
539 --
540 Begin
541 hr_utility.set_location('Entering:'||l_proc, 5);
542 --
543 -- Ensure that the p_datetrack_mode argument is not null
544 --
545 hr_api.mandatory_arg_error
546 (p_api_name => l_proc,
547 p_argument => 'datetrack_mode',
548 p_argument_value => p_datetrack_mode);
549 --
550 -- Only perform the validation if the datetrack update mode is valid
551 --
552 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
553 --
554 --
555 -- Ensure the arguments are not null
556 --
557 hr_api.mandatory_arg_error
558 (p_api_name => l_proc,
559 p_argument => 'validation_start_date',
560 p_argument_value => p_validation_start_date);
561 --
562 hr_api.mandatory_arg_error
563 (p_api_name => l_proc,
564 p_argument => 'validation_end_date',
565 p_argument_value => p_validation_end_date);
566 --
567 If ((nvl(p_regn_id, hr_api.g_number) <> hr_api.g_number) and
568 NOT (dt_api.check_min_max_dates
569 (p_base_table_name => 'ben_regn_f',
570 p_base_key_column => 'regn_id',
571 p_base_key_value => p_regn_id,
572 p_from_date => p_validation_start_date,
573 p_to_date => p_validation_end_date))) Then
574 l_table_name := 'ben_regn_f';
575 Raise l_integrity_error;
576 End If;
577 --
578 End If;
579 --
580 hr_utility.set_location(' Leaving:'||l_proc, 10);
581 Exception
582 When l_integrity_error Then
583 --
584 -- A referential integrity check was violated therefore
585 -- we must error
586 --
587 ben_utility.parent_integrity_error(p_table_name => l_table_name);
588
589 When Others Then
590 --
591 -- An unhandled or unexpected error has occurred which
592 -- we must report
593 --
594 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
595 fnd_message.set_token('PROCEDURE', l_proc);
596 fnd_message.set_token('STEP','15');
597 fnd_message.raise_error;
598 End dt_update_validate;
599 --
600 -- ----------------------------------------------------------------------------
601 -- |--------------------------< dt_delete_validate >--------------------------|
602 -- ----------------------------------------------------------------------------
603 -- {Start Of Comments}
604 --
605 -- Description:
606 -- This procedure is used for referential integrity of datetracked
607 -- child entities when either a datetrack DELETE or ZAP is in operation
608 -- and where there is no cascading of delete defined for this entity.
609 -- For the datetrack mode of DELETE or ZAP we must ensure that no
610 -- datetracked child rows exist between the validation start and end
611 -- dates.
612 --
613 -- Prerequisites:
614 -- This procedure is called from the delete_validate.
615 --
616 -- In Parameters:
617 --
618 -- Post Success:
619 -- Processing continues.
620 --
621 -- Post Failure:
622 -- If a row exists by determining the returning Boolean value from the
623 -- generic dt_api.rows_exist function then we must supply an error via
624 -- the use of the local exception handler l_rows_exist.
625 --
626 -- Developer Implementation Notes:
627 -- This procedure should not need maintenance unless the HR Schema model
628 -- changes.
629 --
630 -- Access Status:
631 -- Internal Row Handler Use Only.
632 --
633 -- {End Of Comments}
634 -- ----------------------------------------------------------------------------
635 Procedure dt_delete_validate
636 (p_dpnt_cvg_eligy_prfl_id in number,
637 p_datetrack_mode in varchar2,
638 p_validation_start_date in date,
639 p_validation_end_date in date) Is
640 --
641 l_proc varchar2(72) := g_package||'dt_delete_validate';
642 l_rows_exist Exception;
643 l_table_name all_tables.table_name%TYPE;
644 --
645 Begin
646 hr_utility.set_location('Entering:'||l_proc, 5);
647 --
648 -- Ensure that the p_datetrack_mode argument is not null
649 --
650 hr_api.mandatory_arg_error
651 (p_api_name => l_proc,
652 p_argument => 'datetrack_mode',
653 p_argument_value => p_datetrack_mode);
654 --
655 -- Only perform the validation if the datetrack mode is either
656 -- DELETE or ZAP
657 --
658 If (p_datetrack_mode = 'DELETE' or
659 p_datetrack_mode = 'ZAP') then
660 --
661 --
662 -- Ensure the arguments are not null
663 --
664 hr_api.mandatory_arg_error
665 (p_api_name => l_proc,
666 p_argument => 'validation_start_date',
667 p_argument_value => p_validation_start_date);
668 --
669 hr_api.mandatory_arg_error
670 (p_api_name => l_proc,
671 p_argument => 'validation_end_date',
672 p_argument_value => p_validation_end_date);
673 --
674 hr_api.mandatory_arg_error
675 (p_api_name => l_proc,
676 p_argument => 'dpnt_cvg_eligy_prfl_id',
677 p_argument_value => p_dpnt_cvg_eligy_prfl_id);
678 --
679 If (dt_api.rows_exist
680 (p_base_table_name => 'ben_dpnt_cvg_rqd_rlshp_f',
681 p_base_key_column => 'dpnt_cvg_eligy_prfl_id',
682 p_base_key_value => p_dpnt_cvg_eligy_prfl_id,
683 p_from_date => p_validation_start_date,
684 p_to_date => p_validation_end_date)) Then
685 l_table_name := 'ben_dpnt_cvg_rqd_rlshp_f';
686 Raise l_rows_exist;
687 End If;
688 If (dt_api.rows_exist
689 (p_base_table_name => 'ben_elig_age_cvg_f',
690 p_base_key_column => 'dpnt_cvg_eligy_prfl_id',
691 p_base_key_value => p_dpnt_cvg_eligy_prfl_id,
692 p_from_date => p_validation_start_date,
693 p_to_date => p_validation_end_date)) Then
694 l_table_name := 'ben_elig_age_cvg_f';
695 Raise l_rows_exist;
696 End If;
697 If (dt_api.rows_exist
698 (p_base_table_name => 'ben_elig_mrtl_stat_cvg_f',
699 p_base_key_column => 'dpnt_cvg_eligy_prfl_id',
700 p_base_key_value => p_dpnt_cvg_eligy_prfl_id,
701 p_from_date => p_validation_start_date,
702 p_to_date => p_validation_end_date)) Then
703 l_table_name := 'ben_elig_mrtl_stat_cvg_f';
704 Raise l_rows_exist;
705 End If;
706 If (dt_api.rows_exist
707 (p_base_table_name => 'ben_elig_mltry_stat_cvg_f',
708 p_base_key_column => 'dpnt_cvg_eligy_prfl_id',
709 p_base_key_value => p_dpnt_cvg_eligy_prfl_id,
710 p_from_date => p_validation_start_date,
711 p_to_date => p_validation_end_date)) Then
712 l_table_name := 'ben_elig_mltry_stat_cvg_f';
713 Raise l_rows_exist;
714 End If;
715 If (dt_api.rows_exist
716 (p_base_table_name => 'ben_elig_dsbld_stat_cvg_f',
717 p_base_key_column => 'dpnt_cvg_eligy_prfl_id',
718 p_base_key_value => p_dpnt_cvg_eligy_prfl_id,
719 p_from_date => p_validation_start_date,
720 p_to_date => p_validation_end_date)) Then
721 l_table_name := 'ben_elig_dsbld_stat_cvg_f';
722 Raise l_rows_exist;
723 End If;
724 If (dt_api.rows_exist
725 (p_base_table_name => 'ben_elig_stdnt_stat_cvg_f',
726 p_base_key_column => 'dpnt_cvg_eligy_prfl_id',
727 p_base_key_value => p_dpnt_cvg_eligy_prfl_id,
728 p_from_date => p_validation_start_date,
729 p_to_date => p_validation_end_date)) Then
730 l_table_name := 'ben_elig_stdnt_stat_cvg_f';
731 Raise l_rows_exist;
732 End If;
733 --
734 If (dt_api.rows_exist
735 (p_base_table_name => 'ben_dpnt_cvrd_anthr_pl_cvg_f',
736 p_base_key_column => 'dpnt_cvg_eligy_prfl_id',
737 p_base_key_value => p_dpnt_cvg_eligy_prfl_id,
738 p_from_date => p_validation_start_date,
739 p_to_date => p_validation_end_date)) Then
740 l_table_name := 'ben_dpnt_cvrd_anthr_pl_cvg_f';
741 Raise l_rows_exist;
742 End If;
743 --
744 If (dt_api.rows_exist
745 (p_base_table_name => 'ben_dsgntr_enrld_cvg_f',
746 p_base_key_column => 'dpnt_cvg_eligy_prfl_id',
747 p_base_key_value => p_dpnt_cvg_eligy_prfl_id,
748 p_from_date => p_validation_start_date,
749 p_to_date => p_validation_end_date)) Then
750 l_table_name := 'ben_dsgntr_enrld_cvg_f';
751 Raise l_rows_exist;
752 End If;
753 --
754 /*
755 If (dt_api.rows_exist
756 (p_base_table_name => 'ben_apld_dpnt_cvg_elig_prfl_f',
757 p_base_key_column => 'dpnt_cvg_eligy_prfl_id',
758 p_base_key_value => p_dpnt_cvg_eligy_prfl_id,
759 p_from_date => p_validation_start_date,
760 p_to_date => p_validation_end_date)) Then
761 l_table_name := 'ben_apld_dpnt_cvg_elig_prfl_f';
762 Raise l_rows_exist;
763 End If;
764 */
765 --
766 End If;
767 --
768 hr_utility.set_location(' Leaving:'||l_proc, 10);
769 Exception
770 When l_rows_exist Then
771 --
772 -- A referential integrity check was violated therefore
773 -- we must error
774 --
775 ben_utility.child_exists_error(p_table_name => l_table_name);
776
777 When Others Then
778 --
779 -- An unhandled or unexpected error has occurred which
780 -- we must report
781 --
782 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
783 fnd_message.set_token('PROCEDURE', l_proc);
784 fnd_message.set_token('STEP','15');
785 fnd_message.raise_error;
786 End dt_delete_validate;
787 --
788 -- ----------------------------------------------------------------------------
789 -- |---------------------------< insert_validate >----------------------------|
790 -- ----------------------------------------------------------------------------
791 Procedure insert_validate
792 (p_rec in ben_dce_shd.g_rec_type,
793 p_effective_date in date,
794 p_datetrack_mode in varchar2,
795 p_validation_start_date in date,
796 p_validation_end_date in date) is
797 --
798 l_proc varchar2(72) := g_package||'insert_validate';
799 --
800 Begin
801 hr_utility.set_location('Entering:'||l_proc, 5);
802 --
803 -- Call all supporting business operations
804 --
805 --
806 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
807 --
808 chk_dpnt_cvg_eligy_prfl_id
809 (p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
810 p_effective_date => p_effective_date,
811 p_object_version_number => p_rec.object_version_number);
812 --
813 chk_dpnt_cvg_elig_det_rl
814 (p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
815 p_dpnt_cvg_elig_det_rl => p_rec.dpnt_cvg_elig_det_rl,
816 p_business_group_id => p_rec.business_group_id,
817 p_effective_date => p_effective_date,
818 p_object_version_number => p_rec.object_version_number);
819 --
820 chk_dpnt_cvg_eligy_prfl_stat
821 (p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
822 p_dpnt_cvg_eligy_prfl_stat_cd => p_rec.dpnt_cvg_eligy_prfl_stat_cd,
823 p_effective_date => p_effective_date,
824 p_object_version_number => p_rec.object_version_number);
825 --
826 chk_name
827 (p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
828 p_name => p_rec.name,
829 p_effective_date => p_effective_date,
830 p_validate_start_date => p_validation_start_date,
831 p_validate_end_date => p_validation_end_date,
832 p_business_group_id => p_rec.business_group_id,
833 p_object_version_number => p_rec.object_version_number);
834 --
835 chk_lookups
836 (p_dpnt_cvg_eligy_prfl_id =>p_rec.dpnt_cvg_eligy_prfl_id,
837 p_dpnt_rlshp_flag =>p_rec.dpnt_rlshp_flag,
838 p_dpnt_age_flag =>p_rec.dpnt_age_flag,
839 p_dpnt_stud_flag =>p_rec.dpnt_stud_flag,
840 p_dpnt_dsbld_flag =>p_rec.dpnt_dsbld_flag,
841 p_dpnt_mrtl_flag =>p_rec.dpnt_mrtl_flag,
842 p_dpnt_mltry_flag =>p_rec.dpnt_mltry_flag,
843 p_dpnt_pstl_flag =>p_rec.dpnt_pstl_flag,
844 p_dpnt_cvrd_in_anthr_pl_flag =>p_rec.dpnt_cvrd_in_anthr_pl_flag,
845 p_dpnt_dsgnt_crntly_enrld_flag =>p_rec.dpnt_dsgnt_crntly_enrld_flag,
846 p_effective_date =>p_effective_date);
847 --
848 hr_utility.set_location(' Leaving:'||l_proc, 10);
849 End insert_validate;
850 --
851 -- ----------------------------------------------------------------------------
852 -- |---------------------------< update_validate >----------------------------|
853 -- ----------------------------------------------------------------------------
854 Procedure update_validate
855 (p_rec in ben_dce_shd.g_rec_type,
856 p_effective_date in date,
857 p_datetrack_mode in varchar2,
858 p_validation_start_date in date,
859 p_validation_end_date in date) is
860 --
861 l_proc varchar2(72) := g_package||'update_validate';
862 --
863 Begin
864 hr_utility.set_location('Entering:'||l_proc, 5);
865 --
866 -- Call all supporting business operations
867 --
868 --
869 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
870 --
871 chk_dpnt_cvg_eligy_prfl_id
872 (p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
873 p_effective_date => p_effective_date,
874 p_object_version_number => p_rec.object_version_number);
875 --
876 chk_dpnt_cvg_elig_det_rl
877 (p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
878 p_dpnt_cvg_elig_det_rl => p_rec.dpnt_cvg_elig_det_rl,
879 p_business_group_id => p_rec.business_group_id,
880 p_effective_date => p_effective_date,
881 p_object_version_number => p_rec.object_version_number);
882 --
883 chk_dpnt_cvg_eligy_prfl_stat
884 (p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
885 p_dpnt_cvg_eligy_prfl_stat_cd => p_rec.dpnt_cvg_eligy_prfl_stat_cd,
886 p_effective_date => p_effective_date,
887 p_object_version_number => p_rec.object_version_number);
888 --
889 chk_name
890 (p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id,
891 p_name => p_rec.name,
892 p_effective_date => p_effective_date,
893 p_validate_start_date => p_validation_start_date,
894 p_validate_end_date => p_validation_end_date,
895 p_business_group_id => p_rec.business_group_id,
896 p_object_version_number => p_rec.object_version_number);
897 --
898 chk_lookups
899 (p_dpnt_cvg_eligy_prfl_id =>p_rec.dpnt_cvg_eligy_prfl_id,
900 p_dpnt_rlshp_flag =>p_rec.dpnt_rlshp_flag,
901 p_dpnt_age_flag =>p_rec.dpnt_age_flag,
902 p_dpnt_stud_flag =>p_rec.dpnt_stud_flag,
903 p_dpnt_dsbld_flag =>p_rec.dpnt_dsbld_flag,
904 p_dpnt_mrtl_flag =>p_rec.dpnt_mrtl_flag,
905 p_dpnt_mltry_flag =>p_rec.dpnt_mltry_flag,
906 p_dpnt_pstl_flag =>p_rec.dpnt_pstl_flag,
907 p_dpnt_cvrd_in_anthr_pl_flag =>p_rec.dpnt_cvrd_in_anthr_pl_flag,
908 p_dpnt_dsgnt_crntly_enrld_flag =>p_rec.dpnt_dsgnt_crntly_enrld_flag,
909 p_effective_date =>p_effective_date);
910 --
911 -- Call the datetrack update integrity operation
912 --
913 dt_update_validate
914 (p_regn_id => p_rec.regn_id,
915 p_datetrack_mode => p_datetrack_mode,
916 p_validation_start_date => p_validation_start_date,
917 p_validation_end_date => p_validation_end_date);
918 --
919 hr_utility.set_location(' Leaving:'||l_proc, 10);
920 End update_validate;
921 --
922 -- ----------------------------------------------------------------------------
923 -- |---------------------------< delete_validate >----------------------------|
924 -- ----------------------------------------------------------------------------
925 Procedure delete_validate
926 (p_rec in ben_dce_shd.g_rec_type,
927 p_effective_date in date,
928 p_datetrack_mode in varchar2,
929 p_validation_start_date in date,
930 p_validation_end_date in date) is
931 --
932 l_proc varchar2(72) := g_package||'delete_validate';
933 --
934 Begin
935 hr_utility.set_location('Entering:'||l_proc, 5);
936 --
937 -- Call all supporting business operations
938 --
939 dt_delete_validate
940 (p_datetrack_mode => p_datetrack_mode,
941 p_validation_start_date => p_validation_start_date,
942 p_validation_end_date => p_validation_end_date,
943 p_dpnt_cvg_eligy_prfl_id => p_rec.dpnt_cvg_eligy_prfl_id);
944 --
945 hr_utility.set_location(' Leaving:'||l_proc, 10);
946 End delete_validate;
947 --
948 --
949 -- ---------------------------------------------------------------------------
950 -- |---------------------< return_legislation_code >-------------------------|
951 -- ---------------------------------------------------------------------------
952 --
953 function return_legislation_code
954 (p_dpnt_cvg_eligy_prfl_id in number) return varchar2 is
955 --
956 -- Declare cursor
957 --
958 cursor csr_leg_code is
959 select a.legislation_code
960 from per_business_groups a,
961 ben_dpnt_cvg_eligy_prfl_f b
962 where b.dpnt_cvg_eligy_prfl_id = p_dpnt_cvg_eligy_prfl_id
963 and a.business_group_id = b.business_group_id;
964 --
965 -- Declare local variables
966 --
967 l_legislation_code varchar2(150);
968 l_proc varchar2(72) := g_package||'return_legislation_code';
969 --
970 begin
971 --
972 hr_utility.set_location('Entering:'|| l_proc, 10);
973 --
974 -- Ensure that all the mandatory parameter are not null
975 --
976 hr_api.mandatory_arg_error(p_api_name => l_proc,
977 p_argument => 'dpnt_cvg_eligy_prfl_id',
978 p_argument_value => p_dpnt_cvg_eligy_prfl_id);
979 --
980 open csr_leg_code;
981 --
982 fetch csr_leg_code into l_legislation_code;
983 --
984 if csr_leg_code%notfound then
985 --
986 close csr_leg_code;
987 --
988 -- The primary key is invalid therefore we must error
989 --
990 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
991 fnd_message.raise_error;
992 --
993 end if;
994 --
995 close csr_leg_code;
996 --
997 hr_utility.set_location(' Leaving:'|| l_proc, 20);
998 --
999 return l_legislation_code;
1000 --
1001 end return_legislation_code;
1002 --
1003 end ben_dce_bus;