1 PACKAGE BODY ghr_formula_functions AS
2 /* $Header: ghforfun.pkb 120.12 2011/04/21 11:42:01 vmididho noship $ */
3
4 --
5 -- Package Variables
6 --
7 g_package varchar2(100) := 'ghr_formula_functions.';
8 g_old_tsp_status Varchar2(1);
9 g_new_tsp_status Varchar2(1);
10 --
11 --
12 --
13
14
15 FUNCTION get_plan_eligibility(
16 p_business_group_id in number
17 ,p_asg_id in number
18 ,p_effective_date in date
19 ,p_pl_id in number
20 )
21 RETURN varchar2 is
22
23 Cursor c_get_primary_address(l_person_id in Number) is
24 select style,region_2
25 from per_addresses_v
26 where person_id = l_person_id
27 and primary_flag = 'Y';
28
29 Cursor c_check_if_nationwide_plan(l_plan_code in varchar2) is
30 select distinct 'N'
31 from ghr_plan_service_areas_f
32 where plan_short_code = l_plan_code
33 and p_effective_date between effective_start_date and effective_end_date;
34
35 Cursor c_get_plan_duty_station(l_plan_code in varchar2
36 ,l_ds_code in Varchar2) Is
37 select 'Y'
38 from ghr_plan_service_areas_f
39 where plan_short_code = l_plan_code
40 and ds_state_code = l_ds_code
41 and p_effective_date between effective_start_date and effective_end_date;
42
43 Cursor c_get_plan_state(l_plan_code in varchar2,l_state_code in Varchar2) Is
44 select 'Y'
45 from ghr_plan_service_areas_f
46 where plan_short_code = l_plan_code
47 and state_short_name = l_state_code
48 and p_effective_date between effective_start_date and effective_end_date;
49 -------------------------------------------------------------------------------
50 --
51 -- Cursor modified for Payroll Integration
52 --
53 Cursor c_get_elements
54 is
55 select element_name
56 from pay_element_types_f elt
57 where element_type_id in
58 (select element_type_id
59 from pay_element_links_f
60 where element_link_id in
61 (select element_link_id
62 from pay_element_entries_f
63 where assignment_id = p_asg_id
64 and p_effective_date between effective_start_date and effective_end_date)
65 and p_effective_date between effective_start_date and effective_end_date)
66 and upper(element_name) =
67 upper(pqp_fedhr_uspay_int_utils.return_new_element_name
68 ('Health Benefits Pre tax',p_business_group_id,p_effective_date,NULL))
69 and p_effective_date between effective_start_date and effective_end_date
70 and (elt.business_group_id is null or elt.business_group_id= p_business_group_id );
71 --
72 -- Added business_group_id stripping for Payroll Integration
73 --
74 -------------------------------------------------------------------------------
75 --
76 -- Cursor modified for Payroll Integration
77 --
78 Cursor c_get_elements_health
79 is
80 select element_name
81 from pay_element_types_f elt
82 where element_type_id in
83 (select element_type_id
84 from pay_element_links_f
85 where element_link_id in
86 (select element_link_id
87 from pay_element_entries_f
88 where assignment_id = p_asg_id
89 and p_effective_date between effective_start_date and effective_end_date)
90 and p_effective_date between effective_start_date and effective_end_date)
91 and upper(element_name) =
92 upper(pqp_fedhr_uspay_int_utils.return_new_element_name
93 ('Health Benefits',p_business_group_id,p_effective_date,NULL))
94 and p_effective_date between effective_start_date and effective_end_date
95 and (elt.business_group_id is null or elt.business_group_id= p_business_group_id);
96 --
97 -- Added business_group_id stripping for Payroll Integration
98 --
99 l_procedure_name varchar2(100);
100 v_location_id number;
101 v_element_name varchar2(240);
102 v_element_name_health varchar2(240);
103 v_duty_station_code varchar2(9);
104 v_duty_station_desc varchar2(126);
105 v_locality_pay_area varchar2(100);
106 v_locality_pay_area_percentage number;
107 v_ds_state_code varchar2(2);
108 v_plan_short_code varchar2(30);
109 v_eligible varchar2(1);
110 v_person_id number;
111 v_address_style varchar2(30);
112 v_region_2 varchar2(120);
113 v_cnt number;
114 v_exists varchar2(1);
115 nationwide_plan varchar2(1);
116
117 BEGIN
118 l_procedure_name := g_package || 'get_plan_eligibility';
119 --hr_utility.set_location('Entering:'|| l_procedure_name, 10);
120 --hr_utility.trace_on(1,'BG');
121
122 /* Get person id and location id */
123
124 -- change this to cursor
125 select asg.person_id,
126 asg.location_id
127 into v_person_id,
128 v_location_id
129 from per_all_people_f per,
130 per_assignments_f asg
131 where asg.assignment_id = p_asg_id
132 and asg.business_group_id = p_business_group_id
133 and p_effective_date between asg.effective_start_date and asg.effective_end_date
134 and per.person_id = asg.person_id
135 and per.business_group_id = p_business_group_id
136 and p_effective_date between per.effective_start_date and per.effective_end_date;
137
138 hr_utility.set_location(l_procedure_name,20);
139 hr_utility.trace('v_person id = ' ||v_person_id );
140 hr_utility.trace('v_location id = ' ||v_location_id );
141
142 /* get plan short code */
143 select short_code into v_plan_short_code
144 from ben_pl_f
145 where pl_id = p_pl_id
146 and p_effective_date between effective_start_date and effective_end_date;
147
148 hr_utility.set_location(l_procedure_name,30);
149 hr_utility.trace('v_plan_short_code = ' || v_plan_short_code);
150
151
152 -- with june 2005 deliverable, there would be only plan Decline Coverage wuth short code ZZ
153 --If v_plan_short_code in ('DCA','DCP') Then
154 If v_plan_short_code in ('ZZ') Then
155 /* Decline Coverage */
156 hr_utility.set_location(l_procedure_name,40);
157 v_eligible := 'Y';
158 Else
159 hr_utility.set_location(l_procedure_name,50);
160 /* Check If nation wide plan */
161 Open c_check_if_nationwide_plan(v_plan_short_code);
162 Fetch c_check_if_nationwide_plan into nationwide_plan;
163 If c_check_if_nationwide_plan%NOTFOUND Then
164 nationwide_plan := 'Y';
165 End If;
166 if nationwide_plan = 'Y' Then
167 v_eligible := 'Y';
168 ElsE
169 hr_utility.set_location(l_procedure_name,60);
170 /* get duty station code */
171 ghr_per_sum.get_duty_station_details
172 (v_location_id,
173 p_effective_date,
174 v_duty_station_code,
175 v_duty_station_desc ,
176 v_locality_pay_area,
177 v_locality_pay_area_percentage
178 );
179
180 hr_utility.set_location(l_procedure_name,70);
181 hr_utility.trace('v_duty_station_code = ' || v_duty_station_code);
182
183 v_ds_state_code := substr(v_duty_station_code,1,2);
184
185 IF substr(v_ds_state_code,1,1) between 'A' and 'Z' and
186 substr(v_ds_state_code,2,1) <> 'Q' Then /* Foreign Duty Station */
187 hr_utility.set_location(l_procedure_name,80);
188 v_eligible := 'N';
189 ELSE
190 hr_utility.set_location(l_procedure_name,90);
191
192 Open c_get_plan_duty_station(v_plan_short_code,v_ds_state_code);
193 Fetch c_get_plan_duty_station into v_exists;
194 if c_get_plan_duty_station%NOTFOUND Then
195 v_exists := 'N';
196 End If;
197 If v_exists = 'Y' Then
198 v_eligible := 'Y';
199 ELSE
200 hr_utility.set_location(l_procedure_name,100);
201 /* Check for address style and value for region2 (state code)*/
202 /* of primary address */
203 Open c_get_primary_address(v_person_id);
204 Fetch c_get_primary_address into v_address_style,v_region_2;
205 If c_get_primary_address%NOTFOUND then
206 v_eligible := 'N';
207 Else
208 hr_utility.set_location(l_procedure_name,110);
209 hr_utility.trace('v_address_style = ' || v_address_style);
210 --Bug# 4725292 Included US_GLB_FED
211 IF v_address_style in ('US','US_GLB','US_GLB_FED') Then /* US STYLE ADDRESS*/
212 hr_utility.set_location(l_procedure_name,120);
213 IF v_region_2 is null Then
214 v_eligible := 'N';
215 ELSE
216 hr_utility.set_location(l_procedure_name,130);
217 Open c_get_plan_state(v_plan_short_code,v_region_2);
218 Fetch c_get_plan_state into v_exists;
219 If c_get_plan_state%NOTFOUND then
220 v_eligible := 'N';
221 Else
222 v_eligible := 'Y';
223 End If;
224 End If;
225 Else
226 v_eligible := 'N';
227 End If;
228 End If;
229 End If;
230 End If;
231 End If;
232 End If;
233 If c_check_if_nationwide_plan%ISOPEN then
234 Close c_check_if_nationwide_plan;
235 End If;
236 If c_get_primary_address%ISOPEN then
237 Close c_get_primary_address;
238 End If;
239 If c_get_plan_duty_station%ISOPEN then
240 Close c_get_plan_duty_station;
241 End If;
242 If c_get_plan_state%ISOPEN then
243 Close c_get_plan_state;
244 End If;
245 hr_utility.trace('v_eligible = ' || v_eligible);
246 hr_utility.set_location(' Leaving:'||l_procedure_name, 1000);
247 Return v_eligible;
248 Exception
249 when others then
250 hr_utility.set_location(' Leaving:'||l_procedure_name, 110);
251 If c_check_if_nationwide_plan%ISOPEN then
252 Close c_check_if_nationwide_plan;
253 End If;
254 if c_get_primary_address%ISOPEN Then
255 CLOSE c_get_primary_address;
256 End If;
257 if c_get_plan_duty_station%ISOPEn Then
258 hr_utility.set_location(' Leaving:'||l_procedure_name, 120);
259 CLOSE c_get_plan_duty_station;
260 End If;
261 if c_get_plan_state%ISOPEN Then
262 hr_utility.set_location(' Leaving:'||l_procedure_name, 130);
263 CLOSE c_get_plan_state;
264 End If;
265 if c_get_elements%ISOPEN Then
266 hr_utility.set_location(' Leaving:'||l_procedure_name, 140);
267 CLOSE c_get_elements;
268 End If;
269
270 Return 'N';
271
272 End get_plan_eligibility;
273
274
275 Function get_plan_short_code ( p_business_group_id in Number
276 ,p_effective_date in Date
277 ,p_pl_id in Number)
278 RETURN varchar2 is
279
280 v_pln_short_code ben_pl_f.short_code%type;
281 l_procedure_name varchar2(100);
282
283 Cursor C1 is
284 select short_code from ben_pl_f
285 where pl_id = p_pl_id
286 and p_effective_date between effective_start_date and effective_end_date;
287 Begin
288 l_procedure_name := g_package || 'get_plan_short_code';
289 hr_utility.set_location('Entering:'|| l_procedure_name, 10);
290 for i in c1 loop
291 v_pln_short_code := i.short_code;
292 End Loop;
293 hr_utility.trace('v_pln_short_code = ' || v_pln_short_code);
294 /*If v_pln_short_code in ('DCA','DCP') then -- Decline Coverage
295 v_pln_short_code := null;
296 End If; */
297 hr_utility.set_location('Leaving:'|| l_procedure_name, 20);
298 Return v_pln_short_code;
299 Exception
300 when others then
301 hr_utility.set_location(' Leaving:'||l_procedure_name, 30);
302 hr_utility.trace('Error ' || sqlerrm(sqlcode));
303 End get_plan_short_code;
304
305
306 FUNCTION get_option_short_code(
307 p_business_group_id in number
308 ,p_effective_date in date
309 ,p_opt_id in number)
310 RETURN varchar2 is
311
312 v_opt_short_code ben_opt_f.short_code%type;
313 l_procedure_name varchar2(100);
314
315 Cursor C1 is
316 select short_code from ben_opt_f
317 where opt_id = p_opt_id
318 and p_effective_date between effective_start_date and effective_end_date;
319 Begin
320 l_procedure_name := g_package || '.get_option_short_code';
321 hr_utility.set_location('Entering:'|| l_procedure_name, 10);
322 hr_utility.trace('p_opt_id = ' || p_opt_id);
323 hr_utility.trace('p_effective_date = ' || p_effective_date);
324 If p_opt_id = -1 Then /* Decline Coverage */
325 v_opt_short_code := 'Y';
326 Else
327 for i in c1 loop
328 v_opt_short_code := i.short_code;
329 End Loop;
330 End If;
331 hr_utility.trace('v_opt_short_code = ' || v_opt_short_code);
332 hr_utility.set_location('Leaving:'|| l_procedure_name, 20);
333 Return substr(v_opt_short_code,1,1);
334 Exception
335 when others then
336 hr_utility.set_location(' Leaving:'||l_procedure_name, 30);
337 hr_utility.trace('Error ' || sqlerrm(sqlcode));
338 End get_option_short_code;
339
340 function chk_person_type(
341 p_business_group_id in Number,p_assignment_id in number
342 )
343 RETURN varchar2 is
344 l_procedure_name varchar2(100);
345 l_person_type per_person_types.system_person_type%type;
346 l_person_id per_people_f.person_id%type;
347 l_session_date fnd_sessions.effective_date%type;
348 cursor c_get_session_date is
349 select trunc(effective_date) session_date
350 from fnd_sessions
351 where session_id = (select userenv('sessionid') from dual);
352 cursor c_per_id is
353 select person_id from
354 per_assignments_f
355 where assignment_id = p_assignment_id
356 and business_group_id = p_business_group_id
357 and primary_flag = 'Y'
358 and assignment_type <> 'B'
359 and l_session_date
360 between effective_start_date
361 and effective_end_date;
362 cursor get_person_type is
363 SELECT pty.system_person_type
364 FROM per_people_f ppf, per_person_types pty
365 WHERE ppf.person_id = l_person_id
366 AND l_session_date
367 BETWEEN ppf.effective_start_date AND ppf.effective_end_date
368 AND ppf.person_type_id = pty.person_type_id
369 AND pty.business_group_id = p_business_group_id
370 AND pty.active_flag = 'Y';
371 Begin
372 l_procedure_name := g_package || '.chk_person_type';
373 hr_utility.set_location('Entering:'|| l_procedure_name, 10);
374 -- Get Session Date
375 l_session_date := trunc(sysdate);
376 for ses_rec in c_get_session_date loop
377 l_session_date := ses_rec.session_date;
378 end loop;
379 hr_utility.set_location('Entering:'|| l_procedure_name, 11);
380 hr_utility.set_location('p_assignment_id '||p_assignment_id,11);
381 hr_utility.set_location('p_bg_id '||p_business_group_id,11);
382 -- Get Person id for given assignment id and BG id
383 for c_per_rec in c_per_id loop
384 l_person_id := c_per_rec.person_id;
385 exit;
386 end loop;
387 hr_utility.set_location('l_person_id '||l_person_id,12);
388 -- Find whether the person is a employee or not
389 IF l_person_id is not null then
390 FOR c_person_type in get_person_type loop
391 l_person_type := c_person_type.system_person_type;
392 END LOOP;
393 hr_utility.set_location('l_person_type '||l_person_type,15);
394 IF l_person_type = 'EMP' then
395 return 'Y';
396 ELSE
397 return 'N';
398 END IF;
399 END IF;
400 return 'N';
401 hr_utility.set_location('Leaving:'|| l_procedure_name, 20);
402 Exception
403 when others then
404 hr_utility.set_location(' Leaving:'||l_procedure_name, 30);
405 hr_utility.trace('Error ' || sqlerrm(sqlcode));
406 End chk_person_type;
407
408 function get_retirement_plan( p_business_group_id in Number
409 ,p_asg_id in Number
410 ,p_effective_date in Date )
411 RETURN VARCHAR2 Is
412
413 l_proc_name varchar2(100);
414 v_retirement_plan VARCHAR2(50);
415 l_multi_error_flag Boolean;
416
417 Begin
418 l_proc_name := g_package || '.get_retirement_plan';
419 hr_utility.set_location('Entering ' ||l_proc_name,10);
420 hr_utility.trace('p_asg_id = ' ||p_asg_id );
421
422 -- Get Retirement Plan
423 ghr_api.retrieve_element_entry_value
424 (p_element_name => 'Retirement Plan'
425 ,p_input_value_name => 'Plan'
426 ,p_assignment_id => p_asg_id
427 ,p_effective_date => p_effective_date
428 ,p_value => v_retirement_plan
429 ,p_multiple_error_flag => l_multi_error_flag);
430
431 hr_utility.set_location(l_proc_name,20);
432 hr_utility.trace('v_retirement_plan = ' ||v_retirement_plan );
433
434
435 hr_utility.set_location('Leaving ' ||l_proc_name,100);
436 Return v_retirement_plan;
437 Exception
438 when others then
439 hr_utility.set_location(' Leaving:'||l_proc_name, 110);
440 Return null;
441 End get_retirement_plan;
442
443 function get_employee_tsp_eligibility( p_business_group_id in Number
444 ,p_asg_id in Number
445 ,p_effective_date in Date )
446 RETURN VARCHAR2 Is
447
448 l_proc_name varchar2(100);
449 v_eligible varchar2(1);
450 v_retirement_plan pay_element_entry_values_f.screen_entry_value%type;
451 v_effective_start_date pay_element_entry_values_f.effective_start_date%type;
452 v_per_system_status per_assignment_status_types.per_system_status%type;
453 v_annuitant_indicator varchar2(50);
454 v_asg_ei_data per_assignment_extra_info%rowtype;
455
456 Begin
457 l_proc_name := g_package || '.get_employee_tsp_eligibility';
458 hr_utility.set_location('Entering ' ||l_proc_name,10);
459 hr_utility.trace('p_asg_id = ' ||p_asg_id );
460
461 v_retirement_plan := ghr_formula_functions.get_retirement_plan( p_business_group_id
462 ,p_asg_id
463 ,p_effective_date);
464 hr_utility.set_location(l_proc_name,20);
465 hr_utility.trace('v_retirement_plan = ' ||v_retirement_plan);
466
467 v_eligible := 'N';
468 If v_retirement_plan is null Then
469 v_eligible := 'N';
470 Elsif v_retirement_plan in ('C','E','G','K','L','M','N','P','R','T','1','3','6','D','F','H','W') Then
471 v_eligible := 'Y';
472 Elsif v_retirement_plan in ('2','4','5') then
473 ghr_history_fetch.fetch_asgei(p_assignment_id => p_asg_id,
474 p_information_type => 'GHR_US_ASG_SF52',
475 p_date_effective => p_effective_date,
476 p_asg_ei_data => v_asg_ei_data);
477 v_annuitant_indicator := v_asg_ei_data.aei_information5;
478 hr_utility.trace('v_annuitant_indicator = ' ||v_annuitant_indicator);
479 If v_annuitant_indicator not in ('2','3','9') then
480 v_eligible := 'Y';
481 Else
482 v_eligible := 'N';
483 End If;
484 Else
485 v_eligible := 'N';
486 End If;
487 hr_utility.set_location('Leaving ' ||l_proc_name,100);
488 hr_utility.trace('v_eligible = ' ||v_eligible );
489 Return v_eligible;
490 Exception
491 when others then
492 hr_utility.set_location(' Leaving:'||l_proc_name, 110);
493 hr_utility.trace('Error: ' ||sqlerrm(sqlcode));
494 Return 'N';
495 End get_employee_tsp_eligibility;
496
497 function check_if_emp_csrs( p_business_group_id in Number
498 ,p_asg_id in Number
499 ,p_effective_date in Date )
500 RETURN VARCHAR2 is
501
502 l_proc_name varchar2(100);
503 v_eligible varchar2(1);
504 v_effective_start_date pay_element_entry_values_f.effective_start_date%type;
505 v_retirement_plan pay_element_entry_values_f.screen_entry_value%type;
506 Begin
507 l_proc_name := g_package || '.check_if_emp_csrs';
508 hr_utility.set_location('Entering '||l_proc_name,10);
509 hr_utility.trace('p_asg_id = ' ||p_asg_id );
510 v_retirement_plan := ghr_formula_functions.get_retirement_plan( p_business_group_id
511 ,p_asg_id
512 ,p_effective_date);
513 hr_utility.set_location(l_proc_name,20);
514 hr_utility.trace('ret plan = ' ||v_retirement_plan);
515
516 If v_retirement_plan in ('1','3','6','C','E','F','G','H','R','T','W') Then
517 v_eligible := 'Y';
518 ElsIf v_retirement_plan in ('2','4','5') then
519 v_eligible := 'Y';
520 Else
521 v_eligible := 'N';
522 End If;
523 hr_utility.set_location('Leaving '||l_proc_name,10);
524 hr_utility.trace('v_eligible = ' ||v_eligible );
525 Return v_eligible;
526 Exception
527 when others then
528 hr_utility.set_location('Exception Leaving:'||l_proc_name, 110);
529 Return 'N';
530 End check_if_emp_csrs;
531 ---------------
532
533 Function get_emp_annual_salary(p_assignment_id in Number,
534 p_effective_date in Date
535 )
536 return Number is
537
538 l_proc_name varchar2(100);
539
540 Begin
541 l_proc_name := g_package|| 'get_emp_annual_salary';
542 hr_utility.set_location('Entering '||l_proc_name,10);
543 hr_utility.set_location('Leaving '||l_proc_name,10);
544
545 return 1;
546 Exception
547 When Others Then
548 hr_utility.set_location('Exception Leaving ' ||l_proc_name,200);
549 hr_utility.trace('Error ' || sqlerrm(sqlcode));
550 End get_emp_annual_salary;
551
552 -- Function to validate tsp amount as entered by the user
553 FUNCTION ghr_tsp_amount_validation(
554 p_business_group_id in number
555 ,p_asg_id in number
556 ,p_effective_date in date
557 ,p_pgm_id in number
558 ,p_pl_id in number
559 )
560 Return Varchar2 is
561
562
563 l_proc_name varchar2(100);
564 l_result Varchar2(1);
565 l_person_id per_all_people_f.person_id%type;
566 l_tsp_amount Number;
567 l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type;
568 l_payroll_id pay_payrolls_f.payroll_id%type;
569 l_enrt_cvg_strt_dt Date;
570 l_effective_date Date;
571
572 Cursor c_get_person_id is
573 Select person_id,payroll_id
574 from per_all_assignments_f
575 where assignment_id = p_asg_id
576 and trunc(p_effective_date) between effective_start_date and effective_end_date;
577
578 Cursor c_get_prtt_enrt_rslt_id is
579 select enrt_cvg_strt_dt,rt_val
580 from ben_prtt_enrt_rslt_f perf , ben_prtt_rt_val prv
581 where perf.person_id = l_person_id
582 and perf.pgm_id = p_pgm_id
583 and perf.pl_id = p_pl_id
584 and perf.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
585 and trunc(l_effective_date) between perf.effective_start_date and perf.effective_end_date
586 and perf.enrt_cvg_thru_dt = hr_api.g_eot
587 and prv.rt_end_dt = hr_api.g_eot
588 and perf.prtt_enrt_rslt_stat_cd is null;
589
590 Begin
591 l_proc_name := g_package|| 'ghr_tsp_amount_validation';
592 l_result := 'Y';
593 hr_utility.set_location('Entering ' ||l_proc_name,10);
594
595 -- get person_id
596 For get_person_id in c_get_person_id loop
597 l_person_id := get_person_id.person_id;
598 l_payroll_id := get_person_id.payroll_id;
599 Exit;
600 End Loop;
601 hr_utility.set_location(l_proc_name,20);
602 hr_utility.trace('l_person_id = ' ||l_person_id );
603 hr_utility.trace('p_pgm_id = ' ||p_pgm_id );
604 hr_utility.trace('p_pl_id = ' ||p_pl_id );
605 hr_utility.trace('p_effective_date = ' ||p_effective_date );
606 --dbms_output.put_line('per id ' ||l_person_id||' pl id:' ||p_pl_id||' pgmid:'||p_pgm_id);
607
608 ghr_history_api.get_session_date(l_effective_date);
609 hr_utility.trace('l_effective_date = ' ||l_effective_date );
610 --Get Prtt Enrt Rslt id
611 For get_prtt_enrt_rslt_id in c_get_prtt_enrt_rslt_id loop
612 l_enrt_cvg_strt_dt := get_prtt_enrt_rslt_id.enrt_cvg_strt_dt;
613 l_tsp_amount := get_prtt_enrt_rslt_id.rt_val;
614 exit;
615 End loop;
616 hr_utility.set_location(l_proc_name,30);
617 hr_utility.trace('l_enrt_cvg_strt_dt = ' ||l_enrt_cvg_strt_dt);
618 hr_utility.trace('l_tsp_amount = ' ||l_tsp_amount );
619 --dbms_output.put_line('AMOUNT ' ||l_tsp_amount);
620
621 l_tsp_amount := nvl(l_tsp_amount,0);
622
623 hr_utility.trace('l_tsp_amount ' ||l_tsp_amount );
624 If l_tsp_amount = 0 Then
625 l_result := 'N';
626 Elsif l_tsp_amount > 0 Then
627 If l_enrt_cvg_strt_dt between to_date('01/12/2004','dd/mm/yyyy')
628 and to_date('30/11/2005','dd/mm/yyyy') Then
629 If l_tsp_amount <= 14000 Then
630 l_result := 'Y';
631 Else
632 l_result := 'N';
633 End If;
634 Elsif l_enrt_cvg_strt_dt between to_date('01/12/2005','dd/mm/yyyy')
635 and to_date('30/11/2006','dd/mm/yyyy') Then
636 If l_tsp_amount <= 15000 Then
637 l_result := 'Y';
638 Else
639 l_result := 'N';
640 End If;
641 Else
642 l_result := 'Y';
643 End If;
644 End If;
645 hr_utility.set_location('Leaving '||l_proc_name,80);
646 hr_utility.trace('l_result ' ||l_result );
647 return l_result;
648 Exception
649 When others Then
650 hr_utility.set_location('Exception Leaving ' ||l_proc_name,200);
651 hr_utility.trace('Error ' || sqlerrm(sqlcode));
652 Return 'N';
653 End ghr_tsp_amount_validation;
654
655
656
657 -- Function to validate tsp percentage contributions as entered by the user
658 FUNCTION ghr_tsp_percentage_validation(
659 p_business_group_id in number
660 ,p_asg_id in number
661 ,p_effective_date in date
662 ,p_pgm_id in number
663 ,p_pl_id in number
664 )
665 Return Varchar2 is
666
667 l_proc_name varchar2(100);
668 l_result Varchar2(1);
669 l_person_id per_all_people_f.person_id%type;
670 l_tsp_percentage Number;
671 l_enrt_cvg_strt_dt Date;
672 l_emp_csrs Varchar2(1);
673 Nothing_to_do Exception;
674 l_effective_date Date;
675
676 Cursor c_get_person_id is
677 Select person_id
678 from per_all_assignments_f
679 where assignment_id = p_asg_id
680 and trunc(p_effective_date) between effective_start_date and effective_end_date;
681
682 Cursor c_get_tsp_percentage is
683 select rt_val,enrt_cvg_strt_dt
684 from ben_prtt_rt_val , ben_prtt_enrt_rslt_f
685 where ben_prtt_rt_val.prtt_enrt_rslt_id = ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id
686 and ben_prtt_enrt_rslt_f.person_id = l_person_id
687 and ben_prtt_enrt_rslt_f.pgm_id = p_pgm_id
688 and ben_prtt_enrt_rslt_f.pl_id = p_pl_id
689 and trunc(l_effective_date) between ben_prtt_enrt_rslt_f.effective_start_date
690 and ben_prtt_enrt_rslt_f.effective_end_date
691 and ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt = hr_api.g_eot
692 and ben_prtt_rt_val.rt_end_dt = hr_api.g_eot
693 and ben_prtt_enrt_rslt_f.prtt_enrt_rslt_stat_cd is null;
694
695 Begin
696 l_proc_name := g_package|| 'ghr_tsp_percentage_validation';
697 l_result := 'Y';
698 hr_utility.set_location('Entering ' ||l_proc_name,10);
699
700 -- get person_id
701 For get_person_id in c_get_person_id loop
702 l_person_id := get_person_id.person_id;
703 Exit;
704 End Loop;
705 hr_utility.set_location(l_proc_name,20);
706 hr_utility.trace('l_person_id = ' ||l_person_id );
707 hr_utility.trace('p_pgm_id = ' ||p_pgm_id );
708 hr_utility.trace('p_pl_id = ' ||p_pl_id );
709 hr_utility.trace('p_effective_date = ' ||p_effective_date );
710 --dbms_output.put_line('per id ' ||l_person_id||' pl id:' ||p_pl_id||' pgmid:'||p_ pgm_id);
711
712 ghr_history_api.get_session_date(l_effective_date);
713 hr_utility.trace('l_effective_date = ' ||l_effective_date );
714
715 -- Get TSP Percentage entered by user.
716 For get_tsp_percentage in c_get_tsp_percentage loop
717 l_tsp_percentage := get_tsp_percentage.rt_val;
718 l_enrt_cvg_strt_dt := get_tsp_percentage.enrt_cvg_strt_dt;
719 exit;
720 End loop;
721 hr_utility.set_location(l_proc_name,30);
722 hr_utility.trace('l_enrt_cvg_strt_dt = ' ||l_enrt_cvg_strt_dt);
723 hr_utility.trace('l_tsp_percentage = ' ||l_tsp_percentage );
724 --dbms_output.put_line('amount ' ||l_tsp_percentage);
725 --dbms_output.put_line ('enrt cvg start date ' ||l_enrt_cvg_strt_dt);
726
727 l_tsp_percentage := nvl(l_tsp_percentage,0);
728 hr_utility.trace('l_tsp_percentage = ' ||l_tsp_percentage );
729 If nvl(l_tsp_percentage,0) = 0 Then
730 hr_utility.set_location(l_proc_name,50);
731 l_result := 'N';
732 Else
733 hr_utility.set_location(l_proc_name,60);
734 -- Check retirement Plan for an employee
735 l_emp_csrs := check_if_emp_csrs( p_business_group_id,p_asg_id,l_enrt_cvg_strt_dt );
736 hr_utility.set_location(l_proc_name,70);
737 hr_utility.trace('l_emp_csrs = ' ||l_emp_csrs );
738
739 If l_emp_csrs = 'N' Then
740 If l_enrt_cvg_strt_dt between to_date('01/12/2004','dd/mm/yyyy')
741 and to_date('30/11/2005','dd/mm/yyyy') Then
742 If l_tsp_percentage <= 15 Then
743 l_result := 'Y';
744 Else
745 l_result := 'N';
746 End If;
747 Else
748 l_result := 'Y';
749 End If;
750 ElsIf l_emp_csrs = 'Y' Then
751 If l_enrt_cvg_strt_dt between to_date('01/12/2004','dd/mm/yyyy')
752 and to_date('30/11/2005','dd/mm/yyyy') Then
753 If l_tsp_percentage <= 10 Then
754 l_result := 'Y';
755 Else
756 l_result := 'N';
757 End If;
758 Else
759 l_result := 'Y';
760 End If;
761 End If;
762 End If;
763 hr_utility.set_location('Leaving ' ||l_proc_name,100);
764 hr_utility.trace('l_result = ' ||l_result); return l_result;
765 Exception
766 When Nothing_to_do Then
767 hr_utility.set_location('Exception (NTD) Leaving ' ||l_proc_name,200);
768 Return l_result;
769 When Others Then
770 hr_utility.set_location('Exception Leaving ' ||l_proc_name,210);
771 hr_utility.trace('Error ' || sqlerrm(sqlcode));
772 Return 'N';
773 End ghr_tsp_percentage_validation;
774
775
776
777 Function tsp_open_season_effective_dt (p_business_group_id in Number
778 ,p_asg_id in Number
779 ,p_effective_date in Date
780 ,p_pgm_id in Number)
781 Return date is
782
783 l_proc_name varchar2(100);
784 Begin
785 l_proc_name := g_package|| 'tsp_open_season_effective_date';
786 hr_utility.set_location('Entering '||l_proc_name,10);
787 --dbms_output.put_line(' In procedure ' ||p_effective_date);
788 hr_utility.set_location('Leaving '||l_proc_name,100);
789 Return p_effective_date;
790 Exception
791 When Others Then
792 hr_utility.set_location('Exception Leaving '||l_proc_name,210);
793 hr_utility.trace('Error ' || sqlerrm(sqlcode));
794 Return p_effective_date;
795 End tsp_open_season_effective_dt;
796
797 Function get_emp_elig_date (p_business_group_id in Number
798 ,p_effective_date in Date
799 ,p_asg_id in Number
800 ,p_pgm_id in Number
801 ,p_opt_id in Number)
802 Return Varchar2 is
803
804 l_proc_name varchar2(100);
805 Begin
806 l_proc_name := g_package|| 'get_emp_elig_date';
807 hr_utility.set_location('Entering '||l_proc_name,10);
808 Return p_effective_date;
809 Exception
810 When Others Then
811 hr_utility.set_location('Exception Leaving '||l_proc_name,200);
812 hr_utility.trace('Error ' || sqlerrm(sqlcode));
813 return null;
814 End get_emp_elig_date;
815
816 Function get_tsp_status_date (p_asg_id in Number
817 ,p_effective_date in Date)
818 Return Date is
819
820 l_proc_name varchar2(100);
821 l_tsp_curr_status_date varchar2(50);
822 l_multi_error_flag Boolean;
823 l_effective_date Date;
824 l_pay_start_date Date;
825 l_pay_end_date Date;
826 l_tsp_status_date date;
827
828 Cursor c_pay_period is
829 select start_date,end_date
830 from per_time_periods
831 where payroll_id in
832 (select payroll_id
833 from per_assignments_f
834 where assignment_id = p_asg_id
835 and trunc(p_effective_date) between effective_start_date and effective_end_date)
836 and p_effective_date between start_date and end_date;
837
838 Begin
839 l_proc_name := g_package|| 'get_tsp_status_date';
840 hr_utility.set_location('Entering '||l_proc_name,10);
841 hr_utility.trace('p_effective_date = ' ||p_effective_date );
842 for pay_period in c_pay_period loop
843 l_pay_start_date := pay_period.start_date;
844 l_pay_end_date := pay_period.end_date;
845 exit;
846 end loop;
847 if p_effective_date > l_pay_start_date then
848 l_effective_date := l_pay_end_date + 1;
849 else
850 l_effective_date := l_pay_start_date;
851 End If;
852 hr_utility.trace('l_effective_date ' ||l_effective_date );
853 hr_utility.trace('old_status ' ||g_old_tsp_status );
854 hr_utility.trace('new_status ' ||g_new_tsp_status );
855 if g_old_tsp_status = g_new_tsp_status then
856 ghr_api.retrieve_element_entry_value
857 (p_element_name => 'TSP'
858 ,p_input_value_name => 'Status Date'
859 ,p_assignment_id => p_asg_id
860 ,p_effective_date => p_effective_date - 1
861 ,p_value => l_tsp_curr_status_date
862 ,p_multiple_error_flag => l_multi_error_flag);
863 hr_utility.trace('l_tsp_curr_status_date ' ||l_tsp_curr_status_date );
864 l_tsp_status_date := to_date(l_tsp_curr_status_date,'yyyy/mm/dd hh24:mi:ss');
865 else
866 l_tsp_status_date:= p_effective_date;
867 end if;
868 hr_utility.trace('l_tsp_status_date = ' ||l_tsp_status_date );
869 return l_tsp_status_date;
870 End get_tsp_status_date;
871
872 Function get_tsp_status (p_business_group_id in Number
873 ,p_effective_date in Date
874 ,p_opt_id in Number
875 ,p_asg_id in Number)
876 Return Varchar2 is
877
878 l_proc_name varchar2(100);
879 l_tsp_status Varchar2(60);
880 l_opt_name ben_opt_f.name%type;
881 l_emp_csrs varchar2(1);
882 l_tsp_curr_status varchar2(1);
883 l_val Varchar2(50);
884 l_exists Varchar2(1);
885 l_multi_error_flag Boolean;
886 l_pay_start_date Date;
887 l_pay_end_date Date;
888 l_effective_date Date;
889 l_tsp_curr_agency_date Date;
890 l_tsp_curr_status_date Varchar2(50);
891 l_dt Varchar2(50);
892
893 Cursor c_get_tsp_option is
894 Select name from ben_opt_f
895 where opt_id = p_opt_id
896 and business_group_id = p_business_group_id
897 and trunc(p_effective_date) between effective_start_date and effective_end_date;
898
899 Cursor c_pay_period is
900 select start_date,end_date
901 from per_time_periods
902 where payroll_id in
903 (select payroll_id
904 from per_assignments_f
905 where assignment_id = p_asg_id
906 and trunc(p_effective_date) between effective_start_date and effective_end_date)
907 and p_effective_date between start_date and end_date;
908
909
910 Begin
911 l_proc_name := g_package|| 'get_tsp_status';
912 hr_utility.set_location('Entering '||l_proc_name,10);
913 For get_tsp_option in c_get_tsp_option Loop
914 l_opt_name := get_tsp_option.name;
915 exit;
916 End Loop;
917 hr_utility.trace('l_opt_name = ' ||l_opt_name );
918 -- Check retirement Plan for an employee
919 l_emp_csrs := check_if_emp_csrs( p_business_group_id,p_asg_id,p_effective_date );
920 hr_utility.trace('l_emp_csrs ' ||l_emp_csrs );
921
922 for pay_period in c_pay_period loop
923 l_pay_start_date := pay_period.start_date;
924 l_pay_end_date := pay_period.end_date;
925 exit;
926 end loop;
927 if p_effective_date > l_pay_start_date then
928 l_effective_date := l_pay_end_date + 1;
929 else
930 l_effective_date := l_pay_start_date;
931 End If;
932 hr_utility.trace('l_effective_date ' ||l_effective_date );
933
934 ghr_api.retrieve_element_entry_value
935 (p_element_name => 'TSP'
936 ,p_input_value_name => 'Status'
937 ,p_assignment_id => p_asg_id
938 ,p_effective_date => l_effective_date - 1
939 ,p_value => l_tsp_curr_status
940 ,p_multiple_error_flag => l_multi_error_flag);
941 hr_utility.trace('l_tsp_curr_status ' ||l_tsp_curr_status );
942
943
944 If l_emp_csrs = 'Y' then -- Processing for CSRS employees (only status valid are E,Y and T)
945 hr_utility.set_location('Entering Processing for CSRS '||l_proc_name,20);
946 If (l_opt_name in ('Amount', 'Percentage')) then
947 l_tsp_status := 'Y';
948 Elsif l_opt_name = 'Terminate Contributions' then
949 if l_tsp_curr_status = 'Y' then
950 l_tsp_status := 'T';
951 Else
952 l_tsp_status := l_tsp_curr_status;
953 End If;
954 End if;
955 Else --Processing for FERS Employee
956
957 hr_utility.set_location('Entering Processing for FERS '||l_proc_name,30);
958 hr_utility.trace('l_effective_date ' ||l_effective_date );
959 --get Agency Contribution Date
960 l_tsp_curr_agency_date := get_agency_contrib_date(p_asg_id,l_effective_date);
961 hr_utility.trace('l_tsp_curr_agency_date ' ||l_tsp_curr_agency_date );
962 hr_utility.trace('l_effective_date ' ||l_effective_date );
963 --Begin Bug# 8622486
964 /* If l_tsp_curr_agency_date > l_effective_date then --Valid status are (I,W,S)
965 hr_utility.set_location('Entering Processing for FERS '||l_proc_name,70);
966 If l_opt_name in ('Amount','Percentage') Then
967 l_tsp_status := 'W';
968 Elsif l_opt_name = 'Terminate Contributions' Then
969 if l_tsp_curr_status in ('W','Y') then
970 l_tsp_status := 'S';
971 else
972 l_tsp_status := l_tsp_curr_status;
973 End If;
974 End If;
975 Else*/ --Valid New Status are Y and T
976 --End Bug# 8622486
977 hr_utility.set_location('Entering Processing for FERS '||l_proc_name,80);
978 If l_opt_name in ('Amount','Percentage') Then
979 l_tsp_status := 'Y';
980 Elsif l_opt_name = 'Terminate Contributions' Then
981 if l_tsp_curr_status in ('W','Y') then
982 l_tsp_status := 'T';
983 else
984 l_tsp_status := l_tsp_curr_status;
985 End If;
986 End If;
987 --End If;--Bug# 8622486
988 End If;
989
990 hr_utility.trace('l_tsp_status = ' ||l_tsp_status );
991 /* Assign the values for old and new status to global variables */
992 g_old_tsp_status := l_tsp_curr_status;
993 g_new_tsp_status := l_tsp_status;
994 hr_utility.set_location('Leaving '||l_proc_name,100);
995 Return l_tsp_status;
996 Exception
997 When Others Then
998 hr_utility.set_location('Exception Leaving '||l_proc_name,200);
999 hr_utility.trace('Error ' || sqlerrm(sqlcode));
1000 return null;
1001 End get_tsp_status;
1002
1003
1004 Function fn_effective_date (p_effective_date in Date)
1005 Return Date is
1006 Begin
1007 return p_effective_date;
1008 End fn_effective_date;
1009
1010 Function tsp_plan_electble( p_business_group_id in Number
1011 ,p_asg_id in Number
1012 ,p_pgm_id in Number
1013 ,p_pl_id in Number
1014 ,p_ler_id in Number
1015 ,p_effective_date in Date
1016 ,p_opt_id in Number)
1017 RETURN VARCHAR2 Is
1018
1019 l_proc_name varchar2(100);
1020 l_eligible Varchar2(1);
1021 l_emp_dt Varchar2(50);
1022 l_exists Varchar2(1);
1023 l_emp_csrs Varchar2(1);
1024 l_multi_error_flag Boolean;
1025 l_effective_date Date;
1026 l_pay_start_date Date;
1027 l_pay_end_date Date;
1028 l_agency_dt Varchar2(50);
1029 l_opt_name ben_opt_f.name%type;
1030
1031 Cursor c_pay_period is
1032 select start_date,end_date
1033 from per_time_periods
1034 where payroll_id in
1035 (select payroll_id
1036 from per_assignments_f
1037 where assignment_id = p_asg_id
1038 and trunc(p_effective_date) between effective_start_date and effective_end_date)
1039 and p_effective_date between start_date and end_date;
1040
1041 Cursor c_get_option_name is
1042 select name
1043 from ben_opt_f
1044 where opt_id = p_opt_id
1045 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1046 Begin
1047
1048 l_proc_name := g_package|| 'tsp_plan_electble';
1049 hr_utility.set_location('Entering '||l_proc_name,10);
1050 for pay_period in c_pay_period loop
1051 l_pay_start_date := pay_period.start_date;
1052 l_pay_end_date := pay_period.end_date;
1053 exit;
1054 end loop;
1055 if p_effective_date > l_pay_start_date then
1056 l_effective_date := l_pay_end_date + 1;
1057 else
1058 l_effective_date := l_pay_start_date;
1059 End If;
1060 hr_utility.trace('l_effective_date ' ||l_effective_date );
1061
1062 -- Check retirement Plan for an employee
1063 -- if retirement plan is any of FERS plan and Agency Contribution date is not entered
1064 -- then employee cannot make elections.
1065 --Bug# 8622486 Removed Agency Contrib Date condition
1066 /*l_emp_csrs := check_if_emp_csrs( p_business_group_id,p_asg_id,l_effective_date );
1067 hr_utility.trace('l_emp_csrs ' ||l_emp_csrs );
1068 if l_emp_csrs = 'N' Then
1069 hr_utility.set_location('Entering '||l_proc_name,20);
1070 l_agency_dt := get_agency_contrib_date(p_asg_id,l_effective_date);
1071 hr_utility.trace('l_agency_dt = ' ||l_agency_dt );
1072 if l_agency_dt is null Then
1073 l_eligible := 'N';
1074 else
1075 l_eligible := 'Y';
1076 End If;
1077 Else*/
1078 --Bug# 8622486
1079 hr_utility.set_location('Entering '||l_proc_name,30);
1080 l_eligible := 'Y';
1081 --End If; --Bug# 8622486
1082 if l_eligible = 'Y' and p_opt_id <> -1 Then
1083 -- Get Emp Contrib Elig Date
1084 hr_utility.set_location('Entering '||l_proc_name,40);
1085 --Get employee contribution date. If not null and greater then effective date
1086 -- then employee cannot make elections.
1087 l_emp_dt := get_emp_contrib_date(p_asg_id,l_effective_date);
1088 hr_utility.trace('l_emp_dt = ' ||l_emp_dt );
1089 If l_emp_dt is null then
1090 l_eligible := 'Y';
1091 ElsIf l_emp_dt is not null Then
1092 -- if there is any value entered for employee contributuion eligibility date
1093 If l_emp_dt > l_effective_date then
1094 hr_utility.set_location('Entering '||l_proc_name,50);
1095 for get_option_name in c_get_option_name loop
1096 l_opt_name := get_option_name.name;
1097 exit;
1098 End Loop;
1099 hr_utility.trace('l_opt_name = ' ||l_opt_name );
1100 If l_opt_name = 'Terminate Contributions' then
1101 hr_utility.set_location('Entering '||l_proc_name,60);
1102 l_eligible := 'Y';
1103 Else
1104 l_eligible := 'N';
1105 End If;
1106 Else
1107 l_eligible := 'Y';
1108 End If;
1109 End If;
1110 End If;
1111 hr_utility.trace('l_eligible = ' ||l_eligible );
1112 hr_utility.set_location('Leaving '||l_proc_name,100);
1113 return l_eligible;
1114 End tsp_plan_electble;
1115
1116 ------- TSP Catch Up Contributions --------------------
1117 function get_emp_tsp_catchup_elig( p_business_group_id in Number
1118 ,p_asg_id in Number
1119 ,p_pgm_id in Number
1120 ,p_effective_date in Date )
1121 RETURN VARCHAR2 Is
1122
1123 l_proc_name varchar2(100);
1124 l_eligible varchar2(1);
1125 l_ee_50 varchar2(1);
1126 l_person_id per_all_people_f.person_id%type;
1127 l_payroll_id per_all_assignments_f.payroll_id%type;
1128 l_pgm_year_end_dt Date;
1129 --l_date_of_birth Date;
1130 l_tspc_rate_start_dt Date;
1131 l_tsp_pgm_id ben_pgm_f.pgm_id%type;
1132 l_pl_id ben_pl_f.pl_id%type;
1133 l_oipl_id ben_oipl_f.oipl_id%type;
1134 l_opt_name ben_opt_f.name%type;
1135
1136
1137 l_db_last_pay_end_date Date;
1138 l_db_last_check_date Date;
1139 l_db_current_check_date Date;
1140 l_db_current_pay_end_date Date;
1141 l_db_current_pay_start_date Date;
1142 l_db_next_pay_start_date Date;
1143
1144 l_agency_last_check_date Date;
1145 l_agency_current_check_date Date;
1146
1147 l_last_check_date Date;
1148 l_current_check_date Date;
1149
1150 -- Get person id
1151 Cursor c_get_person_id is
1152 Select person_id,payroll_id
1153 from per_all_assignments_f
1154 where assignment_id = p_asg_id
1155 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1156
1157 -- get end date and check date of last pay period of current year that has pay date in this year.
1158 Cursor c_get_db_last_pay_period_dtls is
1159 select end_date,regular_payment_date
1160 from per_time_periods
1161 where payroll_id = l_payroll_id
1162 and to_char(p_effective_date,'YYYY') = to_char(regular_payment_date,'YYYY')
1163 order by start_date desc;
1164 /*
1165 -- get date of birth of an employee
1166 Cursor c_get_dob is
1167 Select date_of_birth
1168 from per_all_people_f
1169 where person_id = l_person_id
1170 and trunc(l_current_check_date) between effective_start_date and effective_end_date;
1171
1172 Cursor c_get_pgm_yr is
1173 select yrp.start_date,
1174 yrp.end_date
1175 from ben_yr_perd yrp,
1176 ben_popl_yr_perd cpy
1177 where cpy.pgm_id = p_pgm_id
1178 and cpy.yr_perd_id = yrp.yr_perd_id
1179 and l_current_check_date between yrp.start_date and yrp.end_date;
1180 */
1181 -- Cursor to get program id for TSP
1182 Cursor c_get_tsp_pgm_id is
1183 select pgm_id
1184 from ben_pgm_f
1185 where name = 'Federal Thrift Savings Plan (TSP)'
1186 and business_group_id = p_business_group_id
1187 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1188
1189
1190 -- Cursor to check if employee currently enrolled in TSP Catch Up
1191 Cursor c_chk_enrolled_in_tspc is
1192 select rt_strt_dt
1193 from ben_prtt_enrt_rslt_f perf, ben_prtt_rt_val prv
1194 where perf.person_id = l_person_id
1195 and perf.pgm_id = p_pgm_id
1196 and perf.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
1197 and trunc(p_effective_date) between perf.effective_start_date and perf.effective_end_date
1198 and perf.enrt_cvg_thru_dt = hr_api.g_eot
1199 and prv.rt_end_dt = hr_api.g_eot
1200 and perf.prtt_enrt_rslt_stat_cd is null;
1201
1202 -- Cursor to check if employee currently enrolled in TSP
1203 Cursor c_chk_enrolled_in_tsp is
1204 select pl_id,oipl_id
1205 from ben_prtt_enrt_rslt_f perf
1206 where person_id = l_person_id
1207 and pgm_id = l_tsp_pgm_id
1208 --and pl_id = l_pl_id
1209 and trunc(l_db_next_pay_start_date) between effective_start_date and effective_end_date
1210 and enrt_cvg_thru_dt = hr_api.g_eot
1211 and prtt_enrt_rslt_stat_cd is null;
1212
1213 Cursor c_get_opt_name is
1214 select name from ben_opt_f
1215 where opt_id in (select opt_id from ben_oipl_f
1216 where oipl_id = l_oipl_id
1217 and p_effective_date between effective_start_date and
1218 effective_end_date)
1219 and p_effective_date between effective_start_date and effective_end_date;
1220
1221 Cursor c_get_db_curr_pay_period_dtls is
1222 select start_date,end_date,regular_payment_date
1223 from per_time_periods
1224 where payroll_id = l_payroll_id
1225 and p_effective_date between start_date and end_date
1226 --and end_date = trunc(p_effective_date)
1227 order by start_date ;
1228
1229 Cursor c_get_db_next_pay_period_dtls is
1230 select start_date,end_date,regular_payment_date
1231 from per_time_periods
1232 where payroll_id = l_payroll_id
1233 and start_date >= trunc(p_effective_date)
1234 order by start_date ;
1235
1236 Begin
1237 l_proc_name := g_package || '.get_emp_tsp_catch_up_elig';
1238 hr_utility.set_location('Entering ' ||l_proc_name,10);
1239 hr_utility.trace('p_asg_id = ' ||p_asg_id );
1240 -- get_person_id
1241 For get_person_id in c_get_person_id loop
1242 l_person_id := get_person_id.person_id;
1243 l_payroll_id:= get_person_id.payroll_id;
1244 Exit;
1245 End Loop;
1246 hr_utility.set_location(l_proc_name,20);
1247 hr_utility.trace('l_person_id = ' ||l_person_id );
1248 --dbms_output.put_line('l_person_id = ' ||l_person_id );
1249
1250 -- get last check date and pay period end date of the current year
1251 For get_db_last_pay_period_dtls in c_get_db_last_pay_period_dtls Loop
1252 l_db_last_pay_end_date := get_db_last_pay_period_dtls.end_date;
1253 l_db_last_check_date := get_db_last_pay_period_dtls.regular_payment_date;
1254 exit;
1255 End loop;
1256 hr_utility.set_location(l_proc_name,30);
1257
1258 -- Get agency last check date of year
1259 l_agency_last_check_date := ghr_agency_general.get_agency_last_check_date(l_person_id,
1260 p_asg_id,
1261 p_effective_date,
1262 l_payroll_id);
1263 l_last_check_date := nvl(l_agency_last_check_date,l_db_last_check_date);
1264
1265 -- get current pay period start date and check date
1266 for get_db_curr_pay_period_dtls in c_get_db_curr_pay_period_dtls loop
1267 l_db_current_check_date := get_db_curr_pay_period_dtls.regular_payment_date;
1268 l_db_current_pay_start_date := get_db_curr_pay_period_dtls.start_date;
1269 l_db_current_pay_end_date := get_db_curr_pay_period_dtls.end_date;
1270 exit;
1271 End Loop;
1272
1273 -- get agency check date for current pay period
1274 l_agency_current_check_date := ghr_agency_general.get_agency_check_date(l_person_id,
1275 p_asg_id,
1276 l_db_current_pay_end_date,
1277 l_payroll_id);
1278 l_current_check_date := nvl(l_agency_current_check_date,l_db_current_check_date);
1279
1280 /* ******************************************************************************/
1281 /* If the last check date and current check date are equal and effectiev date is*/
1282 /* current pay period end date then de-enroll */
1283 /*The person is de-enrolled only if there are no future dated enrollment */
1284 /********************************************************************************/
1285 if (l_current_check_date = l_last_check_date ) and (p_effective_date = l_db_current_pay_end_date) Then
1286 for chk_enrolled_in_tspc in c_chk_enrolled_in_tspc loop
1287 l_tspc_rate_start_dt := chk_enrolled_in_tspc.rt_strt_dt;
1288 exit;
1289 end loop;
1290 hr_utility.set_location(l_proc_name,50);
1291 hr_utility.trace('l_tspc_rate_start_dt = ' ||l_tspc_rate_start_dt );
1292 --dbms_output.put_line('l_tspc_rate_start_dt = ' ||l_tspc_rate_start_dt );
1293
1294 --Bug # 3188550
1295 if l_tspc_rate_start_dt is null Then
1296 hr_utility.set_location(l_proc_name,60);
1297 l_eligible := 'N';
1298 elsif l_tspc_rate_start_dt < p_effective_date then
1299 hr_utility.set_location(l_proc_name,63);
1300 l_eligible := 'N';
1301 elsif l_tspc_rate_start_dt >= p_effective_date then
1302 hr_utility.set_location(l_proc_name,65);
1303 l_eligible := 'Y';
1304 end If;
1305 Else -- if the not the last day of last pay period of year
1306 /* ************************************************************************* */
1307 /* To check if employee is 50 years or would be 50 years in the year of */
1308 /* enrollment. the eligibility for age needs to be checked against check */
1309 /* date of the pay period in which elections would be effective */
1310 /* ************************************************************************* */
1311 hr_utility.set_location(l_proc_name,70);
1312 --dbms_output.put_line('checking eligibility') ;
1313
1314 -- get next pay period start date
1315 for get_db_next_pay_period_dtls in c_get_db_next_pay_period_dtls loop
1316 l_db_next_pay_start_date := get_db_next_pay_period_dtls.start_date;
1317 exit;
1318 End Loop;
1319
1320 l_ee_50 := ghr_formula_functions.chk_if_ee_is_50 (l_person_id,
1321 p_asg_id,
1322 p_effective_date,
1323 l_db_next_pay_start_date);
1324 if l_ee_50 = 'N' then
1325 /*
1326 -- 50 years condition
1327 for get_dob in c_get_dob loop
1328 l_date_of_birth := get_dob.date_of_birth;
1329 exit;
1330 End Loop;
1331 hr_utility.trace('l_date_of_birth = ' ||l_date_of_birth );
1332 --dbms_output.put_line('l_date_of_birth = ' ||l_date_of_birth );
1333 for get_pgm_yr in c_get_pgm_yr loop
1334 l_pgm_year_end_dt := get_pgm_yr.end_date;
1335 exit;
1336 End Loop;
1337 if add_months(l_date_of_birth,600) > l_pgm_year_end_dt then
1338 */
1339 l_eligible := 'N';
1340 --dbms_output.put_line('age not 50');
1341 else
1342 /* ***********************************************************************/
1343 /* To check if employee is currently contributing to TSP and is enrolled */
1344 /* in either Amount or Percentage option. */
1345 /*************************************************************************/
1346 hr_utility.set_location(l_proc_name,90);
1347 for get_tsp_pgm_id in c_get_tsp_pgm_id Loop
1348 l_tsp_pgm_id := get_tsp_pgm_id.pgm_id;
1349 exit;
1350 End Loop;
1351
1352 for chk_enrolled_in_tsp in c_chk_enrolled_in_tsp loop
1353 l_pl_id := chk_enrolled_in_tsp.pl_id;
1354 l_oipl_id := chk_enrolled_in_tsp.oipl_id;
1355 exit;
1356 end loop;
1357
1358 hr_utility.trace('l_pl_id = ' ||l_pl_id );
1359 hr_utility.trace('l_oipl_id = ' ||l_oipl_id );
1360 --dbms_output.put_line('l_pl_id '||l_pl_id);
1361
1362 /*Bug#5533819
1363 If l_pl_id is null or l_oipl_id is null then
1364 l_eligible := 'N';
1365 Else
1366 */
1367 for get_opt_name in c_get_opt_name loop
1368 l_opt_name := get_opt_name.name;
1369 exit;
1370 End loop;
1371 hr_utility.trace('l_opt_name = ' ||l_opt_name );
1372 --dbms_output.put_line('l_opt_name = ' ||l_opt_name );
1373 If l_opt_name = 'Terminate Contributions' Then
1374 l_eligible := 'N';
1375 Else
1376 l_eligible := 'Y';
1377 End If;
1378 -- End If;
1379 End If;
1380 End If;
1381 hr_utility.trace('l_eligible = ' ||l_eligible );
1382 hr_utility.set_location('Leaving ' ||l_proc_name,100);
1383 Return l_eligible;
1384 End get_emp_tsp_catchup_elig;
1385
1386
1387 FUNCTION get_fehb_pgm_eligibility( p_business_group_id in Number
1388 ,p_asg_id in Number
1389 ,p_effective_date in Date )
1390
1391 RETURN VARCHAR2 is
1392
1393 cursor get_current_enrollment is
1394 SELECT ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id, 'Enrollment', eef.effective_start_date) enrollment
1395 FROM pay_element_entries_f eef,
1396 pay_element_types_f elt
1397 WHERE assignment_id = p_asg_id
1398 AND elt.element_type_id = eef.element_type_id
1399 AND eef.effective_start_date BETWEEN elt.effective_start_date AND
1400 elt.effective_end_date
1401 and p_effective_date between eef.effective_start_date and eef.effective_end_date
1402 AND upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
1403 p_business_group_id,
1404 p_effective_date))
1405 IN ('HEALTH BENEFITS','HEALTH BENEFITS PRE TAX') ;
1406 v_curr_enrollment varchar2(10);
1407 v_eligible varchar2(1);
1408 l_proc_name VARCHAR2(100);
1409
1410 Begin
1411 l_proc_name := g_package || '.get_fehb_pgm_eligibility';
1412 hr_utility.set_location('Entering ' ||l_proc_name,10);
1413 hr_utility.trace('Assignment id = ' ||p_asg_id );
1414 hr_utility.trace('Effective Date = ' ||p_effective_date );
1415 v_eligible := 'N';
1416 Open get_current_enrollment;
1417 Fetch get_current_enrollment into v_curr_enrollment;
1418 hr_utility.trace('Current Enrollment status = ' ||v_curr_enrollment );
1419 if v_curr_enrollment in ('Z', 'W') Then
1420 v_eligible := 'N';
1421 Else
1422 v_eligible := 'Y';
1423 End If;
1424 Close get_current_enrollment;
1425 hr_utility.trace('Eligible for FEHB = ' ||v_eligible );
1426 hr_utility.set_location('Leaving ' ||l_proc_name,10);
1427 Return v_eligible;
1428 End get_fehb_pgm_eligibility;
1429
1430
1431 FUNCTION get_temps_total_cost( p_business_group_id in Number
1432 ,p_asg_id in Number
1433 ,p_effective_date in Date )
1434 RETURN VARCHAR2 IS
1435
1436 l_procedure_name VARCHAR2(100);
1437 v_temps_total_cost VARCHAR2(50);
1438
1439
1440 cursor c_get_current_temps_total_cost is
1441 SELECT ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id,
1442 'Temps Total Cost',
1443 p_effective_date - 1) temps_cost
1444 FROM pay_element_entries_f eef,
1445 pay_element_types_f elt
1446 WHERE assignment_id = p_asg_id
1447 AND elt.element_type_id = eef.element_type_id
1448 AND eef.effective_start_date BETWEEN elt.effective_start_date AND
1449 elt.effective_end_date
1450 and (p_effective_date - 1) between eef.effective_start_date
1451 and eef.effective_end_date
1452 AND upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
1453 p_business_group_id,
1454 p_effective_date))
1455 IN ('HEALTH BENEFITS','HEALTH BENEFITS PRE TAX') ;
1456 Begin
1457 l_procedure_name := g_package || '.get_temps_total_cost';
1458 hr_utility.set_location('Entering ' ||l_procedure_name,10);
1459 hr_utility.trace('Assignment id = ' ||p_asg_id||'BG '||p_business_group_id );
1460 hr_utility.trace('Effective Date = ' ||p_effective_date );
1461 v_temps_total_cost := '';
1462 Open c_get_current_temps_total_cost;
1463 Fetch c_get_current_temps_total_cost into v_temps_total_cost;
1464 hr_utility.trace('Current Temps Total Cost = ' ||v_temps_total_cost );
1465 Close c_get_current_temps_total_cost;
1466 hr_utility.set_location('Leaving ' ||l_procedure_name,100);
1467 Return v_temps_total_cost;
1468 End get_temps_total_cost;
1469
1470
1471
1472 Function fehb_plan_electable( p_business_group_id in Number
1473 ,p_asg_id in Number
1474 ,p_pgm_id in Number
1475 ,p_pl_id in Number
1476 ,p_ler_id in Number
1477 ,p_effective_date in Date
1478 ,p_opt_id in Number)
1479 RETURN VARCHAR2 Is
1480
1481 l_proc_name VARCHAR2(100);
1482 v_eligible VARCHAR2(1);
1483 v_ler_name ben_ler_f.name%type;
1484 v_opt_name ben_opt_f.name%type;
1485 v_pl_name ben_pl_f.name%type;
1486 v_person_id per_all_people_f.person_id%type;
1487 v_coe_date Date;
1488
1489 Cursor c_get_person_id is
1490 Select person_id
1491 from per_all_assignments_f
1492 where assignment_id = p_asg_id
1493 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1494
1495 Cursor c_get_ler_name is
1496 select name
1497 from ben_ler_f
1498 where ler_id = p_ler_id
1499 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1500
1501 Cursor c_get_option_name is
1502 select name
1503 from ben_opt_f
1504 where opt_id = p_opt_id
1505 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1506
1507 Cursor c_get_plan_name is
1508 select name
1509 from ben_pl_f
1510 where pl_id = p_pl_id
1511 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1512
1513
1514 Begin
1515 l_proc_name := g_package || 'fehb_plan_electable';
1516 hr_utility.set_location('Entering ' ||l_proc_name,10);
1517 --Get Child Order equity date Processing
1518 v_coe_date := get_coe_date(p_asg_id,p_effective_date);
1519 hr_utility.set_location('v_coe_date ' ||v_coe_date,20);
1520 if v_coe_date is null then
1521 v_eligible := 'Y';
1522 Elsif p_opt_id = -1 then
1523 for get_plan_name in c_get_plan_name loop
1524 v_pl_name := get_plan_name.name;
1525 exit;
1526 end loop;
1527 if v_pl_name = 'Decline Coverage' Then
1528 v_eligible := 'N';
1529 Else
1530 v_eligible := 'Y';
1531 End If;
1532 Else
1533 for get_option_name in c_get_option_name loop
1534 v_opt_name := get_option_name.name;
1535 exit;
1536 End Loop;
1537 If v_opt_name like '%Family%' then
1538 v_eligible := 'Y';
1539 Else
1540 v_eligible := 'N';
1541 End If;
1542 End If;
1543 -- end Child Order Equity Date Processing
1544
1545 /*
1546 v_eligible := 'N';
1547 hr_utility.set_location('Entering ' ||l_proc_name,10);
1548 -- get person_id
1549 For get_person_id in c_get_person_id loop
1550 v_person_id := get_person_id.person_id;
1551 Exit;
1552 End Loop;
1553 hr_utility.set_location(l_proc_name,20);
1554 hr_utility.trace('v_person_id = ' ||v_person_id );
1555 For get_ler_name in c_get_ler_name loop
1556 v_ler_name := get_ler_name.name;
1557 exit;
1558 End loop;
1559 hr_utility.set_location(l_proc_name,30);
1560 hr_utility.trace('v_ler_name = ' ||v_ler_name );
1561
1562 if upper(v_ler_name) in ('Initial Opportunity to Enroll'
1563 ,'Open'
1564 ,'Change in Family Status'
1565 ,'Change in Employment Status Affecting Entitlement to Coverage'
1566 ,'Transfer from a post of duty within US to post of duty outside US or vice versa'
1567 ,'Employee/Family member loses coverage under FEHB or another group plan'
1568 ,'Loss of coverage under a non-Federal health plan-moves out of commuting area'
1569 ,'Employee/Family member loses coverage due to discontinuance of an FEHB plan'
1570 ) then
1571 --Get Child Order equity date Processing
1572 v_coe_date := get_coe_date(p_asg_id,p_effective_date);
1573 if v_coe_date is null then
1574 v_eligible := 'Y';
1575 Elsif p_opt_id = -1 then
1576 for get_plan_name in c_get_plan_name loop
1577 v_pl_name := get_plan_name.name;
1578 exit;
1579 end loop;
1580 if v_pl_name = 'Decline Coverage' Then
1581 v_eligible := 'N';
1582 Else
1583 v_eligible := 'Y';
1584 End If;
1585 Else
1586 for get_option_name in c_get_option_name loop
1587 v_opt_name := get_option_name.name;
1588 exit;
1589 End Loop;
1590 If v_opt_name like '%Family%' then
1591 v_eligible := 'Y';
1592 Else
1593 v_eligible := 'N';
1594 End If;
1595 End If;
1596 -- end Child Order Equity Date Processing
1597 Else
1598 v_eligible := 'Y';
1599 End If;
1600 */
1601 --v_eligible := 'Y';
1602 hr_utility.trace('Eligible = ' ||v_eligible );
1603 hr_utility.set_location('Leaving ' ||l_proc_name,100);
1604 Return v_eligible;
1605 End fehb_plan_electable;
1606
1607 Function get_agency_contrib_date (p_asg_id in Number
1608 ,p_effective_date in Date)
1609 Return Date is
1610
1611 l_proc_name VARCHAR2(100);
1612 v_agency_date Date;
1613 v_person_id per_all_people_f.person_id%type;
1614
1615 Cursor c_get_person_id is
1616 Select person_id
1617 from per_all_assignments_f
1618 where assignment_id = p_asg_id
1619 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1620
1621 cursor c_get_agency_date is
1622 select to_date(pei_information14,'yyyy/mm/dd hh24:mi:ss') agency_date
1623 from ghr_people_extra_info_h_v
1624 where pa_history_id =
1625 (select ghr_ss_views_pkg.get_people_ei_id_ason_date(v_person_id,
1626 'GHR_US_PER_BENEFIT_INFO',
1627 p_effective_date) from dual);
1628 Begin
1629 l_proc_name := g_package || '.get_agency_contrib_date';
1630 hr_utility.set_location('Entering ' ||l_proc_name,10);
1631 for get_person_id in c_get_person_id loop
1632 v_person_id := get_person_id.person_id;
1633 exit;
1634 end loop;
1635 hr_utility.trace('v_person_id = ' ||v_person_id );
1636 for get_agency_date in c_get_agency_date loop
1637 v_agency_date := get_agency_date.agency_date;
1638 exit;
1639 End loop;
1640 hr_utility.trace('v_agency_date = ' ||v_agency_date );
1641 hr_utility.set_location('Leaving ' ||l_proc_name,100);
1642 return v_agency_date;
1643 End get_agency_contrib_date;
1644
1645 Function get_emp_contrib_date (p_asg_id in Number
1646 ,p_effective_date in Date)
1647 Return Date is
1648
1649 l_proc_name VARCHAR2(100);
1650 v_emp_date Date;
1651 v_person_id per_all_people_f.person_id%type;
1652
1653 Cursor c_get_person_id is
1654 Select person_id
1655 from per_all_assignments_f
1656 where assignment_id = p_asg_id
1657 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1658
1659 cursor c_get_emp_date is
1660 select to_date(pei_information15,'yyyy/mm/dd hh24:mi:ss') emp_date
1661 from ghr_people_extra_info_h_v
1662 where pa_history_id =
1663 (select ghr_ss_views_pkg.get_people_ei_id_ason_date(v_person_id,
1664 'GHR_US_PER_BENEFIT_INFO',
1665 p_effective_date) from dual);
1666 Begin
1667 l_proc_name := g_package || 'get_emp_contrib_date';
1668 hr_utility.set_location('Entering ' ||l_proc_name,10);
1669 for get_person_id in c_get_person_id loop
1670 v_person_id := get_person_id.person_id;
1671 exit;
1672 end loop;
1673 hr_utility.trace('v_person_id = ' ||v_person_id );
1674 for get_emp_date in c_get_emp_date loop
1675 v_emp_date := get_emp_date.emp_date;
1676 exit;
1677 End loop;
1678 hr_utility.trace('v_emp_date = ' ||v_emp_date );
1679 hr_utility.set_location('Leaving ' ||l_proc_name,100);
1680 return v_emp_date;
1681 End get_emp_contrib_date;
1682
1683 -- FUnction to get Child Order Equity Date
1684 Function get_coe_date (p_asg_id in Number
1685 ,p_effective_date in Date)
1686 Return Date is
1687
1688 l_proc_name VARCHAR2(100);
1689 v_coe_date Date;
1690 v_person_id per_all_people_f.person_id%type;
1691
1692 Cursor c_get_person_id is
1693 Select person_id
1694 from per_all_assignments_f
1695 where assignment_id = p_asg_id
1696 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1697
1698 cursor c_get_coe_date is
1699 select to_date(pei_information10,'yyyy/mm/dd hh24:mi:ss') coe_date
1700 from ghr_people_extra_info_h_v
1701 where pa_history_id =
1702 (select ghr_ss_views_pkg.get_people_ei_id_ason_date(v_person_id,
1703 'GHR_US_PER_BENEFIT_INFO',
1704 p_effective_date) from dual);
1705 Begin
1706 l_proc_name := g_package || 'get_coe_date';
1707 hr_utility.set_location('Entering ' ||l_proc_name,10);
1708 for get_person_id in c_get_person_id loop
1709 v_person_id := get_person_id.person_id;
1710 exit;
1711 end loop;
1712 hr_utility.trace('v_person_id = ' ||v_person_id );
1713 for get_coe_date in c_get_coe_date loop
1714 v_coe_date := get_coe_date.coe_date;
1715 exit;
1716 End loop;
1717 hr_utility.trace('v_coe_date = ' ||v_coe_date );
1718 hr_utility.set_location('Leaving ' ||l_proc_name,100);
1719 return v_coe_date;
1720 End get_coe_date;
1721
1722 Function tsp_cvg_and_rate_start_date (p_business_group_id in Number
1723 ,p_asg_id in Number
1724 ,p_effective_date in Date)
1725 Return date is
1726
1727 l_proc_name varchar2(100);
1728 v_latest_hire_date Date;
1729 v_cvg_rate_date Date;
1730 v_hire_date Date;
1731 v_person_id per_all_people_f.person_id%type;
1732 v_payroll_id per_all_assignments_f.payroll_id%type;
1733 v_noa_family_code ghr_pa_requests.noa_family_code%type;
1734 v_first_noa_code ghr_pa_requests.first_noa_code%type;
1735 v_rehire Varchar2(1);
1736
1737 -- get person id
1738 Cursor c_get_person_id is
1739 Select person_id,payroll_id
1740 from per_all_assignments_f
1741 where assignment_id = p_asg_id
1742 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1743
1744 -- get hire date
1745 Cursor c_get_hire_date is
1746 select decode(PER.CURRENT_EMPLOYEE_FLAG,'Y',PPS.DATE_START,null) hire_date
1747 from per_all_people_f per, per_periods_of_service pps
1748 where per.person_id = v_person_id
1749 and per.person_id = pps.person_id
1750 and PER.EMPLOYEE_NUMBER IS NOT NULL
1751 and PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
1752 FROM PER_PERIODS_OF_SERVICE PPS1
1753 WHERE PPS1.PERSON_ID = PER.PERSON_ID
1754 AND PPS1.DATE_START <= PER.EFFECTIVE_END_DATE) ;
1755
1756 --check if this person exists in database
1757 Cursor c_chk_if_rehire is
1758 select 'Y'
1759 from per_all_assignments_f
1760 where person_id = v_person_id
1761 and (p_effective_date - 30) between effective_start_date and effective_end_date
1762 and assignment_type <> 'B';
1763 /*
1764
1765 AND ((PER.EMPLOYEE_NUMBER IS NULL) OR
1766 (PER.EMPLOYEE_NUMBER IS NOT NULL AND PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
1767 FROM PER_PERIODS_OF_SERVICE PPS1
1768 WHERE PPS1.PERSON_ID = PER.PERSON_ID
1769 AND PPS1.DATE_START <= PER.EFFECTIVE_END_DATE))) AND ((PER.NPW_NUMBER IS NULL) OR
1770 (PER.NPW_NUMBER IS NOT NULL AND PPP.DATE_START =
1771 (SELECT MAX(PPP1.DATE_START) FROM PER_PERIODS_OF_PLACEMENT PPP1
1772 WHERE PPP1.PERSON_ID = PER.PERSON_ID AND PPP1.DATE_START <= PER.EFFECTIVE_END_DATE)))
1773 */
1774 -- get latest rehire or transfer date
1775 Cursor c_get_latest_hire_noac is
1776 select noa_family_code,first_noa_code
1777 from ghr_pa_requests
1778 where person_id = v_person_id
1779 and noa_family_code in ('APP','CONV_APP')
1780 and nvl(effective_date,hr_api.g_date) = trunc(p_effective_date);
1781
1782 -- get coverage and rate start date
1783 Cursor c_get_dates is
1784 select start_date
1785 from per_time_periods
1786 where payroll_id = v_payroll_id
1787 and start_date >= trunc(p_effective_date)
1788 order by start_date ;
1789
1790 Begin
1791 l_proc_name := g_package|| 'tsp_cvg_and_start_date';
1792 hr_utility.set_location('Entering '||l_proc_name,10);
1793 --dbms_output.put_line(' In procedure ' ||p_effective_date);
1794 For get_person_id in c_get_person_id loop
1795 v_person_id := get_person_id.person_id;
1796 v_payroll_id := get_person_id.payroll_id;
1797 exit;
1798 End Loop;
1799 hr_utility.set_location('v_person_id '||v_person_id,20);
1800 --get hire_date
1801 for get_hire_date in c_get_hire_date LOOP
1802 v_hire_date := get_hire_date.hire_date;
1803 exit;
1804 end loop;
1805 If v_hire_date <> p_effective_date then
1806 for get_dates in c_get_dates loop
1807 v_cvg_rate_date := get_dates.start_date;
1808 exit;
1809 end loop;
1810 Else
1811 -- get latest NOAC for the hire action
1812 for get_latest_hire_noac in c_get_latest_hire_noac loop
1813 v_noa_family_code := get_latest_hire_noac.noa_family_code;
1814 v_first_noa_code := get_latest_hire_noac.first_noa_code;
1815 exit;
1816 End loop;
1817 if v_first_noa_code like '1%' and v_first_noa_code not in ('140','141','143','130','132','145','147') Then
1818 for get_dates in c_get_dates loop
1819 v_cvg_rate_date := get_dates.start_date;
1820 exit;
1821 end loop;
1822 elsif v_first_noa_code in ('130','132','145','147') or v_noa_family_code = 'CONV_APP' Then
1823 v_cvg_rate_date := p_effective_date;
1824 elsif v_first_noa_code in ('140','141','143') then
1825 v_rehire := 'N';
1826 for chk_if_rehire in c_chk_if_rehire Loop
1827 v_rehire := 'Y';
1828 exit;
1829 End Loop;
1830 If v_rehire = 'Y' Then
1831 v_cvg_rate_date := p_effective_date;
1832 else
1833 for get_dates in c_get_dates loop
1834 v_cvg_rate_date := get_dates.start_date;
1835 exit;
1836 end loop;
1837 End If;
1838 End If;
1839
1840 End If;
1841 hr_utility.set_location('v_cvg_rate_date '||v_cvg_rate_date,60);
1842 hr_utility.set_location('Leaving '||l_proc_name,100);
1843 Return v_cvg_rate_date;
1844 Exception
1845 When Others Then
1846 hr_utility.set_location('Exception Leaving '||l_proc_name,210);
1847 hr_utility.trace('Error ' || sqlerrm(sqlcode));
1848 Return p_effective_date;
1849 End tsp_cvg_and_rate_start_date;
1850
1851 FUNCTION ghr_tsp_cu_amount_validation(
1852 p_business_group_id in number
1853 ,p_asg_id in number
1854 ,p_effective_date in date
1855 ,p_pgm_id in number
1856 ,p_pl_id in number
1857 )
1858 RETURN varchar2 is
1859
1860 l_proc_name varchar2(100);
1861 l_result Varchar2(1);
1862 l_person_id per_all_people_f.person_id%type;
1863 l_tsp_cu_amount Number;
1864 l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type;
1865 l_payroll_id pay_payrolls_f.payroll_id%type;
1866 l_rt_strt_dt Date;
1867 l_effective_date Date;
1868 l_agency_check_date date;
1869 l_db_check_date Date;
1870 l_check_date date;
1871 l_end_date date;
1872
1873 Cursor c_get_person_id is
1874 Select person_id,payroll_id
1875 from per_all_assignments_f
1876 where assignment_id = p_asg_id
1877 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1878
1879 Cursor c_get_prtt_enrt_rslt_id is
1880 select rt_strt_dt,rt_val
1881 from ben_prtt_enrt_rslt_f perf , ben_prtt_rt_val prv
1882 where perf.person_id = l_person_id
1883 and perf.pgm_id = p_pgm_id
1884 and perf.pl_id = p_pl_id
1885 and perf.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
1886 and trunc(l_effective_date) between perf.effective_start_date and perf.effective_end_date
1887 and perf.enrt_cvg_thru_dt = hr_api.g_eot
1888 and prv.rt_end_dt = hr_api.g_eot
1889 and perf.prtt_enrt_rslt_stat_cd is null;
1890
1891 -- get check__date maianitained in system for the rate start date
1892 Cursor c_get_db_check_date is
1893 select regular_payment_date,end_date
1894 from per_time_periods
1895 where payroll_id = l_payroll_id
1896 and start_date >= trunc(l_rt_strt_dt)
1897 order by start_date ;
1898 Begin
1899 l_proc_name := g_package|| 'ghr_tsp_cu_amount_validation';
1900 l_result := 'Y';
1901 hr_utility.set_location('Entering ' ||l_proc_name,10);
1902
1903 -- get person_id
1904 For get_person_id in c_get_person_id loop
1905 l_person_id := get_person_id.person_id;
1906 l_payroll_id := get_person_id.payroll_id;
1907 Exit;
1908 End Loop;
1909 hr_utility.set_location(l_proc_name,20);
1910 hr_utility.trace('l_person_id = ' ||l_person_id );
1911 hr_utility.trace('p_pgm_id = ' ||p_pgm_id );
1912 hr_utility.trace('p_pl_id = ' ||p_pl_id );
1913 hr_utility.trace('p_effective_date = ' ||p_effective_date );
1914 --dbms_output.put_line('per id ' ||l_person_id||' pl id:' ||p_pl_id||' pgmid:'||p_pgm_id);
1915
1916 ghr_history_api.get_session_date(l_effective_date);
1917 hr_utility.trace('l_effective_date = ' ||l_effective_date );
1918 --Get Prtt Enrt Rslt id
1919 For get_prtt_enrt_rslt_id in c_get_prtt_enrt_rslt_id loop
1920 l_rt_strt_dt := get_prtt_enrt_rslt_id.rt_strt_dt;
1921 l_tsp_cu_amount := get_prtt_enrt_rslt_id.rt_val;
1922 exit;
1923 End loop;
1924 hr_utility.set_location(l_proc_name,30);
1925 hr_utility.trace('l_rt_strt_dt = ' ||l_rt_strt_dt);
1926 hr_utility.trace('l_tsp_cu_amount = ' ||l_tsp_cu_amount );
1927 --dbms_output.put_line('AMOUNT ' ||l_tsp_cu_amount);
1928
1929 hr_utility.trace('l_rt_strt_dt = ' ||l_rt_strt_dt);
1930 -- get check date (for rt_strt_dt)
1931 for get_db_check_date in c_get_db_check_date loop
1932 l_db_check_date := get_db_check_date.regular_payment_date;
1933 l_end_date := get_db_check_date.end_date;
1934 exit;
1935 End Loop;
1936
1937 --get agency_check_date
1938 l_agency_check_date := ghr_agency_general.get_agency_check_date(l_person_id
1939 ,p_asg_id
1940 ,l_end_date
1941 ,l_payroll_id);
1942
1943 -- if agnecy check date is returned then we use that else use the date maintained in system
1944 l_check_date := nvl(l_agency_check_date,l_db_check_date);
1945 hr_utility.trace('l_check_date = ' ||l_check_date);
1946
1947 l_tsp_cu_amount := nvl(l_tsp_cu_amount,0);
1948
1949 hr_utility.trace('l_tsp_cu_amount ' ||l_tsp_cu_amount );
1950 If l_tsp_cu_amount = 0 Then
1951 l_result := 'N';
1952 Elsif l_tsp_cu_amount > 0 Then
1953 If l_check_date between to_date('01/01/2005','dd/mm/yyyy')
1954 and to_date('31/12/2005','dd/mm/yyyy') Then
1955 If l_tsp_cu_amount <= 4000 Then
1956 l_result := 'Y';
1957 Else
1958 l_result := 'N';
1959 End If;
1960 Elsif l_check_date between to_date('01/01/2006','dd/mm/yyyy')
1961 and to_date('31/12/2006','dd/mm/yyyy') Then
1962 If l_tsp_cu_amount <= 5000 Then
1963 l_result := 'Y';
1964 Else
1965 l_result := 'N';
1966 End If;
1967 Else
1968 l_result := 'Y';
1969 End If;
1970 End If;
1971 hr_utility.set_location('Leaving '||l_proc_name,80);
1972 hr_utility.trace('l_result ' ||l_result );
1973 return l_result;
1974 Exception
1975 When others Then
1976 hr_utility.set_location('Exception Leaving ' ||l_proc_name,200);
1977 hr_utility.trace('Error ' || sqlerrm(sqlcode));
1978 Return 'N';
1979 End ghr_tsp_cu_amount_validation;
1980
1981 -- Parameter p_payroll_period_start_date addded. This date must be the start date
1982 -- of the payroll period in which election occurs.
1983 function chk_if_ee_is_50 (p_person_id in Number,
1984 p_asg_id in Number,
1985 p_effective_date in date,
1986 p_payroll_period_start_date in date)
1987 return varchar2 is
1988 l_proc_name varchar2(100);
1989 l_date_of_birth date;
1990 l_payroll_id Number;
1991 l_db_current_check_date Date;
1992 l_db_current_pay_end_date Date;
1993 l_agency_current_check_date Date;
1994 l_current_check_date Date;
1995
1996 Cursor c_get_payroll_id is
1997 select payroll_id
1998 from per_assignments_f
1999 where assignment_id = p_asg_id
2000 and p_effective_date between effective_start_date and effective_end_date;
2001
2002 Cursor c_get_db_curr_pay_period_dtls is
2003 select start_date,end_date,regular_payment_date
2004 from per_time_periods
2005 where payroll_id = l_payroll_id
2006 and start_date = trunc(p_payroll_period_start_date)
2007 order by start_date ;
2008
2009 Cursor c_get_dob is
2010 Select date_of_birth
2011 from per_all_people_f
2012 where person_id = p_person_id
2013 and trunc(l_current_check_date) between effective_start_date and effective_end_date;
2014
2015 Begin
2016 l_proc_name := g_package|| 'chk_if_ee_is_50';
2017 hr_utility.set_location('Entering ' ||l_proc_name,10);
2018 -- Get Payroll Id
2019 for get_payroll_id in c_get_payroll_id loop
2020 l_payroll_id := get_payroll_id.payroll_id;
2021 exit;
2022 End Loop;
2023 hr_utility.set_location(l_proc_name,20);
2024
2025 -- get current pay period end date and check date
2026 -- get check date for the effective date
2027 for get_db_curr_pay_period_dtls in c_get_db_curr_pay_period_dtls loop
2028 l_db_current_check_date := get_db_curr_pay_period_dtls.regular_payment_date;
2029 l_db_current_pay_end_date := get_db_curr_pay_period_dtls.end_date;
2030 exit;
2031 End Loop;
2032 hr_utility.set_location(l_proc_name,30);
2033
2034 -- get agency check date for current pay period
2035 l_agency_current_check_date := ghr_agency_general.get_agency_check_date(p_person_id,
2036 p_asg_id,
2037 l_db_current_pay_end_date,
2038 l_payroll_id);
2039 l_current_check_date := nvl(l_agency_current_check_date,l_db_current_check_date);
2040 hr_utility.set_location(l_proc_name,40);
2041
2042 for get_dob in c_get_dob loop
2043 l_date_of_birth := get_dob.date_of_birth;
2044 exit;
2045 End Loop;
2046
2047 --check if employee would be 50 in that calendar year
2048 If add_months (l_date_of_birth,600) >
2049 to_date('31/12/'||to_char(l_current_check_date,'YYYY'),'DD/MM/YYYY') Then
2050 return 'N';
2051 Else
2052 return 'Y';
2053 End If;
2054 hr_utility.set_location('Leaving '||l_proc_name,100);
2055 Exception
2056 When Others Then
2057 Return 'N';
2058 End chk_if_ee_is_50;
2059
2060
2061 --Bug # 4122470 FEGLI
2062 FUNCTION get_fegli_option_short_code(
2063 p_business_group_id in number
2064 ,p_effective_date in date
2065 ,p_opt_id in number)
2066 RETURN varchar2 is
2067
2068 v_opt_short_code ben_opt_f.name%type;
2069 l_procedure_name varchar2(100);
2070
2071 Cursor C1 is
2072 select short_code from ben_opt_f
2073 where opt_id = p_opt_id
2074 and business_group_id = p_business_group_id
2075 and p_effective_date between effective_start_date and effective_end_date;
2076 Begin
2077 l_procedure_name := g_package || '.get_option_short_code';
2078 hr_utility.set_location('Entering:'|| l_procedure_name, 10);
2079 hr_utility.trace('p_opt_id = ' || p_opt_id);
2080 hr_utility.trace('p_effective_date = ' || p_effective_date);
2081 for i in c1 loop
2082 v_opt_short_code := i.short_code;
2083 exit;
2084 End Loop;
2085 hr_utility.trace('v_opt_short_code = ' || v_opt_short_code);
2086 hr_utility.set_location('Leaving:'|| l_procedure_name, 20);
2087 Return v_opt_short_code;
2088 Exception
2089 when others then
2090 hr_utility.set_location(' Leaving:'||l_procedure_name, 30);
2091 hr_utility.trace('Error ' || sqlerrm(sqlcode));
2092 End get_fegli_option_short_code;
2093
2094
2095 FUNCTION get_fegli_pgm_eligibility( p_business_group_id in Number
2096 ,p_asg_id in Number
2097 ,p_effective_date in Date )
2098
2099 RETURN VARCHAR2 is
2100
2101 cursor get_current_enrollment is
2102 SELECT ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id, 'FEGLI', eef.effective_start_date) enrollment
2103 FROM pay_element_entries_f eef,
2104 pay_element_types_f elt
2105 WHERE assignment_id = p_asg_id
2106 AND elt.element_type_id = eef.element_type_id
2107 AND eef.effective_start_date BETWEEN elt.effective_start_date AND
2108 elt.effective_end_date
2109 and p_effective_date between eef.effective_start_date and eef.effective_end_date
2110 AND upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
2111 p_business_group_id,
2112 p_effective_date))
2113 IN ('FEGLI AB') ;
2114 v_curr_enrollment varchar2(10);
2115 v_eligible varchar2(1);
2116 l_proc_name VARCHAR2(100);
2117
2118 Begin
2119 l_proc_name := g_package || '.get_fegli_pgm_eligibility';
2120 hr_utility.set_location('Entering ' ||l_proc_name,10);
2121 hr_utility.trace('Assignment id = ' ||p_asg_id );
2122 hr_utility.trace('Effective Date = ' ||p_effective_date );
2123 v_eligible := 'N';
2124 Open get_current_enrollment;
2125 Fetch get_current_enrollment into v_curr_enrollment;
2126 hr_utility.trace('Current Enrollment status = ' ||v_curr_enrollment );
2127 if v_curr_enrollment in ('A0') Then
2128 v_eligible := 'N';
2129 Else
2130 v_eligible := 'Y';
2131 End If;
2132 Close get_current_enrollment;
2133 hr_utility.trace('Eligible for FEGLI = ' ||v_eligible );
2134 hr_utility.set_location('Leaving ' ||l_proc_name,10);
2135 Return v_eligible;
2136 End get_fegli_pgm_eligibility;
2137
2138
2139 FUNCTION fegli_rpa_create_update(
2140 p_business_group_id in Number
2141 ,p_asg_id in Number
2142 ,p_effective_date in Date
2143 ,p_pgm_id in Number
2144 ,p_pl_id in Number
2145 ,p_option_id in Number) RETURN VARCHAR2 IS
2146
2147 Cursor c_get_person_id is
2148 Select person_id,payroll_id
2149 from per_all_assignments_f
2150 where assignment_id = p_asg_id
2151 and trunc(p_effective_date) between effective_start_date and effective_end_date;
2152
2153 Cursor c_get_enrt_cvg_info(p_person_id in number) is
2154 select enrt_cvg_strt_dt,
2155 ler_id
2156 from ben_prtt_enrt_rslt_f perf
2157 where perf.person_id = p_person_id
2158 and perf.pgm_id = p_pgm_id
2159 and perf.pl_id = p_pl_id
2160 and perf.prtt_enrt_rslt_stat_cd is null
2161 order by prtt_enrt_rslt_id desc;
2162
2163 Cursor get_option_code is
2164 select short_code from ben_opt_f
2165 where opt_id = p_option_id
2166 and business_group_id = p_business_group_id
2167 and trunc(p_effective_date) between effective_start_date and effective_end_date;
2168
2169 Cursor c_get_ler_name(p_ler_id in number) is
2170 select name
2171 from ben_ler_f
2172 where ler_id = p_ler_id
2173 and trunc(p_effective_date) between effective_start_date and effective_end_date;
2174
2175 l_fegli_code ghr_pa_requests.fegli%type;
2176 l_person_id per_all_assignments_f.person_id%type;
2177 l_payroll_id per_all_assignments_f.payroll_id%type;
2178 l_enrt_cvg_st_dt ben_prtt_enrt_rslt_f.enrt_cvg_strt_dt%type;
2179 l_option_id ben_opt_f.opt_id%type;
2180 l_ler_name ben_ler_f.name%type;
2181 l_ler_id ben_ler_f.ler_id%type;
2182 l_fegli_event_code varchar2(3);
2183
2184 BEGIN
2185
2186 -- get person_id
2187 For get_person_id in c_get_person_id loop
2188 l_person_id := get_person_id.person_id;
2189 l_payroll_id := get_person_id.payroll_id;
2190 Exit;
2191 End Loop;
2192
2193 --get cvg stdt and option
2194 For get_cvg_dtls in c_get_enrt_cvg_info(p_person_id => l_person_id)
2195 loop
2196 l_enrt_cvg_st_dt := get_cvg_dtls.enrt_cvg_strt_dt;
2197 l_ler_id := get_cvg_dtls.ler_id;
2198 exit;
2199 end loop;
2200
2201 hr_utility.set_location('p_option_id'||p_option_id,1000);
2202
2203 for rec in get_option_code
2204 loop
2205 l_fegli_code := rec.short_code;
2206 end loop;
2207
2208 For get_ler_name in c_get_ler_name(p_ler_id => l_ler_id) loop
2209 l_ler_name := get_ler_name.name;
2210 exit;
2211 End loop;
2212
2213 IF l_ler_name = 'Initial Opportunity to Enroll' then
2214 l_fegli_event_code := '3';
2215 ELSIF l_ler_name = 'Open' then
2216 l_fegli_event_code := '6';
2217 ELSiF l_ler_name = 'Change in Family Status' then
2218 l_fegli_event_code := '2';
2219 ELSIF l_ler_name = 'Change in Employment Status entitlement to coverage' OR
2220 l_ler_name = 'Enrollment Following Previous Waiver of Basic or Optional' then
2221 l_fegli_event_code := '1';
2222 ELSIF l_ler_name = 'Change in employment Status cost of insurance' OR
2223 l_ler_name = 'Waiver, Termination or Reduction of Optional Coverage' then
2224 l_fegli_event_code := '5A';
2225 ELSiF l_ler_name = 'Return from Uniformed Service' then
2226 l_fegli_event_code := '5B';
2227 END IF;
2228
2229 hr_utility.set_location('l_fe'||l_fegli_code,1000);
2230
2231 GHR_SS_RPA_CREATION.CREATE_SF52_FEGLI(p_person_id => l_person_id,
2232 p_assignment_id => p_asg_id,
2233 p_fegli_code => l_fegli_code,
2234 p_fegli_event_code => l_fegli_event_code,
2235 p_effective_date => l_enrt_cvg_st_dt);
2236
2237 return 'Y';
2238
2239
2240
2241 END fegli_rpa_create_update;
2242
2243
2244 Function fegli_cvg_and_rate_start_date (p_business_group_id in Number
2245 ,p_asg_id in Number
2246 ,p_effective_date in Date
2247 ,p_ler_id in Number
2248 ,p_pgm_id in Number
2249 ,p_pl_id in Number)
2250 Return date is
2251
2252 l_proc_name varchar2(100);
2253 v_cvg_rate_st_date Date;
2254 v_hire_date Date;
2255 v_period_st_date Date;
2256 v_person_id per_all_people_f.person_id%type;
2257 v_payroll_id per_all_assignments_f.payroll_id%type;
2258 v_ler_name ben_ler_f.name%type;
2259 v_effective_st_dt Date;
2260
2261
2262
2263 -- get person id
2264 Cursor c_get_person_id is
2265 Select person_id,payroll_id
2266 from per_all_assignments_f
2267 where assignment_id = p_asg_id
2268 and trunc(p_effective_date) between effective_start_date and effective_end_date;
2269
2270
2271 --get Life Event Name
2272 Cursor c_get_ler_name is
2273 select name
2274 from ben_ler_f
2275 where ler_id = p_ler_id
2276 and trunc(p_effective_date) between effective_start_date and effective_end_date;
2277
2278
2279 cursor c_get_eff_st_dt is
2280 select trunc(effective_date) session_date
2281 from fnd_sessions
2282 where session_id = (select userenv('sessionid') from dual);
2283
2284
2285 -- get hire date
2286 Cursor c_get_hire_date is
2287 select decode(PER.CURRENT_EMPLOYEE_FLAG,'Y',PPS.DATE_START,null) hire_date
2288 from per_all_people_f per, per_periods_of_service pps
2289 where per.person_id = v_person_id
2290 and per.person_id = pps.person_id
2291 and PER.EMPLOYEE_NUMBER IS NOT NULL
2292 and PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
2293 FROM PER_PERIODS_OF_SERVICE PPS1
2294 WHERE PPS1.PERSON_ID = PER.PERSON_ID
2295 AND PPS1.DATE_START <= PER.EFFECTIVE_END_DATE) ;
2296
2297 -- pay period start date
2298 Cursor c_get_pay_dates(p_effective_date in date) is
2299 select start_date
2300 from per_time_periods
2301 where payroll_id = v_payroll_id
2302 and p_effective_date between start_date and end_date;
2303
2304 -- next pay period start date
2305 Cursor c_get_next_pay_period(p_effective_date in date) is
2306 select start_date
2307 from per_time_periods
2308 where payroll_id = v_payroll_id
2309 and start_date >= trunc(p_effective_date)
2310 order by start_date;
2311
2312 Begin
2313 l_proc_name := g_package|| 'fegli_cvg_and_start_date';
2314 hr_utility.set_location('Entering '||l_proc_name,10);
2315 --dbms_output.put_line(' In procedure ' ||p_effective_date);
2316 For get_person_id in c_get_person_id loop
2317 v_person_id := get_person_id.person_id;
2318 v_payroll_id := get_person_id.payroll_id;
2319 exit;
2320 End Loop;
2321 hr_utility.set_location('v_person_id '||v_person_id,20);
2322 hr_utility.set_location('v_payroll_id '||v_payroll_id,30);
2323 -- Fetching Life Event Name
2324 hr_utility.set_location('v_ler_id '||p_ler_id,40);
2325
2326 hr_utility.set_location('effective date'||p_effective_date,45);
2327
2328 For get_ler_name in c_get_ler_name loop
2329 v_ler_name := get_ler_name.name;
2330 exit;
2331 End loop;
2332 hr_utility.set_location('v_ler_name '||v_ler_name,50);
2333 For get_eff_st_dt in c_get_eff_st_dt
2334 loop
2335 v_effective_st_dt := get_eff_st_dt.session_date;
2336 exit;
2337 End Loop;
2338 If v_effective_st_dt is null then
2339 v_effective_st_dt := p_effective_date;
2340 End if;
2341 hr_utility.set_location('v_effective_st_dt '||v_effective_st_dt,50);
2342 if v_ler_name = 'Initial Opportunity to Enroll' then
2343 --get hire_date
2344 for get_hire_date in c_get_hire_date LOOP
2345 v_hire_date := get_hire_date.hire_date;
2346 exit;
2347 end loop;
2348
2349 --get payroll start date
2350 for get_pay_dates in c_get_pay_dates(p_effective_date => v_effective_st_dt)
2351 loop
2352 v_period_st_date := get_pay_dates.start_date;
2353 exit;
2354 end loop;
2355
2356 if trunc(v_hire_date) > v_period_st_date then
2357 hr_utility.set_location('v_hire_date'||v_hire_date,51);
2358 v_cvg_rate_st_date := v_hire_date;
2359 else
2360 hr_utility.set_location('v_period_st_date'||v_period_st_date,52);
2361 v_cvg_rate_st_date := v_period_st_date;
2362 end if;
2363 elsif v_ler_name = 'Open' then
2364 v_cvg_rate_st_date := v_effective_st_dt+1;
2365 else
2366 v_cvg_rate_st_date := v_effective_st_dt;
2367 end if;
2368 hr_utility.set_location('v_cvg_rate_st_date'||v_cvg_rate_st_date,52);
2369 return v_cvg_rate_st_date;
2370 Exception
2371 When Others Then
2372 hr_utility.set_location('Exception Leaving '||l_proc_name,210);
2373 hr_utility.trace('Error ' || sqlerrm(sqlcode));
2374 Return p_effective_date;
2375 End fegli_cvg_and_rate_start_date;
2376
2377 End;
[Home] [Help]