[Home] [Help]
PACKAGE BODY: APPS.PA_DURATION_UTILS
Source
1 PACKAGE BODY pa_duration_utils AS
2 /*$Header: PADURUTB.pls 120.1 2005/08/19 16:21:43 mwasowic noship $*/
3
4 --
5 -- PROCEDURE get_duration_old
6 --
7 -- PURPOSE
8 -- This procedure returns total number of hours and days for given
9 -- start date and end date.
10 PROCEDURE get_duration_old(p_calendar_id IN NUMBER,
11 p_start_date IN DATE,
12 p_end_date IN DATE,
13 x_duration_days OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
14 x_duration_hours OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
15 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
16 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
17 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
18 IS
19 l_sch_record_tab PA_SCHEDULE_GLOB.ScheduleTabTyp;
20 l_date DATE;
21 l_week_day VARCHAR2(10);
22
23 BEGIN
24 x_return_status := FND_API.G_RET_STS_SUCCESS;
25 x_duration_days := 0;
26 x_duration_hours :=0;
27
28 PA_SCHEDULE_PVT.get_calendar_schedule(p_calendar_id => p_calendar_id,
29 p_start_date => p_start_date,
30 p_end_date => p_end_date,
31 x_sch_record_tab => l_sch_record_tab,
32 x_return_status => x_return_status,
33 x_msg_count => x_msg_count,
34 x_msg_data => x_msg_data);
35
36 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
37 IF l_sch_record_tab.COUNT > 0 THEN
38 FOR j IN l_sch_record_tab.FIRST..l_sch_record_tab.LAST LOOP
39
40 l_date := l_sch_record_tab(j).start_date;
41 IF l_sch_record_tab(j).start_date IS NOT NULL AND
42 l_sch_record_tab(j).end_date IS NOT NULL
43 THEN
44
45 LOOP
46 l_week_day := TO_CHAR(l_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN');
47
48 IF l_week_day = 'MON' AND l_sch_record_tab(j).monday_hours > 0 THEN
49 x_duration_days := x_duration_days +1;
50 x_duration_hours := x_duration_hours + l_sch_record_tab(j).monday_hours;
51
52 ELSIF l_week_day = 'TUE' AND l_sch_record_tab(j).tuesday_hours > 0 THEN
53 x_duration_days := x_duration_days +1;
54 x_duration_hours := x_duration_hours + l_sch_record_tab(j).tuesday_hours;
55
56 ELSIF l_week_day = 'WED' AND l_sch_record_tab(j).wednesday_hours > 0 THEN
57 x_duration_days := x_duration_days +1;
58 x_duration_hours := x_duration_hours + l_sch_record_tab(j).wednesday_hours;
59
60 ELSIF l_week_day = 'THU' AND l_sch_record_tab(j).thursday_hours > 0 THEN
61 x_duration_days := x_duration_days +1;
62 x_duration_hours := x_duration_hours + l_sch_record_tab(j).thursday_hours;
63
64 ELSIF l_week_day = 'FRI' AND l_sch_record_tab(j).friday_hours > 0 THEN
65 x_duration_days := x_duration_days +1;
66 x_duration_hours := x_duration_hours + l_sch_record_tab(j).friday_hours;
67
68 ELSIF l_week_day = 'SAT' AND l_sch_record_tab(j).saturday_hours > 0 THEN
69 x_duration_days := x_duration_days +1;
70 x_duration_hours := x_duration_hours + l_sch_record_tab(j).saturday_hours;
71
72 ELSIF l_week_day = 'SUN' AND l_sch_record_tab(j).sunday_hours > 0 THEN
73 x_duration_days := x_duration_days +1;
74 x_duration_hours := x_duration_hours + l_sch_record_tab(j).sunday_hours;
75
76 END IF;
77
78 l_date := l_date + 1;
79
80 EXIT WHEN trunc(l_date) > trunc(l_sch_record_tab(j).end_date);
81 END LOOP;
82 END IF;
83 END LOOP;
84 END IF;
85 END IF;
86
87 EXCEPTION
88 WHEN OTHERS THEN
89 FND_MSG_PUB.add_exc_msg
90 (p_pkg_name => 'PA_DURATION_UTILS.get_duration_old',
91 p_procedure_name => PA_DEBUG.G_Err_Stack );
92 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
93 RAISE;
94 END get_duration_old;
95
96 FUNCTION get_total_hours(p_calendar_id IN NUMBER,
97 p_start_date IN DATE,
98 p_end_date IN DATE) return NUMBER
99 IS
100 l_hours NUMBER;
101 l_days NUMBER;
102 l_return_status VARCHAR2(1);
103 l_msg_count NUMBER;
104 l_msg_data VARCHAR2(256);
105 BEGIN
106 get_duration(p_calendar_id,
107 p_start_date,
108 p_end_date,
109 l_days,
110 l_hours,
111 l_return_status,
112 l_msg_count,
113 l_msg_data);
114 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
115 return 0;
116 ELSE
117 return l_hours;
118 END IF;
119 EXCEPTION
120 WHEN OTHERS then
121 return 0;
122 END get_total_hours;
123
124
125 -- 07-Mar-2003 Amksingh Bug 2838700 A new procedure get_duration
126 -- is added and the previous get_duration is
127 -- renamed to get_duration_old.
128
129 PROCEDURE get_duration(p_calendar_id IN NUMBER,
130 p_start_date IN DATE,
131 p_end_date IN DATE,
132 x_duration_days OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
133 x_duration_hours OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
134 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
135 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
136 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
137 IS
138 l_sch_record_tab PA_SCHEDULE_GLOB.ScheduleTabTyp;
139 l_date DATE;
140 l_week_day VARCHAR2(10);
141 l_duration_days NUMBER := 0;
142 l_duration_hours NUMBER :=0;
143
144
145 BEGIN
146 x_return_status := FND_API.G_RET_STS_SUCCESS;
147 x_duration_days := 0;
148 x_duration_hours :=0;
149
150 IF (g_calendar_id IS NULL OR g_calendar_id <> p_calendar_id) THEN
151
152 PA_SCHEDULE_PVT.get_calendar_schedule(p_calendar_id => p_calendar_id,
153 p_start_date => p_start_date,
154 p_end_date => p_end_date,
155 x_sch_record_tab => l_sch_record_tab,
156 x_return_status => x_return_status,
157 x_msg_count => x_msg_count,
158 x_msg_data => x_msg_data);
159
160 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
161 IF l_sch_record_tab.COUNT > 0 THEN
162 FOR j IN l_sch_record_tab.FIRST..l_sch_record_tab.LAST LOOP
163
164 l_date := l_sch_record_tab(j).start_date;
165 IF l_sch_record_tab(j).start_date IS NOT NULL AND
166 l_sch_record_tab(j).end_date IS NOT NULL
167 THEN
168
169 LOOP
170 l_week_day := TO_CHAR(l_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN');
171
172 IF l_week_day = 'MON' AND l_sch_record_tab(j).monday_hours > 0 THEN
173 l_duration_days := l_duration_days +1;
174 l_duration_hours := l_duration_hours + l_sch_record_tab(j).monday_hours;
175
176 ELSIF l_week_day = 'TUE' AND l_sch_record_tab(j).tuesday_hours > 0 THEN
177 l_duration_days := l_duration_days +1;
178 l_duration_hours := l_duration_hours + l_sch_record_tab(j).tuesday_hours;
179
180 ELSIF l_week_day = 'WED' AND l_sch_record_tab(j).wednesday_hours > 0 THEN
181 l_duration_days := l_duration_days +1;
182 l_duration_hours := l_duration_hours + l_sch_record_tab(j).wednesday_hours;
183
184 ELSIF l_week_day = 'THU' AND l_sch_record_tab(j).thursday_hours > 0 THEN
185 l_duration_days := l_duration_days +1;
186 l_duration_hours := l_duration_hours + l_sch_record_tab(j).thursday_hours;
187
188 ELSIF l_week_day = 'FRI' AND l_sch_record_tab(j).friday_hours > 0 THEN
189 l_duration_days := l_duration_days +1;
190 l_duration_hours := l_duration_hours + l_sch_record_tab(j).friday_hours;
191
192 ELSIF l_week_day = 'SAT' AND l_sch_record_tab(j).saturday_hours > 0 THEN
193 l_duration_days := l_duration_days +1;
194 l_duration_hours := l_duration_hours + l_sch_record_tab(j).saturday_hours;
195
196 ELSIF l_week_day = 'SUN' AND l_sch_record_tab(j).sunday_hours > 0 THEN
197 l_duration_days := l_duration_days +1;
198 l_duration_hours := l_duration_hours + l_sch_record_tab(j).sunday_hours;
199
200 END IF;
201
202 l_date := l_date + 1;
203
204 EXIT WHEN trunc(l_date) > trunc(l_sch_record_tab(j).end_date);
205 END LOOP;
206 END IF;
207 END LOOP;
208 END IF;
209 END IF;
210 x_duration_days := l_duration_days;
211 x_duration_hours := l_duration_hours;
212 g_duration_days := l_duration_days;
213 g_duration_hours := l_duration_hours;
214 g_calendar_id := p_calendar_id;
215 g_start_date := p_start_date;
216 g_end_date := p_end_date;
217 g_sch_record_tab := l_sch_record_tab;
218 ELSE
219 IF (p_start_date = g_start_date AND p_end_date = g_end_date) THEN
220 x_duration_days := g_duration_days;
221 x_duration_hours := g_duration_hours;
222 ELSIF ( p_start_date >= g_start_date AND p_end_date <= g_end_date) THEN
223 l_sch_record_tab := g_sch_record_tab;
224
225 --hsiu added; bug 2846044
226 l_date := p_start_date;
227
228 IF l_sch_record_tab.COUNT > 0 THEN
229 FOR j IN l_sch_record_tab.FIRST..l_sch_record_tab.LAST LOOP
230
231 --commented for bug 2846044
232 -- l_date := l_sch_record_tab(j).start_date;
233
234 IF l_sch_record_tab(j).start_date IS NOT NULL AND
235 l_sch_record_tab(j).end_date IS NOT NULL
236 THEN
237
238 LOOP
239 EXIT WHEN (trunc(l_date) > trunc(l_sch_record_tab(j).end_date) OR
240 trunc(l_date) > trunc(p_end_date));
241
242 l_week_day := TO_CHAR(l_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN');
243
244 IF l_week_day = 'MON' AND l_sch_record_tab(j).monday_hours > 0 THEN
245 l_duration_days := l_duration_days +1;
246 l_duration_hours := l_duration_hours + l_sch_record_tab(j).monday_hours;
247
248 ELSIF l_week_day = 'TUE' AND l_sch_record_tab(j).tuesday_hours > 0 THEN
249 l_duration_days := l_duration_days +1;
250 l_duration_hours := l_duration_hours + l_sch_record_tab(j).tuesday_hours;
251
252 ELSIF l_week_day = 'WED' AND l_sch_record_tab(j).wednesday_hours > 0 THEN
253 l_duration_days := l_duration_days +1;
254 l_duration_hours := l_duration_hours + l_sch_record_tab(j).wednesday_hours;
255
256 ELSIF l_week_day = 'THU' AND l_sch_record_tab(j).thursday_hours > 0 THEN
257 l_duration_days := l_duration_days +1;
258 l_duration_hours := l_duration_hours + l_sch_record_tab(j).thursday_hours;
259
260 ELSIF l_week_day = 'FRI' AND l_sch_record_tab(j).friday_hours > 0 THEN
261 l_duration_days := l_duration_days +1;
262 l_duration_hours := l_duration_hours + l_sch_record_tab(j).friday_hours;
263
264 ELSIF l_week_day = 'SAT' AND l_sch_record_tab(j).saturday_hours > 0 THEN
265 l_duration_days := l_duration_days +1;
266 l_duration_hours := l_duration_hours + l_sch_record_tab(j).saturday_hours;
267
268 ELSIF l_week_day = 'SUN' AND l_sch_record_tab(j).sunday_hours > 0 THEN
269 l_duration_days := l_duration_days +1;
270 l_duration_hours := l_duration_hours + l_sch_record_tab(j).sunday_hours;
271
272 END IF;
273
274 l_date := l_date + 1;
275
276 --commmented for duration bug
277 -- EXIT WHEN trunc(l_date) > trunc(l_sch_record_tab(j).end_date);
278 END LOOP;
279 END IF;
280 --hsiu added when > end_date
281 EXIT WHEN trunc(l_date) > trunc(p_end_date);
282 END LOOP;
283 END IF;
284 x_duration_days := l_duration_days;
285 x_duration_hours := l_duration_hours;
286 ELSE
287 PA_SCHEDULE_PVT.get_calendar_schedule(p_calendar_id => p_calendar_id,
288 p_start_date => p_start_date,
289 p_end_date => p_end_date,
290 x_sch_record_tab => l_sch_record_tab,
291 x_return_status => x_return_status,
292 x_msg_count => x_msg_count,
293 x_msg_data => x_msg_data);
294
295 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
296 IF l_sch_record_tab.COUNT > 0 THEN
297 FOR j IN l_sch_record_tab.FIRST..l_sch_record_tab.LAST LOOP
298
299 l_date := l_sch_record_tab(j).start_date;
300 IF l_sch_record_tab(j).start_date IS NOT NULL AND
301 l_sch_record_tab(j).end_date IS NOT NULL
302 THEN
303
304 LOOP
305 l_week_day := TO_CHAR(l_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN');
306
307 IF l_week_day = 'MON' AND l_sch_record_tab(j).monday_hours > 0 THEN
308 l_duration_days := l_duration_days +1;
309 l_duration_hours := l_duration_hours + l_sch_record_tab(j).monday_hours;
310
311 ELSIF l_week_day = 'TUE' AND l_sch_record_tab(j).tuesday_hours > 0 THEN
312 l_duration_days := l_duration_days +1;
313 l_duration_hours := l_duration_hours + l_sch_record_tab(j).tuesday_hours;
314
315 ELSIF l_week_day = 'WED' AND l_sch_record_tab(j).wednesday_hours > 0 THEN
316 l_duration_days := l_duration_days +1;
317 l_duration_hours := l_duration_hours + l_sch_record_tab(j).wednesday_hours;
318
319 ELSIF l_week_day = 'THU' AND l_sch_record_tab(j).thursday_hours > 0 THEN
320 l_duration_days := l_duration_days +1;
321 l_duration_hours := l_duration_hours + l_sch_record_tab(j).thursday_hours;
322
323 ELSIF l_week_day = 'FRI' AND l_sch_record_tab(j).friday_hours > 0 THEN
324 l_duration_days := l_duration_days +1;
325 l_duration_hours := l_duration_hours + l_sch_record_tab(j).friday_hours;
326
327 ELSIF l_week_day = 'SAT' AND l_sch_record_tab(j).saturday_hours > 0 THEN
328 l_duration_days := l_duration_days +1;
329 l_duration_hours := l_duration_hours + l_sch_record_tab(j).saturday_hours;
330
331 ELSIF l_week_day = 'SUN' AND l_sch_record_tab(j).sunday_hours > 0 THEN
332 l_duration_days := l_duration_days +1;
333 l_duration_hours := l_duration_hours + l_sch_record_tab(j).sunday_hours;
334
335 END IF;
336
337 l_date := l_date + 1;
338
339 EXIT WHEN trunc(l_date) > trunc(l_sch_record_tab(j).end_date);
340 END LOOP;
341 END IF;
342 END LOOP;
343 END IF;
344 END IF;
345 x_duration_days := l_duration_days;
346 x_duration_hours := l_duration_hours;
347 g_duration_days := l_duration_days;
348 g_duration_hours := l_duration_hours;
349 g_calendar_id := p_calendar_id;
350 g_start_date := p_start_date;
351 g_end_date := p_end_date;
352 g_sch_record_tab := l_sch_record_tab;
353 END IF;
354
355 END IF; -- g_calendar_id IS NULL THEN
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 FND_MSG_PUB.add_exc_msg
360 (p_pkg_name => 'PA_DURATION_UTILS.get_duration',
361 p_procedure_name => PA_DEBUG.G_Err_Stack );
362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
363 RAISE;
364 END get_duration;
365
366
367 END PA_DURATION_UTILS;