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