[Home] [Help]
PACKAGE BODY: APPS.BEN_PPV_BUS
Source
1 Package Body ben_ppv_bus as
2 /* $Header: beppvrhi.pkb 120.5.12010000.2 2008/08/05 15:17:18 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ppv_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_prtl_mo_rt_prtn_val_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 -- prtl_mo_rt_prtn_val_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_prtl_mo_rt_prtn_val_id(p_prtl_mo_rt_prtn_val_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_prtl_mo_rt_prtn_val_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_ppv_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_prtl_mo_rt_prtn_val_id => p_prtl_mo_rt_prtn_val_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_prtl_mo_rt_prtn_val_id,hr_api.g_number)
55 <> ben_ppv_shd.g_old_rec.prtl_mo_rt_prtn_val_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_ppv_shd.constraint_error('BEN_PRTL_MO_RT_PRTN_VAL_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_prtl_mo_rt_prtn_val_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_ppv_shd.constraint_error('BEN_PRTL_MO_RT_PRTN_VAL_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_prtl_mo_rt_prtn_val_id;
78 --
79 procedure chk_dup_prorate_by_day_row(p_effective_date in date,
80 p_acty_base_rt_id in number,
81 p_mode in varchar2,
82 p_prtl_mo_rt_prtn_val_id in number default null)
83 is
84 --
85 cursor c1 is
86 select null
87 from ben_prtl_mo_rt_prtn_val_f ppv
88 where ppv.PRORATE_BY_DAY_TO_MON_FLAG = 'Y'
89 and ppv.acty_base_rt_id = p_acty_base_rt_id
90 and p_effective_date between ppv.effective_start_date
91 and ppv.effective_end_date;
92 --
93 cursor c2 is
94 select null
95 from ben_prtl_mo_rt_prtn_val_f ppv
96 where ppv.PRORATE_BY_DAY_TO_MON_FLAG = 'Y'
97 and ppv.acty_base_rt_id = p_acty_base_rt_id
98 and ppv.prtl_mo_rt_prtn_val_id <> p_prtl_mo_rt_prtn_val_id
99 and p_effective_date between ppv.effective_start_date
100 and ppv.effective_end_date;
101 --
102 l_dummy varchar2(30);
103
104 begin
105 --
106 if p_mode = 'Insert' then
107 open c1;
108 fetch c1 into l_dummy;
109 if c1%found then
110 --
111 close c1;
112 --
113 fnd_message.set_name('BEN','BEN_94261_PPV_DUP');
114 fnd_message.raise_error;
115 --
116 else
117 --
118 close c1;
119 --
120 end if;
121 elsif p_mode = 'Update' then
122 --
123 open c2;
124 fetch c2 into l_dummy;
125 if c2%found then
126 --
127 close c2;
128 --
129 fnd_message.set_name('BEN','BEN_94261_PPV_DUP');
130 fnd_message.raise_error;
131 --
132 else
133 --
134 close c2;
135 --
136 end if;
137 end if;
138 --
139 end;
140
141
142 -- ----------------------------------------------------------------------------
143 -- |------< chk_rndg_cd >------|
144 -- ----------------------------------------------------------------------------
145 --
146 -- Description
147 -- This procedure is used to check that the lookup value is valid.
148 --
149 -- Pre Conditions
150 -- None.
151 --
152 -- In Parameters
153 -- prtl_mo_rt_prtn_val_id PK of record being inserted or updated.
154 -- rndg_cd Value of lookup code.
155 -- effective_date effective date
156 -- object_version_number Object version number of record being
157 -- inserted or updated.
158 --
159 -- Post Success
160 -- Processing continues
161 --
162 -- Post Failure
163 -- Error handled by procedure
164 --
165 -- Access Status
166 -- Internal table handler use only.
167 --
168 Procedure chk_rndg_cd(p_prtl_mo_rt_prtn_val_id in number,
169 p_rndg_cd in varchar2,
170 p_effective_date in date,
171 p_object_version_number in number) is
172 --
173 l_proc varchar2(72) := g_package||'chk_rndg_cd';
174 l_api_updating boolean;
175 --
176 Begin
177 --
178 hr_utility.set_location('Entering:'||l_proc, 5);
179 --
180 l_api_updating := ben_ppv_shd.api_updating
181 (p_prtl_mo_rt_prtn_val_id => p_prtl_mo_rt_prtn_val_id,
182 p_effective_date => p_effective_date,
183 p_object_version_number => p_object_version_number);
184 --
185 if (l_api_updating
186 and p_rndg_cd
187 <> nvl(ben_ppv_shd.g_old_rec.rndg_cd,hr_api.g_varchar2)
188 or not l_api_updating)
189 and p_rndg_cd is not null then
190 --
191 -- check if value of lookup falls within lookup type.
192 --
193 if hr_api.not_exists_in_hr_lookups
194 (p_lookup_type => 'BEN_RNDG',
195 p_lookup_code => p_rndg_cd,
196 p_effective_date => p_effective_date) then
197 --
198 -- raise error as does not exist as lookup
199 --
200 hr_utility.set_message('BEN','BEN_91041_INVALID_RNDG_CD');
201 hr_utility.raise_error;
202 --
203 end if;
204 --
205 end if;
206 --
207 hr_utility.set_location('Leaving:'||l_proc,10);
208 --
209 end chk_rndg_cd;
210 --
211 -- ----------------------------------------------------------------------------
212 -- |------< chk_prtl_mo_prortn_rl >------|
213 -- ----------------------------------------------------------------------------
214 --
215 -- Description
216 -- This procedure is used to check that the Formula Rule is valid.
217 --
218 -- Pre Conditions
219 -- None.
220 --
221 -- In Parameters
222 -- prtl_mo_rt_prtn_val_id PK of record being inserted or updated.
223 -- prtl_mo_prortn_rl Value of formula rule id.
224 -- effective_date effective date
225 -- object_version_number Object version number of record being
226 -- inserted or updated.
227 --
228 -- Post Success
229 -- Processing continues
230 --
231 -- Post Failure
232 -- Error handled by procedure
233 --
234 -- Access Status
235 -- Internal table handler use only.
236 --
237 Procedure chk_prtl_mo_prortn_rl(p_prtl_mo_rt_prtn_val_id in number,
238 p_business_group_id in number,
239 p_prtl_mo_prortn_rl in number,
240 p_effective_date in date,
241 p_object_version_number in number) is
242 --
243 l_proc varchar2(72) := g_package||'chk_prtl_mo_prortn_rl';
244 l_api_updating boolean;
245 l_dummy varchar2(1);
246 --
247 cursor c1 is
248 select null
249 from ff_formulas_f ff
250 ,per_business_groups pbg
251 where ff.formula_id = p_prtl_mo_prortn_rl
252 and ff.formula_type_id = -528
253 and pbg.business_group_id = p_business_group_id
254 and nvl(ff.business_group_id, p_business_group_id) =
255 p_business_group_id
256 and nvl(ff.legislation_code, pbg.legislation_code) =
257 pbg.legislation_code
258 and p_effective_date
259 between ff.effective_start_date
260 and ff.effective_end_date;
261 --
262 Begin
263 --
264 hr_utility.set_location('Entering:'||l_proc, 5);
265 --
266 l_api_updating := ben_ppv_shd.api_updating
267 (p_prtl_mo_rt_prtn_val_id => p_prtl_mo_rt_prtn_val_id,
268 p_effective_date => p_effective_date,
269 p_object_version_number => p_object_version_number);
270 --
271 if (l_api_updating
272 and nvl(p_prtl_mo_prortn_rl,hr_api.g_number)
273 <> ben_ppv_shd.g_old_rec.prtl_mo_prortn_rl
274 or not l_api_updating)
275 and p_prtl_mo_prortn_rl is not null then
276 --
277 -- check if value of formula rule is valid.
278 --
279 open c1;
280 --
281 -- fetch value from cursor if it returns a record then the
282 -- formula is valid otherwise its invalid
283 --
284 fetch c1 into l_dummy;
285 if c1%notfound then
286 --
287 close c1;
288 --
289 -- raise error
290 --
291 --
292 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
293 fnd_message.set_token('ID',p_prtl_mo_prortn_rl);
294 fnd_message.set_token('TYPE_ID',-528);
295 fnd_message.raise_error;
296 --
297 end if;
298 --
299 close c1;
300 --
301 end if;
302 --
303 hr_utility.set_location('Leaving:'||l_proc,10);
304 --
305 end chk_prtl_mo_prortn_rl;
306 --
307 -- ----------------------------------------------------------------------------
308 -- |------< chk_rndg_rl >------|
309 -- ----------------------------------------------------------------------------
310 --
311 -- Description
312 -- This procedure is used to check that the Formula Rule is valid.
313 --
314 -- Pre Conditions
315 -- None.
316 --
317 -- In Parameters
318 -- prtl_mo_rt_prtn_val_id PK of record being inserted or updated.
319 -- rndg_rl Value of formula rule id.
320 -- effective_date effective date
321 -- object_version_number Object version number of record being
322 -- inserted or updated.
323 --
324 -- Post Success
325 -- Processing continues
326 --
327 -- Post Failure
328 -- Error handled by procedure
329 --
330 -- Access Status
331 -- Internal table handler use only.
332 --
333 Procedure chk_rndg_rl(p_prtl_mo_rt_prtn_val_id in number,
334 p_rndg_rl in number,
335 p_effective_date in date,
336 p_object_version_number in number) is
337 --
338 l_proc varchar2(72) := g_package||'chk_rndg_rl';
339 l_api_updating boolean;
340 l_dummy varchar2(1);
341 --
342 cursor c1 is
343 select null
344 from ff_formulas_f ff
345 where ff.formula_id = p_rndg_rl
346 and p_effective_date
347 between ff.effective_start_date
348 and ff.effective_end_date;
349 --
350 Begin
351 --
352 hr_utility.set_location('Entering:'||l_proc, 5);
353 --
354 l_api_updating := ben_ppv_shd.api_updating
355 (p_prtl_mo_rt_prtn_val_id => p_prtl_mo_rt_prtn_val_id,
356 p_effective_date => p_effective_date,
357 p_object_version_number => p_object_version_number);
358 --
359 if (l_api_updating
360 and nvl(p_rndg_rl,hr_api.g_number)
361 <> ben_ppv_shd.g_old_rec.rndg_rl
362 or not l_api_updating)
363 and p_rndg_rl is not null then
364 --
365 -- check if value of formula rule is valid.
366 --
367 open c1;
368 --
369 -- fetch value from cursor if it returns a record then the
370 -- formula is valid otherwise its invalid
371 --
372 fetch c1 into l_dummy;
373 if c1%notfound then
374 --
375 close c1;
376 --
377 -- raise error
378 --
379 hr_utility.set_message('BEN','BEN_91042_INVALID_RNDG_RL');
380 hr_utility.raise_error;
381 --
382 end if;
383 --
384 close c1;
385 --
386 -- Unless Rounding Code = Rule, Rounding rule must be blank.
387 -- if nvl(p_rndg_cd,hr_api.g_varchar2) <> 'RL' and p_rndg_rl is not null then
388 --
389 -- fnd_message.set_name('BEN', 'BEN_91043_RNDG_RL_NOT_NULL');
390 -- fnd_message.raise_error;
391 --
392 -- elsif nvl(p_rndg_cd,hr_api.g_varchar2) = 'RL' and p_rndg_rl is null then
393 --
394 -- fnd_message.set_name('BEN', 'BEN_92340_RNDG_RL_NULL');
395 -- fnd_message.raise_error;
396 --
397 -- end if;
398 --
399 end if;
400 --
401 hr_utility.set_location('Leaving:'||l_proc,10);
402 --
403 end chk_rndg_rl;
404 --
405 -- ----------------------------------------------------------------------------
406 -- |--------------------< chk_rndg_cd_rl >-------------------------------|
407 -- ----------------------------------------------------------------------------
408 --
409 -- Description
410 -- This procedure is used to check that the code/rule dependency as the
411 -- following:
412 -- If Code = 'Rule' then rule must be selected.
413 -- If Code <> 'Rule' thne rule must not be selected.
414 --
415 -- Pre Conditions
416 -- None.
417 --
418 -- In Parameters
419 -- rndg_cd Value of look up value.
420 -- rndg_rl value of look up Value
421 -- inserted or updated.
422 --
423 -- Post Success
424 -- Processing continues
425 --
426 --
427 -- Post Failure
428 -- Error handled by procedure
429 --
430 -- Access Status
431 -- Internal table handler use only.
432 --
433 Procedure chk_rndg_cd_rl(p_rndg_cd in varchar2,
434 p_rndg_rl in number) is
435 --
436 l_proc varchar2(72) := g_package||'chk_rndg_cd_rl';
437 --
438 Begin
439 --
440 hr_utility.set_location('Entering:'||l_proc, 5);
441 --
442 if (p_rndg_cd <> 'RL' and p_rndg_rl is not null)
443 then
444 fnd_message.set_name('BEN', 'BEN_91043_RNDG_RL_NOT_NULL');
445 fnd_message.raise_error;
446 end if;
447
448 if (p_rndg_cd = 'RL' and p_rndg_rl is null)
449 then
450 fnd_message.set_name('BEN', 'BEN_92340_RNDG_RL_NULL');
451 fnd_message.raise_error;
452 end if;
453 --
454 -- Leaving Procedure.
455 --
456 hr_utility.set_location('Leaving:'||l_proc,10);
457 --
461 -- |------< chk_strt_r_stp_cvg_cd >------|
458 end chk_rndg_cd_rl;
459 --
460 -- ----------------------------------------------------------------------------
462 -- ----------------------------------------------------------------------------
463 --
464 -- Description
465 -- This procedure is used to check that the lookup value is valid.
466 --
467 -- Pre Conditions
468 -- None.
469 --
470 -- In Parameters
471 -- prtl_mo_rt_prtn_val_id PK of record being inserted or updated.
472 -- strt_r_stp_cvg_cd Value of lookup code.
473 -- effective_date effective date
474 -- object_version_number Object version number of record being
475 -- inserted or updated.
476 --
477 -- Post Success
478 -- Processing continues
479 --
480 -- Post Failure
481 -- Error handled by procedure
482 --
483 -- Access Status
484 -- Internal table handler use only.
485 --
486 Procedure chk_strt_r_stp_cvg_cd(p_prtl_mo_rt_prtn_val_id in number,
487 p_strt_r_stp_cvg_cd in varchar2,
488 p_effective_date in date,
489 p_object_version_number in number) is
490 --
491 l_proc varchar2(72) := g_package||'chk_strt_r_stp_cvg_cd';
492 l_api_updating boolean;
493 --
494 Begin
495 --
496 hr_utility.set_location('Entering:'||l_proc, 5);
497 --
498 l_api_updating := ben_ppv_shd.api_updating
499 (p_prtl_mo_rt_prtn_val_id => p_prtl_mo_rt_prtn_val_id,
500 p_effective_date => p_effective_date,
501 p_object_version_number => p_object_version_number);
502 --
503 if (l_api_updating
504 and p_strt_r_stp_cvg_cd
505 <> nvl(ben_ppv_shd.g_old_rec.strt_r_stp_cvg_cd,hr_api.g_varchar2)
506 or not l_api_updating)
507 and p_strt_r_stp_cvg_cd is not null then
508 --
509 -- check if value of lookup falls within lookup type.
510 --
511 if hr_api.not_exists_in_hr_lookups
512 (p_lookup_type => 'BEN_STRT_R_STP_CVG',
513 p_lookup_code => p_strt_r_stp_cvg_cd,
514 p_effective_date => p_effective_date) then
515 --
516 -- raise error as does not exist as lookup
517 --
518 fnd_message.set_name('BEN','BEN_92183_INL_SRT_R_ST_CVG_CD');
519 fnd_message.raise_error;
520 --
521 end if;
522 --
523 end if;
524 --
525 hr_utility.set_location('Leaving:'||l_proc,10);
526 --
527 end chk_strt_r_stp_cvg_cd;
528 --
529 -- ----------------------------------------------------------------------------
530 -- |--------------------< chk_prortn_rl_pct_val >------------------------------|
531 -- ----------------------------------------------------------------------------
532 --
533 -- Description
534 -- This procedure is used to check the prtl_mo_prortn_rl and pct_val
535 -- If Rule is not null then val must not be null.
536 -- If Rule is null then val must be null.
537 --
538 -- Pre Conditions
539 -- None.
540 --
541 -- In Parameters
542 -- p_prtl_mo_prortn_rl
543 -- pct_val
544 --
545 -- Post Success
546 -- Processing continues
547 --
548 --
549 -- Post Failure
550 -- Error handled by procedure
551 --
552 -- Access Status
553 -- Internal table handler use only.
554 --
555 Procedure chk_prortn_rl_pct_val (p_prtl_mo_prortn_rl in number,
556 p_pct_val in number,
557 p_prorate_by_day_to_mon_flag in varchar2,
558 p_prtl_mo_rt_prtn_val_id in number,
559 p_acty_base_rt_id in number,
560 p_actl_prem_id in number,
561 p_business_group_id in number,
562 p_validation_start_date in date,
563 p_validation_end_date in date) is
564 --
565 l_proc varchar2(72) := g_package||'chk_prortn_rl_pct_val' ;
566 l_dummy varchar2(10);
567 --
568 -- Bug No 4418762 Added cursor to ensure no overlap of
569 -- prorate on day/month basis and percent/rule defined
570 --
571 cursor chk_overlap is
572 select 'Y'
573 from ben_prtl_mo_rt_prtn_val_f
574 Where prtl_mo_rt_prtn_val_id <> nvl(p_prtl_mo_rt_prtn_val_id,-1)
575 and (acty_base_rt_id = p_acty_base_rt_id or
576 actl_prem_id = p_actl_prem_id) -- Bug 4440097
577 and prorate_by_day_to_mon_flag = 'Y'
578 --Bug 6242951
579 /* and p_validation_start_date <= effective_end_date
580 and p_validation_end_date >= effective_start_date
581 */
582 and not ((p_validation_end_date < effective_start_date)
583 or (p_validation_start_date > effective_end_date))
584 --Bug 6242951
585 and business_group_id = p_business_group_id;
586 Begin
587 --
588 hr_utility.set_location('Entering:'||l_proc, 5);
589 --
590 -- Bug No 4419185
591 --
592 if (p_pct_val <0 or p_pct_val > 100) then
593 fnd_message.set_name('BEN', 'BEN_94263_INVALID_PCT_VAL');
597 if (p_prtl_mo_prortn_rl is not null and p_pct_val is not null
594 fnd_message.raise_error;
595 end if;
596 --
598 and p_prorate_by_day_to_mon_flag = 'N')
599 then
600 fnd_message.set_name('BEN', 'BEN_92267_PRORTN_RL_PCT_VAL1');
601 fnd_message.raise_error;
602 end if;
603 --
604 if (p_prtl_mo_prortn_rl is null and p_pct_val is null and
605 p_prorate_by_day_to_mon_flag = 'N') then
606 fnd_message.set_name('BEN', 'BEN_92268_PRORTN_RL_PCT_VAL2');
607 fnd_message.raise_error;
608 end if;
609 --
610 -- Bug No 4418762
611 --
612 if (p_prtl_mo_prortn_rl is not null or p_pct_val is not null) then
613 open chk_overlap;
614 fetch chk_overlap into l_dummy;
615 if chk_overlap%FOUND then
616 fnd_message.set_name('BEN', 'BEN_94262_PRORATE_FLAG_PCT_VAL');
617 fnd_message.raise_error;
618 end if;
619 close chk_overlap;
620 end if;
621 --
622 -- Leaving Procedure.
623 --
624 hr_utility.set_location('Leaving:'||l_proc,10);
625 --
626 end chk_prortn_rl_pct_val ;
627 --
628 -- ----------------------------------------------------------------------------
629 -- |---------------------------< chk_from_dt_to_dy >--------------------------|
630 -- ----------------------------------------------------------------------------
631 --
632 -- Description
633 -- This procedure is used to check if the min value is
634 -- less than the max value.
635 --
636 -- Pre Conditions
637 -- None.
638 --
639 -- In Parameters
640 -- p_from_dy_mo_num minimum value
641 -- p_to_dy_mo_num maximum value
642 --
643 -- Post Success
644 -- Processing continues
645 --
646 -- Post Failure
647 -- Error handled by procedure
648 --
649 -- Access Status
650 -- Internal table handler use only.
651 --
652 Procedure chk_from_dt_to_dy(p_from_dy_mo_num in number,
653 p_to_dy_mo_num in number) is
654 --
655 l_proc varchar2(72) := g_package||'chk_from_dt_to_dy';
656 --
657 Begin
658 --
659 hr_utility.set_location('Entering:'||l_proc, 5);
660 --
661 -- check the values
662 -- note: Don't want an error if either one is null
663 --
664 if p_from_dy_mo_num is not null and p_to_dy_mo_num is not null
665 and (p_from_dy_mo_num > p_to_dy_mo_num) then
666 --
667 -- raise error as is not a valid combination
668 --
669 fnd_message.set_name('BEN','BEN_92269_MIN_LESS_NOT_EQ_MAX');
670 fnd_message.raise_error;
671 --
672 end if;
673 --
674 hr_utility.set_location('Leaving:'||l_proc, 5);
675 --
676 end chk_from_dt_to_dy;
677 --
678 -- ---------------------------------------------------------------------------
679 -- |-------------< chk_unique_and_not_overlapping >---------------------------|
680 -- ----------------------------------------------------------------------------
681 --
682 -- Description
683 -- ensure that the combination of Start or Stop Code, From Day of Month,
684 -- and To Day of Month fields are unique and that the From Day
685 -- of Month and To Day of Month fields do not overlap
686 -- within business_group
687 --
688 -- Pre Conditions
689 -- None.
690 --
691 -- In Parameters
692 -- p_prtl_mo_rt_prtn_val_id is primary key
693 -- p_from_dy_mo_num From Day of Month
694 -- p_to_dy_mo_num To Day of Month
695 -- p_strt_r_stp_cvg_cd Start or Stop Code
696 -- p_business_group_id
697 --
698 -- Post Success
699 -- Processing continues
700 --
701 -- Post Failure
702 -- Errors handled by the procedure
703 --
704 -- Access Status
705 -- Internal table handler use only.
706 --
707 -- ----------------------------------------------------------------------------
708 Procedure chk_unique_and_not_overlapping
709 ( p_prtl_mo_rt_prtn_val_id in number
710 ,p_acty_base_rt_id in number
711 ,p_from_dy_mo_num in number
712 ,p_to_dy_mo_num in number
713 ,p_strt_r_stp_cvg_cd in varchar2
714 ,p_num_days_month in number -- Bug No 4366086
715 ,p_business_group_id in number
716 ,p_effective_date in date
717 ,p_validation_start_date in date
718 ,p_validation_end_date in date)
719 is
720 --
721 l_proc varchar2(72) := g_package||'chk_unique_and_not_overlapping';
722 l_from_day number(2);
723 l_to_day number(2);
724 l_strt_r_stp_cd varchar2(5);
725 --
726 cursor c1 is select from_dy_mo_num, to_dy_mo_num
727 from ben_prtl_mo_rt_prtn_val_f
728 Where prtl_mo_rt_prtn_val_id <> nvl(p_prtl_mo_rt_prtn_val_id,-1)
729 and acty_base_rt_id = p_acty_base_rt_id
730 and strt_r_stp_cvg_cd = p_strt_r_stp_cvg_cd
731 and num_days_month = p_num_days_month -- Bug No 4366086
732 -- and p_effective_date between effective_start_date
733 -- and effective_end_date
737 --
734 and p_validation_start_date <= effective_end_date
735 and p_validation_end_date >= effective_start_date
736 and business_group_id = p_business_group_id;
738 cursor c2 is select strt_r_stp_cvg_cd
739 from ben_prtl_mo_rt_prtn_val_f
740 Where prtl_mo_rt_prtn_val_id <> nvl(p_prtl_mo_rt_prtn_val_id,-1)
741 and acty_base_rt_id = p_acty_base_rt_id
742 and num_days_month = p_num_days_month -- Bug No 4366086
743 -- and p_effective_date between effective_start_date
744 -- and effective_end_date
745 and p_validation_start_date <= effective_end_date
746 and p_validation_end_date >= effective_start_date
747 and business_group_id = p_business_group_id;
748 --
749 Begin
750 hr_utility.set_location('Entering:'||l_proc, 5);
751 --
752 -- Bug 4366086 Added if condition to check that to_dy_mo_num
753 -- field is not greater than num_day_month
754 --
755 if (p_to_dy_mo_num > p_num_days_month) then
756 --
757 fnd_message.set_name('BEN','BEN_94247_INVALID_TO_DATE');
758 fnd_message.raise_error;
759 --
760 end if;
761 --
762 open c1;
763 fetch c1 into l_from_day, l_to_day;
764 if c1%found then
765 close c1;
766 -- Bug 4366086 : Handled complete overlap cases
767 if ((p_from_dy_mo_num <= l_to_day) and (p_to_dy_mo_num >= l_from_day)) then
768 --
769 fnd_message.set_name('BEN','BEN_92305_MAY_NOT_OVERLAP');
770 fnd_message.raise_error;
771 --
772 end if;
773 end if;
774 --
775 open c2;
776 fetch c2 into l_strt_r_stp_cd;
777 if c2%found then
778 close c2;
779 if l_strt_r_stp_cd = 'STRT' or l_strt_r_stp_cd = 'STP' then
780 if p_strt_r_stp_cvg_cd = 'ETHR' then
781 --
782 fnd_message.set_name('BEN','BEN_92306_MAY_NOT_SELECT_ETHR');
783 fnd_message.raise_error;
784 --
785 end if;
786 elsif l_strt_r_stp_cd = 'ETHR' then
787 if p_strt_r_stp_cvg_cd = 'STRT' or p_strt_r_stp_cvg_cd = 'STP' then
788 --
789 fnd_message.set_name('BEN','BEN_92307_CANNOT_PICK_STRT_STP');
790 fnd_message.raise_error;
791 --
792 end if;
793 end if;
794 end if;
795 --
796 hr_utility.set_location('Leaving:'||l_proc, 15);
797 End chk_unique_and_not_overlapping;
798 --
799 -- ----------------------------------------------------------------------------
800 -- |--------------------------< dt_update_validate >--------------------------|
801 -- ----------------------------------------------------------------------------
802 -- {Start Of Comments}
803 --
804 -- Description:
805 -- This procedure is used for referential integrity of datetracked
806 -- parent entities when a datetrack update operation is taking place
807 -- and where there is no cascading of update defined for this entity.
808 --
809 -- Prerequisites:
810 -- This procedure is called from the update_validate.
811 --
812 -- In Parameters:
813 --
814 -- Post Success:
815 -- Processing continues.
816 --
817 -- Post Failure:
818 --
819 -- Developer Implementation Notes:
820 -- This procedure should not need maintenance unless the HR Schema model
821 -- changes.
822 --
823 -- Access Status:
824 -- Internal Row Handler Use Only.
825 --
826 -- {End Of Comments}
827 -- ----------------------------------------------------------------------------
828 Procedure dt_update_validate
829 (p_actl_prem_id in number default hr_api.g_number,
830 p_cvg_amt_calc_mthd_id in number default hr_api.g_number,
831 p_acty_base_rt_id in number default hr_api.g_number,
832 p_datetrack_mode in varchar2,
833 p_validation_start_date in date,
834 p_validation_end_date in date) Is
835 --
836 l_proc varchar2(72) := g_package||'dt_update_validate';
837 l_integrity_error Exception;
838 l_table_name all_tables.table_name%TYPE;
839 --
840 Begin
841 hr_utility.set_location('Entering:'||l_proc, 5);
842 --
843 -- Ensure that the p_datetrack_mode argument is not null
844 --
845 hr_api.mandatory_arg_error
846 (p_api_name => l_proc,
847 p_argument => 'datetrack_mode',
848 p_argument_value => p_datetrack_mode);
849 --
850 -- Only perform the validation if the datetrack update mode is valid
851 --
852 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
853 --
854 --
855 -- Ensure the arguments are not null
856 --
857 hr_api.mandatory_arg_error
858 (p_api_name => l_proc,
859 p_argument => 'validation_start_date',
860 p_argument_value => p_validation_start_date);
861 --
862 hr_api.mandatory_arg_error
863 (p_api_name => l_proc,
864 p_argument => 'validation_end_date',
865 p_argument_value => p_validation_end_date);
866 --
867 If ((nvl(p_actl_prem_id, hr_api.g_number) <> hr_api.g_number) and
868 NOT (dt_api.check_min_max_dates
872 p_from_date => p_validation_start_date,
869 (p_base_table_name => 'ben_actl_prem_f',
870 p_base_key_column => 'actl_prem_id',
871 p_base_key_value => p_actl_prem_id,
873 p_to_date => p_validation_end_date))) Then
874 l_table_name := 'ben_actl_prem_f';
875 Raise l_integrity_error;
876 End If;
877 If ((nvl(p_cvg_amt_calc_mthd_id, hr_api.g_number) <> hr_api.g_number) and
878 NOT (dt_api.check_min_max_dates
879 (p_base_table_name => 'ben_cvg_amt_calc_mthd_f',
880 p_base_key_column => 'cvg_amt_calc_mthd_id',
881 p_base_key_value => p_cvg_amt_calc_mthd_id,
882 p_from_date => p_validation_start_date,
883 p_to_date => p_validation_end_date))) Then
884 l_table_name := 'ben_cvg_amt_calc_mthd_f';
885 Raise l_integrity_error;
886 End If;
887 If ((nvl(p_acty_base_rt_id, hr_api.g_number) <> hr_api.g_number) and
888 NOT (dt_api.check_min_max_dates
889 (p_base_table_name => 'ben_acty_base_rt_f',
890 p_base_key_column => 'acty_base_rt_id',
891 p_base_key_value => p_acty_base_rt_id,
892 p_from_date => p_validation_start_date,
893 p_to_date => p_validation_end_date))) Then
894 l_table_name := 'ben_acty_base_rt_f';
895 Raise l_integrity_error;
896 End If;
897 --
898 End If;
899 --
900 hr_utility.set_location(' Leaving:'||l_proc, 10);
901 Exception
902 When l_integrity_error Then
903 --
904 -- A referential integrity check was violated therefore
905 -- we must error
906 --
907 hr_utility.set_message('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
908 hr_utility.set_message_token('TABLE_NAME', l_table_name);
909 hr_utility.raise_error;
910 When Others Then
911 --
912 -- An unhandled or unexpected error has occurred which
913 -- we must report
914 --
915 hr_utility.set_message('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
916 hr_utility.set_message_token('PROCEDURE', l_proc);
917 hr_utility.set_message_token('STEP','15');
918 hr_utility.raise_error;
919 End dt_update_validate;
920 --
921 -- ----------------------------------------------------------------------------
922 -- |--------------------------< dt_delete_validate >--------------------------|
923 -- ----------------------------------------------------------------------------
924 -- {Start Of Comments}
925 --
926 -- Description:
927 -- This procedure is used for referential integrity of datetracked
928 -- child entities when either a datetrack DELETE or ZAP is in operation
929 -- and where there is no cascading of delete defined for this entity.
930 -- For the datetrack mode of DELETE or ZAP we must ensure that no
931 -- datetracked child rows exist between the validation start and end
932 -- dates.
933 --
934 -- Prerequisites:
935 -- This procedure is called from the delete_validate.
936 --
937 -- In Parameters:
938 --
939 -- Post Success:
940 -- Processing continues.
941 --
942 -- Post Failure:
943 -- If a row exists by determining the returning Boolean value from the
944 -- generic dt_api.rows_exist function then we must supply an error via
945 -- the use of the local exception handler l_rows_exist.
946 --
947 -- Developer Implementation Notes:
948 -- This procedure should not need maintenance unless the HR Schema model
949 -- changes.
950 --
951 -- Access Status:
952 -- Internal Row Handler Use Only.
953 --
954 -- {End Of Comments}
955 -- ----------------------------------------------------------------------------
956 Procedure dt_delete_validate
957 (p_prtl_mo_rt_prtn_val_id in number,
958 p_datetrack_mode in varchar2,
959 p_validation_start_date in date,
960 p_validation_end_date in date) Is
961 --
962 l_proc varchar2(72) := g_package||'dt_delete_validate';
963 l_rows_exist Exception;
964 l_table_name all_tables.table_name%TYPE;
965 --
966 Begin
967 hr_utility.set_location('Entering:'||l_proc, 5);
968 --
969 -- Ensure that the p_datetrack_mode argument is not null
970 --
971 hr_api.mandatory_arg_error
972 (p_api_name => l_proc,
973 p_argument => 'datetrack_mode',
974 p_argument_value => p_datetrack_mode);
975 --
976 -- Only perform the validation if the datetrack mode is either
977 -- DELETE or ZAP
978 --
979 If (p_datetrack_mode = 'DELETE' or
980 p_datetrack_mode = 'ZAP') then
981 --
982 --
983 -- Ensure the arguments are not null
984 --
985 hr_api.mandatory_arg_error
986 (p_api_name => l_proc,
987 p_argument => 'validation_start_date',
988 p_argument_value => p_validation_start_date);
989 --
990 hr_api.mandatory_arg_error
991 (p_api_name => l_proc,
992 p_argument => 'validation_end_date',
993 p_argument_value => p_validation_end_date);
994 --
995 hr_api.mandatory_arg_error
996 (p_api_name => l_proc,
997 p_argument => 'prtl_mo_rt_prtn_val_id',
1001 --
998 p_argument_value => p_prtl_mo_rt_prtn_val_id);
999 --
1000 --
1002 End If;
1003 --
1004 hr_utility.set_location(' Leaving:'||l_proc, 10);
1005 Exception
1006 When l_rows_exist Then
1007 --
1008 -- A referential integrity check was violated therefore
1009 -- we must error
1010 --
1011 hr_utility.set_message('PAY', 'HR_7215_DT_CHILD_EXISTS');
1012 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1013 hr_utility.raise_error;
1014 When Others Then
1015 --
1016 -- An unhandled or unexpected error has occurred which
1017 -- we must report
1018 --
1019 hr_utility.set_message('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1020 hr_utility.set_message_token('PROCEDURE', l_proc);
1021 hr_utility.set_message_token('STEP','15');
1022 hr_utility.raise_error;
1023 End dt_delete_validate;
1024 --
1025 -- ----------------------------------------------------------------------------
1026 -- |---------------------------< insert_validate >----------------------------|
1027 -- ----------------------------------------------------------------------------
1028 Procedure insert_validate
1029 (p_rec in ben_ppv_shd.g_rec_type,
1030 p_effective_date in date,
1031 p_datetrack_mode in varchar2,
1032 p_validation_start_date in date,
1033 p_validation_end_date in date) is
1034 --
1035 l_proc varchar2(72) := g_package||'insert_validate';
1036 --
1037 Begin
1038 hr_utility.set_location('Entering:'||l_proc, 5);
1039 --
1040 -- Call all supporting business operations
1041 --
1042 --
1043 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1044 --
1045 if p_rec.PRORATE_BY_DAY_TO_MON_FLAG = 'Y' then
1046 --
1047 chk_dup_prorate_by_day_row(p_effective_date => p_effective_date,
1048 p_acty_base_rt_id => p_rec.acty_base_rt_id,
1049 p_mode => 'Insert');
1050 --
1051 end if;
1052 --
1053 chk_prtl_mo_rt_prtn_val_id
1054 (p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id,
1055 p_effective_date => p_effective_date,
1056 p_object_version_number => p_rec.object_version_number);
1057 --
1058 chk_rndg_cd
1059 (p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id,
1060 p_rndg_cd => p_rec.rndg_cd,
1061 p_effective_date => p_effective_date,
1062 p_object_version_number => p_rec.object_version_number);
1063 --
1064 chk_prtl_mo_prortn_rl
1065 (p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id,
1066 p_business_group_id => p_rec.business_group_id,
1067 p_prtl_mo_prortn_rl => p_rec.prtl_mo_prortn_rl,
1068 p_effective_date => p_effective_date,
1069 p_object_version_number => p_rec.object_version_number);
1070 --
1071 chk_rndg_rl
1072 (p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id,
1073 p_rndg_rl => p_rec.rndg_rl,
1074 p_effective_date => p_effective_date,
1075 p_object_version_number => p_rec.object_version_number);
1076 --
1077 chk_rndg_cd_rl
1078 (p_rndg_cd => p_rec.rndg_cd,
1079 p_rndg_rl => p_rec.rndg_rl);
1080 --
1081 chk_strt_r_stp_cvg_cd
1082 (p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id,
1083 p_strt_r_stp_cvg_cd => p_rec.strt_r_stp_cvg_cd,
1084 p_effective_date => p_effective_date,
1085 p_object_version_number => p_rec.object_version_number);
1086 --
1087 chk_prortn_rl_pct_val
1088 (p_prtl_mo_prortn_rl => p_rec.prtl_mo_prortn_rl,
1089 p_pct_val => p_rec.pct_val,
1090 p_prorate_by_day_to_mon_flag => p_rec.prorate_by_day_to_mon_flag,
1091 p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id, -- Bug No 4418762
1092 p_acty_base_rt_id => p_rec.acty_base_rt_id,
1093 p_actl_prem_id => p_rec.actl_prem_id, -- Bug No 4440097
1094 p_business_group_id => p_rec.business_group_id,
1095 p_validation_start_date => p_validation_start_date,
1096 p_validation_end_date => p_validation_end_date);
1097 --
1098 chk_from_dt_to_dy
1099 (p_from_dy_mo_num => p_rec.from_dy_mo_num,
1100 p_to_dy_mo_num => p_rec.to_dy_mo_num);
1101 --
1102 chk_unique_and_not_overlapping
1103 (p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id,
1104 p_acty_base_rt_id => p_rec.acty_base_rt_id,
1105 p_from_dy_mo_num => p_rec.from_dy_mo_num,
1106 p_to_dy_mo_num => p_rec.to_dy_mo_num,
1107 p_strt_r_stp_cvg_cd => p_rec.strt_r_stp_cvg_cd,
1108 p_num_days_month => p_rec.num_days_month, -- Bug No 4366086
1109 p_business_group_id => p_rec.business_group_id,
1110 p_effective_date => p_effective_date,
1111 p_validation_start_date => p_validation_start_date, -- Bug No 4366086
1112 p_validation_end_date => p_validation_end_date); -- Bug No 4366086
1113 --
1114 hr_utility.set_location(' Leaving:'||l_proc, 10);
1115 End insert_validate;
1116 --
1117 -- ----------------------------------------------------------------------------
1118 -- |---------------------------< update_validate >----------------------------|
1119 -- ----------------------------------------------------------------------------
1120 Procedure update_validate
1124 p_validation_start_date in date,
1121 (p_rec in ben_ppv_shd.g_rec_type,
1122 p_effective_date in date,
1123 p_datetrack_mode in varchar2,
1125 p_validation_end_date in date) is
1126 --
1127 l_proc varchar2(72) := g_package||'update_validate';
1128 --
1129 Begin
1130 hr_utility.set_location('Entering:'||l_proc, 5);
1131 --
1132 -- Call all supporting business operations
1133 --
1134 --
1135 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1136 --
1137 if p_rec.PRORATE_BY_DAY_TO_MON_FLAG = 'Y' then
1138 --
1139 chk_dup_prorate_by_day_row(p_effective_date => p_effective_date,
1140 p_acty_base_rt_id => p_rec.acty_base_rt_id,
1141 p_mode => 'Update',
1142 p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id);
1143 --
1144 end if;
1145 --
1146 chk_prtl_mo_rt_prtn_val_id
1147 (p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id,
1148 p_effective_date => p_effective_date,
1149 p_object_version_number => p_rec.object_version_number);
1150 --
1151 chk_rndg_cd
1152 (p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id,
1153 p_rndg_cd => p_rec.rndg_cd,
1154 p_effective_date => p_effective_date,
1155 p_object_version_number => p_rec.object_version_number);
1156 --
1157 chk_prtl_mo_prortn_rl
1158 (p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id,
1159 p_business_group_id => p_rec.business_group_id,
1160 p_prtl_mo_prortn_rl => p_rec.prtl_mo_prortn_rl,
1161 p_effective_date => p_effective_date,
1162 p_object_version_number => p_rec.object_version_number);
1163 --
1164 chk_rndg_rl
1165 (p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id,
1166 p_rndg_rl => p_rec.rndg_rl,
1167 p_effective_date => p_effective_date,
1168 p_object_version_number => p_rec.object_version_number);
1169 --
1170 chk_strt_r_stp_cvg_cd
1171 (p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id,
1172 p_strt_r_stp_cvg_cd => p_rec.strt_r_stp_cvg_cd,
1173 p_effective_date => p_effective_date,
1174 p_object_version_number => p_rec.object_version_number);
1175 --
1176 chk_prortn_rl_pct_val
1177 (p_prtl_mo_prortn_rl => p_rec.prtl_mo_prortn_rl,
1178 p_pct_val => p_rec.pct_val,
1179 p_prorate_by_day_to_mon_flag => p_rec.prorate_by_day_to_mon_flag,
1180 p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id, -- Bug No 4418762
1181 p_acty_base_rt_id => p_rec.acty_base_rt_id,
1182 p_actl_prem_id => p_rec.actl_prem_id, -- Bug No 4440097
1183 p_business_group_id => p_rec.business_group_id,
1184 p_validation_start_date => p_validation_start_date,
1185 p_validation_end_date => p_validation_end_date);
1186 --
1187 chk_from_dt_to_dy
1188 (p_from_dy_mo_num => p_rec.from_dy_mo_num,
1189 p_to_dy_mo_num => p_rec.to_dy_mo_num);
1190 --
1191 chk_unique_and_not_overlapping
1192 (p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id,
1193 p_acty_base_rt_id => p_rec.acty_base_rt_id,
1194 p_from_dy_mo_num => p_rec.from_dy_mo_num,
1195 p_to_dy_mo_num => p_rec.to_dy_mo_num,
1196 p_strt_r_stp_cvg_cd => p_rec.strt_r_stp_cvg_cd,
1197 p_num_days_month => p_rec.num_days_month, -- Bug No 4366086
1198 p_business_group_id => p_rec.business_group_id,
1199 p_effective_date => p_effective_date,
1200 p_validation_start_date => p_validation_start_date, -- Bug No 4366086
1201 p_validation_end_date => p_validation_end_date); -- Bug No 4366086
1202 --
1203 chk_rndg_cd_rl
1204 (p_rndg_cd => p_rec.rndg_cd,
1205 p_rndg_rl => p_rec.rndg_rl);
1206 --
1207 -- Call the datetrack update integrity operation
1208 --
1209 dt_update_validate
1210 (p_actl_prem_id => p_rec.actl_prem_id,
1211 p_cvg_amt_calc_mthd_id => p_rec.cvg_amt_calc_mthd_id,
1212 p_acty_base_rt_id => p_rec.acty_base_rt_id,
1213 p_datetrack_mode => p_datetrack_mode,
1214 p_validation_start_date => p_validation_start_date,
1215 p_validation_end_date => p_validation_end_date);
1216 --
1217 hr_utility.set_location(' Leaving:'||l_proc, 10);
1218 End update_validate;
1219 --
1220 -- ----------------------------------------------------------------------------
1221 -- |---------------------------< delete_validate >----------------------------|
1222 -- ----------------------------------------------------------------------------
1223 Procedure delete_validate
1224 (p_rec in ben_ppv_shd.g_rec_type,
1225 p_effective_date in date,
1226 p_datetrack_mode in varchar2,
1227 p_validation_start_date in date,
1228 p_validation_end_date in date) is
1229 --
1230 l_proc varchar2(72) := g_package||'delete_validate';
1231 --
1232 Begin
1233 hr_utility.set_location('Entering:'||l_proc, 5);
1234 --
1235 -- Call all supporting business operations
1236 --
1237 dt_delete_validate
1238 (p_datetrack_mode => p_datetrack_mode,
1239 p_validation_start_date => p_validation_start_date,
1240 p_validation_end_date => p_validation_end_date,
1241 p_prtl_mo_rt_prtn_val_id => p_rec.prtl_mo_rt_prtn_val_id);
1242 --
1243 hr_utility.set_location(' Leaving:'||l_proc, 10);
1244 End delete_validate;
1245 --
1246 --
1247 -- ---------------------------------------------------------------------------
1248 -- |---------------------< return_legislation_code >-------------------------|
1249 -- ---------------------------------------------------------------------------
1250 --
1251 function return_legislation_code
1252 (p_prtl_mo_rt_prtn_val_id in number) return varchar2 is
1253 --
1254 -- Declare cursor
1255 --
1256 cursor csr_leg_code is
1257 select a.legislation_code
1258 from per_business_groups a,
1259 ben_prtl_mo_rt_prtn_val_f b
1260 where b.prtl_mo_rt_prtn_val_id = p_prtl_mo_rt_prtn_val_id
1261 and a.business_group_id = b.business_group_id;
1262 --
1263 -- Declare local variables
1264 --
1265 l_legislation_code varchar2(150);
1266 l_proc varchar2(72) := g_package||'return_legislation_code';
1267 --
1268 begin
1269 --
1270 hr_utility.set_location('Entering:'|| l_proc, 10);
1271 --
1272 -- Ensure that all the mandatory parameter are not null
1273 --
1274 hr_api.mandatory_arg_error(p_api_name => l_proc,
1275 p_argument => 'prtl_mo_rt_prtn_val_id',
1276 p_argument_value => p_prtl_mo_rt_prtn_val_id);
1277 --
1278 open csr_leg_code;
1279 --
1280 fetch csr_leg_code into l_legislation_code;
1281 --
1282 if csr_leg_code%notfound then
1283 --
1284 close csr_leg_code;
1285 --
1286 -- The primary key is invalid therefore we must error
1287 --
1288 hr_utility.set_message('PAY','HR_7220_INVALID_PRIMARY_KEY');
1289 hr_utility.raise_error;
1290 --
1291 end if;
1292 --
1293 close csr_leg_code;
1294 --
1295 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1296 --
1297 return l_legislation_code;
1298 --
1299 end return_legislation_code;
1300 --
1301 end ben_ppv_bus;