DBA Data[Home] [Help]

PACKAGE BODY: APPS.BEN_ART_BUS

Source


1 Package Body ben_art_bus as
2 /* $Header: beartrhi.pkb 120.0.12010000.2 2008/08/05 14:03:53 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |                     Private Global Definitions                           |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package  varchar2(33)	:= '  ben_art_bus.';  -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_age_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 --   age_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_age_rt_id(p_age_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_age_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_art_shd.api_updating
49     (p_effective_date              => p_effective_date,
50      p_age_rt_id                => p_age_rt_id,
51      p_object_version_number       => p_object_version_number);
52   --
53   if (l_api_updating
54      and nvl(p_age_rt_id,hr_api.g_number)
55      <>  ben_art_shd.g_old_rec.age_rt_id) then
56     --
57     -- raise error as PK has changed
58     --
59     ben_art_shd.constraint_error('BEN_AGE_RT_F_PK');
60     --
61   elsif not l_api_updating then
62     --
63     -- check if PK is null
64     --
65     if p_age_rt_id is not null then
66       --
67       -- raise error as PK is not null
68       --
69       ben_art_shd.constraint_error('BEN_AGE_RT_F_PK');
70       --
71     end if;
72     --
73   end if;
74   --
75   hr_utility.set_location('Leaving:'||l_proc, 10);
76   --
77 End chk_age_rt_id;
78 --
79 -- |--------------------< chk_duplicate_ordr_num >----------------------------|
80 -- ----------------------------------------------------------------------------
81 --
82 -- Description
83 --
84 -- Pre Conditions
85 --   None.
86 --
87 -- In Parameters
88 --    p_vrbl_rt_prfl_id
89 --    p_ordr_num
90 --    p_effective_date
91 --    p_business_group_id
92 --
93 -- Post Success
94 --   Processing continues
95 --
96 -- Post Failure
97 --   Errors handled by the procedure
98 --
99 -- Access Status
100 --   Internal table handler use only.
101 --
102 -- ----------------------------------------------------------------------------
103 procedure chk_duplicate_ordr_num
104           (p_vrbl_rt_prfl_id in number
105            ,p_ordr_num in number
106            ,p_effective_date in date
107            ,p_business_group_id in number
108            ,p_age_rt_id   in number)
109 is
110    l_proc   varchar2(72) := g_package||' chk_duplicate_ordr_num ';
111    l_dummy    char(1);
112    cursor c1 is select null
113                   from ben_age_rt_f
114                  where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
115                    and age_rt_id<> nvl(p_age_rt_id,-1)
116                    and p_effective_date between effective_start_date
117                                             and effective_end_date
118                    and business_group_id + 0 = p_business_group_id
119                    and ordr_num = p_ordr_num;
120 --
121 Begin
122    hr_utility.set_location('Entering:'||l_proc, 5);
123 
124    --
125    open c1;
126    fetch c1 into l_dummy;
127    --
128    if c1%found then
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 -- ----------------------------------------------------------------------------
139 -- |------< chk_excld_flag >------|
140 -- ----------------------------------------------------------------------------
141 --
142 -- Description
143 --   This procedure is used to check that the lookup value is valid.
144 --
145 -- Pre Conditions
146 --   None.
147 --
148 -- In Parameters
149 --   age_rt_id PK of record being inserted or updated.
150 --   excld_flag Value of lookup code.
151 --   effective_date effective date
152 --   object_version_number Object version number of record being
153 --                         inserted or updated.
154 --
155 -- Post Success
156 --   Processing continues
157 --
158 -- Post Failure
159 --   Error handled by procedure
160 --
161 -- Access Status
162 --   Internal table handler use only.
163 --
164 Procedure chk_excld_flag(p_age_rt_id                in number,
165                             p_excld_flag               in varchar2,
166                             p_effective_date              in date,
167                             p_object_version_number       in number) is
168   --
169   l_proc         varchar2(72) := g_package||'chk_excld_flag';
170   l_api_updating boolean;
171   --
172 Begin
173   --
174   hr_utility.set_location('Entering:'||l_proc, 5);
175   --
176   l_api_updating := ben_art_shd.api_updating
177     (p_age_rt_id                => p_age_rt_id,
178      p_effective_date              => p_effective_date,
179      p_object_version_number       => p_object_version_number);
180   --
181   if (l_api_updating
182       and p_excld_flag
183       <> nvl(ben_art_shd.g_old_rec.excld_flag,hr_api.g_varchar2)
184       or not l_api_updating) then
185     --
186     -- check if value of lookup falls within lookup type.
187     --
188     --
189     if hr_api.not_exists_in_hr_lookups
190           (p_lookup_type    => 'YES_NO',
191            p_lookup_code    => p_excld_flag,
192            p_effective_date => p_effective_date) then
193       --
194       -- raise error as does not exist as lookup
195       --
196       hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
197       hr_utility.raise_error;
198       --
199     end if;
200     --
201   end if;
202   --
203   hr_utility.set_location('Leaving:'||l_proc,10);
204   --
205 end chk_excld_flag;
206 --
207 -- ----------------------------------------------------------------------------
208 -- |-------------------------< chk_age_fctr_id >------------------------------|
209 -- ----------------------------------------------------------------------------
210 --
211 -- Description
212 --   This procedure is used to check that the foreign key for the table
213 --   is created properly. a corresponding record should exist in the
214 --   BEN_AGE_FCTR table.
215 --
216 -- Pre Conditions
217 --   None.
218 --
219 -- In Parameters
220 --   age_fctr_id FK of pointing to BEN_AGE_FCTR tables.
221 --   business_group_id or record being inserted or updated
222 --
223 -- Post Success
224 --   Processing continues
225 --
226 -- Post Failure
227 --   Errors handled by the procedure
228 --
229 -- Access Status
230 --   Internal table handler use only.
231 --
232 Procedure chk_age_fctr_id(p_age_fctr_id      in number,
233                           p_business_group_id    in number) is
234   --
235   l_proc         varchar2(72) := g_package||'chk_age_fctr_id';
236   l_dummy        varchar2(1);
237   cursor c1 is select  null
238                from    BEN_AGE_FCTR
239                where   age_fctr_id = p_age_fctr_id
240                and     business_group_id = p_business_group_id;
241   --
242 
243 Begin
244   --
245   hr_utility.set_location('Entering:'||l_proc, 5);
246   --
247   if p_age_fctr_id is not null then
248      open c1;
249      fetch c1 into l_dummy;
250      if c1%notfound then
251         hr_utility.set_message(801,'Age_Fctr_NotExist');
252         hr_utility.raise_error;
253      end if;
254      close c1;
255   end if;
256   --
257   hr_utility.set_location('Leaving:'||l_proc, 10);
258   --
259 End chk_age_fctr_id;
260 --
261 
262 -- ----------------------------------------------------------------------------
263 -- |------< chk_dup_record >------|
264 -- ----------------------------------------------------------------------------
265 --
266 -- Description
267 --   This procedure is used to check that there is no duplicate record
268 --
269 -- Pre Conditions
270 --   None.
271 --
272 -- In Parameters
273 --   p_age_rt_id             PK of record being inserted or updated.
274 --   p_age_fctr_id           Value of FK
275 --   p_vrbl_rt_prfl_id	     FK of the record
276 --   p_effective_date 	     effective date
277 --   p_object_version_number Object version number of record being
278 --                           inserted or updated.
279 --   p_business_group_id     business_group_id of the record
280 --   p_validation_start_date validation_start_date of record
281 --   p_validation_end_date   validation_end_date of record
282 --
283 -- Post Success
284 --   Processing continues
285 --
286 -- Post Failure
287 --   Error handled by procedure
288 --
289 -- Access Status
290 --   Internal table handler use only.
291 --
292 Procedure chk_dup_record
293 		     (p_age_rt_id             in number,
294                       p_age_fctr_id	      in number,
295 		      p_vrbl_rt_prfl_id	      in number,
296                       p_effective_date        in date,
297                       p_object_version_number in number,
298                       p_business_group_id     in number,
299 		      p_validation_start_date in date,
300 		      p_validation_end_date   in date )
301 is
302 --
303 l_proc         varchar2(72) := g_package||'chk_dup_record';
304 l_api_updating boolean;
305 l_exists       varchar2(1);
306 --
307 cursor c_dup is
308 select null
309 from ben_age_rt_f
310 where age_fctr_id = p_age_fctr_id
311 and vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
312 and age_rt_id <> nvl(p_age_rt_id,hr_api.g_number)
313 and business_group_id + 0 = p_business_group_id
314 and p_validation_start_date <= effective_end_date
315 and p_validation_end_date >= effective_start_date;
316 --
317 BEGIN
318 --
319   hr_utility.set_location('Entering:'||l_proc, 5);
320   --
321   l_api_updating := ben_art_shd.api_updating
322     (p_age_rt_id                   => p_age_rt_id,
323      p_effective_date              => p_effective_date,
324      p_object_version_number       => p_object_version_number);
325   --
326   if (l_api_updating
327       and p_age_fctr_id <> nvl(ben_art_shd.g_old_rec.age_fctr_id,hr_api.g_number)
328       or not l_api_updating) then
329 
330       	open c_dup;
331  	fetch c_dup into l_exists;
332 	if c_dup%found then
333     		close c_dup;
334    		--
335    		-- raise error as this Age criteria already exists for this profile
336    		--
337    		fnd_message.set_name('BEN', 'BEN_92992_DUPS_ROW');
338    		fnd_message.set_token('VAR1','Age criteria',TRUE);
339    		fnd_message.set_token('VAR2','Variable Rate Profile',TRUE);
340    		fnd_message.raise_error;
341   		--
342 	end if;
343 	close c_dup;
344 	--
345   end if;
346   hr_utility.set_location('Leaving:'||l_proc,10);
347   --
348 END chk_dup_record;
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             (p_vrbl_rt_prfl_id               in number default hr_api.g_number,
381 	     p_datetrack_mode		     in varchar2,
382              p_validation_start_date	     in date,
383 	     p_validation_end_date	     in date) Is
384 --
385   l_proc	    varchar2(72) := g_package||'dt_update_validate';
386   l_integrity_error Exception;
387   l_table_name	    all_tables.table_name%TYPE;
388 --
389 Begin
390   hr_utility.set_location('Entering:'||l_proc, 5);
391   --
392   -- Ensure that the p_datetrack_mode argument is not null
393   --
394   hr_api.mandatory_arg_error
395     (p_api_name       => l_proc,
396      p_argument       => 'datetrack_mode',
397      p_argument_value => p_datetrack_mode);
398   --
399   -- Only perform the validation if the datetrack update mode is valid
400   --
401   If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
402     --
403     --
404     -- Ensure the arguments are not null
405     --
406     hr_api.mandatory_arg_error
407       (p_api_name       => l_proc,
408        p_argument       => 'validation_start_date',
409        p_argument_value => p_validation_start_date);
410     --
411     hr_api.mandatory_arg_error
412       (p_api_name       => l_proc,
413        p_argument       => 'validation_end_date',
414        p_argument_value => p_validation_end_date);
415     --
416     If ((nvl(p_vrbl_rt_prfl_id, hr_api.g_number) <> hr_api.g_number) and
417       NOT (dt_api.check_min_max_dates
418             (p_base_table_name => 'ben_vrbl_rt_prfl_f',
419              p_base_key_column => 'vrbl_rt_prfl_id',
420              p_base_key_value  => p_vrbl_rt_prfl_id,
421              p_from_date       => p_validation_start_date,
422              p_to_date         => p_validation_end_date)))  Then
423       l_table_name := 'ben_vrbl_rt_prfl_f';
424       Raise l_integrity_error;
425     End If;
426     --
427   End If;
428   --
429   hr_utility.set_location(' Leaving:'||l_proc, 10);
430 Exception
431   When l_integrity_error Then
432     --
433     -- A referential integrity check was violated therefore
434     -- we must error
435     --
436        ben_utility.parent_integrity_error(p_table_name => l_table_name);
437     --
438   When Others Then
439     --
440     -- An unhandled or unexpected error has occurred which
441     -- we must report
442     --
443     hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
444     hr_utility.set_message_token('PROCEDURE', l_proc);
445     hr_utility.set_message_token('STEP','15');
446     hr_utility.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_age_rt_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       => 'age_rt_id',
526        p_argument_value => p_age_rt_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     --
541   When Others Then
542     --
543     -- An unhandled or unexpected error has occurred which
544     -- we must report
545     --
546     hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
547     hr_utility.set_message_token('PROCEDURE', l_proc);
548     hr_utility.set_message_token('STEP','15');
549     hr_utility.raise_error;
550 End dt_delete_validate;
551 --
552 -- ----------------------------------------------------------------------------
553 -- |---------------------------< insert_validate >----------------------------|
554 -- ----------------------------------------------------------------------------
555 Procedure insert_validate
556 	(p_rec 			 in ben_art_shd.g_rec_type,
557 	 p_effective_date	 in date,
558 	 p_datetrack_mode	 in varchar2,
559 	 p_validation_start_date in date,
560 	 p_validation_end_date	 in date) is
561 --
562   l_proc	varchar2(72) := g_package||'insert_validate';
563 --
564 Begin
565   hr_utility.set_location('Entering:'||l_proc, 5);
566   --
567   -- Call all supporting business operations
568   --
569   --
570   hr_api.validate_bus_grp_id(p_rec.business_group_id);  -- Validate Bus Grp
571   --
572   chk_age_rt_id
573   (p_age_rt_id          => p_rec.age_rt_id,
574    p_effective_date        => p_effective_date,
575    p_object_version_number => p_rec.object_version_number);
576   --
577   chk_excld_flag
578   (p_age_rt_id          => p_rec.age_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_age_fctr_id
584   (p_age_fctr_id          => p_rec.age_fctr_id,
585    p_business_group_id    => p_rec.business_group_id);
586   --
587   chk_dup_record
588   (p_age_rt_id             => p_rec.age_rt_id,
589    p_age_fctr_id	   => p_rec.age_fctr_id,
590    p_vrbl_rt_prfl_id	   => p_rec.vrbl_rt_prfl_id,
591    p_effective_date        => p_effective_date,
592    p_object_version_number => p_rec.object_version_number,
593    p_business_group_id     => p_rec.business_group_id,
594    p_validation_start_date => p_validation_start_date,
595    p_validation_end_date   => p_validation_end_date);
596   --
597 chk_duplicate_ordr_num
598           (p_vrbl_rt_prfl_id      => p_rec.vrbl_rt_prfl_id
599            ,p_ordr_num            => p_rec.ordr_num
600            ,p_effective_date      => p_effective_date
601            ,p_business_group_id   => p_rec.business_group_id
602            ,p_age_rt_id		  => p_rec.age_rt_id);
603 --
604   hr_utility.set_location(' Leaving:'||l_proc, 10);
605 End insert_validate;
606 --
607 -- ----------------------------------------------------------------------------
608 -- |---------------------------< update_validate >----------------------------|
609 -- ----------------------------------------------------------------------------
610 Procedure update_validate
611 	(p_rec 			 in ben_art_shd.g_rec_type,
612 	 p_effective_date	 in date,
613 	 p_datetrack_mode	 in varchar2,
614 	 p_validation_start_date in date,
615 	 p_validation_end_date	 in date) is
616 --
617   l_proc	varchar2(72) := g_package||'update_validate';
618 --
619 Begin
620   hr_utility.set_location('Entering:'||l_proc, 5);
621   --
622   -- Call all supporting business operations
623   --
624   --
625   hr_api.validate_bus_grp_id(p_rec.business_group_id);  -- Validate Bus Grp
626   --
627   chk_age_rt_id
628   (p_age_rt_id          => p_rec.age_rt_id,
629    p_effective_date        => p_effective_date,
630    p_object_version_number => p_rec.object_version_number);
631   --
632   chk_excld_flag
633   (p_age_rt_id          => p_rec.age_rt_id,
634    p_excld_flag         => p_rec.excld_flag,
635    p_effective_date        => p_effective_date,
636    p_object_version_number => p_rec.object_version_number);
637   --
638   chk_age_fctr_id
639   (p_age_fctr_id          => p_rec.age_fctr_id,
640    p_business_group_id    => p_rec.business_group_id);
641   --
642   chk_dup_record
643   (p_age_rt_id             => p_rec.age_rt_id,
644    p_age_fctr_id	   => p_rec.age_fctr_id,
645    p_vrbl_rt_prfl_id	   => p_rec.vrbl_rt_prfl_id,
646    p_effective_date        => p_effective_date,
647    p_object_version_number => p_rec.object_version_number,
648    p_business_group_id     => p_rec.business_group_id,
649    p_validation_start_date => p_validation_start_date,
650    p_validation_end_date   => p_validation_end_date);
651   --
652   -- Call the datetrack update integrity operation
653   --
654   dt_update_validate
655     (
656      p_datetrack_mode                => p_datetrack_mode,
657      p_validation_start_date	     => p_validation_start_date,
658      p_validation_end_date	     => p_validation_end_date);
659   --
660   chk_duplicate_ordr_num
661           (p_vrbl_rt_prfl_id      => p_rec.vrbl_rt_prfl_id
662            ,p_ordr_num            => p_rec.ordr_num
663            ,p_effective_date      => p_effective_date
664            ,p_business_group_id   => p_rec.business_group_id
665            ,p_age_rt_id		  => p_rec.age_rt_id);
666 --
667   hr_utility.set_location(' Leaving:'||l_proc, 10);
668 End update_validate;
669 --
670 -- ----------------------------------------------------------------------------
671 -- |---------------------------< delete_validate >----------------------------|
672 -- ----------------------------------------------------------------------------
673 Procedure delete_validate
674 	(p_rec 			 in ben_art_shd.g_rec_type,
675 	 p_effective_date	 in date,
676 	 p_datetrack_mode	 in varchar2,
677 	 p_validation_start_date in date,
678 	 p_validation_end_date	 in date) is
679 --
680   l_proc	varchar2(72) := g_package||'delete_validate';
681 --
682 Begin
683   hr_utility.set_location('Entering:'||l_proc, 5);
684   --
685   -- Call all supporting business operations
686   --
687   dt_delete_validate
688     (p_datetrack_mode		=> p_datetrack_mode,
689      p_validation_start_date	=> p_validation_start_date,
690      p_validation_end_date	=> p_validation_end_date,
691      p_age_rt_id		=> p_rec.age_rt_id);
692   --
693   hr_utility.set_location(' Leaving:'||l_proc, 10);
694 End delete_validate;
695 --
696 --
697 --  ---------------------------------------------------------------------------
698 --  |---------------------< return_legislation_code >-------------------------|
699 --  ---------------------------------------------------------------------------
700 --
701 function return_legislation_code
702   (p_age_rt_id in number) return varchar2 is
703   --
704   -- Declare cursor
705   --
706   cursor csr_leg_code is
707     select a.legislation_code
708     from   per_business_groups a,
709            ben_age_rt_f b
710     where b.age_rt_id      = p_age_rt_id
711     and   a.business_group_id = b.business_group_id;
712   --
713   -- Declare local variables
714   --
715   l_legislation_code  varchar2(150);
716   l_proc              varchar2(72)  :=  g_package||'return_legislation_code';
717   --
718 begin
719   --
720   hr_utility.set_location('Entering:'|| l_proc, 10);
721   --
722   -- Ensure that all the mandatory parameter are not null
723   --
724   hr_api.mandatory_arg_error(p_api_name       => l_proc,
725                              p_argument       => 'age_rt_id',
726                              p_argument_value => p_age_rt_id);
727   --
728   open csr_leg_code;
729     --
730     fetch csr_leg_code into l_legislation_code;
731     --
732     if csr_leg_code%notfound then
733       --
734       close csr_leg_code;
735       --
736       -- The primary key is invalid therefore we must error
737       --
738       hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
739       hr_utility.raise_error;
740       --
741     end if;
742     --
743   close csr_leg_code;
744   --
745   hr_utility.set_location(' Leaving:'|| l_proc, 20);
746   --
747   return l_legislation_code;
748   --
749 end return_legislation_code;
750 --
751 end ben_art_bus;