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