1 package body WF_MAIL_UTIL as
2 /* $Header: wfmlutb.pls 120.5 2006/04/17 07:06:06 sstomar ship $ */
3
4 -- EncodeBLOB
5 -- Receives a BLOB input and encodes it to Base64 CLOB
6 -- IN
7 -- BLOB data
8 -- OUT
9 -- CLOB data
10
11 procedure EncodeBLOB(pIDoc in blob,
12 pODoc in out nocopy clob)
13 is
14 rawData raw(32767);
15 chunksize integer;
16 amount binary_integer := 32767;
17 position integer := 1;
18 base64raw RAW(32767);
19 chunkcount binary_integer := 0;
20 cBuffer varchar2(32000);
21 begin
22 chunksize := 12288;
23 amount := dbms_lob.getLength(pIDoc);
24 if(chunksize < amount) then
25 chunkcount := round((amount / chunksize)+0.5);
26 else
27 chunkCount := 1;
28 end if;
29
30 for i in 1..chunkcount loop
31 dbms_lob.read(pIDoc, chunksize, position, rawData);
32 base64raw := utl_encode.base64_encode(rawData);
33 cBuffer := utl_raw.cast_to_varchar2(base64Raw);
34 dbms_lob.writeAppend(pODoc, length(cBuffer), cBuffer);
35 position := position + chunksize;
36 end loop;
37 dbms_lob.WriteAppend(pODoc, 1, wf_core.newline);
38
39 exception
40 when others then
41 wf_core.context('WF_MAIL_UTIL', 'EncodeBLOB');
42 raise;
43 end EncodeBLOB;
44
45 -- DecodeBLOB
46 -- Receives a CLOB input and decodes it from Base64 to BLOB
47 -- IN
48 -- CLOB data
49 -- OUT
50 -- BLOB data
51 procedure DecodeBLOB(pIDoc in clob,
52 pODoc in out nocopy blob)
53 is
54 rawData raw(32767);
55 chunksize integer;
56 amount binary_integer := 32767;
57 position number := 1;
58 base64raw RAW(32767);
59 chunkcount binary_integer := 0;
60 cBuffer varchar2(32000);
61 bufsize number;
62 begin
63 amount := dbms_lob.getLength(pIDoc);
64 chunksize := 16896; -- Do not change
65 if(chunksize < amount) then
66 chunkcount := round((amount / chunksize)+0.5);
67 else
68 chunkCount := 1;
69 end if;
70
71 dbms_lob.trim(pODoc, 0);
72 for i in 1..chunkcount loop
73 dbms_lob.read(pIDoc, chunksize, position, cBuffer);
74 base64raw := utl_raw.cast_to_raw(cBuffer);
75 rawData := utl_encode.base64_decode(base64Raw);
76 bufsize := utl_raw.length(rawData);
77 dbms_lob.writeAppend(pODoc, bufsize, rawData);
78 position := position + chunksize;
79 end loop;
80
81 exception
82 when others then
83 wf_core.context('WF_MAIL_UTIL','DecodeBLOB');
84 raise;
85 end DecodeBLOB;
86
87 -- getTimezone (PRIVATE)
88 -- Gets the server timezone message
89 -- IN
90 -- contentType - Document Type in varchar2
91 -- RETURN
92 -- timezone - Formatted timezone message in varchar2
93 function getTimezone(contentType in varchar2) return varchar2
94 is
95 timezone varchar2(240);
96
97 begin
98 if g_install='EMBEDDED' AND
99 FND_TIMEZONES.timezones_enabled = 'Y' then
100
101 -- bug 5043031: When Mailer sends notifications to users
102 -- with different lang preferences one after the other;
103 -- user some times gets time zone in different langauge rather than user's prefered language.
104 --
105 g_timezoneName := FND_TIMEZONES.GET_NAME(FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE);
106 g_gmt_offset := getGMTDeviation(g_timezoneName);
107
108 if length(g_gmt_offset) > 0 then
109 if contentType = g_ntfDocText then
110 wf_core.token('TIMEZONE', g_gmt_offset);
111 timezone := wf_core.Substitute('WFTKN','WFNTF_TIMEZONE');
112 else
113 wf_core.token('TIMEZONE', '<SPAN style="font-weight:bold">'||
114 g_gmt_offset||'</SPAN>');
115 timezone := wf_core.Substitute('WFTKN','WFNTF_TIMEZONE');
116 end if;
117 else
118 timezone := '';
119 end if;
120 else
121 timezone := '';
122 end if;
123
124 return timezone;
125
126 end getTimezone;
127
128 -- getGMTDeviation (PRIVATE)
129 -- Function to get the gmtDeviation in String time format, for example,
130 -- Pacific Time with 8 GMT offset would be displayed as '(GMT -8:00/-7:00)
131 -- Pacific Time' or '(GMT -8:00) Pacific Time' depending on whether the
132 -- day light savings is enabled or not.
133 -- IN
134 -- pName - Timezone name
135 -- RETURN
136 -- l_GMT_deviation - GMT deviation in varchar2 format
137 function getGMTDeviation(pName in varchar2) return varchar2
138 is
139 l_gmt_offset number;
140 l_name varchar2(80);
141 l_offset_str1 varchar2(100);
142 l_offset_str2 varchar2(100);
143 l_daylight_flag varchar2(1);
144 l_gmt_deviation varchar2(240);
145
146 begin
147
148 SELECT name, gmt_offset, daylight_savings_flag
149 INTO l_name, l_gmt_offset, l_daylight_flag
150 FROM FND_TIMEZONES_VL t
151 WHERE t.enabled_flag = 'Y'
152 AND t.name=pName;
153
154 l_offset_str1 := to_char(trunc(l_gmt_offset),'S09') || ':'
155 || to_char(abs(l_gmt_offset - trunc(l_gmt_offset))*60,'FM900');
156
157 if(l_daylight_flag = 'Y') then
158 l_gmt_offset := l_gmt_offset + 1;
159 l_offset_str2 := to_char(trunc(l_gmt_offset),'S09') || ':'
160 || to_char(abs(l_gmt_offset - trunc(l_gmt_offset))*60,'FM900');
161 l_gmt_deviation := '(GMT '
162 || trim(l_offset_str1) || '/'
163 || trim(l_offset_str2) || ') '
164 || l_name;
165 else
166 l_gmt_deviation := '(GMT '
167 || trim(l_offset_str1) || ') '
168 || l_name;
169 end if;
170
171 return l_gmt_deviation;
172 end getGMTDeviation;
173
174 -- StrParser
175 -- Parse a string and seperate the elements into a memeory table based on the
176 -- content of the seperators.
177 -- IN
178 -- str - The Varchar2 that is to be parsed
179 -- sep - The list of SINGLE character seprators that will
180 -- segment the str.
181 -- RETURN
182 -- parserStack_t a memory table of Varchar2
183 --
184 function strParser(str in varchar2, sep in varchar2) return parserStack_t
185 is
186 quot pls_integer;
187 i pls_integer;
188 c varchar2(1);
189 attr varchar2(2000);
190 defv varchar2(2000);
191 stack parserStack_t;
192 buf varchar2(2000);
193
194 begin
195 if str is not null or str <> '' then
196 quot := 1;
197 i := 1;
198 buf := '';
199 while i <= length(str) loop
200 c := substrb(str, i, 1);
201 if instrb(sep, c,1 ,1)>0 then
202 if buf is not null or buf <> '' then
203 -- Push the buffer to the stack and start again
204 stack(quot) := trim(buf);
205 quot := quot + 1;
206 buf := '';
207 end if;
208 elsif c = '\' then
209 -- Escape character. Consume this and the next character.
210 i := i + 1;
211 c := substrb(str, i, 1);
212 buf := buf ||c;
213 else
214 buf := buf || c;
215 end if;
216 i := i + 1;
217 end loop;
218 if buf is not null or buf <> '' then
219 stack(quot) := trim(buf);
220 end if;
221 end if;
222 return stack;
223 end strParser;
224
225 -- ParseContentType
226 -- Parses document type returned by the PLSQL/PLSQLCLOB/PLSQLBLOB document
227 -- APIs and returns the parameters
228 -- IN
229 -- pContentType - Document Type
230 -- OUT
231 -- pMimeType - Content Type of the document
232 -- pFileName - File Name
233 -- pExtn - File Extension
234 -- pEncoding - Content Encoding
235 procedure parseContentType(pContentType in varchar2,
236 pMimeType out nocopy varchar2,
237 pFileName out nocopy varchar2,
238 pExtn out nocopy varchar2,
239 pEncoding out nocopy varchar2)
240 is
241 i pls_integer;
242 l_content_type varchar2(255);
243 l_paramlist parserStack_t;
244 l_sublist parserStack_t;
245
246 begin
247 -- Derive the name for the attachment.
248 l_content_type := pContentType;
249 pExtn := '';
250 pFilename := '';
251 pMimeType := '';
252 pEncoding := '';
253 l_paramlist := strParser(l_content_type, ';');
254 if (l_paramlist is null) then
255 return;
256 end if;
257 pMimeType := l_paramlist(1);
258 for i in 1..l_paramlist.COUNT loop
259 l_sublist := strParser(l_paramlist(i),'/');
260 if l_sublist.COUNT = 2 then
261 pExtn := l_sublist(2);
262 end if;
263 l_sublist.DELETE;
264 l_sublist := strParser(l_paramList(i),'="');
265 for i in 1..l_sublist.COUNT loop
266 if lower(l_sublist(i)) = 'name' then
267 pFilename := l_sublist(i+1);
268 end if;
269 if lower(l_sublist(i)) = 'encoding' then
270 pEncoding := l_sublist(i+1);
271 end if;
272 end loop;
273 l_sublist.DELETE;
274 end loop;
275 if lower(pExtn) = 'tab-separated-values' then
276 pExtn := 'tsv';
277 elsif lower(pExtn) = 'comma-separated-values' then
278 pExtn := 'csv';
279 elsif lower(pExtn) = 'plain' then
280 pExtn := 'txt';
281 elsif lower(pExtn) like '%excel' then
282 pExtn := 'xls';
283 elsif lower(pExtn) = 'html' then
284 pExtn := 'htm';
285 elsif lower(pExtn) like '%msword' then
286 pExtn := 'doc';
287 end if;
288 end parseContentType;
289
290 BEGIN
291 -- GLOBAL Package level variables that contain static data.
292 g_install := wf_core.translate('WF_INSTALL');
293 g_timezoneName := '';
294 g_gmt_offset := '';
295 g_ntfDocText := wf_notification.doc_text;
296
297 end WF_MAIL_UTIL;
[Home] [Help]