[Home] [Help]
PACKAGE BODY: APPS.HR_CAL_ABS_DUR_PKG
Source
1 PACKAGE BODY hr_cal_abs_dur_pkg AS
2 /* $Header: peabsdur.pkb 120.0 2005/05/31 04:45:35 appldev noship $ */
3 --
4 -- --------------- calc_sch_based_dur ----------------------------
5 --
6 FUNCTION good_time_format ( p_time IN VARCHAR2 ) RETURN BOOLEAN IS
7 --
8 BEGIN
9 --
10 IF p_time IS NOT NULL THEN
11 --
12 IF NOT (SUBSTR(p_time,1,2) BETWEEN '00' AND '23' AND
13 SUBSTR(p_time,4,2) BETWEEN '00' AND '59' AND
14 SUBSTR(p_time,3,1) = ':' AND
15 LENGTH(p_time) = 5) THEN
16 RETURN FALSE;
17 ELSE
18 RETURN TRUE;
19 END IF;
20 --
21 ELSE
22 RETURN FALSE;
23 END IF;
24 --
25 EXCEPTION
26 --
27 WHEN OTHERS THEN
28 RETURN FALSE;
29 --
30 END good_time_format;
31 --
32 -- --------------- calc_sch_based_dur ----------------------------
33 --
34 PROCEDURE calc_sch_based_dur
35 ( p_days_or_hours IN VARCHAR2,
36 p_date_start IN DATE,
37 p_date_end IN DATE,
38 p_time_start IN VARCHAR2,
39 p_time_end IN VARCHAR2,
40 p_assignment_id IN NUMBER,
41 p_duration IN OUT NOCOPY NUMBER
42 ) IS
43 --
44 l_idx NUMBER;
45 l_ref_date DATE;
46 l_first_band BOOLEAN;
47 l_day_start_time VARCHAR2(5);
48 l_day_end_time VARCHAR2(5);
49 l_start_time VARCHAR2(5);
50 l_end_time VARCHAR2(5);
51 --
52 l_start_date DATE;
53 l_end_date DATE;
54 l_schedule cac_avlblty_time_varray;
55 l_schedule_source VARCHAR2(10);
56 l_return_status VARCHAR2(1);
57 l_return_message VARCHAR2(2000);
58 --
59 e_bad_time_format EXCEPTION;
60 --
61 BEGIN
62 hr_utility.set_location('Entering HR_CAL_ABS_DUR_PKG.calc_sch_based_dur',10);
63 p_duration := 0;
64 --
65 IF NOT good_time_format(p_time_start) THEN
66 RAISE e_bad_time_format;
67 END IF;
68 IF NOT good_time_format(p_time_end) THEN
69 RAISE e_bad_time_format;
70 END IF;
71 l_start_date := TO_DATE(TO_CHAR(p_date_start,'DD-MM-YYYY')||' '||p_time_start,'DD-MM-YYYY HH24:MI');
72 l_end_date := TO_DATE(TO_CHAR(p_date_end,'DD-MM-YYYY')||' '||p_time_end,'DD-MM-YYYY HH24:MI');
73 --
74 -- Fetch the work schedule
75 --
76 hr_wrk_sch_pkg.get_per_asg_schedule
77 ( p_person_assignment_id => p_assignment_id
78 , p_period_start_date => l_start_date
79 , p_period_end_date => l_end_date
80 , p_schedule_category => NULL
81 , p_include_exceptions => 'Y'
82 , p_busy_tentative_as => 'FREE'
83 , x_schedule_source => l_schedule_source
84 , x_schedule => l_schedule
85 , x_return_status => l_return_status
86 , x_return_message => l_return_message
87 );
88 --
89 IF l_return_status = '0' THEN
90 --
91 -- Calculate duration
92 --
93 l_idx := l_schedule.first;
94 --
95 IF p_days_or_hours = 'D' THEN
96 --
97 l_first_band := TRUE;
98 l_ref_date := NULL;
99 WHILE l_idx IS NOT NULL
100 LOOP
101 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
102 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
103 IF l_first_band THEN
104 l_first_band := FALSE;
105 l_ref_date := TRUNC(l_schedule(l_idx).START_DATE_TIME);
106 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
107 ELSE -- not first time
108 IF TRUNC(l_schedule(l_idx).START_DATE_TIME) = l_ref_date THEN
109 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
110 ELSE
111 l_ref_date := TRUNC(l_schedule(l_idx).END_DATE_TIME);
112 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
113 END IF;
114 END IF;
115 END IF;
116 END IF;
117 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
118 END LOOP;
119 --
120 ELSE -- p_days_or_hours is 'H'
121 --
122 l_day_start_time := '00:00';
123 l_day_end_time := '23:59';
124 WHILE l_idx IS NOT NULL
125 LOOP
126 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
127 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
128 IF l_schedule(l_idx).END_DATE_TIME < l_schedule(l_idx).START_DATE_TIME THEN
129 -- Skip this invalid slot which ends before it starts
130 NULL;
131 ELSE
132 IF TRUNC(l_schedule(l_idx).END_DATE_TIME) > TRUNC(l_schedule(l_idx).START_DATE_TIME) THEN
133 -- Start and End on different days
134 --
135 -- Get first day hours
136 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
137 SELECT p_duration + (((SUBSTR(l_day_end_time,1,2)*60 + SUBSTR(l_day_end_time,4,2)) -
138 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
139 INTO p_duration
140 FROM DUAL;
141 --
142 -- Get last day hours
143 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
144 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
145 (SUBSTR(l_day_start_time,1,2)*60 + SUBSTR(l_day_start_time,4,2)) + 1)/60)
146 INTO p_duration
147 FROM DUAL;
148 --
149 -- Get between full day hours
150 SELECT p_duration + ((TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) - 1) * 24)
151 INTO p_duration
152 FROM DUAL;
153 ELSE
154 -- Start and End on same day
155 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
156 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
157 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
158 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
159 INTO p_duration
160 FROM DUAL;
161 END IF;
162 END IF;
163 END IF;
164 END IF;
165 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
166 END LOOP;
167 p_duration := ROUND(p_duration,2);
168 --
169 END IF;
170 END IF;
171 --
172 hr_utility.set_location('Leaving HR_CAL_ABS_DUR_PKG.calc_sch_based_dur',20);
173 EXCEPTION
174 --
175 WHEN e_bad_time_format THEN
176 hr_utility.set_location('Leaving HR_CAL_ABS_DUR_PKG.calc_sch_based_dur',30);
177 hr_utility.set_location(SQLERRM,35);
178 RAISE;
179 --
180 WHEN OTHERS THEN
181 hr_utility.set_location('Leaving HR_CAL_ABS_DUR_PKG.calc_sch_based_dur',30);
182 hr_utility.set_location(SQLERRM,35);
183 RAISE;
184 --
185 END calc_sch_based_dur;
186
187 -- --------------- calculate_absence_duration ---------------------
188 --
189 -- computes the employee's absence duration
190 --
191 PROCEDURE calculate_absence_duration
192 ( p_days_or_hours IN VARCHAR2,
193 p_date_start IN DATE,
194 p_date_end IN DATE,
195 p_time_start IN VARCHAR2,
196 p_time_end IN VARCHAR2,
197 p_business_group_id IN NUMBER,
198 p_legislation_code IN VARCHAR2,
199 p_session_date IN DATE,
200 p_assignment_id IN NUMBER,
201 p_element_type_id IN NUMBER,
202 p_invalid_message IN OUT NOCOPY VARCHAR2,
203 p_duration IN OUT NOCOPY NUMBER,
204 p_use_formula IN OUT NOCOPY VARCHAR2) IS
205 --
206 --
207 --
208 l_formula_id ff_formulas_f.formula_id%type;
209 l_effective_start_date ff_formulas_f.effective_start_date%type;
210 l_inputs ff_exec.inputs_t;
211 l_outputs ff_exec.outputs_t;
212 wrong_parameters exception;
213 l_user_message VARCHAR2(1);
214 l_sch_based_dur VARCHAR2(1);
215 --
216 lv_invalid_message VARCHAR2(2000);
217 lv_duration NUMBER;
218 lv_use_formula VARCHAR2(2000);
219 --
220 begin
221 --
222 lv_invalid_message := p_invalid_message ;
223 lv_duration := p_duration ;
224 lv_use_formula := p_use_formula ;
225 --
226 l_user_message := 'N';
227 --
228 l_sch_based_dur := NVL(FND_PROFILE.Value('HR_SCH_BASED_ABS_CALC'),'N');
229 --
230 IF l_sch_based_dur = 'Y' THEN
231 hr_utility.set_location('HR_CAL_ABS_DUR_PKG.cal_abs_dur',05);
232 p_use_formula := 'N';
233 --
234 -- Invoke work schedule based duration calculation
235 calc_sch_based_dur (p_days_or_hours => p_days_or_hours,
236 p_date_start => p_date_start,
237 p_date_end => p_date_end,
238 p_time_start => p_time_start,
239 p_time_end => p_time_end,
240 p_assignment_id => p_assignment_id,
241 p_duration => p_duration);
242 --
243 ELSE -- Use FastFormula based calculation
244 p_use_formula := 'Y';
245 hr_utility.set_location('HR_CAL_ABS_DUR_PKG.cal_abs_dur',10);
246 --
247 -- There are 3 levels of formula:
248 -- 1. business group (if defined)
249 -- 2. legislation_code (if defined)
250 -- 3. CORE formula (by default for all localisations).
251 --
252 --
253 -- select customer-defined formula (if exists)
254 begin
255 select formula_id, effective_start_date
256 into l_formula_id, l_effective_start_date
257 from ff_formulas_f
258 where formula_name = 'BG_ABSENCE_DURATION'
259 and business_group_id = p_business_group_id
260 and p_session_date between effective_start_date and effective_end_date;
261 exception
262 WHEN NO_DATA_FOUND THEN
263 -- If no business group formula then look for
264 -- legislation-defined formula
265 begin
266 select formula_id, effective_start_date
267 into l_formula_id, l_effective_start_date
268 from ff_formulas_f
269 where formula_name = 'LEGISLATION_ABSENCE_DURATION'
270 and legislation_code = p_legislation_code
271 and business_group_id is null
272 and p_session_date between effective_start_date and effective_end_date;
273 exception
274 WHEN NO_DATA_FOUND THEN
275 -- If none of the two above, then
276 -- select core formula
277 begin
278 select formula_id, effective_start_date
279 into l_formula_id, l_effective_start_date
280 from ff_formulas_f
281 where formula_name = 'CORE_ABSENCE_DURATION'
282 and legislation_code is null
283 and business_group_id is null
284 and p_session_date between effective_start_date and effective_end_date;
285 end;
286 --
287 end;
288 --
289 end;
290 --
291 hr_utility.set_location('HR_CAL_ABS_DUR_PKG.cal_abs_dur',20);
292 --
293 -- initialize the formula
294 --
295 ff_exec.init_formula(l_formula_id,l_effective_start_date,l_inputs,l_outputs);
296 --
297 -- assign inputs
298 --
299 for l_in_cnt in l_inputs.first..l_inputs.last
300 loop
301 if l_inputs(l_in_cnt).name='DAYS_OR_HOURS' then
302 l_inputs(l_in_cnt).value:=p_days_or_hours;
303 elsif l_inputs(l_in_cnt).name='DATE_START' then
304 l_inputs(l_in_cnt).value:=fnd_date.date_to_canonical(p_date_start);
305 elsif l_inputs(l_in_cnt).name='DATE_END' then
306 l_inputs(l_in_cnt).value:=fnd_date.date_to_canonical(p_date_end);
307 elsif l_inputs(l_in_cnt).name='TIME_START' then
308 l_inputs(l_in_cnt).value:=p_time_start;
309 elsif l_inputs(l_in_cnt).name='TIME_END' then
310 l_inputs(l_in_cnt).value:=p_time_end;
311 elsif l_inputs(l_in_cnt).name='DATE_EARNED' then
312 l_inputs(l_in_cnt).value:=fnd_date.date_to_canonical(p_session_date);
313 elsif l_inputs(l_in_cnt).name='BUSINESS_GROUP_ID' then
314 l_inputs(l_in_cnt).value:=p_business_group_id;
315 elsif l_inputs(l_in_cnt).name='LEGISLATION_CODE' then
316 l_inputs(l_in_cnt).value:=p_legislation_code;
317 elsif l_inputs(l_in_cnt).name='ASSIGNMENT_ID' then
318 l_inputs(l_in_cnt).value:=p_assignment_id;
319 elsif l_inputs(l_in_cnt).name='ELEMENT_TYPE_ID' then
320 l_inputs(l_in_cnt).value:=p_element_type_id;
321 else
322 raise wrong_parameters;
323 end if;
324 end loop;
325 --
326 hr_utility.set_location('HR_CAL_ABS_DUR_PKG.cal_abs_dur',30);
327 --
328 -- run the formula
329 --
330 ff_exec.run_formula(l_inputs,l_outputs);
331 --
332 -- assign outputs
333 --
334 for l_out_cnt in l_outputs.first..l_outputs.last
335 loop
336 if l_outputs(l_out_cnt).name='DURATION' then
337 if l_outputs(l_out_cnt).value = 'FAILED' then
338 l_user_message := 'Y';
339 else
340 p_duration := to_number(l_outputs(l_out_cnt).value);
341 end if;
342 elsif l_outputs(l_out_cnt).name='INVALID_MSG' then
343 p_invalid_message := l_outputs(l_out_cnt).value;
344 else
345 raise wrong_parameters;
346 end if;
347 end loop;
348 --
349 if l_user_message = 'Y' then
350 hr_utility.set_location('HR_CAL_ABS_DUR_PKG.cal_abs_dur',35);
351 hr_utility.set_message(800,p_invalid_message);
352 hr_utility.raise_error;
353 end if;
354 --
355 END IF; -- Check calculation mode
356 --
357 exception
358 when NO_DATA_FOUND then
359 -- no formula found, so send back flag for using existing procedures
360 -- do we need the error message now?
361 p_invalid_message := lv_invalid_message ;
362 p_duration := lv_duration ;
363 p_use_formula := 'N';
367 hr_utility.raise_error;
364 hr_utility.set_location('HR_CAL_ABS_DUR_PKG.cal_abs_dur',40);
365 /*
366 hr_utility.set_message(800,'HR_52351_FF_NOT_FOUND');
368 */
369 when wrong_parameters then
370 hr_utility.set_location('HR_CAL_ABS_DUR_PKG.cal_abs_dur',45);
371 hr_utility.set_message(800,'HR_52352_BAD_FF_DEFINITION');
372
373 p_invalid_message := lv_invalid_message ;
374 p_duration := lv_duration ;
375 p_use_formula := lv_use_formula ;
376
377 hr_utility.raise_error;
378 when others then
379
380 p_invalid_message := lv_invalid_message ;
381 p_duration := lv_duration ;
382 p_use_formula := lv_use_formula ;
383 RAISE;
384 --
385 end calculate_absence_duration;
386 --
387 --
388 -- --------------------- count_working_days -----------------------
389 --
390 -- This function is called from the formula and its used to
391 -- count the number of working days (Monday to Friday) for the
392 -- duration of the absence in the CORE formula.
393 --
394 function count_working_days(starting_date DATE, total_days NUMBER)
395 return NUMBER is
396 day_num NUMBER := 0;
397 count_days NUMBER := 0;
398 begin
399 -- find day of the week
400 day_num := to_char(starting_date,'D');
401 -- loop until end of absence
402 for i in 1..(total_days+1) loop
403 -- if neither Saturday nor Sunday then add to counter
404 if ((day_num <> 1) and (day_num <> 7)) then
405 count_days := count_days + 1;
406 end if;
407 -- if Sunday then start again
408 if day_num = 7 then
409 day_num := 1;
410 else
411 -- else next day
412 day_num := day_num + 1;
413 end if;
414 end loop;
415 return count_days;
416 end count_working_days;
417
418 end hr_cal_abs_dur_pkg;