1 package body WF_XML as
2 /* $Header: wfmxmlb.pls 120.31.12010000.10 2009/02/12 02:15:33 alepe ship $ */
3 --
4 -- Exceptions
5 --
6 dequeue_timeout exception;
7 pragma EXCEPTION_INIT(dequeue_timeout, -25228);
8
9 dequeue_disabled exception;
10 pragma EXCEPTION_INIT(dequeue_disabled, -25226);
11
12 dequeue_outofseq exception;
13 pragma EXCEPTION_INIT(dequeue_outofseq, -25237);
14
15 no_queue exception;
16 pragma EXCEPTION_INIT(no_queue, -24010);
17
18 -- g_fist_message
19 -- Flag to control the dequeuing of the SMTP queue
20 g_first_message boolean := TRUE;
21
22 TYPE parserStack_t IS TABLE OF
23 varchar2(2000) INDEX BY BINARY_INTEGER;
24
25 g_LOBTable wf_temp_lob.wf_temp_lob_table_type;
26
27 TYPE wf_resourceList_rec_t IS RECORD
28 (
29 contentId VARCHAR2(1000),
30 fileName VARCHAR2(1000),
31 contentType VARCHAR2(1000),
32 value VARCHAR2(4000)
33
34 );
35
36 TYPE resourceList_t IS TABLE OF
37 wf_resourceList_rec_t INDEX BY BINARY_INTEGER;
38
39 cursor g_urls(p_nid varchar2) is
40 select WMA.TYPE, WMA.DISPLAY_NAME, WNA.TEXT_VALUE, WNA.NAME
41 from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
42 WF_MESSAGE_ATTRIBUTES_VL WMA
43 where WNA.NOTIFICATION_ID = p_nid
44 and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
45 and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
46 and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
47 and WMA.TYPE = 'URL'
48 and WMA.ATTACH = 'N'
49 and WMA.NAME = WNA.NAME;
50
51 -- Set these constants as session level varaibles to minumise the
52 -- calls to wf_core.
53 g_newLine varchar2(1) := wf_core.newLine;
54 g_install varchar2(100) := wf_core.Translate('WF_INSTALL');
55 g_htmlmessage varchar2(200);
56 g_urlNotification varchar2(200);
57 g_urlListAttachment varchar2(200);
58 -- These are only used in legacy procedures.
59 g_webAgent varchar2(200) := wf_core.translate('WF_WEB_AGENT');
60 g_wfSchema varchar2(200) := wf_core.translate('WF_SCHEMA');
61 g_fndapi_misschr varchar2(1) := FND_API.G_MISS_CHAR;
62
63 g_ntfDocText varchar2(30) := wf_notification.doc_text;
64 g_ntfDocHtml varchar2(30) := wf_notification.doc_html;
65
66 -- <<sstomar> : nls changes
67 g_base_language v$nls_parameters.value%TYPE ;
68 g_base_territory v$nls_parameters.value%TYPE ;
69 g_base_codeset v$nls_parameters.value%TYPE ;
70
71 -- << sstomar>> : Initialization can be done by using
72 -- variables from wf_core.nls_date_format,
73 -- wf_core.nls_date_language etc. but since
74 -- when calling WF_NOTIFICATION_UTIL.set/getNLSContext to retrieve
75 -- defaul NLS Language etc. we can define vars. here also.
76 g_base_nlsDateFormat v$nls_parameters.value%TYPE := wf_core.nls_date_format;
77 g_base_nlsDateLanguage v$nls_parameters.value%TYPE := wf_core.nls_date_language;
78 g_base_nlsCalendar v$nls_parameters.value%TYPE := wf_core.nls_calendar;
79 g_base_nlsSort v$nls_parameters.value%TYPE := wf_core.nls_sort ;
80 g_base_nlsNumericCharacters v$nls_parameters.value%TYPE := wf_core.nls_numeric_characters;
81
82
83 -- Return TRUE if the URL points to a image file.
84 -- The URL is pretested to ensure that it does NOT contain
85 -- any URL parameters.
86 function isImageReference(url in varchar2, renderType in varchar2)
87 return boolean
88 is
89 extPos pls_integer;
90 extStr varchar2(1000);
91 params pls_integer;
92
93 l_renderType varchar2(10);
94
95 begin
96 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
97 wf_log_pkg.string(WF_LOG_PKG.level_statement,
98 'wf.plsql.WF_XML.isImageReference',
99 'BEGIN {'||url||'} {'||renderType||'}');
100 end if;
101 l_renderType := renderType;
102
103 params := instr(url, '?');
104 if (params = 0) then
105 extPos := instrb(url, '.', -1, 1) + 1;
106 extStr := lower(substrb(url, extPos));
107
108 if (( l_renderType is null or l_renderType = 'IMG:') and
109 extStr in ('gif','jpg','png','tif','bmp','jpeg')) then
110 return true;
111 else
112 l_renderType := 'LNK:';
113 end if;
114
115 if l_renderType = 'LNK:' then
116 return false;
117 end if;
118 else
119 return false;
120 end if;
121 end isImageReference;
122
123 -- GetRecipients
124 -- IN
125 -- Role
126 -- OUT
127 -- List of recipients with their details contained in a PL/SQL Table.
128 -- NOTE
129 -- A role will only be resolved to one level. If a role contains has a
130 -- role associated to it, then that's as far as we go.
131 procedure GetRecipients(p_role in varchar2,
132 p_recipient_list in out NOCOPY WF_DIRECTORY.wf_local_roles_tbl_type)
133 is
134
135 l_display_name wf_roles.display_name%TYPE;
136 l_description wf_roles.description%TYPE;
137 l_email_address wf_roles.email_address%TYPE;
138 l_notification_preference wf_roles.notification_preference%TYPE;
139 l_language wf_roles.language%TYPE;
140 l_territory wf_roles.territory%TYPE;
141
142 cursor urc1(role varchar2, colon binary_integer) is
143 select name
144 from wf_users
145 where (name, orig_system, orig_system_id) in
146 (select user_name, user_orig_system, user_orig_system_id
147 from wf_user_roles
148 where role_name = urc1.role
149 and role_orig_system = substr(urc1.role, 1, urc1.colon-1)
150 and role_orig_system_id = substr(urc1.role, urc1.colon+1)
151 and user_name <> role_name
152 and user_orig_system <> role_orig_system
153 and user_orig_system_id <> role_orig_system_id)
154 and notification_preference not in ('SUMMARY','QUERY')
155 order by notification_preference, language;
156
157
158 cursor urc2(role varchar2) is
159 select name
160 from wf_users
161 where (name, orig_system, orig_system_id) in
162 (select user_name, user_orig_system, user_orig_system_id
163 from wf_user_roles
164 where role_name = urc2.role
165 and user_name <> role_name)
166 and notification_preference not in ('SUMMARY','QUERY')
167 order by notification_preference, language;
168
169 i binary_integer := 0;
170 colon binary_integer;
171
172
173 begin
174
175 -- Get role details.
176 wf_directory.GetRoleInfo(p_role, l_display_name, l_email_address,
177 l_notification_preference, l_language,
178 l_territory);
179
180 -- If the email address is NULL, then look for the members attached to
181 -- the role. Only attach those roles that 'want' a notification.
182 if l_email_address is not NULL
183 and l_notification_preference not in ('SUMMARY','QUERY') then
184 i := p_recipient_list.COUNT + 1;
185 p_recipient_list(i).name := p_role;
186 p_recipient_list(i).display_name := l_display_name;
187 p_recipient_list(i).description := l_description;
188 p_recipient_list(i).notification_preference :=
189 l_notification_preference;
190 p_recipient_list(i).language := l_language;
191 p_recipient_list(i).territory := l_territory;
192 p_recipient_list(i).email_address := l_email_address;
193 else
194 -- The the ROLE has a blank email address, then we
195 -- are interested only in resolving it one level.
196 -- If this is a user with a blank email address, then no one
197 -- will get a notification.
198 colon := instr(p_role, ':');
199 if colon > 0 then
200 for urrec in urc1(p_role, colon) loop
201 wf_directory.GetRoleInfo(urrec.name, l_display_name,
202 l_email_address,
203 l_notification_preference, l_language,
204 l_territory);
205 i := p_recipient_list.COUNT + 1;
206 p_recipient_list(i).name := p_role;
207 p_recipient_list(i).display_name := l_display_name;
208 p_recipient_list(i).description := l_description;
209 p_recipient_list(i).notification_preference :=
210 l_notification_preference;
211 p_recipient_list(i).language := l_language;
212 p_recipient_list(i).territory := l_territory;
213 p_recipient_list(i).email_address := l_email_address;
214 end loop;
215 else
216 for urrec in urc2(p_role) loop
217 wf_directory.GetRoleInfo(urrec.name, l_display_name,
218 l_email_address,
219 l_notification_preference, l_language,
220 l_territory);
221 i := p_recipient_list.COUNT + 1;
222 p_recipient_list(i).name := p_role;
223 p_recipient_list(i).display_name := l_display_name;
224 p_recipient_list(i).description := l_description;
225 p_recipient_list(i).notification_preference :=
226 l_notification_preference;
227 p_recipient_list(i).language := l_language;
228 p_recipient_list(i).territory := l_territory;
229 p_recipient_list(i).email_address := l_email_address;
230 end loop;
231 end if;
232 end if;
233 exception
234 when others then
235 wf_core.context('Wf_XML','GetRecipients',p_role);
236 raise;
237 end GetRecipients;
238
239 -- EncodeEntityReference
240 -- IN
241 -- Data to be encoded
242 -- RETURN
243 -- Encoded data.
244 -- NOTE
245 -- This is needed to encode the HTML data before placing it into
246 -- the XML structure. If it is placed in neat, then the XML parser
247 -- will not be able to cope.
248 function EncodeEntityReference(p_str in varchar2) return varchar2
249 is
250 l_str varchar2(32000);
251 begin
252 l_str := p_str;
253 l_str := replace(l_str,'&','&'||'amp;');
254 l_str := replace(l_str,'<','&'||'lt;');
255 l_str := replace(l_str,'>','&'||'gt;');
256 l_str := replace(l_str,'"','&'||'quot;');
257 l_str := replace(l_str,'''','&'||'apos;');
258 return l_str;
259 end EncodeEntityReference;
260
261
262 -- DecodeEntityReference (PRIVATE)
263 -- IN
264 -- Data to be decoded
265 -- RETURN
266 -- Decoded data.
267 -- NOTE
268 -- This is needed to decode the HTML data after extracting it from
269 -- the XML structure.
270 function DecodeEntityReference(some_text in varchar2)
271 return varchar2 is
272 l_amp varchar2(1) := '&';
273 buf varchar2(32000);
274 begin
275 buf := some_text;
276 buf := replace(buf, l_amp||'#38;', l_amp);
277 buf := replace(buf, l_amp||'lt;', '<');
278 buf := replace(buf, l_amp||'#60;', '<');
279 buf := replace(buf, l_amp||'gt;', '>');
280 buf := replace(buf, l_amp||'#92;', '\');
281 buf := replace(buf, l_amp||'#39;', '''');
282 buf := replace(buf, l_amp||'apos;', '''');
283 buf := replace(buf, l_amp||'quot;', '"');
284 buf := replace(buf, l_amp||'amp;', l_amp);
285 return buf;
286 exception
287 when others then
288 wf_core.context('Wf_Notification', 'DecodeEntityReference');
289 raise;
290 end DecodeEntityReference;
291
292 -- EnqueueLOBMessage
293 -- IN
294 -- Queue Name
295 -- Priority of the message
296 -- Correlation for the message - the NID of the notification
297 -- for this implementation.
298 -- Message - XML encoded.
299 procedure EnqueueLOBMessage(p_queue in varchar2,
300 p_priority number,
301 p_correlation in varchar2,
302 p_message in CLOB) is
303
304 l_enqueue_options dbms_aq.enqueue_options_t;
305 l_message_properties dbms_aq.message_properties_t;
306 l_correlation varchar2(255) := NULL;
307 l_msgid raw(16);
308
309 l_queueu VARCHAR2(200);
310 l_queueName VARCHAR2(30);
311 l_queueTable VARCHAR2(200);
312 l_schemaName VARCHAR2(320);
313 l_msgLength NUMBER;
314 l_sqlbuf VARCHAR2(2000);
315
316 l_amount binary_integer;
317 l_pos pls_integer;
318
319 l_dequeue_options dbms_aq.dequeue_options_t;
320 begin
321
322
323 /** wf_message_payload_t is obsolete in 2.6.4 onwards **/
324 null;
325
326 exception
327 when others then
328 wf_core.context('WF_XML','EnqueueLOBMessage',p_queue,
329 to_char(p_priority),
330 p_correlation);
331 raise;
332 end EnqueueLOBMessage;
333
334
335 -- EnqueueMessage
336 -- IN
337 -- Queue Name
338 -- Priority of the message
339 -- Correlation for the message - the NID of the notification
340 -- for this implementation.
341 -- Message - XML encoded.
342 procedure EnqueueMessage(p_queue in varchar2,
343 p_priority number,
344 p_correlation in varchar2,
345 p_message in VARCHAR2) is
346
347 l_enqueue_options dbms_aq.enqueue_options_t;
348 l_message_properties dbms_aq.message_properties_t;
349 l_correlation varchar2(255) := NULL;
350 l_msgid raw(16);
351
352 -- l_msgLob CLOB;
353 l_msgLobIdx pls_integer;
354 l_queueu VARCHAR2(200);
355 l_queueName VARCHAR2(30);
356 l_queueTable VARCHAR2(200);
357 l_schemaName VARCHAR2(320);
358 l_msgLength NUMBER;
359 l_sqlbuf VARCHAR2(2000);
360
361 l_amount binary_integer;
362 l_pos pls_integer;
363
364 l_dequeue_options dbms_aq.dequeue_options_t;
365 begin
366
367 /** wf_message_payload_t is obsolete in 2.6.4 onwards **/
368 null;
369
370 exception
371 when others then
372 -- just in case, check and free it any way.
373 wf_temp_lob.ReleaseLob(g_LOBTable, l_msgLobIdx);
374 wf_core.context('WF_XML','EnqueueMessage',p_queue, to_char(p_priority),
375 p_correlation);
376 raise;
377 end EnqueueMessage;
378
379
380 -- NewLOBTag - Create a new TAG node and insert it into the
381 -- Document Tree
382 -- IN
383 -- document as a CLOB
384 -- Position to take the new Tag Node
385 -- New Tag to be created
386 -- Data to be added between the start and end TAGs
387 -- Attribute list to be included in the opening TAG
388 -- OUT
389 -- The document containing the new TAG.
390 function NewLOBTag (p_doc in out NOCOPY CLOB,
391 p_pos in integer,
392 p_tag in varchar2,
393 p_data in varchar2,
394 p_attribute_list IN OUT NOCOPY wf_xml_attr_table_type)
395 return integer
396 is
397
398 -- l_temp CLOB;
399 l_tempStr varchar2(32000);
400 l_tempIdx pls_integer;
401 l_node varchar2(32000);
402 l_start varchar2(32000);
403 l_end varchar2(250);
404 l_pos integer;
405 l_nodesize number;
406
407 l_size number;
408 l_amount number;
409
410 begin
411
412 -- Create an instance of the node
413 -- A Node is deemed to be <TAG>Data</TAG>
414 -- dbms_lob.createTemporary(l_temp, TRUE, dbms_lob.CALL);
415 l_tempIdx := -1;
416
417 l_start := '<' || upper(p_tag);
418
419 -- If there are any attributes to add to the tag, then
420 -- add them now, otherwise, close off the TAG.
421 if p_attribute_list.COUNT = 0 then
422 l_start := l_start || '>';
423 else
424 for i in 1..p_attribute_list.COUNT loop
425 l_start := l_start || ' ' ||
426 p_attribute_list(i).attribute || '="' ||
427 p_attribute_list(i).value || '"';
428 end loop;
429 l_start := l_start || '>';
430 end if;
431
432 -- Create the end TAG.
433 l_end := '</' || upper(p_tag) || '>'||g_newLine;
434
435
436 l_size := dbms_lob.getlength(p_doc);
437
438 -- Create the full node to be inserted.
439 l_node := l_start || p_data || l_end;
440 l_nodesize := length(l_node);
441
442 l_amount := 0;
443 if l_size > 1 and l_size <> p_pos then
444 -- Copy the tail end of the LOB to a holder.
445 l_amount := l_size - p_pos;
446 if l_amount < 32000 then
447 dbms_lob.read(lob_loc => p_doc,
448 amount => l_amount,
449 offset => p_pos + 1,
450 buffer => l_tempStr);
451 else
452 l_tempIdx := wf_temp_lob.GetLob(g_LOBTable);
453 dbms_lob.copy(dest_lob => g_LOBTable(l_tempIdx).temp_lob,
454 src_lob => p_doc,
455 amount => l_amount,
456 dest_offset => 1,
457 src_offset => p_pos +1);
458 end if;
459 end if;
460
461 -- Now insert the new node into the p_pos location
462 dbms_lob.Write(p_doc, l_nodesize, p_pos + 1 , l_node);
463
464 -- Append the saved portion of the LOB
465 -- If l_tempIdx is still -1, then no LOB was used or initialised
466 -- but for the lob, makesure that there is something in it to be
467 -- used (l_amount > 0).
468 if l_tempIdx = -1 and l_amount > 0 then
469 dbms_lob.write(lob_loc => p_doc,
470 amount => l_amount,
471 offset => p_pos + l_nodesize + 1,
472 buffer => l_tempStr);
473 elsif l_amount > 0 then
474 dbms_lob.copy(dest_lob => p_doc,
475 src_lob => g_LOBTable(l_tempIdx).temp_lob,
476 amount => l_amount ,
477 dest_offset => p_pos + l_nodesize + 1);
478 wf_temp_lob.ReleaseLob(g_LOBTable, l_tempIdx);
479 end if;
480
481 l_pos := (p_pos + l_nodesize) - length(l_end);
482 -- Free up the use of the temporary LOB
483 -- dbms_lob.FreeTemporary(l_temp);
484
485 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
486 wf_log_pkg.string(WF_LOG_PKG.level_statement,
487 'wf.plsql.WF_XML.newLOBTag',
488 'TAG: '||l_start||' POS: '||to_char(l_pos));
489 end if;
490
491 return l_pos;
492
493 exception
494 when others then
495 wf_temp_lob.ReleaseLob(g_LOBTable, l_tempIdx);
496 wf_core.context('WF_XML','NewLOBTag', p_tag);
497 raise;
498 end NewLOBTag;
499
500 -- NewLOBTag - Create a new TAG node and insert it into the
501 -- Document Tree
502 -- IN
503 -- document as a CLOB
504 -- Position to take the new Tag Node
505 -- New Tag to be created
506 -- Data to be added between the start and end TAGs
507 -- Attribute list to be included in the opening TAG
508 -- OUT
509 -- The document containing the new TAG.
510 function NewLOBTag (p_doc in out NOCOPY CLOB,
511 p_pos in integer,
512 p_tag in varchar2,
513 p_data in CLOB,
514 p_attribute_list IN OUT NOCOPY wf_xml_attr_table_type)
515 return integer
516 is
517
518 l_tempIdx pls_integer;
519 l_nodeIdx pls_integer;
520 l_tempStr varchar2(32000);
521 l_start varchar2(250);
522 l_end varchar2(250);
523 l_pos integer;
524 l_nodesize number;
525
526 l_size number;
527 l_dataSize number;
528 l_amount number;
529
530 begin
531
532 -- Create an instance of the node
533 -- A Node is deemed to be <TAG>Data</TAG>
534 l_tempIdx := -1;
535 l_nodeIdx := wf_temp_lob.getLob(g_LOBTable);
536
537 l_start := '<' || upper(p_tag);
538
539 -- If there are any attributes to add to the tag, then
540 -- add them now, otherwise, close off the TAG.
541 if p_attribute_list.COUNT = 0 then
542 l_start := l_start || '>';
543 else
544 for i in 1..p_attribute_list.COUNT loop
545 l_start := l_start || ' ' ||
546 p_attribute_list(i).attribute || '="' ||
547 p_attribute_list(i).value || '"';
548 end loop;
549 l_start := l_start || '>';
550 end if;
551
552 -- Create the end TAG.
553 l_end := '</' || upper(p_tag) || '>'||g_newLine;
554
555 l_size := dbms_lob.getlength(p_doc);
556 l_dataSize := dbms_lob.getlength(p_data);
557
558 -- Create the full node to be inserted.
559 dbms_lob.writeAppend(g_LOBTable(l_nodeIdx).temp_lob, length(l_start),
560 l_start);
561 l_nodesize := length(l_start); -- dbms_lob.getLength(g_LOBTable(l_nodeIdx).temp_lob);
562 dbms_lob.copy(dest_lob => g_LOBTable(l_nodeIdx).temp_lob,
563 src_lob => p_data,
564 amount => l_dataSize,
565 dest_offset => l_nodesize+1,
566 src_offset => 1);
567 dbms_lob.writeAppend(g_LOBTable(l_nodeIdx).temp_lob, length(l_end),
568 l_end);
569 l_nodesize := dbms_lob.getLength(g_LOBTable(l_nodeIdx).temp_lob);
570
571 l_amount := 0;
572 if l_size > 1 and l_size <> p_pos then
573 l_amount := l_size - p_pos;
574 -- Copy the tail end of the LOB to a holder.
575 if l_amount < 32000 then
576 dbms_lob.read(lob_loc => p_doc,
577 amount => l_amount,
578 offset => p_pos + 1,
579 buffer => l_tempStr);
580 else
581 l_tempIdx := wf_temp_lob.GetLob(g_LOBTable);
582 dbms_lob.copy(dest_lob => g_LOBTable(l_tempIdx).temp_lob,
583 src_lob => p_doc,
584 amount => l_amount,
585 dest_offset => 1,
586 src_offset => p_pos +1);
587 end if;
588 end if;
589
590 -- Now insert the new node into the p_pos location
591 dbms_lob.copy(dest_lob => p_doc,
592 src_lob => g_LOBTable(l_nodeIdx).temp_lob,
593 amount => l_nodesize,
594 dest_offset => p_pos + 1);
595
596 -- Append the saved portion of the LOB
597 if l_tempIdx = -1 and l_amount > 0 then
598 dbms_lob.write(lob_loc => p_doc,
599 amount => l_amount,
600 offset => p_pos + l_nodesize + 1,
601 buffer => l_tempStr);
602 elsif l_tempIdx > 0 then
603 if l_amount > 0 then
604 dbms_lob.copy(dest_lob => p_doc,
605 src_lob => g_LOBTable(l_tempIdx).temp_lob,
606 amount => l_amount,
607 dest_offset => p_pos + l_nodesize + 1);
608 end if;
609 -- Free up the use of the temporary LOBs
610 wf_temp_lob.releaseLOB(g_LOBTable, l_tempIdx);
611 end if;
612
613
614 l_pos := (p_pos + l_nodesize) - length(l_end);
615
616 -- Free up the use of the temporary LOBs
617 wf_temp_lob.releaseLOB(g_LOBTable, l_nodeIdx);
618
619 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
620 wf_log_pkg.string(WF_LOG_PKG.level_statement,
621 'wf.plsql.WF_XML.newLOBTag',
622 'TAG: '||l_start||' POS: '||to_char(l_pos));
623 end if;
624
625 return l_pos;
626
627 exception
628 when others then
629 wf_temp_lob.releaseLOB(g_LOBTable, l_tempIdx);
630 wf_temp_lob.releaseLOB(g_LOBTable, l_nodeIdx);
631 wf_core.context('WF_XML','NewLOBTag', p_tag);
632 raise;
633 end NewLOBTag;
634
635 -- NewTag - Create a new TAG node and insert it into the
636 -- Document Tree
637 -- IN
638 -- document as a CLOB
639 -- Position to take the new Tag Node
640 -- New Tag to be created
641 -- Data to be added between the start and end TAGs
642 -- Attribute list to be included in the opening TAG
643 -- OUT
644 -- The document containing the new TAG.
645 function NewTag (p_doc in out NOCOPY VARCHAR2,
646 p_pos in integer ,
647 p_tag in varchar2,
648 p_data in varchar2,
649 p_attribute_list IN OUT NOCOPY wf_xml_attr_table_type)
650 return integer
651 is
652
653 l_temp VARCHAR2(32000);
654 l_node varchar2(32000);
655 l_start varchar2(250);
656 l_end varchar2(250);
657 l_pos integer;
658 l_nodesize number;
659
660 l_size number;
661 l_amount number;
662
663 begin
664
665 -- Create an instance of the node
666 -- A Node is deemed to be <TAG>Data</TAG>
667
668 l_start := '<' || upper(p_tag);
669
670 -- If there are any attributes to add to the tag, then
671 -- add them now, otherwise, close off the TAG.
672 if p_attribute_list.COUNT = 0 then
673 l_start := l_start || '>';
674 else
675 for i in 1..p_attribute_list.COUNT loop
676 l_start := l_start || ' ' ||
677 p_attribute_list(i).attribute || '="' ||
678 p_attribute_list(i).value || '"';
679 end loop;
680 l_start := l_start || '>';
681 end if;
682
683 -- Create the end TAG.
684 l_end := '</' || upper(p_tag) || '>'||g_newLine;
685
686
687 l_size := length(p_doc);
688
689 -- Create the full node to be inserted.
690 l_node := l_start || p_data || l_end;
691 l_nodesize := length(l_node);
692
693 l_amount := 0;
694 if l_size > 1 and l_size <> p_pos then
695 -- Copy the tail end of the LOB to a holder.
696 l_amount := l_size - p_pos;
697 l_temp := substr(p_doc, p_pos +1, l_amount);
698 end if;
699
700 -- Now insert the new node into the p_pos location
701 p_doc := substr(p_doc, 1, p_pos)||l_node;
702
703 if Length(l_temp) > 1 then
704 -- Append the saved portion of the LOB
705 p_doc := p_doc||l_temp;
706 end if;
707
708 l_pos := (p_pos + l_nodesize) - length(l_end);
709
710 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
711 wf_log_pkg.string(WF_LOG_PKG.level_statement,
712 'wf.plsql.WF_XML.newTag',
713 'TAG: '||l_start||' POS: '||to_char(l_pos));
714 end if;
715 return l_pos;
716
717 exception
718 when others then
719 wf_core.context('WF_XML','NewTag', p_tag);
720 raise;
721 end NewTag;
722
723
724
725 -- SkipLOBTag - To move return a pointer past the nominated TAG
726 -- starting from a given position in the document.
727 -- IN
728 -- document
729 -- Position to take the new Tag Node
730 -- New Tag to be created
731 -- Data to be added
732 -- RETURN
733 -- New position past the </TAG>.
734 function SkipLOBTag (p_doc in out NOCOPY CLOB,
735 p_tag in varchar2,
736 p_offset in out NOCOPY integer,
737 p_occurance in out NOCOPY integer) return integer is
738
739 l_pos integer;
740 l_tag varchar2(250);
741
742 begin
743
744 l_tag := '</'||upper(p_tag)||'>';
745 l_pos := dbms_lob.instr(p_doc, l_tag, p_offset, p_occurance);
746
747 return l_pos + length(l_tag);
748
749 exception
750 when others then
751 wf_core.context('WF_XML','SkipLOBTag', p_tag, to_char(p_offset),
752 to_char(p_occurance));
753 raise;
754 end SkipLOBTag;
755
756 -- SkipTag - To move return a pointer past the nominated TAG
757 -- starting from a given position in the document.
758 -- IN
759 -- document
760 -- Position to take the new Tag Node
761 -- New Tag to be created
762 -- Data to be added
763 -- RETURN
764 -- New position past the </TAG>.
765 function SkipTag (p_doc in out NOCOPY VARCHAR2,
766 p_tag in varchar2,
767 p_offset in out NOCOPY integer,
768 p_occurance in out NOCOPY integer) return integer is
769
770 l_pos integer;
771 l_tag varchar2(250);
772
773 begin
774
775 l_tag := '</'||upper(p_tag)||'>';
776 l_pos := instr(p_doc, l_tag, p_offset, p_occurance);
777
778 return l_pos + length(l_tag);
779
780 exception
781 when others then
782 wf_core.context('WF_XML','SkipTag', p_tag, to_char(p_offset),
783 to_char(p_occurance));
784 raise;
785 end SkipTag;
786
787
788 -- GetTagValue - Obtain the value for a given TAG from within the
789 -- Document Tree
790 -- IN
791 -- document as a CLOB
792 -- TAG to find the value of
793 -- The position to start looking for the TAG from
794 -- OUT
795 -- Value of the TAG. ie the value between the start and end TAGs
796 -- The position in the CLOB after the find
797 -- The list of attributes associated with the TAG (Not implemented as yet)
798 procedure GetTagValue(p_doc in out NOCOPY CLOB,
799 p_tag in varchar2,
800 p_value out NOCOPY varchar2,
801 p_pos in out NOCOPY integer,
802 p_attrlist in out NOCOPY wf_xml_attr_table_type)
803 as
804
805 l_value varchar2(32000);
806 l_length integer;
807 l_startTag varchar2(255);
808 l_endTag varchar2(255);
809 l_startPos integer;
810 l_endPos integer;
811 l_pos integer;
812 l_occurance integer := 1;
813
814 begin
815
816 -- The idea is to look for the value of a tag from
817 -- a given point (p_pos)
818 l_pos := p_pos;
819 -- Set the opening TAG. Don't use the '>' as there may be
820 -- attributes set on the TAG.
821 l_startTag := '<'||upper(p_tag);
822 l_endTag := '</'||upper(p_tag)||'>';
823
824 l_startPos := dbms_lob.instr(p_doc, l_startTag, l_pos, l_occurance);
825 l_startPos := dbms_lob.instr(p_doc, '>', l_startPos, l_occurance) + 1;
826
827
828 l_endPos := dbms_lob.instr(p_doc, l_endTag, l_startpos, l_occurance) - 1;
829 l_length := l_endPos - l_startPos + 1;
830
831 dbms_lob.read(p_doc, l_length, l_startPos, l_value);
832
833 -- Reposition the position pointer to after the end
834 -- of the current TAG set.
835 p_pos := l_endPos + length(l_endTag) + 1;
836 p_value := l_value;
837 exception
838 when others then
839 wf_core.context('WF_XML','GetTagValue',p_tag, to_char(p_pos));
840 raise;
841 end GetTagValue;
842
843
844 -- AddElementAttribute - Add an Element Attribute Value pair to the attribute
845 -- list.
846 -- IN
847 -- Name of the attribute
848 -- Value for the attribute
849 -- The attribute list to add the name/value pair to.
850 procedure AddElementAttribute(p_attribute_name IN VARCHAR2,
851 p_attribute_value IN VARCHAR2,
852 p_attribute_list IN OUT NOCOPY wf_xml_attr_table_type)
853 is
854 l_index integer;
855 begin
856 l_index := p_attribute_list.COUNT + 1;
857 p_attribute_list(l_index).attribute := p_attribute_name;
858 p_attribute_list(l_index).value := p_attribute_value;
859 exception
860 when others then
861 wf_core.context('WF_XML','AddElementAttribute',p_attribute_name,
862 p_attribute_value);
863 raise;
864 end;
865
866
867 -- STRPARSER - Parse a string and seperate the elements
868 -- into a memeory table based on the content of the
869 -- seperators.
870 -- IN
871 -- str - The Varchar2 that is to be parsed
872 -- sep - The list of SINGLE character seprators that will
873 -- segment the str.
874 -- RETURN parserStack_t a memory table of Varchar2
875 --
876 function strParser(str in varchar2, sep in varchar2) return parserStack_t
877 is
878 quot pls_integer;
879 i pls_integer;
880 c varchar2(1);
881 attr varchar2(2000);
882 defv varchar2(2000);
883
884 stack parserStack_t;
885 buf varchar2(2000);
886
887 begin
888 if str is not null or str <> '' then
889 quot := 1;
890 i := 1;
891 buf := '';
892 while i <= length(str) loop
893 c := substrb(str, i, 1);
894 if instrb(sep, c,1 ,1)>0 then
895 if buf is not null or buf <> '' then
896 -- Push the buffer to the stack and start again
897 stack(quot) := trim(buf);
898 quot := quot + 1;
899 buf := '';
900 end if;
901 elsif c = '\' then
902 -- Escape character. Consume this and the next
903 -- character.
904 i := i + 1;
905 c := substrb(str, i, 1);
906 buf := buf ||c;
907 else
908 buf := buf || c;
909 end if;
910 i := i + 1;
911 end loop;
912 if buf is not null or buf <> '' then
913 stack(quot) := trim(buf);
914 end if;
915 end if;
916 return stack;
917 end strParser;
918
919 -- parseContentType
920 -- IN
921 -- pContentType The Content-Type string to be parsed
922 -- OUT
923 -- pMimeType The MIME type part of the Content-Type
924 -- pFileName The file name part of the Content-Type
925 -- pExtn The extention of the file name
926 -- pEncoding The content-encoding part of the Content-Type
927 --
928 -- NOTE: For bug 3735752, a copy of parseContentType was taken and
929 -- placed into WF_MAIL_UTIL in order to prevent a dependancy
930 -- on WF_XML for WFA_HTML. This copy remains in tact.
931 procedure parseContentType(pContentType in varchar2,
932 pMimeType out nocopy varchar2,
933 pFileName out nocopy varchar2,
934 pExtn out nocopy varchar2,
935 pEncoding out nocopy varchar2)
936 is
937 i pls_integer;
938 l_content_type varchar2(255);
939 l_paramlist parserStack_t;
940 l_sublist parserStack_t;
941
942 begin
943 -- Derrive the name for the attachment.
944 l_content_type := pContentType;
945 pExtn := '';
946 pFilename := '';
947 pMimeType := '';
948 pEncoding := '';
949 l_paramlist := strParser(l_content_type, ';');
950 pMimeType := l_paramlist(1);
951 for i in 1..l_paramlist.COUNT loop
952 l_sublist := strParser(l_paramlist(i),'/');
953 if l_sublist.COUNT = 2 then
954 pExtn := l_sublist(2);
955 end if;
956 l_sublist.DELETE;
957 l_sublist := strParser(l_paramList(i),'="');
958 for i in 1..l_sublist.COUNT loop
959 if lower(l_sublist(i)) = 'name' then
960 pFilename := l_sublist(i+1);
961 end if;
962 if lower(l_sublist(i)) = 'encoding' then
963 pEncoding := l_sublist(i+1);
964 end if;
965 end loop;
966 l_sublist.DELETE;
967 end loop;
968 if lower(pExtn) = 'tab-separated-values' then
969 pExtn := 'tsv';
970 elsif
971 lower(pExtn) = 'comma-separated-values' then
972 pExtn := 'csv';
973 elsif
974 lower(pExtn) = 'plain' then
975 pExtn := 'txt';
976 elsif
977 lower(pExtn) like '%excel' then
978 pExtn := 'xls';
979 elsif
980 lower(pExtn) = 'html' then
981 pExtn := 'htm';
982 elsif
983 lower(pExtn) like '%msword' then
984 pExtn := 'doc';
985 end if;
986
987 end parseContentType;
988
989 -- GetAttachment - Create an attachment tag for each of the URLs and
990 -- DOCUMENT attributes.
991 -- IN
992 -- Notification ID
993 -- Document handle
994 -- MIME Agent for the attachment
995 -- Position in the document where to place the attachment.
996 -- OUT
997 -- New location of the position
998 function GetAttachment(p_nid in number,
999 p_doc in out NOCOPY CLOB,
1000 p_agent in varchar2,
1001 p_disposition in varchar2,
1002 p_doc_type in varchar2,
1003 p_pos in out NOCOPY integer) return integer
1004 is
1005
1006 l_pos integer;
1007 l_occurance integer := 1;
1008 l_tmpcontent varchar2(32000);
1009 -- l_content CLOB;
1010 l_contentIdx pls_integer;
1011 l_blob BLOB;
1012 l_atthname varchar2(255);
1013 l_display_type varchar2(255) := p_doc_type;
1014 l_content_type varchar2(255);
1015 l_attrlist wf_xml_attr_table_type;
1016 l_cbuf varchar2(32000);
1017 l_doc_end integer;
1018 l_doc_length number;
1019 l_start VARCHAR2(10) := '<![CDATA[';
1020 l_end VARCHAR2(4) := ']]>';
1021 l_isURLAttrs boolean;
1022 l_aname varchar2(30);
1023
1024
1025 l_error_result varchar2 (2000);
1026 l_err_name varchar2(30);
1027 l_err_message varchar2(2000);
1028 l_err_stack varchar2(4000);
1029
1030 cursor c_attr(p_nid varchar2) is
1031 select WMA.TYPE, WMA.DISPLAY_NAME,
1032 decode(WMA.TYPE, 'URL', WF_NOTIFICATION.GetUrlText(WNA.TEXT_VALUE,
1033 p_nid), WNA.TEXT_VALUE) URL, WNA.NAME
1034 from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
1035 WF_MESSAGE_ATTRIBUTES_VL WMA
1036 where WNA.NOTIFICATION_ID = p_nid
1037 and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1038 and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
1039 and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
1040 and (WMA.TYPE = 'URL' or WMA.TYPE = 'DOCUMENT')
1041 and WMA.ATTACH = 'Y'
1042 and WMA.NAME = WNA.NAME;
1043
1044 l_extn varchar2(20);
1045 l_mimeType varchar2(255);
1046 l_encoding varchar2(255);
1047 l_filename varchar2(320);
1048 l_attr_url varchar2(32000);
1049
1050 begin
1051
1052 -- dbms_lob.createTemporary(l_content, TRUE, dbms_lob.CALL);
1053
1054 -- Allocate LOB from WF_TEMP LOB pool.
1055 l_contentIdx := wf_temp_lob.getLOB(g_LOBTable);
1056 dbms_lob.createTemporary(l_blob, TRUE, dbms_lob.CALL);
1057
1058 l_pos := p_pos;
1059 for l_crec in c_attr(p_nid) loop
1060 dbms_lob.trim(g_LOBTable(l_contentIdx).temp_lob, 0);
1061 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
1062
1063 wf_log_pkg.string(WF_LOG_PKG.level_statement,
1064 'wf.plsql.WF_XML.GetAttachment',
1065 'Document URL {'||l_crec.url||'}');
1066 end if;
1067
1068 if l_crec.type = 'URL' then
1069 l_isURLAttrs := true;
1070 else
1071 /*
1072 ** The mechanism to obtain PLSQL and PLSQLCLOB documents
1073 ** as attachments.
1074 */
1075 if upper(substr(l_crec.url,1, 6)) = 'PLSQL:' then
1076
1077 -- wf_mail.getDocContent(p_nid, l_crec.name, l_display_type,
1078 -- l_tmpcontent, l_error_result);
1079 -- bug 2879507. call wf_notification.GetAttrDoc2 to get the
1080 -- doc content as well as the document type
1081 wf_notification.GetAttrDoc2(p_nid, l_crec.name, l_display_type,
1082 l_tmpcontent, l_content_type);
1083 l_tmpContent := replace(l_tmpContent, g_fndapi_misschr);
1084 -- default to display type if no document type specified
1085 if (l_content_type is null) then
1086 l_content_type := l_display_type;
1087 end if;
1088 -- Derrive the name for the attachment.
1089 parseContentType(l_content_type, l_mimeType, l_filename,
1090 l_extn, l_encoding);
1091 if l_filename is null or l_filename = '' then
1092 l_filename := l_crec.display_name ||'.'||l_extn;
1093 end if;
1094
1095 AddElementAttribute('content-type',l_mimeType, l_attrlist);
1096 l_pos := NewLOBTag(p_doc, l_pos, 'BODYPART', '', l_attrlist);
1097 l_attrlist.DELETE;
1098 AddElementAttribute('content-type',l_mimeType, l_attrlist);
1099 AddElementAttribute('content-disposition',p_disposition,
1100 l_attrlist);
1101 AddElementAttribute('filename', l_filename, l_attrlist);
1102 AddElementAttribute('encoding', l_encoding, l_attrlist);
1103 dbms_lob.trim(g_LOBTable(l_contentIdx).temp_lob,0);
1104 dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob,
1105 length(l_start), l_start);
1106 dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob,
1107 length(l_tmpContent),
1108 l_tmpContent);
1109 dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob,
1110 length(l_end), l_end);
1111
1112 l_pos := NewLOBTag(p_doc, l_pos, 'MESSAGE',
1113 g_LOBTable(l_contentIdx).temp_lob,
1114 l_attrlist);
1115 l_pos := SkipLOBTag(p_doc, 'MESSAGE', l_pos, l_occurance);
1116 l_pos := SkipLOBTag(p_doc, 'BODYPART', l_pos, l_occurance);
1117 elsif upper(substr(l_crec.url,1, 10)) = 'PLSQLCLOB:' then
1118 /*
1119 ** For PLSQLCLOB documents.
1120 */
1121 dbms_lob.trim(g_LOBTable(l_contentIdx).temp_lob,0);
1122 l_content_type := '';
1123
1124 --
1125 -- Getting Content
1126 -- First we call the existing APIs to render the
1127 -- content and then we fetch the content into
1128 -- the new structure.
1129 -- Note: LOB allocated within wf_mail.getLOBDocContent API
1130 -- is released here after processing by calling
1131 -- WF_MAIL.CloseLob(l_display_type) api.
1132 wf_mail.getLOBDocContent(p_nid, l_crec.name, l_display_type,
1133 l_content_type, l_error_result);
1134
1135 if l_error_result is not null or l_error_result <> '' then
1136 -- Error in getting content
1137 wf_core.context('WF_XML', 'GetAttachment', to_char(p_nid),
1138 l_display_type);
1139 wf_core.raise('WFMLR_DOCUMENT_ERROR');
1140 end if;
1141
1142 -- default to display type is no document type is specified
1143 if (l_content_type is null) then
1144 l_content_type := l_display_type;
1145 end if;
1146 -- Derrive the name for the attachment.
1147 parseContentType(l_content_type, l_mimeType, l_filename,
1148 l_extn, l_encoding);
1149 if l_filename is null or l_filename = '' then
1150 l_filename := l_crec.display_name ||'.'||l_extn;
1151 end if;
1152
1153 if lower(l_mimeType) not like 'text/%' and
1154 (l_encoding is null or lower(l_encoding) <> 'base64') then
1155 -- Assume that there has been no encoding on a RAW
1156 -- type lob. Do not include it here. Defer attaching
1157 -- the content.
1158 --
1159 -- Build the attribute list for the attachment
1160 -- including the content-type, file name etc.
1161 --
1162
1163 -- First the BODYPART structure to take the MESSAGE
1164 AddElementAttribute('content-type',l_mimeType, l_attrlist);
1165 l_pos := NewLOBTag(p_doc, l_pos, 'BODYPART', '', l_attrlist);
1166 l_attrlist.DELETE;
1167 l_attr_url := Wf_Notification.GetText(l_crec.URL, p_nid, l_display_type);
1168 AddElementAttribute('src', l_attr_url, l_attrlist);
1169 AddElementAttribute('content-type',l_mimeType, l_attrlist);
1170 AddElementAttribute('content-disposition',p_disposition,
1171 l_attrlist);
1172 AddElementAttribute('filename', l_filename, l_attrlist);
1173
1174 l_pos := NewLOBTag(p_doc, l_pos, 'MESSAGE', '',
1175 l_attrlist);
1176 l_pos := SkipLOBTag(p_doc, 'MESSAGE', l_pos, l_occurance);
1177 l_pos := SkipLOBTag(p_doc, 'BODYPART', l_pos, l_occurance);
1178
1179 else
1180
1181 -- Fetch the content
1182 WF_MAIL.InitFetchLOB(l_display_type, l_doc_length);
1183 l_doc_end := 0;
1184 dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob,
1185 length(l_start), l_start);
1186 while l_doc_end = 0 loop
1187 WF_MAIL.FetchLobContent(l_tmpContent, l_display_type,
1188 l_doc_end);
1189 l_tmpContent := replace(l_tmpContent, g_fndapi_misschr);
1190 dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob,
1191 length(l_tmpContent), l_tmpContent);
1192 end loop;
1193
1194
1195 dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob,
1196 length(l_end), l_end);
1197 --
1198 -- Build the attribute list for the attachment
1199 -- including the content-type, file name etc.
1200 --
1201
1202 -- First the BODYPART structure to take the MESSAGE
1203 AddElementAttribute('content-type',l_mimeType, l_attrlist);
1204 l_pos := NewLOBTag(p_doc, l_pos, 'BODYPART', '', l_attrlist);
1205 l_attrlist.DELETE;
1206
1207 AddElementAttribute('content-type', l_mimeType, l_attrlist);
1208 AddElementAttribute('content-disposition',p_disposition,
1209 l_attrlist);
1210 AddElementAttribute('filename', l_filename, l_attrlist);
1211 AddElementAttribute('encoding', l_encoding, l_attrlist);
1212
1213 l_pos := NewLOBTag(p_doc, l_pos, 'MESSAGE',
1214 g_LOBTable(l_contentIdx).temp_lob,
1215 l_attrlist);
1216 l_pos := SkipLOBTag(p_doc, 'MESSAGE', l_pos, l_occurance);
1217 l_pos := SkipLOBTag(p_doc, 'BODYPART', l_pos, l_occurance);
1218 end if;
1219
1220 -- Release temp LOB allocated within wf_mail.getLOBDocContent
1221 -- i.e. wf_mail.g_html_messageIdx or wf_mail.g_text_messageIdx Locators
1222 WF_MAIL.CloseLob(l_display_type);
1223
1224 elsif upper(substr(l_crec.url,1, 10)) = 'PLSQLBLOB:' then
1225 /*
1226 ** For PLSQLBLOB documents.
1227 */
1228 dbms_lob.trim(l_blob,0);
1229
1230 --
1231 -- Getting Content
1232 -- First we call the existing APIs to render the
1233 -- content and then we fetch the content into
1234 -- the new structure.
1235 --
1236 begin
1237 Wf_Notification.GetAttrBLOB(p_nid, l_crec.name,
1238 l_display_type,
1239 l_blob, l_content_type,
1240 l_aname);
1241 exception
1242 when others then
1243 if (wf_log_pkg.level_error >=
1244 fnd_log.g_current_runtime_level) then
1245 wf_log_pkg.string(WF_LOG_PKG.level_error,
1246 'wf.plsql.WF_XML.GetAttachment',
1247 'Error when getting BLOB attachment -> '||sqlerrm);
1248 end if;
1249 wf_core.context('WF_XML', 'GetAttachment', to_char(p_nid),
1250 l_display_type);
1251 wf_core.raise('WFMLR_DOCUMENT_ERROR');
1252 end;
1253
1254 -- default to display type is no document type is specified
1255 if (l_content_type is null) then
1256 l_content_type := l_display_type;
1257 end if;
1258 -- Derrive the name for the attachment.
1259 parseContentType(l_content_type, l_mimeType, l_filename,
1260 l_extn, l_encoding);
1261 if l_filename is null or l_filename = '' then
1262 l_filename := l_crec.display_name ||'.'||l_extn;
1263 end if;
1264
1265 -- First the BODYPART structure to take the MESSAGE
1266 AddElementAttribute('content-type',l_mimeType, l_attrlist);
1267 l_pos := NewLOBTag(p_doc, l_pos, 'BODYPART', '', l_attrlist);
1268 l_attrlist.DELETE;
1269 l_attr_url := Wf_Notification.GetText(l_crec.URL, p_nid, l_display_type);
1270 AddElementAttribute('src', l_attr_url, l_attrlist);
1271 AddElementAttribute('content-type',l_mimeType, l_attrlist);
1272 AddElementAttribute('content-disposition',p_disposition,
1273 l_attrlist);
1274 AddElementAttribute('filename', l_filename, l_attrlist);
1275
1276 l_pos := NewLOBTag(p_doc, l_pos, 'MESSAGE', '',
1277 l_attrlist);
1278 l_pos := SkipLOBTag(p_doc, 'MESSAGE', l_pos, l_occurance);
1279 l_pos := SkipLOBTag(p_doc, 'BODYPART', l_pos, l_occurance);
1280
1281 end if;
1282 end if;
1283 l_attrlist.DELETE;
1284 end loop;
1285
1286 -- BUG 3285943 - If this is for a framework notification
1287 -- then we don't need the attached URLs.
1288 -- Bug 5456241 : Pick html / text msg body based on content-type.
1289 if (WF_NOTIFICATION.isFwkRegion(p_nid, l_display_type)='Y' and g_install='EMBEDDED') then
1290 l_isURLAttrs := FALSE;
1291 end if;
1292
1293 if l_isURLAttrs then
1294 dbms_lob.trim(g_LOBTable(l_contentIdx).temp_lob,0);
1295
1296 wf_mail.GetURLAttachment(p_nid, l_tmpContent, l_error_result);
1297 dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob,
1298 length(l_start), l_start);
1299 dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob,
1300 length(l_tmpContent), l_tmpContent);
1301 dbms_lob.writeAppend(g_LOBTable(l_contentIdx).temp_lob,
1302 length(l_end), l_end);
1303
1304
1305 if l_error_result is not null or l_error_result <> '' then
1306 wf_core.context('WF_XML', 'GetAttachments', to_char(p_nid),
1307 p_agent, to_char(p_pos));
1308 wf_core.token('SQLERR', l_error_result);
1309 wf_core.raise('WF_URLLIST_ERROR');
1310 end if;
1311
1312 l_fileName := g_urlListAttachment||'.html';
1313
1314 -- First the BODYPART structure to take the MESSAGE
1315 AddElementAttribute('content-type',l_display_type, l_attrlist);
1316 l_pos := NewLOBTag(p_doc, l_pos, 'BODYPART', '', l_attrlist);
1317 l_attrlist.DELETE;
1318
1319 AddElementAttribute('content-type',g_ntfDocHtml,
1320 l_attrlist);
1321 AddElementAttribute('content-disposition',p_disposition,
1322 l_attrlist);
1323 AddElementAttribute('filename', l_filename, l_attrlist);
1324
1325 l_pos := NewLOBTag(p_doc, l_pos, 'MESSAGE',
1326 g_LOBTable(l_contentIdx).temp_lob, l_attrlist);
1327 l_pos := SkipLOBTag(p_doc, 'MESSAGE', l_pos, l_occurance);
1328 l_pos := SkipLOBTag(p_doc, 'BODYPART', l_pos, l_occurance);
1329
1330 end if;
1331
1332 -- relase TEMP allocated LOB
1333 wf_temp_lob.releaseLob(g_LOBTable, l_contentIdx);
1334
1335 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
1336 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
1337 'wf.plsql.WF_XML.GetAttachment',
1338 'END');
1339 end if;
1340
1341 return l_pos;
1342 exception
1343 when others then
1344 WF_MAIL.CloseLob(l_display_type);
1345 wf_temp_lob.releaseLob(g_LOBTable, l_contentIdx);
1346 wf_core.context('WF_XML', 'GetAttachments', to_char(p_nid), p_agent,
1347 to_char(p_pos));
1348 l_err_message := sqlerrm;
1349 raise;
1350 end GetAttachment;
1351
1352
1353 -- GetAttributes - Create an attribute tag for each of the response
1354 -- required attributes.
1355 -- IN
1356 -- Notification ID
1357 -- Document handle
1358 -- MIME Agent for the attribute
1359 -- Location in the document to insert the new TAG(s)
1360 -- RETURN
1361 -- The new position in the docuemnt.
1362 function GetAttributes(p_nid in number,
1363 p_doc in out NOCOPY CLOB,
1364 p_agent in varchar2,
1365 p_pos in out NOCOPY integer) return integer
1366 is
1367
1368 l_pos integer;
1369 l_attrlist wf_xml_attr_table_type;
1370 l_occurance integer := 1;
1371 l_error_result varchar2 (2000);
1372 l_err_name varchar2(30);
1373 l_err_message varchar2(2000);
1374 l_err_stack varchar2(4000);
1375 l_value varchar2(2000);
1376
1377 cursor c1 is
1378 select WMA.NAME, WMA.DISPLAY_NAME, WMA.DESCRIPTION, WMA.TYPE, WMA.FORMAT,
1379 decode(WMA.TYPE,
1380 'VARCHAR2', decode(WMA.FORMAT,
1381 '', WNA.TEXT_VALUE,
1382 substr(WNA.TEXT_VALUE, 1, to_number(WMA.FORMAT))),
1383 'NUMBER', decode(WMA.FORMAT,
1384 '', to_char(WNA.NUMBER_VALUE),
1385 to_char(WNA.NUMBER_VALUE, WMA.FORMAT)),
1386 'DATE', decode(WMA.FORMAT,
1387 '', to_char(WNA.DATE_VALUE),
1388 to_char(WNA.DATE_VALUE, WMA.FORMAT)),
1389 'LOOKUP', WNA.TEXT_VALUE,
1390 WNA.TEXT_VALUE) VALUE
1391 from WF_NOTIFICATION_ATTRIBUTES WNA,
1392 WF_NOTIFICATIONS WN,
1393 WF_MESSAGE_ATTRIBUTES_VL WMA
1394 where WNA.NOTIFICATION_ID = p_nid
1395 and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1396 and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
1397 and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
1398 and WMA.NAME = WNA.NAME
1399 and WMA.SUBTYPE = 'RESPOND'
1400 and WMA.TYPE not in ('FORM', 'URL')
1401 order by WMA.SEQUENCE;
1402
1403 begin
1404
1405 l_pos := p_pos;
1406 for rec in c1 loop
1407 l_attrlist.DELETE;
1408 AddElementAttribute('name',rec.name, l_attrlist);
1409 AddElementAttribute('type',rec.type, l_attrlist);
1410 if rec.format is not NULL then
1411 AddElementAttribute('format',rec.format, l_attrlist);
1412 end if;
1413 l_pos := NewLOBTag(p_doc, l_pos, 'ATTRIBUTE','',l_attrlist);
1414 l_attrlist.DELETE;
1415 l_pos := NewLOBTag(p_doc, l_pos, 'NAME', rec.display_name, l_attrlist);
1416 l_pos := SkipLOBTag(p_doc, 'NAME', l_pos, l_occurance);
1417 l_value := EncodeEntityReference(rec.value);
1418 l_pos := NewLOBTag(p_doc, l_pos, 'VALUE', rec.value, l_attrlist);
1419
1420 l_pos := SkipLOBTag(p_doc, 'ATTRIBUTE', l_pos, l_occurance);
1421
1422 end loop;
1423 return l_pos;
1424 exception
1425 when others then
1426 wf_core.context('WF_XML', 'GetAttributes', to_char(p_nid));
1427 raise;
1428 end GetAttributes;
1429
1430 -- GetXMLMessage - Return a CLOB Document containing an XML encoded version of the
1431 -- notification. No recipients list will be populated. That
1432 -- will be the responsibility of the calling procedure.
1433 --
1434 -- IN
1435 -- notification id
1436 -- Protocol for the message
1437 -- List of recipients to recieve the notification
1438 -- mailer node name
1439 -- Web Agent for the HTML attachments
1440 -- Reply to address for the final notification
1441 -- Language for the notification
1442 -- Territory for the notification
1443 -- OUT
1444 -- Piority for the message
1445 -- A CLOB Containing the XML encoded message.
1446 procedure GetXMLMessage (p_nid in number,
1447 p_protocol in varchar2,
1448 p_recipient_list in WF_DIRECTORY.wf_local_roles_tbl_type,
1449 p_node in varchar2,
1450 p_agent in varchar2,
1451 p_replyto in varchar2,
1452 p_nlang in varchar2,
1453 p_nterr in varchar2,
1454 p_priority out NOCOPY number,
1455 p_message in out NOCOPY CLOB)
1456 is
1457 -- l_doc CLOB;
1458 l_docIdx pls_integer;
1459 l_doctype varchar(100);
1460 l_pos integer;
1461 l_occurance integer := 1;
1462 l_item_type wf_items.item_type%TYPE;
1463 l_item_key wf_items.item_key%TYPE;
1464 l_priority wf_notifications.priority%TYPE;
1465 l_access_key wf_notifications.access_key%TYPE;
1466
1467 l_response integer;
1468
1469 l_attrlist wf_xml_attr_table_type;
1470 l_receiverlist varchar2 (4000);
1471 l_status varchar2 (8);
1472 l_language varchar2 (30);
1473 l_territory varchar2 (30);
1474 l_installed_lang varchar2 (1);
1475 l_str varchar2 (250);
1476 l_subject varchar2 (2000);
1477 l_text_body varchar2 (32000);
1478 l_html_body varchar2 (32000);
1479 l_body_atth varchar2 (32000);
1480 l_error_result varchar2 (2000);
1481 l_err_name varchar2 (30);
1482 l_err_message varchar2 (2000);
1483 l_err_stack varchar2 (4000);
1484
1485 begin
1486
1487 -- Grab the details of the message to be enqueued using the
1488 -- previous interface of WF_MAIL.GetMessage.
1489 begin
1490
1491 select installed_flag
1492 into l_installed_lang
1493 from wf_languages
1494 where nls_language = p_nlang
1495 and nls_territory = p_nterr
1496 and installed_flag = 'Y';
1497
1498 l_language := ''''||p_nlang||'''';
1499 l_territory := ''''||p_nterr||'''';
1500 exception
1501 when others then
1502 l_language := 'AMERICAN';
1503 l_territory := 'AMERICA';
1504 end;
1505 dbms_session.set_nls('NLS_LANGUAGE' , l_language);
1506 dbms_session.set_nls('NLS_TERRITORY' , l_territory);
1507
1508 wf_mail.getmessage(p_nid, p_node, p_agent, p_replyto,
1509 l_subject, l_text_body, l_html_body, l_body_atth,
1510 l_error_result);
1511 -- Check for any problems
1512 if l_error_result is not NULL then
1513 wf_core.token('LANG', l_language);
1514 wf_core.token('TERR', l_territory);
1515 wf_core.token('ERRMSG', l_error_result);
1516 wf_core.raise('WFXMLERR');
1517 end if;
1518
1519
1520 -- Instantiate a handle to the new document.
1521 -- dbms_lob.createTemporary(l_doc, TRUE, dbms_lob.session);
1522 l_docIdx := wf_temp_lob.getLob(g_LOBTable);
1523
1524 -- Initialise the XML Document and then progressively walk
1525 -- through the elements. Populating them as we go.
1526 -- l_pos is crucial as it determines where the next nodes
1527 -- will be placed.
1528 l_str := '<?xml version="1.0"?>';
1529 l_pos := length(l_str);
1530
1531 dbms_lob.write(g_LOBTable(l_docIdx).temp_lob, l_pos, 1, l_str);
1532
1533 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos,
1534 'NOTIFICATION','',l_attrlist);
1535
1536
1537 AddElementAttribute('language',p_nlang, l_attrlist);
1538 AddElementAttribute('territory',p_nterr, l_attrlist);
1539 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'HEADER', '',
1540 l_attrlist);
1541 l_attrlist.DELETE;
1542
1543 -- Attach the NID
1544 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'NID',
1545 to_char(p_nid),l_attrlist);
1546 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'NID', l_pos, l_occurance);
1547
1548 begin
1549 select priority, access_key, status
1550 into l_priority, l_access_key, l_status
1551 from wf_notifications_view
1552 where notification_id = p_nid;
1553 exception
1554 when NO_DATA_FOUND then
1555 wf_core.raise('WFNTFGM_FAILED');
1556 end;
1557
1558 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'PRIORITY',
1559 to_char(l_priority), l_attrlist);
1560 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'PRIORITY', l_pos,
1561 l_occurance);
1562 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'ACCESSKEY',
1563 l_access_key, l_attrlist);
1564 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'ACCESSKEY', l_pos,
1565 l_occurance);
1566
1567 -- Register the receivers as a single string of email addresses
1568 l_receiverlist := NULL;
1569 for i in 1..p_recipient_list.COUNT loop
1570 l_receiverlist := l_receiverlist || p_recipient_list(i).NAME || ',';
1571 end loop;
1572
1573 l_receiverlist := substr(l_receiverlist,1,length(l_receiverlist)-1);
1574 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'RECEIVERLIST',
1575 l_receiverlist, l_attrlist);
1576 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'RECEIVERLIST',
1577 l_pos, l_occurance);
1578
1579 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'SENDER', '',
1580 l_attrlist);
1581 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'SENDER', l_pos,
1582 l_occurance);
1583
1584 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'STATUS',
1585 l_status, l_attrlist);
1586 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'STATUS', l_pos,
1587 l_occurance);
1588
1589 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'SUBJECT',
1590 l_subject, l_attrlist);
1591 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'HEADER', l_pos,
1592 l_occurance);
1593
1594 l_text_body := EncodeEntityReference(l_text_body);
1595 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'BODYTEXT',
1596 l_text_body, l_attrlist);
1597 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'BODYTEXT', l_pos, l_occurance);
1598
1599 AddElementAttribute('content-type','text/html', l_attrlist);
1600 AddElementAttribute('hmldesc','HTML', l_attrlist);
1601 AddElementAttribute('htmlagent',p_agent, l_attrlist);
1602
1603 l_html_body := EncodeEntityReference(l_html_body);
1604
1605 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos, 'BODYHTML',
1606 l_html_body, l_attrlist);
1607 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'BODYHTML', l_pos,
1608 l_occurance);
1609 l_attrlist.DELETE;
1610
1611 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos,
1612 'ATTACHMENTLIST', '', l_attrlist);
1613 -- Next will be to attach all URLs and DOCUMENT attributes
1614 -- as attachments.
1615 l_pos := GetAttachment(p_nid, g_LOBTable(l_docIdx).temp_lob, p_agent,
1616 NULL, l_doctype, l_pos);
1617 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob, 'ATTACHMENTLIST',
1618 l_pos, l_occurance);
1619
1620 -- Check to see if the notification is a reponse and attach
1621 -- the response attributes.
1622 l_response := 0;
1623 begin
1624 select 1 into l_response
1625 from dual
1626 where exists (select NULL
1627 from WF_MESSAGE_ATTRIBUTES MA,
1628 WF_NOTIFICATIONS N
1629 where N.NOTIFICATION_ID = p_nid
1630 and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
1631 and MA.MESSAGE_NAME = N.MESSAGE_NAME
1632 and MA.SUBTYPE = 'RESPOND');
1633
1634 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos,
1635 'ATTRIBUTELIST', '', l_attrlist);
1636 l_pos := GetAttributes(p_nid, g_LOBTable(l_docIdx).temp_lob, p_agent,
1637 l_pos);
1638
1639 exception
1640 when NO_DATA_FOUND then
1641 null;
1642 end;
1643
1644 p_priority := l_priority;
1645 dbms_lob.copy(p_message, g_LOBTable(l_docIdx).temp_lob,
1646 dbms_lob.getLength(g_LOBTable(l_docIdx).temp_lob), 1, 1);
1647 -- dbms_lob.freetemporary(l_doc).temp_lob);
1648 wf_temp_lob.releaseLob(g_LOBTable, l_docIdx);
1649
1650 exception
1651 when others then
1652 wf_temp_lob.releaseLob(g_LOBTable, l_docIdx);
1653 wf_core.context('WF_XML', 'GetXMLMessage', to_char(p_nid), p_protocol,
1654 p_node, p_nlang, p_nterr);
1655 raise;
1656 end getXMLMessage;
1657
1658 -- GetShortLOBXMLMessage -
1659 -- Return a CLOB Document containing an XML encoded versi on of the
1660 -- notification. No recipients list will be populated. That
1661 -- will be the responsibility of the calling procedure.
1662 --
1663 -- IN
1664 -- notification id
1665 -- Protocol for the message
1666 -- List of recipients to recieve the notification
1667 -- mailer node name
1668 -- Web Agent for the HTML attachments
1669 -- Reply to address for the final notification
1670 -- Language for the notification
1671 -- Territory for the notification
1672 -- OUT
1673 -- Piority for the message
1674 -- A CLOB Containing the XML encoded message.
1675 procedure GetShortLOBXMLMessage (p_nid in number,
1676 p_priority out NOCOPY number,
1677 p_message in out NOCOPY CLOB)
1678 is
1679 -- l_doc CLOB;
1680 l_docIdx pls_integer;
1681 l_pos integer;
1682 l_occurance integer := 1;
1683 l_priority wf_notifications.priority%TYPE;
1684 l_status wf_notifications.status%TYPE;
1685 l_recipient wf_notifications.recipient_role%TYPE;
1686
1687 l_attrlist wf_xml_attr_table_type;
1688 l_str varchar2 (250);
1689
1690 l_error_result varchar2 (2000);
1691 l_err_name varchar2 (30);
1692 l_err_message varchar2 (2000);
1693 l_err_stack varchar2 (4000);
1694 l_more_info_role varchar2(320);
1695
1696 begin
1697
1698 -- Grab the details of the message to be enqueued using the
1699 -- previous interface of WF_MAIL.GetMessage.
1700
1701 -- Instantiate a handle to the new document.
1702 -- dbms_lob.createTemporary(l_doc, TRUE, dbms_lob.session);
1703 l_docIdx := wf_temp_lob.getLOB(g_LOBTable);
1704
1705 -- Initialise the XML Document and then progressively walk
1706 -- through the elements. Populating them as we go.
1707 -- l_pos is crucial as it determines where the next nodes
1708 -- will be placed.
1709 l_str := '<?xml version="1.0"?>';
1710 l_pos := length(l_str);
1711
1712 dbms_lob.write(g_LOBTable(l_docIdx).temp_lob, l_pos, 1, l_str);
1713
1714 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos,
1715 'NOTIFICATION','',l_attrlist);
1716
1717 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos,
1718 'HEADER', '', l_attrlist);
1719 l_attrlist.DELETE;
1720
1721 -- Attach the NID
1722 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos,
1723 'NID', to_char(p_nid),l_attrlist);
1724 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob,
1725 'NID', l_pos, l_occurance);
1726
1727 begin
1728 select recipient_role, priority, status, more_info_role
1729 into l_recipient, l_priority, l_status, l_more_info_role
1730 from wf_notifications
1731 where notification_id = p_nid;
1732 exception
1733 when NO_DATA_FOUND then
1734 wf_core.raise('WFNTFGM_FAILED');
1735 end;
1736
1737 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos,
1738 'PRIORITY', to_char(l_priority), l_attrlist);
1739 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob,
1740 'PRIORITY', l_pos, l_occurance);
1741
1742 if (l_more_info_role is not null) then
1743 l_recipient := l_more_info_role;
1744 end if;
1745 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos,
1746 'RECIPIENT', l_recipient, l_attrlist);
1747 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob,
1748 'RECIPIENT', l_pos, l_occurance);
1749
1750 l_pos := NewLOBTag(g_LOBTable(l_docIdx).temp_lob, l_pos,
1751 'STATUS', l_status, l_attrlist);
1752 l_pos := SkipLOBTag(g_LOBTable(l_docIdx).temp_lob,
1753 'STATUS', l_pos, l_occurance);
1754
1755 p_priority := l_priority;
1756 dbms_lob.copy(p_message, g_LOBTable(l_docIdx).temp_lob,
1757 dbms_lob.getLength(g_LOBTable(l_docIdx).temp_lob), 1, 1);
1758 -- dbms_lob.freetemporary(g_LOBTable(l_docIdx).temp_lob);
1759 wf_temp_lob.releaseLob(g_LOBTable, l_docIdx);
1760
1761 exception
1762 when others then
1763 wf_temp_lob.releaseLob(g_LOBTable, l_docIdx);
1764 wf_core.context('WF_XML', 'GetShortLOBXMLMessage', to_char(p_nid));
1765 raise;
1766 end getShortLOBXMLMessage;
1767
1768 -- GetShortXMLMessage -
1769 -- Return a VARCHAR Document containing an XML encoded versi on of the
1770 -- notification. No recipients list will be populated. That
1771 -- will be the responsibility of the calling procedure.
1772 --
1773 -- IN
1774 -- notification id
1775 -- Protocol for the message
1776 -- List of recipients to recieve the notification
1777 -- mailer node name
1778 -- Web Agent for the HTML attachments
1779 -- Reply to address for the final notification
1780 -- Language for the notification
1781 -- Territory for the notification
1782 -- OUT
1783 -- Piority for the message
1784 -- A VARCHAR Containing the XML encoded message.
1785 procedure GetShortXMLMessage (p_nid in number,
1786 p_priority out NOCOPY number,
1787 p_message in out NOCOPY VARCHAR2)
1788 is
1789 l_pos integer;
1790 l_occurance integer := 1;
1791 l_priority wf_notifications.priority%TYPE;
1792 l_status wf_notifications.status%TYPE;
1793 l_recipient wf_notifications.recipient_role%TYPE;
1794
1795 l_attrlist wf_xml_attr_table_type;
1796 l_str varchar2 (250);
1797
1798 l_error_result varchar2 (2000);
1799 l_err_name varchar2 (30);
1800 l_err_message varchar2 (2000);
1801 l_err_stack varchar2 (4000);
1802 l_more_info_role varchar2(320);
1803
1804 begin
1805
1806 -- Grab the details of the message to be enqueued using the
1807 -- previous interface of WF_MAIL.GetMessage.
1808
1809 -- Instantiate a handle to the new document.
1810 p_message := '';
1811
1812 -- Initialise the XML Document and then progressively walk
1813 -- through the elements. Populating them as we go.
1814 -- l_pos is crucial as it determines where the next nodes
1815 -- will be placed.
1816 l_str := '<?xml version="1.0"?>';
1817 l_pos := length(l_str);
1818
1819 p_message := p_message||l_str;
1820
1821 l_pos := NewTag(p_message, l_pos, 'NOTIFICATION',NULL,l_attrlist);
1822
1823 l_pos := NewTag(p_message, l_pos, 'HEADER', NULL, l_attrlist);
1824 l_attrlist.DELETE;
1825
1826 -- Attach the NID
1827 l_pos := NewTag(p_message, l_pos, 'NID', to_char(p_nid),l_attrlist);
1828 l_pos := SkipTag(p_message, 'NID', l_pos, l_occurance);
1829
1830 begin
1831 select recipient_role, priority, status, more_info_role
1832 into l_recipient, l_priority, l_status, l_more_info_role
1833 from wf_notifications
1834 where notification_id = p_nid;
1835 exception
1836 when NO_DATA_FOUND then
1837 wf_core.raise('WFNTFGM_FAILED');
1838 end;
1839
1840 l_pos := NewTag(p_message, l_pos, 'PRIORITY', to_char(l_priority),
1841 l_attrlist);
1842 l_pos := SkipTag(p_message, 'PRIORITY', l_pos, l_occurance);
1843
1844 if (l_more_info_role is not null) then
1845 l_recipient := l_more_info_role;
1846 end if;
1847 l_pos := NewTag(p_message, l_pos, 'RECIPIENT', l_recipient, l_attrlist);
1848 l_pos := SkipTag(p_message, 'RECIPIENT', l_pos, l_occurance);
1849
1850 l_pos := NewTag(p_message, l_pos, 'STATUS', l_status, l_attrlist);
1851 l_pos := SkipTag(p_message, 'STATUS', l_pos, l_occurance);
1852
1853 p_priority := l_priority;
1854
1855 exception
1856 when others then
1857 wf_core.context('WF_XML', 'GetShortXMLMessage', to_char(p_nid));
1858 raise;
1859 end getShortXMLMessage;
1860
1861
1862 -- EnqueueFullNotification -
1863 -- To push a notification to the outbound notification queue.
1864 -- IN
1865 -- Notification ID
1866 procedure EnqueueFullNotification(p_nid in number) is
1867
1868 l_queue_name varchar2(255);
1869
1870 l_node varchar2(30);
1871 l_agent varchar(255) := g_webAgent;
1872 l_replyto varchar2(320);
1873
1874 l_nlang wf_languages.nls_language%TYPE;
1875 l_nterr wf_languages.nls_territory%TYPE;
1876 l_priority number;
1877 -- l_message CLOB;
1878 l_messageIdx pls_integer;
1879
1880 l_recipient_role varchar2(320);
1881 l_ntf_pref varchar2(30);
1882 l_language varchar2(30);
1883 l_recipient_list WF_DIRECTORY.wf_local_roles_tbl_type;
1884 l_recipient_lang WF_DIRECTORY.wf_local_roles_tbl_type;
1885 l_wf_schema varchar2(320) := g_wfSchema;
1886
1887 l_err_name varchar2(30);
1888 l_err_message varchar2(2000);
1889 l_err_stack varchar2(4000);
1890
1891 i binary_integer := 0;
1892 j binary_integer := 0;
1893
1894 begin
1895
1896 -- Obtain the name of the recipient role, but only if a
1897 -- notification is needed to go out. ie the mail status
1898 -- indicates that a notification should be sent.
1899 begin
1900 select recipient_role
1901 into l_recipient_role
1902 from wf_notifications
1903 where notification_id = p_nid
1904 and ((MAIL_STATUS = 'MAIL' and STATUS <> 'CLOSED')
1905 or (MAIL_STATUS = 'INVALID'));
1906 exception
1907 when NO_DATA_FOUND then
1908 l_recipient_role := NULL;
1909 when others then
1910 raise;
1911 end;
1912 if l_recipient_role is not null then
1913 -- Determine the total list of individual recipients
1914 -- ordered by Protocol and then Language.
1915 l_recipient_list.DELETE;
1916 GetRecipients(l_recipient_role, l_recipient_list);
1917
1918 end if;
1919
1920 if l_recipient_list.COUNT > 0 then
1921 -- A valid role has been found for a notification
1922 -- that is required to go out.
1923 -- Prepare a LOB to contain the payload message.
1924 -- dbms_lob.createTemporary(l_message, TRUE, dbms_lob.SESSION);
1925 l_messageIdx := wf_temp_lob.getLob(g_LOBTable);
1926
1927 wf_queue.set_queue_names;
1928 wf_queue.get_hash_queue_name (p_protocol => 'SMTP',
1929 p_inbound_outbound => 'OUTBOUND',
1930 p_queue_name => l_queue_name);
1931 l_language := l_recipient_list(1).language;
1932 l_ntf_pref := l_recipient_list(1).notification_preference;
1933 l_recipient_lang.DELETE;
1934 j := 1;
1935 i := 1;
1936
1937
1938 -- Walk through the recipient list. This will be sorted by Notification
1939 -- preference and language. We group the recipients this way to help
1940 -- minimise the number of Queue messages to the number of recipients.
1941 loop
1942 exit when i > l_recipient_list.COUNT;
1943 if l_ntf_pref <> l_recipient_list(i).notification_preference then
1944 wf_queue.get_hash_queue_name (p_protocol => 'SMTP',
1945 p_inbound_outbound => 'OUTBOUND',
1946 p_queue_name => l_queue_name);
1947
1948 end if;
1949 loop
1950 exit when (i > l_recipient_list.COUNT)
1951 or (l_ntf_pref <> l_recipient_list(i).notification_preference);
1952 j := 1;
1953 loop
1954 exit when (i > l_recipient_list.COUNT)
1955 or (l_ntf_pref <> l_recipient_list(i).notification_preference)
1956 or (l_language <> l_recipient_list(i).language);
1957
1958 l_recipient_lang(j) := l_recipient_list(i);
1959 i := i + 1;
1960 j := j + 1;
1961 end loop;
1962 -- Get an encoded payload.
1963 getXMLMessage(p_nid, l_ntf_pref,
1964 l_recipient_lang, l_node, l_agent,
1965 l_replyto,
1966 l_recipient_lang(1).language,
1967 l_recipient_lang(1).territory, l_priority, g_LOBTable(l_messageIdx).temp_lob);
1968
1969 -- Push the message to the queue.
1970 enqueueLOBMessage(p_queue => l_queue_name,
1971 p_priority => l_priority,
1972 p_correlation => wf_queue.account_name ||':'||
1973 to_char(p_nid),
1974 p_message => g_LOBTable(l_messageIdx).temp_lob);
1975
1976 j := 1;
1977 l_recipient_lang.DELETE;
1978 if i <= l_recipient_list.COUNT then
1979 l_ntf_pref := l_recipient_list(i).notification_preference;
1980 end if;
1981 end loop;
1982 end loop;
1983 -- dbms_lob.freetemporary(l_message);
1984 wf_temp_lob.releaseLob(g_LOBTable, l_messageIdx);
1985 end if;
1986
1987 exception
1988 when others then
1989 wf_temp_lob.releaseLob(g_LOBTable, l_messageIdx);
1990 wf_core.context('WF_XML', 'EnqueueFullNotification', to_char(p_nid));
1991 raise;
1992 end EnqueueFullNotification;
1993
1994
1995 -- EnqueueLOBNotification - To push a notification ID to the outbound
1996 -- notification queue.
1997 -- IN
1998 -- Notification ID
1999 procedure EnqueueLOBNotification(p_nid in number) is
2000
2001 l_queue_name varchar2(255);
2002 l_priority number;
2003
2004 l_recipient_role varchar2(320);
2005 -- l_message CLOB;
2006 l_messageIdx pls_integer;
2007
2008 l_err_name varchar2(30);
2009 l_err_message varchar2(2000);
2010 l_err_stack varchar2(4000);
2011
2012 begin
2013
2014 -- Obtain the name of the recipient role, but only if a
2015 -- notification is needed to go out. ie the mail status
2016 -- indicates that a notification should be sent.
2017 begin
2018 select recipient_role
2019 into l_recipient_role
2020 from wf_notifications
2021 where notification_id = p_nid
2022 and MAIL_STATUS in ('MAIL', 'INVALID');
2023 exception
2024 when NO_DATA_FOUND then
2025 l_recipient_role := NULL;
2026 when others then
2027 raise;
2028 end;
2029 if l_recipient_role is not null then
2030 -- A valid role has been found for a notification
2031 -- that is required to go out.
2032
2033 -- dbms_lob.createTemporary(l_message, TRUE, dbms_lob.CALL);
2034 l_messageIdx := wf_temp_lob.getLob(g_LOBTable);
2035 -- Get an encoded payload.
2036 getShortLOBXMLMessage(p_nid, l_priority,
2037 g_LOBTable(l_messageIdx).temp_lob);
2038
2039 wf_queue.set_queue_names;
2040 wf_queue.get_hash_queue_name (p_protocol => 'SMTP',
2041 p_inbound_outbound => 'OUTBOUND',
2042 p_queue_name => l_queue_name);
2043
2044 -- Push the message to the queue.
2045 enqueueLOBMessage(p_queue => l_queue_name,
2046 p_priority => l_priority,
2047 p_correlation => wf_queue.account_name ||':'||
2048 to_char(p_nid),
2049 p_message => g_LOBTable(l_messageIdx).temp_lob);
2050 end if;
2051 wf_temp_lob.releaseLob(g_LOBTable, l_messageIdx);
2052
2053 exception
2054 when others then
2055 wf_temp_lob.releaseLob(g_LOBTable, l_messageIdx);
2056 wf_core.context('WF_XML', 'EnqueueLOBNotification', to_char(p_nid));
2057 raise;
2058 end EnqueueLOBNotification;
2059
2060 -- EnqueueNotification - To push a notification ID to the outbound
2061 -- notification queue.
2062 -- IN
2063 -- Notification ID
2064 procedure EnqueueNotification(p_nid in number) is
2065
2066 l_queue_name varchar2(255);
2067 l_priority number;
2068
2069 l_recipient_role varchar2(320);
2070 l_message VARCHAR2(32000);
2071
2072 l_err_name varchar2(30);
2073 l_err_message varchar2(2000);
2074 l_err_stack varchar2(4000);
2075 l_more_info_role varchar2(320);
2076 begin
2077
2078 -- Obtain the name of the recipient role, but only if a
2079 -- notification is needed to go out. ie the mail status
2080 -- indicates that a notification should be sent.
2081 begin
2082 select recipient_role, more_info_role
2083 into l_recipient_role, l_more_info_role
2084 from wf_notifications
2085 where notification_id = p_nid
2086 and MAIL_STATUS in ('MAIL', 'INVALID')
2087 and STATUS <> 'CLOSED';
2088 exception
2089 when NO_DATA_FOUND then
2090 l_recipient_role := NULL;
2091 l_more_info_role := NULL;
2092 when others then
2093 raise;
2094 end;
2095 if (l_recipient_role is not null or l_more_info_role is not null) then
2096 -- A valid role has been found for a notification
2097 -- that is required to go out.
2098
2099 -- Get an encoded payload.
2100 getShortXMLMessage(p_nid, l_priority, l_message);
2101
2102 wf_queue.set_queue_names;
2103 wf_queue.get_hash_queue_name (p_protocol => 'SMTP',
2104 p_inbound_outbound => 'OUTBOUND',
2105 p_queue_name => l_queue_name);
2106
2107 -- Push the message to the queue.
2108 enqueueMessage(p_queue => l_queue_name,
2109 p_priority => l_priority,
2110 p_correlation => wf_queue.account_name ||':'||
2111 to_char(p_nid),
2112 p_message => l_message);
2113 end if;
2114
2115 exception
2116 when others then
2117 wf_core.context('WF_XML', 'EnqueueNotification', to_char(p_nid));
2118 raise;
2119 end EnqueueNotification;
2120
2121
2122 -- DequeueMessage - Remove a notification from the queue
2123 -- IN
2124 -- Queue name to operate on
2125 -- Correlation for the message - NID in this implementation
2126 -- OUT
2127 -- The message that is obtained from the queue.
2128 -- Timeout to signal whether the queue is empty.
2129 procedure DequeueMessage(p_queue_name in varchar2,
2130 p_correlation in varchar2,
2131 p_message in out NOCOPY CLOB,
2132 p_timeout out NOCOPY boolean)
2133 as
2134
2135 l_dequeue_options dbms_aq.dequeue_options_t;
2136 l_message_properties dbms_aq.message_properties_t;
2137 l_correlation varchar2(255) := NULL;
2138 l_message_handle RAW(16);
2139
2140 begin
2141
2142 /** wf_message_payload_t is obsolete in 2.6.4 onwards **/
2143 null;
2144
2145 exception
2146 when dequeue_timeout then
2147 p_timeout := TRUE;
2148 when others then
2149 Wf_Core.Context('WF_XML', 'DequeueMessage', p_queue_name,
2150 p_correlation);
2151 p_timeout := FALSE;
2152 raise;
2153 end DequeueMessage;
2154
2155
2156
2157 -- GetMessage - Get email message data
2158 -- IN
2159 -- Queue number to operate on
2160 -- OUT
2161 -- Notification ID
2162 -- Comma seperated list of the recipients of the notification
2163 -- Status of the notification - For the purpose of message templating
2164 -- Timout. Returns TRUE where the queue is empty.
2165 -- Error message
2166 procedure GetMessage(
2167 p_queue in number,
2168 p_nid out NOCOPY number,
2169 p_receiverlist out NOCOPY varchar2,
2170 p_status out NOCOPY varchar2,
2171 p_timeout out NOCOPY integer,
2172 p_error_result in out NOCOPY varchar2)
2173 is
2174
2175 l_nid number;
2176 l_queue_name varchar2(255);
2177 l_status varchar2(8);
2178 l_receiverlist varchar2(4000);
2179
2180 l_message CLOB;
2181 l_timeout BOOLEAN;
2182 l_pos integer;
2183 l_attrlist wf_xml_attr_table_type;
2184
2185 l_err_name varchar2(30);
2186 l_err_message varchar2(2000);
2187 l_err_stack varchar2(4000);
2188 no_program_unit exception;
2189 pragma exception_init(no_program_unit, -6508);
2190
2191 begin
2192
2193 l_queue_name := g_wfSchema||'.WF_SMTP_O_'||
2194 to_char(p_queue)||'_QUEUE';
2195
2196 -- Grab the next available message from the queue.
2197 DequeueMessage(p_queue_name => l_queue_name,
2198 p_correlation => NULL,
2199 p_message => l_message,
2200 p_timeout => l_timeout);
2201
2202 -- If the result is from the queue being empty then we want to
2203 -- inform the caller to maybe go and do something else a while.
2204 if NOT l_timeout then
2205 -- We now have a message as a CLOB. we need to parse it
2206 -- to reconstruct the DOM Document.
2207 -- new parser
2208
2209 -- Grab the components of the notification to send
2210 -- back to the caller
2211
2212 l_pos := 1;
2213
2214 GetTagValue(l_message, 'NID', l_nid, l_pos, l_attrlist);
2215 GetTagValue(l_message, 'RECEIVERLIST', l_receiverlist, l_pos,
2216 l_attrlist);
2217 GetTagValue(l_message, 'STATUS', l_status, l_pos, l_attrlist);
2218
2219 end if;
2220
2221 p_nid := to_number(l_nid);
2222 p_receiverlist := l_receiverlist;
2223 p_status := l_status;
2224
2225 if l_timeout then
2226 p_timeout := 1;
2227 else
2228 p_timeout := 0;
2229 end if;
2230
2231 exception
2232 when no_program_unit then
2233 wf_core.context('WF_XML', 'GetMessage', to_char(p_queue));
2234 raise;
2235
2236 when others then
2237 -- First look for a wf_core error.
2238 wf_core.get_error(l_err_name, l_err_message, l_err_stack);
2239
2240 -- If no wf_core error look for a sql error.
2241 if (l_err_name is null) then
2242 l_err_message := sqlerrm;
2243 end if;
2244
2245 p_error_result := l_err_message;
2246 wf_core.context('WF_XML', 'GetMessage', to_char(p_queue));
2247 raise;
2248
2249 end GetMessage;
2250
2251 -- GetShortMessage - Get email message data
2252 -- IN
2253 -- Queue number to operate on
2254 -- OUT
2255 -- Notification ID
2256 -- Comma seperated list of the recipients of the notification
2257 -- Status of the notification - For the purpose of message templating
2258 -- Timout. Returns TRUE where the queue is empty.
2259 -- Error message
2260 procedure GetShortMessage(
2261 p_queue in number,
2262 p_nid out NOCOPY number,
2263 p_recipient out NOCOPY varchar2,
2264 p_status out NOCOPY varchar2,
2265 p_timeout out NOCOPY integer,
2266 p_error_result in out NOCOPY varchar2)
2267 is
2268
2269 l_queue_name varchar2(200);
2270
2271 l_err_name varchar2(30);
2272 l_err_message varchar2(2000);
2273 l_err_stack varchar2(4000);
2274 no_program_unit exception;
2275 pragma exception_init(no_program_unit, -6508);
2276
2277 begin
2278
2279 l_queue_name := g_wfSchema||'.WF_SMTP_O_'||
2280 to_char(p_queue)||'_QUEUE';
2281
2282 GetQueueMessage(l_queue_name, p_nid, p_recipient, p_status,
2283 p_timeout, p_error_result);
2284 exception
2285 when no_program_unit then
2286 wf_core.context('WF_XML', 'GetShortMessage', to_char(p_queue));
2287 raise;
2288
2289 when others then
2290 -- First look for a wf_core error.
2291 wf_core.get_error(l_err_name, l_err_message, l_err_stack);
2292
2293 -- If no wf_core error look for a sql error.
2294 if (l_err_name is null) then
2295 l_err_message := sqlerrm;
2296 end if;
2297
2298 p_error_result := l_err_message;
2299 wf_core.context('WF_XML', 'GetShortMessage', to_char(p_queue));
2300 raise;
2301
2302 end GetShortMessage;
2303
2304
2305 -- GetExceptionMessage - Get email message data
2306 -- IN
2307 -- Queue number to operate on
2308 -- OUT
2309 -- Notification ID
2310 -- Comma seperated list of the recipients of the notification
2311 -- Status of the notification - For the purpose of message templating
2312 -- Timout. Returns TRUE where the queue is empty.
2313 -- Error message
2314 procedure GetExceptionMessage(
2315 p_queue in number,
2316 p_nid out NOCOPY number,
2317 p_recipient out NOCOPY varchar2,
2318 p_status out NOCOPY varchar2,
2319 p_timeout out NOCOPY boolean,
2320 p_error_result in out NOCOPY varchar2)
2321 is
2322 l_timeout integer;
2323 l_queue_name varchar2(200);
2324
2325 l_err_name varchar2(30);
2326 l_err_message varchar2(2000);
2327 l_err_stack varchar2(4000);
2328 no_program_unit exception;
2329 pragma exception_init(no_program_unit, -6508);
2330
2331 begin
2332
2333 l_queue_name := wf_queue.enable_exception_queue(
2334 g_wfSchema||
2335 '.WF_SMTP_O_'||to_char(p_queue)||'_QUEUE');
2336 if l_queue_name is not NULL then
2337 GetQueueMessage(l_queue_name, p_nid, p_recipient, p_status,
2338 l_timeout, p_error_result);
2339 -- GetQueueMessage returns timeout as an integer for the benefit
2340 if l_timeout = 0 then
2341 p_timeout := FALSE;
2342 else
2343 p_timeout := TRUE;
2344 end if;
2345 else
2346 p_timeout := TRUE;
2347 end if;
2348
2349 exception
2350 when no_program_unit then
2351 wf_core.context('WF_XML', 'GetExceptionMessage', to_char(p_queue));
2352 raise;
2353
2354 when others then
2355 -- First look for a wf_core error.
2356 wf_core.get_error(l_err_name, l_err_message, l_err_stack);
2357
2358 -- If no wf_core error look for a sql error.
2359 if (l_err_name is null) then
2360 l_err_message := sqlerrm;
2361 end if;
2362
2363 p_error_result := l_err_message;
2364 wf_core.context('WF_XML', 'GetExceptionMessage', to_char(p_queue));
2365 raise;
2366
2367 end GetExceptionMessage;
2368
2369 -- GetQueueMessage - Get email message data
2370 -- IN
2371 -- Queue name
2372 -- OUT
2373 -- Notification ID
2374 -- Comma seperated list of the recipients of the notification
2375 -- Status of the notification - For the purpose of message templating
2376 -- Timout. Returns TRUE where the queue is empty.
2377 -- Error message
2378 procedure GetQueueMessage(
2379 p_queuename in varchar2,
2380 p_nid out NOCOPY number,
2381 p_recipient out NOCOPY varchar2,
2382 p_status out NOCOPY varchar2,
2383 p_timeout out NOCOPY integer,
2384 p_error_result in out NOCOPY varchar2)
2385 is
2386
2387 l_message CLOB;
2388 l_nid number;
2389 l_queue_name varchar2(255);
2390 l_status varchar2(8) := NULL;
2391 l_currstatus varchar2(8);
2392 l_recipient WF_NOTIFICATIONS.RECIPIENT_ROLE%TYPE := NULL;
2393 l_attrlist wf_xml_attr_table_type;
2394 l_timeout BOOLEAN;
2395 l_pos integer;
2396 l_statusOK boolean;
2397
2398 l_err_name varchar2(30);
2399 l_err_message varchar2(2000);
2400 l_err_stack varchar2(4000);
2401 no_program_unit exception;
2402 pragma exception_init(no_program_unit, -6508);
2403
2404 begin
2405
2406 l_queue_name := p_queuename;
2407
2408 -- Grab the next available message from the queue.
2409 -- The lob located will contain a reference to a persistent
2410 -- LOB so there is not need to pre-create a temporary LOB.
2411 loop
2412 DequeueMessage(p_queue_name => l_queue_name,
2413 p_correlation => NULL,
2414 p_message => l_message,
2415 p_timeout => l_timeout);
2416
2417 -- If the result is from the queue being empty then we want to
2418 -- inform the caller to maybe go and do something else a while.
2419 if NOT l_timeout then
2420 -- We now have a message as a CLOB. we need to parse it
2421 -- to reconstruct the DOM Document.
2422 -- new parser
2423
2424 -- Grab the components of the notification to send
2425 -- back to the caller
2426
2427 l_pos := 1;
2428
2429 GetTagValue(l_message, 'NID', l_nid, l_pos, l_attrlist);
2430 GetTagValue(l_message, 'RECIPIENT', l_recipient, l_pos, l_attrlist);
2431 GetTagValue(l_message, 'STATUS', l_status, l_pos, l_attrlist);
2432
2433 -- Verify that the status of the notification is
2434 -- still OK.
2435 begin
2436 select status into l_currstatus
2437 from wf_notifications
2438 where notification_id = l_nid
2439 and status in ('OPEN','CANCELED', 'CLOSED')
2440 and mail_status in ('MAIL','INVALID','FAILED');
2441 l_statusOK := TRUE;
2442 exception
2443 when no_data_found then
2444 l_statusOK := FALSE;
2445 when others then raise;
2446 end;
2447 end if;
2448 exit when l_timeout or l_statusOK;
2449 end loop;
2450 p_nid := to_number(l_nid);
2451 p_recipient := l_recipient;
2452 p_status := l_status;
2453
2454 if l_timeout then
2455 p_timeout := 1;
2456 else
2457 p_timeout := 0;
2458 end if;
2459
2460 exception
2461 when no_program_unit then
2462 wf_core.context('WF_XML', 'GetQueueMessage', p_queuename);
2463 raise;
2464
2465 when others then
2466 -- First look for a wf_core error.
2467 wf_core.get_error(l_err_name, l_err_message, l_err_stack);
2468
2469 -- If no wf_core error look for a sql error.
2470 if (l_err_name is null) then
2471 l_err_message := sqlerrm;
2472 end if;
2473
2474 p_error_result := l_err_message;
2475 wf_core.context('WF_XML', 'GetQueueMessage', p_queuename);
2476 raise;
2477
2478 end GetQueueMessage;
2479
2480
2481 -- RemoveMessage
2482 -- To remove any messages associated with a particular Notification ID.
2483 -- Note that a notification can contain more than one message. Each of
2484 -- these messages will have a unique message handle and could be
2485 -- enqueued on more than one queue.
2486 -- IN
2487 -- Queue Name to remove the messages from
2488 -- Correlation or the Notification ID of the messages to remove.
2489 -- OUT
2490 -- Timeout. Returns TRUE if there was nothing on the Queue
2491 procedure RemoveMessage(p_queue_name in varchar2,
2492 p_correlation in varchar2,
2493 p_timeout out NOCOPY boolean)
2494 is
2495 l_dequeue_options dbms_aq.dequeue_options_t;
2496 l_message_properties dbms_aq.message_properties_t;
2497 l_correlation varchar2(255) := NULL;
2498 l_message_handle RAW(16);
2499
2500 begin
2501
2502 /** wf_message_payload_t is obsolete in 2.6.4 onwards **/
2503 null;
2504
2505 exception
2506 when others then
2507 Wf_Core.Context('WF_XML', 'RemoveMessage', p_queue_name, p_correlation);
2508 p_timeout := FALSE;
2509 raise;
2510 end RemoveMessage;
2511
2512
2513 -- RemoveNotification
2514 -- To remove all enqueues messages for a given notification.
2515 -- IN
2516 -- Notification ID of the message to locate and remove.
2517 -- NOTE
2518 -- This is a destructive procedure that's sole purpose is to purge the
2519 -- message from the queue. We only call this when we do not care for the
2520 -- content.
2521 procedure RemoveNotification (p_nid in number)
2522 is
2523 l_protocol varchar2(10);
2524 l_iobound varchar2(10);
2525 l_queue_count integer;
2526 l_queue_name varchar2(100);
2527 l_wf_schema varchar2(320);
2528 l_timeout boolean;
2529 begin
2530
2531 select protocol, inbound_outbound, queue_count
2532 into l_protocol, l_iobound, l_queue_count
2533 from wf_queues
2534 where protocol = 'SMTP'
2535 and INBOUND_OUTBOUND = 'OUTBOUND'
2536 and DISABLE_FLAG = 'N';
2537
2538 -- Walk though ALL queue names to hunt for the notifications
2539 wf_queue.set_queue_names;
2540 l_wf_schema := g_wfSchema;
2541 for i in 1..l_queue_count loop
2542 -- Build the queue name from the information we know.
2543 l_queue_name := l_wf_schema||'.WF_'||l_protocol||'_'||
2544 substr(l_iobound,1,1)||'_'||to_char(i)||'_QUEUE';
2545 l_timeout := FALSE;
2546 while not l_timeout loop
2547 -- Kill all traces of the message.
2548 RemoveMessage(l_queue_name, wf_queue.account_name||':'||
2549 to_char(p_nid), l_timeout);
2550 end loop;
2551 end loop;
2552 -- Remove the messages from the default Exception queues also
2553 for i in 1..l_queue_count loop
2554 -- Build the queue name from the information we know.
2555 l_queue_name := wf_queue.enable_exception_queue('WF_'||l_protocol||
2556 '_'||substr(l_iobound,1,1)||'_'||to_char(i)||'_QUEUE');
2557 if l_queue_name is not NULL then
2558 l_timeout := FALSE;
2559 while not l_timeout loop
2560 -- Kill all traces of the message.
2561 RemoveMessage(l_queue_name, wf_queue.account_name||':'||
2562 to_char(p_nid), l_timeout);
2563 end loop;
2564 end if;
2565 end loop;
2566
2567 exception
2568 when others then
2569 wf_core.context('WF_XML','RemoveNotification',to_char(p_nid));
2570 raise;
2571 end RemoveNotification;
2572
2573
2574 -- setFistMessage
2575 -- To set the global variable g_first_message for the deqeuing
2576 -- of the SMTP queue.
2577 -- IN
2578 -- 'Y' to set the flag to TRUE
2579 procedure setFirstMessage(p_first_message IN varchar2)
2580 is
2581 begin
2582 if (upper(substrb(p_first_message, 1, 1)) in ('Y','T')) then
2583 g_first_message := TRUE;
2584 else
2585 g_first_message := FALSE;
2586 end if;
2587 end;
2588
2589 --
2590 -- getBodyPart
2591 --
2592 procedure getBodyPart(p_doc in out NOCOPY CLOB,
2593 p_nid in number,
2594 p_doctype in varchar2)
2595 is
2596 l_message varchar2(32000);
2597 l_doc_length number := 0;
2598 l_doc_end integer := 0;
2599 l_start VARCHAR2(10) := '<![CDATA[';
2600 l_end VARCHAR2(4) := ']]>';
2601 begin
2602
2603 WF_MAIL.InitFetchLOB(p_doctype, l_doc_length);
2604 l_doc_end := 0;
2605 dbms_lob.writeAppend(p_doc, length(l_start), l_start);
2606 while l_doc_end = 0 loop
2607 WF_MAIL.FetchLobContent(l_message, p_doctype, l_doc_end);
2608 l_message := replace(l_message, g_fndapi_misschr);
2609 dbms_lob.writeAppend(p_doc, length(l_message), l_message);
2610 end loop;
2611 dbms_lob.writeAppend(p_doc, length(l_end), l_end);
2612 WF_MAIL.CloseLob(p_doctype);
2613
2614 exception
2615 when others then
2616 wf_core.context('WF_XML','getBodyPart',to_char(p_nid),p_doctype);
2617 raise;
2618 end getBodyPart;
2619
2620
2621 procedure GetNLS(base_lang out NOCOPY varchar2, base_territory OUT NOCOPY VARCHAR2,
2622 base_codeset OUT NOCOPY VARCHAR2)
2623 is
2624 nls_base varchar2(100);
2625 underscore_pos integer;
2626 dot_pos integer;
2627
2628 begin
2629 select userenv('LANGUAGE')
2630 into nls_base
2631 from sys.dual;
2632
2633 underscore_pos := instr(nls_base, '_');
2634 dot_pos := instr(nls_base, '.');
2635
2636 base_lang := substr(nls_base, 1, underscore_pos -1);
2637 base_territory := substr(nls_base, underscore_pos +1,
2638 (dot_pos - underscore_pos)-1);
2639 base_codeset := substr(nls_base, dot_pos + 1);
2640
2641
2642 exception
2643 when others then
2644 wf_core.context('WF_XML','GetNLS');
2645 raise;
2646 end getNLS;
2647
2648
2649 -- SetNLS
2650 -- To set the NLS lang and territory of the current session
2651 -- IN
2652 -- language - a varchar2 of the language code
2653 -- territory - a varchar2 of the territory code.
2654 procedure SetNLS(language in VARCHAR2, territory in VARCHAR2)
2655 is
2656 l_language varchar2(30);
2657 l_territory varchar2(30);
2658 l_installed_flag varchar2(1);
2659 begin
2660 begin
2661 -- If nothing is passed in, force it to AMERICAN_AMERICA
2662 if language is null or language = '' then
2663 l_language := 'AMERICAN';
2664 end if;
2665 if territory is null or territory = '' then
2666 l_territory := 'AMERICA';
2667 end if;
2668
2669 select installed_flag
2670 into l_installed_flag
2671 from wf_languages
2672 where nls_language = language
2673 and installed_flag = 'Y';
2674
2675 l_language := ''''||language||'''';
2676 l_territory := ''''||territory||'''';
2677 exception
2678 when others then
2679 l_language := 'AMERICAN';
2680 l_territory := 'AMERICA';
2681 end;
2682
2683 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
2684 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
2685 'wf.plsql.WF_XML.SetNLS',
2686 'Setting Language {'||l_language||'} {'||
2687 l_territory||'} - Passed in {'||language||
2688 '} {'||territory||'}');
2689 end if;
2690
2691 dbms_session.set_nls('NLS_LANGUAGE' , l_language);
2692 dbms_session.set_nls('NLS_TERRITORY' , l_territory);
2693 exception
2694 when others then
2695 wf_core.context('WF_XML','SetNLS', language, territory);
2696 raise;
2697 end setNLS;
2698
2699 -- Parse the p_doc for the URL attributes and edit
2700 -- their src= reference in preperation for email.
2701 function ParseForInlineImages(msgBody IN OUT NOCOPY CLOB,
2702 nid IN number,
2703 resourceList IN OUT NOCOPY resourceList_t)
2704 return boolean
2705 is
2706
2707 imgStrPos number;
2708 value varchar2(32000);
2709 newValue varchar2(32000);
2710 origValue varchar2(4000);
2711 contentId varchar2(1000);
2712
2713 tmpIdx pls_integer;
2714 nameStart pls_integer;
2715 nameEnd pls_integer;
2716 amount number;
2717 tmpLength number;
2718
2719 imagesFound boolean := false;
2720
2721 renderType varchar2(10);
2722 resourceIdx pls_integer;
2723 fileName varchar2(1000);
2724 contentType varchar2(200);
2725 extPos pls_integer;
2726
2727 begin
2728
2729 resourceIdx := 1;
2730 resourceList.DELETE;
2731
2732 imagesFound := false;
2733
2734 tmpIdx := wf_temp_lob.getLob(g_LOBTable);
2735 for url in g_urls(nid) loop
2736 value := url.text_value;
2737 value := wf_notification.SetFrameworkAgent(value);
2738
2739 -- Check if there is a render type specification on the URL
2740 -- Remove it and set the renderType appropriately.
2741 renderType := substr(value, 1, 4);
2742 if(renderType in ('IMG:','LNK:')) then
2743 value := substr(value, 5);
2744 else
2745 renderType := null;
2746 end if;
2747
2748 if isImageReference(value, renderType) then
2749 origValue := value;
2750 value := '<IMG SRC="'||value||
2751 '" alt="'|| url.display_name||'"></IMG>';
2752 imgStrPos := dbms_lob.instr(msgBody, value, 1, 1);
2753 if imgStrPos > 0 then
2754 dbms_lob.trim(g_LOBTable(tmpIdx).temp_lob, 0);
2755 dbms_lob.copy(g_LOBTable(tmpIdx).temp_lob , msgBody,
2756 imgStrPos-1, 1, 1);
2757
2758 nameStart := instrb(origValue, '/', -1, 1);
2759 if nameStart = 0 then
2760 contentId := trim(value);
2761 fileName := contentId;
2762 contentId := replace(contentId, '.');
2763 else
2764 contentId := trim(substrb(origValue, nameStart+1 ));
2765 fileName := contentId;
2766 contentId := replace(contentId, '.');
2767 end if;
2768 contentId := contentID||'.'||trim(to_char(resourceIdx));
2769
2770 extPos := instrb(fileName, '.', -1, 1);
2771 if (extPos > 0) then
2772 contentType := 'image/'||substr(fileName, extPos+1);
2773 else
2774 contentType := 'image/*';
2775 end if;
2776
2777 newValue := '<IMG SRC="cid:'||contentId||
2778 '" alt="'|| url.display_name||
2779 '"></IMG>';
2780
2781 dbms_lob.writeAppend(g_LOBTable(tmpIdx).temp_lob,
2782 length(newValue), newValue);
2783 amount := dbms_lob.getlength(msgBody)-imgStrPos+length(value);
2784 tmpLength := dbms_lob.getlength(g_LOBTable(tmpIdx).temp_lob);
2785 dbms_lob.Copy(g_LOBTable(tmpIdx).temp_lob, msgBody, amount,
2786 tmpLength+1, imgStrPos+length(value));
2787 dbms_lob.trim(msgBody, 0);
2788 dbms_lob.append(msgBody, g_LOBTable(tmpIdx).temp_lob);
2789
2790 imagesFound := TRUE;
2791 resourceList(resourceIdx).value := origValue;
2792 resourceList(resourceIdx).contentId := contentId;
2793 resourceList(resourceIdx).fileName := fileName;
2794 resourceList(resourceIdx).contentType := contentType;
2795 resourceIdx := resourceIdx + 1;
2796 end if; -- Image TAG is in the messages body
2797 end if; -- The URL is an image reference
2798 end loop;
2799
2800 wf_temp_lob.releaseLob(g_LOBTable, tmpIdx);
2801
2802 return imagesFound;
2803 exception
2804 when others then
2805 -- Release temp LOB if any error , bug 6511028
2806 wf_temp_lob.releaseLob(g_LOBTable, tmpIdx);
2807
2808 wf_core.context('WF_XML','ParseForInlineImages',to_char(nid));
2809 raise;
2810 end ParseForInlineImages;
2811
2812 -- Adds the RESOURCE tags for the inline images.
2813 procedure addInlineImages(doc IN OUT NOCOPY CLOB,
2814 pos IN OUT NOCOPY number,
2815 attrlist IN OUT NOCOPY wf_xml_attr_table_type,
2816 nid IN number,
2817 disposition in varchar2,
2818 resourceList in resourceList_t)
2819 is
2820 extPos pls_integer;
2821 contentType varchar2(1000);
2822 value varchar2(32000);
2823 encodedURL varchar2(32000);
2824 nameStart pls_integer;
2825 contentId varchar2(1000);
2826 fileName varchar2(1000);
2827 occurance number := 1;
2828
2829 renderType varchar2(10);
2830
2831 begin
2832 attrlist.DELETE;
2833
2834 for resourceIdx in 1..resourceList.COUNT loop
2835
2836 encodedURL := wf_mail.urlEncode(resourceList(resourceIdx).value);
2837 AddElementAttribute('content-type', resourceList(resourceIdx).contentType, attrlist);
2838 AddElementAttribute('src', resourceList(resourceIdx).value, attrlist);
2839 AddElementAttribute('content-id', resourceList(resourceIdx).contentId, attrlist);
2840 AddElementAttribute('filename', resourceList(resourceIdx).fileName, attrlist);
2841 AddElementAttribute('content-disposition', disposition, attrlist);
2842
2843 pos := NewLOBTag(doc, pos, 'RESOURCE','',attrlist);
2844 pos := SkipLOBTag(doc, 'RESOURCE', pos, occurance);
2845 attrlist.DELETE;
2846
2847 end loop;
2848
2849 exception
2850 when others then
2851 wf_core.context('WF_XML','addInlineImages', to_char(nid));
2852 raise;
2853 end addInlineImages;
2854
2855
2856 -- Adds a recipient element to the XML in p_doc at position p_pos.
2857 --
2858 -- p_doc The XML document to receive the RECIPIENT element
2859 -- p_role The WF_ROLE name of the recipient
2860 -- p_type The type of recipient - to, cc or bcc
2861 -- p_name The display name for the recipient
2862 -- p_email The email address for the recipient
2863 -- p_pos The position at which the element should be added.
2864
2865 procedure addRecipient(p_doc in out nocopy varchar2,
2866 p_role in varchar2,
2867 p_type in varchar2,
2868 p_name in varchar2,
2869 p_email in varchar2,
2870 p_pos in out nocopy number)
2871 is
2872 display_name varchar2(360);
2873 email varchar2(320);
2874
2875 attrlist wf_xml_attr_table_type;
2876 occurance integer := 1;
2877
2878 begin
2879 AddElementAttribute('name', p_role, attrlist);
2880 AddElementAttribute('type', p_type, attrlist);
2881 p_pos := NewTag(p_doc, p_pos, 'RECIPIENT', '', attrlist);
2882 attrlist.DELETE;
2883 if (p_name is not null or p_name <> '') then
2884 display_name := replace(p_name, g_newLine);
2885 display_name := '<![CDATA['||display_name||']]>';
2886 else
2887 display_name := '';
2888 end if;
2889 p_pos := NewTag(p_doc, p_pos, 'NAME', display_name, attrlist);
2890 p_pos := SkipTag(p_doc, 'NAME', p_pos, occurance);
2891
2892 email := replace(p_email, g_newLine);
2893 email := '<![CDATA['||email||']]>';
2894 p_pos := NewTag(p_doc, p_pos, 'ADDRESS', email, attrlist);
2895 p_pos := SkipTag(p_doc, 'ADDRESS', p_pos, occurance);
2896
2897 p_pos := SkipTag(p_doc, 'RECIPIENT', p_pos, occurance);
2898 end;
2899
2900 -- Adds the recipients identified in p_list to the XML document
2901 -- provided in p_doc. This is a helper procedure for the processing
2902 -- of CC and BCC recipients.
2903 -- p_doc The XML Header document to be modified
2904 -- p_list the semicolon list of recipients
2905 -- p_type The type of recipient to set ("cc" or "bcc").
2906 -- p_pos The current pos in the p_doc to add the recipients. This
2907 -- value will be updated after completion to the current update point.
2908
2909 procedure addCopyRecipients(p_doc in out nocopy varchar2,
2910 p_list in varchar2,
2911 p_type in varchar2,
2912 p_pos in out nocopy integer)
2913 is
2914 copy_recipient_list WF_MAIL_UTIL.parserStack_t;
2915 atPos pls_integer;
2916
2917 display_name varchar2(360);
2918 recipient_role varchar2(320);
2919 email varchar2(320);
2920 pref varchar2(8);
2921 dummy varchar2(1000);
2922 dummy_number number;
2923 orig_system varchar2(30);
2924 orig_system_id number;
2925
2926 cursor members(rname varchar2, rorig varchar2, rorigid number) is
2927 select UR.USER_NAME, UR.USER_ORIG_SYSTEM, UR.USER_ORIG_SYSTEM_ID
2928 from WF_USER_ROLES UR
2929 where UR.ROLE_NAME = rname
2930 and UR.ROLE_ORIG_SYSTEM = rorig
2931 and UR.ROLE_ORIG_SYSTEM_ID = rorigid
2932 and ((UR.USER_NAME <> UR.ROLE_NAME) or
2933 (UR.USER_ORIG_SYSTEM <> UR.ROLE_ORIG_SYSTEM and
2934 UR.USER_ORIG_SYSTEM_ID <> UR.ROLE_ORIG_SYSTEM_ID));
2935
2936 step varchar2(200);
2937
2938 begin
2939 step := 'Initializing the copy list';
2940 copy_recipient_list.DELETE;
2941 copy_recipient_list := WF_MAIL_UTIL.strParser(p_list, ';');
2942 if (copy_recipient_list.COUNT > 0) then
2943
2944 for i in 1..copy_recipient_list.COUNT loop
2945
2946 step := 'Processing {'||copy_recipient_list(i)||'}';
2947 atPos := instrb(copy_recipient_list(i), '@', 1);
2948 if (atPos > 0) then
2949 step := 'Processing email address {'||
2950 copy_recipient_list(i)||'}';
2951 addRecipient(p_doc => p_doc, p_role => '', p_type => p_type,
2952 p_name => '', p_email => copy_recipient_list(i),
2953 p_pos => p_pos);
2954
2955 else
2956
2957 step := 'Processing role {'||copy_recipient_list(i)||'}';
2958
2959 -- Get the qualified email address but the other details
2960 -- are not needed.
2961 WF_DIRECTORY.GetRoleInfoMail(role => copy_recipient_list(i),
2962 display_name => display_name,
2963 email_address => email,
2964 notification_preference => pref,
2965 language => dummy,
2966 territory => dummy,
2967 orig_system => orig_system,
2968 orig_system_id => orig_system_id,
2969 installed_flag => dummy);
2970 if (email is not null and pref <> 'DISABLED') then
2971 step := 'Adding role {'||copy_recipient_list(i)||
2972 '} details to the document';
2973 recipient_role := EncodeEntityReference(copy_recipient_list(i));
2974 addRecipient(p_doc => p_doc,
2975 p_role => recipient_role,
2976 p_type => p_type,
2977 p_name => display_name,
2978 p_email => email,
2979 p_pos => p_pos);
2980
2981 elsif (display_name is not null) then
2982
2983 -- A null email address implies a role with members.
2984
2985 -- Recursion *could* be used here to build a list of
2986 -- single memeber roles and then call the addCopyRecipients
2987 -- to build the XML. Since there is no control then on
2988 -- cyclic references, we will traverse the list only
2989 -- at one level.
2990 for r in members(copy_recipient_list(i), orig_system,
2991 orig_system_id) loop
2992
2993 step := 'Getting member role '||r.user_name||
2994 'details to the document';
2995 WF_DIRECTORY.GetRoleInfoMail(role => r.user_name,
2996 display_name => display_name,
2997 email_address => email,
2998 notification_preference => pref,
2999 language => dummy,
3000 territory => dummy,
3001 orig_system => dummy,
3002 orig_system_id => dummy_number,
3003 installed_flag => dummy);
3004
3005 if (email is not null and pref <> 'DISABLED') then
3006 step := 'Adding member role {'||r.user_name||
3007 '} details to the document';
3008 recipient_role := EncodeEntityReference(r.user_name);
3009 addRecipient(p_doc => p_doc,
3010 p_role => recipient_role,
3011 p_type => p_type,
3012 p_name => display_name,
3013 p_email => email,
3014 p_pos => p_pos);
3015 end if;
3016 end loop; -- Memebers
3017 end if; -- role with members
3018 end if; -- Processing a role
3019 end loop;
3020 end if;
3021 exception
3022 when others then
3023 wf_core.context('WF_XML','addCopyRecipients', p_list, p_type,
3024 step);
3025 end addCopyRecipients;
3026
3027
3028 -- Sets the response template delimiters for HTML messages.
3029 --
3030 -- The delimiter string, rather than containing a set of delimiters
3031 -- to use will contain a code that maps to the delimiters to use.
3032 -- This is to avoid confusion on what this value can be set to and
3033 -- simplifies the parsing of the string.
3034 --
3035 -- Allowable Values:
3036 --
3037 -- DEFAULT or blank - Use the hard coded defaults
3038 -- APOS - '
3039 -- QUOTE - "
3040 -- BRACKET - [ ]
3041 -- IN
3042 -- delimiter_string - The code that represents the HTML delimiters
3043 -- to use.
3044 --
3045 procedure setHtmlDelimiters(delimiter_string IN VARCHAR2)
3046 is
3047 open_html_delimiter VARCHAR2(8);
3048 close_html_delimiter VARCHAR2(8);
3049 begin
3050
3051 if delimiter_string = 'DEFAULT' or delimiter_string is null then
3052 -- No delimiters, then use the hard coded defaults.
3053 open_html_delimiter := null;
3054 close_html_delimiter := null;
3055 elsif delimiter_string = 'APOS' then
3056 open_html_delimiter := '''';
3057 close_html_delimiter := '''';
3058 elsif delimiter_string = 'QUOTE' then
3059 open_html_delimiter := '"';
3060 close_html_delimiter := '"';
3061 elsif delimiter_string = 'BRACKET' then
3062 open_html_delimiter := '[';
3063 close_html_delimiter := ']';
3064 else
3065 -- Unable to recognize the value, so use the hard coded default.
3066 open_html_delimiter := null;
3067 close_html_delimiter := null;
3068 end if;
3069
3070
3071
3072 wf_mail.SetResponseDelimiters(open_text => null,
3073 close_text => null,
3074 open_html => open_html_delimiter,
3075 close_html => close_html_delimiter);
3076
3077 end setHtmlDelimiters;
3078
3079
3080 --
3081 -- GenerateDoc
3082 -- To generate the XML content for the enqueued notifications.
3083 procedure GenerateDoc (p_doc in out NOCOPY CLOB,
3084 p_pos in out NOCOPY number,
3085 p_recipient_role in varchar2,
3086 p_event_name in varchar2,
3087 p_event_key in varchar2,
3088 p_parameter_list in wf_parameter_list_t)
3089 is
3090 -- message CLOB;
3091 l_sec_policy varchar2(100);
3092 t_name varchar2(100);
3093 messageIdx pls_integer;
3094 nid NUMBER;
3095 msgbody VARCHAR2(32000);
3096 end_of_msgbody BOOLEAN;
3097 doctype VARCHAR2(100);
3098
3099 pos integer;
3100 amt number;
3101 installed VARCHAR2(1);
3102
3103 orig_system VARCHAR2(100);
3104 orig_system_id number;
3105
3106 -- <<sstomar> : nls parameters, l_nlsCurrency can be removed once
3107 -- it being removed from WFDS APIs
3108 l_nlsDateFormat VARCHAR2(120);
3109 l_nlsDateLanguage varchar2(120);
3110 l_nlsCalendar varchar2(120);
3111 l_nlsNumericCharacters varchar2(30);
3112 l_nlsSort varchar2(120);
3113 l_nlsLanguage VARCHAR2(120);
3114 l_nlsTerritory VARCHAR2(120);
3115 l_nlsCodeset VARCHAR2(30);
3116
3117 --
3118 l_nlsCurrency VARCHAR2(30);
3119
3120 -- whether context has been changes or not.
3121 l_context_changed BOOLEAN := false;
3122
3123 nidStr varchar2(100);
3124 hdrxml varchar2(32000);
3125 hdrxmlPos integer;
3126
3127 access_key VARCHAR2(100);
3128 priority NUMBER;
3129 recipient_role VARCHAR2(100);
3130 status VARCHAR2(100);
3131 subject VARCHAR2(4000);
3132
3133 role VARCHAR2(100);
3134 display_name VARCHAR2(200);
3135 email VARCHAR2(1000);
3136 notification_pref VARCHAR2(100);
3137
3138 occurance integer := 1;
3139 attrlist wf_xml_attr_table_type;
3140 str varchar2 (250);
3141 nodeName varchar2(100) := '#NODE';
3142 agent varchar2(100) := '#AGENT';
3143 replyto varchar2(100) := '#REPLYTO';
3144 fromName varchar2(100) := '#FROM';
3145 disposition varchar2(100) := '#DISPOSITION';
3146 directResponse varchar2(1);
3147 emailParser varchar2(100);
3148 corrId varchar2(128);
3149 htmlfilename varchar2(100);
3150 urlfilename varchar2(100);
3151 inlineAtt varchar2(10);
3152 sendAccessKey varchar2(10);
3153 stylesheetEnabled varchar2(10);
3154 body_atth varchar2 (32000);
3155 messageType varchar2(8);
3156 messageName varchar2(30);
3157 error_result varchar2 (2000);
3158 err_name varchar2 (30);
3159 err_message varchar2 (2000);
3160 err_stack varchar2 (4000);
3161 moreInfoRole varchar2(320);
3162
3163 frameworkContent boolean;
3164 attachInlineImages varchar2(1);
3165
3166 ntfURL varchar2(2000);
3167 imgFound boolean;
3168
3169 bodyToken varchar2(1);
3170
3171 FromInAttr varchar2(1);
3172 ReplyToInAttr varchar2(1);
3173 EnableStyleInAttr varchar2(1);
3174 dummy varchar2(10);
3175
3176 cc_list varchar2(4000);
3177 bcc_list varchar2(4000);
3178
3179 delimiter_string VARCHAR2(8);
3180
3181 resourceList resourceList_t;
3182
3183 begin
3184
3185 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
3186 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
3187 'wf.plsql.WF_XML.generateDoc', 'BEGIN');
3188 end if;
3189
3190 nid := to_number(p_event_key);
3191
3192 -- retrive base nls parameters
3193 -- Get the Base NLS.
3194 -- 4743123 Only set the value of base NLS params once in a session.
3195 -- <<sstomar>>: already being set in GENERATE function,
3196
3197 -- Obtain notification information
3198 begin -- 3741599 no_data_found should exit gracefully.
3199 select ACCESS_KEY, PRIORITY, STATUS, MESSAGE_TYPE,
3200 MESSAGE_NAME, MORE_INFO_ROLE
3201 into access_key, priority, status, messageType,
3202 messageName, moreInfoRole
3203 from WF_NOTIFICATIONS
3204 where NOTIFICATION_ID = nid;
3205 exception
3206 when no_data_found then
3207 -- 4104735 Empty documents now provide a document type and
3208 -- if a empty document, must provide a reason.
3209
3210 -- <<sstomar>>: Not changing default Lang. territory settings:
3211 -- FOR BUG 7578922
3212 AddElementAttribute('nid', nid, attrlist);
3213 AddElementAttribute('language', 'AMERICAN', attrlist);
3214 AddElementAttribute('territory', 'AMERICA', attrlist);
3215 AddElementAttribute('codeset', 'UTF8', attrlist);
3216
3217 -- Added below default base parameters.
3218 AddElementAttribute('nlsDateformat', wf_core.nls_date_format, attrlist);
3219 AddElementAttribute('nlsDateLanguage', wf_core.nls_date_language, attrlist);
3220 AddElementAttribute('nlsNumericCharacters', wf_core.nls_numeric_characters, attrlist);
3221 AddElementAttribute('nlsSort', wf_core.nls_sort, attrlist);
3222
3223 AddElementAttribute('full-document', 'N', attrlist);
3224 AddElementAttribute('reason', 'no_data_found', attrlist);
3225 p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATION', '', attrlist);
3226 attrlist.DELETE;
3227 p_pos := SkipLOBTag(p_doc, 'NOTIFICATION', p_pos, occurance);
3228
3229 return;
3230 when others then
3231 wf_core.context('WF_XML','GenerateDoc',to_char(nid));
3232 raise;
3233 end;
3234
3235 if (wf_mail.test_flag = TRUE) then
3236 if (status not in ('OPEN','CANCELED','CLOSED')) then
3237 status := 'OPEN';
3238 end if;
3239 end if;
3240
3241 recipient_role := p_recipient_role;
3242
3243 -- <<sstomar>> :
3244 -- Obtain recipient's NLS information by using new API
3245 WF_DIRECTORY.GetRoleInfoMail2(recipient_role, display_name, email,
3246 notification_pref,
3247 orig_system,
3248 orig_system_id,
3249 installed,
3250 l_nlsLanguage ,
3251 l_nlsTerritory ,
3252 l_nlsDateFormat ,
3253 l_nlsDateLanguage ,
3254 l_nlsCalendar ,
3255 l_nlsNumericCharacters ,
3256 l_nlsSort ,
3257 l_nlsCurrency );
3258
3259
3260 if notification_pref in ('QUERY', 'SUMMARY','SUMHTML', 'DISABLED')
3261 or email is null then
3262 -- 4104735 Empty documents now provide a document type and
3263 -- if a empty document, must provide a reason.
3264 AddElementAttribute('nid', nid, attrlist);
3265 AddElementAttribute('language', 'AMERICAN', attrlist);
3266 AddElementAttribute('territory', 'AMERICA', attrlist);
3267 AddElementAttribute('codeset', 'UTF8', attrlist);
3268
3269 -- Added below default base parameters.
3270 AddElementAttribute('nlsDateFormat', wf_core.nls_date_format, attrlist);
3271 AddElementAttribute('nlsDateLanguage', wf_core.nls_date_language, attrlist);
3272 AddElementAttribute('nlsNumericCharacters', wf_core.nls_numeric_characters, attrlist);
3273 AddElementAttribute('nlsSort', wf_core.nls_sort, attrlist);
3274
3275 AddElementAttribute('full-document', 'N', attrlist);
3276 AddElementAttribute('reason', 'NOTIFICATION_PREFERENCE:'||
3277 notification_pref, attrlist);
3278 p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATION', '', attrlist);
3279 attrlist.DELETE;
3280 p_pos := SkipLOBTag(p_doc, 'NOTIFICATION', p_pos, occurance);
3281
3282 return;
3283 end if;
3284
3285
3286 corrId := wf_event.getValueForParameter('Q_CORRELATION_ID',
3287 p_parameter_list);
3288 -- Obtain the values for the configurable items
3289 nodename := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
3290 pCorrId => corrId,
3291 pName => 'NODENAME',
3292 pInAttr => dummy);
3293
3294 agent := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
3295 pCorrId => corrId,
3296 pName => 'HTMLAGENT',
3297 pInAttr => dummy);
3298
3299 fromName := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
3300 pCorrId => corrId,
3301 pName => 'FROM',
3302 pInAttr => FromInAttr);
3303
3304 replyto := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
3305 pCorrId => corrId,
3306 pName => 'REPLYTO',
3307 pInAttr => ReplyToInAttr);
3308
3309 inlineAtt := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
3310 pCorrId => corrId,
3311 pName => 'INLINE_ATTACHMENT',
3312 pInAttr => dummy);
3313
3314 sendAccessKey := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
3315 pCorrId => corrId,
3316 pName => 'SEND_ACCESS_KEY',
3317 pInAttr => dummy);
3318
3319 stylesheetEnabled := wf_mailer_parameter.getValueForCorr(pNid => nid,
3320 pCorrId => corrId,
3321 pName => 'ENABLE_STYLESHEET',
3322 pInAttr => EnableStyleInAttr);
3323
3324 -- 5393647 - Parameter to control the text/html response
3325 -- template delimiter
3326 delimiter_string := wf_mailer_parameter.getValueForCorr(pNId => nid,
3327 pCorrId => corrId,
3328 pName => 'HTML_DELIMITER',
3329 pInAttr => dummy);
3330
3331 setHtmlDelimiters(upper(trim(delimiter_string)));
3332
3333 -- 4676402 Support for CC and BCC. These are not configuration parameters
3334 -- so the values can not be retrieved through the wf_mailer_parameter
3335 -- interface.
3336 -- The precedence will be only for normal notifications. Requests
3337 -- for more information will not be subject to the cc and bcc
3338 -- handling.
3339 -- moreInfoRole is null : either FIRST_SEND or wf.notification.answer event.
3340 if (moreInfoRole is null) then
3341 begin
3342 cc_list := upper(Wf_Notification.GetAttrText(nid, '#WFM_CC'));
3343 exception
3344 when others then
3345 if (wf_core.error_name = 'WFNTF_ATTR') then
3346 wf_core.clear();
3347 cc_list := null;
3348 else
3349 raise;
3350 end if;
3351 end;
3352
3353 begin
3354 bcc_list := upper(Wf_Notification.GetAttrText(nid, '#WFM_BCC'));
3355 exception
3356 when others then
3357 if (wf_core.error_name = 'WFNTF_ATTR') then
3358 wf_core.clear();
3359 bcc_list := null;
3360 else
3361 raise;
3362 end if;
3363 end;
3364 end if;
3365 -- Direct Response is not reconfigurable through the message attributes
3366 -- Use the standard API to obtain its value.
3367 -- They are not configurable at the message level because of the
3368 -- confusion caused with multiple mailers and especially those that
3369 -- share the same correlation ID. The value must be consisten in the
3370 -- PL/SQL layer as well as the Java layer.
3371 directResponse := WF_MAILER_PARAMETER.getValueForCorr(pCorrId => corrId,
3372 pName => 'DIRECT_RESPONSE');
3373
3374 attachInlineImages := WF_MAILER_PARAMETER.GetValueForCorr (
3375 pCorrId => corrId,
3376 pName => 'ATTACH_IMAGES');
3377 if inlineAtt = 'Y' then
3378 disposition := 'inline';
3379 else
3380 disposition := 'attachment';
3381 end if;
3382
3383 if sendAccessKey = 'Y' then
3384 wf_mail.Send_Access_Key_On;
3385 else
3386 wf_mail.Send_Access_Key_Off;
3387 end if;
3388
3389 if directResponse = 'Y' then
3390 wf_mail.direct_response_on;
3391 else
3392 wf_mail.direct_response_off;
3393 end if;
3394
3395 -- If we are here, we are going to generate notification xml payload
3396 -- So allocate TEMP LOB now. << bug 6511028 >>
3397 messageIdx := wf_temp_lob.getLob(g_LOBTable);
3398
3399 WF_MAIL.setContext(nid);
3400
3401 -- Set the preferred document type based on the
3402 -- notification preference.
3403 if notification_pref = 'MAILTEXT' then
3404 doctype := g_ntfDocText;
3405 elsif notification_pref in ('MAILHTML','MAILATTH','MAILHTM2') then
3406 doctype := g_ntfDocHtml;
3407 end if;
3408
3409 -- bug 5456241 : Passing doctype parameter
3410 if (WF_NOTIFICATION.isFwkRegion(nid, doctype)='Y' and
3411 g_install ='EMBEDDED') then
3412
3413 frameworkContent := TRUE;
3414 -- 3803327 Text email with framework is currently disabled
3415 -- until the issues with the region facet for text can be
3416 -- fixed.
3417 -- if (notification_pref = 'MAILTEXT') then
3418 -- notification_pref := 'MAILHTM2';
3419 -- elsif (notification_pref = 'MAILATTH') then
3420 -- notification_pref := 'MAILHTML';
3421 -- end if;
3422 -- 3803327 end.
3423 else
3424 frameworkContent := FALSE;
3425 end if;
3426
3427 -- Get the language, territory and codeset for this notification
3428 -- If #WFM_* not defined or not installd, then user's language will be used
3429 -- If user's language also not installed then below API updates OUT params
3430 -- with base language, territory and charset etc..
3431 --
3432 -- TODO : Get_Ntf_Language : optimization required.
3433 WF_MAIL.Get_Ntf_Language(nid, l_nlsLanguage, l_nlsTerritory, l_nlsCodeset);
3434
3435 -- Set NLS language and territory for this notification
3436 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
3437 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
3438 'wf.plsql.WF_XML.generateDoc',
3439 'Setting User NLS {'|| l_nlsLanguage ||'} {'||l_nlsTerritory||'}'
3440 || l_nlsDateFormat || '} {' || l_nlsDateLanguage || '} {'
3441 || '} {' || l_nlsCalendar || '} {' || l_nlsNumericCharacters
3442 || '} {' || l_nlsSort || '}');
3443 end if;
3444
3445 -- set user's language and territory : charset we can not chage for a session at DB layer.
3446 -- setNLS(l_nlsLanguage, l_nlsTerritory);
3447
3448
3449
3450 if( nvl(l_nlsLanguage, 'AMERICA') <> g_base_language or
3451 NVL( l_nlsTerritory, 'AMERICAN') <> g_base_territory or
3452 nvl(l_nlsDateFormat , wf_core.nls_date_format) <> g_base_nlsDateFormat or
3453 nvl(l_nlsDateLanguage, wf_core.nls_date_language) <> g_base_nlsDateLanguage or
3454 nvl(l_nlsNumericCharacters, wf_core.nls_numeric_characters) <> g_base_nlsNumericCharacters or
3455 nvl(l_nlsSort, wf_core.nls_sort) <> g_base_nlsSort ) then
3456
3457 -- nid, l_nlsCalendar, just set here only.
3458 -- at other places pass value as null.
3459 WF_NOTIFICATION_UTIL.SetNLSContext(nid,
3460 l_nlsLanguage ,
3461 l_nlsTerritory ,
3462 l_nlsDateFormat ,
3463 l_nlsDateLanguage ,
3464 l_nlsNumericCharacters ,
3465 l_nlsSort ,
3466 l_nlsCalendar );
3467
3468 l_context_changed := true;
3469 else
3470
3471 -- No need to SET context but set NID, Calendar as these two parameters
3472 -- are being used in WF_NOTIFICATION_UTIL.GetCalendarDate
3473 WF_NOTIFICATION_UTIL.setCurrentNID(nid);
3474 WF_NOTIFICATION_UTIL.setCurrentCalendar(l_nlsCalendar);
3475
3476 END if;
3477
3478
3479 -- Initialise the XML Document and then progressively walk
3480 -- through the elements. Populating them as we go.
3481 -- l_pos is crucial as it determines where the next nodes
3482 -- will be placed.
3483 pos := p_pos;
3484
3485 if frameworkContent = TRUE then
3486 -- Get a modified version of the message. All is rendered
3487 -- except for the &BODY token.
3488 -- NOTE: TEMP LOBs allocated within WF_MAIL.GetLobMessage3 for
3489 -- message body contents should be released here
3490 -- after processsing
3491 WF_MAIL.GetLobMessage3(nid, nodeName, agent, replyto,
3492 recipient_role, l_nlsLanguage, l_nlsTerritory,
3493 notification_pref, email,
3494 display_name,
3495 'N',
3496 subject, body_atth,
3497 error_result, bodyToken);
3498
3499 else
3500 WF_MAIL.GetLobMessage3(nid, nodeName, agent, replyto,
3501 recipient_role, l_nlsLanguage, l_nlsTerritory,
3502 notification_pref, email,
3503 display_name,
3504 'Y',
3505 subject, body_atth,
3506 error_result, bodyToken);
3507
3508 end if;
3509
3510 if error_result is not null or error_result <> '' then
3511 wf_core.token('ERROR',error_result);
3512 wf_core.raise('WFMLR_GENERATE_FAILED');
3513 end if;
3514
3515 g_htmlmessage := wf_core.translate('WF_HTML_MESSAGE');
3516 g_urlNotification := wf_core.translate('WF_URL_NOTIFICATION');
3517 g_urlListAttachment := wf_core.translate('WF_URLLIST_ATTACHMENT');
3518
3519 -- Reset base NLS settings
3520 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
3521 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
3522 'wf.plsql.WF_XML.generateDoc',
3523 'Re-Setting Base NLS {'|| g_base_language ||'} {'||g_base_territory||'}'
3524 || g_base_nlsDateFormat || '} {' || g_base_nlsDateLanguage || '} {'
3525 || '} {' || g_base_nlsCalendar || '} {' || g_base_nlsNumericCharacters
3526 || '} {' || g_base_nlsSort || '}');
3527 end if;
3528
3529
3530 -- RESET base setting so that all XML generation
3531 -- processing would be in DEFAULT settings
3532 -- << sstomar>> : RESETTING for base NLS can be done once ..
3533 -- after generating XML Payload but problem may be that
3534 -- Mailer which runs on default NLS may not be able to parse payload??
3535 -- TODO : need to check above case.
3536 if(l_context_changed ) then
3537
3538 -- set nid, calendar once only.
3539 -- <<bug 8220816>> : keep WF_NOTIFICATION_UTIL.g_nid and g_calendar in global variable
3540 -- during life-life cycle of this API.
3541 WF_NOTIFICATION_UTIL.SetNLSContext(
3542 nid,
3543 g_base_language ,
3544 g_base_territory ,
3545 g_base_nlsDateFormat ,
3546 g_base_nlsDateLanguage ,
3547 g_base_nlsNumericCharacters ,
3548 g_base_nlsSort ,
3549 g_base_nlsCalendar );
3550
3551 end if;
3552
3553 htmlfilename := g_htmlmessage||'.html';
3554 urlfilename := g_urlNotification||'.html';
3555
3556 nidStr := 'NID['||to_char(nid)||'/'||access_key||'@'||nodeName||']';
3557 if directResponse = 'Y' then
3558 nidStr := nidStr||'[2]';
3559 end if;
3560
3561 AddElementAttribute('nid', p_event_key, attrlist);
3562 AddElementAttribute('nidstr', nidStr, attrlist);
3563
3564 AddElementAttribute('language', l_nlsLanguage, attrlist);
3565 AddElementAttribute('territory', l_nlsTerritory, attrlist);
3566 AddElementAttribute('codeset', l_nlsCodeset, attrlist);
3567 --
3568 -- <<sstomar>> : NLS changes, bug 7578922
3569 AddElementAttribute('nlsDateformat', l_nlsDateFormat, attrlist);
3570
3571 -- << REMOVE validation later> we can validate if date_language is installed or not.
3572 if(WF_NOTIFICATION_UTIL.isLanguageInstalled(l_nlsDateLanguage)) then
3573 AddElementAttribute('nlsDateLanguage', l_nlsDateLanguage, attrlist);
3574 else
3575 AddElementAttribute('nlsDateLanguage', l_nlsLanguage, attrlist);
3576 end if;
3577
3578 AddElementAttribute('nlsNumericCharacters', l_nlsNumericCharacters, attrlist);
3579 AddElementAttribute('nlsSort', l_nlsSort, attrlist);
3580
3581 AddElementAttribute('priority', to_char(priority), attrlist);
3582 AddElementAttribute('item_type', messageType, attrlist);
3583 AddElementAttribute('message_name', messageName, attrlist);
3584 AddElementAttribute('full-document', 'Y', attrlist);
3585
3586 pos := NewLOBTag(p_doc, pos, 'NOTIFICATION', '', attrlist);
3587 attrlist.DELETE;
3588
3589 hdrxmlPos := 0;
3590 hdrxml := '';
3591
3592 hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'RECIPIENTLIST', '', attrlist);
3593
3594 -- 4676402 Adding support for CC and BCC. The recipients are
3595 -- straight email addresses in a semi-colon seperated list.
3596 -- Commas should not be used as delimiters as they can appear in the
3597 -- email address.
3598
3599 recipient_role := EncodeEntityReference(recipient_role);
3600 addRecipient(p_doc => hdrxml,
3601 p_role => recipient_role,
3602 p_type => 'to',
3603 p_name => display_name,
3604 p_email => email,
3605 p_pos => hdrxmlPos);
3606
3607 addCopyRecipients(hdrxml, cc_list, 'cc', hdrxmlPos);
3608 addCopyRecipients(hdrxml, bcc_list, 'bcc', hdrxmlPos);
3609
3610 hdrxmlPos := SkipTag(hdrxml, 'RECIPIENTLIST', hdrxmlPos, occurance);
3611
3612 -- 3692786 Only if the FROM and REPLYTO are defined as message
3613 -- attributes will they be put on the XML. Otherwise it will
3614 -- be up to what is defined for the mailer.
3615 if FromInAttr = 'Y' or ReplyToInAttr = 'Y' then
3616 hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'FROM', '', attrlist);
3617
3618 if FromInAttr = 'Y' then
3619 fromName := replace(fromName, g_newLine);
3620 hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'NAME', fromName, attrlist);
3621 hdrxmlPos := SkipTag(hdrxml, 'NAME', hdrxmlPos, occurance);
3622 end if;
3623 if ReplyToInAttr = 'Y' then
3624 replyto := replace(replyto, g_newLine);
3625 replyto := '<![CDATA['||replyto||']]>';
3626 hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'ADDRESS', replyto,
3627 attrlist);
3628 hdrxmlPos := SkipTag(hdrxml, 'ADDRESS', hdrxmlPos, occurance);
3629 end if;
3630 hdrxmlPos := SkipTag(hdrxml, 'FROM', hdrxmlPos, occurance);
3631 end if;
3632
3633 hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'STATUS', status, attrlist);
3634 hdrxmlPos := SkipTag(hdrxml, 'STATUS', hdrxmlPos, occurance);
3635
3636 subject := replace(subject, g_newLine);
3637 subject := '<![CDATA['||subject||']]>';
3638 hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'SUBJECT', subject, attrlist);
3639
3640 pos := NewLOBTag(p_doc, pos, 'HEADER', hdrxml, attrlist);
3641 pos := SkipLOBTag(p_doc, 'HEADER', pos, occurance);
3642
3643 -- <<sstomar bug 6993909>> : to get #WF_SECURITY_POLICY
3644 --- Below API returns Template Name based on l_sec_policy . So if 'OPEN_MAIL_SECURE'
3645 -- it means user should view online version of ntf but user will
3646 -- get "notification detail.html" as attachement.
3647 Wf_Mail.ProcessSecurityPolicy(nid, l_sec_policy, t_name);
3648
3649
3650 /*
3651 ** Potentially, it could be possible for a text/plain notification
3652 ** but that would mean looking ahead to ensure that there are
3653 ** absolutely no attachments of any description.
3654 */
3655 /*
3656 ** The body part section of the XML structure will contain
3657 ** a <![CDATA ]]> construct which removes the need to URL
3658 ** encode the data.
3659 */
3660 if notification_pref in ('MAILTEXT','MAILATTH') then
3661 /*
3662 ** MAILTEXT and MAILATTH will have the text/plain as the
3663 ** primary messasge content. The others will be in the
3664 ** from of an attachment.
3665 */
3666 dbms_lob.trim(g_LOBTable(messageIdx).temp_lob, 0);
3667
3668 -- This getBodyPart API releases temp lob allocated within
3669 -- WF_MAIL.GetLobMessage3 for doctype (text/plain )
3670 -- i.e. g_text_messageIdx locator
3671 getBodyPart(g_LOBTable(messageIdx).temp_lob, nid,
3672 g_ntfDocText);
3673
3674 -- bug 6196382 :
3675 -- Outer structure i.e. <CONTENT tag>'s content-type for fwk based ntf.
3676 -- should be multipart/related; otherwise Thunderbird email client
3677 -- will show text/html body part also with text/plain for MAILATTH user
3678 --
3679
3680 -- << sstomar bug 6993909 >> :
3681 -- Always use 'multipart/mixed' with CONTENT tag for 'MAILTEXT','MAILATTH'.
3682 -- It should also be used when #WF_SECURITY_POLICY=NO_EMAIL
3683 -- i.e. t_name (Template name) ='OPEN_MAIL_SECURE'
3684 AddElementAttribute('content-type', 'multipart/mixed', attrlist);
3685
3686
3687 pos := NewLOBTag(p_doc, pos, 'CONTENT', '', attrlist);
3688 attrlist.DELETE;
3689
3690 -- -- BODYPART TAG: This text/plain is a first body part for
3691 -- MAILATTH and MAILTEXT users.
3692
3693 AddElementAttribute('content-type', g_ntfDocText, attrlist);
3694
3695 pos := NewLOBTag(p_doc, pos, 'BODYPART', '', attrlist);
3696
3697 -- Same Content-Type with message TAG as there won't be Images or
3698 -- Other resources referring within first message BODY.
3699 pos := NewLOBTag(p_doc, pos, 'MESSAGE',
3700 g_LOBTable(messageIdx).temp_lob, attrlist);
3701
3702 pos := SkipLOBTag(p_doc, 'MESSAGE', pos, occurance);
3703
3704 if frameworkContent = TRUE and bodyToken = 'Y' then
3705 -- Build the resource section of the XML so that the
3706 -- java layer can locate the notification body to
3707 -- merge into the template.
3708
3709 ntfURL := wf_mail.urlEncode(wf_notification.getFwkBodyURL2(nid,
3710 g_ntfDocText,
3711 l_nlsLanguage,
3712 l_nlsCalendar));
3713
3714 AddElementAttribute('page-type','fwk', attrlist);
3715 AddElementAttribute('src',ntfURL, attrlist);
3716 if EnableStyleInAttr = 'Y' then
3717 AddElementAttribute('enable-stylesheet', stylesheetEnabled,
3718 attrlist);
3719 end if;
3720
3721 -- <<sstomar>>: These parameters may be redundant as Notification
3722 -- level parameter would be used for NLS context at java layer
3723 AddElementAttribute('language',l_nlsLanguage, attrlist);
3724 AddElementAttribute('territory',l_nlsTerritory, attrlist);
3725
3726 AddElementAttribute('token', 'BODY', attrlist);
3727
3728
3729 pos := NewLOBTag(p_doc, pos, 'RESOURCE','',attrlist);
3730 attrlist.DELETE;
3731 pos := SkipLOBTag(p_doc, 'RESOURCE', pos, occurance);
3732 end if;
3733
3734 pos := SkipLOBTag(p_doc, 'BODYPART', pos, occurance);
3735 attrlist.DELETE;
3736 end if; -- END for 'MAILTEXT','MAILATTH'
3737
3738 if notification_pref in ('MAILHTML','MAILATTH','MAILHTM2') then
3739
3740 --
3741 -- The HTML version of the message is only available
3742 -- to MAILHTML and MAILATTH recipients.
3743 -- MAILHTML2 is a text/html message without the additional
3744 -- two framed "Notification Detail.html" attachment, but other attachement
3745 -- may be available.
3746 --
3747 dbms_lob.trim(g_LOBTable(messageIdx).temp_lob, 0);
3748
3749 -- This getBodyPart API releases temp lob allocated within
3750 -- WF_MAIL.GetLobMessage3 for doctype (text/html )
3751 -- i.e. g_html_messageIdx locator
3752 getBodyPart(g_LOBTable(messageIdx).temp_lob, nid,
3753 g_ntfDocHtml);
3754
3755 imgFound := FALSE;
3756 resourceList.DELETE;
3757
3758 if frameworkContent = FALSE AND attachInlineImages = 'Y' then
3759 imgFound := ParseForInlineImages(g_LOBTable(messageIdx).temp_lob,
3760 nid, resourceList);
3761 end if;
3762
3763 --
3764 -- The content-type for the CONTENT tag is based on a set of rules.
3765 -- It can only be multiplart/mixed or multiplart/related if it is a framework region,
3766 -- or there is an inline image. Inline images and framework regions are
3767 -- only allowed for HTML type notifications.
3768 --
3769 -- 4481199 - If the prefernce is MAILATTH, then the CONTENT
3770 -- element is already defined.
3771
3772 -- << sstomar, bug6993909 : commenting below code block >>
3773 -- if (notification_pref in ('MAILHTML', 'MAILHTM2')) then
3774 -- if(frameworkContent = TRUE or imgFound = TRUE) then
3775 -- AddElementAttribute('content-type', 'multipart/related', attrlist);
3776 -- else
3777 -- AddElementAttribute('content-type', 'multipart/mixed', attrlist);
3778 -- end if;
3779 --
3780 -- pos := NewLOBTag(p_doc, pos, 'CONTENT', '', attrlist);
3781 -- attrlist.DELETE;
3782 -- end if;
3783
3784 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
3785 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
3786 'wf.plsql.WF_XML.generateDoc',
3787 ' Template Name returned by Wf_Mail.ProcessSecurityPolicy: [' || t_name || ']' );
3788
3789 if (frameworkContent ) then
3790 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
3791 'wf.plsql.WF_XML.generateDoc', ' framework-Content ?: YES') ;
3792 else
3793 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
3794 'wf.plsql.WF_XML.generateDoc', ' framework-Content ?: NO') ;
3795 end if;
3796 end if;
3797
3798 -- << sstomar bug 6993909>> :
3799 -- CASE 1 : MAILHTM2
3800 if ( notification_pref in ('MAILHTM2') ) then
3801
3802 -- MAILHTM2, text/html only be exist as a FIRST body part but may have
3803 -- additional attachements EXCEPT "Notification Detail.html".
3804
3805 -- For CONTENT tag.
3806 if ((frameworkContent = true or imgFound = true) and
3807 ( t_name is null or t_name <>'OPEN_MAIL_SECURE') ) then
3808
3809 AddElementAttribute('content-type', 'multipart/related', attrlist);
3810 else
3811 -- NOT a Fwk Content:
3812 -- MAILHTM2 may have INLINE / attachements resources.
3813 AddElementAttribute('content-type', 'multipart/mixed', attrlist);
3814 end if;
3815
3816 pos := NewLOBTag(p_doc, pos, 'CONTENT', '', attrlist);
3817 attrlist.DELETE;
3818
3819 -- For BODYPART tag
3820 -- NOTE : This would be a first BODY part so SET it text/html
3821 AddElementAttribute('content-type', 'text/html', attrlist);
3822
3823 -- CASE 2: MAILHTML always will have "Notification Detail.html" attachement
3824 elsif (notification_pref in ('MAILHTML') ) then
3825
3826 -- No need to check t_name ==>'OPEN_MAIL_SECURE' because
3827 -- "Notification Detail.html" always be there for MAILHTML.
3828 AddElementAttribute('content-type', 'multipart/mixed', attrlist);
3829
3830 pos := NewLOBTag(p_doc, pos, 'CONTENT', '', attrlist);
3831 attrlist.DELETE;
3832
3833 -- For BODYPART tag
3834 if ( (frameworkContent = true or imgFound = true) and
3835 ( t_name is null or t_name <>'OPEN_MAIL_SECURE') ) then
3836
3837 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
3838 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
3839 'wf.plsql.WF_XML.generateDoc',
3840 'Setting BODYPART content-type multipart/related');
3841 end if;
3842
3843 -- Set Content-Type = multipart/related because this BODY MAY have
3844 -- other related body-parts which make aggregate body OBJECT ...
3845 AddElementAttribute('content-type', 'multipart/related', attrlist);
3846 else
3847 -- NO Fwk /Image ref : default text/html
3848 AddElementAttribute('content-type', 'text/html', attrlist);
3849 end if;
3850
3851 -- CASE 3: MAILATTH
3852 else
3853 -- Content-Type for CONTENT tag has already been set.
3854
3855 -- For MAILATTH : text/html msg body would be seen as attachement
3856 -- + "Notification Detail.html"
3857 -- BUT text/html will have references of Images / Stylesheets
3858 -- ( NOT be sent as INLINE attachment).
3859
3860 -- For BODYPART tag
3861 AddElementAttribute('content-type', 'text/html', attrlist);
3862
3863 end if;
3864
3865 pos := NewLOBTag(p_doc, pos, 'BODYPART', '', attrlist);
3866 attrlist.DELETE;
3867
3868 -- FOR MESSAGE tag
3869 AddElementAttribute('content-type', g_ntfDocHtml, attrlist);
3870
3871 if notification_pref = 'MAILATTH' then
3872
3873 -- The text/html will be an attachment for MAILTEXT
3874 AddElementAttribute('content-disposition',disposition, attrlist);
3875 AddElementAttribute('filename',htmlfilename, attrlist);
3876 end if;
3877
3878 pos := NewLOBTag(p_doc, pos, 'MESSAGE',
3879 g_LOBTable(messageIdx).temp_lob, attrlist);
3880 pos := SkipLOBTag(p_doc, 'MESSAGE', pos, occurance);
3881
3882 if frameworkContent = TRUE and bodyToken = 'Y' then
3883 -- Build the resource section of the XML so that the
3884 -- java layer can locate the notification body to
3885 -- merge into the template.
3886
3887 ntfURL := wf_mail.urlEncode(wf_notification.getFwkBodyURL2(nid,
3888 g_ntfDocHtml,
3889 l_nlsLanguage,
3890 l_nlsCalendar));
3891
3892
3893 AddElementAttribute('page-type','fwk', attrlist);
3894 AddElementAttribute('src',ntfURL, attrlist);
3895 if EnableStyleInAttr = 'Y' then
3896 AddElementAttribute('enable-stylesheet', stylesheetEnabled,
3897 attrlist);
3898 end if;
3899
3900 AddElementAttribute('language', l_nlsLanguage, attrlist);
3901 AddElementAttribute('territory', l_nlsTerritory, attrlist);
3902 AddElementAttribute('token', 'BODY', attrlist);
3903
3904
3905 pos := NewLOBTag(p_doc, pos, 'RESOURCE','',attrlist);
3906 attrlist.DELETE;
3907 pos := SkipLOBTag(p_doc, 'RESOURCE', pos, occurance);
3908 elsif (frameworkContent = FALSE AND imgFound = TRUE) then
3909 -- Add RESOURCE tags for each of the images pased in
3910 -- in the ParseForInlineImages
3911 addInlineImages(p_doc, pos, attrlist, nid, disposition,
3912 resourceList);
3913 end if;
3914
3915 pos := SkipLOBTag(p_doc, 'BODYPART', pos, occurance);
3916 attrlist.DELETE;
3917 end if;
3918
3919 if notification_pref in ('MAILHTML','MAILATTH') then
3920 /*
3921 ** Adding the text/html component to the notification.
3922 ** This is the little two framed representation of the
3923 ** notification.
3924 **
3925 ** This is only available for the MAILHTML and MAILATTH recipients.
3926 */
3927 AddElementAttribute('content-type',g_ntfDocHtml, attrlist);
3928 pos := NewLOBTag(p_doc, pos, 'BODYPART', '', attrlist);
3929
3930 body_atth := '<![CDATA['||body_atth||']]>';
3931 AddElementAttribute('content-disposition',disposition, attrlist);
3932 AddElementAttribute('filename',urlfilename, attrlist);
3933 pos := NewLOBTag(p_doc, pos, 'MESSAGE', body_atth, attrlist);
3934 pos := SkipLOBTag(p_doc, 'MESSAGE', pos, occurance);
3935 pos := SkipLOBTag(p_doc, 'BODYPART', pos, occurance);
3936 attrlist.DELETE;
3937 end if;
3938
3939
3940 -- Bug 5379861: Notification Reference section and Attachements of a pl/sql
3941 -- notificaton appears as Non-Translated.
3942 -- Set NLS language and territory for this notification
3943 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
3944 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
3945 'wf.plsql.WF_XML.generateDoc',
3946 'Setting User NLS {'|| l_nlsLanguage ||'} {'||l_nlsTerritory||'}'
3947 || l_nlsDateFormat || '} {' || l_nlsDateLanguage || '} {'
3948 || '} {' || l_nlsCalendar || '} {' || l_nlsNumericCharacters
3949 || '} {' || l_nlsSort || '}');
3950 end if;
3951
3952 -- Set notification /user language before processing attachements.
3953 -- SetNLS(l_nlsLanguage, l_nlsTerritory);
3954 if(l_context_changed ) then
3955
3956 -- l_nlsCalendar only will be stored in global variable
3957 WF_NOTIFICATION_UTIL.SetNLSContext(
3958 nid,
3959 l_nlsLanguage ,
3960 l_nlsTerritory ,
3961 l_nlsDateFormat ,
3962 l_nlsDateLanguage ,
3963 l_nlsNumericCharacters ,
3964 l_nlsSort ,
3965 l_nlsCalendar );
3966
3967
3968 END if;
3969
3970 --
3971 -- Next will be to attach all URLs and DOCUMENT attributes
3972 -- as attachments. Disposition for all URL and DOCUMENT attributes
3973 -- with ATTACH=Y should be attachment.
3974 --
3975
3976 --
3977 -- Bug 6671568: When #WF_SECURITY_POLICY = 'NO_EMAIL' or 'ENC_EMAIL_ONLY'
3978 -- we should not send any other content related to the notification, either
3979 -- attachment or inline through e-mail.
3980 --
3981
3982 -- << sstomar : below API is being called above so t_name have assigned (null or somevalue) .
3983 -- Wf_Mail.ProcessSecurityPolicy(nid, l_sec_policy, t_name);
3984
3985 if (t_name is not null) then
3986 if (t_name <> 'OPEN_MAIL_SECURE')
3987 then
3988 pos := GetAttachment(nid, p_doc, agent, 'attachment', doctype, pos);
3989 end if;
3990 else
3991 pos := GetAttachment(nid, p_doc, agent, 'attachment', doctype, pos);
3992 end if;
3993
3994 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
3995 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
3996 'wf.plsql.WF_XML.generateDoc',
3997 'Re-Setting Base NLS {'|| g_base_language ||'} {'||g_base_territory||'}'
3998 || g_base_nlsDateFormat || '} {' || g_base_nlsDateLanguage || '} {'
3999 || '} {' || g_base_nlsCalendar || '} {' || g_base_nlsNumericCharacters
4000 || '} {' || g_base_nlsSort || '}');
4001 end if;
4002
4003 -- Bug 5379861: Reset back base language.
4004 -- SetNLS(g_base_language, g_base_territory);
4005 -- Reset base language
4006 if( l_context_changed ) then
4007
4008 WF_NOTIFICATION_UTIL.SetNLSContext
4009 (null, -- Resetting null for Nid is fine.
4010 g_base_language ,
4011 g_base_territory ,
4012 g_base_nlsDateFormat ,
4013 g_base_nlsDateLanguage ,
4014 g_base_nlsNumericCharacters ,
4015 g_base_nlsSort ,
4016 g_base_nlsCalendar );
4017 end if;
4018
4019 pos := SkipLOBTag(p_doc, 'CONTENT', pos, occurance);
4020 pos := SkipLOBTag(p_doc, 'NOTIFICATION', pos, occurance);
4021
4022 p_pos := pos;
4023 wf_temp_lob.releaseLob(g_LOBTable, messageIdx);
4024
4025
4026 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
4027 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
4028 'wf.plsql.WF_XML.generateDoc', 'END');
4029 end if;
4030 exception
4031 when others then
4032
4033 wf_temp_lob.releaseLob(g_LOBTable, messageIdx);
4034
4035 -- Since within wf_mail.getLOBMessage3 we are releasing
4036 -- TEMP LOBs incase of any exception, so there is no need to
4037 -- release those LOBs here. -- WF_MAIL.CloseLob(doctype)
4038
4039 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
4040 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
4041 'wf.plsql.WF_XML.generateDoc',
4042 'Re-Setting Base NLS {'|| g_base_language ||'} {'||g_base_territory||'}'
4043 || g_base_nlsDateFormat || '} {' || g_base_nlsDateLanguage || '} {'
4044 || '} {' || g_base_nlsCalendar || '} {' || g_base_nlsNumericCharacters
4045 || '} {' || g_base_nlsSort || '}');
4046 end if;
4047
4048 -- Reset base language, in case of exception.
4049 -- SetNLS(g_base_language, g_base_territory);
4050 -- Reset base language
4051 WF_NOTIFICATION_UTIL.SetNLSContext(
4052 null,
4053 g_base_language ,
4054 g_base_territory ,
4055 g_base_nlsDateFormat ,
4056 g_base_nlsDateLanguage ,
4057 g_base_nlsNumericCharacters ,
4058 g_base_nlsSort ,
4059 g_base_nlsCalendar );
4060
4061
4062
4063 wf_core.context('WF_XML', 'GenerateDoc', p_event_name, p_event_key);
4064 raise;
4065 end GenerateDoc;
4066
4067
4068 -- GenerateGroupDoc
4069 -- To generate the complete set of notification for a group.
4070 procedure GenerateGroupDoc(p_doc in out NOCOPY CLOB,
4071 p_pos in out NOCOPY number,
4072 p_recipient_role in varchar2,
4073 p_notification_pref in varchar2,
4074 p_orig_system in varchar2,
4075 p_orig_system_id in number,
4076 p_event_name in varchar2,
4077 p_event_key in varchar2,
4078 p_parameter_list in wf_parameter_list_t)
4079 is
4080
4081 cursor members(rname varchar2, rorig varchar2, rorigid number) is
4082 select UR.USER_NAME, UR.USER_ORIG_SYSTEM, UR.USER_ORIG_SYSTEM_ID
4083 from WF_USER_ROLES UR
4084 where UR.ROLE_NAME = rname
4085 and UR.ROLE_ORIG_SYSTEM = rorig
4086 and UR.ROLE_ORIG_SYSTEM_ID = rorigid
4087 and ((UR.USER_NAME <> UR.ROLE_NAME) or
4088 (UR.USER_ORIG_SYSTEM <> UR.ROLE_ORIG_SYSTEM and
4089 UR.USER_ORIG_SYSTEM_ID <> UR.ROLE_ORIG_SYSTEM_ID));
4090
4091 members_type members%ROWTYPE;
4092 TYPE members_table_type is TABLE OF members%ROWTYPE
4093 INDEX BY BINARY_INTEGER;
4094 i pls_integer := 1;
4095
4096 members_t members_table_type;
4097 attrlist wf_xml_attr_table_type;
4098 inAttr varchar2(1);
4099 resetNls varchar2(10);
4100 corrId varchar2(128);
4101
4102 begin
4103 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
4104 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
4105 'wf.plsql.WF_XML.generateGroupDoc', 'BEGIN');
4106 end if;
4107 i := 1;
4108 for r in members(p_recipient_role, p_orig_system, p_orig_system_id) loop
4109 members_t(i).user_name := r.user_name;
4110 members_t(i).user_orig_system:= r.user_orig_system;
4111 members_t(i).user_orig_system_id:= r.user_orig_system_id;
4112 i := i + 1;
4113 end loop;
4114
4115
4116 -- 4104735 Removing the test for "p_notification_pref not in
4117 -- ('QUERY','SUMMARY','SUMHTML', 'DISABLED')". The rule to
4118 -- send or not should come from the individual memebers and not the
4119 -- parent role. If there are simply no members then the NULL document
4120 -- should be sent. The testing of the notification preference for
4121 -- the members will be performed again in genreateDoc.
4122 if members_t.count = 0 then
4123 -- No role members. Only log it for now and change the
4124 -- notification preference to QUERY.
4125 -- ---------------------------------------------------
4126 -- wf_core.context('WF_XML','GenerateGroup', p_event_name,
4127 -- p_event_key);
4128 -- wf_core.token('NID', p_event_key);
4129 -- wf_core.token('ROLE', p_recipient_role);
4130 -- wf_core.raise('WFMLR_NOROLEMEMBERS');
4131 -- ---------------------------------------------------
4132 AddElementAttribute('maxcount', '1', attrlist);
4133 p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATIONGROUP', '', attrlist);
4134 attrlist.DELETE;
4135
4136 -- 4104735 Empty documents now provide a document type and
4137 -- if a empty document, must provide a reason.
4138 AddElementAttribute('nid', p_event_key, attrlist);
4139 AddElementAttribute('language', 'AMERICAN', attrlist);
4140 AddElementAttribute('territory', 'AMERICA', attrlist);
4141 AddElementAttribute('codeset', 'UTF8', attrlist);
4142 -- Added below default base parameters.
4143 AddElementAttribute('nlsDateformat', wf_core.nls_date_format, attrlist);
4144 AddElementAttribute('nlsDateLanguage', wf_core.nls_date_language, attrlist);
4145 AddElementAttribute('nlsNumericCharacters', wf_core.nls_numeric_characters, attrlist);
4146 AddElementAttribute('nlsSort', wf_core.nls_sort, attrlist);
4147
4148 AddElementAttribute('full-document', 'N', attrlist);
4149 AddElementAttribute('reason', 'no_members', attrlist);
4150 p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATION', '', attrlist);
4151 attrlist.DELETE;
4152
4153 else
4154
4155 -- 4628088 Send the RESET_NLS flag if one is defined
4156 -- at the message level.
4157 corrId := wf_event.getValueForParameter('Q_CORRELATION_ID',
4158 p_parameter_list);
4159 resetNls := WF_MAILER_PARAMETER.GetValueForCorr (pNid => p_event_key,
4160 pCorrId => corrId,
4161 pName => 'RESET_NLS',
4162 pInAttr => inAttr);
4163
4164 AddElementAttribute('maxcount',to_char(members_t.count), attrlist);
4165 if(inAttr = 'Y') then
4166 AddElementAttribute('reset-nls',resetNls, attrlist);
4167 end if;
4168 p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATIONGROUP', '', attrlist);
4169 attrlist.DELETE;
4170
4171 for i in 1..members_t.count loop
4172 generateDoc(p_doc, p_pos, members_t(i).user_name,
4173 p_event_name, p_event_key, p_parameter_list);
4174 end loop;
4175 end if;
4176
4177 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
4178 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
4179 'wf.plsql.WF_XML.generateGroupDoc', 'END');
4180 end if;
4181 exception
4182 when others then
4183 wf_core.context('WF_XML','GenerateGroupDoc',p_event_name, p_event_key);
4184 raise;
4185 end GenerateGroupDoc;
4186
4187 -- GenerateMessage
4188 -- To generate the XML content for a single notification
4189 procedure GenerateMessage(p_doc in out nocopy CLOB,
4190 p_event_name in varchar2,
4191 p_event_key in varchar2,
4192 p_parameter_list in wf_parameter_list_t)
4193 is
4194 nid NUMBER;
4195
4196 pos integer;
4197 amt number;
4198
4199 installed VARCHAR2(1);
4200 language VARCHAR2(100);
4201 territory VARCHAR2(100);
4202 codeset VARCHAR2(100);
4203 orig_system VARCHAR2(100);
4204 orig_system_id number;
4205
4206 recipient_role VARCHAR2(100);
4207 status VARCHAR2(100);
4208 mail_status VARCHAR2(100);
4209 str varchar2 (2000);
4210
4211 role VARCHAR2(100);
4212 display_name VARCHAR2(200);
4213 email VARCHAR2(1000);
4214 notification_pref VARCHAR2(100);
4215
4216 attrlist wf_xml_attr_table_type;
4217
4218 nodename varchar2(100);
4219 messageType varchar2(8);
4220 messageName varchar2(30);
4221
4222 inAttr varchar2(1);
4223 resetNls varchar2(10);
4224
4225 begin
4226
4227 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
4228 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
4229 'wf.plsql.WF_XML.generateMessage', 'BEGIN');
4230 end if;
4231
4232 nid := to_number(p_event_key);
4233
4234 -- Obtain notification information
4235 begin -- 3741599 If there is no notification, exit gracefully
4236 select NVL(MORE_INFO_ROLE, RECIPIENT_ROLE), STATUS, MAIL_STATUS,
4237 MESSAGE_TYPE, MESSAGE_NAME
4238 into recipient_role, status, mail_status, messageType, messageName
4239 from WF_NOTIFICATIONS
4240 where NOTIFICATION_ID = nid;
4241 exception
4242 when no_data_found then
4243 str := '<?xml version="1.0" ?>';
4244 pos := length(str);
4245 dbms_lob.write(p_doc, pos, 1, str);
4246 nodename := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
4247 pCorrId => messageType,
4248 pName => 'NODENAME',
4249 pInAttr => inAttr);
4250
4251 AddElementAttribute('maxcount','1', attrlist);
4252 pos := NewLOBTag(p_doc, pos, 'NOTIFICATIONGROUP', '', attrlist);
4253 attrlist.DELETE;
4254
4255 -- 4104735 Empty documents now provide a document type and
4256 -- if a empty document, must provide a reason.
4257 AddElementAttribute('nid', nid, attrlist);
4258 AddElementAttribute('language', 'AMERICAN', attrlist);
4259 AddElementAttribute('territory', 'AMERICA', attrlist);
4260 AddElementAttribute('codeset', 'UTF8', attrlist);
4261
4262 -- Added below default base parameters.
4263 AddElementAttribute('nlsDateformat', wf_core.nls_date_format, attrlist);
4264 AddElementAttribute('nlsDateLanguage', wf_core.nls_date_language, attrlist);
4265 AddElementAttribute('nlsNumericCharacters', wf_core.nls_numeric_characters, attrlist);
4266 AddElementAttribute('nlsSort', wf_core.nls_sort, attrlist);
4267
4268 AddElementAttribute('full-document', 'N', attrlist);
4269 AddElementAttribute('reason', 'no_data_found', attrlist);
4270 pos := NewLOBTag(p_doc, pos, 'NOTIFICATION', '', attrlist);
4271 attrlist.DELETE;
4272
4273 return;
4274 when others then
4275 wf_core.context('WF_XML','GenerateMessage',to_char(nid));
4276 raise;
4277 end;
4278
4279 if (wf_mail.test_flag = TRUE) then
4280 mail_status := 'MAIL';
4281 if (status not in ('OPEN','CANCELED','CLOSED')) then
4282 status := 'OPEN';
4283 end if;
4284 end if;
4285
4286 if MAIL_STATUS is null or MAIL_STATUS not in ('MAIL','INVALID') then
4287
4288 str := '<?xml version="1.0" ?>';
4289 pos := length(str);
4290 dbms_lob.write(p_doc, pos, 1, str);
4291 nodename := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
4292 pCorrId => messageType,
4293 pName => 'NODENAME',
4294 pInAttr => inAttr);
4295
4296 AddElementAttribute('maxcount','1', attrlist);
4297 pos := NewLOBTag(p_doc, pos, 'NOTIFICATIONGROUP', '', attrlist);
4298 attrlist.DELETE;
4299
4300 -- 4104735 Empty documents now provide a document type and
4301 -- if a empty document, must provide a reason.
4302 AddElementAttribute('nid', nid, attrlist);
4303 AddElementAttribute('language', 'AMERICAN', attrlist);
4304 AddElementAttribute('territory', 'AMERICA', attrlist);
4305 AddElementAttribute('codeset', 'UTF8', attrlist);
4306
4307 -- Added below default base parameters.
4308 AddElementAttribute('nlsDateformat', wf_core.nls_date_format, attrlist);
4309 AddElementAttribute('nlsDateLanguage', wf_core.nls_date_language, attrlist);
4310 AddElementAttribute('nlsNumericCharacters', wf_core.nls_numeric_characters, attrlist);
4311 AddElementAttribute('nlsSort', wf_core.nls_sort, attrlist);
4312
4313 AddElementAttribute('full-document', 'N', attrlist);
4314 AddElementAttribute('reason', 'MAIL_STATUS:'||
4315 MAIL_STATUS, attrlist);
4316 pos := NewLOBTag(p_doc, pos, 'NOTIFICATION', '', attrlist);
4317 attrlist.DELETE;
4318
4319 return;
4320 end if;
4321
4322 -- <<sstomar>>:
4323 -- Call OLD API instead of WF_DIRECTORY.GetRoleInfoMail2
4324 -- here I think we are OK as we are not using other parameters.
4325 --
4326 -- Obtain recipient information
4327 WF_DIRECTORY.GetRoleInfoMail(recipient_role, display_name, email,
4328 notification_pref,
4329 language, territory,
4330 orig_system, orig_system_id, installed);
4331
4332 if email is not null then
4333 -- Email address is provided. process for one recipient
4334
4335 if notification_pref not in ('QUERY','SUMMARY','SUMHTML',
4336 'DISABLED') then
4337 str := '<?xml version="1.0" ?>';
4338 pos := length(str);
4339 dbms_lob.write(p_doc, pos, 1, str);
4340
4341 -- 4628088 Send the RESET_NLS flag if one is defined
4342 -- at the message level.
4343 resetNls := WF_MAILER_PARAMETER.GetValueForCorr (pNid => nid,
4344 pCorrId => messageType,
4345 pName => 'RESET_NLS',
4346 pInAttr => inAttr);
4347
4348 AddElementAttribute('maxcount','1', attrlist);
4349
4350 if(inAttr = 'Y') then
4351 AddElementAttribute('reset-nls',resetNls, attrlist);
4352 end if;
4353
4354 pos := NewLOBTag(p_doc, pos, 'NOTIFICATIONGROUP', '', attrlist);
4355 attrlist.DELETE;
4356
4357 -- generate XML payload
4358 generateDoc(p_doc, pos, recipient_role,
4359 p_event_name, p_event_key, p_parameter_list);
4360
4361 else
4362 -- This case should not be reached if the notification system
4363 -- is working correctly ie that a NULL is placed in the mail_status
4364 -- However, just in case, generate a blank NOTIFICATION element
4365 -- all the same.
4366
4367 str := '<?xml version="1.0" ?>';
4368 pos := length(str);
4369 dbms_lob.write(p_doc, pos, 1, str);
4370
4371 AddElementAttribute('maxcount','1', attrlist);
4372 pos := NewLOBTag(p_doc, pos, 'NOTIFICATIONGROUP', '', attrlist);
4373 attrlist.DELETE;
4374
4375 -- 4104735 Empty documents now provide a document type and
4376 -- if a empty document, must provide a reason.
4377 AddElementAttribute('nid', nid, attrlist);
4378 AddElementAttribute('language', 'AMERICAN', attrlist);
4379 AddElementAttribute('territory', 'AMERICA', attrlist);
4380 AddElementAttribute('codeset', 'UTF8', attrlist);
4381
4382 -- Added below default base parameters.
4383 AddElementAttribute('nlsDateformat', wf_core.nls_date_format, attrlist);
4384 AddElementAttribute('nlsDateLanguage', wf_core.nls_date_language, attrlist);
4385 AddElementAttribute('nlsNumericCharacters', wf_core.nls_numeric_characters, attrlist);
4386 AddElementAttribute('nlsSort', wf_core.nls_sort, attrlist);
4387
4388 AddElementAttribute('full-document', 'N', attrlist);
4389 AddElementAttribute('reason', 'NOTIFICATION_PREFERENCE:'||
4390 notification_pref, attrlist);
4391 pos := NewLOBTag(p_doc, pos, 'NOTIFICATION', '', attrlist);
4392 attrlist.DELETE;
4393 end if;
4394 else
4395 -- No email address is provided. Assume that this is a
4396 -- role with members.
4397 -- Failure to yeild members will result in a notification
4398 -- being generated to go to the system admin and the
4399 -- error process to be called.
4400 str := '<?xml version="1.0" ?>';
4401 pos := length(str);
4402 dbms_lob.write(p_doc, pos, 1, str);
4403 generateGroupDoc(p_doc, pos, recipient_role, notification_pref,
4404 orig_system, orig_system_id,
4405 p_event_name, p_event_key, p_parameter_list);
4406 end if;
4407
4408 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
4409 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
4410 'wf.plsql.WF_XML.generateMessage', 'END');
4411 end if;
4412 exception
4413 when others then
4414 wf_core.context('WF_XML','Generate',p_event_name, p_event_key);
4415 raise;
4416 end GenerateMessage;
4417
4418 -- GenerateSummaryDoc
4419 -- To generate the XML content for the OPEN notifications.
4420
4421 procedure GenerateSummaryDoc (p_doc in out NOCOPY CLOB,
4422 p_pos in out NOCOPY number,
4423 p_recipient_role in varchar2,
4424 p_event_name in varchar2,
4425 p_event_key in varchar2,
4426 p_parameter_list in wf_parameter_list_t)
4427 is
4428 pos integer;
4429 attrlist wf_xml_attr_table_type;
4430 occurance integer;
4431
4432 docType VARCHAR2(100);
4433
4434 recipient_role VARCHAR2(100);
4435 display_name VARCHAR2(200);
4436 email VARCHAR2(1000);
4437
4438 notification_pref VARCHAR2(100);
4439
4440 orig_system VARCHAR2(100);
4441 orig_system_id number;
4442 installed VARCHAR2(1);
4443
4444 l_nlsDateFormat VARCHAR2(120);
4445 l_nlsDateLanguage varchar2(120);
4446 l_nlsCalendar varchar2(120);
4447 l_nlsNumericCharacters varchar2(30);
4448 l_nlsSort varchar2(120);
4449 l_nlsCurrency varchar2(30);
4450
4451 l_nlsLanguage VARCHAR2(120);
4452 l_nlsTerritory VARCHAR2(120);
4453 l_nlsCodeset VARCHAR2(30);
4454
4455 l_context_changed BOOLEAN := false;
4456
4457 hdrxml varchar2(32000);
4458 hdrxmlPos integer;
4459
4460 corrId varchar2(128);
4461 nodename varchar2(100);
4462 fromName varchar2(100);
4463 replyto varchar2(100);
4464 subject VARCHAR2(4000);
4465
4466 msgbody VARCHAR2(32000);
4467 -- message CLOB;
4468 messageIdx pls_integer;
4469 l_lob VARCHAR2(1);
4470 resourceSrc varchar2(4000);
4471 l_renderBody varchar2(1);
4472 l_messageName varchar2(30);
4473
4474 begin
4475
4476 if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
4477 wf_log_pkg.string(wf_log_pkg.level_event,
4478 'wf.plsql.WF_XML.GenerateSummaryDoc',
4479 'BEGIN');
4480 end if;
4481
4482 recipient_role := p_recipient_role;
4483
4484 corrId := wf_event.getValueForParameter('Q_CORRELATION_ID',
4485 p_parameter_list);
4486 messageIdx := 0;
4487 pos := p_pos;
4488 occurance := 1;
4489
4490 -- Obtain recipient information
4491 --WF_DIRECTORY.GetRoleInfoMail(recipient_role, display_name, email,
4492 -- notification_pref,
4493 -- language, territory,
4494 -- orig_system, orig_system_id, installed);
4495
4496 -- Obtain recipient's NLS information too
4497 WF_DIRECTORY.GetRoleInfoMail2(recipient_role, display_name, email,
4498 notification_pref,
4499 orig_system,
4500 orig_system_id,
4501 installed,
4502 l_nlsLanguage ,
4503 l_nlsTerritory ,
4504 l_nlsDateFormat ,
4505 l_nlsDateLanguage ,
4506 l_nlsCalendar ,
4507 l_nlsNumericCharacters ,
4508 l_nlsSort ,
4509 l_nlsCurrency );
4510
4511
4512 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
4513 wf_log_pkg.string(wf_log_pkg.level_statement,
4514 'wf.plsql.WF_XML.GenerateSummaryDoc',
4515 'ROLE {'||recipient_role||'} LANG {'||l_nlsLanguage||
4516 '} TERR {'||l_nlsTerritory||' NTF {'||notification_pref||
4517 '} EMAIL {'||email||'}');
4518
4519 end if;
4520
4521 -- XX TODO Sentinal for DISABLED case? Though there is no NID to be
4522 -- updated, can be used as documentation in the log
4523 if notification_pref not in ('SUMMARY','SUMHTML') or
4524 email is null then
4525 if (wf_log_pkg.level_exception >= fnd_log.g_current_runtime_level) then
4526 if email is null then
4527 wf_log_pkg.string(wf_log_pkg.level_exception,
4528 'wf.plsql.WF_XML.GenerateSummaryDoc',
4529 'Not generating. Recipient has a null '||
4530 'email address');
4531 else
4532 wf_log_pkg.string(wf_log_pkg.level_exception,
4533 'wf.plsql.WF_XML.GenerateSummaryDoc',
4534 'Not generating. Recipient has pref: '||
4535 notification_pref);
4536 end if;
4537 end if;
4538
4539 AddElementAttribute('nid', '0', attrlist);
4540 AddElementAttribute('language', 'AMERICAN', attrlist);
4541 AddElementAttribute('territory', 'AMERICA', attrlist);
4542 AddElementAttribute('codeset', 'UTF8', attrlist);
4543
4544 -- set values of attributes as notification is NOT to be sent
4545 -- Added below default base parameters.
4546 AddElementAttribute('nlsDateformat', wf_core.nls_date_format, attrlist);
4547 AddElementAttribute('nlsDateLanguage', wf_core.nls_date_language, attrlist);
4548
4549 AddElementAttribute('nlsNumericCharacters', wf_core.nls_numeric_characters, attrlist);
4550 AddElementAttribute('nlsSort', wf_core.nls_sort, attrlist);
4551 AddElementAttribute('full-document', 'N', attrlist);
4552
4553 AddElementAttribute('reason', 'NOTIFICATION_PREFERENCE:'||
4554 notification_pref, attrlist);
4555 p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATION', '', attrlist);
4556 attrlist.DELETE;
4557 p_pos := SkipLOBTag(p_doc, 'NOTIFICATION', p_pos, occurance);
4558
4559 return;
4560
4561 end if;
4562
4563 -- Set the preferred document type based on the
4564 -- notification preference.
4565 if g_install = 'EMBEDDED' then
4566 l_renderBody := 'N';
4567 l_messageName := 'SUMHTML';
4568 if notification_pref = 'SUMMARY' then
4569 docType := g_ntfDocText;
4570 elsif notification_pref = 'SUMHTML' then
4571 docType := g_ntfDocHtml;
4572 end if;
4573 else
4574 l_renderBody := 'Y';
4575 l_messageName := 'SUMMARY';
4576 docType := g_ntfDocText;
4577 end if;
4578
4579 -- Get the Base NLS.
4580 -- << sstomar>> : already being set in GENERATE function
4581 -- WF_MAIL.GetSessionLanguage(base_lang, base_territory, base_codeset);
4582
4583 -- If requested language is not installed, use base NLS setting
4584 if installed = 'N' then
4585 l_nlsLanguage := g_base_language;
4586 l_nlsTerritory := g_base_territory;
4587 end if;
4588 begin
4589 select nls_codeset
4590 into l_nlsCodeset
4591 from wf_languages
4592 where nls_language = l_nlsLanguage;
4593 exception
4594 when others then
4595 l_nlsCodeset := g_base_codeset;
4596 end;
4597
4598 -- <<sstomar>> :
4599 --
4600 -- setNLS(language, territory);
4601 -- Set user / role's context
4602 if( nvl(l_nlsLanguage, 'AMERICA') <> g_base_language or
4603 nvl(l_nlsTerritory, 'AMERICAN') <> g_base_territory or
4604 nvl(l_nlsDateFormat , wf_core.nls_date_format) <> g_base_nlsDateFormat or
4605 nvl(l_nlsDateLanguage, wf_core.nls_date_language) <> g_base_nlsDateLanguage or
4606 nvl(l_nlsNumericCharacters, wf_core.nls_numeric_characters) <> g_base_nlsNumericCharacters or
4607 nvl(l_nlsSort, wf_core.nls_sort) <> g_base_nlsSort ) then
4608
4609
4610 WF_NOTIFICATION_UTIL.SetNLSContext(0,
4611 l_nlsLanguage ,
4612 l_nlsTerritory ,
4613 l_nlsDateFormat ,
4614 l_nlsDateLanguage ,
4615 l_nlsNumericCharacters ,
4616 l_nlsSort ,
4617 l_nlsCalendar);
4618
4619 l_context_changed := true;
4620
4621
4622 END if;
4623
4624 -- Initialise the XML Document and then progressively walk
4625 -- through the elements. Populating them as we go.
4626 -- l_pos is crucial as it determines where the next nodes
4627 -- will be placed.
4628
4629 l_lob := 'N';
4630
4631 -- <<sstomar>>:
4632 -- This WF_MAIL.GetSummary2 API may allocate temp LOB depending on
4633 -- size of contents. We release within getBodyPart api, getBodyPart
4634 -- is being called below...
4635 WF_MAIL.GetSummary2(
4636 role => recipient_role,
4637 dname => display_name,
4638 node => nodename,
4639 renderBody=> l_renderBody,
4640 contType => docType,
4641 subject => subject,
4642 body_text => msgbody,
4643 lob => l_lob);
4644
4645 --SetNLS(base_lang, base_territory);
4646
4647 -- Reset base NLS Context
4648 if( l_context_changed ) then
4649
4650 WF_NOTIFICATION_UTIL.SetNLSContext(
4651 null,
4652 g_base_language ,
4653 g_base_territory ,
4654 g_base_nlsDateFormat ,
4655 g_base_nlsDateLanguage ,
4656 g_base_nlsNumericCharacters ,
4657 g_base_nlsSort ,
4658 g_base_nlsCalendar );
4659 END if;
4660
4661
4662 -- NID hardcoded to 0 for Summary
4663 AddElementAttribute('nid', '0', attrlist);
4664
4665 AddElementAttribute('language', l_nlsLanguage, attrlist);
4666 AddElementAttribute('territory', l_nlsTerritory, attrlist);
4667 AddElementAttribute('codeset', l_nlsCodeset, attrlist);
4668
4669 AddElementAttribute('nlsDateformat', l_nlsDateFormat, attrlist);
4670 AddElementAttribute('nlsDateLanguage', l_nlsDateLanguage, attrlist);
4671
4672 AddElementAttribute('nlsNumericCharacters', l_nlsNumericCharacters, attrlist);
4673 AddElementAttribute('nlsSort', l_nlsSort, attrlist);
4674
4675 -- priority hardcoded to 50
4676 AddElementAttribute('priority', '50', attrlist);
4677 -- Not addding accesskey as no response processing is done for summary email
4678 AddElementAttribute('node', nodename, attrlist);
4679 -- ItemType / messageType hardcoded to WFMAIL
4680 AddElementAttribute('item_type', 'WFMAIL', attrlist);
4681 AddElementAttribute('message_name', l_messageName, attrlist);
4682 AddElementAttribute('full-document', 'Y', attrlist);
4683 pos := NewLOBTag(p_doc, pos, 'NOTIFICATION', '', attrlist);
4684 attrlist.DELETE;
4685
4686 hdrxmlPos := 0;
4687 hdrxml := '';
4688
4689 hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'RECIPIENTLIST', '', attrlist);
4690
4691 addRecipient(p_doc => hdrxml,
4692 p_role => recipient_role,
4693 p_type => 'to',
4694 p_name => display_name,
4695 p_email => email,
4696 p_pos => hdrxmlPos);
4697
4698 hdrxmlPos := SkipTag(hdrxml, 'RECIPIENTLIST', hdrxmlPos, occurance);
4699
4700 hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'FROM', '', attrlist);
4701
4702 -- fromName := '&#FROM';
4703 fromName := WF_MAILER_PARAMETER.GetValueForCorr (pCorrId => corrId,
4704 pName => 'FROM');
4705 fromName := replace(fromName, g_newLine);
4706 fromName := '<![CDATA['||fromName||']]>';
4707 hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'NAME', fromName, attrlist);
4708 hdrxmlPos := SkipTag(hdrxml, 'NAME', hdrxmlPos, occurance);
4709
4710 -- replyto := '&#REPLYTO';
4711 replyto := WF_MAILER_PARAMETER.GetValueForCorr (pCorrId => corrId,
4712 pName => 'REPLYTO');
4713 replyto := replace(replyto, g_newLine);
4714 replyto := '<![CDATA['||replyto||']]>';
4715 hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'ADDRESS', replyto, attrlist);
4716 hdrxmlPos := SkipTag(hdrxml, 'ADDRESS', hdrxmlPos, occurance);
4717 hdrxmlPos := SkipTag(hdrxml, 'FROM', hdrxmlPos, occurance);
4718
4719 -- Not addding status as not needed for summary email
4720
4721 subject := replace(subject, g_newLine);
4722 subject := '<![CDATA['||subject||']]>';
4723 hdrxmlPos := NewTag(hdrxml, hdrxmlPos, 'SUBJECT', subject, attrlist);
4724
4725 pos := NewLOBTag(p_doc, pos, 'HEADER', hdrxml, attrlist);
4726 pos := SkipLOBTag(p_doc, 'HEADER', pos, occurance);
4727
4728 -- <sstomar bug 6993909>>
4729 -- So far I did not see any issue with SUMHTML
4730 -- If required then outer structure can be set multipart/related
4731 -- for SUMHTML, text/plain for SUMMARY.
4732 AddElementAttribute('content-type', 'multipart/mixed', attrlist);
4733 pos := NewLOBTag(p_doc, pos, 'CONTENT', '', attrlist);
4734 attrlist.DELETE;
4735
4736 /*
4737 ** The body part section of the XML structure will contain
4738 ** a <![CDATA ]]> construct which removes the need to URL
4739 ** encode the data.
4740 */
4741 if (l_renderBody = 'Y') then
4742 AddElementAttribute('content-type', 'text/plain', attrlist);
4743 else
4744 AddElementAttribute('content-type', 'multipart/related', attrlist);
4745 end if;
4746 pos := NewLOBTag(p_doc, pos, 'BODYPART', '', attrlist);
4747 attrlist.DELETE;
4748
4749 -- Set the content-type of the MESSAGE tag to being the
4750 -- preferred output.
4751 AddElementAttribute('content-type', docType, attrlist);
4752 if (l_lob = 'Y') then
4753
4754 -- dbms_lob.createTemporary(message, TRUE, dbms_lob.CALL);
4755 messageIdx := wf_temp_lob.getLob(g_LOBTable);
4756 dbms_lob.trim(g_LOBTable(messageIdx).temp_lob, 0);
4757
4758 -- << bug 6511028 >>
4759 -- This getBodyPart API also releases temp lob allocated within
4760 -- WF_MAIL.GetSummary2 for doctype (text/plain )
4761 -- i.e. g_text_messageIdx locator
4762 getBodyPart(g_LOBTable(messageIdx).temp_lob, 1,
4763 g_ntfDocText);
4764
4765 pos := NewLOBTag(p_doc, pos, 'MESSAGE',
4766 g_LOBTable(messageIdx).temp_lob, attrlist);
4767 -- release Temp lob to pool.
4768 wf_temp_lob.releaseLob(g_LOBTable, messageIdx);
4769
4770 else
4771
4772 msgbody := '<![CDATA['||msgbody||']]>';
4773
4774 pos := NewLOBTag(p_doc, pos, 'MESSAGE', msgbody, attrlist);
4775
4776 end if;
4777
4778 pos := SkipLOBTag(p_doc, 'MESSAGE', pos, occurance);
4779 if l_renderBody = 'N' then
4780 -- If this is a HTML summary notification then
4781 -- create a RESOURCE tag to obtain the content from
4782 -- the Applications Framework
4783 resourceSrc := wf_mail.urlEncode(wf_notification.getSummaryUrl2(
4784 recipient_role, docType, l_nlsCalendar));
4785 AddElementAttribute('page-type','fwk', attrlist);
4786 AddElementAttribute('src', resourceSrc, attrlist);
4787
4788 -- << sstomar>> : these attribute may not be used at java layer, insead of
4789 -- Notification level attributes would be used,
4790 AddElementAttribute('language', l_nlsLanguage, attrlist);
4791 AddElementAttribute('territory', l_nlsTerritory, attrlist);
4792 AddElementAttribute('token', 'SUMMARY', attrlist);
4793 pos := NewLOBTag(p_doc, pos, 'RESOURCE', '', attrlist);
4794 pos := SkipLOBTag(p_doc, 'RESOURCE', pos, occurance);
4795 attrList.DELETE;
4796 end if;
4797 pos := SkipLOBTag(p_doc, 'BODYPART', pos, occurance);
4798 attrlist.DELETE;
4799
4800 pos := SkipLOBTag(p_doc, 'CONTENT', pos, occurance);
4801 pos := SkipLOBTag(p_doc, 'NOTIFICATION', pos, occurance);
4802
4803 p_pos := pos;
4804
4805 if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
4806 wf_log_pkg.string(wf_log_pkg.level_event,
4807 'wf.plsql.WF_XML.GenerateSummaryDoc',
4808 'END');
4809 end if;
4810
4811 exception
4812 when others then
4813 if (messageIdx > 0) then
4814 wf_temp_lob.releaseLob(g_LOBTable, messageIdx);
4815 end if;
4816
4817 -- Reset base NLS Context in case of any EXCEPTION
4818 WF_NOTIFICATION_UTIL.SetNLSContext(
4819 null,
4820 g_base_language ,
4821 g_base_territory ,
4822 g_base_nlsDateFormat ,
4823 g_base_nlsDateLanguage ,
4824 g_base_nlsNumericCharacters ,
4825 g_base_nlsSort ,
4826 g_base_nlsCalendar );
4827
4828
4829 wf_core.context('WF_XML', 'GenerateSummaryDoc', p_event_name,
4830 p_event_key);
4831 raise;
4832 end GenerateSummaryDoc;
4833
4834 -- Generate
4835 procedure GenerateGroupSummaryDoc (p_doc in out NOCOPY CLOB,
4836 p_pos in out NOCOPY number,
4837 p_recipient_role in varchar2,
4838 p_orig_system in varchar2,
4839 p_orig_system_id in number,
4840 p_event_name in varchar2,
4841 p_event_key in varchar2,
4842 p_parameter_list in wf_parameter_list_t)
4843 is
4844
4845 cursor members(rname varchar2, rorig varchar2, rorigid number) is
4846 select UR.USER_NAME, UR.USER_ORIG_SYSTEM, UR.USER_ORIG_SYSTEM_ID
4847 from WF_USER_ROLES UR
4848 where UR.ROLE_NAME = rname
4849 and UR.ROLE_ORIG_SYSTEM = rorig
4850 and UR.ROLE_ORIG_SYSTEM_ID = rorigid
4851 and ((UR.USER_NAME <> UR.ROLE_NAME) or
4852 (UR.USER_ORIG_SYSTEM <> UR.ROLE_ORIG_SYSTEM and
4853 UR.USER_ORIG_SYSTEM_ID <> UR.ROLE_ORIG_SYSTEM_ID));
4854
4855 members_type members%ROWTYPE;
4856 TYPE members_table_type is TABLE OF members%ROWTYPE
4857 INDEX BY BINARY_INTEGER;
4858
4859 i pls_integer := 1;
4860
4861 members_t members_table_type;
4862 attrlist wf_xml_attr_table_type;
4863
4864 begin
4865
4866 if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
4867 wf_log_pkg.string(wf_log_pkg.level_event,
4868 'wf.plsql.WF_XML.GenerateGroupSummaryDoc',
4869 'BEGIN');
4870 end if;
4871
4872 i := 1;
4873 for r in members(p_recipient_role, p_orig_system, p_orig_system_id) loop
4874 members_t(i).user_name := r.user_name;
4875 members_t(i).user_orig_system:= r.user_orig_system;
4876 members_t(i).user_orig_system_id:= r.user_orig_system_id;
4877 i := i + 1;
4878 end loop;
4879
4880 if members_t.count = 0 then
4881 if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
4882 wf_log_pkg.string(wf_log_pkg.level_event,
4883 'wf.plsql.WF_XML.GenerateGroupSummaryDoc',
4884 'No role members.');
4885 end if;
4886
4887 AddElementAttribute('maxcount', '1', attrlist);
4888 p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATIONGROUP', '', attrlist);
4889 attrlist.DELETE;
4890
4891 -- 4104735 Empty documents now provide a document type and
4892 -- if a empty document, must provide a reason.
4893 AddElementAttribute('nid', '0', attrlist);
4894 AddElementAttribute('language', 'AMERICAN', attrlist);
4895 AddElementAttribute('territory', 'AMERICA', attrlist);
4896 AddElementAttribute('codeset', 'UTF8', attrlist);
4897
4898 -- Added below default base parameters.
4899 AddElementAttribute('nlsDateformat', wf_core.nls_date_format, attrlist);
4900 AddElementAttribute('nlsDateLanguage', wf_core.nls_date_language, attrlist);
4901
4902 AddElementAttribute('nlsNumericCharacters', wf_core.nls_numeric_characters, attrlist);
4903 AddElementAttribute('nlsSort', wf_core.nls_sort, attrlist);
4904
4905 AddElementAttribute('full-document', 'N', attrlist);
4906 AddElementAttribute('reason', 'no_members', attrlist);
4907 p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATION', '', attrlist);
4908 attrlist.DELETE;
4909 else
4910
4911 AddElementAttribute('maxcount',to_char(members_t.count), attrlist);
4912 p_pos := NewLOBTag(p_doc, p_pos, 'NOTIFICATIONGROUP', '', attrlist);
4913 attrlist.DELETE;
4914
4915 for i in 1..members_t.count loop
4916 GenerateSummaryDoc (p_doc, p_pos, members_t(i).user_name,
4917 p_event_name, p_event_key, p_parameter_list);
4918 end loop;
4919 end if;
4920
4921 if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
4922 wf_log_pkg.string(wf_log_pkg.level_event,
4923 'wf.plsql.WF_XML.GenerateGroupSummaryDoc',
4924 'END');
4925 end if;
4926
4927 exception
4928 when others then
4929 wf_core.context('WF_XML','GenerateGroupSummaryDoc ',p_event_name,
4930 p_event_key);
4931 raise;
4932 end GenerateGroupSummaryDoc ;
4933
4934
4935 -- GenerateSummary
4936 -- To generate the XML content for the enqueued Summary notifications.
4937 procedure GenerateSummary (p_doc in out nocopy CLOB,
4938 p_event_name in varchar2,
4939 p_event_key in varchar2,
4940 p_parameter_list in wf_parameter_list_t)
4941 is
4942 pos integer;
4943
4944 attrlist wf_xml_attr_table_type;
4945 str varchar2 (2000);
4946
4947 recipient_role VARCHAR2(100);
4948
4949 display_name VARCHAR2(200);
4950 email VARCHAR2(1000);
4951 notification_pref VARCHAR2(100);
4952 language VARCHAR2(100);
4953 territory VARCHAR2(100);
4954 orig_system VARCHAR2(100);
4955 orig_system_id number;
4956 installed VARCHAR2(1);
4957
4958 e_RoleNameNotSpecified EXCEPTION;
4959
4960 begin
4961
4962 if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
4963 wf_log_pkg.string(wf_log_pkg.level_event,
4964 'wf.plsql.WF_XML.GenerateSummary',
4965 'BEGIN');
4966 end if;
4967
4968 if p_parameter_list is not null then
4969 recipient_role := wf_event.getValueForParameter('ROLE_NAME',
4970 p_parameter_list);
4971 end if;
4972
4973 if recipient_role is null then
4974 raise e_RoleNameNotSpecified;
4975 end if;
4976
4977 -- Initialise the XML Document and then progressively walk
4978 -- through the elements. Populating them as we go.
4979 -- l_pos is crucial as it determines where the next nodes
4980 -- will be placed.
4981 str := '<?xml version="1.0" ?>';
4982 pos := length(str);
4983 dbms_lob.write(p_doc, pos, 1, str);
4984
4985 -- Obtain recipient information
4986 WF_DIRECTORY.GetRoleInfoMail(recipient_role, display_name, email,
4987 notification_pref,
4988 language, territory,
4989 orig_system, orig_system_id, installed);
4990
4991 if email is not null or email <> '' then
4992
4993 AddElementAttribute('maxcount','1', attrlist);
4994 pos := NewLOBTag(p_doc, pos, 'NOTIFICATIONGROUP', '', attrlist);
4995 attrlist.DELETE;
4996 -- Email address is provided. process for one recipient
4997
4998 GenerateSummaryDoc (p_doc, pos, recipient_role, p_event_name,
4999 p_event_key, p_parameter_list);
5000
5001 else
5002
5003 -- No email address is provided. Assume that this is a
5004 -- role with members.
5005 -- Failure to yeild members will result in a notification
5006 -- being generated to go to the system admin and the
5007 -- error process to be called.
5008
5009 GenerateGroupSummaryDoc (p_doc, pos, recipient_role, orig_system,
5010 orig_system_id, p_event_name, p_event_key,
5011 p_parameter_list);
5012
5013 end if;
5014
5015 if (wf_log_pkg.level_event >= fnd_log.g_current_runtime_level) then
5016 wf_log_pkg.string(wf_log_pkg.level_event,
5017 'wf.plsql.WF_XML.GenerateSummary',
5018 'END');
5019 end if;
5020 exception
5021
5022 when e_RoleNameNotSpecified then
5023 wf_core.context('WF_XML','GenerateSummary',p_event_name, p_event_key);
5024 raise;
5025
5026 when others then
5027 wf_core.context('WF_XML','GenerateSummary',p_event_name, p_event_key);
5028 raise;
5029 end GenerateSummary;
5030
5031 -- Generate
5032 -- To generate the XML content for the enqueued notifications.
5033 function Generate(p_event_name in varchar2,
5034 p_event_key in varchar2,
5035 p_parameter_list in wf_parameter_list_t)
5036 return clob
5037 is
5038 l_doc CLOB;
5039 l_evt wf_event_t;
5040 l_parameters wf_parameter_list_t;
5041 l_erragt wf_agent_t;
5042
5043 begin
5044 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
5045 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
5046 'wf.plsql.WF_XML.generate', 'BEGIN');
5047 end if;
5048 -- We do not use the LOB pooling for the generate as the LOB
5049 -- is to be returned and not reused.
5050 dbms_lob.createTemporary(l_doc, TRUE, dbms_lob.call);
5051
5052
5053 BEGIN
5054
5055 -- <<sstomar>>:
5056 -- Since base NLS parameters / context would be used in subsequent methods
5057 -- So better to set them here.
5058 --
5059 if(g_base_language is null or
5060 g_base_territory is null or
5061 g_base_codeset is NULL OR
5062 g_base_nlsDateFormat is NULL OR
5063 g_base_nlsDateLanguage is NULL OR
5064 g_base_nlsNumericCharacters is NULL OR
5065 g_base_nlsSort is NULL
5066 ) then
5067
5068 WF_NOTIFICATION_UTIL.getNLSContext(
5069 g_base_language ,
5070 g_base_territory ,
5071 g_base_codeset ,
5072 g_base_nlsDateFormat ,
5073 g_base_nlsDateLanguage ,
5074 g_base_nlsNumericCharacters ,
5075 g_base_nlsSort ,
5076 g_base_nlsCalendar );
5077
5078
5079
5080 end if;
5081
5082 -- <sstomar> bug 7130745 : added Question / Answere events
5083 if p_event_name in (wf_xml.WF_NTF_SEND_MESSAGE,
5084 wf_xml.WF_NTF_CANCEL,
5085 wf_xml.WF_NTF_REASIGN,
5086 wf_xml.WF_NTF_SEND_QUESTION,
5087 wf_xml.WF_NTF_SEND_ANSWER ) then
5088
5089 GenerateMessage(l_doc, p_event_name, p_event_key,
5090 p_parameter_list);
5091
5092 elsif p_event_name = WF_NTF_SEND_SUMMARY then
5093
5094
5095 -- Summary events.
5096 GenerateSummary (l_doc, p_event_name, p_event_key, p_parameter_list);
5097
5098 end if;
5099 exception
5100 when others then
5101 wf_core.context('WF_XML','Generate',p_event_name, p_event_key);
5102
5103 -- RESET BASSE LANGUAGE
5104 WF_NOTIFICATION_UTIL.SetNLSContext(null,
5105 g_base_language ,
5106 g_base_territory ,
5107 g_base_nlsDateFormat ,
5108 g_base_nlsDateLanguage ,
5109 g_base_nlsNumericCharacters ,
5110 g_base_nlsSort ,
5111 g_base_nlsCalendar );
5112
5113
5114 raise;
5115 end;
5116
5117 if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
5118 wf_log_pkg.string(WF_LOG_PKG.level_procedure,
5119 'wf.plsql.WF_XML.generate', 'END');
5120 end if;
5121 return l_doc;
5122
5123 end Generate;
5124
5125
5126 -- notificationIsOpen
5127 -- Return the current status of the notification
5128 function notificationIsOpen(nid in number) return boolean
5129 is
5130 l_open integer := 0;
5131 begin
5132 begin
5133 select 1
5134 into l_open
5135 from wf_notifications
5136 where notification_id = nid
5137 and status = 'OPEN';
5138 exception
5139 when others then l_open := 0;
5140 end;
5141 return l_open > 0;
5142 end notificationIsOpen;
5143
5144 -- GetResponseDetails
5145 -- Gets the response details from the incoming XML Notifiction
5146 -- structure.
5147 --
5148 -- IN
5149 -- message - The XML Notification structure containing the
5150 -- inbound response
5151 procedure getResponseDetails(message in CLOB)
5152 IS
5153
5154 l_node varchar2(4000);
5155 l_version integer;
5156 l_from_role varchar2(4000);
5157 l_responses wf_responseList_t;
5158
5159 begin
5160
5161 getResponseDetails(message => message,
5162 node => l_node,
5163 version => l_version,
5164 fromRole => l_from_role,
5165 responses => l_responses);
5166
5167 end getResponseDetails;
5168
5169 -- GetResponseDetails
5170 -- Gets the response details from the incoming XML Notifiction
5171 -- structure.
5172 --
5173 -- IN
5174 -- message - The XML Notification structure containing the
5175 -- inbound response
5176 procedure getResponseDetails(message in CLOB, node out NOCOPY varchar2,
5177 version out NOCOPY integer,
5178 fromRole out NOCOPY varchar2,
5179 responses in out NOCOPY wf_responseList_t)
5180 IS
5181
5182 p xmlparser.parser;
5183 doc xmldom.DOMDocument;
5184
5185 nl xmldom.DOMNodeList;
5186 len1 number;
5187 len2 number;
5188 n xmldom.DOMNode;
5189 m xmldom.DOMNode;
5190 nnm xmldom.DOMNamedNodeMap;
5191
5192 fromAddress varchar2(4000);
5193 attrname varchar2(4000);
5194 attrval varchar2(4000);
5195
5196 node_data varchar2(32000);
5197 node_name varchar2(4000);
5198 from_node_found boolean;
5199
5200 attribute_found boolean;
5201 response_count number;
5202
5203 response_attr_name varchar2(4000);
5204 response_attr_val varchar2(32000);
5205 response_attr_type varchar2(4000);
5206 response_attr_format varchar2(4000);
5207
5208 step varchar2(200);
5209
5210 begin
5211
5212 from_node_found := FALSE;
5213 attribute_found := FALSE;
5214 response_count := 0;
5215 responses.delete;
5216 node := '';
5217 fromRole := '';
5218
5219 -- new parser
5220 p := xmlparser.newParser;
5221
5222 -- set some characteristics
5223 xmlparser.setValidationMode(p, FALSE);
5224
5225 -- parse input file
5226 xmlparser.parseClob(p, message);
5227
5228 -- get document
5229 doc := xmlparser.getDocument(p);
5230
5231 -- get all elements
5232 nl := xmldom.getElementsByTagName(doc, '*');
5233 len1 := xmldom.getLength(nl);
5234
5235 -- loop through elements
5236 for j in 0..len1-1 loop
5237
5238 n := xmldom.item(nl, j);
5239 node_name := xmldom.getNodeName(n);
5240
5241 step := 'Processing ['||node_name||']';
5242 if node_name = 'NOTIFICATION' then
5243
5244 -- get all attributes of element
5245 nnm := xmldom.getAttributes(n);
5246
5247 if (xmldom.isNull(nnm) = FALSE) then
5248
5249 len2 := xmldom.getLength(nnm);
5250
5251 -- loop through attributes
5252 for i in 0..len2-1 loop
5253
5254 m := xmldom.item(nnm, i);
5255 attrname := xmldom.getNodeName(m);
5256
5257 step := 'Processing ['||node_name||'] ['||attrname||']';
5258
5259 if attrname = 'node' then
5260
5261 attrval := xmldom.getNodeValue(m);
5262 node := attrval;
5263 elsif attrname = 'version' then
5264
5265 attrval := xmldom.getNodeValue(m);
5266 begin
5267 version := to_number(attrval);
5268 exception
5269 when others then
5270 version := 0;
5271 end;
5272 end if;
5273
5274 end loop;
5275
5276
5277 end if;
5278
5279 elsif node_name = 'FROM' then
5280
5281 from_node_found := TRUE;
5282
5283 elsif node_name = 'NAME' then
5284
5285 if from_node_found then
5286
5287 n := xmldom.getFirstChild(n);
5288
5289 if ((not xmldom.isNull(n))
5290 and (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then
5291
5292 node_data := xmlDom.getNodeValue(n);
5293 fromRole := node_data;
5294
5295 end if;
5296
5297 end if;
5298
5299 elsif node_name = 'ADDRESS' then
5300
5301 if from_node_found then
5302
5303 from_node_found := FALSE;
5304
5305 n := xmldom.getFirstChild(n);
5306
5307 if ((not xmldom.isNull(n))
5308 and (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then
5309
5310 node_data := xmlDom.getNodeValue(n);
5311 fromAddress := node_data;
5312
5313 fromRole := '"'||fromRole||'" <'||fromAddress||'>';
5314
5315 end if;
5316
5317 end if;
5318
5319 elsif node_name = 'ATTRIBUTE' then
5320
5321 -- get all attributes of element
5322 nnm := xmldom.getAttributes(n);
5323
5324 if (xmldom.isNull(nnm) = FALSE) then
5325
5326 attribute_found := FALSE;
5327 response_attr_name := '';
5328 response_attr_val := '';
5329 response_attr_type := '';
5330 response_attr_format := '';
5331
5332 len2 := xmldom.getLength(nnm);
5333
5334 -- loop through attributes
5335 for i in 0..len2-1 loop
5336
5337 m := xmldom.item(nnm, i);
5338 attrname := xmldom.getNodeName(m);
5339
5340 step := 'Processing ['||node_name||'] ['||attrname||']';
5341
5342 if attrname = 'name' then
5343
5344 attribute_found := TRUE;
5345 attrval := xmldom.getNodeValue(m);
5346 response_attr_name := attrval;
5347
5348 elsif attrname = 'type' then
5349
5350 attrval := xmldom.getNodeValue(m);
5351 response_attr_type := attrval;
5352
5353 elsif attrname = 'format' then
5354
5355 attrval := xmldom.getNodeValue(m);
5356 response_attr_format := attrval;
5357
5358 end if;
5359
5360 end loop;
5361
5362 if attribute_found then
5363
5364 n := xmldom.getFirstChild(n);
5365
5366 if ((not xmldom.isNull(n)) and
5367 (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then
5368
5369 node_data := substrb(xmlDom.getNodeValue(n), 1, 32000);
5370 response_attr_val := node_data;
5371 end if;
5372
5373 response_count := response_count + 1;
5374 responses(response_count).NAME := response_attr_name;
5375 responses(response_count).TYPE := response_attr_type;
5376 responses(response_count).FORMAT := response_attr_format;
5377 responses(response_count).VALUE := response_attr_val;
5378
5379
5380 end if;
5381
5382 end if;
5383
5384 end if;
5385 step := 'Fishished {'||step||'}';
5386
5387 end loop;
5388
5389 if (not xmldom.isNull(doc)) then
5390 xmldom.freeDocument (doc);
5391 end if;
5392
5393 xmlparser.freeParser (p);
5394
5395 exception
5396
5397 when xmldom.INDEX_SIZE_ERR then
5398 wf_core.context('WF_XML','getResponseDetails', step);
5399 raise;
5400
5401 when xmldom.DOMSTRING_SIZE_ERR then
5402 wf_core.context('WF_XML','getResponseDetails', step);
5403 raise;
5404
5405 when xmldom.HIERARCHY_REQUEST_ERR then
5406 wf_core.context('WF_XML','getResponseDetails', step);
5407 raise;
5408
5409 when xmldom.WRONG_DOCUMENT_ERR then
5410 wf_core.context('WF_XML','getResponseDetails', step);
5411 raise;
5412
5413 when xmldom.INVALID_CHARACTER_ERR then
5414 wf_core.context('WF_XML','getResponseDetails', step);
5415 raise;
5416
5417 when xmldom.NO_DATA_ALLOWED_ERR then
5418 wf_core.context('WF_XML','getResponseDetails', step);
5419 raise;
5420
5421 when xmldom.NO_MODIFICATION_ALLOWED_ERR then
5422 wf_core.context('WF_XML','getResponseDetails', step);
5423 raise;
5424
5425 when xmldom.NOT_FOUND_ERR then
5426 wf_core.context('WF_XML','getResponseDetails', step);
5427 raise;
5428
5429 when xmldom.NOT_SUPPORTED_ERR then
5430 wf_core.context('WF_XML','getResponseDetails', step);
5431 raise;
5432
5433 when xmldom.INUSE_ATTRIBUTE_ERR then
5434 wf_core.context('WF_XML','getResponseDetails', step);
5435 raise;
5436
5437 when others then
5438 wf_core.context('WF_XML','getResponseDetails', step);
5439 raise;
5440
5441 end getResponseDetails;
5442
5443
5444 -- sendNotification
5445 -- This API is a wrapper to the wf_xml.enqueueNotification. It is provided
5446 -- as forward compatabilty for the original mailer since the call to
5447 -- wf_xml.enqueueNotification has been removed from
5448 -- wf_notification.sendSingle.
5449 -- To use the original mailer, one must enable the subscription that will
5450 -- call this rule function.
5451 -- IN
5452 -- p_subscription
5453 -- p_event
5454 -- RETURN
5455 -- varchar2 of the status
5456 function SendNotification (p_subscription_guid in raw,
5457 p_event in out NOCOPY WF_EVENT_T) return varchar2
5458 is
5459 l_eventName varchar2(80);
5460 l_eventkey varchar(80);
5461 l_nid number;
5462 begin
5463 l_eventkey := p_event.GetEventKey();
5464 l_nid := to_number(l_eventKey);
5465 l_eventName := p_event.GetEventName();
5466 if l_eventName in (wf_xml.WF_NTF_SEND_MESSAGE, wf_xml.WF_NTF_CANCEL,
5467 wf_xml.WF_NTF_REASIGN) then
5468 wf_xml.enqueueNotification(l_nid);
5469 else
5470 return wf_rule.default_rule(p_subscription_guid, p_event);
5471 end if;
5472 return 'SUCCESS';
5473 exception
5474 when others then
5475 wf_core.Context('WF_XML','SendNotification',p_event.getEventName(),
5476 p_subscription_guid);
5477 -- Save error message and set status to INVALID so mailer will
5478 -- bounce an "invalid reply" message to sender.
5479 wf_event.SetErrorInfo(p_event, 'ERROR');
5480 return 'ERROR';
5481 end sendNotification;
5482
5483
5484 -- handleRecevieEvent
5485 --
5486 function handleReceiveEvent(p_subscription_guid in raw,
5487 p_event in out NOCOPY WF_EVENT_T) return varchar2
5488 is
5489 l_eventName varchar2(80);
5490 l_eventkey varchar(80);
5491 l_paramlist wf_parameter_list_t;
5492 l_eventData CLOB;
5493 l_node varchar2(30);
5494 l_version integer;
5495 l_user varchar2(320);
5496 l_comment varchar2(4000);
5497 l_fromAddr varchar2(2000);
5498 l_nid number;
5499 l_template varchar2(30);
5500 l_module varchar2(200);
5501 l_error varchar2(2000);
5502 i int;
5503 l_responses wf_responseList_t;
5504 lk_type varchar2(240);
5505 lk_code varchar2(4000);
5506 lk_meaning varchar2(1000);
5507 l_value varchar2(4000);
5508 l_error_result varchar2(4000);
5509 l_sig_policy varchar2(100);
5510
5511 l_step varchar2(240);
5512 begin
5513
5514 l_eventkey := p_event.GetEventKey();
5515 l_nid := to_number(l_eventKey);
5516 l_eventName := p_event.GetEventName();
5517 l_paramList := p_event.getParameterList();
5518 l_eventData := p_event.getEventData();
5519
5520 -- Recieve the message.
5521 -- Unpack the content and pass it to the
5522 -- routine in charge of parsing it.
5523
5524 -- Allow the response handling handle the closed notification
5525 -- 3736816 Uncommenting and reimplementing this logic to
5526 -- ensure that responses to CANCELED notifications are processed
5527 -- but do not update anything.
5528 l_step := 'Checking the notification status';
5529 if not notificationIsOpen(l_nid) then
5530 begin
5531 lk_type := '';
5532 lk_code := '';
5533 wf_log_pkg.string(WF_LOG_PKG.LEVEL_EXCEPTION,
5534 'WF_XML.handleReceiveEvent',
5535 'Notification is not OPEN. Submitting response '||
5536 'to provide user feedback');
5537 Wf_Notification.Respond(l_nid, NULL, 'email:'||l_fromAddr);
5538 exception
5539 when others then
5540 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5541 wf_log_pkg.string(WF_LOG_PKG.level_statement,
5542 'wf.plsql.WF_XML.handleReceiveEvent',
5543 'Exception on call to Wf_Notification.Respond NID['||l_nid||'] '||
5544 'Error Msg '||sqlerrm);
5545 end if;
5546
5547 wf_core.Context('WF_XML','handleReceiveEvent',
5548 p_event.getEventName(), p_subscription_guid);
5549 -- Save error message and set status to INVALID so mailer will
5550 -- bounce an "invalid reply" message to sender.
5551 WF_MAIL.HandleResponseError(l_nid, lk_type, lk_code,
5552 l_error_result);
5553 end;
5554 return 'SUCCESS';
5555 end if;
5556
5557 l_step := 'Getting the response details';
5558 getResponseDetails(l_eventData, l_node, l_version, l_fromAddr,
5559 l_responses);
5560
5561 l_step := 'Processing the responses';
5562 if l_responses.COUNT > 0 then
5563 begin
5564 -- Check if this notification requires a Signature. Other than DEFAULT
5565 -- policies, no other policy is processed by the mailer.
5566 Wf_Mail.GetSignaturePolicy(l_nid, l_sig_policy);
5567 if (l_sig_policy is not NULL and upper(l_sig_policy) <> 'DEFAULT') then
5568 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5569 wf_log_pkg.string(WF_LOG_PKG.level_statement,
5570 'wf.plsql.WF_XML.handleReceiveEvent',
5571 'Signature policy is not DEFAULT');
5572 end if;
5573
5574 -- If a new policy is added, only wfmail.msg needs to be updated
5575 wf_core.context('WF_XML', 'HandleReceiveEvent',
5576 to_char(l_nid), l_node, l_fromAddr);
5577 wf_core.token('NID', to_char(l_nid));
5578 wf_core.raise('WFRSPR_' || l_sig_policy);
5579 end if;
5580
5581 for i in 1..l_responses.COUNT loop
5582 l_step := 'Processing the responses -> '||l_responses(i).name;
5583 lk_type := l_responses(i).format;
5584 lk_code := substrb(l_responses(i).value, 1, 4000);
5585 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5586 wf_log_pkg.string(WF_LOG_PKG.level_statement,
5587 'wf.plsql.WF_XML.handleReceiveEvent',
5588 'Response VALUE ['||
5589 substrb(lk_code, 1, 100)||
5590 '] TYPE ['||lk_type||']');
5591 end if;
5592
5593 if l_version < 3 then
5594
5595 ----------------------------------
5596 -- verion < 3 is a normal response
5597 ----------------------------------
5598
5599 -- Process the responses for standard responses
5600 if l_responses(i).type = 'LOOKUP' then
5601 -- Verify the content of the lookup. This will raise
5602 -- an exception if it is not matched. GetLovMeaning
5603 -- allows for nulls. This is not acceptable here.
5604
5605 lk_meaning := wf_mail.GetLovMeaning(lk_type,
5606 lk_code);
5607 if lk_meaning is null then
5608 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5609 wf_log_pkg.string(WF_LOG_PKG.level_statement,
5610 'wf.plsql.WF_XML.handleReceiveEvent',
5611 'LOV Meaning is null');
5612 end if;
5613
5614 wf_core.token('TYPE', lk_type);
5615 wf_core.token('CODE', lk_code);
5616 wf_core.raise('WFSQL_LOOKUP_CODE');
5617 end if;
5618 end if;
5619 if l_responses(i).type = 'MOREINFO' then
5620 null; -- discard these responses
5621 else
5622 wf_notification.setAttrText(l_nid, l_responses(i).name,
5623 lk_code);
5624 end if;
5625 end if;
5626
5627 -- Only process attributes of type MOREINFO on
5628 -- version 3 and 4 templates.
5629 if l_responses(i).type = 'MOREINFO'
5630 and l_version in (3, 4) then
5631 if lk_code is not null then
5632 if l_version = 3 then
5633
5634 if l_responses(i).name = 'WFNTF_MOREINFO_FROM' then
5635 l_user := DecodeEntityReference(lk_code);
5636 elsif l_responses(i).name = 'WFNTF_MOREINFO_QPROMPT' then
5637 if length(l_responses(i).value) > 4000 then
5638 wf_core.context('WF_XML', 'HandleReceiveEvent',
5639 l_responses(i).type,
5640 l_responses(i).name, l_step);
5641 lk_code := substrb(l_responses(i).value, 1,
5642 1000);
5643 wf_core.raise('WFNTF_QUESTION_TOO_LARGE');
5644 end if;
5645 l_comment := lk_code;
5646 end if;
5647 elsif l_version = 4 then
5648 if l_responses(i).name = 'WFNTF_MOREINFO_APROMPT' then
5649 if length(l_responses(i).value) > 4000 then
5650 wf_core.context('WF_XML', 'HandleReceiveEvent',
5651 l_responses(i).type,
5652 l_responses(i).name, l_step);
5653 lk_code := substrb(l_responses(i).value, 1,
5654 1000);
5655 wf_core.raise('WFNTF_QUESTION_TOO_LARGE');
5656 end if;
5657 l_comment := lk_code;
5658 end if;
5659 end if;
5660 end if;
5661 end if;
5662 end loop;
5663
5664 -- Update the results of the more information to the
5665 -- comments table by calling the update API in the
5666 -- correct mode based on the template version.
5667 l_step := 'Updating the notification';
5668 if l_version = 3 then
5669 -- Question mode
5670 -- Send a email back to the sender that the More Info User is invalid
5671 if (l_user is null or length(trim(l_user)) = 0) then
5672 wf_core.raise('WFNTF_NO_ROLE');
5673 elsif(l_comment is null or length(trim(l_comment)) = 0) then
5674 wf_core.raise('WFNTF_NO_QUESTION');
5675 else
5676 wf_notification.UpdateInfo2(l_nid, l_user, l_fromAddr,
5677 l_comment);
5678 end if;
5679 elsif l_version = 4 then
5680 -- Answer mode
5681 l_user := null;
5682 if(l_comment is null or length(trim(l_comment)) = 0) then
5683 wf_core.raise('WFNTF_NO_ANSWER');
5684 else
5685 wf_notification.UpdateInfo2(l_nid, l_user, l_fromAddr,
5686 l_comment);
5687 end if;
5688 else
5689 -- Do not need to preserve context
5690 wf_engine.preserved_context := FALSE;
5691
5692 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5693 wf_log_pkg.string(WF_LOG_PKG.level_statement,
5694 'wf.plsql.WF_XML.handleReceiveEvent',
5695 'Calling Wf_Notification.Respond');
5696 end if;
5697
5698 Wf_Notification.Respond(nid => l_nid,
5699 responder => 'email:'||l_fromAddr);
5700 end if;
5701 exception
5702 when others then
5703 if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5704 wf_log_pkg.string(WF_LOG_PKG.level_statement,
5705 'wf.plsql.WF_XML.handleReceiveEvent',
5706 'Exception in processing the Response. Step '||l_step||
5707 ' Error Msg '||sqlerrm);
5708 end if;
5709
5710 wf_core.Context('WF_XML','handleReceiveEvent',
5711 to_char(l_nid), 'Step '||l_step);
5712 wf_core.context('WF_MAIL','HandleRevieveEvent', to_char(l_nid));
5713 -- Save error message and set status to INVALID so mailer will
5714 -- bounce an "invalid reply" message to sender.
5715 WF_MAIL.HandleResponseError(l_nid, lk_type,
5716 substrb(lk_code, 1, 1000),
5717 l_error_result);
5718 end;
5719 end if;
5720 return 'SUCCESS';
5721
5722 exception
5723
5724 when others then
5725 wf_core.Context('WF_XML','handleReceiveEvent',p_event.getEventName(),
5726 p_subscription_guid);
5727 -- Save error message and set status to INVALID so mailer will
5728 -- bounce an "invalid reply" message to sender.
5729 wf_event.SetErrorInfo(p_event, 'ERROR');
5730 return 'ERROR';
5731
5732 end handleReceiveEvent;
5733
5734
5735 -- receive
5736 -- Handle the notification receive events
5737 -- This will handle the processing of the inbound responses
5738 -- IN
5739 -- p_subscription_guid - The RAW GUID of the event subscription
5740 -- p_event - The WF_EVENT_T containing the event information
5741 -- RETURN
5742 -- varchar2 of the status
5743 function receive (p_subscription_guid in raw,
5744 p_event in out NOCOPY WF_EVENT_T) return varchar2
5745 is
5746 l_eventName varchar2(80);
5747 result varchar2(30);
5748 erragt wf_agent_t;
5749 l_paramlist wf_parameter_list_t;
5750 errMsg varchar2(4000);
5751 errStack varchar2(4000);
5752
5753 begin
5754
5755 l_eventName := p_event.GetEventName();
5756 l_paramList := p_event.getParameterList();
5757
5758 if l_eventName = WF_NTF_RECEIVE_MESSAGE then
5759
5760 result := handleReceiveEvent(p_subscription_guid, p_event);
5761
5762 elsif l_eventName = WF_NTF_RECEIVE_ERROR then
5763 errMsg := wf_event.getValueForParameter('ERROR_MESSAGE',
5764 l_paramList);
5765 errStack := wf_event.getValueForParameter('ERROR_STACK',
5766 l_paramList);
5767 p_event.setErrorSubscription(p_subscription_guid);
5768 p_event.setErrorMessage(substrb(errMsg,1,4000));
5769 p_event.setErrorStack(substrb(errStack,1,4000));
5770 p_event.addParameterToList('ERROR_NAME', 'WF_NTF_RECEIVE_ERROR');
5771 p_event.addParameterToList('ERROR_TYPE', 'ERROR');
5772
5773 erragt := wf_agent_t('WF_ERROR', wf_event.local_system_name);
5774
5775 --
5776 -- sjm - lets just call the API directly
5777 --
5778 wf_error_qh.enqueue(p_event, erragt);
5779
5780 result := 'SUCCESS';
5781
5782 else
5783
5784 return wf_rule.default_rule(p_subscription_guid, p_event);
5785
5786 end if;
5787
5788 return result;
5789
5790 exception
5791
5792 when others then
5793 wf_core.Context('WF_XML','Receive',p_event.getEventName(),
5794 p_subscription_guid);
5795 -- Save error message and set status to INVALID so mailer will
5796 -- bounce an "invalid reply" message to sender.
5797 wf_event.SetErrorInfo(p_event, 'ERROR');
5798 return 'ERROR';
5799 end receive;
5800
5801 -- SummaryRule
5802 -- To handle the summary notification request event
5803 -- and call the approapriate summary generate function for
5804 -- either the role or the member of the role.
5805 -- IN
5806 -- p_subscription_guid The RAW GUID for the subscription
5807 -- p_event The WF_EVENT_T containing the event details
5808 -- OUT
5809 -- VARCHAR2 - The status
5810 function SummaryRule (p_subscription_guid in raw,
5811 p_event in out NOCOPY WF_EVENT_T) return varchar2
5812 is
5813 l_eventName varchar2(80);
5814 l_eventkey varchar(80);
5815 l_paramlist wf_parameter_list_t;
5816
5817 l_event_paramlist wf_parameter_list_t;
5818 -- NOTE: For bug 6049086, more_info_role is included in the SQL
5819 -- in order to send summary mail to more_info_role and not
5820 -- to recepient_role if notification more_info_role is not null.
5821 CURSOR c_get_summary_roles is
5822 SELECT recipient from
5823 (SELECT distinct nvl(more_info_role,recipient_role) recipient
5824 FROM wf_notifications
5825 WHERE mail_status is null
5826 AND status = 'OPEN'
5827 AND rownum > 0)
5828 WHERE Wf_Directory.GetRoleNtfPref(recipient) in ('SUMMARY', 'SUMHTML');
5829
5830 begin
5831
5832 l_eventkey := p_event.GetEventKey();
5833 l_eventName := p_event.GetEventName();
5834 l_paramList := p_event.getParameterList();
5835
5836 for rec_summary_role in c_get_summary_roles loop
5837
5838 l_event_paramlist := wf_parameter_list_t();
5839 wf_event.addParameterToList('ROLE_NAME',
5840 rec_summary_role.RECIPIENT,
5841 l_event_paramlist);
5842 -- Set AQs correlation id to item type i.e. 'WFMAIL'
5843 wf_event.addParameterToList('Q_CORRELATION_ID', 'WFMAIL:SUM',
5844 l_event_paramlist);
5845 wf_event.addParameterToList('NOTIFICATION_ID', '0',
5846 l_event_paramlist);
5847
5848 wf_event.raise(WF_NTF_SEND_SUMMARY,
5849 rec_summary_role.RECIPIENT||':'||sysdate,
5850 null, l_event_paramlist);
5851 end loop;
5852
5853 return 'SUCCESS';
5854
5855 exception
5856
5857 when others then
5858 wf_core.Context('WF_XML','SummaryRule',p_event.getEventName(),
5859 p_subscription_guid);
5860 wf_event.SetErrorInfo(p_event, 'ERROR');
5861 return 'ERROR';
5862 end SummaryRule;
5863
5864 -- Parse the XML message and seperate out the main elements
5865 -- so that a new notification can be constructed including
5866 -- the information on the previous email.
5867 procedure getMessageDetails(pmessage IN CLOB,
5868 pnode OUT NOCOPY varchar2,
5869 planguage OUT NOCOPY varchar2,
5870 pterritory OUT NOCOPY varchar2,
5871 pcodeset OUT NOCOPY varchar2,
5872 pcontentBody OUT NOCOPY varchar2,
5873 psubject OUT NOCOPY varchar2,
5874 pFromRole OUT NOCOPY varchar2,
5875 pFromAddress OUT NOCOPY varchar2)
5876 is
5877 p xmlparser.parser;
5878 doc xmldom.DOMDocument;
5879
5880 nl xmldom.DOMNodeList;
5881 len1 number;
5882 len2 number;
5883 n xmldom.DOMNode;
5884 m xmldom.DOMNode;
5885 nnm xmldom.DOMNamedNodeMap;
5886
5887 from_node_found boolean := FALSE;
5888 fromRole varchar2(4000);
5889 fromAddress varchar2(4000);
5890
5891 node_data varchar2(4000);
5892 node_name varchar2(4000);
5893 attrname varchar2(4000);
5894 attrval varchar2(4000);
5895
5896
5897 subject varchar2(4000);
5898
5899 contentType varchar2(4000);
5900 textPlain_found boolean := FALSE;
5901
5902 begin
5903
5904 -- new parser
5905 p := xmlparser.newParser;
5906
5907 -- set some characteristics
5908 xmlparser.setValidationMode(p, FALSE);
5909
5910 -- parse input file
5911 xmlparser.parseClob(p, pmessage);
5912
5913 -- get document
5914 doc := xmlparser.getDocument(p);
5915
5916 -- get all elements
5917 nl := xmldom.getElementsByTagName(doc, '*');
5918 len1 := xmldom.getLength(nl);
5919
5920 -- loop through elements
5921 for j in 0..len1-1 loop
5922 n := xmldom.item(nl, j);
5923 node_name := xmldom.getNodeName(n);
5924
5925 if node_name = 'NOTIFICATION' then
5926 -- get all attributes of element
5927 nnm := xmldom.getAttributes(n);
5928
5929 if (xmldom.isNull(nnm) = FALSE) then
5930
5931 len2 := xmldom.getLength(nnm);
5932
5933 -- loop through attributes
5934 for i in 0..len2-1 loop
5935 m := xmldom.item(nnm, i);
5936 attrname := xmldom.getNodeName(m);
5937 if attrname = 'node' then
5938 attrval := xmldom.getNodeValue(m);
5939 pnode := attrval;
5940 -- exit;
5941 elsif attrname = 'language' then
5942 attrval := xmldom.getNodeValue(m);
5943 planguage := attrval;
5944 elsif attrname = 'territory' then
5945 attrval := xmldom.getNodeValue(m);
5946 pterritory := attrval;
5947 elsif attrname = 'codeset' then
5948 attrval := xmldom.getNodeValue(m);
5949 pcodeset := attrval;
5950 end if;
5951
5952 end loop;
5953 end if;
5954 elsif node_name = 'SUBJECT' then
5955 n := xmldom.getFirstChild(n);
5956
5957 if ((not xmldom.isNull(n)) and
5958 (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then
5959
5960 node_data := xmlDom.getNodeValue(n);
5961 psubject := node_data;
5962
5963 end if;
5964
5965 elsif node_name = 'FROM' then
5966 from_node_found := TRUE;
5967
5968 elsif node_name = 'NAME' then
5969 if from_node_found then
5970 n := xmldom.getFirstChild(n);
5971
5972 if ((not xmldom.isNull(n)) and
5973 (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then
5974
5975 node_data := xmlDom.getNodeValue(n);
5976 pfromRole := node_data;
5977
5978 end if;
5979 end if;
5980 elsif node_name = 'ADDRESS' then
5981 if from_node_found then
5982
5983 from_node_found := FALSE;
5984
5985 n := xmldom.getFirstChild(n);
5986
5987 if ((not xmldom.isNull(n)) and
5988 (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then
5989
5990 node_data := xmlDom.getNodeValue(n);
5991 pfromAddress := node_data;
5992
5993 end if;
5994 end if;
5995 elsif node_name = 'MESSAGE' then
5996
5997 nnm := xmldom.getAttributes(n);
5998
5999 if (xmldom.isNull(nnm) = FALSE) then
6000
6001 len2 := xmldom.getLength(nnm);
6002
6003 -- loop through attributes
6004 for i in 0..len2-1 loop
6005 m := xmldom.item(nnm, i);
6006 attrname := xmldom.getNodeName(m);
6007 if upper(attrname) = 'CONTENT-TYPE' then
6008 attrval := xmldom.getNodeValue(m);
6009 contentType := attrval;
6010 exit;
6011 end if;
6012 end loop;
6013 textPlain_found := upper(contentType) = 'TEXT/PLAIN';
6014 end if;
6015 if textPlain_found then
6016 n := xmldom.getFirstChild(n);
6017
6018 if ((not xmldom.isNull(n)) and
6019 (xmldom.getNodeType(n) = xmldom.TEXT_NODE)) then
6020
6021 node_data := xmlDom.getNodeValue(n);
6022 pcontentBody := node_data;
6023 end if;
6024 end if;
6025 end if;
6026 end loop;
6027
6028 end getMessageDetails;
6029
6030 FUNCTION error_rule(p_subscription_guid in raw,
6031 p_event in out nocopy wf_event_t) return varchar2
6032 is
6033 nid number;
6034 param_list wf_parameter_list_t;
6035 cb varchar2(240);
6036 ctx varchar2(2000);
6037 itemType varchar2(8);
6038 itemKey varchar2(240);
6039 items pls_integer;
6040 role varchar2(320);
6041
6042 colPos1 pls_integer;
6043 colPos2 pls_integer;
6044
6045 error_name varchar2(4000);
6046 error_msg varchar2(4000);
6047 error_stack varchar2(4000);
6048
6049 -- Dynamic sql stuff
6050 sqlbuf varchar2(120);
6051 tvalue varchar2(4000) := '';
6052 nvalue number := '';
6053 dvalue date := '';
6054 command varchar2(200) :='ERROR';
6055
6056 status varchar2(8);
6057 l_dummy varchar2(1);
6058
6059 begin
6060 param_list := p_event.Parameter_List;
6061 nid := to_number(wf_event.getValueForParameter('NOTIFICATION_ID',
6062 param_list));
6063 error_msg := p_event.getErrorMessage;
6064 error_stack := p_event.getErrorStack;
6065 begin
6066 select MESSAGE_TYPE, CALLBACK, CONTEXT, STATUS
6067 into itemType, cb, ctx, status
6068 from WF_NOTIFICATIONS
6069 where NOTIFICATION_ID = nid;
6070
6071 -- If the notification is closed, then do not bother
6072 -- to process any errors.
6073 if status = 'CLOSED' then
6074 return 'SUCCESS';
6075 end if;
6076
6077 update wf_notifications
6078 set mail_status = 'ERROR'
6079 where notification_id = nid;
6080
6081 if ctx is not null then
6082 colPos1 := instrb(ctx, ':', 1);
6083 colPos2 := instrb(ctx, ':', -1);
6084 if colPos1 > 0 and colPos2 > 0 then
6085 itemKey := substrb(ctx, colPos1+1, (colPos2 - colPos1) -1);
6086 else
6087 itemType := null;
6088 itemKey := null;
6089 end if;
6090 else
6091 itemType := null;
6092 itemKey := null;
6093 end if;
6094
6095 exception
6096 when others then
6097 cb := null;
6098 ctx := null;
6099 itemType := null;
6100 itemKey := null;
6101 return 'SUCCESS';
6102 end;
6103
6104 -- Check to see if the item type still exists and is not
6105 -- complete. If it does not exist or is complete, then
6106 -- do not bother with processing the error.
6107 if itemType is not null and itemKey is not null then
6108 begin
6109 select ACTIVITY_STATUS
6110 into status
6111 from wf_item_activity_statuses ias
6112 , wf_process_activities pa
6113 where ias.item_type = itemType
6114 and ias.item_key = itemKey
6115 and ias.process_activity = pa.instance_id
6116 and pa.process_name = 'ROOT';
6117
6118 -- This will prevent FYI message that are in error being reported.
6119 -- if status = wf_engine.eng_completed then
6120 -- return 'SUCCESS';
6121 -- end if;
6122
6123 exception
6124 when no_data_found then
6125 return 'SUCCESS';
6126 when others then
6127 error_msg := sqlerrm;
6128 wf_core.context('WF_XML','ERROR_RULE','NID['||to_char(nid)||']',
6129 'CTX['||ctx||']');
6130 raise;
6131 end;
6132 else
6133 return 'SUCCESS';
6134 end if;
6135
6136 if cb is not null then
6137 -- Put the error onto the stack.
6138 begin
6139 wf_core.token('ERROR_MESSAGE', error_msg);
6140 wf_core.token('ERROR_STACK', error_stack);
6141 wf_core.raise('WF_ERROR');
6142 exception
6143 when others then null;
6144 end;
6145 l_dummy := '';
6146 -- ### cb is from table
6147 -- BINDVAR_SCAN_IGNORE
6148 sqlbuf := 'begin '||cb||
6149 '(:p1, :p2, :p3, :p4, :p5, :p6, :p7); end;';
6150 begin
6151 execute immediate sqlbuf using
6152 in command,
6153 in ctx,
6154 in l_dummy,
6155 in l_dummy,
6156 in out tvalue,
6157 in out nvalue,
6158 in out dvalue;
6159 exception
6160 when others then
6161 error_msg := sqlerrm;
6162 wf_core.context('WF_XML','ERROR_RULE','NID['||to_char(nid)||']',
6163 'CTX['||ctx||']');
6164 raise;
6165 end;
6166 end if;
6167
6168 return wf_rule.error_rule(p_subscription_guid, p_event);
6169
6170 end error_rule;
6171
6172 -- Gets the LOB content for a PLSQLCLOB
6173 -- IN
6174 -- pAPI the API to call
6175 -- pDoc The LOB to take the document
6176 procedure getDocContent(pNid in NUMBER, pAPI in VARCHAR2,
6177 pDoc in out nocopy CLOB)
6178 is
6179 colon pls_integer;
6180 slash pls_integer;
6181 procname varchar2(240);
6182 procarg varchar2(32000);
6183
6184 target varchar2(240) := '_main';
6185 disptype varchar2(240) := g_ntfDocHtml;
6186 doctype varchar2(240);
6187
6188 sqlbuf varchar2(2000);
6189
6190 begin
6191
6192 colon := instr(pAPI, ':');
6193 slash := instr(pAPI, '/');
6194 if (slash = 0) then
6195 procname := substr(pAPI, colon+1);
6196 procarg := '';
6197 else
6198 procname := substr(pAPI, colon+1, slash-colon-1);
6199 procarg := substr(pAPI, slash+1);
6200 end if;
6201
6202 -- Dynamic sql call to procedure
6203
6204 if (procarg is null) then
6205 procarg := '-dummy-';
6206 elsif pNid > 0 then
6207 procarg := Wf_Notification.GetText(procarg, pNid, disptype);
6208 end if;
6209
6210 sqlbuf := 'begin '||procname||'(:p1, :p2, :p3, :p4); end;';
6211 execute immediate sqlbuf using
6212 in procarg,
6213 in disptype,
6214 in out pDoc,
6215 in out doctype;
6216
6217 end getDocContent;
6218
6219 -- Gets the LOB content for a PLSQLCLOB
6220 -- IN
6221 -- pAPI the API to call
6222 -- pDoc The LOB to take the document
6223 procedure getBDocContent(pNid in NUMBER, pAPI in VARCHAR2,
6224 pDoc in out nocopy BLOB)
6225 is
6226
6227 colon pls_integer;
6228 slash pls_integer;
6229 procname varchar2(240);
6230 procarg varchar2(32000);
6231
6232 target varchar2(240) := '_main';
6233 disptype varchar2(240) := g_ntfDocHtml;
6234 doctype varchar2(240);
6235
6236 sqlbuf varchar2(2000);
6237
6238 begin
6239
6240 colon := instr(pAPI, ':');
6241 slash := instr(pAPI, '/');
6242 if (slash = 0) then
6243 procname := substr(pAPI, colon+1);
6244 procarg := '';
6245 else
6246 procname := substr(pAPI, colon+1, slash-colon-1);
6247 procarg := substr(pAPI, slash+1);
6248 end if;
6249
6250 -- Dynamic sql call to procedure
6251
6252 if (procarg is null) then
6253 procarg := '-dummy-';
6254 elsif pNid > 0 then
6255 procarg := Wf_Notification.GetText(procarg, pNid, disptype);
6256 end if;
6257
6258 sqlbuf := 'begin '||procname||'(:p1, :p2, :p3, :p4); end;';
6259 execute immediate sqlbuf using
6260 in procarg,
6261 in disptype,
6262 in out pDoc,
6263 in out doctype;
6264
6265 end getBDocContent;
6266
6267 -- gets the size of the current LOB table
6268 function getLobTableSize return number
6269 is
6270 begin
6271 return g_LOBTable.COUNT;
6272 end;
6273
6274 end WF_XML;
[Home] [Help]