[Home] [Help]
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:
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
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 */
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
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
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
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
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 */
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
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,
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:
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
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:
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
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;
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);
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
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);
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';
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
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);
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
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);
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);
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';
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);
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);
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);
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:
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(
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';
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
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
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
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);
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(
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
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;
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
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;
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;
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;
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';
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));
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
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
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
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'
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);
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;
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);
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
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
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);
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
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';
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
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);
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:
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
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);
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);
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';
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
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);
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:
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
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
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
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(
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;
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 );
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);
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:
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);
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 );
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;
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:
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);
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:
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')
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
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);
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:
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:
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;
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 );
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);
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:
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);
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 );
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;
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);
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:
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);
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
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
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 );
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')
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
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);
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;
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));
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:
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:
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
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);
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:
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
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);
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:
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
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;
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;
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
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'
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;
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
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;
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:
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;
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);
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
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 );
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 );
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);
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
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
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
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);
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);
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:
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:
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;
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.
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 );
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';
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';
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
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 );
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
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;
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
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;
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:
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 --------------------
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;
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;
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
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,
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
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
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';
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';
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
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;
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;
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
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
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
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:
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:
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;
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;
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 );
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';
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:
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:
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;
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;
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 );
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;
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:
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
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
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;
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;
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;
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'
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'
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:
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
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;
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;
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:
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
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;
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;
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:
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
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;
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;
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:
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
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;
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;
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
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);
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:
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(
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;
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 );
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);
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:
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);
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 );
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;
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:
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);
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)
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;
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 );
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')
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
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);
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:
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
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;
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,
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;
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;
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;
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;
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;
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
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);
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:
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
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;
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;
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 );
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';
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:
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:
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;
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,
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;
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:
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);
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
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;
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;
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;
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;
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);
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:
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;