[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_BF_UTIL
Source
1 package body FND_OAM_BF_UTIL as
2 /* $Header: AFOAMFLB.pls 120.3 2005/08/13 00:49:01 ppradhan noship $ */
3
4 --
5 -- Debug flag - Set this to FALSE before checking in
6 --
7 g_debug constant boolean := FALSE;
8
9 --
10 -- Debug Only
11 --
12 PROCEDURE debug(pStr Varchar2)
13 IS
14
15 BEGIN
16 --dbms_output.put_line(pStr);
17 null;
18 END;
19
20 FUNCTION get_user_id RETURN number
21 IS
22 v_userid number;
23 v_conc_req_id number;
24 BEGIN
25 -- check if its concurrent request
26 select fnd_global.conc_request_id into v_conc_req_id from dual;
27 if v_conc_req_id > 0 then
28 select fcr.requested_by into v_userid
29 from fnd_concurrent_requests fcr
30 where fcr.request_id = v_conc_req_id;
31 else
32 select fnd_global.user_id into v_userid from dual;
33 if (v_userid < 0 or v_userid is null) then
34 v_userid := 0; -- default
35 end if;
36 end if;
37
38 return v_userid;
39 EXCEPTION
40 when others then
41 v_userid := 0;
42 return v_userid;
43 END get_user_id;
44
45 PROCEDURE load_wit_info(
46 p_item_type varchar2,
47 p_count_errored_items number,
48 p_count_active_items number)
49 IS
50 v_x number;
51 v_userid number := 0;
52 BEGIN
53 v_userid := get_user_id;
54 select 1 into v_x
55 from fnd_oam_bf_wit_info
56 where item_type = p_item_type;
57
58 -- update
59 update fnd_oam_bf_wit_info
60 set count_errored_items = p_count_errored_items,
61 count_active_items = p_count_active_items,
62 last_updated_by = v_userid,
63 last_update_date = sysdate,
64 last_update_login = 0
65 where item_type = p_item_type;
66 EXCEPTION
67 when no_data_found then
68 insert into fnd_oam_bf_wit_info (
69 item_type,
70 count_errored_items,
71 count_active_items,
72 created_by,
73 creation_date,
74 last_updated_by,
75 last_update_date,
76 last_update_login)
77 values (
78 p_item_type,
79 p_count_errored_items,
80 p_count_active_items,
81 v_userid,
82 sysdate,
83 v_userid,
84 sysdate,
85 0);
86 when others then
87 raise;
88 END load_wit_info;
89
90 PROCEDURE load_comp_info(
91 p_component_type varchar2,
92 p_component_appl_id number,
93 p_component_id number,
94 p_count_alerts number,
95 p_count_errored_requests number,
96 p_setup_status number,
97 p_test_status number,
98 p_diagnostic_test_status number,
99 p_count_running_requests number,
100 p_count_form_sessions number,
101 p_count_ssf_sessions number)
102 IS
103 v_x number;
104 v_userid number := 0;
105 BEGIN
106 v_userid := get_user_id;
107 select 1 into v_x
108 from fnd_oam_bf_comp_info
109 where component_type = p_component_type
110 and component_appl_id = p_component_appl_id
111 and component_id = p_component_id;
112
113 -- update
114 update fnd_oam_bf_comp_info
115 set count_alerts = p_count_alerts,
116 count_errored_requests = p_count_errored_requests,
117 setup_status = p_setup_status,
118 test_status = p_test_status,
119 diagnostic_test_status = p_diagnostic_test_status,
120 count_running_requests = p_count_running_requests,
121 count_form_sessions = p_count_form_sessions,
122 count_ssf_sessions = p_count_ssf_sessions,
123 last_updated_by = v_userid,
124 last_update_date = sysdate,
125 last_update_login = 0
126 where component_type = p_component_type
127 and component_appl_id = p_component_appl_id
128 and component_id = p_component_id;
129 EXCEPTION
130 when no_data_found then
131 insert into fnd_oam_bf_comp_info (
132 component_type,
133 component_appl_id,
134 component_id,
135 count_alerts,
136 count_errored_requests,
137 setup_status,
138 test_status,
139 diagnostic_test_status,
140 count_running_requests,
141 count_form_sessions,
142 count_ssf_sessions,
143 created_by,
144 creation_date,
145 last_updated_by,
146 last_update_date,
147 last_update_login)
148 values (
149 p_component_type,
150 p_component_appl_id,
151 p_component_id,
152 p_count_alerts,
153 p_count_errored_requests,
154 p_setup_status,
155 p_test_status,
156 p_diagnostic_test_status,
157 p_count_running_requests,
158 p_count_form_sessions,
159 p_count_ssf_sessions,
160 v_userid,
161 sysdate,
162 v_userid,
163 sysdate,
164 0);
165 when others then
166 raise;
167 END load_comp_info;
168
169 PROCEDURE load_rollup_info(
170 p_biz_flow_key varchar2,
171 p_count_alerts number,
172 p_count_errored_requests number,
173 p_count_errored_work_items number,
174 p_setup_status number,
175 p_test_status number,
176 p_diagnostic_test_status number,
177 p_count_running_requests number,
178 p_count_form_sessions number,
179 p_count_ssf_sessions number,
180 p_count_active_work_items number)
181 IS
182 v_x number;
183 v_userid number := 0;
184 BEGIN
185 v_userid := get_user_id;
186 select 1 into v_x
187 from fnd_oam_bf_rollup_info
188 where biz_flow_key = p_biz_flow_key;
189
190 -- update
191 update fnd_oam_bf_rollup_info
192 set count_alerts = p_count_alerts,
193 count_errored_requests = p_count_errored_requests,
194 count_errored_work_items = p_count_errored_work_items,
195 setup_status = p_setup_status,
196 test_status = p_test_status,
197 diagnostic_test_status = p_diagnostic_test_status,
198 count_running_requests = p_count_running_requests,
199 count_form_sessions = p_count_form_sessions,
200 count_ssf_sessions = p_count_ssf_sessions,
201 count_active_work_items = p_count_active_work_items,
202 last_updated_by = v_userid,
203 last_update_date = sysdate,
204 last_update_login = 0
205 where biz_flow_key = p_biz_flow_key;
206 EXCEPTION
207 when no_data_found then
208 insert into fnd_oam_bf_rollup_info (
209 biz_flow_key,
210 count_alerts,
211 count_errored_requests,
212 count_errored_work_items,
213 setup_status,
214 test_status,
215 diagnostic_test_status,
216 count_running_requests,
217 count_form_sessions,
218 count_ssf_sessions,
219 count_active_work_items,
220 created_by,
221 creation_date,
222 last_updated_by,
223 last_update_date,
224 last_update_login)
225 values (
226 p_biz_flow_key,
227 p_count_alerts,
228 p_count_errored_requests,
229 p_count_errored_work_items,
230 p_setup_status,
231 p_test_status,
232 p_diagnostic_test_status,
233 p_count_running_requests,
234 p_count_form_sessions,
235 p_count_ssf_sessions,
236 p_count_active_work_items,
237 v_userid,
238 sysdate,
239 v_userid,
240 sysdate,
241 0);
242 when others then
243 raise;
244 END load_rollup_info;
245
246 --
247 -- Name
248 -- compute_metrics
249 --
250 -- Purpose
251 -- computes metrics related to business flows such as
252 -- - count of open system alerts
253 -- - count of errored concurrent requests
254 -- - count of errored work items
255 -- - count of active work items
256 -- - count of running concurrent requests
257 -- - count of active form sessions
258 -- - count of active ssf sessions (last hour)
259 --
260 -- The resulting values will get populated into fnd_oam_bf_comp_info,
261 -- fnd_oam_bf_wit_info tables.
262 --
263 -- Input Arguments
264 --
265 -- Output Arguments
266 --
267 -- Notes:
268 --
269 --
270 PROCEDURE compute_metrics
271 IS
272 cursor c_comps is
273 select distinct component_type, component_appl_id, component_id
274 from fnd_oam_bf_comp
275 union
276 select distinct component_type, component_appl_id, component_id
277 from fnd_oam_bf_comp_cust;
278 cursor c_wits is
279 select distinct item_type
280 from fnd_oam_bf_wit
281 union
282 select distinct item_type
283 from fnd_oam_bf_wit_cust ;
284 BEGIN
285 -- for each associated components count the
286 -- number of open and new system
287 -- alerts and errored concurrent requests
288 for comp in c_comps loop
289 declare
290 v_alerts number;
291 v_err_requests number;
292 v_running_requests number;
293 v_form_sessions number;
294 v_ssf_sessions number;
295 begin
296 -- count the open or new system alerts for this component
297 select count(distinct(flue.unique_exception_id))
298 into v_alerts
299 from fnd_log_unique_exceptions flue,
300 fnd_log_messages flm,
301 fnd_log_exceptions fle,
302 fnd_log_transaction_context fltc
303 where fltc.transaction_context_id = flm.transaction_context_id
304 and flm.log_sequence = fle.log_sequence
305 and fle.unique_exception_id = flue.unique_exception_id
306 and fltc.component_type = comp.component_type
307 and fltc.component_id = comp.component_id
308 and nvl(fltc.component_appl_id, -1) = comp.component_appl_id
309 and flue.status in ('O','N');
310
311 -- count the errored requests for this component, within
312 -- last 24 hours
313 if comp.component_type = 'CONCURRENT_PROGRAM' then
314 select count(*) into v_err_requests
315 from fnd_concurrent_requests
316 where phase_code='C'
317 and status_code='E'
318 and concurrent_program_id = comp.component_id
319 and program_application_id = comp.component_appl_id
320 and actual_completion_date > sysdate - 1;
321
322 select count(*) into v_running_requests
323 from fnd_concurrent_requests
324 where phase_code = 'R'
325 and concurrent_program_id = comp.component_id
326 and program_application_id = comp.component_appl_id;
327 end if;
328
329 -- compute FORM specific metrics
330 if comp.component_type = 'FORM' then
331 select count(*) into v_form_sessions
332 from fnd_form_sessions_v
333 where form_id = comp.component_id
334 and form_appl_id = comp.component_appl_id;
335 end if;
336
337 -- compute FUNCTION specific metrics
338 if comp.component_type = 'FUNCTION' then
339 -- ssf sessions within the last hour for the given function
340 select count(*) into v_ssf_sessions
341 from icx_sessions
342 where function_id = comp.component_id
343 and last_connect > sysdate - 1/24;
344 end if;
345
346 load_comp_info(
347 p_component_type => comp.component_type,
348 p_component_appl_id => comp.component_appl_id,
349 p_component_id => comp.component_id,
350 p_count_alerts => v_alerts,
351 p_count_errored_requests => v_err_requests,
352 p_setup_status => null,
353 p_test_status => null,
354 p_diagnostic_test_status => null,
355 p_count_running_requests => v_running_requests,
356 p_count_form_sessions => v_form_sessions,
357 p_count_ssf_sessions => v_ssf_sessions);
358 end;
359 end loop;
360
361 -- for each associated work item type, count the
362 -- number of errored work items
363 for wit_x in c_wits loop
364 declare
365 v_err_wi number;
366 v_active_wi number;
367 begin
368 select count(distinct(item_key))
369 into v_err_wi
370 from wf_item_activity_statuses wias,
371 wf_item_types wit
372 where wias.activity_status = 'ERROR'
373 and wias.item_type = wit.name
374 and wias.item_type = wit_x.item_type;
375
376 select count(distinct(item_key))
377 into v_active_wi
378 from wf_items i
379 where i.end_date is null
380 and i.item_type = wit_x.item_type;
381
382 load_wit_info(
383 p_item_type => wit_x.item_type,
384 p_count_errored_items => v_err_wi,
385 p_count_active_items => v_active_wi);
386 end;
387 end loop;
388 END compute_metrics;
389
390 --
391 -- Computes the rolled values for the given metrics and loads it into the
392 -- fnd_oam_bf_rollup_info table. Rollup values are based only on
393 -- associations for which the monitored_flag='Y'
394 --
395 -- 1) p_count_alerts - Total count of open/new alerts for this flow
396 -- 2) p_count_err_requests - Total count of errored concurrent requests
397 -- for this flow.
398 -- 3) p_count_err_wi - Total count of errored work items
399 --
400 PROCEDURE load_rollup(
401 p_flow_key varchar2,
402 p_count_alerts OUT NOCOPY number,
406 p_count_form_sessions OUT NOCOPY number,
403 p_count_err_requests OUT NOCOPY number,
404 p_count_err_wi OUT NOCOPY number,
405 p_count_running_requests OUT NOCOPY number,
407 p_count_ssf_sessions OUT NOCOPY number,
408 p_count_active_wi OUT NOCOPY number)
409 IS
410 v_flow_key fnd_oam_bf.biz_flow_key%type := p_flow_key;
411 v_count_alerts number := 0;
412 v_count_err_requests number := 0;
413 v_count_err_wi number := 0;
414 v_count_running_requests number := 0;
415 v_count_form_sessions number := 0;
416 v_count_ssf_sessions number := 0;
417 v_count_active_wi number := 0;
418
419 -- For all monitored flows under this flow
420 cursor c_monitored_flows is
421 select ba.biz_flow_child_key biz_flow_key from fnd_oam_bf_assoc ba
422 where ba.biz_flow_parent_key = p_flow_key
423 and ((ba.monitored_flag='Y' and 1 not in (
424 select count(*)
425 from fnd_oam_bf_assoc_cust cust
426 where cust.biz_flow_child_key = ba.biz_flow_child_key
427 and cust.biz_flow_parent_key = ba.biz_flow_parent_key
428 and cust.monitored_flag = 'N'))
429 or (ba.monitored_flag='N' and 1 in (
430 select count(*)
431 from fnd_oam_bf_assoc_cust cust
432 where cust.biz_flow_child_key = ba.biz_flow_child_key
433 and cust.biz_flow_parent_key = ba.biz_flow_parent_key
434 and cust.monitored_flag='Y'))
435 )
436 union
437 select ba.biz_flow_child_key biz_flow_key from fnd_oam_bf_assoc_cust ba
438 where ba.biz_flow_parent_key = p_flow_key
439 and ba.monitored_flag = 'Y'
440 and ba.biz_flow_child_key not in
441 (select x.biz_flow_child_key from fnd_oam_bf_assoc x
442 where x.biz_flow_parent_key = p_flow_key);
443
444 -- For all monitored components under this flow
445 cursor c_monitored_components is
446 select c.component_type, c.component_appl_id, c.component_id
447 from fnd_oam_bf_comp c
448 where c.biz_flow_key = p_flow_key
449 and ((c.monitored_flag='Y' and 1 not in (
450 select count(*)
451 from fnd_oam_bf_comp_cust cust
452 where cust.component_type = c.component_type
453 and cust.component_appl_id = c.component_appl_id
454 and cust.component_id = c.component_id
455 and cust.biz_flow_key = c.biz_flow_key
456 and cust.monitored_flag='N'))
457 or (c.monitored_flag = 'N' and 1 in (
458 select count(*)
459 from fnd_oam_bf_comp_cust cust
460 where cust.component_type = c.component_type
461 and cust.component_appl_id = c.component_appl_id
462 and cust.component_id = c.component_id
463 and cust.biz_flow_key = c.biz_flow_key
464 and cust.monitored_flag='Y'))
465 )
466 union
467 select c.component_type, c.component_appl_id, c.component_id
468 from fnd_oam_bf_comp_cust c
469 where c.biz_flow_key = p_flow_key
470 and c.monitored_flag = 'Y'
471 and c.component_type || ':' ||
472 c.component_appl_id || ':' ||
473 c.component_id not in
474 (select x.component_type || ':' ||
475 x.component_appl_id || ':' ||
476 x.component_id
477 from fnd_oam_bf_comp x
478 where x.biz_flow_key = p_flow_key);
479
480 -- For all monitored work item types directly associated with this flow
481 cursor c_monitored_wit is
482 select w.item_type from fnd_oam_bf_wit w
483 where w.biz_flow_key = p_flow_key
484 and ((w.monitored_flag = 'Y' and 1 not in (
485 select count(*) from fnd_oam_bf_wit_cust cust
486 where cust.item_type = w.item_type
487 and cust.biz_flow_key = w.biz_flow_key
488 and cust.monitored_flag = 'N'))
489 or (w.monitored_flag = 'N' and 1 in (
490 select count(*) from fnd_oam_bf_wit_cust cust
491 where cust.item_type = w.item_type
492 and cust.biz_flow_key = w.biz_flow_key
493 and cust.monitored_flag = 'Y'))
494 )
495 union
496 select w.item_type from fnd_oam_bf_wit_cust w
497 where w.biz_flow_key = p_flow_key
498 and w.monitored_flag = 'Y'
499 and w.item_type not in
500 (select x.item_type from fnd_oam_bf_wit x
501 where x.biz_flow_key = p_flow_key);
502
503 BEGIN
504 if (g_debug) then
505 debug('CURRENT: ' || v_flow_key);
506 end if;
507 -- get counts for components directly associated with this flow
508 for c in c_monitored_components loop
509 declare
510 v_temp_alerts number;
511 v_temp_err_req number;
512 v_temp_running_req number;
513 v_temp_form_sessions number;
514 v_temp_ssf_sessions number;
515 begin
516 select nvl(count_alerts,0), nvl(count_errored_requests,0),
517 nvl(count_running_requests,0), nvl(count_form_sessions,0),
518 nvl(count_ssf_sessions,0)
519 into v_temp_alerts, v_temp_err_req, v_temp_running_req,
520 v_temp_form_sessions, v_temp_ssf_sessions
521 from fnd_oam_bf_comp_info
522 where component_type = c.component_type
523 and component_appl_id = c.component_appl_id
524 and component_id = c.component_id;
525 v_count_alerts := v_count_alerts + v_temp_alerts;
526 v_count_err_requests := v_count_err_requests + v_temp_err_req;
527 v_count_running_requests := v_count_running_requests + v_temp_running_req;
528 v_count_form_sessions := v_count_form_sessions + v_temp_form_sessions;
529 v_count_ssf_sessions := v_count_ssf_sessions + v_temp_ssf_sessions;
530 if (g_debug) then
531 debug(c.component_type || ': ' ||
532 v_temp_alerts || ' ' || v_temp_err_req || ' ' ||
533 v_temp_running_req || ' ' || v_temp_form_sessions || ' ' ||
534 v_temp_ssf_sessions);
535 end if;
536 end;
537 end loop;
538
539 -- get counts for work item types directly associated with this flow
540 for w in c_monitored_wit loop
541 declare
542 v_temp_err_wi number := 0;
543 v_temp_active_wi number := 0;
544 begin
545 select nvl(count_errored_items,0), nvl(count_active_items,0)
546 into v_temp_err_wi, v_temp_active_wi
547 from fnd_oam_bf_wit_info
548 where item_type = w.item_type;
549 v_count_err_wi := v_count_err_wi + v_temp_err_wi;
550 v_count_active_wi := v_count_active_wi + v_temp_active_wi;
551 end;
552 end loop;
553
554 -- now add the counts for all the children
555 for fl in c_monitored_flows loop
556 declare
557 v_abs_cust_mflag varchar2(1);
558 begin
559 begin
560 -- check the absolute monitored flag for seeded flow
561 select
562 nvl(fbc.monitored_flag,fb.monitored_flag)
563 into v_abs_cust_mflag
564 from fnd_oam_bf fb, fnd_oam_bf_cust fbc
565 where fb.biz_flow_key = fbc.biz_flow_key (+)
566 and fb.biz_flow_key=fl.biz_flow_key;
567 exception
568 when no_data_found then
569 -- check the absolute monitored flag for user created flow
570 select fbc.monitored_flag
571 into v_abs_cust_mflag
572 from fnd_oam_bf_cust fbc
573 where fbc.biz_flow_key=fl.biz_flow_key;
574 end;
575
576 if (v_abs_cust_mflag = 'Y') then
577 -- we need to monitor this child so recurse
578 declare
579 x_count_alerts number;
580 x_count_err_requests number;
581 x_count_err_wi number;
582 x_count_running_requests number;
583 x_count_form_sessions number;
584 x_count_ssf_sessions number;
585 x_count_active_wi number;
586 begin
587 load_rollup(
588 p_flow_key => fl.biz_flow_key,
589 p_count_alerts => x_count_alerts,
590 p_count_err_requests => x_count_err_requests,
591 p_count_err_wi => x_count_err_wi,
592 p_count_running_requests => x_count_running_requests,
593 p_count_form_sessions => x_count_form_sessions,
594 p_count_ssf_sessions => x_count_ssf_sessions,
595 p_count_active_wi => x_count_active_wi);
596
597 load_rollup_info(
598 p_biz_flow_key => fl.biz_flow_key,
599 p_count_alerts => x_count_alerts,
600 p_count_errored_requests => x_count_err_requests,
601 p_count_errored_work_items => x_count_err_wi,
602 p_setup_status => null,
603 p_test_status => null,
604 p_diagnostic_test_status => null,
605 p_count_running_requests => x_count_running_requests,
606 p_count_form_sessions => x_count_form_sessions,
607 p_count_ssf_sessions => x_count_ssf_sessions,
608 p_count_active_work_items => x_count_active_wi);
609
610 if (g_debug) then
611 debug(fl.biz_flow_key || ':' ||
612 x_count_alerts || ' ' || x_count_err_requests || ' ' ||
613 x_count_err_wi || ' ' || x_count_running_requests ||
614 ' ' || x_count_form_sessions || ' ' ||
615 x_count_ssf_sessions || ' ' || x_count_active_wi);
616 end if;
617 v_count_alerts := v_count_alerts + x_count_alerts;
618 v_count_err_requests := v_count_err_requests + x_count_err_requests;
619 v_count_err_wi := v_count_err_wi + x_count_err_wi;
620 v_count_running_requests := v_count_running_requests + x_count_running_requests;
621 v_count_form_sessions := v_count_form_sessions + x_count_form_sessions;
622 v_count_ssf_sessions := v_count_ssf_sessions + x_count_ssf_sessions;
623 v_count_active_wi := v_count_active_wi + x_count_active_wi;
624 end;
625 end if;
626 end;
627 end loop;
628
629 -- finally update the out parameters and load the info into
630 -- fnd_oam_bf_rollup_info
631 p_count_alerts := v_count_alerts;
632 p_count_err_requests := v_count_err_requests;
633 p_count_err_wi := v_count_err_wi;
634 p_count_running_requests := v_count_running_requests;
635 p_count_form_sessions := v_count_form_sessions;
636 p_count_ssf_sessions := v_count_ssf_sessions;
637 p_count_active_wi := v_count_active_wi;
638
639
640
641 END load_rollup;
642
643
644 --
645 -- Name
646 -- rollup_metrics
647 --
648 -- Purpose
649 -- Rolls up metrics related to business flows such as
650 -- - count of open system alerts
651 -- - count of errored concurrent requests
652 -- - count of errored work items
653 --
654 -- The resulting values will get populated into fnd_oam_bf_rollup_info
655 -- tables.
656 --
657 -- Input Arguments
658 --
659 -- Output Arguments
660 --
661 -- Notes:
662 --
663 --
664 PROCEDURE rollup_metrics
665 IS
666 cursor c_monitored_key_flows is
667 select
668 fb.biz_flow_key biz_flow_key
669 from fnd_oam_bf fb, fnd_oam_bf_cust fbc
670 where fb.biz_flow_key = fbc.biz_flow_key (+)
671 and fb.is_top_level = 'Y'
672 and nvl(fbc.monitored_flag,fb.monitored_flag) = 'Y'
673 union
674 select fbc.biz_flow_key biz_flow_key
675 from fnd_oam_bf_cust fbc
676 where fbc.monitored_flag = 'Y'
677 and fbc.is_top_level = 'Y'
678 and fbc.biz_flow_key not in (
679 select fb.biz_flow_key from fnd_oam_bf fb
680 where fb.is_top_level = 'Y');
681
682 v_count_alerts number;
683 v_count_err_requests number;
684 v_count_err_wi number;
685 v_count_running_requests number;
686 v_count_form_sessions number;
687 v_count_ssf_sessions number;
688 v_count_active_wi number;
689 BEGIN
690 for fl in c_monitored_key_flows loop
691 load_rollup(
692 p_flow_key => fl.biz_flow_key,
693 p_count_alerts => v_count_alerts,
694 p_count_err_requests => v_count_err_requests,
695 p_count_err_wi => v_count_err_wi,
696 p_count_running_requests => v_count_running_requests,
697 p_count_form_sessions => v_count_form_sessions,
701 p_biz_flow_key => fl.biz_flow_key,
698 p_count_ssf_sessions => v_count_ssf_sessions,
699 p_count_active_wi => v_count_active_wi);
700 load_rollup_info(
702 p_count_alerts => v_count_alerts,
703 p_count_errored_requests => v_count_err_requests,
704 p_count_errored_work_items => v_count_err_wi,
705 p_setup_status => null,
706 p_test_status => null,
707 p_diagnostic_test_status => null,
708 p_count_running_requests => v_count_running_requests,
709 p_count_form_sessions => v_count_form_sessions,
710 p_count_ssf_sessions => v_count_ssf_sessions,
711 p_count_active_work_items => v_count_active_wi);
712 end loop;
713 END rollup_metrics;
714
715 --
716 -- Name
717 -- refresh_metrics
718 --
719 -- Purpose
720 -- computes and rolls up metrics related to business flows such as
721 -- - count of open system alerts
722 -- - count of errored concurrent requests
723 -- - count of errored work items
724 --
725 -- The resulting values will get populated into fnd_oam_bf_comp_info,
726 -- fnd_oam_bf_wit_info and fnd_oam_bf_rollup_info tables.
727 --
728 -- Input Arguments
729 --
730 -- Output Arguments
731 --
732 -- Notes:
733 --
734 --
735 PROCEDURE refresh_metrics
736 IS
737
738 BEGIN
739 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> Start compute_metrics');
740 compute_metrics;
741 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> End compute_metrics');
742 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> Start rollup_metrics');
743 rollup_metrics;
744 fnd_file.put_line(fnd_file.log, to_char(sysdate,'HH24:MI:SS')||'> End rollup_metrics');
745 END refresh_metrics;
746
747 --
748 -- Updates the monitored flag for the given flow
749 -- Updates fnd_oam_bf_cust if record exists for given flow key
750 -- Otherwise, copies entry from fnd_oam_bf to
751 -- fnd_oam_bf_cust and updates the monitored_flag in
752 -- fnd_oam_bf_cust.
753 --
754 --
755 PROCEDURE update_bf_monitored_flag (
756 p_flow_key varchar2,
757 p_new_flag varchar2)
758 IS
759 v_userid number;
760 v_cust_flag number := 0;
761 v_base_monitored_flag varchar2(1);
762 BEGIN
763 v_userid := get_user_id;
764 begin
765 select 1 into v_cust_flag from fnd_oam_bf_cust
766 where biz_flow_key = p_flow_key;
767 exception
768 when no_data_found then
769 v_cust_flag := 0;
770 end;
771
772 if (v_cust_flag = 0) then
773 -- select monitored_flag into v_base_monitored_flag
774 -- from fnd_oam_bf
775 -- where biz_flow_key = p_flow_key;
776 --
777 -- if (v_base_monitored_flag <> p_new_flag) then
778 -- -- copy over record to cust table
779 -- insert into fnd_oam_bf_cust(
780 -- biz_flow_key, monitored_flag, is_top_level,
781 -- created_by, creation_date, last_updated_by, last_update_date,
782 -- last_update_login)
783 -- select biz_flow_key, monitored_flag, is_top_level,
784 -- created_by, creation_date, last_updated_by, last_update_date,
785 -- last_update_login
786 -- from fnd_oam_bf
787 -- where biz_flow_key = p_flow_key;
788 -- insert into fnd_oam_bf_cust_tl(
789 -- biz_flow_key, language, flow_display_name, description,
790 -- created_by, creation_date, last_updated_by, last_update_date,
791 -- last_update_login, source_lang)
792 -- select biz_flow_key, language, flow_display_name, description,
793 -- created_by, creation_date, last_updated_by, last_update_date,
794 -- last_update_login, source_lang
795 -- from fnd_oam_bf_tl
796 -- where biz_flow_key = p_flow_key;
797 -- end if;
798 -- now update fnd_oam_bf
799 update fnd_oam_bf set
800 monitored_flag = p_new_flag,
801 last_update_date = sysdate,
802 last_updated_by = v_userid
803 where biz_flow_key = p_flow_key;
804 else
805 -- now update fnd_oam_bf_cust
806 update fnd_oam_bf_cust set
807 monitored_flag = p_new_flag,
808 last_update_date = sysdate,
809 last_updated_by = v_userid
810 where biz_flow_key = p_flow_key;
811 end if;
812
813 EXCEPTION
814 when others then
815 raise;
816 END update_bf_monitored_flag;
817
818 --
819 -- Updates the monitored flag for the given sub flow in context of the
820 -- given parent flow.
821 -- Updates fnd_oam_bf_assoc_cust if record exists for given parent
822 -- and child. Otherwise, copies entry from fnd_oam_bf_assoc to
823 -- fnd_oam_bf_assoc_cust and updates the monitored_flag in
824 -- fnd_oam_bf_assoc_cust.
825 --
826 --
827 PROCEDURE update_bf_monitored_flag (
828 p_parent_flow_key varchar2,
829 p_child_flow_key varchar2,
830 p_new_flag varchar2)
831 IS
832 v_userid number;
833 v_cust_flag number := 0;
834 v_base_monitored_flag varchar2(1);
835 BEGIN
836 v_userid := get_user_id;
837 begin
838 select 1 into v_cust_flag from fnd_oam_bf_assoc_cust
839 where biz_flow_parent_key = p_parent_flow_key
840 and biz_flow_child_key = p_child_flow_key;
841 exception
842 when no_data_found then
843 v_cust_flag := 0;
844 end;
845
846 if (v_cust_flag = 0) then
847 -- select monitored_flag into v_base_monitored_flag
848 -- from fnd_oam_bf_assoc
849 -- where biz_flow_parent_key = p_parent_flow_key
850 -- and biz_flow_child_key = p_child_flow_key;
851 --
852 -- if (v_base_monitored_flag <> p_new_flag) then
853 -- -- copy over record to cust table
854 -- insert into fnd_oam_bf_assoc_cust(
855 -- biz_flow_parent_key, biz_flow_child_key, monitored_flag,
859 -- created_by, creation_date, last_updated_by, last_update_date,
856 -- created_by, creation_date, last_updated_by, last_update_date,
857 -- last_update_login)
858 -- select biz_flow_parent_key, biz_flow_child_key, monitored_flag,
860 -- last_update_login
861 -- from fnd_oam_bf_assoc
862 -- where biz_flow_parent_key = p_parent_flow_key
863 -- and biz_flow_child_key = p_child_flow_key;
864 -- end if;
865 -- now update fnd_oam_bf_assoc
866 update fnd_oam_bf_assoc set
867 monitored_flag = p_new_flag,
868 last_update_date = sysdate,
869 last_updated_by = v_userid
870 where biz_flow_parent_key = p_parent_flow_key
871 and biz_flow_child_key = p_child_flow_key;
872 else
873 -- now update fnd_oam_bf_assoc_cust
874 update fnd_oam_bf_assoc_cust set
875 monitored_flag = p_new_flag,
876 last_update_date = sysdate,
877 last_updated_by = v_userid
878 where biz_flow_parent_key = p_parent_flow_key
879 and biz_flow_child_key = p_child_flow_key;
880 end if;
881 EXCEPTION
882 when others then
883 raise;
884 END update_bf_monitored_flag;
885
886
887 --
888 -- Updates the monitored flag for the given component in context of the
889 -- given parent flow.
890 -- Updates fnd_oam_bf_comp_cust if record exists for given parent
891 -- and child. Otherwise, copies entry from fnd_oam_bf_comp to
892 -- fnd_oam_bf_comp_cust and updates the monitored_flag in
893 -- fnd_oam_bf_comp_cust.
894 --
895 --
896 PROCEDURE update_comp_monitored_flag (
897 p_parent_flow_key varchar2,
898 p_component_type varchar2,
899 p_component_appl_id number,
900 p_component_id number,
901 p_new_flag varchar2)
902 IS
903 v_userid number;
904 v_cust_flag number := 0;
905 v_base_monitored_flag varchar2(1);
906 BEGIN
907 v_userid := get_user_id;
908 begin
909 select 1 into v_cust_flag from fnd_oam_bf_comp_cust
910 where biz_flow_key = p_parent_flow_key
911 and component_type = p_component_type
912 and component_appl_id = p_component_appl_id
913 and component_id = p_component_id;
914 exception
915 when no_data_found then
916 v_cust_flag := 0;
917 end;
918
919 if (v_cust_flag = 0) then
920 -- select monitored_flag into v_base_monitored_flag
921 -- from fnd_oam_bf_comp
922 -- where biz_flow_key = p_parent_flow_key
923 -- and component_type = p_component_type
924 -- and component_appl_id = p_component_appl_id
925 -- and component_id = p_component_id;
926 --
927 -- if (v_base_monitored_flag <> p_new_flag) then
928 -- -- copy over record to cust table
929 -- insert into fnd_oam_bf_comp_cust(
930 -- biz_flow_key, component_type, component_appl_id, component_id,
931 -- monitored_flag,
932 -- created_by, creation_date, last_updated_by, last_update_date,
933 -- last_update_login)
934 -- select biz_flow_key,component_type,component_appl_id, component_id,
935 -- monitored_flag,
936 -- created_by, creation_date, last_updated_by, last_update_date,
937 -- last_update_login
938 -- from fnd_oam_bf_comp
939 -- where biz_flow_key = p_parent_flow_key
940 -- and component_type = p_component_type
941 -- and component_appl_id = p_component_appl_id
942 -- and component_id = p_component_id;
943 -- end if;
944 -- now update regular table
945 update fnd_oam_bf_comp set
946 monitored_flag = p_new_flag,
947 last_update_date = sysdate,
948 last_updated_by = v_userid
949 where biz_flow_key = p_parent_flow_key
950 and component_type = p_component_type
951 and component_appl_id = p_component_appl_id
952 and component_id = p_component_id;
953 else
954 -- now update cust table
955 update fnd_oam_bf_comp_cust set
956 monitored_flag = p_new_flag,
957 last_update_date = sysdate,
958 last_updated_by = v_userid
959 where biz_flow_key = p_parent_flow_key
960 and component_type = p_component_type
961 and component_appl_id = p_component_appl_id
962 and component_id = p_component_id;
963 end if;
964
965
966 EXCEPTION
967 when others then
968 raise;
969 END update_comp_monitored_flag;
970
971 --
972 -- Updates the monitored flag for the given item type in context of the
973 -- given parent flow.
974 --
975 --
976 --
977 PROCEDURE update_wit_monitored_flag (
978 p_parent_flow_key varchar2,
979 p_item_type varchar2,
980 p_new_flag varchar2)
981 IS
982 v_userid number;
983 v_cust_flag number := 0;
984 v_base_monitored_flag varchar2(1);
985 BEGIN
986 v_userid := get_user_id;
987 begin
988 select 1 into v_cust_flag from fnd_oam_bf_wit_cust
989 where biz_flow_key = p_parent_flow_key
990 and item_type = p_item_type;
991 exception
992 when no_data_found then
993 v_cust_flag := 0;
994 end;
995
996 if (v_cust_flag = 0) then
997 -- select monitored_flag into v_base_monitored_flag
998 -- from fnd_oam_bf_wit
999 -- where biz_flow_key = p_parent_flow_key
1000 -- and item_type = p_item_type;
1001 --
1002 -- if (v_base_monitored_flag <> p_new_flag) then
1003 -- -- copy over record to cust table
1004 -- insert into fnd_oam_bf_wit_cust(
1005 -- biz_flow_key, item_type, monitored_flag,
1006 -- created_by, creation_date, last_updated_by, last_update_date,
1007 -- last_update_login)
1008 -- select biz_flow_key, item_type, monitored_flag,
1009 -- created_by, creation_date, last_updated_by, last_update_date,
1010 -- last_update_login
1011 -- from fnd_oam_bf_wit
1012 -- where biz_flow_key = p_parent_flow_key
1013 -- and item_type = p_item_type;
1014 -- end if;
1015 -- now update fnd_oam_bf_assoc
1016 update fnd_oam_bf_wit set
1017 monitored_flag = p_new_flag,
1018 last_update_date = sysdate,
1019 last_updated_by = v_userid
1020 where biz_flow_key = p_parent_flow_key
1021 and item_type = p_item_type;
1022 else
1023 -- now update fnd_oam_bf_assoc_cust
1024 update fnd_oam_bf_wit_cust set
1025 monitored_flag = p_new_flag,
1026 last_update_date = sysdate,
1027 last_updated_by = v_userid
1028 where biz_flow_key = p_parent_flow_key
1029 and item_type = p_item_type;
1030 end if;
1031
1032
1033 EXCEPTION
1034 when others then
1035 raise;
1036 END update_wit_monitored_flag;
1037
1038 end FND_OAM_BF_UTIL;