Rem drv:
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/notification/notification_ui_pkgbody.sql /st_emcore_10.2.0.4.3db11.2.0.2/2 2010/07/14 22:33:03 bnam Exp $
Rem
Rem notification_ui_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2010, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem notification_ui_pkgbody.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem bnam 04/27/10 - backport bug#6976948; add SSL support
Rem xuli 03/13/09 - fix bug 8314208
Rem ssukavan 05/17/07 - ER 5883319 Repeat Notifications support
Rem rpatti 09/06/06 - XbranchMerge rpatti_bug-5503142 from main
Rem mgoswami 08/02/06 - Backport mgoswami_bug-5045550 from main
Rem mgoswami 02/27/06 - Backport bug 5061055
Rem neearora 12/12/05 - replaced hardcoding for JAVA_DEVICE_TYPE
Rem yaofeng 12/07/05 - java device details
Rem yaofeng 12/05/05 - java callback notif method
Rem yaofeng 09/07/05 - nvl on key_column
Rem yaofeng 08/01/05 - hidden key column
Rem yaofeng 07/13/05 - hidden column
Rem gsbhatia 07/01/05 - New repmgr header impl
Rem yaofeng 06/29/05 - hidden key column
Rem vmotamar 06/28/05 - Pref subtab enhancement
Rem yaofeng 06/06/05 - improve performance
Rem yaofeng 06/01/05 - tune sql
Rem yaofeng 05/31/05 - tune sql
Rem yaofeng 03/29/05 - search query
Rem yaofeng 05/17/05 - non thresholded alerts
Rem yaofeng 03/24/05 - show job patterns
Rem yaofeng 03/21/05 - target name
Rem yaofeng 03/18/05 - target name field
Rem yaofeng 03/16/05 - increase number
Rem yaofeng 03/14/05 - type related data
Rem dcawley 03/16/05 - Do not pass null
Rem yaofeng 03/10/05 - improve performance
Rem yaofeng 03/09/05 - 3458526
Rem yaofeng 03/02/05 - default schedule
Rem ancheng 02/18/05 - bug 2662095
Rem yaofeng 02/11/05 - multi task jobs
Rem yaofeng 02/11/05 - all targets deleted case
Rem yaofeng 01/28/05 - return num_keys
Rem yaofeng 01/26/05 - different key values
Rem yaofeng 01/14/05 - policy NLS
Rem yaofeng 01/10/05 - timezone again
Rem yaofeng 01/10/05 - timezone
Rem yaofeng 12/28/04 - assign rules
Rem yaofeng 12/09/04 - send email checkbox
Rem yaofeng 11/11/04 - add usage_type = 0
Rem yaofeng 10/28/04 - apply to all
Rem yaofeng 10/26/04 - group support
Rem yaofeng 10/21/04 - rca support
Rem dcawley 10/18/04 - Change api
Rem yaofeng 09/28/04 - fix wrong email owner
Rem yaofeng 09/24/04 - policy nls
Rem yaofeng 09/08/04 - back compatible
Rem yaofeng 09/02/04 - cleanup
Rem yaofeng 08/13/04 - notification rule changes
Rem dcawley 07/22/04 - Metric severities per metric
Rem dcawley 07/07/04 - Increase user name size
Rem dcawley 06/18/04 - Changes to add configuration
Rem yaofeng 11/24/03 - fix bug 3272366 multiple metrics
Rem yaofeng 11/11/03 - donnot use . *
Rem yaofeng 11/10/03 - fix bug 3243356 multiple rows
Rem yaofeng 10/13/03 - non-thresholdable alerts
Rem yaofeng 09/12/03 - fix bug 3129589 duplicate metrics
Rem yaofeng 09/05/03 - fix cursor leak
Rem yaofeng 08/25/03 - confirmation page for delete
Rem yaofeng 08/18/03 - fix bug 3093073
Rem yaofeng 07/24/03 - os command line arguments
Rem dcawley 05/27/03 - Change test apis
Rem yaofeng 04/28/03 - fix problem when config methods
Rem yaofeng 04/15/03 - pass target type info out for rules
Rem yaofeng 04/04/03 - get available metric indexes
Rem yaofeng 03/13/03 - add update by and time
Rem jpyang 04/03/03 - nls support
Rem yaofeng 02/26/03 - use preference emails for rule
Rem yaofeng 02/14/03 - get disabling period
Rem yaofeng 02/07/03 - email customization
Rem yaofeng 01/20/03 - populate schedules
Rem yaofeng 01/17/03 - notification schedule
Rem yaofeng 01/02/03 - pass key part values in
Rem yaofeng 12/20/02 - support metric indexes(key values)
Rem yaofeng 11/19/02 - support Metric Error
Rem dcawley 12/11/02 - Fix NO_TARGET_GUID
Rem tjaiswal 09/12/02 - User modify user of mgmt user package
Rem dcawley 09/02/02 - Move update_user_pref to mgmt_user package
Rem tjaiswal 08/15/02 - Do not use target guid of mgmt notify rule configs
Rem dcawley 08/08/02 - Use new type definitions
Rem skini 07/12/02 - Change in target_name column size
Rem tjaiswal 06/10/02 - Fix update_user_pref_ email
Rem tjaiswal 05/23/02 - Add target avail notif support
Rem tjaiswal 05/21/02 - tjaiswal_multiple_notif_devices
Rem tjaiswal 05/17/02 - Created
Rem
CREATE OR REPLACE PACKAGE BODY mgmt_preferences
AS
-- ============================================================================
-- a private function to convert a string to an array
-- ============================================================================
FUNCTION string_to_array(string_in IN VARCHAR2,
separator_in IN VARCHAR2)
RETURN SMP_EMD_STRING_ARRAY
IS
l_pos NUMBER := 0;
l_string VARCHAR2(2000) := NULL;
l_array SMP_EMD_STRING_ARRAY;
BEGIN
l_array := SMP_EMD_STRING_ARRAY();
l_string := string_in;
--Loop exits when there is no character left or l_string is null
WHILE (l_string IS NOT NULL AND length(l_string) > 0) LOOP
l_pos := INSTR(l_string, separator_in);
IF (l_pos = 0) -- separator is not found
THEN
l_array.extend();
l_array(l_array.COUNT) := l_string;
l_string := NULL;
RETURN l_array;
ELSE -- extract from l_string the string that are before the separator and assign what's left to l_string
l_array.extend();
l_array(l_array.COUNT) := SUBSTR(l_string, 1, l_pos-1);
l_string := SUBSTR(l_string, l_pos+length(separator_in));
END IF;
END LOOP;
RETURN l_array;
END string_to_array;
-- ============================================================================
-- a private procedure that verifies rule_metric_names_in has the same
-- length as rule_metric_indexes_in
-- ============================================================================
PROCEDURE check_rule_input_param(rule_metric_names_in IN SMP_EMD_STRING_ARRAY,
rule_metric_indexes_in IN SMP_EMD_NVPAIR_ARRAY)
IS
BEGIN
IF (rule_metric_names_in IS NOT null) THEN
IF (rule_metric_indexes_in IS null) THEN
RAISE MGMT_GLOBAL.invalid_rule_data;
END IF;
IF (rule_metric_names_in.count <> rule_metric_indexes_in.count) THEN
RAISE MGMT_GLOBAL.invalid_rule_data;
END IF;
END IF;
END check_rule_input_param;
-- ============================================================================
-- a private util function to determine if rule - rule_name_in of user
-- user_name_in exists
-- returns G_FALSE if the rule does not exist, G_TRUE if rule exists
-- ============================================================================
FUNCTION rule_exists
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2)
RETURN NUMBER
IS
l_rule_name VARCHAR2(256) := '';
BEGIN
SELECT rule_name INTO l_rule_name
FROM mgmt_notify_rules
WHERE rule_name=rule_name_in
AND owner=rule_owner_in
AND rownum=1;
l_rule_name := NVL(l_rule_name, '');
IF( length(trim(l_rule_name)) <> 0 )
THEN
RETURN MGMT_GLOBAL.G_TRUE;
END IF;
RETURN MGMT_GLOBAL.G_FALSE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN MGMT_GLOBAL.G_FALSE;
END rule_exists;
-- ============================================================================
-- A private utility function to find if a device exists
-- Note that this function only takes in the device name only
-- PURPOSE
-- Function to check if a device exists
-- Note, here a profile_name is not passed in
-- PARAMETERS
-- v_device_name - name of the device
-- v_type - type of the device
-- ============================================================================
FUNCTION notif_device_exists(v_device_name IN VARCHAR2)
RETURN NUMBER
IS
CURSOR dCursor IS
SELECT device_name
FROM mgmt_notify_devices
WHERE device_name=v_device_name;
BEGIN
FOR record IN dCursor
LOOP
IF( record.device_name IS NULL )
THEN
RETURN 0;
END IF;
IF( length(record.device_name) > 0 )
THEN
RETURN 1;
END IF;
EXIT;
END LOOP;
RETURN 0;
END notif_device_exists;
-- ============================================================================
-- a private util procedure to check if all targets were selected for a rule.
-- all targets are selected for a rule if the only target associated with the
-- rule is "%"
-- Returns G_TRUE if all targets were selected, G_FALSE otherwise
-- ============================================================================
FUNCTION all_targets_selected
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2)
RETURN NUMBER
IS
l_target_name MGMT_TARGETS.target_name%TYPE := '';
BEGIN
SELECT target_name INTO l_target_name
FROM mgmt_notify_rule_configs
WHERE rule_name=rule_name_in
AND owner=rule_owner_in
AND rownum=1;
l_target_name := NVL(l_target_name, '');
IF( l_target_name='%' )
THEN
RETURN MGMT_GLOBAL.G_TRUE;
END IF;
RETURN MGMT_GLOBAL.G_FALSE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN MGMT_GLOBAL.G_FALSE;
END all_targets_selected;
-- ============================================================================
-- a private util procedure to check if all metrics were selected for a rule.
-- all metrics are selected for a rule if the only metric name and metric column
-- associated with the rule is "%" and "%"
-- Returns G_TRUE if all metrics were selected, G_FALSE otherwise
-- ============================================================================
FUNCTION all_metrics_selected
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2)
RETURN NUMBER
IS
l_metric_name VARCHAR2(64) := '';
l_metric_column VARCHAR2(64) := '';
BEGIN
SELECT metric_name, metric_column INTO l_metric_name, l_metric_column
FROM mgmt_notify_rule_configs
WHERE rule_name=rule_name_in
AND owner=rule_owner_in
AND rownum=1;
l_metric_name := NVL(l_metric_name, '');
l_metric_column := NVL(l_metric_column, '');
IF( l_metric_name='%' AND l_metric_column='%' )
THEN
RETURN MGMT_GLOBAL.G_TRUE;
END IF;
RETURN MGMT_GLOBAL.G_FALSE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN MGMT_GLOBAL.G_FALSE;
END all_metrics_selected;
-- ============================================================================
-- a private util function to return the target_type of rule - rule_name_in
-- of owner - rule_owner_in
-- ============================================================================
FUNCTION get_rule_target_type
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2)
RETURN VARCHAR2
IS
l_target_type VARCHAR2(64) := '';
BEGIN
SELECT target_type INTO l_target_type
FROM mgmt_notify_rule_configs
WHERE rule_name=rule_name_in
AND owner=rule_owner_in
AND rownum=1;
l_target_type := NVL(l_target_type, '');
RETURN l_target_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
BEGIN
SELECT target_type INTO l_target_type
FROM mgmt_notify_job_rule_configs
WHERE rule_name=rule_name_in
AND owner=rule_owner_in
AND rownum=1;
l_target_type := NVL(l_target_type, '');
RETURN l_target_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN '';
END;
END get_rule_target_type;
-- ============================================================================
-- a private util function to check if the current user has super user
-- privilege.
-- throws insufficient_privileges exception
-- ============================================================================
PROCEDURE check_super_user_priv_error
IS
l_user_name VARCHAR2(256) := '';
l_priv_result NUMBER := 0;
BEGIN
-- first get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- now figure out if current user is a privileged user
l_priv_result := MGMT_USER.has_priv(l_user_name, 'SUPER_USER');
IF( l_priv_result = 0 )
THEN
-- this is not a super user
RAISE MGMT_GLOBAL.insufficient_privileges;
END IF;
END check_super_user_priv_error;
-- ============================================================================
-- a private util function to check if the rule - rule_name_in of rule_owner_in
-- exists
-- throws rule_does_not_exist exception
-- ============================================================================
PROCEDURE check_rule_exists_error( rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2 )
IS
l_rule_exists_val NUMBER(1) := MGMT_GLOBAL.G_FALSE;
BEGIN
-- figure out if rule - rule_name_in of user - rule_owner_in exists
l_rule_exists_val := rule_exists(rule_name_in, rule_owner_in);
IF(l_rule_exists_val=MGMT_GLOBAL.G_FALSE)
THEN
-- rule - rule_name_in for user - rule_owner_in does not exist, so throw
-- an exception
RAISE MGMT_GLOBAL.rule_does_not_exist;
END IF;
END check_rule_exists_error;
-- ============================================================================
-- a private util function to check if the device - device_name_in exists
-- throws device_does_not_exist exception
-- ============================================================================
PROCEDURE check_device_exists_error( device_name_in IN VARCHAR2 )
IS
l_device_exists_val NUMBER(1) := MGMT_GLOBAL.G_FALSE;
BEGIN
-- figure out if device - device_name exists
l_device_exists_val := notif_device_exists(device_name_in);
IF(l_device_exists_val=MGMT_GLOBAL.G_FALSE)
THEN
RAISE MGMT_GLOBAL.device_does_not_exist;
END IF;
END check_device_exists_error;
-- ============================================================================
-- a private util function to check if the device - device_name_in exists
-- for edit mode or if the device is duplicate for create mode
-- throws duplicate_device_name exception for create mode
-- throws device_does_not_exist exception for edit mode
-- ============================================================================
PROCEDURE check_device_error( device_name_in IN VARCHAR2,
mode_in IN NUMBER )
IS
l_device_exists_val NUMBER(1) := MGMT_GLOBAL.G_FALSE;
BEGIN
-- figure out if device - device_name_in exists
l_device_exists_val := notif_device_exists(device_name_in);
-- Note that in checking for duplicate device name for create mode, we
-- only check if device - device_name exists
-- we do not use the profile_name or type associated with the device. this
-- is because the os command, plsql and snmp devices are system wide devices
-- and are not associated with a specific user. also, the device names
-- need to be unique across the types - os command, plsql, snmp and email.
-- while the device is created, the current user's name is used
-- for the profile_name column. any subsequent edit of the device will only
-- edit the actual device contents name and not the profile_name column for the
-- device
IF( mode_in=CREATE_MODE )
THEN
-- create mode
-- check if device name is duplicate
IF( l_device_exists_val=MGMT_GLOBAL.G_TRUE )
THEN
RAISE MGMT_GLOBAL.duplicate_device_name;
END IF;
ELSE
-- edit mode
-- check if device exists
IF(l_device_exists_val=MGMT_GLOBAL.G_FALSE)
THEN
RAISE MGMT_GLOBAL.device_does_not_exist;
END IF;
END IF;
END check_device_error;
-- ============================================================================
-- a private util function to get the list of rules associated with device -
-- device_name_in of type device_type_in
-- note no error handling is done here ...
-- ============================================================================
PROCEDURE get_rules_list_for_device( device_name_in IN VARCHAR2,
device_type_in IN NUMBER,
device_rules_list_out OUT CURSOR_TYPE )
IS
BEGIN
-- get the list of rules associated with this device
OPEN device_rules_list_out FOR
SELECT notifyees.rule_name,
notifyees.owner,
notifyees.profile_name
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.device_name = device_name_in
AND notifyees.profile_name = devices.profile_name
AND notifyees.device_name = devices.device_name
AND devices.type = device_type_in;
END get_rules_list_for_device;
-- ============================================================================
-- a private util function to delete the device - device_name_in of type
-- device_type_in. use this util procedure only to delete: os command, plsql
-- or snmp devices.
-- note no error handling is done here ...
-- ============================================================================
PROCEDURE delete_device( device_name_in IN VARCHAR2,
device_type_in IN NUMBER )
IS
BEGIN
-- delete the device
-- note that since the device names for os command, plsql, snmp devices are
-- unique across these types, the profile name of the device is therefore
-- not neccessary. the device name and the device type suffice for the device
-- device deletion
DELETE
FROM mgmt_notify_devices
WHERE device_name = device_name_in
AND type = device_type_in;
END delete_device;
-- ============================================================================
-- a private util function to get the detail data for the program device -
-- device_name_in
-- note no error handling is done here ...
-- ============================================================================
PROCEDURE get_program_device_detail_data( device_name_in IN VARCHAR2,
device_data_out OUT CURSOR_TYPE )
IS
BEGIN
-- get the program device detail data
OPEN device_data_out FOR
SELECT device_name,
device_description,
type,
profile_name,
program
FROM mgmt_notify_devices
WHERE device_name = device_name_in
AND type = EMD_NOTIFICATION.PROGRAM_DEVICE_TYPE;
END get_program_device_detail_data;
-- ============================================================================
-- a private util function to get the detail data for the plsql device -
-- device_name_in
-- note no error handling is done here ...
-- ============================================================================
PROCEDURE get_plsql_device_detail_data( device_name_in IN VARCHAR2,
device_data_out OUT CURSOR_TYPE )
IS
BEGIN
-- get the plsql device detail data
OPEN device_data_out FOR
SELECT device_name,
device_description,
type,
profile_name,
program
FROM mgmt_notify_devices
WHERE device_name = device_name_in
AND type = EMD_NOTIFICATION.PLSQL_DEVICE_TYPE;
END get_plsql_device_detail_data;
-- ============================================================================
-- a private util function to get the detail data for the snmp device -
-- device_name_in
-- note no error handling is done here ...
-- ============================================================================
PROCEDURE get_snmp_device_detail_data( device_name_in IN VARCHAR2,
device_data_out OUT CURSOR_TYPE )
IS
BEGIN
-- get the snmp device detail data
OPEN device_data_out FOR
SELECT device_name,
device_description,
type,
profile_name,
snmp_host,
snmp_port,
snmp_community
FROM mgmt_notify_devices
WHERE device_name = device_name_in
AND type = EMD_NOTIFICATION.SNMP_DEVICE_TYPE;
END get_snmp_device_detail_data;
-- ============================================================================
-- a private util function to get the list of email gateways
-- note no error handling is done here ...
-- ============================================================================
PROCEDURE get_mail_gateways( mail_gateway_list_out OUT CURSOR_TYPE )
IS
BEGIN
-- get the mail gateway data ...
OPEN mail_gateway_list_out FOR
SELECT mail_host,
NVL(smtp_user, '') "smtp_user",
NVL(DECRYPT(smtp_pwd), '') "smtp_pwd",
NVL(smtp_port,'') "smtp_port",
NVL(email_address, '') "email_address",
nvl(email_name, '') "email_name",
nvl(smtp_ssl, 'NONE') "smtp_ssl",
precedence
FROM mgmt_notify_email_gateway
ORDER BY precedence ASC;
END get_mail_gateways;
-- ============================================================================
-- a private util function to update the email gateway data
-- note no error handling is done here ...
-- ============================================================================
PROCEDURE update_mail_gateways( mail_gateway_server_array IN SMP_EMD_STRING_ARRAY,
mail_username_in IN VARCHAR2,
mail_password_in IN VARCHAR2,
mail_address_in IN VARCHAR2,
mail_name_in IN VARCHAR2,
mail_ssl_in IN VARCHAR2)
IS
mail_smtpport NUMBER DEFAULT NULL;
port_number NUMBER DEFAULT 0;
mail_gateway_server VARCHAR2 (256);
BEGIN
-- set the mail gateway data ...
-- first delete the existing gateways
DELETE mgmt_notify_email_gateway;
-- now add the mail gateway servers data
FOR i in 1..mail_gateway_server_array.COUNT
LOOP
--pick up the portNumber if specified in gateway
port_number := InStr(mail_gateway_server_array(i),':');
IF (port_number != 0 )
THEN
mail_smtpport := SubStr(mail_gateway_server_array(i),port_number+1);
mail_gateway_server := SubStr(mail_gateway_server_array(i),1,port_number-1);
--portNumber not specified in text field
ELSE
mail_gateway_server := mail_gateway_server_array(i);
mail_smtpport := NULL;
END IF;
INSERT
INTO mgmt_notify_email_gateway(MAIL_HOST,
SMTP_USER,
SMTP_PWD,
EMAIL_ADDRESS,
EMAIL_NAME,
SMTP_PORT,
PRECEDENCE,
SMTP_SSL)
VALUES(mail_gateway_server,
mail_username_in,
ENCRYPT(mail_password_in),
mail_address_in,
mail_name_in,
mail_smtpport,
i,
mail_ssl_in);
END LOOP;
END update_mail_gateways;
-- ============================================================================
-- a private util function to get a list of all devices(os command, plsql, snmp)
-- note no error handling is done here ...
-- ============================================================================
PROCEDURE get_devices_list( notif_devices_list_out OUT CURSOR_TYPE )
IS
BEGIN
-- get the notif devices data ...
OPEN notif_devices_list_out FOR
SELECT device_name,
device_description,
profile_name,
type,
DECODE( type, EMD_NOTIFICATION.PROGRAM_DEVICE_TYPE, 'OS COMMAND',
EMD_NOTIFICATION.SNMP_DEVICE_TYPE, 'SNMP',
EMD_NOTIFICATION.PLSQL_DEVICE_TYPE, 'PLSQL',
EMD_NOTIFICATION.JAVA_DEVICE_TYPE, 'JAVA' )
device_type
FROM mgmt_notify_devices
WHERE type != EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE
AND type != EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE
ORDER BY device_type ASC;
END get_devices_list;
-- ============================================================================
-- a private util function to get a list of all devices(os command, plsql, snmp)
-- that are associated with rule - rule_name_in of user - rule_owner_in
-- note no error handling is done here ...
-- ============================================================================
PROCEDURE get_devices_list_for_rule( rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2,
rules_device_notif_list_out OUT CURSOR_TYPE )
IS
BEGIN
-- get the notif devices data for rule - rule_name_in of user - rule_owner_in
OPEN rules_device_notif_list_out FOR
SELECT notifyees.device_name,
devices.device_description,
devices.type
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.rule_name = rule_name_in
AND notifyees.owner = rule_owner_in
AND notifyees.device_name = devices.device_name
AND notifyees.profile_name = devices.profile_name
AND devices.type != EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE
AND devices.type != EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE;
END get_devices_list_for_rule;
-- ============================================================================
-- a private util function to get a list of all email notifyees for
-- rule - rule_name_in of user - rule_owner_in
-- note no error handling is done here ...
-- ============================================================================
PROCEDURE get_rule_email_notifyees( rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2,
rules_email_notif_list_out OUT CURSOR_TYPE )
IS
BEGIN
-- get the list of all email notifyees
OPEN rules_email_notif_list_out FOR
SELECT notifyees.profile_name "email_notifyee",
devices.email_address
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.rule_name = rule_name_in
AND notifyees.owner = rule_owner_in
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND notifyees.profile_name = devices.profile_name
AND notifyees.device_name = devices.device_name
AND devices.device_name = devices.email_address
ORDER BY notifyees.profile_name;
END get_rule_email_notifyees;
-- ============================================================================
-- a private util procedure to check for the errors in notif rule creation
-- or updating
-- create_mode_in: CREATE_MODE for create, EDIT_MODE for edit
-- ============================================================================
PROCEDURE check_notif_rule_errors
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2,
mode_in IN NUMBER)
IS
l_user_name VARCHAR2(256) := '';
l_rule_exists_val NUMBER(1) := MGMT_GLOBAL.G_FALSE;
BEGIN
-- for create mode, the following exceptions will be thrown:
-- duplicate_rule_name
-- for edit mode, the following exceptions will be thrown:
-- rule_does_not_exist
-- user_does_not_exist - note: this check will be made in
-- update_notification_rule
-- get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
IF( mode_in=CREATE_MODE )
THEN
-- create mode
-- check if rule is duplicate
l_rule_exists_val := rule_exists(rule_name_in, l_user_name);
IF( l_rule_exists_val=MGMT_GLOBAL.G_TRUE )
THEN
RAISE MGMT_GLOBAL.duplicate_rule_name;
END IF;
ELSE
-- edit mode
-- check if rule - rule_name_in exists for owner - rule_owner_in
-- now figure out if rule - rule_name_in of user - l_user_name exists
l_rule_exists_val := rule_exists(rule_name_in, rule_owner_in);
IF(l_rule_exists_val=MGMT_GLOBAL.G_FALSE)
THEN
-- rule - rule_name_in for user - rule_owner_in does not exist, so throw
-- an exception
RAISE MGMT_GLOBAL.rule_does_not_exist;
END IF;
END IF;
END check_notif_rule_errors;
-- ============================================================================
-- a private util procedure to create or edit a notification rule with
-- the passed in data
-- mode_in: CREATE_MODE for create, EDIT_MODE for edit
-- ============================================================================
PROCEDURE create_edit_notif_rule
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2,
rule_desc_in IN VARCHAR2,
rule_public_in IN NUMBER,
rule_target_type_in IN VARCHAR2,
rule_targets_in IN SMP_EMD_STRING_ARRAY,
target_types_in IN SMP_EMD_STRING_ARRAY DEFAULT NULL,
want_tgt_up_in IN NUMBER,
want_tgt_down_in IN NUMBER,
want_tgt_unreachable_start_in IN NUMBER,
want_tgt_unreachable_end_in IN NUMBER,
want_tgt_metric_err_start_in IN NUMBER,
want_tgt_metric_err_end_in IN NUMBER,
want_tgt_blkout_started_in IN NUMBER,
want_tgt_blkout_ended_in IN NUMBER,
ignore_rca_in IN NUMBER,
rule_metric_names_in IN SMP_EMD_STRING_ARRAY,
rule_metric_columns_in IN SMP_EMD_STRING_ARRAY,
rule_metric_indexes_in IN SMP_EMD_NVPAIR_ARRAY,
metric_want_clears_in IN SMP_EMD_INTEGER_ARRAY,
metric_want_critical_alerts_in IN SMP_EMD_INTEGER_ARRAY,
metric_want_warning_alerts_in IN SMP_EMD_INTEGER_ARRAY,
want_warning_succeed_in IN SMP_EMD_INTEGER_ARRAY,
want_warning_problem_in IN SMP_EMD_INTEGER_ARRAY,
want_critical_succeed_in IN SMP_EMD_INTEGER_ARRAY,
want_critical_problem_in IN SMP_EMD_INTEGER_ARRAY,
policy_names_in IN SMP_EMD_STRING_ARRAY,
want_policy_violation_in IN SMP_EMD_INTEGER_ARRAY,
want_policy_clear_in IN SMP_EMD_INTEGER_ARRAY,
want_policy_job_succeed_in IN SMP_EMD_INTEGER_ARRAY,
want_policy_job_problem_in IN SMP_EMD_INTEGER_ARRAY,
job_names_in IN SMP_EMD_STRING_ARRAY,
job_owner_in IN SMP_EMD_STRING_ARRAY,
job_types_in IN SMP_EMD_STRING_ARRAY,
want_job_scheduled_in IN SMP_EMD_INTEGER_ARRAY,
want_job_started_in IN SMP_EMD_INTEGER_ARRAY,
want_job_suspended_in IN SMP_EMD_INTEGER_ARRAY,
want_job_succeeded_in IN SMP_EMD_INTEGER_ARRAY,
want_job_problem_in IN SMP_EMD_INTEGER_ARRAY,
send_email_in IN NUMBER,
device_names_in IN SMP_EMD_STRING_ARRAY,
device_types_in IN SMP_EMD_INTEGER_ARRAY,
device_not_avail_list_out OUT SMP_EMD_STRING_ARRAY,
mode_in IN NUMBER,
rule_repeat_enabled_in IN NUMBER DEFAULT 0)
IS
l_user_name VARCHAR2(256) := '';
l_device_exists NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_device_owner VARCHAR2(256) := '';
l_current_index NUMBER := 0;
l_key_part_values SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
l_key_value_str VARCHAR2(2000) := ''; -- SMP_EMD_NVPAIR.value%TYPE;
l_target_guid MGMT_TARGETS.target_guid%TYPE;
l_comp_target_guid MGMT_TARGETS.target_guid%TYPE;
l_target_name MGMT_TARGETS.target_name%TYPE;
l_pos NUMBER := 0;
l_email_address SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
l_key_value_array SMP_EMD_STRING_ARRAY;
CURSOR c_preferred_email_address (current_user VARCHAR2) IS
SELECT devices.email_address
FROM mgmt_notify_devices devices
WHERE devices.profile_name = current_user
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address;
BEGIN
-- Check the correctness of the input parameters
-- it's assumed that rule_metric_names_in and rule_metric_indexes_in has the same length. Verify this condition holds.
check_rule_input_param(rule_metric_names_in,rule_metric_indexes_in);
-- initialize OUT parameters
device_not_avail_list_out := SMP_EMD_STRING_ARRAY();
-- first get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
IF( mode_in=CREATE_MODE )
THEN
-- create the rule - rule_name_in for user - rule_owner_in
EMD_NOTIFICATION.ADD_NOTIFICATION_RULE( rule_name_in,
rule_owner_in,
rule_desc_in,
rule_public_in,
rule_repeat_enabled_in );
END IF;
-- set the rule properties
EMD_NOTIFICATION.SET_RULE_PROPERTIES( rule_name_in,
rule_owner_in,
rule_desc_in,
rule_public_in,
rule_repeat_enabled_in );
IF( mode_in=EDIT_MODE )
THEN
-- clear of the data in mgmt_notify_rule_configs for rule - rule_name_in
-- of owner - rule_owner_in
EMD_NOTIFICATION.DELETE_RULE_CONFIGURATIONS( rule_name_in,
rule_owner_in );
END IF;
l_key_part_values := SMP_EMD_STRING_ARRAY();
l_key_part_values.extend(5);
-- set the rule config data
FOR i in 1..rule_targets_in.COUNT
LOOP
-- NOTE: for simple target:
-- target_name is target_name;
-- target_type is the simple target type;
-- target_guid is NULL;
-- for composite target:
-- target_name is ' ';
-- target_type is the simple target type (not the composite target type);
-- target_guid is the composite target guid;
-- get composite target guid
l_target_name := rule_targets_in(i);
l_comp_target_guid := NULL;
IF (target_types_in IS NOT NULL AND
target_types_in(i) <> rule_target_type_in)
THEN
l_target_name := ' ';
l_comp_target_guid := mgmt_target.get_target_guid(
rule_targets_in(i),
target_types_in(i));
END IF;
FOR j in 1..rule_metric_names_in.COUNT
LOOP
BEGIN
-- rule_metric_names_in and rule_metric_indexes_in are same size
l_key_value_array := string_to_array(rule_metric_indexes_in(j).value, ',');
EXCEPTION
WHEN SUBSCRIPT_BEYOND_COUNT
THEN
-- it's assumed that rule_metric_names_in and rule_metric_indexes_in has the same length. Verify this condition holds.
RAISE MGMT_GLOBAL.invalid_rule_data;
END;
FOR m in 1..l_key_value_array.COUNT
LOOP
l_key_value_str := l_key_value_array(m);
l_pos := INSTR(l_key_value_str, ';');
IF (l_pos = 0)
THEN
-- NOT composite key values; simply add the key value
EMD_NOTIFICATION.ADD_CONFIGURATION_TO_RULE
( rule_name_in, rule_owner_in, rule_target_type_in,
l_target_name, l_comp_target_guid, rule_metric_names_in(j),
rule_metric_columns_in(j), l_key_value_str,
0, NULL, ' ', ' ', ' ', ' ', ' ',
NVL(metric_want_clears_in(j), MGMT_GLOBAL.G_FALSE),
NVL(metric_want_warning_alerts_in(j), MGMT_GLOBAL.G_FALSE),
NVL(metric_want_critical_alerts_in(j), MGMT_GLOBAL.G_FALSE),
want_tgt_up_in, want_tgt_down_in,
want_tgt_unreachable_start_in, want_tgt_unreachable_end_in,
want_tgt_metric_err_start_in, want_tgt_metric_err_end_in,
want_tgt_blkout_started_in, want_tgt_blkout_ended_in,
MGMT_GLOBAL.G_FALSE, MGMT_GLOBAL.G_FALSE,
NVL(want_warning_succeed_in(j), MGMT_GLOBAL.G_FALSE),
NVL(want_warning_problem_in(j), MGMT_GLOBAL.G_FALSE),
NVL(want_critical_succeed_in(j), MGMT_GLOBAL.G_FALSE),
NVL(want_critical_problem_in(j), MGMT_GLOBAL.G_FALSE),
MGMT_GLOBAL.G_FALSE, MGMT_GLOBAL.G_FALSE,
NVL(ignore_rca_in, MGMT_GLOBAL.G_FALSE)
);
ELSE
-- composite key values;
-- add the composite key guid and pass in all key part values
-- get the target_guid first
IF (rule_targets_in(i) = '%')
THEN
-- "All Targets" case, simply pick up a target_guid
SELECT target_guid
INTO l_target_guid
FROM mgmt_targets
WHERE target_type = rule_target_type_in
AND rownum = 1;
ELSE
-- a specific target, get its target_guid
l_target_guid := mgmt_target.get_target_guid(rule_targets_in(i),
rule_target_type_in);
END IF;
FOR m in 1..l_key_part_values.COUNT
LOOP
IF (l_key_value_str is not null)
THEN
l_pos := INSTR(l_key_value_str, ';');
IF (l_pos = 0)
THEN
l_key_part_values(m) := l_key_value_str;
l_key_value_str := null;
ELSE
l_key_part_values(m) := SUBSTR(l_key_value_str, 1, l_pos-1);
l_key_value_str := SUBSTR(l_key_value_str, l_pos+1);
END IF;
ELSE
l_key_part_values(m) := null;
END IF;
END LOOP;
EMD_NOTIFICATION.ADD_CONFIGURATION_TO_RULE
( rule_name_in, rule_owner_in, rule_target_type_in,
l_target_name, l_comp_target_guid, rule_metric_names_in(j),
rule_metric_columns_in(j),
mgmt_global.get_composite_key_guid(l_key_part_values),
1, l_target_guid, l_key_part_values(1),
l_key_part_values(2), l_key_part_values(3),
l_key_part_values(4), l_key_part_values(5),
NVL(metric_want_clears_in(j), MGMT_GLOBAL.G_FALSE),
NVL(metric_want_warning_alerts_in(j), MGMT_GLOBAL.G_FALSE),
NVL(metric_want_critical_alerts_in(j), MGMT_GLOBAL.G_FALSE),
want_tgt_up_in, want_tgt_down_in,
want_tgt_unreachable_start_in, want_tgt_unreachable_end_in,
want_tgt_metric_err_start_in, want_tgt_metric_err_end_in,
want_tgt_blkout_started_in, want_tgt_blkout_ended_in,
MGMT_GLOBAL.G_FALSE, MGMT_GLOBAL.G_FALSE,
NVL(want_warning_succeed_in(j), MGMT_GLOBAL.G_FALSE),
NVL(want_warning_problem_in(j), MGMT_GLOBAL.G_FALSE),
NVL(want_critical_succeed_in(j), MGMT_GLOBAL.G_FALSE),
NVL(want_critical_problem_in(j), MGMT_GLOBAL.G_FALSE),
MGMT_GLOBAL.G_FALSE, MGMT_GLOBAL.G_FALSE,
NVL(ignore_rca_in, MGMT_GLOBAL.G_FALSE)
);
END IF;
END LOOP;
END LOOP;
-- add policies
FOR j in 1..policy_names_in.COUNT
LOOP
EMD_NOTIFICATION.ADD_CONFIGURATION_TO_RULE
( rule_name_in, rule_owner_in, rule_target_type_in,
l_target_name, l_comp_target_guid,
policy_names_in(j), '%', '%',
0, NULL, NULL, NULL, NULL, NULL, NULL,
MGMT_GLOBAL.G_FALSE,
MGMT_GLOBAL.G_FALSE,
MGMT_GLOBAL.G_FALSE,
want_tgt_up_in, want_tgt_down_in,
want_tgt_unreachable_start_in, want_tgt_unreachable_end_in,
want_tgt_metric_err_start_in, want_tgt_metric_err_end_in,
want_tgt_blkout_started_in, want_tgt_blkout_ended_in,
NVL(want_policy_clear_in(j), MGMT_GLOBAL.G_FALSE),
NVL(want_policy_violation_in(j), MGMT_GLOBAL.G_FALSE),
MGMT_GLOBAL.G_FALSE, MGMT_GLOBAL.G_FALSE,
MGMT_GLOBAL.G_FALSE, MGMT_GLOBAL.G_FALSE,
NVL(want_policy_job_succeed_in(j), MGMT_GLOBAL.G_FALSE),
NVL(want_policy_job_problem_in(j), MGMT_GLOBAL.G_FALSE),
NVL(ignore_rca_in, MGMT_GLOBAL.G_FALSE)
);
END LOOP;
-- add jobs
FOR j in 1..job_names_in.COUNT
LOOP
EMD_NOTIFICATION.ADD_CONFIGURATION_TO_JOB_RULE
( rule_name_in, rule_owner_in,
rule_target_type_in, l_target_name, l_comp_target_guid,
job_names_in(j),
job_owner_in(j),
job_types_in(j),
NVL(want_job_scheduled_in(j), MGMT_GLOBAL.G_FALSE),
NVL(want_job_started_in(j), MGMT_GLOBAL.G_FALSE),
NVL(want_job_succeeded_in(j), MGMT_GLOBAL.G_FALSE),
NVL(want_job_suspended_in(j), MGMT_GLOBAL.G_FALSE),
NVL(want_job_problem_in(j), MGMT_GLOBAL.G_FALSE)
);
END LOOP;
END LOOP;
-- get the preffered email addresses
OPEN c_preferred_email_address (l_user_name);
FETCH c_preferred_email_address
BULK COLLECT INTO l_email_address;
CLOSE c_preferred_email_address;
-- update the send email data for rule - rule_name_in of user - rule_owner_in
IF( send_email_in=MGMT_GLOBAL.G_TRUE )
THEN
-- add these email devices to the rule
FOR i IN 1..l_email_address.COUNT
LOOP
BEGIN
-- add the device to the rule - rule_name_in of user - rule_owner_in
emd_notification.add_device_to_rule( rule_name_in,
rule_owner_in,
l_email_address(i),
l_user_name );
EXCEPTION
-- ignore duplicate rows (could happen when editing a rule)
WHEN DUP_VAL_ON_INDEX THEN NULL;
END;
END LOOP;
ELSE
-- delete these email devices from the rule
FOR i IN 1..l_email_address.COUNT
LOOP
-- delete the device from the rule - rule_name_in of user - rule_owner_in
emd_notification.delete_device_from_rule( rule_name_in,
rule_owner_in,
l_email_address(i),
l_user_name );
END LOOP;
END IF;
-- IMPORTANT:
-- before devices(os command, snmp, plsql) can be added to rule
-- rule_name_in of user - rule_owner_in, first delete all such devices
-- from mgmt_notify_notifyees which are associated with this rule
IF( mode_in=EDIT_MODE )
THEN
-- delete all the devices(os command, snmp, plsql) which are currently
-- associated with this rule
-- TODO: this will have to change to use APIs from EmdNotification.sql once
-- they are available
DELETE
FROM mgmt_notify_notifyees
WHERE rule_name = rule_name_in
AND owner = rule_owner_in
AND device_name IN ( SELECT notifyees.device_name
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.rule_name = rule_name_in
AND notifyees.owner = rule_owner_in
AND notifyees.device_name = devices.device_name
AND notifyees.profile_name = devices.profile_name
AND devices.type != EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE
AND devices.type != EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE );
END IF;
-- now add the devices from device_names_in to the rule - rule_name_in of
-- user - rule_owner_in
-- TODO: this will have to change to use APIs from EmdNotification.sql once
-- they are available
FOR i in 1..device_names_in.COUNT
LOOP
-- first figure out if the device exists
l_device_exists := notif_device_exists( device_names_in(i) );
IF( l_device_exists=MGMT_GLOBAL.G_FALSE )
THEN
-- if the device does not exist, add the device name to
-- device_not_avail_list_out
device_not_avail_list_out.extend(1);
l_current_index := l_current_index + 1;
device_not_avail_list_out(l_current_index) := device_names_in(i);
ELSE
-- if the device exists, add the device to the rule - rule_name_in of user
-- rule_owner_in
-- first get the device owner for device - device_names_in(i)
l_device_owner := EMD_NOTIFICATION.GET_DEVICE_OWNER( device_names_in(i),
device_types_in(i) );
EMD_NOTIFICATION.ADD_DEVICE_TO_RULE( rule_name_in,
rule_owner_in,
device_names_in(i),
l_device_owner );
END IF;
END LOOP;
END create_edit_notif_rule;
-- ============================================================================
-- a private utility function to return the rule config devices data
-- Note that rule_email_out and user_pref_email_out cursors will be
-- determined in all case. However, rule_devices_out and all_devices_out
-- cursors will be determined only in the following conditions:
-- (current user = rule_owner_in) OR
-- (current user != rule_owner_in AND current user is privileged)
-- ============================================================================
PROCEDURE get_rule_config_dev_data
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2,
rule_email_out OUT CURSOR_TYPE,
user_pref_email_out OUT CURSOR_TYPE,
rule_devices_out OUT CURSOR_TYPE,
all_devices_out OUT CURSOR_TYPE)
IS
l_user_name VARCHAR2(256) := '';
l_priv_result NUMBER := 0;
BEGIN
-- initialize OUT parameters
-- get the current user name
l_user_name := mgmt_user.get_current_em_user();
-- figure out if current user is a privileged user
l_priv_result := MGMT_USER.has_priv(l_user_name, 'SUPER_USER');
-- get the data for cursor - rule_email_out
-- always get the list of emails of user l_user_name that are associated with
-- rule rule_name_in of user rule_owner_in
OPEN rule_email_out FOR
SELECT devices.email_address
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.rule_name = rule_name_in
AND notifyees.owner = rule_owner_in
AND notifyees.profile_name = l_user_name
AND devices.type IN ( EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE )
AND devices.device_name = devices.email_address
AND devices.profile_name = notifyees.profile_name
AND devices.device_name = notifyees.device_name;
-- get the data for cursor - user_pref_email_out
-- get the list of user pref email for the current user - l_user_name
get_user_pref_email( user_pref_email_out );
-- get the data for cursor - rule_devices_out and all_devices_out
-- get this data only if:
-- (l_user_name = rule_owner_in) OR
-- (l_user_name != rule_owner_in AND l_user_name is privileged)
IF( (l_user_name = rule_owner_in) OR
(l_user_name != rule_owner_in AND l_priv_result = 1 ) )
THEN
-- get the data for cursor - rule_devices_out
get_devices_list_for_rule( rule_name_in,
rule_owner_in,
rule_devices_out );
-- get the data for cursor - all_devices_out
get_devices_list( all_devices_out );
ELSE
-- create dummy cursors for rule_devices_out and all_devices_out
OPEN rule_devices_out FOR
SELECT dummy "device_name",
dummy "device_description",
dummy "type"
FROM dual
WHERE rownum < 1;
OPEN all_devices_out FOR
SELECT dummy "device_name",
dummy "device_description",
dummy "profile_name",
dummy "type",
dummy "device_type"
FROM dual
WHERE rownum < 1;
END IF;
END get_rule_config_dev_data;
-- ============================================================================
-- a private util procedure to update the rule devices data for rule -
-- rule_name_in of owner - rule_owner_in
--
-- Note that device_names_in and device_types_in cursors will be
-- used only in the following conditions:
-- (current user = rule_owner_in) OR
-- (current user != rule_owner_in AND current user is privileged)
-- ============================================================================
PROCEDURE update_rule_dev_data
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2,
user_name_in IN VARCHAR2,
send_email_in IN NUMBER,
device_names_in IN SMP_EMD_STRING_ARRAY,
device_types_in IN SMP_EMD_INTEGER_ARRAY,
device_not_avail_list_out OUT SMP_EMD_STRING_ARRAY)
IS
l_user_name VARCHAR2(256) := '';
l_device_exists NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_device_owner VARCHAR2(256) := '';
l_current_index NUMBER := 0;
l_priv_result NUMBER := 0;
l_email_address SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
CURSOR c_preferred_email_address IS
SELECT devices.email_address
FROM mgmt_notify_devices devices
WHERE devices.profile_name = user_name_in
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address;
BEGIN
-- initialize OUT parameters
device_not_avail_list_out := SMP_EMD_STRING_ARRAY();
-- first get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- figure out if current user is a privileged user
l_priv_result := MGMT_USER.has_priv(l_user_name, 'SUPER_USER');
-- get the preffered email addresses
OPEN c_preferred_email_address;
FETCH c_preferred_email_address
BULK COLLECT INTO l_email_address;
CLOSE c_preferred_email_address;
-- update the send email data for rule - rule_name_in of user - rule_owner_in
IF( send_email_in=MGMT_GLOBAL.G_TRUE )
THEN
-- add these email devices to the rule
FOR i IN 1..l_email_address.COUNT
LOOP
BEGIN
-- add the device to the rule - rule_name_in of user - rule_owner_in
emd_notification.add_device_to_rule( rule_name_in,
rule_owner_in,
l_email_address(i),
user_name_in );
EXCEPTION
-- ignore duplicate rows (could happen when editing a rule)
WHEN DUP_VAL_ON_INDEX THEN NULL;
END;
END LOOP;
ELSE
-- delete these email devices from the rule
FOR i IN 1..l_email_address.COUNT
LOOP
-- add the device to the rule - rule_name_in of user - rule_owner_in
emd_notification.delete_device_from_rule( rule_name_in,
rule_owner_in,
l_email_address(i),
user_name_in );
END LOOP;
END IF;
-- IMPORTANT:
-- before devices(os command, snmp, plsql) can be added to rule
-- rule_name_in of user - rule_owner_in, first delete all such devices
-- from mgmt_notify_notifyees which are associated with this rule
-- also do this only if:
-- (l_user_name = rule_owner_in) OR
-- (l_user_name != rule_owner_in AND l_user_name is privileged)
IF( (l_user_name = rule_owner_in) OR
(l_user_name != rule_owner_in AND l_priv_result = 1 ) )
THEN
-- first, delete all the devices(os command, snmp, plsql) which are currently
-- associated with this rule
-- TODO: this will have to change to use APIs from EmdNotification.sql once
-- they are available
DELETE
FROM mgmt_notify_notifyees
WHERE rule_name = rule_name_in
AND owner = rule_owner_in
AND device_name IN ( SELECT notifyees.device_name
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.rule_name = rule_name_in
AND notifyees.owner = rule_owner_in
AND notifyees.device_name = devices.device_name
AND notifyees.profile_name = devices.profile_name
AND devices.type != EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE
AND devices.type != EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE );
-- now add the devices from device_names_in to the rule - rule_name_in of
-- user - rule_owner_in
-- TODO: this will have to change to use APIs from EmdNotification.sql once
-- they are available
FOR i in 1..device_names_in.COUNT
LOOP
-- first figure out if the device exists
l_device_exists := notif_device_exists( device_names_in(i) );
IF( l_device_exists=MGMT_GLOBAL.G_FALSE )
THEN
-- if the device does not exist, add the device name to
-- device_not_avail_list_out
device_not_avail_list_out.extend(1);
l_current_index := l_current_index + 1;
device_not_avail_list_out(l_current_index) := device_names_in(i);
ELSE
-- if the device exists, add the device to the rule - rule_name_in of user
-- rule_owner_in
-- first get the device owner for device - device_names_in(i)
l_device_owner := EMD_NOTIFICATION.GET_DEVICE_OWNER( device_names_in(i),
device_types_in(i) );
EMD_NOTIFICATION.ADD_DEVICE_TO_RULE( rule_name_in,
rule_owner_in,
device_names_in(i),
l_device_owner );
END IF;
END LOOP;
END IF;
END update_rule_dev_data;
-- ============================================================================
-- a private util function to get the assign devices to rules data for a given
-- user. this will return the data for the rules for only this user
-- note this procedure does not do any error handling
-- ============================================================================
PROCEDURE get_assign_data_for_user
(user_name_in IN VARCHAR2,
mode_in IN NUMBER,
devices_data_out OUT CURSOR_TYPE)
IS
BEGIN
-- get the assign dev to rules data for rules of user l_user_name
IF( mode_in = RULE_VIEW_MODE )
THEN
OPEN devices_data_out FOR
SELECT all_devices.rule_name,
all_devices.owner,
all_devices.device_name,
all_devices.type,
notifyees.device_name "notif_device_name",
decode( notifyees.device_name, NULL, 0, 1 ) is_dev_assigned
FROM (SELECT rules.rule_name,
rules.owner,
devices.device_name,
devices.type
FROM mgmt_notify_rules rules,
mgmt_notify_devices devices
WHERE rules.owner = user_name_in
AND devices.type != EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE
AND devices.type != EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE) all_devices,
(SELECT notifyees.rule_name,
notifyees.owner,
notifyees.device_name,
notifyees.profile_name
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.owner = user_name_in
AND devices.type != EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE
AND devices.type != EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE
AND notifyees.device_name = devices.device_name ) notifyees
WHERE all_devices.rule_name = notifyees.rule_name(+)
AND all_devices.owner = notifyees.owner(+)
AND all_devices.device_name = notifyees.device_name(+)
ORDER BY all_devices.owner,
all_devices.rule_name,
all_devices.type,
all_devices.device_name;
ELSE
OPEN devices_data_out FOR
SELECT all_devices.rule_name,
all_devices.owner,
all_devices.device_name,
all_devices.type,
notifyees.device_name "notif_device_name",
decode( notifyees.device_name, NULL, 0, 1 ) is_dev_assigned
FROM (SELECT rules.rule_name,
rules.owner,
devices.device_name,
devices.type
FROM mgmt_notify_rules rules,
mgmt_notify_devices devices
WHERE rules.owner = user_name_in
AND devices.type != EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE
AND devices.type != EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE) all_devices,
(SELECT notifyees.rule_name,
notifyees.owner,
notifyees.device_name,
notifyees.profile_name
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.owner = user_name_in
AND devices.type != EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE
AND devices.type != EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE
AND notifyees.device_name = devices.device_name ) notifyees
WHERE all_devices.rule_name = notifyees.rule_name(+)
AND all_devices.owner = notifyees.owner(+)
AND all_devices.device_name = notifyees.device_name(+)
ORDER BY all_devices.type,
all_devices.device_name,
all_devices.owner,
all_devices.rule_name;
END IF;
END get_assign_data_for_user;
-- ============================================================================
-- a private util function to get the assign devices to all rules of all
-- users.
-- note this procedure does not do any error handling
-- ============================================================================
PROCEDURE get_assign_data_for_all_rules
(mode_in IN NUMBER,
devices_data_out OUT CURSOR_TYPE)
IS
BEGIN
-- get the assign dev to rules data for rules of user l_user_name
IF( mode_in = RULE_VIEW_MODE )
THEN
OPEN devices_data_out FOR
SELECT all_devices.rule_name,
all_devices.owner,
all_devices.device_name,
all_devices.type,
notifyees.device_name "notif_device_name",
decode( notifyees.device_name, NULL, 0, 1 ) is_dev_assigned
FROM (SELECT rules.rule_name,
rules.owner,
devices.device_name,
devices.type
FROM mgmt_notify_rules rules,
mgmt_notify_devices devices
WHERE devices.type != EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE
AND devices.type != EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE) all_devices,
(SELECT notifyees.rule_name,
notifyees.owner,
notifyees.device_name,
notifyees.profile_name
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE devices.type != EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE
AND devices.type != EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE
AND notifyees.device_name = devices.device_name ) notifyees
WHERE all_devices.rule_name = notifyees.rule_name(+)
AND all_devices.owner = notifyees.owner(+)
AND all_devices.device_name = notifyees.device_name(+)
ORDER BY all_devices.owner,
all_devices.rule_name,
all_devices.type,
all_devices.device_name;
ELSE
OPEN devices_data_out FOR
SELECT all_devices.rule_name,
all_devices.owner,
all_devices.device_name,
all_devices.type,
notifyees.device_name "notif_device_name",
decode( notifyees.device_name, NULL, 0, 1 ) is_dev_assigned
FROM (SELECT rules.rule_name,
rules.owner,
devices.device_name,
devices.type
FROM mgmt_notify_rules rules,
mgmt_notify_devices devices
WHERE devices.type != EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE
AND devices.type != EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE) all_devices,
(SELECT notifyees.rule_name,
notifyees.owner,
notifyees.device_name,
notifyees.profile_name
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE devices.type != EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE
AND devices.type != EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE
AND notifyees.device_name = devices.device_name ) notifyees
WHERE all_devices.rule_name = notifyees.rule_name(+)
AND all_devices.owner = notifyees.owner(+)
AND all_devices.device_name = notifyees.device_name(+)
ORDER BY all_devices.type,
all_devices.device_name,
all_devices.owner,
all_devices.rule_name;
END IF;
END get_assign_data_for_all_rules;
-- ============================================================================
--
-- Name: get_user_pref_email
--
-- Purpose:
-- Get the configured email addresses(if any) of the currently logged in user
--
-- ============================================================================
PROCEDURE get_user_pref_email
(user_email_out OUT CURSOR_TYPE)
IS
BEGIN
-- default to the current user
get_user_email(mgmt_user.get_current_em_user(), user_email_out);
END get_user_pref_email;
-- ============================================================================
--
-- Name: get_user_email
--
-- Purpose:
-- Get the email addresses(if any) of the given user
--
-- ============================================================================
PROCEDURE get_user_email
(user_name_in VARCHAR2,
user_email_out OUT CURSOR_TYPE)
IS
BEGIN
-- need super user's priv to get other admin's email addresses
IF (user_name_in != mgmt_user.get_current_em_user())
THEN
check_super_user_priv_error();
END IF;
-- get the email addresses data ...
OPEN user_email_out FOR
SELECT devices.email_address, devices.type
FROM mgmt_notify_devices devices
WHERE devices.profile_name = user_name_in
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address;
END get_user_email;
-- ============================================================================
--
-- Name: get_all_key_values
--
-- Purpose: Get key values for a metric in both current and history metric tables
--
--
-- ============================================================================
PROCEDURE get_all_key_values
(target_type_in IN VARCHAR2,
target_names_in IN SMP_EMD_STRING_ARRAY,
metric_name_in IN VARCHAR2,
metric_column_in IN VARCHAR2,
search_names_in SMP_EMD_STRING_ARRAY,
search_target_name_in IN VARCHAR2,
key_value_current_out OUT CURSOR_TYPE,
key_value_1day_out OUT CURSOR_TYPE,
key_metric_columns_out OUT CURSOR_TYPE)
IS
l_num_keys NUMBER := 1;
BEGIN
SELECT NUM_KEYS
INTO l_num_keys
FROM mgmt_metrics
WHERE target_type = target_type_in
AND metric_name = metric_name_in
AND metric_column = metric_column_in
AND rownum = 1;
IF l_num_keys = 1
THEN
-- simple metric
IF target_names_in.COUNT = 1 AND target_names_in(1) = '%'
THEN
-- all targets case
OPEN key_value_current_out FOR
SELECT /*+ ORDERED */ DISTINCT c.key_value, t.target_name,
m.key_column,
NULL as key_part1_value, NULL as key_part2_value,
NULL as key_part3_value, NULL as key_part4_value,
NULL as key_part5_value
FROM mgmt_targets t, mgmt_metrics m, mgmt_current_metrics c
WHERE t.target_type = target_type_in
AND m.target_type = t.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
AND m.metric_name = metric_name_in
AND m.metric_column = metric_column_in
AND c.target_guid = t.target_guid
AND c.metric_guid = m.metric_guid
AND upper(c.key_value) LIKE search_names_in(1)
AND upper(t.target_name) LIKE search_target_name_in
AND rownum <= 2001;
OPEN key_value_1day_out FOR
SELECT /*+ ORDERED */ DISTINCT c.key_value, t.target_name,
m.key_column,
NULL as key_part1_value, NULL as key_part2_value,
NULL as key_part3_value, NULL as key_part4_value,
NULL as key_part5_value
FROM mgmt_targets t, mgmt_metrics m, mgmt_metrics_1day c
WHERE t.target_type = target_type_in
AND m.target_type = t.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
AND m.metric_name = metric_name_in
AND m.metric_column = metric_column_in
AND c.rollup_timestamp > SYSDATE-7
AND c.target_guid = t.target_guid
AND c.metric_guid = m.metric_guid
AND upper(c.key_value) LIKE search_names_in(1)
AND upper(t.target_name) LIKE search_target_name_in
AND rownum <= 2001;
ELSE
-- a list of targets case
OPEN key_value_current_out FOR
SELECT /*+ ORDERED */ DISTINCT c.key_value, t.target_name,
m.key_column,
NULL as key_part1_value, NULL as key_part2_value,
NULL as key_part3_value, NULL as key_part4_value,
NULL as key_part5_value
FROM mgmt_targets t, mgmt_metrics m, mgmt_current_metrics c
WHERE t.target_type = target_type_in
AND t.target_name IN ( SELECT * FROM TABLE(CAST(target_names_in AS SMP_EMD_STRING_ARRAY)))
AND m.target_type = t.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
AND m.metric_name = metric_name_in
AND m.metric_column = metric_column_in
AND c.target_guid = t.target_guid
AND c.metric_guid = m.metric_guid
AND upper(c.key_value) LIKE search_names_in(1)
AND upper(t.target_name) LIKE search_target_name_in
AND rownum <= 2001;
OPEN key_value_1day_out FOR
SELECT /*+ ORDERED */ DISTINCT c.key_value, t.target_name,
m.key_column,
NULL as key_part1_value, NULL as key_part2_value,
NULL as key_part3_value, NULL as key_part4_value,
NULL as key_part5_value
FROM mgmt_targets t, mgmt_metrics m, mgmt_metrics_1day c
WHERE t.target_type = target_type_in
AND t.target_name IN ( SELECT * FROM TABLE(CAST(target_names_in AS SMP_EMD_STRING_ARRAY)))
AND m.target_type = t.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
AND m.metric_name = metric_name_in
AND m.metric_column = metric_column_in
AND c.rollup_timestamp > SYSDATE-7
AND c.target_guid = t.target_guid
AND c.metric_guid = m.metric_guid
AND upper(c.key_value) LIKE search_names_in(1)
AND upper(t.target_name) LIKE search_target_name_in
AND rownum <= 2001;
END IF;
-- get all key columns for this target type
-- simple key case: no hidden key column
OPEN key_metric_columns_out FOR
SELECT distinct metric_column, column_label, column_label_NLSID,
metric_name, metric_label, metric_label_NLSID,
key_column, is_renderable
FROM mgmt_metrics
WHERE target_type = target_type_in
AND metric_name = metric_name_in
AND usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
AND key_order != 0;
ELSE
-- metric with composite key
IF target_names_in.COUNT = 1 AND target_names_in(1) = '%'
THEN
-- all target type case
OPEN key_value_current_out FOR
SELECT DISTINCT cm.key_value, cm.target_name,
cm.key_column,
ck.key_part1_value, ck.key_part2_value, ck.key_part3_value,
ck.key_part4_value, ck.key_part5_value
FROM (SELECT /*+ ORDERED */ c.target_guid, c.key_value, t.target_name,
m.key_column
FROM mgmt_targets t, mgmt_metrics m, mgmt_current_metrics c
WHERE t.target_type = target_type_in
AND m.target_type = t.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
AND m.metric_name = metric_name_in
AND m.metric_column = metric_column_in
AND c.target_guid = t.target_guid
AND c.metric_guid = m.metric_guid) cm,
mgmt_metrics_composite_keys ck
WHERE cm.target_guid = ck.target_guid (+)
AND cm.key_value = ck.composite_key (+)
AND upper(NVL(ck.key_part1_value, ' ')) LIKE search_names_in(1)
AND upper(NVL(ck.key_part2_value, ' ')) LIKE search_names_in(2)
AND upper(NVL(ck.key_part3_value, ' ')) LIKE search_names_in(3)
AND upper(NVL(ck.key_part4_value, ' ')) LIKE search_names_in(4)
AND upper(NVL(ck.key_part5_value, ' ')) LIKE search_names_in(5)
AND upper(cm.target_name) LIKE search_target_name_in
AND rownum <= 2001;
OPEN key_value_1day_out FOR
SELECT DISTINCT cm.key_value, cm.target_name,
cm.key_column,
ck.key_part1_value, ck.key_part2_value, ck.key_part3_value,
ck.key_part4_value, ck.key_part5_value
FROM (SELECT /*+ ORDERED */ c.target_guid, c.key_value, target_name,
m.key_column
FROM mgmt_targets t, mgmt_metrics m, mgmt_metrics_1day c
WHERE t.target_type = target_type_in
AND m.target_type = t.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
AND m.metric_name = metric_name_in
AND m.metric_column = metric_column_in
AND c.rollup_timestamp > SYSDATE-7
AND c.target_guid = t.target_guid
AND c.metric_guid = m.metric_guid) cm,
mgmt_metrics_composite_keys ck
WHERE cm.target_guid = ck.target_guid (+)
AND cm.key_value = ck.composite_key (+)
AND upper(NVL(ck.key_part1_value, ' ')) LIKE search_names_in(1)
AND upper(NVL(ck.key_part2_value, ' ')) LIKE search_names_in(2)
AND upper(NVL(ck.key_part3_value, ' ')) LIKE search_names_in(3)
AND upper(NVL(ck.key_part4_value, ' ')) LIKE search_names_in(4)
AND upper(NVL(ck.key_part5_value, ' ')) LIKE search_names_in(5)
AND upper(cm.target_name) LIKE search_target_name_in
AND rownum <= 2001;
-- get all key columns for this target type
OPEN key_metric_columns_out FOR
SELECT distinct metric_column, column_label, column_label_NLSID,
metric_name, metric_label, metric_label_NLSID,
key_column, is_renderable
FROM mgmt_metrics
WHERE target_type = target_type_in
AND metric_name = metric_name_in
AND usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
AND key_order != 0;
ELSE
-- a list of targets case
OPEN key_value_current_out FOR
SELECT DISTINCT cm.key_value, cm.target_name,
cm.key_column,
ck.key_part1_value, ck.key_part2_value, ck.key_part3_value,
ck.key_part4_value, ck.key_part5_value
FROM (SELECT /*+ ORDERED */ c.target_guid, c.key_value, t.target_name,
m.key_column
FROM mgmt_targets t, mgmt_metrics m, mgmt_current_metrics c
WHERE t.target_type = target_type_in
AND t.target_name IN ( SELECT * FROM TABLE(CAST(target_names_in AS SMP_EMD_STRING_ARRAY)))
AND m.target_type = t.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
AND m.metric_name = metric_name_in
AND m.metric_column = metric_column_in
AND c.target_guid = t.target_guid
AND c.metric_guid = m.metric_guid) cm,
mgmt_metrics_composite_keys ck
WHERE cm.target_guid = ck.target_guid (+)
AND cm.key_value = ck.composite_key (+)
AND upper(NVL(ck.key_part1_value, ' ')) LIKE search_names_in(1)
AND upper(NVL(ck.key_part2_value, ' ')) LIKE search_names_in(2)
AND upper(NVL(ck.key_part3_value, ' ')) LIKE search_names_in(3)
AND upper(NVL(ck.key_part4_value, ' ')) LIKE search_names_in(4)
AND upper(NVL(ck.key_part5_value, ' ')) LIKE search_names_in(5)
AND upper(cm.target_name) LIKE search_target_name_in
AND rownum <= 2001;
OPEN key_value_1day_out FOR
SELECT DISTINCT cm.key_value, cm.target_name,
cm.key_column,
ck.key_part1_value, ck.key_part2_value, ck.key_part3_value,
ck.key_part4_value, ck.key_part5_value
FROM (SELECT /*+ ORDERED */ c.target_guid, c.key_value, t.target_name,
m.key_column
FROM mgmt_targets t, mgmt_metrics m, mgmt_metrics_1day c
WHERE t.target_type = target_type_in
AND t.target_name IN ( SELECT * FROM TABLE(CAST(target_names_in AS SMP_EMD_STRING_ARRAY)))
AND m.target_type = t.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
AND m.metric_name = metric_name_in
AND m.metric_column = metric_column_in
AND c.rollup_timestamp > SYSDATE-7
AND c.target_guid = t.target_guid
AND c.metric_guid = m.metric_guid) cm,
mgmt_metrics_composite_keys ck
WHERE cm.target_guid = ck.target_guid (+)
AND cm.key_value = ck.composite_key (+)
AND upper(NVL(ck.key_part1_value, ' ')) LIKE search_names_in(1)
AND upper(NVL(ck.key_part2_value, ' ')) LIKE search_names_in(2)
AND upper(NVL(ck.key_part3_value, ' ')) LIKE search_names_in(3)
AND upper(NVL(ck.key_part4_value, ' ')) LIKE search_names_in(4)
AND upper(NVL(ck.key_part5_value, ' ')) LIKE search_names_in(5)
AND upper(cm.target_name) LIKE search_target_name_in
AND rownum <= 2001;
-- get all key columns for this target type
OPEN key_metric_columns_out FOR
SELECT distinct m.metric_column, m.column_label, m.column_label_NLSID,
m.metric_name, m.metric_label, m.metric_label_NLSID,
m.key_column, m.is_renderable
FROM mgmt_metrics m, mgmt_targets t
WHERE t.target_type = target_type_in
AND t.target_name IN ( SELECT * FROM TABLE(CAST(target_names_in AS SMP_EMD_STRING_ARRAY)))
AND m.target_type = t.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
AND metric_name = metric_name_in
AND usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
AND key_order != 0;
END IF;
END IF;
END get_all_key_values;
-- ============================================================================
--
-- Name: update_user_pref_data
--
-- Purpose:
-- Update the current user preferences data. This includes the user password
-- and the user email
--
-- ============================================================================
PROCEDURE update_user_pref_data
(user_name_in IN VARCHAR2,
user_password_in IN VARCHAR2,
should_update_pwd_in IN NUMBER,
user_email_in IN SMP_EMD_STRING_ARRAY,
user_email_format_in IN SMP_EMD_INTEGER_ARRAY,
mail_gateway_setup_out OUT NUMBER,
rules_subscribed_out OUT NUMBER,
schedule_setup_out OUT NUMBER)
IS
l_user_name VARCHAR2(256);
l_old_email_count NUMBER := 0;
BEGIN
-- initialize OUT parameters
mail_gateway_setup_out := MGMT_GLOBAL.G_FALSE;
rules_subscribed_out := MGMT_GLOBAL.G_FALSE;
schedule_setup_out := MGMT_GLOBAL.G_FALSE;
-- get the current user name
l_user_name := mgmt_user.get_current_em_user();
-- update the user password
IF( should_update_pwd_in=MGMT_GLOBAL.G_TRUE )
THEN
mgmt_user.modify_user( user_name_in, user_password_in );
END IF;
SELECT count(email_address)
INTO l_old_email_count
FROM mgmt_notify_devices
WHERE profile_name = user_name_in
AND type IN (EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE)
AND device_name = email_address;
mgmt_user.update_user_pref_email( user_name_in,
user_email_in,
user_email_format_in );
-- find out if the mail gateway is setup
mail_gateway_setup_out := EMD_NOTIFICATION.IS_MAIL_GATEWAY_SETUP();
-- find out if the schedule is defined
schedule_setup_out := emd_notification.user_schedule_set(user_name_in);
-- delete the schedule without any email address
IF (schedule_setup_out=MGMT_GLOBAL.G_FALSE)
THEN
emd_notification.clear_schedule(user_name_in);
END IF;
IF (l_old_email_count = 0 AND user_email_in.COUNT > 0)
THEN
-- create a default notification schedule
EMD_NOTIFICATION.SET_SCHEDULE(user_name_in, ' ', sysdate, 1);
-- 24x7 schedule for each email address
FOR i IN user_email_in.FIRST..user_email_in.LAST
LOOP
-- 24x7 schedule for each email address
EMD_NOTIFICATION.SET_DEVICE_SCHEDULE(user_name_in,
' ',
user_email_in(i),
user_name_in,
MGMT_PREFERENCES.DEFAULT_SCHEDULE_VALUE);
END LOOP;
END IF;
schedule_setup_out := emd_notification.user_schedule_set(user_name_in);
END update_user_pref_data;
-- ============================================================================
--
-- Name: try_update_user_pref
--
-- Purpose:
-- try to update the current user preferences data. This includes the user
-- password and the user email. If all email addresses used in the notification
-- schedule are going to be deleted, do nothing and return a list of all email
-- addresses used in the schedule.
--
-- ============================================================================
PROCEDURE try_update_user_pref
(user_name_in IN VARCHAR2,
user_password_in IN VARCHAR2,
should_update_pwd_in IN NUMBER,
user_email_in IN SMP_EMD_STRING_ARRAY,
user_email_format_in IN SMP_EMD_INTEGER_ARRAY,
mail_gateway_setup_out OUT NUMBER,
rules_subscribed_out OUT NUMBER,
schedule_setup_out OUT NUMBER,
operation_done_out OUT NUMBER,
email_used_out OUT CURSOR_TYPE)
IS
l_defined NUMBER;
l_ok_to_delete NUMBER;
BEGIN
l_defined := emd_notification.user_schedule_set(user_name_in);
IF (l_defined = MGMT_GLOBAL.G_FALSE)
THEN
-- schedule was not defined; update is allowed
update_user_pref_data(user_name_in,
user_password_in,
should_update_pwd_in,
user_email_in,
user_email_format_in,
mail_gateway_setup_out,
rules_subscribed_out,
l_defined);
operation_done_out := MGMT_GLOBAL.G_TRUE;
-- return a dummy cursor
OPEN email_used_out FOR
SELECT dev_schedules.device_name
FROM mgmt_notify_dev_schedules dev_schedules
WHERE rownum < 1;
ELSE
-- schedule was defined;
l_ok_to_delete := 0;
BEGIN
SELECT 1 INTO l_ok_to_delete
FROM mgmt_notify_dev_schedules dev_schedules
WHERE dev_schedules.schedule_owner = user_name_in
AND dev_schedules.schedule_name = ' '
AND dev_schedules.device_owner = user_name_in
AND upper(dev_schedules.device_name) IN
(SELECT upper(column_value)
FROM TABLE(CAST(user_email_in AS SMP_EMD_STRING_ARRAY)))
AND rownum = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
IF (l_ok_to_delete = 1)
THEN
-- do it;
update_user_pref_data(user_name_in,
user_password_in,
should_update_pwd_in,
user_email_in,
user_email_format_in,
mail_gateway_setup_out,
rules_subscribed_out,
l_defined);
operation_done_out := MGMT_GLOBAL.G_TRUE;
-- return a dummy cursor
OPEN email_used_out FOR
SELECT dev_schedules.device_name
FROM mgmt_notify_dev_schedules dev_schedules
WHERE rownum < 1;
ELSE
-- don't update email addresses;
operation_done_out := MGMT_GLOBAL.G_FALSE;
-- check if need to update the password
IF( should_update_pwd_in=MGMT_GLOBAL.G_TRUE )
THEN
-- update the user password
mgmt_user.modify_user(user_name_in, user_password_in);
END IF;
-- return email addresses used in notification schedule
OPEN email_used_out FOR
SELECT dev_schedules.device_name
FROM mgmt_notify_dev_schedules dev_schedules
WHERE dev_schedules.schedule_owner = user_name_in
AND dev_schedules.schedule_name = ' '
AND dev_schedules.device_owner = user_name_in;
END IF;
END IF;
END try_update_user_pref;
-- ============================================================================
--
-- Name: add_email_addresses
--
-- Purpose:
-- Add a list of email addresses to the user's preferences data.
--
-- ============================================================================
PROCEDURE add_email_addresses
(user_name_in IN VARCHAR2,
user_email_in IN SMP_EMD_STRING_ARRAY,
user_email_format_in IN SMP_EMD_INTEGER_ARRAY)
IS
l_count NUMBER := 0;
l_user_emails SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
l_user_email_formats SMP_EMD_INTEGER_ARRAY := SMP_EMD_INTEGER_ARRAY();
CURSOR c_existing_email_addresses IS
SELECT email_address, type
FROM mgmt_notify_devices
WHERE profile_name = user_name_in
AND type IN (EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE)
AND device_name = email_address;
BEGIN
OPEN c_existing_email_addresses;
FETCH c_existing_email_addresses
BULK COLLECT INTO l_user_emails, l_user_email_formats;
CLOSE c_existing_email_addresses;
l_count := l_user_emails.COUNT;
FOR i IN 1..user_email_in.COUNT
LOOP
l_count := l_count + 1;
l_user_emails.extend(1);
l_user_emails(l_count) := user_email_in(i);
l_user_email_formats.extend(1);
l_user_email_formats(l_count) := user_email_format_in(i);
END LOOP;
mgmt_user.update_user_pref_email( user_name_in,
l_user_emails,
l_user_email_formats);
END add_email_addresses;
-- ============================================================================
--
-- Name: get_metrics_for_rule
--
-- Purpose:
-- Get a list of metric columns of a target type for a rule
--
-- ============================================================================
PROCEDURE get_metrics_for_rule
(target_type_in IN VARCHAR2,
target_names_in IN SMP_EMD_STRING_ARRAY,
threshold_metric_colums_out OUT CURSOR_TYPE,
key_metric_columns_out OUT CURSOR_TYPE)
IS
BEGIN
-- get all metric columns which could have thresholds
IF (target_names_in IS NULL OR
target_names_in.COUNT = 0)
THEN
OPEN threshold_metric_colums_out FOR
SELECT distinct metrics.metric_column, metrics.column_label,
metrics.column_label_NLSID,
metrics.metric_name, metrics.metric_label,
metrics.metric_label_NLSID,
NVL(metrics.key_column, ' ') "key_column",
metrics.is_renderable
FROM mgmt_metrics metrics
WHERE metrics.target_type = target_type_in
AND metrics.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
AND metrics.metric_label != ' '
AND metrics.column_label != ' '
AND (EXISTS (SELECT /*+ INDEX(p) */ policy_guid
FROM mgmt_policy_assoc p
WHERE p.policy_type = 1
AND metrics.metric_guid = p.policy_guid)
OR metrics.non_thresholded_alerts = 1)
ORDER BY metrics.column_label;
-- get all key columns for this target type
OPEN key_metric_columns_out FOR
SELECT distinct metric_column, column_label, column_label_NLSID,
metric_name, metric_label, metric_label_NLSID,
key_column, is_renderable
FROM mgmt_metrics
WHERE target_type = target_type_in
AND usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
AND key_order != 0;
ELSE
OPEN threshold_metric_colums_out FOR
SELECT distinct m.metric_column, m.column_label,
m.column_label_NLSID,
m.metric_name, m.metric_label,
m.metric_label_NLSID,
NVL(m.key_column, ' ') "key_column",
m.is_renderable
FROM mgmt_metrics m,
mgmt_targets t
WHERE m.target_type = target_type_in
AND m.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
AND m.metric_label != ' '
AND m.column_label != ' '
AND m.target_type = t.target_type
AND t.target_name IN ( SELECT * FROM TABLE(CAST(target_names_in AS SMP_EMD_STRING_ARRAY)))
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
AND (EXISTS (SELECT /*+ INDEX(p) */ policy_guid
FROM mgmt_policy_assoc p
WHERE p.policy_type = 1
AND m.metric_guid = p.policy_guid)
OR m.non_thresholded_alerts = 1)
ORDER BY m.column_label;
-- get all key columns for this target type
OPEN key_metric_columns_out FOR
SELECT distinct m.metric_column, m.column_label, m.column_label_NLSID,
m.metric_name, m.metric_label, m.metric_label_NLSID,
m.key_column, m.is_renderable
FROM mgmt_metrics m, mgmt_targets t
WHERE t.target_type = target_type_in
AND t.target_name IN ( SELECT * FROM TABLE(CAST(target_names_in AS SMP_EMD_STRING_ARRAY)))
AND m.target_type = t.target_type
AND m.type_meta_ver = t.type_meta_ver
AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
AND usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
AND key_order != 0;
END IF;
END get_metrics_for_rule;
-- ============================================================================
--
-- Name: get_notification_rules
--
-- Purpose:
-- Get the list of notification rules(with the overview data) for a user.
--
-- ============================================================================
PROCEDURE get_notification_rules
(user_name_in IN VARCHAR2,
rules_type_in IN VARCHAR2,
rules_overview_list_out OUT CURSOR_TYPE,
rules_target_type_list_out OUT CURSOR_TYPE)
IS
l_user_exists_val NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_user_name VARCHAR2(256) := '';
l_current_user VARCHAR2(256) := '';
l_priv_result NUMBER := 0;
l_query_str VARCHAR2(256) := '';
l_email_address_count NUMBER := 0;
l_email_address SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
CURSOR c_preferred_email_address (current_user VARCHAR2) IS
SELECT devices.email_address
FROM mgmt_notify_devices devices
WHERE devices.profile_name = current_user
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address;
BEGIN
-- check for error conditions first
-- get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
l_current_user := mgmt_user.get_current_em_user();
-- first, figure out if current user is a privileged user
check_super_user_priv_error();
-- check for the existence of the user(if supplied)
IF( (length(trim(user_name_in)) <> 0 ) )
THEN
-- check for the existence of this user
mgmt_user.check_user_exists(user_name_in);
l_user_exists_val := MGMT_GLOBAL.G_TRUE;
END IF;
-- get the preffered email addresses
OPEN c_preferred_email_address (l_current_user);
FETCH c_preferred_email_address
BULK COLLECT INTO l_email_address;
CLOSE c_preferred_email_address;
l_email_address_count := l_email_address.count;
IF( l_user_exists_val=MGMT_GLOBAL.G_TRUE )
THEN
-- user name was passed in so get the notification rules for user -
-- user_name_in
l_user_name := user_name_in;
ELSE
-- user name was not passed in so get the notification rules for current
-- user
l_user_name := mgmt_user.get_current_em_user();
END IF;
-- error conditions have been taken care of, so get the rules overview data
IF( l_user_exists_val=MGMT_GLOBAL.G_TRUE )
THEN
-- user name was passed in so get the notification rules for
-- user - user_name_in
IF( rules_type_in = RULES_TYPE_ALL )
THEN
-- get all rules for user - user_name_in
OPEN rules_overview_list_out FOR
SELECT DISTINCT
rules.rule_name "rule_name",
rules.owner "rule_owner",
rules.description "rule_desc",
rules.public_rule "rule_public",
l_email_address_count "email_address_count",
decode(emails.profile_name, NULL, 'N', 'Y') "send_email"
FROM mgmt_notify_rules rules,
(SELECT notifyees.rule_name,
notifyees.owner,
notifyees.profile_name
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.profile_name = l_current_user
AND notifyees.device_name = devices.device_name
AND notifyees.profile_name = devices.profile_name
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address ) emails
WHERE rules.owner=l_user_name
AND rules.rule_name=emails.rule_name(+)
AND rules.owner=emails.owner(+)
ORDER BY rules.rule_name;
OPEN rules_target_type_list_out FOR
SELECT distinct configs.rule_name "rule_name",
configs.owner "rule_owner",
configs.target_type "target_type"
FROM mgmt_notify_rule_configs configs
WHERE configs.owner=l_user_name
UNION
SELECT distinct jr.rule_name "rule_name",
jr.owner "rule_owner",
jr.target_type "target_type"
FROM mgmt_notify_job_rule_configs jr
WHERE owner=l_user_name;
ELSE
-- get public rules for user - user_name_in
OPEN rules_overview_list_out FOR
SELECT DISTINCT
rules.rule_name "rule_name",
rules.owner "rule_owner",
rules.description "rule_desc",
rules.public_rule "rule_public",
l_email_address_count "email_address_count",
decode(emails.profile_name, NULL, 'N', 'Y') "send_email"
FROM mgmt_notify_rules rules,
(SELECT notifyees.rule_name,
notifyees.owner,
notifyees.profile_name
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.profile_name = l_current_user
AND notifyees.device_name = devices.device_name
AND notifyees.profile_name = devices.profile_name
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address ) emails
WHERE rules.owner!=l_user_name
AND rules.public_rule=1
AND rules.rule_name=emails.rule_name(+)
AND rules.owner=emails.owner(+)
ORDER BY rules.rule_name;
OPEN rules_target_type_list_out FOR
SELECT distinct configs.rule_name "rule_name",
configs.owner "rule_owner",
configs.target_type "target_type"
FROM mgmt_notify_rules rules,
mgmt_notify_rule_configs configs
WHERE rules.owner!=l_user_name
AND rules.public_rule=1
AND rules.owner=configs.owner
AND rules.rule_name=configs.rule_name
UNION
SELECT distinct jr.rule_name "rule_name",
jr.owner "rule_owner",
jr.target_type "target_type"
FROM mgmt_notify_rules rules,
mgmt_notify_job_rule_configs jr
WHERE rules.owner!=l_user_name
AND rules.public_rule=1
AND rules.owner=jr.owner
AND rules.rule_name=jr.rule_name;
END IF;
ELSE
-- user name was not passed in so get the notification rules for the
-- currently logged in (privileged) user
IF( rules_type_in = RULES_TYPE_ALL )
THEN
-- get all rules for current privileged user
OPEN rules_overview_list_out FOR
SELECT DISTINCT
rules.rule_name "rule_name",
rules.owner "rule_owner",
rules.description "rule_desc",
rules.public_rule "rule_public",
l_email_address_count "email_address_count",
decode(emails.profile_name, NULL, 'N', 'Y') "send_email"
FROM mgmt_notify_rules rules,
(SELECT notifyees.rule_name,
notifyees.owner,
notifyees.profile_name
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.profile_name = l_current_user
AND notifyees.device_name = devices.device_name
AND notifyees.profile_name = devices.profile_name
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address ) emails
WHERE rules.rule_name=emails.rule_name(+)
AND rules.owner=emails.owner(+)
ORDER BY rules.owner, rules.rule_name;
OPEN rules_target_type_list_out FOR
SELECT distinct configs.rule_name "rule_name",
configs.owner "rule_owner",
configs.target_type "target_type"
FROM mgmt_notify_rule_configs configs
UNION
SELECT distinct jr.rule_name "rule_name",
jr.owner "rule_owner",
jr.target_type "target_type"
FROM mgmt_notify_job_rule_configs jr;
ELSE
-- get public rules for current privileged user
OPEN rules_overview_list_out FOR
SELECT DISTINCT
rules.rule_name "rule_name",
rules.owner "rule_owner",
rules.description "rule_desc",
rules.public_rule "rule_public",
l_email_address_count "email_address_count",
decode(emails.profile_name, NULL, 'N', 'Y') "send_email"
FROM mgmt_notify_rules rules,
(SELECT notifyees.rule_name,
notifyees.owner,
notifyees.profile_name
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.profile_name = l_current_user
AND notifyees.device_name = devices.device_name
AND notifyees.profile_name = devices.profile_name
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address ) emails
WHERE rules.public_rule=1
AND rules.rule_name=emails.rule_name(+)
AND rules.owner=emails.owner(+)
ORDER BY rules.owner, rules.rule_name;
OPEN rules_target_type_list_out FOR
SELECT distinct configs.rule_name "rule_name",
configs.owner "rule_owner",
configs.target_type "target_type"
FROM mgmt_notify_rules rules,
mgmt_notify_rule_configs configs
WHERE rules.public_rule=1
AND rules.owner=configs.owner
AND rules.rule_name=configs.rule_name
UNION
SELECT distinct jr.rule_name "rule_name",
jr.owner "rule_owner",
jr.target_type "target_type"
FROM mgmt_notify_rules rules,
mgmt_notify_rule_configs jr
WHERE rules.public_rule=1
AND rules.owner=jr.owner
AND rules.rule_name=jr.rule_name;
END IF;
END IF;
END get_notification_rules;
-- ============================================================================
--
-- Name: get_notification_rules
--
-- Purpose:
-- Get the list of notification rules(with the overview data) for the current
-- user.
--
-- ============================================================================
PROCEDURE get_notification_rules
(rules_type_in IN VARCHAR2,
rules_overview_list_out OUT CURSOR_TYPE,
rules_target_type_list_out OUT CURSOR_TYPE)
IS
l_user_name VARCHAR2(256) := '';
l_email_address_count NUMBER := 0;
l_email_address SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
CURSOR c_preferred_email_address (current_user VARCHAR2) IS
SELECT devices.email_address
FROM mgmt_notify_devices devices
WHERE devices.profile_name = current_user
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address;
BEGIN
-- get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- get the preffered email addresses
OPEN c_preferred_email_address (l_user_name);
FETCH c_preferred_email_address
BULK COLLECT INTO l_email_address;
CLOSE c_preferred_email_address;
l_email_address_count := l_email_address.count;
-- error conditions have been taken care of, so get the rules overview data
IF( rules_type_in = RULES_TYPE_ALL )
THEN
-- get all rules for user - user_name_in
OPEN rules_overview_list_out FOR
SELECT DISTINCT
rules.rule_name "rule_name",
rules.owner "rule_owner",
rules.description "rule_desc",
rules.public_rule "rule_public",
l_email_address_count "email_address_count",
decode(emails.profile_name, NULL, 'N', 'Y') "send_email"
FROM mgmt_notify_rules rules,
(SELECT notifyees.rule_name,
notifyees.owner,
notifyees.profile_name
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.profile_name = l_user_name
AND notifyees.device_name = devices.device_name
AND notifyees.profile_name = devices.profile_name
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address ) emails
WHERE rules.owner=l_user_name
AND rules.rule_name=emails.rule_name(+)
AND rules.owner=emails.owner(+)
ORDER BY rules.rule_name;
OPEN rules_target_type_list_out FOR
SELECT distinct configs.rule_name "rule_name",
configs.owner "rule_owner",
configs.target_type "target_type"
FROM mgmt_notify_rule_configs configs
WHERE configs.owner=l_user_name
UNION
SELECT distinct jr.rule_name "rule_name",
jr.owner "rule_owner",
jr.target_type "target_type"
FROM mgmt_notify_job_rule_configs jr
WHERE owner=l_user_name;
ELSE
-- get public rules for user - user_name_in
OPEN rules_overview_list_out FOR
SELECT DISTINCT
rules.rule_name "rule_name",
rules.owner "rule_owner",
rules.description "rule_desc",
rules.public_rule "rule_public",
l_email_address_count "email_address_count",
decode(emails.profile_name, NULL, 'N', 'Y') "send_email"
FROM mgmt_notify_rules rules,
(SELECT notifyees.rule_name,
notifyees.owner,
notifyees.profile_name
FROM mgmt_notify_notifyees notifyees,
mgmt_notify_devices devices
WHERE notifyees.profile_name = l_user_name
AND notifyees.device_name = devices.device_name
AND notifyees.profile_name = devices.profile_name
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address ) emails
WHERE rules.owner!=l_user_name
AND rules.public_rule=1
AND rules.rule_name=emails.rule_name(+)
AND rules.owner=emails.owner(+)
ORDER BY rules.rule_name;
OPEN rules_target_type_list_out FOR
SELECT distinct configs.rule_name "rule_name",
configs.owner "rule_owner",
configs.target_type "target_type"
FROM mgmt_notify_rules rules,
mgmt_notify_rule_configs configs
WHERE rules.owner!=l_user_name
AND rules.public_rule=1
AND rules.owner=configs.owner
AND rules.rule_name=configs.rule_name
UNION
SELECT distinct jr.rule_name "rule_name",
jr.owner "rule_owner",
jr.target_type "target_type"
FROM mgmt_notify_rules rules,
mgmt_notify_rule_configs jr
WHERE rules.owner!=l_user_name
AND rules.public_rule=1
AND rules.owner=jr.owner
AND rules.rule_name=jr.rule_name;
END IF;
END get_notification_rules;
-- ============================================================================
-- a private plsql to get the notification rule detail data
-- ============================================================================
PROCEDURE get_notif_rule_detail_data
(rule_name_in IN VARCHAR2,
user_name_in IN VARCHAR2,
language_in IN VARCHAR2,
country_in IN VARCHAR2,
rules_detail_list_out OUT CURSOR_TYPE,
rules_target_list_out OUT CURSOR_TYPE,
rules_metric_list_out OUT CURSOR_TYPE,
rules_metric_index_list_out OUT CURSOR_TYPE,
rules_policy_list_out OUT CURSOR_TYPE,
rules_job_list_out OUT CURSOR_TYPE,
rules_email_notif_list_out OUT CURSOR_TYPE,
rules_device_notif_list_out OUT CURSOR_TYPE,
user_pref_email_list_out OUT CURSOR_TYPE,
notif_devices_list_out OUT CURSOR_TYPE,
key_metric_columns_out OUT CURSOR_TYPE)
IS
l_all_targets_selected NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_all_metrics_selected NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_target_type VARCHAR2(64) := '';
l_number_of_targets NUMBER := 0;
l_visible_targets_count NUMBER := 0;
BEGIN
-- initialize OUT parameters
-- get the target type for rule - rule_name_in of user - user_name_in
l_target_type := get_rule_target_type( rule_name_in,
user_name_in );
-- get the data for cursor - rules_detail_list_out
OPEN rules_detail_list_out FOR
SELECT rules.rule_name "rule_name",
rules.owner "rule_owner",
rules.description "rule_desc",
rules.public_rule "rule_public",
l_target_type "target_type",
(SELECT type_display_name
FROM mgmt_targets
WHERE target_type=l_target_type
AND rownum=1) "type_display_name",
rules.repeat "rule_repeat"
FROM mgmt_notify_rules rules
WHERE rules.rule_name=rule_name_in
AND rules.owner=user_name_in
ORDER BY rules.rule_name;
-- get the data for cursor - rules_target_list_out
SELECT count(*) INTO l_number_of_targets
FROM mgmt_notify_rule_configs
WHERE rule_name=rule_name_in
AND owner=user_name_in;
SELECT count(config.target_name)
INTO l_visible_targets_count
FROM mgmt_notify_rule_configs config,
mgmt_targets tgt
WHERE config.rule_name=rule_name_in
AND config.owner=user_name_in
AND config.target_type=tgt.target_type
AND (config.target_name=tgt.target_name
OR config.target_name=' ' AND config.target_guid IS NOT NULL);
IF (l_number_of_targets > 0)
THEN
OPEN rules_target_list_out FOR
SELECT distinct
config.target_name,
config.target_type,
config.visible_target_name,
config.visible_target_type,
tgt.target_name "comp_target_name",
tgt.target_type "comp_target_type",
tgt.type_display_name "comp_type_display_name",
l_number_of_targets "targets_count",
l_visible_targets_count "visible_targets_count",
want_target_up,
want_target_down,
want_target_unreachable_start,
want_target_unreachable_end,
want_target_metric_err_start,
want_target_metric_err_end,
want_target_blackout_start,
want_target_blackout_end,
ignore_rca
FROM (SELECT rc.target_name,
rc.target_type,
rc.target_guid,
mt.target_name as visible_target_name,
mt.target_type as visible_target_type,
want_target_up,
want_target_down,
want_target_unreachable_start,
want_target_unreachable_end,
want_target_metric_err_start,
want_target_metric_err_end,
want_target_blackout_start,
want_target_blackout_end,
ignore_rca
FROM mgmt_notify_rule_configs rc,
mgmt_targets mt
WHERE rc.rule_name = rule_name_in
AND rc.owner = user_name_in
AND rc.target_name = mt.target_name (+)
AND rc.target_type = mt.target_type (+) ) config,
mgmt_targets tgt
WHERE config.target_guid = tgt.target_guid (+)
ORDER BY target_type, target_name;
ELSE
SELECT count(*) INTO l_number_of_targets
FROM mgmt_notify_job_rule_configs
WHERE rule_name=rule_name_in
AND owner=user_name_in;
SELECT count(config.target_name)
INTO l_visible_targets_count
FROM mgmt_notify_job_rule_configs config,
mgmt_targets tgt
WHERE config.rule_name=rule_name_in
AND config.owner=user_name_in
AND config.target_type=tgt.target_type
AND config.target_name=tgt.target_name;
OPEN rules_target_list_out FOR
SELECT distinct
config.target_name,
config.target_type,
config.visible_target_name,
config.visible_target_type,
tgt.target_name "comp_target_name",
tgt.target_type "comp_target_type",
tgt.type_display_name "comp_type_display_name",
l_number_of_targets "targets_count",
l_visible_targets_count "visible_targets_count",
0 want_target_up,
0 want_target_down,
0 want_target_unreachable_start,
0 want_target_unreachable_end,
0 want_target_metric_err_start,
0 want_target_metric_err_end,
0 want_target_blackout_start,
0 want_target_blackout_end,
0 ignore_rca
FROM (SELECT jrc.target_name,
jrc.target_type,
jrc.target_guid,
mt.target_name as visible_target_name,
mt.target_type as visible_target_type
FROM mgmt_notify_job_rule_configs jrc,
mgmt_targets mt
WHERE jrc.rule_name = rule_name_in
AND jrc.owner = user_name_in
AND jrc.target_name = mt.target_name (+)
AND jrc.target_type = mt.target_type (+) ) config,
mgmt_targets tgt
WHERE config.target_guid = tgt.target_guid (+)
ORDER BY target_type, target_name;
END IF;
-- get the data for cursor - rules_metric_list_out
-- the cursor for metrics data depends on whether:
-- 1. all targets were selected
-- 2. all metrics were selected
-- there are four possibilities:
--====================================================================
-- "all targets selected" | "all metrics selected" | "possibility #" |
--====================================================================
-- YES | YES | P1
-- YES | NO | P2
-- NO | YES | P3
-- NO | NO | P4
-- the query for the metrics cursor data depends on these possibilities
-- find out if all targets were selected
l_all_targets_selected := all_targets_selected( rule_name_in,
user_name_in );
-- find out if all metrics were selected
l_all_metrics_selected := all_metrics_selected( rule_name_in,
user_name_in );
IF( l_all_targets_selected=MGMT_GLOBAL.G_TRUE )
THEN
IF( l_all_metrics_selected=MGMT_GLOBAL.G_TRUE )
THEN
-- possibility # P1 - all targets, all metrics
OPEN rules_metric_list_out FOR
SELECT distinct metrics.metric_name,
metrics.metric_column,
metrics.metric_label,
metrics.column_label,
NVL(metrics.key_column, ' ') "key_column",
metrics.metric_label_nlsid,
metrics.column_label_nlsid,
metrics.num_keys,
rules.key_value,
rules.want_clears,
rules.want_warnings,
rules.want_critical_alerts,
rules.want_warning_job_succeeded,
rules.want_warning_job_problems,
rules.want_critical_job_succeeded,
rules.want_critical_job_problems,
rules.want_policy_clears,
rules.want_policy_violations,
rules.want_policy_job_succeeded,
rules.want_policy_job_problems
FROM mgmt_notify_rule_configs rules,
mgmt_metrics metrics,
(SELECT metric_guid
FROM mgmt_metric_thresholds
UNION ALL
SELECT metric_guid
FROM mgmt_metrics_ext
WHERE alertable = 'Y'
AND thresholdable = 'N') thresholds
WHERE rules.rule_name=rule_name_in
AND rules.owner=user_name_in
AND rules.target_type=l_target_type
AND metrics.metric_name=rules.metric_name
AND metrics.metric_column=rules.metric_column
AND metrics.target_type=l_target_type
AND metrics.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
AND metrics.metric_type!=2
AND metrics.metric_guid=thresholds.metric_guid
AND (metric_label != ' ' AND column_label != ' ');
ELSE
-- possibility # P2 - all targets, some metrics
OPEN rules_metric_list_out FOR
SELECT distinct metrics.metric_name,
metrics.metric_column,
metrics.metric_label,
metrics.column_label,
NVL(metrics.key_column, ' ') "key_column",
metrics.metric_label_nlsid,
metrics.column_label_nlsid,
metrics.num_keys,
rules.key_value,
rules.want_clears,
rules.want_warnings,
rules.want_critical_alerts,
rules.want_warning_job_succeeded,
rules.want_warning_job_problems,
rules.want_critical_job_succeeded,
rules.want_critical_job_problems,
rules.want_policy_clears,
rules.want_policy_violations,
rules.want_policy_job_succeeded,
rules.want_policy_job_problems
FROM mgmt_notify_rule_configs rules,
mgmt_metrics metrics
WHERE rules.rule_name=rule_name_in
AND rules.owner=user_name_in
AND metrics.target_type=rules.target_type
AND metrics.metric_name=rules.metric_name
AND metrics.metric_column=rules.metric_column
AND metrics.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
AND metrics.metric_type!=2
AND (metric_label != ' ' AND column_label != ' ');
END IF;
ELSE
IF( l_all_metrics_selected=MGMT_GLOBAL.G_TRUE )
THEN
-- possibility # P3 - some targets, all metrics
OPEN rules_metric_list_out FOR
SELECT distinct metrics.metric_name,
metrics.metric_column,
metrics.metric_label,
metrics.column_label,
NVL(metrics.key_column, ' ') "key_column",
metrics.metric_label_nlsid,
metrics.column_label_nlsid,
metrics.num_keys,
rules.key_value,
rules.want_clears,
rules.want_warnings,
rules.want_critical_alerts,
rules.want_warning_job_succeeded,
rules.want_warning_job_problems,
rules.want_critical_job_succeeded,
rules.want_critical_job_problems,
rules.want_policy_clears,
rules.want_policy_violations,
rules.want_policy_job_succeeded,
rules.want_policy_job_problems
FROM mgmt_notify_rule_configs rules,
mgmt_targets targets,
mgmt_metrics metrics,
(SELECT target_guid, metric_guid
FROM mgmt_metric_thresholds
UNION ALL
SELECT NULL target_guid, metric_guid
FROM mgmt_metrics_ext
WHERE alertable = 'Y'
AND thresholdable = 'N') thresholds
WHERE rules.rule_name=rule_name_in
AND rules.owner=user_name_in
AND targets.target_name=rules.target_name
AND targets.target_type=rules.target_type
AND (thresholds.target_guid=targets.target_guid
OR thresholds.target_guid IS NULL)
AND metrics.metric_guid=thresholds.metric_guid
AND metrics.target_type=rules.target_type
AND metrics.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
AND metrics.metric_type!=2
AND (metric_label != ' ' AND column_label != ' ');
ELSE
-- possibility # P4 - some targets, some metrics
OPEN rules_metric_list_out FOR
SELECT distinct metrics.metric_name,
metrics.metric_column,
metrics.metric_label,
metrics.column_label,
NVL(metrics.key_column, ' ') "key_column",
metrics.metric_label_nlsid,
metrics.column_label_nlsid,
metrics.num_keys,
rules.key_value,
rules.want_clears,
rules.want_warnings,
rules.want_critical_alerts,
rules.want_warning_job_succeeded,
rules.want_warning_job_problems,
rules.want_critical_job_succeeded,
rules.want_critical_job_problems,
rules.want_policy_clears,
rules.want_policy_violations,
rules.want_policy_job_succeeded,
rules.want_policy_job_problems
FROM mgmt_notify_rule_configs rules,
mgmt_metrics metrics
WHERE rules.rule_name=rule_name_in
AND rules.owner=user_name_in
AND metrics.target_type=rules.target_type
AND metrics.metric_name=rules.metric_name
AND metrics.metric_column=rules.metric_column
AND metrics.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
AND (metric_label != ' ' AND column_label != ' ');
END IF;
END IF;
OPEN rules_metric_index_list_out FOR
SELECT distinct target_type,
metric_name,
metric_column,
key_value,
ck.key_part1_value,
ck.key_part2_value,
ck.key_part3_value,
ck.key_part4_value,
ck.key_part5_value
FROM mgmt_notify_rule_configs r,
mgmt_metrics_composite_keys ck
WHERE rule_name=rule_name_in
AND owner=user_name_in
AND ck.composite_key (+)= r.key_value;
-- get the policy related data
IF (language_in IS NOT NULL AND country_in IS NOT NULL)
THEN
OPEN rules_policy_list_out FOR
SELECT distinct policies.policy_name,
policies.policy_label_nlsid,
NVL(MGMT_MESSAGE.GET_MESSAGE(policies.policy_label_nlsid,
'POLICY',
language_in,
country_in,
policies.policy_name),
policies.policy_name) policy_label,
policies.policy_type,
category.category_name,
NVL(MGMT_MESSAGE.GET_MESSAGE(cs.category_name_nlsid,
'CATEGORY',
language_in,
country_in,
cs.category_name),
cs.category_name) category_label,
rules.want_policy_clears,
rules.want_policy_violations,
rules.want_policy_job_succeeded,
rules.want_policy_job_problems
FROM mgmt_notify_rule_configs rules,
mgmt_policies policies,
mgmt_categories cs,
mgmt_category_map category
WHERE rules.rule_name=rule_name_in
AND rules.owner=user_name_in
AND rules.target_type=l_target_type
AND rules.metric_name=policies.policy_name
AND rules.metric_column='%'
AND policies.target_type=l_target_type
AND policies.policy_guid=category.object_guid
AND cs.category_name=category.category_name
AND cs.class_name='Functional';
ELSE
OPEN rules_policy_list_out FOR
SELECT distinct policies.policy_name,
policies.policy_label_nlsid,
policies.policy_name policy_label,
policies.policy_type,
category.category_name,
category.category_name category_label,
rules.want_policy_clears,
rules.want_policy_violations,
rules.want_policy_job_succeeded,
rules.want_policy_job_problems
FROM mgmt_notify_rule_configs rules,
mgmt_policies policies,
mgmt_category_map category
WHERE rules.rule_name=rule_name_in
AND rules.owner=user_name_in
AND rules.target_type=l_target_type
AND rules.metric_name=policies.policy_name
AND rules.metric_column='%'
AND policies.target_type=l_target_type
AND policies.policy_guid=category.object_guid;
END IF;
-- get the job related data
OPEN rules_job_list_out FOR
SELECT distinct configs.job_name,
configs.job_owner,
configs.job_type,
info.job_type_category,
configs.want_job_scheduled,
configs.want_job_running,
configs.want_job_succeeded,
configs.want_job_suspended,
configs.want_job_problems
FROM mgmt_notify_job_rule_configs configs,
mgmt_job_type_info info
WHERE rule_name=rule_name_in
AND owner=user_name_in
AND target_type=l_target_type
AND configs.job_type=info.job_type(+);
-- get all key columns for this target type
OPEN key_metric_columns_out FOR
SELECT distinct metric_column, column_label, column_label_NLSID,
metric_name, metric_label, metric_label_NLSID,
key_column, is_renderable
FROM mgmt_metrics
WHERE target_type = l_target_type
AND usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
AND key_order != 0;
-- get the data for cursor - rules_email_notif_list_out
get_rule_email_notifyees( rule_name_in,
user_name_in,
rules_email_notif_list_out );
-- get the data for cursor - rules_device_notif_list_out
get_devices_list_for_rule( rule_name_in,
user_name_in,
rules_device_notif_list_out );
-- get the data for cursor - user_pref_email_list_out
get_user_pref_email( user_pref_email_list_out );
-- get the data for cursor - notif_devices_list_out
get_devices_list( notif_devices_list_out );
END get_notif_rule_detail_data;
-- ============================================================================
--
-- Name: get_notification_rule_details
--
-- Purpose:
-- Get the notification rule detail data for rule - rule_name_in of the
-- current user
--
-- ============================================================================
PROCEDURE get_notification_rule_details
(rule_name_in IN VARCHAR2,
rules_detail_list_out OUT CURSOR_TYPE,
rules_target_list_out OUT CURSOR_TYPE,
rules_metric_list_out OUT CURSOR_TYPE,
rules_metric_index_list_out OUT CURSOR_TYPE,
rules_policy_list_out OUT CURSOR_TYPE,
rules_job_list_out OUT CURSOR_TYPE,
rules_email_notif_list_out OUT CURSOR_TYPE,
rules_device_notif_list_out OUT CURSOR_TYPE,
user_pref_email_list_out OUT CURSOR_TYPE,
notif_devices_list_out OUT CURSOR_TYPE,
key_metric_columns_out OUT CURSOR_TYPE)
IS
l_user_name VARCHAR2(256) := '';
BEGIN
-- initialize OUT parameters
-- check for error conditions first
-- first get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- now figure out if rule - rule_name_in of user - l_user_name exists
check_rule_exists_error(rule_name_in,
l_user_name);
-- error conditions have been taken care of, so get the rule detail data
-- for rule - rule_name_in of user - l_user_name
get_notif_rule_detail_data( rule_name_in,
l_user_name,
NULL,
NULL,
rules_detail_list_out,
rules_target_list_out,
rules_metric_list_out,
rules_metric_index_list_out,
rules_policy_list_out,
rules_job_list_out,
rules_email_notif_list_out,
rules_device_notif_list_out,
user_pref_email_list_out,
notif_devices_list_out,
key_metric_columns_out);
END get_notification_rule_details;
-- ============================================================================
--
-- Name: get_notification_rule_details
--
-- Purpose:
-- Get the notification rule detail for rule - rule_name_in of user -
-- user_name_in
--
-- ============================================================================
PROCEDURE get_notification_rule_details
(rule_name_in IN VARCHAR2,
user_name_in IN VARCHAR2,
language_in IN VARCHAR2,
country_in IN VARCHAR2,
rules_detail_list_out OUT CURSOR_TYPE,
rules_target_list_out OUT CURSOR_TYPE,
rules_metric_list_out OUT CURSOR_TYPE,
rules_metric_index_list_out OUT CURSOR_TYPE,
rules_policy_list_out OUT CURSOR_TYPE,
rules_job_list_out OUT CURSOR_TYPE,
rules_email_notif_list_out OUT CURSOR_TYPE,
rules_device_notif_list_out OUT CURSOR_TYPE,
user_pref_email_list_out OUT CURSOR_TYPE,
notif_devices_list_out OUT CURSOR_TYPE,
key_metric_columns_out OUT CURSOR_TYPE)
IS
l_user_name VARCHAR2(256) := '';
BEGIN
-- initialize OUT parameters
-- check for error conditions first
-- check for the existence of the user(if supplied)
IF( (length(trim(user_name_in)) <> 0 ) )
THEN
-- check for the existence of this user
mgmt_user.check_user_exists(user_name_in);
l_user_name := user_name_in;
ELSE
-- user name was not passed in so get the notification rule detail data
-- for rule - rule_name_in of the currently logged in (privileged) user
l_user_name := mgmt_user.get_current_em_user();
END IF;
-- now figure out if rule - rule_name_in of user - l_user_name exists
check_rule_exists_error(rule_name_in,
l_user_name);
-- error conditions have been taken care of, so get the rule detail data
-- for rule - rule_name_in of user - l_user_name
get_notif_rule_detail_data( rule_name_in,
l_user_name,
language_in,
country_in,
rules_detail_list_out,
rules_target_list_out,
rules_metric_list_out,
rules_metric_index_list_out,
rules_policy_list_out,
rules_job_list_out,
rules_email_notif_list_out,
rules_device_notif_list_out,
user_pref_email_list_out,
notif_devices_list_out,
key_metric_columns_out);
END get_notification_rule_details;
-- ============================================================================
--
-- Name: get_notification_rule_details
--
-- Purpose:
-- Get the notification rule detail for rule - rule_name_in of user -
-- user_name_in
--
-- ============================================================================
PROCEDURE get_notification_rule_details
(rule_name_in IN VARCHAR2,
user_name_in IN VARCHAR2,
rules_detail_list_out OUT CURSOR_TYPE,
rules_target_list_out OUT CURSOR_TYPE,
rules_metric_list_out OUT CURSOR_TYPE,
rules_metric_index_list_out OUT CURSOR_TYPE,
rules_policy_list_out OUT CURSOR_TYPE,
rules_job_list_out OUT CURSOR_TYPE,
rules_email_notif_list_out OUT CURSOR_TYPE,
rules_device_notif_list_out OUT CURSOR_TYPE,
user_pref_email_list_out OUT CURSOR_TYPE,
notif_devices_list_out OUT CURSOR_TYPE,
key_metric_columns_out OUT CURSOR_TYPE)
IS
BEGIN
get_notification_rule_details(rule_name_in,
user_name_in,
NULL,
NULL,
rules_detail_list_out,
rules_target_list_out,
rules_metric_list_out,
rules_metric_index_list_out,
rules_policy_list_out,
rules_job_list_out,
rules_email_notif_list_out,
rules_device_notif_list_out,
user_pref_email_list_out,
notif_devices_list_out,
key_metric_columns_out);
END get_notification_rule_details;
-- ============================================================================
--
-- Name: create_notification_rule
--
-- Purpose:
-- Create a notification rule - rule_name_in for the current user
-- with the passed in values
--
--
-- ============================================================================
PROCEDURE create_notification_rule
(rule_name_in IN VARCHAR2,
rule_desc_in IN VARCHAR2,
rule_public_in IN NUMBER,
rule_target_type_in IN VARCHAR2,
rule_targets_in IN SMP_EMD_STRING_ARRAY,
want_clears_in IN NUMBER,
want_critical_alerts_in IN NUMBER,
want_warning_alerts_in IN NUMBER,
want_tgt_up_in IN NUMBER,
want_tgt_down_in IN NUMBER,
want_tgt_unreachable_start_in IN NUMBER,
want_tgt_unreachable_end_in IN NUMBER,
want_tgt_metric_err_start_in IN NUMBER,
want_tgt_metric_err_end_in IN NUMBER,
want_tgt_blkout_started_in IN NUMBER,
want_tgt_blkout_ended_in IN NUMBER,
rule_metric_names_in IN SMP_EMD_STRING_ARRAY,
rule_metric_columns_in IN SMP_EMD_STRING_ARRAY,
rule_metric_indexes_in IN SMP_EMD_NVPAIR_ARRAY,
send_email_in IN NUMBER,
email_address_in IN SMP_EMD_STRING_ARRAY,
device_names_in IN SMP_EMD_STRING_ARRAY,
device_types_in IN SMP_EMD_INTEGER_ARRAY,
device_not_avail_list_out OUT SMP_EMD_STRING_ARRAY,
email_gateway_setup_out OUT NUMBER,
rule_repeat_enabled_in IN NUMBER DEFAULT 0)
IS
l_metric_want_clears SMP_EMD_INTEGER_ARRAY;
l_metric_want_critical_alerts SMP_EMD_INTEGER_ARRAY;
l_metric_want_warning_alerts SMP_EMD_INTEGER_ARRAY;
l_want_warning_succeed SMP_EMD_INTEGER_ARRAY;
l_want_warning_problem SMP_EMD_INTEGER_ARRAY;
l_want_critical_succeed SMP_EMD_INTEGER_ARRAY;
l_want_critical_problem SMP_EMD_INTEGER_ARRAY;
l_count INTEGER := 0;
l_want_critical_alerts NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_want_warning_alerts NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_user_name VARCHAR2(256) := '';
BEGIN
-- Check the correctness of the input parameters
-- it's assumed that rule_metric_names_in and rule_metric_indexes_in has the same length. Verify this condition holds.
check_rule_input_param(rule_metric_names_in,rule_metric_indexes_in);
-- initialize OUT parameters
email_gateway_setup_out := MGMT_GLOBAL.G_FALSE;
device_not_avail_list_out := SMP_EMD_STRING_ARRAY();
-- get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- check for error conditions
check_notif_rule_errors( rule_name_in,
l_user_name,
CREATE_MODE );
-- error conditions have been taken care of, so create the rule - rule_name_in
-- for user - l_user_name
l_count := rule_metric_names_in.count;
l_metric_want_clears := SMP_EMD_INTEGER_ARRAY();
l_metric_want_critical_alerts := SMP_EMD_INTEGER_ARRAY();
l_metric_want_warning_alerts := SMP_EMD_INTEGER_ARRAY();
l_want_warning_succeed := SMP_EMD_INTEGER_ARRAY();
l_want_warning_problem := SMP_EMD_INTEGER_ARRAY();
l_want_critical_succeed := SMP_EMD_INTEGER_ARRAY();
l_want_critical_problem := SMP_EMD_INTEGER_ARRAY();
l_metric_want_clears.extend(l_count);
l_metric_want_critical_alerts.extend(l_count);
l_metric_want_warning_alerts.extend(l_count);
l_want_warning_succeed.extend(l_count);
l_want_warning_problem.extend(l_count);
l_want_critical_succeed.extend(l_count);
l_want_critical_problem.extend(l_count);
FOR i IN 1..l_count
LOOP
l_metric_want_clears(i) := NVL(want_clears_in, MGMT_GLOBAL.G_FALSE);
l_metric_want_critical_alerts(i) := NVL(want_critical_alerts_in, MGMT_GLOBAL.G_FALSE);
l_metric_want_warning_alerts(i) := NVL(want_warning_alerts_in, MGMT_GLOBAL.G_FALSE);
l_want_warning_succeed(i) := MGMT_GLOBAL.G_FALSE;
l_want_warning_problem(i) := MGMT_GLOBAL.G_FALSE;
l_want_critical_succeed(i) := MGMT_GLOBAL.G_FALSE;
l_want_critical_problem(i) := MGMT_GLOBAL.G_FALSE;
END LOOP;
create_edit_notif_rule( rule_name_in,
l_user_name,
rule_desc_in,
rule_public_in,
rule_target_type_in,
rule_targets_in,
null,
want_tgt_up_in,
want_tgt_down_in,
want_tgt_unreachable_start_in,
want_tgt_unreachable_end_in,
want_tgt_metric_err_start_in,
want_tgt_metric_err_end_in,
want_tgt_blkout_started_in,
want_tgt_blkout_ended_in,
MGMT_GLOBAL.G_FALSE,
rule_metric_names_in,
rule_metric_columns_in,
rule_metric_indexes_in,
l_metric_want_clears,
l_metric_want_critical_alerts,
l_metric_want_warning_alerts,
l_want_warning_succeed,
l_want_warning_problem,
l_want_critical_succeed,
l_want_critical_problem,
SMP_EMD_STRING_ARRAY(),
null,
null,
null,
null,
SMP_EMD_STRING_ARRAY(),
SMP_EMD_STRING_ARRAY(),
SMP_EMD_STRING_ARRAY(),
null,
null,
null,
null,
null,
send_email_in,
device_names_in,
device_types_in,
device_not_avail_list_out,
CREATE_MODE,
rule_repeat_enabled_in );
-- figure out if the mail gateway is setup
email_gateway_setup_out := EMD_NOTIFICATION.IS_MAIL_GATEWAY_SETUP();
END create_notification_rule;
-- ============================================================================
--
-- Name: create_notification_rule
--
-- Purpose:
-- Create a notification rule - rule_name_in for the current user
-- with the passed in values
--
--
-- ============================================================================
PROCEDURE create_notification_rule
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2,
rule_desc_in IN VARCHAR2,
rule_public_in IN NUMBER,
rule_target_type_in IN VARCHAR2,
rule_targets_in IN SMP_EMD_STRING_ARRAY,
target_types_in IN SMP_EMD_STRING_ARRAY,
want_tgt_up_in IN NUMBER,
want_tgt_down_in IN NUMBER,
want_tgt_unreachable_start_in IN NUMBER,
want_tgt_unreachable_end_in IN NUMBER,
want_tgt_metric_err_start_in IN NUMBER,
want_tgt_metric_err_end_in IN NUMBER,
want_tgt_blkout_started_in IN NUMBER,
want_tgt_blkout_ended_in IN NUMBER,
ignore_rca_in IN NUMBER,
rule_metric_names_in IN SMP_EMD_STRING_ARRAY,
rule_metric_columns_in IN SMP_EMD_STRING_ARRAY,
rule_metric_indexes_in IN SMP_EMD_NVPAIR_ARRAY,
metric_want_clears_in IN SMP_EMD_INTEGER_ARRAY,
metric_want_critical_alerts_in IN SMP_EMD_INTEGER_ARRAY,
metric_want_warning_alerts_in IN SMP_EMD_INTEGER_ARRAY,
want_warning_succeed_in IN SMP_EMD_INTEGER_ARRAY,
want_warning_problem_in IN SMP_EMD_INTEGER_ARRAY,
want_critical_succeed_in IN SMP_EMD_INTEGER_ARRAY,
want_critical_problem_in IN SMP_EMD_INTEGER_ARRAY,
policy_names_in IN SMP_EMD_STRING_ARRAY,
want_policy_violation_in IN SMP_EMD_INTEGER_ARRAY,
want_policy_clear_in IN SMP_EMD_INTEGER_ARRAY,
want_policy_job_succeed_in IN SMP_EMD_INTEGER_ARRAY,
want_policy_job_problem_in IN SMP_EMD_INTEGER_ARRAY,
job_names_in IN SMP_EMD_STRING_ARRAY,
job_owner_in IN SMP_EMD_STRING_ARRAY,
job_types_in IN SMP_EMD_STRING_ARRAY,
want_job_scheduled_in IN SMP_EMD_INTEGER_ARRAY,
want_job_started_in IN SMP_EMD_INTEGER_ARRAY,
want_job_suspended_in IN SMP_EMD_INTEGER_ARRAY,
want_job_succeeded_in IN SMP_EMD_INTEGER_ARRAY,
want_job_problem_in IN SMP_EMD_INTEGER_ARRAY,
send_email_in IN NUMBER,
device_names_in IN SMP_EMD_STRING_ARRAY,
device_types_in IN SMP_EMD_INTEGER_ARRAY,
device_not_avail_list_out OUT SMP_EMD_STRING_ARRAY,
email_gateway_setup_out OUT NUMBER,
rule_repeat_enabled_in IN NUMBER DEFAULT 0)
IS
l_want_clears NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_want_critical_alerts NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_want_warning_alerts NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_user_name VARCHAR2(256) := '';
BEGIN
-- Check the correctness of the input parameters
-- it's assumed that rule_metric_names_in and rule_metric_indexes_in has the same length. Verify this condition holds.
check_rule_input_param(rule_metric_names_in,rule_metric_indexes_in);
-- initialize OUT parameters
email_gateway_setup_out := MGMT_GLOBAL.G_FALSE;
device_not_avail_list_out := SMP_EMD_STRING_ARRAY();
-- get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- check for error conditions
check_notif_rule_errors( rule_name_in,
l_user_name,
CREATE_MODE );
create_edit_notif_rule( rule_name_in,
l_user_name,
rule_desc_in,
rule_public_in,
rule_target_type_in,
rule_targets_in,
target_types_in,
want_tgt_up_in,
want_tgt_down_in,
want_tgt_unreachable_start_in,
want_tgt_unreachable_end_in,
want_tgt_metric_err_start_in,
want_tgt_metric_err_end_in,
want_tgt_blkout_started_in,
want_tgt_blkout_ended_in,
ignore_rca_in,
rule_metric_names_in,
rule_metric_columns_in,
rule_metric_indexes_in,
metric_want_clears_in,
metric_want_critical_alerts_in,
metric_want_warning_alerts_in,
want_warning_succeed_in,
want_warning_problem_in,
want_critical_succeed_in,
want_critical_problem_in,
policy_names_in,
want_policy_violation_in,
want_policy_clear_in,
want_policy_job_succeed_in,
want_policy_job_problem_in,
job_names_in,
job_owner_in,
job_types_in,
want_job_scheduled_in,
want_job_started_in,
want_job_suspended_in,
want_job_succeeded_in,
want_job_problem_in,
send_email_in,
device_names_in,
device_types_in,
device_not_avail_list_out,
CREATE_MODE,
rule_repeat_enabled_in );
-- figure out if the mail gateway is setup
email_gateway_setup_out := EMD_NOTIFICATION.IS_MAIL_GATEWAY_SETUP();
END create_notification_rule;
-- ============================================================================
--
-- Name: update_notification_rule
--
-- Purpose:
-- Update the notification rule data for rule - rule_name_in of the currently
-- logged in user
--
-- This API will be used by a non-privileged user
--
-- ============================================================================
PROCEDURE update_notification_rule
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2,
rule_desc_in IN VARCHAR2,
rule_public_in IN NUMBER,
rule_target_type_in IN VARCHAR2,
rule_targets_in IN SMP_EMD_STRING_ARRAY,
target_types_in IN SMP_EMD_STRING_ARRAY,
want_tgt_up_in IN NUMBER,
want_tgt_down_in IN NUMBER,
want_tgt_unreachable_start_in IN NUMBER,
want_tgt_unreachable_end_in IN NUMBER,
want_tgt_metric_err_start_in IN NUMBER,
want_tgt_metric_err_end_in IN NUMBER,
want_tgt_blkout_started_in IN NUMBER,
want_tgt_blkout_ended_in IN NUMBER,
ignore_rca_in IN NUMBER,
rule_metric_names_in IN SMP_EMD_STRING_ARRAY,
rule_metric_columns_in IN SMP_EMD_STRING_ARRAY,
rule_metric_indexes_in IN SMP_EMD_NVPAIR_ARRAY,
metric_want_clears_in IN SMP_EMD_INTEGER_ARRAY,
metric_want_critical_alerts_in IN SMP_EMD_INTEGER_ARRAY,
metric_want_warning_alerts_in IN SMP_EMD_INTEGER_ARRAY,
want_warning_succeed_in IN SMP_EMD_INTEGER_ARRAY,
want_warning_problem_in IN SMP_EMD_INTEGER_ARRAY,
want_critical_succeed_in IN SMP_EMD_INTEGER_ARRAY,
want_critical_problem_in IN SMP_EMD_INTEGER_ARRAY,
policy_names_in IN SMP_EMD_STRING_ARRAY,
want_policy_violation_in IN SMP_EMD_INTEGER_ARRAY,
want_policy_clear_in IN SMP_EMD_INTEGER_ARRAY,
want_policy_job_succeed_in IN SMP_EMD_INTEGER_ARRAY,
want_policy_job_problem_in IN SMP_EMD_INTEGER_ARRAY,
job_names_in IN SMP_EMD_STRING_ARRAY,
job_owner_in IN SMP_EMD_STRING_ARRAY,
job_types_in IN SMP_EMD_STRING_ARRAY,
want_job_scheduled_in IN SMP_EMD_INTEGER_ARRAY,
want_job_started_in IN SMP_EMD_INTEGER_ARRAY,
want_job_suspended_in IN SMP_EMD_INTEGER_ARRAY,
want_job_succeeded_in IN SMP_EMD_INTEGER_ARRAY,
want_job_problem_in IN SMP_EMD_INTEGER_ARRAY,
send_email_in IN NUMBER,
device_names_in IN SMP_EMD_STRING_ARRAY,
device_types_in IN SMP_EMD_INTEGER_ARRAY,
device_not_avail_list_out OUT SMP_EMD_STRING_ARRAY,
email_gateway_setup_out OUT NUMBER,
rule_repeat_enabled_in IN NUMBER DEFAULT 0)
IS
l_want_clears NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_want_critical_alerts NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_want_warning_alerts NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_user_name VARCHAR2(256) := '';
BEGIN
-- initialize OUT parameters
email_gateway_setup_out := MGMT_GLOBAL.G_FALSE;
device_not_avail_list_out := SMP_EMD_STRING_ARRAY();
-- non-super user calls this procedure, so use the current em user and
-- ignore the passed in rule_owner_in
-- get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- check for error conditions
check_notif_rule_errors( rule_name_in,
l_user_name,
EDIT_MODE );
create_edit_notif_rule( rule_name_in,
l_user_name,
rule_desc_in,
rule_public_in,
rule_target_type_in,
rule_targets_in,
target_types_in,
want_tgt_up_in,
want_tgt_down_in,
want_tgt_unreachable_start_in,
want_tgt_unreachable_end_in,
want_tgt_metric_err_start_in,
want_tgt_metric_err_end_in,
want_tgt_blkout_started_in,
want_tgt_blkout_ended_in,
ignore_rca_in,
rule_metric_names_in,
rule_metric_columns_in,
rule_metric_indexes_in,
metric_want_clears_in,
metric_want_critical_alerts_in,
metric_want_warning_alerts_in,
want_warning_succeed_in,
want_warning_problem_in,
want_critical_succeed_in,
want_critical_problem_in,
policy_names_in,
want_policy_violation_in,
want_policy_clear_in,
want_policy_job_succeed_in,
want_policy_job_problem_in,
job_names_in,
job_owner_in,
job_types_in,
want_job_scheduled_in,
want_job_started_in,
want_job_suspended_in,
want_job_succeeded_in,
want_job_problem_in,
send_email_in,
device_names_in,
device_types_in,
device_not_avail_list_out,
EDIT_MODE,
rule_repeat_enabled_in );
-- figure out if the mail gateway is setup
email_gateway_setup_out := EMD_NOTIFICATION.IS_MAIL_GATEWAY_SETUP();
END update_notification_rule;
-- ============================================================================
--
-- Name: update_notification_rule_priv
--
-- Purpose:
-- Update the notification rule data for rule - rule_name_in
--
-- If rule_owner_in is specified and is valid, then the data for the
-- notification rule - rule_name_in of owner - rule_owner_in will be updated
-- ELSE the data for the notification rule - rule_name_in of the currently
-- logged in privileged user will be updated
--
-- ============================================================================
PROCEDURE update_notification_rule_priv
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2,
rule_desc_in IN VARCHAR2,
rule_public_in IN NUMBER,
rule_target_type_in IN VARCHAR2,
rule_targets_in IN SMP_EMD_STRING_ARRAY,
target_types_in IN SMP_EMD_STRING_ARRAY,
want_tgt_up_in IN NUMBER,
want_tgt_down_in IN NUMBER,
want_tgt_unreachable_start_in IN NUMBER,
want_tgt_unreachable_end_in IN NUMBER,
want_tgt_metric_err_start_in IN NUMBER,
want_tgt_metric_err_end_in IN NUMBER,
want_tgt_blkout_started_in IN NUMBER,
want_tgt_blkout_ended_in IN NUMBER,
ignore_rca_in IN NUMBER,
rule_metric_names_in IN SMP_EMD_STRING_ARRAY,
rule_metric_columns_in IN SMP_EMD_STRING_ARRAY,
rule_metric_indexes_in IN SMP_EMD_NVPAIR_ARRAY,
metric_want_clears_in IN SMP_EMD_INTEGER_ARRAY,
metric_want_critical_alerts_in IN SMP_EMD_INTEGER_ARRAY,
metric_want_warning_alerts_in IN SMP_EMD_INTEGER_ARRAY,
want_warning_succeed_in IN SMP_EMD_INTEGER_ARRAY,
want_warning_problem_in IN SMP_EMD_INTEGER_ARRAY,
want_critical_succeed_in IN SMP_EMD_INTEGER_ARRAY,
want_critical_problem_in IN SMP_EMD_INTEGER_ARRAY,
policy_names_in IN SMP_EMD_STRING_ARRAY,
want_policy_violation_in IN SMP_EMD_INTEGER_ARRAY,
want_policy_clear_in IN SMP_EMD_INTEGER_ARRAY,
want_policy_job_succeed_in IN SMP_EMD_INTEGER_ARRAY,
want_policy_job_problem_in IN SMP_EMD_INTEGER_ARRAY,
job_names_in IN SMP_EMD_STRING_ARRAY,
job_owner_in IN SMP_EMD_STRING_ARRAY,
job_types_in IN SMP_EMD_STRING_ARRAY,
want_job_scheduled_in IN SMP_EMD_INTEGER_ARRAY,
want_job_started_in IN SMP_EMD_INTEGER_ARRAY,
want_job_suspended_in IN SMP_EMD_INTEGER_ARRAY,
want_job_succeeded_in IN SMP_EMD_INTEGER_ARRAY,
want_job_problem_in IN SMP_EMD_INTEGER_ARRAY,
send_email_in IN NUMBER,
device_names_in IN SMP_EMD_STRING_ARRAY,
device_types_in IN SMP_EMD_INTEGER_ARRAY,
device_not_avail_list_out OUT SMP_EMD_STRING_ARRAY,
email_gateway_setup_out OUT NUMBER,
rule_repeat_enabled_in IN NUMBER DEFAULT 0)
IS
l_want_clears NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_want_critical_alerts NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_want_warning_alerts NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_user_name VARCHAR2(256) := '';
BEGIN
-- initialize OUT parameters
email_gateway_setup_out := MGMT_GLOBAL.G_FALSE;
device_not_avail_list_out := SMP_EMD_STRING_ARRAY();
-- get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- first, figure out if current user is a privileged user
check_super_user_priv_error();
-- check for the existence of the user(if supplied)
IF( (length(trim(rule_owner_in)) <> 0 ) )
THEN
-- check for the existence of this user
mgmt_user.check_user_exists(rule_owner_in);
l_user_name := rule_owner_in;
ELSE
-- user name was not passed in so update the notification rule data for the
-- rule - rule_name_in of currently logged in (privileged) user
l_user_name := mgmt_user.get_current_em_user();
END IF;
-- check for error conditions
check_notif_rule_errors( rule_name_in,
l_user_name,
EDIT_MODE );
create_edit_notif_rule( rule_name_in,
l_user_name,
rule_desc_in,
rule_public_in,
rule_target_type_in,
rule_targets_in,
target_types_in,
want_tgt_up_in,
want_tgt_down_in,
want_tgt_unreachable_start_in,
want_tgt_unreachable_end_in,
want_tgt_metric_err_start_in,
want_tgt_metric_err_end_in,
want_tgt_blkout_started_in,
want_tgt_blkout_ended_in,
ignore_rca_in,
rule_metric_names_in,
rule_metric_columns_in,
rule_metric_indexes_in,
metric_want_clears_in,
metric_want_critical_alerts_in,
metric_want_warning_alerts_in,
want_warning_succeed_in,
want_warning_problem_in,
want_critical_succeed_in,
want_critical_problem_in,
policy_names_in,
want_policy_violation_in,
want_policy_clear_in,
want_policy_job_succeed_in,
want_policy_job_problem_in,
job_names_in,
job_owner_in,
job_types_in,
want_job_scheduled_in,
want_job_started_in,
want_job_suspended_in,
want_job_succeeded_in,
want_job_problem_in,
send_email_in,
device_names_in,
device_types_in,
device_not_avail_list_out,
EDIT_MODE,
rule_repeat_enabled_in );
-- figure out if the mail gateway is setup
email_gateway_setup_out := EMD_NOTIFICATION.IS_MAIL_GATEWAY_SETUP();
END update_notification_rule_priv;
-- ============================================================================
-- a private plsql to delete the notif rule data
-- ============================================================================
PROCEDURE delete_notif_rule_data
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2)
IS
l_rule_emails SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
BEGIN
-- delete the rule rule_name_in of user - rule_owner_in
-- this will also delete the related rows from mgmt_notify_notifyees
DELETE
FROM mgmt_notify_rules
WHERE rule_name=rule_name_in
AND owner=rule_owner_in;
END delete_notif_rule_data;
-- ============================================================================
--
-- Name: delete_notification_rule
--
-- Purpose:
-- Delete the notification rule - rule_name_in of the currently logged in user
--
-- ============================================================================
PROCEDURE delete_notification_rule
(rule_name_in IN VARCHAR2)
IS
l_user_name VARCHAR2(256) := '';
BEGIN
-- check for error conditions
-- first get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- now figure out if rule - rule_name_in of user - l_user_name exists
check_rule_exists_error(rule_name_in,
l_user_name);
-- error conditions have been taken care of, so delete the rule
-- rule_name_in of user - l_user_name
delete_notif_rule_data( rule_name_in,
l_user_name );
END delete_notification_rule;
-- ============================================================================
--
-- Name: delete_notification_rule_priv
--
-- Purpose:
-- Delete the notification rule - rule_name_in of user - rule_owner_in
--
-- ============================================================================
PROCEDURE delete_notification_rule_priv
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2)
IS
l_user_name VARCHAR2(256) := '';
BEGIN
-- check for error conditions
-- first get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- first, figure out if current user is a privileged user
check_super_user_priv_error();
-- check for the existence of the user(if supplied)
IF( (length(trim(rule_owner_in)) <> 0 ) )
THEN
-- check for the existence of this user
mgmt_user.check_user_exists( rule_owner_in );
l_user_name := rule_owner_in;
ELSE
-- user name was not passed in so delete the notification rule - rule_name_in
-- of the currently logged in (privileged) user
l_user_name := mgmt_user.get_current_em_user();
END IF;
-- now figure out if rule - rule_name_in of user - l_user_name exists
check_rule_exists_error(rule_name_in,
l_user_name);
-- error conditions have been taken care of, so delete the rule
-- rule_name_in of user - l_user_name
delete_notif_rule_data( rule_name_in,
l_user_name );
END delete_notification_rule_priv;
-- ============================================================================
--
-- Name: update_rule_overview_data
--
-- Purpose:
-- Update the notification rule overview data for a set of rules
-- The overview data for the rules include the make public, send email,
-- send trap information for each rule in the set of rules
-- rule_name_in(1), rule_owner_in(1), rule_public_in(1), rule_email_in(1),
-- rule_snmp_in(1) are all associated to each other and are part of the
-- same rule
-- NOTE: This is a UI specific API
--
-- ============================================================================
PROCEDURE update_rule_overview_data
(rule_name_in IN SMP_EMD_STRING_ARRAY,
rule_owner_in IN SMP_EMD_STRING_ARRAY,
rule_public_in IN SMP_EMD_INTEGER_ARRAY,
send_email_in IN SMP_EMD_INTEGER_ARRAY)
IS
l_user_name VARCHAR2(256) := '';
l_user_priv_val INTEGER := 0;
l_count INTEGER := 0;
l_user_exists_val NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_rule_exists_val NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_email_address SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
CURSOR c_preferred_email_address IS
SELECT devices.email_address
FROM mgmt_notify_devices devices
WHERE devices.profile_name = l_user_name
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address;
BEGIN
-- initialize OUT parameters
-- check for the invalid rule data error
l_count := rule_name_in.COUNT;
IF( rule_owner_in.COUNT <> l_count OR rule_public_in.COUNT <> l_count )
THEN
-- invalid rule data, so throw an exception
RAISE MGMT_GLOBAL.invalid_rule_data;
END IF;
-- get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- now figure out if l_user_name is a privileged user
l_user_priv_val := MGMT_USER.has_priv(l_user_name, 'SUPER_USER');
-- get the preffered email addresses
OPEN c_preferred_email_address;
FETCH c_preferred_email_address
BULK COLLECT INTO l_email_address;
CLOSE c_preferred_email_address;
FOR i in 1..rule_name_in.COUNT
LOOP
-- first check if rule_owner_in(i) exists
IF( (length(trim(rule_owner_in(i))) <> 0 ) )
THEN
-- check for the existence of this user
mgmt_user.check_user_exists( rule_owner_in(i) );
END IF;
-- next check if rule - rule_name_in(i) of rule_owner_in(i) exists
check_rule_exists_error(rule_name_in(i), rule_owner_in(i));
-- now update the rule make public data only if l_user_name is the same as
-- rule_owner_in(i) or l_user_priv_val=1
IF( l_user_name=rule_owner_in(i) OR l_user_priv_val=1 )
THEN
EMD_NOTIFICATION.MAKE_RULE_PUBLIC( rule_name_in(i),
rule_owner_in(i),
rule_public_in(i) );
END IF;
-- update the send email data for rule - rule_name_in of user - rule_owner_in
IF( send_email_in(i)=MGMT_GLOBAL.G_TRUE )
THEN
-- add these email devices to the rule
FOR j IN 1..l_email_address.COUNT
LOOP
BEGIN
-- add the device to the rule - rule_name_in of user - rule_owner_in
emd_notification.add_device_to_rule( rule_name_in(i),
rule_owner_in(i),
l_email_address(j),
l_user_name );
EXCEPTION
-- ignore duplicate rows (could happen when editing a rule)
WHEN DUP_VAL_ON_INDEX THEN NULL;
END;
END LOOP;
ELSE
-- delete these email devices from the rule
FOR j IN 1..l_email_address.COUNT
LOOP
-- add the device to the rule - rule_name_in of user - rule_owner_in
emd_notification.delete_device_from_rule( rule_name_in(i),
rule_owner_in(i),
l_email_address(j),
l_user_name );
END LOOP;
END IF;
END LOOP;
END update_rule_overview_data;
-- ============================================================================
--
-- Name: get_rule_target_types
--
-- Purpose:
-- Get target types which could have availability, metrics, policies, or jobs
-- to create a notification rule
--
-- ============================================================================
PROCEDURE get_rule_target_types
(avail_target_type_out OUT CURSOR_TYPE,
metric_target_type_out OUT CURSOR_TYPE,
policy_target_type_out OUT CURSOR_TYPE,
job_target_type_out OUT CURSOR_TYPE )
IS
BEGIN
OPEN avail_target_type_out FOR
SELECT DISTINCT t.target_type
FROM mgmt_targets t,
mgmt_metrics m
WHERE t.target_type = m.target_type
ANd metric_name = 'Response'
AND metric_column = 'Status';
OPEN metric_target_type_out FOR
SELECT DISTINCT m.target_type
FROM mgmt_metrics m
WHERE NOT (m.metric_name='Response' AND
m.metric_column='Status')
AND (EXISTS (SELECT /*+ INDEX (p)*/ p.policy_guid
FROM mgmt_policy_assoc p
WHERE p.policy_type = 1 AND -- metric
p.policy_guid = m.metric_guid)
OR non_thresholded_alerts = 1);
OPEN policy_target_type_out FOR
SELECT DISTINCT t.target_type
FROM mgmt_targets t,
mgmt_policy_assoc pa
WHERE t.target_guid=pa.object_guid
AND pa.object_type=2 -- target
AND pa.policy_type=2; -- policies
OPEN job_target_type_out FOR
SELECT DISTINCT t.target_type
FROM mgmt_job_type_info i,
mgmt_job_single_target_types st,
mgmt_targets t
WHERE st.job_type_id = i.job_type_id
ANd t.target_type = single_target_type;
END get_rule_target_types;
-- ============================================================================
--
-- Name: get_rule_devices_data
--
-- Purpose:
-- Get the devices data for a rule creation. Note this is a UI specific plsql
-- and should be used with care.
--
-- ============================================================================
PROCEDURE get_rule_devices_data
(user_email_out OUT CURSOR_TYPE,
rule_devices_out OUT CURSOR_TYPE)
IS
BEGIN
-- get the email addresses data ...
get_user_pref_email( user_email_out );
-- get the available devices list
get_devices_list( rule_devices_out );
END get_rule_devices_data;
-- ============================================================================
--
-- Name: get_rule_config_devices_data
--
-- Purpose:
-- Get the rule devices data for rule - rule_name_in of the current user.
--
-- ============================================================================
PROCEDURE get_rule_config_devices_data
(rule_name_in IN VARCHAR2,
rule_email_out OUT CURSOR_TYPE,
user_pref_email_out OUT CURSOR_TYPE,
rule_devices_out OUT CURSOR_TYPE,
all_devices_out OUT CURSOR_TYPE)
IS
l_user_name VARCHAR2(256) := '';
BEGIN
-- initialize OUT parameters
-- check for error conditions first
-- first get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- now figure out if rule - rule_name_in of user - l_user_name exists
check_rule_exists_error(rule_name_in,
l_user_name);
-- error conditions have been taken care of, so get the rule detail data
-- for rule - rule_name_in of user - l_user_name
get_rule_config_dev_data( rule_name_in,
l_user_name,
rule_email_out,
user_pref_email_out,
rule_devices_out,
all_devices_out);
END get_rule_config_devices_data;
-- ============================================================================
--
-- Name: get_rule_config_devices_data
--
-- Purpose:
-- Get the rule devices data for rule - rule_name_in of the current user.
--
-- If the rule_owner_in is specified and is valid, then the rule detail data
-- for rule - rule_name_in of owner - rule_owner_in will be returned ELSE
-- the rule detail data for rule - rule_name_in of the current
-- user will be returned
--
-- Note that rule_email_out and user_pref_email_out cursors will be
-- determined in all case. However, rule_devices_out and all_devices_out
-- cursors will be determined only in the following conditions:
-- (current user = rule_owner_in) OR
-- (current user != rule_owner_in AND current user is privileged)
--
-- ============================================================================
PROCEDURE get_rule_config_devices_data
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2,
rule_email_out OUT CURSOR_TYPE,
user_pref_email_out OUT CURSOR_TYPE,
rule_devices_out OUT CURSOR_TYPE,
all_devices_out OUT CURSOR_TYPE)
IS
l_user_name VARCHAR2(256) := '';
BEGIN
-- initialize OUT parameters
-- check for error conditions first
-- check for the existence of the user(if supplied)
IF( (length(trim(rule_owner_in)) <> 0 ) )
THEN
-- check for the existence of this user
mgmt_user.check_user_exists( rule_owner_in );
l_user_name := rule_owner_in;
ELSE
-- user name was not passed in so get the notification rule detail data
-- for rule - rule_name_in of the currently logged in (privileged) user
l_user_name := mgmt_user.get_current_em_user();
END IF;
-- now figure out if rule - rule_name_in of user - l_user_name exists
check_rule_exists_error( rule_name_in,
l_user_name );
-- error conditions have been taken care of, so get the rule detail data
-- for rule - rule_name_in of user - l_user_name
get_rule_config_dev_data( rule_name_in,
l_user_name,
rule_email_out,
user_pref_email_out,
rule_devices_out,
all_devices_out);
END get_rule_config_devices_data;
-- ============================================================================
--
-- Name: update_rule_devices_data
--
-- Purpose:
-- Update the notification rule devices data for rule - rule_name_in of the
-- currently logged in user
--
-- ============================================================================
PROCEDURE update_rule_devices_data
(rule_name_in IN VARCHAR2,
send_email_in IN NUMBER,
device_names_in IN SMP_EMD_STRING_ARRAY,
device_types_in IN SMP_EMD_INTEGER_ARRAY,
device_not_avail_list_out OUT SMP_EMD_STRING_ARRAY,
email_gateway_setup_out OUT NUMBER)
IS
l_user_name VARCHAR2(256) := '';
BEGIN
-- initialize OUT parameters
email_gateway_setup_out := MGMT_GLOBAL.G_FALSE;
device_not_avail_list_out := SMP_EMD_STRING_ARRAY();
-- get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- check for error conditions
-- now figure out if rule - rule_name_in of user - l_user_name exists
check_rule_exists_error( rule_name_in,
l_user_name );
-- error conditions have been taken care of, so update the rule devices
-- data for rule - rule_name_in for user - l_user_name
update_rule_dev_data( rule_name_in,
l_user_name,
l_user_name,
send_email_in,
device_names_in,
device_types_in,
device_not_avail_list_out );
-- figure out if the mail gateway is setup
email_gateway_setup_out := EMD_NOTIFICATION.IS_MAIL_GATEWAY_SETUP();
END update_rule_devices_data;
-- ============================================================================
--
-- Name: update_rule_devices_data
--
-- Purpose:
-- Update the notification rule devices data for rule - rule_name_in of
-- user - rule_owner_in
--
-- If rule_owner_in is specified and is valid, then the data for the
-- notification rule - rule_name_in of owner - rule_owner_in will be updated
-- ELSE the data for the notification rule - rule_name_in of the currently
-- logged in user will be updated
--
-- Note that device_names_in and device_types_in cursors will be
-- used only in the following conditions:
-- (current user = rule_owner_in) OR
-- (current user != rule_owner_in AND current user is privileged)
--
-- ============================================================================
PROCEDURE update_rule_devices_data
(rule_name_in IN VARCHAR2,
rule_owner_in IN VARCHAR2,
send_email_in IN NUMBER,
device_names_in IN SMP_EMD_STRING_ARRAY,
device_types_in IN SMP_EMD_INTEGER_ARRAY,
device_not_avail_list_out OUT SMP_EMD_STRING_ARRAY,
email_gateway_setup_out OUT NUMBER)
IS
l_user_name VARCHAR2(256) := '';
l_rule_owner VARCHAR2(256) := '';
BEGIN
-- initialize OUT parameters
email_gateway_setup_out := MGMT_GLOBAL.G_FALSE;
device_not_avail_list_out := SMP_EMD_STRING_ARRAY();
-- check for error conditions first
-- check for the existence of the user(if supplied)
IF( (length(trim(rule_owner_in)) <> 0 ) )
THEN
-- check for the existence of this user
mgmt_user.check_user_exists( rule_owner_in );
l_rule_owner := rule_owner_in;
ELSE
-- user name was not passed in so get the notification rule detail data
-- for rule - rule_name_in of the currently logged in user
l_rule_owner := mgmt_user.get_current_em_user();
END IF;
-- now figure out if rule - rule_name_in of user - rule_owner_in exists
check_rule_exists_error( rule_name_in,
l_rule_owner );
l_user_name := mgmt_user.get_current_em_user();
-- error conditions have been taken care of, so update the rule devices data
-- for rule - rule_name_in for user - rule_owner_in
update_rule_dev_data( rule_name_in,
l_rule_owner,
l_user_name,
send_email_in,
device_names_in,
device_types_in,
device_not_avail_list_out );
-- figure out if the mail gateway is setup
email_gateway_setup_out := EMD_NOTIFICATION.IS_MAIL_GATEWAY_SETUP();
END update_rule_devices_data;
-- ============================================================================
--
-- Name: update_mail_gateway_data
--
-- PURPOSE: Update the mail gateway data. The user can enter more than one
-- mail gateway server names.
--
-- ============================================================================
PROCEDURE update_mail_gateway_data
(mail_gateway_server_array IN SMP_EMD_STRING_ARRAY,
mail_username_in IN VARCHAR2 DEFAULT NULL,
mail_password_in IN VARCHAR2 DEFAULT NULL,
mail_address_in IN VARCHAR2,
mail_name_in IN VARCHAR2,
mail_ssl_in IN VARCHAR2 DEFAULT NULL)
IS
BEGIN
-- check for error conditions first
-- figure out if current user is a privileged user
check_super_user_priv_error();
-- error conditions have been taken care of
-- set the mail gateway data ...
update_mail_gateways( mail_gateway_server_array,
mail_username_in,
mail_password_in,
mail_address_in,
mail_name_in,
mail_ssl_in );
END update_mail_gateway_data;
-- ============================================================================
--
-- Name: get_notif_devices_data
--
-- Purpose:
-- Get the notification devices data.
--
-- This API can be called only by a privileged user
--
-- This procedure returns the list if email gateways configured and also the
-- the list of notification devices(snmp device, os command, plsql device)
-- that are configured system wide
--
-- ============================================================================
PROCEDURE get_notif_devices_data
(mail_gateway_list_out OUT CURSOR_TYPE,
notif_devices_list_out OUT CURSOR_TYPE)
IS
BEGIN
-- check for error conditions first
-- figure out if current user is a privileged user
check_super_user_priv_error();
-- error conditions have been taken care of
-- get the mail gateway data ...
get_mail_gateways( mail_gateway_list_out );
-- get the notif devices data ...
get_devices_list( notif_devices_list_out );
END get_notif_devices_data;
-- ============================================================================
--
-- Name: edit_program_device
--
-- PURPOSE: Create/Edit the program device.
--
-- ============================================================================
PROCEDURE edit_program_device
(device_name_in IN VARCHAR2,
device_desc_in IN VARCHAR2,
os_command_name_in IN VARCHAR2,
os_command_args_in IN EMDSYSTEM_ARGS,
mode_in IN NUMBER)
IS
l_user_name VARCHAR2(256) := '';
l_device_owner VARCHAR2(256) := '';
BEGIN
-- check for error conditions first
-- figure out if current user is a privileged user
check_super_user_priv_error();
-- figure out if device - device_name exists
check_device_error( device_name_in,
mode_in );
-- error conditions have been taken care of
-- get the current user
l_user_name := mgmt_user.get_current_em_user();
-- create/edit the program device
IF( mode_in=CREATE_MODE )
THEN
-- add the program device
emd_notification.add_program_device( device_name_in,
l_user_name,
os_command_name_in,
device_desc_in,
os_command_args_in );
ELSE
-- first get the owner for device - device_name_in of type - PROGRAM_DEVICE_TYPE
l_device_owner := emd_notification.get_device_owner( device_name_in,
EMD_NOTIFICATION.PROGRAM_DEVICE_TYPE );
-- now edit the program device
emd_notification.edit_program_device( device_name_in,
l_device_owner,
os_command_name_in,
device_desc_in,
os_command_args_in );
END IF;
END edit_program_device;
-- ============================================================================
--
-- Name: get_program_device_data
--
-- PURPOSE: get the program device detail data. a list of all the rules that
-- use this program device is also returned
--
-- ============================================================================
PROCEDURE get_program_device_data
(device_name_in IN VARCHAR2,
device_data_out OUT CURSOR_TYPE,
device_rules_list_out OUT CURSOR_TYPE,
device_parameters_out OUT CURSOR_TYPE)
IS
BEGIN
-- check for error conditions first
-- figure out if current user is a privileged user
check_super_user_priv_error();
-- figure out if device - device_name exists
check_device_exists_error( device_name_in );
-- error conditions have been taken care of
-- now get the snmp device detail data
get_program_device_detail_data( device_name_in,
device_data_out );
-- get the list of rules associated with this snmp device
get_rules_list_for_device( device_name_in,
EMD_NOTIFICATION.PROGRAM_DEVICE_TYPE,
device_rules_list_out );
-- get the list of parameters associated with this device
OPEN device_parameters_out FOR
SELECT params.parameter,
params.position
FROM mgmt_notify_device_params params,
mgmt_notify_devices devices
WHERE params.device_name = device_name_in
AND params.profile_name = devices.profile_name
AND params.device_name = devices.device_name
AND devices.type = EMD_NOTIFICATION.PROGRAM_DEVICE_TYPE
ORDER BY params.position ASC;
END get_program_device_data;
-- ============================================================================
--
-- Name: delete_program_device_data
--
-- PURPOSE: delete the program device detail data.
--
-- ============================================================================
PROCEDURE delete_program_device_data
(device_name_in IN VARCHAR2)
IS
BEGIN
-- check for error conditions first
-- figure out if current user is a privileged user
check_super_user_priv_error();
-- figure out if device - device_name exists
check_device_exists_error( device_name_in );
-- error conditions have been taken care of
-- now delete the snmp device
delete_device( device_name_in,
EMD_NOTIFICATION.PROGRAM_DEVICE_TYPE );
END delete_program_device_data;
-- ============================================================================
--
-- Name: edit_plsql_device
--
-- PURPOSE: Create/Edit the plsql device.
--
-- ============================================================================
PROCEDURE edit_plsql_device
(device_name_in IN VARCHAR2,
device_desc_in IN VARCHAR2,
plsql_proc_name IN VARCHAR2,
mode_in IN NUMBER)
IS
l_user_name VARCHAR2(256) := '';
l_device_owner VARCHAR2(256) := '';
BEGIN
-- check for error conditions first
-- figure out if current user is a privileged user
check_super_user_priv_error();
-- figure out if device - device_name exists
check_device_error( device_name_in,
mode_in );
-- error conditions have been taken care of
-- get the current user
l_user_name := mgmt_user.get_current_em_user();
-- create/edit the plsql device
IF( mode_in=CREATE_MODE )
THEN
-- add the plsql device
emd_notification.add_plsql_device( device_name_in,
l_user_name,
plsql_proc_name,
device_desc_in );
ELSE
-- first get the owner for device - device_name_in of type - PLSQL_DEVICE_TYPE
l_device_owner := emd_notification.get_device_owner( device_name_in,
EMD_NOTIFICATION.PLSQL_DEVICE_TYPE );
-- now edit the plsql device
emd_notification.edit_plsql_device( device_name_in,
l_device_owner,
plsql_proc_name,
device_desc_in );
END IF;
END edit_plsql_device;
-- ============================================================================
--
-- Name: get_plsql_device_data
--
-- PURPOSE: get the plsql device detail data. a list of all the rules that
-- use this plsql device is also returned
--
-- ============================================================================
PROCEDURE get_plsql_device_data
(device_name_in IN VARCHAR2,
device_data_out OUT CURSOR_TYPE,
device_rules_list_out OUT CURSOR_TYPE)
IS
BEGIN
-- check for error conditions first
-- figure out if current user is a privileged user
check_super_user_priv_error();
-- figure out if device - device_name exists
check_device_exists_error( device_name_in );
-- error conditions have been taken care of
-- now get the snmp device detail data
get_plsql_device_detail_data( device_name_in,
device_data_out );
-- get the list of rules associated with this snmp device
get_rules_list_for_device( device_name_in,
EMD_NOTIFICATION.PLSQL_DEVICE_TYPE,
device_rules_list_out );
END get_plsql_device_data;
-- ============================================================================
--
-- Name: delete_plsql_device_data
--
-- PURPOSE: delete the plsql device detail data.
--
-- ============================================================================
PROCEDURE delete_plsql_device_data
(device_name_in IN VARCHAR2)
IS
BEGIN
-- check for error conditions first
-- figure out if current user is a privileged user
check_super_user_priv_error();
-- figure out if device - device_name exists
check_device_exists_error( device_name_in );
-- error conditions have been taken care of
-- now delete the snmp device
delete_device( device_name_in,
EMD_NOTIFICATION.PLSQL_DEVICE_TYPE );
END delete_plsql_device_data;
-- ============================================================================
--
-- Name: edit_snmp_device
--
-- PURPOSE: Create/Edit the snmp device.
--
-- ============================================================================
PROCEDURE edit_snmp_device
(device_name_in IN VARCHAR2,
device_desc_in IN VARCHAR2,
snmp_host_name_in IN VARCHAR2,
snmp_port_in IN NUMBER,
snmp_community_in IN VARCHAR2,
mode_in IN NUMBER)
IS
l_user_name VARCHAR2(256) := '';
l_device_owner VARCHAR2(256) := '';
BEGIN
-- check for error conditions first
-- figure out if current user is a privileged user
check_super_user_priv_error();
-- figure out if device - device_name exists
check_device_error( device_name_in,
mode_in );
-- error conditions have been taken care of
-- get the current user
l_user_name := mgmt_user.get_current_em_user();
-- create/edit the snmp device
IF( mode_in=CREATE_MODE )
THEN
-- add the snmp device
emd_notification.add_snmp_device( device_name_in,
l_user_name,
snmp_host_name_in,
snmp_port_in,
snmp_community_in,
device_desc_in );
ELSE
-- first get the owner for device - device_name_in of type - SNMP_DEVICE_TYPE
l_device_owner := emd_notification.get_device_owner( device_name_in,
EMD_NOTIFICATION.SNMP_DEVICE_TYPE );
-- now edit the snmp device
emd_notification.edit_snmp_device( device_name_in,
l_device_owner,
snmp_host_name_in,
snmp_port_in,
snmp_community_in,
device_desc_in );
END IF;
END edit_snmp_device;
-- ============================================================================
--
-- Name: get_snmp_device_data
--
-- PURPOSE: get the snmp device detail data. a list of all the rules that
-- use this plsql device is also returned
--
-- ============================================================================
PROCEDURE get_snmp_device_data
(device_name_in IN VARCHAR2,
device_data_out OUT CURSOR_TYPE,
device_rules_list_out OUT CURSOR_TYPE)
IS
BEGIN
-- check for error conditions first
-- figure out if current user is a privileged user
check_super_user_priv_error();
-- figure out if device - device_name exists
check_device_exists_error( device_name_in );
-- error conditions have been taken care of
-- now get the snmp device detail data
get_snmp_device_detail_data( device_name_in,
device_data_out );
-- get the list of rules associated with this snmp device
get_rules_list_for_device( device_name_in,
EMD_NOTIFICATION.SNMP_DEVICE_TYPE,
device_rules_list_out );
END get_snmp_device_data;
-- ============================================================================
--
-- Name: delete_snmp_device_data
--
-- PURPOSE: delete the snmp device detail data.
--
-- ============================================================================
PROCEDURE delete_snmp_device_data
(device_name_in IN VARCHAR2)
IS
BEGIN
-- check for error conditions first
-- figure out if current user is a privileged user
check_super_user_priv_error();
-- figure out if device - device_name exists
check_device_exists_error( device_name_in );
-- error conditions have been taken care of
-- now delete the snmp device
delete_device( device_name_in,
EMD_NOTIFICATION.SNMP_DEVICE_TYPE );
END delete_snmp_device_data;
-- ============================================================================
--
-- Name: get_java_device_data
--
-- PURPOSE: get the java device detail data. a list of all the rules that
-- use this java device is also returned
--
-- ============================================================================
PROCEDURE get_java_device_data
(device_name_in IN VARCHAR2,
device_data_out OUT CURSOR_TYPE,
device_parameter_out OUT CURSOR_TYPE,
device_rules_list_out OUT CURSOR_TYPE)
IS
BEGIN
-- check for error conditions first
-- figure out if current user is a privileged user
check_super_user_priv_error();
-- figure out if device - device_name exists
check_device_exists_error( device_name_in );
-- error conditions have been taken care of
-- now get the java device detail data
OPEN device_data_out FOR
SELECT device_name,
device_description,
type,
profile_name,
program
FROM mgmt_notify_devices
WHERE device_name = device_name_in
AND type = EMD_NOTIFICATION.JAVA_DEVICE_TYPE;
-- now get the java device parameter data
OPEN device_parameter_out FOR
SELECT params.parameter,
params.position
FROM mgmt_notify_device_params params,
mgmt_notify_devices devices
WHERE params.device_name = device_name_in
AND params.profile_name = devices.profile_name
AND params.device_name = devices.device_name
AND devices.type = EMD_NOTIFICATION.JAVA_DEVICE_TYPE
ORDER BY params.position ASC;
-- get the list of rules associated with this snmp device
get_rules_list_for_device( device_name_in,
EMD_NOTIFICATION.JAVA_DEVICE_TYPE,
device_rules_list_out );
END get_java_device_data;
-- ============================================================================
--
-- Name: get_assign_dev_to_rules_data
--
-- PURPOSE: get the data for whether a device is assigned to a rule. this data
-- is returned for all devices for all the rules of the current user.
--
-- NOTE: This API can be called by any user
--
-- ============================================================================
PROCEDURE get_assign_dev_to_rules_data
(mode_in IN NUMBER,
devices_data_out OUT CURSOR_TYPE)
IS
l_user_name VARCHAR2(256) := '';
BEGIN
-- first get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- get the assign dev to rules data for rules of user l_user_name
get_assign_data_for_user( l_user_name,
mode_in,
devices_data_out );
END get_assign_dev_to_rules_data;
-- ============================================================================
-- Name: get_assign_dev_to_rules_data
--
-- PURPOSE: get the data for whether a device is assigned to a rule.
--
-- If rule_owner_in is specified and is valid, then the data for all rules
-- of owner - rule_owner_in will be obtained.
-- ELSE the data for all rules for all users will be returned.
--
-- NOTE: This API should be called only by a super user.
--
-- ============================================================================
PROCEDURE get_assign_dev_to_rules_data
(rule_owner_in IN VARCHAR2,
mode_in IN NUMBER,
devices_data_out OUT CURSOR_TYPE)
IS
l_user_name VARCHAR2(256) := '';
l_all_users NUMBER(1) := MGMT_GLOBAL.G_FALSE;
BEGIN
-- check for error conditions
-- first get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- first, figure out if current user is a privileged user
check_super_user_priv_error();
-- check for the existence of the user(if supplied)
IF( (length(trim(rule_owner_in)) <> 0 ) )
THEN
-- check for the existence of this user
mgmt_user.check_user_exists( rule_owner_in );
l_user_name := rule_owner_in;
ELSE
-- user name was not passed in so delete the notification rule - rule_name_in
-- of the currently logged in (privileged) user
l_user_name := mgmt_user.get_current_em_user();
l_all_users := MGMT_GLOBAL.G_TRUE;
END IF;
-- error conditions have been taken care of
-- get the assign dev to rules data for rules of user l_user_name
IF( l_all_users = MGMT_GLOBAL.G_FALSE )
THEN
-- get the data for rules of user - l_user_name
get_assign_data_for_user( l_user_name,
mode_in,
devices_data_out );
ELSE
-- get the data for all rules of all users
get_assign_data_for_all_rules( mode_in,
devices_data_out );
END IF;
END get_assign_dev_to_rules_data;
-- ============================================================================
--
-- Name: update_assign_data
--
-- Purpose:
-- Update the notification rule devices data for the passed in array data
--
-- NOTE: rule_name_in(i), rule_owner_in(i), device_name_in(i), device_type_in(i) and
-- notify_in(i) indicate that if notify_in(i) is 1, then rule_name_in(i)
-- of rule_owner_in(i) will be notified using device_name_in(i) of type
-- device_type_in(i)
--
-- Note: This API can be used by any user
--
-- ============================================================================
PROCEDURE update_assign_data
(rule_name_in IN SMP_EMD_STRING_ARRAY,
rule_owner_in IN SMP_EMD_STRING_ARRAY,
device_name_in IN SMP_EMD_STRING_ARRAY,
device_type_in IN SMP_EMD_INTEGER_ARRAY,
notify_in IN SMP_EMD_INTEGER_ARRAY,
device_not_avail_list_out OUT SMP_EMD_STRING_ARRAY,
rule_not_avail_list_out OUT SMP_EMD_STRING_ARRAY,
user_not_avail_list_out OUT SMP_EMD_STRING_ARRAY)
IS
l_user_name VARCHAR2(256) := '';
l_user_priv_val INTEGER := 0;
l_user_exists_val NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_rule_exists_val NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_device_exists_val NUMBER(1) := MGMT_GLOBAL.G_FALSE;
l_user_not_avail_count NUMBER := 0;
l_rule_not_avail_count NUMBER := 0;
l_device_not_avail_count NUMBER := 0;
l_device_owner VARCHAR2(256) := '';
l_count INTEGER := 0;
BEGIN
-- initialize OUT parameters
device_not_avail_list_out := SMP_EMD_STRING_ARRAY();
rule_not_avail_list_out := SMP_EMD_STRING_ARRAY();
user_not_avail_list_out := SMP_EMD_STRING_ARRAY();
-- check for the invalid rule data error
l_count := rule_name_in.COUNT;
IF( rule_owner_in.COUNT <> l_count OR device_name_in.COUNT <> l_count OR
device_type_in.COUNT <> l_count OR notify_in.COUNT <> l_count )
THEN
-- invalid rule data, so throw an exception
RAISE MGMT_GLOBAL.invalid_rule_data;
END IF;
-- get the current user name from sys context
l_user_name := mgmt_user.get_current_em_user();
-- now figure out if l_user_name is a privileged user
l_user_priv_val := MGMT_USER.has_priv(l_user_name, 'SUPER_USER');
FOR i in 1..rule_name_in.COUNT
LOOP
-- first check if rule_owner_in(i) exists
BEGIN
l_user_exists_val := MGMT_GLOBAL.G_TRUE;
mgmt_user.check_user_exists( rule_owner_in(i) );
EXCEPTION
WHEN MGMT_GLOBAL.USER_DOES_NOT_EXIST
THEN
l_user_exists_val := MGMT_GLOBAL.G_FALSE;
END;
IF( l_user_exists_val = MGMT_GLOBAL.G_FALSE )
THEN
-- add this user name to user_not_avail_list_out array
user_not_avail_list_out.extend(1);
l_user_not_avail_count := l_user_not_avail_count + 1;
user_not_avail_list_out(l_user_not_avail_count) := rule_owner_in(i);
ELSE
-- check if the rule exists
l_rule_exists_val := rule_exists( rule_name_in(i), rule_owner_in(i) );
IF( l_rule_exists_val = MGMT_GLOBAL.G_FALSE )
THEN
-- add this rule name to rule_not_avail_list_out array
rule_not_avail_list_out.extend(1);
l_rule_not_avail_count := l_rule_not_avail_count + 1;
rule_not_avail_list_out(l_rule_not_avail_count) := rule_name_in(i);
ELSE
-- check if the device exists
l_device_exists_val := notif_device_exists( device_name_in(i) );
IF( l_device_exists_val = MGMT_GLOBAL.G_FALSE )
THEN
-- add this device name to device_not_avail_list_out
device_not_avail_list_out.extend(1);
l_device_not_avail_count := l_device_not_avail_count + 1;
device_not_avail_list_out(l_device_not_avail_count) := device_name_in(i);
ELSE
-- all error conditions have been taken care of
-- if notify_in(i) = 1, rule_name_in(i) of rule_owner_in(i) should
-- be notified by device_name_in(i) of device_type_in(i)
-- if notify_in(i) is 0 or 1, first delete this device from the
-- corresponsind rule. if notify_in(i) is 1, add the device to
-- the corresponding rule
-- first find the owner of device_name_in(i) of type device_type_in(i)
l_device_owner := EMD_NOTIFICATION.GET_DEVICE_OWNER( device_name_in(i),
device_type_in(i) );
-- delete device from rule always
EMD_NOTIFICATION.DELETE_DEVICE_FROM_RULE( rule_name_in(i),
rule_owner_in(i),
device_name_in(i),
l_device_owner );
-- add device to rule only if notify_in(i) = 1
IF( notify_in(i) = 1 )
THEN
EMD_NOTIFICATION.ADD_DEVICE_TO_RULE( rule_name_in(i),
rule_owner_in(i),
device_name_in(i),
l_device_owner );
END IF;
END IF;
END IF;
END IF;
END LOOP;
END update_assign_data;
-- ============================================================================
-- Name: subscribe_to_rules
--
-- PURPOSE: subscribe a user for a list of public notification rules
--
-- If rule_owner_in is specified and is valid, then the data for all rules
-- of owner - rule_owner_in will be obtained.
-- ELSE the data for all rules for all users will be returned.
--
-- NOTE: This API should be called only by a super user.
--
-- ============================================================================
PROCEDURE subscribe_to_rules
(user_name_in IN VARCHAR2,
rule_name_list_in IN SMP_EMD_STRING_ARRAY,
rule_owner_list_in IN SMP_EMD_STRING_ARRAY,
rule_not_avail_list_out OUT SMP_EMD_STRING_ARRAY)
IS
l_public NUMBER(1) := 0;
l_count NUMBER := 0;
l_email_address SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
CURSOR c_preferred_email_address IS
SELECT devices.email_address
FROM mgmt_notify_devices devices
WHERE devices.profile_name = user_name_in
AND devices.type IN (EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address;
BEGIN
-- first, figure out if current user is a privileged user
check_super_user_priv_error();
-- check for the existence of the user(if supplied)
mgmt_user.check_user_exists( user_name_in );
-- error conditions have been taken care of
-- initialize the out parameter
rule_not_avail_list_out := SMP_EMD_STRING_ARRAY();
-- get the preffered email addresses
OPEN c_preferred_email_address;
FETCH c_preferred_email_address
BULK COLLECT INTO l_email_address;
CLOSE c_preferred_email_address;
-- first, remove all rules for this user
DELETE FROM mgmt_notify_notifyees
WHERE profile_name = user_name_in
AND device_name IN (SELECT * FROM TABLE(CAST(l_email_address AS SMP_EMD_STRING_ARRAY)));
-- add rules in the list
FOR i IN 1..rule_name_list_in.COUNT
LOOP
l_public := 0;
BEGIN
SELECT public_rule
INTO l_public
FROM mgmt_notify_rules
WHERE rule_name = rule_name_list_in(i)
AND owner = rule_owner_list_in(i)
AND public_rule = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_count := l_count + 1;
rule_not_avail_list_out.extend();
rule_not_avail_list_out(l_count) := rule_name_list_in(i);
END;
IF (l_public = 1 )
THEN
FOR j IN 1..l_email_address.COUNT
LOOP
BEGIN
emd_notification.add_device_to_rule(rule_name_list_in(i),
rule_owner_list_in(i),
l_email_address(j),
user_name_in);
EXCEPTION
-- ignore duplicate rows
WHEN DUP_VAL_ON_INDEX THEN NULL;
END;
END LOOP;
END IF;
END LOOP;
END subscribe_to_rules;
-- ============================================================================
--
-- Name: get_schedule_defination
--
-- Purpose:
-- get the details for a notification schedule
--
-- NOTE:
--
-- Note:
--
-- ============================================================================
PROCEDURE get_schedule_defination
(owner_in IN VARCHAR2,
schedule_name_in IN VARCHAR2,
schedule_info_out OUT CURSOR_TYPE,
schedule_emails_out OUT CURSOR_TYPE,
avail_emails_out OUT CURSOR_TYPE)
IS
BEGIN
OPEN schedule_info_out FOR
SELECT start_date, num_weeks, timezone_region, updated_by, updated,
disable_start, disable_end
FROM mgmt_notify_schedules
WHERE schedule_owner = owner_in
AND schedule_name = schedule_name_in;
OPEN schedule_emails_out FOR
SELECT device_name, schedule
FROM mgmt_notify_dev_schedules
WHERE schedule_owner = owner_in
AND device_owner = owner_in;
OPEN avail_emails_out FOR
SELECT devices.email_address, devices.type
FROM mgmt_notify_devices devices
WHERE devices.profile_name = owner_in
AND devices.type IN (EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE,
EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE)
AND devices.device_name = devices.email_address;
END get_schedule_defination;
-- ============================================================================
--
-- Name: create_notification_schedule
--
-- Purpose:
-- Create a notification schedule
--
-- NOTE:
--
-- Note:
--
-- ============================================================================
PROCEDURE create_notification_schedule
(owner_in IN VARCHAR2,
schedule_name_in IN VARCHAR2,
schedule_weeks_in IN NUMBER,
schedule_start_date_in IN Date,
schedule_emails_in IN SMP_EMD_NVPAIR_ARRAY,
emails_not_avail_out OUT SMP_EMD_STRING_ARRAY)
IS
BEGIN
create_notification_schedule(
owner_in,
schedule_name_in,
schedule_weeks_in,
schedule_start_date_in,
null,
schedule_emails_in,
emails_not_avail_out);
END create_notification_schedule;
-- ============================================================================
--
-- Name: create_notification_schedule
--
-- Purpose:
-- Create a notification schedule
--
-- NOTE:
--
-- Note:
--
-- ============================================================================
PROCEDURE create_notification_schedule
(owner_in IN VARCHAR2,
schedule_name_in IN VARCHAR2,
schedule_weeks_in IN NUMBER,
schedule_start_date_in IN Date,
schedule_timezone_in IN VARCHAR2,
schedule_emails_in IN SMP_EMD_NVPAIR_ARRAY,
emails_not_avail_out OUT SMP_EMD_STRING_ARRAY)
IS
l_current_index NUMBER := 0;
BEGIN
emails_not_avail_out := SMP_EMD_STRING_ARRAY();
IF (schedule_timezone_in IS NULL)
THEN
emd_notification.set_schedule(owner_in,
schedule_name_in,
schedule_start_date_in,
schedule_weeks_in);
ELSE
emd_notification.set_schedule(owner_in,
schedule_name_in,
schedule_start_date_in,
schedule_weeks_in,
schedule_timezone_in);
END IF;
FOR i IN 1..schedule_emails_in.COUNT LOOP
l_current_index := l_current_index + 1;
emails_not_avail_out.extend(1);
emails_not_avail_out(l_current_index) := schedule_emails_in(i).name;
emd_notification.set_device_schedule(owner_in,
schedule_name_in,
schedule_emails_in(i).name,
owner_in,
schedule_emails_in(i).value);
END LOOP;
-- delete the schedule without any email address
IF (emd_notification.user_schedule_set(owner_in, schedule_name_in)=MGMT_GLOBAL.G_FALSE)
THEN
emd_notification.clear_schedule(owner_in, schedule_name_in);
END IF;
END create_notification_schedule;
-- ============================================================================
--
-- Name: test_plsql_device
--
-- Purpose:
-- Test the plsql device
--
-- NOTE: This API should be called only by a super user.
--
-- ============================================================================
FUNCTION test_plsql_device(plsql_in IN VARCHAR2,
err_msg_out OUT VARCHAR2)
RETURN NUMBER
IS
l_ret_val NUMBER := 1;
BEGIN
-- first, figure out if current user is a privileged user
check_super_user_priv_error();
-- test the program
l_ret_val := emd_notification.test_plsql_device( plsql_in,
err_msg_out );
RETURN l_ret_val;
END test_plsql_device;
END mgmt_preferences;
/
SHOW ERRORS;