[Home] [Help]
PACKAGE BODY: APPS.BEN_LER_BUS
Source
1 Package Body ben_ler_bus as
2 /* $Header: belerrhi.pkb 120.2 2006/11/03 10:34:58 vborkar noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ler_bus.'; -- Global package name
9 g_rows_exist Exception;
10 ---
11 -- added for Bug: 4651734
12 g_business_group_id number(15); -- For validating translation;
13
14 PROCEDURE set_translation_globals(p_business_group_id IN NUMBER) IS
15 BEGIN
16 g_business_group_id := p_business_group_id;
17 END;
18 --
19 procedure validate_translation(ler_id IN NUMBER,
20 language IN VARCHAR2,
21 ler_name IN VARCHAR2,
22 p_business_group_id IN NUMBER DEFAULT NULL) IS
23 /*
24 This procedure fails if a Life event reason translation is already present in
25 the table for a given language. Otherwise, no action is performed. It is
26 used to ensure uniqueness of translated names.
27 */
28
29 --
30 cursor c_translation(p_language IN VARCHAR2,
31 p_ler_name IN VARCHAR2,
32 p_ler_id IN NUMBER,
33 p_bus_grp_id in number) IS
34 SELECT 1
35 FROM ben_ler_f_tl ler_tl,
36 ben_ler_f ler
37 WHERE upper(ler.name)= upper(p_ler_name)
38 AND ler.ler_id = ler_tl.ler_id
39 AND ler_tl.language = p_language
40 AND (ler_tl.ler_id <> p_ler_id OR p_ler_id IS NULL)
41 AND (ler.business_group_id = p_bus_grp_id OR p_bus_grp_id IS NULL);
42
43 l_package_name VARCHAR2(80) := 'BEN_LER_BUS.VALIDATE_TRANSLATION';
44 l_business_group_id NUMBER := nvl(p_business_group_id, g_business_group_id);
45
46 g_dummy NUmber(1);
47 BEGIN
48
49 hr_utility.set_location (l_package_name,10);
50 OPEN c_translation(language, ler_name,ler_id,l_business_group_id);
51 hr_utility.set_location (l_package_name,50);
52 FETCH c_translation INTO g_dummy;
53
54 IF c_translation%NOTFOUND THEN
55 hr_utility.set_location (l_package_name,60);
56 CLOSE c_translation;
57 ELSE
58 hr_utility.set_location (l_package_name,70);
59 CLOSE c_translation;
60 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
61 fnd_message.raise_error;
62 END IF;
63 hr_utility.set_location ('Leaving:'||l_package_name,80);
64 END validate_translation;
65 --
66 -- change ended .. for bug 4651734
67 --
68
69 -- ----------------------------------------------------------------------------
70 -- |------< chk_ler_id >------|
71 -- ----------------------------------------------------------------------------
72 --
73 -- Description
74 -- This procedure is used to check that the primary key for the table
75 -- is created properly. It should be null on insert and
76 -- should not be able to be updated.
77 --
78 -- Pre Conditions
79 -- None.
80 --
81 -- In Parameters
82 -- ler_id PK of record being inserted or updated.
83 -- effective_date Effective Date of session
84 -- object_version_number Object version number of record being
85 -- inserted or updated.
86 --
87 -- Post Success
88 -- Processing continues
89 --
90 -- Post Failure
91 -- Errors handled by the procedure
92 --
93 -- Access Status
94 -- Internal table handler use only.
95 --
96 Procedure chk_ler_id(p_ler_id in number,
97 p_effective_date in date,
98 p_object_version_number in number) is
99 --
100 l_proc varchar2(72) := g_package||'chk_ler_id';
101 l_api_updating boolean;
102 --
103 Begin
104 --
105 hr_utility.set_location('Entering:'||l_proc, 5);
106 --
107 l_api_updating := ben_ler_shd.api_updating
108 (p_effective_date => p_effective_date,
109 p_ler_id => p_ler_id,
110 p_object_version_number => p_object_version_number);
111 --
112 if (l_api_updating
113 and nvl(p_ler_id,hr_api.g_number)
114 <> ben_ler_shd.g_old_rec.ler_id) then
115 --
116 -- raise error as PK has changed
117 --
118 ben_ler_shd.constraint_error('BEN_LER_PK');
119 --
120 elsif not l_api_updating then
121 --
122 -- check if PK is null
123 --
124 if p_ler_id is not null then
125 --
126 -- raise error as PK is not null
127 --
128 ben_ler_shd.constraint_error('BEN_LER_PK');
129 --
130 end if;
131 --
132 end if;
133 --
134 hr_utility.set_location('Leaving:'||l_proc, 10);
135 --
136 End chk_ler_id;
137 -- ----------------------------------------------------------------------------
138 -- |------< chk_child_rows >------|
139 -- ----------------------------------------------------------------------------
140 --
141 -- Description
142 -- This procedure is used to check if there are any child rows for the parent
143 -- ben_ler record that is either being deleted or is having it's typ_cd updated.
144 --
145 -- Pre Conditions
146 -- None.
147 --
148 -- In Parameters
149 -- p_ler_id pk value being check
150 -- p_validation_start_date date range to check for.
151 -- p_validation_end_date date range to check for.
152 -- Out Parameters
153 -- p_table_name the table that contains child rows.
154 --
155 -- Post Success
156 -- Processing continues
157 --
158 -- Post Failure
159 -- Errors handled by the procedure
160 --
161 -- Access Status
162 -- Internal table handler use only.
163 --
164 Procedure chk_child_rows
165 (p_ler_id in number,
166 p_validation_start_date in date,
167 p_validation_end_date in date,
168 p_delete_flag in varchar2 default 'N',
169 p_table_name out nocopy varchar2) is
170 --
171 l_proc varchar2(72) := g_package||'chk_child_rows';
172 l_table_name all_tables.table_name%TYPE := null;
173 --
174 Begin
175 --
176 hr_utility.set_location('Entering:'||l_proc, 5);
177 --
178 If (dt_api.rows_exist
179 (p_base_table_name => 'ben_lee_rsn_f',
180 p_base_key_column => 'ler_id',
181 p_base_key_value => p_ler_id,
182 p_from_date => p_validation_start_date,
183 p_to_date => p_validation_end_date)) Then
184 l_table_name := 'ben_lee_rsn_f';
185 End If;
186 If (dt_api.rows_exist
187 (p_base_table_name => 'ben_ler_chg_plip_enrt_f',
188 p_base_key_column => 'ler_id',
189 p_base_key_value => p_ler_id,
190 p_from_date => p_validation_start_date,
191 p_to_date => p_validation_end_date)) Then
192 l_table_name := 'ben_ler_chg_plip_enrt_f';
193 End If;
194 If (dt_api.rows_exist
195 (p_base_table_name => 'ben_ler_chg_pl_nip_enrt_f',
196 p_base_key_column => 'ler_id',
197 p_base_key_value => p_ler_id,
198 p_from_date => p_validation_start_date,
199 p_to_date => p_validation_end_date)) Then
200 l_table_name := 'ben_ler_chg_pl_nip_enrt_f';
201 End If;
202 If (dt_api.rows_exist
203 (p_base_table_name => 'ben_ler_chg_oipl_enrt_f',
204 p_base_key_column => 'ler_id',
205 p_base_key_value => p_ler_id,
206 p_from_date => p_validation_start_date,
207 p_to_date => p_validation_end_date)) Then
208 l_table_name := 'ben_ler_chg_oipl_enrt_f';
209 End If;
210 If (dt_api.rows_exist
211 (p_base_table_name => 'ben_elig_to_prte_rsn_f',
212 p_base_key_column => 'ler_id',
213 p_base_key_value => p_ler_id,
214 p_from_date => p_validation_start_date,
215 p_to_date => p_validation_end_date)) Then
216 l_table_name := 'ben_elig_to_prte_rsn_f';
217 End If;
218 If (dt_api.rows_exist
219 (p_base_table_name => 'ben_ler_chg_dpnt_cvg_f',
220 p_base_key_column => 'ler_id',
221 p_base_key_value => p_ler_id,
222 p_from_date => p_validation_start_date,
223 p_to_date => p_validation_end_date)) Then
224 l_table_name := 'ben_ler_chg_dpnt_cvg_f';
225 End If;
226 If (dt_api.rows_exist
227 (p_base_table_name => 'ben_elig_per_f',
228 p_base_key_column => 'ler_id',
229 p_base_key_value => p_ler_id,
230 p_from_date => p_validation_start_date,
231 p_to_date => p_validation_end_date)) Then
232 l_table_name := 'ben_elig_per_f';
233 End If;
234 If (dt_api.rows_exist
235 (p_base_table_name => 'ben_ler_per_info_cs_ler_f',
236 p_base_key_column => 'ler_id',
237 p_base_key_value => p_ler_id,
238 p_from_date => p_validation_start_date,
239 p_to_date => p_validation_end_date)) Then
240 l_table_name := 'ben_ler_per_info_cs_ler_f';
241 End If;
242 If (dt_api.rows_exist
243 (p_base_table_name => 'ben_ler_rltd_per_cs_ler_f',
244 p_base_key_column => 'ler_id',
245 p_base_key_value => p_ler_id,
246 p_from_date => p_validation_start_date,
247 p_to_date => p_validation_end_date)) Then
248 l_table_name := 'ben_ler_rltd_per_cs_ler_f';
249 End If;
250 If (dt_api.rows_exist
251 (p_base_table_name => 'ben_css_rltd_per_per_in_ler_f',
252 p_base_key_column => 'ler_id',
253 p_base_key_value => p_ler_id,
254 p_from_date => p_validation_start_date,
255 p_to_date => p_validation_end_date)) Then
256 l_table_name := 'ben_css_rltd_per_per_in_ler_f';
257 End If;
258 If (dt_api.rows_exist
259 (p_base_table_name => 'ben_css_rltd_per_per_in_ler_f',
260 p_base_key_column => 'rsltg_ler_id',
261 p_base_key_value => p_ler_id,
262 p_from_date => p_validation_start_date,
263 p_to_date => p_validation_end_date)) Then
264 l_table_name := 'ben_css_rltd_per_per_in_ler_f';
265 End If;
266
267 if l_table_name is not null then
268 if p_delete_flag = 'Y' then
269 ben_utility.child_exists_error(p_table_name => l_table_name);
270 else
271 fnd_message.set_name('BEN', 'BEN_91040_CAN_NOT_CHG_TYPE');
272 fnd_message.raise_error;
273 end if;
274 end if;
275 --
276 hr_utility.set_location('Leaving:'||l_proc, 10);
277 End chk_child_rows;
278
279 --
280 Procedure chk_seeded_life
281 (p_ler_id in number) is
282 --
283 l_proc varchar2(72) := g_package||'chk_seeded_life';
284 dummy varchar2(10);
285 --
286 Begin
287 --
288 hr_utility.set_location('Entering:'||l_proc, 5);
289 --
290 select null into dummy from ben_ler_f ler
291 where ler.ler_id = p_ler_id and ler.typ_cd in
292 (select typ_cd from ben_startup_lers);
293 -- fnd_message.set_name('BEN','BEN_92490_CANNOT_DELETE_LER');
294 fnd_message.set_name('BEN','BEN_92490_CANNOT_DELETE_LER');
295 fnd_message.raise_error;
296 hr_utility.set_location('Leaving:'||l_proc,10);
297 Exception
298 When no_data_found then
299 null;
300 End chk_seeded_life;
301
302 --
303 -- ----------------------------------------------------------------------------
304 -- |------< chk_whn_to_prcs_cd >------|
305 -- ----------------------------------------------------------------------------
306 --
307 -- Description
308 -- This procedure is used to check that the lookup value is valid.
309 --
310 -- Pre Conditions
311 -- None.
312 --
313 -- In Parameters
314 -- ler_id PK of record being inserted or updated.
315 -- whn_to_prcs_cd Value of lookup code.
316 -- effective_date effective date
317 -- object_version_number Object version number of record being
318 -- inserted or updated.
319 --
320 -- Post Success
321 -- Processing continues
322 --
323 -- Post Failure
324 -- Error handled by procedure
325 --
326 -- Access Status
327 -- Internal table handler use only.
328 --
329 Procedure chk_whn_to_prcs_cd(p_ler_id in number,
330 p_whn_to_prcs_cd in varchar2,
331 p_effective_date in date,
332 p_object_version_number in number) is
333 --
334 l_proc varchar2(72) := g_package||'chk_whn_to_prcs_cd';
335 l_api_updating boolean;
336 --
337 Begin
338 --
339 hr_utility.set_location('Entering:'||l_proc, 5);
340 --
341 l_api_updating := ben_ler_shd.api_updating
342 (p_ler_id => p_ler_id,
343 p_effective_date => p_effective_date,
344 p_object_version_number => p_object_version_number);
345 --
346 if (l_api_updating
347 and p_whn_to_prcs_cd
348 <> nvl(ben_ler_shd.g_old_rec.whn_to_prcs_cd,hr_api.g_varchar2)
349 or not l_api_updating)
350 and p_whn_to_prcs_cd is not null then
351 --
352 -- check if value of lookup falls within lookup type.
353 --
354 if hr_api.not_exists_in_hr_lookups
355 (p_lookup_type => 'BEN_WHN_TO_PRCS_LER',
356 p_lookup_code => p_whn_to_prcs_cd,
357 p_effective_date => p_effective_date) then
358 --
359 -- raise error as does not exist as lookup
360 --
361 fnd_message.set_name('BEN','BEN_91010_INV_WHN_TO_PRCS_CD');
362 fnd_message.raise_error;
363 --
364 end if;
365 --
366 end if;
367 --
368 hr_utility.set_location('Leaving:'||l_proc,10);
369 --
370 end chk_whn_to_prcs_cd;
371 --
372 -- ----------------------------------------------------------------------------
373 -- |------< chk_ler_eval_rl >------|
374 -- ----------------------------------------------------------------------------
375 --
376 -- Description
377 -- This procedure is used to check that the Formula Rule is valid.
378 --
379 -- Pre Conditions
380 -- None.
381 --
382 -- In Parameters
383 -- ler_id PK of record being inserted or updated.
384 -- ler_eval_rl Value of formula rule id.
385 -- effective_date effective date
386 -- object_version_number Object version number of record being
387 -- inserted or updated.
388 --
389 -- Post Success
390 -- Processing continues
391 --
392 -- Post Failure
393 -- Error handled by procedure
394 --
395 -- Access Status
396 -- Internal table handler use only.
397 --
398 Procedure chk_ler_eval_rl(p_ler_id in number,
399 p_ler_eval_rl in number,
400 p_business_group_id in number,
401 p_effective_date in date,
402 p_object_version_number in number) is
403 --
404 l_proc varchar2(72) := g_package||'chk_ler_eval_rl';
405 l_api_updating boolean;
406 l_dummy varchar2(1);
407 --
408 cursor c1 is
409 select null
410 from ff_formulas_f ff
411 ,per_business_groups pbg
412 where ff.formula_id = p_ler_eval_rl
413 and ff.formula_type_id = -157 -- Life Event Evaluation
414 and pbg.business_group_id = p_business_group_id
415 and nvl(ff.business_group_id, p_business_group_id) = p_business_group_id
416 and nvl(ff.legislation_code, pbg.legislation_code) = pbg.legislation_code
417 and p_effective_date between ff.effective_start_date
418 and ff.effective_end_date;
419 --
420 Begin
421 --
422 hr_utility.set_location('Entering:'||l_proc, 5);
423 --
424 l_api_updating := ben_ler_shd.api_updating
425 (p_ler_id => p_ler_id,
426 p_effective_date => p_effective_date,
427 p_object_version_number => p_object_version_number);
428 --
429 if (l_api_updating
430 and nvl(p_ler_eval_rl,hr_api.g_number)
431 <> ben_ler_shd.g_old_rec.ler_eval_rl
432 or not l_api_updating)
433 and p_ler_eval_rl is not null then
434 --
435 -- check if value of formula rule is valid.
436 --
437 open c1;
438 --
439 -- fetch value from cursor if it returns a record then the
440 -- formula is valid otherwise its invalid
441 --
442 fetch c1 into l_dummy;
443 if c1%notfound then
444 --
445 close c1;
446 --
447 -- raise error
448 --
449 fnd_message.set_name('BEN','BEN_91007_INVALID_RULE');
450 fnd_message.raise_error;
451 --
452 end if;
453 --
454 close c1;
455 --
456 end if;
457 --
458 hr_utility.set_location('Leaving:'||l_proc,10);
459 --
460 end chk_ler_eval_rl;
461 --
462 -- ----------------------------------------------------------------------------
463 -- |---------------------------< chk_ptnl_ler_trtmt_cd >----------------------|
464 -- ----------------------------------------------------------------------------
465 --
466 -- Description
467 -- This procedure is used to check that the lookup value is valid.
468 --
469 -- Pre Conditions
470 -- None.
471 --
472 -- In Parameters
473 -- ler_id PK of record being inserted or updated.
474 -- ptnl_ler_trtmt_cd Value of lookup code.
475 -- effective_date effective date
476 -- object_version_number Object version number of record being
477 -- inserted or updated.
478 --
479 -- Post Success
480 -- Processing continues
481 --
482 -- Post Failure
483 -- Error handled by procedure
484 --
485 -- Access Status
486 -- Internal table handler use only.
487 --
488 Procedure chk_ptnl_ler_trtmt_cd(p_ler_id in number,
489 p_ptnl_ler_trtmt_cd in varchar2,
490 p_effective_date in date,
491 p_object_version_number in number) is
492 --
493 l_proc varchar2(72) := g_package||'chk_ptnl_ler_trtmt_cd';
494 l_api_updating boolean;
495 --
496 Begin
497 --
498 hr_utility.set_location('Entering:'||l_proc, 5);
499 --
500 l_api_updating := ben_ler_shd.api_updating
501 (p_ler_id => p_ler_id,
502 p_effective_date => p_effective_date,
503 p_object_version_number => p_object_version_number);
504 --
505 if (l_api_updating
506 and p_ptnl_ler_trtmt_cd
507 <> nvl(ben_ler_shd.g_old_rec.ptnl_ler_trtmt_cd,hr_api.g_varchar2)
508 or not l_api_updating)
509 and p_ptnl_ler_trtmt_cd is not null then
510 --
511 -- check if value of lookup falls within lookup type.
512 --
513 if hr_api.not_exists_in_hr_lookups
514 (p_lookup_type => 'BEN_PTNL_LER_TRTMT',
515 p_lookup_code => p_ptnl_ler_trtmt_cd,
516 p_effective_date => p_effective_date) then
517 --
518 -- raise error as does not exist as lookup
519 --
520 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
521 fnd_message.set_token('FIELD','p_ptnl_ler_trtmt_cd');
522 fnd_message.set_token('TYPE','BEN_PTNL_LER_TRTMT');
523 fnd_message.raise_error;
524 --
525 end if;
526 --
527 end if;
528 --
529 hr_utility.set_location('Leaving:'||l_proc,10);
530 --
531 end chk_ptnl_ler_trtmt_cd;
532 --
533 --
534 --
535 -- ----------------------------------------------------------------------------
536 -- |--------------------< chk_ptnl_ler_trtmt_cd_seed_ler >--------------------|
537 -- ----------------------------------------------------------------------------
538 --
539 -- Description
540 -- This procedure is used to check that for only seeded temporal life events the
541 -- the life event treatment code can be "Never detect this temporal life event".
542 -- Bug : 3575124
543 -- Pre Conditions
544 -- None.
545 --
546 -- In Parameters
547 -- ler_id PK of record being inserted or updated.
548 -- ptnl_ler_trtmt_cd Value of lookup code.
549 -- effective_date effective date
550 -- object_version_number Object version number of record being
551 -- inserted or updated.
552 -- typ_cd Type code of LER
553 --
554 -- Post Success
555 -- Processing continues
556 --
557 -- Post Failure
558 -- Error handled by procedure
559 --
560 -- Access Status
561 -- Internal table handler use only.
562 --
563 Procedure chk_ptnl_ler_trtmt_cd_seed_ler(p_ler_id in number,
564 p_ptnl_ler_trtmt_cd in varchar2,
565 p_effective_date in date,
566 p_object_version_number in number,
567 p_typ_cd in varchar2) is
568 --
569 l_proc varchar2(72) := g_package||'chk_ptnl_ler_trtmt_cd_seed_ler';
570 l_api_updating boolean;
571 --
572 Begin
573 --
574 hr_utility.set_location('Entering:'||l_proc, 5);
575 --
576 l_api_updating := ben_ler_shd.api_updating
577 (p_ler_id => p_ler_id,
578 p_effective_date => p_effective_date,
579 p_object_version_number => p_object_version_number);
580 --
581 if ( ( l_api_updating
582 and ( p_ptnl_ler_trtmt_cd
583 <> nvl(ben_ler_shd.g_old_rec.ptnl_ler_trtmt_cd,hr_api.g_varchar2)
584 or p_typ_cd
585 <> nvl(ben_ler_shd.g_old_rec.typ_cd,hr_api.g_varchar2)
586 )
587 )
588 or not l_api_updating)
589 and p_ptnl_ler_trtmt_cd is not null then
590 --
591 if p_typ_cd not in ('DRVDAGE', 'DRVDCAL', 'DRVDCMP', 'DRVDLOS', 'DRVDHRW', 'DRVDTPF')
592 and p_ptnl_ler_trtmt_cd = 'IGNRTHIS'
593 then
594 --
595 fnd_message.set_name('BEN','BEN_93957_LER_TRTMT_SEED_ERR');
596 fnd_message.raise_error;
597 --
598 end if;
599 --
600 end if;
601 --
602 hr_utility.set_location('Leaving:'||l_proc,10);
603 --
604 end chk_ptnl_ler_trtmt_cd_seed_ler;
605 --
606 -- ----------------------------------------------------------------------------
607 -- |---------------------------< chk_lf_evt_oper_cd >-------------------------|
608 -- ----------------------------------------------------------------------------
609 --
610 -- Description
611 -- This procedure is used to check that the lookup value is valid.
612 --
613 -- Pre Conditions
614 -- None.
615 --
616 -- In Parameters
617 -- ler_id PK of record being inserted or updated.
618 -- lf_evt_oper_cd Value of lookup code.
619 -- business_group_id
620 -- effective_date
621 -- validation_sdtart_date
622 -- validation_end_date
623 -- object_version_number Object version number of record being
624 -- inserted or updated.
625 --
626 -- Post Success
627 -- Processing continues
628 --
629 -- Post Failure
630 -- Error handled by procedure
631 --
632 -- Access Status
633 -- Internal table handler use only.
634 --
635 Procedure chk_lf_evt_oper_cd(p_ler_id in number,
636 p_lf_evt_oper_cd in varchar2,
637 p_business_group_id in number,
638 p_effective_date in date,
639 p_object_version_number in number,
640 p_validation_start_date in date,
641 p_validation_end_date in date,
642 p_typ_cd in varchar2) is
643 cursor c1 is
644 select null
645 from ben_ler_f
646 where lf_evt_oper_cd = p_lf_evt_oper_cd
647 and ler_id <> nvl(p_ler_id,-1)
648 and business_group_id = p_business_group_id
649 and effective_end_date >= p_validation_start_date
650 and effective_start_date <= p_validation_end_date ;
651 --
652 l_dummy varchar2(1);
653 l_proc varchar2(72) := g_package||'chk_lf_evt_oper_cd';
654 l_api_updating boolean;
655 --
656 Begin
657 --
658 hr_utility.set_location('Entering:'||l_proc, 5);
659 --
660 -- GSP Rate Synchronization : Check if Operation Code is not null for LER type = GSP and ABSENCES
661 if p_typ_cd in ('GSP', 'ABS') and p_lf_evt_oper_cd is null then
662 --
663 fnd_message.set_name('BEN', 'BEN_94032_GSP_OPER_CD_NULL');
664 fnd_message.raise_error;
665 --
666 end if;
667 --
668 l_api_updating := ben_ler_shd.api_updating
669 (p_ler_id => p_ler_id,
670 p_effective_date => p_effective_date,
671 p_object_version_number => p_object_version_number);
672 --
673 if (l_api_updating
674 and p_lf_evt_oper_cd
675 <> nvl(ben_ler_shd.g_old_rec.lf_evt_oper_cd,hr_api.g_varchar2)
676 or not l_api_updating)
677 and p_lf_evt_oper_cd is not null then
678 --
679 -- check if value of lookup falls within lookup type.
680 --
681 if hr_api.not_exists_in_hr_lookups
682 (p_lookup_type => 'BEN_LF_EVT_OPER',
683 p_lookup_code => p_lf_evt_oper_cd,
684 p_effective_date => p_effective_date) then
685 --
686 -- raise error as does not exist as lookup
687 --
688 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
689 fnd_message.set_token('FIELD','lf_evt_oper_cd');
690 fnd_message.set_token('TYPE','BEN_LF_EVT_OPER');
691 fnd_message.raise_error;
692 --
693 end if;
694 --
695 -- GSP Rate Synchronization
696 if p_typ_cd = 'ABS' and p_lf_evt_oper_cd not in ('START', 'END', 'DELETE') then
697 --
698 fnd_message.set_name('BEN', 'BEN_94033_ABS_OPER_CD_FALSE');
699 fnd_message.raise_error;
700 --
701 elsif p_typ_cd = 'GSP' and p_lf_evt_oper_cd not in ('PROG', 'SYNC') then
702 --
703 fnd_message.set_name('BEN', 'BEN_94034_GSP_OPER_CD_FALSE');
704 fnd_message.raise_error;
705 --
706 end if;
707 --
708 -- Bug 2851090 Added checks for duplicate Start and End
709 -- Life Event Operation Codes
710 --
711 if p_lf_evt_oper_cd = 'START' then
712 open c1;
713 fetch c1 into l_dummy;
714 if c1%found then
715 close c1;
716 fnd_message.set_name('BEN','BEN_93362_DUP_START_LF_EVT');
717 fnd_message.raise_error;
718 end if;
719 close c1;
720 elsif p_lf_evt_oper_cd = 'END' then
721 open c1;
722 fetch c1 into l_dummy;
723 if c1%found then
724 close c1;
725 fnd_message.set_name('BEN','BEN_93363_DUP_END_LF_EVT');
726 fnd_message.raise_error;
727 end if;
728 close c1;
729 elsif p_lf_evt_oper_cd = 'DELETE' then
730 open c1;
731 fetch c1 into l_dummy;
732 if c1%found then
733 close c1;
734 fnd_message.set_name('BEN','BEN_93145_DUP_DEL_LF_EVT');
735 fnd_message.raise_error;
736 end if;
737 close c1;
738 elsif p_lf_evt_oper_cd = 'PROG' then -- GSP Rate Synchronization
739 open c1;
740 fetch c1 into l_dummy;
741 if c1%found then
742 close c1;
743 fnd_message.set_name('BEN','BEN_94030_DUP_PROG_LF_EVT');
744 fnd_message.raise_error;
745 end if;
746 close c1;
747 elsif p_lf_evt_oper_cd = 'SYNC' then
748 open c1;
749 fetch c1 into l_dummy;
750 if c1%found then
751 close c1;
752 fnd_message.set_name('BEN','BEN_94031_DUP_SYNC_LF_EVT');
753 fnd_message.raise_error;
754 end if;
755 close c1; -- GSP Rate Synchronization
756 end if;
757 end if;
758 --
759 hr_utility.set_location('Leaving:'||l_proc,10);
760 --
761 end chk_lf_evt_oper_cd;
762 -- ----------------------------------------------------------------------------
763 -- |---------------------------< chk_slctbl_slf_svc_cd >----------------------|
764 -- ----------------------------------------------------------------------------
765 --
766 -- Description
767 -- This procedure is used to check that the lookup value is valid.
768 --
769 -- Pre Conditions
770 -- None.
771 --
772 -- In Parameters
773 -- ler_id PK of record being inserted or updated.
774 -- slctbl_slf_svc_cd Value of lookup code.
775 -- effective_date effective date
776 -- object_version_number Object version number of record being
777 -- inserted or updated.
778 --
779 -- Post Success
780 -- Processing continues
781 --
782 -- Post Failure
783 -- Error handled by procedure
784 --
785 -- Access Status
786 -- Internal table handler use only.
787 --
788 Procedure chk_slctbl_slf_svc_cd (p_ler_id in number,
789 p_slctbl_slf_svc_cd in varchar2,
790 p_effective_date in date,
791 p_object_version_number in number) is
792 --
793 l_proc varchar2(72) := g_package||'chk_slctbl_slf_svc_cd' ;
794 l_api_updating boolean;
795 --
796 Begin
797 --
798 hr_utility.set_location('Entering:'||l_proc, 5);
799 --
800 l_api_updating := ben_ler_shd.api_updating
801 (p_ler_id => p_ler_id,
802 p_effective_date => p_effective_date,
803 p_object_version_number => p_object_version_number);
804 --
805 if (l_api_updating
806 and p_slctbl_slf_svc_cd
807 <> nvl(ben_ler_shd.g_old_rec.slctbl_slf_svc_cd ,hr_api.g_varchar2)
808 or not l_api_updating)
809 and p_slctbl_slf_svc_cd is not null then
810 --
811 -- check if value of lookup falls within lookup type.
812 --
813 if hr_api.not_exists_in_hr_lookups
814 (p_lookup_type => 'BEN_SLCTBL_SLF_SVC_CD',
815 p_lookup_code => p_slctbl_slf_svc_cd ,
816 p_effective_date => p_effective_date) then
817 --
818 -- raise error as does not exist as lookup
819 --
820 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
821 fnd_message.set_token('FIELD','p_slctbl_slf_svc_cd');
822 fnd_message.set_token('TYPE','BEN_SLCTBL_SLF_SVC_CD');
823 fnd_message.raise_error;
824 --
825 end if;
826 --
827 end if;
828 --
829 hr_utility.set_location('Leaving:'||l_proc,10);
830 --
831 end chk_slctbl_slf_svc_cd ;
832
833 -- ----------------------------------------------------------------------------
834 -- |------< chk_typ_cd >------|
835 -- ----------------------------------------------------------------------------
836 --
837 -- Description
838 -- This procedure is used to check that the lookup value is valid.
839 --
840 -- Pre Conditions
841 -- None.
842 --
843 -- In Parameters
844 -- ler_id PK of record being inserted or updated.
845 -- typ_cd Value of lookup code.
846 -- effective_date effective date
847 -- object_version_number Object version number of record being
848 -- inserted or updated.
849 --
850 -- Post Success
851 -- Processing continues
852 --
853 -- Post Failure
854 -- Error handled by procedure
855 --
856 -- Access Status
857 -- Internal table handler use only.
858 --
859 Procedure chk_typ_cd(p_ler_id in number,
860 p_typ_cd in varchar2,
861 p_business_group_id in number,
862 p_effective_date in date,
863 p_object_version_number in number,
864 p_validation_start_date in date,
865 p_validation_end_date in date) is
866 --
867 CURSOR l_csr_ler IS
868 SELECT 'x'
869 FROM ben_ler_f
870 WHERE typ_cd = nvl(p_typ_cd, hr_api.g_varchar2)
871 AND business_group_id + 0 = p_business_group_id
872 AND effective_end_date >= p_validation_start_date
873 AND effective_start_date <= p_validation_end_date ;
874 --
875 l_db_ler_row l_csr_ler%rowtype;
876 l_proc varchar2(72) := g_package||'chk_typ_cd';
877 l_api_updating boolean;
878 l_table_name all_tables.table_name%TYPE;
879 --
880 Begin
881 --
882 hr_utility.set_location('Entering:'||l_proc, 5);
883 --
884 l_api_updating := ben_ler_shd.api_updating
885 (p_ler_id => p_ler_id,
886 p_effective_date => p_effective_date,
887 p_object_version_number => p_object_version_number);
888 --
889 if (l_api_updating and
890 ben_ler_shd.g_old_rec.typ_cd in ('DRVDAGE', 'DRVDLOS', 'DRVDCAL',
891 'DRVDHRW', 'DRVDCMP', 'DRVDTPF', 'QMSCOCO', 'QDROCOU', 'QUAINGR')
892 and nvl(p_typ_cd,hr_api.g_varchar2) <> ben_ler_shd.g_old_rec.typ_cd) then
893 --
894 -- The user is not allowed to change Life Event TYPE of derived types.
895 --
896 fnd_message.set_name('BEN','BEN_91423_DELIVERED_TYPE_CHG');
897 fnd_message.raise_error;
898 end if;
899
900 if nvl(p_typ_cd,hr_api.g_varchar2)
901 <> nvl(ben_ler_shd.g_old_rec.typ_cd,hr_api.g_varchar2)
902 and
903 p_typ_cd in ('DRVDAGE', 'DRVDLOS', 'DRVDCAL',
904 'DRVDHRW', 'DRVDCMP', 'DRVDTPF', 'SCHEDDO','SCHEDDA','SCHEDDU',
905 'QMSCOCO', 'QDROCOU', 'QUAINGR') then -- GSP Rate Synchronization : Removed GSP
906 --
907 -- Check to see if a ler already exists of this type. If so, do not
908 -- allow creation of it. If not, allow creation, since this program is
909 -- called by the process used to seed these life events.
910 --
911 open l_csr_ler ;
912 fetch l_csr_ler into l_db_ler_row;
913 if l_csr_ler%found then
914 close l_csr_ler;
915 --
916 -- The user is not allowed to create Life Events of these derived types.
917 -- GRADE/STEP : Only one Grade/step life event is allowed.
918 --
919 if p_typ_cd = 'GSP' then
920 fnd_message.set_name('BEN','BEN_93619_ONLY_1_GSP_LE_ALWD');
921 else
922 fnd_message.set_name('BEN','BEN_91424_DERIV_TYPE_INS');
923 end if;
924 fnd_message.raise_error;
925 else
926 close l_csr_ler;
927 end if;
928 end if;
929
930
931 if (l_api_updating
932 and p_typ_cd
933 <> nvl(ben_ler_shd.g_old_rec.typ_cd,hr_api.g_varchar2)
934 or not l_api_updating)
935 and p_typ_cd is not null then
936 --
937 -- check if value of lookup falls within lookup type.
938 --
939 if hr_api.not_exists_in_hr_lookups
940 (p_lookup_type => 'BEN_LER_TYP',
941 p_lookup_code => p_typ_cd,
942 p_effective_date => p_effective_date) then
943 --
944 -- raise error as does not exist as lookup
945 --
946 fnd_message.set_name('BEN','BEN_91011_INVALID_TYPE');
947 fnd_message.raise_error;
948 --
949 end if;
950 --
951 end if;
952 --
953 -- Only allow the Type Code to change if the record is not being used in any
954 -- foreign keys. CAN change the type from null to something though.
955 if (l_api_updating
956 and nvl(p_typ_cd,hr_api.g_varchar2)
957 <> nvl(ben_ler_shd.g_old_rec.typ_cd,hr_api.g_varchar2)
958 and ben_ler_shd.g_old_rec.typ_cd is not null) then
959 --
960 -- Call a routine to check to see if any child rows exist. This
961 -- procedure will return an error message if any children exist.
962 --
963 chk_child_rows
964 (p_ler_id => p_ler_id,
965 p_validation_start_date => p_validation_start_date,
966 p_validation_end_date => p_validation_end_date,
967 p_table_name => l_table_name );
968 end if;
969 hr_utility.set_location('Leaving:'||l_proc,10);
970 --
971 end chk_typ_cd;
972 --
973 -- ----------------------------------------------------------------------------
974 -- |------< chk_typ_cd_not_null >------|
975 -- ----------------------------------------------------------------------------
976 --
977 -- Description
978 -- This procedure is used to check that the typ_cd is not null
979 --
980 -- Pre Conditions
981 -- None.
982 --
983 -- In Parameters
984 -- ler_id PK of record being inserted or updated.
985 -- typ_cd Value of lookup code.
986 -- effective_date effective date
987 -- object_version_number Object version number of record being
988 -- inserted or updated.
989 --
990 -- Post Success
991 -- Processing continues
992 --
993 -- Post Failure
994 -- Error handled by procedure
995 --
996 -- Access Status
997 -- Internal table handler use only.
998 --
999 Procedure chk_typ_cd_not_null(p_ler_id in number,
1000 p_typ_cd in varchar2,
1001 p_effective_date in date,
1002 p_object_version_number in number) is
1003 --
1004 l_proc varchar2(72) := g_package||'chk_typ_cd_not_null';
1005 l_api_updating boolean;
1006 --
1007 Begin
1008 --
1009 hr_utility.set_location('Entering:'||l_proc, 5);
1010 --
1011 l_api_updating := ben_ler_shd.api_updating
1012 (p_ler_id => p_ler_id,
1013 p_effective_date => p_effective_date,
1014 p_object_version_number => p_object_version_number);
1015 --
1016 if (l_api_updating and p_typ_cd is null ) then
1017 --
1018 fnd_message.set_name('BEN','BEN_91011_INVALID_TYPE');
1019 fnd_message.raise_error;
1020 --
1021 end if;
1022 --
1023 hr_utility.set_location('Leaving:'||l_proc,10);
1024 --
1025 end chk_typ_cd_not_null;
1026 --
1027 --
1028 -- ----------------------------------------------------------------------------
1029 -- |------------------------< chk_irec_typ_cd_uniq_in_bg >--------------------|
1030 -- ----------------------------------------------------------------------------
1031 --
1032 -- Description
1033 -- This procedure is used to check that no other Life event of type = 'iRecruitment'
1034 -- exists in the same Business Group
1035 --
1036 -- Pre Conditions
1037 -- None.
1038 --
1039 -- In Parameters
1040 -- ler_id PK of record being inserted or updated.
1041 -- typ_cd Value of lookup code.
1042 -- effective_date effective date
1043 -- business_group_id Business Group Id of the life event
1044 -- object_version_number Object version number of record being
1045 -- inserted or updated.
1046 -- validation_start_date validation start date of the record
1047 -- validation_end_date validation end date of the record
1048 --
1049 -- Post Success
1050 -- Processing continues
1051 --
1052 -- Post Failure
1053 -- Error handled by procedure
1054 --
1055 -- Access Status
1056 -- Internal table handler use only.
1057 --
1058 Procedure chk_irec_typ_cd_uniq_in_bg(p_ler_id in number,
1059 p_typ_cd in varchar2,
1060 p_business_group_id in number,
1061 p_effective_date in date,
1062 p_object_version_number in number,
1063 p_validation_start_date in date,
1064 p_validation_end_date in date) is
1065 --
1066 cursor l_csr_ler is
1067 select null
1068 from ben_ler_f ler
1069 where ler.typ_cd = 'IREC'
1070 and ler.ler_id <> nvl(p_ler_id, -1)
1071 and ler.business_group_id + 0 = p_business_group_id
1072 and p_validation_start_date <= ler.effective_end_date
1073 and p_validation_end_date >= ler.effective_start_date;
1074 --
1075 l_proc varchar2(72) := g_package||'chk_irec_typ_cd_uniq_in_bg';
1076 l_api_updating boolean;
1077 l_dummy varchar2(1);
1078 --
1079 Begin
1080 --
1081 hr_utility.set_location('Entering:'||l_proc, 5);
1082 --
1083 l_api_updating := ben_ler_shd.api_updating
1084 (p_ler_id => p_ler_id,
1085 p_effective_date => p_effective_date,
1086 p_object_version_number => p_object_version_number);
1087 --
1088 if ( (l_api_updating
1089 and nvl(p_typ_cd,hr_api.g_varchar2) <> ben_ler_shd.g_old_rec.typ_cd
1090 or not l_api_updating )
1091 and p_typ_cd = 'IREC' )
1092 then
1093 --
1094 open l_csr_ler;
1095 fetch l_csr_ler into l_dummy;
1096 if l_csr_ler%found
1097 then
1098 --
1099 close l_csr_ler;
1100 --
1101 --Raise error : Life event of Type = 'iRecruitment' already exists for the Business Group
1102 --
1103 fnd_message.set_name('BEN','BEN_93924_IREC_LER_EXIST_IN_BG');
1104 fnd_message.raise_error;
1105 --
1106 end if;
1107 --
1108 close l_csr_ler;
1109 --
1110 end if;
1111 --
1112 hr_utility.set_location('Leaving:'||l_proc,10);
1113 --
1114 end chk_irec_typ_cd_uniq_in_bg;
1115 --
1116
1117 --
1118 -- ----------------------------------------------------------------------------
1119 -- |------< chk_ck_rltd_per_elig_flag >------|
1120 -- ----------------------------------------------------------------------------
1121 --
1122 -- Description
1123 -- This procedure is used to check that the lookup value is valid.
1124 --
1125 -- Pre Conditions
1126 -- None.
1127 --
1128 -- In Parameters
1129 -- ler_id PK of record being inserted or updated.
1130 -- ck_rltd_per_elig_flag Value of lookup code.
1131 -- effective_date effective date
1132 -- object_version_number Object version number of record being
1133 -- inserted or updated.
1134 --
1135 -- Post Success
1136 -- Processing continues
1137 --
1138 -- Post Failure
1139 -- Error handled by procedure
1140 --
1141 -- Access Status
1142 -- Internal table handler use only.
1143 --
1144 Procedure chk_ck_rltd_per_elig_flag(p_ler_id in number,
1145 p_ck_rltd_per_elig_flag in varchar2,
1146 p_effective_date in date,
1147 p_object_version_number in number) is
1148 --
1149 l_proc varchar2(72) := g_package||'chk_ck_rltd_per_elig_flag';
1150 l_api_updating boolean;
1151 --
1152 Begin
1153 --
1154 hr_utility.set_location('Entering:'||l_proc, 5);
1155 --
1156 l_api_updating := ben_ler_shd.api_updating
1157 (p_ler_id => p_ler_id,
1158 p_effective_date => p_effective_date,
1159 p_object_version_number => p_object_version_number);
1160 --
1161 if (l_api_updating
1162 and p_ck_rltd_per_elig_flag
1163 <> nvl(ben_ler_shd.g_old_rec.ck_rltd_per_elig_flag,hr_api.g_varchar2)
1164 or not l_api_updating)
1165 and p_ck_rltd_per_elig_flag is not null then
1166 --
1167 -- check if value of lookup falls within lookup type.
1168 --
1169 if hr_api.not_exists_in_hr_lookups
1170 (p_lookup_type => 'YES_NO',
1171 p_lookup_code => p_ck_rltd_per_elig_flag,
1172 p_effective_date => p_effective_date) then
1173 --
1174 -- raise error as does not exist as lookup
1175 --
1176 fnd_message.set_name('BEN','BEN_91014_INV_CHK_RLTD_FLAG');
1177 fnd_message.raise_error;
1178 --
1179 end if;
1180 --
1181 end if;
1182 --
1183 hr_utility.set_location('Leaving:'||l_proc,10);
1184 --
1185 end chk_ck_rltd_per_elig_flag;
1186 -- ----------------------------------------------------------------------------
1187 -- |------< chk_cm_aply_flag >------|
1188 -- ----------------------------------------------------------------------------
1189 --
1190 -- Description
1191 -- This procedure is used to check that the lookup value is valid.
1192 --
1193 -- Pre Conditions
1194 -- None.
1195 --
1196 -- In Parameters
1197 -- ler_id PK of record being inserted or updated.
1198 -- cm_aply_flag Value of lookup code.
1199 -- effective_date effective date
1200 -- object_version_number Object version number of record being
1201 -- inserted or updated.
1202 --
1203 -- Post Success
1204 -- Processing continues
1205 --
1206 -- Post Failure
1207 -- Error handled by procedure
1208 --
1209 -- Access Status
1210 -- Internal table handler use only.
1211 --
1212 Procedure chk_cm_aply_flag(p_ler_id in number,
1213 p_cm_aply_flag in varchar2,
1214 p_typ_cd in varchar2,
1215 p_effective_date in date,
1216 p_object_version_number in number) is
1217 --
1218 l_proc varchar2(72) := g_package||'chk_cm_aply_flag';
1219 l_api_updating boolean;
1220 --
1221 Begin
1222 --
1223 hr_utility.set_location('Entering:'||l_proc, 5);
1224 --
1225 l_api_updating := ben_ler_shd.api_updating
1226 (p_ler_id => p_ler_id,
1227 p_effective_date => p_effective_date,
1228 p_object_version_number => p_object_version_number);
1229 --
1230 if (l_api_updating
1231 and p_cm_aply_flag
1232 <> nvl(ben_ler_shd.g_old_rec.cm_aply_flag,hr_api.g_varchar2)
1233 or not l_api_updating)
1234 and p_cm_aply_flag is not null then
1235 --
1236 -- check if value of lookup falls within lookup type.
1237 --
1238 if hr_api.not_exists_in_hr_lookups
1239 (p_lookup_type => 'YES_NO',
1240 p_lookup_code => p_cm_aply_flag,
1241 p_effective_date => p_effective_date) then
1242 --
1243 -- raise error as does not exist as lookup
1244 --
1245 fnd_message.set_name('BEN','BEN_91013_INV_CM_APLY_FLAG');
1246 fnd_message.raise_error;
1247 --
1248 end if;
1249 --
1250 end if;
1251 --
1252 -- If type-code is not 'Personal' then communications flag must be off.
1253 --
1254 if (p_typ_cd <> 'PRSNL' or p_typ_cd is null) and p_cm_aply_flag = 'Y' then
1255 --
1256 -- raise error as does not exist as lookup
1257 --
1258 fnd_message.set_name('BEN','BEN_91012_CM_APLY_FLAG_OFF');
1259 fnd_message.raise_error;
1260 --
1261 end if;
1262 --
1263 hr_utility.set_location('Leaving:'||l_proc,10);
1264 --
1265 end chk_cm_aply_flag;
1266 --
1267 -- ----------------------------------------------------------------------------
1268 -- |------< chk_name >------|
1269 -- ----------------------------------------------------------------------------
1270 --
1271 -- Description
1272 -- This procedure is used to check that the name field is unique.
1273 --
1274 -- Pre Conditions
1275 -- None.
1276 --
1277 -- In Parameters
1278 -- ler_id PK of record being inserted or updated.
1279 -- name Name of record being inserted or updated.
1280 -- business_group_id business_group
1281 -- object_version_number Object version number of record being
1282 -- inserted or updated.
1283 --
1284 -- Post Success
1285 -- Processing continues
1286 --
1287 -- Post Failure
1288 -- Errors handled by the procedure
1289 --
1290 -- Access Status
1291 -- Internal table handler use only.
1292 --
1293 Procedure chk_name(p_ler_id in number,
1294 p_name in varchar2,
1295 p_business_group_id in number,
1296 p_effective_date in date,
1297 p_validation_start_date in date,
1298 p_validation_end_date in date,
1299 p_object_version_number in number) is
1300
1301 -- Cursor selects non-unique names
1302 -- Note, we are allowing records with different keys to have the same
1303 -- name value as long as the two records are not 'effective' at any
1304 -- one time.
1305 CURSOR l_csr_ler IS
1306 SELECT name
1307 FROM ben_ler_f
1308 WHERE ler_id <> nvl(p_ler_id, hr_api.g_number)
1309 AND name = p_name
1310 AND business_group_id + 0 = p_business_group_id
1311 AND effective_end_date >= p_validation_start_date
1312 AND effective_start_date <= p_validation_end_date ;
1313 --
1314 l_db_ler_row l_csr_ler%rowtype;
1315 l_proc varchar2(72) := g_package||'chk_name';
1316 l_api_updating boolean;
1317 --
1318 Begin
1319 --
1320 hr_utility.set_location('Entering:'||l_proc, 5);
1321 --
1322 l_api_updating := ben_ler_shd.api_updating
1323 (p_ler_id => p_ler_id,
1324 p_effective_date => p_effective_date,
1325 p_object_version_number => p_object_version_number);
1326
1327 if (l_api_updating
1328 and nvl(p_name,hr_api.g_varchar2)
1329 <> ben_ler_shd.g_old_rec.name
1330 or not l_api_updating) then
1331
1332 open l_csr_ler ;
1333 fetch l_csr_ler into l_db_ler_row;
1334 if l_csr_ler%found then
1335 close l_csr_ler;
1336 --
1337 -- raise error as there is another record in database with same name.
1338 --
1339 ben_ler_shd.constraint_error('BEN_LER_UK1');
1340 end if;
1341 close l_csr_ler;
1342
1343 end if;
1344 --
1345 hr_utility.set_location('Leaving:'||l_proc, 10);
1346 --
1347 End chk_name;
1348 -- ----------------------------------------------------------------------------
1349 -- |------------------------< chk_ovridg_le_flag >----------------------------|
1350 -- ----------------------------------------------------------------------------
1351 --
1352 -- Description
1353 -- This procedure is used to check that the ovridg_le_flag is in lookup
1354 -- YES_NO
1355 --
1356 -- Pre Conditions
1357 -- None.
1358 --
1359 -- In Parameters
1360 -- ler_id PK of record being inserted or updated.
1361 -- ovridg_le_flag flag
1362 -- business_group_id business_group
1363 -- object_version_number Object version number of record being
1364 -- inserted or updated.
1365 --
1366 -- Post Success
1367 -- Processing continues
1368 --
1369 -- Post Failure
1370 -- Errors handled by the procedure
1371 --
1372 -- Access Status
1373 -- Internal table handler use only.
1374 --
1375 Procedure chk_ovridg_le_flag(p_ler_id in number,
1376 p_ovridg_le_flag in varchar2,
1377 p_effective_date in date,
1378 p_object_version_number in number) is
1379
1380 --
1381 l_proc varchar2(72) := g_package||'chk_ovridg_le_flag';
1382 l_api_updating boolean;
1383 --
1384 Begin
1385 --
1386 hr_utility.set_location('Entering:'||l_proc, 5);
1387 --
1388 l_api_updating := ben_ler_shd.api_updating
1389 (p_ler_id => p_ler_id,
1390 p_effective_date => p_effective_date,
1391 p_object_version_number => p_object_version_number);
1392
1393 if (l_api_updating
1394 and nvl(p_ovridg_le_flag,hr_api.g_varchar2)
1395 <> ben_ler_shd.g_old_rec.ovridg_le_flag
1396 or not l_api_updating) then
1397 --
1398 -- check if value of lookup falls within lookup type.
1399 --
1400 if hr_api.not_exists_in_hr_lookups
1401 (p_lookup_type => 'YES_NO',
1402 p_lookup_code => p_ovridg_le_flag,
1403 p_effective_date => p_effective_date) then
1404 --
1405 -- raise error as does not exist as lookup
1406 --
1407 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1408 fnd_message.set_token('FIELD','p_ovridg_le_flag');
1409 fnd_message.set_token('TYPE', 'YES_NO');
1410 fnd_message.raise_error;
1411 --
1412 end if;
1413 --
1414 end if;
1415 --
1416 hr_utility.set_location('Leaving:'||l_proc, 10);
1417 --
1418 End chk_ovridg_le_flag;
1419 --
1420 -- ----------------------------------------------------------------------------
1421 -- |------------------------< chk_qualg_evt_flag >----------------------------|
1422 -- ----------------------------------------------------------------------------
1423 --
1424 -- Description
1425 -- This procedure is used to check that the qualg_evt_flag is in lookup
1426 -- YES_NO
1427 --
1428 -- Pre Conditions
1429 -- None.
1430 --
1431 -- In Parameters
1432 -- ler_id PK of record being inserted or updated.
1433 -- qualg_evt_flag flag
1434 -- business_group_id business_group
1435 -- object_version_number Object version number of record being
1436 -- inserted or updated.
1437 --
1438 -- Post Success
1439 -- Processing continues
1440 --
1441 -- Post Failure
1442 -- Errors handled by the procedure
1443 --
1444 -- Access Status
1445 -- Internal table handler use only.
1446 --
1447 Procedure chk_qualg_evt_flag(p_ler_id in number,
1448 p_qualg_evt_flag in varchar2,
1449 p_effective_date in date,
1450 p_object_version_number in number) is
1451
1452 --
1453 l_proc varchar2(72) := g_package||'chk_qualg_evt_flag';
1454 l_api_updating boolean;
1455 --
1456 Begin
1457 --
1458 hr_utility.set_location('Entering:'||l_proc, 5);
1459 --
1460 l_api_updating := ben_ler_shd.api_updating
1461 (p_ler_id => p_ler_id,
1462 p_effective_date => p_effective_date,
1463 p_object_version_number => p_object_version_number);
1464
1465 if (l_api_updating
1466 and nvl(p_qualg_evt_flag,hr_api.g_varchar2)
1467 <> ben_ler_shd.g_old_rec.qualg_evt_flag
1468 or not l_api_updating) then
1469 --
1470 -- check if value of lookup falls within lookup type.
1471 --
1472 if hr_api.not_exists_in_hr_lookups
1473 (p_lookup_type => 'YES_NO',
1474 p_lookup_code => p_qualg_evt_flag,
1475 p_effective_date => p_effective_date) then
1476 --
1477 -- raise error as does not exist as lookup
1478 --
1479 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1480 fnd_message.set_token('FIELD','p_qualg_evt_flag');
1481 fnd_message.set_token('TYPE', 'YES_NO');
1482 fnd_message.raise_error;
1483 --
1484 end if;
1485 --
1486 end if;
1487 --
1488 hr_utility.set_location('Leaving:'||l_proc, 10);
1489 --
1490 End chk_qualg_evt_flag;
1491 --
1492 -- ----------------------------------------------------------------------------
1493 -- |---------------------------< chk_tmlns_eval_cd >--------------------------|
1494 -- ----------------------------------------------------------------------------
1495 --
1496 -- Description
1497 -- This procedure is used to check that the lookup value is valid.
1498 --
1499 -- Pre Conditions
1500 -- None.
1501 --
1502 -- In Parameters
1503 -- ler_id PK of record being inserted or updated.
1504 -- tmlns_eval_cd Value of lookup code.
1505 -- effective_date effective date
1506 -- object_version_number Object version number of record being
1507 -- inserted or updated.
1508 --
1509 -- Post Success
1510 -- Processing continues
1511 --
1512 -- Post Failure
1513 -- Error handled by procedure
1514 --
1515 -- Access Status
1516 -- Internal table handler use only.
1517 --
1518 Procedure chk_tmlns_eval_cd(p_ler_id in number,
1519 p_tmlns_eval_cd in varchar2,
1520 p_effective_date in date,
1521 p_object_version_number in number) is
1522 --
1523 l_proc varchar2(72) := g_package||'chk_tmlns_eval_cd';
1524 l_api_updating boolean;
1525 --
1526 Begin
1527 --
1528 hr_utility.set_location('Entering:'||l_proc, 5);
1529 --
1530 l_api_updating := ben_ler_shd.api_updating
1531 (p_ler_id => p_ler_id,
1532 p_effective_date => p_effective_date,
1533 p_object_version_number => p_object_version_number);
1534 --
1535 if (l_api_updating
1536 and p_tmlns_eval_cd
1537 <> nvl(ben_ler_shd.g_old_rec.tmlns_eval_cd,hr_api.g_varchar2)
1538 or not l_api_updating)
1539 and p_tmlns_eval_cd is not null then
1540 --
1541 -- check if value of lookup falls within lookup type.
1542 --
1543 if hr_api.not_exists_in_hr_lookups
1544 (p_lookup_type => 'BEN_LER_TMLNS_EVAL',
1545 p_lookup_code => p_tmlns_eval_cd,
1546 p_effective_date => p_effective_date) then
1547 --
1548 -- raise error as does not exist as lookup
1549 --
1550 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1551 fnd_message.set_token('FIELD','p_tmlns_eval_cd');
1552 fnd_message.set_token('TYPE','BEN_LER_TMLNS_EVAL');
1553 fnd_message.raise_error;
1554 --
1555 end if;
1556 --
1557 end if;
1558 --
1559 hr_utility.set_location('Leaving:'||l_proc,10);
1560 --
1561 end chk_tmlns_eval_cd;
1562 --
1563 -- ----------------------------------------------------------------------------
1564 -- |---------------------------< chk_tmlns_perd_cd >--------------------------|
1565 -- ----------------------------------------------------------------------------
1566 --
1567 -- Description
1568 -- This procedure is used to check that the lookup value is valid.
1569 --
1570 -- Pre Conditions
1571 -- None.
1572 --
1573 -- In Parameters
1574 -- ler_id PK of record being inserted or updated.
1575 -- tmlns_perd_cd Value of lookup code.
1576 -- effective_date effective date
1577 -- object_version_number Object version number of record being
1578 -- inserted or updated.
1579 --
1580 -- Post Success
1581 -- Processing continues
1582 --
1583 -- Post Failure
1584 -- Error handled by procedure
1585 --
1586 -- Access Status
1587 -- Internal table handler use only.
1588 --
1589 Procedure chk_tmlns_perd_cd(p_ler_id in number,
1590 p_tmlns_perd_cd in varchar2,
1591 p_effective_date in date,
1592 p_object_version_number in number) is
1593 --
1594 l_proc varchar2(72) := g_package||'chk_tmlns_perd_cd';
1595 l_api_updating boolean;
1596 --
1597 Begin
1598 --
1599 hr_utility.set_location('Entering:'||l_proc, 5);
1600 --
1601 l_api_updating := ben_ler_shd.api_updating
1602 (p_ler_id => p_ler_id,
1603 p_effective_date => p_effective_date,
1604 p_object_version_number => p_object_version_number);
1605 --
1606 if (l_api_updating
1607 and p_tmlns_perd_cd
1608 <> nvl(ben_ler_shd.g_old_rec.tmlns_perd_cd,hr_api.g_varchar2)
1609 or not l_api_updating)
1610 and p_tmlns_perd_cd is not null then
1611 --
1612 -- check if value of lookup falls within lookup type.
1613 --
1614 if hr_api.not_exists_in_hr_lookups
1615 (p_lookup_type => 'BEN_LER_TMLNS_PERD',
1616 p_lookup_code => p_tmlns_perd_cd,
1617 p_effective_date => p_effective_date) then
1618 --
1619 -- raise error as does not exist as lookup
1620 --
1621 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1622 fnd_message.set_token('FIELD','p_tmlns_perd_cd');
1623 fnd_message.set_token('TYPE','BEN_LER_TMLNS_PERD');
1624 fnd_message.raise_error;
1625 --
1626 end if;
1627 --
1628 end if;
1629 --
1630 hr_utility.set_location('Leaving:'||l_proc,10);
1631 --
1632 end chk_tmlns_perd_cd;
1633 --
1634 -- ----------------------------------------------------------------------------
1635 -- |-------------------------< chk_tmlns_perd_rl >----------------------------|
1636 -- ----------------------------------------------------------------------------
1637 --
1638 -- Description
1639 -- This procedure is used to check that the Formula Rule is valid.
1640 --
1641 -- Pre Conditions
1642 -- None.
1643 --
1644 -- In Parameters
1645 -- benefit_action_id PK of record being inserted or updated.
1646 -- tmlns_perd_rl Value of formula rule id.
1647 -- effective_date effective date
1648 -- object_version_number Object version number of record being
1649 -- inserted or updated.
1650 --
1651 -- Post Success
1652 -- Processing continues
1653 --
1654 -- Post Failure
1655 -- Error handled by procedure
1656 --
1657 -- Access Status
1658 -- Internal table handler use only.
1659 --
1660 Procedure chk_tmlns_perd_rl(p_ler_id in number,
1661 p_tmlns_perd_rl in number,
1662 p_business_group_id in number,
1663 p_effective_date in date,
1664 p_object_version_number in number) is
1665 --
1666 l_proc varchar2(72) := g_package||'chk_tmlns_perd_rl';
1667 l_api_updating boolean;
1668 --
1669 Begin
1670 --
1671 hr_utility.set_location('Entering:'||l_proc, 5);
1672 --
1673 l_api_updating := ben_ler_shd.api_updating
1674 (p_ler_id => p_ler_id,
1675 p_object_version_number => p_object_version_number,
1676 p_effective_date => p_effective_date);
1677 --
1678 if (l_api_updating
1679 and nvl(p_tmlns_perd_rl,hr_api.g_number)
1680 <> ben_ler_shd.g_old_rec.tmlns_perd_rl
1681 or not l_api_updating)
1682 and p_tmlns_perd_rl is not null then
1683 --
1684 -- check if value of formula rule is valid.
1685 --
1686 if not benutils.formula_exists
1687 (p_formula_id => p_tmlns_perd_rl,
1688 p_formula_type_id => -453,
1689 p_business_group_id => p_business_group_id,
1690 p_effective_date => p_effective_date) then
1691 --
1692 -- raise error
1693 --
1694 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1695 fnd_message.set_token('ID',p_tmlns_perd_rl);
1696 fnd_message.set_token('TYPE_ID',-453);
1697 fnd_message.raise_error;
1698 --
1699 end if;
1700 --
1701 end if;
1702 --
1703 hr_utility.set_location('Leaving:'||l_proc,10);
1704 --
1705 end chk_tmlns_perd_rl;
1706 -- ----------------------------------------------------------------------------
1707 -- |---------------------------< chk_slf_svc_cd_qlfg_evt >----------------------|
1708 -- ----------------------------------------------------------------------------
1709 --
1710 -- Description
1711 -- This procedure is used to check that the qlfg evt flag is set when slf svc cd is COBRA , REGCOBRA
1712 --
1713 -- Pre Conditions
1714 -- None.
1715 --
1716 -- In Parameters
1717 -- ler_id PK of record being inserted or updated.
1718 -- slctbl_slf_svc_cd Value of lookup code.
1719 -- qualg_evt_flag .
1720 -- effective_date effective date
1721 -- object_version_number Object version number of record being
1722 -- inserted or updated.
1723 --
1724 -- Post Success
1725 -- Processing continues
1726 --
1727 -- Post Failure
1728 -- Error handled by procedure
1729 --
1730 -- Access Status
1731 -- Internal table handler use only.
1732 --
1733 Procedure chk_slf_svc_cd_qlfg_evt (p_ler_id in number,
1734 p_slctbl_slf_svc_cd in varchar2,
1735 p_qualg_evt_flag in varchar2 ,
1736 p_effective_date in date,
1737 p_object_version_number in number) is
1738 --
1739 l_proc varchar2(72) := g_package||'chk_slf_svc_cd_qlfg_evt' ;
1740 l_api_updating boolean;
1741 --
1742 Begin
1743 --
1744 hr_utility.set_location('Entering:'||l_proc, 5);
1745 --
1746 l_api_updating := ben_ler_shd.api_updating
1747 (p_ler_id => p_ler_id,
1748 p_effective_date => p_effective_date,
1749 p_object_version_number => p_object_version_number);
1750 --
1751 if (l_api_updating
1752 and ( p_slctbl_slf_svc_cd <> nvl(ben_ler_shd.g_old_rec.slctbl_slf_svc_cd ,hr_api.g_varchar2)
1753 or p_qualg_evt_flag <> nvl(ben_ler_shd.g_old_rec.qualg_evt_flag , hr_api.g_varchar2) )
1754 or not l_api_updating)
1755 and p_slctbl_slf_svc_cd is not null then
1756 --
1757 -- if value of p_slctbl_slf_svc_cd is in (COBRA , REGCOBRA) then p_qualg_evt_flag should be Y , else throw an error.
1758 --
1759 if p_slctbl_slf_svc_cd in ('COBRA' , 'REGCOBRA' ) then
1760 if p_qualg_evt_flag = 'N' then
1761 --
1762 -- raise error
1763 --
1764 fnd_message.set_name('BEN','BEN_92959_QLFG_EVT_FLAG_UNCHK');
1765 fnd_message.raise_error;
1766 --
1767 else
1768 -- valid data entered
1769 null ;
1770 end if;
1771 end if ;
1772 end if ;
1773 --
1774 hr_utility.set_location('Leaving:'||l_proc,10);
1775 --
1776 end chk_slf_svc_cd_qlfg_evt ;
1777 -- ----------------------------------------------------------------------------
1778 -- |--------------------------< dt_update_validate >--------------------------|
1779 -- ----------------------------------------------------------------------------
1780 -- {Start Of Comments}
1781 --
1782 -- Description:
1783 -- This procedure is used for referential integrity of datetracked
1784 -- parent entities when a datetrack update operation is taking place
1785 -- and where there is no cascading of update defined for this entity.
1786 --
1787 -- Prerequisites:
1788 -- This procedure is called from the update_validate.
1789 --
1790 -- In Parameters:
1791 --
1792 -- Post Success:
1793 -- Processing continues.
1794 --
1795 -- Post Failure:
1796 --
1797 -- Developer Implementation Notes:
1798 -- This procedure should not need maintenance unless the HR Schema model
1799 -- changes.
1800 --
1801 -- Access Status:
1802 -- Internal Row Handler Use Only.
1803 --
1804 -- {End Of Comments}
1805 -- ----------------------------------------------------------------------------
1806 Procedure dt_update_validate
1807 (p_formula_id in number default hr_api.g_number,
1808 p_datetrack_mode in varchar2,
1809 p_validation_start_date in date,
1810 p_validation_end_date in date) Is
1811 --
1812 l_proc varchar2(72) := g_package||'dt_update_validate';
1813 l_integrity_error Exception;
1814 l_table_name all_tables.table_name%TYPE;
1815 --
1816 Begin
1817 hr_utility.set_location('Entering:'||l_proc, 5);
1818 --
1819 -- Ensure that the p_datetrack_mode argument is not null
1820 --
1821 hr_api.mandatory_arg_error
1822 (p_api_name => l_proc,
1823 p_argument => 'datetrack_mode',
1824 p_argument_value => p_datetrack_mode);
1825 --
1826 -- Only perform the validation if the datetrack update mode is valid
1827 --
1828 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
1829 --
1830 --
1831 -- Ensure the arguments are not null
1832 --
1833 hr_api.mandatory_arg_error
1834 (p_api_name => l_proc,
1835 p_argument => 'validation_start_date',
1836 p_argument_value => p_validation_start_date);
1837 --
1838 hr_api.mandatory_arg_error
1839 (p_api_name => l_proc,
1840 p_argument => 'validation_end_date',
1841 p_argument_value => p_validation_end_date);
1842 --
1843 If ((nvl(p_formula_id, hr_api.g_number) <> hr_api.g_number) and
1844 NOT (dt_api.check_min_max_dates
1845 (p_base_table_name => 'ff_formulas_f',
1846 p_base_key_column => 'formula_id',
1847 p_base_key_value => p_formula_id,
1848 p_from_date => p_validation_start_date,
1849 p_to_date => p_validation_end_date))) Then
1850 l_table_name := 'ff_formulas_f';
1851 Raise l_integrity_error;
1852 End If;
1853 --
1854 End If;
1855 --
1856 hr_utility.set_location(' Leaving:'||l_proc, 10);
1857 Exception
1858 When l_integrity_error Then
1859 --
1860 -- A referential integrity check was violated therefore
1861 -- we must error
1862 --
1863 ben_utility.parent_integrity_error(p_table_name => l_table_name);
1864 When Others Then
1865 --
1866 -- An unhandled or unexpected error has occurred which
1867 -- we must report
1868 --
1869 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1870 fnd_message.set_token('PROCEDURE', l_proc);
1871 fnd_message.set_token('STEP','15');
1872 fnd_message.raise_error;
1873 End dt_update_validate;
1874 --
1875 -- ----------------------------------------------------------------------------
1876 -- |--------------------------< dt_delete_validate >--------------------------|
1877 -- ----------------------------------------------------------------------------
1878 -- {Start Of Comments}
1879 --
1880 -- Description:
1881 -- This procedure is used for referential integrity of datetracked
1882 -- child entities when either a datetrack DELETE or ZAP is in operation
1883 -- and where there is no cascading of delete defined for this entity.
1884 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1885 -- datetracked child rows exist between the validation start and end
1886 -- dates.
1887 --
1888 -- Prerequisites:
1889 -- This procedure is called from the delete_validate.
1890 --
1891 -- In Parameters:
1892 --
1893 -- Post Success:
1894 -- Processing continues.
1895 --
1896 -- Post Failure:
1897 --
1898 -- Developer Implementation Notes:
1899 -- This procedure should not need maintenance unless the HR Schema model
1900 -- changes.
1901 --
1902 -- Access Status:
1903 -- Internal Row Handler Use Only.
1904 --
1905 -- {End Of Comments}
1906 -- ----------------------------------------------------------------------------
1907 Procedure dt_delete_validate
1908 (p_ler_id in number,
1909 p_datetrack_mode in varchar2,
1910 p_validation_start_date in date,
1911 p_validation_end_date in date) Is
1912 --
1913 l_proc varchar2(72) := g_package||'dt_delete_validate';
1914 l_table_name all_tables.table_name%TYPE;
1915 --
1916 Begin
1917 hr_utility.set_location('Entering:'||l_proc, 5);
1918 --
1919 -- Ensure that the p_datetrack_mode argument is not null
1920 --
1921 hr_api.mandatory_arg_error
1922 (p_api_name => l_proc,
1923 p_argument => 'datetrack_mode',
1924 p_argument_value => p_datetrack_mode);
1925 --
1926 -- Only perform the validation if the datetrack mode is either
1927 -- DELETE or ZAP
1928 --
1929 If (p_datetrack_mode = 'DELETE' or
1930 p_datetrack_mode = 'ZAP') then
1931 --
1932 --
1933 -- Ensure the arguments are not null
1934 --
1935 hr_api.mandatory_arg_error
1936 (p_api_name => l_proc,
1937 p_argument => 'validation_start_date',
1938 p_argument_value => p_validation_start_date);
1939 --
1940 hr_api.mandatory_arg_error
1941 (p_api_name => l_proc,
1942 p_argument => 'validation_end_date',
1943 p_argument_value => p_validation_end_date);
1944 --
1945 hr_api.mandatory_arg_error
1946 (p_api_name => l_proc,
1947 p_argument => 'ler_id',
1948 p_argument_value => p_ler_id);
1949 --
1950 -- Check whether the row being deleted is a seeded life event. If yes, the procedure
1951 -- will return an error messge.
1952 chk_seeded_life(p_ler_id);
1953 -- Call a routine to check to see if any child rows exist. This
1954 -- procedure will return an error message if any children exist.
1955 --
1956 chk_child_rows
1957 (p_ler_id => p_ler_id,
1958 p_validation_start_date => p_validation_start_date,
1959 p_validation_end_date => p_validation_end_date,
1960 p_delete_flag => 'Y',
1961 p_table_name => l_table_name);
1962
1963 --
1964 End If;
1965 --
1966 hr_utility.set_location(' Leaving:'||l_proc, 10);
1967 Exception
1968 WHEN hr_utility.hr_error THEN
1969 raise;
1970 When Others Then
1971 --
1972 -- An unhandled or unexpected error has occurred which
1973 -- we must report
1974 --
1975 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1976 fnd_message.set_token('PROCEDURE', l_proc);
1977 fnd_message.set_token('STEP','15');
1978 fnd_message.raise_error;
1979 End dt_delete_validate;
1980 --
1981 -- ----------------------------------------------------------------------------
1982 -- |---------------------------< insert_validate >----------------------------|
1983 -- ----------------------------------------------------------------------------
1984 Procedure insert_validate
1985 (p_rec in ben_ler_shd.g_rec_type,
1986 p_effective_date in date,
1987 p_datetrack_mode in varchar2,
1988 p_validation_start_date in date,
1989 p_validation_end_date in date) is
1990 --
1991 l_proc varchar2(72) := g_package||'insert_validate';
1992 --
1993 Begin
1994 hr_utility.set_location('Entering:'||l_proc, 5);
1995 --
1996 -- Call all supporting business operations
1997 --
1998 --
1999 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2000 --
2001 chk_ler_id
2002 (p_ler_id => p_rec.ler_id,
2003 p_effective_date => p_effective_date,
2004 p_object_version_number => p_rec.object_version_number);
2005 --
2006 chk_whn_to_prcs_cd
2007 (p_ler_id => p_rec.ler_id,
2008 p_whn_to_prcs_cd => p_rec.whn_to_prcs_cd,
2009 p_effective_date => p_effective_date,
2010 p_object_version_number => p_rec.object_version_number);
2011 --
2012 chk_tmlns_eval_cd
2013 (p_ler_id => p_rec.ler_id,
2014 p_tmlns_eval_cd => p_rec.tmlns_eval_cd,
2015 p_effective_date => p_effective_date,
2016 p_object_version_number => p_rec.object_version_number);
2017 --
2018 chk_tmlns_perd_cd
2019 (p_ler_id => p_rec.ler_id,
2020 p_tmlns_perd_cd => p_rec.tmlns_perd_cd,
2021 p_effective_date => p_effective_date,
2022 p_object_version_number => p_rec.object_version_number);
2023 --
2024 chk_tmlns_perd_rl
2025 (p_ler_id => p_rec.ler_id,
2026 p_tmlns_perd_rl => p_rec.tmlns_perd_rl,
2027 p_business_group_id => p_rec.business_group_id,
2028 p_effective_date => p_effective_date,
2029 p_object_version_number => p_rec.object_version_number);
2030 --
2031 chk_ler_eval_rl
2032 (p_ler_id => p_rec.ler_id,
2033 p_ler_eval_rl => p_rec.ler_eval_rl,
2034 p_business_group_id => p_rec.business_group_id,
2035 p_effective_date => p_effective_date,
2036 p_object_version_number => p_rec.object_version_number);
2037 --
2038 chk_ptnl_ler_trtmt_cd
2039 (p_ler_id => p_rec.ler_id,
2040 p_ptnl_ler_trtmt_cd => p_rec.ptnl_ler_trtmt_cd,
2041 p_effective_date => p_effective_date,
2042 p_object_version_number => p_rec.object_version_number);
2043 --
2044 chk_ptnl_ler_trtmt_cd_seed_ler
2045 (p_ler_id => p_rec.ler_id,
2046 p_ptnl_ler_trtmt_cd => p_rec.ptnl_ler_trtmt_cd,
2047 p_effective_date => p_effective_date,
2048 p_object_version_number => p_rec.object_version_number,
2049 p_typ_cd => p_rec.typ_cd);
2050 --
2051 chk_lf_evt_oper_cd
2052 (p_ler_id => p_rec.ler_id,
2053 p_lf_evt_oper_cd => p_rec.lf_evt_oper_cd,
2054 p_business_group_id => p_rec.business_group_id,
2055 p_effective_date => p_effective_date,
2056 p_object_version_number => p_rec.object_version_number,
2057 p_validation_start_date => p_validation_start_date,
2058 p_validation_end_date => p_validation_end_date,
2059 p_typ_cd => p_rec.typ_cd);
2060 --
2061 chk_slctbl_slf_svc_cd
2062 (p_ler_id => p_rec.ler_id,
2063 p_slctbl_slf_svc_cd => p_rec.slctbl_slf_svc_cd,
2064 p_effective_date => p_effective_date,
2065 p_object_version_number => p_rec.object_version_number);
2066 --
2067 chk_typ_cd_not_null
2068 (p_ler_id => p_rec.ler_id,
2069 p_typ_cd => p_rec.typ_cd,
2070 p_effective_date => p_effective_date,
2071 p_object_version_number => p_rec.object_version_number);
2072 --
2073 chk_typ_cd
2074 (p_ler_id => p_rec.ler_id,
2075 p_typ_cd => p_rec.typ_cd,
2076 p_business_group_id => p_rec.business_group_id,
2077 p_effective_date => p_effective_date,
2078 p_object_version_number => p_rec.object_version_number,
2079 p_validation_start_date => p_validation_start_date,
2080 p_validation_end_date => p_validation_end_date);
2081 --
2082 --iRec
2083 chk_irec_typ_cd_uniq_in_bg
2084 (p_ler_id => p_rec.ler_id,
2085 p_typ_cd => p_rec.typ_cd,
2086 p_business_group_id => p_rec.business_group_id,
2087 p_effective_date => p_effective_date,
2088 p_object_version_number => p_rec.object_version_number,
2089 p_validation_start_date => p_validation_start_date,
2090 p_validation_end_date => p_validation_end_date);
2091 --iRec
2092 --
2093 chk_ck_rltd_per_elig_flag
2094 (p_ler_id => p_rec.ler_id,
2095 p_ck_rltd_per_elig_flag => p_rec.ck_rltd_per_elig_flag,
2096 p_effective_date => p_effective_date,
2097 p_object_version_number => p_rec.object_version_number);
2098 --
2099 -- we will be deleting this flag after beta 1, don't check it's value.
2100 --chk_cm_aply_flag
2101 --(p_ler_id => p_rec.ler_id,
2102 -- p_cm_aply_flag => p_rec.cm_aply_flag,
2103 -- p_typ_cd => p_rec.typ_cd,
2104 -- p_effective_date => p_effective_date,
2105 -- p_object_version_number => p_rec.object_version_number);
2106 --
2107 chk_name
2108 (p_ler_id => p_rec.ler_id,
2109 p_name => p_rec.name,
2110 p_business_group_id => p_rec.business_group_id,
2111 p_effective_date => p_effective_date,
2112 p_validation_start_date => p_validation_start_date,
2113 p_validation_end_date => p_validation_end_date,
2114 p_object_version_number => p_rec.object_version_number);
2115 --
2116 chk_ovridg_le_flag
2117 (p_ler_id => p_rec.ler_id,
2118 p_ovridg_le_flag => p_rec.ovridg_le_flag,
2119 p_effective_date => p_effective_date,
2120 p_object_version_number => p_rec.object_version_number);
2121 --
2122 chk_qualg_evt_flag
2123 (p_ler_id => p_rec.ler_id,
2124 p_qualg_evt_flag => p_rec.qualg_evt_flag,
2125 p_effective_date => p_effective_date,
2126 p_object_version_number => p_rec.object_version_number);
2127 --
2128 chk_slf_svc_cd_qlfg_evt
2129 (p_ler_id => p_rec.ler_id,
2130 p_slctbl_slf_svc_cd => p_rec.slctbl_slf_svc_cd,
2131 p_qualg_evt_flag => p_rec.qualg_evt_flag,
2132 p_effective_date => p_effective_date,
2133 p_object_version_number => p_rec.object_version_number) ;
2134 --
2135 hr_utility.set_location(' Leaving:'||l_proc, 10);
2136 End insert_validate;
2137 --
2138 -- ----------------------------------------------------------------------------
2139 -- |---------------------------< update_validate >----------------------------|
2140 -- ----------------------------------------------------------------------------
2141 Procedure update_validate
2142 (p_rec in ben_ler_shd.g_rec_type,
2143 p_effective_date in date,
2144 p_datetrack_mode in varchar2,
2145 p_validation_start_date in date,
2146 p_validation_end_date in date) is
2147 --
2148 l_proc varchar2(72) := g_package||'update_validate';
2149 --
2150 Begin
2151 hr_utility.set_location('Entering:'||l_proc, 5);
2152 --
2153 -- Call all supporting business operations
2154 --
2155 --
2156 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2157 --
2158 chk_ler_id
2159 (p_ler_id => p_rec.ler_id,
2160 p_effective_date => p_effective_date,
2161 p_object_version_number => p_rec.object_version_number);
2162 --
2163 chk_whn_to_prcs_cd
2164 (p_ler_id => p_rec.ler_id,
2165 p_whn_to_prcs_cd => p_rec.whn_to_prcs_cd,
2166 p_effective_date => p_effective_date,
2167 p_object_version_number => p_rec.object_version_number);
2168 --
2169 chk_tmlns_eval_cd
2170 (p_ler_id => p_rec.ler_id,
2171 p_tmlns_eval_cd => p_rec.tmlns_eval_cd,
2172 p_effective_date => p_effective_date,
2173 p_object_version_number => p_rec.object_version_number);
2174 --
2175 chk_tmlns_perd_cd
2176 (p_ler_id => p_rec.ler_id,
2177 p_tmlns_perd_cd => p_rec.tmlns_perd_cd,
2178 p_effective_date => p_effective_date,
2179 p_object_version_number => p_rec.object_version_number);
2180 --
2181 chk_tmlns_perd_rl
2182 (p_ler_id => p_rec.ler_id,
2183 p_tmlns_perd_rl => p_rec.tmlns_perd_rl,
2184 p_business_group_id => p_rec.business_group_id,
2185 p_effective_date => p_effective_date,
2186 p_object_version_number => p_rec.object_version_number);
2187 --
2188 chk_ler_eval_rl
2189 (p_ler_id => p_rec.ler_id,
2190 p_ler_eval_rl => p_rec.ler_eval_rl,
2191 p_business_group_id => p_rec.business_group_id,
2192 p_effective_date => p_effective_date,
2193 p_object_version_number => p_rec.object_version_number);
2194 --
2195 chk_ptnl_ler_trtmt_cd
2196 (p_ler_id => p_rec.ler_id,
2197 p_ptnl_ler_trtmt_cd => p_rec.ptnl_ler_trtmt_cd,
2198 p_effective_date => p_effective_date,
2199 p_object_version_number => p_rec.object_version_number);
2200 --
2201 chk_ptnl_ler_trtmt_cd_seed_ler
2202 (p_ler_id => p_rec.ler_id,
2203 p_ptnl_ler_trtmt_cd => p_rec.ptnl_ler_trtmt_cd,
2204 p_effective_date => p_effective_date,
2205 p_object_version_number => p_rec.object_version_number,
2206 p_typ_cd => p_rec.typ_cd);
2207 --
2208 chk_lf_evt_oper_cd
2209 (p_ler_id => p_rec.ler_id,
2210 p_lf_evt_oper_cd => p_rec.lf_evt_oper_cd,
2211 p_business_group_id => p_rec.business_group_id,
2212 p_effective_date => p_effective_date,
2213 p_object_version_number => p_rec.object_version_number,
2214 p_validation_start_date => p_validation_start_date,
2215 p_validation_end_date => p_validation_end_date,
2216 p_typ_cd => p_rec.typ_cd); --
2217 chk_slctbl_slf_svc_cd
2218 (p_ler_id => p_rec.ler_id,
2219 p_slctbl_slf_svc_cd => p_rec.slctbl_slf_svc_cd,
2220 p_effective_date => p_effective_date,
2221 p_object_version_number => p_rec.object_version_number);
2222
2223 --
2224 chk_typ_cd
2225 (p_ler_id => p_rec.ler_id,
2226 p_typ_cd => p_rec.typ_cd,
2227 p_business_group_id => p_rec.business_group_id,
2228 p_effective_date => p_effective_date,
2229 p_object_version_number => p_rec.object_version_number,
2230 p_validation_start_date => p_validation_start_date,
2231 p_validation_end_date => p_validation_end_date);
2232 --
2233 --iRec
2234 chk_irec_typ_cd_uniq_in_bg
2235 (p_ler_id => p_rec.ler_id,
2236 p_typ_cd => p_rec.typ_cd,
2237 p_business_group_id => p_rec.business_group_id,
2238 p_effective_date => p_effective_date,
2239 p_object_version_number => p_rec.object_version_number,
2240 p_validation_start_date => p_validation_start_date,
2241 p_validation_end_date => p_validation_end_date);
2242 --iRec
2243
2244 --
2245 chk_ck_rltd_per_elig_flag
2246 (p_ler_id => p_rec.ler_id,
2247 p_ck_rltd_per_elig_flag => p_rec.ck_rltd_per_elig_flag,
2248 p_effective_date => p_effective_date,
2249 p_object_version_number => p_rec.object_version_number);
2250 --
2251 --chk_cm_aply_flag
2252 --(p_ler_id => p_rec.ler_id,
2253 -- p_cm_aply_flag => p_rec.cm_aply_flag,
2254 -- p_typ_cd => p_rec.typ_cd,
2255 -- p_effective_date => p_effective_date,
2256 -- p_object_version_number => p_rec.object_version_number);
2257 --
2258 chk_name
2259 (p_ler_id => p_rec.ler_id,
2260 p_name => p_rec.name,
2261 p_business_group_id => p_rec.business_group_id,
2262 p_effective_date => p_effective_date,
2263 p_validation_start_date => p_validation_start_date,
2264 p_validation_end_date => p_validation_end_date,
2265 p_object_version_number => p_rec.object_version_number);
2266 --
2267 chk_ovridg_le_flag
2268 (p_ler_id => p_rec.ler_id,
2269 p_ovridg_le_flag => p_rec.ovridg_le_flag,
2270 p_effective_date => p_effective_date,
2271 p_object_version_number => p_rec.object_version_number);
2272 --
2273 chk_qualg_evt_flag
2274 (p_ler_id => p_rec.ler_id,
2275 p_qualg_evt_flag => p_rec.qualg_evt_flag,
2276 p_effective_date => p_effective_date,
2277 p_object_version_number => p_rec.object_version_number);
2278 --
2279 chk_slf_svc_cd_qlfg_evt
2280 (p_ler_id => p_rec.ler_id,
2281 p_slctbl_slf_svc_cd => p_rec.slctbl_slf_svc_cd,
2282 p_qualg_evt_flag => p_rec.qualg_evt_flag,
2283 p_effective_date => p_effective_date,
2284 p_object_version_number => p_rec.object_version_number) ;
2285 --
2286 -- Call the datetrack update integrity operation
2287 --
2288 dt_update_validate
2289 (p_formula_id => p_rec.ler_eval_rl,
2290 p_datetrack_mode => p_datetrack_mode,
2291 p_validation_start_date => p_validation_start_date,
2292 p_validation_end_date => p_validation_end_date);
2293 --
2294 hr_utility.set_location(' Leaving:'||l_proc, 10);
2295 End update_validate;
2296 --
2297 -- ----------------------------------------------------------------------------
2298 -- |---------------------------< delete_validate >----------------------------|
2299 -- ----------------------------------------------------------------------------
2300 Procedure delete_validate
2301 (p_rec in ben_ler_shd.g_rec_type,
2302 p_effective_date in date,
2303 p_datetrack_mode in varchar2,
2304 p_validation_start_date in date,
2305 p_validation_end_date in date) is
2306 --
2307 l_proc varchar2(72) := g_package||'delete_validate';
2308 --
2309 Begin
2310 hr_utility.set_location('Entering:'||l_proc, 5);
2311 --
2312 -- Call all supporting business operations
2313 --
2314 dt_delete_validate
2315 (p_datetrack_mode => p_datetrack_mode,
2316 p_validation_start_date => p_validation_start_date,
2317 p_validation_end_date => p_validation_end_date,
2318 p_ler_id => p_rec.ler_id);
2319 --
2320 hr_utility.set_location(' Leaving:'||l_proc, 10);
2321 End delete_validate;
2322 --
2323 --
2324 -- ---------------------------------------------------------------------------
2325 -- |---------------------< return_legislation_code >-------------------------|
2326 -- ---------------------------------------------------------------------------
2327 --
2328 function return_legislation_code
2329 (p_ler_id in number) return varchar2 is
2330 --
2331 -- Declare cursor
2332 --
2333 cursor csr_leg_code is
2334 select a.legislation_code
2335 from per_business_groups a,
2336 ben_ler_f b
2337 where b.ler_id = p_ler_id
2338 and a.business_group_id = b.business_group_id;
2339 --
2340 -- Declare local variables
2341 --
2342 l_legislation_code varchar2(150);
2343 l_proc varchar2(72) := g_package||'return_legislation_code';
2344 --
2345 begin
2346 --
2347 hr_utility.set_location('Entering:'|| l_proc, 10);
2348 --
2349 -- Ensure that all the mandatory parameter are not null
2350 --
2351 hr_api.mandatory_arg_error(p_api_name => l_proc,
2352 p_argument => 'ler_id',
2353 p_argument_value => p_ler_id);
2354 --
2355 open csr_leg_code;
2356 --
2357 fetch csr_leg_code into l_legislation_code;
2358 --
2359 if csr_leg_code%notfound then
2360 --
2361 close csr_leg_code;
2362 --
2363 -- The primary key is invalid therefore we must error
2364 --
2365 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
2366 fnd_message.raise_error;
2367 --
2368 end if;
2369 --
2370 close csr_leg_code;
2371 --
2372 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2373 --
2374 return l_legislation_code;
2375 --
2376 end return_legislation_code;
2377 --
2378
2379 --
2380 end ben_ler_bus;