1 PACKAGE BODY pay_ca_vac_bank AS
2 /* $Header: pycavbvb.pkb 120.0 2005/05/29 03:53:36 appldev noship $ */
3
4 -------------------------- calc_years_of_service ---------------------------
5 /*
6 * NAME
7 * calc_years_of_service
8 * DESCRIPTION
9 * This is a function that calculates the effective years of service,
10 * rounded down to a whole year, based on the entered DATE_TYPE.
11 * 'Hire Date' - The years of service is calculated using the hire date on
12 * PER_PERIODS_OF_SERVICE as the starting_point
13 */
14 FUNCTION calc_years_of_service(p_assignment_id NUMBER,
15 p_date_earned DATE,
16 p_date_type VARCHAR2)
17 RETURN NUMBER IS
18
19 CURSOR c_years_of_service IS
20 SELECT TRUNC(MONTHS_BETWEEN(p_date_earned, pds.date_start)/12)
21 FROM per_all_assignments_f asg,
22 per_periods_of_service pds
23 WHERE asg.assignment_id = p_assignment_id
24 AND p_date_earned BETWEEN asg.effective_start_date
25 AND asg.effective_end_date
26 AND pds.person_id = asg.person_id;
27
28 l_years_of_service NUMBER := 0;
29
30 BEGIN
31
32 hr_utility.set_location('Starting calc_years_of_service', 10);
33 IF p_date_type = 'HD' THEN
34 hr_utility.set_location('calc_years_of_service', 20);
35 OPEN c_years_of_service;
36 FETCH c_years_of_service INTO l_years_of_service;
37 CLOSE c_years_of_service;
38 END IF;
39 hr_utility.set_location('Ending calc_years_of_service', 40);
40
41 RETURN l_years_of_service;
42
43 END;
44
45 END pay_ca_vac_bank;
[Home] [Help]