[Home] [Help]
PACKAGE BODY: APPS.BEN_XCV_BUS
Source
1 Package Body ben_xcv_bus as
2 /* $Header: bexcvrhi.pkb 120.3 2006/04/11 11:18:40 tjesumic noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xcv_bus.'; -- Global package name
9
10
11 -- ----------------------------------------------------------------------------
12 -- |----------------------< not_exists_in_hr_lookups >------------------------|
13 -- ----------------------------------------------------------------------------
14 --
15 function not_exists_in_hr_lookups
16 (p_effective_date in date
17 ,p_lookup_type in varchar2
18 ,p_lookup_code in varchar2
19 ) return boolean is
20 --
21 -- Declare Local Variables
22 --
23 l_exists varchar2(1);
24 --
25 -- Declare Local cursors
26 --
27 cursor csr_hr_look is
28 select null
29 from hr_lookups
30 where lookup_code = p_lookup_code
31 and lookup_type = p_lookup_type
32 and p_effective_date between
33 nvl(start_date_active, p_effective_date)
34 and nvl(end_date_active, p_effective_date);
35 --
36 begin
37 --
38 -- When the lookup_type is YES_NO attempt to validate without
39 -- executing the cursor. This is to reduce checking time for
40 -- valid values in row handlers which have a lot of Yes No flags.
41 --
42 if p_lookup_type = 'YES_NO' then
43 if p_lookup_code = 'Y' or p_lookup_code = 'N' then
44 return false;
45 end if;
46 -- If the value is not known then go onto check against the
47 -- hr_lookups view. Just in case there has been a change to
48 -- the system defined lookup.
49 end if;
50 hr_utility.set_location(hr_api.g_package||'not_exists_in_hr_lookups', 10);
51 --
52 open csr_hr_look;
53 fetch csr_hr_look into l_exists;
54 if csr_hr_look%notfound then
55 close csr_hr_look;
56 return true;
57 else
58 close csr_hr_look;
59 return false;
60 end if;
61 end not_exists_in_hr_lookups;
62 --
63 --
64 -- ----------------------------------------------------------------------------
65 -- |---------------------< not_exists_in_hrstanlookups >----------------------|
66 -- ----------------------------------------------------------------------------
67 --
68 function not_exists_in_hrstanlookups
69 (p_effective_date in date
70 ,p_lookup_type in varchar2
71 ,p_lookup_code in varchar2
72 ) return boolean is
73 --
74 -- Declare Local Variables
75 --
76 l_exists varchar2(1);
77 --
78 -- Declare Local cursors
79 --
80 cursor csr_hr_look is
81 select null
82 from hr_standard_lookups
83 where lookup_code = p_lookup_code
84 and lookup_type = p_lookup_type
85 and p_effective_date between
86 nvl(start_date_active, p_effective_date)
87 and nvl(end_date_active, p_effective_date);
88 --
89 begin
90 --
91 -- When the lookup_type is YES_NO attempt to validate without
92 -- executing the cursor. This is to reduce checking time for
93 -- valid values in row handlers which have a lot of Yes No flags.
94 --
95 if p_lookup_type = 'YES_NO' then
96 if p_lookup_code = 'Y' or p_lookup_code = 'N' then
97 return false;
98 end if;
99 -- If the value is not known then go onto check against the
100 -- hr_lookups view. Just in case there has been a change to
101 -- the system defined lookup.
102 end if;
103 hr_utility.set_location(hr_api.g_package||'not_exists_in_hrstanlookups', 10);
104 --
105 open csr_hr_look;
106 fetch csr_hr_look into l_exists;
107 if csr_hr_look%notfound then
108 close csr_hr_look;
109 return true;
110 else
111 close csr_hr_look;
112 return false;
113 end if;
114 end not_exists_in_hrstanlookups;
115
116 --
117 -- ----------------------------------------------------------------------------
118 -- |----------------------< chk_startup_action >------------------------------|
119 -- ----------------------------------------------------------------------------
120 --
121 -- Description:
122 -- This procedure will check that the current action is allowed according
123 -- to the current startup mode.
124 --
125 -- ----------------------------------------------------------------------------
126 PROCEDURE chk_startup_action
127 (p_insert IN boolean
128 ,p_business_group_id IN number
129 ,p_legislation_code IN varchar2
130 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
131 --
132 BEGIN
133 --
134 -- Call the supporting procedure to check startup mode
135 --
136 IF (p_insert) THEN
137 --
138 -- Call procedure to check startup_action for inserts.
139 --
140 hr_startup_data_api_support.chk_startup_action
141 (p_generic_allowed => TRUE
142 ,p_startup_allowed => TRUE
143 ,p_user_allowed => TRUE
144 ,p_business_group_id => p_business_group_id
145 ,p_legislation_code => p_legislation_code
146 ,p_legislation_subgroup => p_legislation_subgroup
147 );
148 ELSE
149 --
150 -- Call procedure to check startup_action for updates and deletes.
151 --
152 hr_startup_data_api_support.chk_upd_del_startup_action
153 (p_generic_allowed => TRUE
154 ,p_startup_allowed => TRUE
155 ,p_user_allowed => TRUE
156 ,p_business_group_id => p_business_group_id
157 ,p_legislation_code => p_legislation_code
158 ,p_legislation_subgroup => p_legislation_subgroup
159 );
160 END IF;
161 --
162 END chk_startup_action;
163
164 --
165 -- ---------------------------------------------------------------------------
166 -- |----------------------< set_security_group_id >--------------------------|
167 -- ---------------------------------------------------------------------------
168 --
169 Procedure set_security_group_id
170 (p_ext_crit_val_id in number
171 ) is
172 --
173 -- Declare cursor
174 --
175 cursor csr_sec_grp is
176 select pbg.security_group_id
177 from per_business_groups_perf pbg
178 , ben_ext_crit_val xcv
179 where xcv.ext_crit_val_id = p_ext_crit_val_id
180 and pbg.business_group_id = xcv.business_group_id;
181 --
182 -- Declare local variables
183 --
184 l_security_group_id number;
185 l_proc varchar2(72) := g_package||'set_security_group_id';
186 --
187 begin
188 --
189 hr_utility.set_location('Entering:'|| l_proc, 10);
190 --
191 -- Ensure that all the mandatory parameter are not null
192 --
193 hr_api.mandatory_arg_error
194 (p_api_name => l_proc
195 ,p_argument => 'ext_crit_val_id'
196 ,p_argument_value => p_ext_crit_val_id
197 );
198 --
199 open csr_sec_grp;
200 fetch csr_sec_grp into l_security_group_id;
201 --
202 if csr_sec_grp%notfound then
203 --
204 close csr_sec_grp;
205 --
206 -- The primary key is invalid therefore we must error
207 --
208 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
209 fnd_message.raise_error;
210 --
211 end if;
212 close csr_sec_grp;
213 --
214 -- Set the security_group_id in CLIENT_INFO
215 --
216 hr_api.set_security_group_id
217 (p_security_group_id => l_security_group_id
218 );
219 --
220 hr_utility.set_location(' Leaving:'|| l_proc, 20);
221 --
222 end set_security_group_id;
223
224 --
225 -- ----------------------------------------------------------------------------
226 -- |------< chk_ext_crit_val_id >------|
227 -- ----------------------------------------------------------------------------
228 --
229 -- Description
230 -- This procedure is used to check that the primary key for the table
231 -- is created properly. It should be null on insert and
232 -- should not be able to be updated.
233 --
234 -- Pre Conditions
235 -- None.
236 --
237 -- In Parameters
238 -- ext_crit_val_id PK of record being inserted or updated.
239 -- object_version_number Object version number of record being
240 -- inserted or updated.
241 --
242 -- Post Success
243 -- Processing continues
244 --
245 -- Post Failure
246 -- Errors handled by the procedure
247 --
248 -- Access Status
249 -- Internal table handler use only.
250 --
251 Procedure chk_ext_crit_val_id(p_ext_crit_val_id in number,
252 p_object_version_number in number) is
253 --
254 l_proc varchar2(72) := g_package||'chk_ext_crit_val_id';
255 l_api_updating boolean;
256 --
257 Begin
258 --
259 hr_utility.set_location('Entering:'||l_proc, 5);
260 --
261 l_api_updating := ben_xcv_shd.api_updating
262 (p_ext_crit_val_id => p_ext_crit_val_id,
263 p_object_version_number => p_object_version_number);
264 --
265 if (l_api_updating
266 and nvl(p_ext_crit_val_id,hr_api.g_number)
267 <> ben_xcv_shd.g_old_rec.ext_crit_val_id) then
268 --
269 -- raise error as PK has changed
270 --
271 ben_xcv_shd.constraint_error('BEN_EXT_CRIT_VAL_PK');
272 --
273 elsif not l_api_updating then
274 --
275 -- check if PK is null
276 --
277 if p_ext_crit_val_id is not null then
278 --
279 -- raise error as PK is not null
280 --
281 ben_xcv_shd.constraint_error('BEN_EXT_CRIT_VAL_PK');
282 --
283 end if;
284 --
285 end if;
286 --
287 hr_utility.set_location('Leaving:'||l_proc, 10);
288 --
289 End chk_ext_crit_val_id;
290 --
291 -- ----------------------------------------------------------------------------
292 -- |------< chk_ext_crit_typ_id >------|
293 -- ----------------------------------------------------------------------------
294 --
295 -- Description
296 -- This procedure checks that a referenced foreign key actually exists
297 -- in the referenced table.
298 --
299 -- Pre-Conditions
300 -- None.
301 --
302 -- In Parameters
303 -- p_ext_crit_val_id PK
304 -- p_ext_crit_typ_id ID of FK column
305 -- p_object_version_number object version number
306 --
307 -- Post Success
308 -- Processing continues
309 --
310 -- Post Failure
311 -- Error raised.
312 --
313 -- Access Status
314 -- Internal table handler use only.
315 --
316 Procedure chk_ext_crit_typ_id (p_ext_crit_val_id in number,
317 p_ext_crit_typ_id in number,
318 p_object_version_number in number) is
319 --
320 l_proc varchar2(72) := g_package||'chk_ext_crit_typ_id';
321 l_api_updating boolean;
322 l_dummy varchar2(1);
323 --
324 cursor c1 is
325 select null
326 from ben_ext_crit_typ a
327 where a.ext_crit_typ_id = p_ext_crit_typ_id;
328 --
329 Begin
330 --
331 hr_utility.set_location('Entering:'||l_proc,5);
332 --
333 l_api_updating := ben_xcv_shd.api_updating
334 (p_ext_crit_val_id => p_ext_crit_val_id,
335 p_object_version_number => p_object_version_number);
336 --
337 if (l_api_updating
338 and nvl(p_ext_crit_typ_id,hr_api.g_number)
339 <> nvl(ben_xcv_shd.g_old_rec.ext_crit_typ_id,hr_api.g_number)
340 or not l_api_updating) then
341 --
342 -- check if ext_crit_typ_id value exists in ben_ext_crit_typ table
343 --
344 open c1;
345 --
346 fetch c1 into l_dummy;
347 if c1%notfound then
348 --
349 close c1;
350 --
351 -- raise error as FK does not relate to PK in ben_ext_crit_typ
352 -- table.
353 --
354 ben_xcv_shd.constraint_error('BEN_EXT_CRIT_VAL_FK1');
355 --
356 end if;
357 --
358 close c1;
359 --
360 end if;
361 --
362 hr_utility.set_location('Leaving:'||l_proc,10);
363 --
364 End chk_ext_crit_typ_id;
365
366
367
368 Procedure chk_ext_crit_bg_id(p_ext_crit_val_id in number,
369 p_ext_crit_bg_id in number,
370 p_ext_crit_typ_id in number,
371 p_business_group_id in number,
372 p_object_version_number in number) is
373 --
374 l_proc varchar2(72) := g_package||'chk_ext_crit_bg_id';
375 l_api_updating boolean;
376 l_dummy varchar2(1);
377 --
378 cursor c1 is
379 select null
380 from per_business_groups_perf a
381 where a.business_group_id = p_ext_crit_bg_id ;
382
383 cursor c2 is
384 select ecp.ext_global_flag
385 from ben_ext_crit_prfl ecp ,
386 ben_ext_crit_typ ect
387 where ect.ext_crit_typ_id = p_ext_crit_typ_id
388 and ect.ext_crit_prfl_id = ecp.ext_crit_prfl_id
389 ;
390 --
391 Begin
392 --
393 hr_utility.set_location('Entering:'||l_proc,5);
394 --
395 l_api_updating := ben_xcv_shd.api_updating
396 (p_ext_crit_val_id => p_ext_crit_val_id,
397 p_object_version_number => p_object_version_number);
398 --
399 if (l_api_updating
400 and nvl(p_ext_crit_bg_id,hr_api.g_number)
401 <> nvl(ben_xcv_shd.g_old_rec.ext_crit_bg_id,hr_api.g_number)
402 or not l_api_updating) then
403 --
404 -- check if ext_crit_typ_id value exists in ben_ext_crit_typ table
405 --
406 if p_ext_crit_bg_id is not null and p_ext_crit_bg_id <> p_business_group_id then
407 open c1;
408 --
409 fetch c1 into l_dummy;
410 if c1%notfound then
411 --
412 close c1;
413 --
414 -- raise error as FK does not relate to PK in ben_ext_crit_typ
415 -- table.
416 --
417 ben_xcv_shd.constraint_error('BEN_EXT_CRIT_VAL_FK3');
418 --
419 end if;
420 --
421 close c1;
422 l_dummy := null ;
423
424 open c2 ;
425 fetch c2 into l_dummy;
426 close c2;
427 if l_dummy = 'N' then
428 fnd_message.set_name('BEN','BEN_92776_PARENT_REC_EXISTS');
429 fnd_message.raise_error;
430 end if ;
431 end if;
432 end if ;
433 --
434 hr_utility.set_location('Leaving:'||l_proc,10);
435 --
436 End chk_ext_crit_bg_id;
437
438
439
440
441 --
442 -- ----------------------------------------------------------------------------
443 -- |------< chk_val_1 >------|
444 -- ----------------------------------------------------------------------------
445 --
446 -- Description
447 -- This procedure checks when a Criterion Value is inserted or updated, it is
448 -- valid for it's parent crit_typ_cd.
449 --
450 -- Pre-Conditions
451 -- None.
452 --
453 -- In Parameters
454 -- p_val_1
455 -- p_ext_crit_val_id PK
456 -- p_ext_crit_typ_id ID of FK column
457 -- p_object_version_number object version number
458 --
459 -- Post Success
460 -- Processing continues
461 --
462 -- Post Failure
463 -- Error raised.
464 --
465 -- Access Status
466 -- Internal table handler use only.
467 --
468 Procedure chk_val_1 ( p_val_1 in varchar2,
469 p_ext_crit_val_id in number,
470 p_ext_crit_typ_id in number,
471 p_effective_date in date,
472 p_business_group_id in number,
473 p_ext_crit_bg_id in number,
474 p_legislation_code in varchar2,
475 p_object_version_number in number ,
476 p_val_2 in varchar2
477 ) is
478 --
479 l_proc varchar2(72) := g_package||'chk_val_1';
480 l_api_updating boolean;
481 l_dummy varchar2(1);
482 l_crit_typ_cd varchar2(30);
483 l_number number;
484 --
485 cursor c1 is
486 select a.crit_typ_cd
487 from ben_ext_crit_typ a
488 where a.ext_crit_typ_id = p_ext_crit_typ_id;
489 --
490 cursor c2 (p_business_group_id number) is
491 select null
492 from per_all_people_f per
493 where per.person_id = l_number
494 and p_effective_date between per.effective_start_date
495 and per.effective_end_date
496 and per.business_group_id = p_business_group_id;
497 --
498 cursor c3 (p_business_group_id number) is
499 select null
500 from hr_all_organization_units_vl org
501 where org.organization_id = l_number
502 and org.internal_external_flag = 'INT'
503 and p_effective_date between org.date_from
504 and nvl(org.date_to,p_effective_date)
505 and org.business_group_id = p_business_group_id;
506 --
507 cursor c4 is
508 select null
509 from hr_locations loc
510 where loc.location_id = l_number
511 and p_effective_date <= nvl(loc.inactive_date,p_effective_date);
512 --
513 cursor c5 (p_business_group_id number) is
514 select null
515 from hr_tax_units_v gre
516 where gre.tax_unit_id = l_number
517 and p_effective_date between gre.date_from
518 and nvl(gre.date_to,p_effective_date)
519 and gre.business_group_id = p_business_group_id;
520 --
521 cursor c6 (p_business_group_id number) is
522 select null
523 from ben_pl_f pln
524 where pln.pl_id = l_number
525 and p_effective_date between pln.effective_start_date
526 and pln.effective_end_date
527 and pln.business_group_id = p_business_group_id;
528 --
529 cursor c7 (p_business_group_id number) is
530 select null
531 from ben_benfts_grp bgr
532 where bgr.benfts_grp_id = l_number
533 and bgr.business_group_id = p_business_group_id;
534 --
535 cursor c8 (p_business_group_id number) is
536 select null
537 from per_assignment_status_types ast
538 where ast.assignment_status_type_id = l_number
539 and ast.active_flag = 'Y'
540 ;
541 /*
542 and ((ast.business_group_id is null and ast.legislation_code is null)
543 or (ast.legislation_code is not null
544 and ast.legislation_code = p_legislation_code)
545 or (ast.business_group_id is not null
546 and ast.business_group_id = p_business_group_id)
547 );
548 */
549
550
551 -- and nvl(ast.business_group_id,p_business_group_id) = p_business_group_id;
552 -- need to somehow add legislation code to this cursor.
553 --
554 cursor c9 is
555 select null
556 from ben_ext_crit_typ a,
557 ben_ext_crit_val b
558 where a.ext_crit_typ_id = b.ext_crit_typ_id
559 and a.ext_crit_typ_id = p_ext_crit_typ_id
560 and b.val_1 = p_val_1;
561
562
563 cursor c10 is
564 select null
565 from pay_event_groups
566 where event_group_id = p_val_1
567 ;
568 --
569 Begin
570 --
571 hr_utility.set_location('Entering:'||l_proc,5);
572 --
573 l_api_updating := ben_xcv_shd.api_updating
574 (p_ext_crit_val_id => p_ext_crit_val_id,
575 p_object_version_number => p_object_version_number);
576 --
577 if (l_api_updating
578 and nvl(p_val_1,hr_api.g_varchar2)
579 <> nvl(ben_xcv_shd.g_old_rec.val_1,hr_api.g_varchar2)
580 or not l_api_updating) then
581 --
582 -- val_1 is mandatory
583 --
584 open c1;
585 -- won't fail because already checked in above edit.
586 fetch c1 into l_crit_typ_cd;
587 --
588 close c1;
589 if l_crit_typ_cd = 'PPC' and p_val_1 is null then
590 --
591 fnd_message.set_name('BEN','BEN_91910_EXT_VAL1_RQD');
592 fnd_message.raise_error;
593 --
594 end if;
595 --
596 --
597 -- numeric check for those that store foreign keys
598 --
599 if l_crit_typ_cd in ('PID','POR','PLO','PLE','BPL','PBG','PAS') then
600 begin
601 l_number := to_number(p_val_1);
602 exception
603 when invalid_number then
604 fnd_message.set_name('BEN','BEN_91911_EXT_INVLD_VAL1');
605 fnd_message.raise_error;
606 end;
607 end if;
608 --
609 if l_crit_typ_cd = 'PID' then
610 --
611 open c2 (nvl(p_ext_crit_bg_id,p_business_group_id ) ) ;
612 fetch c2 into l_dummy;
613 if c2%notfound then
614 close c2;
615 fnd_message.set_name('BEN','BEN_91911_EXT_INVLD_VAL1');
616 fnd_message.raise_error;
617 end if;
618 close c2;
619 --
620 elsif l_crit_typ_cd = 'PST' then
621 --
622 -- check if value of lookup falls within lookup type.
623 --
624 if p_business_group_id is not null then
625 /* BG is set, so use the existing call, with no modifications*/
626 if hr_api.not_exists_in_hr_lookups
627 (p_lookup_type => 'US_STATE',
628 p_lookup_code => p_val_1,
629 p_effective_date => p_effective_date) then
630 --
631 -- raise error as does not exist as lookup
632 --
633 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
634 fnd_message.set_token('FIELD','p_val_1');
635 fnd_message.set_name('TYPE','US_STATE');
636 fnd_message.raise_error;
637 --
638 end if;
639 else
640 /* BG is null, so alternative call is required */
641 if not_exists_in_hrstanlookups
642 (p_lookup_type => 'US_STATE',
643 p_lookup_code => p_val_1,
644 p_effective_date => p_effective_date) then
645 --
646 -- raise error as does not exist as lookup
647 --
648 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
649 fnd_message.set_token('FIELD','p_val_1');
650 fnd_message.set_name('TYPE','US_STATE');
651 fnd_message.raise_error;
652 --
653 end if;
654 end if;
655 --
656 elsif l_crit_typ_cd = 'PPC' then
657
658 -- Postal Code is not edited, not even for numeric because
659 -- Canada has letters in it's postal code.
660 null;
661
662 elsif l_crit_typ_cd = 'POR' then
663 --
664 open c3 (nvl(p_ext_crit_bg_id,p_business_group_id ) );
665 fetch c3 into l_dummy;
666 if c3%notfound then
667 close c3;
668 fnd_message.set_name('BEN','BEN_91911_EXT_INVLD_VAL1');
669 fnd_message.raise_error;
670 end if;
671 close c3;
672 --
673 elsif l_crit_typ_cd = 'PLO' then
674 --
675 open c4;
676 fetch c4 into l_dummy;
677 if c4%notfound then
678 close c4;
679 fnd_message.set_name('BEN','BEN_91911_EXT_INVLD_VAL1');
680 fnd_message.raise_error;
681 end if;
682 close c4;
683 --
684 elsif l_crit_typ_cd = 'PLE' then
685 --
686 open c5(nvl(p_ext_crit_bg_id,p_business_group_id ) );
687 fetch c5 into l_dummy;
688 if c5%notfound then
689 close c5;
690 fnd_message.set_name('BEN','BEN_91911_EXT_INVLD_VAL1');
691 fnd_message.raise_error;
692 end if;
693 close c5;
694 --
695 elsif l_crit_typ_cd = 'BPL' then
696 --
697 open c6(nvl(p_ext_crit_bg_id,p_business_group_id ) );
698 fetch c6 into l_dummy;
699 if c6%notfound then
700 close c6;
701 fnd_message.set_name('BEN','BEN_91911_EXT_INVLD_VAL1');
702 fnd_message.raise_error;
703 end if;
704 close c6;
705
706
707 elsif l_crit_typ_cd = 'CELT' then
708 --
709 -- check if value of lookup falls within lookup type.
710 --
711 if p_business_group_id is not null then
712 /* BG is set, so use the existing call, with no modifications*/
713 if hr_api.not_exists_in_hr_lookups
714 (p_lookup_type => 'BEN_EXT_CHG_TYP',
715 p_lookup_code => p_val_1,
716 p_effective_date => p_effective_date) then
717 --
718 -- raise error as does not exist as lookup
719 --
720 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
721 -- Commented for Bug 2493525
722 /* fnd_message.set_token('FIELD','p_val_1');
723 fnd_message.set_name('TYPE','BEN_EXT_CHG_TYP'); */
724
725 fnd_message.set_token('VALUE',p_val_1);
726 fnd_message.set_token('FIELD','Value');
727 fnd_message.set_token('TYPE','BEN_EXT_CHG_TYP');
728 -- End of Bug 2493525
729 fnd_message.raise_error;
730 --
731 end if;
732 else
733 /* BG is null, so alternative call is required */
734 if not_exists_in_hrstanlookups
735 (p_lookup_type => 'BEN_EXT_CHG_TYP',
736 p_lookup_code => p_val_1,
737 p_effective_date => p_effective_date) then
738 --
739 -- raise error as does not exist as lookup
740 --
741 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
742 -- Commented for Bug 2493525
743 /* fnd_message.set_token('FIELD','p_val_1');
744 fnd_message.set_name('TYPE','BEN_EXT_CHG_TYP'); */
745
746 fnd_message.set_token('VALUE',p_val_1);
747 fnd_message.set_token('FIELD','Value');
748 fnd_message.set_token('TYPE','BEN_EXT_CHG_TYP');
749 -- End of Bug 2493525
750 fnd_message.raise_error;
751 --
752 end if;
753 end if;
754
755 --
756 elsif l_crit_typ_cd = 'CCE' then
757 --
758 -- check if value of lookup falls within lookup type.
759 --
760 if p_val_2 is null or p_val_2 = 'BEN' then
761 if p_business_group_id is not null then
762 /* BG is set, so use the existing call, with no modifications*/
763 if hr_api.not_exists_in_hr_lookups
764 (p_lookup_type => 'BEN_EXT_CHG_EVT',
765 p_lookup_code => p_val_1,
766 p_effective_date => p_effective_date) then
767 --
768 -- raise error as does not exist as lookup
769 --
770 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
771 -- Commented for Bug 2493525
772 /* fnd_message.set_token('FIELD','p_val_1');
773 fnd_message.set_name('TYPE','BEN_EXT_CHG_EVT'); */
774
775 fnd_message.set_token('VALUE',p_val_1);
776 fnd_message.set_token('FIELD','Value');
777 fnd_message.set_token('TYPE','BEN_EXT_CHG_EVT');
778 -- End of Bug 2493525
779 fnd_message.raise_error;
780 --
781 end if;
782 else
783 /* BG is null, so alternative call is required */
784 if not_exists_in_hrstanlookups
785 (p_lookup_type => 'BEN_EXT_CHG_EVT',
786 p_lookup_code => p_val_1,
787 p_effective_date => p_effective_date) then
788 --
789 -- raise error as does not exist as lookup
790 --
791 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
792 -- Commented for Bug 2493525
793 /* fnd_message.set_token('FIELD','p_val_1');
794 fnd_message.set_name('TYPE','BEN_EXT_CHG_EVT'); */
795
796 fnd_message.set_token('VALUE',p_val_1);
797 fnd_message.set_token('FIELD','Value');
798 fnd_message.set_token('TYPE','BEN_EXT_CHG_EVT');
799 -- End of Bug 2493525
800 fnd_message.raise_error;
801 --
802 end if;
803 end if;
804 elsif p_val_2 = 'PAY' then
805
806 open c10 ;
807 fetch c10 into l_dummy ;
808 if c10%notfound then
809 close c10 ;
810 fnd_message.set_name('BEN','BEN_91911_EXT_INVLD_VAL1');
811 fnd_message.raise_error;
812 end if ;
813
814 close c10 ;
815
816
817 end if ;
818 --
819 elsif l_crit_typ_cd = 'PBG' then
820 --
821 open c7(nvl(p_ext_crit_bg_id,p_business_group_id ) );
822 fetch c7 into l_dummy;
823 if c7%notfound then
824 close c7;
825 fnd_message.set_name('BEN','BEN_91911_EXT_INVLD_VAL1');
826 fnd_message.raise_error;
827 end if;
828 close c7;
829 --
830 elsif l_crit_typ_cd = 'PAS' then
831 --
832 open c8(nvl(p_ext_crit_bg_id,p_business_group_id ) );
833 fetch c8 into l_dummy;
834 if c8%notfound then
835 close c8;
836 fnd_message.set_name('BEN','BEN_91911_EXT_INVLD_VAL1');
837 fnd_message.raise_error;
838 end if;
839 close c8;
840 --
841 end if;
842 --
843 -- make sure val_1 is unique within parent crit_typ_cd.
844 --
845 if l_crit_typ_cd = 'PPC' then
846 open c9;
847 fetch c9 into l_dummy;
848 if c9%found then
849 close c9;
850 fnd_message.set_name('BEN','BEN_91912_EXT_VAL1_NOT_UNIQUE');
851 fnd_message.raise_error;
852 end if;
853 close c9;
854 --
855 end if;
856
857 --
858 end if;
859 --
860 hr_utility.set_location('Leaving:'||l_proc,10);
861 --
862 End chk_val_1;
863 -- ----------------------------------------------------------------------------
864 -- |------< chk_val_2 >------|
865 -- ----------------------------------------------------------------------------
866 --
867 -- Description
868 -- This procedure checks when a Criterion Value is inserted or updated, it is
869 -- valid for it's parent crit_typ_cd.
870 --
871 -- Pre-Conditions
872 -- None.
873 --
874 -- In Parameters
875 -- p_val_2
876 -- p_ext_crit_val_id PK
877 -- p_ext_crit_typ_id ID of FK column
878 -- p_object_version_number object version number
879 --
880 -- Post Success
881 -- Processing continues
882 --
883 -- Post Failure
884 -- Error raised.
885 --
886 -- Access Status
887 -- Internal table handler use only.
888 --
889 Procedure chk_val_2 ( p_val_2 in varchar2,
890 p_val_1 in varchar2,
891 p_ext_crit_val_id in number,
892 p_ext_crit_typ_id in number,
893 p_effective_date in date,
894 p_business_group_id in number,
895 p_ext_crit_bg_id in number,
896 p_object_version_number in number) is
897 --
898 l_proc varchar2(72) := g_package||'chk_val_2';
899 l_api_updating boolean;
900 l_dummy varchar2(1);
901 l_crit_typ_cd varchar2(30);
902 --
903 cursor c1 is
904 select a.crit_typ_cd
905 from ben_ext_crit_typ a
906 where a.ext_crit_typ_id = p_ext_crit_typ_id;
907 --
908 cursor c2 is
909 select null
910 from ben_ext_crit_typ a,
911 ben_ext_crit_val b
912 where a.ext_crit_typ_id = b.ext_crit_typ_id
913 and a.ext_crit_typ_id = p_ext_crit_typ_id
914 and b.val_2 = p_val_2;
915 --
916 cursor c3 is
917 select null
918 from ben_ext_crit_typ a,
919 ben_ext_crit_val b
920 where a.ext_crit_typ_id = b.ext_crit_typ_id
921 and a.ext_crit_typ_id = p_ext_crit_typ_id
922 and b.val_1 = p_val_1
923 and b.val_2 = p_val_2;
924 Begin
925 --
926 hr_utility.set_location('Entering:'||l_proc,5);
927 --
928 l_api_updating := ben_xcv_shd.api_updating
929 (p_ext_crit_val_id => p_ext_crit_val_id,
930 p_object_version_number => p_object_version_number);
931 --
932 if (l_api_updating
933 and nvl(p_val_2,hr_api.g_varchar2)
934 <> nvl(ben_xcv_shd.g_old_rec.val_2,hr_api.g_varchar2)
935 or not l_api_updating) then
936 --
937 open c1;
938 -- won't fail because already checked in above edit.
939 fetch c1 into l_crit_typ_cd;
940 --
941 close c1;
942 --
943 -- val_2 is currently only valid with parent crit_typ_cd = PPC
944 --
945 /*if p_val_2 is not null and l_crit_typ_cd <> 'PPC' then
946 --
947 fnd_message.set_name('BEN','BEN_91913_EXT_INVLD_VAL2');
948 fnd_message.raise_error;
949 --
950 end if;*/
951 --
952 -- make sure val_2 is unique within parent crit_typ_cd.
953 --
954 if l_crit_typ_cd = 'PPC' then
955 open c2;
956 fetch c2 into l_dummy;
957 if c2%found then
958 close c2;
959 fnd_message.set_name('BEN','BEN_91914_EXT_VAL2_NOT_UNIQUE');
960 fnd_message.raise_error;
961 end if;
962 close c2;
963 elsif l_crit_typ_cd = 'REE' then
964 open c3;
965 fetch c3 into l_dummy;
966 if c3%found then
967 close c3;
968 fnd_message.set_name('BEN','BEN_91982_EXT_VAL_NOT_UNIQUE');
969 fnd_message.raise_error;
970 end if;
971 close c3;
972 --
973 end if;
974 end if;
975 --
976 hr_utility.set_location('Leaving:'||l_proc,10);
977 --
978 End chk_val_2;
979 -- ----------------------------------------------------------------------------
980 -- |------< chk_val_dpndcy >------|
981 -- ----------------------------------------------------------------------------
982 --
983 -- Description
984 -- This procedure checks the dependency between val_1 and val_2, specifically:
985 -- if val_2 exists it must be greater than val_1.
986 --
987 -- Pre-Conditions
988 -- None.
989 --
990 -- In Parameters
991 -- p_val_1
992 -- p_val_2
993 -- p_ext_crit_val_id PK
994 -- p_object_version_number object version number
995 --
996 -- Post Success
997 -- Processing continues
998 --
999 -- Post Failure
1000 -- Error raised.
1001 --
1002 -- Access Status
1003 -- Internal table handler use only.
1004 --
1005 Procedure chk_val_dpndcy (
1006 p_val_1 in varchar2,
1007 p_val_2 in varchar2,
1008 p_ext_crit_val_id in number,
1009 p_ext_crit_typ_id in number,
1010 p_object_version_number in number) is
1011 --
1012 l_proc varchar2(72) := g_package||'chk_val_dpndcy';
1013 l_api_updating boolean;
1014 --
1015 l_crit_typ_cd varchar2(30);
1016 cursor c1 is
1017 select a.crit_typ_cd
1018 from ben_ext_crit_typ a
1019 where a.ext_crit_typ_id = p_ext_crit_typ_id;
1020 --
1021 Begin
1022 --
1023 hr_utility.set_location('Entering:'||l_proc,5);
1024 --
1025 l_api_updating := ben_xcv_shd.api_updating
1026 (p_ext_crit_val_id => p_ext_crit_val_id,
1027 p_object_version_number => p_object_version_number);
1028 --
1029 if (l_api_updating
1030 and
1031 (nvl(p_val_1,hr_api.g_varchar2)
1032 <> nvl(ben_xcv_shd.g_old_rec.val_1,hr_api.g_varchar2) or
1033 nvl(p_val_2,hr_api.g_varchar2)
1034 <> nvl(ben_xcv_shd.g_old_rec.val_2,hr_api.g_varchar2))
1035 or not l_api_updating) then
1036 --
1037 open c1;
1038 fetch c1 into l_crit_typ_cd;
1039 --
1040 close c1;
1041 -- val_2 must be > val_1.
1042 --
1043 if l_crit_typ_cd = 'REE' and p_val_2 is not null and p_val_1 is null then
1044 --
1045 fnd_message.set_name('BEN','BEN_91910_EXT_VAL1_RQD');
1046 fnd_message.raise_error;
1047 --
1048 end if;
1049 if l_crit_typ_cd = 'PPC' and p_val_2 is not null and p_val_1 >= p_val_2 then
1050 --
1051 fnd_message.set_name('BEN','BEN_91915_EXT_VAL1_GT_VAL2');
1052 fnd_message.raise_error;
1053 --
1054 end if;
1055 --
1056 end if;
1057 --
1058 hr_utility.set_location('Leaving:'||l_proc,10);
1059 --
1060 End chk_val_dpndcy;
1061 -- ----------------------------------------------------------------------------
1062 -- |---------------------------< insert_validate >----------------------------|
1063 -- ----------------------------------------------------------------------------
1064 Procedure insert_validate(p_rec in ben_xcv_shd.g_rec_type
1065 ,p_effective_date in date) is
1066 --
1067 l_proc varchar2(72) := g_package||'insert_validate';
1068 --
1069 Begin
1070 hr_utility.set_location('Entering:'||l_proc, 5);
1071 --
1072 -- Call all supporting business operations
1073 --
1074 --
1075 chk_startup_action(True
1076 ,p_rec.business_group_id
1077 ,p_rec.legislation_code);
1078 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1079 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1080 END IF;
1081 --
1082 chk_ext_crit_val_id
1083 (p_ext_crit_val_id => p_rec.ext_crit_val_id,
1084 p_object_version_number => p_rec.object_version_number);
1085 --
1086 chk_ext_crit_typ_id
1087 (p_ext_crit_val_id => p_rec.ext_crit_val_id,
1088 p_ext_crit_typ_id => p_rec.ext_crit_typ_id,
1089 p_object_version_number => p_rec.object_version_number);
1090 --
1091 chk_val_1
1092 (p_val_1 => p_rec.val_1,
1093 p_ext_crit_val_id => p_rec.ext_crit_val_id,
1094 p_ext_crit_typ_id => p_rec.ext_crit_typ_id,
1095 p_effective_date => p_effective_date,
1096 p_business_group_id => p_rec.business_group_id,
1097 p_ext_crit_bg_id => p_rec.ext_crit_bg_id,
1098 p_legislation_code => p_rec.legislation_code,
1099 p_object_version_number => p_rec.object_version_number,
1100 p_val_2 => p_rec.val_2
1101 );
1102 --
1103 chk_val_2
1104 (p_val_2 => p_rec.val_2,
1105 p_val_1 => p_rec.val_1,
1106 p_ext_crit_val_id => p_rec.ext_crit_val_id,
1107 p_ext_crit_typ_id => p_rec.ext_crit_typ_id,
1108 p_effective_date => p_effective_date,
1109 p_business_group_id => p_rec.business_group_id,
1110 p_ext_crit_bg_id => p_rec.ext_crit_bg_id,
1111 p_object_version_number => p_rec.object_version_number);
1112 --
1113 chk_val_dpndcy
1114 (p_val_1 => p_rec.val_1,
1115 p_val_2 => p_rec.val_2,
1116 p_ext_crit_val_id => p_rec.ext_crit_val_id,
1117 p_ext_crit_typ_id => p_rec.ext_crit_typ_id,
1118 p_object_version_number => p_rec.object_version_number);
1119 --
1120 chk_ext_crit_bg_id(p_ext_crit_val_id => p_rec.ext_crit_val_id,
1121 p_ext_crit_bg_id => p_rec.ext_crit_bg_id,
1122 p_ext_crit_typ_id => p_rec.ext_crit_typ_id,
1123 p_business_group_id => p_rec.business_group_id,
1124 p_object_version_number => p_rec.object_version_number)
1125 ;
1126
1127 hr_utility.set_location(' Leaving:'||l_proc, 10);
1128 End insert_validate;
1129 --
1130 -- ----------------------------------------------------------------------------
1131 -- |---------------------------< update_validate >----------------------------|
1132 -- ----------------------------------------------------------------------------
1133 Procedure update_validate(p_rec in ben_xcv_shd.g_rec_type
1134 ,p_effective_date in date) is
1135 --
1136 l_proc varchar2(72) := g_package||'update_validate';
1137 --
1138 Begin
1139 hr_utility.set_location('Entering:'||l_proc, 5);
1140 --
1141 -- Call all supporting business operations
1142 --
1143 --
1144 chk_startup_action(False
1145 ,p_rec.business_group_id
1146 ,p_rec.legislation_code);
1147 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
1148 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
1149 END IF;
1150 --
1151 chk_ext_crit_val_id
1152 (p_ext_crit_val_id => p_rec.ext_crit_val_id,
1153 p_object_version_number => p_rec.object_version_number);
1154 --
1155 chk_ext_crit_typ_id
1156 (p_ext_crit_val_id => p_rec.ext_crit_val_id,
1157 p_ext_crit_typ_id => p_rec.ext_crit_typ_id,
1158 p_object_version_number => p_rec.object_version_number);
1159 --
1160 chk_val_1
1161 (p_val_1 => p_rec.val_1,
1162 p_ext_crit_val_id => p_rec.ext_crit_val_id,
1163 p_ext_crit_typ_id => p_rec.ext_crit_typ_id,
1164 p_effective_date => p_effective_date,
1165 p_business_group_id => p_rec.business_group_id,
1166 p_ext_crit_bg_id => p_rec.ext_crit_bg_id ,
1167 p_legislation_code => p_rec.legislation_code,
1168 p_object_version_number => p_rec.object_version_number,
1169 p_val_2 => p_rec.val_2
1170 );
1171 --
1172 chk_val_2
1173 (p_val_2 => p_rec.val_2,
1174 p_val_1 => p_rec.val_1,
1175 p_ext_crit_val_id => p_rec.ext_crit_val_id,
1176 p_ext_crit_typ_id => p_rec.ext_crit_typ_id,
1177 p_effective_date => p_effective_date,
1178 p_business_group_id => p_rec.business_group_id,
1179 p_ext_crit_bg_id => p_rec.ext_crit_bg_id,
1180 p_object_version_number => p_rec.object_version_number);
1181 --
1182 chk_val_dpndcy
1183 (p_val_1 => p_rec.val_1,
1184 p_val_2 => p_rec.val_2,
1185 p_ext_crit_val_id => p_rec.ext_crit_val_id,
1186 p_ext_crit_typ_id => p_rec.ext_crit_typ_id,
1187 p_object_version_number => p_rec.object_version_number);
1188 --
1189 --
1190 chk_ext_crit_bg_id(p_ext_crit_val_id => p_rec.ext_crit_val_id,
1191 p_ext_crit_bg_id => p_rec.ext_crit_bg_id,
1192 p_ext_crit_typ_id => p_rec.ext_crit_typ_id,
1193 p_business_group_id => p_rec.business_group_id,
1194 p_object_version_number => p_rec.object_version_number)
1195 ;
1196 hr_utility.set_location(' Leaving:'||l_proc, 10);
1197 End update_validate;
1198 --
1199 -- ----------------------------------------------------------------------------
1200 -- |---------------------------< delete_validate >----------------------------|
1201 -- ----------------------------------------------------------------------------
1202 Procedure delete_validate(p_rec in ben_xcv_shd.g_rec_type) is
1203 --
1204 l_proc varchar2(72) := g_package||'delete_validate';
1205 --
1206 Begin
1207 hr_utility.set_location('Entering:'||l_proc, 5);
1208 --
1209 -- Call all supporting business operations
1210 --
1211 chk_startup_action(False
1212 ,ben_xcv_shd.g_old_rec.business_group_id
1213 ,ben_xcv_shd.g_old_rec.legislation_code);
1214 --
1215 hr_utility.set_location(' Leaving:'||l_proc, 10);
1216 End delete_validate;
1217 --
1218 --
1219 -- ---------------------------------------------------------------------------
1220 -- |---------------------< return_legislation_code >-------------------------|
1221 -- ---------------------------------------------------------------------------
1222 --
1223 function return_legislation_code
1224 (p_ext_crit_val_id in number) return varchar2 is
1225 --
1226 -- Declare cursor
1227 --
1228 cursor csr_leg_code is
1229 select a.legislation_code
1230 from per_business_groups_perf a,
1231 ben_ext_crit_val b
1232 where b.ext_crit_val_id = p_ext_crit_val_id
1233 and a.business_group_id(+) = b.business_group_id;
1234 --
1235 -- Declare local variables
1236 --
1237 l_legislation_code varchar2(150);
1238 l_proc varchar2(72) := g_package||'return_legislation_code';
1239 --
1240 begin
1241 --
1242 hr_utility.set_location('Entering:'|| l_proc, 10);
1243 --
1244 -- Ensure that all the mandatory parameter are not null
1245 --
1246 hr_api.mandatory_arg_error(p_api_name => l_proc,
1247 p_argument => 'ext_crit_val_id',
1248 p_argument_value => p_ext_crit_val_id);
1249 --
1250 open csr_leg_code;
1251 --
1252 fetch csr_leg_code into l_legislation_code;
1253 --
1254 if csr_leg_code%notfound then
1255 --
1256 close csr_leg_code;
1257 --
1258 -- The primary key is invalid therefore we must error
1259 --
1260 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1261 fnd_message.raise_error;
1262 --
1263 end if;
1264 --
1265 close csr_leg_code;
1266 --
1267 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1268 --
1269 return l_legislation_code;
1270 --
1271 end return_legislation_code;
1272 --
1273 end ben_xcv_bus;