[Home] [Help]
PACKAGE BODY: APPS.BEN_EGL_BUS
Source
4 -- ----------------------------------------------------------------------------
1 Package Body ben_egl_bus as
2
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_egl_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_eligy_criteria_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 -- eligy_criteria_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_eligy_criteria_id(p_eligy_criteria_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_eligy_criteria_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_egl_shd.api_updating
47 (p_eligy_criteria_id => p_eligy_criteria_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_eligy_criteria_id,hr_api.g_number)
52 <> ben_egl_shd.g_old_rec.eligy_criteria_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_egl_shd.constraint_error('BEN_eligy_criteria_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_eligy_criteria_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_egl_shd.constraint_error('BEN_eligy_criteria_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_eligy_criteria_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------------------------< chk_name_unique >-------------------------------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- ensure that the Name is unique
82 -- within business_group
83 --
84 -- Pre Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_name eligibility criteria name
89 -- p_eligy_criteria_id is eligy_criteria_id
90 -- p_business_group_id
91 --
92 -- Post Success
93 -- Processing continues
94 --
95 -- Post Failure
96 -- Errors handled by the procedure
97 --
98 -- Access Status
99 -- Internal table handler use only.
100 ---- ----------------------------------------------------------------------------
101 Procedure chk_name_unique
102 ( p_eligy_criteria_id in number
103 ,p_name in varchar2
104 ,p_business_group_id in number)
105 is
106 l_proc varchar2(72) := g_package||'chk_name_unique';
107 l_dummy char(1);
108
109 cursor c1 is select null
110 from ben_eligy_criteria
111 Where eligy_criteria_id <> nvl(p_eligy_criteria_id,-1)
112 and name = p_name
113 and business_group_id = p_business_group_id;
114
115 --
116 Begin
117 hr_utility.set_location('Entering:'||l_proc, 5);
118 --
119 open c1;
120 fetch c1 into l_dummy;
121 if c1%found then
122 close c1;
123 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
124 fnd_message.raise_error;
125 end if;
126 close c1;
127 --
128 hr_utility.set_location('Leaving:'||l_proc, 15);
129 --
130 End chk_name_unique;
131
132 /* Bug 5338058 - Commenting this check as short_code need not to be unique
133 --
134 -- ----------------------------------------------------------------------------
135 -- |------------------------< chk_short_code_unique >-------------------------------|
139 -- ensure that the short code is unique
136 -- ----------------------------------------------------------------------------
137 --
138 -- Description
140 -- within business_group
141 --
142 -- Pre Conditions
143 -- None.
144 --
145 -- In Parameters
146 -- p_name is
147 -- p_eligy_criteria_id is eligy_criteria_id
148 -- p_business_group_id
149 --
150 -- Post Success
151 -- Processing continues
152 --
153 -- Post Failure
154 -- Errors handled by the procedure
155 --
156 -- Access Status
157 -- Internal table handler use only.
158 ---- ----------------------------------------------------------------------------
159 Procedure chk_short_code_unique
160 ( p_eligy_criteria_id in number
161 ,p_short_code in varchar2
162 ,p_business_group_id in number)
163 is
164 l_proc varchar2(72) := g_package||'chk_short_code_unique';
165 l_dummy char(1);
166
167 cursor c1 is select null
168 from ben_eligy_criteria
169 Where eligy_criteria_id <> nvl(p_eligy_criteria_id,-1)
170 and short_code = p_short_code
171 and business_group_id = p_business_group_id;
172
173 --
174 Begin
175 hr_utility.set_location('Entering:'||l_proc, 5);
176 --
177 open c1;
178 fetch c1 into l_dummy;
179 if c1%found then
180 close c1;
181 fnd_message.set_name('BEN','BEN_94151_NOT_UNIQUE');
182 fnd_message.set_token('FIELD','Short Code');
183 fnd_message.raise_error;
184 end if;
185 --
186 hr_utility.set_location('Leaving:'||l_proc, 15);
187
188 End chk_short_code_unique;
189 --
190 */
191
192 --
193 --
194 -- ----------------------------------------------------------------------------
195 -- |----------------------------< chk_all_lookups >---------------------------|
196 -- ----------------------------------------------------------------------------
197 --
198 -- Description
199 -- This procedure is used to check that the lookup codes are valid.
200 --
201 -- Pre Conditions
202 -- None.
203 --
204 -- In Parameters
205 -- p_eligy_criteria_id PK of record being inserted or updated.
206 -- p_criteria_type value of lookup code
207 -- p_crit_col1_val_type_cd value of lookup code
208 -- p_crit_col1_datatype value of lookup code
209 --
210 -- effective_date effective date
211 -- object_version_number Object version number of record being
212 -- inserted or updated.
213 --
214 -- Post Success
215 -- Processing continues
216 --
217 -- Post Failure
218 -- Error handled by procedure
219 --
220 -- Access Status
221 -- Internal table handler use only.
222 --
223 Procedure chk_all_lookups(p_eligy_criteria_id in number,
224 p_criteria_type in varchar2,
225 p_crit_col1_val_type_cd in varchar2,
226 p_crit_col1_datatype in varchar2,
227 p_crit_col2_val_type_cd in varchar2,
228 p_crit_col2_datatype in varchar2,
229 p_allow_range_validation_flg in varchar2,
230 p_allow_range_validation_flag2 in varchar2,
231 p_user_defined_flag in varchar2,
232 p_effective_date in date,
233 p_object_version_number in number) is
234 --
235 --
236 --
237
238 l_proc varchar2(72) := g_package||'chk_all_lookups';
239 l_api_updating boolean;
240 l_dummy varchar2(30);
241 --
242 Begin
243 --
244 hr_utility.set_location('Entering:'||l_proc, 5);
245 --
246 l_api_updating := ben_egl_shd.api_updating
247 (p_eligy_criteria_id => p_eligy_criteria_id,
248 p_object_version_number => p_object_version_number);
249 --
250 if (l_api_updating
251 and p_criteria_type
252 <> nvl(ben_egl_shd.g_old_rec.criteria_type,hr_api.g_varchar2)
253 or not l_api_updating)
254 and p_criteria_type is not null then
255 --
256 -- check if value of lookup falls within lookup type.
257 --
258 if hr_api.not_exists_in_hr_lookups
259 (p_lookup_type => 'BEN_CRITERIA_TYPE',
260 p_lookup_code => p_criteria_type,
261 p_effective_date => p_effective_date) then
262 --
263 -- raise error as does not exist as lookup
264 --
265 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
266 fnd_message.set_token('FIELD', 'TYPE');
267 fnd_message.set_token('TYPE','BEN_CRITERIA_TYPE');
268 fnd_message.set_token('VALUE',p_criteria_type);
269 fnd_message.raise_error;
270 --
271 end if;
272 --
273 end if;
274 --
275 if (l_api_updating
276 and p_crit_col1_val_type_cd
277 <> nvl(ben_egl_shd.g_old_rec.crit_col1_val_type_cd,hr_api.g_varchar2)
278 or not l_api_updating)
279 and p_crit_col1_val_type_cd is not null then
280 --
281 -- check if value of lookup falls within lookup type.
282 --
283 if hr_api.not_exists_in_hr_lookups
284 (p_lookup_type => 'CRIT_COL1_VAL_TYPE_CD',
285 p_lookup_code => p_crit_col1_val_type_cd,
286 p_effective_date => p_effective_date) then
287 --
288 -- raise error as does not exist as lookup
289 --
293 fnd_message.set_token('VALUE',p_crit_col1_val_type_cd);
290 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
291 fnd_message.set_token('FIELD', 'Crit Col1 Val Type Cd');
292 fnd_message.set_token('TYPE', 'CRIT_COL1_VAL_TYPE_CD');
294 fnd_message.raise_error;
295 --
296 end if;
297 --
298 end if;
299 --
300 if (l_api_updating
301 and p_crit_col1_datatype
302 <> nvl(ben_egl_shd.g_old_rec.crit_col1_datatype,hr_api.g_varchar2)
303 or not l_api_updating)
304 and p_crit_col1_datatype 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 => 'CRIT_COL1_DATATYPE',
310 p_lookup_code => p_crit_col1_datatype,
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_91628_LOOKUP_TYPE_GENERIC');
316 fnd_message.set_token('FIELD', 'Datatype');
317 fnd_message.set_token('TYPE', 'CRIT_COL1_DATATYPE');
318 fnd_message.set_token('VALUE', p_crit_col1_datatype);
319 fnd_message.raise_error;
320 --
321 end if;
322 --
323 end if;
324 --
325 if (l_api_updating
326 and p_crit_col2_val_type_cd
327 <> nvl(ben_egl_shd.g_old_rec.crit_col2_val_type_cd,hr_api.g_varchar2)
328 or not l_api_updating)
329 and p_crit_col2_val_type_cd is not null then
330 --
331 -- check if value of lookup falls within lookup type.
332 --
333 if hr_api.not_exists_in_hr_lookups
334 (p_lookup_type => 'CRIT_COL1_VAL_TYPE_CD',
335 p_lookup_code => p_crit_col2_val_type_cd,
336 p_effective_date => p_effective_date) then
337 --
338 -- raise error as does not exist as lookup
339 --
340 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
341 fnd_message.set_token('FIELD', p_crit_col2_val_type_cd);
342 fnd_message.set_token('TYPE', 'CRIT_COL1_VAL_TYPE_CD');
343 fnd_message.set_token('VALUE', p_crit_col2_val_type_cd);
344 fnd_message.raise_error;
345 --
346 end if;
347 --
348 end if;
349 --
350 if (l_api_updating
351 and p_allow_range_validation_flg
352 <> nvl(ben_egl_shd.g_old_rec.allow_range_validation_flg,hr_api.g_varchar2)
353 or not l_api_updating)
354 and p_allow_range_validation_flg is not null then
355 --
356 -- check if value of lookup falls within lookup type.
357 --
358 if hr_api.not_exists_in_hr_lookups
359 (p_lookup_type => 'YES_NO',
360 p_lookup_code => p_allow_range_validation_flg,
361 p_effective_date => p_effective_date) then
362 --
363 -- raise error as does not exist as lookup
364 --
368 fnd_message.set_token('VALUE', p_allow_range_validation_flg);
365 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
366 fnd_message.set_token('FIELD', 'Allow Range Validation');
367 fnd_message.set_token('TYPE', 'YES_NO');
369 fnd_message.raise_error;
370 --
371 end if;
372 --
373 end if;
374 --
375 -- Added by rbingi for allow_range_validation_flag2 exixts check in Lookup
376 if (l_api_updating
377 and p_allow_range_validation_flag2
378 <> nvl(ben_egl_shd.g_old_rec.allow_range_validation_flag2,hr_api.g_varchar2)
379 or not l_api_updating)
380 and p_allow_range_validation_flag2 is not null then
381 --
382 -- check if value of lookup falls within lookup type.
383 --
384 if hr_api.not_exists_in_hr_lookups
385 (p_lookup_type => 'YES_NO',
386 p_lookup_code => p_allow_range_validation_flag2,
387 p_effective_date => p_effective_date) then
388 --
389 -- raise error as does not exist as lookup
390 --
391 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
392 fnd_message.set_token('FIELD', 'Allow Range Validation');
393 fnd_message.set_token('TYPE', 'YES_NO');
394 fnd_message.set_token('VALUE', p_allow_range_validation_flag2);
395 fnd_message.raise_error;
396 --
397 end if;
398 --
399 end if;
400 --
401
402 if (l_api_updating
403 and p_user_defined_flag
404 <> nvl(ben_egl_shd.g_old_rec.user_defined_flag,hr_api.g_varchar2)
405 or not l_api_updating)
406 and p_user_defined_flag is not null then
407 --
408 -- check if value of lookup falls within lookup type.
409 --
410 if hr_api.not_exists_in_hr_lookups
411 (p_lookup_type => 'YES_NO',
412 p_lookup_code => p_user_defined_flag,
413 p_effective_date => p_effective_date) then
414 --
415 -- raise error as does not exist as lookup
416 --
417 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
418 fnd_message.set_token('FIELD', 'User Defined Flag');
419 fnd_message.set_token('TYPE', 'YES_NO');
420 fnd_message.set_token('VALUE', p_user_defined_flag);
421 fnd_message.raise_error;
422 --
423 end if;
424 --
425 end if;
426 --
427 hr_utility.set_location('Leaving:'||l_proc,10);
428 --
429 end chk_all_lookups;
430
431 --
432 -- ----------------------------------------------------------------------------
433 -- |------------------------< chk_col_lookup_type >-------------------------------|
434 -- ----------------------------------------------------------------------------
435 --
436 -- Description
437 -- To check if look-up type is valid one
438 --
439 -- Pre Conditions
440 -- None.
441 --
442 -- In Parameters
443 -- p_eligy_criteria_id in number,
444 -- p_col1_lookup_type in varchar2,
445 -- p_effective_date in date,
446 -- p_object_version_number in number
447 --
448 -- Post Success
449 -- Processing continues
450 --
451 -- Post Failure
452 -- Errors handled by the procedure
453 --
454 -- Access Status
455 -- Internal table handler use only.
456
457 Procedure chk_col_lookup_type( p_eligy_criteria_id in number,
458 p_col1_lookup_type in varchar2,
459 p_col2_lookup_type in varchar2,
460 p_effective_date in date,
461 p_object_version_number in number,
462 p_business_group_id in number) is
463
464 --
465 l_proc varchar2(72) := g_package||'chk_col_lookup_type';
466 l_api_updating boolean;
467 l_dummy varchar2(1);
468 --
469 --
470 /*
471 cursor c1(p_col_lookup_type varchar2) is
472 select null
473 from hr_lookups
474 where lookup_type = p_col_lookup_type
478 */
475 and p_effective_date between
476 nvl(start_date_active, p_effective_date)
477 and nvl(end_date_active, p_effective_date);
479 CURSOR c1 (
480 cv_lookup_type varchar2,
481 cv_business_group_id number
482 )
483 IS
484 SELECT NULL
485 FROM fnd_lookup_types_vl flv
486 WHERE lookup_type = cv_lookup_type
487 AND ( customization_level IN ('E', 'S')
488 OR ( customization_level = 'U'
489 AND ( security_group_id = 0
490 OR security_group_id IN (
491 SELECT security_group_id
492 FROM fnd_security_groups
493 WHERE security_group_key =
494 TO_CHAR (cv_business_group_id))
495 )
496 )
497 );
498 --
499 --
500 begin
501 hr_utility.set_location('Entering:'||l_proc, 5);
502 --
503 --
504 l_api_updating := ben_egl_shd.api_updating
505 (p_eligy_criteria_id => p_eligy_criteria_id,
506 p_object_version_number => p_object_version_number);
507 --
508 if (l_api_updating
509 and p_col1_lookup_type
510 <> nvl(ben_egl_shd.g_old_rec.col1_lookup_type,hr_api.g_varchar2)
511 or not l_api_updating)
512 and p_col1_lookup_type is not null then
513 --
514 open c1(p_col1_lookup_type, p_business_group_id);
515 fetch c1 into l_dummy;
516 if c1%notfound then
517 close c1;
518 fnd_message.set_name('PER','HR_6091_DEF_MISSING_LOOKUPS');
519 fnd_message.set_token('LOOKUP_TYPE', p_col1_lookup_type);
520 fnd_message.raise_error;
521 end if;
522 close c1;
523 --
524 end if;
525 --
526
527 if (l_api_updating
528 and p_col2_lookup_type
529 <> nvl(ben_egl_shd.g_old_rec.col2_lookup_type,hr_api.g_varchar2)
530 or not l_api_updating)
531 and p_col2_lookup_type is not null then
532 --
533 open c1(p_col2_lookup_type, p_business_group_id);
534 fetch c1 into l_dummy;
535 if c1%notfound then
536 close c1;
537 fnd_message.set_name('PER','HR_6091_DEF_MISSING_LOOKUPS');
538 fnd_message.set_token('LOOKUP_TYPE', p_col2_lookup_type);
539 fnd_message.raise_error;
540 end if;
541 close c1;
542 --
543 end if;
544 --
545 hr_utility.set_location('Leaving:'||l_proc, 15);
546
547 end chk_col_lookup_type;
548 --
549 -- ----------------------------------------------------------------------------
550 -- |------------------------< chk_access_calc_rule >-------------------------------|
551 -- ----------------------------------------------------------------------------
552 --
553 -- Description
554 -- ensure that Access calc rule is valid
555 -- within business_group
556 --
557 -- Pre Conditions
558 -- None.
559 --
560 -- In Parameters
561 -- p_eligy_criteria_id in number,
562 -- p_access_calc_rule in varchar2,
563 -- p_business_group_id in number,
564 -- p_effective_date in date,
565 -- p_object_version_number in number
566 --
567 -- Post Success
568 -- Processing continues
569 --
570 -- Post Failure
571 -- Errors handled by the procedure
572 --
573 -- Access Status
574 -- Internal table handler use only.
575
576 Procedure chk_access_calc_rule (p_eligy_criteria_id in number,
577 p_access_calc_rule in number,
578 p_business_group_id in number,
579 p_effective_date in date,
580 p_object_version_number in number) is
581
582 --
583 l_proc varchar2(72) := g_package||'chk_access_calc_rule';
584 l_api_updating boolean;
585 l_dummy varchar2(1);
586 --
587 CURSOR c_formula
588 IS
589 SELECT NULL
590 FROM ff_formulas_f
591 WHERE formula_id = p_access_calc_rule
592 AND formula_type_id = -552
593 AND nvl(business_group_id, p_business_group_id) = p_business_group_id;
594 --
595 Begin
596 hr_utility.set_location('Entering:'||l_proc, 5);
597 --
598 --
599 l_api_updating := ben_egl_shd.api_updating
600 (p_eligy_criteria_id => p_eligy_criteria_id,
601 p_object_version_number => p_object_version_number);
602 --
603 if (l_api_updating
604 and p_access_calc_rule
605 <> nvl(ben_egl_shd.g_old_rec.access_calc_rule,hr_api.g_number)
606 or not l_api_updating)
607 and p_access_calc_rule is not null
608 then
609 --
610 -- Bug 4303085 : Do not validate the formula id against effective date since
611 -- BEN_ELIGY_CRITERIA is not a datetracked table
612 --
613 open c_formula;
614 --
615 fetch c_formula into l_dummy;
616 --
617 if c_formula%notfound
618 then
619 --
620 close c_formula;
621 --
622 /*
623 if not benutils.formula_exists
624 (p_formula_id => p_access_calc_rule,
625 p_formula_type_id => -552,
629 --
626 p_business_group_id => p_business_group_id,
627 p_effective_date => p_effective_date) then
628 */
630 -- raise error
631 --
632 fnd_message.set_name('BEN','BEN_91741_FORMULA_NOT_FOUND');
633 fnd_message.set_token('ID',p_access_calc_rule);
634 fnd_message.set_token('TYPE_ID',-552);
635 fnd_message.raise_error;
636 --
637 end if;
638 --
639 close c_formula;
640 --
641 end if;
642 --
643 hr_utility.set_location('Leaving:'||l_proc, 15);
644
645 end chk_access_calc_rule;
646
647 --
648 -- ----------------------------------------------------------------------------
649 -- |------------------------< chk_col_value_set_id >-------------------------------|
650 -- ----------------------------------------------------------------------------
651 --
652 -- Description
653
654 -- Pre Conditions
655 -- None.
656 --
657 -- In Parameters
658 -- p_eligy_criteria_id in number,
659 -- p_col1_value_set_id in varchar2,
660 -- p_object_version_number in number
661 --
662 -- Post Success
663 -- Processing continues
664 --
665 -- Post Failure
666 -- Errors handled by the procedure
667 --
668 -- Access Status
669 -- Internal table handler use only.
670
671 Procedure chk_col_value_set_id (p_eligy_criteria_id in number,
672 p_col1_value_set_id in number,
673 p_col2_value_set_id in number,
674 p_object_version_number in number) is
675
676 --
677 l_proc varchar2(72) := g_package||'chk_col_value_set_id';
678 l_api_updating boolean;
679 l_dummy varchar2(1);
680 --
681 --
682 cursor c1(p_col_value_set_id number) is
683 select null
684 from fnd_flex_value_sets
685 where flex_value_set_id = p_col_value_set_id;
686
687 --
688 --
689 Begin
690 hr_utility.set_location('Entering:'||l_proc, 5);
691 --
692 --
693 l_api_updating := ben_egl_shd.api_updating
694 (p_eligy_criteria_id => p_eligy_criteria_id,
695 p_object_version_number => p_object_version_number);
696 --
697 if (l_api_updating
698 and p_col1_value_set_id
699 <> nvl(ben_egl_shd.g_old_rec.col1_value_set_id,hr_api.g_number)
700 or not l_api_updating)
701 and p_col1_value_set_id is not null then
702 --
703 open c1(p_col1_value_set_id);
704 fetch c1 into l_dummy;
705 if c1%notfound then
706 close c1;
707 fnd_message.set_name('BEN','BEN_94132_INVALID_VALUE_SET_ID');
708 fnd_message.set_token('VALUESET',p_col1_value_set_id);
709 fnd_message.raise_error;
710 end if;
711 close c1;
712 --
713 end if;
714
715
716 if (l_api_updating
717 and p_col2_value_set_id
718 <> nvl(ben_egl_shd.g_old_rec.col2_value_set_id,hr_api.g_number)
719 or not l_api_updating)
720 and p_col2_value_set_id is not null then
721 --
722 open c1(p_col2_value_set_id);
723 fetch c1 into l_dummy;
724 if c1%notfound then
725 close c1;
726 fnd_message.set_name('BEN','BEN_94132_INVALID_VALUE_SET_ID');
727 fnd_message.set_token('VALUESET',p_col2_value_set_id);
728 fnd_message.raise_error;
729 end if;
730 close c1;
731 --
732 end if;
733 --
734 hr_utility.set_location('Leaving:'||l_proc, 15);
735
736 end chk_col_value_set_id;
737
738 --
739 -- ----------------------------------------------------------------------- --
740 -- -----------------------< chk_child_records >-----------------------------|
741 -- -------------------------------------------------------------------------
742 --
743 -- Description
744 -- This procedure is used to check that eligy criteria do not exist in the
745 -- ben_eligy_crit_values_f table when the user deletes the record in the ben_
746 -- eligy_criteria table.
747 --
748 -- Pre Conditions
749 -- None.
750 --
751 -- In Parameters
752 -- eligy_criteria_id PK of record being inserted or updated.
753 --
754 -- Post Success
755 -- Processing continues
756 --
757 -- Post Failure
758 -- Error handled by procedure
759 --
760 -- Access Status
761 -- Internal table handler use only.
762 --
763 procedure chk_child_records(p_eligy_criteria_id in number) is
764 --
765 l_proc varchar2(72):= g_package||'chk_child_records';
766 v_dummy varchar2(1);
767 --
768 cursor chk_eligy_criteria is
769 select null
770 from ben_eligy_crit_values_f ecv
771 where ecv.eligy_criteria_id = p_eligy_criteria_id;
772 begin
773 --
774 hr_utility.set_location('Entering:'||l_proc, 5);
775 --
776 -- check if eligy criteria exists in the eligy_crit_values_f table
777 --
778 open chk_eligy_criteria;
779 --
780 -- fetch value from cursor if it returns a record then the
781 -- the user cannot delete the eligy criteria
782 --
783 fetch chk_eligy_criteria into v_dummy;
784 if chk_eligy_criteria%found then
785 close chk_eligy_criteria;
786 --
787 -- raise error
788 --
792 --
789 fnd_message.set_name('BEN','BEN_94133_EGL_CHLD_RCD_EXISTS');
790 fnd_message.raise_error;
791 end if;
793 close chk_eligy_criteria;
794 --
795 hr_utility.set_location('Leaving:'||l_proc,10);
796 --
797 end chk_child_records;
798 -- ----------------------------------------------------------------------------
799 -- |---------------------< chk_access_exclusive >---------------------------|
800 -- -- ----------------------------------------------------------------------------
801 --
802 -- Description
803 -- This procedure is used to check that
804 -- 1. p_acess_calc_rule is mutually exclusive to .p_access_table_name1 AND p_access_column_name1
805 --
806
807 -- Pre Conditions
808 -- None.
809 --
810 -- In Parameters
811 -- p_eligy_criteria_id PK of record being inserted or updated.
812 -- p_acess_calc_rule
813 -- p_access_table_name1
814 -- p_access_column_name1
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_access_exclusive(p_access_calc_rule in number,
825 p_access_table_name1 in varchar2,
826 p_access_column_name1 in varchar2) is
827 --
828 l_proc varchar2(72) := g_package||'chk_access_exclusive';
829 --
830 Begin
831 --
832 hr_utility.set_location('Entering:'||l_proc, 5);
833 --
834 if (p_access_calc_rule is not null and
835 (p_access_table_name1 is not null
836 or p_access_column_name1 is not null ) )
837
838 then
839 --
840 fnd_message.set_name('BEN','BEN_94134_ACCESS_RL_TAB_COL');
841 fnd_message.raise_error;
842 --
843 end if;
844
845 hr_utility.set_location('Leaving:'||l_proc, 10);
846
847 end chk_access_exclusive;
848 --
849 -- ----------------------------------------------------------------------------
850 -- |------------------------< chk_table_column >-------------------------------|
851 -- ----------------------------------------------------------------------------
852 --
853 -- Description
854 -- Table and column names to be valid .
855 -- Pre Conditions
856 -- None.
857 --
858 -- In Parameters
859 -- p_eligy_criteria_id in number,
860 -- p_access_table_name1 in varchar2,
861 -- p_access_column_name1 in varchar2,
862 -- p_access_table_name2 in varchar2,
863 -- p_access_column_name2 in varchar2,
864 -- p_object_version_number in number
865 --
866 -- Post Success
867 -- Processing continues
868 --
869 -- Post Failure
870 -- Errors handled by the procedure
871 --
872 -- Access Status
873 -- Internal table handler use only.
874
875 Procedure chk_table_column (p_eligy_criteria_id in number,
876 p_access_table_name1 in varchar2,
877 p_access_column_name1 in varchar2,
878 p_access_table_name2 in varchar2,
879 p_access_column_name2 in varchar2,
880 p_object_version_number in number) is
881
882 --
883 l_proc varchar2(72) := g_package||'chk_table_column';
884 l_table_id number(15);
885 l_dummy varchar2(1);
886 --
887 --
888 cursor c1(p_access_table_name varchar2) is
889 select table_id
890 from fnd_tables
891 where table_name = p_access_table_name;
892 --
893 cursor c2(p_access_column_name varchar2) is
894 select null
895 from fnd_columns
896 where table_id = l_table_id
897 and column_name = p_access_column_name;
898 --
899 --
900 Begin
901 hr_utility.set_location('Entering:'||l_proc, 5);
902 --
903
904 if p_access_table_name1 is not null then
905 --
906 open c1(p_access_table_name1);
907 fetch c1 into l_table_id;
908 --
909 if c1%notfound then
910 close c1;
911 fnd_message.set_name('BEN','BEN_91039_INVALID_TABLE');
912 fnd_message.set_token('PROCNAME',l_proc);
913 fnd_message.raise_error;
914 --
915 else
916 --
917 if p_access_table_name1 not in ('PER_ALL_ASSIGNMENTS_F','PER_ALL_PEOPLE_F')then
918 fnd_message.set_name ('BEN','BEN_91039_INVALID_TABLE');
919 fnd_message.set_token('TABLENAME','p_access_table_name1');
920 fnd_message.set_token('PROCNAME',l_proc);
921 fnd_message.raise_error;
922 end if;
923 --
924 end if;
925 --
926 close c1;
927 --
928 end if;
929 --
930 --
931 if (p_access_table_name1 is not null and p_access_column_name1 is null) then
932 fnd_message.set_name('BEN','BEN_94433_COLUMN_REQUIRED');
933 fnd_message.raise_error;
934 end if;
935 --
936
937 if p_access_column_name1 is not null then
938 --
939 if p_access_table_name1 is null then
940 fnd_message.set_name('BEN','BEN_94432_TABLE_REQUIRED');
941 fnd_message.raise_error;
942 --
943 else
944 open c2(p_access_column_name1);
945 fetch c2 into l_dummy;
946 if c2%notfound then
947 close c2;
951 fnd_message.raise_error;
948 fnd_message.set_name('BEN','BEN_91039_INVALID_COLUMN');
949 fnd_message.set_token('COLUMNNAME',p_access_column_name1);
950 fnd_message.set_token('PROCNAME',l_proc);
952 end if;
953 --
954 close c2;
955 --
956 end if;
957
958 end if;
959 --
960
961 --
962 if p_access_table_name2 is not null then
963 --
964 open c1(p_access_table_name2);
965 fetch c1 into l_table_id;
966 --
967 if c1%notfound then
968 close c1;
969 fnd_message.set_name('BEN','BEN_91039_INVALID_TABLE');
970 fnd_message.set_token('TABLENAME',p_access_table_name2);
971 fnd_message.set_token('PROCNAME',l_proc);
972 end if;
973 --
974 close c1;
975 --
976 end if;
977 --
978 --
979 if (p_access_table_name2 is not null and p_access_column_name2 is null) then
980 fnd_message.set_name('BEN','BEN_94484_COLUMN_REQUIRED');
981 fnd_message.raise_error;
982 end if;
983 --
984
985 if p_access_column_name2 is not null then
986 --
987 if p_access_table_name2 is null then
988 fnd_message.set_name('BEN','BEN_94485_TABLE_REQUIRED');
989 fnd_message.raise_error;
990 --
991 else
992 open c2(p_access_column_name2);
993 fetch c2 into l_dummy;
994 --
995 if c2%notfound then
996 close c2;
997 fnd_message.set_name('BEN','BEN_91039_INVALID_COLUMN');
998 fnd_message.set_token('COLUMNNAME',p_access_column_name1);
999 fnd_message.set_token('PROCNAME',l_proc);
1000 fnd_message.raise_error;
1001 end if;
1002 --
1003 close c2;
1004 --
1005 end if;
1006 --
1007 end if;
1008 --
1009 hr_utility.set_location('Leaving:'||l_proc, 15);
1010
1011 end chk_table_column;
1012 --
1013 -- ----------------------------------------------------------------------------
1014 -- |------------------------< chk_col_val_type_cd >-------------------------------|
1015 -- ----------------------------------------------------------------------------
1016 --
1017 -- Description
1018 -- validating crit_col_val_type_cd value for both set1 and set2
1019 -- Pre Conditions
1020 -- None.
1021 --
1022 -- In Parameters
1023 -- p_eligy_criteria_id in number,
1024 -- p_col1_value_set_id in varchar2,
1025 -- p_object_version_number in number
1026 --
1027 -- Post Success
1028 -- Processing continues
1029 --
1030 -- Post Failure
1031 -- Errors handled by the procedure
1032 --
1033 -- Access Status
1034 -- Internal table handler use only.
1035
1036 Procedure chk_col_val_type_cd (p_crit_col_val_type_cd in varchar2,
1037 p_col_lookup_type in varchar2,
1038 p_access_calc_rule in number,
1039 p_col_value_set_id in number,
1040 p_access_table_name in varchar2,
1041 p_access_column_name in varchar2,
1042 p_allow_range_validation_flg in varchar2,
1043 p_set in number
1044 ) is
1045 --Bug 4729818 Added new parameter p_set
1046 --
1047 l_proc varchar2(72) := g_package||'chk_col_val_type_cd';
1048 l_api_updating boolean;
1049 l_dummy varchar2(1);
1050 --
1051
1052 --
1053 Begin
1054 hr_utility.set_location('Entering:'||l_proc, 5);
1055 --
1056 if p_crit_col_val_type_cd is not null
1057 then
1058 --
1059 if p_crit_col_val_type_cd = 'LOOKUP' --lookup
1060 then
1061 --
1062 if (p_col_lookup_type is null or p_col_value_set_id is not null)
1063 then
1064 --
1065 fnd_message.set_name('BEN','BEN_94137_VALID_CRIT_BASIS');
1066 fnd_message.raise_error;
1067 --
1068 end if;
1069 --
1070 else
1071 --
1072 if p_crit_col_val_type_cd = 'VAL_SET'
1073 then
1074 --
1075 if (p_col_lookup_type is not null or p_col_value_set_id is null)
1076 then
1077 --
1078 fnd_message.set_name('BEN','BEN_94137_VALID_CRIT_BASIS');
1079 fnd_message.raise_error;
1080 --
1081 end if;
1082 --
1083 else
1084 --
1085 if p_crit_col_val_type_cd in ('ORG_HIER','POS_HIER')
1086 then
1087 --
1088 if (p_col_lookup_type is not null or p_col_value_set_id is not null or p_access_calc_rule is not null)
1089 then
1090 --
1091 fnd_message.set_name('BEN','BEN_94137_VALID_CRIT_BASIS');
1092 fnd_message.raise_error;
1093 --
1094 else
1095 --
1096 if(p_allow_range_validation_flg = 'Y')
1097 then
1098 --
1099 fnd_message.set_name('BEN','BEN_94137_VALID_CRIT_BASIS');
1100 fnd_message.raise_error;
1101 --
1102 else
1103 --
1104 if p_crit_col_val_type_cd ='ORG_HIER'
1105 then
1106 --
1107 if (p_access_table_name <> 'PER_ALL_ASSIGNMENTS_F' or p_access_column_name <> 'ORGANIZATION_ID')
1108 then
1109 --
1110 fnd_message.set_name('BEN','BEN_94137_VALID_CRIT_BASIS');
1111 fnd_message.raise_error;
1112 --
1113 end if;
1114 --
1115 else
1119 --
1116 --
1117 if p_crit_col_val_type_cd ='POS_HIER'
1118 then
1120 if (p_access_table_name <> 'PER_ALL_ASSIGNMENTS_F' or p_access_column_name <> 'POSITION_ID')
1121 then
1122 --
1123 fnd_message.set_name('BEN','BEN_94137_VALID_CRIT_BASIS');
1124 fnd_message.raise_error;
1125 --
1126 end if;
1127 --
1128 end if;
1129 --
1130 end if;
1131 --
1132 end if;
1133 --
1134 end if;
1135 --
1136 end if;--hier
1137 --
1138 end if;--val
1139 --
1140 end if;--look
1141 --
1142 else
1143 --
1144 if p_crit_col_val_type_cd is null
1145 then
1146 --
1147 -- Bug 4729818 Display Appropriate error messages for two Sets
1148 if (p_set = 1) then --signifies set 1 is being checked
1149 fnd_message.set_name('BEN','BEN_94152_VAL_TYP_CD_NULL');
1150 fnd_message.raise_error;
1151 end if;
1152
1153 if (p_set = 2) then -- signifies set 2 is being checked
1154 --RKG Set 2 Error Message for Bug 4729818
1155 fnd_message.set_name('BEN','BEN_94483_VAL_TYP_CD_NULL');
1156 fnd_message.raise_error;
1157 end if;
1158 --
1159 end if;
1160 --
1161 end if;-- upd
1162 --
1163 hr_utility.set_location('Leaving:'||l_proc, 15);
1164 --
1165 end chk_col_val_type_cd;
1166 --
1167 -- ----------------------------------------------------------------------------
1168 -- |------------------------< chk_col_val_type_cd_upd >-------------------------------|
1169 -- ----------------------------------------------------------------------------
1170 --
1171 -- Description
1172 -- validating crit_col_val_type_cd value for both set1 and set2
1173 -- Pre Conditions
1174 -- None.
1175 --
1176 -- In Parameters
1177 -- p_eligy_criteria_id in number,
1178 -- p_col1_value_set_id in varchar2,
1179 -- p_object_version_number in number
1180 --
1181 -- Post Success
1182 -- Processing continues
1183 --
1184 -- Post Failure
1185 -- Errors handled by the procedure
1186 --
1187 -- Access Status
1188 -- Internal table handler use only.
1189
1190 Procedure chk_col_val_type_cd_upd(p_eligy_criteria_id in number,
1191 p_crit_col_val_type_cd in varchar2,
1192 p_col_lookup_type in varchar2,
1193 p_access_calc_rule in number,
1194 p_col_value_set_id in number,
1195 p_access_table_name in varchar2,
1196 p_access_column_name in varchar2,
1197 p_object_version_number in number,
1198 p_allow_range_validation_flg in varchar2,
1199 p_set in number) is
1200
1201 --Bug 4729818 Added additional parameter p_set
1202 --
1203 l_proc varchar2(72) := g_package||'chk_col_val_type_cd_upd';
1204 l_dummy varchar2(1);
1205 l_api_updating boolean;
1206 --
1207 cursor chk_eligy_criteria is
1208 select null
1209 from ben_eligy_crit_values_f ecv
1210 where ecv.eligy_criteria_id = p_eligy_criteria_id;
1211 --
1212 Begin
1213 hr_utility.set_location('Entering:'||l_proc, 5);
1214 --
1215 --
1216 l_api_updating := ben_egl_shd.api_updating
1217 (p_eligy_criteria_id => p_eligy_criteria_id,
1218 p_object_version_number => p_object_version_number);
1219 --
1220 --
1221 if p_crit_col_val_type_cd is null
1222 then
1223 --
1224 --Bug 4729818 Display appropriate error messages
1225 if (p_set = 1) then --signifies set 1 is being checked
1226 fnd_message.set_name('BEN','BEN_94152_VAL_TYP_CD_NULL');
1227 fnd_message.raise_error;
1228 end if;
1229
1230 if (p_set = 2) then -- signifies set 2 is being checked
1231 --RKG Set 2 Error Message for Bug 4729818
1232 fnd_message.set_name('BEN','BEN_94483_VAL_TYP_CD_NULL');
1233 fnd_message.raise_error;
1234 end if;
1235 --
1236
1237 else
1238 if (
1239 (l_api_updating
1240 and p_crit_col_val_type_cd
1241 <> nvl(ben_egl_shd.g_old_rec.crit_col1_val_type_cd,hr_api.g_number)
1242 )
1243 or not l_api_updating
1244 )
1245 and p_crit_col_val_type_cd is not null then
1246 --
1247 open chk_eligy_criteria;
1248
1249 fetch chk_eligy_criteria into l_dummy;
1250 --
1251 if chk_eligy_criteria%found then
1252 close chk_eligy_criteria;
1253 --
1254 fnd_message.set_name('BEN','BEN_94133_EGL_CHLD_RCD_EXISTS');
1255 fnd_message.raise_error;
1256 --
1257 --
1258 else
1259 chk_col_val_type_cd(p_crit_col_val_type_cd => p_crit_col_val_type_cd,
1260 p_col_lookup_type => p_col_lookup_type,
1261 p_access_calc_rule => p_access_calc_rule,
1262 p_col_value_set_id => p_col_value_set_id,
1263 p_access_table_name => p_access_table_name,
1264 p_access_column_name => p_access_column_name,
1265 p_allow_range_validation_flg => p_allow_range_validation_flg,
1266 p_set => p_set);
1267 -- Bug 4729818 Changed the above call according to the signature of chk_col_val_type_cd
1268 end if;
1269 --
1273 --
1270 close chk_eligy_criteria;
1271 --
1272 end if;-- upd
1274 end if;--if null
1275 hr_utility.set_location('Leaving:'||l_proc, 15);
1276
1277 end chk_col_val_type_cd_upd;
1278
1279 -- ----------------------------------------------------------------------------
1280 -- |---------------------< chk_tab_col_rl_null >---------------------------|
1281 -- -- ----------------------------------------------------------------------------
1282 --
1283 -- Description
1284 -- This procedure is used to check that
1285 -- 1. p_acess_calc_rule is mutually exclusive to .p_access_table_name1 AND p_access_column_name1
1286 --
1287
1288 -- Pre Conditions
1289 -- None.
1290 --
1291 -- In Parameters
1292 -- p_eligy_criteria_id PK of record being inserted or updated.
1293 -- p_acess_calc_rule
1294 -- p_access_table_name1
1295 -- p_access_column_name1
1296 -- Post Success
1297 -- Processing continues
1298 --
1299 -- Post Failure
1300 -- Error handled by procedure
1301 --
1302 -- Access Status
1303 -- Internal table handler use only.
1304 --
1305 Procedure chk_tab_col_rl_null(p_access_calc_rule in number,
1306 p_access_table_name1 in varchar2,
1307 p_access_column_name1 in varchar2) is
1308 --
1309 l_proc varchar2(72) := g_package||'chk_tab_col_rl_null';
1310 --
1311 Begin
1312 --
1313 hr_utility.set_location('Entering:'||l_proc, 5);
1314 --
1315 if (p_access_calc_rule is null and
1316 (p_access_table_name1 is null
1317 and p_access_column_name1 is null ) )
1318
1319 then
1320 --
1321 fnd_message.set_name('BEN','BEN_94149_TAB_COL_RL_NULL');
1322 fnd_message.raise_error;
1323 --
1324 end if;
1325 --
1326 hr_utility.set_location('Leaving:'||l_proc, 10);
1327
1328 end chk_tab_col_rl_null;
1329 --
1330 -- ----------------------------------------------------------------------------
1331 -- |------------------------< chk_tab_col_rl_upd >-------------------------------|
1332 -- ----------------------------------------------------------------------------
1333 --
1334 -- Description
1335 -- Check for child records while updating table,column or access rule
1336 -- Pre Conditions
1337 -- None.
1338 --
1339 -- In Parameters
1340 -- p_eligy_criteria_id in number,
1341 -- p_access_calc_rule in number
1342 -- p_access_table_name1 in varchar2,
1343 -- p_access_column_name1 in varchar2
1344 -- p_object_version_number in number
1345 --
1346 -- Post Success
1347 -- Processing continues
1348 --
1349 -- Post Failure
1350 -- Errors handled by the procedure
1351 --
1352 -- Access Status
1353 -- Internal table handler use only.
1354
1355 Procedure chk_tab_col_rl_upd( p_eligy_criteria_id in number,
1356 p_access_calc_rule in number,
1357 p_access_table_name1 in varchar2,
1358 p_access_column_name1 in varchar2,
1359 p_object_version_number in number) is
1360
1361 --
1362 l_proc varchar2(72) := g_package||'chk_tab_col_rl_upd';
1363 l_api_updating boolean;
1364 --
1365
1366 Begin
1367 hr_utility.set_location('Entering:'||l_proc, 5);
1368 --
1369 --
1370 l_api_updating := ben_egl_shd.api_updating
1371 (p_eligy_criteria_id => p_eligy_criteria_id,
1372 p_object_version_number => p_object_version_number);
1373 --
1374
1375 if (l_api_updating
1376 and p_access_calc_rule
1377 <> nvl(ben_egl_shd.g_old_rec.access_calc_rule,hr_api.g_number)
1378 or not l_api_updating)
1379 and p_access_calc_rule is not null then
1380 --
1381 chk_child_records(p_eligy_criteria_id => p_eligy_criteria_id);
1382 --
1383 end if;
1384 --
1385 if (l_api_updating
1386 and p_access_table_name1
1387 <> nvl(ben_egl_shd.g_old_rec.access_table_name1,hr_api.g_number)
1388 or not l_api_updating)
1389 and p_access_table_name1 is not null then
1390 --
1391 chk_child_records(p_eligy_criteria_id => p_eligy_criteria_id);
1392 --
1393 end if;
1394 --
1395 if (l_api_updating
1396 and p_access_column_name1
1397 <> nvl(ben_egl_shd.g_old_rec.access_column_name1,hr_api.g_number)
1398 or not l_api_updating)
1399 and p_access_column_name1 is not null then
1400 --
1401 chk_child_records(p_eligy_criteria_id => p_eligy_criteria_id);
1402 --
1403 end if;
1404 --
1405
1406 hr_utility.set_location('Leaving:'||l_proc, 15);
1407
1408 end chk_tab_col_rl_upd;
1409 --
1410 --
1411 -- ----------------------------------------------------------------------------
1412 -- |--------------------< chk_allw_range_vld_flag_upd >-----------------------|
1413 -- ----------------------------------------------------------------------------
1414 -- Created in Bug 4584283 fix. To error when allow_range_validation_flag is
1415 -- updated when child records exists.
1416 procedure chk_allw_range_vld_flag_upd(
1417 p_eligy_criteria_id in number,
1418 p_allow_range_validation_flg in varchar2,
1419 p_allow_range_validation_flg2 in varchar2,
1420 p_object_version_number in number) is
1421 l_api_updating boolean;
1422 --
1423 Begin
1424 --
1425 --
1429 --
1426 l_api_updating := ben_egl_shd.api_updating
1427 (p_eligy_criteria_id => p_eligy_criteria_id,
1428 p_object_version_number => p_object_version_number);
1430 if -- Flag 1 is changed
1431 (((l_api_updating
1432 and p_allow_range_validation_flg
1433 <> nvl(ben_egl_shd.g_old_rec.allow_range_validation_flg,hr_api.g_varchar2)
1434 or not l_api_updating) and p_allow_range_validation_flg is not null)
1435 or -- Flag 2 is changed
1436 ((l_api_updating
1437 and p_allow_range_validation_flg2
1438 <> nvl(ben_egl_shd.g_old_rec.allow_range_validation_flag2,hr_api.g_varchar2)
1439 or not l_api_updating) and p_allow_range_validation_flg2 is not null)
1440 ) then
1441 --
1442 chk_child_records(p_eligy_criteria_id => p_eligy_criteria_id);
1443 --
1444 end if;
1445 --
1446 --
1447 End chk_allw_range_vld_flag_upd;
1448 --
1449 --
1450 -- ----------------------------------------------------------------------------
1451 -- |---------------------------< insert_validate >----------------------------|
1452 -- ----------------------------------------------------------------------------
1453 Procedure insert_validate(p_rec in ben_egl_shd.g_rec_type
1454 ,p_effective_date in date) is
1455 --
1456 l_proc varchar2(72) := g_package||'insert_validate';
1457 l_set2_empty varchar2(1) := 'Y';
1458 --
1459 Begin
1460 hr_utility.set_location('Entering:'||l_proc, 5);
1461 --
1462 -- Call all supporting business operations
1463 --
1464 --
1465 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1466 --
1467 chk_eligy_criteria_id(p_eligy_criteria_id => p_rec.eligy_criteria_id,
1468 p_object_version_number => p_rec.object_version_number);
1469 --
1470 chk_name_unique( p_eligy_criteria_id => p_rec.eligy_criteria_id
1471 ,p_name => p_rec.name
1472 ,p_business_group_id => p_rec.business_group_id);
1473 --
1474 /* Bug 5338058 - Commenting this check as short_code need not to be unique
1475 chk_short_code_unique( p_eligy_criteria_id => p_rec.eligy_criteria_id
1476 ,p_short_code => p_rec.short_code
1477 ,p_business_group_id => p_rec.business_group_id); */
1478 --
1479 chk_all_lookups ( p_eligy_criteria_id => p_rec.eligy_criteria_id ,
1480 p_criteria_type => p_rec.criteria_type,
1481 p_crit_col1_val_type_cd => p_rec.crit_col1_val_type_cd,
1482 p_crit_col1_datatype => p_rec.crit_col1_datatype,
1483 p_crit_col2_val_type_cd => p_rec.crit_col2_val_type_cd,
1484 p_crit_col2_datatype => p_rec.crit_col2_datatype,
1485 p_allow_range_validation_flg => p_rec.allow_range_validation_flg,
1486 p_allow_range_validation_flag2 => p_rec.allow_range_validation_flag2, -- added paramter by rbingi
1487 p_user_defined_flag => p_rec.user_defined_flag,
1488 p_effective_date => p_effective_date,
1489 p_object_version_number => p_rec.object_version_number) ;
1490 --
1491 chk_col_lookup_type (p_eligy_criteria_id => p_rec.eligy_criteria_id,
1492 p_col1_lookup_type => p_rec.col1_lookup_type,
1493 p_col2_lookup_type => p_rec.col2_lookup_type,
1494 p_effective_date => p_effective_date,
1495 p_object_version_number => p_rec.object_version_number,
1496 p_business_group_id => p_rec.business_group_id);
1497 --
1498 chk_col_value_set_id(p_eligy_criteria_id => p_rec.eligy_criteria_id,
1499 p_col1_value_set_id => p_rec.col1_value_set_id,
1500 p_col2_value_set_id => p_rec.col2_value_set_id,
1501 p_object_version_number => p_rec.object_version_number);
1502 --
1503 chk_access_calc_rule(p_eligy_criteria_id => p_rec.eligy_criteria_id,
1504 p_access_calc_rule => p_rec.access_calc_rule,
1505 p_business_group_id => p_rec.business_group_id,
1506 p_effective_date => p_effective_date,
1507 p_object_version_number => p_rec.object_version_number);
1508 chk_access_exclusive(p_access_calc_rule => p_rec.access_calc_rule,
1509 p_access_table_name1 => p_rec.access_table_name1,
1510 p_access_column_name1 => p_rec.access_column_name1);
1511 chk_table_column ( p_eligy_criteria_id => p_rec.eligy_criteria_id,
1512 p_access_table_name1 => p_rec.access_table_name1,
1513 p_access_column_name1 => p_rec.access_column_name1,
1514 p_access_table_name2 => p_rec.access_table_name2,
1515 p_access_column_name2 => p_rec.access_column_name2,
1516 p_object_version_number => p_rec.object_version_number);
1517 --
1518 chk_col_val_type_cd(p_crit_col_val_type_cd => p_rec.crit_col1_val_type_cd,
1519 p_col_lookup_type => p_rec.col1_lookup_type,
1520 p_access_calc_rule => p_rec.access_calc_rule,
1521 p_col_value_set_id => p_rec.col1_value_set_id,
1522 p_access_table_name => p_rec.access_table_name1,
1523 p_access_column_name => p_rec.access_column_name1,
1524 p_allow_range_validation_flg =>p_rec.allow_range_validation_flg,
1525 p_set => 1);
1529 then
1526 --Bug 4729818 Make the call according to the signature
1527 --
1528 if p_rec.criteria_type = 'USER'
1530 chk_tab_col_rl_null(p_access_calc_rule => p_rec.access_calc_rule,
1531 p_access_table_name1 => p_rec.access_table_name1,
1532 p_access_column_name1 => p_rec.access_column_name1);
1533 end if;
1534
1535 --
1536 if(p_rec.crit_col2_val_type_cd is not null) then
1537 l_set2_empty:='N';
1538 end if;
1539 --
1540 if(p_rec.col2_lookup_type is not null) then
1541 l_set2_empty:='N';
1542 end if;
1543 --
1544 if(p_rec.col2_value_set_id is not null) then
1545 l_set2_empty:='N';
1546 end if;
1547 --
1548 if(p_rec.access_table_name2 is not null) then
1549 l_set2_empty:='N';
1550 end if;
1551 --
1552 if(p_rec.access_column_name2 is not null) then
1553 l_set2_empty:='N';
1554 end if;
1555 --
1556 if(p_rec.access_calc_rule2 is not null) then
1557 l_set2_empty:='N';
1558 end if;
1559 --
1560 if(p_rec.time_access_calc_rule2 is not null) then
1561 l_set2_empty:='N';
1562 end if;
1563 -- Check for Hierarchy Criteria type, There shd not be Sub-Criteria(Set2) defined
1564 if p_rec.crit_col1_val_type_cd like '%HIER' and
1565 l_set2_empty = 'N' then
1566 --
1567 fnd_message.set_name('BEN','BEN_94271_EGL_HIER_NO_SET2');
1568 fnd_message.raise_error;
1569 --
1570 end if;
1571 --
1572 if(l_set2_empty = 'N') then
1573 --
1574 chk_access_calc_rule(p_eligy_criteria_id => p_rec.eligy_criteria_id,
1575 p_access_calc_rule => p_rec.access_calc_rule2,
1576 p_business_group_id => p_rec.business_group_id,
1577 p_effective_date => p_effective_date,
1578 p_object_version_number => p_rec.object_version_number);
1579 --
1580 --
1581 chk_access_exclusive(p_access_calc_rule => p_rec.access_calc_rule2,
1582 p_access_table_name1 => p_rec.access_table_name2,
1583 p_access_column_name1 => p_rec.access_column_name2);
1584 --
1585 --
1586 chk_col_val_type_cd(p_crit_col_val_type_cd => p_rec.crit_col2_val_type_cd,
1587 p_col_lookup_type => p_rec.col2_lookup_type,
1588 p_access_calc_rule => p_rec.access_calc_rule2,
1589 p_col_value_set_id => p_rec.col2_value_set_id,
1590 p_access_table_name => p_rec.access_table_name2,
1591 p_access_column_name => p_rec.access_column_name2,
1592 p_allow_range_validation_flg =>p_rec.allow_range_validation_flag2,
1593 p_set => 2);
1594 -- Bug 4729818 Make the call according to the signature
1595
1596
1597 --
1598 if p_rec.criteria_type = 'USER'
1599 then
1600 chk_tab_col_rl_null(p_access_calc_rule => p_rec.access_calc_rule2,
1601 p_access_table_name1 => p_rec.access_table_name2,
1602 p_access_column_name1 => p_rec.access_column_name2);
1603 end if;
1604 end if;
1605
1606 hr_utility.set_location(' Leaving:'||l_proc, 10);
1607
1608 End insert_validate;
1609 --
1610 -- ----------------------------------------------------------------------------
1611 -- |---------------------------< update_validate >----------------------------|
1612 -- ----------------------------------------------------------------------------
1613 Procedure update_validate(p_rec in ben_egl_shd.g_rec_type
1614 ,p_effective_date in date) is
1615 --
1616 l_proc varchar2(72) := g_package||'update_validate';
1617 l_set2_empty varchar2(1) := 'Y';
1618 --
1619 Begin
1620 hr_utility.set_location('Entering:'||l_proc, 5);
1621 --
1622 -- Call all supporting business operations
1623 --
1624 --
1625 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1626 --
1627 chk_eligy_criteria_id( p_eligy_criteria_id => p_rec.eligy_criteria_id,
1628 p_object_version_number => p_rec.object_version_number);
1629 --
1630 chk_name_unique( p_eligy_criteria_id => p_rec.eligy_criteria_id
1631 ,p_name => p_rec.name
1632 ,p_business_group_id => p_rec.business_group_id);
1633 --
1634 /* Bug 5338058 - Commenting this check as short_code need not to be unique
1635 chk_short_code_unique( p_eligy_criteria_id => p_rec.eligy_criteria_id
1636 ,p_short_code => p_rec.short_code
1637 ,p_business_group_id => p_rec.business_group_id); */
1638 --
1639 chk_all_lookups ( p_eligy_criteria_id => p_rec.eligy_criteria_id ,
1640 p_criteria_type => p_rec.criteria_type,
1641 p_crit_col1_val_type_cd => p_rec.crit_col1_val_type_cd,
1642 p_crit_col1_datatype => p_rec.crit_col1_datatype,
1643 p_crit_col2_val_type_cd => p_rec.crit_col2_val_type_cd,
1644 p_crit_col2_datatype => p_rec.crit_col2_datatype,
1645 p_allow_range_validation_flg => p_rec.allow_range_validation_flg,
1646 p_allow_range_validation_flag2 => p_rec.allow_range_validation_flag2, -- added paramter by rbingi
1647 p_user_defined_flag => p_rec.user_defined_flag,
1648 p_effective_date => p_effective_date,
1649 p_object_version_number => p_rec.object_version_number) ;
1650 --
1651 chk_col_lookup_type ( p_eligy_criteria_id => p_rec.eligy_criteria_id,
1655 p_object_version_number => p_rec.object_version_number,
1652 p_col1_lookup_type => p_rec.col1_lookup_type,
1653 p_col2_lookup_type => p_rec.col2_lookup_type,
1654 p_effective_date => p_effective_date,
1656 p_business_group_id => p_rec.business_group_id);
1657 --
1658 chk_col_value_set_id( p_eligy_criteria_id => p_rec.eligy_criteria_id,
1659 p_col1_value_set_id => p_rec.col1_value_set_id,
1660 p_col2_value_set_id => p_rec.col2_value_set_id,
1661 p_object_version_number => p_rec.object_version_number);
1662 --
1663 chk_tab_col_rl_upd( p_eligy_criteria_id => p_rec.eligy_criteria_id,
1664 p_access_calc_rule => p_rec.access_calc_rule,
1665 p_access_table_name1 => p_rec.access_table_name1,
1666 p_access_column_name1 => p_rec.access_column_name1,
1667 p_object_version_number => p_rec.object_version_number);
1668 --
1669 chk_access_calc_rule(p_eligy_criteria_id => p_rec.eligy_criteria_id,
1670 p_access_calc_rule => p_rec.access_calc_rule,
1671 p_business_group_id => p_rec.business_group_id,
1672 p_effective_date => p_effective_date,
1673 p_object_version_number => p_rec.object_version_number);
1674 chk_access_exclusive(p_access_calc_rule => p_rec.access_calc_rule,
1675 p_access_table_name1 => p_rec.access_table_name1,
1676 p_access_column_name1 => p_rec.access_column_name1);
1677 chk_table_column ( p_eligy_criteria_id => p_rec.eligy_criteria_id,
1678 p_access_table_name1 => p_rec.access_table_name1,
1679 p_access_column_name1 => p_rec.access_column_name1,
1680 p_access_table_name2 => p_rec.access_table_name2,
1681 p_access_column_name2 => p_rec.access_column_name2,
1682 p_object_version_number => p_rec.object_version_number);
1683 --
1684 chk_col_val_type_cd_upd (p_eligy_criteria_id => p_rec.eligy_criteria_id,
1685 p_crit_col_val_type_cd => p_rec.crit_col1_val_type_cd,
1686 p_col_lookup_type => p_rec.col1_lookup_type,
1687 p_access_calc_rule => p_rec.access_calc_rule,
1688 p_col_value_set_id => p_rec.col1_value_set_id,
1689 p_access_table_name => p_rec.access_table_name1,
1690 p_access_column_name => p_rec.access_column_name1,
1691 p_object_version_number => p_rec.object_version_number,
1692 p_allow_range_validation_flg =>p_rec.allow_range_validation_flg,
1693 p_set => 1);
1694 -- Bug 4729818 Make the call according to the signature
1695 -- Bug 4584283, Added call to chk_allw_range_vld_flag_upd.
1696 chk_allw_range_vld_flag_upd( p_eligy_criteria_id => p_rec.eligy_criteria_id,
1697 p_allow_range_validation_flg => p_rec.allow_range_validation_flg,
1698 p_allow_range_validation_flg2 => p_rec.allow_range_validation_flag2,
1699 p_object_version_number => p_rec.object_version_number);
1700 --
1701 if p_rec.criteria_type = 'USER'
1702 then
1703 chk_tab_col_rl_null( p_access_calc_rule => p_rec.access_calc_rule,
1704 p_access_table_name1 => p_rec.access_table_name1,
1705 p_access_column_name1 => p_rec.access_column_name1);
1706 end if;
1707 --
1708 if(p_rec.crit_col2_val_type_cd is not null) then
1709 l_set2_empty:='N';
1710 end if;
1711 --
1712 if(p_rec.col2_lookup_type is not null) then
1713 l_set2_empty:='N';
1714 end if;
1715 --
1716 if(p_rec.col2_value_set_id is not null) then
1717 l_set2_empty:='N';
1718 end if;
1719 --
1720 if(p_rec.access_table_name2 is not null) then
1721 l_set2_empty:='N';
1722 end if;
1723 --
1724 if(p_rec.access_column_name2 is not null) then
1725 l_set2_empty:='N';
1726 end if;
1727 --
1728 if(p_rec.access_calc_rule2 is not null) then
1729 l_set2_empty:='N';
1730 end if;
1731 --
1732 if(p_rec.time_access_calc_rule2 is not null) then
1733 l_set2_empty:='N';
1734 end if;
1735 --
1736 -- Check for Hierarchy Criteria type, There shd not be any Sub-Criteria defined
1737 if p_rec.crit_col1_val_type_cd like '%HIER' and
1738 l_set2_empty = 'N' then
1739 --
1740 fnd_message.set_name('BEN','BEN_94271_EGL_HIER_NO_SET2');
1741 fnd_message.raise_error;
1742 --
1743 end if;
1744 --
1745 if(l_set2_empty = 'N') then
1746
1747 -- Following call added by rbingi
1748
1749 --Bug 4726244 added checks chk_tab_col_rl_upd , chk_col_val_type_cd_upd
1750
1751 chk_tab_col_rl_upd( p_eligy_criteria_id => p_rec.eligy_criteria_id,
1752 p_access_calc_rule => p_rec.access_calc_rule2,
1753 p_access_table_name1 => p_rec.access_table_name2,
1754 p_access_column_name1 => p_rec.access_column_name2,
1755 p_object_version_number => p_rec.object_version_number);
1756
1757 chk_access_calc_rule(p_eligy_criteria_id => p_rec.eligy_criteria_id,
1758 p_access_calc_rule => p_rec.access_calc_rule2,
1759 p_business_group_id => p_rec.business_group_id,
1760 p_effective_date => p_effective_date,
1761 p_object_version_number => p_rec.object_version_number);
1762 --
1766 --
1763 chk_access_exclusive(p_access_calc_rule => p_rec.access_calc_rule2,
1764 p_access_table_name1 => p_rec.access_table_name2,
1765 p_access_column_name1 => p_rec.access_column_name2);
1767 chk_col_val_type_cd(p_crit_col_val_type_cd => p_rec.crit_col2_val_type_cd,
1768 p_col_lookup_type => p_rec.col2_lookup_type,
1769 p_access_calc_rule => p_rec.access_calc_rule2,
1770 p_col_value_set_id => p_rec.col2_value_set_id,
1771 p_access_table_name => p_rec.access_table_name2,
1772 p_access_column_name => p_rec.access_column_name2,
1773 p_allow_range_validation_flg =>p_rec.allow_range_validation_flag2,
1774 p_set => 2);
1775 -- Bug 4729818 Make the call according to the signature
1776
1777 chk_col_val_type_cd_upd (p_eligy_criteria_id => p_rec.eligy_criteria_id,
1778 p_crit_col_val_type_cd => p_rec.crit_col2_val_type_cd,
1779 p_col_lookup_type => p_rec.col2_lookup_type,
1780 p_access_calc_rule => p_rec.access_calc_rule2,
1781 p_col_value_set_id => p_rec.col2_value_set_id,
1782 p_access_table_name => p_rec.access_table_name2,
1783 p_access_column_name => p_rec.access_column_name2,
1784 p_object_version_number => p_rec.object_version_number,
1785 p_allow_range_validation_flg =>p_rec.allow_range_validation_flag2,
1786 p_set => 2);
1787 -- Bug 4729818 Make the call according to the signature
1788
1789 --
1790 if p_rec.criteria_type = 'USER'
1791 then
1792 chk_tab_col_rl_null(p_access_calc_rule => p_rec.access_calc_rule2,
1793 p_access_table_name1 => p_rec.access_table_name2,
1794 p_access_column_name1 => p_rec.access_column_name2);
1795 end if;
1796 end if;
1797
1798 hr_utility.set_location(' Leaving:'||l_proc, 10);
1799 End update_validate;
1800 --
1801 -- ----------------------------------------------------------------------------
1802 -- |---------------------------< delete_validate >----------------------------|
1803 -- ----------------------------------------------------------------------------
1804 Procedure delete_validate(p_rec in ben_egl_shd.g_rec_type
1805 ,p_effective_date in date) is
1806 --
1807 l_proc varchar2(72) := g_package||'delete_validate';
1808 --
1809 Begin
1810 hr_utility.set_location('Entering:'||l_proc, 5);
1811 --
1812 -- Call all supporting business operations
1813 --
1814 chk_child_records
1815 (p_eligy_criteria_id => p_rec.eligy_criteria_id);
1816 --
1817 hr_utility.set_location(' Leaving:'||l_proc, 10);
1818 End delete_validate;
1819 --
1820 --
1821 -- ---------------------------------------------------------------------------
1822 -- |---------------------< return_legislation_code >-------------------------|
1823 -- ---------------------------------------------------------------------------
1824 --
1825 function return_legislation_code
1826 (p_eligy_criteria_id in number) return varchar2 is
1827 --
1828 -- Declare cursor
1829 --
1830 cursor csr_leg_code is
1831 select a.legislation_code
1832 from per_business_groups a,
1833 ben_eligy_criteria b
1834 where b.eligy_criteria_id = p_eligy_criteria_id
1835 and a.business_group_id = b.business_group_id;
1836 --
1837 -- Declare local variables
1838 --
1839 l_legislation_code varchar2(150);
1840 l_proc varchar2(72) := g_package||'return_legislation_code';
1841 --
1842 begin
1843 --
1844 hr_utility.set_location('Entering:'|| l_proc, 10);
1845 --
1846 -- Ensure that all the mandatory parameter are not null
1847 --
1848 hr_api.mandatory_arg_error(p_api_name => l_proc,
1849 p_argument => 'eligy_criteria_id',
1850 p_argument_value => p_eligy_criteria_id);
1851 --
1852 open csr_leg_code;
1853 --
1854 fetch csr_leg_code into l_legislation_code;
1855 --
1856 if csr_leg_code%notfound then
1857 --
1858 close csr_leg_code;
1859 --
1860 -- The primary key is invalid therefore we must error
1861 --
1862 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1863 hr_utility.raise_error;
1864 --
1865
1866 end if;
1867 --
1868 close csr_leg_code;
1869 --
1870 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1871 --
1872 return l_legislation_code;
1873 --
1874 end return_legislation_code;
1875 --
1876 end ben_egl_bus;