[Home] [Help]
PACKAGE BODY: APPS.PER_CALENDAR_UTIL
Source
1 PACKAGE BODY PER_CALENDAR_UTIL AS
2 /* $Header: pecalutl.pkb 120.0.12010000.4 2008/11/19 07:24:46 osamvats noship $ */
3
4 DATEICALFORMAT CONSTANT VARCHAR2(15) DEFAULT 'yyyyMMdd';
5 TIMEICALFORMAT CONSTANT VARCHAR2(15) DEFAULT 'HH24MIss';
6
7 -- CONVERTS DATE FROM OLD TIME ZONE TO GMT
8 FUNCTION CONVERT_TIMEZONE
9 (L_DATE IN DATE,
10 L_OLDTIMEZONE IN VARCHAR2 DEFAULT DBTIMEZONE)
11 RETURN DATE
12 IS
13 BEGIN
14 RETURN FND_TIMEZONE_PUB.adjust_datetime(L_DATE,L_OLDTIMEZONE,'GMT');
15
16 END CONVERT_TIMEZONE;
17
18 --REPLACING \ , ;
19 FUNCTION ESCAPE_CHARACTERS(IPSTRING IN VARCHAR2)
20
21 RETURN VARCHAR2
22 AS
23 TEMP VARCHAR2(2000):= IPSTRING;
24 BEGIN
25 TEMP := REPLACE(TEMP,'\','\\');
26 TEMP := REPLACE(TEMP,',','\,');
27 TEMP := REPLACE(TEMP,';','\;');
28 RETURN TEMP;
29
30 END ESCAPE_CHARACTERS;
31
32
33 PROCEDURE CALENDAR_GENERATE_ICAL_STRING
34 (DTSTART IN VARCHAR2
35 ,DTEND IN VARCHAR2
36 ,SUBJECT IN VARCHAR2
37 ,LOCATION IN VARCHAR2
38 ,DESCRIPTION IN VARCHAR2
39 ,DTSTAMP IN VARCHAR2
40 ,ACCESS IN VARCHAR2
41 ,UID IN VARCHAR2
42 ,METHOD IN VARCHAR2
43 ,MAIL_TO IN VARCHAR2
44 ,ICS OUT NOCOPY VARCHAR2
45 ,PRIMARY_KEY IN VARCHAR2 DEFAULT NULL
46 ) IS
47 NEWLINE VARCHAR2(5);
48 L_CALENDAR VARCHAR2(10000);
49 BEGIN
50
51 NEWLINE:=FND_GLOBAL.LOCAL_CHR(10);
52 L_CALENDAR := 'BEGIN:VCALENDAR' ||NEWLINE
53 || 'VERSION:2.0'|| NEWLINE
54 || 'PRODID:-//ORACLE//Ical Builder//EN' || NEWLINE
55 || 'METHOD:' || METHOD || NEWLINE
56 || 'BEGIN:VEVENT' || NEWLINE
57 || 'DTSTART' ||DTSTART || NEWLINE
58 || 'DTEND' || DTEND || NEWLINE;
59
60 IF PRIMARY_KEY IS NOT NULL THEN
61 L_CALENDAR := L_CALENDAR || 'UID:' || UID|| PRIMARY_KEY|| NEWLINE;
62 ELSE
63 L_CALENDAR := L_CALENDAR || 'UID:' || UID|| NEWLINE;
64 END IF;
65
66 L_CALENDAR := L_CALENDAR || 'DTSTAMP:' || DTSTAMP || NEWLINE;
67
68 IF DESCRIPTION IS NOT NULL THEN
69 L_CALENDAR := L_CALENDAR || 'DESCRIPTION:' || ESCAPE_CHARACTERS(DESCRIPTION)|| NEWLINE;
70 END IF;
71
72 IF LOCATION IS NOT NULL THEN
73 L_CALENDAR := L_CALENDAR || 'LOCATION:' || ESCAPE_CHARACTERS(LOCATION)|| NEWLINE;
74 END IF;
75
76 IF MAIL_TO IS NOT NULL THEN
77 L_CALENDAR := L_CALENDAR || 'ORGANIZER:MAILTO:' || (MAIL_TO)|| NEWLINE;
78 END IF;
79
80 IF SUBJECT IS NOT NULL THEN
81 L_CALENDAR := L_CALENDAR || 'SUMMARY:' || ESCAPE_CHARACTERS(SUBJECT)|| NEWLINE;
82 END IF;
83
84
85 L_CALENDAR := L_CALENDAR || 'CLASS:'||ACCESS || NEWLINE;
86
87
88 L_CALENDAR := L_CALENDAR || 'END:VEVENT' || NEWLINE || 'END:VCALENDAR';
89
90 ICS := L_CALENDAR;
91
92 END CALENDAR_GENERATE_ICAL_STRING;
93
94
95 PROCEDURE CALENDAR_GENERATE_ICAL
96 (DTSTARTDATE IN DATE
97 ,DTENDDATE IN DATE
98 ,DTSTARTTIME IN DATE DEFAULT NULL
99 ,DTENDTIME IN DATE DEFAULT NULL
100 ,SUBJECT IN VARCHAR2 DEFAULT NULL
101 ,LOCATION IN VARCHAR2 DEFAULT NULL
102 ,DESCRIPTION IN VARCHAR2 DEFAULT NULL
103 ,ACCESS IN VARCHAR2 DEFAULT 'PUBLIC'
104 ,TIMEZONE IN VARCHAR2 DEFAULT NULL
105 ,METHOD IN VARCHAR2 DEFAULT 'PUBLISH'
106 ,IGNORE_TIME_ZONE IN BOOLEAN DEFAULT FALSE
107 ,MAIL_TO IN VARCHAR2 DEFAULT NULL
108 ,ICAL OUT NOCOPY VARCHAR2
109 ,PRIMARY_KEY IN VARCHAR2 DEFAULT NULL
110 )
111 IS
112 STARTDATE VARCHAR2(30);
113 ENDDATE VARCHAR2(30);
114
115 IDTSTARTDATE DATE;
116 IDTENDDATE DATE;
117 IDTSTARTTIME DATE;
118 IDTENDTIME DATE;
119 TEMP VARCHAR2(30);
120 DEFAULT_TIME_ZONE VARCHAR(80);
121 DTSTAMP VARCHAR2(30);
122 UID VARCHAR2(300);
123 BEGIN
124 IDTSTARTDATE:= DTSTARTDATE;
125 IDTENDDATE:= DTENDDATE;
126 IDTSTARTTIME:= DTSTARTTIME;
127 IDTENDTIME:= DTENDTIME;
128
129
130 IF TIMEZONE IS NOT NULL THEN
131 DEFAULT_TIME_ZONE:=TIMEZONE;
132 ELSE
133 SELECT DBTIMEZONE INTO DEFAULT_TIME_ZONE FROM DUAL;
134 END IF;
135
136 IF NOT IGNORE_TIME_ZONE THEN
137 -- FOR START TIME
138 IF IDTSTARTTIME IS NOT NULL THEN
139 TEMP:=TO_CHAR(DTSTARTDATE,DATEICALFORMAT)||' '||TO_CHAR(DTSTARTTIME,TIMEICALFORMAT);
140 IDTSTARTDATE:=TO_DATE(TEMP,DATEICALFORMAT||' '||TIMEICALFORMAT);
141 IDTSTARTDATE:=CONVERT_TIMEZONE(IDTSTARTDATE,DEFAULT_TIME_ZONE);
142 IDTSTARTTIME:=IDTSTARTDATE;
143
144 END IF;
145
146 -- FOR END TIME
147 IF IDTENDTIME IS NOT NULL THEN
148 TEMP:=TO_CHAR(DTENDDATE,DATEICALFORMAT)||' '||TO_CHAR(DTENDTIME,TIMEICALFORMAT);
149 IDTENDDATE:=TO_DATE(TEMP,DATEICALFORMAT||' '||TIMEICALFORMAT);
150 IDTENDDATE:=CONVERT_TIMEZONE(IDTENDDATE,DEFAULT_TIME_ZONE);
151 IDTENDTIME:=IDTENDDATE;
152
153 END IF;
154
155
156
157 END IF;
158
159
160 IF IDTSTARTTIME IS NULL THEN
161 --for events with date only
162 STARTDATE:=';VALUE=DATE:' || to_char(IDTSTARTDATE,DATEICALFORMAT);
163 UID:='DTS' || to_char(IDTSTARTDATE,DATEICALFORMAT);
164
165 ELSE
166 STARTDATE:=':' ||to_char(IDTSTARTDATE,DATEICALFORMAT) ||'T' || to_char(IDTSTARTTIME,TIMEICALFORMAT) ;
167 UID:='DTS' ||to_char(IDTSTARTDATE,DATEICALFORMAT)||to_char(IDTSTARTTIME,TIMEICALFORMAT);
168 IF NOT IGNORE_TIME_ZONE THEN
169 STARTDATE:=STARTDATE ||'Z';
170 UID:=UID||'Z';
171 END IF;
172
173 END IF;
174
175 IF IDTENDTIME IS NULL THEN
176 --for events with date only. to be considered as day event.
177 -- so increment by one
178 IDTENDDATE:=IDTENDDATE+1;
179 ENDDATE:=';VALUE=DATE:' || to_char(IDTENDDATE,DATEICALFORMAT);
180 UID:=UID||'DTE'||to_char(IDTENDDATE,DATEICALFORMAT);
181 ELSE
182 ENDDATE:=':' || to_char(IDTENDDATE,DATEICALFORMAT) ||'T' ||to_char(IDTENDTIME,TIMEICALFORMAT);
183 UID:=UID||'DTE'||to_char(IDTENDDATE,DATEICALFORMAT)||to_char(IDTENDTIME,TIMEICALFORMAT);
184 IF NOT IGNORE_TIME_ZONE THEN
185 ENDDATE:=ENDDATE ||'Z';
186 UID:=UID||'Z';
187 END IF;
188
189
190 END IF;
191
192 DTSTAMP:=TO_CHAR(SYSDATE,DATEICALFORMAT) ||'T'|| TO_CHAR(SYSDATE,TIMEICALFORMAT) ||'Z';
193
194 --BUILING ICALENDAR STRING
195 PER_CALENDAR_UTIL.CALENDAR_GENERATE_ICAL_STRING
196 (DTSTART =>STARTDATE
197 ,DTEND =>ENDDATE
198 ,SUBJECT =>SUBJECT
199 ,LOCATION =>LOCATION
200 ,DESCRIPTION =>DESCRIPTION
201 ,DTSTAMP =>DTSTAMP
202 ,ACCESS =>ACCESS
203 ,UID =>UID
204 ,ICS =>ICAL
205 ,MAIL_TO =>MAIL_TO
206 ,METHOD =>METHOD
207 ,PRIMARY_KEY=>PRIMARY_KEY
208 );
209
210
211 END CALENDAR_GENERATE_ICAL;
212
213
214
215
216 PROCEDURE CALENDAR_GENERATE_ICAL
217 (DTSTARTDATE IN DATE
218 ,DTENDDATE IN DATE
219 ,DTSTARTTIME IN VARCHAR2
220 ,DTENDTIME IN VARCHAR2
221 ,DTTIMEFORMAT IN VARCHAR2 DEFAULT NULL
222 ,SUBJECT IN VARCHAR2 DEFAULT NULL
223 ,LOCATION IN VARCHAR2 DEFAULT NULL
224 ,DESCRIPTION IN VARCHAR2 DEFAULT NULL
225 ,ACCESS IN VARCHAR2 DEFAULT 'PUBLIC'
226 ,TIMEZONE IN VARCHAR2 DEFAULT NULL
227 ,METHOD IN VARCHAR2 DEFAULT 'PUBLISH'
228 ,IGNORE_TIME_ZONE IN BOOLEAN DEFAULT FALSE
229 ,MAIL_TO IN VARCHAR2 DEFAULT NULL
230 ,ICAL OUT NOCOPY VARCHAR2
231 ,PRIMARY_KEY IN VARCHAR2 DEFAULT NULL
232 )
233 IS
234 IDTSTARTDATE DATE;
235 IDTSTARTTIME DATE;
236 ISDTSTARTTIME VARCHAR2(30);
237 IDTENDDATE DATE;
238 IDTENDTIME DATE;
239 ISDTENDTIME VARCHAR2(30);
240 TEMP VARCHAR2(30);
241
242 BEGIN
243 IDTSTARTDATE:=DTSTARTDATE;
244 ISDTSTARTTIME:=DTSTARTTIME;
245 IDTENDDATE:=DTENDDATE;
246 ISDTENDTIME:=DTENDTIME;
247
248
249
250
251 IF DTTIMEFORMAT IS NULL THEN
252 -- NO TIME INFO CAN BE USED. SO CALLING DEFAULT ROUTINE WITHOUT TIME INFO
253 IDTSTARTTIME:=NULL;
254 IDTENDTIME:=NULL;
255 ELSE
256 -- TRYING TO EXTRACT TIME INFO
257 IF ISDTSTARTTIME IS NOT NULL THEN
258 TEMP:=TO_CHAR(IDTSTARTDATE,DATEICALFORMAT);
259 TEMP:=TEMP || ' ' || ISDTSTARTTIME;
260 IDTSTARTDATE:=TO_DATE(TEMP,DATEICALFORMAT||' '||DTTIMEFORMAT);
261 IDTSTARTTIME:=IDTSTARTDATE;
262 END IF;
263 IF ISDTENDTIME IS NOT NULL THEN
264 TEMP:=TO_CHAR(IDTENDDATE,DATEICALFORMAT);
265 TEMP:=TEMP || ' ' || ISDTENDTIME;
266 IDTENDDATE:=TO_DATE(TEMP,DATEICALFORMAT||' '||DTTIMEFORMAT);
267 IDTENDTIME:=IDTENDDATE;
268
269 END IF;
270 END IF;
271
272 --CALLING MASTER ROUNTINE
273 PER_CALENDAR_UTIL.CALENDAR_GENERATE_ICAL(
274 DTSTARTDATE => IDTSTARTDATE
275 ,DTENDDATE => IDTENDDATE
276 ,DTSTARTTIME => IDTSTARTTIME
277 ,DTENDTIME => IDTENDTIME
278 ,SUBJECT => SUBJECT
279 ,LOCATION => LOCATION
280 ,DESCRIPTION => DESCRIPTION
281 ,ACCESS => ACCESS
282 ,TIMEZONE => TIMEZONE
283 ,METHOD => METHOD
284 ,IGNORE_TIME_ZONE => IGNORE_TIME_ZONE
285 ,MAIL_TO => MAIL_TO
286 ,ICAL => ICAL
287 ,PRIMARY_KEY=>PRIMARY_KEY
288 );
289 END CALENDAR_GENERATE_ICAL;
290 END PER_CALENDAR_UTIL;