1 PACKAGE BODY fnd_oam_collection AS
2 /* $Header: AFOAMCLB.pls 120.7 2007/01/03 20:02:35 ravmohan noship $ */
3 -- Some Constants to define the various agents we will be pinging
4 AOLJ_DB_POOL constant number := 1;
5 PLSQL_AGNT constant number := 2;
6 SERVLET_AGNT constant number := 3;
7 JSP constant number := 4;
8 TCF constant number := 5;
9 JTF constant number := 6;
10 DISCOVERER constant number := 7;
11 PHP constant number := 8;
12 REPORT constant number := 9;
13 FORMS constant number := 10;
14 FWK constant number := 11;
15
16 AGENT_MIN constant number := PLSQL_AGNT;
17 AGENT_MAX constant number := PHP;
18
19 -- Some constants to define status values
20 STATUS_NORMAL constant number := 0;
21 STATUS_WARNING constant number := 1;
22 STATUS_ERROR constant number := 2;
23 STATUS_INACTIVE constant number := 3;
24 STATUS_UNKNOWN constant number := 4;
25
26 -- Some constants to define operator codes
27 OPER_E constant varchar2(1) := 'E';
28 OPER_G constant varchar2(1) := 'G';
29 OPER_L constant varchar2(1) := 'L';
30 OPER_I constant varchar2(1) := 'I';
31
32 -- internal constant for group id for Applications Systems Status Group
33 APP_SYS_STATUS constant number := 7;
34
35 -- internal type used for computation of rollup status
36 TYPE st_table IS TABLE OF number index by binary_integer;
37
38 -- Module name for this package
39 MODULE constant varchar2(200) := 'fnd.plsql.FND_OAM_COLLECTION';
40
41 PROCEDURE debug (p_txt varchar2)
42 IS
43
44 BEGIN
45 --dbms_output.put_line(p_txt);
46 null;
47 END debug;
48
49 --
50 -- Checks if currently we are in the context of a concurrent
51 -- request (i.e. FNDOAMCOL execution) or not.
52 --
53 FUNCTION is_request RETURN boolean
54 IS
55 v_conc_req_id fnd_concurrent_requests.request_id%TYPE;
56 v_retu boolean := false;
57 BEGIN
58 -- check if its concurrent request
59 select fnd_global.conc_request_id into v_conc_req_id from dual;
60 if v_conc_req_id > 0 then
61 v_retu := true;
62 end if;
63 return v_retu;
64 END is_request;
65
66 --
67 -- Gets the current user id
68 --
69 FUNCTION get_user_id RETURN number
70 IS
71 v_userid number;
72 v_conc_req_id number;
73 BEGIN
74 -- check if its concurrent request
75 select fnd_global.conc_request_id into v_conc_req_id from dual;
76 if v_conc_req_id > 0 then
77 select fcr.requested_by into v_userid
78 from fnd_concurrent_requests fcr
79 where fcr.request_id = v_conc_req_id;
80 else
81 select fnd_global.user_id into v_userid from dual;
82 if (v_userid < 0 or v_userid is null) then
83 v_userid := 0; -- default
84 end if;
85 end if;
86
87 return v_userid;
88 EXCEPTION
89 when others then
90 v_userid := 0;
91 return v_userid;
92 END get_user_id;
93
94 --
95 -- Checks to see if the value for the given metric needs to be
96 -- collected
97 --
98 -- Note:
99 -- For use for Dashbaord Collection Program only.
100 --
101 FUNCTION is_collection_enabled (p_metric_short_name varchar2) RETURN boolean
102 IS
103 v_is_supported fnd_oam_metval.is_supported%TYPE;
104 v_collection_enabled_flag fnd_oam_metval.collection_enabled_flag%TYPE;
105 v_retu boolean := TRUE;
106 BEGIN
107 select nvl(is_supported,'Y'), nvl(collection_enabled_flag,'Y')
108 into v_is_supported, v_collection_enabled_flag
109 from fnd_oam_metval
110 where metric_short_name = p_metric_short_name
111 and rownum = 1;
112
113 if (v_is_supported = 'N' or v_collection_enabled_flag = 'N') then
114 v_retu := FALSE;
115 end if;
116
117 --if (v_retu) then
118 -- dbms_output.put_line('COLLECTION_ENABLED: ' || p_metric_short_name || ': '|| 'TRUE');
119 --else
120 -- dbms_output.put_line('COLLECTION_ENABLED: ' || p_metric_short_name || ': '|| 'FALSE');
121 --end if;
122
123 return v_retu;
124 EXCEPTION
125 when no_data_found then
126 --dbms_output.put_line('COLLECTION_ENABLED: ' || p_metric_short_name || ': ' || 'FALSE');
127 return FALSE;
128 END is_collection_enabled;
129
130 --
131 -- Checks to see if the value for the given service instance needs to
132 -- be collected
133 --
134 FUNCTION is_collection_enabled (
135 p_application_id number,
136 p_concurrent_queue_name varchar2) RETURN boolean
137 IS
138 v_collection_enabled_flag fnd_oam_svci_info.collection_enabled_flag%TYPE;
139 v_retu boolean := TRUE;
140 BEGIN
141 select nvl(collection_enabled_flag, 'Y')
142 into v_collection_enabled_flag
143 from fnd_oam_svci_info
144 where application_id = p_application_id
145 and concurrent_queue_name = p_concurrent_queue_name;
146 if (v_collection_enabled_flag = 'N') then
147 v_retu := FALSE;
148 end if;
149
150 --if (v_retu) then
151 -- dbms_output.put_line('COLLECTION_ENABLED: ' || p_concurrent_queue_name || ': '|| 'TRUE');
152 --else
153 -- dbms_output.put_line('COLLECTION_ENABLED: ' || p_concurrent_queue_name || ': '|| 'FALSE');
154 --end if;
155
156 return v_retu;
157 EXCEPTION
158 when no_data_found then
159 --dbms_output.put_line('COLLECTION_ENABLED: ' || p_concurrent_queue_name || ': ' || 'TRUE');
160 return TRUE;
161 END is_collection_enabled;
162
163 --
164 -- Returns the operator symbol based on the threshold operator code
165 --
166 FUNCTION get_operator_symbol(p_threshold_operator varchar2) RETURN varchar2
167 IS
168 v_retu varchar2(10) := '=';
169 BEGIN
170 if p_threshold_operator = OPER_E then
171 v_retu := '=';
172 elsif p_threshold_operator = OPER_G then
173 v_retu := '>';
174 elsif p_threshold_operator = OPER_L then
175 v_retu := '<';
176 elsif p_threshold_operator = OPER_I then
177 v_retu := 'IN';
178 end if;
179
180 return v_retu;
181 END get_operator_symbol;
182
183 --
184 -- Checks the metric value against specified threshold to see if
185 -- an alert needs to be raised for the given metric
186 --
187 FUNCTION shall_raise_alert(p_metric_short_name varchar2) RETURN boolean
188 IS
189 v_retu boolean := FALSE;
190 l_metric_type fnd_oam_metval.metric_type%TYPE;
191 l_threshold_operator fnd_oam_metval.threshold_operator%TYPE;
192 l_threshold_value fnd_oam_metval.threshold_value%TYPE;
193 l_operator_symbol varchar2(10);
194 BEGIN
195 --if (is_alert_enabled(p_metric_short_name)) then
196 select metric_type, threshold_operator, threshold_value
197 into l_metric_type, l_threshold_operator, l_threshold_value
198 from fnd_oam_metval
199 where metric_short_name = p_metric_short_name
200 and rownum = 1;
201
202 if (l_threshold_operator is not null and
203 l_threshold_value is not null) then
204 l_operator_symbol := get_operator_symbol(l_threshold_operator);
205
206 declare
207 v_raise_alert number := 0;
208 v_check_sql varchar2(1024);
209 v_value_column varchar2(50);
210 begin
211 --determine the column our threshold value should measure against
212 if (l_metric_type = 'I') then -- Numeric metric
213 v_value_column := 'metric_value';
214 elsif (l_metric_type = 'S') then -- Status metric
215 v_value_column := 'status_code';
216 end if;
217
218 -- construct threshold check sql and execute
219 -- bug #4670957 - ilawler
220 -- converted literals to use binds where possible
221 begin
222 if (l_threshold_operator = OPER_I) then
223 --can't bind a list, use string concat
224 v_check_sql := 'select 1 from fnd_oam_metval where metric_short_name = :1 and '||
225 v_value_column||' '||l_operator_symbol ||' '||'('||l_threshold_value||')';
226 execute immediate v_check_sql
227 into v_raise_alert
228 using p_metric_short_name;
229 else
230 --use bind for =,<,> since they're the majority case.
231 v_check_sql := 'select 1 from fnd_oam_metval where metric_short_name = :1 and '||
232 v_value_column||' '||l_operator_symbol ||' '||':2';
233 execute immediate v_check_sql
234 into v_raise_alert
235 using p_metric_short_name, l_threshold_value;
236 end if;
237 exception
238 when no_data_found then
239 v_raise_alert := 0;
240 end;
241
242 -- convert the number into a boolean
243 if v_raise_alert = 1 then
244 v_retu := TRUE;
245 end if;
246 end;
247 end if;
248 --end if;
249
250 --if (v_retu) then
251 --dbms_output.put_line('RAISE_ALERT: ' || p_metric_short_name || ': '||'TYPE: '||l_metric_type||' OP: '||l_operator_symbol||' '||'THRSHVAL: '||l_threshold_value|| ' :TRUE');
252 --else
253 --dbms_output.put_line('RAISE_ALERT: ' || p_metric_short_name || ': '||'TYPE: '||l_metric_type||' OP:'||l_operator_symbol||' '||'THRSHVAL: '||l_threshold_value|| ' :FALSE');
254 --end if;
255
256 return v_retu;
257 END shall_raise_alert;
258
259 --
260 -- Checks the status code for the given service instance against threshold
261 -- value to see if an alert needs to be raised.
262 --
263 FUNCTION shall_raise_alert(p_application_id number,
264 p_concurrent_queue_name varchar2)
265 RETURN boolean
266 IS
267 v_retu boolean := FALSE;
268 l_threshold_value fnd_oam_svci_info.threshold_value%TYPE;
269 BEGIN
270 --if (is_alert_enabled(p_application_id, p_concurrent_queue_name)) then
271 select threshold_value
272 into l_threshold_value
273 from fnd_oam_svci_info
274 where application_id = p_application_id
275 and concurrent_queue_name = p_concurrent_queue_name;
276
277 if (l_threshold_value is not null) then
278 declare
279 v_raise_alert number := 0;
280 v_check_sql varchar2(1024);
281 begin
282 -- construct threshold check sql and execute
283 -- bug #4670957 - ilawler
284 -- converted literals to use binds where possible
285 v_check_sql := 'select 1 from fnd_oam_app_sys_status where ' ||
286 'application_id = :1 and concurrent_queue_name = :2 and ' ||
287 'status_code IN ' || '('||l_threshold_value||')';
288 --dbms_output.put_line('CHK_SQL: ' || v_check_sql);
289
290 begin
291 execute immediate v_check_sql
292 into v_raise_alert
293 using p_application_id, p_concurrent_queue_name;
294 exception
295 when no_data_found then
296 v_raise_alert := 0;
297 end;
298
299 if v_raise_alert = 1 then
300 v_retu := TRUE;
301 end if;
302 end;
303 end if;
304 --end if;
305
306 --if (v_retu) then
307 --dbms_output.put_line('RAISE_ALERT: ' ||p_concurrent_queue_name|| ': '||'THRSHVAL: '||l_threshold_value|| ' :TRUE');
308 --else
309 --dbms_output.put_line('RAISE_ALERT: ' ||p_concurrent_queue_name|| ': '||'THRSHVAL: '||l_threshold_value|| ' :FALSE');
310 --end if;
311
312 return v_retu;
313 END shall_raise_alert;
314
315 --
316 --
317 -- Name
318 -- construct_url
319 --
320 -- Purpose
321 -- Constructs a url to ping for the given agent. This is an internal
322 -- procedure only.
323 --
324 -- Input Arguments
325 -- 1) agent: type of agent for which the url is required. Valid values
326 -- are AOLJ_DB_POOL, WEB_AGNT, SERVLET_AGNT, JSP, TCF, JTF, DISCOVERER,
327 -- PHP, REPORT, FWK.
328 --
329 -- Output Arguments
330 --
331 -- Returns
332 -- The URL for the given agent. For example for PHP (personal home page)
333 -- 'http://<host>:<port>/OA_HTML/US/ICXINDEX.htm' is returned. If the URL
334 -- cannot be constructed, null is returned.
335 --
336 -- Notes:
337 --
338 --
339 FUNCTION construct_url(agent in number) RETURN varchar2
340 IS
341 v_url varchar2(1000) := null;
342 v_err varchar2(2000);
343 valid_agent boolean := true;
344 BEGIN
345 if agent = AOLJ_DB_POOL then
346 v_url := fnd_web_config.jsp_agent;
347 if v_url is not null then
348 v_url := v_url || 'jsp/fnd/AoljDbcPoolStatus.jsp';
349 end if;
350 elsif agent = PLSQL_AGNT then
351 v_url := fnd_web_config.plsql_agent;
352 if v_url is not null then
353 v_url := v_url || 'fnd_web.ping';
354 end if;
355 elsif agent = SERVLET_AGNT or agent = TCF then
356 -- we'll first try to retrieve value of 'APPS_SERVLET_AGENT' profile
357 -- option. If this value is not available then we will use the base
358 -- web server url to construct the url to ping for servlet agent.
359 begin
360 select pov.profile_option_value
361 into v_url
362 from fnd_profile_options po,
363 fnd_profile_option_values pov
364 where po.profile_option_name = 'APPS_SERVLET_AGENT'
365 and pov.application_id = po.application_id
366 and pov.profile_option_id = po.profile_option_id
367 and pov.level_id = 10001;
368 exception
369 when others then
370 null;
371 end;
372 if v_url is null then
373 v_url := fnd_web_config.web_server;
374 if v_url is not null then
375 v_url := v_url || 'oa_servlets';
376 end if;
377 end if;
378 if v_url is not null then
379 v_url := fnd_web_config.trail_slash(v_url);
380 if agent = SERVLET_AGNT then
381 v_url := v_url || 'oracle.apps.fnd.test.HelloWorldServlet';
382 elsif agent = TCF then
383 v_url := v_url || 'oracle.apps.fnd.tcf.SocketServer';
384 end if;
385 end if;
386 elsif agent = JSP then
387 v_url := fnd_web_config.jsp_agent;
388 if v_url is not null then
389 v_url := v_url || 'jsp/fnd/fndping.jsp?dbc=' || fnd_web_config.database_id;
390 end if;
391 elsif agent = JTF then
392 v_url := fnd_web_config.jsp_agent;
393 if v_url is not null then
394 v_url := v_url || 'jtflogin.jsp';
395 end if;
396 elsif agent = DISCOVERER then
397 --v_url := fnd_web_config.web_server;
398 --if v_url is not null then
399 -- v_url := v_url || 'servlets/discoservlet';
400 --end if;
401 v_url := fnd_profile.value('ICX_DISCOVERER_VIEWER_LAUNCHER');
402 elsif agent = PHP then
403 v_url := fnd_web_config.jsp_agent;
404 if v_url is not null then
405 v_url := v_url || 'US/ICXINDEX.htm';
406 end if;
407 elsif agent = REPORT then
408 -- retrieve ICX_REPORT_LANCHER and ICX_REPORT_SERVER profile option
409 -- values to construct the reports server url.
410 declare
411 v_launcher varchar(1000);
412 v_server varchar(100);
413 begin
414 select pov.profile_option_value
415 into v_launcher
416 from fnd_profile_options po,
417 fnd_profile_option_values pov
418 where po.profile_option_name = 'ICX_REPORT_LAUNCHER'
419 and pov.application_id = po.application_id
420 and pov.profile_option_id = po.profile_option_id
421 and pov.level_id = 10001;
422
423 select pov.profile_option_value
424 into v_server
425 from fnd_profile_options po,
426 fnd_profile_option_values pov
427 where po.profile_option_name = 'ICX_REPORT_SERVER'
428 and pov.application_id = po.application_id
429 and pov.profile_option_id = po.profile_option_id
430 and pov.level_id = 10001;
431
432 if v_launcher is not null and v_server is not null then
433 v_url := fnd_web_config.trail_slash(v_launcher);
434 v_url := v_url || 'showenv?server=' || v_server;
435 end if;
436 exception
437 when others then
438 null;
439 end;
440 elsif agent = FWK then
441 v_url := fnd_web_config.web_server;
442 elsif agent = FORMS then
443 -- retrieve ICX_FORMS_LAUNCHER profile option
444 -- values to construct the reports server url.
445 declare
446 v_launcher varchar(1000);
447 begin
448 select pov.profile_option_value
449 into v_launcher
450 from fnd_profile_options po,
451 fnd_profile_option_values pov
452 where po.profile_option_name = 'ICX_FORMS_LAUNCHER'
453 and pov.application_id = po.application_id
454 and pov.profile_option_id = po.profile_option_id
455 and pov.level_id = 10001;
456
457 if v_launcher is not null then
458 v_url := v_launcher;
459 end if;
460 exception
461 when others then
462 null;
463 end;
464 else
465 valid_agent := false;
466 end if;
467
468 if (v_url is null) and valid_agent then
469 fnd_file.put_line(fnd_file.log, fnd_message.get);
470 end if;
471 return v_url;
472 END construct_url;
473
474
475 --
476 -- Name
477 -- get_agent_status
478 --
479 -- Purpose
480 -- Returns the status for a given agent. This is for internal purpose only.
481 --
482 -- Input Arguments
483 -- 1) agent: type of agent for which the url is required. Valid values
484 -- are AOLJ_DB_POOL, WEB_AGNT, SERVLET_AGNT, JSP, TCF, JTF, DISCOVERER,
485 -- PHP, REPORT, FWK.
486 --
487 -- Output Arguments
488 --
489 -- Returns
490 -- The status for the given agent. 0 - normal, 1 - warning, 2 - error,
491 -- 3 - unknown
492 --
493 -- Notes:
494 --
495 --
496 FUNCTION get_agent_status (agent in number) RETURN number
497 IS
498 v_status number := STATUS_UNKNOWN;
499 v_url varchar2(2000) := null;
500 v_response varchar2(4000) := null;
501 BEGIN
502 -- compose a pingable url
503 v_url := construct_url(agent);
504 --dbms_output.put_line('URL: ' || v_url);
505
506 if v_url is null then
507 return STATUS_WARNING; -- status warning if unable to construct url,
508 -- probably some profile option is not set
509 end if;
510
511 -- Check if SSL, if so make it unavailable for now (bug 2905278)
512 if (instr(v_url, 'https:', 1, 1) = 1) then
513 return STATUS_UNKNOWN;
514 end if;
515 -- End (bug 2905278)
516
517
518 -- now make a network call
519 v_response := utl_http.request(v_url);
520
521 -- now parse the response to determine status based on agent.
522 if agent = PLSQL_AGNT then
523 declare
524 v_index number := -1;
525 begin
526 v_index := instr(v_response, 'FND_WEB.PING', 1, 2);
527 if v_index <= 0 then
528 v_status := STATUS_ERROR;
529 else
530 v_status := STATUS_NORMAL;
531 end if;
532 end;
533 elsif agent = SERVLET_AGNT then
534 declare
535 v_index number := -1;
536 begin
537 v_index := instr(v_response, 'HelloWorldServlet', 1, 1);
538 if v_index <= 0 then
539 v_status := STATUS_ERROR;
540 else
541 v_status := STATUS_NORMAL;
542 end if;
543 end;
544 elsif agent = JSP then
545 declare
546 v_index number := -1;
547 begin
548 v_index := instr(v_response, 'AOL_VERSION', 1, 1);
549 if v_index <= 0 then
550 v_status := STATUS_ERROR;
551 else
552 v_status := STATUS_NORMAL;
553 end if;
554 end;
555 elsif agent = REPORT then
556 declare
557 v_index number := -1;
558 begin
559 v_index := instr(v_response, 'SERVER_NAME', 1, 1);
560 if v_index <= 0 then
561 v_status := STATUS_ERROR;
562 else
563 v_status := STATUS_NORMAL;
564 end if;
565 end;
566 elsif agent = JTF or agent = DISCOVERER
567 or agent = PHP or agent = TCF or agent = FORMS then
568 declare
569 v_index number := -1;
570 begin
571 v_index := instr(v_response, 'Bad Request', 1, 1);
572 if v_index <= 0 then
573 v_index := instr(v_response, '404 Not Found', 1, 1);
574 end if;
575 if v_index <= 0 then
576 v_index := instr(v_response, '500 Internal Server Error', 1, 1);
577 end if;
578 if v_index <= 0 then
579 v_status := STATUS_NORMAL;
580 else
581 v_status := STATUS_ERROR;
582 end if;
583 end;
584 end if;
585
586 return v_status;
587 EXCEPTION
588 when utl_http.init_failed then
589 --dbms_output.put_line('INIT_FAILED');
590 return STATUS_UNKNOWN;
591 when utl_http.request_failed then
592 --dbms_output.put_line('REQUEST_FAILED');
593 return STATUS_ERROR;
594 when others then
595 return STATUS_UNKNOWN;
596 END get_agent_status;
597
598 --
599 -- Name
600 -- insert_app_sys_status_internal
601 -- Purpose
602 -- This procedure is for internal use of this package only!
603 -- This procedure will insert a row into fnd_oam_app_sys_status
604 --
605 -- Input Arguments
606 -- p_metric_short_name varchar2
607 -- p_application_id number
608 -- p_concurrent_queue_short_name varchar2
609 -- p_name varchar2
610 -- p_type varchar2
611 -- p_status_code number
612 -- p_node_name varchar2
613 --
614 -- Output Arguments
615 --
616 -- Input/Output Arguments
617 --
618 -- Notes:
619 -- This is an internal convenience method only for this package and should
620 -- not be exposed.
621 --
622 PROCEDURE insert_app_sys_status_internal (
623 p_metric_short_name in varchar2,
624 p_application_id number,
625 p_concurrent_queue_short_name varchar2,
626 p_name varchar2,
627 p_type varchar2,
628 p_status_code in number,
629 p_node_name in varchar2)
630 IS
631 v_userid number;
632 BEGIN
633 v_userid := get_user_id;
634
635 insert into fnd_oam_app_sys_status (metric_short_name, application_id,
636 concurrent_queue_name, name, type, status_code, node_name, last_updated_by,
637 last_update_date, last_update_login)
638 values
639 (p_metric_short_name,
640 p_application_id,
641 p_concurrent_queue_short_name,
642 p_name,
643 p_type,
644 p_status_code,
645 p_node_name,
646 v_userid, sysdate, 0);
647 END insert_app_sys_status_internal;
648
649 --
650 -- Name
651 -- update_metric_internal
652 -- Purpose
653 -- This procedure is for internal use of this package only!
654 -- This procedure will update a row in fnd_oam_metval for the given
655 -- metric name.
656 --
657 -- Input Arguments
658 -- p_metric_name varchar2
659 -- p_value varchar2
660 -- p_status_code number : if < 0 then status_code is not updated.
661 --
662 -- Output Arguments
663 --
664 -- Input/Output Arguments
665 --
666 -- Notes:
667 -- This is an internal convenience method only for this package and should
668 -- not be exposed.
669 --
670 PROCEDURE update_metric_internal (
671 p_metric_name in varchar2,
672 p_value in varchar2,
673 p_status_code in number)
674 IS
675 v_userid number;
676 BEGIN
677 v_userid := get_user_id;
678 if p_status_code >= 0 then
679 update fnd_oam_metval
680 set metric_value = p_value,
681 status_code = p_status_code,
682 last_collected_date = sysdate,
683 last_updated_by = v_userid,
684 last_update_date = sysdate,
685 last_update_login = 0
686 where metric_short_name = p_metric_name;
687 else
688 update fnd_oam_metval
689 set metric_value = p_value,
690 last_collected_date = sysdate,
691 last_updated_by = v_userid,
692 last_update_date = sysdate,
693 last_update_login = 0
694 where metric_short_name = p_metric_name;
695 end if;
696 END update_metric_internal;
697
698 --
699 -- Name
700 -- refresh_status_for_service
701 -- Purpose
702 -- This procedure is for internal use of this package only!
703 -- For a given server type, and node name this procedure will insert
704 -- the status information for all the service instances for that
705 -- particular server type running on the given node into
706 -- fnd_oam_app_sys_status
707 --
708 -- Input Arguments
709 -- p_server_type varchar2 - 'C' - Concurrent Processing,
710 -- - 'F' - Forms
711 -- - 'W' - Web
712 -- p_node_name varchar2
713 --
714 --
715 -- Output Arguments
716 --
717 -- Input/Output Arguments
718 --
719 --
720 -- Notes:
721 -- This is an internal convenience proc only for this package and should
722 -- not be exposed.
723 --
724 PROCEDURE refresh_status_for_service(
725 p_server_type in varchar2,
726 p_node_name in varchar2)
727 IS
728 cursor svc_c(v_server_type varchar2, v_node_name varchar2) is
729 select
730 fcq.application_id application_id,
731 fcq.concurrent_queue_id concurrent_queue_id,
732 fcq.concurrent_queue_name concurrent_queue_name
733 from fnd_cp_services fcs, fnd_concurrent_queues fcq
734 where
735 fcs.service_id = to_number(fcq.manager_type)
736 and fcs.server_type in (v_server_type, 'E')
737 and upper(fcq.target_node) = upper(v_node_name)
738 and upper(fcs.enabled) = 'Y'
739 and upper(fcq.enabled_flag) = 'Y'
740 order by fcs.oam_display_order asc;
741
742 v_count number := 0;
743 BEGIN
744 for svc_inst in svc_c(p_server_type, p_node_name) loop
745 v_count := v_count + 1;
746 declare
747 v_target number := -1;
748 v_actual number := -1;
749 v_status_code number;
750 v_desc varchar2(1000);
751 v_err_code number;
752 v_err_msg varchar2(1000);
753 begin
754 -- check if collection is enabled before calling API to get status
755 if (is_collection_enabled(
756 svc_inst.application_id, svc_inst.concurrent_queue_name)) then
757 fnd_oam.get_svc_inst_status(svc_inst.application_id,
758 svc_inst.concurrent_queue_id, v_target, v_actual, v_status_code,
759 v_desc, v_err_code, v_err_msg);
760
761 if v_err_code > 0 then -- error
762 -- Log some information for now.
763 fnd_file.put_line(fnd_file.log, 'Error in refresh_status_for_service:');
764 fnd_file.put_line(fnd_file.log, 'SERVER_TYPE: ' || p_server_type);
765 fnd_file.put_line(fnd_file.log, 'NODE NAME: ' || p_node_name);
766 fnd_file.put_line(fnd_file.log, 'APP ID: ' ||
767 svc_inst.application_id);
768 fnd_file.put_line(fnd_file.log, 'CONCURRENT QUEUE ID: ' ||
769 svc_inst.concurrent_queue_id);
770 fnd_file.put_line(fnd_file.log, 'CONCURRENT QUEUE NAME: ' ||
771 svc_inst.concurrent_queue_name);
772 fnd_file.put_line(fnd_file.log, 'v_target: ' || v_target);
773 fnd_file.put_line(fnd_file.log, 'v_actual: ' || v_actual);
774 fnd_file.put_line(fnd_file.log, 'v_status_code: ' || v_status_code);
775 fnd_file.put_line(fnd_file.log, 'v_desc: ' || v_desc);
776 fnd_file.put_line(fnd_file.log, 'v_err_code: ' || v_err_code);
777 fnd_file.put_line(fnd_file.log, 'v_err_msg: ' || v_err_msg);
778 v_status_code := STATUS_UNKNOWN;
779 end if;
780 --dbms_output.put_line('STATUS CODE: ' || v_status_code);
781
782 else
783 -- upload status null since collection not enabled.
784 v_status_code := -1;
785 end if;
786
787 -- now insert into fnd_oam_mets:
788 insert_app_sys_status_internal(
789 p_server_type || '_' || to_char(v_count) ||
790 '_' || p_node_name,
791 svc_inst.application_id, svc_inst.concurrent_queue_name, null,
792 p_server_type,v_status_code, p_node_name);
793
794 end;
795 end loop;
796 EXCEPTION
797 when others then
798 raise;
799 END refresh_status_for_service;
800
801 --
802 -- Internal API to register status in a table in order to compute
803 -- rollup.
804 --
805 PROCEDURE register_status_in_table(
806 p_satus_code number,
807 p_info_table IN OUT NOCOPY st_table)
808 IS
809
810 BEGIN
811 if p_satus_code = STATUS_ERROR then
812 p_info_table(1) := 1;
813 elsif p_satus_code = STATUS_WARNING then
814 p_info_table(2) := 1;
815 elsif p_satus_code = STATUS_UNKNOWN then
816 p_info_table(3) := 1;
817 elsif p_satus_code = STATUS_NORMAL then
818 p_info_table(4) := 1;
819 elsif p_satus_code = STATUS_INACTIVE then
820 p_info_table(5) := 1;
821 end if;
822 END register_status_in_table;
823
824 --
825 -- Name
826 -- compute_rollup_status
827 -- Purpose
828 -- This function is for internal use of this package only!
829 -- Computes the the rollup status for a set of metrics given some
830 -- metadata information about the individual component metrics
831 -- that comprise the rollup.
832 --
833 -- Input Arguments
834 -- p_comp_info - st_table Table of numbers of with five entries as follows:
835 -- at index 1: if at least one component has STATUS_ERROR then 1
836 -- otherwise 0.
837 -- at index 2: if at least one component has STATUS_WARNING then 1
838 -- otherwise 0.
839 -- at index 3: if at least one component has STATUS_UNKNOWN then 1
840 -- otherwise 0.
841 -- at index 4: if at least one component has STATUS_NORMAL then 1
842 -- otherwise 0.
843 -- at index 5: if at least one component has STATUS_INACTIVE then
844 -- 1 otherwise 0.
845 --
846 -- Output Arguments
847 --
848 -- Input/Output Arguments
849 --
850 -- Returns
851 -- one of the following status codes:
852 -- STATUS_ERROR, STATUS_WARNING, STATUS_UNKNOWN, STATUS_NORMAL,
853 -- STATUS_INACTIVE
854 --
855 -- Notes:
856 -- This is an internal convenience method only for this package and should
857 -- not be exposed.
858 --
859 FUNCTION compute_rollup_status(
860 p_comp_info st_table) RETURN number
861 IS
862 v_status number := STATUS_NORMAL;
863 BEGIN
864 if p_comp_info.exists(1) and p_comp_info(1) = 1 then
865 v_status := STATUS_ERROR;
866 elsif p_comp_info.exists(2) and p_comp_info(2) = 1 then
867 v_status := STATUS_WARNING;
868 elsif p_comp_info.exists(3) and p_comp_info(3) = 1 then
869 v_status := STATUS_WARNING;
870 elsif p_comp_info.exists(4) and p_comp_info(4) = 1 then
871 v_status := STATUS_NORMAL;
872 elsif p_comp_info.exists(5) and p_comp_info(5) = 1 then
873 v_status := STATUS_INACTIVE;
874 end if;
875
876 return v_status;
877 END compute_rollup_status;
878
879 --
880 -- Name
881 -- compute_overall_server_status
882 -- Purpose
883 -- This procedure is for internal use of this package only!
884 -- Given the server name this procedure will compute and insert the status
885 -- for that server into fnd_oam_mets, based on the status of the server
886 -- on individual nodes.
887 --
888 -- Input Arguments
889 -- p_server varchar2 -- metric name for the server e.g. 'CP_SERVER'
890 -- p_server_type varchar2 -- server type eg. 'C','A','W','D' or 'F'
891 --
892 -- Output Arguments
893 --
894 -- Input/Output Arguments
895 --
896 --
897 -- Notes:
898 -- This is an internal convenience method only for this package and should
899 -- not be exposed.
900 --
901 PROCEDURE compute_overall_server_status(
902 p_server in varchar2,
903 p_server_type in varchar2)
904 IS
905
906 BEGIN
907 declare
908 cursor server_st is
909 select status_code from fnd_oam_app_sys_status
910 where metric_short_name like p_server || '%';
911 overall_st number := STATUS_NORMAL;
912 found_up boolean := false;
913 found_warning boolean := false;
914 server_count number := 0;
915 info_table st_table;
916 begin
917 --for server in server_st loop
918 -- if server.status_code = STATUS_NORMAL then
919 -- found_up := true;
920 -- elsif server.status_code = STATUS_WARNING then
921 -- found_warning := true;
922 -- overall_st := STATUS_WARNING;
923 -- else
924 -- overall_st := STATUS_WARNING;
925 -- end if;
926 -- server_count := server_count + 1;
927 --end loop;
928 --if server_count > 0 and found_up = false and found_warning = false then
929 -- overall_st := STATUS_ERROR;
930 --end if;
931
932 for server in server_st loop
933 register_status_in_table(server.status_code, info_table);
934 --if server.status_code = STATUS_ERROR then
935 -- info_table(1) := 1;
936 --elsif server.status_code = STATUS_WARNING then
937 -- info_table(2) := 1;
938 --elsif server.status_code = STATUS_UNKNOWN then
939 -- info_table(3) := 1;
940 --elsif server.status_code = STATUS_NORMAL then
941 -- info_table(4) := 1;
942 --elsif server.status_code = STATUS_INACTIVE then
943 -- info_table(5) := 1;
944 --end if;
945 end loop;
946
947 overall_st := compute_rollup_status(info_table);
948
949 -- now insert into fnd_oam_mets
950 insert_app_sys_status_internal(
951 p_server || '_OVERALL',
952 null, null, null, p_server_type, overall_st, null);
953
954 end;
955 END compute_overall_server_status;
956
957 --
958 -- Name
959 -- refresh_status_for_tier
960 -- Purpose
961 -- This procedure is for internal use of this package only!
962 -- For a given server type, and node name this procedure will insert
963 -- the status information for all the service instances for that
964 -- particular server type running on the given node into
965 -- fnd_oam_app_sys_status
966 --
967 -- Input Arguments
968 -- p_server_type varchar2 - 'C' - Concurrent Processing,
969 -- - 'F' - Forms
970 -- - 'W' - Web
971 -- - 'A' - Admin
972 -- p_node_name varchar2
973 --
974 --
975 -- Output Arguments
976 --
977 -- Input/Output Arguments
978 --
979 --
980 -- Notes:
981 -- This is an internal convenience proc only for this package and should
982 -- not be exposed.
983 --
984 PROCEDURE refresh_status_for_tier(
985 p_server_type in varchar2,
986 p_node_name in varchar2)
987 IS
988 v_server_type_prep varchar2(30);
989 BEGIN
990 -- determine the server type
991 if p_server_type = 'C' then
992 v_server_type_prep := 'CP_SERVER_';
993 elsif p_server_type = 'W' then
994 v_server_type_prep := 'WEB_SERVER_';
995 elsif p_server_type = 'F' then
996 v_server_type_prep := 'FORMS_SERVER_';
997 elsif p_server_type = 'A' then
998 v_server_type_prep := 'ADMIN_SERVER_';
999 else
1000 v_server_type_prep := 'UNKNOWN_SERVER_';
1001 end if;
1002
1003 refresh_status_for_service(p_server_type, p_node_name);
1004
1005 -- Now Compute Overall CP Server status on this node
1006 declare
1007 v_tier_overall_status number := STATUS_NORMAL; -- start off by up
1008 v_found_up_instance boolean := false;
1009 v_found_inactive_instance boolean := false;
1010 v_count number := 0;
1011
1012 cursor status_c is
1013 select status_code from fnd_oam_app_sys_status where
1014 upper(node_name) = upper(p_node_name) and (
1015 metric_short_name like p_server_type || '_%');
1016 info_table st_table;
1017 begin
1018 for st in status_c loop
1019 register_status_in_table(st.status_code, info_table);
1020 --if st.status_code = STATUS_ERROR then
1021 -- info_table(1) := 1;
1022 --elsif st.status_code = STATUS_WARNING then
1023 -- info_table(2) := 1;
1024 --elsif st.status_code = STATUS_UNKNOWN then
1025 -- info_table(3) := 1;
1026 --elsif st.status_code = STATUS_NORMAL then
1027 -- info_table(4) := 1;
1028 --elsif st.status_code = STATUS_INACTIVE then
1029 -- info_table(5) := 1;
1030 --end if;
1031 end loop;
1032
1033 -- also factor in the status of the current host while
1034 -- computing the status for this tier on this host.
1035 declare
1036 v_host_status number;
1037 begin
1038 select status_code into v_host_status
1039 from fnd_oam_app_sys_status
1040 where upper(node_name) = upper(p_node_name)
1041 and metric_short_name like 'HOST_%'
1042 and rownum = 1; -- we expect only one row per host
1043 register_status_in_table(v_host_status, info_table);
1044 exception
1045 when others then
1046 null;
1047 end;
1048
1049 -- compute the rollup for this tier on this node and update
1050 -- into the table.
1051 v_tier_overall_status := compute_rollup_status(info_table);
1052
1053 insert_app_sys_status_internal(
1054 v_server_type_prep || p_node_name,
1055 null, null, null, p_server_type, v_tier_overall_status, p_node_name);
1056 end;
1057 END refresh_status_for_tier;
1058
1059
1060 --
1061 -- Name
1062 -- refresh_app_sys_status
1063 --
1064 -- Purpose
1065 -- Derives the status of the following applications servers using URL
1066 -- pings of the corresponding processes that belong to the server. The status
1067 -- and host information for each of the processes as well as servers are
1068 -- updated in the FND_OAM_APP_SYS_STATUS table
1069 -- 1) Admin - Currently no processes are defined for this server
1070 -- 2) Web - Consists of Apache Web Listener, Apache Jserv
1071 -- 3) Forms - Consists of the forms launcher
1072 -- 4) CP - Consists of the Internal Concurrent Manager, Reports
1073 -- 5) Data - Consists of the database instances as defined in gv$instance.
1074 --
1075 -- Input Arguments
1076 --
1077 -- Output Arguments
1078 --
1079 -- Notes:
1080 --
1081 --
1082 PROCEDURE refresh_app_sys_status
1083 IS
1084 pragma AUTONOMOUS_TRANSACTION;
1085 cursor nodes_c is
1086 select upper(node_name) node_name, status, support_cp, support_forms,
1087 support_web, support_admin
1088 from FND_OAM_FNDNODES_VL
1089 where node_mode = 'O'
1090 and (nvl(support_cp, 'N') = 'Y' or
1091 nvl(support_forms, 'N') = 'Y' or
1092 nvl(support_web, 'N') = 'Y' or
1093 nvl(support_admin, 'N') = 'Y' or
1094 nvl(support_db, 'N') = 'Y');
1095 BEGIN
1096 -- delete existing app sys status information. We will change this later if we are
1097 -- going to move it to an archive table
1098 delete from fnd_oam_app_sys_status;
1099
1100 -- now update latest host information
1101 declare
1102 i number := 1;
1103 seq_counter number := 1;
1104 node_status number;
1105 begin
1106 for node in nodes_c loop
1107 select decode(nvl(node.status, 'U'),
1108 'Y',STATUS_NORMAL,
1109 'N',STATUS_ERROR,
1110 'U',STATUS_UNKNOWN, STATUS_UNKNOWN) into node_status
1111 from dual;
1112 insert_app_sys_status_internal(
1113 'HOST_'||to_char(i),
1114 null, null, null, null, node_status, node.node_name);
1115
1116 i := i + 1;
1117
1118
1119 -- now based on the various flags for the applications servers, update
1120 -- the status of individual processes as well as those of the servers
1121
1122 -- Concurrent Processing
1123 if node.support_cp = 'Y' then
1124 refresh_status_for_tier('C', node.node_name);
1125 end if; -- if node.support_cp = 'Y' then
1126
1127 -- For Forms
1128 if node.support_forms = 'Y' then
1129 refresh_status_for_tier('F', node.node_name);
1130 end if; -- if node.support_forms = 'Y' then
1131
1132 -- For ADMIN
1133 if node.support_admin = 'Y' then
1134 refresh_status_for_tier('A', node.node_name);
1135 end if; -- end if node.support_admin = 'Y' then
1136
1137 -- For WEB SERVER
1138 if node.support_web = 'Y' then
1139 refresh_status_for_tier('W', node.node_name);
1140 end if; -- if node.support_web = 'Y' then
1141
1142 end loop;
1143
1144 -- For DATA SERVER
1145 -- We will need to collect database statuses for all the
1146 -- individual instances list in gv$instance
1147 declare
1148 v_db_status number := STATUS_NORMAL;
1149 v_db_count number := 0;
1150 v_node_name varchar2(100);
1151 v_db_node_registered boolean := false;
1152 cursor dbst_c is
1153 select upper(host_name) host_name, instance_name, database_status
1154 from gv$instance;
1155
1156 -- added the where clause to this cursor as a fix for bug 3955412
1157 cursor nodex_c is
1158 --rjaiswal bug#4917109
1159 -- For virtual host we have the entry of main host in this column 'webhost'
1160 -- So this column can be used to compare the gv$instance host_name value
1161 -- instead of comparing it with node_name.Added webhost column
1162 select upper(node_name) node_name,upper(webhost) webhost from FND_OAM_FNDNODES_VL
1163 where node_mode = 'O'
1164 and (nvl(support_cp, 'N') = 'Y' or
1165 nvl(support_forms, 'N') = 'Y' or
1166 nvl(support_web, 'N') = 'Y' or
1167 nvl(support_admin, 'N') = 'Y' or
1168 nvl(support_db, 'N') = 'Y');
1169 --rjaiswal bug#4917109
1170 begin
1171 for db in dbst_c loop
1172 v_db_status := STATUS_NORMAL;
1173
1174 -- what are the possible values for database_status here that we
1175 -- need to check??
1176 if db.database_status <> 'ACTIVE' then
1177 v_db_status := STATUS_WARNING;
1178 end if;
1179
1180 -- fix for bug 2848014; if host_name has domain name as well
1181 -- only use the first part.
1182 v_node_name := db.host_name;
1183 for ndx in nodex_c loop
1184 declare
1185 v_gv_host varchar2(100);
1186 v_fn_node varchar2(100);
1187 --rjaiswal bug#4917109 starts
1188 --this variable is required to hold the value of web host:
1189 v_fn_webhost varchar2(100);
1190 --rjaiswal bug#4917109 ends
1191 begin
1192 select decode(instr(db.host_name, '.') - 1, -1,
1193 db.host_name,
1194 substr(db.host_name, 0,
1195 instr(db.host_name, '.') - 1))
1196 into v_gv_host from dual;
1197
1198 select decode(instr(ndx.node_name, '.') - 1, -1,
1199 ndx.node_name,
1200 substr(ndx.node_name, 0,
1201 instr(ndx.node_name, '.') - 1))
1202 into v_fn_node from dual;
1203 --rjaiswal bug#4917109 starts
1204 -- Storing the value of webhost inorder to compare it with v_gv_host
1205 select decode(instr(ndx.webhost, '.') - 1, -1,
1206 ndx.webhost,
1207 substr(ndx.webhost, 0,
1208 instr(ndx.webhost, '.') - 1))
1209 into v_fn_webhost from dual;
1210 -- ADDED "or v_gv_host = v_fn_webhost" in the "if" condition
1211 -- This column can be used to compare the gv$instance host_name value
1212 if ( v_gv_host = v_fn_node or v_gv_host = v_fn_webhost) then
1213 v_node_name := ndx.node_name;
1214 --rjaiswal bug#4917109 ends
1215 v_db_node_registered := true;
1216 exit; -- we found match so we are done with this loop
1217 end if;
1218 end;
1219 end loop;
1220
1221 if (v_db_node_registered = false) then
1222 -- no match found in FND_OAM_FNDNODES_VL, so we will create
1223 -- a new entry for this host (if one does not
1224 -- already exist) with status as unknown for now.
1225 -- This is temporary bug fix for 2952829.
1226 declare
1227 v_temp number;
1228 v_host_count number;
1229 begin
1230 select 1 into v_temp
1231 from fnd_oam_app_sys_status
1232 where metric_short_name like 'HOST_%'
1233 and node_name = db.host_name;
1234 exception
1235 when no_data_found then
1236 select count(*) into v_host_count
1237 from fnd_oam_app_sys_status
1238 where metric_short_name like 'HOST_%'
1239 and node_name is not null;
1240
1241 insert_app_sys_status_internal(
1242 'HOST_'||to_char(v_host_count+1),
1243 null, null, null, null,
1244 STATUS_UNKNOWN, db.host_name);
1245 end;
1246 v_node_name := db.host_name;
1247 end if;
1248
1249 -- now insert status Database instance into
1250 -- fnd_oam_app_sys_status
1251 -- we will insert only if an entry for the host on which the
1252 -- db instance lives has already been inserted. This way we
1253 -- will avoid errors during computing rollup in situations where
1254 -- a db instance was living on a node that is not being
1255 -- monitored.
1256 declare
1257 v_temp number;
1258 begin
1259 select 1 into v_temp
1260 from fnd_oam_app_sys_status
1261 where metric_short_name like 'HOST_%'
1262 and node_name = v_node_name;
1263
1264 select count(*) into v_db_count
1265 from fnd_oam_app_sys_status
1266 where metric_short_name like 'DATABASE_INS_%'
1267 and node_name = v_node_name;
1268
1269 insert_app_sys_status_internal(
1270 'DATABASE_INS_' || to_char(v_db_count+1) || '_' || v_node_name,
1271 null, null, db.instance_name, 'D', v_db_status, v_node_name);
1272 exception
1273 when no_data_found then
1274 null;
1275 end;
1276 end loop;
1277 end;
1278
1279 -- DATA SERVER
1280 -- Now compute the rollup for database server status on each node
1281 declare
1282 cursor all_monitored_nodes is
1283 select node_name from fnd_oam_app_sys_status
1284 where metric_short_name like 'HOST_%'
1285 and node_name is not null;
1286 cursor db_instance (p_node varchar2) is
1287 select metric_short_name, status_code
1288 from fnd_oam_app_sys_status
1289 where metric_short_name like 'DATABASE_INS_%'
1290 and node_name = p_node;
1291 begin
1292 for nd in all_monitored_nodes loop
1293 declare
1294 info_table st_table;
1295 v_db_status number;
1296 v_db_count number := 0;
1297 begin
1298 for dbi in db_instance(nd.node_name) loop
1299 if dbi.status_code = STATUS_ERROR then
1300 info_table(1) := 1;
1301 elsif dbi.status_code = STATUS_WARNING then
1302 info_table(2) := 1;
1303 elsif dbi.status_code = STATUS_UNKNOWN then
1304 info_table(3) := 1;
1305 elsif dbi.status_code = STATUS_NORMAL then
1306 info_table(4) := 1;
1307 elsif dbi.status_code = STATUS_INACTIVE then
1308 info_table(5) := 1;
1309 end if;
1310 v_db_count := v_db_count + 1;
1311 end loop;
1312
1313 if (v_db_count > 0) then
1314 v_db_status := compute_rollup_status(info_table);
1315 insert_app_sys_status_internal(
1316 'DATA_SERVER_' || nd.node_name,
1317 null, null, null, 'D', v_db_status, nd.node_name);
1318 end if;
1319 end;
1320 end loop;
1321 end;
1322
1323 -- Now finally, compute the overall status for CP, Forms, Admin, Web and
1324 -- Database. These are computed simply from the overall statuses across
1325 -- the different nodes.
1326 -- CP
1327 compute_overall_server_status('CP_SERVER', 'C');
1328
1329 -- FORM
1330 compute_overall_server_status('FORMS_SERVER', 'F');
1331
1332 -- DATA
1333 compute_overall_server_status('DATA_SERVER', 'D');
1334
1335 -- ADMIN
1336 compute_overall_server_status('ADMIN_SERVER', 'A');
1337
1338 -- WEB
1339 compute_overall_server_status('WEB_SERVER', 'W');
1340 end;
1341 commit;
1342 EXCEPTION
1343 when others then
1344 rollback;
1345 raise;
1346 END refresh_app_sys_status;
1347
1348 --
1349 -- Name
1350 -- refresh_activity
1351 --
1352 -- Purpose
1353 -- Computes the values for the following indicators and updates the
1354 -- fnd_oam_mets table using an autonomous transaction.
1355 -- 1) Number of Active Users
1356 -- 2) Number of Database sessions
1357 -- 3) Number of Running requests
1358 -- 4) Number of Service Processes
1359 -- 5) Number of Serivces Up
1360 -- 6) Number of Serivces Down
1361 -- 7) Number of invalid objects
1362 -- 8) % of Workflow mailer messages waiting to be sent
1363 --
1364 -- Input Arguments
1365 --
1366 -- Output Arguments
1367 --
1368 -- Notes:
1369 --
1370 --
1371 PROCEDURE refresh_activity
1372 IS
1373 pragma AUTONOMOUS_TRANSACTION;
1374 ct_active_users number;
1375 ct_db_sessions number;
1376 ct_running_req number;
1377 ct_service_processes number;
1378 ct_services_up number;
1379 ct_services_down number;
1380 ct_invalid_objects number;
1381 ct_waiting_msg number;
1382 ct_processed_msg number;
1383 BEGIN
1384 if (is_collection_enabled('ACTIVE_USERS')) then
1385 -- get the number of active users
1386 select count(distinct(F.login_id))
1387 into ct_active_users
1388 from fnd_login_resp_forms F,
1389 gv$session S
1390 where F.AUDSID = S.AUDSID;
1391
1392 -- update the number of active forms users
1393 update_metric_internal('ACTIVE_USERS', to_char(ct_active_users), -1);
1394 end if;
1395
1396 if (is_collection_enabled('DB_SESSIONS')) then
1397 -- get the number of db sessions
1398 select count(*) into ct_db_sessions from gv$session where audsid > 0;
1399
1400 -- update the number of db sessions
1401 update_metric_internal('DB_SESSIONS', to_char(ct_db_sessions), -1);
1402 end if;
1403
1404 if (is_collection_enabled('RUNNING_REQ')) then
1405 -- get the number of running requests
1406 select count(*)
1407 into ct_running_req
1408 from fnd_concurrent_requests
1409 where phase_code = 'R';
1410
1411 -- update the number of running requests
1412 update_metric_internal('RUNNING_REQ', to_char(ct_running_req), -1);
1413 end if;
1414
1415 if (is_collection_enabled('SERVICE_PROCS')) then
1416 -- get the number of service processes
1417 select count(*)
1418 into ct_service_processes
1419 from fnd_concurrent_processes
1420 where process_status_code in ('R','A','P');
1421
1422 -- update the number of service processes
1423 update_metric_internal('SERVICE_PROCS', to_char(ct_service_processes), -1);
1424 end if;
1425
1426 if (is_collection_enabled('SERVICES_UP')) then
1427 -- Now get the number of Services Up
1428 select count(concurrent_queue_id)
1429 into ct_services_up
1430 from fnd_concurrent_queues_vl
1431 where running_processes = max_processes and max_processes > 0;
1432
1433 -- Update Services Up
1434 update_metric_internal('SERVICES_UP', to_char(ct_services_up), 0);
1435 end if;
1436
1437 if (is_collection_enabled('SERVICES_DOWN')) then
1438 -- Get the number of services down
1439 select count(concurrent_queue_id)
1440 into ct_services_down
1441 from fnd_concurrent_queues_vl
1442 where running_processes = 0 and max_processes > 0;
1443
1444 -- Update Services down
1445 update_metric_internal('SERVICES_DOWN', to_char(ct_services_down), 2);
1446 end if;
1447
1448 if (is_collection_enabled('INVALID_OBJECTS')) then
1449 -- Get the count of invalid objects for 'APPS' schema only
1450 -- using user() function instead of hard coding 'APPS' - fix
1451 -- for bug 3876651
1452 SELECT COUNT(*)
1453 into ct_invalid_objects
1454 FROM DBA_OBJECTS DO
1455 WHERE DO.STATUS = 'INVALID' AND
1456 DO.OWNER = user and
1457 EXISTS (select 1
1458 from DBA_ERRORS DE
1459 where DE.NAME = DO.OBJECT_NAME AND
1460 DE.OWNER = DO.OWNER );
1461
1462 -- Update the invalid objects
1463 update_metric_internal('INVALID_OBJECTS', to_char(ct_invalid_objects), -1);
1464 end if;
1465
1466 -- added changes for this metric for performance optimization
1467 -- if a greater than threshold is specified for alerting, during
1468 -- FNDOAMCOL execution metric value will be counted only upto
1469 -- the threshold value
1470 if (is_collection_enabled('WFM_WAIT_MSG')) then
1471 declare
1472 v_alrt_enabled_flag fnd_oam_metval.alert_enabled_flag%TYPE;
1473 v_threshold_oper fnd_oam_metval.threshold_operator%TYPE;
1474 v_threshold_val fnd_oam_metval.threshold_value%TYPE;
1475 begin
1476 select nvl(alert_enabled_flag,'Y'),
1477 threshold_operator, threshold_value
1478 into v_alrt_enabled_flag, v_threshold_oper, v_threshold_val
1479 from fnd_oam_metval
1480 where metric_short_name = 'WFM_WAIT_MSG';
1481
1482 if (is_request() and
1483 v_alrt_enabled_flag = 'Y' and
1484 v_threshold_oper = OPER_G and
1485 v_threshold_val is not null) then
1486 -- Count only upto the specified threshold
1487 select count(*)
1488 into ct_waiting_msg
1489 from
1490 (
1491 select mail_status
1492 from wf_notifications
1493 where mail_status = 'MAIL' ) v
1494 where rownum <= to_number(v_threshold_val) + 1;
1495 else
1496 -- Get the full count of waiting workflow mailer messages
1497 select count(*)
1498 into ct_waiting_msg
1499 from wf_notifications
1500 where mail_status = 'MAIL';
1501 end if;
1502 end;
1503
1504 -- Update the unsent workflow email metric
1505 update_metric_internal('WFM_WAIT_MSG', to_char(ct_waiting_msg), -1);
1506 end if;
1507
1508 commit;
1509 EXCEPTION
1510 when others then
1511 rollback;
1512 raise;
1513 END refresh_activity;
1514
1515 --
1516 -- Name
1517 -- refresh_config_changes
1518 --
1519 -- Purpose
1520 -- Computes the values for the following indicators and updates the
1521 -- fnd_oam_mets table using an autonomous transaction.
1522 -- 1) Number of patches applied in the last 24 hours
1523 -- 2) Number of changes in profile options in last 24 hours
1524 --
1525 -- Input Arguments
1526 --
1527 -- Output Arguments
1528 --
1529 -- Notes:
1530 --
1531 --
1532 PROCEDURE refresh_config_changes
1533 IS
1534 pragma AUTONOMOUS_TRANSACTION;
1535 ct_patches number;
1536 ct_profile_options number;
1537 ct_context_files number;
1538 BEGIN
1539 if (is_collection_enabled('PATCHES')) then
1540 -- Get the number of patches applied within last 24 hours
1541
1542 -- Modified query for bug 3835667
1543 select count(*) into ct_patches
1544 from ad_patch_drivers d, ad_patch_runs r
1545 where r.end_date >= sysdate - 1
1546 and d.patch_driver_id = r.patch_driver_id;
1547
1548 --select count(distinct(PATCH_NAME))
1549 -- into ct_patches
1550 -- from ad_applied_patches where APPLIED_PATCH_ID in
1551 -- (select APPLIED_PATCH_ID from ad_patch_drivers where PATCH_DRIVER_ID in
1552 -- (select PATCH_DRIVER_ID from ad_patch_runs where sysdate-START_DATE <=1));
1553
1554 -- Update the number of patches applied within last 24 hours
1555 update_metric_internal('PATCHES', to_char(ct_patches), -1);
1556 end if;
1557
1558 if (is_collection_enabled('PROFILE_OPT')) then
1559 -- get the number of profile options changed in last 24 hours
1560 select count(*) into ct_profile_options
1561 from fnd_profile_options ovl,
1562 fnd_profile_option_values v
1563 where ovl.start_date_active <= SYSDATE
1564 and (nvl(ovl.end_date_active, SYSDATE) >= SYSDATE)
1565 and (v.level_id = 10001 and v.level_value = 0)
1566 and ovl.profile_option_id = v.profile_option_id
1567 and ovl.application_id = v.application_id
1568 and (sysdate - v.last_update_date <= 1);
1569
1570 -- Update the profile options
1571 update_metric_internal('PROFILE_OPT', to_char(ct_profile_options), -1);
1572 end if;
1573
1574 if (is_collection_enabled('CONTEXT_FILES_EDITED')) then
1575 -- query the number of context files changed in last 24 hours
1576 select count(*) into ct_context_files
1577 from (
1578 select focf.last_update_date lud
1579 from fnd_oam_context_files focf
1580 where (status <> 'H' or status is null)
1581 and upper(name) <> 'METADATA')
1582 where lud >= sysdate - 1;
1583
1584 -- update the number of context files changed
1585 update_metric_internal('CONTEXT_FILES_EDITED', to_char(ct_context_files), -1);
1586 end if;
1587
1588 commit;
1589 EXCEPTION
1590 when others then
1591 rollback;
1592 raise;
1593 END refresh_config_changes;
1594
1595 --
1596 -- Name
1597 -- refresh_throughput
1598 --
1599 -- Purpose
1600 -- Computes the values for the following indicators and updates the
1601 -- fnd_oam_mets table using an autonomous transaction.
1602 -- 1) % of Completed requests
1603 -- 2) % of Workflow mailer messages that have been processed
1604 --
1605 -- Input Arguments
1606 --
1607 -- Output Arguments
1608 --
1609 -- Notes:
1610 --
1611 --
1612 PROCEDURE refresh_throughput
1613 IS
1614 pragma AUTONOMOUS_TRANSACTION;
1615 ct_completed_req number;
1616 ct_total_req number;
1617 ct_waiting_msg number;
1618 ct_processed_msg number;
1619 BEGIN
1620 if (is_collection_enabled('COMPLETED_REQ')) then
1621 -- Compute the percentage of completed requests as the following:
1622 -- The numerator is the sum of the running requests and the requests that
1623 -- completed in the last 24 hours.
1624 -- The denominator is the sum of the running requests, the requests that
1625 -- completed in the last 24 hours, and the requests
1626 -- that are eligible to run at the current time.
1627 declare
1628 v_numerator number;
1629 v_denominator number;
1630 begin
1631 select count(*) into v_numerator
1632 from fnd_concurrent_requests
1633 where phase_code in ('R', 'C')
1634 and status_code <> 'D'
1635 and greatest(requested_start_date, request_date)
1636 between sysdate-1 and sysdate;
1637
1638 select count(*) into v_denominator
1639 from fnd_concurrent_requests
1640 where ( (phase_code in ('R', 'C')
1641 and status_code <> 'D')
1642 or ( status_code in ('I','Q')
1643 and hold_flag <> 'Y') )
1644 and greatest(requested_start_date, request_date)
1645 between sysdate-1 and sysdate;
1646
1647 select round((greatest(1,v_numerator)/greatest(1,v_denominator))* 100)
1648 into ct_completed_req
1649 from dual;
1650 update_metric_internal('COMPLETED_REQ', to_char(ct_completed_req), -1);
1651 end;
1652 end if;
1653
1654
1655 if (is_collection_enabled('WFM_PROC_MSG')) then
1656 -- Get the count of processed workflow mailer messages that began within
1657 -- last 24 hours
1658 select count(*)
1659 into ct_processed_msg
1660 from wf_notifications
1661 where mail_status = 'SENT'
1662 and status = 'OPEN'
1663 and (sysdate - begin_date <= 1);
1664
1665 update_metric_internal('WFM_PROC_MSG', to_char(ct_processed_msg), -1);
1666 end if;
1667
1668 commit;
1669 EXCEPTION
1670 when others then
1671 rollback;
1672 raise;
1673 END refresh_throughput;
1674
1675 --
1676 -- Name
1677 -- refresh_user_alerts_summary
1678 --
1679 -- Purpose
1680 -- Computes the values for the following indicators and updates the
1681 -- fnd_oam_mets table using an autonomous transaction.
1682 --
1683 -- 1) Number of New User Alerts
1684 -- 2) Number of New User Alert Occurrances
1685 -- 3) Number of Open User Alerts
1686 -- 4) Number of Open User Alert Occurrances
1687 --
1688 -- Input Arguments
1689 --
1690 -- Output Arguments
1691 --
1692 -- Notes:
1693 --
1694 --
1695 PROCEDURE refresh_user_alerts_summary
1696 IS
1697 pragma AUTONOMOUS_TRANSACTION;
1698 --ct_unpr number;
1699 --ct_pr number;
1700 --ct_total_unpr number;
1701 ct_new_al number;
1702 ct_new_occ number;
1703 ct_open_al number;
1704 ct_open_occ number;
1705 BEGIN
1706 if (is_collection_enabled('USER_ALERT_NEW')) then
1707 -- get the number of new alerts
1708 select count(*) into ct_new_al from fnd_log_unique_exceptions where
1709 status='N' and category='USER';
1710
1711 -- update new alerts
1712 update_metric_internal('USER_ALERT_NEW', to_char(ct_new_al), -1);
1713 end if;
1714
1715 if (is_collection_enabled('USER_ALERT_NEW_OCC')) then
1716 -- get the number of new occurrences
1717 select count(*) into ct_new_occ
1718 from fnd_log_exceptions fle, fnd_log_unique_exceptions flue
1719 where fle.unique_exception_id = flue.unique_exception_id
1720 and flue.status='N'
1721 and flue.category='USER';
1722
1723 -- update new occurrances
1724 update_metric_internal('USER_ALERT_NEW_OCC', to_char(ct_new_occ), -1);
1725 end if;
1726
1727
1728 if (is_collection_enabled('USER_ALERT_OPEN')) then
1729 -- get number of open alerts
1730 select count(*) into ct_open_al from fnd_log_unique_exceptions where
1731 status='O' and category='USER';
1732
1733 -- update open alerts
1734 update_metric_internal('USER_ALERT_OPEN', to_char(ct_open_al), -1);
1735 end if;
1736
1737 if (is_collection_enabled('USER_ALERT_OPEN_OCC')) then
1738 -- get the number of open occurrences
1739 select count(*) into ct_open_occ
1740 from fnd_log_exceptions fle, fnd_log_unique_exceptions flue
1741 where fle.unique_exception_id = flue.unique_exception_id
1742 and flue.status='O'
1743 and flue.category='USER';
1744
1745 -- update open occurrances
1746 update_metric_internal('USER_ALERT_OPEN_OCC', to_char(ct_open_occ), -1);
1747 end if;
1748
1749 commit;
1750 EXCEPTION
1751 when others then
1752 rollback;
1753 raise;
1754 END refresh_user_alerts_summary;
1755
1756 --
1757 -- Name
1758 -- refresh_exceptions_summary
1759 --
1760 -- Purpose
1761 -- Computes the values for the following indicators and updates the
1762 -- fnd_oam_mets table using an autonomous transaction.
1763 -- #1) Number of critical unprocessed exceptions in last 24 hours
1764 -- #2) Number of critical processed exceptions in last 24 hours
1765 -- #3) Number of total critical unprocessed exceptions
1766 --
1767 -- 1) Number of New System Alerts
1768 -- 2) Number of New Occurrances
1769 -- 3) Number of Open System Alerts
1770 -- 4) Number of Open Occurrances
1771 --
1772 -- Input Arguments
1773 --
1774 -- Output Arguments
1775 --
1776 -- Notes:
1777 --
1778 --
1779 PROCEDURE refresh_exceptions_summary
1780 IS
1781 pragma AUTONOMOUS_TRANSACTION;
1782 --ct_unpr number;
1783 --ct_pr number;
1784 --ct_total_unpr number;
1785 ct_new_al number;
1786 ct_new_occ number;
1787 ct_open_al number;
1788 ct_open_occ number;
1789 BEGIN
1790 if (is_collection_enabled('CRIT_UNPR_EXCEP')) then
1791 -- get the number of new alerts
1792 select count(*) into ct_new_al from fnd_log_unique_exceptions where
1793 status='N';
1794
1795 -- update new alerts
1796 update_metric_internal('CRIT_UNPR_EXCEP', to_char(ct_new_al), -1);
1797 end if;
1798
1799 if (is_collection_enabled('CRIT_PR_EXCEP')) then
1800 -- get the number of new occurrences
1801 select count(*) into ct_new_occ
1802 from fnd_log_exceptions fle, fnd_log_unique_exceptions flue
1803 where fle.unique_exception_id = flue.unique_exception_id
1804 and flue.status='N';
1805
1806 -- update new occurrances
1807 update_metric_internal('CRIT_PR_EXCEP', to_char(ct_new_occ), -1);
1808 end if;
1809
1810
1811 if (is_collection_enabled('CRIT_TOTAL_UNPR_EXCEP')) then
1812 -- get number of open alerts
1813 select count(*) into ct_open_al from fnd_log_unique_exceptions where
1814 status='O';
1815
1816 -- update open alerts
1817 update_metric_internal('CRIT_TOTAL_UNPR_EXCEP', to_char(ct_open_al), -1);
1818 end if;
1819
1820 if (is_collection_enabled('OPEN_OCC')) then
1821 -- get the number of open occurrences
1822 select count(*) into ct_open_occ
1823 from fnd_log_exceptions fle, fnd_log_unique_exceptions flue
1824 where fle.unique_exception_id = flue.unique_exception_id
1825 and flue.status='O';
1826
1827 -- update open occurrances
1828 update_metric_internal('OPEN_OCC', to_char(ct_open_occ), -1);
1829 end if;
1830
1831 commit;
1832 EXCEPTION
1833 when others then
1834 rollback;
1835 raise;
1836 END refresh_exceptions_summary;
1837
1838 --
1839 -- Name
1840 -- refresh_miscellaneous
1841 --
1842 -- Purpose
1843 -- Computes the values for the following indicators and
1844 -- updates the FND_OAM_METVAL using an autonomous transaction.
1845 -- Metrics: PL./SQL Agent, Servlet Agent, JSP Agent, JTF, Discoverer,
1846 -- Personal Home Page, TCF
1847 --
1848 -- Input Arguments
1849 --
1850 -- Output Arguments
1851 --
1852 -- Notes:
1853 -- THIS PROCEDURE IS DEPRECATED - PLEASE DO NOT CALL THIS ANYMORE
1854 -- Use the corresponding Java API instead.
1855 --
1856 PROCEDURE refresh_miscellaneous
1857 IS
1858 pragma AUTONOMOUS_TRANSACTION;
1859 v_status_code number;
1860 v_agent_short_name varchar2(30);
1861 BEGIN
1862 for agent in AGENT_MIN..AGENT_MAX loop
1863 v_status_code := get_agent_status(agent);
1864
1865 -- figure out the metric short name
1866 if agent = AOLJ_DB_POOL then
1867 v_agent_short_name := 'AOLJ_DB_POOL_GEN';
1868 elsif agent = PLSQL_AGNT then
1869 v_agent_short_name := 'PL_SQL_AGNT_GEN';
1870 elsif agent = SERVLET_AGNT then
1871 v_agent_short_name := 'SERVLET_AGNT_GEN';
1872 elsif agent = TCF then
1873 v_agent_short_name := 'TCF_GEN';
1874 elsif agent = JSP then
1875 v_agent_short_name := 'JSP_AGNT_GEN';
1876 elsif agent = JTF then
1877 v_agent_short_name := 'JTF_GEN';
1878 elsif agent = DISCOVERER then
1879 v_agent_short_name := 'DISCOVERER_GEN';
1880 elsif agent = PHP then
1881 v_agent_short_name := 'PHP_GEN';
1882 elsif agent = REPORT then
1883 v_agent_short_name := 'REPORT_GEN';
1884 elsif agent = FWK then
1885 v_agent_short_name := 'FWK_GEN';
1886 elsif agent = FORMS then
1887 v_agent_short_name := 'FORMS_GEN';
1888 end if;
1889
1890 update_metric_internal(v_agent_short_name, null, v_status_code);
1891 end loop;
1892
1893 commit;
1894 EXCEPTION
1895 when others then
1896 rollback;
1897 raise;
1898 END refresh_miscellaneous;
1899
1900 --
1901 -- Name
1902 -- raise_alerts
1903 --
1904 -- Purpose
1905 -- Checks values for all metrics and service instances that are currently
1906 -- being monitored and raises alert if the values or status codes match
1907 -- the thresholds specified by the user.
1908 --
1909 -- Input Arguments
1910 --
1911 -- Output Arguments
1912 --
1913 -- Notes:
1914 --
1915 --
1916 PROCEDURE raise_alerts
1917 IS
1918 cursor c_mets is
1919 select metric_short_name, metric_type, metric_value, status_code,
1920 threshold_operator, threshold_value
1921 from fnd_oam_metval
1922 where nvl(is_supported,'Y') = 'Y'
1923 and nvl(collection_enabled_flag,'Y') = 'Y'
1924 and nvl(alert_enabled_flag, 'N') = 'Y'
1925 and group_id <> 8 and group_id <> 0; -- Disabling Alerting for Web Components and Internal Metrics
1926 -- For OAM Rollup A
1927
1928 cursor c_svci is
1929 select foa.application_id application_id,
1930 foa.concurrent_queue_name concurrent_queue_name,
1931 fcq.concurrent_queue_id concurrent_queue_id,
1932 foa.status_code status_code
1933 from fnd_oam_app_sys_status foa,
1934 fnd_concurrent_queues fcq,
1935 fnd_oam_svci_info fsi
1936 where foa.application_id = fcq.application_id
1937 and foa.concurrent_queue_name = fcq.concurrent_queue_name
1938 and foa.application_id = fsi.application_id (+)
1939 and foa.concurrent_queue_name = fsi.concurrent_queue_name (+)
1940 and nvl(fsi.collection_enabled_flag, 'Y') = 'Y'
1941 and nvl(fsi.alert_enabled_flag, 'N') = 'Y';
1942
1943 v_metric_list varchar2(3500) := '';
1944 v_st_list varchar2(3500) := '';
1945 v_temp varchar2(300) := '';
1946
1947 v_x varchar2(2000);
1948 BEGIN
1949 for met in c_mets loop
1950 if (shall_raise_alert(met.metric_short_name)) then
1951 v_temp := met.metric_short_name;
1952 if (met.metric_type = 'S') then
1953 v_temp := v_temp || ':' || met.status_code || ',';
1954 if (length(v_st_list || v_temp) < 3500) then
1955 v_st_list := v_st_list || v_temp;
1956 end if;
1957 else
1958 v_temp := v_temp ||':'||met.threshold_operator||':'||met.threshold_value||',';
1959 if (length(v_metric_list || v_temp) < 3500) then
1960 v_metric_list := v_metric_list || v_temp;
1961 end if;
1962 end if;
1963 end if;
1964 end loop;
1965
1966 for svci in c_svci loop
1967 if (shall_raise_alert(svci.application_id,
1968 svci.concurrent_queue_name)) then
1969 v_temp := svci.application_id||':'||svci.concurrent_queue_id||':'||svci.status_code||',';
1970
1971 if (length(v_st_list || v_temp) < 3500) then
1972 v_st_list := v_st_list || v_temp;
1973 end if;
1974 end if;
1975 end loop;
1976
1977 if (length(v_metric_list) > 0) then
1978 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
1979 fnd_message.clear;
1980 fnd_message.set_name('FND','OAM_DASHBOARD_METRIC_ALERT');
1981 fnd_message.set_token_sql('METRICS_AND_VALUES',
1982 'select fnd_oam_dashboard_util.get_trans_name_values(''MET'','''||v_metric_list||''') from dual');
1983 --dbms_output.put_line('MET ALERT: ' || fnd_message.get);
1984 fnd_log.message(log_level=>fnd_log.level_unexpected,
1985 module=>MODULE||'.raise_alert',
1986 pop_message=>true);
1987 end if;
1988 end if;
1989
1990 if (length(v_st_list) > 0) then
1991 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
1992 fnd_message.clear;
1993 fnd_message.set_name('FND','OAM_DASHBOARD_STATUS_ALERT');
1994 fnd_message.set_token_sql('COMPONENTS_AND_STATUS',
1995 'select fnd_oam_dashboard_util.get_trans_name_values(''STATUS'','''||v_st_list||''') from dual');
1996 --dbms_output.put_line('STATUS ALERT: ' || fnd_message.get);
1997 fnd_log.message(log_level=>fnd_log.level_unexpected,
1998 module=>MODULE||'.raise_alert',
1999 pop_message=>true);
2000 end if;
2001 end if;
2002 END;
2003
2004
2005 --
2006 -- Name
2007 -- refresh_all
2008 --
2009 -- Purpose
2010 -- Computes the values for all the indicators and updates the
2011 -- fnd_oam_mets table.
2012 --
2013 -- Input Arguments
2014 --
2015 -- Output Arguments
2016 -- errbuf - for any error message
2017 -- retcode - 0 for success, 1 for success with warnings, 2 for error
2018 --
2019 -- Notes:
2020 --
2021 --
2022 PROCEDURE refresh_all (errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2)
2023 IS
2024
2025 BEGIN
2026 fnd_file.put_line(fnd_file.log, 'Refreshing All ...');
2027
2028 fnd_file.put_line(fnd_file.log, 'Refreshing Applications System Status ...');
2029 refresh_app_sys_status;
2030 fnd_file.new_line(fnd_file.log, 1);
2031
2032 fnd_file.put_line(fnd_file.log, 'Refreshing Activity ...');
2033 refresh_activity;
2034 fnd_file.new_line(fnd_file.log, 1);
2035
2036 fnd_file.put_line(fnd_file.log, 'Refreshing Configuration Changes ...');
2037 refresh_config_changes;
2038 fnd_file.new_line(fnd_file.log, 1);
2039
2040 fnd_file.put_line(fnd_file.log, 'Refreshing Throughput ...');
2041 refresh_throughput;
2042 fnd_file.new_line(fnd_file.log, 1);
2043
2044 fnd_file.put_line(fnd_file.log, 'Refreshing Exceptions Summary ...');
2045 refresh_exceptions_summary;
2046 fnd_file.new_line(fnd_file.log, 1);
2047
2048 fnd_file.put_line(fnd_file.log, 'Refreshing Miscellaneous ...');
2049 refresh_miscellaneous;
2050 fnd_file.new_line(fnd_file.log, 1);
2051
2052 fnd_file.put_line(fnd_file.log, 'Done refreshing All ...');
2053
2054 -- Cancel any reduntant pending requests
2055 fnd_file.put_line(fnd_file.log, 'Cancelling any pending requests for FNDOAMCOL ...');
2056 declare
2057 cursor pending_req is
2058 select
2059 fcr.request_id request_id
2060 from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
2061 where
2062 fcr.program_application_id = fcp.application_id
2063 and fcr.concurrent_program_id = fcp.concurrent_program_id
2064 and fcp.concurrent_program_name = 'FNDOAMCOL'
2065 and fcr.phase_code = 'P';
2066 ret_code number;
2067 ret_msg varchar2(1000);
2068 begin
2069 for p_r in pending_req loop
2070 begin
2071 ret_code := -1;
2072 ret_msg := '';
2073 fnd_file.put_line(fnd_file.log, 'Found pending request: ' || p_r.request_id);
2074 ret_code := fnd_amp_private.cancel_request(p_r.request_id, ret_msg);
2075 if (ret_code <> 4 and ret_code <> 5) then
2076 fnd_file.put_line(fnd_file.log, 'Unable to cancel request ' || p_r.request_id);
2077 fnd_file.put_line(fnd_file.log, 'Message: ' || ret_msg);
2078 elsif (ret_code >= 0) then
2079 fnd_file.put_line(fnd_file.log, 'Cancelled request ' || p_r.request_id);
2080 else
2081 fnd_file.put_line(fnd_file.log, 'ret_code: ' || ret_code);
2082 fnd_file.put_line(fnd_file.log, 'Message: ' || ret_msg);
2083 end if;
2084 end;
2085 end loop;
2086
2087 exception
2088 -- some error occurred while cancelling pending requests
2089 -- Not failing the program since refresh was completed successfully.
2090 when others then
2091 null;
2092 end;
2093 fnd_file.new_line(fnd_file.log, 1);
2094 fnd_file.put_line(fnd_file.log, 'Done cancelling any pending requests for FNDOAMCOL ...');
2095
2096 retcode := '0';
2097 errbuf := 'Refresh Action OK';
2098 EXCEPTION
2099 when others then
2100 retcode := '2';
2101 errbuf := SQLERRM;
2102 END refresh_all;
2103
2104 --
2105 -- Name
2106 -- submit_col_req_conditional
2107 --
2108 -- Purpose
2109 -- Submits a request for program 'FNDOAMCOL' if and only if there are no
2110 -- other requests for this program in the pending or running phase.
2111 --
2112 -- Input Arguments
2113 --
2114 -- Output Arguments
2115 --
2116 --
2117 -- Notes:
2118 --
2119 --
2120 PROCEDURE submit_col_req_conditional
2121 IS
2122 retcode number;
2123 retval boolean;
2124 msg varchar2(1000);
2125 active_count number;
2126
2127 appl_id number;
2128 resp_id number;
2129 user_id number;
2130 user_name varchar2(80);
2131 resp_name varchar2(80);
2132 resp_key varchar2(50);
2133
2134 p_request_id number := null;
2135 p_phase varchar2(100);
2136 p_status varchar2(100);
2137 p_dev_phase varchar2(100);
2138 p_dev_status varchar2(100);
2139 p_message varchar2(500);
2140 outcome boolean;
2141 BEGIN
2142 -- First query to see if there is a request already submitted for this
2143 -- program.
2144 outcome :=
2145 fnd_concurrent.get_request_status(
2146 request_id=>p_request_id,
2147 appl_shortname=>'FND',
2148 program=>'FNDOAMCOL',
2149 phase=>p_phase,
2150 status=>p_status,
2151 dev_phase=>p_dev_phase,
2152 dev_status=>p_dev_status,
2153 message=>p_message);
2154
2155 --dbms_output.put_line('REQ ID ' || p_request_id);
2156 --dbms_output.put_line('PHASE ' || p_phase);
2157 --dbms_output.put_line('STATUS ' || p_status);
2158 --dbms_output.put_line('DEV_PHASE ' || p_dev_phase);
2159 --dbms_output.put_line('DEV_STATUS ' || p_dev_status);
2160 --dbms_output.put_line('MESSAGE ' || p_message);
2161 if p_dev_phase is null then
2162 p_dev_phase := 'X';
2163 end if;
2164 if ((outcome = false and p_request_id is null) or
2165 (outcome = true and p_request_id is not null and
2166 p_dev_phase <> 'PENDING' and
2167 p_dev_phase <> 'RUNNING')) and
2168 fnd_program.program_exists('FNDOAMCOL', 'FND') = true then
2169
2170 --dbms_output.put_line('Submmitting request');
2171 --select application_id, responsibility_id, responsibility_name
2172 -- into appl_id, resp_id, resp_name
2173 -- from fnd_responsibility_vl
2174 --where responsibility_name = 'System Administrator';
2175
2176 select application_id, responsibility_id, responsibility_key
2177 into appl_id, resp_id, resp_key
2178 from fnd_responsibility
2179 where responsibility_key = 'SYSTEM_ADMINISTRATOR';
2180
2181 select user_id, user_name
2182 into user_id, user_name
2183 from fnd_user
2184 where user_name = 'SYSADMIN';
2185
2186 -- Now initialize the environment for SYSADMIN
2187 fnd_global.apps_initialize(user_id, resp_id, appl_id);
2188
2189 -- Set the repeat options
2190 retval := fnd_request.set_repeat_options(repeat_interval => 10,
2191 repeat_unit => 'MINUTES',
2192 repeat_type => 'END');
2193
2194 -- Submit the request.
2195 retcode := fnd_request.submit_request(application=>'FND', program=>'FNDOAMCOL');
2196
2197 end if;
2198 commit;
2199 EXCEPTION
2200 when others then
2201 rollback;
2202 null;
2203 END submit_col_req_conditional;
2204
2205 --
2206 -- Name
2207 -- resubmit
2208 --
2209 -- Purpose
2210 -- Submits a request for program 'FNDOAMCOL' with the new repeat
2211 -- interval if the current interval is different from the new repeat
2212 -- interval.
2213 --
2214 -- It will cancel any pending or running requests before submitting
2215 -- a new request.
2216 --
2217 -- If more than one repeating requests are found, it will cancel them
2218 -- all and submit a new request with the given repeat interval.
2219 --
2220 --
2221 -- Input Arguments
2222 --
2223 -- p_repeat_interval - The new repeat interval
2224 -- p_repeat_interval_unit_code - The new repeat interval unit code
2225 --
2226 -- Output Arguments
2227 -- p_ret_code
2228 -- -1 - Was unable to cancel one or more in progress requests.
2229 -- Check p_ret_msg for any error message.
2230 -- -2 - There was no need to resubmit - since the currently
2231 -- repeating request has the same repeat interval.
2232 -- >0 - Successfully resubmitted. Request id of the new request.
2233 --
2234 -- p_ret_msg
2235 -- Any return error message.
2236 -- Notes:
2237 --
2238 --
2239 PROCEDURE resubmit(
2240 p_repeat_interval fnd_concurrent_requests.resubmit_interval%TYPE,
2241 p_repeat_interval_unit_code fnd_concurrent_requests.resubmit_interval_unit_code%TYPE,
2242 p_ret_code OUT NOCOPY number,
2243 p_ret_msg OUT NOCOPY varchar2)
2244 IS
2245 v_in_progress_count number := 0;
2246 v_continue_submit boolean := false;
2247 v_curr_interval fnd_concurrent_requests.resubmit_interval%TYPE;
2248 v_curr_unit_code fnd_concurrent_requests.resubmit_interval_unit_code%TYPE;
2249 ret_code number := -1;
2250 ret_msg varchar2(1000) := '';
2251 retval boolean;
2252 BEGIN
2253 select count(*) into v_in_progress_count
2254 from fnd_concurrent_requests fcr,
2255 fnd_concurrent_programs fcp
2256 where
2257 fcr.program_application_id = fcp.application_id
2258 and fcr.concurrent_program_id = fcp.concurrent_program_id
2259 and fcp.concurrent_program_name = 'FNDOAMCOL'
2260 and fcr.phase_code in ('R','P')
2261 and fcr.resubmit_interval is not null
2262 and fcr.resubmit_interval_unit_code is not null;
2263
2264 if (v_in_progress_count <> 1) then
2265 v_continue_submit := true;
2266 else
2267 -- compare repeat intervals to see if we need to resubmit
2268 select fcr.resubmit_interval, fcr.resubmit_interval_unit_code
2269 into v_curr_interval, v_curr_unit_code
2270 from fnd_concurrent_requests fcr,
2271 fnd_concurrent_programs fcp
2272 where
2273 fcr.program_application_id = fcp.application_id
2274 and fcr.concurrent_program_id = fcp.concurrent_program_id
2275 and fcp.concurrent_program_name = 'FNDOAMCOL'
2276 and fcr.phase_code in ('R','P')
2277 and fcr.resubmit_interval is not null
2278 and fcr.resubmit_interval_unit_code is not null;
2279
2280 if (v_curr_interval <> p_repeat_interval or
2281 v_curr_unit_code <> p_repeat_interval_unit_code) then
2282 v_continue_submit := true;
2283 else
2284 p_ret_code := -2; -- there was no need to resubmit
2285 p_ret_msg := '';
2286 end if;
2287 end if;
2288
2289 if (v_continue_submit = true) then
2290 -- cancel all pending running requests for FNDOAMCOL and submit new
2291 declare
2292 cursor repeating_req is
2293 select
2294 fcr.request_id request_id
2295 from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
2296 where
2297 fcr.program_application_id = fcp.application_id
2298 and fcr.concurrent_program_id = fcp.concurrent_program_id
2299 and fcp.concurrent_program_name = 'FNDOAMCOL'
2300 and fcr.phase_code in ('P','R');
2301
2302
2303 begin
2304 for p_r in repeating_req loop
2305 begin
2306 ret_code := fnd_amp_private.cancel_request(p_r.request_id, ret_msg);
2307 if (ret_code <> 4 and ret_code <> 5) then
2308 p_ret_code := -1;
2309 p_ret_msg := ret_msg;
2310 -- unable to cancel request
2311 return;
2312 end if;
2313
2314 -- log some debugging info
2315 if (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
2316 fnd_log.string(log_level=>fnd_log.level_event,
2317 module=>MODULE||'.resubmit',
2318 message=>'Cancelled Request ' || p_r.request_id);
2319 end if;
2320 end;
2321 end loop;
2322
2323 -- Set the repeat options
2324 retval := fnd_request.set_repeat_options(
2325 repeat_interval => p_repeat_interval,
2326 repeat_unit => p_repeat_interval_unit_code,
2327 repeat_type => 'END');
2328 -- Submit the request.
2329 p_ret_code := fnd_request.submit_request(application=>'FND', program=>'FNDOAMCOL');
2330 p_ret_msg := ret_msg;
2331
2332 -- check for any errors in the request submission
2333 if (p_ret_code = 0) then
2334 p_ret_msg := fnd_message.get;
2335 end if;
2336 exception
2337 -- some error occurred
2338 when others then
2339 raise;
2340 end;
2341
2342 end if;
2343
2344 END resubmit;
2345
2346 --
2347 -- API to check if an already new or open alert exists for the given
2348 -- encoded messge
2349 --
2350 /*
2351 FUNCTION is_alert_open ( p_enc_msg fnd_log_unique_exceptions.encoded_message%TYPE) RETURN boolean
2352 IS
2353 v_exists boolean := FALSE;
2354 v_temp number := 0;
2355 BEGIN
2356 debug('p_enc_msg: ' || p_enc_msg);
2357 begin
2358 select 1 into v_temp
2359 from fnd_log_unique_exceptions
2360 where encoded_message = p_enc_msg
2361 and status in ('N','O');
2362 v_exists := TRUE;
2363 exception
2364 when no_data_found then
2365 v_exists := FALSE;
2366 end;
2367 debug('is_alert_open returning ' || v_temp);
2368 return v_exists;
2369 END is_alert_open;
2370 */
2371
2372 --
2373 -- Internal API for Alerting for Long Running Requests General
2374 --
2375 PROCEDURE ALERT_LRR_GEN
2376 IS
2377 v_lng_run_req_count fnd_oam_metval_vl.metric_value%TYPE;
2378 v_lng_run_req_alert_enable varchar2(1):='';
2379 v_lng_run_req_tolerance varchar2(10):='';
2380 v_lng_run_req_offset varchar2(10):='';
2381
2382 cursor req_id_list(p_tol varchar2,p_offset varchar2) is
2383 select fcr.request_id request_id,
2384 fcp.user_concurrent_program_name user_concurrent_program_name
2385 from
2386 fnd_concurrent_requests fcr,
2387 fnd_conc_prog_onsite_info fcpoi,
2388 fnd_concurrent_programs_vl fcp
2389 where
2390 fcr.program_application_id=fcpoi.program_application_id
2391 AND fcp.application_id = fcr.PROGRAM_APPLICATION_ID
2392 AND fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
2393 and fcr.concurrent_program_id=fcpoi.concurrent_program_id
2394 and fcpoi.avg_run_time is not null
2395 and (sysdate -fcr.ACTUAL_START_DATE)*86400 >
2396 (fcpoi.avg_run_time*(1+.01*to_number(p_tol)))
2397 and ((sysdate -fcr.ACTUAL_START_DATE)*86400 >
2398 (to_number(p_offset)*60))
2399 and fcr.phase_code='R';
2400
2401 -- Types for record of tables
2402 TYPE NumTabType IS TABLE OF NUMBER
2403 INDEX BY BINARY_INTEGER;
2404 TYPE VarcharTabType IS TABLE OF
2405 fnd_concurrent_programs_vl.user_concurrent_program_name%TYPE
2406 INDEX BY BINARY_INTEGER;
2407
2408 TYPE RecTabType IS RECORD
2409 (request_id NumTabType,
2410 display_name VarcharTabType);
2411
2412 v_req_list_rec RecTabType;
2413 v_attch_id number;
2414
2415 BEGIN
2416 select threshold_value, alert_enabled_flag
2417 into v_lng_run_req_count, v_lng_run_req_alert_enable
2418 from fnd_oam_metval
2419 where metric_short_name ='LONG_RUNNING_REQ_COUNT';
2420
2421 select threshold_value into v_lng_run_req_tolerance
2422 from fnd_oam_metval
2423 where metric_short_name ='LONG_RUNNING_REQ_TOLERANCE';
2424
2425 select threshold_value into v_lng_run_req_offset
2426 from fnd_oam_metval
2427 where metric_short_name ='LONG_RUNNING_REQ_OFFSET';
2428
2429 if(v_lng_run_req_alert_enable ='Y') then
2430 open req_id_list(v_lng_run_req_tolerance,v_lng_run_req_offset);
2431 fetch req_id_list bulk collect
2432 into v_req_list_rec.request_id, v_req_list_rec.display_name;
2433 close req_id_list;
2434
2435 if ((v_req_list_rec.request_id is not null) and (v_req_list_rec.request_id.count >= TO_NUMBER(v_lng_run_req_count))) then
2436 -- raise alert with attachment
2437 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
2438 fnd_message.clear;
2439 fnd_message.set_name('FND','OAM_SYSAL_LONG_RUNNING_REQ_GEN');
2440 fnd_message.set_token('COUNT',v_lng_run_req_count);
2441 fnd_message.set_token('TOLERANCE',v_lng_run_req_tolerance);
2442 v_attch_id := fnd_log.message_with_attachment(
2443 log_level=>fnd_log.level_unexpected,
2444 module=>MODULE||'.Alert_Long_Running_Requests',
2445 pop_message=>false);
2446 fnd_log_attachment.writeln(
2447 pattachment_id => v_attch_id,
2448 pmessage => fnd_message.get);
2449 fnd_log_attachment.writeln(
2450 pattachment_id => v_attch_id,
2451 pmessage => ' ');
2452 for i in v_req_list_rec.request_id.FIRST..v_req_list_rec.request_id.LAST loop
2453 -- write info to attachment about each request.
2454 fnd_log_attachment.writeln(
2455 pattachment_id => v_attch_id,
2456 pmessage => v_req_list_rec.request_id(i) || ' - ' ||
2457 v_req_list_rec.display_name(i));
2458 end loop;
2459 fnd_log_attachment.close(
2460 pattachment_id => v_attch_id);
2461 end if;
2462 end if;
2463 end if; -- if(v_lng_run_req_alert_enable ='Y') then
2464
2465 END ALERT_LRR_GEN;
2466
2467 --
2468 -- Internal API for Alerting for Long Running Requests Specific
2469 --
2470 PROCEDURE ALERT_LRR_SPE
2471 IS
2472 v_spec_long_run_enabled varchar2(1):='';
2473
2474 cursor spec_req_id_list is
2475 select fcr.request_id request_id,
2476 fcp.user_concurrent_program_name user_concurrent_program_name,
2477 fcpoi.max_run_time max_run_time,
2478 fcpoi.avg_run_time avg_run_time,
2479 fcpoi.alert_long_running_threshold/60 threshold_minutes,
2480 fcpoi.alert_long_running_tolerance tolerance
2481 from
2482 fnd_concurrent_requests fcr,
2483 fnd_conc_prog_onsite_info fcpoi,
2484 fnd_concurrent_programs_vl fcp
2485 where
2486 fcr.program_application_id=fcpoi.program_application_id
2487 AND fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
2488 AND fcp.application_id = fcr.PROGRAM_APPLICATION_ID
2489 and fcr.concurrent_program_id=fcpoi.concurrent_program_id
2490 and ((fcpoi.ALERT_LONG_RUNNING_THRESHOLD is not null)
2491 or (fcpoi.AVG_RUN_TIME is not null))
2492 and (sysdate -fcr.ACTUAL_START_DATE)*86400 >
2493 (to_number(nvl(fcpoi.ALERT_LONG_RUNNING_THRESHOLD,
2494 fcpoi.AVG_RUN_TIME))*(1+.01*to_number(nvl(
2495 fcpoi.ALERT_LONG_RUNNING_TOLERANCE,0))))
2496 and fcpoi.ALERT_LONG_RUNNING_ENABLED='Y'
2497 and fcr.phase_code='R';
2498
2499 -- Types for record of tables
2500 TYPE NumTabType IS TABLE OF NUMBER
2501 INDEX BY BINARY_INTEGER;
2502 TYPE VarcharTabType IS TABLE OF
2503 fnd_concurrent_programs_vl.user_concurrent_program_name%TYPE
2504 INDEX BY BINARY_INTEGER;
2505
2506 TYPE SpecRecTabType IS RECORD
2507 (request_id NumTabType,
2508 display_name VarcharTabType,
2509 max_run_time NumTabType,
2510 avg_run_time NumTabType,
2511 threshold NumTabType,
2512 tolerance NumTabType);
2513
2514 v_spec_req_list_rec SpecRecTabType;
2515
2516 v_spec_cnt number := 0;
2517 v_spec_tol number;
2518
2519 BEGIN
2520
2521 select threshold_value into v_spec_long_run_enabled
2522 from fnd_oam_metval
2523 where metric_short_name ='SPECIFIC_LONG_RUNNING_ENABLED';
2524
2525 if(v_spec_long_run_enabled ='Y') then
2526 --debug('v_spec_long_run_enabled is Y');
2527 select count(*) into v_spec_cnt
2528 from fnd_conc_prog_onsite_info
2529 where ALERT_LONG_RUNNING_ENABLED='Y' and rownum < 2;
2530 if (v_spec_cnt > 0) then
2531 open spec_req_id_list;
2532 fetch spec_req_id_list bulk collect
2533 into v_spec_req_list_rec.request_id,
2534 v_spec_req_list_rec.display_name,
2535 v_spec_req_list_rec.max_run_time,
2536 v_spec_req_list_rec.avg_run_time,
2537 v_spec_req_list_rec.threshold,
2538 v_spec_req_list_rec.tolerance;
2539 close spec_req_id_list;
2540
2541 if (v_spec_req_list_rec.request_id is not null and v_spec_req_list_rec.request_id.count > 0) then
2542 for i in v_spec_req_list_rec.request_id.FIRST..v_spec_req_list_rec.request_id.LAST loop
2543 --debug('Looping in cursor spec_req_id_list');
2544 if ((fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)) then
2545 -- Compute tolerance for this program
2546 v_spec_tol := round(
2547 nvl(v_spec_req_list_rec.threshold(i),
2548 v_spec_req_list_rec.avg_run_time(i))*(
2549 1+.01*to_number(nvl(
2550 v_spec_req_list_rec.tolerance(i),0))));
2551
2552 -- Note: In the future if we provide users with more options, we
2553 -- may need to choose different messages based on those options
2554
2555 -- raise proxy alert for each request
2556 fnd_message.clear;
2557 fnd_message.set_name('FND','OAM_SYSAL_LONG_RUNNING_REQ_SPE');
2558 fnd_message.set_token('PROG', v_spec_req_list_rec.display_name(i));
2559 fnd_message.set_token('TOLERANCE', v_spec_tol);
2560 fnd_log.proxy_alert_for_conc_req(
2561 module => MODULE||'.Alert_Long_Running_Requests',
2562 pop_message => true,
2563 request_id => v_spec_req_list_rec.request_id(i));
2564 end if;
2565 end loop;
2566 end if;
2567 end if;
2568 end if;
2569
2570 END ALERT_LRR_SPE;
2571
2572 --
2573 -- Internal API for Alerting for Long Pending Requests General
2574 --
2575 PROCEDURE ALERT_LPR_GEN
2576 IS
2577 v_lng_pend_req_count fnd_oam_metval_vl.metric_value%TYPE;
2578 v_lng_pend_req_alert_enable varchar2(1):='';
2579 v_lng_pend_req_tolerance varchar2(10):='';
2580
2581 cursor req_id_list(p_tol varchar2) is
2582 select fcr.request_id request_id,
2583 fcp.user_concurrent_program_name user_concurrent_program_name
2584 from
2585 fnd_concurrent_requests fcr,
2586 fnd_conc_prog_onsite_info fcpoi,
2587 fnd_concurrent_programs_vl fcp
2588 where
2589 fcr.program_application_id=fcpoi.program_application_id
2590 AND fcp.application_id = fcr.PROGRAM_APPLICATION_ID
2591 AND fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
2592 and fcr.concurrent_program_id=fcpoi.concurrent_program_id
2593 and ((sysdate -fcr.REQUESTED_START_DATE)*86400 >
2594 (to_number(nvl(p_tol,0))*60))
2595 and fcr.phase_code='P'
2596 and fcr.status_code in ('I', 'Q');
2597
2598 -- Types for record of tables
2599 TYPE NumTabType IS TABLE OF NUMBER
2600 INDEX BY BINARY_INTEGER;
2601 TYPE VarcharTabType IS TABLE OF
2602 fnd_concurrent_programs_vl.user_concurrent_program_name%TYPE
2603 INDEX BY BINARY_INTEGER;
2604
2605 TYPE RecTabType IS RECORD
2606 (request_id NumTabType,
2607 display_name VarcharTabType);
2608
2609 v_req_list_rec RecTabType;
2610 v_attch_id number;
2611
2612 BEGIN
2613 select threshold_value,alert_enabled_flag
2614 into v_lng_pend_req_count,v_lng_pend_req_alert_enable
2615 from fnd_oam_metval
2616 where metric_short_name ='LONG_PENDING_REQ_COUNT';
2617
2618 select threshold_value
2619 into v_lng_pend_req_tolerance
2620 from fnd_oam_metval
2621 where metric_short_name ='LONG_PENDING_REQ_TOLERANCE';
2622
2623 if(v_lng_pend_req_alert_enable ='Y') then
2624 open req_id_list(v_lng_pend_req_tolerance);
2625 fetch req_id_list bulk collect
2626 into v_req_list_rec.request_id, v_req_list_rec.display_name;
2627 close req_id_list;
2628
2629 if (v_req_list_rec.request_id is not null and v_req_list_rec.request_id.count >= TO_NUMBER(v_lng_pend_req_count)) then
2630 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
2631 fnd_message.clear;
2632 fnd_message.set_name('FND','OAM_SYSAL_LONG_PENDING_REQ_GEN');
2633 fnd_message.set_token('COUNT',v_lng_pend_req_count);
2634 fnd_message.set_token('TOLERANCE',v_lng_pend_req_tolerance);
2635 v_attch_id := fnd_log.message_with_attachment(
2636 log_level=>fnd_log.level_unexpected,
2637 module=>MODULE||'.Alert_Long_Pending_Requests',
2638 pop_message=>false);
2639 fnd_log_attachment.writeln(
2640 pattachment_id => v_attch_id,
2641 pmessage => fnd_message.get);
2642 fnd_log_attachment.writeln(
2643 pattachment_id => v_attch_id,
2644 pmessage => ' ');
2645 for i in v_req_list_rec.request_id.FIRST..v_req_list_rec.request_id.LAST loop
2646 -- write info to attachment about each request.
2647 fnd_log_attachment.writeln(
2648 pattachment_id => v_attch_id,
2649 pmessage => v_req_list_rec.request_id(i) || ' - ' ||
2650 v_req_list_rec.display_name(i));
2651 end loop;
2652 fnd_log_attachment.close(
2653 pattachment_id => v_attch_id);
2654 end if;
2655 end if;
2656 end if; -- if(v_lng_pend_req_alert_enable ='Y') then
2657
2658 END ALERT_LPR_GEN;
2659
2660 --
2661 -- Internal API for Alerting for Long Pending Requests Specific
2662 --
2663 PROCEDURE ALERT_LPR_SPE
2664 IS
2665 v_spec_long_pend_enabled varchar2(1):='';
2666
2667 cursor spec_req_id_list is
2668 select fcr.request_id request_id,
2669 fcp.user_concurrent_program_name user_concurrent_program_name,
2670 nvl(fcpoi.ALERT_LONG_PENDING_TOLERANCE,0)/60 tolerance_minutes
2671 from
2672 fnd_concurrent_requests fcr,
2673 fnd_conc_prog_onsite_info fcpoi,
2674 fnd_concurrent_programs_vl fcp
2675 where
2676 fcr.program_application_id=fcpoi.program_application_id
2677 AND fcp.application_id = fcr.PROGRAM_APPLICATION_ID
2678 AND fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
2679 and fcr.concurrent_program_id=fcpoi.concurrent_program_id
2680 and (sysdate -fcr.REQUESTED_START_DATE)*86400 >
2681 (to_number(nvl(fcpoi.ALERT_LONG_PENDING_TOLERANCE,0)))
2682 and fcpoi.ALERT_LONG_PENDING_ENABLED='Y'
2683 and fcr.phase_code='P'
2684 and fcr.status_code in ('I', 'Q');
2685
2686 -- Types for record of tables
2687 TYPE NumTabType IS TABLE OF NUMBER
2688 INDEX BY BINARY_INTEGER;
2689 TYPE VarcharTabType IS TABLE OF
2690 fnd_concurrent_programs_vl.user_concurrent_program_name%TYPE
2691 INDEX BY BINARY_INTEGER;
2692
2693 TYPE SpecRecTabType IS RECORD
2694 (request_id NumTabType,
2695 display_name VarcharTabType,
2696 tolerance NumTabType);
2697
2698 v_spec_req_list_rec SpecRecTabType;
2699 v_spec_cnt number := 0;
2700 BEGIN
2701
2702 select threshold_value
2703 into v_spec_long_pend_enabled
2704 from fnd_oam_metval
2705 where metric_short_name ='SPECIFIC_LONG_PENDING_ENABLED';
2706
2707 if (v_spec_long_pend_enabled ='Y') then
2708 select count(*) into v_spec_cnt
2709 from fnd_conc_prog_onsite_info
2710 where ALERT_LONG_PENDING_ENABLED='Y' and rownum < 2;
2711
2712 if (v_spec_cnt > 0) then
2713 open spec_req_id_list;
2714 fetch spec_req_id_list bulk collect
2715 into
2716 v_spec_req_list_rec.request_id,
2717 v_spec_req_list_rec.display_name,
2718 v_spec_req_list_rec.tolerance;
2719 close spec_req_id_list;
2720
2721 if (v_spec_req_list_rec.request_id is not null and v_spec_req_list_rec.request_id.count > 0) then
2722 for i in v_spec_req_list_rec.request_id.FIRST..v_spec_req_list_rec.request_id.LAST loop
2723 if ((fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)) then
2724 fnd_message.clear;
2725 fnd_message.set_name('FND','OAM_SYSAL_LONG_PENDING_REQ_SPE');
2726 fnd_message.set_token('PROG', v_spec_req_list_rec.display_name(i));
2727 fnd_message.set_token('TOLERANCE', v_spec_req_list_rec.tolerance(i));
2728 fnd_log.proxy_alert_for_conc_req(
2729 module => MODULE||'.Alert_Long_Pending_Requests',
2730 pop_message => true,
2731 request_id => v_spec_req_list_rec.request_id(i));
2732 end if;
2733 end loop;
2734 end if;
2735 end if;
2736 end if;
2737
2738 END ALERT_LPR_SPE;
2739
2740 --
2741 -- Name
2742 -- Alert_Long_Running_Requests
2743 --
2744 -- Purpose
2745 -- The procedure will raise a consolidated alert if more than
2746 -- a user specified threshold
2747 -- number of concurrent requests are running for more than a
2748 -- user specified threshold offset
2749 -- period of time and for more than a user specified threshold
2750 -- tolerance percentage of their
2751 -- respective average runtimes. It also raise an alert for specified
2752 -- concurrent programs if it
2753 -- runs for more than the user specified threshold tolerance percentage
2754 -- of its user specific threshold offset period of time.
2755 --
2756 -- Input Arguments
2757 --
2758 -- None
2759 --
2760 -- Output Arguments
2761 --
2762 -- None
2763 --
2764 -- Notes:
2765 --
2766 --
2767 PROCEDURE Alert_Long_Running_Requests
2768 IS
2769 BEGIN
2770 --
2771 -- Check logging enabled in the first place. There's nothing to do
2772 -- if not enabled.
2773 --
2774 if (fnd_log.level_unexpected < fnd_log.g_current_runtime_level) then
2775 fnd_file.put_line(fnd_file.log, 'Logging Not Enabled.');
2776 return;
2777 end if;
2778
2779 -- General Alert
2780 ALERT_LRR_GEN;
2781 -- Specific Alert
2782 ALERT_LRR_SPE;
2783
2784 END Alert_Long_Running_Requests;
2785
2786
2787 --
2788 -- Name
2789 -- Alert_Long_Pending_Requests
2790 --
2791 -- Purpose
2792 --
2793 -- The procedure raises a consolidated alert if more than a user specified
2794 -- threshold number of concurrent requests are pending for more than a user
2795 -- specified threshold period of time after their requested start time.
2796 -- It also raise an alert for specified concurrent programs if it is
2797 -- pending for more than a user specified threshold period of time.
2798 --
2799 -- Input Arguments
2800 --
2801 -- None
2802 --
2803 -- Output Arguments
2804 --
2805 -- None
2806 --
2807 -- Notes:
2808 --
2809 --
2810 PROCEDURE Alert_Long_Pending_Requests
2811 IS
2812
2813 BEGIN
2814 --
2815 -- Check logging enabled in the first place. There's nothing to do
2816 -- if not enabled.
2817 --
2818 if (fnd_log.level_unexpected < fnd_log.g_current_runtime_level) then
2819 fnd_file.put_line(fnd_file.log, 'Logging Not Enabled.');
2820 return;
2821 end if;
2822
2823 -- General Alert
2824 ALERT_LPR_GEN;
2825 -- Specific Alert
2826 ALERT_LPR_SPE;
2827
2828 END Alert_Long_Pending_Requests;
2829
2830 END fnd_oam_collection;
[Home] [Help]