[Home] [Help]
PACKAGE BODY: APPS.CAC_VIEW_UTIL_PVT
Source
1 PACKAGE BODY CAC_VIEW_UTIL_PVT as
2 /* $Header: cacpvutb.pls 120.7 2006/01/09 23:57:17 deeprao noship $ */
3 /*======================================================================+
4 | Copyright (c) 2004 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | cacpvutb.pls |
9 | |
10 | DESCRIPTION |
11 | This package is a private utility for Calendar views. |
12 | |
13 | NOTES |
14 | |
15 | Date Developer Change |
16 | ----------- --------------- --------------------------------------- |
17 | 01-July-2004 Rada Despotovic Created |
18 *=======================================================================*/
19
20 /* -----------------------------------------------------------------
21 * -- Function Name: create_repeat_collab_details
22 * -- Description : This function creates collaboration details record
23 * -- for target task_id by copying the data from source
24 * -- task_id.
25 * -- Parameter : p_source_task_id = Task Id
26 * -- Parameter : p_target_task_id = Task Id
27 * -- Return Type : VARCHAR2
28 * -----------------------------------------------------------------*/
29 PROCEDURE create_repeat_collab_details (
30 p_source_task_id IN NUMBER,
31 p_target_task_id IN NUMBER
32 )
33 IS
34 CURSOR c_collab
35 IS
36 SELECT location, dial_in, meeting_mode, meeting_url,
37 meeting_id, join_url, playback_url, chat_url, download_url,
38 is_standalone_location
39 FROM cac_view_collab_details_vl
40 WHERE task_id = p_source_task_id;
41
42 collab_row c_collab%ROWTYPE;
43 l_seqnum NUMBER := 0;
44 l_row_id VARCHAR2(30);
45 BEGIN
46 SAVEPOINT create_repeat_collab_sp;
47 OPEN c_collab;
48 FETCH c_collab INTO collab_row;
49
50 IF c_collab%NOTFOUND THEN
51 RETURN;
52 CLOSE c_collab;
53 END IF;
54 CLOSE c_collab;
55 -- new collab_id
56 SELECT cac_view_collab_details_s.nextval
57 INTO l_seqnum
58 FROM DUAL;
59
60 CAC_VIEW_COLLAB_DETAILS_PKG.INSERT_ROW(
61 X_ROWID => l_row_id,
62 X_COLLAB_ID => l_seqnum,
63 X_TASK_ID => p_target_task_id,
64 X_MEETING_MODE => collab_row.meeting_mode,
65 X_MEETING_ID => collab_row.meeting_id,
66 X_MEETING_URL => collab_row.meeting_url,
67 X_JOIN_URL => collab_row.join_url,
68 X_PLAYBACK_URL => collab_row.playback_url,
69 X_DOWNLOAD_URL => collab_row.download_url,
70 X_CHAT_URL => collab_row.chat_url,
71 X_IS_STANDALONE_LOCATION => collab_row.is_standalone_location,
72 X_LOCATION => collab_row.location,
73 X_DIAL_IN => collab_row.dial_in,
74 X_CREATION_DATE => SYSDATE,
75 X_CREATED_BY => jtf_task_utl.created_by,
76 X_LAST_UPDATE_DATE => SYSDATE,
77 X_LAST_UPDATED_BY => jtf_task_utl.updated_by,
78 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
79
80 EXCEPTION
81 WHEN OTHERS THEN
82 ROLLBACK TO create_repeat_collab_sp;
83 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
84 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
85 fnd_msg_pub.add;
86
87 END create_repeat_collab_details;
88
89 /* -----------------------------------------------------------------
90 * -- Function Name: update_repeat_collab_details
91 * -- Description : This function updates collaboration details record
92 * -- for target task_id by copying the data from source
93 * -- task_id.
94 * -- Parameter : p_source_task_id = Task Id
95 * -- Parameter : p_target_task_id = Task Id
96 * -- Return Type : VARCHAR2
97 * -----------------------------------------------------------------*/
98 PROCEDURE update_repeat_collab_details(
99 p_source_task_id IN NUMBER,
100 p_target_task_id IN NUMBER
101 )
102 IS
103 CURSOR c_collab
104 IS
105 SELECT location, dial_in, meeting_mode, meeting_url,
106 meeting_id, join_url, playback_url, chat_url, download_url,
107 is_standalone_location
108 FROM cac_view_collab_details_vl
109 WHERE task_id = p_source_task_id;
110
111 CURSOR c_collab_update
112 IS
113 SELECT collab_id
114 FROM cac_view_collab_details_vl
115 WHERE task_id = p_target_task_id;
116
117 collab_row c_collab%ROWTYPE;
118 collab_update_row c_collab_update%ROWTYPE;
119 l_seqnum NUMBER := 0;
120 l_row_id VARCHAR2(30);
121 BEGIN
122
123 SAVEPOINT update_repeat_collab_sp;
124 OPEN c_collab;
125 FETCH c_collab INTO collab_row;
126
127 IF c_collab%NOTFOUND THEN
128 CLOSE c_collab;
129 RETURN;
130 END IF;
131 CLOSE c_collab;
132 OPEN c_collab_update;
133 FETCH c_collab_update INTO collab_update_row;
134
135 IF c_collab_update%NOTFOUND THEN
136 CLOSE c_collab_update;
137 -- insert here
138 -- new collab_id
139 SELECT cac_view_collab_details_s.nextval
140 INTO l_seqnum
141 FROM DUAL;
142 CAC_VIEW_COLLAB_DETAILS_PKG.INSERT_ROW(
143 X_ROWID => l_row_id,
144 X_COLLAB_ID => l_seqnum,
145 X_TASK_ID => p_target_task_id,
146 X_MEETING_MODE => collab_row.meeting_mode,
147 X_MEETING_ID => collab_row.meeting_id,
148 X_MEETING_URL => collab_row.meeting_url,
149 X_JOIN_URL => collab_row.join_url,
150 X_PLAYBACK_URL => collab_row.playback_url,
151 X_DOWNLOAD_URL => collab_row.download_url,
152 X_CHAT_URL => collab_row.chat_url,
153 X_IS_STANDALONE_LOCATION => collab_row.is_standalone_location,
154 X_LOCATION => collab_row.location,
155 X_DIAL_IN => collab_row.dial_in,
156 X_CREATION_DATE => SYSDATE,
157 X_CREATED_BY => jtf_task_utl.created_by,
158 X_LAST_UPDATE_DATE => SYSDATE,
159 X_LAST_UPDATED_BY => jtf_task_utl.updated_by,
160 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
161 RETURN;
162 END IF;
163 CLOSE c_collab_update;
164
165 CAC_VIEW_COLLAB_DETAILS_PKG.UPDATE_ROW (
166 X_COLLAB_ID => collab_update_row.collab_id,
167 X_TASK_ID => p_target_task_id,
168 X_MEETING_MODE => collab_row.meeting_mode,
169 X_MEETING_ID => collab_row.meeting_id,
170 X_MEETING_URL => collab_row.meeting_url,
171 X_JOIN_URL => collab_row.join_url,
172 X_PLAYBACK_URL => collab_row.playback_url,
173 X_DOWNLOAD_URL => collab_row.download_url,
174 X_CHAT_URL => collab_row.chat_url,
175 X_IS_STANDALONE_LOCATION => collab_row.is_standalone_location,
176 X_LOCATION => collab_row.location,
177 X_DIAL_IN => collab_row.dial_in,
178 X_LAST_UPDATE_DATE => SYSDATE,
179 X_LAST_UPDATED_BY => jtf_task_utl.updated_by,
180 X_LAST_UPDATE_LOGIN => fnd_global.login_id
181 );
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 ROLLBACK TO update_repeat_collab_sp;
186 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
187 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
188 fnd_msg_pub.add;
189 END;
190
191 PROCEDURE AdjustForTimezone
192 ( p_source_tz_id IN NUMBER
193 , p_dest_tz_id IN NUMBER
194 , p_source_day_time IN DATE
195 , x_dest_day_time OUT NOCOPY DATE
196 )
197 IS
198 l_return_status VARCHAR2(1);
199 l_msg_count NUMBER;
200 l_msg_data VARCHAR2(2000);
201
202 l_SourceTimezoneID NUMBER;
203
204 BEGIN
205 IF (p_source_day_time IS NOT NULL)
206 THEN
207 /****************************************************************************
208 ** NULL is the same in every timezone
209 ****************************************************************************/
210 IF (p_source_tz_id IS NULL)
211 THEN
212 /**************************************************************************
213 ** If the timezone is not defined used the profile value
214 **************************************************************************/
215 --l_SourceTimezoneID := to_number(FND_PROFILE.Value('JTF_CAL_DEFAULT_TIMEZONE'));
216 l_SourceTimezoneID := to_number(FND_PROFILE.Value('SERVER_TIMEZONE_ID'));
217 --l_SourceTimezoneID := to_number(NVL(FND_PROFILE.Value('CLIENT_TIMEZONE_ID'),4));
218 ELSE
219 l_SourceTimezoneID := p_source_tz_id;
220 END IF;
221 /***********************************************************************
222 ** Only adjust if the timezones are different
223 ***********************************************************************/
224 IF (l_SourceTimezoneID <> p_dest_tz_id)
225 THEN
226 /*********************************************************************
227 ** Call the API to get the adjusted date (this API is slow..)
228 *********************************************************************/
229 HZ_TIMEZONE_PUB.Get_Time( p_api_version => 1.0
230 , p_init_msg_list => FND_API.G_FALSE
231 , p_source_tz_id => l_SourceTimezoneID
232 , p_dest_tz_id => p_dest_tz_id
233 , p_source_day_time => p_source_day_time
234 , x_dest_day_time => x_dest_day_time
235 , x_return_status => l_return_status
236 , x_msg_count => l_msg_count
237 , x_msg_data => l_msg_data
238 );
239 ELSE
240 x_dest_day_time := p_source_day_time;
241 END IF;
242 ELSE
243 x_dest_day_time := NULL;
244 END IF;
245 END AdjustForTimezone;
246
247 FUNCTION GET_REMINDER_MEANING (p_lookup_code IN VARCHAR2)
248 RETURN VARCHAR2
249 IS
250 CURSOR c_uom (b_lookup_code VARCHAR2) IS
251 SELECT meaning
252 FROM fnd_lookups
253 WHERE lookup_type = 'CAC_VIEW_REMINDER_UOM'
254 AND lookup_code = b_lookup_code
255 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
256 AND TRUNC(NVL(end_date_active, SYSDATE));
257
258 rec_uom c_uom%ROWTYPE;
259 BEGIN
260 OPEN c_uom (p_lookup_code);
261 FETCH c_uom INTO rec_uom;
262 CLOSE c_uom;
263
264 RETURN rec_uom.meaning;
265 END GET_REMINDER_MEANING;
266
267 FUNCTION GET_REMINDER_DESCRIPTION (p_reminder IN NUMBER)
268 RETURN VARCHAR2
269 IS
270 l_week NUMBER;
271 l_day NUMBER;
272 l_hr NUMBER;
273 l_min NUMBER;
274 l_rem NUMBER;
275
276 l_code VARCHAR2(80);
277 l_result_text VARCHAR2(500);
278 BEGIN
279 l_week := floor(p_reminder / (60*24*7));
280
281 l_rem := p_reminder - l_week*7*60*24;
282 l_day := floor(l_rem / (60*24));
283
284 l_rem := l_rem - l_day*60*24;
285 l_hr := floor(l_rem / 60);
286
287 l_rem := l_rem - l_hr*60;
288 l_min := l_rem;
289
290 l_result_text := NULL;
291
292 IF l_week > 0 THEN
293 IF l_week = 1 THEN
294 l_code := 'WEEK';
295 ELSE
296 l_code := 'WEEKS';
297 END IF;
298
299 l_result_text := l_week ||' '|| get_reminder_meaning(l_code);
300 END IF;
301
302 IF l_day > 0 THEN
303 IF l_day = 1 THEN
304 l_code := 'DAY';
305 ELSE
306 l_code := 'DAYS';
307 END IF;
308
309 IF l_result_text IS NULL THEN
310 l_result_text := l_day ||' '|| get_reminder_meaning(l_code);
311 ELSE
312 l_result_text := l_result_text ||' '|| l_day ||' '|| get_reminder_meaning(l_code);
313 END IF;
314 END IF;
315
316 IF l_hr > 0 THEN
317 IF l_hr = 1 THEN
318 l_code := 'HOUR';
319 ELSE
320 l_code := 'HOURS';
321 END IF;
322
323 IF l_result_text IS NULL THEN
324 l_result_text := l_hr ||' '|| get_reminder_meaning(l_code);
325 ELSE
326 l_result_text := l_result_text ||' '|| l_hr ||' '|| get_reminder_meaning(l_code);
327 END IF;
328 END IF;
329
330 IF l_min > 0 THEN
331 l_code := 'MIN';
332
333 IF l_result_text IS NULL THEN
334 l_result_text := l_min ||' '|| get_reminder_meaning(l_code);
335 ELSE
336 l_result_text := l_result_text ||' '|| l_min ||' '|| get_reminder_meaning(l_code);
337 END IF;
338 END IF;
339
340 IF l_result_text IS NOT NULL THEN
341 l_result_text := l_result_text ||' '|| get_reminder_meaning('BEFORE');
342 END IF;
343
344 RETURN l_result_text;
345 END GET_REMINDER_DESCRIPTION;
346
347 PROCEDURE ADJUST_DAYS(p_difference IN NUMBER
348 ,p_sunday IN VARCHAR2
349 ,p_monday IN VARCHAR2
350 ,p_tuesday IN VARCHAR2
351 ,p_wednesday IN VARCHAR2
352 ,p_thursday IN VARCHAR2
353 ,p_friday IN VARCHAR2
354 ,p_saturday IN VARCHAR2
355 ,x_sunday OUT NOCOPY VARCHAR2
356 ,x_monday OUT NOCOPY VARCHAR2
357 ,x_tuesday OUT NOCOPY VARCHAR2
358 ,x_wednesday OUT NOCOPY VARCHAR2
359 ,x_thursday OUT NOCOPY VARCHAR2
360 ,x_friday OUT NOCOPY VARCHAR2
361 ,x_saturday OUT NOCOPY VARCHAR2)
362 IS
363 TYPE days_list IS TABLE OF VARCHAR2(1);
364 l_days days_list;
365 l_days_out days_list;
366 l_pos NUMBER;
367 BEGIN
368 l_days := days_list(p_sunday
369 ,p_monday
370 ,p_tuesday
371 ,p_wednesday
372 ,p_thursday
373 ,p_friday
374 ,p_saturday);
375
376 l_days_out := days_list('N','N','N','N','N','N','N');
377
378 FOR i IN l_days.FIRST..l_days.LAST
379 LOOP
380 IF l_days(i) = 'Y' THEN
381
382 l_pos := i + p_difference;
383
384 IF l_pos < l_days.FIRST THEN
385 l_pos := l_days.LAST;
386 ELSIF l_pos > l_days.LAST THEN
387 l_pos := l_days.FIRST;
388 END IF;
389
390 l_days_out(l_pos) := 'Y';
391 END IF;
392 END LOOP;
393
394 x_sunday := l_days_out(1);
395 x_monday := l_days_out(2);
396 x_tuesday := l_days_out(3);
397 x_wednesday := l_days_out(4);
398 x_thursday := l_days_out(5);
399 x_friday := l_days_out(6);
400 x_saturday := l_days_out(7);
401
402 END ADJUST_DAYS;
403
404 PROCEDURE ADJUST_RECUR_RULE_FOR_TIMEZONE
405 (p_source_tz_id IN NUMBER
406 ,p_dest_tz_id IN NUMBER
407 ,p_base_start_datetime IN DATE
408 ,p_base_end_datetime IN DATE
409 ,p_start_date_active IN DATE
410 ,p_end_date_active IN DATE
411 ,p_occurs_which IN NUMBER
412 ,p_date_of_month IN NUMBER
413 ,p_occurs_month IN NUMBER
414 ,p_sunday IN VARCHAR2
415 ,p_monday IN VARCHAR2
416 ,p_tuesday IN VARCHAR2
417 ,p_wednesday IN VARCHAR2
418 ,p_thursday IN VARCHAR2
419 ,p_friday IN VARCHAR2
420 ,p_saturday IN VARCHAR2
421 ,x_start_date_active OUT NOCOPY DATE
422 ,x_end_date_active OUT NOCOPY DATE
423 ,x_occurs_which OUT NOCOPY NUMBER
424 ,x_date_of_month OUT NOCOPY NUMBER
425 ,x_occurs_month OUT NOCOPY NUMBER
426 ,x_sunday OUT NOCOPY VARCHAR2
427 ,x_monday OUT NOCOPY VARCHAR2
428 ,x_tuesday OUT NOCOPY VARCHAR2
429 ,x_wednesday OUT NOCOPY VARCHAR2
430 ,x_thursday OUT NOCOPY VARCHAR2
431 ,x_friday OUT NOCOPY VARCHAR2
432 ,x_saturday OUT NOCOPY VARCHAR2
433 )
434 IS
435 l_converted_basetime DATE;
436 l_difference NUMBER;
437 l_repeat_start_date DATE;
438 l_repeat_end_date DATE;
439 BEGIN
440 -- Convert base start date time
441 CAC_VIEW_UTIL_PVT.AdjustForTimezone
442 ( p_source_tz_id => p_source_tz_id
443 , p_dest_tz_id => p_dest_tz_id
444 , p_source_day_time => p_base_start_datetime
445 , x_dest_day_time => l_converted_basetime
446 );
447
448 -- Difference of date
449 l_difference := TRUNC(l_converted_basetime) - TRUNC(p_base_start_datetime);
450
451 l_repeat_start_date := TO_DATE(TO_CHAR(p_start_date_active, 'DD-MON-YYYY')||' '||
452 TO_CHAR(p_base_start_datetime, 'HH24:MI:SS'),
453 'DD-MON-YYYY HH24:MI:SS');
454 l_repeat_end_date := TO_DATE(TO_CHAR(p_end_date_active, 'DD-MON-YYYY')||' '||
455 TO_CHAR(p_base_end_datetime, 'HH24:MI:SS'),
456 'DD-MON-YYYY HH24:MI:SS');
457
458 CAC_VIEW_UTIL_PVT.AdjustForTimezone
459 ( p_source_tz_id => p_source_tz_id
460 , p_dest_tz_id => p_dest_tz_id
461 , p_source_day_time => l_repeat_start_date
462 , x_dest_day_time => x_start_date_active
463 );
464
465 CAC_VIEW_UTIL_PVT.AdjustForTimezone
466 ( p_source_tz_id => p_source_tz_id
467 , p_dest_tz_id => p_dest_tz_id
468 , p_source_day_time => l_repeat_end_date
469 , x_dest_day_time => x_end_date_active
470 );
471
472 -- Adjust the day of the week
473 ADJUST_DAYS(l_difference
474 ,p_sunday
475 ,p_monday
476 ,p_tuesday
477 ,p_wednesday
478 ,p_thursday
479 ,p_friday
480 ,p_saturday
481 ,x_sunday
482 ,x_monday
483 ,x_tuesday
484 ,x_wednesday
485 ,x_thursday
486 ,x_friday
487 ,x_saturday
488 );
489
490 IF p_date_of_month IS NOT NULL THEN
491 x_date_of_month := TO_NUMBER(TO_CHAR(l_converted_basetime, 'DD'));
492 END IF;
493
494 -- Adjust the occuring month
495 IF p_occurs_month IS NOT NULL THEN
496 l_difference := TO_NUMBER(TO_CHAR(l_converted_basetime, 'MM'))
497 - TO_NUMBER(TO_CHAR(p_base_start_datetime, 'MM'));
498 x_occurs_month := p_occurs_month + l_difference;
499 END IF;
500
501 -- Adjust occurs_which
502 IF p_occurs_which IS NOT NULL AND p_occurs_which <> 0 THEN
503 x_occurs_which := CEIL(TO_NUMBER(TO_CHAR(l_converted_basetime, 'DD'))/7);
504 IF x_occurs_which = 5
505 THEN
506 x_occurs_which := 99;
507 END IF;
508 END IF;
509
510 END ADJUST_RECUR_RULE_FOR_TIMEZONE;
511
512 FUNCTION GET_DURATION_MEANING (p_lookup_code IN VARCHAR2)
513 RETURN VARCHAR2
514 IS
515 CURSOR c_uom (b_lookup_code VARCHAR2) IS
516 SELECT meaning
517 FROM fnd_lookups
518 WHERE lookup_type = 'CAC_VIEW_DURATION'
519 AND lookup_code = b_lookup_code
520 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
521 AND TRUNC(NVL(end_date_active, SYSDATE));
522
523 rec_uom c_uom%ROWTYPE;
524 BEGIN
525 OPEN c_uom (p_lookup_code);
526 FETCH c_uom INTO rec_uom;
527 CLOSE c_uom;
528
529 RETURN rec_uom.meaning;
530 END GET_DURATION_MEANING;
531
532 FUNCTION GET_DURATION_DESCRIPTION (p_duration IN NUMBER)
533 RETURN VARCHAR2
534 IS
535 l_week NUMBER;
536 l_day NUMBER;
537 l_hr NUMBER;
538 l_min NUMBER;
539 l_dur NUMBER;
540
541 l_code VARCHAR2(80);
542 l_result_text VARCHAR2(500);
543 BEGIN
544
545 l_week := FLOOR(p_duration / (60*24*7));
546
547 l_dur := p_duration - l_week*7*60*24;
548 l_day := FLOOR(l_dur / (60*24));
549
550 l_dur := l_dur - l_day*60*24;
551 l_hr := FLOOR(l_dur / 60);
552
553 l_dur := l_dur - l_hr*60;
554 l_min := l_dur;
555
556 l_result_text := NULL;
557
558 IF l_week > 0 THEN
559 IF l_week = 1 THEN
560 l_code := 'WEK';
561 ELSE
562 l_code := 'WEKS';
563 END IF;
564
565 l_result_text := l_week ||' '|| get_duration_meaning(l_code);
566 END IF;
567
568 IF l_day > 0 THEN
569 IF l_day = 1 THEN
570 l_code := 'DAY';
571 ELSE
572 l_code := 'DAYS';
573 END IF;
574
575 IF l_result_text IS NULL THEN
576 l_result_text := l_day ||' '|| get_duration_meaning(l_code);
577 ELSE
578 l_result_text := l_result_text ||' '|| l_day ||' '|| get_duration_meaning(l_code);
579 END IF;
580 END IF;
581
582 IF l_hr > 0 THEN
583 IF l_hr = 1 THEN
584 l_code := 'HR';
585 ELSE
586 l_code := 'HRS';
587 END IF;
588
589 IF l_result_text IS NULL THEN
590 l_result_text := l_hr ||' '|| get_duration_meaning(l_code);
591 ELSE
592 l_result_text := l_result_text ||' '|| l_hr ||' '|| get_duration_meaning(l_code);
593 END IF;
594 END IF;
595
596 IF l_min > 0 THEN
597 l_code := 'MINS';
598
599 IF l_result_text IS NULL THEN
600 l_result_text := l_min ||' '|| get_duration_meaning(l_code);
601 ELSE
602 l_result_text := l_result_text ||' '|| l_min ||' '|| get_duration_meaning(l_code);
603 END IF;
604 END IF;
605
606 RETURN l_result_text;
607 END GET_DURATION_DESCRIPTION;
608
609 END CAC_VIEW_UTIL_PVT;