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