1 PACKAGE BODY JTF_CAL_UTILITY_PVT AS
2 /* $Header: jtfvcub.pls 120.1 2005/07/02 01:39:10 appldev ship $ */
3
4 FUNCTION GetUserName
5 /*****************************************************************************
6 ** This function will pick up the user name of the given Resource ID
7 *****************************************************************************/
8 (p_resource_id IN NUMBER
9 )RETURN VARCHAR2
10 IS
11
12 CURSOR c_GetInfo
13 ( b_resource_id IN NUMBER
14 )IS SELECT jrb.user_name
15 FROM jtf_rs_resource_extns jrb
16 WHERE jrb.resource_id = b_resource_id;
17
18 l_UserName VARCHAR2(360);
19
20 BEGIN
21 IF (c_GetInfo%ISOPEN)
22 THEN
23 CLOSE c_GetInfo;
24 END IF;
25
26 OPEN c_GetInfo(p_resource_id);
27 FETCH c_GetInfo INTO l_UserName;
28
29 IF (c_GetInfo%FOUND)
30 THEN
31 IF (c_GetInfo%ISOPEN)
32 THEN
33 CLOSE c_GetInfo;
34 END IF;
35 RETURN l_UserName;
36 ELSE
37 IF (c_GetInfo%ISOPEN)
38 THEN
39 CLOSE c_GetInfo;
40 END IF;
41 RETURN NULL;
42 END IF;
43 EXCEPTION
44 WHEN OTHERS
45 THEN
46 IF (c_GetInfo%ISOPEN)
47 THEN
48 CLOSE c_GetInfo;
49 END IF;
50 RETURN NULL;
51 END GetUserName;
52
53 FUNCTION GetResourceName
54 /****************************************************************
55 ** This function will pick up the name of the given Resource Id.
56 ****************************************************************/
57 (p_resource_id IN NUMBER
58 ,p_resource_type IN VARCHAR2
59 )RETURN VARCHAR2
60 IS
61 CURSOR c_GetGroupInfo(b_resource_id IN NUMBER)
62 IS SELECT jrt.group_name resource_name
63 FROM jtf_rs_groups_tl jrt
64 WHERE jrt.group_id = b_resource_id
65 AND jrt.language = userenv('LANG');
66
67 CURSOR c_GetInduvidualInfo(b_resource_id IN NUMBER)
68 IS SELECT jrt.resource_name
69 FROM jtf_rs_resource_extns_tl jrt
70 WHERE jrt.resource_id = b_resource_id
71 AND jrt.language = userenv('LANG');
72
73 l_ResourceInfo VARCHAR2(360);
74
75 BEGIN
76 IF (p_resource_type = 'RS_EMPLOYEE')
77 THEN
78 IF (c_GetInduvidualInfo%ISOPEN)
79 THEN
80 CLOSE c_GetInduvidualInfo;
81 END IF;
82
83 OPEN c_GetInduvidualInfo(p_resource_id
84 );
85 FETCH c_GetInduvidualInfo INTO l_ResourceInfo;
86 IF (c_GetInduvidualInfo%FOUND)
87 THEN
88 IF (c_GetInduvidualInfo%ISOPEN)
89 THEN
90 CLOSE c_GetInduvidualInfo;
91 END IF;
92 RETURN l_ResourceInfo;
93 ELSE
94 IF (c_GetInduvidualInfo%ISOPEN)
95 THEN
96 CLOSE c_GetInduvidualInfo;
97 END IF;
98 RETURN NULL;
99 END IF;
100 ELSIF (p_resource_type = 'RS_GROUP')
101 THEN
102 IF (c_GetGroupInfo%ISOPEN)
103 THEN
104 CLOSE c_GetGroupInfo;
105 END IF;
106
107 OPEN c_GetGroupInfo(p_resource_id
108 );
109 FETCH c_GetGroupInfo INTO l_ResourceInfo;
110 IF (c_GetGroupInfo%FOUND)
111 THEN
112 IF (c_GetGroupInfo%ISOPEN)
113 THEN
114 CLOSE c_GetGroupInfo;
115 END IF;
116 RETURN l_ResourceInfo;
117 ELSE
118 IF (c_GetGroupInfo%ISOPEN)
119 THEN
120 CLOSE c_GetGroupInfo;
121 END IF;
122 RETURN NULL;
123 END IF;
124 ELSE
125 RETURN NULL;
126 END IF;
127 EXCEPTION
128 WHEN OTHERS
129 THEN
130 IF (c_GetGroupInfo%ISOPEN)
131 THEN
132 CLOSE c_GetGroupInfo;
133 END IF;
134 IF (c_GetInduvidualInfo%ISOPEN)
135 THEN
136 CLOSE c_GetInduvidualInfo;
137 END IF;
138 RETURN NULL;
139 END GetResourceName;
140
141 PROCEDURE GetResourceInfo
142 /***************************************************************
143 ** This Procedure will return the ResourceID and ResourceType
144 ** for the current FND_USER
145 ***************************************************************/
146 ( p_UserID IN NUMBER
147 , x_ResourceID OUT NOCOPY NUMBER
148 , x_ResourceType OUT NOCOPY VARCHAR2
149 )IS
150 CURSOR c_ResourceInfo
151 ( b_UserID NUMBER
152 )IS SELECT jrb.resource_id ResourceID
153 , 'RS_'||jrb.category ResourceType
154 FROM jtf_rs_resource_extns jrb
155 WHERE jrb.user_id = b_UserID;
156
157 r_ResourceInfo c_ResourceInfo%ROWTYPE;
158
159 BEGIN
160 IF (c_ResourceInfo%ISOPEN)
161 THEN
162 CLOSE c_ResourceInfo;
163 END IF;
164 OPEN c_ResourceInfo(p_UserID);
165 FETCH c_ResourceInfo INTO r_ResourceInfo;
166 IF (c_ResourceInfo%FOUND)
167 THEN
168 CLOSE c_ResourceInfo;
169 x_ResourceID := r_ResourceInfo.ResourceID;
170 x_ResourceType := r_ResourceInfo.ResourceType;
171 ELSE
172 CLOSE c_ResourceInfo;
173 x_ResourceID := NULL;
174 x_ResourceType := NULL;
175 END IF;
176 END GetResourceInfo;
177
178 PROCEDURE GetResourceInfo
179 /***************************************************************
180 ** This Procedure will return the ResourceID and ResourceName
181 ** for the current FND_USER
182 ***************************************************************/
183 ( x_ResourceID OUT NOCOPY VARCHAR2
184 , x_ResourceName OUT NOCOPY VARCHAR2
185 )IS
186 CURSOR c_ResourceInfo
187 IS SELECT jrb.resource_id ResourceID
188 , jrt.resource_name ResourceName
189 FROM jtf_rs_resource_extns jrb
190 , jtf_rs_resource_extns_tl jrt
191 WHERE jrb.resource_id = jrt.resource_id
192 AND jrt.language = userenv('LANG')
193 AND jrb.user_id = to_number(fnd_profile.value('USER_ID'));
194
195 r_ResourceInfo c_ResourceInfo%ROWTYPE;
196
197 BEGIN
198 IF (c_ResourceInfo%ISOPEN)
199 THEN
200 CLOSE c_ResourceInfo;
201 END IF;
202 OPEN c_ResourceInfo;
203 FETCH c_ResourceInfo INTO r_ResourceInfo;
204 IF (c_ResourceInfo%FOUND)
205 THEN
206 CLOSE c_ResourceInfo;
207 x_ResourceID := r_ResourceInfo.ResourceID;
208 x_ResourceName := r_ResourceInfo.ResourceName;
209 ELSE
210 CLOSE c_ResourceInfo;
211 x_ResourceID := -1;
212 x_ResourceName := NULL;
213 END IF;
214 END GetResourceInfo;
215
216
217 PROCEDURE GetResourceInfo
218 /***************************************************************
219 ** This Procedure will return the ResourceID and ResourceName
220 ** for the passed user id
221 ***************************************************************/
222 ( p_userid IN NUMBER
223 , x_ResourceID OUT NOCOPY NUMBER
224 , x_ResourceType OUT NOCOPY VARCHAR2
225 , x_ResourceName OUT NOCOPY VARCHAR2
226 )IS
227 CURSOR c_ResourceInfo
228 ( b_UserID NUMBER
229 ) IS SELECT jrb.resource_id ResourceID
230 , 'RS_'||jrb.category ResourceType
231 , jrt.resource_name ResourceName
232 FROM jtf_rs_resource_extns jrb
233 , jtf_rs_resource_extns_tl jrt
234 WHERE jrb.resource_id = jrt.resource_id
235 AND jrt.language = userenv('LANG')
236 AND jrb.user_id = b_UserID;
237
238 r_ResourceInfo c_ResourceInfo%ROWTYPE;
239
240 BEGIN
241 IF (c_ResourceInfo%ISOPEN)
242 THEN
243 CLOSE c_ResourceInfo;
244 END IF;
245 OPEN c_ResourceInfo(p_userid);
246 FETCH c_ResourceInfo INTO r_ResourceInfo;
247 IF (c_ResourceInfo%FOUND)
248 THEN
249 CLOSE c_ResourceInfo;
250 x_ResourceID := r_ResourceInfo.ResourceID;
251 x_ResourceType := r_ResourceInfo.ResourceType;
252 x_ResourceName := r_ResourceInfo.ResourceName;
253 ELSE
254 CLOSE c_ResourceInfo;
255 x_ResourceID := -1;
256 x_ResourceType := NULL;
257 x_ResourceName := NULL;
258 END IF;
259 END GetResourceInfo;
260
261 FUNCTION GetItemType
262 /*****************************************************************************
263 ** This function will return the calendar item type based on whether
264 ** its begin and end date/time are defined. The type will determine
265 ** where on the calendar the item is shown 1=Calendar 2=task 3=memo 5=split
266 *****************************************************************************/
267 ( p_SourceCode IN VARCHAR2
268 , p_PeriodStartDate IN DATE
269 , p_PeriodEndDate IN DATE
270 , p_StartDate IN DATE
271 , p_EndDate IN DATE
272 , p_CalSpanDaysProfile IN VARCHAR2
273 )RETURN NUMBER
274 IS
275 BEGIN
276
277 IF (p_StartDate IS NULL)
278 THEN
279 /************************************************************************
280 ** Blank start date items are no views candidate
281 ************************************************************************/
282 RETURN 2;
283 END IF;
284 IF (p_EndDate IS NULL)
285 THEN
286 /**********************************************************************
287 ** Untimed calendar items are always displayed as memo
288 **********************************************************************/
289 RETURN 3;
290 ELSIF (TRUNC(p_StartDate) = TRUNC (p_EndDate))
291 THEN
292 /**********************************************************************
293 ** It's completely within the period, so it should be shown on the
294 ** calendar and the task list based on source
295 **********************************************************************/
296 /*All day appointment end date is in "23 hour 59 mins" format" to fix bug 3465725*/
297 IF ((p_EndDate - p_StartDate) = 0 OR (p_EndDate - p_StartDate)*24*60 = 1439)
298 THEN
299 /** Bug 2863891, don't show tasks on top of the page **/
300 IF (p_SourceCode <> 'APPOINTMENT') THEN
301 RETURN 1;
302 ELSE
303 RETURN 3;
304 END IF;
305 ELSE
306 RETURN 1;
307 END IF;
308 ELSE
309 /**********************************************************************
310 ** It spans accross multiple days : split accross all days
311 **********************************************************************/
312 IF (p_CalSpanDaysProfile = 'Y')
313 THEN
314 RETURN 5;
315 ELSE
316 RETURN 3;
317 END IF;
318 END IF;
319 --RDESPOTO, 04/05/2003, return 'All Day' as default item type
320 RETURN 3;
321 END GetItemType;
322
323
324 FUNCTION GetItemType
325 /*****************************************************************************
326 ** This function will return the calendar item type based on whether
327 ** its begin and end date/time are defined. The type will determine
328 ** where on the calendar the item is shown 1=Calendar 2=task 3=memo
329 *****************************************************************************/
330 ( p_SourceCode IN VARCHAR2
331 , p_PeriodStartDate IN DATE
332 , p_PeriodEndDate IN DATE
333 , p_StartDate IN DATE
334 , p_EndDate IN DATE
335 )RETURN NUMBER
336 IS
337 BEGIN
338 RETURN GetItemType
339 (
340 p_SourceCode,
341 p_PeriodStartDate,
342 p_PeriodEndDate,
343 p_StartDate,
344 p_EndDate,
345 fnd_profile.value('JTF_CAL_SPAN_DAYS')
346 );
347 /*
348 IF (p_StartDate IS NULL)
349 THEN
350 ************************************************************************
351 ** Blank start date items are no views candidate
352 ************************************************************************
353 RETURN 2;
354 END IF;
355
356 IF (p_SourceCode = 'APPOINTMENT')
357 THEN
358 ************************************************************************
359 ** It's an appointment apply appointment rules
360 ************************************************************************
361 IF ((p_EndDate - p_StartDate) = 0)
362 THEN
363 **********************************************************************
364 ** Untimed Appointments are always displayed as memo
365 **********************************************************************
366 RETURN 3;
367 ELSIF (p_StartDate < p_PeriodStartDate)
368 THEN
369 **********************************************************************
370 ** It started before the period we want to display: memo
371 **********************************************************************
372 RETURN 3;
373 ELSIF (p_EndDate > p_PeriodEndDate)
374 THEN
375 **********************************************************************
376 ** It ended after the period we want to display: memo
377 **********************************************************************
378 RETURN 3;
379 ELSE
380 **********************************************************************
381 ** It's completely within the period we want to display: Calendar
382 **********************************************************************
383 RETURN 1;
384 END IF;
385 ELSIF (p_SourceCode = 'CALENDARITEM')
386 THEN
387 ************************************************************************
388 ** It's a calendar item apply calendar item rules
389 ************************************************************************
390 IF (p_EndDate IS NULL)
391 THEN
392 **********************************************************************
393 ** Untimed calendar items are always displayed as memo
394 **********************************************************************
395 RETURN 3;
396 ELSIF (TRUNC(p_StartDate) <> TRUNC (p_EndDate))
397 THEN
398 **********************************************************************
399 ** It spans accross multiple days : memo
400 **********************************************************************
401 RETURN 3;
402 ELSE
403 **********************************************************************
404 ** It's completely within the period we want to display: Calendar
405 **********************************************************************
406 RETURN 1;
407 END IF;
408 ELSE
409 ************************************************************************
410 ** It's a Task apply rules for tasks
411 ************************************************************************
412 IF (p_EndDate IS NULL)
413 THEN
414 **********************************************************************
415 ** Untimed calendar items are always displayed as memo
416 **********************************************************************
417 RETURN 3;
418 ELSIF (TRUNC(p_StartDate) = TRUNC (p_EndDate))
419 THEN
420 **********************************************************************
421 ** It's completely within the period, so it should be shown on the
422 ** calendar and the task list
423 **********************************************************************
424 RETURN 4;
425 ELSE
426 **********************************************************************
427 ** It spans accross multiple days : memo
428 **********************************************************************
429 RETURN 3;
430 END IF;
431 END IF;
432 */
433 END GetItemType;
434
435 FUNCTION GetItemStatus( p_task_status_id IN NUMBER)RETURN VARCHAR2
436 IS
437 CURSOR c_Status(b_task_status_id IN NUMBER)
438 IS SELECT jtl.Name
439 FROM jtf_task_statuses_tl jtl
440 WHERE jtl.task_status_id = b_task_status_id
441 AND jtl.language = userenv('LANG');
442
443 r_Status c_Status%ROWTYPE;
444
445 BEGIN
446 IF (c_status%ISOPEN)
447 THEN
448 CLOSE c_status;
449 END IF;
450 OPEN c_Status(p_task_status_id);
451 FETCH c_Status INTO r_Status;
452 IF (c_Status%FOUND)
453 THEN
454 IF (c_status%ISOPEN)
455 THEN
456 CLOSE c_status;
457 END IF;
458 RETURN r_Status.Name;
459 ELSE
460 IF (c_status%ISOPEN)
461 THEN
462 CLOSE c_status;
463 END IF;
464 RETURN NULL;
465 END IF;
466 EXCEPTION
467 WHEN OTHERS
468 THEN
469 IF (c_status%ISOPEN)
470 THEN
471 CLOSE c_status;
472 END IF;
473 RETURN NULL;
474
475 END GetItemStatus;
476
477 FUNCTION GetItemURL( p_task_id IN NUMBER
478 , p_task_source_code IN VARCHAR2
479 )RETURN VARCHAR2
480 IS
481 /**
482 CURSOR c_URL(b_task_source_code IN VARCHAR2)
483 IS SELECT SUBSTR(fff.web_html_call,1,
484 DECODE(INSTR(fff.web_html_call,'?'),0,LENGTH(fff.web_html_call),
485 INSTR(fff.web_html_call,'?')-1)) agent
486 FROM fnd_form_functions fff, jtf_objects_b jtb
487 WHERE jtb.object_code = b_task_source_code
488 AND fff.fUNCTION_ID = jtb.web_function_id;
489 **/
490 CURSOR c_URL(b_task_source_code IN VARCHAR2)
491 IS SELECT jtb.URL agent
492 FROM jtf_objects_b jtb
493 WHERE jtb.object_code = b_task_source_code;
494
495 r_URL c_URL%ROWTYPE;
496
497 BEGIN
498 IF (p_task_source_code='APPOINTMENT')
499 THEN
500 RETURN 'jtfCalApptMain.jsp';
501 ELSE
502 RETURN 'jtfTaskMain.jsp';
503 END IF;
504
505 /**COMMENTED
506 IF (c_URL%ISOPEN)
507 THEN
508 CLOSE c_URL;
509 END IF;
510 OPEN c_URL(p_task_source_code);
511 FETCH c_URL INTO r_URL;
512 IF (c_URL%FOUND)
513 THEN
514 IF (c_URL%ISOPEN)
515 THEN
516 CLOSE c_URL;
517 END IF;
518 RETURN r_URL.agent;
519 ELSE
520 IF (c_URL%ISOPEN)
521 THEN
522 CLOSE c_URL;
523 END IF;
524 RETURN NULL;
525 END IF;
526 **/
527 EXCEPTION
528 WHEN OTHERS
529 THEN
530 IF (c_URL%ISOPEN)
531 THEN
532 CLOSE c_URL;
533 END IF;
534 RETURN NULL;
535
536 END GetItemURL;
537
538 FUNCTION GetCategoryName
539 (p_category_id NUMBER)RETURN VARCHAR2
540 IS
541 CURSOR c_category
542 (b_category_id NUMBER)
543 IS SELECT perz_data_desc name
544 FROM jtf_perz_data
545 WHERE perz_data_id = b_category_id;
546
547 r_Category c_Category%ROWTYPE;
548
549 BEGIN
550 IF (c_category%ISOPEN)
551 THEN
552 CLOSE c_category;
553 END IF;
554
555 OPEN c_category(p_category_id);
556
557 FETCH c_category INTO r_category;
558 IF (c_category%FOUND)
559 THEN
560 IF (c_category%ISOPEN)
561 THEN
562 CLOSE c_category;
563 END IF;
564 RETURN r_category.name;
565 ELSE
566 IF (c_category%ISOPEN)
567 THEN
568 CLOSE c_category;
569 END IF;
570 RETURN NULL;
571 END IF;
572 EXCEPTION
573 WHEN OTHERS
574 THEN
575 IF (c_category%ISOPEN)
576 THEN
577 CLOSE c_category;
578 END IF;
579 RETURN NULL;
580 END GetCategoryName;
581
582 FUNCTION GetTaskPriority
583 (p_task_priority_id NUMBER)RETURN VARCHAR2
584 IS
585 CURSOR c_Priority
586 (b_task_priority_id NUMBER)
587 IS SELECT name
588 FROM jtf_task_priorities_tl
589 WHERE task_priority_id = b_task_priority_id
590 AND language = userenv('LANG');
591
592 r_Priority c_Priority%ROWTYPE;
593
594 BEGIN
595 IF (c_Priority%ISOPEN)
596 THEN
597 CLOSE c_Priority;
598 END IF;
599
600 OPEN c_Priority(p_task_priority_id);
601
602 FETCH c_priority INTO r_priority;
603 IF (c_priority%FOUND)
604 THEN
605 IF (c_Priority%ISOPEN)
606 THEN
607 CLOSE c_Priority;
608 END IF;
609 RETURN r_priority.name;
610 ELSE
611 IF (c_Priority%ISOPEN)
612 THEN
613 CLOSE c_Priority;
614 END IF;
615 RETURN NULL;
616 END IF;
617 EXCEPTION
618 WHEN OTHERS
619 THEN
620 IF (c_Priority%ISOPEN)
621 THEN
622 CLOSE c_Priority;
623 END IF;
624 RETURN NULL;
625 END GetTaskPriority;
626
627 FUNCTION TaskHasNotes
628 (p_task_id IN NUMBER)RETURN VARCHAR2
629 IS
630
631 CURSOR c_notes(b_task_id IN NUMBER)
632 IS SELECT jtf_note_id
633 FROM jtf_notes_b
634 WHERE source_object_code = 'TASK'
635 AND source_object_id = b_task_id
636 AND ROWNUM = 1;
637
638 l_note_id NUMBER;
639
640 BEGIN
641 IF (c_notes%ISOPEN)
642 THEN
643 CLOSE c_Notes;
644 END IF;
645
646 OPEN c_notes(p_task_id);
647
648 FETCH c_notes INTO l_note_id;
649 IF (c_notes%FOUND)
650 THEN
651 IF (c_notes%ISOPEN)
652 THEN
653 CLOSE c_notes;
654 END IF;
655 RETURN 'Y';
656 ELSE
657 IF (c_notes%ISOPEN)
658 THEN
659 CLOSE c_notes;
660 END IF;
661 RETURN 'N';
662 END IF;
663 EXCEPTION
664 WHEN OTHERS
665 THEN
666 IF (c_notes%ISOPEN)
667 THEN
668 CLOSE c_notes;
669 END IF;
670 RETURN 'N';
671 END TaskHasNotes;
672
673 FUNCTION GetGroupColor
674 (p_ResourceID NUMBER -- Resource ID of calendar user
675 ,p_ResourceType VARCHAR2 -- Resource Type of Calendar User
676 ,p_GroupID NUMBER -- Group ID of Group Calendar
677 )RETURN VARCHAR2
678 IS CURSOR c_Group
679 (b_ResourceID NUMBER -- Resource ID of calendar user
680 ,b_ResourceType VARCHAR2 -- Resource Type of Calendar User
681 ,b_GroupID NUMBER -- Group ID of Group Calendar
682 )IS SELECT jpa.attribute_value color
683 FROM jtf_perz_profile jpp
684 , jtf_perz_data jpd
685 , jtf_perz_data_attrib jpa
686 WHERE jpp.profile_name = to_char(b_ResourceID)||':JTF_CAL'
687 AND jpp.profile_id = jpd.profile_id
688 AND jpa.perz_data_id = jpd.perz_data_id
689 AND jpa.attribute_name = to_char(b_GroupID)||':COLOR';
690
691 l_color VARCHAR2(300);
692
693 BEGIN
694
695 IF (c_Group%ISOPEN)
696 THEN
697 CLOSE c_Group;
698 END IF;
699
700 OPEN c_Group(p_ResourceID
701 ,p_ResourceType
702 ,p_GroupID
703 );
704
705 FETCH c_Group INTO l_Color;
706 IF (c_Group%FOUND)
707 THEN
708 IF (c_Group%ISOPEN)
709 THEN
710 CLOSE c_Group;
711 END IF;
712 RETURN l_Color;
713 ELSE
714 IF (c_Group%ISOPEN)
715 THEN
716 CLOSE c_Group;
717 END IF;
718 RETURN NULL;
719 END IF;
720 EXCEPTION
721 WHEN OTHERS
722 THEN
723 IF (c_Group%ISOPEN)
724 THEN
725 CLOSE c_Group;
726 END IF;
727 RETURN NULL;
728 END GetGroupColor;
729
730 FUNCTION GetGroupColorName(p_ResourceID NUMBER -- Resource ID of calendar user
731 ,p_ResourceType VARCHAR2 -- Resource Type of Calendar User
732 ,p_GroupID NUMBER -- Group ID of Group Calendar
733 )RETURN VARCHAR2
734 IS CURSOR c_Group
735 (b_ResourceID NUMBER -- Resource ID of calendar user
736 ,b_ResourceType VARCHAR2 -- Resource Type of Calendar User
737 ,b_GroupID NUMBER -- Group ID of Group Calendar
738 )IS SELECT flu.meaning color
739 FROM jtf_perz_profile jpp
740 , jtf_perz_data jpd
741 , jtf_perz_data_attrib jpa
742 , fnd_lookups flu
743 WHERE jpp.profile_name = to_char(b_ResourceID)||':JTF_CAL'
744 AND jpp.profile_id = jpd.profile_id
745 AND jpa.perz_data_id = jpd.perz_data_id
746 AND jpa.attribute_name = to_char(b_GroupID)||':COLOR'
747 AND flu.lookup_type = 'JTF_CALND_GROUP_COLOR'
748 AND flu.lookup_code = jpa.attribute_value;
749
750 l_color VARCHAR2(300);
751
752 BEGIN
753
754 IF (c_Group%ISOPEN)
755 THEN
756 CLOSE c_Group;
757 END IF;
758
759 OPEN c_Group(p_ResourceID
760 ,p_ResourceType
761 ,p_GroupID
762 );
763
764 FETCH c_Group INTO l_Color;
765 IF (c_Group%FOUND)
766 THEN
767 IF (c_Group%ISOPEN)
768 THEN
769 CLOSE c_Group;
770 END IF;
771 RETURN l_Color;
772 ELSE
773 IF (c_Group%ISOPEN)
774 THEN
775 CLOSE c_Group;
776 END IF;
777 RETURN NULL;
778 END IF;
779 EXCEPTION
780 WHEN OTHERS
781 THEN
782 IF (c_Group%ISOPEN)
783 THEN
784 CLOSE c_Group;
785 END IF;
786 RETURN NULL;
787 END GetGroupColorName;
788
789 FUNCTION GetGroupPrefix(p_ResourceID NUMBER -- Resource ID of calendar user
790 ,p_ResourceType VARCHAR2 -- Resource Type of Calendar User
791 ,p_GroupID NUMBER -- Group ID of Group Calendar
792 )RETURN VARCHAR2
793
794 IS CURSOR c_Group
795 (b_ResourceID NUMBER -- Resource ID of calendar user
796 ,b_ResourceType VARCHAR2 -- Resource Type of Calendar User
797 ,b_GroupID NUMBER -- Group ID of Group Calendar
798 )IS SELECT jpa.attribute_value color
799 FROM jtf_perz_profile jpp
800 , jtf_perz_data jpd
801 , jtf_perz_data_attrib jpa
802 WHERE jpp.profile_name = to_char(b_ResourceID)||':JTF_CAL'
803 AND jpp.profile_id = jpd.profile_id
804 AND jpa.perz_data_id = jpd.perz_data_id
805 AND jpa.attribute_name = to_char(b_GroupID)||':PREFIX';
806
807 l_prefix VARCHAR2(300);
808
809 BEGIN
810
811 IF (c_Group%ISOPEN)
812 THEN
813 CLOSE c_Group;
814 END IF;
815
816 OPEN c_Group(p_ResourceID
817 ,p_ResourceType
818 ,p_GroupID
819 );
820
821 FETCH c_Group INTO l_prefix;
822 IF (c_Group%FOUND)
823 THEN
824 IF (c_Group%ISOPEN)
825 THEN
826 CLOSE c_Group;
827 END IF;
828 RETURN l_prefix;
829 ELSE
830 IF (c_Group%ISOPEN)
831 THEN
832 CLOSE c_Group;
833 END IF;
834 RETURN NULL;
835 END IF;
836 EXCEPTION
837 WHEN OTHERS
838 THEN
839 IF (c_Group%ISOPEN)
840 THEN
841 CLOSE c_Group;
842 END IF;
843 RETURN NULL;
844 END GetGroupPrefix;
845
846 PROCEDURE GetPreferences
847 ( p_ResourceID IN NUMBER
848 , p_ResourceType IN VARCHAR2
849 , x_Preferences OUT NOCOPY JTF_CAL_PVT.Preference
850 , x_WeekTimePrefTbl OUT NOCOPY JTF_CAL_PVT.WeekTimePrefTblType
851 , x_CalSpanDaysProfile OUT NOCOPY VARCHAR2
852 )
853 IS
854 BEGIN
855 GetPreferences
856 (
857 p_ResourceID,
858 p_ResourceType,
859 x_Preferences,
860 x_WeekTimePrefTbl
861 );
862
863 x_CalSpanDaysProfile := fnd_profile.value('JTF_CAL_SPAN_DAYS');
864
865 END GetPreferences;
866
867 PROCEDURE GetPreferences
868 ( p_LoggedOnRSID IN NUMBER
869 , p_LoggedOnRSType IN VARCHAR2
870 , p_QueryRSID IN NUMBER
871 , p_QueryRSType IN VARCHAR2
872 , x_Preferences OUT NOCOPY JTF_CAL_PVT.Preference
873 , x_WeekTimePrefTbl OUT NOCOPY JTF_CAL_PVT.WeekTimePrefTblType
874 , x_CalSpanDaysProfile OUT NOCOPY VARCHAR2
875 ) AS
876 BEGIN
877 GetPreferences
878 (
879 p_LoggedOnRSID,
880 p_LoggedOnRSType,
881 x_Preferences,
882 x_WeekTimePrefTbl
883 );
884
885 --Update preference structure with query res info
886 x_Preferences.LoggedOnRSID := p_QueryRSID;
887 x_Preferences.LoggedOnRSType := p_QueryRSType;
888 x_Preferences.LoggedOnRSName := GetResourceName( p_QueryRSID
889 , p_QueryRSType
890 );
891 x_CalSpanDaysProfile := fnd_profile.value('JTF_CAL_SPAN_DAYS');
892
893 END GetPreferences;
894
895 PROCEDURE GetPreferences
896 ( p_ResourceID IN NUMBER
897 , p_ResourceType IN VARCHAR2
898 , x_Preferences OUT NOCOPY JTF_CAL_PVT.Preference
899 , x_WeekTimePrefTbl OUT NOCOPY JTF_CAL_PVT.WeekTimePrefTblType
900 )
901 IS
902 CURSOR c_Preference
903 (b_ResourceID IN NUMBER
904 )IS SELECT jpa.attribute_name AttributeName
905 , jpa.attribute_value AttributeValue
906 FROM jtf_perz_profile jpp
907 , jtf_perz_data jpd
908 , jtf_perz_data_attrib jpa
909 WHERE jpp.profile_name = to_char(b_ResourceID)||':JTF_CAL'
910 AND jpp.profile_id = jpd.profile_id
911 AND jpd.perz_data_id = jpa.perz_data_id
912 AND jpa.attribute_name IN ( 'APPT_INCR'
913 , 'ISSUE_INVITATION'
914 , 'DISPLAY_ITEMS'
915 , 'CLOCK_FORMAT'
916 , 'APPT_PREFIX'
917 , 'APPT_COLOR'
918 , 'TASK_PREFIX'
919 , 'TASK_COLOR'
920 , 'ITEM_PREFIX'
921 , 'ITEM_COLOR'
922 , 'WEEK_BEGIN'
923 , 'WEEK_END'
924 , 'SUNDAY_START'
925 , 'SUNDAY_END'
926 , 'MONDAY_START'
927 , 'MONDAY_END'
928 , 'TUESDAY_START'
929 , 'TUESDAY_END'
930 , 'WEDNESDAY_START'
931 , 'WEDNESDAY_END'
932 , 'THURSDAY_START'
933 , 'THURSDAY_END'
934 , 'FRIDAY_START'
935 , 'FRIDAY_END'
936 , 'SATURDAY_START'
937 , 'SATURDAY_END'
938 , 'TASK_CUST_SRC' );
939
940 r_Preference c_Preference%ROWTYPE;
941 l_dummyNumber NUMBER:=0;
942
943 BEGIN
944 /*****************************************************************************
945 ** Initializing with x_Preference with defaults
946 *****************************************************************************/
947 x_Preferences.LoggedOnRSID := p_ResourceID;
948 x_Preferences.LoggedOnRSType := p_ResourceType;
949 x_Preferences.SendEmail := 'YES';
950 x_Preferences.DisplayItems := 'NO';
951 x_Preferences.TaskCustomerSource := 'NO';
952 x_Preferences.TimeFormat := '12';
953 x_Preferences.DateFormat := 'DD-MON-YYYY';
954 x_Preferences.TimeZone := to_number(NVL(FND_PROFILE.Value('CLIENT_TIMEZONE_ID'),'4'));
955 x_Preferences.WeekStart := 2;
956 x_Preferences.WeekEnd := 6;
957 x_Preferences.ApptIncrement := 30;
958 x_Preferences.ApptColor := '#663300'; --Brown
959 x_Preferences.ApptPrefix := '';
960 x_Preferences.TaskColor := '#663300'; --Brown
961 x_Preferences.TaskPrefix := '';
962 x_Preferences.ItemColor := '#663300'; --Brown
963 x_Preferences.ItemPrefix := '';
964 x_Preferences.MinStartTime := TO_DATE(TO_CHAR(TRUNC(SYSDATE)
965 ,'DD-MON-YYYY')||' 09:00'
966 ,'DD-MON-YYYY HH24:MI');
967 x_Preferences.MaxEndTime := TO_DATE(TO_CHAR(TRUNC(SYSDATE)
968 ,'DD-MON-YYYY')||' 18:00'
969 ,'DD-MON-YYYY HH24:MI');
970 IF (c_Preference%ISOPEN)
971 THEN
972 CLOSE c_Preference;
973 END IF;
974
975 FOR r_Preference IN c_Preference(p_ResourceID)
976 LOOP
977 /***************************************************************************
978 ** Preferences found for this user: overwrite the defaults
979 ***************************************************************************/
980 IF (r_preference.AttributeName = 'APPT_INCR')
981 THEN
982 x_Preferences.ApptIncrement := TO_NUMBER(r_preference.AttributeValue);
983
984 ELSIF (r_preference.AttributeName = 'ISSUE_INVITATION')
985 THEN
986 x_Preferences.SendEmail := r_preference.AttributeValue;
987
988 ELSIF (r_preference.AttributeName = 'DISPLAY_ITEMS')
989 THEN
990 x_Preferences.DisplayItems := r_preference.AttributeValue;
991
992 ELSIF (r_preference.AttributeName = 'TASK_CUST_SRC')
993 THEN
994 x_Preferences.TaskCustomerSource := r_preference.AttributeValue;
995
996 ELSIF (r_preference.AttributeName = 'CLOCK_FORMAT')
997 THEN
998 x_Preferences.TimeFormat := r_preference.AttributeValue;
999
1000 ELSIF (r_preference.AttributeName = 'APPT_PREFIX')
1001 THEN
1002 x_Preferences.ApptPrefix := r_preference.AttributeValue;
1003
1004 ELSIF (r_preference.AttributeName = 'APPT_COLOR')
1005 THEN
1006 x_Preferences.ApptColor := r_preference.AttributeValue;
1007
1008 ELSIF (r_preference.AttributeName = 'TASK_PREFIX')
1009 THEN
1010 x_Preferences.TaskPrefix := r_preference.AttributeValue;
1011
1012 ELSIF (r_preference.AttributeName = 'TASK_COLOR')
1013 THEN
1014 x_Preferences.TaskColor := r_preference.AttributeValue;
1015
1016 ELSIF (r_preference.AttributeName = 'ITEM_PREFIX')
1017 THEN
1018 x_Preferences.ItemPrefix := r_preference.AttributeValue;
1019
1020 ELSIF (r_preference.AttributeName = 'ITEM_COLOR')
1021 THEN
1022 x_Preferences.ItemColor := r_preference.AttributeValue;
1023
1024 ELSIF (r_preference.AttributeName = 'WEEK_BEGIN')
1025 THEN
1026 x_Preferences.WeekStart := TO_NUMBER(r_preference.AttributeValue);
1027
1028 ELSIF (r_preference.AttributeName = 'WEEK_END')
1029 THEN
1030 x_Preferences.WeekEnd := TO_NUMBER(r_preference.AttributeValue);
1031
1032 ELSIF (r_preference.AttributeName = 'SUNDAY_START')
1033 THEN
1034 x_WeekTimePrefTbl(1).DayStart := TO_NUMBER(r_preference.AttributeValue);
1035
1036 ELSIF (r_preference.AttributeName = 'MONDAY_START')
1037 THEN
1038 x_WeekTimePrefTbl(2).DayStart := TO_NUMBER(r_preference.AttributeValue);
1039
1040 ELSIF (r_preference.AttributeName = 'TUESDAY_START')
1041 THEN
1042 x_WeekTimePrefTbl(3).DayStart := TO_NUMBER(r_preference.AttributeValue);
1043
1044 ELSIF (r_preference.AttributeName = 'WEDNESDAY_START')
1045 THEN
1046 x_WeekTimePrefTbl(4).DayStart := TO_NUMBER(r_preference.AttributeValue);
1047
1048 ELSIF (r_preference.AttributeName = 'THURSDAY_START')
1049 THEN
1050 x_WeekTimePrefTbl(5).DayStart := TO_NUMBER(r_preference.AttributeValue);
1051
1052 ELSIF (r_preference.AttributeName = 'FRIDAY_START')
1053 THEN
1054 x_WeekTimePrefTbl(6).DayStart := TO_NUMBER(r_preference.AttributeValue);
1055
1056 ELSIF (r_preference.AttributeName = 'SATURDAY_START')
1057 THEN
1058 x_WeekTimePrefTbl(7).DayStart := TO_NUMBER(r_preference.AttributeValue);
1059
1060 ELSIF (r_preference.AttributeName = 'SUNDAY_END')
1061 THEN
1062 x_WeekTimePrefTbl(1).DayEnd := TO_NUMBER(r_preference.AttributeValue);
1063
1064 ELSIF (r_preference.AttributeName = 'MONDAY_END')
1065 THEN
1066 x_WeekTimePrefTbl(2).DayEnd := TO_NUMBER(r_preference.AttributeValue);
1067
1068 ELSIF (r_preference.AttributeName = 'TUESDAY_END')
1069 THEN
1070 x_WeekTimePrefTbl(3).DayEnd := TO_NUMBER(r_preference.AttributeValue);
1071
1072 ELSIF (r_preference.AttributeName = 'WEDNESDAY_END')
1073 THEN
1074 x_WeekTimePrefTbl(4).DayEnd := TO_NUMBER(r_preference.AttributeValue);
1075
1076 ELSIF (r_preference.AttributeName = 'THURSDAY_END')
1077 THEN
1078 x_WeekTimePrefTbl(5).DayEnd := TO_NUMBER(r_preference.AttributeValue);
1079
1080 ELSIF (r_preference.AttributeName = 'FRIDAY_END')
1081 THEN
1082 x_WeekTimePrefTbl(6).DayEnd := TO_NUMBER(r_preference.AttributeValue);
1083
1084 ELSIF (r_preference.AttributeName = 'SATURDAY_END')
1085 THEN
1086 x_WeekTimePrefTbl(7).DayEnd := TO_NUMBER(r_preference.AttributeValue);
1087
1088 END IF;
1089 END LOOP;
1090
1091 END GetPreferences;
1092
1093 FUNCTION GetAccessLevel
1094 /*******************************************************************************
1095 ** Function will return the access level for a task/appointment, if the private
1096 ** flag is set the task/appoint is only visible for the owner of the task.
1097 ** - 0 no access: show as anonymous block
1098 ** - 1 owner access (i.e. page level security applies)
1099 ** - 2 read only (i.e. page level gets over ruled)
1100 *******************************************************************************/
1101 ( p_PrivateFlag VARCHAR2
1102 , p_QueryRSRole VARCHAR2
1103 , p_LoggedOnRSID NUMBER
1104 , p_LoggedOnRSType VARCHAR2
1105 , p_QueryRSID NUMBER
1106 , p_QueryRSType VARCHAR2
1107 , p_SourceType VARCHAR2
1108 )RETURN NUMBER
1109 IS
1110 BEGIN
1111 /*****************************************************************************
1112 ** Current user is an assignee and should not see the private tasks
1113 ** Don't do this for Appointment as invitees to private appointment should
1114 ** be able to see it !@#$
1115 *****************************************************************************/
1116 IF (p_QueryRSRole = 'ASSIGNEE')
1117 THEN
1118 /***************************************************************************
1119 ** If Logged on user is not the same as current user then private tasks
1120 ** shouldn't be visible
1121 ***************************************************************************/
1122 IF (((p_QueryRSID <> p_LoggedOnRSID) OR (p_QueryRSType <> p_LoggedOnRSType))
1123 AND (p_PrivateFlag = 'Y'))
1124 THEN
1125 RETURN 0;
1126 /**COMMENTED. ALL PRIVATE TASKS ARE VISIBLE TO ASSIGNEES
1127 ELSIF ((p_SourceType <> 'APPOINTMENT') AND (p_PrivateFlag = 'Y'))
1128 THEN
1129 RETURN 0;
1130 **/
1131 ELSE
1132 RETURN 1;
1133 END IF;
1134
1135 /*****************************************************************************
1136 ** Current user is the owner and should see the private task
1137 *****************************************************************************/
1138 ELSIF( p_QueryRSRole = 'OWNER')
1139 THEN
1140 /***************************************************************************
1141 ** If Logged on user is not the same as current user then private tasks
1142 ** shouldn't be visible
1143 ***************************************************************************/
1144 IF (((p_QueryRSID <> p_LoggedOnRSID) OR (p_QueryRSType <> p_LoggedOnRSType))
1145 AND (p_PrivateFlag = 'Y'))
1146 THEN
1147 RETURN 0;
1148 ELSE
1149 RETURN 1;
1150 END IF;
1151
1152 /*****************************************************************************
1153 ** Current user is not the owner only be allowed to access this task in
1154 ** through the read only page
1155 *****************************************************************************/
1156 ELSE
1157 RETURN 2;
1158 END IF;
1159 END GetAccessLevel;
1160
1161 PROCEDURE AdjustMinMaxTime
1162 /*******************************************************************************
1163 ** If there are appointments/tasks scheduled during the period we are displaying
1164 ** that fall outside the range set in the user preferences the range will be
1165 ** adjusted.
1166 *******************************************************************************/
1167 ( p_StartDate IN DATE
1168 , p_EndDate IN DATE
1169 , p_increment IN NUMBER
1170 , x_min_time IN OUT NOCOPY DATE
1171 , x_max_time IN OUT NOCOPY DATE
1172 )
1173 IS
1174 l_MinDateTimePref DATE;
1175 l_MaxDateTimePref DATE;
1176 l_MinDateTimeIn DATE;
1177 l_MaxDateTimeIn DATE;
1178
1179 l_NewHrs NUMBER;
1180 l_OldHrs NUMBER;
1181 l_EndDate DATE;
1182 l_Adjusted BOOLEAN;
1183 BEGIN
1184
1185
1186 /*****************************************************************************
1187 ** Determine the min date/time based on the preferences
1188 *****************************************************************************/
1189 --l_MinDateTimePref := x_min_time;
1190
1191 /*****************************************************************************
1192 ** Adjust the min value, but it has to be done in the given increments :-(
1193 *****************************************************************************/
1194 l_NewHrs := TO_NUMBER(TO_CHAR(p_StartDate,'hh24'));
1195 l_OldHrs := TO_NUMBER(TO_CHAR(x_min_time,'hh24'));
1196
1197
1198 --First Adjust the date part, if required.
1199
1200 IF (trunc(x_min_time) > trunc(p_StartDate))
1201 THEN
1202 x_min_time := trunc(p_StartDate) + 1;
1203
1204 END IF;
1205
1206
1207 --Now adjust the time part, if required.
1208 IF (l_OldHrs > l_NewHrs)
1209 THEN
1210 x_min_time := trunc(x_min_time) + l_NewHrs/24;
1211 END IF;
1212 /*
1213 WHILE (p_StartDate < l_MinDateTimePref)
1214 LOOP <<MINTIME>>
1215 l_MinDateTimePref := l_MinDateTimePref - (p_increment/24/60);
1216 END LOOP MINTIME;
1217
1218 x_min_time := l_MinDateTimePref;
1219 */
1220 /*****************************************************************************
1221 ** Determine the max date/time based on the preferences
1222 *****************************************************************************/
1223 --l_MaxDateTimePref := x_max_time;
1224
1225 /***************************************************************************
1226 ** Adjust the max value, but it has to be done in the given increments :-(
1227 ***************************************************************************/
1228 l_NewHrs := TO_NUMBER(TO_CHAR(p_EndDate,'hh24')) + 1;
1229 l_OldHrs := TO_NUMBER(TO_CHAR(x_max_time,'hh24'));
1230 l_Adjusted := false;
1231
1232 --First Adjust the date part, if required.
1233
1234 IF (trunc(x_max_time) < trunc(p_EndDate))
1235 THEN
1236 x_max_time := TRUNC(p_EndDate) - 1/(60*60*24);
1237 l_Adjusted := true;
1238 END IF;
1239
1240
1241
1242 --Now adjust the time part, if required.
1243 IF (NOT l_Adjusted)
1244 THEN
1245 --First reset the hours, if greater than 23 (should be 0-23)
1246 IF (l_NewHrs > 23)
1247 THEN
1248 l_NewHrs := 23;
1249 END IF;
1250 IF (l_OldHrs < l_NewHrs)
1251 THEN
1252 l_EndDate := x_max_time;
1253 x_max_time := trunc(x_max_time) + (l_NewHrs+1)/24;
1254 --If it goes to the next day then reset it to the previous day at 23:59
1255 IF (TRUNC(x_max_time) <> TRUNC(l_EndDate))
1256 THEN
1257 x_max_time := x_max_time - 1/(60*60*24);
1258 END IF;
1259 ELSIF (l_OldHrs = l_NewHrs)
1260 THEN
1261 --Compare the minutes part if hours is same
1262 IF (TO_NUMBER(TO_CHAR(p_EndDate,'mi')) > 0)
1263 THEN
1264 l_EndDate := x_max_time;
1265 x_max_time := trunc(x_max_time) + (l_NewHrs+1)/24;
1266 --If it goes to the next day then reset it to the previous day at 23:59
1267 IF (TRUNC(x_max_time) <> TRUNC(l_EndDate))
1268 THEN
1269 x_max_time := x_max_time - 1/(60*60*24);
1270 END IF;
1271 END IF;
1272 END IF;
1273 END IF;
1274
1275 /*
1276 WHILE (p_EndDate > l_MaxDateTimePref)
1277 LOOP <<MAXTIME>>
1278 l_MaxDateTimePref := l_MaxDateTimePref + (p_increment/24/60);
1279 END LOOP MAXTIME;
1280
1281 x_max_time := l_MaxDateTimePref;
1282 */
1283
1284 END AdjustMinMaxTime;
1285
1286 PROCEDURE SortTable
1287 /******************************************************************
1288 ** We need to sort the output table to make life easier on the
1289 ** java end.. This is a simple bi-directional bubble sort, which
1290 ** should do the trick.
1291 ******************************************************************/
1292 (p_CalendarItems IN OUT NOCOPY JTF_CAL_PVT.QueryOutTab
1293 )
1294 IS
1295 l_LastRecord BINARY_INTEGER;
1296
1297 PROCEDURE Swap
1298 /******************************************************************
1299 ** Swap the records
1300 ******************************************************************/
1301 (p_index IN NUMBER
1302 )
1303 IS
1304 l_record JTF_CAL_PVT.QueryOut;
1305 BEGIN
1306 l_record := p_CalendarItems(p_index);
1307 p_CalendarItems(p_index) := p_CalendarItems(p_index - 1);
1308 p_CalendarItems(p_index - 1) := l_record;
1309 END Swap;
1310
1311 BEGIN
1312 l_LastRecord := p_CalendarItems.LAST;
1313 IF (l_LastRecord is null)
1314 THEN
1315 RETURN;
1316 ELSE
1317 FOR l_high IN 1 .. l_LastRecord
1318 LOOP <<HIGH>>
1319 IF p_CalendarItems(l_high).StartDate < p_CalendarItems(l_high - 1).StartDate
1320 THEN
1321 Swap(l_high);
1322 FOR l_low IN REVERSE 1 .. (l_high - 1)
1323 LOOP <<LOW>>
1324 IF p_CalendarItems(l_low).StartDate < p_CalendarItems(l_low - 1).StartDate
1325 THEN
1326 Swap(l_low);
1327 ELSE
1328 EXIT;
1329 END IF;
1330 END LOOP LOW;
1331 END IF;
1332 END LOOP HIGH;
1333 END IF;
1334 EXCEPTION
1335 WHEN COLLECTION_IS_NULL
1336 THEN RETURN;
1337
1338 WHEN OTHERS
1339 THEN RAISE;
1340 END SortTable;
1341
1342 PROCEDURE AdjustForTimezone
1343 ( p_source_tz_id IN NUMBER
1344 , p_dest_tz_id IN NUMBER
1345 , p_source_day_time IN DATE
1346 , x_dest_day_time OUT NOCOPY DATE
1347 )
1348 IS
1349 l_return_status VARCHAR2(1);
1350 l_msg_count NUMBER;
1351 l_msg_data VARCHAR2(2000);
1352
1353 l_SourceTimezoneID NUMBER;
1354
1355 BEGIN
1356 IF (p_source_day_time IS NOT NULL)
1357 THEN
1358 /****************************************************************************
1359 ** NULL is the same in every timezone
1360 ****************************************************************************/
1361 IF (p_source_tz_id IS NULL)
1362 THEN
1363 /**************************************************************************
1364 ** If the timezone is not defined used the profile value
1365 **************************************************************************/
1366 --l_SourceTimezoneID := to_number(FND_PROFILE.Value('JTF_CAL_DEFAULT_TIMEZONE'));
1367 l_SourceTimezoneID := to_number(FND_PROFILE.Value('SERVER_TIMEZONE_ID'));
1368 --l_SourceTimezoneID := to_number(NVL(FND_PROFILE.Value('CLIENT_TIMEZONE_ID'),4));
1369 ELSE
1370 l_SourceTimezoneID := p_source_tz_id;
1371 END IF;
1372 /***********************************************************************
1373 ** Only adjust if the timezones are different
1374 ***********************************************************************/
1375 IF (l_SourceTimezoneID <> p_dest_tz_id)
1376 THEN
1377 /*********************************************************************
1378 ** Call the API to get the adjusted date (this API is slow..)
1379 *********************************************************************/
1380 HZ_TIMEZONE_PUB.Get_Time( p_api_version => 1.0
1381 , p_init_msg_list => FND_API.G_FALSE
1382 , p_source_tz_id => l_SourceTimezoneID
1383 , p_dest_tz_id => p_dest_tz_id
1384 , p_source_day_time => p_source_day_time
1385 , x_dest_day_time => x_dest_day_time
1386 , x_return_status => l_return_status
1387 , x_msg_count => l_msg_count
1388 , x_msg_data => l_msg_data
1389 );
1390 ELSE
1391 x_dest_day_time := p_source_day_time;
1392 END IF;
1393 ELSE
1394 x_dest_day_time := NULL;
1395 END IF;
1396 END AdjustForTimezone;
1397
1398 FUNCTION GetUserID
1399 /*****************************************************************************
1400 ** This function will pick up the user ID of the given Resource ID
1401 *****************************************************************************/
1402 (p_resource_id IN NUMBER
1403 )RETURN NUMBER
1404 IS
1405
1406 CURSOR c_GetInfo
1407 ( b_resource_id IN NUMBER
1408 )IS SELECT jrb.user_id
1409 FROM jtf_rs_resource_extns jrb
1410 WHERE jrb.resource_id = b_resource_id;
1411
1412 l_UserID NUMBER;
1413
1414 BEGIN
1415 IF (c_GetInfo%ISOPEN)
1416 THEN
1417 CLOSE c_GetInfo;
1418 END IF;
1419
1420 OPEN c_GetInfo(p_resource_id);
1421 FETCH c_GetInfo INTO l_UserID;
1422
1423 IF (c_GetInfo%FOUND)
1424 THEN
1425 IF (c_GetInfo%ISOPEN)
1426 THEN
1427 CLOSE c_GetInfo;
1428 END IF;
1429 RETURN l_UserID;
1430 ELSE
1431 IF (c_GetInfo%ISOPEN)
1432 THEN
1433 CLOSE c_GetInfo;
1434 END IF;
1435 RETURN NULL;
1436 END IF;
1437 EXCEPTION
1438 WHEN OTHERS
1439 THEN
1440 IF (c_GetInfo%ISOPEN)
1441 THEN
1442 CLOSE c_GetInfo;
1443 END IF;
1444 RETURN NULL;
1445 END GetUserID;
1446
1447 FUNCTION isValidTimezone (p_timezone_id IN NUMBER)
1448 RETURN BOOLEAN
1449 IS
1450 CURSOR c_timezone_id
1451 IS
1452 SELECT 1
1453 FROM hz_timezones
1454 WHERE timezone_id = p_timezone_id
1455 AND ROWNUM = 1;
1456 l_exist NUMBER;
1457
1458 BEGIN
1459 OPEN c_timezone_id;
1460 FETCH c_timezone_id INTO l_exist;
1461 IF c_timezone_id%NOTFOUND THEN
1462 CLOSE c_timezone_id;
1463 RETURN FALSE;
1464 ELSE
1465 CLOSE c_timezone_id;
1466 RETURN TRUE;
1467 END IF;
1468 END isValidTimezone;
1469
1470 FUNCTION isValidObjectCode (p_object_code IN VARCHAR2)
1471 RETURN BOOLEAN
1472 IS
1473 CURSOR c_object_code
1474 IS
1475 SELECT 1
1476 FROM jtf_objects_b
1477 WHERE object_code = p_object_code
1478 AND ROWNUM = 1;
1479 l_exist NUMBER;
1480
1481 BEGIN
1482 OPEN c_object_code;
1483 FETCH c_object_code INTO l_exist;
1484 IF c_object_code%NOTFOUND THEN
1485 CLOSE c_object_code;
1486 RETURN FALSE;
1487 ELSE
1488 CLOSE c_object_code;
1489 RETURN TRUE;
1490 END IF;
1491
1492 END;
1493
1494 END JTF_CAL_UTILITY_PVT;
[Home] [Help]