1 PACKAGE BODY CAC_AVLBLTY_PUB AS
2 /* $Header: caccabb.pls 120.5 2008/01/09 12:52:05 lokumar ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CAC_AVLBLTY_PUB';
5
6 /*******************************************************************************
7 ** Public APIs
8 *******************************************************************************/
9
10 PROCEDURE GET_SCHEDULE
11 /*******************************************************************************
12 ** getSchedule
13 **
14 ** Roughly translates to JTF_CALENDAR_PUB_24HR.Get_Resource_Shifts API.
15 ** It will return a list of periods for which the given Object is considered
16 ** to be available. The algorithme used is as follows:
17 **
18 ** 24*7*365 (full availability if no constraints are defined)
19 ** Schedule (if a schedule was defined we'll use it)
20 ** Holidays (if Holidays are defined in HR we'll honor them)
21 ** Exceptions - (Resource level Exceptions will be honored)
22 ** --------------
23 ** Schedule
24 **
25 *******************************************************************************/
26 ( p_api_version IN NUMBER -- API version you coded against
27 , p_init_msg_list IN VARCHAR2 -- Create a new error stack?
28 , p_Object_Type IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
29 , p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
30 , p_Start_Date_Time IN DATE -- start date and time of period of interest
31 , p_End_Date_Time IN DATE -- end date and time of period of interest
32 , p_Schedule_Category IN VARCHAR2 -- Schedule Category of the schedule instance we'll look at
33 , p_Include_Exception IN VARCHAR2 -- 'T' or 'F' depending on whether the exceptions be included or not
34 , p_Busy_Tentative IN VARCHAR2 -- How to treat periods with FREEBUSYTYPE = BUSY TENTATIVE?
35 -- FREE: BUSY TENTATIVE means FREE
36 -- BUSY: BUSY TENTATIVE means BUSY
37 -- NULL: leave the interpretation to caller
38 , x_Schedule OUT NOCOPY CAC_AVLBLTY_TIME_VARRAY
39 -- return schedule
40 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
41 -- 'E': API completed with recoverable errors; explanation on errorstack
42 -- 'U': API completed with UN recoverable errors: error message on error stack
43 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
44 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
45 ) IS
46
47 l_api_name CONSTANT VARCHAR2(30) := 'GET_SCHEDULE';
48 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
49 l_api_version CONSTANT NUMBER := 1.0;
50 l_summary CAC_AVLBLTY_SUMMARY_VARRAY;
51
52 BEGIN
53
54 -- Check version number
55 IF NOT fnd_api.compatible_api_call
56 ( l_api_version
57 , p_api_version
58 , l_api_name
59 , g_pkg_name
60 )
61 THEN
62 RAISE fnd_api.g_exc_unexpected_error;
63 END IF;
64
65 -- Initialize message list
66 IF fnd_api.to_boolean( p_init_msg_list )
67 THEN
68 fnd_msg_pub.initialize;
69 END IF;
70
71 -- Initialize return status to SUCCESS
72 x_return_status := fnd_api.g_ret_sts_success;
73
74 CAC_AVLBLTY_PVT.GET_SCHEDULE_DATA
75 (
76 p_Object_Type => p_Object_Type,
77 p_Object_ID => p_Object_ID,
78 p_Start_Date_Time => p_Start_Date_Time,
79 p_End_Date_Time => p_End_Date_Time,
80 p_Schdl_Cat => p_Schedule_Category,
81 p_Include_Exception => p_Include_Exception,
82 p_Busy_Tentative => p_Busy_Tentative,
83 p_return_type => 'D',
84 x_Schedule => x_Schedule,
85 x_Schedule_Summary => l_summary
86 );
87
88 EXCEPTION
89
90 WHEN fnd_api.g_exc_error
91 THEN
92 x_return_status := fnd_api.g_ret_sts_error;
93 fnd_msg_pub.count_and_get( p_encoded => 'F'
94 , p_count => x_msg_count
95 , p_data => x_msg_data
96 );
97
98 WHEN fnd_api.g_exc_unexpected_error
99 THEN
100 x_return_status := fnd_api.g_ret_sts_unexp_error;
101 fnd_msg_pub.count_and_get( p_encoded => 'F'
102 , p_count => x_msg_count
103 , p_data => x_msg_data
104 );
105
106 WHEN OTHERS
107 THEN
108 --
109 -- Set status
110 --
111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
112
113 --
114 -- Push message onto CRM stack
115 --
116 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
117 , l_api_name
118 , SQLERRM
119 );
120 --
121 -- Count the messages on the CRM stack
122 --
123 x_msg_count := FND_MSG_PUB.COUNT_MSG;
124
125 END GET_SCHEDULE;
126
127
128 PROCEDURE GET_SCHEDULE_SUMMARY
129 /*******************************************************************************
130 ** GET_SCHEDULE_SUMMARY
131 **
132 ** This API will return summary of schedule on day by day basis
133 ** The algorithme used is as follows:
134 **
135 ** 24*7*365 (full availability if no constraints are defined)
136 ** Schedule (if a schedule was defined we'll use it)
137 ** Holidays (if Holidays are defined in HR we'll honor them)
138 ** Exceptions - (Resource level Exceptions will be honored)
139 ** --------------
140 ** Schedule
141 **
142 *******************************************************************************/
143 ( p_api_version IN NUMBER -- API version you coded against
144 , p_init_msg_list IN VARCHAR2 -- Create a new error stack?
145 , p_Object_Type IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
146 , p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
147 , p_Start_Date IN DATE -- start date of period of interest
148 , p_End_Date IN DATE -- end date of period of interest
149 , p_Schedule_Category IN VARCHAR2 -- Schedule Category of the schedule instance we'll look at
150 , p_Include_Exception IN VARCHAR2 -- 'T' or 'F' depending on whether the exceptions be included or not
151 , p_Busy_Tentative IN VARCHAR2 -- How to treat periods with FREEBUSYTYPE = BUSY TENTATIVE?
152 -- FREE: BUSY TENTATIVE means FREE
153 -- BUSY: BUSY TENTATIVE means BUSY
154 -- NULL: leave the interpretation to caller
155 , x_Schedule_Summary OUT NOCOPY CAC_AVLBLTY_SUMMARY_VARRAY
156 -- return schedule summary
157 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
158 -- 'E': API completed with recoverable errors; explanation on errorstack
159 -- 'U': API completed with UN recoverable errors: error message on error stack
160 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
161 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
162 ) IS
163
164 l_api_name CONSTANT VARCHAR2(30) := 'GET_SCHEDULE_SUMMARY';
165 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
166 l_api_version CONSTANT NUMBER := 1.0;
167 l_Schedule CAC_AVLBLTY_TIME_VARRAY;
168
169 BEGIN
170
171 -- Check version number
172 IF NOT fnd_api.compatible_api_call
173 ( l_api_version
174 , p_api_version
175 , l_api_name
176 , g_pkg_name
177 )
178 THEN
179 RAISE fnd_api.g_exc_unexpected_error;
180 END IF;
181
182 -- Initialize message list
183 IF fnd_api.to_boolean( p_init_msg_list )
184 THEN
185 fnd_msg_pub.initialize;
186 END IF;
187
188 -- Initialize return status to SUCCESS
189 x_return_status := fnd_api.g_ret_sts_success;
190
191 CAC_AVLBLTY_PVT.GET_SCHEDULE_DATA
192 (
193 p_Object_Type => p_Object_Type,
194 p_Object_ID => p_Object_ID,
195 p_Start_Date_Time => p_Start_Date,
196 p_End_Date_Time => p_End_Date,
197 p_Schdl_Cat => p_Schedule_Category,
198 p_Include_Exception => p_Include_Exception,
199 p_Busy_Tentative => p_Busy_Tentative,
200 p_return_type => 'S',
201 x_Schedule => l_Schedule,
202 x_Schedule_Summary => x_Schedule_Summary
203 );
204
205 EXCEPTION
206
207 WHEN fnd_api.g_exc_error
208 THEN
209 x_return_status := fnd_api.g_ret_sts_error;
210 fnd_msg_pub.count_and_get( p_encoded => 'F'
211 , p_count => x_msg_count
212 , p_data => x_msg_data
213 );
214
215 WHEN fnd_api.g_exc_unexpected_error
216 THEN
217 x_return_status := fnd_api.g_ret_sts_unexp_error;
218 fnd_msg_pub.count_and_get( p_encoded => 'F'
219 , p_count => x_msg_count
220 , p_data => x_msg_data
221 );
222
223 WHEN OTHERS
224 THEN
225 --
226 -- Set status
227 --
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229
230 --
231 -- Push message onto CRM stack
232 --
233 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
234 , l_api_name
235 , SQLERRM
236 );
237 --
238 -- Count the messages on the CRM stack
239 --
240 x_msg_count := FND_MSG_PUB.COUNT_MSG;
241
242 END GET_SCHEDULE_SUMMARY;
243
244
245 PROCEDURE IS_AVAILABLE
246 /*****************************************************************************
247 ** Method IS_AVAILABLE
248 **
249 ** Roughly translates to JTF_CALENDAR_PUB_24HR. Is_Res_Available API.
250 ** It will return:
251 ** - 'T' if the resource is available for the given period
252 ** - 'F' if the resource is unavailable for the given period
253 **
254 *******************************************************************************/
255 ( p_api_version IN NUMBER -- API version you coded against
256 , p_init_msg_list IN VARCHAR2 -- Create a new error stack?
257 , p_Object_Type IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
258 , p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
259 , p_Start_Date_Time IN DATE -- start date and time of period of interest
260 , p_End_Date_Time IN DATE -- end date and time of period of interest
261 , p_Schedule_Category IN VARCHAR2 -- Schedule Category of the schedule instance we'll look at
262 , p_Busy_Tentative IN VARCHAR2 -- How to treat periods with FREEBUSYTYPE = BUSY TENTATIVE?
263 -- FREE: BUSY TENTATIVE means FREE
264 -- BUSY: BUSY TENTATIVE means BUSY
265 -- NULL: leave the interpretation to caller
266 , p_task_assignment_id IN NUMBER DEFAULT NULL -- specifies the task assignment id to be ignored while checking availability
267 -- Added by lokumar for bug#6345516
268 , x_Available OUT NOCOPY VARCHAR2 -- 'T' or 'F'
269 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
270 -- 'E': API completed with recoverable errors; explanation on errorstack
271 -- 'U': API completed with UN recoverable errors: error message on error stack
272 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
273 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
274 ) IS
275
276 CURSOR c_tasks
277 (
278 b_ObjectType VARCHAR2,
279 b_ObjectID NUMBER,
280 b_StartDate DATE,
281 b_EndDate DATE,
282 b_BusyTentative VARCHAR2,
283 b_ToTimeZone NUMBER
284 ) IS
285 SELECT GREATEST( CAC_AVLBLTY_PVT.ADJUST_FOR_TIMEZONE( jtb.timezone_id
286 , b_ToTimeZone
287 , jtb.calendar_start_date
288 )
289 , b_StartDate
290 ) StartDateTime
291 , LEAST( CAC_AVLBLTY_PVT.ADJUST_FOR_TIMEZONE( jtb.timezone_id
292 , b_ToTimeZone
293 , jtb.calendar_end_date
294 )
295 , b_EndDate
296 ) EndDateTime
300 ) FBType
297 , DECODE( jta.free_busy_type, 'FREE','FREE'
298 , 'BUSY','BUSY'
299 , 'TENTATIVE',NVL(b_BusyTentative,'TENTATIVE')
301 , jtb.task_type_id CategoryID
302 , jtb.entity CategoryType
303 , jta.task_assignment_id
304 FROM jtf_task_all_assignments jta
305 , jtf_tasks_b jtb
306 , ( SELECT /*+ INDEX(jts JTF_TASK_STATUSES_B_U1) */ jts.task_status_id
307 FROM jtf_task_statuses_b jts
308 WHERE jts.assignment_status_flag = 'Y'
309 AND NVL(jts.closed_flag,'N') = 'N'
310 AND NVL(jts.completed_flag,'N') = 'N'
311 AND NVL(jts.rejected_flag,'N') = 'N'
312 AND NVL(jts.on_hold_flag,'N') = 'N'
313 AND NVL(jts.cancelled_flag,'N') = 'N'
314 ) jto
315 WHERE jta.resource_type_code = b_ObjectType
316 AND jta.resource_id = b_ObjectID
317 AND jta.assignment_status_id = jto.task_status_id
318 AND jta.task_id = jtb.task_id
319 AND jtb.open_flag = 'Y'
320 AND jtb.calendar_end_date >= b_StartDate
321 AND jtb.calendar_start_date <= b_EndDate
322 AND jtb.entity IN ('BOOKING','TASK','APPOINTMENT');
323
324 l_api_name CONSTANT VARCHAR2(30) := 'IS_AVAILABLE';
325 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
326 l_api_version CONSTANT NUMBER := 1.0;
327
328 BEGIN
329
330 -- Check version number
331 IF NOT fnd_api.compatible_api_call
332 ( l_api_version
333 , p_api_version
334 , l_api_name
335 , g_pkg_name
336 )
337 THEN
338 RAISE fnd_api.g_exc_unexpected_error;
339 END IF;
340
341 -- Initialize message list
342 IF fnd_api.to_boolean( p_init_msg_list )
343 THEN
344 fnd_msg_pub.initialize;
345 END IF;
346
347 -- Initialize return status to SUCCESS
348 x_return_status := fnd_api.g_ret_sts_success;
349
350 x_Available := 'T';
351
352 FOR ref_tasks IN c_tasks(p_Object_Type,
353 p_Object_Id,
354 p_Start_Date_Time-1,
355 p_End_Date_Time+1,
356 p_Busy_Tentative,
357 TO_NUMBER(FND_PROFILE.Value('SERVER_TIMEZONE_ID')))
358 LOOP
359 -- If condition modified by lokumar for bug#5752188
360 IF ((p_task_assignment_id is null OR p_task_assignment_id<>ref_tasks.task_assignment_id) AND (ref_tasks.FBType = 'BUSY') AND (NOT (
361 ((p_Start_Date_Time < ref_tasks.StartDateTime) AND
362 (p_End_Date_Time <= ref_tasks.StartDateTime)) OR
363 ((p_Start_Date_Time >= ref_tasks.EndDateTime) AND
364 (p_End_Date_Time > ref_tasks.EndDateTime)))))
365 THEN
366 x_Available := 'F';
367 EXIT;
368 END IF;
369 END LOOP;
370
371 EXCEPTION
372
373 WHEN fnd_api.g_exc_unexpected_error
374 THEN
375 x_return_status := fnd_api.g_ret_sts_unexp_error;
376 fnd_msg_pub.count_and_get( p_encoded => 'F'
377 , p_count => x_msg_count
378 , p_data => x_msg_data
379 );
380
381 WHEN OTHERS
382 THEN
383 --
384 -- Set status
385 --
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387
388 --
389 -- Push message onto CRM stack
390 --
391 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
392 , l_api_name
393 , SQLERRM
394 );
395 --
396 -- Count the messages on the CRM stack
397 --
398 x_msg_count := FND_MSG_PUB.COUNT_MSG;
399
400 END IS_AVAILABLE;
401
402
403 END CAC_AVLBLTY_PUB;
[Home] [Help]