[Home] [Help]
PACKAGE BODY: APPS.WF_OAM_METRICS
Source
1 package body WF_OAM_METRICS as
2 /* $Header: WFOAMMTB.pls 120.2 2006/05/22 06:52:12 aiqbal noship $ */
3
4 --workItemsStatConcurrent
5 -- This procedure is invoked by the concurrent program FNDWFWITSTATCC
6 -- to populate the metrics data corresponding to Work Items.
7 procedure workItemsStatConcurrent(errorBuf OUT NOCOPY VARCHAR2,
8 errorCode OUT NOCOPY VARCHAR2) is
9
10 begin
11
12 populateWorkItemsGraphData();
13 populateActiveWorkItemsData();
14 populateErroredWorkItemsData();
15 populateDeferredWorkItemsData();
16 populateSuspendedWorkItemsData();
17 populateCompletedWorkItemsData();
18
19 commit;
20 errorCode := '0';
21
22 exception
23 when others then
24 errorCode := '2';
25 wf_core.context('WF_METRICS', 'workItemsStatConcurrent' );
26 raise;
27
28 end workItemsStatConcurrent;
29
30
31 procedure populateWorkItemsGraphData
32 is
33
34 activeCount pls_integer :=0 ;
35 deferredCount pls_integer :=0;
36 suspendedCount pls_integer :=0;
37 erroredCount pls_integer := 0;
38
39 Begin
40
41 SELECT count(item_key) into activeCount FROM
42 (select /*+ PARALLEL(wf_item_activity_statuses) */
43 distinct item_type, item_key from wf_item_activity_statuses
44 WHERE activity_status in ('ACTIVE','NOTIFIED','WAITING'));
45
46 SELECT count(*) into deferredCount FROM wf_item_activity_statuses WHERE activity_status = 'DEFERRED';
47
48 SELECT count(item_key) into erroredCount FROM
49 (select /*+ PARALLEL(wf_item_activity_statuses) */
50 distinct item_type, item_key from wf_item_activity_statuses
51 WHERE activity_status = 'ERROR');
52
53 SELECT count(*) into suspendedCount FROM wf_item_activity_statuses WHERE activity_status = 'SUSPEND';
54
55 update FND_USER_PREFERENCES set preference_value = to_char(activeCount) where preference_name = 'NUM_ACTIVE' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFItemsGraph';
56
57 update FND_USER_PREFERENCES set preference_value = to_char(deferredCount) where preference_name = 'NUM_DEFER' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFItemsGraph';
58
59 update FND_USER_PREFERENCES set preference_value = to_char(erroredCount) where preference_name = 'NUM_ERROR' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFItemsGraph';
60
61 update FND_USER_PREFERENCES set preference_value = to_char(suspendedCount) where preference_name = 'NUM_SUSPEND' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFItemsGraph';
62
63 --Update the Last Updated Time
64 update fnd_user_preferences
65 set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
66 where preference_name = 'LAST_UPDATE_TIME' and
67 module_name = 'WFManagerWFItemsGraph' and
68 user_name = '-WF_DEFAULT-';
69
70 exception
71 when others then
72 wf_core.context('WF_METRICS', 'populateWorkItemGraphsData' );
73 raise;
74
75
76 end populateWorkItemsGraphData;
77
78
79 procedure populateActiveWorkItemsData
80 is
81
82 l_item_type dbms_sql.VARCHAR2_table;
83 l_cnt dbms_sql.NUMBER_table;
84
85 cursor wf_items_cursor is
86 SELECT /*+ PARALLEL(wf_item_activity_statuses) */
87 item_type, count(distinct(item_key)) cnt
88 FROM wf_item_activity_statuses
89 WHERE activity_status in ('ACTIVE','NOTIFIED','WAITING')
90 GROUP BY item_type ORDER BY item_type;
91 begin
92
93 update wf_item_types set NUM_ACTIVE = 0;
94
95 open wf_items_cursor;
96
97 loop
98 fetch wf_items_cursor bulk collect into l_item_type, l_cnt limit 1000;
99
100 forall i in 1 .. l_item_type.COUNT
101 update wf_item_types set NUM_ACTIVE = l_cnt(i)
102 where name = l_item_type(i);
103
104 exit when wf_items_cursor%notfound;
105
106 end loop;
107 close wf_items_cursor;
108
109 update fnd_user_preferences
110 set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
111 where preference_name = 'LAST_UPDATE_TIME' and
112 module_name = 'WFManagerActiveWFItems' and
113 user_name = '-WF_DEFAULT-';
114
115 exception
116 when others then
117 if wf_items_cursor%ISOPEN then
118 close wf_items_cursor;
119 end if;
120 wf_core.context('WF_METRICS', 'populateActiveWorkItemsData' );
121 raise;
122
123 end populateActiveWorkItemsData;
124
125
126 procedure populateErroredWorkItemsData
127 is
128
129 l_item_type dbms_sql.VARCHAR2_table;
130 l_cnt dbms_sql.NUMBER_table;
131
132 cursor wf_items_cursor is
133 SELECT item_type, count(distinct(item_key)) cnt
134 FROM wf_item_activity_statuses
135 WHERE activity_status = 'ERROR'
136 GROUP BY item_type ORDER BY item_type;
137 begin
138
139 update wf_item_types set NUM_ERROR = 0;
140
141 open wf_items_cursor;
142
143 loop
144
145 fetch wf_items_cursor Bulk Collect into l_item_type, l_cnt limit 1000;
146
147 forall i in 1 .. l_item_type.COUNT
148 update wf_item_types set NUM_ERROR = l_cnt(i)
149 where name = l_item_type(i);
150
151 exit when wf_items_cursor%notfound;
152
153 end loop;
154 close wf_items_cursor;
155
156 update fnd_user_preferences
157 set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
158 where preference_name = 'LAST_UPDATE_TIME' and
159 module_name = 'WFManagerErrorWFItems' and
160 user_name = '-WF_DEFAULT-';
161
162 exception
163 when others then
164 if wf_items_cursor%isopen then
165 close wf_items_cursor;
166 end if;
167 wf_core.context('WF_METRICS', 'populateErroredWorkItemsData' );
168 raise;
169
170 end populateErroredWorkItemsData;
171
172
173 procedure populateDeferredWorkItemsData
174 is
175
176 l_item_type dbms_sql.VARCHAR2_table;
177 l_cnt dbms_sql.NUMBER_table;
178
179 cursor wf_items_cursor is
180 SELECT item_type, count(distinct(item_key)) cnt
181 FROM wf_item_activity_statuses
182 WHERE activity_status = 'DEFERRED'
183 GROUP BY item_type ORDER BY item_type;
184 begin
185
186 update wf_item_types set NUM_DEFER = 0;
187
188 open wf_items_cursor;
189
190 loop
191
192 fetch wf_items_cursor Bulk Collect into l_item_type, l_cnt limit 1000;
193
194 forall i in 1 .. l_item_type.COUNT
195 update wf_item_types set NUM_DEFER = l_cnt(i)
196 where name = l_item_type(i);
197
198 exit when wf_items_cursor%notfound;
199
200 end loop;
201 close wf_items_cursor;
202
203 update fnd_user_preferences
204 set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
205 where preference_name = 'LAST_UPDATE_TIME' and
206 module_name = 'WFManagerDeferWFItems' and
207 user_name = '-WF_DEFAULT-';
208
209 exception
210 when others then
211 if wf_items_cursor%isopen then
212 close wf_items_cursor;
213 end if;
214 wf_core.context('WF_METRICS', 'populateDeferredWorkItemsData' );
215 raise;
216
217 end populateDeferredWorkItemsData;
218
219
220 procedure populateSuspendedWorkItemsData
221 is
222
223 l_item_type dbms_sql.VARCHAR2_table;
224 l_cnt dbms_sql.NUMBER_table;
225
226 cursor wf_items_cursor is
227 SELECT item_type, count(distinct(item_key)) cnt
228 FROM wf_item_activity_statuses
229 WHERE activity_status = 'SUSPEND'
230 GROUP BY item_type ORDER BY item_type;
231 begin
232
233 update wf_item_types set NUM_SUSPEND = 0;
234
235 open wf_items_cursor;
236
237 loop
238
239 fetch wf_items_cursor Bulk Collect into l_item_type, l_cnt limit 1000;
240
241 forall i in 1 .. l_item_type.COUNT
242 update wf_item_types set NUM_SUSPEND = l_cnt(i)
243 where name = l_item_type(i);
244
245 exit when wf_items_cursor%notfound;
246
247 end loop;
248 close wf_items_cursor;
249
250 update fnd_user_preferences
251 set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
252 where preference_name = 'LAST_UPDATE_TIME' and
253 module_name = 'WFManagerSuspendWFItems' and
254 user_name = '-WF_DEFAULT-';
255
256 exception
257 when others then
258 if wf_items_cursor%isopen then
259 close wf_items_cursor;
260 end if;
261 wf_core.context('WF_METRICS', 'populateSuspendedWorkItemsData' );
262 raise;
263
264 end populateSuspendedWorkItemsData;
265
266
267 procedure populateCompletedWorkItemsData
268 is
269
270 l_item_type dbms_sql.VARCHAR2_table;
271 l_cnt dbms_sql.NUMBER_table;
272 l_purgeCnt dbms_sql.NUMBER_table;
273
274 cursor wf_items_cursor is
275 SELECT /*+ PARALLEL(wi) */ wi.item_type, count(wi.item_key) cnt
276 FROM wf_items wi
277 WHERE wi.end_date IS NOT NULL
278 GROUP BY wi.item_type
279 order by wi.item_type;
280 begin
281
282 update wf_item_types set NUM_COMPLETE = 0, NUM_PURGEABLE = 0;
283
284 open wf_items_cursor;
285 loop
286 fetch wf_items_cursor bulk collect into l_item_type, l_cnt limit 1000;
287
288 for i in 1 .. l_item_type.COUNT loop
289 l_purgeCnt(i) := wf_purge.getpurgeablecount(l_item_type(i));
290 end loop;
291
292 forall i in 1 .. l_item_type.COUNT
293 update wf_item_types set NUM_COMPLETE = l_cnt(i),
294 NUM_PURGEABLE = l_purgeCnt(i)
295 where name = l_item_type(i);
296
297 exit when wf_items_cursor%notfound;
298
299 end loop;
300 close wf_items_cursor;
301
302 update fnd_user_preferences
303 set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
304 where preference_name = 'LAST_UPDATE_TIME' and
305 module_name = 'WFManagerCompleteWFItems' and
306 user_name = '-WF_DEFAULT-';
307
308 exception
309 when others then
310 if wf_items_cursor%ISOPEN then
311 close wf_items_cursor;
312 end if;
313 wf_core.context('WF_METRICS', 'populateCompletedWorkItemsData' );
314 raise;
315
316 end populateCompletedWorkItemsData;
317
318
319 --agentActivityStatConcurrent
320 -- This procedure is invoked by the concurrent program FNDWFAASTATCC
321 -- to populate the metrics data corresponding to Agent Activity.
322 procedure agentActivityStatConcurrent(errorBuf OUT NOCOPY VARCHAR2,
323 errorCode OUT NOCOPY VARCHAR2) is
324
325 begin
326
327 populateAgentActivityGraphData();
328 populateAgentActivityData();
329
330 commit;
331
332 errorCode:= '0';
333
334 exception
335 when others then
336 errorCode := '2';
337 wf_core.context('WF_METRICS', 'agentActivityStatConcurrent' );
338 raise;
339
340 end agentActivityStatConcurrent;
341
342 procedure populateAgentActivityGraphData
343 is
344
345 readyCount number := 0;
346 waitingCount number := 0;
347 processedCount number := 0;
348 expiredCount number := 0;
349 undeliverableCount number := 0;
350 erroredCount number := 0;
351 agentname varchar2(10) := '%';
352 begin
353
354 wf_queue.getcntmsgst(agentname, readyCount, waitingCount, processedCount,
355 expiredCount, undeliverableCount, erroredCount);
356
357
358 update FND_USER_PREFERENCES set preference_value = to_char(readyCount) where preference_name = 'NUM_READY' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFAgentsGraph';
359
360 update FND_USER_PREFERENCES set preference_value = to_char(waitingCount) where preference_name = 'NUM_WAITING' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFAgentsGraph';
361
362 update FND_USER_PREFERENCES set preference_value = to_char(processedCount) where preference_name = 'NUM_PROCESSED' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFAgentsGraph';
363
364 update FND_USER_PREFERENCES set preference_value = to_char(expiredCount) where preference_name = 'NUM_EXPIRED' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFAgentsGraph';
365
366 update FND_USER_PREFERENCES set preference_value = to_char(undeliverableCount) where preference_name = 'NUM_UNDELIV' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFAgentsGraph';
367
368 update FND_USER_PREFERENCES set preference_value = to_char(erroredCount) where preference_name = 'NUM_ERROR' and user_name='-WF_DEFAULT-' and module_name='WFManagerWFAgentsGraph';
369
370 update fnd_user_preferences
371 set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
372 where preference_name = 'LAST_UPDATE_TIME' and
373 module_name = 'WFManagerWFAgentsGraph' and
374 user_name = '-WF_DEFAULT-';
375
376 exception
377 when others then
378 wf_core.context('WF_METRICS', 'populateAgentActivityGraphData' );
379 raise;
380
381 end populateAgentActivityGraphData;
382
383
384 procedure populateAgentActivityData
385 is
386
387 readyCount number := 0;
388 waitingCount number := 0;
389 processedCount number := 0;
390 expiredCount number := 0;
391 undeliverableCount number := 0;
392 erroredCount number := 0;
393
394 agentName varchar2(60);
395
396 cursor wf_agents_cursor is
397 SELECT distinct(queue_name) as queue_name FROM wf_agents
398 WHERE queue_name is not null
399 AND queue_name not like '%WF_SMTP_O_1_QUEUE'
400 AND system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
401
402
403 begin
404
405 UPDATE wf_agents
406 SET NUM_READY = 0, NUM_WAITING = 0, NUM_EXPIRED = 0, NUM_UNDELIV = 0,
407 NUM_ERROR = 0, NUM_PROCESS = 0;
408
409
410 for agent_row in wf_agents_cursor loop
411 readyCount := 0;
412 waitingCount := 0;
413 processedCount := 0;
414 expiredCount := 0;
415 undeliverableCount := 0;
416 erroredCount := 0;
417
418 agentName := extractAgentName(agent_row.queue_name);
419
420 wf_queue.getcntmsgst(agentName, readyCount, waitingCount,
421 processedCount, expiredCount, undeliverableCount,
422 erroredCount);
423
424 update wf_agents
425 SET NUM_READY = readyCount, NUM_WAITING = waitingCount,
426 NUM_EXPIRED = expiredCount, NUM_UNDELIV = undeliverableCount,
430
427 NUM_ERROR = erroredCount, NUM_PROCESS = processedCount
428 WHERE queue_name = agent_row.queue_name and
429 system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
431 end loop;
432
433 update fnd_user_preferences
434 set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
435 where preference_name = 'LAST_UPDATE_TIME' and
436 module_name = 'WFManagerWFAgents' and
437 user_name = '-WF_DEFAULT-';
438
439 exception
440 when others then
441 wf_core.context('WF_METRICS', 'populateAgentActivityData' );
442 raise;
443
444 end populateAgentActivityData;
445
446
447 function extractAgentName(pName varchar2) return varchar2
448 is
449 agentName varchar2(60);
450
451 begin
452
453 if instr(pName, '.') = 0 then
454 agentName := pName;
455
456 else
457 agentName := substr(pName, instr(pName, '.')+1);
458
459 end if;
460
461 return agentName;
462
463 exception
464 when others then
465 wf_core.context('WF_METRICS', 'extractAgentName' );
466 raise;
467
468 end extractAgentName;
469
470
471
472 --ntfMailerStatConcurrent
473 -- This procedure is invoked by the concurrent program FNDWFMLRSTATCC
474 -- to populate the Notification Mailer throughput data.
475 procedure ntfMailerStatConcurrent(errorBuf OUT NOCOPY VARCHAR2,
476 errorCode OUT NOCOPY VARCHAR2)
477 is
478
479 begin
480
481 populateNtfMailerGraphData();
482 commit;
483
484 errorCode:= '0';
485
486 exception
487 when others then
488 errorCode := '2';
489 wf_core.context('WF_METRICS', 'ntfMailerStatConcurrent' );
490 raise;
491
492
493 end ntfMailerStatConcurrent;
494
495
496 procedure populateNtfMailerGraphData
497 is
498
499 processedCount number := 0;
500 waitingCount number := 0;
501
502 begin
503
504 SELECT count(*) into processedCount FROM wf_notifications
505 WHERE mail_status = 'SENT' AND status = 'OPEN';
506
507 SELECT count(*) into waitingCount FROM wf_notifications
508 WHERE mail_status = 'MAIL';
509
510 UPDATE FND_USER_PREFERENCES
511 SET preference_value = to_char(processedCount)
512 WHERE preference_name = 'NUM_PROCESSED' and
513 user_name='-WF_DEFAULT-' and module_name='WFManagerWFNtfsGraph';
514
515 UPDATE FND_USER_PREFERENCES SET preference_value = to_char(waitingCount) WHERE preference_name = 'NUM_WAITING' and
516 user_name='-WF_DEFAULT-' and module_name='WFManagerWFNtfsGraph';
517
518
519 UPDATE fnd_user_preferences
520 set preference_value = to_char(sysdate,'dd/MM/YYYY HH24:MI:SS')
521 where preference_name = 'LAST_UPDATE_TIME' and
522 module_name = 'WFManagerWFNtfsGraph' and
523 user_name = '-WF_DEFAULT-';
524
525 exception
526 when others then
527 wf_core.context('WF_METRICS', 'populateNtfMailerGraphData' );
528 raise;
529
530
531 end populateNtfMailerGraphData;
532
533
534 END WF_OAM_METRICS;