1 PACKAGE BODY JTF_CALENDAR_PUB AS
2 /* $Header: jtfclavb.pls 120.4.12010000.3 2009/09/18 08:23:40 anangupt ship $ */
3
4 -- ************************************************************************
5 -- Start of Comments
6 -- Package Name : JTF_CLAENDAR_PUB
7 -- Purpose : Joint Task Force core Calendar Public API's
8 -- This package is for finding the availability,
9 -- working shift hours of a particular resource
10 -- during a specified period
11 -- Procedures : (See below for specification)
12 -- Notes : This package is publicly available for use
13 -- History : 09/29/99 VMOVVA created
14 -- History : 11/02/01 Chan-ik Jang Changed jtf_task_assignments
15 -- to jtf_task_all_assignments
16 -- History : 01/08/02 JAWANG Undo Chan-ik's Changes
17 -- 03/05/02 JAWANG returned attribute1 - 15
18 -- in Get_Resource_Shifts API.
19 -- 03/13/02 JAWANG Fixed GSCC Warning of to_date
20 -- 03/27/03 ABRAINA Modified cursors in Get_available_time
21 -- and Get_Res_Schedule.
22 -- 04/10/03 ABRAINA Modified code in Get_Res_Schedule.
23 -- 04/28/03 ABRAINA Modified all cursor c_cal_shift_constr
24 -- and work_hrs for first day of week change
25 -- 06/16/03 ABRAINA Fixed GSCC warning.
26 -- 08/11/03 ABRAINA Added ResourceDt_To_ServerDT
27 -- 12/12/05 SBARAT Changed jtf_rs_resources_vl to jtf_task_resources_vl
28 -- due to MOAC change, bug# 4455792
29 -- End of Comments
30 -- ************************************************************************
31 G_PKG_NAME CONSTANT VARCHAR2(30):= 'JTF_CALENDAR_PUB';
32 G_EXC_REQUIRED_FIELDS_NULL EXCEPTION;
33 --G_EXC_NOT_VALID_RESOURCE EXCEPTION;
34 L_PARAMETERS VARCHAR2(200);
35 --
36 -- Added sort by jawang on 06/27/2002
37 procedure sort_tab(l_tab in out NOCOPY SHIFT_TBL_TYPE ) ;
38 procedure sort_tab_attr(l_tab in out NOCOPY SHIFT_TBL_ATTRIBUTES_TYPE) ;
39 procedure bubble ( p_index in integer ,
40 l_tab in out NOCOPY SHIFT_TBL_TYPE ) ;
41 procedure bubble_attr ( p_index in integer ,
42 l_tab in out NOCOPY SHIFT_TBL_ATTRIBUTES_TYPE ) ;
43
44 Function check_for_required_fields
45 (p_resource_id IN NUMBER := get_g_miss_num ,
46 p_resource_type IN VARCHAR2 := get_g_miss_char,
47 p_start_date IN DATE := get_g_miss_date,
48 p_end_date IN DATE := get_g_miss_date,
49 p_duration IN NUMBER := get_g_miss_num
50 )
51 return boolean is
52 begin
53 if p_resource_id is null or
54 p_resource_type is null or
55 p_start_date is null or
56 p_end_date is null or
57 p_duration is null THEN
58 return(FALSE);
59 else
60 return(TRUE);
61 end if;
62 end;
63
64 -- Added sort by jawang on 06/27/2002
65 /******** Sort Procedure ****************/
66 procedure sort_tab(l_tab in out NOCOPY SHIFT_TBL_TYPE )
67 is
68 l_last number;
69 l_hi number;
70 l_lo number;
71 l_up_datetime date;
72 l_dw_datetime date;
73 begin
74 begin
75 l_last := l_tab.last;
76 exception
77 when collection_is_null then return;
78 end;
79 if l_last is null then return; end if;
80 for l_hi in 2 .. l_last
81 loop
82 --
83 -- Modified for bug 3510573 by ABRAINA
84 --
85 -- Modified for bug 3891896 by ABRAINA
86 --
87 l_up_datetime := to_date( (to_char(l_tab(l_hi).shift_date,'DD/MM/RRRR')||' '||l_tab(l_hi).start_time),'DD/MM/RRRR HH24:MI');
88 l_dw_datetime := to_date( (to_char(l_tab(l_hi-1).shift_date,'DD/MM/RRRR')||' '||l_tab(l_hi-1).start_time),'DD/MM/RRRR HH24:MI');
89 if l_up_datetime < l_dw_datetime then
90 --if l_tab(l_hi).start_time < l_tab(l_hi-1).start_time then
91 --
92 -- End Modification
93 --
94 bubble(l_hi, l_tab);
95 for l_lo in reverse 2 .. l_hi-1
96 loop
97 if l_tab(l_lo).start_time < l_tab(l_lo-1).start_time then
98 bubble(l_lo, l_tab);
99 else
100 exit;
101 end if;
102 end loop;
103 end if;
104 end loop;
105 end sort_tab;
106
107 -- added by sudhir for sorting attribute type table
108
109 /******** Sort Procedure ****************/
110 procedure sort_tab_attr(l_tab in out NOCOPY SHIFT_TBL_ATTRIBUTES_TYPE)
111 is
112 l_last number;
113 l_hi number;
114 l_lo number;
115 l_up_datetime date;
116 l_dw_datetime date;
117 begin
118 begin
119 l_last := l_tab.last;
120 exception
121 when collection_is_null then return;
122 end;
123 if l_last is null then return; end if;
124 for l_hi in 2 .. l_last
125 loop
126 --
127 -- Modified for bug 3510573 by ABRAINA
128 --
129 -- Modified for bug 3891896 by ABRAINA
130 --
131 l_up_datetime := to_date( (to_char(l_tab(l_hi).shift_date,'DD/MM/RRRR')||' '||l_tab(l_hi).start_time),'DD/MM/RRRR HH24:MI');
132 l_dw_datetime := to_date( (to_char(l_tab(l_hi-1).shift_date,'DD/MM/RRRR')||' '||l_tab(l_hi-1).start_time),'DD/MM/RRRR HH24:MI');
133 if l_up_datetime < l_dw_datetime then
134 --if l_tab(l_hi).start_time < l_tab(l_hi-1).start_time then
135 --
136 -- End Modification
137 --
138 bubble_attr(l_hi, l_tab);
139 for l_lo in reverse 2 .. l_hi-1
140 loop
141 if l_tab(l_lo).start_time < l_tab(l_lo-1).start_time then
142 bubble_attr(l_lo, l_tab);
143 else
144 exit;
145 end if;
146 end loop;
147 end if;
148 end loop;
149 end sort_tab_attr;
150
151
152 -- bubble up the row below this one
153 procedure bubble ( p_index in integer ,
154 l_tab in out NOCOPY SHIFT_TBL_TYPE ) is
155 l_rec Shift_Rec_Type;
156 begin
157 l_rec := l_tab(p_index);
158 l_tab(p_index) := l_tab(p_index-1);
159 l_tab(p_index-1) := l_rec;
160 end bubble;
161
162 -- added by sudhir 25/04/2002
163 -- bubble up the row below this one
164 procedure bubble_attr ( p_index in integer ,
165 l_tab in out NOCOPY SHIFT_TBL_ATTRIBUTES_TYPE) is
166 l_rec Shift_Rec_Attributes_Type;
167 begin
168 l_rec := l_tab(p_index);
169 l_tab(p_index) := l_tab(p_index-1);
170 l_tab(p_index-1) := l_rec;
171 end bubble_attr;
172
173 --
174 -- *************** Is Resource Available *********************
175 --
176 PROCEDURE Is_Res_Available
177 ( p_api_version IN NUMBER,
178 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
179 p_resource_id IN NUMBER,
180 p_resource_type IN VARCHAR2,
181 p_start_date_time IN DATE,
182 p_duration IN NUMBER,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_msg_count OUT NOCOPY NUMBER,
185 x_msg_data OUT NOCOPY VARCHAR2,
186 x_avail OUT NOCOPY VARCHAR2
187 )
188 IS
189 l_api_name CONSTANT VARCHAR2(30) := 'Is_Res_Available';
190 l_api_version CONSTANT NUMBER := 1.0;
191 l_return_status VARCHAR2(1);
192 l_msg_count NUMBER;
193 l_msg_data VARCHAR2(250);
194 l_shift SHIFT_TBL_TYPE;
195 v_begin_time VARCHAR2(5);
196 v_end_time VARCHAR2(5);
197
198 BEGIN
199 -- Standard call to check for call compatibility.
200 IF NOT FND_API.Compatible_API_Call (l_api_version ,
201 p_api_version ,
202 l_api_name ,
203 G_PKG_NAME )
204 THEN
205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
206 END IF;
207
208 -- Initialize message list if p_init_msg_list is set to TRUE.
209 IF FND_API.to_Boolean( p_init_msg_list ) THEN
210 FND_MSG_PUB.initialize;
211 END IF;
212
213 -- Call to check for required fields
214 IF not check_for_required_fields (p_resource_id =>p_resource_id,
215 p_resource_type =>p_resource_type,
216 p_start_date =>p_start_date_time,
217 p_duration =>p_duration)
218 THEN
219 l_parameters := 'p_resource_id, p_resource_type, p_start_date_time, p_duration';
220 RAISE G_EXC_REQUIRED_FIELDS_NULL;
221 END IF;
222
223 -- Initialize API return status to success
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225
226 JTF_CALENDAR_PUB.Get_Available_Time
227 ( p_api_version => 1.0,
228 p_resource_id => p_resource_id,
229 p_resource_type => p_resource_type,
230 p_start_date => p_start_date_time,
231 p_end_date => p_start_date_time,
232 x_return_status => l_return_status,
233 x_msg_count => l_msg_count,
234 x_msg_data => l_msg_data,
235 x_shift => l_shift
236 );
237
238 v_begin_time := to_char(p_start_date_time,'HH24.MI');
239 v_end_time := to_char((p_start_date_time + p_duration/24),'HH24.MI');
240
241 x_avail := 'N';
242
243 IF v_end_time > v_begin_time THEN
244 for i in 1 .. l_shift.count loop
245 IF v_begin_time >= l_shift(i).start_time
246 AND v_end_time <= l_shift(i).end_time THEN
247 x_avail := 'Y';
248 EXIT;
249 END IF;
250 end loop;
251 END IF;
252
253 EXCEPTION
254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
256 FND_MSG_PUB.Count_And_Get
257 ( p_count => x_msg_count,
258 p_data => x_msg_data
259 );
260 WHEN G_EXC_REQUIRED_FIELDS_NULL THEN
261 x_return_status := FND_API.G_RET_STS_ERROR ;
262 fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
263 fnd_message.set_token('P_PARAMETER', l_parameters);
264 fnd_msg_pub.add;
265 FND_MSG_PUB.Count_And_Get
266 (p_count => x_msg_count,
267 p_data => x_msg_data
268 );
269 WHEN OTHERS THEN
270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
271 fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
272 fnd_message.set_token('ERROR_CODE',SQLCODE);
273 fnd_message.set_token('ERROR_MESSAGE', SQLERRM);
274 fnd_msg_pub.add;
275 FND_MSG_PUB.Count_And_Get
276 ( p_count => x_msg_count,
277 p_data => x_msg_data
278 );
279
280 END Is_Res_Available;
281 --
282 -- ****************** Get Available Time **********************
283 --
284 PROCEDURE Get_Available_Time
285 ( p_api_version IN NUMBER,
286 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
287 p_resource_id IN NUMBER,
288 p_resource_type IN VARCHAR2,
289 p_start_date IN DATE,
290 p_end_date IN DATE,
291 x_return_status OUT NOCOPY VARCHAR2,
292 x_msg_count OUT NOCOPY NUMBER,
293 x_msg_data OUT NOCOPY VARCHAR2,
294 x_shift OUT NOCOPY SHIFT_TBL_TYPE
295 )
296 IS
297
298 -- we are declaring a table of records here again to manuplate the start and end time in DATE datatype.
299 type rec_type is record
300 ( shift_construct_id number
301 , start_time date
302 , end_time date
303 , availability_type varchar2(40) );
304
305 type tbl_type is table of rec_type index by binary_integer;
306
307 l_api_name CONSTANT VARCHAR2(30):= 'Get_Available_Time';
308 l_api_version CONSTANT NUMBER := 1.0;
309
310 -- Gets the shift_id and duration info, used for calculating the right shift pattern based
311 -- on the calendar id selected using the parameters passed resource_id, resource_type
312 -- and requested_date
313
314 cursor shift_info(p_calendar_id NUMBER) is
315 select shift_id,(shift_end_date - shift_start_date) shift_duration
316 from jtf_cal_shift_assign
317 where calendar_id = p_calendar_id
318 order by shift_sequence_number;
319
320 -- Based on the shift_id corresponding shift construction is found.
321
322 cursor c_cal_shift_constr(p_shift_id NUMBER,p_day date, p_uot_value NUMBER) is
323 select shift_construct_id,
324 begin_time start_constr,
325 end_time end_constr,
326 availability_type_code
327 from jtf_cal_shift_constructs
328 where shift_id = p_shift_id
329 --
330 --Modified condition to take care first Day of week for Bug 1342982
331 --
332 and to_char(begin_time, 'd') = to_char(p_day, 'd')
333 -- and unit_of_time_value = p_uot_value
334 --end
335 -- validate shift construct
336 -- added by jawang on 06/07/2002 to fix bug 2393255
337 and ( ( p_day between start_date_active
338 and end_date_active)
339 or (start_date_active <=p_day
340 and end_date_active IS NULL));
341
342 -- Get all the exceptions and tasks for the resource on the requested date.
343 -- Added two new parameters p_tz_enabled, p_server_tz_id and
344 -- modified the query accordingly. Done by SBARAT on 23/06/2005 for Bug# 4443443
345 cursor c_cal_except(p_calendar_id NUMBER, p_day date, p_res_id NUMBER, p_res_type VARCHAR2,p_tz_enabled VARCHAR2,p_server_tz_id NUMBER,p_res_Timezone_id NUMBER) is
346 select Decode(p_tz_enabled,'Y',
347 Decode(p_res_Timezone_id,NULL, e.start_date_time,
348 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(p_res_Timezone_id,
349 p_server_tz_id,
350 e.start_date_time),
351 e.start_date_time)
352 ),
353 e.start_date_time) start_except,
354 Decode(p_tz_enabled,'Y',
355 Decode(p_res_Timezone_id,NULL, e.end_date_time,
356 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(p_res_Timezone_id,
357 p_server_tz_id,
358 e.end_date_time),
359 e.end_date_time)
360 ),
361 e.end_date_time) end_except
362 from jtf_cal_exception_assign a
363 ,jtf_cal_exceptions_vl e
364 where a.calendar_id = p_calendar_id
365 and a.exception_id = e.exception_id
366 -- validate exception assignment
367 and ( p_day >= trunc(Decode(p_tz_enabled,'Y',
368 Decode(p_res_Timezone_id,NULL, a.start_date_active,
369 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(p_res_Timezone_id,
370 p_server_tz_id,
371 a.start_date_active),
372 a.start_date_active)
373 ),
374 a.start_date_active))
375 or a.start_date_active is null)
376 and ( p_day <= trunc(Decode(p_tz_enabled,'Y',
377 Decode(p_res_Timezone_id,NULL, a.end_date_active,
378 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(p_res_Timezone_id,
379 p_server_tz_id,
380 a.end_date_active),
381 a.end_date_active)
382 ),
383 a.end_date_active))
384 or a.end_date_active is null)
385 -- validate exception
386 and p_day between trunc(Decode(p_tz_enabled,'Y',
387 Decode(p_res_Timezone_id,NULL, e.start_date_time,
388 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(p_res_Timezone_id,
389 p_server_tz_id,
390 e.start_date_time),
391 e.start_date_time)
392 ),
393 e.start_date_time))
394 and trunc(Decode(p_tz_enabled,'Y',
395 Decode(p_res_Timezone_id,NULL, e.end_date_time,
396 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(p_res_Timezone_id,
397 p_server_tz_id,
398 e.end_date_time),
399 e.end_date_time)
400 ),
401 e.end_date_time))
402 UNION ALL
403 -- we are picking up from scheduled date form tasks.
404 select Decode(p_tz_enabled,'Y',
405 Decode(t.timezone_id,NULL, t.scheduled_start_date,
406 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
407 p_server_tz_id,
408 t.scheduled_start_date),
409 t.scheduled_start_date)
410 ),
411 t.scheduled_start_date) start_except,
412 Decode(p_tz_enabled,'Y',
413 Decode(t.timezone_id,NULL, t.scheduled_end_date,
414 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
415 p_server_tz_id,
416 t.scheduled_end_date),
417 t.scheduled_end_date)
418 ),
419 t.scheduled_end_date) end_except
420 from jtf_tasks_b t,
421 jtf_task_assignments a,
422 jtf_task_statuses_b s --changed to table from jtf_task_statuses_vl bug #2473783
423 where a.resource_id = p_res_id
424 and a.resource_type_code = p_res_type
425 and Decode(p_tz_enabled,'Y',
426 Decode(t.timezone_id,NULL, t.scheduled_end_date,
427 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
428 p_server_tz_id,
429 t.scheduled_end_date),
430 t.scheduled_end_date)
431 ),
432 t.scheduled_end_date) >= p_day -- Changed to "schedule_end_date" for bug 2817811 by A.Raina.
433 and Decode(p_tz_enabled,'Y',
434 Decode(t.timezone_id,NULL, t.scheduled_start_date,
435 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
436 p_server_tz_id,
437 t.scheduled_start_date),
438 t.scheduled_start_date)
439 ),
440 t.scheduled_start_date) < p_day+1 --- removed trunc bug #2473783
441 and s.task_status_id = a.assignment_status_id
442 and t.task_id = a.task_id
443 and nvl(s.cancelled_flag,'N') <> 'Y'
444 and nvl(s.completed_flag,'N') <> 'Y'
445 and t.scheduled_start_date <> t.scheduled_end_date ; -- bug # 2520762
446
447 --
448 j INTEGER := 0;
449 l_shift_id NUMBER;
450 l_unit_of_time_value NUMBER;
451 l_begin_time VARCHAR2(5);
452 l_end_time VARCHAR2(5);
453 l_calendar_id NUMBER;
454 l_calendar_name jtf_calendars_vl.calendar_name%TYPE; --bug # 2493461 VARCHAR2(240)
455 l_calendar_start_date DATE;
456 l_shifts_total_duration NUMBER;
457 l_left_days NUMBER;
458 l_availability VARCHAR2(40);
459 l_day date;
460 l_range_start date;
461 l_range_end date;
462 l_utv number;
463 l_cnt number;
464 l_count NUMBER;
465 l_shift_date DATE;
466 l_shift SHIFT_TBL_TYPE;
467 l_tbl tbl_type; -- added by Sarvi.
468 l_idx number := 0;
469 l_resource_name jtf_task_resources_vl.resource_name%TYPE; -- bug #2418561
470 l_st_time date;
471
472 l_tz_enabled VARCHAR2(10):=fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS'); -- Added by SBARAT on 23/06/2005 for Bug# 4443443
473 l_server_tz_id Number := to_number (fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
474 l_res_Timezone_id Number;
475
476 BEGIN
477 -- Standard call to check for call compatibility.
478 IF NOT FND_API.Compatible_API_Call (l_api_version,
479 p_api_version,
480 l_api_name,
481 G_PKG_NAME)
482 THEN
483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
484 END IF;
485
486 -- Initialize message list if p_init_msg_list is set to TRUE.
487 IF FND_API.to_Boolean( p_init_msg_list ) THEN
488 FND_MSG_PUB.initialize;
489 END IF;
490
491 -- Call to check for required fields
492 IF not check_for_required_fields (p_resource_id =>p_resource_id,
493 p_resource_type =>p_resource_type,
494 p_start_date =>p_start_date,
495 p_end_date =>p_start_date)
496 THEN
497 l_parameters := 'p_resource_id, p_resource_type, p_start_date, p_end_date';
498 RAISE G_EXC_REQUIRED_FIELDS_NULL;
499 END IF;
500
501 -- Initialize API return status to success
502 x_return_status := FND_API.G_RET_STS_SUCCESS;
503
504 l_shift_date := trunc(p_start_date);
505
506 while l_shift_date <= p_end_date loop
507
508 begin --(1)
509 -- get the primary calendar for a resource on the given date
510 select a.calendar_id,b.start_date_active
511 into l_calendar_id,l_calendar_start_date
512 from jtf_cal_resource_assign a,
513 jtf_calendars_b b
514 where a.resource_id = p_resource_id
515 and a.resource_type_code = p_resource_type
516 and a.calendar_id = b.calendar_id
517 and a.primary_calendar_flag = 'Y'
518 and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),l_shift_date);
519 -- Commented for bug 3891896 by ABRAINA
520 -- and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),to_date(get_g_miss_date,'DD/MM/RRRR'));
521 -- end
522
523 -- if condition added for bug 3270116 by ABRAINA
524 IF Validate_Cal_Date(l_calendar_id, l_shift_date) THEN -- (1)
525 -- get the shift in which the given date falls for the above calendar
526 --
527 begin --(2)
528 select sum(shift_end_date - shift_start_date)
529 into l_shifts_total_duration
530 from jtf_cal_shift_assign
531 where calendar_id = l_calendar_id;
532
533 -- Based on the mod value the shift is selected. This happens when two shifts are attached to the
534 -- calendar and a pattern of two in sequence is required.
535 l_left_days := mod((l_shift_date - l_calendar_start_date),l_shifts_total_duration);
536 -- This cursor will have all the shift attached to the resources primary calendar
537 -- We loop thru the cursor and based on the condition we find the correct shift_id
538 l_shift_id := null;
539 for c in shift_info(l_calendar_id) loop
540 l_left_days := l_left_days - c.shift_duration;
541 IF l_left_days < 0 THEN -- earlier it was <= it was not picking the correct shift.
542 l_shift_id := c.shift_id;
543 EXIT;
544 END IF;
545 end loop;
546 --
547 -- Find the day of the Requested Date
548 --
549 l_utv := to_char(l_shift_date, 'd');
550 --
551 -- Find the working hours on the Requested day
552 --
553 l_tbl.delete;
554 l_idx := 0;
555 FOR j in c_cal_shift_constr(l_shift_id,l_shift_date, l_utv) LOOP
556 l_idx := l_idx + 1;
557 l_tbl(l_idx).shift_construct_id := j.shift_construct_id;
558 --added this condition for 24hr shift .. sudarsana 22 Oct 2001
559 IF trunc(j.start_constr) = trunc(j.end_constr) THEN
560 l_tbl(l_idx).start_time := l_shift_date + (j.start_constr - trunc(j.start_constr));
561 l_tbl(l_idx).end_time := l_shift_date + (j.end_constr - trunc(j.end_constr));
562 else
563 l_tbl(l_idx).start_time := l_shift_date + (j.start_constr - trunc(j.start_constr));
564 l_tbl(l_idx).end_time := l_shift_date + (j.end_constr - trunc(j.end_constr)) +
565 (trunc(j.end_constr) - trunc(j.start_constr));
566 end if;
567 l_tbl(l_idx).availability_type := j.availability_type_code;
568 END LOOP;
569
570 --
571 -- Added for Simplex Timezone Enh # 3040681 by ABRAINA
572 --
573 If fnd_profile.value_specific('ENABLE_TIMEZONE_CONVERSIONS') = 'Y' Then
574 l_res_Timezone_id := Get_Res_Timezone_Id (p_resource_id, p_resource_type);
575 If l_res_Timezone_id <> l_server_tz_id Then
576 l_idx := 0;
577 For r in 1..l_tbl.count loop
578 l_idx := l_idx + 1;
579 l_tbl(l_idx).start_time := ResourceDt_To_ServerDT(l_tbl(l_idx).start_time,l_res_Timezone_id,l_server_tz_id);
580 l_tbl(l_idx).end_time := ResourceDt_To_ServerDT(l_tbl(l_idx).end_time,l_res_Timezone_id,l_server_tz_id);
581 End Loop;
582 End If;
583 End If;
584
585 --
586 -- Deduct all Task Assignments, Exceptions from working hours on the requested date
587 --
588 -- Modified by SBARAT on 23/06/2005 for Bug# 4443443
589 for m in c_cal_except(l_calendar_id,l_shift_date, p_resource_id, p_resource_type,l_tz_enabled,l_server_tz_id,l_res_Timezone_id)
590 loop
591 l_cnt := l_tbl.count;
592 for n in 1..l_cnt
593 loop -- loop thru the table loaded with shifts.
594 -- If we find an exception satisfying this condition then, we have
595 -- to change the start/end time of the shifts accordingly. Like shift 8 - 16
596 -- and exception 10-11, then we need to split the record into two like
597 -- 8 - 10 and 11 - 16, showing the resource availablity.
598 -- dbms_output.put_line('shifts: '||to_Char(l_tbl(n).start_time,'DD-MON-YYYY HH24:MI')|| '-' ||to_char(l_tbl(n).end_time,'dd-mon-yyyy hh24:mi'));
599 -- dbms_output.put_line('tasks: '||to_Char(m.start_except,'DD-MON-YYYY HH24:MI')|| '-' ||to_char(m.end_except,'dd-mon-yyyy hh24:mi'));
600
601 if m.start_except > l_tbl(n).start_time
602 and m.start_except < l_tbl(n).end_time
603 and m.end_except > l_tbl(n).start_time
604 and m.end_except < l_tbl(n).end_time
605 then
606 -- an extra entry is created at the end of the pl/sql table
607 -- is it a problem that the ordering is disrupted this way?
608 l_idx := l_tbl.count + 1;
609 l_tbl(l_idx).shift_construct_id := l_tbl(n).shift_construct_id;
610 l_tbl(l_idx).start_time := m.end_except; -- this is for the new entry
611 l_tbl(l_idx).end_time := l_tbl(n).end_time; -- this is for the new entry
612 l_tbl(l_idx).availability_type := l_tbl(n).availability_type;
613 l_tbl(n).end_time := m.start_except; -- This changes the existing entries end_time.
614 -- Various possibilities of exception are checked with shift times, to be chopped off.
615 elsif m.start_except < l_tbl(n).start_time
616 and m.end_except > l_tbl(n).start_time
617 and m.end_except < l_tbl(n).end_time
618 then
619 l_tbl(n).start_time := m.end_except;
620 elsif m.start_except > l_tbl(n).start_time
621 and m.start_except < l_tbl(n).end_time
622 and m.end_except > l_tbl(n).end_time
623 then
624 l_tbl(n).end_time := m.start_except;
625 -- added on 28, Sep 2000 start
626 elsif m.start_except >= l_tbl(n).start_time
627 and m.start_except < l_tbl(n).end_time
628 and m.end_except < l_tbl(n).end_time
629 then
630 l_tbl(n).start_time := m.end_except;
631 elsif m.start_except > l_tbl(n).start_time
632 and m.start_except < l_tbl(n).end_time
633 and m.end_except <= l_tbl(n).end_time
634 then
635 l_tbl(n).end_time := m.start_except;
636 -- added on 28, Sep 2000 end
637 elsif m.start_except = l_tbl(n).start_time
638 and m.end_except = l_tbl(n).end_time
639 then
640 l_tbl(n).start_time := null;
641 l_tbl(n).end_time := null;
642 l_tbl(n).availability_type := null;
643 -- l_tbl.delete; # bug :2595871
644 elsif m.start_except = l_tbl(n).start_time
645 and m.end_except < l_tbl(n).end_time
646 then
647 l_tbl(n).start_time := null;
648 l_tbl(n).end_time := null;
649 l_tbl(n).availability_type := null;
650 l_tbl.delete;
651 -- added Jan10, 2001, start.
652 elsif m.start_except = l_tbl(n).start_time
653 and m.end_except > l_tbl(n).end_time
654 then
655 l_tbl(n).start_time := null;
656 l_tbl(n).end_time := null;
657 l_tbl(n).availability_type := null;
658 -- l_tbl.delete; # bug :2595871
659
660 elsif m.start_except < l_tbl(n).start_time
661 and m.end_except = l_tbl(n).end_time
662 then
663 l_tbl(n).start_time := null;
664 l_tbl(n).end_time := null;
665 l_tbl(n).availability_type := null;
666 -- l_tbl.delete; # bug :2595871
667 -- added Jan10, 2001, end.
668 elsif m.start_except < l_tbl(n).start_time -- When exception falls out of the range
669 and m.end_except >l_tbl(n).end_time
670 then
671 l_tbl(n).start_time := null; -- added for bug #:2595871
672 l_tbl(n).end_time := null;
673 l_tbl(n).availability_type := null;
674 -- l_tbl.delete; # bug :2595871
675 end if;
676 end loop;
677 end loop;
678
679 -- store found shift constructs for this day in output pl/sql table
680 for r in 1..l_tbl.count
681 loop
682 --added this condition for 24hr shift .. sudarsana 22 Oct 2001
683 if ((trunc(l_tbl(r).end_time)) = trunc((l_tbl(r).start_time))) THEN
684 l_idx := x_shift.count + 1;
685 x_shift(l_idx).shift_construct_id := l_tbl(r).shift_construct_id;
686 x_shift(l_idx).shift_date := trunc(l_tbl(r).start_time);
687 -- reformat the start and end times just at the end of the procedure
688 -- when the calculations are done
689 x_shift(l_idx).start_time := to_char(l_tbl(r).start_time,'hh24.mi');
690 x_shift(l_idx).end_time := to_char(l_tbl(r).end_time,'hh24.mi');
691 x_shift(l_idx).availability_type := l_tbl(r).availability_type;
692 else
693 --added this condition for 24hr shift .. sudarsana 22 Oct 2001
694 l_st_time := l_tbl(r).start_time;
695 while(trunc(l_st_time) <= trunc(l_tbl(r).end_time))
696 loop
697 l_idx := x_shift.count + 1;
698 x_shift(l_idx).shift_construct_id := l_tbl(r).shift_construct_id;
699 x_shift(l_idx).shift_date := trunc(l_st_time);
700 if(trunc(l_st_time) = trunc(l_tbl(r).start_time))
701 then
702 x_shift(l_idx).start_time := to_char(l_tbl(r).start_time,'hh24.mi');
703 else
704 x_shift(l_idx).start_time := '00.00';
705 end if;
706 if(trunc(l_st_time) = trunc(l_tbl(r).end_time))
707 then
708 x_shift(l_idx).end_time := to_char(l_tbl(r).end_time,'hh24.mi');
709 else
710 x_shift(l_idx).end_time := '23.59';
711 end if;
712 x_shift(l_idx).availability_type := l_tbl(r).availability_type;
713
714 l_st_time := l_st_time + 1;
715 end loop;
716 end if;
717 end loop;
718 exception
719 when no_data_found then
720 NULL;
721 end; --2
722 end if; --(1)
723 exception
724 when no_data_found then
725 NULL;
726 end; -- 1
727 l_shift_date := l_shift_date + 1;
728 end loop;
729 --
730 -- Update return status to Success if there is atleast one available time slot
731 if x_shift.count = 0 then
732 x_return_status := fnd_api.g_ret_sts_error ;
733 fnd_message.set_name('JTF','JTF_CAL_NO_SHIFT_CONSTR_FOUND');
734 --fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS_FOR_RESOURCE');
735 --fnd_message.set_token('P_RESOURCE_NAME', l_resource_name);
736 fnd_msg_pub.add;
737 fnd_msg_pub.count_and_get( p_count => x_msg_count
738 , p_data => x_msg_data );
739 end if;
740
741 -- Added sort by jawang on 06/27/2002
742 if x_shift.count > 0
743 then
744 -- sort the out table
745 sort_tab(x_shift);
746 end if;
747
748 EXCEPTION
749 when g_exc_required_fields_null then
750 x_return_status := fnd_api.g_ret_sts_error ;
751 fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
752 fnd_message.set_token('P_PARAMETER', l_parameters);
753 fnd_msg_pub.add;
754 fnd_msg_pub.count_and_get ( p_count => x_msg_count
755 , p_data => x_msg_data );
756
757 when fnd_api.g_exc_unexpected_error then
758 x_return_status := fnd_api.g_ret_sts_unexp_error;
759 fnd_msg_pub.count_and_get ( p_count => x_msg_count
760 , p_data => x_msg_data );
761
762 when others then
763 x_return_status := fnd_api.g_ret_sts_unexp_error ;
764 fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
765 fnd_message.set_token('ERROR_CODE',sqlcode);
766 fnd_message.set_token('ERROR_MESSAGE',sqlerrm);
767 fnd_msg_pub.count_and_get ( p_count => x_msg_count
768 , p_data => x_msg_data );
769 END get_available_time;
770
771
772 --
773 -- ****************** Get Available Time Slot **********************
774 --
775 PROCEDURE Get_Available_Slot
776 (p_api_version IN NUMBER ,
777 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
778 p_resource_id IN NUMBER ,
779 p_resource_type IN VARCHAR2 ,
780 p_start_date_time IN DATE ,
781 p_end_date_time IN DATE ,
782 p_duration IN NUMBER ,
783 x_return_status OUT NOCOPY VARCHAR2 ,
784 x_msg_count OUT NOCOPY NUMBER ,
785 x_msg_data OUT NOCOPY VARCHAR2 ,
786 x_slot_start_date OUT NOCOPY DATE ,
787 x_slot_end_date OUT NOCOPY DATE ,
788 x_shift_construct_id OUT NOCOPY NUMBER ,
789 x_availability_type OUT NOCOPY VARCHAR2
790 )
791 IS
792 l_api_name CONSTANT VARCHAR2(30) := 'Get_Available_Slot';
793 l_api_version CONSTANT NUMBER := 1.0;
794 l_return_status VARCHAR2(1);
795 l_msg_count NUMBER;
796 l_msg_data VARCHAR2(250);
797 l_shift jtf_calendar_pub_24hr.SHIFT_TBL_TYPE;
798 v_start_date DATE;
799 v_end_date DATE;
800 v_slot_start_date DATE;
801 v_slot_end_date DATE;
802 i number;
803
804 BEGIN
805 -- Standard call to check for call compatibility.
806 IF NOT FND_API.Compatible_API_Call (l_api_version ,
807 p_api_version ,
808 l_api_name ,
809 G_PKG_NAME )
810 THEN
811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
812 END IF;
813
814 -- Initialize message list if p_init_msg_list is set to TRUE.
815 IF FND_API.to_Boolean( p_init_msg_list ) THEN
816 FND_MSG_PUB.initialize;
817 END IF;
818
819 -- Call to check for required fields
820 IF not check_for_required_fields (p_resource_id =>p_resource_id,
821 p_resource_type =>p_resource_type,
822 p_start_date =>p_start_date_time,
823 p_end_date =>p_end_date_time,
824 p_duration =>p_duration)
825 THEN
826 l_parameters := 'p_resource_id, p_resource_type, p_start_date_time, p_end_date_time, p_duration';
827 RAISE G_EXC_REQUIRED_FIELDS_NULL;
828 END IF;
829
830 -- Initialize API return status to success
831 x_return_status := FND_API.G_RET_STS_SUCCESS;
832 --
833 JTF_CALENDAR_PUB_24hr.Get_Available_Slot
834 ( p_api_version => l_api_version ,
835 p_init_msg_list => p_init_msg_list,
836 p_resource_id => p_resource_id,
837 p_resource_type => p_resource_type,
838 p_start_date_time => p_start_date_time,
839 p_end_date_time => p_end_date_time,
840 p_duration => p_duration,
841 x_return_status => x_return_status,
842 x_msg_count => x_msg_count,
843 x_msg_data => x_msg_data,
844 x_slot_start_date => x_slot_start_date,
845 x_slot_end_date => x_slot_end_date,
846 x_shift_construct_id => x_shift_construct_id,
847 x_availability_type => x_availability_type );
848
849 EXCEPTION
850 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
852 FND_MSG_PUB.Count_And_Get
853 ( p_count => x_msg_count,
854 p_data => x_msg_data
855 );
856 WHEN G_EXC_REQUIRED_FIELDS_NULL THEN
857 x_return_status := FND_API.G_RET_STS_ERROR ;
858 fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
859 fnd_message.set_token('P_PARAMETER', l_parameters);
860 fnd_msg_pub.add;
861 FND_MSG_PUB.Count_And_Get
862 (p_count => x_msg_count,
863 p_data => x_msg_data
864 );
865 WHEN OTHERS THEN
866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
867 fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
868 fnd_message.set_token('ERROR_CODE',SQLCODE);
869 fnd_message.set_token('ERROR_MESSAGE', SQLERRM);
870 fnd_msg_pub.add;
871 FND_MSG_PUB.Count_And_Get
872 (p_count => x_msg_count,
873 p_data => x_msg_data
874 );
875
876 END Get_Available_Slot;
877 --
878 -- ************* Get Resource Shifts *******************
879 --
880 PROCEDURE get_resource_shifts
881 ( p_api_version in number
882 , p_init_msg_list in varchar2
883 , p_resource_id in number
884 , p_resource_type in varchar2
885 , p_start_date in date
886 , p_end_date in date
887 , x_return_status out NOCOPY varchar2
888 , x_msg_count out NOCOPY number
889 , x_msg_data out NOCOPY varchar2
890 , x_shift out NOCOPY shift_tbl_type
891 )
892 IS
893 type rec_type is record
894 ( shift_construct_id number
895 , start_time date
896 , end_time date
897 , availability_type varchar2(40) );
898
899 type tbl_type is table of rec_type index by binary_integer;
900
901 cursor shift_info(p_calendar_id NUMBER) is
902 select shift_id,(shift_end_date - shift_start_date) shift_duration
903 from jtf_cal_shift_assign
904 where calendar_id = p_calendar_id
905 order by shift_sequence_number;
906
907 cursor c_cal_shift_constr(p_shift_id NUMBER, p_day date, p_uot_value NUMBER) is
908 select shift_construct_id,
909 begin_time start_constr,
910 end_time end_constr,
911 availability_type_code
912 from jtf_cal_shift_constructs
913 where shift_id = p_shift_id
914 --
915 --Modified condition to take care first Day of week for Bug 1342982
916 --
917 and to_char(begin_time, 'd') = to_char(p_day, 'd')
918 -- validate shift construct
919 -- added by jawang on 06/07/2002 to fix bug 2393255
920 and ( ( p_day between start_date_active
921 and end_date_active)
922 or (start_date_active <=p_day
923 and end_date_active IS NULL));
924
925
926 cursor c_cal_except
927 ( p_calendar_id number
928 , p_day date )
929 is
930 select e.start_date_time start_except
931 , e.end_date_time end_except
932 from jtf_cal_exception_assign a
933 , jtf_cal_exceptions_vl e
934 where a.calendar_id = p_calendar_id
935 and a.exception_id = e.exception_id
936 -- validate exception assignment
937 and ( p_day >= trunc(a.start_date_active)
938 or a.start_date_active is null)
939 and ( p_day <= trunc(a.end_date_active)
940 or a.end_date_active is null)
941 -- validate exception
942 and p_day between trunc(e.start_date_time)
943 and trunc(e.end_date_time);
944
945 -- added the date check for bug #1355824
946
947 l_api_name constant varchar2(30) := 'Get_Resource_Shifts';
948 l_api_version constant number := 1.0;
949 l_parameters varchar2(2000) := null;
950 g_exc_required_fields_null exception;
951 l_range_start date;
952 l_range_end date;
953 l_day date;
954 l_utv number;
955 l_idx number := 0;
956 l_tbl tbl_type;
957 l_cnt number;
958 l_shifts_total_duration number;
959 l_shift_date date;
960 l_left_days number;
961 l_calendar_id number;
962 l_shift_id number;
963
964 l_calendar_name jtf_calendars_vl.calendar_name%TYPE; -- bug # 2493461 varchar2(100)
965 l_calendar_start_date date;
966 l_exp_flg varchar2(1) := 'N';
967 l_start_date_time date;
968 l_st_time date;
969
970 l_server_tz_id Number := to_number (fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
971 l_res_Timezone_id Number;
972
973 l_return_status varchar2(1) := FND_API.G_RET_STS_ERROR ;
974
975 l_resource_name jtf_task_resources_vl.resource_name%TYPE;-- bug # 2418561
976
977 BEGIN
978 -- standard call to check for call compatibility.
979 if not fnd_api.compatible_api_call
980 ( l_api_version
981 , p_api_version
982 , l_api_name
983 , JTF_CALENDAR_PUB.g_pkg_name )
984 then
985 raise fnd_api.g_exc_unexpected_error;
986 end if;
987
988 -- initialize message list if p_init_msg_list is set to true.
989 if fnd_api.to_boolean ( p_init_msg_list )
990 then
991 fnd_msg_pub.initialize;
992 end if;
993
994 -- call to check for required fields
995 if not JTF_CALENDAR_PUB.check_for_required_fields
996 ( p_resource_id => p_resource_id
997 , p_resource_type => p_resource_type
998 , p_start_date => p_start_date
999 , p_end_date => p_start_date )
1000 then
1001 l_parameters := 'p_resource_id, p_resource_type, p_start_date, p_end_date';
1002 raise g_exc_required_fields_null;
1003 end if;
1004
1005 -- initialize api return status to success
1006 x_return_status := fnd_api.g_ret_sts_success;
1007
1008 -- bug# 1344222
1009 -- Comment out by jawang on 06/17/2002
1010
1011 -- get all valid resource-to-calendar assignments for this resource in
1012 -- this period ordered by start date
1013 -- because there is a primary flag, only one record is expected
1014
1015 -- This code is added to get resource name to be printed in error message.
1016 BEGIN
1017 select resource_name
1018 into l_resource_name
1019 --from jtf_rs_all_resources_vl
1020 --Modified by jawang to fix the bug 2416932
1021 from jtf_task_resources_vl
1022 where resource_id = p_resource_id
1023 and resource_type = p_resource_type;
1024 EXCEPTION
1025 WHEN Others THEN
1026 NULL;
1027 END;
1028
1029 l_shift_date := trunc(p_start_date);
1030
1031 While l_shift_date <= p_end_date Loop
1032 --added l_shift_date in valid_cal loop bug #1355824
1033
1034 -- get the primary calendar for a resource on the given date
1035 --
1036 BEGIN --(1)
1037 select a.calendar_id,b.calendar_name,b.start_date_active,a.start_date_time
1038 into l_calendar_id,l_calendar_name,l_calendar_start_date,l_start_date_time
1039 from jtf_cal_resource_assign a,
1040 jtf_calendars_vl b
1041 where a.resource_id = p_resource_id
1042 and a.resource_type_code = p_resource_type
1043 and a.calendar_id = b.calendar_id
1044 and a.primary_calendar_flag = 'Y'
1045 -- Commented for bug 3891896 by ABRAINA
1046 -- and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),to_date(get_g_miss_date,'DD/MM/RRRR'));
1047 and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),l_shift_date);
1048
1049 -- Added for bug 3270116
1050 l_return_status := FND_API.G_RET_STS_SUCCESS;
1051
1052 -- if condition added for bug 3270116 by ABRAINA
1053 IF Validate_Cal_Date(l_calendar_id, l_shift_date)
1054 THEN
1055
1056 l_tbl.delete;
1057 l_idx := 0;
1058
1059 BEGIN --(2)
1060 select sum(shift_end_date - shift_start_date)
1061 into l_shifts_total_duration
1062 from jtf_cal_shift_assign
1063 where calendar_id = l_calendar_id;
1064
1065 l_left_days := mod((l_shift_date - l_calendar_start_date),l_shifts_total_duration);
1066
1067 l_shift_id := null;
1068 for c in shift_info(l_calendar_id) loop
1069 l_left_days := l_left_days - c.shift_duration;
1070 IF l_left_days < 0 THEN
1071 l_shift_id := c.shift_id;
1072 EXIT;
1073 END IF;
1074 end loop;
1075 -- Added by Sarvi
1076
1077 -- calculate unit of time value
1078 -- this is dependant on nls setting
1079 l_utv := to_char(l_shift_date,'d');
1080 l_tbl.delete;
1081 l_idx := 0;
1082 for j in c_cal_shift_constr ( l_shift_id
1083 , l_shift_date
1084 , l_utv )
1085 loop
1086 l_idx := l_idx + 1;
1087 l_tbl(l_idx).shift_construct_id := j.shift_construct_id;
1088 -- The date part of the shift construct start is just a container
1089 -- without real meaning. In order to process the multi-day
1090 -- exceptions more easily, the requested day is added to it, so
1091 -- that the resulting datetime has a real meaning.
1092 -- added this chg for the 24 hr shift split .. sudarsana 23 oct 2001
1093 IF trunc(j.start_constr) = trunc(j.end_constr) THEN
1094 l_tbl(l_idx).start_time := l_shift_date + (j.start_constr - trunc(j.start_constr));
1095 l_tbl(l_idx).end_time := l_shift_date + (j.end_constr - trunc(j.end_constr));
1096 else
1097 l_tbl(l_idx).start_time := l_shift_date + (j.start_constr - trunc(j.start_constr));
1098 l_tbl(l_idx).end_time := l_shift_date + (j.end_constr - trunc(j.end_constr)) +
1099 (trunc(j.end_constr) - trunc(j.start_constr));
1100 end if;
1101 l_tbl(l_idx).availability_type := j.availability_type_code;
1102 end loop;
1103
1104
1105
1106 -- deduct all exceptions from working hours on the requested day
1107 -- exceptions can consist of more than one day
1108 for m in c_cal_except ( l_calendar_id
1109 , l_shift_date )
1110 loop -- loop the exception cursor
1111 l_cnt := l_tbl.count;
1112
1113 for n in 1..l_cnt
1114 loop -- loop thru the table loaded with shifts.
1115
1116 -- If we find an exception satisfying this condition then, we have
1117 -- to change the start/end time of the shifts accordingly. Like shift 8 - 16
1118 -- and exception 10-11, then we need to split the record into two like
1119 -- 8 - 10 and 11 - 16, showing the resource availablity.
1120 if m.start_except > l_tbl(n).start_time
1121 and m.start_except < l_tbl(n).end_time
1122 and m.end_except > l_tbl(n).start_time
1123 and m.end_except < l_tbl(n).end_time
1124 then
1125 -- an extra entry is created at the end of the pl/sql table
1126 -- is it a problem that the ordering is disrupted this way?
1127 l_idx := l_tbl.count + 1;
1128 l_tbl(l_idx).shift_construct_id := l_tbl(n).shift_construct_id;
1129 l_tbl(l_idx).start_time := m.end_except; -- this is for the new entry
1130 l_tbl(l_idx).end_time := l_tbl(n).end_time; -- this is for the new entry
1131 l_tbl(l_idx).availability_type := l_tbl(n).availability_type;
1132 l_tbl(n).end_time := m.start_except; -- This changes the existing entries end_time.
1133
1134 elsif m.start_except < l_tbl(n).start_time
1135 and m.end_except > l_tbl(n).start_time
1136 and m.end_except < l_tbl(n).end_time
1137 then
1138 l_tbl(n).start_time := m.end_except;
1139 --l_tbl(n).end_time := m.start_except;
1140
1141 elsif m.start_except > l_tbl(n).start_time
1142 and m.start_except < l_tbl(n).end_time
1143 and m.end_except > l_tbl(n).end_time
1144 then
1145 l_tbl(n).end_time := m.start_except;
1146 -- added on 28, Sep 2000 start
1147 elsif m.start_except >= l_tbl(n).start_time
1148 and m.start_except < l_tbl(n).end_time
1149 and m.end_except < l_tbl(n).end_time
1150 then
1151 l_tbl(n).start_time := m.end_except;
1152
1153 elsif m.start_except > l_tbl(n).start_time
1154 and m.start_except < l_tbl(n).end_time
1155 and m.end_except <= l_tbl(n).end_time
1156 then
1157 l_tbl(n).end_time := m.start_except;
1158 -- added on 28, Sep 2000 end
1159
1160 elsif m.start_except = l_tbl(n).start_time
1161 and m.end_except = l_tbl(n).end_time
1162 then
1163 l_tbl(n).start_time := null;
1164 l_tbl(n).end_time := null;
1165 l_tbl(n).availability_type := null;
1166 l_tbl.delete;
1167
1168 elsif m.start_except = l_tbl(n).start_time
1169 and m.end_except < l_tbl(n).end_time
1170 then
1171 l_tbl(n).start_time := null;
1172 l_tbl(n).end_time := null;
1173 l_tbl(n).availability_type := null;
1174 l_tbl.delete;
1175
1176 elsif m.start_except < l_tbl(n).start_time -- When exception falls out of the range
1177 and m.end_except >l_tbl(n).end_time
1178 then
1179 l_tbl.delete;
1180 -- added jan10, 2001 start
1181 elsif m.start_except = l_tbl(n).start_time
1182 and m.end_except > l_tbl(n).end_time
1183 then
1184 l_tbl(n).start_time := null;
1185 l_tbl(n).end_time := null;
1186 l_tbl(n).availability_type := null;
1187 l_tbl.delete;
1188
1189 elsif m.start_except < l_tbl(n).start_time
1190 and m.end_except = l_tbl(n).end_time
1191 then
1192 l_tbl(n).start_time := null;
1193 l_tbl(n).end_time := null;
1194 l_tbl(n).availability_type := null;
1195 l_tbl.delete;
1196 -- added jan10, 2001 end
1197 end if;
1198
1199 if l_exp_flg = 'Y' THEN
1200 l_tbl.delete; -- if we find the exception and shift times are same then delete the row from table of records.
1201 end if;
1202 end loop;
1203 end loop;
1204
1205 -- moved this code here so that exceptions are also adjusted for time zone conversion
1206 --
1207 -- Added for Simplex Timezone Enh # 3040681 by ABRAINA
1208 --
1209 If fnd_profile.value_specific('ENABLE_TIMEZONE_CONVERSIONS') = 'Y' Then
1210 l_res_Timezone_id := Get_Res_Timezone_Id (p_resource_id, p_resource_type);
1211 If l_res_Timezone_id <> l_server_tz_id Then
1212 l_idx := 0;
1213 For r in 1..l_tbl.count loop
1214 l_idx := l_idx + 1;
1215 l_tbl(l_idx).start_time := ResourceDt_To_ServerDT(l_tbl(l_idx).start_time,l_res_Timezone_id,l_server_tz_id);
1216 l_tbl(l_idx).end_time := ResourceDt_To_ServerDT(l_tbl(l_idx).end_time,l_res_Timezone_id,l_server_tz_id);
1217 End Loop;
1218 End If;
1219 End If;
1220
1221 -- store found shift constructs for this day in output pl/sql table
1222 for r in 1..l_tbl.count
1223 loop
1224 if ((trunc(l_tbl(r).end_time)) = trunc((l_tbl(r).start_time))) THEN
1225 l_idx := x_shift.count + 1;
1226 x_shift(l_idx).shift_construct_id := l_tbl(r).shift_construct_id;
1227 x_shift(l_idx).shift_date := trunc(l_tbl(r).start_time);
1228 -- reformat the start and end times just at the end of the procedure
1229 -- when the calculations are done
1230 x_shift(l_idx).start_time := to_char(l_tbl(r).start_time,'hh24.mi');
1231 x_shift(l_idx).end_time := to_char(l_tbl(r).end_time,'hh24.mi');
1232 x_shift(l_idx).availability_type := l_tbl(r).availability_type;
1233 else
1234 --added this condition for 24hr shift .. sudarsana 22 Oct 2001
1235 l_st_time := l_tbl(r).start_time;
1236 while(trunc(l_st_time) <= trunc(l_tbl(r).end_time))
1237 loop
1238 l_idx := x_shift.count + 1;
1239 x_shift(l_idx).shift_construct_id := l_tbl(r).shift_construct_id;
1240 x_shift(l_idx).shift_date := trunc(l_st_time);
1241 if(trunc(l_st_time) = trunc(l_tbl(r).start_time))
1242 then
1243 x_shift(l_idx).start_time := to_char(l_tbl(r).start_time,'hh24.mi');
1244 else
1245 x_shift(l_idx).start_time := '00.00';
1246 end if;
1247 if(trunc(l_st_time) = trunc(l_tbl(r).end_time))
1248 then
1249 x_shift(l_idx).end_time := to_char(l_tbl(r).end_time,'hh24.mi');
1250 else
1251 x_shift(l_idx).end_time := '23.59';
1252 end if;
1253 x_shift(l_idx).availability_type := l_tbl(r).availability_type;
1254
1255 l_st_time := l_st_time + 1;
1256 end loop;
1257 end if;
1258 end loop;
1259 exception
1260 when no_data_found then
1261 x_return_status := FND_API.G_RET_STS_ERROR ;
1262 -- Added for bug 3270116
1263 l_return_status := FND_API.G_RET_STS_ERROR ;
1264 -- end
1265 fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS');
1266 fnd_message.set_token('P_CAL_NAME', l_calendar_name);
1267 fnd_msg_pub.add;
1268 FND_MSG_PUB.Count_And_Get
1269 (p_count => x_msg_count,
1270 p_data => x_msg_data
1271 );
1272 end; --2
1273 end if; -- valid cal end if
1274 exception
1275 when no_data_found then
1276 x_return_status := FND_API.G_RET_STS_ERROR ;
1277 -- No Need to set l_return_status to FND_API.G_RET_STS_ERROR
1278 -- as for this exception we want to handle it.
1279 -- For a resource, even if a single shift is returned for a search window
1280 -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1281 l_tbl.delete; -- to delete the record from TOR if no shift found
1282 fnd_message.set_name('JTF','JTF_CAL_RES_NO_CAL');
1283 fnd_message.set_token('P_SHIFT_DATE', l_shift_date);
1284 fnd_message.set_token('P_RES_NAME', l_resource_name);
1285 fnd_msg_pub.add;
1286
1287 FND_MSG_PUB.Count_And_Get
1288 (p_count => x_msg_count,
1289 p_data => x_msg_data
1290 );
1291 end; -- 1
1292 -- end loop; -- valid cal
1293 l_shift_date := l_shift_date + 1;
1294 end loop;
1295
1296 -- see if shift constructs have been found
1297 if x_shift.count = 0
1298 then
1299 x_return_status := fnd_api.g_ret_sts_error ;
1300 -- Added for bug 3270116
1301 l_return_status := FND_API.G_RET_STS_ERROR ;
1302 -- end
1303 fnd_message.set_name('JTF','JTF_CAL_NO_SHIFT_CONSTR_FOUND');
1304 fnd_msg_pub.add;
1305
1306 fnd_msg_pub.count_and_get( p_count => x_msg_count
1307 , p_data => x_msg_data );
1308 end if;
1309
1310 -- Added sort by jawang on 06/27/2002
1311 if x_shift.count > 0
1312 then
1313 -- sort the out table
1314 sort_tab(x_shift);
1315 end if;
1316
1317 -- Added for bug 3270116
1318 -- For a resource, even if a single shift is returned for any search window
1319 -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1320 if l_return_status = FND_API.G_RET_STS_SUCCESS then
1321 x_return_status := FND_API.G_RET_STS_SUCCESS;
1322 else
1323 x_return_status := FND_API.G_RET_STS_ERROR ;
1324 end if;
1325 -- end
1326 EXCEPTION
1327 when g_exc_required_fields_null then
1328 x_return_status := fnd_api.g_ret_sts_error ;
1329 fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
1330 fnd_message.set_token('P_PARAMETER', l_parameters);
1331 fnd_msg_pub.add;
1332 fnd_msg_pub.count_and_get ( p_count => x_msg_count
1333 , p_data => x_msg_data );
1334
1335 when fnd_api.g_exc_unexpected_error then
1336 x_return_status := fnd_api.g_ret_sts_unexp_error;
1337 fnd_msg_pub.count_and_get ( p_count => x_msg_count
1338 , p_data => x_msg_data );
1339 when others then
1340 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1341 fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
1342 fnd_message.set_token('ERROR_CODE',sqlcode);
1343 fnd_message.set_token('ERROR_MESSAGE',sqlerrm);
1344 fnd_msg_pub.count_and_get ( p_count => x_msg_count
1345 , p_data => x_msg_data );
1346 --END;
1347 END get_resource_shifts;
1348
1349
1350 --
1351 -- ********** Get Resource Shifts with 15 attributes ***********
1352 --
1353
1354 PROCEDURE get_resource_shifts
1355 ( p_api_version in number
1356 , p_init_msg_list in varchar2
1357 , p_resource_id in number
1358 , p_resource_type in varchar2
1359 , p_start_date in date
1360 , p_end_date in date
1361 , x_return_status out NOCOPY varchar2
1362 , x_msg_count out NOCOPY number
1363 , x_msg_data out NOCOPY varchar2
1364 , x_shift out NOCOPY shift_tbl_attributes_type
1365 )
1366 IS
1367 type rec_attributes_type is record
1368 ( shift_construct_id number
1369 , start_time date
1370 , end_time date
1371 , availability_type varchar2(40)
1372 , attribute1 varchar2(150)
1373 , attribute2 varchar2(150)
1374 , attribute3 varchar2(150)
1375 , attribute4 varchar2(150)
1376 , attribute5 varchar2(150)
1377 , attribute6 varchar2(150)
1378 , attribute7 varchar2(150)
1379 , attribute8 varchar2(150)
1380 , attribute9 varchar2(150)
1381 , attribute10 varchar2(150)
1382 , attribute11 varchar2(150)
1383 , attribute12 varchar2(150)
1384 , attribute13 varchar2(150)
1385 , attribute14 varchar2(150)
1386 , attribute15 varchar2(150)
1387 );
1388
1389 type tbl_attributes_type is table of rec_attributes_type index by binary_integer;
1390
1391 cursor shift_info(p_calendar_id NUMBER) is
1392 select shift_id,(shift_end_date - shift_start_date) shift_duration
1393 from jtf_cal_shift_assign
1394 where calendar_id = p_calendar_id
1395 order by shift_sequence_number;
1396
1397 cursor c_cal_shift_constr(p_shift_id NUMBER, p_day date, p_uot_value NUMBER) is
1398 select shift_construct_id,
1399 begin_time start_constr,
1400 end_time end_constr,
1401 availability_type_code,
1402 attribute1,
1403 attribute2,
1404 attribute3,
1405 attribute4,
1406 attribute5,
1407 attribute6,
1408 attribute7,
1409 attribute8,
1410 attribute9,
1411 attribute10,
1412 attribute11,
1413 attribute12,
1414 attribute13,
1415 attribute14,
1416 attribute15
1417 from jtf_cal_shift_constructs
1418 where shift_id = p_shift_id
1419 --
1420 --Modified condition to take care first Day of week for Bug 1342982
1421 --
1422 and to_char(begin_time, 'd') = to_char(p_day, 'd')
1423 -- added by jawang on 06/07/2002 to fix bug 2393255
1424 and ( ( p_day between start_date_active
1425 and end_date_active)
1426 or (start_date_active <=p_day
1427 and end_date_active IS NULL));
1428
1429 cursor c_cal_except
1430 ( p_calendar_id number
1431 , p_day date )
1432 is
1433 select e.start_date_time start_except
1434 , e.end_date_time end_except
1435 from jtf_cal_exception_assign a
1436 , jtf_cal_exceptions_vl e
1437 where a.calendar_id = p_calendar_id
1438 and a.exception_id = e.exception_id
1439 -- validate exception assignment
1440 and ( p_day >= trunc(a.start_date_active)
1441 or a.start_date_active is null)
1442 and ( p_day <= trunc(a.end_date_active)
1443 or a.end_date_active is null)
1444 -- validate exception
1445 and p_day between trunc(e.start_date_time)
1446 and trunc(e.end_date_time);
1447
1448 cursor valid_cal
1449 (p_resource_id number,
1450 p_resource_type varchar2,p_shift_date DATE) IS
1451 -- added p_shift_date bug 1355824
1452 select start_date_time
1453 from jtf_cal_resource_assign
1454 where resource_id = p_resource_id
1455 and resource_type_code = p_resource_type
1456 and primary_calendar_flag = 'Y'
1457 and (( p_shift_date >= trunc(start_date_time) and end_date_time IS NULL )
1458 OR (p_shift_date between trunc(start_date_time)
1459 and trunc(end_date_time)));
1460
1461 l_api_name constant varchar2(30) := 'Get_Resource_Shifts';
1462 l_api_version constant number := 1.0;
1463 l_parameters varchar2(2000) := null;
1464 g_exc_required_fields_null exception;
1465 l_range_start date;
1466 l_range_end date;
1467 l_day date;
1468 l_utv number;
1469 l_idx number := 0;
1470 l_tbl tbl_attributes_type;
1471 l_cnt number;
1472 l_shifts_total_duration number;
1473 l_shift_date date;
1474 l_left_days number;
1475 l_calendar_id number;
1476 l_shift_id number;
1477
1478 l_calendar_name jtf_calendars_vl.calendar_name%TYPE; -- bug # 2493461 varchar2(100)
1479 l_calendar_start_date date;
1480 l_exp_flg varchar2(1) := 'N';
1481 l_start_date_time date;
1482 l_st_time date;
1483 l_server_tz_id Number := to_number (fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
1484 l_res_Timezone_id Number;
1485
1486 l_return_status varchar2(1) := FND_API.G_RET_STS_ERROR ;
1487
1488 l_resource_name jtf_task_resources_vl.resource_name%TYPE;-- bug # 2418561
1489
1490 BEGIN
1491
1492 -- standard call to check for call compatibility.
1493 if not fnd_api.compatible_api_call
1494 ( l_api_version
1495 , p_api_version
1496 , l_api_name
1497 , JTF_CALENDAR_PUB.g_pkg_name )
1498 then
1499 raise fnd_api.g_exc_unexpected_error;
1500 end if;
1501
1502 -- initialize message list if p_init_msg_list is set to true.
1503 if fnd_api.to_boolean ( p_init_msg_list )
1504 then
1505 fnd_msg_pub.initialize;
1506 end if;
1507
1508 -- call to check for required fields
1509 if not JTF_CALENDAR_PUB.check_for_required_fields
1510 ( p_resource_id => p_resource_id
1511 , p_resource_type => p_resource_type
1512 , p_start_date => p_start_date
1513 , p_end_date => p_start_date )
1514 then
1515 l_parameters := 'p_resource_id, p_resource_type, p_start_date, p_end_date';
1516 raise g_exc_required_fields_null;
1517 end if;
1518
1519 -- initialize api return status to success
1520 x_return_status := fnd_api.g_ret_sts_success;
1521
1522 -- bug# 1344222
1523 -- Comment out by jawang on 06/17/2002
1524
1525 -- This code is added to get resource name to be printed in error message.
1526 -- Added for Bug 4063687
1527 BEGIN
1528 select resource_name
1529 into l_resource_name
1530 --from jtf_rs_all_resources_vl
1531 --Modified by jawang to fix the bug 2416932
1532 from jtf_task_resources_vl
1533 where resource_id = p_resource_id
1534 and resource_type = p_resource_type;
1535 EXCEPTION
1536 WHEN Others THEN
1537 NULL;
1538 END;
1539
1540 -- get all valid resource-to-calendar assignments for this resource in
1541 -- this period ordered by start date
1542 -- because there is a primary flag, only one record is expected
1543
1544 l_shift_date := trunc(p_start_date);
1545
1546 while l_shift_date <= p_end_date Loop
1547 -- We first check if there is a valid primary calendar on this date.
1548 -- for v in valid_cal(p_resource_id,p_resource_type,l_shift_date) loop
1549 -- get the primary calendar for a resource on the given date
1550 --
1551 begin --(1)
1552 select a.calendar_id,b.calendar_name,b.start_date_active,a.start_date_time
1553 into l_calendar_id,l_calendar_name,l_calendar_start_date,l_start_date_time
1554 from jtf_cal_resource_assign a,
1555 jtf_calendars_vl b
1556 where a.resource_id = p_resource_id
1557 and a.resource_type_code = p_resource_type
1558 and a.calendar_id = b.calendar_id
1559 and a.primary_calendar_flag = 'Y'
1560 -- Commented for bug 3891896 by ABRAINA
1561 -- and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),to_date(get_g_miss_date,'DD/MM/RRRR'));
1562 and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),l_shift_date);
1563
1564 -- Added for bug 3270116
1565 l_return_status := FND_API.G_RET_STS_SUCCESS;
1566
1567 --added l_shift_date in valid_cal loop bug #1355824
1568 -- if condition added for bug 3270116 by ABRAINA
1569 IF Validate_Cal_Date(l_calendar_id, l_shift_date)
1570 THEN
1571 -- This check is not necessary. Can be removed after testing.
1572 begin --(2)
1573 select sum(shift_end_date - shift_start_date)
1574 into l_shifts_total_duration
1575 from jtf_cal_shift_assign
1576 where calendar_id = l_calendar_id;
1577
1578 l_left_days := mod((l_shift_date - l_calendar_start_date),l_shifts_total_duration);
1579
1580 l_shift_id := null;
1581 for c in shift_info(l_calendar_id) loop
1582 l_left_days := l_left_days - c.shift_duration;
1583 IF l_left_days < 0 THEN
1584 l_shift_id := c.shift_id;
1585 EXIT;
1586 END IF;
1587 end loop;
1588 -- Added by Sarvi
1589
1590 -- calculate unit of time value
1591 -- this is dependant on nls setting
1592 l_utv := to_char(l_shift_date,'d');
1593 l_tbl.delete;
1594 l_idx := 0;
1595 for j in c_cal_shift_constr ( l_shift_id
1596 , l_shift_date
1597 , l_utv )
1598 loop
1599 l_idx := l_idx + 1;
1600 l_tbl(l_idx).shift_construct_id := j.shift_construct_id;
1601 -- The date part of the shift construct start is just a container
1602 -- without real meaning. In order to process the multi-day
1603 -- exceptions more easily, the requested day is added to it, so
1604 -- that the resulting datetime has a real meaning.
1605 -- added this chg for the 24 hr shift split .. sudarsana 23 oct 2001
1606 IF trunc(j.start_constr) = trunc(j.end_constr) THEN
1607 l_tbl(l_idx).start_time := l_shift_date + (j.start_constr - trunc(j.start_constr));
1608 l_tbl(l_idx).end_time := l_shift_date + (j.end_constr - trunc(j.end_constr));
1609 else
1610 l_tbl(l_idx).start_time := l_shift_date + (j.start_constr - trunc(j.start_constr));
1611 l_tbl(l_idx).end_time := l_shift_date + (j.end_constr - trunc(j.end_constr)) +
1612 (trunc(j.end_constr) - trunc(j.start_constr));
1613 end if;
1614 l_tbl(l_idx).availability_type := j.availability_type_code;
1615
1616 -- Added by Jane on 03/05/2002
1617 l_tbl(l_idx).attribute1 := j.attribute1;
1618 l_tbl(l_idx).attribute2 := j.attribute2;
1619 l_tbl(l_idx).attribute3 := j.attribute3;
1620 l_tbl(l_idx).attribute4 := j.attribute4;
1621 l_tbl(l_idx).attribute5 := j.attribute5;
1622 l_tbl(l_idx).attribute6 := j.attribute6;
1623 l_tbl(l_idx).attribute7 := j.attribute7;
1624 l_tbl(l_idx).attribute8 := j.attribute8;
1625 l_tbl(l_idx).attribute9 := j.attribute9;
1626 l_tbl(l_idx).attribute10 := j.attribute10;
1627 l_tbl(l_idx).attribute11 := j.attribute11;
1628 l_tbl(l_idx).attribute12 := j.attribute12;
1629 l_tbl(l_idx).attribute13 := j.attribute13;
1630 l_tbl(l_idx).attribute14 := j.attribute14;
1631 l_tbl(l_idx).attribute15 := j.attribute15;
1632
1633 end loop;
1634
1635
1636
1637 -- deduct all exceptions from working hours on the requested day
1638 -- exceptions can consist of more than one day
1639 for m in c_cal_except ( l_calendar_id
1640 , l_shift_date )
1641 loop -- loop the exception cursor
1642 l_cnt := l_tbl.count;
1643
1644 for n in 1..l_cnt
1645 loop -- loop thru the table loaded with shifts.
1646
1647 -- If we find an exception satisfying this condition then, we have
1648 -- to change the start/end time of the shifts accordingly. Like shift 8 - 16
1649 -- and exception 10-11, then we need to split the record into two like
1650 -- 8 - 10 and 11 - 16, showing the resource availablity.
1651 if m.start_except > l_tbl(n).start_time
1652 and m.start_except < l_tbl(n).end_time
1653 and m.end_except > l_tbl(n).start_time
1654 and m.end_except < l_tbl(n).end_time
1655 then
1656 -- an extra entry is created at the end of the pl/sql table
1657 -- is it a problem that the ordering is disrupted this way?
1658 l_idx := l_tbl.count + 1;
1659 l_tbl(l_idx).shift_construct_id := l_tbl(n).shift_construct_id;
1660 l_tbl(l_idx).start_time := m.end_except; -- this is for the new entry
1661 l_tbl(l_idx).end_time := l_tbl(n).end_time; -- this is for the new entry
1662 l_tbl(l_idx).availability_type := l_tbl(n).availability_type;
1663
1664 -- Added by Jane on 03/05/2002
1665 l_tbl(l_idx).attribute1 := l_tbl(n).attribute1;
1666 l_tbl(l_idx).attribute2 := l_tbl(n).attribute2;
1667 l_tbl(l_idx).attribute3 := l_tbl(n).attribute3;
1668 l_tbl(l_idx).attribute4 := l_tbl(n).attribute4;
1669 l_tbl(l_idx).attribute5 := l_tbl(n).attribute5;
1670 l_tbl(l_idx).attribute6 := l_tbl(n).attribute6;
1671 l_tbl(l_idx).attribute7 := l_tbl(n).attribute7;
1672 l_tbl(l_idx).attribute8 := l_tbl(n).attribute8;
1673 l_tbl(l_idx).attribute9 := l_tbl(n).attribute9;
1674 l_tbl(l_idx).attribute10 := l_tbl(n).attribute10;
1675 l_tbl(l_idx).attribute11 := l_tbl(n).attribute11;
1676 l_tbl(l_idx).attribute12 := l_tbl(n).attribute12;
1677 l_tbl(l_idx).attribute13 := l_tbl(n).attribute13;
1678 l_tbl(l_idx).attribute14 := l_tbl(n).attribute14;
1679 l_tbl(l_idx).attribute15 := l_tbl(n).attribute15;
1680
1681
1682 l_tbl(n).end_time := m.start_except; -- This changes the existing entries end_time.
1683
1684 elsif m.start_except < l_tbl(n).start_time
1685 and m.end_except > l_tbl(n).start_time
1686 and m.end_except < l_tbl(n).end_time
1687 then
1688 l_tbl(n).start_time := m.end_except;
1689 --l_tbl(n).end_time := m.start_except;
1690
1691 elsif m.start_except > l_tbl(n).start_time
1692 and m.start_except < l_tbl(n).end_time
1693 and m.end_except > l_tbl(n).end_time
1694 then
1695 l_tbl(n).end_time := m.start_except;
1696 -- added on 28, Sep 2000 start
1697 elsif m.start_except >= l_tbl(n).start_time
1698 and m.start_except < l_tbl(n).end_time
1699 and m.end_except < l_tbl(n).end_time
1700 then
1701 l_tbl(n).start_time := m.end_except;
1702
1703 elsif m.start_except > l_tbl(n).start_time
1704 and m.start_except < l_tbl(n).end_time
1705 and m.end_except <= l_tbl(n).end_time
1706 then
1707 l_tbl(n).end_time := m.start_except;
1708 -- added on 28, Sep 2000 end
1709
1710 elsif m.start_except = l_tbl(n).start_time
1711 and m.end_except = l_tbl(n).end_time
1712 then
1713 l_tbl(n).start_time := null;
1714 l_tbl(n).end_time := null;
1715 l_tbl(n).availability_type := null;
1716
1717 -- Added by Jane on 03/05/2002
1718 l_tbl(n).attribute1 := null;
1719 l_tbl(n).attribute2 := null;
1720 l_tbl(n).attribute3 := null;
1721 l_tbl(n).attribute4 := null;
1722 l_tbl(n).attribute5 := null;
1723 l_tbl(n).attribute6 := null;
1724 l_tbl(n).attribute7 := null;
1725 l_tbl(n).attribute8 := null;
1726 l_tbl(n).attribute9 := null;
1727 l_tbl(n).attribute10 := null;
1728 l_tbl(n).attribute11 := null;
1729 l_tbl(n).attribute12 := null;
1730 l_tbl(n).attribute13 := null;
1731 l_tbl(n).attribute14 := null;
1732 l_tbl(n).attribute15 := null;
1733
1734
1735 l_tbl.delete;
1736
1737 elsif m.start_except = l_tbl(n).start_time
1738 and m.end_except < l_tbl(n).end_time
1739 then
1740 l_tbl(n).start_time := null;
1741 l_tbl(n).end_time := null;
1742 l_tbl(n).availability_type := null;
1743
1744 -- Added by Jane on 03/05/2002
1745 l_tbl(n).attribute1 := null;
1746 l_tbl(n).attribute2 := null;
1747 l_tbl(n).attribute3 := null;
1748 l_tbl(n).attribute4 := null;
1749 l_tbl(n).attribute5 := null;
1750 l_tbl(n).attribute6 := null;
1751 l_tbl(n).attribute7 := null;
1752 l_tbl(n).attribute8 := null;
1753 l_tbl(n).attribute9 := null;
1754 l_tbl(n).attribute10 := null;
1755 l_tbl(n).attribute11 := null;
1756 l_tbl(n).attribute12 := null;
1757 l_tbl(n).attribute13 := null;
1758 l_tbl(n).attribute14 := null;
1759 l_tbl(n).attribute15 := null;
1760
1761
1762 l_tbl.delete;
1763
1764 elsif m.start_except < l_tbl(n).start_time -- When exception falls out of the range
1765 and m.end_except >l_tbl(n).end_time
1766 then
1767 l_tbl.delete;
1768 -- added jan10, 2001 start
1769 elsif m.start_except = l_tbl(n).start_time
1770 and m.end_except > l_tbl(n).end_time
1771 then
1772 l_tbl(n).start_time := null;
1773 l_tbl(n).end_time := null;
1774 l_tbl(n).availability_type := null;
1775
1776 -- Added by Jane on 03/05/2002
1777 l_tbl(n).attribute1 := null;
1778 l_tbl(n).attribute2 := null;
1779 l_tbl(n).attribute3 := null;
1780 l_tbl(n).attribute4 := null;
1781 l_tbl(n).attribute5 := null;
1782 l_tbl(n).attribute6 := null;
1783 l_tbl(n).attribute7 := null;
1784 l_tbl(n).attribute8 := null;
1785 l_tbl(n).attribute9 := null;
1786 l_tbl(n).attribute10 := null;
1787 l_tbl(n).attribute11 := null;
1788 l_tbl(n).attribute12 := null;
1789 l_tbl(n).attribute13 := null;
1790 l_tbl(n).attribute14 := null;
1791 l_tbl(n).attribute15 := null;
1792
1793 l_tbl.delete;
1794
1795 elsif m.start_except < l_tbl(n).start_time
1796 and m.end_except = l_tbl(n).end_time
1797 then
1798 l_tbl(n).start_time := null;
1799 l_tbl(n).end_time := null;
1800 l_tbl(n).availability_type := null;
1801
1802 -- Added by Jane on 03/05/2002
1803 l_tbl(n).attribute1 := null;
1804 l_tbl(n).attribute2 := null;
1805 l_tbl(n).attribute3 := null;
1806 l_tbl(n).attribute4 := null;
1807 l_tbl(n).attribute5 := null;
1808 l_tbl(n).attribute6 := null;
1809 l_tbl(n).attribute7 := null;
1810 l_tbl(n).attribute8 := null;
1811 l_tbl(n).attribute9 := null;
1812 l_tbl(n).attribute10 := null;
1813 l_tbl(n).attribute11 := null;
1814 l_tbl(n).attribute12 := null;
1815 l_tbl(n).attribute13 := null;
1816 l_tbl(n).attribute14 := null;
1817 l_tbl(n).attribute15 := null;
1818
1819 l_tbl.delete;
1820 -- added jan10, 2001 end
1821 end if;
1822
1823 if l_exp_flg = 'Y' THEN
1824 l_tbl.delete; -- if we find the exception and shift times are same then delete the row from table of records.
1825 end if;
1826 end loop; -- end loop thru the table loaded with shifts.
1827 end loop; -- end loop the exception cursor
1828
1829 -- Moved this code here so that exceptions are also considered for timezone conversion
1830 --
1831 -- Added for Simplex Timezone Enh # 3040681 by ABRAINA
1832 --
1833 If fnd_profile.value_specific('ENABLE_TIMEZONE_CONVERSIONS') = 'Y' Then
1834 l_res_Timezone_id := Get_Res_Timezone_Id (p_resource_id, p_resource_type);
1835 If l_res_Timezone_id <> l_server_tz_id Then
1836 l_idx := 0;
1837 For r in 1..l_tbl.count loop
1838 l_idx := l_idx + 1;
1839 l_tbl(l_idx).start_time := ResourceDt_To_ServerDT(l_tbl(l_idx).start_time,l_res_Timezone_id,l_server_tz_id);
1840 l_tbl(l_idx).end_time := ResourceDt_To_ServerDT(l_tbl(l_idx).end_time,l_res_Timezone_id,l_server_tz_id);
1841 End Loop;
1842 End If;
1843 End If;
1844
1845
1846 -- store found shift constructs for this day in output pl/sql table
1847 for r in 1..l_tbl.count
1848 loop
1849 if ((trunc(l_tbl(r).end_time)) = trunc((l_tbl(r).start_time))) THEN
1850 l_idx := x_shift.count + 1;
1851 x_shift(l_idx).shift_construct_id := l_tbl(r).shift_construct_id;
1852 x_shift(l_idx).shift_date := trunc(l_tbl(r).start_time);
1853 -- reformat the start and end times just at the end of the procedure
1854 -- when the calculations are done
1855 x_shift(l_idx).start_time := to_char(l_tbl(r).start_time,'hh24.mi');
1856 x_shift(l_idx).end_time := to_char(l_tbl(r).end_time,'hh24.mi');
1857 x_shift(l_idx).availability_type := l_tbl(r).availability_type;
1858
1859 -- Added by Jane on 03/05/2002
1860 x_shift(l_idx).attribute1 := l_tbl(r).attribute1;
1861 x_shift(l_idx).attribute2 := l_tbl(r).attribute2;
1862 x_shift(l_idx).attribute3 := l_tbl(r).attribute3;
1863 x_shift(l_idx).attribute4 := l_tbl(r).attribute4;
1864 x_shift(l_idx).attribute5 := l_tbl(r).attribute5;
1865 x_shift(l_idx).attribute6 := l_tbl(r).attribute6;
1866 x_shift(l_idx).attribute7 := l_tbl(r).attribute7;
1867 x_shift(l_idx).attribute8 := l_tbl(r).attribute8;
1868 x_shift(l_idx).attribute9 := l_tbl(r).attribute9;
1869 x_shift(l_idx).attribute10 := l_tbl(r).attribute10;
1870 x_shift(l_idx).attribute11 := l_tbl(r).attribute11;
1871 x_shift(l_idx).attribute12 := l_tbl(r).attribute12;
1872 x_shift(l_idx).attribute13 := l_tbl(r).attribute13;
1873 x_shift(l_idx).attribute14 := l_tbl(r).attribute14;
1874 x_shift(l_idx).attribute15 := l_tbl(r).attribute15;
1875
1876 else
1877 --added this condition for 24hr shift .. sudarsana 22 Oct 2001
1878 l_st_time := l_tbl(r).start_time;
1879 while(trunc(l_st_time) <= trunc(l_tbl(r).end_time))
1880 loop
1881 l_idx := x_shift.count + 1;
1882 x_shift(l_idx).shift_construct_id := l_tbl(r).shift_construct_id;
1883 x_shift(l_idx).shift_date := trunc(l_st_time);
1884 if(trunc(l_st_time) = trunc(l_tbl(r).start_time))
1885 then
1886 x_shift(l_idx).start_time := to_char(l_tbl(r).start_time,'hh24.mi');
1887 else
1888 x_shift(l_idx).start_time := '00.00';
1889 end if;
1890 if(trunc(l_st_time) = trunc(l_tbl(r).end_time))
1891 then
1892 x_shift(l_idx).end_time := to_char(l_tbl(r).end_time,'hh24.mi');
1893 else
1894 x_shift(l_idx).end_time := '23.59';
1895 end if;
1896 x_shift(l_idx).availability_type := l_tbl(r).availability_type;
1897
1898 -- Added by Jane on 03/05/2002
1899 x_shift(l_idx).attribute1 := l_tbl(r).attribute1;
1900 x_shift(l_idx).attribute2 := l_tbl(r).attribute2;
1901 x_shift(l_idx).attribute3 := l_tbl(r).attribute3;
1902 x_shift(l_idx).attribute4 := l_tbl(r).attribute4;
1903 x_shift(l_idx).attribute5 := l_tbl(r).attribute5;
1904 x_shift(l_idx).attribute6 := l_tbl(r).attribute6;
1905 x_shift(l_idx).attribute7 := l_tbl(r).attribute7;
1906 x_shift(l_idx).attribute8 := l_tbl(r).attribute8;
1907 x_shift(l_idx).attribute9 := l_tbl(r).attribute9;
1908 x_shift(l_idx).attribute10 := l_tbl(r).attribute10;
1909 x_shift(l_idx).attribute11 := l_tbl(r).attribute11;
1910 x_shift(l_idx).attribute12 := l_tbl(r).attribute12;
1911 x_shift(l_idx).attribute13 := l_tbl(r).attribute13;
1912 x_shift(l_idx).attribute14 := l_tbl(r).attribute14;
1913 x_shift(l_idx).attribute15 := l_tbl(r).attribute15;
1914
1915
1916 l_st_time := l_st_time + 1;
1917 end loop;
1918 end if;
1919 end loop;
1920 exception
1921 when no_data_found then
1922 x_return_status := FND_API.G_RET_STS_ERROR ;
1923 -- Added for bug 3270116
1924 l_return_status := FND_API.G_RET_STS_ERROR ;
1925 -- end
1926 fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS');
1927 fnd_message.set_token('P_CAL_NAME', l_calendar_name);
1928 fnd_msg_pub.add;
1929 FND_MSG_PUB.Count_And_Get
1930 (p_count => x_msg_count,
1931 p_data => x_msg_data
1932 );
1933 end; --2
1934 end if; -- valid cal end if
1935 exception
1936 when no_data_found then
1937 x_return_status := FND_API.G_RET_STS_ERROR ;
1938 -- No Need to set l_return_status to FND_API.G_RET_STS_ERROR
1939 -- as for this exception we want to handle it.
1940 -- For a resource, even if a single shift is returned for a search window
1941 -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1942 l_tbl.delete; -- to delete the record from TOR if no shift found
1943 fnd_message.set_name('JTF','JTF_CAL_RES_NO_CAL');
1944 fnd_message.set_token('P_SHIFT_DATE', l_shift_date);
1945 fnd_message.set_token('P_RES_NAME', l_resource_name);
1946 fnd_msg_pub.add;
1947
1948 FND_MSG_PUB.Count_And_Get
1949 (p_count => x_msg_count,
1950 p_data => x_msg_data
1951 );
1952 end; -- 1
1953 -- end loop; -- valid cal
1954 l_shift_date := l_shift_date + 1;
1955 end loop;
1956
1957
1958 -- see if shift constructs have been found
1959 if x_shift.count = 0
1960 then
1961 x_return_status := fnd_api.g_ret_sts_error ;
1962 -- Added for bug 3270116
1963 l_return_status := FND_API.G_RET_STS_ERROR ;
1964 -- end
1965 fnd_message.set_name('JTF','JTF_CAL_NO_SHIFT_CONSTR_FOUND');
1966 fnd_msg_pub.add;
1967
1968 fnd_msg_pub.count_and_get( p_count => x_msg_count
1969 , p_data => x_msg_data );
1970 end if;
1971
1972 -- Added sort by jawang on 06/27/2002
1973 if x_shift.count > 0
1974 then
1975 -- sort the out table
1976 sort_tab_attr(x_shift);
1977 end if;
1978
1979 -- Added for bug 3270116
1980 -- For a resource, even if a single shift is returned for any search window
1981 -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1982 if l_return_status = FND_API.G_RET_STS_SUCCESS then
1983 x_return_status := FND_API.G_RET_STS_SUCCESS;
1984 else
1985 x_return_status := FND_API.G_RET_STS_ERROR ;
1986 end if;
1987 -- end
1988
1989 EXCEPTION
1990 when g_exc_required_fields_null then
1991 x_return_status := fnd_api.g_ret_sts_error ;
1992 fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
1993 fnd_message.set_token('P_PARAMETER', l_parameters);
1994 fnd_msg_pub.add;
1995 fnd_msg_pub.count_and_get ( p_count => x_msg_count
1996 , p_data => x_msg_data );
1997
1998 when fnd_api.g_exc_unexpected_error then
1999 x_return_status := fnd_api.g_ret_sts_unexp_error;
2000 fnd_msg_pub.count_and_get ( p_count => x_msg_count
2001 , p_data => x_msg_data );
2002 when others then
2003 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2004 fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
2005 fnd_message.set_token('ERROR_CODE',sqlcode);
2006 fnd_message.set_token('ERROR_MESSAGE',sqlerrm);
2007 fnd_msg_pub.count_and_get ( p_count => x_msg_count
2008 , p_data => x_msg_data );
2009 --END;
2010 END get_resource_shifts;
2011
2012 --
2013 -- ********** Get Resource Schedule **************
2014 --
2015 PROCEDURE Get_Res_Schedule
2016 ( p_api_version IN NUMBER ,
2017 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2018 p_resource_id IN NUMBER ,
2019 p_resource_type IN VARCHAR2 ,
2020 p_start_date IN DATE ,
2021 p_end_date IN DATE ,
2022 x_return_status OUT NOCOPY VARCHAR2 ,
2023 x_msg_count OUT NOCOPY NUMBER ,
2024 x_msg_data OUT NOCOPY VARCHAR2 ,
2025 x_shift OUT NOCOPY SHIFT_TBL_TYPE
2026 )
2027 IS
2028 l_api_name CONSTANT VARCHAR2(30):= 'Get_Rsc_Schedule';
2029 l_api_version CONSTANT NUMBER := 1.0;
2030 --
2031 cursor shift_info(p_calendar_id NUMBER) is
2032 select shift_id,(shift_end_date - shift_start_date) shift_duration
2033 from jtf_cal_shift_assign
2034 where calendar_id = p_calendar_id
2035 order by shift_sequence_number;
2036 --
2037 cursor work_hrs(p_shift_id NUMBER, p_day date, p_uot_value NUMBER) is
2038 select shift_construct_id,
2039 begin_time shift_begin_time,
2040 end_time shift_end_time,
2041 availability_type_code
2042 from jtf_cal_shift_constructs
2043 where shift_id = p_shift_id
2044 --
2045 --Modified condition to take care first Day of week for Bug 1342982
2046 --
2047 and to_char(begin_time, 'd') = to_char(p_day, 'd')
2048 -- added by jawang on 06/07/2002 to fix by 2393255
2049 and ( ( p_day between start_date_active
2050 and end_date_active)
2051 or (start_date_active <=p_day
2052 and end_date_active IS NULL));
2053
2054 --
2055 cursor excp_hrs(p_calendar_id NUMBER, p_req_date DATE) is
2056 select to_char(a.start_date_time,'HH24.MI') excp_start_time,
2057 to_char(a.end_date_time,'HH24.MI') excp_end_time
2058 from jtf_cal_exceptions_vl a, jtf_cal_exception_assign b
2059 where trunc(a.start_date_time) = p_req_date
2060 and a.exception_id = b.exception_id
2061 and b.calendar_id = p_calendar_id;
2062 --
2063
2064 cursor task_hrs(p_res_id NUMBER,p_res_type VARCHAR2,p_req_date DATE,p_tz_enabled VARCHAR2,p_server_tz_id NUMBER) is
2065 -- we are picking up the schedule time from task.
2066
2067 --
2068 -- Removed "distinct" clause added ealier for bug 2817811 by A.Raina
2069 --
2070 -- Added two new parameters p_tz_enabled, p_server_tz_id and
2071 -- modified the query accordingly. Done by SBARAT on 23/06/2005 for Bug# 4443443
2072 --
2073 -- For bug 4547539, added db index skip hint to force db to use second indexed
2074 -- column schedule_end_date for index search
2075 select /*+ index_ss(T JTF_TASKS_B_N12) */
2076 trunc(Decode(p_tz_enabled,'Y',
2077 Decode(t.timezone_id,NULL, t.scheduled_start_date,
2078 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
2079 p_server_tz_id,
2080 t.scheduled_start_date),
2081 t.scheduled_start_date)
2082 ),
2083 t.scheduled_start_date)
2084 ) task_start_date,
2085 trunc(Decode(p_tz_enabled,'Y',
2086 Decode(t.timezone_id,NULL, t.scheduled_end_date,
2087 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
2088 p_server_tz_id,
2089 t.scheduled_end_date),
2090 t.scheduled_end_date)
2091 ),
2092 t.scheduled_end_date)
2093 ) task_end_date,
2094 to_char(Decode(p_tz_enabled,'Y',
2095 Decode(t.timezone_id,NULL, t.scheduled_start_date,
2096 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
2097 p_server_tz_id,
2098 t.scheduled_start_date),
2099 t.scheduled_start_date)
2100 ),
2101 t.scheduled_start_date), 'HH24.MI'
2102 ) task_start_time,
2103 to_char(Decode(p_tz_enabled,'Y',
2104 Decode(t.timezone_id,NULL, t.scheduled_end_date,
2105 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
2106 p_server_tz_id,
2107 t.scheduled_end_date),
2108 t.scheduled_end_date)
2109 ),
2110 t.scheduled_end_date), 'HH24.MI'
2111 ) task_end_time
2112 from jtf_tasks_b t,
2113 jtf_task_assignments a,
2114 jtf_task_statuses_b s
2115 where a.resource_id = p_res_id
2116 and a.resource_type_code = p_res_type
2117 and p_req_date between
2118 trunc(Decode(p_tz_enabled,'Y',
2119 Decode(t.timezone_id,NULL, t.scheduled_start_date,
2120 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
2121 p_server_tz_id,
2122 t.scheduled_start_date),
2123 t.scheduled_start_date)
2124 ),
2125 t.scheduled_start_date)
2126 )
2127 and
2128 Decode(p_tz_enabled,'Y',
2129 Decode(t.timezone_id,NULL, t.scheduled_end_date,
2130 Nvl(HZ_TIMEZONE_PUB.CONVERT_DATETIME(t.timezone_id,
2131 p_server_tz_id,
2132 t.scheduled_end_date),
2133 t.scheduled_end_date)
2134 ),
2135 t.scheduled_end_date)
2136 and s.task_status_id = a.assignment_status_id
2137 AND t.task_id = a.task_id
2138 and nvl(s.cancelled_flag,'N') <> 'Y'
2139 and nvl(s.completed_flag,'N') <> 'Y'
2140 and t.scheduled_start_date <> t.scheduled_end_date ; -- bug # 2520762
2141 --
2142
2143 j INTEGER := 0;
2144 l_shift_id NUMBER;
2145 l_unit_of_time_value NUMBER;
2146 l_calendar_id NUMBER;
2147 l_calendar_name jtf_calendars_vl.calendar_name%TYPE; -- bug # 2493461 VARCHAR2(240)
2148 l_calendar_start_date DATE;
2149 l_shifts_total_duration NUMBER;
2150 l_left_days NUMBER;
2151 l_shift_date DATE;
2152 l_res_type VARCHAR2(30);
2153 l_st_date DATE;
2154 l_ed_time DATE;
2155 l_st_time DATE;
2156 l_shift_b_time varchar2(25);
2157 l_shift_b_date date;
2158 l_shift_e_time varchar2(25);
2159 l_shift_e_date date;
2160 l_tz_enabled VARCHAR2(10):=fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS'); -- Added by SBARAT on 23/06/2005 for Bug# 4443443
2161 l_server_tz_id Number := to_number (fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
2162 l_res_Timezone_id Number;
2163
2164 l_resource_name jtf_task_resources_vl.resource_name%TYPE;-- bug # 2418561
2165
2166 BEGIN
2167 -- Standard call to check for call compatibility.
2168 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2169 p_api_version ,
2170 l_api_name ,
2171 G_PKG_NAME )
2172 THEN
2173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2174 END IF;
2175 -- Initialize message list if p_init_msg_list is set to TRUE.
2176 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2177 FND_MSG_PUB.initialize;
2178 END IF;
2179 -- Call to check for required fields
2180 IF not check_for_required_fields (p_resource_id =>p_resource_id,
2181 p_resource_type =>p_resource_type,
2182 p_start_date =>p_start_date,
2183 p_end_date =>p_end_date)
2184 THEN
2185 l_parameters := 'p_resource_id, p_resource_type, p_start_date, p_end_date';
2186 RAISE G_EXC_REQUIRED_FIELDS_NULL;
2187 END IF;
2188 -- Initialize API return status to success
2189 x_return_status := FND_API.G_RET_STS_SUCCESS;
2190
2191 -- This code is added to get resource name to be printed in error message.
2192 -- Added for Bug 4063687
2193 BEGIN
2194 select resource_name
2195 into l_resource_name
2196 --from jtf_rs_all_resources_vl
2197 --Modified by jawang to fix the bug 2416932
2198 from jtf_task_resources_vl
2199 where resource_id = p_resource_id
2200 and resource_type = p_resource_type;
2201 EXCEPTION
2202 WHEN Others THEN
2203 NULL;
2204 END;
2205
2206 --
2207 l_shift_date := trunc(p_start_date);
2208 x_shift.delete;
2209 While l_shift_date <= p_end_date Loop
2210 -- get the primary calendar for a resource on the given date
2211 --
2212 begin --(1)
2213 select a.calendar_id,b.calendar_name,b.start_date_active
2214 into l_calendar_id,l_calendar_name,l_calendar_start_date
2215 from jtf_cal_resource_assign a,
2216 jtf_calendars_vl b
2217 where a.resource_id = p_resource_id
2218 and a.resource_type_code = p_resource_type
2219 and a.calendar_id = b.calendar_id
2220 and a.primary_calendar_flag = 'Y'
2221 -- Commented for bug 3891896 by ABRAINA
2222 -- and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),to_date(get_g_miss_date,'DD/MM/RRRR'));
2223 and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),l_shift_date);
2224
2225 -- if condition added for bug 3270116 by ABRAINA
2226 IF Validate_Cal_Date(l_calendar_id, l_shift_date) THEN -- (1)
2227 --
2228 -- get the shift in which the given date falls for the above calendar
2229 --
2230 begin --(2)
2231 select sum(shift_end_date - shift_start_date)
2232 into l_shifts_total_duration
2233 from jtf_cal_shift_assign
2234 where calendar_id = l_calendar_id;
2235
2236 select mod((l_shift_date - l_calendar_start_date),l_shifts_total_duration)
2237 into l_left_days
2238 from dual;
2239
2240 l_shift_id := null;
2241 for c in shift_info(l_calendar_id) loop
2242 l_left_days := l_left_days - c.shift_duration;
2243 IF l_left_days < 0 THEN
2244 l_shift_id := c.shift_id;
2245 EXIT;
2246 END IF;
2247 end loop;
2248 --
2249 -- Find the day of the Requested Date
2250 --
2251 select to_char(l_shift_date, 'd')
2252 into l_unit_of_time_value
2253 from dual;
2254 --
2255 -- Find the working hours on the Requested day
2256 --
2257 --FOR c1 in work_hrs(l_shift_id, l_unit_of_time_value) LOOP
2258 -- Modified by jawang on 06/07/2002 to fix the bug 2393255
2259 FOR c1 in work_hrs(l_shift_id, l_shift_date, l_unit_of_time_value) LOOP
2260 if(trunc(c1.shift_begin_time) = trunc(c1.shift_end_time))
2261 then
2262 j := x_shift.count + 1;
2263 x_shift(j).shift_construct_id := c1.shift_construct_id;
2264 x_shift(j).shift_date := l_shift_date;
2265 x_shift(j).start_time := to_char(c1.shift_begin_time, 'hh24.mi');
2266 x_shift(j).end_time := to_char(c1.shift_end_time, 'hh24.mi');
2267 --
2268 -- Added for Simplex Timezone Enh # 3040681 by ABRAINA
2269 --
2270 If fnd_profile.value_specific('ENABLE_TIMEZONE_CONVERSIONS') = 'Y' Then
2271 l_res_Timezone_id := Get_Res_Timezone_Id (p_resource_id, p_resource_type);
2272 If l_res_Timezone_id <> l_server_tz_id Then
2273 l_shift_b_time := to_char(l_shift_date,'DD-MON-YYYY')||' '||to_char(c1.shift_begin_time, 'hh24.mi');
2274 l_shift_b_date := to_date(l_shift_b_time,'DD/MM/YYYY HH24:MI');
2275 l_shift_e_time := to_char(l_shift_date,'DD-MON-YYYY')||' '||to_char(c1.shift_end_time, 'hh24.mi');
2276 l_shift_e_date := to_date(l_shift_e_time,'DD/MM/YYYY HH24:MI');
2277 x_shift(j).shift_date := to_char(ResourceDt_To_ServerDT(l_shift_b_date,l_res_Timezone_id,l_server_tz_id),'DD-MON-YYYY');
2278 x_shift(j).start_time := to_char(ResourceDt_To_ServerDT(l_shift_b_date,l_res_Timezone_id,l_server_tz_id),'hh24.mi');
2279 x_shift(j).end_time := to_char(ResourceDt_To_ServerDT(l_shift_e_date,l_res_Timezone_id,l_server_tz_id),'hh24.mi');
2280 End If;
2281 End If;
2282 x_shift(j).availability_type := 'W';
2283 else
2284 --added this condition for 24hr shift .. sudarsana 22 Oct 2001
2285 l_st_time := l_shift_date;
2286 l_ed_time := l_shift_date + (c1.shift_end_time - trunc(c1.shift_end_time)) +
2287 (trunc(c1.shift_end_time) - trunc(c1.shift_begin_time));
2288 while(trunc(l_st_time) <= trunc(l_ed_time))
2289 loop
2290 j := x_shift.count + 1;
2291 x_shift(j).shift_construct_id := c1.shift_construct_id;
2292 x_shift(j).shift_date := trunc(l_st_time);
2293 if(trunc(l_st_time) = trunc(l_shift_date))
2294 then
2295 x_shift(j).start_time := to_char( c1.shift_begin_time,'hh24.mi');
2296 else
2297 x_shift(j).start_time := '00.00';
2298 end if;
2299 if(trunc(l_st_time) = trunc(l_ed_time))
2300 then
2301 x_shift(j).end_time := to_char(c1.shift_end_time,'hh24.mi');
2302 else
2303 x_shift(j).end_time := '23.59';
2304 end if;
2305
2306 --
2307 -- Added for Simplex Timezone Enh # 3040681 by ABRAINA
2308 --
2309 If fnd_profile.value_specific('ENABLE_TIMEZONE_CONVERSIONS') = 'Y' Then
2310 l_res_Timezone_id := Get_Res_Timezone_Id (p_resource_id, p_resource_type);
2311 If l_res_Timezone_id <> l_server_tz_id Then
2312 l_shift_b_time := to_char(l_shift_date,'DD-MON-YYYY')||' '||to_char(c1.shift_begin_time, 'hh24.mi');
2313 l_shift_b_date := to_date(l_shift_b_time,'DD/MM/YYYY HH24:MI');
2314 l_shift_e_time := to_char(l_shift_date,'DD-MON-YYYY')||' '||to_char(c1.shift_end_time, 'hh24.mi');
2315 l_shift_e_date := to_date(l_shift_e_time,'DD/MM/YYYY HH24:MI');
2316 x_shift(j).shift_date := to_char(ResourceDt_To_ServerDT(l_shift_b_date,l_res_Timezone_id,l_server_tz_id),'DD-MON-YYYY');
2317 x_shift(j).start_time := to_char(ResourceDt_To_ServerDT(l_shift_b_date,l_res_Timezone_id,l_server_tz_id),'hh24.mi');
2318 x_shift(j).end_time := to_char(ResourceDt_To_ServerDT(l_shift_e_date,l_res_Timezone_id,l_server_tz_id),'hh24.mi');
2319 End If;
2320 End If;
2321 x_shift(j).availability_type := 'W';
2322
2323 l_st_time := l_st_time + 1;
2324 end loop;
2325 end if;
2326 END LOOP;
2327 --
2328 -- Find all the Exception hours on the requested date
2329 --
2330 For c2 in excp_hrs(l_calendar_id, l_shift_date) LOOP
2331 j := j + 1;
2332 x_shift(j).shift_date := l_shift_date;
2333 x_shift(j).start_time := c2.excp_start_time;
2334 x_shift(j).end_time := c2.excp_end_time;
2335 x_shift(j).availability_type := 'E';
2336 END LOOP;
2337 --
2338 -- Find all the assigned Task hours on the requested date
2339 --
2340 -- Modified by SBARAT on 23/06/2005 for Bug# 4443443
2341
2342 For c3 in task_hrs(p_resource_id,p_resource_type,l_shift_date,l_tz_enabled,l_server_tz_id) loop
2343
2344 --
2345 -- Modified this code for bug 2817811 by A.Raina
2346 --
2347 IF l_shift_date = c3.task_start_date and l_shift_date = c3.task_end_date THEN
2348 j := j + 1;
2349 x_shift(j).shift_date := l_shift_date;
2350 x_shift(j).start_time := c3.task_start_time;
2351 x_shift(j).end_time := c3.task_end_time;
2352 x_shift(j).availability_type := 'T';
2353 ELSIF l_shift_date = c3.task_start_date and l_shift_date <> c3.task_end_date THEN
2354
2355 FOR c1 in work_hrs(l_shift_id, l_shift_date, l_unit_of_time_value) LOOP
2356 IF c3.task_start_time >= to_char(c1.shift_begin_time,'hh24.mi') THEN
2357 j := j + 1;
2358 x_shift(j).shift_date := l_shift_date;
2359 x_shift(j).start_time := c3.task_start_time;
2360 x_shift(j).end_time := to_char(c1.shift_end_time,'hh24.mi');
2361 x_shift(j).availability_type := 'T';
2362 ELSE
2363 j := j + 1;
2364 x_shift(j).shift_date := l_shift_date;
2365 x_shift(j).start_time := to_char(c1.shift_begin_time,'hh24.mi');
2366 x_shift(j).end_time := to_char(c1.shift_end_time,'hh24.mi');
2367 x_shift(j).availability_type := 'T';
2368 END IF;
2369 END LOOP;
2370
2371 ELSIF l_shift_date <> c3.task_start_date and l_shift_date <> c3.task_end_date THEN
2372
2373 FOR c1 in work_hrs(l_shift_id, l_shift_date, l_unit_of_time_value) LOOP
2374 j := j + 1;
2375 x_shift(j).shift_date := l_shift_date;
2376 x_shift(j).start_time := to_char(c1.shift_begin_time,'hh24.mi');
2377 x_shift(j).end_time := to_char(c1.shift_end_time,'hh24.mi');
2378 x_shift(j).availability_type := 'T';
2379 End Loop;
2380
2381 ELSIF l_shift_date <> c3.task_start_date and l_shift_date = c3.task_end_date THEN
2382 FOR c1 in work_hrs(l_shift_id, l_shift_date, l_unit_of_time_value) LOOP
2383 IF c3.task_end_time <= to_char(c1.shift_end_time,'hh24.mi') THEN
2384 j := j + 1;
2385 x_shift(j).shift_date := l_shift_date;
2386 x_shift(j).start_time := to_char(c1.shift_begin_time,'hh24.mi');
2387 x_shift(j).end_time := c3.task_end_time;
2388 x_shift(j).availability_type := 'T';
2389 ELSE
2390 j := j + 1;
2391 x_shift(j).shift_date := l_shift_date;
2392 x_shift(j).start_time := to_char(c1.shift_begin_time,'hh24.mi');
2393 x_shift(j).end_time := to_char(c1.shift_end_time,'hh24.mi');
2394 x_shift(j).availability_type := 'T';
2395 END IF;
2396 END LOOP;
2397 END IF;
2398 --
2399 --End modification
2400 --
2401
2402 END LOOP;
2403 exception
2404 when no_data_found then
2405 x_return_status := FND_API.G_RET_STS_ERROR ;
2406 fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS');
2407 fnd_message.set_token('P_CAL_NAME', l_calendar_name);
2408 fnd_msg_pub.add;
2409 FND_MSG_PUB.Count_And_Get
2410 (p_count => x_msg_count,
2411 p_data => x_msg_data
2412 );
2413 end; --(2)
2414 end if; --(1)
2415 exception
2416 when no_data_found then
2417 x_return_status := FND_API.G_RET_STS_ERROR ;
2418 fnd_message.set_name('JTF','JTF_CAL_RES_NO_CAL');
2419 fnd_message.set_token('P_RES_NAME', l_resource_name);
2420 fnd_message.set_token('P_SHIFT_DATE', l_shift_date);
2421 fnd_msg_pub.add;
2422 FND_MSG_PUB.Count_And_Get
2423 (p_count => x_msg_count,
2424 p_data => x_msg_data
2425 );
2426 end; --(1)
2427 l_shift_date := l_shift_date + 1;
2428 end loop;
2429 --
2430 -- Update return status to Success if there is atleast one available time slot
2431 IF x_shift.count > 0 and x_return_status = 'E' THEN
2432 x_return_status := FND_API.G_RET_STS_SUCCESS;
2433 END IF;
2434
2435 -- Added sort by jawang on 06/27/2002
2436 if x_shift.count > 0
2437 then
2438 -- sort the out table
2439 sort_tab(x_shift);
2440 end if;
2441
2442 EXCEPTION
2443 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2445 FND_MSG_PUB.Count_And_Get
2446 ( p_count => x_msg_count,
2447 p_data => x_msg_data
2448 );
2449 WHEN G_EXC_REQUIRED_FIELDS_NULL THEN
2450 x_return_status := FND_API.G_RET_STS_ERROR ;
2451 fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
2452 fnd_message.set_token('P_PARAMETER', l_parameters);
2453 fnd_msg_pub.add;
2454 FND_MSG_PUB.Count_And_Get
2455 (p_count => x_msg_count,
2456 p_data => x_msg_data
2457 );
2458 WHEN OTHERS THEN
2459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2460 fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
2461 fnd_message.set_token('ERROR_CODE',SQLCODE);
2462 fnd_message.set_token('ERROR_MESSAGE', SQLERRM);
2463 fnd_msg_pub.add;
2464 FND_MSG_PUB.Count_And_Get
2465 (p_count => x_msg_count,
2466 p_data => x_msg_data
2467 );
2468
2469 END Get_Res_Schedule;
2470
2471 function get_g_false return varchar2 is
2472 begin
2473 return(fnd_api.g_false);
2474 end get_g_false;
2475
2476
2477 function get_g_miss_num return number is
2478 begin
2479 return(fnd_api.g_miss_num);
2480 end get_g_miss_num;
2481
2482 function get_g_miss_char return varchar2 is
2483 begin
2484 return(fnd_api.g_miss_char);
2485 end get_g_miss_char;
2486
2487 function get_g_miss_date return date is
2488 begin
2489 return(fnd_api.g_miss_date);
2490 end get_g_miss_date;
2491
2492 --
2493 -- Function ResourceDt_To_ServerDT Added for Simplex Timezone Enh # 3040681 by ABRAINA
2494 --
2495
2496 --
2497 -- Function ResourceDt_To_ServerDT Added for Simplex Timezone Enh # 3040681 by ABRAINA
2498 --
2499
2500 Function ResourceDt_To_ServerDT ( P_Resource_DtTime IN date, P_Resource_TZ_Id IN Number , p_Server_TZ_id IN Number ) RETURN date IS
2501
2502 x_Server_time Date := P_Resource_DtTime;
2503
2504 l_api_name CONSTANT VARCHAR2(30) := 'ResourceDt_To_ServerDT';
2505 l_API_VERSION Number := 1.0 ;
2506 p_API_VERSION Number := 1.0 ;
2507 l_INIT_MSG_LIST varchar2(1) := 'F';
2508 p_INIT_MSG_LIST varchar2(1) := 'F';
2509 X_msg_count Number;
2510 X_msg_data Varchar2(2000);
2511 X_RETURN_STATUS Varchar2(10);
2512
2513 BEGIN
2514
2515 -- Standard call to check for call compatibility.
2516 IF NOT FND_API.Compatible_API_Call (l_api_version ,
2517 p_api_version ,
2518 l_api_name ,
2519 G_PKG_NAME )
2520 THEN
2521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2522 END IF;
2523
2524 -- Initialize message list if p_init_msg_list is set to TRUE.
2525 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2526 FND_MSG_PUB.initialize;
2527 END IF;
2528
2529 -- Initialize API return status to success
2530 x_return_status := FND_API.G_RET_STS_SUCCESS;
2531
2532 HZ_TIMEZONE_PUB.Get_Time( l_API_VERSION
2533 , l_INIT_MSG_LIST
2534 , P_Resource_TZ_Id
2535 , p_Server_TZ_id
2536 , P_Resource_DtTime
2537 , x_Server_time
2538 , X_RETURN_STATUS
2539 , X_msg_count
2540 , X_msg_data);
2541
2542 Return x_Server_time;
2543
2544 EXCEPTION
2545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2547 FND_MSG_PUB.Count_And_Get
2548 ( p_count => x_msg_count,
2549 p_data => x_msg_data
2550 );
2551 WHEN G_EXC_REQUIRED_FIELDS_NULL THEN
2552 x_return_status := FND_API.G_RET_STS_ERROR ;
2553 fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
2554 fnd_message.set_token('P_PARAMETER', l_parameters);
2555 fnd_msg_pub.add;
2556 FND_MSG_PUB.Count_And_Get
2557 (p_count => x_msg_count,
2558 p_data => x_msg_data
2559 );
2560 WHEN OTHERS THEN
2561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2562 fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
2563 fnd_message.set_token('ERROR_CODE',SQLCODE);
2564 fnd_message.set_token('ERROR_MESSAGE', SQLERRM);
2565 fnd_msg_pub.add;
2566 FND_MSG_PUB.Count_And_Get
2567 ( p_count => x_msg_count,
2568 p_data => x_msg_data
2569 );
2570
2571 END ResourceDt_To_ServerDT;
2572
2573 Function Get_Res_Timezone_Id ( p_resource_Id IN NUMBER, p_resource_type IN VARCHAR2 ) RETURN Number IS
2574
2575 Cursor C_Res_TimeZone Is
2576 Select TIME_ZONE
2577 From JTF_RS_RESOURCE_EXTNS
2578 Where RESOURCE_ID = p_resource_id
2579 AND 'RS_'||category = p_resource_type
2580 And trunc(sysdate) between trunc(nvl(START_DATE_ACTIVE,sysdate))
2581 and trunc(nvl(END_DATE_ACTIVE,sysdate));
2582
2583 CURSOR c_group_res_timezone Is
2584 SELECT TIME_ZONE
2585 FROM JTF_RS_GROUPS_B
2586 WHERE group_id = p_resource_id
2587 AND trunc(SYSDATE) BETWEEN trunc(nvl(START_DATE_ACTIVE,SYSDATE))
2588 AND trunc(nvl(END_DATE_ACTIVE,SYSDATE));
2589
2590 l_res_timezone_id NUMBER;
2591
2592 BEGIN
2593 IF p_resource_type = 'RS_GROUP' THEN
2594 OPEN C_group_res_TimeZone ;
2595 FETCH C_group_res_TimeZone INTO l_res_timezone_id;
2596 CLOSE C_group_res_TimeZone ;
2597 ELSE
2598 OPEN C_Res_TimeZone ;
2599 FETCH C_Res_TimeZone INTO l_res_timezone_id;
2600 CLOSE C_Res_TimeZone ;
2601 END IF;
2602
2603 l_res_timezone_id := nvl(l_res_timezone_id,fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
2604
2605 RETURN l_res_timezone_id;
2606
2607 END Get_Res_Timezone_Id;
2608
2609 -- Function added for bug 3270116 by ABRAINA
2610 Function Validate_Cal_Date ( P_Calendar_Id IN number, P_shift_date IN date ) RETURN boolean IS
2611 v_valid_cal Number;
2612
2613 BEGIN
2614
2615 select 1
2616 into v_valid_cal
2617 from jtf_calendars_b a
2618 where calendar_id = P_Calendar_Id
2619 -- Commented for bug 3891896 by ABRAINA
2620 -- and P_shift_date between trunc(a.start_date_active) and nvl(trunc(a.end_date_active),to_date(get_g_miss_date,'DD/MM/RRRR'));
2621 and P_shift_date between trunc(a.start_date_active) and nvl(trunc(a.end_date_active),P_shift_date);
2622
2623 If v_valid_cal = 1 Then
2624 Return (TRUE);
2625 Else
2626 Return (FALSE);
2627 End If;
2628
2629 End Validate_Cal_Date;
2630
2631 END JTF_CALENDAR_PUB;
2632
[Home] [Help]