1 PACKAGE JTF_CALENDAR_PUB AUTHID CURRENT_USER AS
2 /* $Header: jtfclavs.pls 120.3.12010000.2 2009/08/26 11:07:01 rkamasam ship $ */
3 /*#
4 * Joint Task Force core Calendar Public API's.
5 * This package is for finding the availability and working shift hours of a particular resource
6 * during a specified period.
7 * @rep:scope internal
8 * @rep:product CAC
9 * @rep:displayname JTF Calendar Public API
10 * @rep:lifecycle active
11 * @rep:compatibility S
12 * @rep:category BUSINESS_ENTITY CAC_SCHEDULE
13 */
14
15 -- ************************************************************************
16 -- Start of Comments
17 -- Package Name : JTF_CLAENDAR_PUB
18 -- Purpose : Joint Task Force core Calendar Public API's
19 -- This package is for finding the availability,
20 -- working shift hours of a particular resource
21 -- during a specified period
22 -- Procedures : (See below for specification)
23 -- Notes : This package is publicly available for use
24 -- History : 09/29/99 VMOVVA created
25 -- 03/05/02 JAWANG modified
26 -- changed Shift_Rec_effective_dates_Type
27 -- to Shift_Rec_Attributes_Type
28 -- 08/11/03 ABRAINA Added ResourceDt_To_ServerDT
29 -- End of Comments
30 -- ************************************************************************
31 TYPE Shift_Rec_Type IS RECORD
32 ( shift_construct_id NUMBER ,
33 shift_date DATE,
34 start_time VARCHAR2(5),
35 end_time VARCHAR2(5),
36 availability_type VARCHAR2(40)
37 );
38
39 TYPE Shift_Tbl_Type IS TABLE OF Shift_Rec_Type
40 INDEX BY BINARY_INTEGER;
41
42 -- Added by Jane Wang on 03/05/2002
43 -- To return attribute1 - attribute15 in Get_Resource_Shifts API.
44 TYPE Shift_Rec_Attributes_Type IS RECORD
45 ( shift_construct_id NUMBER ,
46 shift_date DATE,
47 start_time VARCHAR2(5),
48 end_time VARCHAR2(5),
49 availability_type VARCHAR2(40),
50 attribute1 VARCHAR2(150),
51 attribute2 VARCHAR2(150),
52 attribute3 VARCHAR2(150),
53 attribute4 VARCHAR2(150),
54 attribute5 VARCHAR2(150),
55 attribute6 VARCHAR2(150),
56 attribute7 VARCHAR2(150),
57 attribute8 VARCHAR2(150),
58 attribute9 VARCHAR2(150),
59 attribute10 VARCHAR2(150),
60 attribute11 VARCHAR2(150),
61 attribute12 VARCHAR2(150),
62 attribute13 VARCHAR2(150),
63 attribute14 VARCHAR2(150),
64 attribute15 VARCHAR2(150)
65
66 );
67
68 TYPE Shift_Tbl_Attributes_Type IS TABLE OF Shift_Rec_Attributes_Type
69 INDEX BY BINARY_INTEGER;
70
71 -- ************************************************************************
72 -- Start of comments
73 -- API name : Is_Res_Available
74 -- Type : Public.
75 -- Function : Determine whether the given resource is
76 -- available during the specified period.
77 -- Pre-reqs : None.
78 -- Parameters :
79 -- IN : p_api_version IN NUMBER Required
80 -- p_init_msg_list IN VARCHAR2 Optional
81 -- Default = FND_API.G_FALSE
82 -- p_resource_id IN NUMBER Required
83 -- p_resource_type IN VARCHAR2 Required
84 -- p_start_date_time IN DATE
85 -- p_duration IN NUMBER Required
86 -- OUT : x_return_status OUT VARCHAR2(1)
87 -- x_msg_count OUT NUMBER
88 -- x_msg_data OUT VARCHAR2(2000)
89 -- x_avail OUT VARCHAR2
90 -- Version : Current version 1.0
91 -- Initial version 1.0
92 --
93 -- Notes :
94 --
95 -- End of comments
96 -- ************************************************************************
97 /*#
98 * Determine whether the given resource is available during the specified period.
99 * @param p_api_version API Version
100 * @param p_init_msg_list Initialize message list flag
101 * @param p_resource_id Search for Resource Id
102 * @param p_resource_type Resource Type
103 * @param p_start_date_time Search Start DateTime
104 * @param p_duration Search Duration
105 * @param x_return_status API return status flag
106 * @param x_msg_count API Error message count
107 * @param x_msg_data API Error message data
108 * @param x_avail Resource availabilty confirmation flag
109 * @rep:scope internal
110 * @rep:lifecycle active
111 * @rep:displayname Is Resource Available
112 */
113 PROCEDURE Is_Res_Available
114 ( p_api_version IN NUMBER ,
115 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE ,
116 p_resource_id IN NUMBER ,
117 p_resource_type IN VARCHAR2 ,
118 p_start_date_time IN DATE ,
119 p_duration IN NUMBER ,
120 x_return_status OUT NOCOPY VARCHAR2 ,
121 x_msg_count OUT NOCOPY NUMBER ,
122 x_msg_data OUT NOCOPY VARCHAR2 ,
123 x_avail OUT NOCOPY varchar2
124 );
125 -- ************************************************************************
126 -- Start of comments
127 -- API name : Get_Available Time
128 -- Type : Public.
129 -- Function : Get availability (Working Hours - Exception Hours
130 -- - Task Assignment Hours) of a resource during the
131 -- specified period
132 -- Pre-reqs : None.
133 -- Parameters :
134 -- IN : p_api_version IN NUMBER Required
135 -- p_init_msg_list IN VARCHAR2 Optional
136 -- Default = FND_API.G_FALSE
137 -- p_resource_id IN NUMBER Required
138 -- p_resource_type IN VARCHAR2 Required
139 -- p_start_date IN DATE
140 -- p_end_date IN DATE
141 -- OUT : x_return_status OUT VARCHAR2(1)
142 -- x_msg_count OUT NUMBER
143 -- x_msg_data OUT VARCHAR2(2000)
144 -- x_shift OUT SHIFT_TBL_TYPE
145 -- Version : Current version 1.0
146 -- Initial version 1.0
147 --
148 -- Notes :
149 --
150 -- End of comments
151 -- ************************************************************************
152 /*#
153 * Get availabile time gets (Working Hours - Exception Hours - Task Assignment Hours)
154 * of a resource during the specified period
155 * @param p_api_version API Version
156 * @param p_init_msg_list Initialize message list flag
157 * @param p_resource_id Search for Resource Id
158 * @param p_resource_type Resource Type
159 * @param p_start_date Search Start DateTime
160 * @param p_end_date Search End DateTime
161 * @param x_return_status API return status flag
162 * @param x_msg_count API Error message count
163 * @param x_msg_data API Error message data
164 * @param x_shift Shift returned for a given resource
165 * @rep:scope internal
166 * @rep:lifecycle active
167 * @rep:displayname Get Available Time
168 */
169 PROCEDURE Get_Available_Time
170 ( p_api_version IN NUMBER ,
171 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
172 p_resource_id IN NUMBER ,
173 p_resource_type IN VARCHAR2 ,
174 p_start_date IN DATE ,
175 p_end_date IN DATE ,
176 x_return_status OUT NOCOPY VARCHAR2 ,
177 x_msg_count OUT NOCOPY NUMBER ,
178 x_msg_data OUT NOCOPY VARCHAR2 ,
179 x_shift OUT NOCOPY SHIFT_TBL_TYPE
180 );
181 -- ************************************************************************
182 -- Start of comments
183 -- API name : Get_Available_Slot
184 -- Type : Public.
185 -- Function : Get available time slot of requested duration for
186 -- the given resource during the specified period.
187 -- Pre-reqs : None.
188 -- Parameters :
189 -- IN : p_api_version IN NUMBER Required
190 -- p_init_msg_list IN VARCHAR2 Optional
191 -- Default = FND_API.G_FALSE
192 -- p_resource_id IN NUMBER Required
193 -- p_resource_type IN VARCHAR2 Required
194 -- p_start_date_time IN DATE
195 -- p_end_date_time IN DATE
196 -- p_duration IN NUMBER Required
197 -- OUT : x_return_status OUT VARCHAR2(1)
198 -- x_msg_count OUT NUMBER
199 -- x_msg_data OUT VARCHAR2(2000)
200 -- x_slot_start_date OUT DATE
201 -- x_slot_end_date OUT DATE
202 -- x_shift_construct_id OUT NUMBER
203 -- x_availability_type OUT VARCHAR2
204 -- Version : Current version 1.0
205 -- Initial version 1.0
206 --
207 -- Notes :
208 --
209 -- End of comments
210 -- ************************************************************************
211 /*#
212 * Get available time slot of requested duration for the given resource during
213 * the specified period.
214 * @param p_api_version API Version
215 * @param p_init_msg_list Initialize message list flag
216 * @param p_resource_id Search for Resource Id
217 * @param p_resource_type Resource Type
218 * @param p_start_date_time Search Start DateTime
219 * @param p_end_date_time Search End DateTime
220 * @param p_duration Search duration
221 * @param x_return_status API return status flag
222 * @param x_msg_count API Error message count
223 * @param x_msg_data API Error message data
224 * @param x_slot_start_date Returned slot start DateTime
225 * @param x_slot_end_date Returned slot end DateTime
226 * @param x_shift_construct_id Returned shift construct ID
227 * @param x_availability_type Returned availabilty type
228 * @rep:scope internal
229 * @rep:lifecycle active
230 * @rep:displayname Get Available Slot
231 */
232 PROCEDURE Get_Available_Slot
233 ( p_api_version IN NUMBER ,
234 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
235 p_resource_id IN NUMBER ,
236 p_resource_type IN VARCHAR2 ,
237 p_start_date_time IN DATE ,
238 p_end_date_time IN DATE ,
239 p_duration IN NUMBER ,
240 x_return_status OUT NOCOPY VARCHAR2 ,
241 x_msg_count OUT NOCOPY NUMBER ,
242 x_msg_data OUT NOCOPY VARCHAR2 ,
243 x_slot_start_date OUT NOCOPY DATE ,
244 x_slot_end_date OUT NOCOPY DATE ,
245 x_shift_construct_id OUT NOCOPY NUMBER ,
246 x_availability_type OUT NOCOPY VARCHAR2
247 );
248 -- ************************************************************************
249 -- Start of comments
250 -- API name : Get_Resource_Shifts
251 -- Type : Public.
252 -- Function : Get the availability (Working Hours - Exception Hours)
253 -- of a resource during the specified period
254 -- Pre-reqs : None.
255 -- Parameters :
256 -- IN : p_api_version IN NUMBER Required
257 -- p_init_msg_list IN VARCHAR2 Optional
258 -- Default = FND_API.G_FALSE
259 -- p_resource_id IN NUMBER Required
260 -- p_resource_type IN VARCHAR2 Required
261 -- p_start_date IN DATE
262 -- p_end_date IN DATE
263 -- OUT : x_return_status OUT VARCHAR2(1)
264 -- x_msg_count OUT NUMBER
265 -- x_msg_data OUT VARCHAR2(2000)
266 -- x_shift OUT SHIFT_TBL_TYPE
267 -- Version : Current version 1.0
268 -- Initial version 1.0
269 --
270 -- Notes :
271 --
272 -- End of comments
273
274 -- ************************************************************************
275
276 PROCEDURE Get_Resource_Shifts
277 ( p_api_version IN NUMBER ,
278 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
279 p_resource_id IN NUMBER ,
280 p_resource_type IN VARCHAR2 ,
281 p_start_date IN DATE ,
282 p_end_date IN DATE ,
283 x_return_status OUT NOCOPY VARCHAR2 ,
284 x_msg_count OUT NOCOPY NUMBER ,
285 x_msg_data OUT NOCOPY VARCHAR2 ,
286 x_shift OUT NOCOPY SHIFT_TBL_TYPE
287 );
288
289 -- ************************************************************************
290 -- Start of comments
291 -- API name : Get_Resource_Shifts
292 -- Type : Public.
293 -- Function : Get the availability (Working Hours - Exception Hours)
294 -- of a resource during the specified period
295 -- Pre-reqs : None.
296 -- Parameters :
297 -- IN : p_api_version IN NUMBER Required
298 -- p_init_msg_list IN VARCHAR2 Optional
299 -- Default = FND_API.G_FALSE
300 -- p_resource_id IN NUMBER Required
301 -- p_resource_type IN VARCHAR2 Required
302 -- p_start_date IN DATE
303 -- p_end_date IN DATE
304 -- OUT : x_return_status OUT VARCHAR2(1)
305 -- x_msg_count OUT NUMBER
306 -- x_msg_data OUT VARCHAR2(2000)
307 -- x_shift OUT SHIFT_TBL_ATTRIBUTES_TYPE
308 -- Version : Current version 1.0
309 -- Initial version 1.0
310 --
311 -- Notes :
312 --
313 -- End of comments
314
315 -- ************************************************************************
316 /*#
317 * Get the availability (Working Hours - Exception Hours) of a resource during
318 * the specified period
319 * @param p_api_version API Version
320 * @param p_init_msg_list Initialize message list flag
321 * @param p_resource_id Search for Resource Id
322 * @param p_resource_type Resource Type
323 * @param p_start_date Search Start DateTime
324 * @param p_end_date Search End DateTime
325 * @param x_return_status API return status flag
326 * @param x_msg_count API Error message count
327 * @param x_msg_data API Error message data
328 * @param x_shift Shift returned with attributes for a given resource
329 * @rep:scope internal
330 * @rep:lifecycle active
331 * @rep:displayname Get Resource Shift with attributes
332 */
333 PROCEDURE Get_Resource_Shifts
334 ( p_api_version IN NUMBER ,
335 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
336 p_resource_id IN NUMBER ,
337 p_resource_type IN VARCHAR2 ,
338 p_start_date IN DATE ,
339 p_end_date IN DATE ,
340 x_return_status OUT NOCOPY VARCHAR2 ,
341 x_msg_count OUT NOCOPY NUMBER ,
342 x_msg_data OUT NOCOPY VARCHAR2 ,
343 x_shift OUT NOCOPY SHIFT_TBL_ATTRIBUTES_TYPE
344 );
345
346 -- ************************************************************************
347 -- Start of comments
348 -- API name : Get_Res_Schedule
349 -- Type : Public.
350 -- Function : Get the working, exception, assigned Task hours for
351 -- a given Resource in the given date range.
352 -- Pre-reqs : None.
353 -- Parameters :
357 -- p_resource_id IN NUMBER Required
354 -- IN : p_api_version IN NUMBER Required
355 -- p_init_msg_list IN VARCHAR2 Optional
356 -- Default = FND_API.G_FALSE
358 -- p_resource_type IN VARCHAR2 Required
359 -- p_start_date IN DATE
360 -- p_end_date IN DATE
361 -- p_duration IN NUMBER Required
362 -- OUT : x_return_status OUT VARCHAR2(1)
363 -- x_msg_count OUT NUMBER
364 -- x_msg_data OUT VARCHAR2(2000)
365 -- x_shift OUT SHIFT_TBL_TYPE
366 -- Version : Current version 1.0
367 -- Initial version 1.0
368 --
369 -- Notes :
370 --
371 -- End of comments
372
373
374
375 -- ************************************************************************
376 /*#
377 * Get the working, exception, assigned Task hours for a given Resource in the
378 * given date range.
379 * @param p_api_version API Version
380 * @param p_init_msg_list Initialize message list flag
381 * @param p_resource_id Search for Resource Id
382 * @param p_resource_type Resource Type
383 * @param p_start_date Search Start DateTime
384 * @param p_end_date Search End DateTime
385 * @param x_return_status API return status flag
386 * @param x_msg_count API Error message count
387 * @param x_msg_data API Error message data
388 * @param x_shift Shift returned for a given resource
389 * @rep:scope internal
390 * @rep:lifecycle active
391 * @rep:displayname Get Resource Schedule
392 */
393 PROCEDURE Get_Res_Schedule
394 ( p_api_version IN NUMBER ,
395 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
396 p_resource_id IN NUMBER ,
397 p_resource_type IN VARCHAR2 ,
398 p_start_date IN DATE ,
399 p_end_date IN DATE ,
400 x_return_status OUT NOCOPY VARCHAR2 ,
401 x_msg_count OUT NOCOPY NUMBER ,
402 x_msg_data OUT NOCOPY VARCHAR2 ,
403 x_shift OUT NOCOPY SHIFT_TBL_TYPE
404 );
405
406 -- ************************************************************************
407 -- Start of comments
408 -- API name : get_g_false
409 -- Type : Public.
410 -- Function : Used as a wrapper function to get FND_API.G_FALSE.
411 -- Pre-reqs : None.
412 -- Parameters :
413 -- IN : None.
414 -- OUT : None.
415 -- RETURN : FND_API.G_FALSE VARCHAR2
416 -- Version : Current version 1.0
417 -- Initial version 1.0
418 --
419 -- Notes :
420 --
421 -- End of comments
422 -- ************************************************************************
423
424 FUNCTION get_g_false return varchar2;
425
426 -- ************************************************************************
427 -- Start of comments
428 -- API name : get_g_miss_num
429 -- Type : Public.
430 -- Function : Used as a wrapper function to get FND_API.G_MISS_NUM.
431 -- Pre-reqs : None.
432 -- Parameters :
433 -- IN : None.
434 -- OUT : None.
435 -- RETURN : FND_API.G_MISS_NUM NUMBER
436 -- Version : Current version 1.0
437 -- Initial version 1.0
438 --
439 -- Notes :
440 --
441 -- End of comments
442 -- ************************************************************************
443
444 FUNCTION get_g_miss_num return number;
445
446 -- ************************************************************************
447 -- Start of comments
448 -- API name : get_g_miss_char
449 -- Type : Public.
450 -- Function : Used as a wrapper function to get FND_API.G_MISS_CHAR.
451 -- Pre-reqs : None.
452 -- Parameters :
453 -- IN : None.
454 -- OUT : None.
455 -- RETURN : FND_API.G_MISS_CHAR VARCHAR2
456 -- Version : Current version 1.0
457 -- Initial version 1.0
458 --
459 -- Notes :
460 --
461 -- End of comments
462 -- ************************************************************************
463
464 FUNCTION get_g_miss_char return varchar2;
465
466 -- ************************************************************************
467 -- Start of comments
468 -- API name : get_g_miss_date
469 -- Type : Public.
470 -- Function : Used as a wrapper function to get FND_API.G_MISS_DATE.
471 -- Pre-reqs : None.
472 -- Parameters :
473 -- IN : None.
474 -- OUT : None.
475 -- RETURN : FND_API.G_MISS_DATE DATE
476 -- Version : Current version 1.0
477 -- Initial version 1.0
478 --
479 -- Notes :
480 --
481 -- End of comments
482 -- ************************************************************************
483
484 FUNCTION get_g_miss_date return date;
485
486 -- ************************************************************************
487 -- Start of comments
488 -- API name : ResourceDt_To_ServerDT
489 -- Type : Public.
490 -- Pre-reqs : None.
491 -- Parameters :
492 -- IN : None.
493 -- OUT : None.
494 -- RETURN : FND_API.G_MISS_DATE DATE
495 -- Version : Current version 1.0
496 -- Initial version 1.0
497 --
498 -- Notes : Create Function for timezone conversion form
499 -- resource timezone to server timezone.
500 -- Added for Simplex Timezone Enh # 3034073 by ABRAINA
501 --
502 -- End of comments
506 Function Get_Res_Timezone_Id ( P_Resource_Id IN Number , p_resource_type IN VARCHAR2 ) RETURN Number ;
503 -- ************************************************************************
504
505 Function ResourceDt_To_ServerDT ( P_Resource_DtTime IN date, P_Resource_TZ_Id IN Number , p_Server_TZ_id IN Number ) RETURN date ;
507 Function Validate_Cal_Date ( P_Calendar_Id IN Number, P_shift_date IN Date ) RETURN Boolean ;
508 END;
[Home] [Help]