[Home] [Help]
PACKAGE BODY: APPS.GHR_GENERAL
Source
1 PACKAGE BODY ghr_general AS
2 /* $Header: ghgenral.pkb 120.5 2010/04/09 05:27:46 vmididho ship $ */
3 --
4 --
5 FUNCTION return_number(p_value varchar2) RETURN number is
6 BEGIN
7 return(to_number(p_value)) ;
8 EXCEPTION
9 when others then
10 return(0);
11 end return_number;
12 FUNCTION return_rif_date(p_value varchar2) RETURN date is
13 BEGIN
14 return(fnd_date.canonical_to_date(p_value)) ;
15 EXCEPTION
16 when others then
17 return(to_date('1901/01/01','yyyy/mm/dd'));
18 end return_rif_date;
19
20 --
21 -- ---------------------------------------------------------------------------
22 -- |---------------------------< get_remark_code >----------------------------|
23 -- ---------------------------------------------------------------------------
24 -- {Start of Comments}
25 --
26 -- Description:
27 -- Retrieve remark code from table ghr_remarks.
28 --
29 -- Prerequisites:
30 -- p_remark_id must be provided.
31 --
32 -- In Parameters:
33 -- p_remark_id
34 --
35 -- Post Success:
36 -- Processing continues.
37 --
38 -- Post Failure:
39 -- An application error will be raised and processing is terminated.
40 --
41 -- Developer Implementation Notes:
42 -- None.
43 --
44 -- Access Status:
45 -- All.
46 --
47 -- {End of Comments}
48 -- ---------------------------------------------------------------------------
49 --
50 FUNCTION get_remark_code(p_remark_id IN ghr_remarks.code%type)
51 Return VARCHAR2 AS
52
53 rem_code ghr_remarks.code%type;
54 cursor c1 (p_remark_id Number) is
55 select code from ghr_remarks where remark_id = p_remark_id;
56 BEGIN
57 open c1(p_remark_id);
58 fetch c1 into rem_code;
59 close c1;
60 Return(rem_code);
61 END get_remark_code;
62
63 Procedure get_poi_to_send_ntfn(
64 Itemtype in varchar2,
65 Itemkey in varchar2,
66 actid in number,
67 funcmode in varchar2,
68 result out NOCOPY varchar2
69 )
70 is
71
72 l_person_id number;
73 l_effective_date date := sysdate;
74 l_groupbox_name ghr_groupboxes.name%type;
75 l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
76 l_gbx_user_id ghr_pois.person_id%type;
77 l_receiver_name ghr_groupboxes.name%type;
78
79
80 cursor get_position_id is
81 select asg.position_id
82 from per_all_assignments_f asg
83 where asg.person_id = l_person_id
84 and l_effective_date between asg.effective_start_date and asg.effective_end_date
85 and asg.assignment_type <> 'B';
86
87 cursor get_user_name is
88 select fnd.user_name
89 from fnd_user fnd
90 where l_gbx_user_id = fnd.employee_id;
91
92 l_position_id per_all_assignments_f.position_id%type;
93
94 begin
95
96 hr_utility.set_location('Entering' ,1);
97
98 if funcmode = 'RUN' then
99 hr_utility.set_location('Run' ,1);
100 l_person_id := wf_engine.GetItemAttrText(itemtype => Itemtype,
101 itemkey => Itemkey,
102 aname => 'PERSON_ID');
103 hr_utility.set_location('l_person_id is ' || l_person_id ,1);
104
105 /*l_effective_date := wf_engine.GetItemAttrText(itemtype => Itemtype,
106 itemkey => Itemkey,
107 aname => 'EFFECTIVE_DATE');
108 l_effective_date := nvl(l_effective_date,sysdate);
109 */
110
111 for get_pos_rec in get_position_id loop
112 l_position_id := get_pos_rec.position_id;
113 end loop;
114
115 hr_utility.set_location('l_pos _id is ' || l_position_id ,1);
116
117 ghr_wf_wgi_pkg.get_emp_personnel_groupbox (
118 p_position_id => l_position_id,
119 p_effective_date => l_effective_date,
120 p_groupbox_name => l_groupbox_name,
121 p_personnel_office_id => l_personnel_office_id,
122 p_gbx_user_id => l_gbx_user_id
123 );
124 hr_utility.set_location('l_gbx_id is ' || l_gbx_user_id ,1);
125
126 /* Assuming that either the groupbox or the approver name will be
127 available for each POI. If values exist for both then the notification will
128 be sent to the groupbox
129 */
130 if l_groupbox_name is not null then
131 l_receiver_name := l_groupbox_name;
132 else
133 for user_name_rec in get_user_name loop
134 l_receiver_name := user_name_rec.user_name;
135 end loop;
136 end if;
137
138 wf_engine.SetItemAttrText(itemtype => itemtype
139 ,itemkey => itemkey
140 ,aname => 'RECEIVER_NAME'
141 ,avalue => l_receiver_name);
142
143
144 result := 'COMPLETE:';
145
146 end if;
147
148 end get_poi_to_send_ntfn;
149
150 Procedure ghr_fehb_migrate(
151 p_assignment_id in per_assignments_f.assignment_id%type,
152 p_business_group_id in per_assignments_f.business_group_id%type,
153 p_person_id in per_assignments_f.person_id%type,
154 p_effective_date in Date ,
155 p_health_plan in ben_pl_f.short_code%type,
156 p_option_code in ben_opt_f.short_code%type,
157 p_element_entry_id in pay_element_entries_f.element_entry_id%type,
158 p_object_version_number in pay_element_entries_f.object_version_number%type,
159 p_temps_cost in pay_element_entry_values_f.screen_entry_value%type) is
160
161
162 l_prog_count number;
163 l_plan_count number;
164 l_oipl_count number;
165 l_person_count number;
166 l_plan_nip_count number;
167 l_oipl_nip_count number;
168
169 l_pgm_id ben_pgm_f.pgm_id%type;
170 l_pl_id ben_pl_f.pl_id%type;
171 l_opt_id ben_opt_f.opt_id%type;
172 l_pl_typ_id ben_pl_typ_f.pl_typ_id%type;
173 l_plip_id ben_plip_f.plip_id%type;
174 l_oipl_id ben_oipl_f.oipl_id%type;
175 l_ler_id ben_ler_f.ler_id%type;
176 l_ptip_id ben_ptip_f.ptip_id%type;
177 l_exists boolean;
178 l_ptnl_ler_for_per_id number;
179 l_ovn number;
180 l_errbuf varchar2(2000);
181 l_retcode number;
182 l_elig_per_elctbl_chc_id number;
183 l_prtt_enrt_rslt_id number;
184 l_enrt_bnft_id number;
185 l_prtt_rt_val_id number;
186 l_prtt_rt_val_id1 number;
187 l_prtt_rt_val_id2 number;
188 l_prtt_rt_val_id3 number;
189 l_prtt_rt_val_id4 number;
190 l_prtt_rt_val_id5 number;
191 l_prtt_rt_val_id6 number;
192 l_prtt_rt_val_id7 number;
193 l_prtt_rt_val_id8 number;
194 l_prtt_rt_val_id9 number;
195 l_prtt_rt_val_id10 number;
196 l_suspend_flag varchar2(30);
197 l_esd date;
198 l_eed date;
199 l_prtt_enrt_interim_id number;
200 L_boolean boolean;
201 l_per_in_ler_id number;
202 l_benefit_action_id number;
203 l_err_msg varchar2(2000);
204 Nothing_To_Do Exception;
205 Ben_Rec_Exists Exception;
206
207 l_eff_start_date Date;
208 l_eff_end_date Date;
209 l_del_warning Boolean;
210 l_object_version_number Number;
211 l_input_val_id1 Number;
212 l_input_val_id2 Number;
213 l_input_val_id3 Number;
214 l_input_val_id4 Number;
215
216 l_name Varchar2(240);
217 l_ssn Varchar2(30);
218 --Bug # 9329643
219 l_employee_number Varchar2(30);
220
221 -- Cursor to get the ler_id
222 Cursor get_ler_id is
223 select ler.ler_id
224 from ben_ler_f ler
225 where ler.business_group_id = p_business_group_id
226 and ler.name = 'Added for Migration'
227 and p_effective_date between effective_start_date and effective_end_date;
228
229 -- Cursor to get Pgm_id for the given Business Group
230 Cursor get_pgm_id is
231 select pgm.pgm_id
232 from ben_pgm_f pgm
233 where pgm.name = 'Federal Employees Health Benefits'
234 and pgm.business_group_id = p_business_group_id
235 and p_effective_date between effective_start_date and effective_end_date;
236
237 --Cursor to get the Plan Type Id for the given Business_group_id
238 Cursor get_pl_typ_id is
239 select plt.pl_typ_id
240 from ben_pl_typ_f plt
241 where plt.name = 'Health Benefits'
242 and plt.business_group_id = p_business_group_id
243 and p_effective_date between effective_start_date and effective_end_date;
244
245
246 -- Cursor to get the Ptip_id for the given Pgm and Plan Type.
247 Cursor get_ptip_id is
248 select ptip_id
249 from ben_ptip_f
250 where pl_typ_id = l_pl_typ_id
251 and pgm_id = l_pgm_id
252 and p_effective_date between effective_start_date and effective_end_date;
253
254 -- Cursor to get the Plan Id for the EE's Health Plan screen entry value
255 Cursor get_pl_id is
256 select pln.pl_id pl_id
257 from ben_pl_f pln
258 where pln.short_code = p_health_plan
259 and pln.business_group_id = p_business_group_id
260 and p_effective_date between effective_start_date and effective_end_date
261 and pl_stat_cd = 'A';
262
263 --Cursor to get the plan in Program Id for the given Pl_id
264 Cursor get_plip_id is
265 select plip.plip_id
266 from ben_plip_f plip
267 where plip.pl_id = l_pl_id
268 and plip.pgm_id = l_pgm_id
269 and plip.business_group_id = p_business_group_id
270 and p_effective_date between effective_start_date and effective_end_date;
271
272
273 --Cursor to get the opt_id for the EE's Enrollment Screen Entry Value
274 Cursor get_opt_id is
275 Select opt_id
276 from ben_opt_f opt
277 where opt.short_code = p_option_code
278 and opt.business_group_id = p_business_group_id
279 and p_effective_date between effective_start_date and effective_end_date;
280
281 -- Cursor to get the option in plan Id
282
283 Cursor get_oipl_id is
284 select oipl_id
285 from ben_oipl_f
286 where pl_id = l_pl_id
287 and opt_id = l_opt_id
288 and business_group_id = p_business_group_id
289 and p_effective_date between effective_start_date and effective_end_date;
290
291 -- Cursor to get the elig_chc_id for the plan, Option combination associated with the ler "added during migration" after benmngle is run
292
293 Cursor get_elig_chc_id_opt is
294 select elig_per_elctbl_chc_id,
295 pil.per_in_ler_id
296 from ben_elig_per_ELCTBL_chc chc ,
297 ben_per_in_ler pil
298 where chc.pgm_id = l_pgm_id
299 and chc.pl_typ_id = l_pl_typ_id
300 and chc.pl_id = l_pl_id
301 and chc.plip_id = l_plip_id
302 and chc.ptip_id = l_ptip_id
303 and chc.oipl_id = l_oipl_id
304 and pil.per_in_ler_id = chc.per_in_ler_id
305 and pil.ler_id = l_ler_id
306 and pil.person_id = p_person_id;
307
308 Cursor get_elig_chc_id is
309 select elig_per_elctbl_chc_id,
310 pil.per_in_ler_id
311 from ben_elig_per_ELCTBL_chc chc ,
312 ben_per_in_ler pil
313 where chc.pgm_id = l_pgm_id
314 and chc.pl_typ_id = l_pl_typ_id
315 and chc.pl_id = l_pl_id
316 and chc.plip_id = l_plip_id
317 and chc.ptip_id = l_ptip_id
318 and pil.per_in_ler_id = chc.per_in_ler_id
319 and pil.ler_id = l_ler_id
320 and pil.person_id = p_person_id;
321
322 -- Cursor to check if Employee is currently enrolled in FEHB including
323 -- enrollments made in his prior employment
324 cursor c_emp_in_fehb is
325 select 1
326 from ben_prtt_enrt_rslt_f prt
327 where prt.person_id = p_person_id
328 and pgm_id = l_pgm_id;
329
330 cursor c_get_ovn is
331 select object_version_number
332 from pay_element_entries_f
333 where element_entry_id = p_element_entry_id
334 and effective_start_date < p_effective_date
335 order by effective_start_date desc;
336
337 cursor c_get_new_element_details is
338 SELECT eef_new.element_entry_id,
339 eef_new.object_version_number,
340 eef_new.element_type_id
341 FROM pay_element_entries_f eef_new,
342 pay_element_types_f elt_new
343 WHERE eef_new.assignment_id = p_assignment_id
344 and elt_new.element_type_id = eef_new.element_type_id
345 AND eef_new.effective_start_date BETWEEN elt_new.effective_start_date AND elt_new.effective_end_date
346 and eef_new.effective_start_date = trunc(p_effective_date)
347 and eef_new.effective_end_date = hr_api.g_eot
348 AND upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt_new.element_name,
349 p_business_group_id,
350 eef_new.effective_start_date))
351 IN ('HEALTH BENEFITS PRE TAX'
352 );
353
354 cursor c_get_input_value_ids(l_element_type_id in number) is
355 select input_value_id
356 from pay_input_values_f
357 where element_type_id = l_element_type_id
358 and name = 'Temps Total Cost'
359 and trunc(p_effective_date) between effective_start_date and effective_end_date;
360
361 Cursor c_get_ssn is
362 select national_identifier,
363 --Bug # 9329643
364 employee_number
365 from per_all_people_f
366 where person_id = p_person_id
367 and trunc(p_effective_date) between effective_start_date and effective_end_date;
368 Begin
369 for ler_rec in get_ler_id loop
370 l_ler_id := ler_rec.ler_id;
371 end loop;
372 If l_ler_id is null Then
373 l_err_msg := 'No Life Events defined in employee''s business group ';
374 Raise Nothing_to_do;
375 End If;
376
377 --Get Pgm ID
378 for pgm_rec in get_pgm_id loop
379 l_pgm_id := pgm_rec.pgm_id;
380 end loop;
381 If l_pgm_id is null Then
382 l_err_msg := 'Federal Employee Health Benefits program not defined in employee''s business group ' ;
383 Raise Nothing_to_do;
384 End If;
385
386 --get Full Name
387 l_name := ghr_pa_requests_pkg.get_full_name_unsecure(p_person_id,p_effective_date);
388
389 --get SSN
390 For get_ssn in c_get_ssn loop
391 l_ssn := get_ssn.national_identifier;
392 --Bug # 9329643 Modified SSN to Emp No
393 l_employee_number := get_ssn.employee_number;
394 exit;
395 End loop;
396
397 --Check if record already exists in the Ben table for this person as
398 l_exists := FALSE;
399 for emp_fehb_rec in c_emp_in_fehb loop
400 l_exists := TRUE;
401 exit;
402 end loop;
403 If l_exists then
404 Raise Ben_Rec_Exists;
405 End If;
406
407 for plt_rec in get_pl_typ_id loop
408 l_pl_typ_id := plt_rec.pl_typ_id;
409 end loop;
410
411 for ptip_rec in get_ptip_id loop
412 l_ptip_id := ptip_rec.ptip_id;
413 end loop;
414
415 --get pl_id,opt_id,opil_id,electible_choice_id
416
417 for pl_rec in get_pl_id loop
418 l_pl_id := pl_rec.pl_id;
419 end loop;
420 If l_pl_id is null Then
421 --Bug # 9329643 Modified SSN to Emp No
422 l_err_msg := 'Employee Name : '|| l_name||' Emp No : '||l_employee_number|| ' : Health Plan ' || p_health_plan ||' is not valid for migration ';
423 Raise Nothing_to_do;
424 End If;
425
426 If p_option_code is not null then
427 for opt_rec in get_opt_id loop
428 l_opt_id := opt_rec.opt_id;
429 end loop;
430 If l_opt_id is null Then
431 --dbms_output.put_line ('NO option found ');
432 --Bug # 9329643 Modified SSN to Emp No
433 l_err_msg := 'Employee Name : '|| l_name||' Emp No : '||l_employee_number|| ' : Enrollment Status ' || p_option_code ||' is not valid for migration ';
434 Raise Nothing_to_do;
435 End If;
436 end if;
437
438 -- get plip_id
439 for plip_id_rec in get_plip_id loop
440 l_plip_id := plip_id_rec.plip_id;
441 end loop;
442 --dbms_output.put_line('plip id'|| ' ' ||l_plip_id );
443
444 -- get oipl_id
445 if l_opt_id is not null then
446 for oipl_id_rec in get_oipl_id loop
447 l_oipl_id := oipl_id_rec.oipl_id;
448 end loop;
449 If l_oipl_id is null Then
450 --dbms_output.put_line ('NO Option in Plan Found ');
451 --Bug # 9329643 Modified SSN to Emp No
452 l_err_msg := 'Employee Name : '|| l_name||' Emp No : '||l_employee_number|| ' : Plan/Option combination ' ||p_health_plan||'/'||p_option_code || ' is not defined in employee''s business group';
453 Raise Nothing_to_do;
454 End If;
455 else
456 l_oipl_id := null;
457 end if;
458
459
460 -- Create Potential Life Event
461
462 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
463 (p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id
464 ,p_lf_evt_ocrd_dt => p_effective_date
465 ,p_ptnl_ler_for_per_stat_cd => 'UNPROCD'
466 ,p_ler_id => l_ler_id
467 ,p_person_id => p_person_id
468 ,p_business_group_id => p_business_group_id
469 ,p_unprocd_dt => p_effective_date
470 ,p_object_version_number => l_ovn
471 ,p_effective_date => p_effective_date
472 );
473
474 --dbms_output.put_line('PTNL L.E' || ' ' || l_ptnl_ler_for_per_id);
475 --dbms_output.put_line('pgm_id is ' || ' ' || l_pgm_id);
476 --dbms_output.put_line('pl_typ_id is ' || ' ' || l_pl_typ_id);
477 --dbms_output.put_line('pl_id is ' || ' ' || l_pl_id);
478 --dbms_output.put_line('plip_id is ' || ' ' || l_plip_id);
479 --dbms_output.put_line('ptip_id is ' || ' ' || l_ptip_id);
480 --dbms_output.put_line('oipl_id is ' || ' ' || l_oipl_id);
481 --dbms_output.put_line('ler_id is ' || ' ' || l_ler_id);
482 --dbms_output.put_line('person_id is ' || ' ' || p_person_id);
483 -- Run Process Life Events for the EE
484 --dbms_output.put_line('Before calling p_proc_lf_evts_from_benauthe 1');
485 ben_on_line_lf_evt.p_evt_lf_evts_from_benauthe(
486 p_person_id => p_person_id
487 ,p_effective_date => p_effective_date
488 ,p_business_group_id => p_business_group_id
489 ,p_pgm_id => l_pgm_id
490 ,p_pl_id => l_pl_id
491 ,p_mode => 'L'
492 ,p_popl_enrt_typ_cycl_id => null
493 ,p_lf_evt_ocrd_dt => p_effective_date
494 ,p_prog_count => l_prog_count
495 ,p_plan_count => l_plan_count
496 ,p_oipl_count => l_oipl_count
497 ,p_person_count => l_person_count
498 ,p_plan_nip_count => l_plan_nip_count
499 ,p_oipl_nip_count => l_oipl_nip_count
500 ,p_ler_id => l_ler_id
501 ,p_errbuf => l_errbuf
502 ,p_retcode => l_retcode);
503 --
504 --dbms_output.put_line('Before calling p_proc_lf_evts_from_benauthe 2');
505 ben_on_line_lf_evt.p_proc_lf_evts_from_benauthe(
506 p_person_id => p_person_id
507 ,p_effective_date => p_effective_date
508 ,p_business_group_id => p_business_group_id
509 ,p_mode => 'L'
510 ,p_ler_id => l_ler_id
511 ,p_person_count => l_person_count
512 ,p_benefit_action_id => l_benefit_action_id
513 ,p_errbuf => l_errbuf
514 ,p_retcode => l_retcode);
515 --
516 --dbms_output.put_line('Before opening cursor '||l_oipl_id);
517 If l_oipl_id is not null Then
518 --dbms_output.put_line('1.here');
519 open get_elig_chc_id_opt;
520 fetch get_elig_chc_id_opt into l_elig_per_elctbl_chc_id,l_per_in_ler_id;
521 If get_elig_chc_id_opt%NOTFOUND then
522 --Bug # 9329643 Modified SSN to Emp No
523 l_err_msg := 'Employee Name : '|| l_name||' Emp No : '||l_employee_number|| ' : No electable choice found for this employee';
524 l_err_msg := l_err_msg|| 'Please ensure that the employee is eligible for Federal Employee Health Benefits program and /or the combination of the Plan / Option: '||p_health_plan||'/'||p_option_code;
525 Raise Nothing_to_do;
526 End If;
527 Else
528 --dbms_output.put_line('2.here');
529 open get_elig_chc_id;
530 fetch get_elig_chc_id into l_elig_per_elctbl_chc_id,l_per_in_ler_id;
531 If get_elig_chc_id%NOTFOUND then
532 --Bug # 9329643 Modified SSN to Emp No
533 l_err_msg := 'Employee Name : '|| l_name||' Emp No : '||l_employee_number|| ' : No electable choice found for this employee';
534 l_err_msg := l_err_msg|| 'Please ensure that the employee is eligible for Federal Employee Health Benefits program and /or the combination of the Plan / Option: '||p_health_plan||'/'||p_option_code;
535 Raise Nothing_to_do;
536 End If;
537 End If;
538 --l_elig_per_elctbl_chc_id := elig_rec.elig_per_elctbl_chc_id;
539 --l_per_in_ler_id := elig_rec.per_in_ler_id;
540 --dbms_output.put_line('Electable choice id ' || l_elig_per_elctbl_chc_id);
541 --dbms_output.put_line('PER in LER ID ' || l_per_in_ler_id);
542 ben_election_information.election_information
543 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
544 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
545 ,p_effective_date => p_effective_date
546 ,p_enrt_mthd_cd => 'E'
547 ,p_enrt_bnft_id => l_enrt_bnft_id
548 ,p_prtt_rt_val_id1 => l_prtt_rt_val_id1
549 ,p_prtt_rt_val_id2 => l_prtt_rt_val_id2
550 ,p_prtt_rt_val_id3 => l_prtt_rt_val_id3
551 ,p_prtt_rt_val_id4 => l_prtt_rt_val_id4
552 ,p_prtt_rt_val_id5 => l_prtt_rt_val_id5
553 ,p_prtt_rt_val_id6 => l_prtt_rt_val_id6
554 ,p_prtt_rt_val_id7 => l_prtt_rt_val_id7
555 ,p_prtt_rt_val_id8 => l_prtt_rt_val_id8
556 ,p_prtt_rt_val_id9 => l_prtt_rt_val_id9
557 ,p_prtt_rt_val_id10 => l_prtt_rt_val_id10
558 ,p_enrt_cvg_strt_dt => p_effective_date
559 ,p_enrt_cvg_thru_dt => hr_api.g_eot
560 ,p_datetrack_mode => 'INSERT'
561 ,p_suspend_flag => l_suspend_flag
562 ,p_effective_start_date => l_esd
563 ,p_effective_end_date => l_eed
564 ,p_object_version_number => l_ovn
565 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
566 ,p_business_group_id => p_business_group_id
567 ,p_dpnt_actn_warning => l_Boolean
568 ,p_bnf_actn_warning => l_Boolean
569 ,p_ctfn_actn_warning => l_Boolean
570 );
571 --dbms_output.put_line('NOw calling ben-proc_common_enrt_rslt.post_enrt');
572
573 ben_proc_common_enrt_rslt.process_post_enrt_calls_w
574 (p_validate => 'N'
575 ,p_person_id => p_person_id
576 ,p_per_in_ler_id => l_per_in_ler_id
577 ,p_pgm_id => l_pgm_id
578 ,p_pl_id => l_pl_id
579 ,p_flx_cr_flag => 'N'
580 ,p_enrt_mthd_cd => 'E'
581 ,p_proc_cd => null
582 ,p_cls_enrt_flag => 'Y'
583 ,p_business_group_id => p_business_group_id
584 ,p_effective_date => p_effective_date);
585 --
586 If get_elig_chc_id_opt%ISOPEN Then
587 close get_elig_chc_id_opt;
588 End If;
589 If get_elig_chc_id%ISOPEN Then
590 close get_elig_chc_id;
591 End If;
592 --dbms_output.put_line('Enrollment Result id ' || l_prtt_enrt_rslt_id);
593
594 -- if the new element for Health Benefits Pre tax is created then (if option is %P)
595 -- 1) delete the element record of "Health Benefits' element
596 -- 2) update value for Temps Total Cost in the new element
597 If p_option_code like '%P' then
598 -- get the version number of the previous record
599 for get_ovn in c_get_ovn LOOP
600 l_object_version_number := get_ovn.object_version_number;
601 exit;
602 End Loop;
603 --if there was no previous record for the same element entry then pass ZAP else delete to API
604 if l_object_version_number is null then
605 l_object_version_number := p_object_version_number;
606 py_element_entry_api.delete_element_entry(
607 p_datetrack_delete_mode => hr_api.g_zap,
608 p_effective_date => p_effective_date,
609 p_element_entry_id => p_element_entry_id,
610 p_object_version_number => l_object_version_number,
611 p_effective_start_date => l_eff_start_date,
612 p_effective_end_date => l_eff_end_date,
613 p_delete_warning => l_del_warning);
614 Else
615 py_element_entry_api.delete_element_entry(
616 p_datetrack_delete_mode => hr_api.g_delete,
617 p_effective_date => (p_effective_date - 1),
618 p_element_entry_id => p_element_entry_id,
619 p_object_version_number => l_object_version_number,
620 p_effective_start_date => l_eff_start_date,
621 p_effective_end_date => l_eff_end_date,
622 p_delete_warning => l_del_warning);
623 End If;
624 If p_temps_cost is not null then
625 for get_new_element_details in c_get_new_element_details loop
626 --dbms_output.put_line ('element entry_id ' ||get_new_element_details.element_entry_id);
627 --dbms_output.put_line ('object_version_number ' ||get_new_element_details.object_version_number);
628 --dbms_output.put_line ('element_type_id ' ||get_new_element_details.element_type_id);
629
630 for get_input_value_ids in c_get_input_value_ids(get_new_element_details.element_type_id) loop
631 l_input_val_id3 := get_input_value_ids.input_value_id;
632 exit;
633 end loop;
634 --dbms_output.put_line ('input_value_id ' ||l_input_val_id3);
635 py_element_entry_api.update_element_entry(
636 p_datetrack_update_mode => hr_api.g_correction
637 ,p_effective_date => p_effective_date
638 ,p_business_group_id => p_business_group_id
639 ,p_element_entry_id => get_new_element_details.element_entry_id
640 ,p_object_version_number => get_new_element_details.object_version_number
641 ,p_input_value_id3 => l_input_val_id3
642 ,p_entry_value3 => p_temps_cost
643 ,p_effective_start_date => l_eff_start_date
644 ,p_effective_end_date => l_eff_end_date
645 ,p_update_warning => l_del_warning
646 );
647 exit;
648 end loop;
649 End If;
650 End If;
651 Exception
652 When Ben_Rec_Exists Then
653 null;
654 When Nothing_to_do Then
655 --dbms_output.put_line('1.Script Failed. Contact Your System Administrator.! ');
656 If get_elig_chc_id_opt%ISOPEN then
657 close get_elig_chc_id_opt;
658 End If;
659 If get_elig_chc_id%ISOPEN Then
660 close get_elig_chc_id;
661 End If;
662 rollback;
663 ghr_mto_int.log_message(null,l_err_msg);
664 /*ghr_wgi_pkg.create_ghr_errorlog
665 (p_program_name => 'FEHB_MIG-'||to_char(sysdate,'MM/DD/YYYY'),
666 p_log_text => l_err_msg,
667 p_message_name => null,
668 p_log_date => sysdate
669 ); */
670 --dbms_output.put_line('Data Issue... Nothing_to_do! ');
671 When others then
672 --dbms_output.put_line('2.Script Failed. Contact Your System Administrator.! ');
673 --dbms_output.put_line('Error ' ||sqlerrm(sqlcode));
674 If get_elig_chc_id_opt%ISOPEN then
675 close get_elig_chc_id_opt;
676 End If;
677 If get_elig_chc_id%ISOPEN Then
678 close get_elig_chc_id;
679 End If;
680 rollback;
681 --Bug # 9329643 Modified SSN to Emp No
682 l_err_msg := 'Name :'|| l_name||' Emp No : '||l_employee_number;
683 ghr_mto_int.log_message(null,l_err_msg||' '||sqlerrm(sqlcode));
684 /*ghr_wgi_pkg.create_ghr_errorlog
685 (p_program_name => 'FEHB_MIG-'||to_char(sysdate,'MM/DD/YYYY'),
686 p_log_text => 'PERSON ID:'||p_person_id || ' ' || sqlerrm(sqlcode),
687 p_message_name => null,
688 p_log_date => sysdate
689 ); */
690 End ghr_fehb_migrate;
691
692 /* This procedure is used to migrate existing employees on TSP elements into */
693 /* Assumptions: */
694 /* 1) IF value for both Rate and Amount input value exists, rate supercedes */
695 /* 2) Existing element start date would be used as Rate Start Date and */
696 /* 3) Coverage Start date */
697 /* 4) */
698 /* */
699 Procedure ghr_tsp_migrate(
700 p_assignment_id in per_assignments_f.assignment_id%type,
701 p_opt_name in Varchar2,
702 p_opt_val in Number,
703 p_effective_date in Date,
704 p_business_group_id in per_assignments_f.business_group_id%type,
705 p_person_id in per_assignments_f.person_id%type) is
706
707 l_prog_count number;
708 l_plan_count number;
709 l_oipl_count number;
710 l_person_count number;
711 l_plan_nip_count number;
712 l_oipl_nip_count number;
713
714 l_pgm_id ben_pgm_f.pgm_id%type;
715 l_pl_id ben_pl_f.pl_id%type;
716 l_opt_id ben_opt_f.opt_id%type;
717 l_pl_typ_id ben_pl_typ_f.pl_typ_id%type;
718 l_plip_id ben_plip_f.plip_id%type;
719 l_oipl_id ben_oipl_f.oipl_id%type;
720 l_ler_id ben_ler_f.ler_id%type;
721 l_ptip_id ben_ptip_f.ptip_id%type;
722 l_assignment_id per_all_assignments_f.assignment_id%type;
723 l_exists boolean;
724 l_ovn number;
725 l_errbuf varchar2(2000);
726 l_retcode number;
727 l_elig_per_elctbl_chc_id number;
728 l_prtt_enrt_rslt_id number;
729 l_enrt_rt_id number;
730 l_enrt_bnft_id number;
731 l_opt_val number;
732 l_prtt_rt_val_id number;
733 l_prtt_rt_val_id1 number;
734 l_prtt_rt_val_id2 number;
735 l_prtt_rt_val_id3 number;
736 l_prtt_rt_val_id4 number;
737 l_prtt_rt_val_id5 number;
738 l_prtt_rt_val_id6 number;
739 l_prtt_rt_val_id7 number;
740 l_prtt_rt_val_id8 number;
741 l_prtt_rt_val_id9 number;
742 l_prtt_rt_val_id10 number;
743 l_suspend_flag varchar2(30);
744 l_esd date;
745 l_eed date;
746 l_effective_date date;
747 l_prtt_enrt_interim_id number;
748 L_boolean boolean;
749 l_per_in_ler_id number;
750 l_benefit_action_id number;
751 l_err_msg varchar2(2000);
752 Nothing_To_Do Exception;
753 Ben_Enrt_Exists Exception;
754
755 l_name Varchar2(240);
756 l_ssn Varchar2(30);
757 --Bug # 9329643
758 l_employee_number Varchar2(30);
759
760 -- Cursor to get Pgm_id for the given Business Group
761 Cursor c_get_pgm_id is
762 select pgm.pgm_id
763 from ben_pgm_f pgm
764 where pgm.name = 'Federal Thrift Savings Plan (TSP)'
765 and pgm.business_group_id = p_business_group_id;
766
767 --Cursor to get the Plan Type Id for the given Business_group_id
768 Cursor c_get_pl_typ_id is
769 select plt.pl_typ_id
770 from ben_pl_typ_f plt
771 where plt.name = 'Savings Plan'
772 and plt.business_group_id = p_business_group_id;
773
774 -- Cursor to get the Ptip_id for the given Pgm and Plan Type.
775 Cursor c_get_ptip_id is
776 select ptip_id
777 from ben_ptip_f
778 where pl_typ_id = l_pl_typ_id
779 and pgm_id = l_pgm_id;
780
781 -- Cursor to get the Plan Id
782 Cursor c_get_pl_id is
783 select pln.pl_id pl_id
784 from ben_pl_f pln
785 where pln.name = 'TSP'
786 and pln.business_group_id = p_business_group_id;
787
788 --Cursor to get the plan in Program Id for the given Pl_id
789 Cursor c_get_plip_id is
790 select plip.plip_id
791 from ben_plip_f plip
792 where plip.pl_id = l_pl_id
793 and plip.pgm_id = l_pgm_id
794 and plip.business_group_id = p_business_group_id;
795
796 --Cursor to get the opt_id for the EE's Enrollment Screen Entry Value
797 Cursor c_get_opt_id is
798 Select opt_id
799 from ben_opt_f opt
800 where name = p_opt_name
801 and opt.business_group_id = p_business_group_id;
802
803 -- Cursor to get the option in plan Id
804 Cursor c_get_oipl_id is
805 select oipl_id
806 from ben_oipl_f
807 where pl_id = l_pl_id
808 and opt_id = l_opt_id
809 and business_group_id = p_business_group_id;
810
811 -- Cursor to get the elig_chc_id for the plan, Option
812 Cursor c_get_elig_chc_id_opt is
813 select elig_per_elctbl_chc_id,
814 pil.per_in_ler_id
815 from ben_elig_per_ELCTBL_chc chc ,
816 ben_per_in_ler pil
817 where chc.pgm_id = l_pgm_id
818 and chc.pl_typ_id = l_pl_typ_id
819 and chc.pl_id = l_pl_id
820 and chc.plip_id = l_plip_id
821 and chc.ptip_id = l_ptip_id
822 and chc.oipl_id = l_oipl_id
823 and pil.per_in_ler_id = chc.per_in_ler_id
824 --and pil.ler_id = l_ler_id
825 and pil.person_id = p_person_id;
826
827 Cursor c_get_enrt_rt_id is
828 select enrt_rt_id
829 from ben_enrt_rt
830 where elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id;
831
832
833 -- Cursor to check if Employee is currently enrolled in TSP
834 cursor c_emp_in_tsp is
835 select 1
836 from ben_prtt_enrt_rslt_f
837 where person_id = p_person_id
838 and pgm_id = l_pgm_id;
839
840 Cursor c_get_ssn is
841 --Bug # 9329643
842 select national_identifier,
843 employee_number
844 from per_all_people_f
845 where person_id = p_person_id
846 and trunc(p_effective_date) between effective_start_date and effective_end_date;
847
848 BEGIN
849
850 -- Get PGM ID
851 for pgm_rec in c_get_pgm_id loop
852 l_pgm_id := pgm_rec.pgm_id;
853 exit;
854 end loop;
855 If l_pgm_id is null Then
856 --dbms_output.put_line ('NO program found ');
857 l_err_msg := 'Federal Thrift Savings Plan (TSP) program not defined in employee''s business group ' ;
858 Raise Nothing_to_do;
859 End If;
860
861 --get Full Name
862 l_name := ghr_pa_requests_pkg.get_full_name_unsecure(p_person_id,p_effective_date);
863 --dbms_output.put_line ('Full Name '||l_name);
864
865 --get SSN
866 For get_ssn in c_get_ssn loop
867 l_ssn := get_ssn.national_identifier;
868 --Bug #9329643
869 l_employee_number := get_ssn.employee_number;
870 exit;
871 End loop;
872 --dbms_output.put_line ('SSN '||l_ssn);
873
874
875 --Check if record already exists in the Ben table for this person
876 l_exists := FALSE;
877 for emp_tsp_rec in c_emp_in_tsp loop
878 l_exists := TRUE;
879 exit;
880 end loop;
881 If l_exists then
882 --dbms_output.put_line('No Action' );
883 Raise ben_enrt_exists;
884 End If;
885
886 For plt_rec in c_get_pl_typ_id loop
887 l_pl_typ_id := plt_rec.pl_typ_id;
888 exit;
889 end loop;
890 --dbms_output.put_line('pl_typ id'|| ' ' ||l_pl_typ_id );
891
892 for ptip_rec in c_get_ptip_id loop
893 l_ptip_id := ptip_rec.ptip_id;
894 exit;
895 end loop;
896 --dbms_output.put_line('ptip id'|| ' ' ||l_ptip_id );
897
898 --get pl_id,opt_id,opil_id,electible_choice_id
899
900 for pl_rec in c_get_pl_id loop
901 l_pl_id := pl_rec.pl_id;
902 exit;
903 end loop;
904 --dbms_output.put_line(' l_plan_id ' || l_pl_id);
905 If l_pl_id is null Then
906 --dbms_output.put_line ('NO plan found ');
907 --Bug # 9329643 Modified SSN to Emp No
908 l_err_msg := 'Employee Name : '|| l_name||' Emp No : '||l_employee_number|| ' : TSP Plan is not valid for migration ';
909 Raise Nothing_to_do;
910 End If;
911
912 for opt_rec in c_get_opt_id loop
913 l_opt_id := opt_rec.opt_id;
914 exit;
915 end loop;
916 If l_opt_id is null Then
917 --dbms_output.put_line ('NO option found ');
918 --Bug # 9329643 Modified SSN to Emp No
919 l_err_msg := 'Employee Name : '|| l_name||' Emp No : '||l_employee_number|| ' : Option ' || p_opt_name ||' is not valid for migration ';
920 Raise Nothing_to_do;
921 End If;
922 --dbms_output.put_line('opt id'|| ' ' ||l_opt_id );
923
924 -- get plip_id
925 for plip_id_rec in c_get_plip_id loop
926 l_plip_id := plip_id_rec.plip_id;
927 exit;
928 end loop;
929 --dbms_output.put_line('plip id'|| ' ' ||l_plip_id );
930
931 -- get oipl_id
932 for oipl_id_rec in c_get_oipl_id loop
933 l_oipl_id := oipl_id_rec.oipl_id;
934 exit;
935 end loop;
936 If l_oipl_id is null Then
937 --dbms_output.put_line ('NO Option in Plan Found ');
938 --Bug # 9329643 Modified SSN to Emp No
939 l_err_msg := 'Employee Name : '|| l_name||' Emp No : '||l_employee_number|| ' : Plan/Option combination TSP/' ||p_opt_name || ' is not defined in employee''s business group';
940 Raise Nothing_to_do;
941 End If;
942
943 --dbms_output.put_line('l_oipl_id is ' || ' ' || l_oipl_id);
944 --dbms_output.put_line('p_person_id is ' || ' ' || p_person_id);
945 --dbms_output.put_line('p_effective_date is ' || ' ' || p_effective_date);
946
947 if p_effective_date < to_date('07/01/2005','MM/DD/YYYY') then
948 l_effective_date := to_date('07/01/2005','MM/DD/YYYY');
949 else
950 l_effective_date := p_effective_date;
951 End If;
952
953 ben_on_line_lf_evt.p_manage_life_events(
954 p_person_id => p_person_id
955 ,p_effective_date => l_effective_date
956 ,p_business_group_id => p_business_group_id
957 ,p_pgm_id => l_pgm_id
958 ,p_pl_id => l_pl_id
959 ,p_mode => 'U' -- Unrestricted
960 ,p_prog_count => l_prog_count
961 ,p_plan_count => l_plan_count
962 ,p_oipl_count => l_oipl_count
963 ,p_person_count => l_person_count
964 ,p_plan_nip_count => l_plan_nip_count
965 ,p_oipl_nip_count => l_oipl_nip_count
966 ,p_ler_id => l_ler_id
967 ,p_errbuf => l_errbuf
968 ,p_retcode => l_retcode);
969
970 --commit;
971 --dbms_output.put_line('Before opening cursor '||l_ler_id);
972 --dbms_output.put_line('1.here');
973
974 for get_elig_chc_id in c_get_elig_chc_id_opt loop
975 l_elig_per_elctbl_chc_id := get_elig_chc_id.elig_per_elctbl_chc_id;
976 l_per_in_ler_id := get_elig_chc_id.per_in_ler_id;
977 exit;
978 End Loop;
979 If l_elig_per_elctbl_chc_id is null Then
980 --dbms_output.put_line('No Electable choice id ');
981 --Bug # 9329643 Modified SSN to Emp No
982 l_err_msg := 'Name : '|| l_name||' Emp No : '||l_employee_number|| ' : No electable choice found for this employee. Please ensure that the employee is eligible for Federal Thrift Savings Plan (TSP) program';
983 Raise Nothing_to_do;
984 End If;
985
986 --dbms_output.put_line('Electable choice id ' || l_elig_per_elctbl_chc_id);
987 --dbms_output.put_line('PER in LER ID ' || l_per_in_ler_id);
988 --dbms_output.put_line('opt val ' || p_opt_val);
989
990
991 for get_enrt_rt_id in c_get_enrt_rt_id loop
992 l_enrt_rt_id := get_enrt_rt_id.enrt_rt_id;
993 exit;
994 End Loop;
995 If l_enrt_rt_id is null Then
996 --dbms_output.put_line('No Electable rate id ');
997 --Bug # 9329643 Modified SSN to Emp No
998 l_err_msg := 'Name:'|| l_name||' Emp No : '||l_employee_number|| ' :TSP value is outside IRS limits' ;
999 Raise Nothing_to_do;
1000 End If;
1001 --dbms_output.put_line('enrt rate id ' || l_enrt_rt_id);
1002
1003 ben_election_information.election_information
1004 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
1005 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
1006 ,p_effective_date => l_effective_date
1007 ,p_enrt_mthd_cd => 'E'
1008 ,p_enrt_bnft_id => l_enrt_bnft_id
1009 ,p_enrt_rt_id1 => l_enrt_rt_id
1010 ,p_rt_val1 => p_opt_val
1011 ,p_rt_strt_dt1 => l_effective_date
1012 ,p_rt_end_dt1 => hr_api.g_eot
1013 ,p_prtt_rt_val_id1 => l_prtt_rt_val_id1
1014 ,p_prtt_rt_val_id2 => l_prtt_rt_val_id2
1015 ,p_prtt_rt_val_id3 => l_prtt_rt_val_id3
1016 ,p_prtt_rt_val_id4 => l_prtt_rt_val_id4
1017 ,p_prtt_rt_val_id5 => l_prtt_rt_val_id5
1018 ,p_prtt_rt_val_id6 => l_prtt_rt_val_id6
1019 ,p_prtt_rt_val_id7 => l_prtt_rt_val_id7
1020 ,p_prtt_rt_val_id8 => l_prtt_rt_val_id8
1021 ,p_prtt_rt_val_id9 => l_prtt_rt_val_id9
1022 ,p_prtt_rt_val_id10 => l_prtt_rt_val_id10
1023 ,p_enrt_cvg_strt_dt => p_effective_date
1024 ,p_enrt_cvg_thru_dt => hr_api.g_eot
1025 ,p_datetrack_mode => 'INSERT'
1026 ,p_suspend_flag => l_suspend_flag
1027 ,p_effective_start_date => l_esd
1028 ,p_effective_end_date => l_eed
1029 ,p_object_version_number => l_ovn
1030 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
1031 ,p_business_group_id => p_business_group_id
1032 ,p_dpnt_actn_warning => l_Boolean
1033 ,p_bnf_actn_warning => l_Boolean
1034 ,p_ctfn_actn_warning => l_Boolean
1035 );
1036
1037 --dbms_output.put_line('Enrollment Result id ' || l_prtt_enrt_rslt_id);
1038 Exception
1039 When ben_Enrt_Exists Then
1040 null;
1041 When Nothing_to_do Then
1042 rollback;
1043 ghr_mto_int.log_message(null,l_err_msg);
1044 /*
1045 ghr_wgi_pkg.create_ghr_errorlog
1046 (p_program_name => 'TSP_MIG-'||to_char(sysdate,'MM/DD/YYYY'),
1047 p_log_text => l_err_msg,
1048 p_message_name => null,
1049 p_log_date => sysdate
1050 ); */
1051 --dbms_output.put_line('Data Issue... Nothing_to_do! ');
1052 When others then
1053 --dbms_output.put_line('Script Failed. Contact Your System Administrator.! ');
1054 rollback;
1055 --Bug # 9329643 Modified SSN to Emp No
1056 l_err_msg := 'Name :'|| l_name||' Emp No : '||l_employee_number;
1057 ghr_mto_int.log_message(null,l_err_msg||' '||sqlerrm(sqlcode));
1058 /* ghr_wgi_pkg.create_ghr_errorlog
1059 (p_program_name => 'TSP_MIG-'||to_char(sysdate,'MM/DD/YYYY'),
1060 p_log_text => 'PERSON ID:'||p_person_id || ' ' || sqlerrm(sqlcode),
1061 p_message_name => null,
1062 p_log_date => sysdate
1063 ); */
1064 End ghr_tsp_migrate;
1065
1066
1067 /* TSP Catch Up Migration Procedure */
1068 Procedure ghr_tsp_catchup_migrate(
1069 p_assignment_id in per_assignments_f.assignment_id%type,
1070 p_opt_name in Varchar2,
1071 p_opt_val in Number,
1072 p_effective_date in Date,
1073 p_business_group_id in per_assignments_f.business_group_id%type,
1074 p_person_id in per_assignments_f.person_id%type) is
1075
1076 l_prog_count number;
1077 l_plan_count number;
1078 l_oipl_count number;
1079 l_person_count number;
1080 l_plan_nip_count number;
1081 l_oipl_nip_count number;
1082
1083 l_pgm_id ben_pgm_f.pgm_id%type;
1084 l_pl_id ben_pl_f.pl_id%type;
1085 l_opt_id ben_opt_f.opt_id%type;
1086 l_pl_typ_id ben_pl_typ_f.pl_typ_id%type;
1087 l_plip_id ben_plip_f.plip_id%type;
1088 l_oipl_id ben_oipl_f.oipl_id%type;
1089 l_ler_id ben_ler_f.ler_id%type;
1090 l_ptip_id ben_ptip_f.ptip_id%type;
1091 l_exists boolean;
1092 l_ovn number;
1093 l_errbuf varchar2(2000);
1094 l_retcode number;
1095 l_elig_per_elctbl_chc_id number;
1096 l_prtt_enrt_rslt_id number;
1097 l_enrt_rt_id number;
1098 l_enrt_bnft_id number;
1099 l_opt_val number;
1100 l_prtt_rt_val_id number;
1101 l_prtt_rt_val_id1 number;
1102 l_prtt_rt_val_id2 number;
1103 l_prtt_rt_val_id3 number;
1104 l_prtt_rt_val_id4 number;
1105 l_prtt_rt_val_id5 number;
1106 l_prtt_rt_val_id6 number;
1107 l_prtt_rt_val_id7 number;
1108 l_prtt_rt_val_id8 number;
1109 l_prtt_rt_val_id9 number;
1110 l_prtt_rt_val_id10 number;
1111 l_suspend_flag varchar2(30);
1112 l_esd date;
1113 l_eed date;
1114 l_prtt_enrt_interim_id number;
1115 L_boolean boolean;
1116 l_per_in_ler_id number;
1117 l_benefit_action_id number;
1118 l_err_msg varchar2(2000);
1119 Nothing_To_Do Exception;
1120 Ben_Enrt_Exists Exception;
1121
1122 l_name Varchar2(240);
1123 l_ssn Varchar2(30);
1124 --Bug # 9329643
1125 l_employee_number Varchar2(30);
1126 l_effective_date date;
1127
1128 -- Cursor to get Pgm_id for the given Business Group
1129 Cursor c_get_pgm_id is
1130 select pgm.pgm_id
1131 from ben_pgm_f pgm
1132 where pgm.name = 'Federal Thrift Savings Plan (TSP) Catch Up Contributions'
1133 and pgm.business_group_id = p_business_group_id;
1134
1135 --Cursor to get the Plan Type Id for the given Business_group_id
1136 Cursor c_get_pl_typ_id is
1137 select plt.pl_typ_id
1138 from ben_pl_typ_f plt
1139 where plt.name = 'Savings Plan'
1140 and plt.business_group_id = p_business_group_id;
1141
1142 -- Cursor to get the Ptip_id for the given Pgm and Plan Type.
1143 Cursor c_get_ptip_id is
1144 select ptip_id
1145 from ben_ptip_f
1146 where pl_typ_id = l_pl_typ_id
1147 and pgm_id = l_pgm_id;
1148
1149 -- Cursor to get the Plan Id
1150 Cursor c_get_pl_id is
1151 select pln.pl_id pl_id
1152 from ben_pl_f pln
1153 where pln.name = 'TSP Catch Up'
1154 and pln.business_group_id = p_business_group_id;
1155
1156 --Cursor to get the plan in Program Id for the given Pl_id
1157 Cursor c_get_plip_id is
1158 select plip.plip_id
1159 from ben_plip_f plip
1160 where plip.pl_id = l_pl_id
1161 and plip.pgm_id = l_pgm_id
1162 and plip.business_group_id = p_business_group_id;
1163
1164 --Cursor to get the opt_id for the EE's Enrollment Screen Entry Value
1165 Cursor c_get_opt_id is
1166 Select opt_id
1167 from ben_opt_f opt
1168 where name = p_opt_name
1169 and opt.business_group_id = p_business_group_id;
1170
1171 -- Cursor to get the option in plan Id
1172 Cursor c_get_oipl_id is
1173 select oipl_id
1174 from ben_oipl_f
1175 where pl_id = l_pl_id
1176 and opt_id = l_opt_id
1177 and business_group_id = p_business_group_id;
1178
1179 -- Cursor to get the elig_chc_id for the plan, Option
1180 Cursor c_get_elig_chc_id_opt is
1181 select elig_per_elctbl_chc_id,
1182 pil.per_in_ler_id
1183 from ben_elig_per_ELCTBL_chc chc ,
1184 ben_per_in_ler pil
1185 where chc.pgm_id = l_pgm_id
1186 and chc.pl_typ_id = l_pl_typ_id
1187 and chc.pl_id = l_pl_id
1188 and chc.plip_id = l_plip_id
1189 and chc.ptip_id = l_ptip_id
1190 and chc.oipl_id = l_oipl_id
1191 and pil.per_in_ler_id = chc.per_in_ler_id
1192 and pil.person_id = p_person_id;
1193
1194 Cursor c_get_enrt_rt_id is
1195 select enrt_rt_id
1196 from ben_enrt_rt
1197 where elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id;
1198
1199
1200 -- Cursor to check if Employee is currently enrolled in TSP Catchup
1201 cursor c_emp_in_tsp is
1202 select 1
1203 from ben_prtt_enrt_rslt_f
1204 where person_id = p_person_id
1205 and pgm_id = l_pgm_id;
1206
1207 Cursor c_get_ssn is
1208 --Bug # 9329643 added employee number
1209 select national_identifier,
1210 employee_number
1211 from per_all_people_f
1212 where person_id = p_person_id
1213 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1214
1215 BEGIN
1216 --dbms_output.put_line('********** start ************** ' ||to_char(p_opt_val)||' plan ' ||p_opt_name);
1217
1218 -- Get PGM ID
1219 for pgm_rec in c_get_pgm_id loop
1220 l_pgm_id := pgm_rec.pgm_id;
1221 exit;
1222 end loop;
1223 If l_pgm_id is null Then
1224 --dbms_output.put_line ('NO program found ');
1225 l_err_msg := 'Federal Thrift Savings Plan (TSP) Catch Up Contributions program not defined in employee''s business group ' ;
1226 Raise Nothing_to_do;
1227 End If;
1228
1229 --get Full Name
1230 l_name := ghr_pa_requests_pkg.get_full_name_unsecure(p_person_id,p_effective_date);
1231 --dbms_output.put_line ('Full Name '||l_name);
1232
1233 --get SSN
1234 For get_ssn in c_get_ssn loop
1235 l_ssn := get_ssn.national_identifier;
1236 l_employee_number := get_ssn.employee_number;
1237 exit;
1238 End loop;
1239 --dbms_output.put_line ('SSN '||l_ssn);
1240
1241 --Check if record already exists in the Ben table for this person
1242 l_exists := FALSE;
1243 for emp_tsp_rec in c_emp_in_tsp loop
1244 l_exists := TRUE;
1245 exit;
1246 end loop;
1247 If l_exists then
1248 --dbms_output.put_line('No Action' );
1249 Raise ben_enrt_exists;
1250 End If;
1251
1252 For plt_rec in c_get_pl_typ_id loop
1253 l_pl_typ_id := plt_rec.pl_typ_id;
1254 exit;
1255 end loop;
1256 --dbms_output.put_line('pl_typ id'|| ' ' ||l_pl_typ_id );
1257
1258 for ptip_rec in c_get_ptip_id loop
1259 l_ptip_id := ptip_rec.ptip_id;
1260 exit;
1261 end loop;
1262 --dbms_output.put_line('ptip id'|| ' ' ||l_ptip_id );
1263
1264 --get pl_id,opt_id,opil_id,electible_choice_id
1265
1266 for pl_rec in c_get_pl_id loop
1267 l_pl_id := pl_rec.pl_id;
1268 exit;
1269 end loop;
1270 --dbms_output.put_line(' l_plan_id ' || l_pl_id);
1271 If l_pl_id is null Then
1272 --dbms_output.put_line ('NO plan found ');
1273 --Bug # 9329643 Modified SSN to Emp No
1274 l_err_msg := 'Employee Name : '|| l_name||' Emp No : '||l_employee_number|| ' : TSP Catch Up Plan is not valid for migration ';
1275 Raise Nothing_to_do;
1276 End If;
1277
1278 for opt_rec in c_get_opt_id loop
1279 l_opt_id := opt_rec.opt_id;
1280 exit;
1281 end loop;
1282 If l_opt_id is null Then
1283 --dbms_output.put_line ('NO option found ');
1284 --Bug # 9329643 Modified SSN to Emp No
1285 l_err_msg := 'Employee Name : '|| l_name||' Emp No : '||l_employee_number|| ' : Option ' || p_opt_name ||' is not valid for migration ';
1286 Raise Nothing_to_do;
1287 End If;
1288 --dbms_output.put_line('opt id'|| ' ' ||l_opt_id );
1289
1290 -- get plip_id
1291 for plip_id_rec in c_get_plip_id loop
1292 l_plip_id := plip_id_rec.plip_id;
1293 exit;
1294 end loop;
1295 --dbms_output.put_line('plip id'|| ' ' ||l_plip_id );
1296
1297 -- get oipl_id
1298 for oipl_id_rec in c_get_oipl_id loop
1299 l_oipl_id := oipl_id_rec.oipl_id;
1300 exit;
1301 end loop;
1302 If l_oipl_id is null Then
1303 --dbms_output.put_line ('NO Option in Plan Found ');
1304 --Bug # 9329643 Modified SSN to Emp No
1305 l_err_msg := 'Employee Name : '|| l_name||' Emp No : '||l_employee_number|| ' : Plan/Option combination TSP Catch Up/' ||p_opt_name || ' is not defined in employee''s business group';
1306 Raise Nothing_to_do;
1307 End If;
1308
1309 --dbms_output.put_line('l_oipl_id is ' || ' ' || l_oipl_id);
1310 --dbms_output.put_line('p_effective_date is ' || ' ' || p_effective_date);
1311
1312 if p_effective_date < to_date('07/01/2005','MM/DD/YYYY') then
1313 l_effective_date := to_date('07/01/2005','MM/DD/YYYY');
1314 else
1315 l_effective_date := p_effective_date;
1316 End If;
1317
1318 ben_on_line_lf_evt.p_manage_life_events(
1319 p_person_id => p_person_id
1320 ,p_effective_date => l_effective_date
1321 ,p_business_group_id => p_business_group_id
1322 ,p_pgm_id => l_pgm_id
1323 ,p_pl_id => l_pl_id
1324 ,p_mode => 'U' -- Unrestricted
1325 ,p_prog_count => l_prog_count
1326 ,p_plan_count => l_plan_count
1327 ,p_oipl_count => l_oipl_count
1328 ,p_person_count => l_person_count
1329 ,p_plan_nip_count => l_plan_nip_count
1330 ,p_oipl_nip_count => l_oipl_nip_count
1331 ,p_ler_id => l_ler_id
1332 ,p_errbuf => l_errbuf
1333 ,p_retcode => l_retcode);
1334
1335
1336 --dbms_output.put_line('Before opening cursor '||l_oipl_id);
1337 --dbms_output.put_line('1.here');
1338
1339 for get_elig_chc_id in c_get_elig_chc_id_opt loop
1340 l_elig_per_elctbl_chc_id := get_elig_chc_id.elig_per_elctbl_chc_id;
1341 l_per_in_ler_id := get_elig_chc_id.per_in_ler_id;
1342 exit;
1343 End Loop;
1344 If l_elig_per_elctbl_chc_id is null Then
1345 --dbms_output.put_line('No Electable choice id ');
1346 --Bug # 9329643 Modified SSN to Emp No
1347 l_err_msg := 'Name : '|| l_name||' Emp No : '||l_employee_number|| ' : No electable choice found for this employee.Please ensure that the employee is eligible for Federal Thrift Savings Plan (TSP) Catch Up Contributions program';
1348 Raise Nothing_to_do;
1349 End If;
1350
1351 --dbms_output.put_line('Electable choice id ' || l_elig_per_elctbl_chc_id);
1352 --dbms_output.put_line('PER in LER ID ' || l_per_in_ler_id);
1353 --dbms_output.put_line('opt val ' || p_opt_val);
1354
1355 for get_enrt_rt_id in c_get_enrt_rt_id loop
1356 l_enrt_rt_id := get_enrt_rt_id.enrt_rt_id;
1357 exit;
1358 End Loop;
1359 If l_enrt_rt_id is null Then
1360 --dbms_output.put_line('No Electable rate id ');
1361 --Bug # 9329643 Modified SSN to Emp No
1362 l_err_msg := 'Name :'|| l_name||' Emp No : '||l_employee_number|| ' :TSP Catch Up value is outside IRS limits' ;
1363 Raise Nothing_to_do;
1364 End If;
1365 --dbms_output.put_line('enrt rate id ' || l_enrt_rt_id);
1366 ben_election_information.election_information
1367 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
1368 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
1369 ,p_effective_date => l_effective_date
1370 ,p_enrt_mthd_cd => 'E'
1371 ,p_enrt_bnft_id => l_enrt_bnft_id
1372 ,p_enrt_rt_id1 => l_enrt_rt_id
1373 ,p_rt_val1 => p_opt_val
1374 ,p_rt_strt_dt1 => l_effective_date
1375 ,p_rt_end_dt1 => hr_api.g_eot
1376 ,p_prtt_rt_val_id1 => l_prtt_rt_val_id1
1377 ,p_prtt_rt_val_id2 => l_prtt_rt_val_id2
1378 ,p_prtt_rt_val_id3 => l_prtt_rt_val_id3
1379 ,p_prtt_rt_val_id4 => l_prtt_rt_val_id4
1380 ,p_prtt_rt_val_id5 => l_prtt_rt_val_id5
1381 ,p_prtt_rt_val_id6 => l_prtt_rt_val_id6
1382 ,p_prtt_rt_val_id7 => l_prtt_rt_val_id7
1383 ,p_prtt_rt_val_id8 => l_prtt_rt_val_id8
1384 ,p_prtt_rt_val_id9 => l_prtt_rt_val_id9
1385 ,p_prtt_rt_val_id10 => l_prtt_rt_val_id10
1386 ,p_enrt_cvg_strt_dt => p_effective_date
1387 ,p_enrt_cvg_thru_dt => hr_api.g_eot
1388 ,p_datetrack_mode => 'INSERT'
1389 ,p_suspend_flag => l_suspend_flag
1390 ,p_effective_start_date => l_esd
1391 ,p_effective_end_date => l_eed
1392 ,p_object_version_number => l_ovn
1393 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
1394 ,p_business_group_id => p_business_group_id
1395 ,p_dpnt_actn_warning => l_Boolean
1396 ,p_bnf_actn_warning => l_Boolean
1397 ,p_ctfn_actn_warning => l_Boolean
1398 );
1399 --dbms_output.put_line('Enrollment Result id ' || l_prtt_enrt_rslt_id);
1400 Exception
1401 When ben_Enrt_Exists Then
1402 null;
1403 When Nothing_to_do Then
1404 rollback;
1405 ghr_mto_int.log_message(null,l_err_msg);
1406 --dbms_output.put_line('Data Issue... Nothing_to_do! ');
1407 When others then
1408 --dbms_output.put_line('Script Failed. Contact Your System Administrator.! ');
1409 rollback;
1410 --Bug # 9329643 Modified SSN to Emp No
1411 l_err_msg := 'Name :'|| l_name||' Emp No : '||l_employee_number;
1412 ghr_mto_int.log_message(null,l_err_msg||' '||sqlerrm(sqlcode));
1413 End ghr_tsp_catchup_migrate;
1414
1415 end ghr_general;