1 PACKAGE BODY JTF_CAL_AVLBLTY_PVT AS
2 /* $Header: jtfvavb.pls 115.16 2003/10/28 00:37:24 cjang ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_CAL_AVLBLTY_PVT';
5
6 PROCEDURE Availability
7 ( p_api_version IN NUMBER
8 , p_init_msg_list IN VARCHAR2
9 , x_return_status OUT NOCOPY VARCHAR2
10 , x_msg_count OUT NOCOPY NUMBER
11 , x_msg_data OUT NOCOPY VARCHAR2
12 , p_RSList IN RSTab
13 , p_StartDateTime IN DATE -- Start DateTime of the period to check
14 , p_EndDateTime IN DATE -- End DateTime of the period to check
15 , p_SlotSize IN NUMBER -- The slot size in minutes
16 , x_NumberOfSlots OUT NOCOPY NUMBER
17 , x_AvailbltyList OUT NOCOPY AvlblTb -- list of resources and their availability
18 , x_TotalAvailbltyList OUT NOCOPY AvlblTb -- Total availability
19 )
20 IS
21 l_api_name CONSTANT VARCHAR2(30) := 'Availability';
22 l_api_version CONSTANT NUMBER := 1.0;
23 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
24 l_RSSectionStart NUMBER;
25 l_RSSectionEnd NUMBER;
26 l_FirstSlot NUMBER;
27 l_LastSlot NUMBER;
28 i BINARY_INTEGER;
29 l_StartDate DATE;
30 l_EndDate DATE;
31
32 x_WeekTimePrefTbl JTF_CAL_PVT.WeekTimePrefTblType;
33 x_Preferences JTF_CAL_PVT.Preference;
34 l_ItemDisplayType NUMBER;
35
36 CURSOR c_Tasks
37 /******************************************************************
38 ** This Cursor will fetch all Tasks related to an Employee
39 ** Resource for the given period
40 ******************************************************************/
41 ( b_ResourceID IN NUMBER
42 , b_ResourceType IN VARCHAR2
43 , b_StartDate IN DATE
44 , b_EndDate IN DATE
45 )IS SELECT jtb.source_object_id ItemSourceID
46 , jtb.source_object_type_code ItemSourceCode
47 , jtb.calendar_start_date StartDate
48 , jtb.calendar_end_date EndDate
49 , jtb.timezone_id TimezoneID
50 FROM jtf_task_all_assignments jta
51 , jtf_tasks_b jtb
52 , jtf_task_statuses_b jtsb
53 WHERE jta.resource_id = b_ResourceID -- 101272224
54 AND jta.resource_type_code = b_ResourceType -- 'RS_EMPLOYEE'
55 AND jta.task_id = jtb.task_id -- join to tasks_b
56 AND jtb.task_status_id = jtsb.task_status_id -- join to to task_status_b
57 AND jta.show_on_calendar = 'Y'
58 AND jta.assignment_status_id <> 4 -- using status rejected for declined
59 AND NVL(jtsb.closed_flag,'N')<> 'Y'
60 AND ( jtb.calendar_start_date <= b_EndDate
61 OR jtb.calendar_start_date IS NULL
62 )
63 AND ( jtb.calendar_end_date >= b_StartDate
64 OR jtb.calendar_end_date IS NULL
65 );
66
67 FUNCTION NumberOfSlots
68 /*****************************************************************************
69 ** Given a Start, End date and Slot size in minutes this function will
70 ** return the number of slots needed for the period.
71 *****************************************************************************/
72 ( p_StartDate IN DATE
73 , p_EndDate IN DATE
74 , p_Slotsize IN NUMBER
75 )RETURN NUMBER
76 IS
77 BEGIN
78 /***************************************************************************
79 ** determine the period in minutes, rounded to the smallest number greater
80 ** than the result.
81 ***************************************************************************/
82 RETURN CEIL(((p_EndDate - p_StartDate)*24*60)/p_SlotSize);
83 EXCEPTION
84 WHEN OTHERS
85 THEN
86 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
87
88 END NumberOfSlots;
89
90 PROCEDURE InitializeList
91 /*****************************************************************************
92 ** Given a List of type AvlblTb this function will initialize a range of
93 ** records in the table with the given values for:
94 ** - ResourceID
95 ** - ResourcType
96 ** - InitialValue (0 for available, 1 for unavailable)
97 ** The slot sequence will be generated
98 *****************************************************************************/
99 ( p_List IN OUT NOCOPY AvlblTb
100 , p_StartRecord IN NUMBER
101 , p_EndRecord IN NUMBER
102 , p_ResourceID IN NUMBER
103 , p_ResourceType IN VARCHAR2
104 , p_ResourceName IN VARCHAR2
105 , p_InitValue IN NUMBER
106 )
107 IS
108 m BINARY_INTEGER;
109 n NUMBER := 1;
110 l_ResourceName VARCHAR2(360);
111 BEGIN
112 IF ( ( p_ResourceName IS NULL )
113 AND ( p_ResourceID IS NOT NULL )
114 AND ( p_ResourceType IS NOT NULL )
115 )
116 THEN
117 -- l_ResourceName := JTF_CAL_UTILITY_PVT.GetUserName
118 -- (p_resource_id => p_ResourceID
119 -- );
120 l_ResourceName := JTF_CAL_UTILITY_PVT.GetResourceName
121 (p_resource_id => p_ResourceID
122 ,p_resource_type => p_ResourceType
123 );
124 ELSE
125 l_ResourceName := p_ResourceName;
126 END IF;
127
128 FOR m IN p_StartRecord..p_EndRecord
129 LOOP
130 p_List(m).ResourceID := p_ResourceID;
131 p_List(m).ResourceType := p_ResourceType;
132 p_List(m).ResourceName := l_ResourceName;
133 p_List(m).SlotSequence := n;
134 p_List(m).SlotAvailable:= p_InitValue;
135 n := n + 1;
136 END LOOP;
137
138 EXCEPTION
139 WHEN OTHERS
140 THEN
141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
142
143 END InitializeList;
144
145 PROCEDURE Slots
146 /*****************************************************************************
147 ** - This procedure will determine the slots that are unavailable for a given
148 ** period. The period is defined by the Task start date and Task end date.
149 ** - If the tasks starts before the StartDate the
150 ** - The Number Of Slots is the last slot that will be displayed, therefore
151 ** any tasks that span beyond that will return that max number
152 *****************************************************************************/
153 ( p_StartDate IN DATE -- start of period
154 , p_SlotSize IN NUMBER -- Size of the slots used
155 , p_NumberOfSlots IN NUMBER -- Max Number of slots
156 , p_TaskStartDate IN DATE -- Start time for this task
157 , p_TaskEndDate IN DATE -- End time for this task
158 , p_FirstSlot OUT NOCOPY NUMBER -- output: first slot for task
159 , p_LastSlot OUT NOCOPY NUMBER -- output: last slot for task
160 )
161 IS
162 l_FirstSlot NUMBER;
163 l_LastSlot NUMBER;
164
165 BEGIN
166
167 l_FirstSlot := TRUNC(round((((p_TaskStartDate - p_StartDate) * 24 * 60)/p_SlotSize),6)) + 1;
168 -- - round(,6) because the division doesn't return integers
169 -- - add 1 so the slots start with 1 not 0
170 l_LastSlot := CEIL((((p_TaskEndDate -(1/24/60/60)) - p_StartDate)* 24 * 60)/p_SlotSize);
171 -- - minus 1 second so '1 till 2 meetings' don't take up an extra slot for ending on the
172 -- beginning of the next edge
173
174 IF (l_FirstSlot < 1)
175 THEN
176 -- If it starts before the period we are interested in return 1
177 p_FirstSlot := 1;
178 ELSE
179 p_FirstSlot := l_FirstSlot;
180 END IF;
181
182 IF (l_LastSlot > p_NumberOfSlots)
183 THEN
184 -- If it ends beyond the period we are interested in return NumberOfSlots
185 p_LastSlot := p_NumberOfSlots;
186 ELSE
187 p_LastSlot := l_LastSlot;
188 END IF;
189
190 EXCEPTION
191 WHEN OTHERS
192 THEN
193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
194 END Slots;
195
196 PROCEDURE UpdateList
197 ( p_List IN OUT NOCOPY AvlblTb -- List Name
198 , p_StartRecord IN NUMBER -- For this RS List section starts with record #
199 , p_FirstSlot IN NUMBER -- First Slot to set to unavailable
200 , p_LastSlot IN NUMBER -- Last Slot to set to unavailable
201 )
202 IS
203 p BINARY_INTEGER;
204 BEGIN
205 FOR p IN p_FirstSlot..p_LastSlot
206 LOOP <<UNAVAILBLE>>
207 p_list(p_StartRecord + (p-1) ).SlotAvailable := 0;
208 END LOOP UNAVAILABLE;
209
210 EXCEPTION
211 WHEN OTHERS
212 THEN
213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
214 END UpdateList;
215
216 BEGIN
217 /*****************************************************************************
218 ** Standard call to check for call compatibility
219 *****************************************************************************/
220 IF NOT FND_API.Compatible_API_Call( l_api_version
221 , p_api_version
222 , l_api_name
223 , G_PKG_NAME
224 )
225 THEN
226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
227 END IF;
228
229 /*****************************************************************************
230 ** Initialize message list if p_init_msg_list is set to TRUE
231 *****************************************************************************/
232 IF FND_API.To_Boolean(p_init_msg_list)
233 THEN
234 FND_MSG_PUB.Initialize;
235 END IF;
236
237 /*****************************************************************************
238 ** Initialize API return status to success
239 *****************************************************************************/
240 x_return_status := FND_API.G_RET_STS_SUCCESS;
241
242 /*****************************************************************************
243 ** Get the Timezone of the first user (this is the Query user)
244 *****************************************************************************/
245 JTF_CAL_UTILITY_PVT.GetPreferences
246 ( p_ResourceID => p_RSList(p_RSList.FIRST).ResourceID
247 , p_ResourceType => p_RSList(p_RSList.FIRST).ResourceType
248 , x_Preferences => x_Preferences
249 , x_WeekTimePrefTbl => x_WeekTimePrefTbl
250 );
251
252 /*****************************************************************************
253 ** Determine the total number of slots for the given period
254 *****************************************************************************/
255 x_NumberOfSlots := NumberOfSlots( p_StartDateTime -- start of period
256 , p_EndDateTime -- end of period
257 , p_SlotSize -- slotsize in minutes
258 );
259
260 /*****************************************************************************
261 ** Initialize the total availability list to: everybody is available
262 *****************************************************************************/
263 InitializeList( x_TotalAvailbltyList -- ListName
264 , 1 -- start with record #
265 , x_NumberOfSlots -- end with record #
266 , NULL -- Resource ID
267 , NULL -- Rescource Type
268 , NULL -- Resource Name
269 , 1 -- init to 1
270 );
271
272 FOR i IN p_RSList.FIRST..p_RSList.LAST
273 LOOP <<RESOURCES>>
274 /***************************************************************************
275 ** Initialize the availability list section for this resource to:
276 ** resource is available
277 ***************************************************************************/
278 l_RSSectionStart := 1 + ((i-1) * x_NumberOfSlots);
279 l_RSSectionEnd := i * x_NumberOfSlots;
280
281 InitializeList( x_AvailbltyList -- ListName
282 , l_RSSectionStart -- start with record #
283 , l_RSSectionEnd -- end with record #
284 , p_RSList(i).ResourceID -- Resource ID
285 , p_RSList(i).ResourceType -- Rescource Type
286 , p_RSList(i).ResourceName -- Resource Name
287 , 1 -- init to 1
288 );
289
290 /***************************************************************************
291 ** Find all the Tasks assigned to this resource that are shown on Calendar
292 ***************************************************************************/
293 FOR r_ResourceTask IN c_Tasks( p_RSList(i).ResourceID
294 , p_RSList(i).ResourceType
295 , p_StartDateTime - 1 -- allow for max timezone adjustments
296 , p_EndDateTime + 1 -- allow for max timezone adjustments
297 )
298 LOOP <<RESOURCE_TASKS>>
299
300 /*************************************************************************
301 ** We will have to adjust the Start/End Date for the users timezone (if
302 ** needed)
303 *************************************************************************/
304 /* Rada, make local copies of start and end date to avoid NOCOPY issue*/
305 l_StartDate := r_ResourceTask.Startdate;
306 l_EndDate := r_ResourceTask.Enddate;
307
308
309 JTF_CAL_UTILITY_PVT.AdjustForTimezone
310 ( p_source_tz_id => r_ResourceTask.TimezoneID
311 , p_dest_tz_id => x_Preferences.Timezone
312 , p_source_day_time => l_StartDate
313 , x_dest_day_time => r_ResourceTask.Startdate
314 );
315
316 JTF_CAL_UTILITY_PVT.AdjustForTimezone
317 ( p_source_tz_id => r_ResourceTask.TimezoneID
318 , p_dest_tz_id => x_Preferences.Timezone
319 , p_source_day_time => l_EndDate
320 , x_dest_day_time => r_ResourceTask.Enddate
321 );
322
323 /***************************************************************************
324 ** Now that the StartDate and EndDate are corrected we need to check whether
325 ** it we are still interested in it
326 ***************************************************************************/
330 THEN
327 IF ( ( r_ResourceTask.StartDate <= p_EndDateTime )
328 AND ( r_ResourceTask.EndDate > p_StartDateTime)
329 )
331 /*************************************************************************
332 ** Determine the display type, only stuff on the calendar is taken into
333 ** account for availability
334 *************************************************************************/
335 l_ItemDisplayType := JTF_CAL_UTILITY_PVT.GetItemType
336 ( p_SourceCode => r_ResourceTask.ItemSourceCode
337 , p_PeriodStartDate => p_StartDateTime
338 , p_PeriodEndDate => p_EndDateTime
339 , p_StartDate => r_ResourceTask.StartDate
340 , p_EndDate => r_ResourceTask.EndDate
341 , p_CalSpanDaysProfile => 'Y'
342 );
343
344 IF (l_ItemDisplayType IN (1,5))
345 THEN
346 /*************************************************************************
347 ** This procedure will determine what slot are unavailable because of
348 ** the task that is fetched
349 *************************************************************************/
350 Slots( p_StartDateTime -- start of period
351 , p_SlotSize -- Size of the slots used
352 , x_NumberOfSlots -- Last slot for period
353 , r_ResourceTask.StartDate -- Start time for this task
354 , r_ResourceTask.EndDate -- End time for this task
355 , l_FirstSlot -- output: first slot for task
356 , l_LastSlot -- output: last slot for task
357 );
358
359 /*************************************************************************
360 ** Update the availabity list section of this resource with the Slot
361 ** Data
362 *************************************************************************/
363 UpdateList( x_AvailbltyList -- List Name
364 , l_RSSectionStart -- For this RS List section starts with record #
365 , l_FirstSlot -- First Slot to set to unavailable
366 , l_LastSlot -- Last Slot to set to unavailable
367 );
368
369 /*************************************************************************
370 ** Update the total availabity list with the Slot Data
371 *************************************************************************/
372 UpdateList( x_TotalAvailbltyList -- List Name
373 , 1 -- start with record #
374 , l_FirstSlot -- First Slot to set to unavailable
375 , l_LastSlot -- Last Slot to set to unavailable
376 );
377 END IF;
378 END IF;
379 END LOOP RESOURCE_TASKS;
380 END LOOP RESOURCES;
381
382 /*****************************************************************************
383 ** Standard call to get message count and if count is > 1, get message info
384 *****************************************************************************/
385 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
386 , p_data => x_msg_data
387 );
388
389 EXCEPTION
390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
391 THEN
392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
393 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
394 , p_data => x_msg_data
395 );
396 WHEN OTHERS
397 THEN
398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
399 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
400 THEN
401 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
402 , l_api_name
403 );
404 END IF;
405 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
406 , p_data => x_msg_data
407 );
408
409 END Availability;
410 END JTF_CAL_AVLBLTY_PVT;
[Home] [Help]