[Home] [Help]
PACKAGE BODY: APPS.BEN_VMR_BUS
Source
1 Package Body ben_vmr_bus as
2 /* $Header: bevmrrhi.pkb 120.1 2008/02/05 08:16:03 rtagarra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_vmr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_vrbl_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 -- vrbl_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_vrbl_mtchg_rt_id(p_vrbl_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_vrbl_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_vmr_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_vrbl_mtchg_rt_id => p_vrbl_mtchg_rt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_vrbl_mtchg_rt_id,hr_api.g_number)
55 <> ben_vmr_shd.g_old_rec.vrbl_mtchg_rt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_vmr_shd.constraint_error('BEN_VRBL_MTCHG_RT_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_vrbl_mtchg_rt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_vmr_shd.constraint_error('BEN_VRBL_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_vrbl_mtchg_rt_id;
78 --
79 -- |--------------------< chk_duplicate_ordr_num >----------------------------|
80 -- ----------------------------------------------------------------------------
81 --
82 -- Description
83 --
84 -- Pre Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_vrbl_rt_prfl_id
89 -- p_ordr_num
90 -- p_effective_date
91 -- p_business_group_id
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Errors handled by the procedure
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 -- ----------------------------------------------------------------------------
103 procedure chk_duplicate_ordr_num
104 (p_vrbl_rt_prfl_id in number
105 ,p_ordr_num in number
106 ,p_effective_date in date
107 ,p_business_group_id in number)
108 is
109 l_proc varchar2(72) := g_package||' chk_duplicate_ordr_num ';
110 l_dummy char(1);
111 cursor c1 is select null
112 from ben_vrbl_mtchg_rt_f
113 where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
114 --and asnt_set_rt_id <> nvl(p_asnt_set_rt_id,-1)
115 and p_effective_date between effective_start_date
116 and effective_end_date
117 and business_group_id + 0 = p_business_group_id
118 and ordr_num = p_ordr_num;
119 --
120 Begin
121 hr_utility.set_location('Entering:'||l_proc, 5);
122
123 --
124 open c1;
125 fetch c1 into l_dummy;
126 --
127 if c1%found then
128 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
129 fnd_message.raise_error;
130 end if;
131 close c1;
132 --
133 hr_utility.set_location('Leaving:'||l_proc, 15);
134 End chk_duplicate_ordr_num;
135
136 --
137 -- ----------------------------------------------------------------------------
138 -- |------< chk_no_mx_amt_of_py_num_flag >------|
139 -- ----------------------------------------------------------------------------
140 --
141 -- Description
142 -- This procedure is used to check that the lookup value is valid.
143 --
144 -- Pre Conditions
145 -- None.
146 --
147 -- In Parameters
148 -- vrbl_mtchg_rt_id PK of record being inserted or updated.
149 -- no_mx_amt_of_py_num_flag Value of lookup code.
150 -- effective_date effective date
151 -- object_version_number Object version number of record being
152 -- inserted or updated.
153 --
154 -- Post Success
155 -- Processing continues
156 --
157 -- Post Failure
158 -- Error handled by procedure
159 --
160 -- Access Status
161 -- Internal table handler use only.
162 --
163 Procedure chk_no_mx_amt_of_py_num_flag(p_vrbl_mtchg_rt_id in number,
164 p_no_mx_amt_of_py_num_flag in varchar2,
165 p_effective_date in date,
166 p_object_version_number in number) is
167 --
168 l_proc varchar2(72) := g_package||'chk_no_mx_amt_of_py_num_flag';
169 l_api_updating boolean;
170 --
171 Begin
172 --
173 hr_utility.set_location('Entering:'||l_proc, 5);
174 --
175 l_api_updating := ben_vmr_shd.api_updating
176 (p_vrbl_mtchg_rt_id => p_vrbl_mtchg_rt_id,
177 p_effective_date => p_effective_date,
178 p_object_version_number => p_object_version_number);
179 --
180 if (l_api_updating
181 and p_no_mx_amt_of_py_num_flag
182 <> nvl(ben_vmr_shd.g_old_rec.no_mx_amt_of_py_num_flag,hr_api.g_varchar2)
183 or not l_api_updating) then
184 --
185 -- check if value of lookup falls within lookup type.
186 --
187 --
188 if hr_api.not_exists_in_hr_lookups
189 (p_lookup_type => 'YES_NO',
190 p_lookup_code => p_no_mx_amt_of_py_num_flag,
191 p_effective_date => p_effective_date) then
192 --
193 -- raise error as does not exist as lookup
194 --
195 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
196 hr_utility.raise_error;
197 --
198 end if;
199 --
200 end if;
201 --
202 hr_utility.set_location('Leaving:'||l_proc,10);
203 --
204 end chk_no_mx_amt_of_py_num_flag;
205 --
206 -- ----------------------------------------------------------------------------
207 -- |------< chk_no_mx_pct_of_py_num_flag >------|
208 -- ----------------------------------------------------------------------------
209 --
210 -- Description
211 -- This procedure is used to check that the lookup value is valid.
212 --
213 -- Pre Conditions
214 -- None.
215 --
216 -- In Parameters
217 -- vrbl_mtchg_rt_id PK of record being inserted or updated.
218 -- no_mx_pct_of_py_num_flag Value of lookup code.
219 -- effective_date effective date
220 -- object_version_number Object version number of record being
221 -- inserted or updated.
222 --
223 -- Post Success
224 -- Processing continues
225 --
226 -- Post Failure
227 -- Error handled by procedure
228 --
229 -- Access Status
230 -- Internal table handler use only.
231 --
232 Procedure chk_no_mx_pct_of_py_num_flag(p_vrbl_mtchg_rt_id in number,
233 p_no_mx_pct_of_py_num_flag in varchar2,
234 p_effective_date in date,
235 p_object_version_number in number) is
236 --
237 l_proc varchar2(72) := g_package||'chk_no_mx_pct_of_py_num_flag';
238 l_api_updating boolean;
239 --
240 Begin
241 --
242 hr_utility.set_location('Entering:'||l_proc, 5);
243 --
244 l_api_updating := ben_vmr_shd.api_updating
245 (p_vrbl_mtchg_rt_id => p_vrbl_mtchg_rt_id,
246 p_effective_date => p_effective_date,
247 p_object_version_number => p_object_version_number);
248 --
249 if (l_api_updating
250 and p_no_mx_pct_of_py_num_flag
251 <> nvl(ben_vmr_shd.g_old_rec.no_mx_pct_of_py_num_flag,hr_api.g_varchar2)
252 or not l_api_updating) then
253 --
254 -- check if value of lookup falls within lookup type.
255 --
256 --
257 if hr_api.not_exists_in_hr_lookups
258 (p_lookup_type => 'YES_NO',
259 p_lookup_code => p_no_mx_pct_of_py_num_flag,
260 p_effective_date => p_effective_date) then
261 --
262 -- raise error as does not exist as lookup
263 --
264 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
265 hr_utility.raise_error;
266 --
267 end if;
268 --
269 end if;
270 --
271 hr_utility.set_location('Leaving:'||l_proc,10);
272 --
273 end chk_no_mx_pct_of_py_num_flag;
274 --
275 -- ----------------------------------------------------------------------------
276 -- |------< chk_cntnu_mtch_aftr_max_rl_fg >------|
277 -- ----------------------------------------------------------------------------
278 --
279 -- Description
280 -- This procedure is used to check that the lookup value is valid.
281 --
282 -- Pre Conditions
283 -- None.
284 --
285 -- In Parameters
286 -- vrbl_mtchg_rt_id PK of record being inserted or updated.
287 -- cntnu_mtch_aftr_max_rl_flag Value of lookup code.
288 -- effective_date effective date
289 -- object_version_number Object version number of record being
290 -- inserted or updated.
291 --
292 -- Post Success
293 -- Processing continues
294 --
295 -- Post Failure
296 -- Error handled by procedure
297 --
298 -- Access Status
299 -- Internal table handler use only.
300 --
301 Procedure chk_cntnu_mtch_aftr_max_rl_fg(p_vrbl_mtchg_rt_id in number,
302 p_cntnu_mtch_aftr_max_rl_flag in varchar2,
303 p_effective_date in date,
304 p_object_version_number in number) is
305 --
306 l_proc varchar2(72) := g_package||'chk_cntnu_mtch_aftr_max_rl_fg';
307 l_api_updating boolean;
308 --
309 Begin
310 --
311 hr_utility.set_location('Entering:'||l_proc, 5);
312 --
313 l_api_updating := ben_vmr_shd.api_updating
314 (p_vrbl_mtchg_rt_id => p_vrbl_mtchg_rt_id,
315 p_effective_date => p_effective_date,
316 p_object_version_number => p_object_version_number);
317 --
318 if (l_api_updating
319 and p_cntnu_mtch_aftr_max_rl_flag
320 <> nvl(ben_vmr_shd.g_old_rec.cntnu_mtch_aftr_max_rl_flag,hr_api.g_varchar2)
321 or not l_api_updating) then
322 --
323 -- check if value of lookup falls within lookup type.
324 --
325 --
326 if hr_api.not_exists_in_hr_lookups
327 (p_lookup_type => 'YES_NO',
328 p_lookup_code => p_cntnu_mtch_aftr_max_rl_flag,
329 p_effective_date => p_effective_date) then
330 --
331 -- raise error as does not exist as lookup
332 --
333 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
334 hr_utility.raise_error;
335 --
336 end if;
337 --
338 end if;
339 --
340 hr_utility.set_location('Leaving:'||l_proc,10);
341 --
342 end chk_cntnu_mtch_aftr_max_rl_fg;
343 --
344 -- ----------------------------------------------------------------------------
345 -- |------< chk_mtchg_rt_calc_rl >------|
346 -- ----------------------------------------------------------------------------
347 --
348 -- Description
349 -- This procedure is used to check that the Formula Rule is valid.
350 --
351 -- Pre Conditions
352 -- None.
353 --
354 -- In Parameters
355 -- vrbl_mtchg_rt_id PK of record being inserted or updated.
356 -- mtchg_rt_calc_rl Value of formula rule id.
357 -- effective_date effective date
358 -- object_version_number Object version number of record being
359 -- inserted or updated.
360 --
361 -- Post Success
362 -- Processing continues
363 --
364 -- Post Failure
365 -- Error handled by procedure
366 --
367 -- Access Status
368 -- Internal table handler use only.
369 --
370 Procedure chk_mtchg_rt_calc_rl(p_vrbl_mtchg_rt_id in number,
371 p_mtchg_rt_calc_rl in number,
372 p_business_group_id in number,
373 p_effective_date in date,
374 p_object_version_number in number) is
375 --
376 l_proc varchar2(72) := g_package||'chk_mtchg_rt_calc_rl';
377 l_api_updating boolean;
378 l_dummy varchar2(1);
379 --
380 cursor c1 is
381 select null
382 from ff_formulas_f ff
383 ,per_business_groups pbg
384 where ff.formula_id = p_mtchg_rt_calc_rl
385 and ff.formula_type_id = -160
386 and pbg.business_group_id = p_business_group_id
387 and nvl(ff.business_group_id, p_business_group_id) =
388 p_business_group_id
389 and nvl(ff.legislation_code, pbg.legislation_code) =
390 pbg.legislation_code
391 and p_effective_date
392 between ff.effective_start_date
393 and ff.effective_end_date;
394 --
395 Begin
396 --
397 hr_utility.set_location('Entering:'||l_proc, 5);
398 --
399 l_api_updating := ben_vmr_shd.api_updating
400 (p_vrbl_mtchg_rt_id => p_vrbl_mtchg_rt_id,
401 p_effective_date => p_effective_date,
402 p_object_version_number => p_object_version_number);
403 --
404 if (l_api_updating
405 and nvl(p_mtchg_rt_calc_rl,hr_api.g_number)
406 <> ben_vmr_shd.g_old_rec.mtchg_rt_calc_rl
407 or not l_api_updating)
408 and p_mtchg_rt_calc_rl is not null then
409 --
410 -- check if value of formula rule is valid.
411 --
412 open c1;
413 --
414 -- fetch value from cursor if it returns a record then the
415 -- formula is valid otherwise its invalid
416 --
417 fetch c1 into l_dummy;
418 if c1%notfound then
419 --
420 close c1;
421 --
422 -- raise error
423 --
424 --
425 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
426 fnd_message.set_token('ID',p_mtchg_rt_calc_rl);
427 fnd_message.set_token('TYPE_ID',-160);
428 fnd_message.raise_error;
429 --
430 end if;
431 --
432 close c1;
433 --
434 end if;
435 --
436 hr_utility.set_location('Leaving:'||l_proc,10);
437 --
438 end chk_mtchg_rt_calc_rl;
439 --
440 -- ----------------------------------------------------------------------------
441 -- |------< chk_no_mx_mtch_amt_flag >------|
442 -- ----------------------------------------------------------------------------
443 --
444 -- Description
445 -- This procedure is used to check that the lookup value is valid.
446 --
447 -- Pre Conditions
448 -- None.
449 --
450 -- In Parameters
451 -- vrbl_mtchg_rt_id PK of record being inserted or updated.
452 -- no_mx_mtch_amt_flag Value of lookup code.
453 -- effective_date effective date
454 -- object_version_number Object version number of record being
455 -- inserted or updated.
456 --
457 -- Post Success
458 -- Processing continues
459 --
460 -- Post Failure
461 -- Error handled by procedure
462 --
463 -- Access Status
464 -- Internal table handler use only.
465 --
466 Procedure chk_no_mx_mtch_amt_flag(p_vrbl_mtchg_rt_id in number,
467 p_no_mx_mtch_amt_flag in varchar2,
468 p_effective_date in date,
469 p_object_version_number in number) is
470 --
471 l_proc varchar2(72) := g_package||'chk_no_mx_mtch_amt_flag';
472 l_api_updating boolean;
473 --
474 Begin
475 --
476 hr_utility.set_location('Entering:'||l_proc, 5);
477 --
478 l_api_updating := ben_vmr_shd.api_updating
479 (p_vrbl_mtchg_rt_id => p_vrbl_mtchg_rt_id,
480 p_effective_date => p_effective_date,
481 p_object_version_number => p_object_version_number);
482 --
483 if (l_api_updating
484 and p_no_mx_mtch_amt_flag
485 <> nvl(ben_vmr_shd.g_old_rec.no_mx_mtch_amt_flag,hr_api.g_varchar2)
486 or not l_api_updating) then
487 --
488 -- check if value of lookup falls within lookup type.
489 --
490 --
491 if hr_api.not_exists_in_hr_lookups
492 (p_lookup_type => 'YES_NO',
493 p_lookup_code => p_no_mx_mtch_amt_flag,
494 p_effective_date => p_effective_date) then
495 --
496 -- raise error as does not exist as lookup
497 --
498 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
499 hr_utility.raise_error;
500 --
501 end if;
502 --
503 end if;
504 --
505 hr_utility.set_location('Leaving:'||l_proc,10);
506 --
507 end chk_no_mx_mtch_amt_flag;
508 --
509 -- ----------------------------------------------------------------------------
510 -- |--------------------------< dt_update_validate >--------------------------|
511 -- ----------------------------------------------------------------------------
512 -- {Start Of Comments}
513 --
514 -- Description:
515 -- This procedure is used for referential integrity of datetracked
516 -- parent entities when a datetrack update operation is taking place
517 -- and where there is no cascading of update defined for this entity.
518 --
519 -- Prerequisites:
520 -- This procedure is called from the update_validate.
521 --
522 -- In Parameters:
523 --
524 -- Post Success:
525 -- Processing continues.
526 --
527 -- Post Failure:
528 --
529 -- Developer Implementation Notes:
530 -- This procedure should not need maintenance unless the HR Schema model
531 -- changes.
532 --
533 -- Access Status:
534 -- Internal Row Handler Use Only.
535 --
536 -- {End Of Comments}
537 -- ----------------------------------------------------------------------------
538 Procedure dt_update_validate
539 (p_vrbl_rt_prfl_id in number default hr_api.g_number,
540 p_datetrack_mode in varchar2,
541 p_validation_start_date in date,
542 p_validation_end_date in date) Is
543 --
544 l_proc varchar2(72) := g_package||'dt_update_validate';
545 l_integrity_error Exception;
546 l_table_name all_tables.table_name%TYPE;
547 --
548 Begin
549 hr_utility.set_location('Entering:'||l_proc, 5);
550 --
551 -- Ensure that the p_datetrack_mode argument is not null
552 --
553 hr_api.mandatory_arg_error
554 (p_api_name => l_proc,
555 p_argument => 'datetrack_mode',
556 p_argument_value => p_datetrack_mode);
557 --
558 -- Only perform the validation if the datetrack update mode is valid
559 --
560 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
561 --
562 --
563 -- Ensure the arguments are not null
564 --
565 hr_api.mandatory_arg_error
566 (p_api_name => l_proc,
567 p_argument => 'validation_start_date',
568 p_argument_value => p_validation_start_date);
569 --
570 hr_api.mandatory_arg_error
571 (p_api_name => l_proc,
572 p_argument => 'validation_end_date',
573 p_argument_value => p_validation_end_date);
574 --
575 If ((nvl(p_vrbl_rt_prfl_id, hr_api.g_number) <> hr_api.g_number) and
576 NOT (dt_api.check_min_max_dates
577 (p_base_table_name => 'ben_vrbl_rt_prfl_f',
578 p_base_key_column => 'vrbl_rt_prfl_id',
579 p_base_key_value => p_vrbl_rt_prfl_id,
580 p_from_date => p_validation_start_date,
581 p_to_date => p_validation_end_date))) Then
582 l_table_name := 'ben_vrbl_rt_prfl_f';
583 Raise l_integrity_error;
584 End If;
585 --
586 End If;
587 --
588 hr_utility.set_location(' Leaving:'||l_proc, 10);
589 Exception
590 When l_integrity_error Then
591 --
592 -- A referential integrity check was violated therefore
593 -- we must error
594 --
595 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
596 hr_utility.set_message_token('TABLE_NAME', l_table_name);
597 hr_utility.raise_error;
598 When Others Then
599 --
600 -- An unhandled or unexpected error has occurred which
601 -- we must report
602 --
603 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
604 hr_utility.set_message_token('PROCEDURE', l_proc);
605 hr_utility.set_message_token('STEP','15');
606 hr_utility.raise_error;
607 End dt_update_validate;
608 --
609 -- ----------------------------------------------------------------------------
610 -- |--------------------------< dt_delete_validate >--------------------------|
611 -- ----------------------------------------------------------------------------
612 -- {Start Of Comments}
613 --
614 -- Description:
615 -- This procedure is used for referential integrity of datetracked
616 -- child entities when either a datetrack DELETE or ZAP is in operation
617 -- and where there is no cascading of delete defined for this entity.
618 -- For the datetrack mode of DELETE or ZAP we must ensure that no
619 -- datetracked child rows exist between the validation start and end
620 -- dates.
621 --
622 -- Prerequisites:
623 -- This procedure is called from the delete_validate.
624 --
625 -- In Parameters:
626 --
627 -- Post Success:
628 -- Processing continues.
629 --
630 -- Post Failure:
631 -- If a row exists by determining the returning Boolean value from the
632 -- generic dt_api.rows_exist function then we must supply an error via
633 -- the use of the local exception handler l_rows_exist.
634 --
635 -- Developer Implementation Notes:
636 -- This procedure should not need maintenance unless the HR Schema model
637 -- changes.
638 --
639 -- Access Status:
640 -- Internal Row Handler Use Only.
641 --
642 -- {End Of Comments}
643 -- ----------------------------------------------------------------------------
644 Procedure dt_delete_validate
645 (p_vrbl_mtchg_rt_id in number,
646 p_datetrack_mode in varchar2,
647 p_validation_start_date in date,
648 p_validation_end_date in date) Is
649 --
650 l_proc varchar2(72) := g_package||'dt_delete_validate';
651 l_rows_exist Exception;
652 l_table_name all_tables.table_name%TYPE;
653 --
654 Begin
655 hr_utility.set_location('Entering:'||l_proc, 5);
656 --
657 -- Ensure that the p_datetrack_mode argument is not null
658 --
659 hr_api.mandatory_arg_error
660 (p_api_name => l_proc,
661 p_argument => 'datetrack_mode',
662 p_argument_value => p_datetrack_mode);
663 --
664 -- Only perform the validation if the datetrack mode is either
665 -- DELETE or ZAP
666 --
667 If (p_datetrack_mode = 'DELETE' or
668 p_datetrack_mode = 'ZAP') then
669 --
670 --
671 -- Ensure the arguments are not null
672 --
673 hr_api.mandatory_arg_error
674 (p_api_name => l_proc,
675 p_argument => 'validation_start_date',
676 p_argument_value => p_validation_start_date);
677 --
678 hr_api.mandatory_arg_error
679 (p_api_name => l_proc,
680 p_argument => 'validation_end_date',
681 p_argument_value => p_validation_end_date);
682 --
683 hr_api.mandatory_arg_error
684 (p_api_name => l_proc,
685 p_argument => 'vrbl_mtchg_rt_id',
686 p_argument_value => p_vrbl_mtchg_rt_id);
687 --
688 --
689 --
690 End If;
691 --
692 hr_utility.set_location(' Leaving:'||l_proc, 10);
693 Exception
694 When l_rows_exist Then
695 --
696 -- A referential integrity check was violated therefore
697 -- we must error
698 --
699 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
700 hr_utility.set_message_token('TABLE_NAME', l_table_name);
701 hr_utility.raise_error;
702 When Others Then
703 --
704 -- An unhandled or unexpected error has occurred which
705 -- we must report
706 --
707 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
708 hr_utility.set_message_token('PROCEDURE', l_proc);
709 hr_utility.set_message_token('STEP','15');
710 hr_utility.raise_error;
711 End dt_delete_validate;
712 --
713 -- ----------------------------------------------------------------------------
714 -- |---------------------------< insert_validate >----------------------------|
715 -- ----------------------------------------------------------------------------
716 Procedure insert_validate
717 (p_rec in ben_vmr_shd.g_rec_type,
718 p_effective_date in date,
719 p_datetrack_mode in varchar2,
720 p_validation_start_date in date,
721 p_validation_end_date in date) is
722 --
723 l_proc varchar2(72) := g_package||'insert_validate';
724 --
725 Begin
726 hr_utility.set_location('Entering:'||l_proc, 5);
727 --
728 -- Call all supporting business operations
729 --
730 --
731 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
732 --
733 chk_vrbl_mtchg_rt_id
734 (p_vrbl_mtchg_rt_id => p_rec.vrbl_mtchg_rt_id,
735 p_effective_date => p_effective_date,
736 p_object_version_number => p_rec.object_version_number);
737 --
738 chk_no_mx_amt_of_py_num_flag
739 (p_vrbl_mtchg_rt_id => p_rec.vrbl_mtchg_rt_id,
740 p_no_mx_amt_of_py_num_flag => p_rec.no_mx_amt_of_py_num_flag,
741 p_effective_date => p_effective_date,
742 p_object_version_number => p_rec.object_version_number);
743 --
744 chk_no_mx_pct_of_py_num_flag
745 (p_vrbl_mtchg_rt_id => p_rec.vrbl_mtchg_rt_id,
746 p_no_mx_pct_of_py_num_flag => p_rec.no_mx_pct_of_py_num_flag,
747 p_effective_date => p_effective_date,
748 p_object_version_number => p_rec.object_version_number);
749 --
750 chk_cntnu_mtch_aftr_max_rl_fg
751 (p_vrbl_mtchg_rt_id => p_rec.vrbl_mtchg_rt_id,
752 p_cntnu_mtch_aftr_max_rl_flag => p_rec.cntnu_mtch_aftr_max_rl_flag,
753 p_effective_date => p_effective_date,
754 p_object_version_number => p_rec.object_version_number);
755 --
756 chk_mtchg_rt_calc_rl
757 (p_vrbl_mtchg_rt_id => p_rec.vrbl_mtchg_rt_id,
758 p_mtchg_rt_calc_rl => p_rec.mtchg_rt_calc_rl,
759 p_business_group_id => p_rec.business_group_id,
760 p_effective_date => p_effective_date,
761 p_object_version_number => p_rec.object_version_number);
762 --
763 chk_no_mx_mtch_amt_flag
764 (p_vrbl_mtchg_rt_id => p_rec.vrbl_mtchg_rt_id,
765 p_no_mx_mtch_amt_flag => p_rec.no_mx_mtch_amt_flag,
766 p_effective_date => p_effective_date,
767 p_object_version_number => p_rec.object_version_number);
768 --
769 chk_duplicate_ordr_num
770 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
771 ,p_ordr_num => p_rec.ordr_num
772 ,p_effective_date => p_effective_date
773 ,p_business_group_id => p_rec.business_group_id);
774 --
775 hr_utility.set_location(' Leaving:'||l_proc, 10);
776 End insert_validate;
777 --
778 -- ----------------------------------------------------------------------------
779 -- |---------------------------< update_validate >----------------------------|
780 -- ----------------------------------------------------------------------------
781 Procedure update_validate
782 (p_rec in ben_vmr_shd.g_rec_type,
783 p_effective_date in date,
784 p_datetrack_mode in varchar2,
785 p_validation_start_date in date,
786 p_validation_end_date in date) is
787 --
788 l_proc varchar2(72) := g_package||'update_validate';
789 --
790 Begin
791 hr_utility.set_location('Entering:'||l_proc, 5);
792 --
793 -- Call all supporting business operations
794 --
795 --
796 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
797 --
798 chk_vrbl_mtchg_rt_id
799 (p_vrbl_mtchg_rt_id => p_rec.vrbl_mtchg_rt_id,
800 p_effective_date => p_effective_date,
801 p_object_version_number => p_rec.object_version_number);
802 --
803 chk_no_mx_amt_of_py_num_flag
804 (p_vrbl_mtchg_rt_id => p_rec.vrbl_mtchg_rt_id,
805 p_no_mx_amt_of_py_num_flag => p_rec.no_mx_amt_of_py_num_flag,
806 p_effective_date => p_effective_date,
807 p_object_version_number => p_rec.object_version_number);
808 --
809 chk_no_mx_pct_of_py_num_flag
810 (p_vrbl_mtchg_rt_id => p_rec.vrbl_mtchg_rt_id,
811 p_no_mx_pct_of_py_num_flag => p_rec.no_mx_pct_of_py_num_flag,
812 p_effective_date => p_effective_date,
813 p_object_version_number => p_rec.object_version_number);
814 --
815 chk_cntnu_mtch_aftr_max_rl_fg
816 (p_vrbl_mtchg_rt_id => p_rec.vrbl_mtchg_rt_id,
817 p_cntnu_mtch_aftr_max_rl_flag => p_rec.cntnu_mtch_aftr_max_rl_flag,
818 p_effective_date => p_effective_date,
819 p_object_version_number => p_rec.object_version_number);
820 --
821 chk_mtchg_rt_calc_rl
822 (p_vrbl_mtchg_rt_id => p_rec.vrbl_mtchg_rt_id,
823 p_mtchg_rt_calc_rl => p_rec.mtchg_rt_calc_rl,
824 p_business_group_id => p_rec.business_group_id,
825 p_effective_date => p_effective_date,
826 p_object_version_number => p_rec.object_version_number);
827 --
828 chk_no_mx_mtch_amt_flag
829 (p_vrbl_mtchg_rt_id => p_rec.vrbl_mtchg_rt_id,
830 p_no_mx_mtch_amt_flag => p_rec.no_mx_mtch_amt_flag,
831 p_effective_date => p_effective_date,
832 p_object_version_number => p_rec.object_version_number);
833 --
834 -- Call the datetrack update integrity operation
835 --
836 dt_update_validate
837 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
838 p_datetrack_mode => p_datetrack_mode,
839 p_validation_start_date => p_validation_start_date,
840 p_validation_end_date => p_validation_end_date);
841 --
842 chk_duplicate_ordr_num
843 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
844 ,p_ordr_num => p_rec.ordr_num
845 ,p_effective_date => p_effective_date
846 ,p_business_group_id => p_rec.business_group_id);
847 --
848 hr_utility.set_location(' Leaving:'||l_proc, 10);
849 End update_validate;
850 --
851 -- ----------------------------------------------------------------------------
852 -- |---------------------------< delete_validate >----------------------------|
853 -- ----------------------------------------------------------------------------
854 Procedure delete_validate
855 (p_rec in ben_vmr_shd.g_rec_type,
856 p_effective_date in date,
857 p_datetrack_mode in varchar2,
858 p_validation_start_date in date,
859 p_validation_end_date in date) is
860 --
861 l_proc varchar2(72) := g_package||'delete_validate';
862 --
863 Begin
864 hr_utility.set_location('Entering:'||l_proc, 5);
865 --
866 -- Call all supporting business operations
867 --
868 dt_delete_validate
869 (p_datetrack_mode => p_datetrack_mode,
870 p_validation_start_date => p_validation_start_date,
871 p_validation_end_date => p_validation_end_date,
872 p_vrbl_mtchg_rt_id => p_rec.vrbl_mtchg_rt_id);
873 --
874 hr_utility.set_location(' Leaving:'||l_proc, 10);
875 End delete_validate;
876 --
877 --
878 -- ---------------------------------------------------------------------------
879 -- |---------------------< return_legislation_code >-------------------------|
880 -- ---------------------------------------------------------------------------
881 --
882 function return_legislation_code
883 (p_vrbl_mtchg_rt_id in number) return varchar2 is
884 --
885 -- Declare cursor
886 --
887 cursor csr_leg_code is
888 select a.legislation_code
889 from per_business_groups a,
890 ben_vrbl_mtchg_rt_f b
891 where b.vrbl_mtchg_rt_id = p_vrbl_mtchg_rt_id
892 and a.business_group_id = b.business_group_id;
893 --
894 -- Declare local variables
895 --
896 l_legislation_code varchar2(150);
897 l_proc varchar2(72) := g_package||'return_legislation_code';
898 --
899 begin
900 --
901 hr_utility.set_location('Entering:'|| l_proc, 10);
902 --
903 -- Ensure that all the mandatory parameter are not null
904 --
905 hr_api.mandatory_arg_error(p_api_name => l_proc,
906 p_argument => 'vrbl_mtchg_rt_id',
907 p_argument_value => p_vrbl_mtchg_rt_id);
908 --
909 open csr_leg_code;
910 --
911 fetch csr_leg_code into l_legislation_code;
912 --
913 if csr_leg_code%notfound then
914 --
915 close csr_leg_code;
916 --
917 -- The primary key is invalid therefore we must error
918 --
919 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
920 hr_utility.raise_error;
921 --
922 end if;
923 --
924 close csr_leg_code;
925 --
926 hr_utility.set_location(' Leaving:'|| l_proc, 20);
927 --
928 return l_legislation_code;
929 --
930 end return_legislation_code;
931 --
932 end ben_vmr_bus;