[Home] [Help]
PACKAGE BODY: APPS.BEN_COP_BUS
Source
1 Package Body ben_cop_bus as
2 /* $Header: becoprhi.pkb 120.5.12020000.2 2012/07/03 12:02:13 amnaraya ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cop_bus.'; -- Global package name
9
10 --
11 -- ----------------------------------------------------------------------------
12 -- |-------------------------< chk_oipl_id >----------------------------------|
13 -- ----------------------------------------------------------------------------
14 --
15 -- Description
16 -- This procedure is used to check that the primary key for the table
17 -- is created properly. It should be null on insert and
18 -- should not be able to be updated.
19 --
20 -- Pre Conditions
21 -- None.
22 --
23 -- In Parameters
24 -- oipl_id PK of record being inserted or updated.
25 -- effective_date Effective Date of session
26 -- object_version_number Object version number of record being
27 -- inserted or updated.
28 --
29 -- Post Success
30 -- Processing continues
31 --
32 -- Post Failure
33 -- Errors handled by the procedure
34 --
35 -- Access Status
36 -- Internal table handler use only.
37 --
38 Procedure chk_oipl_id(p_oipl_id in number,
39 p_effective_date in date,
40 p_object_version_number in number) is
41 --
42 l_proc varchar2(72) := g_package||'chk_oipl_id';
43 l_api_updating boolean;
44 --
45 Begin
46 --
47 hr_utility.set_location('Entering:'||l_proc, 5);
48 --
49 l_api_updating := ben_cop_shd.api_updating
50 (p_effective_date => p_effective_date,
51 p_oipl_id => p_oipl_id,
52 p_object_version_number => p_object_version_number);
53 --
54 if (l_api_updating
55 and nvl(p_oipl_id,hr_api.g_number)
56 <> ben_cop_shd.g_old_rec.oipl_id) then
57 --
58 -- raise error as PK has changed
59 --
60 ben_cpo_shd.constraint_error('BEN_OIPL_PK');
61 --
62 elsif not l_api_updating then
63 --
64 -- check if PK is null
65 --
66 if p_oipl_id is not null then
67 --
68 -- raise error as PK is not null
69 --
70 ben_cop_shd.constraint_error('BEN_OIPL_PK');
71 --
72 end if;
73 --
74 end if;
75 --
76 hr_utility.set_location('Leaving:'||l_proc, 10);
77 --
78 End chk_oipl_id;
79 -- ----------------------------------------------------------------------------
80 -- |----------------------------< chk_dflt_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 -- oipl_id PK of record being inserted or updated.
91 -- dflt_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_dflt_flag(p_oipl_id in number,
106 p_dflt_flag in varchar2,
107 p_effective_date in date,
108 p_object_version_number in number) is
109 --
110 l_proc varchar2(72) := g_package||'chk_dflt_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_cop_shd.api_updating
118 (p_oipl_id => p_oipl_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_dflt_flag
124 <> nvl(ben_cop_shd.g_old_rec.dflt_flag,hr_api.g_varchar2)
125 or not l_api_updating)
126 and p_dflt_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_dflt_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_dflt_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_dflt_flag;
149 -- ----------------------------------------------------------------------------
150 -- |----------------------------< chk_mndtry_flag >---------------------------|
151 -- ----------------------------------------------------------------------------
152 --
153 -- Description
154 -- This procedure is used to check that the lookup value is valid.
155 --
156 -- Pre Conditions
157 -- None.
158 --
159 -- In Parameters
160 -- oipl_id PK of record being inserted or updated.
161 -- mndtry_flag Value of lookup code.
162 -- effective_date effective date
163 -- object_version_number Object version number of record being
164 -- inserted or updated.
165 --
166 -- Post Success
167 -- Processing continues
168 --
169 -- Post Failure
170 -- Error handled by procedure
171 --
172 -- Access Status
173 -- Internal table handler use only.
174 --
175 Procedure chk_mndtry_flag(p_oipl_id in number,
176 p_mndtry_flag in varchar2,
177 p_effective_date in date,
178 p_object_version_number in number) is
179 --
180 l_proc varchar2(72) := g_package||'chk_mndtry_flag';
181 l_api_updating boolean;
182 --
183 Begin
184 --
185 hr_utility.set_location('Entering:'||l_proc, 5);
186 --
187 l_api_updating := ben_cop_shd.api_updating
188 (p_oipl_id => p_oipl_id,
189 p_effective_date => p_effective_date,
190 p_object_version_number => p_object_version_number);
191 --
192 if (l_api_updating
193 and p_mndtry_flag
194 <> nvl(ben_cop_shd.g_old_rec.mndtry_flag,hr_api.g_varchar2)
195 or not l_api_updating)
196 and p_mndtry_flag is not null then
197 --
198 -- check if value of lookup falls within lookup type.
199 --
200 if hr_api.not_exists_in_hr_lookups
201 (p_lookup_type => 'YES_NO',
202 p_lookup_code => p_mndtry_flag,
203 p_effective_date => p_effective_date) then
204 --
205 -- raise error as does not exist as lookup
206 --
207 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
208 fnd_message.set_token('FIELD','p_mndtry_flag');
209 fnd_message.set_token('TYPE','YES_NO');
210 fnd_message.raise_error;
211 --
212 end if;
213 --
214 end if;
215 --
216 hr_utility.set_location('Leaving:'||l_proc,10);
217 --
218 end chk_mndtry_flag;
219 -- ----------------------------------------------------------------------------
220 -- |----------------------------< chk_elig_apls_flag >------------------------|
221 -- ----------------------------------------------------------------------------
222 --
223 -- Description
224 -- This procedure is used to check that the lookup value is valid.
225 --
226 -- Pre Conditions
227 -- None.
228 --
229 -- In Parameters
230 -- oipl_id PK of record being inserted or updated.
231 -- elig_apls_flag Value of lookup code.
232 -- effective_date effective date
233 -- object_version_number Object version number of record being
234 -- inserted or updated.
235 --
236 -- Post Success
237 -- Processing continues
238 --
239 -- Post Failure
240 -- Error handled by procedure
241 --
242 -- Access Status
243 -- Internal table handler use only.
244 --
245 Procedure chk_elig_apls_flag(p_oipl_id in number,
246 p_elig_apls_flag in varchar2,
247 p_effective_date in date,
248 p_object_version_number in number) is
249 --
250 l_proc varchar2(72) := g_package||'chk_elig_apls_flag';
251 l_api_updating boolean;
252 --
253 Begin
254 --
255 hr_utility.set_location('Entering:'||l_proc, 5);
256 --
257 l_api_updating := ben_cop_shd.api_updating
258 (p_oipl_id => p_oipl_id,
259 p_effective_date => p_effective_date,
260 p_object_version_number => p_object_version_number);
261 --
262 if (l_api_updating
263 and p_elig_apls_flag
264 <> nvl(ben_cop_shd.g_old_rec.elig_apls_flag,hr_api.g_varchar2)
265 or not l_api_updating)
266 and p_elig_apls_flag is not null then
267 --
268 -- check if value of lookup falls within lookup type.
269 --
270 if hr_api.not_exists_in_hr_lookups
271 (p_lookup_type => 'YES_NO',
272 p_lookup_code => p_elig_apls_flag,
273 p_effective_date => p_effective_date) then
274 --
275 -- raise error as does not exist as lookup
276 --
277 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
278 fnd_message.set_token('FIELD','p_elig_apls_flag');
279 fnd_message.set_token('TYPE','YES_NO');
280 fnd_message.raise_error;
281 --
282 end if;
283 --
284 end if;
285 --
286 hr_utility.set_location('Leaving:'||l_proc,10);
287 --
288 end chk_elig_apls_flag;
289 -- ----------------------------------------------------------------------------
290 -- |----------------------------< chk_trk_inelig_per_flag >-------------------|
291 -- ----------------------------------------------------------------------------
292 --
293 -- Description
294 -- This procedure is used to check that the lookup value is valid.
295 --
296 -- Pre Conditions
297 -- None.
298 --
299 -- In Parameters
300 -- oipl_id PK of record being inserted or updated.
301 -- trk_inelig_per_flag Value of lookup code.
302 -- effective_date effective date
303 -- object_version_number Object version number of record being
304 -- inserted or updated.
305 --
306 -- Post Success
307 -- Processing continues
308 --
309 -- Post Failure
310 -- Error handled by procedure
311 --
312 -- Access Status
313 -- Internal table handler use only.
314 --
315 Procedure chk_trk_inelig_per_flag(p_oipl_id in number,
316 p_trk_inelig_per_flag in varchar2,
317 p_effective_date in date,
318 p_object_version_number in number) is
319 --
320 l_proc varchar2(72) := g_package||'chk_trk_inelig_per_flag';
321 l_api_updating boolean;
322 --
323 Begin
324 --
325 hr_utility.set_location('Entering:'||l_proc, 5);
326 --
327 l_api_updating := ben_cop_shd.api_updating
328 (p_oipl_id => p_oipl_id,
329 p_effective_date => p_effective_date,
330 p_object_version_number => p_object_version_number);
331 --
332 if (l_api_updating
333 and p_trk_inelig_per_flag
334 <> nvl(ben_cop_shd.g_old_rec.trk_inelig_per_flag,hr_api.g_varchar2)
335 or not l_api_updating)
336 and p_trk_inelig_per_flag is not null then
337 --
338 -- check if value of lookup falls within lookup type.
339 --
340 if hr_api.not_exists_in_hr_lookups
341 (p_lookup_type => 'YES_NO',
342 p_lookup_code => p_trk_inelig_per_flag,
343 p_effective_date => p_effective_date) then
344 --
345 -- raise error as does not exist as lookup
346 --
347 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
348 fnd_message.set_token('FIELD','p_trk_inelig_per_flag');
349 fnd_message.set_token('TYPE','YES_NO');
350 fnd_message.raise_error;
351 --
352 end if;
353 --
354 end if;
355 --
356 hr_utility.set_location('Leaving:'||l_proc,10);
357 --
358 end chk_trk_inelig_per_flag;
359 -- ----------------------------------------------------------------------------
360 -- |---------------------------< chk_drvbl_fctr_prtn_elig_flag >--------------|
361 -- ----------------------------------------------------------------------------
362 --
363 -- Description
364 -- This procedure is used to check that the lookup value is valid.
365 --
366 -- Pre Conditions
367 -- None.
368 --
369 -- In Parameters
370 -- oipl_id PK of record being inserted or updated.
371 -- drvbl_fctr_prtn_elig_flag Value of lookup code.
372 -- effective_date effective date
373 -- object_version_number Object version number of record being
374 -- inserted or updated.
375 --
376 -- Post Success
377 -- Processing continues
378 --
379 -- Post Failure
380 -- Error handled by procedure
381 --
382 -- Access Status
383 -- Internal table handler use only.
384 --
385 Procedure chk_drvbl_fctr_prtn_elig_flag
386 (p_oipl_id in number,
387 p_drvbl_fctr_prtn_elig_flag in varchar2,
388 p_effective_date in date,
389 p_object_version_number in number) is
390 --
391 l_proc varchar2(72) := g_package||'chk_drvbl_fctr_prtn_elig_flag';
392 l_api_updating boolean;
393 --
394 Begin
395 --
396 hr_utility.set_location('Entering:'||l_proc, 5);
397 --
398 l_api_updating := ben_cop_shd.api_updating
399 (p_oipl_id => p_oipl_id,
400 p_effective_date => p_effective_date,
401 p_object_version_number => p_object_version_number);
402 --
403 if (l_api_updating
404 and p_drvbl_fctr_prtn_elig_flag
405 <> nvl(ben_cop_shd.g_old_rec.drvbl_fctr_prtn_elig_flag,hr_api.g_varchar2)
406 or not l_api_updating)
407 and p_drvbl_fctr_prtn_elig_flag is not null then
408 --
409 -- check if value of lookup falls within lookup type.
410 --
411 if hr_api.not_exists_in_hr_lookups
412 (p_lookup_type => 'YES_NO',
413 p_lookup_code => p_drvbl_fctr_prtn_elig_flag,
414 p_effective_date => p_effective_date) then
415 --
416 -- raise error as does not exist as lookup
417 --
418 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
419 fnd_message.set_token('FIELD','p_drvbl_fctr_prtn_elig_flag');
420 fnd_message.set_token('TYPE','YES_NO');
421 fnd_message.raise_error;
422 --
423 end if;
424 --
425 end if;
426 --
427 hr_utility.set_location('Leaving:'||l_proc,10);
428 --
429 end chk_drvbl_fctr_prtn_elig_flag;
430 -- ----------------------------------------------------------------------------
431 -- |----------------------------< chk_prtn_elig_ovrid_alwd_flag >-------------|
432 -- ----------------------------------------------------------------------------
433 --
434 -- Description
435 -- This procedure is used to check that the lookup value is valid.
436 --
437 -- Pre Conditions
438 -- None.
439 --
440 -- In Parameters
441 -- oipl_id PK of record being inserted or updated.
442 -- prtn_elig_ovrid_alwd_flag Value of lookup code.
443 -- effective_date effective date
444 -- object_version_number Object version number of record being
445 -- inserted or updated.
446 --
447 -- Post Success
448 -- Processing continues
449 --
450 -- Post Failure
451 -- Error handled by procedure
452 --
453 -- Access Status
454 -- Internal table handler use only.
455 --
456 Procedure chk_prtn_elig_ovrid_alwd_flag
457 (p_oipl_id in number,
458 p_prtn_elig_ovrid_alwd_flag in varchar2,
459 p_effective_date in date,
460 p_object_version_number in number) is
461 --
462 l_proc varchar2(72) := g_package||'chk_prtn_elig_ovrid_alwd_flag';
463 l_api_updating boolean;
464 --
465 Begin
466 --
467 hr_utility.set_location('Entering:'||l_proc, 5);
468 --
469 l_api_updating := ben_cop_shd.api_updating
470 (p_oipl_id => p_oipl_id,
471 p_effective_date => p_effective_date,
472 p_object_version_number => p_object_version_number);
473 --
474 if (l_api_updating
475 and p_prtn_elig_ovrid_alwd_flag
476 <> nvl(ben_cop_shd.g_old_rec.prtn_elig_ovrid_alwd_flag,hr_api.g_varchar2)
477 or not l_api_updating)
478 and p_prtn_elig_ovrid_alwd_flag is not null then
479 --
480 -- check if value of lookup falls within lookup type.
481 --
482 if hr_api.not_exists_in_hr_lookups
483 (p_lookup_type => 'YES_NO',
484 p_lookup_code => p_prtn_elig_ovrid_alwd_flag,
485 p_effective_date => p_effective_date) then
486 --
487 -- raise error as does not exist as lookup
488 --
489 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
490 fnd_message.set_token('FIELD','p_prtn_elig_ovrid_alwd_flag');
491 fnd_message.set_token('TYPE','YES_NO');
492 fnd_message.raise_error;
493 --
494 end if;
495 --
496 end if;
497 --
498 hr_utility.set_location('Leaving:'||l_proc,10);
499 --
500 end chk_prtn_elig_ovrid_alwd_flag;
501 -- ----------------------------------------------------------------------------
502 -- |---------------------------< chk_drvbl_fctr_apls_rts_flag >---------------|
503 -- ----------------------------------------------------------------------------
504 --
505 -- Description
506 -- This procedure is used to check that the lookup value is valid.
507 --
508 -- Pre Conditions
509 -- None.
510 --
511 -- In Parameters
512 -- oipl_id PK of record being inserted or updated.
513 -- drvbl_fctr_apls_rts_flag Value of lookup code.
514 -- effective_date effective date
515 -- object_version_number Object version number of record being
516 -- inserted or updated.
517 --
518 -- Post Success
519 -- Processing continues
520 --
521 -- Post Failure
522 -- Error handled by procedure
523 --
524 -- Access Status
525 -- Internal table handler use only.
526 --
527 Procedure chk_drvbl_fctr_apls_rts_flag
528 (p_oipl_id in number,
529 p_drvbl_fctr_apls_rts_flag in varchar2,
530 p_effective_date in date,
531 p_object_version_number in number) is
532 --
533 l_proc varchar2(72) := g_package||'chk_drvbl_fctr_apls_rts_flag';
534 l_api_updating boolean;
535 --
536 Begin
537 --
538 hr_utility.set_location('Entering:'||l_proc, 5);
539 --
540 l_api_updating := ben_cop_shd.api_updating
541 (p_oipl_id => p_oipl_id,
542 p_effective_date => p_effective_date,
543 p_object_version_number => p_object_version_number);
544 --
545 if (l_api_updating
546 and p_drvbl_fctr_apls_rts_flag
547 <> nvl(ben_cop_shd.g_old_rec.drvbl_fctr_apls_rts_flag,hr_api.g_varchar2)
548 or not l_api_updating)
549 and p_drvbl_fctr_apls_rts_flag is not null then
550 --
551 -- check if value of lookup falls within lookup type.
552 --
553 if hr_api.not_exists_in_hr_lookups
554 (p_lookup_type => 'YES_NO',
555 p_lookup_code => p_drvbl_fctr_apls_rts_flag,
556 p_effective_date => p_effective_date) then
557 --
558 -- raise error as does not exist as lookup
559 --
560 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
561 fnd_message.set_token('FIELD','p_drvbl_fctr_apls_rts_flag');
562 fnd_message.set_token('TYPE','YES_NO');
563 fnd_message.raise_error;
564 --
565 end if;
566 --
567 end if;
568 --
569 hr_utility.set_location('Leaving:'||l_proc,10);
570 --
571 end chk_drvbl_fctr_apls_rts_flag;
572 -- ----------------------------------------------------------------------------
573 -- |-----------------------< chk_oipl_stat_cd >-------------------------------|
574 -- ----------------------------------------------------------------------------
575 --
576 -- Description
577 -- This procedure is used to check that the lookup value is valid.
578 --
579 -- Pre Conditions
580 -- None.
581 --
582 -- In Parameters
583 -- oipl_id PK of record being inserted or updated.
584 -- oipl_stat_cd Value of lookup code.
585 -- effective_date effective date
586 -- object_version_number Object version number of record being
587 -- inserted or updated.
588 --
589 -- Post Success
590 -- Processing continues
591 --
592 -- Post Failure
593 -- Error handled by procedure
594 --
595 -- Access Status
596 -- Internal table handler use only.
597 --
598 Procedure chk_oipl_stat_cd(p_oipl_id in number,
599 p_oipl_stat_cd in varchar2,
600 p_effective_date in date,
601 p_object_version_number in number) is
602 --
603 l_proc varchar2(72) := g_package||'chk_oipl_stat_cd';
604 l_api_updating boolean;
605 --
606 Begin
607 --
608 hr_utility.set_location('Entering:'||l_proc, 5);
609 --
610 l_api_updating := ben_cop_shd.api_updating
611 (p_oipl_id => p_oipl_id,
612 p_effective_date => p_effective_date,
613 p_object_version_number => p_object_version_number);
614 --
615 if (l_api_updating
616 and p_oipl_stat_cd
617 <> nvl(ben_cop_shd.g_old_rec.oipl_stat_cd,hr_api.g_varchar2)
618 or not l_api_updating)
619 and p_oipl_stat_cd is not null then
620 --
621 -- check if value of lookup falls within lookup type.
622 --
623 if hr_api.not_exists_in_hr_lookups
624 (p_lookup_type => 'BEN_STAT',
625 p_lookup_code => p_oipl_stat_cd,
626 p_effective_date => p_effective_date) then
627 --
628 -- raise error as does not exist as lookup
629 --
630 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
631 fnd_message.set_token('FIELD','p_oipl_stat_cd');
632 fnd_message.set_token('TYPE','BEN_STAT');
633 fnd_message.raise_error;
634 --
635 end if;
636 --
637 end if;
638 --
639 hr_utility.set_location('Leaving:'||l_proc,10);
640 --
641 end chk_oipl_stat_cd;
642 -- ----------------------------------------------------------------------------
643 -- |-----------------------< chk_pcp_dsgn_cd >-------------------------------|
644 -- ----------------------------------------------------------------------------
645 --
646 -- Description
647 -- This procedure is used to check that the lookup value is valid.
648 --
649 -- Pre Conditions
650 -- None.
651 --
652 -- In Parameters
653 -- oipl_id PK of record being inserted or updated.
654 -- pcp_dsgn_cd Value of lookup code.
655 -- effective_date effective date
656 -- object_version_number Object version number of record being
657 -- inserted or updated.
658 --
659 -- Post Success
660 -- Processing continues
661 --
662 -- Post Failure
663 -- Error handled by procedure
664 --
665 -- Access Status
666 -- Internal table handler use only.
667 --
668 Procedure chk_pcp_dsgn_cd(p_oipl_id in number,
669 p_pcp_dsgn_cd in varchar2,
670 p_effective_date in date,
671 p_object_version_number in number) is
672 --
673 l_proc varchar2(72) := g_package||'chk_pcp_dsgn_cd';
674 l_api_updating boolean;
675 --
676 Begin
677 --
678 hr_utility.set_location('Entering:'||l_proc, 5);
679 --
680 l_api_updating := ben_cop_shd.api_updating
681 (p_oipl_id => p_oipl_id,
682 p_effective_date => p_effective_date,
683 p_object_version_number => p_object_version_number);
684 --
685 if (l_api_updating
686 and p_pcp_dsgn_cd
687 <> nvl(ben_cop_shd.g_old_rec.pcp_dsgn_cd,hr_api.g_varchar2)
688 or not l_api_updating)
689 and p_pcp_dsgn_cd is not null then
690 --
691 -- check if value of lookup falls within lookup type.
692 --
693 if hr_api.not_exists_in_hr_lookups
694 (p_lookup_type => 'BEN_PCP_DSGN',
695 p_lookup_code => p_pcp_dsgn_cd,
696 p_effective_date => p_effective_date) then
697 --
698 -- raise error as does not exist as lookup
699 --
700 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
701 fnd_message.set_token('FIELD','p_pcp_dsgn_cd');
702 fnd_message.set_token('TYPE','BEN_PCP_DSGN');
703 fnd_message.raise_error;
704 --
705 end if;
706 --
707 end if;
708 --
709 hr_utility.set_location('Leaving:'||l_proc,10);
710 --
711 end chk_pcp_dsgn_cd;
712 --
713 -- -------------------------------------------------------------------------------
714 -- |-----------------------< chk_pcp_dpnt_dsgn_cd >-------------------------------|
715 -- -------------------------------------------------------------------------------
716 --
717 -- Description
718 -- This procedure is used to check that the lookup value is valid.
719 --
720 -- Pre Conditions
721 -- None.
722 --
723 -- In Parameters
724 -- oipl_id PK of record being inserted or updated.
725 -- pcp_dpnt_dsgn_cd Value of lookup code.
726 -- effective_date effective date
727 -- object_version_number Object version number of record being
728 -- inserted or updated.
729 --
730 -- Post Success
731 -- Processing continues
732 --
733 -- Post Failure
734 -- Error handled by procedure
735 --
736 -- Access Status
737 -- Internal table handler use only.
738 --
739 Procedure chk_pcp_dpnt_dsgn_cd(p_oipl_id in number,
740 p_pcp_dpnt_dsgn_cd in varchar2,
741 p_effective_date in date,
742 p_object_version_number in number) is
743 --
744 l_proc varchar2(72) := g_package||'chk_pcp_dpnt_dsgn_cd';
745 l_api_updating boolean;
746 --
747 Begin
748 --
749 hr_utility.set_location('Entering:'||l_proc, 5);
750 --
751 l_api_updating := ben_cop_shd.api_updating
752 (p_oipl_id => p_oipl_id,
753 p_effective_date => p_effective_date,
754 p_object_version_number => p_object_version_number);
755 --
756 if (l_api_updating
757 and p_pcp_dpnt_dsgn_cd
758 <> nvl(ben_cop_shd.g_old_rec.pcp_dpnt_dsgn_cd,hr_api.g_varchar2)
759 or not l_api_updating)
760 and p_pcp_dpnt_dsgn_cd is not null then
761 --
762 -- check if value of lookup falls within lookup type.
763 --
764 if hr_api.not_exists_in_hr_lookups
765 (p_lookup_type => 'BEN_PCP_DSGN',
766 p_lookup_code => p_pcp_dpnt_dsgn_cd,
767 p_effective_date => p_effective_date) then
768 --
769 -- raise error as does not exist as lookup
770 --
771 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
772 fnd_message.set_token('FIELD','p_pcp_dpnt_dsgn_cd');
773 fnd_message.set_token('TYPE','BEN_PCP_DSGN');
774 fnd_message.raise_error;
775 --
776 end if;
777 --
778 end if;
779 --
780 hr_utility.set_location('Leaving:'||l_proc,10);
781 --
782 end chk_pcp_dpnt_dsgn_cd;
783 -- ----------------------------------------------------------------------------
784 -- |-----------------------< chk_auto_enrt_flag >-----------------------------|
785 -- ----------------------------------------------------------------------------
786 --
787 -- Description
788 -- This procedure is used to check that the lookup value is valid.
789 --
790 -- Pre Conditions
791 -- None.
792 --
793 -- In Parameters
794 -- oipl_id PK of record being inserted or updated.
795 -- auto_enrt_flag Value of lookup code.
796 -- effective_date effective date
797 -- object_version_number Object version number of record being
798 -- inserted or updated.
799 --
800 -- Post Success
801 -- Processing continues
802 --
803 -- Post Failure
804 -- Error handled by procedure
805 --
806 -- Access Status
807 -- Internal table handler use only.
808 --
809 Procedure chk_auto_enrt_flag(p_oipl_id in number,
810 p_auto_enrt_flag in varchar2,
811 p_effective_date in date,
812 p_object_version_number in number) is
813 --
814 l_proc varchar2(72) := g_package||'chk_auto_enrt_flag';
815 l_api_updating boolean;
816 --
817 Begin
818 --
819 hr_utility.set_location('Entering:'||l_proc, 5);
820 --
821 l_api_updating := ben_cop_shd.api_updating
822 (p_oipl_id => p_oipl_id,
823 p_effective_date => p_effective_date,
824 p_object_version_number => p_object_version_number);
825 --
826 if (l_api_updating
827 and p_auto_enrt_flag
828 <> nvl(ben_cop_shd.g_old_rec.auto_enrt_flag,hr_api.g_varchar2)
829 or not l_api_updating)
830 and p_auto_enrt_flag is not null then
831 --
832 -- check if value of lookup falls within lookup type.
833 --
834 if hr_api.not_exists_in_hr_lookups
835 (p_lookup_type => 'YES_NO',
836 p_lookup_code => p_auto_enrt_flag,
837 p_effective_date => p_effective_date) then
838 --
839 -- raise error as does not exist as lookup
840 --
841 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
842 fnd_message.set_token('FIELD','p_auto_enrt_flag');
843 fnd_message.set_token('TYPE','YES_NO');
844 fnd_message.raise_error;
845 --
846 end if;
847 --
848 end if;
849 --
850 hr_utility.set_location('Leaving:'||l_proc,10);
851 --
852 end chk_auto_enrt_flag;
853 --
854 -- ----------------------------------------------------------------------------
855 -- |------------------< chk_vrfy_fmly_mmbr_cd >-------------------------------|
856 -- ----------------------------------------------------------------------------
857 --
858 -- Description
859 -- This procedure is used to check that the lookup value is valid.
860 --
861 -- Pre Conditions
862 -- None.
863 --
864 -- In Parameters
865 -- oipl_id PK of record being inserted or updated.
866 -- vrfy_fmly_mmbr_cd Value of lookup code.
867 -- effective_date effective date
868 -- object_version_number Object version number of record being
869 -- inserted or updated.
870 --
871 -- Post Success
872 -- Processing continues
873 --
874 -- Post Failure
875 -- Error handled by procedure
876 --
877 -- Access Status
878 -- Internal table handler use only.
879 --
880 Procedure chk_vrfy_fmly_mmbr_cd(p_oipl_id in number,
881 p_vrfy_fmly_mmbr_cd in varchar2,
882 p_effective_date in date,
883 p_object_version_number in number) is
884 --
885 l_proc varchar2(72) := g_package||'chk_vrfy_fmly_mmbr_cd';
886 l_api_updating boolean;
887 --
888 Begin
889 --
890 hr_utility.set_location('Entering:'||l_proc, 5);
891 --
892 l_api_updating := ben_cop_shd.api_updating
893 (p_oipl_id => p_oipl_id,
894 p_effective_date => p_effective_date,
895 p_object_version_number => p_object_version_number);
896 --
897 if (l_api_updating
898 and p_vrfy_fmly_mmbr_cd
899 <> nvl(ben_cop_shd.g_old_rec.vrfy_fmly_mmbr_cd,hr_api.g_varchar2)
900 or not l_api_updating)
901 and p_vrfy_fmly_mmbr_cd is not null then
902 --
903 -- check if value of lookup falls within lookup type.
904 --
905 if hr_api.not_exists_in_hr_lookups
906 (p_lookup_type => 'BEN_FMLY_MMBR',
907 p_lookup_code => p_vrfy_fmly_mmbr_cd,
908 p_effective_date => p_effective_date) then
909 --
910 -- raise error as does not exist as lookup
911 --
912 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
913 fnd_message.set_token('FIELD','p_vrfy_fmly_mmbr_cd');
914 fnd_message.set_token('TYPE','BEN_FMLY_MMBR');
915 fnd_message.raise_error;
916 --
917 end if;
918 --
919 end if;
920 --
921 hr_utility.set_location('Leaving:'||l_proc,10);
922 --
923 end chk_vrfy_fmly_mmbr_cd;
924 -- ---------------------------------------------------------------------
925 -- |------------------< chk_enrt_cd >-------------------------------|
926 -- ----------------------------------------------------------------------------
927 --
928 -- Description
929 -- This procedure is used to check that the lookup value is valid.
930 --
931 -- Pre Conditions
932 -- None.
933 --
934 -- In Parameters
935 -- oipl_id PK of record being inserted or updated.
936 -- enrt_cd Value of lookup code.
937 -- effective_date effective date
938 -- object_version_number Object version number of record being
939 -- inserted or updated.
940 --
941 -- Post Success
942 -- Processing continues
943 --
944 -- Post Failure
945 -- Error handled by procedure
946 --
947 -- Access Status
948 -- Internal table handler use only.
949 --
950 Procedure chk_enrt_cd(p_oipl_id in number,
951 p_enrt_cd in varchar2,
952 p_effective_date in date,
953 p_object_version_number in number) is
954 --
955 l_proc varchar2(72) := g_package||'chk_enrt_cd';
956 l_api_updating boolean;
957 --
958 Begin
959 --
960 hr_utility.set_location('Entering:'||l_proc, 5);
961 --
962 l_api_updating := ben_cop_shd.api_updating
963 (p_oipl_id => p_oipl_id,
964 p_effective_date => p_effective_date,
965 p_object_version_number => p_object_version_number);
966 --
967 if (l_api_updating
968 and p_enrt_cd
969 <> nvl(ben_cop_shd.g_old_rec.enrt_cd,hr_api.g_varchar2)
970 or not l_api_updating)
971 and p_enrt_cd is not null then
972 --
973 -- check if value of lookup falls within lookup type.
974 --
975 if hr_api.not_exists_in_hr_lookups
976 (p_lookup_type => 'BEN_ENRT',
977 p_lookup_code => p_enrt_cd,
978 p_effective_date => p_effective_date) then
979 --
980 -- raise error as does not exist as lookup
981 --
982 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
983 fnd_message.set_token('FIELD','p_enrt_cd');
984 fnd_message.set_token('TYPE','BEN_ENRT');
985 fnd_message.raise_error;
986 --
987 end if;
988 --
989 end if;
990 --
991 hr_utility.set_location('Leaving:'||l_proc,10);
992 --
993 end chk_enrt_cd;
994 -- ----------------------------------------------------------------------------
995 -- |-----------------------< chk_dflt_enrt_cd >-------------------------------|
996 -- ----------------------------------------------------------------------------
997 --
998 -- Description
999 -- This procedure is used to check that the lookup value is valid.
1000 --
1001 -- Pre Conditions
1002 -- None.
1003 --
1004 -- In Parameters
1005 -- oipl_id PK of record being inserted or updated.
1006 -- dflt_enrt_cd Value of lookup code.
1007 -- effective_date effective date
1008 -- object_version_number Object version number of record being
1009 -- inserted or updated.
1010 --
1011 -- Post Success
1012 -- Processing continues
1013 --
1014 -- Post Failure
1015 -- Error handled by procedure
1016 --
1017 -- Access Status
1018 -- Internal table handler use only.
1019 --
1020 Procedure chk_dflt_enrt_cd(p_oipl_id in number,
1021 p_dflt_enrt_cd in varchar2,
1022 p_effective_date in date,
1023 p_object_version_number in number) is
1024 --
1025 l_proc varchar2(72) := g_package||'chk_dflt_enrt_cd';
1026 l_api_updating boolean;
1027 --
1028 Begin
1029 --
1030 hr_utility.set_location('Entering:'||l_proc, 5);
1031 --
1032 l_api_updating := ben_cop_shd.api_updating
1033 (p_oipl_id => p_oipl_id,
1034 p_effective_date => p_effective_date,
1035 p_object_version_number => p_object_version_number);
1036 --
1037 if (l_api_updating
1038 and p_dflt_enrt_cd
1039 <> nvl(ben_cop_shd.g_old_rec.dflt_enrt_cd,hr_api.g_varchar2)
1040 or not l_api_updating)
1041 and p_dflt_enrt_cd is not null then
1042 --
1043 -- check if value of lookup falls within lookup type.
1044 --
1045 if hr_api.not_exists_in_hr_lookups
1046 (p_lookup_type => 'BEN_DFLT_ENRT',
1047 p_lookup_code => p_dflt_enrt_cd,
1048 p_effective_date => p_effective_date) then
1049 --
1050 -- raise error as does not exist as lookup
1051 --
1052 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1053 fnd_message.set_token('FIELD','p_dflt_enrt_cd');
1054 fnd_message.set_token('TYPE','BEN_DFLT_ENRT');
1055 fnd_message.raise_error;
1056 --
1057 end if;
1058 --
1059 end if;
1060 --
1061 hr_utility.set_location('Leaving:'||l_proc,10);
1062 --
1063 end chk_dflt_enrt_cd;
1064 -- ----------------------------------------------------------------------------
1065 -- |--------------------------< chk_auto_enrt_mthd_rl >-----------------------|
1066 -- ----------------------------------------------------------------------------
1067 --
1068 -- Description
1069 -- This procedure is used to check that the Formula Rule is valid.
1070 --
1071 -- Pre Conditions
1072 -- None.
1073 --
1074 -- In Parameters
1075 -- oipl_id PK of record being inserted or updated.
1076 -- auto_enrt_mthd_rl Value of formula rule id.
1077 -- effective_date effective date
1078 -- object_version_number Object version number of record being
1079 -- inserted or updated.
1080 --
1081 -- Post Success
1082 -- Processing continues
1083 --
1084 -- Post Failure
1085 -- Error handled by procedure
1086 --
1087 -- Access Status
1088 -- Internal table handler use only.
1089 --
1090 Procedure chk_auto_enrt_mthd_rl(p_oipl_id in number,
1091 p_business_group_id in number,
1092 p_auto_enrt_mthd_rl in number,
1093 p_effective_date in date,
1094 p_object_version_number in number) is
1095 --
1096 l_proc varchar2(72) := g_package||'chk_auto_enrt_mthd_rl';
1097 l_api_updating boolean;
1098 --
1099 Begin
1100 --
1101 hr_utility.set_location('Entering:'||l_proc, 5);
1102 --
1103 l_api_updating := ben_cop_shd.api_updating
1104 (p_oipl_id => p_oipl_id,
1105 p_effective_date => p_effective_date,
1106 p_object_version_number => p_object_version_number);
1107 --
1108 if (l_api_updating
1109 and nvl(p_auto_enrt_mthd_rl,hr_api.g_number)
1110 <> ben_cop_shd.g_old_rec.auto_enrt_mthd_rl
1111 or not l_api_updating)
1112 and p_auto_enrt_mthd_rl is not null then
1113 --
1114 -- check if value of formula rule is valid.
1115 --
1116 if not benutils.formula_exists
1117 (p_formula_id => p_auto_enrt_mthd_rl,
1118 p_formula_type_id => -146,
1119 p_business_group_id => p_business_group_id,
1120 p_effective_date => p_effective_date) then
1121 --
1122 -- raise error
1123 --
1124 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1125 fnd_message.set_token('ID',p_auto_enrt_mthd_rl);
1126 fnd_message.set_token('TYPE_ID',-146);
1127 fnd_message.raise_error;
1128 --
1129 end if;
1130 --
1131 end if;
1132 --
1133 hr_utility.set_location('Leaving:'||l_proc,10);
1134 --
1135 end chk_auto_enrt_mthd_rl;
1136 --
1137 -- ----------------------------------------------------------------------------
1138 -- |--------------------------< chk_dflt_enrt_det_rl >------------------------|
1139 -- ----------------------------------------------------------------------------
1140 --
1141 -- Description
1142 -- This procedure is used to check that the Formula Rule is valid.
1143 --
1144 -- Pre Conditions
1145 -- None.
1146 --
1147 -- In Parameters
1148 -- oipl_id PK of record being inserted or updated.
1149 -- dflt_enrt_det_rl Value of formula rule id.
1150 -- effective_date effective date
1151 -- object_version_number Object version number of record being
1152 -- inserted or updated.
1153 --
1154 -- Post Success
1155 -- Processing continues
1156 --
1157 -- Post Failure
1158 -- Error handled by procedure
1159 --
1160 -- Access Status
1161 -- Internal table handler use only.
1162 --
1163 Procedure chk_dflt_enrt_det_rl(p_oipl_id in number,
1164 p_business_group_id in number,
1165 p_dflt_enrt_det_rl in number,
1166 p_effective_date in date,
1167 p_object_version_number in number) is
1168 --
1169 l_proc varchar2(72) := g_package||'chk_dflt_enrt_det_rl';
1170 l_api_updating boolean;
1171 --
1172 --
1173 Begin
1174 --
1175 hr_utility.set_location('Entering:'||l_proc, 5);
1176 --
1177 l_api_updating := ben_cop_shd.api_updating
1178 (p_oipl_id => p_oipl_id,
1179 p_effective_date => p_effective_date,
1180 p_object_version_number => p_object_version_number);
1181 --
1182 if (l_api_updating
1183 and nvl(p_dflt_enrt_det_rl,hr_api.g_number)
1184 <> ben_cop_shd.g_old_rec.dflt_enrt_det_rl
1185 or not l_api_updating)
1186 and p_dflt_enrt_det_rl is not null then
1187 --
1188 -- check if value of formula rule is valid.
1189 --
1190 if not benutils.formula_exists
1191 (p_formula_id => p_dflt_enrt_det_rl,
1192 p_formula_type_id => -32,
1193 p_business_group_id => p_business_group_id,
1194 p_effective_date => p_effective_date) then
1195 --
1196 -- raise error
1197 --
1198 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1199 fnd_message.set_token('ID',p_dflt_enrt_det_rl);
1200 fnd_message.set_token('TYPE_ID',-32);
1201 fnd_message.raise_error;
1202 --
1203 end if;
1204 --
1205 end if;
1206 --
1207 hr_utility.set_location('Leaving:'||l_proc,10);
1208 --
1209 end chk_dflt_enrt_det_rl;
1210 -- ----------------------------------------------------------------------------
1211 -- |--------------------------< chk_mndtry_rl >-------------------------------|
1212 -- ----------------------------------------------------------------------------
1213 --
1214 -- Description
1215 -- This procedure is used to check that the Formula Rule is valid.
1216 --
1217 -- Pre Conditions
1218 -- None.
1219 --
1220 -- In Parameters
1221 -- oipl_id PK of record being inserted or updated.
1222 -- mndtry_rl Value of formula rule id.
1223 -- effective_date effective date
1224 -- object_version_number Object version number of record being
1225 -- inserted or updated.
1226 --
1227 -- Post Success
1228 -- Processing continues
1229 --
1230 -- Post Failure
1231 -- Error handled by procedure
1232 --
1233 -- Access Status
1234 -- Internal table handler use only.
1235 --
1236 Procedure chk_mndtry_rl(p_oipl_id in number,
1237 p_business_group_id in number,
1238 p_mndtry_rl in number,
1239 p_effective_date in date,
1240 p_object_version_number in number) is
1241 --
1242 l_proc varchar2(72) := g_package||'chk_mndtry_rl';
1243 l_api_updating boolean;
1244 --
1245 Begin
1246 --
1247 hr_utility.set_location('Entering:'||l_proc, 5);
1248 --
1249 l_api_updating := ben_cop_shd.api_updating
1250 (p_oipl_id => p_oipl_id,
1251 p_effective_date => p_effective_date,
1252 p_object_version_number => p_object_version_number);
1253 --
1254 if (l_api_updating
1255 and nvl(p_mndtry_rl,hr_api.g_number)
1256 <> ben_cop_shd.g_old_rec.mndtry_rl
1257 or not l_api_updating)
1258 and p_mndtry_rl is not null then
1259 --
1260 -- check if value of formula rule is valid.
1261 --
1262 if not benutils.formula_exists
1263 (p_formula_id => p_mndtry_rl,
1264 p_formula_type_id => -159,
1265 p_business_group_id => p_business_group_id,
1266 p_effective_date => p_effective_date) then
1267 --
1268 -- raise error
1269 --
1270 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1271 fnd_message.set_token('ID',p_mndtry_rl);
1272 fnd_message.set_token('TYPE_ID',-159);
1273 fnd_message.raise_error;
1274 --
1275 end if;
1276 --
1277 end if;
1278 --
1279 hr_utility.set_location('Leaving:'||l_proc,10);
1280 --
1281 end chk_mndtry_rl;
1282 -- ----------------------------------------------------------------------------
1283 -- |-------------------< chk_rqd_perd_enrt_nenrt_rl >-------------------------|
1284 -- ----------------------------------------------------------------------------
1285 --
1286 -- Description
1287 -- This procedure is used to check that the Formula Rule is valid.
1288 --
1289 -- Pre Conditions
1290 -- None.
1291 --
1292 -- In Parameters
1293 -- oipl_id PK of record being inserted or updated.
1294 -- rqd_perd_enrt_nenrt_rl Value of formula rule id.
1295 -- effective_date effective date
1296 -- object_version_number Object version number of record being
1297 -- inserted or updated.
1298 --
1299 -- Post Success
1300 -- Processing continues
1301 --
1302 -- Post Failure
1303 -- Error handled by procedure
1304 --
1305 -- Access Status
1306 -- Internal table handler use only.
1307 --
1308 Procedure chk_rqd_perd_enrt_nenrt_rl(p_oipl_id in number,
1309 p_business_group_id in number,
1310 p_rqd_perd_enrt_nenrt_rl in number,
1311 p_effective_date in date,
1312 p_object_version_number in number) is
1313 --
1314 l_proc varchar2(72) := g_package||'chk_rqd_perd_enrt_nenrt_rl';
1315 l_api_updating boolean;
1316 --
1317 Begin
1318 --
1319 hr_utility.set_location('Entering:'||l_proc, 5);
1320 --
1321 l_api_updating := ben_cop_shd.api_updating
1322 (p_oipl_id => p_oipl_id,
1323 p_effective_date => p_effective_date,
1324 p_object_version_number => p_object_version_number);
1325 --
1326 if (l_api_updating
1327 and nvl(p_rqd_perd_enrt_nenrt_rl,hr_api.g_number)
1328 <> ben_cop_shd.g_old_rec.rqd_perd_enrt_nenrt_rl
1329 or not l_api_updating)
1330 and p_rqd_perd_enrt_nenrt_rl is not null then
1331 --
1332 -- check if value of formula rule is valid.
1333 --
1334 if not benutils.formula_exists
1335 (p_formula_id => p_rqd_perd_enrt_nenrt_rl,
1336 p_formula_type_id => -513,
1337 p_business_group_id => p_business_group_id,
1338 p_effective_date => p_effective_date) then
1339 --
1340 -- raise error
1341 --
1342 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1343 fnd_message.set_token('ID',p_rqd_perd_enrt_nenrt_rl);
1344 fnd_message.set_token('TYPE_ID',-513);
1345 fnd_message.raise_error;
1346 --
1347 end if;
1348 --
1349 end if;
1350 --
1351 hr_utility.set_location('Leaving:'||l_proc,10);
1352 --
1353 end chk_rqd_perd_enrt_nenrt_rl;
1354 -- ----------------------------------------------------------------------------
1355 -- |------------------------< chk_postelcn_edit_rl >--------------------------|
1356 -- ----------------------------------------------------------------------------
1357 --
1358 -- Description
1359 -- This procedure is used to check that the Formula Rule is valid.
1360 --
1361 -- Pre Conditions
1362 -- None.
1363 --
1364 -- In Parameters
1365 -- oipl_id PK of record being inserted or updated.
1366 -- postelcn_edit_rl Value of formula rule id.
1367 -- effective_date effective date
1368 -- object_version_number Object version number of record being
1369 -- inserted or updated.
1370 --
1371 -- Post Success
1372 -- Processing continues
1373 --
1374 -- Post Failure
1375 -- Error handled by procedure
1376 --
1377 -- Access Status
1378 -- Internal table handler use only.
1379 --
1380 Procedure chk_postelcn_edit_rl(p_oipl_id in number,
1381 p_business_group_id in number,
1382 p_postelcn_edit_rl in number,
1383 p_effective_date in date,
1384 p_object_version_number in number) is
1385 --
1386 l_proc varchar2(72) := g_package||'chk_postelcn_edit_rl';
1387 l_api_updating boolean;
1388 --
1389 Begin
1390 --
1391 hr_utility.set_location('Entering:'||l_proc, 5);
1392 --
1393 l_api_updating := ben_cop_shd.api_updating
1394 (p_oipl_id => p_oipl_id,
1395 p_effective_date => p_effective_date,
1396 p_object_version_number => p_object_version_number);
1397 --
1398 if (l_api_updating
1399 and nvl(p_postelcn_edit_rl,hr_api.g_number)
1400 <> ben_cop_shd.g_old_rec.postelcn_edit_rl
1401 or not l_api_updating)
1402 and p_postelcn_edit_rl is not null then
1403 --
1404 -- check if value of formula rule is valid.
1405 --
1406 if not benutils.formula_exists
1407 (p_formula_id => p_postelcn_edit_rl,
1408 p_formula_type_id => -215,
1409 p_business_group_id => p_business_group_id,
1410 p_effective_date => p_effective_date) then
1411 --
1412 -- raise error
1413 --
1414 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1415 fnd_message.set_token('ID',p_postelcn_edit_rl);
1416 fnd_message.set_token('TYPE_ID',-215);
1417 fnd_message.raise_error;
1418 --
1419 end if;
1420 --
1421 end if;
1422 --
1423 hr_utility.set_location('Leaving:'||l_proc,10);
1424 --
1425 end chk_postelcn_edit_rl;
1426 -- ----------------------------------------------------------------------------
1427 -- |--------------------------< chk_vrfy_fmly_mmbr_rl >-----------------------|
1428 -- ----------------------------------------------------------------------------
1429 --
1430 -- Description
1431 -- This procedure is used to check that the Formula Rule is valid.
1432 --
1433 -- Pre Conditions
1434 -- None.
1435 --
1436 -- In Parameters
1437 -- oipl_id PK of record being inserted or updated.
1438 -- vrfy_fmly_mmbr_rl Value of formula rule id.
1439 -- effective_date effective date
1440 -- object_version_number Object version number of record being
1441 -- inserted or updated.
1442 --
1443 -- Post Success
1444 -- Processing continues
1445 --
1446 -- Post Failure
1447 -- Error handled by procedure
1448 --
1449 -- Access Status
1450 -- Internal table handler use only.
1451 --
1452 Procedure chk_vrfy_fmly_mmbr_rl(p_oipl_id in number,
1453 p_business_group_id in number,
1454 p_vrfy_fmly_mmbr_rl in number,
1455 p_effective_date in date,
1456 p_object_version_number in number) is
1457 --
1458 l_proc varchar2(72) := g_package||'chk_vrfy_fmly_mmbr_rl';
1459 l_api_updating boolean;
1460 --
1461 Begin
1462 --
1463 hr_utility.set_location('Entering:'||l_proc, 5);
1464 --
1465 l_api_updating := ben_cop_shd.api_updating
1466 (p_oipl_id => p_oipl_id,
1467 p_effective_date => p_effective_date,
1468 p_object_version_number => p_object_version_number);
1469 --
1470 if (l_api_updating
1471 and nvl(p_vrfy_fmly_mmbr_rl,hr_api.g_number)
1472 <> ben_cop_shd.g_old_rec.vrfy_fmly_mmbr_rl
1473 or not l_api_updating)
1474 and p_vrfy_fmly_mmbr_rl is not null then
1475 --
1476 -- check if value of formula rule is valid.
1477 --
1478 if not benutils.formula_exists
1479 (p_formula_id => p_vrfy_fmly_mmbr_rl,
1480 p_formula_type_id => -21,
1481 p_business_group_id => p_business_group_id,
1482 p_effective_date => p_effective_date) then
1483 --
1484 -- raise error
1485 --
1486 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1487 fnd_message.set_token('ID',p_vrfy_fmly_mmbr_rl);
1488 fnd_message.set_token('TYPE_ID',-21);
1489 fnd_message.raise_error;
1490 --
1491 end if;
1492 --
1493 end if;
1494 --
1495 hr_utility.set_location('Leaving:'||l_proc,10);
1496 --
1497 end chk_vrfy_fmly_mmbr_rl;
1498 --
1499 -- ----------------------------------------------------------------------------
1500 -- |--------------------------< chk_enrt_rl >-----------------------|
1501 -- ----------------------------------------------------------------------------
1502 --
1503 -- Description
1504 -- This procedure is used to check that the Formula Rule is valid.
1505 --
1506 -- Pre Conditions
1507 -- None.
1508 --
1509 -- In Parameters
1510 -- oipl_id PK of record being inserted or updated.
1511 -- enrt_rl Value of formula rule id.
1512 -- effective_date effective date
1513 -- object_version_number Object version number of record being
1514 -- inserted or updated.
1515 --
1516 -- Post Success
1517 -- Processing continues
1518 --
1519 -- Post Failure
1520 -- Error handled by procedure
1521 --
1522 -- Access Status
1523 -- Internal table handler use only.
1524 --
1525 Procedure chk_enrt_rl(p_oipl_id in number,
1526 p_business_group_id in number,
1527 p_enrt_rl in number,
1528 p_effective_date in date,
1529 p_object_version_number in number) is
1530 --
1531 l_proc varchar2(72) := g_package||'chk_enrt_rl';
1532 l_api_updating boolean;
1533 --
1534 Begin
1535 --
1536 hr_utility.set_location('Entering:'||l_proc, 5);
1537 --
1538 l_api_updating := ben_cop_shd.api_updating
1539 (p_oipl_id => p_oipl_id,
1540 p_effective_date => p_effective_date,
1541 p_object_version_number => p_object_version_number);
1542 --
1543 if (l_api_updating
1544 and nvl(p_enrt_rl,hr_api.g_number)
1545 <> ben_cop_shd.g_old_rec.enrt_rl
1546 or not l_api_updating)
1547 and p_enrt_rl is not null then
1548 --
1549 -- check if value of formula rule is valid.
1550 --
1551 if not benutils.formula_exists
1552 (p_formula_id => p_enrt_rl,
1553 p_formula_type_id => -393,
1554 p_business_group_id => p_business_group_id,
1555 p_effective_date => p_effective_date) then
1556 --
1557 -- raise error
1558 --
1559 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1560 fnd_message.set_token('ID',p_enrt_rl);
1561 fnd_message.set_token('TYPE_ID',-393);
1562 fnd_message.raise_error;
1563 --
1564 end if;
1565 --
1566 end if;
1567 --
1568 hr_utility.set_location('Leaving:'||l_proc,10);
1569 --
1570 end chk_enrt_rl;
1571 -- ----------------------------------------------------------------------------
1572 -- |--------------------------< chk_cd_rl_combination >-------------------------------|
1573 -- ----------------------------------------------------------------------------
1574 --
1575 -- Description
1576 -- This procedure is used to check the code is RULE then the rule must be
1577 -- defined else it should not be.
1578 --
1579 -- Pre Conditions
1580 -- None.
1581 --
1582 -- In Parameters
1583 -- p_dflt_enrt_det_rl in varchar2
1584 -- p_dflt_enrt_cd in number
1585 --
1586 -- object_version_number Object version number of record being
1587 -- inserted or updated.
1588 -- Post Success
1589 -- Processing continues
1590 --
1591 -- Post Failure
1592 -- Error handled by procedure
1593 --
1594 -- Access Status
1595 -- Internal table handler use only.
1596 --
1597 procedure chk_cd_rl_combination
1598 (
1599 p_dflt_enrt_cd in varchar2,
1600 p_dflt_enrt_det_rl in number ) IS
1601 l_proc varchar2(72) := g_package||'chk_cd_rl_combination';
1602 BEGIN
1603 hr_utility.set_location('Entering:'||l_proc, 5);
1604 --
1605 if (p_dflt_enrt_cd <> 'RL' and p_dflt_enrt_det_rl is not null)
1606 then
1607 fnd_message.set_name('BEN','BEN_91730_NO_RULE');
1608 fnd_message.raise_error;
1609 end if;
1610 if (p_dflt_enrt_cd = 'RL' and p_dflt_enrt_det_rl is null)
1611 then
1612 fnd_message.set_name('BEN','BEN_91731_RULE');
1613 fnd_message.raise_error;
1614 end if;
1615 --
1616 -- Leaving Procedure.
1617 --
1618 hr_utility.set_location('Leaving:'||l_proc,10);
1619 END;
1620 --
1621 -- ----------------------------------------------------------------------------
1622 -- |-----------------------< chk_auto_enrt_and_mthd >--------------------------|
1623 -- ----------------------------------------------------------------------------
1624 --
1625 -- Description
1626 -- This procedure is used to check that if the automatic enrollment flag
1627 -- flag is checked, the plan record for the option must have the enrollment
1628 -- method = "Automatic".
1629 --
1630 -- Pre Conditions
1631 -- None.
1632 --
1633 -- In Parameters
1634 -- oipl_id PK of record being inserted or updated.
1635 -- auto_enrt_flag Automatic Enrollment Flag.
1636 -- pl_id pl_id.
1637 -- effective_date Session date of record.
1638 -- business_group_id Business group id of record being inserted.
1639 --
1640 -- Post Success
1641 -- Processing continues
1642 --
1643 -- Post Failure
1644 -- Errors handled by the procedure
1645 --
1646 -- Access Status
1647 -- Internal table handler use only.
1648 --
1649 Procedure chk_auto_enrt_and_mthd(p_oipl_id in number,
1650 p_auto_enrt_flag in varchar2,
1651 p_pl_id in number,
1652 p_effective_date in date,
1653 p_business_group_id in number) is
1654 --
1655 l_proc varchar2(72) := g_package||'chk_auto_enrt_and_mthd';
1656 l_dummy varchar2(1);
1657 --
1658 cursor c1 is
1659 select null
1660 from ben_pl_f pl
1661 where pl.pl_id = p_pl_id
1662 and pl.enrt_mthd_cd = 'A'
1663 and p_effective_date between pl.effective_start_date
1664 and pl.effective_end_date
1665 and pl.business_group_id + 0 = p_business_group_id;
1666 --
1667 Begin
1668 --
1669 hr_utility.set_location('Entering:'||l_proc, 5);
1670 --
1671 if p_auto_enrt_flag = 'Y' then
1672 open c1;
1673 --
1674 fetch c1 into l_dummy;
1675 if c1%notfound then
1676 --
1677 close c1;
1678 --
1679 -- raise an error as the Enrollment Method Code has a value of
1680 -- "automatic".
1681 --
1682 fnd_message.set_name('BEN','BEN_91967_AUTO_ENRT_AND_MTHD');
1683 fnd_message.raise_error;
1684 --
1685 end if;
1686 --
1687 close c1;
1688 end if;
1689 --
1690 --
1691 hr_utility.set_location('Leaving:'||l_proc, 10);
1692 --
1693 End chk_auto_enrt_and_mthd;
1694 --
1695 -- ----------------------------------------------------------------------------
1696 -- |---------------------< chk_auto_enrt_and_flags >--------------------------|
1697 -- ----------------------------------------------------------------------------
1698 --
1699 -- Description
1700 -- This procedure is used to check that if the automatic enrollment flag
1701 -- flag is checked, the mandatory flag and default flag must not be checked.
1702 --
1703 -- Pre Conditions
1704 -- None.
1705 --
1706 -- In Parameters
1707 -- auto_enrt_flag Automatic Enrollment Flag.
1708 -- mndtry_flag Mandatory Flag.
1709 -- dflt_flag Default Flag.
1710 --
1711 -- Post Success
1712 -- Processing continues
1713 --
1714 -- Post Failure
1715 -- Errors handled by the procedure
1716 --
1717 -- Access Status
1718 -- Internal table handler use only.
1719 --
1720 Procedure chk_auto_enrt_and_flags(p_auto_enrt_flag in varchar2,
1721 p_mndtry_flag in varchar2,
1722 p_dflt_flag in varchar2) is
1723 --
1724 l_proc varchar2(72) := g_package||'chk_auto_enrt_and_flags';
1725 --
1726 Begin
1727 --
1728 hr_utility.set_location('Entering:'||l_proc, 5);
1729 --
1730 if p_auto_enrt_flag = 'Y' and
1731 (p_mndtry_flag = 'Y' or p_dflt_flag = 'Y') then
1732 --
1733 -- raise an error as the Mandatory Flag or Default Flag has a value
1734 -- of 'Y'.
1735 --
1736 fnd_message.set_name('BEN','BEN_91969_AUTO_ENRT_AND_FLAGS');
1737 fnd_message.raise_error;
1738 --
1739 --
1740 end if;
1741 --
1742 --
1743 hr_utility.set_location('Leaving:'||l_proc, 10);
1744 --
1745 End chk_auto_enrt_and_flags;
1746 --
1747 -- ----------------------------------------------------------------------------
1748 -- |-----------------------< chk_plan_pcp_before_oipl >--------------------------|
1749 -- ----------------------------------------------------------------------------
1750 --
1751 -- Description
1752 -- This procedure is used to check that an oipl cannot have its pcp_dsgn_cd
1753 -- nor its pcp_dpnt_dsgn_cd set to not null until the corresponding plan has
1754 -- a ben_pl_pcp row attached to it.
1755 --
1756 -- Pre Conditions
1757 -- None.
1758 --
1759 -- In Parameters
1760 -- oipl_id PK of record being inserted or updated.
1761 -- pl_id FK of record being inserted or updated.
1762 -- pcp_dsgn_cd pcp designation code
1763 -- pcp_dpnt_dsgn_cd pcp dependent designation code
1764 -- effective_date Session date of record.
1765 -- business_group_id Business group id of record being inserted.
1766 -- object_version_number Object version number of record being
1767 -- inserted or updated.
1768 --
1769 -- Post Success
1770 -- Processing continues
1771 --
1772 -- Post Failure
1773 -- Errors handled by the procedure
1774 --
1775 -- Access Status
1776 -- Internal table handler use only.
1777 --
1778 Procedure chk_plan_pcp_before_oipl(p_oipl_id in number,
1779 p_pl_id in number,
1780 p_pcp_dsgn_cd in varchar2,
1781 p_pcp_dpnt_dsgn_cd in varchar2,
1782 p_effective_date in date,
1783 p_business_group_id in number,
1784 p_object_version_number in number) is
1785 --
1786 l_proc varchar2(72) := g_package||'chk_plan_pcp_before_oipl';
1787 l_api_updating boolean;
1788 l_dummy varchar2(1);
1789 --
1790 cursor c1 is
1791 select null
1792 from ben_pl_pcp a
1793 where a.pl_id = p_pl_id
1794 and a.business_group_id = p_business_group_id;
1795 --
1796 Begin
1797 --
1798 hr_utility.set_location('Entering:'||l_proc, 5);
1799 --
1800 l_api_updating := ben_cop_shd.api_updating
1801 (p_effective_date => p_effective_date,
1802 p_oipl_id => p_oipl_id,
1803 p_object_version_number => p_object_version_number);
1804 --
1805 if (l_api_updating
1806 and (p_pcp_dsgn_cd is not null or p_pcp_dpnt_dsgn_cd is not null)) then
1807 --
1808 open c1;
1809 --
1810 fetch c1 into l_dummy;
1811 if c1%notfound then
1812 --
1813 close c1;
1814 --
1815 -- raise an error as there is no ben_pl_pcp row for the corresponding plan.
1816 --
1817 fnd_message.set_name('BEN','BEN_92592_NO_PL_PCP_ROW');
1818 fnd_message.raise_error;
1819 --
1820 end if;
1821 --
1822 close c1;
1823 --
1824 end if;
1825 --
1826 hr_utility.set_location('Leaving:'||l_proc, 10);
1827 --
1828 End chk_plan_pcp_before_oipl;
1829 --
1830 -- ----------------------------------------------------------------------------
1831 -- |-----------------------< chk_plan_oipl_mutexcl >--------------------------|
1832 -- ----------------------------------------------------------------------------
1833 --
1834 -- Description
1835 -- This procedure is used to check that the oipl is mutually exclusive
1836 -- for the actl_prem_id. A plan cannot exist with this actl_prem_id
1837 -- due to the ARC relationship on ben_actl_prem_f.
1838 --
1839 -- Pre Conditions
1840 -- None.
1841 --
1842 -- In Parameters
1843 -- oipl_id PK of record being inserted or updated.
1844 -- actl_prem_id actl_prem_id.
1845 -- effective_date Session date of record.
1846 -- business_group_id Business group id of record being inserted.
1847 -- object_version_number Object version number of record being
1848 -- inserted or updated.
1849 --
1850 -- Post Success
1851 -- Processing continues
1852 --
1853 -- Post Failure
1854 -- Errors handled by the procedure
1855 --
1856 -- Access Status
1857 -- Internal table handler use only.
1858 --
1859 Procedure chk_plan_oipl_mutexcl(p_oipl_id in number,
1860 p_actl_prem_id in number,
1861 p_effective_date in date,
1862 p_business_group_id in number,
1863 p_object_version_number in number) is
1864 --
1865 l_proc varchar2(72) := g_package||'chk_plan_oipl_mutexcl';
1866 l_api_updating boolean;
1867 l_dummy varchar2(1);
1868 --
1869 cursor c1 is
1870 select null
1871 from ben_pl_f a
1872 where a.business_group_id +0 = p_business_group_id
1873 and a.actl_prem_id = p_actl_prem_id
1874 and p_effective_date
1875 between a.effective_start_date
1876 and a.effective_end_date;
1877 --
1878 Begin
1879 --
1880 hr_utility.set_location('Entering:'||l_proc, 5);
1881 --
1882 l_api_updating := ben_cop_shd.api_updating
1883 (p_effective_date => p_effective_date,
1884 p_oipl_id => p_oipl_id,
1885 p_object_version_number => p_object_version_number);
1886 --
1887 if (l_api_updating
1888 and p_actl_prem_id is not null) then
1889 --
1890 -- Check if actl_prem_id is mutually exclusive.
1891 --
1892 open c1;
1893 --
1894 fetch c1 into l_dummy;
1895 if c1%found then
1896 --
1897 close c1;
1898 --
1899 -- raise an error as this actl_prem_id has been assigned to plan(s).
1900 --
1901 fnd_message.set_name('BEN','BEN_91611_PLAN_OPTION_EXCL2');
1902 fnd_message.raise_error;
1903 --
1904 end if;
1905 --
1906 close c1;
1907 --
1908 end if;
1909 --
1910 hr_utility.set_location('Leaving:'||l_proc, 10);
1911 --
1912 End chk_plan_oipl_mutexcl;
1913 --
1914 -- ----------------------------------------------------------------------------
1915 -- |--------------------------< chk_opt_id >----------------------------------|
1916 -- ----------------------------------------------------------------------------
1917 --
1918 -- Description
1919 -- This procedure checks that option id is unique for a plan
1920 --
1921 -- Pre-Conditions
1922 -- None.
1923 --
1924 -- In Parameters
1925 -- p_oipl_id PK
1926 -- p_opt_id ID of FK column
1927 -- p_pl_id
1928 -- p_effective_date session date
1929 -- p_object_version_number object version number
1930 --
1931 -- Post Success
1932 -- Processing continues
1933 --
1934 -- Post Failure
1935 -- Error raised.
1936 --
1937 -- Access Status
1938 -- Internal table handler use only.
1939 --
1940 Procedure chk_opt_id (p_oipl_id in number,
1941 p_pl_id in number,
1942 p_opt_id in number,
1943 p_validation_start_date in date,
1944 p_validation_end_date in date,
1945 p_effective_date in date,
1946 p_business_group_id in number,
1947 p_object_version_number in number) is
1948 --
1949 l_proc varchar2(72) := g_package||'chk_opt_id';
1950 l_api_updating boolean;
1951 l_exists varchar2(1);
1952 --
1953 CURSOR c1 IS
1954 SELECT NULL
1955 FROM ben_oipl_f cop
1956 WHERE cop.opt_id = p_opt_id
1957 AND cop.pl_id = p_pl_id
1958 AND cop.oipl_id <> nvl(p_oipl_id, hr_api.g_number)
1959 AND cop.business_group_id = p_business_group_id
1960 AND p_validation_start_date <= cop.effective_end_date
1961 AND p_validation_end_date >= cop.effective_start_date
1962 --
1963 -- Start of CAGR band aid.
1964 --
1965 AND NOT EXISTS(SELECT 'x'
1966 FROM ben_pl_f pln
1967 , ben_pl_typ_f ptp
1968 WHERE pln.pl_id = p_pl_id
1969 AND pln.pl_typ_id = ptp.pl_typ_id
1970 AND pln.business_group_id = p_business_group_id
1971 AND p_validation_start_date <= pln.effective_end_date
1972 AND p_validation_end_date >= pln.effective_start_date
1973 AND ptp.opt_typ_cd = 'CAGR'
1974 AND ptp.business_group_id = p_business_group_id
1975 AND p_validation_start_date <= ptp.effective_end_date
1976 AND p_validation_end_date >= ptp.effective_start_date);
1977 --
1978 -- End of CAGR band aid.
1979 --
1980 Begin
1981 --
1982 hr_utility.set_location('Entering:'||l_proc,5);
1983 --
1984 l_api_updating := ben_cop_shd.api_updating
1985 (p_oipl_id => p_oipl_id,
1986 p_effective_date => p_effective_date,
1987 p_object_version_number => p_object_version_number);
1988 --
1989 if (l_api_updating
1990 and nvl(p_opt_id,hr_api.g_number)
1991 <> nvl(ben_cop_shd.g_old_rec.opt_id, hr_api.g_number)
1992 or not l_api_updating) then
1993 --
1994 --
1995 open c1;
1996 --
1997 fetch c1 into l_exists;
1998 if c1%found then
1999 close c1;
2000 --
2001 -- raise error as this beneficiary already exists for this enrt rslt
2002 --
2003 fnd_message.set_name('BEN','BEN_91845_DUP_OPT');
2004 fnd_message.raise_error;
2005 --
2006 end if;
2007 --
2008 close c1;
2009 --
2010 end if;
2011 --
2012 hr_utility.set_location('Leaving:'||l_proc,10);
2013 --
2014 End chk_opt_id;
2015 --
2016 --
2017 -- ----------------------------------------------------------------------------
2018 -- |-------------------< chk_ordr_num_unq_in_plan >---------------------------|
2019 -- ----------------------------------------------------------------------------
2020 --
2021 -- Description
2022 -- This procedure checks that ordr_num is unique for a plan
2023 --
2024 -- Pre-Conditions
2025 -- None.
2026 --
2027 -- In Parameters
2028 -- p_oipl_id PK
2029 -- p_ordr_num
2030 -- p_pl_id
2031 -- p_effective_date session date
2032 -- p_object_version_number object version number
2033 --
2034 -- Post Success
2035 -- Processing continues
2036 --
2037 -- Post Failure
2038 -- Error raised.
2039 --
2040 -- Access Status
2041 -- Internal table handler use only.
2042 --
2043 Procedure chk_ordr_num_unq_in_plan
2044 (p_oipl_id in number,
2045 p_pl_id in number,
2046 p_ordr_num in number,
2047 p_validation_start_date in date,
2048 p_validation_end_date in date,
2049 p_effective_date in date,
2050 p_business_group_id in number,
2051 p_object_version_number in number) is
2052 --
2053 l_proc varchar2(72) := g_package||'chk_ordr_num_unq_in_plan';
2054 l_api_updating boolean;
2055 l_exists varchar2(1);
2056 --
2057 cursor c1 is
2058 select null
2059 from ben_oipl_f
2060 where ordr_num = p_ordr_num
2061 and pl_id = p_pl_id
2062 and oipl_id <> nvl(p_oipl_id, hr_api.g_number)
2063 and business_group_id + 0 = p_business_group_id
2064 and p_validation_start_date <= effective_end_date
2065 and p_validation_end_date >= effective_start_date;
2066 --
2067 Begin
2068 --
2069 hr_utility.set_location('Entering:'||l_proc,5);
2070 --
2071 l_api_updating := ben_cop_shd.api_updating
2072 (p_oipl_id => p_oipl_id,
2073 p_effective_date => p_effective_date,
2074 p_object_version_number => p_object_version_number);
2075 --
2076 if (l_api_updating
2077 and nvl(p_ordr_num,hr_api.g_number)
2078 <> nvl(ben_cop_shd.g_old_rec.ordr_num, hr_api.g_number)
2079 or not l_api_updating) then
2080 --
2081 --
2082 open c1;
2083 --
2084 fetch c1 into l_exists;
2085 if c1%found then
2086 close c1;
2087 --
2088 -- raise error as this ordr_num already exists for this Plans options
2089 --
2090 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
2091 fnd_message.raise_error;
2092 --
2093 end if;
2094 --
2095 close c1;
2096 --
2097 end if;
2098 --
2099 hr_utility.set_location('Leaving:'||l_proc,10);
2100 --
2101 End chk_ordr_num_unq_in_plan;
2102 --
2103 -- ----------------------------------------------------------------------------
2104 -- |--------------------------< dt_update_validate >--------------------------|
2105 -- ----------------------------------------------------------------------------
2106 -- {Start Of Comments}
2107 --
2108 -- Description:
2109 -- This procedure is used for referential integrity of datetracked
2110 -- parent entities when a datetrack update operation is taking place
2111 -- and where there is no cascading of update defined for this entity.
2112 --
2113 -- Prerequisites:
2114 -- This procedure is called from the update_validate.
2115 --
2116 -- In Parameters:
2117 --
2118 -- Post Success:
2119 -- Processing continues.
2120 --
2121 -- Post Failure:
2122 --
2123 -- Developer Implementation Notes:
2124 -- This procedure should not need maintenance unless the HR Schema model
2125 -- changes.
2126 --
2127 -- Access Status:
2128 -- Internal Row Handler Use Only.
2129 --
2130 -- {End Of Comments}
2131 -- ----------------------------------------------------------------------------
2132 Procedure dt_update_validate
2133 (p_dflt_enrt_det_rl in number default hr_api.g_number,
2134 p_mndtry_rl in number default hr_api.g_number,
2135 p_rqd_perd_enrt_nenrt_rl in number default hr_api.g_number,
2136 p_actl_prem_id in number default hr_api.g_number,
2137 p_pl_id in number default hr_api.g_number,
2138 p_opt_id in number default hr_api.g_number,
2139 p_datetrack_mode in varchar2,
2140 p_validation_start_date in date,
2141 p_validation_end_date in date) Is
2142 --
2143 l_proc varchar2(72) := g_package||'dt_update_validate';
2144 l_integrity_error Exception;
2145 l_table_name all_tables.table_name%TYPE;
2146 --
2147 Begin
2148 hr_utility.set_location('Entering:'||l_proc, 5);
2149 --
2150 -- Ensure that the p_datetrack_mode argument is not null
2151 --
2152 hr_api.mandatory_arg_error
2153 (p_api_name => l_proc,
2154 p_argument => 'datetrack_mode',
2155 p_argument_value => p_datetrack_mode);
2156 --
2157 -- Only perform the validation if the datetrack update mode is valid
2158 --
2159 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
2160 --
2161 --
2162 -- Ensure the arguments are not null
2163 --
2164 hr_api.mandatory_arg_error
2165 (p_api_name => l_proc,
2166 p_argument => 'validation_start_date',
2167 p_argument_value => p_validation_start_date);
2168 --
2169 hr_api.mandatory_arg_error
2170 (p_api_name => l_proc,
2171 p_argument => 'validation_end_date',
2172 p_argument_value => p_validation_end_date);
2173 --
2174 If ((nvl(p_dflt_enrt_det_rl, hr_api.g_number) <> hr_api.g_number) and
2175 NOT (dt_api.check_min_max_dates
2176 (p_base_table_name => 'ff_formulas_f',
2177 p_base_key_column => 'formula_id',
2178 p_base_key_value => p_dflt_enrt_det_rl,
2179 p_from_date => p_validation_start_date,
2180 p_to_date => p_validation_end_date))) Then
2181 l_table_name := 'ff_formulas_f';
2182 Raise l_integrity_error;
2183 End If;
2184 If ((nvl(p_mndtry_rl, hr_api.g_number) <> hr_api.g_number) and
2185 NOT (dt_api.check_min_max_dates
2186 (p_base_table_name => 'ff_formulas_f',
2187 p_base_key_column => 'formula_id',
2188 p_base_key_value => p_mndtry_rl,
2189 p_from_date => p_validation_start_date,
2190 p_to_date => p_validation_end_date))) Then
2191 l_table_name := 'ff_formulas_f';
2192 Raise l_integrity_error;
2193 End If;
2194 If ((nvl(p_rqd_perd_enrt_nenrt_rl, hr_api.g_number) <> hr_api.g_number) and
2195 NOT (dt_api.check_min_max_dates
2196 (p_base_table_name => 'ff_formulas_f',
2197 p_base_key_column => 'formula_id',
2198 p_base_key_value => p_rqd_perd_enrt_nenrt_rl,
2199 p_from_date => p_validation_start_date,
2200 p_to_date => p_validation_end_date))) Then
2201 l_table_name := 'ff_formulas_f';
2202 Raise l_integrity_error;
2203 End If;
2204 If ((nvl(p_actl_prem_id, hr_api.g_number) <> hr_api.g_number) and
2205 NOT (dt_api.check_min_max_dates
2206 (p_base_table_name => 'ben_actl_prem_f',
2207 p_base_key_column => 'actl_prem_id',
2208 p_base_key_value => p_actl_prem_id,
2209 p_from_date => p_validation_start_date,
2210 p_to_date => p_validation_end_date))) Then
2211 l_table_name := 'ben_actl_prem_f';
2212 Raise l_integrity_error;
2213 End If;
2214 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
2215 NOT (dt_api.check_min_max_dates
2216 (p_base_table_name => 'ben_pl_f',
2217 p_base_key_column => 'pl_id',
2218 p_base_key_value => p_pl_id,
2219 p_from_date => p_validation_start_date,
2220 p_to_date => p_validation_end_date))) Then
2221 l_table_name := 'ben_pl_f';
2222 Raise l_integrity_error;
2223 End If;
2224 If ((nvl(p_opt_id, hr_api.g_number) <> hr_api.g_number) and
2225 NOT (dt_api.check_min_max_dates
2226 (p_base_table_name => 'ben_opt_f',
2227 p_base_key_column => 'opt_id',
2228 p_base_key_value => p_opt_id,
2229 p_from_date => p_validation_start_date,
2230 p_to_date => p_validation_end_date))) Then
2231 l_table_name := 'ben_opt_f';
2232 Raise l_integrity_error;
2233 End If;
2234 --
2235 End If;
2236 --
2237 hr_utility.set_location(' Leaving:'||l_proc, 10);
2238 Exception
2239 When l_integrity_error Then
2240 --
2241 -- A referential integrity check was violated therefore
2242 -- we must error
2243 --
2244 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
2245 hr_utility.set_message_token('TABLE_NAME', l_table_name);
2246 hr_utility.raise_error;
2247 When Others Then
2248 --
2249 -- An unhandled or unexpected error has occurred which
2250 -- we must report
2251 --
2252 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2253 hr_utility.set_message_token('PROCEDURE', l_proc);
2254 hr_utility.set_message_token('STEP','15');
2255 hr_utility.raise_error;
2256 End dt_update_validate;
2257 --
2258 -- ----------------------------------------------------------------------------
2259 -- |--------------------------< dt_delete_validate >--------------------------|
2260 -- ----------------------------------------------------------------------------
2261 -- {Start Of Comments}
2262 --
2263 -- Description:
2264 -- This procedure is used for referential integrity of datetracked
2265 -- child entities when either a datetrack DELETE or ZAP is in operation
2266 -- and where there is no cascading of delete defined for this entity.
2267 -- For the datetrack mode of DELETE or ZAP we must ensure that no
2268 -- datetracked child rows exist between the validation start and end
2269 -- dates.
2270 --
2271 -- Prerequisites:
2272 -- This procedure is called from the delete_validate.
2273 --
2274 -- In Parameters:
2275 --
2276 -- Post Success:
2277 -- Processing continues.
2278 --
2279 -- Post Failure:
2280 -- If a row exists by determining the returning Boolean value from the
2281 -- generic dt_api.rows_exist function then we must supply an error via
2282 -- the use of the local exception handler l_rows_exist.
2283 --
2284 -- Developer Implementation Notes:
2285 -- This procedure should not need maintenance unless the HR Schema model
2286 -- changes.
2287 --
2288 -- Access Status:
2289 -- Internal Row Handler Use Only.
2290 --
2291 -- {End Of Comments}
2292 -- ----------------------------------------------------------------------------
2293 Procedure dt_delete_validate
2294 (p_oipl_id in number,
2295 p_datetrack_mode in varchar2,
2296 p_validation_start_date in date,
2297 p_validation_end_date in date) Is
2298 --
2299 l_proc varchar2(72) := g_package||'dt_delete_validate';
2300 l_rows_exist Exception;
2301 l_table_name all_tables.table_name%TYPE;
2302 l_opt_id number;
2303 l_Dummy number;
2304 --
2305 cursor c1 is
2306 select cop.opt_id
2307 from ben_oipl_f cop
2308 where cop.oipl_id = p_oipl_id;
2309 --
2310 cursor c_epe is
2311 select null
2312 from ben_elig_per_elctbl_chc epe
2313 where epe.oipl_id = p_oipl_id;
2314 --
2315 Begin
2316 hr_utility.set_location('Entering:'||l_proc, 5);
2317 --
2318 -- Ensure that the p_datetrack_mode argument is not null
2319 --
2320 hr_api.mandatory_arg_error
2321 (p_api_name => l_proc,
2322 p_argument => 'datetrack_mode',
2323 p_argument_value => p_datetrack_mode);
2324 --
2325 -- Only perform the validation if the datetrack mode is either
2326 -- DELETE or ZAP
2327 --
2328 If (p_datetrack_mode = 'DELETE' or
2329 p_datetrack_mode = 'ZAP') then
2330 --
2331 --
2332 -- Ensure the arguments are not null
2333 --
2334 hr_api.mandatory_arg_error
2335 (p_api_name => l_proc,
2336 p_argument => 'validation_start_date',
2337 p_argument_value => p_validation_start_date);
2338 --
2339 hr_api.mandatory_arg_error
2340 (p_api_name => l_proc,
2341 p_argument => 'validation_end_date',
2342 p_argument_value => p_validation_end_date);
2343 --
2344 hr_api.mandatory_arg_error
2345 (p_api_name => l_proc,
2346 p_argument => 'oipl_id',
2347 p_argument_value => p_oipl_id);
2348 --
2349 open c1;
2350 fetch c1 into l_opt_id;
2351 close c1;
2352 --
2353 -- commented out to fix bug 1244535
2354 /*
2355 If (dt_api.rows_exist
2356 (p_base_table_name => 'ben_elig_per_opt_f',
2357 p_base_key_column => 'opt_id',
2358 p_base_key_value => l_opt_id,
2359 p_from_date => p_validation_start_date,
2360 p_to_date => p_validation_end_date)) Then
2361 l_table_name := 'ben_elig_per_opt_f';
2362 Raise l_rows_exist;
2363 End If;
2364 */
2365 -- Bug - 1894249
2366 -- Added validation to ensure that the option cannot be deleted
2367 -- if a participant has been enrolled in the oipl .
2368
2369 If (dt_api.rows_exist
2370 (p_base_table_name => 'ben_prtt_enrt_rslt_f',
2371 p_base_key_column => 'oipl_id',
2372 p_base_key_value => p_oipl_id,
2373 p_from_date => p_validation_start_date,
2374 p_to_date => p_validation_end_date)) Then
2375 l_table_name := 'ben_prtt_enrt_rslt_f';
2376 Raise l_rows_exist;
2377 End If;
2378
2379 If p_datetrack_mode = 'ZAP' then
2380 -- Check in Ben_elig_per_elctbl_chc added rbingi, Bug 4558201
2381 Open c_epe;
2382 Fetch c_epe into l_Dummy;
2383 --
2384 If c_epe%FOUND then
2385 l_table_name := 'ben_elig_per_elctbl_chc';
2386 Close c_epe ;
2387 Raise l_rows_exist ;
2388 End if;
2389 Close c_epe ;
2390 End if;
2391
2392 If (dt_api.rows_exist
2393 (p_base_table_name => 'ben_elig_to_prte_rsn_f',
2394 p_base_key_column => 'oipl_id',
2395 p_base_key_value => p_oipl_id,
2396 p_from_date => p_validation_start_date,
2397 p_to_date => p_validation_end_date)) Then
2398 l_table_name := 'ben_elig_to_prte_rsn_f';
2399 Raise l_rows_exist;
2400 End If;
2401 If (dt_api.rows_exist
2402 (p_base_table_name => 'ben_ler_chg_oipl_enrt_f',
2403 p_base_key_column => 'oipl_id',
2404 p_base_key_value => p_oipl_id,
2405 p_from_date => p_validation_start_date,
2406 p_to_date => p_validation_end_date)) Then
2407 l_table_name := 'ben_ler_chg_oipl_enrt_f';
2408 Raise l_rows_exist;
2409 End If;
2410 If (dt_api.rows_exist
2411 (p_base_table_name => 'ben_prtn_elig_f',
2412 p_base_key_column => 'oipl_id',
2413 p_base_key_value => p_oipl_id,
2414 p_from_date => p_validation_start_date,
2415 p_to_date => p_validation_end_date)) Then
2416 l_table_name := 'ben_prtn_elig_f';
2417 Raise l_rows_exist;
2418 End If;
2419 If (dt_api.rows_exist
2420 (p_base_table_name => 'ben_cvg_amt_calc_mthd_f',
2421 p_base_key_column => 'oipl_id',
2422 p_base_key_value => p_oipl_id,
2423 p_from_date => p_validation_start_date,
2424 p_to_date => p_validation_end_date)) Then
2425 l_table_name := 'ben_cvg_amt_calc_mthd_f';
2426 Raise l_rows_exist;
2427 End If;
2428 If (dt_api.rows_exist
2429 (p_base_table_name => 'ben_acty_base_rt_f',
2430 p_base_key_column => 'oipl_id',
2431 p_base_key_value => p_oipl_id,
2432 p_from_date => p_validation_start_date,
2433 p_to_date => p_validation_end_date)) Then
2434 l_table_name := 'ben_acty_base_rt_f';
2435 Raise l_rows_exist;
2436 End If;
2437 --
2438 End If;
2439 --
2440 hr_utility.set_location(' Leaving:'||l_proc, 10);
2441 Exception
2442 When l_rows_exist Then
2443 --
2444 -- A referential integrity check was violated therefore
2445 -- we must error
2446 --
2447 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
2448 hr_utility.set_message_token('TABLE_NAME', l_table_name);
2449 hr_utility.raise_error;
2450 When Others Then
2451 Raise;
2452 --
2453 -- An unhandled or unexpected error has occurred which
2454 -- we must report
2455 --
2456 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2457 hr_utility.set_message_token('PROCEDURE', l_proc);
2458 hr_utility.set_message_token('STEP','15');
2459 hr_utility.raise_error;
2460 End dt_delete_validate;
2461 --
2462 -- ----------------------------------------------------------------------------
2463 -- |---------------------------< insert_validate >----------------------------|
2464 -- ----------------------------------------------------------------------------
2465 Procedure insert_validate
2466 (p_rec in ben_cop_shd.g_rec_type,
2467 p_effective_date in date,
2468 p_datetrack_mode in varchar2,
2469 p_validation_start_date in date,
2470 p_validation_end_date in date) is
2471 --
2472 l_proc varchar2(72) := g_package||'insert_validate';
2473 --
2474 Begin
2475 hr_utility.set_location('Entering:'||l_proc, 5);
2476 --
2477 -- Call all supporting business operations
2478 --
2479 --
2480 if p_rec.business_group_id is not null and p_rec.legislation_code is null then
2481 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2482 end if;
2483 --
2484 chk_oipl_id
2485 (p_oipl_id => p_rec.oipl_id,
2486 p_effective_date => p_effective_date,
2487 p_object_version_number => p_rec.object_version_number);
2488 --
2489 chk_mndtry_flag
2490 (p_oipl_id => p_rec.oipl_id,
2491 p_mndtry_flag => p_rec.mndtry_flag,
2492 p_effective_date => p_effective_date,
2493 p_object_version_number => p_rec.object_version_number);
2494 --
2495 chk_dflt_flag
2496 (p_oipl_id => p_rec.oipl_id,
2497 p_dflt_flag => p_rec.dflt_flag,
2498 p_effective_date => p_effective_date,
2499 p_object_version_number => p_rec.object_version_number);
2500 --
2501 chk_auto_enrt_flag
2502 (p_oipl_id => p_rec.oipl_id,
2503 p_auto_enrt_flag => p_rec.auto_enrt_flag,
2504 p_effective_date => p_effective_date,
2505 p_object_version_number => p_rec.object_version_number);
2506 --
2507 chk_elig_apls_flag
2508 (p_oipl_id => p_rec.oipl_id,
2509 p_elig_apls_flag => p_rec.elig_apls_flag,
2510 p_effective_date => p_effective_date,
2511 p_object_version_number => p_rec.object_version_number);
2512 --
2513 chk_trk_inelig_per_flag
2514 (p_oipl_id => p_rec.oipl_id,
2515 p_trk_inelig_per_flag => p_rec.trk_inelig_per_flag,
2516 p_effective_date => p_effective_date,
2517 p_object_version_number => p_rec.object_version_number);
2518 --
2519 chk_drvbl_fctr_prtn_elig_flag
2520 (p_oipl_id => p_rec.oipl_id,
2521 p_drvbl_fctr_prtn_elig_flag => p_rec.drvbl_fctr_prtn_elig_flag,
2522 p_effective_date => p_effective_date,
2523 p_object_version_number => p_rec.object_version_number);
2524 --
2525 chk_prtn_elig_ovrid_alwd_flag
2526 (p_oipl_id => p_rec.oipl_id,
2527 p_prtn_elig_ovrid_alwd_flag => p_rec.prtn_elig_ovrid_alwd_flag,
2528 p_effective_date => p_effective_date,
2529 p_object_version_number => p_rec.object_version_number);
2530 --
2531 chk_drvbl_fctr_apls_rts_flag
2532 (p_oipl_id => p_rec.oipl_id,
2533 p_drvbl_fctr_apls_rts_flag => p_rec.drvbl_fctr_apls_rts_flag,
2534 p_effective_date => p_effective_date,
2535 p_object_version_number => p_rec.object_version_number);
2536 --
2537 chk_oipl_stat_cd
2538 (p_oipl_id => p_rec.oipl_id,
2539 p_oipl_stat_cd => p_rec.oipl_stat_cd,
2540 p_effective_date => p_effective_date,
2541 p_object_version_number => p_rec.object_version_number);
2542 --
2543 chk_pcp_dsgn_cd
2544 (p_oipl_id => p_rec.oipl_id,
2545 p_pcp_dsgn_cd => p_rec.pcp_dsgn_cd,
2546 p_effective_date => p_effective_date,
2547 p_object_version_number => p_rec.object_version_number);
2548
2549 chk_pcp_dpnt_dsgn_cd
2550 (p_oipl_id => p_rec.oipl_id,
2551 p_pcp_dpnt_dsgn_cd => p_rec.pcp_dpnt_dsgn_cd,
2552 p_effective_date => p_effective_date,
2553 p_object_version_number => p_rec.object_version_number);
2554
2555 --
2556 chk_dflt_enrt_cd
2557 (p_oipl_id => p_rec.oipl_id,
2558 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
2559 p_effective_date => p_effective_date,
2560 p_object_version_number => p_rec.object_version_number);
2561 --
2562 chk_enrt_cd
2563 (p_oipl_id => p_rec.oipl_id,
2564 p_enrt_cd => p_rec.enrt_cd,
2565 p_effective_date => p_effective_date,
2566 p_object_version_number => p_rec.object_version_number);
2567 --
2568 chk_dflt_enrt_det_rl
2569 (p_oipl_id => p_rec.oipl_id,
2570 p_business_group_id => p_rec.business_group_id,
2571 p_dflt_enrt_det_rl => p_rec.dflt_enrt_det_rl,
2572 p_effective_date => p_effective_date,
2573 p_object_version_number => p_rec.object_version_number);
2574 --
2575 chk_auto_enrt_mthd_rl
2576 (p_oipl_id => p_rec.oipl_id,
2577 p_business_group_id => p_rec.business_group_id,
2578 p_auto_enrt_mthd_rl => p_rec.auto_enrt_mthd_rl,
2579 p_effective_date => p_effective_date,
2580 p_object_version_number => p_rec.object_version_number);
2581 --
2582 chk_mndtry_rl
2583 (p_oipl_id => p_rec.oipl_id,
2584 p_business_group_id => p_rec.business_group_id,
2585 p_mndtry_rl => p_rec.mndtry_rl,
2586 p_effective_date => p_effective_date,
2587 p_object_version_number => p_rec.object_version_number);
2588 --
2589 chk_rqd_perd_enrt_nenrt_rl
2590 (p_oipl_id => p_rec.oipl_id,
2591 p_business_group_id => p_rec.business_group_id,
2592 p_rqd_perd_enrt_nenrt_rl => p_rec.rqd_perd_enrt_nenrt_rl,
2593 p_effective_date => p_effective_date,
2594 p_object_version_number => p_rec.object_version_number);
2595 --
2596 chk_postelcn_edit_rl
2597 (p_oipl_id => p_rec.oipl_id,
2598 p_business_group_id => p_rec.business_group_id,
2599 p_postelcn_edit_rl => p_rec.postelcn_edit_rl,
2600 p_effective_date => p_effective_date,
2601 p_object_version_number => p_rec.object_version_number);
2602 --
2603 chk_vrfy_fmly_mmbr_rl
2604 (p_oipl_id => p_rec.oipl_id,
2605 p_business_group_id => p_rec.business_group_id,
2606 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl,
2607 p_effective_date => p_effective_date,
2608 p_object_version_number => p_rec.object_version_number);
2609 --
2610 chk_enrt_rl
2611 (p_oipl_id => p_rec.oipl_id,
2612 p_business_group_id => p_rec.business_group_id,
2613 p_enrt_rl => p_rec.enrt_rl,
2614 p_effective_date => p_effective_date,
2615 p_object_version_number => p_rec.object_version_number);
2616 --
2617 chk_cd_rl_combination
2618 (p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
2619 p_dflt_enrt_det_rl => p_rec.dflt_enrt_det_rl);
2620 --
2621 chk_opt_id
2622 (p_oipl_id => p_rec.oipl_id,
2623 p_pl_id => p_rec.pl_id,
2624 p_opt_id => p_rec.opt_id,
2625 p_validation_start_date => p_validation_start_date,
2626 p_validation_end_date => p_validation_end_date,
2627 p_effective_date => p_effective_date,
2628 p_business_group_id => p_rec.business_group_id,
2629 p_object_version_number => p_rec.object_version_number);
2630 --
2631 chk_plan_pcp_before_oipl
2632 (p_oipl_id => p_rec.oipl_id,
2633 p_pl_id => p_rec.pl_id,
2634 p_pcp_dsgn_cd => p_rec.pcp_dsgn_cd,
2635 p_pcp_dpnt_dsgn_cd => p_rec.pcp_dpnt_dsgn_cd,
2636 p_effective_date => p_effective_date,
2637 p_business_group_id => p_rec.business_group_id,
2638 p_object_version_number => p_rec.object_version_number);
2639 --
2640 chk_ordr_num_unq_in_plan
2641 (p_oipl_id => p_rec.oipl_id,
2642 p_pl_id => p_rec.pl_id,
2643 p_ordr_num => p_rec.ordr_num,
2644 p_validation_start_date => p_validation_start_date,
2645 p_validation_end_date => p_validation_end_date,
2646 p_effective_date => p_effective_date,
2647 p_business_group_id => p_rec.business_group_id,
2648 p_object_version_number => p_rec.object_version_number);
2649 --
2650 hr_utility.set_location(' Leaving:'||l_proc, 10);
2651 End insert_validate;
2652 --
2653 -- ----------------------------------------------------------------------------
2654 -- |---------------------------< update_validate >----------------------------|
2655 -- ----------------------------------------------------------------------------
2656 Procedure update_validate
2657 (p_rec in ben_cop_shd.g_rec_type,
2658 p_effective_date in date,
2659 p_datetrack_mode in varchar2,
2660 p_validation_start_date in date,
2661 p_validation_end_date in date) is
2662 --
2663 l_proc varchar2(72) := g_package||'update_validate';
2664 --
2665 Begin
2666 hr_utility.set_location('Entering:'||l_proc, 5);
2667 --
2668 -- Call all supporting business operations
2669 --
2670 --
2671 if p_rec.business_group_id is not null and p_rec.legislation_code is null then
2672 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2673 end if;
2674 --
2675 chk_oipl_id
2676 (p_oipl_id => p_rec.oipl_id,
2677 p_effective_date => p_effective_date,
2678 p_object_version_number => p_rec.object_version_number);
2679 --
2680 chk_mndtry_flag
2681 (p_oipl_id => p_rec.oipl_id,
2682 p_mndtry_flag => p_rec.mndtry_flag,
2683 p_effective_date => p_effective_date,
2684 p_object_version_number => p_rec.object_version_number);
2685 --
2686 chk_dflt_flag
2687 (p_oipl_id => p_rec.oipl_id,
2688 p_dflt_flag => p_rec.dflt_flag,
2689 p_effective_date => p_effective_date,
2690 p_object_version_number => p_rec.object_version_number);
2691 --
2692 chk_auto_enrt_flag
2693 (p_oipl_id => p_rec.oipl_id,
2694 p_auto_enrt_flag => p_rec.auto_enrt_flag,
2695 p_effective_date => p_effective_date,
2696 p_object_version_number => p_rec.object_version_number);
2697 --
2698 chk_elig_apls_flag
2699 (p_oipl_id => p_rec.oipl_id,
2700 p_elig_apls_flag => p_rec.elig_apls_flag,
2701 p_effective_date => p_effective_date,
2702 p_object_version_number => p_rec.object_version_number);
2703 --
2704 chk_trk_inelig_per_flag
2705 (p_oipl_id => p_rec.oipl_id,
2706 p_trk_inelig_per_flag => p_rec.trk_inelig_per_flag,
2707 p_effective_date => p_effective_date,
2708 p_object_version_number => p_rec.object_version_number);
2709 --
2710 chk_drvbl_fctr_prtn_elig_flag
2711 (p_oipl_id => p_rec.oipl_id,
2712 p_drvbl_fctr_prtn_elig_flag => p_rec.drvbl_fctr_prtn_elig_flag,
2713 p_effective_date => p_effective_date,
2714 p_object_version_number => p_rec.object_version_number);
2715 --
2716 chk_prtn_elig_ovrid_alwd_flag
2717 (p_oipl_id => p_rec.oipl_id,
2718 p_prtn_elig_ovrid_alwd_flag => p_rec.prtn_elig_ovrid_alwd_flag,
2719 p_effective_date => p_effective_date,
2720 p_object_version_number => p_rec.object_version_number);
2721 --
2722 chk_drvbl_fctr_apls_rts_flag
2723 (p_oipl_id => p_rec.oipl_id,
2724 p_drvbl_fctr_apls_rts_flag => p_rec.drvbl_fctr_apls_rts_flag,
2725 p_effective_date => p_effective_date,
2726 p_object_version_number => p_rec.object_version_number);
2727 --
2728 chk_oipl_stat_cd
2729 (p_oipl_id => p_rec.oipl_id,
2730 p_oipl_stat_cd => p_rec.oipl_stat_cd,
2731 p_effective_date => p_effective_date,
2732 p_object_version_number => p_rec.object_version_number);
2733 --
2734 chk_pcp_dsgn_cd
2735 (p_oipl_id => p_rec.oipl_id,
2736 p_pcp_dsgn_cd => p_rec.pcp_dsgn_cd,
2737 p_effective_date => p_effective_date,
2738 p_object_version_number => p_rec.object_version_number);
2739 --
2740 chk_pcp_dpnt_dsgn_cd
2741 (p_oipl_id => p_rec.oipl_id,
2742 p_pcp_dpnt_dsgn_cd => p_rec.pcp_dpnt_dsgn_cd,
2743 p_effective_date => p_effective_date,
2744 p_object_version_number => p_rec.object_version_number);
2745 --
2746 chk_dflt_enrt_cd
2747 (p_oipl_id => p_rec.oipl_id,
2748 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
2749 p_effective_date => p_effective_date,
2750 p_object_version_number => p_rec.object_version_number);
2751 --
2752 chk_enrt_cd
2753 (p_oipl_id => p_rec.oipl_id,
2754 p_enrt_cd => p_rec.enrt_cd,
2755 p_effective_date => p_effective_date,
2756 p_object_version_number => p_rec.object_version_number);
2757 --
2758 chk_dflt_enrt_det_rl
2759 (p_oipl_id => p_rec.oipl_id,
2760 p_business_group_id => p_rec.business_group_id,
2761 p_dflt_enrt_det_rl => p_rec.dflt_enrt_det_rl,
2762 p_effective_date => p_effective_date,
2763 p_object_version_number => p_rec.object_version_number);
2764 --
2765 chk_auto_enrt_mthd_rl
2766 (p_oipl_id => p_rec.oipl_id,
2767 p_business_group_id => p_rec.business_group_id,
2768 p_auto_enrt_mthd_rl => p_rec.auto_enrt_mthd_rl,
2769 p_effective_date => p_effective_date,
2770 p_object_version_number => p_rec.object_version_number);
2771 --
2772 chk_mndtry_rl
2773 (p_oipl_id => p_rec.oipl_id,
2774 p_business_group_id => p_rec.business_group_id,
2775 p_mndtry_rl => p_rec.mndtry_rl,
2776 p_effective_date => p_effective_date,
2777 p_object_version_number => p_rec.object_version_number);
2778 --
2779 chk_rqd_perd_enrt_nenrt_rl
2780 (p_oipl_id => p_rec.oipl_id,
2781 p_business_group_id => p_rec.business_group_id,
2782 p_rqd_perd_enrt_nenrt_rl => p_rec.rqd_perd_enrt_nenrt_rl,
2783 p_effective_date => p_effective_date,
2784 p_object_version_number => p_rec.object_version_number);
2785 --
2786 chk_postelcn_edit_rl
2787 (p_oipl_id => p_rec.oipl_id,
2788 p_business_group_id => p_rec.business_group_id,
2789 p_postelcn_edit_rl => p_rec.postelcn_edit_rl,
2790 p_effective_date => p_effective_date,
2791 p_object_version_number => p_rec.object_version_number);
2792 --
2793 chk_vrfy_fmly_mmbr_rl
2794 (p_oipl_id => p_rec.oipl_id,
2795 p_business_group_id => p_rec.business_group_id,
2796 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl,
2797 p_effective_date => p_effective_date,
2798 p_object_version_number => p_rec.object_version_number);
2799 --
2800 chk_enrt_rl
2801 (p_oipl_id => p_rec.oipl_id,
2802 p_business_group_id => p_rec.business_group_id,
2803 p_enrt_rl => p_rec.enrt_rl,
2804 p_effective_date => p_effective_date,
2805 p_object_version_number => p_rec.object_version_number);
2806 --
2807 chk_cd_rl_combination
2808 (p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
2809 p_dflt_enrt_det_rl => p_rec.dflt_enrt_det_rl);
2810 --
2811 chk_auto_enrt_and_mthd
2812 (p_oipl_id => p_rec.oipl_id,
2813 p_auto_enrt_flag => p_rec.auto_enrt_flag,
2814 p_pl_id => p_rec.pl_id,
2815 p_effective_date => p_effective_date,
2816 p_business_group_id => p_rec.business_group_id);
2817 --
2818 chk_auto_enrt_and_flags
2819 (p_auto_enrt_flag => p_rec.auto_enrt_flag,
2820 p_mndtry_flag => p_rec.mndtry_flag,
2821 p_dflt_flag => p_rec.dflt_flag);
2822 --
2823 chk_auto_enrt_and_mthd
2824 (p_oipl_id => p_rec.oipl_id,
2825 p_auto_enrt_flag => p_rec.auto_enrt_flag,
2826 p_pl_id => p_rec.pl_id,
2827 p_effective_date => p_effective_date,
2828 p_business_group_id => p_rec.business_group_id);
2829 --
2830 chk_auto_enrt_and_flags
2831 (p_auto_enrt_flag => p_rec.auto_enrt_flag,
2832 p_mndtry_flag => p_rec.mndtry_flag,
2833 p_dflt_flag => p_rec.dflt_flag);
2834 --
2835 chk_plan_pcp_before_oipl
2836 (p_oipl_id => p_rec.oipl_id,
2837 p_pl_id => p_rec.pl_id,
2838 p_pcp_dsgn_cd => p_rec.pcp_dsgn_cd,
2839 p_pcp_dpnt_dsgn_cd => p_rec.pcp_dpnt_dsgn_cd,
2840 p_effective_date => p_effective_date,
2841 p_business_group_id => p_rec.business_group_id,
2842 p_object_version_number => p_rec.object_version_number);
2843 --
2844 chk_plan_oipl_mutexcl
2845 (p_oipl_id => p_rec.oipl_id,
2846 p_actl_prem_id => p_rec.actl_prem_id,
2847 p_effective_date => p_effective_date,
2848 p_business_group_id => p_rec.business_group_id,
2849 p_object_version_number => p_rec.object_version_number);
2850 --
2851 chk_opt_id
2852 (p_oipl_id => p_rec.oipl_id,
2853 p_pl_id => p_rec.pl_id,
2854 p_opt_id => p_rec.opt_id,
2855 p_validation_start_date => p_validation_start_date,
2856 p_validation_end_date => p_validation_end_date,
2857 p_effective_date => p_effective_date,
2858 p_business_group_id => p_rec.business_group_id,
2859 p_object_version_number => p_rec.object_version_number);
2860 --
2861 chk_ordr_num_unq_in_plan
2862 (p_oipl_id => p_rec.oipl_id,
2863 p_pl_id => p_rec.pl_id,
2864 p_ordr_num => p_rec.ordr_num,
2865 p_validation_start_date => p_validation_start_date,
2866 p_validation_end_date => p_validation_end_date,
2867 p_effective_date => p_effective_date,
2868 p_business_group_id => p_rec.business_group_id,
2869 p_object_version_number => p_rec.object_version_number);
2870 --
2871 -- Call the datetrack update integrity operation
2872 --
2873 dt_update_validate
2874 (p_dflt_enrt_det_rl => p_rec.dflt_enrt_det_rl,
2875 p_mndtry_rl => p_rec.mndtry_rl,
2876 p_rqd_perd_enrt_nenrt_rl => p_rec.rqd_perd_enrt_nenrt_rl,
2877 p_actl_prem_id => p_rec.actl_prem_id,
2878 p_pl_id => p_rec.pl_id,
2879 p_opt_id => p_rec.opt_id,
2880 p_datetrack_mode => p_datetrack_mode,
2881 p_validation_start_date => p_validation_start_date,
2882 p_validation_end_date => p_validation_end_date);
2883 --
2884 hr_utility.set_location(' Leaving:'||l_proc, 10);
2885 End update_validate;
2886 --
2887 -- ----------------------------------------------------------------------------
2888 -- |---------------------------< delete_validate >----------------------------|
2889 -- ----------------------------------------------------------------------------
2890 Procedure delete_validate
2891 (p_rec in ben_cop_shd.g_rec_type,
2892 p_effective_date in date,
2893 p_datetrack_mode in varchar2,
2894 p_validation_start_date in date,
2895 p_validation_end_date in date) is
2896 --
2897 l_proc varchar2(72) := g_package||'delete_validate';
2898 --
2899
2900 --
2901 Begin
2902 hr_utility.set_location('Entering:'||l_proc, 5);
2903
2904 --
2905 -- Call all supporting business operations
2906 --
2907 dt_delete_validate
2908 (p_datetrack_mode => p_datetrack_mode,
2909 p_validation_start_date => p_validation_start_date,
2910 p_validation_end_date => p_validation_end_date,
2911 p_oipl_id => p_rec.oipl_id);
2912 --
2913 hr_utility.set_location(' Leaving:'||l_proc, 10);
2914 --
2915 End delete_validate;
2916 --
2917 -- ---------------------------------------------------------------------------
2918 -- |---------------------< return_legislation_code >-------------------------|
2919 -- ---------------------------------------------------------------------------
2920 --
2921 function return_legislation_code
2922 (p_oipl_id in number) return varchar2 is
2923 --
2924 -- Declare cursor
2925 --
2926 cursor csr_leg_code is
2927 select a.legislation_code
2928 from per_business_groups a,
2929 ben_oipl_f b
2930 where b.oipl_id = p_oipl_id
2931 and a.business_group_id = b.business_group_id;
2932 --
2933 -- Declare local variables
2934 --
2935 l_legislation_code varchar2(150);
2936 l_proc varchar2(72) := g_package||'return_legislation_code';
2937 --
2938 begin
2939 --
2940 hr_utility.set_location('Entering:'|| l_proc, 10);
2941 --
2942 -- Ensure that all the mandatory parameter are not null
2943 --
2944 hr_api.mandatory_arg_error(p_api_name => l_proc,
2945 p_argument => 'oipl_id',
2946 p_argument_value => p_oipl_id);
2947 --
2948 open csr_leg_code;
2949 --
2950 fetch csr_leg_code into l_legislation_code;
2951 --
2952 if csr_leg_code%notfound then
2953 --
2954 close csr_leg_code;
2955 --
2956 -- The primary key is invalid therefore we must error
2957 --
2958 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
2959 fnd_message.raise_error;
2960 --
2961 end if;
2962 --
2963 close csr_leg_code;
2964 --
2965 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2966 --
2967 return l_legislation_code;
2968
2969 --
2970 end return_legislation_code;
2971 --
2972 end ben_cop_bus;