[Home] [Help]
PACKAGE BODY: APPS.CAC_VIEW_PVT
Source
1 PACKAGE BODY CAC_VIEW_PVT AS
2 /* $Header: cacvpb.pls 120.15 2006/09/19 12:08:41 sankgupt noship $ */
3
4 TYPE MENUS_TBL IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
5
6 FUNCTION get_locations(p_task_id IN NUMBER)
7 RETURN VARCHAR2
8 IS
9 CURSOR c_locations IS
10 SELECT resource_id
11 FROM jtf_task_all_assignments jta
12 WHERE jta.resource_type_code = 'PN_LOCATION'
13 AND jta.task_id = p_task_id;
14
15 CURSOR c_standalone_location IS
16 SELECT location
17 FROM cac_view_collab_details_vl cdv
18 WHERE cdv.task_id = p_task_id;
19
20
21 l_locations VARCHAR2(4000);
22 l_location VARCHAR2(2000);
23 BEGIN
24 FOR rec_std IN c_standalone_location
25 LOOP
26 l_locations := rec_std.location;
27 END LOOP;
28 FOR rec IN c_locations
29 LOOP
30 IF(rec.resource_id IS NOT NULL) THEN
31 l_location := JTF_TASK_UTL.get_owner('PN_LOCATION', rec.resource_id);
32 END IF;
33 IF(l_location IS NOT NULL) THEN
34 IF l_locations IS NULL THEN
35 l_locations := l_location;
36 ELSE
37 l_locations := l_locations || ', '|| l_location;
38 END IF;
39 END IF;
40 END LOOP;
41
42 RETURN l_locations;
43 END;
44
45 PROCEDURE GetCalendarList
46 /*******************************************************************************
47 ** Given a ResourceID, this procedure will return a list of Calendars that the
48 ** Calendar user has access to
49 *******************************************************************************/
50 ( p_api_version IN NUMBER
51 , p_init_msg_list IN VARCHAR2
52 , p_validation_level IN NUMBER
53 , x_return_status OUT NOCOPY VARCHAR2
54 , x_msg_count OUT NOCOPY NUMBER
55 , x_msg_data OUT NOCOPY VARCHAR2
56 , p_resourceID IN OUT NOCOPY NUMBER
57 , p_resourceType IN OUT NOCOPY VARCHAR2
58 , p_userID IN NUMBER
59 , x_calendarList OUT NOCOPY CalLstTblType
60 )
61 IS
62 l_index BINARY_INTEGER;
63 l_ResourceName VARCHAR2(360);
64 l_GranteeKey VARCHAR2(240);
65 /*****************************************************************************
66 ** NOTE: Since we consider the views provided by AOL performance hazards we
67 ** will pick up the data we need directly from their tables
68 *****************************************************************************/
69
70 CURSOR c_PersonalCalendars(b_Grantee_Key IN VARCHAR2)
71 /*****************************************************************************
72 ** This cursor will pick up all Resource Ids of persons the Calendar User has
73 ** access to and the level of access that was granted.
74 ****************************************************************/
75 IS SELECT DISTINCT fmu.menu_name Privilege
76 , fgs.instance_pk1_value ResourceID
77 , jrt.resource_name ResourceName
78 FROM fnd_grants fgs
79 , fnd_menus fmu
80 , fnd_objects fos
81 , jtf_rs_resource_extns_tl jrt
82 WHERE fgs.object_id = fos.object_id -- grants joint to object
83 AND fgs.menu_id = fmu.menu_id -- grants joint to menus
84 AND fos.obj_name = 'JTF_TASK_RESOURCE'
85 AND fgs.grantee_key = b_Grantee_Key
86 AND fgs.grantee_type = 'USER'
87 AND fgs.start_date < SYSDATE
88 AND ( fgs.end_date >= SYSDATE
89 OR fgs.end_date IS NULL
90 )
91 AND fgs.instance_pk2_value = ('RS_EMPLOYEE')
92 --AND to_char(jrt.resource_id) = fgs.instance_pk1_value -- Commented by SBARAT on 23/02/2006 for bug# 5045559
93 AND jrt.resource_id = to_number(fgs.instance_pk1_value) -- Added by SBARAT on 23/02/2006 for bug# 5045559
94 AND jrt.LANGUAGE = USERENV('LANG');
95
96 CURSOR c_GroupCalendars(b_GranteeKey IN VARCHAR2)
97 /*****************************************************************************
98 ** This cursor will pick up all Resource Group Ids of persons the Calendar
99 ** User has the CALENDAR_ADMIN role.
100 *****************************************************************************/
101 IS SELECT DISTINCT DECODE(fmu.menu_name,'JTF_CAL_ADMIN_ACCESS','JTF_CAL_FULL_ACCESS',FMU.MENU_NAME) Privilege
102 , fgs.instance_pk1_value ResourceID
103 , jrt.group_name ResourceName
104 FROM fnd_grants fgs
105 , fnd_menus fmu
106 , fnd_objects fos
107 , jtf_rs_groups_tl jrt
108 WHERE fgs.object_id = fos.object_id -- grants joint to object
109 AND fgs.menu_id = fmu.menu_id -- grants joint to menus
110 AND fmu.MENU_NAME in ('JTF_CAL_ADMIN_ACCESS','JTF_CAL_FULL_ACCESS')
111 AND fos.obj_name = 'JTF_TASK_RESOURCE'
112 AND fgs.grantee_key = b_GranteeKey --'1000001366'
113 AND fgs.grantee_type = 'USER'
114 AND fgs.start_date < SYSDATE
115 AND ( fgs.end_date >= SYSDATE
116 OR fgs.end_date IS NULL
117 )
118 AND fgs.instance_pk2_value = ('RS_GROUP')
119 AND jrt.group_id = TO_NUMBER(fgs.instance_pk1_value)
120 AND jrt.LANGUAGE = USERENV('LANG');
121
122 BEGIN
123 IF fnd_api.to_boolean (NVL(p_init_msg_list,fnd_api.g_false))
124 THEN
125 fnd_msg_pub.initialize;
126 END IF;
127
128 l_index := 1;
129 /*****************************************************************************
130 ** Get basic Resource Information for current FND_USER
131 *****************************************************************************/
132 IF ((p_ResourceID IS NULL) OR (p_ResourceType IS NULL))
133 THEN
134 Jtf_Cal_Utility_Pvt.GetResourceInfo( p_UserID => p_userID
135 , x_ResourceID => p_ResourceID
136 , x_ResourceType => p_ResourceType
137 , x_ResourceName => l_ResourceName
138 );
139 ELSE
140 l_ResourceName := Jtf_Cal_Utility_Pvt.GetResourceName( p_ResourceID
141 , p_ResourceType
142 );
143 END IF;
144
145 /*****************************************************************************
146 ** Determine the GranteeKey
147 *****************************************************************************/
148 l_GranteeKey := TO_CHAR(p_ResourceID);
149
150 /*****************************************************************************
151 ** There is no record in the GRANTS table for CALENDAR ACCESS to your own
152 ** personal calendar in order to simplify the logic on the client side we will
153 ** add a record to the list
154 *****************************************************************************/
155 x_calendarList(l_index).ResourceID := p_resourceID;
156 x_calendarList(l_index).ResourceType := 'RS_EMPLOYEE';
157 x_calendarList(l_index).CalendarName := l_ResourceName;
158 x_calendarList(l_index).AccessLevel := 'JTF_CAL_ADMIN_ACCESS';
159 l_index := l_index + 1;
160
161 /*****************************************************************************
162 ** Get all the Personal Calendars the Calender User has access to.
163 *****************************************************************************/
164 FOR r_PersonalCalendar IN c_PersonalCalendars(l_GranteeKey)
165 LOOP <<PERSONAL_CALENDARS>>
166 x_calendarList(l_index).ResourceID := r_PersonalCalendar.ResourceID;
167 x_calendarList(l_index).ResourceType := 'RS_EMPLOYEE';
168 x_calendarList(l_index).CalendarName := r_PersonalCalendar.ResourceName;
169 x_calendarList(l_index).AccessLevel := r_PersonalCalendar.Privilege;
170 l_index := l_index + 1;
171 END LOOP PERSONAL_CALENDARS;
172
173 /*****************************************************************************
174 ** Get all the Group Calendars the Calender User has access to.
175 *****************************************************************************/
176 FOR r_GroupCalendar IN c_GroupCalendars(l_GranteeKey)
177 LOOP <<GROUP_CALENDARS>>
178 x_calendarList(l_index).ResourceID := r_GroupCalendar.ResourceID;
179 x_calendarList(l_index).ResourceType := 'RS_GROUP';
180 x_calendarList(l_index).CalendarName := r_GroupCalendar.ResourceName;
181 x_calendarList(l_index).AccessLevel := r_GroupCalendar.Privilege;
182 l_index := l_index + 1;
183 END LOOP GROUP_CALENDARS;
184
185 END GetCalendarList;
186
187 FUNCTION GET_ACCESS_LEVEL
188 ( p_entity IN VARCHAR2
189 , p_object_code IN VARCHAR2
190 , p_loggedon_resource_id IN NUMBER
191 , p_loggedon_resource_type IN VARCHAR2
192 , p_query_resource_id IN NUMBER
193 , p_query_resource_type IN VARCHAR2
194 , p_private_flag IN VARCHAR2
195 , p_access_list IN MENUS_TBL
196 ) RETURN NUMBER
197 IS
198 l_ret_value NUMBER;
199 BEGIN
200 l_ret_value := 3;
201 IF (p_entity = 'BOOKING')
202 THEN
203 IF ((p_loggedon_resource_id = p_query_resource_id) AND
204 (p_loggedon_resource_type = p_query_resource_type))
205 THEN
206 l_ret_value := 1;
207 ELSE
208 IF (p_access_list.COUNT > 0)
209 THEN
210 FOR i IN p_access_list.FIRST..p_access_list.LAST
211 LOOP
212 IF (p_access_list(i) = 'CAC_BKG_READ_ONLY_ACCESS')
213 THEN
214 l_ret_value := 1;
215 EXIT;
216 END IF;
217 END LOOP;
218 END IF;
219 END IF;
220 ELSIF (p_entity = 'TASK')
221 THEN
222 IF ((p_loggedon_resource_id = p_query_resource_id) AND
223 (p_loggedon_resource_type = p_query_resource_type))
224 THEN
225 l_ret_value := 2;
226 ELSIF (p_private_flag = 'Y')
227 THEN
228 l_ret_value := 0;
229 ELSE
230 IF (p_access_list.COUNT > 0)
231 THEN
232 FOR i IN p_access_list.FIRST..p_access_list.LAST
233 LOOP
234 IF (p_access_list(i) = 'JTF_TASK_FULL_ACCESS')
235 THEN
236 l_ret_value := 2;
237 EXIT;
238 ELSIF (p_access_list(i) = 'JTF_TASK_READ_ONLY')
239 THEN
240 l_ret_value := 1;
241 -- continue checking
242 END IF;
243 END LOOP;
244 END IF;
245 END IF;
246 ELSIF (p_entity = 'APPOINTMENT')
247 THEN
248 IF ((p_loggedon_resource_id = p_query_resource_id) AND
249 (p_loggedon_resource_type = p_query_resource_type))
250 THEN
251 IF (p_object_code = 'EXTERNAL APPOINTMENT')
252 THEN
253 l_ret_value := 1;
254 ELSE
255 l_ret_value := 2;
256 END IF;
257 ELSIF (p_private_flag = 'Y')
258 THEN
259 l_ret_value := 0;
260 ELSE
261 IF (p_access_list.COUNT > 0)
262 THEN
263 FOR i IN p_access_list.FIRST..p_access_list.LAST
264 LOOP
265 IF (p_access_list(i) = 'JTF_CAL_FULL_ACCESS')
266 THEN
267 IF (p_object_code = 'EXTERNAL APPOINTMENT')
268 THEN
269 l_ret_value := 1;
270 ELSE
271 l_ret_value := 2;
272 END IF;
273 EXIT;
274 ELSIF (p_access_list(i) = 'JTF_CAL_READ_ACCESS')
275 THEN
276 l_ret_value := 1;
277 -- continue checking
278 END IF;
279 END LOOP;
280 END IF;
281 END IF;
282 END IF;
283 RETURN l_ret_value;
284 END GET_ACCESS_LEVEL;
285
286 PROCEDURE GetBookings
287 ( p_LoggedOnRSID IN NUMBER
288 , p_LoggedOnRSType IN VARCHAR2
289 , p_QueryRSID IN NUMBER
290 , p_QueryRSType IN VARCHAR2
291 , p_QueryStartDate IN DATE
292 , p_QueryEndDate IN DATE
293 , p_QueryMode IN VARCHAR2
294 , p_TimezoneId IN NUMBER
295 , p_CalSpanDaysProfile IN VARCHAR2
296 , p_GroupRSID IN VARCHAR2
297 , p_ShowBookings IN CHAR
298 , p_QueryUserAccess IN MENUS_TBL
299 , x_index IN OUT NOCOPY BINARY_INTEGER
300 , x_DisplayItems IN OUT NOCOPY CAC_VIEW_PVT.QueryOutTab
301 )
302 IS
303 l_TempStartDate DATE;
304 l_TempEndDate DATE;
305 l_TempItemDisplayType NUMBER;
306 l_ItemDisplayType NUMBER;
307
308 l_StartDate DATE;
309 l_EndDate DATE;
310 l_NewStartDate DATE;
311 l_NewEndDate DATE;
312
313 l_objects_input jtf_objects_pub.PG_INPUT_REC;
314
315 CURSOR c_Bookings
316 /*****************************************************************************
317 ** This cursor will only return Bookings that need to be displayed
318 ** in the page or is needed to derive that information
319 *****************************************************************************/
320 ( b_ResourceID IN NUMBER
321 , b_ResourceType IN VARCHAR2
322 , b_StartDate IN DATE -- start of query period
323 , b_EndDate IN DATE -- end of query period
324 )IS
325 SELECT task_view.TASK_ID TaskId,
326 booking.ASSIGNEE_ROLE ,
327 booking_status.NAME AS BOOKING_STATUS_NAME,
328 task_type.NAME AS TYPE_NAME,
329 task_priority.NAME AS PRIORITY_NAME,
330 task_view.TASK_NAME ItemName ,
331 task_view.calendar_start_date AS StartDate,
332 task_view.calendar_end_date AS EndDate,
333 task_view.SOURCE_OBJECT_TYPE_CODE SourceObjectTypeCode,
334 task_view.SOURCE_OBJECT_ID SourceId,
335 booking.assignment_status_id AssignmentStatus,
336 freebusy.meaning FREE_BUSY_STATUS,
337 booking.assignee_role AssigneeRole,
338 task_view.alarm_on RemindIndicator,
339 DECODE(task_view.recurrence_rule_id
340 ,NULL,0
341 ,1
342 ) RepeatIndicator,
343 task_view.TIMEZONE_ID TimezoneID,
344 task_view.OWNER_ID,
345 task_view.OWNER_TYPE_CODE,
346 owner.SOURCE_NAME AS OWNER,
347 task_view.PRIVATE_FLAG PrivateFlag ,
348 task_view.DESCRIPTION,
349 jtf_object.name AS SOURCE_NAME,
350 jtf_task_utl.get_owner(task_view.SOURCE_OBJECT_TYPE_CODE, task_view.SOURCE_OBJECT_ID) AS SOURCE_INSTANCE
351 FROM
352 jtf_tasks_vl task_view,
353 jtf_task_statuses_vl booking_status,
354 jtf_task_types_tl task_type,
355 jtf_task_priorities_tl task_priority,
356 jtf_rs_resource_extns owner,
357 jtf_task_all_assignments booking,
358 fnd_lookups freebusy,
359 jtf_objects_tl jtf_object
360 WHERE
361 booking.resource_id = b_ResourceId --10125
362 AND booking.resource_type_code = b_ResourceType --'RS_EMPLOYEE'
363 AND booking.task_id = task_view.task_id
364 --AND task_view.entity = 'BOOKING' -- all bookings
365 AND (NVL(task_view.deleted_flag,'N') = 'N') -- not deleted
366 AND (task_view.calendar_start_date <= b_EndDate --sysdate + 5*360 --start date
367 OR task_view.calendar_start_date is null)
368 AND (task_view.calendar_end_date >= b_StartDate--sysdate - 5*360 --end date
369 OR task_view.calendar_end_date is null)
370 AND task_view.task_type_id = task_type.task_type_id -- type
371 AND task_type.language = userenv('LANG')
372 AND booking.assignment_status_id = booking_status.task_status_id -- booking status
373 AND NVL(booking_status.cancelled_flag, 'N') <>'Y' -- not cancelled
374 AND NVL(booking_status.rejected_flag, 'N') <> 'Y' --not rejected
375 AND task_view.task_priority_id = task_priority.task_priority_id --priority
376 AND task_priority.language = userenv('LANG')
377 AND task_view.owner_id = owner.resource_id --owner
378 AND task_view.owner_type_code = 'RS_' || owner.category
379 AND freebusy.lookup_type = 'CAC_VIEW_FREE_BUSY'
380 AND booking.free_busy_type = freebusy.lookup_code
381 AND task_view.source_object_type_code = jtf_object.object_code
382 AND task_view.source_object_type_code = 'EXTERNAL APPOINTMENT'
383 AND jtf_object.language = userenv('LANG') ;
384
385 BEGIN
386 IF (p_ShowBookings ='Y') THEN
387 IF (c_Bookings%ISOPEN)
388 THEN
389 CLOSE c_Bookings; -- Make sure the cursor is closed
390 END IF;
391 FOR r_Bookings IN c_Bookings( p_QueryRSID
392 , p_QueryRSType
393 , p_QueryStartDate - 1 -- allow for max timezone correction
394 , p_QueryEndDate + 1 -- allow for max timezone correction
395 )
396 LOOP <<BOOKINGS>>
397 /***************************************************************************
398 ** We will have to adjust the Start/End Date for the users timezone
399 ***************************************************************************/
400 l_StartDate := r_Bookings.StartDate;
401 l_EndDate := r_Bookings.EndDate;
402
403 if p_TimezoneId is not null
404 then
405 CAC_VIEW_UTIL_PVT.AdjustForTimezone
406 ( p_source_tz_id => to_number(r_Bookings.TimezoneID)
407 , p_dest_tz_id => p_TimezoneId
408 , p_source_day_time => l_StartDate
409 , x_dest_day_time => l_NewStartDate
410 );
411
412 CAC_VIEW_UTIL_PVT.AdjustForTimezone
413 ( p_source_tz_id => to_number(r_Bookings.TimezoneID)
414 , p_dest_tz_id => p_TimezoneId
415 , p_source_day_time => l_EndDate
416 , x_dest_day_time => l_NewEndDate
417 );
418
419 else
420
421 CAC_VIEW_UTIL_PVT.AdjustForTimezone
422 ( p_source_tz_id => r_Bookings.TimezoneID
423 , p_dest_tz_id => to_number(NVL(FND_PROFILE.Value('CLIENT_TIMEZONE_ID'),'4'))
424 , p_source_day_time => l_StartDate
425 , x_dest_day_time => l_NewStartDate
426 );
427
428 CAC_VIEW_UTIL_PVT.AdjustForTimezone
429 ( p_source_tz_id => r_Bookings.TimezoneID
430 , p_dest_tz_id => to_number(NVL(FND_PROFILE.Value('CLIENT_TIMEZONE_ID'),'4'))
431 , p_source_day_time => l_EndDate
432 , x_dest_day_time => l_NewEndDate
433 );
434
435 end if;
436
437 l_TempItemDisplayType := GetItemType
438 ( p_SourceCode => r_Bookings.SourceObjectTypeCode
439 , p_PeriodStartDate => p_QueryStartDate
440 , p_PeriodEndDate => p_QueryEndDate
441 , p_StartDate => l_NewStartDate
442 , p_EndDate => l_NewEndDate
443 , p_CalSpanDaysProfile => p_CalSpanDaysProfile
444 );
445
446 IF l_TempItemDisplayType <> 3 THEN
447 r_Bookings.Startdate := l_NewStartDate;
448 r_Bookings.Enddate := l_NewEndDate;
449 END IF;
450
451 IF ((p_QueryMode = 4) -- All tasks
452 OR ((p_QueryMode <> 4)
453 AND (l_TempItemDisplayType <> 2))) -- Filter tasks item type 2
454 THEN
455
456 --MultiDay span case
457 l_TempStartDate := r_Bookings.StartDate;
458 l_TempEndDate := r_Bookings.EndDate;
459 l_ItemDisplayType := l_TempItemDisplayType;
460 IF (l_TempItemDisplayType = 5)
461 THEN
462 l_TempEndDate := TRUNC(l_TempStartDate) + 1 - 1/(24*60*60);
463 l_ItemDisplayType := 1;
464
465
466 END IF;
467
468 WHILE (((l_TempEndDate <= r_Bookings.EndDate) AND
469 (l_TempStartDate <= r_Bookings.EndDate)) OR
470 (l_TempStartDate IS NULL) OR (l_TempEndDate IS NULL))
471 LOOP
472 /***************************************************************************
473 ** Now that the StartDate and EndDate are corrected we need to check
474 ** whether we want to display them
475 ***************************************************************************/
476 IF ( ( l_TempStartDate <= p_QueryEndDate
477 OR l_TempStartDate IS NULL
478 )
479 AND ( l_TempEndDate >= p_QueryStartDate
480 OR l_TempEndDate IS NULL
481 )
482 )
483 THEN
484 /*************************************************************************
485 ** Store the task information in a PL/SQL table.
486 *************************************************************************/
487 x_index := x_index + 1;
488 /***********************************************************************
489 ** These items should be displayed on the Calendar or Memo, Todolist
490 ** items are filtered unless query mode is combi
491 ***********************************************************************/
492 x_DisplayItems(x_index).ItemDisplayType := l_ItemDisplayType;
493 x_DisplayItems(x_index).ItemSourceID := r_Bookings.TaskId;
494 x_DisplayItems(x_index).Location := get_locations(r_Bookings.TaskId);
495 x_DisplayItems(x_index).Attendees := cac_view_acc_daily_view_pvt.get_attendees(r_Bookings.TaskId);
496 x_DisplayItems(x_index).ItemSourceCode := 'BOOKING';
497 x_DisplayItems(x_index).SourceObjectTypeCode := r_Bookings.SourceObjectTypeCode;
498 x_DisplayItems(x_index).ItemName := r_Bookings.ItemName;
499 x_DisplayItems(x_index).AccessLevel := GET_ACCESS_LEVEL
500 ( 'BOOKING'
501 , r_Bookings.SourceObjectTypeCode
502 , p_LoggedOnRSID
503 , p_LoggedOnRSType
504 , p_QueryRSID
505 , p_QueryRSType
506 , r_Bookings.PrivateFlag
507 , p_QueryUserAccess
508 );
509
510 x_DisplayItems(x_index).AssignmentStatus := r_Bookings.AssignmentStatus;
511 --Find related items (references). Use "source" column to store the value
512 x_DisplayItems(x_index).Source := r_Bookings.SOURCE_NAME || '-' || r_Bookings.SOURCE_INSTANCE;
513 x_DisplayItems(x_index).TaskType := r_Bookings.TYPE_NAME;
514 x_DisplayItems(x_index).Priority := r_Bookings.PRIORITY_NAME;
515 x_DisplayItems(x_index).Status := r_Bookings.FREE_BUSY_STATUS;
516 IF(r_Bookings.AssigneeRole <> 'OWNER') THEN
517 x_DisplayItems(x_index).InviteIndicator := 1;
518 ELSE
519 x_DisplayItems(x_index).InviteIndicator := 0;
520 END IF;
521 IF(r_Bookings.RemindIndicator = 'Y' AND r_Bookings.AssigneeRole = 'OWNER') THEN
522 x_DisplayItems(x_index).RemindIndicator := 1;
523 ELSE
524 x_DisplayItems(x_index).RemindIndicator := 0;
525 END IF;
526 x_DisplayItems(x_index).RepeatIndicator := r_Bookings.RepeatIndicator;
527 x_DisplayItems(x_index).StartDate := l_TempStartDate;
528 x_DisplayItems(x_index).EndDate := l_TempEndDate;
529 x_DisplayItems(x_index).GroupRSID := p_GroupRSID;
530
531 -- Get Drilldown information
532 l_objects_input.ENTITY := 'BOOKING';
533 l_objects_input.OBJECT_CODE := r_Bookings.SourceObjectTypeCode;
534 l_objects_input.SOURCE_OBJECT_ID := r_Bookings.SourceId;
535 l_objects_input.TASK_ID := r_Bookings.TaskId;
536 jtf_objects_pub.GET_DRILLDOWN_PAGE
537 ( P_INPUT_REC => l_objects_input
538 , X_PG_FUNCTION => x_DisplayItems(x_index).URL
539 , X_PG_PARAMETERS => x_DisplayItems(x_index).URLParamList
540 );
541 END IF;
542 -- Increment the dates.
543 IF (l_TempItemDisplayType = 5)
544 THEN
545 l_TempStartDate := TRUNC(l_TempStartDate) + 1;
546 l_TempEndDate := TRUNC(l_TempStartDate) + 1 - 1/(24*60*60);
547 IF (l_TempEndDate > r_Bookings.EndDate)
548 THEN
549 l_TempEndDate := r_Bookings.EndDate;
550 END IF;
551 IF (l_TempStartDate >= r_Bookings.EndDate)
552 THEN
553 EXIT;
554 END IF;
555 ELSE
556 EXIT;
557 END IF;
558 END LOOP;
559
560 END IF; --Filter tasks 2
561
562 END LOOP BOOKINGS;
563 END IF; -- end of IF (p_ShowBookings)
564
565 END GetBookings;
566
567 PROCEDURE GetApptsAndTasks
568 ( p_LoggedOnRSID IN NUMBER
569 , p_LoggedOnRSType IN VARCHAR2
570 , p_QueryRSID IN NUMBER
571 , p_QueryRSType IN VARCHAR2
572 , p_QueryStartDate IN DATE
573 , p_QueryEndDate IN DATE
574 , p_QueryMode IN VARCHAR2
575 , p_CalSpanDaysProfile IN VARCHAR2
576 , p_GroupRSID IN VARCHAR2
577 , p_ShowApts IN CHAR
578 , p_ShowTasks IN CHAR
579 , p_ShowOpenInvite IN CHAR
580 , p_ShowDeclined IN CHAR
581 , p_AptFirstDetail IN CHAR
582 , p_AptSecondDetail IN CHAR
583 , p_AptThirdDetail IN CHAR
584 , p_ShowBusyTask IN CHAR
585 , p_ShowFreeTask IN CHAR
586 , p_ShowTentativeTask IN CHAR
587 , p_TaskFirstDetail IN CHAR
588 , p_TaskSecondDetail IN CHAR
589 , p_TaskThirdDetail IN CHAR
590 , p_TimezoneId IN NUMBER
591 , p_pers_cal IN VARCHAR2
592 , p_QueryUserAccess IN MENUS_TBL
593 , x_index IN OUT NOCOPY BINARY_INTEGER
594 , x_DisplayItems IN OUT NOCOPY CAC_VIEW_PVT.QueryOutTab
595 ) IS
596
597 l_TempStartDate DATE;
598 l_TempEndDate DATE;
599 l_TempItemDisplayType NUMBER;
600 l_ItemDisplayType NUMBER;
601
602 l_StartDate DATE;
603 l_EndDate DATE;
604 l_NewStartDate DATE;
605 l_NewEndDate DATE;
606
607 l_objects_input jtf_objects_pub.PG_INPUT_REC;
608
609 CURSOR c_Appointments
610 /*****************************************************************************
611 ** This cursor will only return Tasks/Appointments that need to be displayed
612 ** in the page or is needed to derive that information
613 *****************************************************************************/
614 ( b_ResourceID IN NUMBER
615 , b_ResourceType IN VARCHAR2
616 , b_StartDate IN DATE -- start of query period
617 , b_EndDate IN DATE -- end of query period
618 )IS SELECT jtb.task_id TaskId
619 , jtb.source_object_type_code SourceObjectTypeCode
620 , jtb.source_object_id SourceId
621 , jtb.customer_id CustomerId
622 , jtt.task_name ItemName
623 , jtb.calendar_start_date StartDate
624 , jtb.calendar_end_date EndDate
625 , jtb.timezone_id TimezoneID
626 , jtb.duration Duration -- always in minutes
627 , jtb.private_flag PrivateFlag -- needed to determin access level
628 , jta.assignment_status_id AssignmentStatus
629 , jtb.alarm_on RemindIndicator
630 , DECODE(jtb.recurrence_rule_id
631 ,NULL,0
632 ,1
633 ) RepeatIndicator
634 , jta.assignee_role AssigneeRole
635 , jta.free_busy_type free_busy_type
636 FROM jtf_task_all_assignments jta
637 , jtf_tasks_b jtb
638 , jtf_tasks_tl jtt
639 , jtf_task_statuses_b jtsb
640 WHERE jta.resource_id = b_ResourceID -- 101272224
641 AND jta.resource_type_code = b_ResourceType -- 'RS_EMPLOYEE'
642 AND jta.task_id = jtb.task_id -- join to tasks_b
643 AND jtb.task_status_id = jtsb.task_status_id -- join to task_status_b
644 AND jtb.task_id = jtt.task_id -- join to tasks_tl
645 AND jtt.LANGUAGE = USERENV('LANG') -- join to tasks_tl
646 AND jta.show_on_calendar = 'Y'
647 AND (p_ShowDeclined = 'Y' AND (jta.assignment_status_id IN (3,4,18))
648 OR (p_ShowDeclined = 'N' AND jta.assignment_status_id IN (3,18)))
649 AND (p_ShowOpenInvite = 'Y' AND (jta.assignment_status_id IN (3,4,18))
650 OR (p_ShowOpenInvite = 'N' AND jta.assignment_status_id IN (3,4)))
651 AND NVL(jtb.deleted_flag,'N')<> 'Y'
652 AND ( jtb.calendar_start_date <= b_EndDate
653 OR jtb.calendar_start_date IS NULL
654 )
655 AND ( jtb.calendar_end_date >= b_StartDate
656 OR jtb.calendar_end_date IS NULL
657 )
658 AND jtb.entity = 'APPOINTMENT'
659 AND jtb.source_object_type_code <> 'EXTERNAL APPOINTMENT'
660 ;
661 --
662 /*CURSOR c_Tasks
663 /*****************************************************************************
664 ** This cursor will only return Tasks/Appointments that need to be displayed
665 ** in the page or is needed to derive that information
666 *****************************************************************************/
667 /*( b_ResourceID IN NUMBER
668 , b_ResourceType IN VARCHAR2
669 , b_StartDate IN DATE -- start of query period
670 , b_EndDate IN DATE -- end of query period
671 )IS SELECT jtb.task_id TaskId
672 , jtb.source_object_type_code SourceObjectTypeCode
673 , jtb.source_object_id SourceId
674 , jtb.customer_id CustomerId
675 , jtt.task_name ItemName
676 , jtb.calendar_start_date StartDate
677 , jtb.calendar_end_date EndDate
678 , jtb.timezone_id TimezoneID
679 , jtb.duration Duration -- always in minutes
680 , jtb.private_flag PrivateFlag -- needed to determin access level
681 , DECODE(jtb.recurrence_rule_id
682 ,NULL,0
683 ,1
684 ) RepeatIndicator
685 , jta.assignee_role AssigneeRole
686 FROM jtf_task_all_assignments jta
687 , jtf_tasks_b jtb
688 , jtf_tasks_tl jtt
689 , jtf_task_statuses_b jtsb
690 WHERE jta.resource_id = b_ResourceID -- 101272224
691 AND jta.resource_type_code = b_ResourceType -- 'RS_EMPLOYEE'
692 AND jta.task_id = jtb.task_id -- join to tasks_b
693 AND jtb.task_status_id = jtsb.task_status_id -- join to task_status_b
694 AND jtb.task_id = jtt.task_id -- join to tasks_tl
695 AND jtt.LANGUAGE = USERENV('LANG') -- join to tasks_tl
696 AND jta.show_on_calendar = 'Y'
697 AND jta.assignment_status_id <> 4 -- using status rejected for declined
698 AND NVL(jtsb.closed_flag,'N')<> 'Y'
699 AND NVL(jtb.deleted_flag,'N')<> 'Y'
700 AND ( jtb.calendar_start_date <= b_EndDate
701 OR jtb.calendar_start_date IS NULL
702 )
703 AND ( jtb.calendar_end_date >= b_StartDate
704 OR jtb.calendar_end_date IS NULL
705 )
706 AND jtb.source_object_type_code <> 'APPOINTMENT'
707 ;*/
708
709 CURSOR c_Tasks
710 (
711 b_ResourceId IN NUMBER
712 , b_ResourceType IN VARCHAR2
713 , b_StartDate IN DATE -- start of query period
714 , b_EndDate IN DATE -- end of query period
715 )
716 IS
717 SELECT /*+ first_rows */ task_view.TASK_ID,
718 assignment.TASK_ASSIGNMENT_ID,
719 assignment.ASSIGNEE_ROLE,
720 task_status.NAME AS STATUS_NAME,
721 assign_status.NAME AS ASSIGN_STATUS_NAME,
722 task_type.NAME AS TYPE_NAME,
723 task_priority.NAME AS PRIORITY_NAME,
724 task_view.TASK_NAME,
725 --task_view.CALENDAR_START_DATE,
726 --task_view.CALENDAR_END_DATE,
727 assignment.BOOKING_START_DATE CALENDAR_START_DATE,
728 assignment.BOOKING_END_DATE CALENDAR_END_DATE,
729 task_view.SOURCE_OBJECT_TYPE_CODE,
730 task_view.SOURCE_OBJECT_ID,
731 task_view.CUSTOMER_ID,
732 task_view.TASK_CONFIRMATION_STATUS,
733 DECODE(task_view.recurrence_rule_id
734 ,NULL,0
735 ,1
736 ) RepeatIndicator,
737 task_view.TIMEZONE_ID,
738 task_view.OWNER_ID,
739 task_view.OWNER_TYPE_CODE,
740 owner.SOURCE_NAME AS OWNER,
741 party.PARTY_NAME AS CUSTOMER_NAME,
742 location.CITY,
743 task_view.PRIVATE_FLAG,
744 task_view.DESCRIPTION,
745 jtf_object.name AS SOURCE_NAME,
746 jtf_task_utl.get_owner(task_view.SOURCE_OBJECT_TYPE_CODE, task_view.SOURCE_OBJECT_ID) AS SOURCE_INSTANCE,
747 assignment.free_busy_type free_busy_type
748 FROM
749 jtf_tasks_vl task_view,
750 jtf_task_statuses_tl task_status,
751 jtf_task_statuses_vl assign_status,
752 jtf_task_types_tl task_type,
753 jtf_task_priorities_tl task_priority,
754 hz_parties party,
755 hz_party_sites site,
756 hz_locations location,
757 jtf_rs_resource_extns owner,
758 jtf_task_all_assignments assignment,
759 jtf_objects_tl jtf_object
760 WHERE
761 assignment.resource_id = b_ResourceId --13475
762 AND assignment.resource_type_code = b_ResourceType --'RS_EMPLOYEE'
763 AND assignment.task_id = task_view.task_id
764 AND task_view.entity = 'TASK'
765 AND task_view.task_type_id <> 22 -- not escalations
766 AND (NVL(task_view.deleted_flag,'N') = 'N') -- not deleted
767 AND (NVL(assignment.show_on_calendar,'Y') = 'Y') -- for backward compatibility
768 AND assignment.booking_start_date <= b_EndDate --sysdate + 5*360 --start date
769 AND assignment.booking_end_date >= b_StartDate --sysdate - 5*360 --end date
770 AND task_view.task_type_id = task_type.task_type_id -- type
771 AND task_type.language = userenv('LANG')
772 AND task_view.task_status_id = task_status.task_status_id -- task status
773 AND task_status.language = userenv('LANG')
774 AND assignment.assignment_status_id = assign_status.task_status_id -- assignment status
775 AND NVL(assign_status.cancelled_flag, 'N') <>'Y' -- not cancelled
776 AND task_view.task_priority_id = task_priority.task_priority_id --priority
777 AND task_priority.language = userenv('LANG')
778 AND task_view.customer_id = party.party_id(+) --customer
779 AND task_view.address_id = site.party_site_id(+) --task location
780 AND site.location_id = location.location_id(+)
781 AND task_view.source_object_type_code = jtf_object.object_code --source
782 AND jtf_object.language = userenv('LANG')
783 AND task_view.owner_id = owner.resource_id --owner
784 AND task_view.owner_type_code = 'RS_' || owner.category
785 AND assignment.ASSIGNEE_ROLE= decode (1, (select count(task_assignment_id)
786 from jtf_task_all_assignments
787 where task_id=task_view.task_id
788 and resource_id = b_ResourceId
789 and resource_type_code=b_ResourceType),
790 assignment.ASSIGNEE_ROLE,'ASSIGNEE')
791 AND assignment.free_busy_type <> 'FREE' ;
792
793 --
794
795 BEGIN
796 IF (p_ShowApts ='Y') THEN
797 IF (c_Appointments%ISOPEN)
798 THEN
799 CLOSE c_Appointments; -- Make sure the cursor is closed
800 END IF;
801 FOR r_PersonalApt IN c_Appointments( p_QueryRSID
802 , p_QueryRSType
803 , p_QueryStartDate - 1 -- allow for max timezone correction
804 , p_QueryEndDate + 1 -- allow for max timezone correction
805 )
806 LOOP <<PERSONAL_APTS>>
807 /***************************************************************************
808 ** We will have to adjust the Start/End Date for the users timezone
809 ***************************************************************************/
810 l_StartDate := r_PersonalApt.StartDate;
811 l_EndDate := r_PersonalApt.EndDate;
812
813 if p_TimezoneId is not null
814 then
815 CAC_VIEW_UTIL_PVT.AdjustForTimezone
816 ( p_source_tz_id => to_number(r_PersonalApt.TimezoneID)
817 , p_dest_tz_id => p_TimezoneId
818 , p_source_day_time => l_StartDate
819 , x_dest_day_time => l_NewStartDate
820 );
821
822 CAC_VIEW_UTIL_PVT.AdjustForTimezone
823 ( p_source_tz_id => to_number(r_PersonalApt.TimezoneID)
824 , p_dest_tz_id => p_TimezoneId
825 , p_source_day_time => l_EndDate
826 , x_dest_day_time => l_NewEndDate
827 );
828
829 else
830 CAC_VIEW_UTIL_PVT.AdjustForTimezone
831 ( p_source_tz_id => r_PersonalApt.TimezoneID
832 , p_dest_tz_id => to_number(NVL(FND_PROFILE.Value('CLIENT_TIMEZONE_ID'),'4'))
833 , p_source_day_time => l_StartDate
834 , x_dest_day_time => l_NewStartDate
835 );
836
837 CAC_VIEW_UTIL_PVT.AdjustForTimezone
838 ( p_source_tz_id => r_PersonalApt.TimezoneID
839 , p_dest_tz_id => to_number(NVL(FND_PROFILE.Value('CLIENT_TIMEZONE_ID'),'4'))
840 , p_source_day_time => l_EndDate
841 , x_dest_day_time => l_NewEndDate
842 );
843
844 end if;
845
846 l_TempItemDisplayType := GetItemType
847 ( p_SourceCode => r_PersonalApt.SourceObjectTypeCode
848 , p_PeriodStartDate => p_QueryStartDate
849 , p_PeriodEndDate => p_QueryEndDate
850 , p_StartDate => l_NewStartDate
851 , p_EndDate => l_NewEndDate
852 , p_CalSpanDaysProfile => p_CalSpanDaysProfile
853 );
854
855
856
857
858 IF l_TempItemDisplayType <> 3 THEN
859 r_PersonalApt.Startdate := l_NewStartDate;
860 r_PersonalApt.Enddate := l_NewEndDate;
861 END IF;
862
863
864
865 IF ((p_QueryMode = 4) -- All tasks
866 OR ((p_QueryMode <> 4)
867 AND (l_TempItemDisplayType <> 2))) -- Filter tasks item type 2
868 THEN
869
870 --MultiDay span case
871 l_TempStartDate := r_PersonalApt.StartDate;
872 l_TempEndDate := r_PersonalApt.EndDate;
873 l_ItemDisplayType := l_TempItemDisplayType;
874 IF (l_TempItemDisplayType = 5)
875 THEN
876 l_TempEndDate := TRUNC(l_TempStartDate) + 1 - 1/(24*60*60);
877 l_ItemDisplayType := 1;
878
879
880 END IF;
881
882 WHILE (((l_TempEndDate <= r_PersonalApt.EndDate) AND
883 (l_TempStartDate <= r_PersonalApt.EndDate)) OR
884 (l_TempStartDate IS NULL) OR (l_TempEndDate IS NULL))
885 LOOP
886 /***************************************************************************
887 ** Now that the StartDate and EndDate are corrected we need to check
888 ** whether we want to display them
889 ***************************************************************************/
890 IF ( ( l_TempStartDate <= p_QueryEndDate
891 OR l_TempStartDate IS NULL
892 )
893 AND ( l_TempEndDate >= p_QueryStartDate
894 OR l_TempEndDate IS NULL
895 )
896 )
897 THEN
898 /*************************************************************************
899 ** Store the task information in a PL/SQL table.
900 *************************************************************************/
901 x_index := x_index + 1;
902 /***********************************************************************
903 ** These items should be displayed on the Calendar or Memo, Todolist
904 ** items are filtered unless query mode is combi
905 ***********************************************************************/
906 x_DisplayItems(x_index).ItemDisplayType := l_ItemDisplayType;
907 x_DisplayItems(x_index).ItemSourceID := r_PersonalApt.TaskId;
908 x_DisplayItems(x_index).Location := get_locations(r_PersonalApt.TaskId);
909 x_DisplayItems(x_index).Attendees := cac_view_acc_daily_view_pvt.get_attendees(r_PersonalApt.TaskId);
910 x_DisplayItems(x_index).ItemSourceCode := 'APPOINTMENT';
911 x_DisplayItems(x_index).SourceObjectTypeCode := r_PersonalApt.SourceObjectTypeCode;
912 x_DisplayItems(x_index).ItemName := r_PersonalApt.ItemName;
913 x_DisplayItems(x_index).AccessLevel := GET_ACCESS_LEVEL
914 ( 'APPOINTMENT'
915 , r_PersonalApt.SourceObjectTypeCode
916 , p_LoggedOnRSID
917 , p_LoggedOnRSType
918 , p_QueryRSID
919 , p_QueryRSType
920 , r_PersonalApt.PrivateFlag
921 , p_QueryUserAccess
922 );
923
924 x_DisplayItems(x_index).AssignmentStatus := r_PersonalApt.AssignmentStatus;
925 --Find related items (references). Use "source" column to store the value
926 x_DisplayItems(x_index).Source := cac_view_acc_daily_view_pvt.get_related_items(r_PersonalApt.TaskId);
927 IF(r_PersonalApt.AssigneeRole <> 'OWNER') THEN
928 x_DisplayItems(x_index).InviteIndicator := 1;
929 ELSE
930 x_DisplayItems(x_index).InviteIndicator := 0;
931 END IF;
932 IF(r_PersonalApt.RemindIndicator = 'Y' AND r_PersonalApt.AssigneeRole = 'OWNER') THEN
933 x_DisplayItems(x_index).RemindIndicator := 1;
934 ELSE
935 x_DisplayItems(x_index).RemindIndicator := 0;
936 END IF;
937 x_DisplayItems(x_index).RepeatIndicator := r_PersonalApt.RepeatIndicator;
938 x_DisplayItems(x_index).StartDate := l_TempStartDate;
939 x_DisplayItems(x_index).EndDate := l_TempEndDate;
940 x_DisplayItems(x_index).GroupRSID := p_GroupRSID;
941 x_DisplayItems(x_index).FreeBusyType := r_PersonalApt.free_busy_type;
942
943 -- Get Drilldown information
944 l_objects_input.ENTITY := 'APPOINTMENT';
945 l_objects_input.OBJECT_CODE := r_PersonalApt.SourceObjectTypeCode;
946 l_objects_input.SOURCE_OBJECT_ID := r_PersonalApt.SourceId;
947 l_objects_input.TASK_ID := r_PersonalApt.TaskId;
948 jtf_objects_pub.GET_DRILLDOWN_PAGE
949 ( P_INPUT_REC => l_objects_input
950 , X_PG_FUNCTION => x_DisplayItems(x_index).URL
951 , X_PG_PARAMETERS => x_DisplayItems(x_index).URLParamList
952 );
953 END IF;
954 -- Increment the dates.
955 IF (l_TempItemDisplayType = 5)
956 THEN
957 l_TempStartDate := TRUNC(l_TempStartDate) + 1;
958 l_TempEndDate := TRUNC(l_TempStartDate) + 1 - 1/(24*60*60);
959 IF (l_TempEndDate > r_PersonalApt.EndDate)
960 THEN
961 l_TempEndDate := r_PersonalApt.EndDate;
962 END IF;
963 IF (l_TempStartDate >= r_PersonalApt.EndDate)
964 THEN
965 EXIT;
966 END IF;
967 ELSE
968 EXIT;
969 END IF;
970 END LOOP;
971
972 END IF; --Filter tasks 2
973
974 END LOOP PERSONAL_APTS;
975 END IF; -- end of IF (p_ShowApts)
976 --
977 IF (p_ShowTasks ='Y') THEN
978 IF (c_Tasks%ISOPEN)
979 THEN
980 CLOSE c_Tasks; -- Make sure the cursor is closed
981 END IF;
982 FOR r_PersonalTask IN c_Tasks( p_QueryRSID
983 , p_QueryRSType
984 , p_QueryStartDate - 1 -- allow for max timezone correction
985 , p_QueryEndDate + 1 -- allow for max timezone correction
986 )
987 LOOP <<PERSONAL_TASKS>>
988 /***************************************************************************
989 ** We will have to adjust the Start/End Date for the users timezone
990 ***************************************************************************/
991 l_StartDate := r_PersonalTask.CALENDAR_START_DATE;
992 l_EndDate := r_PersonalTask.CALENDAR_END_DATE;
993
994 if p_TimezoneId is not null
995 then
996 CAC_VIEW_UTIL_PVT.AdjustForTimezone
997 ( p_source_tz_id => to_number(r_PersonalTask.TIMEZONE_ID)
998 , p_dest_tz_id => p_TimezoneId
999 , p_source_day_time => l_StartDate
1000 , x_dest_day_time => l_NewStartDate
1001 );
1002
1003 CAC_VIEW_UTIL_PVT.AdjustForTimezone
1004 ( p_source_tz_id => to_number(r_PersonalTask.TIMEZONE_ID)
1005 , p_dest_tz_id => p_TimezoneId
1006 , p_source_day_time => l_EndDate
1007 , x_dest_day_time => l_NewEndDate
1008 );
1009
1010 else
1011 CAC_VIEW_UTIL_PVT.AdjustForTimezone
1012 ( p_source_tz_id => r_PersonalTask.TIMEZONE_ID
1013 , p_dest_tz_id => to_number(NVL(FND_PROFILE.Value('CLIENT_TIMEZONE_ID'),'4'))
1014 , p_source_day_time => l_StartDate
1015 , x_dest_day_time => l_NewStartDate
1016 );
1017
1018 CAC_VIEW_UTIL_PVT.AdjustForTimezone
1019 ( p_source_tz_id => r_PersonalTask.TIMEZONE_ID
1020 , p_dest_tz_id => to_number(NVL(FND_PROFILE.Value('CLIENT_TIMEZONE_ID'),'4'))
1021 , p_source_day_time => l_EndDate
1022 , x_dest_day_time => l_NewEndDate
1023 );
1024
1025 end if;
1026
1027
1028 l_TempItemDisplayType := GetItemType
1029 ( p_SourceCode => r_PersonalTask.SOURCE_OBJECT_TYPE_CODE
1030 , p_PeriodStartDate => p_QueryStartDate
1031 , p_PeriodEndDate => p_QueryEndDate
1032 , p_StartDate => l_NewStartDate
1033 , p_EndDate => l_NewEndDate
1034 , p_CalSpanDaysProfile => p_CalSpanDaysProfile
1035 );
1036
1037
1038 IF l_TempItemDisplayType <> 3 THEN
1039 r_PersonalTask.CALENDAR_START_DATE := l_NewStartDate;
1040 r_PersonalTask.CALENDAR_END_DATE := l_NewEndDate;
1041 END IF;
1042
1043
1044
1045 IF ((p_QueryMode = 4) -- All tasks
1046 OR ((p_QueryMode <> 4)
1047 AND (l_TempItemDisplayType <> 2))) -- Filter tasks item type 2
1048 THEN
1049
1050 --MultiDay span case
1051 l_TempStartDate := r_PersonalTask.CALENDAR_START_DATE;
1052 l_TempEndDate := r_PersonalTask.CALENDAR_END_DATE;
1053 l_ItemDisplayType := l_TempItemDisplayType;
1054 IF (l_TempItemDisplayType = 5)
1055 THEN
1056 l_TempEndDate := TRUNC(l_TempStartDate) + 1 - 1/(24*60*60);
1057 IF (r_PersonalTask.SOURCE_OBJECT_TYPE_CODE ='APPOINTMENT') THEN
1058 l_ItemDisplayType := 1;
1059 ELSE
1060 l_ItemDisplayType := 3; -- show such tasks as all day
1061 END IF;
1062
1063 END IF;
1064
1065
1066 WHILE (((l_TempEndDate <= r_PersonalTask.CALENDAR_END_DATE) AND
1067 (l_TempStartDate <= r_PersonalTask.CALENDAR_END_DATE)) OR
1068 (l_TempStartDate IS NULL) OR (l_TempEndDate IS NULL))
1069 LOOP
1070 /***************************************************************************
1071 ** Now that the StartDate and EndDate are corrected we need to check
1072 ** whether we want to display them
1073 ***************************************************************************/
1074 IF ( ( l_TempStartDate <= p_QueryEndDate
1075 OR l_TempStartDate IS NULL
1076 )
1077 AND ( l_TempEndDate >= p_QueryStartDate
1078 OR l_TempEndDate IS NULL
1079 )
1080 )
1081 THEN
1082 /*************************************************************************
1083 ** Store the task information in a PL/SQL table.
1084 *************************************************************************/
1085 x_index := x_index + 1;
1086 /***********************************************************************
1087 ** These items should be displayed on the Calendar;
1088 ** items are filtered unless query mode is combi
1089 ***********************************************************************/
1090 x_DisplayItems(x_index).ItemDisplayType := l_ItemDisplayType;
1091 x_DisplayItems(x_index).ItemSourceID := r_PersonalTask.TASK_ID;
1092 x_DisplayItems(x_index).ItemSourceCode := 'TASK';
1093 x_DisplayItems(x_index).SourceObjectTypeCode := r_PersonalTask.SOURCE_OBJECT_TYPE_CODE;
1094 x_DisplayItems(x_index).ItemName := r_PersonalTask.TASK_NAME;
1095 x_DisplayItems(x_index).AccessLevel := GET_ACCESS_LEVEL
1096 ( 'TASK'
1097 , r_PersonalTask.SOURCE_OBJECT_TYPE_CODE
1098 , p_LoggedOnRSID
1099 , p_LoggedOnRSType
1100 , p_QueryRSID
1101 , p_QueryRSType
1102 , r_PersonalTask.Private_Flag
1103 , p_QueryUserAccess
1104 );
1105 x_DisplayItems(x_index).RepeatIndicator := r_PersonalTask.RepeatIndicator;
1106 x_DisplayItems(x_index).RemindIndicator := 0;
1107 x_DisplayItems(x_index).InviteIndicator := 0;
1108 x_DisplayItems(x_index).StartDate := l_TempStartDate;
1109 x_DisplayItems(x_index).EndDate := l_TempEndDate;
1110 x_DisplayItems(x_index).GroupRSID := p_GroupRSID;
1111 x_DisplayItems(x_index).FreeBusyType := r_PersonalTask.free_busy_type;
1112 -- The following fields are optional, check whether they match
1113 -- First, Second or Third Detail task preferences
1114 IF (p_TaskFirstDetail = '1' OR p_TaskSecondDetail = '1'
1115 OR p_TaskThirdDetail = '1') THEN
1116 x_DisplayItems(x_index).Attendees := cac_view_acc_daily_view_pvt.get_attendees(r_PersonalTask.TASK_ID);
1117 END IF;
1118 IF (p_TaskFirstDetail = '2' OR p_TaskSecondDetail = '2'
1119 OR p_TaskThirdDetail = '2') THEN
1120 x_DisplayItems(x_index).source := r_PersonalTask.SOURCE_NAME || '-' ||
1121 r_PersonalTask.SOURCE_INSTANCE;
1122 END IF;
1123 IF (p_TaskFirstDetail = '3' OR p_TaskSecondDetail = '3'
1124 OR p_TaskThirdDetail = '3') THEN
1125 x_DisplayItems(x_index).customer := r_PersonalTask.CUSTOMER_NAME;
1126 END IF;
1127 IF (p_TaskFirstDetail = '4' OR p_TaskSecondDetail = '4'
1128 OR p_TaskThirdDetail = '4') THEN
1129 x_DisplayItems(x_index).CustomerConfirmation := r_PersonalTask.TASK_CONFIRMATION_STATUS;
1130 NULL;
1131 END IF;
1132 IF (p_TaskFirstDetail = '5' OR p_TaskSecondDetail = '5'
1133 OR p_TaskThirdDetail = '5') THEN
1134 x_DisplayItems(x_index).Status := r_PersonalTask.STATUS_NAME;
1135 END IF;
1136 IF (p_TaskFirstDetail = '6' OR p_TaskSecondDetail = '6'
1137 OR p_TaskThirdDetail = '6') THEN
1138 x_DisplayItems(x_index).AssigneeStatus := r_PersonalTask.ASSIGN_STATUS_NAME;
1139 END IF;
1140 IF (p_TaskFirstDetail = '7' OR p_TaskSecondDetail = '7'
1141 OR p_TaskThirdDetail = '7') THEN
1142 x_DisplayItems(x_index).Priority := r_PersonalTask.PRIORITY_NAME;
1143 END IF;
1144 IF (p_TaskFirstDetail = '8' OR p_TaskSecondDetail = '8'
1145 OR p_TaskThirdDetail = '8') THEN
1146 x_DisplayItems(x_index).TaskType := r_PersonalTask.TYPE_NAME;
1147 END IF;
1148 IF (p_TaskFirstDetail = '9' OR p_TaskSecondDetail = '9'
1149 OR p_TaskThirdDetail = '9') THEN
1150 x_DisplayItems(x_index).Description := r_PersonalTask.DESCRIPTION;
1151 END IF;
1152 IF (p_TaskFirstDetail = '10' OR p_TaskSecondDetail = '10'
1153 OR p_TaskThirdDetail = '10') THEN
1154 x_DisplayItems(x_index).Owner := r_PersonalTask.OWNER;
1155 END IF;
1156 IF (p_TaskFirstDetail = '11' OR p_TaskSecondDetail = '11'
1157 OR p_TaskThirdDetail = '11') THEN
1158 x_DisplayItems(x_index).Location := r_PersonalTask.CITY;
1159 END IF;
1160
1161 -- Get Drilldown information
1162 l_objects_input.ENTITY := 'TASK';
1163 l_objects_input.OBJECT_CODE := r_PersonalTask.SOURCE_OBJECT_TYPE_CODE;
1164 l_objects_input.SOURCE_OBJECT_ID := r_PersonalTask.SOURCE_OBJECT_ID;
1165 l_objects_input.TASK_ASSIGNMENT_ID := r_PersonalTask.TASK_ASSIGNMENT_ID;
1166 l_objects_input.TASK_ID := r_PersonalTask.TASK_ID;
1167 --Bug 5228719 Initialize JTF Objects cache
1168 jtf_objects_pub.INITIALIZE_CACHE;
1169 jtf_objects_pub.GET_DRILLDOWN_PAGE
1170 ( P_INPUT_REC => l_objects_input
1171 , X_PG_FUNCTION => x_DisplayItems(x_index).URL
1172 , X_PG_PARAMETERS => x_DisplayItems(x_index).URLParamList
1173 );
1174 END IF;
1175 -- Increment the dates.
1176 IF (l_TempItemDisplayType = 5)
1177 THEN
1178 l_TempStartDate := TRUNC(l_TempStartDate) + 1;
1179 l_TempEndDate := TRUNC(l_TempStartDate) + 1 - 1/(24*60*60);
1180 IF (l_TempEndDate > r_PersonalTask.CALENDAR_END_DATE)
1181 THEN
1182 l_TempEndDate := r_PersonalTask.CALENDAR_END_DATE;
1183 END IF;
1184 IF (l_TempStartDate >= r_PersonalTask.CALENDAR_END_DATE)
1185 THEN
1186 EXIT;
1187 END IF;
1188 ELSE
1189 EXIT;
1190 END IF;
1191 END LOOP;
1192 END IF; --Filter tasks 2
1193
1194 END LOOP PERSONAL_TASKS;
1195 END IF; -- end of IF (p_ShowTasks)
1196
1197 --
1198
1199 END GetApptsAndTasks;
1200
1201 PROCEDURE GetItems
1202 /*******************************************************************************
1203 ** This procedure will return Marketing Calendar Items
1204 *******************************************************************************/
1205 ( p_LoggedOnRSID IN NUMBER
1206 , p_LoggedOnRSType IN VARCHAR2
1207 , p_QueryRSID IN NUMBER
1208 , p_QueryRSType IN VARCHAR2
1209 , p_QueryStartDate IN DATE
1210 , p_QueryEndDate IN DATE
1211 , p_QueryMode IN VARCHAR2
1212 , p_TimezoneId IN NUMBER
1213 , p_CalSpanDaysProfile IN VARCHAR2
1214 , p_GroupRSID IN VARCHAR2
1215 , x_index IN OUT NOCOPY BINARY_INTEGER
1216 , x_DisplayItems IN OUT NOCOPY CAC_VIEW_PVT.QueryOutTab
1217 ) IS
1218
1219 CURSOR c_Items
1220 /*****************************************************************************
1221 ** This Cursor will fetch all Calendar Items related to a Resource
1222 *****************************************************************************/
1223 ( b_ResourceID IN NUMBER
1224 , b_ResourceType IN VARCHAR2
1225 , b_StartDate IN DATE
1226 , b_EndDate IN DATE
1227 )IS SELECT DISTINCT jtb.source_id ItemSourceID
1228 , jtb.cal_item_id CalItemId
1229 , jtb.source_code SourceCode
1230 , jtb.source_id SourceID
1231 , jtb.start_date StartDate
1232 , jtb.end_date EndDate
1233 , jtb.timezone_id TimezoneID
1234 FROM jtf_cal_items_b jtb
1235 WHERE( jtb.start_date <= b_EndDate
1236 )
1237 AND ( jtb.end_date >= b_StartDate
1238 )
1239 AND jtb.resource_type = 'RS_GROUP'
1240 AND jtb.resource_id IN --select groups that user is member of
1241 (SELECT mem.group_id
1242 FROM
1243 jtf_rs_group_members mem,
1244 jtf_rs_group_usages rgu
1245 WHERE mem.resource_id = b_ResourceID
1246 AND nvl(mem.delete_flag, 'N') <> 'Y'
1247 AND rgu.group_id = mem.group_id
1248 AND rgu.usage = 'CALENDAR_ITEMS')
1249 UNION -- individual items
1250 SELECT jtb.source_id ItemSourceID
1251 , jtb.cal_item_id CalItemId
1252 , jtb.source_code SourceCode
1253 , jtb.source_id SourceID
1254 , jtb.start_date StartDate
1255 , jtb.end_date EndDate
1256 , jtb.timezone_id TimezoneID
1257 FROM jtf_cal_items_b jtb
1258 WHERE jtb.resource_id = b_ResourceID
1259 AND jtb.resource_type = 'RS_EMPLOYEE'
1260 AND ( jtb.start_date <= b_EndDate
1261 )
1262 AND ( jtb.end_date >= b_StartDate
1263 )
1264 ;
1265 l_TempStartDate DATE;
1266 l_TempEndDate DATE;
1267 l_TempItemDisplayType NUMBER;
1268 l_ItemDisplayType NUMBER;
1269 l_StartDate DATE;
1270 l_EndDate DATE;
1271 l_NewStartDate DATE;
1272 l_NewEndDate DATE;
1273 l_item_name VARCHAR2(2000);
1274
1275 l_objects_input jtf_objects_pub.PG_INPUT_REC;
1276
1277 BEGIN
1278 /*****************************************************************************
1279 ** Now we need to get all the Calendar Items for this Employee Resource.
1280 *****************************************************************************/
1281 IF (c_Items%ISOPEN)
1282 THEN
1283 CLOSE c_Items; -- Make sure the cursor is closed
1284 END IF;
1285 FOR r_PersonalItem IN c_Items( p_QueryRSID
1286 , p_QueryRSType
1287 , p_QueryStartDate - 1 -- allow for max timezone adjustment
1288 , p_QueryEndDate + 1 -- allow for max timezone adjustment
1289 )
1290
1291
1292 LOOP <<PERSONAL_ITEMS>>
1293 /***************************************************************************
1294 ** We will have to adjust the Start/End Date for the users timezone
1295 ***************************************************************************/
1296 --Added by jawang on 11/21/2002 to fix the NOCOPY issue
1297 l_StartDate := r_PersonalItem .Startdate;
1298 l_EndDate := r_PersonalItem .Enddate;
1299
1300 if p_TimezoneId is not null
1301 then
1302 CAC_VIEW_UTIL_PVT.AdjustForTimezone
1303 ( p_source_tz_id => TO_NUMBER(r_PersonalItem .TimezoneID)
1304 , p_dest_tz_id => p_TimezoneId
1305 , p_source_day_time => l_StartDate
1306 , x_dest_day_time => l_NewStartDate
1307 );
1308
1309 CAC_VIEW_UTIL_PVT.AdjustForTimezone
1310 ( p_source_tz_id => TO_NUMBER(r_PersonalItem .TimezoneID)
1311 , p_dest_tz_id => p_TimezoneId
1312 , p_source_day_time => l_EndDate
1313 , x_dest_day_time => l_NewEndDate
1314 );
1315 else
1316 CAC_VIEW_UTIL_PVT.AdjustForTimezone
1317 ( p_source_tz_id => r_PersonalItem .TimezoneID
1318 , p_dest_tz_id => TO_NUMBER(NVL(Fnd_Profile.Value('CLIENT_TIMEZONE_ID'),'4'))
1319 , p_source_day_time => l_StartDate
1320 , x_dest_day_time => l_NewStartDate
1321 );
1322
1323 CAC_VIEW_UTIL_PVT.AdjustForTimezone
1324 ( p_source_tz_id => r_PersonalItem .TimezoneID
1325 , p_dest_tz_id => TO_NUMBER(NVL(Fnd_Profile.Value('CLIENT_TIMEZONE_ID'),'4'))
1326 , p_source_day_time => l_EndDate
1327 , x_dest_day_time => l_NewEndDate
1328 );
1329
1330 end if;
1331
1332 l_TempItemDisplayType := GetItemType
1333 ( p_SourceCode => 'CALENDARITEM'
1334 , p_PeriodStartDate => p_QueryStartDate
1335 , p_PeriodEndDate => p_QueryEndDate
1336 , p_StartDate => l_NewStartDate
1337 , p_EndDate => l_NewEndDate
1338 , p_CalSpanDaysProfile => p_CalSpanDaysProfile
1339 );
1340
1341 IF l_TempItemDisplayType <> 3 THEN
1342 r_PersonalItem.Startdate := l_NewStartDate;
1343 r_PersonalItem.Enddate := l_NewEndDate;
1344 END IF;
1345
1346 IF ((p_QueryMode = 4)
1347 OR ((p_QueryMode <> 4)
1348 AND (l_TempItemDisplayType <> 2)))
1349 THEN
1350
1351 --MultiDay span case
1352 l_TempStartDate := r_PersonalItem.StartDate;
1353 l_TempEndDate := r_PersonalItem.EndDate;
1354 l_ItemDisplayType := l_TempItemDisplayType;
1355 IF (l_TempItemDisplayType = 5)
1356 THEN
1357 l_TempEndDate := TRUNC(l_TempStartDate) + 1 - 1/(24*60*60);
1358 l_ItemDisplayType := 1;
1359 END IF;
1360
1361 WHILE (((l_TempEndDate <= r_PersonalItem.EndDate) AND
1362 (l_TempStartDate <= r_PersonalItem.EndDate)) OR
1363 (l_TempStartDate IS NULL) OR (l_TempEndDate IS NULL))
1364 LOOP
1365 /***************************************************************************
1366 ** Now that the StartDate and EndDate are corrected we need to check whether
1367 ** we want to display them
1368 ***************************************************************************/
1369 IF ( NVL(l_TempStartDate,TRUNC(SYSDATE)) <= p_QueryEndDate
1370 AND NVL(l_TempEndDate,TRUNC(SYSDATE)) >= p_QueryStartDate
1371 )
1372 THEN
1373 x_index := x_index + 1;
1374
1375 x_DisplayItems(x_index).ItemDisplayType := l_ItemDisplayType;
1376 x_DisplayItems(x_index).ItemSourceID := r_PersonalItem.ItemSourceID;
1377 IF(r_PersonalItem.SourceCode IS NOT NULL
1378 AND r_PersonalItem.SourceId IS NOT NULL )THEN
1379 x_DisplayItems(x_index).ItemName := JTF_TASK_UTL.get_owner(r_PersonalItem.SourceCode, r_PersonalItem.SourceId);
1380 END IF;
1381 x_DisplayItems(x_index).AccessLevel := 1;
1382 x_DisplayItems(x_index).StartDate := l_TempStartDate;
1383 x_DisplayItems(x_index).EndDate := l_TempEndDate;
1384 x_DisplayItems(x_index).ItemSourceCode := 'CALENDARITEM';
1385 x_DisplayItems(x_index).GroupRSID := p_GroupRSID;
1386 x_DisplayItems(x_index).AssignmentStatus := 0;
1387 x_DisplayItems(x_index).InviteIndicator := 0;
1388 x_DisplayItems(x_index).RepeatIndicator := 0;
1389 x_DisplayItems(x_index).RemindIndicator := 0;
1390
1391 -- Get Drilldown information
1392 l_objects_input.ENTITY := 'CALENDARITEM';
1393 l_objects_input.OBJECT_CODE := r_PersonalItem.SourceCode;
1394 l_objects_input.SOURCE_OBJECT_ID := r_PersonalItem.SourceId;
1395 l_objects_input.CAL_ITEM_ID := r_PersonalItem.CalItemId;
1396 jtf_objects_pub.GET_DRILLDOWN_PAGE
1397 ( P_INPUT_REC => l_objects_input
1398 , X_PG_FUNCTION => x_DisplayItems(x_index).URL
1399 , X_PG_PARAMETERS => x_DisplayItems(x_index).URLParamList
1400 );
1401 END IF;
1402 -- Increment the dates.
1403 IF (l_TempItemDisplayType = 5)
1404 THEN
1405 l_TempStartDate := TRUNC(l_TempStartDate) + 1;
1406 l_TempEndDate := TRUNC(l_TempStartDate) + 1 - 1/(24*60*60);
1407 IF (l_TempEndDate > r_PersonalItem.EndDate)
1408 THEN
1409 l_TempEndDate := r_PersonalItem.EndDate;
1410 END IF;
1411 IF (l_TempStartDate >= r_PersonalItem.EndDate)
1412 THEN
1413 EXIT;
1414 END IF;
1415 ELSE
1416 EXIT;
1417 END IF;
1418 END LOOP;
1419 END IF;
1420 END LOOP PERSONAL_ITEMS;
1421 END GetItems;
1422
1423 PROCEDURE GET_CAL_EVENTS
1424 ( p_object_type IN VARCHAR2
1425 , p_object_id IN NUMBER
1426 , p_start_date IN DATE
1427 , p_end_date IN DATE
1428 , x_items IN OUT NOCOPY CAC_VIEW_PVT.QueryOutTab
1429 )
1430 IS
1431 l_hrcal CAC_HR_CAL_EVENTS_PVT.HR_CAL_EVENT_TBL_TYPE;
1432 l_index NUMBER;
1433 l_objects_input jtf_objects_pub.PG_INPUT_REC;
1434
1435 BEGIN
1436 l_index := x_items.count;
1437
1438 CAC_HR_CAL_EVENTS_PVT.GET_HR_CAL_EVENTS
1439 (p_object_type => p_object_type,
1440 p_object_id => p_object_id,
1441 p_start_date => p_start_date,
1442 p_end_date => p_end_date,
1443 p_event_type => NULL,
1444 p_event_id => NULL,
1445 x_hr_cal_events => l_hrcal
1446 );
1447
1448 IF l_hrcal.count > 0 THEN
1449 FOR i IN l_hrcal.first..l_hrcal.last
1450 LOOP
1451 x_items(l_index).ItemDisplayType := 6;
1452 x_items(l_index).ItemSourceCode := 'HR_CAL_EVENT';
1453 x_items(l_index).ItemSourceID := l_hrcal(i).cal_event_id;
1454 x_items(l_index).StartDate := l_hrcal(i).start_date_time;
1455 x_items(l_index).EndDate := l_hrcal(i).end_date_time;
1456 x_items(l_index).ItemName := l_hrcal(i).event_name;
1457 x_items(l_index).AssignmentStatus:= 0;
1458 x_items(l_index).InviteIndicator := 0;
1459 x_items(l_index).RepeatIndicator := 0;
1460 x_items(l_index).RemindIndicator := 0;
1461 x_items(l_index).SourceObjectTypeCode := 'HR_CAL_EVENT';
1462 -- Get Drilldown information
1463 l_objects_input.ENTITY := 'HR_CAL_EVENT';
1464 l_objects_input.OBJECT_CODE := 'HR_CAL_EVENT';
1465 l_objects_input.HR_CAL_EVENT_ID := l_hrcal(i).cal_event_id;
1466 jtf_objects_pub.GET_DRILLDOWN_PAGE
1467 ( P_INPUT_REC => l_objects_input
1468 , X_PG_FUNCTION => x_items(l_index).URL
1469 , X_PG_PARAMETERS => x_items(l_index).URLParamList
1470 );
1471 l_index := l_index + 1;
1472 END LOOP;
1473 END IF;
1474
1475 END GET_CAL_EVENTS;
1476
1477 PROCEDURE GET_SCHEDULES
1478 ( p_object_type IN VARCHAR2
1479 , p_object_id IN NUMBER
1480 , p_start_date IN DATE
1481 , p_end_date IN DATE
1482 , p_timezone_id IN NUMBER
1483 , p_view_timezone IN NUMBER
1484 , x_items IN OUT NOCOPY CAC_VIEW_PVT.QueryOutTab
1485 )
1486 IS
1487 l_return_status VARCHAR2(1);
1488 l_msg_count NUMBER;
1489 l_msg_data VARCHAR2(2000);
1490
1491 l_schedule_summary CAC_AVLBLTY_SUMMARY_VARRAY;
1492 l_schedule_details CAC_AVLBLTY_DETAIL_VARRAY;
1493 l_schedule_daytime CAC_AVLBLTY_DAY_TIME_VARRAY;
1494
1495 l_new_start_date DATE;
1496 l_new_end_date DATE;
1497 l_skip BOOLEAN;
1498
1499 l_index NUMBER;
1500 l_objects_input jtf_objects_pub.PG_INPUT_REC;
1501
1502 BEGIN
1503 l_index := x_items.count;
1504
1505 CAC_AVLBLTY_PUB.GET_SCHEDULE_SUMMARY
1506 ( p_api_version => 1.0
1507 , p_init_msg_list => fnd_api.g_false
1508 , p_object_type => p_object_type
1509 , p_object_id => p_object_id
1510 , p_start_date => p_start_date - 1
1511 , p_end_date => p_end_date + 1
1512 , p_schedule_category => NULL
1513 , p_include_exception => 'T'
1514 , p_busy_tentative => NULL
1515 , x_schedule_summary => l_schedule_summary
1516 , x_return_status => l_return_status
1517 , x_msg_count => l_msg_count
1518 , x_msg_data => l_msg_data
1519 );
1520
1521 IF l_return_status = 'S' AND
1522 l_schedule_summary.count > 0
1523 THEN
1524 FOR i IN l_schedule_summary.first..l_schedule_summary.last
1525 LOOP
1526 l_schedule_details := l_schedule_summary(i).summary_lines;
1527 IF l_schedule_details.count > 0
1528 THEN
1529 FOR j IN l_schedule_details.first..l_schedule_details.last
1530 LOOP
1531 l_schedule_daytime := l_schedule_details(j).day_times;
1532 IF l_schedule_daytime.count > 0
1533 THEN
1534 FOR k IN l_schedule_daytime.first..l_schedule_daytime.last
1535 LOOP
1536 l_skip := FALSE;
1537
1538
1539 IF p_timezone_id IS NOT NULL
1540 THEN
1541
1542 CAC_VIEW_UTIL_PVT.AdjustForTimezone
1543 ( p_source_tz_id => p_timezone_id
1544 , p_dest_tz_id => to_number(NVL(FND_PROFILE.Value('CLIENT_TIMEZONE_ID'),'4'))
1545 , p_source_day_time => l_schedule_daytime(k).start_date_time
1546 , x_dest_day_time => l_new_start_date
1547 );
1548
1549 CAC_VIEW_UTIL_PVT.AdjustForTimezone
1550 ( p_source_tz_id => p_timezone_id
1551 , p_dest_tz_id => to_number(NVL(FND_PROFILE.Value('CLIENT_TIMEZONE_ID'),'4'))
1552 , p_source_day_time => l_schedule_daytime(k).end_date_time
1553 , x_dest_day_time => l_new_end_date
1554 );
1555
1556 ELSIF p_view_timezone is not null
1557 then
1558 CAC_VIEW_UTIL_PVT.AdjustForTimezone
1559 ( p_source_tz_id => to_number(NVL(FND_PROFILE.Value('SERVER_TIMEZONE_ID'),'4'))
1560 , p_dest_tz_id => p_view_timezone
1561 , p_source_day_time => l_schedule_daytime(k).start_date_time
1562 , x_dest_day_time => l_new_start_date
1563 );
1564
1565 CAC_VIEW_UTIL_PVT.AdjustForTimezone
1566 ( p_source_tz_id => to_number(NVL(FND_PROFILE.Value('SERVER_TIMEZONE_ID'),'4'))
1567 , p_dest_tz_id => p_view_timezone
1568 , p_source_day_time => l_schedule_daytime(k).end_date_time
1569 , x_dest_day_time => l_new_end_date
1570 );
1571
1572 ELSE
1573 l_new_start_date := l_schedule_daytime(k).start_date_time;
1574 l_new_end_date := l_schedule_daytime(k).end_date_time;
1575 END IF;
1576 IF ((l_new_start_date <= p_start_date) AND
1577 (l_new_end_date > p_start_date))
1578 THEN
1579 l_new_start_date := p_start_date;
1580 IF (l_new_end_date > p_end_date)
1581 THEN
1582 l_new_end_date := p_end_date;
1583 END IF;
1584 ELSIF ((l_new_start_date > p_start_date) AND
1585 (l_new_start_date < p_end_date))
1586 THEN
1587 IF (l_new_end_date > p_end_date)
1588 THEN
1589 l_new_end_date := p_end_date;
1590 END IF;
1591 ELSE
1592 l_skip := TRUE;
1593 END IF;
1594
1595 --Bug 4586452 Change the endtime if it ends at midnight
1596 if ((to_char(l_new_end_date,'HH24:MI'))='00:00')
1597 then
1598 l_new_end_date:= l_new_end_date - (1/(24*60));
1599 end if;
1600
1601 IF ((NOT l_skip) AND (l_new_end_date > l_new_start_date))
1602 THEN
1603 x_items(l_index).ItemDisplayType := 7;
1604 x_items(l_index).ItemName := l_schedule_details(j).period_category_name ||': '||
1605 to_char(l_new_start_date,'HH24:MI')||' - '||
1606 to_char(l_new_end_date,'HH24:MI');
1607 x_items(l_index).FreeBusyType := l_schedule_details(j).free_busy_type;
1608 x_items(l_index).DisplayColor := l_schedule_details(j).display_color;
1609 x_items(l_index).ItemSourceCode := 'SCHEDULE';
1610 x_items(l_index).ItemSourceID := 0;
1611 x_items(l_index).AssignmentStatus:= 0;
1612 x_items(l_index).InviteIndicator := 0;
1613 x_items(l_index).RepeatIndicator := 0;
1614 x_items(l_index).RemindIndicator := 0;
1615 x_items(l_index).SourceObjectTypeCode := 'SCHEDULE';
1616 x_items(l_index).StartDate := l_new_start_date;
1617 x_items(l_index).EndDate := l_new_end_date;
1618 -- Get Drilldown information
1619 l_objects_input.ENTITY := 'SCHEDULE';
1620 l_objects_input.OBJECT_CODE := 'SCHEDULE';
1621 l_objects_input.SCHEDULE_ID := NULL; --TBD
1622 jtf_objects_pub.GET_DRILLDOWN_PAGE
1623 ( P_INPUT_REC => l_objects_input
1624 , X_PG_FUNCTION => x_items(l_index).URL
1625 , X_PG_PARAMETERS => x_items(l_index).URLParamList
1626 );
1627 l_index := l_index + 1;
1628 END IF;
1629 END LOOP;
1630 END IF; -- end if l_schedule_daytime.count > 0
1631 END LOOP;
1632 END IF; -- end if l_schedule_details.count > 0
1633 END LOOP;
1634 END IF; -- end if l_schedule_summary.count > 0
1635
1636 END GET_SCHEDULES;
1637
1638 PROCEDURE SortTable
1639 /******************************************************************
1640 ** We need to sort the output table to make life easier on the
1641 ** java end.. This is a simple bi-directional bubble sort, which
1642 ** should do the trick.
1643 ******************************************************************/
1644 (p_CalendarItems IN OUT NOCOPY CAC_VIEW_PVT.QueryOutTab
1645 )
1646 IS
1647 l_LastRecord BINARY_INTEGER;
1648
1649 PROCEDURE Swap
1650 /******************************************************************
1651 ** Swap the records
1652 ******************************************************************/
1653 (p_index IN NUMBER
1654 )
1655 IS
1656 l_record CAC_VIEW_PVT.QueryOut;
1657 BEGIN
1658 l_record := p_CalendarItems(p_index);
1659 p_CalendarItems(p_index) := p_CalendarItems(p_index - 1);
1660 p_CalendarItems(p_index - 1) := l_record;
1661 END Swap;
1662
1663 BEGIN
1664 l_LastRecord := p_CalendarItems.LAST;
1665 IF (l_LastRecord is null)
1666 THEN
1667 RETURN;
1668 ELSE
1669 FOR l_high IN 1 .. l_LastRecord
1670 LOOP <<HIGH>>
1671 IF (p_CalendarItems(l_high).ItemDisplayType NOT IN (6,7) AND
1672 p_CalendarItems(l_high - 1).ItemDisplayType NOT IN (6,7) AND
1673 p_CalendarItems(l_high).StartDate < p_CalendarItems(l_high - 1).StartDate) OR
1674 (p_CalendarItems(l_high).ItemDisplayType IN (6,7) AND
1675 p_CalendarItems(l_high - 1).ItemDisplayType NOT IN (6,7) AND
1676 trunc(p_CalendarItems(l_high).StartDate) <= trunc(p_CalendarItems(l_high - 1).StartDate)) OR
1677 (p_CalendarItems(l_high).ItemDisplayType IN (6,7) AND
1678 p_CalendarItems(l_high - 1).ItemDisplayType IN (6,7) AND
1679 p_CalendarItems(l_high).StartDate < p_CalendarItems(l_high - 1).StartDate)
1680 THEN
1681 Swap(l_high);
1682 FOR l_low IN REVERSE 1 .. (l_high - 1)
1683 LOOP <<LOW>>
1684 IF (p_CalendarItems(l_low).ItemDisplayType NOT IN (6,7) AND
1685 p_CalendarItems(l_low - 1).ItemDisplayType NOT IN (6,7) AND
1686 p_CalendarItems(l_low).StartDate < p_CalendarItems(l_low - 1).StartDate) OR
1687 (p_CalendarItems(l_low).ItemDisplayType IN (6,7) AND
1688 p_CalendarItems(l_low - 1).ItemDisplayType NOT IN (6,7) AND
1689 trunc(p_CalendarItems(l_low).StartDate) <= trunc(p_CalendarItems(l_low - 1).StartDate)) OR
1690 (p_CalendarItems(l_low).ItemDisplayType IN (6,7) AND
1691 p_CalendarItems(l_low - 1).ItemDisplayType IN (6,7) AND
1692 p_CalendarItems(l_low).StartDate < p_CalendarItems(l_low - 1).StartDate)
1693 THEN
1694 Swap(l_low);
1695 ELSE
1696 EXIT;
1697 END IF;
1698 END LOOP LOW;
1699 END IF;
1700 END LOOP HIGH;
1701 END IF;
1702 EXCEPTION
1703 WHEN COLLECTION_IS_NULL
1704 THEN RETURN;
1705
1706 WHEN OTHERS
1707 THEN RAISE;
1708 END SortTable;
1709
1710 PROCEDURE GET_PERSON_PRIMARY_ASSIGNMENT
1711 ( p_emp_resource_id IN NUMBER
1712 , x_per_assignment_id OUT NOCOPY NUMBER
1713 , x_per_assignment_type OUT NOCOPY VARCHAR2
1714 )
1715 IS
1716 CURSOR c IS
1717 SELECT a.assignment_id
1718 FROM per_assignments_f a
1719 , jtf_rs_resource_extns r
1720 WHERE r.resource_id = p_emp_resource_id
1721 AND a.person_id = r.source_id
1722 AND a.primary_flag = 'Y'
1723 AND trunc(SYSDATE) BETWEEN NVL(a.effective_start_date, trunc(SYSDATE))
1724 AND NVL(a.effective_end_date, trunc(SYSDATE+1));
1725 BEGIN
1726
1727 OPEN c;
1728 FETCH c INTO x_per_assignment_id;
1729 CLOSE c;
1730
1731 x_per_assignment_type := 'PERSON_ASSIGNMENT';
1732
1733 END GET_PERSON_PRIMARY_ASSIGNMENT;
1734
1735 PROCEDURE GET_EMPLOYEE_RESOURCE
1736 ( p_per_assignment_id IN NUMBER
1737 , x_emp_resource_id OUT NOCOPY NUMBER
1738 , x_emp_resource_type OUT NOCOPY VARCHAR2
1739 )
1740 IS
1741 CURSOR c IS
1742 SELECT r.resource_id
1743 FROM per_assignments_f a
1744 , jtf_rs_resource_extns r
1745 WHERE a.assignment_id = p_per_assignment_id
1746 AND r.source_id = a.person_id
1747 AND r.category = 'EMPLOYEE';
1748 BEGIN
1749
1750 OPEN c;
1751 FETCH c INTO x_emp_resource_id;
1752 CLOSE c;
1753
1754 x_emp_resource_type := 'RS_EMPLOYEE';
1755
1756 END GET_EMPLOYEE_RESOURCE;
1757
1758 PROCEDURE GET_RESOURCES
1759 ( p_input IN CAC_VIEW_PVT.QueryIn
1760 , x_query_emp_resource_id OUT NOCOPY NUMBER
1761 , x_query_emp_resource_type OUT NOCOPY VARCHAR2
1762 , x_query_per_assignment_id OUT NOCOPY NUMBER
1763 , x_query_per_assignment_type OUT NOCOPY VARCHAR2
1764 , x_query_user_access OUT NOCOPY MENUS_TBL
1765 , x_loggedon_emp_resource_id OUT NOCOPY NUMBER
1766 , x_loggedon_emp_resource_type OUT NOCOPY VARCHAR2
1767 )
1768 IS
1769 CURSOR C_GET_MENUS
1770 (
1771 b_resource_id NUMBER,
1772 b_resource_type VARCHAR2
1773 ) IS
1774 SELECT fmu.menu_name
1775 FROM fnd_grants fgs
1776 , fnd_menus fmu
1777 , fnd_objects fos
1778 WHERE fgs.object_id = fos.object_id
1779 AND fgs.menu_id = fmu.menu_id
1780 AND fos.obj_name = 'CAC_CAL_RESOURCES'
1781 AND fgs.grantee_key = FND_GLOBAL.USER_NAME
1782 AND fgs.grantee_type = 'USER'
1783 AND fgs.start_date < SYSDATE
1784 AND ( fgs.end_date >= SYSDATE
1785 OR fgs.end_date IS NULL
1786 )
1787 and fgs.instance_type = 'INSTANCE'
1788 AND fgs.instance_pk1_value = TO_CHAR(b_resource_id)
1789 AND fgs.instance_pk2_value = b_resource_type;
1790
1791 l_user_id NUMBER;
1792 i BINARY_INTEGER;
1793 BEGIN
1794 IF p_input.QueryRSID IS NOT NULL
1795 THEN
1796 IF p_input.QueryRSType = 'RS_EMPLOYEE' THEN
1797
1798 x_query_emp_resource_id := p_input.QueryRSID;
1799 x_query_emp_resource_type := p_input.QueryRSType;
1800
1801 -- Get a primary person assignment id for the employee
1802 GET_PERSON_PRIMARY_ASSIGNMENT
1803 ( p_emp_resource_id => p_input.QueryRSID
1804 , x_per_assignment_id => x_query_per_assignment_id
1805 , x_per_assignment_type => x_query_per_assignment_type
1806 );
1807
1808 ELSIF p_input.QueryRSType = 'PERSON_ASSIGNMENT' THEN
1809
1810 x_query_per_assignment_id := p_input.QueryRSID;
1811 x_query_per_assignment_type := p_input.QueryRSType;
1812
1813 -- Get an employee resource info for the person assignment id
1814 GET_EMPLOYEE_RESOURCE
1815 ( p_per_assignment_id => p_input.QueryRSID
1816 , x_emp_resource_id => x_query_emp_resource_id
1817 , x_emp_resource_type => x_query_emp_resource_type
1818 );
1819
1820 END IF;
1821 ELSE
1822 IF p_input.UserID IS NOT NULL THEN
1823 l_user_id := p_input.UserID;
1824 ELSE
1825 l_user_id := fnd_global.user_id;
1826 END IF;
1827
1828 -- Get an employee resource info for the given user id
1829 Jtf_Cal_Utility_Pvt.GetResourceInfo
1830 ( p_UserID => l_user_id
1831 , x_ResourceID => x_query_emp_resource_id
1832 , x_ResourceType => x_query_emp_resource_type
1833 );
1834
1835 -- Get a primary person assignment id for the employee
1836 GET_PERSON_PRIMARY_ASSIGNMENT
1837 ( p_emp_resource_id => x_query_emp_resource_id
1838 , x_per_assignment_id => x_query_per_assignment_id
1839 , x_per_assignment_type => x_query_per_assignment_type
1840 );
1841 END IF;
1842
1843 -- Get an employee resource info for the loggon user
1844 Jtf_Cal_Utility_Pvt.GetResourceInfo
1845 ( p_UserID => fnd_global.user_id
1846 , x_ResourceID => x_loggedon_emp_resource_id
1847 , x_ResourceType => x_loggedon_emp_resource_type
1848 );
1849
1850 -- Check access
1851 IF ((x_loggedon_emp_resource_id = x_query_emp_resource_id) AND
1852 (x_loggedon_emp_resource_type = x_query_emp_resource_type))
1853 THEN
1854 i := 1;
1855 x_query_user_access(i) := 'JTF_CAL_FULL_ACCESS';
1856 i := i+1;
1857 x_query_user_access(i) := 'JTF_TASK_FULL_ACCESS';
1858 i := i+1;
1859 x_query_user_access(i) := 'CAC_BKG_READ_ONLY_ACCESS';
1860 ELSIF (p_input.UseCalendarSecurity = 'Y')
1861 THEN
1862 i := 1;
1863 FOR ref_menus IN C_GET_MENUS(x_query_emp_resource_id,x_query_emp_resource_type)
1864 LOOP
1865 x_query_user_access(i) := ref_menus.menu_name;
1866 i := i+1;
1867 END LOOP;
1868 ELSE
1869 i := 1;
1870 x_query_user_access(i) := 'JTF_CAL_READ_ACCESS';
1871 i := i+1;
1872 x_query_user_access(i) := 'JTF_TASK_READ_ONLY';
1873 i := i+1;
1874 x_query_user_access(i) := 'CAC_BKG_READ_ONLY_ACCESS';
1875 END IF;
1876 END GET_RESOURCES;
1877
1878 PROCEDURE GetView
1879 /*******************************************************************************
1880 ** This procedure will return all task information needed to
1881 ** display the daily Calendar page
1882 *******************************************************************************/
1883 ( p_api_version IN NUMBER
1884 , p_init_msg_list IN VARCHAR2
1885 , p_validation_level IN NUMBER
1886 , x_return_status OUT NOCOPY VARCHAR2
1887 , x_msg_count OUT NOCOPY NUMBER
1888 , x_msg_data OUT NOCOPY VARCHAR2
1889 , p_input IN CAC_VIEW_PVT.QueryIn
1890 , x_DisplayItems OUT NOCOPY CAC_VIEW_PVT.QueryOutTab
1891 )IS
1892 l_LoggedOnRSID NUMBER; -- ResourceID of the logged on user
1893 l_LoggedOnRSType VARCHAR2(30); -- ResourceType of the logged on user
1894 l_QueryRSID NUMBER; -- ResourceID of the logged on user
1895 l_QueryRSType VARCHAR2(30); -- ResourceType of the logged on user
1896 l_QueryRSName VARCHAR2(360);-- Resource Name of the logged on user
1897 l_query_per_rs_id NUMBER; -- Person Primary Assignment ID
1898 l_query_per_rs_type VARCHAR2(30); -- PERSON_ASSIGNMENT
1899 l_query_user_name VARCHAR2(100);--
1900 l_query_user_access MENUS_TBL ; --
1901 l_LoggedOnToday DATE; -- Today of logged on user
1902 l_QueryDate DATE; -- Query Date of logged on user
1903 l_QueryStartDate DATE; -- Start of the query period
1904 l_QueryEndDate DATE; -- End of the query period
1905
1906 --l_WeekTimePrefTbl WeekTimePrefTblType;
1907 l_CalSpanDaysProfile VARCHAR2(10);
1908 l_MinDayTime NUMBER;
1909 l_MaxDayTime NUMBER;
1910 l_WeekStartDay NUMBER;
1911 l_WeekEndDay NUMBER;
1912 l_QueryDays NUMBER;
1913 l_DayNumber NUMBER;
1914 l_offset NUMBER;
1915 l_SundayDate DATE;
1916 l_SaturdayDate DATE;
1917
1918 l_return_status VARCHAR2(1);
1919 l_msg_count NUMBER;
1920 l_msg_data VARCHAR2(2000);
1921 l_index BINARY_INTEGER;
1922 CURSOR c_Groups
1923 /*****************************************************************************
1924 ** This Cursor will fetch all Groups Calendars the Calendar User is
1925 ** subscribed to
1926 *****************************************************************************/
1927 ( b_ResourceID IN NUMBER
1928 )IS SELECT DISTINCT TO_NUMBER(fgs.instance_pk1_value) GroupID
1929 /*, Jtf_Cal_Utility_Pvt.GetGroupColor
1930 ( b_ResourceID
1931 , 'RS_EMPLOYEE'
1932 , TO_NUMBER(fgs.instance_pk1_value)
1933 ) Color
1934 , Jtf_Cal_Utility_Pvt.GetGroupPrefix
1935 ( b_ResourceID
1936 , 'RS_EMPLOYEE'
1937 , TO_NUMBER(fgs.instance_pk1_value)
1938 ) Prefix*/
1939 FROM fnd_grants fgs
1940 , fnd_objects fos
1941 , jtf_rs_group_usages rgu
1942 WHERE fgs.object_id = fos.object_id -- grants joint to object
1943 AND fos.obj_name = 'JTF_TASK_RESOURCE'
1944 AND fgs.grantee_key = TO_CHAR(b_ResourceID)
1945 AND fgs.start_date < SYSDATE
1946 AND ( (fgs.end_date >= SYSDATE)
1947 OR (fgs.end_date IS NULL)
1948 )
1949 AND fgs.instance_pk2_value = 'RS_GROUP'
1950 AND rgu.GROUP_ID = TO_NUMBER(fgs.instance_pk1_value)
1951 AND rgu.USAGE = 'GROUP_CALENDAR';
1952
1953 BEGIN
1954 IF fnd_api.to_boolean (NVL(p_init_msg_list,fnd_api.g_false))
1955 THEN
1956 fnd_msg_pub.initialize;
1957 END IF;
1958
1959 -- Initialize JTF Objects cache
1960 jtf_objects_pub.INITIALIZE_CACHE;
1961
1962 /*****************************************************************************
1963 ** Make sure I have all the Resource Information needed for the logged
1964 ** on user
1965 *****************************************************************************/
1966
1967 l_index := -1;
1968 --IF ( (p_input.LoggedOnRSID IS NULL)
1969 -- OR (p_input.LoggedOnRSType IS NULL)
1970 -- )
1971 --THEN
1972 /***************************************************************************
1973 ** If I didn't get it, try to look it up with the UserID
1974 ***************************************************************************/
1975 -- IF(p_input.UserID IS NULL)
1976 -- THEN
1977 -- NULL; --error no user information provided, this is not supposed to happen...
1978 -- ELSE
1979 -- Jtf_Cal_Utility_Pvt.GetResourceInfo( p_UserID => p_input.UserID
1980 -- , x_ResourceID => l_LoggedOnRSID
1981 -- , x_ResourceType => l_LoggedOnRSType
1982 -- );
1983 -- END IF;
1984
1985 --ELSE
1986 /***************************************************************************
1987 ** If I did get it
1988 ***************************************************************************/
1989 -- l_LoggedOnRSID := p_input.LoggedOnRSID;
1990 -- l_LoggedOnRSType := p_input.LoggedOnRSType;
1991 --END IF;
1992
1993 /*****************************************************************************
1994 ** Determine the resource id/type for which the data should be fetched
1995 *****************************************************************************/
1996 --IF ((p_input.QueryRSID IS NULL) OR (p_input.QueryRSType IS NULL))
1997 --THEN
1998 -- l_QueryRSID := l_LoggedOnRSID;
1999 -- l_QueryRSType := l_LoggedOnRSType;
2000 --ELSE
2001 -- l_QueryRSID := p_input.QueryRSID;
2002 -- l_QueryRSType := p_input.QueryRSType;
2003 --END IF;
2004
2005 GET_RESOURCES
2006 ( p_input => p_input
2007 , x_query_emp_resource_id => l_QueryRSID
2008 , x_query_emp_resource_type => l_QueryRSType
2009 , x_query_per_assignment_id => l_query_per_rs_id
2010 , x_query_per_assignment_type => l_query_per_rs_type
2011 , x_query_user_access => l_query_user_access
2012 , x_loggedon_emp_resource_id => l_LoggedOnRSID
2013 , x_loggedon_emp_resource_type => l_LoggedOnRSType
2014 );
2015
2016 -- need to fetch SpanDaysProfile profile independently
2017 l_CalSpanDaysProfile := fnd_profile.value('JTF_CAL_SPAN_DAYS');
2018 /***************************************************************************
2019 ** What is today for the logged on user
2020 ***************************************************************************/
2021 if p_input.ViewTimezoneID is not null
2022 then
2023 Hz_Timezone_Pub.Get_Time( p_api_version => 1.0
2024 , p_init_msg_list => Fnd_Api.G_FALSE
2025 , p_source_tz_id => TO_NUMBER(NVL(Fnd_Profile.Value('SERVER_TIMEZONE_ID'),'4'))
2026 , p_dest_tz_id => p_input.ViewTimezoneID
2027 , p_source_day_time => SYSDATE -- database sysdate
2028 , x_dest_day_time => l_LoggedOnToday
2029 , x_return_status => l_return_status
2030 , x_msg_count => l_msg_count
2031 , x_msg_data => l_msg_data
2032 );
2033 else
2034 Hz_Timezone_Pub.Get_Time( p_api_version => 1.0
2035 , p_init_msg_list => Fnd_Api.G_FALSE
2036 , p_source_tz_id => TO_NUMBER(NVL(Fnd_Profile.Value('SERVER_TIMEZONE_ID'),'4'))
2037 , p_dest_tz_id => TO_NUMBER(NVL(Fnd_Profile.Value('CLIENT_TIMEZONE_ID'),'4'))
2038 , p_source_day_time => SYSDATE -- database sysdate
2039 , x_dest_day_time => l_LoggedOnToday
2040 , x_return_status => l_return_status
2041 , x_msg_count => l_msg_count
2042 , x_msg_data => l_msg_data
2043 );
2044 end if;
2045
2046 /*****************************************************************************
2047 ** Set the Current Time for the Resource
2048 *****************************************************************************/
2049 -- Rada, 09/25/2003 remove all x_preferences
2050 --x_Preferences.CurrentTime := l_LoggedOnToday;
2051
2052 /****************************************************************************
2053 ** If p_input.StartDate IS NULL I have to figure out what the current day
2054 ****************************************************************************/
2055 IF (p_Input.StartDate IS NULL)
2056 THEN
2057 l_QueryDate := l_LoggedOnToday;
2058 ELSE
2059 l_QueryDate := p_Input.StartDate;
2060 END IF;
2061
2062 -- get the name for sunday from a known date
2063 l_SundayDate := TO_DATE('1995/01/01','yyyy/mm/dd');
2064 l_SaturdayDate := l_SundayDate - 1;
2065
2066 /*****************************************************************************
2067 ** Depending on the QueryMode we have to determine the QueryStartDate and the
2068 ** QueryEndDate
2069 ** - 1 = Daily view
2070 ** - 2 = Weekly view
2071 ** - 3 = Monthly view
2072 ** - 4 = Combi view (daily + todo list)
2073 *****************************************************************************/
2074 IF (p_Input.QueryMode IN (1,4))
2075 THEN
2076 /***************************************************************************
2077 ** Daily is easy..
2078 ***************************************************************************/
2079 l_QueryStartDate := TRUNC(l_QueryDate); -- today 00:00:00
2080 l_QueryEndDate := (TRUNC(l_QueryDate) + 1) - (1/24/60/60); -- today 23:59:59
2081 l_QueryDays := 1;
2082 ELSIF (p_Input.QueryMode = 2)
2083 THEN
2084 /***************************************************************************
2085 ** Weekly View
2086 ***************************************************************************/
2087 l_QueryStartDate := p_input.StartDate;
2088 l_QueryEndDate := p_input.EndDate;
2089
2090 ELSIF (p_Input.QueryMode = 3)
2091 THEN
2092 --REMOVE THE LOGIC FOR MONTHLY VIEW END DATE. USE ENDDATE INPUT PARAMETER INSTEAD.
2093 l_QueryStartDate := p_input.StartDate;
2094 l_QueryEndDate := p_input.EndDate;
2095 IF l_QueryEndDate IS NULL THEN
2096 l_QueryEndDate := sysdate + 31;
2097 l_QueryDays := 0;
2098 END IF;
2099 /***************************************************************************
2100 ** Monthly is easy too
2101 ***************************************************************************/
2102
2103 -- Modified by jawang on 09/26/2002 to show previous and next month's appoints and tasks for a given month as well
2104 /*l_QueryStartDate := TRUNC(l_QueryDate,'MON'); -- start of month 00:00:00
2105 l_DayNumber := MOD((TRUNC(l_QueryStartDate) - l_SundayDate),7);
2106
2107 IF (l_DayNumber>0)
2108 THEN
2109 l_QueryStartDate := l_QueryStartDate - l_DayNumber;
2110 ELSIF (l_DayNumber<0) THEN
2111 l_QueryStartDate := l_QueryStartDate - (7 + l_DayNumber);
2112 END IF;
2113
2114 l_QueryEndDate := TRUNC(LAST_DAY(l_QueryDate));-- end of month 00:00:00
2115 l_DayNumber := MOD((TRUNC(l_QueryEndDate) - l_SaturdayDate),7);
2116
2117 IF (l_DayNumber>0)
2118 THEN
2119 l_QueryEndDate := l_QueryEndDate + (7-l_DayNumber);
2120 ELSIF (l_DayNumber<0) THEN
2121 l_QueryEndDate := l_QueryEndDate - l_DayNumber;
2122 END IF;
2123 l_QueryEndDate := l_QueryEndDate + (1 - (1/24/60/60));
2124 l_QueryDays := 0;*/
2125
2126 END IF;
2127
2128 /*****************************************************************************
2129 ** Adjust the preferences to reflect the period
2130 *****************************************************************************/
2131 l_DayNumber := MOD((TRUNC(l_QueryStartDate) - l_SundayDate),7);
2132 IF (l_DayNumber>=0)
2133 THEN
2134 l_DayNumber := 1 + l_DayNumber;
2135 ELSE
2136 l_DayNumber := 1 - l_DayNumber;
2137 END IF;
2138 l_MinDayTime := 50;
2139 l_MaxDayTime := -1;
2140 /*FOR I IN l_DayNumber .. l_DayNumber+l_QueryDays-1
2141 LOOP
2142 NULL;
2143 IF (l_WeekTimePrefTbl.EXISTS(I))
2144 THEN
2145 IF (l_WeekTimePrefTbl(I).DayStart BETWEEN 0 AND 23)
2146 THEN
2147 IF (l_MinDayTime>l_WeekTimePrefTbl(I).DayStart)
2148 THEN
2149 l_MinDayTime := l_WeekTimePrefTbl(I).DayStart;
2150 END IF;
2151 END IF;
2152 IF (l_WeekTimePrefTbl(I).DayEnd BETWEEN 0 AND 23)
2153 THEN
2154 IF (l_MaxDayTime<l_WeekTimePrefTbl(I).DayEnd)
2155 THEN
2156 l_MaxDayTime := l_WeekTimePrefTbl(I).DayEnd;
2157 END IF;
2158 END IF;
2159 END IF;
2160 END LOOP;*/
2161 IF (l_MinDayTime=50)
2162 THEN
2163 l_MinDayTime := 9;
2164 END IF;
2165 IF (l_MaxDayTime<0)
2166 THEN
2167 l_MaxDayTime := 18;
2168 END IF;
2169 IF (l_MaxDayTime<l_MinDayTime)
2170 THEN
2171 l_MinDayTime := 9;
2172 l_MaxDayTime := 18;
2173 END IF;
2174 /*x_Preferences.MinStartTime := TO_DATE(TO_CHAR(l_QueryStartDate,'DD-MON-YYYY')||
2175 ' '||TO_CHAR(l_MinDayTime),'DD-MON-YYYY hh24');
2176 x_Preferences.MaxEndTime := TO_DATE(TO_CHAR(l_QueryEndDate,'DD-MON-YYYY')||
2177 ' '||TO_CHAR(l_MaxDayTime),'DD-MON-YYYY hh24');*/
2178
2179 -- Bug # 4189292, amigupta, code commented for bringing Group Appointments
2180
2181 /*****************************************************************************
2182 ** If it's a personal calendar we need to super impose the tasks/appointments
2183 ** of groups we subscribed to
2184 *****************************************************************************/
2185
2186 --RDESPOTO, 04/09/2004
2187
2188 /* IF (l_QueryRSType = 'RS_EMPLOYEE')
2189 THEN
2190 FOR r_Groups IN c_Groups(l_QueryRSID)
2191 LOOP <<GROUPS>>
2192
2193 /*************************************************************************
2194 ** The GROUPS loop will get the GROUP_Ids of all Calendar groups
2195 ** that I am currently a member of
2196 *************************************************************************/
2197 /* GetApptsAndTasks (
2198 l_LoggedOnRSID,
2199 l_LoggedOnRSType,
2200 r_Groups.GroupID,
2201 'RS_GROUP',
2202 l_QueryStartDate,
2203 l_QueryEndDate,
2204 p_input.QueryMode,
2205 l_CalSpanDaysProfile,
2206 r_Groups.GroupID,
2207 p_input.ShowApts,
2208 p_input.ShowTasks,
2209 p_input.ShowOpenInvite,
2210 p_Input.ShowDeclined,
2211 p_Input.AptFirstDetail,
2212 p_Input.AptSecondDetail,
2213 p_Input.AptThirdDetail,
2214 p_Input.ShowBusyTask,
2215 p_Input.ShowFreeTask,
2216 p_Input.ShowTentativeTask,
2217 p_Input.TaskFirstDetail,
2218 p_Input.TaskSecondDetail,
2219 p_Input.TaskThirdDetail,
2220 'Y', -- we are in "personal" calendar
2221 l_index,
2222 x_DisplayItems
2223 --x_Preferences
2224 );
2225 END LOOP GROUPS;
2226 END IF;
2227 */
2228
2229 /*****************************************************************************
2230 ** No matter what the resource type get the TASKS for the query user
2231 *****************************************************************************/
2232
2233 GetApptsAndTasks (
2234 l_LoggedOnRSID,
2235 l_LoggedOnRSType,
2236 l_QueryRSID,
2237 l_QueryRSType,
2238 l_QueryStartDate,
2239 l_QueryEndDate,
2240 p_input.QueryMode,
2241 l_CalSpanDaysProfile,
2242 NULL,
2243 p_input.ShowApts,
2244 p_input.ShowTasks,
2245 p_input.ShowOpenInvite,
2246 p_Input.ShowDeclined,
2247 p_Input.AptFirstDetail,
2248 p_Input.AptSecondDetail,
2249 p_Input.AptThirdDetail,
2250 p_Input.ShowBusyTask,
2251 p_Input.ShowFreeTask,
2252 p_Input.ShowTentativeTask,
2253 p_Input.TaskFirstDetail,
2254 p_Input.TaskSecondDetail,
2255 p_Input.TaskThirdDetail,
2256 p_Input.ViewTimezoneID,
2257 'N',
2258 l_query_user_access,
2259 l_index,
2260 x_DisplayItems
2261 );
2262 IF (p_input.ShowEvents) = 'Y' THEN
2263 GetItems (
2264 l_LoggedOnRSID,
2265 l_LoggedOnRSType,
2266 l_QueryRSID,
2267 l_QueryRSType,
2268 l_QueryStartDate,
2269 l_QueryEndDate,
2270 p_input.QueryMode,
2271 p_Input.ViewTimezoneID,
2272 'N',
2273 NULL,
2274 l_index,
2275 x_DisplayItems
2276 );
2277 END IF;
2278 /* ER# 3740057, amigupta, Call to getBookings procedure in order to fetch Bookings */
2279 GetBookings(
2280 l_LoggedOnRSID,
2281 l_LoggedOnRSType,
2282 l_QueryRSID,
2283 l_QueryRSType,
2284 l_QueryStartDate,
2285 l_QueryEndDate,
2286 p_input.QueryMode,
2287 p_Input.ViewTimezoneID,
2288 l_CalSpanDaysProfile,
2289 NULL,
2290 p_input.ShowBookings, -- p_input.ShowBookings
2291 l_query_user_access,
2292 l_index,
2293 x_DisplayItems
2294 );
2295
2296 IF p_input.ShowSchedules = 'Y' THEN
2297 GET_SCHEDULES
2298 (p_object_type => l_query_per_rs_type
2299 ,p_object_id => l_query_per_rs_id
2300 ,p_start_date => l_QueryStartDate
2301 ,p_end_date => l_QueryEndDate
2302 ,p_timezone_id => p_input.EmpLocTimezoneId
2303 ,p_view_timezone => p_input.ViewTimezoneID
2304 ,x_items => x_DisplayItems);
2305 END IF;
2306
2307 IF p_input.ShowHRCalendarEvents = 'Y' THEN
2308 GET_CAL_EVENTS
2309 (p_object_type => l_query_per_rs_type
2310 ,p_object_id => l_query_per_rs_id
2311 ,p_start_date => l_QueryStartDate
2312 ,p_end_date => l_QueryEndDate
2313 ,x_items => x_DisplayItems);
2314 END IF;
2315
2316 /*****************************************************************************
2317 ** Almost done, just have to sort the table
2318 *****************************************************************************/
2319 SortTable(x_DisplayItems);
2320
2321 END GetView;
2322
2323 FUNCTION GetItemType
2324 /*****************************************************************************
2325 ** This function will return the calendar item type based on whether
2326 ** its begin and end date/time are defined. The type will determine
2327 ** where on the calendar the item is shown 1=Calendar 2=task 3=memo 5=split
2328 *****************************************************************************/
2329 ( p_SourceCode IN VARCHAR2
2330 , p_PeriodStartDate IN DATE
2331 , p_PeriodEndDate IN DATE
2332 , p_StartDate IN DATE
2333 , p_EndDate IN DATE
2334 , p_CalSpanDaysProfile IN VARCHAR2
2335 )RETURN NUMBER
2336 IS
2337 BEGIN
2338
2339 IF (p_StartDate IS NULL)
2340 THEN
2341 /************************************************************************
2342 ** Blank start date items are no views candidate
2343 ************************************************************************/
2344 RETURN 2;
2345 END IF;
2346 IF (p_EndDate IS NULL)
2347 THEN
2348 /**********************************************************************
2349 ** Untimed calendar items are always displayed as memo
2350 **********************************************************************/
2351 RETURN 3;
2352 ELSIF (TRUNC(p_StartDate) = TRUNC (p_EndDate))
2353 THEN
2354 /**********************************************************************
2355 ** It's completely within the period, so it should be shown on the
2356 ** calendar and the task list based on source
2357 **********************************************************************/
2358 /*All day appointment end date is in "23 hour 59 mins" format" to fix bug 3465725*/
2359 IF ((p_EndDate - p_StartDate) = 0 OR (p_EndDate - p_StartDate)*24*60 = 1439)
2360 THEN
2361 /** Bug 2863891, don't show tasks on top of the page **/
2362 IF (p_SourceCode <> 'APPOINTMENT') THEN
2363 RETURN 1;
2364 ELSE
2365 RETURN 3;
2366 END IF;
2367 ELSE
2368 RETURN 1;
2369 END IF;
2370 ELSE
2371 /**********************************************************************
2372 ** It spans accross multiple days : split accross all days
2373 **********************************************************************/
2374 IF (p_CalSpanDaysProfile = 'Y')
2375 THEN
2376 RETURN 5;
2377 ELSE
2378 RETURN 3;
2379 END IF;
2380 END IF;
2381 --RDESPOTO, 04/05/2003, return 'All Day' as default item type
2382 RETURN 3;
2383 END GetItemType;
2384
2385 FUNCTION get_Contact(p_party_type_code in varchar2,p_party_id IN NUMBER)
2386 RETURN VARCHAR2
2387 IS
2388 person_party_name varchar2(25);
2389 BEGIN
2390 Select per.party_name into person_party_name
2391 From hz_parties per, hz_relationships hr
2392 Where hr.subject_table_name = 'HZ_PARTIES'
2393 and hr.object_table_name = 'HZ_PARTIES'
2394 and hr.directional_flag = 'F'
2395 and hr.subject_id = per.party_id
2396 and per.party_type = 'PERSON'
2397 and hr.party_id = p_party_id;
2398 RETURN person_party_name ;
2399 EXCEPTION
2400 WHEN OTHERS THEN
2401 RETURN NULL;
2402 END get_Contact;
2403
2404 END CAC_VIEW_PVT;
2405