[Home] [Help]
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;
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:
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')
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
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
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
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;
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
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
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
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
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;
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;
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
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
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:
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
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,
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
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,
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
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
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,
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'
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;
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:
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
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
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);
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
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
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;
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
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
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(
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
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
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(
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
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
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
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'),
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
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
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
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
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:
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
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
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: --
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
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
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
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 (+)
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);
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);
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
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:
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
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
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);
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');
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
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
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);
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
2826: ALERT_LPR_SPE;
2827:
2828: END Alert_Long_Pending_Requests;
2829:
2830: END fnd_oam_collection;