1 Package Body ben_abr_bus as
2 /* $Header: beabrrhi.pkb 120.18 2008/05/15 10:36:51 krupani ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
9
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_abr_bus.'; -- Global package name
10 -- ----------------------------------------------------------------------------
11 -- |-----------------------< chk_acty_base_rt_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 -- acty_base_rt_id PK of record being inserted or updated.
24 -- effective_date Effective Date of session
25 -- object_version_number Object version number of record being
26 -- inserted or updated.
27 ----
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Errors handled by the procedure
33 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_acty_base_rt_id(p_acty_base_rt_id in number,
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_acty_base_rt_id';
42 l_api_updating boolean;
43 --
44 Begin
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 --
48 l_api_updating := ben_abr_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_acty_base_rt_id => p_acty_base_rt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_acty_base_rt_id,hr_api.g_number)
55 <> ben_abr_shd.g_old_rec.acty_base_rt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_abr_shd.constraint_error('BEN_ACTY_BASE_RT_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_acty_base_rt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_abr_shd.constraint_error('BEN_ACTY_BASE_RT_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_acty_base_rt_id;
78 --
79 --
80 -- ---------------------------------------------------------------------------
81 -- |------------------------< chk_name_unique >-------------------------------|
82 -- ----------------------------------------------------------------------------
83 --
84 -- Description
85 -- ensure that the Activity Base Rate Name is unique
86 -- within business_group
87 --
88 -- Pre Conditions
89 -- None.
90 --
91 -- In Parameters
92 -- p_name is Acty_Base_Rt name
93 -- p_acty_base_rt_id is acty_base_rt_id
94 -- p_business_group_id
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Errors handled by the procedure
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 -- ----------------------------------------------------------------------------
106 Procedure chk_name_unique
107 ( p_acty_base_rt_id in varchar2
108 ,p_name in varchar2
109 ,p_business_group_id in number)
110 is
111 l_proc varchar2(72) := g_package||'chk_name_unique';
112 l_dummy char(1);
113 cursor c1 is select null
114 from ben_acty_base_rt_f
115 Where acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
116 and name = p_name
117 and business_group_id = p_business_group_id;
118 --
119 Begin
120 hr_utility.set_location('Entering:'||l_proc, 5);
121 hr_utility.set_location('p_acty_base_rt_id' || p_acty_base_rt_id,235411);
122 --
123 open c1;
124 fetch c1 into l_dummy;
125 if c1%found then
126 close c1;
127 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
128 fnd_message.raise_error;
129 end if;
130 --
131 hr_utility.set_location('Leaving:'||l_proc, 15);
132 End chk_name_unique;
133 --
134 --ICM
135 -- |------------------------< chk_elem_unique >-------------------------------|
136 -- ----------------------------------------------------------------------------
137 --
138 -- Description
139 -- ensure that the same Element is not attached to more than one ICM Rates.
140 --
141 -- Pre Conditions
142 -- None.
143 --
144 -- In Parameters
145 -- p_name is Acty_Base_Rt name
146 -- p_acty_base_rt_id is acty_base_rt_id
147 -- p_business_group_id
148 -- p_element_type_id
149 -- p_rt_mlt_cd
150 -- Post Success
151 -- Processing continues
152 --
153 -- Post Failure
154 -- Errors handled by the procedure
155 --
156 -- Access Status
157 -- Internal table handler use only.
158 --
159 -- ----------------------------------------------------------------------------
160 Procedure chk_elem_unique
161 ( p_acty_base_rt_id in varchar2
162 ,p_effective_date in date
163 ,p_business_group_id in number
164 ,p_element_type_id in number
168 l_proc varchar2(72) := g_package||'chk_elem_unique';
165 ,p_rt_mlt_cd in varchar2
166 ,p_rec in ben_abr_shd.g_rec_type)
167 is
169 --
170 cursor c_oipl is
171 select opt_id, pl_id
172 from ben_oipl_f
173 where oipl_id = p_rec.oipl_id
174 and p_effective_date between effective_start_date and effective_end_date;
175 --
176 l_oipl c_oipl%rowtype;
177 --
178 cursor c_pl_typ(p_pl_id number) is
179 select opt_typ_cd
180 from ben_pl_typ_f ptp,
181 ben_pl_f pln
182 where pln.pl_id = p_pl_id
183 and ptp.pl_typ_id = pln.pl_typ_id;
184 --
185 l_opt_typ_cd ben_pl_typ_f.opt_typ_cd%TYPE;
186 --
187 cursor c1 is
188 select null
189 from ben_acty_base_rt_f abr
190 where abr.element_type_id = p_element_type_id
191 and abr.acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
192 and abr.business_group_id = p_business_group_id
193 and p_effective_date between abr.effective_start_date and abr.effective_end_date
194 and abr.pl_id IN (
195 select pl_id from ben_pl_f pln, ben_pl_typ_f typ
196 where typ.opt_typ_cd ='ICM'
197 and typ.pl_typ_id = pln.pl_typ_id
198 and pln.pl_typ_id IN (
199 select pl_typ_id from ben_pl_f where pl_id IN
200 (select pl_id
201 from ben_acty_base_rt_f
202 where business_group_id = p_business_group_id
203 and element_type_id = p_element_type_id)));
204 --
205 l_element_check varchar2(1);
206 --
207 --Same Comp Obj of ICM cant have more than one Rate Check
208 cursor c_duplicate_rate_pln(p_pl_id number) is
209 select null
210 from ben_acty_base_rt_f abr
211 where abr.pl_id = nvl(p_pl_id,-1)
212 and abr.acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
213 and abr.business_group_id = p_business_group_id
214 and p_effective_date between abr.effective_start_date and abr.effective_end_date;
215 --
216 cursor c_duplicate_rate_oipl(p_oipl_id number) is
217 select null
218 from ben_acty_base_rt_f abr
219 where abr.oipl_id = nvl(p_oipl_id,-1)
220 and abr.acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
221 and abr.business_group_id = p_business_group_id
222 and p_effective_date between abr.effective_start_date and abr.effective_end_date;
223 --
224 l_duplicate varchar2(1);
225 l_dup boolean := FALSE;
226 --
227 Begin
228 hr_utility.set_location('Entering:'||l_proc, 5);
229 --
230 --ICM Changes
231 if p_rec.pl_id is not null then
232 --
233 open c_pl_typ(p_rec.pl_id);
234 fetch c_pl_typ into l_opt_typ_cd;
235 close c_pl_typ;
236 --
237 if l_opt_typ_cd = 'ICM' then
238 --
239 open c_duplicate_rate_pln(p_rec.pl_id);
240 fetch c_duplicate_rate_pln into l_duplicate;
241 if c_duplicate_rate_pln%found then
242 --
243 l_dup := TRUE;
244 --
245 end if;
246 --
247 close c_duplicate_rate_pln;
248 --
249 end if;
250 --
251 end if;
252 --
253 if p_rec.oipl_id is not null then
254 -- get opt_id and pl_id
255 open c_oipl;
256 fetch c_oipl into l_oipl ;
257 close c_oipl;
258 --
259 open c_pl_typ(l_oipl.pl_id);
260 fetch c_pl_typ into l_opt_typ_cd;
261 close c_pl_typ;
262 --
263 if l_opt_typ_cd = 'ICM' then
264 --
265 open c_duplicate_rate_oipl(p_rec.oipl_id);
266 fetch c_duplicate_rate_oipl into l_duplicate;
267 if c_duplicate_rate_oipl%found then
268 --
269 l_dup := TRUE;
270 --
271 end if;
272 --
273 close c_duplicate_rate_oipl;
274 --
275 end if;
276 --
277 end if;
278 --
279 --ICM Changes
280 if l_opt_typ_cd = 'ICM' then
281 --
282 if l_dup then --Trying to define Duplicate rate for the comp obj
283 --
284 fnd_message.set_name('BEN','BEN_94663_DUPLICATE_RATE');
285 fnd_message.raise_error;
286 --
287 end if;
288 --
289 open c1;
290 fetch c1 into l_element_check;
291 if c1%found then
292 --
293 close c1;
294 fnd_message.set_name('BEN','BEN_94662_DUPLICATE_ELEMENT');
295 fnd_message.raise_error;
296 --
297 end if;
298 close c1;
299 --
300 --For checking proper rt_mlt_cd for ICM Plans
301 if p_rt_mlt_cd in ('CL','CLANDCVG','CVG','PRNT','PRNTANDCVG','AP','APANDCVG','SAREC') then
302 --
303 fnd_message.set_name('BEN','BEN_94664_INVALID_CALC_METHOD');
304 fnd_message.raise_error;
305 --
306 end if;
307 --
308 end if;
309 --
310 hr_utility.set_location('Leaving:'||l_proc, 15);
311 End chk_elem_unique;
312 --
313 --ICM
314 --
315 -- ----------------------------------------------------------------------------
316 -- |----------------------------< chk_ordr_num >---------------------------|
317 -- ----------------------------------------------------------------------------
318 --
319 -- Description
320 -- This procedure is used to check that if display on enrollment check box
321 -- is selected and the Plan Type display code for self service is assigned
322 --
323 -- Pre Conditions
324 -- None.
325 --
326 -- In Parameters
327 -- acty_base_rt_id PK of record being inserted or updated.
328 -- effective_date effective date
329 -- object_version_number Object version number of record being
330 -- inserted or updated.
334 -- p_ordr_num Ordr_num
331 -- p_business_group_id Business group id
332 -- p_pl_id or p_plip_id Plan/Plan in Option/Plan in Program ID to which
333 -- or p_oipl_id or p_oiplip_id Standard Rate is associated
335 --
336 -- Post Success
337 -- Processing continues
338 --
339 -- Post Failure
340 -- Error handled by procedure
341 --
342 -- Access Status
343 -- Internal table handler use only.
344 --
345 Procedure chk_ordr_num(p_acty_base_rt_id in number,
346 p_effective_date in date,
347 p_object_version_number in number,
348 p_business_group_id in number,
349 p_pl_id in number,
350 p_plip_id in number,
351 p_oipl_id in number,
352 p_opt_id in number,
353 p_oiplip_id in number,
354 p_ordr_num in number) is
355 --
356 l_proc varchar2(72) := g_package||'chk_ordr_num';
357 l_api_updating boolean;
358 l_dummy number;
359 --
360 cursor c_abr is
361 select 1
362 from ben_acty_base_rt_f abr
363 where abr.acty_base_rt_id <> nvl(p_acty_base_rt_id,-9999)
364 and (
365 nvl(abr.pl_id,-1) = nvl(p_pl_id,-99999)
366 or nvl(abr.plip_id,-1) = nvl(p_plip_id,-99999)
367 or nvl(abr.oipl_id,-1) = nvl(p_oipl_id,-99999)
368 or nvl(abr.opt_id,-1) = nvl(p_opt_id,-99999)
369 or nvl(abr.oiplip_id,-1) = nvl(p_oiplip_id,-99999)
370 )
371 and abr.ordr_num = p_ordr_num
372 and abr.business_group_id = p_business_group_id
373 and p_effective_date between abr.effective_start_date and abr.effective_end_date;
374 --
375 cursor c_pl_typ(p_pl_id number) is
376 select opt_dsply_fmt_cd
377 from ben_pl_typ_v plt,
378 ben_pl_v pln
379 where pln.pl_id = p_pl_id
380 and plt.pl_typ_id = pln.pl_typ_id;
381 --
382 cursor c_plip is
383 select pl_id
384 from ben_plip_f
385 where plip_id = p_plip_id;
386 --
387 cursor c_oipl is
388 select pl_id
389 from ben_oipl_f
390 where oipl_id = p_oipl_id;
391 --
392 cursor c_oiplip is
393 select oipl.pl_id
394 from ben_oiplip_f oiplip,
395 ben_oipl_f oipl
396 where oiplip.oiplip_id = p_oiplip_id
397 and oipl.oipl_id = oiplip.oipl_id;
398 --
399 l_pl_id number := nvl(p_pl_id,-1);
400 l_opt_dsply_fmt_cd varchar2(30);
401 --
402 Begin
403 --
404 hr_utility.set_location('Entering:'||l_proc, 5);
405 --
406 l_api_updating := ben_abr_shd.api_updating
407 (p_acty_base_rt_id => p_acty_base_rt_id,
408 p_effective_date => p_effective_date,
409 p_object_version_number => p_object_version_number);
410 --
411 if (l_api_updating
412 and p_ordr_num
413 <> nvl(ben_abr_shd.g_old_rec.ordr_num,hr_api.g_number)
414 or not l_api_updating)
415 and p_ordr_num is not null then
416 --
417 -- check if value of lookup falls within lookup type.
418 --
419 if hr_api.not_exists_in_hr_lookups
420 (p_lookup_type => 'BEN_RT_ORDR_NUM',
421 p_lookup_code => to_char(p_ordr_num),
422 p_effective_date => p_effective_date) then
423 --
424 -- raise error as does not exist as lookup
425 --
426 fnd_message.set_name('BEN','BEN_91438_LOOKUP_VALUE_INVALID');
427 fnd_message.set_token('FIELD',to_char(p_ordr_num));
428 fnd_message.set_token('TYPE','BEN_RT_ORDR_NUM');
429 fnd_message.raise_error;
430 --
431 end if;
432 --
433 if l_pl_id = -1 and p_plip_id is null then
434 open c_plip;
435 fetch c_plip into l_pl_id;
436 close c_plip;
437 elsif l_pl_id = -1 and p_oipl_id is null then
438 open c_oipl;
439 fetch c_oipl into l_pl_id;
440 close c_oipl;
441 elsif l_pl_id = -1 and p_oiplip_id is null then
442 open c_oiplip;
443 fetch c_oiplip into l_pl_id;
444 close c_oiplip;
445 end if;
446 --
447 open c_pl_typ (l_pl_id);
448 fetch c_pl_typ into l_opt_dsply_fmt_cd;
449 close c_pl_typ;
450 --
451 if l_opt_dsply_fmt_cd = 'VRT' then -- added for 3042658, for horizontal plan types ordr_num = 1
452 open c_abr;
453 fetch c_abr into l_dummy;
454 close c_abr;
455 --
456 if l_dummy = 1 then
457 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
458 fnd_message.raise_error;
459 end if;
460 --
461 end if;
462 --
463 end if;
464 --
465 end chk_ordr_num;
466
467 -- ----------------------------------------------------------------------------
468 --
469 -- ---------------------------------------------------------------------------
470 -- |------------------------< chk_ext_inp_values >----------------------------|
471 -- ----------------------------------------------------------------------------
472 --
473 -- Description
474 -- ensure that there are no records in ben_extra_input_values
475 --
476 -- Pre Conditions
477 -- None.
478 --
479 -- In Parameters
480 -- p_acty_base_rt_id is acty_base_rt_id
481 --
482 -- Post Success
483 -- Processing continues
484 --
485 -- Post Failure
486 -- Errors handled by the procedure
487 --
488 -- Access Status
489 -- Internal table handler use only.
490 --
494 p_input_va_calc_rl in number,
491 -- ----------------------------------------------------------------------------
492 Procedure chk_ext_inp_values
493 ( p_acty_base_rt_id in number,
495 p_object_version_number in number,
496 p_effective_date in date)
497 is
498 l_proc varchar2(72) := g_package||'chk_ext_inp_values';
499 l_api_updating boolean;
500 l_dummy char(1);
501 cursor c1 is select null
502 from ben_extra_input_values
503 Where acty_base_rt_id = nvl(p_acty_base_rt_id,-1);
504 --
505 Begin
506 hr_utility.set_location('Entering:'||l_proc, 5);
507 --
508 l_api_updating := ben_abr_shd.api_updating
509 (p_acty_base_rt_id => p_acty_base_rt_id,
510 p_effective_date => p_effective_date,
511 p_object_version_number => p_object_version_number);
512 --
513 if (l_api_updating
514 and nvl(p_input_va_calc_rl,hr_api.g_number)
515 <> nvl(ben_abr_shd.g_old_rec.input_va_calc_rl,hr_api.g_number)
516 )
517 then
518 open c1;
519 fetch c1 into l_dummy;
520 if c1%found then
521 close c1;
522 fnd_message.set_name('BEN','BEN_93185_INP_VAL_EXISTS');
523 fnd_message.raise_error;
524 end if;
525 end if;
526 --
527 hr_utility.set_location('Leaving:'||l_proc, 15);
528 End chk_ext_inp_values;
529 --
530 --
531
532 -- ----------------------------------------------------------------------------
533
534 Procedure chk_impted_incom_rate_unique
535 ( p_acty_base_rt_id in varchar2
536 ,p_pl_id in varchar2
537 ,p_rt_usg_cd in varchar2
538 ,p_effective_start_date in date
539 ,p_business_group_id in number )
540 is
541 l_proc varchar2(72) := g_package||'chk_impted_incom_rate_unique';
542 l_dummy char(1);
543 l_name ben_acty_base_rt_f.name%type ;
544 -- the date is validated system allow the the different rate for a plan in different periods
545 -- the futer data also validated
546 cursor c1 is select name
547 from ben_acty_base_rt_f
548 Where acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
549 and pl_id = p_pl_id
550 and (p_effective_start_date between effective_start_date and effective_end_date
551 or p_effective_start_date < effective_start_date ) ;
552
553 --- cursor to chek imputed income plan
554 cursor c_is_pl_imputed is
555 select '1' from BEN_PL_F PLN where pl_id = p_pl_id and
556 p_effective_start_date between effective_start_date and effective_end_date
557 and PLN.imptd_incm_calc_cd in ('PRTT','DCA','DPNT','SPS');
558
559 --
560 Begin
561 hr_utility.set_location('Entering:'||l_proc, 5);
562 --
563 --- when the rate is imputing chek the plan in imputing
564 open c_is_pl_imputed;
565 fetch c_is_pl_imputed into l_dummy;
566 if c_is_pl_imputed%found and p_rt_usg_cd <> 'IMPTDINC' then
567 fnd_message.set_name('BEN','BEN_92556_IMPTD_NO_STD_RATE');
568 fnd_message.raise_error;
569 end if;
570 close c_is_pl_imputed;
571 If p_rt_usg_cd = 'IMPTDINC' then
572 --- chek the multiple rate
573 open c1;
574 fetch c1 into l_name;
575 if c1%found then
576 close c1;
577 fnd_message.set_name('BEN', 'BEN_92555_IMPTD_RATE_EXISTS');
578 fnd_message.set_token('RATE', l_name);
579 fnd_message.raise_error;
580 end if;
581 End if;
582 --
583 hr_utility.set_location('Leaving:'||l_proc, 15);
584 End chk_impted_incom_rate_unique;
585 --
586
587 --
588 --
589 -- ----------------------------------------------------------------------------
590 -- |---------------------------< chk_pl_with_cvg >----------------------------|
591 -- ----------------------------------------------------------------------------
592
593 Function chk_pl_cvg
594 (p_pl_id in number ,
595 p_effective_date in date ) return varchar2 is
596
597 l_proc varchar2(72) := g_package||'chk_pl_with_cvg';
598
599 cursor c_cvg is select 'x' from ben_cvg_amt_calc_mthd_f
600 where pl_id = p_pl_id and
601 entr_val_at_enrt_flag = 'Y' and
602 p_effective_Date between effective_start_date and effective_end_Date ;
603
604 l_dummy varchar2(1) ;
605
606 Begin
607 hr_utility.set_location('Entering:'||l_proc, 5);
608 open c_cvg ;
609 fetch c_cvg into l_dummy ;
610 if c_cvg%found then
611 close c_cvg ;
612 hr_utility.set_location('Leaving:'||l_proc,10);
613 return ('Y' );
614 end if ;
615 close c_cvg ;
616 return ('N' );
617 end chk_pl_cvg ;
618
619 -- ----------------------------------------------------------------------------
620 -- |---------------------------< chk_oipl_with_cvg >----------------------------|
621 -- ----------------------------------------------------------------------------
622
623 Function chk_oipl_with_cvg
624 (p_oipl_id in number ,
625 p_effective_date in date ) return varchar2 is
626
627 l_proc varchar2(72) := g_package||'chk_oipl_with_cvg';
628
629 cursor c_cvg is select 'x' from ben_cvg_amt_calc_mthd_f
630 where oipl_id = p_oipl_id and
631 entr_val_at_enrt_flag = 'Y' and
632 p_effective_Date between effective_start_date and effective_end_Date ;
633
634 l_dummy varchar2(1) ;
635
636 Begin
640 if c_cvg%found then
637 hr_utility.set_location('Entering:'||l_proc, 5);
638 open c_cvg ;
639 fetch c_cvg into l_dummy ;
641 close c_cvg ;
642 hr_utility.set_location('Leaving:'||l_proc,10);
643 return ('Y' );
644 end if ;
645 close c_cvg ;
646 return ('N' );
647 end chk_oipl_with_cvg ;
648
649
650
651 -- ----------------------------------------------------------------------------
652 -- |------< chk_entr_at_enrt_with_cvg >------|
653 -- ----------------------------------------------------------------------------
654 --
655 -- Description
656 --
657 --
658 --
659 -- when the mlt_cd id CVG and entr_at_enrt is Y then the
660 -- then the com object is validated with coverage
661 -- for the them object coverage also entr_At_enrt then
662 -- then error is to be thrown , the same validation is to be done in
663 -- coverage side too
664 -- when the level is plan and plan in program is validated with plan
665 -- option in plan and option in plan in progrm is validated with option in plan
666 -- in coverage plan and option inplan level only supported now
667
668 Procedure chk_entr_at_enrt_with_cvg(p_rt_mlt_cd in varchar2,
669 p_entr_val_at_enrt_flag in varchar2,
670 p_pl_id in number,
671 p_plip_id in number,
672 p_oipl_id in number,
673 p_oiplip_id in number,
674 p_effective_date in date ) is
675
676 l_pl_id number(15) := p_pl_id ;
677 l_oipl_id number(15) := p_oipl_id ;
678 l_dummy varchar2(1) ;
679 l_proc varchar2(72) := g_package||'chk_entr_at_enrt_with_cvg';
680
681 cursor c_oipl is select pl_id from
682 ben_plip_f where plip_id = p_plip_id and
683 p_effective_date between effective_start_date and effective_end_date ;
684
685 cursor c_oiplip is select oipl_id from
686 ben_oiplip_f where oiplip_id = p_oiplip_id and
687 p_effective_date between effective_start_date and effective_end_date ;
688
689 begin
690 hr_utility.set_location('Entering:'||l_proc, 5);
691 if p_rt_mlt_cd = 'CVG' and p_entr_val_at_enrt_flag = 'Y' then
692 -- decide the level
693 If p_pl_id is not null or p_plip_id is not null then
694 -- plan level
695 if p_plip_id is not null then
696 open c_oipl ;
697 fetch c_oipl into l_pl_id ;
698 If c_oipl%notfound then
699 close c_oipl ;
700 ben_abr_shd.constraint_error('BEN_OIPL_PK') ;
701 end if ;
702 close c_oipl ;
703 else
704 l_pl_id := p_pl_id ;
705 end if ;
706 if chk_pl_cvg (p_pl_id => l_pl_id , p_effective_date => p_effective_date ) ='Y' then
707 fnd_message.set_name('BEN','BEN_92653_ENTR_VAL_RATE_CVG');
708 fnd_message.raise_error;
709 end if ;
710
711 elsif p_oipl_id is not null or p_oiplip_id is not null then
712 -- plan in option levele
713 if p_oiplip_id is not null then
714 open c_oiplip ;
715 fetch c_oiplip into l_oipl_id ;
716 If c_oiplip%notfound then
717 close c_oiplip ;
718 ben_abr_shd.constraint_error('BEN_OIPLIP_PK') ;
719 end if ;
720 close c_oiplip ;
721 else
722 l_oipl_id := p_oipl_id ;
723 end if ;
724 if chk_oipl_with_cvg(l_oipl_id,p_effective_Date) = 'Y' then
725 fnd_message.set_name('BEN','BEN_92653_ENTR_VAL_RATE_CVG');
726 fnd_message.raise_error;
727 end if ;
728 else
729 --if any other level added
730 null;
731 end if ;
732 end if ;
733 hr_utility.set_location('Leaving:'||l_proc,10);
734 end chk_entr_at_enrt_with_cvg;
735
736
737 -- ----------------------------------------------------------------------------
738 -- |-------------------< chk_elmt_typ_input_val_rqd >-------------------------|
739 -- ----------------------------------------------------------------------------
740 --
741 -- Description
742 -- This procedure is used to check if the Status is anything other than
743 -- Pending that both Element_Type_id and Input_Value_id must have a value.
744 --
745 -- Pre Conditions
746 -- None.
747 --
748 -- In Parameters
749 -- p_acty_base_rt_stat_cd Status of record
750 -- p_input_value_id value of input_value_id field
751 -- p_element_type_id value of element_type_id
752 --
753 -- Post Success
754 -- Processing continues
755 --
756 -- Post Failure
757 -- Error handled by procedure
758 --
759 -- Access Status
760 -- Internal table handler use only.
761 --
762 ------------------------------------------------------------------------------
763 procedure chk_elmt_typ_input_val_rqd
764 (p_rec in ben_abr_shd.g_rec_type,
765 p_acty_base_rt_stat_cd in varchar2,
766 p_input_value_id in number,
767 p_ele_rqd_flag in varchar2,
768 p_effective_date in date,
769 p_element_type_id in number) is
770 --
771 --ICM Changes
772 cursor c_oipl is
773 select opt_id, pl_id
774 from ben_oipl_f
775 where oipl_id = p_rec.oipl_id
776 and p_effective_date between effective_start_date and effective_end_date;
777 --
778 l_oipl c_oipl%rowtype;
779 --
780 cursor c_pl_typ(p_pl_id number) is
784 where pln.pl_id = p_pl_id
781 select nvl(opt_typ_cd,'ZZZ') --Bug 7042738
782 from ben_pl_typ_f ptp,
783 ben_pl_f pln
785 and ptp.pl_typ_id = pln.pl_typ_id;
786 --
787 l_opt_typ_cd ben_pl_typ_f.opt_typ_cd%TYPE := 'ZZZ'; --Bug 7042738
788 --ICM Changes
789 --
790 begin
791 --
792 --ICM Changes
793 if p_rec.pl_id is not null then
794 --
795 open c_pl_typ(p_rec.pl_id);
796 fetch c_pl_typ into l_opt_typ_cd;
797 close c_pl_typ;
798 --
799 end if;
800 --
801 if p_rec.oipl_id is not null then
802 -- get opt_id and pl_id
803 open c_oipl;
804 fetch c_oipl into l_oipl ;
805 close c_oipl;
806 --
807 open c_pl_typ(l_oipl.pl_id);
808 fetch c_pl_typ into l_opt_typ_cd;
809 close c_pl_typ;
810 --
811 end if;
812 --
813 --ICM Changes
814
815 if p_acty_base_rt_stat_cd <> 'P' and p_ele_rqd_flag = 'Y' then
816 --
817 if (((p_input_value_id is null OR p_element_type_id is null) AND l_opt_typ_cd <> 'ICM') OR (p_element_type_id is null AND l_opt_typ_cd = 'ICM')) then
818 --
819 -- Raise error as Element Type Id and Input Value Id need a
820 -- Value unless the Status = 'P' for pending.
821 --
822 fnd_message.set_name('BEN','BEN_91933_CHK_STAT_FOR_RQD');
823 fnd_message.raise_error;
824 --
825 end if;
826 --
827 end if;
828 --
829 end chk_elmt_typ_input_val_rqd;
830 --
831 procedure chk_prtl_mo_det_mthd_cd
832 (p_prtl_mo_det_mthd_cd in varchar2,
833 p_ele_entry_val_cd in varchar2) is
834 begin
835 --
836 if p_prtl_mo_det_mthd_cd in ('NONE','PRTVAL','WASHRULE','RL') then
837 if p_ele_entry_val_cd in ('DFND','CMCD','PYR') then
838 fnd_message.set_name('BEN','BEN_93060_NO_PRTN_DFND');
839 fnd_message.raise_error;
840 end if;
841 end if;
842 end chk_prtl_mo_det_mthd_cd;
843 -- ---------------------------------------------------------------------
844 -- |-------------------< chk_entr_ann_val_flag >-------------------------|
845 -- ----------------------------------------------------------------------------
846 --
847 -- Description
848 -- This procedure is used to check if the
849 -- ann_mn_elcn_val and ann_mx_elcn_val are required.
850 -- and mn_elcn_val and mx_elcn_val is null when entr_ann_val_flag is 'Y'
851 --
852 -- Pre Conditions
853 -- None.
854 --
855 -- In Parameters
856 -- p_entr_ann_val_flag value of entr_ann_val_flag
857 -- p_ann_mn_elcn_val value of ann mn_elcn_val
858 -- p_ann_mx_elcn_val value of ann_mx_elcn_val
859 -- p_mn_elcn_val value of mn_elcn_val
860 -- p_mx_elcn_val value of mx_elcn_val
861 -- p_det_pl_ytd_cntrs_cd value of det_pl_ytd_cntrs_cd - Expected Contributions
862 --
863 -- Post Success
864 -- Processing continues
865 --
866 -- Post Failure
867 -- Error handled by procedure
868 --
869 -- Access Status
870 -- Internal table handler use only.
871 --
872 ------------------------------------------------------------------------------
873 procedure chk_entr_ann_val_flag
874 (p_entr_ann_val_flag in varchar2,
875 p_ann_mn_elcn_val in number,
876 p_ann_mx_elcn_val in number,
877 p_mn_elcn_val in number,
878 p_mx_elcn_val in number,
879 p_det_pl_ytd_cntrs_cd in varchar2) is
880 begin
881
882 if p_entr_ann_val_flag = 'Y' then
883 --
884 if (p_ann_mn_elcn_val is null OR p_ann_mx_elcn_val is null) then
885 --
886 fnd_message.set_name('BEN','BEN_92442_ANN_FLAG_ON');
887 fnd_message.raise_error;
888 --
889 end if;
890
891 if (p_mn_elcn_val is not null OR p_mx_elcn_val is not null) then
892 --
893 fnd_message.set_name('BEN','BEN_92443_ANN_VALS_ONLY');
894 fnd_message.raise_error;
895 --
896 end if;
897 --
898 -- Bug : 3197632
899 -- Raise error if Enter Annual Value is checked and Expected Contributions is not selected.
900 /* As per bug update 3197632, error is no longer needed.
901 if p_det_pl_ytd_cntrs_cd is null then
902 --
903 fnd_message.set_name('BEN','BEN_93993_ANN_EXP_CONTRB_MDTRY');
904 fnd_message.raise_error;
905 --
906 end if;
907 */
908 --
909 end if;
910 --
911 end chk_entr_ann_val_flag;
912 --
913
914
915
916 Procedure chk_subj_to_imptd_incm(
917 p_acty_base_rt_id in number,
918 p_pl_id in number,
919 p_oipl_id in number,
920 p_plip_id in number,
921 p_oiplip_id in number,
922 p_acty_typ_cd in varchar2,
923 p_tx_typ_cd in varchar2,
924 p_subj_to_imptd_incm_flag in varchar2,
925 p_effective_date in date
926 ) is
927 --
928 l_proc varchar2(72) := g_package||'chk_subj_to_imptd_incm';
929 l_api_updating boolean;
930 l_prflvalue varchar2(4000) ;
931
932 cursor c_pln is
933 select 'x'
937 and p_effective_date between pln.effective_start_date
934 from ben_pl_f pln
935 where pln.pl_id = p_pl_id
936 and pln.subj_to_imptd_incm_typ_cd is not null
938 and pln.effective_end_date ;
939
940 cursor c_oipl is
941 select 'x'
942 from ben_pl_f pln,
943 ben_oipl_f oipl
944 where oipl.oipl_id = p_oipl_id
945 and pln.pl_id = oipl.pl_id
946 and pln.subj_to_imptd_incm_typ_cd is not null
947 and p_effective_date between pln.effective_start_date
948 and pln.effective_end_date
949 and p_effective_date between oipl.effective_start_date
950 and oipl.effective_end_date
951 ;
952
953 cursor c_plip is
954 select 'x'
955 from ben_pl_f pln,
956 ben_plip_f plip
957 where plip.plip_id = p_plip_id
958 and pln.pl_id = plip.pl_id
959 and pln.subj_to_imptd_incm_typ_cd is not null
960 and p_effective_date between pln.effective_start_date
961 and pln.effective_end_date
962 and p_effective_date between plip.effective_start_date
963 and plip.effective_end_date
964 ;
965
966
967 cursor c_oiplip is
968 select 'x'
969 from ben_pl_f pln,
970 ben_oipl_f oipl,
971 ben_oiplip_f oiplip
972 where oiplip.oiplip_id = p_oiplip_id
973 and oipl.oipl_id = oiplip.oipl_id
974 and pln.pl_id = oipl.pl_id
975 and pln.subj_to_imptd_incm_typ_cd is not null
976 and p_effective_date between pln.effective_start_date
977 and pln.effective_end_date
978 and p_effective_date between oipl.effective_start_date
979 and oipl.effective_end_date
980 and p_effective_date between oiplip.effective_start_date
981 and oiplip.effective_end_date
982
983 ;
984
985 l_dummy varchar2(15) ;
986
987 Begin
988 --
989 hr_utility.set_location('Entering:'||l_proc, 5);
990 l_prflvalue := fnd_profile.value('BEN_IMPTD_INCM_POST_TAX');
991 hr_utility.set_location('Profile:'||l_prflvalue, 99 );
992 if l_prflvalue = 'Y' then
993 /* this validation is not done becasuse the opt can not be validated
994 discussed with BP
995 if p_plip_id is not null then
996 open c_plip ;
997 fetch c_plip into l_dummy ;
998 close c_plip
999 end if ;
1000 if p_oipl_id is not null then
1001 open c_oipl ;
1002 fetch c_oipl into l_dummy ;
1003 close c_oipl
1004 end if ;
1005 if p_oiplip_id is not null then
1006 open c_oiplip ;
1007 fetch c_oiplip into l_dummy ;
1008 close c_oiplip
1009 end if ;
1010 if p_pln_id is not null then
1011 open c_pln ;
1012 fetch c_pln into l_dummy ;
1013 close c_pln
1014 end if ;
1015 */
1016 ---
1017 if not (p_tx_typ_cd = 'AFTERTAX' and substr(p_acty_typ_cd,1,2) = 'EE') then
1018 fnd_message.set_name('BEN','BEN_93397_SUBJ_IMPTD_INCOM_FLG');
1019 fnd_message.raise_error;
1020 --
1021 end if ;
1022
1023 end if ;
1024 hr_utility.set_location('Leaving:'||l_proc,10);
1025 --
1026 end chk_subj_to_imptd_incm;
1027
1028 -- ----------------------------------------------------------------------------
1029 -- |--------------------------< chk_all_rules >-------------------------------|
1030 -- ----------------------------------------------------------------------------
1031 --
1032 -- Description
1033 -- This procedure is used to check that the Formula Rules are valid.
1034 --
1035 -- Pre Conditions
1036 -- None.
1037 --
1038 -- In Parameters
1039 -- Partial Month Determination method rule p_prtl_mo_det_mthd_rl
1040 -- Partial Month Effective Date Determination rule p_prtl_mo_eff_dt_det_rl
1041 -- Rounding Rule p_rndg_rl
1042 -- p_prtl_mo_det_mthd_cd
1043 -- p_prtl_mo_eff_dt_det_cd
1044 -- p_rndg_cd
1045 -- p_val_calc_rl
1046 -- p_mn_mx_elcn_rl
1047 -- p_business_group_id
1048 -- effective_date effective date
1049 -- object_version_number Object version number of record being
1050 -- inserted or updated.
1051 --
1052 -- Post Success
1053 -- Processing continues
1054 --
1055 -- Post Failure
1056 -- Error handled by procedure
1057 --
1058 -- Access Status
1059 -- Internal table handler use only.
1060 --
1061 Procedure chk_all_rules(p_acty_base_rt_id in number,
1062 p_prtl_mo_det_mthd_rl in number,
1063 p_prtl_mo_eff_dt_det_rl in number,
1064 p_rndg_rl in number,
1065 p_lwr_lmt_calc_rl in number,
1066 p_upr_lmt_calc_rl in number,
1067 p_prtl_mo_det_mthd_cd in varchar2,
1068 p_prtl_mo_eff_dt_det_cd in varchar2,
1069 p_rndg_cd in varchar2,
1070 p_val_calc_rl in number,
1071 p_prort_mn_ann_elcn_val_rl in number,
1072 p_prort_mx_ann_elcn_val_rl in number,
1073 p_mn_mx_elcn_rl in number,
1074 p_element_det_rl in number,
1075 p_effective_date in date,
1076 p_business_group_id in number,
1077 p_object_version_number in number) is
1078 --
1082
1079 l_proc varchar2(72) := g_package||'chk_all_rules';
1080 l_api_updating boolean;
1081 l_dummy varchar2(1);
1083 --
1084
1085 cursor c1(p_rule number, p_formula_type_id in number) is
1086 select null
1087 from ff_formulas_f ff
1088 ,per_business_groups pbg
1089 where ff.formula_id = p_rule
1090 and ff.formula_type_id = p_formula_type_id
1091 and pbg.business_group_id = p_business_group_id
1092 and nvl(ff.business_group_id, p_business_group_id) =
1093 p_business_group_id
1094 and nvl(ff.legislation_code, pbg.legislation_code) =
1095 pbg.legislation_code
1096 and p_effective_date
1097 between ff.effective_start_date
1098 and ff.effective_end_date;
1099 --
1100
1101
1102 Begin
1103 --
1104 hr_utility.set_location('Entering:'||l_proc, 5);
1105 l_api_updating := ben_abr_shd.api_updating
1106 (p_acty_base_rt_id => p_acty_base_rt_id,
1107 p_effective_date => p_effective_date,
1108 p_object_version_number => p_object_version_number);
1109 --
1110 if (l_api_updating
1111 and nvl(p_prtl_mo_det_mthd_rl,hr_api.g_number)
1112 <> ben_abr_shd.g_old_rec.prtl_mo_det_mthd_rl
1113 or not l_api_updating)
1114 and p_prtl_mo_det_mthd_rl is not null then
1115 --
1116 -- check if value of formula rule is valid.
1117 --
1118 open c1(p_prtl_mo_det_mthd_rl, -165); -- formula_type_id = ??
1119 --
1120 -- fetch value from cursor if it returns a record then the
1121 -- formula is valid otherwise its invalid
1122 --
1123 fetch c1 into l_dummy;
1124 if c1%notfound then
1125 --
1126 close c1;
1127 --
1128 -- raise error
1129 --
1130 fnd_message.set_name('BEN','BEN_91172_PRTL_MO_DET_MTHD_RL');
1131 fnd_message.raise_error;
1132 --
1133 end if;
1134 --
1135 close c1;
1136 --
1137 --
1138 -- Unless Partial Month Determination Method Code = Rule,
1139 -- Partial Month Determination Method rule must be blank.
1140 if nvl(p_prtl_mo_det_mthd_cd,hr_api.g_varchar2) <> 'RL'
1141 and p_prtl_mo_det_mthd_rl is not null then
1142 --
1143 fnd_message.set_name('BEN', 'BEN_91432_PMD_RL_NOT_NULL');
1144 fnd_message.raise_error;
1145 --
1146 elsif nvl(p_prtl_mo_det_mthd_cd,hr_api.g_varchar2) = 'RL'
1147 and p_prtl_mo_det_mthd_rl is null then
1148 --
1149 fnd_message.set_name('BEN', 'BEN_91434_PMD_RL_NULL');
1150 fnd_message.raise_error;
1151 --
1152 end if;
1153 --
1154 end if;
1155 --
1156 if (l_api_updating
1157 and nvl(p_prtl_mo_eff_dt_det_rl,hr_api.g_number)
1158 <> ben_abr_shd.g_old_rec.prtl_mo_eff_dt_det_rl
1159 or not l_api_updating)
1160 and p_prtl_mo_eff_dt_det_rl is not null then
1161 --
1162 -- check if value of formula rule is valid.
1163 --
1164 open c1(p_prtl_mo_eff_dt_det_rl, -48); -- formula_type_id = -48
1165 --
1166 -- fetch value from cursor if it returns a record then the
1167 -- formula is valid otherwise its invalid
1168 --
1169 fetch c1 into l_dummy;
1170 if c1%notfound then
1171 --
1172 close c1;
1173 --
1174 -- raise error
1175 --
1176 fnd_message.set_name('BEN','BEN_91173_PRL_MO_EFF_DT_DET_RL');
1177 fnd_message.raise_error;
1178 --
1179 end if;
1180 --
1181 close c1;
1182 --
1183 --
1184 -- Unless Partial Month Effective Date Determination Code = Rule,
1185 -- Partial Month Effective Date Determination rule must be blank.
1186 if nvl(p_prtl_mo_eff_dt_det_cd,hr_api.g_varchar2) <> 'RL'
1187 and p_prtl_mo_eff_dt_det_rl is not null then
1188 --
1189 fnd_message.set_name('BEN', 'BEN_91433_PMEDD_RL_NOT_NULL');
1190 fnd_message.raise_error;
1191 --
1192 elsif nvl(p_prtl_mo_eff_dt_det_cd,hr_api.g_varchar2) = 'RL'
1193 and p_prtl_mo_eff_dt_det_rl is null then
1194 --
1195 fnd_message.set_name('BEN', 'BEN_91435_PMEDD_RL_NULL');
1196 fnd_message.raise_error;
1197 --
1198 end if;
1199 --
1200 end if;
1201 --
1202 if (l_api_updating
1203 and nvl(p_rndg_rl,hr_api.g_number)
1204 <> ben_abr_shd.g_old_rec.rndg_rl
1205 or not l_api_updating)
1206 and p_rndg_rl is not null then
1207 --
1208 -- check if value of formula rule is valid.
1209 --
1210 open c1(p_rndg_rl, -169); -- formula_type_id = ??
1211 --
1212 -- fetch value from cursor if it returns a record then the
1213 -- formula is valid otherwise its invalid
1214 --
1215 fetch c1 into l_dummy;
1216 if c1%notfound then
1217 --
1218 close c1;
1219 --
1220 -- raise error
1221 --
1222 fnd_message.set_name('BEN','BEN_91042_INVALID_RNDG_RL');
1223 fnd_message.raise_error;
1224 --
1225 end if;
1226 --
1227 close c1;
1228 --
1229 --
1230 -- Unless Rounding Code = Rule, Rounding rule must be blank.
1231 if nvl(p_rndg_cd,hr_api.g_varchar2) <> 'RL' and p_rndg_rl is not null then
1232 --
1233 fnd_message.set_name('BEN','BEN_91043_RNDG_RL_NOT_NULL');
1234 fnd_message.raise_error;
1235 --
1236 elsif nvl(p_rndg_cd,hr_api.g_varchar2) = 'RL' and p_rndg_rl is null then
1237 --
1241 end if;
1238 fnd_message.set_name('BEN','BEN_92340_RNDG_RL_NULL');
1239 fnd_message.raise_error;
1240 --
1242 --
1243 end if;
1244 --
1245 if (l_api_updating
1246 and nvl(p_lwr_lmt_calc_rl,hr_api.g_number)
1247 <> ben_abr_shd.g_old_rec.lwr_lmt_calc_rl
1248 or not l_api_updating)
1249 and p_lwr_lmt_calc_rl is not null then
1250 --
1251 -- check if value of formula rule is valid.
1252 --
1253 open c1(p_lwr_lmt_calc_rl, -392);
1254 --
1255 -- fetch value from cursor if it returns a record then the
1256 -- formula is valid otherwise its invalid
1257 --
1258 fetch c1 into l_dummy;
1259 if c1%notfound then
1260 --
1261 close c1;
1262 --
1263 -- raise error
1264 --
1265 fnd_message.set_name('BEN','BEN_91815_INVALID_LWR_LMT_RL');
1266 fnd_message.raise_error;
1267 --
1268 end if;
1269 --
1270 close c1;
1271 --
1272 end if;
1273 --
1274 if (l_api_updating
1275 and nvl(p_upr_lmt_calc_rl,hr_api.g_number)
1276 <> ben_abr_shd.g_old_rec.upr_lmt_calc_rl
1277 or not l_api_updating)
1278 and p_upr_lmt_calc_rl is not null then
1279 --
1280 -- check if value of formula rule is valid.
1281 --
1282 open c1(p_upr_lmt_calc_rl, -293);
1283 --
1284 -- fetch value from cursor if it returns a record then the
1285 -- formula is valid otherwise its invalid
1286 --
1287 fetch c1 into l_dummy;
1288 if c1%notfound then
1289 --
1290 close c1;
1291 --
1292 -- raise error
1293 --
1294 fnd_message.set_name('BEN','BEN_91823_INVALID_UPR_LMT_RL');
1295 fnd_message.raise_error;
1296 --
1297 end if;
1298 --
1299 close c1;
1300 --
1301 end if;
1302 --
1303 if (l_api_updating
1304 and nvl(p_val_calc_rl,hr_api.g_number)
1305 <> ben_abr_shd.g_old_rec.val_calc_rl
1306 or not l_api_updating)
1307 and p_val_calc_rl is not null then
1308 --
1309 -- check if value of formula rule is valid.
1310 --
1311 open c1(p_val_calc_rl, -171);
1312 --
1313 -- fetch value from cursor if it returns a record then the
1314 -- formula is valid otherwise its invalid
1315 --
1316 fetch c1 into l_dummy;
1317 if c1%notfound then
1318 --
1319 close c1;
1320 --
1321 -- raise error
1322 --
1323 fnd_message.set_name('BEN','BEN_91174_VAL_CALC_RL');
1324 fnd_message.raise_error;
1325 --
1326 end if;
1327 --
1328 close c1;
1329 --
1330 end if;
1331 --
1332 --
1333 if (l_api_updating
1334 and nvl(p_prort_mn_ann_elcn_val_rl,hr_api.g_number)
1335 <> ben_abr_shd.g_old_rec.prort_mn_ann_elcn_val_rl
1336 or not l_api_updating)
1337 and p_prort_mn_ann_elcn_val_rl is not null then
1338 --
1339 -- check if value of formula rule is valid.
1340 --
1341 open c1(p_prort_mn_ann_elcn_val_rl, -534);
1342 --
1343 -- fetch value from cursor if it returns a record then the
1344 -- formula is valid otherwise its invalid
1345 --
1346 fetch c1 into l_dummy;
1347 if c1%notfound then
1348 --
1349 close c1;
1350 --
1351 -- raise error
1352 --
1353 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1354 fnd_message.set_token('ID',p_prort_mn_ann_elcn_val_rl);
1355 fnd_message.set_token('TYPE_ID',-534);
1356 fnd_message.raise_error;
1357 --
1358 end if;
1359 --
1360 close c1;
1361 --
1362 end if;
1363 --
1364 if (l_api_updating
1365 and nvl(p_prort_mx_ann_elcn_val_rl,hr_api.g_number)
1366 <> ben_abr_shd.g_old_rec.prort_mx_ann_elcn_val_rl
1367 or not l_api_updating)
1368 and p_prort_mx_ann_elcn_val_rl is not null then
1369 --
1370 -- check if value of formula rule is valid.
1371 --
1372 open c1(p_prort_mx_ann_elcn_val_rl, -534);
1373 --
1374 -- fetch value from cursor if it returns a record then the
1375 -- formula is valid otherwise its invalid
1376 --
1377 fetch c1 into l_dummy;
1378 if c1%notfound then
1379 --
1380 close c1;
1381 --
1382 -- raise error
1383 --
1384 fnd_message.set_name('BEN','BEN_91741_FORMULA_NOT_FOUND');
1385 fnd_message.set_token('ID',p_prort_mx_ann_elcn_val_rl);
1386 fnd_message.set_token('TYPE_ID',-534);
1387 fnd_message.raise_error;
1388 --
1389 end if;
1390 --
1391 close c1;
1392 --
1393 end if;
1394
1395 --- Bug 3981982
1396 if (l_api_updating
1397 and nvl(p_mn_mx_elcn_rl,hr_api.g_number)
1398 <> ben_abr_shd.g_old_rec.mn_mx_elcn_rl
1399 or not l_api_updating)
1400 and p_mn_mx_elcn_rl is not null then
1401 --
1402 -- check if value of formula rule is valid.
1403 --
1404 open c1(p_mn_mx_elcn_rl, -551);
1405 --
1406 -- fetch value from cursor if it returns a record then the
1407 -- formula is valid otherwise its invalid
1408 --
1409 fetch c1 into l_dummy;
1410
1411 if c1%notfound then
1412 --
1413 close c1;
1414 --
1415 -- raise error
1416 --
1420 end if;
1417 fnd_message.set_name('BEN','BEN_94127_INVALID_MN_MX_RL');
1418 fnd_message.raise_error;
1419 --
1421 --
1422 close c1;
1423 --
1424 end if;
1425 --
1426 -- cwb multiple currency
1427 if (l_api_updating
1428 and nvl(p_element_det_rl,hr_api.g_number)
1429 <> ben_abr_shd.g_old_rec.element_det_rl
1430 or not l_api_updating)
1431 and p_element_det_rl is not null then
1432 --
1433 -- check if value of formula rule is valid.
1434 --
1435 open c1(p_element_det_rl, -557);
1436 --
1437 -- fetch value from cursor if it returns a record then the
1438 -- formula is valid otherwise its invalid
1439 --
1440 fetch c1 into l_dummy;
1441
1442 if c1%notfound then
1443 --
1444 close c1;
1445 --
1446
1447 fnd_message.set_name('BEN','BEN_91741_FORMULA_NOT_FOUND');
1448 fnd_message.set_token('ID',p_element_det_rl);
1449 fnd_message.set_token('TYPE_ID',-557);
1450 fnd_message.raise_error;
1451
1452 --
1453 end if;
1454 --
1455 close c1;
1456 --
1457 end if;
1458
1459 --
1460 hr_utility.set_location('Leaving:'||l_proc,10);
1461 --
1462 end chk_all_rules;
1463
1464
1465 -- ----------------------------------------------------------------------------
1466 -- |----------------------------< chk_all_flags >-----------------------------|
1467 -- ----------------------------------------------------------------------------
1468 --
1469 -- Description
1470 -- This procedure is used to check that the flag lookup values are valid.
1471 --
1472 -- Pre Conditions
1473 -- None.
1474 --
1475 -- In Parameters
1476 -- acty_base_rt_id PK of record being inserted or updated.
1477 -- use_to_calc_net_flx_cr_flag Value of lookup code.
1478 -- asn_on_enrt_flag Value of lookup code.
1479 -- abv_mx_elcn_val_alwd_flag Value of lookup code.
1480 -- blw_mn_elcn_alwd_flag Value of lookup code.
1481 -- uses_ded_sched_flag Value of lookup code.
1482 -- uses_varbl_rt_flag Value of lookup code.
1483 -- vstg_sched_apls_fLag Value of lookup code.
1484 -- proc_each_pp_dflt_flag Value of lookup code.
1485 -- prdct_flx_cr_when_elig_flag Value of lookup code.
1486 -- no_std_rt_used_flag Value of lookup code.
1487 -- uses_pymt_sched_flag Value of lookup code.
1488 -- val_ovrid_alwd_flag Value of lookup code.
1489 -- no_mx_elcn_val_dfnd_flag Value of lookup code.
1490 -- entr_val_at_enrt_flag Value of lookup code.
1491 -- entr_ann_val_flag Value of lookup code.
1492 -- effective_date effective date
1493 -- object_version_number Object version number of record being
1494 -- inserted or updated.
1495 --
1496 -- Post Success
1497 -- Processing continues
1498 --
1499 -- Post Failure
1500 -- Error handled by procedure
1501 --
1502 -- Access Status
1503 -- Internal table handler use only.
1504 --
1505 Procedure chk_all_flags(p_acty_base_rt_id in number,
1506 p_use_to_calc_net_flx_cr_flag in varchar2,
1507 p_asn_on_enrt_flag in varchar2,
1508 p_abv_mx_elcn_val_alwd_flag in varchar2,
1509 p_blw_mn_elcn_alwd_flag in varchar2,
1510 p_dsply_on_enrt_flag in varchar2,
1511 p_use_calc_acty_bs_rt_flag in varchar2,
1512 p_uses_ded_sched_flag in varchar2,
1513 p_uses_varbl_rt_flag in varchar2,
1514 p_vstg_sched_apls_fLag in varchar2,
1515 p_proc_each_pp_dflt_flag in varchar2,
1516 p_prdct_flx_cr_when_elig_flag in varchar2,
1517 p_no_std_rt_used_flag in varchar2,
1518 p_uses_pymt_sched_flag in varchar2,
1519 p_val_ovrid_alwd_flag in varchar2,
1520 p_no_mx_elcn_val_dfnd_flag in varchar2,
1521 p_no_mn_elcn_val_dfnd_flag in varchar2,
1522 p_entr_val_at_enrt_flag in varchar2,
1523 p_entr_ann_val_flag in varchar2,
1524 p_only_one_bal_typ_alwd_flag in varchar2,
1525 p_rt_usg_cd in varchar2,
1526 p_ele_rqd_flag in varchar2,
1527 p_subj_to_imptd_incm_flag in varchar2,
1528 p_acty_typ_cd in varchar2,
1529 p_business_group_id in number,
1530 p_effective_date in date,
1531 p_object_version_number in number) is
1532 --
1533 l_proc varchar2(72) := g_package||'chk_all_flags';
1534 l_api_updating boolean;
1535 l_dummy char(1);
1536 cursor c1 is select null
1537 from ben_acty_vrbl_rt_f
1538 where acty_base_rt_id = p_acty_base_rt_id
1539 and business_group_id = p_business_group_id
1540 and p_effective_date between effective_start_date
1541 and effective_end_date
1542 UNION select null
1543 from ben_vrbl_rt_rl_f
1544 where acty_base_rt_id = p_acty_base_rt_id
1548 --
1545 and business_group_id = p_business_group_id
1546 and p_effective_date between effective_start_date
1547 and effective_end_date;
1549 --
1550 Begin
1551 --
1552 hr_utility.set_location('Entering:'||l_proc, 5);
1553 --
1554 l_api_updating := ben_abr_shd.api_updating
1555 (p_acty_base_rt_id => p_acty_base_rt_id,
1556 p_effective_date => p_effective_date,
1557 p_object_version_number => p_object_version_number);
1558 --
1559 if (l_api_updating
1560 and p_use_to_calc_net_flx_cr_flag
1561 <> nvl(ben_abr_shd.g_old_rec.use_to_calc_net_flx_cr_flag,hr_api.g_varchar2)
1562 or not l_api_updating)
1563 and p_use_to_calc_net_flx_cr_flag is not null then
1564 --
1565 -- check if value of lookup falls within lookup type.
1566 --
1567 if hr_api.not_exists_in_hr_lookups
1568 (p_lookup_type => 'YES_NO',
1569 p_lookup_code => p_use_to_calc_net_flx_cr_flag,
1570 p_effective_date => p_effective_date) then
1571 --
1572 -- raise error as does not exist as lookup
1573 --
1574 fnd_message.set_name('BEN','BEN_91175_CALC_NET_FLX_CR_FLAG');
1575 fnd_message.raise_error;
1576 --
1577 end if;
1578 --
1579 end if;
1580 --
1581 if (l_api_updating
1582 and p_asn_on_enrt_flag
1583 <> nvl(ben_abr_shd.g_old_rec.asn_on_enrt_flag,hr_api.g_varchar2)
1584 or not l_api_updating)
1585 and p_asn_on_enrt_flag is not null then
1586 --
1587 -- check if value of lookup falls within lookup type.
1588 --
1589 if hr_api.not_exists_in_hr_lookups
1590 (p_lookup_type => 'YES_NO',
1591 p_lookup_code => p_asn_on_enrt_flag,
1592 p_effective_date => p_effective_date) then
1593 --
1594 -- raise error as does not exist as lookup
1595 --
1596 fnd_message.set_name('BEN','BEN_91176_ASN_ON_ENRT_FLAG');
1597 fnd_message.raise_error;
1598 --
1599 end if;
1600 --
1601 end if;
1602 --
1603 --
1604 if (l_api_updating
1605 and p_abv_mx_elcn_val_alwd_flag
1606 <> nvl(ben_abr_shd.g_old_rec.abv_mx_elcn_val_alwd_flag,hr_api.g_varchar2)
1607 or not l_api_updating)
1608 and p_abv_mx_elcn_val_alwd_flag is not null then
1609 --
1610 -- check if value of lookup falls within lookup type.
1611 --
1612 if hr_api.not_exists_in_hr_lookups
1613 (p_lookup_type => 'YES_NO',
1614 p_lookup_code => p_abv_mx_elcn_val_alwd_flag,
1615 p_effective_date => p_effective_date) then
1616 --
1617 -- raise error as does not exist as lookup
1618 --
1619 fnd_message.set_name('BEN','BEN_91177_ABV_MX_EL_ALWD_FLAG');
1620 fnd_message.raise_error;
1621 --
1622 end if;
1623 --
1624 end if;
1625 --
1626 --
1627 if (l_api_updating
1628 and p_blw_mn_elcn_alwd_flag
1629 <> nvl(ben_abr_shd.g_old_rec.blw_mn_elcn_alwd_flag,hr_api.g_varchar2)
1630 or not l_api_updating)
1631 and p_blw_mn_elcn_alwd_flag is not null then
1632 --
1633 -- check if value of lookup falls within lookup type.
1634 --
1635 if hr_api.not_exists_in_hr_lookups
1636 (p_lookup_type => 'YES_NO',
1637 p_lookup_code => p_blw_mn_elcn_alwd_flag,
1638 p_effective_date => p_effective_date) then
1639 --
1640 -- raise error as does not exist as lookup
1641 --
1642 fnd_message.set_name('BEN','BEN_91178_BLW_MN_EL_ALWD_FLAG');
1643 fnd_message.raise_error;
1644 --
1645 end if;
1646 --
1647 end if;
1648 --
1649 if (l_api_updating
1650 and p_dsply_on_enrt_flag
1651 <> nvl(ben_abr_shd.g_old_rec.dsply_on_enrt_flag,hr_api.g_varchar2)
1652 or not l_api_updating)
1653 and p_dsply_on_enrt_flag is not null then
1654 --
1655 -- check if value of lookup falls within lookup type.
1656 --
1657 if hr_api.not_exists_in_hr_lookups
1658 (p_lookup_type => 'YES_NO',
1659 p_lookup_code => p_dsply_on_enrt_flag,
1660 p_effective_date => p_effective_date) then
1661 --
1662 -- raise error as does not exist as lookup
1663 --
1664 fnd_message.set_name('BEN','BEN_91627_DSPLY_ON_ENRT_FLAG');
1665 fnd_message.raise_error;
1666 --
1667 end if;
1668 --
1669 end if;
1670 --
1671 if (l_api_updating
1672 and p_use_calc_acty_bs_rt_flag
1673 <> nvl(ben_abr_shd.g_old_rec.use_calc_acty_bs_rt_flag,hr_api.g_varchar2)
1674 or not l_api_updating)
1675 and p_use_calc_acty_bs_rt_flag is not null then
1676 --
1677 -- check if value of lookup falls within lookup type.
1678 --
1679 if hr_api.not_exists_in_hr_lookups
1680 (p_lookup_type => 'YES_NO',
1681 p_lookup_code => p_use_calc_acty_bs_rt_flag,
1682 p_effective_date => p_effective_date) then
1683 --
1684 -- raise error as does not exist as lookup
1685 --
1686 fnd_message.set_name('BEN','BEN_91179_CAL_ACTY_BS_RT_FLAG');
1687 fnd_message.raise_error;
1688 --
1689 end if;
1690 --
1691 end if;
1692 --
1693 --
1694 --
1695 if (l_api_updating
1696 and p_uses_ded_sched_flag
1697 <> nvl(ben_abr_shd.g_old_rec.uses_ded_sched_flag,hr_api.g_varchar2)
1698 or not l_api_updating)
1699 and p_uses_ded_sched_flag is not null then
1700 --
1701 -- check if value of lookup falls within lookup type.
1702 --
1703 if hr_api.not_exists_in_hr_lookups
1704 (p_lookup_type => 'YES_NO',
1705 p_lookup_code => p_uses_ded_sched_flag,
1706 p_effective_date => p_effective_date) then
1707 --
1708 -- raise error as does not exist as lookup
1709 --
1710 fnd_message.set_name('BEN','BEN_91180_USES_DED_SCHED_FLAG');
1711 fnd_message.raise_error;
1712 --
1713 end if;
1714 --
1715 end if;
1716 --
1717 if (l_api_updating
1718 and p_uses_varbl_rt_flag
1719 <> nvl(ben_abr_shd.g_old_rec.uses_varbl_rt_flag,hr_api.g_varchar2)
1720 or not l_api_updating)
1721 and p_uses_varbl_rt_flag is not null then
1722 --
1723 -- check if value of lookup falls within lookup type.
1724 --
1725 if hr_api.not_exists_in_hr_lookups
1726 (p_lookup_type => 'YES_NO',
1727 p_lookup_code => p_uses_varbl_rt_flag,
1728 p_effective_date => p_effective_date) then
1729 --
1730 -- raise error as does not exist as lookup
1731 --
1732 fnd_message.set_name('BEN','BEN_91181_USES_VARBL_RT_FLAG');
1733 fnd_message.raise_error;
1734 --
1735 end if;
1736 --
1737 -- Uses Variable Rate Falg cannot be updated to "off", when acty variable rate rule or
1738 -- variable rate records exists for flex credit calculation.
1739 --
1740 --
1741 --
1742
1743
1744
1745 if (l_api_updating
1746 and p_uses_varbl_rt_flag
1747 <> nvl(ben_abr_shd.g_old_rec.uses_varbl_rt_flag,hr_api.g_varchar2)
1748 ) and p_uses_varbl_rt_flag is not null and p_acty_base_rt_id is not null
1749 and p_uses_varbl_rt_flag = 'N' --bug 3960628
1750 then
1751 open c1;
1752 fetch c1 into l_dummy;
1753 if c1%found then
1754 close c1;
1755 fnd_message.set_name('BEN','BEN_91430_VRBL_RT_RL_EXISTS');
1756 fnd_message.raise_error;
1757 end if;
1758 close c1;
1759 end if;
1760 --
1761 end if;
1762 --
1763 if (l_api_updating
1764 and p_vstg_sched_apls_fLag
1765 <> nvl(ben_abr_shd.g_old_rec.vstg_sched_apls_fLag,hr_api.g_varchar2)
1766 or not l_api_updating)
1767 and p_vstg_sched_apls_fLag is not null then
1768 --
1769 -- check if value of lookup falls within lookup type.
1770 --
1771 if hr_api.not_exists_in_hr_lookups
1772 (p_lookup_type => 'YES_NO',
1773 p_lookup_code => p_vstg_sched_apls_fLag,
1774 p_effective_date => p_effective_date) then
1775 --
1776 -- raise error as does not exist as lookup
1777 --
1778 fnd_message.set_name('BEN','BEN_91182_VSTG_SCHED_APLS_FLAG');
1779 fnd_message.raise_error;
1780 --
1781 end if;
1782 --
1783 end if;
1784 --
1785 if (l_api_updating
1786 and p_proc_each_pp_dflt_flag
1787 <> nvl(ben_abr_shd.g_old_rec.proc_each_pp_dflt_flag,hr_api.g_varchar2)
1788 or not l_api_updating)
1789 and p_proc_each_pp_dflt_flag is not null then
1790 --
1791 -- check if value of lookup falls within lookup type.
1792 --
1793 if hr_api.not_exists_in_hr_lookups
1794 (p_lookup_type => 'YES_NO',
1795 p_lookup_code => p_proc_each_pp_dflt_flag,
1796 p_effective_date => p_effective_date) then
1797 --
1798 -- raise error as does not exist as lookup
1799 --
1800 fnd_message.set_name('BEN','BEN_91183_PROC_PP_DFLT_FLAG');
1801 fnd_message.raise_error;
1802 --
1803 end if;
1804 --
1805 end if;
1806 --
1807 if (l_api_updating
1808 and p_prdct_flx_cr_when_elig_flag
1809 <> nvl(ben_abr_shd.g_old_rec.prdct_flx_cr_when_elig_flag,hr_api.g_varchar2)
1810 or not l_api_updating)
1811 and p_prdct_flx_cr_when_elig_flag is not null then
1812 --
1813 -- check if value of lookup falls within lookup type.
1814 --
1815 if hr_api.not_exists_in_hr_lookups
1816 (p_lookup_type => 'YES_NO',
1817 p_lookup_code => p_prdct_flx_cr_when_elig_flag,
1818 p_effective_date => p_effective_date) then
1819 --
1820 -- raise error as does not exist as lookup
1821 --
1822 fnd_message.set_name('BEN','BEN_91184_PRDCT_FLX_CR_FLAG');
1823 fnd_message.raise_error;
1824 --
1825 end if;
1826 --
1827 end if;
1828 --
1829 if (l_api_updating
1830 and p_no_std_rt_used_flag
1831 <> nvl(ben_abr_shd.g_old_rec.no_std_rt_used_flag,hr_api.g_varchar2)
1832 or not l_api_updating)
1833 and p_no_std_rt_used_flag is not null then
1834 --
1835 -- check if value of lookup falls within lookup type.
1836 --
1837 if hr_api.not_exists_in_hr_lookups
1838 (p_lookup_type => 'YES_NO',
1839 p_lookup_code => p_no_std_rt_used_flag,
1840 p_effective_date => p_effective_date) then
1841 --
1842 -- raise error as does not exist as lookup
1843 --
1844 fnd_message.set_name('BEN','BEN_91185_NO_STD_RT_US_FLAG');
1845 fnd_message.raise_error;
1846 --
1847 end if;
1848 --
1849 -- If no standard Rate Used Flag is "on", then uses Variable Rate flag
1850 -- must be "on".
1851 If P_NO_STD_RT_USED_FLAG = 'Y' and P_USES_VARBL_RT_FLAG <> 'Y' then
1852 fnd_message.set_name('BEN','BEN_91417_INVLD_STD_VRBL_FLAGS');
1856 end if;
1853 fnd_message.raise_error;
1854 end if;
1855 --
1857 --
1858 if (l_api_updating
1859 and p_uses_pymt_sched_flag
1860 <> nvl(ben_abr_shd.g_old_rec.uses_pymt_sched_flag,hr_api.g_varchar2)
1861 or not l_api_updating)
1862 and p_uses_pymt_sched_flag is not null then
1863 --
1864 -- check if value of lookup falls within lookup type.
1865 --
1866 if hr_api.not_exists_in_hr_lookups
1867 (p_lookup_type => 'YES_NO',
1868 p_lookup_code => p_uses_pymt_sched_flag,
1869 p_effective_date => p_effective_date) then
1870 --
1871 -- raise error as does not exist as lookup
1872 --
1873 fnd_message.set_name('BEN','BEN_91186_USES_PYMT_SCHED_FLAG');
1874 fnd_message.raise_error;
1875 --
1876 end if;
1877 /* --
1878 -- Process each pay period and Uses payment frequecy flags must be
1879 -- mutually exclusive.
1880 if p_proc_each_pp_dflt_flag is not null and (
1881 (p_uses_pymt_sched_flag = 'Y' and p_proc_each_pp_dflt_flag = 'Y' ) or
1882 (p_uses_pymt_sched_flag = 'N' and p_proc_each_pp_dflt_flag = 'N'))
1883 then
1884 --
1885 fnd_message.set_name('BEN','BEN_91412_PROC_PP_PYMT_SCHED_F');
1886 fnd_message.raise_error;
1887 --
1888 end if;*/
1889 end if;
1890 --
1891 if (l_api_updating
1892 and p_val_ovrid_alwd_flag
1893 <> nvl(ben_abr_shd.g_old_rec.val_ovrid_alwd_flag,hr_api.g_varchar2)
1894 or not l_api_updating)
1895 and p_val_ovrid_alwd_flag is not null then
1896 --
1897 -- check if value of lookup falls within lookup type.
1898 --
1899 if hr_api.not_exists_in_hr_lookups
1900 (p_lookup_type => 'YES_NO',
1901 p_lookup_code => p_val_ovrid_alwd_flag,
1902 p_effective_date => p_effective_date) then
1903 --
1904 -- raise error as does not exist as lookup
1905 --
1906 fnd_message.set_name('BEN','BEN_91187_VAL_OVRID_ALWD_FLAG');
1907 fnd_message.raise_error;
1908 --
1909 end if;
1910 --
1911 end if;
1912 --
1913 if (l_api_updating
1914 and p_no_mx_elcn_val_dfnd_flag
1915 <> nvl(ben_abr_shd.g_old_rec.no_mx_elcn_val_dfnd_flag,hr_api.g_varchar2)
1916 or not l_api_updating)
1917 and p_no_mx_elcn_val_dfnd_flag is not null then
1918 --
1919 -- check if value of lookup falls within lookup type.
1920 --
1921 if hr_api.not_exists_in_hr_lookups
1922 (p_lookup_type => 'YES_NO',
1923 p_lookup_code => p_no_mx_elcn_val_dfnd_flag,
1924 p_effective_date => p_effective_date) then
1925 --
1926 -- raise error as does not exist as lookup
1927 --
1928 fnd_message.set_name('BEN','BEN_91188_NO_MX_ELCN_VAL_FLAG');
1929 fnd_message.raise_error;
1930 --
1931 end if;
1932 --
1933 end if;
1934 --
1935 if (l_api_updating
1936 and p_entr_val_at_enrt_flag
1937 <> nvl(ben_abr_shd.g_old_rec.entr_val_at_enrt_flag,hr_api.g_varchar2)
1938 or not l_api_updating)
1939 and p_entr_val_at_enrt_flag is not null then
1940 --
1941 -- check if value of lookup falls within lookup type.
1942 --
1943 if hr_api.not_exists_in_hr_lookups
1944 (p_lookup_type => 'YES_NO',
1945 p_lookup_code => p_entr_val_at_enrt_flag,
1946 p_effective_date => p_effective_date) then
1947 --
1948 -- raise error as does not exist as lookup
1949 --
1950 fnd_message.set_name('BEN','BEN_91189_ENT_VAL_AT_ENRT_FLAG');
1951 fnd_message.raise_error;
1952 --
1953 end if;
1954 --
1955 end if;
1956 --
1957 if (l_api_updating
1958 and p_entr_ann_val_flag
1959 <> nvl(ben_abr_shd.g_old_rec.entr_ann_val_flag,hr_api.g_varchar2)
1960 or not l_api_updating)
1961 and p_entr_ann_val_flag is not null then
1962 --
1963 -- check if value of lookup falls within lookup type.
1964 --
1965 if hr_api.not_exists_in_hr_lookups
1966 (p_lookup_type => 'YES_NO',
1967 p_lookup_code => p_entr_ann_val_flag,
1968 p_effective_date => p_effective_date) then
1969 --
1970 -- raise error as does not exist as lookup
1971 --
1972 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1973 fnd_message.set_token('FIELD','p_entr_ann_val_flag');
1974 fnd_message.set_token('VALUE', p_entr_ann_val_flag);
1975 fnd_message.set_token('TYPE','YES_NO');
1976 fnd_message.raise_error;
1977 --
1978 end if;
1979 --
1980 end if;
1981
1982 -- if the enter annual val flag is on, the enter val at enrollment flag must
1983 -- be on. If either is on, display-on-enrollment and assign-on-enrollment
1984 -- flags must be on.
1985 if p_entr_ann_val_flag = 'Y' and p_entr_val_at_enrt_flag = 'N' then
1986 fnd_message.set_name('BEN','BEN_92416_ENTR_FLAGS');
1987 fnd_message.raise_error;
1988 end if;
1989 --Bug 2172033 error this only if p_acty_typ_cd is not like CWB%
1990 --
1991 if substr(p_acty_typ_cd,1,3) <> 'CWB' then
1992 --
1993 if p_entr_val_at_enrt_flag = 'Y'
1994 and (p_dsply_on_enrt_flag = 'N' or p_asn_on_enrt_flag = 'N') then
1995 fnd_message.set_name('BEN','BEN_92417_ENTR_FLAGS2');
1996 fnd_message.raise_error;
1997 end if;
1998 --
1999 end if;
2000 --
2001 if p_acty_typ_cd = 'SSDSPLY' and p_dsply_on_enrt_flag = 'N' then
2002 fnd_message.set_name ('BEN','BEN_92629_ENTR_FLAGS3');
2003 fnd_message.raise_error;
2004 end if;
2005
2006 if (l_api_updating
2007 and p_no_mn_elcn_val_dfnd_flag
2008 <> nvl(ben_abr_shd.g_old_rec.no_mn_elcn_val_dfnd_flag,hr_api.g_varchar2)
2009 or not l_api_updating)
2010 and p_no_mn_elcn_val_dfnd_flag is not null then
2011 --
2012 -- check if value of lookup falls within lookup type.
2013 --
2014 if hr_api.not_exists_in_hr_lookups
2015 (p_lookup_type => 'YES_NO',
2016 p_lookup_code => p_no_mn_elcn_val_dfnd_flag,
2017 p_effective_date => p_effective_date) then
2018 --
2019 -- raise error as does not exist as lookup
2020 --
2021 fnd_message.set_name('BEN','BEN_91190_NO_MN_EL_DFND_FLAG');
2022 fnd_message.raise_error; --
2023 end if;
2024 --
2025 end if;
2026 --
2027 if (l_api_updating
2028 and p_only_one_bal_typ_alwd_flag
2029 <> nvl(ben_abr_shd.g_old_rec.only_one_bal_typ_alwd_flag,hr_api.g_varchar2)
2030 or not l_api_updating)
2031 and p_only_one_bal_typ_alwd_flag is not null then
2032 --
2033 -- check if value of lookup falls within lookup type.
2034 --
2035 if hr_api.not_exists_in_hr_lookups
2036 (p_lookup_type => 'YES_NO',
2037 p_lookup_code => p_only_one_bal_typ_alwd_flag,
2038 p_effective_date => p_effective_date) then
2039 --
2040 -- raise error as does not exist as lookup
2041 --
2042 fnd_message.set_name('BEN','BEN_91410_ONE_BAL_TYPE_ALWD');
2043 fnd_message.raise_error;
2044 --
2045 end if;
2046 --
2047 end if;
2048 --
2049 if (l_api_updating
2050 and p_ele_rqd_flag
2051 <> nvl(ben_abr_shd.g_old_rec.ele_rqd_flag,hr_api.g_varchar2)
2052 or not l_api_updating)
2053 and p_ele_rqd_flag is not null then
2054 --
2055 -- check if value of lookup falls within lookup type.
2056 --
2057 if hr_api.not_exists_in_hr_lookups
2058 (p_lookup_type => 'YES_NO',
2059 p_lookup_code => p_ele_rqd_flag,
2060 p_effective_date => p_effective_date) then
2061 --
2062 -- raise error as does not exist as lookup
2063 --
2064 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2065 fnd_message.set_token('FIELD', 'p_ele_rqd_flag');
2066 fnd_message.set_token('VALUE', p_ele_rqd_flag);
2067 fnd_message.set_token('TYPE', 'YES_NO');
2068 fnd_message.raise_error;
2069 --
2070 end if;
2071 --
2072 end if;
2073 --
2074 if (l_api_updating
2075 and p_subj_to_imptd_incm_flag
2076 <> nvl(ben_abr_shd.g_old_rec.subj_to_imptd_incm_flag,hr_api.g_varchar2)
2077 or not l_api_updating)
2078 and p_subj_to_imptd_incm_flag is not null then
2079 --
2080 -- check if value of lookup falls within lookup type.
2081 --
2082 if hr_api.not_exists_in_hr_lookups
2083 (p_lookup_type => 'YES_NO',
2084 p_lookup_code => p_subj_to_imptd_incm_flag,
2085 p_effective_date => p_effective_date) then
2086 --
2087 -- raise error as does not exist as lookup
2088 --
2089 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2090 fnd_message.set_token('FIELD', 'p_subj_to_imptd_incm_flag');
2091 fnd_message.set_token('VALUE', p_subj_to_imptd_incm_flag);
2092 fnd_message.set_token('TYPE', 'YES_NO');
2093 fnd_message.raise_error;
2094 --
2095 end if;
2096 --
2097 end if;
2098 --
2099 -- When Activity rates are for Flex Credits Calculations.
2100 -- Set Assign on Enrollment Flag to "on"
2101 -- Set Enter value at Enrollment Flag to 'off"
2102 -- Set Predict Flex Credits when eligible flag to "on"
2103 -- Check for values rather than setting them is desirable ???
2104 hr_utility.set_location('Leaving:'||l_proc,10);
2105 --
2106 end chk_all_flags;
2107 --
2108 -- ----------------------------------------------------------------------------
2109 -- |----------------------< chk_estonly_no_ptd_fctr >-------------------------|
2110 -- ----------------------------------------------------------------------------
2111 --
2112 -- Description
2113 -- This procedure ensures that the period to date comp level factor is null
2114 -- when the "Year to date Contribution Code" is "Estimate Only"
2115 --
2116 -- Pre Conditions
2117 -- None.
2118 --
2119 -- In Parameters
2120 -- ptd_comp_lvl_fctr_id Period to Date Comp. Level Factor.
2121 -- det_pl_ytd_cntrs_cd Plan Year-To-Date Contribution Code.
2122 --
2123 -- Post Success
2124 -- Processing continues
2125 --
2126 -- Post Failure
2127 -- Error handled by procedure
2128 --
2129 -- Access Status
2130 -- Internal table handler use only.
2131 --
2132 Procedure chk_estonly_no_ptd_fctr(p_ptd_comp_lvl_fctr_id in number,
2133 p_det_pl_ytd_cntrs_cd in varchar2) is
2134 --
2135 l_proc varchar2(72) := g_package||'chk_estonly_no_ptd_fctr';
2136 --
2137 Begin
2138 --
2139 hr_utility.set_location('Entering:'||l_proc, 5);
2140 --
2141 -- Period to Date Comp. Level Factor and the value of "Estimate Only"
2142 -- for Plan Year-To-Date Contribution Code are mutually exclusive.
2143 --
2144 if p_det_pl_ytd_cntrs_cd = 'ESTONLY' and
2145 p_ptd_comp_lvl_fctr_id is not null then
2146 --
2147 fnd_message.set_name('BEN','BEN_92515_ESTONLY_NO_PTD_FCTR');
2148 fnd_message.raise_error;
2149 --
2150 end if;
2151 --
2152 end chk_estonly_no_ptd_fctr;
2153 --
2154 ----------------------------------------------------------------------------
2155 -- |----------------------< chk_acty_type >-------------------------|
2156 -- ----------------------------------------------------------------------------
2157 --
2158 -- Description
2159 -- This procedure ensures that if activity type begins with 'PRD' then
2160 -- the plan must have 'allows reimbursment' on and the plan can have
2161 -- only one of these rates and element type is non recurring
2162 --
2163 -- Pre Conditions
2164 -- None.
2165 --
2166 -- In Parameters
2167 -- acty_base_rt_id Activity Base Rate Id
2168 -- acty_type_cd Activity Type Code
2169 -- pl_id Plan Id
2170 -- effective_start_date
2171 -- business_group_id
2172 --
2173 -- Post Success
2174 -- Processing continues
2175 --
2176 -- Post Failure
2177 -- Error handled by procedure
2178 --
2179 -- Access Status
2180 -- Internal table handler use only.
2181 --
2182 Procedure chk_acty_type(p_acty_base_rt_id in number,
2183 p_acty_typ_cd in varchar2,
2184 p_sub_acty_typ_cd in varchar2,
2185 p_pl_id in number,
2186 p_effective_start_date in date,
2187 p_business_group_id in number,
2188 p_plip_id in number ) is
2189 --
2190 l_dummy char(1);
2191 cursor c1 is select null
2192 from ben_acty_base_rt_f
2193 Where acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
2194 and acty_typ_cd = p_acty_typ_cd
2195 and pl_id = p_pl_id
2196 and p_effective_start_date between effective_start_date and effective_end_date
2197 and business_group_id = p_business_group_id;
2198 --
2199 cursor c_pln is select alws_reimbmts_flag
2200 from ben_pl_f
2201 where pl_id = p_pl_id
2202 and p_effective_start_date between effective_start_date and effective_end_date
2203 and business_group_id = p_business_group_id;
2204
2205
2206 cursor c_plip is select pl.alws_reimbmts_flag
2207 from ben_pl_f pl ,
2208 ben_plip_f plip
2209 where plip.plip_id = p_plip_id
2210 and p_effective_start_date between plip.effective_start_date and plip.effective_end_date
2211 and plip.business_group_id = p_business_group_id
2212 and pl.pl_id = plip.pl_id
2213 and p_effective_start_date between pl.effective_start_date and pl.effective_end_date
2214 and pl.business_group_id = p_business_group_id;
2215
2216 --
2217 l_alws_reimbmts_flag varchar2(1);
2218 l_proc varchar2(72) := g_package||'chk_acty_type';
2219 --
2220 Begin
2221 --
2222 hr_utility.set_location('Entering:'||l_proc, 5);
2223 --
2224 if substr(p_acty_typ_cd,1,3) = 'PRD' then
2225 --
2226 open c1;
2227 fetch c1 into l_dummy;
2228 if c1%FOUND then
2229 close c1;
2230 fnd_message.set_name('BEN','BEN_92656_ONE_RT_REIMBMTS');
2231 fnd_message.raise_error;
2232 end if;
2233 close c1;
2234 --
2235 if p_pl_id is not null then
2236 open c_pln;
2237 fetch c_pln into l_alws_reimbmts_flag;
2238 close c_pln;
2239 elsif p_plip_id is not null then
2240 open c_plip;
2241 fetch c_plip into l_alws_reimbmts_flag;
2242 close c_plip;
2243 end if ;
2244 --
2245 if nvl(l_alws_reimbmts_flag,'N') <> 'Y' then
2246 fnd_message.set_name('BEN','BEN_92655_ALWS_REIMBMTS_FLAG');
2247 fnd_message.raise_error;
2248 end if;
2249 --
2250 end if;
2251 /*
2252 -- Commented : Bug 3570935
2253 --when the acty type cd in ( 'CWBWS' , CWBAHE) then the sub_acty_typ_cd should have value
2254 if p_acty_typ_cd in ( 'CWBWS', 'CWBAHE') then
2255 if p_sub_acty_typ_cd is null then
2256 fnd_message.set_name('BEN','BEN_93738_CWB_SACTY_TYP_ERR');
2257 fnd_message.raise_error;
2258 end if ;
2259 end if ;
2260 */
2261
2262 -- check if value of lookup falls within lookup type.
2263 --
2264 if p_sub_acty_typ_cd is not null then
2265 --
2266 /*
2267 -- Commented : Bug 3570935
2268 if p_acty_typ_cd not in ( 'CWBWS' , 'CWBAHE') then
2269 fnd_message.set_name('BEN','BEN_93739_CWB_SACTY_NOT_NULL');
2270 fnd_message.raise_error;
2271 end if ;
2272 */
2273 if hr_api.not_exists_in_hr_lookups
2274 (p_lookup_type => 'BEN_SUB_ACTY_TYP',
2275 p_lookup_code => p_sub_acty_typ_cd ,
2276 p_effective_date => p_effective_start_date) then
2277 --
2278 -- raise error as does not exist as lookup
2279 --
2280 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2281 fnd_message.set_token('FIELD', 'sub_acty_typ_cd');
2282 fnd_message.set_token('VALUE', p_sub_acty_typ_cd);
2283 fnd_message.set_token('TYPE', 'BEN_SUB_ACTY_TYP');
2284 fnd_message.raise_error;
2285 --
2286 end if ;
2287 end if ;
2288
2289 hr_utility.set_location('Leaving:'||l_proc, 5);
2290 --
2291 End chk_acty_type;
2292
2293 -- ----------------------------------------------------------------------------
2294 -- |---------------------< chk_lwr_lmt_val_and_rl >---------------------------|
2295 -- ----------------------------------------------------------------------------
2296 --
2297 -- Description
2298 -- This procedure is used to check that lwr_lmt_val and lwr_lmt_calc_rl
2299 -- are mutually exclusive.
2300 --
2301 -- Pre Conditions
2302 -- None.
2303 --
2304 -- In Parameters
2305 -- acty_base_rt_id PK of record being inserted or updated.
2306 -- lwr_lmt_val Lower Limit Value.
2307 -- lwr_lmt_calc_rl Lower Limit Value Rule.
2308 --
2309 -- Post Success
2310 -- Processing continues
2311 --
2312 -- Post Failure
2313 -- Error handled by procedure
2314 --
2315 -- Access Status
2316 -- Internal table handler use only.
2317 --
2318 Procedure chk_lwr_lmt_val_and_rl(p_acty_base_rt_id in number,
2319 p_lwr_lmt_val in number,
2320 p_lwr_lmt_calc_rl in number) is
2321 --
2322 l_proc varchar2(72) := g_package||'chk_lwr_lmt_val_and_rl';
2323 --
2324 Begin
2325 --
2326 hr_utility.set_location('Entering:'||l_proc, 5);
2327 --
2328 -- Lower Limit Value and Lower Limit Value Rule fields must be
2329 -- mutually exclusive.
2330 if (p_lwr_lmt_val is not null and p_lwr_lmt_calc_rl is not null)
2331 then
2332 --
2333 fnd_message.set_name('BEN','BEN_91859_LWR_LMT_VAL_AND_RL');
2334 fnd_message.raise_error;
2335 --
2336 end if;
2337 end chk_lwr_lmt_val_and_rl;
2338 --
2339 -- ----------------------------------------------------------------------------
2340 -- |---------------------< chk_upr_lwr_lmt_val >-------------------------------|
2341 -- ----------------------------------------------------------------------------
2342 --
2343 -- Description
2344 -- This procedure is used to check that if upr_lmt_val is not null then it
2345 -- should be greater to lwr_lmt_val
2346 --
2347 -- Pre Conditions
2348 -- None.
2349 --
2350 -- In Parameters
2351 -- upr_lmt_val Upper Limit Value.
2352 -- lwr_lmt_val Lower Limit Value Rule.
2353 --
2354 -- Post Success
2355 -- Processing continues
2356 --
2357 -- Post Failure
2358 -- Error handled by procedure
2359 --
2360 -- Access Status
2361 -- Internal table handler use only.
2362 --
2363 --
2364 Procedure chk_upr_lwr_lmt_val( p_upr_lmt_val in number,
2365 p_lwr_lmt_val in number) is
2366 --
2367 l_proc varchar2(72) := g_package||'chk_upr_lwr_lmt_val';
2368 --
2369 Begin
2370 --
2371 hr_utility.set_location('Entering:'||l_proc, 5);
2372 --
2373 -- Upper Limit Value should not be less than Lower Limit Value
2374 -- mutually exclusive.
2375 if (p_upr_lmt_val is not null and p_lwr_lmt_val is not null) and
2376 (p_upr_lmt_val < p_lwr_lmt_val)
2377 then
2378 --
2379 fnd_message.set_name('BEN','BEN_92505_HIGH_LOW_LMT_VAL');
2380 fnd_message.raise_error;
2381 null;
2382 --
2383 end if;
2384 end chk_upr_lwr_lmt_val;
2385 --
2386
2387
2388 --
2389 -- Bug 3460673
2390 -- ----------------------------------------------------------------------------
2391 -- |---------------------< chk_incr_val_less_than_max_val >-------------------------------|
2392 -- ----------------------------------------------------------------------------
2393 --
2394 -- Description
2395 -- This procedure is used to check that if INCRMT_ELCN_VAL should be less
2396 -- less than MX_ELCN_VAL.
2397 --
2398 --
2399 -- Pre Conditions
2400 -- None.
2401 --
2402 -- In Parameters
2403 -- incrmt_elcn_val Increment Value.
2404 -- mx_elcn_val Max Value.
2405 -- ann_mx_elcn_val Annual Max Value.
2406 --
2407 -- Post Success
2408 -- Processing continues
2409 --
2410 -- Post Failure
2411 -- Error handled by procedure
2412 --
2413 -- Access Status
2414 -- Internal table handler use only.
2415 --
2416 --
2417 Procedure chk_incr_val_less_than_max_val (p_incrmt_elcn_val in number,
2418 p_mx_elcn_val in number,
2419 p_ann_mx_elcn_val in number) is
2420 --
2421 l_proc varchar2(72) := g_package||'chk_incr_val_less_than_max_val';
2422 --
2423 Begin
2424 --
2425 hr_utility.set_location('Entering:'||l_proc, 5);
2426 --
2427 -- Increment Value should not be greater than Max Value
2428 -- bug 3947162 --absolute value check --
2429 if (p_incrmt_elcn_val is not null and p_mx_elcn_val is not null) and
2430 (p_incrmt_elcn_val > abs(p_mx_elcn_val))
2431 then
2432 --
2433 fnd_message.set_name('BEN','BEN_93881_INCR_GRTR_TH_MAX_ERR');
2434 fnd_message.set_token('MAX_FIELD','Max');
2435 fnd_message.raise_error;
2436 null;
2437 --
2438 end if;
2439 --
2440 --
2441 -- Increment Value should not be greater than Annual Max Value
2442 -- bug 3947162 --absolute value check --
2443 if (p_incrmt_elcn_val is not null and p_ann_mx_elcn_val is not null) and
2444 (p_incrmt_elcn_val > abs(p_ann_mx_elcn_val ))
2445 then
2446 --
2447 fnd_message.set_name('BEN','BEN_93881_INCR_GRTR_TH_MAX_ERR');
2448 fnd_message.set_token('MAX_FIELD','Annual Max');
2449 fnd_message.raise_error;
2450 null;
2451 --
2452 end if;
2453 --
2454 hr_utility.set_location('Leaving:'||l_proc, 10);
2455 --
2456 end chk_incr_val_less_than_max_val;
2457 --Bug 3460673
2458 --
2459
2460 --
2461 -- ----------------------------------------------------------------------------
2462 -- |---------------------< chk_upr_lmt_val_and_rl >---------------------------|
2463 -- ----------------------------------------------------------------------------
2464 --
2465 -- Description
2466 -- This procedure is used to check that upr_lmt_val and upr_lmt_calc_rl
2467 -- are mutually exclusive.
2468 --
2469 -- Pre Conditions
2470 -- None.
2471 --
2472 -- In Parameters
2473 -- acty_base_rt_id PK of record being inserted or updated.
2474 -- upr_lmt_val Upper Limit Value.
2475 -- upr_lmt_calc_rl Upper Limit Value Rule.
2476 --
2477 -- Post Success
2478 -- Processing continues
2479 --
2480 -- Post Failure
2481 -- Error handled by procedure
2482 --
2483 -- Access Status
2484 -- Internal table handler use only.
2485 --
2486 Procedure chk_upr_lmt_val_and_rl(p_acty_base_rt_id in number,
2487 p_upr_lmt_val in number,
2488 p_upr_lmt_calc_rl in number) is
2489 --
2490 l_proc varchar2(72) := g_package||'chk_upr_lmt_val_and_rl';
2491 --
2492 Begin
2493 --
2494 hr_utility.set_location('Entering:'||l_proc, 5);
2495 --
2496 -- Upper Limit Value and Upper Limit Value Rule fields must be
2497 -- mutually exclusive.
2498 if (p_upr_lmt_val is not null and p_upr_lmt_calc_rl is not null)
2499 then
2500 --
2501 fnd_message.set_name('BEN','BEN_91860_UPR_LMT_VAL_AND_RL');
2502 fnd_message.raise_error;
2503 --
2504 end if;
2505 end chk_upr_lmt_val_and_rl;
2506 --
2507 --
2508 -- ----------------------------------------------------------------------------
2509 -- |---------------------< chk_abr_seq_num >---------------------------|
2510 -- ----------------------------------------------------------------------------
2511 --
2512 -- Description
2513 -- This procedure is used to check that abr_seq_num is specified only
2514 -- for the plan types with option usage as 'ABSENCES'
2515 --
2516 -- Pre Conditions
2517 -- None.
2518 --
2519 -- In Parameters
2520 -- pl_id
2521 -- oipl_id
2522 -- opt_id
2523 -- p_abr_seq_num
2524 --
2525 -- Post Success
2526 -- Processing continues
2527 --
2528 -- Post Failure
2529 -- Error handled by procedure
2530 --
2531 -- Access Status
2532 -- Internal table handler use only.
2533 --
2534 Procedure chk_abr_seq_num(p_pl_id in number,
2535 p_oipl_id in number,
2536 p_opt_id in number,
2537 p_abr_seq_num in number,
2538 p_effective_start_date in date,
2539 p_business_group_id in number) is
2540 --
2541 l_proc varchar2(72) := g_package||'chk_abr_seq_num';
2542 l_pl_id ben_pl_f.pl_id%type;
2543 l_dummy char(1);
2544 --
2545 cursor get_plan_id is
2546 select pl_id
2547 from ben_oipl_f
2548 where oipl_id = p_oipl_id
2549 and business_group_id = p_business_group_id
2550 and p_effective_start_date between effective_start_date and effective_end_date;
2551 --
2552 cursor chk_opt_usg_pltyp is
2553 select null
2554 from BEN_PL_TYP_F
2555 where PL_TYP_ID in (select pl_typ_id
2556 from BEN_PL_TYP_OPT_TYP_F
2557 where opt_id = p_opt_id
2558 and business_group_id = p_business_group_id
2559 and p_effective_start_date between effective_start_date and effective_end_date)
2560 and OPT_TYP_CD = 'ABS'
2561 and p_effective_start_date between effective_start_date and effective_end_date
2562 and business_group_id = p_business_group_id;
2563 --
2564 cursor chk_opt_usg_pl(p_pl_id number) is
2565 select null
2566 from BEN_PL_TYP_F
2567 where PL_TYP_ID = (select pl_typ_id from ben_pl_f
2568 where pl_id = p_pl_id
2569 and business_group_id = p_business_group_id
2570 and p_effective_start_date between effective_start_date and effective_end_date)
2571 and OPT_TYP_CD = 'ABS'
2572 and p_effective_start_date between effective_start_date and effective_end_date
2573 and business_group_id = p_business_group_id;
2574 Begin
2575 --
2576 hr_utility.set_location('Entering:'||l_proc, 5);
2577 --
2578 if p_abr_seq_num is not null then
2579 --
2580 if p_opt_id is not null then
2581 open chk_opt_usg_pltyp;
2582 fetch chk_opt_usg_pltyp into l_dummy;
2583 if chk_opt_usg_pltyp%NOTFOUND then
2584 close chk_opt_usg_pltyp;
2585 fnd_message.set_name('BEN','BEN_ABR_SEQ_NUM_NOT_ALLOWED');
2586 fnd_message.raise_error;
2587 end if;
2588 close chk_opt_usg_pltyp;
2589 --
2590 else
2591 --
2592 if p_oipl_id is not null then
2593 open get_plan_id;
2594 fetch get_plan_id into l_pl_id;
2595 close get_plan_id;
2596 else -- pl_id is not null
2597 l_pl_id := p_pl_id;
2598 end if;
2599 --
2600 open chk_opt_usg_pl(l_pl_id);
2601 fetch chk_opt_usg_pl into l_dummy;
2602 if chk_opt_usg_pl%NOTFOUND then
2603 close chk_opt_usg_pl;
2604 fnd_message.set_name('BEN','BEN_ABR_SEQ_NUM_NOT_ALLOWED');
2605 fnd_message.raise_error;
2606 end if;
2607 close chk_opt_usg_pl;
2608 --
2609 end if;
2610 --
2611 end if;
2612 --
2613 end chk_abr_seq_num;
2614 --
2615 -- ----------------------------------------------------------------------------
2616 -- |----------------------------< chk_all_lookups >---------------------------|
2617 -- ----------------------------------------------------------------------------
2618 --
2619 -- Description
2620 -- This procedure is used to check that the lookup values are valid.
2621 --
2622 -- Pre Conditions
2623 -- None.
2624 --
2625 -- In Parameters
2626 -- acty_base_rt_id PK of record being inserted or updated.
2627 -- acty_typ_cd Value of lookup code.
2628 -- rt_typ_cd Value of lookup code.
2629 -- bnft_rt_typ_cd Value of lookup code.
2630 -- tx_typ_cd Value of lookup code.
2631 -- parnt_chld_cd Value of lookup code.
2632 -- rt_mlt_cd Value of lookup code.
2633 -- rcrrg_cd Value of lookup code.
2634 -- rndg_cd Value of lookup code.
2635 -- prtl_mo_det_mthd_cd Value of lookup code.
2636 -- acty_base_rt_stat_cd Value of lookup code.
2637 -- procg_src_cd Value of lookup code.
2638 -- frgn_erg_ded_typ_cd Value of lookup code.
2639 -- prtl_mo_eff_dt_det_cd Value of lookup code.
2640 -- effective_date effective date
2641 -- object_version_number Object version number of record being
2642 -- inserted or updated.
2643 --
2644 -- Post Success
2645 -- Processing continues
2646 --
2647 -- Post Failure
2648 -- Error handled by procedure
2649 --
2650 -- Access Status
2651 -- Internal table handler use only.
2652 --
2653 Procedure chk_all_lookups(p_acty_base_rt_id in number,
2654 p_acty_typ_cd in varchar2,
2655 p_rt_typ_cd in varchar2,
2656 p_bnft_rt_typ_cd in varchar2,
2657 p_tx_typ_cd in varchar2,
2658 p_parnt_chld_cd in varchar2,
2659 p_rt_mlt_cd in varchar2,
2660 p_rcrrg_cd in varchar2,
2661 p_rndg_cd in varchar2,
2662 p_prtl_mo_det_mthd_cd in varchar2,
2663 p_acty_base_rt_stat_cd in varchar2,
2664 p_procg_src_cd in varchar2,
2665 p_frgn_erg_ded_typ_cd in varchar2,
2666 p_prtl_mo_eff_dt_det_cd in varchar2,
2667 p_rt_usg_cd in varchar2,
2668 p_prort_mn_ann_elcn_val_cd in varchar2,
2669 p_prort_mx_ann_elcn_val_cd in varchar2,
2670 p_one_ann_pymt_cd in varchar2,
2671 p_det_pl_ytd_cntrs_cd in varchar2,
2672 p_asmt_to_use_cd in varchar2,
2673 p_currency_det_cd in varchar2,
2674 p_effective_date in date,
2675 p_object_version_number in number) is
2676 --
2677 l_proc varchar2(72) := g_package||'chk_all_lookups';
2678 l_api_updating boolean;
2679 --
2680 Begin
2681 --
2682 hr_utility.set_location('Entering:'||l_proc, 5);
2683 --
2684 l_api_updating := ben_abr_shd.api_updating
2685 (p_acty_base_rt_id => p_acty_base_rt_id,
2686 p_effective_date => p_effective_date,
2687 p_object_version_number => p_object_version_number);
2688 --
2689 if (l_api_updating
2690 and p_rt_usg_cd
2691 <> nvl(ben_abr_shd.g_old_rec.rt_usg_cd,hr_api.g_varchar2)
2692 or not l_api_updating)
2693 and p_rt_usg_cd is not null then
2694 --
2695 -- check if value of lookup falls within lookup type.
2696 --
2697 if hr_api.not_exists_in_hr_lookups
2698 (p_lookup_type => 'BEN_RT_USG',
2699 p_lookup_code => p_rt_usg_cd,
2700 p_effective_date => p_effective_date) then
2701 --
2702 -- raise error as does not exist as lookup
2703 --
2704 fnd_message.set_name('BEN','BEN_91431_INVLD_RT_USG_CD');
2705 fnd_message.raise_error;
2706 --
2707 end if;
2708 --
2709 end if;
2710 --
2711 if (l_api_updating
2712 and p_prort_mn_ann_elcn_val_cd
2713 <> nvl(ben_abr_shd.g_old_rec.prort_mn_ann_elcn_val_cd,hr_api.g_varchar2)
2714 or not l_api_updating)
2715 and p_prort_mn_ann_elcn_val_cd is not null then
2716 --
2717 -- check if value of lookup falls within lookup type.
2718 --
2719 if hr_api.not_exists_in_hr_lookups
2720 (p_lookup_type => 'BEN_PRORT_ANN_ELCN_VAL',
2721 p_lookup_code => p_prort_mn_ann_elcn_val_cd,
2722 p_effective_date => p_effective_date) then
2723 --
2724 -- raise error as does not exist as lookup
2725 --
2726 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2727 fnd_message.set_token('FIELD','p_prort_mn_ann_elcn_val_cd');
2728 fnd_message.set_token('VALUE', p_prort_mn_ann_elcn_val_cd);
2729 fnd_message.set_token('TYPE','BEN_PRORT_ANN_ELCN_VAL');
2730 fnd_message.raise_error;
2731 --
2732 end if;
2733 --
2734 end if;
2735 --
2736 if (l_api_updating
2737 and p_prort_mx_ann_elcn_val_cd
2738 <> nvl(ben_abr_shd.g_old_rec.prort_mx_ann_elcn_val_cd,hr_api.g_varchar2)
2739 or not l_api_updating)
2740 and p_prort_mx_ann_elcn_val_cd is not null then
2741 --
2742 -- check if value of lookup falls within lookup type.
2743 --
2744 if hr_api.not_exists_in_hr_lookups
2745 (p_lookup_type => 'BEN_PRORT_ANN_ELCN_VAL',
2746 p_lookup_code => p_prort_mx_ann_elcn_val_cd,
2747 p_effective_date => p_effective_date) then
2748 --
2749 -- raise error as does not exist as lookup
2750 --
2751 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2752 fnd_message.set_token('FIELD','p_prort_mx_ann_elcn_val_cd');
2753 fnd_message.set_token('VALUE', p_prort_mx_ann_elcn_val_cd);
2754 fnd_message.set_token('TYPE','BEN_PRORT_ANN_ELCN_VAL');
2755 fnd_message.raise_error;
2756 --
2757 end if;
2758 --
2759 end if;
2760 --
2761 if (l_api_updating
2762 and p_one_ann_pymt_cd
2763 <> nvl(ben_abr_shd.g_old_rec.one_ann_pymt_cd,hr_api.g_varchar2)
2764 or not l_api_updating)
2765 and p_one_ann_pymt_cd is not null then
2766 --
2767 -- check if value of lookup falls within lookup type.
2768 --
2769 if hr_api.not_exists_in_hr_lookups
2770 (p_lookup_type => 'BEN_ONE_ANN_PYMT',
2771 p_lookup_code => p_one_ann_pymt_cd,
2772 p_effective_date => p_effective_date) then
2773 --
2774 -- raise error as does not exist as lookup
2775 --
2776 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2777 fnd_message.set_token('FIELD','p_one_ann_pymt_cd');
2778 fnd_message.set_token('VALUE', p_one_ann_pymt_cd);
2779 fnd_message.set_token('TYPE','BEN_ONE_ANN_PYMT');
2780 fnd_message.raise_error;
2781 --
2782 end if;
2783 --
2784 end if;
2785 --
2786 if (l_api_updating
2787 and p_acty_typ_cd
2788 <> nvl(ben_abr_shd.g_old_rec.acty_typ_cd,hr_api.g_varchar2)
2789 or not l_api_updating)
2790 and p_acty_typ_cd is not null then
2791 --
2792 -- check if value of lookup falls within lookup type.
2793 --
2794 if hr_api.not_exists_in_hr_lookups
2795 (p_lookup_type => 'BEN_ACTY_TYP',
2796 p_lookup_code => p_acty_typ_cd,
2797 p_effective_date => p_effective_date) then
2798 --
2799 -- raise error as does not exist as lookup
2800 --
2801 fnd_message.set_name('BEN','BEN_91191_ACTY_TYP_CD');
2802 fnd_message.raise_error;
2803 --
2804 end if;
2805 --
2806 -- If Activity Type Code = Distribution, the Partial Month
2807 -- Determination Method Code must be blank.
2808 If p_rt_usg_cd = 'FLXCR' and p_acty_typ_cd in ('ERPYD', 'EEPYD')
2809 and p_PRTL_MO_DET_MTHD_CD is not null
2810 then
2811 fnd_message.set_name('BEN','BEN_91418_PRTL_MO_DET_CD_NOTNL');
2812 fnd_message.raise_error;
2813 end if;
2814 end if;
2815 --
2816 --
2817 if (l_api_updating
2818 and p_rt_typ_cd
2819 <> nvl(ben_abr_shd.g_old_rec.rt_typ_cd,hr_api.g_varchar2)
2820 or not l_api_updating)
2821 and p_rt_typ_cd is not null then
2822 --
2823 -- check if value of lookup falls within lookup type.
2824 --
2825 if hr_api.not_exists_in_hr_lookups
2826 (p_lookup_type => 'BEN_RT_TYP',
2827 p_lookup_code => p_rt_typ_cd,
2828 p_effective_date => p_effective_date) then
2829 --
2830 -- raise error as does not exist as lookup
2831 --
2832 fnd_message.set_name('BEN','BEN_91192_RT_TYP_CD');
2833 fnd_message.raise_error;
2834 --
2835 end if;
2836 --
2837 end if;
2838 --
2839 if (l_api_updating
2840 and p_bnft_rt_typ_cd
2841 <> nvl(ben_abr_shd.g_old_rec.bnft_rt_typ_cd,hr_api.g_varchar2)
2842 or not l_api_updating)
2843 and p_bnft_rt_typ_cd is not null then
2844 --
2845 -- check if value of lookup falls within lookup type.
2846 --
2847 if hr_api.not_exists_in_hr_lookups
2848 (p_lookup_type => 'BEN_RT_TYP',
2849 p_lookup_code => p_bnft_rt_typ_cd,
2850 p_effective_date => p_effective_date) then
2851 --
2852 -- raise error as does not exist as lookup
2853 --
2854 fnd_message.set_name('BEN','BEN_91192_RT_TYP_CD');
2855 fnd_message.raise_error;
2856 --
2857 end if;
2858 --
2859 end if;
2860 --
2861 --
2862 if (l_api_updating
2863 and p_tx_typ_cd
2864 <> nvl(ben_abr_shd.g_old_rec.tx_typ_cd,hr_api.g_varchar2)
2865 or not l_api_updating)
2866 and p_tx_typ_cd is not null then
2867 --
2868 -- check if value of lookup falls within lookup type.
2869 --
2870 if hr_api.not_exists_in_hr_lookups
2871 (p_lookup_type => 'BEN_TX_TYP',
2872 p_lookup_code => p_tx_typ_cd,
2873 p_effective_date => p_effective_date) then
2874 --
2875 -- raise error as does not exist as lookup
2876 --
2877 fnd_message.set_name('BEN','BEN_91193_INVLD_TX_TYP_CD');
2878 fnd_message.raise_error;
2879 --
2880 end if;
2881 --
2882 end if;
2883 --
2884 --
2885 if (l_api_updating
2886 and p_parnt_chld_cd
2887 <> nvl(ben_abr_shd.g_old_rec.parnt_chld_cd,hr_api.g_varchar2)
2888 or not l_api_updating)
2889 and p_parnt_chld_cd is not null then
2890 --
2891 -- check if value of lookup falls within lookup type.
2892 --
2893 if hr_api.not_exists_in_hr_lookups
2894 (p_lookup_type => 'BEN_PRNT_CHLD',
2895 p_lookup_code => p_parnt_chld_cd,
2896 p_effective_date => p_effective_date) then
2897 --
2898 -- raise error as does not exist as lookup
2899 --
2900 fnd_message.set_name('BEN','BEN_91194_INVLD_PARNT_CHLD_CD');
2901 fnd_message.raise_error;
2902 --
2903 end if;
2904 --
2905 end if;
2906 --
2910 <> nvl(ben_abr_shd.g_old_rec.rt_mlt_cd,hr_api.g_varchar2)
2907 --
2908 if (l_api_updating
2909 and p_rt_mlt_cd
2911 or not l_api_updating)
2912 and p_rt_mlt_cd is not null then
2913 --
2914 -- check if value of lookup falls within lookup type.
2915 --
2916 if hr_api.not_exists_in_hr_lookups
2917 (p_lookup_type => 'BEN_MLT',
2918 p_lookup_code => p_rt_mlt_cd,
2919 p_effective_date => p_effective_date) then
2920 --
2921 -- raise error as does not exist as lookup
2922 --
2923 fnd_message.set_name('BEN','BEN_91195_INVLD_RT_MLT_CD');
2924 fnd_message.raise_error;
2925 --
2926 end if;
2927 --
2928 -- COMMENT OUT PER BUG# 894
2929 -- -- Rate Multiple code = Balance Type or Comp lvl Factor
2930 -- if p_rt_usg_cd = 'FLXCR' and
2931 -- p_rt_mlt_cd not in ('BALTYP', 'COMPLVLFCTR', 'NONE') THEN
2932 -- fnd_message.set_name('BEN','BEN_91416_INVLD_MLT_FOR_FLXCR');
2933 -- fnd_message.raise_error;
2934 -- end if;
2935 --
2936 end if;
2937 --
2938 --
2939 if (l_api_updating
2940 and p_rcrrg_cd
2941 <> nvl(ben_abr_shd.g_old_rec.rcrrg_cd,hr_api.g_varchar2)
2942 or not l_api_updating)
2943 and p_rcrrg_cd is not null then
2944 --
2945 -- check if value of lookup falls within lookup type.
2946 --
2947 if hr_api.not_exists_in_hr_lookups
2948 (p_lookup_type => 'BEN_RCRRG',
2949 p_lookup_code => p_rcrrg_cd,
2950 p_effective_date => p_effective_date) then
2951 --
2952 -- raise error as does not exist as lookup
2953 --
2954 fnd_message.set_name('BEN','BEN_91196_INVLD_RCRRG_CD');
2955 fnd_message.raise_error;
2956 --
2957 end if;
2958 --
2959 end if;
2960 --
2961 --
2962 if (l_api_updating
2963 and p_rndg_cd
2964 <> nvl(ben_abr_shd.g_old_rec.rndg_cd,hr_api.g_varchar2)
2965 or not l_api_updating)
2966 and p_rndg_cd is not null then
2967 --
2968 -- check if value of lookup falls within lookup type.
2969 --
2970 if hr_api.not_exists_in_hr_lookups
2971 (p_lookup_type => 'BEN_RNDG',
2972 p_lookup_code => p_rndg_cd,
2973 p_effective_date => p_effective_date) then
2974 --
2975 -- raise error as does not exist as lookup
2976 --
2977 fnd_message.set_name('BEN','BEN_91041_INVALID_RNDG_CD');
2978 fnd_message.raise_error;
2979 --
2980 end if;
2981 --
2982 end if;
2983 --
2984 --
2985 if (l_api_updating
2986 and p_prtl_mo_det_mthd_cd
2987 <> nvl(ben_abr_shd.g_old_rec.prtl_mo_det_mthd_cd,hr_api.g_varchar2)
2988 or not l_api_updating)
2989 and p_prtl_mo_det_mthd_cd is not null then
2990 --
2991 -- check if value of lookup falls within lookup type.
2992 --
2993 if hr_api.not_exists_in_hr_lookups
2994 (p_lookup_type => 'BEN_PRTL_MO_DET_MTHD',
2995 p_lookup_code => p_prtl_mo_det_mthd_cd,
2996 p_effective_date => p_effective_date) then
2997 --
2998 -- raise error as does not exist as lookup
2999 --
3000 fnd_message.set_name('BEN','BEN_91200_INVLD_PRTL_MO_DET_CD');
3001 fnd_message.raise_error;
3002 --
3003 end if;
3004 --
3005 end if;
3006 --
3007 --
3008 if (l_api_updating
3009 and p_acty_base_rt_stat_cd
3010 <> nvl(ben_abr_shd.g_old_rec.acty_base_rt_stat_cd,hr_api.g_varchar2)
3011 or not l_api_updating)
3012 and p_acty_base_rt_stat_cd is not null then
3013 --
3014 -- check if value of lookup falls within lookup type.
3015 --
3016 if hr_api.not_exists_in_hr_lookups
3017 (p_lookup_type => 'BEN_STAT',
3018 p_lookup_code => p_acty_base_rt_stat_cd,
3019 p_effective_date => p_effective_date) then
3020 --
3021 -- raise error as does not exist as lookup
3022 --
3023 fnd_message.set_name('BEN','BEN_21201_INVLD_BASE_RT_STA_CD');
3024 fnd_message.raise_error;
3025 --
3026 end if;
3027 --
3028 end if;
3029 --
3030 --
3031 if (l_api_updating
3032 and p_procg_src_cd
3033 <> nvl(ben_abr_shd.g_old_rec.procg_src_cd,hr_api.g_varchar2)
3034 or not l_api_updating)
3035 and p_procg_src_cd is not null then
3036 --
3037 -- check if value of lookup falls within lookup type.
3038 --
3039 if hr_api.not_exists_in_hr_lookups
3040 (p_lookup_type => 'BEN_PROCG_SRC',
3041 p_lookup_code => p_procg_src_cd,
3042 p_effective_date => p_effective_date) then
3043 --
3044 -- raise error as does not exist as lookup
3045 --
3046 fnd_message.set_name('BEN','BEN_91203_INVLD_PROCG_SRC_CD');
3047 fnd_message.raise_error;
3048 --
3049 end if;
3050 --
3051 end if;
3052 --
3053 --
3054 if (l_api_updating
3055 and p_frgn_erg_ded_typ_cd
3056 <> nvl(ben_abr_shd.g_old_rec.frgn_erg_ded_typ_cd,hr_api.g_varchar2)
3057 or not l_api_updating)
3058 and p_frgn_erg_ded_typ_cd is not null then
3059 --
3060 -- check if value of lookup falls within lookup type.
3061 --
3062 if hr_api.not_exists_in_hr_lookups
3063 (p_lookup_type => 'BEN_ERG_DED',
3064 p_lookup_code => p_frgn_erg_ded_typ_cd,
3065 p_effective_date => p_effective_date) then
3066 --
3067 -- raise error as does not exist as lookup
3068 --
3069 fnd_message.set_name('BEN','BEN_91204_FRGN_ERG_DED_TYP_CD');
3070 fnd_message.raise_error;
3071 --
3072 end if;
3073 --
3074 end if;
3075 --
3076 --
3077 if (l_api_updating
3078 and p_prtl_mo_eff_dt_det_cd
3079 <> nvl(ben_abr_shd.g_old_rec.prtl_mo_eff_dt_det_cd,hr_api.g_varchar2)
3080 or not l_api_updating)
3081 and p_prtl_mo_eff_dt_det_cd is not null then
3082 --
3083 -- check if value of lookup falls within lookup type.
3084 --
3085 if hr_api.not_exists_in_hr_lookups
3086 (p_lookup_type => 'BEN_PRTL_MO_EFF_DT_DET',
3087 p_lookup_code => p_prtl_mo_eff_dt_det_cd,
3088 p_effective_date => p_effective_date) then
3089 --
3090 -- raise error as does not exist as lookup
3091 --
3092 fnd_message.set_name('BEN','BEN_91205_PRTL_MO_EFF_DT_DE_CD');
3093 fnd_message.raise_error;
3094 --
3095 end if;
3096 --
3097 end if;
3098 --
3099 if (l_api_updating
3100 and p_det_pl_ytd_cntrs_cd
3101 <> nvl(ben_abr_shd.g_old_rec.det_pl_ytd_cntrs_cd,hr_api.g_varchar2)
3102 or not l_api_updating)
3103 and p_det_pl_ytd_cntrs_cd is not null then
3104 --
3105 -- check if value of lookup falls within lookup type.
3106 --
3107 if hr_api.not_exists_in_hr_lookups
3108 (p_lookup_type => 'BEN_DET_PL_YTD_CNTRS',
3109 p_lookup_code => p_det_pl_ytd_cntrs_cd,
3110 p_effective_date => p_effective_date) then
3111 --
3112 -- raise error as does not exist as lookup
3113 --
3114 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3115 fnd_message.set_token('FIELD','p_det_pl_ytd_cntrs_cd');
3116 fnd_message.set_token('VALUE', p_det_pl_ytd_cntrs_cd);
3117 fnd_message.set_token('TYPE','BEN_DET_PL_YTD_CNTRS');
3118 fnd_message.raise_error;
3119 --
3120 end if;
3121 --
3122 end if;
3123 --
3124 if (l_api_updating
3125 and p_asmt_to_use_cd
3126 <> nvl(ben_abr_shd.g_old_rec.asmt_to_use_cd,hr_api.g_varchar2)
3127 or not l_api_updating)
3128 and p_asmt_to_use_cd is not null then
3129 --
3130 -- check if value of lookup falls within lookup type.
3131 --
3132 if hr_api.not_exists_in_hr_lookups
3133 (p_lookup_type => 'BEN_ASMT_TO_USE',
3134 p_lookup_code => p_asmt_to_use_cd,
3135 p_effective_date => p_effective_date) then
3136 --
3137 -- raise error as does not exist as lookup
3138 --
3139 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3140 fnd_message.set_token('FIELD','p_asmt_to_use_cd');
3141 fnd_message.set_token('VALUE', p_asmt_to_use_cd);
3142 fnd_message.set_token('TYPE','BEN_ASMT_TO_USE');
3143 fnd_message.raise_error;
3144 --
3145 end if;
3146 --
3147 end if;
3148 --
3149 if (l_api_updating
3150 and p_currency_det_cd
3151 <> nvl(ben_abr_shd.g_old_rec.currency_det_cd,hr_api.g_varchar2)
3152 or not l_api_updating)
3153 and p_currency_det_cd is not null then
3154 --
3155 -- check if value of lookup falls within lookup type.
3156 --
3157 if hr_api.not_exists_in_hr_lookups
3158 (p_lookup_type => 'BEN_CUR_DET',
3159 p_lookup_code => p_currency_det_cd,
3160 p_effective_date => p_effective_date) then
3161 --
3162 -- raise error as does not exist as lookup
3163 --
3164 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3165 fnd_message.set_token('FIELD','p_currency_det_cd');
3166 fnd_message.set_token('VALUE', p_currency_det_cd);
3167 fnd_message.set_token('TYPE','BEN_CUR_DET');
3168 fnd_message.raise_error;
3169 --
3170 end if;
3171 --
3172 end if;
3173 hr_utility.set_location('Leaving:'||l_proc,10);
3174 --
3175 end chk_all_lookups;
3176 --
3177 -- ----------------------------------------------------------------------------
3178 -- |------< chk_prtl_mo_det_mthd_cd_rl >------|
3179 -- ----------------------------------------------------------------------------
3180 --
3181 -- Description
3182 -- This procedure is used to check that the code/rule dependency as the
3183 -- following:
3184 -- If Code = 'Rule' then rule must be selected.
3185 -- If Code <> 'Rule' thne rule must not be selected.
3186 --
3187 -- Pre Conditions
3188 -- None.
3189 --
3190 -- In Parameters
3191 -- prtl_mo_det_mthd_cd Value of look up value.
3192 -- prtl_mo_det_mthd_rl value of look up Value
3193 -- inserted or updated.
3194 --
3195 -- Post Success
3196 -- Processing continues
3197 --
3198 -- Post Failure
3199 -- Error handled by procedure
3200 --
3201 -- Access Status
3202 -- Internal table handler use only.
3203 --
3204 Procedure chk_prtl_mo_det_mthd_cd_rl(p_prtl_mo_det_mthd_cd in varchar2,
3205 p_prtl_mo_det_mthd_rl in number) is
3206 --
3207 l_proc varchar2(72) := g_package||'chk_prtl_mo_det_mthd_cd_rl';
3208 --
3209 Begin
3210 --
3211 hr_utility.set_location('Entering:'||l_proc, 5);
3212 --
3213 if (p_prtl_mo_det_mthd_cd <> 'RL' and p_prtl_mo_det_mthd_rl is not null)
3214 then
3215 fnd_message.set_name('BEN','BEN_91730_NO_RULE');
3216 fnd_message.raise_error;
3217 end if;
3218
3219 if (p_prtl_mo_det_mthd_cd = 'RL' and p_prtl_mo_det_mthd_rl is null)
3220 then
3221 fnd_message.set_name('BEN','BEN_91731_RULE');
3222 fnd_message.raise_error;
3223 end if;
3224 --
3225 -- Leaving Procedure.
3226 --
3227 hr_utility.set_location('Leaving:'||l_proc,10);
3228 --
3229 end chk_prtl_mo_det_mthd_cd_rl;
3230 --
3231 -- ----------------------------------------------------------------------------
3232 -- |----------------------< chk_rndg_cd_rl >----------------------------------|
3233 -- ----------------------------------------------------------------------------
3234 --
3235 -- Description
3236 -- This procedure is used to check that the code/rule dependency as the
3237 -- following:
3238 -- If Code = 'Rule' then rule must be selected.
3239 -- If Code <> 'Rule' thne rule must not be selected.
3240 --
3241 -- Pre Conditions
3242 -- None.
3243 --
3244 -- In Parameters
3245 -- rndg_cd Value of look up value.
3246 -- rndg_rl value of look up Value
3247 -- inserted or updated.
3248 --
3249 -- Post Success
3250 -- Processing continues
3251 --
3252 -- Post Failure
3253 -- Error handled by procedure
3254 --
3255 -- Access Status
3256 -- Internal table handler use only.
3257 --
3258 Procedure chk_rndg_cd_rl (p_rndg_cd in varchar2,
3259 p_rndg_rl in number) is
3260 --
3261 l_proc varchar2(72) := g_package||'chk_rndg_cd_rl';
3262 --
3263 Begin
3264 --
3265 hr_utility.set_location('Entering:'||l_proc, 5);
3266 --
3267 -- check dependency of Code and Rule.
3268 --
3269 if (p_rndg_cd <> 'RL' and
3270 p_rndg_rl is not null) then
3271 fnd_message.set_name('BEN','BEN_91732_NO_RNDG_RULE');
3272 fnd_message.raise_error;
3273 end if;
3274
3275 if (p_rndg_cd = 'RL' and p_rndg_rl is null) then
3279
3276 fnd_message.set_name('BEN','BEN_91733_RNDG_RULE');
3277 fnd_message.raise_error;
3278 end if;
3280 --
3281 -- Leaving Procedure.
3282 --
3283 hr_utility.set_location('Leaving:'||l_proc,10);
3284 --
3285 end chk_rndg_cd_rl;
3286 --
3287 -- ----------------------------------------------------------------------------
3288 -- |-------------------------< chk_ann_rts >----------------------------------|
3289 -- ----------------------------------------------------------------------------
3290 --
3291 -- Description
3292 -- This procedure is used to check:
3293 -- 1. the mn/mx_ann_elcn_val code/rule dependency as the following:
3294 -- If Code = 'Rule' then rule must be selected.
3295 -- If Code <> 'Rule' thne rule must not be selected.
3296 -- 2. clm and ptd comp level factor id's can't be the same
3297 -- 3. these 6 fields can only be filled in when the entr_ann_val_flag is on.
3298 --
3299 --
3300 -- Pre Conditions
3301 -- None.
3302 --
3303 -- In Parameters
3304 -- prort_mn_ann_elcn_val_cd
3305 -- prort_mn_ann_elcn_val_rl
3306 -- prort_mx_ann_elcn_val_cd
3307 -- prort_mx_ann_elcn_val_rl
3308 -- clm_comp_lvl_fctr_id
3309 -- ptd_comp_lvl_fctr_id
3310 --
3311 --
3312 -- Post Success
3313 -- Processing continues
3314 --
3315 -- Post Failure
3316 -- Error handled by procedure
3317 --
3318 -- Access Status
3319 -- Internal table handler use only.
3320 --
3321 Procedure chk_ann_rts (p_prort_mn_ann_elcn_val_cd in varchar2
3322 ,p_prort_mn_ann_elcn_val_rl in number
3323 ,p_prort_mx_ann_elcn_val_cd in varchar2
3324 ,p_prort_mx_ann_elcn_val_rl in number
3325 ,p_clm_comp_lvl_fctr_id in number
3326 ,p_ptd_comp_lvl_fctr_id in number
3327 ,p_entr_ann_val_flag in varchar2
3328 ,p_rt_mlt_cd in varchar2 ) is
3329 --
3330 l_proc varchar2(72) := g_package||'chk_ann_rts';
3331 --
3332 Begin
3333 --
3334 hr_utility.set_location('Entering:'||l_proc, 5);
3335 --
3336 -- check dependency of Code and Rule.
3337 --
3338 if (p_prort_mn_ann_elcn_val_cd <> 'RL' and
3339 p_prort_mn_ann_elcn_val_rl is not null) or
3340 (p_prort_mx_ann_elcn_val_cd <> 'RL' and
3341 p_prort_mx_ann_elcn_val_rl is not null) then
3342 fnd_message.set_name('BEN','BEN_91730_NO_RULE');
3343 fnd_message.raise_error;
3344 end if;
3345
3346 if (p_prort_mn_ann_elcn_val_cd = 'RL'
3347 and p_prort_mn_ann_elcn_val_rl is null) or
3348 (p_prort_mx_ann_elcn_val_cd = 'RL'
3349 and p_prort_mx_ann_elcn_val_rl is null) then
3350 fnd_message.set_name('BEN','BEN_91731_RULE');
3351 fnd_message.raise_error;
3352 end if;
3353
3354 -- check comp lvl fctrs
3355 if p_clm_comp_lvl_fctr_id = p_ptd_comp_lvl_fctr_id then
3356 fnd_message.set_name('BEN','BEN_92414_SAME_COMP_FCTRS');
3357 fnd_message.raise_error;
3358 end if;
3359
3360 -- check entr ann val flag dependency
3361 -- Bug 4016477. Should able to use for SAREC Case also.
3362 --
3363 if (p_entr_ann_val_flag = 'N' and p_rt_mlt_cd <> 'SAREC') and
3364 (p_clm_comp_lvl_fctr_id is not null or
3365 p_ptd_comp_lvl_fctr_id is not null or
3366 p_prort_mn_ann_elcn_val_cd is not null or
3367 p_prort_mn_ann_elcn_val_rl is not null or
3368 p_prort_mx_ann_elcn_val_cd is not null or
3369 p_prort_mx_ann_elcn_val_rl is not null) then
3370 fnd_message.set_name('BEN','BEN_92415_ENTR_ANN_VAL');
3371 fnd_message.raise_error;
3372 end if;
3373
3374 --
3375 -- Leaving Procedure.
3376 --
3377 hr_utility.set_location('Leaving:'||l_proc,10);
3378 --
3379 end chk_ann_rts;
3380 --
3381 -- ----------------------------------------------------------------------------
3382 -- |------< chk_only_one_id >------|
3383 -- ----------------------------------------------------------------------------
3384 --
3385 -- Description
3386 -- This procedure checks that only one of the program or plan or oipl or
3387 -- ptip id is referenced in a record.
3388 --
3389 -- Pre-Conditions
3390 -- None.
3391 --
3392 -- In Parameters
3393 -- p_pgm_id ID of FK column
3394 -- p_pl_id ID of FK column
3395 -- p_oipl_id ID of FK column
3396 -- p_oiplip_id ID of FK column
3397 -- p_plip_id ID of FK column
3398 -- p_ptip_id ID of FK column
3399 -- p_cmbn_plip_id ID of FK column
3400 -- p_cmbn_ptip_id ID of FK column
3401 -- p_cmbn_ptip_opt_id ID of FK column
3402 --
3403 -- Post Success
3404 -- Processing continues
3405 --
3406 -- Post Failure
3407 -- Error raised.
3408 --
3409 -- Access Status
3410 -- Internal table handler use only.
3411 --
3412 -- Local function only used by chk_only_one_id.
3413 --
3414 function is_id_not_null (id in number) return number is
3415 begin
3416 if nvl(id, hr_api.g_number) <> hr_api.g_number then
3417 return 1;
3418 else
3419 return 0;
3420 end if;
3421 end;
3422 --
3423 --
3424 Procedure chk_only_one_id (p_pgm_id in number,
3425 p_oipl_id in number,
3426 p_opt_id in number,
3427 p_oiplip_id in number,
3428 p_plip_id in number,
3429 p_ptip_id in number,
3430 p_pl_id in number,
3431 p_cmbn_plip_id in number,
3432 p_cmbn_ptip_id in number,
3433 p_cmbn_ptip_opt_id in number
3434 ) is
3435 --
3436 l_proc varchar2(72) := g_package||'chk_only_one_id';
3437 l_cum_id_val number := 0;
3438 --
3439 Begin
3440 --
3441 hr_utility.set_location('Entering:'||l_proc,5);
3442 --
3443 l_cum_id_val := is_id_not_null(p_pgm_id) + is_id_not_null(p_pl_id) +
3444 is_id_not_null(p_plip_id) + is_id_not_null(p_ptip_id) +
3445 is_id_not_null(p_oipl_id) + is_id_not_null(p_cmbn_plip_id) +
3446 is_id_not_null(p_cmbn_ptip_id) + is_id_not_null(p_oiplip_id)
3447 + is_id_not_null(p_cmbn_ptip_opt_id)+ is_id_not_null(p_opt_id) ;
3448 --
3449 -- If more than one id is not null then raise error.
3450 --
3451 if l_cum_id_val > 1 then
3452 --
3453 -- raise error as both pl_id and pgm_id can't be not null
3454 --
3455 fnd_message.set_name('BEN','BEN_91436_ONLY_ONE_ID_ALWD');
3456 fnd_message.raise_error;
3457 --
3458 elsif l_cum_id_val = 0 then
3459 --
3460 -- Atleast all id values are null so raise the error
3461 --
3462 fnd_message.set_name('BEN','BEN_91437_ONE_ID_REQD');
3463 fnd_message.raise_error;
3464 --
3465 end if;
3466 hr_utility.set_location('Leaving:'||l_proc,10);
3467 --
3468 End chk_only_one_id;
3469 --
3470 -- ----------------------------------------------------------------------------
3471 -- |------< chk_actual_premium_asnmt_cd >------|
3472 -- ----------------------------------------------------------------------------
3473 --
3474 -- Description
3475 -- This procedure checks that the user is not able to connect an Actual
3476 -- Premium that has a prem_asnmt_cd = 'PROC' to the acty_base_rt
3477 --
3478 -- Pre-Conditions
3479 -- None.
3480 --
3481 -- In Parameters
3482 -- p_actl_prem_id ID of FK column
3483 -- p_acty_base_rt_id Primary Key
3484 -- p_effective_date session date
3485 -- p_business_group_id business_group_id
3486 --
3487 -- Post Success
3488 -- Processing continues
3489 --
3490 -- Post Failure
3491 -- Error raised.
3492 --
3493 -- Access Status
3494 -- Internal table handler use only.
3495 --
3496 -- Local function only used by chk_actual_premium_asnmt_cd.
3497 --
3498 Procedure chk_actual_premium_asnmt_cd(p_actl_prem_id in number,
3499 p_acty_base_rt_id in number,
3500 p_effective_date in date,
3501 p_business_group_id in number
3502 ) is
3503 --
3504 l_proc varchar2(72) := g_package||'chk_actual_premium_asnmt_cd';
3505 l_dummy char(1);
3506 --
3507 cursor c1 is select null
3508 from ben_actl_prem_f
3509 where actl_prem_id = p_actl_prem_id
3510 and prem_asnmt_cd = 'PROC'
3511 and business_group_id = p_business_group_id
3512 and p_effective_date between effective_start_date
3513 and effective_end_date;
3514 --
3515 Begin
3516 --
3517 hr_utility.set_location('Entering:'||l_proc,5);
3518 --
3519 -- If the actual premium that we are trying to attach to the acty_base_rt
3520 -- has a prem_asnmt_cd = 'PROC' then error.
3521 --
3522 if p_actl_prem_id is not null then
3523 --
3524 open c1;
3525 fetch c1 into l_dummy;
3526 if c1%found then
3527 --
3528 -- the actual premium that we are trying to attach to the
3529 -- acty_base_rt has a prem_asnmt_cd = 'PROC'(error)
3530 --
3531 fnd_message.set_name('BEN','BEN_92457_ACTL_PREM_PROC');
3532 fnd_message.raise_error;
3533 --
3534 end if;
3535 close c1;
3536 --
3537 end if;
3538 --
3539 hr_utility.set_location('Leaving:'||l_proc,10);
3540 --
3541 End chk_actual_premium_asnmt_cd;
3542 --
3543 -- ----------------------------------------------------------------------------
3544 -- |--------------------<chk_pgm_typ_code >-----------------------------|
3545 -- ----------------------------------------------------------------------------
3546 --
3547 -- Description
3551 -- None.
3548 -- make sure the program's type code is either Flex or Flex plus Core.
3549 --
3550 -- Pre Conditions
3552 --
3553 -- In Parameters
3554 -- p_pgm_id
3555 -- p_business_group_id
3556 -- p_effective_date
3557 --
3558 -- Post Success
3559 -- Processing continues
3560 --
3561 -- Post Failure
3562 -- Errors handled by the procedure
3563 --
3564 -- Access Status
3565 -- Internal table handler use only.
3566 --
3567 -- ----------------------------------------------------------------------------
3568 Procedure chk_pgm_typ_code
3569 ( p_pgm_id in number
3570 ,p_ptip_id in number
3571 ,p_plip_id in number
3572 ,p_rt_usg_cd in varchar2
3573 ,p_business_group_id in number
3574 ,p_effective_date in date )
3575 is
3576 l_proc varchar2(72) := g_package||' chk_pgm_typ_code ';
3577 l_dummy char(1);
3578 cursor c1 is select null
3579 from ben_pgm_f
3580 where pgm_id = p_pgm_id
3581 and (pgm_typ_cd = 'FLEX' or pgm_typ_cd = 'FPC')
3582 and business_group_id = p_business_group_id
3583 and p_effective_date between effective_start_date
3584 and effective_end_date;
3585 --
3586 cursor c2 is select null
3587 from ben_pgm_f a, ben_ptip_f b
3588 where b.ptip_id = p_ptip_id
3589 and a.pgm_id = b.pgm_id
3590 and (a.pgm_typ_cd = 'FLEX' or pgm_typ_cd = 'FPC')
3591 and b.business_group_id = p_business_group_id
3592 and p_effective_date between b.effective_start_date
3593 and b.effective_end_date;
3594 --
3595 Begin
3596 --
3597 hr_utility.set_location('Entering:'||l_proc, 5);
3598 --
3599 if p_pgm_id is not null and p_rt_usg_cd = 'FLXCR' then
3600 open c1;
3601 fetch c1 into l_dummy;
3602 if c1%notfound then
3603 close c1;
3604 fnd_message.set_name('BEN','BEN_91414_INVLD_PGM_FOR_FLXCR');
3605 fnd_message.raise_error;
3606 end if;
3607 close c1;
3608 end if;
3609 --
3610 if p_ptip_id is not null and p_rt_usg_cd = 'FLXCR' then
3611 open c2;
3612 fetch c2 into l_dummy;
3613 if c2%notfound then
3614 close c2;
3615 fnd_message.set_name('BEN','BEN_91414_INVLD_PGM_FOR_FLXCR');
3616 fnd_message.raise_error;
3617 end if;
3618 close c2;
3619 end if;
3620 --
3621 hr_utility.set_location('Leaving:'||l_proc, 15);
3622 --
3623 End chk_pgm_typ_code;
3624 --
3625 /*-- ----------------------------------------------------------------------------
3626 -- |----------------------------< chk_organization_id >---------------------------|
3627 -- ----------------------------------------------------------------------------
3628 --
3629 -- Description
3630 -- This procedure is used to check that the referenced foriegn key actually exists
3631 --
3632 -- Pre Conditions
3633 -- None.
3634 --
3635 -- In Parameters
3636 -- acty_base_rt_id PK of record being inserted or updated.
3637 -- p_organization_id Id of FK column
3638 -- effective_date effective date
3639 -- object_version_number Object version number of record being
3640 -- inserted or updated.
3641 --
3642 -- Post Success
3643 -- Processing continues
3644 --
3645 -- Post Failure
3646 -- Error handled by procedure
3647 --
3648 -- Access Status
3649 -- Internal table handler use only.
3650 --
3651 Procedure chk_organization_id(p_acty_base_rt_id in number,
3652 p_organization_id in number,
3653 p_effective_date in date,
3654 p_object_version_number in number) is
3655 --
3656 l_proc varchar2(72) := g_package||'chk_organization_id';
3657 l_api_updating boolean;
3658 l_dummy varchar2(1);
3659 --
3660 cursor c1 is
3661 select null
3662 from hr_organization_units a
3663 where a.organization_id = p_organization_id;
3664 --
3665 Begin
3666 --
3667 hr_utility.set_location('Entering:'||l_proc,5);
3668 --
3669 l_api_updating := ben_abr_shd.api_updating
3670 (p_acty_base_rt_id => p_acty_base_rt_id,
3671 p_effective_date => p_effective_date,
3672 p_object_version_number => p_object_version_number);
3673 --
3674 if (l_api_updating
3675 and nvl(p_organization_id,hr_api.g_number)
3676 <> nvl(ben_abr_shd.g_old_rec.organization_id,hr_api.g_number)
3677 or not l_api_updating) then
3678 --
3679 -- check if organization_id value exists in hr_organization_units table
3680 --
3681 open c1;
3682 --
3683 fetch c1 into l_dummy;
3684 if c1%notfound then
3685 --
3686 close c1;
3687 --
3688 -- raise error as FK does not relate to PK in hr_organization_units
3689 -- table.
3690 --
3691 ben_abr_shd.constraint_error('BEN_ACTY_BASE_RT_FK4');
3692 --
3693 end if;
3694 --
3695 close c1;
3696 --
3697 end if;
3698 --
3699 hr_utility.set_location('Leaving:'||l_proc,10);
3700 --
3701 End chk_organization_id;
3702 --
3703 */
3704
3705 --
3706 -- ----------------------------------------------------------------------------
3710
3707 -- |--------------------------------< chk_pay_rate_grade_rule_id >-------------------------------|
3708 -- ----------------------------------------------------------------------------
3709 --
3711
3712 Procedure chk_pay_rate_grade_rule_id(p_acty_base_rt_id in number,
3713 p_pay_rate_grade_rule_id in number,
3714 p_pl_id in number,
3715 p_opt_id in number,
3716 p_business_group_id in number,
3717 p_effective_date in date,
3718 p_object_version_number in number
3719 ) is
3720 --
3721 l_proc varchar2(72) := g_package||'chk_pay_rate_grade_rule_id';
3722 l_api_updating boolean;
3723 l_dummy varchar2(1);
3724 --
3725 cursor c2 is
3726 select 'x'
3727 From pay_grade_rules_f a , pay_rates b , per_spinal_points c,ben_opt_f opt
3728 where a.grade_rule_id = p_pay_rate_grade_rule_id
3729 and a.rate_id = b.rate_id
3730 and a.rate_type = 'SP'
3731 and c.spinal_point_id = a.grade_or_spinal_point_id
3732 and c.spinal_point_id = opt.mapping_table_pk_id
3733 and opt.mapping_table_name = 'PER_SPINAL_POINTS'
3734 and a.business_group_id = p_business_group_id
3735 and c.business_group_id = p_business_group_id
3736 and b.business_group_id = p_business_group_id
3737 and opt.business_group_id = p_business_group_id
3738 and opt.opt_id = p_opt_id
3739 and p_effective_date between a.effective_start_date and a.effective_end_date
3740 and p_effective_date between opt.effective_start_date and opt.effective_end_date ;
3741
3742
3743 cursor c1 is
3744 select 'x'
3745 From pay_grade_rules_f a , pay_rates b , per_grades c,ben_pl_f pl
3746 where a.grade_rule_id = p_pay_rate_grade_rule_id
3747 and a.rate_id = b.rate_id
3748 and a.rate_type = 'G'
3749 and c.grade_id = a.grade_or_spinal_point_id
3750 and c.grade_id = pl.mapping_table_pk_id
3751 and pl.mapping_table_name = 'PER_GRADES'
3752 and a.business_group_id = p_business_group_id
3753 and c.business_group_id = p_business_group_id
3754 and b.business_group_id = p_business_group_id
3755 and pl.business_group_id = p_business_group_id
3756 and pl.pl_id = p_pl_id
3757 and p_effective_date between a.effective_start_date and a.effective_end_date
3758 and p_effective_date between pl.effective_start_date and pl.effective_end_date ;
3759
3760 --
3761 Begin
3762 --
3763 hr_utility.set_location('Entering:'||l_proc,5);
3764 --
3765 l_api_updating := ben_abr_shd.api_updating
3766 (p_acty_base_rt_id => p_acty_base_rt_id,
3767 p_effective_date => p_effective_date,
3768 p_object_version_number => p_object_version_number);
3769 --
3770 if (l_api_updating
3771 and
3772 ( nvl(p_pay_rate_grade_rule_id,hr_api.g_number)
3773 <> nvl(ben_abr_shd.g_old_rec.pay_rate_grade_rule_id,hr_api.g_number)
3774 or
3775 nvl(p_pl_id,hr_api.g_number)
3776 <> nvl(ben_abr_shd.g_old_rec.pl_id,hr_api.g_number)
3777 or
3778 nvl(p_opt_id,hr_api.g_number)
3779 <> nvl(ben_abr_shd.g_old_rec.opt_id,hr_api.g_number)
3780 )
3781 or not l_api_updating) then
3782 --
3783 -- check if organization_id value exists in hr_organization_units table
3784 --
3785 if p_pl_id is not null then
3786 open c1;
3787 --
3788 fetch c1 into l_dummy;
3789 if c1%notfound then
3790 close c1;
3791 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
3792 fnd_message.set_token('PROCEDURE', l_proc);
3793 fnd_message.set_token('STEP','10');
3794 fnd_message.raise_error;
3795
3796 end if;
3797 --
3798 close c1;
3799 --
3800 End if;
3801
3802 --
3803 if p_opt_id is not null then
3804 open c2;
3805 --
3806 fetch c2 into l_dummy;
3807 if c2%notfound then
3808 close c2;
3809 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
3810 fnd_message.set_token('PROCEDURE', l_proc);
3811 fnd_message.set_token('STEP','11');
3812 fnd_message.raise_error;
3813
3814 end if;
3815 --
3816 close c2;
3817 --
3818 End if;
3819 end if ;
3820 --
3821 hr_utility.set_location('Leaving:'||l_proc,10);
3822 --
3823 End chk_pay_rate_grade_rule_id;
3824
3825
3826
3827
3828 --
3829 -- ----------------------------------------------------------------------------
3830 -- |--------------------------------< chk_nnmntry_uom >-------------------------------|
3831 -- ----------------------------------------------------------------------------
3832 --
3833 -- Description
3834 -- This procedure is used to check that the referenced foriegn key actually exists
3835 --
3836 -- Pre Conditions
3837 -- None.
3838 --
3839 -- In Parameters
3840 -- acty_base_rt_id PK of record being inserted or updated.
3841 -- p_nnmntry_uom FK column
3842 -- effective_date effective date
3843 -- object_version_number Object version number of record being
3844 -- inserted or updated.
3845 --
3846 -- Post Success
3847 -- Processing continues
3848 --
3852 -- Access Status
3849 -- Post Failure
3850 -- Error handled by procedure
3851 --
3853 -- Internal table handler use only.
3854 --
3855 Procedure chk_nnmntry_uom(p_acty_base_rt_id in number,
3856 p_nnmntry_uom in varchar2,
3857 p_effective_date in date,
3858 p_object_version_number in number) is
3859 --
3860 l_proc varchar2(72) := g_package||'chk_nnmntry_uom';
3861 l_api_updating boolean;
3862 l_dummy varchar2(1);
3863 --
3864 cursor c1 is
3865 select null
3866 from fnd_currencies_tl
3867 where currency_code = p_nnmntry_uom;
3868 --
3869 Begin
3870 --
3871 hr_utility.set_location('Entering:'||l_proc,5);
3872 --
3873 l_api_updating := ben_abr_shd.api_updating
3874 (p_acty_base_rt_id => p_acty_base_rt_id,
3875 p_effective_date => p_effective_date,
3876 p_object_version_number => p_object_version_number);
3877 --
3878 if (l_api_updating
3879 and nvl(p_nnmntry_uom,hr_api.g_varchar2)
3880 <> nvl(ben_abr_shd.g_old_rec.nnmntry_uom,hr_api.g_varchar2)
3881 or not l_api_updating) then
3882 --
3883 -- check if nnmntry_uom value exists in fnd_currency_tl table
3884 --
3885 if p_nnmntry_uom is not null then
3886 open c1;
3887 --
3888 fetch c1 into l_dummy;
3889 if c1%notfound then
3890 --
3891 if hr_api.not_exists_in_hr_lookups
3892 (p_lookup_type => 'BEN_NNMNTRY_UOM',
3893 p_lookup_code => p_nnmntry_uom,
3894 p_effective_date => p_effective_date) then
3895 --
3896 -- raise error as FK does not relate to PK in fnd_currencies_tl
3897 -- table.
3898 --
3899 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3900 fnd_message.set_token('FIELD','p_nnmntry_uom');
3901 fnd_message.set_token('VALUE', p_nnmntry_uom);
3902 fnd_message.set_token('TYPE','BEN_NNMNTRY_UOM');
3903 fnd_message.raise_error;
3904 --
3905 end if;
3906 end if;
3907 --
3908 close c1;
3909 end if;
3910 --
3911 end if;
3912 --
3913 hr_utility.set_location('Leaving:'||l_proc,10);
3914 --
3915 End chk_nnmntry_uom;
3916 --
3917 -- ----------------------------------------------------------------------------
3918 -- |----------------------------< chk_oab_element_typ_id >---------------------------|
3919 -- ----------------------------------------------------------------------------
3920 --
3921 -- Description
3922 -- This procedure is used to check that the referenced foriegn key actually exists
3923 --
3924 -- Pre Conditions
3925 -- None.
3926 --
3927 -- In Parameters
3928 -- acty_base_rt_id PK of record being inserted or updated.
3929 -- p_oab_element_typ_id Id of FK column
3930 -- effective_date effective date
3931 -- object_version_number Object version number of record being
3932 -- inserted or updated.
3933 --
3934 -- Post Success
3935 -- Processing continues
3936 --
3937 -- Post Failure
3938 -- Error handled by procedure
3939 --
3940 -- Access Status
3941 -- Internal table handler use only.
3942 --
3943 /* Procedure chk_oab_element_typ_id(p_acty_base_rt_id in number,
3944 p_oab_element_typ_id in number,
3945 p_effective_date in date,
3946 p_object_version_number in number) is
3947 --
3948 l_proc varchar2(72) := g_package||'chk_oab_element_typ_id';
3949 l_api_updating boolean;
3950 l_dummy varchar2(1);
3951 --
3952 cursor c1 is
3953 select null
3954 from ben_oab_ele_typ a
3955 where a.oab_element_typ_id = p_oab_element_typ_id;
3956 --
3957 Begin
3958 --
3959 hr_utility.set_location('Entering:'||l_proc,5);
3960 --
3961 l_api_updating := ben_abr_shd.api_updating
3962 (p_acty_base_rt_id => p_acty_base_rt_id,
3963 p_effective_date => p_effective_date,
3964 p_object_version_number => p_object_version_number);
3965 --
3966 if (l_api_updating
3967 and nvl(p_oab_element_typ_id,hr_api.g_number)
3968 <> nvl(ben_abr_shd.g_old_rec.oab_element_typ_id,hr_api.g_number)
3969 or not l_api_updating) then
3970 --
3971 -- check if oab_element_typ_id value exists in hr_organization_units table
3972 --
3973 open c1;
3974 --
3975 fetch c1 into l_dummy;
3976 if c1%notfound then
3977 --
3978 close c1;
3979 --
3980 -- raise error as FK does not relate to PK in hr_organization_units
3981 -- table.
3982 --
3983 ben_abr_shd.constraint_error('BEN_ACTY_BASE_RT_FK3');
3984 --
3985 end if;
3986 --
3987 close c1;
3988 --
3989 end if;
3990 --
3991 hr_utility.set_location('Leaving:'||l_proc,10);
3992 --
3993 End chk_oab_element_typ_id; */
3994 --
3995 --
3996 -- ----------------------------------------------------------------------------
3997 -- |----------------------< chk_cwb_acty_typ_cd_unique>------------------------|
3998 -- ----------------------------------------------------------------------------
3999 --
4000 -- Description
4001 -- This procedure is used to check that CWB Plans do not have more than one
4005 --
4002 -- Active Rate defined for the same Activity Type Code. This check does not apply
4003 -- to the Activity Type Code 'CWBAHE'. Plans may have multiple 'CWBAHE' rates
4004 -- defined.
4006 -- Pre Conditions
4007 -- None.
4008 --
4009 -- In Parameters
4010 -- acty_base_rt_id PK of record being inserted or updated.
4011 -- p_acty_typ_cd activity type code
4012 -- p_pl_id plan id
4013 -- p_oipl_id Option in Plan Id
4014 -- p_acty_base_rt_stat_cd status code
4015 -- p_business_group_id business group id
4016 -- effective_date effective date
4017 -- object_version_number Object version number of record being
4018 -- inserted or updated.
4019 --
4020 -- Post Success
4021 -- Processing continues
4022 --
4023 -- Post Failure
4024 -- Error handled by procedure
4025 --
4026 -- Access Status
4027 -- Internal table handler use only.
4028 --
4029 Procedure chk_cwb_acty_typ_cd_unique(p_acty_base_rt_id in number,
4030 p_acty_typ_cd in varchar2,
4031 p_pl_id in number,
4032 p_oipl_id in number,
4033 p_acty_base_rt_stat_cd in varchar2,
4034 p_business_group_id in number,
4035 p_effective_date in date,
4036 p_object_version_number in number) is
4037 --
4038 l_proc varchar2(72) := g_package||'chk_cwb_acty_typ_cd_unique';
4039 l_api_updating boolean;
4040 l_dummy varchar2(1);
4041 l_acty_typ_meaning hr_lookups.meaning%type;
4042 --
4043 cursor c1 is
4044 select null
4045 from ben_acty_base_rt abr
4046 where abr.acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
4047 and abr.acty_typ_cd = p_acty_typ_cd
4048 and abr.pl_id = p_pl_id
4049 and abr.business_group_id = p_business_group_id
4050 and abr.acty_base_rt_stat_cd = 'A'
4051 and p_effective_date
4052 between abr.effective_start_date
4053 and abr.effective_end_date;
4054 --
4055 cursor c2 is
4056 select null
4057 from ben_acty_base_rt abr
4058 where abr.acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
4059 and abr.acty_typ_cd = p_acty_typ_cd
4060 and abr.oipl_id = p_oipl_id
4061 and abr.business_group_id = p_business_group_id
4062 and abr.acty_base_rt_stat_cd = 'A'
4063 and p_effective_date
4064 between abr.effective_start_date
4065 and abr.effective_end_date;
4066 --
4067 Begin
4068 --
4069 hr_utility.set_location('Entering:'||l_proc,5);
4070 --
4071 l_api_updating := ben_abr_shd.api_updating
4072 (p_acty_base_rt_id => p_acty_base_rt_id,
4073 p_effective_date => p_effective_date,
4074 p_object_version_number => p_object_version_number);
4075 --
4076 if (l_api_updating
4077 and (nvl(p_acty_typ_cd,hr_api.g_varchar2)
4078 <> nvl(ben_abr_shd.g_old_rec.acty_typ_cd,hr_api.g_varchar2)
4079 or
4080 nvl(p_pl_id,hr_api.g_number)
4081 <> nvl(ben_abr_shd.g_old_rec.pl_id,hr_api.g_number)
4082 or
4083 nvl(p_oipl_id,hr_api.g_number)
4084 <> nvl(ben_abr_shd.g_old_rec.oipl_id,hr_api.g_number)
4085 or
4086 nvl(p_acty_base_rt_stat_cd,hr_api.g_varchar2)
4087 <> nvl(ben_abr_shd.g_old_rec.acty_base_rt_stat_cd,hr_api.g_varchar2)
4088 )
4089 or not l_api_updating) and p_acty_base_rt_stat_cd = 'A' then
4090 --
4091 -- if the activity type code is not 'CWBAHE' then check if any
4092 -- another Active Rate exists for the Plan with the same Activity Type Code
4093 --
4094 if p_acty_typ_cd <> 'CWBAHE' then
4095 if p_oipl_id is not null then
4096 open c2;
4097 --
4098 fetch c2 into l_dummy;
4099 if c2%found then
4100 --
4101 -- raise error as a Rate already exists for the Option in the
4102 -- table ben_acty_base_rt_f with the same Activity Type Code.
4103 --
4104 l_acty_typ_meaning := hr_general.decode_lookup('BEN_ACTY_TYP',p_acty_typ_cd);
4105 fnd_message.set_name('BEN','BEN_93018_ACTY_TYP_NOT_UNIQUE');
4106 fnd_message.set_token('ACTY_TYPE_CODE',l_acty_typ_meaning);
4107 fnd_message.raise_error;
4108 --
4109 end if;
4110 --
4111 close c2;
4112 --
4113 else
4114 --
4115 open c1;
4116 --
4117 fetch c1 into l_dummy;
4118 if c1%found then
4119 --
4120 -- raise error as a Rate already exists for the Plan in the
4121 -- table ben_acty_base_rt_f with the same Activity Type Code.
4122 --
4123 l_acty_typ_meaning := hr_general.decode_lookup('BEN_ACTY_TYP',p_acty_typ_cd);
4124 fnd_message.set_name('BEN','BEN_93018_ACTY_TYP_NOT_UNIQUE');
4125 fnd_message.set_token('ACTY_TYPE_CODE',l_acty_typ_meaning);
4126 fnd_message.raise_error;
4127 --
4128 end if;
4129 --
4130 close c1;
4131 --
4132 end if; -- oipl_id is not null
4133 end if; -- CWBAHE
4134 end if;
4135 hr_utility.set_location('Leaving:'||l_proc,10);
4136 --
4137 End chk_cwb_acty_typ_cd_unique;
4138 --
4139 -- 2940151
4140 --
4141 -- ----------------------------------------------------------------------------
4142 -- |--------------------------------< chk_acty_typ_cd_gsp >-------------------------------|
4143 -- ----------------------------------------------------------------------------
4144 --
4145 -- Description
4146 -- This procedure is used to check a comp object does not have many rates of type GSPSA
4147 --
4148 -- Pre Conditions
4149 -- None.
4150 --
4151 -- In Parameters
4152 -- acty_base_rt_id PK of record being inserted or updated.
4153 -- p_acty_typ_cd Activity type code
4154 -- effective_date effective date
4155 -- object_version_number Object version number of record being
4156 -- inserted or updated.
4157 --
4158 -- Post Success
4159 -- Processing continues
4160 --
4161 -- Post Failure
4162 -- Error handled by procedure
4163 --
4164 -- Access Status
4165 -- Internal table handler use only.
4166 --
4167 Procedure chk_acty_typ_cd_gsp
4168 (p_acty_base_rt_id in number,
4169 --p_pgm_id in number,
4170 p_pl_id in number,
4171 /*p_opt_id in number,
4172 p_plip_id in number,
4173 p_oipl_id in number,*/
4174 p_acty_typ_cd in varchar2,
4175 p_effective_date in date,
4176 p_business_group_id in number,
4177 p_object_version_number in number) is
4178 --
4179 l_proc varchar2(72) := g_package||'chk_acty_typ_cd_gsp';
4180 l_api_updating boolean;
4181 l_dummy varchar2(1);
4182 --
4183 cursor c1 is
4184 select 'X'
4185 from ben_acty_base_rt_f
4186 where pl_id = p_pl_id
4187 and p_effective_date between effective_start_date and effective_end_date
4188 and business_group_id = p_business_group_id
4189 and acty_typ_cd = 'GSPSA';
4190 --
4191 Begin
4192 --
4193 hr_utility.set_location('Entering:'||l_proc,5);
4194 --
4195 l_api_updating := ben_abr_shd.api_updating
4196 (p_acty_base_rt_id => p_acty_base_rt_id,
4197 p_effective_date => p_effective_date,
4198 p_object_version_number => p_object_version_number);
4199 --
4200 if (l_api_updating
4201 and nvl(p_acty_typ_cd,hr_api.g_varchar2)
4202 <> nvl(ben_abr_shd.g_old_rec.acty_typ_cd,hr_api.g_varchar2)
4203 or not l_api_updating) then
4204 --
4205 -- check if nnmntry_uom value exists in fnd_currency_tl table
4206 --
4207
4208 open c1;
4209 --
4210 fetch c1 into l_dummy;
4211 if c1%found then
4212 --
4213 fnd_message.set_name('BEN','BEN_93549_ACTY_TYP_GSP');
4214 fnd_message.raise_error;
4215 --
4216
4217 end if;
4218 --
4219 close c1;
4220
4221 --
4222 end if;
4223 --
4224 hr_utility.set_location('Leaving:'||l_proc,10);
4225 --
4226 End chk_acty_typ_cd_gsp;
4227 --
4228 -- ----------------------------------------------------------------------------
4229 -- |--------------------------------< chk_cwb_element_currency >-------------------------------|
4230 -- ----------------------------------------------------------------------------
4231 --
4232 --
4233 Procedure chk_cwb_element_currency
4234 (p_element_det_rl in number,
4235 p_currency_det_cd in varchar2,
4236 p_acty_typ_cd in varchar2
4237 ) is
4238 --
4239 l_proc varchar2(72) := g_package||'chk_cwb_element_currency';
4240
4241 --
4242
4243 Begin
4244 --
4245 hr_utility.set_location('Entering:'||l_proc,5);
4246 --
4247
4248
4249 if p_element_det_rl is not null and p_acty_typ_cd not like 'CWB%' then
4250
4251 --
4252 fnd_message.set_name('BEN','BEN_94998_CWB_INVLD');
4253 fnd_message.set_token('FIELD','Element Determination Rule');
4254 fnd_message.raise_error;
4255 --
4256 end if;
4257
4258 if p_currency_det_cd is NOT NULL and p_acty_typ_cd not like 'CWB%' then
4259
4260 --
4261 fnd_message.set_name('BEN','BEN_94998_CWB_INVLD');
4262 fnd_message.set_token('FIELD','Currency Determination Code');
4263 fnd_message.raise_error;
4264 --
4265 end if;
4266 --
4267 hr_utility.set_location('Leaving:'||l_proc,10);
4268 --
4269 End chk_cwb_element_currency;
4270
4271 -- ----------------------------------------------------------------------------
4272 -- |------------------------< future_var_rt_recs_exist >-------------------------|
4273 -- ----------------------------------------------------------------------------
4274 -- {Start Of Comments}
4275 --
4276 -- Description:
4277 -- This procedure is used for referential integrity of datetracked
4278 -- parent entities when a datetrack operation is taking place
4279 -- and where there is no cascading of update defined for this entity.
4280 --
4281 -- Prerequisites:
4282 -- This procedure is called from the dt_delete_validate.
4283 --
4284 -- In Parameters:
4285 --
4286 -- Post Success:
4287 -- Processing continues.
4288 --
4289 -- Post Failure:
4290 --
4291 -- Developer Implementation Notes:
4292 -- This procedure should not need maintenance unless the HR Schema model
4293 -- changes.
4294 --
4295 -- Access Status:
4296 -- Internal Row Handler Use Only.
4297 --
4298 -- {End Of Comments}
4299 -- ----------------------------------------------------------------------------
4300
4301 Function future_var_rt_recs_exist (
4302 p_acty_base_rt_id in number,
4303 p_datetrack_mode in varchar2,
4304 p_validation_start_date in date,
4305 p_validation_end_date in date)
4306 return boolean Is
4307 --
4308 l_proc varchar2(72) := g_package||'future_var_rt_recs_exist';
4309 l_Dummy varchar2(1);
4310 l_fut_rows_exist Exception;
4311 l_curr_uses_varbl_rt_flag varchar2(30) ;
4312 l_return_val boolean := FALSE ;
4313 --
4314 cursor c_fut_var_rt_recs is
4315 select 1
4316 from ben_acty_vrbl_rt_f
4317 where acty_base_rt_id = p_acty_base_rt_id
4318 and effective_start_date <= p_validation_end_date
4319 and effective_end_date >= p_validation_start_date;
4320 --
4321 Begin
4322 --
4323 hr_utility.set_location('Entering: '||l_proc,9);
4324 --
4325 l_curr_uses_varbl_rt_flag := ben_abr_shd.g_old_rec.uses_varbl_rt_flag;
4326 --
4327 If l_curr_uses_varbl_rt_flag = 'N' then
4328 --
4329 -- Get next validation start and end dates
4330 --
4331 Open c_fut_var_rt_recs;
4332 fetch c_fut_var_rt_recs into l_dummy;
4333 if c_fut_var_rt_recs%FOUND then
4334 l_return_val := TRUE ;
4335 else
4336 l_return_val := FALSE ;
4337 End If;
4338 close c_fut_var_rt_recs;
4339 End if;
4340 --
4341 hr_utility.set_location('Leaving:'||l_proc,15);
4342 --
4343 return l_return_val;
4344 --
4345 End future_var_rt_recs_exist;
4346 --
4347 -- end
4348 -- ----------------------------------------------------------------------------
4349 -- |--------------------------< dt_update_validate >--------------------------|
4350 -- ----------------------------------------------------------------------------
4351 -- {Start Of Comments}
4352 --
4353 -- Description:
4354 -- This procedure is used for referential integrity of datetracked
4355 -- parent entities when a datetrack update operation is taking place
4356 -- and where there is no cascading of update defined for this entity.
4357 --
4358 -- Prerequisites:
4359 -- This procedure is called from the update_validate.
4360 --
4361 -- In Parameters:
4362 --
4363 -- Post Success:
4364 -- Processing continues.
4365 --
4366 -- Post Failure:
4367 --
4368 -- Developer Implementation Notes:
4369 -- This procedure should not need maintenance unless the HR Schema model
4370 -- changes.
4371 --
4372 -- Access Status:
4373 -- Internal Row Handler Use Only.
4374 --
4375 -- {End Of Comments}
4376 -- ----------------------------------------------------------------------------
4377 Procedure dt_update_validate
4378 (p_prtl_mo_det_mthd_rl in number default hr_api.g_number,
4379 p_prtl_mo_eff_dt_det_rl in number default hr_api.g_number,
4380 p_rndg_rl in number default hr_api.g_number,
4381 p_lwr_lmt_calc_rl in number default hr_api.g_number,
4382 p_upr_lmt_calc_rl in number default hr_api.g_number,
4383 p_val_calc_rl in number default hr_api.g_number,
4384 p_vstg_for_acty_rt_id in number default hr_api.g_number,
4385 p_actl_prem_id in number default hr_api.g_number,
4386 p_parnt_acty_base_rt_id in number default hr_api.g_number,
4387 p_pgm_id in number default hr_api.g_number,
4388 p_ptip_id in number default hr_api.g_number,
4389 p_oipl_id in number default hr_api.g_number,
4390 p_opt_id in number default hr_api.g_number,
4391 p_oiplip_id in number default hr_api.g_number,
4392 p_plip_id in number default hr_api.g_number,
4393 p_pl_id in number default hr_api.g_number,
4394 -- bug: 5367301 start
4395 p_element_type_id in number default hr_api.g_number,
4396 p_input_va_calc_rl in number default hr_api.g_number,
4397 p_element_det_rl in number default hr_api.g_number,
4398 -- bug: 5367301 end
4399 p_datetrack_mode in varchar2,
4400 p_validation_start_date in date,
4401 p_validation_end_date in date) Is
4402 --
4403 l_proc varchar2(72) := g_package||'dt_update_validate';
4404 l_integrity_error Exception;
4405 l_table_name all_tables.table_name%TYPE;
4406 --
4407 Begin
4408 hr_utility.set_location('Entering:'||l_proc, 5);
4409 --
4410 -- Ensure that the p_datetrack_mode argument is not null
4411 --
4412 hr_api.mandatory_arg_error
4413 (p_api_name => l_proc,
4414 p_argument => 'datetrack_mode',
4415 p_argument_value => p_datetrack_mode);
4416 --
4417 -- Only perform the validation if the datetrack update mode is valid
4418 --
4419 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
4420 --
4421 --
4422 -- Ensure the arguments are not null
4423 --
4424 hr_api.mandatory_arg_error
4425 (p_api_name => l_proc,
4426 p_argument => 'validation_start_date',
4427 p_argument_value => p_validation_start_date);
4428 --
4429 hr_api.mandatory_arg_error
4430 (p_api_name => l_proc,
4431 p_argument => 'validation_end_date',
4432 p_argument_value => p_validation_end_date);
4433 --
4434 If ((nvl(p_prtl_mo_det_mthd_rl, hr_api.g_number) <> hr_api.g_number) and
4435 NOT (dt_api.check_min_max_dates
4436 (p_base_table_name => 'ff_formulas_f',
4437 p_base_key_column => 'formula_id',
4438 p_base_key_value => p_prtl_mo_det_mthd_rl,
4439 p_from_date => p_validation_start_date,
4440 p_to_date => p_validation_end_date))) Then
4441 l_table_name := 'ff_formulas_f';
4442 Raise l_integrity_error;
4443 End If;
4444 If ((nvl(p_prtl_mo_eff_dt_det_rl, hr_api.g_number) <> hr_api.g_number) and
4445 NOT (dt_api.check_min_max_dates
4446 (p_base_table_name => 'ff_formulas_f',
4447 p_base_key_column => 'formula_id',
4448 p_base_key_value => p_prtl_mo_eff_dt_det_rl,
4449 p_from_date => p_validation_start_date,
4450 p_to_date => p_validation_end_date))) Then
4451 l_table_name := 'ff_formulas_f';
4452 Raise l_integrity_error;
4453 End If;
4454 If ((nvl(p_rndg_rl, hr_api.g_number) <> hr_api.g_number) and
4455 NOT (dt_api.check_min_max_dates
4456 (p_base_table_name => 'ff_formulas_f',
4457 p_base_key_column => 'formula_id',
4458 p_base_key_value => p_rndg_rl,
4459 p_from_date => p_validation_start_date,
4460 p_to_date => p_validation_end_date))) Then
4461 l_table_name := 'ff_formulas_f';
4462 Raise l_integrity_error;
4463 End If;
4464 If ((nvl(p_lwr_lmt_calc_rl, hr_api.g_number) <> hr_api.g_number) and
4465 NOT (dt_api.check_min_max_dates
4466 (p_base_table_name => 'ff_formulas_f',
4467 p_base_key_column => 'formula_id',
4468 p_base_key_value => p_lwr_lmt_calc_rl,
4469 p_from_date => p_validation_start_date,
4470 p_to_date => p_validation_end_date))) Then
4471 l_table_name := 'ff_formulas_f';
4472 Raise l_integrity_error;
4473 End If;
4474 If ((nvl(p_upr_lmt_calc_rl, hr_api.g_number) <> hr_api.g_number) and
4475 NOT (dt_api.check_min_max_dates
4476 (p_base_table_name => 'ff_formulas_f',
4477 p_base_key_column => 'formula_id',
4478 p_base_key_value => p_upr_lmt_calc_rl,
4479 p_from_date => p_validation_start_date,
4480 p_to_date => p_validation_end_date))) Then
4481 l_table_name := 'ff_formulas_f';
4482 Raise l_integrity_error;
4483 End If;
4484 If ((nvl(p_val_calc_rl, hr_api.g_number) <> hr_api.g_number) and
4485 NOT (dt_api.check_min_max_dates
4486 (p_base_table_name => 'ff_formulas_f',
4487 p_base_key_column => 'formula_id',
4488 p_base_key_value => p_val_calc_rl,
4489 p_from_date => p_validation_start_date,
4490 p_to_date => p_validation_end_date))) Then
4491 l_table_name := 'ff_formulas_f';
4492 Raise l_integrity_error;
4493 End If;
4494 If ((nvl(p_vstg_for_acty_rt_id, hr_api.g_number) <> hr_api.g_number) and
4495 NOT (dt_api.check_min_max_dates
4496 (p_base_table_name => 'ben_vstg_for_acty_rt_f',
4497 p_base_key_column => 'vstg_for_acty_rt_id',
4498 p_base_key_value => p_vstg_for_acty_rt_id,
4499 p_from_date => p_validation_start_date,
4500 p_to_date => p_validation_end_date))) Then
4501 l_table_name := 'ben_vstg_for_acty_rt_f';
4502 Raise l_integrity_error;
4503 End If;
4504 If ((nvl(p_actl_prem_id, hr_api.g_number) <> hr_api.g_number) and
4505 NOT (dt_api.check_min_max_dates
4506 (p_base_table_name => 'ben_actl_prem_f',
4507 p_base_key_column => 'actl_prem_id',
4508 p_base_key_value => p_actl_prem_id,
4509 p_from_date => p_validation_start_date,
4510 p_to_date => p_validation_end_date))) Then
4511 l_table_name := 'ben_vstg_for_acty_rt_f';
4512 Raise l_integrity_error;
4513 End If;
4514 If ((nvl(p_pgm_id, hr_api.g_number) <> hr_api.g_number) and
4515 NOT (dt_api.check_min_max_dates
4516 (p_base_table_name => 'ben_pgm_f',
4517 p_base_key_column => 'pgm_id',
4518 p_base_key_value => p_pgm_id,
4519 p_from_date => p_validation_start_date,
4520 p_to_date => p_validation_end_date))) Then
4521 l_table_name := 'ben_pgm_f';
4522 Raise l_integrity_error;
4523 End If;
4524 If ((nvl(p_parnt_acty_base_rt_id, hr_api.g_number) <> hr_api.g_number) and
4525 NOT (dt_api.check_min_max_dates
4526 (p_base_table_name => 'ben_acty_base_rt_f',
4527 p_base_key_column => 'acty_base_rt_id',
4528 p_base_key_value => p_parnt_acty_base_rt_id,
4529 p_from_date => p_validation_start_date,
4530 p_to_date => p_validation_end_date))) Then
4531 l_table_name := 'ben_acty_base_rt_f';
4532 Raise l_integrity_error;
4533 End If;
4534 If ((nvl(p_ptip_id, hr_api.g_number) <> hr_api.g_number) and
4535 NOT (dt_api.check_min_max_dates
4536 (p_base_table_name => 'ben_ptip_f',
4537 p_base_key_column => 'ptip_id',
4538 p_base_key_value => p_ptip_id,
4539 p_from_date => p_validation_start_date,
4540 p_to_date => p_validation_end_date))) Then
4541 l_table_name := 'ben_ptip_f';
4542 Raise l_integrity_error;
4543 End If;
4544 If ((nvl(p_oipl_id, hr_api.g_number) <> hr_api.g_number) and
4545 NOT (dt_api.check_min_max_dates
4546 (p_base_table_name => 'ben_oipl_f',
4547 p_base_key_column => 'oipl_id',
4548 p_base_key_value => p_oipl_id,
4549 p_from_date => p_validation_start_date,
4550 p_to_date => p_validation_end_date))) Then
4551 l_table_name := 'ben_oipl_f';
4552 Raise l_integrity_error;
4553 End If;
4554 If ((nvl(p_opt_id, hr_api.g_number) <> hr_api.g_number) and
4555 NOT (dt_api.check_min_max_dates
4556 (p_base_table_name => 'ben_opt_f',
4557 p_base_key_column => 'opt_id',
4558 p_base_key_value => p_opt_id,
4559 p_from_date => p_validation_start_date,
4560 p_to_date => p_validation_end_date))) Then
4561 l_table_name := 'ben_opt_f';
4562 Raise l_integrity_error;
4563 End If;
4564 If ((nvl(p_oiplip_id, hr_api.g_number) <> hr_api.g_number) and
4565 NOT (dt_api.check_min_max_dates
4566 (p_base_table_name => 'ben_oiplip_f',
4567 p_base_key_column => 'oiplip_id',
4568 p_base_key_value => p_oiplip_id,
4569 p_from_date => p_validation_start_date,
4570 p_to_date => p_validation_end_date))) Then
4574 If ((nvl(p_plip_id, hr_api.g_number) <> hr_api.g_number) and
4571 l_table_name := 'ben_oiplip_f';
4572 Raise l_integrity_error;
4573 End If;
4575 NOT (dt_api.check_min_max_dates
4576 (p_base_table_name => 'ben_plip_f',
4577 p_base_key_column => 'plip_id',
4578 p_base_key_value => p_plip_id,
4579 p_from_date => p_validation_start_date,
4580 p_to_date => p_validation_end_date))) Then
4581 l_table_name := 'ben_plip_f';
4582 Raise l_integrity_error;
4583 End If;
4584 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
4585 NOT (dt_api.check_min_max_dates
4586 (p_base_table_name => 'ben_pl_f',
4587 p_base_key_column => 'pl_id',
4588 p_base_key_value => p_pl_id,
4589 p_from_date => p_validation_start_date,
4590 p_to_date => p_validation_end_date))) Then
4591 l_table_name := 'ben_pl_f';
4592 Raise l_integrity_error;
4593 End If;
4594 -- ELEMENT_TYPE
4595 -- Bug: 5367301 start
4596 If ((nvl(p_element_type_id, hr_api.g_number) <> hr_api.g_number) and
4597 NOT (dt_api.check_min_max_dates
4598 (p_base_table_name => 'pay_element_types_f',
4599 p_base_key_column => 'element_type_id',
4600 p_base_key_value => p_element_type_id,
4601 p_from_date => p_validation_start_date,
4602 p_to_date => p_validation_end_date))) Then
4603 l_table_name := 'pay_element_types_f';
4604 Raise l_integrity_error;
4605 End If;
4606 --
4607 If ((nvl(p_input_va_calc_rl, hr_api.g_number) <> hr_api.g_number) and
4608 NOT (dt_api.check_min_max_dates
4609 (p_base_table_name => 'ff_formulas_f',
4610 p_base_key_column => 'formula_id',
4611 p_base_key_value => p_input_va_calc_rl,
4612 p_from_date => p_validation_start_date,
4613 p_to_date => p_validation_end_date))) Then
4614 l_table_name := 'ff_formulas_f';
4615 Raise l_integrity_error;
4616 End If;
4617 --
4618 If ((nvl(p_element_det_rl, hr_api.g_number) <> hr_api.g_number) and
4619 NOT (dt_api.check_min_max_dates
4620 (p_base_table_name => 'ff_formulas_f',
4621 p_base_key_column => 'formula_id',
4622 p_base_key_value => p_element_det_rl,
4623 p_from_date => p_validation_start_date,
4624 p_to_date => p_validation_end_date))) Then
4625 l_table_name := 'ff_formulas_f';
4626 Raise l_integrity_error;
4627 End If;
4628 --
4629
4630
4631 -- Bug: 5367301 end
4632
4633 --
4634 End If;
4635 --
4636 hr_utility.set_location(' Leaving:'||l_proc, 10);
4637 Exception
4638 When l_integrity_error Then
4639 --
4640 -- A referential integrity check was violated therefore
4641 -- we must error
4642 --
4643 ben_utility.parent_integrity_error(p_table_name => l_table_name);
4644 --
4645 When Others Then
4646 --
4647 -- An unhandled or unexpected error has occurred which
4648 -- we must report
4649 --
4650 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
4651 fnd_message.set_token('PROCEDURE', l_proc);
4652 fnd_message.set_token('STEP','15');
4653 fnd_message.raise_error;
4654 End dt_update_validate;
4655 --
4656 -- ----------------------------------------------------------------------------
4657 -- |--------------------------< dt_delete_validate >--------------------------|
4658 -- ----------------------------------------------------------------------------
4659 -- {Start Of Comments}
4660 --
4661 -- Description:
4662 -- This procedure is used for referential integrity of datetracked
4663 -- child entities when either a datetrack DELETE or ZAP is in operation
4664 -- and where there is no cascading of delete defined for this entity.
4665 -- For the datetrack mode of DELETE or ZAP we must ensure that no
4666 -- datetracked child rows exist between the validation start and end
4667 -- dates.
4668 --
4669 -- Prerequisites:
4670 -- This procedure is called from the delete_validate.
4671 --
4672 -- In Parameters:
4673 --
4674 -- Post Success:
4675 -- Processing continues.
4676 --
4677 -- Post Failure:
4678 -- If a row exists by determining the returning Boolean value from the
4679 -- generic dt_api.rows_exist function then we must supply an error via
4680 -- the use of the local exception handler l_rows_exist.
4681 --
4682 -- Developer Implementation Notes:
4683 -- This procedure should not need maintenance unless the HR Schema model
4684 -- changes.
4685 --
4686 -- Access Status:
4687 -- Internal Row Handler Use Only.
4688 --
4689 -- {End Of Comments}
4690 -- ----------------------------------------------------------------------------
4691 Procedure dt_delete_validate
4692 (p_acty_base_rt_id in number,
4693 p_datetrack_mode in varchar2,
4694 p_validation_start_date in date,
4695 p_validation_end_date in date) Is
4696 --
4697 l_proc varchar2(72) := g_package||'dt_delete_validate';
4698 l_rows_exist Exception;
4699 l_table_name all_tables.table_name%TYPE;
4700 --
4701 Begin
4702 hr_utility.set_location('Entering:'||l_proc, 5);
4703 --
4704 -- Ensure that the p_datetrack_mode argument is not null
4705 --
4706 hr_api.mandatory_arg_error
4707 (p_api_name => l_proc,
4708 p_argument => 'datetrack_mode',
4709 p_argument_value => p_datetrack_mode);
4710 --
4711 -- Only perform the validation if the datetrack mode is either
4712 -- DELETE or ZAP
4713 --
4714 If (p_datetrack_mode = 'DELETE' or
4715 p_datetrack_mode = 'ZAP') then
4716 --
4717 --
4718 -- Ensure the arguments are not null
4719 --
4720 hr_api.mandatory_arg_error
4721 (p_api_name => l_proc,
4722 p_argument => 'validation_start_date',
4723 p_argument_value => p_validation_start_date);
4724 --
4725 hr_api.mandatory_arg_error
4726 (p_api_name => l_proc,
4727 p_argument => 'validation_end_date',
4728 p_argument_value => p_validation_end_date);
4729 --
4730 hr_api.mandatory_arg_error
4731 (p_api_name => l_proc,
4732 p_argument => 'acty_base_rt_id',
4733 p_argument_value => p_acty_base_rt_id);
4734 --
4735 If (dt_api.rows_exist
4736 (p_base_table_name => 'ben_acty_rt_ptd_lmt_f',
4737 p_base_key_column => 'acty_base_rt_id',
4738 p_base_key_value => p_acty_base_rt_id,
4739 p_from_date => p_validation_start_date,
4740 p_to_date => p_validation_end_date)) Then
4741 l_table_name := 'ben_acty_rt_ptd_lmt_f';
4742 Raise l_rows_exist;
4743 End If;
4744 If (dt_api.rows_exist
4745 (p_base_table_name => 'ben_acty_rt_pymt_sched_f',
4746 p_base_key_column => 'acty_base_rt_id',
4747 p_base_key_value => p_acty_base_rt_id,
4748 p_from_date => p_validation_start_date,
4749 p_to_date => p_validation_end_date)) Then
4750 l_table_name := 'ben_acty_rt_pymt_sched_f';
4751 Raise l_rows_exist;
4752 End If;
4753 If (dt_api.rows_exist
4754 (p_base_table_name => 'ben_acty_base_rt_f',
4755 p_base_key_column => 'parnt_acty_base_rt_id',
4756 p_base_key_value => p_acty_base_rt_id,
4757 p_from_date => p_validation_start_date,
4758 p_to_date => p_validation_end_date)) Then
4759 l_table_name := 'ben_acty_base_rt_f';
4760 Raise l_rows_exist;
4761 End If;
4762 If (dt_api.rows_exist
4763 (p_base_table_name => 'ben_acty_vrbl_rt_f',
4764 p_base_key_column => 'acty_base_rt_id',
4765 p_base_key_value => p_acty_base_rt_id,
4766 p_from_date => p_validation_start_date,
4767 p_to_date => p_validation_end_date)) Then
4768 l_table_name := 'ben_acty_vrbl_rt_f';
4769 Raise l_rows_exist;
4770 End If;
4771 If (dt_api.rows_exist
4772 (p_base_table_name => 'ben_prtl_mo_rt_prtn_val_f',
4773 p_base_key_column => 'acty_base_rt_id',
4774 p_base_key_value => p_acty_base_rt_id,
4775 p_from_date => p_validation_start_date,
4776 p_to_date => p_validation_end_date)) Then
4777 l_table_name := 'ben_prtl_mo_rt_prtn_val_f';
4778 Raise l_rows_exist;
4779 End If;
4780 If (dt_api.rows_exist
4781 (p_base_table_name => 'ben_mtchg_rt_f',
4782 p_base_key_column => 'acty_base_rt_id',
4783 p_base_key_value => p_acty_base_rt_id,
4784 p_from_date => p_validation_start_date,
4785 p_to_date => p_validation_end_date)) Then
4786 l_table_name := 'ben_mtchg_rt_f';
4787 Raise l_rows_exist;
4788 End If;
4789 If (dt_api.rows_exist
4790 (p_base_table_name => 'ben_bnft_prvdd_ldgr_f',
4791 p_base_key_column => 'acty_base_rt_id',
4792 p_base_key_value => p_acty_base_rt_id,
4793 p_from_date => p_validation_start_date,
4794 p_to_date => p_validation_end_date)) Then
4795 l_table_name := 'ben_bnft_prvdd_ldgr_f';
4796 Raise l_rows_exist;
4797 End If;
4798 If (dt_api.rows_exist
4799 (p_base_table_name => 'ben_bnft_pool_rlovr_rqmt_f',
4800 p_base_key_column => 'acty_base_rt_id',
4801 p_base_key_value => p_acty_base_rt_id,
4802 p_from_date => p_validation_start_date,
4803 p_to_date => p_validation_end_date)) Then
4804 l_table_name := 'ben_bnft_pool_rlovr_rqmt_f';
4805 Raise l_rows_exist;
4806 End If;
4807 If (dt_api.rows_exist
4808 (p_base_table_name => 'ben_aplcn_to_bnft_pool_f',
4809 p_base_key_column => 'acty_base_rt_id',
4810 p_base_key_value => p_acty_base_rt_id,
4811 p_from_date => p_validation_start_date,
4812 p_to_date => p_validation_end_date)) Then
4813 l_table_name := 'ben_aplcn_to_bnft_pool_f';
4814 Raise l_rows_exist;
4815 End If;
4816 If (dt_api.rows_exist
4817 (p_base_table_name => 'ben_vrbl_rt_rl_f',
4818 p_base_key_column => 'acty_base_rt_id',
4819 p_base_key_value => p_acty_base_rt_id,
4820 p_from_date => p_validation_start_date,
4821 p_to_date => p_validation_end_date)) Then
4822 l_table_name := 'ben_vrbl_rt_rl_f';
4823 Raise l_rows_exist;
4824 End If;
4825 If (dt_api.rows_exist
4826 (p_base_table_name => 'ben_comp_lvl_acty_rt_f',
4827 p_base_key_column => 'acty_base_rt_id',
4828 p_base_key_value => p_acty_base_rt_id,
4829 p_from_date => p_validation_start_date,
4830 p_to_date => p_validation_end_date)) Then
4831 l_table_name := 'ben_comp_lvl_acty_rt_f';
4832 Raise l_rows_exist;
4833 End If;
4834 --
4835 End If;
4836 -- bug 3636162, added to check any variable rate records attatched to future
4837 -- updates. In that case DeleteNextChange or FutureChange's should not be allowed
4838 If (p_datetrack_mode = 'DELETE_NEXT_CHANGE' or p_datetrack_mode = 'FUTURE_CHANGE')
4839 and future_var_rt_recs_exist (
4840 p_acty_base_rt_id ,
4841 p_datetrack_mode ,
4842 p_validation_start_date ,
4843 p_validation_end_date )
4844 then
4845 l_table_name := 'ben_acty_vrbl_rt_f';
4846 Raise l_rows_exist;
4847 End If;
4848
4849
4850
4851 hr_utility.set_location(' Leaving:'||l_proc, 10);
4852 Exception
4853 When l_rows_exist Then
4854 --
4855 -- A referential integrity check was violated therefore
4856 -- we must error
4857 --
4858 ben_utility.child_exists_error(p_table_name => l_table_name);
4859 --
4860 When Others Then
4861 --
4862 -- An unhandled or unexpected error has occurred which
4863 -- we must report
4864 --
4865 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
4866 fnd_message.set_token('PROCEDURE', l_proc);
4867 fnd_message.set_token('STEP','15');
4868 fnd_message.raise_error;
4869 End dt_delete_validate;
4870 --
4871 -- ----------------------------------------------------------------------------
4872 -- |------< chk_mlt_cd_dependencies >------|
4873 -- ----------------------------------------------------------------------------
4874 --
4875 -- Description
4876 --
4877 --
4878 --
4879 -- In Parameters
4880 -- rt_mlt_cd
4881 -- val
4882 -- mn_elcn_val
4883 -- mx_elcn_val
4884 -- incrmt_elcn_val
4885 -- dflt_val
4886 -- rt_typ_cd
4887 -- bnft_rt_typ_cd
4888 -- val_calc_rl
4889 -- acty_base_rt_id
4890 -- effective_date
4891 -- object_version_number
4892 --
4893 --
4894 Procedure chk_mlt_cd_dependencies(p_rt_mlt_cd in varchar2,
4895 p_entr_val_at_enrt_flag in varchar2,
4896 p_entr_ann_val_flag in varchar2,
4897 p_val in number,
4898 p_mn_elcn_val in number,
4899 p_mx_elcn_val in number,
4900 p_incrmt_elcn_val in number,
4901 p_dflt_val in number,
4902 p_rt_typ_cd in varchar2,
4903 p_bnft_rt_typ_cd in varchar2,
4904 p_val_calc_rl in number,
4905 p_acty_base_rt_id in number,
4906 p_pay_rate_grade_rule_id in number,
4907 p_acty_typ_cd in varchar2,
4908 p_effective_date in date,
4909 p_object_version_number in number
4910 ) is
4911 --
4912 l_proc varchar2(72) := g_package||'chk_mlt_cd_dependencies';
4913 l_api_updating boolean;
4914 --
4915 Begin
4916 --
4917 hr_utility.set_location('Entering:'||l_proc, 5);
4918 --
4919 l_api_updating := ben_abr_shd.api_updating
4920 (p_acty_base_rt_id => p_acty_base_rt_id,
4921 p_effective_date => p_effective_date,
4922 p_object_version_number => p_object_version_number);
4923 --
4924 if (l_api_updating
4925 and
4926 (nvl(p_rt_mlt_cd,hr_api.g_varchar2)
4927 <> nvl(ben_abr_shd.g_old_rec.rt_mlt_cd,hr_api.g_varchar2) or
4928 nvl(p_val,hr_api.g_number)
4929 <> nvl(ben_abr_shd.g_old_rec.val,hr_api.g_number) or
4930 nvl(p_mn_elcn_val,hr_api.g_number)
4931 <> nvl(ben_abr_shd.g_old_rec.mn_elcn_val,hr_api.g_number) or
4932 nvl(p_mx_elcn_val,hr_api.g_number)
4933 <> nvl(ben_abr_shd.g_old_rec.mx_elcn_val,hr_api.g_number) or
4934 nvl(p_incrmt_elcn_val,hr_api.g_number)
4935 <> nvl(ben_abr_shd.g_old_rec.incrmt_elcn_val,hr_api.g_number) or
4936 -- begin bug 2282186
4937 nvl(p_dflt_val,hr_api.g_number)
4938 <> nvl(ben_abr_shd.g_old_rec.dflt_val,hr_api.g_number) or
4939 -- end bug 2282186
4940 nvl(p_rt_typ_cd,hr_api.g_varchar2)
4941 <> nvl(ben_abr_shd.g_old_rec.rt_typ_cd,hr_api.g_varchar2) or
4942 nvl(p_bnft_rt_typ_cd,hr_api.g_varchar2)
4943 <> nvl(ben_abr_shd.g_old_rec.bnft_rt_typ_cd,hr_api.g_varchar2) or
4944 nvl(p_val_calc_rl,hr_api.g_number)
4945 <> nvl(ben_abr_shd.g_old_rec.val_calc_rl,hr_api.g_number)
4946 )
4947 or
4948 not l_api_updating)
4949 then
4950 --
4951 if p_entr_val_at_enrt_flag = 'N'
4952 and p_rt_mlt_cd in ('FLFX', 'CVG', 'CL') then
4953 --
4954 if p_val is null then
4955 --
4956 fnd_message.set_name('BEN','BEN_91536_VAL_RQD');
4957 fnd_message.raise_error;
4958 --
4959 end if;
4960 --
4961 if p_mn_elcn_val is not null then
4962 --
4963 fnd_message.set_name('BEN','BEN_91539_MIN_VAL_SPEC');
4964 fnd_message.raise_error;
4965 --
4966 end if;
4967 --
4968 if p_mx_elcn_val is not null then
4969 --
4970 fnd_message.set_name('BEN','BEN_91541_MAX_VAL_SPEC');
4971 fnd_message.raise_error;
4972 --
4973 end if;
4974 --
4975 if p_incrmt_elcn_val is not null then
4976 --
4977 fnd_message.set_name('BEN','BEN_91543_INCRMT_VAL_SPEC');
4978 fnd_message.raise_error;
4982 if p_dflt_val is not null then
4979 --
4980 end if;
4981 --
4983 --
4984 -- fnd_message.set_name('BEN','BEN_91545_DFLT_VAL_SPEC'); -- Bug 2272978
4985 fnd_message.set_name('BEN','BEN_91551_DFLT_VAL_SPEC');
4986 fnd_message.raise_error;
4987 --
4988 end if;
4989 --
4990 elsif p_entr_val_at_enrt_flag = 'Y'
4991 and p_rt_mlt_cd in ('FLFX', 'CVG', 'CL') then
4992 --
4993 if p_val is not null then
4994 --
4995 fnd_message.set_name('BEN','BEN_91537_VAL_SPEC');
4996 fnd_message.raise_error;
4997 --
4998 end if;
4999 --
5000 if p_entr_ann_val_flag = 'N' and substr(p_acty_typ_cd,1,3) <> 'CWB' then
5001 --
5002 if p_mn_elcn_val is null then
5003 --
5004 fnd_message.set_name('BEN','BEN_91538_MIN_VAL_RQD');
5005 fnd_message.raise_error;
5006 --
5007 end if;
5008 --
5009 if p_mx_elcn_val is null then
5010 --
5011 fnd_message.set_name('BEN','BEN_91540_MAX_VAL_RQD');
5012 fnd_message.raise_error;
5013 --
5014 end if;
5015 --
5016 end if;
5017 --
5018 if p_incrmt_elcn_val is null and substr(p_acty_typ_cd,1,3) <> 'CWB' then
5019 --
5020 fnd_message.set_name('BEN','BEN_91542_INCRMT_VAL_RQD');
5021 fnd_message.raise_error;
5022 --
5023 end if;
5024 --
5025 if p_dflt_val is null and substr(p_acty_typ_cd,1,3) <> 'CWB' then
5026 --
5027 -- fnd_message.set_name('BEN','BEN_91544_DFLT_VAL_RQD');
5028 fnd_message.set_name('BEN','BEN_91550_DFLT_VAL_RQD'); -- Bug 2272978
5029 fnd_message.raise_error;
5030 --
5031 end if;
5032 --
5033
5034 -- begin bug 2282186
5035 if p_dflt_val < p_mn_elcn_val or p_dflt_val > p_mx_elcn_val then
5036 --
5037 fnd_message.set_name('PAY','HR_INPVAL_DEFAULT_INVALID');
5038 fnd_message.raise_error;
5039 --
5040 end if;
5041 -- end bug 2282186
5042
5043 elsif p_entr_val_at_enrt_flag = 'Y'
5044 and p_rt_mlt_cd in ('AP','PRNT','NSVU','RL','CLANDCVG',
5045 'APANDCVG','PRNTANDCVG') then
5046 --
5047 fnd_message.set_name('BEN','BEN_91973_ENTR_VAL_AT_ENRT');
5048 fnd_message.raise_error;
5049 --
5050 end if;
5051 --
5052 if p_rt_mlt_cd is NULL then
5053 --
5054 fnd_message.set_name('BEN','BEN_91535_MLT_CD_RQD');
5055 fnd_message.raise_error;
5056 --
5057 end if;
5058 --
5059 if p_val is NULL then
5060 --
5061 if p_rt_mlt_cd in ('AP','PRNT','CLANDCVG','APANDCVG',
5062 'PRNTANDCBG') then
5063 --
5064 fnd_message.set_name('BEN','BEN_91536_VAL_RQD');
5065 fnd_message.raise_error;
5066 --
5067 end if;
5068 --
5069 else
5070 --
5071 if p_rt_mlt_cd in ('RL','NSVU') then
5072 --
5073 fnd_message.set_name('BEN','BEN_91537_VAL_SPEC');
5074 fnd_message.raise_error;
5075 --
5076 end if;
5077 --
5078 end if;
5079 --
5080 if p_mn_elcn_val is not NULL then
5081 --
5082 if p_rt_mlt_cd in ('AP','PRNT','CLANDCVG','APANDCVG',
5083 'PRNTANDCVG','RL','NSVU') then
5084 --
5085 fnd_message.set_name('BEN','BEN_91539_MIN_VAL_SPEC');
5086 fnd_message.raise_error;
5087 --
5088 end if;
5089 --
5090 end if;
5091 --
5092 if p_mx_elcn_val is not NULL then
5093 --
5094 if p_rt_mlt_cd in ('AP','PRNT','CLANDCVG','APANDCVG',
5095 'PRNTANDCVG','RL','NSVU') then
5096 --
5097 fnd_message.set_name('BEN','BEN_91541_MAX_VAL_SPEC');
5098 fnd_message.raise_error;
5099 --
5100 end if;
5101 --
5102 end if;
5103 --
5104 if p_incrmt_elcn_val is not NULL then
5105 --
5106 if p_rt_mlt_cd in ('AP','PRNT','CLANDCVG','APANDCVG',
5107 'PRNTANDCVG','RL','NSVU') then
5108 --
5109 fnd_message.set_name('BEN','BEN_91543_INCRMT_VAL_SPEC');
5110 fnd_message.raise_error;
5111 --
5112 end if;
5113 --
5114 end if;
5115 --
5116 if p_dflt_val is not NULL then
5117 --
5118 if p_rt_mlt_cd in ('AP','PRNT','CLANDCVG','APANDCVG',
5119 'PRNTANDCVG','RL','NSVU') then
5120 --
5121 -- fnd_message.set_name('BEN','BEN_91545_DFLT_VAL_SPEC');
5122 fnd_message.set_name('BEN','BEN_91551_DFLT_VAL_SPEC'); -- Bug 2272978
5123 fnd_message.raise_error;
5124 --
5125 end if;
5126 --
5127 end if;
5128 --
5132 'PRNTANDCVG') then
5129 if p_rt_typ_cd is NULL then
5130 --
5131 if p_rt_mlt_cd in ('CL','AP','PRNT','CLANDCVG','APANDCVG',
5133 --
5134 fnd_message.set_name('BEN','BEN_91544_RT_TYP_CD_RQD');
5135 fnd_message.raise_error;
5136 --
5137 end if;
5138 else
5139 --
5140 if p_rt_mlt_cd in ('FLFX','CVG','RL','NSVU') then
5141 --
5142 fnd_message.set_name('BEN','BEN_91545_RT_TYP_CD_SPEC');
5143 fnd_message.raise_error;
5144 --
5145 end if;
5146 --
5147 end if;
5148 --
5149 if p_bnft_rt_typ_cd is NULL then
5150 --
5151 if p_rt_mlt_cd in ('CVG','CLANDCVG','APANDCVG','PRNTANDCVG') then
5152 --
5153 fnd_message.set_name('BEN','BEN_91546_BNFTS_TYP_CD_RQD');
5154 fnd_message.raise_error;
5155 --
5156 end if;
5157 --
5158 else
5159 --
5160 if p_rt_mlt_cd in ('FLFX','CL','AP','PRNT','RL','NSVU') then
5161 --
5162 fnd_message.set_name('BEN','BEN_91547_BNFTS_TYP_CD_SPEC');
5163 fnd_message.raise_error;
5164 --
5165 end if;
5166 --
5167 end if;
5168 --
5169 if p_val_calc_rl is NULL then
5170 --
5171 if p_rt_mlt_cd in ('RL') then
5172 --
5173 fnd_message.set_name('BEN','BEN_91548_VAL_CALC_RL_RQD');
5174 fnd_message.raise_error;
5175 --
5176 end if;
5177 --
5178 else
5179 --
5180 if p_rt_mlt_cd in ('FLFX','CL','AP','CVG','PRNT',
5181 'CLANDCVG','APANDCVG','PRNTANDCVG','NSVU') then
5182 --
5183 fnd_message.set_name('BEN','BEN_91549_VAL_CALC_RL_SPEC');
5184 fnd_message.raise_error;
5185 --
5186 end if;
5187 end if;
5188 --- GSP val is require when the mult cd is PRV
5189 if p_pay_rate_grade_rule_id is not null and p_rt_mlt_cd <> 'PRV' then
5190 fnd_message.set_name('BEN','BEN_91544_RT_TYP_CD_RQD');
5191 fnd_message.raise_error;
5192 end if ;
5193 --Rate by criteria
5194 if (p_acty_typ_cd ='RBC' and p_rt_mlt_cd <> 'NSVU') then
5195 fnd_message.set_name('BEN','BEN_94138_ACTY_RT_MLT_CD');
5196 fnd_message.raise_error;
5197 end if;
5198
5199 --
5200 end if;
5201 --
5202 hr_utility.set_location('Leaving:'||l_proc,10);
5203 --
5204 end chk_mlt_cd_dependencies;
5205 -- ----------------------------------------------------------------------------
5206 -- |---------------------< chk_mn_mx_rl >---------------------------|
5207 -- BUG # 3981982
5208 -- ----------------------------------------------------------------------------
5209 --
5210 -- Description
5211 -- This procedure is used to check that
5212 -- 1. p_mn_mx_elcn_rl is mutually exclusive to p_mn_elcn_val, p_mx_elcn_val, p_incrmt_elcn_val and p_dflt_val.
5213 -- 2. p_mn_mx_elcn_rl is NOT NULL only if
5214 -- p_entr_val_at_enrt_flag is Y.
5215 -- and
5216 -- p_ acty_typ_cd is in (CWBWS, CWBRA).
5217
5218 -- Pre Conditions
5219 -- None.
5220 --
5221 -- In Parameters
5222 -- p_acty_base_rt_id PK of record being inserted or updated.
5223 -- p_mn_mx_elcn_rl
5224 -- p_mn_elcn_val
5225 -- p_mx_elcn_val
5226 -- p_incrmt_elcn_val
5227 -- p_dflt_val
5228 -- p_entr_val_at_enrt_flag
5229 -- p_ acty_typ_cd
5230 -- Post Success
5231 -- Processing continues
5232 --
5233 -- Post Failure
5234 -- Error handled by procedure
5235 --
5236 -- Access Status
5237 -- Internal table handler use only.
5238 --
5239 Procedure chk_mn_mx_rl(p_acty_base_rt_id in number,
5240 p_mn_mx_elcn_rl in number,
5241 p_mn_elcn_val in number,
5242 p_mx_elcn_val in number,
5243 p_incrmt_elcn_val in number,
5244 p_dflt_val in number,
5245 p_entr_val_at_enrt_flag in varchar2,
5246 p_acty_typ_cd in varchar2
5247 ) is
5248 --
5249 l_proc varchar2(72) := g_package||'chk_mn_mx_rl';
5250 --
5251 Begin
5252 --
5253 hr_utility.set_location('Entering:'||l_proc, 5);
5254 --
5255 -- p_mn_mx_elcn_rl is mutually exclusive to p_mn_elcn_val, p_mx_elcn_val, p_incrmt_elcn_val and p_dflt_val.
5256 -- mutually exclusive.
5257 if ( p_mn_mx_elcn_rl is not null and p_mn_elcn_val is not null
5258 and p_mx_elcn_val is not null and p_incrmt_elcn_val is not null
5259 and p_dflt_val is not null)
5260 then
5261 --
5262 fnd_message.set_name('BEN','BEN_94128_MN_MX_VAL_AND_RL');
5263 fnd_message.raise_error;
5264 --
5265 end if;
5266 --
5267 --p_mn_mx_elcn_rl is NOT NULL only if
5268 --p_entr_val_at_enrt_flag is Y.
5269 --and
5270 --p_ acty_typ_cd is in (CWBWS, CWBRA)
5271
5272 if (p_mn_mx_elcn_rl is NOT NULL) then
5273 if (p_entr_val_at_enrt_flag <> 'Y' or p_acty_typ_cd not in ('CWBWS', 'CWBRA')) then
5274 --
5275 fnd_message.set_name('BEN','BEN_94129_MN_MX_RL_NOT_NULL');
5276 fnd_message.raise_error;
5277 --
5278 end if;
5279 end if;
5280
5281
5282 end chk_mn_mx_rl;
5283 --
5284 -- Added on Mar 14, 2006 against Bug:5091755
5285 --
5286 -- |------< chk_code_rule_num >------|
5290 -- This procedure is used to check that the Rule is only allowed to
5287 -- ----------------------------------------------------------------------------
5288 --
5289 -- Description
5291 -- have a value if the value of the Code = 'Rule', and if code is
5292 -- = RL then p_rule must have a value. If cd = 'WASHRULE' then num
5293 -- must have a value otherwise num must be null.
5294 --
5295 -- Pre Conditions
5296 -- None.
5297 --
5298 -- In Parameters
5299 -- P_CODE value of code item.
5300 -- P_RULE value of rule item
5301 -- P_NUM value of rule item
5302 --
5303 -- Post Success
5304 -- Processing continues
5305 --
5306 -- Post Failure
5307 -- Error handled by procedure
5308 --
5309 -- Access Status
5310 -- Internal table handler use only.
5311 --
5312
5313 PROCEDURE chk_code_rule_num (
5314 p_code IN VARCHAR2,
5315 p_num IN NUMBER,
5316 p_rule IN NUMBER
5317 )
5318 IS
5319 --
5320 l_proc VARCHAR2 (72) := g_package || 'chk_code_rule_num';
5321 --
5322 BEGIN
5323 --
5324 hr_utility.set_location ('Entering:' || l_proc, 5);
5325
5326 --
5327 IF NVL(p_code,'N') <> 'RL' AND p_rule IS NOT NULL
5328 THEN
5329 --
5330 fnd_message.set_name ('BEN', 'BEN_91624_CD_RL_2');
5331 fnd_message.raise_error;
5332 --
5333 ELSIF p_code = 'RL' AND p_rule IS NULL
5334 THEN
5335 --
5336 fnd_message.set_name ('BEN', 'BEN_91623_CD_RL_1');
5337 fnd_message.raise_error;
5338 --
5339 END IF;
5340
5341 --
5342 IF NVL(p_code,'N') <> 'WASHRULE' AND p_num IS NOT NULL
5343 THEN
5344 --
5345 fnd_message.set_name ('BEN', 'BEN_92270_NTWSHRL_NUM_NTNULL');
5346 fnd_message.raise_error;
5347 --
5348 ELSIF p_code = 'WASHRULE' AND p_num IS NULL
5349 THEN
5350 --
5351 fnd_message.set_name ('BEN', 'BEN_92271_NTWSHRL_NUM_NULL');
5352 fnd_message.raise_error;
5353 --
5354 END IF;
5355
5356 --
5357 hr_utility.set_location ('Leaving:' || l_proc, 10);
5358 --
5359 END chk_code_rule_num;
5360 --
5361 -- Change ends against Bug:5091755
5362
5363 --
5364 -- ----------------------------------------------------------------------------
5365 -- |---------------------------< insert_validate >----------------------------|
5366 -- ----------------------------------------------------------------------------
5367 Procedure insert_validate
5368 (p_rec in ben_abr_shd.g_rec_type,
5369 p_effective_date in date,
5370 p_datetrack_mode in varchar2,
5371 p_validation_start_date in date,
5372 p_validation_end_date in date) is
5373
5374 l_proc varchar2(72) := g_package||'insert_validate';
5375
5376 Begin
5377 hr_utility.set_location('Entering:'||l_proc, 5);
5378
5379 -- Call all supporting business operations
5380 --
5381
5382 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
5383
5384 chk_acty_base_rt_id(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5385 p_effective_date => p_effective_date,
5386 p_object_version_number => p_rec.object_version_number);
5387
5388 chk_name_unique
5389 ( p_acty_base_rt_id => p_rec.acty_base_rt_id
5390 ,p_name => p_rec.name
5391 ,p_business_group_id => p_rec.business_group_id);
5392 --ICM Changes
5393
5394 -- Added 'if' condition for Bug 6881417
5395 if(not ben_abr_bus.g_ssben_call) then
5396 chk_elem_unique
5397 ( p_acty_base_rt_id => p_rec.acty_base_rt_id
5398 ,p_effective_date => p_effective_date
5399 ,p_business_group_id => p_rec.business_group_id
5400 ,p_element_type_id => p_rec.element_type_id
5401 ,p_rt_mlt_cd => p_rec.rt_mlt_cd
5402 ,p_rec => p_rec);
5403 end if;
5404 --
5405 chk_acty_type
5406 (p_acty_base_rt_id => p_rec.acty_base_rt_id
5407 ,p_pl_id => p_rec.pl_id
5408 ,p_acty_typ_cd => p_rec.acty_typ_cd
5409 ,p_sub_acty_typ_cd => p_rec.sub_acty_typ_cd
5410 ,p_effective_start_date => p_effective_date
5411 ,p_business_group_id => p_rec.business_group_id
5412 ,p_plip_id => p_rec.plip_id );
5413
5414 chk_impted_incom_rate_unique
5415 ( p_acty_base_rt_id => p_rec.acty_base_rt_id
5416 ,p_pl_id => p_rec.pl_id
5417 ,p_rt_usg_cd => p_rec.rt_usg_cd
5418 ,p_effective_start_date => p_effective_date
5419 ,p_business_group_id => p_rec.business_group_id);
5420
5421 chk_all_rules(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5422 p_prtl_mo_det_mthd_rl => p_rec.prtl_mo_det_mthd_rl,
5423 p_prtl_mo_eff_dt_det_rl => p_rec.prtl_mo_eff_dt_det_rl,
5424 p_rndg_rl => p_rec.rndg_rl,
5425 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl,
5426 p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl,
5427 p_prtl_mo_det_mthd_cd => p_rec.prtl_mo_det_mthd_cd,
5428 p_prtl_mo_eff_dt_det_cd => p_rec.prtl_mo_eff_dt_det_cd,
5429 p_rndg_cd => p_rec.rndg_cd,
5430 p_val_calc_rl => p_rec.val_calc_rl,
5431 p_prort_mn_ann_elcn_val_rl => p_rec.prort_mn_ann_elcn_val_rl,
5432 p_prort_mx_ann_elcn_val_rl => p_rec.prort_mx_ann_elcn_val_rl,
5433 p_mn_mx_elcn_rl => p_rec.mn_mx_elcn_rl,
5434 p_element_det_rl => p_rec.element_det_rl,
5435 p_effective_date => p_effective_date,
5436 p_business_group_id => p_rec.business_group_id,
5440 p_use_to_calc_net_flx_cr_flag => p_rec.use_to_calc_net_flx_cr_flag,
5437 p_object_version_number => p_rec.object_version_number);
5438
5439 chk_all_flags(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5441 p_asn_on_enrt_flag => p_rec.asn_on_enrt_flag,
5442 p_abv_mx_elcn_val_alwd_flag => p_rec.abv_mx_elcn_val_alwd_flag,
5443 p_blw_mn_elcn_alwd_flag => p_rec.blw_mn_elcn_alwd_flag,
5444 p_dsply_on_enrt_flag => p_rec.dsply_on_enrt_flag,
5445 p_use_calc_acty_bs_rt_flag => p_rec.use_calc_acty_bs_rt_flag,
5446 p_uses_ded_sched_flag => p_rec.uses_ded_sched_flag,
5447 p_uses_varbl_rt_flag => p_rec.uses_varbl_rt_flag,
5448 p_vstg_sched_apls_fLag => p_rec.vstg_sched_apls_fLag,
5449 p_proc_each_pp_dflt_flag => p_rec.proc_each_pp_dflt_flag,
5450 p_prdct_flx_cr_when_elig_flag => p_rec.prdct_flx_cr_when_elig_flag,
5451 p_no_std_rt_used_flag => p_rec.no_std_rt_used_flag,
5452 p_uses_pymt_sched_flag => p_rec.uses_pymt_sched_flag,
5453 p_val_ovrid_alwd_flag => p_rec.val_ovrid_alwd_flag,
5454 p_no_mx_elcn_val_dfnd_flag => p_rec.no_mx_elcn_val_dfnd_flag ,
5455 p_no_mn_elcn_val_dfnd_flag => p_rec.no_mn_elcn_val_dfnd_flag ,
5456 p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag,
5457 p_entr_ann_val_flag => p_rec.entr_ann_val_flag,
5458 p_only_one_bal_typ_alwd_flag => p_rec.only_one_bal_typ_alwd_flag,
5459 p_rt_usg_cd => p_rec.rt_usg_cd,
5460 p_ele_rqd_flag => p_rec.ele_rqd_flag,
5461 p_subj_to_imptd_incm_flag => p_rec.subj_to_imptd_incm_flag,
5462 p_acty_typ_cd => p_rec.acty_typ_cd,
5463 p_business_group_id => p_rec.business_group_id,
5464 p_effective_date => p_effective_date,
5465 p_object_version_number => p_rec.object_version_number);
5466
5467 chk_lwr_lmt_val_and_rl
5468 (p_acty_base_rt_id => p_rec.acty_base_rt_id,
5469 p_lwr_lmt_val => p_rec.lwr_lmt_val,
5470 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl);
5471
5472 chk_upr_lwr_lmt_val
5473 (p_upr_lmt_val => p_rec.upr_lmt_val,
5474 p_lwr_lmt_val => p_rec.lwr_lmt_val);
5475
5476 chk_upr_lmt_val_and_rl
5477 (p_acty_base_rt_id => p_rec.acty_base_rt_id,
5478 p_upr_lmt_val => p_rec.upr_lmt_val,
5479 p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl);
5480
5481 chk_all_lookups(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5482 p_acty_typ_cd => p_rec.acty_typ_cd,
5483 p_rt_typ_cd => p_rec.rt_typ_cd,
5484 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
5485 p_tx_typ_cd => p_rec.tx_typ_cd,
5486 p_parnt_chld_cd => p_rec.parnt_chld_cd,
5487 p_rt_mlt_cd => p_rec.rt_mlt_cd,
5488 p_rcrrg_cd => p_rec.rcrrg_cd,
5489 p_rndg_cd => p_rec.rndg_cd,
5490 p_prtl_mo_det_mthd_cd => p_rec.prtl_mo_det_mthd_cd,
5491 p_acty_base_rt_stat_cd => p_rec.acty_base_rt_stat_cd,
5492 p_procg_src_cd => p_rec.procg_src_cd,
5493 p_frgn_erg_ded_typ_cd => p_rec.frgn_erg_ded_typ_cd,
5494 p_prtl_mo_eff_dt_det_cd => p_rec.prtl_mo_eff_dt_det_cd,
5495 p_rt_usg_cd => p_rec.rt_usg_cd,
5496 p_prort_mn_ann_elcn_val_cd => p_rec.prort_mn_ann_elcn_val_cd,
5497 p_prort_mx_ann_elcn_val_cd => p_rec.prort_mx_ann_elcn_val_cd,
5498 p_one_ann_pymt_cd => p_rec.one_ann_pymt_cd,
5499 p_det_pl_ytd_cntrs_cd => p_rec.det_pl_ytd_cntrs_cd,
5500 p_asmt_to_use_cd => p_rec.asmt_to_use_cd,
5501 p_currency_det_cd => p_rec.currency_det_cd,
5502 p_effective_date => p_effective_date,
5503 p_object_version_number => p_rec.object_version_number);
5504
5505 chk_estonly_no_ptd_fctr
5506 (p_ptd_comp_lvl_fctr_id => p_rec.ptd_comp_lvl_fctr_id,
5507 p_det_pl_ytd_cntrs_cd => p_rec.det_pl_ytd_cntrs_cd);
5508
5509 -- Added on Mar 14, 2006 against Bug:5091755
5510 chk_code_rule_num
5511 (p_code => p_rec.prtl_mo_det_mthd_cd,
5512 p_num => p_rec.wsh_rl_dy_mo_num,
5513 p_rule => p_rec.prtl_mo_det_mthd_rl);
5514 -- Change ends against Bug:5091755
5515 chk_prtl_mo_det_mthd_cd_rl
5516 (p_prtl_mo_det_mthd_cd => p_rec.prtl_mo_det_mthd_cd,
5517 p_prtl_mo_det_mthd_rl => p_rec.prtl_mo_det_mthd_rl);
5518
5519 chk_rndg_cd_rl
5520 (p_rndg_cd => p_rec.rndg_cd,
5521 p_rndg_rl => p_rec.rndg_rl);
5522
5523 chk_ann_rts (p_prort_mn_ann_elcn_val_cd => p_rec.prort_mn_ann_elcn_val_cd
5524 ,p_prort_mn_ann_elcn_val_rl => p_rec.prort_mn_ann_elcn_val_rl
5525 ,p_prort_mx_ann_elcn_val_cd => p_rec.prort_mx_ann_elcn_val_cd
5526 ,p_prort_mx_ann_elcn_val_rl => p_rec.prort_mx_ann_elcn_val_rl
5527 ,p_clm_comp_lvl_fctr_id => p_rec.clm_comp_lvl_fctr_id
5528 ,p_ptd_comp_lvl_fctr_id => p_rec.ptd_comp_lvl_fctr_id
5529 ,p_entr_ann_val_flag => p_rec.entr_ann_val_flag
5530 ,p_rt_mlt_cd => p_rec.rt_mlt_cd ) ;
5531
5532 chk_nnmntry_uom(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5533 p_nnmntry_uom => p_rec.nnmntry_uom,
5534 p_effective_date => p_effective_date,
5535 p_object_version_number => p_rec.object_version_number);
5536
5537 /* chk_organization_id(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5538 p_organization_id => p_rec.organization_id,
5542 chk_pgm_typ_code
5539 p_effective_date => p_effective_date,
5540 p_object_version_number => p_rec.object_version_number); */
5541
5543 (p_pgm_id => p_rec.pgm_id
5544 ,p_ptip_id => p_rec.ptip_id
5545 ,p_plip_id => p_rec.plip_id
5546 ,p_rt_usg_cd => p_rec.rt_usg_cd
5547 ,p_business_group_id => p_rec.business_group_id
5548 ,p_effective_date => p_effective_date );
5549
5550 chk_only_one_id (p_pgm_id => p_rec.pgm_id,
5551 p_oipl_id => p_rec.oipl_id,
5552 p_opt_id => p_rec.opt_id,
5553 p_oiplip_id => p_rec.oiplip_id,
5554 p_plip_id => p_rec.plip_id,
5555 p_ptip_id => p_rec.ptip_id,
5556 p_pl_id => p_rec.pl_id,
5557 p_cmbn_plip_id => p_rec.cmbn_plip_id,
5558 p_cmbn_ptip_id => p_rec.cmbn_ptip_id,
5559 p_cmbn_ptip_opt_id => p_rec.cmbn_ptip_opt_id
5560 );
5561 chk_actual_premium_asnmt_cd
5562 (p_actl_prem_id => p_rec.actl_prem_id,
5563 p_acty_base_rt_id => p_rec.acty_base_rt_id,
5564 p_effective_date => p_effective_date,
5565 p_business_group_id => p_rec.business_group_id);
5566
5567 chk_mlt_cd_dependencies
5568 (p_rt_mlt_cd => p_rec.rt_mlt_cd,
5569 p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag,
5570 p_entr_ann_val_flag => p_rec.entr_ann_val_flag,
5571 p_val => p_rec.val,
5572 p_mn_elcn_val => p_rec.mn_elcn_val,
5573 p_mx_elcn_val => p_rec.mx_elcn_val,
5574 p_incrmt_elcn_val => p_rec.incrmt_elcn_val,
5575 p_dflt_val => p_rec.dflt_val,
5576 p_rt_typ_cd => p_rec.rt_typ_cd,
5577 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
5578 p_val_calc_rl => p_rec.val_calc_rl,
5579 p_acty_base_rt_id => p_rec.acty_base_rt_id,
5580 p_pay_rate_grade_rule_id => p_rec.pay_rate_grade_rule_id,
5581 p_acty_typ_cd => p_rec.acty_typ_cd,
5582 p_effective_date => p_effective_date,
5583 p_object_version_number => p_rec.object_version_number
5584 );
5585
5586 chk_elmt_typ_input_val_rqd
5587 (p_rec => p_rec,
5588 p_acty_base_rt_stat_cd => p_rec.acty_base_rt_stat_cd,
5589 p_input_value_id => p_rec.input_value_id,
5590 p_ele_rqd_flag => p_rec.ele_rqd_flag,
5591 p_effective_date => p_effective_date,
5592 p_element_type_id => p_rec.element_type_id);
5593
5594
5595 chk_prtl_mo_det_mthd_cd
5596 (p_prtl_mo_det_mthd_cd => p_rec.prtl_mo_det_mthd_cd,
5597 p_ele_entry_val_cd => p_rec.ele_entry_val_cd);
5598
5599 chk_entr_ann_val_flag
5600 (p_entr_ann_val_flag => p_rec.entr_ann_val_flag,
5601 p_ann_mn_elcn_val => p_rec.ann_mn_elcn_val,
5602 p_ann_mx_elcn_val => p_rec.ann_mx_elcn_val,
5603 p_mn_elcn_val => p_rec.mn_elcn_val,
5604 p_mx_elcn_val => p_rec.mx_elcn_val,
5605 p_det_pl_ytd_cntrs_cd => p_rec.det_pl_ytd_cntrs_cd);
5606
5607
5608
5609 chk_entr_at_enrt_with_cvg(p_rt_mlt_cd => p_rec.rt_mlt_cd ,
5610 p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag ,
5611 p_pl_id => p_rec.pl_id ,
5612 p_plip_id => p_rec.plip_id ,
5613 p_oipl_id => p_rec.oipl_id ,
5614 p_oiplip_id => p_rec.oiplip_id ,
5615 p_effective_date => p_effective_date ) ;
5616
5617 if p_rec.dsply_on_enrt_flag = 'Y' then
5618 /* Calling the procedure only when the dsply_on_enrt_flag is Y. */
5619 chk_ordr_num(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5620 p_effective_date => p_effective_date,
5621 p_object_version_number => p_rec.object_version_number,
5622 p_business_group_id => p_rec.business_group_id,
5623 p_pl_id => p_rec.pl_id,
5624 p_plip_id => p_rec.plip_id ,
5625 p_oipl_id => p_rec.oipl_id ,
5626 p_opt_id => p_rec.opt_id,
5627 p_oiplip_id => p_rec.oiplip_id ,
5628 p_ordr_num => p_rec.ordr_num);
5629
5630 end if;
5631
5632 --
5633 --Bug 3460673
5634 if p_rec.ENTR_VAL_AT_ENRT_FLAG = 'Y' then
5635 chk_incr_val_less_than_max_val (p_incrmt_elcn_val => p_rec.incrmt_elcn_val,
5636 p_mx_elcn_val => p_rec.mx_elcn_val,
5637 p_ann_mx_elcn_val => p_rec.ann_mx_elcn_val);
5638 end if;
5639 --Bug 3460673
5640 --
5641
5642
5643
5644 --
5645 -- CWB Changes Bug 2275257
5646 --
5647 if p_rec.acty_typ_cd is not null and p_rec.acty_typ_cd like 'CWB%'
5648 then
5649 chk_cwb_acty_typ_cd_unique(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5650 p_acty_typ_cd => p_rec.acty_typ_cd,
5651 p_pl_id => p_rec.pl_id,
5652 p_oipl_id => p_rec.oipl_id,
5653 p_acty_base_rt_stat_cd => p_rec.acty_base_rt_stat_cd,
5654 p_business_group_id => p_rec.business_group_id,
5655 p_effective_date => p_effective_date,
5656 p_object_version_number => p_rec.object_version_number);
5657 end if;
5658
5659
5660 if p_rec.subj_to_imptd_incm_flag = 'Y' then
5664 p_oipl_id => p_rec.oipl_id,
5661 chk_subj_to_imptd_incm(
5662 p_acty_base_rt_id => p_rec.acty_base_rt_id,
5663 p_pl_id => p_rec.pl_id,
5665 p_plip_id => p_rec.plip_id,
5666 p_oiplip_id => p_rec.oiplip_id,
5667 p_acty_typ_cd => p_rec.acty_typ_cd ,
5668 p_tx_typ_cd => p_rec.tx_typ_cd ,
5669 p_subj_to_imptd_incm_flag => p_rec.subj_to_imptd_incm_flag,
5670 p_effective_date => p_effective_date ) ;
5671
5672
5673 end if ;
5674
5675 if p_rec.pay_rate_grade_rule_id is not null then
5676 chk_pay_rate_grade_rule_id(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5677 p_pay_rate_grade_rule_id => p_rec.pay_rate_grade_rule_id,
5678 p_pl_id => p_rec.pl_id ,
5679 p_opt_id => p_rec.opt_id,
5680 p_business_group_id => p_rec.business_group_id,
5681 p_effective_date => p_effective_date,
5682 p_object_version_number => p_rec.object_version_number) ;
5683 end if ;
5684
5685 --muky
5686 if p_rec.acty_typ_cd is not null and p_rec.acty_typ_cd like 'GSPSA'
5687 then
5688 chk_acty_typ_cd_gsp
5689 (p_acty_base_rt_id => p_rec.acty_base_rt_id,
5690 --p_pgm_id => p_rec.pgm_id ,
5691 p_pl_id => p_rec.pl_id ,
5692 /*p_opt_id => p_rec.opt_id ,
5693 p_plip_id => p_rec.plip_id ,
5694 p_oipl_id => p_rec.oipl_id ,*/
5695 p_acty_typ_cd => p_rec.acty_typ_cd,
5696 p_effective_date => p_effective_date,
5697 p_business_group_id => p_rec.business_group_id,
5698 p_object_version_number => p_rec.object_version_number);
5699 end if;
5700
5701 chk_mn_mx_rl
5702 ( p_acty_base_rt_id => p_rec.acty_base_rt_id
5703 ,p_mn_mx_elcn_rl => p_rec.mn_mx_elcn_rl
5704 ,p_mn_elcn_val => p_rec.mn_elcn_val
5705 ,p_mx_elcn_val => p_rec.mx_elcn_val
5706 ,p_incrmt_elcn_val => p_rec.incrmt_elcn_val
5707 ,p_dflt_val => p_rec.dflt_val
5708 ,p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag
5709 ,p_acty_typ_cd => p_rec.acty_typ_cd);
5710 -- CWB
5711 chk_cwb_element_currency
5712 (p_element_det_rl => p_rec.element_det_rl,
5713 p_currency_det_cd => p_rec.currency_det_cd,
5714 p_acty_typ_cd => p_rec.acty_typ_cd ) ;
5715 --
5716 chk_abr_seq_num
5717 (p_pl_id => p_rec.pl_id,
5718 p_oipl_id => p_rec.oipl_id,
5719 p_opt_id => p_rec.opt_id,
5720 p_abr_seq_num => p_rec.abr_seq_num,
5721 p_effective_start_date => p_effective_date,
5722 p_business_group_id => p_rec.business_group_id);
5723
5724 --
5725 End insert_validate;
5726
5727 -- ----------------------------------------------------------------------------
5728 -- |---------------------------< update_validate >----------------------------|
5729 -- ----------------------------------------------------------------------------
5730 Procedure update_validate
5731 (p_rec in ben_abr_shd.g_rec_type,
5732 p_effective_date in date,
5733 p_datetrack_mode in varchar2,
5734 p_validation_start_date in date,
5735 p_validation_end_date in date) is
5736 --
5737 l_proc varchar2(72) := g_package||'update_validate';
5738 --
5739 Begin
5740 hr_utility.set_location('Entering:'||l_proc, 5);
5741 --
5742 -- Call all supporting business operations
5743 --
5744 --
5745 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
5746 --
5747 -- Call the datetrack update integrity operation
5748 --
5749
5750 chk_acty_base_rt_id(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5751 p_effective_date => p_effective_date,
5752 p_object_version_number => p_rec.object_version_number);
5753
5754 chk_name_unique
5755 ( p_acty_base_rt_id => p_rec.acty_base_rt_id
5756 ,p_name => p_rec.name
5757 ,p_business_group_id => p_rec.business_group_id);
5758 --ICM Changes
5759 chk_elem_unique
5760 ( p_acty_base_rt_id => p_rec.acty_base_rt_id
5761 ,p_effective_date => p_effective_date
5762 ,p_business_group_id => p_rec.business_group_id
5763 ,p_element_type_id => p_rec.element_type_id
5764 ,p_rt_mlt_cd => p_rec.rt_mlt_cd
5765 ,p_rec => p_rec);
5766
5767 chk_acty_type
5768 (p_acty_base_rt_id => p_rec.acty_base_rt_id
5769 ,p_pl_id => p_rec.pl_id
5770 ,p_acty_typ_cd => p_rec.acty_typ_cd
5771 ,p_sub_acty_typ_cd => p_rec.sub_acty_typ_cd
5772 ,p_effective_start_date => p_effective_date
5773 ,p_business_group_id => p_rec.business_group_id
5774 ,p_plip_id => p_rec.plip_id );
5775
5776 chk_impted_incom_rate_unique
5777 ( p_acty_base_rt_id => p_rec.acty_base_rt_id
5778 ,p_pl_id => p_rec.pl_id
5779 ,p_rt_usg_cd => p_rec.rt_usg_cd
5780 ,p_effective_start_date => p_effective_date
5781 ,p_business_group_id => p_rec.business_group_id);
5785 p_prtl_mo_eff_dt_det_rl => p_rec.prtl_mo_eff_dt_det_rl,
5782
5783 chk_all_rules(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5784 p_prtl_mo_det_mthd_rl => p_rec.prtl_mo_det_mthd_rl,
5786 p_rndg_rl => p_rec.rndg_rl,
5787 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl,
5788 p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl,
5789 p_val_calc_rl => p_rec.val_calc_rl,
5790 p_prtl_mo_det_mthd_cd => p_rec.prtl_mo_det_mthd_cd,
5791 p_prtl_mo_eff_dt_det_cd => p_rec.prtl_mo_eff_dt_det_cd,
5792 p_rndg_cd => p_rec.rndg_cd,
5793 p_prort_mn_ann_elcn_val_rl => p_rec.prort_mn_ann_elcn_val_rl,
5794 p_prort_mx_ann_elcn_val_rl => p_rec.prort_mx_ann_elcn_val_rl,
5795 p_mn_mx_elcn_rl => p_rec.mn_mx_elcn_rl,
5796 p_element_det_rl => p_rec.element_det_rl,
5797 p_effective_date => p_effective_date,
5798 p_business_group_id => p_rec.business_group_id,
5799 p_object_version_number => p_rec.object_version_number);
5800
5801 chk_all_flags(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5802 p_use_to_calc_net_flx_cr_flag => p_rec.use_to_calc_net_flx_cr_flag,
5803 p_asn_on_enrt_flag => p_rec.asn_on_enrt_flag,
5804 p_abv_mx_elcn_val_alwd_flag => p_rec.abv_mx_elcn_val_alwd_flag,
5805 p_blw_mn_elcn_alwd_flag => p_rec.blw_mn_elcn_alwd_flag,
5806 p_dsply_on_enrt_flag => p_rec.dsply_on_enrt_flag,
5807 p_use_calc_acty_bs_rt_flag => p_rec.use_calc_acty_bs_rt_flag,
5808 p_uses_ded_sched_flag => p_rec.uses_ded_sched_flag,
5809 p_uses_varbl_rt_flag => p_rec.uses_varbl_rt_flag,
5810 p_vstg_sched_apls_fLag => p_rec.vstg_sched_apls_fLag,
5811 p_proc_each_pp_dflt_flag => p_rec.proc_each_pp_dflt_flag,
5812 p_prdct_flx_cr_when_elig_flag => p_rec.prdct_flx_cr_when_elig_flag,
5813 p_no_std_rt_used_flag => p_rec.no_std_rt_used_flag,
5814 p_uses_pymt_sched_flag => p_rec.uses_pymt_sched_flag,
5815 p_val_ovrid_alwd_flag => p_rec.val_ovrid_alwd_flag,
5816 p_no_mx_elcn_val_dfnd_flag => p_rec.no_mx_elcn_val_dfnd_flag ,
5817 p_no_mn_elcn_val_dfnd_flag => p_rec.no_mn_elcn_val_dfnd_flag ,
5818 p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag,
5819 p_entr_ann_val_flag => p_rec.entr_ann_val_flag,
5820 p_only_one_bal_typ_alwd_flag => p_rec.only_one_bal_typ_alwd_flag,
5821 p_rt_usg_cd => p_rec.rt_usg_cd,
5822 p_ele_rqd_flag => p_rec.ele_rqd_flag,
5823 p_subj_to_imptd_incm_flag => p_rec.subj_to_imptd_incm_flag,
5824 p_acty_typ_cd => p_rec.acty_typ_cd,
5825 p_business_group_id => p_rec.business_group_id,
5826 p_effective_date => p_effective_date,
5827 p_object_version_number => p_rec.object_version_number);
5828
5829 chk_lwr_lmt_val_and_rl
5830 (p_acty_base_rt_id => p_rec.acty_base_rt_id,
5831 p_lwr_lmt_val => p_rec.lwr_lmt_val,
5832 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl);
5833
5834 chk_upr_lwr_lmt_val
5835 (p_upr_lmt_val => p_rec.upr_lmt_val,
5836 p_lwr_lmt_val => p_rec.lwr_lmt_val);
5837
5838 chk_upr_lmt_val_and_rl
5839 (p_acty_base_rt_id => p_rec.acty_base_rt_id,
5840 p_upr_lmt_val => p_rec.upr_lmt_val,
5841 p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl);
5842
5843 chk_all_lookups(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5844 p_acty_typ_cd => p_rec.acty_typ_cd,
5845 p_rt_typ_cd => p_rec.rt_typ_cd,
5846 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
5847 p_tx_typ_cd => p_rec.tx_typ_cd,
5848 p_parnt_chld_cd => p_rec.parnt_chld_cd,
5849 p_rt_mlt_cd => p_rec.rt_mlt_cd,
5850 p_rcrrg_cd => p_rec.rcrrg_cd,
5851 p_rndg_cd => p_rec.rndg_cd,
5852 p_prtl_mo_det_mthd_cd => p_rec.prtl_mo_det_mthd_cd,
5853 p_acty_base_rt_stat_cd => p_rec.acty_base_rt_stat_cd,
5854 p_procg_src_cd => p_rec.procg_src_cd,
5855 p_frgn_erg_ded_typ_cd => p_rec.frgn_erg_ded_typ_cd,
5856 p_prtl_mo_eff_dt_det_cd => p_rec.prtl_mo_eff_dt_det_cd,
5857 p_rt_usg_cd => p_rec.rt_usg_cd,
5858 p_prort_mn_ann_elcn_val_cd => p_rec.prort_mn_ann_elcn_val_cd,
5859 p_prort_mx_ann_elcn_val_cd => p_rec.prort_mx_ann_elcn_val_cd,
5860 p_one_ann_pymt_cd => p_rec.one_ann_pymt_cd,
5861 p_det_pl_ytd_cntrs_cd => p_rec.det_pl_ytd_cntrs_cd,
5862 p_asmt_to_use_cd => p_rec.asmt_to_use_cd,
5863 p_currency_det_cd => p_rec.currency_det_cd,
5864 p_effective_date => p_effective_date,
5865 p_object_version_number => p_rec.object_version_number);
5866
5867 chk_estonly_no_ptd_fctr
5868 (p_ptd_comp_lvl_fctr_id => p_rec.ptd_comp_lvl_fctr_id,
5869 p_det_pl_ytd_cntrs_cd => p_rec.det_pl_ytd_cntrs_cd);
5870
5871 -- Added on Mar 14, 2006 against Bug:5091755
5872 chk_code_rule_num
5873 (p_code => p_rec.prtl_mo_det_mthd_cd,
5874 p_num => p_rec.wsh_rl_dy_mo_num,
5875 p_rule => p_rec.prtl_mo_det_mthd_rl);
5876 -- Change ends against Bug:5091755
5877 chk_prtl_mo_det_mthd_cd_rl
5878 (p_prtl_mo_det_mthd_cd => p_rec.prtl_mo_det_mthd_cd,
5879 p_prtl_mo_det_mthd_rl => p_rec.prtl_mo_det_mthd_rl);
5880
5881 chk_rndg_cd_rl
5885 chk_ann_rts (p_prort_mn_ann_elcn_val_cd => p_rec.prort_mn_ann_elcn_val_cd
5882 (p_rndg_cd => p_rec.rndg_cd,
5883 p_rndg_rl => p_rec.rndg_rl);
5884
5886 ,p_prort_mn_ann_elcn_val_rl => p_rec.prort_mn_ann_elcn_val_rl
5887 ,p_prort_mx_ann_elcn_val_cd => p_rec.prort_mx_ann_elcn_val_cd
5888 ,p_prort_mx_ann_elcn_val_rl => p_rec.prort_mx_ann_elcn_val_rl
5889 ,p_clm_comp_lvl_fctr_id => p_rec.clm_comp_lvl_fctr_id
5890 ,p_ptd_comp_lvl_fctr_id => p_rec.ptd_comp_lvl_fctr_id
5891 ,p_entr_ann_val_flag => p_rec.entr_ann_val_flag
5892 ,p_rt_mlt_cd => p_rec.rt_mlt_cd ) ;
5893
5894 chk_nnmntry_uom(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5895 p_nnmntry_uom => p_rec.nnmntry_uom,
5896 p_effective_date => p_effective_date,
5897 p_object_version_number => p_rec.object_version_number);
5898
5899 /* chk_organization_id(p_acty_base_rt_id => p_rec.acty_base_rt_id,
5900 p_organization_id => p_rec.organization_id,
5901 p_effective_date => p_effective_date,
5902 p_object_version_number => p_rec.object_version_number); */
5903
5904
5905 chk_pgm_typ_code
5906 ( p_pgm_id => p_rec.pgm_id
5907 ,p_ptip_id => p_rec.ptip_id
5908 ,p_plip_id => p_rec.plip_id
5909 ,p_rt_usg_cd => p_rec.rt_usg_cd
5910 ,p_business_group_id => p_rec.business_group_id
5911 ,p_effective_date => p_effective_date );
5912
5913
5914 chk_only_one_id (p_pgm_id => p_rec.pgm_id,
5915 p_oipl_id => p_rec.oipl_id,
5916 p_opt_id => p_rec.opt_id,
5917 p_oiplip_id => p_rec.oiplip_id,
5918 p_plip_id => p_rec.plip_id,
5919 p_ptip_id => p_rec.ptip_id,
5920 p_pl_id => p_rec.pl_id,
5921 p_cmbn_plip_id => p_rec.cmbn_plip_id,
5922 p_cmbn_ptip_id => p_rec.cmbn_ptip_id,
5923 p_cmbn_ptip_opt_id => p_rec.cmbn_ptip_opt_id
5924 );
5925 chk_actual_premium_asnmt_cd
5926 (p_actl_prem_id => p_rec.actl_prem_id,
5927 p_acty_base_rt_id => p_rec.acty_base_rt_id,
5928 p_effective_date => p_effective_date,
5929 p_business_group_id => p_rec.business_group_id);
5930
5931 dt_update_validate
5932 (p_prtl_mo_det_mthd_rl => p_rec.prtl_mo_det_mthd_rl,
5933 p_prtl_mo_eff_dt_det_rl => p_rec.prtl_mo_eff_dt_det_rl,
5934 p_rndg_rl => p_rec.rndg_rl,
5935 p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl,
5936 p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl,
5937 p_val_calc_rl => p_rec.val_calc_rl,
5938 p_vstg_for_acty_rt_id => p_rec.vstg_for_acty_rt_id,
5939 p_actl_prem_id => p_rec.actl_prem_id,
5940 p_parnt_acty_base_rt_id => p_rec.parnt_acty_base_rt_id,
5941 p_pgm_id => p_rec.pgm_id,
5942 p_ptip_id => p_rec.ptip_id,
5943 p_oipl_id => p_rec.oipl_id,
5944 p_opt_id => p_rec.opt_id,
5945 p_plip_id => p_rec.plip_id,
5946 p_oiplip_id => p_rec.oiplip_id,
5947 p_pl_id => p_rec.pl_id,
5948 -- bug start
5949 p_element_type_id => p_rec.element_type_id,
5950 p_input_va_calc_rl => p_rec.input_va_calc_rl,
5951 p_element_det_rl => p_rec.element_det_rl,
5952 -- bug end
5953 p_datetrack_mode => p_datetrack_mode,
5954 p_validation_start_date => p_validation_start_date,
5955 p_validation_end_date => p_validation_end_date);
5956
5957
5958 chk_mlt_cd_dependencies
5959 (p_rt_mlt_cd => p_rec.rt_mlt_cd,
5960 p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag,
5961 p_entr_ann_val_flag => p_rec.entr_ann_val_flag,
5962 p_val => p_rec.val,
5963 p_mn_elcn_val => p_rec.mn_elcn_val,
5964 p_mx_elcn_val => p_rec.mx_elcn_val,
5965 p_incrmt_elcn_val => p_rec.incrmt_elcn_val,
5966 p_dflt_val => p_rec.dflt_val,
5967 p_rt_typ_cd => p_rec.rt_typ_cd,
5968 p_bnft_rt_typ_cd => p_rec.bnft_rt_typ_cd,
5969 p_val_calc_rl => p_rec.val_calc_rl,
5970 p_acty_base_rt_id => p_rec.acty_base_rt_id,
5971 p_pay_rate_grade_rule_id => p_rec.pay_rate_grade_rule_id,
5972 p_acty_typ_cd => p_rec.acty_typ_cd,
5973 p_effective_date => p_effective_date,
5974 p_object_version_number => p_rec.object_version_number
5975 );
5976
5977 chk_elmt_typ_input_val_rqd
5978 (p_rec => p_rec,
5979 p_acty_base_rt_stat_cd => p_rec.acty_base_rt_stat_cd,
5980 p_input_value_id => p_rec.input_value_id,
5981 p_ele_rqd_flag => p_rec.ele_rqd_flag,
5982 p_effective_date => p_effective_date,
5983 p_element_type_id => p_rec.element_type_id);
5984
5985 chk_prtl_mo_det_mthd_cd
5986 (p_prtl_mo_det_mthd_cd => p_rec.prtl_mo_det_mthd_cd,
5987 p_ele_entry_val_cd => p_rec.ele_entry_val_cd);
5988
5989 chk_entr_ann_val_flag
5990 (p_entr_ann_val_flag => p_rec.entr_ann_val_flag,
5991 p_ann_mn_elcn_val => p_rec.ann_mn_elcn_val,
5992 p_ann_mx_elcn_val => p_rec.ann_mx_elcn_val,
5993 p_mn_elcn_val => p_rec.mn_elcn_val,
5997 chk_entr_at_enrt_with_cvg(p_rt_mlt_cd => p_rec.rt_mlt_cd ,
5994 p_mx_elcn_val => p_rec.mx_elcn_val,
5995 p_det_pl_ytd_cntrs_cd => p_rec.det_pl_ytd_cntrs_cd);
5996
5998 p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag ,
5999 p_pl_id => p_rec.pl_id ,
6000 p_plip_id => p_rec.plip_id ,
6001 p_oipl_id => p_rec.oipl_id ,
6002 p_oiplip_id => p_rec.oiplip_id ,
6003 p_effective_date => p_effective_date ) ;
6004
6005 if p_rec.subj_to_imptd_incm_flag = 'Y' then
6006 chk_subj_to_imptd_incm(
6007 p_acty_base_rt_id => p_rec.acty_base_rt_id,
6008 p_pl_id => p_rec.pl_id,
6009 p_oipl_id => p_rec.oipl_id,
6010 p_plip_id => p_rec.plip_id,
6011 p_oiplip_id => p_rec.oiplip_id,
6012 p_acty_typ_cd => p_rec.acty_typ_cd ,
6013 p_tx_typ_cd => p_rec.tx_typ_cd ,
6014 p_subj_to_imptd_incm_flag => p_rec.subj_to_imptd_incm_flag,
6015 p_effective_date => p_effective_date ) ;
6016
6017 end if ;
6018
6019 if p_rec.dsply_on_enrt_flag = 'Y' then
6020 /* Calling the procedure only when the dsply_on_enrt_flag is Y. */
6021 chk_ordr_num(p_acty_base_rt_id => p_rec.acty_base_rt_id,
6022 p_effective_date => p_effective_date,
6023 p_object_version_number => p_rec.object_version_number,
6024 p_business_group_id => p_rec.business_group_id,
6025 p_pl_id => p_rec.pl_id,
6026 p_plip_id => p_rec.plip_id ,
6027 p_oipl_id => p_rec.oipl_id ,
6028 p_opt_id => p_rec.opt_id,
6029 p_oiplip_id => p_rec.oiplip_id ,
6030 p_ordr_num => p_rec.ordr_num);
6031
6032 end if;
6033
6034 --
6035 --Bug 3460673
6036 if p_rec.ENTR_VAL_AT_ENRT_FLAG = 'Y' then
6037 chk_incr_val_less_than_max_val (p_incrmt_elcn_val => p_rec.incrmt_elcn_val,
6038 p_mx_elcn_val => p_rec.mx_elcn_val,
6039 p_ann_mx_elcn_val => p_rec.ann_mx_elcn_val);
6040 end if;
6041 --Bug 3460673
6042 --
6043
6044 --
6045 -- CWB Changes Bug 2275257
6046 --
6047 if p_rec.acty_typ_cd is not null and p_rec.acty_typ_cd like 'CWB%'
6048 then
6049 chk_cwb_acty_typ_cd_unique(p_acty_base_rt_id => p_rec.acty_base_rt_id,
6050 p_acty_typ_cd => p_rec.acty_typ_cd,
6051 p_pl_id => p_rec.pl_id,
6052 p_oipl_id => p_rec.oipl_id,
6053 p_acty_base_rt_stat_cd => p_rec.acty_base_rt_stat_cd,
6054 p_business_group_id => p_rec.business_group_id,
6055 p_effective_date => p_effective_date,
6056 p_object_version_number => p_rec.object_version_number);
6057 end if;
6058
6059 --
6060 -- ABSE Changes
6061 --
6062 chk_ext_inp_values(p_acty_base_rt_id => p_rec.acty_base_rt_id,
6063 p_input_va_calc_rl => p_rec.input_va_calc_rl,
6064 p_object_version_number => p_rec.object_version_number,
6065 p_effective_date => p_effective_date);
6066
6067 if p_rec.pay_rate_grade_rule_id is not null then
6068 chk_pay_rate_grade_rule_id(p_acty_base_rt_id => p_rec.acty_base_rt_id,
6069 p_pay_rate_grade_rule_id => p_rec.pay_rate_grade_rule_id,
6070 p_pl_id => p_rec.pl_id ,
6071 p_opt_id => p_rec.opt_id,
6072 p_business_group_id => p_rec.business_group_id,
6073 p_effective_date => p_effective_date,
6074 p_object_version_number => p_rec.object_version_number) ;
6075 end if ;
6076
6077 --muky
6078 if p_rec.acty_typ_cd is not null and p_rec.acty_typ_cd like 'GSPSA'
6079 then
6080 chk_acty_typ_cd_gsp
6081 (p_acty_base_rt_id => p_rec.acty_base_rt_id,
6082 --p_pgm_id => p_rec.pgm_id ,
6083 p_pl_id => p_rec.pl_id ,
6084 /*p_opt_id => p_rec.opt_id ,
6085 p_plip_id => p_rec.plip_id ,
6086 p_oipl_id => p_rec.oipl_id ,*/
6087 p_acty_typ_cd => p_rec.acty_typ_cd,
6088 p_effective_date => p_effective_date,
6089 p_business_group_id => p_rec.business_group_id,
6090 p_object_version_number => p_rec.object_version_number);
6091 end if;
6092
6093 chk_mn_mx_rl
6094 ( p_acty_base_rt_id => p_rec.acty_base_rt_id
6095 ,p_mn_mx_elcn_rl => p_rec.mn_mx_elcn_rl
6096 ,p_mn_elcn_val => p_rec.mn_elcn_val
6097 ,p_mx_elcn_val => p_rec.mx_elcn_val
6098 ,p_incrmt_elcn_val => p_rec.incrmt_elcn_val
6099 ,p_dflt_val => p_rec.dflt_val
6100 ,p_entr_val_at_enrt_flag => p_rec.entr_val_at_enrt_flag
6101 ,p_acty_typ_cd => p_rec.acty_typ_cd);
6102
6103 chk_cwb_element_currency
6104 ( p_element_det_rl => p_rec.element_det_rl,
6105 p_currency_det_cd => p_rec.currency_det_cd,
6106 p_acty_typ_cd => p_rec.acty_typ_cd ) ;
6107 --
6108 chk_abr_seq_num
6109 (p_pl_id => p_rec.pl_id,
6110 p_oipl_id => p_rec.oipl_id,
6111 p_opt_id => p_rec.opt_id,
6112 p_abr_seq_num => p_rec.abr_seq_num,
6113 p_effective_start_date => p_effective_date,
6114 p_business_group_id => p_rec.business_group_id);
6115 --
6116
6117
6118 hr_utility.set_location(' Leaving:'||l_proc, 10);
6119
6120 End update_validate;
6121
6122 -- ----------------------------------------------------------------------------
6123 -- |---------------------------< delete_validate >----------------------------|
6124 -- ----------------------------------------------------------------------------
6125 Procedure delete_validate
6126 (p_rec in ben_abr_shd.g_rec_type,
6127 p_effective_date in date,
6128 p_datetrack_mode in varchar2,
6129 p_validation_start_date in date,
6130 p_validation_end_date in date) is
6131 --
6132 l_proc varchar2(72) := g_package||'delete_validate';
6133 --
6134 Begin
6135 hr_utility.set_location('Entering:'||l_proc, 5);
6136 --
6137 -- Call all supporting business operations
6138 --
6139 dt_delete_validate
6140 (p_datetrack_mode => p_datetrack_mode,
6141 p_validation_start_date => p_validation_start_date,
6142 p_validation_end_date => p_validation_end_date,
6143 p_acty_base_rt_id => p_rec.acty_base_rt_id);
6144 --
6145 hr_utility.set_location(' Leaving:'||l_proc, 10);
6146 End delete_validate;
6147 --
6148 --
6149 -- ---------------------------------------------------------------------------
6150 -- |---------------------< return_legislation_code >-------------------------|
6151 -- ---------------------------------------------------------------------------
6152 --
6153 function return_legislation_code
6154 (p_acty_base_rt_id in number) return varchar2 is
6155 --
6156 -- Declare cursor
6157 --
6158 cursor csr_leg_code is
6159 select a.legislation_code
6160 from per_business_groups a,
6161 ben_acty_base_rt_f b
6162 where b.acty_base_rt_id = p_acty_base_rt_id
6163 and a.business_group_id = b.business_group_id;
6164 --
6165 -- Declare local variables
6166 --
6167 l_legislation_code varchar2(150);
6168 l_proc varchar2(72) := g_package||'return_legislation_code';
6169 --
6170 begin
6171 --
6172 hr_utility.set_location('Entering:'|| l_proc, 10);
6173 --
6174 -- Ensure that all the mandatory parameter are not null
6175 --
6176 hr_api.mandatory_arg_error(p_api_name => l_proc,
6177 p_argument => 'acty_base_rt_id',
6178 p_argument_value => p_acty_base_rt_id);
6179 --
6180 open csr_leg_code;
6181 --
6182 fetch csr_leg_code into l_legislation_code;
6183 --
6184 if csr_leg_code%notfound then
6185 --
6186 close csr_leg_code;
6187 --
6188 -- The primary key is invalid therefore we must error
6189 --
6190 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
6191 fnd_message.raise_error;
6192 --
6193 end if;
6194 --
6195 close csr_leg_code;
6196 --
6197 hr_utility.set_location(' Leaving:'|| l_proc, 20);
6198 --
6199 return l_legislation_code;
6200 --
6201 end return_legislation_code;
6202 --
6203
6204 end ben_abr_bus;
[Home] [Help]