[Home] [Help]
PACKAGE BODY: APPS.BEN_XCC_BUS
Source
1 Package Body ben_xcc_bus as
2 /* $Header: bexccrhi.pkb 120.1 2005/10/31 11:39:19 tjesumic noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xcc_bus.'; -- Global package name
9
10 --
11 -- ----------------------------------------------------------------------------
12 -- |----------------------< chk_startup_action >------------------------------|
13 -- ----------------------------------------------------------------------------
14 --
15 -- Description:
16 -- This procedure will check that the current action is allowed according
17 -- to the current startup mode.
18 --
19 -- ----------------------------------------------------------------------------
20 PROCEDURE chk_startup_action
21 (p_insert IN boolean
22 ,p_business_group_id IN number
23 ,p_legislation_code IN varchar2
24 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
25 --
26 BEGIN
27 --
28 -- Call the supporting procedure to check startup mode
29 --
30 IF (p_insert) THEN
31 --
32 -- Call procedure to check startup_action for inserts.
33 --
34 hr_startup_data_api_support.chk_startup_action
35 (p_generic_allowed => TRUE
36 ,p_startup_allowed => TRUE
37 ,p_user_allowed => TRUE
38 ,p_business_group_id => p_business_group_id
39 ,p_legislation_code => p_legislation_code
40 ,p_legislation_subgroup => p_legislation_subgroup
41 );
42 ELSE
43 --
44 -- Call procedure to check startup_action for updates and deletes.
45 --
46 hr_startup_data_api_support.chk_upd_del_startup_action
47 (p_generic_allowed => TRUE
48 ,p_startup_allowed => TRUE
49 ,p_user_allowed => TRUE
50 ,p_business_group_id => p_business_group_id
51 ,p_legislation_code => p_legislation_code
52 ,p_legislation_subgroup => p_legislation_subgroup
53 );
54 END IF;
55 --
56 END chk_startup_action;
57
58 --
59 -- ---------------------------------------------------------------------------
60 -- |----------------------< set_security_group_id >--------------------------|
61 -- ---------------------------------------------------------------------------
62 --
63 Procedure set_security_group_id
64 (p_ext_crit_cmbn_id in number
65 ) is
66 --
67 -- Declare cursor
68 --
69 cursor csr_sec_grp is
70 select pbg.security_group_id
71 from per_business_groups pbg
72 , ben_ext_crit_cmbn xcc
73 where xcc.ext_crit_cmbn_id = p_ext_crit_cmbn_id
74 and pbg.business_group_id = xcc.business_group_id;
75 --
76 -- Declare local variables
77 --
78 l_security_group_id number;
79 l_proc varchar2(72) := g_package||'set_security_group_id';
80 --
81 begin
82 --
83 hr_utility.set_location('Entering:'|| l_proc, 10);
84 --
85 -- Ensure that all the mandatory parameter are not null
86 --
87 hr_api.mandatory_arg_error
88 (p_api_name => l_proc
89 ,p_argument => 'ext_crit_cmbn_id'
90 ,p_argument_value => p_ext_crit_cmbn_id
91 );
92 --
93 open csr_sec_grp;
94 fetch csr_sec_grp into l_security_group_id;
95 --
96 if csr_sec_grp%notfound then
97 --
98 close csr_sec_grp;
99 --
100 -- The primary key is invalid therefore we must error
101 --
102 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
103 fnd_message.raise_error;
104 --
105 end if;
106 close csr_sec_grp;
107 --
108 -- Set the security_group_id in CLIENT_INFO
109 --
110 hr_api.set_security_group_id
111 (p_security_group_id => l_security_group_id
112 );
113 --
114 hr_utility.set_location(' Leaving:'|| l_proc, 20);
115 --
116 end set_security_group_id;
117
118 --
119 -- ----------------------------------------------------------------------------
120 -- |------< chk_ext_crit_cmbn_id >------|
121 -- ----------------------------------------------------------------------------
122 --
123 -- Description
124 -- This procedure is used to check that the primary key for the table
125 -- is created properly. It should be null on insert and
126 -- should not be able to be updated.
127 --
128 -- Pre Conditions
129 -- None.
130 --
131 -- In Parameters
132 -- ext_crit_cmbn_id PK of record being inserted or updated.
133 -- object_version_number Object version number of record being
134 -- inserted or updated.
135 --
136 -- Post Success
137 -- Processing continues
138 --
139 -- Post Failure
140 -- Errors handled by the procedure
141 --
142 -- Access Status
143 -- Internal table handler use only.
144 --
145 Procedure chk_ext_crit_cmbn_id(p_ext_crit_cmbn_id in number,
146 p_object_version_number in number) is
147 --
148 l_proc varchar2(72) := g_package||'chk_ext_crit_cmbn_id';
149 l_api_updating boolean;
150 --
151 Begin
152 --
153 hr_utility.set_location('Entering:'||l_proc, 5);
154 --
155 l_api_updating := ben_xcc_shd.api_updating
156 (p_ext_crit_cmbn_id => p_ext_crit_cmbn_id,
157 p_object_version_number => p_object_version_number);
158 --
159 if (l_api_updating
160 and nvl(p_ext_crit_cmbn_id,hr_api.g_number)
161 <> ben_xcc_shd.g_old_rec.ext_crit_cmbn_id) then
162 --
163 -- raise error as PK has changed
164 --
165 ben_xcc_shd.constraint_error('BEN_EXT_CRIT_CMBN_PK');
166 --
167 elsif not l_api_updating then
168 --
169 -- check if PK is null
170 --
171 if p_ext_crit_cmbn_id is not null then
172 --
173 -- raise error as PK is not null
174 --
175 ben_xcc_shd.constraint_error('BEN_EXT_CRIT_CMBN_PK');
176 --
177 end if;
178 --
179 end if;
180 --
181 hr_utility.set_location('Leaving:'||l_proc, 10);
182 --
183 End chk_ext_crit_cmbn_id;
184 --
185 -- ----------------------------------------------------------------------------
186 -- |------< chk_ext_crit_val_id >------|
187 -- ----------------------------------------------------------------------------
188 --
189 -- Description
190 -- This procedure checks that a referenced foreign key actually exists
191 -- in the referenced table.
192 --
193 -- Pre-Conditions
194 -- None.
195 --
196 -- In Parameters
197 -- p_ext_crit_cmbn_id PK
198 -- p_ext_crit_val_id ID of FK column
199 -- p_object_version_number object version number
200 --
201 -- Post Success
202 -- Processing continues
203 --
204 -- Post Failure
205 -- Error raised.
206 --
207 -- Access Status
208 -- Internal table handler use only.
209 --
210 Procedure chk_ext_crit_val_id (p_ext_crit_cmbn_id in number,
211 p_ext_crit_val_id in number,
212 p_object_version_number in number) is
213 --
214 l_proc varchar2(72) := g_package||'chk_ext_crit_val_id';
215 l_api_updating boolean;
216 l_dummy varchar2(1);
217 --
218 cursor c1 is
219 select null
220 from ben_ext_crit_val a
221 where a.ext_crit_val_id = p_ext_crit_val_id;
222 --
223 Begin
224 --
225 hr_utility.set_location('Entering:'||l_proc,5);
226 --
227 l_api_updating := ben_xcc_shd.api_updating
228 (p_ext_crit_cmbn_id => p_ext_crit_cmbn_id,
229 p_object_version_number => p_object_version_number);
230 --
231 if (l_api_updating
232 and nvl(p_ext_crit_val_id,hr_api.g_number)
233 <> nvl(ben_xcc_shd.g_old_rec.ext_crit_val_id,hr_api.g_number)
234 or not l_api_updating) then
235 --
236 -- check if ext_crit_val_id value exists in ben_ext_crit_val table
237 --
238 open c1;
239 --
240 fetch c1 into l_dummy;
241 if c1%notfound then
242 --
243 close c1;
244 --
245 -- raise error as FK does not relate to PK in ben_ext_crit_val
246 -- table.
247 --
248 ben_xcc_shd.constraint_error('BEN_EXT_CRIT_CMBN_FK1');
249 --
250 end if;
251 --
252 close c1;
253 --
254 end if;
255 --
256 hr_utility.set_location('Leaving:'||l_proc,10);
257 --
258 End chk_ext_crit_val_id;
259 --
260 -- ----------------------------------------------------------------------------
261 -- |------< chk_oper_cd >------|
262 -- ----------------------------------------------------------------------------
263 --
264 -- Description
265 -- This procedure is used to check that the lookup value is valid.
266 --
267 -- Pre Conditions
268 -- None.
269 --
270 -- In Parameters
271 -- ext_crit_cmbn_id PK of record being inserted or updated.
272 -- oper_cd Value of lookup code.
273 -- effective_date effective date
274 -- object_version_number Object version number of record being
275 -- inserted or updated.
276 --
277 -- Post Success
278 -- Processing continues
279 --
280 -- Post Failure
281 -- Error handled by procedure
282 --
283 -- Access Status
284 -- Internal table handler use only.
285 --
286 Procedure chk_oper_cd(p_ext_crit_cmbn_id in number,
287 p_oper_cd in varchar2,
288 p_effective_date in date,
289 p_business_group_id in varchar2,
290 p_object_version_number in number) is
291 --
292 l_proc varchar2(72) := g_package||'chk_oper_cd';
293 l_api_updating boolean;
294 --
295 Begin
296 --
297 hr_utility.set_location('Entering:'||l_proc, 5);
298 --
299 l_api_updating := ben_xcc_shd.api_updating
300 (p_ext_crit_cmbn_id => p_ext_crit_cmbn_id,
301 p_object_version_number => p_object_version_number);
302 --
303 if (l_api_updating
304 and p_oper_cd
305 <> nvl(ben_xcc_shd.g_old_rec.oper_cd,hr_api.g_varchar2)
306 or not l_api_updating) then
307 --
308 -- check if value of lookup falls within lookup type.
309 --
310 if p_business_group_id is not null then
311 /* BG is set, so use the existing call, with no modifications*/
312 if hr_api.not_exists_in_hr_lookups
313 (p_lookup_type => 'BEN_EXT_TTL_COND_OPER',
314 p_lookup_code => p_oper_cd,
315 p_effective_date => p_effective_date) then
316 --
317 -- raise error as does not exist as lookup
318 --
319 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
320 fnd_message.set_token('FIELD','p_oper_cd');
321 fnd_message.set_token('TYPE','OPERATOR');
322 fnd_message.raise_error;
323 --
324 end if;
325 else
326 /* BG is null, so alternative call is required */
327 if hr_api.not_exists_in_hrstanlookups
328 (p_lookup_type => 'BEN_EXT_TTL_COND_OPER',
329 p_lookup_code => p_oper_cd,
330 p_effective_date => p_effective_date) then
331 --
332 -- raise error as does not exist as lookup
333 --
334 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
335 fnd_message.set_token('FIELD','p_oper_cd');
336 fnd_message.set_token('TYPE','OPERATOR');
337 fnd_message.raise_error;
338 --
339 end if;
340 end if;
341 --
342 end if; /* (l_api_updating... */
343 --
344 hr_utility.set_location('Leaving:'||l_proc,10);
345 --
346 end chk_oper_cd;
347 --
348 -- ----------------------------------------------------------------------------
349 -- |------< chk_crit_typ_cd >------|
350 -- ----------------------------------------------------------------------------
351 --
352 -- Description
353 -- This procedure is used to check that the lookup value is valid.
354 --
355 -- Pre Conditions
356 -- None.
357 --
358 -- In Parameters
359 -- ext_crit_cmbn_id PK of record being inserted or updated.
360 -- crit_typ_cd Value of lookup code.
361 -- effective_date effective date
362 -- object_version_number Object version number of record being
363 -- inserted or updated.
364 --
365 -- Post Success
366 -- Processing continues
367 --
368 -- Post Failure
369 -- Error handled by procedure
370 --
371 -- Access Status
372 -- Internal table handler use only.
373 --
374 Procedure chk_crit_typ_cd(p_ext_crit_cmbn_id in number,
375 p_crit_typ_cd in varchar2,
376 p_effective_date in date,
377 p_business_group_id in varchar2,
378 p_object_version_number in number) is
379 --
380 l_proc varchar2(72) := g_package||'chk_crit_typ_cd';
381 l_api_updating boolean;
382 --
383 Begin
384 --
385 hr_utility.set_location('Entering:'||l_proc, 5);
386 --
387 l_api_updating := ben_xcc_shd.api_updating
388 (p_ext_crit_cmbn_id => p_ext_crit_cmbn_id,
389 p_object_version_number => p_object_version_number);
390 --
391 if (l_api_updating
392 and p_crit_typ_cd
393 <> nvl(ben_xcc_shd.g_old_rec.crit_typ_cd,hr_api.g_varchar2)
394 or not l_api_updating) then
395 --
396 -- check if value of lookup falls within lookup type.
397 --
398 if p_business_group_id is not null then
399 /* BG is set, so use the existing call, with no modifications*/
400 --
401 -- check if value of lookup falls within lookup type.
402 --
403 --
404 if hr_api.not_exists_in_hr_lookups
405 (p_lookup_type => 'BEN_EXT_CRIT_TYP',
406 p_lookup_code => p_crit_typ_cd,
407 p_effective_date => p_effective_date) then
408 --
409 -- raise error as does not exist as lookup
410 --
411 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
412 fnd_message.set_token('FIELD','p_crit_typ_cd');
413 fnd_message.set_token('TYPE','BEN_EXT_CRIT_TYP');
414 fnd_message.raise_error;
415 --
416 end if;
417 else
418 /* BG is null, so alternative call is required */
419 --
420 -- check if value of lookup falls within lookup type.
421 --
422 --
423 if hr_api.not_exists_in_hrstanlookups
424 (p_lookup_type => 'BEN_EXT_CRIT_TYP',
425 p_lookup_code => p_crit_typ_cd,
426 p_effective_date => p_effective_date) then
427 --
428 -- raise error as does not exist as lookup
429 --
430 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
431 fnd_message.set_token('FIELD','p_crit_typ_cd');
432 fnd_message.set_token('TYPE','BEN_EXT_CRIT_TYP');
433 fnd_message.raise_error;
434 --
435 end if;
436 end if;
437 --
438 end if; /* (l_api_updating... */
439 --
440 hr_utility.set_location('Leaving:'||l_proc,10);
441 --
442 end chk_crit_typ_cd;
443 --
444 --
445 -- ----------------------------------------------------------------------------
446 -- |------< chk_crit_date >------|
447 -- ----------------------------------------------------------------------------
448 --
449 -- Description
450 -- This procedure is used to check that if criteria is date, then
451 -- operator is '=' or 'between'
452 --
453 -- Pre Conditions
454 -- None.
455 --
456 -- In Parameters
457 -- ext_crit_cmbn_id PK of record being inserted or updated.
458 -- crit_typ_cd Value of lookup code.
459 -- oper_cd operator code
460 -- effective_date effective date
461 -- object_version_number Object version number of record being
462 -- inserted or updated.
463 --
464 -- Post Success
465 -- Processing continues
466 --
467 -- Post Failure
468 -- Error handled by procedure
469 --
470 -- Access Status
471 -- Internal table handler use only.
472 --
473 Procedure chk_crit_date(p_ext_crit_cmbn_id in number,
474 p_crit_typ_cd in varchar2,
475 p_oper_cd in varchar2,
476 p_effective_date in date,
477 p_object_version_number in number) is
478 --
479 l_proc varchar2(72) := g_package||'chk_crit_date';
480 l_api_updating boolean;
481 --
482 Begin
483 --
484 hr_utility.set_location('Entering:'||l_proc, 5);
485 --
486 l_api_updating := ben_xcc_shd.api_updating
487 (p_ext_crit_cmbn_id => p_ext_crit_cmbn_id,
488 p_object_version_number => p_object_version_number);
489 --
490 if (l_api_updating
491 and (p_oper_cd
492 <> nvl(ben_xcc_shd.g_old_rec.oper_cd,hr_api.g_varchar2)
493 or p_crit_typ_cd
494 <> nvl(ben_xcc_shd.g_old_rec.crit_typ_cd, hr_api.g_varchar2)
495 )
496 or not l_api_updating) then
497 --
498 -- check if criteria is date then operator is '=' or 'between'
499 --
500 --
501 if p_crit_typ_cd = 'CAD' or p_crit_typ_cd = 'CED' then
502 if p_oper_cd <> 'EQ' and p_oper_cd <> 'BE' then
503 fnd_message.set_name('BEN','BEN_92173_CRIT_DT_OPER_EQ_BET');
504 fnd_message.raise_error;
505 end if;
506 --
507 end if;
508 --
509 end if;
510 --
511 hr_utility.set_location('Leaving:'||l_proc,10);
512 --
513 end chk_crit_date;
514 --
515 --
516 -- ----------------------------------------------------------------------------
517 -- |------< chk_crit_chg_evt >------|
518 -- ----------------------------------------------------------------------------
519 --
520 -- Description
521 -- This procedure is used to check that if criteria is change event, then
522 -- operator is '=' or '!='
523 --
524 -- Pre Conditions
525 -- None.
526 --
527 -- In Parameters
528 -- ext_crit_cmbn_id PK of record being inserted or updated.
529 -- crit_typ_cd Value of lookup code.
530 -- oper_cd operator code
531 -- effective_date effective date
532 -- object_version_number Object version number of record being
533 -- inserted or updated.
534 --
535 -- Post Success
536 -- Processing continues
537 --
538 -- Post Failure
539 -- Error handled by procedure
540 --
541 -- Access Status
542 -- Internal table handler use only.
543 --
544 Procedure chk_crit_chg_evt(p_ext_crit_cmbn_id in number,
545 p_crit_typ_cd in varchar2,
546 p_oper_cd in varchar2,
547 p_val_1 in varchar2,
548 p_effective_date in date,
549 p_object_version_number in number) is
550 --
551 l_proc varchar2(72) := g_package||'chk_crit_chg_evt';
552 l_api_updating boolean;
553 --
554
555 cursor c1 is
556 select 'x' from
557 hr_lookups where
558 lookup_type = 'BEN_EXT_CHG_EVT'
559 and lookup_code = p_val_1
560 ;
561
562
563 cursor c2 is
564 select 'x' from
565 pay_event_groups where
566 event_group_id = p_val_1
567 ;
568
569 l_dummy varchar2(1) ;
570
571 Begin
572 --
573 hr_utility.set_location('Entering:'||l_proc, 5);
574 --
575 l_api_updating := ben_xcc_shd.api_updating
576 (p_ext_crit_cmbn_id => p_ext_crit_cmbn_id,
577 p_object_version_number => p_object_version_number);
578 --
579 if (l_api_updating
580 and (p_oper_cd
581 <> nvl(ben_xcc_shd.g_old_rec.oper_cd,hr_api.g_varchar2)
582 or
583 p_crit_typ_cd
584 <> nvl(ben_xcc_shd.g_old_rec.crit_typ_cd,hr_api.g_varchar2)
585 )
586 or not l_api_updating) then
587 --
588 -- check if criteria is change event then operator is '=' or '<>'
589 --
590 --
591 if p_crit_typ_cd in ( 'CCE' , 'CPE') then
592 if p_oper_cd <> 'EQ' and p_oper_cd <> 'NE' then
593 fnd_message.set_name('BEN','BEN_92174_CRIT_CHG_OPER_EQ_NEQ');
594 fnd_message.raise_error;
595 end if;
596 --
597 end if;
598
599
600 if p_crit_typ_cd = 'CCE' then
601
602 open c1 ;
603 fetch c1 into l_dummy ;
604 if c1%notfound then
605 close c1 ;
606 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
607 fnd_message.set_token('FIELD','Value 1');
608 fnd_message.set_token('TYPE','BEN_EXT_CHG_EVT');
609 fnd_message.raise_error;
610 end if ;
611 close c1 ;
612 end if ;
613
614 if p_crit_typ_cd = 'CPE' then
615
616 open c2 ;
617 fetch c2 into l_dummy ;
618 if c2%notfound then
619 close c2 ;
620 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
621 fnd_message.set_token('FIELD','Value 1');
622 fnd_message.set_token('TYPE','BEN_EXT_CHG_EVT');
623 fnd_message.raise_error;
624 end if ;
625 close c2 ;
626 end if ;
627 --
628 end if;
629 --
630 hr_utility.set_location('Leaving:'||l_proc,10);
631 --
632 end chk_crit_chg_evt;
633 --
634 --
635 -- ----------------------------------------------------------------------------
636 -- |------< chk_oper_between >------|
637 -- ----------------------------------------------------------------------------
638 --
639 -- Description
640 -- This procedure is used to check that if operator is between , then
641 -- value 1 and value 2 are not null
642 --
643 -- Pre Conditions
644 -- None.
645 --
646 -- In Parameters
647 -- ext_crit_cmbn_id PK of record being inserted or updated.
648 -- oper_cd operator code
649 -- val_1 value 1
650 -- val_2 value 2
651 -- effective_date effective date
652 -- object_version_number Object version number of record being
653 -- inserted or updated.
654 --
655 -- Post Success
656 -- Processing continues
657 --
658 -- Post Failure
659 -- Error handled by procedure
660 --
661 -- Access Status
662 -- Internal table handler use only.
663 --
664 Procedure chk_oper_between(p_ext_crit_cmbn_id in number,
665 p_oper_cd in varchar2,
666 p_val_1 in varchar2,
667 p_val_2 in varchar2,
668 p_effective_date in date,
669 p_object_version_number in number) is
670 --
671 l_proc varchar2(72) := g_package||'chk_oper_between';
672 l_api_updating boolean;
673 --
674 Begin
675 --
676 hr_utility.set_location('Entering:'||l_proc, 5);
677 --
678 l_api_updating := ben_xcc_shd.api_updating
679 (p_ext_crit_cmbn_id => p_ext_crit_cmbn_id,
680 p_object_version_number => p_object_version_number);
681 --
682 if (l_api_updating
683 and ((p_oper_cd
684 <> nvl(ben_xcc_shd.g_old_rec.oper_cd,hr_api.g_varchar2))
685 or (p_val_1 <> nvl(ben_xcc_shd.g_old_rec.val_1, hr_api.g_varchar2))
686 or (p_val_2 <> nvl(ben_xcc_shd.g_old_rec.val_2, hr_api.g_varchar2))
687 ))
688 or not l_api_updating then
689 --
690 -- check if operator is between then val_1 and val_2 are not null
691 --
692 --
693 if p_oper_cd = 'BE' then
694 if p_val_1 is null or p_val_2 is null then
695 fnd_message.set_name('BEN','BEN_92175_OPER_BET_VAL_NULL');
696 fnd_message.raise_error;
697 end if;
698 --
699 end if;
700 --
701 end if;
702 --
703 hr_utility.set_location('Leaving:'||l_proc,10);
704 --
705 end chk_oper_between;
706 --
707 --
708 -- ----------------------------------------------------------------------------
709 -- |------< chk_oper_eq_neq >------|
710 -- ----------------------------------------------------------------------------
711 --
712 -- Description
713 -- This procedure is used to check that if operator is '=' or '!=', then
714 -- value 1 is not null and value 2 is null
715 --
716 -- Pre Conditions
717 -- None.
718 --
719 -- In Parameters
720 -- ext_crit_cmbn_id PK of record being inserted or updated.
721 -- oper_cd operator code
722 -- val_1 value 1
723 -- val_2 value 2
724 -- effective_date effective date
725 -- object_version_number Object version number of record being
726 -- inserted or updated.
727 --
728 -- Post Success
729 -- Processing continues
730 --
731 -- Post Failure
732 -- Error handled by procedure
733 --
734 -- Access Status
735 -- Internal table handler use only.
736 --
737 Procedure chk_oper_eq_neq(p_ext_crit_cmbn_id in number,
738 p_oper_cd in varchar2,
739 p_val_1 in varchar2,
740 p_val_2 in varchar2,
741 p_effective_date in date,
742 p_object_version_number in number) is
743 --
744 l_proc varchar2(72) := g_package||'chk_oper_eq_neq';
745 l_api_updating boolean;
746 --
747 Begin
748 --
749 hr_utility.set_location('Entering:'||l_proc, 5);
750 --
751 l_api_updating := ben_xcc_shd.api_updating
752 (p_ext_crit_cmbn_id => p_ext_crit_cmbn_id,
753 p_object_version_number => p_object_version_number);
754 --
755 if (l_api_updating
756 and ((p_oper_cd
757 <> nvl(ben_xcc_shd.g_old_rec.oper_cd,hr_api.g_varchar2))
758 or (p_val_1 <> nvl(ben_xcc_shd.g_old_rec.val_1, hr_api.g_varchar2))
759 or (p_val_2 <> nvl(ben_xcc_shd.g_old_rec.val_2, hr_api.g_varchar2))
760 ))
761 or not l_api_updating then
762 --
763 -- check if operator is between then val_1 and val_2 are not null
764 --
765 --
766 if p_oper_cd = 'EQ' or p_oper_cd = 'NE' then
767 if p_val_1 is null or p_val_2 is not null then
768 fnd_message.set_name('BEN','BEN_92176_OPER_EQ_NEQ_VAL');
769 fnd_message.raise_error;
770 end if;
771 --
772 end if;
773 --
774 end if;
775 --
776 hr_utility.set_location('Leaving:'||l_proc,10);
777 --
778 end chk_oper_eq_neq;
779 --
780 -- ----------------------------------------------------------------------------
781 -- |---------------------------< insert_validate >----------------------------|
782 -- ----------------------------------------------------------------------------
783 Procedure insert_validate(p_rec in ben_xcc_shd.g_rec_type
784 ,p_effective_date in date) is
785 --
786 l_proc varchar2(72) := g_package||'insert_validate';
787 --
788 Begin
789 hr_utility.set_location('Entering:'||l_proc, 5);
790 --
791 -- Call all supporting business operations
792 --
793 chk_startup_action(True
794 ,p_rec.business_group_id
795 ,p_rec.legislation_code);
796 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
797 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
798 END IF;
799 --
800 chk_ext_crit_cmbn_id
801 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
802 p_object_version_number => p_rec.object_version_number);
803 --
804 chk_ext_crit_val_id
805 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
806 p_ext_crit_val_id => p_rec.ext_crit_val_id,
807 p_object_version_number => p_rec.object_version_number);
808 --
809 chk_oper_cd
810 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
811 p_oper_cd => p_rec.oper_cd,
812 p_effective_date => p_effective_date,
813 p_business_group_id => p_rec.business_group_id,
814 p_object_version_number => p_rec.object_version_number);
815 --
816 chk_crit_typ_cd
817 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
818 p_crit_typ_cd => p_rec.crit_typ_cd,
819 p_effective_date => p_effective_date,
820 p_business_group_id => p_rec.business_group_id,
821 p_object_version_number => p_rec.object_version_number);
822 --
823 chk_crit_date
824 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
825 p_crit_typ_cd => p_rec.crit_typ_cd,
826 p_oper_cd => p_rec.oper_cd,
827 p_effective_date => p_effective_date,
828 p_object_version_number => p_rec.object_version_number);
829 --
830 chk_crit_chg_evt
831 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
832 p_crit_typ_cd => p_rec.crit_typ_cd,
833 p_oper_cd => p_rec.oper_cd,
834 p_val_1 => p_rec.val_1,
835 p_effective_date => p_effective_date,
836 p_object_version_number => p_rec.object_version_number);
837 --
838 chk_oper_between
839 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
840 p_oper_cd => p_rec.oper_cd,
841 p_val_1 => p_rec.val_1,
842 p_val_2 => p_rec.val_2,
843 p_effective_date => p_effective_date,
844 p_object_version_number => p_rec.object_version_number);
845 --
846 chk_oper_eq_neq
847 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
848 p_oper_cd => p_rec.oper_cd,
849 p_val_1 => p_rec.val_1,
850 p_val_2 => p_rec.val_2,
851 p_effective_date => p_effective_date,
852 p_object_version_number => p_rec.object_version_number);
853 --
854 hr_utility.set_location(' Leaving:'||l_proc, 10);
855 End insert_validate;
856 --
857 -- ----------------------------------------------------------------------------
858 -- |---------------------------< update_validate >----------------------------|
859 -- ----------------------------------------------------------------------------
860 Procedure update_validate(p_rec in ben_xcc_shd.g_rec_type
861 ,p_effective_date in date) is
862 --
863 l_proc varchar2(72) := g_package||'update_validate';
864 --
865 Begin
866 hr_utility.set_location('Entering:'||l_proc, 5);
867 --
868 -- Call all supporting business operations
869 --
870 --
871 chk_startup_action(False
872 ,p_rec.business_group_id
873 ,p_rec.legislation_code);
874 IF hr_startup_data_api_support.g_startup_mode NOT IN ('GENERIC','STARTUP') THEN
875 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate bus_grp
876 END IF;
877 --
878 chk_ext_crit_cmbn_id
879 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
880 p_object_version_number => p_rec.object_version_number);
881 --
882 chk_ext_crit_val_id
883 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
884 p_ext_crit_val_id => p_rec.ext_crit_val_id,
885 p_object_version_number => p_rec.object_version_number);
886 --
887 chk_oper_cd
888 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
889 p_oper_cd => p_rec.oper_cd,
890 p_effective_date => p_effective_date,
891 p_business_group_id => p_rec.business_group_id,
892 p_object_version_number => p_rec.object_version_number);
893 --
894 chk_crit_typ_cd
895 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
896 p_crit_typ_cd => p_rec.crit_typ_cd,
897 p_effective_date => p_effective_date,
898 p_business_group_id => p_rec.business_group_id,
899 p_object_version_number => p_rec.object_version_number);
900 --
901 chk_crit_date
902 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
903 p_crit_typ_cd => p_rec.crit_typ_cd,
904 p_oper_cd => p_rec.oper_cd,
905 p_effective_date => p_effective_date,
906 p_object_version_number => p_rec.object_version_number);
907 --
908 chk_crit_chg_evt
909 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
910 p_crit_typ_cd => p_rec.crit_typ_cd,
911 p_oper_cd => p_rec.oper_cd,
912 p_val_1 => p_rec.val_1,
913 p_effective_date => p_effective_date,
914 p_object_version_number => p_rec.object_version_number);
915 --
916 chk_oper_between
917 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
918 p_oper_cd => p_rec.oper_cd,
919 p_val_1 => p_rec.val_1,
920 p_val_2 => p_rec.val_2,
921 p_effective_date => p_effective_date,
922 p_object_version_number => p_rec.object_version_number);
923 --
924 chk_oper_eq_neq
925 (p_ext_crit_cmbn_id => p_rec.ext_crit_cmbn_id,
926 p_oper_cd => p_rec.oper_cd,
927 p_val_1 => p_rec.val_1,
928 p_val_2 => p_rec.val_2,
929 p_effective_date => p_effective_date,
930 p_object_version_number => p_rec.object_version_number);
931 --
932 hr_utility.set_location(' Leaving:'||l_proc, 10);
933 End update_validate;
934 --
935 -- ----------------------------------------------------------------------------
936 -- |---------------------------< delete_validate >----------------------------|
937 -- ----------------------------------------------------------------------------
938 Procedure delete_validate(p_rec in ben_xcc_shd.g_rec_type
939 ,p_effective_date in date) is
940 --
941 l_proc varchar2(72) := g_package||'delete_validate';
942 --
943 Begin
944 hr_utility.set_location('Entering:'||l_proc, 5);
945 --
946 -- Call all supporting business operations
947 --
948 chk_startup_action(False
949 ,ben_xcc_shd.g_old_rec.business_group_id
950 ,ben_xcc_shd.g_old_rec.legislation_code);
951 hr_utility.set_location(' Leaving:'||l_proc, 10);
952 End delete_validate;
953 --
954 --
955 -- ---------------------------------------------------------------------------
956 -- |---------------------< return_legislation_code >-------------------------|
957 -- ---------------------------------------------------------------------------
958 --
959 function return_legislation_code
960 (p_ext_crit_cmbn_id in number) return varchar2 is
961 --
962 -- Declare cursor
963 --
964 cursor csr_leg_code is
965 select a.legislation_code
966 from per_business_groups a,
967 ben_ext_crit_cmbn b
968 where b.ext_crit_cmbn_id = p_ext_crit_cmbn_id
969 and a.business_group_id(+) = b.business_group_id;
970 --
971 -- Declare local variables
972 --
973 l_legislation_code varchar2(150);
974 l_proc varchar2(72) := g_package||'return_legislation_code';
975 --
976 begin
977 --
978 hr_utility.set_location('Entering:'|| l_proc, 10);
979 --
980 -- Ensure that all the mandatory parameter are not null
981 --
982 hr_api.mandatory_arg_error(p_api_name => l_proc,
983 p_argument => 'ext_crit_cmbn_id',
984 p_argument_value => p_ext_crit_cmbn_id);
985 --
986 open csr_leg_code;
987 --
988 fetch csr_leg_code into l_legislation_code;
989 --
990 if csr_leg_code%notfound then
991 --
992 close csr_leg_code;
993 --
994 -- The primary key is invalid therefore we must error
995 --
996 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
997 fnd_message.raise_error;
998 --
999 end if;
1000 --
1001 close csr_leg_code;
1002 --
1003 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1004 --
1005 return l_legislation_code;
1006 --
1007 end return_legislation_code;
1008 --
1009 end ben_xcc_bus;