[Home] [Help]
PACKAGE BODY: APPS.BEN_PBR_BUS
Source
1 Package Body ben_pbr_bus as
2 /* $Header: bepbrrhi.pkb 120.2 2006/03/30 23:50:00 gsehgal noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_pbr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_py_bss_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 -- py_bss_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_py_bss_rt_id(p_py_bss_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_py_bss_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_pbr_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_py_bss_rt_id => p_py_bss_rt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_py_bss_rt_id,hr_api.g_number)
55 <> ben_pbr_shd.g_old_rec.py_bss_rt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_pbr_shd.constraint_error('BEN_PY_BSS_RTS_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_py_bss_rt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_pbr_shd.constraint_error('BEN_PY_BSS_RTS_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_py_bss_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 -- py_bss_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_py_bss_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_pbr_shd.api_updating
118 (p_py_bss_rt_id => p_py_bss_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_pbr_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_pay_basis_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 -- pay_basis_id FK pointing to PER_PAY_BASES table.
161 -- business_group_id of current record
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_pay_basis_id(p_pay_basis_id in number,
173 p_business_group_id in number) is
174 --
175 l_proc varchar2(72) := g_package||'chk_pay_basis_id';
176 l_dummy varchar2(1);
177 cursor c1 is select null
178 from per_pay_bases
179 where pay_basis_id = p_pay_basis_id
180 and business_group_id = p_business_group_id;
181 --
182 Begin
183 --
184 hr_utility.set_location('Entering:'||l_proc, 5);
185 --
186 if p_pay_basis_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,'PayBasis_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_pay_basis_id;
199 --
200 -- ----------------------------------------------------------------------------
201 -- |------< chk_dup_record >------|
202 -- ----------------------------------------------------------------------------
203 --
204 -- Description
205 -- This procedure is used to check that there is no duplicate record
206 --
207 -- Pre Conditions
208 -- None.
209 --
210 -- In Parameters
211 -- p_py_bss_rt_id PK of record being inserted or updated.
212 -- p_pay_basis_id Value of FK
213 -- p_vrbl_rt_prfl_id FK of the record
214 -- p_effective_date effective date
215 -- p_object_version_number Object version number of record being
216 -- inserted or updated.
217 -- p_business_group_id business_group_id of the record
218 -- p_validation_start_date validation_start_date of record
219 -- p_validation_end_date validation_end_date of record
220 --
221 -- Post Success
222 -- Processing continues
223 --
224 -- Post Failure
225 -- Error handled by procedure
226 --
227 -- Access Status
228 -- Internal table handler use only.
229 --
230 Procedure chk_dup_record
231 (p_py_bss_rt_id in number,
232 p_pay_basis_id in number,
233 p_vrbl_rt_prfl_id in number,
234 p_effective_date in date,
235 p_object_version_number in number,
236 p_business_group_id in number,
237 p_validation_start_date in date,
238 p_validation_end_date in date )
239 is
240 --
241 l_proc varchar2(72) := g_package||'chk_dup_record';
242 l_api_updating boolean;
243 l_exists varchar2(1);
244 --
245 cursor c_dup is
246 select null
247 from ben_py_bss_rt_f
248 where pay_basis_id = p_pay_basis_id
249 and vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
250 and py_bss_rt_id <> nvl(p_py_bss_rt_id,hr_api.g_number)
251 and business_group_id + 0 = p_business_group_id
252 and p_validation_start_date <= effective_end_date
253 and p_validation_end_date >= effective_start_date;
254 --
255 BEGIN
256 --
257 hr_utility.set_location('Entering:'||l_proc, 5);
258 --
259 l_api_updating := ben_pbr_shd.api_updating
260 (p_py_bss_rt_id => p_py_bss_rt_id,
261 p_effective_date => p_effective_date,
262 p_object_version_number => p_object_version_number);
263 --
264 if (l_api_updating
265 and p_pay_basis_id <> nvl(ben_pbr_shd.g_old_rec.pay_basis_id,hr_api.g_number)
266 or not l_api_updating) then
267
268 open c_dup;
269 fetch c_dup into l_exists;
270 if c_dup%found then
271 close c_dup;
272 --
273 -- raise error as this Pay Basis criteria already exists for this profile
274 --
275 fnd_message.set_name('BEN', 'BEN_92992_DUPS_ROW');
276 fnd_message.set_token('VAR1','Pay Basis criteria',TRUE);
277 fnd_message.set_token('VAR2','Variable Rate Profile',TRUE);
278 fnd_message.raise_error;
279 --
280 end if;
281 close c_dup;
282 --
283 end if;
284 hr_utility.set_location('Leaving:'||l_proc,10);
285 --
286 END chk_dup_record;
287
288 --
289 -- added for Bug 5078478 .. add this procedure to check the duplicate seq no
290 -- |--------------------< chk_duplicate_ordr_num >----------------------------|
291 -- ----------------------------------------------------------------------------
292 --
293 -- Description
294 --
295 -- Pre Conditions
296 -- None.
297 --
298 -- In Parameters
299 -- p_py_bss_rt_id
300 -- p_vrbl_rt_prfl_id
301 -- p_ordr_num
302 -- p_effective_date
303 -- p_business_group_id
304 --
305 -- Post Success
306 -- Processing continues
307 --
308 -- Post Failure
309 -- Errors handled by the procedure
310 --
311 -- Access Status
312 -- Internal table handler use only.
313 --
314 -- ----------------------------------------------------------------------------
315
316
317 procedure chk_duplicate_ordr_num
318 (p_vrbl_rt_prfl_id in number
319 ,p_py_bss_rt_id in number
320 ,p_ordr_num in number
321 ,p_validation_start_date in date
322 ,p_validation_end_date in date
323 ,p_business_group_id in number)
324 is
325 l_proc varchar2(72) := g_package||' chk_duplicate_ordr_num ';
326 l_dummy char(1);
327 cursor c1 is select null
328 from ben_py_bss_rt_f
329 where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
330 -- changed against bug: 5113011
331 and py_bss_rt_id <> nvl(p_py_bss_rt_id ,-1)
332 -- and pay_basis_id <> nvl(p_pay_basis_id ,-1)
333 --and p_effective_date between effective_start_date
334 -- and effective_end_date
335 and p_validation_start_date <= effective_end_date
336 and p_validation_end_date >= effective_start_date
337 and business_group_id + 0 = p_business_group_id
338 and ordr_num = p_ordr_num;
339 --
340 Begin
341 hr_utility.set_location('Entering:'||l_proc, 5);
342
343 --
344 open c1;
345 fetch c1 into l_dummy;
346 --
347 if c1%found then
348 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
349 fnd_message.raise_error;
350 end if;
351 close c1;
352 --
353 hr_utility.set_location('Leaving:'||l_proc, 15);
354 End chk_duplicate_ordr_num;
355
356
357
358 --
359 -- ----------------------------------------------------------------------------
360 -- |--------------------------< dt_update_validate >--------------------------|
361 -- ----------------------------------------------------------------------------
362 -- {Start Of Comments}
363 --
364 -- Description:
365 -- This procedure is used for referential integrity of datetracked
366 -- parent entities when a datetrack update operation is taking place
367 -- and where there is no cascading of update defined for this entity.
368 --
369 -- Prerequisites:
370 -- This procedure is called from the update_validate.
371 --
372 -- In Parameters:
373 --
374 -- Post Success:
375 -- Processing continues.
376 --
377 -- Post Failure:
378 --
379 -- Developer Implementation Notes:
380 -- This procedure should not need maintenance unless the HR Schema model
381 -- changes.
382 --
383 -- Access Status:
384 -- Internal Row Handler Use Only.
385 --
386 -- {End Of Comments}
387 -- ----------------------------------------------------------------------------
388 Procedure dt_update_validate
389 (p_vrbl_rt_prfl_id in number default hr_api.g_number,
390 p_datetrack_mode in varchar2,
391 p_validation_start_date in date,
392 p_validation_end_date in date) Is
393 --
394 l_proc varchar2(72) := g_package||'dt_update_validate';
395 l_integrity_error Exception;
396 l_table_name all_tables.table_name%TYPE;
400 --
397 --
398 Begin
399 hr_utility.set_location('Entering:'||l_proc, 5);
401 -- Ensure that the p_datetrack_mode argument is not null
402 --
403 hr_api.mandatory_arg_error
404 (p_api_name => l_proc,
405 p_argument => 'datetrack_mode',
406 p_argument_value => p_datetrack_mode);
407 --
408 -- Only perform the validation if the datetrack update mode is valid
409 --
410 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
411 --
412 --
413 -- Ensure the arguments are not null
414 --
415 hr_api.mandatory_arg_error
416 (p_api_name => l_proc,
417 p_argument => 'validation_start_date',
418 p_argument_value => p_validation_start_date);
419 --
420 hr_api.mandatory_arg_error
421 (p_api_name => l_proc,
422 p_argument => 'validation_end_date',
423 p_argument_value => p_validation_end_date);
424 --
425 If ((nvl(p_vrbl_rt_prfl_id, hr_api.g_number) <> hr_api.g_number) and
426 NOT (dt_api.check_min_max_dates
427 (p_base_table_name => 'ben_vrbl_rt_prfl_f',
428 p_base_key_column => 'vrbl_rt_prfl_id',
429 p_base_key_value => p_vrbl_rt_prfl_id,
430 p_from_date => p_validation_start_date,
431 p_to_date => p_validation_end_date))) Then
432 l_table_name := 'ben_vrbl_rt_prfl_f';
433 Raise l_integrity_error;
434 End If;
435 --
436 End If;
437 --
438 hr_utility.set_location(' Leaving:'||l_proc, 10);
439 Exception
440 When l_integrity_error Then
441 --
442 -- A referential integrity check was violated therefore
443 -- we must error
444 --
445 ben_utility.parent_integrity_error (p_table_name => l_table_name);
446 --
447 When Others Then
448 --
449 -- An unhandled or unexpected error has occurred which
450 -- we must report
451 --
452 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
453 hr_utility.set_message_token('PROCEDURE', l_proc);
454 hr_utility.set_message_token('STEP','15');
455 hr_utility.raise_error;
456 End dt_update_validate;
457 --
458 -- ----------------------------------------------------------------------------
459 -- |--------------------------< dt_delete_validate >--------------------------|
460 -- ----------------------------------------------------------------------------
461 -- {Start Of Comments}
462 --
463 -- Description:
464 -- This procedure is used for referential integrity of datetracked
465 -- child entities when either a datetrack DELETE or ZAP is in operation
466 -- and where there is no cascading of delete defined for this entity.
467 -- For the datetrack mode of DELETE or ZAP we must ensure that no
468 -- datetracked child rows exist between the validation start and end
469 -- dates.
470 --
471 -- Prerequisites:
472 -- This procedure is called from the delete_validate.
473 --
474 -- In Parameters:
475 --
476 -- Post Success:
477 -- Processing continues.
478 --
479 -- Post Failure:
480 -- If a row exists by determining the returning Boolean value from the
481 -- generic dt_api.rows_exist function then we must supply an error via
482 -- the use of the local exception handler l_rows_exist.
483 --
484 -- Developer Implementation Notes:
485 -- This procedure should not need maintenance unless the HR Schema model
486 -- changes.
487 --
488 -- Access Status:
489 -- Internal Row Handler Use Only.
490 --
491 -- {End Of Comments}
492 -- ----------------------------------------------------------------------------
493 Procedure dt_delete_validate
494 (p_py_bss_rt_id in number,
495 p_datetrack_mode in varchar2,
496 p_validation_start_date in date,
497 p_validation_end_date in date) Is
498 --
499 l_proc varchar2(72) := g_package||'dt_delete_validate';
500 l_rows_exist Exception;
501 l_table_name all_tables.table_name%TYPE;
502 --
503 Begin
504 hr_utility.set_location('Entering:'||l_proc, 5);
505 --
506 -- Ensure that the p_datetrack_mode argument is not null
507 --
508 hr_api.mandatory_arg_error
509 (p_api_name => l_proc,
510 p_argument => 'datetrack_mode',
511 p_argument_value => p_datetrack_mode);
512 --
513 -- Only perform the validation if the datetrack mode is either
514 -- DELETE or ZAP
515 --
516 If (p_datetrack_mode = 'DELETE' or
517 p_datetrack_mode = 'ZAP') then
518 --
519 --
520 -- Ensure the arguments are not null
521 --
522 hr_api.mandatory_arg_error
523 (p_api_name => l_proc,
524 p_argument => 'validation_start_date',
525 p_argument_value => p_validation_start_date);
526 --
527 hr_api.mandatory_arg_error
528 (p_api_name => l_proc,
529 p_argument => 'validation_end_date',
530 p_argument_value => p_validation_end_date);
531 --
532 hr_api.mandatory_arg_error
533 (p_api_name => l_proc,
534 p_argument => 'py_bss_rt_id',
538 --
535 p_argument_value => p_py_bss_rt_id);
536 --
537 --
539 End If;
540 --
541 hr_utility.set_location(' Leaving:'||l_proc, 10);
542 Exception
543 When l_rows_exist Then
544 --
545 -- A referential integrity check was violated therefore
546 -- we must error
547 --
548 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
549 hr_utility.set_message_token('TABLE_NAME', l_table_name);
550 hr_utility.raise_error;
551 When Others Then
552 --
553 -- An unhandled or unexpected error has occurred which
554 -- we must report
555 --
556 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
557 hr_utility.set_message_token('PROCEDURE', l_proc);
558 hr_utility.set_message_token('STEP','15');
559 hr_utility.raise_error;
560 End dt_delete_validate;
561 --
562 -- ----------------------------------------------------------------------------
563 -- |---------------------------< insert_validate >----------------------------|
564 -- ----------------------------------------------------------------------------
565 Procedure insert_validate
566 (p_rec in ben_pbr_shd.g_rec_type,
567 p_effective_date in date,
568 p_datetrack_mode in varchar2,
569 p_validation_start_date in date,
570 p_validation_end_date in date) is
571 --
572 l_proc varchar2(72) := g_package||'insert_validate';
573 --
574 Begin
575 hr_utility.set_location('Entering:'||l_proc, 5);
576 --
577 -- Call all supporting business operations
578 --
579 --
580 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
581 --
582 chk_py_bss_rt_id
583 (p_py_bss_rt_id => p_rec.py_bss_rt_id,
584 p_effective_date => p_effective_date,
585 p_object_version_number => p_rec.object_version_number);
586 --
587 chk_excld_flag
588 (p_py_bss_rt_id => p_rec.py_bss_rt_id,
589 p_excld_flag => p_rec.excld_flag,
590 p_effective_date => p_effective_date,
591 p_object_version_number => p_rec.object_version_number);
592 --
593 chk_pay_basis_id(p_pay_basis_id => p_rec.pay_basis_id,
594 p_business_group_id => p_rec.business_group_id);
595 --
596 chk_dup_record
597 (p_py_bss_rt_id => p_rec.py_bss_rt_id,
598 p_pay_basis_id => p_rec.pay_basis_id,
599 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
600 p_effective_date => p_effective_date,
601 p_object_version_number => p_rec.object_version_number,
602 p_business_group_id => p_rec.business_group_id,
603 p_validation_start_date => p_validation_start_date,
604 p_validation_end_date => p_validation_end_date);
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_py_bss_rt_id => p_rec.py_bss_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_pbr_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_py_bss_rt_id
640 (p_py_bss_rt_id => p_rec.py_bss_rt_id,
641 p_effective_date => p_effective_date,
642 p_object_version_number => p_rec.object_version_number);
643 --
644 chk_excld_flag
645 (p_py_bss_rt_id => p_rec.py_bss_rt_id,
646 p_excld_flag => p_rec.excld_flag,
647 p_effective_date => p_effective_date,
648 p_object_version_number => p_rec.object_version_number);
649 --
650 chk_pay_basis_id(p_pay_basis_id => p_rec.pay_basis_id,
651 p_business_group_id => p_rec.business_group_id);
652 --
653 chk_dup_record
654 (p_py_bss_rt_id => p_rec.py_bss_rt_id,
655 p_pay_basis_id => p_rec.pay_basis_id,
656 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
657 p_effective_date => p_effective_date,
658 p_object_version_number => p_rec.object_version_number,
659 p_business_group_id => p_rec.business_group_id,
660 p_validation_start_date => p_validation_start_date,
661 p_validation_end_date => p_validation_end_date);
662 --
663 -- added for Bug 5078478 .. add this procedure to check the duplicate seq no
664 chk_duplicate_ordr_num
665 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
666 ,p_py_bss_rt_id => p_rec.py_bss_rt_id
667 ,p_ordr_num => p_rec.ordr_num
668 ,p_validation_start_date => p_validation_start_date
669 ,p_validation_end_date => p_validation_end_date
670 ,p_business_group_id => p_rec.business_group_id);
671
672 --
673 -- Call the datetrack update integrity operation
674
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_pbr_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_py_bss_rt_id => p_rec.py_bss_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_py_bss_rt_id in number) return varchar2 is
718 --
719 -- Declare cursor
723 from per_business_groups a,
720 --
721 cursor csr_leg_code is
722 select a.legislation_code
724 ben_py_bss_rt_f b
725 where b.py_bss_rt_id = p_py_bss_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 => 'py_bss_rt_id',
741 p_argument_value => p_py_bss_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_pbr_bus;