[Home] [Help]
PACKAGE BODY: APPS.BEN_PEO_BUS
Source
1 Package Body ben_peo_bus as
2 /* $Header: bepeorhi.pkb 120.0 2005/05/28 10:38:09 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_peo_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-------------------------< chk_elig_to_prte_rsn_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_to_prte_rsn_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_to_prte_rsn_id(p_elig_to_prte_rsn_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_to_prte_rsn_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_peo_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_elig_to_prte_rsn_id,hr_api.g_number)
55 <> ben_peo_shd.g_old_rec.elig_to_prte_rsn_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_peo_shd.constraint_error('BEN_ELIG_TO_PRTE_RSN_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_elig_to_prte_rsn_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_peo_shd.constraint_error('BEN_ELIG_TO_PRTE_RSN_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_elig_to_prte_rsn_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |-------------------------< chk_ignr_prtn_ovrid_flag >---------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that the lookup value is valid.
85 --
86 -- Pre Conditions
87 -- None.
88 --
89 -- In Parameters
90 -- elig_to_prte_rsn_id PK of record being inserted or updated.
91 -- ignr_prtn_ovrid_flag Value of lookup code.
92 -- effective_date effective date
93 -- object_version_number Object version number of record being
94 -- inserted or updated.
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Error handled by procedure
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_ignr_prtn_ovrid_flag(p_elig_to_prte_rsn_id in number,
109 --
106 p_ignr_prtn_ovrid_flag in varchar2,
107 p_effective_date in date,
108 p_object_version_number in number) is
110 l_proc varchar2(72) := g_package||'chk_ignr_prtn_ovrid_flag';
111 l_api_updating boolean;
112 --
113 Begin
114 --
115 hr_utility.set_location('Entering:'||l_proc, 5);
116 --
117 l_api_updating := ben_peo_shd.api_updating
118 (p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
119 p_effective_date => p_effective_date,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and p_ignr_prtn_ovrid_flag
124 <> nvl(ben_peo_shd.g_old_rec.ignr_prtn_ovrid_flag,hr_api.g_varchar2)
125 or not l_api_updating)
126 and p_ignr_prtn_ovrid_flag is not null then
127 --
128 -- check if value of lookup falls within lookup type.
129 --
130 if hr_api.not_exists_in_hr_lookups
131 (p_lookup_type => 'YES_NO',
132 p_lookup_code => p_ignr_prtn_ovrid_flag,
133 p_effective_date => p_effective_date) then
134 --
135 -- raise error as does not exist as lookup
136 --
137 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
138 fnd_message.set_token('FIELD','p_ignr_prtn_ovrid_flag');
139 fnd_message.set_token('TYPE','YES_NO');
140 fnd_message.raise_error;
141 --
142 end if;
143 --
144 end if;
145 --
146 hr_utility.set_location('Leaving:'||l_proc,10);
147 --
148 end chk_ignr_prtn_ovrid_flag;
149 --
150 -- ----------------------------------------------------------------------------
151 -- |-------------------------< chk_prtn_ovridbl_flag >------------------------|
152 -- ----------------------------------------------------------------------------
153 --
154 -- Description
155 -- This procedure is used to check that the lookup value is valid.
156 --
157 -- Pre Conditions
158 -- None.
159 --
160 -- In Parameters
161 -- elig_to_prte_rsn_id PK of record being inserted or updated.
162 -- prtn_ovridbl_flag Value of lookup code.
163 -- effective_date effective date
164 -- object_version_number Object version number of record being
165 -- inserted or updated.
166 --
167 -- Post Success
168 -- Processing continues
169 --
170 -- Post Failure
171 -- Error handled by procedure
172 --
173 -- Access Status
174 -- Internal table handler use only.
175 --
176 Procedure chk_prtn_ovridbl_flag (p_elig_to_prte_rsn_id in number,
177 p_prtn_ovridbl_flag in varchar2,
178 p_effective_date in date,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_prtn_ovridbl_flag';
182 l_api_updating boolean;
183 --
184 Begin
185 --
186 hr_utility.set_location('Entering:'||l_proc, 5);
187 --
188 l_api_updating := ben_peo_shd.api_updating
189 (p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
190 p_effective_date => p_effective_date,
191 p_object_version_number => p_object_version_number);
192 --
193 if (l_api_updating
194 and p_prtn_ovridbl_flag
195 <> nvl(ben_peo_shd.g_old_rec.prtn_ovridbl_flag,hr_api.g_varchar2)
196 or not l_api_updating)
197 and p_prtn_ovridbl_flag is not null then
198 --
199 -- check if value of lookup falls within lookup type.
200 --
201 if hr_api.not_exists_in_hr_lookups
202 (p_lookup_type => 'YES_NO',
203 p_lookup_code => p_prtn_ovridbl_flag,
204 p_effective_date => p_effective_date) then
205 --
206 -- raise error as does not exist as lookup
207 --
208 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
209 fnd_message.set_token('FIELD','p_prtn_ovridbl_flag');
210 fnd_message.set_token('TYPE','YES_NO');
211 fnd_message.raise_error;
212 --
213 end if;
214 --
215 end if;
216 --
217 hr_utility.set_location('Leaving:'||l_proc,10);
218 --
219 end chk_prtn_ovridbl_flag;
220 --
221 -- ----------------------------------------------------------------------------
222 -- |-----------------------< chk_elig_inelig_cd >-----------------------------|
223 -- ----------------------------------------------------------------------------
224 --
225 -- Description
226 -- This procedure is used to check that the lookup value is valid.
227 --
228 -- Pre Conditions
229 -- None.
230 --
231 -- In Parameters
232 -- elig_to_prte_rsn_id PK of record being inserted or updated.
233 -- elig_inelig_cd Value of lookup code.
234 -- effective_date effective date
235 -- object_version_number Object version number of record being
236 -- inserted or updated.
237 --
238 -- Post Success
239 -- Processing continues
240 --
241 -- Post Failure
242 -- Error handled by procedure
243 --
244 -- Access Status
245 -- Internal table handler use only.
246 --
247 Procedure chk_vrfy_fmly_mmbr_cd(p_elig_to_prte_rsn_id in number,
248 p_vrfy_fmly_mmbr_cd in varchar2,
249 p_effective_date in date,
250 p_object_version_number in number) is
251 --
252 l_proc varchar2(72) := g_package||'chk_vrfy_fmly_mmbr_cd';
256 --
253 l_api_updating boolean;
254 --
255 Begin
257 hr_utility.set_location('Entering:'||l_proc, 5);
258 --
259 l_api_updating := ben_peo_shd.api_updating
260 (p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
261 p_effective_date => p_effective_date,
262 p_object_version_number => p_object_version_number);
263 --
264 if (l_api_updating
265 and p_vrfy_fmly_mmbr_cd
266 <> nvl(ben_peo_shd.g_old_rec.vrfy_fmly_mmbr_cd,hr_api.g_varchar2)
267 or not l_api_updating)
268 and p_vrfy_fmly_mmbr_cd is not null
269 then
270 --
271 -- check if value of lookup falls within lookup type.
272 --
273
274 if hr_api.not_exists_in_hr_lookups
275 (p_lookup_type => 'BEN_FMLY_MMBR',
276 p_lookup_code => p_vrfy_fmly_mmbr_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_vrfy_fmly_mmbr_cd');
283 fnd_message.set_token('TYPE','BEN_FMLY_MMBR');
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_vrfy_fmly_mmbr_cd;
293 --
294 -- ----------------------------------------------------------------------------
295 -- |-----------------------< chk_elig_inelig_cd >-----------------------------|
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_to_prte_rsn_id PK of record being inserted or updated.
306 -- elig_inelig_cd 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_elig_inelig_cd(p_elig_to_prte_rsn_id in number,
321 p_elig_inelig_cd 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_elig_inelig_cd';
326 l_api_updating boolean;
327 --
328 Begin
329 --
330 hr_utility.set_location('Entering:'||l_proc, 5);
331 --
332 l_api_updating := ben_peo_shd.api_updating
333 (p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_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_elig_inelig_cd
339 <> nvl(ben_peo_shd.g_old_rec.elig_inelig_cd,hr_api.g_varchar2)
340 or not l_api_updating)
341 and p_elig_inelig_cd is not null
342 then
343 --
344 -- check if value of lookup falls within lookup type.
345 --
346
347 if hr_api.not_exists_in_hr_lookups
348 (p_lookup_type => 'BEN_ELIG_INELIG',
349 p_lookup_code => p_elig_inelig_cd,
350 p_effective_date => p_effective_date) then
351 --
352 -- raise error as does not exist as lookup
353 --
354 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
355 fnd_message.set_token('FIELD', 'p_elig_inelig_cd');
356 fnd_message.set_token('TYPE','BEN_ELIG_INELIG');
357 fnd_message.raise_error;
358 --
359 end if;
360 --
361 end if;
362 --
363 hr_utility.set_location('Leaving:'||l_proc,10);
364 --
365 end chk_elig_inelig_cd;
366 --
367 -- ----------------------------------------------------------------------------
368 -- |-----------------------< chk_prtn_eff_strt_dt_cd >------------------------|
369 -- ----------------------------------------------------------------------------
370 --
371 -- Description
372 -- This procedure is used to check that the lookup value is valid.
373 --
374 -- Pre Conditions
375 -- None.
376 --
377 -- In Parameters
378 -- elig_to_prte_rsn_id PK of record being inserted or updated.
379 -- prtn_eff_strt_dt_cd Value of lookup code.
380 -- effective_date effective date
381 -- object_version_number Object version number of record being
382 -- inserted or updated.
383 --
384 -- Post Success
385 -- Processing continues
386 --
387 -- Post Failure
388 -- Error handled by procedure
389 --
390 -- Access Status
391 -- Internal table handler use only.
392 --
393 Procedure chk_prtn_eff_strt_dt_cd(p_elig_to_prte_rsn_id in number,
394 p_prtn_eff_strt_dt_cd in varchar2,
395 p_effective_date in date,
396 p_object_version_number in number) is
397 --
398 l_proc varchar2(72) := g_package||'chk_prtn_eff_strt_dt_cd';
399 l_api_updating boolean;
400 --
401 Begin
402 --
403 hr_utility.set_location('Entering:'||l_proc, 5);
404 --
405 l_api_updating := ben_peo_shd.api_updating
409 --
406 (p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
407 p_effective_date => p_effective_date,
408 p_object_version_number => p_object_version_number);
410 if (l_api_updating
411 and p_prtn_eff_strt_dt_cd
412 <> nvl(ben_peo_shd.g_old_rec.prtn_eff_strt_dt_cd,hr_api.g_varchar2)
413 or not l_api_updating)
414 and p_prtn_eff_strt_dt_cd is not null
415 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 => 'BEN_PRTN_ELIG_STRT',
422 p_lookup_code => p_prtn_eff_strt_dt_cd,
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_prtn_eff_strt_dt_cd');
429 fnd_message.set_token('TYPE','BEN_PRTN_ELIG_STRT');
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_prtn_eff_strt_dt_cd;
439 --
440 -- ----------------------------------------------------------------------------
441 -- |----------------------< chk_prtn_eff_strt_dt_rl >-------------------------|
442 -- ----------------------------------------------------------------------------
443 --
444 -- Description
445 -- This procedure is used to check that the Formula Rule is valid.
446 --
447 -- Pre Conditions
448 -- None.
449 --
450 -- In Parameters
451 -- elig_to_prte_rsn_id PK of record being inserted or updated.
452 -- prtn_eff_strt_dt_rl Value of formula rule id.
453 -- effective_date effective date
454 -- object_version_number Object version number of record being
455 -- inserted or updated.
456 --
457 -- Post Success
458 -- Processing continues
459 --
460 -- Post Failure
461 -- Error handled by procedure
462 --
463 -- Access Status
464 -- Internal table handler use only.
465 --
466 Procedure chk_vrfy_fmly_mmbr_rl
467 (p_elig_to_prte_rsn_id in number
468 ,p_vrfy_fmly_mmbr_rl in number
469 ,p_business_group_id in number
470 ,p_effective_date in date
471 ,p_object_version_number in number)
472 is
473 --
474 l_proc varchar2(72) := g_package||'chk_vrfy_fmly_mmbr_rl';
475 l_api_updating boolean;
476 --
477 Begin
478 --
479 hr_utility.set_location('Entering:'||l_proc, 5);
480 --
481 l_api_updating := ben_peo_shd.api_updating
482 (p_effective_date => p_effective_date,
483 p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
484 p_object_version_number => p_object_version_number);
485 --
486 if (l_api_updating
487 and nvl(p_vrfy_fmly_mmbr_rl,hr_api.g_number)
488 <> ben_peo_shd.g_old_rec.vrfy_fmly_mmbr_rl
489 or not l_api_updating)
490 and p_vrfy_fmly_mmbr_rl is not null then
491 --
492 -- check if value of formula rule is valid.
493 --
494 if not benutils.formula_exists
495 (p_formula_id => p_vrfy_fmly_mmbr_rl,
496 p_formula_type_id => -21,
497 p_business_group_id => p_business_group_id,
498 p_effective_date => p_effective_date) then
499 --
500 -- raise error
501 --
502 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
503 fnd_message.set_token('ID',p_vrfy_fmly_mmbr_rl);
504 fnd_message.set_token('TYPE_ID',-21);
505 fnd_message.raise_error;
506 --
507 end if;
508 --
509 end if;
510 --
511 hr_utility.set_location('Leaving:'||l_proc,10);
512 --
513 end chk_vrfy_fmly_mmbr_rl;
514 --
515 -- ----------------------------------------------------------------------------
516 -- |----------------------< chk_prtn_eff_strt_dt_rl >-------------------------|
517 -- ----------------------------------------------------------------------------
518 --
519 -- Description
520 -- This procedure is used to check that the Formula Rule is valid.
521 --
522 -- Pre Conditions
523 -- None.
524 --
525 -- In Parameters
526 -- elig_to_prte_rsn_id PK of record being inserted or updated.
527 -- prtn_eff_strt_dt_rl Value of formula rule id.
528 -- effective_date effective date
529 -- object_version_number Object version number of record being
530 -- inserted or updated.
531 --
532 -- Post Success
533 -- Processing continues
534 --
535 -- Post Failure
536 -- Error handled by procedure
537 --
538 -- Access Status
539 -- Internal table handler use only.
540 --
541 Procedure chk_prtn_eff_strt_dt_rl
542 (p_elig_to_prte_rsn_id in number
543 ,p_prtn_eff_strt_dt_rl in number
544 ,p_business_group_id in number
545 ,p_effective_date in date
546 ,p_object_version_number in number)
547 is
548 --
549 l_proc varchar2(72) := g_package||'chk_prtn_eff_strt_dt_rl';
550 l_api_updating boolean;
551 --
552 Begin
553 --
554 hr_utility.set_location('Entering:'||l_proc, 5);
555 --
556 l_api_updating := ben_peo_shd.api_updating
557 (p_effective_date => p_effective_date,
558 p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
562 and nvl(p_prtn_eff_strt_dt_rl,hr_api.g_number)
559 p_object_version_number => p_object_version_number);
560 --
561 if (l_api_updating
563 <> ben_peo_shd.g_old_rec.prtn_eff_strt_dt_rl
564 or not l_api_updating)
565 and p_prtn_eff_strt_dt_rl is not null then
566 --
567 -- check if value of formula rule is valid.
568 --
569 if not benutils.formula_exists
570 (p_formula_id => p_prtn_eff_strt_dt_rl,
571 p_formula_type_id => -82,
572 p_business_group_id => p_business_group_id,
573 p_effective_date => p_effective_date) then
574 --
575 -- raise error
576 --
577 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
578 fnd_message.set_token('ID',p_prtn_eff_strt_dt_rl);
579 fnd_message.set_token('TYPE_ID',-82);
580 fnd_message.raise_error;
581 --
582 end if;
583 --
584 end if;
585 --
586 hr_utility.set_location('Leaving:'||l_proc,10);
587 --
588 end chk_prtn_eff_strt_dt_rl;
589 --
590 -- ----------------------------------------------------------------------------
591 -- |-----------------------< chk_prtn_eff_end_dt_cd >------------------------|
592 -- ----------------------------------------------------------------------------
593 --
594 -- Description
595 -- This procedure is used to check that the lookup value is valid.
596 --
597 -- Pre Conditions
598 -- None.
599 --
600 -- In Parameters
601 -- elig_to_prte_rsn_id PK of record being inserted or updated.
602 -- prtn_eff_end_dt_cd Value of lookup code.
603 -- effective_date effective date
604 -- object_version_number Object version number of record being
605 -- inserted or updated.
606 --
607 -- Post Success
608 -- Processing continues
609 --
610 -- Post Failure
611 -- Error handled by procedure
612 --
613 -- Access Status
614 -- Internal table handler use only.
615 --
616 Procedure chk_prtn_eff_end_dt_cd(p_elig_to_prte_rsn_id in number,
617 p_prtn_eff_end_dt_cd in varchar2,
618 p_effective_date in date,
619 p_object_version_number in number) is
620 --
621 l_proc varchar2(72) := g_package||'chk_prtn_eff_end_dt_cd';
622 l_api_updating boolean;
623 --
624 Begin
625 --
626 hr_utility.set_location('Entering:'||l_proc, 5);
627 --
628 l_api_updating := ben_peo_shd.api_updating
629 (p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
630 p_effective_date => p_effective_date,
631 p_object_version_number => p_object_version_number);
632 --
633 if (l_api_updating
634 and p_prtn_eff_end_dt_cd
635 <> nvl(ben_peo_shd.g_old_rec.prtn_eff_end_dt_cd,hr_api.g_varchar2)
636 or not l_api_updating)
637 and p_prtn_eff_end_dt_cd is not null
638 then
639 --
640 -- check if value of lookup falls within lookup type.
641 --
642
643 if hr_api.not_exists_in_hr_lookups
644 (p_lookup_type => 'BEN_PRTN_ELIG_END',
645 p_lookup_code => p_prtn_eff_end_dt_cd,
646 p_effective_date => p_effective_date) then
647 --
648 -- raise error as does not exist as lookup
649 --
650 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
651 fnd_message.set_token('FIELD', 'p_prtn_eff_end_dt_cd');
652 fnd_message.set_token('TYPE','BEN_PRTN_ELIG_END');
653 fnd_message.raise_error;
654 --
655 end if;
656 --
657 end if;
658 --
659 hr_utility.set_location('Leaving:'||l_proc,10);
660 --
661 end chk_prtn_eff_end_dt_cd;
662 --
663 -- ----------------------------------------------------------------------------
664 -- |----------------------< chk_prtn_eff_end_dt_rl >-------------------------|
665 -- ----------------------------------------------------------------------------
666 --
667 -- Description
668 -- This procedure is used to check that the Formula Rule is valid.
669 --
670 -- Pre Conditions
671 -- None.
672 --
673 -- In Parameters
674 -- elig_to_prte_rsn_id PK of record being inserted or updated.
675 -- prtn_eff_end_dt_rl Value of formula rule id.
676 -- effective_date effective date
677 -- object_version_number Object version number of record being
678 -- inserted or updated.
679 --
680 -- Post Success
681 -- Processing continues
682 --
683 -- Post Failure
684 -- Error handled by procedure
685 --
686 -- Access Status
687 -- Internal table handler use only.
688 --
689 Procedure chk_prtn_eff_end_dt_rl(p_elig_to_prte_rsn_id in number,
690 p_prtn_eff_end_dt_rl in number,
691 p_business_group_id in number,
692 p_effective_date in date,
693 p_object_version_number in number) is
694 --
695 l_proc varchar2(72) := g_package||'chk_prtn_eff_end_dt_rl';
696 l_api_updating boolean;
697 --
698 Begin
699 --
700 hr_utility.set_location('Entering:'||l_proc, 5);
701 --
702 l_api_updating := ben_peo_shd.api_updating
703 (p_effective_date => p_effective_date,
704 p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
705 p_object_version_number => p_object_version_number);
706 --
710 or not l_api_updating)
707 if (l_api_updating
708 and nvl(p_prtn_eff_end_dt_rl,hr_api.g_number)
709 <> ben_peo_shd.g_old_rec.prtn_eff_end_dt_rl
711 and p_prtn_eff_end_dt_rl is not null then
712 --
713 -- check if value of formula rule is valid.
714 --
715 if not benutils.formula_exists
716 (p_formula_id => p_prtn_eff_end_dt_rl,
717 p_formula_type_id => -83,
718 p_business_group_id => p_business_group_id,
719 p_effective_date => p_effective_date) then
720 --
721 -- raise error
722 --
723 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
724 fnd_message.set_token('ID',p_prtn_eff_end_dt_rl);
725 fnd_message.set_token('TYPE_ID',-83);
726 fnd_message.raise_error;
727 --
728 end if;
729 --
730 end if;
731 --
732 hr_utility.set_location('Leaving:'||l_proc,10);
733 --
734 end chk_prtn_eff_end_dt_rl;
735 --
736 -- ----------------------------------------------------------------------------
737 -- |---------------------< chk_wait_perd_dt_to_use_cd >-----------------------|
738 -- ----------------------------------------------------------------------------
739 --
740 -- Description
741 -- This procedure is used to check that the lookup value is valid.
742 --
743 -- Pre Conditions
744 -- None.
745 --
746 -- In Parameters
747 -- elig_to_prte_rsn_id PK of record being inserted or updated.
748 -- wait_perd_dt_to_use_cd Value of lookup code.
749 -- effective_date effective date
750 -- object_version_number Object version number of record being
751 -- inserted or updated.
752 --
753 -- Post Success
754 -- Processing continues
755 --
756 -- Post Failure
757 -- Error handled by procedure
758 --
759 -- Access Status
760 -- Internal table handler use only.
761 --
762 Procedure chk_wait_perd_dt_to_use_cd
763 (p_elig_to_prte_rsn_id in number
764 ,p_wait_perd_dt_to_use_cd in varchar2
765 ,p_effective_date in date
766 ,p_object_version_number in number)
767 is
768 --
769 l_proc varchar2(72) := g_package || 'chk_wait_perd_dt_to_use_cd';
770 l_api_updating boolean;
771 --
772 Begin
773 --
774 hr_utility.set_location('Entering:'||l_proc, 5);
775 --
776 l_api_updating := ben_peo_shd.api_updating
777 (p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
778 p_effective_date => p_effective_date,
779 p_object_version_number => p_object_version_number);
780 --
781 if (l_api_updating
782 and p_wait_perd_dt_to_use_cd
783 <> nvl(ben_peo_shd.g_old_rec.wait_perd_dt_to_use_cd,hr_api.g_varchar2)
784 or not l_api_updating)
785 and p_wait_perd_dt_to_use_cd is not null
786 then
787 --
788 -- check if value of lookup falls within lookup type.
789 --
790
791 if hr_api.not_exists_in_hr_lookups
792 (p_lookup_type => 'BEN_MX_WTG_DT_TO_USE'
793 ,p_lookup_code => p_wait_perd_dt_to_use_cd
794 ,p_effective_date => p_effective_date)
795 then
796 --
797 -- raise error as does not exist as lookup
798 --
799 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
800 fnd_message.set_token('FIELD', 'p_wait_perd_dt_to_use_cd');
801 fnd_message.set_token('TYPE','BEN_MX_WTG_DT_TO_USE');
802 fnd_message.raise_error;
803 --
804 end if;
805 --
806 end if;
807 --
808 hr_utility.set_location('Leaving:'||l_proc,10);
809 --
810 end chk_wait_perd_dt_to_use_cd;
811 --
812
813 -- --------------------------------chk_cd_rl_combination >-------------------------------|
814 -- ----------------------------------------------------------------------------
815 --
816 -- Description
817 -- This procedure is used to check the code is RULE then the rule must be
818 -- defined else it should not be.
819 --
820 -- Pre Conditions
821 -- None.
822 --
823 -- In Parameters
824 -- p_vrfy_fmly_mmbr_cd in varchar2,
825 -- p_vrfy_fmly_mmbr_rl in number
826 --
827 -- Post Success
828 -- Processing continues
829 --
830 -- Post Failure
831 -- Error handled by procedure
832 --
833 -- Access Status
834 -- Internal table handler use only.
835 --
836 procedure chk_cd_rl_combination
837 (
838 p_vrfy_fmly_mmbr_cd in varchar2,
839 p_vrfy_fmly_mmbr_rl in number ) IS
840 l_proc varchar2(72) := g_package||'chk_cd_rl_combination';
841 BEGIN
842 hr_utility.set_location('Entering:'||l_proc, 5);
843 --
844 if ( p_vrfy_fmly_mmbr_cd <> 'RL' and p_vrfy_fmly_mmbr_rl is not null)
845 then
846 fnd_message.set_name('BEN','BEN_91730_NO_RULE');
847 fnd_message.raise_error;
848 end if;
849
850 if ( p_vrfy_fmly_mmbr_cd = 'RL' and p_vrfy_fmly_mmbr_rl is null)
851 then
852 fnd_message.set_name('BEN','BEN_91731_RULE');
853 fnd_message.raise_error;
854 end if;
855 --
856 --leaving Procedure.
857 --
858 hr_utility.set_location('Leaving:'||l_proc,10);
859 --
860 end chk_cd_rl_combination;
861
862 --
863 -- ----------------------------------------------------------------------------
864 -- ----------------------------------------------------------------------------
865 -- |---------------------< chk_wait_perd_dt_to_use_rl >-----------------------|
866 -- ----------------------------------------------------------------------------
870 --
867 --
868 -- Description
869 -- This procedure is used to check that the Formula Rule is valid.
871 -- Pre Conditions
872 -- None.
873 --
874 -- In Parameters
875 -- elig_to_prte_rsn_id PK of record being inserted or updated.
876 -- wait_perd_dt_to_userrl Value of formula rule id.
877 -- effective_date effective date
878 -- object_version_number Object version number of record being
879 -- inserted or updated.
880 --
881 -- Post Success
882 -- Processing continues
883 --
884 -- Post Failure
885 -- Error handled by procedure
886 --
887 -- Access Status
888 -- Internal table handler use only.
889 --
890 Procedure chk_wait_perd_dt_to_use_rl
891 (p_elig_to_prte_rsn_id in number
892 ,p_wait_perd_dt_to_use_rl in number
893 ,p_business_group_id in number
894 ,p_effective_date in date
895 ,p_object_version_number in number)
896 is
897 --
898 l_proc varchar2(72) := g_package || 'chk_wait_perd_dt_to_use_rl';
899 l_api_updating boolean;
900 --
901 Begin
902 --
903 hr_utility.set_location('Entering:'||l_proc, 5);
904 --
905 l_api_updating := ben_peo_shd.api_updating
906 (p_effective_date => p_effective_date,
907 p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
908 p_object_version_number => p_object_version_number);
909 --
910 if (l_api_updating
911 and nvl(p_wait_perd_dt_to_use_rl,hr_api.g_number)
912 <> ben_peo_shd.g_old_rec.wait_perd_dt_to_use_rl
913 or not l_api_updating)
914 and p_wait_perd_dt_to_use_rl is not null then
915 --
916 -- check if value of formula rule is valid.
917 --
918 if not benutils.formula_exists
919 (p_formula_id => p_wait_perd_dt_to_use_rl,
920 p_formula_type_id => -162,
921 p_business_group_id => p_business_group_id,
922 p_effective_date => p_effective_date) then
923 --
924 -- raise error
925 --
926 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
927 fnd_message.set_token('ID',p_wait_perd_dt_to_use_rl);
928 fnd_message.set_token('TYPE_ID',-162);
929 fnd_message.raise_error;
930 --
931 end if;
932 --
933 end if;
934 --
935 hr_utility.set_location('Leaving:'||l_proc,10);
936 --
937 end chk_wait_perd_dt_to_use_rl;
938 --
939 -- ----------------------------------------------------------------------------
940 -- |-----------------------< chk_wait_perd_uom >------------------------------|
941 -- ----------------------------------------------------------------------------
942 --
943 -- Description
944 -- This procedure is used to check that the lookup value is valid.
945 --
946 -- Pre Conditions
947 -- None.
948 --
949 -- In Parameters
950 -- elig_to_prte_rsn_id PK of record being inserted or updated.
951 -- wait_perd_uom Value of lookup code.
952 -- effective_date effective date
953 -- object_version_number Object version number of record being
954 -- inserted or updated.
955 --
956 -- Post Success
957 -- Processing continues
958 --
959 -- Post Failure
960 -- Error handled by procedure
961 --
962 -- Access Status
963 -- Internal table handler use only.
964 --
965 Procedure chk_wait_perd_uom(p_elig_to_prte_rsn_id in number,
966 p_wait_perd_uom in varchar2,
967 p_effective_date in date,
968 p_object_version_number in number) is
969 --
970 l_proc varchar2(72) := g_package || 'chk_wait_perd_uom';
971 l_api_updating boolean;
972 --
973 Begin
974 --
975 hr_utility.set_location('Entering:'||l_proc, 5);
976 --
977 l_api_updating := ben_peo_shd.api_updating
978 (p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
979 p_effective_date => p_effective_date,
980 p_object_version_number => p_object_version_number);
981 --
982 if (l_api_updating
983 and p_wait_perd_uom
984 <> nvl(ben_peo_shd.g_old_rec.wait_perd_uom,hr_api.g_varchar2)
985 or not l_api_updating)
986 and p_wait_perd_uom is not null then
987 --
988 -- check if value of lookup falls within lookup type.
989 --
990 if hr_api.not_exists_in_hr_lookups
991 (p_lookup_type => 'BEN_TM_UOM',
992 p_lookup_code => p_wait_perd_uom,
993 p_effective_date => p_effective_date) then
994 --
995 -- raise error as does not exist as lookup
996 --
997 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
998 fnd_message.set_token('FIELD', 'p_wait_perd_uom');
999 fnd_message.set_token('TYPE','BEN_TM_UOM');
1000 fnd_message.raise_error;
1001 --
1002 end if;
1003 --
1004 end if;
1005 --
1006 hr_utility.set_location('Leaving:'||l_proc,10);
1007 --
1008 end chk_wait_perd_uom;
1009 --
1010 -- ----------------------------------------------------------------------------
1011 -- |-------------------------< chk_wait_perd_rl >-----------------------------|
1012 -- ----------------------------------------------------------------------------
1013 --
1014 -- Description
1015 -- This procedure is used to check that the Formula Rule is valid.
1016 --
1017 -- Pre Conditions
1018 -- None.
1019 --
1020 -- In Parameters
1021 -- elig_to_prte_rsn_id PK of record being inserted or updated.
1025 -- inserted or updated.
1022 -- wait_perd_dt_to_userrl Value of formula rule id.
1023 -- effective_date effective date
1024 -- object_version_number Object version number of record being
1026 --
1027 -- Post Success
1028 -- Processing continues
1029 --
1030 -- Post Failure
1031 -- Error handled by procedure
1032 --
1033 -- Access Status
1034 -- Internal table handler use only.
1035 --
1036 Procedure chk_wait_perd_rl
1037 (p_elig_to_prte_rsn_id in number
1038 ,p_wait_perd_rl in number
1039 ,p_business_group_id in number
1040 ,p_effective_date in date
1041 ,p_object_version_number in number)
1042 is
1043 --
1044 l_proc varchar2(72) := g_package || 'chk_wait_perd_rl';
1045 l_api_updating boolean;
1046 --
1047 Begin
1048 --
1049 hr_utility.set_location('Entering:'||l_proc, 5);
1050 --
1051 l_api_updating := ben_peo_shd.api_updating
1052 (p_effective_date => p_effective_date,
1053 p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
1054 p_object_version_number => p_object_version_number);
1055 --
1056 if (l_api_updating
1057 and nvl(p_wait_perd_rl,hr_api.g_number)
1058 <> ben_peo_shd.g_old_rec.wait_perd_rl
1059 or not l_api_updating)
1060 and p_wait_perd_rl is not null then
1061 --
1062 -- check if value of formula rule is valid.
1063 --
1064 if not benutils.formula_exists
1065 (p_formula_id => p_wait_perd_rl,
1066 p_formula_type_id => -518,
1067 p_business_group_id => p_business_group_id,
1068 p_effective_date => p_effective_date) then
1069 --
1070 -- raise error
1071 --
1072 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1073 fnd_message.set_token('ID',p_wait_perd_rl);
1074 fnd_message.set_token('TYPE_ID',-518);
1075 fnd_message.raise_error;
1076 --
1077 end if;
1078 --
1079 end if;
1080 --
1081 hr_utility.set_location('Leaving:'||l_proc,10);
1082 --
1083 end chk_wait_perd_rl;
1084 --
1085 -- ----------------------------------------------------------------------------
1086 -- |-------------------------< chk_mx_poe_uom >-------------------------------|
1087 -- ----------------------------------------------------------------------------
1088 --
1089 -- Description
1090 -- This procedure is used to check that the lookup value is valid.
1091 --
1092 -- Pre Conditions
1093 -- None.
1094 --
1095 -- In Parameters
1096 -- elig_to_prte_rsn_id PK of record being inserted or updated.
1097 -- mx_poe_uom Value of lookup code.
1098 -- effective_date effective date
1099 -- object_version_number Object version number of record being
1100 -- inserted or updated.
1101 --
1102 -- Post Success
1103 -- Processing continues
1104 --
1105 -- Post Failure
1106 -- Error handled by procedure
1107 --
1108 -- Access Status
1109 -- Internal table handler use only.
1110 --
1111 Procedure chk_mx_poe_uom
1112 (p_elig_to_prte_rsn_id in number
1113 ,p_mx_poe_uom in varchar2
1114 ,p_effective_date in date
1115 ,p_object_version_number in number)
1116 is
1117 --
1118 l_proc varchar2(72) := g_package || 'chk_mx_poe_uom';
1119 l_api_updating boolean;
1120 --
1121 Begin
1122 --
1123 hr_utility.set_location('Entering:'||l_proc, 5);
1124 --
1125 l_api_updating := ben_peo_shd.api_updating
1126 (p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
1127 p_effective_date => p_effective_date,
1128 p_object_version_number => p_object_version_number);
1129 --
1130 if (l_api_updating
1131 and p_mx_poe_uom
1132 <> nvl(ben_peo_shd.g_old_rec.mx_poe_uom,hr_api.g_varchar2)
1133 or not l_api_updating)
1134 and p_mx_poe_uom is not null then
1135 --
1136 -- check if value of lookup falls within lookup type.
1137 --
1138 if hr_api.not_exists_in_hr_lookups
1139 (p_lookup_type => 'BEN_RQD_PERD_ENRT_NENRT_TM_UOM',
1140 p_lookup_code => p_mx_poe_uom,
1141 p_effective_date => p_effective_date) then
1142 --
1143 -- raise error as does not exist as lookup
1144 --
1145 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1146 fnd_message.set_token('FIELD', 'p_mx_poe_uom');
1147 fnd_message.set_token('TYPE','BEN_RQD_PERD_ENRT_NENRT_TM_UOM');
1148 fnd_message.raise_error;
1149 --
1150 end if;
1151 --
1152 end if;
1153 --
1154 hr_utility.set_location('Leaving:'||l_proc,10);
1155 --
1156 end chk_mx_poe_uom;
1157 --
1158 -- ----------------------------------------------------------------------------
1159 -- |-----------------------< chk_mx_poe_det_dt_rl >---------------------------|
1160 -- ----------------------------------------------------------------------------
1161 --
1162 -- Description
1163 -- This procedure is used to check that the Formula Rule is valid.
1164 --
1165 -- Pre Conditions
1166 -- None.
1167 --
1168 -- In Parameters
1169 -- elig_to_prte_rsn_id PK of record being inserted or updated.
1170 -- mx_poe_det_dt_rl Value of formula rule id.
1171 -- effective_date effective date
1172 -- object_version_number Object version number of record being
1173 -- inserted or updated.
1174 --
1175 -- Post Success
1176 -- Processing continues
1177 --
1178 -- Post Failure
1182 -- Internal table handler use only.
1179 -- Error handled by procedure
1180 --
1181 -- Access Status
1183 --
1184 Procedure chk_mx_poe_det_dt_rl
1185 (p_elig_to_prte_rsn_id in number
1186 ,p_mx_poe_det_dt_rl in number
1187 ,p_business_group_id in number
1188 ,p_effective_date in date
1189 ,p_object_version_number in number)
1190 is
1191 --
1192 l_proc varchar2(72) := g_package || 'chk_mx_poe_det_dt_rl';
1193 l_api_updating boolean;
1194 --
1195 Begin
1196 --
1197 hr_utility.set_location('Entering:'||l_proc, 5);
1198 --
1199 l_api_updating := ben_peo_shd.api_updating
1200 (p_effective_date => p_effective_date,
1201 p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
1202 p_object_version_number => p_object_version_number);
1203 --
1204 if (l_api_updating
1205 and nvl(p_mx_poe_det_dt_rl,hr_api.g_number)
1206 <> ben_peo_shd.g_old_rec.mx_poe_det_dt_rl
1207 or not l_api_updating)
1208 and p_mx_poe_det_dt_rl is not null then
1209 --
1210 -- check if value of formula rule is valid.
1211 --
1212 if not benutils.formula_exists
1213 (p_formula_id => p_mx_poe_det_dt_rl,
1214 p_formula_type_id => -527,
1215 p_business_group_id => p_business_group_id,
1216 p_effective_date => p_effective_date) then
1217 --
1218 -- raise error
1219 --
1220 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1221 fnd_message.set_token('ID',p_mx_poe_det_dt_rl);
1222 fnd_message.set_token('TYPE_ID',-527);
1223 fnd_message.raise_error;
1224 --
1225 end if;
1226 --
1227 end if;
1228 --
1229 hr_utility.set_location('Leaving:'||l_proc,10);
1230 --
1231 end chk_mx_poe_det_dt_rl;
1232 --
1233 -- ----------------------------------------------------------------------------
1234 -- |---------------------------< chk_mx_poe_rl >------------------------------|
1235 -- ----------------------------------------------------------------------------
1236 --
1237 -- Description
1238 -- This procedure is used to check that the Formula Rule is valid.
1239 --
1240 -- Pre Conditions
1241 -- None.
1242 --
1243 -- In Parameters
1244 -- elig_to_prte_rsn_id PK of record being inserted or updated.
1245 -- mx_poe_rl Value of formula rule id.
1246 -- effective_date effective date
1247 -- object_version_number Object version number of record being
1248 -- inserted or updated.
1249 --
1250 -- Post Success
1251 -- Processing continues
1252 --
1253 -- Post Failure
1254 -- Error handled by procedure
1255 --
1256 -- Access Status
1257 -- Internal table handler use only.
1258 --
1259 Procedure chk_mx_poe_rl
1260 (p_elig_to_prte_rsn_id in number
1261 ,p_mx_poe_rl in number
1262 ,p_business_group_id in number
1263 ,p_effective_date in date
1264 ,p_object_version_number in number)
1265 is
1266 --
1267 l_proc varchar2(72) := g_package || 'chk_mx_poe_rl';
1268 l_api_updating boolean;
1269 --
1270 Begin
1271 --
1272 hr_utility.set_location('Entering:'||l_proc, 5);
1273 --
1274 l_api_updating := ben_peo_shd.api_updating
1275 (p_effective_date => p_effective_date,
1276 p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
1277 p_object_version_number => p_object_version_number);
1278 --
1279 if (l_api_updating
1280 and nvl(p_mx_poe_rl,hr_api.g_number)
1281 <> ben_peo_shd.g_old_rec.mx_poe_rl
1282 or not l_api_updating)
1283 and p_mx_poe_rl is not null then
1284 --
1285 -- check if value of formula rule is valid.
1286 --
1287 if not benutils.formula_exists
1288 (p_formula_id => p_mx_poe_rl,
1289 p_formula_type_id => -526,
1290 p_business_group_id => p_business_group_id,
1291 p_effective_date => p_effective_date) then
1292 --
1293 -- raise error
1294 --
1295 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1296 fnd_message.set_token('ID',p_mx_poe_rl);
1297 fnd_message.set_token('TYPE_ID',-526);
1298 fnd_message.raise_error;
1299 --
1300 end if;
1301 --
1302 --
1303 end if;
1304 --
1305 hr_utility.set_location('Leaving:'||l_proc,10);
1306 --
1307 end chk_mx_poe_rl;
1308 --
1309 -- ----------------------------------------------------------------------------
1310 -- |----------------------< chk_mx_poe_det_dt_cd >----------------------------|
1311 -- ----------------------------------------------------------------------------
1312 --
1313 -- Description
1314 -- This procedure is used to check that the lookup value is valid.
1315 --
1316 -- Pre Conditions
1317 -- None.
1318 --
1319 -- In Parameters
1320 -- elig_to_prte_rsn_id PK of record being inserted or updated.
1321 -- mx_poe_det_dt_cd Value of lookup code.
1322 -- effective_date effective date
1323 -- object_version_number Object version number of record being
1324 -- inserted or updated.
1325 --
1326 -- Post Success
1327 -- Processing continues
1328 --
1329 -- Post Failure
1330 -- Error handled by procedure
1331 --
1332 -- Access Status
1333 -- Internal table handler use only.
1334 --
1335 Procedure chk_mx_poe_det_dt_cd
1336 (p_elig_to_prte_rsn_id in number
1337 ,p_mx_poe_det_dt_cd in varchar2
1338 ,p_effective_date in date
1339 ,p_object_version_number in number)
1343 l_api_updating boolean;
1340 is
1341 --
1342 l_proc varchar2(72) := g_package || 'chk_mx_poe_det_dt_cd';
1344 --
1345 Begin
1346 --
1347 hr_utility.set_location('Entering:'||l_proc, 5);
1348 --
1349 l_api_updating := ben_peo_shd.api_updating
1350 (p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
1351 p_effective_date => p_effective_date,
1352 p_object_version_number => p_object_version_number);
1353 --
1354 if (l_api_updating
1355 and p_mx_poe_det_dt_cd
1356 <> nvl(ben_peo_shd.g_old_rec.mx_poe_det_dt_cd,hr_api.g_varchar2)
1357 or not l_api_updating)
1358 and p_mx_poe_det_dt_cd is not null then
1359 --
1360 -- check if value of lookup falls within lookup type.
1361 --
1362 if hr_api.not_exists_in_hr_lookups
1363 (p_lookup_type => 'BEN_MX_POE_DET_DT',
1364 p_lookup_code => p_mx_poe_det_dt_cd,
1365 p_effective_date => p_effective_date) then
1366 --
1367 -- raise error as does not exist as lookup
1368 --
1369 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1370 fnd_message.set_token('FIELD', 'p_mx_poe_det_dt_cd');
1371 fnd_message.set_token('TYPE','BEN_MX_POE_DET_DT');
1372 fnd_message.raise_error;
1373 --
1374 end if;
1375 --
1376 end if;
1377 --
1378 hr_utility.set_location('Leaving:'||l_proc,10);
1379 --
1380 end chk_mx_poe_det_dt_cd;
1381 --
1382 -- ----------------------------------------------------------------------------
1383 -- |----------------------< chk_mx_poe_apls_cd >----------------------------|
1384 -- ----------------------------------------------------------------------------
1385 --
1386 -- Description
1387 -- This procedure is used to check that the lookup value is valid.
1388 --
1389 -- Pre Conditions
1390 -- None.
1391 --
1392 -- In Parameters
1393 -- elig_to_prte_rsn_id PK of record being inserted or updated.
1394 -- mx_poe_apls_cd Value of lookup code.
1395 -- effective_date effective date
1396 -- object_version_number Object version number of record being
1397 -- inserted or updated.
1398 --
1399 -- Post Success
1400 -- Processing continues
1401 --
1402 -- Post Failure
1403 -- Error handled by procedure
1404 --
1405 -- Access Status
1406 -- Internal table handler use only.
1407 --
1408 Procedure chk_mx_poe_apls_cd
1409 (p_elig_to_prte_rsn_id in number
1410 ,p_mx_poe_apls_cd in varchar2
1411 ,p_effective_date in date
1412 ,p_object_version_number in number)
1413 is
1414 --
1415 l_proc varchar2(72) := g_package || 'chk_mx_poe_apls_cd';
1416 l_api_updating boolean;
1417 --
1418 Begin
1419 --
1420 hr_utility.set_location('Entering:'||l_proc, 5);
1421 --
1422 l_api_updating := ben_peo_shd.api_updating
1423 (p_elig_to_prte_rsn_id => p_elig_to_prte_rsn_id,
1424 p_effective_date => p_effective_date,
1425 p_object_version_number => p_object_version_number);
1426 --
1427 if (l_api_updating
1428 and p_mx_poe_apls_cd
1429 <> nvl(ben_peo_shd.g_old_rec.mx_poe_apls_cd,hr_api.g_varchar2)
1430 or not l_api_updating)
1431 and p_mx_poe_apls_cd is not null then
1432 --
1433 -- check if value of lookup falls within lookup type.
1434 --
1435 if hr_api.not_exists_in_hr_lookups
1436 (p_lookup_type => 'BEN_MX_POE_APLS',
1437 p_lookup_code => p_mx_poe_apls_cd,
1438 p_effective_date => p_effective_date) then
1439 --
1440 -- raise error as does not exist as lookup
1441 --
1442 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1443 fnd_message.set_token('FIELD', 'p_mx_poe_apls_cd');
1444 fnd_message.set_token('TYPE','BEN_MX_POE_APLS');
1445 fnd_message.raise_error;
1446 --
1447 end if;
1448 --
1449 end if;
1450 --
1451 hr_utility.set_location('Leaving:'||l_proc,10);
1452 --
1453 end chk_mx_poe_apls_cd;
1454 --
1455 -- ----------------------------------------------------------------------------
1456 -- |-----------------------< chk_only_one_fk >--------------------------------|
1457 -- ----------------------------------------------------------------------------
1458 --
1459 -- Description
1460 -- This procedure is used to check that the oipl_id or pgm_id or pl_id or
1461 -- ptip_id or plip_id is populated and not more than one of them.
1462 --
1463 -- Pre Conditions
1464 -- None.
1465 --
1466 -- In Parameters
1467 -- oipl_id Option in plan id of option.
1468 -- pgm_id program id of program.
1469 -- pl_id plan id of plan
1470 --
1471 -- Post Success
1472 -- Processing continues
1473 --
1474 -- Post Failure
1475 -- Error handled by procedure
1476 --
1477 -- Access Status
1478 -- Internal table handler use only.
1479 --
1480 Procedure chk_only_one_fk(p_oipl_id in number,
1481 p_pgm_id in number,
1482 p_pl_id in number,
1483 p_ptip_id in number,
1484 p_plip_id in number) is
1485 --
1486 l_proc varchar2(72) := g_package||'chk_only_one_fk';
1487 l_api_updating boolean;
1488 --
1489 function count_them(p_id in number) return number is
1490 --
1491 begin
1492 --
1493 if p_id is not null then
1494 --
1495 return 1;
1496 --
1497 else
1498 --
1499 return 0;
1500 --
1504 --
1501 end if;
1502 --
1503 end;
1505 Begin
1506 --
1507 hr_utility.set_location('Entering:'||l_proc, 5);
1508 --
1509 -- check that only one of the ID's is populated.
1510 --
1511 if count_them(p_id => p_pgm_id) +
1512 count_them(p_id => p_pl_id) +
1513 count_them(p_id => p_oipl_id) +
1514 count_them(p_id => p_ptip_id) +
1515 count_them(p_id => p_plip_id) <> 1 then
1516 --
1517 fnd_message.set_name('BEN','BEN_92146_ONLY_ONE_FK');
1518 fnd_message.raise_error;
1519 --
1520 end if;
1521 --
1522 hr_utility.set_location('Leaving:'||l_proc, 5);
1523 --
1524 end chk_only_one_fk;
1525 -- ----------------------------------------------------------------------------
1526 -- |--------------------------< dt_update_validate >--------------------------|
1527 -- ----------------------------------------------------------------------------
1528 -- {Start Of Comments}
1529 --
1530 -- Description:
1531 -- This procedure is used for referential integrity of datetracked
1532 -- parent entities when a datetrack update operation is taking place
1533 -- and where there is no cascading of update defined for this entity.
1534 --
1535 -- Prerequisites:
1536 -- This procedure is called from the update_validate.
1537 --
1538 -- In Parameters:
1539 --
1540 -- Post Success:
1541 -- Processing continues.
1542 --
1543 -- Post Failure:
1544 --
1545 -- Developer Implementation Notes:
1546 -- This procedure should not need maintenance unless the HR Schema model
1547 -- changes.
1548 --
1549 -- Access Status:
1550 -- Internal Row Handler Use Only.
1551 --
1552 -- {End Of Comments}
1553 -- ----------------------------------------------------------------------------
1554 Procedure dt_update_validate
1555 (p_oipl_id in number default hr_api.g_number,
1556 p_ler_id in number default hr_api.g_number,
1557 p_pl_id in number default hr_api.g_number,
1558 p_pgm_id in number default hr_api.g_number,
1559 p_ptip_id in number default hr_api.g_number,
1560 p_plip_id in number default hr_api.g_number,
1561 p_datetrack_mode in varchar2,
1562 p_validation_start_date in date,
1563 p_validation_end_date in date) Is
1564 --
1565 l_proc varchar2(72) := g_package||'dt_update_validate';
1566 l_integrity_error Exception;
1567 l_table_name all_tables.table_name%TYPE;
1568 --
1569 Begin
1570 hr_utility.set_location('Entering:'||l_proc, 5);
1571 --
1572 -- Ensure that the p_datetrack_mode argument is not null
1573 --
1574 hr_api.mandatory_arg_error
1575 (p_api_name => l_proc,
1576 p_argument => 'datetrack_mode',
1577 p_argument_value => p_datetrack_mode);
1578 --
1579 -- Only perform the validation if the datetrack update mode is valid
1580 --
1581 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
1582 --
1583 --
1584 -- Ensure the arguments are not null
1585 --
1586 hr_api.mandatory_arg_error
1587 (p_api_name => l_proc,
1588 p_argument => 'validation_start_date',
1589 p_argument_value => p_validation_start_date);
1590 --
1591 hr_api.mandatory_arg_error
1592 (p_api_name => l_proc,
1593 p_argument => 'validation_end_date',
1594 p_argument_value => p_validation_end_date);
1595 --
1596 If ((nvl(p_oipl_id, hr_api.g_number) <> hr_api.g_number) and
1597 NOT (dt_api.check_min_max_dates
1598 (p_base_table_name => 'ben_oipl_f',
1599 p_base_key_column => 'oipl_id',
1600 p_base_key_value => p_oipl_id,
1601 p_from_date => p_validation_start_date,
1602 p_to_date => p_validation_end_date))) Then
1603 l_table_name := 'ben_oipl_f';
1604 Raise l_integrity_error;
1605 End If;
1606 If ((nvl(p_ler_id, hr_api.g_number) <> hr_api.g_number) and
1607 NOT (dt_api.check_min_max_dates
1608 (p_base_table_name => 'ben_ler_f',
1609 p_base_key_column => 'ler_id',
1610 p_base_key_value => p_ler_id,
1611 p_from_date => p_validation_start_date,
1612 p_to_date => p_validation_end_date))) Then
1613 l_table_name := 'ben_ler_f';
1614 Raise l_integrity_error;
1615 End If;
1616 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
1617 NOT (dt_api.check_min_max_dates
1618 (p_base_table_name => 'ben_pl_f',
1619 p_base_key_column => 'pl_id',
1620 p_base_key_value => p_pl_id,
1621 p_from_date => p_validation_start_date,
1622 p_to_date => p_validation_end_date))) Then
1623 l_table_name := 'ben_pl_f';
1624 Raise l_integrity_error;
1625 End If;
1626 If ((nvl(p_pgm_id, hr_api.g_number) <> hr_api.g_number) and
1627 NOT (dt_api.check_min_max_dates
1628 (p_base_table_name => 'ben_pgm_f',
1629 p_base_key_column => 'pgm_id',
1630 p_base_key_value => p_pgm_id,
1631 p_from_date => p_validation_start_date,
1632 p_to_date => p_validation_end_date))) Then
1633 l_table_name := 'ben_pgm_f';
1634 Raise l_integrity_error;
1635 End If;
1636 If ((nvl(p_ptip_id, hr_api.g_number) <> hr_api.g_number) and
1637 NOT (dt_api.check_min_max_dates
1641 p_from_date => p_validation_start_date,
1638 (p_base_table_name => 'ben_ptip_f',
1639 p_base_key_column => 'ptip_id',
1640 p_base_key_value => p_ptip_id,
1642 p_to_date => p_validation_end_date))) Then
1643 l_table_name := 'ben_ptip_f';
1644 Raise l_integrity_error;
1645 End If;
1646 If ((nvl(p_plip_id, hr_api.g_number) <> hr_api.g_number) and
1647 NOT (dt_api.check_min_max_dates
1648 (p_base_table_name => 'ben_plip_f',
1649 p_base_key_column => 'plip_id',
1650 p_base_key_value => p_plip_id,
1651 p_from_date => p_validation_start_date,
1652 p_to_date => p_validation_end_date))) Then
1653 l_table_name := 'ben_plip_f';
1654 Raise l_integrity_error;
1655 End If;
1656 --
1657 End If;
1658 --
1659 hr_utility.set_location(' Leaving:'||l_proc, 10);
1660 Exception
1661 When l_integrity_error Then
1662 --
1663 -- A referential integrity check was violated therefore
1664 -- we must error
1665 --
1666 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
1667 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1668 hr_utility.raise_error;
1669 When Others Then
1670 --
1671 -- An unhandled or unexpected error has occurred which
1672 -- we must report
1673 --
1674 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1675 hr_utility.set_message_token('PROCEDURE', l_proc);
1676 hr_utility.set_message_token('STEP','15');
1677 hr_utility.raise_error;
1678 End dt_update_validate;
1679 --
1680 -- ----------------------------------------------------------------------------
1681 -- |--------------------------< dt_delete_validate >--------------------------|
1682 -- ----------------------------------------------------------------------------
1683 -- {Start Of Comments}
1684 --
1685 -- Description:
1686 -- This procedure is used for referential integrity of datetracked
1687 -- child entities when either a datetrack DELETE or ZAP is in operation
1688 -- and where there is no cascading of delete defined for this entity.
1689 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1690 -- datetracked child rows exist between the validation start and end
1691 -- dates.
1692 --
1693 -- Prerequisites:
1694 -- This procedure is called from the delete_validate.
1695 --
1696 -- In Parameters:
1697 --
1698 -- Post Success:
1699 -- Processing continues.
1700 --
1701 -- Post Failure:
1702 -- If a row exists by determining the returning Boolean value from the
1703 -- generic dt_api.rows_exist function then we must supply an error via
1704 -- the use of the local exception handler l_rows_exist.
1705 --
1706 -- Developer Implementation Notes:
1707 -- This procedure should not need maintenance unless the HR Schema model
1708 -- changes.
1709 --
1710 -- Access Status:
1711 -- Internal Row Handler Use Only.
1712 --
1713 -- {End Of Comments}
1714 -- ----------------------------------------------------------------------------
1715 Procedure dt_delete_validate
1716 (p_elig_to_prte_rsn_id in number,
1717 p_datetrack_mode in varchar2,
1718 p_validation_start_date in date,
1719 p_validation_end_date in date) Is
1720 --
1721 l_proc varchar2(72) := g_package||'dt_delete_validate';
1722 l_rows_exist Exception;
1723 l_table_name all_tables.table_name%TYPE;
1724 --
1725 Begin
1726 hr_utility.set_location('Entering:'||l_proc, 5);
1727 --
1728 -- Ensure that the p_datetrack_mode argument is not null
1729 --
1730 hr_api.mandatory_arg_error
1731 (p_api_name => l_proc,
1732 p_argument => 'datetrack_mode',
1733 p_argument_value => p_datetrack_mode);
1734 --
1735 -- Only perform the validation if the datetrack mode is either
1736 -- DELETE or ZAP
1737 --
1738 If (p_datetrack_mode = 'DELETE' or
1739 p_datetrack_mode = 'ZAP') then
1740 --
1741 --
1742 -- Ensure the arguments are not null
1743 --
1744 hr_api.mandatory_arg_error
1745 (p_api_name => l_proc,
1746 p_argument => 'validation_start_date',
1747 p_argument_value => p_validation_start_date);
1748 --
1749 hr_api.mandatory_arg_error
1750 (p_api_name => l_proc,
1751 p_argument => 'validation_end_date',
1752 p_argument_value => p_validation_end_date);
1753 --
1754 hr_api.mandatory_arg_error
1755 (p_api_name => l_proc,
1756 p_argument => 'elig_to_prte_rsn_id',
1757 p_argument_value => p_elig_to_prte_rsn_id);
1758 --
1759 --
1760 --
1761 End If;
1762 --
1763 hr_utility.set_location(' Leaving:'||l_proc, 10);
1764 Exception
1765 When l_rows_exist Then
1766 --
1767 -- A referential integrity check was violated therefore
1768 -- we must error
1769 --
1770 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
1771 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1772 hr_utility.raise_error;
1773 When Others Then
1774 --
1775 -- An unhandled or unexpected error has occurred which
1776 -- we must report
1777 --
1778 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1779 hr_utility.set_message_token('PROCEDURE', l_proc);
1780 hr_utility.set_message_token('STEP','15');
1781 hr_utility.raise_error;
1782 End dt_delete_validate;
1783 --
1787 Procedure insert_validate
1784 -- ----------------------------------------------------------------------------
1785 -- |---------------------------< insert_validate >----------------------------|
1786 -- ----------------------------------------------------------------------------
1788 (p_rec in ben_peo_shd.g_rec_type
1789 ,p_effective_date in date
1790 ,p_datetrack_mode in varchar2
1791 ,p_validation_start_date in date
1792 ,p_validation_end_date in date) is
1793 --
1794 l_proc varchar2(72) := g_package||'insert_validate';
1795 --
1796 Begin
1797 hr_utility.set_location('Entering:'||l_proc, 5);
1798 --
1799 -- Call all supporting business operations
1800 --
1801 --
1802 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1803 --
1804 chk_elig_to_prte_rsn_id
1805 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1806 p_effective_date => p_effective_date,
1807 p_object_version_number => p_rec.object_version_number);
1808 --
1809 chk_ignr_prtn_ovrid_flag
1810 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1811 p_ignr_prtn_ovrid_flag => p_rec.ignr_prtn_ovrid_flag,
1812 p_effective_date => p_effective_date,
1813 p_object_version_number => p_rec.object_version_number);
1814 --
1815 chk_prtn_ovridbl_flag
1816 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1817 p_prtn_ovridbl_flag => p_rec.prtn_ovridbl_flag,
1818 p_effective_date => p_effective_date,
1819 p_object_version_number => p_rec.object_version_number);
1820 --
1821 chk_elig_inelig_cd
1822 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1823 p_elig_inelig_cd => p_rec.elig_inelig_cd,
1824 p_effective_date => p_effective_date,
1825 p_object_version_number => p_rec.object_version_number);
1826 --
1827 chk_vrfy_fmly_mmbr_cd
1828 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1829 p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd,
1830 p_effective_date => p_effective_date,
1831 p_object_version_number => p_rec.object_version_number);
1832 --
1833 chk_vrfy_fmly_mmbr_rl
1834 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1835 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl,
1836 p_business_group_id => p_rec.business_group_id,
1837 p_effective_date => p_effective_date,
1838 p_object_version_number => p_rec.object_version_number);
1839 --
1840 chk_prtn_eff_strt_dt_cd
1841 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1842 p_prtn_eff_strt_dt_cd => p_rec.prtn_eff_strt_dt_cd,
1843 p_effective_date => p_effective_date,
1844 p_object_version_number => p_rec.object_version_number);
1845 --
1846 chk_prtn_eff_strt_dt_rl
1847 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1848 p_prtn_eff_strt_dt_rl => p_rec.prtn_eff_strt_dt_rl,
1849 p_business_group_id => p_rec.business_group_id,
1850 p_effective_date => p_effective_date,
1851 p_object_version_number => p_rec.object_version_number);
1852 --
1853 chk_prtn_eff_end_dt_cd
1854 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1855 p_prtn_eff_end_dt_cd => p_rec.prtn_eff_end_dt_cd,
1856 p_effective_date => p_effective_date,
1857 p_object_version_number => p_rec.object_version_number);
1858 --
1859 chk_prtn_eff_end_dt_rl
1860 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1861 p_prtn_eff_end_dt_rl => p_rec.prtn_eff_end_dt_rl,
1862 p_business_group_id => p_rec.business_group_id,
1863 p_effective_date => p_effective_date,
1864 p_object_version_number => p_rec.object_version_number);
1865 --
1866 chk_wait_perd_dt_to_use_cd
1867 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1868 p_wait_perd_dt_to_use_cd => p_rec.wait_perd_dt_to_use_cd,
1869 p_effective_date => p_effective_date,
1870 p_object_version_number => p_rec.object_version_number);
1871 --
1872 chk_wait_perd_dt_to_use_rl
1873 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1874 p_wait_perd_dt_to_use_rl => p_rec.wait_perd_dt_to_use_rl,
1875 p_business_group_id => p_rec.business_group_id,
1876 p_effective_date => p_effective_date,
1877 p_object_version_number => p_rec.object_version_number);
1878 --
1879 chk_wait_perd_uom
1880 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1881 p_wait_perd_uom => p_rec.wait_perd_uom,
1882 p_effective_date => p_effective_date,
1883 p_object_version_number => p_rec.object_version_number);
1884 --
1885 chk_wait_perd_rl
1886 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1887 p_wait_perd_rl => p_rec.wait_perd_rl,
1888 p_business_group_id => p_rec.business_group_id,
1889 p_effective_date => p_effective_date,
1890 p_object_version_number => p_rec.object_version_number);
1891 --
1892 chk_mx_poe_uom
1893 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1894 p_mx_poe_uom => p_rec.mx_poe_uom,
1895 p_effective_date => p_effective_date,
1896 p_object_version_number => p_rec.object_version_number);
1897 --
1898 chk_mx_poe_det_dt_rl
1899 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1900 p_mx_poe_det_dt_rl => p_rec.mx_poe_det_dt_rl,
1901 p_business_group_id => p_rec.business_group_id,
1902 p_effective_date => p_effective_date,
1903 p_object_version_number => p_rec.object_version_number);
1904 --
1905 chk_mx_poe_rl
1906 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1907 p_mx_poe_rl => p_rec.mx_poe_rl,
1908 p_business_group_id => p_rec.business_group_id,
1909 p_effective_date => p_effective_date,
1910 p_object_version_number => p_rec.object_version_number);
1911 --
1912 chk_mx_poe_det_dt_cd
1916 p_object_version_number => p_rec.object_version_number);
1913 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1914 p_mx_poe_det_dt_cd => p_rec.mx_poe_det_dt_cd,
1915 p_effective_date => p_effective_date,
1917 --
1918 chk_mx_poe_apls_cd
1919 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1920 p_mx_poe_apls_cd => p_rec.mx_poe_apls_cd,
1921 p_effective_date => p_effective_date,
1922 p_object_version_number => p_rec.object_version_number);
1923 --
1924 chk_only_one_fk
1925 (p_oipl_id => p_rec.oipl_id,
1926 p_pgm_id => p_rec.pgm_id,
1927 p_pl_id => p_rec.pl_id,
1928 p_ptip_id => p_rec.ptip_id,
1929 p_plip_id => p_rec.plip_id);
1930 --
1931
1932 chk_cd_rl_combination
1933 (p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd,
1934 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl);
1935 --
1936 hr_utility.set_location(' Leaving:'||l_proc, 10);
1937 --
1938 End insert_validate;
1939 --
1940 -- ----------------------------------------------------------------------------
1941 -- |---------------------------< update_validate >----------------------------|
1942 -- ----------------------------------------------------------------------------
1943 Procedure update_validate
1944 (p_rec in ben_peo_shd.g_rec_type
1945 ,p_effective_date in date
1946 ,p_datetrack_mode in varchar2
1947 ,p_validation_start_date in date
1948 ,p_validation_end_date in date)
1949 --
1950 is
1951 --
1952 l_proc varchar2(72) := g_package||'update_validate';
1953 --
1954 Begin
1955 --
1956 hr_utility.set_location('Entering:'||l_proc, 5);
1957 --
1958 -- Call all supporting business operations
1959 --
1960 --
1961 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1962 --
1963 chk_elig_to_prte_rsn_id
1964 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1965 p_effective_date => p_effective_date,
1966 p_object_version_number => p_rec.object_version_number);
1967 --
1968 chk_ignr_prtn_ovrid_flag
1969 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1970 p_ignr_prtn_ovrid_flag => p_rec.ignr_prtn_ovrid_flag,
1971 p_effective_date => p_effective_date,
1972 p_object_version_number => p_rec.object_version_number);
1973 --
1974 chk_prtn_ovridbl_flag
1975 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1976 p_prtn_ovridbl_flag => p_rec.prtn_ovridbl_flag,
1977 p_effective_date => p_effective_date,
1978 p_object_version_number => p_rec.object_version_number);
1979 --
1980 chk_elig_inelig_cd
1981 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1982 p_elig_inelig_cd => p_rec.elig_inelig_cd,
1983 p_effective_date => p_effective_date,
1984 p_object_version_number => p_rec.object_version_number);
1985 --
1986 chk_vrfy_fmly_mmbr_cd
1987 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1988 p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd,
1989 p_effective_date => p_effective_date,
1990 p_object_version_number => p_rec.object_version_number);
1991 --
1992 chk_vrfy_fmly_mmbr_rl
1993 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
1994 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl,
1995 p_business_group_id => p_rec.business_group_id,
1996 p_effective_date => p_effective_date,
1997 p_object_version_number => p_rec.object_version_number);
1998 --
1999 chk_prtn_eff_strt_dt_cd
2000 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
2001 p_prtn_eff_strt_dt_cd => p_rec.prtn_eff_strt_dt_cd,
2002 p_effective_date => p_effective_date,
2003 p_object_version_number => p_rec.object_version_number);
2004 --
2005 chk_prtn_eff_strt_dt_rl
2006 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
2007 p_prtn_eff_strt_dt_rl => p_rec.prtn_eff_strt_dt_rl,
2008 p_business_group_id => p_rec.business_group_id,
2009 p_effective_date => p_effective_date,
2010 p_object_version_number => p_rec.object_version_number);
2011 --
2012 chk_prtn_eff_end_dt_cd
2013 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
2014 p_prtn_eff_end_dt_cd => p_rec.prtn_eff_end_dt_cd,
2015 p_effective_date => p_effective_date,
2016 p_object_version_number => p_rec.object_version_number);
2017 --
2018 chk_prtn_eff_end_dt_rl
2019 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
2020 p_prtn_eff_end_dt_rl => p_rec.prtn_eff_end_dt_rl,
2021 p_business_group_id => p_rec.business_group_id,
2022 p_effective_date => p_effective_date,
2023 p_object_version_number => p_rec.object_version_number);
2024 --
2025 chk_wait_perd_dt_to_use_cd
2026 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
2027 p_wait_perd_dt_to_use_cd => p_rec.wait_perd_dt_to_use_cd,
2028 p_effective_date => p_effective_date,
2029 p_object_version_number => p_rec.object_version_number);
2030 --
2031 chk_wait_perd_dt_to_use_rl
2032 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
2033 p_wait_perd_dt_to_use_rl => p_rec.wait_perd_dt_to_use_rl,
2034 p_business_group_id => p_rec.business_group_id,
2035 p_effective_date => p_effective_date,
2036 p_object_version_number => p_rec.object_version_number);
2037 --
2038 chk_wait_perd_uom
2039 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
2040 p_wait_perd_uom => p_rec.wait_perd_uom,
2041 p_effective_date => p_effective_date,
2042 p_object_version_number => p_rec.object_version_number);
2043 --
2044 chk_mx_poe_uom
2045 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
2049 --
2046 p_mx_poe_uom => p_rec.mx_poe_uom,
2047 p_effective_date => p_effective_date,
2048 p_object_version_number => p_rec.object_version_number);
2050 chk_mx_poe_det_dt_rl
2051 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
2052 p_mx_poe_det_dt_rl => p_rec.mx_poe_det_dt_rl,
2053 p_business_group_id => p_rec.business_group_id,
2054 p_effective_date => p_effective_date,
2055 p_object_version_number => p_rec.object_version_number);
2056 --
2057 chk_mx_poe_rl
2058 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
2059 p_mx_poe_rl => p_rec.mx_poe_rl,
2060 p_business_group_id => p_rec.business_group_id,
2061 p_effective_date => p_effective_date,
2062 p_object_version_number => p_rec.object_version_number);
2063 --
2064 chk_mx_poe_det_dt_cd
2065 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
2066 p_mx_poe_det_dt_cd => p_rec.mx_poe_det_dt_cd,
2067 p_effective_date => p_effective_date,
2068 p_object_version_number => p_rec.object_version_number);
2069 --
2070 chk_mx_poe_apls_cd
2071 (p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id,
2072 p_mx_poe_apls_cd => p_rec.mx_poe_apls_cd,
2073 p_effective_date => p_effective_date,
2074 p_object_version_number => p_rec.object_version_number);
2075 --
2076 chk_only_one_fk
2077 (p_oipl_id => p_rec.oipl_id,
2078 p_pgm_id => p_rec.pgm_id,
2079 p_pl_id => p_rec.pl_id,
2080 p_ptip_id => p_rec.ptip_id,
2081 p_plip_id => p_rec.plip_id);
2082 --
2083 -- Call the datetrack update integrity operation
2084 --
2085 dt_update_validate
2086 (p_oipl_id => p_rec.oipl_id,
2087 p_ler_id => p_rec.ler_id,
2088 p_pl_id => p_rec.pl_id,
2089 p_pgm_id => p_rec.pgm_id,
2090 p_ptip_id => p_rec.ptip_id,
2091 p_plip_id => p_rec.plip_id,
2092 p_datetrack_mode => p_datetrack_mode,
2093 p_validation_start_date => p_validation_start_date,
2094 p_validation_end_date => p_validation_end_date);
2095 --
2096
2097 chk_cd_rl_combination
2098 (p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd,
2099 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl);
2100 --
2101 hr_utility.set_location(' Leaving:'||l_proc, 10);
2102 --
2103 End update_validate;
2104 --
2105 -- ----------------------------------------------------------------------------
2106 -- |---------------------------< delete_validate >----------------------------|
2107 -- ----------------------------------------------------------------------------
2108 Procedure delete_validate
2109 (p_rec in ben_peo_shd.g_rec_type,
2110 p_effective_date in date,
2111 p_datetrack_mode in varchar2,
2112 p_validation_start_date in date,
2113 p_validation_end_date in date) is
2114 --
2115 l_proc varchar2(72) := g_package||'delete_validate';
2116 --
2117 Begin
2118 hr_utility.set_location('Entering:'||l_proc, 5);
2119 --
2120 -- Call all supporting business operations
2121 --
2122 dt_delete_validate
2123 (p_datetrack_mode => p_datetrack_mode,
2124 p_validation_start_date => p_validation_start_date,
2125 p_validation_end_date => p_validation_end_date,
2126 p_elig_to_prte_rsn_id => p_rec.elig_to_prte_rsn_id);
2127 --
2128 hr_utility.set_location(' Leaving:'||l_proc, 10);
2129 --
2130 End delete_validate;
2131 --
2132 -- ---------------------------------------------------------------------------
2133 -- |---------------------< return_legislation_code >-------------------------|
2134 -- ---------------------------------------------------------------------------
2135 --
2136 function return_legislation_code
2137 (p_elig_to_prte_rsn_id in number) return varchar2 is
2138 --
2139 -- Declare cursor
2140 --
2141 cursor csr_leg_code is
2142 select a.legislation_code
2143 from per_business_groups a,
2144 ben_elig_to_prte_rsn_f b
2145 where b.elig_to_prte_rsn_id = p_elig_to_prte_rsn_id
2146 and a.business_group_id = b.business_group_id;
2147 --
2148 -- Declare local variables
2149 --
2150 l_legislation_code varchar2(150);
2151 l_proc varchar2(72) := g_package||'return_legislation_code';
2152 --
2153 begin
2154 --
2155 --
2156 hr_utility.set_location('Entering:'|| l_proc, 10);
2157 --
2158 -- Ensure that all the mandatory parameter are not null
2159 --
2160 hr_api.mandatory_arg_error(p_api_name => l_proc,
2161 p_argument => 'elig_to_prte_rsn_id',
2162 p_argument_value => p_elig_to_prte_rsn_id);
2163 --
2164 open csr_leg_code;
2165 --
2166 fetch csr_leg_code into l_legislation_code;
2167 --
2168 if csr_leg_code%notfound then
2169 --
2170 close csr_leg_code;
2171 --
2172 -- The primary key is invalid therefore we must error
2173 --
2174 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
2175 hr_utility.raise_error;
2176 --
2177 end if;
2178 --
2179 close csr_leg_code;
2180 --
2181 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2182 --
2183 return l_legislation_code;
2184 --
2185 end return_legislation_code;
2186 --
2187 end ben_peo_bus;