[Home] [Help]
44:
45: flag BOOLEAN := FALSE;
46:
47: BEGIN
48: hr_utility.set_location('Entering check_payroll_period_type', 10);
49:
50: FOR l_payroll_period IN c_get_payroll_period LOOP
51: IF p_payroll_period_input LIKE '%'||l_payroll_period.display_period_type THEN
52: flag:=TRUE;
49:
50: FOR l_payroll_period IN c_get_payroll_period LOOP
51: IF p_payroll_period_input LIKE '%'||l_payroll_period.display_period_type THEN
52: flag:=TRUE;
53: hr_utility.set_location('l_payroll_period.display_period_type : '||l_payroll_period.display_period_type,20);
54: hr_utility.set_location('p_payroll_period_input : '||p_payroll_period_input,30);
55: EXIT;
56: END IF;
57: END LOOP;
50: FOR l_payroll_period IN c_get_payroll_period LOOP
51: IF p_payroll_period_input LIKE '%'||l_payroll_period.display_period_type THEN
52: flag:=TRUE;
53: hr_utility.set_location('l_payroll_period.display_period_type : '||l_payroll_period.display_period_type,20);
54: hr_utility.set_location('p_payroll_period_input : '||p_payroll_period_input,30);
55: EXIT;
56: END IF;
57: END LOOP;
58: hr_utility.set_location('Leaving check_payroll_period_type', 90);
54: hr_utility.set_location('p_payroll_period_input : '||p_payroll_period_input,30);
55: EXIT;
56: END IF;
57: END LOOP;
58: hr_utility.set_location('Leaving check_payroll_period_type', 90);
59: RETURN flag;
60:
61: EXCEPTION
62: WHEN OTHERS THEN
59: RETURN flag;
60:
61: EXCEPTION
62: WHEN OTHERS THEN
63: hr_utility.set_location('Exception in Leaving check_payroll_period_type', 50);
64: hr_utility.set_location(SQLERRM||SQLCODE, 60);
65: RETURN FALSE;
66: END check_payroll_period_type;
67: -------------------------------Bug 12578980-----------------------------------
60:
61: EXCEPTION
62: WHEN OTHERS THEN
63: hr_utility.set_location('Exception in Leaving check_payroll_period_type', 50);
64: hr_utility.set_location(SQLERRM||SQLCODE, 60);
65: RETURN FALSE;
66: END check_payroll_period_type;
67: -------------------------------Bug 12578980-----------------------------------
68:
131: l_er_age_threshold pqp_pension_types_f.er_age_threshold%TYPE;
132:
133: BEGIN
134:
135: hr_utility.set_location('Entering : '||l_proc_name, 10);
136:
137: l_pension_id := p_pension_type_id;
138:
139: --
139: --
140: -- Check if the pension_type_id is already in cache
141: --
142: IF NOT g_pension_rec.EXISTS(l_pension_id) THEN
143: hr_utility.set_location('..Pension Id :'||l_pension_id
144: ||' does not exists',15);
145: g_pension_rec.DELETE;
146: OPEN c_pty_cur (c_business_group_id => p_business_group_id
147: ,c_pension_type_id => p_pension_type_id
158: AND g_pension_rec(l_pension_id).effective_end_date
159: AND g_pension_rec(l_pension_id).pension_type_id = l_pension_id
160: AND g_pension_rec(l_pension_id).business_group_id = p_business_group_id
161: ) THEN
162: hr_utility.set_location('..Pension Id :'||l_pension_id
163: ||' does exists in pl/sql table',20);
164: hr_utility.set_location('..Pension Id is not valid for given date'
165: ||p_date_earned,25);
166: g_pension_rec.DELETE(l_pension_id);
160: AND g_pension_rec(l_pension_id).business_group_id = p_business_group_id
161: ) THEN
162: hr_utility.set_location('..Pension Id :'||l_pension_id
163: ||' does exists in pl/sql table',20);
164: hr_utility.set_location('..Pension Id is not valid for given date'
165: ||p_date_earned,25);
166: g_pension_rec.DELETE(l_pension_id);
167:
168: OPEN c_pty_cur (c_business_group_id => p_business_group_id
226: IF (c_get_ee_age_threshold%FOUND
227: and l_ee_age_threshold = 'Y'
228: ) THEN
229:
230: hr_utility.set_location(' l_ee_age_threshold is '||l_ee_age_threshold,30);
231:
232: OPEN c_get_person_age;
233: FETCH c_get_person_age into l_person_year_of_birth;
234: IF (c_get_person_age%FOUND and l_person_year_of_birth IS NOT NULL) THEN
232: OPEN c_get_person_age;
233: FETCH c_get_person_age into l_person_year_of_birth;
234: IF (c_get_person_age%FOUND and l_person_year_of_birth IS NOT NULL) THEN
235:
236: hr_utility.set_location(' l_person_year_of_birth is '||l_person_year_of_birth,35);
237:
238: OPEN c_get_subcat(NVL(g_pension_rec(l_pension_id).pension_sub_category,trim(to_char(0,'9'))));
239: FETCH c_get_subcat INTO l_subcat;
240: CLOSE c_get_subcat;
238: OPEN c_get_subcat(NVL(g_pension_rec(l_pension_id).pension_sub_category,trim(to_char(0,'9'))));
239: FETCH c_get_subcat INTO l_subcat;
240: CLOSE c_get_subcat;
241:
242: hr_utility.set_location(' l_subcat is '||l_subcat,40);
243:
244: IF l_subcat IS NOT NULL THEN
245:
246: BEGIN
257: p_column_value := NVL(p_column_value,trim(to_char(0,'9')));
258:
259: EXCEPTION
260: WHEN NO_DATA_FOUND THEN
261: hr_utility.set_location('NO_DATA_FOUND for UDT : ', 90);
262:
263: p_column_value := trim(to_char(0,'9'));
264:
265: p_error_message := 'Pension Type '
272:
273: WHEN OTHERS THEN
274:
275: IF (SQLCODE = -1422) THEN
276: hr_utility.set_location('MORE THAN ONE ROW FETCHED for UDT :', 90);
277: p_column_value := trim(to_char(0,'9'));
278: p_error_message := 'The table PQP_NL_ABP_EE_ANNUAL_SALARY_THRESHOLD has '
279: ||'overlapping rows for the age of the employee.';
280:
282: RETURN 3;
283:
284: END;
285:
286: hr_utility.set_location(' p_column_value is '||p_column_value,40);
287:
288: END IF;-- subcat check
289:
290: END IF;--c_get_person_age%FOUND
298: END IF; -- c_get_ee_age_threshold%FOUND
299:
300: CLOSE c_get_ee_age_threshold;
301:
302: hr_utility.set_location(' p_error_message is '||p_error_message,45);
303:
304: ELSIF p_column_name = 'ER_ANNUAL_SALARY_THRESHOLD' THEN
305: -- p_column_value
306: -- := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).er_annual_salary_threshold)
312: IF (c_get_er_age_threshold%FOUND
313: and l_er_age_threshold = 'Y'
314: ) THEN
315:
316: hr_utility.set_location(' l_er_age_threshold is '||l_er_age_threshold,30);
317:
318: OPEN c_get_person_age;
319: FETCH c_get_person_age into l_person_year_of_birth;
320: IF (c_get_person_age%FOUND and l_person_year_of_birth IS NOT NULL) THEN
318: OPEN c_get_person_age;
319: FETCH c_get_person_age into l_person_year_of_birth;
320: IF (c_get_person_age%FOUND and l_person_year_of_birth IS NOT NULL) THEN
321:
322: hr_utility.set_location(' l_person_year_of_birth is '||l_person_year_of_birth,35);
323:
324: OPEN c_get_subcat(NVL(g_pension_rec(l_pension_id).pension_sub_category,trim(to_char(0,'9'))));
325: FETCH c_get_subcat INTO l_subcat;
326: CLOSE c_get_subcat;
324: OPEN c_get_subcat(NVL(g_pension_rec(l_pension_id).pension_sub_category,trim(to_char(0,'9'))));
325: FETCH c_get_subcat INTO l_subcat;
326: CLOSE c_get_subcat;
327:
328: hr_utility.set_location(' l_subcat is '||l_subcat,40);
329:
330: IF l_subcat IS NOT NULL THEN
331:
332: BEGIN
343: p_column_value := NVL(p_column_value,trim(to_char(0,'9')));
344:
345: EXCEPTION
346: WHEN NO_DATA_FOUND THEN
347: hr_utility.set_location('NO_DATA_FOUND for UDT : ', 90);
348:
349: p_column_value := trim(to_char(0,'9'));
350:
351: -- fnd_message.set_name('PQP','PQP_230129_PEN_AGE_ANN_SAL_THR');
363:
364: WHEN OTHERS THEN
365:
366: IF (SQLCODE = -1422) THEN
367: hr_utility.set_location('MORE THAN ONE ROW FETCHED for UDT :', 90);
368: p_column_value := trim(to_char(0,'9'));
369: p_error_message := 'The table PQP_NL_ABP_ER_ANNUAL_SALARY_THRESHOLD has '
370: ||'overlapping rows for the age of the employee.';
371:
373: RETURN 3;
374:
375: END;
376:
377: hr_utility.set_location(' p_column_value is '||p_column_value,40);
378:
379: END IF;-- subcat check
380:
381: END IF;--c_get_person_age%FOUND
389: END IF; -- c_get_er_age_threshold%FOUND
390:
391: CLOSE c_get_er_age_threshold;
392:
393: hr_utility.set_location(' p_error_message is '||p_error_message,45);
394:
395: ELSIF p_column_name = 'ANNUAL_PREMIUM_AMOUNT' THEN
396: p_column_value
397: := NVL(fnd_number.number_to_canonical(g_pension_rec(l_pension_id).annual_premium_amount)
454: ||' Column : '||p_column_name||' is invalid.';
455: RETURN 1;
456: END IF;
457:
458: hr_utility.set_location('..Column Name :'||p_column_name , 30);
459: hr_utility.set_location('..Column Value :'||p_column_value, 35);
460: hr_utility.set_location('..p_error_message :'||p_error_message, 40);
461: hr_utility.set_location('Leaving : '||l_proc_name, 80);
462:
455: RETURN 1;
456: END IF;
457:
458: hr_utility.set_location('..Column Name :'||p_column_name , 30);
459: hr_utility.set_location('..Column Value :'||p_column_value, 35);
460: hr_utility.set_location('..p_error_message :'||p_error_message, 40);
461: hr_utility.set_location('Leaving : '||l_proc_name, 80);
462:
463: RETURN 0;
456: END IF;
457:
458: hr_utility.set_location('..Column Name :'||p_column_name , 30);
459: hr_utility.set_location('..Column Value :'||p_column_value, 35);
460: hr_utility.set_location('..p_error_message :'||p_error_message, 40);
461: hr_utility.set_location('Leaving : '||l_proc_name, 80);
462:
463: RETURN 0;
464:
457:
458: hr_utility.set_location('..Column Name :'||p_column_name , 30);
459: hr_utility.set_location('..Column Value :'||p_column_value, 35);
460: hr_utility.set_location('..p_error_message :'||p_error_message, 40);
461: hr_utility.set_location('Leaving : '||l_proc_name, 80);
462:
463: RETURN 0;
464:
465: EXCEPTION
463: RETURN 0;
464:
465: EXCEPTION
466: WHEN OTHERS THEN
467: hr_utility.set_location('Error when others : '||l_proc_name, 90);
468: hr_utility.set_location('Leaving : '||l_proc_name, 95);
469: p_error_message := 'Error occured while fetching values for Pension Type';
470: RETURN 1;
471:
464:
465: EXCEPTION
466: WHEN OTHERS THEN
467: hr_utility.set_location('Error when others : '||l_proc_name, 90);
468: hr_utility.set_location('Leaving : '||l_proc_name, 95);
469: p_error_message := 'Error occured while fetching values for Pension Type';
470: RETURN 1;
471:
472: END get_pension_type_details;
640: ELSE
641: l_payroll_period := p_payroll_period;
642: END IF;
643:
644: l_debug := hr_utility.debug_enabled;
645:
646:
647: IF l_debug THEN
648: pqp_utilities.debug(' p_business_group_id is ' || p_business_group_id );
3224: AND special_pension_type_code = 'ABP';
3225:
3226: BEGIN
3227:
3228: hr_utility.set_location('Entering : '||l_proc_name, 10);
3229:
3230: l_date_earned := p_date_earned;
3231:
3232: --check if the pension type is an ABP Pension Type
3283: WHERE assignment_id = p_assignment_id
3284: AND trunc(p_date_earned) BETWEEN effective_start_date
3285: AND effective_end_date;
3286:
3287: hr_utility.set_location('Fetched l_cur_payroll_id : '||to_char(l_cur_payroll_id),20);
3288:
3289: --
3290: -- Get the hire date
3291: --
3309: -- the hire date and the participation start date
3310: l_effective_date := GREATEST(l_begin_of_year_date,l_hire_date,l_participation_st_dt);
3311: END IF;
3312:
3313: hr_utility.set_location('Fetched l_effective_date : '
3314: ||to_char(l_effective_date),30);
3315:
3316: -- For the payroll id derived from above, get the time_period_id,
3317: -- start and end dates for the period which has the start_date
3323: END IF; -- Hire date found
3324:
3325: FOR temp_rec IN c_per_time_cur (l_payroll_id,l_effective_date)
3326: LOOP
3327: hr_utility.set_location('Fetched l_time_period_id : '
3328: ||to_char(l_time_period_id),50);
3329:
3330: -- Check if there is a completed payroll run for the time
3331: -- period, payroll and assignment combination.
3681: l_person_id NUMBER;
3682:
3683: begin
3684:
3685: hr_utility.set_location('Entering the function get_pension_threshold_ratio',10);
3686: open c_get_person_id;
3687:
3688: fetch c_get_person_id into l_person_id;
3689:
3686: open c_get_person_id;
3687:
3688: fetch c_get_person_id into l_person_id;
3689:
3690: hr_utility.set_location('got person id '||l_person_id,20);
3691:
3692: if c_get_person_id%NOTFOUND THEN
3693:
3694: close c_get_person_id;
3698: fnd_message.raise_error;
3699:
3700: else
3701:
3702: hr_utility.set_location('get all assignment ids ',30);
3703:
3704: close c_get_person_id;
3705: open c_get_all_assignmentid(c_person_id => l_person_id);
3706:
3726: FETCH c_get_fte INTO l_fte;
3727: CLOSE c_get_fte;
3728: END IF;
3729:
3730: hr_utility.set_location('got fte value '||l_fte||'for assignment'||l_assignment_id,40);
3731:
3732: -- sum the FTEs of each of the assignments
3733: l_sum_fte := l_sum_fte + nvl(l_fte,100);
3734:
3735: end loop;
3736:
3737: close c_get_all_assignmentid;
3738:
3739: hr_utility.set_location('got sum of ftes'||l_sum_fte,50);
3740:
3741: --
3742: -- Call function to check if the user has overridden the part
3743: -- time percentage . If a value is found , use this to derive the
3760: if not l_sum_fte = 0 THEN
3761:
3762: l_ratio := l_fte/l_sum_fte;
3763:
3764: hr_utility.set_location('got ratio'||l_ratio,60);
3765:
3766: end if;
3767: end if;
3768:
3765:
3766: end if;
3767: end if;
3768:
3769: hr_utility.set_location('leaving the function get_pension_threshold_ratio',70);
3770:
3771: return l_ratio;
3772:
3773: end get_pension_threshold_ratio;
3888: trunc(effective_start_date)
3889: AND trunc(effective_end_date);
3890:
3891: BEGIN
3892: hr_utility.set_location('Entering gen_dynamic_formula',10);
3893: FOR temp_rec IN c_tax_si_cur
3894: LOOP
3895: l_tax_si_red_str := temp_rec.redstr;
3896: END LOOP;
3949: to_number(substr(l_tax_si_red_str,8,1));
3950: l_sii_tbl_indx := l_sii_tbl_indx+1;
3951: END IF;
3952:
3953: hr_utility.set_location('Calling sort on all 3 tables',20);
3954: sort_table(l_tax_bal_tbl);
3955: sort_table(l_si_gross_sal_tbl);
3956: sort_table(l_si_incm_bal_tbl);
3957:
3957:
3958:
3959: IF l_tax_bal_tbl.count > 0 THEN
3960:
3961: hr_utility.set_location('Taxation balances exist in the reduction',30);
3962: IF l_tax_bal_tbl.count = 1 THEN
3963: l_formula_string := l_single_bal_string;
3964: ELSIF l_tax_bal_tbl.count = 2 THEN
3965: l_formula_string := l_two_bal_string;
4001: l_counter := 1;
4002:
4003: IF l_si_incm_bal_tbl.count > 0 THEN
4004:
4005: hr_utility.set_location('SI Income balances exist in the reduction',40);
4006: IF l_si_incm_bal_tbl.count = 1 THEN
4007: l_formula_string := l_formula_string||l_single_bal_string;
4008: ELSIF l_si_incm_bal_tbl.count = 2 THEN
4009: l_formula_string := l_formula_string||l_two_bal_string;
4077: l_counter := 1;
4078:
4079: IF l_si_gross_sal_tbl.count > 0 THEN
4080:
4081: hr_utility.set_location('SI Gross Salary balances exist in the reduction',50);
4082: IF l_si_gross_sal_tbl.count = 1 THEN
4083: l_formula_string := l_formula_string||l_single_bal_string;
4084: ELSIF l_si_gross_sal_tbl.count = 2 THEN
4085: l_formula_string := l_formula_string||l_two_bal_string;
4149: END LOOP;
4150:
4151: END IF;
4152: p_formula_string := l_formula_string;
4153: hr_utility.set_location('Leaving gen_dynamic_formula',50);
4154:
4155: END gen_dynamic_formula;
4156:
4157: -- ----------------------------------------------------------------------------
4241: trunc(effective_start_date)
4242: AND trunc(effective_end_date);
4243:
4244: BEGIN
4245: hr_utility.set_location('Entering gen_dynamic_formula',10);
4246: FOR temp_rec IN c_tax_si_cur
4247: LOOP
4248: l_tax_si_red_str := temp_rec.redstr;
4249: END LOOP;
4302: to_number(substr(l_tax_si_red_str,8,1));
4303: l_sii_tbl_indx := l_sii_tbl_indx+1;
4304: END IF;
4305:
4306: hr_utility.set_location('Calling sort on all 3 tables',20);
4307: sort_table(l_tax_bal_tbl);
4308: sort_table(l_si_gross_sal_tbl);
4309: sort_table(l_si_incm_bal_tbl);
4310:
4310:
4311:
4312: IF l_tax_bal_tbl.count > 0 THEN
4313:
4314: hr_utility.set_location('Taxation balances exist in the reduction',30);
4315: IF l_tax_bal_tbl.count = 1 THEN
4316: l_formula_string := l_single_bal_string;
4317: ELSIF l_tax_bal_tbl.count = 2 THEN
4318: l_formula_string := l_two_bal_string;
4354: l_counter := 1;
4355:
4356: IF l_si_incm_bal_tbl.count > 0 THEN
4357:
4358: hr_utility.set_location('SI Income balances exist in the reduction',40);
4359: IF l_si_incm_bal_tbl.count = 1 THEN
4360: l_formula_string := l_formula_string||l_single_bal_string;
4361: ELSIF l_si_incm_bal_tbl.count = 2 THEN
4362: l_formula_string := l_formula_string||l_two_bal_string;
4430: l_counter := 1;
4431:
4432: IF l_si_gross_sal_tbl.count > 0 THEN
4433:
4434: hr_utility.set_location('SI Gross Salary balances exist in the reduction',50);
4435: IF l_si_gross_sal_tbl.count = 1 THEN
4436: l_formula_string := l_formula_string||l_single_bal_string;
4437: ELSIF l_si_gross_sal_tbl.count = 2 THEN
4438: l_formula_string := l_formula_string||l_two_bal_string;
4502: END LOOP;
4503:
4504: END IF;
4505: p_formula_string := l_formula_string;
4506: hr_utility.set_location('Leaving gen_dynamic_formula',50);
4507:
4508: END gen_dynamic_sav_formula;
4509:
4510:
5059: END IF;
5060:
5061: --
5062: IF g_ptp_formula_exists = TRUE THEN
5063: -- hr_utility.trace('FORMULA EXISTS');
5064: --
5065: l_inputs(1).name := 'ASSIGNMENT_ID';
5066: l_inputs(1).value := p_assignment_id;
5067: l_inputs(2).name := 'DATE_EARNED';
5084: p_outputs => l_outputs);
5085: --
5086: l_part_time_perc := l_outputs(1).value;
5087:
5088: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5089: ELSE
5090: -- hr_utility.trace('FORMULA DOESNT EXISTS');
5091: l_part_time_perc := NULL;
5092:
5086: l_part_time_perc := l_outputs(1).value;
5087:
5088: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5089: ELSE
5090: -- hr_utility.trace('FORMULA DOESNT EXISTS');
5091: l_part_time_perc := NULL;
5092:
5093: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5094:
5089: ELSE
5090: -- hr_utility.trace('FORMULA DOESNT EXISTS');
5091: l_part_time_perc := NULL;
5092:
5093: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5094:
5095: END IF;
5096: ELSIF g_ptp_formula_exists = FALSE THEN
5097: l_part_time_perc := NULL;
5146: END IF;
5147:
5148: --
5149: IF g_ptp_formula_exists = TRUE THEN
5150: -- hr_utility.trace('FORMULA EXISTS');
5151: --
5152: l_inputs(1).name := 'ASSIGNMENT_ID';
5153: l_inputs(1).value := p_assignment_id;
5154: l_inputs(2).name := 'DATE_EARNED';
5171: p_outputs => l_outputs);
5172: --
5173: l_part_time_perc := l_outputs(1).value;
5174:
5175: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5176: ELSE
5177: -- hr_utility.trace('FORMULA DOESNT EXISTS');
5178: l_part_time_perc := NULL;
5179:
5173: l_part_time_perc := l_outputs(1).value;
5174:
5175: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5176: ELSE
5177: -- hr_utility.trace('FORMULA DOESNT EXISTS');
5178: l_part_time_perc := NULL;
5179:
5180: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5181:
5176: ELSE
5177: -- hr_utility.trace('FORMULA DOESNT EXISTS');
5178: l_part_time_perc := NULL;
5179:
5180: -- hr_utility.trace('l_part_time_perc'||l_part_time_perc);
5181:
5182: END IF;
5183: ELSIF g_ptp_formula_exists = FALSE THEN
5184: l_part_time_perc := NULL;
5401: default -99;
5402: l_proc_name varchar2(30) := 'Get_Version_id';
5403:
5404: BEGIN
5405: hr_utility.set_location('Entering : '||l_proc_name,10);
5406: --first chk to see if a named hierarchy exists for the BG
5407: OPEN c_find_named_hierarchy;
5408: FETCH c_find_named_hierarchy INTO l_named_hierarchy;
5409: -- if a named hiearchy is found , find the valid version on that date
5410:
5411: IF c_find_named_hierarchy%FOUND THEN
5412:
5413: CLOSE c_find_named_hierarchy;
5414: hr_utility.set_location('Found named hierarchy : '||l_named_hierarchy,20);
5415: -- now find the valid version on that date
5416: OPEN c_find_ver_frm_hierarchy(l_named_hierarchy);
5417: FETCH c_find_ver_frm_hierarchy INTO l_version_id;
5418:
5419: --if no valid version is found, try to get it frm the BG
5420: IF c_find_ver_frm_hierarchy%NOTFOUND THEN
5421:
5422: CLOSE c_find_ver_frm_hierarchy;
5423: hr_utility.set_location('No valid version was found for the named hierarchy',30);
5424: -- find the valid version id from the BG
5425: OPEN c_find_ver_frm_bg;
5426: FETCH c_find_ver_frm_bg INTO l_version_id;
5427: CLOSE c_find_ver_frm_bg;
5424: -- find the valid version id from the BG
5425: OPEN c_find_ver_frm_bg;
5426: FETCH c_find_ver_frm_bg INTO l_version_id;
5427: CLOSE c_find_ver_frm_bg;
5428: hr_utility.set_location('Found the version from the BG : '||l_version_id,40);
5429:
5430: -- else a valid version has been found for the named hierarchy
5431: ELSE
5432:
5430: -- else a valid version has been found for the named hierarchy
5431: ELSE
5432:
5433: CLOSE c_find_ver_frm_hierarchy;
5434: hr_utility.set_location('Found the version for named hierarchy : '||l_version_id,50);
5435:
5436: END IF; --end of if no valid version found
5437:
5438: -- else find the valid version from BG
5438: -- else find the valid version from BG
5439: ELSE
5440:
5441: CLOSE c_find_named_hierarchy;
5442: hr_utility.set_location('No named hierarchy exists',60);
5443: --now find the version number from the BG
5444: OPEN c_find_ver_frm_bg;
5445: FETCH c_find_ver_frm_bg INTO l_version_id;
5446: CLOSE c_find_ver_frm_bg;
5443: --now find the version number from the BG
5444: OPEN c_find_ver_frm_bg;
5445: FETCH c_find_ver_frm_bg INTO l_version_id;
5446: CLOSE c_find_ver_frm_bg;
5447: hr_utility.set_location('Found the version from the BG : '||l_version_id,60);
5448:
5449: END IF; -- end of if named hierarchy found
5450:
5451: RETURN nvl(l_version_id,-99);
5504: AND asg.assignment_id = p_assignment_id;
5505:
5506: BEGIN
5507:
5508: hr_utility.set_location('Entering : '||l_proc_name, 10);
5509:
5510: --
5511: -- Derive the assignment start date
5512: --
5513: OPEN c_get_assign_start_date;
5514: FETCH c_get_assign_start_date INTO l_asg_st_dt;
5515: CLOSE c_get_assign_start_date;
5516:
5517: hr_utility.set_location('.....Assignment Id : '||p_assignment_id, 13);
5518: hr_utility.set_location('.....Assignment Start : '||l_asg_st_dt, 15);
5519:
5520: --
5521: -- Derive the greater of effective start date and assignment start date
5514: FETCH c_get_assign_start_date INTO l_asg_st_dt;
5515: CLOSE c_get_assign_start_date;
5516:
5517: hr_utility.set_location('.....Assignment Id : '||p_assignment_id, 13);
5518: hr_utility.set_location('.....Assignment Start : '||l_asg_st_dt, 15);
5519:
5520: --
5521: -- Derive the greater of effective start date and assignment start date
5522: --
5521: -- Derive the greater of effective start date and assignment start date
5522: --
5523: l_eff_dt := GREATEST(NVL(l_asg_st_dt,p_period_start_date),p_period_start_date);
5524:
5525: hr_utility.set_location('.....Pay Period Start : '||p_period_start_date, 20);
5526: hr_utility.set_location('.....Effective Date : '||l_eff_dt, 25);
5527:
5528: OPEN get_dob;
5529: FETCH get_dob INTO l_dob;
5522: --
5523: l_eff_dt := GREATEST(NVL(l_asg_st_dt,p_period_start_date),p_period_start_date);
5524:
5525: hr_utility.set_location('.....Pay Period Start : '||p_period_start_date, 20);
5526: hr_utility.set_location('.....Effective Date : '||l_eff_dt, 25);
5527:
5528: OPEN get_dob;
5529: FETCH get_dob INTO l_dob;
5530: CLOSE get_dob;
5532:
5533: --
5534: l_dob := NVL(l_dob,p_date_earned);
5535:
5536: hr_utility.set_location('.....Birth Date : '||l_dob, 30);
5537: --
5538: l_age := TRUNC(MONTHS_BETWEEN(l_eff_dt,l_dob)/12,6);
5539:
5540: hr_utility.set_location('.....Age is : '||l_age, 35);
5536: hr_utility.set_location('.....Birth Date : '||l_dob, 30);
5537: --
5538: l_age := TRUNC(MONTHS_BETWEEN(l_eff_dt,l_dob)/12,6);
5539:
5540: hr_utility.set_location('.....Age is : '||l_age, 35);
5541:
5542: hr_utility.set_location('Leaving : '||l_proc_name, 40);
5543:
5544: RETURN(l_age);
5538: l_age := TRUNC(MONTHS_BETWEEN(l_eff_dt,l_dob)/12,6);
5539:
5540: hr_utility.set_location('.....Age is : '||l_age, 35);
5541:
5542: hr_utility.set_location('Leaving : '||l_proc_name, 40);
5543:
5544: RETURN(l_age);
5545: --
5546:
5546:
5547: EXCEPTION
5548: WHEN OTHERS THEN
5549:
5550: hr_utility.set_location('Leaving with errors: '||l_proc_name, 50);
5551: RETURN 0;
5552:
5553: END get_pay_period_age;
5554: