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