1 PACKAGE BODY ghr_create_ptnl_life_events AS
2 /* $Header: ghcrplle.pkb 120.1 2005/09/07 08:25:14 bgarg noship $ */
3 --
4 --
5
6 PROCEDURE create_ptnl_ler_for_per
7 (p_pa_request_rec in ghr_pa_requests%rowtype
8 )
9
10 is
11
12
13 l_exists boolean := FALSE;
14 l_life_event varchar2(80);
15 l_prior_duty_station ghr_duty_stations_f.duty_station_code%type;
16 l_ptnl_ler_for_per_id number;
17 l_ovn number;
18 l_business_group_id number;
19 l_ler_id number;
20 l_ptnl_le_exists varchar2(1) := 'N';
21 l_prior_work_schedule per_assignment_extra_info.aei_information7%type;
22 l_asg_ei_data per_assignment_extra_info%rowtype;
23 l_session ghr_history_api.g_session_var_type;
24 l_proc varchar2(75) := 'create_ptnl_ler_for_per';
25 l_assignment_id per_all_assignments_f.assignment_id%type;
26 l_hire Boolean;
27
28
29 l_prog_count number;
30 l_plan_count number;
31 l_oipl_count number;
32 l_person_count number;
33 l_plan_nip_count number;
34 l_oipl_nip_count number;
35 l_errbuf varchar2(2000);
36 l_retcode number;
37 l_benefit_action_id number;
38 l_hr_user_type varchar2(20);
39 l_last_separation_date DATE;
40 l_emp_exemp BOOLEAN;
41 l_current_enrollment VARCHAR2(3);
42 l_current_element VARCHAR2(10);
43
44
45
46
47 Cursor c_get_ds_code is
48 select lei.lei_information3,
49 dut.duty_station_code
50 from hr_location_extra_info lei,
51 per_all_assignments_f asg,
52 ghr_duty_stations_v dut
53 where p_pa_request_rec.employee_assignment_id = asg.assignment_id
54 and p_pa_request_rec.effective_date between
55 asg.effective_Start_date and asg.effective_end_date
56 and asg.location_id = lei.location_id
57 and lei.information_type = 'GHR_US_LOC_INFORMATION'
58 and lei.lei_information3 = dut.duty_station_id
59 and p_pa_request_rec.effective_date between
60 dut.effective_start_date and asg.effective_end_date;
61
62
63
64 -- Cursor to get the Business Group Id of the Employee
65 Cursor c_bgp_id is
66 select business_group_id
67 from per_all_people_f
68 where person_id = p_pa_request_rec.person_id
69 and p_pa_request_rec.effective_date
70 between effective_start_date and effective_end_date;
71
72 -- Cursor to get the ler_id for a given ler_name as identified in the procedure below
73
74 Cursor c_ler_id is
75 select ler.ler_id
76 from ben_ler_f ler
77 where ler.business_group_id = l_business_group_id
78 and upper(ler.name) = upper(l_life_event) ;
79
80 -- This name comparision could be replaced by the ler_short_code comparision once available.
81
82 cursor c_chk_ptnl_ler is
83 select 1 from ben_ptnl_ler_for_per
84 where person_id = p_pa_request_rec.person_id
85 and lf_evt_ocrd_dt = p_pa_request_rec.effective_date
86 and ler_id = l_ler_id
87 and PTNL_LER_FOR_PER_STAT_CD = 'UNPROCD'
88 and business_group_id = l_business_group_id;
89
90
91
92 Cursor c_get_current_enrollment is
93 SELECT ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id,
94 'Enrollment',
95 p_pa_request_rec.effective_date) enrollment,
96 decode(upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
97 l_business_group_id,
98 p_pa_request_rec.effective_date)),'HEALTH BENEFITS','After','HEALTH BENEFITS PRE TAX','Pre') ben_type
99
100 FROM pay_element_entries_f eef,
101 pay_element_types_f elt
102 WHERE assignment_id = p_pa_request_rec.employee_assignment_id
103 AND elt.element_type_id = eef.element_type_id
104 AND eef.effective_start_date BETWEEN elt.effective_start_date AND
105 elt.effective_end_date
106 and p_pa_request_rec.effective_date between eef.effective_start_date
107 and eef.effective_end_date
108 AND upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
109 l_business_group_id,
110 p_pa_request_rec.effective_date))
111 IN ('HEALTH BENEFITS','HEALTH BENEFITS PRE TAX') ;
112
113 Cursor c_chk_emp_exemp is
114 select 1
115 from per_all_people_f , per_person_types
116 where person_id = p_pa_request_rec.person_id
117 and trunc (p_pa_request_rec.effective_date - 1) between
118 effective_start_date and effective_end_date
119 and per_all_people_f.business_group_id = per_person_types.business_group_id
120 and per_all_people_f.person_type_id = per_person_types.person_type_id
121 and system_person_type = 'EX_EMP';
122
123 Cursor c_chk_separated is
124 select 1
125 from ghr_pa_requests pa
126 where pa.noa_family_code = 'SEPARATION'
127 and pa.effective_date = p_pa_request_rec.effective_date - 1
128 and pa.person_id = p_pa_request_rec.person_id
129 and exists (select '1'
130 from ghr_pa_history pah
131 where pah.pa_request_id = pa.pa_request_id);
132
133
134
135 Cursor c_prior_pa_details is
136 select pa.duty_station_code, pa.work_schedule, pa.effective_date
137 from ghr_pa_requests pa
138 where pa.noa_family_code = 'SEPARATION'
139 and pa.effective_date < p_pa_request_rec.effective_date
140 and pa.person_id = p_pa_request_rec.person_id
141 and exists (select '1'
142 from ghr_pa_history pah
143 where pah.pa_request_id = pa.pa_request_id)
144 order by pa.effective_date desc ;
145
146
147 begin
148 ghr_history_api.get_g_session_var(l_session);
149 hr_utility.set_location('Entering ' || l_proc,10 );
150
151 FOR bgp_id in c_bgp_id LOOP
152 l_business_group_id := bgp_id.business_group_id;
153 END LOOP;
154
155 -- Check if person exists as 'Ex Employee'
156 l_emp_exemp := FALSE;
157 For i in c_chk_emp_exemp Loop
158 l_emp_exemp := TRUE;
159 END LOOP;
160 -- Added an extra check if Emp is rehired next day
161 If not l_emp_exemp Then
162 for chk_separated in c_chk_separated Loop
163 l_emp_exemp := TRUE;
164 exit;
165 End Loop;
166 End If;
167 --
168 hr_utility.set_location(l_proc,20 );
169 IF l_emp_exemp THEN
170 hr_utility.set_location(l_proc,30 );
171 FOR prior_pa_details IN c_prior_pa_details LOOP
172 l_prior_duty_station := prior_pa_details.duty_station_code;
173 l_prior_work_schedule := prior_pa_details.work_schedule;
174 l_last_separation_date := prior_pa_details.effective_date;
175 EXIT;
176 END LOOP;
177 ELSE
178 hr_utility.set_location(l_proc,40 );
179 FOR get_ds_code IN c_get_ds_code LOOP
180 hr_utility.set_location( l_proc,80 );
181 l_prior_duty_station := get_ds_code.duty_Station_code;
182 END LOOP;
183
184 Ghr_History_Fetch.Fetch_asgei (
185 p_assignment_id => p_pa_request_rec.employee_assignment_id,
186 p_information_type => 'GHR_US_ASG_SF52',
187 p_date_effective => p_pa_request_rec.effective_date,
188 p_asg_ei_data => l_asg_ei_data);
189 l_prior_work_schedule := l_asg_ei_data.aei_information7;
190 END IF;
191
192
193 hr_utility.set_location(l_proc,40 );
194 hr_utility.trace('Family Code ' ||p_pa_request_rec.noa_family_code);
195
196 IF p_pa_request_rec.noa_family_code = 'APP' THEN
197 IF l_session.noa_id_correct is null THEN
198
199 IF p_pa_request_rec.first_noa_code not in ('115','122','130','132','145', '147', '149', '171', '140', '141', '143') THEN
200 l_life_event := 'Initial Opportunity to Enroll' ;
201 ELSIF p_pa_request_rec.first_noa_code IN ('115', '122', '149', '171') THEN
202 if nvl(p_pa_request_rec.tenure,hr_api.g_varchar2) <> '0' Then
203 l_life_event := 'Continued Coverage';
204 end if;
205 ELSIF p_pa_request_rec.first_noa_code IN ('140', '141', '143') THEN
206 IF l_emp_exemp THEN
207 IF l_last_separation_date < (p_pa_request_rec.effective_date - 3) THEN
208 l_life_event := 'Initial Opportunity to Enroll' ;
209 ELSE
210 IF l_prior_duty_station = p_pa_request_rec.duty_station_code THEN
211 IF NVL(l_prior_work_schedule,hr_api.g_varchar2) = NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) THEN
212 l_life_event := 'Continued Coverage';
213 ELSE
214 IF (NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) IN ('P', 'Q', 'S', 'T') AND
215 NVL(l_prior_work_schedule,hr_api.g_varchar2) NOT IN ('P', 'Q', 'S', 'T') )
216 OR
217 (NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) IN ('F', 'G', 'B') AND
218 NVL(l_prior_work_schedule,hr_api.g_varchar2) NOT IN ('F', 'G', 'B') )
219 OR
220 (NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) IN ('I', 'J') AND
221 NVL(l_prior_work_schedule,hr_api.g_varchar2) NOT IN ('I', 'J') ) THEN
222
223 FOR current_enrollment_rec in c_get_current_enrollment LOOP
224 l_current_element := current_enrollment_rec.ben_type;
225 END LOOP;
226 If l_current_element = 'After' Then
227 l_life_event := 'Change in Employment Status Affecting Entitlement to Coverage';
228 Else
229 l_life_event := 'Change in Employment Status Affecting Cost of Insurance' ;
230 End If;
231
232 END IF;
233 END IF;
234
235 ELSIF ((SUBSTR(l_prior_duty_station,1,1) IN ('1','2','3','4','5','6','7','8','9','0') AND
236 SUBSTR(p_pa_request_rec.duty_Station_code,1,1) NOT IN ('1','2','3','4','5','6','7','8','9','0'))
237 OR
238 (SUBSTR(l_prior_duty_station,1,1) NOT IN ('1','2','3','4','5','6','7','8','9','0') AND
239 SUBSTR(p_pa_request_rec.duty_station_code,1,1) IN ('1','2','3','4','5','6','7','8','9','0'))) THEN
240
241 l_life_event := 'Transfer from a post of duty within US to post of duty outside US or vice versa';
242
243 ELSIF SUBSTR(p_pa_request_rec.duty_Station_code,1,1) IN ('1','2','3','4','5','6','7','8','9','0') AND
244 (SUBSTR(l_prior_duty_station,1,2) <> SUBSTR(p_pa_request_rec.duty_station_code,1,2)) THEN
245
246 l_life_event := 'Employee/Family Member Loses coverage under FEHB or Another Group Plan' ;
247
248 END IF;
249 END IF;
250 ELSE
251 l_life_event := 'Initial Opportunity to Enroll' ;
252 END IF;
253
254 ELSIF p_pa_request_rec.first_noa_code IN ('130', '132', '145', '147') THEN
255 IF l_emp_exemp THEN
256 IF l_prior_duty_station = p_pa_request_rec.duty_station_code THEN
257
258 IF NVL(l_prior_work_schedule,hr_api.g_varchar2) = NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) THEN
259 l_life_event := 'Continued Coverage';
260 ELSE
261 IF (NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) IN ('P', 'Q', 'S', 'T') AND
262 NVL(l_prior_work_schedule,hr_api.g_varchar2) NOT IN ('P', 'Q', 'S', 'T') )
263 OR
264 (NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) IN ('F', 'G', 'B') AND
265 NVL(l_prior_work_schedule,hr_api.g_varchar2) NOT IN ('F', 'G', 'B') )
266 OR
267 (NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) IN ('I', 'J') AND
268 NVL(l_prior_work_schedule,hr_api.g_varchar2) NOT IN ('I', 'J') ) THEN
269
270 FOR current_enrollment_rec in c_get_current_enrollment LOOP
271 l_current_element := current_enrollment_rec.ben_type;
272 END LOOP;
273 If l_current_element = 'After' Then
274 l_life_event := 'Change in Employment Status Affecting Entitlement to Coverage';
275 Else
276 l_life_event := 'Change in Employment Status Affecting Cost of Insurance' ;
277 End If;
278
279 END IF;
280 END IF;
281
282 ELSIF ((SUBSTR(l_prior_duty_station,1,1) IN ('1','2','3','4','5','6','7','8','9','0') AND
283 SUBSTR(p_pa_request_rec.duty_Station_code,1,1) NOT IN ('1','2','3','4','5','6','7','8','9','0'))
284 OR
285 (SUBSTR(l_prior_duty_station,1,1) NOT IN ('1','2','3','4','5','6','7','8','9','0') AND
286 SUBSTR(p_pa_request_rec.duty_station_code,1,1) IN ('1','2','3','4','5','6','7','8','9','0'))) THEN
287
288 l_life_event := 'Transfer from a post of duty within US to post of duty outside US or vice versa';
289
290 ELSIF SUBSTR(p_pa_request_rec.duty_Station_code,1,1) IN ('1','2','3','4','5','6','7','8','9','0') AND
291 (SUBSTR(l_prior_duty_station,1,2) <> SUBSTR(p_pa_request_rec.duty_station_code,1,2)) THEN
292
293 l_life_event := 'Employee/Family Member Loses coverage under FEHB or Another Group Plan' ;
294
295 END IF;
296 ELSE
297 l_life_event := 'Continued Coverage';
298 END IF;
299 END IF;
300 END IF;
301 ELSIF p_pa_request_rec.noa_family_code = 'CONV_APP' THEN
302 IF l_session.noa_id_correct is null THEN
303 IF p_pa_request_rec.first_noa_code IN ('540', '541', '543') THEN
304 IF l_emp_exemp THEN
305 IF l_prior_duty_station = p_pa_request_rec.duty_station_code THEN
306
307 IF NVL(l_prior_work_schedule,hr_api.g_varchar2) = NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) THEN
308 l_life_event := 'Continued Coverage';
309 ELSE
310 IF (NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) IN ('P', 'Q', 'S', 'T') AND
311 NVL(l_prior_work_schedule,hr_api.g_varchar2) NOT IN ('P', 'Q', 'S', 'T') )
312 OR
313 (NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) IN ('F', 'G', 'B') AND
314 NVL(l_prior_work_schedule,hr_api.g_varchar2) NOT IN ('F', 'G', 'B') )
315 OR
316 (NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) IN ('I', 'J') AND
317 NVL(l_prior_work_schedule,hr_api.g_varchar2) NOT IN ('I', 'J') ) THEN
318
319 FOR current_enrollment_rec in c_get_current_enrollment LOOP
320 l_current_element := current_enrollment_rec.ben_type;
321 END LOOP;
322 If l_current_element = 'After' Then
323 l_life_event := 'Change in Employment Status Affecting Entitlement to Coverage';
324 Else
325 l_life_event := 'Change in Employment Status Affecting Cost of Insurance' ;
326 End If;
327
328 END IF;
329 END IF;
330
331 ELSIF ((SUBSTR(l_prior_duty_station,1,1) IN ('1','2','3','4','5','6','7','8','9','0') AND
332 SUBSTR(p_pa_request_rec.duty_Station_code,1,1) NOT IN ('1','2','3','4','5','6','7','8','9','0'))
333 OR
334 (SUBSTR(l_prior_duty_station,1,1) NOT IN ('1','2','3','4','5','6','7','8','9','0') AND
335 SUBSTR(p_pa_request_rec.duty_station_code,1,1) IN ('1','2','3','4','5','6','7','8','9','0'))) THEN
336
337 l_life_event := 'Transfer from a post of duty within US to post of duty outside US or vice versa';
338
339 ELSIF SUBSTR(p_pa_request_rec.duty_Station_code,1,1) IN ('1','2','3','4','5','6','7','8','9','0') AND
340 (SUBSTR(l_prior_duty_station,1,2) <> SUBSTR(p_pa_request_rec.duty_station_code,1,2)) THEN
341
342 l_life_event := 'Employee/Family Member Loses coverage under FEHB or Another Group Plan' ;
343
344 END IF;
345 End If;
346 ElsIF p_pa_request_rec.first_noa_code NOT IN ('515', '522', '549', '571') THEN
347
348 FOR current_enrollment_rec in c_get_current_enrollment LOOP
349 l_current_enrollment := current_enrollment_rec.enrollment;
350 END LOOP;
351
352 IF l_current_enrollment = 'Z' THEN
353 l_life_event := 'Initial Opportunity to Enroll' ;
354 END IF;
355 NULL;
356 END IF;
357 END IF;
358
359 ELSIF p_pa_request_rec.noa_family_code = 'SEPARATION' THEN
360 IF l_session.noa_id_correct is null THEN
361 l_life_Event := 'Termination of Appointment' ;
362 END IF;
363
364 ELSIF (SUBSTR(l_prior_duty_station,1,2) <> SUBSTR(p_pa_request_rec.duty_Station_code,1,2) ) THEN
365
366 IF ((SUBSTR(l_prior_duty_station,1,1) IN ('1','2','3','4','5','6','7','8','9','0') AND
367 SUBSTR(p_pa_request_rec.duty_Station_code,1,1) NOT IN ('1','2','3','4','5','6','7','8','9','0'))
368 OR
369 (SUBSTR(l_prior_duty_station,1,1) NOT IN ('1','2','3','4','5','6','7','8','9','0') AND
370 SUBSTR(p_pa_request_rec.duty_station_code,1,1) IN ('1','2','3','4','5','6','7','8','9','0'))) THEN
371
372
373
374 l_life_Event := 'Transfer from a post of duty within US to post of duty outside US or vice versa';
375 ELSIF SUBSTR(l_prior_duty_station,1,1) IN ('1','2','3','4','5','6','7','8','9','0') AND
376 SUBSTR(p_pa_request_rec.duty_Station_code,1,1) IN ('1','2','3','4','5','6','7','8','9','0') THEN
377
378 l_life_Event := 'Employee/Family Member Loses coverage under FEHB or Another Group Plan' ;
379
380 END IF;
381
382 ELSIF NVL(l_prior_work_schedule,hr_api.g_varchar2) <> NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) THEN
383
384 IF (NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) IN ('P', 'Q', 'S', 'T') AND
385 NVL(l_prior_work_schedule,hr_api.g_varchar2) NOT IN ('P', 'Q', 'S', 'T') )
386 OR
387 (NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) IN ('F', 'G', 'B') AND
388 NVL(l_prior_work_schedule,hr_api.g_varchar2) NOT IN ('F', 'G', 'B') )
389 OR
390 (NVL(p_pa_request_rec.work_schedule,hr_api.g_varchar2) IN ('I', 'J') AND
391 NVL(l_prior_work_schedule,hr_api.g_varchar2) NOT IN ('I', 'J') ) THEN
392
393 FOR current_enrollment_rec in c_get_current_enrollment LOOP
394 l_current_element := current_enrollment_rec.ben_type;
395 END LOOP;
396 If l_current_element = 'After' Then
397 l_life_event := 'Change in Employment Status Affecting Entitlement to Coverage';
398 Else
399 l_life_event := 'Change in Employment Status Affecting Cost of Insurance' ;
400 End If;
401
402 END IF;
403 END IF;
404
405 hr_utility.set_location(l_proc,90 );
406 hr_utility.trace('Life Event ' ||l_life_event);
407 -- Now create the relevant potential life event for the Employee
408
409 IF l_life_event is not null THEN
410 hr_utility.set_location( l_proc,140 );
411
412 -- get the Business Group Id
413 FOR bgp_id in c_bgp_id LOOP
414 hr_utility.set_location( l_proc,150 );
415 l_business_group_id := bgp_id.business_group_id;
416 END LOOP;
417
418
419 --get the ler_id
420 for ler_id in c_ler_id loop
421 hr_utility.set_location( l_proc,160 );
422 l_ler_id := ler_id.ler_id;
423 end loop;
424 l_ptnl_le_exists := 'N';
425 for ptnl_ler_id in c_chk_ptnl_ler loop
426 l_ptnl_le_exists := 'Y';
427 exit;
428 end loop;
429 if l_ler_id is not null and l_ptnl_le_exists = 'N' then
430 hr_utility.set_location( l_proc,170 );
431 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
432 (p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id
433 ,p_lf_evt_ocrd_dt => p_pa_request_rec.effective_date
434 ,p_ptnl_ler_for_per_stat_cd => 'UNPROCD'
435 ,p_ler_id => l_ler_id
436 ,p_person_id => p_pa_request_rec.person_id
437 ,p_business_group_id => l_business_group_id
441 );
438 ,p_unprocd_dt => p_pa_request_rec.effective_date
439 ,p_object_version_number => l_ovn
440 ,p_effective_date => p_pa_request_rec.effective_date
442 ELSE
443 hr_utility.set_location( l_proc,180 );
444 -- Should this be an error condition. For now leaving it at null
445 null;
446 END IF;
447 END IF;
448 hr_utility.set_location('Leaving ' || l_proc ,200 );
449 END create_ptnl_ler_for_per;
450
451
452 PROCEDURE create_ptnl_tsp_ler_for_per
453 (p_pa_request_rec in ghr_pa_requests%rowtype) as
454
455 l_session ghr_history_api.g_session_var_type;
456 l_proc Varchar2(75) := 'create_ptnl_tsp_ler_for_per';
457 Begin
458 hr_utility.set_location('Entering ' || l_proc,5 );
459 hr_utility.set_location('Leaving ' || l_proc ,500 );
460
461 End create_ptnl_tsp_ler_for_per;
462
463 end ghr_create_ptnl_life_events;
[Home] [Help]