[Home] [Help]
PACKAGE BODY: APPS.BEN_ETP_BUS
Source
1 Package Body ben_etp_bus as
2 /* $Header: beetprhi.pkb 120.0 2005/05/28 03:02:29 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_etp_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_ELIG_TTL_PRTT_PRTE_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 -- ELIG_TTL_PRTT_PRTE_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_ELIG_TTL_PRTT_PRTE_id(p_ELIG_TTL_PRTT_PRTE_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_ELIG_TTL_PRTT_PRTE_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_etp_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_ELIG_TTL_PRTT_PRTE_id => p_ELIG_TTL_PRTT_PRTE_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_ELIG_TTL_PRTT_PRTE_id,hr_api.g_number)
55 <> ben_etp_shd.g_old_rec.ELIG_TTL_PRTT_PRTE_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_etp_shd.constraint_error('BEN_ELIG_TTL_PRTT_PRTE_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_ELIG_TTL_PRTT_PRTE_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_etp_shd.constraint_error('BEN_ELIG_TTL_PRTT_PRTE_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_ELIG_TTL_PRTT_PRTE_id;
78 --
79 -- ---------------------------------------------------------------------------
80 -- |-----------------------< chk_duplicate_ordr_num >---------------------------|
81 -- ---------------------------------------------------------------------------
82 --
83 -- Description
84 -- Ensure that the Sequence Number is unique
85 -- within business_group
86 --
87 -- Pre Conditions
88 -- None.
89 --
90 -- In Parameters
91 -- p_ELIG_TTL_PRTT_PRTE_id ELIG_TTL_PRTT_PRTE_id
92 -- p_eligy_prfl_id eligy_prfl_id
93 -- p_ordr_num Sequence Number
94 -- p_business_group_id
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Errors handled by the procedure
101 --
102 -- Access Status
103 -- Internal table handler use only
104 --
105 -- ----------------------------------------------------------------------------
106 Procedure chk_duplicate_ordr_num
107 ( p_ELIG_TTL_PRTT_PRTE_id in number
108 ,p_eligy_prfl_id in number
109 ,p_ordr_num in number
110 ,p_business_group_id in number)
111 is
112 l_proc varchar2(72) := g_package||'chk_duplicate_ordr_num';
113 l_dummy char(1);
114 cursor c1 is
115 select null
116 from ben_elig_TTL_PRTT_prte_f
117 where elig_TTL_PRTT_prte_id <> nvl(p_elig_TTL_PRTT_prte_id,-1)
118 and eligy_prfl_id = p_eligy_prfl_id
119 and ordr_num = p_ordr_num
120 and business_group_id = p_business_group_id;
121 --
122 Begin
123 hr_utility.set_location('Entering:'||l_proc, 5);
124 --
125 open c1;
126 fetch c1 into l_dummy;
127 if c1%found then
128 close c1;
129 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
130 fnd_message.raise_error;
131 end if;
132 close c1;
133 --
134 hr_utility.set_location('Leaving:'||l_proc, 15);
135 End chk_duplicate_ordr_num;
136
137 --
138 -- ----------------------------------------------------------------------------
139 -- |------< chk_prtt_det_rl >------|
140 -- ----------------------------------------------------------------------------
141 --
142 -- Description
143 -- This procedure is used to check that the Formula Rule is valid.
144 --
145 -- Pre Conditions
146 -- None.
147 --
148 -- In Parameters
149 -- ELIG_TTL_PRTT_PRTE_id PK of record being inserted or updated.
150 -- prtt_det_rl Value of formula rule id.
151 -- effective_date effective date
152 -- object_version_number Object version number of record being
153 -- inserted or updated.
154 --
155 -- Post Success
156 -- Processing continues
157 --
158 -- Post Failure
159 -- Error handled by procedure
160 --
161 -- Access Status
162 -- Internal table handler use only.
163 --
164 Procedure chk_prtt_det_rl(p_ELIG_TTL_PRTT_PRTE_id in number,
165 p_prtt_det_rl in number,
166 p_effective_date in date,
167 p_object_version_number in number) is
168 --
169 l_proc varchar2(72) := g_package||'chk_prtt_det_rl';
170 l_api_updating boolean;
171 l_dummy varchar2(1);
172 --
173 cursor c1 is
174 select null
175 from ff_formulas_f ff
176 where ff.formula_id = p_prtt_det_rl
177 and p_effective_date
178 between ff.effective_start_date
179 and ff.effective_end_date;
180 --
181 Begin
182 --
183 hr_utility.set_location('Entering:'||l_proc, 5);
184 --
185 l_api_updating := ben_etp_shd.api_updating
186 (p_ELIG_TTL_PRTT_PRTE_id => p_ELIG_TTL_PRTT_PRTE_id,
187 p_effective_date => p_effective_date,
188 p_object_version_number => p_object_version_number);
189 --
190 if (l_api_updating
191 and nvl(p_prtt_det_rl,hr_api.g_number)
192 <> ben_etp_shd.g_old_rec.prtt_det_rl
193 or not l_api_updating)
194 and p_prtt_det_rl is not null then
195 --
196 -- check if value of formula rule is valid.
197 --
198 open c1;
199 --
200 -- fetch value from cursor if it returns a record then the
201 -- formula is valid otherwise its invalid
202 --
203 fetch c1 into l_dummy;
204 if c1%notfound then
205 --
206 close c1;
207 --
208 -- raise error
209 --
210 fnd_message.set_name('PAY','FORMULA_DOES_NOT_EXIST');
211 fnd_message.raise_error;
212 --
213 end if;
214 --
215 close c1;
216 --
217 end if;
218 --
219 hr_utility.set_location('Leaving:'||l_proc,10);
220 --
221 end chk_prtt_det_rl;
222 --
223 -- ----------------------------------------------------------------------------
224 -- |------< chk_prtt_det_cd >------|
225 -- ----------------------------------------------------------------------------
226 --
227 -- Description
228 -- This procedure is used to check that the lookup value is valid.
229 --
230 -- Pre Conditions
231 -- None.
232 --
233 -- In Parameters
234 -- ELIG_TTL_PRTT_PRTE_id PK of record being inserted or updated.
235 -- prtt_det_cd Value of lookup code.
236 -- effective_date effective date
237 -- object_version_number Object version number of record being
238 -- inserted or updated.
239 --
240 -- Post Success
241 -- Processing continues
242 --
243 -- Post Failure
244 -- Error handled by procedure
245 --
246 -- Access Status
247 -- Internal table handler use only.
248 --
249 Procedure chk_prtt_det_cd(p_ELIG_TTL_PRTT_PRTE_id in number,
250 p_prtt_det_cd in varchar2,
251 p_effective_date in date,
252 p_object_version_number in number) is
253 --
254 l_proc varchar2(72) := g_package||'chk_prtt_det_cd';
255 l_api_updating boolean;
256 --
257 Begin
258 --
259 hr_utility.set_location('Entering:'||l_proc, 5);
260 --
261 l_api_updating := ben_etp_shd.api_updating
262 (p_ELIG_TTL_PRTT_PRTE_id => p_ELIG_TTL_PRTT_PRTE_id,
263 p_effective_date => p_effective_date,
264 p_object_version_number => p_object_version_number);
265 --
266 if (l_api_updating
267 and p_prtt_det_cd
268 <> nvl(ben_etp_shd.g_old_rec.prtt_det_cd,hr_api.g_varchar2)
269 or not l_api_updating)
270 and p_prtt_det_cd is not null then
271 --
272 -- check if value of lookup falls within lookup type.
273 --
274 if hr_api.not_exists_in_hr_lookups
275 (p_lookup_type => 'BEN_PRTT_DET',
276 p_lookup_code => p_prtt_det_cd,
277 p_effective_date => p_effective_date) then
278 --
279 -- raise error as does not exist as lookup
280 --
281 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
282 fnd_message.set_token('FIELD','p_prtt_det_cd');
283 fnd_message.set_token('TYPE','BEN_PRTT_DET');
284 fnd_message.raise_error;
285 --
286 end if;
287 --
288 end if;
289 --
290 hr_utility.set_location('Leaving:'||l_proc,10);
291 --
292 end chk_prtt_det_cd;
293 --
294 -- ----------------------------------------------------------------------------
295 -- |------< chk_no_mx_prtt_num_apls_flag >------|
296 -- ----------------------------------------------------------------------------
297 --
298 -- Description
299 -- This procedure is used to check that the lookup value is valid.
300 --
301 -- Pre Conditions
302 -- None.
303 --
304 -- In Parameters
305 -- ELIG_TTL_PRTT_PRTE_id PK of record being inserted or updated.
306 -- no_mx_prtt_num_apls_flag Value of lookup code.
307 -- effective_date effective date
308 -- object_version_number Object version number of record being
309 -- inserted or updated.
310 --
311 -- Post Success
312 -- Processing continues
313 --
314 -- Post Failure
315 -- Error handled by procedure
316 --
317 -- Access Status
318 -- Internal table handler use only.
319 --
320 Procedure chk_no_mx_prtt_num_apls_flag(p_ELIG_TTL_PRTT_PRTE_id in number,
321 p_no_mx_prtt_num_apls_flag in varchar2,
322 p_effective_date in date,
323 p_object_version_number in number) is
324 --
325 l_proc varchar2(72) := g_package||'chk_no_mx_prtt_num_apls_flag';
326 l_api_updating boolean;
327 --
328 Begin
329 --
330 hr_utility.set_location('Entering:'||l_proc, 5);
331 --
332 l_api_updating := ben_etp_shd.api_updating
333 (p_ELIG_TTL_PRTT_PRTE_id => p_ELIG_TTL_PRTT_PRTE_id,
334 p_effective_date => p_effective_date,
335 p_object_version_number => p_object_version_number);
336 --
337 if (l_api_updating
338 and p_no_mx_prtt_num_apls_flag
339 <> nvl(ben_etp_shd.g_old_rec.no_mx_prtt_num_apls_flag,hr_api.g_varchar2)
340 or not l_api_updating) then
341 --
342 -- check if value of lookup falls within lookup type.
343 --
344 --
345 if hr_api.not_exists_in_hr_lookups
346 (p_lookup_type => 'YES_NO',
347 p_lookup_code => p_no_mx_prtt_num_apls_flag,
348 p_effective_date => p_effective_date) then
349 --
350 -- raise error as does not exist as lookup
351 --
352 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
353 fnd_message.set_token('FIELD','p_no_mx_prtt_num_apls_flag');
354 fnd_message.set_token('TYPE','YES_NO');
355 fnd_message.raise_error;
356 --
357 end if;
358 --
359 end if;
360 --
361 hr_utility.set_location('Leaving:'||l_proc,10);
362 --
363 end chk_no_mx_prtt_num_apls_flag;
364 --
365 -- ----------------------------------------------------------------------------
366 -- |------< chk_no_mn_prtt_num_apls_flag >------|
367 -- ----------------------------------------------------------------------------
368 --
369 -- Description
370 -- This procedure is used to check that the lookup value is valid.
371 --
372 -- Pre Conditions
373 -- None.
374 --
375 -- In Parameters
376 -- ELIG_TTL_PRTT_PRTE_id PK of record being inserted or updated.
377 -- no_mn_prtt_num_apls_flag Value of lookup code.
378 -- effective_date effective date
379 -- object_version_number Object version number of record being
380 -- inserted or updated.
381 --
382 -- Post Success
383 -- Processing continues
384 --
385 -- Post Failure
386 -- Error handled by procedure
387 --
391 Procedure chk_no_mn_prtt_num_apls_flag(p_ELIG_TTL_PRTT_PRTE_id in number,
388 -- Access Status
389 -- Internal table handler use only.
390 --
392 p_no_mn_prtt_num_apls_flag in varchar2,
393 p_effective_date in date,
394 p_object_version_number in number) is
395 --
396 l_proc varchar2(72) := g_package||'chk_no_mn_prtt_num_apls_flag';
397 l_api_updating boolean;
398 --
399 Begin
400 --
401 hr_utility.set_location('Entering:'||l_proc, 5);
402 --
403 l_api_updating := ben_etp_shd.api_updating
404 (p_ELIG_TTL_PRTT_PRTE_id => p_ELIG_TTL_PRTT_PRTE_id,
405 p_effective_date => p_effective_date,
406 p_object_version_number => p_object_version_number);
407 --
408 if (l_api_updating
409 and p_no_mn_prtt_num_apls_flag
410 <> nvl(ben_etp_shd.g_old_rec.no_mn_prtt_num_apls_flag,hr_api.g_varchar2)
411 or not l_api_updating) then
412 --
413 -- check if value of lookup falls within lookup type.
414 --
415 --
416 if hr_api.not_exists_in_hr_lookups
417 (p_lookup_type => 'YES_NO',
418 p_lookup_code => p_no_mn_prtt_num_apls_flag,
419 p_effective_date => p_effective_date) then
420 --
421 -- raise error as does not exist as lookup
422 --
423 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
424 fnd_message.set_token('FIELD','p_no_mn_prtt_num_apls_flag');
425 fnd_message.set_token('TYPE','YES_NO');
426 fnd_message.raise_error;
427 --
428 end if;
429 --
430 end if;
431 --
432 hr_utility.set_location('Leaving:'||l_proc,10);
433 --
434 end chk_no_mn_prtt_num_apls_flag;
435 --
436 -- ----------------------------------------------------------------------------
437 -- |------< chk_excld_flag >------|
438 -- ----------------------------------------------------------------------------
439 --
440 -- Description
441 -- This procedure is used to check that the lookup value is valid.
442 --
443 -- Pre Conditions
444 -- None.
445 --
446 -- In Parameters
447 -- ELIG_TTL_PRTT_PRTE_id PK of record being inserted or updated.
448 -- excld_flag Value of lookup code.
449 -- effective_date effective date
450 -- object_version_number Object version number of record being
451 -- inserted or updated.
452 --
453 -- Post Success
454 -- Processing continues
455 --
456 -- Post Failure
457 -- Error handled by procedure
458 --
459 -- Access Status
460 -- Internal table handler use only.
461 --
462 Procedure chk_excld_flag(p_ELIG_TTL_PRTT_PRTE_id in number,
463 p_excld_flag in varchar2,
464 p_effective_date in date,
465 p_object_version_number in number) is
466 --
467 l_proc varchar2(72) := g_package||'chk_excld_flag';
468 l_api_updating boolean;
469 --
470 Begin
471 --
472 hr_utility.set_location('Entering:'||l_proc, 5);
473 --
474 l_api_updating := ben_etp_shd.api_updating
475 (p_ELIG_TTL_PRTT_PRTE_id => p_ELIG_TTL_PRTT_PRTE_id,
476 p_effective_date => p_effective_date,
477 p_object_version_number => p_object_version_number);
478 --
479 if (l_api_updating
480 and p_excld_flag
481 <> nvl(ben_etp_shd.g_old_rec.excld_flag,hr_api.g_varchar2)
482 or not l_api_updating) then
483 --
484 -- check if value of lookup falls within lookup type.
485 --
486 --
487 if hr_api.not_exists_in_hr_lookups
488 (p_lookup_type => 'YES_NO',
489 p_lookup_code => p_excld_flag,
490 p_effective_date => p_effective_date) then
491 --
492 -- raise error as does not exist as lookup
493 --
494 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
495 fnd_message.set_token('FIELD','p_excld_flag');
496 fnd_message.set_token('TYPE','YES_NO');
497 fnd_message.raise_error;
498 --
499 end if;
500 --
501 end if;
502 --
503 hr_utility.set_location('Leaving:'||l_proc,10);
504 --
505 end chk_excld_flag;
506 -- ----------------------------------------------------------------------------
507 -- |------< chk_flag_and_val >------|
508 -- ----------------------------------------------------------------------------
509 --
510 -- Description
511 -- This procedure is used to check if the flag is turned on then
512 -- the value must be null
513 --
514 -- Pre Conditions
515 -- None.
516 --
517 -- In Parameters
518 -- P_FLAG value of flag item.
519 -- P_VAL value of value item
520 -- P_MSG message name to dispaly if validation fails
521 --
522 -- Post Success
523 -- Processing continues
524 --
525 -- Post Failure
526 -- Error handled by procedure
527 --
528 -- Access Status
529 -- Internal table handler use only.
530 --
531 Procedure chk_flag_and_val(p_flag in varchar2,
535 l_proc varchar2(72) := g_package||'chk_flag_and_val';
532 p_val in number,
533 p_msg in varchar2) is
534 --
536 --
537 Begin
538 --
539 hr_utility.set_location('Entering:'||l_proc, 5);
540 --
541 if p_flag = 'Y' and p_val is not null then
542 fnd_message.set_name('BEN', p_msg);
543 fnd_message.raise_error;
544 end if;
545 --
546 hr_utility.set_location('Leaving:'||l_proc,10);
547 --
548 end chk_flag_and_val;
549 --
550 ----------------------------------------------------------------------------
551 --|-------------------------< chk_mn_mx_prtt_num >-------------------------|
552 ----------------------------------------------------------------------------
553 --
554 -- Description
555 -- This procedure is used to check that prtt number is always less
556 -- than max prtt number and either the flag is set or the number.
557 --
558 -- Pre Conditions
559 -- None.
560 --
561 -- In Parameters
562 -- ELIG_TTL_PRTT_PRTE_id PK of record being inserted or updated.
563 -- mn_prtt_num Value of Minimum value.
564 -- mx_prtt_num Value of Maximum value.
565 -- no_mn_prtt_num_apls_flag Value of Minimum flag.
566 -- no_mx_prtt_num_apls_flag Value of Maximum flag.
567 -- effective_date effective date
568 -- object_version_number Object version number of record being
569 -- inserted or updated.
570 --
571 -- Post Success
572 -- Processing continues
573 --
574 -- Post Failure
575 -- Error handled by procedure
576 --
577 -- Access Status
578 -- Internal table handler use only.
579 --
580 Procedure chk_mn_mx_prtt_num(p_ELIG_TTL_PRTT_PRTE_id in number,
581 p_no_mn_prtt_num_apls_flag in varchar2,
582 p_mn_prtt_num in number,
583 p_no_mx_prtt_num_apls_flag in varchar2,
584 p_mx_prtt_num in number,
585 p_object_version_number in number) is
586 --
587 l_proc varchar2(72) := g_package || 'chk_mn_mx_prtt_num';
588 l_api_updating boolean;
589 l_dummy varchar2(1);
590 --
591 Begin
592 --
593 hr_utility.set_location('Entering:'||l_proc, 5);
594 --
595 -- Minimum prtt Number must be < Maximum prtt Number,
596 -- if both are used.
597 --
598 if p_mn_prtt_num is not null and p_mx_prtt_num is not null then
599 --
600 -- raise error if max value not greater than min value
601 --
602 if p_mx_prtt_num <= p_mn_prtt_num then
603 --
604 fnd_message.set_name('BEN','BEN_91069_INVALID_MIN_MAX');
605 fnd_message.raise_error;
606 --
607 end if;
608 --
609 end if;
610 --
611 -- If No Minimum prtt flag set to "on" (Y),
612 -- then minimum prtt number must be blank.
613 --
614 if p_no_mn_prtt_num_apls_flag = 'Y' and
615 p_mn_prtt_num is not null then
616 --
617 fnd_message.set_name('BEN','BEN_91054_MIN_VAL_NOT_NULL');
618 fnd_message.raise_error;
619 --
620 elsif p_no_mn_prtt_num_apls_flag = 'N' and
621 p_mn_prtt_num is null then
622 --
623 fnd_message.set_name('BEN','BEN_91055_MIN_VAL_REQUIRED');
624 fnd_message.raise_error;
625 --
626 end if;
627 --
628 -- If No Maximum poe flag set to "on" (Y),
629 -- then maximum poe number must be blank.
630 --
631 if p_no_mx_prtt_num_apls_flag = 'Y' and
632 p_mx_prtt_num is not null then
633 --
634 fnd_message.set_name('BEN','BEN_91056_MAX_VAL_NOT_NULL');
635 fnd_message.raise_error;
636 --
637 elsif p_no_mx_prtt_num_apls_flag = 'N' and
638 p_mx_prtt_num is null then
639 --
640 fnd_message.set_name('BEN','BEN_91057_MAX_VAL_REQUIRED');
641 fnd_message.raise_error;
642 --
643 end if;
644 --
645 hr_utility.set_location('Leaving:'||l_proc,10);
646 --
647 end chk_mn_mx_prtt_num;
648 -- ----------------------------------------------------------------------------
649 -- |--------------------------< dt_update_validate >--------------------------|
650 -- ----------------------------------------------------------------------------
651 -- {Start Of Comments}
652 --
653 -- Description:
654 -- This procedure is used for referential integrity of datetracked
655 -- parent entities when a datetrack update operation is taking place
656 -- and where there is no cascading of update defined for this entity.
657 --
658 -- Prerequisites:
659 -- This procedure is called from the update_validate.
660 --
661 -- In Parameters:
662 --
663 -- Post Success:
664 -- Processing continues.
665 --
666 -- Post Failure:
667 --
668 -- Developer Implementation Notes:
669 -- changes.
670 --
671 -- Access Status:
672 -- Internal Row Handler Use Only.
673 --
674 -- {End Of Comments}
675 -- ----------------------------------------------------------------------------
676 Procedure dt_update_validate
680 p_validation_end_date in date) Is
677 (p_eligy_prfl_id in number default hr_api.g_number,
678 p_datetrack_mode in varchar2,
679 p_validation_start_date in date,
681 --
682 l_proc varchar2(72) := g_package||'dt_update_validate';
683 l_integrity_error Exception;
684 l_table_name all_tables.table_name%TYPE;
685 --
686 Begin
687 hr_utility.set_location('Entering:'||l_proc, 5);
688 --
689 -- Ensure that the p_datetrack_mode argument is not null
690 --
691 hr_api.mandatory_arg_error
692 (p_api_name => l_proc,
693 p_argument => 'datetrack_mode',
694 p_argument_value => p_datetrack_mode);
695 --
696 -- Only perform the validation if the datetrack update mode is valid
697 --
698 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
699 --
700 --
701 -- Ensure the arguments are not null
702 --
703 hr_api.mandatory_arg_error
704 (p_api_name => l_proc,
705 p_argument => 'validation_start_date',
706 p_argument_value => p_validation_start_date);
707 --
708 hr_api.mandatory_arg_error
709 (p_api_name => l_proc,
710 p_argument => 'validation_end_date',
711 p_argument_value => p_validation_end_date);
712 --
713 If ((nvl(p_eligy_prfl_id, hr_api.g_number) <> hr_api.g_number) and
714 NOT (dt_api.check_min_max_dates
715 (p_base_table_name => 'ben_eligy_prfl_f',
716 p_base_key_column => 'eligy_prfl_id',
717 p_base_key_value => p_eligy_prfl_id,
718 p_from_date => p_validation_start_date,
719 p_to_date => p_validation_end_date))) Then
720 l_table_name := 'ben_eligy_prfl_f';
721 Raise l_integrity_error;
722 End If;
723 --
724 End If;
725 --
726 hr_utility.set_location(' Leaving:'||l_proc, 10);
727 Exception
728 When l_integrity_error Then
729 --
730 -- A referential integrity check was violated therefore
731 -- we must error
732 --
733 ben_utility.parent_integrity_error(p_table_name => l_table_name);
734 When Others Then
735 --
736 -- An unhandled or unexpected error has occurred which
737 -- we must report
738 --
739 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
740 fnd_message.set_token('PROCEDURE', l_proc);
741 fnd_message.set_token('STEP','15');
742 fnd_message.raise_error;
743 End dt_update_validate;
744 --
745 -- ----------------------------------------------------------------------------
746 -- |--------------------------< dt_delete_validate >--------------------------|
747 -- ----------------------------------------------------------------------------
748 -- {Start Of Comments}
749 --
750 -- Description:
751 -- This procedure is used for referential integrity of datetracked
752 -- child entities when either a datetrack DELETE or ZAP is in operation
753 -- and where there is no cascading of delete defined for this entity.
754 -- For the datetrack mode of DELETE or ZAP we must ensure that no
755 -- datetracked child rows exist between the validation start and end
756 -- dates.
757 --
758 -- Prerequisites:
759 -- This procedure is called from the delete_validate.
760 --
761 -- In Parameters:
762 --
763 -- Post Success:
764 -- Processing continues.
765 --
766 -- Post Failure:
767 -- If a row exists by determining the returning Boolean value from the
768 -- generic dt_api.rows_exist function then we must supply an error via
769 -- the use of the local exception handler l_rows_exist.
770 --
771 -- Developer Implementation Notes:
772 -- This procedure should not need maintenance unless the HR Schema model
773 -- changes.
774 --
775 -- Access Status:
776 -- Internal Row Handler Use Only.
777 --
778 -- {End Of Comments}
779 -- ----------------------------------------------------------------------------
780 Procedure dt_delete_validate
781 (p_ELIG_TTL_PRTT_PRTE_id in number,
782 p_datetrack_mode in varchar2,
783 p_validation_start_date in date,
784 p_validation_end_date in date) Is
785 --
786 l_proc varchar2(72) := g_package||'dt_delete_validate';
787 l_rows_exist Exception;
788 l_table_name all_tables.table_name%TYPE;
789 --
790 Begin
791 hr_utility.set_location('Entering:'||l_proc, 5);
792 --
793 -- Ensure that the p_datetrack_mode argument is not null
794 --
795 hr_api.mandatory_arg_error
796 (p_api_name => l_proc,
797 p_argument => 'datetrack_mode',
798 p_argument_value => p_datetrack_mode);
799 --
800 -- Only perform the validation if the datetrack mode is either
801 -- DELETE or ZAP
802 --
803 If (p_datetrack_mode = 'DELETE' or
804 p_datetrack_mode = 'ZAP') then
805 --
806 --
807 -- Ensure the arguments are not null
808 --
809 hr_api.mandatory_arg_error
810 (p_api_name => l_proc,
811 p_argument => 'validation_start_date',
812 p_argument_value => p_validation_start_date);
813 --
817 p_argument_value => p_validation_end_date);
814 hr_api.mandatory_arg_error
815 (p_api_name => l_proc,
816 p_argument => 'validation_end_date',
818 --
819 hr_api.mandatory_arg_error
820 (p_api_name => l_proc,
821 p_argument => 'ELIG_TTL_PRTT_PRTE_id',
822 p_argument_value => p_ELIG_TTL_PRTT_PRTE_id);
823 --
824 --
825 --
826 End If;
827 --
828 hr_utility.set_location(' Leaving:'||l_proc, 10);
829 Exception
830 When l_rows_exist Then
831 --
832 -- A referential integrity check was violated therefore
833 -- we must error
834 --
835 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
836 fnd_message.set_token('TABLE_NAME', l_table_name);
837 fnd_message.raise_error;
838 When Others Then
839 --
840 -- An unhandled or unexpected error has occurred which
841 -- we must report
842 --
843 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
844 fnd_message.set_token('PROCEDURE', l_proc);
845 fnd_message.set_token('STEP','15');
846 fnd_message.raise_error;
847 End dt_delete_validate;
848 --
849 -- ----------------------------------------------------------------------------
850 -- |---------------------------< insert_validate >----------------------------|
851 -- ----------------------------------------------------------------------------
852 Procedure insert_validate
853 (p_rec in ben_etp_shd.g_rec_type,
854 p_effective_date in date,
855 p_datetrack_mode in varchar2,
856 p_validation_start_date in date,
857 p_validation_end_date in date) is
858 --
859 l_proc varchar2(72) := g_package||'insert_validate';
860 --
861 Begin
862 hr_utility.set_location('Entering:'||l_proc, 5);
863 --
864 -- Call all supporting business operations
865 --
866 --
867 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
868 --
869 chk_ELIG_TTL_PRTT_PRTE_id
870 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
871 p_effective_date => p_effective_date,
872 p_object_version_number => p_rec.object_version_number);
873 --
874 chk_prtt_det_rl
875 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
876 p_prtt_det_rl => p_rec.prtt_det_rl,
877 p_effective_date => p_effective_date,
878 p_object_version_number => p_rec.object_version_number);
879 --
880 chk_prtt_det_cd
881 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
882 p_prtt_det_cd => p_rec.prtt_det_cd,
883 p_effective_date => p_effective_date,
884 p_object_version_number => p_rec.object_version_number);
885 --
886 chk_no_mx_prtt_num_apls_flag
887 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
888 p_no_mx_prtt_num_apls_flag => p_rec.no_mx_prtt_num_apls_flag,
889 p_effective_date => p_effective_date,
890 p_object_version_number => p_rec.object_version_number);
891 --
892 chk_no_mn_prtt_num_apls_flag
893 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
894 p_no_mn_prtt_num_apls_flag => p_rec.no_mn_prtt_num_apls_flag,
895 p_effective_date => p_effective_date,
896 p_object_version_number => p_rec.object_version_number);
897 --
898 chk_excld_flag
899 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
900 p_excld_flag => p_rec.excld_flag,
901 p_effective_date => p_effective_date,
902 p_object_version_number => p_rec.object_version_number);
903 --
904 chk_flag_and_val
905 (p_flag => p_rec.no_mn_prtt_num_apls_flag,
906 p_val => p_rec.mn_prtt_num,
907 p_msg => 'BEN_91695_MIN_VAL_FLAG_EXCLSV');
908 --
909 chk_flag_and_val
910 (p_flag => p_rec.no_mx_prtt_num_apls_flag,
911 p_val => p_rec.mx_prtt_num,
912 p_msg => 'BEN_91696_MAX_VAL_FLAG_EXCLSV');
913 --
914 chk_mn_mx_prtt_num
915 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
916 p_no_mn_prtt_num_apls_flag => p_rec.no_mn_prtt_num_apls_flag,
917 p_mn_prtt_num => p_rec.mn_prtt_num,
918 p_no_mx_prtt_num_apls_flag => p_rec.no_mx_prtt_num_apls_flag,
919 p_mx_prtt_num => p_rec.mx_prtt_num,
920 p_object_version_number => p_rec.object_version_number);
921 --
922 chk_duplicate_ordr_num
923 (p_elig_TTL_PRTT_prte_id => p_rec.elig_TTL_PRTT_prte_id,
924 p_eligy_prfl_id => p_rec.eligy_prfl_id,
925 p_ordr_num => p_rec.ordr_num,
926 p_business_group_id => p_rec.business_group_id);
927 --
928 hr_utility.set_location(' Leaving:'||l_proc, 10);
929 End insert_validate;
930 --
931 -- ----------------------------------------------------------------------------
932 -- |---------------------------< update_validate >----------------------------|
933 -- ----------------------------------------------------------------------------
934 Procedure update_validate
935 (p_rec in ben_etp_shd.g_rec_type,
936 p_effective_date in date,
937 p_datetrack_mode in varchar2,
938 p_validation_start_date in date,
939 p_validation_end_date in date) is
940 --
941 l_proc varchar2(72) := g_package||'update_validate';
942 --
943 Begin
947 --
944 hr_utility.set_location('Entering:'||l_proc, 5);
945 --
946 -- Call all supporting business operations
948 --
949 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
950 --
951 chk_ELIG_TTL_PRTT_PRTE_id
952 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
953 p_effective_date => p_effective_date,
954 p_object_version_number => p_rec.object_version_number);
955 --
956 chk_prtt_det_rl
957 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
958 p_prtt_det_rl => p_rec.prtt_det_rl,
959 p_effective_date => p_effective_date,
960 p_object_version_number => p_rec.object_version_number);
961 --
962 chk_prtt_det_cd
963 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
964 p_prtt_det_cd => p_rec.prtt_det_cd,
965 p_effective_date => p_effective_date,
966 p_object_version_number => p_rec.object_version_number);
967 --
968 chk_no_mx_prtt_num_apls_flag
969 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
970 p_no_mx_prtt_num_apls_flag => p_rec.no_mx_prtt_num_apls_flag,
971 p_effective_date => p_effective_date,
972 p_object_version_number => p_rec.object_version_number);
973 --
974 chk_no_mn_prtt_num_apls_flag
975 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
976 p_no_mn_prtt_num_apls_flag => p_rec.no_mn_prtt_num_apls_flag,
977 p_effective_date => p_effective_date,
978 p_object_version_number => p_rec.object_version_number);
979 --
980 chk_excld_flag
981 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
982 p_excld_flag => p_rec.excld_flag,
983 p_effective_date => p_effective_date,
984 p_object_version_number => p_rec.object_version_number);
985 --
986 chk_flag_and_val
987 (p_flag => p_rec.no_mn_prtt_num_apls_flag,
988 p_val => p_rec.mn_prtt_num,
989 p_msg => 'BEN_91695_MIN_VAL_FLAG_EXCLSV');
990 --
991 chk_flag_and_val
992 (p_flag => p_rec.no_mx_prtt_num_apls_flag,
993 p_val => p_rec.mx_prtt_num,
994 p_msg => 'BEN_91696_MAX_VAL_FLAG_EXCLSV');
995 --
996 chk_mn_mx_prtt_num
997 (p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id,
998 p_no_mn_prtt_num_apls_flag => p_rec.no_mn_prtt_num_apls_flag,
999 p_mn_prtt_num => p_rec.mn_prtt_num,
1000 p_no_mx_prtt_num_apls_flag => p_rec.no_mx_prtt_num_apls_flag,
1001 p_mx_prtt_num => p_rec.mx_prtt_num,
1002 p_object_version_number => p_rec.object_version_number);
1003 --
1004 chk_duplicate_ordr_num
1005 (p_elig_TTL_PRTT_prte_id => p_rec.elig_TTL_PRTT_prte_id,
1006 p_eligy_prfl_id => p_rec.eligy_prfl_id,
1007 p_ordr_num => p_rec.ordr_num,
1008 p_business_group_id => p_rec.business_group_id);
1009 --
1010 -- Call the datetrack update integrity operation
1011 --
1012 dt_update_validate
1013 (p_eligy_prfl_id => p_rec.eligy_prfl_id,
1014 p_datetrack_mode => p_datetrack_mode,
1015 p_validation_start_date => p_validation_start_date,
1016 p_validation_end_date => p_validation_end_date);
1017 --
1018 hr_utility.set_location(' Leaving:'||l_proc, 10);
1019 End update_validate;
1020 --
1021 -- ----------------------------------------------------------------------------
1022 -- |---------------------------< delete_validate >----------------------------|
1023 -- ----------------------------------------------------------------------------
1024 Procedure delete_validate
1025 (p_rec in ben_etp_shd.g_rec_type,
1026 p_effective_date in date,
1027 p_datetrack_mode in varchar2,
1028 p_validation_start_date in date,
1029 p_validation_end_date in date) is
1030 --
1031 l_proc varchar2(72) := g_package||'delete_validate';
1032 --
1033 Begin
1034 hr_utility.set_location('Entering:'||l_proc, 5);
1035 --
1036 -- Call all supporting business operations
1037 --
1038 dt_delete_validate
1039 (p_datetrack_mode => p_datetrack_mode,
1040 p_validation_start_date => p_validation_start_date,
1041 p_validation_end_date => p_validation_end_date,
1042 p_ELIG_TTL_PRTT_PRTE_id => p_rec.ELIG_TTL_PRTT_PRTE_id);
1043 --
1044 hr_utility.set_location(' Leaving:'||l_proc, 10);
1045 End delete_validate;
1046 --
1047 --
1048 -- ---------------------------------------------------------------------------
1049 -- |---------------------< return_legislation_code >-------------------------|
1050 -- ---------------------------------------------------------------------------
1051 --
1052 function return_legislation_code
1053 (p_ELIG_TTL_PRTT_PRTE_id in number) return varchar2 is
1054 --
1055 -- Declare cursor
1056 --
1057 cursor csr_leg_code is
1058 select a.legislation_code
1059 from per_business_groups a,
1060 ben_ELIG_TTL_PRTT_PRTE_f b
1061 where b.ELIG_TTL_PRTT_PRTE_id = p_ELIG_TTL_PRTT_PRTE_id
1062 and a.business_group_id = b.business_group_id;
1063 --
1064 -- Declare local variables
1065 --
1066 l_legislation_code varchar2(150);
1067 l_proc varchar2(72) := g_package||'return_legislation_code';
1068 --
1069 begin
1070 --
1074 --
1071 hr_utility.set_location('Entering:'|| l_proc, 10);
1072 --
1073 -- Ensure that all the mandatory parameter are not null
1075 hr_api.mandatory_arg_error(p_api_name => l_proc,
1076 p_argument => 'ELIG_TTL_PRTT_PRTE_id',
1077 p_argument_value => p_ELIG_TTL_PRTT_PRTE_id);
1078 --
1079 open csr_leg_code;
1080 --
1081 fetch csr_leg_code into l_legislation_code;
1082 --
1083 if csr_leg_code%notfound then
1084 --
1085 close csr_leg_code;
1086 --
1087 -- The primary key is invalid therefore we must error
1088 --
1089 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1090 fnd_message.raise_error;
1091 --
1092 end if;
1093 --
1094 close csr_leg_code;
1095 --
1096 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1097 --
1098 return l_legislation_code;
1099 --
1100 end return_legislation_code;
1101 --
1102 end ben_etp_bus;