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