[Home] [Help]
PACKAGE BODY: APPS.BEN_PRG_BUS
Source
1 Package Body ben_prg_bus as
2 /* $Header: beprgrhi.pkb 120.1 2008/02/05 09:42:42 rtagarra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_prg_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-------------------------< chk_pl_gd_r_svc_ctfn_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 -- pl_gd_r_svc_ctfn_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_pl_regn_id(p_pl_regn_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_pl_regn_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_prg_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_pl_regn_id => p_pl_regn_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_pl_regn_id,hr_api.g_number)
55 <> ben_prg_shd.g_old_rec.pl_regn_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_prg_shd.constraint_error('BEN_PL_REGN_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_pl_regn_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_prg_shd.constraint_error('BEN_PL_REGN_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_pl_regn_id;
78
79 -- ----------------------------------------------------------------------------
80 -- |------------------------------< chk_parent_rec_exists >-------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure checks that a parent rec exists in different business group
85 --
86 -- Pre-Conditions
87 -- None.
91 -- p_business_group_id
88 --
89 -- In Parameters
90 -- p_rptg_grp_id ID of FK column
92 -- p_effective_date session date
93 -- p_object_version_number object version number
94 --
95 -- Post Success
96 -- Processing continues
97 --
98 -- Post Failure
99 -- Error raised.
100 --
101 -- Access Status
102 -- Internal table handler use only.
103 --
104 Procedure chk_parent_rec_exists
105 (p_rptg_grp_id in number,
106 p_business_group_id in number,
107 p_effective_date in date,
108 p_object_version_number in number) is
109 --
110 l_proc varchar2(72) := g_package||'chk_parent_rec_exists';
111 l_api_updating boolean;
112 l_dummy varchar2(1);
113 --
114 -- This should return if there is a parent in different business group
115 -- If there is no parent business group id this should not return any rows
116 cursor c1 is
117 select null
118 from ben_rptg_grp bnr
119 where bnr.rptg_grp_id = p_rptg_grp_id
120 and nvl(bnr.business_group_id,p_business_group_id) <> p_business_group_id ;
121 --
122 Begin
123 --
124 hr_utility.set_location('Entering:'||l_proc,5);
125 --
126 -- check if rptg_grp_id value exists in ben_rptg_grp table
127 --
128 open c1;
129 --
130 fetch c1 into l_dummy;
131 if c1%found then
132 --
133 close c1;
134 --
135 -- raise error
136 fnd_message.set_name('BEN','BEN_92776_PARENT_REC_EXISTS');
137 fnd_message.raise_error;
138 --
139 end if;
140 --
141 close c1;
142 --
143 --
144 hr_utility.set_location('Leaving:'||l_proc,10);
145 --
146 End chk_parent_rec_exists;
147 --
148
149 -- ----------------------------------------------------------------------------
150 -- |----------------------------< chk_rptg_grp_id >---------------------------|
151 -- ----------------------------------------------------------------------------
152 --
153 -- Description
154 -- This procedure checks that a referenced foreign key actually exists
155 -- in the referenced table.
156 --
157 -- Pre-Conditions
158 -- None.
159 --
160 -- In Parameters
161 -- p_pl_regn_id PK
162 -- p_regy_grp_id ID of FK column
163 -- p_effective_date Session Date of record
164 -- p_object_version_number object version number
165 --
166 -- Post Success
167 -- Processing continues
168 --
169 -- Post Failure
170 -- Error raised.
171 --
172 -- Access Status
173 -- Internal table handler use only.
174 --
175 Procedure chk_rptg_grp_id (p_pl_regn_id in number,
176 p_rptg_grp_id in number,
177 p_effective_date in date,
178 p_object_version_number in number) is
179 --
180 l_proc varchar2(72) := g_package||'chk_rptg_grp_id';
181 l_api_updating boolean;
182 l_dummy varchar2(1);
183 --
184 cursor c1 is
185 select null
186 from ben_rptg_grp a
187 where a.rptg_grp_id = p_rptg_grp_id;
188 --
189 Begin
190 --
191 hr_utility.set_location('Entering:'||l_proc,5);
192 --
193 l_api_updating := ben_prg_shd.api_updating
194 (p_pl_regn_id => p_pl_regn_id,
195 p_effective_date => p_effective_date,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_rptg_grp_id,hr_api.g_number)
200 <> nvl(ben_prg_shd.g_old_rec.rptg_grp_id,hr_api.g_number)
201 or not l_api_updating) then
202 --
203 -- check if pl_id value exists in ben_rept_grp table
204 --
205 open c1;
206 --
207 fetch c1 into l_dummy;
208 if c1%notfound then
209 --
210 close c1;
211 --
212 -- raise error as FK does not relate to PK in ben_rptg_grp
213 -- table.
214 --
215 ben_cpy_shd.constraint_error('BEN_PL_REGN_FK2');
216 --
217 end if;
218 --
219 close c1;
220 --
221 end if;
222 --
223 hr_utility.set_location('Leaving:'||l_proc,10);
224 --
225 End chk_rptg_grp_id;
226 -- ----------------------------------------------------------------------------
227 -- |--------------------< chk_hghly_compd_det_rl >----------------------------|
228 -- ----------------------------------------------------------------------------
229 --
230 -- Description
231 -- This procedure is used to check that the Formula Rule is valid.
232 --
233 -- Pre Conditions
234 -- None.
235 --
236 -- In Parameters
237 -- pl_regn_id PK of record being inserted or updated.
238 -- hghly_compd_det_rl Value of formula rule id.
239 -- effective_date effective date
240 -- object_version_number Object version number of record being
241 -- inserted or updated.
242 --
243 -- Post Success
244 -- Processing continues
245 --
246 -- Post Failure
247 -- Error handled by procedure
248 --
249 -- Access Status
250 -- Internal table handler use only.
251 --
252 Procedure chk_hghly_compd_det_rl(p_pl_regn_id in number,
253 p_business_group_id in number,
254 p_hghly_compd_det_rl in number,
258 l_proc varchar2(72) := g_package||'chk_hghly_compd_det_rl';
255 p_effective_date in date,
256 p_object_version_number in number) is
257 --
259 l_api_updating boolean;
260 l_dummy varchar2(1);
261 --
262 cursor c1 is
263 select null
264 from ff_formulas_f ff
265 ,per_business_groups pbg
266 where ff.formula_id = p_hghly_compd_det_rl
267 and ff.formula_type_id = -31
268 and pbg.business_group_id = p_business_group_id
269 and nvl(ff.business_group_id, p_business_group_id) =
270 p_business_group_id
271 and nvl(ff.legislation_code, pbg.legislation_code) =
272 pbg.legislation_code
273 and p_effective_date
274 between ff.effective_start_date
275 and ff.effective_end_date;
276 --
277 Begin
278 --
279 hr_utility.set_location('Entering:'||l_proc, 5);
280 --
281 l_api_updating := ben_prg_shd.api_updating
282 (p_pl_regn_id => p_pl_regn_id ,
283 p_effective_date => p_effective_date,
284 p_object_version_number => p_object_version_number);
285 --
286 if (l_api_updating
287 and nvl(p_hghly_compd_det_rl ,hr_api.g_number)
288 <> ben_prg_shd.g_old_rec.hghly_compd_det_rl
289 or not l_api_updating)
290 and p_hghly_compd_det_rl is not null then
291 --
292 -- check if value of formula rule is valid.
293 --
294 open c1;
295 --
296 -- fetch value from cursor if it returns a record then the
297 -- formula is valid otherwise its invalid
298 --
299 fetch c1 into l_dummy;
300 if c1%notfound then
301 --
302 close c1;
303 --
304 -- raise error
305 --
306 fnd_message.set_name('BEN','BEN_91064_INVLD_HG_COMP_DET_RL');
307 fnd_message.raise_error;
308 --
309 end if;
310 --
311 close c1;
312 --
313 end if;
314 --
315 hr_utility.set_location('Leaving:'||l_proc,10);
316 --
317 end chk_hghly_compd_det_rl ;
318 -- ----------------------------------------------------------------------------
319 -- |--------------------< chk_key_ee_det_rl >---------------------------------|
320 -- ----------------------------------------------------------------------------
321 --
322 -- Description
323 -- This procedure is used to check that the Formula Rule is valid.
324 --
325 -- Pre Conditions
326 -- None.
327 --
328 -- In Parameters
329 -- pl_regn_id PK of record being inserted or updated.
330 -- hghly_compd_det_rl Value of formula rule id.
331 -- effective_date effective date
332 -- object_version_number Object version number of record being
333 -- inserted or updated.
334 --
335 -- Post Success
336 -- Processing continues
337 --
338 -- Post Failure
339 -- Error handled by procedure
340 --
341 -- Access Status
342 -- Internal table handler use only.
343 --
344 Procedure chk_key_ee_det_rl(p_pl_regn_id in number,
345 p_business_group_id in number,
346 p_key_ee_det_rl in number,
347 p_effective_date in date,
348 p_object_version_number in number) is
349 --
350 l_proc varchar2(72) := g_package||'chk_key_ee_det_rl';
351 l_api_updating boolean;
352 l_dummy varchar2(1);
353 --
354 cursor c1 is
355 select null
356 from ff_formulas_f ff
357 ,per_business_groups pbg
358 where ff.formula_id = p_key_ee_det_rl
359 and ff.formula_type_id = -39
360 and pbg.business_group_id = p_business_group_id
361 and nvl(ff.business_group_id, p_business_group_id) =
362 p_business_group_id
363 and nvl(ff.legislation_code, pbg.legislation_code) =
364 pbg.legislation_code
365 and p_effective_date
366 between ff.effective_start_date
367 and ff.effective_end_date;
368 --
369 Begin
370 --
371 hr_utility.set_location('Entering:'||l_proc, 5);
372 --
373 l_api_updating := ben_prg_shd.api_updating
374 (p_pl_regn_id => p_pl_regn_id,
375 p_effective_date => p_effective_date,
376 p_object_version_number => p_object_version_number);
377 --
378 if (l_api_updating
379 and nvl(p_key_ee_det_rl ,hr_api.g_number)
380 <> ben_prg_shd.g_old_rec.key_ee_det_rl
381 or not l_api_updating)
382 and p_key_ee_det_rl is not null then
383 --
384 -- check if value of formula rule is valid.
385 --
386 open c1;
387 --
388 -- fetch value from cursor if it returns a record then the
389 -- formula is valid otherwise its invalid
390 --
391 fetch c1 into l_dummy;
392 if c1%notfound then
393 --
394 close c1;
395 --
396 -- raise error
397 --
398 fnd_message.set_name('BEN','BEN_91089_INVLD_KEY_EE_DET_RL');
399 fnd_message.raise_error;
400 --
401 end if;
402 --
403 close c1;
404 --
405 end if;
406 --
407 hr_utility.set_location('Leaving:'||l_proc,10);
408 --
412 -- ----------------------------------------------------------------------------
409 end chk_key_ee_det_rl;
410 -- ----------------------------------------------------------------------------
411 -- |--------------------< chk_cntr_nndscrn_rl >-------------------------------|
413 --
414 -- Description
415 -- This procedure is used to check that the Formula Rule is valid.
416 --
417 -- Pre Conditions
418 -- None.
419 --
420 -- In Parameters
421 -- cntr_nndscrn_rl PK of record being inserted or updated.
422 -- hghly_compd_det_rl Value of formula rule id.
423 -- effective_date effective date
424 -- object_version_number Object version number of record being
425 -- inserted or updated.
426 --
427 -- Post Success
428 -- Processing continues
429 --
430 -- Post Failure
431 -- Error handled by procedure
432 --
433 -- Access Status
434 -- Internal table handler use only.
435 --
436 Procedure chk_cntr_nndscrn_rl(p_pl_regn_id in number,
437 p_business_group_id in number,
438 p_cntr_nndscrn_rl in number,
439 p_effective_date in date,
440 p_object_version_number in number) is
441 --
442 l_proc varchar2(72) := g_package||'chk_cntr_nndscrn_rl';
443 l_api_updating boolean;
444 l_dummy varchar2(1);
445 --
446 cursor c1 is
447 select null
448 from ff_formulas_f ff
449 ,per_business_groups pbg
450 where ff.formula_id = p_cntr_nndscrn_rl
451 and ff.formula_type_id = -42
452 and pbg.business_group_id = p_business_group_id
453 and nvl(ff.business_group_id, p_business_group_id) =
454 p_business_group_id
455 and nvl(ff.legislation_code, pbg.legislation_code) =
456 pbg.legislation_code
457 and p_effective_date
458 between ff.effective_start_date
459 and ff.effective_end_date;
460 --
461 Begin
462 --
463 hr_utility.set_location('Entering:'||l_proc, 5);
464 --
465 l_api_updating := ben_prg_shd.api_updating
466 (p_pl_regn_id => p_pl_regn_id,
467 p_effective_date => p_effective_date,
468 p_object_version_number => p_object_version_number);
469 --
470 if (l_api_updating
471 and nvl(p_cntr_nndscrn_rl ,hr_api.g_number)
472 <> ben_prg_shd.g_old_rec.cntr_nndscrn_rl
473 or not l_api_updating)
474 and p_cntr_nndscrn_rl is not null then
475 --
476 -- check if value of formula rule is valid.
477 --
478 open c1;
479 --
480 -- fetch value from cursor if it returns a record then the
481 -- formula is valid otherwise its invalid
482 --
483 fetch c1 into l_dummy;
484 if c1%notfound then
485 --
486 close c1;
487 --
488 -- raise error
489 --
490 fnd_message.set_name('BEN','BEN_91090_INVLD_CNTR_NDSCRN_RL');
491 fnd_message.raise_error;
492 --
493 end if;
494 --
495 close c1;
496 --
497 end if;
498 --
499 hr_utility.set_location('Leaving:'||l_proc,10);
500 --
501 end chk_cntr_nndscrn_rl ;
502 -- ----------------------------------------------------------------------------
503 -- |--------------------< chk_cvg_nndscrn_rl >--------------------------------|
504 -- ----------------------------------------------------------------------------
505 --
506 -- Description
507 -- This procedure is used to check that the Formula Rule is valid.
508 --
509 -- Pre Conditions
510 -- None.
511 --
512 -- In Parameters
513 -- cvg_nndscrn_rl PK of record being inserted or updated.
514 -- hghly_compd_det_rl Value of formula rule id.
515 -- effective_date effective date
516 -- object_version_number Object version number of record being
517 -- inserted or updated.
518 --
519 -- Post Success
520 -- Processing continues
521 --
522 -- Post Failure
523 -- Error handled by procedure
524 --
525 -- Access Status
526 -- Internal table handler use only.
527 --
528 Procedure chk_cvg_nndscrn_rl(p_pl_regn_id in number,
529 p_business_group_id in number,
530 p_cvg_nndscrn_rl in number,
531 p_effective_date in date,
532 p_object_version_number in number) is
533 --
534 l_proc varchar2(72) := g_package||'chk_cvg_nndscrn_rl';
535 l_api_updating boolean;
536 l_dummy varchar2(1);
537 --
538 cursor c1 is
539 select null
540 from ff_formulas_f ff
541 ,per_business_groups pbg
542 where ff.formula_id = p_cvg_nndscrn_rl
543 and ff.formula_type_id = -41
544 and pbg.business_group_id = p_business_group_id
545 and nvl(ff.business_group_id, p_business_group_id) =
546 p_business_group_id
547 and nvl(ff.legislation_code, pbg.legislation_code) =
548 pbg.legislation_code
549 and p_effective_date
550 between ff.effective_start_date
551 and ff.effective_end_date;
552 --
553 Begin
554 --
555 hr_utility.set_location('Entering:'||l_proc, 5);
556 --
557 l_api_updating := ben_prg_shd.api_updating
561 --
558 (p_pl_regn_id => p_pl_regn_id ,
559 p_effective_date => p_effective_date,
560 p_object_version_number => p_object_version_number);
562 if (l_api_updating
563 and nvl(p_cvg_nndscrn_rl ,hr_api.g_number)
564 <> ben_prg_shd.g_old_rec.cvg_nndscrn_rl
565 or not l_api_updating)
566 and p_cvg_nndscrn_rl is not null then
567 --
568 -- check if value of formula rule is valid.
569 --
570 open c1;
571 --
572 -- fetch value from cursor if it returns a record then the
573 -- formula is valid otherwise its invalid
574 --
575 fetch c1 into l_dummy;
576 if c1%notfound then
577 --
578 close c1;
579 --
580 -- raise error
581 --
582 fnd_message.set_name('BEN','BEN_91091_INVLD_CVG_NDSCRN_RL');
583 fnd_message.raise_error;
584 --
585 end if;
586 --
587 close c1;
588 --
589 end if;
590 --
591 hr_utility.set_location('Leaving:'||l_proc,10);
592 --
593 end chk_cvg_nndscrn_rl;
594 -- ----------------------------------------------------------------------------
595 -- |--------------------< chk_five_pct_ownr_rl >------------------------------|
596 -- ----------------------------------------------------------------------------
597 --
598 -- Description
599 -- This procedure is used to check that the Formula Rule is valid.
600 --
601 -- Pre Conditions
602 -- None.
603 --
604 -- In Parameters
605 -- five_pct_ownr_rl PK of record being inserted or updated.
606 -- hghly_compd_det_rl Value of formula rule id.
607 -- effective_date effective date
608 -- object_version_number Object version number of record being
609 -- inserted or updated.
610 --
611 -- Post Success
612 -- Processing continues
613 --
614 -- Post Failure
615 -- Error handled by procedure
616 --
617 -- Access Status
618 -- Internal table handler use only.
619 --
620 Procedure chk_five_pct_ownr_rl(p_pl_regn_id in number,
621 p_business_group_id in number,
622 p_five_pct_ownr_rl in number,
623 p_effective_date in date,
624 p_object_version_number in number) is
625 --
626 l_proc varchar2(72) := g_package||'chk_five_pct_ownr_rl';
627 l_api_updating boolean;
628 l_dummy varchar2(1);
629 --
630 cursor c1 is
631 select null
632 from ff_formulas_f ff
633 ,per_business_groups pbg
634 where ff.formula_id = p_five_pct_ownr_rl
635 and ff.formula_type_id = -154
636 and pbg.business_group_id = p_business_group_id
637 and nvl(ff.business_group_id, p_business_group_id) =
638 p_business_group_id
639 and nvl(ff.legislation_code, pbg.legislation_code) =
640 pbg.legislation_code
641 and p_effective_date
642 between ff.effective_start_date
643 and ff.effective_end_date;
644 --
645 Begin
646 --
647 hr_utility.set_location('Entering:'||l_proc, 5);
648 --
649 l_api_updating := ben_prg_shd.api_updating
650 (p_pl_regn_id => p_pl_regn_id,
651 p_effective_date => p_effective_date,
652 p_object_version_number => p_object_version_number);
653 --
654 if (l_api_updating
655 and nvl(p_five_pct_ownr_rl ,hr_api.g_number)
656 <> ben_prg_shd.g_old_rec.five_pct_ownr_rl
657 or not l_api_updating)
658 and p_five_pct_ownr_rl is not null then
659 --
660 -- check if value of formula rule is valid.
661 --
662 open c1;
663 --
664 -- fetch value from cursor if it returns a record then the
665 -- formula is valid otherwise its invalid
666 --
667 fetch c1 into l_dummy;
668 if c1%notfound then
669 --
670 close c1;
671 --
672 -- raise error
673 --
674 fnd_message.set_name('BEN','BEN_91092_INVLD_5_PCT_OWNR_RL');
675 fnd_message.raise_error;
676 --
677 end if;
678 --
679 close c1;
680 --
681 end if;
682 --
683 hr_utility.set_location('Leaving:'||l_proc,10);
684 --
685 end chk_five_pct_ownr_rl;
686
687 -- ----------------------------------------------------------------------------
688 -- |-----------------------< chk_regy_pl_typ_cd >-----------------------------|
689 -- ----------------------------------------------------------------------------
690 --
691 -- Description
692 -- This procedure is used to check that the lookup value is valid.
693 --
694 -- Pre Conditions
695 -- None.
696 --
697 -- In Parameters
698 -- pl_regn_id PK of record being inserted or updated.
699 -- regy_pl_typ_cd Value of lookup code.
700 -- effective_date effective date
701 -- object_version_number Object version number of record being
702 -- inserted or updated.
703 --
704 -- Post Success
705 -- Processing continues
706 --
707 -- Post Failure
708 -- Error handled by procedure
709 --
710 -- Access Status
711 -- Internal table handler use only.
712 --
713 Procedure chk_regy_pl_typ_cd(p_pl_regn_id in number,
714 p_regy_pl_typ_cd in varchar2,
715 p_effective_date in date,
716 p_object_version_number in number) is
717 --
718 l_proc varchar2(72) := g_package||'chk_regy_pl_typ_cd';
719 l_api_updating boolean;
720 --
721 Begin
722 --
723 hr_utility.set_location('Entering:'||l_proc, 5);
724 --
725 l_api_updating := ben_prg_shd.api_updating
726 (p_pl_regn_id => p_pl_regn_id,
727 p_effective_date => p_effective_date,
728 p_object_version_number => p_object_version_number);
729 --
730 if (l_api_updating
731 and p_regy_pl_typ_cd
732 <> nvl(ben_prg_shd.g_old_rec.regy_pl_typ_cd,hr_api.g_varchar2)
733 or not l_api_updating) and p_regy_pl_typ_cd is not null then
734 --
735 -- check if value of lookup falls within lookup type.
736 --
737 if hr_api.not_exists_in_hr_lookups
738 (p_lookup_type => 'BEN_REGY_PL_TYP',
739 p_lookup_code => p_regy_pl_typ_cd,
740 p_effective_date => p_effective_date) then
741 --
742 -- raise error as does not exist as lookup
743 --
744 fnd_message.set_name('BEN','BEN_91093_INVLD_REGY_PL_TYP_CD');
745 fnd_message.raise_error;
746 --
747 end if;
748 --
749 end if;
750 --
751 hr_utility.set_location('Leaving:'||l_proc,10);
752 --
753 end chk_regy_pl_typ_cd;
754
755 -- ----------------------------------------------------------------------------
756 -- |--------------------------< dt_update_validate >--------------------------|
757 -- ----------------------------------------------------------------------------
758 -- {Start Of Comments}
759 --
760 -- Description:
761 -- This procedure is used for referential integrity of datetracked
762 -- parent entities when a datetrack update operation is taking place
763 -- and where there is no cascading of update defined for this entity.
764 --
765 -- Prerequisites:
766 -- This procedure is called from the update_validate.
767 --
768 -- In Parameters:
769 --
770 -- Post Success:
771 -- Processing continues.
772 --
773 -- Post Failure:
774 --
775 -- Developer Implementation Notes:
776 -- This procedure should not need maintenance unless the HR Schema model
777 -- changes.
778 --
779 -- Access Status:
780 -- Internal Row Handler Use Only.
781 --
782 -- {End Of Comments}
783 -- ----------------------------------------------------------------------------
784 Procedure dt_update_validate
785 (p_hghly_compd_det_rl in number default hr_api.g_number,
786 p_key_ee_det_rl in number default hr_api.g_number,
787 p_cntr_nndscrn_rl in number default hr_api.g_number,
788 p_cvg_nndscrn_rl in number default hr_api.g_number,
789 p_five_pct_ownr_rl in number default hr_api.g_number,
790 p_regn_id in number default hr_api.g_number,
791 p_pl_id in number default hr_api.g_number,
792 p_datetrack_mode in varchar2,
793 p_validation_start_date in date,
794 p_validation_end_date in date) Is
795 --
796 l_proc varchar2(72) := g_package||'dt_update_validate';
797 l_integrity_error Exception;
798 l_table_name all_tables.table_name%TYPE;
799 --
800 Begin
801 hr_utility.set_location('Entering:'||l_proc, 5);
802 --
803 -- Ensure that the p_datetrack_mode argument is not null
804 --
805 hr_api.mandatory_arg_error
806 (p_api_name => l_proc,
807 p_argument => 'datetrack_mode',
808 p_argument_value => p_datetrack_mode);
809 --
810 -- Only perform the validation if the datetrack update mode is valid
811 --
812 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
813 --
814 --
815 -- Ensure the arguments are not null
816 --
817 hr_api.mandatory_arg_error
818 (p_api_name => l_proc,
819 p_argument => 'validation_start_date',
820 p_argument_value => p_validation_start_date);
821 --
822 hr_api.mandatory_arg_error
823 (p_api_name => l_proc,
824 p_argument => 'validation_end_date',
825 p_argument_value => p_validation_end_date);
826 --
827 If ((nvl(p_hghly_compd_det_rl, hr_api.g_number) <> hr_api.g_number) and
828 NOT (dt_api.check_min_max_dates
829 (p_base_table_name => 'ff_formulas_f',
830 p_base_key_column => 'formula_id',
831 p_base_key_value => p_hghly_compd_det_rl,
835 Raise l_integrity_error;
832 p_from_date => p_validation_start_date,
833 p_to_date => p_validation_end_date))) Then
834 l_table_name := 'ff_formulas_f';
836 End If;
837 If ((nvl(p_key_ee_det_rl, hr_api.g_number) <> hr_api.g_number) and
838 NOT (dt_api.check_min_max_dates
839 (p_base_table_name => 'ff_formulas_f',
840 p_base_key_column => 'formula_id',
841 p_base_key_value => p_key_ee_det_rl,
842 p_from_date => p_validation_start_date,
843 p_to_date => p_validation_end_date))) Then
844 l_table_name := 'ff_formulas_f';
845 Raise l_integrity_error;
846 End If;
847 If ((nvl(p_cntr_nndscrn_rl, hr_api.g_number) <> hr_api.g_number) and
848 NOT (dt_api.check_min_max_dates
849 (p_base_table_name => 'ff_formulas_f',
850 p_base_key_column => 'formula_id',
851 p_base_key_value => p_cntr_nndscrn_rl,
852 p_from_date => p_validation_start_date,
853 p_to_date => p_validation_end_date))) Then
854 l_table_name := 'ff_formulas_f';
855 Raise l_integrity_error;
856 End If;
857 If ((nvl(p_cvg_nndscrn_rl, hr_api.g_number) <> hr_api.g_number) and
858 NOT (dt_api.check_min_max_dates
859 (p_base_table_name => 'ff_formulas_f',
860 p_base_key_column => 'formula_id',
861 p_base_key_value => p_cvg_nndscrn_rl,
862 p_from_date => p_validation_start_date,
863 p_to_date => p_validation_end_date))) Then
864 l_table_name := 'ff_formulas_f';
865 Raise l_integrity_error;
866 End If;
867 If ((nvl(p_five_pct_ownr_rl, hr_api.g_number) <> hr_api.g_number) and
868 NOT (dt_api.check_min_max_dates
869 (p_base_table_name => 'ff_formulas_f',
870 p_base_key_column => 'formula_id',
871 p_base_key_value => p_five_pct_ownr_rl,
872 p_from_date => p_validation_start_date,
873 p_to_date => p_validation_end_date))) Then
874 l_table_name := 'ff_formulas_f';
875 Raise l_integrity_error;
876 End If;
877 If ((nvl(p_regn_id, hr_api.g_number) <> hr_api.g_number) and
878 NOT (dt_api.check_min_max_dates
879 (p_base_table_name => 'ben_regn_f',
880 p_base_key_column => 'regn_id',
881 p_base_key_value => p_regn_id,
882 p_from_date => p_validation_start_date,
883 p_to_date => p_validation_end_date))) Then
884 l_table_name := 'ben_regn_f';
885 Raise l_integrity_error;
886 End If;
887 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
888 NOT (dt_api.check_min_max_dates
889 (p_base_table_name => 'ben_pl_f',
890 p_base_key_column => 'pl_id',
891 p_base_key_value => p_pl_id,
892 p_from_date => p_validation_start_date,
893 p_to_date => p_validation_end_date))) Then
894 l_table_name := 'ben_pl_f';
895 Raise l_integrity_error;
896 End If;
897 --
898 End If;
899 --
900 hr_utility.set_location(' Leaving:'||l_proc, 10);
901 Exception
902 When l_integrity_error Then
903 --
904 -- A referential integrity check was violated therefore
905 -- we must error
906 --
907 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
908 fnd_message.set_token('TABLE_NAME', l_table_name);
909 fnd_message.raise_error;
910 When Others Then
911 --
912 -- An unhandled or unexpected error has occurred which
913 -- we must report
914 --
915 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
916 fnd_message.set_token('PROCEDURE', l_proc);
917 fnd_message.set_token('STEP','15');
918 fnd_message.raise_error;
919 End dt_update_validate;
920 --
921 -- ----------------------------------------------------------------------------
922 -- |--------------------------< dt_delete_validate >--------------------------|
923 -- ----------------------------------------------------------------------------
924 -- {Start Of Comments}
925 --
926 -- Description:
927 -- This procedure is used for referential integrity of datetracked
928 -- child entities when either a datetrack DELETE or ZAP is in operation
929 -- and where there is no cascading of delete defined for this entity.
930 -- For the datetrack mode of DELETE or ZAP we must ensure that no
931 -- datetracked child rows exist between the validation start and end
932 -- dates.
933 --
934 -- Prerequisites:
935 -- This procedure is called from the delete_validate.
936 --
937 -- In Parameters:
938 --
939 -- Post Success:
940 -- Processing continues.
941 --
942 -- Post Failure:
943 -- If a row exists by determining the returning Boolean value from the
944 -- generic dt_api.rows_exist function then we must supply an error via
945 -- the use of the local exception handler l_rows_exist.
946 --
947 -- Developer Implementation Notes:
948 -- This procedure should not need maintenance unless the HR Schema model
949 -- changes.
950 --
951 -- Access Status:
952 -- Internal Row Handler Use Only.
953 --
954 -- {End Of Comments}
955 -- ----------------------------------------------------------------------------
956 Procedure dt_delete_validate
957 (p_pl_regn_id in number,
958 p_datetrack_mode in varchar2,
959 p_validation_start_date in date,
960 p_validation_end_date in date) Is
961 --
962 l_proc varchar2(72) := g_package||'dt_delete_validate';
963 l_rows_exist Exception;
967 hr_utility.set_location('Entering:'||l_proc, 5);
964 l_table_name all_tables.table_name%TYPE;
965 --
966 Begin
968 --
969 -- Ensure that the p_datetrack_mode argument is not null
970 --
971 hr_api.mandatory_arg_error
972 (p_api_name => l_proc,
973 p_argument => 'datetrack_mode',
974 p_argument_value => p_datetrack_mode);
975 --
976 -- Only perform the validation if the datetrack mode is either
977 -- DELETE or ZAP
978 --
979 If (p_datetrack_mode = 'DELETE' or
980 p_datetrack_mode = 'ZAP') then
981 --
982 --
983 -- Ensure the arguments are not null
984 --
985 hr_api.mandatory_arg_error
986 (p_api_name => l_proc,
987 p_argument => 'validation_start_date',
988 p_argument_value => p_validation_start_date);
989 --
990 hr_api.mandatory_arg_error
991 (p_api_name => l_proc,
992 p_argument => 'validation_end_date',
993 p_argument_value => p_validation_end_date);
994 --
995 hr_api.mandatory_arg_error
996 (p_api_name => l_proc,
997 p_argument => 'pl_regn_id',
998 p_argument_value => p_pl_regn_id);
999 --
1000 --
1001 --
1002 End If;
1003 --
1004 hr_utility.set_location(' Leaving:'||l_proc, 10);
1005 Exception
1006 When l_rows_exist Then
1007 --
1008 -- A referential integrity check was violated therefore
1009 -- we must error
1010 --
1011 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
1012 fnd_message.set_token('TABLE_NAME', l_table_name);
1013 fnd_message.raise_error;
1014 When Others Then
1015 --
1016 -- An unhandled or unexpected error has occurred which
1017 -- we must report
1018 --
1019 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1020 fnd_message.set_token('PROCEDURE', l_proc);
1021 fnd_message.set_token('STEP','15');
1022 fnd_message.raise_error;
1023 End dt_delete_validate;
1024 --
1025 -- ----------------------------------------------------------------------------
1026 -- |---------------------------< insert_validate >----------------------------|
1027 -- ----------------------------------------------------------------------------
1028 Procedure insert_validate
1029 (p_rec in ben_prg_shd.g_rec_type,
1030 p_effective_date in date,
1031 p_datetrack_mode in varchar2,
1032 p_validation_start_date in date,
1033 p_validation_end_date in date) is
1034 --
1035 l_proc varchar2(72) := g_package||'insert_validate';
1036 --
1037 Begin
1038 hr_utility.set_location('Entering:'||l_proc, 5);
1039 --
1040 -- Call all supporting business operations
1041 --
1042 --CWB Changes
1043 if p_rec.business_group_id is not null then
1044 --
1045 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1046 --
1047 end if;
1048 --
1049 chk_pl_regn_id
1050 (p_pl_regn_id => p_rec.pl_regn_id,
1051 p_effective_date => p_effective_date,
1052 p_object_version_number => p_rec.object_version_number);
1053 --
1054 chk_rptg_grp_id
1055 (p_pl_regn_id => p_rec.pl_regn_id,
1056 p_rptg_grp_id => p_rec.rptg_grp_id,
1057 p_effective_date => p_effective_date,
1058 p_object_version_number => p_rec.object_version_number);
1059 --
1060 --
1061 hr_api.mandatory_arg_error
1062 (p_api_name => l_proc,
1063 p_argument => 'Regulation',
1064 p_argument_value => p_rec.regn_id);
1065 --
1066 chk_hghly_compd_det_rl
1067 (p_pl_regn_id => p_rec.pl_regn_id,
1068 p_business_group_id => p_rec.business_group_id,
1069 p_hghly_compd_det_rl => p_rec.hghly_compd_det_rl,
1070 p_effective_date => p_effective_date,
1071 p_object_version_number => p_rec.object_version_number);
1072 --
1073 chk_key_ee_det_rl
1074 (p_pl_regn_id => p_rec.pl_regn_id,
1075 p_business_group_id => p_rec.business_group_id,
1076 p_key_ee_det_rl => p_rec.key_ee_det_rl,
1077 p_effective_date => p_effective_date,
1078 p_object_version_number => p_rec.object_version_number);
1079 --
1080 chk_cntr_nndscrn_rl
1081 (p_pl_regn_id => p_rec.pl_regn_id,
1082 p_business_group_id => p_rec.business_group_id,
1083 p_cntr_nndscrn_rl => p_rec.cntr_nndscrn_rl,
1084 p_effective_date => p_effective_date,
1085 p_object_version_number => p_rec.object_version_number);
1086 --
1087 chk_cvg_nndscrn_rl
1088 (p_pl_regn_id => p_rec.pl_regn_id,
1089 p_business_group_id => p_rec.business_group_id,
1090 p_cvg_nndscrn_rl => p_rec.cvg_nndscrn_rl,
1091 p_effective_date => p_effective_date,
1092 p_object_version_number => p_rec.object_version_number);
1093 --
1094 chk_five_pct_ownr_rl
1095 (p_pl_regn_id => p_rec.pl_regn_id,
1096 p_business_group_id => p_rec.business_group_id,
1097 p_five_pct_ownr_rl => p_rec.five_pct_ownr_rl,
1098 p_effective_date => p_effective_date,
1099 p_object_version_number => p_rec.object_version_number);
1100 --
1101 chk_regy_pl_typ_cd
1102 (p_pl_regn_id => p_rec.pl_regn_id,
1103 p_regy_pl_typ_cd => p_rec.regy_pl_typ_cd,
1104 p_effective_date => p_effective_date,
1105 p_object_version_number => p_rec.object_version_number);
1106 --
1107 chk_parent_rec_exists
1111 p_object_version_number => p_rec.object_version_number);
1108 (p_rptg_grp_id => p_rec.rptg_grp_id,
1109 p_business_group_id => p_rec.business_group_id,
1110 p_effective_date => p_effective_date,
1112
1113 --
1114 hr_utility.set_location(' Leaving:'||l_proc, 10);
1115 End insert_validate;
1116 --
1117 -- ----------------------------------------------------------------------------
1118 -- |---------------------------< update_validate >----------------------------|
1119 -- ----------------------------------------------------------------------------
1120 Procedure update_validate
1121 (p_rec in ben_prg_shd.g_rec_type,
1122 p_effective_date in date,
1123 p_datetrack_mode in varchar2,
1124 p_validation_start_date in date,
1125 p_validation_end_date in date) is
1126 --
1127 l_proc varchar2(72) := g_package||'update_validate';
1128 --
1129 Begin
1130 hr_utility.set_location('Entering:'||l_proc, 5);
1131 --
1132 -- Call all supporting business operations
1133 --
1134 -- CWB Changes
1135 if p_rec.business_group_id is not null then
1136 --
1137 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1138 --
1139 end if;
1140 --
1141 chk_pl_regn_id
1142 (p_pl_regn_id => p_rec.pl_regn_id,
1143 p_effective_date => p_effective_date,
1144 p_object_version_number => p_rec.object_version_number);
1145 --
1146 chk_rptg_grp_id
1147 (p_pl_regn_id => p_rec.pl_regn_id,
1148 p_rptg_grp_id => p_rec.rptg_grp_id,
1149 p_effective_date => p_effective_date,
1150 p_object_version_number => p_rec.object_version_number);
1151 --
1152 --
1153 hr_api.mandatory_arg_error
1154 (p_api_name => l_proc,
1155 p_argument => 'Regulation',
1156 p_argument_value => p_rec.regn_id);
1157 --
1158 chk_hghly_compd_det_rl
1159 (p_pl_regn_id => p_rec.pl_regn_id,
1160 p_business_group_id => p_rec.business_group_id,
1161 p_hghly_compd_det_rl => p_rec.hghly_compd_det_rl,
1162 p_effective_date => p_effective_date,
1163 p_object_version_number => p_rec.object_version_number);
1164 --
1165 chk_key_ee_det_rl
1166 (p_pl_regn_id => p_rec.pl_regn_id,
1167 p_business_group_id => p_rec.business_group_id,
1168 p_key_ee_det_rl => p_rec.key_ee_det_rl,
1169 p_effective_date => p_effective_date,
1170 p_object_version_number => p_rec.object_version_number);
1171 --
1172 chk_cntr_nndscrn_rl
1173 (p_pl_regn_id => p_rec.pl_regn_id,
1174 p_business_group_id => p_rec.business_group_id,
1175 p_cntr_nndscrn_rl => p_rec.cntr_nndscrn_rl,
1176 p_effective_date => p_effective_date,
1177 p_object_version_number => p_rec.object_version_number);
1178 --
1179 chk_cvg_nndscrn_rl
1180 (p_pl_regn_id => p_rec.pl_regn_id,
1181 p_business_group_id => p_rec.business_group_id,
1182 p_cvg_nndscrn_rl => p_rec.cvg_nndscrn_rl,
1183 p_effective_date => p_effective_date,
1184 p_object_version_number => p_rec.object_version_number);
1185 --
1186 chk_five_pct_ownr_rl
1187 (p_pl_regn_id => p_rec.pl_regn_id,
1188 p_business_group_id => p_rec.business_group_id,
1189 p_five_pct_ownr_rl => p_rec.five_pct_ownr_rl,
1190 p_effective_date => p_effective_date,
1191 p_object_version_number => p_rec.object_version_number);
1192 --
1193 chk_regy_pl_typ_cd
1194 (p_pl_regn_id => p_rec.pl_regn_id,
1195 p_regy_pl_typ_cd => p_rec.regy_pl_typ_cd,
1196 p_effective_date => p_effective_date,
1197 p_object_version_number => p_rec.object_version_number);
1198 --
1199 -- Call the datetrack update integrity operation
1200 --
1201 chk_parent_rec_exists
1202 (p_rptg_grp_id => p_rec.rptg_grp_id,
1203 p_business_group_id => p_rec.business_group_id,
1204 p_effective_date => p_effective_date,
1205 p_object_version_number => p_rec.object_version_number);
1206 --
1207 dt_update_validate
1208 (p_hghly_compd_det_rl => p_rec.hghly_compd_det_rl,
1209 p_key_ee_det_rl => p_rec.key_ee_det_rl,
1210 p_cntr_nndscrn_rl => p_rec.cntr_nndscrn_rl,
1211 p_cvg_nndscrn_rl => p_rec.cvg_nndscrn_rl,
1212 p_five_pct_ownr_rl => p_rec.five_pct_ownr_rl,
1213 p_regn_id => p_rec.regn_id,
1214 p_pl_id => p_rec.pl_id,
1215 p_datetrack_mode => p_datetrack_mode,
1216 p_validation_start_date => p_validation_start_date,
1217 p_validation_end_date => p_validation_end_date);
1218 --
1219 hr_utility.set_location(' Leaving:'||l_proc, 10);
1220 End update_validate;
1221 --
1222 -- ----------------------------------------------------------------------------
1223 -- |---------------------------< delete_validate >----------------------------|
1224 -- ----------------------------------------------------------------------------
1225 Procedure delete_validate
1226 (p_rec in ben_prg_shd.g_rec_type,
1227 p_effective_date in date,
1228 p_datetrack_mode in varchar2,
1229 p_validation_start_date in date,
1230 p_validation_end_date in date) is
1231 --
1232 l_proc varchar2(72) := g_package||'delete_validate';
1233 --
1234 Begin
1235 hr_utility.set_location('Entering:'||l_proc, 5);
1236 --
1237 -- Call all supporting business operations
1238 --
1239 dt_delete_validate
1240 (p_datetrack_mode => p_datetrack_mode,
1241 p_validation_start_date => p_validation_start_date,
1242 p_validation_end_date => p_validation_end_date,
1243 p_pl_regn_id => p_rec.pl_regn_id);
1244 --
1245 hr_utility.set_location(' Leaving:'||l_proc, 10);
1246 End delete_validate;
1247 -- ---------------------------------------------------------------------------
1248 -- |---------------------< return_legislation_code >-------------------------|
1249 -- ---------------------------------------------------------------------------
1250 --
1251 function return_legislation_code
1252 (p_pl_regn_id in number) return varchar2 is
1253 --
1254 -- Declare cursor
1255 --
1256 cursor csr_leg_code is
1257 select a.legislation_code
1258 from per_business_groups a,
1259 ben_pl_regn_f b
1260 where b.pl_regn_id = p_pl_regn_id
1261 and a.business_group_id = b.business_group_id;
1262 --
1263 -- Declare local variables
1264 --
1265 l_legislation_code per_business_groups.legislation_code%type; --varchar2(150);
1266 l_proc varchar2(72) := g_package||'return_legislation_code';
1267 --
1268 begin
1269 --
1270 hr_utility.set_location('Entering:'|| l_proc, 10);
1271 --
1272 -- Ensure that all the mandatory parameter are not null
1273 --
1274 hr_api.mandatory_arg_error(p_api_name => l_proc,
1275 p_argument => 'pl_regn_id',
1276 p_argument_value => p_pl_regn_id);
1277 --
1278 open csr_leg_code;
1279 --
1280 fetch csr_leg_code into l_legislation_code;
1281 --
1282 /** CWB Changes
1283 if csr_leg_code%notfound then
1284 --
1285 close csr_leg_code;
1286 --
1287 -- The primary key is invalid therefore we must error
1288 --
1289 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1290 fnd_message.raise_error;
1291 --
1292 end if;
1293 --
1294 */
1295 close csr_leg_code;
1296 --
1297 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1298 --
1299 return l_legislation_code;
1300 --
1301 end return_legislation_code;
1302 --
1303 end ben_prg_bus;