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