1 package WF_MAIL AUTHID CURRENT_USER as
2 /* $Header: wfmlrs.pls 120.15 2011/03/05 07:40:32 sstomar ship $ */
3 /*#
4 * Provides APIs to access the Oracle Workflow message subsystem and send messages through e-mail.
5 * @rep:scope public
6 * @rep:product OWF
7 * @rep:displayname Workflow Message Subsystem
8 * @rep:lifecycle active
9 * @rep:category BUSINESS_ENTITY WF_NOTIFICATION
10 * @rep:compatibility S
11 */
12 --
13 -- Global table to store the url content.
14 --
15 type url_content_array is table of varchar2(2000) index by binary_integer;
16 content_array url_content_array;
17
18 -- bug 2437782
19 -- record type to store the response attribute information
20 TYPE resp_attr_rec IS RECORD
21 (
22 attr_prompt varchar2(80),
23 attr_type varchar2(8),
24 attr_name varchar2(30),
25 attr_format varchar2(240)
26 );
27
28 TYPE resp_attrs_t IS TABLE OF resp_attr_rec INDEX BY BINARY_INTEGER;
29
30 -- Test flag. Internal use only
31 test_flag boolean := FALSE;
32
33 --
34 -- E-mail notification recipient record
35 -- Name - Name of the recipient
36 -- Address - Valid e-mail address
37 -- Recipient_Type - TO or CC or BCC
38 TYPE wf_recipient_rec_t IS RECORD
39 (
40 NAME VARCHAR2(360),
41 ADDRESS VARCHAR2(320),
42 RECIPIENT_TYPE VARCHAR2(4)
43 );
44
45
46
47 -- Global variable to hold a static stylesheet definition
48 g_newLine varchar2(1) := wf_core.newLine;
49 g_template_style varchar2(32000) :=
50 'body,html,td, p { top-margin:0; padding:0; font-family:Tahoma; font-size:9pt; color:#3c3c3c; }'||g_newline||
51 'H2.OraHeader{ color:#343434; padding:0px 0px 0px 0px; font-family:Arial; font-weight:bold; font-size:9pt; vertical-align:middle; }'||g_newline||
52 '.OraHeaderSub,.x3w {margin-bottom:0px;color:#426ca7;font-family:Arial;font-weight:bold;font-size:92%;vertical-align:middle;border-bottom:1px solid #aabed5;margin:5px 0px 0px 0px}'||g_newline||
53 '.OraHeaderBar,.x6z {color:#3c3c3c;font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;background-repeat:repeat-x;padding:0px 0px 0px 8px;font-weight:bold;font-size:8pt;vertical-align:middle;'||
54 'border-style:solid;border-width:1px 1px 1px 1px;border-color:#a3bed8;margin-top:5px;margin-bottom:0px}'||g_newline||
55 '.OraLink:link,.xd:link,.OraLink:active,.xd:active,.OraLink:visited,.xd:visited,.OraBulletedList A,.xj A,.OraCopyright A,.xv A,'||
56 '.OraPrivacy A,.xw A,.OraAbout A,.xx A,.OraLinkText,.x2v,.OraVLinkText,.x2w,.OraALinkText,.x2x,.OraHGridNavRowActiveLink,.x3u,.OraShuttleLinkText,.x4a,.OraSubTab A,.x4h A,.p_OraTreeRow A:link,.x5c A:link,'||
57 '.p_OraTreeRow A:active,.x5c A:active,.p_OraTreeRow A:visited,.x5c A:visited {font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;font-size:x-small;color:#2da0cb}'||g_newline||
58 '.OraContentContainerHeader,.xp {background-color:#d6f5ff;margin:5px 0px 0px 0px;padding:0px 0px 0px 8px;font-weight:bold;font-size:8pt;vertical-align:middle;border-bottom:1px solid #aabed5;'||
59 'margin-top:5px;margin-bottom:0px;font-family:Tahoma;color:#343434}'||g_newline||
60 '.OraTipLabel,.x55 {font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;font-weight:bold;color:#426ca7;font-size:8pt}'||
61 '.OraTipText,.x56 {font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;color:#3a5a87;font-size:8pt}'||g_newline||
62 '.OraTableColumnHeader, .x1r, .OraTableRowHeader, .x1u { text-align:left;vertical-align:bottom;font-family:Tahoma,Arial,Helvetica,Geneva,'||
63 'sans-serif;font-weight:bold;background-color:#cfe0f1;color:#3c3c3c;border-color:#c9cbd3;font-size:8pt }'||g_newline||
64 '.OraTableCellText, .x1l { color:#3c3c3c;vertical-align:baseline;font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;border-color:#c9cbd3;font-size:9pt;background-color:#f2f2f5 }'||g_newline||
65 '.OraTableContent,.x1h {border-color:#c9cbd3;border-style:solid;border-width:1px;border-collapse:collapse}'||g_newline||
66 '.OraTableHeaderLink,.x24 {text-decoration:none;text-align:left;vertical-align:bottom;font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;font-weight:bold;background-color:#cfe0f1;'||
67 'color:#3c3c3c;border-color:#c9cbd3;font-size:8pt}'||g_newline||
68 '.OraInstructionTextStrong,.x1,.OraDataText,.x2 {font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;font-size:x-small;font-weight:bold;color:#3c3c3c} '||g_newline||
69 '.OraPromptText,.x8 {font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;font-size:8pt;text-align:right;color:#000000;font-weight:normal} '||g_newline||
70 '.OraTableBorder0001,.x4j {border-style:solid;border-width:0px 0px 0px 1px}'||g_newline||
71 '.OraTableBorder1000,.x4q {border-style:solid;border-width:1px 0px 0px}'||g_newline||
72 '.OraTableBorder1111,.x4x {border-style:solid;border-width:1px}';
73
74 -- Defining explicit formatting values for whatever output that requires it
75 -- Header attributes
76 g_th_bgcolor varchar2(7) := '#cfe0f1';
77 g_th_fontcolor varchar2(7) := '#3c3c3c';
78 g_th_fontface varchar2(80) := 'Tahoma, Arial, Helvetica, Geneva, sans-serif';
79 g_th_fontsize varchar2(2) := '2';
80
81 -- Cell attributes
82 g_td_bgcolor varchar2(7) := '#f2f2f5';
83 g_td_fontcolor varchar2(7) := '#3c3c3c';
84 g_td_fontface varchar2(80) := 'Tahoma, Arial, Helvetica, Geneva, sans-serif';
85 g_td_fontsize varchar2(2) := '2';
86
87 -- Table of recipient record
88 TYPE wf_recipient_list_t IS TABLE OF wf_recipient_rec_t INDEX BY BINARY_INTEGER;
89
90 -- UpdateStatus
91 -- Update mail status and close any notification with no response.
92 -- Handle error.
93 -- IN
94 -- notification id
95 -- status
96 -- error name (null if error is in WF_CORE)
97 procedure UpdateStatus(
98 nid in number,
99 status in varchar2,
100 error_name in varchar2 default null);
101
102 -- UpdateStatus2
103 -- Update mail status and close any notification with no response.
104 -- Handle error.
105 -- IN
106 -- nid notification id
107 -- status Status to set the notification
108 -- autoclose Flag to specify whether the notification should be closed
109 -- automitically
110 -- error name (null if error is in WF_CORE)
111 -- external_error Any error message that can not be reflected or captured
112 -- through the wf_core.context facilty ie Java.
113 procedure UpdateStatus2(
114 nid in number,
115 status in varchar2,
116 autoclose in varchar2,
117 error_name in varchar2 default null,
118 external_error in varchar2 default null);
119
120 -- ResetFailed
121 -- Update mail status from FAILED to MAIL.
122 -- IN
123 -- Queue number
124 procedure ResetFailed(p_queue varchar2 default '1');
125
126 -- HandleSendError
127 -- Call any callback in error mode if error occurs in sending mail.
128 -- IN
129 -- notification id
130 -- error name (null if error is in WF_CORE)
131 -- external_error Any error message that can not be reflected or captured
132 -- through the wf_core.context facilty ie Java.
133 procedure HandleSendError(
134 nid in number,
135 status in varchar2,
136 error_name in varchar2 default null,
137 external_error in varchar2 default null);
138
139
140 -- UpdateRecipient
141 -- Updates the recipient of a notification to DISABLED where
142 -- there has been a failure to deliver to their email address.
143 -- This function is triggered by the oracle.apps.wf.notification.send.failure
144 -- event.
145 function Disable_Recipient_Ntf_Pref(p_subscription_guid in raw,
146 p_event in out NOCOPY WF_EVENT_T) return varchar2;
147
148 -- HandleResponseError (PRIVATE) handle exception in response
149 --
150 -- Sets the MAIL_ERROR error message attribute, then sets the
151 -- notification status to INVALID.
152 --
153 -- IN
154 -- notification id
155 -- lookup type
156 -- value found
157
158 procedure HandleResponseError(nid in number,
159 lk_type in varchar2,
160 lk_meaning in varchar2,
161 error_result in out NOCOPY varchar2);
162
163
164 -- WordWrap (PRIVATE)
165 -- Insert newlines to word wrap a line buffer.
166 -- Inputs:
167 -- text - text buffer
168 -- indent - number of tabs to indent each line by
169 -- Returns:
170 -- buffer contents with newlines and tabs embedded
171 function WordWrap(
172 text in varchar2,
173 indent in number default 0) return varchar2;
174
175 -- GetWarning - get warning messages
176 --
177 -- IN
178 -- Template
179 -- unsolicited from
180 -- unsolicited subject
181 -- unsolicited body
182 -- OUT
183 -- message subject
184 -- message body (text)
185 -- message body (html)
186 procedure GetWarning(
187 template in varchar2,
188 ufrom in varchar2,
189 usubject in varchar2,
190 ubody in varchar2,
191 subject out NOCOPY varchar2,
192 text_body_text out NOCOPY varchar2,
193 html_body_text out NOCOPY varchar2) ;
194
195 -- GetWarning - get warning messages
196 --
197 -- IN
198 -- unsolicited from
199 -- unsolicited subject
200 -- unsolicited body
201 -- OUT
202 -- message subject
203 -- message body (text)
204 -- message body (html)
205 procedure GetWarning(
206 ufrom in varchar2,
207 usubject in varchar2,
208 ubody in varchar2,
209 subject out NOCOPY varchar2,
210 text_body_text out NOCOPY varchar2,
211 html_body_text out NOCOPY varchar2) ;
212
213 -- GetSummary - get summary messages for one role
214 -- where the summary body can be a LOB
215 -- Bug# 2358498
216 --
217 -- IN
218 -- role name
219 -- role display name
220 -- mailer node name
221 -- OUT
222 -- message subject
223 -- message body (text)
224 -- message body (html)
225 -- lob (Y or N)
226 procedure GetSummary(
227 role in varchar2,
228 dname in varchar2,
229 node in varchar2,
230 subject out NOCOPY varchar2,
231 body_text out NOCOPY varchar2,
232 lob out NOCOPY varchar2);
233
234 -- GetSummary - get summary messages for one role
235 --
236 -- IN
237 -- role name
238 -- role display name
239 -- mailer node name
240 -- OUT
241 -- message subject
242 -- message body (text)
243 -- message body (html)
244 procedure GetSummary(
245 role in varchar2,
246 dname in varchar2,
247 node in varchar2,
248 subject out NOCOPY varchar2,
249 body_text out NOCOPY varchar2);
250
251 -- GetSummary2 - get summary messages for one role
252 -- Support the render flag for Applications Framework.
253 -- If set, no body will be rendered as it will be
254 -- deferred to the middle tier.
255 -- IN
256 -- role name
257 -- role display name
258 -- mailer node name
259 -- content type
260 -- OUT
261 -- message subject
262 -- message body (text)
263 -- message body (html)
264 -- lob (Y or N)
265 procedure GetSummary2(
266 role in varchar2,
267 dname in varchar2,
268 node in varchar2,
269 renderBody in varchar2,
270 contType in varchar2,
271 subject out NOCOPY varchar2,
272 body_text out NOCOPY varchar2,
273 lob out NOCOPY varchar2);
274
275 -- GetMessage - get email message data
276 --
277 -- IN
278 -- notification id
279 -- mailer node name
280 -- web agent path
281 -- Replyto Address
282 -- DIRECT_RESPONSE Flag
283 -- OUT
284 -- message subject
285 -- message body (text)
286 -- message body (html)
287 -- message attachments
288 procedure GetMessage(
289 nid in number,
290 node in varchar2,
291 agent in varchar2,
292 replyto in varchar2,
293 subject out NOCOPY varchar2,
294 text_body out NOCOPY varchar2,
295 html_body out NOCOPY varchar2,
296 body_atth out NOCOPY varchar2,
297 error_result in out NOCOPY varchar2);
298
299 --
300 -- setContext (PRIVATE)
301 -- Set the context by executing the selector function
302 -- IN
303 -- nid - Notification id
304 --
305 procedure setContext(nid NUMBER);
306
307 -- GetLOBMessage4 - get email message data
308 -- Bug 10202313: Added n_status, n_mstatus parameters to store the status, mail_status
309 -- columns of wf_notifications table
310 --
311 -- IN
312 -- notification id
313 -- mailer node name
314 -- web agent path
315 -- Replyto Address
316 -- recipient role
317 -- language
318 -- territory
319 -- notification preference
320 -- email address
321 -- display name
322 -- render body flag
323 -- notification status
324 -- notification mail status
325 -- OUT
326 -- message subject
327 -- message attachments
328 -- bodyToken a flag to indicate if there is a BODY token in the
329 -- main message template
330 procedure GetLOBMessage4(
331 nid in number,
332 node in varchar2,
333 agent in varchar2,
334 replyto in varchar2,
335 recipient in varchar2,
336 language in varchar2,
337 territory in varchar2,
338 ntf_pref in varchar2,
339 email in varchar2,
340 dname in varchar2,
341 renderbody in varchar2,
342 subject out NOCOPY varchar2,
343 body_atth out NOCOPY varchar2,
344 error_result in out NOCOPY varchar2,
345 bodyToken in out NOCOPY varchar2,
346 n_status in out NOCOPY varchar2,
347 n_mstatus in out NOCOPY varchar2);
348
349 -- GetLOBMessage3 - get email message data
350 --
351 -- IN
352 -- notification id
353 -- mailer node name
354 -- web agent path
355 -- Replyto Address
356 -- recipient role
357 -- language
358 -- territory
359 -- notification preference
360 -- email address
361 -- display name
362 -- render body flag
363 -- OUT
364 -- message subject
365 -- message attachments
366 -- bodyToken a flag to indicate if there is a BODY token in the
367 -- main message template
368 procedure GetLOBMessage3(
369 nid in number,
370 node in varchar2,
371 agent in varchar2,
372 replyto in varchar2,
373 recipient in varchar2,
374 language in varchar2,
378 dname in varchar2,
375 territory in varchar2,
376 ntf_pref in varchar2,
377 email in varchar2,
379 renderbody in varchar2,
380 subject out NOCOPY varchar2,
381 body_atth out NOCOPY varchar2,
382 error_result in out NOCOPY varchar2,
383 bodyToken in out NOCOPY varchar2);
384
385 -- GetLOBMessage2 - get email message data
386 --
387 -- IN
388 -- notification id
389 -- mailer node name
390 -- web agent path
391 -- Replyto Address
392 -- recipient role
393 -- language
394 -- territory
395 -- notification preference
396 -- email address
397 -- display name
398 -- OUT
399 -- message subject
400 -- message attachments
401 procedure GetLOBMessage2(
402 nid in number,
403 node in varchar2,
404 agent in varchar2,
405 replyto in varchar2,
406 recipient in varchar2,
407 language in varchar2,
408 territory in varchar2,
409 ntf_pref in varchar2,
410 email in varchar2,
411 dname in varchar2,
412 subject out NOCOPY varchar2,
413 body_atth out NOCOPY varchar2,
414 error_result in out NOCOPY varchar2);
415
416 -- GetLOBMessage - get email message data
417 --
418 -- IN
419 -- notification id
420 -- mailer node name
421 -- web agent path
422 -- Replyto Address
423 -- DIRECT_RESPONSE Flag
424 -- OUT
425 -- message subject
426 -- message attachments
427 procedure GetLOBMessage(
428 nid in number,
429 node in varchar2,
430 agent in varchar2,
431 replyto in varchar2,
432 subject out NOCOPY varchar2,
433 body_atth out NOCOPY varchar2,
434 error_result in out NOCOPY varchar2);
435
436 -- initFetchLOB
437 --
438 -- IN
439 -- type - Document type (TEXT or HTML)
440 --
441 procedure InitFetchLOB(doc_type VARCHAR2 default WF_NOTIFICATION.doc_text,
442 doc_length OUT NOCOPY NUMBER);
443
444
445 -- FetchLOBContent
446 --
447 -- IN
448 -- type of document to fetch TEXT/HTML
449 -- End of LOB marker
450 -- OUT
451 -- 32K chunk of the LOB
452 --
453 -- Use the API in the following manner
454 -- WF_MAIL.InitFetchLob(WF_NOTIFICATION.doc_text)
455 -- while not clob_end loop
456 -- WF_MAIL.FetchLobContent(cBuf, WF_NOTIFICATION.doc_text, clob_end);
457 -- ...
458 -- end loop;
459 --
460 procedure FetchLOBContent(buffer OUT NOCOPY VARCHAR2,
461 doc_type IN VARCHAR2 DEFAULT WF_NOTIFICATION.doc_text,
462 end_of_clob IN OUT NOCOPY NUMBER);
463 -- CloseLOB - Close the message LOBs ready for use again later
464 --
465 -- IN
466 -- Document type
467 procedure CloseLOB(doc_type in VARCHAR2);
468
469 -- CloseLOB - Close the message LOBs ready for use again later
470 --
471 procedure CloseLOB;
472
473 -- FetchUrlContent - Fetched the content from the global buffer which
474 -- populated by GetUrlContent().
475 --
476 -- IN
477 -- piece_count - the index to the url_content_array.
478 -- OUT
479 -- piece_value - the data stored in the global content_array table.
480 function FetchUrlContent(piece_count in number,
481 error_result in out NOCOPY varchar2) return varchar2;
482
483 -- GetUrlContent - get URL content
484 --
485 -- IN
486 -- url address id
487 -- OUT
488 -- piece_count
489 -- error result
490 procedure GetUrlContent(
491 url in varchar2,
492 piece_count out NOCOPY number,
493 error_result in out NOCOPY varchar2);
494
495 -- GetDocContent - get Document content
496 --
497 -- IN
498 -- notification id
499 -- document attribute name
500 -- display type
501 -- OUT
502 -- document content
503 -- error result
504 procedure GetDocContent(
505 nid in number,
506 docattrname in varchar2,
507 disptype in varchar2,
508 doccontent out NOCOPY varchar2,
509 error_result in out NOCOPY varchar2);
510
511 -- GetLOBDocContent - get LOB Document content
512 --
513 -- IN
514 -- notification id
515 -- document attribute name
516 -- display type
517 -- OUT
518 -- document content
519 -- error result
520 procedure GetLOBDocContent(
521 nid in number,
522 docattrname in varchar2,
523 disptype in varchar2,
524 error_result in out NOCOPY varchar2);
525
526 -- GetLOBDocContent - get LOB Document content
527 -- Returns the document type of the PLSQLCLOB document
528 --
529 -- IN
530 -- notification id
531 -- document attribute name
532 -- display type
533 -- OUT
534 -- document type
535 -- document content
536 -- error result
537 procedure GetLOBDocContent(
538 nid in number,
539 docattrname in varchar2,
540 disptype in varchar2,
541 doctype out NOCOPY varchar2,
542 error_result in out NOCOPY varchar2);
543
544 -- GetLovMeaning (PRIVATE)
545 -- Return the displayed meaning of a lookup
546 -- Inputs:
547 -- lk_type - lookup type
548 -- lk_code - lookup code
549 -- Returns:
550 -- lookup meaning
551 function GetLovMeaning(
552 lk_type in varchar2,
553 lk_code in varchar2) return varchar2;
554
555 -- GetLovCode (PRIVATE) Return the hidden code of a lookup
556 --
557 -- IN
561 -- lookup code
558 -- lookup type
559 -- lookup meaning
560 -- RETURN
562 function GetLovCode(
563 lk_type in varchar2,
564 lk_meaning in varchar2)
565 return varchar2;
566
567
568 -- PutMessage - response processing
569 --
570 -- IN
571 -- notification id
572 -- node name
573 -- response body
574 -- from address
575 procedure PutMessage(
576 nid in number,
577 node in varchar2,
578 resp_body in varchar2,
579 from_addr in varchar2,
580 error_result in out NOCOPY varchar2);
581
582 -- PutDirectMessage - direct response processing
583 --
584 -- IN
585 -- notification id
586 -- node name
587 -- response body
588 -- from address
589 procedure PutDirectMessage(
590 nid in number,
591 node in varchar2,
592 resp_body in varchar2,
593 from_addr in varchar2,
594 error_result in out NOCOPY varchar2);
595
596 -- More Info Feature - bug 2282139
597 -- PutMoreInfoRequest
598 -- Reply processor. Read body of a request for more information
599 -- parse the body for the role to send the request to.
600 -- Used by the notification mail response processor.
601 -- IN
602 -- notification id
603 -- mailer node name
604 -- response body text
605 -- email 'from' address
606 procedure PutMoreInfoRequest(
607 nid in number,
608 node in varchar2,
609 resp_body in varchar2,
610 from_addr in varchar2,
611 error_result in out NOCOPY varchar2);
612
613 -- PutMoreInfoMessage
614 -- Reply processor. Read body of a reply for more information
615 -- request, parse the body for the comments from the user and
616 -- update wf_notification and wf_comments apropriately
617 -- Used by the notification mail response processor.
618 -- IN
619 -- notification id
620 -- mailer node name
621 -- response body text
622 -- email 'from' address
623 procedure PutMoreInfoMessage(
624 nid in number,
625 node in varchar2,
626 resp_body in varchar2,
627 from_addr in varchar2,
628 error_result in out NOCOPY varchar2);
629
630 -- direct_response - Return the value of the direct response flag
631 --
632 -- OUT
633 -- Direct Response as [TRUE|FALSE]
634 function direct_response return boolean;
635
636 -- send_accesskey - Return the value of the send access key flag
637 --
638 -- OUT
639 -- Direct Response as [TRUE|FALSE]
640 function send_accesskey return boolean;
641
642 -- Autoclose_FYI - Return the value of the Autoclose FYI flag
643 --
644 -- OUT
645 -- AUTOCLOSE_FYI as [TRUE|FALSE]
646 function Autoclose_FYI return boolean;
647
648 -- Autoclose_FYI_on - Set the Autoclose flag to TRUE
649 --
650 -- OUT
651 procedure Autoclose_FYI_On;
652
653 -- Autoclose_FYI_off - Set the Autoclose flag to TRUE
654 --
655 -- OUT
656 procedure Autoclose_FYI_Off;
657
658 -- direct_response_on - Set the value of the direct response flag to TRUE
659 --
660 procedure direct_response_on;
661
662 -- direct_response - Set the value of the direct response flag to FALSE
663 --
664 procedure direct_response_off;
665
666 -- send_accesskey - Set the value of the send acces key flag to TRUE
667 --
668 procedure send_access_key_on;
669
670 -- send_accesskey - Set the value of the send acces key flag to FALSE
671 --
672 procedure send_access_key_off;
673
674
675 function UrlEncode(in_string varchar2) return varchar2;
676 pragma restrict_references(UrlEncode, WNDS);
677
678 procedure GetUrlAttachment (nid in number,
679 buffer out NOCOPY varchar2,
680 error_result out NOCOPY varchar2);
681
682 -- set_template - Set the mail template
683 -- if nothing is specify, it will clear the mail template value.
684 procedure set_template(name in varchar2 default null);
685
686 --
687 -- GetCharset (PRIVATE)
688 -- Get the character set base of the language and territory info.
689 -- NOTE
690 -- We may do more in the future to find the character set.
691 --
692 procedure GetCharset(lang in varchar2,
693 terr in varchar2 default null,
694 charset out NOCOPY varchar2);
695
696 -- GetSessionLanguage
697 -- Get the session language and territory for the
698 -- current session
699 --
700 -- OUT
701 -- Language
702 -- Territory
703 -- codeset
704 procedure GetSessionLanguage(lang out NOCOPY varchar2,
705 terr out NOCOPY varchar2,
706 codeset out NOCOPY varchar2);
707
708
709 -- GetSessionLanguage
710 -- Get the session language and territory for the
711 -- current session as well as DB char-set, if exist for the default DB char-set
712 --
713 -- OUT
714 -- Language
715 -- Territory
716 -- codeset
717 -- db_override_cs : overridden char-set if exists for DB char-set.
718 --
719 procedure GetSessionLanguage(lang out NOCOPY varchar2,
720 terr out NOCOPY varchar2,
721 codeset out NOCOPY varchar2,
722 db_override_cs out NOCOPY varchar2);
723
724
725 -- Bug 2375920
726 -- GetSignaturePolicy (PUBLIC)
727 -- Get the signature policy for the notification from
728 -- the notification attribute
729 -- IN
730 -- nid - Notification id
731 -- OUT
732 -- sig_policy - Signature policy
736
733
734 procedure GetSignaturePolicy(nid in number,
735 sig_policy out NOCOPY varchar2);
737 -- gets the size of the current LOB table
738 function getLobTableSize return number;
739
740 -- GetTemplateName (For Internal Use only)
741 -- Get the template type and name based on the status of the
742 -- notification and whether, or not, name has been overridden
743 -- in the configuration parameters or on the message definition
744 -- itself.
745 --
746 -- IN
747 -- Notification ID
748 -- Notification status
749 -- Notification Mail status
750 -- OUT
751 -- Item type for template
752 -- Message name for template
753
754 procedure getTemplateName(nid in number, n_status in varchar2,
755 n_mstatus in varchar2, t_type out NOCOPY varchar2,
756 t_name out NOCOPY varchar2);
757
758 -- ProcessSecurityPolicy
759 -- Processes the security policy for the notification. The security policy
760 -- is determined by the #WF_SECURITY_POLICY.
761 -- IN
762 -- p_nid - Notification id
763 -- OUT
764 -- p_email - Determines whether content is secure or not
765 -- p_message_name - If the notification is not to be sent through email,
766 -- suggest a template to use.
767 procedure ProcessSecurityPolicy(p_nid in number,
768 p_email out NOCOPY varchar2,
769 p_message_name out NOCOPY varchar2);
770
771 -- get_Ntf_Function_URL (For internal use only)
772 -- Returns the Applications Framework URL
773 -- IN
774 -- Notification ID
775 -- Notification Access Key
776 -- Signature policy
777 function get_Ntf_Function_URL(nid in number,
778 n_key in varchar2,
779 n_sig_policy in varchar2,
780 n_override_agent in varchar2 default null)
781 return varchar2;
782
783
784
785 -- Set_FYI_Flag (Private)
786 -- Sets a global flag to identify if the current e-mail being processed is a
787 -- FYI notification
788 -- IN
789 -- p_fyi boolean
790 procedure Set_FYI_Flag(p_fyi in boolean);
791
792 -- Get_FYI_Flag (Private)
793 -- Returns a global flag to identify if the current e-mail being processed is
794 -- a FYI notification
795 -- OUT
796 -- Boolean value
797 function Get_FYI_Flag return boolean;
798
799 -- Get_Ntf_Language (PRIVATE)
800 -- Overrides the language and territory setting for the notification based
801 -- on the #WFM_LANGUAGE and #WFM_TERRITORY attributes. If neither user's
802 -- preference nor the notification level setting are valid, the base NLS
803 -- setting is used-- IN
804 -- p_nid - Notification Id
805 -- IN OUT
806 -- p_language - NLS Language
807 -- p_territory - NLS Territory
808 -- p_codeset - NLS Codeset
809 procedure Get_Ntf_Language(p_nid in number,
810 p_language in out nocopy varchar2,
811 p_territory in out nocopy varchar2,
812 p_codeset in out nocopy varchar2);
813
814
815
816 -- Get_Ntf_Language (PRIVATE and only for WF Mailer)
817 -- Overrides the language and territory setting for the notification based
818 -- on the #WFM_LANGUAGE and #WFM_TERRITORY attributes. If neither user's
819 -- preference nor the notification level setting are valid, the base NLS
820 -- setting is used.
821 --
822 -- If overridden char-set exists for a language, p_codeset will return that.
823 -- otherwise languages's default char-set.
824 --
825 --
826 -- IN
827 -- p_nid - Notification Id
828 -- RESET_NLS parameter
829 -- IN OUT
830 -- p_language - NLS Language
831 -- p_territory - NLS Territory
832 -- p_codeset - NLS Codeset
833 -- p_is_iana_cs - if returend p_codeset is an Overriden char-set ( if any) for p_language.
834 procedure Get_Ntf_Language(p_nid in number,
835 p_language in out nocopy varchar2,
836 p_territory in out nocopy varchar2,
837 p_codeset in out nocopy varchar2,
838 p_is_iana_cs in out nocopy varchar2);
839
840
841
842
843
844 --
845 -- Generic mailer routines
846 --
847 -- Send
848 -- Sends a e-mail notification to the specified list of recipients.
849 -- This API unlike wf_notification.send does not require workflow
850 -- message or workflow roles to send a notification.
851 /*#
852 * Sends a message to be dispatched through e-mail by the Oracle Workflow
853 * Notification Mailer. The message content and recipients provided in the
854 * parameters do not need to be associated with any workflow process or
855 * directory service roles. The message content must be complete,
856 * preformatted, and email-ready, and should conform to the content type
857 * specified in the p_content_type parameter. Where possible, Oracle
858 * recommends setting only the required parameters and using the default
859 * values for the optional parameters.
860 * @param p_subject The Subject header value
861 * @paraminfo {@rep:required}
862 * @param p_message The message content
863 * @paraminfo {@rep:required}
864 * @param p_recipient_list The list of recipients to whom the message should be sent
865 * @paraminfo {@rep:innertype WF_MAIL#wf_recipient_list_t} {@rep:required}
866 * @param p_module The program or application to which the message belongs
867 * @paraminfo {@rep:required}
868 * @param p_idstring An optional identifier for the message, used in response processing
872 * @param p_territory An optional territory setting for the message
869 * @param p_from An optional From header value
870 * @param p_replyto An optional Reply-To header value
871 * @param p_language An optional language setting for the message
873 * @param p_codeset An optional codeset encoding specification for the message
874 * @param p_content_type An optional Content-Type header value
875 * @param p_callback_event The event raised to enable further processing, if needed, after the message is dispatched
876 * @param p_event_key The key that identifies the message when the message is enqueued for dispatch
877 * @param p_fyi_flag A flag to indicate whether the message is for your information (FYI) or requires a response
878 *
879 * @rep:displayname Send Message
880 * @rep:businessevent oracle.apps.wf.notification.send
881 * @rep:scope public
882 * @rep:lifecycle active
883 * @rep:compatibility S
884 *
885 */
886 procedure send(p_subject in varchar2,
887 p_message in out nocopy clob,
888 p_recipient_list in wf_recipient_list_t,
889 p_module in varchar2,
890 p_idstring in varchar2 default null,
891 p_from in varchar2 default null,
892 p_replyto in varchar2 default null,
893 p_language in varchar2 default 'AMERICAN',
894 p_territory in varchar2 default 'AMERICA',
895 p_codeset in varchar2 default 'UTF8',
896 p_content_type in varchar2 default 'text/plain',
897 p_callback_event in varchar2 default null,
898 p_event_key in varchar2 default null,
899 p_fyi_flag in varchar2 default null);
900
901
902 --
903 -- SendMoreInfoResponseWarning
904 -- procedure to send a warning notification about the answer for a More
905 -- Informantion request that has already been answered.
906 -- IN
907 -- p_nid - Notification Id
908 -- p_from_email - Email address of the responder
909
910 procedure SendMoreInfoResponseWarning(p_nid IN NUMBER,
911 p_from_email IN VARCHAR2 default null);
912
913 -- SetNtfEventsSubStatus
914 -- This procedure sets the status of seeded subscription to the event group
915 -- oracle.apps.wf.notification.send.group. This subscription is responsible
916 -- for notification XML message generation and presenting it to the mailer for
917 -- e-mail dispatch. Disabling this subscription causes e-mails not to be sent.
918 --
919 -- ENABLED - E-mails are sent
920 -- DISABLED - E-mails are not sent
921 -- IN
922 -- p_status - Subscription status (Either ENABLED or DISABLED)
923 procedure SetNtfEventsSubStatus(p_status in varchar2);
924
925 -- SetResponseDelimiters
926 -- Sets the package level variables with one procedure call. The
927 -- response delimiters are used to determine the free form text
928 -- values in email notification responses.
929 --
930 -- IN
931 -- open_text - Opening text/plain delimiter
932 -- close_text - Closing text/plain delimiter
933 -- open_html - Opening text/html delimiter
934 -- close_html - Closing text/html delimiter
935 procedure SetResponseDelimiters(open_text in varchar2,
936 close_text in varchar2,
937 open_html in varchar2,
938 close_html in varchar2);
939
940 end WF_MAIL;