[Home] [Help]
PACKAGE BODY: APPS.BEN_CER_BUS
Source
1 Package Body ben_cer_bus as
2 /* $Header: becerrhi.pkb 120.0 2005/05/28 01:00:50 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cer_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_prtn_eligy_rl_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 -- prtn_eligy_rl_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_prtn_eligy_rl_id(p_prtn_eligy_rl_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_prtn_eligy_rl_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_cer_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_prtn_eligy_rl_id => p_prtn_eligy_rl_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_prtn_eligy_rl_id,hr_api.g_number)
55 <> ben_cer_shd.g_old_rec.prtn_eligy_rl_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_cer_shd.constraint_error('BEN_ELIGY_RL_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_prtn_eligy_rl_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_cer_shd.constraint_error('BEN_ELIGY_RL_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_prtn_eligy_rl_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_mndtry_flag >------|
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 -- prtn_eligy_rl_id PK of record being inserted or updated.
91 -- mndtry_flag 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_mndtry_flag(p_prtn_eligy_rl_id in number,
106 p_mndtry_flag 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_mndtry_flag';
111 l_api_updating boolean;
112 --
113 Begin
114 --
115 hr_utility.set_location('Entering:'||l_proc, 5);
116 --
117 l_api_updating := ben_cer_shd.api_updating
118 (p_prtn_eligy_rl_id => p_prtn_eligy_rl_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_mndtry_flag
124 <> nvl(ben_cer_shd.g_old_rec.mndtry_flag,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 => 'YES_NO',
132 p_lookup_code => p_mndtry_flag,
133 p_effective_date => p_effective_date) then
134 --
135 -- raise error as does not exist as lookup
136 --
137 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
138 fnd_message.set_token('FIELD', 'p_mndtry_flag');
139 fnd_message.set_token('TYPE', 'YES_NO');
140 fnd_message.raise_error;
141 --
142 end if;
143 --
144 end if;
145 --
146 hr_utility.set_location('Leaving:'||l_proc,10);
147 --
148 end chk_mndtry_flag;
149 --
150 -- ----------------------------------------------------------------------------
151 -- |------< chk_drvbl_fctr_apls_flag >------|
152 -- ----------------------------------------------------------------------------
153 --
154 -- Description
158 -- None.
155 -- This procedure is used to check that the lookup value is valid.
156 --
157 -- Pre Conditions
159 --
160 -- In Parameters
161 -- prtn_eligy_rl_id PK of record being inserted or updated.
162 -- drvbl_fctr_apls_flag Value of lookup code.
163 -- effective_date effective date
164 -- object_version_number Object version number of record being
165 -- inserted or updated.
166 --
167 -- Post Success
168 -- Processing continues
169 --
170 -- Post Failure
171 -- Error handled by procedure
172 --
173 -- Access Status
174 -- Internal table handler use only.
175 --
176 Procedure chk_drvbl_fctr_apls_flag(p_prtn_eligy_rl_id in number,
177 p_drvbl_fctr_apls_flag in varchar2,
178 p_effective_date in date,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_drvbl_fctr_apls_flag';
182 l_api_updating boolean;
183 --
184 Begin
185 --
186 hr_utility.set_location('Entering:'||l_proc, 5);
187 --
188 l_api_updating := ben_cer_shd.api_updating
189 (p_prtn_eligy_rl_id => p_prtn_eligy_rl_id,
190 p_effective_date => p_effective_date,
191 p_object_version_number => p_object_version_number);
192 --
193 if (l_api_updating
194 and p_drvbl_fctr_apls_flag
195 <> nvl(ben_cer_shd.g_old_rec.drvbl_fctr_apls_flag,hr_api.g_varchar2)
196 or not l_api_updating) then
197 --
198 -- check if value of lookup falls within lookup type.
199 --
200 --
201 if hr_api.not_exists_in_hr_lookups
202 (p_lookup_type => 'YES_NO',
203 p_lookup_code => p_drvbl_fctr_apls_flag,
204 p_effective_date => p_effective_date) then
205 --
206 -- raise error as does not exist as lookup
207 --
208 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
209 fnd_message.set_token('FIELD', 'p_drvbl_fctr_apls_flag');
210 fnd_message.set_token('TYPE', 'YES_NO');
211 fnd_message.raise_error;
212 --
213 end if;
214 --
215 end if;
216 --
217 hr_utility.set_location('Leaving:'||l_proc,10);
218 --
219 end chk_drvbl_fctr_apls_flag;
220 --
221 --
222 -- ----------------------------------------------------------------------------
223 -- |---------------------------------< chk_formula_id >-----------------------|
224 -- ----------------------------------------------------------------------------
225 --
226 -- Description
227 -- This procedure is used to check that the Formula Rule is valid.
228 --
229 -- Pre Conditions
230 -- None.
231 --
232 -- In Parameters
233 -- prtn_eligy_rl_id PK of record being inserted or updated.
234 -- formula_id Value of formula rule id.
235 -- effective_date effective date
236 -- object_version_number Object version number of record being
237 -- inserted or updated.
238 --
239 -- Post Success
240 -- Processing continues
241 --
242 -- Post Failure
243 -- Error handled by procedure
244 --
245 -- Access Status
246 -- Internal table handler use only.
247 Procedure chk_formula_id(p_prtn_eligy_rl_id in number,
248 p_business_group_id in number,
249 p_formula_id in number,
250 p_effective_date in date,
251 p_object_version_number in number) is
252 --
253 l_proc varchar2(72) := g_package||'chk_formula_id';
254 l_api_updating boolean;
255 l_dummy varchar2(1);
256 --
257 cursor c1 is
258 select null
259 from ff_formulas_f ff,
260 per_business_groups pbg
261 where ff.formula_id = p_formula_id
262 and ff.formula_type_id = -153 -- BEN_ELIGY
263 and pbg.business_group_id = p_business_group_id
264 and nvl(ff.business_group_id,p_business_group_id) =
265 p_business_group_id
266 and nvl(ff.legislation_code,pbg.legislation_code) =
267 pbg.legislation_code
268 and p_effective_date
269 between ff.effective_start_date
270 and ff.effective_end_date;
271 --
272 Begin
273 --
274 hr_utility.set_location('Entering:'||l_proc, 5);
275 --
276 l_api_updating := ben_cer_shd.api_updating
277 (p_prtn_eligy_rl_id => p_prtn_eligy_rl_id,
278 p_effective_date => p_effective_date,
279 p_object_version_number => p_object_version_number);
280 --
281 if (l_api_updating
282 and nvl(p_formula_id,hr_api.g_number)
283 <> ben_cer_shd.g_old_rec.formula_id
284 or not l_api_updating)
285 and p_formula_id is not null then
286 --
287 -- check if value of formula rule is valid.
288 --
289 open c1;
290 --
291 -- fetch value from cursor if it returns a record then the
292 -- formula is valid otherwise its invalid
293 --
294 fetch c1 into l_dummy;
295 if c1%notfound then
296 --
297 close c1;
298 --
299 -- raise error
300 --
301 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
302 fnd_message.set_token('ID',p_formula_id);
303 fnd_message.set_token('TYPE_ID',-153);
304 fnd_message.raise_error;
305 --
306 end if;
307 --
308 close c1;
309 --
310 end if;
311 --
315 --
312 hr_utility.set_location('Leaving:'||l_proc,10);
313 --
314 end chk_formula_id;
316 -- ----------------------------------------------------------------------------
317 -- |--------------------<chk_duplicate_ordr_num >-----------------------------|
318 -- ----------------------------------------------------------------------------
319 --
320 -- Description
321 -- make sure ordr_to_aply_num is unique within prtn_elig_prfl
322 --
323 -- Pre Conditions
324 -- None.
325 --
326 -- In Parameters
327 -- p_prtn_eligy_prfl_rl_id
328 -- p_prtn_elig_prfl_id
329 -- p_ordr_to_aply_num
330 -- p_business_group_id
331 -- p_effective_date
332 --
333 -- Post Success
334 -- Processing continues
335 --
336 -- Post Failure
337 -- Errors handled by the procedure
338 --
339 -- Access Status
340 -- Internal table handler use only.
341 --
342 -- ----------------------------------------------------------------------------
343 Procedure chk_duplicate_ordr_num
344 ( p_prtn_eligy_rl_id in number
345 ,p_prtn_elig_id in number
346 ,p_ordr_to_aply_num in number
347 ,p_business_group_id in number
348 ,p_effective_date in date )
349 is
350 l_proc varchar2(72) := g_package||' chk_duplicate_ordr_num ';
351 l_dummy char(1);
352 cursor c1 is select null
353 from ben_prtn_eligy_rl_f
354 where prtn_eligy_rl_id <> nvl(p_prtn_eligy_rl_id, -1 )
355 and prtn_elig_id = p_prtn_elig_id
356 and ordr_to_aply_num = p_ordr_to_aply_num
357 and business_group_id + 0 = p_business_group_id
358 and p_effective_date between effective_start_date
359 and effective_end_date;
360 --
361 Begin
362 --
363 hr_utility.set_location('Entering:'||l_proc, 5);
364 --
365 open c1;
366 fetch c1 into l_dummy;
367 if c1%found then
368 close c1;
369 hr_utility.set_message(801,'Duplicate sequence#');
370 hr_utility.raise_error;
371 end if;
372 close c1;
373 --
374 hr_utility.set_location('Leaving:'||l_proc, 15);
375 --
376 End chk_duplicate_ordr_num;
377 --
378 -- bug 3876692
379 --
380 -- ----------------------------------------------------------------------------
381 -- |------< chk_flx_impt_extnce >------|
382 -- ----------------------------------------------------------------------------
383 --
384 -- Description
385 -- This procedure checks if plan type is flex or imputed shell or not.
386 --
387 -- Pre-Conditions
388 -- None.
389 --
390 -- In Parameters
391 -- p_prtn_elig_id of FK column
392 -- p_effective_date session date
393 --
394 -- Post Success
395 -- Processing continues
396 --
397 -- Post Failure
398 -- Error raised.
399 --
400 -- Access Status
401 -- Internal table handler use only.
402 --
403 Procedure chk_flx_impt_extnce(
404 p_prtn_elig_id in number,
405 p_effective_date in date) is
406 --
407 l_proc varchar2(72) := g_package||'chk_flx_impt_extnce';
408 l_exists varchar2(1);
409 --
410 cursor c1 is
411 select null
412 from ben_prtn_elig_f prtn, ben_pl_f p
413 where prtn.prtn_elig_id = p_prtn_elig_id
414 and prtn.pl_id = p.pl_id
415 and (p.invk_flx_cr_pl_flag = 'Y'
416 or
417 p.imptd_incm_calc_cd is not null)
418 and p_effective_date between prtn.effective_start_date
419 and prtn.effective_end_date
420 and p_effective_date between p.effective_start_date
421 and p.effective_end_date;
422 --
423 cursor c2 is
424 select null
425 from ben_prtn_elig_f epa, ben_pl_f pln, ben_plip_f plip
426 where epa.prtn_elig_id = p_prtn_elig_id
427 and epa.plip_id = plip.plip_id
428 and plip.pl_id = pln.pl_id
429 and (pln.invk_flx_cr_pl_flag = 'Y'
430 or
431 pln.imptd_incm_calc_cd is not null)
432 and p_effective_date between epa.effective_start_date
433 and epa.effective_end_date
434 and p_effective_date between pln.effective_start_date
435 and pln.effective_end_date
436 and p_effective_date between plip.effective_start_date
437 and plip.effective_end_date;
438 --
439 Begin
440 --
441 hr_utility.set_location('Entering:'||l_proc,5);
442 --
443 open c1;
444 fetch c1 into l_exists;
445 if c1%found then
446 close c1;
447 --
448 -- raise error as insert or update is not allowed when plan
449 -- is flex or imputed shell type
450 --
451 fnd_message.set_name('BEN','BEN_94046_PLN_INS_VAL');
452 fnd_message.raise_error;
453 --
454 else
455 --
456 close c1;
457 open c2;
458 fetch c2 into l_exists;
459 if c2%found then
460 close c2;
461 --
462 -- raise error as insert or update is not allowed when plan
463 -- is flex or imputed shell type
464 --
465 fnd_message.set_name('BEN','BEN_94046_PLN_INS_VAL');
466 fnd_message.raise_error;
467 --
468 end if;
469 close c2;
470 --
471 end if;
472 --
473 hr_utility.set_location('Leaving:'||l_proc,10);
474 --
475 End chk_flx_impt_extnce;
476 --
477 --
478 -- ----------------------------------------------------------------------------
482 --
479 -- |--------------------------< dt_update_validate >--------------------------|
480 -- ----------------------------------------------------------------------------
481 -- {Start Of Comments}
483 -- Description:
484 -- This procedure is used for referential integrity of datetracked
485 -- parent entities when a datetrack update operation is taking place
486 -- and where there is no cascading of update defined for this entity.
487 --
488 -- Prerequisites:
489 -- This procedure is called from the update_validate.
490 --
491 -- In Parameters:
492 --
493 -- Post Success:
494 -- Processing continues.
495 --
496 -- Post Failure:
497 --
498 -- Developer Implementation Notes:
499 -- This procedure should not need maintenance unless the HR Schema model
500 -- changes.
501 --
502 -- Access Status:
503 -- Internal Row Handler Use Only.
504 --
505 -- {End Of Comments}
506 -- ----------------------------------------------------------------------------
507 Procedure dt_update_validate
508 (p_prtn_elig_id in number default hr_api.g_number,
509 p_datetrack_mode in varchar2,
510 p_validation_start_date in date,
511 p_validation_end_date in date) Is
512 --
513 l_proc varchar2(72) := g_package||'dt_update_validate';
514 l_integrity_error Exception;
515 l_table_name all_tables.table_name%TYPE;
516 --
517 Begin
518 hr_utility.set_location('Entering:'||l_proc, 5);
519 --
520 -- Ensure that the p_datetrack_mode argument is not null
521 --
522 hr_api.mandatory_arg_error
523 (p_api_name => l_proc,
524 p_argument => 'datetrack_mode',
525 p_argument_value => p_datetrack_mode);
526 --
527 -- Only perform the validation if the datetrack update mode is valid
528 --
529 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
530 --
531 --
532 -- Ensure the arguments are not null
533 --
534 hr_api.mandatory_arg_error
535 (p_api_name => l_proc,
536 p_argument => 'validation_start_date',
537 p_argument_value => p_validation_start_date);
538 --
539 hr_api.mandatory_arg_error
540 (p_api_name => l_proc,
541 p_argument => 'validation_end_date',
542 p_argument_value => p_validation_end_date);
543 --
544 If ((nvl(p_prtn_elig_id, hr_api.g_number) <> hr_api.g_number) and
545 NOT (dt_api.check_min_max_dates
546 (p_base_table_name => 'ben_prtn_elig_f',
547 p_base_key_column => 'prtn_elig_id',
548 p_base_key_value => p_prtn_elig_id,
549 p_from_date => p_validation_start_date,
550 p_to_date => p_validation_end_date))) Then
551 l_table_name := 'ben_prtn_elig_f';
552 Raise l_integrity_error;
553 End If;
554 --
555 End If;
556 --
557 hr_utility.set_location(' Leaving:'||l_proc, 10);
558 Exception
559 When l_integrity_error Then
560 --
561 -- A referential integrity check was violated therefore
562 -- we must error
563 --
564 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
565 hr_utility.set_message_token('TABLE_NAME', l_table_name);
566 hr_utility.raise_error;
567 When Others Then
568 --
569 -- An unhandled or unexpected error has occurred which
570 -- we must report
571 --
572 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
573 hr_utility.set_message_token('PROCEDURE', l_proc);
574 hr_utility.set_message_token('STEP','15');
575 hr_utility.raise_error;
576 End dt_update_validate;
577 --
578 -- ----------------------------------------------------------------------------
579 -- |--------------------------< dt_delete_validate >--------------------------|
580 -- ----------------------------------------------------------------------------
581 -- {Start Of Comments}
582 --
583 -- Description:
584 -- This procedure is used for referential integrity of datetracked
585 -- child entities when either a datetrack DELETE or ZAP is in operation
586 -- and where there is no cascading of delete defined for this entity.
587 -- For the datetrack mode of DELETE or ZAP we must ensure that no
588 -- datetracked child rows exist between the validation start and end
589 -- dates.
590 --
591 -- Prerequisites:
592 -- This procedure is called from the delete_validate.
593 --
594 -- In Parameters:
595 --
596 -- Post Success:
597 -- Processing continues.
598 --
599 -- Post Failure:
600 -- If a row exists by determining the returning Boolean value from the
601 -- generic dt_api.rows_exist function then we must supply an error via
602 -- the use of the local exception handler l_rows_exist.
603 --
604 -- Developer Implementation Notes:
605 -- This procedure should not need maintenance unless the HR Schema model
606 -- changes.
607 --
608 -- Access Status:
609 -- Internal Row Handler Use Only.
610 --
611 -- {End Of Comments}
612 -- ----------------------------------------------------------------------------
613 Procedure dt_delete_validate
614 (p_prtn_eligy_rl_id in number,
615 p_datetrack_mode in varchar2,
616 p_validation_start_date in date,
617 p_validation_end_date in date) Is
618 --
619 l_proc varchar2(72) := g_package||'dt_delete_validate';
620 l_rows_exist Exception;
621 l_table_name all_tables.table_name%TYPE;
622 --
623 Begin
624 hr_utility.set_location('Entering:'||l_proc, 5);
625 --
626 -- Ensure that the p_datetrack_mode argument is not null
627 --
631 p_argument_value => p_datetrack_mode);
628 hr_api.mandatory_arg_error
629 (p_api_name => l_proc,
630 p_argument => 'datetrack_mode',
632 --
633 -- Only perform the validation if the datetrack mode is either
634 -- DELETE or ZAP
635 --
636 If (p_datetrack_mode = 'DELETE' or
637 p_datetrack_mode = 'ZAP') then
638 --
639 --
640 -- Ensure the arguments are not null
641 --
642 hr_api.mandatory_arg_error
643 (p_api_name => l_proc,
644 p_argument => 'validation_start_date',
645 p_argument_value => p_validation_start_date);
646 --
647 hr_api.mandatory_arg_error
648 (p_api_name => l_proc,
649 p_argument => 'validation_end_date',
650 p_argument_value => p_validation_end_date);
651 --
652 hr_api.mandatory_arg_error
653 (p_api_name => l_proc,
654 p_argument => 'prtn_eligy_rl_id',
655 p_argument_value => p_prtn_eligy_rl_id);
656 --
657 --
658 --
659 End If;
660 --
661 hr_utility.set_location(' Leaving:'||l_proc, 10);
662 Exception
663 When l_rows_exist Then
664 --
665 -- A referential integrity check was violated therefore
666 -- we must error
667 --
668 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
669 hr_utility.set_message_token('TABLE_NAME', l_table_name);
670 hr_utility.raise_error;
671 When Others Then
672 --
673 -- An unhandled or unexpected error has occurred which
674 -- we must report
675 --
676 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
677 hr_utility.set_message_token('PROCEDURE', l_proc);
678 hr_utility.set_message_token('STEP','15');
679 hr_utility.raise_error;
680 End dt_delete_validate;
681 --
682 -- ----------------------------------------------------------------------------
683 -- |---------------------------< insert_validate >----------------------------|
684 -- ----------------------------------------------------------------------------
685 Procedure insert_validate
686 (p_rec in ben_cer_shd.g_rec_type,
687 p_effective_date in date,
688 p_datetrack_mode in varchar2,
689 p_validation_start_date in date,
690 p_validation_end_date in date) is
691 --
692 l_proc varchar2(72) := g_package||'insert_validate';
693 --
694 Begin
695 hr_utility.set_location('Entering:'||l_proc, 5);
696 --
697 -- Call all supporting business operations
698 --
699 --
700 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
701 --
702 chk_prtn_eligy_rl_id
703 (p_prtn_eligy_rl_id => p_rec.prtn_eligy_rl_id,
704 p_effective_date => p_effective_date,
705 p_object_version_number => p_rec.object_version_number);
706 --
707 chk_mndtry_flag
708 (p_prtn_eligy_rl_id => p_rec.prtn_eligy_rl_id,
709 p_mndtry_flag => p_rec.mndtry_flag,
710 p_effective_date => p_effective_date,
711 p_object_version_number => p_rec.object_version_number);
712 --
713 chk_drvbl_fctr_apls_flag
714 (p_prtn_eligy_rl_id => p_rec.prtn_eligy_rl_id,
715 p_drvbl_fctr_apls_flag => p_rec.drvbl_fctr_apls_flag,
716 p_effective_date => p_effective_date,
717 p_object_version_number => p_rec.object_version_number);
718 --
719 chk_duplicate_ordr_num
720 (p_rec.prtn_eligy_rl_id
721 ,p_rec.prtn_elig_id
722 ,p_rec.ordr_to_aply_num
723 ,p_rec.business_group_id
724 ,p_effective_date);
725 --
726 chk_formula_id
727 (p_prtn_eligy_rl_id => p_rec.prtn_eligy_rl_id,
728 p_business_group_id => p_rec.business_group_id,
729 p_formula_id => p_rec.formula_id,
730 p_effective_date => p_effective_date,
731 p_object_version_number => p_rec.object_version_number);
732 --
733 -- bug 3876692
734 --
738 hr_utility.set_location(' Leaving:'||l_proc, 10);
735 chk_flx_impt_extnce(p_prtn_elig_id => p_rec.prtn_elig_id,
736 p_effective_date => p_effective_date);
737 --
739 End insert_validate;
740 --
741 -- ----------------------------------------------------------------------------
742 -- |---------------------------< update_validate >----------------------------|
743 -- ----------------------------------------------------------------------------
744 Procedure update_validate
748 p_validation_start_date in date,
745 (p_rec in ben_cer_shd.g_rec_type,
746 p_effective_date in date,
747 p_datetrack_mode in varchar2,
749 p_validation_end_date in date) is
750 --
751 l_proc varchar2(72) := g_package||'update_validate';
752 --
753 Begin
754 hr_utility.set_location('Entering:'||l_proc, 5);
755 --
756 -- Call all supporting business operations
757 --
758 --
759 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
760 --
761 chk_prtn_eligy_rl_id
762 (p_prtn_eligy_rl_id => p_rec.prtn_eligy_rl_id,
763 p_effective_date => p_effective_date,
764 p_object_version_number => p_rec.object_version_number);
765 --
766 chk_mndtry_flag
767 (p_prtn_eligy_rl_id => p_rec.prtn_eligy_rl_id,
768 p_mndtry_flag => p_rec.mndtry_flag,
769 p_effective_date => p_effective_date,
770 p_object_version_number => p_rec.object_version_number);
771 --
772 chk_drvbl_fctr_apls_flag
773 (p_prtn_eligy_rl_id => p_rec.prtn_eligy_rl_id,
774 p_drvbl_fctr_apls_flag => p_rec.drvbl_fctr_apls_flag,
775 p_effective_date => p_effective_date,
776 p_object_version_number => p_rec.object_version_number);
777 --
778 chk_duplicate_ordr_num
779 (p_rec.prtn_eligy_rl_id
780 ,p_rec.prtn_elig_id
781 ,p_rec.ordr_to_aply_num
782 ,p_rec.business_group_id
783 ,p_effective_date);
784 --
785 chk_formula_id
786 (p_prtn_eligy_rl_id => p_rec.prtn_eligy_rl_id,
787 p_business_group_id => p_rec.business_group_id,
788 p_formula_id => p_rec.formula_id,
789 p_effective_date => p_effective_date,
790 p_object_version_number => p_rec.object_version_number);
791 --
792 -- Call the datetrack update integrity operation
793 --
794 dt_update_validate
795 (p_prtn_elig_id => p_rec.prtn_elig_id,
796 p_datetrack_mode => p_datetrack_mode,
797 p_validation_start_date => p_validation_start_date,
798 p_validation_end_date => p_validation_end_date);
799 --
800 -- bug 3876692
801 --
802 chk_flx_impt_extnce(p_prtn_elig_id => p_rec.prtn_elig_id,
803 p_effective_date => p_effective_date);
804 --
805 hr_utility.set_location(' Leaving:'||l_proc, 10);
806 End update_validate;
807 --
808 -- ----------------------------------------------------------------------------
809 -- |---------------------------< delete_validate >----------------------------|
810 -- ----------------------------------------------------------------------------
811 Procedure delete_validate
812 (p_rec in ben_cer_shd.g_rec_type,
813 p_effective_date in date,
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||'delete_validate';
819 --
820 Begin
821 hr_utility.set_location('Entering:'||l_proc, 5);
822 --
823 -- Call all supporting business operations
824 --
825 dt_delete_validate
826 (p_datetrack_mode => p_datetrack_mode,
827 p_validation_start_date => p_validation_start_date,
828 p_validation_end_date => p_validation_end_date,
829 p_prtn_eligy_rl_id => p_rec.prtn_eligy_rl_id);
830 --
831 hr_utility.set_location(' Leaving:'||l_proc, 10);
832 End delete_validate;
833 --
834 --
835 -- ---------------------------------------------------------------------------
836 -- |---------------------< return_legislation_code >-------------------------|
837 -- ---------------------------------------------------------------------------
838 --
839 function return_legislation_code
840 (p_prtn_eligy_rl_id in number) return varchar2 is
841 --
842 -- Declare cursor
843 --
844 cursor csr_leg_code is
845 select a.legislation_code
846 from per_business_groups a,
847 ben_prtn_eligy_rl_f b
848 where b.prtn_eligy_rl_id = p_prtn_eligy_rl_id
849 and a.business_group_id = b.business_group_id;
850 --
851 -- Declare local variables
852 --
853 l_legislation_code varchar2(150);
854 l_proc varchar2(72) := g_package||'return_legislation_code';
855 --
856 begin
857 --
858 hr_utility.set_location('Entering:'|| l_proc, 10);
859 --
860 -- Ensure that all the mandatory parameter are not null
861 --
862 hr_api.mandatory_arg_error(p_api_name => l_proc,
863 p_argument => 'prtn_eligy_rl_id',
864 p_argument_value => p_prtn_eligy_rl_id);
865 --
866 open csr_leg_code;
867 --
868 fetch csr_leg_code into l_legislation_code;
869 --
870 if csr_leg_code%notfound then
871 --
872 close csr_leg_code;
873 --
874 -- The primary key is invalid therefore we must error
875 --
876 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
877 hr_utility.raise_error;
878 --
879 end if;
880 --
881 close csr_leg_code;
882 --
883 hr_utility.set_location(' Leaving:'|| l_proc, 20);
884 --
885 return l_legislation_code;
886 --
887 end return_legislation_code;
888 --
889 end ben_cer_bus;