4 /* Package Global Variables */
1 PACKAGE BODY hri_bpl_utilization AS
2 /* $Header: hributl.pkb 120.4 2007/03/05 06:15:18 pachidam noship $ */
3
5 TYPE g_number_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6 TYPE g_varchar2_tab_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
7
8 g_formula_tab g_number_tab_type;
9 g_hr_formula_tab g_varchar2_tab_type;
10 g_formula_type_id NUMBER;
11 g_template_formula_id NUMBER;
12
13 /* Global Cursors */
14 CURSOR g_formula_type_csr IS
15 SELECT formula_type_id
16 FROM ff_formula_types
17 WHERE formula_type_name = 'QuickPaint';
18
19 CURSOR g_template_formula_csr IS
20 SELECT fff.formula_id
21 FROM ff_formulas_f fff
22 WHERE fff.formula_type_id = g_formula_type_id
23 AND fff.business_group_id IS NULL
24 AND sysdate BETWEEN fff.effective_start_date AND fff.effective_end_date
25 AND fff.formula_name = 'TEMPLATE_BIS_DAYS_TO_HOURS';
26
27
28 /******************************************************************************/
29 /* Retrieves the HR formula to default absence duration for a business group */
30 /******************************************************************************/
31 FUNCTION use_hr_formula(p_business_group_id IN NUMBER)
32 RETURN VARCHAR2 IS
33
34 CURSOR use_customer_formula_csr IS
35 SELECT 'Y'
36 FROM ff_formulas_f fff
37 WHERE fff.formula_type_id = g_formula_type_id
38 AND TRUNC(sysdate) BETWEEN fff.effective_start_date AND fff.effective_end_date
39 AND fff.business_group_id = p_business_group_id
40 AND fff.formula_name = 'BG_ABSENCE_DURATION'
41 UNION ALL
42 SELECT 'Y'
43 FROM
44 ff_formulas_f fff
45 ,per_business_groups bgr
46 WHERE fff.formula_type_id = g_formula_type_id
47 AND TRUNC(sysdate) BETWEEN fff.effective_start_date AND fff.effective_end_date
48 AND fff.business_group_id IS NULL
49 AND bgr.business_group_id = p_business_group_id
50 AND bgr.legislation_code = fff.legislation_code
51 AND fff.formula_name = 'LEGISLATION_ABSENCE_DURATION'
52 UNION ALL
53 SELECT 'Y'
54 FROM ff_formulas_f fff
55 WHERE fff.formula_type_id = g_formula_type_id
56 AND TRUNC(sysdate) BETWEEN fff.effective_start_date AND fff.effective_end_date
57 AND fff.business_group_id IS NULL
58 AND fff.formula_name = 'CORE_ABSENCE_DURATION';
59
60 l_use_customer_formula VARCHAR2(30);
61
62 BEGIN
63
64 /* Try the cache */
65 BEGIN
66
67 l_use_customer_formula := g_hr_formula_tab(p_business_group_id);
68
69 /* Cache Miss */
70 EXCEPTION WHEN OTHERS THEN
71
72 /* Try and retrieve a customer formula */
73 OPEN use_customer_formula_csr;
74 FETCH use_customer_formula_csr INTO l_use_customer_formula;
75 CLOSE use_customer_formula_csr;
76
77 /* If no formula is found set flag to No */
78 IF (l_use_customer_formula IS NULL) THEN
79 l_use_customer_formula := 'N';
80 END IF;
81
82 /* Cache the formula to use for the business group */
83 g_hr_formula_tab(p_business_group_id) := l_use_customer_formula;
84
85 END;
86
87 RETURN l_use_customer_formula;
88
89 END use_hr_formula;
90
91 /******************************************************************************/
92 /* Retrieves the days to hours conversion formula for a business group */
93 /******************************************************************************/
94 FUNCTION get_formula_id(p_business_group_id IN NUMBER)
95 RETURN NUMBER IS
96
97 CURSOR customer_formula_csr IS
98 SELECT fff.formula_id
99 FROM ff_formulas_f fff
100 WHERE fff.formula_type_id = g_formula_type_id
101 AND fff.business_group_id = p_business_group_id
102 AND TRUNC(sysdate) BETWEEN fff.effective_start_date AND fff.effective_end_date
103 AND fff.formula_name = 'BIS_DAYS_TO_HOURS';
104
105 CURSOR customer_global_formula_csr IS
106 SELECT fff.formula_id
107 FROM ff_formulas_f fff
108 WHERE fff.formula_type_id = g_formula_type_id
109 AND fff.business_group_id = 0
110 AND TRUNC(sysdate) BETWEEN fff.effective_start_date AND fff.effective_end_date
111 AND fff.formula_name = 'BIS_DAYS_TO_HOURS';
112
113 l_formula_id NUMBER;
114
115 BEGIN
116
117 /* Try the cache */
118 BEGIN
119
120 l_formula_id := g_formula_tab(p_business_group_id);
121
122 /* Cache Miss */
123 EXCEPTION WHEN OTHERS THEN
124
125 /* Try and retrieve a customer formula */
126 OPEN customer_formula_csr;
127 FETCH customer_formula_csr INTO l_formula_id;
128 CLOSE customer_formula_csr;
129
130 /* If there is no customer formula, get the global */
131 IF (l_formula_id IS NULL) THEN
132 OPEN customer_global_formula_csr;
133 FETCH customer_global_formula_csr INTO l_formula_id;
134 CLOSE customer_global_formula_csr;
135 END IF;
136
137 /* If there is no customer formula, get the template */
138 IF (l_formula_id IS NULL) THEN
139 l_formula_id := g_template_formula_id;
140 END IF;
141
142 /* Cache the formula to use for the business group */
143 g_formula_tab(p_business_group_id) := l_formula_id;
144
145 END;
146
147 RETURN l_formula_id;
148
149 END get_formula_id;
150
151
152 /******************************************************************************/
153 /* Runs the days to hours conversion formula */
154 /******************************************************************************/
155 FUNCTION run_formula(p_assignment_id IN NUMBER,
156 p_business_group_id IN NUMBER,
157 p_effective_date IN DATE,
158 p_session_date IN DATE,
159 p_number_of_days IN NUMBER)
160 RETURN NUMBER IS
161
162 l_ff_inputs FF_Exec.Inputs_t;
163 l_ff_outputs FF_Exec.Outputs_t;
164 l_hours NUMBER;
165 l_formula_id NUMBER;
166
167 BEGIN
168
169 -- Get the formula id to run
170 l_formula_id := get_formula_id(p_business_group_id);
171
172 -- Initialise the Inputs and Outputs tables
173 FF_Exec.Init_Formula
174 ( p_formula_id => l_formula_id
175 , p_effective_date => p_session_date
176 , p_inputs => l_ff_inputs
177 , p_outputs => l_ff_outputs );
178
179 -- Set up context values for the formula
180 FOR i IN l_ff_inputs.first .. l_ff_inputs.last LOOP
181
182 IF (l_ff_inputs(i).name = 'DATE_EARNED') THEN
183 l_ff_inputs(i).value := FND_Date.Date_To_Canonical(p_effective_date);
184 ELSIF (l_ff_inputs(i).name = 'ASSIGNMENT_ID') THEN
185 l_ff_inputs(i).value := p_assignment_id;
186 ELSIF (l_ff_inputs(i).name = 'DAYS_WORKED') THEN
187 l_ff_inputs(i).value := p_number_of_days;
188 END IF;
189
190 END LOOP;
191
192 -- Run the formula and get the return value
193 FF_Exec.Run_Formula
194 ( p_inputs => l_ff_inputs
195 , p_outputs => l_ff_outputs);
196
197 l_hours := FND_NUMBER.canonical_to_number(l_ff_outputs(l_ff_outputs.first).value);
198
199 RETURN l_hours;
200
201 END run_formula;
202
203
204 /******************************************************************************/
205 /* Converts a value from days to hours */
206 /******************************************************************************/
207 FUNCTION convert_days_to_hours(p_assignment_id IN NUMBER,
208 p_business_group_id IN NUMBER,
209 p_effective_date IN DATE,
210 p_session_date IN DATE,
211 p_number_of_days IN NUMBER)
212 RETURN NUMBER IS
213
214 l_hours NUMBER;
215
216 BEGIN
217
218 IF (p_number_of_days IS NULL) THEN
219 l_hours := to_number(null);
220 ELSIF (p_number_of_days = 0) THEN
221 l_hours := 0;
222 ELSE
223 l_hours := run_formula(p_assignment_id => p_assignment_id,
224 p_business_group_id => p_business_group_id,
225 p_effective_date => p_effective_date,
226 p_session_date => p_session_date,
227 p_number_of_days => p_number_of_days);
228 END IF;
229
230 RETURN l_hours;
231
232 END convert_days_to_hours;
233
234 /******************************************************************************/
235 /* Converts a value from hours to days */
236 /******************************************************************************/
237 FUNCTION convert_hours_to_days(p_assignment_id IN NUMBER,
238 p_business_group_id IN NUMBER,
239 p_effective_date IN DATE,
240 p_session_date IN DATE,
241 p_number_of_hours IN NUMBER)
242 RETURN NUMBER IS
243
244 l_hours_in_day NUMBER;
245 l_number_of_days NUMBER;
246
247 BEGIN
248
249 -- Check a valid number is passed in
250 IF (p_number_of_hours IS NULL) THEN
251 l_number_of_days := to_number(null);
252 ELSIF (p_number_of_hours = 0) THEN
253 l_number_of_days := 0;
254 ELSE
255 -- Get the number of working hours in 1 day
256 l_hours_in_day := convert_days_to_hours
257 (p_assignment_id => p_assignment_id,
258 p_business_group_id => p_business_group_id,
259 p_effective_date => p_effective_date,
260 p_session_date => p_session_date,
261 p_number_of_days => 1);
262
263 -- Scale back to the number of hours given
264 IF (l_hours_in_day IS NULL OR
265 l_hours_in_day = 0) THEN
266 l_number_of_days := to_number(null);
267 ELSE
268 l_number_of_days := p_number_of_hours / l_hours_in_day;
269 END IF;
270 END IF;
271
272 RETURN l_number_of_days;
273
274 END convert_hours_to_days;
275
276 /******************************************************************************/
277 /* Converts an absence duration in hours and/or days to hours or days */
278 /******************************************************************************/
279 FUNCTION calculate_absence_duration(p_absence_attendance_id IN VARCHAR2,
280 p_uom_code IN VARCHAR2,
281 p_absence_hours IN NUMBER,
282 p_absence_days IN NUMBER,
283 p_assignment_id IN NUMBER,
284 p_business_group_id IN NUMBER,
285 p_primary_flag IN VARCHAR2,
286 p_date_start IN DATE,
287 p_date_end IN DATE,
288 p_time_start IN VARCHAR2,
289 p_time_end IN VARCHAR2)
290 RETURN NUMBER IS
291
292 -- Cursor to get additional absence details
293 -- if required to call HR formula
294 CURSOR absence_details_csr IS
295 SELECT
296 piv.element_type_id
297 ,bgr.legislation_code
298 FROM
299 per_absence_attendances paa
300 ,per_absence_attendance_types pat
301 ,pay_input_values_f piv
302 ,per_business_groups bgr
303 WHERE paa.absence_attendance_id = p_absence_attendance_id
304 AND paa.business_group_id = bgr.business_group_id
305 AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
306 AND pat.input_value_id = piv.input_value_id (+)
307 AND pat.date_effective BETWEEN piv.effective_start_date (+)
308 AND piv.effective_end_date (+);
309
310 l_hours NUMBER;
311 l_days NUMBER;
312
313 l_abs_element_id NUMBER;
314 l_abs_leg_code VARCHAR2(30);
315 l_abs_err_msg VARCHAR2(1000);
316 l_abs_duration NUMBER;
317 l_abs_use_formula VARCHAR2(30);
318
319 BEGIN
320
321 -- Check that at least one time component is provided
322 -- Route 1
323 IF (p_absence_hours IS NOT NULL OR p_absence_days IS NOT NULL) THEN
324
325 -- Bug 4870326 - Ensure absence total for person adds up to duration
326 -- by returning value only for primary assignment
327 IF (p_primary_flag = 'N') THEN
328
329 l_abs_duration := 0;
330
331 ELSE
332
333 -- Get absence duration in days
334 IF (p_uom_code = 'DAYS') THEN
335
336 -- Return days if provided, otherwise convert hours to days
337 IF (p_absence_days IS NOT NULL) THEN
338 l_abs_duration := p_absence_days;
339 ELSE
340 l_abs_duration := convert_hours_to_days
341 (p_assignment_id => p_assignment_id,
342 p_business_group_id => p_business_group_id,
343 p_effective_date => p_date_start,
344 p_session_date => TRUNC(SYSDATE),
345 p_number_of_hours => p_absence_hours);
346 END IF;
347
348 -- Get absence duration in hours
349 ELSE
350
351 -- Return hours if provided, otherwise convert days to hours
352 IF (p_absence_hours IS NOT NULL) THEN
353 l_abs_duration := p_absence_hours;
354 ELSE
355 l_abs_duration := convert_days_to_hours
356 (p_assignment_id => p_assignment_id,
357 p_business_group_id => p_business_group_id,
358 p_effective_date => p_date_start,
359 p_session_date => TRUNC(SYSDATE),
360 p_number_of_days => p_absence_days);
361 END IF;
362
363 END IF; -- Absence duration Days/Hours
364
365 END IF; -- Primary/secondary assignment
366
367 -- No absence duration component is provided
368 -- Route 2
369 ELSE
370
371 -- Check whether HR formula is set up to default absence duration
372 l_abs_use_formula := use_hr_formula
373 (p_business_group_id => p_business_group_id);
374
375 -- If so, call the formula to get the absence duration
376 IF (l_abs_use_formula = 'Y') THEN
377
378 -- Get extra details needed
379 OPEN absence_details_csr;
380 FETCH absence_details_csr INTO
381 l_abs_element_id,
382 l_abs_leg_code;
383 CLOSE absence_details_csr;
384
385 -- Trap any errors running custom formulas
386 BEGIN
387 hr_cal_abs_dur_pkg.calculate_absence_duration
388 ( p_days_or_hours => SUBSTR(p_uom_code, 1, 1),
389 p_date_start => p_date_start,
390 p_date_end => p_date_end,
391 p_time_start => p_time_start,
392 p_time_end => p_time_end,
393 p_business_group_id => p_business_group_id,
394 p_legislation_code => l_abs_leg_code,
395 p_session_date => TRUNC(SYSDATE),
396 p_assignment_id => p_assignment_id,
397 p_element_type_id => l_abs_element_id,
398 p_invalid_message => l_abs_err_msg,
399 p_duration => l_abs_duration,
400 p_use_formula => l_abs_use_formula);
401
402 -- If an exception occurs log it and continue using basic calculation
403 EXCEPTION WHEN OTHERS THEN
404 hri_bpl_conc_log.output
405 ('Warning: Error encountered running absence duration formula');
406 hri_bpl_conc_log.output
407 ('for assignment ' || to_char(p_assignment_id) ||
408 ' on ' || to_char(p_date_start, 'DD-MON-YYYY') ||
409 ' ' || p_time_start);
410 -- Reset duration so basic calculation is used
411 l_abs_duration := to_number(null);
412 END;
413
414 END IF;
415
416 -- If hr formula is not setup then get absence duration using basic calculation
417 IF (l_abs_duration IS NULL) THEN
418
419 -- If absence spans 1 day and the time component is provided use that
420 IF (p_time_start IS NOT NULL AND
421 p_time_end IS NOT NULL AND
422 p_date_start = p_date_end) THEN
423 l_hours := 24 * (to_date(p_time_end, 'HH24:MI') -
424 to_date(p_time_start, 'HH24:MI'));
425 ELSE
426 l_days := p_date_end - p_date_start + 1;
427 END IF;
428
429 -- Get the duration by re-calling this function
430 IF (l_hours >= 0 OR l_days >= 0) THEN
431
432 -- Recursive call will provide one of the absence durations
433 -- so pl/sql will follow route 1
434 l_abs_duration := calculate_absence_duration
435 (p_absence_attendance_id => p_absence_attendance_id,
436 p_uom_code => p_uom_code,
437 p_absence_hours => l_hours,
438 p_absence_days => l_days,
439 p_assignment_id => p_assignment_id,
440 p_business_group_id => p_business_group_id,
441 p_primary_flag => p_primary_flag,
442 p_date_start => p_date_start,
443 p_date_end => p_date_end,
444 p_time_start => p_time_start,
445 p_time_end => p_time_end);
446 ELSE
447 l_abs_duration := 0;
448 END IF;
449
450 END IF;
451
452 END IF;
453
454 -- Return the sum of the converted components
455 RETURN l_abs_duration;
456
457 END calculate_absence_duration;
458
459 /******************************************************************************/
460 /* Retrieves value from profile option Absence Duration */
461 /******************************************************************************/
462 FUNCTION get_abs_durtn_profile_vl
463 RETURN VARCHAR2 IS
464
465 l_profile_vl VARCHAR2(40);
466
467 BEGIN
468
469 fnd_profile.get('HRI_DBI_ABS_DRTN_UOM',l_profile_vl);
470
471 RETURN l_profile_vl;
472
473 EXCEPTION WHEN OTHERS THEN
474 l_profile_vl:= 'DAYS';
475 RETURN l_profile_vl;
476
477 END get_abs_durtn_profile_vl;
478
479 /******************************************************************************/
480 /* Initialize formula type id */
481 /******************************/
482 BEGIN
483
484 OPEN g_formula_type_csr;
485 FETCH g_formula_type_csr INTO g_formula_type_id;
486 CLOSE g_formula_type_csr;
487
488 OPEN g_template_formula_csr;
489 FETCH g_template_formula_csr INTO g_template_formula_id;
490 CLOSE g_template_formula_csr;
491
492 END hri_bpl_utilization;
[Home] [Help]