1 PACKAGE BODY CAC_AVLBLTY_PVT AS
2 /* $Header: caccapb.pls 120.7.12010000.7 2009/01/21 11:57:23 anangupt ship $ */
3
4 /*******************************************************************************
5 ** Private APIs
6 *******************************************************************************/
7
8 -- finish get_shift_bands method, use caching of objects
9 -- add sort for tasks/appointments in case of timezone conversion
10
11 TYPE PERIOD_REC_TYPE IS RECORD
12 ( template_detail_id NUMBER
13 , period_id NUMBER
14 , period_span_ms NUMBER
15 , day_num NUMBER
16 , day_start_ms NUMBER
17 );
18
19 TYPE PERIOD_TBL_TYPE IS TABLE OF PERIOD_REC_TYPE
20 INDEX BY BINARY_INTEGER;
21
22 TYPE SCHDL_DETAILS_REC_TYPE IS RECORD
23 ( period_id NUMBER
24 , start_date_time DATE
25 , end_date_time DATE
26 );
27
28 TYPE SCHDL_DETAILS_TBL_TYPE IS TABLE OF SCHDL_DETAILS_REC_TYPE
29 INDEX BY BINARY_INTEGER;
30
31
32 FUNCTION ADJUST_FOR_TIMEZONE
33 ( p_source_tz_id IN NUMBER
34 , p_dest_tz_id IN NUMBER
35 , p_source_day_time IN DATE
36 )RETURN DATE
37 IS
38
39 l_dest_day_time DATE;
40 l_return_status VARCHAR2(1);
41 l_msg_count NUMBER;
42 l_msg_data VARCHAR2(2000);
43
44 BEGIN
45 IF (p_source_day_time IS NOT NULL)
46 THEN
47 --
48 -- Only adjust if the timezones are different and not NULL
49 --
50 IF ( (p_Source_tz_id IS NOT NULL)
51 AND (p_Source_tz_id <> p_dest_tz_id)
52 )
53 THEN
54 --
55 -- Call the API to get the adjusted date (this API is slow..)
56 --
57 HZ_TIMEZONE_PUB.Get_Time( p_api_version => 1.0
58 , p_init_msg_list => FND_API.G_FALSE
59 , p_source_tz_id => p_Source_tz_id
60 , p_dest_tz_id => p_dest_tz_id
61 , p_source_day_time => p_source_day_time
62 , x_dest_day_time => l_dest_day_time
63 , x_return_status => l_return_status
64 , x_msg_count => l_msg_count
65 , x_msg_data => l_msg_data
66 );
67
68 RETURN l_dest_day_time;
69
70 ELSE
71 RETURN p_source_day_time;
72
73 END IF;
74 ELSE
75 RETURN p_source_day_time;
76
77 END IF;
78
79 END ADJUST_FOR_TIMEZONE;
80
81
82 FUNCTION CONVERT_TO_MILLIS
83 /*******************************************************************************
84 ** CONVERT_TO_MILLIS
85 **
86 ** Will return the period + UOM in day so it can be added to an Oracle DATE.
87 *******************************************************************************/
88 ( p_Duration IN NUMBER
89 , p_UOM IN VARCHAR2
90 )RETURN NUMBER
91
92 IS
93
94 BEGIN
95 IF (p_UOM = 'MIN')
96 THEN
97 RETURN (p_Duration*60*1000);
98 ELSIF (p_UOM = 'HR')
99 THEN
100 RETURN (p_Duration*3600*1000);
101 ELSIF (p_UOM = 'DAY')
102 THEN
103 RETURN (p_Duration*24*3600*1000);
104 ELSIF (p_UOM = 'WK')
105 THEN
106 RETURN (p_Duration*7*24*3600*1000);
107 ELSE
108 RETURN NULL;
109 END IF;
110 END CONVERT_TO_MILLIS;
111
112
113 FUNCTION GET_SHIFT_BANDS
114 /*******************************************************************************
115 ** GET_SHIFT_BANDS
116 **
117 ** Will create a new CAC_AVLBLTY_TIME_BAND_VARRAY collection.
118 *******************************************************************************/
119 ( p_period_id IN NUMBER
120 ) RETURN CAC_AVLBLTY_TIME_BAND_VARRAY IS
121
122 l_shift_bands CAC_AVLBLTY_TIME_BAND_VARRAY;
123
124 BEGIN
125
126 l_shift_bands := CAC_AVLBLTY_TIME_BAND_VARRAY();
127
128 RETURN l_shift_bands;
129
130 END GET_SHIFT_BANDS;
131
132 PROCEDURE CREATE_AVLBLTY_SUMMARY
133 /*******************************************************************************
134 ** CREATE_AVLBLTY_SUMMARY
135 **
136 ** Will create a new object in the CAC_AVLBLTY_SUMMARY_VARRAY collection.
137 *******************************************************************************/
138 ( p_blank_record IN BOOLEAN
139 , p_start_dt IN DATE
140 , p_end_dt IN DATE
141 , p_category_id IN NUMBER
142 , p_category_name IN VARCHAR2
143 , p_free_busy IN VARCHAR2
144 , p_display_color IN VARCHAR2
145 , x_avlblty_summary IN OUT NOCOPY CAC_AVLBLTY_SUMMARY_VARRAY
146 , x_index IN OUT NOCOPY NUMBER
147 ) IS
148
149 l_summary_date DATE;
150 l_detail_v CAC_AVLBLTY_DETAIL_VARRAY;
151 l_daytime_v CAC_AVLBLTY_DAY_TIME_VARRAY;
152 l_found_index BOOLEAN;
153 l_last_index NUMBER;
154 l_end_dt DATE;
155 l_dt DATE;
156
157 BEGIN
158
159 IF ((p_blank_record) OR (p_category_id IS NULL))
160 THEN
161 RETURN;
162 END IF;
163
164 IF (x_index IS NULL)
165 THEN
166 x_index := 0;
167 ELSE
168 l_summary_date := x_avlblty_summary(x_index).SUMMARY_DATE;
169 END IF;
170
171 IF (TRUNC(p_end_dt) > TRUNC(p_start_dt))
172 THEN
173 l_end_dt := TRUNC(p_start_dt) + 1;
174 ELSE
175 l_end_dt := p_end_dt;
176 END IF;
177
178 IF (TRUNC(p_start_dt) = l_summary_date)
179 THEN
180 l_found_index := false;
181 l_detail_v := x_avlblty_summary(x_index).SUMMARY_LINES;
182 FOR i in l_detail_v.first..l_detail_v.last
183 LOOP
184 IF (l_detail_v(i).PERIOD_CATEGORY_ID = p_category_id)
185 THEN
186 l_found_index := true;
187 l_daytime_v := l_detail_v(i).DAY_TIMES;
188 l_last_index := l_daytime_v.COUNT;
189 IF (l_daytime_v(l_last_index).END_DATE_TIME = p_start_dt)
190 THEN
191 l_daytime_v(l_last_index).END_DATE_TIME := l_end_dt;
192 ELSE
193 l_daytime_v.EXTEND(1);
194 l_daytime_v(l_last_index+1) := CAC_AVLBLTY_DAY_TIME
195 (
196 START_DATE_TIME => p_start_dt,
197 END_DATE_TIME => l_end_dt
198 );
199 END IF;
200 l_detail_v(i).TOTAL_TIME_MS := l_detail_v(i).TOTAL_TIME_MS + (l_end_dt-p_start_dt)*24*3600*1000;
201 l_detail_v(i).DAY_TIMES := l_daytime_v;
202 EXIT;
203 END IF;
204 END LOOP;
205 IF (NOT l_found_index)
206 THEN
207 l_last_index := l_detail_v.COUNT;
208 l_daytime_v := CAC_AVLBLTY_DAY_TIME_VARRAY();
209 l_daytime_v.EXTEND(1);
210 l_daytime_v(1) := CAC_AVLBLTY_DAY_TIME
211 (
212 START_DATE_TIME => p_start_dt,
213 END_DATE_TIME => l_end_dt
214 );
215 l_detail_v.EXTEND(1);
216 l_detail_v(l_last_index+1) := CAC_AVLBLTY_DETAIL
217 (
218 TOTAL_TIME_MS => (l_end_dt-p_start_dt)*24*3600*1000,
219 PERIOD_CATEGORY_ID => p_category_id,
220 PERIOD_CATEGORY_NAME => p_category_name,
221 FREE_BUSY_TYPE => p_free_busy,
222 DISPLAY_COLOR => p_display_color,
223 DAY_TIMES => l_daytime_v
224 );
225 END IF;
226 x_avlblty_summary(x_index).SUMMARY_LINES := l_detail_v;
227 ELSE
228 x_index := x_index + 1;
229 l_daytime_v := CAC_AVLBLTY_DAY_TIME_VARRAY();
230 l_daytime_v.EXTEND(1);
231 l_daytime_v(1) := CAC_AVLBLTY_DAY_TIME
232 (
233 START_DATE_TIME => p_start_dt,
234 END_DATE_TIME => l_end_dt
235 );
236 l_detail_v := CAC_AVLBLTY_DETAIL_VARRAY();
237 l_detail_v.EXTEND(1);
238 l_detail_v(1) := CAC_AVLBLTY_DETAIL
239 (
240 TOTAL_TIME_MS => (l_end_dt-p_start_dt)*24*3600*1000,
241 PERIOD_CATEGORY_ID => p_category_id,
242 PERIOD_CATEGORY_NAME => p_category_name,
243 FREE_BUSY_TYPE => p_free_busy,
244 DISPLAY_COLOR => p_display_color,
245 DAY_TIMES => l_daytime_v
246 );
247 x_avlblty_summary.EXTEND(1);
248 x_avlblty_summary(x_index) := CAC_AVLBLTY_SUMMARY
249 (
250 SUMMARY_DATE => TRUNC(p_start_dt),
251 SUMMARY_LINES => l_detail_v
252 );
253 END IF;
254
255 -- loop by incrementing a day
256 l_dt := TRUNC(p_start_dt) + 1;
257 WHILE (p_end_dt > l_dt)
258 LOOP
259 l_end_dt := l_dt + 1;
260 IF (l_end_dt > p_end_dt)
261 THEN
262 l_end_dt := p_end_dt;
263 END IF;
264 x_index := x_index + 1;
265 l_daytime_v := CAC_AVLBLTY_DAY_TIME_VARRAY();
266 l_daytime_v.EXTEND(1);
267 l_daytime_v(1) := CAC_AVLBLTY_DAY_TIME
268 (
269 START_DATE_TIME => l_dt,
270 END_DATE_TIME => l_end_dt
271 );
272 l_detail_v := CAC_AVLBLTY_DETAIL_VARRAY();
273 l_detail_v.EXTEND(1);
274 l_detail_v(1) := CAC_AVLBLTY_DETAIL
275 (
276 TOTAL_TIME_MS => (l_end_dt-l_dt)*24*3600*1000,
277 PERIOD_CATEGORY_ID => p_category_id,
278 PERIOD_CATEGORY_NAME => p_category_name,
279 FREE_BUSY_TYPE => p_free_busy,
280 DISPLAY_COLOR => p_display_color,
281 DAY_TIMES => l_daytime_v
282 );
283 x_avlblty_summary.EXTEND(1);
284 x_avlblty_summary(x_index) := CAC_AVLBLTY_SUMMARY
285 (
286 SUMMARY_DATE => TRUNC(l_dt),
287 SUMMARY_LINES => l_detail_v
288 );
289 l_dt := l_dt + 1;
290 END LOOP;
291
292 END CREATE_AVLBLTY_SUMMARY;
293
294 PROCEDURE CREATE_AVLBLTY_TIME
295 /*******************************************************************************
296 ** CREATE_AVLBLTY_TIME
297 **
298 ** Will create a new object in the CAC_AVLBLTY_TIME_VARRAY collection.
299 *******************************************************************************/
300 ( p_blank_record IN BOOLEAN
301 , p_period_name IN VARCHAR2
302 , p_start_dt IN DATE
303 , p_end_dt IN DATE
304 , p_duration IN NUMBER
305 , p_category_id IN NUMBER
306 , p_category_name IN VARCHAR2
307 , p_free_busy IN VARCHAR2
308 , p_display_color IN VARCHAR2
309 , p_update_next IN BOOLEAN
310 , p_shift_bands IN CAC_AVLBLTY_TIME_BAND_VARRAY
311 , x_avlblty_time IN OUT NOCOPY CAC_AVLBLTY_TIME_VARRAY
312 , x_index IN OUT NOCOPY NUMBER
313 ) IS
314
315 l_start_dt DATE;
316
317 BEGIN
318 IF (x_index IS NULL)
319 THEN
320 x_index := 1;
321 ELSE
322 IF (p_update_next)
323 THEN
324 -- see if the previous one should be merged with the current one
325 IF (((NOT p_blank_record) AND (x_avlblty_time(x_index).PERIOD_CATEGORY_ID =
326 p_category_id)) OR (p_blank_record AND
327 x_avlblty_time(x_index).PERIOD_CATEGORY_ID IS NULL))
328 THEN
329 x_avlblty_time(x_index).END_DATE_TIME := p_end_dt;
330 x_avlblty_time(x_index).DURATION_MS := x_avlblty_time(x_index).DURATION_MS +
331 (p_end_dt-p_start_dt)*24*3600*1000;
332 RETURN;
333 END IF;
334 x_avlblty_time(x_index).NEXT_OBJECT_INDEX := x_index + 1;
335 /*IF (x_avlblty_time(x_index).end_date_time < p_start_dt)
336 THEN
337 l_start_dt := x_avlblty_time(x_index).end_date_time;
338 x_index := x_index + 1;
339 x_avlblty_time.EXTEND(1);
340 x_avlblty_time(x_index) := CAC_AVLBLTY_TIME
341 (
342 PERIOD_NAME => NULL,
343 START_DATE_TIME => l_start_dt,
344 END_DATE_TIME => p_start_dt,
345 DURATION_MS => (p_start_dt-l_start_dt)*24*3600*1000,
346 PERIOD_CATEGORY_ID => NULL,
347 PERIOD_CATEGORY_NAME => NULL,
348 FREE_BUSY_TYPE => NULL,
349 DISPLAY_COLOR => NULL,
350 SUPER_OBJECT_INDEX => NULL,
351 SHIFT_BANDS => NULL,
352 NEXT_OBJECT_INDEX => x_index+1
353 );
354 END IF;*/
355 END IF;
356 x_index := x_index + 1;
357 END IF;
358 x_avlblty_time.EXTEND(1);
359
360 IF (p_blank_record)
361 THEN
362 x_avlblty_time(x_index) := CAC_AVLBLTY_TIME
363 (
364 PERIOD_NAME => NULL,
365 START_DATE_TIME => p_start_dt,
366 END_DATE_TIME => p_end_dt,
367 DURATION_MS => (p_end_dt-p_start_dt)*24*3600*1000,
368 PERIOD_CATEGORY_ID => NULL,
369 PERIOD_CATEGORY_NAME => NULL,
370 FREE_BUSY_TYPE => NULL,
371 DISPLAY_COLOR => NULL,
372 SUPER_OBJECT_INDEX => NULL,
373 SHIFT_BANDS => NULL,
374 NEXT_OBJECT_INDEX => NULL
375 );
376 ELSE
377 x_avlblty_time(x_index) := CAC_AVLBLTY_TIME
378 (
379 PERIOD_NAME => p_period_name,
380 START_DATE_TIME => p_start_dt,
381 END_DATE_TIME => p_end_dt,
382 DURATION_MS => p_duration,
383 PERIOD_CATEGORY_ID => p_category_id,
384 PERIOD_CATEGORY_NAME => p_category_name,
385 FREE_BUSY_TYPE => p_free_busy,
386 DISPLAY_COLOR => p_display_color,
387 SUPER_OBJECT_INDEX => NULL,
388 SHIFT_BANDS => p_shift_bands,
389 NEXT_OBJECT_INDEX => NULL
390 );
391 END IF;
392
393 END CREATE_AVLBLTY_TIME;
394
395
396 PROCEDURE GET_SCHEDULE_DATA
397 /*******************************************************************************
398 **
399 ** GET_SCHEDULE_DATA
400 **
401 ** returns the schedule for the given:
402 ** - Object Instance
403 ** - Schedule Type
404 ** - Period
405 **
406 *******************************************************************************/
407 ( p_Object_Type IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
408 , p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
409 , p_Start_Date_Time IN DATE -- start date and time of period of interest
410 , p_End_Date_Time IN DATE -- end date and time of period of interest
411 , p_Schdl_Cat IN VARCHAR2 -- Schedule Category of the schedule instance we'll look at
412 , p_Include_Exception IN VARCHAR2 -- 'T' or 'F' depending on whether the exceptions be included or not
413 , p_Busy_Tentative IN VARCHAR2 -- How to treat periods with FREEBUSYTYPE = BUSY TENTATIVE?
414 -- FREE: BUSY TENTATIVE means FREE
415 -- BUSY: BUSY TENTATIVE means BUSY
416 -- NULL: leave the interpretation to caller
417 , p_return_type IN VARCHAR2 -- 'D': Detailed schedule
418 -- 'S': Schedule Summary
419 -- 'B': Both detailed and summary of schedule
420 , x_Schedule OUT NOCOPY CAC_AVLBLTY_TIME_VARRAY
421 , x_Schedule_Summary OUT NOCOPY CAC_AVLBLTY_SUMMARY_VARRAY
422 -- return schedule
423 )
424 IS
425
426 CURSOR C_SCHEDULE
427 (
428 b_schdl_cat VARCHAR2,
429 b_object_type VARCHAR2,
430 b_object_id NUMBER,
431 b_start_dt DATE,
432 b_end_dt DATE,
433 b_busy_tentative VARCHAR2
434 ) IS
435 SELECT CSSB.SCHEDULE_ID,
436 CSSD.START_DATE_TIME,
437 CSSD.END_DATE_TIME,
438 CSPVL.PERIOD_ID,
439 CSPVL.HAS_DETAILS,
440 CSPVL.DURATION,
441 CSPVL.DURATION_UOM,
442 NVL(CSPVL.PERIOD_NAME,FL.MEANING) AS PERIOD_NAME,
443 CSPCVL.PERIOD_CATEGORY_ID,
444 CSPCVL.PERIOD_CATEGORY_NAME,
445 DECODE(CSPCVL.FREE_BUSY_TYPE,'FREE','FREE','BUSY','BUSY',NVL(b_busy_tentative,'TENTATIVE')) FREE_BUSY_TYPE,
446 CSPCVL.DISPLAY_COLOR
447 FROM CAC_SR_SCHDL_OBJECTS CSSO,
448 CAC_SR_SCHEDULES_B CSSB,
449 CAC_SR_SCHDL_DETAILS CSSD,
450 CAC_SR_PERIODS_VL CSPVL,
451 CAC_SR_PERIOD_CATS_VL CSPCVL,
452 FND_LOOKUPS FL
453 WHERE CSSO.OBJECT_TYPE = b_object_type
454 AND CSSO.OBJECT_ID = b_object_id
455 AND CSSO.START_DATE_ACTIVE <= b_end_dt
456 AND CSSO.END_DATE_ACTIVE >= b_start_dt
457 AND CSSO.SCHEDULE_ID = CSSB.SCHEDULE_ID
458 AND CSSB.DELETED_DATE IS NULL
459 AND (CSSB.SCHEDULE_CATEGORY = b_schdl_cat
460 OR CSSB.SCHEDULE_ID IN (SELECT SCHEDULE_ID
461 FROM CAC_SR_PUBLISH_SCHEDULES
462 WHERE OBJECT_TYPE = b_object_type
463 AND OBJECT_ID = b_object_id
464 AND b_schdl_cat IS NULL
465 ))
466 AND CSSD.SCHEDULE_OBJECT_ID = CSSO.SCHEDULE_OBJECT_ID
467 AND CSSD.START_DATE_TIME < b_end_dt
468 AND CSSD.END_DATE_TIME > b_start_dt
469 AND CSPVL.PERIOD_ID = CSSD.PERIOD_ID
470 AND CSPCVL.PERIOD_CATEGORY_ID = CSPVL.PERIOD_CATEGORY_ID
471 AND FL.LOOKUP_TYPE(+) = 'CAC_SR_WEEK_DAY'
472 AND FL.LOOKUP_CODE(+) = CSPVL.week_day_num
473 ORDER BY CSSD.START_DATE_TIME;
474
475 CURSOR C_EXCEPTION
476 (
477 b_schdl_cat VARCHAR2,
478 b_object_type VARCHAR2,
479 b_object_id NUMBER,
480 b_start_dt DATE,
481 b_end_dt DATE,
482 b_busy_tentative VARCHAR2
483 ) IS
484 SELECT CSEVL.EXCEPTION_ID,
485 CSEVL.START_DATE_TIME,
486 DECODE(CSEVL.WHOLE_DAY_FLAG,'N',CSEVL.END_DATE_TIME,CSEVL.END_DATE_TIME+1) END_DATE_TIME, -- Add 24 hrs if it's whole day
487 CSEVL.EXCEPTION_NAME,
488 CSPVL.PERIOD_CATEGORY_ID,
489 CSPVL.PERIOD_CATEGORY_NAME,
490 DECODE(CSPVL.FREE_BUSY_TYPE,'FREE','FREE','BUSY','BUSY',NVL(b_busy_tentative,'TENTATIVE')) FREE_BUSY_TYPE,
491 CSPVL.DISPLAY_COLOR,
492 CSEVL.HR_CAL_EVENT_TYPE,
493 CSEVL.HR_CAL_EVENT_ID,
494 DECODE(CSSE.SCHEDULE_OBJECT_ID,NULL,
495 DECODE(CSEVL.HR_CAL_EVENT_TYPE,NULL,
496 DECODE(CSEVL.HR_CAL_EVENT_ID,NULL,3,2),1),
497 DECODE(CSEVL.HR_CAL_EVENT_TYPE,NULL,
498 DECODE(CSEVL.HR_CAL_EVENT_ID,NULL,6,5),4)) LEVEL_IND
499 FROM CAC_SR_SCHDL_OBJECTS CSSO,
500 CAC_SR_SCHEDULES_B CSSB,
501 CAC_SR_SCHDL_EXCEPTIONS CSSE,
502 CAC_SR_EXCEPTIONS_VL CSEVL,
503 CAC_SR_PERIOD_CATS_VL CSPVL
504 WHERE CSSO.OBJECT_TYPE = b_object_type
505 AND CSSO.OBJECT_ID = b_object_id
506 AND CSSO.START_DATE_ACTIVE <= b_end_dt
507 AND CSSO.END_DATE_ACTIVE >= b_start_dt
508 AND CSSO.SCHEDULE_ID = CSSB.SCHEDULE_ID
509 AND CSSB.DELETED_DATE IS NULL
510 AND (CSSB.SCHEDULE_CATEGORY = b_schdl_cat
511 OR CSSB.SCHEDULE_ID IN (SELECT SCHEDULE_ID
512 FROM CAC_SR_PUBLISH_SCHEDULES
513 WHERE OBJECT_TYPE = b_object_type
514 AND OBJECT_ID = b_object_id
515 AND b_schdl_cat IS NULL
516 ))
517 AND ( ((CSSE.SCHEDULE_ID = CSSB.SCHEDULE_ID) AND (CSSE.SCHEDULE_OBJECT_ID IS NULL OR CSSE.SCHEDULE_OBJECT_ID = CSSO.SCHEDULE_OBJECT_ID))
518 OR CSSE.SCHEDULE_OBJECT_ID = CSSO.SCHEDULE_OBJECT_ID)
519 AND CSEVL.EXCEPTION_ID = CSSE.EXCEPTION_ID
520 AND CSPVL.PERIOD_CATEGORY_ID = CSEVL.PERIOD_CATEGORY_ID
521 ORDER BY LEVEL_IND,CSEVL.START_DATE_TIME;
522
523 l_index NUMBER;
524 l_excp_start_dt DATE;
525 l_excp_end_dt DATE;
526 l_excp_fb VARCHAR2(30);
527 l_schdl_id NUMBER;
528 l_start_dt DATE;
529 l_end_dt DATE;
530 l_period_id NUMBER;
531 l_has_details VARCHAR2(1);
532 l_duration_num NUMBER;
533 l_duration_uom VARCHAR2(30);
534 l_fb VARCHAR2(30);
535 l_duration_ms NUMBER;
536 l_rec_processed BOOLEAN;
537 l_shift_bands CAC_AVLBLTY_TIME_BAND_VARRAY;
538 l_super_recs CAC_AVLBLTY_TIME_VARRAY;
539 l_excp_recs CAC_AVLBLTY_TIME_VARRAY;
540 l_super_index NUMBER;
541 l_period_name VARCHAR2(2000);
542 l_category_id NUMBER;
543 l_category_name VARCHAR2(2000);
544 l_color VARCHAR2(30);
545 l_summary_index NUMBER;
546 l_pre_schedule BOOLEAN;
547 l_temp_end_dt DATE;
548 l_temp_dt DATE;
549 l_excp_idx NUMBER;
550 l_hr_cal_events CAC_HR_CAL_EVENTS_PVT.HR_CAL_EVENT_TBL_TYPE;
551 l_idx NUMBER;
552 l_excp_duration NUMBER;
553 l_temp_excp_rec CAC_AVLBLTY_TIME;
554 l_temp_temp_excp_rec CAC_AVLBLTY_TIME;
555 k NUMBER;
556
557 BEGIN
558
559 x_Schedule := CAC_AVLBLTY_TIME_VARRAY();
560 x_Schedule_Summary := CAC_AVLBLTY_SUMMARY_VARRAY();
561 l_excp_recs := CAC_AVLBLTY_TIME_VARRAY();
562
563 IF (NVL(p_Include_Exception,'T') = 'T')
564 THEN
565 l_excp_idx := 0;
566 FOR ref_excp IN C_EXCEPTION(p_Schdl_Cat,p_Object_Type,p_Object_ID,p_Start_Date_Time,p_End_Date_Time,p_Busy_Tentative)
567 LOOP
568 l_hr_cal_events.DELETE;
569 IF (ref_excp.HR_CAL_EVENT_TYPE IS NOT NULL)
570 THEN
571 CAC_HR_CAL_EVENTS_PVT.GET_HR_CAL_EVENTS
572 (
573 p_Object_Type => p_Object_Type
574 , p_Object_ID => p_Object_ID
575 , p_Start_Date => p_Start_Date_Time
576 , p_End_Date => p_End_Date_Time
577 , p_Event_Type => ref_excp.HR_CAL_EVENT_TYPE
578 , p_Event_Id => NULL
579 , x_hr_cal_events => l_hr_cal_events
580 );
581 ELSIF (ref_excp.HR_CAL_EVENT_ID IS NOT NULL)
582 THEN
583 CAC_HR_CAL_EVENTS_PVT.GET_HR_CAL_EVENTS
584 (
585 p_Object_Type => p_Object_Type
586 , p_Object_ID => p_Object_ID
587 , p_Start_Date => p_Start_Date_Time
588 , p_End_Date => p_End_Date_Time
589 , p_Event_Type => NULL
590 , p_Event_Id => ref_excp.HR_CAL_EVENT_ID
591 , x_hr_cal_events => l_hr_cal_events
592 );
593 ELSE
594 l_hr_cal_events(1).START_DATE_TIME := ref_excp.START_DATE_TIME;
595 l_hr_cal_events(1).END_DATE_TIME := ref_excp.END_DATE_TIME;
596 END IF;
597 IF (l_hr_cal_events.COUNT > 0)
598 THEN
599 FOR i IN l_hr_cal_events.FIRST..l_hr_cal_events.LAST
600 LOOP
601 IF ((l_hr_cal_events(i).END_DATE_TIME > l_hr_cal_events(i).START_DATE_TIME)
602 AND NOT (((l_hr_cal_events(i).START_DATE_TIME < p_Start_Date_Time) AND
603 (l_hr_cal_events(i).END_DATE_TIME < p_Start_Date_Time)) OR
604 ((l_hr_cal_events(i).START_DATE_TIME > p_End_Date_Time) AND
605 (l_hr_cal_events(i).END_DATE_TIME > p_End_Date_Time))))
606 THEN
607 IF (l_excp_idx = 0)
608 THEN
609 l_excp_recs.EXTEND(1);
610 l_excp_idx := 1;
611 l_excp_recs(1) := CAC_AVLBLTY_TIME
612 (
613 PERIOD_NAME => ref_excp.EXCEPTION_NAME,
614 START_DATE_TIME => l_hr_cal_events(i).START_DATE_TIME,
615 END_DATE_TIME => l_hr_cal_events(i).END_DATE_TIME,
616 DURATION_MS => (l_hr_cal_events(i).END_DATE_TIME-l_hr_cal_events(i).START_DATE_TIME)*24*3600*1000,
617 PERIOD_CATEGORY_ID => ref_excp.PERIOD_CATEGORY_ID,
618 PERIOD_CATEGORY_NAME => ref_excp.PERIOD_CATEGORY_NAME,
619 FREE_BUSY_TYPE => ref_excp.FREE_BUSY_TYPE,
620 DISPLAY_COLOR => ref_excp.DISPLAY_COLOR,
621 SUPER_OBJECT_INDEX => NULL,
622 SHIFT_BANDS => NULL,
623 NEXT_OBJECT_INDEX => NULL
624 );
625 ELSE
626 l_idx := l_excp_idx;
627 FOR j IN 1..l_idx
628 LOOP
629 IF (l_hr_cal_events(i).START_DATE_TIME <= l_excp_recs(j).START_DATE_TIME)
630 THEN
631 -- move all the items
632 l_temp_excp_rec := l_excp_recs(j);
633 l_excp_recs(j) := CAC_AVLBLTY_TIME
634 (
635 PERIOD_NAME => ref_excp.EXCEPTION_NAME,
636 START_DATE_TIME => l_hr_cal_events(i).START_DATE_TIME,
637 END_DATE_TIME => l_hr_cal_events(i).END_DATE_TIME,
638 DURATION_MS => (l_hr_cal_events(i).END_DATE_TIME-l_hr_cal_events(i).START_DATE_TIME)*24*3600*1000,
639 PERIOD_CATEGORY_ID => ref_excp.PERIOD_CATEGORY_ID,
640 PERIOD_CATEGORY_NAME => ref_excp.PERIOD_CATEGORY_NAME,
641 FREE_BUSY_TYPE => ref_excp.FREE_BUSY_TYPE,
642 DISPLAY_COLOR => ref_excp.DISPLAY_COLOR,
643 SUPER_OBJECT_INDEX => NULL,
644 SHIFT_BANDS => NULL,
645 NEXT_OBJECT_INDEX => NULL
646 );
647 k := j+1;
648 WHILE k <= l_idx
649 LOOP
650 IF (l_excp_recs(k).START_DATE_TIME >= l_hr_cal_events(i).END_DATE_TIME)
651 THEN
652 l_temp_temp_excp_rec := l_excp_recs(k);
653 l_excp_recs(k) := l_temp_excp_rec;
654 l_temp_excp_rec := l_temp_temp_excp_rec;
655 ELSE
656 l_temp_excp_rec := NULL;
657 IF (l_excp_recs(k).END_DATE_TIME <= l_hr_cal_events(i).END_DATE_TIME)
658 THEN
659 -- k should be deleted
660 FOR l IN k+1..l_idx
661 LOOP
662 l_excp_recs(k) := l_excp_recs(l);
663 END LOOP;
664 l_excp_recs.trim(1);
665 l_idx := l_idx - 1;
666 l_excp_idx := l_excp_idx - 1;
667 k := k - 1;
668 ELSE
669 -- partial overwritten
670 l_excp_recs(k).START_DATE_TIME := l_hr_cal_events(i).END_DATE_TIME;
671 EXIT;
672 END IF;
673 END IF;
674 k := k + 1;
675 END LOOP;
676 IF (l_temp_excp_rec IS NOT NULL)
677 THEN
678 l_excp_recs.EXTEND(1);
679 l_excp_idx := l_excp_idx + 1;
680 l_excp_recs(l_excp_idx) := l_temp_excp_rec;
681 END IF;
682 l_hr_cal_events(i).START_DATE_TIME := l_hr_cal_events(i).END_DATE_TIME;
683 EXIT;
684 ELSE
685 IF (l_hr_cal_events(i).START_DATE_TIME < l_excp_recs(j).END_DATE_TIME)
686 THEN
687 -- overwrite
688 l_excp_recs(j).END_DATE_TIME := l_hr_cal_events(i).START_DATE_TIME;
689 END IF;
690 END IF;
691 END LOOP;
692 IF (l_hr_cal_events(i).END_DATE_TIME > l_hr_cal_events(i).START_DATE_TIME)
693 THEN
694 l_excp_recs.EXTEND(1);
695 l_excp_idx := l_excp_idx + 1;
696 l_excp_recs(l_excp_idx) := CAC_AVLBLTY_TIME
697 (
698 PERIOD_NAME => ref_excp.EXCEPTION_NAME,
699 START_DATE_TIME => l_hr_cal_events(i).START_DATE_TIME,
700 END_DATE_TIME => l_hr_cal_events(i).END_DATE_TIME,
701 DURATION_MS => (l_hr_cal_events(i).END_DATE_TIME-l_hr_cal_events(i).START_DATE_TIME)*24*3600*1000,
702 PERIOD_CATEGORY_ID => ref_excp.PERIOD_CATEGORY_ID,
703 PERIOD_CATEGORY_NAME => ref_excp.PERIOD_CATEGORY_NAME,
704 FREE_BUSY_TYPE => ref_excp.FREE_BUSY_TYPE,
705 DISPLAY_COLOR => ref_excp.DISPLAY_COLOR,
706 SUPER_OBJECT_INDEX => NULL,
707 SHIFT_BANDS => NULL,
708 NEXT_OBJECT_INDEX => NULL
709 );
710 END IF;
711 END IF;
712 END IF;
713 END LOOP;
714 END IF;
715 END LOOP;
716 END IF;
717
718 l_excp_idx := l_excp_recs.first;
719
720 l_pre_schedule := FALSE;
721 OPEN C_SCHEDULE(p_Schdl_Cat,p_Object_Type,p_Object_ID,p_Start_Date_Time,p_End_Date_Time,p_Busy_Tentative);
722 FETCH C_SCHEDULE
723 INTO l_schdl_id, l_start_dt, l_end_dt, l_period_id, l_has_details, l_duration_num, l_duration_uom, l_period_name, l_category_id, l_category_name, l_fb, l_color;
724
725 IF C_SCHEDULE%NOTFOUND
726 THEN
727 -- no schedule found, so assume free
728 l_schdl_id := NULL;
729 l_start_dt := p_Start_Date_Time;
730 l_end_dt := p_End_Date_Time;
731 l_fb := NULL;
732 l_period_id := NULL;
733 l_has_details := NULL;
734 l_duration_num := NULL;
735 l_duration_uom := NULL;
736 l_period_name := NULL;
737 l_category_id := NULL;
738 l_category_name:= NULL;
739 l_color := NULL;
740 ELSIF (l_start_dt > p_Start_Date_Time)
741 THEN
742 l_pre_schedule := TRUE;
743 l_temp_end_dt := l_end_dt;
744 l_end_dt := l_start_dt;
745 l_start_dt := p_Start_Date_Time;
746 ELSIF (l_start_dt < p_Start_Date_Time)
747 THEN
748 l_start_dt := p_Start_Date_Time;
749 END IF;
750
751 -- loop through the schedule data
752 WHILE (l_start_dt < p_End_Date_Time)
753 LOOP
754 -- Reset the records start and end within the query start and end
755 IF (l_end_dt > p_End_Date_Time)
756 THEN
757 l_end_dt := p_End_Date_Time;
758 END IF;
759
760 -- Fetch shift bands if needed
761 IF (p_return_type IN ('B','D'))
762 THEN
763 IF ((l_has_details = 'Y') AND (NOT l_pre_schedule))
764 THEN
765 l_shift_bands := GET_SHIFT_BANDS(l_period_id);
766 ELSE
767 l_shift_bands := NULL;
768 END IF;
769 END IF;
770
771 l_rec_processed := FALSE;
772 -- loop through the exceptions which lie within the current record
773 -- note that if no exception is fetched then l_excp_start_dt will be NULL
774 WHILE ((l_excp_idx IS NOT NULL) AND (l_excp_recs(l_excp_idx).START_DATE_TIME < l_end_dt))
775 LOOP
776 IF (l_excp_recs(l_excp_idx).END_DATE_TIME <= l_start_dt)
777 THEN
778 -- this exception is before current record so fetch the next one
779 -- Note that here we're not checking for p_Include_Exception as
780 -- this part will not be executed if the flag is set to false
781 -- or there are no exceptions
782 l_excp_idx := l_excp_recs.NEXT(l_excp_idx);
783 ELSE
784 -- this exception ends after the record start
785 -- now check how to split the record
786 IF (l_excp_recs(l_excp_idx).START_DATE_TIME > l_start_dt)
787 THEN
788 -- exception starts after the schedule record start
789 -- so create a schedule record for the first part
790 IF (p_return_type IN ('B','D'))
791 THEN
792 CREATE_AVLBLTY_TIME
793 (
794 l_pre_schedule,
795 l_period_name,
796 l_start_dt,
797 l_excp_recs(l_excp_idx).START_DATE_TIME,
798 (l_excp_recs(l_excp_idx).START_DATE_TIME - l_start_dt)*24*3600*1000,
799 l_category_id,
800 l_category_name,
801 l_fb,
802 l_color,
803 true,
804 NULL,
805 x_Schedule,
806 l_index
807 );
808 END IF;
809 IF (p_return_type IN ('B','S'))
810 THEN
811 CREATE_AVLBLTY_SUMMARY
812 (
813 l_pre_schedule,
814 l_start_dt,
815 l_excp_recs(l_excp_idx).START_DATE_TIME,
816 l_category_id,
817 l_category_name,
818 l_fb,
819 l_color,
820 x_Schedule_Summary,
821 l_summary_index
822 );
823 END IF;
824 -- reset the schedule record start to the exception start
825 l_start_dt := l_excp_recs(l_excp_idx).START_DATE_TIME;
826 END IF;
827 -- now check where the exception ends and split the remaining record
828 IF (l_excp_recs(l_excp_idx).END_DATE_TIME = l_end_dt)
829 THEN
830 -- exception ends at the same time, so the whole record will be overwritten
831 IF (p_return_type IN ('B','D'))
832 THEN
833 CREATE_AVLBLTY_TIME
834 (
835 FALSE,
836 l_excp_recs(l_excp_idx).PERIOD_NAME,
837 l_start_dt,
838 l_end_dt,
839 (l_end_dt - l_start_dt)*24*3600*1000,
840 l_excp_recs(l_excp_idx).PERIOD_CATEGORY_ID,
841 l_excp_recs(l_excp_idx).PERIOD_CATEGORY_NAME,
842 l_excp_recs(l_excp_idx).FREE_BUSY_TYPE,
843 l_excp_recs(l_excp_idx).DISPLAY_COLOR,
844 true,
845 NULL,
846 x_Schedule,
847 l_index
848 );
849 END IF;
850 IF (p_return_type IN ('B','S'))
851 THEN
852 CREATE_AVLBLTY_SUMMARY
853 (
854 FALSE,
855 l_start_dt,
856 l_end_dt,
857 l_excp_recs(l_excp_idx).PERIOD_CATEGORY_ID,
858 l_excp_recs(l_excp_idx).PERIOD_CATEGORY_NAME,
859 l_excp_recs(l_excp_idx).FREE_BUSY_TYPE,
860 l_excp_recs(l_excp_idx).DISPLAY_COLOR,
861 x_Schedule_Summary,
862 l_summary_index
863 );
864 END IF;
865 -- fetch the next exception and quit exception loop
866 l_excp_idx := l_excp_recs.NEXT(l_excp_idx);
867 -- quit exception loop and go to the next schedule record
868 l_rec_processed := TRUE;
869 EXIT;
870 ELSIF (l_excp_recs(l_excp_idx).END_DATE_TIME > l_end_dt)
871 THEN
872 -- exception ends after the record, so the whole record will be overwritten
873 IF (p_return_type IN ('B','D'))
874 THEN
875 CREATE_AVLBLTY_TIME
876 (
877 FALSE,
878 l_excp_recs(l_excp_idx).PERIOD_NAME,
879 l_start_dt,
880 l_end_dt,
881 (l_end_dt - l_start_dt)*24*3600*1000,
882 l_excp_recs(l_excp_idx).PERIOD_CATEGORY_ID,
883 l_excp_recs(l_excp_idx).PERIOD_CATEGORY_NAME,
884 l_excp_recs(l_excp_idx).FREE_BUSY_TYPE,
885 l_excp_recs(l_excp_idx).DISPLAY_COLOR,
886 true,
887 NULL,
888 x_Schedule,
889 l_index
890 );
891 END IF;
892 IF (p_return_type IN ('B','S'))
893 THEN
894 CREATE_AVLBLTY_SUMMARY
895 (
896 FALSE,
897 l_start_dt,
898 l_end_dt,
899 l_excp_recs(l_excp_idx).PERIOD_CATEGORY_ID,
900 l_excp_recs(l_excp_idx).PERIOD_CATEGORY_NAME,
901 l_excp_recs(l_excp_idx).FREE_BUSY_TYPE,
902 l_excp_recs(l_excp_idx).DISPLAY_COLOR,
903 x_Schedule_Summary,
904 l_summary_index
905 );
906 END IF;
907 -- reset the exception to the part where it's not used
908 l_excp_recs(l_excp_idx).START_DATE_TIME := l_end_dt;
909 -- quit exception loop and go to the next schedule record
910 l_rec_processed := TRUE;
911 EXIT;
912 ELSE
913 -- exception ends before the end of the record, so split into two
914 IF (p_return_type IN ('B','D'))
915 THEN
916 CREATE_AVLBLTY_TIME
917 (
918 FALSE,
919 l_excp_recs(l_excp_idx).PERIOD_NAME,
920 l_start_dt,
921 l_excp_recs(l_excp_idx).END_DATE_TIME,
922 (l_excp_recs(l_excp_idx).END_DATE_TIME - l_start_dt)*24*3600*1000,
923 l_excp_recs(l_excp_idx).PERIOD_CATEGORY_ID,
924 l_excp_recs(l_excp_idx).PERIOD_CATEGORY_NAME,
925 l_excp_recs(l_excp_idx).FREE_BUSY_TYPE,
926 l_excp_recs(l_excp_idx).DISPLAY_COLOR,
927 true,
928 NULL,
929 x_Schedule,
930 l_index
931 );
932 END IF;
933 IF (p_return_type IN ('B','S'))
934 THEN
935 CREATE_AVLBLTY_SUMMARY
936 (
937 FALSE,
938 l_start_dt,
939 l_excp_recs(l_excp_idx).END_DATE_TIME,
940 l_excp_recs(l_excp_idx).PERIOD_CATEGORY_ID,
941 l_excp_recs(l_excp_idx).PERIOD_CATEGORY_NAME,
942 l_excp_recs(l_excp_idx).FREE_BUSY_TYPE,
943 l_excp_recs(l_excp_idx).DISPLAY_COLOR,
944 x_Schedule_Summary,
945 l_summary_index
946 );
947 END IF;
948 -- this is the second part. there could possibly be more exceptions
949 -- in this part, so set the start of the record to the end of
950 -- exception and fetch the next exception
951 l_start_dt := l_excp_recs(l_excp_idx).END_DATE_TIME;
952 l_excp_idx := l_excp_recs.NEXT(l_excp_idx);
953 END IF;
954 END IF;
955 END LOOP;
956 -- now create an item if the record was not completely processed
957 IF NOT l_rec_processed
958 THEN
959 l_duration_ms := CONVERT_TO_MILLIS(l_duration_num,l_duration_uom);
960 -- use the minimum of the durations
961 IF (((l_end_dt - l_start_dt)*24*3600*1000) > NVL(l_duration_ms,0))
962 THEN
963 l_duration_ms := (l_end_dt - l_start_dt)*24*3600*1000;
964 END IF;
965 IF (p_return_type IN ('B','D'))
966 THEN
967 CREATE_AVLBLTY_TIME
968 (
969 l_pre_schedule,
970 l_period_name,
971 l_start_dt,
972 l_end_dt,
973 l_duration_ms,
974 l_category_id,
975 l_category_name,
976 l_fb,
977 l_color,
978 true,
979 NULL,
980 x_Schedule,
981 l_index
982 );
983 END IF;
984 IF (p_return_type IN ('B','S'))
985 THEN
986 CREATE_AVLBLTY_SUMMARY
987 (
988 l_pre_schedule,
989 l_start_dt,
990 l_end_dt,
991 l_category_id,
992 l_category_name,
993 l_fb,
994 l_color,
995 x_Schedule_Summary,
996 l_summary_index
997 );
998 END IF;
999 END IF;
1000 IF (l_pre_schedule)
1001 THEN
1002 l_pre_schedule := FALSE;
1003 l_start_dt := l_end_dt;
1004 l_end_dt := l_temp_end_dt;
1005 -- if there was an schedule then fetch the next record or else just quit
1006 ELSIF (l_schdl_id IS NOT NULL)
1007 THEN
1008 l_temp_end_dt := l_end_dt;
1009 FETCH C_SCHEDULE
1010 INTO l_schdl_id, l_start_dt, l_end_dt, l_period_id, l_has_details, l_duration_num, l_duration_uom, l_period_name, l_category_id, l_category_name, l_fb, l_color;
1011 IF C_SCHEDULE%NOTFOUND
1012 THEN
1013 l_schdl_id := NULL;
1014 l_start_dt := l_end_dt;
1015 l_end_dt := p_End_Date_Time;
1016 l_fb := NULL;
1017 l_period_id := NULL;
1018 l_has_details := NULL;
1019 l_duration_num := NULL;
1020 l_duration_uom := NULL;
1021 l_period_name := NULL;
1022 l_category_id := NULL;
1023 l_category_name:= NULL;
1024 l_color := NULL;
1025 ELSE
1026 IF (l_start_dt > l_temp_end_dt)
1027 THEN
1028 l_temp_dt := l_start_dt;
1029 l_start_dt := l_temp_end_dt;
1030 l_temp_end_dt := l_end_dt;
1031 l_end_dt := l_temp_dt;
1032 l_pre_schedule := TRUE;
1033 END IF;
1034 END IF;
1035 ELSE
1036 EXIT;
1037 END IF;
1038 END LOOP;
1039
1040 IF C_SCHEDULE%ISOPEN
1041 THEN
1042 CLOSE C_SCHEDULE;
1043 END IF;
1044
1045 IF C_EXCEPTION%ISOPEN
1046 THEN
1047 CLOSE C_EXCEPTION;
1048 END IF;
1049
1050 END GET_SCHEDULE_DATA;
1051
1052
1053 /**
1054 PROCEDURE getBookingData
1055 /*******************************************************************************
1056 **
1057 ** getBookingData
1058 **
1059 ** returns all Bookings and appointments in task tables for the given:
1060 ** - Object Instance
1061 ** - Period
1062 **
1063 *******************************************************************************
1064 ( p_api_version IN NUMBER -- API version you coded against
1065 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
1066 , p_ObjectType IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
1067 , p_ObjectID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
1068 , p_PeriodStartDateTime IN DATE -- start date and time of period of interest
1069 , p_PeriodEndDateTime IN DATE -- end date and time of period of interest
1070 , p_OpagueBkngCat IN JTF_NUMBER_TABLE -- Booking Categories (i.e. task types) that should be considered OPAGUE
1071 , p_BookingStatus IN VARCHAR2 -- Are we looking for Firm or Soft bookings
1072 , p_BusyTentative IN VARCHAR2 -- How to treat FREEBUSYTIME objects with FREEBUSYTYPE = BUSY TENTATIVE?
1073 -- FREE: BUSY TENTATIVE means FREE
1074 -- BUSY: BUSY TENTATIVE means BUSY
1075 -- NULL: leave the interpretation to caller
1076 , x_BookingData IN OUT CAC_SR_FREEBUSYTIME_VARRAY
1077 -- returns the existings bookings for the Object Instance
1078 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
1079 -- 'E': API completed with recoverable errors; explanation on errorstack (Warnings)
1080 -- 'U': API completed with UN recoverable errors: error message on error stack
1081 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack, if 1 then x_msg_data contains the message
1082 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
1083 )
1084 IS
1085 TYPE c_Bookings_type IS REF CURSOR;
1086
1087 c_Bookings c_Bookings_type;
1088
1089 l_FreeBusyTime CAC_SR_FREEBUSYTIME;
1090
1091 i NUMBER := 0;
1092
1093 --
1094 -- This is the base query for picking up all opague records from the task table
1095 --
1096 l_query VARCHAR2(2000) :=
1097
1098 'SELECT GREATEST( CAC_AVLBLTY_PVT.AdjustForTimezone( jtb.timezone_id ' ||
1099 ', :b_ToTimeZone ' ||
1100 ', jtb.calendar_start_date ' ||
1101 ') ' ||
1102 ', :b_PeriodStartDateTime ' ||
1103 ') StartDateTime ' ||
1104 ', LEAST( CAC_AVLBLTY_PVT.AdjustForTimezone( jtb.timezone_id ' ||
1105 ', :b_ToTimeZone ' ||
1106 ', jtb.calendar_end_date ' ||
1107 ') ' ||
1108 ', :b_PeriodEndDateTime ' ||
1109 ') EndDateTime ' ||
1110 ', DECODE( jta.free_busy_type, ''FREE'',''FREE'' ' ||
1111 ', ''BUSY'',''BUSY'' ' ||
1112 ', ''TENTATIVE'',NVL(:b_BusyTentative,''TENTATIVE'') ' ||
1113 ') FBType ' ||
1114 ', jtb.task_type_id CategoryID ' ||
1115 ', jtb.entity CategoryType '||
1116 'FROM jtf_task_all_assignments jta ' ||
1117 ', jtf_tasks_b jtb ' ||
1118 ', ( SELECT jts.task_status_id ' ||
1119 ' FROM jtf_task_statuses_b jts ' ||
1120 ' WHERE jts.assignment_status_flag = ''Y'' ' ||
1121 ' AND NVL(jts.closed_flag,''N'') = ''N'' ' ||
1122 ' AND NVL(jts.completed_flag,''N'') = ''N'' ' ||
1123 ' AND NVL(jts.rejected_flag,''N'') = ''N'' ' ||
1124 ' AND NVL(jts.on_hold_flag,''N'') = ''N'' ' ||
1125 ' AND NVL(jts.cancelled_flag,''N'') = ''N'' ' ||
1126 ' ) jto ' ||
1127 'WHERE jta.resource_type_code = :b_ObjectType ' ||
1128 'AND jta.resource_id = :b_ObjectID ' ||
1129 'AND jta.assignment_status_id = jto.task_status_id ' ||
1130 'AND jta.task_id = jtb.task_id ' ||
1131 'AND jtb.open_flag = ''Y'' ' ||
1132 'AND jtb.calendar_end_date >= :b_StartDate ' ||
1133 'AND jtb.calendar_start_date <= :b_EndDate ' ||
1134 'AND jtb.entity IN (''BOOKING'',''TASK'',''APPOINTMENT'') '; -- Add appointment here once they go to servertimezone
1135
1136 l_opague VARCHAR2(2000);
1137 l_status VARCHAR2(200);
1138 l_order VARCHAR2(200) := 'ORDER BY 1';
1139
1140 l_ServerTimeZone NUMBER := TO_NUMBER(FND_PROFILE.Value('SERVER_TIMEZONE_ID'));
1141
1142 BEGIN
1143
1144 IF (x_BookingData IS NULL)
1145 THEN
1146 x_BookingData := CAC_SR_FREEBUSYTIME_VARRAY();
1147 END IF;
1148
1149 --
1150 -- the list of OPAGUE tasks may have been restricted to the booking categories
1151 -- (task types) in p_OpagueXcptnPeriodCat
1152 --
1153 IF ((p_OpagueBkngCat IS NOT NULL) AND (p_OpagueBkngCat.COUNT > 0))
1154 THEN
1155 l_opague := 'AND jtb.task_type_id IN (';
1156 FOR i IN 1..p_OpagueBkngCat.LAST
1157 LOOP
1158 IF (i < p_OpagueBkngCat.LAST)
1159 THEN
1160 l_opague := l_opague || p_OpagueBkngCat(i)||',';
1161 ELSE
1162 l_opague := l_opague || p_OpagueBkngCat(i)||') ';
1163 END IF;
1164 END LOOP;
1165 END IF;
1166
1167 --
1168 -- We'll need to change the query to look for either Firm or Soft bookings:
1169 -- - free_busy_type = 'BUSY' means the booking is Firm
1170 -- - free_busy_type = 'TENTATIVE' means the booking is Soft
1171 --
1172 IF (p_BookingStatus IS NOT NULL)
1173 THEN
1174 l_status := 'AND jta.free_busy_type = '''||p_BookingStatus||''' ';
1175
1176 END IF;
1177
1178 --
1179 -- Build the query
1180 --
1181 l_query := l_query || l_opague || l_Status || l_order;
1182
1183 --
1184 -- Initialize the CAC_SR_FREEBUSYTIME object
1185 --
1186 l_FreeBusyTime := CAC_SR_FREEBUSYTIME(NULL,NULL,NULL,NULL,NULL);
1187
1188 OPEN c_Bookings FOR l_query USING l_ServerTimeZone
1189 , (p_PeriodStartDateTime - 1) -- for timezone adjustments
1190 , l_ServerTimeZone
1191 , (p_PeriodEndDateTime + 1) -- for timezone adjustments
1192 , p_BusyTentative
1193 , p_ObjectType
1194 , p_ObjectID
1195 , (p_PeriodStartDateTime - 1) -- for timezone adjustments
1196 , (p_PeriodEndDateTime + 1); -- for timezone adjustments
1197
1198 <<BOOKINGS>>
1199 LOOP -- Bookings
1200 FETCH c_Bookings INTO l_FreeBusyTime.StartDatetime
1201 , l_FreeBusyTime.EndDateTime
1202 , l_FreeBusyTime.FBType
1203 , l_FreeBusyTime.CategoryID
1204 , l_FreeBusyTime.CategoryType;
1205
1206 IF (c_Bookings%FOUND)
1207 THEN
1208 --
1209 -- If after timezone adjustment it is still within the query period use it
1210 --
1211 IF ( ( l_FreeBusyTime.StartDatetime <= p_PeriodEndDateTime )
1212 AND ( l_FreeBusyTime.EndDateTime > p_PeriodStartDateTime)
1213 )
1214 THEN
1215 --
1216 -- stick it in FreeBusyList
1217 --
1218 Extend( p_varray => x_BookingData
1219 , p_element => l_FreeBusyTime
1220 , p_index => i
1221 );
1222 END IF;
1223
1224 ELSE -- (c_Bookings%NOTFOUND)
1225 CLOSE c_Bookings;
1226 EXIT BOOKINGS; -- exit Bookings loop
1227
1228 END IF;
1229 END LOOP;-- end Bookings loop
1230
1231 --
1232 -- Remove any null element that extend may have added
1233 --
1234 Trim( p_varray => x_BookingData
1235 , p_index => i
1236 );
1237
1238 END getBookingData;
1239 **/
1240
1241
1242 PROCEDURE INSERT_SCHEDULE_DETAILS
1243 /*******************************************************************************
1244 **
1245 ** INSERT_SCHEDULE_DETAILS
1246 **
1247 ** popluates the schedule details table from the pl/sql table
1248 **
1249 *******************************************************************************/
1250 ( p_Schedule_Id IN NUMBER -- id of the schedule
1251 , p_Schedule_Object_Id IN NUMBER
1252 , p_Schedule_Details IN SCHDL_DETAILS_TBL_TYPE
1253 ) IS
1254
1255 l_created_by NUMBER;
1256 l_creation_date DATE;
1257 l_last_updated_by NUMBER;
1258 l_last_update_date DATE;
1259 l_last_update_login NUMBER;
1260
1261 BEGIN
1262
1263 l_created_by := FND_GLOBAL.USER_ID;
1264 l_creation_date := SYSDATE;
1265 l_last_updated_by := FND_GLOBAL.USER_ID;
1266 l_last_update_date := SYSDATE;
1267 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1268
1269 FOR i IN p_Schedule_Details.FIRST..p_Schedule_Details.LAST
1270 LOOP
1271 INSERT INTO CAC_SR_SCHDL_DETAILS
1272 (
1273 SCHEDULE_DETAIL_ID,
1274 SCHEDULE_ID,
1275 SCHEDULE_OBJECT_ID,
1276 PERIOD_ID,
1277 START_DATE_TIME,
1278 END_DATE_TIME,
1279 CREATED_BY,
1280 CREATION_DATE,
1281 LAST_UPDATED_BY,
1282 LAST_UPDATE_DATE,
1283 LAST_UPDATE_LOGIN
1284 )
1285 VALUES
1286 (
1287 CAC_SR_SCHDL_DETAILS_S.NEXTVAL,
1288 p_schedule_id,
1289 p_schedule_object_id,
1290 p_Schedule_Details(i).period_id,
1291 p_Schedule_Details(i).start_date_time,
1292 p_Schedule_Details(i).end_date_time,
1293 l_created_by,
1294 l_creation_date,
1295 l_last_updated_by,
1296 l_last_update_date,
1297 l_last_update_login
1298 );
1299 END LOOP;
1300
1301 END INSERT_SCHEDULE_DETAILS;
1302
1303
1304 PROCEDURE CREATE_PERIOD_DATA_DUR
1305 /*******************************************************************************
1306 **
1307 ** CREATE_PERIOD_DATA_DUR
1308 **
1309 ** popluates the period data pl/sql table by expanding the template
1310 ** should be called for duration based template
1311 **
1312 *******************************************************************************/
1313 ( p_tmpl_id IN NUMBER
1314 , x_period_data OUT NOCOPY PERIOD_TBL_TYPE
1315 ) IS
1316
1317 CURSOR C_TMPL_DETAILS
1318 (
1319 b_tmpl_id NUMBER
1320 ) IS
1321 SELECT DTLS.TEMPLATE_DETAIL_ID,
1322 DTLS.TEMPLATE_DETAIL_SEQ,
1323 DTLS.TEMPLATE_ID,
1324 DTLS.CHILD_PERIOD_ID,
1325 DTLS.CHILD_TEMPLATE_ID,
1326 CSPB.DURATION,
1327 CSPB.DURATION_UOM
1328 FROM CAC_SR_PERIODS_B CSPB,
1329 (SELECT CSTD.TEMPLATE_DETAIL_ID,
1330 CSTD.TEMPLATE_DETAIL_SEQ,
1331 CSTD.TEMPLATE_ID,
1332 CSTD.CHILD_PERIOD_ID,
1333 CSTD.CHILD_TEMPLATE_ID
1334 FROM CAC_SR_TMPL_DETAILS CSTD
1335 START WITH CSTD.TEMPLATE_ID = b_tmpl_id
1336 CONNECT BY PRIOR CSTD.CHILD_TEMPLATE_ID = CSTD.TEMPLATE_ID
1337 ORDER SIBLINGS BY TEMPLATE_DETAIL_SEQ) DTLS
1338 WHERE CSPB.PERIOD_ID(+) = DTLS.CHILD_PERIOD_ID
1339 ORDER BY TEMPLATE_DETAIL_SEQ; --ADDED FOR BUG#7491187
1340
1341
1342 i BINARY_INTEGER;
1343
1344 BEGIN
1345
1346 i := 0;
1347
1348 FOR REF_TMPL_DTLS IN C_TMPL_DETAILS(p_tmpl_id)
1349 LOOP
1350 IF (REF_TMPL_DTLS.CHILD_PERIOD_ID IS NOT NULL)
1351 THEN
1352 -- this is a period, so add a new record
1353 i:= i+1;
1354 x_period_data(i).template_detail_id := REF_TMPL_DTLS.TEMPLATE_DETAIL_ID;
1355 x_period_data(i).period_id := REF_TMPL_DTLS.CHILD_PERIOD_ID;
1356 x_period_data(i).period_span_ms := CONVERT_TO_MILLIS(REF_TMPL_DTLS.DURATION,REF_TMPL_DTLS.DURATION_UOM);
1357 ELSE
1358 -- this is a template, so add a dummy record
1359 i:= i+1;
1360 x_period_data(i).template_detail_id := REF_TMPL_DTLS.TEMPLATE_DETAIL_ID;
1361 x_period_data(i).period_id := NULL;
1362 x_period_data(i).period_span_ms := 0;
1363 END IF;
1364 END LOOP;
1365
1366 END CREATE_PERIOD_DATA_DUR;
1367
1368
1369 PROCEDURE CREATE_PERIOD_DATA_CAL
1370 /*******************************************************************************
1371 **
1372 ** CREATE_PERIOD_DATA_CAL
1373 **
1374 ** popluates the period data pl/sql table by expanding the template
1375 ** should be called for calendar based template
1376 **
1377 *******************************************************************************/
1378 ( p_tmpl_id IN NUMBER
1379 , p_tmpl_length IN NUMBER
1380 , x_period_data OUT NOCOPY PERIOD_TBL_TYPE
1381 ) IS
1382
1383 CURSOR C_TMPL_DETAILS
1384 (
1385 b_tmpl_id NUMBER
1386 ) IS
1387 SELECT DTLS.TEMPLATE_DETAIL_ID,
1388 DTLS.TEMPLATE_DETAIL_SEQ,
1389 DTLS.TEMPLATE_ID,
1390 DTLS.CHILD_PERIOD_ID,
1391 DTLS.CHILD_TEMPLATE_ID,
1392 CSPB.WEEK_DAY_NUM,
1393 CSPB.START_TIME_MS,
1394 CSPB.DURATION,
1395 CSPB.DURATION_UOM,
1396 CSTB.TEMPLATE_LENGTH_DAYS
1397 FROM CAC_SR_TEMPLATES_B CSTB,
1398 CAC_SR_PERIODS_B CSPB,
1399 (SELECT CSTD.TEMPLATE_DETAIL_ID,
1400 CSTD.TEMPLATE_DETAIL_SEQ,
1401 CSTD.TEMPLATE_ID,
1402 CSTD.CHILD_PERIOD_ID,
1403 CSTD.CHILD_TEMPLATE_ID
1404 FROM CAC_SR_TMPL_DETAILS CSTD
1405 START WITH CSTD.TEMPLATE_ID = b_tmpl_id
1406 CONNECT BY PRIOR CSTD.CHILD_TEMPLATE_ID = CSTD.TEMPLATE_ID
1407 ORDER SIBLINGS BY TEMPLATE_DETAIL_SEQ) DTLS
1408 WHERE CSTB.TEMPLATE_ID(+) = DTLS.CHILD_TEMPLATE_ID
1409 AND CSPB.PERIOD_ID(+) = DTLS.CHILD_PERIOD_ID
1410 ORDER BY TEMPLATE_DETAIL_SEQ; --ADDED FOR BUG#7491187
1411
1412 i BINARY_INTEGER;
1413 i_first BINARY_INTEGER;
1414 i_last BINARY_INTEGER;
1415 l_last_tmpl_id NUMBER;
1416 l_last_tmpl_length_ms NUMBER;
1417 l_total_length_ms NUMBER;
1418 l_duration_gap_ms NUMBER;
1419
1420 BEGIN
1421
1422 i := 0;
1423 l_total_length_ms := 0;
1424 -- do we need length based logic? it seems, it'll work without it
1425 l_last_tmpl_id := p_tmpl_id;
1426 l_last_tmpl_length_ms := p_tmpl_length * 24 * 3600 * 1000;
1427
1428 FOR REF_TMPL_DTLS IN C_TMPL_DETAILS(p_tmpl_id)
1429 LOOP
1430 IF (REF_TMPL_DTLS.CHILD_PERIOD_ID IS NOT NULL)
1431 THEN
1432 -- this is a period, so add a new record
1433 -- but before that check if there is a gap between the
1434 -- previous record and this one and fill it
1435 IF (i > 0)
1436 THEN
1437 -- calculate the gap
1438 IF (REF_TMPL_DTLS.WEEK_DAY_NUM >= x_period_data(i).day_num)
1439 THEN
1440 l_duration_gap_ms := (REF_TMPL_DTLS.WEEK_DAY_NUM - x_period_data(i).day_num) * 24 * 3600 * 1000
1441 + REF_TMPL_DTLS.START_TIME_MS - x_period_data(i).day_start_ms
1442 - x_period_data(i).period_span_ms;
1443 ELSE
1444 l_duration_gap_ms := (7 + REF_TMPL_DTLS.WEEK_DAY_NUM - x_period_data(i).day_num) * 24 * 3600 * 1000
1445 + REF_TMPL_DTLS.START_TIME_MS - x_period_data(i).day_start_ms
1446 - x_period_data(i).period_span_ms;
1447 END IF;
1448 IF (l_duration_gap_ms > 0)
1449 THEN
1450 -- create a new record for this gap
1451 i:= i+1;
1452 x_period_data(i).template_detail_id := NULL;
1453 x_period_data(i).period_id := NULL;
1454 x_period_data(i).period_span_ms := l_duration_gap_ms;
1455 x_period_data(i).day_start_ms := NULL;
1456 x_period_data(i).day_num := NULL;
1457 -- increment the total length
1458 l_total_length_ms := l_total_length_ms + x_period_data(i).period_span_ms;
1459 END IF;
1460 ELSE
1461 -- this is the first record
1462 -- so prefill the array assuming week starts on sunday
1463 l_duration_gap_ms := (REF_TMPL_DTLS.WEEK_DAY_NUM - 1)*24*3600*1000 + REF_TMPL_DTLS.START_TIME_MS;
1464 IF (l_duration_gap_ms > 0)
1465 THEN
1466 -- create a new record for this gap
1467 i:= i+1;
1468 x_period_data(i).template_detail_id := NULL;
1469 x_period_data(i).period_id := NULL;
1470 x_period_data(i).period_span_ms := l_duration_gap_ms;
1471 x_period_data(i).day_start_ms := 0;
1472 x_period_data(i).day_num := 1;
1473 -- increment the total length
1474 l_total_length_ms := l_total_length_ms + x_period_data(i).period_span_ms;
1475 END IF;
1476 END IF;
1477 -- create an actual period record
1478 i:= i+1;
1479 x_period_data(i).template_detail_id := REF_TMPL_DTLS.TEMPLATE_DETAIL_ID;
1480 x_period_data(i).period_id := REF_TMPL_DTLS.CHILD_PERIOD_ID;
1481 x_period_data(i).period_span_ms := CONVERT_TO_MILLIS(REF_TMPL_DTLS.DURATION,REF_TMPL_DTLS.DURATION_UOM);
1482 x_period_data(i).day_start_ms := REF_TMPL_DTLS.START_TIME_MS;
1483 x_period_data(i).day_num := REF_TMPL_DTLS.WEEK_DAY_NUM;
1484 -- increment the total length
1485 l_total_length_ms := l_total_length_ms + x_period_data(i).period_span_ms;
1486 ELSE
1487 -- this is a template, so store the tmpl id and length for future records
1488 l_last_tmpl_id := REF_TMPL_DTLS.CHILD_TEMPLATE_ID;
1489 l_last_tmpl_length_ms := REF_TMPL_DTLS.TEMPLATE_LENGTH_DAYS * 24 * 3600 * 1000;
1490 -- also create a dummy row
1491 IF (i = 0)
1492 THEN
1493 i := 1;
1494 x_period_data(i).template_detail_id := REF_TMPL_DTLS.TEMPLATE_DETAIL_ID;
1495 x_period_data(i).period_id := NULL;
1496 x_period_data(i).period_span_ms := 0;
1497 x_period_data(i).day_start_ms := 0;
1498 x_period_data(i).day_num := 1;
1499 ELSE
1500 i := i+1;
1501 x_period_data(i).template_detail_id := REF_TMPL_DTLS.TEMPLATE_DETAIL_ID;
1502 x_period_data(i).period_id := NULL;
1503 x_period_data(i).period_span_ms := 0;
1504 x_period_data(i).day_start_ms := x_period_data(i-1).day_start_ms + x_period_data(i-1).period_span_ms;
1505 x_period_data(i).day_num := x_period_data(i-1).day_num;
1506 --reset if it's crossing the day
1507 IF (x_period_data(i).day_start_ms > 24 * 3600 * 1000)
1508 THEN
1509 x_period_data(i).day_num := x_period_data(i).day_num + TRUNC(x_period_data(i).day_start_ms / (24 * 3600 * 1000));
1510 x_period_data(i).day_start_ms := x_period_data(i).day_start_ms - (x_period_data(i).day_num - x_period_data(i-1).day_num) * 24 * 3600 * 1000;
1511 IF (x_period_data(i).day_num > 7)
1512 THEN
1513 x_period_data(i).day_num := x_period_data(i).day_num - 7;
1514 END IF;
1515 END IF;
1516 END IF;
1517 END IF;
1518 END LOOP;
1519
1520 -- now calculate the gap between the first and last record and
1521 -- fill it so that the template becomes curcular
1522 IF (i > 0)
1523 THEN
1524 l_duration_gap_ms := p_tmpl_length * 24 * 3600 * 1000 - l_total_length_ms;
1525 IF (l_duration_gap_ms > 0)
1526 THEN
1527 -- create a new record for this gap
1528 i:= i+1;
1529 x_period_data(i).template_detail_id := NULL;
1530 x_period_data(i).period_id := NULL;
1531 x_period_data(i).period_span_ms := l_duration_gap_ms;
1532 x_period_data(i).day_start_ms := NULL;
1533 x_period_data(i).day_num := NULL;
1534 END IF;
1535 END IF;
1536
1537 END CREATE_PERIOD_DATA_CAL;
1538
1539
1540 PROCEDURE CREATE_PERIOD_DATA_DAY
1541 /*******************************************************************************
1542 **
1543 ** CREATE_PERIOD_DATA_DAY
1544 **
1545 ** popluates the period data pl/sql table by expanding the template
1546 ** should be called for day based template
1547 **
1548 *******************************************************************************/
1549 ( p_tmpl_id IN NUMBER
1550 , p_tmpl_length IN NUMBER
1551 , x_period_data OUT NOCOPY PERIOD_TBL_TYPE
1552 ) IS
1553
1554 CURSOR C_TMPL_DETAILS
1555 (
1556 b_tmpl_id NUMBER
1557 ) IS
1558 SELECT DTLS.TEMPLATE_DETAIL_ID,
1559 DTLS.TEMPLATE_DETAIL_SEQ,
1560 DTLS.TEMPLATE_ID,
1561 DTLS.CHILD_PERIOD_ID,
1562 DTLS.CHILD_TEMPLATE_ID,
1563 DTLS.DAY_START,
1564 DTLS.DAY_STOP,
1565 CSPB.START_TIME_MS,
1566 CSPB.END_TIME_MS,
1567 CSPB.DURATION,
1568 CSPB.DURATION_UOM
1569 FROM CAC_SR_PERIODS_B CSPB,
1570 (SELECT CSTD.TEMPLATE_DETAIL_ID,
1571 CSTD.TEMPLATE_DETAIL_SEQ,
1572 CSTD.TEMPLATE_ID,
1573 CSTD.CHILD_PERIOD_ID,
1574 CSTD.CHILD_TEMPLATE_ID,
1575 CSTD.DAY_START,
1576 CSTD.DAY_STOP
1577 FROM CAC_SR_TMPL_DETAILS CSTD
1578 START WITH CSTD.TEMPLATE_ID = b_tmpl_id
1579 CONNECT BY PRIOR CSTD.CHILD_TEMPLATE_ID = CSTD.TEMPLATE_ID
1580 ORDER SIBLINGS BY TEMPLATE_DETAIL_SEQ) DTLS
1581 WHERE CSPB.PERIOD_ID(+) = DTLS.CHILD_PERIOD_ID
1582 ORDER BY CHILD_PERIOD_ID NULLS FIRST , TEMPLATE_DETAIL_SEQ; --ADDED FOR BUG#7491187
1583 --MODIFIED FOR BUG#7758438
1584
1585 TYPE NUMBER_TBL_TYPE IS TABLE OF NUMBER
1586 INDEX BY BINARY_INTEGER;
1587
1588 i BINARY_INTEGER;
1589 l_tmpl_offset NUMBER_TBL_TYPE;
1590 l_period_data PERIOD_TBL_TYPE;
1591 l_duration_gap_ms NUMBER;
1592
1593 BEGIN
1594
1595 i := 0;
1596 l_tmpl_offset(p_tmpl_id) := 0;
1597
1598 FOR REF_TMPL_DTLS IN C_TMPL_DETAILS(p_tmpl_id)
1599 LOOP
1600 IF (REF_TMPL_DTLS.CHILD_PERIOD_ID IS NOT NULL)
1601 THEN
1602 -- this is a period, so add a new record
1603 FOR ref_i IN REF_TMPL_DTLS.DAY_START..REF_TMPL_DTLS.DAY_STOP
1604 LOOP
1605 i := i+1;
1606 l_period_data(i).template_detail_id := REF_TMPL_DTLS.TEMPLATE_DETAIL_ID;
1607 l_period_data(i).period_id := REF_TMPL_DTLS.CHILD_PERIOD_ID;
1608 IF (REF_TMPL_DTLS.DURATION IS NULL)
1609 THEN
1610 l_period_data(i).period_span_ms := REF_TMPL_DTLS.END_TIME_MS - REF_TMPL_DTLS.START_TIME_MS;
1611 ELSE
1612 l_period_data(i).period_span_ms := CONVERT_TO_MILLIS(REF_TMPL_DTLS.DURATION,REF_TMPL_DTLS.DURATION_UOM);
1613 END IF;
1614 l_period_data(i).day_start_ms := NVL(REF_TMPL_DTLS.START_TIME_MS,0);
1615 l_period_data(i).day_num := l_tmpl_offset(REF_TMPL_DTLS.TEMPLATE_ID) + ref_i;
1616 END LOOP;
1617 ELSE
1618 -- this is a template, so store the tmpl offset for future records
1619 l_tmpl_offset(REF_TMPL_DTLS.CHILD_TEMPLATE_ID) := REF_TMPL_DTLS.DAY_START - 1;
1620 -- add a dummy record
1621 l_period_data(i).template_detail_id := REF_TMPL_DTLS.TEMPLATE_DETAIL_ID;
1622 l_period_data(i).period_id := NULL;
1623 l_period_data(i).day_start_ms := 0;
1624 l_period_data(i).day_num := REF_TMPL_DTLS.DAY_START;
1625 l_period_data(i).period_span_ms := 0;
1626 END IF;
1627 END LOOP;
1628
1629 i := 0;
1630 -- loop through the entire template length, find periods for those
1631 -- days and fill up gaps
1632 FOR ref_i IN 1..p_tmpl_length
1633 LOOP
1634 FOR ref_j IN l_period_data.FIRST..l_period_data.LAST
1635 LOOP
1636 IF (ref_i = l_period_data(ref_j).day_num)
1637 THEN
1638 IF (i = 0)
1639 THEN
1640 -- this is the first record being added
1641 -- check if it needs to be prefilled
1642 l_duration_gap_ms := (ref_i - 1) * 24 * 3600 * 1000
1643 + l_period_data(ref_j).day_start_ms;
1644 IF (l_duration_gap_ms > 0)
1645 THEN
1646 i := 1;
1647 x_period_data(i).template_detail_id := NULL;
1648 x_period_data(i).period_id := NULL;
1649 x_period_data(i).period_span_ms := l_duration_gap_ms;
1650 x_period_data(i).day_start_ms := 0;
1651 x_period_data(i).day_num := 1;
1652 END IF;
1653 ELSE
1654 -- check if there is any gap between this and the previous record
1655 -- and fill it
1656 l_duration_gap_ms := (ref_i - x_period_data(i).day_num) * 24 * 3600 * 1000
1657 - (x_period_data(i).day_start_ms + x_period_data(i).period_span_ms)
1658 + l_period_data(ref_j).day_start_ms;
1659 IF (l_duration_gap_ms > 0)
1660 THEN
1661 i := i+1;
1662 x_period_data(i).template_detail_id := NULL;
1663 x_period_data(i).period_id := NULL;
1664 x_period_data(i).period_span_ms := l_duration_gap_ms;
1665 x_period_data(i).day_start_ms := NULL;
1666 x_period_data(i).day_num := NULL;
1667 END IF;
1668 END IF;
1669 i := i+1;
1670 x_period_data(i).template_detail_id := l_period_data(ref_j).template_detail_id;
1671 x_period_data(i).period_id := l_period_data(ref_j).period_id;
1672 x_period_data(i).period_span_ms := l_period_data(ref_j).period_span_ms;
1673 x_period_data(i).day_start_ms := l_period_data(ref_j).day_start_ms;
1674 x_period_data(i).day_num := l_period_data(ref_j).day_num;
1675 END IF;
1676 END LOOP;
1677 END LOOP;
1678
1679 -- check if the last part needs to be filled
1680 -- i should be the last index
1681 IF (i = 0)
1682 THEN
1683 x_period_data(1).template_detail_id := NULL;
1684 x_period_data(1).period_id := NULL;
1685 x_period_data(1).period_span_ms := p_tmpl_length * 24 * 3600 * 1000;
1686 x_period_data(1).day_start_ms := 0;
1687 x_period_data(1).day_num := 1;
1688 ELSE
1689 l_duration_gap_ms := (p_tmpl_length+1 - x_period_data(i).day_num) * 24 * 3600 * 1000
1690 - (x_period_data(i).day_start_ms + x_period_data(i).period_span_ms);
1691 IF (l_duration_gap_ms > 0)
1692 THEN
1693 i := i+1;
1694 x_period_data(i).template_detail_id := NULL;
1695 x_period_data(i).period_id := NULL;
1696 x_period_data(i).period_span_ms := l_duration_gap_ms;
1697 x_period_data(i).day_start_ms := NULL;
1698 x_period_data(i).day_num := NULL;
1699 END IF;
1700 END IF;
1701
1702 END CREATE_PERIOD_DATA_DAY;
1703
1704
1705 PROCEDURE GET_PERIOD_START_DUR
1706 /*******************************************************************************
1707 **
1708 ** GET_PERIOD_START_DUR
1709 **
1710 ** get the starting index of the pl/sql table to use
1711 ** should be called for duration based template
1712 **
1713 *******************************************************************************/
1714 ( p_Schdl_Start_Date IN DATE
1715 , p_period_data IN PERIOD_TBL_TYPE
1716 , p_start_template_detail_id IN NUMBER
1717 , x_start_index OUT NOCOPY BINARY_INTEGER
1718 , x_ms_to_use OUT NOCOPY NUMBER
1719 ) IS
1720
1721 l_day_ms NUMBER;
1722 l_total_ms NUMBER;
1723
1724 BEGIN
1725
1726 x_start_index := p_period_data.FIRST;
1727 x_ms_to_use := p_period_data(x_start_index).period_span_ms;
1728
1729 IF p_start_template_detail_id IS NOT NULL
1730 THEN
1731 -- loop through the records and find out where this date starts
1732 -- the assumption here is that the records start on first day
1733 FOR i IN p_period_data.FIRST..p_period_data.LAST
1734 LOOP
1735 IF (p_start_template_detail_id = p_period_data(i).template_detail_id)
1736 THEN
1737 -- current record will be used
1738 x_start_index := i;
1739 -- calculate how much time should be used
1740 x_ms_to_use := p_period_data(x_start_index).period_span_ms;
1741 -- quit the loop
1742 EXIT;
1743 END IF;
1744 END LOOP;
1745 END IF;
1746
1747 END GET_PERIOD_START_DUR;
1748
1749
1750 PROCEDURE GET_PERIOD_START_CAL
1751 /*******************************************************************************
1752 **
1753 ** GET_PERIOD_START_CAL
1754 **
1755 ** get the starting index of the pl/sql table to use
1756 ** should be called for calendar based template
1757 **
1758 *******************************************************************************/
1759 ( p_Schdl_Start_Date IN DATE
1760 , p_period_data IN PERIOD_TBL_TYPE
1761 , p_start_template_detail_id IN NUMBER
1762 , x_start_index OUT NOCOPY BINARY_INTEGER
1763 , x_ms_to_use OUT NOCOPY NUMBER
1764 , x_ms_use_blank OUT NOCOPY BOOLEAN
1765 ) IS
1766
1767 l_sunday_date DATE;
1768 l_day_number NUMBER;
1769 l_day_ms NUMBER;
1770 l_total_ms NUMBER;
1771 l_start_calc BOOLEAN;
1772
1773 BEGIN
1774
1775 x_start_index := p_period_data.FIRST;
1776 x_ms_to_use := p_period_data(x_start_index).period_span_ms;
1777 x_ms_use_blank := FALSE;
1778
1779 -- get a known sunday
1780 l_sunday_date := TO_DATE('1995/01/01','yyyy/mm/dd');
1781 -- calculate the day number, 1 - sunday, 2 - monday etc.
1782 l_day_number := MOD((TRUNC(p_Schdl_Start_Date) - l_sunday_date),7);
1783 IF (l_day_number >= 0)
1784 THEN
1785 l_day_number := 1 + l_day_number;
1786 ELSE
1787 l_day_number := 1 - l_day_number;
1788 END IF;
1789
1790 -- calculate the time, using the day part and time part
1791 l_day_ms := (l_day_number - 1) * 24 * 3600 * 1000
1792 + (p_Schdl_Start_Date - TRUNC(p_Schdl_Start_Date)) * 24 * 3600 * 1000;
1793
1794 -- loop through the records and find out where this date starts
1795 -- the assumption here is that the records start on sunday
1796 l_start_calc := FALSE;
1797 l_total_ms := 0;
1798 FOR i IN p_period_data.FIRST..p_period_data.LAST
1799 LOOP
1800 IF ((NOT l_start_calc) AND ((p_start_template_detail_id IS NULL) OR
1801 (p_start_template_detail_id = p_period_data(i).template_detail_id)))
1802 THEN
1803 l_start_calc := TRUE;
1804 x_start_index := i;
1805 IF (l_total_ms > l_day_ms)
1806 THEN
1807 x_ms_to_use := l_total_ms - l_day_ms;
1808 x_ms_use_blank := TRUE;
1809 EXIT;
1810 END IF;
1811 END IF;
1812 l_total_ms := l_total_ms + p_period_data(i).period_span_ms;
1813 IF (l_start_calc)
1814 THEN
1815 IF (l_total_ms > l_day_ms)
1816 THEN
1817 x_start_index := i;
1818 -- calculate how much time should be used
1819 x_ms_to_use := l_total_ms - l_day_ms;
1820 -- quit the loop
1821 EXIT;
1822 END IF;
1823 END IF;
1824 END LOOP;
1825
1826 END GET_PERIOD_START_CAL;
1827
1828
1829 PROCEDURE GET_PERIOD_START_DAY
1830 /*******************************************************************************
1831 **
1832 ** GET_PERIOD_START_DAY
1833 **
1834 ** get the starting index of the pl/sql table to use
1835 ** should be called for day based template
1836 **
1837 *******************************************************************************/
1838 ( p_Schdl_Start_Date IN DATE
1839 , p_period_data IN PERIOD_TBL_TYPE
1840 , p_start_template_detail_id IN NUMBER
1841 , x_start_index OUT NOCOPY BINARY_INTEGER
1842 , x_ms_to_use OUT NOCOPY NUMBER
1843 , x_ms_use_blank OUT NOCOPY BOOLEAN
1844 ) IS
1845
1846 l_day_ms NUMBER;
1847 l_total_ms NUMBER;
1848 l_start_calc BOOLEAN;
1849
1850 BEGIN
1851
1852 x_start_index := p_period_data.FIRST;
1853 x_ms_to_use := p_period_data(x_start_index).period_span_ms;
1854 x_ms_use_blank := FALSE;
1855
1856 -- calculate the time, using the day part and time part
1857 l_day_ms := (p_Schdl_Start_Date - TRUNC(p_Schdl_Start_Date)) * 24 * 3600 * 1000;
1858
1859 -- loop through the records and find out where this date starts
1860 -- the assumption here is that the records start on first day
1861 l_start_calc := FALSE;
1862 l_total_ms := 0;
1863 FOR i IN p_period_data.FIRST..p_period_data.LAST
1864 LOOP
1865 IF ((NOT l_start_calc) AND ((p_start_template_detail_id IS NULL) OR
1866 (p_start_template_detail_id = p_period_data(i).template_detail_id)))
1867 THEN
1868 l_start_calc := TRUE;
1869 x_start_index := i;
1870 IF (p_period_data(i).day_start_ms > l_day_ms)
1871 THEN
1872 -- this means that the previous record should be used
1873 x_ms_to_use := p_period_data(i).day_start_ms - l_day_ms;
1874 x_ms_use_blank := TRUE;
1875 EXIT;
1876 END IF;
1877 l_total_ms := p_period_data(i).day_start_ms;
1878 END IF;
1879 IF (l_start_calc)
1880 THEN
1881 l_total_ms := l_total_ms + p_period_data(i).period_span_ms;
1882 IF (l_total_ms > l_day_ms)
1883 THEN
1884 -- current record will be used
1885 x_start_index := i;
1886 -- calculate how much time should be used
1887 x_ms_to_use := l_total_ms - l_day_ms;
1888 -- quit the loop
1889 EXIT;
1890 END IF;
1891 END IF;
1892 END LOOP;
1893
1894 END GET_PERIOD_START_DAY;
1895
1896
1897 PROCEDURE GENERATE_SCHEDULE_DETAILS
1898 /*******************************************************************************
1899 **
1900 ** GENERATE_SCHEDULE_DETAILS
1901 **
1902 ** generates the schedule details
1903 ** - Schedule
1904 ** - template
1905 ** - duration
1906 **
1907 *******************************************************************************/
1908 ( p_period_data IN PERIOD_TBL_TYPE
1909 , p_Schdl_Tmpl_Type IN VARCHAR2
1910 , p_Schdl_Start_Date IN DATE
1911 , p_Schdl_End_Date IN DATE
1912 , p_start_template_detail_id IN NUMBER
1913 , x_schedule_details OUT NOCOPY SCHDL_DETAILS_TBL_TYPE
1914 ) IS
1915
1916 l_rec_start DATE;
1917 l_rec_end DATE;
1918 l_schdl_dtls_data SCHDL_DETAILS_TBL_TYPE;
1919 l_schdl_i BINARY_INTEGER;
1920 l_period_i BINARY_INTEGER;
1921 l_ms_to_use NUMBER;
1922 l_use_blank BOOLEAN;
1923
1924 BEGIN
1925
1926 IF (p_period_data.COUNT > 0)
1927 THEN
1928 IF (p_Schdl_Tmpl_Type = 'DUR')
1929 THEN
1930 GET_PERIOD_START_DUR(p_Schdl_Start_Date,p_period_data,p_start_template_detail_id,l_period_i,l_ms_to_use);
1931 l_use_blank := FALSE;
1932 ELSIF (p_Schdl_Tmpl_Type = 'CAL')
1933 THEN
1934 GET_PERIOD_START_CAL(p_Schdl_Start_Date,p_period_data,p_start_template_detail_id,l_period_i,l_ms_to_use,l_use_blank);
1935 ELSIF (p_Schdl_Tmpl_Type = 'DAY')
1936 THEN
1937 GET_PERIOD_START_DAY(p_Schdl_Start_Date,p_period_data,p_start_template_detail_id,l_period_i,l_ms_to_use,l_use_blank);
1938 END IF;
1939
1940 l_schdl_i := 0;
1941
1942 l_rec_start := p_Schdl_Start_Date;
1943
1944 WHILE (l_rec_start < p_Schdl_End_Date+1)
1945 LOOP
1946 -- add the period span to calculate end of the schdl detail record
1947 l_rec_end := l_rec_start + l_ms_to_use / (24*3600*1000.0);
1948 -- if the end is after schedule end then reset it.
1949 -- note tha addition of 1 day, as the end date of schedule has 00 in
1950 -- time part, but it's actually till end of that day
1951 IF (l_rec_end > p_Schdl_End_Date+1)
1952 THEN
1953 l_rec_end := p_Schdl_End_Date+1;
1954 END IF;
1955
1956 IF (l_schdl_i = 0)
1957 THEN
1958 -- create a new record
1959 l_schdl_i := 1;
1960 IF (l_use_blank)
1961 THEN
1962 l_schdl_dtls_data(l_schdl_i).period_id := NULL;
1963 ELSE
1964 l_schdl_dtls_data(l_schdl_i).period_id := p_period_data(l_period_i).period_id;
1965 IF (l_period_i = p_period_data.LAST)
1966 THEN
1967 l_period_i := p_period_data.FIRST;
1968 ELSE
1969 l_period_i := l_period_i + 1;
1970 END IF;
1971 END IF;
1972 l_schdl_dtls_data(l_schdl_i).start_date_time := l_rec_start;
1973 l_schdl_dtls_data(l_schdl_i).end_date_time := l_rec_end;
1974 ELSE
1975 IF (NVL(l_schdl_dtls_data(l_schdl_i).period_id,-1) =
1976 NVL(p_period_data(l_period_i).period_id,-1))
1977 THEN
1978 -- if the previous record's period id is same as this one
1979 -- then increment the end of previous record
1980 l_schdl_dtls_data(l_schdl_i).end_date_time := l_rec_end;
1981 ELSE
1982 -- create a new record
1983 l_schdl_i := l_schdl_i + 1;
1984 l_schdl_dtls_data(l_schdl_i).period_id := p_period_data(l_period_i).period_id;
1985 l_schdl_dtls_data(l_schdl_i).start_date_time := l_rec_start;
1986 l_schdl_dtls_data(l_schdl_i).end_date_time := l_rec_end;
1987 END IF;
1988 IF (l_period_i = p_period_data.LAST)
1989 THEN
1990 l_period_i := p_period_data.FIRST;
1991 ELSE
1992 l_period_i := l_period_i + 1;
1993 END IF;
1994 END IF;
1995
1996 -- set the start of next record to the end of this record
1997 l_rec_start := l_rec_end;
1998 l_ms_to_use := p_period_data(l_period_i).period_span_ms;
1999 END LOOP;
2000 ELSE
2001 l_schdl_dtls_data(1).period_id := NULL;
2002 l_schdl_dtls_data(1).start_date_time := p_Schdl_Start_Date;
2003 l_schdl_dtls_data(1).end_date_time := p_Schdl_End_Date + 1;
2004 END IF;
2005
2006 x_schedule_details := l_schdl_dtls_data;
2007
2008 END GENERATE_SCHEDULE_DETAILS;
2009
2010
2011 PROCEDURE POPULATE_SCHEDULE_DETAILS
2012 /*******************************************************************************
2013 **
2014 ** POPULATE_SCHEDULE_DETAILS
2015 **
2016 ** expands the schedule for the given:
2017 ** - Schedule
2018 ** - template
2019 ** - duration
2020 **
2021 *******************************************************************************/
2022 ( p_Schedule_Id IN NUMBER -- id of the schedule
2023 , p_Schdl_Tmpl_Id IN NUMBER
2024 , p_Schdl_Tmpl_Length IN NUMBER
2025 , p_Schdl_Tmpl_Type IN VARCHAR2
2026 , p_Schdl_Start_Date IN DATE
2027 , p_Schdl_End_Date IN DATE -- end date of the schedule
2028 -- 24 hrs will be added to this as it should be till end of that day
2029 ) IS
2030
2031 CURSOR c_get_schdl_objects
2032 (
2033 b_schedule_id NUMBER
2034 ) IS
2035 SELECT SCHEDULE_OBJECT_ID
2036 , START_DATE_ACTIVE
2037 , END_DATE_ACTIVE
2038 , START_TEMPLATE_DETAIL_ID
2039 FROM CAC_SR_SCHDL_OBJECTS
2040 WHERE SCHEDULE_ID = b_schedule_id;
2041
2042 l_period_data PERIOD_TBL_TYPE;
2043 l_schdl_dtls_data SCHDL_DETAILS_TBL_TYPE;
2044 l_obj_schdl_dtls SCHDL_DETAILS_TBL_TYPE;
2045
2046 BEGIN
2047
2048 IF (p_Schdl_Tmpl_Type = 'DUR')
2049 THEN
2050 CREATE_PERIOD_DATA_DUR(p_Schdl_Tmpl_Id,l_period_data);
2051 ELSIF (p_Schdl_Tmpl_Type = 'CAL')
2052 THEN
2053 CREATE_PERIOD_DATA_CAL(p_Schdl_Tmpl_Id,p_Schdl_Tmpl_Length,l_period_data);
2054 ELSIF (p_Schdl_Tmpl_Type = 'DAY')
2055 THEN
2056 CREATE_PERIOD_DATA_DAY(p_Schdl_Tmpl_Id,p_Schdl_Tmpl_Length,l_period_data);
2057 END IF;
2058
2059 GENERATE_SCHEDULE_DETAILS
2060 (
2061 l_period_data,
2062 p_Schdl_Tmpl_Type,
2063 p_Schdl_Start_Date,
2064 p_Schdl_End_Date,
2065 NULL,
2066 l_schdl_dtls_data
2067 );
2068
2069 INSERT_SCHEDULE_DETAILS
2070 (
2071 p_Schedule_id,
2072 NULL,
2073 l_schdl_dtls_data
2074 );
2075
2076 FOR ref_schdl_objects IN c_get_schdl_objects(p_schedule_id)
2077 LOOP
2078 IF ((ref_schdl_objects.start_date_active = p_Schdl_Start_Date) AND
2079 (ref_schdl_objects.end_date_active = p_Schdl_End_Date) AND
2080 (ref_schdl_objects.start_template_detail_id IS NULL))
2081 THEN
2082 INSERT_SCHEDULE_DETAILS
2083 (
2084 p_Schedule_id,
2085 ref_schdl_objects.schedule_object_id,
2086 l_schdl_dtls_data
2087 );
2088 ELSE
2089 GENERATE_SCHEDULE_DETAILS
2090 (
2091 l_period_data,
2092 p_Schdl_Tmpl_Type,
2093 ref_schdl_objects.start_date_active,
2094 ref_schdl_objects.end_date_active,
2095 ref_schdl_objects.start_template_detail_id,
2096 l_obj_schdl_dtls
2097 );
2098
2099 INSERT_SCHEDULE_DETAILS
2100 (
2101 p_Schedule_id,
2102 ref_schdl_objects.schedule_object_id,
2103 l_obj_schdl_dtls
2104 );
2105 END IF;
2106 END LOOP;
2107
2108 END POPULATE_SCHEDULE_DETAILS;
2109
2110
2111 PROCEDURE POPULATE_OBJECT_SCHDL_DETAILS
2112 /*******************************************************************************
2113 **
2114 ** POPULATE_OBJECT_SCHDL_DETAILS
2115 **
2116 ** expands the schedule for the given:
2117 ** - Resource
2118 ** - Schedule
2119 **
2120 *******************************************************************************/
2121 ( p_Schedule_Id IN NUMBER -- id of the schedule
2122 , p_Schedule_Object_Id IN NUMBER
2123 , p_Object_Start_Date IN DATE
2124 , p_Object_End_Date IN DATE
2125 , p_Start_Template_Detail_Id IN NUMBER
2126 ) IS
2127
2128 CURSOR c_get_schdl
2129 (
2130 b_schedule_id NUMBER
2131 ) IS
2132 SELECT CSTB.TEMPLATE_ID
2133 , CSTB.TEMPLATE_TYPE
2134 , CSTB.TEMPLATE_LENGTH_DAYS
2135 , CSSB.START_DATE_ACTIVE
2136 , CSSB.END_DATE_ACTIVE
2137 FROM CAC_SR_SCHEDULES_B CSSB
2138 , CAC_SR_TEMPLATES_B CSTB
2139 WHERE CSSB.SCHEDULE_ID = b_schedule_id
2140 AND CSTB.TEMPLATE_ID = CSSB.TEMPLATE_ID;
2141
2142 l_period_data PERIOD_TBL_TYPE;
2143 l_schdl_dtls_data SCHDL_DETAILS_TBL_TYPE;
2144 l_Schdl_Tmpl_Id NUMBER;
2145 l_Schdl_Tmpl_Type VARCHAR2(30);
2146 l_Schdl_Tmpl_Length NUMBER;
2147 l_Schdl_Start_Date DATE;
2148 l_Schdl_End_Date DATE;
2149
2150 BEGIN
2151
2152 DELETE FROM CAC_SR_SCHDL_DETAILS
2153 WHERE SCHEDULE_OBJECT_ID = p_schedule_object_id;
2154
2155 OPEN c_get_schdl(p_Schedule_Id);
2156 FETCH c_get_schdl
2157 INTO l_Schdl_Tmpl_Id,l_Schdl_Tmpl_Type,l_Schdl_Tmpl_Length,l_Schdl_Start_Date,l_Schdl_End_Date;
2158 CLOSE c_get_schdl;
2159
2160 IF (l_Schdl_Tmpl_Type = 'DUR')
2161 THEN
2162 CREATE_PERIOD_DATA_DUR(l_Schdl_Tmpl_Id,l_period_data);
2163 ELSIF (l_Schdl_Tmpl_Type = 'CAL')
2164 THEN
2165 CREATE_PERIOD_DATA_CAL(l_Schdl_Tmpl_Id,l_Schdl_Tmpl_Length,l_period_data);
2166 ELSIF (l_Schdl_Tmpl_Type = 'DAY')
2167 THEN
2168 CREATE_PERIOD_DATA_DAY(l_Schdl_Tmpl_Id,l_Schdl_Tmpl_Length,l_period_data);
2169 END IF;
2170
2171 GENERATE_SCHEDULE_DETAILS
2172 (
2173 l_period_data,
2174 l_Schdl_Tmpl_Type,
2175 p_Object_Start_Date,
2176 p_Object_End_Date,
2177 p_Start_Template_Detail_Id,
2178 l_schdl_dtls_data
2179 );
2180
2181 INSERT_SCHEDULE_DETAILS
2182 (
2183 p_Schedule_id,
2184 p_Schedule_Object_Id,
2185 l_schdl_dtls_data
2186 );
2187
2188 END POPULATE_OBJECT_SCHDL_DETAILS;
2189
2190
2191 PROCEDURE POST_CREATE_SCHEDULE
2192 /*******************************************************************************
2193 **
2194 ** POST_CREATE_SCHEDULE
2195 **
2196 ** expands the schedule for the given:
2197 ** - Schedule
2198 ** - template
2199 ** - duration
2200 ** and submits business events
2201 **
2202 *******************************************************************************/
2203 ( p_Schedule_Id IN NUMBER -- id of the schedule
2204 , p_Schedule_Category IN VARCHAR2
2205 , p_Schdl_Tmpl_Id IN NUMBER
2206 , p_Schdl_Tmpl_Length IN NUMBER
2207 , p_Schdl_Tmpl_Type IN VARCHAR2
2208 , p_Schdl_Start_Date IN DATE
2209 , p_Schdl_End_Date IN DATE
2210 )
2211 IS
2212
2213 CURSOR c_get_schdl_objects
2214 (
2215 b_schedule_id NUMBER
2216 ) IS
2217 SELECT OBJECT_TYPE
2218 , OBJECT_ID
2219 , START_DATE_ACTIVE
2220 , END_DATE_ACTIVE
2221 FROM CAC_SR_SCHDL_OBJECTS
2222 WHERE SCHEDULE_ID = b_schedule_id;
2223
2224 BEGIN
2225
2226 POPULATE_SCHEDULE_DETAILS
2227 (
2228 p_Schedule_Id,
2229 p_Schdl_Tmpl_Id,
2230 p_Schdl_Tmpl_Length,
2231 p_Schdl_Tmpl_Type,
2232 p_Schdl_Start_Date,
2233 p_Schdl_End_Date
2234 );
2235
2236 CAC_AVLBLTY_EVENTS_PVT.RAISE_CREATE_SCHEDULE
2237 (
2238 p_Schedule_Id,
2239 p_Schedule_Category,
2240 p_Schdl_Start_Date,
2241 p_Schdl_End_Date
2242 );
2243
2244 FOR ref_objects IN c_get_schdl_objects(p_Schedule_Id)
2245 LOOP
2246 CAC_AVLBLTY_EVENTS_PVT.RAISE_ADD_RESOURCE
2247 (
2248 p_Schedule_Id,
2249 p_Schedule_Category,
2250 p_Schdl_Start_Date,
2251 p_Schdl_End_Date,
2252 ref_objects.object_type,
2253 ref_objects.object_id,
2254 ref_objects.start_date_active,
2255 ref_objects.end_date_active
2256 );
2257 END LOOP;
2258
2259 END POST_CREATE_SCHEDULE;
2260
2261
2262 PROCEDURE POST_UPDATE_SCHEDULE
2263 /*******************************************************************************
2264 **
2265 ** POST_UPDATE_SCHEDULE
2266 **
2267 ** expands the schedule for the given:
2268 ** - Schedule
2269 ** - template
2270 ** - duration
2271 ** and submits business events
2272 **
2273 *******************************************************************************/
2274 ( p_Schedule_Id IN NUMBER -- id of the schedule
2275 , p_Schedule_Category IN VARCHAR2
2276 , p_Schdl_Tmpl_Id IN NUMBER
2277 , p_Schdl_Tmpl_Length IN NUMBER
2278 , p_Schdl_Tmpl_Type IN VARCHAR2
2279 , p_Schdl_Start_Date IN DATE
2280 , p_Schdl_End_Date IN DATE
2281 )
2282 IS
2283
2284 CURSOR c_get_schdl_objects
2285 (
2286 b_schedule_id NUMBER
2287 ) IS
2288 SELECT CSSO.OBJECT_TYPE
2289 , CSSO.OBJECT_ID
2290 , CSSO.START_DATE_ACTIVE
2291 , CSSO.END_DATE_ACTIVE
2292 , CSSD.SCHEDULE_OBJECT_ID
2293 FROM CAC_SR_SCHDL_OBJECTS CSSO,
2294 (SELECT SCHEDULE_OBJECT_ID, MIN(START_DATE_TIME)
2295 FROM CAC_SR_SCHDL_DETAILS
2296 WHERE SCHEDULE_ID = b_schedule_id
2297 ) CSSD
2298 WHERE CSSO.SCHEDULE_ID = b_schedule_id
2299 AND CSSD.SCHEDULE_OBJECT_ID(+) = CSSO.SCHEDULE_OBJECT_ID;
2300
2301 BEGIN
2302
2303 DELETE FROM CAC_SR_SCHDL_DETAILS
2304 WHERE SCHEDULE_ID = p_schedule_id;
2305
2306 POPULATE_SCHEDULE_DETAILS
2307 (
2308 p_Schedule_Id,
2309 p_Schdl_Tmpl_Id,
2310 p_Schdl_Tmpl_Length,
2311 p_Schdl_Tmpl_Type,
2312 p_Schdl_Start_Date,
2313 p_Schdl_End_Date
2314 );
2315
2316 CAC_AVLBLTY_EVENTS_PVT.RAISE_UPDATE_SCHEDULE
2317 (
2318 p_Schedule_Id,
2319 p_Schedule_Category,
2320 p_Schdl_Start_Date,
2321 p_Schdl_End_Date
2322 );
2323
2324 END POST_UPDATE_SCHEDULE;
2325
2326
2327 PROCEDURE POST_DELETE_SCHEDULE
2328 /*******************************************************************************
2329 **
2330 ** POST_DELETE_SCHEDULE
2331 **
2332 ** submits business events
2333 **
2334 *******************************************************************************/
2335 ( p_Schedule_Id IN NUMBER -- id of the schedule
2336 , p_Schedule_Category IN VARCHAR2
2337 , p_Schdl_Start_Date IN DATE
2338 , p_Schdl_End_Date IN DATE
2339 )
2340 IS
2341
2342 BEGIN
2343
2344 CAC_AVLBLTY_EVENTS_PVT.RAISE_DELETE_SCHEDULE
2345 (
2346 p_Schedule_Id,
2347 p_Schedule_Category,
2348 p_Schdl_Start_Date,
2349 p_Schdl_End_Date
2350 );
2351
2352 END POST_DELETE_SCHEDULE;
2353
2354
2355 END CAC_AVLBLTY_PVT;
[Home] [Help]