1 package body wf_fwkmon as
2 /* $Header: wffkmonb.pls 120.1.12000000.2 2007/05/23 21:45:43 vshanmug ship $ */
3
4
5 -- ===========================================================================
6 -- FUNCTION NAME: getNotificationResult
7 --
8 -- DESCRIPTION: Returns the display result value for a notification.
9 --
10 -- PARAMETERS: x_notificationId IN Notification ID
11 --
12 -- ===========================================================================
13 FUNCTION getNotificationResult(x_notificationId IN number) return varchar2
14 IS
15
16 ntf_result varchar2(80) := '';
17
18 CURSOR get_result IS
19 SELECT decode(ma.type,
20 'NUMBER', to_char(na.number_value),
21 'DATE', to_char(na.date_value,
22 nvl(ma.format, 'DD/MON/YYYY HH24:MI:SS')),
23 'LOOKUP', wf_core.activity_result(ma.format, na.text_value),
24 na.text_value) result
25 FROM wf_notification_attributes na,
26 wf_message_attributes_vl ma,
27 wf_notifications n
28 WHERE n.notification_id = x_notificationId
29 AND n.message_type = ma.message_type
30 AND n.message_name = ma.message_name
31 AND ma.name = na.name
32 AND ma.name = 'RESULT'
33 AND na.notification_id = n.notification_id;
34
35 BEGIN
36
37 OPEN get_result;
38 FETCH get_result into ntf_result;
39 CLOSE get_result;
40
41 return ntf_result;
42
43 EXCEPTION
44 WHEN NO_DATA_FOUND THEN
45
46 return ntf_result;
47
48 END getNotificationResult;
49
50
51 FUNCTION isRespondNotification(x_notificationId IN number) RETURN number
52
53 IS
54
55 resp_exists pls_integer := 0;
56
57
58 BEGIN
59
60 -- Per Ahmed Alomari, this is the fast correct way to check for the
61 -- single row existence here. 13-NOV-01
62
63 SELECT 1
64 INTO resp_exists
65 FROM wf_notifications wn,
66 wf_notification_attributes wna,
67 wf_message_attributes m
68 WHERE wn.notification_id = x_notificationId
69 AND wn.notification_id = wna.notification_id
70 AND wn.message_name = m.message_name
71 AND wn.message_type = m.message_type
72 AND m.name = wna.name
73 AND m.subtype = 'RESPOND'
74 AND rownum = 1;
75
76 return resp_exists;
77
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80
81 return resp_exists;
82
83
84 END isRespondNotification;
85
86
87 FUNCTION getItemStatus(x_itemType IN varchar2,
88 x_itemKey IN varchar2,
89 x_endDate IN date,
90 x_rootProcess IN varchar2,
91 x_rootVersion IN number) RETURN varchar2
92
93 IS
94 status_ret varchar2(30) := '';
95 activity_status varchar2(30) := '';
96 activity_result varchar2(30) := '';
97 error_count pls_integer := 0;
98
99 -- Accoring to Kevin Hudson:
100 -- The only way a root process can be set to #FORCE is by aborting the
101 -- the workflow. Other activities can be set to this status for other
102 -- reasons, but checking the root is a reliable "Abort" indicator.
103
104 CURSOR get_root_info IS
105 SELECT wias.activity_result_code,
106 wias.activity_status
107 FROM wf_item_activity_statuses wias,
108 wf_activities wa,
109 wf_process_activities wpa
110 WHERE wias.item_key = x_itemKey
111 AND wias.item_type = x_itemType
112 AND wa.name = x_rootProcess
113 AND wa.version = x_rootVersion
114 AND wa.item_type = x_itemType
115 AND wa.name = wpa.activity_name
116 AND wpa.instance_id = wias.process_activity;
117
118 BEGIN
119
120 OPEN get_root_info;
121 FETCH get_root_info into activity_result, activity_status;
122 CLOSE get_root_info;
123
124 BEGIN
125
126 -- Per Ahmed Alomari, this is the fast correct way to check for the
127 -- single row existence here. 13-NOV-01
128
129 SELECT 1
130 INTO error_count
131 FROM wf_item_activity_statuses
132 WHERE item_type = x_itemType
133 AND item_key = x_itemKey
134 AND activity_status = 'ERROR'
135 AND rownum = 1;
136
137 EXCEPTION
138 WHEN NO_DATA_FOUND THEN
139 error_count := 0;
140 END;
141
142 -- If the end date isn't null, the workflow has completed processing.
143
144 if (x_endDate is not null) then
145
146 if (activity_result = '#FORCE') then
147 status_ret := 'FORCE';
148 elsif (error_count > 0) then
149 status_ret := 'COMPLETE_WITH_ERRORS';
150 else
151 status_ret := 'COMPLETE';
152 end if;
153
154 else -- Workflow is still in process
155
156 if (activity_status = 'SUSPEND') then
157 if (error_count > 0) then
158 status_ret := 'SUSPEND_WITH_ERRORS';
159 else
160 status_ret := 'SUSPEND';
161 end if;
162 else
163 if (error_count > 0) then
164 status_ret := 'ERROR';
165 else
166 status_ret := 'ACTIVE';
167 end if;
168 end if;
169
170 end if;
171
172 return status_ret;
173
174 END getItemStatus;
175
176
177 function getRoleEmailAddress (x_role_name in varchar2) return varchar2
178
179 IS
180 -- Copied from wf_directory.getRoleDisplayName()
181
182 colon pls_integer;
183
184 cursor c_role is
185 select email_address
186 from wf_roles
187 where name = x_role_name
188 and ORIG_SYSTEM NOT IN ('HZ_PARTY','POS','ENG_LIST','AMV_CHN',
189 'HZ_GROUP','CUST_CONT');
190
191 cursor corig_role is
192 select email_address
193 from wf_roles
194 where orig_system = substr(x_role_name, 1, colon-1)
195 and orig_system_id = substr(x_role_name, colon+1)
196 and name = x_role_name;
197
198 email wf_roles.email_address%TYPE;
199
200 begin
201 colon := instr(x_role_name, ':');
202 if (colon = 0) then
203 open c_role;
204 fetch c_role into email;
205 close c_role;
206 else
207 open corig_role;
208 fetch corig_role into email;
209 close corig_role;
210 end if;
211
212 return email;
213
214 end getRoleEmailAddress;
215
216
217 FUNCTION getEncryptedAccessKey(itemType in varchar2,
218 itemKey in varchar2,
219 adminMode in varchar2 ) RETURN varchar2 is
220
221 l_key varchar2(2048);
222
223 BEGIN
224
225 l_key := icx_call.encrypt(wf_monitor.getAccessKey(itemType, itemKey, adminMode));
226
227 return l_key;
228
229 EXCEPTION
230 when others then
231 raise;
232
233 END getEncryptedAccessKey;
234
235
236
237 FUNCTION getEncryptedAdminMode (adminMode in varchar2) RETURN varchar2 is
238
239 l_adminMode varchar2(2048);
240
241 BEGIN
242
243 l_adminMode := icx_call.encrypt(adminMode);
244
245 return l_adminMode;
246
247 EXCEPTION
248 when others then
249 raise;
250
251 END getEncryptedAdminMode;
252
253
254
255 FUNCTION isMonitorAdministrator(userName in varchar2) RETURN varchar2 is
256
257 l_adminRole varchar2(30);
258 l_isAdmin varchar2(1) := 'N';
259
260 BEGIN
261
262 l_adminRole := wf_core.translate('WF_ADMIN_ROLE');
263
264 if (l_adminRole = '*') then
265
266 l_isAdmin := 'Y';
267
268 else
269
270 if (wf_directory.isPerformer(userName, l_adminRole)) then
271
272 l_isAdmin := 'Y';
273
274 end if;
275 end if;
276
277 return l_isAdmin;
278
279
280 EXCEPTION
281 when others then
282 raise;
283
284 END isMonitorAdministrator;
285
286
287 FUNCTION getAnonymousSimpleURL(itemType in varchar2,
288 itemKey in varchar2,
289 firstPage in varchar2 ,
290 adminMode in varchar2 ) RETURN varchar2 is
291 l_url varchar2(4000);
292 l_adminMode varchar2(2048);
293 l_accessKey varchar2(2048);
294 l_regionToDisplay varchar2(30) := 'WF_SSG_MONITOR_HISTORY_PAGE';
295 l_itemType varchar2(2000);
296 l_itemKey varchar2(2000);
297
298 BEGIN
299
300 if (firstPage = 'DIAGRAM') then
301 l_regionToDisplay := 'WF_SSG_MONITOR_DIAGRAM_PAGE';
302 end if;
303
304 --
305 -- Encode all parameters.
306 --
307
308 l_accessKey := getEncryptedAccessKey(itemType, itemKey, adminMode);
309
310 l_adminMode := icx_call.encrypt(adminMode);
311
312 l_accessKey := wfa_html.conv_special_url_chars(getEncryptedAccessKey(itemType, itemKey, adminMode));
313
314 l_adminMode := wfa_html.conv_special_url_chars(icx_call.encrypt(adminMode));
315
316 l_itemType := wfa_html.conv_special_url_chars(itemType);
317
318 l_itemKey := wfa_html.conv_special_url_chars(itemKey);
319
320 l_url := getGuestMonitorURL('0', l_regionToDisplay, l_accessKey, l_adminMode,
321 l_itemType, l_itemKey);
322
323 return l_url;
324
325 EXCEPTION
326 when others then
327 raise;
328
329 END getAnonymousSimpleURL;
330
331
332 FUNCTION getAnonymousAdvanceURL(itemType in varchar2,
333 itemKey in varchar2,
334 firstPage in varchar2 ,
335 adminMode in varchar2) RETURN varchar2 is
336 l_url varchar2(4000);
337 l_regionToDisplay varchar2(30) := 'WF_G_MONITOR_HISTORY_PAGE';
338 l_accessKey varchar2(2048);
339 l_adminMode varchar2(2048);
340 l_itemType varchar2(2000);
341 l_itemKey varchar2(2000);
342
343 BEGIN
344
345 if (firstPage = 'DIAGRAM') then
346 l_regionToDisplay := 'WF_G_MONITOR_DIAGRAM_PAGE';
347 end if;
348
349 --
350 -- Encode all parameters.
351 --
352
353 l_accessKey := wfa_html.conv_special_url_chars(getEncryptedAccessKey(itemType, itemKey, adminMode));
354
355 l_adminMode := wfa_html.conv_special_url_chars(icx_call.encrypt(adminMode));
356
357 l_itemType := wfa_html.conv_special_url_chars(itemType);
358
359 l_itemKey := wfa_html.conv_special_url_chars(itemKey);
360
361 l_url := getGuestMonitorURL('0', l_regionToDisplay, l_accessKey, l_adminMode,
362 l_itemType, l_itemKey);
363
364 return l_url;
365
366 EXCEPTION
367 when others then
368 raise;
369
370 END getAnonymousAdvanceURL;
371
372
373 FUNCTION getGuestMonitorURL (akRegionApplicationId in varchar2 ,
374 akRegionCode in varchar2 ,
375 accessKey in varchar2 ,
376 adminMode in varchar2 ,
377 itemType in varchar2 ,
378 itemKey in varchar2 ) RETURN varchar2 is
379
380 l_url varchar2(4000);
381
382 begin
383
384 l_url := FND_WEB_CONFIG.PLSQL_AGENT||'wf_fwkmon.GuestMonitor'||
385 '?akRegionApplicationId='||akRegionApplicationId||
386 '&'||'akRegionCode='||akRegionCode||
387 '&'||'wa='||accessKey||
388 '&'||'wm='||adminMode||
389 '&'||'itemType='||itemType||
390 '&'||'itemKey='||itemKey;
391
392 return l_url;
393
394 end getGuestMonitorURL;
395
396
397 PROCEDURE GuestMonitor (akRegionApplicationId in varchar2 ,
398 akRegionCode in varchar2 ,
399 wa in varchar2 ,
400 wm in varchar2 ,
401 itemType in varchar2 ,
402 itemKey in varchar2 ) is
403
404 l_session_id number;
405 l_validate boolean;
406 l_url varchar2(4000);
407 l_dbc varchar2(240);
408 l_language_code varchar2(30);
409 l_transaction_id number;
410 l_accessKey varchar2(2000);
411 l_adminMode varchar2(2000);
412 l_itemType varchar2(2000);
413 l_itemKey varchar2(2000);
414
415 begin
416
417 -- If the user already has an ICX session, use that. Otherwise, create a new
418 -- one for the GUEST user and assign the PREFERENCES responsibility.
419
420 if not icx_sec.validateSession(c_validate_only => 'Y')
421
422 then
423
424 -- user_id 6 is the seeded id for username GUEST
425
426 l_session_id := icx_sec.createSession
427 (p_user_id => 6,
428 c_mode_code => '115X');
429
430
431 l_validate := icx_sec.validateSessionPrivate(c_session_id => l_session_id,
432 c_validate_only => 'Y');
433
434 owa_util.mime_header('text/html', FALSE);
435
436 icx_sec.sendsessioncookie(l_session_id);
437
438 -- Set the responsibility to the PREFERENCES responsibility (which
439 -- we can assume the GUEST user has). This points to the
440 -- ICX_PREFERENCES menu, to which the "Guest" monitor application
441 -- menus have been added.
442
443 l_transaction_id := icx_sec.createTransaction(
444 p_session_id => l_session_id,
445 p_resp_appl_id => 178,
446 p_responsibility_id => 20873,
447 p_security_group_id => 0);
448
449 icx_sec.updateSessionContext(p_application_id => 178,
450 p_responsibility_id => 20873,
451 p_security_group_id => 0,
452 p_session_id => l_session_id,
453 p_transaction_id => l_transaction_id);
454
455 else
456
457 -- We are reusing a preexisting session, so we need to
458 -- get the transaction_id for the url. You could have multiple
459 -- txn ids per function, or none.
460
461 BEGIN
462
463 SELECT max(transaction_id)
464 INTO l_transaction_id
465 FROM icx_transactions
466 WHERE session_id = icx_sec.g_session_id
467 AND responsibility_id = icx_sec.g_responsibility_id
468 AND security_group_id = icx_sec.g_security_group_id
469 AND function_id = icx_sec.g_function_id
470 GROUP BY transaction_id;
471
472 EXCEPTION
473 WHEN NO_DATA_FOUND THEN
474
475 SELECT icx_transactions_s.nextval
476 INTO l_transaction_id
477 FROM sys.dual;
478
479 END;
480
481 end if;
482
483 -- Get the framework agent and make sure it always has a trailing slash.
484
485 l_url := fnd_web_config.trail_slash(fnd_profile.value('APPS_FRAMEWORK_AGENT'));
486
487 fnd_profile.get(name => 'APPS_DATABASE_ID',
488 val => l_dbc);
489
490 if l_dbc is null
491 then
492 l_dbc := FND_WEB_CONFIG.DATABASE_ID;
493 end if;
494
495 --
496 -- Encode all the parameters (they would have been decoded when GuestMonitor was called)
497 --
498
499 l_accessKey := wfa_html.conv_special_url_chars(wa);
500
501 l_adminMode := wfa_html.conv_special_url_chars(wm);
502
503 l_itemType := wfa_html.conv_special_url_chars(itemType);
504
505 l_itemKey := wfa_html.conv_special_url_chars(itemKey);
506
507 l_url := l_url||'OA_HTML/';
508
509 l_url := l_url||'OA.jsp?'||'akRegionCode='||akRegionCode||
510 '&'||'akRegionApplicationId='||akRegionApplicationId||
511 '&'||'dbc='||l_dbc||
512 -- '&'||'language_code='||icx_sec.g_language_code||
513 '&'||'transactionid='||l_transaction_id||
514 '&'||'wa='||l_accessKey||
515 '&'||'wm='||l_adminMode||
516 '&'||'itemType='||l_itemType||
517 '&'||'itemKey='||l_itemKey||
518 '&'||'wia=Y';
519
520 owa_util.redirect_url(l_url);
521
522 end GuestMonitor;
523
524 --
525 -- GetNtfResponderName
526 -- Function to return the Notification Responder's display name
527 --
528 -- IN
529 -- p_notification_id - Notification ID
530 -- RETURN
531 -- Responder's display anme
532 --
533 function GetNtfResponderName(p_notification_id in number)
534 return varchar2
535 is
536 l_responder wf_notifications.responder%type;
537 l_username varchar2(360);
538 begin
539
540 SELECT responder
541 INTO l_responder
542 FROM wf_notifications
543 WHERE notification_id = p_notification_id;
544
545 -- Check if directory service has display name
546 l_username := wf_directory.GetRoleDisplayName2(l_responder);
547
548 if (l_username is not null) then
549 return l_username;
550 end if;
551
552 -- If the responder was purged from directory service, check for the
553 -- denormalized value from wf_comments table
554 SELECT wc.from_user
555 INTO l_username
556 FROM wf_notifications wn,
557 wf_comments wc
558 WHERE wn.notification_id = p_notification_id
559 AND wn.notification_id = wc.notification_id
560 AND wn.responder = wc.from_role
561 AND wc.action_type = 'RESPOND'
562 AND wc.action like 'RESPOND%'
563 AND rownum = 1;
564
565 return l_username;
566
567 exception
568 when no_data_found then
569 return null;
570 when others then
571 wf_core.context('wf_fwkmon', 'GetNtfResponderName', to_char(p_notification_id));
572 raise;
573 end GetNtfResponderName;
574
575 end wf_fwkmon;
[Home] [Help]