1 PACKAGE BODY CAC_VIEW_AVAIL_PVT AS
2 /* $Header: cacvavb.pls 120.3 2006/04/27 06:57:12 sbarat noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_CAL_AVLBLTY_PVT';
5
6 FUNCTION NumberOfSlots
7 /*****************************************************************************
8 ** Given a Start, End date and Slot size in minutes this function will
9 ** return the number of slots needed for the period.
10 *****************************************************************************/
11 ( p_StartDate IN DATE
12 , p_EndDate IN DATE
13 , p_Slotsize IN NUMBER
14 )RETURN NUMBER
15 IS
16 BEGIN
17 /***************************************************************************
18 ** determine the period in minutes, rounded to the smallest number greater
19 ** than the result.
20 ***************************************************************************/
21 RETURN CEIL(((p_EndDate - p_StartDate)*24*60)/p_SlotSize);
22 EXCEPTION
23 WHEN OTHERS
24 THEN
25 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
26
27 END NumberOfSlots;
28
29 PROCEDURE InitializeList
30 /*****************************************************************************
31 ** Given a List of type AvlblTb this function will initialize a range of
32 ** records in the table with the given values for:
33 ** - ResourceID
34 ** - ResourcType
35 ** - InitialValue (1 for available, 0 for unavailable)
36 ** The slot sequence will be generated
37 *****************************************************************************/
38 ( p_List IN OUT NOCOPY AvlblTb
39 , p_StartRecord IN NUMBER
40 , p_EndRecord IN NUMBER
41 , p_ResourceID IN NUMBER
42 , p_ResourceType IN VARCHAR2
43 , p_ResourceName IN VARCHAR2
44 , p_InitValue IN NUMBER
45 )
46 IS
47 m BINARY_INTEGER;
48 n NUMBER := 1;
49 l_ResourceName VARCHAR2(360);
50 BEGIN
51 IF ( ( p_ResourceName IS NULL )
52 AND ( p_ResourceID IS NOT NULL )
53 AND ( p_ResourceType IS NOT NULL )
54 )
55 THEN
56 l_ResourceName := jtf_task_utl.get_owner(p_object_type_code => p_ResourceType
57 ,p_object_id => p_ResourceID);
58 ELSE
59 l_ResourceName := p_ResourceName;
60 END IF;
61
62 FOR m IN p_StartRecord..p_EndRecord
63 LOOP
64 p_List(m).ResourceID := p_ResourceID;
65 p_List(m).ResourceType := p_ResourceType;
66 p_List(m).ResourceName := l_ResourceName;
67 p_List(m).SlotSequence := n;
68 p_List(m).SlotAvailable:= p_InitValue;
69 n := n + 1;
70 END LOOP;
71
72 EXCEPTION
73 WHEN OTHERS
74 THEN
75 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
76
77 END InitializeList;
78
79 PROCEDURE Slots
80 /*****************************************************************************
81 ** - This procedure will determine the slots that are unavailable for a given
82 ** period. The period is defined by the Task start date and Task end date.
83 ** - If the tasks starts before the StartDate the
84 ** - The Number Of Slots is the last slot that will be displayed, therefore
85 ** any tasks that span beyond that will return that max number
86 *****************************************************************************/
87 ( p_StartDate IN DATE -- start of period
88 , p_SlotSize IN NUMBER -- Size of the slots used
89 , p_NumberOfSlots IN NUMBER -- Max Number of slots
90 , p_TaskStartDate IN DATE -- Start time for this task
91 , p_TaskEndDate IN DATE -- End time for this task
92 , p_FirstSlot OUT NOCOPY NUMBER -- output: first slot for task
93 , p_LastSlot OUT NOCOPY NUMBER -- output: last slot for task
94 )
95 IS
96 l_FirstSlot NUMBER;
97 l_LastSlot NUMBER;
98
99 BEGIN
100
101 l_FirstSlot := TRUNC(round((((p_TaskStartDate - p_StartDate) * 24 * 60)/p_SlotSize),6)) + 1;
102 -- - round(,6) because the division doesn't return integers
103 -- - add 1 so the slots start with 1 not 0
104 l_LastSlot := CEIL((((p_TaskEndDate -(1/24/60/60)) - p_StartDate)* 24 * 60)/p_SlotSize);
105 -- - minus 1 second so '1 till 2 meetings' don't take up an extra slot for ending on the
106 -- beginning of the next edge
107
108 IF (l_FirstSlot < 1)
109 THEN
110 -- If it starts before the period we are interested in return 1
111 p_FirstSlot := 1;
112 ELSE
113 p_FirstSlot := l_FirstSlot;
114 END IF;
115
116 IF (l_LastSlot > p_NumberOfSlots)
117 THEN
118 -- If it ends beyond the period we are interested in return NumberOfSlots
119 p_LastSlot := p_NumberOfSlots;
120 ELSE
121 p_LastSlot := l_LastSlot;
122 END IF;
123
124 EXCEPTION
125 WHEN OTHERS
126 THEN
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128 END Slots;
129
130 PROCEDURE UpdateList
131 ( p_List IN OUT NOCOPY AvlblTb -- List Name
132 , p_StartRecord IN NUMBER -- For this RS List section starts with record #
133 , p_FirstSlot IN NUMBER -- First Slot to set to unavailable
134 , p_LastSlot IN NUMBER -- Last Slot to set to unavailable
135 , p_free_busy IN VARCHAR2
136 )
137 IS
138 l_slot_code NUMBER; -- 0: Unavailable, 1: Available, 2: Tentative
139 BEGIN
140 IF p_free_busy = 'BUSY' THEN
141 l_slot_code := 0;
142 ELSIF p_free_busy = 'FREE' THEN
143 l_slot_code := 1;
144 ELSIF p_free_busy = 'TENTATIVE' THEN
145 l_slot_code := 2;
146 ELSE
147 l_slot_code := 0;
148 END IF;
149
150 FOR i IN p_FirstSlot..p_LastSlot
151 LOOP
152 p_list(p_StartRecord + (i-1) ).SlotAvailable := l_slot_code;
153 END LOOP;
154
155 EXCEPTION
156 WHEN OTHERS
157 THEN
158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159 END UpdateList;
160
161 PROCEDURE UpdateList
162 ( p_List IN OUT NOCOPY AvlblTb -- List Name
163 , p_StartRecord IN NUMBER -- For this RS List section starts with record #
164 , p_FirstSlot IN NUMBER -- First Slot to set to unavailable
165 , p_LastSlot IN NUMBER -- Last Slot to set to unavailable
166 )
167 IS
168 p BINARY_INTEGER;
169 BEGIN
170 UpdateList
171 ( p_List => p_List
172 , p_StartRecord => p_StartRecord
173 , p_FirstSlot => p_FirstSlot
174 , p_LastSlot => p_LastSlot
175 , p_free_busy => NULL
176 );
177 EXCEPTION
178 WHEN OTHERS
179 THEN
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END UpdateList;
182
183 PROCEDURE Availability
184 ( p_api_version IN NUMBER
185 , p_init_msg_list IN VARCHAR2
186 , x_return_status OUT NOCOPY VARCHAR2
187 , x_msg_count OUT NOCOPY NUMBER
188 , x_msg_data OUT NOCOPY VARCHAR2
189 , p_RSList IN RSTab
190 , p_StartDateTime IN DATE -- Start DateTime of the period to check
191 , p_EndDateTime IN DATE -- End DateTime of the period to check
192 , p_SlotSize IN NUMBER -- The slot size in minutes
193 , x_NumberOfSlots OUT NOCOPY NUMBER
194 , x_AvailbltyList OUT NOCOPY AvlblTb -- list of resources and their availability
195 , x_TotalAvailbltyList OUT NOCOPY AvlblTb -- Total availability
196 )
197 IS
198 l_api_name CONSTANT VARCHAR2(30) := 'Availability';
199 l_api_version CONSTANT NUMBER := 1.0;
200 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
201 l_RSSectionStart NUMBER;
202 l_RSSectionEnd NUMBER;
203 l_FirstSlot NUMBER;
204 l_LastSlot NUMBER;
205 i BINARY_INTEGER;
206 l_StartDate DATE;
207 l_EndDate DATE;
208
209 x_WeekTimePrefTbl JTF_CAL_PVT.WeekTimePrefTblType;
210 x_Preferences JTF_CAL_PVT.Preference;
211 l_ItemDisplayType NUMBER;
212
213 CURSOR c_Tasks
214 /******************************************************************
215 ** This Cursor will fetch all Tasks related to an Employee
216 ** Resource for the given period
217 ******************************************************************/
218 ( b_ResourceID IN NUMBER
219 , b_ResourceType IN VARCHAR2
220 , b_StartDate IN DATE
221 , b_EndDate IN DATE
222 )IS SELECT /*+ INDEX(jtsb JTF_TASK_STATUSES_B_U1) */
223 jtb.source_object_id ItemSourceID
224 , jtb.source_object_type_code ItemSourceCode
225 , jtb.calendar_start_date StartDate
226 , jtb.calendar_end_date EndDate
227 , jtb.timezone_id TimezoneID
228 FROM jtf_task_all_assignments jta
229 , jtf_tasks_b jtb
230 , jtf_task_statuses_b jtsb
231 WHERE jta.resource_id = b_ResourceID -- 101272224
232 AND jta.resource_type_code = b_ResourceType -- 'RS_EMPLOYEE'
233 AND jta.task_id = jtb.task_id -- join to tasks_b
234 AND jtb.task_status_id = jtsb.task_status_id -- join to to task_status_b
235 AND jta.show_on_calendar = 'Y'
236 AND jta.assignment_status_id <> 4 -- using status rejected for declined
237 AND NVL(jtsb.closed_flag,'N')<> 'Y'
238 AND ( jtb.calendar_start_date <= b_EndDate
239 OR jtb.calendar_start_date IS NULL
240 )
241 AND ( jtb.calendar_end_date >= b_StartDate
242 OR jtb.calendar_end_date IS NULL
243 );
244
245 BEGIN
246 /*****************************************************************************
247 ** Standard call to check for call compatibility
248 *****************************************************************************/
249 IF NOT FND_API.Compatible_API_Call( l_api_version
250 , p_api_version
251 , l_api_name
252 , G_PKG_NAME
253 )
254 THEN
255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
256 END IF;
257
258 /*****************************************************************************
259 ** Initialize message list if p_init_msg_list is set to TRUE
260 *****************************************************************************/
261 IF FND_API.To_Boolean(p_init_msg_list)
262 THEN
263 FND_MSG_PUB.Initialize;
264 END IF;
265
266 /*****************************************************************************
267 ** Initialize API return status to success
268 *****************************************************************************/
269 x_return_status := FND_API.G_RET_STS_SUCCESS;
270
271 /*****************************************************************************
272 ** Get the Timezone of the first user (this is the Query user)
273 *****************************************************************************/
274 JTF_CAL_UTILITY_PVT.GetPreferences
275 ( p_ResourceID => p_RSList(p_RSList.FIRST).ResourceID
276 , p_ResourceType => p_RSList(p_RSList.FIRST).ResourceType
277 , x_Preferences => x_Preferences
278 , x_WeekTimePrefTbl => x_WeekTimePrefTbl
279 );
280
281 /*****************************************************************************
282 ** Determine the total number of slots for the given period
283 *****************************************************************************/
284 x_NumberOfSlots := NumberOfSlots( p_StartDateTime -- start of period
285 , p_EndDateTime -- end of period
286 , p_SlotSize -- slotsize in minutes
287 );
288
289 /*****************************************************************************
290 ** Initialize the total availability list to: everybody is available
291 *****************************************************************************/
292 InitializeList( x_TotalAvailbltyList -- ListName
293 , 1 -- start with record #
294 , x_NumberOfSlots -- end with record #
295 , NULL -- Resource ID
296 , NULL -- Rescource Type
297 , NULL -- Resource Name
298 , 1 -- init to 1
299 );
300
301 FOR i IN p_RSList.FIRST..p_RSList.LAST
302 LOOP <<RESOURCES>>
303 /***************************************************************************
304 ** Initialize the availability list section for this resource to:
305 ** resource is available
306 ***************************************************************************/
307 l_RSSectionStart := 1 + ((i-1) * x_NumberOfSlots);
308 l_RSSectionEnd := i * x_NumberOfSlots;
309
310 InitializeList( x_AvailbltyList -- ListName
311 , l_RSSectionStart -- start with record #
312 , l_RSSectionEnd -- end with record #
313 , p_RSList(i).ResourceID -- Resource ID
314 , p_RSList(i).ResourceType -- Rescource Type
315 , p_RSList(i).ResourceName -- Resource Name
316 , 1 -- init to 1
317 );
318
319 /***************************************************************************
320 ** Find all the Tasks assigned to this resource that are shown on Calendar
321 ***************************************************************************/
322 FOR r_ResourceTask IN c_Tasks( p_RSList(i).ResourceID
323 , p_RSList(i).ResourceType
324 , p_StartDateTime - 1 -- allow for max timezone adjustments
325 , p_EndDateTime + 1 -- allow for max timezone adjustments
326 )
327 LOOP <<RESOURCE_TASKS>>
328
329 /*************************************************************************
330 ** We will have to adjust the Start/End Date for the users timezone (if
331 ** needed)
332 *************************************************************************/
333 /* Rada, make local copies of start and end date to avoid NOCOPY issue*/
334 l_StartDate := r_ResourceTask.Startdate;
335 l_EndDate := r_ResourceTask.Enddate;
336
337
338 CAC_VIEW_UTIL_PVT.AdjustForTimezone
339 ( p_source_tz_id => r_ResourceTask.TimezoneID
340 , p_dest_tz_id => x_Preferences.Timezone
341 , p_source_day_time => l_StartDate
342 , x_dest_day_time => r_ResourceTask.Startdate
343 );
344
345 CAC_VIEW_UTIL_PVT.AdjustForTimezone
346 ( p_source_tz_id => r_ResourceTask.TimezoneID
347 , p_dest_tz_id => x_Preferences.Timezone
348 , p_source_day_time => l_EndDate
349 , x_dest_day_time => r_ResourceTask.Enddate
350 );
351
352 /***************************************************************************
353 ** Now that the StartDate and EndDate are corrected we need to check whether
354 ** it we are still interested in it
355 ***************************************************************************/
356 IF ( ( r_ResourceTask.StartDate <= p_EndDateTime )
357 AND ( r_ResourceTask.EndDate > p_StartDateTime)
358 )
359 THEN
360 /*************************************************************************
361 ** Determine the display type, only stuff on the calendar is taken into
362 ** account for availability
363 *************************************************************************/
364 l_ItemDisplayType := JTF_CAL_UTILITY_PVT.GetItemType
365 ( p_SourceCode => r_ResourceTask.ItemSourceCode
366 , p_PeriodStartDate => p_StartDateTime
367 , p_PeriodEndDate => p_EndDateTime
368 , p_StartDate => r_ResourceTask.StartDate
369 , p_EndDate => r_ResourceTask.EndDate
370 , p_CalSpanDaysProfile => 'Y'
371 );
372
373 IF (l_ItemDisplayType IN (1,5))
374 THEN
375 /*************************************************************************
376 ** This procedure will determine what slot are unavailable because of
377 ** the task that is fetched
378 *************************************************************************/
379 Slots( p_StartDateTime -- start of period
380 , p_SlotSize -- Size of the slots used
381 , x_NumberOfSlots -- Last slot for period
382 , r_ResourceTask.StartDate -- Start time for this task
383 , r_ResourceTask.EndDate -- End time for this task
384 , l_FirstSlot -- output: first slot for task
385 , l_LastSlot -- output: last slot for task
386 );
387
388 /*************************************************************************
389 ** Update the availabity list section of this resource with the Slot
390 ** Data
391 *************************************************************************/
392 UpdateList( x_AvailbltyList -- List Name
393 , l_RSSectionStart -- For this RS List section starts with record #
394 , l_FirstSlot -- First Slot to set to unavailable
395 , l_LastSlot -- Last Slot to set to unavailable
396 );
397
398 /*************************************************************************
399 ** Update the total availabity list with the Slot Data
400 *************************************************************************/
401 UpdateList( x_TotalAvailbltyList -- List Name
402 , 1 -- start with record #
403 , l_FirstSlot -- First Slot to set to unavailable
404 , l_LastSlot -- Last Slot to set to unavailable
405 );
406 END IF;
407 END IF;
408 END LOOP RESOURCE_TASKS;
409
410 END LOOP RESOURCES;
411
412 /*****************************************************************************
413 ** Standard call to get message count and if count is > 1, get message info
414 *****************************************************************************/
415 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
416 , p_data => x_msg_data
417 );
418
419 EXCEPTION
420 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
421 THEN
422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
423 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
424 , p_data => x_msg_data
425 );
426 WHEN OTHERS
427 THEN
428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
429 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
430 THEN
431 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
432 , l_api_name
433 );
434 END IF;
435 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
436 , p_data => x_msg_data
437 );
438
439 END Availability;
440
441 PROCEDURE CHECK_AVAILABILITY
442 ( p_api_version IN NUMBER
443 , p_init_msg_list IN VARCHAR2
444 , p_RSList IN RSTab
445 , p_StartDateTime IN DATE -- Start DateTime of the period to check
446 , p_EndDateTime IN DATE -- End DateTime of the period to check
447 , p_SlotSize IN NUMBER -- The slot size in minutes
448 , x_NumberOfSlots OUT NOCOPY NUMBER
449 , x_AvailbltyList OUT NOCOPY AvlblTb -- list of resources and their availability
450 , x_TotalAvailbltyList OUT NOCOPY AvlblTb -- Total availability
451 , x_return_status OUT NOCOPY VARCHAR2
452 , x_msg_count OUT NOCOPY NUMBER
453 , x_msg_data OUT NOCOPY VARCHAR2
454 )
455 IS
456 l_api_name CONSTANT VARCHAR2(30) := 'Availability';
457 l_api_version CONSTANT NUMBER := 1.0;
458 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
459 l_RSSectionStart NUMBER;
460 l_RSSectionEnd NUMBER;
461 l_FirstSlot NUMBER;
462 l_LastSlot NUMBER;
463 i BINARY_INTEGER;
464 l_StartDate DATE;
465 l_EndDate DATE;
466
467 x_WeekTimePrefTbl JTF_CAL_PVT.WeekTimePrefTblType;
468 x_Preferences JTF_CAL_PVT.Preference;
469 l_ItemDisplayType NUMBER;
470
471 CURSOR c_Tasks
472 /******************************************************************
473 ** This Cursor will fetch all Tasks related to an Employee
474 ** Resource for the given period
475 ******************************************************************/
476 ( b_ResourceID NUMBER
477 , b_ResourceType VARCHAR2
478 , b_StartDate DATE
479 , b_EndDate DATE
480 )IS SELECT /*+ INDEX(jtsb JTF_TASK_STATUSES_B_U1) */
481 jtb.source_object_id ItemSourceID
482 , jtb.source_object_type_code ItemSourceCode
483 , jtb.calendar_start_date StartDate
484 , jtb.calendar_end_date EndDate
485 , jtb.timezone_id TimezoneID
486 , NVL(jta.free_busy_type,'BUSY') FreeBusyType
487 FROM jtf_task_all_assignments jta
488 , jtf_tasks_b jtb
489 , jtf_task_statuses_b jtsb
490 WHERE jta.resource_id = b_ResourceID -- 101272224
491 AND jta.resource_type_code = b_ResourceType -- 'RS_EMPLOYEE'
492 AND jta.task_id = jtb.task_id -- join to tasks_b
493 AND jtb.task_status_id = jtsb.task_status_id -- join to to task_status_b
494 AND jta.show_on_calendar = 'Y'
495 AND jta.assignment_status_id <> 4 -- using status rejected for declined
496 AND NVL(jtsb.closed_flag,'N')<> 'Y'
497 AND ( jtb.calendar_start_date <= b_EndDate
498 OR jtb.calendar_start_date IS NULL
499 )
500 AND ( jtb.calendar_end_date >= b_StartDate
501 OR jtb.calendar_end_date IS NULL
502 );
503
504 BEGIN
505 /*****************************************************************************
506 ** Standard call to check for call compatibility
507 *****************************************************************************/
508 IF NOT FND_API.Compatible_API_Call( l_api_version
509 , p_api_version
510 , l_api_name
511 , G_PKG_NAME
512 )
513 THEN
514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
515 END IF;
516
517 /*****************************************************************************
518 ** Initialize message list if p_init_msg_list is set to TRUE
519 *****************************************************************************/
520 IF FND_API.To_Boolean(p_init_msg_list)
521 THEN
522 FND_MSG_PUB.Initialize;
523 END IF;
524
525 /*****************************************************************************
526 ** Initialize API return status to success
527 *****************************************************************************/
528 x_return_status := FND_API.G_RET_STS_SUCCESS;
529
530 /*****************************************************************************
531 ** Get the Timezone of the first user (this is the Query user)
532 *****************************************************************************/
533 JTF_CAL_UTILITY_PVT.GetPreferences
534 ( p_ResourceID => p_RSList(p_RSList.FIRST).ResourceID
535 , p_ResourceType => p_RSList(p_RSList.FIRST).ResourceType
536 , x_Preferences => x_Preferences
537 , x_WeekTimePrefTbl => x_WeekTimePrefTbl
538 );
539
540 /*****************************************************************************
541 ** Determine the total number of slots for the given period
542 *****************************************************************************/
543 x_NumberOfSlots := NumberOfSlots( p_StartDateTime -- start of period
544 , p_EndDateTime -- end of period
545 , p_SlotSize -- slotsize in minutes
546 );
547
548 /*****************************************************************************
549 ** Initialize the total availability list to: everybody is available
550 *****************************************************************************/
551 InitializeList( x_TotalAvailbltyList -- ListName
552 , 1 -- start with record #
553 , x_NumberOfSlots -- end with record #
554 , NULL -- Resource ID
555 , NULL -- Rescource Type
556 , NULL -- Resource Name
557 , 1 -- init to 1
558 );
559
560 FOR i IN p_RSList.FIRST..p_RSList.LAST
561 LOOP <<RESOURCES>>
562 /***************************************************************************
563 ** Initialize the availability list section for this resource to:
564 ** resource is available
565 ***************************************************************************/
566 l_RSSectionStart := 1 + ((i-1) * x_NumberOfSlots);
567 l_RSSectionEnd := i * x_NumberOfSlots;
568
569 InitializeList( x_AvailbltyList -- ListName
570 , l_RSSectionStart -- start with record #
571 , l_RSSectionEnd -- end with record #
572 , p_RSList(i).ResourceID -- Resource ID
573 , p_RSList(i).ResourceType -- Rescource Type
574 , p_RSList(i).ResourceName -- Resource Name
575 , 1 -- init to 1
576 );
577
578 /***************************************************************************
579 ** Find all the Tasks assigned to this resource that are shown on Calendar
580 ***************************************************************************/
581 FOR r_ResourceTask IN c_Tasks( p_RSList(i).ResourceID
582 , p_RSList(i).ResourceType
583 , p_StartDateTime - 1 -- allow for max timezone adjustments
584 , p_EndDateTime + 1 -- allow for max timezone adjustments
585 )
586 LOOP <<RESOURCE_TASKS>>
587 /* amigupta, If the Resource Type is PARTY_RELATIONSHIP i.e Contact don't check the availability and combined availability
588 */
589 IF( p_RSList(i).ResourceType <> 'PARTY_RELATIONSHIP')
590 THEN
591
592 /*************************************************************************
593 ** We will have to adjust the Start/End Date for the users timezone (if
594 ** needed)
595 *************************************************************************/
596 /* Rada, make local copies of start and end date to avoid NOCOPY issue*/
597 l_StartDate := r_ResourceTask.Startdate;
598 l_EndDate := r_ResourceTask.Enddate;
599
600
601 CAC_VIEW_UTIL_PVT.AdjustForTimezone
602 ( p_source_tz_id => r_ResourceTask.TimezoneID
603 , p_dest_tz_id => x_Preferences.Timezone
604 , p_source_day_time => l_StartDate
605 , x_dest_day_time => r_ResourceTask.Startdate
606 );
607
608 CAC_VIEW_UTIL_PVT.AdjustForTimezone
609 ( p_source_tz_id => r_ResourceTask.TimezoneID
610 , p_dest_tz_id => x_Preferences.Timezone
611 , p_source_day_time => l_EndDate
612 , x_dest_day_time => r_ResourceTask.Enddate
613 );
614
615 /***************************************************************************
616 ** Now that the StartDate and EndDate are corrected we need to check whether
617 ** it we are still interested in it
618 ***************************************************************************/
619 IF ( ( r_ResourceTask.StartDate <= p_EndDateTime )
620 AND ( r_ResourceTask.EndDate > p_StartDateTime)
621 )
622 THEN
623 /*************************************************************************
624 ** Determine the display type, only stuff on the calendar is taken into
625 ** account for availability
626 *************************************************************************/
627 l_ItemDisplayType := JTF_CAL_UTILITY_PVT.GetItemType
628 ( p_SourceCode => r_ResourceTask.ItemSourceCode
629 , p_PeriodStartDate => p_StartDateTime
630 , p_PeriodEndDate => p_EndDateTime
631 , p_StartDate => r_ResourceTask.StartDate
632 , p_EndDate => r_ResourceTask.EndDate
633 , p_CalSpanDaysProfile => 'Y'
634 );
635
636 IF (l_ItemDisplayType IN (1,5) AND r_ResourceTask.FreeBusyType <> 'FREE')
637 THEN
638 /*************************************************************************
639 ** This procedure will determine what slot are unavailable because of
640 ** the task that is fetched
641 *************************************************************************/
642 Slots( p_StartDateTime -- start of period
643 , p_SlotSize -- Size of the slots used
644 , x_NumberOfSlots -- Last slot for period
645 , r_ResourceTask.StartDate -- Start time for this task
646 , r_ResourceTask.EndDate -- End time for this task
647 , l_FirstSlot -- output: first slot for task
648 , l_LastSlot -- output: last slot for task
649 );
650
651 /*************************************************************************
652 ** Update the availabity list section of this resource with the Slot
653 ** Data
654 *************************************************************************/
655 UpdateList( x_AvailbltyList -- List Name
656 , l_RSSectionStart -- For this RS List section starts with record #
657 , l_FirstSlot -- First Slot to set to unavailable
658 , l_LastSlot -- Last Slot to set to unavailable
659 , r_ResourceTask.FreeBusyType
660 );
661
662 /*************************************************************************
663 ** Update the total availabity list with the Slot Data
664 *************************************************************************/
665 UpdateList( x_TotalAvailbltyList -- List Name
666 , 1 -- start with record #
667 , l_FirstSlot -- First Slot to set to unavailable
668 , l_LastSlot -- Last Slot to set to unavailable
669 , r_ResourceTask.FreeBusyType
670 );
671 END IF;
672 END IF;
673 END IF; --End Contact
674 END LOOP RESOURCE_TASKS;
675
676 END LOOP RESOURCES;
677
678 /*****************************************************************************
679 ** Standard call to get message count and if count is > 1, get message info
680 *****************************************************************************/
681 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
682 , p_data => x_msg_data
683 );
684
685 EXCEPTION
686 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
687 THEN
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
689 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
690 , p_data => x_msg_data
691 );
692 WHEN OTHERS
693 THEN
694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
695 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
696 THEN
697 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
698 , l_api_name
699 );
700 END IF;
701 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
702 , p_data => x_msg_data
703 );
704
705 END CHECK_AVAILABILITY;
706
707 PROCEDURE CHECK_AVAILABILITY
708 ( p_api_version IN NUMBER
709 , p_init_msg_list IN VARCHAR2
710 , p_task_id IN NUMBER
711 , p_StartDateTime IN DATE -- Start DateTime of the period to check
712 , p_EndDateTime IN DATE -- End DateTime of the period to check
713 , p_SlotSize IN NUMBER -- The slot size in minutes
714 , x_NumberOfSlots OUT NOCOPY NUMBER
715 , x_AvailbltyList OUT NOCOPY AvlblTb -- list of resources and their availability
716 , x_TotalAvailbltyList OUT NOCOPY AvlblTb -- Total availability
717 , x_return_status OUT NOCOPY VARCHAR2
718 , x_msg_count OUT NOCOPY NUMBER
719 , x_msg_data OUT NOCOPY VARCHAR2
720 )
721 IS
722 l_api_name CONSTANT VARCHAR2(30) := 'Availability';
723 l_api_version CONSTANT NUMBER := 1.0;
724 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
725
726 CURSOR c_resources (b_task_id NUMBER) IS
727 SELECT jta.resource_id
728 , jta.resource_type_code
729 , jtf_task_utl.get_owner(jta.resource_type_code, jta.resource_id) resource_name
730 FROM jtf_task_all_assignments jta
731 WHERE jta.task_id = b_task_id
732 AND jta.show_on_calendar = 'Y'
733 AND jta.assignment_status_id <> 4 -- using status rejected for declined
734 AND jta.resource_type_code IN ('PN_LOCATIONS', 'RS_EMPLOYEE', 'RS_GROUP');
735
736 l_resource_list RSTab;
737 i NUMBER := 0;
738
739 BEGIN
740 /*****************************************************************************
741 ** Standard call to check for call compatibility
742 *****************************************************************************/
743 IF NOT FND_API.Compatible_API_Call( l_api_version
744 , p_api_version
745 , l_api_name
746 , g_pkg_name
747 )
748 THEN
749 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
750 END IF;
751
752 /*****************************************************************************
753 ** Initialize message list if p_init_msg_list is set to TRUE
754 *****************************************************************************/
755 IF FND_API.To_Boolean(p_init_msg_list)
756 THEN
757 FND_MSG_PUB.Initialize;
758 END IF;
759
760 /*****************************************************************************
761 ** Initialize API return status to success
762 *****************************************************************************/
763 x_return_status := FND_API.G_RET_STS_SUCCESS;
764
765 FOR rec IN c_resources (p_task_id)
766 LOOP
767 i := i + 1;
768 l_resource_list(i).resourceID := rec.resource_id;
769 l_resource_list(i).resourceType := rec.resource_type_code;
770 l_resource_list(i).resourceName := rec.resource_name;
771 END LOOP;
772
773 CHECK_AVAILABILITY
774 ( p_api_version => p_api_version
775 , p_init_msg_list => p_init_msg_list
776 , p_RSList => l_resource_list
777 , p_StartDateTime => p_StartDateTime
778 , p_EndDateTime => p_EndDateTime
779 , p_SlotSize => p_SlotSize
780 , x_NumberOfSlots => x_NumberOfSlots
781 , x_AvailbltyList => x_AvailbltyList
782 , x_TotalAvailbltyList => x_TotalAvailbltyList
783 , x_return_status => x_return_status
784 , x_msg_count => x_msg_count
785 , x_msg_data => x_msg_data
786 );
787
788 /*****************************************************************************
789 ** Standard call to get message count and if count is > 1, get message info
790 *****************************************************************************/
791 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
792 , p_data => x_msg_data
793 );
794
795 EXCEPTION
796 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
797 THEN
798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
799 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
800 , p_data => x_msg_data
801 );
802 WHEN OTHERS
803 THEN
804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
805 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
806 THEN
807 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
808 , l_api_name
809 );
810 END IF;
811 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
812 , p_data => x_msg_data
813 );
814
815 END CHECK_AVAILABILITY;
816
817 END CAC_VIEW_AVAIL_PVT;
[Home] [Help]