1 package body FND_WF_NOTIFICATION as
2 /* $Header: afwfntfb.pls 115.3 2003/09/08 15:01:01 ctilley noship $ */
3
4 -- AddAttr
5 -- Add a new run-time notification attribute.
6 -- The attribute will be completely unvalidated. It is up to the
7 -- user to do any validation and insure consistency.
8 -- IN:
9 -- nid - Notification Id
10 -- aname - Attribute name
11 --
12 procedure AddAttr(nid in number,
13 aname in varchar2)
14 is
15 begin
16 wf_notification.AddAttr(nid,
17 aname);
18 end AddAttr;
19
20
21 -- SetAttrText
22 -- Set the value of a notification attribute, given text representation.
23 -- If the attribute is a NUMBER or DATE type, then translate the
24 -- text-string value to a number/date using attribute format.
25 -- For all other types, store the value directly.
26 -- IN:
27 -- nid - Notification id
28 -- aname - Attribute Name
29 -- avalue - New value for attribute
30 --
31 procedure SetAttrText(nid in number,
32 aname in varchar2,
33 avalue in varchar2)
34 is
35 begin
36 wf_notification.SetAttrText(nid,
37 aname,
38 avalue);
39 end SetAttrText;
40
41
42 -- SetAttrNumber
43 -- Set the value of a number notification attribute.
44 -- Attribute must be a NUMBER-type attribute.
45 -- IN:
46 -- nid - Notification id
47 -- aname - Attribute Name
48 -- avalue - New value for attribute
49 --
50 procedure SetAttrNumber (nid in number,
51 aname in varchar2,
52 avalue in number)
53 is
54 begin
55 wf_notification.SetAttrNumber(nid,
56 aname,
57 avalue);
58 end SetAttrNumber;
59
60
61 -- SetAttrDate
62 -- Set the value of a date notification attribute.
63 -- Attribute must be a DATE-type attribute.
64 -- IN:
65 -- nid - Notification id
66 -- aname - Attribute Name
67 -- avalue - New value for attribute
68 --
69 procedure SetAttrDate (nid in number,
70 aname in varchar2,
71 avalue in date)
72 is
73 begin
74 wf_notification.SetAttrDate(nid,
75 aname,
76 avalue);
77 end SetAttrDate;
78
79
80 -- GetAttrInfo
81 -- Get type information about a notification attribute.
82 -- IN:
83 -- nid - Notification id
84 -- aname - Attribute name
85 -- OUT:
86 -- atype - Attribute type
87 -- subtype - 'SEND' or 'RESPOND',
88 -- format - Attribute format
89 --
90 procedure GetAttrInfo(nid in number,
91 aname in varchar2,
92 atype out nocopy varchar2,
93 subtype out nocopy varchar2,
94 format out nocopy varchar2)
95 is
96 begin
97 wf_notification.GetAttrInfo(nid,
98 aname,
99 atype,
100 subtype,
101 format);
102 end GetAttrInfo;
103
104
105 -- GetAttrText
106 -- Get the value of a text notification attribute.
107 -- If the attribute is a NUMBER or DATE type, then translate the
108 -- number/date value to a text-string representation using attrbute format.
109 -- For all other types, get the value directly.
110 -- IN:
111 -- nid - Notification id
112 -- aname - Attribute Name
113 -- RETURNS:
114 -- Attribute value
115
116 function GetAttrText (nid in number,
117 aname in varchar2)
118 return varchar2 is
119 lvalue varchar2(4000);
120 begin
121 lvalue := wf_notification.GetAttrText(nid,
122 aname);
123 return lvalue;
124 end GetAttrText;
125
126 -- GetAttrNumber
127 -- Get the value of a number notification attribute.
128 -- Attribute must be a NUMBER-type attribute.
129 -- IN:
130 -- nid - Notification id
131 -- aname - Attribute Name
132 -- RETURNS:
133 -- Attribute value
134
135 function GetAttrNumber (nid in number,
136 aname in varchar2)
137 return number is
138 lvalue number;
139 begin
140 lvalue := wf_notification.GetAttrNumber(nid,
141 aname);
142 return lvalue;
143 end GetAttrNumber;
144
145
146 -- GetAttrDate
147 -- Get the value of a date notification attribute.
148 -- Attribute must be a DATE-type attribute.
149 -- IN:
150 -- nid - Notification id
151 -- aname - Attribute Name
152 -- RETURNS:
153 -- Attribute value
154
155 function GetAttrDate (nid in number,
156 aname in varchar2)
157 return date is
158 lvalue date;
159 begin
160 lvalue := wf_notification.GetAttrDate(nid,
161 aname);
162 return lvalue;
163 end GetAttrDate;
164
165 --
166 -- GetAttrDoc
167 -- Get the displayed value of a DOCUMENT-type attribute.
168 -- Returns referenced document in format requested.
169 -- Use GetAttrText to get retrieve the actual attr value (i.e. the
170 -- document key string instead of the actual document).
171 -- NOTE:
172 -- Only PLSQL document type is implemented.
173 -- IN:
174 -- nid - Notification id
175 -- aname - Attribute Name
176 -- disptype - Requested display type. Valid values:
177 -- 'text/plain' - plain text
178 -- 'text/html' - html
179 -- '' - attachment(?)
180 -- RETURNS:
181 -- Referenced document in format requested.
182 --
183 function GetAttrDoc(
184 nid in number,
185 aname in varchar2,
186 disptype in varchar2)
187 return varchar2 is
188 lvalue varchar2(32000);
189 begin
190 lvalue := wf_notification.GetAttrDoc(nid,
191 aname,
192 disptype);
193 return lvalue;
194 end GetAttrDoc;
195
196
197 --
198 -- GetText
199 -- Substitute tokens in an arbitrary text string.
200 -- This function may return up to 32K chars. It can NOT be used in a view
201 -- definition or in a Form. For views and forms, use GetShortText, which
202 -- truncates values at 2000 chars.
203 -- IN:
204 -- some_text - Text to be substituted
205 -- nid - Notification id of notification to use for token values
206 -- disptype - Display type ('text/plain', 'text/html', '')
207 -- RETURNS:
208 -- Some_text with tokens substituted.
209 -- NOTE:
210 -- If errors are detected this routine returns some_text untouched
211 -- instead of raising exceptions.
212 --
213 function GetText(some_text in varchar2,
214 nid in number,
215 disptype in varchar2 default '')
216 return varchar2 is
217 lvalue varchar2(32000);
218 begin
219 lvalue := wf_notification.GetText(some_text,
220 nid,
221 disptype);
222 return lvalue;
223 end GetText;
224
225 --
226 -- GetUrlText
227 -- Substitute url-style tokens (with dashes) an arbitrary text string.
228 -- This function may return up to 32K chars. It can NOT be used in a view
229 -- definition or in a Form. For views and forms, use GetShortText, which
230 -- truncates values at 2000 chars.
231 -- IN:
232 -- some_text - Text to be substituted
233 -- nid - Notification id of notification to use for token values
234 -- RETURNS:
235 -- Some_text with tokens substituted.
236 -- NOTE:
237 -- If errors are detected this routine returns some_text untouched
238 -- instead of raising exceptions.
239 --
240 function GetUrlText(some_text in varchar2,
241 nid in number)
242 return varchar2 is
243 lvalue varchar2(32000);
244 begin
245 lvalue := wf_notification.GetUrlText(some_text,
246 nid);
247 return lvalue;
248 end GetUrlText;
249
250 --
251 -- GetShortText
252 -- Substitute tokens in an arbitrary text string, limited to 2000 chars.
253 -- This function is meant to be used in view definitions and Forms, where
254 -- the field size must be limited to 2000 chars. Use GetText() to retrieve
255 -- up to 32K if the text may be longer.
256 -- IN:
257 -- some_text - Text to be substituted
258 -- nid - Notification id of notification to use for token values
259 -- RETURNS:
260 -- Some_text with tokens substituted.
261 -- NOTE:
262 -- If errors are detected this routine returns some_text untouched
263 -- instead of raising exceptions.
264 --
265 function GetShortText(some_text in varchar2,
266 nid in number)
267 return varchar2 is
268 lvalue varchar2(32000);
269 begin
270 lvalue := wf_notification.GetShortText(some_text,
271 nid);
272 return lvalue;
273 end GetShortText;
274
275 --
276 -- GetSubject
277 -- Get subject of notification message with token values substituted
278 -- from notification attributes.
279 -- IN:
280 -- nid - Notification Id
281 -- RETURNS:
282 -- Substituted message subject
283 -- NOTE:
284 -- If errors are detected this routine returns the subject unsubstituted,
285 -- or null if all else fails, instead of raising exceptions. It must do
286 -- this so the routine can be pragma'd and used in the
287 -- wf_notifications_view view.
288 --
289 function GetSubject(
290 nid in number)
291 return varchar2 is
292 lvalue varchar2(240);
293 begin
297
294 lvalue := wf_notification.GetSubject(nid);
295 return lvalue;
296 end GetSubject;
298 --
299 -- GetBody
300 -- Get body of notification message with token values substituted
301 -- from notification attributes.
302 -- This function may return up to 32K chars. It can NOT be used in a view
303 -- definition or in a Form. For views and forms, use GetShortBody, which
304 -- truncates values at 2000 chars.
305 -- IN:
306 -- nid - Notification Id
307 -- disptype - Display type ('text/plain', 'text/html', '')
308 -- RETURNS:
309 -- Substituted message body
310 -- NOTE:
311 -- If errors are detected this routine returns the body unsubstituted,
312 -- or null if all else fails, instead of raising exceptions.
313 --
314 function GetBody(
315 nid in number,
316 disptype in varchar2 default '')
317 return varchar2 is
318 lvalue varchar2(32000);
319 begin
320 lvalue := wf_notification.GetBody(nid,
321 disptype);
322 return lvalue;
323 end GetBody;
324
325 --
326 -- GetShortBody
327 -- Get body of notification message with token values substituted
328 -- from notification attributes.
329 -- This function is meant to be used in view definitions and Forms, where
330 -- the field size must be limited to 2000 chars. Use GetBody() to retrieve
331 -- up to 32K if the text may be longer.
332 -- IN:
333 -- nid - Notification Id
334 -- RETURNS:
335 -- Substituted message body
336 -- NOTE:
337 -- If errors are detected this routine returns the body unsubstituted,
338 -- or null if all else fails, instead of raising exceptions. It must do
339 -- this so the routine can be pragma'd and used in the
340 -- wf_notifications_view view.
341 --
342 function GetShortBody(nid in number)
343 return varchar2 is
344 lvalue varchar2(32000);
345 begin
346
347 lvalue := wf_notification.GetShortBody(nid);
348 return lvalue;
349 end GetShortBody;
350
351 --
352 -- GetInfo
353 -- Return info about notification
354 -- IN
355 -- nid - Notification Id
356 -- OUT
357 -- role - Role notification is sent to
358 -- message_type - Type flag of message
359 -- message_name - Message name
360 -- priority - Notification priority
361 -- due_date - Due date
362 -- status - Notification status (OPEN, CLOSED, CANCELED)
363 --
364 procedure GetInfo(nid in number,
365 role out nocopy varchar2,
366 message_type out nocopy varchar2,
367 message_name out nocopy varchar2,
368 priority out nocopy varchar2,
369 due_date out nocopy varchar2,
370 status out nocopy varchar2)
371 is
372 begin
373 wf_notification.GetInfo(nid,
374 role,
375 message_type,
376 message_name,
377 priority,
378 due_date,
379 status);
380 end GetInfo;
381
382 --
383 -- Responder
384 -- Return responder of closed notification.
385 -- IN
386 -- nid - Notification Id
387 -- RETURNS
388 -- Responder to notification. If no responder was set or notification
389 -- not yet closed, return null.
390 --
391 function Responder(
392 nid in number)
393 return varchar2 is
394 lvalue varchar2(240);
395 begin
396 lvalue := wf_notification.Responder(nid);
397 return lvalue;
398 end Responder;
399
400
401 -- AccessCheck
402 -- Check that the notification is open and access key is valid.
403 -- IN
404 -- Access string <nid>/<nkey>
405 -- RETURNS
406 -- user name (if notificaiton is open and key is valid)
407 -- othersise null
408 function AccessCheck(access_str in varchar2)
409 return varchar2 is
410 lvalue varchar2(320);
411 begin
412 lvalue := wf_notification.AccessCheck(access_str);
413 return lvalue;
414 end AccessCheck;
415
416 --
417 -- Send
418 -- Send the role the specified message.
419 -- Insert a single notification in the notifications table, and set
420 -- the default send and respond attributes for the notification.
421 -- IN:
422 -- role - Role to send notification to
423 -- msg_type - Message type
424 -- msg_name - Message name
425 -- due_date - Date due
426 -- callback - Callback function
427 -- context - Data for callback
428 -- send_comment - Comment to add to notification
429 -- priority - Notification priority
430 -- RETURNS:
431 -- Notification Id
432 --
436 due_date in date default null,
433 function Send(role in varchar2,
434 msg_type in varchar2,
435 msg_name in varchar2,
437 callback in varchar2 default null,
438 context in varchar2 default null,
439 send_comment in varchar2 default null,
440 priority in number default null)
441 return number is
442 lvalue number;
443 begin
444 lvalue := wf_notification.Send(role,
445 msg_type,
446 msg_name,
447 due_date,
448 callback,
449 context,
450 send_comment,
451 priority);
452 return lvalue;
453 end Send;
454
455 --
456 -- SendGroup
457 -- Send the role users the specified message.
458 -- Send a separate notification to every user assigned to the role.
459 -- IN:
460 -- role - Role of users to send notification to
461 -- msg_type - Message type
462 -- msg_name - Message name
463 -- due_date - Date due
464 -- callback - Callback function
465 -- context - Data for callback
466 -- send_comment - Comment to add to notification
467 -- priority - Notification priority
468 -- RETURNS:
469 -- Group ID - Id of notification group
470 --
471 function SendGroup(role in varchar2,
472 msg_type in varchar2,
473 msg_name in varchar2,
474 due_date in date default null,
475 callback in varchar2 default null,
476 context in varchar2 default null,
477 send_comment in varchar2 default null,
478 priority in number default null)
479 return number is
480 lvalue number;
481 begin
482 lvalue := wf_notification.SendGroup(role,
483 msg_type,
484 msg_name,
485 due_date,
486 callback,
487 context,
488 send_comment,
489 priority);
490 return lvalue;
491 end SendGroup;
492
493 --
494 -- Forward
495 -- Forward a notification, identified by NID to another user. Validate
496 -- the user and Return error messages ...
497 -- IN:
498 -- nid - Notification Id
499 -- new_role - Role to forward notification to
500 -- forward_comment - comment to append to notification
501 -- user - role who perform this action if provided
502 -- cnt - count for recursive purpose
503 --
504 procedure Forward(nid in number,
505 new_role in varchar2,
506 forward_comment in varchar2 default null,
507 user in varchar2 default null,
508 cnt in number default 0)
509 is
510 begin
511 wf_notification.Forward(nid,
512 new_role,
513 forward_comment,
514 user,
515 cnt);
516 end Forward;
517
518 --
519 -- Transfer
520 -- Transfer a notification, identified by NID to another user. Validate
521 -- the user and Return error messages ...
522 -- IN:
523 -- nid - Notification Id
524 -- new_role - Role to transfer notification to
525 -- forward_comment - comment to append to notification
526 -- user - role who perform this action if provided
527 -- cnt - count for recursive purpose
528 --
529 procedure Transfer(nid in number,
530 new_role in varchar2,
531 forward_comment in varchar2 default null,
532 user in varchar2 default null,
533 cnt in number default 0)
534 is
535 begin
536 wf_notification.Transfer(nid,
537 new_role,
538 forward_comment,
539 user,
540 cnt);
541 end Transfer;
542
543 --
544 -- Cancel
545 -- Cancel a single notification.
546 -- IN:
547 -- nid - Notification Id
548 -- cancel_comment - Comment to append to notification
549 --
550 procedure Cancel(nid in number,
551 cancel_comment in varchar2 default null)
552 is
553 begin
554 wf_notification.Cancel(nid, cancel_comment);
555 end Cancel;
556
557 --
558 -- CancelGroup
559 -- Cancel all notifications belonging to a notification group
560 -- IN:
561 -- gid - Notification group id
562 -- cancel_comment - Comment to append to all notifications
563 --
564 procedure CancelGroup(gid in number,
565 cancel_comment in varchar2 default null)
566 is
567 begin
568 wf_notification.CancelGroup(gid, cancel_comment);
569 end CancelGroup;
570
571 --
572 -- Respond
573 -- Respond to a notification.
574 -- IN:
575 -- nid - Notification Id
576 -- respond_comment - Comment to append to notification
577 -- responder - User or role responding to notification
578 --
579 procedure Respond(nid in number,
580 respond_comment in varchar2 default null,
581 responder in varchar2 default null)
582 is
583 begin
584 wf_notification.Respond(nid,
585 respond_comment,
586 responder);
587 end Respond;
588
589 --
590 -- TestContext
591 -- Test if current context is correct
592 -- IN
593 -- nid - Notification id
594 -- RETURNS
595 -- TRUE if context ok, or context check not implemented
596 -- FALSE if context check fails
597 --
598 function TestContext(
599 nid in number)
600 return boolean
601 is
602 begin
603
604 return (wf_notification.TestContext(nid));
605
606 end TestContext;
607
608 --
609 --
610 -- VoteCount
611 -- Count the number of responses for a result_code
612 -- IN:
613 -- Gid - Notification group id
614 -- ResultCode - Result code to be tallied
615 -- OUT:
616 -- ResultCount - Number of responses for ResultCode
617 -- PercentOfTotalPop - % ResultCode ( As a % of total population )
618 -- PercentOfVotes - % ResultCode ( As a % of votes cast )
619 --
620 procedure VoteCount ( Gid in number,
621 ResultCode in varchar2,
622 ResultCount out nocopy number,
623 PercentOfTotalPop out nocopy number,
624 PercentOfVotes out nocopy number )
625 is
626 begin
627 wf_notification.VoteCount(Gid,
628 ResultCode,
629 ResultCount,
630 PercentOfTotalPop,
631 PercentOfVotes);
632 end VoteCount;
633 --
634 -- OpenNotifications
635 -- Determine if any Notifications in the Group are OPEN
636 --
637 --IN:
638 -- Gid - Notification group id
639 --
640 --Returns:
641 -- TRUE - if the Group contains open notifications
645 return Boolean
642 -- FALSE - if the group does NOT contain open notifications
643 --
644 function OpenNotificationsExist( Gid in Number )
646 is
647 begin
648 return (wf_notification.OpenNotificationsExist(Gid));
649
650 end OpenNotificationsExist;
651
652 --
653 -- WorkCount
654 -- Count number of open notifications for user
655 -- IN:
656 -- username - user to check
657 -- RETURNS:
658 -- Number of open notifications for that user
659 --
660 function WorkCount(
661 username in varchar2)
662 return number
663 is
664 lvalue number;
665 begin
666 lvalue := wf_notification.WorkCount(username);
667 return lvalue;
668 end WorkCount;
669
670 --
671 -- Close
672 -- Close a notification.
673 -- IN:
674 -- nid - Notification Id
675 -- resp - Respond Required? 0 - No, 1 - Yes
676 -- responder - User or role close this notification
677 --
678 procedure Close(nid in number,
679 responder in varchar2 default null)
680 is
681 begin
682 wf_notification.Close(nid,
683 responder);
684 end Close;
685
686 -- GetSubSubjectDisplay
687 -- Get the design subject of a notification and Substitute tokens in text
688 -- with the display name of the attributes in the subject.
689 -- This is used in routing rule poplists
690 -- IN:
691 -- message_type - Item type of the message
692 -- message_name - Name of the message to substitute
693 --
694 function GetSubSubjectDisplay(message_type IN VARCHAR2, message_name IN VARCHAR2)
695 return varchar2
696 is
697 lvalue varchar2(2000);
698 begin
699
700 lvalue := wf_notification.GetSubSubjectDisplay(message_type,
701 message_name);
702 return lvalue;
703 end GetSubSubjectDisplay;
704
705 -- GetSubSubjectDisplayShort
706 -- Get the design subject of a notification and Substitute tokens in text
707 -- with ellipsis (...)
708 -- This is used in routing rule poplists on the Web screens
709 -- IN:
710 -- message_type - Item type of the message
711 -- message_name - Name of the message to substitute
712 --
713 function GetSubSubjectDisplayShort(message_type IN VARCHAR2, message_name IN VARCHAR2)
714 return varchar2
715 is
716 lvalue varchar2(2000);
717 begin
718
719 lvalue := wf_notification.GetSubSubjectDisplayShort(message_type,
720 message_name);
721 return lvalue;
722 end GetSubSubjectDisplayShort;
723
724 -- PLSQL-Clob Processing
725
726 --Name : GetFullBody (PUBLIC)
727 --Desc : Gets full body of message with all PLSQLCLOB variables transalted.
728 -- and returns the message in 32K chunks in the msgbody out variable.
729 -- Call this repeatedly until end_of_body is true.
730 -- Call syntax is
731 --while not (end_of_msgbody) loop
732 -- wf_notification.getfullbody(nid,msgbody,end_of_msgbody);
733 --end loop;
734 procedure GetFullBody (nid in number,
735 msgbody out nocopy varchar2,
736 end_of_body in out nocopy boolean,
737 disptype in varchar2 default 'text/plain')
738 is
739 begin
740 wf_notification.GetFullBody(nid,
741 msgbody,
742 end_of_body,
743 disptype);
744 end GetFullBody;
745
746 --Name: GetFullBodyWrapper (PUBLIC)
747 --Desc : Gets full body of message with all PLSQLCLOB variables transalted.
748 -- and returns the message in 32K chunks in the msgbody out variable.
749 -- Call this repeatedly until end_of_body is "Y". Uses string arg
750 -- instead of boolean like GetFullBody for end_of_msg_body. Created
751 -- since booleans cannot be passed via JDBC.
752 -- Call syntax is
753 --while (end_of_msgbody <> "Y") loop
754 -- wf_notification.getfullbody(nid,msgbody,end_of_msgbody);
755 --end loop;
756 procedure GetFullBodyWrapper (nid in number,
757 msgbody out nocopy varchar2,
758 end_of_body out nocopy varchar2,
759 disptype in varchar2 default 'text/plain')
760 is
761 begin
762 wf_notification.GetFullBodyWrapper(nid,
763 msgbody,
764 end_of_body,
765 disptype);
766 end GetFullBodyWrapper;
767
768
769
770 END FND_WF_NOTIFICATION;