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