1 package WF_NOTIFICATION AUTHID CURRENT_USER as
2 /* $Header: wfntfs.pls 120.27 2011/12/27 10:04:56 dsardar ship $ */
3 /*#
4 * Provides APIs to access the Oracle Workflow Notification
5 * System and manage notifications.
6 * @rep:scope public
7 * @rep:product OWF
8 * @rep:displayname Workflow Notification System
9 * @rep:lifecycle active
10 * @rep:compatibility S
11 * @rep:category BUSINESS_ENTITY WF_NOTIFICATION
12 * @rep:ihelp FND/@notif_api See the related online help
13 */
14
15 --
16 -- Type
17 --
18 type tdType is table of varchar2(4005) index by binary_integer;
19
20 --
21 -- Constant values
22 --
23
24 -- Document attribute presentation type tags
25 doc_text varchar2(30) := 'text/plain';
26 doc_html varchar2(30) := 'text/html';
27 fwk_region_start varchar2(20) := 'JSP:/OA_HTML/OA.jsp?';
28 notification_macro varchar2(20) := 'WF_NOTIFICATION';
29 fwk_mailer_page varchar2(100) := '/OA_HTML/OA.jsp?page=/oracle/apps/fnd/wf/worklist/webui/NotifMailerPG';
30
31
32 -- Cached NLS values
33 -- 64 is drawned from v$nls_valid_values view.
34 nls_language varchar2(64);
35 nls_territory varchar2(64);
36 nls_charset varchar2(64);
37
38 -- Debug
39 debug boolean := FALSE;
40
41
42 -- Bug 3065814
43 -- Global context variables for post-notification
44 g_context_user varchar2(320);
45 g_context_user_comment VARCHAR2(4000);
46 g_context_recipient_role varchar2(320);
47 g_context_original_recipient varchar2(320);
48 g_context_from_role varchar2(320);
49 g_context_new_role varchar2(320);
50 g_context_more_info_role varchar2(320);
51 g_context_proxy varchar2(320);
52
53 -- User's NLS date mask
54 g_nls_date_mask varchar2(50);
55
56 -- Bug 7476628 - Flag to indicate if WF_RENDER.XML_STYLE_SHEET should be called
57 -- in PLSQL layer
58 g_wf_render_xml_style_sheet boolean := true;
59
60 -- NTF_TABLE
61 -- Generate a "Browser Look and Feel (BLAF)" look a like table.
62 -- ADA compliance is achieved through "scope".
63 --
64 -- IN
65 -- cells - array of table cells
66 -- col - number of columns
67 -- type - V to generate a vertical table
68 -- - H to generate a horizontal table
69 -- rs - the result html code for the table
70 --
71 -- NOTE
72 -- type - Vertical table is Header always on the first column
73 -- - Horizontal table is Headers always on first row
74 --
75 -- cell has the format:
76 -- R40%:content of the cell here
77 -- ^ ^
78 -- | |
79 -- | + -- width specification
80 -- +-- align specification (L-Left, C-Center, R-Right)
81 --
82 procedure NTF_Table(cells in tdType,
83 col in pls_integer,
84 type in varchar2, -- 'V'ertical or 'H'orizontal
85 rs in out nocopy varchar2);
86 --
87 -- WF_MSG_ATTR
88 -- Create a table of message attributes
89 -- NOTE
90 -- o Considered using dynamic sql passing in attributes as a comma delimited
91 -- list. The cost of non-reusable sql may be high.
92 -- o Considered using bind variables with dynamic sql. Then we must impose
93 -- a hard limit on the number of bind variables. If a limit exceed we
94 -- need some fall back handling.
95 -- o Parsing the comma delimited list and making individual select is more
96 -- costly. But the sql will be reusable, it may end up cheaper.
97 --
98 function wf_msg_attr(nid in number,
99 attrs in varchar2,
100 disptype in varchar2)
101 return varchar2;
102
103 -- AddAttr
104 -- Add a new run-time notification attribute.
105 -- The attribute will be completely unvalidated. It is up to the
106 -- user to do any validation and insure consistency.
107 -- IN:
108 -- nid - Notification Id
109 -- aname - Attribute name
110 --
111 /*#
112 * Adds a new runtime notification attribute. Perform validation and insure
113 * consistency in the use of the attribute, as it is completely unvalidated
114 * by Oracle Workflow.
115 * @param nid Notification ID
116 * @param aname Attribute Name
117 * @rep:scope public
118 * @rep:lifecycle active
119 * @rep:displayname Add Notification Attribute
120 * @rep:compatibility S
121 * @rep:ihelp FND/@notif_api#a_addatt See the related online help
122 */
123 procedure AddAttr(nid in number,
124 aname in varchar2);
125
126
127 -- SetAttrText
128 -- Set the value of a notification attribute, given text representation.
129 -- If the attribute is a NUMBER or DATE type, then translate the
130 -- text-string value to a number/date using attribute format.
131 -- For all other types, store the value directly.
132 -- IN:
133 -- nid - Notification id
134 -- aname - Attribute Name
135 -- avalue - New value for attribute
136 --
137 /*#
138 * Sets the value of a notification text attribute at either send or respond
139 * time. The notification agent (sender) may set the value of SEND attributes.
140 * The performer (responder) may set the value of RESPOND attributes.
141 * @param nid Notification ID
142 * @param aname Attribute Name
143 * @param avalue Attribute Value
144 * @rep:scope public
145 * @rep:lifecycle active
146 * @rep:displayname Set Notification Text Attribute Value
147 * @rep:compatibility S
148 * @rep:businessevent oracle.apps.wf.notification.setattrtext
149 * @rep:ihelp FND/@notif_api#a_setatt See the related online help
150 */
151 procedure SetAttrText(nid in number,
152 aname in varchar2,
153 avalue in varchar2);
154
155
156 -- SetAttrNumber
157 -- Set the value of a number notification attribute.
158 -- Attribute must be a NUMBER-type attribute.
159 -- IN:
160 -- nid - Notification id
161 -- aname - Attribute Name
162 -- avalue - New value for attribute
163 --
164 /*#
165 * Sets the value of a notification number attribute at either send or respond
166 * time. The notification agent (sender) may set the value of SEND attributes.
167 * The performer (responder) may set the value of RESPOND attributes.
168 * @param nid Notification ID
169 * @param aname Attribute Name
170 * @param avalue Attribute Value
171 * @rep:scope public
172 * @rep:lifecycle active
173 * @rep:displayname Set Notification Number Attribute Value
174 * @rep:compatibility S
175 * @rep:ihelp FND/@notif_api#a_setatt See the related online help
176 */
177 procedure SetAttrNumber (nid in number,
178 aname in varchar2,
179 avalue in number);
180
181
182 -- SetAttrDate
183 -- Set the value of a date notification attribute.
184 -- Attribute must be a DATE-type attribute.
185 -- IN:
186 -- nid - Notification id
187 -- aname - Attribute Name
188 -- avalue - New value for attribute
189 --
190 /*#
191 * Sets the value of notification date attribute at either send or respond
192 * time. The notification agent (sender) may set the value of SEND attributes.
193 * The performer (responder) may set the value of RESPOND attributes.
194 * @param nid Notification ID
195 * @param aname Attribute Name
196 * @param avalue Attribute Value
197 * @rep:scope public
198 * @rep:lifecycle active
199 * @rep:displayname Set Notification Date Attribute Value
200 * @rep:compatibility S
201 * @rep:ihelp FND/@notif_api#a_setatt See the related online help
202 */
203 procedure SetAttrDate (nid in number,
204 aname in varchar2,
205 avalue in date);
206
207
208 -- GetAttrInfo
209 -- Get type information about a notification attribute.
210 -- IN:
211 -- nid - Notification id
212 -- aname - Attribute name
213 -- OUT:
214 -- atype - Attribute type
215 -- subtype - 'SEND' or 'RESPOND',
216 -- format - Attribute format
217 --
218 /*#
219 * Returns information about a notification attribute, such as its type,
220 * subtype, and format, if any is specified. The subtype is always SEND
221 * or RESPOND to indicate the attribute's source.
222 * @param nid Notification ID
223 * @param aname Attribute Name
224 * @param atype Attribute Type
225 * @param subtype Attribute Subtype ('SEND' or 'RESPOND')
226 * @param format Attribute Format
227 * @rep:scope public
228 * @rep:lifecycle active
229 * @rep:displayname Get Notification Attribute Information
230 * @rep:compatibility S
231 * @rep:ihelp FND/@notif_api#a_getattinfo See the related online help
232 */
233 procedure GetAttrInfo(nid in number,
234 aname in varchar2,
235 atype out nocopy varchar2,
236 subtype out nocopy varchar2,
237 format out nocopy varchar2);
238
239
240 -- GetAttrText
241 -- Get the value of a text notification attribute.
242 -- If the attribute is a NUMBER or DATE type, then translate the
243 -- number/date value to a text-string representation using attrbute format.
244 -- For all other types, get the value directly.
245 -- IN:
246 -- nid - Notification id
247 -- aname - Attribute Name
248 -- ignore_notfound - Ignore if Not Found
249 -- RETURNS:
250 -- Attribute value
251 /*#
252 * Returns the value of a specified text message attribute.
253 * @param nid Notification ID
254 * @param aname Attribute Name
255 * @param ignore_notfound Ignore if Not Found
256 * @return Text Attribute Value
257 * @rep:scope public
258 * @rep:lifecycle active
259 * @rep:displayname Get Notification Text Attribute Value
260 * @rep:compatibility S
261 * @rep:ihelp FND/@notif_api#a_getatt See the related online help
262 */
263 function GetAttrText (nid in number,
264 aname in varchar2,
265 ignore_notfound in boolean default NULL)
266 return varchar2;
267
268
269 -- GetAttrNumber
270 -- Get the value of a number notification attribute.
271 -- Attribute must be a NUMBER-type attribute.
272 -- IN:
273 -- nid - Notification id
274 -- aname - Attribute Name
275 -- RETURNS:
276 -- Attribute value
277 /*#
278 * Returns the value of a specified number message attribute.
279 * @param nid Notification ID
280 * @param aname Attribute Name
281 * @return Number Attribute Value
282 * @rep:scope public
283 * @rep:lifecycle active
284 * @rep:displayname Get Notification Number Attribute Value
285 * @rep:compatibility S
286 * @rep:ihelp FND/@notif_api#a_getatt See the related online help
287 */
288 function GetAttrNumber (nid in number,
289 aname in varchar2)
290 return number;
291
292
293 -- GetAttrDate
294 -- Get the value of a date notification attribute.
295 -- Attribute must be a DATE-type attribute.
296 -- IN:
297 -- nid - Notification id
298 -- aname - Attribute Name
299 -- RETURNS:
300 -- Attribute value
301 /*#
302 * Returns the value of a specified date message attribute.
303 * @param nid Notification ID
304 * @param aname Attribute Name
305 * @return Date Attribute Value
306 * @rep:scope public
307 * @rep:lifecycle active
308 * @rep:displayname Get Notification Date Attribute Value
309 * @rep:compatibility S
310 * @rep:ihelp FND/@notif_api#a_getatt See the related online help
311 */
312 function GetAttrDate (nid in number,
313 aname in varchar2)
314 return date;
315
316 --
317 -- GetAttrDoc
318 -- Get the displayed value of a DOCUMENT-type attribute.
319 -- Returns referenced document in format requested.
320 -- Use GetAttrText to get retrieve the actual attr value (i.e. the
321 -- document key string instead of the actual document).
322 -- NOTE:
323 -- Only PLSQL document type is implemented.
324 -- IN:
325 -- nid - Notification id
326 -- aname - Attribute Name
327 -- disptype - Requested display type. Valid values:
328 -- 'text/plain' - plain text
329 -- 'text/html' - html
330 -- '' - attachment(?)
331 -- RETURNS:
332 -- Referenced document in format requested.
333 --
334 /*#
335 * Returns the displayed value of a Document-type attribute. The
336 * referenced document appears in either plain text or HTML format, as
337 * requested. If you wish to retrieve the actual attribute value, that is,
338 * the document key string instead of the actual document, use GetAttrText().
339 * @param nid Notification ID
340 * @param aname Attribute Name
341 * @param disptype Display Type
342 * @return Document Attribute Value
343 * @rep:scope public
344 * @rep:lifecycle active
345 * @rep:displayname Get Notification Document Attribute Value
346 * @rep:compatibility S
347 * @rep:ihelp FND/@notif_api#a_gad See the related online help
348 */
349 function GetAttrDoc(
350 nid in number,
351 aname in varchar2,
352 disptype in varchar2)
353 return varchar2;
354
355 --
356 -- SetFrameworkAgent
357 -- Check the URL for a JSP: entry and then substitute
358 -- it with the value of the APPS_FRAMEWORK_AGENT
359 -- profile option.
360 -- IN:
361 -- URL - URL to be ckecked
362 -- RETURNS:
363 -- URL with Frame work agent added
364 -- NOTE:
365 -- If errors are detected this routine returns some_text untouched
366 -- instead of raising exceptions.
367 --
368 function SetFrameworkAgent(url in varchar2)
369 return varchar2;
370
371 --
372 -- GetText
373 -- Substitute tokens in an arbitrary text string.
374 -- This function may return up to 32K chars. It can NOT be used in a view
375 -- definition or in a Form. For views and forms, use GetShortText, which
376 -- truncates values at 2000 chars.
377 -- IN:
378 -- some_text - Text to be substituted
379 -- nid - Notification id of notification to use for token values
380 -- disptype - Display type ('text/plain', 'text/html', '')
381 -- RETURNS:
382 -- Some_text with tokens substituted.
383 -- NOTE:
384 -- If errors are detected this routine returns some_text untouched
385 -- instead of raising exceptions.
386 --
387 /*#
388 * Substitutes tokens in an arbitrary text string using token values from a
389 * particular notification. This function may return up to 32K characters.
390 * You cannot use this function in a view definition or in an Oracle Forms
391 * Developer form. For views and forms, use GetShortText() which truncates
392 * values at 1950 characters. If an error is detected, this function returns
393 * some_text unsubstituted rather than raise exceptions.
394 * @param some_text Text to be substituted
395 * @param nid Notification ID
396 * @param disptype Display Type
397 * @return Substituted Text
398 * @rep:scope public
399 * @rep:lifecycle active
400 * @rep:displayname Get Notification Text
401 * @rep:compatibility S
402 * @rep:ihelp FND/@notif_api#a_gettext See the related online help
403 */
404 function GetText(some_text in varchar2,
405 nid in number,
406 disptype in varchar2 default '')
407 return varchar2;
408
409 --
410 -- GetUrlText
411 -- Substitute url-style tokens (with dashes) an arbitrary text string.
412 -- This function may return up to 32K chars. It can NOT be used in a view
413 -- definition or in a Form. For views and forms, use GetShortText, which
414 -- truncates values at 2000 chars.
415 -- IN:
416 -- some_text - Text to be substituted
417 -- nid - Notification id of notification to use for token values
418 -- RETURNS:
419 -- Some_text with tokens substituted.
420 -- NOTE:
421 -- If errors are detected this routine returns some_text untouched
425 function GetUrlText(some_text in varchar2,
422 -- instead of raising exceptions.
423 --
424
426 nid in number)
427 return varchar2;
428 -- <<sstomar>> bug 8430385 : useing wf_notification_util.getCalendarDate in
429 -- wf_notification.getTextInternal()
430 --pragma restrict_references(GetUrlText, WNDS);
431
432 --
433 -- GetShortText
434 -- Substitute tokens in an arbitrary text string, limited to 2000 chars.
435 -- This function is meant to be used in view definitions and Forms, where
436 -- the field size must be limited to 2000 chars. Use GetText() to retrieve
437 -- up to 32K if the text may be longer.
438 -- IN:
439 -- some_text - Text to be substituted
440 -- nid - Notification id of notification to use for token values
441 -- RETURNS:
442 -- Some_text with tokens substituted.
443 -- NOTE:
444 -- If errors are detected this routine returns some_text untouched
445 -- instead of raising exceptions.
446 --
447 /*#
448 * Substitutes tokens in an arbitrary text string using token values from a
449 * particular notification. This function may return up to 1950 characters.
450 * This function is meant for use in view definitions and Oracle Forms
451 * Developer forms, where the field size is limited to 1950 characters. Use
452 * GetText() in other situations where you need to retrieve up to 32K
453 * characters. If an error is detected, this function returns some_text
454 * unsubstituted rather than raise exceptions.
455 * @param some_text Text to be substituted
456 * @param nid Notification ID
457 * @return Substituted Text
458 * @rep:scope public
459 * @rep:lifecycle active
460 * @rep:displayname Get Notification Short Text
461 * @rep:compatibility S
462 * @rep:ihelp FND/@notif_api#a_gst See the related online help
463 */
464 function GetShortText(some_text in varchar2,
465 nid in number)
466 return varchar2;
467 -- <<sstomar>> bug 8430385 : useing wf_notification_util.getCalendarDate in
468 -- wf_notification.getTextInternal()
469 --pragma restrict_references(GetShortText, WNDS);
470
471 --
472 -- GetSubject
473 -- Get subject of notification message with token values substituted
474 -- from notification attributes.
475 -- IN:
476 -- nid - Notification Id
477 -- RETURNS:
478 -- Substituted message subject
479 -- NOTE:
480 -- If errors are detected this routine returns the subject unsubstituted,
481 -- or null if all else fails, instead of raising exceptions. It must do
482 -- this so the routine can be pragma'd and used in the
483 -- wf_notifications_view view.
484 --
485 /*#
486 * Returns the subject line for the notification message. Any message
487 * attribute in the subject is token substituted with the value of the
488 * corresponding message attribute.
489 * @param nid Notification ID
490 * @return Subject Text
491 * @rep:scope public
492 * @rep:lifecycle active
493 * @rep:displayname Get Notification Subject
494 * @rep:compatibility S
495 * @rep:ihelp FND/@notif_api#a_getsubj See the related online help
496 */
497 function GetSubject(
498 nid in number)
499 return varchar2;
500 --pragma restrict_references(GetSubject, WNDS);
501
502 --
503 -- GetBody
504 -- Get body of notification message with token values substituted
505 -- from notification attributes.
506 -- This function may return up to 32K chars. It can NOT be used in a view
507 -- definition or in a Form. For views and forms, use GetShortBody, which
508 -- truncates values at 2000 chars.
509 -- IN:
510 -- nid - Notification Id
511 -- disptype - Display type ('text/plain', 'text/html', '')
512 -- RETURNS:
513 -- Substituted message body
514 -- NOTE:
515 -- If errors are detected this routine returns the body unsubstituted,
516 -- or null if all else fails, instead of raising exceptions.
517 --
518 /*#
519 * Returns the HTML or plain text message body for the notification,
520 * depending on the message body type specified. Any message attribute
521 * in the body is token substituted with the value of the corresponding
522 * notification attribute. This function may return up to 32K characters.
523 * You cannot use this function in view definitions or in Oracle
524 * Developer forms. For views and forms, use GetShortBody( ) which
525 * truncates values at 1950 characters. Note that the returned plain text
526 * message body is not formatted, it should be wordwrapped as appropriate
527 * for the output device. Body text may contain tabs (which indicate
528 * indentation) and newlines (which indicate paragraph termination).
529 * @param nid Notification ID
530 * @param disptype Display Type
531 * @return Notification Body
532 * @rep:scope public
533 * @rep:lifecycle active
534 * @rep:displayname Get Notification Body
535 * @rep:compatibility S
536 * @rep:ihelp FND/@notif_api#a_getbody See the related online help
537 */
538 function GetBody(
539 nid in number,
540 disptype in varchar2 default '')
541 return varchar2;
542
543 --
544 -- GetShortBody
545 -- Get body of notification message with token values substituted
546 -- from notification attributes.
547 -- This function is meant to be used in view definitions and Forms, where
551 -- nid - Notification Id
548 -- the field size must be limited to 2000 chars. Use GetBody() to retrieve
549 -- up to 32K if the text may be longer.
550 -- IN:
552 -- RETURNS:
553 -- Substituted message body
554 -- NOTE:
555 -- If errors are detected this routine returns the body unsubstituted,
556 -- or null if all else fails, instead of raising exceptions. It must do
557 -- this so the routine can be pragma'd and used in the
558 -- wf_notifications_view view.
559 --
560 /*#
561 * Returns the message body for the notification. Any message attribute
562 * in the body is token substituted with the value of the corresponding
563 * notification attribute. This function may return up to 1950 characters.
564 * This function is meant for use in view definitions and Oracle Developer
565 * forms, where the field size is limited to 1950 characters. Use GetBody()
566 * in other situations where you need to retrieve up to 32K characters.
567 * Note that the returned plain text message body is not formatted; it
568 * should be wordwrapped as appropriate for the output device. Body text
569 * may contain tabs (which indicate indentation) and newlines (which
570 * indicate paragraph termination). If an error is detected, this function
571 * returns the body unsubstituted or null if all else fails, rather than
572 * raise exceptions.
573 * @param nid Notification ID
574 * @return Notification Body
575 * @rep:scope public
576 * @rep:lifecycle active
577 * @rep:displayname Get Notification Short Body
578 * @rep:compatibility S
579 * @rep:ihelp FND/@notif_api#a_gsb See the related online help
580 */
581 function GetShortBody(nid in number)
582 return varchar2;
583 -- <<sstomar>> bug 8430385 : useing wf_notification_util.getCalendarDate in
584 -- wf_notification.getTextInternal()
585 --pragma restrict_references(GetShortBody, WNDS);
586
587 --
588 -- GetInfo
589 -- Return info about notification
590 -- IN
591 -- nid - Notification Id
592 -- OUT
593 -- role - Role notification is sent to
594 -- message_type - Type flag of message
595 -- message_name - Message name
596 -- priority - Notification priority
597 -- due_date - Due date
598 -- status - Notification status (OPEN, CLOSED, CANCELED)
599 --
600 /*#
601 * Returns the role that the notification is sent to, the item type of the
602 * message, the name of the message, the notification priority, the due
603 * date and the status for the specified notification.
604 * @param nid Notification ID
605 * @param role Recipient Role
606 * @param message_type Message Type
607 * @param message_name Message Name
608 * @param priority Priority
609 * @param due_date Due Date
610 * @param status Status
611 * @rep:scope public
612 * @rep:lifecycle active
613 * @rep:displayname Get Notification Information
614 * @rep:compatibility S
615 * @rep:ihelp FND/@notif_api#a_getinfo See the related online help
616 */
617 procedure GetInfo(nid in number,
618 role out nocopy varchar2,
619 message_type out nocopy varchar2,
620 message_name out nocopy varchar2,
621 priority out nocopy varchar2,
622 due_date out nocopy varchar2,
623 status out nocopy varchar2);
624
625 --
626 -- Responder
627 -- Return responder of closed notification.
628 -- IN
629 -- nid - Notification Id
630 -- RETURNS
631 -- Responder to notification. If no responder was set or notification
632 -- not yet closed, return null.
633 --
634 /*#
635 * Returns the responder of a closed notification. If the notification was
636 * closed using the Web Notification interface the value returned will be a
637 * valid role defined in the view WF_ROLES. If the Notification was closed
638 * using the e-mail interface then the value returned will be an e-mail address.
639 * @param nid Notification ID
640 * @return Responder
641 * @rep:scope public
642 * @rep:lifecycle active
643 * @rep:displayname Get Notification Responder
644 * @rep:compatibility S
645 * @rep:ihelp FND/@notif_api#responder See the related online help
646 */
647 function Responder(
648 nid in number)
649 return varchar2;
650
651 -- AccessCheck
652 -- Check that the notification is open and access key is valid.
653 -- IN
654 -- Access string <nid>/<nkey>
655 -- RETURNS
656 -- user name (if notificaiton is open and key is valid)
657 -- othersise null
658 /*#
659 * Returns a username if the notification access string is valid and the
660 * notification is open, otherwise it returns null. The access string is
661 * automatically generated by the notification mailer that sends the
662 * notification and is used to verify the authenticity of both text and
663 * HTML versions of e-mail notifications.
664 * @param access_str Access String
665 * @return Username
666 * @rep:scope public
667 * @rep:lifecycle active
668 * @rep:displayname Check Notification Access
669 * @rep:compatibility S
670 * @rep:ihelp FND/@notif_api#a_acccheck See the related online help
671 */
672 function AccessCheck(access_str in varchar2) return varchar2;
673 pragma restrict_references(AccessCheck, WNDS);
674
675 --
676 -- Send
677 -- Send the role the specified message.
681 -- role - Role to send notification to
678 -- Insert a single notification in the notifications table, and set
679 -- the default send and respond attributes for the notification.
680 -- IN:
682 -- msg_type - Message type
683 -- msg_name - Message name
684 -- due_date - Date due
685 -- callback - Callback function
686 -- context - Data for callback
687 -- send_comment - Comment to add to notification
688 -- priority - Notification priority
689 -- RETURNS:
690 -- Notification Id
691 --
692 /*#
693 * Sends the specified message to a role, returning a notification ID if
694 * successful. The notification ID must be used in all future references
695 * to the notification. If your message has message attributes, the procedure
696 * looks up the values of the attributes from the message attribute table or
697 * it can use an optionally supplied callback interface function to get the
698 * value from the item type attributes table.
699 * @param role Recipient Role
700 * @param msg_type Message Type
701 * @param msg_name Message Name
702 * @param due_date Due Date
703 * @param callback Callback Function
704 * @param context Context
705 * @param send_comment Send Comment
706 * @param priority Priority
707 * @return Notification ID
708 * @rep:scope public
709 * @rep:lifecycle active
710 * @rep:displayname Send Notification
711 * @rep:compatibility S
712 * @rep:ihelp FND/@notif_api#a_send See the related online help
713 */
714 function Send(role in varchar2,
715 msg_type in varchar2,
716 msg_name in varchar2,
717 due_date in date default null,
718 callback in varchar2 default null,
719 context in varchar2 default null,
720 send_comment in varchar2 default null,
721 priority in number default null)
722 return number;
723
724 --
725 -- SendGroup
726 -- Send the role users the specified message.
727 -- Send a separate notification to every user assigned to the role.
728 -- IN:
729 -- role - Role of users to send notification to
730 -- msg_type - Message type
731 -- msg_name - Message name
732 -- due_date - Date due
733 -- callback - Callback function
734 -- context - Data for callback
735 -- send_comment - Comment to add to notification
736 -- priority - Notification priority
737 -- RETURNS:
738 -- Group ID - Id of notification group
739 --
740 /*#
741 * Sends a separate notification to all the users assigned to a specific role
742 * and returns a number called a notification group ID, if successful. The
743 * notification group ID identifies that group of users and the notification
744 * they each received. If your message has message attributes, the procedure
745 * looks up the values of the attributes from the message attribute table or
746 * it can use an optionally supplied callback interface function to get the
747 * value from the item type attributes table.
748 * @param role Recipient Role
749 * @param msg_type Message Type
750 * @param msg_name Message Name
751 * @param due_date Due Date
752 * @param callback Callback Function
753 * @param context Context
754 * @param send_comment Send Comment
755 * @param priority Priority
756 * @return Notification Group ID
757 * @rep:scope public
758 * @rep:lifecycle active
759 * @rep:displayname Send Group Notification
760 * @rep:compatibility S
761 * @rep:ihelp FND/@notif_api#a_sndgrp See the related online help
762 */
763 function SendGroup(role in varchar2,
764 msg_type in varchar2,
765 msg_name in varchar2,
766 due_date in date default null,
767 callback in varchar2 default null,
768 context in varchar2 default null,
769 send_comment in varchar2 default null,
770 priority in number default null)
771 return number;
772
773 --
774 -- Forward
775 -- Forward a notification, identified by NID to another user. Validate
776 -- the user and Return error messages ...
777 -- IN:
778 -- nid - Notification Id
779 -- new_role - Role to forward notification to
780 -- forward_comment - comment to append to notification
781 -- user - role who perform this action if provided
782 -- cnt - count for recursive purpose
783 -- action_source - From where the procedure is called from
784 -- RULE - Routing Rule
785 -- WA - By Proxy User thro' Worklist Access
786 -- ADMIN - Status Monitor
787 --
788 /*#
789 * Delegates a notification to a new role to perform work, even though
790 * the original role recipient still maintains ownership of the notification
791 * activity. Also implicitly calls the Callback function specified in the
792 * Send or SendGroup function with FORWARD mode. A comment can be supplied
793 * to explain why the forward is taking place.
794 * @param nid Notification ID
795 * @param new_role New Recipient Role for the Notification
796 * @param forward_comment Forwarding Comment
797 * @param user For Internal Use Only
798 * @param cnt For Internal Use Only
799 * @param action_source For Internal Use Only
800 * @rep:scope public
801 * @rep:lifecycle active
802 * @rep:displayname Forward Notification
806 */
803 * @rep:compatibility S
804 * @rep:businessevent oracle.apps.wf.notification.reassign
805 * @rep:ihelp FND/@notif_api#a_forward See the related online help
807 procedure Forward(nid in number,
808 new_role in varchar2,
809 forward_comment in varchar2 default null,
810 user in varchar2 default null,
811 cnt in number default 0,
812 action_source in varchar2 default null);
813
814 --
815 -- Transfer
816 -- Transfer a notification, identified by NID to another user. Validate
817 -- the user and Return error messages ...
818 -- IN:
819 -- nid - Notification Id
820 -- new_role - Role to transfer notification to
821 -- forward_comment - comment to append to notification
822 -- user - role who perform this action if provided
823 -- cnt - count for recursive purpose
824 -- action_source - From where the procedure is called from
825 -- RULE - Routing Rule
826 -- WA - By Proxy User thro' Worklist Access
827 -- ADMIN - Status Monitor
828 --
829 /*#
830 * Forwards a notification to a new role and transfers ownership of the
831 * notification to the new role. It also implicitly calls the Callback
832 * function specified in the Send or SendGroup function with TRANSFER mode.
833 * A comment can be supplied to explain why the forward is taking place.
834 * @param nid Notification ID
835 * @param new_role New Recipient Role for the Notification
836 * @param forward_comment Forwarding Comment
837 * @param user For Internal Use Only
838 * @param cnt For Internal Use Only
839 * @param action_source For Internal Use Only
840 * @rep:scope public
841 * @rep:lifecycle active
842 * @rep:displayname Transfer Notification
843 * @rep:compatibility S
844 * @rep:businessevent oracle.apps.wf.notification.reassign
845 * @rep:ihelp FND/@notif_api#a_transfer See the related online help
846 */
847 procedure Transfer(nid in number,
848 new_role in varchar2,
849 forward_comment in varchar2 default null,
850 user in varchar2 default null,
851 cnt in number default 0,
852 action_source in varchar2 default null);
853
854 --
855 -- Cancel
856 -- Cancel a single notification.
857 -- IN:
858 -- nid - Notification Id
859 -- cancel_comment - Comment to append to notification
860 --
861 /*#
862 * Cancels a notification. The notification status is then changed to
863 * 'CANCELED' but the row is not removed from the WF_NOTIFICATIONS table
864 * until a purge operation is performed. If the notification was delivered
865 * via e-mail and expects a response, a 'Canceled' e-mail is sent to the
866 * original recipient as a warning that the notification is no longer valid.
867 * @param nid Notification ID
868 * @param cancel_comment Cancel Comment
869 * @rep:scope public
870 * @rep:lifecycle active
871 * @rep:displayname Cancel Notification
872 * @rep:compatibility S
873 * @rep:businessevent oracle.apps.wf.notification.cancel
874 * @rep:ihelp FND/@notif_api#a_cancel See the related online help
875 */
876 procedure Cancel(nid in number,
877 cancel_comment in varchar2 default null);
878
879 --
880 -- CancelGroup
881 -- Cancel all notifications belonging to a notification group
882 -- IN:
883 -- gid - Notification group id
884 -- cancel_comment - Comment to append to all notifications
885 --
886 /*#
887 * Cancels the individual copies of a specific notification sent to all users in
888 * a notification group. The notifications are identified by the notification
889 * Group ID (gid). The notification status is then changed to 'CANCELED' but the
890 * rows are not removed from the WF_NOTIFICATIONS table until a purge operation
891 * is performed. If the notification was delivered via e-mail and expects a
892 * response, a 'Canceled' e-mail is sent to the original recipient as a warning
893 * that the notification is no longer valid.
894 * @param gid Notification Group ID
895 * @param cancel_comment Cancel Comment
896 * @param timeout Timeout
897 * @rep:scope public
898 * @rep:lifecycle active
899 * @rep:displayname Cancel Group Notification
900 * @rep:compatibility S
901 * @rep:businessevent oracle.apps.wf.notification.cancel
902 * @rep:ihelp FND/@notif_api#a_cnclgrp See the related online help
903 */
904 procedure CancelGroup(gid in number,
905 cancel_comment in varchar2 default null,
906 timeout in boolean default FALSE);
907
908 --
909 -- Respond
910 -- Respond to a notification.
911 -- ER 10177347: Moved its code to Respond2 and Complete APIs
912 -- IN:
913 -- nid - Notification Id
914 -- respond_comment - Comment to append to notification
915 -- responder - User or role responding to notification
916 -- action_source - From where the procedure is called from
917 -- RULE - Routing Rule
918 -- WA - By Proxy User thro' Worklist Access
919 -- ADMIN - Status Monitory
920 --
921 /*#
922 * Completes the response to the notification when the performer applies the
923 * response. The procedure marks the notification as 'CLOSED' and communicates
927 * @param nid Notification ID
924 * RESPOND attributes back to the database via the callback function (if supplied).
925 * This procedure also accepts the name of the individual who actually responded
926 * to the notification and stores in the RESPONDER column of WF_NOTIFICATIONS table.
928 * @param respond_comment Respond Comment
929 * @param responder Performer who responded to the notification
930 * @param action_source For Internal Use Only
931 * @rep:scope public
932 * @rep:lifecycle active
933 * @rep:displayname Respond to Notification
934 * @rep:compatibility S
935 * @rep:businessevent oracle.apps.wf.notification.respond
936 * @rep:ihelp FND/@notif_api#a_respond See the related online help
937 */
938 procedure Respond(nid in number,
939 respond_comment in varchar2 default null,
940 responder in varchar2 default null,
941 action_source in varchar2 default null);
942
943 --
944 -- TestContext
945 -- Test if current context is correct
946 -- IN
947 -- nid - Notification id
948 -- RETURNS
949 -- TRUE if context ok, or context check not implemented
950 -- FALSE if context check fails
951 --
952 /*#
953 * Tests if the current context is correct by calling the Item Type
954 * Selector/Callback function. This function returns TRUE if the context
955 * check is OK, or if no Selector/Callback function is implemented. It
956 * returns FALSE if the context check fails.
957 * @param nid Notification ID
958 * @return True if check is OK, False otherwise
959 * @rep:scope public
960 * @rep:lifecycle active
961 * @rep:displayname Test Context
962 * @rep:compatibility S
963 * @rep:ihelp FND/@notif_api#a_tc See the related online help
964 */
965 function TestContext(
966 nid in number)
967 return boolean;
968
969 --
970 --
971 -- VoteCount
972 -- Count the number of responses for a result_code
973 -- IN:
974 -- Gid - Notification group id
975 -- ResultCode - Result code to be tallied
976 -- OUT:
977 -- ResultCount - Number of responses for ResultCode
978 -- PercentOfTotalPop - % ResultCode ( As a % of total population )
979 -- PercentOfVotes - % ResultCode ( As a % of votes cast )
980 --
981 /*#
982 * Counts the number of responses for a specified result code.
983 * @param Gid Notification Group ID
984 * @param ResultCode Result code to be tallied
985 * @param ResultCount Vote Count
986 * @param PercentOfTotalPop Percent of total population
987 * @param PercentOfVotes Percent of votes cast
988 * @rep:scope public
989 * @rep:lifecycle active
990 * @rep:displayname Vote Count
991 * @rep:compatibility S
992 * @rep:ihelp FND/@notif_api#a_votec See the related online help
993 */
994 procedure VoteCount ( Gid in number,
995 ResultCode in varchar2,
996 ResultCount out nocopy number,
997 PercentOfTotalPop out nocopy number,
998 PercentOfVotes out nocopy number );
999 --
1000 -- OpenNotificationsExist
1001 -- Determine if any Notifications in the Group are OPEN
1002 --
1003 --IN:
1004 -- Gid - Notification group id
1005 --
1006 --Returns:
1007 -- TRUE - if the Group contains open notifications
1008 -- FALSE - if the group does NOT contain open notifications
1009 --
1010 /*#
1011 * Returns 'TRUE' if any notification associated with the specified notification
1012 * group ID is 'OPEN', otherwise it returns 'FALSE'.
1013 * @param Gid Notification Group ID
1014 * @return Notification Open Status
1015 * @rep:scope public
1016 * @rep:lifecycle active
1017 * @rep:displayname Open Notifications Exist
1018 * @rep:compatibility S
1019 * @rep:ihelp FND/@notif_api#a_opennt See the related online help
1020 */
1021 function OpenNotificationsExist( Gid in Number ) return Boolean;
1022
1023 --
1024 -- WorkCount
1025 -- Count number of open notifications for user
1026 -- IN:
1027 -- username - user to check
1028 -- RETURNS:
1029 -- Number of open notifications for that user
1030 --
1031 /*#
1032 * Returns the number of open notifications assigned to a role.
1033 * @param username Recipient Role
1034 * @return Open Notification Count
1035 * @rep:scope public
1036 * @rep:lifecycle active
1037 * @rep:displayname Work Count
1038 * @rep:compatibility S
1039 * @rep:ihelp FND/@notif_api#a_wct See the related online help
1040 */
1041 function WorkCount(
1042 username in varchar2)
1043 return number;
1044
1045 --
1046 -- Close
1047 -- Close a notification.
1048 -- IN:
1049 -- nid - Notification Id
1050 -- resp - Respond Required? 0 - No, 1 - Yes
1051 -- responder - User or role close this notification
1052 --
1053 /*#
1054 * Closes a notification.
1055 * @param nid Notification ID
1056 * @param responder Role performing close
1057 * @rep:scope public
1058 * @rep:lifecycle active
1059 * @rep:displayname Close Notification
1060 * @rep:compatibility S
1061 * @rep:businessevent oracle.apps.wf.notification.close
1062 * @rep:ihelp FND/@notif_api#a_close See the related online help
1063 */
1064 procedure Close(nid in number,
1068 -- Get the design subject of a notification and Substitute tokens in text
1065 responder in varchar2 default null);
1066
1067 -- GetSubSubjectDisplay
1069 -- with the display name of the attributes in the subject.
1070 -- This is used in routing rule poplists
1071 -- IN:
1072 -- message_type - Item type of the message
1073 -- message_name - Name of the message to substitute
1074 --
1075 function GetSubSubjectDisplay(message_type IN VARCHAR2, message_name IN VARCHAR2)
1076 return varchar2;
1077
1078 -- GetSubSubjectDisplayShort
1079 -- Get the design subject of a notification and Substitute tokens in text
1080 -- with ellipsis (...)
1081 -- This is used in routing rule poplists on the Web screens
1082 -- IN:
1083 -- message_type - Item type of the message
1084 -- message_name - Name of the message to substitute
1085 --
1086 function GetSubSubjectDisplayShort(message_type IN VARCHAR2, message_name IN VARCHAR2)
1087 return varchar2;
1088
1089 -- PLSQL-Clob Processing
1090
1091 --Name : GetFullBody (PUBLIC)
1092 --Desc : Gets full body of message with all PLSQLCLOB variables transalted.
1093 -- and returns the message in 32K chunks in the msgbody out variable.
1094 -- Call this repeatedly until end_of_body is true.
1095 -- Call syntax is
1096 --while not (end_of_msgbody) loop
1097 -- wf_notification.getfullbody(nid,msgbody,end_of_msgbody);
1098 --end loop;
1099 procedure GetFullBody (nid in number,
1100 msgbody out nocopy varchar2,
1101 end_of_body in out nocopy boolean,
1102 disptype in varchar2 default 'text/plain');
1103
1104 --Name: GetFullBodyWrapper (PUBLIC)
1105 --Desc : Gets full body of message with all PLSQLCLOB variables transalted.
1106 -- and returns the message in 32K chunks in the msgbody out variable.
1107 -- Call this repeatedly until end_of_body is "Y". Uses string arg
1108 -- instead of boolean like GetFullBody for end_of_msg_body. Created
1109 -- since booleans cannot be passed via JDBC.
1110 -- Call syntax is
1111 --while (end_of_msgbody <> "Y") loop
1112 -- wf_notification.getfullbody(nid,msgbody,end_of_msgbody);
1113 --end loop;
1114 procedure GetFullBodyWrapper (nid in number,
1115 msgbody out nocopy varchar2,
1116 end_of_body out nocopy varchar2,
1117 disptype in varchar2 default 'text/plain');
1118
1119 --Name WriteToClob (PUBLIC)
1120 /*#
1121 * Appends a character string to the end of a character large object
1122 * (CLOB). You can use this procedure to help build the CLOB for a
1123 * PL/SQL CLOB document attribute for a notification.
1124 * @param clob_loc CLOB Locator
1125 * @param msg_string Message string to append
1126 * @rep:scope public
1127 * @rep:lifecycle active
1128 * @rep:displayname Write to CLOB
1129 * @rep:compatibility S
1130 * @rep:ihelp FND/@notif_api#a_wrtclob See the related online help
1131 */
1132 procedure WriteToClob ( clob_loc in out nocopy clob,
1133 msg_string in varchar2);
1134
1135 --
1136 -- GetAttrClob
1137 -- Get the displayed value of a PLSQLCLOB DOCUMENT-type attribute.
1138 -- Returns referenced document in format requested.
1139 -- Use GetAttrText to get retrieve the actual attr value (i.e. the
1140 -- document key string instead of the actual document).
1141 -- NOTE:
1142 -- Only PLSQL document type is implemented.
1143 -- IN:
1144 -- nid - Notification id
1145 -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
1146 -- disptype - Requested display type. Valid values:
1147 -- wf_notification.doc_text - 'text/plain'
1148 -- wf_notification.doc_html - 'text/html'
1149 -- document - The clob into which
1150 -- aname - Attribute Name (the first part of the string that matches
1151 -- the attr list)
1152 --
1153 procedure GetAttrClob(
1154 nid in number,
1155 astring in varchar2,
1156 disptype in varchar2,
1157 document in out nocopy clob,
1158 aname out nocopy varchar2);
1159
1160 --
1161 -- GetAttrClob
1162 -- Get the displayed value of a PLSQLCLOB DOCUMENT-type attribute.
1163 -- Returns referenced document in format requested.
1164 -- Use GetAttrText to get retrieve the actual attr value (i.e. the
1165 -- document key string instead of the actual document).
1166 -- The document type of the PLSQLCLOB document is returned from the
1167 -- user-defined API
1168 -- NOTE:
1169 -- Only PLSQL document type is implemented.
1170 -- IN:
1171 -- nid - Notification id
1172 -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
1173 -- disptype - Requested display type. Valid values:
1174 -- wf_notification.doc_text - 'text/plain'
1175 -- wf_notification.doc_html - 'text/html'
1176 -- document - Th clob into which
1177 -- aname - Attribute Name (the first part of the string that matches
1178 -- the attr list)
1179 --
1180 procedure GetAttrClob(
1181 nid in number,
1182 astring in varchar2,
1183 disptype in varchar2,
1184 document in out nocopy clob,
1185 doctype out nocopy varchar2,
1186 aname out nocopy varchar2);
1187
1188 --Name Read_Clob
1189 --reads a specific clob in 32K chunks. Call this repeatedly until
1193
1190 --end_of_clob is true.
1191 procedure read_clob (line out nocopy varchar2 ,
1192 end_of_clob in out nocopy boolean);
1194 -- Name: NewClob
1195 -- Creates a new record in the temp table with a clob
1196 -- this is necessary because clobs cannot reside in plsql
1197 -- but must be part of a table.
1198 procedure NewClob (clobloc in out nocopy clob,
1199 msg_string in varchar2);
1200
1201
1202 --Name ReadAttrClob (PUBLIC)
1203 --Desc : Gets full text of a PLSQLCLOB variable
1204 -- and returns the 32K chunks in the doctext out variable.
1205 -- Call this repeatedly until end_of_text is true.
1206 --USE : use this to get the value of idividual PLSQLCLOBs such as attachments.
1207 -- to susbtitute a PLSQLSQL clob into a message body, use GetFullBody
1208 procedure ReadAttrClob(nid in number,
1209 aname in varchar2,
1210 doctext in out nocopy varchar2,
1211 end_of_text in out nocopy boolean);
1212
1213 --variable used in clob manipulation
1214 last_nid pls_integer;
1215 last_disptype varchar(30);
1216 clob_exists pls_integer;
1217 clob_chunk pls_integer:=0;
1218 temp_clob clob;
1219
1220
1221 --
1222 -- Denormalization of Notifications
1223 --
1224
1225 --
1226 -- GetSessionLanguage (PRIVATE)
1227 -- Try to return the cached session language value.
1228 -- If it is not cached yet, call the real query function.
1229 --
1230 function GetSessionLanguage
1231 return varchar2;
1232
1233 --
1234 -- GetNLSLanguage (PRIVATE)
1235 -- Get the NLS Lanugage setting of current session
1236 -- Try to cached the value for future use.
1237 -- NOTE:
1238 -- Because it tried to use cached values first. The subsequent calls
1239 -- will give you the cached values instead of the current value.
1240 --
1241 procedure GetNLSLanguage(language out nocopy varchar2,
1242 territory out nocopy varchar2,
1243 charset out nocopy varchar2);
1244
1245 --
1246 -- Denormalize_Notification
1247 -- Populate the donormalized value to WF_NOTIFICATIONS table according
1248 -- to the language setting of username provided.
1249 -- IN:
1250 -- nid - Notification id
1251 -- username - optional role name, if not provided, use the
1252 -- recipient_role of the notification.
1253 -- langcode - language code
1254 --
1255 -- NOTE: username has precedence over langcode. Either username or
1256 -- langcode is needed.
1257 --
1258 /*#
1259 * Stores denormalized values for certain notification fields, including the
1260 * notification subject, in the WF_NOTIFICATIONS table. If you are using
1261 * the Notification System to send a notification outside of a workflow
1262 * process, you must call Denormalize_Notification() after setting the values
1263 * for any notification attributes, in order to populate the denormalized
1264 * fields. This procedure tests whether the language in which the notification
1265 * should be delivered matches the current session language, and stores the
1266 * denormalized information according to this setting only if the languages match.
1267 * @param nid Notification ID
1268 * @param username Recipient Role
1269 * @param langcode Language Code
1270 * @rep:scope public
1271 * @rep:lifecycle active
1272 * @rep:displayname Denormalize Notification
1273 * @rep:compatibility S
1274 * @rep:ihelp FND/@notif_api#a_dnrm See the related online help
1275 */
1276 procedure Denormalize_Notification(nid in number,
1277 username in varchar2 default null,
1278 langcode in varchar2 default null);
1279
1280 --
1281 -- NtfSignRequirementsMet
1282 -- Checks if the notification's singature requirements are met
1283 -- IN
1284 -- nid - notification id
1285 -- OUT
1286 -- true - if the ntf is signed
1287 -- false - if the ntf is not signed
1288 --
1289 /*#
1290 * Returns 'TRUE' if the response to a notification meets the signature
1291 * requirements imposed by the electronic signature policy for the
1292 * notification.
1293 * @param nid Notification ID
1294 * @return Signature Status
1295 * @rep:scope public
1296 * @rep:lifecycle active
1297 * @rep:displayname Notification Signature Requirements Met
1298 * @rep:compatibility S
1299 * @rep:ihelp FND/@notif_api#a_signmet See the related online help
1300 */
1301 function NtfSignRequirementsMet(nid in number)
1302 return boolean;
1303
1304 --
1305 -- Request More Info
1306 --
1307
1308 --
1309 -- UpdateInfo
1310 -- non-null username: Ask this user for more information
1311 -- null username: Reply to the inquery
1312 -- comment could be question or answer
1313 -- IN
1314 -- nid - Notification Id
1315 -- username - User to whom the comment is intended
1316 -- comment - Comment text
1317 -- wl_user - Worklist user to whom the notfication belongs, in case a proxy is acting
1318 -- action_source - Source from where the call is made. Could be null or 'WA'
1319 --
1320 procedure UpdateInfo(nid in number,
1321 username in varchar2 default null,
1322 comment in varchar2 default null,
1323 wl_user in varchar2 default null,
1324 action_source in varchar2 default null,
1328 -- Transfer Request Information
1325 cnt in number default 0);
1326
1327 --
1329 --
1330
1331 --
1332 -- TransferMoreInfo
1333 -- NOTE:
1334 -- This API is used to Transfer Request More Information notification.
1335 -- A Recipient or a Workflow Admin can transfer Request More Info Notification
1336 -- to any other user.
1337 -- IN
1338 -- p_nid - Notification Id
1339 -- p_new_user - User to whom the Question is Transferred
1340 -- p_comment - Comment text while Transfer Request MorInformation
1341 -- p_p_wl_user - Worklist user to whom the notfication belongs, in case a proxy is acting
1342 -- p_action_source - Source from where the call is made. Could be null or 'WA'
1343 -- p_count - Count used for recursive calls when there are vacation rules set recursively
1344 -- p_routing_rule_user - User for which Routing rule is present.
1345 --
1346 procedure TransferMoreInfo(p_nid in number,
1347 p_new_user in varchar2 default null,
1348 p_comment in varchar2 default null,
1349 p_wl_user in varchar2 default null,
1350 p_action_source in varchar2 default null,
1351 p_count in number default 0,
1352 p_routing_rule_user in varchar2 default null);
1353
1354 --
1355 -- Route Request Information
1356 --
1357
1358 --
1359 -- RouteMoreInfo
1360 -- This API checks whether there is any Routing rule defined for a user
1361 -- and transfers the Request More Information if there is one by calling
1362 -- TransferMoreInfo API recursively.
1363 -- IN
1364 -- p_nid - Notification Id
1365 -- p_wl_user - Worklist user to whom the notfication belongs, in case a proxy is acting
1366 -- p_action_source - Source from where the call is made. Could be null or 'WA'
1367 -- p_count - Count used for recursive calls when there are vacation rules set recursively
1368 --
1369 procedure RouteMoreInfo(p_nid in number,
1370 p_wl_user in varchar2, -- Logged in User (Can be a proxy user too)
1371 p_action_source in varchar2,
1372 p_count in number);
1373
1374 --
1375 -- IsValidInfoRole
1376 -- Check to see if a role is a participant so far
1377 function IsValidInfoRole(nid in number,
1378 username in varchar2)
1379 return boolean;
1380
1381 --
1382 -- More Info mailer support - bug 2282139
1383 --
1384 -- UpdateInfo2 - Called from mailer
1385 -- non-null username - Ask this user for more information
1386 -- null username - Reply to the inquery
1387 -- from email - From email id of responder/requestor
1388 -- comment - could be question or answer
1389 --
1390 procedure UpdateInfo2(nid in number,
1391 username in varchar2 default null,
1392 from_email in varchar2 default null,
1393 comment in varchar2 default null);
1394
1395 -- UpdateInfoGuest - Called for updating more info when access
1396 -- key is present
1397 -- responder - Sesson user responding to More info request
1398 -- moreinfoanswer - could be question or answer
1399 --
1400 procedure UpdateInfoGuest(nid in number,
1401 moreinforesponder in varchar2 default null,
1402 moreinfoanswer in varchar2 default null);
1403
1404 --
1405 -- HideMoreInfo
1406 -- Checks the notification attribute #HIDE_MOREINFO to see if the
1407 -- More Information request button is allowed or hidden. Just in case
1408 -- more_info_role becomes not null with direct table update...
1409 function HideMoreInfo(nid in number) return varchar2;
1410
1411 --
1412 -- GetComments
1413 -- Consolidates the questions and answers asked for the notification.
1414 -- Also returns the last question asked
1415 -- This is for the mailer to send the history with the email.
1416 procedure GetComments(nid in number,
1417 display_type in varchar2,
1418 html_history out nocopy varchar2,
1419 last_ques out nocopy varchar2);
1420
1421 -- bug 2581129
1422 -- GetSubject
1423 -- Get subject of notification message with token values substituted
1424 -- from notification attributes. Overloaded to have display type
1425 -- IN
1426 -- nid - Notification Id
1427 -- disptype - Display Type
1428 -- RETURNS
1429 -- Substituted message subject
1430 -- NOTE
1431 -- If errors are detected this routine returns the subject unsubstituted,
1432 -- or null if all else fails, instead of raising exceptions.
1433 --
1434 function GetSubject(nid in number,
1435 disptype in varchar2)
1436 return varchar2;
1437 --pragma restrict_references(GetSubject, WNDS);
1438
1439 --
1440 -- GetAttrBlob
1441 -- Get the displayed value of a PLSQLBLOB DOCUMENT-type attribute.
1442 -- Returns referenced document in format requested.
1443 -- Use GetAttrText to get retrieve the actual attr value (i.e. the
1444 -- document key string instead of the actual document).
1445 -- NOTE:
1449 -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
1446 -- Only PLSQL document type is implemented.
1447 -- IN:
1448 -- nid - Notification id
1450 -- disptype - Requested display type. Valid values:
1451 -- wf_notification.doc_text - 'text/plain'
1452 -- wf_notification.doc_html - 'text/html'
1453 -- document - The blob into which
1454 -- aname - Attribute Name (the first part of the string that matches
1455 -- the attr list)
1456 --
1457 procedure GetAttrBlob(
1458 nid in number,
1459 astring in varchar2,
1460 disptype in varchar2,
1461 document in out nocopy blob,
1462 aname out nocopy varchar2);
1463
1464 --
1465 -- GetAttrBlob
1466 -- Get the displayed value of a PLSQLBLOB DOCUMENT-type attribute.
1467 -- Returns referenced document in format requested.
1468 -- Use GetAttrText to get retrieve the actual attr value (i.e. the
1469 -- document key string instead of the actual document).
1470 -- The document type of the PLSQLBLOB document is returned from the
1471 -- user-defined API
1472 -- NOTE:
1473 -- Only PLSQL document type is implemented.
1474 -- IN:
1475 -- nid - Notification id
1476 -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
1477 -- disptype - Requested display type. Valid values:
1478 -- wf_notification.doc_text - 'text/plain'
1479 -- wf_notification.doc_html - 'text/html'
1480 -- document - Th blob into which
1481 -- aname - Attribute Name (the first part of the string that matches
1482 -- the attr list)
1483 --
1484 procedure GetAttrBlob(
1485 nid in number,
1486 astring in varchar2,
1487 disptype in varchar2,
1488 document in out nocopy blob,
1489 doctype out nocopy varchar2,
1490 aname out nocopy varchar2);
1491
1492 --
1493 -- Set_NTF_Table_Direction
1494 -- Sets the default direction of notification tables
1495 -- generated through wf_notification.wf_ntf_history
1496 -- and wf_notification.wf_msg_attr
1497 procedure Set_NTF_Table_Direction(direction in varchar2);
1498
1499 --
1500 -- Set_NTF_Table_Type
1501 -- Sets the default table type for attr tables
1502 -- generated through wf_notification.wf_msg_attr
1503 procedure Set_NTF_Table_type(tableType in varchar2);
1504
1505 --
1506 -- SubstituteSpecialChars (PUBLIC)
1507 -- Substitutes the occurence of special characters like <, >, \, ', " etc
1508 -- with their html codes in any arbitrary string.
1509 -- IN
1510 -- some_text - text to be substituted
1511 -- RETURN
1512 -- substituted text
1513 /*#
1514 * Substitutes HTML character entity references for special characters in
1515 * a text string and returns the modified text including the substitutions.
1516 * You can use this function as a security precaution when creating a PL/SQL
1517 * document or a PL/SQL CLOB document that contains HTML, to ensure that only
1518 * the HTML code you intend to include is executed.
1519 * @param some_text Text string with HTML characters
1520 * @return String with HTML characters substituted with HTML codes
1521 * @rep:scope public
1522 * @rep:lifecycle active
1523 * @rep:displayname Substitute HTML Special Characters
1524 * @rep:compatibility S
1525 * @rep:ihelp FND/@notif_api#a_subsc See the related online help
1526 */
1527 function SubstituteSpecialChars(some_text in varchar2)
1528 return varchar2;
1529 pragma restrict_references(SubstituteSpecialChars, WNDS);
1530
1531
1532
1533 --
1534 -- isFwkRegion
1535 -- Verifies whether message body is embedded with framework regions
1536 -- IN
1537 -- nid - Notificatin ID
1538 -- RETURN
1539 -- 'Y' or 'N'
1540 function isFwkRegion(nid in number) return varchar2;
1541
1542 --
1543 -- isFwkRegion
1544 -- Overridden for bug 5456241: Default doc type is text/html
1545 -- Verifies whether message body is embedded with framework regions
1546 -- IN
1547 -- nid - Notificatin ID
1548 -- content_type display type (text/plain or text/html)
1549 -- RETURN
1550 -- 'Y' or 'N'
1551 function isFwkRegion(nid in number, content_type in varchar2 ) return varchar2;
1552
1553 --
1554 -- isFwkBody
1555 -- Verifies whether message body is embedded with framework regions
1556 -- IN
1557 -- nid - Notificatin ID
1558 -- RETURN
1559 -- 'Y' or 'N'
1560 function isFwkBody(nid in number) return varchar2;
1561
1562 -- isFwkBody :
1563 -- Overridden for bug 5456241: Default doc type is text/html
1564 -- Verifies whether message body is embedded with framework regions
1565 -- IN
1566 -- nid - Notificatin ID
1567 -- content_type - Display Type (text/plain or text/html)
1568 -- RETURN
1569 -- 'Y' or 'N'
1570
1571 function isFwkBody(nid in number, content_type in varchar2) return varchar2;
1572
1573 --
1574 -- fwkTokenExist
1575 -- Checks if any notification attribute exist in given message body,
1576 -- is of type DOCUMENT and its value starts with 'JSP:/OA_HTML/OA.jsp?';
1577 --
1578 -- IN
1579 -- nid - Notification ID
1580 -- msgbody - Notification Message body
1584 function fwkTokenExist(nid in number, msgbody in varchar2) return varchar2;
1581 -- RETURN
1582 -- 'Y' or 'N'
1583 --
1585
1586 --
1587 -- getNtfActInfo
1588 -- Fetches Itemtype, itemkey, activity id of a notification
1589 -- IN
1590 -- nid - Notification ID
1591 -- OUT
1592 -- l_itype Itemtype of the notification activity
1593 -- l_itype Itemkey of the process part of which the notification was sent
1594 -- l_actid Activity ID of the Notification Activity in the process
1595
1596 procedure getNtfActInfo (nid in number,
1597 l_itype out nocopy varchar2,
1598 l_ikey out nocopy varchar2,
1599 l_actid out nocopy number);
1600
1601 --
1602 -- GetAttrDoc2
1603 -- Get the displayed value of a DOCUMENT-type attribute.
1604 -- Returns referenced document in format requested along with the
1605 -- document type specified by the user.
1606 -- NOTE:
1607 -- Only PLSQL document type is implemented.
1608 -- This procedure wraps the original implementation and the function
1609 -- GetAttrDoc will call this procedure.
1610 -- IN:
1611 -- nid - Notification id
1612 -- aname - Attribute Name
1613 -- disptype - Requested display type. Valid values:
1614 -- 'text/plain' - plain text
1615 -- 'text/html' - html
1616 -- '' - attachment(?)
1617 -- RETURNS:
1618 -- Referenced document in format requested.
1619 -- Document type specified by the user
1620 --
1621
1622 procedure GetAttrDoc2(
1623 nid in number,
1624 aname in varchar2,
1625 disptype in varchar2,
1626 document out nocopy varchar2,
1627 doctype out nocopy varchar2);
1628
1629 --
1630 -- getFwkBodyURL
1631 -- This API returns a URL to access notification body with
1632 -- Framework content embedded
1633 -- IN:
1634 -- nid - Notification id
1635 -- disptype - Requested display type. Valid values:
1636 -- 'text/plain' - plain text
1637 -- 'text/html' - html
1638 -- RETURNS:
1639 -- Returns the URL returned by call to getFwkBodyURLLang
1640 --
1641
1642 function getFwkBodyURL( nid in number,
1643 contenttype varchar2 ) return varchar2;
1644
1645 --
1646 -- getFwkBodyURLLang
1647 -- This API returns a URL to access notification body with
1648 -- Framework content embedded.
1649 -- IN:
1650 -- nid - Notification id
1651 -- disptype - Requested display type. Valid values:
1652 -- 'text/plain' - plain text
1653 -- 'text/html' - html
1654 -- RETURNS:
1655 -- Returns the URL to access the notification detail body
1656 --
1657
1658 function getFwkBodyURLLang( nid in number,
1659 contenttype varchar2,
1660 language varchar2) return varchar2;
1661
1662
1663 --
1664 -- getFwkBodyURL
1665 -- This API returns a URL to access notification body with
1666 -- Framework content embedded.
1667 -- IN:
1668 -- p_nid - Notification id
1669 -- p_contenttype - Requested display type. Valid values:
1670 -- 'text/plain' - plain text
1671 -- 'text/html' - html,
1672 -- p_language language value of that notification / user
1673 -- p_nlsCalendar nls calender of that user
1674 --
1675 -- RETURNS:
1676 -- Returns the URL to access the notification detail body
1677 --
1678
1679 function getFwkBodyURL2( p_nid in number,
1680 p_contenttype varchar2,
1681 p_language varchar2,
1682 p_nlsCalendar varchar2) return varchar2;
1683
1684
1685 --
1686 -- getSummaryURL
1687 -- This API returns a URL to access summary of notiifications
1688 --
1689 -- IN:
1690 -- mailer_role - role for which summary of notifications required
1691 -- disptype - Requested display type. Valid values:
1692 -- 'text/plain' - plain text
1693 -- 'text/html' - html
1694 -- RETURNS:
1695 -- Returns the URL to access the summary of notiification for the role
1696 --
1697
1698 function getSummaryURL( mailer_role varchar2,
1699 contenttype varchar2 ) return varchar2;
1700
1701
1702
1703 -- getSummaryURL
1704 -- This API returns a URL to access summary of notiifications
1705 --
1706 -- IN:
1707 -- p_mailer_role - role for which summary of notifications required
1708 -- p_contentType - Requested display type. Valid values:
1709 -- 'text/plain' - plain text
1710 -- 'text/html' - html
1711 -- p_nlsCalendar - nls Calender of that role / user
1712 --
1713 -- RETURNS:
1714 -- Returns the URL to access the summary of notiification for the role
1715 --
1716
1717 function getSummaryURL2( p_mailer_role IN varchar2,
1718 p_contentType IN varchar2,
1719 p_nlsCalendar IN varchar2) return varchar2;
1720
1721
1722 -- GetSignatureRequired
1723 -- Determine signing requirements for a policy
1724 -- IN:
1725 -- nid - Notification id - used for error context only
1726 -- p_sig_policy - Policy Name
1727 -- OUT:
1731 -- p_render_hint - hints like ATTR_ONLY or FULL_TEXT
1728 -- p_sig_required - Y/N
1729 -- p_fwk_sig_flavor - sigFlavor for browser signing.
1730 -- p_email_sig_flavor - sigFlavor for email
1732
1733 procedure GetSignatureRequired(p_sig_policy in varchar2,
1734 p_nid in number,
1735 p_sig_required out nocopy varchar2,
1736 p_fwk_sig_flavor out nocopy varchar2,
1737 p_email_sig_flavor out nocopy varchar2,
1738 p_render_hint out nocopy varchar2);
1739
1740 -- SetUIErrorMessage
1741 -- API for Enhanced error handling for OAFwk UI Bug#2845488 grengara
1742 -- This procedure can be used for handling exceptions gracefully when dynamic SQL is invloved
1743
1744 procedure SetUIErrorMessage;
1745
1746 --
1747 -- GetComments2
1748 -- Creates the Action History table for a given a notification id based on
1749 -- different filter criteria.
1750 -- IN
1751 -- p_nid - Notification id
1752 -- p_display_type - Display Type
1753 -- p_action_type - Action Type to look for (REASSIGN, RESPOND, QA,...)
1754 -- p_comment_date - Comment Date
1755 -- p_from_role - Comment provider
1756 -- p_to_role - Comment receiver
1757 -- p_hide_reassign - If Reassign comments be shown or not
1758 -- p_hide_requestinfo - If More Info request be shown or not
1759 -- OUT
1760 -- p_action_history - Action History table
1761 --
1762 procedure GetComments2(p_nid in number,
1763 p_display_type in varchar2 default '',
1764 p_action_type in varchar2 default null,
1765 p_comment_date in date default null,
1766 p_from_role in varchar2 default null,
1767 p_to_role in varchar2 default null,
1768 p_hide_reassign in varchar2 default 'N',
1769 p_hide_requestinfo in varchar2 default 'N',
1770 p_action_history out nocopy varchar2);
1771
1772
1773 --
1774 -- SetComments
1775 -- Private procedure that is used to store a comment record into WF_COMMENTS
1776 -- table with the denormalized information. A record is inserted for every
1777 -- action performed on a notification.
1778 -- IN
1779 -- p_nid - Notification Id
1780 -- p_from_role - Internal Name of the comment provider
1781 -- p_to_role - Internal Name of the comment recipient
1782 -- p_action - Action performed
1783 -- p_action_source - Source from where the action is performed
1784 -- p_user_comment - Comment Text
1785 --
1786 procedure SetComments(p_nid in number,
1787 p_from_role in varchar2,
1788 p_to_role in varchar2,
1789 p_action in varchar2,
1790 p_action_source in varchar2,
1791 p_user_comment in varchar2);
1792
1793 --
1794 -- Resend
1795 -- Private procedure to resend a notification given the notification id. This
1796 -- procedure checks the mail status and recipient's notification preference to
1797 -- see if it is eligible to send e-mail.
1798 -- IN
1799 -- p_nid - Notification Id
1800 --
1801 procedure Resend(p_nid in number);
1802
1803 --
1804 -- getNtfResponse
1805 -- Fetches result(response) CODE and response display prompt to the notification
1806 -- IN
1807 -- p_nid - Notification ID
1808 -- OUT
1809 -- p_result_code Result code of the notification
1810 -- p_result_display Display value of the result code
1811
1815
1812 procedure getNtfResponse (p_nid in number,
1813 p_result_code out nocopy varchar2,
1814 p_result_display out nocopy varchar2);
1816 --
1817 -- SetNLSLanguage (PRIVATE)
1818 -- Set the NLS Lanugage setting of current session
1819 --
1820 procedure SetNLSLanguage(p_language in varchar2,
1821 p_territory in varchar2);
1822
1823 --
1824 -- PropagateHistory (PUBLIC)
1825 -- This API allows Product Teams to publish custom action
1826 -- to WF_COMMENTS table.
1827 --
1828 procedure propagatehistory(p_item_type in varchar2,
1829 p_item_key in varchar2,
1830 p_document_id in varchar2,
1831 p_from_role in varchar2,
1832 p_to_role in varchar2,
1833 p_action in varchar2,
1834 p_action_source in varchar2,
1835 p_user_comment in varchar2) ;
1836
1837 --
1838 -- Resend_Failed_Error_Ntfs (CONCURRENT PROGRAM API)
1839 -- API to re-enqueue notifications with mail_status FAILED and UNAVAIL in order
1840 -- to re-send them. Mailer had processed these notifications earlier and updated
1844 -- errbuf - CP error message
1841 -- the status since these notifications could not be delivered/processed.
1842 --
1843 -- OUT
1845 -- retcode - CP return code (0 = success, 1 = warning, 2 = error)
1846 -- IN
1847 -- p_mail_status - Mail status that needs to be resent.
1848 -- ERROR - Only for FYI notifications
1849 -- FAILED - All notifications
1850 -- p_msg_type - Message type of the notification
1851 -- p_role - Workflow role whose notifications are to be re-enqueued
1852 --
1853 -- p_from_date - Notification has been sent on or after this date
1854 -- p_to_date - Notification has been sent on before this date
1855 -- - Type is varchar2 because CP reports problems with Date type
1856 --
1857 procedure Resend_Failed_Error_Ntfs(errbuf out nocopy varchar2,
1858 retcode out nocopy varchar2,
1859 p_mail_status in varchar2 default null,
1860 p_msg_type in varchar2 default null,
1861 p_role in varchar2 default null,
1862 p_from_date in varchar2 default null,
1863 p_to_date in varchar2 default null);
1864
1865 -- Denormalize Custom Columns
1866 procedure denormalizeColsConcurrent(retcode out nocopy varchar2,
1867 errbuf out nocopy varchar2,
1868 p_item_type in varchar2,
1869 p_status in varchar2,
1870 p_recipient in varchar2);
1871
1872 --
1873 -- GetText2 (INTERNAL ONLY)
1874 -- This procedure is same as GetText above. Only difference is, this provides
1875 -- a flag to suppress substitution of DOCUMENT type tokens in the text. This
1879 -- IN:
1876 -- is created for internal purposes only to substitute tokens within the
1877 -- PLSQL DOCUMENT attribute's value. We don't support DOCUMENT type tokens
1878 -- within a DOCUMENT type attribute.
1880 -- some_text - Text to be substituted
1881 -- nid - Notification id of notification to use for token values
1882 -- disptype - Display type ('text/plain', 'text/html', '')
1883 -- sub_doc - Substitute DOCUMENT type tokens (true, false)
1884 -- RETURNS:
1885 -- Some_text with tokens substituted.
1886 --
1887 function GetText2(some_text in varchar2,
1888 nid in number,
1889 disptype in varchar2 default '',
1890 sub_doc in boolean default true)
1891 return varchar2;
1892
1893
1894 --
1895 -- isFYINtf (INTERNAL ONLY)
1896 -- This function checks whether a notification is FYI or Response Required notification.
1897 -- IN:
1898 -- nid - Notification id to be checked
1899 -- RETURNS:
1900 -- boolean value true | false.
1901 --
1902 function isFYI(nid in number) return boolean;
1903
1904 --
1905 -- Respond2
1906 -- ER 10177347: Process the response to the notification when the performer
1907 -- applies the response from worklist in deferred mode. It has the same
1908 -- functionality as that of the respond() API except that it does not
1909 -- call Complete procedure to complete the notification activity.
1910 -- IN
1911 -- nid Notification ID
1912 -- respond_comment Respond Comment
1916 procedure Respond2(nid in number,
1913 -- responder Performer who responded to the notification
1914 -- action_source For Internal Use Only
1915 -- response_found boolean value that tells whether respond attributes exists or not
1917 respond_comment in varchar2 default null,
1918 responder in varchar2 default null,
1919 action_source in varchar2 default null,
1920 response_found out nocopy boolean);
1921
1922 --
1923 --
1924 -- process_response
1925 -- ER 10177347: Determines that the notification response has to be
1926 -- processed in synschronous mode or defer mode, calls the respond()
1927 -- or respond2() API and enqueues the event into WF_NOTIFICATION_IN queue
1928 -- accordingly based on the value of the parameter 'defer_response'
1929 -- IN
1930 -- nid Notification ID
1931 -- respond_comment Respond Comment
1935 procedure process_response(nid in number,
1932 -- responder Performer who responded to the notification
1933 -- action_source For Internal Use Only
1934 -- defer_response value of the profile option 'WF_NTF_DEFER_RESPONSE_PROCESS'
1936 respond_comment in varchar2 default null,
1937 responder in varchar2 default null,
1938 action_source in varchar2 default null,
1939 defer_response in varchar2 default null);
1940
1941
1942 --
1943 -- Complete
1944 -- ER 10177347: This procedure executes the callback function in
1945 -- COMPLETE mode to comeplete the notification activity
1946 -- IN
1947 -- p_nid Notification ID
1948 procedure Complete(p_nid in number);
1949
1950
1951 END WF_Notification;
[Home] [Help]