[Home] [Help]
PACKAGE BODY: APPS.GHR_BENEFITS_EIT
Source
1 PACKAGE BODY ghr_benefits_eit AS
2 /* $Header: ghbenenr.pkb 120.0.12010000.4 2008/10/17 11:02:33 vmididho ship $ */
3 PROCEDURE ghr_benefits_fehb
4 (errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY NUMBER,
6 p_person_id per_all_people_f.person_id%type,
7 p_effective_date VARCHAR2,
8 p_business_group_id per_all_people_f.business_group_id%type,
9 p_pl_code ben_pl_f.short_code%type,
10 p_opt_code ben_opt_f.short_code%type,
11 p_pre_tax varchar2,
12 p_assignment_id per_all_assignments_f.assignment_id%type,
13 p_temps_total_cost varchar2,
14 p_temp_appt varchar2 default 'N')
15 IS
16 -- Cursor to get Program
17 CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
18 c_effective_date ben_pgm_f.effective_start_date%type) is
19 SELECT pgm.pgm_id
20 FROM ben_pgm_f pgm
21 WHERE pgm.name = c_prog_name
22 AND pgm.business_group_id = c_business_group_id
23 AND c_effective_date between effective_start_date and effective_end_date;
24
25 CURSOR c_emp_in_ben(c_person_id ben_prtt_enrt_rslt_f.person_id%type, c_pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
26 c_effective_date ben_pgm_f.effective_start_date%type) is
27 SELECT 1
28 FROM ben_prtt_enrt_rslt_f
29 WHERE person_id = c_person_id
30 AND pgm_id = c_pgm_id
31 AND prtt_enrt_rslt_stat_cd IS NULL
32 AND c_effective_date between effective_start_date and effective_end_date;
33
34 --Cursor to get the Plan Type Id for the given Business_group_id
35 CURSOR c_get_pl_typ_id(c_plan_type ben_pl_typ_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
36 c_effective_date ben_pgm_f.effective_start_date%type) is
37 SELECT plt.pl_typ_id
38 FROM ben_pl_typ_f plt
39 WHERE plt.name = c_plan_type -- 'Savings Plan'
40 AND plt.business_group_id = c_business_group_id
41 AND c_effective_date between effective_start_date and effective_end_date;
42
43 CURSOR get_ptip_id(c_plan_type_id ben_ptip_f.pl_typ_id%type, c_pgm_id ben_ptip_f.pgm_id%type,
44 c_effective_date ben_pgm_f.effective_start_date%type) is
45 SELECT ptip_id
46 FROM ben_ptip_f
47 WHERE pl_typ_id = c_plan_type_id
48 AND pgm_id = c_pgm_id
49 AND c_effective_date between effective_start_date and effective_end_date;
50
51 CURSOR get_pl_id(c_health_plan ben_pl_f.short_code%type, c_business_group_id ben_pgm_f.business_group_id%type,
52 c_effective_date ben_pgm_f.effective_start_date%type) is
53 SELECT pln.pl_id pl_id
54 FROM ben_pl_f pln
55 WHERE pln.short_code = c_health_plan
56 AND pln.business_group_id = c_business_group_id
57 AND c_effective_date between effective_start_date and effective_end_date
58 AND pl_stat_cd = 'A';
59
60 --Cursor to get the opt_id for the EE's Enrollment Screen Entry Value
61 CURSOR get_opt_id(c_option_code ben_opt_f.short_code%type, c_business_group_id ben_pgm_f.business_group_id%type,
62 c_effective_date ben_pgm_f.effective_start_date%type) is
63 SELECT opt_id
64 FROM ben_opt_f opt
65 WHERE opt.short_code = c_option_code
66 AND opt.business_group_id = c_business_group_id
67 AND c_effective_date between effective_start_date and effective_end_date;
68
69 --Cursor to get the plan in Program Id for the given Pl_id
70 CURSOR get_plip_id(c_plan_id ben_plip_f.pl_id%type, c_pgm_id ben_plip_f.pgm_id%type, c_business_group_id ben_pgm_f.business_group_id%type,
71 c_effective_date ben_pgm_f.effective_start_date%type) is
72 SELECT plip.plip_id
73 FROM ben_plip_f plip
74 WHERE plip.pl_id = c_plan_id
75 AND plip.pgm_id = c_pgm_id
76 AND plip.business_group_id = c_business_group_id
77 AND c_effective_date between effective_start_date and effective_end_date;
78
79 -- Cursor to get the option in plan Id
80
81 CURSOR get_oipl_id(c_pl_id ben_pl_f.pl_id%type, c_opt_id ben_opt_f.opt_id%type, c_business_group_id ben_pgm_f.business_group_id%type,
82 c_effective_date ben_pgm_f.effective_start_date%type) is
83 SELECT oipl_id
84 FROM ben_oipl_f
85 WHERE pl_id = c_pl_id
86 AND opt_id = c_opt_id
87 AND business_group_id = c_business_group_id
88 AND c_effective_date between effective_start_date and effective_end_date;
89
90 Cursor get_ler_id(c_life_event ben_ler_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
91 c_effective_date ben_pgm_f.effective_start_date%type) is
92 select ler.ler_id
93 from ben_ler_f ler
94 where ler.business_group_id = c_business_group_id
95 and ler.name = c_life_event
96 and c_effective_date between effective_start_date and effective_end_date;
97
98 CURSOR get_elig_chc_id_opt(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,
99 c_pl_id ben_pl_f.pl_id%type, c_plip_id ben_plip_f.plip_id%type,
100 c_ptip_id ben_ptip_f.ptip_id%type, c_oipl_id ben_oipl_f.oipl_id%type,
101 c_ler_id ben_ler_f.ler_id%type, c_person_id per_all_people_f.person_id%type) IS
102 SELECT elig_per_elctbl_chc_id,
103 pil.per_in_ler_id,
104 prtt_enrt_rslt_id
105 FROM ben_elig_per_ELCTBL_chc chc ,
106 ben_per_in_ler pil
107 WHERE chc.pgm_id = c_pgm_id
108 AND chc.pl_typ_id = c_pl_typ_id
109 AND chc.pl_id = c_pl_id
110 AND chc.plip_id = c_plip_id
111 AND chc.ptip_id = c_ptip_id
112 AND chc.oipl_id = c_oipl_id
113 AND pil.per_in_ler_id = chc.per_in_ler_id
114 AND pil.ler_id = c_ler_id
115 AND pil.person_id = c_person_id
116 AND PER_IN_LER_STAT_CD NOT IN ('BCKDT','PROCD');
117
118 Cursor get_elig_chc_id(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,
119 c_pl_id ben_pl_f.pl_id%type, c_plip_id ben_plip_f.plip_id%type,
120 c_ptip_id ben_ptip_f.ptip_id%type,
121 c_ler_id ben_ler_f.ler_id%type, c_person_id per_all_people_f.person_id%type) is
122 select elig_per_elctbl_chc_id,
123 pil.per_in_ler_id,
124 prtt_enrt_rslt_id
125 from ben_elig_per_ELCTBL_chc chc ,
126 ben_per_in_ler pil
127 where chc.pgm_id = c_pgm_id
128 and chc.pl_typ_id = c_pl_typ_id
129 and chc.pl_id = c_pl_id
130 and chc.plip_id = c_plip_id
131 and chc.ptip_id = c_ptip_id
132 and pil.per_in_ler_id = chc.per_in_ler_id
133 and pil.ler_id = c_ler_id
134 and pil.person_id = c_person_id
135 AND PER_IN_LER_STAT_CD NOT IN ('BCKDT','PROCD');
136
137 Nothing_to_do EXCEPTION;
138 ben_enrt_exists EXCEPTION;
139
140 l_exists BOOLEAN;
141 l_person_id per_all_people_f.person_id%type ;
142 l_effective_date date;
143 l_warning boolean;
144 l_business_group_id per_all_people_f.business_group_id%type;
145 l_pl_code ben_pl_f.short_code%type ;
146 l_opt_code ben_opt_f.short_code%type;
147 l_pgm_id ben_pgm_f.pgm_id%type;
148 l_err_msg varchar2(2000);
149 l_pl_typ_id ben_pl_typ_f.pl_typ_id%type;
150 l_ptip_id ben_ptip_f.ptip_id%type;
151 l_pl_id ben_pl_f.pl_id%type;
152 l_opt_id ben_opt_f.opt_id%type;
153 l_plip_id ben_plip_f.plip_id%type;
154 l_oipl_id ben_oipl_f.oipl_id%type;
155 l_ler_id ben_ler_f.ler_id%type;
156 l_ptnl_ler_for_per_id NUMBER;
157 l_elig_per_elctbl_chc_id NUMBER;
158 l_prtt_enrt_rslt_id number;
159 l_per_in_ler_id NUMBER;
160 l_ovn NUMBER;
161 l_prog_count NUMBER;
162 l_plan_count NUMBER;
163 l_oipl_count NUMBER;
164 l_person_count NUMBER;
165 l_plan_nip_count NUMBER;
166 l_oipl_nip_count NUMBER;
167 l_benefit_action_id NUMBER;
168 l_errbuf varchar2(2000);
169 l_retcode NUMBER;
170 l_enrt_bnft_id NUMBER;
171 l_prtt_rt_val_id1 NUMBER;
172 l_prtt_rt_val_id2 NUMBER;
173 l_prtt_rt_val_id3 NUMBER;
174 l_prtt_rt_val_id4 NUMBER;
175 l_prtt_rt_val_id5 NUMBER;
176 l_prtt_rt_val_id6 NUMBER;
177 l_prtt_rt_val_id7 NUMBER;
178 l_prtt_rt_val_id8 NUMBER;
179 l_prtt_rt_val_id9 NUMBER;
180 l_prtt_rt_val_id10 NUMBER;
181 l_commit NUMBER;
182 l_suspend_flag varchar2(10);
183 l_esd date;
184 l_eed date;
185 l_prtt_enrt_interim_id number;
186 l_Boolean BOOLEAN;
187 l_ses_exist BOOLEAN;
188 l_life_event VARCHAR2(100);
189 l_cvrg_st_dt date;
190
191 cursor c_session(c_session_id fnd_sessions.session_id%type) IS
192 SELECT 1 FROM fnd_sessions
193 where session_id = c_session_id;
194
195 cursor c_get_ler_id is
196 select ler_id from ben_ptnl_ler_for_per
197 where business_group_id = p_business_group_id
198 and person_id = p_person_id
199 and ptnl_ler_for_per_stat_cd ='UNPROCD'
200 and ler_id not in (select ler_id from ben_ler_f where name
201 = 'Unrestricted' and business_group_id = p_business_group_id)
202 and LF_EVT_OCRD_DT = l_effective_date;
203
204 cursor c_get_cvg_st_dt
205 is
206 select enrt_cvg_strt_dt
207 from ben_prtt_enrt_rslt_f
208 where prtt_enrt_rslt_id = l_prtt_enrt_rslt_id;
209
210
211
212 cursor c_get_unproc_lf_evt
213 is
214 select le.name
215 from ben_ptnl_ler_for_per ptnl,
216 ben_ler_f le
217 where ptnl.business_group_id = p_business_group_id
218 and ptnl.person_id = p_person_id
219 and ptnl_ler_for_per_stat_cd ='UNPROCD'
220 and le.name <> 'Unrestricted'
221 and ptnl.ler_id = le.ler_id
222 and lf_evt_ocrd_dt < l_effective_date
223 order by ptnl_ler_for_per_id;
224
225 cursor get_cur_enr(p_asg_id in NUMBER,
226 p_business_group_id in NUMBER,
227 p_effective_date in DATE)
228 is
229 SELECT ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id, 'Enrollment', eef.effective_start_date) enrollment,
230 eef.element_entry_id ,
231 eef.object_version_number
232 FROM pay_element_entries_f eef,
233 pay_element_types_f elt
234 WHERE assignment_id = p_asg_id
235 AND elt.element_type_id = eef.element_type_id
236 AND eef.effective_start_date BETWEEN elt.effective_start_date AND
237 elt.effective_end_date
238 AND p_effective_date between eef.effective_start_date and eef.effective_end_date
239 AND upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
240 p_business_group_id,
241 p_effective_date))
242 IN ('HEALTH BENEFITS');
243
244 cursor c_get_cur_lf_evt(p_effective_date in date,
245 p_business_group_id in number,
246 p_person_id in number)
247 is
248 select per_in_ler_id
249 from ben_per_in_ler pil,ben_ler_f lf
250 where pil.person_id = p_person_id
251 and pil.business_group_id = p_business_group_id
252 and pil.PER_IN_LER_STAT_CD in ('STRTD')
253 and lf.ler_id = pil.ler_id
254 and name <> 'Unrestricted'
255 and lf_evt_ocrd_dt <> p_effective_date
256 and p_effective_date between lf.effective_start_date and lf.effective_end_date;
257
258 cursor c_chk_asg_exists(p_effective_date in date)
259 is
260 select 1
261 from per_all_assignments_f asg
262 where asg.assignment_id = p_assignment_id
263 and p_effective_date between asg.effective_start_date
264 and asg.effective_end_date;
265
266
267 l_object_version_number pay_element_entries_f.object_version_number%type;
268 l_effective_start_date date;
269 l_effective_end_date date;
270 l_exp_date date;
271 l_delete_warning boolean;
272 BEGIN
273
274 l_person_id := p_person_id;
275 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
276 l_business_group_id := p_business_group_id;
277 l_pl_code := p_pl_code;
278 l_opt_code := p_opt_code;
279
280 dt_fndate.change_ses_date (p_ses_date => TRUNC (SYSDATE),
281 p_commit => l_commit
282 );
283
284 -- Get Program ID
285 FOR pgm_rec in c_get_pgm_id('Federal Employees Health Benefits', l_business_group_id, l_effective_date) LOOP -- Eff date and BG ID
286 l_pgm_id := pgm_rec.pgm_id;
287 EXIT;
288 END LOOP;
289
290 hr_utility.set_location('Program ID ' || l_pgm_id,1234);
291 If l_pgm_id is null Then
292 -- Raise Error message
293 hr_utility.set_message_token('PROGRAM','Federal Employee Health Benefits program');
294 hr_utility.set_message(8301,'GHR_38966_BEN_PRG_INVALID');
295 hr_utility.raise_error;
296 End If;
297
298 -- Check if Person is already enrolled
299 /* l_exists := FALSE;
300 for emp_ben_rec in c_emp_in_ben(l_person_id, l_pgm_id, l_effective_date) LOOP -- Enter person id here...
301 l_exists := TRUE;
302 exit;
303 end loop;
304
305 If l_exists then
306 Raise ben_enrt_exists;
307 End If; */
308
309 For lf_evt_rec in c_get_cur_lf_evt(p_effective_date => l_effective_date,
310 p_business_group_id => l_business_group_id,
311 p_person_id => l_person_id)
312 Loop
313 ben_close_enrollment.close_single_enrollment
314 (p_per_in_ler_id => lf_evt_rec.per_in_ler_id
315 ,p_effective_date => l_effective_date-1
316 ,p_business_group_id => l_business_group_id
317 ,p_close_cd => 'FORCE'
318 ,p_validate => FALSE
319 ,p_close_uneai_flag => NULL
320 ,p_uneai_effective_date => NULL);
321
322
323 End Loop;
324
325
326 hr_utility.set_location('person_id is ' || p_person_id,1235);
327 hr_utility.set_location('l_effective_date is ' || p_effective_date,1236);
328 hr_utility.set_location('business_group_id is ' || p_business_group_id,1237);
329
330 --Check if person is having more than one life event
331 -- in unprocessed status. If any other life event
332 -- which is unprocessed need to be processed or voided
333 -- for processing the current life event
334 -- Unprocessed Life Event Exists
335 For unproc_lf_evt in c_get_unproc_lf_evt
336 Loop
337 hr_utility.set_message(8301,'GHR_38519_UNPRC_LF_EVT');
338 hr_utility.raise_error;
339 End Loop;
340
341 For ler_rec in c_get_ler_id loop
342 l_ler_id := ler_rec.ler_id;
343 End Loop;
344
345 /* If l_ler_id is null then
346 hr_utility.set_message(8301,'GHR_38520_NO_LFEVT_EXISTS');
347 hr_utility.raise_error;
348 End If; */
349 IF l_ler_id is not null THEN
350 hr_utility.set_location('Life event ID ' || l_ler_id,1234);
351
352 -- Calling BENMNGLE
353
354 ben_on_line_lf_evt.p_evt_lf_evts_from_benauthe(
355 p_person_id => l_person_id
356 ,p_effective_date => l_effective_date
357 ,p_business_group_id => l_business_group_id
358 ,p_pgm_id => l_pgm_id
359 -- ,p_pl_id => l_pl_id -- No need. Commented by Venkat
360 ,p_mode => 'L'
361 ,p_popl_enrt_typ_cycl_id => null
362 ,p_lf_evt_ocrd_dt => l_effective_date
363 ,p_prog_count => l_prog_count
364 ,p_plan_count => l_plan_count
365 ,p_oipl_count => l_oipl_count
366 ,p_person_count => l_person_count
367 ,p_plan_nip_count => l_plan_nip_count
368 ,p_oipl_nip_count => l_oipl_nip_count
369 ,p_ler_id => l_ler_id
370 ,p_errbuf => l_errbuf
371 ,p_retcode => l_retcode);
372 --
373
374 ben_on_line_lf_evt.p_proc_lf_evts_from_benauthe(
375 p_person_id => l_person_id
376 ,p_effective_date => l_effective_date
377 ,p_business_group_id => l_business_group_id
378 ,p_mode => 'L'
379 ,p_ler_id => l_ler_id
380 ,p_person_count => l_person_count
381 ,p_benefit_action_id => l_benefit_action_id
382 ,p_errbuf => l_errbuf
383 ,p_retcode => l_retcode);
384
385 IF p_opt_code is not null and p_pl_code is not null THEN
386 -- Get Plan type
387 For plt_rec in c_get_pl_typ_id('Health Benefits', l_business_group_id, l_effective_date)
388 Loop
389 l_pl_typ_id := plt_rec.pl_typ_id;
390 exit;
391 End Loop;
392 hr_utility.set_location('Plan type ID ' || l_pl_typ_id,1234);
393
394 -- Get Plan type in Program ID
395 For ptip_rec in get_ptip_id(l_pl_typ_id,l_pgm_id,l_effective_date) loop
396 l_ptip_id := ptip_rec.ptip_id;
397 End Loop;
398 hr_utility.set_location('Plan type in Prog ID ' || l_ptip_id,1234);
399
400 -- Get Plan ID
401 For pl_rec in get_pl_id(l_pl_code, l_business_group_id, l_effective_date) loop
402 l_pl_id := pl_rec.pl_id;
403 End Loop;
404
405 hr_utility.set_location('Plan ID ' || l_pl_id,1234);
406
407 IF l_pl_id IS NULL THEN
408 hr_utility.set_message_token('PLAN','Federal Employee Health Benefits plan ' || l_pl_code);
409 hr_utility.set_message(8301,'GHR_38967_BEN_PLAN_INVALID');
410 hr_utility.raise_error;
411 END IF;
412
413
414 -- Get Options ID
415 IF p_opt_code IS NOT NULL THEN
416
417 If NVL(p_opt_code,hr_api.g_varchar2) NOT IN ('W','X','Y','Z') then
418 IF p_pre_tax = 'Y' THEN
419 l_opt_code := l_opt_code || 'A';
420 ELSE
421 l_opt_code := l_opt_code || 'P';
422 END IF;
423 END IF;
424
425 For opt_rec in get_opt_id(l_opt_code, l_business_group_id, l_effective_date)
426 Loop
427 l_opt_id := opt_rec.opt_id;
428 End Loop;
429
430 hr_utility.set_location('Option ID ' || l_opt_id,1234);
431 If l_opt_id IS NULL then
432 hr_utility.set_location ('NO option found ',1234);
433 hr_utility.set_message_token('OPTION','FEHB Option ' || l_opt_code);
434 hr_utility.set_message(8301,'GHR_38967_BEN_PLAN_INVALID');
435 hr_utility.raise_error;
436 END IF;
437 END IF;
438
439 -- Get Plan in Program ID
440 FOR plip_id_rec in get_plip_id(l_pl_id, l_pgm_id, l_business_group_id, l_effective_date) loop
441 l_plip_id := plip_id_rec.plip_id;
442 END LOOP;
443
444 hr_utility.set_location('Plan in prog ID ' || l_plip_id,1234);
445 -- get oipl_id
446 IF l_opt_id IS NOT NULL THEN
447 FOR oipl_id_rec in get_oipl_id(l_pl_id,l_opt_id ,l_business_group_id ,l_effective_date ) loop
448 l_oipl_id := oipl_id_rec.oipl_id;
449 END LOOP;
450 IF l_oipl_id IS NULL THEN
451 hr_utility.set_message_token('PROGRAM ','FEHB');
452 hr_utility.set_message_token('PLAN_OPTION', l_pl_code || '/' || l_opt_code);
453 hr_utility.set_message(8301,'GHR_38969_BEN_PLAN_OPT_INVALID');
454 hr_utility.raise_error;
455 END IF;
456 ELSE
457 l_oipl_id := null;
458 END IF;
459
460 hr_utility.set_location('Option in plan ID ' || l_oipl_id,1234);
461
462 -- Create Potential Life event
463 -- No need for this now... Need
464 /*
465
466 IF p_temp_appt = 'Y' THEN
467 l_life_event := 'Continued Coverage';
468 ELSE
469 l_life_event := 'Initial Opportunity to Enroll';
470 END IF;
471
472 for ler_rec in get_ler_id(l_life_event,l_business_group_id, l_effective_date) loop
473 l_ler_id := ler_rec.ler_id;
474 end loop;
475 */
476
477 --hr_utility.trace_off;
478
479 hr_utility.set_location('l_ler_id ' || l_ler_id,1235);
480 hr_utility.set_location('l_pgm_id ' || l_pgm_id,1235);
481 hr_utility.set_location('l_pl_typ_id ' || l_pl_typ_id,1235);
482 hr_utility.set_location('l_pl_id ' || l_pl_id,1235);
483 hr_utility.set_location('l_plip_id ' || l_plip_id,1235);
484 hr_utility.set_location('l_ptip_id ' || l_ptip_id,1235);
485 hr_utility.set_location('l_oipl_id ' || l_oipl_id,1235);
486 hr_utility.set_location('l_person_id ' || l_person_id,1235);
487 If l_oipl_id is not null Then
488 open get_elig_chc_id_opt(l_pgm_id , l_pl_typ_id , l_pl_id , l_plip_id ,
489 l_ptip_id , l_oipl_id , l_ler_id , l_person_id) ;
490 fetch get_elig_chc_id_opt into l_elig_per_elctbl_chc_id,l_per_in_ler_id,l_prtt_enrt_rslt_id;
491 If get_elig_chc_id_opt%NOTFOUND then
492 hr_utility.set_message_token('PLAN_OPT', l_pl_code || '/' || l_opt_code);
493 hr_utility.set_message(8301,'GHR_38970_BEN_PLAN_INELIG');
494 hr_utility.raise_error;
495 End If;
496 Else
497 open get_elig_chc_id(l_pgm_id , l_pl_typ_id , l_pl_id , l_plip_id ,
498 l_ptip_id , l_ler_id , l_person_id) ;
499 fetch get_elig_chc_id into l_elig_per_elctbl_chc_id,l_per_in_ler_id,l_prtt_enrt_rslt_id;
500 If get_elig_chc_id%NOTFOUND then
501 hr_utility.set_message_token('PLAN_OPT', l_pl_code);
502 hr_utility.set_message(8301,'GHR_38970_BEN_PLAN_INELIG');
503 hr_utility.raise_error;
504 End If;
505 End If;
506
507
508 -- Enrolling a person
509 ben_election_information.election_information
510 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
511 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
512 ,p_effective_date => l_effective_date
513 ,p_enrt_mthd_cd => 'E'
514 ,p_enrt_bnft_id => l_enrt_bnft_id
515 ,p_prtt_rt_val_id1 => l_prtt_rt_val_id1
516 ,p_prtt_rt_val_id2 => l_prtt_rt_val_id2
517 ,p_prtt_rt_val_id3 => l_prtt_rt_val_id3
518 ,p_prtt_rt_val_id4 => l_prtt_rt_val_id4
519 ,p_prtt_rt_val_id5 => l_prtt_rt_val_id5
520 ,p_prtt_rt_val_id6 => l_prtt_rt_val_id6
521 ,p_prtt_rt_val_id7 => l_prtt_rt_val_id7
522 ,p_prtt_rt_val_id8 => l_prtt_rt_val_id8
523 ,p_prtt_rt_val_id9 => l_prtt_rt_val_id9
524 ,p_prtt_rt_val_id10 => l_prtt_rt_val_id10
525 ,p_enrt_cvg_strt_dt => l_effective_date
526 -- ,p_enrt_cvg_thru_dt => NULL
527 ,p_datetrack_mode => 'INSERT'
528 ,p_suspend_flag => l_suspend_flag
529 ,p_effective_start_date => l_esd
530 ,p_effective_end_date => l_eed
531 ,p_object_version_number => l_ovn
532 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
533 ,p_business_group_id => l_business_group_id
534 ,p_dpnt_actn_warning => l_Boolean
535 ,p_bnf_actn_warning => l_Boolean
536 ,p_ctfn_actn_warning => l_Boolean
537 );
538
539 ben_proc_common_enrt_rslt.process_post_enrt_calls_w
540 (p_validate => 'N'
541 ,p_person_id => l_person_id
542 ,p_per_in_ler_id => l_per_in_ler_id
543 ,p_pgm_id => l_pgm_id
544 ,p_pl_id => l_pl_id
545 ,p_flx_cr_flag => 'N'
546 ,p_enrt_mthd_cd => 'E'
547 ,p_proc_cd => null
548 -- changed to N as it should not be closed immediately after enrollment
549 ,p_cls_enrt_flag => 'N'
550 ,p_business_group_id => l_business_group_id
551 ,p_effective_date => l_effective_date);
552
553 hr_utility.set_location('p_assignment_id'||p_assignment_id,1000);
554 hr_utility.set_location('l_business_group_id'||l_business_group_id,1001);
555 hr_utility.set_location('l_effective_date'||l_effective_date,1002);
556
557 for cur_cvrg_st_dt in c_get_cvg_st_dt
558 loop
559 l_cvrg_st_dt := cur_cvrg_st_dt.enrt_cvg_strt_dt;
560 end loop;
561
562 for chk_asg_rec in c_chk_asg_exists(p_effective_date => l_cvrg_st_dt)
563 loop
564 IF p_pre_tax = 'N' THEN
565 ghr_element_api.process_sf52_element
566 (p_assignment_id => p_assignment_id
567 ,p_element_name => 'Health Benefits Pre tax'
568 ,p_input_value_name3 => 'Temps Total Cost'
569 ,p_value3 => p_temps_total_cost
570 ,p_effective_date => l_cvrg_st_dt
571 ,p_process_warning => l_warning
572 );
573 ELSE
574 ghr_element_api.process_sf52_element
575 (p_assignment_id => p_assignment_id
576 ,p_element_name => 'Health Benefits'
577 ,p_input_value_name3 => 'Temps Total Cost'
578 ,p_value3 => p_temps_total_cost
579 ,p_effective_date => l_cvrg_st_dt
580 ,p_process_warning => l_warning
581 );
582 END IF;
583 end loop;
584 END IF;
585 END IF;
586
587 EXCEPTION
588 WHEN ben_enrt_exists THEN
589 errbuf := 'Enrollment already exists';
590 retcode := 2;
591 WHEN Nothing_to_do THEN
592 errbuf := l_err_msg;
593 hr_utility.set_location('Error tsp: ' || l_err_msg,1234);
594 retcode := 2;
595 WHEN OTHERS THEN
596 errbuf := 'Err' || sqlcode || ' : ' || sqlerrm;
597 hr_utility.set_location('Error tsp: ' || sqlerrm,1234);
598 retcode := 2;
599 hr_utility.raise_error;
600 END ghr_benefits_fehb;
601
602
603 PROCEDURE ghr_benefits_tsp
604 (errbuf OUT NOCOPY VARCHAR2,
605 retcode OUT NOCOPY NUMBER,
606 p_person_id per_all_people_f.person_id%type,
607 p_effective_date VARCHAR2,
608 p_business_group_id per_all_people_f.business_group_id%type,
609 p_tsp_status varchar2,
610 p_opt_name ben_opt_f.name%type,
611 p_opt_val number
612 )
613
614 IS
615 -- Cursor to get Program
616 CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
617 c_effective_date ben_pgm_f.effective_start_date%type) is
618 SELECT pgm.pgm_id
619 FROM ben_pgm_f pgm
620 WHERE pgm.name = c_prog_name
621 AND pgm.business_group_id = c_business_group_id
622 AND c_effective_date between effective_start_date and effective_end_date;
623
624 CURSOR c_emp_in_ben(c_person_id ben_prtt_enrt_rslt_f.person_id%type, c_pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
625 c_effective_date ben_pgm_f.effective_start_date%type) is
626 SELECT 1
627 FROM ben_prtt_enrt_rslt_f
628 WHERE person_id = c_person_id
629 AND pgm_id = c_pgm_id
630 AND c_effective_date between effective_start_date and effective_end_date;
631
632 --Cursor to get the Plan Type Id for the given Business_group_id
633 CURSOR c_get_pl_typ_id(c_plan_type ben_pl_typ_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
634 c_effective_date ben_pgm_f.effective_start_date%type) is
635 SELECT plt.pl_typ_id
636 FROM ben_pl_typ_f plt
637 WHERE plt.name = c_plan_type -- 'Savings Plan'
638 AND plt.business_group_id = c_business_group_id
639 AND c_effective_date between effective_start_date and effective_end_date;
640
641 CURSOR get_ptip_id(c_plan_type_id ben_ptip_f.pl_typ_id%type, c_pgm_id ben_ptip_f.pgm_id%type,
642 c_effective_date ben_pgm_f.effective_start_date%type) is
643 SELECT ptip_id
644 FROM ben_ptip_f
645 WHERE pl_typ_id = c_plan_type_id
646 AND pgm_id = c_pgm_id
647 AND c_effective_date between effective_start_date and effective_end_date;
648
649 CURSOR get_pl_id(c_pl_name ben_pl_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
650 c_effective_date ben_pgm_f.effective_start_date%type) is
651 SELECT pln.pl_id pl_id
652 FROM ben_pl_f pln
653 WHERE pln.name = c_pl_name
654 AND pln.business_group_id = c_business_group_id
655 AND c_effective_date between effective_start_date and effective_end_date;
656
657 --Cursor to get the opt_id for the EE's Enrollment Screen Entry Value
658 CURSOR get_opt_id(c_opt_name ben_opt_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
659 c_effective_date ben_pgm_f.effective_start_date%type) is
660 SELECT opt_id
661 FROM ben_opt_f opt
662 WHERE opt.name = c_opt_name
663 AND opt.business_group_id = c_business_group_id
664 AND c_effective_date between effective_start_date and effective_end_date;
665
666 --Cursor to get the plan in Program Id for the given Pl_id
667 CURSOR get_plip_id(c_plan_id ben_plip_f.pl_id%type, c_pgm_id ben_plip_f.pgm_id%type, c_business_group_id ben_pgm_f.business_group_id%type,
668 c_effective_date ben_pgm_f.effective_start_date%type) is
669 SELECT plip.plip_id
670 FROM ben_plip_f plip
671 WHERE plip.pl_id = c_plan_id
672 AND plip.pgm_id = c_pgm_id
673 AND plip.business_group_id = c_business_group_id
674 AND c_effective_date between effective_start_date and effective_end_date;
675
676 -- Cursor to get the option in plan Id
677
678 CURSOR get_oipl_id(c_pl_id ben_pl_f.pl_id%type, c_opt_id ben_opt_f.opt_id%type, c_business_group_id ben_pgm_f.business_group_id%type,
679 c_effective_date ben_pgm_f.effective_start_date%type) is
680 SELECT oipl_id
681 FROM ben_oipl_f
682 WHERE pl_id = c_pl_id
683 AND opt_id = c_opt_id
684 AND business_group_id = c_business_group_id
685 AND c_effective_date between effective_start_date and effective_end_date;
686
687 CURSOR get_elig_chc_id_opt(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,
688 c_pl_id ben_pl_f.pl_id%type, c_plip_id ben_plip_f.plip_id%type,
689 c_ptip_id ben_ptip_f.ptip_id%type, c_oipl_id ben_oipl_f.oipl_id%type,
690 c_person_id per_all_people_f.person_id%type) IS
691 SELECT elig_per_elctbl_chc_id,
692 pil.per_in_ler_id
693 FROM ben_elig_per_ELCTBL_chc chc ,
694 ben_per_in_ler pil
695 WHERE chc.pgm_id = c_pgm_id
696 AND chc.pl_typ_id = c_pl_typ_id
697 AND chc.pl_id = c_pl_id
698 AND chc.plip_id = c_plip_id
699 AND chc.ptip_id = c_ptip_id
700 AND chc.oipl_id = c_oipl_id
701 AND pil.per_in_ler_id = chc.per_in_ler_id
702 --AND pil.ler_id = c_ler_id
703 AND pil.person_id = c_person_id;
704
705 CURSOR c_get_enrt_rt_id(c_elig_per_elctbl_chc_id ben_enrt_rt.elig_per_elctbl_chc_id%type) is
706 SELECT enrt_rt_id
707 FROM ben_enrt_rt
708 WHERE elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id;
709
710 Nothing_to_do EXCEPTION;
711 ben_enrt_exists EXCEPTION;
712
713 l_exists BOOLEAN;
714 l_person_id per_all_people_f.person_id%type ;
715 l_effective_date date;
716 l_business_group_id per_all_people_f.business_group_id%type;
717 l_pl_code ben_pl_f.short_code%type ;
718 l_opt_name ben_opt_f.name%type;
719 l_pgm_id ben_pgm_f.pgm_id%type;
720 l_err_msg varchar2(2000);
721 l_pl_typ_id ben_pl_typ_f.pl_typ_id%type;
722 l_ptip_id ben_ptip_f.ptip_id%type;
723 l_pl_id ben_pl_f.pl_id%type;
724 l_opt_id ben_opt_f.opt_id%type;
725 l_plip_id ben_plip_f.plip_id%type;
726 l_oipl_id ben_oipl_f.oipl_id%type;
727 l_ler_id ben_ler_f.ler_id%type;
728 l_ptnl_ler_for_per_id NUMBER;
729 l_elig_per_elctbl_chc_id NUMBER;
730 l_prtt_enrt_rslt_id number;
731 l_per_in_ler_id NUMBER;
732 l_ovn NUMBER;
733 l_prog_count NUMBER;
734 l_plan_count NUMBER;
735 l_oipl_count NUMBER;
736 l_person_count NUMBER;
737 l_plan_nip_count NUMBER;
738 l_oipl_nip_count NUMBER;
739 l_benefit_action_id NUMBER;
740 l_errbuf varchar2(2000);
741 l_retcode NUMBER;
742 l_enrt_bnft_id NUMBER;
743 l_prtt_rt_val_id1 NUMBER;
744 l_prtt_rt_val_id2 NUMBER;
745 l_prtt_rt_val_id3 NUMBER;
746 l_prtt_rt_val_id4 NUMBER;
747 l_prtt_rt_val_id5 NUMBER;
748 l_prtt_rt_val_id6 NUMBER;
749 l_prtt_rt_val_id7 NUMBER;
750 l_prtt_rt_val_id8 NUMBER;
751 l_prtt_rt_val_id9 NUMBER;
752 l_prtt_rt_val_id10 NUMBER;
753 l_commit NUMBER;
754 l_suspend_flag varchar2(10);
755 l_esd date;
756 l_eed date;
757 l_prtt_enrt_interim_id number;
758 l_Boolean BOOLEAN;
759 l_ses_exist BOOLEAN;
760 l_enrt_rt_id ben_enrt_rt.enrt_rt_id%type;
761
762 cursor c_session(c_session_id fnd_sessions.session_id%type) IS
763 SELECT 1 FROM fnd_sessions
764 where session_id = c_session_id;
765
766 BEGIN
767
768 l_person_id := p_person_id;
769 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
770 l_business_group_id := p_business_group_id;
771 l_opt_name := p_opt_name;
772
773 -- hr_utility.trace_on(null,'sundar');
774
775 dt_fndate.change_ses_date (p_ses_date => TRUNC (SYSDATE),
776 p_commit => l_commit
777 );
778
779 if l_effective_date < to_date('07/01/2005','MM/DD/YYYY') then
780 l_effective_date := to_date('07/01/2005','MM/DD/YYYY');
781 End If;
782
783 -- Get Program ID
784 FOR pgm_rec in c_get_pgm_id('Federal Thrift Savings Plan (TSP)', l_business_group_id, l_effective_date) LOOP -- Eff date and BG ID
785 l_pgm_id := pgm_rec.pgm_id;
786 EXIT;
787 END LOOP;
788
789 hr_utility.set_location('Program ID ' || l_pgm_id,1234);
790
791 If l_pgm_id is null Then
792 -- Raise Error message
793 hr_utility.set_message_token('PROGRAM','Federal Thrift Savings Plan (TSP) program');
794 hr_utility.set_message(8301,'GHR_38966_BEN_PRG_INVALID');
795 hr_utility.raise_error;
796 End If;
797
798 -- Check if Person is already enrolled
799 /* l_exists := FALSE;
800 FOR emp_ben_rec in c_emp_in_ben(l_person_id, l_pgm_id, l_effective_date) LOOP
801 l_exists := TRUE;
802 exit;
803 END LOOP;
804
805 IF l_exists THEN
806 Raise ben_enrt_exists;
807 END IF; */
808
809 -- Get Plan type
810 FOR plt_rec in c_get_pl_typ_id('Savings Plan', l_business_group_id, l_effective_date) loop
811 l_pl_typ_id := plt_rec.pl_typ_id;
812 EXIT;
813 END LOOP;
814 hr_utility.set_location('Plan type ID ' || l_pl_typ_id,1234);
815
816 -- Get Plan type in Program ID
817 FOR ptip_rec in get_ptip_id(l_pl_typ_id,l_pgm_id,l_effective_date) loop
818 l_ptip_id := ptip_rec.ptip_id;
819 END LOOP;
820 hr_utility.set_location('Plan type in Prog ID ' || l_ptip_id,1234);
821
822 -- Get Plan ID
823 FOR pl_rec in get_pl_id('TSP', l_business_group_id, l_effective_date) loop
824 l_pl_id := pl_rec.pl_id;
825 END LOOP;
826 hr_utility.set_location('Plan ID ' || l_pl_id,1234);
827
828 IF l_pl_id IS NULL THEN
829 hr_utility.set_message_token('PLAN','Federal Thrift Savings Plan (TSP)');
830 hr_utility.set_message(8301,'GHR_38967_BEN_PLAN_INVALID');
831 hr_utility.raise_error;
832 END IF;
833
834 ben_on_line_lf_evt.p_manage_life_events(
835 p_person_id => l_person_id
836 ,p_effective_date => l_effective_date
837 ,p_business_group_id => l_business_group_id
838 ,p_pgm_id => l_pgm_id
839 ,p_pl_id => l_pl_id
840 ,p_mode => 'U' -- Unrestricted
841 ,p_prog_count => l_prog_count
842 ,p_plan_count => l_plan_count
843 ,p_oipl_count => l_oipl_count
844 ,p_person_count => l_person_count
845 ,p_plan_nip_count => l_plan_nip_count
846 ,p_oipl_nip_count => l_oipl_nip_count
847 ,p_ler_id => l_ler_id
848 ,p_errbuf => l_errbuf
849 ,p_retcode => l_retcode);
850
851 If p_tsp_status is not null then
852 IF p_tsp_status IN ('S','T') THEN
853 l_opt_name := 'Terminate Contributions';
854 END IF;
855
856 -- Get Options ID
857 FOR opt_rec in get_opt_id(l_opt_name, l_business_group_id, l_effective_date) loop
858 l_opt_id := opt_rec.opt_id;
859 END LOOP;
860
861 hr_utility.set_location('Option ID ' || l_opt_id,1234);
862 IF l_opt_id IS NULL THEN
863 hr_utility.set_location ('NO option found ',1234);
864 hr_utility.set_message_token('OPTION','TSP Option ' || l_opt_name);
865 hr_utility.set_message(8301,'GHR_38967_BEN_PLAN_INVALID');
866 hr_utility.raise_error;
867 End If;
868
869 -- Get Plan in Program ID
870 FOR plip_id_rec in get_plip_id(l_pl_id, l_pgm_id, l_business_group_id, l_effective_date) loop
871 l_plip_id := plip_id_rec.plip_id;
872 END LOOP;
873
874 hr_utility.set_location('Plan in prog ID ' || l_plip_id,1234);
875 -- get oipl_id
876 FOR oipl_id_rec in get_oipl_id(l_pl_id,l_opt_id ,l_business_group_id ,l_effective_date ) loop
877 l_oipl_id := oipl_id_rec.oipl_id;
878 END LOOP;
879 IF l_oipl_id IS NULL THEN
880 hr_utility.set_message_token('PROGRAM ','TSP');
881 hr_utility.set_message_token('PLAN_OPTION', 'TSP' || '/' || l_opt_name);
882 hr_utility.set_message(8301,'GHR_38969_BEN_PLAN_OPT_INVALID');
883 hr_utility.raise_error;
884 END IF;
885
886 hr_utility.set_location('Option in plan ID ' || l_oipl_id,1234);
887
888 /*(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,
889 c_pl_id ben_pl_f.pl_id%type, c_plip_id ben_plip_f.plip_id%type,
890 c_ptip_id ben_ptip_f.ptip_id%type, c_oipl_id ben_oipl_f.oipl_id%type,
891 c_person_id per_all_people_f.person_id%type) */
892 hr_utility.set_location('p_manage_life_events done' ,1234);
893 for get_elig_chc_id in get_elig_chc_id_opt(l_pgm_id , l_pl_typ_id , l_pl_id , l_plip_id ,
894 l_ptip_id , l_oipl_id , l_person_id) loop
895 l_elig_per_elctbl_chc_id := get_elig_chc_id.elig_per_elctbl_chc_id;
896 l_per_in_ler_id := get_elig_chc_id.per_in_ler_id;
897 exit;
898 End Loop;
899 hr_utility.set_location('l_elig_per_elctbl_chc_id ' || l_elig_per_elctbl_chc_id ,1234);
900
901 If l_elig_per_elctbl_chc_id is null Then
902 hr_utility.set_message(8301,'GHR_38971_BEN_TSP_INELIG');
903 hr_utility.raise_error;
904 End If;
905
906 for get_enrt_rt_id in c_get_enrt_rt_id(l_elig_per_elctbl_chc_id) loop
907 l_enrt_rt_id := get_enrt_rt_id.enrt_rt_id;
908 exit;
909 End Loop;
910
911 -- Enrolling a person
912 ben_election_information.election_information
913 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
914 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
915 ,p_effective_date => l_effective_date
916 ,p_enrt_mthd_cd => 'E'
917 ,p_enrt_bnft_id => l_enrt_bnft_id
918 ,p_enrt_rt_id1 => l_enrt_rt_id
919 ,p_rt_val1 => p_opt_val
920 ,p_rt_strt_dt1 => l_effective_date
921 ,p_rt_end_dt1 => hr_api.g_eot
922 ,p_prtt_rt_val_id1 => l_prtt_rt_val_id1
923 ,p_prtt_rt_val_id2 => l_prtt_rt_val_id2
924 ,p_prtt_rt_val_id3 => l_prtt_rt_val_id3
925 ,p_prtt_rt_val_id4 => l_prtt_rt_val_id4
926 ,p_prtt_rt_val_id5 => l_prtt_rt_val_id5
927 ,p_prtt_rt_val_id6 => l_prtt_rt_val_id6
928 ,p_prtt_rt_val_id7 => l_prtt_rt_val_id7
929 ,p_prtt_rt_val_id8 => l_prtt_rt_val_id8
930 ,p_prtt_rt_val_id9 => l_prtt_rt_val_id9
931 ,p_prtt_rt_val_id10 => l_prtt_rt_val_id10
932 ,p_enrt_cvg_strt_dt => l_effective_date
933 -- ,p_enrt_cvg_thru_dt => hr_api.g_eot
934 ,p_datetrack_mode => 'INSERT'
935 ,p_suspend_flag => l_suspend_flag
936 ,p_effective_start_date => l_esd
937 ,p_effective_end_date => l_eed
938 ,p_object_version_number => l_ovn
939 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
940 ,p_business_group_id => l_business_group_id
941 ,p_dpnt_actn_warning => l_Boolean
942 ,p_bnf_actn_warning => l_Boolean
943 ,p_ctfn_actn_warning => l_Boolean
944 );
945
946 END IF;
947
948 -- hr_utility.trace_off;
949 EXCEPTION
950 WHEN ben_enrt_exists THEN
951 null;
952 WHEN Nothing_to_do THEN
953 hr_utility.set_location('Error tsp: ' || l_err_msg,1234);
954 rollback;
955 WHEN OTHERS THEN
956 errbuf := 'Err' || sqlcode || ' : ' || sqlerrm;
957 hr_utility.set_location('Error tsp: ' || sqlerrm,1234);
958 retcode := 2;
959 hr_utility.raise_error;
960 END ghr_benefits_tsp;
961
962
963 END ghr_benefits_eit;