[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: 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
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';
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
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
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);
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);
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:
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:
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;
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.
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 );
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';
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';
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
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 );
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
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;
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
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;
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:
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 --------------------
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;
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;
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
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,
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
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
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';
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';
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
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;
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;
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
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
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
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:
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:
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;
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;
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 );
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';
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:
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:
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;
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;
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 );
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;
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:
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
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
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;
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;
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;
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'
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'
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:
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
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;
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;
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:
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
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;
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;
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:
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
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;
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;
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:
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
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;
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;
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
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);
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:
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(
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;
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 );
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);
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:
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);
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 );
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;
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:
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);
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)
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;
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 );
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')
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
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);
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:
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
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;
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,
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;
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;