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