[Home] [Help]
PACKAGE BODY: APPS.BEN_CLF_BUS
Source
1 Package Body ben_clf_bus as
2 /* $Header: beclfrhi.pkb 120.0 2005/05/28 01:04:10 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_clf_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_comp_lvl_fctr_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 -- comp_lvl_fctr_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_comp_lvl_fctr_id(p_comp_lvl_fctr_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_comp_lvl_fctr_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := ben_clf_shd.api_updating
47 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_comp_lvl_fctr_id,hr_api.g_number)
52 <> ben_clf_shd.g_old_rec.comp_lvl_fctr_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_clf_shd.constraint_error('BEN_COMP_LVL_FCTR_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_comp_lvl_fctr_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_clf_shd.constraint_error('BEN_COMP_LVL_FCTR_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_comp_lvl_fctr_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_defined_balance_id >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure is used to check that the defined Balance is valid.
82 --
83 -- Pre Conditions
84 -- None.
85 --
86 -- In Parameters
87 -- comp_lvl_fctr_id PK of record being inserted or updated.
88 -- defined_balance_id Value of defined balance id.
89 -- effective_date effective date
90 -- object_version_number Object version number of record being
91 -- inserted or updated.
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Error handled by procedure
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 Procedure chk_defined_balance_id(p_comp_lvl_fctr_id in number,
103 p_business_group_id in number,
104 p_defined_balance_id in number,
105 p_object_version_number in number) is
106 --
107 l_proc varchar2(72) := g_package||'chk_defined_balance_id';
108 l_api_updating boolean;
109 l_dummy varchar2(1);
110 --
111 cursor c1 is
112 select null
113 from pay_defined_balances a
114 where a.defined_balance_id = p_defined_balance_id
115 and nvl(a.business_group_id,p_business_group_id) = p_business_group_id;
116 --
117 Begin
118 --
119 hr_utility.set_location('Entering:'||l_proc, 5);
120 --
121 l_api_updating := ben_clf_shd.api_updating
122 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
123 p_object_version_number => p_object_version_number);
124 --
125 if (l_api_updating
126 and nvl(p_defined_balance_id,hr_api.g_number)
127 <> ben_clf_shd.g_old_rec.defined_balance_id
128 or not l_api_updating)
129 and p_defined_balance_id is not null then
130 --
131 -- check if value of defined_balance_id is valid.
132 --
133 open c1;
134 --
135 fetch c1 into l_dummy;
136 if c1%notfound then
137 --
138 close c1;
139 --
140 ben_clf_shd.constraint_error('BEN_COMP_LVL_FCTR_FK2');
141 --
142 end if;
143 --
144 close c1;
145 --
146 end if;
147 --
148 hr_utility.set_location('Leaving:'||l_proc,10);
149 --
150 end chk_defined_balance_id;
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_rndg_rl >------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- Description
156 -- This procedure is used to check that the Formula Rule is valid.
157 --
158 -- Pre Conditions
159 -- None.
160 --
161 -- In Parameters
162 -- comp_lvl_fctr_id PK of record being inserted or updated.
163 -- rndg_rl Value of formula rule id.
164 -- effective_date effective date
165 -- object_version_number Object version number of record being
166 -- inserted or updated.
167 --
168 -- Post Success
169 -- Processing continues
170 --
171 -- Post Failure
172 -- Error handled by procedure
173 --
174 -- Access Status
175 -- Internal table handler use only.
176 --
177 Procedure chk_rndg_rl(p_comp_lvl_fctr_id in number,
178 p_business_group_id in number,
179 p_rndg_rl in number,
180 p_rndg_cd in varchar2,
181 p_effective_date in date,
182 p_object_version_number in number) is
183 --
184 l_proc varchar2(72) := g_package||'chk_rndg_rl';
185 l_api_updating boolean;
186 l_dummy varchar2(1);
187 --
188 cursor c1 is
189 select null
190 from ff_formulas_f ff
191 ,per_business_groups pbg
192 where ff.formula_id = p_rndg_rl
193 and ff.formula_type_id = -169
194 and pbg.business_group_id = p_business_group_id
195 and nvl(ff.business_group_id, p_business_group_id) =
196 p_business_group_id
197 and nvl(ff.legislation_code, pbg.legislation_code) =
198 pbg.legislation_code
199 and p_effective_date
200 between ff.effective_start_date
201 and ff.effective_end_date;
202 --
203 Begin
204 --
205 hr_utility.set_location('Entering:'||l_proc, 5);
206 --
207 l_api_updating := ben_clf_shd.api_updating
208 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
209 p_object_version_number => p_object_version_number);
210 --
211 if (l_api_updating
212 and nvl(p_rndg_rl,hr_api.g_number)
213 <> ben_clf_shd.g_old_rec.rndg_rl
214 or not l_api_updating)
215 and p_rndg_rl is not null then
216 --
217 -- check if value of formula rule is valid.
218 --
219 open c1;
220 --
221 -- fetch value from cursor if it returns a record then the
222 -- formula is valid otherwise its invalid
223 --
224 fetch c1 into l_dummy;
225 if c1%notfound then
226 --
227 close c1;
228 --
229 -- raise error
230 --
231 fnd_message.set_name('BEN','BEN_91042_INVALID_RNDG_RL');
232 fnd_message.raise_error;
233 --
234 end if;
235 --
236 close c1;
237 --
238 end if;
239 --
240 -- Unless Rounding Code = Rule, Rounding rule must be blank.
241 if nvl(p_rndg_cd,hr_api.g_varchar2) <> 'RL' and p_rndg_rl is not null then
242 --
243 fnd_message.set_name('BEN', 'BEN_91043_RNDG_RL_NOT_NULL');
244 fnd_message.raise_error;
245 --
246 elsif nvl(p_rndg_cd,hr_api.g_varchar2) = 'RL' and p_rndg_rl is null then
247 --
248 fnd_message.set_name('BEN', 'BEN_92340_RNDG_RL_NULL');
249 fnd_message.raise_error;
250 --
251 end if;
252 --
253 --
254 hr_utility.set_location('Leaving:'||l_proc,10);
255 --
256 end chk_rndg_rl;
257 --
258 -- ----------------------------------------------------------------------------
259 -- |------< chk_rndg_cd >------|
260 -- ----------------------------------------------------------------------------
261 --
262 -- Description
263 -- This procedure is used to check that the lookup value is valid.
264 --
265 -- Pre Conditions
266 -- None.
267 --
268 -- In Parameters
269 -- comp_lvl_fctr_id PK of record being inserted or updated.
270 -- rndg_cd Value of lookup code.
271 -- effective_date effective date
272 -- object_version_number Object version number of record being
273 -- inserted or updated.
274 --
275 -- Post Success
276 -- Processing continues
277 --
278 -- Post Failure
279 -- Error handled by procedure
280 --
281 -- Access Status
282 -- Internal table handler use only.
283 --
284 Procedure chk_rndg_cd(p_comp_lvl_fctr_id in number,
285 p_rndg_cd in varchar2,
286 p_effective_date in date,
287 p_object_version_number in number) is
288 --
289 l_proc varchar2(72) := g_package||'chk_rndg_cd';
290 l_api_updating boolean;
291 --
292 Begin
293 --
294 hr_utility.set_location('Entering:'||l_proc, 5);
295 --
296 l_api_updating := ben_clf_shd.api_updating
297 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
298 p_object_version_number => p_object_version_number);
299 --
300 if (l_api_updating
301 and p_rndg_cd
302 <> nvl(ben_clf_shd.g_old_rec.rndg_cd,hr_api.g_varchar2)
303 or not l_api_updating)
304 and p_rndg_cd is not null then
305 --
306 -- check if value of lookup falls within lookup type.
307 --
308 if hr_api.not_exists_in_hr_lookups
309 (p_lookup_type => 'BEN_RNDG',
310 p_lookup_code => p_rndg_cd,
311 p_effective_date => p_effective_date) then
312 --
313 -- raise error as does not exist as lookup
314 --
315 fnd_message.set_name('BEN','BEN_91041_INVALID_RNDG_CD');
316 fnd_message.raise_error;
317 --
318 end if;
319 --
320 end if;
321 --
322 hr_utility.set_location('Leaving:'||l_proc,10);
323 --
324 end chk_rndg_cd;
325 --
326 -- ----------------------------------------------------------------------------
327 -- |------< chk_comp_lvl_det_rl >------|
328 -- ----------------------------------------------------------------------------
329 --
330 -- Description
331 -- This procedure is used to check that the Formula Rule is valid.
332 --
333 -- Pre Conditions
334 -- None.
335 --
336 -- In Parameters
337 -- comp_lvl_fctr_id PK of record being inserted or updated.
338 -- comp_lvl_det_rl Value of formula rule id.
339 -- effective_date effective date
340 -- object_version_number Object version number of record being
341 -- inserted or updated.
342 --
343 -- Post Success
344 -- Processing continues
345 --
346 -- Post Failure
347 -- Error handled by procedure
348 --
349 -- Access Status
350 -- Internal table handler use only.
351 --
352 Procedure chk_comp_lvl_det_rl(p_comp_lvl_fctr_id in number,
353 p_business_group_id in number,
354 p_comp_lvl_det_rl in number,
355 p_comp_lvl_det_cd in varchar2, -- Bug No 4242978
356 p_effective_date in date,
357 p_object_version_number in number) is
358 --
359 l_proc varchar2(72) := g_package||'chk_comp_lvl_det_rl';
360 l_api_updating boolean;
361 l_dummy varchar2(1);
362 --
363 cursor c1 is
364 select null
365 from ff_formulas_f ff
366 ,per_business_groups pbg
367 where ff.formula_id = p_comp_lvl_det_rl
368 and ff.formula_type_id = -174
369 and pbg.business_group_id = p_business_group_id
370 and nvl(ff.business_group_id, p_business_group_id) =
371 p_business_group_id
372 and nvl(ff.legislation_code, pbg.legislation_code) =
373 pbg.legislation_code
374 and p_effective_date
375 between ff.effective_start_date
376 and ff.effective_end_date;
377 --
378 Begin
379 --
380 hr_utility.set_location('Entering:'||l_proc, 5);
381 --
382 l_api_updating := ben_clf_shd.api_updating
383 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
384 p_object_version_number => p_object_version_number);
385 --
386 if (l_api_updating
387 and nvl(p_comp_lvl_det_rl,hr_api.g_number)
388 <> ben_clf_shd.g_old_rec.comp_lvl_det_rl
389 or not l_api_updating)
390 and p_comp_lvl_det_rl is not null then
391 --
392 -- check if value of formula rule is valid.
393 --
394 open c1;
395 --
396 -- fetch value from cursor if it returns a record then the
397 -- formula is valid otherwise its invalid
398 --
399 fetch c1 into l_dummy;
400 if c1%notfound then
401 --
402 close c1;
403 --
404 -- raise error
405 --
406 fnd_message.set_name('BEN','BEN_91050_INVALID_COMP_LVL_RL');
407 fnd_message.raise_error;
408 --
409 end if;
410 --
411 close c1;
412 --
413 end if;
414 --
415 -- Bug No 4242978 Added validations for determination rule not null when
416 -- det code is RL and det rule should be null when det code not RL
417 --
418 -- Unless comp_lvl determination Code = Rule, comp_lvl determination rule must be blank.
419 if nvl(p_comp_lvl_det_cd,hr_api.g_varchar2) <> 'RL' and p_comp_lvl_det_rl is not null then
420 --
421 fnd_message.set_name('BEN', 'BEN_94207_COMP_DET_RL_NOT_NULL');
422 fnd_message.raise_error;
423 --
424 elsif nvl(p_comp_lvl_det_cd,hr_api.g_varchar2) = 'RL' and p_comp_lvl_det_rl is null then
425 --
426 fnd_message.set_name('BEN', 'BEN_94206_COMP_DET_RL_NULL');
427 fnd_message.raise_error;
428 --
429 end if;
430 --
431 hr_utility.set_location('Leaving:'||l_proc,10);
432 --
433 end chk_comp_lvl_det_rl;
434 --
435 -- ----------------------------------------------------------------------------
436 -- |------< chk_no_mx_comp_flag >------|
437 -- ----------------------------------------------------------------------------
438 --
439 -- Description
440 -- This procedure is used to check that the lookup value is valid.
441 --
442 -- Pre Conditions
443 -- None.
444 --
445 -- In Parameters
446 -- comp_lvl_fctr_id PK of record being inserted or updated.
447 -- no_mx_comp_flag Value of lookup code.
448 -- effective_date effective date
449 -- object_version_number Object version number of record being
450 -- inserted or updated.
451 --
452 -- Post Success
453 -- Processing continues
454 --
455 -- Post Failure
456 -- Error handled by procedure
457 --
458 -- Access Status
459 -- Internal table handler use only.
460 --
461 Procedure chk_no_mx_comp_flag(p_comp_lvl_fctr_id in number,
462 p_no_mx_comp_flag in varchar2,
463 p_effective_date in date,
464 p_object_version_number in number) is
465 --
466 l_proc varchar2(72) := g_package||'chk_no_mx_comp_flag';
467 l_api_updating boolean;
468 --
469 Begin
470 --
471 hr_utility.set_location('Entering:'||l_proc, 5);
472 --
473 l_api_updating := ben_clf_shd.api_updating
474 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
475 p_object_version_number => p_object_version_number);
476 --
477 if (l_api_updating
478 and p_no_mx_comp_flag
479 <> nvl(ben_clf_shd.g_old_rec.no_mx_comp_flag,hr_api.g_varchar2)
480 or not l_api_updating)
481 and p_no_mx_comp_flag is not null then
482 --
483 -- check if value of lookup falls within lookup type.
484 --
485 if hr_api.not_exists_in_hr_lookups
486 (p_lookup_type => 'YES_NO',
487 p_lookup_code => p_no_mx_comp_flag,
488 p_effective_date => p_effective_date) then
489 --
490 -- raise error as does not exist as lookup
491 --
492 fnd_message.set_name('BEN','BEN_91051_INVALID_MIN_FLAG');
493 fnd_message.raise_error;
494 --
495 end if;
496 --
497 end if;
498 --
499 hr_utility.set_location('Leaving:'||l_proc,10);
500 --
501 end chk_no_mx_comp_flag;
502 --
503 -- ----------------------------------------------------------------------------
504 -- |------< chk_no_mn_comp_flag >------|
505 -- ----------------------------------------------------------------------------
506 --
507 -- Description
508 -- This procedure is used to check that the lookup value is valid.
509 --
510 -- Pre Conditions
511 -- None.
512 --
513 -- In Parameters
514 -- comp_lvl_fctr_id PK of record being inserted or updated.
515 -- no_mn_comp_flag Value of lookup code.
516 -- effective_date effective date
517 -- object_version_number Object version number of record being
518 -- inserted or updated.
519 --
520 -- Post Success
521 -- Processing continues
522 --
523 -- Post Failure
524 -- Error handled by procedure
525 --
526 -- Access Status
527 -- Internal table handler use only.
528 --
529 Procedure chk_no_mn_comp_flag(p_comp_lvl_fctr_id in number,
530 p_no_mn_comp_flag in varchar2,
531 p_effective_date in date,
532 p_object_version_number in number) is
533 --
534 l_proc varchar2(72) := g_package||'chk_no_mn_comp_flag';
535 l_api_updating boolean;
536 --
537 Begin
538 --
539 hr_utility.set_location('Entering:'||l_proc, 5);
540 --
541 l_api_updating := ben_clf_shd.api_updating
542 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
543 p_object_version_number => p_object_version_number);
544 --
545 if (l_api_updating
546 and p_no_mn_comp_flag
547 <> nvl(ben_clf_shd.g_old_rec.no_mn_comp_flag,hr_api.g_varchar2)
548 or not l_api_updating)
549 and p_no_mn_comp_flag is not null then
550 --
551 -- check if value of lookup falls within lookup type.
552 --
553 if hr_api.not_exists_in_hr_lookups
554 (p_lookup_type => 'YES_NO',
555 p_lookup_code => p_no_mn_comp_flag,
556 p_effective_date => p_effective_date) then
557 --
558 -- raise error as does not exist as lookup
559 --
560 fnd_message.set_name('BEN','BEN_91052_INVALID_MAX_FLAG');
561 fnd_message.raise_error;
562 --
563 end if;
564 --
565 end if;
566 --
567 hr_utility.set_location('Leaving:'||l_proc,10);
568 --
569 end chk_no_mn_comp_flag;
570 --
571 -- ----------------------------------------------------------------------------
572 -- |------< chk_comp_src_cd >------|
573 -- ----------------------------------------------------------------------------
574 --
575 -- Description
576 -- This procedure is used to check that the lookup value is valid.
577 --
578 -- Pre Conditions
579 -- None.
580 --
581 -- In Parameters
582 -- comp_lvl_fctr_id PK of record being inserted or updated.
583 -- comp_src_cd Value of lookup code.
584 -- effective_date effective date
585 -- object_version_number Object version number of record being
586 -- inserted or updated.
587 --
588 -- Post Success
589 -- Processing continues
590 --
591 -- Post Failure
592 -- Error handled by procedure
593 --
594 -- Access Status
595 -- Internal table handler use only.
596 --
597 Procedure chk_comp_src_cd(p_comp_lvl_fctr_id in number,
598 p_comp_src_cd in varchar2,
599 p_effective_date in date,
600 p_object_version_number in number) is
601 --
602 l_proc varchar2(72) := g_package||'chk_comp_src_cd';
603 l_api_updating boolean;
604 --
605 Begin
606 --
607 hr_utility.set_location('Entering:'||l_proc, 5);
608 --
609 l_api_updating := ben_clf_shd.api_updating
610 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
611 p_object_version_number => p_object_version_number);
612 --
613 if (l_api_updating
614 and p_comp_src_cd
615 <> nvl(ben_clf_shd.g_old_rec.comp_src_cd,hr_api.g_varchar2)
616 or not l_api_updating)
617 and p_comp_src_cd is not null then
618 --
619 -- check if value of lookup falls within lookup type.
620 --
621 if hr_api.not_exists_in_hr_lookups
622 (p_lookup_type => 'BEN_COMP_SRC',
623 p_lookup_code => p_comp_src_cd,
624 p_effective_date => p_effective_date) then
625 --
626 -- raise error as does not exist as lookup
627 --
628 fnd_message.set_name('BEN','BEN_91053_INVALID_SRC_CD');
629 fnd_message.raise_error;
630 --
631 end if;
632 --
633 end if;
634 --
635 hr_utility.set_location('Leaving:'||l_proc,10);
636 --
637 end chk_comp_src_cd;
638 --
639 -- ----------------------------------------------------------------------------
640 -- |------< chk_comp_lvl_uom >------|
641 -- ----------------------------------------------------------------------------
642 --
643 -- Description
644 -- This procedure is used to check that the lookup value is valid.
645 --
646 -- Pre Conditions
647 -- None.
648 --
649 -- In Parameters
650 -- comp_lvl_fctr_id PK of record being inserted or updated.
651 -- comp_lvl_uom Value of lookup code.
652 -- effective_date effective date
653 -- object_version_number Object version number of record being
654 -- inserted or updated.
655 --
656 -- Post Success
657 -- Processing continues
658 --
659 -- Post Failure
660 -- Error handled by procedure
661 --
662 -- Access Status
663 -- Internal table handler use only.
664 --
665 Procedure chk_comp_lvl_uom(p_comp_lvl_fctr_id in number,
666 p_comp_lvl_uom in varchar2,
667 p_effective_date in date,
668 p_object_version_number in number) is
669 --
670 cursor c1 is select currency_code
671 from fnd_currencies
672 where currency_code = p_comp_lvl_uom
673 and enabled_flag = 'Y' and
674 p_effective_date
675 between nvl(start_date_active, p_effective_date)
676 and nvl(end_date_active, p_effective_date);
677 l_proc varchar2(72) := g_package||'chk_comp_lvl_uom';
678 l_api_updating boolean;
679 l_dummy varchar2(30);
680 --
681 Begin
682 --
683 hr_utility.set_location('Entering:'||l_proc, 5);
684 --
685 l_api_updating := ben_clf_shd.api_updating
686 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
687 p_object_version_number => p_object_version_number);
688 --
689 if (l_api_updating
690 and p_comp_lvl_uom
691 <> nvl(ben_clf_shd.g_old_rec.comp_lvl_uom,hr_api.g_varchar2)
692 or not l_api_updating)
693 and p_comp_lvl_uom is not null then
694 --
695 -- check if value of lookup falls within fnd_currencies.
696 --
697 /* if hr_api.not_exists_in_hr_lookups
698 (p_lookup_type => 'HR_UOM',
699 p_lookup_code => p_comp_lvl_uom,
700 p_effective_date => p_effective_date) then
701 --
702 -- raise error as does not exist as lookup
703 --
704 fnd_message.set_name('BEN','BEN_91048_INVALID_UOM');
705 fnd_message.raise_error;
706 --
707 end if; */
708 open c1;
709 fetch c1 into l_dummy;
710 if c1%notfound then
711 close c1;
712 --
713 -- raise error as currency not found
714 --
715 fnd_message.set_name('BEN','BEN_91048_INVALID_UOM');
716 fnd_message.raise_error;
717 end if;
718 close c1;
719 --
720 end if;
721 --
722 hr_utility.set_location('Leaving:'||l_proc,10);
723 --
724 end chk_comp_lvl_uom;
725 --
726 --
727 -- ----------------------------------------------------------------------------
728 -- |------< chk_comp_lvl_det_cd >------|
729 -- ----------------------------------------------------------------------------
730 --
731 -- Description
732 -- This procedure is used to check that the lookup value is valid.
733 --
734 -- Pre Conditions
735 -- None.
736 --
737 -- In Parameters
738 -- comp_lvl_fctr_id PK of record being inserted or updated.
739 -- comp_lvl_det_cd Value of lookup code.
740 -- effective_date effective date
741 -- object_version_number Object version number of record being
742 -- inserted or updated.
743 --
744 -- Post Success
745 -- Processing continues
746 --
747 -- Post Failure
748 -- Error handled by procedure
749 --
750 -- Access Status
751 -- Internal table handler use only.
752 --
753 Procedure chk_comp_lvl_det_cd(p_comp_lvl_fctr_id in number,
754 p_comp_lvl_det_cd in varchar2,
755 p_effective_date in date,
756 p_object_version_number in number) is
757 --
758 l_proc varchar2(72) := g_package||'chk_comp_lvl_det_cd';
759 l_api_updating boolean;
760 --
761 Begin
762 --
763 hr_utility.set_location('Entering:'||l_proc, 5);
764 --
765 l_api_updating := ben_clf_shd.api_updating
766 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
767 p_object_version_number => p_object_version_number);
768 --
769 if (l_api_updating
770 and p_comp_lvl_det_cd
771 <> nvl(ben_clf_shd.g_old_rec.comp_lvl_det_cd,hr_api.g_varchar2)
772 or not l_api_updating)
773 and p_comp_lvl_det_cd is not null then
774 --
775 -- check if value of lookup falls within lookup type.
776 --
777 if hr_api.not_exists_in_hr_lookups
778 (p_lookup_type => 'BEN_COMP_LVL_DET',
779 p_lookup_code => p_comp_lvl_det_cd,
780 p_effective_date => p_effective_date) then
781 --
782 -- raise error as does not exist as lookup
783 --
784 -- Bug 4129562 : Replaced hr_utility calls by fnd_message
785 --
786 -- hr_utility.set_message('BEN','BEN_91268_INV_COMP_LVL_DET_CD');
787 -- hr_utility.raise_error;
788 fnd_message.set_name('BEN','BEN_91268_INV_COMP_LVL_DET_CD');
789 fnd_message.raise_error;
790 --
791 end if;
792 --
793 end if;
794 --
795 hr_utility.set_location('Leaving:'||l_proc,10); --
796 end chk_comp_lvl_det_cd;
797 --
798 -- ----------------------------------------------------------------------------
799 -- |------< chk_sttd_sal_prdcty_cd >------|
800 -- ----------------------------------------------------------------------------
801 --
802 -- Description
803 -- This procedure is used to check that the lookup value is valid.
804 --
805 -- Pre Conditions
806 -- None.
807 --
808 -- In Parameters
809 -- comp_lvl_fctr_id PK of record being inserted or updated.
810 -- sttd_sal_prdcty_cd Value of lookup code.
811 -- effective_date effective date
812 -- object_version_number Object version number of record being
813 -- inserted or updated.
814 --
815 -- Post Success
816 -- Processing continues
817 --
818 -- Post Failure
819 -- Error handled by procedure
820 --
821 -- Access Status
822 -- Internal table handler use only.
823 --
824 Procedure chk_sttd_sal_prdcty_cd(p_comp_lvl_fctr_id in number,
825 p_sttd_sal_prdcty_cd in varchar2,
826 p_effective_date in date,
827 p_object_version_number in number) is
828 --
829 l_proc varchar2(72) := g_package||'chk_sttd_sal_prdcty_cd';
830 l_api_updating boolean;
831 --
832 Begin
833 --
834 hr_utility.set_location('Entering:'||l_proc, 5);
835 --
836 l_api_updating := ben_clf_shd.api_updating
837 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
838 p_object_version_number => p_object_version_number);
839 --
840 if (l_api_updating
841 and p_sttd_sal_prdcty_cd
842 <> nvl(ben_clf_shd.g_old_rec.sttd_sal_prdcty_cd,hr_api.g_varchar2)
843 or not l_api_updating)
844 and p_sttd_sal_prdcty_cd is not null then
845 --
846 -- check if value of lookup falls within lookup type.
847 --
848 if hr_api.not_exists_in_hr_lookups
849 (p_lookup_type => 'BEN_ACTY_REF_PERD',
850 p_lookup_code => p_sttd_sal_prdcty_cd,
851 p_effective_date => p_effective_date) then
852 --
853 -- raise error as does not exist as lookup
854 --
855 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
856 fnd_message.set_token('FIELD', 'p_sttd_sal_prdcty_cd');
857 fnd_message.set_token('TYPE', 'BEN_ACTY_REF_PERD');
858 fnd_message.raise_error;
859 --
860 end if;
861 --
862 end if;
863 --
864 hr_utility.set_location('Leaving:'||l_proc,10); --
865 end chk_sttd_sal_prdcty_cd;
866 --
867 -- ----------------------------------------------------------------------------
868 -- |-----------------< chk_comp_alt_val_to_use_cd >--------------------------|
869 -- ----------------------------------------------------------------------------
870 --
871 -- Description
872 -- This procedure is used to check that the lookup value is valid.
873 --
874 -- Pre Conditions
875 -- None.
876 --
877 -- In Parameters
878 -- comp_lvl_fctr_id PK of record being inserted or updated.
879 -- comp_alt_val_to_use_cd Value of lookup code.
880 -- effective_date effective date
881 -- object_version_number Object version number of record being
882 -- inserted or updated.
883 --
884 -- Post Success
885 -- Processing continues
886 --
887 -- Post Failure
888 -- Error handled by procedure
889 --
890 -- Access Status
891 -- Internal table handler use only.
892 --
893 Procedure chk_comp_alt_val_to_use_cd(p_comp_lvl_fctr_id in number,
894 p_comp_alt_val_to_use_cd in varchar2,
895 p_effective_date in date,
896 p_object_version_number in number) is
897 --
898 l_proc varchar2(72) := g_package||'chk_comp_alt_val_to_use_cd';
899 l_api_updating boolean;
900 --
901 Begin
902 --
903 hr_utility.set_location('Entering:'||l_proc, 5);
904 --
905 l_api_updating := ben_clf_shd.api_updating
906 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
907 p_object_version_number => p_object_version_number);
908 --
909 if (l_api_updating
910 and p_comp_alt_val_to_use_cd
911 <> nvl(ben_clf_shd.g_old_rec.comp_alt_val_to_use_cd,hr_api.g_varchar2)
912 or not l_api_updating)
913 and p_comp_alt_val_to_use_cd is not null then
914 --
915 -- check if value of lookup falls within lookup type.
916 --
917 if hr_api.not_exists_in_hr_lookups
918 (p_lookup_type => 'BEN_COMP_ALT_VAL_TO_USE',
919 p_lookup_code => p_comp_alt_val_to_use_cd,
920 p_effective_date => p_effective_date) then
921 --
922 -- raise error as does not exist as lookup
923 --
924 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
925 fnd_message.set_token('FIELD', 'p_comp_alt_val_to_use_cd');
926 fnd_message.set_token('TYPE', 'BEN_COMP_ALT_VAL_TO_USE');
927 fnd_message.raise_error;
928 --
929 end if;
930 --
931 end if;
932 --
933 hr_utility.set_location('Leaving:'||l_proc,10); --
934 end chk_comp_alt_val_to_use_cd;
935 --
936 -- ----------------------------------------------------------------------------
937 -- |---------------------< chk_comp_calc_rl >------------------------------|
938 -- ----------------------------------------------------------------------------
939 --
940 -- Description
941 -- This procedure is used to check that the Formula Rule is valid.
942 --
943 -- Pre Conditions
944 -- None.
945 --
946 -- In Parameters
947 -- comp_lvl_fctr_id PK of record being inserted or updated.
948 -- comp_calc_rl Value of formula rule id.
949 -- effective_date effective date
950 -- object_version_number Object version number of record being
951 -- inserted or updated.
952 --
953 -- Post Success
954 -- Processing continues
955 --
956 -- Post Failure
957 -- Error handled by procedure
958 --
959 -- Access Status
960 -- Internal table handler use only.
961 --
962 Procedure chk_comp_calc_rl(p_comp_lvl_fctr_id in number,
963 p_business_group_id in number,
964 p_comp_calc_rl in number,
965 p_effective_date in date,
966 p_object_version_number in number) is
967 --
968 l_proc varchar2(72) := g_package||'chk_comp_calc_rl';
969 l_api_updating boolean;
970 l_dummy varchar2(1);
971 --
972 cursor c1 is
973 select null
974 from ff_formulas_f ff
975 ,per_business_groups pbg
976 where ff.formula_id = p_comp_calc_rl
977 and ff.formula_type_id = -517
978 and pbg.business_group_id = p_business_group_id
979 and nvl(ff.business_group_id, p_business_group_id) =
980 p_business_group_id
981 and nvl(ff.legislation_code, pbg.legislation_code) =
982 pbg.legislation_code
983 and p_effective_date
984 between ff.effective_start_date
985 and ff.effective_end_date;
986 --
987 Begin
988 --
989 hr_utility.set_location('Entering:'||l_proc, 5);
990 --
991 l_api_updating := ben_clf_shd.api_updating
992 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
993 p_object_version_number => p_object_version_number);
994 --
995 if (l_api_updating
996 and nvl(p_comp_calc_rl,hr_api.g_number)
997 <> ben_clf_shd.g_old_rec.comp_calc_rl
998 or not l_api_updating)
999 and p_comp_calc_rl is not null then
1000 --
1001 -- check if value of formula rule is valid.
1002 --
1003 open c1;
1004 --
1005 -- fetch value from cursor if it returns a record then the
1006 -- formula is valid otherwise its invalid
1007 --
1008 fetch c1 into l_dummy;
1009 if c1%notfound then
1010 --
1011 close c1;
1012 --
1013 -- raise error
1014 --
1015 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1016 fnd_message.set_token('ID',p_comp_calc_rl);
1017 fnd_message.set_token('TYPE_ID',-517);
1018 fnd_message.raise_error;
1019 --
1020 end if;
1021 --
1022 close c1;
1023 --
1024 end if;
1025 --
1026 hr_utility.set_location('Leaving:'||l_proc,10);
1027 --
1028 end chk_comp_calc_rl;
1029 --
1030 ------------------------------------------------------------------------
1031 ----
1032 -- |------< chk_name >------|
1033 --
1034 ------------------------------------------------------------------------
1035 ----
1036 --
1037 -- Description
1038 -- This procedure is used to check that the Name is unique in a business group.
1039 --
1040 -- Pre Conditions
1041 -- None.
1042 --
1043 -- In Parameters
1044 -- comp_lvl_fctr_id PK of record being inserted or updated.
1045 -- name Value of Name.
1046 -- effective_date effective date
1047 -- object_version_number Object version number of record being
1048 -- inserted or updated.
1049 --
1050 -- Post Success
1051 -- Processing continues
1052 --
1053 -- Post Failure
1054 -- Error handled by procedure
1055 --
1056 -- Access Status
1057 -- Internal table handler use only.
1058 --
1059 Procedure chk_name(p_comp_lvl_fctr_id in number,
1060 p_business_group_id in number,
1061 p_name in varchar2,
1062 p_effective_date in date,
1063 p_object_version_number in number) is
1064 --
1065 l_proc varchar2(72):= g_package||'chk_name';
1066 l_api_updating boolean;
1067 l_dummy varchar2(1);
1068 --
1069 cursor c1 is
1070 select null
1071 from ben_comp_lvl_fctr clf
1072 where clf.business_group_id = p_business_group_id and
1073 clf.name = p_name;
1074 Begin
1075 --
1076 hr_utility.set_location('Entering:'||l_proc, 5);
1077 --
1078 l_api_updating := ben_clf_shd.api_updating
1079 (p_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
1080 -- p_effective_date => p_effective_date,
1081 p_object_version_number => p_object_version_number);
1082 --
1083 if (l_api_updating
1084 and nvl(p_name,hr_api.g_varchar2)
1085 <> ben_clf_shd.g_old_rec.name
1086 or not l_api_updating)
1087 and p_name is not null then
1088 --
1089 -- check if name already used.
1090 --
1091 open c1;
1092 --
1093 -- fetch value from cursor if it returns a record then the
1094
1095 -- name is invalid otherwise its valid
1096 --
1097 fetch c1 into l_dummy;
1098 if c1%found then
1099 --
1100 close c1;
1101 --
1102 -- raise error
1103 --
1104 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
1105 fnd_message.raise_error;
1106 --
1107 end if;
1108 --
1109 close c1;
1110 end if;
1111 --
1112 hr_utility.set_location('Leaving:'||l_proc,10);
1113 --
1114 end chk_name;
1115 --
1116 -----------------------------------------------------------------------------
1117 -- |--------------------------------< chk_day_mo>---------------------------|
1118 -----------------------------------------------------------------------------
1119 --
1120 -- Description
1121 -- This procedure is used to check the date format
1122 --
1123 -- Pre Conditions
1124 -- None.
1125 --
1126 -- In Parameters
1127 -- comp_lvl_fctr_id PK of record being inserted or updated.
1128 -- day_mo Value of Minimum hours worked.
1129 --
1130 -- Post Success
1131 -- Processing continues
1132 --
1133 -- Post Failure
1134 -- Error handled by procedure
1135 --
1136 -- Access Status
1137 -- Internal table handler use only.
1138 --
1139 Procedure chk_day_mo(p_year in varchar2,
1140 p_day_mo in varchar2,
1141 p_label in varchar2) is
1142 --
1143 l_proc varchar2(72) := g_package || 'chk_day_mo';
1144 l_year varchar2(4) := nvl(p_year,'2000'); --pick a leap year
1145 l_date date;
1146 --
1147 Begin
1148 --
1149 hr_utility.set_location('Entering:'||l_proc, 5);
1150 hr_utility.set_location('p_day_mo '||p_day_mo, 5);
1151 hr_utility.set_location('p_year '||p_year, 5);
1152 --
1153 if p_day_mo is null then
1154 hr_api.mandatory_arg_error(p_api_name => l_proc,
1155 p_argument => p_label,
1156 p_argument_value => '');
1157 end if;
1158 l_date := to_date(substr('0'||p_day_mo,-4)||l_year,'ddmmyyyy');
1159 --
1160 hr_utility.set_location('Leaving:'||l_proc,10);
1161 Exception
1162 when others then
1163 fnd_message.set_name('BEN','BEN_92603_INVALID_DATE');
1164 fnd_message.raise_error;
1165 End chk_day_mo;
1166 --
1167 -----------------------------------------------------------------------------
1168 -- |--------------------------------< chk_date_range>------------------------|
1169 -----------------------------------------------------------------------------
1170 --
1171 -- Description
1172 -- This procedure is used to check the date format
1173 --
1174 -- Pre Conditions
1175 -- None.
1176 --
1177 -- In Parameters
1178 -- comp_lvl_fctr_id PK of record being inserted or updated.
1179 -- day_mo Value of Minimum hours worked.
1180 --
1181 -- Post Success
1182 -- Processing continues
1183 --
1184 -- Post Failure
1185 -- Error handled by procedure
1186 --
1187 -- Access Status
1188 -- Internal table handler use only.
1189 --
1190 Procedure chk_date_range(p_clf_rec in ben_clf_shd.g_rec_type) is
1191 --
1192 l_proc varchar2(72) := g_package || 'chk_date_range';
1193 l_start_year varchar2(4) := nvl(p_clf_rec.start_year,'2000');
1194 l_end_year varchar2(4) := nvl(p_clf_rec.end_year,'2000');
1195 l_date1 date;
1196 l_date2 date;
1197 --
1198 Begin
1199 --
1200 hr_utility.set_location('Entering:'||l_proc, 5);
1201 --
1202 if p_clf_rec.start_year is not null and
1203 p_clf_rec.end_year is not null then
1204 l_date1 := to_date(substr('0'||p_clf_rec.start_day_mo,-4)||l_start_year,'ddmmyyyy');
1205 l_date2 := to_date(substr('0'||p_clf_rec.end_day_mo,-4)||l_end_year,'ddmmyyyy');
1206 if l_date2 < l_date1 then
1207 fnd_message.set_name('BEN','BEN_91824_START_DT_AFTR_END_DT');
1208 fnd_message.set_token('PROC',l_proc);
1209 fnd_message.set_token('START_DT',l_date1);
1210 fnd_message.set_token('END_DT',l_date2);
1211 fnd_message.raise_error;
1212 end if;
1213 end if;
1214 --
1215 hr_utility.set_location('Leaving:'||l_proc,10);
1216
1217 End chk_date_range;
1218 --
1219 -----------------------------------------------------------------------------
1220 -- |-----------------< chk_mn_mx_comp_val>--------------------|
1221 -----------------------------------------------------------------------------
1222 --
1223 -- Description
1224 -- This procedure is used to check that minimum compensation value is
1225 -- always less than max compensation value .
1226 --
1227 -- Pre Conditions
1228 -- None.
1229 --
1230 -- In Parameters
1231 -- comp_lvl_fctr_id PK of record being inserted or updated.
1232 -- mn_hrs_num Value of Minimum hours worked.
1233 -- mx_hrs_num Value of Maximum hours worked.
1234 -- effective_date effective date
1235 -- object_version_number Object version number of record being
1236 -- inserted or updated.
1237 --
1238 -- Post Success
1239 -- Processing continues
1240 --
1241 -- Post Failure
1242 -- Error handled by procedure
1243 --
1244 -- Access Status
1245 -- Internal table handler use only.
1246 --
1247 Procedure chk_mn_mx_comp_val(p_comp_lvl_fctr_id in number,
1248 p_no_mn_comp_flag in varchar2,
1249 p_mn_comp_val in number,
1250 p_no_mx_comp_flag in varchar2,
1251 p_mx_comp_val in number,
1252 p_object_version_number in number) is
1253 --
1254 l_proc varchar2(72) := g_package || 'chk_mn_mx_comp_val';
1255 l_api_updating boolean;
1256 l_dummy varchar2(1);
1257 --
1258 Begin
1259 --
1260 hr_utility.set_location('Entering:'||l_proc, 5);
1261 --
1262 -- Minimum compensation value must be < Maximum compensation value ,
1263 -- if both are used.
1264 --
1265 if p_mn_comp_val is not null and
1266 p_mx_comp_val is not null then
1267 --
1268 -- raise error if max value not greater than min value
1269 --
1270 -- Bug fix 1873685
1271 if p_mx_comp_val < p_mn_comp_val then
1272 -- if p_mx_comp_val <= p_mn_comp_val then
1273 -- end fix 1873685
1274 --
1275 fnd_message.set_name('BEN','BEN_91069_INVALID_MIN_MAX');
1276 fnd_message.raise_error;
1277 --
1278 end if;
1279 --
1280 end if;
1281 --
1282 -- If No Minimum hours flag set to "on" (Y),
1283 -- then minimum hours number must be blank.
1284 --
1285 if p_no_mn_comp_flag = 'Y' and
1286 p_mn_comp_val is not null then
1287 --
1288 fnd_message.set_name('BEN','BEN_91054_MIN_VAL_NOT_NULL');
1289 fnd_message.raise_error;
1290 --
1291 elsif p_no_mn_comp_flag = 'N' and
1292 p_mn_comp_val is null then
1293 --
1294 fnd_message.set_name('BEN','BEN_91055_MIN_VAL_REQUIRED');
1295 fnd_message.raise_error;
1296 --
1297 end if;
1298 --
1299 -- If No Maximum comp flag set to "on" (Y),
1300 -- then maximum comp number and must be blank.
1301 --
1302 if p_no_mx_comp_flag = 'Y' and
1303 p_mx_comp_val is not null then
1304 --
1305 fnd_message.set_name('BEN','BEN_91056_MAX_VAL_NOT_NULL');
1306 fnd_message.raise_error;
1307 --
1308 elsif p_no_mx_comp_flag = 'N' and
1309 p_mx_comp_val is null then
1310 --
1311 fnd_message.set_name('BEN','BEN_91057_MAX_VAL_REQUIRED');
1312 fnd_message.raise_error;
1313 --
1314 end if;
1315 --
1316 hr_utility.set_location('Leaving:'||l_proc,10);
1317 --
1318 end chk_mn_mx_comp_val;
1319 --
1320 -------------------------------------------------------------------------------
1321 -- |--------------------------< chk_source >---------------------------------|
1322 -------------------------------------------------------------------------------
1323 ----
1324 --
1325 -- Description
1326 -- This procedure checks to make sure that the Defined Balance is not null
1327 -- if the comp_src_cd = BALTYP and that the Benefits Balance Type is not null
1328 -- if the comp_src_cd = BNFTBALTYP
1329 --
1330 -- Pre Conditions
1331 -- None.
1332 --
1333 -- In Parameters
1334 -- comp_src_cd Source Code.
1335 -- defined_balance_id Defined Balance.
1336 -- bnfts_bal_id Benefits Balance Type.
1337 -- inserted or updated.
1338 --
1339 -- Post Success
1340 -- Processing continues
1341 --
1342 -- Post Failure
1343 -- Error handled by procedure
1344 --
1345 -- Access Status
1346 -- Internal table handler use only.
1347 --
1348 Procedure chk_source(p_comp_src_cd in varchar2,
1349 p_defined_balance_id in number,
1350 p_bnfts_bal_id in number) is
1351 --
1352 l_proc varchar2(72) := g_package || 'chk_source';
1353 --
1354 Begin
1355 --
1356 hr_utility.set_location('Entering:'||l_proc, 5);
1357 --
1358 -- Defined Balance must be entered if "Balance Type" is selected for
1359 -- Source.
1360 --
1361 if p_comp_src_cd = 'BALTYP' and p_defined_balance_id is null then
1362 --
1363 fnd_message.set_name('BEN','BEN_91975_DEFINED_BALANCE');
1364 fnd_message.raise_error;
1365 --
1366 elsif p_comp_src_cd = 'BNFTBALTYP' and p_bnfts_bal_id is null then
1367 --
1368 fnd_message.set_name('BEN','BEN_91976_BNFTS_BALANCE_TYP');
1369 fnd_message.raise_error;
1370 --
1371 end if;
1372 --
1373 hr_utility.set_location('Leaving:'||l_proc,10);
1374 --
1375 end chk_source;
1376 --
1377 -- Bug 2978945 begin
1378
1379 -- ----------------------------------------------------------------------- --
1380 -- -----------------------< chk_child_records >-----------------------------|
1381 -- -------------------------------------------------------------------------
1382 --
1383 -- Description
1384 -- This procedure is used to check that compensation level factor child records
1385 -- do not exist when the user deletes the record in the
1386 -- ben_comp_lvl_fctr table.
1387 --
1388 -- Pre Conditions
1389 -- None.
1390 --
1391 -- In Parameters
1392 -- comp_lvl_fctr_id PK of record being inserted or updated.
1393 --
1394 -- Post Success
1395 -- Processing continues
1396 --
1397 -- Post Failure
1398 -- Error handled by procedure
1399 --
1400 -- Access Status
1401 -- Internal table handler use only.
1402 --
1403 procedure chk_child_records(p_comp_lvl_fctr_id in number) is
1404 --
1405 l_proc varchar2(72):= g_package||'chk_child_records';
1406 v_dummy varchar2(1);
1407
1408
1409 begin
1410 --
1411 hr_utility.set_location('Entering:'||l_proc, 5);
1412
1413 --Used in Standard Rates
1414 If (ben_batch_utils.rows_exist
1415 (p_base_table_name => 'BEN_ACTY_BASE_RT_F',
1416 p_base_key_column => 'comp_lvl_fctr_id',
1417 p_base_key_value => p_comp_lvl_fctr_id
1418 )) Then
1419 ben_utility.child_exists_error('BEN_ACTY_BASE_RT_F');
1420 End If;
1421
1422 --Used in Coverages Calculation Method
1423 If (ben_batch_utils.rows_exist
1424 (p_base_table_name => 'BEN_CVG_AMT_CALC_MTHD_F',
1425 p_base_key_column => 'comp_lvl_fctr_id',
1426 p_base_key_value => p_comp_lvl_fctr_id
1427 )) Then
1428 ben_utility.child_exists_error('BEN_CVG_AMT_CALC_MTHD_F');
1429 End If;
1430
1431 --Used in eligibility profiles
1432 If (ben_batch_utils.rows_exist
1433 (p_base_table_name => 'BEN_ELIG_COMP_LVL_PRTE_F',
1434 p_base_key_column => 'comp_lvl_fctr_id',
1435 p_base_key_value => p_comp_lvl_fctr_id
1436 )) Then
1437 ben_utility.child_exists_error('BEN_ELIG_COMP_LVL_PRTE_F');
1438 --Raise l_rows_exist;
1439 End If;
1440
1441 --Used in variable rate profiles criteria
1442 If (ben_batch_utils.rows_exist
1443 (p_base_table_name => 'BEN_COMP_LVL_RT_F',
1444 p_base_key_column => 'comp_lvl_fctr_id',
1445 p_base_key_value => p_comp_lvl_fctr_id
1446 )) Then
1447 ben_utility.child_exists_error('BEN_COMP_LVL_RT_F');
1448 End If;
1449
1450 --Used in variable rate profiles
1451 If (ben_batch_utils.rows_exist
1452 (p_base_table_name => 'BEN_VRBL_RT_PRFL_F',
1453 p_base_key_column => 'comp_lvl_fctr_id',
1454 p_base_key_value => p_comp_lvl_fctr_id
1455 )) Then
1456 ben_utility.child_exists_error('BEN_VRBL_RT_PRFL_F');
1457 End If;
1458
1459 --Used in Period To Date limits
1460 If (ben_batch_utils.rows_exist
1461 (p_base_table_name => 'BEN_PTD_LMT_F',
1462 p_base_key_column => 'comp_lvl_fctr_id',
1463 p_base_key_value => p_comp_lvl_fctr_id
1464 )) Then
1465 ben_utility.child_exists_error('BEN_PTD_LMT_F');
1466 End If;
1467
1468
1469 --Used in Benefit Pools
1470 If (ben_batch_utils.rows_exist
1471 (p_base_table_name => 'BEN_BNFT_PRVDR_POOL_F',
1472 p_base_key_column => 'comp_lvl_fctr_id',
1473 p_base_key_value => p_comp_lvl_fctr_id
1474 )) Then
1475 ben_utility.child_exists_error('BEN_BNFT_PRVDR_POOL_F');
1476 End If;
1477
1478 hr_utility.set_location('Leaving:'||l_proc,10);
1479 --
1480 end chk_child_records;
1481
1482 -- Bug 2978945 end
1483
1484 -- ----------------------------------------------------------------------------
1485 -- |---------------------------< insert_validate >----------------------------|
1486 -- ----------------------------------------------------------------------------
1487 Procedure insert_validate(p_rec in ben_clf_shd.g_rec_type
1488 ,p_effective_date in date) is
1489 --
1490 l_proc varchar2(72) := g_package||'insert_validate';
1491 --
1492 Begin
1493 hr_utility.set_location('Entering:'||l_proc, 5);
1494 --
1495 -- Call all supporting business operations
1496 --
1497 --
1498 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1499 --
1500 chk_comp_lvl_fctr_id
1501 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1502 p_object_version_number => p_rec.object_version_number);
1503 --
1504 chk_rndg_cd
1505 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1506 p_rndg_cd => p_rec.rndg_cd,
1507 p_effective_date => p_effective_date,
1508 p_object_version_number => p_rec.object_version_number);
1509 --
1510 chk_defined_balance_id
1511 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1512 p_business_group_id => p_rec.business_group_id,
1513 p_defined_balance_id => p_rec.defined_balance_id,
1514 p_object_version_number => p_rec.object_version_number);
1515 --
1516 chk_rndg_rl
1517 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1518 p_business_group_id => p_rec.business_group_id,
1519 p_rndg_rl => p_rec.rndg_rl,
1520 p_rndg_cd => p_rec.rndg_cd,
1521 p_effective_date => p_effective_date,
1522 p_object_version_number => p_rec.object_version_number);
1523 --
1524 chk_comp_lvl_det_rl
1525 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1526 p_business_group_id => p_rec.business_group_id,
1527 p_comp_lvl_det_rl => p_rec.comp_lvl_det_rl,
1528 p_comp_lvl_det_cd => p_rec.comp_lvl_det_cd, -- Bug No 4242978
1529 p_effective_date => p_effective_date,
1530 p_object_version_number => p_rec.object_version_number);
1531 --
1532 chk_no_mx_comp_flag
1533 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1534 p_no_mx_comp_flag => p_rec.no_mx_comp_flag,
1535 p_effective_date => p_effective_date,
1536 p_object_version_number => p_rec.object_version_number);
1537 --
1538 chk_no_mn_comp_flag
1539 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1540 p_no_mn_comp_flag => p_rec.no_mn_comp_flag,
1541 p_effective_date => p_effective_date,
1542 p_object_version_number => p_rec.object_version_number);
1543 --
1544 chk_comp_src_cd
1545 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1546 p_comp_src_cd => p_rec.comp_src_cd,
1547 p_effective_date => p_effective_date,
1548 p_object_version_number => p_rec.object_version_number);
1549 --
1550 chk_comp_lvl_uom
1551 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1552 p_comp_lvl_uom => p_rec.comp_lvl_uom,
1553 p_effective_date => p_effective_date,
1554 p_object_version_number => p_rec.object_version_number);
1555 --
1556 chk_mn_mx_comp_val
1557 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1558 p_no_mn_comp_flag => p_rec.no_mn_comp_flag,
1559 p_mn_comp_val => p_rec.mn_comp_val,
1560 p_no_mx_comp_flag => p_rec.no_mx_comp_flag,
1561 p_mx_comp_val => p_rec.mx_comp_val,
1562 p_object_version_number => p_rec.object_version_number);
1563 --
1564 chk_name
1565 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1566 p_business_group_id => p_rec.business_group_id,
1567 p_name => p_rec.name,
1568 p_effective_date => p_effective_date,
1569 p_object_version_number => p_rec.object_version_number);
1570 --
1571 chk_comp_lvl_det_cd
1572 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1573 p_comp_lvl_det_cd => p_rec.comp_lvl_det_cd,
1574 p_effective_date => p_effective_date,
1575 p_object_version_number => p_rec.object_version_number);
1576 --
1577 chk_sttd_sal_prdcty_cd
1578 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1579 p_sttd_sal_prdcty_cd => p_rec.sttd_sal_prdcty_cd,
1580 p_effective_date => p_effective_date,
1581 p_object_version_number => p_rec.object_version_number);
1582 --
1583 chk_comp_alt_val_to_use_cd
1584 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1585 p_comp_alt_val_to_use_cd => p_rec.comp_alt_val_to_use_cd,
1586 p_effective_date => p_effective_date,
1587 p_object_version_number => p_rec.object_version_number);
1588 --
1589 chk_comp_calc_rl
1590 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1591 p_business_group_id => p_rec.business_group_id,
1592 p_comp_calc_rl => p_rec.comp_calc_rl,
1593 p_effective_date => p_effective_date,
1594 p_object_version_number => p_rec.object_version_number);
1595 --
1596 chk_source
1597 (p_comp_src_cd => p_rec.comp_src_cd,
1598 p_defined_balance_id => p_rec.defined_balance_id,
1599 p_bnfts_bal_id => p_rec.bnfts_bal_id);
1600 --
1601 if p_rec.comp_src_cd in ('OICAMTEARNED','OICAMTPAID') then
1602 chk_day_mo
1603 (p_day_mo => p_rec.start_day_mo,
1604 p_year => p_rec.start_year,
1605 p_label => 'p_start_DDMM');
1606 --
1607 chk_day_mo
1608 (p_day_mo => p_rec.end_day_mo,
1609 p_year => p_rec.end_year,
1610 p_label => 'p_end_DDMM');
1611 --
1612 chk_date_range
1613 (p_clf_rec => p_rec);
1614 end if;
1615 --
1616 hr_utility.set_location(' Leaving:'||l_proc, 10);
1617 End insert_validate;
1618 --
1619 -- ----------------------------------------------------------------------------
1620 -- |---------------------------< update_validate >----------------------------|
1621 -- ----------------------------------------------------------------------------
1622 Procedure update_validate(p_rec in ben_clf_shd.g_rec_type
1623 ,p_effective_date in date) is
1624 --
1625 l_proc varchar2(72) := g_package||'update_validate';
1626 --
1627 Begin
1628 hr_utility.set_location('Entering:'||l_proc, 5);
1629 --
1630 -- Call all supporting business operations
1631 --
1632 --
1633 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1634 --
1635 chk_comp_lvl_fctr_id
1636 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1637 p_object_version_number => p_rec.object_version_number);
1638 --
1639 chk_rndg_cd
1640 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1641 p_rndg_cd => p_rec.rndg_cd,
1642 p_effective_date => p_effective_date,
1643 p_object_version_number => p_rec.object_version_number);
1644 --
1645 chk_defined_balance_id
1646 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1647 p_business_group_id => p_rec.business_group_id,
1648 p_defined_balance_id => p_rec.defined_balance_id,
1649 p_object_version_number => p_rec.object_version_number);
1650 --
1651 chk_rndg_rl
1652 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1653 p_business_group_id => p_rec.business_group_id,
1654 p_rndg_rl => p_rec.rndg_rl,
1655 p_rndg_cd => p_rec.rndg_cd,
1656 p_effective_date => p_effective_date,
1657 p_object_version_number => p_rec.object_version_number);
1658 --
1659 chk_comp_lvl_det_rl
1660 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1661 p_business_group_id => p_rec.business_group_id,
1662 p_comp_lvl_det_rl => p_rec.comp_lvl_det_rl,
1663 p_comp_lvl_det_cd => p_rec.comp_lvl_det_cd, -- Bug No 4242978
1664 p_effective_date => p_effective_date,
1665 p_object_version_number => p_rec.object_version_number);
1666 --
1667 chk_no_mx_comp_flag
1668 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1669 p_no_mx_comp_flag => p_rec.no_mx_comp_flag,
1670 p_effective_date => p_effective_date,
1671 p_object_version_number => p_rec.object_version_number);
1672 --
1673 chk_no_mn_comp_flag
1674 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1675 p_no_mn_comp_flag => p_rec.no_mn_comp_flag,
1676 p_effective_date => p_effective_date,
1677 p_object_version_number => p_rec.object_version_number);
1678 --
1679 chk_comp_src_cd
1680 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1681 p_comp_src_cd => p_rec.comp_src_cd,
1682 p_effective_date => p_effective_date,
1683 p_object_version_number => p_rec.object_version_number);
1684 --
1685 chk_comp_lvl_uom
1686 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1687 p_comp_lvl_uom => p_rec.comp_lvl_uom,
1688 p_effective_date => p_effective_date,
1689 p_object_version_number => p_rec.object_version_number);
1690
1691 --
1692 chk_mn_mx_comp_val
1693 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1694 p_no_mn_comp_flag => p_rec.no_mn_comp_flag,
1695 p_mn_comp_val => p_rec.mn_comp_val,
1696 p_no_mx_comp_flag => p_rec.no_mx_comp_flag,
1697 p_mx_comp_val => p_rec.mx_comp_val,
1698 p_object_version_number => p_rec.object_version_number);
1699 --
1700 chk_name
1701 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1702 p_business_group_id => p_rec.business_group_id,
1703 p_name => p_rec.name,
1704 p_effective_date => p_effective_date,
1705 p_object_version_number => p_rec.object_version_number);
1706 --
1707 --
1708 chk_comp_lvl_det_cd
1709 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1710 p_comp_lvl_det_cd => p_rec.comp_lvl_det_cd,
1711 p_effective_date => p_effective_date,
1712 p_object_version_number => p_rec.object_version_number);
1713 --
1714 chk_sttd_sal_prdcty_cd
1715 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1716 p_sttd_sal_prdcty_cd => p_rec.sttd_sal_prdcty_cd,
1717 p_effective_date => p_effective_date,
1718 p_object_version_number => p_rec.object_version_number);
1719 --
1720 chk_comp_alt_val_to_use_cd
1721 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1722 p_comp_alt_val_to_use_cd => p_rec.comp_alt_val_to_use_cd,
1723 p_effective_date => p_effective_date,
1724 p_object_version_number => p_rec.object_version_number);
1725 --
1726 chk_comp_calc_rl
1727 (p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id,
1728 p_business_group_id => p_rec.business_group_id,
1729 p_comp_calc_rl => p_rec.comp_calc_rl,
1730 p_effective_date => p_effective_date,
1731 p_object_version_number => p_rec.object_version_number);
1732 --
1733 chk_source
1734 (p_comp_src_cd => p_rec.comp_src_cd,
1735 p_defined_balance_id => p_rec.defined_balance_id,
1736 p_bnfts_bal_id => p_rec.bnfts_bal_id);
1737 --
1738 if p_rec.comp_src_cd in ('OICAMTEARNED','OICAMTPAID') then
1739 chk_day_mo
1740 (p_day_mo => p_rec.start_day_mo,
1741 p_year => p_rec.start_year,
1742 p_label => 'p_start_DDMM');
1743 --
1744 chk_day_mo
1745 (p_day_mo => p_rec.end_day_mo,
1746 p_year => p_rec.end_year,
1747 p_label => 'p_end_DDMM');
1748 --
1749 chk_date_range
1750 (p_clf_rec => p_rec);
1751 end if;
1752 --
1753 hr_utility.set_location(' Leaving:'||l_proc, 10);
1754 End update_validate;
1755 --
1756 -- ----------------------------------------------------------------------------
1757 -- |---------------------------< delete_validate >----------------------------|
1758 -- ----------------------------------------------------------------------------
1759 Procedure delete_validate(p_rec in ben_clf_shd.g_rec_type
1760 ,p_effective_date in date) is
1761 --
1762 l_proc varchar2(72) := g_package||'delete_validate';
1763 --
1764 Begin
1765 hr_utility.set_location('Entering:'||l_proc, 5);
1766 --
1767 -- Call all supporting business operations
1768 --
1769
1770 chk_child_records(p_comp_lvl_fctr_id => p_rec.comp_lvl_fctr_id); -- Bug 2978945
1771
1772 hr_utility.set_location(' Leaving:'||l_proc, 10);
1773 End delete_validate;
1774 --
1775 --
1776 -- ---------------------------------------------------------------------------
1777 -- |---------------------< return_legislation_code >-------------------------|
1778 -- ---------------------------------------------------------------------------
1779 --
1780 function return_legislation_code
1781 (p_comp_lvl_fctr_id in number) return varchar2 is
1782 --
1783 -- Declare cursor
1784 --
1785 cursor csr_leg_code is
1786 select a.legislation_code
1787 from per_business_groups a,
1788 ben_comp_lvl_fctr b
1789 where b.comp_lvl_fctr_id = p_comp_lvl_fctr_id
1790 and a.business_group_id = b.business_group_id;
1791 --
1792 -- Declare local variables
1793 --
1794 l_legislation_code varchar2(150);
1795 l_proc varchar2(72) := g_package||'return_legislation_code';
1796 --
1797 begin
1798 --
1799 hr_utility.set_location('Entering:'|| l_proc, 10);
1800 --
1801 -- Ensure that all the mandatory parameter are not null
1802 --
1803 hr_api.mandatory_arg_error(p_api_name => l_proc,
1804 p_argument => 'comp_lvl_fctr_id',
1805 p_argument_value => p_comp_lvl_fctr_id);
1806 --
1807 open csr_leg_code;
1808 --
1809 fetch csr_leg_code into l_legislation_code;
1810 --
1811 if csr_leg_code%notfound then
1812 --
1813 close csr_leg_code;
1814 --
1815 -- The primary key is invalid therefore we must error
1816 --
1817 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1818 fnd_message.raise_error;
1819 --
1820 end if;
1821 --
1822 close csr_leg_code;
1823 --
1824 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1825 --
1826 return l_legislation_code;
1827 --
1828 end return_legislation_code;
1829 --
1830 end ben_clf_bus;