1 PACKAGE BODY PAY_PAYACVEA_PKG as
2 /* $Header: pyacvea.pkb 115.1 99/07/17 05:41:39 porting ship $ */
3 --
4 --
5 PROCEDURE get_dates(
6 p_element_entry_id IN number,
7 p_payroll_id IN number,
8 p_session_date IN date,
9 p_person_id IN number,
10 p_start_date OUT date,
11 p_end_date OUT date
12 ) IS
13 --
14 return_end_date date;
15 l_end_date date;
16 l_year varchar2(4);
17 --
18 BEGIN
19 --
20 -- This procedure gets the start and end dates for the period covered by
21 -- the accrual.
22 --
23 -- The start date is whichever is the later - the first period of the year,
24 -- or when the employee was enrolled onto the plan (ie when the accrual
25 -- element was given to the employee).
26 --
27 -- Since accruals are based on complete time periods we have a slight problem
28 -- in determining what year we are in - it is not enough to get the year from
29 -- the effective date, since the effective date may be halfway into the first
30 -- period of the year: for example, if we use monthly periods and the effective
31 -- date is '20-01-1994' then we should use 1993 as the accrual year, since
32 -- the effective date has not covered a whole accrual period for 1994.
33 -- If the effective date falls on the last day of the first period (in this
34 -- example '31-01-1994') then we have a whole accrual period and we can use
35 -- 1994 as the accrual year. Obviously, if the effective date is later than the
36 -- end date of the first period of the year then there's no problem.
37 --
38 -- The end date requires a little more work - this should be the earliest of the
39 -- following dates -
40 --
41 -- the effective date
42 --
43 -- (GET RID OF THIS ????)
44 -- the end date of the period in which the employee left the plan
45 --
46 -- the end date of the period in which the employee's period of service ended
47 --
48 --
49 -- find out what year we're dealing with...
50 --
51 select to_char(ptp.start_date, 'YYYY')
52 into l_year
53 from per_time_periods ptp
54 where p_session_date between
55 ptp.start_date and ptp.end_date
56 and ptp.payroll_id = p_payroll_id;
57 --
58 -- get the period start date
59 --
60 hr_utility.set_location('pay_payacvea_pkg.get_dates', 1);
61 select greatest(ptp1.start_date, ptp2.start_date)
62 into p_start_date
63 from per_time_periods ptp1,
64 per_time_periods ptp2
65 where ptp1.start_date =
66 (
67 select min(ptp3.start_date)
68 from per_time_periods ptp3
69 where ptp3.start_date >=
70 (
71 select min(effective_start_date)
72 from pay_element_entries_f pee
73 where pee.element_entry_id = p_element_entry_id
74 )
75 and ptp3.payroll_id = ptp1.payroll_id
76 )
77 and ptp1.payroll_id = p_payroll_id
78 and ptp2.start_date =
79 (
80 select min(ptp3.start_date)
81 from per_time_periods ptp3
82 where ptp3.start_date >=
83 to_date('01-01-'||l_year, 'DD-MM-YYYY')
84 and ptp3.payroll_id = ptp2.payroll_id
85 )
86 and ptp2.payroll_id = p_payroll_id;
87 --
88 -- get the end date (ie the effective date)
89 --
90 return_end_date := p_session_date;
91 --
92 /*
93 *****
94 ***** REMOVE ???
95 *****
96 -- now get the end date of the period in which the element is no longer
97 -- effective; this may return no value
98 --
99 hr_utility.set_location('pay_payacvea_pkg.get_dates', 3);
100 begin
101 select ptp1.end_date
102 into l_end_date
103 from per_time_periods ptp1
104 where ptp1.end_date =
105 (
106 select max(ptp2.end_date)
107 from per_time_periods ptp2
108 where ptp2.end_date <=
109 (
110 select max(effective_end_date)
111 from pay_element_entries_f pee
112 where pee.element_entry_id = p_element_entry_id
113 )
114 and ptp2.payroll_id = ptp1.payroll_id
115 )
116 and ptp1.payroll_id = p_payroll_id;
117 EXCEPTION
118 when NO_DATA_FOUND then null;
119 end;
120 if l_end_date is not null then
121 if l_end_date < return_end_date then
122 return_end_date := l_end_date;
123 end if;
124 end if;
125 */
126 --
127 -- ...and now the period in which the employee's period of service ends
128 --
129 l_end_date := null;
130 hr_utility.set_location('pay_payacvea_pkg.get_dates', 4);
131 begin
132 select ptp1.end_date
133 into l_end_date
134 from per_time_periods ptp1
135 where ptp1.end_date =
136 (
137 select max(ptp2.end_date)
138 from per_periods_of_service pos,
139 per_time_periods ptp2
140 where p_session_date between
141 pos.date_start and pos.actual_termination_date
142 and pos.person_id = p_person_id
143 and pos.actual_termination_date <= ptp2.end_date
144 and ptp2.payroll_id = ptp1.payroll_id
145 )
146 and ptp1.payroll_id = p_payroll_id;
147 EXCEPTION
148 when NO_DATA_FOUND then null;
149 end;
150 if l_end_date is not null then
151 if l_end_date < return_end_date then
152 return_end_date := l_end_date;
153 end if;
154 end if;
155 --
156 p_end_date := return_end_date;
157 --
158 --
159 --
160 END get_dates;
161 --
162 --
163 --
164 END PAY_PAYACVEA_PKG;
[Home] [Help]