1 package body py_za_coida_pkg as
2 /* $Header: pyzacoid.pkb 120.3 2006/06/15 09:29:22 amahanty ship $ */
3 -- *****************************************************************************
4 -- This function calculates the total number of working days during a given
5 -- period. When this function is called the user has to specify if Saturday
6 -- is a working day.
7 -- *****************************************************************************
8
9 Function get_working_days
10 (
11 p_period_start in date
12 ,p_period_end in date
13 )
14 Return number
15 as
16
17 l_tot_work_days number;
18 l_next_date date; -- next date to process
19 l_days varchar2(10);
20
21
22 Begin
23
24 -- Initialise some variables
25
26 l_tot_work_days := 0;
27 l_next_date := p_period_start;
28
29 Loop
30
31 If l_next_date > p_period_end then
32 exit;
33 End if;
34
35 l_tot_work_days := l_tot_work_days + 1;
36
37 -- go to next day
38 l_next_date := l_next_date + 1;
39
40 End Loop;
41
42 Return (l_tot_work_days);
43
44 End get_working_days;
45
46 -- *****************************************************************************
47 -- This function calculates the total number of absence days for all employees
48 -- within a given period (for a specific absence type).
49 -- *****************************************************************************
50 /* Bug 3612045
51 Function get_emp_absence
52 (
53 p_start_date date
54 ,p_end_date date
55 ,p_type varchar2
56 )
57 Return number
58 as
59
60 l_tot_absence_days number := 0;
61
62 Cursor c_absences is
63 Select
64 sum(absence_days)
65 From
66 per_absence_attendances paa
67 , per_absence_attendance_types pat
68 Where
69 pat.absence_attendance_type_id = paa.absence_attendance_type_id
70 And upper(pat.name) = upper(p_type)
71 And paa.date_start >= p_start_date
72 And paa.date_end <= p_end_date
73 And paa.date_end >= p_start_date
74 And paa.date_end <= p_end_date;
75
76 Begin
77
78 Open c_absences;
79 Fetch c_absences into l_tot_absence_days;
80 Close c_absences;
81
82 Return (l_tot_absence_days);
83
84 End get_emp_absence;
85 */
86 -- *****************************************************************************
87 -- This function calculates the total number of days actually worked by
88 -- a specified person_id. It accepts a period start, period end date,
89 -- payroll id and person id as parameters.
90 -- If the employee start date is earlier than the period start the function will
91 -- use the period start date when calculating the number of days worked, else it
92 -- would use the employee start date. If the period end date is before the
93 -- employee end date the function will use the period end date to calculate the
94 -- number of days worked, else it will use the employee end date.
95 -- Also one should keep in mind that the assignment dates cannot be used since
96 -- one person may have multiple assignments.
97 -- *****************************************************************************
98
99 Function get_emp_days_worked
100 (
101 p_start_date date
102 ,p_end_date date
103 ,p_payroll_id number
104 ,p_person_id number default null
105 )
106 Return number
107 as
108
109 l_tot_days_worked number := 0;
110 l_assignment_id number;
111 l_effective_start_date date;
112 l_effective_end_date date;
113 l_assignment_status number;
114
115 lp_assignment_id number := 0;
116 lp_effective_start_date date := to_date('01-01-1001', 'DD-MM-YYYY');
117 lp_effective_end_date date := to_date('01-01-1001', 'DD-MM-YYYY');
118
119 l_count number := 0;
120
121 -- Get the employee record(s) to process
122 Cursor c_get_emps is
123
124 Select
125 paf.assignment_id,
126 paf.effective_start_date,
127 paf.effective_end_date,
128 paf.assignment_status_type_id
129 From
130 per_assignments_f paf,
131 per_assignment_status_types past,
132 pay_payrolls_f ppaf,
133 per_periods_of_service pos
134 Where
135 -------------------------------------------------------------------------------------
136 pos.PERSON_ID = paf.PERSON_ID
137 and nvl(pos.actual_termination_date, paf.effective_end_date) >= paf.EFFECTIVE_END_DATE
138 and paf.ASSIGNMENT_TYPE = 'E'
139 -------------------------------------------------------------------------------------
140 -- Per_Assignments_F
141 -- paf.person_id = nvl(p_person_id,paf.person_id)
142 And paf.person_id = p_person_id
143 And paf.payroll_id = p_payroll_id
144 And p_start_date <= paf.effective_end_date
145 And p_end_date >= paf.effective_start_date
146
147 And paf.assignment_status_type_id = past.assignment_status_type_id
148 And past.per_system_status = 'ACTIVE_ASSIGN'
149
150 -- Per_Payrolls_F
151 And paf.payroll_id = ppaf.payroll_id
152 And p_start_date <= ppaf.effective_end_date
153 And p_end_date >= ppaf.effective_start_date
154 Order by
155 2;
156
157 Begin
158
159 Open c_get_emps;
160 Fetch c_get_emps into l_assignment_id, l_effective_start_date,
161 l_effective_end_date, l_assignment_status;
162
163 If p_person_id is not null then
164 Loop -- There may be more than one record
165 Exit when c_get_emps%notfound; -- for each employee i.e. broken service
166
167 if (l_effective_start_date <= lp_effective_end_date)
168 And (l_effective_end_date > lp_effective_end_date) then
169 l_effective_start_date := lp_effective_end_date + 1;
170 Else
171 null;
172 End if;
173
174 If l_effective_end_date <= lp_effective_end_date then
175 l_effective_end_date := lp_effective_end_date;
176 Else
177 null;
178 End if;
179
180 -- check the start date
181 If l_effective_start_date < p_start_date then
182 -- use p_start_date, the start of the period
183 l_effective_start_date := p_start_date;
184 Else
185 -- use l_effective_start_date, employee start date
186 null;
187 End if;
188
189 -- check the end date
190 If p_end_date < l_effective_end_date then
191 -- use p_end_date, the end of the period
192 l_effective_end_date := p_end_date;
193 Else
194 -- use l_effective_end_date, employee end date
195 null;
196 End if;
197
198 -- now calculate the days worked and increment the total
199 -- This is only done when the end date is not equal to the prev end date
200 If l_effective_end_date <> lp_effective_end_date then
201 l_count := l_count + py_za_coida_pkg.get_working_days
202 (
203 l_effective_start_date,
204 l_effective_end_date
205 );
206 End if;
207
208
209 lp_effective_start_date := l_effective_start_date;
210 lp_effective_end_date := l_effective_end_date;
211
212 -- There may be more!
213 Fetch c_get_emps into l_assignment_id, l_effective_start_date,
214 l_effective_end_date, l_assignment_status;
215 End Loop;
216
217 Else
218
219 Loop -- This loop is when there is no
220 Exit when c_get_emps%notfound; -- emp id i.e. all employees
221
222 -- check the start date
223 If l_effective_start_date < p_start_date then
224 -- use p_start_date, the start of the period
225 l_effective_start_date := p_start_date;
226 Else
227 -- use l_effective_start_date, employee start date
228 null;
229 End if;
230
231 -- check the end date
232 If p_end_date < l_effective_end_date then
233 -- use p_end_date, the end of the period
234 l_effective_end_date := p_end_date;
235 Else
236 -- use l_effective_end_date, employee end date
237 null;
238 End if;
239
240 -- now calculate the days worked and increment the total
241 l_count := l_count + py_za_coida_pkg.get_working_days
242 (
243 l_effective_start_date,
244 l_effective_end_date
245 );
246
247 -- get the next employee record to process
248 Fetch c_get_emps into l_assignment_id, l_effective_start_date,
249 l_effective_end_date, l_assignment_status;
250
251 End Loop;
252
253 End if;
254
255 -- Total for all employees
256 l_tot_days_worked := l_count;
257
258 Close c_get_emps;
259
260 Return (l_tot_days_worked);
261
262 End get_emp_days_worked;
263
264 End py_za_coida_pkg;
[Home] [Help]