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