[Home] [Help]
PACKAGE BODY: APPS.BEN_LDC_BUS
Source
1 Package Body ben_ldc_bus as
2 /* $Header: beldcrhi.pkb 120.0.12010000.2 2008/08/05 14:28:29 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ldc_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_ler_chg_dpnt_cvg_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 -- ler_chg_dpnt_cvg_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_ler_chg_dpnt_cvg_id(p_ler_chg_dpnt_cvg_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_ler_chg_dpnt_cvg_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_ldc_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_ler_chg_dpnt_cvg_id => p_ler_chg_dpnt_cvg_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_ler_chg_dpnt_cvg_id,hr_api.g_number)
55 <> ben_ldc_shd.g_old_rec.ler_chg_dpnt_cvg_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_ldc_shd.constraint_error('BEN_LER_CHG_DPNT_CVG_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_ler_chg_dpnt_cvg_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_ldc_shd.constraint_error('BEN_LER_CHG_DPNT_CVG_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_ler_chg_dpnt_cvg_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_ler_id >------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to ensure that the value is unique within parent
85 -- and within bus grp. The FK check is done elsewhere in the dt logic.
86 --
87 -- Pre Conditions
88 -- None.
89 --
90 -- In Parameters
91 -- effective_date effective date
92 -- object_version_number Object version number of record being
93 -- inserted or updated.
94 --
95 -- Post Success
96 -- Processing continues
97 --
98 -- Post Failure
99 -- Error handled by procedure
100 --
101 -- Access Status
102 -- Internal table handler use only.
103 --
104 Procedure chk_ler_id(p_ler_chg_dpnt_cvg_id in number,
105 p_ler_id in number,
106 p_pgm_id in number,
107 p_ptip_id in number,
108 p_pl_id in number,
109 p_effective_date in date,
110 p_validation_start_date in date,
111 p_validation_end_date in date,
112 p_business_group_id in number,
113 p_object_version_number in number) is
114
115
116 --
117 l_proc varchar2(72) := g_package||'chk_ler_id';
118 l_api_updating boolean;
119 l_exists varchar2(1);
120 --
121 -- unique in bg, parent, and eff dates
122 --
123 cursor chk_unique is
124 select null
125 from ben_ler_chg_dpnt_cvg_f
126 where ler_id = p_ler_id
127 and ler_chg_dpnt_cvg_id <> nvl(p_ler_chg_dpnt_cvg_id, hr_api.g_number)
128 and (ptip_id = p_ptip_id or pgm_id = p_pgm_id or pl_id = p_pl_id)
129 and business_group_id + 0 = p_business_group_id
130 and p_validation_start_date <= effective_end_date
131 and p_validation_end_date >= effective_start_date;
132 --
133 Begin
134 --
135 hr_utility.set_location('Entering:'||l_proc, 5);
136 --
137 l_api_updating := ben_ldc_shd.api_updating
138 (p_ler_chg_dpnt_cvg_id => p_ler_chg_dpnt_cvg_id ,
139 p_effective_date => p_effective_date,
140 p_object_version_number => p_object_version_number);
141 --
142 if (l_api_updating
143 and p_ler_id
144 <> nvl(ben_ldc_shd.g_old_rec.ler_id,hr_api.g_number)
145 or not l_api_updating) then
146 --
147 -- this value must be unique
148 --
149 open chk_unique;
150 fetch chk_unique into l_exists;
151 if chk_unique%found then
152 close chk_unique;
153 --
154 -- raise error as UK1 is violated
155 --
156 fnd_message.set_name('PAY','VALUE IS NOT UNIQUE');
157 fnd_message.raise_error;
158 --
159 end if;
160 --
161 close chk_unique;
162 --
163 end if;
164 --
165 hr_utility.set_location('Leaving:'||l_proc,10);
166 --
167 end chk_ler_id;
168 --
169
170 -- ----------------------------------------------------------------------------
171 -- |------< chk_cvg_eff_end_rl >------|
172 -- ----------------------------------------------------------------------------
173 --
174 -- Description
175 -- This procedure is used to check that the Formula Rule is valid.
176 --
177 -- Pre Conditions
178 -- None.
179 --
180 -- In Parameters
181 -- ler_chg_dpnt_cvg_id PK of record being inserted or updated.
182 -- cvg_eff_end_rl Value of formula rule id.
183 -- effective_date effective date
184 -- object_version_number Object version number of record being
185 -- inserted or updated.
186 --
187 -- Post Success
188 -- Processing continues
189 --
190 -- Post Failure
191 -- Error handled by procedure
192 --
193 -- Access Status
194 -- Internal table handler use only.
195 --
196 Procedure chk_cvg_eff_end_rl(p_ler_chg_dpnt_cvg_id in number,
197 p_cvg_eff_end_rl in number,
198 p_effective_date in date,
199 p_business_group_id in number,
200 p_object_version_number in number) is
201 --
202 l_proc varchar2(72) := g_package||'chk_cvg_eff_end_rl';
203 l_api_updating boolean;
204 l_dummy varchar2(1);
205 --
206 cursor c1 is
207 select null
208 from ff_formulas_f ff
209 ,per_business_groups pbg
210 where ff.formula_id = p_cvg_eff_end_rl
211 and ff.formula_type_id = -28 --Dependent Coverage End
212 and pbg.business_group_id = p_business_group_id
213 and nvl(ff.business_group_id, p_business_group_id) =
214 p_business_group_id
215 and nvl(ff.legislation_code, pbg.legislation_code) =
216 pbg.legislation_code
217 and p_effective_date
218 between ff.effective_start_date
219 and ff.effective_end_date;
220 --
221 Begin
222 --
223 hr_utility.set_location('Entering:'||l_proc, 5);
224 --
225 l_api_updating := ben_ldc_shd.api_updating
226 (p_ler_chg_dpnt_cvg_id => p_ler_chg_dpnt_cvg_id,
227 p_effective_date => p_effective_date,
228 p_object_version_number => p_object_version_number);
229 --
230 if (l_api_updating
231 and nvl(p_cvg_eff_end_rl,hr_api.g_number)
232 <> ben_ldc_shd.g_old_rec.cvg_eff_end_rl
233 or not l_api_updating)
234 and p_cvg_eff_end_rl is not null then
235 --
236 -- check if value of formula rule is valid.
237 --
238 open c1;
239 --
240 -- fetch value from cursor if it returns a record then the
241 -- formula is valid otherwise its invalid
242 --
243 fetch c1 into l_dummy;
244 if c1%notfound then
245 --
246 close c1;
247 --
248 -- raise error
249 --
250 fnd_message.set_name('PAY','FORMULA_DOES_NOT_EXIST');
251 fnd_message.raise_error;
252 --
253 end if;
254 --
255 close c1;
256 --
257 end if;
258 --
259 hr_utility.set_location('Leaving:'||l_proc,10);
260 --
261 end chk_cvg_eff_end_rl;
262 --
263 -- ----------------------------------------------------------------------------
264 -- |------< chk_cvg_eff_strt_rl >------|
265 -- ----------------------------------------------------------------------------
266 --
267 -- Description
268 -- This procedure is used to check that the Formula Rule is valid.
269 --
270 -- Pre Conditions
271 -- None.
272 --
273 -- In Parameters
274 -- ler_chg_dpnt_cvg_id PK of record being inserted or updated.
275 -- cvg_eff_strt_rl Value of formula rule id.
276 -- effective_date effective date
277 -- object_version_number Object version number of record being
278 -- inserted or updated.
279 --
280 -- Post Success
281 -- Processing continues
282 --
283 -- Post Failure
284 -- Error handled by procedure
285 --
286 -- Access Status
287 -- Internal table handler use only.
288 --
289 Procedure chk_cvg_eff_strt_rl(p_ler_chg_dpnt_cvg_id in number,
290 p_cvg_eff_strt_rl in number,
291 p_effective_date in date,
292 p_business_group_id in number,
293 p_object_version_number in number) is
294 --
295 l_proc varchar2(72) := g_package||'chk_cvg_eff_strt_rl';
296 l_api_updating boolean;
297 l_dummy varchar2(1);
298 --
299 cursor c1 is
300 select null
301 from ff_formulas_f ff
302 ,per_business_groups pbg
303 where ff.formula_id = p_cvg_eff_strt_rl
304 and ff.formula_type_id = -27 --Dependent Coverage Start
305 and pbg.business_group_id = p_business_group_id
306 and nvl(ff.business_group_id, p_business_group_id) =
307 p_business_group_id
308 and nvl(ff.legislation_code, pbg.legislation_code) =
309 pbg.legislation_code
310 and p_effective_date
311 between ff.effective_start_date
312 and ff.effective_end_date;
313 --
314 Begin
315 --
316 hr_utility.set_location('Entering:'||l_proc, 5);
317 --
318 l_api_updating := ben_ldc_shd.api_updating
319 (p_ler_chg_dpnt_cvg_id => p_ler_chg_dpnt_cvg_id,
320 p_effective_date => p_effective_date,
321 p_object_version_number => p_object_version_number);
322 --
323 if (l_api_updating
324 and nvl(p_cvg_eff_strt_rl,hr_api.g_number)
325 <> ben_ldc_shd.g_old_rec.cvg_eff_strt_rl
326 or not l_api_updating)
327 and p_cvg_eff_strt_rl is not null then
328 --
329 -- check if value of formula rule is valid.
330 --
331 open c1;
332 --
333 -- fetch value from cursor if it returns a record then the
334 -- formula is valid otherwise its invalid
335 --
336 fetch c1 into l_dummy;
337 if c1%notfound then
338 --
339 close c1;
340 --
341 -- raise error
342 --
343 fnd_message.set_name('PAY','FORMULA_DOES_NOT_EXIST');
344 fnd_message.raise_error;
345 --
346 end if;
347 --
348 close c1;
349 --
350 end if;
351 --
352 hr_utility.set_location('Leaving:'||l_proc,10);
353 --
354 end chk_cvg_eff_strt_rl;
355 --
356 -- ----------------------------------------------------------------------------
357 -- |------< chk_ler_chg_dpnt_cvg_rl >------|
358 -- ----------------------------------------------------------------------------
359 --
360 -- Description
361 -- This procedure is used to check that the Formula Rule is valid.
362 --
363 -- Pre Conditions
364 -- None.
365 --
366 -- In Parameters
367 -- ler_chg_dpnt_cvg_id PK of record being inserted or updated.
368 -- ler_chg_dpnt_cvg_rl Value of formula rule id.
369 -- effective_date effective date
370 -- object_version_number Object version number of record being
371 -- inserted or updated.
372 --
373 -- Post Success
374 -- Processing continues
375 --
376 -- Post Failure
377 -- Error handled by procedure
378 --
379 -- Access Status
380 -- Internal table handler use only.
381 --
382 Procedure chk_ler_chg_dpnt_cvg_rl(p_ler_chg_dpnt_cvg_id in number,
383 p_ler_chg_dpnt_cvg_rl in number,
384 p_effective_date in date,
385 p_business_group_id in number,
386 p_object_version_number in number) is
387 --
388 l_proc varchar2(72) := g_package||'chk_ler_chg_dpnt_cvg_rl';
389 l_api_updating boolean;
390 l_dummy varchar2(1);
391 --
392 cursor c1 is
393 select null
394 from ff_formulas_f ff
395 ,per_business_groups pbg
396 where ff.formula_id = p_ler_chg_dpnt_cvg_rl
397 and ff.formula_type_id = -36 --Change Dependent Coverage
398 and pbg.business_group_id = p_business_group_id
399 and nvl(ff.business_group_id, p_business_group_id) =
400 p_business_group_id
401 and nvl(ff.legislation_code, pbg.legislation_code) =
402 pbg.legislation_code
403 and p_effective_date
404 between ff.effective_start_date
405 and ff.effective_end_date;
406 --
407 Begin
408 --
409 hr_utility.set_location('Entering:'||l_proc, 5);
410 --
411 l_api_updating := ben_ldc_shd.api_updating
412 (p_ler_chg_dpnt_cvg_id => p_ler_chg_dpnt_cvg_id,
413 p_effective_date => p_effective_date,
414 p_object_version_number => p_object_version_number);
415 --
416 if (l_api_updating
417 and nvl(p_ler_chg_dpnt_cvg_rl,hr_api.g_number)
418 <> ben_ldc_shd.g_old_rec.ler_chg_dpnt_cvg_rl
419 or not l_api_updating)
420 and p_ler_chg_dpnt_cvg_rl is not null then
421 --
422 -- check if value of formula rule is valid.
423 --
424 open c1;
425 --
426 -- fetch value from cursor if it returns a record then the
427 -- formula is valid otherwise its invalid
428 --
429 fetch c1 into l_dummy;
430 if c1%notfound then
431 --
432 close c1;
433 --
434 -- raise error
435 --
436 fnd_message.set_name('PAY','FORMULA_DOES_NOT_EXIST');
437 fnd_message.raise_error;
438 --
442 --
439 end if;
440 --
441 close c1;
443 end if;
444 --
445 hr_utility.set_location('Leaving:'||l_proc,10);
446 --
447 end chk_ler_chg_dpnt_cvg_rl;
448 --
449 -- ----------------------------------------------------------------------------
450 -- |------< chk_cvg_eff_strt_cd >------|
451 -- ----------------------------------------------------------------------------
452 --
453 -- Description
454 -- This procedure is used to check that the lookup value is valid.
455 --
456 -- Pre Conditions
457 -- None.
458 --
459 -- In Parameters
460 -- ler_chg_dpnt_cvg_id PK of record being inserted or updated.
461 -- cvg_eff_strt_cd Value of lookup code.
462 -- effective_date effective date
463 -- object_version_number Object version number of record being
464 -- inserted or updated.
465 --
466 -- Post Success
467 -- Processing continues
468 --
469 -- Post Failure
470 -- Error handled by procedure
471 --
472 -- Access Status
473 -- Internal table handler use only.
474 --
475 Procedure chk_cvg_eff_strt_cd(p_ler_chg_dpnt_cvg_id in number,
476 p_cvg_eff_strt_cd in varchar2,
477 p_effective_date in date,
478 p_object_version_number in number) is
479 --
480 l_proc varchar2(72) := g_package||'chk_cvg_eff_strt_cd';
481 l_api_updating boolean;
482 --
483 Begin
484 --
485 hr_utility.set_location('Entering:'||l_proc, 5);
486 --
487 l_api_updating := ben_ldc_shd.api_updating
488 (p_ler_chg_dpnt_cvg_id => p_ler_chg_dpnt_cvg_id,
489 p_effective_date => p_effective_date,
490 p_object_version_number => p_object_version_number);
491 --
492 if (l_api_updating
493 and p_cvg_eff_strt_cd
494 <> nvl(ben_ldc_shd.g_old_rec.cvg_eff_strt_cd,hr_api.g_varchar2)
495 or not l_api_updating)
496 and p_cvg_eff_strt_cd is not null then
497 --
498 -- check if value of lookup falls within lookup type.
499 --
500 if hr_api.not_exists_in_hr_lookups
501 (p_lookup_type => 'BEN_DPNT_CVG_STRT',
502 p_lookup_code => p_cvg_eff_strt_cd,
503 p_effective_date => p_effective_date) then
504 --
505 -- raise error as does not exist as lookup
506 --
507 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
508 fnd_message.set_token('FIELD','p_cvg_eff_strt_cd');
509 fnd_message.set_token('TYPE','BEN_DPNT_CVG_STRT');
510 fnd_message.raise_error;
511 --
512 end if;
513 --
514 end if;
515 --
516 hr_utility.set_location('Leaving:'||l_proc,10);
517 --
518 end chk_cvg_eff_strt_cd;
519 --
520 -- ----------------------------------------------------------------------------
521 -- |------< chk_cvg_eff_end_cd >------|
522 -- ----------------------------------------------------------------------------
523 --
524 -- Description
525 -- This procedure is used to check that the lookup value is valid.
526 --
527 -- Pre Conditions
528 -- None.
529 --
530 -- In Parameters
531 -- ler_chg_dpnt_cvg_id PK of record being inserted or updated.
532 -- cvg_eff_end_cd Value of lookup code.
533 -- effective_date effective date
534 -- object_version_number Object version number of record being
535 -- inserted or updated.
536 --
537 -- Post Success
538 -- Processing continues
539 --
540 -- Post Failure
541 -- Error handled by procedure
542 --
543 -- Access Status
544 -- Internal table handler use only.
545 --
546 Procedure chk_cvg_eff_end_cd(p_ler_chg_dpnt_cvg_id in number,
547 p_cvg_eff_end_cd in varchar2,
548 p_effective_date in date,
549 p_object_version_number in number) is
550 --
551 l_proc varchar2(72) := g_package||'chk_cvg_eff_end_cd';
552 l_api_updating boolean;
553 --
554 Begin
555 --
556 hr_utility.set_location('Entering:'||l_proc, 5);
557 --
558 l_api_updating := ben_ldc_shd.api_updating
559 (p_ler_chg_dpnt_cvg_id => p_ler_chg_dpnt_cvg_id,
560 p_effective_date => p_effective_date,
561 p_object_version_number => p_object_version_number);
562 --
563 if (l_api_updating
564 and p_cvg_eff_end_cd
565 <> nvl(ben_ldc_shd.g_old_rec.cvg_eff_end_cd,hr_api.g_varchar2)
566 or not l_api_updating)
567 and p_cvg_eff_end_cd is not null then
568 --
569 -- check if value of lookup falls within lookup type.
570 --
571 if hr_api.not_exists_in_hr_lookups
572 (p_lookup_type => 'BEN_DPNT_CVG_END',
573 p_lookup_code => p_cvg_eff_end_cd,
574 p_effective_date => p_effective_date) then
575 --
576 -- raise error as does not exist as lookup
577 --
578 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
579 fnd_message.set_token('FIELD','p_cvg_eff_end_cd');
580 fnd_message.set_token('TYPE','BEN_DPNT_CVG_END');
581 fnd_message.raise_error;
585 end if;
582 --
583 end if;
584 --
586 --
587 hr_utility.set_location('Leaving:'||l_proc,10);
588 --
589 end chk_cvg_eff_end_cd;
590 --
591 -- ----------------------------------------------------------------------------
592 -- |------< chk_add_rmv_cvg_cd >------|
593 -- ----------------------------------------------------------------------------
594 --
595 -- Description
596 -- This procedure is used to check that the lookup value is valid.
597 --
598 -- Pre Conditions
599 -- None.
600 --
601 -- In Parameters
602 -- ler_chg_dpnt_cvg_id PK of record being inserted or updated.
603 -- add_rmv_cvg_cd Value of lookup code.
604 -- effective_date effective date
605 -- object_version_number Object version number of record being
606 -- inserted or updated.
607 --
608 -- Post Success
609 -- Processing continues
610 --
611 -- Post Failure
612 -- Error handled by procedure
613 --
614 -- Access Status
615 -- Internal table handler use only.
616 --
617 Procedure chk_add_rmv_cvg_cd(p_ler_chg_dpnt_cvg_id in number,
618 p_add_rmv_cvg_cd in varchar2,
619 p_effective_date in date,
620 p_object_version_number in number) is
621 --
622 l_proc varchar2(72) := g_package||'chk_add_rmv_cvg_cd';
623 l_api_updating boolean;
624 --
625 Begin
626 --
627 hr_utility.set_location('Entering:'||l_proc, 5);
628 --
629 l_api_updating := ben_ldc_shd.api_updating
630 (p_ler_chg_dpnt_cvg_id => p_ler_chg_dpnt_cvg_id,
631 p_effective_date => p_effective_date,
632 p_object_version_number => p_object_version_number);
633 --
634 if (l_api_updating
635 and p_add_rmv_cvg_cd
636 <> nvl(ben_ldc_shd.g_old_rec.add_rmv_cvg_cd,hr_api.g_varchar2)
637 or not l_api_updating) then
638 --
639 -- check if value of lookup falls within lookup type.
640 --
641 --
642 if hr_api.not_exists_in_hr_lookups
643 (p_lookup_type => 'BEN_ADD_RMV',
644 p_lookup_code => p_add_rmv_cvg_cd,
645 p_effective_date => p_effective_date) then
646 --
647 -- raise error as does not exist as lookup
648 --
649 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
650 fnd_message.set_token('FIELD','p_add_rmv_cvg_cd');
651 fnd_message.set_token('TYPE','BEN_ADD_RMV');
652 fnd_message.raise_error;
653 --
654 end if;
655 --
656 end if;
657 --
658 hr_utility.set_location('Leaving:'||l_proc,10);
659 --
660 end chk_add_rmv_cvg_cd;
661 --
662 -- ----------------------------------------------------------------------------
663 -- |------< chk_dpnt_dsgn_cd >------|
664 -- ----------------------------------------------------------------------------
665 --
666 -- Description
667 -- This procedure is used to check that the dpnt_dsgn_cd on ben_pgm_f has a
668 -- value of 'O' 'R'. If not, signal error, and disallow the insert/update.
669 --
670 -- Pre Conditions
671 -- None.
672 --
673 -- In Parameters
674 --
675 -- Post Success
676 -- Processing continues
677 --
678 -- Post Failure
679 -- Error handled by procedure
680 --
681 -- Access Status
682 -- Internal table handler use only.
683 --
684 Procedure chk_dpnt_dsgn_cd(p_ler_chg_dpnt_cvg_id in number,
685 p_pgm_id in varchar2,
686 p_ptip_id in number,
687 p_pl_id in number,
688 p_effective_date in date,
689 p_validation_start_date in date,
690 p_validation_end_date in date,
691 p_business_group_id in number,
692 p_object_version_number in number) is
693 --
694 l_proc varchar2(72) := g_package||'chk_dpnt_dsgn_cd';
695 l_api_updating boolean;
696 l_exists varchar2(1);
697 --
698 -- ben_pgm_f.dpnt_dsgn_cd must = 'R' or 'O' in order to insert/update.
699 --
700 cursor chk_dpnt_dsgn is
701 select null
702 from ben_pgm_f
703 where pgm_id = p_pgm_id
704 and dpnt_dsgn_cd in ('R','O')
705 and business_group_id + 0 = p_business_group_id
706 and p_effective_date
707 between effective_start_date and
708 effective_end_date;
709 -- and p_validation_start_date <= effective_end_date
710 -- and p_validation_end_date >= effective_start_date;
711 --
712 -- Note: currently documents say to only check pgm, but pl and ptip also
713 -- have a dpnt_dsgn_cd, so we may need to add 2 more cursors here in the
714 -- future.
715 Begin
716 --
717 hr_utility.set_location('Entering:'||l_proc, 5);
718 --
719 if p_pgm_id is not null then
720 --
721 open chk_dpnt_dsgn;
722 fetch chk_dpnt_dsgn into l_exists;
723 if chk_dpnt_dsgn%notfound then
724 close chk_dpnt_dsgn;
728 fnd_message.set_name('PAY','PGM_DPNT_DSGN_NOT_O_R');
725 --
726 -- raise error
727 --
729 fnd_message.raise_error;
730 --
731 end if;
732 --
733 close chk_dpnt_dsgn;
734 --
735 end if;
736 --
737 hr_utility.set_location('Leaving:'||l_proc,10);
738 --
739 end chk_dpnt_dsgn_cd;
740 --
741 -- ----------------------------------------------------------------------------
742 -- |------< chk_ler_chg_dpnt_cvg_cd >------|
743 -- ----------------------------------------------------------------------------
744 --
745 -- Description
746 -- This procedure is used to check that the lookup value is valid.
747 -- If ler_chg_dpnt_cvg_cd is not null then cvg_eff_strt_cd and cvg_eff_end_cd
748 -- should also be not null.
749 -- If ler_chg_dpnt_cvg_cd is null then cvg_eff_strt_cd and cvg_eff_end_cd
750 -- should also be null.
751 -- Pre Conditions
752 -- None.
753 --
754 -- In Parameters
755 -- ler_chg_dpnt_cvg_id PK of record being inserted or updated.
756 -- ler_chg_dpnt_cvg_cd Value of lookup code.
757 -- cvg_eff_strt_cd Value of cvg_eff_strt_cd
758 -- cvg_eff_end_cd Value of cvg_eff_end_cd
759 -- effective_date effective date
760 -- object_version_number Object version number of record being
761 -- inserted or updated.
762 --
763 -- Post Success
764 -- Processing continues
765 --
766 -- Post Failure
767 -- Error handled by procedure
768 --
769 -- Access Status
770 -- Internal table handler use only.
771 --
772 Procedure chk_ler_chg_dpnt_cvg_cd(p_ler_chg_dpnt_cvg_id in number,
773 p_ler_chg_dpnt_cvg_cd in varchar2,
774 p_cvg_eff_strt_cd in varchar2,
775 p_cvg_eff_end_cd in varchar2,
776 p_effective_date in date,
777 p_object_version_number in number) is
778 --
779 l_proc varchar2(72) := g_package||'chk_ler_chg_dpnt_cvg_cd';
780 l_api_updating boolean;
781 --
782 Begin
783 --
784 hr_utility.set_location('Entering:'||l_proc, 5);
785 --
786 l_api_updating := ben_ldc_shd.api_updating
787 (p_ler_chg_dpnt_cvg_id => p_ler_chg_dpnt_cvg_id,
788 p_effective_date => p_effective_date,
789 p_object_version_number => p_object_version_number);
790 --
791 if (l_api_updating
792 and p_ler_chg_dpnt_cvg_cd
793 <> nvl(ben_ldc_shd.g_old_rec.ler_chg_dpnt_cvg_cd,hr_api.g_varchar2)
794 or not l_api_updating)
795 and p_ler_chg_dpnt_cvg_cd is not null then
796 --
797 -- check if value of lookup falls within lookup type.
798 --
799 if hr_api.not_exists_in_hr_lookups
800 (p_lookup_type => 'BEN_LER_CHG_DPNT_CVG',
801 p_lookup_code => p_ler_chg_dpnt_cvg_cd,
802 p_effective_date => p_effective_date) then
803 --
804 -- raise error as does not exist as lookup
805 --
806 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
807 fnd_message.set_token('FIELD','p_ler_chg_dpnt_cvg_cd');
808 fnd_message.set_token('TYPE','BEN_LER_CHG_DPNT_CVG');
809 fnd_message.raise_error;
810 --
811 end if;
812 --
813 end if;
814 --
815 -- Commented per Bug 1403687 on 29-Sep-2000 by Indrasen
816 /*
817 if (p_ler_chg_dpnt_cvg_cd is not null) and
818 (p_cvg_eff_strt_cd is null or p_cvg_eff_end_cd is null) then
819
820 fnd_message.set_name('BEN','BEN_92513_DPNDNT_CVRG_DT_NULL');
821 fnd_message.raise_error;
822 end if;
823 */
824 --
825 if (p_ler_chg_dpnt_cvg_cd is null) and
826 (p_cvg_eff_strt_cd is not null or p_cvg_eff_end_cd is not null) then
827 --null;
828 fnd_message.set_name('BEN','BEN_92514_CHNG_DPNDNT_CVG_RQD');
829 fnd_message.raise_error;
830 end if;
831
832 --
833 hr_utility.set_location('Leaving:'||l_proc,10);
834 --
835 end chk_ler_chg_dpnt_cvg_cd;
836 --
837 -- ----------------------------------------------------------------------------
838 -- |------< chk_cvg_eff_strt_dependency >------|
839 -- ----------------------------------------------------------------------------
840 --
841 -- Description
842 -- This procedure is used to check the code/rule dependency as following:
843 -- If code = 'Rule' then rule must be selected.
844 -- If code <> 'Rule' then code must not be selected.
845 --
846 -- Pre Conditions
847 -- None.
848 --
849 -- In Parameters
850 -- ler_chg_dpnt_cvg_id PK of record being inserted or updated.
851 -- cvg_eff_strt_cd Value of lookup code.
852 -- cvg_eff_strt_rl
853 -- effective_date effective date
854 -- object_version_number Object version number of record being
855 -- inserted or updated.
856 --
857 -- Post Success
858 -- Processing continues
859 --
860 -- Post Failure
861 -- Error handled by procedure
862 --
863 -- Access Status
864 -- Internal table handler use only.
865 --
866 Procedure chk_cvg_eff_strt_dependency(p_ler_chg_dpnt_cvg_id in number,
870 p_object_version_number in number) is
867 p_cvg_eff_strt_cd in varchar2,
868 p_cvg_eff_strt_rl in number,
869 p_effective_date in date,
871 --
872 l_proc varchar2(72) := g_package||'chk_cvg_eff_strt_dependency ';
873 l_api_updating boolean;
874 --
875 Begin
876 --
877 hr_utility.set_location('Entering:'||l_proc, 5);
878 --
879 l_api_updating := ben_ldc_shd.api_updating
880 (p_ler_chg_dpnt_cvg_id => p_ler_chg_dpnt_cvg_id,
881 p_effective_date => p_effective_date,
882 p_object_version_number => p_object_version_number);
883 --
884 if (l_api_updating
885 and
886 (nvl(p_cvg_eff_strt_cd,hr_api.g_varchar2)
887 <> nvl(ben_ldc_shd.g_old_rec.cvg_eff_strt_cd,hr_api.g_varchar2) or
888 nvl(p_cvg_eff_strt_rl,hr_api.g_number)
889 <> nvl(ben_ldc_shd.g_old_rec.cvg_eff_strt_rl,hr_api.g_number))
890 or not l_api_updating) then
891 --
892 if (p_cvg_eff_strt_cd = 'RL' and p_cvg_eff_strt_rl is null) then
893 --
894 fnd_message.set_name('BEN','BEN_91623_CD_RL_1');
895 fnd_message.raise_error;
896 -- please word this message like 'If you choose a Coverage Start Code of "Rule",
897 -- you must choose a Coverage Start Rule.'
898 end if;
899 --
900 if nvl(p_cvg_eff_strt_cd,hr_api.g_varchar2) <> 'RL' and p_cvg_eff_strt_rl is not null then
901 --
902 fnd_message.set_name('BEN','BEN_91624_CD_RL_2');
903 fnd_message.raise_error;
904 end if;
905 --
906 end if;
907 --
908 hr_utility.set_location('Leaving:'||l_proc,10);
909 --
910 end chk_cvg_eff_strt_dependency;
911 --
912 -- ----------------------------------------------------------------------------
913 -- |------< chk_cvg_eff_end_dependency >------|
914 -- ----------------------------------------------------------------------------
915 --
916 -- Description
917 -- This procedure is used to check the code/rule dependency as following:
918 -- If code = 'Rule' then rule must be selected.
919 -- If code <> 'Rule' then code must not be selected.
920 --
921 -- Pre Conditions
922 -- None.
923 --
924 -- In Parameters
925 -- ler_chg_dpnt_cvg_id PK of record being inserted or updated.
926 -- cvg_eff_end_cd Value of lookup code.
927 -- cvg_eff_end_rl
928 -- effective_date effective date
929 -- object_version_number Object version number of record being
930 -- inserted or updated.
931 --
932 -- Post Success
933 -- Processing continues
934 --
935 -- Post Failure
936 -- Error handled by procedure
937 --
938 -- Access Status
939 -- Internal table handler use only.
940 --
941 Procedure chk_cvg_eff_end_dependency(p_ler_chg_dpnt_cvg_id in number,
942 p_cvg_eff_end_cd in varchar2,
943 p_cvg_eff_end_rl in number,
944 p_effective_date in date,
945 p_object_version_number in number) is
946 --
947 l_proc varchar2(72) := g_package||'chk_cvg_eff_end_dependency ';
948 l_api_updating boolean;
949 --
950 Begin
951 --
952 hr_utility.set_location('Entering:'||l_proc, 5);
953 --
954 l_api_updating := ben_ldc_shd.api_updating
955 (p_ler_chg_dpnt_cvg_id => p_ler_chg_dpnt_cvg_id,
956 p_effective_date => p_effective_date,
957 p_object_version_number => p_object_version_number);
958 --
959 if (l_api_updating
960 and
961 (nvl(p_cvg_eff_end_cd,hr_api.g_varchar2)
962 <> nvl(ben_ldc_shd.g_old_rec.cvg_eff_end_cd,hr_api.g_varchar2) or
963 nvl(p_cvg_eff_end_rl,hr_api.g_number)
964 <> nvl(ben_ldc_shd.g_old_rec.cvg_eff_end_rl,hr_api.g_number))
965 or not l_api_updating) then
966 --
967 if (p_cvg_eff_end_cd = 'RL' and p_cvg_eff_end_rl is null) then
968 --
969 fnd_message.set_name('BEN','BEN_91623_CD_RL_1');
970 fnd_message.raise_error;
971 -- please word this message like 'If you choose a Coverage Start Code of "Rule",
972 -- you must choose a Coverage Start Rule.'
973 end if;
974 --
975 if nvl(p_cvg_eff_end_cd,hr_api.g_varchar2) <> 'RL' and p_cvg_eff_end_rl is not null then
976 --
977 fnd_message.set_name('BEN','BEN_91624_CD_RL_2');
978 fnd_message.raise_error;
979 -- please word this message like 'If you choose a Coverage Start Rule,
980 -- you must choose a Coverage Start Code of "Rule".'
981 end if;
982 --
983 end if;
984 --
985 hr_utility.set_location('Leaving:'||l_proc,10);
986 --
987 end chk_cvg_eff_end_dependency;
988 --
989 -- ----------------------------------------------------------------------------
990 -- |------< chk_chg_dpnt_cvg_dependency >------|
991 -- ---------------------------------------------------------------------------
992 --
993 -- Description
997 --
994 -- This procedure is used to check the code/rule dependency as following:
995 -- If code = 'Rule' then rule must be selected.
996 -- If code <> 'Rule' then code must not be selected.
998 -- Pre Conditions
999 -- None.
1000 --
1001 -- In Parameters
1002 -- ler_chg_dpnt_cvg_id PK of record being inserted or updated.
1003 -- ler_chg_dpnt_cvg_cd Value of lookup code.
1004 -- ler_chg_dpnt_cvg_rl
1005 -- effective_date effective date
1006 -- object_version_number Object version number of record being
1007 -- inserted or updated.
1008 --
1009 -- Post Success
1010 -- Processing continues
1011 --
1012 -- Post Failure
1013 -- Error handled by procedure
1014 --
1015 -- Access Status
1016 -- Internal table handler use only.
1017 --
1018 Procedure chk_chg_dpnt_cvg_dependency(p_ler_chg_dpnt_cvg_id in number,
1019 p_ler_chg_dpnt_cvg_cd in varchar2,
1020 p_ler_chg_dpnt_cvg_rl in number,
1021 p_effective_date in date,
1022 p_object_version_number in number) is
1023 --
1024 l_proc varchar2(72) := g_package||'chk_chg_dpnt_cvg_dependency';
1025 l_api_updating boolean;
1026 --
1027 Begin
1028 --
1029 hr_utility.set_location('Entering:'||l_proc, 5);
1030 --
1031 l_api_updating := ben_ldc_shd.api_updating
1032 (p_ler_chg_dpnt_cvg_id => p_ler_chg_dpnt_cvg_id,
1033 p_effective_date => p_effective_date,
1034 p_object_version_number => p_object_version_number);
1035 --
1036 if (l_api_updating
1037 and
1038 (nvl(p_ler_chg_dpnt_cvg_cd,hr_api.g_varchar2)
1039 <> nvl(ben_ldc_shd.g_old_rec.ler_chg_dpnt_cvg_cd,hr_api.g_varchar2) or
1040 nvl(p_ler_chg_dpnt_cvg_rl,hr_api.g_number)
1041 <> nvl(ben_ldc_shd.g_old_rec.ler_chg_dpnt_cvg_rl,hr_api.g_number))
1042 or not l_api_updating) then
1043 --
1044 if (p_ler_chg_dpnt_cvg_cd = 'RL' and p_ler_chg_dpnt_cvg_rl is null) then
1045 --
1046 fnd_message.set_name('BEN','BEN_91623_CD_RL_1');
1047 fnd_message.raise_error;
1048 -- please word this message like 'If you choose a Coverage Start Code of "Rule",
1049 -- you must choose a Coverage Start Rule.'
1050 end if;
1051 --
1052 if nvl(p_ler_chg_dpnt_cvg_cd,hr_api.g_varchar2) <> 'RL' and p_ler_chg_dpnt_cvg_rl is not null then
1053 --
1054 fnd_message.set_name('BEN','BEN_91624_CD_RL_2');
1055 fnd_message.raise_error;
1056 -- please word this message like 'If you choose a Coverage Start Rule,
1057 -- you must choose a Coverage Start Code of "Rule".'
1058 end if;
1059 --
1060 end if;
1061 --
1062 hr_utility.set_location('Leaving:'||l_proc,10);
1063 --
1064 end chk_chg_dpnt_cvg_dependency;
1065 --
1066 -- ----------------------------------------------------------------------------
1067 -- |--------------------------< dt_update_validate >--------------------------|
1068 -- ----------------------------------------------------------------------------
1069 -- {Start Of Comments}
1070 --
1071 -- Description:
1072 -- This procedure is used for referential integrity of datetracked
1073 -- parent entities when a datetrack update operation is taking place
1074 -- and where there is no cascading of update defined for this entity.
1075 --
1076 -- Prerequisites:
1077 -- This procedure is called from the update_validate.
1078 --
1079 -- In Parameters:
1080 --
1081 -- Post Success:
1082 -- Processing continues.
1083 --
1084 -- Post Failure:
1085 --
1086 -- Developer Implementation Notes:
1087 -- This procedure should not need maintenance unless the HR Schema model
1088 -- changes.
1089 --
1090 -- Access Status:
1091 -- Internal Row Handler Use Only.
1092 --
1093 -- {End Of Comments}
1094 -- ----------------------------------------------------------------------------
1095 Procedure dt_update_validate
1096 (p_ler_chg_dpnt_cvg_rl in number default hr_api.g_number,
1097 p_cvg_eff_strt_rl in number default hr_api.g_number,
1098 p_cvg_eff_end_rl in number default hr_api.g_number,
1099 p_ptip_id in number default hr_api.g_number,
1100 p_ler_id in number default hr_api.g_number,
1101 p_pl_id in number default hr_api.g_number,
1102 p_pgm_id in number default hr_api.g_number,
1103 p_datetrack_mode in varchar2,
1104 p_validation_start_date in date,
1105 p_validation_end_date in date) Is
1106 --
1107 l_proc varchar2(72) := g_package||'dt_update_validate';
1108 l_integrity_error Exception;
1109 l_table_name all_tables.table_name%TYPE;
1110 --
1111 Begin
1112 hr_utility.set_location('Entering:'||l_proc, 5);
1113 --
1114 -- Ensure that the p_datetrack_mode argument is not null
1115 --
1116 hr_api.mandatory_arg_error
1117 (p_api_name => l_proc,
1118 p_argument => 'datetrack_mode',
1119 p_argument_value => p_datetrack_mode);
1120 --
1124 --
1121 -- Only perform the validation if the datetrack update mode is valid
1122 --
1123 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
1125 --
1126 -- Ensure the arguments are not null
1127 --
1128 hr_api.mandatory_arg_error
1129 (p_api_name => l_proc,
1130 p_argument => 'validation_start_date',
1131 p_argument_value => p_validation_start_date);
1132 --
1133 hr_api.mandatory_arg_error
1134 (p_api_name => l_proc,
1135 p_argument => 'validation_end_date',
1136 p_argument_value => p_validation_end_date);
1137 --
1138 If ((nvl(p_ler_chg_dpnt_cvg_rl, hr_api.g_number) <> hr_api.g_number) and
1139 NOT (dt_api.check_min_max_dates
1140 (p_base_table_name => 'ff_formulas_f',
1141 p_base_key_column => 'formula_id',
1142 p_base_key_value => p_ler_chg_dpnt_cvg_rl,
1143 p_from_date => p_validation_start_date,
1144 p_to_date => p_validation_end_date))) Then
1145 l_table_name := 'ff_formulas_f';
1146 Raise l_integrity_error;
1147 End If;
1148 If ((nvl(p_cvg_eff_strt_rl, hr_api.g_number) <> hr_api.g_number) and
1149 NOT (dt_api.check_min_max_dates
1150 (p_base_table_name => 'ff_formulas_f',
1151 p_base_key_column => 'formula_id',
1152 p_base_key_value => p_cvg_eff_strt_rl,
1153 p_from_date => p_validation_start_date,
1154 p_to_date => p_validation_end_date))) Then
1155 l_table_name := 'ff_formulas_f';
1156 Raise l_integrity_error;
1157 End If;
1158 If ((nvl(p_cvg_eff_end_rl, hr_api.g_number) <> hr_api.g_number) and
1159 NOT (dt_api.check_min_max_dates
1160 (p_base_table_name => 'ff_formulas_f',
1161 p_base_key_column => 'formula_id',
1162 p_base_key_value => p_cvg_eff_end_rl,
1163 p_from_date => p_validation_start_date,
1164 p_to_date => p_validation_end_date))) Then
1165 l_table_name := 'ff_formulas_f';
1166 Raise l_integrity_error;
1167 End If;
1168 If ((nvl(p_ptip_id, hr_api.g_number) <> hr_api.g_number) and
1169 NOT (dt_api.check_min_max_dates
1170 (p_base_table_name => 'ben_ptip_f',
1171 p_base_key_column => 'ptip_id',
1172 p_base_key_value => p_ptip_id,
1173 p_from_date => p_validation_start_date,
1174 p_to_date => p_validation_end_date))) Then
1175 l_table_name := 'ben_ptip_f';
1176 Raise l_integrity_error;
1177 End If;
1178 If ((nvl(p_ler_id, hr_api.g_number) <> hr_api.g_number) and
1179 NOT (dt_api.check_min_max_dates
1180 (p_base_table_name => 'ben_ler_f',
1181 p_base_key_column => 'ler_id',
1182 p_base_key_value => p_ler_id,
1183 p_from_date => p_validation_start_date,
1184 p_to_date => p_validation_end_date))) Then
1185 l_table_name := 'ben_ler_f';
1186 Raise l_integrity_error;
1187 End If;
1188 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
1189 NOT (dt_api.check_min_max_dates
1190 (p_base_table_name => 'ben_pl_f',
1191 p_base_key_column => 'pl_id',
1192 p_base_key_value => p_pl_id,
1193 p_from_date => p_validation_start_date,
1194 p_to_date => p_validation_end_date))) Then
1195 l_table_name := 'ben_pl_f';
1196 Raise l_integrity_error;
1197 End If;
1198 If ((nvl(p_pgm_id, hr_api.g_number) <> hr_api.g_number) and
1199 NOT (dt_api.check_min_max_dates
1200 (p_base_table_name => 'ben_pgm_f',
1201 p_base_key_column => 'pgm_id',
1202 p_base_key_value => p_pgm_id,
1203 p_from_date => p_validation_start_date,
1204 p_to_date => p_validation_end_date))) Then
1205 l_table_name := 'ben_pgm_f';
1206 Raise l_integrity_error;
1207 End If;
1208 --
1209 End If;
1210 --
1211 hr_utility.set_location(' Leaving:'||l_proc, 10);
1212 Exception
1213 When l_integrity_error Then
1214 --
1215 -- A referential integrity check was violated therefore
1216 -- we must error
1217 --
1218 ben_utility.parent_integrity_error(p_table_name => l_table_name);
1219 When Others Then
1220 --
1221 -- An unhandled or unexpected error has occurred which
1222 -- we must report
1223 --
1224 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1225 fnd_message.set_token('PROCEDURE', l_proc);
1226 fnd_message.set_token('STEP','15');
1227 fnd_message.raise_error;
1228 End dt_update_validate;
1229 --
1230 -- ----------------------------------------------------------------------------
1231 -- |--------------------------< dt_delete_validate >--------------------------|
1232 -- ----------------------------------------------------------------------------
1233 -- {Start Of Comments}
1234 --
1235 -- Description:
1236 -- This procedure is used for referential integrity of datetracked
1237 -- child entities when either a datetrack DELETE or ZAP is in operation
1238 -- and where there is no cascading of delete defined for this entity.
1242 --
1239 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1240 -- datetracked child rows exist between the validation start and end
1241 -- dates.
1243 -- Prerequisites:
1244 -- This procedure is called from the delete_validate.
1245 --
1246 -- In Parameters:
1247 --
1248 -- Post Success:
1249 -- Processing continues.
1250 --
1251 -- Post Failure:
1252 -- If a row exists by determining the returning Boolean value from the
1253 -- generic dt_api.rows_exist function then we must supply an error via
1254 -- the use of the local exception handler l_rows_exist.
1255 --
1256 -- Developer Implementation Notes:
1257 -- This procedure should not need maintenance unless the HR Schema model
1258 -- changes.
1259 --
1260 -- Access Status:
1261 -- Internal Row Handler Use Only.
1262 --
1263 -- {End Of Comments}
1264 -- ----------------------------------------------------------------------------
1265 Procedure dt_delete_validate
1266 (p_ler_chg_dpnt_cvg_id in number,
1267 p_datetrack_mode in varchar2,
1268 p_validation_start_date in date,
1269 p_validation_end_date in date) Is
1270 --
1271 l_proc varchar2(72) := g_package||'dt_delete_validate';
1272 l_rows_exist Exception;
1273 l_table_name all_tables.table_name%TYPE;
1274 --
1275 Begin
1276 hr_utility.set_location('Entering:'||l_proc, 5);
1277 --
1278 -- Ensure that the p_datetrack_mode argument is not null
1279 --
1280 hr_api.mandatory_arg_error
1281 (p_api_name => l_proc,
1282 p_argument => 'datetrack_mode',
1283 p_argument_value => p_datetrack_mode);
1284 --
1285 -- Only perform the validation if the datetrack mode is either
1286 -- DELETE or ZAP
1287 --
1288 If (p_datetrack_mode = 'DELETE' or
1289 p_datetrack_mode = 'ZAP') then
1290 --
1291 --
1292 -- Ensure the arguments are not null
1293 --
1294 hr_api.mandatory_arg_error
1295 (p_api_name => l_proc,
1296 p_argument => 'validation_start_date',
1297 p_argument_value => p_validation_start_date);
1298 --
1299 hr_api.mandatory_arg_error
1300 (p_api_name => l_proc,
1301 p_argument => 'validation_end_date',
1302 p_argument_value => p_validation_end_date);
1303 --
1304 hr_api.mandatory_arg_error
1305 (p_api_name => l_proc,
1306 p_argument => 'ler_chg_dpnt_cvg_id',
1307 p_argument_value => p_ler_chg_dpnt_cvg_id);
1308 --
1309 If (dt_api.rows_exist
1310 (p_base_table_name => 'ben_ler_chg_dpnt_cvg_ctfn_f',
1311 p_base_key_column => 'ler_chg_dpnt_cvg_id',
1312 p_base_key_value => p_ler_chg_dpnt_cvg_id,
1313 p_from_date => p_validation_start_date,
1314 p_to_date => p_validation_end_date)) Then
1315 l_table_name := 'ben_ler_chg_dpnt_cvg_ctfn_f';
1316 Raise l_rows_exist;
1317 End If;
1318 --
1319 End If;
1320 --
1321 hr_utility.set_location(' Leaving:'||l_proc, 10);
1322 Exception
1323 When l_rows_exist Then
1324 --
1325 -- A referential integrity check was violated therefore
1326 -- we must error
1327 --
1328 ben_utility.child_exists_error(p_table_name => l_table_name);
1329 When Others Then
1330 --
1331 -- An unhandled or unexpected error has occurred which
1332 -- we must report
1333 --
1334 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1335 fnd_message.set_token('PROCEDURE', l_proc);
1336 fnd_message.set_token('STEP','15');
1337 fnd_message.raise_error;
1338 End dt_delete_validate;
1339 --
1340 -- ----------------------------------------------------------------------------
1341 -- |---------------------------< insert_validate >----------------------------|
1342 -- ----------------------------------------------------------------------------
1343 Procedure insert_validate
1344 (p_rec in ben_ldc_shd.g_rec_type,
1345 p_effective_date in date,
1346 p_datetrack_mode in varchar2,
1347 p_validation_start_date in date,
1348 p_validation_end_date in date) is
1349 --
1350 l_proc varchar2(72) := g_package||'insert_validate';
1351 --
1352 Begin
1353 hr_utility.set_location('Entering:'||l_proc, 5);
1354 --
1355 -- Call all supporting business operations
1356 --
1357 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1358 --
1359 chk_ler_chg_dpnt_cvg_id
1360 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1361 p_effective_date => p_effective_date,
1362 p_object_version_number => p_rec.object_version_number);
1363 --
1364 chk_ler_id
1365 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1366 p_ler_id => p_rec.ler_id,
1367 p_pgm_id => p_rec.pgm_id,
1368 p_ptip_id => p_rec.ptip_id,
1369 p_pl_id => p_rec.pl_id,
1370 p_effective_date => p_effective_date,
1371 p_validation_start_date => p_validation_start_date,
1372 p_validation_end_date => p_validation_end_date,
1373 p_business_group_id => p_rec.business_group_id,
1374 p_object_version_number => p_rec.object_version_number);
1375 --
1376 chk_cvg_eff_end_rl
1380 p_business_group_id => p_rec.business_group_id,
1377 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1378 p_cvg_eff_end_rl => p_rec.cvg_eff_end_rl,
1379 p_effective_date => p_effective_date,
1381 p_object_version_number => p_rec.object_version_number);
1382 --
1383 chk_cvg_eff_strt_rl
1384 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1385 p_cvg_eff_strt_rl => p_rec.cvg_eff_strt_rl,
1386 p_effective_date => p_effective_date,
1387 p_business_group_id => p_rec.business_group_id,
1388 p_object_version_number => p_rec.object_version_number);
1389 --
1390 chk_ler_chg_dpnt_cvg_rl
1391 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1392 p_ler_chg_dpnt_cvg_rl => p_rec.ler_chg_dpnt_cvg_rl,
1393 p_effective_date => p_effective_date,
1394 p_business_group_id => p_rec.business_group_id,
1395 p_object_version_number => p_rec.object_version_number);
1396 --
1397 chk_ler_chg_dpnt_cvg_cd
1398 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1399 p_ler_chg_dpnt_cvg_cd => p_rec.ler_chg_dpnt_cvg_cd,
1400 p_cvg_eff_strt_cd => p_rec.cvg_eff_strt_cd,
1401 p_cvg_eff_end_cd => p_rec.cvg_eff_end_cd,
1402 p_effective_date => p_effective_date,
1403 p_object_version_number => p_rec.object_version_number);
1404 --
1405 chk_cvg_eff_strt_cd
1406 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1407 p_cvg_eff_strt_cd => p_rec.cvg_eff_strt_cd,
1408 p_effective_date => p_effective_date,
1409 p_object_version_number => p_rec.object_version_number);
1410 --
1411 chk_cvg_eff_end_cd
1412 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1413 p_cvg_eff_end_cd => p_rec.cvg_eff_end_cd,
1414 p_effective_date => p_effective_date,
1415 p_object_version_number => p_rec.object_version_number);
1416 --
1417 chk_add_rmv_cvg_cd
1418 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1419 p_add_rmv_cvg_cd => p_rec.add_rmv_cvg_cd,
1420 p_effective_date => p_effective_date,
1421 p_object_version_number => p_rec.object_version_number);
1422 --
1423 chk_dpnt_dsgn_cd
1424 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1425 p_pgm_id => p_rec.pgm_id,
1426 p_ptip_id => p_rec.ptip_id,
1427 p_pl_id => p_rec.pl_id,
1428 p_effective_date => p_effective_date,
1429 p_validation_start_date => p_validation_start_date,
1430 p_validation_end_date => p_validation_end_date,
1431 p_business_group_id => p_rec.business_group_id,
1432 p_object_version_number => p_rec.object_version_number);
1433 --
1434 chk_cvg_eff_strt_dependency
1435 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1436 p_cvg_eff_strt_cd => p_rec.cvg_eff_strt_cd,
1437 p_cvg_eff_strt_rl => p_rec.cvg_eff_strt_rl,
1438 p_effective_date => p_effective_date,
1439 p_object_version_number => p_rec.object_version_number);
1440 --
1441 chk_cvg_eff_end_dependency
1442 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1443 p_cvg_eff_end_cd => p_rec.cvg_eff_end_cd,
1444 p_cvg_eff_end_rl => p_rec.cvg_eff_end_rl,
1445 p_effective_date => p_effective_date,
1446 p_object_version_number => p_rec.object_version_number);
1447 --
1448 chk_chg_dpnt_cvg_dependency
1449 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1450 p_ler_chg_dpnt_cvg_cd => p_rec.ler_chg_dpnt_cvg_cd,
1451 p_ler_chg_dpnt_cvg_rl => p_rec.ler_chg_dpnt_cvg_rl,
1452 p_effective_date => p_effective_date,
1453 p_object_version_number => p_rec.object_version_number);
1454 --
1455 hr_utility.set_location(' Leaving:'||l_proc, 10);
1456 End insert_validate;
1457 --
1458 -- ----------------------------------------------------------------------------
1459 -- |---------------------------< update_validate >----------------------------|
1460 -- ----------------------------------------------------------------------------
1461 Procedure update_validate
1462 (p_rec in ben_ldc_shd.g_rec_type,
1463 p_effective_date in date,
1464 p_datetrack_mode in varchar2,
1465 p_validation_start_date in date,
1466 p_validation_end_date in date) is
1467 --
1468 l_proc varchar2(72) := g_package||'update_validate';
1469 --
1470 Begin
1471 hr_utility.set_location('Entering:'||l_proc, 5);
1472 --
1473 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1474 --
1475 -- Call all supporting business operations
1476 --
1477 chk_ler_chg_dpnt_cvg_id
1478 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1479 p_effective_date => p_effective_date,
1480 p_object_version_number => p_rec.object_version_number);
1481 --
1482 chk_ler_id
1483 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1484 p_ler_id => p_rec.ler_id,
1485 p_pgm_id => p_rec.pgm_id,
1486 p_ptip_id => p_rec.ptip_id,
1487 p_pl_id => p_rec.pl_id,
1488 p_effective_date => p_effective_date,
1489 p_validation_start_date => p_validation_start_date,
1490 p_validation_end_date => p_validation_end_date,
1491 p_business_group_id => p_rec.business_group_id,
1495 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1492 p_object_version_number => p_rec.object_version_number);
1493 --
1494 chk_cvg_eff_end_rl
1496 p_cvg_eff_end_rl => p_rec.cvg_eff_end_rl,
1497 p_effective_date => p_effective_date,
1498 p_business_group_id => p_rec.business_group_id,
1499 p_object_version_number => p_rec.object_version_number);
1500 --
1501 chk_cvg_eff_strt_rl
1502 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1503 p_cvg_eff_strt_rl => p_rec.cvg_eff_strt_rl,
1504 p_effective_date => p_effective_date,
1505 p_business_group_id => p_rec.business_group_id,
1506 p_object_version_number => p_rec.object_version_number);
1507 --
1508 chk_ler_chg_dpnt_cvg_rl
1509 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1510 p_ler_chg_dpnt_cvg_rl => p_rec.ler_chg_dpnt_cvg_rl,
1511 p_effective_date => p_effective_date,
1512 p_business_group_id => p_rec.business_group_id,
1513 p_object_version_number => p_rec.object_version_number);
1514 --
1515 chk_ler_chg_dpnt_cvg_cd
1516 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1517 p_ler_chg_dpnt_cvg_cd => p_rec.ler_chg_dpnt_cvg_cd,
1518 p_cvg_eff_strt_cd => p_rec.cvg_eff_strt_cd,
1519 p_cvg_eff_end_cd => p_rec.cvg_eff_end_cd,
1520 p_effective_date => p_effective_date,
1521 p_object_version_number => p_rec.object_version_number);
1522 --
1523 chk_cvg_eff_strt_cd
1524 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1525 p_cvg_eff_strt_cd => p_rec.cvg_eff_strt_cd,
1526 p_effective_date => p_effective_date,
1527 p_object_version_number => p_rec.object_version_number);
1528 --
1529 chk_cvg_eff_end_cd
1530 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1531 p_cvg_eff_end_cd => p_rec.cvg_eff_end_cd,
1532 p_effective_date => p_effective_date,
1533 p_object_version_number => p_rec.object_version_number);
1534 --
1535 chk_add_rmv_cvg_cd
1536 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1537 p_add_rmv_cvg_cd => p_rec.add_rmv_cvg_cd,
1538 p_effective_date => p_effective_date,
1539 p_object_version_number => p_rec.object_version_number);
1540 --
1541 chk_dpnt_dsgn_cd
1542 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1543 p_pgm_id => p_rec.pgm_id,
1544 p_ptip_id => p_rec.ptip_id,
1545 p_pl_id => p_rec.pl_id,
1546 p_effective_date => p_effective_date,
1547 p_validation_start_date => p_validation_start_date,
1548 p_validation_end_date => p_validation_end_date,
1549 p_business_group_id => p_rec.business_group_id,
1550 p_object_version_number => p_rec.object_version_number);
1551 --
1552 chk_cvg_eff_strt_dependency
1553 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1554 p_cvg_eff_strt_cd => p_rec.cvg_eff_strt_cd,
1555 p_cvg_eff_strt_rl => p_rec.cvg_eff_strt_rl,
1556 p_effective_date => p_effective_date,
1557 p_object_version_number => p_rec.object_version_number);
1558 --
1559 chk_cvg_eff_end_dependency
1560 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1561 p_cvg_eff_end_cd => p_rec.cvg_eff_end_cd,
1562 p_cvg_eff_end_rl => p_rec.cvg_eff_end_rl,
1563 p_effective_date => p_effective_date,
1564 p_object_version_number => p_rec.object_version_number);
1565 --
1566 chk_chg_dpnt_cvg_dependency
1567 (p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id,
1568 p_ler_chg_dpnt_cvg_cd => p_rec.ler_chg_dpnt_cvg_cd,
1569 p_ler_chg_dpnt_cvg_rl => p_rec.ler_chg_dpnt_cvg_rl,
1570 p_effective_date => p_effective_date,
1571 p_object_version_number => p_rec.object_version_number);
1572 --
1573 -- Call the datetrack update integrity operation
1574 --
1575 dt_update_validate
1576 (p_ler_chg_dpnt_cvg_rl => p_rec.ler_chg_dpnt_cvg_rl,
1577 p_cvg_eff_strt_rl => p_rec.cvg_eff_strt_rl,
1578 p_cvg_eff_end_rl => p_rec.cvg_eff_end_rl,
1579 p_ptip_id => p_rec.ptip_id,
1580 p_ler_id => p_rec.ler_id,
1581 p_pl_id => p_rec.pl_id,
1582 p_pgm_id => p_rec.pgm_id,
1583 p_datetrack_mode => p_datetrack_mode,
1584 p_validation_start_date => p_validation_start_date,
1585 p_validation_end_date => p_validation_end_date);
1586 --
1587 hr_utility.set_location(' Leaving:'||l_proc, 10);
1588 End update_validate;
1589 --
1590 -- ----------------------------------------------------------------------------
1591 -- |---------------------------< delete_validate >----------------------------|
1592 -- ----------------------------------------------------------------------------
1593 Procedure delete_validate
1594 (p_rec in ben_ldc_shd.g_rec_type,
1595 p_effective_date in date,
1596 p_datetrack_mode in varchar2,
1597 p_validation_start_date in date,
1598 p_validation_end_date in date) is
1599 --
1600 l_proc varchar2(72) := g_package||'delete_validate';
1601 --
1602 Begin
1603 hr_utility.set_location('Entering:'||l_proc, 5);
1604 --
1605 -- Call all supporting business operations
1606 --
1607 dt_delete_validate
1608 (p_datetrack_mode => p_datetrack_mode,
1609 p_validation_start_date => p_validation_start_date,
1610 p_validation_end_date => p_validation_end_date,
1611 p_ler_chg_dpnt_cvg_id => p_rec.ler_chg_dpnt_cvg_id);
1612 --
1613 hr_utility.set_location(' Leaving:'||l_proc, 10);
1614 End delete_validate;
1615 --
1616 --
1617 -- ---------------------------------------------------------------------------
1618 -- |---------------------< return_legislation_code >-------------------------|
1619 -- ---------------------------------------------------------------------------
1620 --
1621 function return_legislation_code
1622 (p_ler_chg_dpnt_cvg_id in number) return varchar2 is
1623 --
1624 -- Declare cursor
1625 --
1626 cursor csr_leg_code is
1627 select a.legislation_code
1628 from per_business_groups a,
1629 ben_ler_chg_dpnt_cvg_f b
1630 where b.ler_chg_dpnt_cvg_id = p_ler_chg_dpnt_cvg_id
1631 and a.business_group_id = b.business_group_id;
1632 --
1633 -- Declare local variables
1634 --
1635 l_legislation_code varchar2(150);
1636 l_proc varchar2(72) := g_package||'return_legislation_code';
1637 --
1638 begin
1639 --
1640 hr_utility.set_location('Entering:'|| l_proc, 10);
1641 --
1642 -- Ensure that all the mandatory parameter are not null
1643 --
1644 hr_api.mandatory_arg_error(p_api_name => l_proc,
1645 p_argument => 'ler_chg_dpnt_cvg_id',
1646 p_argument_value => p_ler_chg_dpnt_cvg_id);
1647 --
1648 open csr_leg_code;
1649 --
1650 fetch csr_leg_code into l_legislation_code;
1651 --
1652 if csr_leg_code%notfound then
1653 --
1654 close csr_leg_code;
1655 --
1656 -- The primary key is invalid therefore we must error
1657 --
1658 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1659 fnd_message.raise_error;
1660 --
1661 end if;
1662 --
1663 close csr_leg_code;
1664 --
1665 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1666 --
1667 return l_legislation_code;
1668 --
1669 end return_legislation_code;
1670 --
1671 end ben_ldc_bus;