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