[Home] [Help]
PACKAGE BODY: APPS.BEN_PDP_BUS
Source
1 Package Body ben_pdp_bus as
2 /* $Header: bepdprhi.pkb 120.13 2008/02/22 16:27:41 rtagarra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_pdp_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_elig_cvrd_dpnt_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- elig_cvrd_dpnt_id PK of record being inserted or updated.
24 -- effective_date Effective Date of session
25 -- object_version_number Object version number of record being
26 -- inserted or updated.
27 --
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Errors handled by the procedure
33 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_elig_cvrd_dpnt_id(p_elig_cvrd_dpnt_id in number,
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_elig_cvrd_dpnt_id';
42 l_api_updating boolean;
43 --
44 Begin
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 --
48 l_api_updating := ben_pdp_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_elig_cvrd_dpnt_id,hr_api.g_number)
55 <> ben_pdp_shd.g_old_rec.elig_cvrd_dpnt_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_pdp_shd.constraint_error('BEN_ELIG_CVRD_DPNT_PK');
60 --
64 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
65 if p_elig_cvrd_dpnt_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_pdp_shd.constraint_error('BEN_ELIG_CVRD_DPNT_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_elig_cvrd_dpnt_id;
78
79 --
80 -- ---------------------------------------------------------------------------
81 -- |------< chk_ovrdn_flag >------|
82 -- ---------------------------------------------------------------------------
83 --
84 -- Description
85 -- This procedure is used to check that the lookup value is valid.
86 --
87 -- Pre Conditions
88 -- None.
89 --
90 -- Post Success
91 -- Processing continues
92 --
93 -- Post Failure
94 -- Error handled by procedure
95 --
96 -- Access Status
97 -- Internal table handler use only.
98 --
99 Procedure chk_ovrdn_flag(p_elig_cvrd_dpnt_id in number,
100 p_ovrdn_flag in varchar2,
101 p_effective_date in date,
102 p_object_version_number in number) is
103 --
104 l_proc varchar2(72) := g_package||'chk_ovrdn_flag';
105 l_api_updating boolean;
106 --
107 Begin
108 --
109 hr_utility.set_location('Entering:'||l_proc, 5);
110 --
111 l_api_updating := ben_pdp_shd.api_updating
112 (p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
113 p_effective_date => p_effective_date,
114 p_object_version_number => p_object_version_number);
115 --
116 if (l_api_updating
117 and p_ovrdn_flag
118 <> nvl(ben_pdp_shd.g_old_rec.ovrdn_flag,hr_api.g_varchar2)
119 or not l_api_updating) then
120 --
121 -- check if value of lookup falls within lookup type.
122 --
123 --
124 if hr_api.not_exists_in_hr_lookups
125 (p_lookup_type => 'YES_NO',
126 p_lookup_code => p_ovrdn_flag,
127 p_effective_date => p_effective_date) then
128 --
129 -- raise error as does not exist as lookup
130 --
131 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
132 fnd_message.set_token('FIELD', p_ovrdn_flag);
133 fnd_message.set_token('TYPE','YES_NO');
134 fnd_message.raise_error;
135 --
136 end if;
137 --
138 end if;
139 --
140 hr_utility.set_location('Leaving:'||l_proc,10);
141 --
142 end chk_ovrdn_flag;
143
144 -- ---------------------------------------------------------------------------
145 -- |----------------------< CHK_CVG_PNDG_FLAG >-------------------------------|
146 -- ---------------------------------------------------------------------------
147 --
148 -- Description
149 -- This procedure is used to check that the lookup value is valid.
150 --
151 -- Pre Conditions
152 -- None.
153 --
154 -- Post Success
155 -- Processing continues
156 --
157 -- Post Failure
158 -- Error handled by procedure
159 --
160 -- Access Status
161 -- Internal table handler use only.
162 --
163 Procedure chk_cvg_pndg_flag(p_elig_cvrd_dpnt_id in number,
164 p_cvg_pndg_flag in varchar2,
165 p_effective_date in date,
166 p_object_version_number in number) is
167 --
168 l_proc varchar2(72) := g_package||'chk_cvg_pndg_flag';
169 l_api_updating boolean;
170 --
171 Begin
172 --
173 hr_utility.set_location('Entering:'||l_proc, 5);
174 --
175 l_api_updating := ben_pdp_shd.api_updating
176 (p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
177 p_effective_date => p_effective_date,
178 p_object_version_number => p_object_version_number);
179 --
180 if (l_api_updating
181 and p_cvg_pndg_flag
182 <> nvl(ben_pdp_shd.g_old_rec.cvg_pndg_flag,hr_api.g_varchar2)
183 or not l_api_updating) then
184 --
185 -- check if value of lookup falls within lookup type.
186 --
187 --
188 if hr_api.not_exists_in_hr_lookups
189 (p_lookup_type => 'YES_NO',
190 p_lookup_code => p_cvg_pndg_flag,
191 p_effective_date => p_effective_date) then
192 --
193 -- raise error as does not exist as lookup
194 --
195 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
196 fnd_message.set_token('FIELD', p_cvg_pndg_flag);
197 fnd_message.set_token('TYPE','YES_NO');
198 fnd_message.raise_error;
199 --
200 end if;
201 --
202 end if;
203 --
204 hr_utility.set_location('Leaving:'||l_proc,10);
205 --
206 end chk_cvg_pndg_flag;
207 --
208 -- ----------------------------------------------------------------------------
209 -- |------< chk_cvg_dates >------|
210 -- ----------------------------------------------------------------------------
211 --
212 -- Description
213 -- This procedure is used to check that cvg start date is less then cvr thru date
214 --
215 -- Pre Conditions
216 -- None.
217 --
218 -- In Parameters
219 -- elig_cvrd_dpnt_id PK of record being inserted or updated.
220 -- effective_date effective date
221 -- object_version_number Object version number of record being
222 -- inserted or updated.
223 --
224 -- Post Success
225 -- Processing continues
226 --
227 -- Post Failure
228 -- Error handled by procedure
229 --
230 -- Access Status
231 -- Internal table handler use only.
232 --
233 Procedure chk_cvg_dates(p_cvg_strt_dt in date,
234 p_cvg_thru_dt in date) is
235 --
236 l_proc varchar2(72) := g_package||'chk_cvg_dates';
237 --
238 Begin
239 --
240 hr_utility.set_location('Entering:'||l_proc,5);
241 --
242 if p_cvg_strt_dt is not null and p_cvg_thru_dt is not null and
243 p_cvg_strt_dt > p_cvg_thru_dt then
244 --
245 -- raise error as dates are out of seq
246 --
247 fnd_message.set_name('BEN', 'BEN_91649_CVG_STRT_THRU_DT');
248 fnd_message.raise_error;
249 --
250 --
251 end if;
252 --
253 hr_utility.set_location('Leaving:'||l_proc,10);
254 --
255 end chk_cvg_dates;
256 --
257 --
258 -- ----------------------------------------------------------------------------
259 -- |------< chk_dpnt_person_id >------|
260 -- ----------------------------------------------------------------------------
261 --
262 -- Description
263 -- This procedure checks that a referenced foreign key actually exists
264 -- in the referenced table.
265 --
266 -- Pre-Conditions
267 -- None.
268 --
269 -- In Parameters
270 -- p_elig_cvrd_dpnt_id PK
271 -- p_dpnt_person_id ID of FK column
272 -- p_effective_date session date
273 -- p_object_version_number object version number
274 --
275 -- Post Success
276 -- Processing continues
277 --
278 -- Post Failure
279 -- Error raised.
280 --
281 -- Access Status
282 -- Internal table handler use only.
283 --
284 Procedure chk_dpnt_person_id (p_elig_cvrd_dpnt_id in number,
285 p_dpnt_person_id in number,
286 p_prtt_enrt_rslt_id in number,
287 p_validation_start_date in date,
288 p_validation_end_date in date,
289 p_effective_date in date,
290 p_cvg_strt_dt in date,
291 p_business_group_id in number,
292 p_object_version_number in number) is
293 --
294 l_proc varchar2(72) := g_package||'chk_dpnt_person_id';
295 l_api_updating boolean;
296 l_dummy varchar2(1);
297 l_exists varchar2(1);
298 l_exists_2 varchar2(1);
299 --
300 cursor c3 is
301 select null
302 from ben_elig_cvrd_dpnt_f ecd
303 ,ben_per_in_ler pil
304 where ecd.dpnt_person_id = p_dpnt_person_id
305 and ecd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
306 and ecd.elig_cvrd_dpnt_id <> nvl(p_elig_cvrd_dpnt_id, hr_api.g_number)
307 and ecd.business_group_id = p_business_group_id
308 and p_cvg_strt_dt between ecd.cvg_strt_dt and ecd.cvg_thru_dt
309 and ecd.cvg_thru_dt <= ecd.effective_end_date
310 and ecd.per_in_ler_id = pil.per_in_ler_id(+)
311 and nvl(pil.per_in_ler_stat_cd, 'A') not in ('VOIDD', 'BCKDT')
312 --and p_validation_start_date <= effective_end_date
313 --and p_validation_end_date >= effective_start_date
314 ;
315 --
316 cursor c1 is
317 select null
318 from per_all_people_f a
319 where a.person_id = p_dpnt_person_id
320 and a.business_group_id + 0 = p_business_group_id
321 and p_validation_start_date <= effective_end_date
322 and p_validation_end_date >= effective_start_date
323 ;
324 --
325 Begin
326 --
327 hr_utility.set_location('Entering:'||l_proc,5);
328 --
329 l_api_updating := ben_pdp_shd.api_updating
330 (p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
331 p_effective_date => p_effective_date,
332 p_object_version_number => p_object_version_number);
333 --
334 if (l_api_updating
335 and nvl(p_dpnt_person_id,hr_api.g_number)
336 <> nvl(ben_pdp_shd.g_old_rec.dpnt_person_id, hr_api.g_number)
337 or not l_api_updating) then
338 --
339 -- check if dpnt_person_id value exists in per_all_people_f table
340 --
341 open c1;
342 --
343 fetch c1 into l_dummy;
344 if c1%notfound then
345 --
346 close c1;
347 --
348 -- raise error as FK does not relate to PK in per_all_people
349 -- table.
350 --
351 ben_pdp_shd.constraint_error('BEN_ELIG_CVRD_DPNT_FK1');
352 --
353 end if;
354 --
355 close c1;
356 --
357 if p_prtt_enrt_rslt_id is not null then
358 open c3;
359 fetch c3 into l_exists_2;
360 if c3%found then
361 close c3;
362 --
363 -- raise error as this dependent already exists for this enrt rslt
364 --
365 fnd_message.set_name('BEN', 'BEN_91651_DUP_CVRD_DPNT');
366 fnd_message.raise_error;
367 --
368 end if;
369 close c3;
370 end if;
371 --
372 end if;
373 --
374 hr_utility.set_location('Leaving:'||l_proc,10);
375 --
376 End chk_dpnt_person_id;
377 --
378 --
379 -- ----------------------------------------------------------------------------
380 -- |------< chk_prtt_enrt_rslt_id >------|
381 -- ----------------------------------------------------------------------------
382 --
383 -- Description
384 -- This procedure checks that a referenced foreign key actually exists
385 -- in the referenced table.
386 --
387 -- Pre-Conditions
388 -- None.
389 --
390 -- In Parameters
391 -- p_elig_cvrd_dpnt_id PK
392 -- p_prtt_enrt_rslt_id ID of FK column
393 -- p_effective_date session date
394 -- p_object_version_number object version number
395 --
396 -- Post Success
397 -- Processing continues
398 --
399 -- Post Failure
400 -- Error raised.
401 --
402 -- Access Status
403 -- Internal table handler use only.
404 --
405 Procedure chk_prtt_enrt_rslt_id (p_elig_cvrd_dpnt_id in number,
406 p_prtt_enrt_rslt_id in number,
407 p_validation_start_date in date,
408 p_validation_end_date in date,
409 p_effective_date in date,
410 p_business_group_id in number,
411 p_object_version_number in number) is
412 --
413 l_proc varchar2(72) := g_package||'chk_prtt_enrt_rslt_id';
414 l_api_updating boolean;
415 l_dummy varchar2(1);
416 --
417 --
418 --
419 cursor c1 is
420 select null
421 from ben_prtt_enrt_rslt_f a
422 where a.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
423 and a.prtt_enrt_rslt_stat_cd is null
424 and a.business_group_id + 0 = p_business_group_id
425 and p_validation_start_date <= effective_end_date
426 and p_validation_end_date >= effective_start_date
427 ;
428 --
429 Begin
430 --
431 hr_utility.set_location('Entering:'||l_proc,5);
432 --
433 l_api_updating := ben_pdp_shd.api_updating
434 (p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
435 p_effective_date => p_effective_date,
436 p_object_version_number => p_object_version_number);
437 --
438 if p_prtt_enrt_rslt_id is not null
439 and (l_api_updating
440 and nvl(p_prtt_enrt_rslt_id, hr_api.g_number)
441 <> nvl(ben_pdp_shd.g_old_rec.prtt_enrt_rslt_id, hr_api.g_number)
442 or not l_api_updating) then
443 --
444 -- check if prtt_enrt_rslt_id value exists in ben_prtt_enrt_rslt_f table
445 --
446 open c1;
447 --
448 fetch c1 into l_dummy;
449 if c1%notfound then
450 --
451 close c1;
452 --
453 -- raise error as FK does not relate to PK in ben_prtt_enrt_rslt_f
454 -- table.
455 --
456 ben_pdp_shd.constraint_error('BEN_ELIG_CVRD_DPNT_FK2');
457 --
458 end if;
459 --
460 close c1;
461 --
462 --
463 end if;
464 --
465 hr_utility.set_location('Leaving:'||l_proc,10);
466 --
467 End chk_prtt_enrt_rslt_id;
468 --
469 --
470 -- ----------------------------------------------------------------------------
471 -- |------< chk_max_num_dpnt >------|
472 -- ----------------------------------------------------------------------------
473 --
474 -- Description
475 -- This procedure checks that the number of covered dependents does not
476 -- exceed the maximum set for the PL or OIPL.
477 --
478 -- Pre-Conditions
479 -- None.
483 -- p_dpnt_person_id
480 --
481 -- In Parameters
482 -- p_elig_cvrd_dpnt_id PK
484 -- p_effective_date session date
485 -- p_object_version_number object version number
486 --
487 -- Post Success
488 -- Processing continues
489 --
490 -- Post Failure
491 -- Error raised.
492 --
493 -- Access Status
494 -- Internal table handler use only.
495 --
496 Procedure chk_max_num_dpnt (p_elig_cvrd_dpnt_id in number,
497 p_prtt_enrt_rslt_id in number,
498 p_dpnt_person_id in number,
499 p_cvg_strt_dt in date,
500 p_cvg_thru_dt in date,
501 p_effective_date in date,
502 p_business_group_id in number,
503 p_object_version_number in number) is
504 --
505 l_proc varchar2(72) := g_package||'chk_max_num_dpnt';
506 l_api_updating boolean;
507 --
508 l_temp varchar2(1);
509 l_total_num_dpnt number(15);
510 l_rlshp_num_dpnt number(15);
511 l_person_id number(15);
512 l_pl_id number(15);
513 l_oipl_id number(15);
514 l_opt_id number(15);
515 l_contact_type per_contact_relationships.contact_type%type ; -- UTF8 varchar2(30);
516 l_t_mx_dpnts_alwd_num number(15);
517 l_t_no_mx_num_dfnd_flag varchar2(1);
518 l_r_mx_dpnts_alwd_num number(15);
519 l_r_no_mx_num_dfnd_flag varchar2(1);
520 l_dsgn_rqmt_id number(15);
521 l_heir number(15);
522 --
523 -- get required info
524 --
525 cursor info1_c is
526 select r.person_id
527 ,r.pl_id
528 ,r.oipl_id
529 ,o.opt_id
530 from ben_prtt_enrt_rslt_f r ,
531 ben_oipl_f o
532 where r.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
533 and r.prtt_enrt_rslt_stat_cd is null
534 and r.business_group_id + 0 = p_business_group_id
535 and p_effective_date between r.effective_start_date
536 and r.effective_end_date
537 and o.oipl_id(+) = r.oipl_id
538 and o.business_group_id(+)= p_business_group_id
539 and p_effective_date between o.effective_start_date(+)
540 and o.effective_end_date(+)
541 ;
542 --
543 cursor info2_c is
544 select c.contact_type
545 from per_contact_relationships c
546 where c.person_id = l_person_id
547 and c.contact_person_id = p_dpnt_person_id
548 -- bug 1762932 added personal_flag
549 and nvl(c.personal_flag,'N') = 'Y'
550 and c.business_group_id + 0 = p_business_group_id
551 and p_effective_date between nvl(c.date_start, p_effective_date)
552 and nvl(c.date_end, p_effective_date)
553 ;
554 --
555 -- total designation requirements
556 --
557 cursor total_rqmt_c is
558 select mx_dpnts_alwd_num
559 ,no_mx_num_dfnd_flag
560 ,decode(oipl_id, null, decode(opt_id, null, 3, 2), 1) heir
561 from ben_dsgn_rqmt_f
562 where
563 ((nvl(pl_id, hr_api.g_number) = l_pl_id)
564 or (nvl(oipl_id, hr_api.g_number) = l_oipl_id)
565 or (nvl(opt_id, hr_api.g_number) = l_opt_id))
566 and dsgn_typ_cd = 'DPNT'
567 and grp_rlshp_cd is null
568 and business_group_id + 0 = p_business_group_id
569 and p_effective_date between effective_start_date
570 and effective_end_date
571 order by heir
572 ;
573
574 --
575 -- any designation requirements for this comp object?
576 --
577 cursor any_rqmt_c is
578 select 's'
579 from ben_dsgn_rqmt_f r
580 where ((nvl(pl_id, hr_api.g_number) = l_pl_id)
581 or (nvl(oipl_id, hr_api.g_number) = l_oipl_id)
582 or (nvl(opt_id, hr_api.g_number) = l_opt_id))
583 and r.dsgn_typ_cd = 'DPNT'
584 and r.business_group_id + 0 = p_business_group_id
585 and p_effective_date between nvl(r.effective_start_date, p_effective_date)
586 and nvl(r.effective_end_date, p_effective_date)
587 ;
588
589 --
590 -- designation requirement for relationship type of this dpnt
591 --
592 cursor rlshp_rqmt_c is
593 select r.mx_dpnts_alwd_num
594 ,r.no_mx_num_dfnd_flag
595 ,r.dsgn_rqmt_id
596 ,decode(oipl_id, null, decode(opt_id, null, 3, 2), 1) heir
597 from ben_dsgn_rqmt_f r,
598 ben_dsgn_rqmt_rlshp_typ dr
599 where ((nvl(pl_id, hr_api.g_number) = l_pl_id)
600 or (nvl(oipl_id, hr_api.g_number) = l_oipl_id)
601 or (nvl(opt_id, hr_api.g_number) = l_opt_id))
602 and r.dsgn_typ_cd = 'DPNT'
603 and r.business_group_id + 0 = p_business_group_id
604 and p_effective_date between nvl(r.effective_start_date, p_effective_date)
605 and nvl(r.effective_end_date, p_effective_date)
606 and dr.dsgn_rqmt_id = r.dsgn_rqmt_id
607 and dr.rlshp_typ_cd = l_contact_type
608 order by heir
609 ;
610 --
611 -- total number of covered dependents for the result
612 --
613 cursor total_num_dpnt_c is
614 select count(elig_cvrd_dpnt_id)
615 from ben_elig_cvrd_dpnt_f
616 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
617 and cvg_strt_dt is not null
618 and cvg_thru_dt = hr_api.g_eot
622 and effective_end_date
619 -- and cvrd_flag = 'Y'
620 and business_group_id + 0 = p_business_group_id
621 and p_effective_date between effective_start_date
623 and p_cvg_strt_dt <= nvl(cvg_thru_dt, hr_api.g_date)
624 and nvl(p_cvg_thru_dt, hr_api.g_date) >= cvg_strt_dt
625 ;
626 --
627 --
628 -- number of covered dependents of any of the rel types covered
629 -- by the appropriate dsgn rqmt.
630
631 cursor rlshp_num_dpnt_c is
632 select count(*)
633 from per_contact_relationships c
634 , ben_elig_cvrd_dpnt_f d
635 where
636 c.person_id = l_person_id
637 and c.contact_person_id = d.dpnt_person_id
638 and d.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
639 and d.cvg_strt_dt is not null
640 and d.cvg_thru_dt = hr_api.g_eot
641 and p_cvg_strt_dt <= nvl(d.cvg_thru_dt, hr_api.g_date)
642 and nvl(p_cvg_thru_dt, hr_api.g_date) >= d.cvg_strt_dt
643 and c.business_group_id + 0 = p_business_group_id
644 and p_effective_date between nvl(c.date_start, p_effective_date)
645 and nvl(c.date_end, p_effective_date)
646 and d.effective_end_date = hr_api.g_eot -- bug 1237204
647 and d.business_group_id + 0 = p_business_group_id
648 and c.contact_type in
649 (select rlshp_typ_cd
650 from ben_dsgn_rqmt_rlshp_typ
651 where dsgn_rqmt_id = l_dsgn_rqmt_id)
652 ;
653 --
654
655 Begin
656 --
657 hr_utility.set_location('Entering:'||l_proc,5);
658 --
659
660 l_api_updating := ben_pdp_shd.api_updating
661 (p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
662 p_effective_date => p_effective_date,
663 p_object_version_number => p_object_version_number);
664
665 -- check that we are inserting a new covered dpnt or updating an
666 -- existing not-covered dependent to 'covered'.
667
668 if p_prtt_enrt_rslt_id is not null and
669 p_cvg_strt_dt is not null and
670 p_cvg_thru_dt = hr_api.g_eot and
671 (not l_api_updating or
672 ben_pdp_shd.g_old_rec.cvg_strt_dt = null)
673 then
674 --
675 hr_utility.set_location('open info1_c :'||l_proc,10);
676 --
677 open info1_c;
678 --
679 fetch info1_c into l_person_id
680 ,l_pl_id
681 ,l_oipl_id
682 ,l_opt_id
683 ;
684 --
685 if info1_c%notfound then
686 --
687 close info1_c;
688 --
689 -- raise error as FK does not relate to PK in ben_prtt_enrt_rslt_f
690 -- table.
691 --
692 ben_pdp_shd.constraint_error('BEN_ELIG_CVRD_DPNT_FK2');
693 --
694 else
695 --
696 --
697 close info1_c;
698 open info2_c;
699 fetch info2_c into l_contact_type;
700 if info2_c%notfound then
701 --
702 close info2_c;
703 --
704 -- raise error as there are no contact relationship
705 --
706 fnd_message.set_name('BEN', 'BEN_91652_NO_CNTCT_RLSHP');
707 fnd_message.raise_error;
708 --
709 else
710 -- Check if there are any requirements at all
711 -- Check total max requirement is done as part of post-forms-commit
712 -- process. The procedure is chk_max_num_dpnt_for_pen
713 null;
714 end if;
715 end if;
716 end if;
717 --
718 hr_utility.set_location('Leaving:'||l_proc,99);
719 --
720 end chk_max_num_dpnt;
721
722 --
723 --
724 -- ----------------------------------------------------------------------------
725 -- |------< chk_crt_ordr >------|
726 -- ----------------------------------------------------------------------------
727 --
728 -- Description
729 -- This procedure is used to enforce that if a dependent has an active court
730 -- order they can not be uncovered.
731 --
732 -- Pre Conditions
733 -- None.
734 --
735 -- In Parameters
736 -- dpnt_person_id
737 -- cvg_strt_dt
738 -- cvg_thru_dt
739 -- business_group_id
740 -- effective_date effective date
741 --
742 -- Post Success
743 -- Processing continues
744 --
745 -- Post Failure
746 -- Error handled by procedure
747 --
748 -- Access Status
749 -- Internal table handler use only.
750 --
751 Procedure chk_crt_ordr(p_dpnt_person_id in number,
752 p_cvg_strt_dt in date,
753 p_cvg_thru_dt in date,
754 p_business_group_id in number,
755 p_effective_date in date) is
756 --
757 l_proc varchar2(72) := g_package||'chk_crt_ordr';
758 l_dummy varchar2(1);
759 --
760 cursor c1 is
761 select null
762 from ben_crt_ordr crt,
763 ben_crt_ordr_cvrd_per crc
764 where crc.person_id = p_dpnt_person_id
765 and crc.business_group_id = p_business_group_id
766 and crc.crt_ordr_id = crt.crt_ordr_id;
767 --
768 Begin
769 --
770 hr_utility.set_location('Entering:'||l_proc, 5);
771 --
772 --
773 if p_cvg_thru_dt <> hr_api.g_eot then
774 --
775 -- check if there is an active court order.
776 --
777 open c1;
778 --
779 -- fetch value from cursor if it returns a record then there
780 -- is an open court order for the dependent
781 --
782 fetch c1 into l_dummy;
783 if c1%found then
784 --
785 close c1;
786 --
787 -- raise error
788 --
789 fnd_message.set_name('BEN','BEN_92093_DPNT_ACTV_CRTORDR'); fnd_message.raise_error;
790 --
791 end if;
792 --
793 close c1;
794 --
795 end if;
796 --
797 hr_utility.set_location('Leaving:'||l_proc,10);
798 --
799 end chk_crt_ordr;
800 --
801 -- ---------------------------------------------------------------------------
802 -- |------------------------< crt_ordr_warning >----------------------------|
803 -- ---------------------------------------------------------------------------
804 -- Procedure used to create warning messages for crt_ordrs.
805 --
806 -- Description
807 -- This procedure is used to create warning messages for persons
808 -- not designated as covered dependents but reqired to be covered
809 -- under court orders.
810 --
811 -- Pre Conditions
812 -- None.
813 --
814 -- In Parameters
815 -- prtt_enrt_rslt_id PK of record being inserted or updated.
816 -- effective_date effective date
817 --
818 -- Post Success
819 -- Processing continues
820 --
821 -- Post Failure
822 -- Error handled by procedure
823 --
824 -- Access Status
825 -- Internal table handler use only.
826 --
827 PROCEDURE crt_ordr_warning (
828 p_prtt_enrt_rslt_id IN NUMBER,
829 p_effective_date IN DATE,
830 p_business_group_id IN NUMBER
831 )
832 IS
833 --
834 l_proc VARCHAR2 (72) := g_package || 'crt_ordr_warning';
835 l_api_updating BOOLEAN;
836 l_level VARCHAR2 (30) := 'PL';
837 l_code VARCHAR2 (30);
838 --
839 CURSOR c_rslt
840 IS
841 SELECT person_id, pgm_id, pl_id, ptip_id, pl_typ_id,
842 enrt_cvg_strt_dt, enrt_cvg_thru_dt, per_in_ler_id
843 FROM ben_prtt_enrt_rslt_f rslt
844 WHERE rslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
845 and rslt.prtt_enrt_rslt_stat_cd is null
846 AND rslt.business_group_id = p_business_group_id
847 AND p_effective_date BETWEEN rslt.effective_start_date
848 AND rslt.effective_end_date;
849 --
850 rslt_person_id NUMBER (15);
851 rslt_pgm_id NUMBER (15);
852 rslt_pl_id NUMBER (15);
853 rslt_ptip_id NUMBER (15);
854 rslt_pl_typ_id NUMBER (15);
855 rslt_enrt_cvg_strt_dt DATE;
856 rslt_enrt_cvg_thru_dt DATE;
857 rslt_per_in_ler_id NUMBER (15);
858 --
859 CURSOR c_pgm
860 IS
861 SELECT dpnt_dsgn_lvl_cd, dpnt_dsgn_cd, pgm_typ_cd
862 FROM ben_pgm_f pgm
863 WHERE pgm.pgm_id = rslt_pgm_id
864 AND pgm.business_group_id = p_business_group_id
865 AND p_effective_date BETWEEN pgm.effective_start_date
866 AND pgm.effective_end_date;
867 --
868 l_pgm c_pgm%ROWTYPE;
869 --
870 CURSOR c_plan
871 IS
872 SELECT pl.dpnt_dsgn_cd
873 FROM ben_pl_f pl
874 WHERE pl.pl_id = rslt_pl_id
875 AND pl.business_group_id = p_business_group_id
876 AND p_effective_date BETWEEN pl.effective_start_date
877 AND pl.effective_end_date;
878 --
879 l_plan c_plan%ROWTYPE;
880 --
881 CURSOR c_ptip
882 IS
883 SELECT ptip.dpnt_dsgn_cd
884 FROM ben_ptip_f ptip
885 WHERE ptip.ptip_id = rslt_ptip_id
886 AND ptip.business_group_id = p_business_group_id
887 AND p_effective_date BETWEEN ptip.effective_start_date
888 AND ptip.effective_end_date;
889 --
890 l_ptip c_ptip%ROWTYPE;
891 --
892 CURSOR c_plan_flag
893 IS
894 SELECT pl.alws_qmcso_flag, pl.alws_qdro_flag, pl.pl_typ_id
895 FROM ben_pl_f pl
896 WHERE pl.pl_id = rslt_pl_id
897 AND pl.business_group_id = p_business_group_id
898 AND p_effective_date BETWEEN pl.effective_start_date
899 AND pl.effective_end_date;
900 --
901 l_alws_qmcso VARCHAR2 (30);
902 l_alws_qdro VARCHAR2 (30);
903 l_pl_typ_id_pl NUMBER (15);
904 l_benefit_name ben_pl_typ_f.NAME%TYPE;
905 --
906 l_lf_evt_ocrd_dt DATE;
907 --
908 CURSOR c_lf_evt_ocrd_dt
909 IS
910 SELECT lf_evt_ocrd_dt
911 FROM ben_per_in_ler pil
912 WHERE pil.per_in_ler_id = rslt_per_in_ler_id;
913 --
914 CURSOR c_crt_ordr
915 IS
916 SELECT per.first_name || ' ' || per.last_name NAME, lkp.meaning,
917 cvr.person_id, bpl.NAME, crt.CRT_ORDR_TYP_CD
918 FROM ben_crt_ordr crt,
919 ben_crt_ordr_cvrd_per cvr,
920 per_all_people_f per,
921 per_contact_relationships con,
922 hr_lookups lkp,
923 ben_pl_f bpl
924 WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
925 AND crt.person_id = rslt_person_id
926 AND crt.pl_id = rslt_pl_id
927 AND crt.crt_ordr_id = cvr.crt_ordr_id
928 AND cvr.person_id = per.person_id
929 AND cvr.person_id = con.contact_person_id
930 AND con.contact_type = lkp.lookup_code
931 AND lkp.lookup_type = 'CONTACT'
932 AND p_effective_date BETWEEN NVL (lkp.start_date_active,
933 p_effective_date
934 )
935 AND NVL (lkp.end_date_active,
936 p_effective_date
937 )
938 AND GREATEST (l_lf_evt_ocrd_dt, rslt_enrt_cvg_strt_dt)
939 BETWEEN GREATEST
940 (NVL (apls_perd_strtg_dt,
941 p_effective_date
942 ),
943 NVL (detd_qlfd_ordr_dt,
944 apls_perd_strtg_dt
945 )
946 )
947 AND NVL (apls_perd_endg_dt,
948 rslt_enrt_cvg_thru_dt
949 )
950 AND crt.business_group_id = p_business_group_id
951 AND cvr.business_group_id = p_business_group_id
952 AND p_effective_date BETWEEN NVL (con.date_start, p_effective_date)
953 AND NVL (con.date_end, p_effective_date)
954 AND con.business_group_id = p_business_group_id
955 AND bpl.pl_id = rslt_pl_id
956 AND p_effective_date BETWEEN NVL (bpl.effective_start_date,
957 p_effective_date
958 )
959 AND NVL (bpl.effective_end_date,
960 p_effective_date
961 )
962 UNION
963 SELECT per.first_name || ' ' || per.last_name NAME, lkp.meaning,
964 cvr.person_id, bpt.NAME, crt.CRT_ORDR_TYP_CD
965 FROM ben_crt_ordr crt,
966 ben_crt_ordr_cvrd_per cvr,
967 per_all_people_f per,
968 per_contact_relationships con,
969 hr_lookups lkp,
970 ben_pl_typ_f bpt
971 WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
972 AND crt.person_id = rslt_person_id
973 AND crt.pl_typ_id = l_pl_typ_id_pl
974 AND crt.crt_ordr_id = cvr.crt_ordr_id
975 AND cvr.person_id = per.person_id
976 AND cvr.person_id = con.contact_person_id
977 AND con.contact_type = lkp.lookup_code
978 AND lkp.lookup_type = 'CONTACT'
979 AND p_effective_date BETWEEN NVL (lkp.start_date_active,
980 p_effective_date
981 )
982 AND NVL (lkp.end_date_active,
983 p_effective_date
984 )
985 AND GREATEST(l_lf_evt_ocrd_dt, rslt_enrt_cvg_strt_dt)
986 BETWEEN GREATEST
987 (NVL (apls_perd_strtg_dt,
988 p_effective_date
989 ),
990 NVL (detd_qlfd_ordr_dt,
991 apls_perd_strtg_dt
992 )
993 )
994 AND NVL (apls_perd_endg_dt,
995 rslt_enrt_cvg_thru_dt
996 )
997 AND crt.business_group_id = p_business_group_id
998 AND cvr.business_group_id = p_business_group_id
999 AND p_effective_date BETWEEN NVL (con.date_start, p_effective_date)
1000 AND NVL (con.date_end, p_effective_date)
1001 AND con.business_group_id = p_business_group_id
1002 AND bpt.pl_typ_id = l_pl_typ_id_pl
1003 AND p_effective_date BETWEEN NVL (bpt.effective_start_date,
1004 p_effective_date
1005 )
1006 AND NVL (bpt.effective_end_date,
1007 p_effective_date
1008 );
1009
1010 --
1011 l_name VARCHAR2 (500);
1012 l_contact_type per_contact_relationships.contact_type%TYPE;
1013 l_dpnt_id NUMBER (15);
1014 l_crt_ordr_typ_cd VARCHAR2(30);
1015 l_crt_ordr_meaning VARCHAR2(80);
1016 --
1017 --
1018 -- Bug 4718038 : Check PDP record for court order warning as of life event occurred date
1019 --
1020 CURSOR c_elig_dpnt
1021 IS
1022 SELECT NULL
1023 FROM ben_elig_cvrd_dpnt_f pdp, ben_per_in_ler pil
1024 WHERE pdp.dpnt_person_id = l_dpnt_id
1025 AND pdp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1026 AND p_effective_date BETWEEN pdp.effective_start_date
1027 AND pdp.effective_end_date
1028 AND GREATEST(pil.lf_evt_ocrd_dt, rslt_enrt_cvg_strt_dt ) BETWEEN cvg_strt_dt
1029 AND cvg_thru_dt
1030 AND pdp.business_group_id = p_business_group_id
1031 AND pil.business_group_id = p_business_group_id
1032 AND pdp.per_in_ler_id = pil.per_in_ler_id;
1033
1034 --
1035 l_dummy VARCHAR2 (1);
1036 l_message fnd_new_messages.message_name%TYPE := 'BEN_92430_CRT_ORD_WARNING';
1037 l_cobra_pgm BOOLEAN := FALSE;
1038 --
1039 BEGIN
1040 --
1041 hr_utility.set_location ('Entering:' || l_proc, 5);
1042 --
1043 hr_utility.set_location ('Checking court order at PDP level : ' || p_prtt_enrt_Rslt_id, 12);
1044 --
1045 IF p_prtt_enrt_rslt_id IS NOT NULL
1046 THEN
1047 --
1048 OPEN c_rslt;
1049 --
1050 FETCH c_rslt INTO rslt_person_id,
1051 rslt_pgm_id,
1052 rslt_pl_id,
1053 rslt_ptip_id,
1054 rslt_pl_typ_id,
1055 rslt_enrt_cvg_strt_dt,
1056 rslt_enrt_cvg_thru_dt,
1057 rslt_per_in_ler_id;
1058 --
1059 IF c_rslt%FOUND
1060 THEN
1061 --
1062 IF rslt_pgm_id IS NOT NULL
1063 THEN
1064 --
1065 -- find the level from the program
1066 --
1067 OPEN c_pgm;
1068 --
1072 THEN
1069 FETCH c_pgm INTO l_pgm;
1070 --
1071 IF c_pgm%NOTFOUND
1073 --
1074 CLOSE c_pgm;
1075
1076 fnd_message.set_name ('BEN', 'BEN_91470_PGM_NOT_FOUND');
1077 fnd_message.raise_error;
1078 --
1079 END IF;
1080 --
1081 CLOSE c_pgm;
1082 --
1083 l_level := l_pgm.dpnt_dsgn_lvl_cd;
1084 --
1085 IF l_pgm.pgm_typ_cd IN ('COBRANFLX', 'COBRAFLX')
1086 THEN
1087 --
1088 l_cobra_pgm := TRUE;
1089 --
1090 END IF;
1091 --
1092 ELSE
1093 --
1094 -- PLAN level
1095 --
1096 l_level := 'PL';
1097 --
1098 END IF;
1099 --
1100 -- Retrieve designation code
1101 --
1102 hr_utility.set_location ('Level = ' || l_level, 40);
1103 --
1104 IF l_level = 'PGM'
1105 THEN
1106 --
1107 l_code := l_pgm.dpnt_dsgn_cd;
1108 --
1109 ELSIF l_level = 'PTIP'
1110 THEN
1111 --
1112 OPEN c_ptip;
1113 --
1114 FETCH c_ptip INTO l_ptip;
1115 --
1116 IF c_ptip%NOTFOUND
1117 THEN
1118 --
1119 CLOSE c_ptip;
1120
1121 fnd_message.set_name ('BEN', 'BEN_91471_MISSING_PLAN_TYPE');
1122 fnd_message.raise_error;
1123 --
1124 END IF;
1125 --
1126 CLOSE c_ptip;
1127 --
1128 l_code := l_ptip.dpnt_dsgn_cd;
1129 --
1130 ELSIF l_level = 'PL'
1131 THEN
1132 --
1133 OPEN c_plan;
1134 --
1135 FETCH c_plan INTO l_plan;
1136 --
1137 IF c_plan%NOTFOUND
1138 THEN
1139 --
1140 CLOSE c_plan;
1141 --
1142 fnd_message.set_name ('BEN', 'BEN_91472_PLAN_NOT_FOUND');
1143 fnd_message.raise_error;
1144 --
1145 END IF;
1146 --
1147 CLOSE c_plan;
1148 --
1149 l_code := l_plan.dpnt_dsgn_cd;
1150 --
1151 ELSE
1152 --
1153 l_code := NULL;
1154 --
1155 END IF;
1156 --
1157 hr_utility.set_location ('dsgn code = ' || l_code, 40);
1158 --
1159 IF l_code IS NOT NULL AND
1160 NOT l_cobra_pgm
1161 THEN
1162 --
1163 OPEN c_plan_flag;
1164 --
1165 FETCH c_plan_flag INTO l_alws_qmcso,
1166 l_alws_qdro,
1167 l_pl_typ_id_pl;
1168 --
1169 IF c_plan_flag%NOTFOUND
1170 THEN
1171 --
1172 CLOSE c_plan_flag;
1173 --
1174 fnd_message.set_name ('BEN', 'BEN_91472_PLAN_NOT_FOUND');
1175 fnd_message.raise_error;
1176 --
1177 END IF;
1178 --
1179 CLOSE c_plan_flag;
1180 --
1181 OPEN c_lf_evt_ocrd_dt;
1182 --
1183 FETCH c_lf_evt_ocrd_dt into l_lf_evt_ocrd_dt;
1184 --
1185 CLOSE c_lf_evt_ocrd_dt;
1186 --
1187 /*
1188 hr_utility.set_location('ACE l_lf_evt_ocrd_dt = ' || l_lf_evt_ocrd_dt, 9999);
1189 hr_utility.set_location('ACE rslt_enrt_cvg_strt_dt = ' || rslt_enrt_cvg_strt_dt, 9999);
1190 hr_utility.set_location('ACE p_prtt_enrt_rslt_id = ' || p_prtt_enrt_rslt_id, 9999);
1191 hr_utility.set_location('ACE p_effective_date = ' || p_effective_date, 9999);
1192 */
1193
1194 OPEN c_crt_ordr;
1195 --
1196 LOOP
1197 --
1198 FETCH c_crt_ordr INTO l_name,
1199 l_contact_type,
1200 l_dpnt_id,
1201 l_benefit_name,
1202 l_crt_ordr_typ_cd;
1203 --
1204 EXIT WHEN c_crt_ordr%NOTFOUND;
1205 --
1206 hr_utility.set_location('Court Order Found', 9999);
1207 /*
1208 hr_utility.set_location ('dpnt name = ' || l_name, 40);
1209 hr_utility.set_location ('type = ' || l_contact_type, 40);
1210 hr_utility.set_location ('dpnt id = ' || l_dpnt_id, 40);
1211 */
1212 --
1213 OPEN c_elig_dpnt;
1214 --
1215 FETCH c_elig_dpnt INTO l_dummy;
1216 --
1217 IF c_elig_dpnt%NOTFOUND
1218 THEN
1219 --
1220 hr_utility.set_location('C_ELIG_DPNT Returned No Rows', 9999);
1221 --
1222 l_crt_ordr_meaning := hr_general.decode_lookup
1223 (p_lookup_type => 'BEN_CRT_ORDR_TYP',
1224 p_lookup_code => l_crt_ordr_typ_cd
1225 );
1226 --
1227 ben_warnings.load_warning
1228 (p_application_short_name => 'BEN',
1229 p_message_name => l_message,
1230 p_parma => l_benefit_name,
1231 p_parmb => l_contact_type || ' , ' || l_name,
1232 p_parmc => l_crt_ordr_meaning,
1233 p_person_id => rslt_person_id
1234 );
1235 --
1236 END IF;
1237 --
1238 CLOSE c_elig_dpnt;
1239 --
1240 END LOOP;
1241 --
1242 CLOSE c_crt_ordr;
1243 --
1244 END IF;
1245 --
1246 END IF;
1247 --
1248 CLOSE c_rslt;
1249 --
1250 END IF;
1251
1252 hr_utility.set_location ('Leaving:' || l_proc, 10);
1253 END crt_ordr_warning;
1254
1255 -- ---------------------------------------------------------------------------
1256 -- |------------------------< crt_ordr_warning_ss >----------------------------|
1257 -- ---------------------------------------------------------------------------
1258 -- Function is called from SS to check court order(s) for a dependent.
1259 --
1260 -- In Parameters
1261 -- p_prtt_enrt_rslt_id PK of enrollment record
1262 -- p_enrt_cvg_strt_dt Enrollment coverage start date
1263 -- p_person_id PK of person
1264 -- p_dpnt_person_id PK of contact person
1265 -- p_pl_id PK of plan to query for court order
1266 -- p_pl_typ_id PK of plan type to query for court order
1267 -- p_effective_date Effective date
1268 -- p_per_in_ler_id PK of person LE
1269 -- p_business_group_id Business group in which we need to query for court order(s)
1270 --
1271 -- Out Parameters
1272 -- l_return Y/N Flag denoting whether court order(s) exist
1273 -- for above IN parameters
1274 --
1275 Function crt_ordr_warning_ss
1276 (p_prtt_enrt_rslt_id in number
1277 ,p_enrt_cvg_strt_dt in date
1278 ,p_enrt_cvg_thru_dt in date
1279 ,p_person_id in number
1280 ,p_dpnt_person_id in number
1281 ,p_pl_id in number
1282 ,p_pl_typ_id in number
1283 ,p_effective_date in date
1284 ,p_per_in_ler_id in number
1285 ,p_business_group_id in number)
1286 Return VARCHAR2 is
1287 --
1288 cursor c_leod is
1289 select lf_evt_ocrd_dt
1290 from ben_per_in_ler
1291 where per_in_ler_id = p_per_in_ler_id;
1292 --
1293 cursor c_crt_ordr(p_lf_evt_ocrd_dt date) is
1294 select 'Y'
1295 from ben_crt_ordr crt,
1296 ben_crt_ordr_cvrd_per cvr
1297 where crt.crt_ordr_typ_cd in ('QMCSO','QDRO')
1298 and crt.person_id = p_person_id
1299 and (crt.pl_id = p_pl_id or crt.pl_typ_id = p_pl_typ_id)
1300 and crt.crt_ordr_id = cvr.crt_ordr_id
1301 and cvr.person_id = p_dpnt_person_id
1302 and (greatest(p_enrt_cvg_strt_dt, p_lf_evt_ocrd_dt) between greatest(nvl(crt.apls_perd_strtg_dt,p_effective_date)
1303 ,nvl(crt.detd_qlfd_ordr_dt,crt.apls_perd_strtg_dt))
1304 and nvl(crt.apls_perd_endg_dt,p_enrt_cvg_thru_dt))
1305 and crt.business_group_id = p_business_group_id
1306 and cvr.business_group_id = p_business_group_id;
1307 --
1308 l_leod date;
1309 l_return VARCHAR2(1) := 'N';
1310 Begin
1311 --
1312 open c_leod;
1313 fetch c_leod into l_leod;
1314 close c_leod;
1315 --
1316 open c_crt_ordr(l_leod);
1317 fetch c_crt_ordr into l_return;
1318 close c_crt_ordr;
1319
1320 return l_return;
1321 --
1322 End crt_ordr_warning_ss;
1323 --
1324 /*--Bug#5088571
1325 -- ---------------------------------------------------------------------------
1326 -- |------------------------< chk_dpnt_strt_end_dt >----------------------------|
1327 -- ---------------------------------------------------------------------------
1328 -- Description
1329 -- This procedure is used to check whether the Rate Start date is greater than Rate End date.
1330 --
1331
1332 procedure chk_dpnt_strt_end_dt
1333 (p_cvg_strt_dt in date,
1334 p_cvg_thru_dt in date,
1338 l_proc varchar2(72) := g_package||'chk_dpnt_strt_end_dt';
1335 p_prtt_enrt_rslt_id in number
1336 ) is
1337 --
1339 l_person_id number;
1340 l_message_name varchar2(500) := 'BEN_94592_RT_STRT_GT_END_DT';
1341 --
1342 cursor c_person_id is
1343 select person_id
1344 from ben_prtt_enrt_rslt_f pen
1345 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
1346 --
1347 Begin
1348 --
1349 hr_utility.set_location('Entering:'||l_proc, 5);
1350 --
1351 open c_person_id;
1352 fetch c_person_id into l_person_id;
1353 close c_person_id;
1354 --
1355 if p_cvg_strt_dt > p_cvg_thru_dt then
1356 benutils.write(p_text=>fnd_message.get);
1357 ben_warnings.load_warning
1358 (p_application_short_name => 'BEN'
1359 ,p_message_name => l_message_name
1360 ,p_parma => 'Dependent Coverage End Date' || ' ' || fnd_date.date_to_displaydate(p_cvg_thru_dt)
1361 ,p_parmb => 'Dependent Coverage Start Date' ||' '|| fnd_date.date_to_displaydate(p_cvg_strt_dt)
1362 ,p_person_id => l_person_id
1363 );
1364 end if;
1365 --
1366 hr_utility.set_location('Leaving:'||l_proc,10);
1367 --
1368 end chk_dpnt_strt_end_dt;
1369 --
1370 ----Bug#5088571*/
1371 -- ----------------------------------------------------------------------------
1372 -- |--------------------------< dt_update_validate >--------------------------|
1373 -- ----------------------------------------------------------------------------
1374 -- {Start Of Comments}
1375 --
1376 -- Description:
1377 -- This procedure is used for referential integrity of datetracked
1378 -- parent entities when a datetrack update operation is taking place
1379 -- and where there is no cascading of update defined for this entity.
1380 --
1381 -- Prerequisites:
1382 -- This procedure is called from the update_validate.
1383 --
1384 -- In Parameters:
1385 --
1386 -- Post Success:
1387 -- Processing continues.
1388 --
1389 -- Post Failure:
1390 --
1391 -- Developer Implementation Notes:
1392 -- This procedure should not need maintenance unless the HR Schema model
1393 -- changes.
1394 --
1395 -- Access Status:
1396 -- Internal Row Handler Use Only.
1397 --
1398 -- {End Of Comments}
1399 -- ----------------------------------------------------------------------------
1400 Procedure dt_update_validate
1401 (p_prtt_enrt_rslt_id in number default hr_api.g_number,
1402 p_datetrack_mode in varchar2,
1403 p_validation_start_date in date,
1404 p_validation_end_date in date) Is
1405 --
1406 l_proc varchar2(72) := g_package||'dt_update_validate';
1407 l_integrity_error Exception;
1408 l_table_name all_tables.table_name%TYPE;
1409 --
1410 Begin
1411 hr_utility.set_location('Entering:'||l_proc, 5);
1412 --
1413 -- Ensure that the p_datetrack_mode argument is not null
1414 --
1415 hr_api.mandatory_arg_error
1416 (p_api_name => l_proc,
1417 p_argument => 'datetrack_mode',
1418 p_argument_value => p_datetrack_mode);
1419 --
1420 -- Only perform the validation if the datetrack update mode is valid
1421 --
1422 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
1423 --
1424 --
1425 -- Ensure the arguments are not null
1426 --
1427 hr_api.mandatory_arg_error
1428 (p_api_name => l_proc,
1429 p_argument => 'validation_start_date',
1430 p_argument_value => p_validation_start_date);
1431 --
1432 hr_api.mandatory_arg_error
1433 (p_api_name => l_proc,
1434 p_argument => 'validation_end_date',
1435 p_argument_value => p_validation_end_date);
1436 --
1437 /*
1438 If ((nvl(p_prtt_enrt_rslt_id, hr_api.g_number) <> hr_api.g_number) and
1439 NOT (dt_api.check_min_max_dates
1440 (p_base_table_name => 'ben_prtt_enrt_rslt_f',
1441 p_base_key_column => 'prtt_enrt_rslt_id',
1442 p_base_key_value => p_prtt_enrt_rslt_id,
1443 p_from_date => p_validation_start_date,
1444 p_to_date => p_validation_end_date))) Then
1445 l_table_name := 'ben_prtt_enrt_rslt_f';
1446 Raise l_integrity_error;
1447 End If;
1448 */
1449 --
1450 End If;
1451 --
1452 hr_utility.set_location(' Leaving:'||l_proc, 10);
1453 Exception
1454 When l_integrity_error Then
1455 --
1456 -- A referential integrity check was violated therefore
1457 -- we must error
1458 --
1459 -- ben_utility.parent_integrity_error(p_table_name => l_table_name);
1460 --
1461 ben_utility.parent_integrity_error(p_table_name => l_table_name);
1462
1463 When Others Then
1464 --
1465 -- An unhandled or unexpected error has occurred which
1466 -- we must report
1467 --
1468 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1469 fnd_message.set_token('PROCEDURE', l_proc);
1470 fnd_message.set_token('STEP','15');
1471 fnd_message.raise_error;
1472 End dt_update_validate;
1473 --
1474 -- ----------------------------------------------------------------------------
1475 -- |--------------------------< dt_delete_validate >--------------------------|
1476 -- ----------------------------------------------------------------------------
1477 -- {Start Of Comments}
1478 --
1479 -- Description:
1480 -- This procedure is used for referential integrity of datetracked
1481 -- child entities when either a datetrack DELETE or ZAP is in operation
1485 -- dates.
1482 -- and where there is no cascading of delete defined for this entity.
1483 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1484 -- datetracked child rows exist between the validation start and end
1486 --
1487 -- Prerequisites:
1488 -- This procedure is called from the delete_validate.
1489 --
1490 -- In Parameters:
1491 --
1492 -- Post Success:
1493 -- Processing continues.
1494 --
1495 -- Post Failure:
1496 -- If a row exists by determining the returning Boolean value from the
1497 -- generic dt_api.rows_exist function then we must supply an error via
1498 -- the use of the local exception handler l_rows_exist.
1499 --
1500 -- Developer Implementation Notes:
1501 -- This procedure should not need maintenance unless the HR Schema model
1502 -- changes.
1503 --
1504 -- Access Status:
1505 -- Internal Row Handler Use Only.
1506 --
1507 -- {End Of Comments}
1508 -- ----------------------------------------------------------------------------
1509 Procedure dt_delete_validate
1510 (p_elig_cvrd_dpnt_id in number,
1511 p_datetrack_mode in varchar2,
1512 p_validation_start_date in date,
1513 p_validation_end_date in date) Is
1514 --
1515 l_proc varchar2(72) := g_package||'dt_delete_validate';
1516 l_rows_exist Exception;
1517 l_table_name all_tables.table_name%TYPE;
1518 --
1519 Begin
1520 hr_utility.set_location('Entering:'||l_proc, 5);
1521 --
1522 -- Ensure that the p_datetrack_mode argument is not null
1523 --
1524 hr_api.mandatory_arg_error
1525 (p_api_name => l_proc,
1526 p_argument => 'datetrack_mode',
1527 p_argument_value => p_datetrack_mode);
1528 --
1529 -- Only perform the validation if the datetrack mode is either
1530 -- DELETE or ZAP
1531 --
1532 If (p_datetrack_mode = 'DELETE' or
1533 p_datetrack_mode = 'ZAP') then
1534 --
1535 --
1536 -- Ensure the arguments are not null
1537 --
1538 hr_api.mandatory_arg_error
1539 (p_api_name => l_proc,
1540 p_argument => 'validation_start_date',
1541 p_argument_value => p_validation_start_date);
1542 --
1543 hr_api.mandatory_arg_error
1544 (p_api_name => l_proc,
1545 p_argument => 'validation_end_date',
1546 p_argument_value => p_validation_end_date);
1547 --
1548 hr_api.mandatory_arg_error
1549 (p_api_name => l_proc,
1550 p_argument => 'elig_cvrd_dpnt_id',
1551 p_argument_value => p_elig_cvrd_dpnt_id);
1552 --
1553 If (dt_api.rows_exist
1554 (p_base_table_name => 'ben_cvrd_dpnt_ctfn_prvdd_f',
1555 p_base_key_column => 'elig_cvrd_dpnt_id',
1556 p_base_key_value => p_elig_cvrd_dpnt_id,
1557 p_from_date => p_validation_start_date,
1558 p_to_date => p_validation_end_date)) Then
1559 l_table_name := 'ben_cvrd_dpnt_ctfn_prvdd_f';
1560 Raise l_rows_exist;
1561 End If;
1562 --
1563 End If;
1564 --
1565 hr_utility.set_location(' Leaving:'||l_proc, 10);
1566 Exception
1567 When l_rows_exist Then
1568 --
1569 -- A referential integrity check was violated therefore
1570 -- we must error
1571 --
1572 -- ben_utility.child_exists_error(p_table_name => l_table_name);
1573 --
1574 ben_utility.child_exists_error(p_table_name => l_table_name);
1575 When Others Then
1576 --
1577 -- An unhandled or unexpected error has occurred which
1578 -- we must report
1579 --
1580 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1581 fnd_message.set_token('PROCEDURE', l_proc);
1582 fnd_message.set_token('STEP','15');
1583 fnd_message.raise_error;
1584 End dt_delete_validate;
1585 --
1586 -- ----------------------------------------------------------------------------
1587 -- |---------------------------< insert_validate >----------------------------|
1588 -- ----------------------------------------------------------------------------
1589 Procedure insert_validate
1590 (p_rec in ben_pdp_shd.g_rec_type,
1591 p_effective_date in date,
1592 p_datetrack_mode in varchar2,
1593 p_validation_start_date in date,
1594 p_validation_end_date in date) is
1595 --
1596 l_proc varchar2(72) := g_package||'insert_validate';
1597 --
1598 Begin
1599 hr_utility.set_location('Entering:'||l_proc, 5);
1600 --
1601 -- Call all supporting business operations
1602 --
1603 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1604 --
1605 chk_elig_cvrd_dpnt_id
1606 (p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1607 p_effective_date => p_effective_date,
1608 p_object_version_number => p_rec.object_version_number);
1609 --
1610 chk_max_num_dpnt
1611 (p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1612 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1613 p_dpnt_person_id => p_rec.dpnt_person_id,
1614 p_cvg_strt_dt => p_rec.cvg_strt_dt,
1615 p_cvg_thru_dt => p_rec.cvg_thru_dt,
1616 p_effective_date => p_effective_date,
1617 p_business_group_id => p_rec.business_group_id,
1618 p_object_version_number => p_rec.object_version_number);
1619 --
1620 --chk_cvg_dates
1621 --(p_cvg_strt_dt => p_rec.cvg_strt_dt,
1622 -- p_cvg_thru_dt => p_rec.cvg_thru_dt);
1623 --
1624 chk_dpnt_person_id
1625 (p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1626 p_dpnt_person_id => p_rec.dpnt_person_id,
1627 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1631 p_cvg_strt_dt => p_rec.cvg_strt_dt,
1628 p_validation_start_date => p_validation_start_date,
1629 p_validation_end_date => p_validation_end_date,
1630 p_effective_date => p_effective_date,
1632 p_business_group_id => p_rec.business_group_id,
1633 p_object_version_number => p_rec.object_version_number);
1634 --
1635 --
1636 -- chk_prtt_enrt_rslt_id
1637 -- (p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1638 -- p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1639 -- p_validation_start_date => p_validation_start_date,
1640 -- p_validation_end_date => p_validation_end_date,
1641 -- p_effective_date => p_effective_date,
1642 -- p_business_group_id => p_rec.business_group_id,
1643 -- p_object_version_number => p_rec.object_version_number);
1644
1645 --
1646 chk_ovrdn_flag
1647 (p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1648 p_ovrdn_flag => p_rec.ovrdn_flag,
1649 p_effective_date => p_effective_date,
1650 p_object_version_number => p_rec.object_version_number);
1651 --
1652 chk_cvg_pndg_flag
1653 (p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1654 p_cvg_pndg_flag => p_rec.cvg_pndg_flag,
1655 p_effective_date => p_effective_date,
1656 p_object_version_number => p_rec.object_version_number);
1657 --
1658 /*
1659 Bug 3756863 : Moved to POST_INSERT
1660 --
1661 crt_ordr_warning
1662 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1663 p_effective_date => p_effective_date,
1664 p_business_group_id => p_rec.business_group_id);
1665 */
1666 --
1667 /* chk_dpnt_strt_end_dt
1668 (p_cvg_strt_dt => p_rec.cvg_strt_dt,
1669 p_cvg_thru_dt => p_rec.cvg_thru_dt,
1670 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id); */
1671 --
1672 hr_utility.set_location(' Leaving:'||l_proc, 10);
1673 --
1674 End insert_validate;
1675 --
1676 -- ----------------------------------------------------------------------------
1677 -- |---------------------------< update_validate >----------------------------|
1678 -- ----------------------------------------------------------------------------
1679 Procedure update_validate
1680 (p_rec in ben_pdp_shd.g_rec_type,
1681 p_effective_date in date,
1682 p_datetrack_mode in varchar2,
1683 p_validation_start_date in date,
1684 p_validation_end_date in date) is
1685 --
1686 l_proc varchar2(72) := g_package||'update_validate';
1687 --
1688 Begin
1689 hr_utility.set_location('Entering:'||l_proc, 5);
1690 --
1691 -- Call all supporting business operations
1692 --
1693 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1694 --
1695 chk_elig_cvrd_dpnt_id
1696 (p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1697 p_effective_date => p_effective_date,
1698 p_object_version_number => p_rec.object_version_number);
1699 --
1700 --
1701 chk_max_num_dpnt
1702 (p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1703 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1704 p_dpnt_person_id => p_rec.dpnt_person_id,
1705 p_cvg_strt_dt => p_rec.cvg_strt_dt,
1706 p_cvg_thru_dt => p_rec.cvg_thru_dt,
1707 p_effective_date => p_effective_date,
1708 p_business_group_id => p_rec.business_group_id,
1709 p_object_version_number => p_rec.object_version_number);
1710 --
1711 chk_dpnt_person_id
1712 (p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1713 p_dpnt_person_id => p_rec.dpnt_person_id,
1714 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1715 p_validation_start_date => p_validation_start_date,
1716 p_validation_end_date => p_validation_end_date,
1717 p_effective_date => p_effective_date,
1718 p_cvg_strt_dt => p_rec.cvg_strt_dt,
1719 p_business_group_id => p_rec.business_group_id,
1720 p_object_version_number => p_rec.object_version_number);
1721 --
1722 --
1723 -- chk_prtt_enrt_rslt_id
1724 -- (p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1725 -- p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1726 -- p_validation_start_date => p_validation_start_date,
1727 -- p_validation_end_date => p_validation_end_date,
1728 -- p_effective_date => p_effective_date,
1729 -- p_business_group_id => p_rec.business_group_id,
1730 -- p_object_version_number => p_rec.object_version_number);
1731 --
1732 chk_ovrdn_flag
1733 (p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1734 p_ovrdn_flag => p_rec.ovrdn_flag,
1735 p_effective_date => p_effective_date,
1736 p_object_version_number => p_rec.object_version_number);
1737
1738 chk_cvg_pndg_flag
1739 (p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id,
1740 p_cvg_pndg_flag => p_rec.cvg_pndg_flag,
1741 p_effective_date => p_effective_date,
1742 p_object_version_number => p_rec.object_version_number);
1743
1744 --
1745 -- chk_crt_ordr
1746 -- (p_dpnt_person_id => p_rec.dpnt_person_id,
1747 -- p_cvg_strt_dt => p_rec.cvg_strt_dt,
1748 -- p_cvg_thru_dt => p_rec.cvg_thru_dt,
1749 -- p_business_group_id => p_rec.business_group_id,
1750 -- p_effective_date => p_effective_date);
1751 --
1752 /*
1753 --
1754 Bug 3756863 : Moved to POST_UPDATE
1755 crt_ordr_warning
1756 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1757 p_effective_date => p_effective_date,
1758 p_business_group_id => p_rec.business_group_id);
1759 */
1760 --
1761 -- Call the datetrack update integrity operation
1762 --
1763 dt_update_validate
1764 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1765 p_datetrack_mode => p_datetrack_mode,
1766 p_validation_start_date => p_validation_start_date,
1767 p_validation_end_date => p_validation_end_date);
1768 --
1769 /* chk_dpnt_strt_end_dt
1770 (p_cvg_strt_dt => p_rec.cvg_strt_dt,
1771 p_cvg_thru_dt => p_rec.cvg_thru_dt,
1772 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id); */
1773 --
1774 hr_utility.set_location(' Leaving:'||l_proc, 10);
1775 End update_validate;
1776 --
1777 -- ----------------------------------------------------------------------------
1778 -- |---------------------------< delete_validate >----------------------------|
1779 -- ----------------------------------------------------------------------------
1780 --
1781 Procedure delete_validate
1782 (p_rec in ben_pdp_shd.g_rec_type,
1783 p_effective_date in date,
1784 p_datetrack_mode in varchar2,
1785 p_validation_start_date in date,
1786 p_validation_end_date in date) is
1787 --
1788 l_proc varchar2(72) := g_package||'delete_validate';
1789 --
1790 Begin
1791 hr_utility.set_location('Entering:'||l_proc, 5);
1792 --
1793 -- Call all supporting business operations
1794 --
1795 dt_delete_validate
1796 (p_datetrack_mode => p_datetrack_mode,
1797 p_validation_start_date => p_validation_start_date,
1798 p_validation_end_date => p_validation_end_date,
1799 p_elig_cvrd_dpnt_id => p_rec.elig_cvrd_dpnt_id);
1800 --
1801 /*
1802 --
1803 Bug 3756863 : Moved to POST_DELETE
1804 crt_ordr_warning
1805 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1806 p_effective_date => p_effective_date,
1807 p_business_group_id => p_rec.business_group_id);
1808 */
1809 --
1810 hr_utility.set_location(' Leaving:'||l_proc, 10);
1811 --
1812 End delete_validate;
1813 --
1814 --
1815 -- ---------------------------------------------------------------------------
1816 -- |---------------------< return_legislation_code >-------------------------|
1817 -- ---------------------------------------------------------------------------
1818 --
1819 function return_legislation_code
1820 (p_elig_cvrd_dpnt_id in number) return varchar2 is
1821 --
1822 -- Declare cursor
1823 --
1824 cursor csr_leg_code is
1825 select a.legislation_code
1826 from per_business_groups a,
1827 ben_elig_cvrd_dpnt_f b
1828 where b.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1829 and a.business_group_id = b.business_group_id;
1830 --
1831 -- Declare local variables
1832 --
1833 l_legislation_code varchar2(150);
1834 l_proc varchar2(72) := g_package||'return_legislation_code';
1835 --
1836 begin
1837 --
1838 hr_utility.set_location('Entering:'|| l_proc, 10);
1839 --
1840 -- Ensure that all the mandatory parameter are not null
1841 --
1842 hr_api.mandatory_arg_error(p_api_name => l_proc,
1843 p_argument => 'elig_cvrd_dpnt_id',
1844 p_argument_value => p_elig_cvrd_dpnt_id);
1845 --
1846 open csr_leg_code;
1847 --
1848 fetch csr_leg_code into l_legislation_code;
1849 --
1850 if csr_leg_code%notfound then
1851 --
1852 close csr_leg_code;
1853 --
1854 -- The primary key is invalid therefore we must error
1855 --
1856 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1857 fnd_message.raise_error;
1858 --
1859 end if;
1860 --
1861 close csr_leg_code;
1862 --
1863 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1864 --
1865 return l_legislation_code;
1866 --
1867 end return_legislation_code;
1868 --
1869 end ben_pdp_bus;