[Home] [Help]
89: l_er_age_threshold pqp_pension_types_f.er_age_threshold%TYPE;
90:
91: BEGIN
92:
93: hr_utility.set_location('Entering : '||l_proc_name, 10);
94:
95: l_pension_id := p_pension_type_id;
96:
97: --
97: --
98: -- Check if the pension_type_id is already in cache
99: --
100: IF NOT g_pension_rec.EXISTS(l_pension_id) THEN
101: hr_utility.set_location('..Pension Id :'||l_pension_id
102: ||' does not exists',15);
103: g_pension_rec.DELETE;
104: OPEN c_pty_cur (c_business_group_id => p_business_group_id
105: ,c_pension_type_id => p_pension_type_id
116: AND g_pension_rec(l_pension_id).effective_end_date
117: AND g_pension_rec(l_pension_id).pension_type_id = l_pension_id
118: AND g_pension_rec(l_pension_id).business_group_id = p_business_group_id
119: ) THEN
120: hr_utility.set_location('..Pension Id :'||l_pension_id
121: ||' does exists in pl/sql table',20);
122: hr_utility.set_location('..Pension Id is not valid for given date'
123: ||p_date_earned,25);
124: g_pension_rec.DELETE(l_pension_id);
118: AND g_pension_rec(l_pension_id).business_group_id = p_business_group_id
119: ) THEN
120: hr_utility.set_location('..Pension Id :'||l_pension_id
121: ||' does exists in pl/sql table',20);
122: hr_utility.set_location('..Pension Id is not valid for given date'
123: ||p_date_earned,25);
124: g_pension_rec.DELETE(l_pension_id);
125:
126: OPEN c_pty_cur (c_business_group_id => p_business_group_id
184: IF (c_get_ee_age_threshold%FOUND
185: and l_ee_age_threshold = 'Y'
186: ) THEN
187:
188: hr_utility.set_location(' l_ee_age_threshold is '||l_ee_age_threshold,30);
189:
190: OPEN c_get_person_age;
191: FETCH c_get_person_age into l_person_year_of_birth;
192: IF (c_get_person_age%FOUND and l_person_year_of_birth IS NOT NULL) THEN
190: OPEN c_get_person_age;
191: FETCH c_get_person_age into l_person_year_of_birth;
192: IF (c_get_person_age%FOUND and l_person_year_of_birth IS NOT NULL) THEN
193:
194: hr_utility.set_location(' l_person_year_of_birth is '||l_person_year_of_birth,35);
195:
196: OPEN c_get_subcat(NVL(g_pension_rec(l_pension_id).pension_sub_category,trim(to_char(0,'9'))));
197: FETCH c_get_subcat INTO l_subcat;
198: CLOSE c_get_subcat;
196: OPEN c_get_subcat(NVL(g_pension_rec(l_pension_id).pension_sub_category,trim(to_char(0,'9'))));
197: FETCH c_get_subcat INTO l_subcat;
198: CLOSE c_get_subcat;
199:
200: hr_utility.set_location(' l_subcat is '||l_subcat,40);
201:
202: IF l_subcat IS NOT NULL THEN
203:
204: BEGIN
215: p_column_value := NVL(p_column_value,trim(to_char(0,'9')));
216:
217: EXCEPTION
218: WHEN NO_DATA_FOUND THEN
219: hr_utility.set_location('NO_DATA_FOUND for UDT : ', 90);
220:
221: p_column_value := trim(to_char(0,'9'));
222:
223: p_error_message := 'Pension Type '
230:
231: WHEN OTHERS THEN
232:
233: IF (SQLCODE = -1422) THEN
234: hr_utility.set_location('MORE THAN ONE ROW FETCHED for UDT :', 90);
235: p_column_value := trim(to_char(0,'9'));
236: p_error_message := 'The table PQP_NL_ABP_EE_ANNUAL_SALARY_THRESHOLD has '
237: ||'overlapping rows for the age of the employee.';
238:
240: RETURN 3;
241:
242: END;
243:
244: hr_utility.set_location(' p_column_value is '||p_column_value,40);
245:
246: END IF;-- subcat check
247:
248: END IF;--c_get_person_age%FOUND
256: END IF; -- c_get_ee_age_threshold%FOUND
257:
258: CLOSE c_get_ee_age_threshold;
259:
260: hr_utility.set_location(' p_error_message is '||p_error_message,45);
261:
262: ELSIF p_column_name = 'ER_ANNUAL_SALARY_THRESHOLD' THEN
263: -- p_column_value
264: -- := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).er_annual_salary_threshold)
270: IF (c_get_er_age_threshold%FOUND
271: and l_er_age_threshold = 'Y'
272: ) THEN
273:
274: hr_utility.set_location(' l_er_age_threshold is '||l_er_age_threshold,30);
275:
276: OPEN c_get_person_age;
277: FETCH c_get_person_age into l_person_year_of_birth;
278: IF (c_get_person_age%FOUND and l_person_year_of_birth IS NOT NULL) THEN
276: OPEN c_get_person_age;
277: FETCH c_get_person_age into l_person_year_of_birth;
278: IF (c_get_person_age%FOUND and l_person_year_of_birth IS NOT NULL) THEN
279:
280: hr_utility.set_location(' l_person_year_of_birth is '||l_person_year_of_birth,35);
281:
282: OPEN c_get_subcat(NVL(g_pension_rec(l_pension_id).pension_sub_category,trim(to_char(0,'9'))));
283: FETCH c_get_subcat INTO l_subcat;
284: CLOSE c_get_subcat;
282: OPEN c_get_subcat(NVL(g_pension_rec(l_pension_id).pension_sub_category,trim(to_char(0,'9'))));
283: FETCH c_get_subcat INTO l_subcat;
284: CLOSE c_get_subcat;
285:
286: hr_utility.set_location(' l_subcat is '||l_subcat,40);
287:
288: IF l_subcat IS NOT NULL THEN
289:
290: BEGIN
301: p_column_value := NVL(p_column_value,trim(to_char(0,'9')));
302:
303: EXCEPTION
304: WHEN NO_DATA_FOUND THEN
305: hr_utility.set_location('NO_DATA_FOUND for UDT : ', 90);
306:
307: p_column_value := trim(to_char(0,'9'));
308:
309: -- fnd_message.set_name('PQP','PQP_230129_PEN_AGE_ANN_SAL_THR');
321:
322: WHEN OTHERS THEN
323:
324: IF (SQLCODE = -1422) THEN
325: hr_utility.set_location('MORE THAN ONE ROW FETCHED for UDT :', 90);
326: p_column_value := trim(to_char(0,'9'));
327: p_error_message := 'The table PQP_NL_ABP_ER_ANNUAL_SALARY_THRESHOLD has '
328: ||'overlapping rows for the age of the employee.';
329:
331: RETURN 3;
332:
333: END;
334:
335: hr_utility.set_location(' p_column_value is '||p_column_value,40);
336:
337: END IF;-- subcat check
338:
339: END IF;--c_get_person_age%FOUND
347: END IF; -- c_get_er_age_threshold%FOUND
348:
349: CLOSE c_get_er_age_threshold;
350:
351: hr_utility.set_location(' p_error_message is '||p_error_message,45);
352:
353: ELSIF p_column_name = 'ANNUAL_PREMIUM_AMOUNT' THEN
354: p_column_value
355: := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).annual_premium_amount)
412: ||' Column : '||p_column_name||' is invalid.';
413: RETURN 1;
414: END IF;
415:
416: hr_utility.set_location('..Column Name :'||p_column_name , 30);
417: hr_utility.set_location('..Column Value :'||p_column_value, 35);
418: hr_utility.set_location('..p_error_message :'||p_error_message, 40);
419: hr_utility.set_location('Leaving : '||l_proc_name, 80);
420:
413: RETURN 1;
414: END IF;
415:
416: hr_utility.set_location('..Column Name :'||p_column_name , 30);
417: hr_utility.set_location('..Column Value :'||p_column_value, 35);
418: hr_utility.set_location('..p_error_message :'||p_error_message, 40);
419: hr_utility.set_location('Leaving : '||l_proc_name, 80);
420:
421: RETURN 0;
414: END IF;
415:
416: hr_utility.set_location('..Column Name :'||p_column_name , 30);
417: hr_utility.set_location('..Column Value :'||p_column_value, 35);
418: hr_utility.set_location('..p_error_message :'||p_error_message, 40);
419: hr_utility.set_location('Leaving : '||l_proc_name, 80);
420:
421: RETURN 0;
422:
415:
416: hr_utility.set_location('..Column Name :'||p_column_name , 30);
417: hr_utility.set_location('..Column Value :'||p_column_value, 35);
418: hr_utility.set_location('..p_error_message :'||p_error_message, 40);
419: hr_utility.set_location('Leaving : '||l_proc_name, 80);
420:
421: RETURN 0;
422:
423: EXCEPTION
421: RETURN 0;
422:
423: EXCEPTION
424: WHEN OTHERS THEN
425: hr_utility.set_location('Error when others : '||l_proc_name, 90);
426: hr_utility.set_location('Leaving : '||l_proc_name, 95);
427: p_error_message := 'Error occured while fetching values for Pension Type';
428: RETURN 1;
429:
422:
423: EXCEPTION
424: WHEN OTHERS THEN
425: hr_utility.set_location('Error when others : '||l_proc_name, 90);
426: hr_utility.set_location('Leaving : '||l_proc_name, 95);
427: p_error_message := 'Error occured while fetching values for Pension Type';
428: RETURN 1;
429:
430: END get_pension_type_details;
598: ELSE
599: l_payroll_period := p_payroll_period;
600: END IF;
601:
602: l_debug := hr_utility.debug_enabled;
603:
604:
605: IF l_debug THEN
606: pqp_utilities.debug(' p_business_group_id is ' || p_business_group_id );
3127: AND special_pension_type_code = 'ABP';
3128:
3129: BEGIN
3130:
3131: hr_utility.set_location('Entering : '||l_proc_name, 10);
3132:
3133: l_date_earned := p_date_earned;
3134:
3135: --check if the pension type is an ABP Pension Type
3186: WHERE assignment_id = p_assignment_id
3187: AND trunc(p_date_earned) BETWEEN effective_start_date
3188: AND effective_end_date;
3189:
3190: hr_utility.set_location('Fetched l_cur_payroll_id : '||to_char(l_cur_payroll_id),20);
3191:
3192: --
3193: -- Get the hire date
3194: --
3212: -- the hire date and the participation start date
3213: l_effective_date := GREATEST(l_begin_of_year_date,l_hire_date,l_participation_st_dt);
3214: END IF;
3215:
3216: hr_utility.set_location('Fetched l_effective_date : '
3217: ||to_char(l_effective_date),30);
3218:
3219: -- For the payroll id derived from above, get the time_period_id,
3220: -- start and end dates for the period which has the start_date
3226: END IF; -- Hire date found
3227:
3228: FOR temp_rec IN c_per_time_cur (l_payroll_id,l_effective_date)
3229: LOOP
3230: hr_utility.set_location('Fetched l_time_period_id : '
3231: ||to_char(l_time_period_id),50);
3232:
3233: -- Check if there is a completed payroll run for the time
3234: -- period, payroll and assignment combination.
3584: l_person_id NUMBER;
3585:
3586: begin
3587:
3588: hr_utility.set_location('Entering the function get_pension_threshold_ratio',10);
3589: open c_get_person_id;
3590:
3591: fetch c_get_person_id into l_person_id;
3592:
3589: open c_get_person_id;
3590:
3591: fetch c_get_person_id into l_person_id;
3592:
3593: hr_utility.set_location('got person id '||l_person_id,20);
3594:
3595: if c_get_person_id%NOTFOUND THEN
3596:
3597: close c_get_person_id;
3601: fnd_message.raise_error;
3602:
3603: else
3604:
3605: hr_utility.set_location('get all assignment ids ',30);
3606:
3607: close c_get_person_id;
3608: open c_get_all_assignmentid(c_person_id => l_person_id);
3609:
3629: FETCH c_get_fte INTO l_fte;
3630: CLOSE c_get_fte;
3631: END IF;
3632:
3633: hr_utility.set_location('got fte value '||l_fte||'for assignment'||l_assignment_id,40);
3634:
3635: -- sum the FTEs of each of the assignments
3636: l_sum_fte := l_sum_fte + nvl(l_fte,100);
3637:
3638: end loop;
3639:
3640: close c_get_all_assignmentid;
3641:
3642: hr_utility.set_location('got sum of ftes'||l_sum_fte,50);
3643:
3644: --
3645: -- Call function to check if the user has overridden the part
3646: -- time percentage . If a value is found , use this to derive the
3663: if not l_sum_fte = 0 THEN
3664:
3665: l_ratio := l_fte/l_sum_fte;
3666:
3667: hr_utility.set_location('got ratio'||l_ratio,60);
3668:
3669: end if;
3670: end if;
3671:
3668:
3669: end if;
3670: end if;
3671:
3672: hr_utility.set_location('leaving the function get_pension_threshold_ratio',70);
3673:
3674: return l_ratio;
3675:
3676: end get_pension_threshold_ratio;
3791: trunc(effective_start_date)
3792: AND trunc(effective_end_date);
3793:
3794: BEGIN
3795: hr_utility.set_location('Entering gen_dynamic_formula',10);
3796: FOR temp_rec IN c_tax_si_cur
3797: LOOP
3798: l_tax_si_red_str := temp_rec.redstr;
3799: END LOOP;
3852: to_number(substr(l_tax_si_red_str,8,1));
3853: l_sii_tbl_indx := l_sii_tbl_indx+1;
3854: END IF;
3855:
3856: hr_utility.set_location('Calling sort on all 3 tables',20);
3857: sort_table(l_tax_bal_tbl);
3858: sort_table(l_si_gross_sal_tbl);
3859: sort_table(l_si_incm_bal_tbl);
3860:
3860:
3861:
3862: IF l_tax_bal_tbl.count > 0 THEN
3863:
3864: hr_utility.set_location('Taxation balances exist in the reduction',30);
3865: IF l_tax_bal_tbl.count = 1 THEN
3866: l_formula_string := l_single_bal_string;
3867: ELSIF l_tax_bal_tbl.count = 2 THEN
3868: l_formula_string := l_two_bal_string;
3904: l_counter := 1;
3905:
3906: IF l_si_incm_bal_tbl.count > 0 THEN
3907:
3908: hr_utility.set_location('SI Income balances exist in the reduction',40);
3909: IF l_si_incm_bal_tbl.count = 1 THEN
3910: l_formula_string := l_formula_string||l_single_bal_string;
3911: ELSIF l_si_incm_bal_tbl.count = 2 THEN
3912: l_formula_string := l_formula_string||l_two_bal_string;
3980: l_counter := 1;
3981:
3982: IF l_si_gross_sal_tbl.count > 0 THEN
3983:
3984: hr_utility.set_location('SI Gross Salary balances exist in the reduction',50);
3985: IF l_si_gross_sal_tbl.count = 1 THEN
3986: l_formula_string := l_formula_string||l_single_bal_string;
3987: ELSIF l_si_gross_sal_tbl.count = 2 THEN
3988: l_formula_string := l_formula_string||l_two_bal_string;
4052: END LOOP;
4053:
4054: END IF;
4055: p_formula_string := l_formula_string;
4056: hr_utility.set_location('Leaving gen_dynamic_formula',50);
4057:
4058: END gen_dynamic_formula;
4059:
4060: -- ----------------------------------------------------------------------------
4144: trunc(effective_start_date)
4145: AND trunc(effective_end_date);
4146:
4147: BEGIN
4148: hr_utility.set_location('Entering gen_dynamic_formula',10);
4149: FOR temp_rec IN c_tax_si_cur
4150: LOOP
4151: l_tax_si_red_str := temp_rec.redstr;
4152: END LOOP;
4205: to_number(substr(l_tax_si_red_str,8,1));
4206: l_sii_tbl_indx := l_sii_tbl_indx+1;
4207: END IF;
4208:
4209: hr_utility.set_location('Calling sort on all 3 tables',20);
4210: sort_table(l_tax_bal_tbl);
4211: sort_table(l_si_gross_sal_tbl);
4212: sort_table(l_si_incm_bal_tbl);
4213:
4213:
4214:
4215: IF l_tax_bal_tbl.count > 0 THEN
4216:
4217: hr_utility.set_location('Taxation balances exist in the reduction',30);
4218: IF l_tax_bal_tbl.count = 1 THEN
4219: l_formula_string := l_single_bal_string;
4220: ELSIF l_tax_bal_tbl.count = 2 THEN
4221: l_formula_string := l_two_bal_string;
4257: l_counter := 1;
4258:
4259: IF l_si_incm_bal_tbl.count > 0 THEN
4260:
4261: hr_utility.set_location('SI Income balances exist in the reduction',40);
4262: IF l_si_incm_bal_tbl.count = 1 THEN
4263: l_formula_string := l_formula_string||l_single_bal_string;
4264: ELSIF l_si_incm_bal_tbl.count = 2 THEN
4265: l_formula_string := l_formula_string||l_two_bal_string;
4333: l_counter := 1;
4334:
4335: IF l_si_gross_sal_tbl.count > 0 THEN
4336:
4337: hr_utility.set_location('SI Gross Salary balances exist in the reduction',50);
4338: IF l_si_gross_sal_tbl.count = 1 THEN
4339: l_formula_string := l_formula_string||l_single_bal_string;
4340: ELSIF l_si_gross_sal_tbl.count = 2 THEN
4341: l_formula_string := l_formula_string||l_two_bal_string;
4405: END LOOP;
4406:
4407: END IF;
4408: p_formula_string := l_formula_string;
4409: hr_utility.set_location('Leaving gen_dynamic_formula',50);
4410:
4411: END gen_dynamic_sav_formula;
4412:
4413:
4962: END IF;
4963:
4964: --
4965: IF g_ptp_formula_exists = TRUE THEN
4966: -- hr_utility.trace('FORMULA EXISTS');
4967: --
4968: l_inputs(1).name := 'ASSIGNMENT_ID';
4969: l_inputs(1).value := p_assignment_id;
4970: l_inputs(2).name := 'DATE_EARNED';
4987: p_outputs => l_outputs);
4988: --
4989: l_part_time_perc := l_outputs(1).value;
4990:
4991: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
4992: ELSE
4993: -- hr_utility.trace('FORMULA DOESNT EXISTS');
4994: l_part_time_perc := NULL;
4995:
4989: l_part_time_perc := l_outputs(1).value;
4990:
4991: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
4992: ELSE
4993: -- hr_utility.trace('FORMULA DOESNT EXISTS');
4994: l_part_time_perc := NULL;
4995:
4996: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
4997:
4992: ELSE
4993: -- hr_utility.trace('FORMULA DOESNT EXISTS');
4994: l_part_time_perc := NULL;
4995:
4996: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
4997:
4998: END IF;
4999: ELSIF g_ptp_formula_exists = FALSE THEN
5000: l_part_time_perc := NULL;
5049: END IF;
5050:
5051: --
5052: IF g_ptp_formula_exists = TRUE THEN
5053: -- hr_utility.trace('FORMULA EXISTS');
5054: --
5055: l_inputs(1).name := 'ASSIGNMENT_ID';
5056: l_inputs(1).value := p_assignment_id;
5057: l_inputs(2).name := 'DATE_EARNED';
5074: p_outputs => l_outputs);
5075: --
5076: l_part_time_perc := l_outputs(1).value;
5077:
5078: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5079: ELSE
5080: -- hr_utility.trace('FORMULA DOESNT EXISTS');
5081: l_part_time_perc := NULL;
5082:
5076: l_part_time_perc := l_outputs(1).value;
5077:
5078: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5079: ELSE
5080: -- hr_utility.trace('FORMULA DOESNT EXISTS');
5081: l_part_time_perc := NULL;
5082:
5083: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5084:
5079: ELSE
5080: -- hr_utility.trace('FORMULA DOESNT EXISTS');
5081: l_part_time_perc := NULL;
5082:
5083: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5084:
5085: END IF;
5086: ELSIF g_ptp_formula_exists = FALSE THEN
5087: l_part_time_perc := NULL;
5304: default -99;
5305: l_proc_name varchar2(30) := 'Get_Version_id';
5306:
5307: BEGIN
5308: hr_utility.set_location('Entering : '||l_proc_name,10);
5309: --first chk to see if a named hierarchy exists for the BG
5310: OPEN c_find_named_hierarchy;
5311: FETCH c_find_named_hierarchy INTO l_named_hierarchy;
5312: -- if a named hiearchy is found , find the valid version on that date
5313:
5314: IF c_find_named_hierarchy%FOUND THEN
5315:
5316: CLOSE c_find_named_hierarchy;
5317: hr_utility.set_location('Found named hierarchy : '||l_named_hierarchy,20);
5318: -- now find the valid version on that date
5319: OPEN c_find_ver_frm_hierarchy(l_named_hierarchy);
5320: FETCH c_find_ver_frm_hierarchy INTO l_version_id;
5321:
5322: --if no valid version is found, try to get it frm the BG
5323: IF c_find_ver_frm_hierarchy%NOTFOUND THEN
5324:
5325: CLOSE c_find_ver_frm_hierarchy;
5326: hr_utility.set_location('No valid version was found for the named hierarchy',30);
5327: -- find the valid version id from the BG
5328: OPEN c_find_ver_frm_bg;
5329: FETCH c_find_ver_frm_bg INTO l_version_id;
5330: CLOSE c_find_ver_frm_bg;
5327: -- find the valid version id from the BG
5328: OPEN c_find_ver_frm_bg;
5329: FETCH c_find_ver_frm_bg INTO l_version_id;
5330: CLOSE c_find_ver_frm_bg;
5331: hr_utility.set_location('Found the version from the BG : '||l_version_id,40);
5332:
5333: -- else a valid version has been found for the named hierarchy
5334: ELSE
5335:
5333: -- else a valid version has been found for the named hierarchy
5334: ELSE
5335:
5336: CLOSE c_find_ver_frm_hierarchy;
5337: hr_utility.set_location('Found the version for named hierarchy : '||l_version_id,50);
5338:
5339: END IF; --end of if no valid version found
5340:
5341: -- else find the valid version from BG
5341: -- else find the valid version from BG
5342: ELSE
5343:
5344: CLOSE c_find_named_hierarchy;
5345: hr_utility.set_location('No named hierarchy exists',60);
5346: --now find the version number from the BG
5347: OPEN c_find_ver_frm_bg;
5348: FETCH c_find_ver_frm_bg INTO l_version_id;
5349: CLOSE c_find_ver_frm_bg;
5346: --now find the version number from the BG
5347: OPEN c_find_ver_frm_bg;
5348: FETCH c_find_ver_frm_bg INTO l_version_id;
5349: CLOSE c_find_ver_frm_bg;
5350: hr_utility.set_location('Found the version from the BG : '||l_version_id,60);
5351:
5352: END IF; -- end of if named hierarchy found
5353:
5354: RETURN nvl(l_version_id,-99);
5407: AND asg.assignment_id = p_assignment_id;
5408:
5409: BEGIN
5410:
5411: hr_utility.set_location('Entering : '||l_proc_name, 10);
5412:
5413: --
5414: -- Derive the assignment start date
5415: --
5416: OPEN c_get_assign_start_date;
5417: FETCH c_get_assign_start_date INTO l_asg_st_dt;
5418: CLOSE c_get_assign_start_date;
5419:
5420: hr_utility.set_location('.....Assignment Id : '||p_assignment_id, 13);
5421: hr_utility.set_location('.....Assignment Start : '||l_asg_st_dt, 15);
5422:
5423: --
5424: -- Derive the greater of effective start date and assignment start date
5417: FETCH c_get_assign_start_date INTO l_asg_st_dt;
5418: CLOSE c_get_assign_start_date;
5419:
5420: hr_utility.set_location('.....Assignment Id : '||p_assignment_id, 13);
5421: hr_utility.set_location('.....Assignment Start : '||l_asg_st_dt, 15);
5422:
5423: --
5424: -- Derive the greater of effective start date and assignment start date
5425: --
5424: -- Derive the greater of effective start date and assignment start date
5425: --
5426: l_eff_dt := GREATEST(NVL(l_asg_st_dt,p_period_start_date),p_period_start_date);
5427:
5428: hr_utility.set_location('.....Pay Period Start : '||p_period_start_date, 20);
5429: hr_utility.set_location('.....Effective Date : '||l_eff_dt, 25);
5430:
5431: OPEN get_dob;
5432: FETCH get_dob INTO l_dob;
5425: --
5426: l_eff_dt := GREATEST(NVL(l_asg_st_dt,p_period_start_date),p_period_start_date);
5427:
5428: hr_utility.set_location('.....Pay Period Start : '||p_period_start_date, 20);
5429: hr_utility.set_location('.....Effective Date : '||l_eff_dt, 25);
5430:
5431: OPEN get_dob;
5432: FETCH get_dob INTO l_dob;
5433: CLOSE get_dob;
5435:
5436: --
5437: l_dob := NVL(l_dob,p_date_earned);
5438:
5439: hr_utility.set_location('.....Birth Date : '||l_dob, 30);
5440: --
5441: l_age := TRUNC(MONTHS_BETWEEN(l_eff_dt,l_dob)/12,6);
5442:
5443: hr_utility.set_location('.....Age is : '||l_age, 35);
5439: hr_utility.set_location('.....Birth Date : '||l_dob, 30);
5440: --
5441: l_age := TRUNC(MONTHS_BETWEEN(l_eff_dt,l_dob)/12,6);
5442:
5443: hr_utility.set_location('.....Age is : '||l_age, 35);
5444:
5445: hr_utility.set_location('Leaving : '||l_proc_name, 40);
5446:
5447: RETURN(l_age);
5441: l_age := TRUNC(MONTHS_BETWEEN(l_eff_dt,l_dob)/12,6);
5442:
5443: hr_utility.set_location('.....Age is : '||l_age, 35);
5444:
5445: hr_utility.set_location('Leaving : '||l_proc_name, 40);
5446:
5447: RETURN(l_age);
5448: --
5449:
5449:
5450: EXCEPTION
5451: WHEN OTHERS THEN
5452:
5453: hr_utility.set_location('Leaving with errors: '||l_proc_name, 50);
5454: RETURN 0;
5455:
5456: END get_pay_period_age;
5457: