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