1 package body WF_ALERT as
2 /* $Header: wfalertb.sql 115.3 99/07/16 23:50:16 porting ship $ */
3
4 /*------------------------------------------------------------------------
5 Name: CheckAlert
6 Description: this function checks if conditions are set for performing an
7 alert action. In this specific example, it checks to see if any
8 error occured since the last time it ran. If there
9 are errors then it returns TRUE and nothing more. The workflow
10 will then proceed to send a notification reporting all errors
11 in the database.
12 If instead nothing is found, then it returns FALSE and the
13 workflow will wait and try again later.
14
15 Note: Substitute this with any function that checks for a condition or event.
16 ------------------------------------------------------------------------*/
17 procedure CheckAlert(itemtype in varchar2,
18 itemkey in varchar2,
19 actid in number,
20 funcmode in varchar2,
21 resultout in out varchar2)
22 is
23 lastrun date;
24 dummy varchar2(30);
25
26 cursor error_cursor (lastrun in date) is
27 select 'errors exist'
28 from wf_item_activity_statuses
29 where activity_status = 'ERROR'
30 and begin_date >= nvl(lastrun,begin_date);
31
32 begin
33
34 -- Do nothing in any mode other than run mode
35 -- this includes timeout, cancel, etc.
36 if (funcmode <> wf_engine.eng_run) then
37 resultout := wf_engine.eng_null;
38 return;
39 end if;
40
41
42 begin
43 lastrun := wf_engine.getitemattrdate(itemtype,itemkey,'LAST_CHECKED');
44 exception when others then
45 if ( wf_core.error_name = 'WFENG_ITEM_ATTR' ) then
46 wf_engine.AddItemAttr(itemtype,itemkey,'LAST_CHECKED');
47 lastrun := null;
48 else
49 raise;
50 end if;
51 end;
52
53 -- check to see if errors have occured since last time.
54 -- if lastrun is null, then check for any errors.
55 open error_cursor (lastrun);
56 fetch error_cursor into dummy;
57 close error_cursor;
58
59 if dummy is null then
60 resultout := wf_engine.eng_completed||':F';
61 else
62 resultout := wf_engine.eng_completed||':T';
63 end if;
64
65 -- store a value for when this was last run so that next time
66 -- we only examine events that occured in the delta
67 -- This way we will only send a new notification (and so cancel the old one)
68 -- if new errors have since happened.
69 wf_engine.setitemattrdate(itemtype,itemkey,'LAST_CHECKED',sysdate);
70
71
72 exception
73 when others then
74 Wf_Core.Context('Wf_Alert', 'CheckAlert', itemtype,
75 itemkey, to_char(actid), funcmode);
76 raise;
77 end CheckAlert;
78
79
80
81
82 /*------------------------------------------------------------------------
83 Name: ErrorReport
84 Description: this is a PLSQL document that is called in the post-alert
85 procedure. This funciton builds a report of errors.
86 Standard notification processing will cancel any previous
87 versions of this notification.
88 Note: This is an example of an alert event. It may be substituted
89 with any event processing
90 -------------------------------------------------------------------------*/
91
92 procedure ErrorReport ( document_id in varchar2,
93 display_type in varchar2,
94 document in out varchar2,
95 document_type in out varchar2) is
96 err_url varchar2(1000);
97
98 -- select a non breakable space,  , when no data found
99 -- to force grid to display in table in html.
100 cursor error_list is
101 select ias.item_type,
102 ias.item_key,
103 ac.name Activity,
104 ias.activity_result_code Result,
105 -- ias.error_name ERROR_NAME,
106 ias.error_message ERROR_MESSAGE,
107 ias.error_stack ERROR_STACK
108 from wf_item_activity_statuses ias,
109 wf_process_activities pa,
110 wf_activities ac,
111 wf_activities ap,
112 wf_items i
113 where ias.activity_status = 'ERROR'
114 and ias.process_activity = pa.instance_id
115 and pa.activity_name = ac.name
116 and pa.activity_item_type = ac.item_type
117 and pa.process_name = ap.name
118 and pa.process_item_type = ap.item_type
119 and pa.process_version = ap.version
120 and i.item_type = ias.item_type
121 and i.item_key = ias.item_key
122 and i.begin_date >= ac.begin_date
123 and i.begin_date < nvl(ac.end_date, i.begin_date+1)
124 order by ias.begin_date, ias.execution_time;
125
126 begin
127 -- will return doc output in display format
128 document_type := display_type;
129
130 -- print table header
131 if display_type='text/html' then
132 document := '<BR><CENTER><TABLE BORDER CELLPADDING=5 BGCOLOR=#FFFFCC>'||
133
134 '<TR BGCOLOR=#83c1c1><TH>Item Type</TH><TH>Item Key</TH><TH>User Key</TH><TH>Error Message</TH><TH>Error Stack</TH></TR>';
135
136 else
137 document_type := 'text/plain';
138 document:=wf_core.local_chr(10)||
139 rpad('Item Type',15)||
140 rpad('Item Key',10)||
141 rpad('User key',15)||
142 rpad('Error Message',20)||
143 rpad('Error Stack',60)||wf_core.local_chr(10);
144 end if;
145
146 -- print each record
147 for error_rec in error_list loop
148 -- look up the monitor URL
149 err_url := WF_MONITOR.GetAdvancedEnvelopeURL
150 ( x_agent => wf_core.translate('WF_WEB_AGENT'),
151 x_item_type => error_rec.item_type,
152 x_item_key => error_rec.item_key,
153 x_admin_mode => 'YES')||
154 '&x_active=ACTIVE'||
155 '&x_complete=COMPLETE&x_error=ERROR'||
156 '&x_suspend=SUSPEND&x_proc_func=Y'||
157 '&x_note_resp=Y&x_note_noresp=Y'||
158 '&x_func_std=Y&x_sort_column=STARTDATE'||
159 '&x_sort_order=ASC';
160
161
162 document := document||'<tr>';
163 document := document||'<td>'||error_rec.item_type||'</td>';
164 document := document||'<td><a href="'||err_url||'">'
165 ||error_rec.item_key||'</a></td>';
166 document := document||'<td>'||
167 nvl(wf_engine.getitemuserkey(
168 error_rec.item_type,error_rec.item_key),'<br>')||'</td>';
169 document := document||'<td>'||error_rec.error_message||'</td>';
170 document := document||'<td>'||error_rec.error_stack||'</td>';
171 document := document||'</tr>';
172 end loop;
173
174
175 if display_type='text/html' then
176 document := document||'</TABLE>';
177 end if;
178
179
180
181 exception
182 when others then
183 Wf_Core.Context('Wf_Alert', 'ErrorReport');
184 raise;
185 end ErrorReport;
186
187
188 END WF_ALERT;