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