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