[Home] [Help]
PACKAGE BODY: APPS.BEN_EGN_BUS
Source
4 -- ----------------------------------------------------------------------------
1 Package Body ben_egn_bus as
2 /* $Header: beegnrhi.pkb 120.0 2005/05/28 02:12:39 appldev noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_egn_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_ELIG_GNDR_PRTE_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_GNDR_PRTE_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_GNDR_PRTE_id(p_ELIG_GNDR_PRTE_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_GNDR_PRTE_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_egn_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_ELIG_GNDR_PRTE_id => p_ELIG_GNDR_PRTE_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_ELIG_GNDR_PRTE_id,hr_api.g_number)
55 <> ben_egn_shd.g_old_rec.ELIG_GNDR_PRTE_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_egn_shd.constraint_error('BEN_ELIG_GNDR_PRTE_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_ELIG_GNDR_PRTE_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_egn_shd.constraint_error('BEN_ELIG_GNDR_PRTE_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_ELIG_GNDR_PRTE_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_dup_elig_criteria >------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure checks, that the same eligibilty criteria (sex) is not
85 -- entered more than once for the same eligibility profile
86 --
87 -- Pre Conditions
88 -- None.
89 --
90 -- In Parameters
91 -- ELIG_GNDR_PRTE_id PK of record being inserted or updated.
92 -- eligy_prfl_id FK eligy_prfl_id
93 -- sex The sex code specified for this profile
94 --
95 -- Post Success
96 -- Processing continues
97 --
98 -- Post Failure
99 -- Errors handled by the procedure
100 --
101 -- Access Status
102 -- Internal table handler use only.
103 --
104 Procedure chk_dup_elig_criteria(p_ELIG_GNDR_PRTE_id in number,
105 p_eligy_prfl_id in number,
106 p_sex 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_dup_elig_criteria';
111 l_api_updating boolean;
112 l_dummy char(1);
113 --
114 cursor c_elig_gndr_prte is
115 select null
116 from ben_elig_gndr_prte_f egn
117 where egn.eligy_prfl_id = p_eligy_prfl_id
118 and egn.sex = p_sex
119 and egn.ELIG_GNDR_PRTE_id <> nvl(p_ELIG_GNDR_PRTE_id, hr_api.g_number) ;
120 --
121 Begin
122 --
123 hr_utility.set_location('Entering:'||l_proc, 5);
124 --
125 l_api_updating := ben_egn_shd.api_updating
126 (p_ELIG_GNDR_PRTE_id => p_ELIG_GNDR_PRTE_id,
127 p_effective_date => p_effective_date,
128 p_object_version_number => p_object_version_number);
129 --
130 -- check if the same sex is entered more than once for the same eligy_prfl_id
131 --
132 open c_elig_gndr_prte;
133 fetch c_elig_gndr_prte into l_dummy;
134 if c_elig_gndr_prte%found then
135 --
136 close c_elig_gndr_prte;
137 hr_utility.set_location(l_proc, 7);
138 --
139 -- raise error as duplicate criteria has been entered
140 --
141 fnd_message.set_name('BEN','BEN_91349_DUP_ELIG_CRITERIA');
142 fnd_message.raise_error;
143 --
144 end if;
145 --
146 close c_elig_gndr_prte;
147 --
148 hr_utility.set_location('Leaving:'||l_proc,10);
149 --
150 end chk_dup_elig_criteria;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_sex >------|
154 -- ----------------------------------------------------------------------------
155 --
156 -- Description
157 -- This procedure is used to check that the Sex code entered is valid ,
158 -- It is validated against the Lookup type 'SEX'
159 --
160 -- Pre Conditions
161 -- None.
162 --
163 -- In Parameters
164 -- ELIG_GNDR_PRTE_id PK of record being inserted or updated.
165 -- sex The sex code specified for this profile
166 -- effective_date Effective Date of session
167 -- object_version_number Object version number of record being
168 -- inserted or updated.
169 --
170 -- Post Success
171 -- Processing continues
172 --
173 -- Post Failure
174 -- Errors handled by the procedure
175 --
176 -- Access Status
177 -- Internal table handler use only.
178 --
179 Procedure chk_sex(p_ELIG_GNDR_PRTE_id in number,
180 p_sex in varchar2,
181 p_effective_date in date,
182 p_object_version_number in number) is
183 --
184 l_proc varchar2(72) := g_package||'chk_sex';
185 l_api_updating boolean;
186 l_dummy char(1);
187 --
188 Begin
189 --
190 hr_utility.set_location('Entering:'||l_proc, 5);
191 --
192 l_api_updating := ben_egn_shd.api_updating
193 (p_ELIG_GNDR_PRTE_id => p_ELIG_GNDR_PRTE_id,
194 p_effective_date => p_effective_date,
198 and p_sex
195 p_object_version_number => p_object_version_number);
196 --
197 if (l_api_updating
199 <> nvl(ben_egn_shd.g_old_rec.sex ,hr_api.g_varchar2)
200 or not l_api_updating) then
201 --
202 -- check if value of lookup falls within lookup type.
203 --
204 --
205 if hr_api.not_exists_in_hr_lookups
206 (p_lookup_type => 'SEX',
207 p_lookup_code => p_sex,
208 p_effective_date => p_effective_date) then
209 --
210 -- raise error as does not exist as lookup
211 --
212 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
213 fnd_message.raise_error;
214 --
215 end if;
216 --
217 end if;
218 --
219 hr_utility.set_location('Leaving:'||l_proc,10);
220 --
221 end chk_sex;
222 --
223 -- ---------------------------------------------------------------------------
224 -- |-----------------------< chk_duplicate_ordr_num >---------------------------|
225 -- ---------------------------------------------------------------------------
226 --
227 -- Description
228 -- Ensure that the Sequence Number is unique
229 -- within business_group
230 --
231 -- Pre Conditions
232 -- None.
233 --
234 -- In Parameters
235 -- p_ELIG_GNDR_PRTE_id ELIG_GNDR_PRTE_id
236 -- p_eligy_prfl_id eligy_prfl_id
237 -- p_ordr_num Sequence Number
238 -- p_business_group_id
239 --
240 -- Post Success
241 -- Processing continues
242 --
243 -- Post Failure
244 -- Errors handled by the procedure
245 --
246 -- Access Status
247 -- Internal table handler use only
248 --
249 -- ----------------------------------------------------------------------------
250 Procedure chk_duplicate_ordr_num
251 ( p_ELIG_GNDR_PRTE_id in number
252 ,p_eligy_prfl_id in number
253 ,p_ordr_num in number
254 ,p_business_group_id in number)
255 is
256 l_proc varchar2(72) := g_package||'chk_duplicate_ordr_num';
257 l_dummy char(1);
258 cursor c1 is
259 select null
260 from ben_ELIG_GNDR_PRTE_f
261 where ELIG_GNDR_PRTE_id <> nvl(p_ELIG_GNDR_PRTE_id,-1)
262 and eligy_prfl_id = p_eligy_prfl_id
263 and ordr_num = p_ordr_num
264 and business_group_id = p_business_group_id;
265 --
266 Begin
267 hr_utility.set_location('Entering:'||l_proc, 5);
268 --
269 open c1;
270 fetch c1 into l_dummy;
271 if c1%found then
272 close c1;
273 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
274 fnd_message.raise_error;
275 end if;
276 close c1;
277 --
278 hr_utility.set_location('Leaving:'||l_proc, 15);
279 End chk_duplicate_ordr_num;
280 --
281 -- ----------------------------------------------------------------------------
282 -- |------< chk_excld_flag >------|
283 -- ----------------------------------------------------------------------------
284 --
285 -- Description
286 -- This procedure is used to check that the lookup value is valid.
287 --
288 -- Pre Conditions
289 -- None.
290 --
291 -- In Parameters
292 -- ELIG_GNDR_PRTE_id PK of record being inserted or updated.
293 -- excld_flag Value of lookup code.
294 -- effective_date effective date
295 -- object_version_number Object version number of record being
296 -- inserted or updated.
297 --
298 -- Post Success
299 -- Processing continues
300 --
301 -- Post Failure
302 -- Error handled by procedure
303 --
304 -- Access Status
305 -- Internal table handler use only.
306 --
307 Procedure chk_excld_flag(p_ELIG_GNDR_PRTE_id in number,
308 p_excld_flag in varchar2,
309 p_effective_date in date,
310 p_object_version_number in number) is
311 --
312 l_proc varchar2(72) := g_package||'chk_excld_flag';
313 l_api_updating boolean;
314 --
315 Begin
316 --
317 hr_utility.set_location('Entering:'||l_proc, 5);
318 --
319 l_api_updating := ben_egn_shd.api_updating
320 (p_ELIG_GNDR_PRTE_id => p_ELIG_GNDR_PRTE_id,
321 p_effective_date => p_effective_date,
322 p_object_version_number => p_object_version_number);
323 --
324 if (l_api_updating
325 and p_excld_flag
326 <> nvl(ben_egn_shd.g_old_rec.excld_flag,hr_api.g_varchar2)
327 or not l_api_updating) then
328 --
329 -- check if value of lookup falls within lookup type.
330 --
331 --
332 if hr_api.not_exists_in_hr_lookups
333 (p_lookup_type => 'YES_NO',
334 p_lookup_code => p_excld_flag,
335 p_effective_date => p_effective_date) then
336 --
337 -- raise error as does not exist as lookup
338 --
339 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
340 fnd_message.raise_error;
341 --
342 end if;
343 --
344 end if;
345 --
346 hr_utility.set_location('Leaving:'||l_proc,10);
347 --
348 end chk_excld_flag;
349 --
350 -- ----------------------------------------------------------------------------
351 -- |--------------------------< dt_update_validate >--------------------------|
352 -- ----------------------------------------------------------------------------
353 -- {Start Of Comments}
354 --
355 -- Description:
356 -- This procedure is used for referential integrity of datetracked
357 -- parent entities when a datetrack update operation is taking place
358 -- and where there is no cascading of update defined for this entity.
359 --
360 -- Prerequisites:
361 -- This procedure is called from the update_validate.
362 --
363 -- In Parameters:
364 --
365 -- Post Success:
366 -- Processing continues.
367 --
368 -- Post Failure:
369 --
370 -- Developer Implementation Notes:
371 -- This procedure should not need maintenance unless the HR Schema model
372 -- changes.
373 --
374 -- Access Status:
375 -- Internal Row Handler Use Only.
376 --
377 -- {End Of Comments}
378 -- ----------------------------------------------------------------------------
379 Procedure dt_update_validate
380 (
381 p_eligy_prfl_id in number default hr_api.g_number,
382 p_datetrack_mode in varchar2,
383 p_validation_start_date in date,
384 p_validation_end_date in date) Is
385 --
386 l_proc varchar2(72) := g_package||'dt_update_validate';
387 l_integrity_error Exception;
388 l_table_name all_tables.table_name%TYPE;
389 --
390 Begin
391 hr_utility.set_location('Entering:'||l_proc, 5);
392 --
393 -- Ensure that the p_datetrack_mode argument is not null
394 --
395 hr_api.mandatory_arg_error
396 (p_api_name => l_proc,
397 p_argument => 'datetrack_mode',
398 p_argument_value => p_datetrack_mode);
399 --
400 -- Only perform the validation if the datetrack update mode is valid
401 --
402 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
403 --
404 --
405 -- Ensure the arguments are not null
406 --
407 hr_api.mandatory_arg_error
408 (p_api_name => l_proc,
409 p_argument => 'validation_start_date',
410 p_argument_value => p_validation_start_date);
411 --
412 hr_api.mandatory_arg_error
413 (p_api_name => l_proc,
414 p_argument => 'validation_end_date',
415 p_argument_value => p_validation_end_date);
416 --
417 If ((nvl(p_eligy_prfl_id, hr_api.g_number) <> hr_api.g_number) and
418 NOT (dt_api.check_min_max_dates
419 (p_base_table_name => 'ben_eligy_prfl_f',
420 p_base_key_column => 'eligy_prfl_id',
421 p_base_key_value => p_eligy_prfl_id,
422 p_from_date => p_validation_start_date,
423 p_to_date => p_validation_end_date))) Then
424 l_table_name := 'ben_eligy_prfl_f';
425 Raise l_integrity_error;
426 End If;
427 --
428 End If;
429 --
430 hr_utility.set_location(' Leaving:'||l_proc, 10);
431 Exception
432 When l_integrity_error Then
433 --
434 -- A referential integrity check was violated therefore
435 -- we must error
436 --
437 ben_utility.parent_integrity_error(p_table_name => l_table_name);
438 When Others Then
439 --
440 -- An unhandled or unexpected error has occurred which
441 -- we must report
442 --
443 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
444 fnd_message.set_token('PROCEDURE', l_proc);
445 fnd_message.set_token('STEP','15');
446 fnd_message.raise_error;
447 End dt_update_validate;
448 --
449 -- ----------------------------------------------------------------------------
450 -- |--------------------------< dt_delete_validate >--------------------------|
451 -- ----------------------------------------------------------------------------
452 -- {Start Of Comments}
453 --
454 -- Description:
455 -- This procedure is used for referential integrity of datetracked
456 -- child entities when either a datetrack DELETE or ZAP is in operation
457 -- and where there is no cascading of delete defined for this entity.
458 -- For the datetrack mode of DELETE or ZAP we must ensure that no
459 -- datetracked child rows exist between the validation start and end
460 -- dates.
461 --
462 -- Prerequisites:
463 -- This procedure is called from the delete_validate.
464 --
465 -- In Parameters:
466 --
467 -- Post Success:
468 -- Processing continues.
469 --
470 -- Post Failure:
471 -- If a row exists by determining the returning Boolean value from the
472 -- generic dt_api.rows_exist function then we must supply an error via
473 -- the use of the local exception handler l_rows_exist.
474 --
475 -- Developer Implementation Notes:
476 -- This procedure should not need maintenance unless the HR Schema model
477 -- changes.
478 --
479 -- Access Status:
480 -- Internal Row Handler Use Only.
481 --
482 -- {End Of Comments}
483 -- ----------------------------------------------------------------------------
484 Procedure dt_delete_validate
485 (p_ELIG_GNDR_PRTE_id in number,
486 p_datetrack_mode in varchar2,
487 p_validation_start_date in date,
488 p_validation_end_date in date) Is
489 --
490 l_proc varchar2(72) := g_package||'dt_delete_validate';
491 l_rows_exist Exception;
492 l_table_name all_tables.table_name%TYPE;
493 --
494 Begin
495 hr_utility.set_location('Entering:'||l_proc, 5);
496 --
497 -- Ensure that the p_datetrack_mode argument is not null
498 --
499 hr_api.mandatory_arg_error
500 (p_api_name => l_proc,
501 p_argument => 'datetrack_mode',
502 p_argument_value => p_datetrack_mode);
503 --
504 -- Only perform the validation if the datetrack mode is either
505 -- DELETE or ZAP
506 --
507 If (p_datetrack_mode = 'DELETE' or
508 p_datetrack_mode = 'ZAP') then
509 --
510 --
511 -- Ensure the arguments are not null
512 --
513 hr_api.mandatory_arg_error
514 (p_api_name => l_proc,
515 p_argument => 'validation_start_date',
516 p_argument_value => p_validation_start_date);
517 --
518 hr_api.mandatory_arg_error
519 (p_api_name => l_proc,
520 p_argument => 'validation_end_date',
521 p_argument_value => p_validation_end_date);
522 --
523 hr_api.mandatory_arg_error
524 (p_api_name => l_proc,
525 p_argument => 'ELIG_GNDR_PRTE_id',
526 p_argument_value => p_ELIG_GNDR_PRTE_id);
527 --
528 --
529 --
530 End If;
531 --
532 hr_utility.set_location(' Leaving:'||l_proc, 10);
533 Exception
534 When l_rows_exist Then
535 --
536 -- A referential integrity check was violated therefore
537 -- we must error
538 --
539 ben_utility.child_exists_error(p_table_name => l_table_name);
540 When Others Then
541 --
542 -- An unhandled or unexpected error has occurred which
543 -- we must report
544 --
545 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
546 fnd_message.set_token('PROCEDURE', l_proc);
547 fnd_message.set_token('STEP','15');
548 fnd_message.raise_error;
549 End dt_delete_validate;
550 --
551 -- ----------------------------------------------------------------------------
552 -- |---------------------------< insert_validate >----------------------------|
553 -- ----------------------------------------------------------------------------
554 Procedure insert_validate
555 (p_rec in ben_egn_shd.g_rec_type,
556 p_effective_date in date,
557 p_datetrack_mode in varchar2,
558 p_validation_start_date in date,
559 p_validation_end_date in date) is
560 --
561 l_proc varchar2(72) := g_package||'insert_validate';
562 --
563 Begin
564 hr_utility.set_location('Entering:'||l_proc, 5);
565 --
566 -- Call all supporting business operations
567 --
568 chk_ELIG_GNDR_PRTE_id
569 (p_ELIG_GNDR_PRTE_id => p_rec.ELIG_GNDR_PRTE_id,
570 p_effective_date => p_effective_date,
571 p_object_version_number => p_rec.object_version_number);
572 --
573 chk_excld_flag
574 (p_ELIG_GNDR_PRTE_id => p_rec.ELIG_GNDR_PRTE_id,
575 p_excld_flag => p_rec.excld_flag,
576 p_effective_date => p_effective_date,
577 p_object_version_number => p_rec.object_version_number);
578 --
579 chk_sex
580 (p_ELIG_GNDR_PRTE_id => p_rec.ELIG_GNDR_PRTE_id,
581 p_sex => p_rec.sex,
582 p_effective_date => p_effective_date,
583 p_object_version_number => p_rec.object_version_number);
584 --
585 chk_duplicate_ordr_num
586 (p_ELIG_GNDR_PRTE_id => p_rec.ELIG_GNDR_PRTE_id,
587 p_eligy_prfl_id => p_rec.eligy_prfl_id,
588 p_ordr_num => p_rec.ordr_num,
589 p_business_group_id => p_rec.business_group_id);
590 --
591 chk_dup_elig_criteria
592 (p_ELIG_GNDR_PRTE_id => p_rec.ELIG_GNDR_PRTE_id,
593 p_eligy_prfl_id => p_rec.eligy_prfl_id,
594 p_sex => p_rec.sex,
595 p_effective_date => p_effective_date,
596 p_object_version_number => p_rec.object_version_number);
597 --
598
599 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
600 --
601 hr_utility.set_location(' Leaving:'||l_proc, 10);
602 End insert_validate;
603 --
604 -- ----------------------------------------------------------------------------
605 -- |---------------------------< update_validate >----------------------------|
606 -- ----------------------------------------------------------------------------
607 Procedure update_validate
608 (p_rec in ben_egn_shd.g_rec_type,
609 p_effective_date in date,
610 p_datetrack_mode in varchar2,
611 p_validation_start_date in date,
612 p_validation_end_date in date) is
613 --
614 l_proc varchar2(72) := g_package||'update_validate';
615 --
616 Begin
617 hr_utility.set_location('Entering:'||l_proc, 5);
618 --
619 -- Call all supporting business operations
620 --
621 chk_ELIG_GNDR_PRTE_id
622 (p_ELIG_GNDR_PRTE_id => p_rec.ELIG_GNDR_PRTE_id,
623 p_effective_date => p_effective_date,
624 p_object_version_number => p_rec.object_version_number);
625 --
626 chk_excld_flag
627 (p_ELIG_GNDR_PRTE_id => p_rec.ELIG_GNDR_PRTE_id,
628 p_excld_flag => p_rec.excld_flag,
629 p_effective_date => p_effective_date,
630 p_object_version_number => p_rec.object_version_number);
631 --
632 chk_sex
633 (p_ELIG_GNDR_PRTE_id => p_rec.ELIG_GNDR_PRTE_id,
634 p_sex => p_rec.sex,
635 p_effective_date => p_effective_date,
636 p_object_version_number => p_rec.object_version_number);
637 --
638 chk_duplicate_ordr_num
639 (p_ELIG_GNDR_PRTE_id => p_rec.ELIG_GNDR_PRTE_id,
640 p_eligy_prfl_id => p_rec.eligy_prfl_id,
641 p_ordr_num => p_rec.ordr_num,
642 p_business_group_id => p_rec.business_group_id);
643 --
644 chk_dup_elig_criteria
645 (p_ELIG_GNDR_PRTE_id => p_rec.ELIG_GNDR_PRTE_id,
646 p_eligy_prfl_id => p_rec.eligy_prfl_id,
647 p_sex => p_rec.sex,
648 p_effective_date => p_effective_date,
649 p_object_version_number => p_rec.object_version_number);
650 --
651
652 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
653 --
654 -- Call the datetrack update integrity operation
655 --
656 dt_update_validate
657 (
658 p_eligy_prfl_id => p_rec.eligy_prfl_id,
659 p_datetrack_mode => p_datetrack_mode,
660 p_validation_start_date => p_validation_start_date,
661 p_validation_end_date => p_validation_end_date);
662 --
663 hr_utility.set_location(' Leaving:'||l_proc, 10);
664 End update_validate;
665 --
666 -- ----------------------------------------------------------------------------
667 -- |---------------------------< delete_validate >----------------------------|
668 -- ----------------------------------------------------------------------------
669 Procedure delete_validate
670 (p_rec in ben_egn_shd.g_rec_type,
671 p_effective_date in date,
672 p_datetrack_mode in varchar2,
673 p_validation_start_date in date,
674 p_validation_end_date in date) is
675 --
676 l_proc varchar2(72) := g_package||'delete_validate';
677 --
678 Begin
679 hr_utility.set_location('Entering:'||l_proc, 5);
680 --
681 -- Call all supporting business operations
682 --
683 dt_delete_validate
684 (p_datetrack_mode => p_datetrack_mode,
685 p_validation_start_date => p_validation_start_date,
686 p_validation_end_date => p_validation_end_date,
687 p_ELIG_GNDR_PRTE_id => p_rec.ELIG_GNDR_PRTE_id);
688 --
689 hr_utility.set_location(' Leaving:'||l_proc, 10);
690 End delete_validate;
691 --
692 --
693 -- ---------------------------------------------------------------------------
694 -- |---------------------< return_legislation_code >-------------------------|
695 -- ---------------------------------------------------------------------------
696 --
697 function return_legislation_code
698 (p_ELIG_GNDR_PRTE_id in number) return varchar2 is
699 --
700 -- Declare cursor
701 --
702 cursor csr_leg_code is
703 select a.legislation_code
704 from per_business_groups a,
705 ben_ELIG_GNDR_PRTE_f b
706 where b.ELIG_GNDR_PRTE_id = p_ELIG_GNDR_PRTE_id
707 and a.business_group_id = b.business_group_id;
708 --
709 -- Declare local variables
710 --
711 l_legislation_code varchar2(150);
712 l_proc varchar2(72) := g_package||'return_legislation_code';
713 --
714 begin
715 --
716 hr_utility.set_location('Entering:'|| l_proc, 10);
717 --
718 -- Ensure that all the mandatory parameter are not null
719 --
720 hr_api.mandatory_arg_error(p_api_name => l_proc,
721 p_argument => 'ELIG_GNDR_PRTE_id',
722 p_argument_value => p_ELIG_GNDR_PRTE_id);
723 --
724 open csr_leg_code;
725 --
726 fetch csr_leg_code into l_legislation_code;
727 --
728 if csr_leg_code%notfound then
729 --
730 close csr_leg_code;
731 --
732 -- The primary key is invalid therefore we must error
733 --
734 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
735 fnd_message.raise_error;
736 --
737 end if;
738 --
739 close csr_leg_code;
740 --
741 hr_utility.set_location(' Leaving:'|| l_proc, 20);
742 --
743 return l_legislation_code;
744 --
745 end return_legislation_code;
746 --
747 end ben_egn_bus;