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