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