1 package body PAY_FR_TERMINATION_PKG as
2 /* $Header: pyfrterm.pkb 120.0 2005/05/29 05:11:23 appldev noship $ */
3 /*---------------------------------------------------------------------------------------------------------------
4 This function obtains details of the number of hours an employee has worked relative to a full time employee.
5 For a full time employee the value is = to the number of hours they would normally have worked
6 (asignment.normal hours * months). For part time employees, the number of hours worked relative to the monthly
7 reference hours needs to be calculated. The procedure also splits the hours worked by pre service ( any service
8 that is greater than 10 years date and post service any hours after the pre service :
9
10 01-JAN-2011
11 |
12 Hire Date 01-JAN-01 | Termination Date 28-FEB-2014
13 |<------ Pre Service ------->|<--Post Service-->|
14
15
16 ---------------------------------------------------------------------------------------------------------------*/
17 Function get_termination_service_det (p_business_group_id in number,
18 -- p_person_id in number,
19 p_assignment_id in number,
20 p_termination_date in date,
21 p_pre_service_ratio out NOCOPY number,
22 p_post_service_ratio out NOCOPY number)
23 Return number is
24
25 ZERO_NORMAL_HOURS exception;
26
27 -- l_greater_than_10_years_service varchar2(1) := 'N';
28 l_date_greater_than_10_years date;
29 l_total_service number :=0;
30 l_post_service_date date;
31 l_person_id number;
32 l_hire_date date;
33 l_10_years_ago date;
34 l_total_pre_service number := 0;
35 l_total_post_service number := 0;
36 l_normal_monthly_hours number := 0;
37 l_months_worked_pre_service number := 0;
38 l_months_worked_post_service number := 0;
39 l_last_day_worked DATE;
40 l_actual_months_worked number := 0;
41 l_period_of_service_id number;
42 g_package varchar2(30) := 'PAY_FR_TERMINATION_PKG';
43 l_proc varchar2(72) := g_package||'get_termination_service_det';
44 i number :=0;
45
46 l_current_monthly_ref_hours number :=0;
47 l_indx PLS_INTEGER;
48
49 TYPE assignment_rec IS RECORD
50 (business_group_id NUMBER,
51 assignment_id NUMBER,
52 effective_start_date DATE,
53 effective_end_date DATE,
54 normal_hours NUMBER,
55 frequency VARCHAR2(1),
56 establishment_id NUMBER,
57 period_of_service_id NUMBER,
58 part_time_flag VARCHAR2(1));
59
60 TYPE t_assignments IS TABLE OF assignment_rec INDEX BY BINARY_INTEGER;
61 l_assignments t_assignments;
62
63 TYPE monthly_hours_rec IS RECORD
64 ( monthly_hours NUMBER,
65 date_from DATE,
66 date_to DATE);
67
68 TYPE t_monthly_hours IS TABLE OF monthly_hours_rec INDEX BY BINARY_INTEGER;
69 l_monthly_hours t_monthly_hours;
70
71 cursor assignments(c_person_id number) is
72 select assign.business_group_id,
73 assign.assignment_id,
74 assign.effective_start_date,
75 assign.effective_end_date,
76 -- Modified as part of time analysis changes
77 -- normal_hours,
78 -- frequency,
79 decode(contract.ctr_information12, 'HOUR', fnd_number.canonical_to_number(contract.ctr_information11), assign.normal_hours) normal_hours,
80 decode(contract.ctr_information12, 'HOUR', contract.ctr_information13, assign.frequency) frequency,
81 --
82 assign.establishment_id,
83 assign.period_of_service_id,
84 substr(hruserdt.get_table_value (assign.business_group_id,'FR_CIPDZ','CIPDZ',assign.employment_category,P_termination_date),1,1) part_time_flag
85 from
86 per_all_assignments_f assign,
87 --
88 per_contracts_f contract
89 where assign.person_id = c_person_id
90 --
91 and assign.contract_id = contract.contract_id
92 --
93 order by assign.effective_start_date;
94
95 /* get table of historical monthly hours from org eit */
96 Cursor monthly_hours(c_org_id number) is
97 select to_number(org_information3) monthly_hours,
98 fnd_date.canonical_to_date(org_information1) date_from,
99 fnd_date.canonical_to_date(org_information2) date_to
100 from
101 hr_organization_information
102 where
103 org_information_context = 'FR_HISTORICAL_MONTHLY_REF_HRS'
104 and organization_id = c_org_id
105 order by fnd_date.canonical_to_date(org_information1);
106
107
108 Function Find_Hours(p_date_start date) return number is
109 l_hours_index number :=0;
110
111 l_found varchar2(1) := 'N';
112 l_proc varchar2(72) := g_package||'Find_Hours';
113
114 Begin
115
116 hr_utility.set_location('Entering:'|| l_proc, 10);
117 hr_utility.trace('p_date_start = ' || p_date_start);
118 While l_hours_index < l_monthly_hours.count and l_found = 'N' loop
119 l_hours_index := l_hours_index + 1;
120
121 hr_utility.trace('mth hrs st and end = ' || ' ' || l_monthly_hours(l_hours_index).date_from || ' ' || l_monthly_hours(l_hours_index).date_to);
122
123
124 If p_date_start between l_monthly_hours(l_hours_index).date_from and
125 l_monthly_hours(l_hours_index).date_to Then
126 l_found := 'Y';
127 End if;
128 End loop;
129
130 If l_found = 'Y' Then
131 Return l_hours_index;
132 Else
133 hr_utility.trace('no monthly reference hours found for date ' || p_date_start);
134 Return 0;
135 End If;
136
137 hr_utility.set_location(' Leaving:'||l_proc, 70);
138
139 End;
140
141 Procedure Calculate_Service(p_hours_index in number,
142 p_start in date,
143 p_end in date) is
144
145 l_number_of_months number := 0;
146 l_normal_monthly_hours number := 0;
147 l_actual_months_worked number := 0;
148 l_proc varchar2(72) := g_package||'Calculate_Service';
149
150 Function Calculate_Pre_and_Post_Service (p_start_period date, p_end_period date)
151 Return number is
152
153 l_proc varchar2(72) := g_package||'Calculate_Service';
154
155 Begin
156
157 hr_utility.set_location('Entering:'|| l_proc, 10);
158 hr_utility.trace('p_start = '|| p_start_period || 'p_end_period = ' || p_end_period);
159
160 l_number_of_months := months_between(p_end_period + 1,p_start_period);
161 IF l_assignments(i).part_time_flag = 'P' THEN
162 /* Convert the assignment normal hours to a monthly frequency if it is not monthly already */
163 IF l_assignments(i).frequency <> 'M' THEN
164 l_normal_monthly_hours := PAY_FR_GENERAL.convert_hours
165 (p_effective_date => p_start_period
166 ,p_business_group_id => l_assignments(i).business_group_id
167 ,p_assignment_id => l_assignments(i).assignment_id
168 ,p_hours => l_assignments(i).normal_hours
169 ,p_from_freq_code => l_assignments(i).frequency
170 ,p_to_freq_code => 'M');
171 ELSE
172 l_normal_monthly_hours := l_assignments(i).normal_hours;
173 END IF;
174
175 /* multiply l_nomber_of_months by above to give hours worked during period and then divide by historical
176 monthly hours to give months worked relative to a full time employee */
177
178 l_actual_months_worked := l_number_of_months *
179 (l_normal_monthly_hours/ l_monthly_hours(p_hours_index).monthly_hours);
180
181 hr_utility.trace('l_actual_months_worked = ' || l_actual_months_worked);
182 hr_utility.trace('l_number_of_months = ' || l_number_of_months );
183 hr_utility.trace('l_normal_monthly_hours = ' || l_normal_monthly_hours);
184 hr_utility.trace('l_monthly_hours(l_indx) = ' || l_monthly_hours(p_hours_index).monthly_hours);
185
186 ELSE
187 l_actual_months_worked := l_number_of_months;
188
189 hr_utility.trace('l_number_of_months = ' || l_number_of_months );
190
191 END IF;
192
193 hr_utility.set_location(' Leaving:'||l_proc, 70);
194
195 Return l_actual_months_worked;
196
197 End;
198
199 Begin
200
201 hr_utility.set_location('Entering:'|| l_proc, 10);
202
203 /* Is this service in the pre service period or service in the post period ? We may need to
204 split this service up so that the correct amount is processed in the pre and post periods */
205
206 If p_start <= l_date_greater_than_10_years and p_end <= l_date_greater_than_10_years Then /* All Pre Service */
207 l_total_pre_service := l_total_pre_service + Calculate_Pre_and_Post_Service(p_start, p_end);
208 Elsif
209 p_start <= l_date_greater_than_10_years and p_end > l_date_greater_than_10_years then /* pre and some is post */
210 l_total_pre_service := l_total_pre_service + Calculate_Pre_and_Post_Service
211 (p_start, l_date_greater_than_10_years);
212 l_total_post_service := l_total_post_service +
213 -- Calculate_Pre_and_Post_Service ((l_date_greater_than_10_years + 1 ), p_end);
214 Calculate_Pre_and_Post_Service (l_date_greater_than_10_years , p_end);
215 Elsif
216 p_start > l_date_greater_than_10_years Then /* then all post service */
217 l_total_post_service := l_total_post_service + Calculate_Pre_and_Post_Service (p_start, p_end);
218 End if;
219
220 hr_utility.set_location(' Leaving:'||l_proc, 70);
221
222 End;
223
224 Procedure Process_days(p_hours_index in number , p_date_end in date) is
225 l_hours_index number :=0;
226 l_new_date_start date;
227 l_new_date_end date;
228 l_days_to_process number :=0;
229 l_found_hours varchar2(1) := 'Y';
230
231 l_proc varchar2(72) := g_package||'Process_days';
232
233 Begin
234
235 hr_utility.set_location('Entering:'|| l_proc, 10);
236
237 l_hours_index := p_hours_index;
238 l_new_date_start := l_assignments(i).effective_start_date;
239 l_new_date_end := least(p_date_end,l_assignments(i).effective_end_date);
240 While l_new_date_start <= l_assignments(i).effective_end_date And l_found_hours = 'Y' loop
241 l_days_to_process := l_new_date_end - l_new_date_start;
242 Calculate_Service(p_hours_index => l_hours_index, p_start => l_new_date_start, p_end => l_new_date_end);
243 l_hours_index := find_hours(p_date_start => (l_new_date_end + 1));
244 If l_hours_index <> 0 Then
245 l_new_date_start := l_new_date_end + 1;
246 l_new_date_end := least(l_assignments(i).effective_end_date,l_monthly_hours(l_hours_index).date_to);
247 Else
248 l_found_hours := 'N';
249 End If;
250 End Loop;
251
252 hr_utility.set_location(' Leaving:'||l_proc, 70);
253
254 EXCEPTION
255 when others then
256 hr_utility.set_location('process_details',20);
257 hr_utility.trace(SQLCODE);
258 hr_utility.trace(SQLERRM);
259 Raise;
260
261 End Process_days;
262
263 Procedure Process_assignment_row is
264
265 l_proc varchar2(72) := g_package||'Process_assignment_row';
266 l_index number :=0;
267 Begin
268
269 hr_utility.set_location('Entering:'|| l_proc, 10);
270 hr_utility.trace('l_assignments(i).effective_start_date = ' || l_assignments(i).effective_start_date);
271
272 l_index := Find_Hours(l_assignments(i).effective_start_date); -- will always contain at least one row
273
274 hr_utility.trace('l_monthly_hours(l_index).date_to = ' || l_monthly_hours(l_index).date_to);
275
276 Process_days(l_index,l_monthly_hours(l_index).date_to);
277
278 hr_utility.set_location(' Leaving:'||l_proc, 70);
279
280 End Process_assignment_row;
281
282 BEGIN
283
284 hr_utility.set_location('Entering:'|| l_proc, 10);
285
286 l_10_years_ago := add_months (p_termination_date,-120);
287
288 select distinct person_id
289 into l_person_id
290 from per_all_assignments_f
291 where assignment_id = p_assignment_id
292 and business_group_id = p_business_group_id;
293
294 select min(start_date)
295 into l_hire_date
296 from per_all_people_f
297 where person_id = l_person_id;
298
299 If add_months(l_hire_date, 120) < p_termination_date Then
300 -- l_greater_than_10_years_service = 'Y'
301 l_date_greater_than_10_years := add_months(l_hire_date,120) -1;
302 Else
303 -- l_greater_than_10_years_service = 'N';
304 l_date_greater_than_10_years := p_termination_date + 365;
305 END IF;
306
307 hr_utility.trace ('hire date = ' || l_hire_date);
308 hr_utility.trace ('l_date_greater_than_10_years = ' || l_date_greater_than_10_years);
309
310 l_indx := 1;
311 For a in assignments(l_person_id) loop
312 l_assignments(l_indx).effective_start_date := a.effective_start_date;
313 l_assignments(l_indx).assignment_id := a.assignment_id;
314 l_assignments(l_indx).effective_end_date := a.effective_end_date;
315 l_assignments(l_indx).period_of_service_id := a.period_of_service_id;
316 If a.part_time_flag = 'P' And ( a.normal_hours is null or a.frequency is null) Then
317 RAISE ZERO_NORMAL_HOURS;
318 Else
319 l_assignments(l_indx).normal_hours := a.normal_hours;
320 l_assignments(l_indx).frequency := a.frequency;
321 l_assignments(l_indx).part_time_flag := a.part_time_flag;
322 l_assignments(l_indx).business_group_id := a.business_group_id;
323 l_assignments(l_indx).establishment_id := a.establishment_id;
324 l_indx := l_indx + 1;
325 End If;
326 End loop;
327
328 /* For the last assignment effective end date, make it equal to the last day worked */
329
330 hr_utility.trace('last period of service_id = ' || l_assignments(l_assignments.last).period_of_service_id);
331 l_period_of_service_id := l_assignments(l_assignments.last).period_of_service_id;
332 Select fnd_date.canonical_to_date(pds_information10)
333 into
334 l_last_day_worked
335 from per_periods_of_service
336 where period_of_service_id = l_period_of_service_id;
337
338 l_assignments(l_assignments.last).effective_end_date := l_last_day_worked;
339
340 hr_utility.trace ('start date 1 = ' || l_assignments(1).effective_start_date);
341
342 i :=0;
343 While i < l_assignments.last Loop
344 i := i + 1;
345 select to_number(org_information4)
346 into l_current_monthly_ref_hours
347 from hr_organization_information
348 where org_information_context = 'FR_ESTAB_INFO'
349 and organization_id = l_assignments(i).establishment_id;
350 l_monthly_hours.delete;
351 l_indx := 2;
352 For h in monthly_hours(l_assignments(i).establishment_id) loop
353 l_monthly_hours(l_indx).monthly_hours := h.monthly_hours;
354 l_monthly_hours(l_indx).date_from := h.date_from;
355 l_monthly_hours(l_indx).date_to := h.date_to;
356
357 hr_utility.trace('date from and date to = ' || l_monthly_hours(l_indx).date_from || ' ' || l_monthly_hours(l_indx).date_to);
358 l_indx := l_indx + 1;
359 End loop;
360
361 /* If any historical reference hours were found then add a row at the beginning of the table to cater
362 for assignments that start before the first historical monthly reference hours row. If we found
363 no historical data then we still want to process the assignments so use the current monthly reference
364 hours - store this in the last row.
365 */
366
367 hr_utility.trace(' 1. l_indx = ' || l_indx || ' count = ' || l_monthly_hours.count);
368
369 If l_monthly_hours.count > 0 Then
370 IF l_assignments(1).effective_start_date < l_monthly_hours(2).date_from Then
371 l_monthly_hours(1).date_from := l_assignments(1).effective_start_date - 50;
372 l_monthly_hours(1).date_to := l_monthly_hours(2).date_from - 1;
373 Else
374 l_monthly_hours(1).date_from := l_monthly_hours(2).date_from - 50;
375 l_monthly_hours(1).date_to := l_monthly_hours(2).date_from - 1;
376 -- l_indx := l_monthly_hours.count + 1;
377 -- l_monthly_hours(1).monthly_hours := l_monthly_hours(2).monthly_hours;
378 -- l_monthly_hours(1).date_from := to_char(to_date('01-JAN-1900','DD-MON-YYYY'),'DD-MON-YYYY');
379 -- l_monthly_hours(1).date_to := l_monthly_hours(2).date_from - 1;
380 End If;
381 l_indx := l_monthly_hours.count + 1;
382 l_monthly_hours(1).monthly_hours := l_monthly_hours(2).monthly_hours;
383 l_monthly_hours(l_indx).monthly_hours := l_current_monthly_ref_hours;
384 l_monthly_hours(l_indx).date_from := l_monthly_hours(l_indx - 1).date_to + 1;
385
386 hr_utility.trace('l_indx = ' || l_indx || ' count = ' || l_monthly_hours.count);
387 hr_utility.trace(' l_monthly_hours.date to = ' || l_monthly_hours(l_monthly_hours.count).date_to);
388 hr_utility.trace(' l_monthly_hours(l_indx).date_from = ' || l_monthly_hours(l_indx).date_from);
389
390 l_monthly_hours(l_indx).date_to := l_assignments(l_assignments.last).effective_end_date + 365;
391 Else
392 l_monthly_hours(1).monthly_hours := l_current_monthly_ref_hours;
393 l_monthly_hours(1).date_from := l_assignments(1).effective_start_date - 50;
394 l_monthly_hours(1).date_to := l_assignments(l_assignments.last).effective_end_date + 365;
395 End if;
396
397 process_assignment_row;
398 End loop;
399
400 /* Bug 2859175
401 NOTE Due to bug 2859175 pre_service is now returned as the total service and post service is returned as
402 the service greater than 10 years. So if an employee had 13 years service then pre service is
403 returned as 13 years and post service is returned as 3 years. */
404
405 -- p_pre_service_ratio := round(l_total_pre_service);
406 l_total_service := (l_total_pre_service + l_total_post_service) / 12;
407 p_pre_service_ratio := l_total_service;
408 p_post_service_ratio := l_total_post_service / 12;
409 -- p_post_service_ratio := round(l_total_post_service);
410
411 Return 0;
412
413 hr_utility.set_location(' Leaving:'||l_proc, 70);
414
415 EXCEPTION
416 WHEN NO_DATA_FOUND THEN
417 hr_utility.trace('No Data Found');
418 p_pre_service_ratio := 0;
419 p_post_service_ratio := 0;
420 RETURN 999;
421
422 WHEN ZERO_NORMAL_HOURS THEN
423 p_pre_service_ratio := 0;
424 p_post_service_ratio := 0;
425 hr_utility.trace('raised exception ZERO_NORMAL_HOURS - raised when part time employee has no values for
426 assignment normal hours or frequency');
427 RETURN 998;
428 RAISE;
429
430
431 when others then
432 hr_utility.set_location('l_proc',80);
433 hr_utility.trace(SQLCODE);
434 hr_utility.trace(SQLERRM);
435 p_pre_service_ratio := 0;
436 p_post_service_ratio := 0;
437 RETURN 999;
438 Raise;
439
440 End get_termination_service_det;
441 end PAY_FR_TERMINATION_PKG;
[Home] [Help]