[Home] [Help]
PACKAGE BODY: APPS.BEN_TTP_BUS
Source
1 Package Body ben_ttp_bus as
2 /* $Header: bettprhi.pkb 120.0.12010000.2 2008/08/05 15:32:38 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ttp_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_ttl_prtt_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 -- ttl_prtt_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_ttl_prtt_rt_id(p_ttl_prtt_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_ttl_prtt_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_ttp_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_ttl_prtt_rt_id => p_ttl_prtt_rt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_ttl_prtt_rt_id,hr_api.g_number)
55 <> ben_ttp_shd.g_old_rec.ttl_prtt_rt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_ttp_shd.constraint_error('BEN_TTL_PRTT_RT_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_ttl_prtt_rt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_ttp_shd.constraint_error('BEN_TTL_PRTT_RT_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_ttl_prtt_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_ttl_prtt_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, 10);
134 End chk_duplicate_ordr_num;
135 --
136 -- Added For bug 2529689
137 --
138 --
139 -- |--------------------< chk_ttlprtt_mlt_cd >----------------------------|
140 -- ----------------------------------------------------------------------------
141 --
142 -- Description
143 -- If the Usage selected is Actual Premiums, the Calculation type should be
144 -- Multiple of total participants
145 --
146 -- Pre Conditions
147 -- None.
148 --
149 -- In Parameters
150 -- p_vrbl_rt_prfl_id
151 -- p_effective_date
152 -- p_business_group_id
153 --
154 -- Post Success
155 -- Processing continues
156 --
157 -- Post Failure
158 -- Errors handled by the procedure
159 --
160 -- Access Status
161 -- Internal table handler use only.
162 --
163 -- ----------------------------------------------------------------------------
164 procedure chk_ttlprtt_mlt_cd
165 (p_vrbl_rt_prfl_id in number
166 ,p_effective_date in date
167 ,p_business_group_id in number)
168 is
169 l_proc varchar2(72) := g_package||' chk_ttlprtt_mlt_cd ';
170
171
172 cursor c1 is select vrbl_usg_cd, mlt_cd
173 from ben_vrbl_rt_prfl_f
174 where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
175 and p_effective_date between effective_start_date
176 and effective_end_date
177 and business_group_id = p_business_group_id;
178
179 l_c1 c1%ROWTYPE;
180 --
181 Begin
182 hr_utility.set_location('Entering:'||l_proc, 5);
183
184 --
185 open c1;
186 fetch c1 into l_c1;
187 --
188 hr_utility.set_location('vrbl rt prfl id is :'||p_vrbl_rt_prfl_id, 69);
189 hr_utility.set_location('vrbl_usg_cd is :'||l_c1.vrbl_usg_cd, 69);
190 hr_utility.set_location('mlt_cd id is :'||l_c1.mlt_cd, 69);
191
192 IF l_c1.vrbl_usg_cd = 'ACP' THEN
193 IF l_c1.mlt_cd <> 'TTLPRTT' THEN
194 close c1;
195 --
196 -- raise an error as mlt_cd can only be TTLPRTT .
197 --
198 --
199 fnd_message.set_name('BEN','BEN_92263_TTLPRTT_MLTCD');
200 fnd_message.raise_error;
201 END IF;
202 END IF;
203
204 close c1;
205 --
206 hr_utility.set_location('Leaving:'||l_proc, 10);
207 End chk_ttlprtt_mlt_cd;
208
209 --
210
211
212 --
213 -- ----------------------------------------------------------------------------
214 -- |------< chk_prtt_det_rl >------|
215 -- ----------------------------------------------------------------------------
216 --
217 -- Description
218 -- This procedure is used to check that the Formula Rule is valid.
219 --
220 -- Pre Conditions
221 -- None.
222 --
223 -- In Parameters
224 -- ttl_prtt_rt_id PK of record being inserted or updated.
225 -- prtt_det_rl Value of formula rule id.
226 -- effective_date effective date
227 -- object_version_number Object version number of record being
228 -- inserted or updated.
229 --
230 -- Post Success
231 -- Processing continues
232 --
233 -- Post Failure
234 -- Error handled by procedure
235 --
236 -- Access Status
237 -- Internal table handler use only.
238 --
239 Procedure chk_prtt_det_rl(p_ttl_prtt_rt_id in number,
240 p_prtt_det_rl in number,
241 p_effective_date in date,
242 p_object_version_number in number) is
243 --
244 l_proc varchar2(72) := g_package||'chk_prtt_det_rl';
245 l_api_updating boolean;
246 l_dummy varchar2(1);
247 --
248 cursor c1 is
249 select null
250 from ff_formulas_f ff
251 where ff.formula_id = p_prtt_det_rl
252 and p_effective_date
253 between ff.effective_start_date
254 and ff.effective_end_date;
255 --
256 Begin
257 --
258 hr_utility.set_location('Entering:'||l_proc, 5);
259 --
260 l_api_updating := ben_ttp_shd.api_updating
261 (p_ttl_prtt_rt_id => p_ttl_prtt_rt_id,
262 p_effective_date => p_effective_date,
263 p_object_version_number => p_object_version_number);
264 --
265 if (l_api_updating
266 and nvl(p_prtt_det_rl,hr_api.g_number)
267 <> ben_ttp_shd.g_old_rec.prtt_det_rl
268 or not l_api_updating)
269 and p_prtt_det_rl is not null then
270 --
271 -- check if value of formula rule is valid.
272 --
273 open c1;
274 --
275 -- fetch value from cursor if it returns a record then the
276 -- formula is valid otherwise its invalid
277 --
278 fetch c1 into l_dummy;
279 if c1%notfound then
280 --
281 close c1;
282 --
283 -- raise error
284 --
285 fnd_message.set_name('PAY','FORMULA_DOES_NOT_EXIST');
286 fnd_message.raise_error;
287 --
288 end if;
289 --
290 close c1;
291 --
292 end if;
293 --
294 hr_utility.set_location('Leaving:'||l_proc,10);
295 --
296 end chk_prtt_det_rl;
297 --
298 -- ----------------------------------------------------------------------------
299 -- |------< chk_prtt_det_cd >------|
300 -- ----------------------------------------------------------------------------
301 --
302 -- Description
303 -- This procedure is used to check that the lookup value is valid.
304 --
305 -- Pre Conditions
306 -- None.
307 --
308 -- In Parameters
309 -- ttl_prtt_rt_id PK of record being inserted or updated.
310 -- prtt_det_cd Value of lookup code.
311 -- effective_date effective date
312 -- object_version_number Object version number of record being
313 -- inserted or updated.
314 --
315 -- Post Success
316 -- Processing continues
317 --
318 -- Post Failure
319 -- Error handled by procedure
320 --
321 -- Access Status
322 -- Internal table handler use only.
323 --
324 Procedure chk_prtt_det_cd(p_ttl_prtt_rt_id in number,
325 p_prtt_det_cd in varchar2,
326 p_effective_date in date,
327 p_object_version_number in number) is
328 --
329 l_proc varchar2(72) := g_package||'chk_prtt_det_cd';
330 l_api_updating boolean;
331 --
332 Begin
333 --
334 hr_utility.set_location('Entering:'||l_proc, 5);
335 --
336 l_api_updating := ben_ttp_shd.api_updating
337 (p_ttl_prtt_rt_id => p_ttl_prtt_rt_id,
338 p_effective_date => p_effective_date,
339 p_object_version_number => p_object_version_number);
340 --
341 if (l_api_updating
342 and p_prtt_det_cd
343 <> nvl(ben_ttp_shd.g_old_rec.prtt_det_cd,hr_api.g_varchar2)
344 or not l_api_updating)
345 and p_prtt_det_cd is not null then
346 --
347 -- check if value of lookup falls within lookup type.
348 --
349 if hr_api.not_exists_in_hr_lookups
350 (p_lookup_type => 'BEN_PRTT_DET',
351 p_lookup_code => p_prtt_det_cd,
352 p_effective_date => p_effective_date) then
353 --
354 -- raise error as does not exist as lookup
355 --
356 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
357 fnd_message.set_token('FIELD','p_prtt_det_cd');
358 fnd_message.set_token('TYPE','BEN_PRTT_DET');
359 fnd_message.raise_error;
360 --
361 end if;
362 --
363 end if;
364 --
365 hr_utility.set_location('Leaving:'||l_proc,10);
366 --
367 end chk_prtt_det_cd;
368 --
369 -- ----------------------------------------------------------------------------
370 -- |------< chk_no_mx_prtt_num_apls_flag >------|
371 -- ----------------------------------------------------------------------------
372 --
373 -- Description
374 -- This procedure is used to check that the lookup value is valid.
375 --
376 -- Pre Conditions
377 -- None.
378 --
379 -- In Parameters
380 -- ttl_prtt_rt_id PK of record being inserted or updated.
381 -- no_mx_prtt_num_apls_flag Value of lookup code.
382 -- effective_date effective date
383 -- object_version_number Object version number of record being
384 -- inserted or updated.
385 --
386 -- Post Success
387 -- Processing continues
388 --
389 -- Post Failure
390 -- Error handled by procedure
391 --
392 -- Access Status
393 -- Internal table handler use only.
394 --
395 Procedure chk_no_mx_prtt_num_apls_flag(p_ttl_prtt_rt_id in number,
396 p_no_mx_prtt_num_apls_flag in varchar2,
397 p_effective_date in date,
398 p_object_version_number in number) is
399 --
400 l_proc varchar2(72) := g_package||'chk_no_mx_prtt_num_apls_flag';
401 l_api_updating boolean;
402 --
403 Begin
404 --
405 hr_utility.set_location('Entering:'||l_proc, 5);
406 --
407 l_api_updating := ben_ttp_shd.api_updating
408 (p_ttl_prtt_rt_id => p_ttl_prtt_rt_id,
409 p_effective_date => p_effective_date,
410 p_object_version_number => p_object_version_number);
411 --
412 if (l_api_updating
413 and p_no_mx_prtt_num_apls_flag
414 <> nvl(ben_ttp_shd.g_old_rec.no_mx_prtt_num_apls_flag,hr_api.g_varchar2)
415 or not l_api_updating) then
416 --
417 -- check if value of lookup falls within lookup type.
418 --
419 --
420 if hr_api.not_exists_in_hr_lookups
421 (p_lookup_type => 'YES_NO',
422 p_lookup_code => p_no_mx_prtt_num_apls_flag,
423 p_effective_date => p_effective_date) then
424 --
425 -- raise error as does not exist as lookup
426 --
427 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
428 fnd_message.set_token('FIELD','p_no_mx_prtt_num_apls_flag');
429 fnd_message.set_token('TYPE','YES_NO');
430 fnd_message.raise_error;
431 --
432 end if;
433 --
434 end if;
435 --
436 hr_utility.set_location('Leaving:'||l_proc,10);
437 --
438 end chk_no_mx_prtt_num_apls_flag;
439 --
440 -- ----------------------------------------------------------------------------
441 -- |------< chk_no_mn_prtt_num_apls_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 -- ttl_prtt_rt_id PK of record being inserted or updated.
452 -- no_mn_prtt_num_apls_flag Value of lookup code.
456 --
453 -- effective_date effective date
454 -- object_version_number Object version number of record being
455 -- inserted or updated.
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_mn_prtt_num_apls_flag(p_ttl_prtt_rt_id in number,
467 p_no_mn_prtt_num_apls_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_mn_prtt_num_apls_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_ttp_shd.api_updating
479 (p_ttl_prtt_rt_id => p_ttl_prtt_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_mn_prtt_num_apls_flag
485 <> nvl(ben_ttp_shd.g_old_rec.no_mn_prtt_num_apls_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_mn_prtt_num_apls_flag,
494 p_effective_date => p_effective_date) then
495 --
496 -- raise error as does not exist as lookup
497 --
498 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
499 fnd_message.set_token('FIELD','p_no_mn_prtt_num_apls_flag');
500 fnd_message.set_token('TYPE','YES_NO');
501 fnd_message.raise_error;
502 --
503 end if;
504 --
505 end if;
506 --
507 hr_utility.set_location('Leaving:'||l_proc,10);
508 --
509 end chk_no_mn_prtt_num_apls_flag;
510 --
511 -- ----------------------------------------------------------------------------
512 -- |------< chk_excld_flag >------|
513 -- ----------------------------------------------------------------------------
514 --
515 -- Description
516 -- This procedure is used to check that the lookup value is valid.
517 --
518 -- Pre Conditions
519 -- None.
520 --
521 -- In Parameters
522 -- ttl_prtt_rt_id PK of record being inserted or updated.
523 -- excld_flag Value of lookup code.
524 -- effective_date effective date
525 -- object_version_number Object version number of record being
526 -- inserted or updated.
527 --
528 -- Post Success
529 -- Processing continues
530 --
531 -- Post Failure
532 -- Error handled by procedure
533 --
534 -- Access Status
535 -- Internal table handler use only.
536 --
537 Procedure chk_excld_flag(p_ttl_prtt_rt_id in number,
538 p_excld_flag in varchar2,
539 p_effective_date in date,
540 p_object_version_number in number) is
541 --
542 l_proc varchar2(72) := g_package||'chk_excld_flag';
543 l_api_updating boolean;
544 --
545 Begin
546 --
547 hr_utility.set_location('Entering:'||l_proc, 5);
548 --
549 l_api_updating := ben_ttp_shd.api_updating
550 (p_ttl_prtt_rt_id => p_ttl_prtt_rt_id,
551 p_effective_date => p_effective_date,
552 p_object_version_number => p_object_version_number);
553 --
554 if (l_api_updating
555 and p_excld_flag
556 <> nvl(ben_ttp_shd.g_old_rec.excld_flag,hr_api.g_varchar2)
557 or not l_api_updating) then
558 --
559 -- check if value of lookup falls within lookup type.
560 --
561 --
562 if hr_api.not_exists_in_hr_lookups
563 (p_lookup_type => 'YES_NO',
564 p_lookup_code => p_excld_flag,
565 p_effective_date => p_effective_date) then
566 --
567 -- raise error as does not exist as lookup
568 --
569 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
570 fnd_message.set_token('FIELD','p_excld_flag');
571 fnd_message.set_token('TYPE','YES_NO');
572 fnd_message.raise_error;
573 --
574 end if;
575 --
576 end if;
577 --
578 hr_utility.set_location('Leaving:'||l_proc,10);
579 --
580 end chk_excld_flag;
581 --
582 -- ----------------------------------------------------------------------------
583 -- |------------------------< chk_prtt_cvg_vol_mutexcl >----------------------|
584 -- ----------------------------------------------------------------------------
585 --
586 -- Description
587 -- This procedure is used to check that the prtt_rt is mutually exclusive for
588 -- vrbl_rt_prfl_id. A ttl_cvg_vol_rt cannot exist with this vrbl_rt_prfl_id
589 -- due to the ARC relationship on ben_vrbl_rt_prfl_f.
590 --
591 -- Pre Conditions
592 -- None.
593 --
597 -- effective_date Session date of record.
594 -- In Parameters
595 -- ttl_prtt_rt_id PK of record being inserted or updated.
596 -- vrbl_rt_prfl_id vrbl_rt_prfl_id.
598 -- business_group_id Business group id of record being inserted.
599 --
600 -- Post Success
601 -- Processing continues
602 --
603 -- Post Failure
604 -- Errors handled by the procedure
605 --
606 -- Access Status
607 -- Internal table handler use only.
608 --
609 Procedure chk_prtt_cvg_vol_mutexcl(p_ttl_prtt_rt_id in number,
610 p_vrbl_rt_prfl_id in number,
611 p_effective_date in date,
612 p_business_group_id in number) is
613 --
614 l_proc varchar2(72) := g_package||'chk_prtt_cvg_vol_mutexcl';
615 l_api_updating boolean;
616 l_dummy varchar2(1);
617 --
618 cursor c1 is
619 select null
620 from ben_ttl_cvg_vol_rt_f a
621
622 where a.business_group_id +0 = p_business_group_id
623 and a.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
624 and p_effective_date
625 between a.effective_start_date
626 and a.effective_end_date;
627 --
628 Begin
629 --
630 hr_utility.set_location('Entering:'||l_proc, 5);
631 --
632 if p_vrbl_rt_prfl_id is not null then
633 --
634 -- Check if vrbl_rt_prfl_id is mutually exclusive.
635 --
636 open c1;
637 --
638 fetch c1 into l_dummy;
639 if c1%found then
640 --
641 --
642 close c1;
643 --
644 -- raise an error as vrbl_rt_prfl_id assigned to ttl_cvg_vol_rt(s).
645 --
646 fnd_message.set_name('BEN','BEN_92259_TTLPRTT_CVGVOL_EXCL2');
647 fnd_message.raise_error;
648 --
649 end if;
650 --
651 close c1;
652 --
653 end if;
654 --
655 hr_utility.set_location('Leaving:'||l_proc, 10);
656 --
657 End chk_prtt_cvg_vol_mutexcl;
658 -- ----------------------------------------------------------------------------
659 -- |------< chk_flag_and_val >------|
660 -- ----------------------------------------------------------------------------
661 --
662 -- Description
663 -- This procedure is used to check if the flag is turned on then
664 -- the value must be null
665 --
666 -- Pre Conditions
667 -- None.
668 --
669 -- In Parameters
670 -- P_FLAG value of flag item.
671 -- P_VAL value of value item
672 -- P_MSG message name to dispaly if validation fails
673 --
674 -- Post Success
675 -- Processing continues
676 --
677 -- Post Failure
678 -- Error handled by procedure
679 --
680 -- Access Status
681 -- Internal table handler use only.
682 --
683 Procedure chk_flag_and_val(p_flag in varchar2,
684 p_val in number,
685 p_msg in varchar2) is
686 --
687 l_proc varchar2(72) := g_package||'chk_flag_and_val';
688 --
689 Begin
690 --
691 hr_utility.set_location('Entering:'||l_proc, 5);
692 --
693 if p_flag = 'Y' and p_val is not null then
694 fnd_message.set_name('BEN', p_msg);
695 fnd_message.raise_error;
696 end if;
697 --
698 hr_utility.set_location('Leaving:'||l_proc,10);
699 --
700 end chk_flag_and_val;
701 --
702 -- ----------------------------------------------------------------------------
703 -- |---------------------------< chk_mn_val_mx_val >--------------------------|
704 -- ----------------------------------------------------------------------------
705 --
706 -- Description
707 -- This procedure is used to check if the min value is
708 -- less than the max value.
709 --
710 -- Pre Conditions
711 -- None.
712 --
713 -- In Parameters
714 -- p_mn_val minimum value
715 -- p_mx_val maximum value
716 --
717 -- Post Success
718 -- Processing continues
719 --
720 -- Post Failure
721 -- Error handled by procedure
722 --
723 -- Access Status
724 -- Internal table handler use only.
725 --
726 Procedure chk_mn_val_mx_val(p_mn_val in number,
727 p_mx_val in number) is
728 --
729 l_proc varchar2(72) := g_package||'chk_mn_val_mx_val';
730 --
731 Begin
732 --
733 hr_utility.set_location('Entering:'||l_proc, 5);
734 --
735 -- check the values
736 -- note: Don't want an error if either one is null
737 --
738 if (p_mn_val >= p_mx_val) then
739 --
740 -- raise error as is not a valid combination
741 --
742 fnd_message.set_name('BEN','BEN_91142_MIN_LESS_NOT_EQ_MAX');
743 fnd_message.raise_error;
744 --
745 end if;
746 --
747 hr_utility.set_location('Leaving:'||l_proc, 5);
748 --
749 end chk_mn_val_mx_val;
750 ----------------------------------------------------------------------------
754 -- Description
751 --|-------------------------< chk_mn_mx_prtt_num >-------------------------|
752 ----------------------------------------------------------------------------
753 --
755 -- This procedure is used to check that prtt number is always less
756 -- than max prtt number and either the flag is set or the number.
757 --
758 -- Pre Conditions
759 -- None.
760 --
761 -- In Parameters
762 -- ttl_prtt_rt_id PK of record being inserted or updated.
763 -- mn_prtt_num Value of Minimum value.
764 -- mx_prtt_num Value of Maximum value.
765 -- no_mn_prtt_num_apls_flag Value of Minimum flag.
766 -- no_mx_prtt_num_apls_flag Value of Maximum flag.
767 -- effective_date effective date
768 -- object_version_number Object version number of record being
769 -- inserted or updated.
770 --
771 -- Post Success
772 -- Processing continues
773 --
774 -- Post Failure
775 -- Error handled by procedure
776 --
777 -- Access Status
778 -- Internal table handler use only.
779 --
780 Procedure chk_mn_mx_prtt_num(p_ttl_prtt_rt_id in number,
781 p_no_mn_prtt_num_apls_flag in varchar2,
782 p_mn_prtt_num in number,
783 p_no_mx_prtt_num_apls_flag in varchar2,
784 p_mx_prtt_num in number,
785 p_object_version_number in number) is
786 --
787 l_proc varchar2(72) := g_package || 'chk_mn_mx_prtt_num';
788 l_api_updating boolean;
789 l_dummy varchar2(1);
790 --
791 Begin
792 --
793 hr_utility.set_location('Entering:'||l_proc, 5);
794 --
795 -- Minimum prtt Number must be < Maximum prtt Number,
796 -- if both are used.
797 --
798 if p_mn_prtt_num is not null and p_mx_prtt_num is not null then
799 --
800 -- raise error if max value not greater than min value
801 --
802 if p_mx_prtt_num <= p_mn_prtt_num then
803 --
804 fnd_message.set_name('BEN','BEN_91069_INVALID_MIN_MAX');
805 fnd_message.raise_error;
806 --
807 end if;
808 --
809 end if;
810 --
811 -- If No Minimum prtt flag set to "on" (Y),
812 -- then minimum prtt number must be blank.
813 --
814 if p_no_mn_prtt_num_apls_flag = 'Y' and
815 p_mn_prtt_num is not null then
816 --
817 fnd_message.set_name('BEN','BEN_91054_MIN_VAL_NOT_NULL');
818 fnd_message.raise_error;
819 --
820 elsif p_no_mn_prtt_num_apls_flag = 'N' and
821 p_mn_prtt_num is null then
822 --
823 fnd_message.set_name('BEN','BEN_91055_MIN_VAL_REQUIRED');
824 fnd_message.raise_error;
825 --
826 end if;
827 --
828 -- If No Maximum poe flag set to "on" (Y),
829 -- then maximum poe number must be blank.
830 --
831 if p_no_mx_prtt_num_apls_flag = 'Y' and
832 p_mx_prtt_num is not null then
833 --
834 fnd_message.set_name('BEN','BEN_91056_MAX_VAL_NOT_NULL');
835 fnd_message.raise_error;
836 --
837 elsif p_no_mx_prtt_num_apls_flag = 'N' and
838 p_mx_prtt_num is null then
839 --
840 fnd_message.set_name('BEN','BEN_91057_MAX_VAL_REQUIRED');
841 fnd_message.raise_error;
842 --
843 end if;
844 --
845 hr_utility.set_location('Leaving:'||l_proc,10);
846 --
847 end chk_mn_mx_prtt_num;
848 -- ----------------------------------------------------------------------------
849 -- |--------------------------< dt_update_validate >--------------------------|
850 -- ----------------------------------------------------------------------------
851 -- {Start Of Comments}
852 --
853 -- Description:
854 -- This procedure is used for referential integrity of datetracked
855 -- parent entities when a datetrack update operation is taking place
856 -- and where there is no cascading of update defined for this entity.
857 --
858 -- Prerequisites:
859 -- This procedure is called from the update_validate.
860 --
861 -- In Parameters:
862 --
863 -- Post Success:
864 -- Processing continues.
865 --
866 -- Post Failure:
867 --
868 -- Developer Implementation Notes:
869 -- changes.
870 --
871 -- Access Status:
872 -- Internal Row Handler Use Only.
873 --
874 -- {End Of Comments}
875 -- ----------------------------------------------------------------------------
876 Procedure dt_update_validate
877 (p_vrbl_rt_prfl_id in number default hr_api.g_number,
878 p_datetrack_mode in varchar2,
879 p_validation_start_date in date,
880 p_validation_end_date in date) Is
881 --
882 l_proc varchar2(72) := g_package||'dt_update_validate';
883 l_integrity_error Exception;
884 l_table_name all_tables.table_name%TYPE;
885 --
886 Begin
887 hr_utility.set_location('Entering:'||l_proc, 5);
888 --
889 -- Ensure that the p_datetrack_mode argument is not null
890 --
891 hr_api.mandatory_arg_error
892 (p_api_name => l_proc,
893 p_argument => 'datetrack_mode',
894 p_argument_value => p_datetrack_mode);
895 --
899 --
896 -- Only perform the validation if the datetrack update mode is valid
897 --
898 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
900 --
901 -- Ensure the arguments are not null
902 --
903 hr_api.mandatory_arg_error
904 (p_api_name => l_proc,
905 p_argument => 'validation_start_date',
906 p_argument_value => p_validation_start_date);
907 --
908 hr_api.mandatory_arg_error
909 (p_api_name => l_proc,
910 p_argument => 'validation_end_date',
911 p_argument_value => p_validation_end_date);
912 --
913 If ((nvl(p_vrbl_rt_prfl_id, hr_api.g_number) <> hr_api.g_number) and
914 NOT (dt_api.check_min_max_dates
915 (p_base_table_name => 'ben_vrbl_rt_prfl_f',
916 p_base_key_column => 'vrbl_rt_prfl_id',
917 p_base_key_value => p_vrbl_rt_prfl_id,
918 p_from_date => p_validation_start_date,
919 p_to_date => p_validation_end_date))) Then
920 l_table_name := 'ben_vrbl_rt_prfl_f';
921 Raise l_integrity_error;
922 End If;
923 --
924 End If;
925 --
926 hr_utility.set_location(' Leaving:'||l_proc, 10);
927 Exception
928 When l_integrity_error Then
929 --
930 -- A referential integrity check was violated therefore
931 -- we must error
932 --
933 ben_utility.parent_integrity_error(p_table_name => l_table_name);
934 When Others Then
935 --
936 -- An unhandled or unexpected error has occurred which
937 -- we must report
938 --
939 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
940 fnd_message.set_token('PROCEDURE', l_proc);
941 fnd_message.set_token('STEP','15');
942 fnd_message.raise_error;
943 End dt_update_validate;
944 --
945 -- ----------------------------------------------------------------------------
946 -- |--------------------------< dt_delete_validate >--------------------------|
947 -- ----------------------------------------------------------------------------
948 -- {Start Of Comments}
949 --
950 -- Description:
951 -- This procedure is used for referential integrity of datetracked
952 -- child entities when either a datetrack DELETE or ZAP is in operation
953 -- and where there is no cascading of delete defined for this entity.
954 -- For the datetrack mode of DELETE or ZAP we must ensure that no
955 -- datetracked child rows exist between the validation start and end
956 -- dates.
957 --
958 -- Prerequisites:
959 -- This procedure is called from the delete_validate.
960 --
961 -- In Parameters:
962 --
963 -- Post Success:
964 -- Processing continues.
965 --
966 -- Post Failure:
967 -- If a row exists by determining the returning Boolean value from the
968 -- generic dt_api.rows_exist function then we must supply an error via
969 -- the use of the local exception handler l_rows_exist.
970 --
971 -- Developer Implementation Notes:
972 -- This procedure should not need maintenance unless the HR Schema model
973 -- changes.
974 --
975 -- Access Status:
976 -- Internal Row Handler Use Only.
977 --
978 -- {End Of Comments}
979 -- ----------------------------------------------------------------------------
980 Procedure dt_delete_validate
981 (p_ttl_prtt_rt_id in number,
982 p_datetrack_mode in varchar2,
983 p_validation_start_date in date,
984 p_validation_end_date in date) Is
985 --
986 l_proc varchar2(72) := g_package||'dt_delete_validate';
987 l_rows_exist Exception;
988 l_table_name all_tables.table_name%TYPE;
989 --
990 Begin
991 hr_utility.set_location('Entering:'||l_proc, 5);
992 --
993 -- Ensure that the p_datetrack_mode argument is not null
994 --
995 hr_api.mandatory_arg_error
996 (p_api_name => l_proc,
997 p_argument => 'datetrack_mode',
998 p_argument_value => p_datetrack_mode);
999 --
1000 -- Only perform the validation if the datetrack mode is either
1001 -- DELETE or ZAP
1002 --
1003 If (p_datetrack_mode = 'DELETE' or
1004 p_datetrack_mode = 'ZAP') then
1005 --
1006 --
1007 -- Ensure the arguments are not null
1008 --
1009 hr_api.mandatory_arg_error
1010 (p_api_name => l_proc,
1011 p_argument => 'validation_start_date',
1012 p_argument_value => p_validation_start_date);
1013 --
1014 hr_api.mandatory_arg_error
1015 (p_api_name => l_proc,
1016 p_argument => 'validation_end_date',
1017 p_argument_value => p_validation_end_date);
1018 --
1019 hr_api.mandatory_arg_error
1020 (p_api_name => l_proc,
1021 p_argument => 'ttl_prtt_rt_id',
1022 p_argument_value => p_ttl_prtt_rt_id);
1023 --
1024 --
1025 --
1026 End If;
1027 --
1028 hr_utility.set_location(' Leaving:'||l_proc, 10);
1029 Exception
1030 When l_rows_exist Then
1031 --
1032 -- A referential integrity check was violated therefore
1033 -- we must error
1034 --
1035 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
1039 --
1036 fnd_message.set_token('TABLE_NAME', l_table_name);
1037 fnd_message.raise_error;
1038 When Others Then
1040 -- An unhandled or unexpected error has occurred which
1041 -- we must report
1042 --
1043 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1044 fnd_message.set_token('PROCEDURE', l_proc);
1045 fnd_message.set_token('STEP','15');
1046 fnd_message.raise_error;
1047 End dt_delete_validate;
1048 --
1049 -- ----------------------------------------------------------------------------
1050 -- |---------------------------< insert_validate >----------------------------|
1051 -- ----------------------------------------------------------------------------
1052 Procedure insert_validate
1053 (p_rec in ben_ttp_shd.g_rec_type,
1054 p_effective_date in date,
1055 p_datetrack_mode in varchar2,
1056 p_validation_start_date in date,
1057 p_validation_end_date in date) is
1058 --
1059 l_proc varchar2(72) := g_package||'insert_validate';
1060 --
1061 Begin
1062 hr_utility.set_location('Entering:'||l_proc, 5);
1063 --
1064 -- Call all supporting business operations
1065 --
1066 --
1067 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1068 --
1069 chk_ttl_prtt_rt_id
1070 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1071 p_effective_date => p_effective_date,
1072 p_object_version_number => p_rec.object_version_number);
1073 --
1074 chk_prtt_det_rl
1075 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1076 p_prtt_det_rl => p_rec.prtt_det_rl,
1077 p_effective_date => p_effective_date,
1078 p_object_version_number => p_rec.object_version_number);
1079 --
1080 chk_prtt_det_cd
1081 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1082 p_prtt_det_cd => p_rec.prtt_det_cd,
1083 p_effective_date => p_effective_date,
1084 p_object_version_number => p_rec.object_version_number);
1085 --
1086 chk_no_mx_prtt_num_apls_flag
1087 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1088 p_no_mx_prtt_num_apls_flag => p_rec.no_mx_prtt_num_apls_flag,
1089 p_effective_date => p_effective_date,
1090 p_object_version_number => p_rec.object_version_number);
1091 --
1092 chk_no_mn_prtt_num_apls_flag
1093 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1094 p_no_mn_prtt_num_apls_flag => p_rec.no_mn_prtt_num_apls_flag,
1095 p_effective_date => p_effective_date,
1096 p_object_version_number => p_rec.object_version_number);
1097 --
1098 chk_excld_flag
1099 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1100 p_excld_flag => p_rec.excld_flag,
1101 p_effective_date => p_effective_date,
1102 p_object_version_number => p_rec.object_version_number);
1103 --
1104 chk_prtt_cvg_vol_mutexcl
1105 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1106 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1107 p_effective_date => p_effective_date,
1108 p_business_group_id => p_rec.business_group_id);
1109 --
1110 chk_flag_and_val
1111 (p_flag => p_rec.no_mn_prtt_num_apls_flag,
1112 p_val => p_rec.mn_prtt_num,
1113 p_msg => 'BEN_91695_MIN_VAL_FLAG_EXCLSV');
1114 --
1115 chk_flag_and_val
1116 (p_flag => p_rec.no_mx_prtt_num_apls_flag,
1117 p_val => p_rec.mx_prtt_num,
1118 p_msg => 'BEN_91696_MAX_VAL_FLAG_EXCLSV');
1119 --
1120 chk_mn_val_mx_val
1121 (p_mn_val => p_rec.mn_prtt_num,
1122 p_mx_val => p_rec.mx_prtt_num);
1123 --
1124 chk_mn_mx_prtt_num
1125 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1126 p_no_mn_prtt_num_apls_flag => p_rec.no_mn_prtt_num_apls_flag,
1127 p_mn_prtt_num => p_rec.mn_prtt_num,
1128 p_no_mx_prtt_num_apls_flag => p_rec.no_mx_prtt_num_apls_flag,
1129 p_mx_prtt_num => p_rec.mx_prtt_num,
1130 p_object_version_number => p_rec.object_version_number);
1131 --
1132 chk_duplicate_ordr_num
1133 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
1134 ,p_ordr_num => p_rec.ordr_num
1135 ,p_effective_date => p_effective_date
1136 ,p_business_group_id => p_rec.business_group_id);
1137
1138 -- For bug 2529689
1139 -- Call procedure to check mltcd is TTLPRTT
1140 chk_ttlprtt_mlt_cd
1141 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1142 p_effective_date => p_effective_date,
1143 p_business_group_id => p_rec.business_group_id);
1144
1145 --
1146 hr_utility.set_location(' Leaving:'||l_proc, 10);
1147 End insert_validate;
1148 --
1149 -- ----------------------------------------------------------------------------
1150 -- |---------------------------< update_validate >----------------------------|
1151 -- ----------------------------------------------------------------------------
1152 Procedure update_validate
1153 (p_rec in ben_ttp_shd.g_rec_type,
1154 p_effective_date in date,
1155 p_datetrack_mode in varchar2,
1156 p_validation_start_date in date,
1157 p_validation_end_date in date) is
1158 --
1159 l_proc varchar2(72) := g_package||'update_validate';
1160 --
1161 Begin
1162 hr_utility.set_location('Entering:'||l_proc, 5);
1163 --
1167 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1164 -- Call all supporting business operations
1165 --
1166 --
1168 --
1169 chk_ttl_prtt_rt_id
1170 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1171 p_effective_date => p_effective_date,
1172 p_object_version_number => p_rec.object_version_number);
1173 --
1174 chk_prtt_det_rl
1175 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1176 p_prtt_det_rl => p_rec.prtt_det_rl,
1177 p_effective_date => p_effective_date,
1178 p_object_version_number => p_rec.object_version_number);
1179 --
1180 chk_prtt_det_cd
1181 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1182 p_prtt_det_cd => p_rec.prtt_det_cd,
1183 p_effective_date => p_effective_date,
1184 p_object_version_number => p_rec.object_version_number);
1185 --
1186 chk_no_mx_prtt_num_apls_flag
1187 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1188 p_no_mx_prtt_num_apls_flag => p_rec.no_mx_prtt_num_apls_flag,
1189 p_effective_date => p_effective_date,
1190 p_object_version_number => p_rec.object_version_number);
1191 --
1192 chk_no_mn_prtt_num_apls_flag
1193 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1194 p_no_mn_prtt_num_apls_flag => p_rec.no_mn_prtt_num_apls_flag,
1195 p_effective_date => p_effective_date,
1196 p_object_version_number => p_rec.object_version_number);
1197 --
1198 chk_excld_flag
1199 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1200 p_excld_flag => p_rec.excld_flag,
1201 p_effective_date => p_effective_date,
1202 p_object_version_number => p_rec.object_version_number);
1203 --
1204 chk_prtt_cvg_vol_mutexcl
1205 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1206 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1207 p_effective_date => p_effective_date,
1208 p_business_group_id => p_rec.business_group_id);
1209 --
1210 chk_flag_and_val
1211 (p_flag => p_rec.no_mn_prtt_num_apls_flag,
1212 p_val => p_rec.mn_prtt_num,
1213 p_msg => 'BEN_91695_MIN_VAL_FLAG_EXCLSV');
1214 --
1215 chk_flag_and_val
1216 (p_flag => p_rec.no_mx_prtt_num_apls_flag,
1217 p_val => p_rec.mx_prtt_num,
1218 p_msg => 'BEN_91696_MAX_VAL_FLAG_EXCLSV');
1219 --
1220 chk_mn_val_mx_val
1221 (p_mn_val => p_rec.mn_prtt_num,
1222 p_mx_val => p_rec.mx_prtt_num);
1223 --
1224 chk_mn_mx_prtt_num
1225 (p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id,
1226 p_no_mn_prtt_num_apls_flag => p_rec.no_mn_prtt_num_apls_flag,
1227 p_mn_prtt_num => p_rec.mn_prtt_num,
1228 p_no_mx_prtt_num_apls_flag => p_rec.no_mx_prtt_num_apls_flag,
1229 p_mx_prtt_num => p_rec.mx_prtt_num,
1230 p_object_version_number => p_rec.object_version_number);
1231 --
1232 -- Call the datetrack update integrity operation
1233 --
1234 dt_update_validate
1235 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1236 p_datetrack_mode => p_datetrack_mode,
1237 p_validation_start_date => p_validation_start_date,
1238 p_validation_end_date => p_validation_end_date);
1239 --
1240 chk_duplicate_ordr_num
1241 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
1242 ,p_ordr_num => p_rec.ordr_num
1243 ,p_effective_date => p_effective_date
1244 ,p_business_group_id => p_rec.business_group_id);
1245 --
1246 -- For bug 2529689
1247 -- Call procedure to check mltcd is TTLPRTT
1248 chk_ttlprtt_mlt_cd
1249 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1250 p_effective_date => p_effective_date,
1251 p_business_group_id => p_rec.business_group_id);
1252 --
1253
1254 hr_utility.set_location(' Leaving:'||l_proc, 10);
1255 End update_validate;
1256 --
1257 -- ----------------------------------------------------------------------------
1258 -- |---------------------------< delete_validate >----------------------------|
1259 -- ----------------------------------------------------------------------------
1260 Procedure delete_validate
1261 (p_rec in ben_ttp_shd.g_rec_type,
1262 p_effective_date in date,
1263 p_datetrack_mode in varchar2,
1264 p_validation_start_date in date,
1265 p_validation_end_date in date) is
1266 --
1267 l_proc varchar2(72) := g_package||'delete_validate';
1268 --
1269 Begin
1270 hr_utility.set_location('Entering:'||l_proc, 5);
1271 --
1272 -- Call all supporting business operations
1273 --
1274 dt_delete_validate
1275 (p_datetrack_mode => p_datetrack_mode,
1276 p_validation_start_date => p_validation_start_date,
1277 p_validation_end_date => p_validation_end_date,
1278 p_ttl_prtt_rt_id => p_rec.ttl_prtt_rt_id);
1279 --
1280 hr_utility.set_location(' Leaving:'||l_proc, 10);
1281 End delete_validate;
1282 --
1283 --
1284 -- ---------------------------------------------------------------------------
1285 -- |---------------------< return_legislation_code >-------------------------|
1286 -- ---------------------------------------------------------------------------
1287 --
1288 function return_legislation_code
1289 (p_ttl_prtt_rt_id in number) return varchar2 is
1290 --
1291 -- Declare cursor
1292 --
1293 cursor csr_leg_code is
1294 select a.legislation_code
1295 from per_business_groups a,
1296 ben_ttl_prtt_rt_f b
1297 where b.ttl_prtt_rt_id = p_ttl_prtt_rt_id
1298 and a.business_group_id = b.business_group_id;
1299 --
1300 -- Declare local variables
1301 --
1302 l_legislation_code varchar2(150);
1303 l_proc varchar2(72) := g_package||'return_legislation_code';
1304 --
1305 begin
1306 --
1307 hr_utility.set_location('Entering:'|| l_proc, 10);
1308 --
1309 -- Ensure that all the mandatory parameter are not null
1310 --
1311 hr_api.mandatory_arg_error(p_api_name => l_proc,
1312 p_argument => 'ttl_prtt_rt_id',
1313 p_argument_value => p_ttl_prtt_rt_id);
1314 --
1315 open csr_leg_code;
1316 --
1317 fetch csr_leg_code into l_legislation_code;
1318 --
1319 if csr_leg_code%notfound then
1320 --
1321 close csr_leg_code;
1322 --
1323 -- The primary key is invalid therefore we must error
1324 --
1325 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1326 fnd_message.raise_error;
1327 --
1328 end if;
1329 --
1330 close csr_leg_code;
1331 --
1332 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1333 --
1334 return l_legislation_code;
1335 --
1336 end return_legislation_code;
1337 --
1338 end ben_ttp_bus;