1 PACKAGE BODY ghr_formula_functions AS
2 /* $Header: ghforfun.pkb 120.7.12010000.4 2008/11/05 11:37:38 vmididho ship $ */
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 If l_tsp_curr_agency_date > l_effective_date then --Valid status are (I,W,S)
964 hr_utility.set_location('Entering Processing for FERS '||l_proc_name,70);
965 If l_opt_name in ('Amount','Percentage') Then
966 l_tsp_status := 'W';
967 Elsif l_opt_name = 'Terminate Contributions' Then
968 if l_tsp_curr_status in ('W','Y') then
969 l_tsp_status := 'S';
970 else
971 l_tsp_status := l_tsp_curr_status;
972 End If;
973 End If;
974 Else --Valid New Status are Y and T
975 hr_utility.set_location('Entering Processing for FERS '||l_proc_name,80);
976 If l_opt_name in ('Amount','Percentage') Then
977 l_tsp_status := 'Y';
978 Elsif l_opt_name = 'Terminate Contributions' Then
979 if l_tsp_curr_status in ('W','Y') then
980 l_tsp_status := 'T';
981 else
982 l_tsp_status := l_tsp_curr_status;
983 End If;
984 End If;
985 End If;
986 End If;
987
988 hr_utility.trace('l_tsp_status = ' ||l_tsp_status );
989 /* Assign the values for old and new status to global variables */
990 g_old_tsp_status := l_tsp_curr_status;
991 g_new_tsp_status := l_tsp_status;
992 hr_utility.set_location('Leaving '||l_proc_name,100);
993 Return l_tsp_status;
994 Exception
995 When Others Then
996 hr_utility.set_location('Exception Leaving '||l_proc_name,200);
997 hr_utility.trace('Error ' || sqlerrm(sqlcode));
998 return null;
999 End get_tsp_status;
1000
1001
1002 Function fn_effective_date (p_effective_date in Date)
1003 Return Date is
1004 Begin
1005 return p_effective_date;
1006 End fn_effective_date;
1007
1008 Function tsp_plan_electble( p_business_group_id in Number
1009 ,p_asg_id in Number
1010 ,p_pgm_id in Number
1011 ,p_pl_id in Number
1012 ,p_ler_id in Number
1013 ,p_effective_date in Date
1014 ,p_opt_id in Number)
1015 RETURN VARCHAR2 Is
1016
1017 l_proc_name varchar2(100);
1018 l_eligible Varchar2(1);
1019 l_emp_dt Varchar2(50);
1020 l_exists Varchar2(1);
1021 l_emp_csrs Varchar2(1);
1022 l_multi_error_flag Boolean;
1023 l_effective_date Date;
1024 l_pay_start_date Date;
1025 l_pay_end_date Date;
1026 l_agency_dt Varchar2(50);
1027 l_opt_name ben_opt_f.name%type;
1028
1029 Cursor c_pay_period is
1030 select start_date,end_date
1031 from per_time_periods
1032 where payroll_id in
1033 (select payroll_id
1034 from per_assignments_f
1035 where assignment_id = p_asg_id
1036 and trunc(p_effective_date) between effective_start_date and effective_end_date)
1037 and p_effective_date between start_date and end_date;
1038
1039 Cursor c_get_option_name is
1040 select name
1041 from ben_opt_f
1042 where opt_id = p_opt_id
1043 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1044 Begin
1045
1046 l_proc_name := g_package|| 'tsp_plan_electble';
1047 hr_utility.set_location('Entering '||l_proc_name,10);
1048 for pay_period in c_pay_period loop
1049 l_pay_start_date := pay_period.start_date;
1050 l_pay_end_date := pay_period.end_date;
1051 exit;
1052 end loop;
1053 if p_effective_date > l_pay_start_date then
1054 l_effective_date := l_pay_end_date + 1;
1055 else
1056 l_effective_date := l_pay_start_date;
1057 End If;
1058 hr_utility.trace('l_effective_date ' ||l_effective_date );
1059
1060 -- Check retirement Plan for an employee
1061 -- if retirement plan is any of FERS plan and Agency Contribution date is not entered
1062 -- then employee cannot make elections.
1063 l_emp_csrs := check_if_emp_csrs( p_business_group_id,p_asg_id,l_effective_date );
1064 hr_utility.trace('l_emp_csrs ' ||l_emp_csrs );
1065 if l_emp_csrs = 'N' Then
1066 hr_utility.set_location('Entering '||l_proc_name,20);
1067 l_agency_dt := get_agency_contrib_date(p_asg_id,l_effective_date);
1068 hr_utility.trace('l_agency_dt = ' ||l_agency_dt );
1069 if l_agency_dt is null Then
1070 l_eligible := 'N';
1071 else
1072 l_eligible := 'Y';
1073 End If;
1074 Else
1075 hr_utility.set_location('Entering '||l_proc_name,30);
1076 l_eligible := 'Y';
1077 End If;
1078 if l_eligible = 'Y' and p_opt_id <> -1 Then
1079 -- Get Emp Contrib Elig Date
1080 hr_utility.set_location('Entering '||l_proc_name,40);
1081 --Get employee contribution date. If not null and greater then effective date
1082 -- then employee cannot make elections.
1083 l_emp_dt := get_emp_contrib_date(p_asg_id,l_effective_date);
1084 hr_utility.trace('l_emp_dt = ' ||l_emp_dt );
1085 If l_emp_dt is null then
1086 l_eligible := 'Y';
1087 ElsIf l_emp_dt is not null Then
1088 -- if there is any value entered for employee contributuion eligibility date
1089 If l_emp_dt > l_effective_date then
1090 hr_utility.set_location('Entering '||l_proc_name,50);
1091 for get_option_name in c_get_option_name loop
1092 l_opt_name := get_option_name.name;
1093 exit;
1094 End Loop;
1095 hr_utility.trace('l_opt_name = ' ||l_opt_name );
1096 If l_opt_name = 'Terminate Contributions' then
1097 hr_utility.set_location('Entering '||l_proc_name,60);
1098 l_eligible := 'Y';
1099 Else
1100 l_eligible := 'N';
1101 End If;
1102 Else
1103 l_eligible := 'Y';
1104 End If;
1105 End If;
1106 End If;
1107 hr_utility.trace('l_eligible = ' ||l_eligible );
1108 hr_utility.set_location('Leaving '||l_proc_name,100);
1109 return l_eligible;
1110 End tsp_plan_electble;
1111
1112 ------- TSP Catch Up Contributions --------------------
1113 function get_emp_tsp_catchup_elig( p_business_group_id in Number
1114 ,p_asg_id in Number
1115 ,p_pgm_id in Number
1116 ,p_effective_date in Date )
1117 RETURN VARCHAR2 Is
1118
1119 l_proc_name varchar2(100);
1120 l_eligible varchar2(1);
1121 l_ee_50 varchar2(1);
1122 l_person_id per_all_people_f.person_id%type;
1123 l_payroll_id per_all_assignments_f.payroll_id%type;
1124 l_pgm_year_end_dt Date;
1125 --l_date_of_birth Date;
1126 l_tspc_rate_start_dt Date;
1127 l_tsp_pgm_id ben_pgm_f.pgm_id%type;
1128 l_pl_id ben_pl_f.pl_id%type;
1129 l_oipl_id ben_oipl_f.oipl_id%type;
1130 l_opt_name ben_opt_f.name%type;
1131
1132
1133 l_db_last_pay_end_date Date;
1134 l_db_last_check_date Date;
1135 l_db_current_check_date Date;
1136 l_db_current_pay_end_date Date;
1137 l_db_current_pay_start_date Date;
1138 l_db_next_pay_start_date Date;
1139
1140 l_agency_last_check_date Date;
1141 l_agency_current_check_date Date;
1142
1143 l_last_check_date Date;
1144 l_current_check_date Date;
1145
1146 -- Get person id
1147 Cursor c_get_person_id is
1148 Select person_id,payroll_id
1149 from per_all_assignments_f
1150 where assignment_id = p_asg_id
1151 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1152
1153 -- get end date and check date of last pay period of current year that has pay date in this year.
1154 Cursor c_get_db_last_pay_period_dtls is
1155 select end_date,regular_payment_date
1156 from per_time_periods
1157 where payroll_id = l_payroll_id
1158 and to_char(p_effective_date,'YYYY') = to_char(regular_payment_date,'YYYY')
1159 order by start_date desc;
1160 /*
1161 -- get date of birth of an employee
1162 Cursor c_get_dob is
1163 Select date_of_birth
1164 from per_all_people_f
1165 where person_id = l_person_id
1166 and trunc(l_current_check_date) between effective_start_date and effective_end_date;
1167
1168 Cursor c_get_pgm_yr is
1169 select yrp.start_date,
1170 yrp.end_date
1171 from ben_yr_perd yrp,
1172 ben_popl_yr_perd cpy
1173 where cpy.pgm_id = p_pgm_id
1174 and cpy.yr_perd_id = yrp.yr_perd_id
1175 and l_current_check_date between yrp.start_date and yrp.end_date;
1176 */
1177 -- Cursor to get program id for TSP
1178 Cursor c_get_tsp_pgm_id is
1179 select pgm_id
1180 from ben_pgm_f
1181 where name = 'Federal Thrift Savings Plan (TSP)'
1182 and business_group_id = p_business_group_id
1183 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1184
1185
1186 -- Cursor to check if employee currently enrolled in TSP Catch Up
1187 Cursor c_chk_enrolled_in_tspc is
1188 select rt_strt_dt
1189 from ben_prtt_enrt_rslt_f perf, ben_prtt_rt_val prv
1190 where perf.person_id = l_person_id
1191 and perf.pgm_id = p_pgm_id
1192 and perf.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
1193 and trunc(p_effective_date) between perf.effective_start_date and perf.effective_end_date
1194 and perf.enrt_cvg_thru_dt = hr_api.g_eot
1195 and prv.rt_end_dt = hr_api.g_eot
1196 and perf.prtt_enrt_rslt_stat_cd is null;
1197
1198 -- Cursor to check if employee currently enrolled in TSP
1199 Cursor c_chk_enrolled_in_tsp is
1200 select pl_id,oipl_id
1201 from ben_prtt_enrt_rslt_f perf
1202 where person_id = l_person_id
1203 and pgm_id = l_tsp_pgm_id
1204 --and pl_id = l_pl_id
1205 and trunc(l_db_next_pay_start_date) between effective_start_date and effective_end_date
1206 and enrt_cvg_thru_dt = hr_api.g_eot
1207 and prtt_enrt_rslt_stat_cd is null;
1208
1209 Cursor c_get_opt_name is
1210 select name from ben_opt_f
1211 where opt_id in (select opt_id from ben_oipl_f
1212 where oipl_id = l_oipl_id
1213 and p_effective_date between effective_start_date and
1214 effective_end_date)
1215 and p_effective_date between effective_start_date and effective_end_date;
1216
1217 Cursor c_get_db_curr_pay_period_dtls is
1218 select start_date,end_date,regular_payment_date
1219 from per_time_periods
1220 where payroll_id = l_payroll_id
1221 and p_effective_date between start_date and end_date
1222 --and end_date = trunc(p_effective_date)
1223 order by start_date ;
1224
1225 Cursor c_get_db_next_pay_period_dtls is
1226 select start_date,end_date,regular_payment_date
1227 from per_time_periods
1228 where payroll_id = l_payroll_id
1229 and start_date >= trunc(p_effective_date)
1230 order by start_date ;
1231
1232 Begin
1233 l_proc_name := g_package || '.get_emp_tsp_catch_up_elig';
1234 hr_utility.set_location('Entering ' ||l_proc_name,10);
1235 hr_utility.trace('p_asg_id = ' ||p_asg_id );
1236 -- get_person_id
1237 For get_person_id in c_get_person_id loop
1238 l_person_id := get_person_id.person_id;
1239 l_payroll_id:= get_person_id.payroll_id;
1240 Exit;
1241 End Loop;
1242 hr_utility.set_location(l_proc_name,20);
1243 hr_utility.trace('l_person_id = ' ||l_person_id );
1244 --dbms_output.put_line('l_person_id = ' ||l_person_id );
1245
1246 -- get last check date and pay period end date of the current year
1247 For get_db_last_pay_period_dtls in c_get_db_last_pay_period_dtls Loop
1248 l_db_last_pay_end_date := get_db_last_pay_period_dtls.end_date;
1249 l_db_last_check_date := get_db_last_pay_period_dtls.regular_payment_date;
1250 exit;
1251 End loop;
1252 hr_utility.set_location(l_proc_name,30);
1253
1254 -- Get agency last check date of year
1255 l_agency_last_check_date := ghr_agency_general.get_agency_last_check_date(l_person_id,
1256 p_asg_id,
1257 p_effective_date,
1258 l_payroll_id);
1259 l_last_check_date := nvl(l_agency_last_check_date,l_db_last_check_date);
1260
1261 -- get current pay period start date and check date
1262 for get_db_curr_pay_period_dtls in c_get_db_curr_pay_period_dtls loop
1263 l_db_current_check_date := get_db_curr_pay_period_dtls.regular_payment_date;
1264 l_db_current_pay_start_date := get_db_curr_pay_period_dtls.start_date;
1265 l_db_current_pay_end_date := get_db_curr_pay_period_dtls.end_date;
1266 exit;
1267 End Loop;
1268
1269 -- get agency check date for current pay period
1270 l_agency_current_check_date := ghr_agency_general.get_agency_check_date(l_person_id,
1271 p_asg_id,
1272 l_db_current_pay_end_date,
1273 l_payroll_id);
1274 l_current_check_date := nvl(l_agency_current_check_date,l_db_current_check_date);
1275
1276 /* ******************************************************************************/
1277 /* If the last check date and current check date are equal and effectiev date is*/
1278 /* current pay period end date then de-enroll */
1279 /*The person is de-enrolled only if there are no future dated enrollment */
1280 /********************************************************************************/
1281 if (l_current_check_date = l_last_check_date ) and (p_effective_date = l_db_current_pay_end_date) Then
1282 for chk_enrolled_in_tspc in c_chk_enrolled_in_tspc loop
1283 l_tspc_rate_start_dt := chk_enrolled_in_tspc.rt_strt_dt;
1284 exit;
1285 end loop;
1286 hr_utility.set_location(l_proc_name,50);
1287 hr_utility.trace('l_tspc_rate_start_dt = ' ||l_tspc_rate_start_dt );
1288 --dbms_output.put_line('l_tspc_rate_start_dt = ' ||l_tspc_rate_start_dt );
1289
1290 --Bug # 3188550
1291 if l_tspc_rate_start_dt is null Then
1292 hr_utility.set_location(l_proc_name,60);
1293 l_eligible := 'N';
1294 elsif l_tspc_rate_start_dt < p_effective_date then
1295 hr_utility.set_location(l_proc_name,63);
1296 l_eligible := 'N';
1297 elsif l_tspc_rate_start_dt >= p_effective_date then
1298 hr_utility.set_location(l_proc_name,65);
1299 l_eligible := 'Y';
1300 end If;
1301 Else -- if the not the last day of last pay period of year
1302 /* ************************************************************************* */
1303 /* To check if employee is 50 years or would be 50 years in the year of */
1304 /* enrollment. the eligibility for age needs to be checked against check */
1305 /* date of the pay period in which elections would be effective */
1306 /* ************************************************************************* */
1307 hr_utility.set_location(l_proc_name,70);
1308 --dbms_output.put_line('checking eligibility') ;
1309
1310 -- get next pay period start date
1311 for get_db_next_pay_period_dtls in c_get_db_next_pay_period_dtls loop
1312 l_db_next_pay_start_date := get_db_next_pay_period_dtls.start_date;
1313 exit;
1314 End Loop;
1315
1316 l_ee_50 := ghr_formula_functions.chk_if_ee_is_50 (l_person_id,
1317 p_asg_id,
1318 p_effective_date,
1319 l_db_next_pay_start_date);
1320 if l_ee_50 = 'N' then
1321 /*
1322 -- 50 years condition
1323 for get_dob in c_get_dob loop
1324 l_date_of_birth := get_dob.date_of_birth;
1325 exit;
1326 End Loop;
1327 hr_utility.trace('l_date_of_birth = ' ||l_date_of_birth );
1328 --dbms_output.put_line('l_date_of_birth = ' ||l_date_of_birth );
1329 for get_pgm_yr in c_get_pgm_yr loop
1330 l_pgm_year_end_dt := get_pgm_yr.end_date;
1331 exit;
1332 End Loop;
1333 if add_months(l_date_of_birth,600) > l_pgm_year_end_dt then
1334 */
1335 l_eligible := 'N';
1336 --dbms_output.put_line('age not 50');
1337 else
1338 /* ***********************************************************************/
1339 /* To check if employee is currently contributing to TSP and is enrolled */
1340 /* in either Amount or Percentage option. */
1341 /*************************************************************************/
1342 hr_utility.set_location(l_proc_name,90);
1343 for get_tsp_pgm_id in c_get_tsp_pgm_id Loop
1344 l_tsp_pgm_id := get_tsp_pgm_id.pgm_id;
1345 exit;
1346 End Loop;
1347
1348 for chk_enrolled_in_tsp in c_chk_enrolled_in_tsp loop
1349 l_pl_id := chk_enrolled_in_tsp.pl_id;
1350 l_oipl_id := chk_enrolled_in_tsp.oipl_id;
1351 exit;
1352 end loop;
1353
1354 hr_utility.trace('l_pl_id = ' ||l_pl_id );
1355 hr_utility.trace('l_oipl_id = ' ||l_oipl_id );
1356 --dbms_output.put_line('l_pl_id '||l_pl_id);
1357
1358 /*Bug#5533819
1359 If l_pl_id is null or l_oipl_id is null then
1360 l_eligible := 'N';
1361 Else
1362 */
1363 for get_opt_name in c_get_opt_name loop
1364 l_opt_name := get_opt_name.name;
1365 exit;
1366 End loop;
1367 hr_utility.trace('l_opt_name = ' ||l_opt_name );
1368 --dbms_output.put_line('l_opt_name = ' ||l_opt_name );
1369 If l_opt_name = 'Terminate Contributions' Then
1370 l_eligible := 'N';
1371 Else
1372 l_eligible := 'Y';
1373 End If;
1374 -- End If;
1375 End If;
1376 End If;
1377 hr_utility.trace('l_eligible = ' ||l_eligible );
1378 hr_utility.set_location('Leaving ' ||l_proc_name,100);
1379 Return l_eligible;
1380 End get_emp_tsp_catchup_elig;
1381
1382
1383 FUNCTION get_fehb_pgm_eligibility( p_business_group_id in Number
1384 ,p_asg_id in Number
1385 ,p_effective_date in Date )
1386
1387 RETURN VARCHAR2 is
1388
1389 cursor get_current_enrollment is
1390 SELECT ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id, 'Enrollment', eef.effective_start_date) enrollment
1391 FROM pay_element_entries_f eef,
1392 pay_element_types_f elt
1393 WHERE assignment_id = p_asg_id
1394 AND elt.element_type_id = eef.element_type_id
1395 AND eef.effective_start_date BETWEEN elt.effective_start_date AND
1396 elt.effective_end_date
1397 and p_effective_date between eef.effective_start_date and eef.effective_end_date
1398 AND upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
1399 p_business_group_id,
1400 p_effective_date))
1401 IN ('HEALTH BENEFITS','HEALTH BENEFITS PRE TAX') ;
1402 v_curr_enrollment varchar2(10);
1403 v_eligible varchar2(1);
1404 l_proc_name VARCHAR2(100);
1405
1406 Begin
1407 l_proc_name := g_package || '.get_fehb_pgm_eligibility';
1408 hr_utility.set_location('Entering ' ||l_proc_name,10);
1409 hr_utility.trace('Assignment id = ' ||p_asg_id );
1410 hr_utility.trace('Effective Date = ' ||p_effective_date );
1411 v_eligible := 'N';
1412 Open get_current_enrollment;
1413 Fetch get_current_enrollment into v_curr_enrollment;
1414 hr_utility.trace('Current Enrollment status = ' ||v_curr_enrollment );
1415 if v_curr_enrollment in ('Z', 'W') Then
1416 v_eligible := 'N';
1417 Else
1418 v_eligible := 'Y';
1419 End If;
1420 Close get_current_enrollment;
1421 hr_utility.trace('Eligible for FEHB = ' ||v_eligible );
1422 hr_utility.set_location('Leaving ' ||l_proc_name,10);
1423 Return v_eligible;
1424 End get_fehb_pgm_eligibility;
1425
1426
1427 FUNCTION get_temps_total_cost( p_business_group_id in Number
1428 ,p_asg_id in Number
1429 ,p_effective_date in Date )
1430 RETURN VARCHAR2 IS
1431
1432 l_procedure_name VARCHAR2(100);
1433 v_temps_total_cost VARCHAR2(50);
1434
1435
1436 cursor c_get_current_temps_total_cost is
1437 SELECT ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id,
1438 'Temps Total Cost',
1439 p_effective_date - 1) temps_cost
1440 FROM pay_element_entries_f eef,
1441 pay_element_types_f elt
1442 WHERE assignment_id = p_asg_id
1443 AND elt.element_type_id = eef.element_type_id
1444 AND eef.effective_start_date BETWEEN elt.effective_start_date AND
1445 elt.effective_end_date
1446 and (p_effective_date - 1) between eef.effective_start_date
1447 and eef.effective_end_date
1448 AND upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
1449 p_business_group_id,
1450 p_effective_date))
1451 IN ('HEALTH BENEFITS','HEALTH BENEFITS PRE TAX') ;
1452 Begin
1453 l_procedure_name := g_package || '.get_temps_total_cost';
1454 hr_utility.set_location('Entering ' ||l_procedure_name,10);
1455 hr_utility.trace('Assignment id = ' ||p_asg_id||'BG '||p_business_group_id );
1456 hr_utility.trace('Effective Date = ' ||p_effective_date );
1457 v_temps_total_cost := '';
1458 Open c_get_current_temps_total_cost;
1459 Fetch c_get_current_temps_total_cost into v_temps_total_cost;
1460 hr_utility.trace('Current Temps Total Cost = ' ||v_temps_total_cost );
1461 Close c_get_current_temps_total_cost;
1462 hr_utility.set_location('Leaving ' ||l_procedure_name,100);
1463 Return v_temps_total_cost;
1464 End get_temps_total_cost;
1465
1466
1467
1468 Function fehb_plan_electable( p_business_group_id in Number
1469 ,p_asg_id in Number
1470 ,p_pgm_id in Number
1471 ,p_pl_id in Number
1472 ,p_ler_id in Number
1473 ,p_effective_date in Date
1474 ,p_opt_id in Number)
1475 RETURN VARCHAR2 Is
1476
1477 l_proc_name VARCHAR2(100);
1478 v_eligible VARCHAR2(1);
1479 v_ler_name ben_ler_f.name%type;
1480 v_opt_name ben_opt_f.name%type;
1481 v_pl_name ben_pl_f.name%type;
1482 v_person_id per_all_people_f.person_id%type;
1483 v_coe_date Date;
1484
1485 Cursor c_get_person_id is
1486 Select person_id
1487 from per_all_assignments_f
1488 where assignment_id = p_asg_id
1489 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1490
1491 Cursor c_get_ler_name is
1492 select name
1493 from ben_ler_f
1494 where ler_id = p_ler_id
1495 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1496
1497 Cursor c_get_option_name is
1498 select name
1499 from ben_opt_f
1500 where opt_id = p_opt_id
1501 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1502
1503 Cursor c_get_plan_name is
1504 select name
1505 from ben_pl_f
1506 where pl_id = p_pl_id
1507 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1508
1509
1510 Begin
1511 l_proc_name := g_package || 'fehb_plan_electable';
1512 hr_utility.set_location('Entering ' ||l_proc_name,10);
1513 --Get Child Order equity date Processing
1514 v_coe_date := get_coe_date(p_asg_id,p_effective_date);
1515 hr_utility.set_location('v_coe_date ' ||v_coe_date,20);
1516 if v_coe_date is null then
1517 v_eligible := 'Y';
1518 Elsif p_opt_id = -1 then
1519 for get_plan_name in c_get_plan_name loop
1520 v_pl_name := get_plan_name.name;
1521 exit;
1522 end loop;
1523 if v_pl_name = 'Decline Coverage' Then
1524 v_eligible := 'N';
1525 Else
1526 v_eligible := 'Y';
1527 End If;
1528 Else
1529 for get_option_name in c_get_option_name loop
1530 v_opt_name := get_option_name.name;
1531 exit;
1532 End Loop;
1533 If v_opt_name like '%Family%' then
1534 v_eligible := 'Y';
1535 Else
1536 v_eligible := 'N';
1537 End If;
1538 End If;
1539 -- end Child Order Equity Date Processing
1540
1541 /*
1542 v_eligible := 'N';
1543 hr_utility.set_location('Entering ' ||l_proc_name,10);
1544 -- get person_id
1545 For get_person_id in c_get_person_id loop
1546 v_person_id := get_person_id.person_id;
1547 Exit;
1548 End Loop;
1549 hr_utility.set_location(l_proc_name,20);
1550 hr_utility.trace('v_person_id = ' ||v_person_id );
1551 For get_ler_name in c_get_ler_name loop
1552 v_ler_name := get_ler_name.name;
1553 exit;
1554 End loop;
1555 hr_utility.set_location(l_proc_name,30);
1556 hr_utility.trace('v_ler_name = ' ||v_ler_name );
1557
1558 if upper(v_ler_name) in ('Initial Opportunity to Enroll'
1559 ,'Open'
1560 ,'Change in Family Status'
1561 ,'Change in Employment Status Affecting Entitlement to Coverage'
1562 ,'Transfer from a post of duty within US to post of duty outside US or vice versa'
1563 ,'Employee/Family member loses coverage under FEHB or another group plan'
1564 ,'Loss of coverage under a non-Federal health plan-moves out of commuting area'
1565 ,'Employee/Family member loses coverage due to discontinuance of an FEHB plan'
1566 ) then
1567 --Get Child Order equity date Processing
1568 v_coe_date := get_coe_date(p_asg_id,p_effective_date);
1569 if v_coe_date is null then
1570 v_eligible := 'Y';
1571 Elsif p_opt_id = -1 then
1572 for get_plan_name in c_get_plan_name loop
1573 v_pl_name := get_plan_name.name;
1574 exit;
1575 end loop;
1576 if v_pl_name = 'Decline Coverage' Then
1577 v_eligible := 'N';
1578 Else
1579 v_eligible := 'Y';
1580 End If;
1581 Else
1582 for get_option_name in c_get_option_name loop
1583 v_opt_name := get_option_name.name;
1584 exit;
1585 End Loop;
1586 If v_opt_name like '%Family%' then
1587 v_eligible := 'Y';
1588 Else
1589 v_eligible := 'N';
1590 End If;
1591 End If;
1592 -- end Child Order Equity Date Processing
1593 Else
1594 v_eligible := 'Y';
1595 End If;
1596 */
1597 --v_eligible := 'Y';
1598 hr_utility.trace('Eligible = ' ||v_eligible );
1599 hr_utility.set_location('Leaving ' ||l_proc_name,100);
1600 Return v_eligible;
1601 End fehb_plan_electable;
1602
1603 Function get_agency_contrib_date (p_asg_id in Number
1604 ,p_effective_date in Date)
1605 Return Date is
1606
1607 l_proc_name VARCHAR2(100);
1608 v_agency_date Date;
1609 v_person_id per_all_people_f.person_id%type;
1610
1611 Cursor c_get_person_id is
1612 Select person_id
1613 from per_all_assignments_f
1614 where assignment_id = p_asg_id
1615 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1616
1617 cursor c_get_agency_date is
1618 select to_date(pei_information14,'yyyy/mm/dd hh24:mi:ss') agency_date
1619 from ghr_people_extra_info_h_v
1620 where pa_history_id =
1621 (select ghr_ss_views_pkg.get_people_ei_id_ason_date(v_person_id,
1622 'GHR_US_PER_BENEFIT_INFO',
1623 p_effective_date) from dual);
1624 Begin
1625 l_proc_name := g_package || '.get_agency_contrib_date';
1626 hr_utility.set_location('Entering ' ||l_proc_name,10);
1627 for get_person_id in c_get_person_id loop
1628 v_person_id := get_person_id.person_id;
1629 exit;
1630 end loop;
1631 hr_utility.trace('v_person_id = ' ||v_person_id );
1632 for get_agency_date in c_get_agency_date loop
1633 v_agency_date := get_agency_date.agency_date;
1634 exit;
1635 End loop;
1636 hr_utility.trace('v_agency_date = ' ||v_agency_date );
1637 hr_utility.set_location('Leaving ' ||l_proc_name,100);
1638 return v_agency_date;
1639 End get_agency_contrib_date;
1640
1641 Function get_emp_contrib_date (p_asg_id in Number
1642 ,p_effective_date in Date)
1643 Return Date is
1644
1645 l_proc_name VARCHAR2(100);
1646 v_emp_date Date;
1647 v_person_id per_all_people_f.person_id%type;
1648
1649 Cursor c_get_person_id is
1650 Select person_id
1651 from per_all_assignments_f
1652 where assignment_id = p_asg_id
1653 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1654
1655 cursor c_get_emp_date is
1656 select to_date(pei_information15,'yyyy/mm/dd hh24:mi:ss') emp_date
1657 from ghr_people_extra_info_h_v
1658 where pa_history_id =
1659 (select ghr_ss_views_pkg.get_people_ei_id_ason_date(v_person_id,
1660 'GHR_US_PER_BENEFIT_INFO',
1661 p_effective_date) from dual);
1662 Begin
1663 l_proc_name := g_package || 'get_emp_contrib_date';
1664 hr_utility.set_location('Entering ' ||l_proc_name,10);
1665 for get_person_id in c_get_person_id loop
1666 v_person_id := get_person_id.person_id;
1667 exit;
1668 end loop;
1669 hr_utility.trace('v_person_id = ' ||v_person_id );
1670 for get_emp_date in c_get_emp_date loop
1671 v_emp_date := get_emp_date.emp_date;
1672 exit;
1673 End loop;
1674 hr_utility.trace('v_emp_date = ' ||v_emp_date );
1675 hr_utility.set_location('Leaving ' ||l_proc_name,100);
1676 return v_emp_date;
1677 End get_emp_contrib_date;
1678
1679 -- FUnction to get Child Order Equity Date
1680 Function get_coe_date (p_asg_id in Number
1681 ,p_effective_date in Date)
1682 Return Date is
1683
1684 l_proc_name VARCHAR2(100);
1685 v_coe_date Date;
1686 v_person_id per_all_people_f.person_id%type;
1687
1688 Cursor c_get_person_id is
1689 Select person_id
1690 from per_all_assignments_f
1691 where assignment_id = p_asg_id
1692 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1693
1694 cursor c_get_coe_date is
1695 select to_date(pei_information10,'yyyy/mm/dd hh24:mi:ss') coe_date
1696 from ghr_people_extra_info_h_v
1697 where pa_history_id =
1698 (select ghr_ss_views_pkg.get_people_ei_id_ason_date(v_person_id,
1699 'GHR_US_PER_BENEFIT_INFO',
1700 p_effective_date) from dual);
1701 Begin
1702 l_proc_name := g_package || 'get_coe_date';
1703 hr_utility.set_location('Entering ' ||l_proc_name,10);
1704 for get_person_id in c_get_person_id loop
1705 v_person_id := get_person_id.person_id;
1706 exit;
1707 end loop;
1708 hr_utility.trace('v_person_id = ' ||v_person_id );
1709 for get_coe_date in c_get_coe_date loop
1710 v_coe_date := get_coe_date.coe_date;
1711 exit;
1712 End loop;
1713 hr_utility.trace('v_coe_date = ' ||v_coe_date );
1714 hr_utility.set_location('Leaving ' ||l_proc_name,100);
1715 return v_coe_date;
1716 End get_coe_date;
1717
1718 Function tsp_cvg_and_rate_start_date (p_business_group_id in Number
1719 ,p_asg_id in Number
1720 ,p_effective_date in Date)
1721 Return date is
1722
1723 l_proc_name varchar2(100);
1724 v_latest_hire_date Date;
1725 v_cvg_rate_date Date;
1726 v_hire_date Date;
1727 v_person_id per_all_people_f.person_id%type;
1728 v_payroll_id per_all_assignments_f.payroll_id%type;
1729 v_noa_family_code ghr_pa_requests.noa_family_code%type;
1730 v_first_noa_code ghr_pa_requests.first_noa_code%type;
1731 v_rehire Varchar2(1);
1732
1733 -- get person id
1734 Cursor c_get_person_id is
1735 Select person_id,payroll_id
1736 from per_all_assignments_f
1737 where assignment_id = p_asg_id
1738 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1739
1740 -- get hire date
1741 Cursor c_get_hire_date is
1742 select decode(PER.CURRENT_EMPLOYEE_FLAG,'Y',PPS.DATE_START,null) hire_date
1743 from per_all_people_f per, per_periods_of_service pps
1744 where per.person_id = v_person_id
1745 and per.person_id = pps.person_id
1746 and PER.EMPLOYEE_NUMBER IS NOT NULL
1747 and PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
1748 FROM PER_PERIODS_OF_SERVICE PPS1
1749 WHERE PPS1.PERSON_ID = PER.PERSON_ID
1750 AND PPS1.DATE_START <= PER.EFFECTIVE_END_DATE) ;
1751
1752 --check if this person exists in database
1753 Cursor c_chk_if_rehire is
1754 select 'Y'
1755 from per_all_assignments_f
1756 where person_id = v_person_id
1757 and (p_effective_date - 30) between effective_start_date and effective_end_date
1758 and assignment_type <> 'B';
1759 /*
1760
1761 AND ((PER.EMPLOYEE_NUMBER IS NULL) OR
1762 (PER.EMPLOYEE_NUMBER IS NOT NULL AND PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
1763 FROM PER_PERIODS_OF_SERVICE PPS1
1764 WHERE PPS1.PERSON_ID = PER.PERSON_ID
1765 AND PPS1.DATE_START <= PER.EFFECTIVE_END_DATE))) AND ((PER.NPW_NUMBER IS NULL) OR
1766 (PER.NPW_NUMBER IS NOT NULL AND PPP.DATE_START =
1767 (SELECT MAX(PPP1.DATE_START) FROM PER_PERIODS_OF_PLACEMENT PPP1
1768 WHERE PPP1.PERSON_ID = PER.PERSON_ID AND PPP1.DATE_START <= PER.EFFECTIVE_END_DATE)))
1769 */
1770 -- get latest rehire or transfer date
1771 Cursor c_get_latest_hire_noac is
1772 select noa_family_code,first_noa_code
1773 from ghr_pa_requests
1774 where person_id = v_person_id
1775 and noa_family_code in ('APP','CONV_APP')
1776 and nvl(effective_date,hr_api.g_date) = trunc(p_effective_date);
1777
1778 -- get coverage and rate start date
1779 Cursor c_get_dates is
1780 select start_date
1781 from per_time_periods
1782 where payroll_id = v_payroll_id
1783 and start_date >= trunc(p_effective_date)
1784 order by start_date ;
1785
1786 Begin
1787 l_proc_name := g_package|| 'tsp_cvg_and_start_date';
1788 hr_utility.set_location('Entering '||l_proc_name,10);
1789 --dbms_output.put_line(' In procedure ' ||p_effective_date);
1790 For get_person_id in c_get_person_id loop
1791 v_person_id := get_person_id.person_id;
1792 v_payroll_id := get_person_id.payroll_id;
1793 exit;
1794 End Loop;
1795 hr_utility.set_location('v_person_id '||v_person_id,20);
1796 --get hire_date
1797 for get_hire_date in c_get_hire_date LOOP
1798 v_hire_date := get_hire_date.hire_date;
1799 exit;
1800 end loop;
1801 If v_hire_date <> p_effective_date then
1802 for get_dates in c_get_dates loop
1803 v_cvg_rate_date := get_dates.start_date;
1804 exit;
1805 end loop;
1806 Else
1807 -- get latest NOAC for the hire action
1808 for get_latest_hire_noac in c_get_latest_hire_noac loop
1809 v_noa_family_code := get_latest_hire_noac.noa_family_code;
1810 v_first_noa_code := get_latest_hire_noac.first_noa_code;
1811 exit;
1812 End loop;
1813 if v_first_noa_code like '1%' and v_first_noa_code not in ('140','141','143','130','132','145','147') Then
1814 for get_dates in c_get_dates loop
1815 v_cvg_rate_date := get_dates.start_date;
1816 exit;
1817 end loop;
1818 elsif v_first_noa_code in ('130','132','145','147') or v_noa_family_code = 'CONV_APP' Then
1819 v_cvg_rate_date := p_effective_date;
1820 elsif v_first_noa_code in ('140','141','143') then
1821 v_rehire := 'N';
1822 for chk_if_rehire in c_chk_if_rehire Loop
1823 v_rehire := 'Y';
1824 exit;
1825 End Loop;
1826 If v_rehire = 'Y' Then
1827 v_cvg_rate_date := p_effective_date;
1828 else
1829 for get_dates in c_get_dates loop
1830 v_cvg_rate_date := get_dates.start_date;
1831 exit;
1832 end loop;
1833 End If;
1834 End If;
1835
1836 End If;
1837 hr_utility.set_location('v_cvg_rate_date '||v_cvg_rate_date,60);
1838 hr_utility.set_location('Leaving '||l_proc_name,100);
1839 Return v_cvg_rate_date;
1840 Exception
1841 When Others Then
1842 hr_utility.set_location('Exception Leaving '||l_proc_name,210);
1843 hr_utility.trace('Error ' || sqlerrm(sqlcode));
1844 Return p_effective_date;
1845 End tsp_cvg_and_rate_start_date;
1846
1847 FUNCTION ghr_tsp_cu_amount_validation(
1848 p_business_group_id in number
1849 ,p_asg_id in number
1850 ,p_effective_date in date
1851 ,p_pgm_id in number
1852 ,p_pl_id in number
1853 )
1854 RETURN varchar2 is
1855
1856 l_proc_name varchar2(100);
1857 l_result Varchar2(1);
1858 l_person_id per_all_people_f.person_id%type;
1859 l_tsp_cu_amount Number;
1860 l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type;
1861 l_payroll_id pay_payrolls_f.payroll_id%type;
1862 l_rt_strt_dt Date;
1863 l_effective_date Date;
1864 l_agency_check_date date;
1865 l_db_check_date Date;
1866 l_check_date date;
1867 l_end_date date;
1868
1869 Cursor c_get_person_id is
1870 Select person_id,payroll_id
1871 from per_all_assignments_f
1872 where assignment_id = p_asg_id
1873 and trunc(p_effective_date) between effective_start_date and effective_end_date;
1874
1875 Cursor c_get_prtt_enrt_rslt_id is
1876 select rt_strt_dt,rt_val
1877 from ben_prtt_enrt_rslt_f perf , ben_prtt_rt_val prv
1878 where perf.person_id = l_person_id
1879 and perf.pgm_id = p_pgm_id
1880 and perf.pl_id = p_pl_id
1881 and perf.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
1882 and trunc(l_effective_date) between perf.effective_start_date and perf.effective_end_date
1883 and perf.enrt_cvg_thru_dt = hr_api.g_eot
1884 and prv.rt_end_dt = hr_api.g_eot
1885 and perf.prtt_enrt_rslt_stat_cd is null;
1886
1887 -- get check__date maianitained in system for the rate start date
1888 Cursor c_get_db_check_date is
1889 select regular_payment_date,end_date
1890 from per_time_periods
1891 where payroll_id = l_payroll_id
1892 and start_date >= trunc(l_rt_strt_dt)
1893 order by start_date ;
1894 Begin
1895 l_proc_name := g_package|| 'ghr_tsp_cu_amount_validation';
1896 l_result := 'Y';
1897 hr_utility.set_location('Entering ' ||l_proc_name,10);
1898
1899 -- get person_id
1900 For get_person_id in c_get_person_id loop
1901 l_person_id := get_person_id.person_id;
1902 l_payroll_id := get_person_id.payroll_id;
1903 Exit;
1904 End Loop;
1905 hr_utility.set_location(l_proc_name,20);
1906 hr_utility.trace('l_person_id = ' ||l_person_id );
1907 hr_utility.trace('p_pgm_id = ' ||p_pgm_id );
1908 hr_utility.trace('p_pl_id = ' ||p_pl_id );
1909 hr_utility.trace('p_effective_date = ' ||p_effective_date );
1910 --dbms_output.put_line('per id ' ||l_person_id||' pl id:' ||p_pl_id||' pgmid:'||p_pgm_id);
1911
1912 ghr_history_api.get_session_date(l_effective_date);
1913 hr_utility.trace('l_effective_date = ' ||l_effective_date );
1914 --Get Prtt Enrt Rslt id
1915 For get_prtt_enrt_rslt_id in c_get_prtt_enrt_rslt_id loop
1916 l_rt_strt_dt := get_prtt_enrt_rslt_id.rt_strt_dt;
1917 l_tsp_cu_amount := get_prtt_enrt_rslt_id.rt_val;
1918 exit;
1919 End loop;
1920 hr_utility.set_location(l_proc_name,30);
1921 hr_utility.trace('l_rt_strt_dt = ' ||l_rt_strt_dt);
1922 hr_utility.trace('l_tsp_cu_amount = ' ||l_tsp_cu_amount );
1923 --dbms_output.put_line('AMOUNT ' ||l_tsp_cu_amount);
1924
1925 hr_utility.trace('l_rt_strt_dt = ' ||l_rt_strt_dt);
1926 -- get check date (for rt_strt_dt)
1927 for get_db_check_date in c_get_db_check_date loop
1928 l_db_check_date := get_db_check_date.regular_payment_date;
1929 l_end_date := get_db_check_date.end_date;
1930 exit;
1931 End Loop;
1932
1933 --get agency_check_date
1934 l_agency_check_date := ghr_agency_general.get_agency_check_date(l_person_id
1935 ,p_asg_id
1936 ,l_end_date
1937 ,l_payroll_id);
1938
1939 -- if agnecy check date is returned then we use that else use the date maintained in system
1940 l_check_date := nvl(l_agency_check_date,l_db_check_date);
1941 hr_utility.trace('l_check_date = ' ||l_check_date);
1942
1943 l_tsp_cu_amount := nvl(l_tsp_cu_amount,0);
1944
1945 hr_utility.trace('l_tsp_cu_amount ' ||l_tsp_cu_amount );
1946 If l_tsp_cu_amount = 0 Then
1947 l_result := 'N';
1948 Elsif l_tsp_cu_amount > 0 Then
1949 If l_check_date between to_date('01/01/2005','dd/mm/yyyy')
1950 and to_date('31/12/2005','dd/mm/yyyy') Then
1951 If l_tsp_cu_amount <= 4000 Then
1952 l_result := 'Y';
1953 Else
1954 l_result := 'N';
1955 End If;
1956 Elsif l_check_date between to_date('01/01/2006','dd/mm/yyyy')
1957 and to_date('31/12/2006','dd/mm/yyyy') Then
1958 If l_tsp_cu_amount <= 5000 Then
1959 l_result := 'Y';
1960 Else
1961 l_result := 'N';
1962 End If;
1963 Else
1964 l_result := 'Y';
1965 End If;
1966 End If;
1967 hr_utility.set_location('Leaving '||l_proc_name,80);
1968 hr_utility.trace('l_result ' ||l_result );
1969 return l_result;
1970 Exception
1971 When others Then
1972 hr_utility.set_location('Exception Leaving ' ||l_proc_name,200);
1973 hr_utility.trace('Error ' || sqlerrm(sqlcode));
1974 Return 'N';
1975 End ghr_tsp_cu_amount_validation;
1976
1977 -- Parameter p_payroll_period_start_date addded. This date must be the start date
1978 -- of the payroll period in which election occurs.
1979 function chk_if_ee_is_50 (p_person_id in Number,
1980 p_asg_id in Number,
1981 p_effective_date in date,
1982 p_payroll_period_start_date in date)
1983 return varchar2 is
1984 l_proc_name varchar2(100);
1985 l_date_of_birth date;
1986 l_payroll_id Number;
1987 l_db_current_check_date Date;
1988 l_db_current_pay_end_date Date;
1989 l_agency_current_check_date Date;
1990 l_current_check_date Date;
1991
1992 Cursor c_get_payroll_id is
1993 select payroll_id
1994 from per_assignments_f
1995 where assignment_id = p_asg_id
1996 and p_effective_date between effective_start_date and effective_end_date;
1997
1998 Cursor c_get_db_curr_pay_period_dtls is
1999 select start_date,end_date,regular_payment_date
2000 from per_time_periods
2001 where payroll_id = l_payroll_id
2002 and start_date = trunc(p_payroll_period_start_date)
2003 order by start_date ;
2004
2005 Cursor c_get_dob is
2006 Select date_of_birth
2007 from per_all_people_f
2008 where person_id = p_person_id
2009 and trunc(l_current_check_date) between effective_start_date and effective_end_date;
2010
2011 Begin
2012 l_proc_name := g_package|| 'chk_if_ee_is_50';
2013 hr_utility.set_location('Entering ' ||l_proc_name,10);
2014 -- Get Payroll Id
2015 for get_payroll_id in c_get_payroll_id loop
2016 l_payroll_id := get_payroll_id.payroll_id;
2017 exit;
2018 End Loop;
2019 hr_utility.set_location(l_proc_name,20);
2020
2021 -- get current pay period end date and check date
2022 -- get check date for the effective date
2023 for get_db_curr_pay_period_dtls in c_get_db_curr_pay_period_dtls loop
2024 l_db_current_check_date := get_db_curr_pay_period_dtls.regular_payment_date;
2025 l_db_current_pay_end_date := get_db_curr_pay_period_dtls.end_date;
2026 exit;
2027 End Loop;
2028 hr_utility.set_location(l_proc_name,30);
2029
2030 -- get agency check date for current pay period
2031 l_agency_current_check_date := ghr_agency_general.get_agency_check_date(p_person_id,
2032 p_asg_id,
2033 l_db_current_pay_end_date,
2034 l_payroll_id);
2035 l_current_check_date := nvl(l_agency_current_check_date,l_db_current_check_date);
2036 hr_utility.set_location(l_proc_name,40);
2037
2038 for get_dob in c_get_dob loop
2039 l_date_of_birth := get_dob.date_of_birth;
2040 exit;
2041 End Loop;
2042
2043 --check if employee would be 50 in that calendar year
2044 If add_months (l_date_of_birth,600) >
2045 to_date('31/12/'||to_char(l_current_check_date,'YYYY'),'DD/MM/YYYY') Then
2046 return 'N';
2047 Else
2048 return 'Y';
2049 End If;
2050 hr_utility.set_location('Leaving '||l_proc_name,100);
2051 Exception
2052 When Others Then
2053 Return 'N';
2054 End chk_if_ee_is_50;
2055 End;
[Home] [Help]