[Home] [Help]
PACKAGE BODY: APPS.CAC_VIEW_ACC_DAILY_VIEW_PVT
Source
1 PACKAGE BODY CAC_VIEW_ACC_DAILY_VIEW_PVT as
2 /* $Header: caccadvb.pls 120.6 2008/01/18 09:21:42 anangupt ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | jtfcadvb.pls |
9 | |
10 | DESCRIPTION |
11 | This package is used for accessbility daily view |
12 | |
13 | NOTES |
14 | |
15 | |
16 | Date Developer Change |
17 | ----------- --------------- --------------------------------------- |
18 | 07-Oct-2003 Chan-Ik Jang Created |
19 | 23-Jan-2003 Chan-Ik Jang Added get_related_items |
20 | 28-Jan-2003 Chan-Ik Jang Added get_event_for_detail |
21 | 05-Jan-2003 Chan-Ik Jang Fix the incorrect preference name in |
22 | get_prefix_type |
23 | 10-Jan-2003 Chan-Ik Jang Fix the bug 3433268 |
24 | - make_sentence_weekly |
25 | - make_sentence_monthly |
26 | - make_sentence_yearly |
27 | 30-Apr-2004 Chan-Ik Jang Fix the bug 3600455 |
28 | 08-Jun-2004 Chan-Ik Jang Fix the bug 3667531 |
29 *=======================================================================*/
30
31 /* -----------------------------------------------------------------
32 * -- Function Name: get_start_time
33 * -- Description : This function extracts only time portion of start
34 * -- dates and returns the time as format
35 * -- 'HH12:MI AM'
36 * -- Parameter : p_start_date = Start Date
37 * p_end_date = End Date
38 * -- Return Type : VARCHAR2
39 * -----------------------------------------------------------------*/
40 FUNCTION get_start_time(p_start_date IN DATE
41 ,p_end_date IN DATE)
42 RETURN VARCHAR2
43 IS
44 l_duration NUMBER;
45 l_start_time VARCHAR2(80);
46 BEGIN
47 l_duration := (p_end_date - p_start_date)*24*60;
48 IF l_duration IN (0, 1439) THEN
49 fnd_message.set_name('JTF','CAC_VIEW_APT_ALL_DAY');
50 l_start_time := fnd_message.get;
51 ELSE
52 l_start_time := TO_CHAR(p_start_date, 'HH12:MI AM');
53 END IF;
54
55 RETURN l_start_time;
56 END;
57
58 /* -----------------------------------------------------------------
59 * -- Function Name: get_client_date
60 * -- Description : This function is used to convert source timezone
61 * -- to client timezone defined currently.
62 * -- Parameter : p_server_date = Date for server date
63 * -- p_source_timezone_id = Source Timezone Id
64 * -- Return Type : DATE
65 * -----------------------------------------------------------------*/
66 FUNCTION get_client_date(p_server_date IN DATE
67 ,p_source_timezone_id IN NUMBER)
68 RETURN DATE
69 IS
70 l_client_timezone_id NUMBER;
71 l_client_date DATE;
72 BEGIN
73 l_client_timezone_id := TO_NUMBER(NVL(FND_PROFILE.VALUE('CLIENT_TIMEZONE_ID'),4));
74
75 CAC_VIEW_UTIL_PVT.AdjustForTimezone
76 (p_source_tz_id => p_source_timezone_id
77 ,p_dest_tz_id => l_client_timezone_id
78 ,p_source_day_time => p_server_date
79 ,x_dest_day_time => l_client_date
80 );
81
82 RETURN l_client_date;
83
84 END get_client_date;
85
86 /* -----------------------------------------------------------------
87 * -- Function Name: get_start_date
88 * -- Description : This function determines valid start date among
89 * -- various dates and returns the date
90 * -- Parameter : p_source_object_type_code = Source object Type Code
91 * p_date_selected = Date type selected
92 * p_planned_start_date = Planned start date
93 * p_scheduled_start_date = Scheduled start date
94 * p_actual_start_date = Actual start date
95 * p_calendar_start_date = Calendar start date
96 * -- Return Type : DATE
97 * -----------------------------------------------------------------*/
98 FUNCTION get_start_date(p_source_object_type_code IN VARCHAR2
99 ,p_date_selected IN VARCHAR2
100 ,p_planned_start_date IN DATE
101 ,p_scheduled_start_date IN DATE
102 ,p_actual_start_date IN DATE
103 ,p_calendar_start_date IN DATE
104 )
105 RETURN DATE
106 IS
107 l_start_date DATE;
108 BEGIN
109 IF p_source_object_type_code = 'APPOINTMENT' THEN
110 l_start_date := p_calendar_start_date;
111 ELSIF p_date_selected = 'P' THEN
112 l_start_date := p_planned_start_date;
113 ELSIF p_date_selected = 'S' THEN
114 l_start_date := p_scheduled_start_date;
115 ELSIF p_date_selected = 'A' THEN
116 l_start_date := p_actual_start_date;
117 ELSIF p_date_selected = 'D' THEN
118 l_start_date := p_calendar_start_date;
119 ELSIF p_date_selected IS NULL THEN
120 l_start_date := p_planned_start_date;
121 END IF;
122
123 RETURN l_start_date;
124 END get_start_date;
125
126 /* -----------------------------------------------------------------
127 * -- Function Name: get_end_date
128 * -- Description : This function determines valid end date among
129 * -- various dates and returns the date
130 * -- Parameter : p_source_object_type_code = Source object Type Code
131 * p_date_selected = Date type selected
132 * p_planned_end_date = Planned end date
133 * p_scheduled_end_date = Scheduled end date
134 * p_actual_end_date = Actual end date
135 * p_calendar_end_date = Calendar end date
136 * -- Return Type : DATE
137 * -----------------------------------------------------------------*/
138 FUNCTION get_end_date(p_source_object_type_code IN VARCHAR2
139 ,p_date_selected IN VARCHAR2
140 ,p_planned_end_date IN DATE
141 ,p_scheduled_end_date IN DATE
142 ,p_actual_end_date IN DATE
143 ,p_calendar_end_date IN DATE
144 )
145 RETURN DATE
146 IS
147 l_end_date DATE;
148 BEGIN
149 IF p_source_object_type_code = 'APPOINTMENT' THEN
150 l_end_date := p_calendar_end_date;
151 ELSIF p_date_selected = 'P' THEN
152 l_end_date := p_planned_end_date;
153 ELSIF p_date_selected = 'S' THEN
154 l_end_date := p_scheduled_end_date;
155 ELSIF p_date_selected = 'A' THEN
156 l_end_date := p_actual_end_date;
157 ELSIF p_date_selected = 'D' THEN
158 l_end_date := p_calendar_end_date;
159 ELSIF p_date_selected IS NULL THEN
160 l_end_date := p_planned_end_date;
161 END IF;
162
163 RETURN l_end_date;
164 END get_end_date;
165
166 /* -----------------------------------------------------------------
167 * -- Function Name: get_duration
168 * -- Description : This function determines duration and returns
169 * -- the duration in unit of minutes.
170 * -- Parameter : p_source_object_type_code = Source object Type Code
171 * p_date_selected = Date type selected
172 * p_planned_start_date = Planned start date
173 * p_planned_end_date = Planned end date
174 * p_scheduled_start_date = Scheduled start date
175 * p_scheduled_end_date = Scheduled end date
176 * p_actual_start_date = Actual start date
177 * p_actual_end_date = Actual end date
178 * p_calendar_start_date = Calendar start date
179 * p_calendar_end_date = Calendar end date
180 * -- Return Type : VARCHAR2
181 * -----------------------------------------------------------------*/
182 FUNCTION get_duration(p_source_object_type_code IN VARCHAR2
183 ,p_date_selected IN VARCHAR2
184 ,p_planned_start_date IN DATE
185 ,p_planned_end_date IN DATE
186 ,p_scheduled_start_date IN DATE
187 ,p_scheduled_end_date IN DATE
188 ,p_actual_start_date IN DATE
189 ,p_actual_end_date IN DATE
190 ,p_calendar_start_date IN DATE
191 ,p_calendar_end_date IN DATE
192 )
193 RETURN VARCHAR2
194 IS
195 l_start_date DATE;
196 l_end_date DATE;
197 l_min NUMBER;
198 BEGIN
199 l_start_date := get_start_date(p_source_object_type_code => p_source_object_type_code
200 ,p_date_selected => p_date_selected
201 ,p_planned_start_date => p_planned_start_date
202 ,p_scheduled_start_date => p_scheduled_start_date
203 ,p_actual_start_date => p_actual_start_date
204 ,p_calendar_start_date => p_calendar_start_date
205 );
206 l_end_date := get_end_date(p_source_object_type_code => p_source_object_type_code
207 ,p_date_selected => p_date_selected
208 ,p_planned_end_date => p_planned_end_date
209 ,p_scheduled_end_date => p_scheduled_end_date
210 ,p_actual_end_date => p_actual_end_date
211 ,p_calendar_end_date => p_calendar_end_date
212 );
213
214 l_min := round((l_end_date - l_start_date)*24*60,1);
215 IF l_min = 0 AND
216 trunc(l_start_date) = l_start_date AND
217 trunc(l_end_date) = l_end_date THEN
218 l_min := 1440;
219 END IF;
220
221 RETURN to_duration(l_min);
222 END get_duration;
223
224 /* -----------------------------------------------------------------
225 * -- Function Name: to_duration
226 * -- Description : This function returns the descriptive duration
227 * -- string, ex. 30 Minutes.
228 * -- Parameter : p_duration_min = Duration in minutes
229 * -- Return Type : VARCHAR2
230 * -----------------------------------------------------------------*/
231 FUNCTION to_duration(p_duration_min IN NUMBER)
232 RETURN VARCHAR2
233 IS
234 l_hour NUMBER;
235 l_min NUMBER;
236
237 l_hour_text VARCHAR2(240);
238 l_min_text VARCHAR2(240);
239
240 CURSOR c_time_uom (b_code VARCHAR2) IS
241 SELECT meaning
242 FROM fnd_lookups
243 WHERE lookup_type = 'CAC_VIEW_DURATION'
244 AND lookup_code = b_code;
245
246 l_min_code VARCHAR2(10);
247 l_hour_code VARCHAR2(10);
248
249 l_duration VARCHAR2(240);
250 BEGIN
251 l_min := mod(p_duration_min, 60);
252 l_hour := (p_duration_min - l_min)/60;
253
254 IF l_min > 1 THEN
255 l_min_code := 'MINS';
256 ELSE
257 l_min_code := 'MIN';
258 END IF;
259
260 IF l_hour > 1 THEN
261 l_hour_code := 'HRS';
262 ELSIF l_hour = 1 THEN
263 l_hour_code := 'HR';
264 ELSE
265 l_hour_code := NULL;
266 END IF;
267
268 OPEN c_time_uom (l_min_code);
269 FETCH c_time_uom
270 INTO l_min_text;
271
272 IF c_time_uom%NOTFOUND THEN
273 l_min_text := 'Minutes';
274 END IF;
275
276 CLOSE c_time_uom;
277
278 IF NVL(l_min,0) > 0 THEN
279 l_duration := l_min || ' ' || l_min_text;
280 END IF;
281
282 IF l_hour_code IS NOT NULL THEN
283 OPEN c_time_uom (l_hour_code);
284 FETCH c_time_uom
285 INTO l_hour_text;
286
287 IF c_time_uom%NOTFOUND THEN
288 l_hour_text := 'Hours';
289 END IF;
290
291 CLOSE c_time_uom;
292
293 l_duration := l_hour || ' ' || l_hour_text ||' '|| l_duration;
294 END IF;
295
296 RETURN l_duration;
297 END to_duration;
298
299 /* -----------------------------------------------------------------
300 * -- Function Name: get_reminder
301 * -- Description : This function returns the descriptive reminder
302 * -- string, ex. 30 Minutes Before.
303 * -- Currently the following minutes defined in the
304 * -- lookup type JTF_CALND_REMIND_ME are supported.
305 * --
306 * -- Minute Reminder Text
307 * -- ------- ------------------
308 * -- 0 Do Not Remind Me
309 * -- 5 5 Minutes Before
310 * -- 10 10 Minutes Before
311 * -- 15 15 Minutes Before
312 * -- 30 30 Minutes Before
313 * -- 60 1 Hour Before
314 * -- 120 2 Hours Before
315 * -- 1440 1 Day Before
316 * -- 2880 2 Days Before
317 * -- 4320 3 Days Before
318 * -- 10080 1 Week Before
319 * --
320 * -- Parameter : p_reminder_min = Reminder in minutes
321 * -- Return Type : VARCHAR2
322 * -----------------------------------------------------------------*/
323 FUNCTION get_reminder(p_reminder_min IN NUMBER)
324 RETURN VARCHAR2
325 IS
326 CURSOR c_reminder (b_min VARCHAR2) IS
327 SELECT meaning
328 FROM fnd_lookups
329 WHERE lookup_type = 'JTF_CALND_REMIND_ME'
330 AND lookup_code = b_min;
331
332 l_reminder VARCHAR2(30);
333 l_reminder_string VARCHAR2(240);
337 ELSE
334 BEGIN
335 IF p_reminder_min IS NULL THEN
336 l_reminder := '0';
338 l_reminder := TO_CHAR(p_reminder_min);
339 END IF;
340
341 OPEN c_reminder (l_reminder);
342 FETCH c_reminder
343 INTO l_reminder_string;
344
345 IF c_reminder%NOTFOUND THEN
346 l_reminder_string := NULL;
347 END IF;
348
349 CLOSE c_reminder;
350
351 if l_reminder_string is null and p_reminder_min is not null
352 then
353 l_reminder_string := CAC_VIEW_UTIL_PVT.get_reminder_description(p_reminder_min);
354 end if;
355
356 RETURN l_reminder_string;
357 END get_reminder;
358
359 /* -----------------------------------------------------------------
360 * -- Function Name: get_reminder
361 * -- Description : This function returns the descriptive reminder
362 * -- string according to reminder unit of measuure.
363 * --
364 * -- Parameter : p_reminder = if p_reminder_uom is null,
365 * -- this is considered as minute
366 * -- p_reminder_uom = Unit of Measure for reminder
367 * -- Return Type : VARCHAR2
368 * -----------------------------------------------------------------*/
369 FUNCTION get_reminder(p_reminder IN NUMBER
370 ,p_reminder_uom IN VARCHAR2)
371 RETURN VARCHAR2
372 IS
373 l_reminder_minute NUMBER;
374 BEGIN
375 IF p_reminder_uom IS NULL THEN
376 l_reminder_minute := p_reminder;
377
378 ELSIF p_reminder_uom = 'DAY' THEN
379 l_reminder_minute := p_reminder * 24*60;
380
381 ELSIF p_reminder_uom = 'HOUR' THEN
382 l_reminder_minute := p_reminder * 60;
383
384 ELSE
385 l_reminder_minute := p_reminder;
386 END IF;
387
388 RETURN get_reminder(p_reminder_min => l_reminder_minute);
389 END get_reminder;
390
391 /* -----------------------------------------------------------------
392 * -- Function Name: get_reminder
393 * -- Description : This function returns the descriptive reminder
394 * -- string according to task_id
395 * --
396 * -- Parameter : p_task_id = primary key of task table
397 * -- Return Type : VARCHAR2
398 * -----------------------------------------------------------------*/
399 FUNCTION get_reminder(p_task_id IN NUMBER)
400 RETURN VARCHAR2
401 IS
402 CURSOR c_task IS
403 SELECT alarm_start
404 , alarm_start_uom
405 FROM jtf_tasks_b
406 WHERE task_id = p_task_id;
407
408 l_reminder NUMBER;
409 l_reminder_uom VARCHAR2(30);
410 l_reminder_string VARCHAR2(240);
411 BEGIN
412 OPEN c_task;
413 FETCH c_task INTO l_reminder, l_reminder_uom;
414
415 IF c_task%FOUND THEN
416 l_reminder_string := get_reminder(l_reminder, l_reminder_uom);
417 ELSE
418 l_reminder_string := NULL;
419 END IF;
420 CLOSE c_task;
421
422 RETURN l_reminder_string;
423 END get_reminder;
424
425 /* -----------------------------------------------------------------
426 * -- Function Name: get_attendees
427 * -- Description : This function returns the list of attendees.
428 * -- The attendee names are concatenated as a string.
429 * -- Parameter : p_task_id = Task Id
430 * -- Return Type : VARCHAR2
431 * -----------------------------------------------------------------*/
432 FUNCTION get_attendees(p_task_id IN NUMBER)
433 RETURN VARCHAR2
434 IS
435 CURSOR c_attendees IS
436 SELECT source_first_name||' '||source_last_name attendee_name
437 FROM jtf_rs_resource_extns rs
438 , jtf_task_all_assignments jta
439 WHERE jta.resource_type_code = 'RS_EMPLOYEE'
440 AND jta.assignee_role = 'ASSIGNEE'
441 AND jta.task_id = p_task_id
442 AND rs.category = 'EMPLOYEE'
443 AND rs.resource_id = jta.resource_id
444 UNION
445 SELECT source_first_name||' '||source_last_name attendee_name
446 FROM jtf_rs_resource_extns rs
447 , jtf_rs_group_members rg
448 , jtf_task_all_assignments jta
449 WHERE jta.resource_type_code = 'RS_GROUP'
450 AND jta.assignee_role = 'ASSIGNEE'
451 AND jta.task_id = p_task_id
452 AND rg.group_id = jta.resource_id
453 AND rs.resource_id = rg.resource_id
454 UNION
455 SELECT source_first_name||' '||source_last_name attendee_name
456 FROM jtf_rs_resource_extns rs
457 , jtf_rs_team_members rt_ind
458 , jtf_task_all_assignments jta
459 WHERE jta.resource_type_code = 'RS_TEAM'
460 AND jta.assignee_role = 'ASSIGNEE'
461 AND jta.task_id = p_task_id
462 AND rt_ind.team_id = jta.resource_id
463 AND rt_ind.resource_type = 'INDIVIDUAL'
464 AND rs.resource_id = rt_ind.team_resource_id
465 UNION
466 SELECT source_first_name||' '||source_last_name attendee_name
467 FROM jtf_rs_resource_extns rs
471 WHERE jta.resource_type_code = 'RS_TEAM'
468 , jtf_rs_group_members rg
469 , jtf_rs_team_members rt_grp
470 , jtf_task_all_assignments jta
472 AND jta.assignee_role = 'ASSIGNEE'
473 AND jta.task_id = p_task_id
474 AND rt_grp.team_id = jta.resource_id
475 AND rt_grp.resource_type = 'GROUP'
476 AND rg.group_id = rt_grp.team_resource_id
477 AND rs.resource_id = rg.resource_id;
478
479 l_attendees VARCHAR2(4000);
480 BEGIN
481 FOR rec IN c_attendees
482 LOOP
483 IF l_attendees IS NULL THEN
484 l_attendees := rec.attendee_name;
485 ELSE
486 l_attendees := l_attendees || ', '||rec.attendee_name;
487 END IF;
488 END LOOP;
489
490 RETURN l_attendees;
491 END get_attendees;
492
493 /* -----------------------------------------------------------------
494 * -- Function Name: get_prefix
495 * -- Description : This function checks the preference CAC_VIEW_PREF
496 * -- for the current login user and returns the prefix
497 * -- defined by the user.
498 * -- Parameter : p_preference_name = Preference name
499 * -- Return Type : VARCHAR2
500 * -----------------------------------------------------------------*/
501 FUNCTION get_prefix(p_preference_name IN VARCHAR2)
502 RETURN VARCHAR2
503 IS
504 CURSOR c_prefix IS
505 SELECT preference_value
506 FROM fnd_user_preferences
507 WHERE user_name = fnd_global.user_name
508 AND module_name = 'CAC_VIEW_PREF'
509 AND preference_name = p_preference_name;
510
511 l_prefix VARCHAR2(240);
512 BEGIN
513 OPEN c_prefix;
514 FETCH c_prefix INTO l_prefix;
515 CLOSE c_prefix;
516
517 RETURN l_prefix;
518 END get_prefix;
519
520 /* -----------------------------------------------------------------
521 * -- Function Name: get_prefix_type (Private Function)
522 * -- Description : This function returns the corresponding preference
523 * -- name for the given object code and assignment status id.
524 * -- There are four preference names supported.
525 * -- CAC_VWS_APPT_INV_PREFIX - Prefix for invitation.
526 * -- CAC_VWS_APPT_DECL_PREFIX - Prefix for decliend invitation.
527 * -- CAC_VWS_APPT_PREFIX - Prefix for normal appointment.
528 * -- CAC_VWS_TASK_PREFIX - Prefix for task.
529 * -- Parameter : p_object_code = object code
530 * -- p_assignment_status_id = Assignment status id
531 * -- Return Type : VARCHAR2
532 * -----------------------------------------------------------------*/
533 FUNCTION get_prefix_type (p_object_code IN VARCHAR2
534 ,p_assignment_status_id IN NUMBER)
535 RETURN VARCHAR2
536 IS
537 l_preference_name VARCHAR2(240);
538 BEGIN
539 IF p_object_code = 'APPOINTMENT' THEN
540 IF p_assignment_status_id = 18 THEN
541 l_preference_name := 'CAC_VWS_APPT_INV_PREFIX';
542 ELSIF p_assignment_status_id = 4 THEN
543 l_preference_name := 'CAC_VWS_APPT_DECL_PREFIX';
544 ELSE
545 l_preference_name := 'CAC_VWS_APPT_PREFIX';
546 END IF;
547 ELSE
548 l_preference_name := 'CAC_VWS_TASK_PREFIX';
549 END IF;
550
551 RETURN l_preference_name;
552 END get_prefix_type;
553
554 /* -----------------------------------------------------------------
555 * -- Function Name: get_subject
556 * -- Description : This function returns the subject along with the prefix
557 * -- for event data.
558 * -- Parameter : p_source_code = Source Object Code
559 * -- p_source_id = Source Object Id
560 * -- Return Type : VARCHAR2
561 * -----------------------------------------------------------------*/
562 FUNCTION get_subject(p_source_code IN VARCHAR2
563 ,p_source_id IN NUMBER)
564 RETURN VARCHAR2
565 IS
566 l_object_name VARCHAR2(240);
567 l_prefix VARCHAR2(240);
568 l_subject VARCHAR2(240);
569 BEGIN
570 l_object_name := jtf_task_utl.get_owner(p_object_type_code => p_source_code
571 ,p_object_id => p_source_id);
572 l_prefix := get_prefix(p_preference_name => 'CAC_VWS_EVENT_PREFIX');
573
574 l_subject := l_object_name;
575 IF l_prefix IS NOT NULL THEN
576 l_subject := l_prefix || ' ' || l_subject;
577 END IF;
578
579 RETURN l_subject;
580 END get_subject;
581
582 /* -----------------------------------------------------------------
583 * -- Function Name: get_subject
584 * -- Description : This function returns the subject along with the prefix
585 * -- for appointments.
586 * -- Parameter : p_object_code = Object Code
587 * -- p_object_name = Object Name
588 * -- p_task_id = Task Id
589 * -- p_resource_id = Resource Id
590 * -- Return Type : VARCHAR2
594 ,p_task_id IN NUMBER
591 * -----------------------------------------------------------------*/
592 FUNCTION get_subject(p_object_code IN VARCHAR2
593 ,p_object_name IN VARCHAR2
595 ,p_resource_id IN NUMBER)
596 RETURN VARCHAR2
597 IS
598 CURSOR c_assignment IS
599 SELECT assignment_status_id
600 FROM jtf_task_all_assignments
601 WHERE task_id = p_task_id
602 AND resource_type_code = 'RS_EMPLOYEE'
603 AND resource_id = p_resource_id;
604
605 l_assignment_status_id NUMBER;
606 l_prefix VARCHAR2(240);
607 l_subject VARCHAR2(240);
608 BEGIN
609 OPEN c_assignment;
610 FETCH c_assignment INTO l_assignment_status_id;
611 CLOSE c_assignment;
612
613 l_prefix := get_prefix(get_prefix_type(p_object_code => p_object_code
614 ,p_assignment_status_id => l_assignment_status_id));
615 l_subject := p_object_name;
616 IF l_prefix IS NOT NULL THEN
617 l_subject := l_prefix || ' ' || l_subject;
618 END IF;
619
620 RETURN l_subject;
621 END get_subject;
622
623 /* -----------------------------------------------------------------
624 * -- Function Name: get_weekdays (Private Function)
625 * -- Description : This function returns the descriptive weekdays
626 * -- as string.
627 * -- Parameter : p_sunday = Sunday, Y/N
628 * -- p_monday = Monday, Y/N
629 * -- p_tuesday = Tuesday, Y/N
630 * -- p_wednesday = Wednesday, Y/N
631 * -- p_thursday = Thursday, Y/N
632 * -- p_friday = Friday, Y/N
633 * -- p_saturday = Saturday, Y/N
634 * -- Return Type : VARCHAR2
635 * -----------------------------------------------------------------*/
636 FUNCTION get_weekdays(p_sunday IN VARCHAR2
637 ,p_monday IN VARCHAR2
638 ,p_tuesday IN VARCHAR2
639 ,p_wednesday IN VARCHAR2
640 ,p_thursday IN VARCHAR2
641 ,p_friday IN VARCHAR2
642 ,p_saturday IN VARCHAR2)
643 RETURN VARCHAR2
644 IS
645 CURSOR c_weekdays IS
646 SELECT meaning
647 FROM fnd_lookups
648 WHERE lookup_type = 'JTF_CALND_WEEKDAYS'
649 AND ( (lookup_code = decode(NVL(p_sunday,'N'), 'Y','1','0')) OR
650 (lookup_code = decode(NVL(p_monday,'N'), 'Y','2','0')) OR
651 (lookup_code = decode(NVL(p_tuesday,'N'), 'Y','3','0')) OR
652 (lookup_code = decode(NVL(p_wednesday,'N'),'Y','4','0')) OR
653 (lookup_code = decode(NVL(p_thursday,'N'), 'Y','5','0')) OR
654 (lookup_code = decode(NVL(p_friday,'N'), 'Y','6','0')) OR
655 (lookup_code = decode(NVL(p_saturday,'N'), 'Y','7','0'))
656 )
657 ORDER BY lookup_code;
658
659 l_weekdays VARCHAR2(240);
660 BEGIN
661 FOR rec IN c_weekdays LOOP
662 IF l_weekdays IS NULL THEN
663 l_weekdays := rec.meaning;
664 ELSE
665 l_weekdays := l_weekdays ||', '|| rec.meaning;
666 END IF;
667 END LOOP;
668
669 RETURN l_weekdays;
670 END get_weekdays;
671
672 /* -----------------------------------------------------------------
673 * -- Function Name: get_occurs_month (Private Function)
674 * -- Description : This function returns the descriptive month
675 * -- as string, ex. March
676 * -- Parameter : p_occurs_month = Month as number
677 * -- Return Type : VARCHAR2
678 * -----------------------------------------------------------------*/
679 FUNCTION get_occurs_month(p_occurs_month IN NUMBER)
680 RETURN VARCHAR2
681 IS
682 l_month_var VARCHAR2(80);
683 BEGIN
684 SELECT to_char(to_date('2000-'||to_char(p_occurs_month,'09')||'-01', 'YYYY-MM-DD'),'Month')
685 INTO l_month_var
686 FROM dual;
687
688 RETURN rtrim(l_month_var);
689 EXCEPTION
690 WHEN OTHERS THEN
691 RETURN NULL;
692 END get_occurs_month;
693
694 /* -----------------------------------------------------------------
695 * -- Function Name: make_sentence_daily (Private Function)
696 * -- Description : This function returns the repating information
697 * -- as string when it repeats daily.
698 * -- Parameter : p_occurs_every = Ocurrences Frequencies
699 * -- p_occurs_number= The maximum number of occurrences
700 * -- p_end_date = The date the occurrences ends
701 * -- Return Type : VARCHAR2
702 * -----------------------------------------------------------------*/
703 FUNCTION make_sentence_daily(p_occurs_every IN NUMBER
704 ,p_occurs_number IN NUMBER
705 ,p_end_date IN DATE
706 ,p_timezone IN VARCHAR2)
707 RETURN VARCHAR2
708 IS
709 BEGIN
710 IF p_occurs_number IS NOT NULL THEN
714 ELSE
711 IF p_occurs_every = 1 THEN
712 fnd_message.set_name('JTF', 'CAC_VIEW_DAILY_REPEAT_4');
713 fnd_message.set_token('OCCURS_NUMBER', p_occurs_number);
715 fnd_message.set_name('JTF', 'CAC_VIEW_DAILY_REPEAT_2');
716 fnd_message.set_token('OCCURS_EVERY', p_occurs_every);
717 fnd_message.set_token('OCCURS_NUMBER', p_occurs_number);
718 END IF;
719 ELSE
720 IF p_occurs_every = 1 THEN
721 fnd_message.set_name('JTF', 'CAC_VIEW_DAILY_REPEAT_3');
722 fnd_message.set_token('END_DATE', p_end_date);
723 ELSE
724 fnd_message.set_name('JTF', 'CAC_VIEW_DAILY_REPEAT_1');
725 fnd_message.set_token('OCCURS_EVERY', p_occurs_every);
726 fnd_message.set_token('END_DATE', p_end_date);
727 END IF;
728 END IF;
729 fnd_message.set_token('TIMEZONE', p_timezone);
730
731 RETURN fnd_message.get;
732 END make_sentence_daily;
733
734 /* -----------------------------------------------------------------
735 * -- Function Name: make_sentence_weekly (Private Function)
736 * -- Description : This function returns the repating information
737 * -- as string when it repeats weekly.
738 * -- Parameter : p_occurs_every = Ocurrences Frequencies
739 * -- p_occurs_number= The maximum number of occurrences
740 * -- p_end_date = The date the occurrences ends
741 * -- p_sunday = Sunday, Y/N
742 * -- p_monday = Monday, Y/N
743 * -- p_tuesday = Tuesday, Y/N
744 * -- p_wednesday = Wednesday, Y/N
745 * -- p_thursday = Thursday, Y/N
746 * -- p_friday = Friday, Y/N
747 * -- p_saturday = Saturday, Y/N
748 * -- Return Type : VARCHAR2
749 * -----------------------------------------------------------------*/
750 FUNCTION make_sentence_weekly(p_occurs_every IN NUMBER
751 ,p_occurs_number IN NUMBER
752 ,p_end_date IN DATE
753 ,p_sunday IN VARCHAR2
754 ,p_monday IN VARCHAR2
755 ,p_tuesday IN VARCHAR2
756 ,p_wednesday IN VARCHAR2
757 ,p_thursday IN VARCHAR2
758 ,p_friday IN VARCHAR2
759 ,p_saturday IN VARCHAR2
760 ,p_timezone IN VARCHAR2)
761 RETURN VARCHAR2
762 IS
763 l_weekdays VARCHAR2(240);
764 BEGIN
765 l_weekdays := get_weekdays(p_sunday => p_sunday
766 ,p_monday => p_monday
767 ,p_tuesday => p_tuesday
768 ,p_wednesday => p_wednesday
769 ,p_thursday => p_thursday
770 ,p_friday => p_friday
771 ,p_saturday => p_saturday);
772
773 IF p_occurs_number IS NOT NULL THEN
774 IF p_occurs_every = 1 THEN
775 fnd_message.set_name('JTF', 'CAC_VIEW_WEEKLY_REPEAT_4');
776 fnd_message.set_token('WHICH_DAYS', l_weekdays);
777 fnd_message.set_token('OCCURS_NUMBER', p_occurs_number);
778 ELSE
779 fnd_message.set_name('JTF', 'CAC_VIEW_WEEKLY_REPEAT_2');
780 fnd_message.set_token('OCCURS_EVERY', p_occurs_every);
781 fnd_message.set_token('WHICH_DAYS', l_weekdays);
782 fnd_message.set_token('OCCURS_NUMBER', p_occurs_number);
783 END IF;
784 ELSE
785 IF p_occurs_every = 1 THEN
786 fnd_message.set_name('JTF', 'CAC_VIEW_WEEKLY_REPEAT_3');
787 fnd_message.set_token('WHICH_DAYS', l_weekdays);
788 fnd_message.set_token('END_DATE', p_end_date);
789 ELSE
790 fnd_message.set_name('JTF', 'CAC_VIEW_WEEKLY_REPEAT_1');
791 fnd_message.set_token('OCCURS_EVERY', p_occurs_every);
792 fnd_message.set_token('WHICH_DAYS', l_weekdays);
793 fnd_message.set_token('END_DATE', p_end_date);
794 END IF;
795 END IF;
796 fnd_message.set_token('TIMEZONE', p_timezone);
797
798 RETURN fnd_message.get;
799 END make_sentence_weekly;
800
801 /* -----------------------------------------------------------------
802 * -- Function Name: make_sentence_monthly (Private Function)
803 * -- Description : This function returns the repating information
804 * -- as string when it repeats monthly.
805 * -- Parameter : p_occurs_every = Ocurrences Frequencies
806 * -- p_occurs_number= The maximum number of occurrences
807 * -- p_date_of_month= The date which occurs every month
808 * -- p_occurs_which = The position of the week
809 * -- p_end_date = The date the occurrences ends
810 * -- p_sunday = Sunday, Y/N
811 * -- p_monday = Monday, Y/N
812 * -- p_tuesday = Tuesday, Y/N
816 * -- p_saturday = Saturday, Y/N
813 * -- p_wednesday = Wednesday, Y/N
814 * -- p_thursday = Thursday, Y/N
815 * -- p_friday = Friday, Y/N
817 * -- Return Type : VARCHAR2
818 * -----------------------------------------------------------------*/
819 FUNCTION make_sentence_monthly(p_occurs_every IN NUMBER
820 ,p_occurs_number IN NUMBER
821 ,p_date_of_month IN NUMBER
822 ,p_occurs_which IN NUMBER
823 ,p_end_date IN DATE
824 ,p_sunday IN VARCHAR2
825 ,p_monday IN VARCHAR2
826 ,p_tuesday IN VARCHAR2
827 ,p_wednesday IN VARCHAR2
828 ,p_thursday IN VARCHAR2
829 ,p_friday IN VARCHAR2
830 ,p_saturday IN VARCHAR2
831 ,p_timezone IN VARCHAR2)
832 RETURN VARCHAR2
833 IS
834 CURSOR c_occurs_which IS
835 SELECT lower(meaning)
836 FROM fnd_lookups
837 WHERE lookup_type = 'JTF_TASK_RECUR_OCCURS'
838 AND lookup_code = p_occurs_which;
839
840 l_weekdays VARCHAR2(240);
841 l_occurs_which VARCHAR2(100);
842 BEGIN
843 l_weekdays := get_weekdays(p_sunday => p_sunday
844 ,p_monday => p_monday
845 ,p_tuesday => p_tuesday
846 ,p_wednesday => p_wednesday
847 ,p_thursday => p_thursday
848 ,p_friday => p_friday
849 ,p_saturday => p_saturday);
850
851 OPEN c_occurs_which;
852 FETCH c_occurs_which INTO l_occurs_which;
853 CLOSE c_occurs_which;
854
855 IF p_date_of_month IS NOT NULL THEN
856 IF p_occurs_number IS NOT NULL THEN
857 IF p_occurs_every = 1 THEN
858 fnd_message.set_name('JTF', 'CAC_VIEW_MONTHLY_REPEAT_6');
859 fnd_message.set_token('DATE_OF_MONTH', p_date_of_month);
860 fnd_message.set_token('OCCURS_NUMBER', p_occurs_number);
861 ELSE
862 fnd_message.set_name('JTF', 'CAC_VIEW_MONTHLY_REPEAT_2');
863 fnd_message.set_token('OCCURS_EVERY', p_occurs_every);
864 fnd_message.set_token('DATE_OF_MONTH', p_date_of_month);
865 fnd_message.set_token('OCCURS_NUMBER', p_occurs_number);
866 END IF;
867 ELSE
868 IF p_occurs_every = 1 THEN
869 fnd_message.set_name('JTF', 'CAC_VIEW_MONTHLY_REPEAT_5');
870 fnd_message.set_token('DATE_OF_MONTH', p_date_of_month);
871 fnd_message.set_token('END_DATE', p_end_date);
872 ELSE
873 fnd_message.set_name('JTF', 'CAC_VIEW_MONTHLY_REPEAT_1');
874 fnd_message.set_token('OCCURS_EVERY', p_occurs_every);
875 fnd_message.set_token('DATE_OF_MONTH', p_date_of_month);
876 fnd_message.set_token('END_DATE', p_end_date);
877 END IF;
878 END IF;
879 ELSE
880 IF p_occurs_number IS NOT NULL THEN
881 IF p_occurs_every = 1 THEN
882 fnd_message.set_name('JTF', 'CAC_VIEW_MONTHLY_REPEAT_8');
883 fnd_message.set_token('OCCURS_WHICH', l_occurs_which);
884 fnd_message.set_token('WHICH_DAYS', l_weekdays);
885 fnd_message.set_token('OCCURS_NUMBER', p_occurs_number);
886 ELSE
887 fnd_message.set_name('JTF', 'CAC_VIEW_MONTHLY_REPEAT_4');
888 fnd_message.set_token('OCCURS_EVERY', p_occurs_every);
889 fnd_message.set_token('OCCURS_WHICH', l_occurs_which);
890 fnd_message.set_token('WHICH_DAYS', l_weekdays);
891 fnd_message.set_token('OCCURS_NUMBER', p_occurs_number);
892 END IF;
893 ELSE
894 IF p_occurs_every = 1 THEN
895 fnd_message.set_name('JTF', 'CAC_VIEW_MONTHLY_REPEAT_7');
896 fnd_message.set_token('OCCURS_WHICH', l_occurs_which);
897 fnd_message.set_token('WHICH_DAYS', l_weekdays);
898 fnd_message.set_token('END_DATE', p_end_date);
899 ELSE
900 fnd_message.set_name('JTF', 'CAC_VIEW_MONTHLY_REPEAT_3');
901 fnd_message.set_token('OCCURS_EVERY', p_occurs_every);
902 fnd_message.set_token('OCCURS_WHICH', l_occurs_which);
903 fnd_message.set_token('WHICH_DAYS', l_weekdays);
904 fnd_message.set_token('END_DATE', p_end_date);
905 END IF;
906 END IF;
907 END IF;
908 fnd_message.set_token('TIMEZONE', p_timezone);
909
910 RETURN fnd_message.get;
911 END make_sentence_monthly;
912
913 /* -----------------------------------------------------------------
914 * -- Function Name: make_sentence_yearly (Private Function)
918 * -- p_occurs_number= The maximum number of occurrences
915 * -- Description : This function returns the repating information
916 * -- as string when it repeats yearly.
917 * -- Parameter : p_occurs_every = Ocurrences Frequencies
919 * -- p_occurs_month = The month which occurs every year
920 * -- p_date_of_month= The date of month which occurs every year
921 * -- p_occurs_which = The position of the week
922 * -- p_end_date = The date the occurrences ends
923 * -- p_sunday = Sunday, Y/N
924 * -- p_monday = Monday, Y/N
925 * -- p_tuesday = Tuesday, Y/N
926 * -- p_wednesday = Wednesday, Y/N
927 * -- p_thursday = Thursday, Y/N
928 * -- p_friday = Friday, Y/N
929 * -- p_saturday = Saturday, Y/N
930 * -- Return Type : VARCHAR2
931 * -----------------------------------------------------------------*/
932 FUNCTION make_sentence_yearly(p_occurs_every IN NUMBER
933 ,p_occurs_number IN NUMBER
934 ,p_occurs_month IN NUMBER
935 ,p_date_of_month IN NUMBER
936 ,p_occurs_which IN NUMBER
937 ,p_end_date IN DATE
938 ,p_sunday IN VARCHAR2
939 ,p_monday IN VARCHAR2
940 ,p_tuesday IN VARCHAR2
941 ,p_wednesday IN VARCHAR2
942 ,p_thursday IN VARCHAR2
943 ,p_friday IN VARCHAR2
944 ,p_saturday IN VARCHAR2
945 ,p_timezone IN VARCHAR2)
946 RETURN VARCHAR2
947 IS
948 CURSOR c_occurs_which IS
949 SELECT meaning
950 FROM fnd_lookups
951 WHERE lookup_type = 'JTF_TASK_RECUR_OCCURS'
952 AND lookup_code = p_occurs_which;
953
954 l_weekdays VARCHAR2(240);
955 l_occurs_which VARCHAR2(100);
956 BEGIN
957 l_weekdays := get_weekdays(p_sunday => p_sunday
958 ,p_monday => p_monday
959 ,p_tuesday => p_tuesday
960 ,p_wednesday => p_wednesday
961 ,p_thursday => p_thursday
962 ,p_friday => p_friday
963 ,p_saturday => p_saturday);
964
965 OPEN c_occurs_which;
966 FETCH c_occurs_which INTO l_occurs_which;
967 CLOSE c_occurs_which;
968
969 IF p_date_of_month IS NOT NULL THEN
970 IF p_occurs_number IS NOT NULL THEN
971 IF p_occurs_every = 1 THEN
972 fnd_message.set_name('JTF', 'CAC_VIEW_YEARLY_REPEAT_6');
973 fnd_message.set_token('OCCURS_MONTH', get_occurs_month(p_occurs_month));
974 fnd_message.set_token('DATE_OF_MONTH', p_date_of_month);
975 fnd_message.set_token('OCCURS_NUMBER', p_occurs_number);
976 ELSE
977 fnd_message.set_name('JTF', 'CAC_VIEW_YEARLY_REPEAT_2');
978 fnd_message.set_token('OCCURS_EVERY', p_occurs_every);
979 fnd_message.set_token('OCCURS_MONTH', get_occurs_month(p_occurs_month));
980 fnd_message.set_token('DATE_OF_MONTH', p_date_of_month);
981 fnd_message.set_token('OCCURS_NUMBER', p_occurs_number);
982 END IF;
983 ELSE
984 IF p_occurs_every = 1 THEN
985 fnd_message.set_name('JTF', 'CAC_VIEW_YEARLY_REPEAT_5');
986 fnd_message.set_token('OCCURS_MONTH', get_occurs_month(p_occurs_month));
987 fnd_message.set_token('DATE_OF_MONTH', p_date_of_month);
988 fnd_message.set_token('END_DATE', p_end_date);
989 ELSE
990 fnd_message.set_name('JTF', 'CAC_VIEW_YEARLY_REPEAT_1');
991 fnd_message.set_token('OCCURS_EVERY', p_occurs_every);
992 fnd_message.set_token('OCCURS_MONTH', get_occurs_month(p_occurs_month));
993 fnd_message.set_token('DATE_OF_MONTH', p_date_of_month);
994 fnd_message.set_token('END_DATE', p_end_date);
995 END IF;
996 END IF;
997 ELSE
998 IF p_occurs_number IS NOT NULL THEN
999 IF p_occurs_every = 1 THEN
1000 fnd_message.set_name('JTF', 'CAC_VIEW_YEARLY_REPEAT_8');
1001 fnd_message.set_token('OCCURS_WHICH', l_occurs_which);
1002 fnd_message.set_token('WHICH_DAYS', l_weekdays);
1003 fnd_message.set_token('OCCURS_MONTH', get_occurs_month(p_occurs_month));
1004 fnd_message.set_token('OCCURS_NUMBER', p_occurs_number);
1005 ELSE
1006 fnd_message.set_name('JTF', 'CAC_VIEW_YEARLY_REPEAT_4');
1007 fnd_message.set_token('OCCURS_EVERY', p_occurs_every);
1008 fnd_message.set_token('OCCURS_WHICH', l_occurs_which);
1009 fnd_message.set_token('WHICH_DAYS', l_weekdays);
1013 ELSE
1010 fnd_message.set_token('OCCURS_MONTH', get_occurs_month(p_occurs_month));
1011 fnd_message.set_token('OCCURS_NUMBER', p_occurs_number);
1012 END IF;
1014 IF p_occurs_every = 1 THEN
1015 fnd_message.set_name('JTF', 'CAC_VIEW_YEARLY_REPEAT_7');
1016 fnd_message.set_token('OCCURS_WHICH', l_occurs_which);
1017 fnd_message.set_token('WHICH_DAYS', l_weekdays);
1018 fnd_message.set_token('OCCURS_MONTH', get_occurs_month(p_occurs_month));
1019 fnd_message.set_token('END_DATE', p_end_date);
1020 ELSE
1021 fnd_message.set_name('JTF', 'CAC_VIEW_YEARLY_REPEAT_3');
1022 fnd_message.set_token('OCCURS_EVERY', p_occurs_every);
1023 fnd_message.set_token('OCCURS_WHICH', l_occurs_which);
1024 fnd_message.set_token('WHICH_DAYS', l_weekdays);
1025 fnd_message.set_token('OCCURS_MONTH', get_occurs_month(p_occurs_month));
1026 fnd_message.set_token('END_DATE', p_end_date);
1027 END IF;
1028 END IF;
1029 END IF;
1030 fnd_message.set_token('TIMEZONE', p_timezone);
1031
1032 RETURN fnd_message.get;
1033 END make_sentence_yearly;
1034
1035 /* -----------------------------------------------------------------
1036 * -- Function Name: get_repeating
1037 * -- Description : This function returns the repeating information
1038 * -- as string
1039 * -- Parameter : p_object_type = Ignored
1040 * -- p_recurrence_rule_id = recurrence rule id
1041 * -- Return Type : VARCHAR2
1042 * -----------------------------------------------------------------*/
1043 FUNCTION get_repeating(p_object_type IN VARCHAR2
1044 ,p_recurrence_rule_id IN NUMBER)
1045 RETURN VARCHAR2
1046 IS
1047 CURSOR c_repeating (l_server_timezone_id NUMBER) IS
1048 SELECT occurs_number
1049 , occurs_every
1050 , occurs_uom
1051 , end_date_active
1052 , occurs_which
1053 , date_of_month
1054 , occurs_month
1055 , sunday
1056 , monday
1057 , tuesday
1058 , wednesday
1059 , thursday
1060 , friday
1061 , saturday
1062 , '(GMT '||to_char(trunc(gmt_offset),'S09') || ':' ||to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') || ') ' || name timezone_name
1063 , planned_end_date
1064 FROM jtf_task_recur_rules r
1065 , fnd_timezones_vl tz
1066 , jtf_tasks_vl j
1067 WHERE r.recurrence_rule_id = p_recurrence_rule_id
1068 AND tz.enabled_flag = 'Y'
1069 AND upgrade_tz_id = l_server_timezone_id
1070 and j.recurrence_rule_id=r.recurrence_rule_id;
1071
1072 rec_repeating c_repeating%ROWTYPE;
1073 l_repeating_statement VARCHAR2(1000);
1074 l_client_timezone_id NUMBER;
1075 l_end_date rec_repeating.end_date_active%TYPE;
1076 l_time_hh NUMBER;
1077 l_time_mm NUMBER;
1078 BEGIN
1079 l_client_timezone_id := TO_NUMBER(NVL(fnd_profile.value('CLIENT_TIMEZONE_ID'),'4'));
1080
1081
1082 IF p_recurrence_rule_id IS NULL THEN
1083 fnd_message.set_name('JTF', 'CAC_VIEW_NO_REPEAT');
1084 RETURN fnd_message.get;
1085 END IF;
1086
1087 OPEN c_repeating (l_client_timezone_id);
1088 FETCH c_repeating INTO rec_repeating;
1089 CLOSE c_repeating;
1090
1091 --for bug #4567434 adjusting date to client time zone
1092 l_time_hh := to_number(to_char(rec_repeating.planned_end_date,'HH24'));
1093
1094 l_time_mm := to_number(to_char(rec_repeating.planned_end_date,'MI'));
1095
1096 l_end_date := rec_repeating.end_date_active;
1097 l_end_date := l_end_date+(l_time_hh/24)+(l_time_mm/1440);
1098
1099 l_end_date:=NVL(hz_timezone_pub.convert_datetime(TO_NUMBER(NVL(fnd_profile.value('SERVER_TIMEZONE_ID'),'4'))
1100 ,l_client_timezone_id
1101 ,l_end_date),l_end_date);
1102
1103
1104
1105 IF rec_repeating.occurs_uom = 'DAY' THEN
1106 l_repeating_statement := make_sentence_daily(p_occurs_every => rec_repeating.occurs_every
1107 ,p_occurs_number => rec_repeating.occurs_number
1108 ,p_end_date => l_end_date
1109 ,p_timezone => rec_repeating.timezone_name);
1110
1111 ELSIF rec_repeating.occurs_uom IN ('WEK', 'WK') THEN
1112 l_repeating_statement := make_sentence_weekly(p_occurs_every => rec_repeating.occurs_every
1113 ,p_occurs_number => rec_repeating.occurs_number
1114 ,p_end_date => l_end_date
1115 ,p_sunday => rec_repeating.sunday
1116 ,p_monday => rec_repeating.monday
1117 ,p_tuesday => rec_repeating.tuesday
1121 ,p_saturday => rec_repeating.saturday
1118 ,p_wednesday => rec_repeating.wednesday
1119 ,p_thursday => rec_repeating.thursday
1120 ,p_friday => rec_repeating.friday
1122 ,p_timezone => rec_repeating.timezone_name);
1123
1124 ELSIF rec_repeating.occurs_uom IN ('MON', 'MTH') THEN
1125 l_repeating_statement := make_sentence_monthly(p_occurs_every => rec_repeating.occurs_every
1126 ,p_occurs_number => rec_repeating.occurs_number
1127 ,p_date_of_month => rec_repeating.date_of_month
1128 ,p_occurs_which => rec_repeating.occurs_which
1129 ,p_end_date => l_end_date
1130 ,p_sunday => rec_repeating.sunday
1131 ,p_monday => rec_repeating.monday
1132 ,p_tuesday => rec_repeating.tuesday
1133 ,p_wednesday => rec_repeating.wednesday
1134 ,p_thursday => rec_repeating.thursday
1135 ,p_friday => rec_repeating.friday
1136 ,p_saturday => rec_repeating.saturday
1137 ,p_timezone => rec_repeating.timezone_name);
1138
1139 ELSIF rec_repeating.occurs_uom IN ('YER', 'YR') THEN
1140 l_repeating_statement := make_sentence_yearly(p_occurs_every => rec_repeating.occurs_every
1141 ,p_occurs_number => rec_repeating.occurs_number
1142 ,p_occurs_month => rec_repeating.occurs_month
1143 ,p_date_of_month => rec_repeating.date_of_month
1144 ,p_occurs_which => rec_repeating.occurs_which
1145 ,p_end_date => l_end_date
1146 ,p_sunday => rec_repeating.sunday
1147 ,p_monday => rec_repeating.monday
1148 ,p_tuesday => rec_repeating.tuesday
1149 ,p_wednesday => rec_repeating.wednesday
1150 ,p_thursday => rec_repeating.thursday
1151 ,p_friday => rec_repeating.friday
1152 ,p_saturday => rec_repeating.saturday
1153 ,p_timezone => rec_repeating.timezone_name);
1154
1155 END IF;
1156
1157 RETURN l_repeating_statement;
1158 END get_repeating;
1159
1160 /* -----------------------------------------------------------------
1161 * -- Function Name: get_repeating
1162 * -- Description : This function returns the repeating information
1163 * -- as string
1164 * -- Parameter : p_task_id = task id as NUMBER type
1165 * -- Return Type : VARCHAR2
1166 * -----------------------------------------------------------------*/
1167 FUNCTION get_repeating(p_task_id IN NUMBER)
1168 RETURN VARCHAR2
1169 IS
1170 CURSOR c_task IS
1171 SELECT recurrence_rule_id
1172 FROM jtf_tasks_b
1173 WHERE task_id = p_task_id;
1174
1175 l_recurrence_rule_id NUMBER;
1176 l_repeating_statement VARCHAR2(1000);
1177 BEGIN
1178 OPEN c_task;
1179 FETCH c_task INTO l_recurrence_rule_id;
1180
1181 IF c_task%FOUND THEN
1182 l_repeating_statement := get_repeating(NULL, l_recurrence_rule_id);
1183 ELSE
1184 l_repeating_statement := NULL;
1185 END IF;
1186 CLOSE c_task;
1187
1188 RETURN l_repeating_statement;
1189 END get_repeating;
1190
1191 /* -----------------------------------------------------------------
1192 * -- Function Name: get_destination_uri
1193 * -- Description : This function returns the url information
1194 * -- of the destination page
1195 * -- related to the given object code
1196 * -- Parameter : p_object_code = Object Code
1197 * -- p_object_id = Object id
1198 * -- Return Type : VARCHAR2
1199 * -----------------------------------------------------------------*/
1200 FUNCTION get_destination_uri(p_object_code IN VARCHAR2
1201 ,p_object_id IN NUMBER)
1202 RETURN VARCHAR2
1203 IS
1204 /*
1205 CURSOR c_uri IS
1206 SELECT oa_web_function_name
1207 , oa_web_function_parameters
1208 FROM jtf_objects_b
1209 WHERE object_code = p_object_code;
1210
1211 rec_uri c_uri%ROWTYPE;
1215 BEGIN
1212 */
1213 l_uri VARCHAR2(255);
1214 l_amp VARCHAR2(1);
1216 l_amp := '&';
1217 /*
1218 OPEN c_uri;
1219 FETCH c_uri INTO rec_uri;
1220 IF c_uri%NOTFOUND THEN
1221 CLOSE c_uri;
1222 RETURN NULL;
1223 END IF;
1224 CLOSE c_uri;
1225
1226 RETURN 'OA.jsp?OAFunc='||rec_uri.oa_web_function_name||l_amp||replace(rec_uri.oa_web_function_parameters, l_amp||'ID', p_object_id);
1227 */
1228 IF p_object_code = 'APPOINTMENT' THEN
1229 l_uri := 'OA.jsp?OAFunc=CAC_VIEW_APT_GENERAL'||l_amp||'addBreadCrumb=Y'||l_amp||'cacAptId='||p_object_id;
1230 END IF;
1231
1232 RETURN l_uri;
1233 END get_destination_uri;
1234
1235 /* -----------------------------------------------------------------
1236 * -- Function Name: show_flag
1237 * -- Description : This function returns the indication of whether
1238 * -- the given object should be displayed or not.
1239 * -- related to the given object code
1240 * -- Parameter : p_object_code = Object Code
1241 * -- Return Type : VARCHAR2
1242 * -----------------------------------------------------------------*/
1243 FUNCTION show_flag (p_object_code IN VARCHAR2)
1244 RETURN VARCHAR2
1245 IS
1246 l_preference_name VARCHAR2(240);
1247
1248 CURSOR c_show IS
1249 SELECT preference_value
1250 FROM fnd_user_preferences
1251 WHERE user_name = fnd_global.user_name
1252 AND module_name = 'CAC_VIEW_PREF'
1253 AND preference_name = l_preference_name;
1254
1255 l_show_flag VARCHAR2(1);
1256 BEGIN
1257 IF p_object_code = 'APPOINTMENT' THEN
1258 l_preference_name := 'CAC_VWS_APPT_SHOW';
1259 ELSIF p_object_code = 'TASK' THEN
1260 l_preference_name := 'CAC_VWS_TASK_SHOW';
1261 ELSE
1262 l_preference_name := 'CAC_VWS_EVENT_SHOW';
1263 END IF;
1264
1265 OPEN c_show;
1266 FETCH c_show INTO l_show_flag;
1267 CLOSE c_show;
1268
1269 IF l_show_flag IS NULL
1270 THEN
1271 IF p_object_code = 'APPOINTMENT' THEN
1272 l_show_flag := 'Y';
1273 ELSIF p_object_code = 'TASK' THEN
1274 l_show_flag := 'N';
1275 ELSE
1276 l_show_flag := 'N';
1277 END IF;
1278 END IF;
1279
1280 RETURN l_show_flag;
1281 EXCEPTION
1282 WHEN OTHERS THEN
1283 RETURN 'N';
1284 END show_flag;
1285
1286 /* -----------------------------------------------------------------
1287 * -- Function Name: get_sql
1288 * -- Description : This function returns SQL statement
1289 * -- for the given object type code.
1290 * -- Parameter : p_object_type_code = Object Type Code
1291 * -- Return Type : VARCHAR2
1292 * -----------------------------------------------------------------*/
1293 FUNCTION get_sql (p_object_type_code IN VARCHAR2)
1294 RETURN VARCHAR2
1295 IS
1296 CURSOR c_references IS
1297 SELECT select_id, select_name, from_table, where_clause
1298 FROM jtf_objects_b
1299 WHERE object_code = p_object_type_code;
1300
1301 rec c_references%ROWTYPE;
1302 l_where_clause jtf_objects_b.where_clause%TYPE;
1303 sql_stmt VARCHAR2(2000);
1304 BEGIN
1305 OPEN c_references;
1306 FETCH c_references INTO rec;
1307
1308 IF c_references%NOTFOUND
1309 THEN
1310 sql_stmt := NULL;
1311 ELSE
1312 IF (rec.where_clause IS NULL)
1313 THEN
1314 l_where_clause := ' ';
1315 ELSE
1316 l_where_clause := rec.where_clause || ' AND ';
1317 END IF;
1318
1319 sql_stmt := 'SELECT ' || rec.select_name ||
1320 ' FROM ' || rec.from_table ||
1321 ' WHERE ' || l_where_clause ||
1322 rec.select_id ||' = :object_id AND ROWNUM = 1';
1323 END IF;
1324 CLOSE c_references;
1325
1326 RETURN sql_stmt;
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 RETURN NULL;
1330 END get_sql;
1331
1332 /* -----------------------------------------------------------------
1333 * -- Function Name: get_object_name
1334 * -- Description : This function returns object name
1335 * -- for the given object id.
1336 * -- Parameter : p_sql = SQL statement
1337 * -- p_object_id = object id
1338 * -- Return Type : VARCHAR2
1339 * -----------------------------------------------------------------*/
1340 FUNCTION get_object_name (p_sql IN VARCHAR2
1341 ,p_object_id IN NUMBER)
1342 RETURN VARCHAR2
1343 IS
1344 l_object_name VARCHAR2(255);
1345 BEGIN
1346 EXECUTE IMMEDIATE p_sql
1347 INTO l_object_name
1348 USING p_object_id;
1349
1350 RETURN l_object_name;
1351 EXCEPTION
1352 WHEN OTHERS THEN
1353 RETURN NULL;
1354 END get_object_name;
1355
1359 * -- of items related to the given task id.
1356 /* -----------------------------------------------------------------
1357 * -- Function Name: get_related_items
1358 * -- Description : This function returns the concatednated information
1360 * -- Parameter : p_task_id = Task Id
1361 * -- Return Type : VARCHAR2
1362 * -----------------------------------------------------------------*/
1363 FUNCTION get_related_items (p_task_id IN NUMBER)
1364 RETURN VARCHAR2
1365 IS
1366 CURSOR c_related_items IS
1367 SELECT object_type_code
1368 , object_id
1369 FROM jtf_task_references_b
1370 WHERE task_id = p_task_id
1371 ORDER BY object_type_code;
1372
1373 l_related_items VARCHAR2(1000);
1374 l_object_name VARCHAR2(255);
1375 l_object_type_code VARCHAR2(255);
1376 l_sql VARCHAR2(2000);
1377 BEGIN
1378 l_object_type_code := '###';
1379
1380 FOR rec IN c_related_items
1381 LOOP
1382 IF l_object_type_code <> rec.object_type_code THEN
1383 l_object_type_code := rec.object_type_code;
1384 l_sql := get_sql(l_object_type_code);
1385 END IF;
1386
1387 l_object_name := get_object_name(l_sql, rec.object_id);
1388
1389 IF l_object_name IS NOT NULL
1390 THEN
1391 IF l_related_items IS NULL
1392 THEN
1393 l_related_items := l_object_name;
1394 ELSE
1395 l_related_items := l_related_items || ', ' || l_object_name;
1396 END IF;
1397 END IF;
1398 END LOOP;
1399
1400 RETURN l_related_items;
1401 EXCEPTION
1402 WHEN OTHERS THEN
1403 RETURN NULL;
1404 END get_related_items;
1405
1406 /* -----------------------------------------------------------------
1407 * -- Function Name: get_event_for_detail
1408 * -- Description : This function returns the FireAction event name
1409 * -- related to the given task id and resource id.
1410 * -- Returns INVITE if assignment status id is 18
1411 * -- Returns DEFAULT if assignment status id is NOT 18
1412 * -- Parameter : p_task_id = Task Id
1413 * -- p_resource_id = Resource Id
1414 * -- Return Type : VARCHAR2
1415 * -----------------------------------------------------------------*/
1416 FUNCTION get_event_for_detail (p_task_id IN NUMBER
1417 ,p_resource_id IN NUMBER)
1418 RETURN VARCHAR2
1419 IS
1420 CURSOR c_assignment IS
1421 SELECT assignment_status_id
1422 FROM jtf_task_all_assignments
1423 WHERE task_id = p_task_id
1424 AND resource_id = p_resource_id
1425 AND resource_type_code = 'RS_EMPLOYEE';
1426
1427 l_assignment_status_id NUMBER;
1428 l_event_name VARCHAR2(30);
1429 BEGIN
1430 OPEN c_assignment;
1431 FETCH c_assignment INTO l_assignment_status_id;
1432 CLOSE c_assignment;
1433
1434 IF l_assignment_status_id = 18 THEN
1435 -- if the status is invited
1436 l_event_name := 'INVITE';
1437 ELSE
1438 l_event_name := 'DEFAULT';
1439 END IF;
1440
1441 RETURN l_event_name;
1442 EXCEPTION
1443 WHEN OTHERS THEN
1444 RETURN NULL;
1445 END get_event_for_detail;
1446
1447 END CAC_VIEW_ACC_DAILY_VIEW_PVT;