[Home] [Help]
PACKAGE BODY: APPS.HR_LOC_WORK_SCHEDULE
Source
1 PACKAGE BODY hr_loc_work_schedule AS
2 /* $Header: hrlocwks.pkb 120.0.12000000.2 2007/02/26 06:39:10 pdavidra ship $ */
3
4 g_package varchar2(33) := ' HR_LOC_WORK_SCHEDULE.'; -- Global package name
5 --
6 -- The following two global variables are only to be
7 -- used by the return_legislation_code function.
8 --
9 g_legislation_code varchar2(150) default null;
10 g_absence_attendance_id number default null;
11
12
13 -- Function to check whether given parameter is in proper time format
14 FUNCTION good_time_format ( p_time IN VARCHAR2 ) RETURN BOOLEAN IS
15 --
16 BEGIN
17 --
18 IF p_time IS NOT NULL THEN
19 --
20 IF NOT (SUBSTR(p_time,1,2) BETWEEN '00' AND '23' AND
21 SUBSTR(p_time,4,2) BETWEEN '00' AND '59' AND
22 SUBSTR(p_time,3,1) = ':' AND
23 LENGTH(p_time) = 5) THEN
24 RETURN FALSE;
25 ELSE
26 RETURN TRUE;
27 END IF;
28 --
29 ELSE
30 RETURN FALSE;
31 END IF;
32 --
33 EXCEPTION
34 --
35 WHEN OTHERS THEN
36 RETURN FALSE;
37 --
38 END good_time_format;
39
40
41 -- Function to retrieve time duration between given 2 dates for given assignment
42 FUNCTION calc_sch_based_dur ( p_assignment_id IN NUMBER,
43 p_days_or_hours IN VARCHAR2,
44 p_include_event IN VARCHAR2,
45 p_date_start IN DATE,
46 p_date_end IN DATE,
47 p_time_start IN VARCHAR2,
48 p_time_end IN VARCHAR2,
49 p_duration IN OUT NOCOPY NUMBER
50 ) RETURN NUMBER IS
51 --
52 l_return NUMBER;
53 l_idx NUMBER;
54 l_ref_date DATE;
55 l_first_band BOOLEAN;
56 l_day_start_time VARCHAR2(5);
57 l_day_end_time VARCHAR2(5);
58 l_start_time VARCHAR2(5);
59 l_end_time VARCHAR2(5);
60 --
61 l_start_date DATE;
62 l_end_date DATE;
63 l_schedule cac_avlblty_time_varray;
64 l_schedule_source VARCHAR2(10);
65 l_return_status VARCHAR2(1);
66 l_return_message VARCHAR2(2000);
67 --
68 l_time_start VARCHAR2(10);
69 l_time_end VARCHAR2(10);
70 --
71 e_bad_time_format EXCEPTION;
72
73 CURSOR get_time_format(l_time varchar2) is
74 SELECT replace(trim(to_char(fnd_number.canonical_to_number(l_time),'00.00')),'.',':') FROM dual;
75
76 --
77 BEGIN
78 hr_utility.set_location('Entering '||g_package||'.calc_sch_based_dur',10);
79 l_return := -1;
80 p_duration := 0;
81 l_time_start := p_time_start;
82 l_time_end := p_time_end;
83
84
85 OPEN get_time_format(l_time_start);
86 FETCH get_time_format INTO l_time_start;
87 CLOSE get_time_format;
88
89 OPEN get_time_format(l_time_end);
90 FETCH get_time_format INTO l_time_end;
91 CLOSE get_time_format;
92
93
94 --
95 IF l_time_start IS NULL THEN
96 l_time_start := '00:00';
97 ELSE
98 IF NOT good_time_format(l_time_start) THEN
99 RAISE e_bad_time_format;
100 END IF;
101 END IF;
102 IF l_time_end IS NULL THEN
103 l_time_end := '23:59';
104 ELSE
105 IF NOT good_time_format(l_time_end) THEN
106 RAISE e_bad_time_format;
107 END IF;
108 END IF;
109 l_start_date := TO_DATE(TO_CHAR(p_date_start,'DD-MM-YYYY')||' '||l_time_start,'DD-MM-YYYY HH24:MI');
110 l_end_date := TO_DATE(TO_CHAR(p_date_end,'DD-MM-YYYY')||' '||l_time_end,'DD-MM-YYYY HH24:MI');
111 /* 115.2, following check is not required
112 IF p_days_or_hours = 'D' THEN
113 l_end_date := l_end_date + 1;
114 END IF;
115 */
116 --
117 -- Fetch the work schedule
118 hr_utility.set_location('calling hr_wrk_sch_pkg.get_per_asg_schedule',10);
119 --
120 hr_wrk_sch_pkg.get_per_asg_schedule
121 ( p_person_assignment_id => p_assignment_id
122 , p_period_start_date => l_start_date
123 , p_period_end_date => l_end_date
124 , p_schedule_category => NULL
125 , p_include_exceptions => p_include_event
126 , p_busy_tentative_as => 'FREE'
127 , x_schedule_source => l_schedule_source
128 , x_schedule => l_schedule
129 , x_return_status => l_return_status
130 , x_return_message => l_return_message
131 );
132 --
133
134 l_return := l_return_status;
135 hr_utility.set_location('l_return status :' || l_return_status,10);
136
137 IF l_return_status = '0' THEN
138 --
139 -- Calculate duration
140 --
141 l_idx := l_schedule.first;
142
143 IF p_days_or_hours = 'D' THEN
144 --
145 l_first_band := TRUE;
146 l_ref_date := NULL;
147 WHILE l_idx IS NOT NULL
148 LOOP
149
150 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
151 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
152 IF l_first_band THEN
153 l_first_band := FALSE;
154 l_ref_date := TRUNC(l_schedule(l_idx).START_DATE_TIME);
155
156 hr_utility.set_location('start date time '|| l_schedule(l_idx).START_DATE_TIME,20);
157 hr_utility.set_location('end date time '|| l_schedule(l_idx).END_DATE_TIME,20);
158
159 IF (TRUNC(l_schedule(l_idx).END_DATE_TIME) = TRUNC(l_schedule(l_idx).START_DATE_TIME)) THEN
160 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
161 ELSE
162 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
163 END IF;
164 ELSE -- not first time
165 IF TRUNC(l_schedule(l_idx).START_DATE_TIME) = l_ref_date THEN
166 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
167 ELSE
168 l_ref_date := TRUNC(l_schedule(l_idx).END_DATE_TIME);
169 IF (TRUNC(l_schedule(l_idx).END_DATE_TIME) = TRUNC(l_schedule(l_idx).START_DATE_TIME)) THEN
170 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) + 1);
171 ELSE
172 p_duration := p_duration + (TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME));
173 END IF;
174 END IF;
175 END IF;
176 END IF;
177 END IF;
178 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
179 END LOOP;
180 --
181 ELSE -- p_days_or_hours is 'H'
182 --
183 l_day_start_time := '00:00';
184 l_day_end_time := '23:59';
185 WHILE l_idx IS NOT NULL
186 LOOP
187
188
189 IF l_schedule(l_idx).FREE_BUSY_TYPE IS NOT NULL THEN
190 IF l_schedule(l_idx).FREE_BUSY_TYPE = 'FREE' THEN
191 IF l_schedule(l_idx).END_DATE_TIME < l_schedule(l_idx).START_DATE_TIME THEN
192 -- Skip this invalid slot which ends before it starts
193 NULL;
194 ELSE
195 IF TRUNC(l_schedule(l_idx).END_DATE_TIME) > TRUNC(l_schedule(l_idx).START_DATE_TIME) THEN
196 -- Start and End on different days
197 --
198 -- Get first day hours
199 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
200 SELECT p_duration + (((SUBSTR(l_day_end_time,1,2)*60 + SUBSTR(l_day_end_time,4,2)) -
201 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
202 INTO p_duration
203 FROM DUAL;
204 --
205 -- Get last day hours
206 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
207 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
208 (SUBSTR(l_day_start_time,1,2)*60 + SUBSTR(l_day_start_time,4,2)) + 1)/60)
209 INTO p_duration
210 FROM DUAL;
211 --
212 -- Get between full day hours
213 SELECT p_duration + ((TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) - 1) * 24)
214 INTO p_duration
215 FROM DUAL;
216 ELSE
217 -- Start and End on same day
218 l_start_time := TO_CHAR(l_schedule(l_idx).START_DATE_TIME,'HH24:MI');
219 l_end_time := TO_CHAR(l_schedule(l_idx).END_DATE_TIME,'HH24:MI');
220 SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
221 (SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
222 INTO p_duration
223 FROM DUAL;
224 END IF;
225 END IF;
226 END IF;
227 END IF;
228 l_idx := l_schedule(l_idx).NEXT_OBJECT_INDEX;
229 END LOOP;
230 p_duration := ROUND(p_duration,2);
231 --
232 END IF;
233 END IF;
234 RETURN l_return;
235 --
236 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',20);
237 EXCEPTION
238 --
239 WHEN e_bad_time_format THEN
240 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',30);
241 hr_utility.set_location(SQLERRM,35);
242 RAISE;
243 --
244 WHEN OTHERS THEN
245 hr_utility.set_location('Leaving '||g_package||'.calc_sch_based_dur',40);
246 hr_utility.set_location(SQLERRM,45);
247 RAISE;
248 --
249
250 RETURN l_return;
251 END calc_sch_based_dur;
252
253
254 END hr_loc_work_schedule;
255