DBA Data[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;