Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\notification\notification

Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\notification\notification_pkgbodys.sql

Rem drv: <create type="pkgbodys" pos="rep_metric/rep_metric_pkgbody.sql+"/>
Rem
Rem $Header: notification_pkgbodys.sql 18-jul-2007.18:39:38 minfan Exp $
Rem
Rem notification_pkgbodys.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      notification_pkgbodys.sql
Rem
Rem    DESCRIPTION
Rem      This is the implementation of the Notification Manager for 
Rem      Oracle Enterprise Manager
Rem    NOTES
Rem      The Notification Manager is implemented using one DBMS JOB that calls
Rem      the CHECK_FOR_SEVERITIES procedure and another that calls the
Rem      CHECK_FOR_JOBS procedure. This procedures check for new notification
Rem      source events, match rules to them and queues notifications that are
Rem      delivered by one or more OMSs
Rem
Rem      RCA: for 10gR2 RCA tasks are added to the set of notification tasks
Rem           handled by this subsystem.  If the overhead of handling these
Rem           tasks impacts notifications adversely then they should be removed.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    minfan      07/18/07 - bug 6260471
Rem    denath      06/27/07 - Fix 5983944.proc ping devices modified for
Rem                           checking device in ping status along with down
Rem                           status and create a ping notification.
Rem    neearora    07/13/07 - Bug 6122386 
Rem    denath      07/05/07 - Bug fix 6082882.modified
Rem                           queue_metric_notification. passed
Rem                           violation.target_guid to devices cursor and use
Rem                           in place of c.target_guid while joining with
Rem                           MGMT_METRICS_COMPOSITE_KEYS.target_guid for
Rem                           wildcard key value metrics to get picked up.
Rem    neearora    06/21/07 - added validations in set_rule_repeat
Rem    neearora    06/20/07 - bug 6142074
Rem    minfan      06/15/07 - backport of bug 5852652
Rem    shnavane    04/06/07 - Fix bug #5850762
Rem    shnavane    06/13/07 - Backport shnavane_bug-5850762 from main
Rem    neearora    06/09/07 - bug 6122386
Rem    neearora    05/26/07 - bug 6067642
Rem    neearora    05/03/07 - added API to get set global repeat settings
Rem    smalathe    04/23/07 - Bug 5881705: Add a debug message
Rem    neearora    03/27/07 - added procedure QUEUE_NOTIF_INPUT
Rem    denath      07/26/06 - Fix Bug 5383506.Added check for policy violation 
Rem                           warning and information notifiications to be 
Rem                           sent in queue_metric_notifications. 
Rem    denath      03/18/07 - Backport denath_bug-5383506 from main
Rem    neearora    06/24/06 - bug 5045575. Modify GET_JOB_STATE_CHANGE_DETAILS
Rem                           to select target on the basis of execution id  
Rem    neearora    07/20/06 - Backport neearora_bug-5045575 from main 
Rem    rmaggarw    06/25/06 - bug5347519: sort by checkuser 
Rem    rmaggarw    07/10/06 - Backport rmaggarw_bug-5347519 from main 
Rem    neearora    10/21/05 - Added support for java notifications 
Rem    neearora    10/21/05 - Added support for java notifications 
Rem    rmaggarw    11/28/05 - use this_date instead of sysdate in 
Rem                           check_for_severities 
Rem    rmaggarw    12/05/05 - Backport rmaggarw_bug-4764102 from main 
Rem    dcawley     11/25/05 - Backport dcawley_bug-4607171 from main 
Rem    dcawley     09/13/05 - Default job type 
Rem    dcawley     11/25/05 - Backport dcawley_bug-4602660 from main 
Rem    dcawley     10/17/05 - Change summary message 
Rem    dcawley     11/25/05 - Remove check for OMS 
Rem    dcawley     09/24/05 - Remove plsql check when queuing 
Rem    dcawley     09/09/05 - Improve performance of rule matching 
Rem    dcawley     08/31/05 - Get job type label 
Rem    dcawley     08/05/05 - Use metric name for CAs not label 
Rem    dcawley     08/04/05 - Include occurred timestamp for jobs and cas 
Rem    dcawley     08/04/05 - Add logging 
Rem    dcawley     08/03/05 - Fix index for getting keys 
Rem    rmaggarw    07/29/05 - remove commit from failover 
Rem    dcawley     07/13/05 - Add callback for cleaning up keys 
Rem    smalathe    07/13/05 - Bug4487356: Use Select count 
Rem    scgrover    07/07/05 - add extended sql trace 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    dcawley     06/30/05 - Include aborted state for ca 
Rem    dcawley     06/21/05 - Change annotation for ca 
Rem    dcawley     06/16/05 - Do not include rca in summary 
Rem    dcawley     04/04/05 - Fix sql injection problems 
Rem    dcawley     03/24/05 - Set additional gateway parameters 
Rem    dcawley     03/24/05 - Sepcify escape character for rule matching 
Rem    dcawley     03/22/05 - Fix cleanup 
Rem    dcawley     03/15/05 - Support wild cards for composites 
Rem    jriel       03/15/05 - queue rca task with normal type 
Rem    dcawley     03/10/05 - Reorder columns 
Rem    pkantawa    03/09/05 - exclude jobs in delete pending state 
Rem    dcawley     02/24/05 - Increase queue name size 
Rem    dcawley     02/24/05 - Change get repository owner 
Rem    dcawley     02/24/05 - Fix timezone problem when logging delivery times 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    rzazueta    02/04/05 - Prepend queue_name with schema name 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ramalhot    02/02/05 - changed signature for handle_target_deleted 
Rem    dcawley     01/28/05 - Use policy name 
Rem    dcawley     01/11/05 - Allow registration without e-mail addresses 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    dcawley     01/11/05 - Rename callback 
Rem    dcawley     01/06/05 - Add target deletion callback 
Rem    dcawley     01/10/05 - Upper case job details 
Rem    dcawley     01/05/05 - Fix test routine for plsql method 
Rem    dcawley     12/22/04 - Truncate minutes when checking schedule 
Rem    dcawley     12/20/04 - Catch errors with timezone 
Rem    dcawley     12/20/04 - Fix rule for rca 
Rem    dcawley     12/16/04 - Add RCA notification type 
Rem    dcawley     12/14/04 - Include policy flags when matching rules 
Rem    dcawley     12/09/04 - Change annotation values 
Rem    dcawley     12/07/04 - Delete job configs 
Rem    jriel       11/18/04 - don't filter RCA during cleanup 
Rem    dcawley     11/16/04 - Pass back nlsid for violation details 
Rem    dcawley     10/28/04 - Cleanup old administrative messages 
Rem    dcawley     10/27/04 - Add api for deleting a specific configuration 
Rem    jriel       10/11/04 - only handle new severities 
Rem    dcawley     10/11/04 - RCA changes to rule 
Rem    jriel       09/28/04 - add proc to queue rca directly 
Rem    dcawley     09/27/04 - Queue corrective action 
Rem    dcawley     09/15/04 - Switch to violations table 
Rem    dcawley     09/01/04 - Use job status buckets 
Rem    jriel       08/31/04 - add target guid to notification for RCA 
Rem    dcawley     08/19/04 - Register OS of OMS 
Rem    dcawley     08/11/04 - Do not notify user's without target priv 
Rem    jriel       08/06/04 - add queue rca 
Rem    jriel       07/20/04 - add RCA 
Rem    dcawley     07/22/04 - Allow states per metric 
Rem    dcawley     07/21/04 - Change target table for jobs 
Rem    dcawley     07/15/04 - Add corrective actions 
Rem    dcawley     07/12/04 - Add procedure for getting job state details 
Rem    dcawley     07/07/04 - Increase user name size 
Rem    dcawley     06/29/04 - Remove tracing 
Rem    dcawley     06/14/04 - Changes for 10gR2 
Rem    aholser     05/13/04 - enable method performance logging 
Rem    dcawley     04/15/04 - remove send mail 
Rem    dcawley     03/25/04 - Add timezone to schedule 
Rem    yaofeng     02/07/04 - device name length 
Rem    yaofeng     02/05/04 - use target name 
Rem    dcawley     01/27/04 - Fix schedule for when Sunday is first day 
Rem    dcawley     01/26/04 - Log problems with metric versioning 
Rem    dcawley     12/19/03 - Remove retry recirds 
Rem    dcawley     11/25/03 - Do not send emails when there is no schedule 
Rem    yaofeng     11/12/03 - show date in NLS format 
Rem    yaofeng     11/11/03 - don't use . * 
Rem    dcawley     10/16/03 - Fix host label lookup 
Rem    dcawley     09/25/03 - Add key column name 
Rem    dcawley     09/18/03 - Add key value guid for plsql notifications
Rem    dcawley     09/08/03 - Use timezone_region 
Rem    dcawley     09/03/03 - Annotations should be target timezone 
Rem    dcawley     08/27/03 - Use upper case for schedule owner 
Rem    dcawley     08/27/03 - Clear old parameters when editing a command 
Rem    rpinnama    07/25/03 - 
Rem    rpinnama    07/25/03 - Move queue initialization to post_creation
Rem    dcawley     07/23/03 - Log device specific delivery times
Rem    dcawley     07/18/03 - Change annotations
Rem    dcawley     07/09/03 - Move queue initialization
Rem    dcawley     06/26/03 - Add UNREGISTER_OMS
Rem    dcawley     06/25/03 - Check for missing guids
Rem    dcawley     05/21/03 - Update for moving delivery to OMS
Rem

CREATE OR REPLACE PACKAGE BODY EMD_NOTIFICATION
IS

-- Queues sizes, these are read from MGMT_PARAMETERS
NUM_EMAIL_QUEUES number := DEFAULT_NOTIFY_EMAIL_QUEUES;
NUM_OSCMD_QUEUES number := DEFAULT_NOTIFY_OSCMD_QUEUES;
NUM_PLSQL_QUEUES number := DEFAULT_NOTIFY_PLSQL_QUEUES;
NUM_SNMP_QUEUES number := DEFAULT_NOTIFY_SNMP_QUEUES;
NUM_RCA_QUEUES number := DEFAULT_NOTIFY_RCA_QUEUES;
NUM_JAVA_QUEUES number := DEFAULT_NOTIFY_JAVA_QUEUES;
G_DEV_PING_STATE_TIMEOUT number := DEF_DEV_PING_STATE_TIMEOUT;

-- A cache of device availability. Entries are of the form:
--   <device_name><device_owner>
AVAILABLE_DEVICES SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
UNAVAILABLE_DEVICES SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();

-- Work out what monday is using a well known data, so schedule starts 
-- can be calculated
MONDAY NUMBER := TO_CHAR(TO_DATE('05-01-2004', 'dd-mm-yyyy'), 'D');

-- Performance data
NUM_EMAILS_QUEUED number := 0;
NUM_OSCMDS_QUEUED number := 0;
NUM_PLSQLS_QUEUED number := 0;
NUM_SNMPS_QUEUED number := 0;
NUM_JAVA_QUEUED number := 0;
TOTAL_ENQUEUE_TIME number := 0;

NUM_JOB_EMAILS_QUEUED number := 0;
NUM_JOB_OSCMDS_QUEUED number := 0;
NUM_JOB_PLSQLS_QUEUED number := 0;
NUM_JOB_SNMPS_QUEUED number := 0;
NUM_JOB_JAVA_QUEUED number := 0;
NUM_RCAS_QUEUED number := 0;
TOTAL_JOB_ENQUEUE_TIME number := 0;

OLDERR number := 0;
OLDERRTIME DATE;

G_RECURSIVE_CLEANUP BOOLEAN := FALSE;

-- Repository owner
G_REPOSITORY_OWNER VARCHAR2(30) := NULL;
G_QUEUE_NAME VARCHAR2(50) := NULL;
G_NOTIFY_INPUT_Q_NAME VARCHAR2(50) := NULL;

-- Logging context
G_LOG_CONTEXT VARCHAR2(15) := 'NOTIFICATION';

-- The escape character for matching strings in rules
G_ESC VARCHAR2(1) := '\';

FUNCTION GET_MILLISEC_FROM_INTERVAL(i INTERVAL DAY TO SECOND)
RETURN NUMBER
IS
  duration_sec NUMBER; 
BEGIN
  duration_sec :=  (extract (day from i)) * 24 * 60 * 60 +
                   (extract (hour from i)) * 60 * 60   +
                   (extract (minute from i)) * 60   +
                   (extract (second from i));

return duration_sec * 1000;  
END GET_MILLISEC_FROM_INTERVAL;

FUNCTION IS_WINDOWS_PROGRAM(v_program IN VARCHAR2)
RETURN BOOLEAN
IS
  l_program VARCHAR2(512);
BEGIN
  l_program := UPPER(v_program);
                                                                                
  -- Check if this is a Windows program
  IF INSTR(l_program, '\') > 0 OR
     SUBSTR(l_program, 2, 1) = ':' OR
     INSTR(l_program, '.BAT') > 0 OR
     INSTR(l_program, '.CMD') > 0 OR
     INSTR(l_program, '.EXE') > 0
  THEN
    RETURN TRUE;
  END IF;
                                                                                
  RETURN FALSE;
                                                                                
END IS_WINDOWS_PROGRAM;

-- PURPOSE
--  Procedure to set a configuration value
PROCEDURE SET_CONFIG_VALUE(p_name IN VARCHAR2, p_value IN VARCHAR2)
IS
BEGIN
  UPDATE mgmt_parameters
  SET parameter_value = p_value
  WHERE parameter_name = p_name;

IF (SQL%ROWCOUNT = 0) THEN
      INSERT INTO mgmt_parameters (parameter_name, parameter_value)
      VALUES (p_name, p_value);
  END IF;
END SET_CONFIG_VALUE;

-- PURPOSE
--   Function to check the availabilty of a device based on its notification
--   schedule
FUNCTION DEVICE_AVAILABLE(v_device_name   IN VARCHAR2,
                          v_profile_name  IN VARCHAR2,
                          v_time_to_check IN DATE DEFAULT SYSDATE)
RETURN BOOLEAN
IS
  dev_type NUMBER;
  sd DATE;
  disable_s DATE;
  disable_e DATE;
  num_wks NUMBER := 1;
  hour NUMBER;
  day NUMBER;
  adj_hour NUMBER;
  available VARCHAR2(1);
  log_msg     VARCHAR2(512);
  cache_entry VARCHAR2(256);
  timezone_rgn VARCHAR2(64);
  adj_time_to_check DATE := v_time_to_check;
BEGIN

-- Do not check schedule for non-email devices
  BEGIN
     SELECT type INTO dev_type FROM MGMT_NOTIFY_DEVICES
     WHERE device_name = v_device_name AND
           profile_name = v_profile_name;

IF dev_type <> LONG_EMAIL_DEVICE_TYPE AND
        dev_type <> SHORT_EMAIL_DEVICE_TYPE
     THEN
       RETURN TRUE;
     END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    -- No device found so return false
    RETURN FALSE;
  END;

-- Check the cache
  BEGIN
    SELECT column_value INTO cache_entry FROM 
    TABLE(CAST(AVAILABLE_DEVICES AS SMP_EMD_STRING_ARRAY))
    WHERE column_value = v_device_name || v_profile_name;
    RETURN TRUE;
  EXCEPTION WHEN NO_DATA_FOUND
  THEN
    BEGIN
      SELECT column_value INTO cache_entry FROM 
      TABLE(CAST(UNAVAILABLE_DEVICES AS SMP_EMD_STRING_ARRAY))
      WHERE column_value = v_device_name || v_profile_name;
      RETURN FALSE;
    EXCEPTION WHEN NO_DATA_FOUND
    THEN
      -- this is the first time checking this device's schedule
      NULL;
    END;
  END;

-- Get the start date, length and disable dates of the user's schedule
  BEGIN
    SELECT start_date, num_weeks, disable_start, disable_end, timezone_region 
    INTO sd, num_wks, disable_s, disable_e, timezone_rgn
    FROM MGMT_NOTIFY_SCHEDULES
    WHERE schedule_owner = v_profile_name 
      AND schedule_name = ' ';

EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    -- No schedule found so return false
    RETURN FALSE;
  END;

-- Adjust the time to check based on the user's timezone
  adj_time_to_check := MGMT_GLOBAL.ADJUST_TZ(v_time_to_check,
                                             TO_CHAR(SYSTIMESTAMP,'TZR'),
                                             timezone_rgn);

-- Make sure we are after the start date
  IF adj_time_to_check < sd
  THEN
    UNAVAILABLE_DEVICES.EXTEND;
    UNAVAILABLE_DEVICES(UNAVAILABLE_DEVICES.COUNT) := v_device_name || 
                                                      v_profile_name;
    RETURN FALSE;
  END IF;

-- Make sure the schedule is not disabled
  IF SYSDATE >= disable_s AND SYSDATE <= disable_e
  THEN
    UNAVAILABLE_DEVICES.EXTEND;
    UNAVAILABLE_DEVICES(UNAVAILABLE_DEVICES.COUNT) := v_device_name || 
                                                      v_profile_name;
    RETURN FALSE;
  END IF;

-- Get the day of the start date. Note Sunday is 1, Monday 2 etc. The UI
  -- always shows Monday as the first day even if the start date happens to
  -- be a Tuesday. So the effective start date of a schedule is the preceeding
  -- Monday since the first 24 characters of the schedule array in 
  -- MGMT_NOTIFY_DEV_SCHEDULES will be for the Monday
  day := TO_CHAR(sd, 'D');

IF day >= MONDAY
  THEN
    sd := sd - (day - MONDAY);
  ELSE
    sd := sd - (7 - (MONDAY - day));
  END IF;

-- Remove the minutes from the time to check to avoid any rounding errors
  adj_time_to_check := TRUNC(adj_time_to_check, 'HH');

-- Get the hour
  hour := ROUND((adj_time_to_check - sd) * 24);

-- If the current hour is greater than the number of hours in the schedule
  IF hour > (num_wks * 7 * 24)
  THEN
    adj_hour := mod(hour, (num_wks * 7 * 24)) + 1; 
  ELSE
    adj_hour := hour + 1;
  END IF;

BEGIN
    -- Check the schedule for the device
    SELECT SUBSTR(schedule, adj_hour, 1) INTO available 
    FROM MGMT_NOTIFY_DEV_SCHEDULES
    WHERE schedule_owner = v_profile_name 
      AND schedule_name = ' '
      AND device_name = v_device_name 
      AND device_owner = v_profile_name;
  
    IF available = 'Y'
    THEN
      AVAILABLE_DEVICES.EXTEND;
      AVAILABLE_DEVICES(AVAILABLE_DEVICES.COUNT) := v_device_name || 
                                                    v_profile_name;
      RETURN TRUE;
    ELSE
      UNAVAILABLE_DEVICES.EXTEND;
      UNAVAILABLE_DEVICES(UNAVAILABLE_DEVICES.COUNT) := v_device_name || 
                                                        v_profile_name;
      RETURN FALSE;
    END IF;

EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    -- This device does not exist in the schedule
    UNAVAILABLE_DEVICES.EXTEND;
    UNAVAILABLE_DEVICES(UNAVAILABLE_DEVICES.COUNT) := v_device_name || 
                                                      v_profile_name;
    RETURN FALSE;
  END;

END DEVICE_AVAILABLE;

-- PURPOSE
--   Function used for testing DEVICE_AVAILABLE
FUNCTION IS_DEVICE_AVAILABLE(v_device_name   IN VARCHAR2,
                             v_profile_name  IN VARCHAR2,
                             v_time_to_check IN DATE)
RETURN BOOLEAN
IS
BEGIN
  UNAVAILABLE_DEVICES := SMP_EMD_STRING_ARRAY();
  AVAILABLE_DEVICES := SMP_EMD_STRING_ARRAY();
  RETURN DEVICE_AVAILABLE(v_device_name, UPPER(v_profile_name),
                          v_time_to_check);
END IS_DEVICE_AVAILABLE;

-- PURPOSE
--  Function to read a configuration value
FUNCTION GET_CONFIG_VALUE(pname IN VARCHAR2, default_value IN VARCHAR2)
RETURN VARCHAR2
IS
  value VARCHAR2(256);
BEGIN

SELECT parameter_value INTO value FROM MGMT_PARAMETERS
  WHERE parameter_name = pname;

RETURN value;

EXCEPTION
WHEN NO_DATA_FOUND
THEN

RETURN default_value;
 
END GET_CONFIG_VALUE;

-- PURPOSE
--  To initialize the queue table and get the number of queues which 
--  can be using in mapping user/object combinations to a queue
--
-- NOTES
--   The number of queues remains fixed once they have been initialized. To
--   modify the number of queues, the SET_NUMBER_QUEUES procedure must be 
--   called which results in the temporary disabling of the queues while 
---  reassignment tables place
PROCEDURE INITIALIZE_QUEUES
IS
BEGIN

-- Get the number of queues 
  SELECT COUNT(qname) INTO NUM_EMAIL_QUEUES FROM MGMT_NOTIFY_QUEUES
  WHERE qname LIKE 'EMAIL%';

IF NUM_EMAIL_QUEUES = 0
  THEN
    NUM_EMAIL_QUEUES := GET_CONFIG_VALUE(NOTIFY_EMAIL_QUEUES, 
                                         DEFAULT_NOTIFY_EMAIL_QUEUES);
    -- Insert the queue names
    FOR i IN 1..NUM_EMAIL_QUEUES
    LOOP
      INSERT INTO MGMT_NOTIFY_QUEUES (qname) VALUES ('EMAIL' || i);
    END LOOP;
  END IF;

SELECT COUNT(qname) INTO NUM_OSCMD_QUEUES FROM MGMT_NOTIFY_QUEUES
  WHERE qname LIKE 'OSCMD%';

IF NUM_OSCMD_QUEUES = 0
  THEN
    NUM_OSCMD_QUEUES := GET_CONFIG_VALUE(NOTIFY_OSCMD_QUEUES, 
                                         DEFAULT_NOTIFY_OSCMD_QUEUES);
    FOR i IN 1..NUM_OSCMD_QUEUES
    LOOP
      INSERT INTO MGMT_NOTIFY_QUEUES (qname) VALUES ('OSCMD' || i);
    END LOOP;
  END IF;

SELECT COUNT(qname) INTO NUM_PLSQL_QUEUES FROM MGMT_NOTIFY_QUEUES
  WHERE qname LIKE 'PLSQL%';

IF NUM_PLSQL_QUEUES = 0
  THEN
    NUM_PLSQL_QUEUES := GET_CONFIG_VALUE(NOTIFY_PLSQL_QUEUES, 
                                         DEFAULT_NOTIFY_PLSQL_QUEUES);

FOR i IN 1..NUM_PLSQL_QUEUES
    LOOP
      INSERT INTO MGMT_NOTIFY_QUEUES (qname) VALUES ('PLSQL' || i);
    END LOOP;
  END IF;

SELECT COUNT(qname) INTO NUM_SNMP_QUEUES FROM MGMT_NOTIFY_QUEUES
  WHERE qname LIKE 'SNMP%';

IF NUM_SNMP_QUEUES = 0
  THEN
    NUM_SNMP_QUEUES := GET_CONFIG_VALUE(NOTIFY_SNMP_QUEUES, 
                                        DEFAULT_NOTIFY_SNMP_QUEUES);

FOR i IN 1..NUM_SNMP_QUEUES
    LOOP
      INSERT INTO MGMT_NOTIFY_QUEUES (qname) VALUES ('SNMP' || i);
    END LOOP;
  END IF;

SELECT COUNT(qname) INTO NUM_JAVA_QUEUES FROM MGMT_NOTIFY_QUEUES
  WHERE qname LIKE 'JAVA%';
  
  IF NUM_JAVA_QUEUES = 0
  THEN
    NUM_JAVA_QUEUES := GET_CONFIG_VALUE(NOTIFY_JAVA_QUEUES, 
                                        DEFAULT_NOTIFY_JAVA_QUEUES);

FOR i IN 1..NUM_JAVA_QUEUES
    LOOP
      INSERT INTO MGMT_NOTIFY_QUEUES (qname) VALUES ('JAVA' || i);
    END LOOP;
  END IF;

-- for 10gR2 RCA will only support a single queue.  This is due to the fact
-- that RCA tasks may have to be synchronized under some circumstances and
-- there is not support to do this in 10gR2 across OMSs, so a single RCA
-- queue forces all tasks to run on a single OMS

SELECT COUNT(qname) INTO NUM_RCA_QUEUES FROM MGMT_NOTIFY_QUEUES
  WHERE qname LIKE 'RCA%';
  
  IF NUM_RCA_QUEUES = 0
  THEN
    NUM_RCA_QUEUES := GET_CONFIG_VALUE(NUM_RCA_QUEUES, 
                                        DEFAULT_NOTIFY_RCA_QUEUES);

FOR i IN 1..NUM_RCA_QUEUES
    LOOP
      INSERT INTO MGMT_NOTIFY_QUEUES (qname) VALUES ('RCA' || i);
    END LOOP;
  END IF;
  COMMIT;
  
END INITIALIZE_QUEUES;

-- PURPOSE
-- To get the name of a queue to which the notification will be assigned
--
-- NOTES
--   Queueing is done based on the user and object GUID (target, policy or job)
--   of the notification. Queues have a name of the form:
-- 
--     <device-type><number> 
--
--   where device-type is EMAIL, OSCMD, SNMP or PLSQL and number is the queue
--   number which is based on values defined in MGMT_PARAMETERS for queue
--   sizes
FUNCTION GET_QUEUE(device_type_in IN NUMBER, device_owner_in IN VARCHAR2, 
                   source_guid_in IN RAW)
RETURN VARCHAR2
IS
  qname VARCHAR2(30);
  qnumber NUMBER := 0;
  windows_os_qs SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
BEGIN
 
  -- Check the device type so the start of the qname can be set
  IF device_type_in IN (EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE, 
                        SHORT_EMAIL_DEVICE_TYPE)
  THEN
    -- Get the hash of the user and source_guid
    qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 
                                           1, NUM_EMAIL_QUEUES);
    RETURN 'EMAIL' || qnumber;

ELSIF device_type_in = PROGRAM_DEVICE_TYPE
  THEN
    -- Get the hash of the user and source_guid
    qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 
                                           1, NUM_OSCMD_QUEUES);
    RETURN 'OSCMD' || qnumber;
  ELSIF device_type_in = WINDOWS_PROGRAM_DEVICE_TYPE
  THEN
    SELECT qname BULK COLLECT INTO windows_os_qs 
    FROM MGMT_NOTIFY_QUEUES
    WHERE qname LIKE 'OSCMD%' AND windows = 1;

IF windows_os_qs IS NULL OR windows_os_qs.COUNT = 0
    THEN
      RETURN NULL;
    END IF;

-- Get the hash of the user and source_guid
    qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 
                                           1, windows_os_qs.COUNT);

RETURN windows_os_qs(qnumber);

ELSIF device_type_in = PLSQL_DEVICE_TYPE
  THEN
    -- Get the hash of the user and source_guid
    qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 
                                           1, NUM_PLSQL_QUEUES);
    RETURN 'PLSQL' || qnumber;
  ELSIF device_type_in = SNMP_DEVICE_TYPE
  THEN
    -- Get the hash of the user and source_guid
    qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 
                                           1, NUM_SNMP_QUEUES);
    RETURN 'SNMP' || qnumber;
  ELSIF device_type_in = RCA_DEVICE_TYPE
  THEN
    -- Get the hash of the user and source_guid
    qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 
                                           1, NUM_RCA_QUEUES);
    RETURN 'RCA' || qnumber;  
  ELSIF device_type_in = JAVA_DEVICE_TYPE
  THEN
    -- Get the hash of the user and source_guid
    qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 
                                           1, NUM_JAVA_QUEUES);
    RETURN 'JAVA' || qnumber;  
  
  END IF; 
  
  RETURN NULL;

END GET_QUEUE;

-- PURPOSE
--   Procedure to queue a notification. 
--
-- NOTES
--
PROCEDURE QUEUE(qname_in IN VARCHAR2,
                notification_in IN MGMT_NOTIFY_NOTIFICATION)
IS
  enq_options         dbms_aq.enqueue_options_t;
  qMsg_properties     dbms_aq.message_properties_t;
  qMsg_handle         RAW(16);
  recipient           sys.aq$_agent;
BEGIN

-- Set up the qname as the recipient name
  recipient := sys.aq$_agent(qname_in, NULL, 0);
  qMsg_properties.recipient_list(1) := recipient;

-- Add the message to the queue
  DBMS_AQ.ENQUEUE(queue_name => G_QUEUE_NAME,
                  enqueue_options => enq_options,       
                  message_properties => qMsg_properties,  
                  payload => notification_in,
                  msgid => qMsg_handle);

EXCEPTION
WHEN OTHERS
THEN
    mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, 
                       MGMT_GLOBAL.UNEXPECTED_ERR,
                       MGMT_GLOBAL.UNEXPECTED_ERR_M || 
                       'QUEUE(' 
                       || qname_in || '/' || notification_in.device_name ||
                       '/' || notification_in.device_owner || ') ' || 
                       SQLERRM, 'ORA', notification_in.source_guid);
END QUEUE;

PROCEDURE QUEUE_READY(qnames_in IN SMP_EMD_STRING_ARRAY,
                      qtimeout_in IN NUMBER,
                      qname_out OUT VARCHAR2)
IS
  agents          dbms_aq.aq$_agent_list_t;
  agent           sys.aq$_agent;
BEGIN
  -- in standalone mode where is no dbms job to process the data 
  -- of MGMT_NOTIFY_INPUT_Q. calling CHECK_FOR_SEVERITIES from 
  -- listen call to process repeat notifications.   
  IF (EMD_MAINTENANCE.IS_CENTRAL_MODE = 0) 
  THEN  
    CHECK_FOR_SEVERITIES();
  END IF;

FOR i IN qnames_in.FIRST..qnames_in.LAST
  LOOP
    agents(i) := sys.aq$_agent(qnames_in(i), G_QUEUE_NAME, 0);
  END LOOP;

-- This raises an ORA-25254 exception if there is nothing in the queue
  DBMS_AQ.LISTEN (agents, qtimeout_in, agent);
  qname_out := agent.name;

END QUEUE_READY;

PROCEDURE DEQUEUE(qname_in IN VARCHAR2, 
                  qtimeout_in IN NUMBER,
                  qmsg_id_out OUT RAW,
                  notification_out OUT MGMT_NOTIFY_NOTIFICATION)
IS
  dq_options      dbms_aq.dequeue_options_t;
  qMsg_properties dbms_aq.message_properties_t;
BEGIN

dq_options.consumer_name := qname_in;
  dq_options.wait := qtimeout_in;
  dq_options.navigation := DBMS_AQ.FIRST_MESSAGE;

-- Dequeue a notification
  DBMS_AQ.DEQUEUE(queue_name => G_QUEUE_NAME,
                  dequeue_options    => dq_options,
                  message_properties => qMsg_properties,
                  payload            => notification_out,
                  msgid              => qmsg_id_out);

END DEQUEUE;

-- PURPOSE
--   Procedure to queue an RCA update task
--   NOTE: this is used during regression runs only!!
--
PROCEDURE QUEUE_RCA_TASK(target_guid_in IN RAW,                          
                         severity_guid_in IN RAW,
                         rca_type_in IN NUMBER,
                         rca_mode_in IN VARCHAR2)
IS
  qMsg_properties dbms_aq.message_properties_t;
  qMsg        MGMT_NOTIFY_NOTIFICATION;
  qname       VARCHAR2(30);      
BEGIN
    qname := GET_QUEUE(RCA_DEVICE_TYPE, 
                     'RCA', 
                     target_guid_in);

qMsg := MGMT_NOTIFY_NOTIFICATION(NORMAL_NOTIFICATION, 
                             IS_NOT_PING,
                             rca_type_in, 
                             severity_guid_in,
                             'RCA', 
                             'RCA',
                             RCA_DEVICE_TYPE,
                             rca_mode_in,
                             RAWTOHEX(target_guid_in), 
                             qname,
                             0);
    
    QUEUE(qname, qMsg);    
END QUEUE_RCA_TASK;

-- PURPOSE
--   Procedure to queue an RCA update task
--
PROCEDURE QUEUE_RCA_UPDATE(target_guid_in IN RAW,                          
                           severity_guid_in IN RAW, 
                           event_guid_in IN RAW,
                           delay_in IN NUMBER)
IS
  qMsg_properties dbms_aq.message_properties_t;
  qMsg        MGMT_NOTIFY_NOTIFICATION;
  qname       VARCHAR2(30);  
  rca_type    VARCHAR2(30);
BEGIN
    qname := GET_QUEUE(RCA_DEVICE_TYPE, 
                     'RCA', 
                     target_guid_in);
      
    IF delay_in = 0 THEN
      rca_type := 'RCA_IMMEDIATE';
    ELSE
      rca_type := 'RCA_NORMAL';
    END IF;

IF severity_guid_in IS NULL
    THEN
      qMsg := MGMT_NOTIFY_NOTIFICATION(NORMAL_NOTIFICATION, 
                               IS_NOT_PING,
                               RCA_RETRY_EVENT, 
                               event_guid_in,
                               'RCA', 
                               'RCA',
                               RCA_DEVICE_TYPE,
                               rca_type,
                               RAWTOHEX(target_guid_in), 
                               qname,
                               0);
    ELSE
      qMsg := MGMT_NOTIFY_NOTIFICATION(NORMAL_NOTIFICATION, 
                               IS_NOT_PING,
                               RCA_RETRY_SEVERITY, 
                               severity_guid_in,
                               'RCA', 
                               'RCA',
                               RCA_DEVICE_TYPE,
                               rca_type,
                               RAWTOHEX(target_guid_in), 
                               qname,
                               0);
    END IF;
    
    QUEUE_WITH_DELAY(qname, qMsg, delay_in);
    
END QUEUE_RCA_UPDATE;

-- PURPOSE
--   Procedure to queue a task with a delay
--
-- NOTES
--
PROCEDURE QUEUE_WITH_DELAY(qname_in IN VARCHAR2,
                notification_in IN MGMT_NOTIFY_NOTIFICATION,
                delay_in IN NUMBER)
IS
  enq_options         dbms_aq.enqueue_options_t;
  qMsg_properties     dbms_aq.message_properties_t;
  qMsg_handle         RAW(16);
  recipient           sys.aq$_agent;
BEGIN

-- Set up the qname as the recipient name
  recipient := sys.aq$_agent(qname_in, NULL, 0);
  qMsg_properties.recipient_list(1) := recipient;
  qMsg_properties.delay := delay_in;

EXCEPTION
WHEN OTHERS
THEN
    mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, 
                       MGMT_GLOBAL.UNEXPECTED_ERR,
                       MGMT_GLOBAL.UNEXPECTED_ERR_M || 
                       'QUEUE_WITH_DELAY(' 
                       || qname_in || '/' || notification_in.device_name ||
                       '/' || notification_in.device_owner || ') ' || 
                       SQLERRM, 'ORA', notification_in.source_guid);
END QUEUE_WITH_DELAY;

PROCEDURE CHK_AND_QUEUE_REP_NOTIF(p_sev_guid_in IN RAW,
                                  p_rule_guid IN RAW,
                                  p_curr_repeat_count NUMBER)
IS
  l_repeat_enabled  NUMBER(1);
  l_rep_frequency   NUMBER;
  l_max_rep_count   NUMBER;
  l_ack             NUMBER(1);
  l_violation_type  NUMBER(1);
  l_cnt             NUMBER(1);
  l_source_type     NUMBER(1);
  l_qname           VARCHAR2(30);
  l_target_guid     RAW(16);
  l_notif_status    NUMBER;
  l_violation_level NUMBER;
  tgt_is_service    NUMBER := 0;
  l_num_emails      NUMBER := 0;
  l_tz_region       VARCHAR2(64); 
  qMsg              MGMT_NOTIFY_NOTIFICATION;
  l_target_type     MGMT_TARGETS.TARGET_TYPE%TYPE;

CURSOR devices
  IS
    SELECT
    DISTINCT d.profile_name, d.device_name, d.type, d.status,
             d.program, r.owner, r.rule_name
      FROM mgmt_notify_rules r, mgmt_notify_devices d, mgmt_notify_notifyees n
     WHERE r.rule_guid = p_rule_guid
       AND r.rule_name = n.rule_name
       AND r.owner = n.owner
       AND n.device_name = d.device_name
       AND n.profile_name = d.profile_name
       AND d.type IN (LONG_EMAIL_DEVICE_TYPE, SHORT_EMAIL_DEVICE_TYPE);
BEGIN
  BEGIN
    GET_GLOBAL_REPEAT_SETTINGS(l_repeat_enabled, l_rep_frequency, l_max_rep_count);
    IF(l_repeat_enabled = 0)
    THEN
      IF EMDW_LOG.P_IS_INFO_SET
      THEN
        EMDW_LOG.INFO('Global repeat setting is turned off. ' || 
                      ' No more repeat notificaion will be send'
                      , 'CHK_AND_QUEUE_REP_NOTIF');
      END IF;

RETURN;
    END IF;

IF(p_curr_repeat_count > l_max_rep_count)
    THEN
      IF EMDW_LOG.P_IS_INFO_SET
      THEN
        EMDW_LOG.INFO('Global repeat setting count is less the current repeat count. ' || 
                      ' No more repeat notificaion will be send'
                      , 'CHK_AND_QUEUE_REP_NOTIF');
      END IF;

RETURN;
    END IF;

SELECT acknowledged, violation_type, target_guid, notification_status
      INTO l_ack, l_violation_type, l_target_guid, l_notif_status
      FROM mgmt_violations
     WHERE violation_guid = p_sev_guid_in;

IF(l_ack = 1)
    THEN
      IF EMDW_LOG.P_IS_DEBUG_SET
      THEN
        EMDW_LOG.DEBUG('Severity is already acknowledge. ' ||
                      ' No more repeat notificaion will be send'
                      , 'CHK_AND_QUEUE_REP_NOTIF');
      END IF;

RETURN;
    END IF;

IF(l_violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_AVAILABILITY)
    THEN
      SELECT count(*)
        INTO l_cnt
        FROM mgmt_current_availability
       WHERE severity_guid = p_sev_guid_in;
    ELSE
      SELECT count(*)
        INTO l_cnt
        FROM mgmt_current_violation
       WHERE violation_guid = p_sev_guid_in;
    END IF;

IF(l_cnt = 0)
    THEN
      IF EMDW_LOG.P_IS_DEBUG_SET
      THEN
        EMDW_LOG.DEBUG('Severity state has changed. ' ||
                      ' No more repeat notificaion will be send'
                      , 'CHK_AND_QUEUE_REP_NOTIF');
      END IF;

RETURN;
    END IF;

IF l_violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY
    THEN
      l_source_type := POLICY_VIOLATION;
    ELSE
      l_source_type := METRIC_SEVERITY;
    END IF;

-- Check if this is an RCA notification
    SELECT target_type 
      INTO l_target_type 
      FROM mgmt_targets
     WHERE target_guid = l_target_guid;

tgt_is_service := mgmt_service.implements_service_interface(l_target_type);

IF tgt_is_service = 1 AND
       l_violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_AVAILABILITY AND
       l_notif_status IN 
                      (MGMT_GLOBAL.G_NOTIF_STATUS_RCA_CAUSE,
                       MGMT_GLOBAL.G_NOTIF_STATUS_RCA_NO_CAUSE) AND
       l_violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL
    THEN
      l_source_type := RCA_COMPLETED_SEVERITY;
    END IF;

FOR device IN devices
    LOOP
      IF DEVICE_AVAILABLE(device.device_name, device.profile_name)
      THEN
        -- Get the queue name for this user/object combination
        l_qname := GET_QUEUE(device.type, device.profile_name,
                             l_target_guid);
        IF l_qname IS NOT NULL
        THEN
          qMsg := MGMT_NOTIFY_NOTIFICATION(REPEAT_NOTIFICATION, IS_NOT_PING,
                                           l_source_type,
                                           p_sev_guid_in,
                                           device.device_name,
                                           device.profile_name,
                                           device.type,
                                           device.rule_name,
                                           device.owner, l_qname, p_curr_repeat_count);
          QUEUE(l_qname, qMsg);
          NUM_EMAILS_QUEUED := NUM_EMAILS_QUEUED + 1;
          l_num_emails := l_num_emails + 1; 
        END IF;
      ELSE
        IF EMDW_LOG.P_IS_INFO_SET
        THEN
          EMDW_LOG.INFO( device.device_name || ' Email device is not avaliable.' ||
                        ' Repeat notificaion will not be send'
                        , 'CHK_AND_QUEUE_REP_NOTIF');
        END IF;
      END IF;
     
    END LOOP;

IF(l_num_emails > 0)
    THEN
      SELECT TIMEZONE_REGION
        INTO l_tz_region
        FROM mgmt_targets
       WHERE target_guid = l_target_guid;

INSERT INTO MGMT_ANNOTATION (source_obj_type, source_obj_guid, timestamp,
                                   annotation_type, user_name, message)
      VALUES (MGMT_GLOBAL.G_ANNOTATION_SOURCE_SEVERITY, p_sev_guid_in, 
              mgmt_global.sysdate_tzrgn(l_tz_region), 'NOTIFICATION',
                '<SYSTEM>', l_num_emails || ' repeat email notifications will be sent ');
    END IF;

IF(p_curr_repeat_count < l_max_rep_count) 
    THEN
      queue_repeat_notif_input(p_sev_guid_in, p_rule_guid, p_curr_repeat_count + 1, l_rep_frequency); 
    END IF;

EXCEPTION
  WHEN OTHERS THEN
    mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME,
                       MGMT_GLOBAL.UNEXPECTED_ERR,
                       MGMT_GLOBAL.UNEXPECTED_ERR_M ||
                       ' CHK_AND_QUEUE_REP_NOTIF(' 
                       || p_sev_guid_in || '/' || p_rule_guid || '/' || p_curr_repeat_count || ') ' ||
                       SQLERRM, 'ORA', p_sev_guid_in);
  END;
END CHK_AND_QUEUE_REP_NOTIF;

-- PURPOSE
--   Procedure to find the devices that need to be notified for a severity
--   occurrence and queue the notifications 
-- 
PROCEDURE QUEUE_METRIC_NOTIFICATIONS
(
 v_violation IN MGMT_VIOLATIONS%ROWTYPE,
 v_ca_state_change IN MGMT_JOB_STATE_CHANGES%ROWTYPE DEFAULT NULL
)
IS
  qMsg        MGMT_NOTIFY_NOTIFICATION;
  tguid       RAW(16);
  tgt_name    MGMT_TARGETS.target_name%TYPE;
  tgt_type    MGMT_TARGETS.target_type%TYPE;
  tz_region   VARCHAR2(64);
  met_name    VARCHAR2(64);
  met_col     VARCHAR2(64);
  pname       VARCHAR2(64);
  dname       VARCHAR2(132);      
  qname       VARCHAR2(30);      
  origUser    VARCHAR2(256);
  checkUser   VARCHAR2(256);
  lastUser    VARCHAR2(256);
  has_priv    NUMBER := 0;
  is_response NUMBER := 0;
  qstart      DATE;
  num_emails  NUMBER := 0;
  num_oscmds  NUMBER := 0;
  num_plsql   NUMBER := 0;
  num_snmp    NUMBER := 0;
  num_java NUMBER := 0;
  tgt_is_service   NUMBER := 0;
  t           DATE;
  l_device_type NUMBER := 0;
  l_source_type NUMBER := 0;
  l_source_id RAW(16) := v_violation.violation_guid;
  l_annotation_type NUMBER :=  MGMT_GLOBAL.G_ANNOTATION_SOURCE_SEVERITY;
  l_annotation_msg VARCHAR2(256) :=  'The following notifications will be sent: ';
  l_ca_state NUMBER := -1;
  l_notification_type NUMBER := NORMAL_NOTIFICATION;
  l_error_message VARCHAR2(256);
  l_rule_guid RAW(16);
  l_repeat    NUMBER(1);
  l_repeat_enabled NUMBER(1);
  l_rep_frequency NUMBER;
  l_max_rep_count NUMBER; 
  l_ack           NUMBER(1);  
  l_tz_region     VARCHAR2(64); 
  l_rule_guids    MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY();
  l_distinct_rules  MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY();
  CURSOR devices(tguid RAW, tname VARCHAR2, ttype VARCHAR2, mname VARCHAR2, 
                 mcol VARCHAR2, kval VARCHAR2, violation_level NUMBER,
                 is_response_metric NUMBER, ca_state NUMBER,
                 is_service NUMBER) 
  IS
    SELECT /*+ ORDERED PUSH_SUBQ */
    DISTINCT d.profile_name, d.device_name, d.type, d.status, d.program,
             cfg.owner, cfg.rule_name, DECODE(d.type, 
                                SHORT_EMAIL_DEVICE_TYPE,d.profile_name, 
                                LONG_EMAIL_DEVICE_TYPE ,d.profile_name, 
                                cfg.owner) checkUser
    FROM 
    (SELECT rule_name, owner
     FROM   MGMT_NOTIFY_RULE_CONFIGS c
     WHERE ((ttype LIKE c.target_type ESCAPE G_ESC AND 
             tname LIKE c.target_name ESCAPE G_ESC) 
             OR (c.target_guid IS NOT NULL AND 
                 EXISTS (SELECT 1
                         FROM MGMT_FLAT_TARGET_ASSOC a,
                              MGMT_TARGETS t
                         WHERE a.source_target_guid = c.target_guid
                         AND a.is_membership = 1
                         AND a.assoc_target_guid = t.target_guid
                         AND t.target_type = c.target_type
                         AND t.target_name = tname)))
     AND mname LIKE c.metric_name ESCAPE G_ESC
     AND mcol LIKE c.metric_column ESCAPE G_ESC
     AND ((kval LIKE c.key_value ESCAPE G_ESC) 
           OR (c.key_value <> ' ' AND EXISTS 
           (SELECT 1 FROM mgmt_metrics_composite_keys k
            WHERE k.composite_key = kval
              AND NVL(k.key_part1_value, ' ') LIKE c.key_part_1 ESCAPE G_ESC
              AND NVL(k.key_part2_value, ' ') LIKE c.key_part_2 ESCAPE G_ESC
              AND NVL(k.key_part3_value, ' ') LIKE c.key_part_3 ESCAPE G_ESC
              AND NVL(k.key_part4_value, ' ') LIKE c.key_part_4 ESCAPE G_ESC
              AND NVL(k.key_part5_value, ' ') LIKE c.key_part_5 ESCAPE G_ESC
              AND k.target_guid = tguid)))
    AND ((ca_state = -1 AND 
          (
           -- The following conditions should only match violation related to
           -- metric severities and policies
           (violation_level = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_START AND
            want_target_unreachable_start = 1) OR
           (violation_level = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_CLEAR AND
            want_target_unreachable_end = 1) OR
           (violation_level = MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START AND
            want_target_blackout_start = 1) OR
           (violation_level = MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END AND
            want_target_blackout_end = 1) OR
           (violation_level = MGMT_GLOBAL.G_SEVERITY_ERROR_START AND
            ((want_target_metric_err_start = 1 AND is_response_metric = 1) OR
             (want_target_metric_err_start = 2 AND is_response_metric = 0) OR
             (want_target_metric_err_start = 3))) OR
           (violation_level = MGMT_GLOBAL.G_SEVERITY_ERROR_END AND
            ((want_target_metric_err_end = 1 AND is_response_metric = 1) OR
             (want_target_metric_err_end = 2 AND is_response_metric = 0) OR
             (want_target_metric_err_end = 3))) OR
           (violation_level = MGMT_GLOBAL.G_SEVERITY_CLEAR AND
            want_clears = 1 AND v_violation.violation_type <> 1) OR
           (violation_level = MGMT_GLOBAL.G_SEVERITY_CLEAR AND
            want_target_up = 1 AND v_violation.violation_type = 1) OR
           (violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING AND
            want_warnings = 1) OR
           (violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND
            want_critical_alerts = 1 AND v_violation.violation_type <> 1) OR
           (violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND
            want_target_down = 1 AND v_violation.violation_type = 1 AND
             is_service = 0) OR
           (violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND
            want_target_down = 1 AND v_violation.violation_type = 1 AND
            is_service = 1 AND is_response_metric = 1 and ignore_rca = 1 AND
            v_violation.notification_status NOT IN
              (MGMT_GLOBAL.G_NOTIF_STATUS_RCA_CAUSE,  
               MGMT_GLOBAL.G_NOTIF_STATUS_RCA_NO_CAUSE)) OR
           (violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND 
            want_target_down = 1 AND v_violation.violation_type = 1 AND
            is_service = 1 AND is_response_metric = 1 and ignore_rca = 0 AND
            v_violation.notification_status IN
              (MGMT_GLOBAL.G_NOTIF_STATUS_RCA_CAUSE,  
               MGMT_GLOBAL.G_NOTIF_STATUS_RCA_NO_CAUSE)) OR
           ((violation_level = MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL OR violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING OR violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL) AND
            want_policy_violations = 1 ) OR
           (violation_level = MGMT_GLOBAL.G_SEVERITY_CLEAR AND
            want_policy_clears = 1 ) 
          )
         )
         OR
         (ca_state <> -1 AND
          (
            -- The following conditions should only match violations related to
            -- corrective action executions
           (ca_state = MGMT_JOBS.STATUS_BUCKET_OK AND
            violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING AND
            want_warning_job_succeeded = 1) OR
           (ca_state = MGMT_JOBS.STATUS_BUCKET_OK AND
            violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND
            want_critical_job_succeeded = 1) OR
           (ca_state = MGMT_JOBS.STATUS_BUCKET_OK AND
            violation_level <> MGMT_GLOBAL.G_SEVERITY_CLEAR AND
            want_policy_job_succeeded = 1) OR
           (ca_state = MGMT_JOBS.STATUS_BUCKET_PROBLEM AND
            violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING AND
            want_warning_job_problems = 1) OR
           (ca_state = MGMT_JOBS.STATUS_BUCKET_PROBLEM AND
            violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND
            want_critical_job_problems = 1) OR
           (ca_state = MGMT_JOBS.STATUS_BUCKET_PROBLEM AND
            violation_level <> MGMT_GLOBAL.G_SEVERITY_CLEAR AND
            want_policy_job_problems = 1)
          )
         )
        )) cfg, mgmt_notify_notifyees n, mgmt_notify_devices d 
    WHERE n.rule_name = cfg.rule_name 
      AND n.owner = cfg.owner 
      AND ((d.device_name = n.device_name AND d.profile_name = n.profile_name) 
           OR
           (n.device_name = ' ' AND d.profile_name = n.profile_name AND
            d.type IN (SHORT_EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE)
           )
          )
    ORDER BY checkUser;
BEGIN

BEGIN
    GET_GLOBAL_REPEAT_SETTINGS(l_repeat_enabled, l_rep_frequency, l_max_rep_count);
    -- Get the target name and 
    SELECT target_name, target_type, timezone_region
    INTO tgt_name, tgt_type, tz_region
     FROM MGMT_TARGETS
    WHERE target_guid = v_violation.target_guid;
    
    tgt_is_service := mgmt_service.implements_service_interface(tgt_type);
  EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    -- Severity contains an invalid target guid
    IF emdw_log.p_is_debug_set
    THEN
      emdw_log.debug('QMN: invalid target: violation_guid = ' 
                     || v_violation.violation_guid
                     || ' target_guid=' || v_violation.target_guid,
                     CHECK_MODULE_NAME) ;
    END IF;

RETURN;
  END;

-- If this is a policy violation then get the policy name
  IF v_violation.violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY
  THEN
    l_source_type := POLICY_VIOLATION;
    BEGIN 
      -- Get the policy_name
      SELECT policy_name INTO met_name FROM MGMT_POLICIES p
      WHERE policy_guid = v_violation.policy_guid;
      met_col := '%';
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      -- Severity contains an invalid policy guid
      IF emdw_log.p_is_debug_set
      THEN
        emdw_log.debug('QMN: invalid policy: violation_guid = ' 
                       || v_violation.violation_guid
                       || ' policy_guid=' || v_violation.policy_guid,
                       CHECK_MODULE_NAME) ;
      END IF;
      RETURN;
    END;
  ELSE
    l_source_type := METRIC_SEVERITY;
    BEGIN
      -- Get the metric name and column
      SELECT m.metric_name, m.metric_column INTO met_name, met_col
      FROM MGMT_TARGETS t, MGMT_METRICS m
      WHERE t.target_guid = v_violation.target_guid
        AND m.metric_guid = v_violation.policy_guid
        AND t.target_type = m.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 = ' ');
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      -- Severity contains an invalid metric guid
      IF emdw_log.p_is_debug_set
      THEN
        emdw_log.debug('QMN: invalid metric: violation_guid = ' 
                       || v_violation.violation_guid
                       || ' metric_guid' || v_violation.policy_guid,
                       CHECK_MODULE_NAME) ;
      END IF;
      RETURN;
    WHEN TOO_MANY_ROWS
    THEN
      -- Get the metric name and column
      SELECT m.metric_name, m.metric_column INTO met_name, met_col
      FROM MGMT_TARGETS t, MGMT_METRICS m
      WHERE t.target_guid = v_violation.target_guid
        AND m.metric_guid = v_violation.policy_guid
        AND t.target_type = m.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 rownum < 2;
  
      IF emdw_log.p_is_debug_set
      THEN
        emdw_log.debug('QMN: duplicate metric: violation_guid = ' 
                       || v_violation.violation_guid
                       || ' metric_guid=' || v_violation.policy_guid,
                       CHECK_MODULE_NAME) ;
      END IF;

-- Log an error
      mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, 
                         MGMT_GLOBAL.UNEXPECTED_ERR,
            'QUEUE_METRIC_NOTIFICATIONS found duplicate metric definition for ' 
                         || met_name || '/' || met_col || '/' || 
                         tgt_type, 'ORA', v_violation.violation_guid);
      RETURN;
    END;
  END IF;
 
  -- Check if this is the Response Metric
  IF met_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND 
     met_col = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
  THEN
    is_response := 1;
  END IF;

-- First get the currently logged in user. this user name will be used
  -- later for resetting the context back to this user
  origUser := mgmt_user.get_current_em_user();

-- Check if this is for a corrective action
  IF v_ca_state_change.state_change_guid IS NOT NULL
  THEN
    l_ca_state := v_ca_state_change.status_bucket;
    l_source_id := v_ca_state_change.state_change_guid;
    l_annotation_msg := 'The following notifications will be sent for the Corrective Action: ';

-- Update the source type
    IF l_source_type = METRIC_SEVERITY
    THEN
      l_source_type := METRIC_CA_STATE_CHANGE;
    ELSIF l_source_type = POLICY_VIOLATION
    THEN
      l_source_type := POLICY_CA_STATE_CHANGE;
    END IF;
  END IF;

-- Check if this is an RCA notification
  IF tgt_is_service = 1 AND 
     is_response = 1 AND 
     v_violation.notification_status IN 
                    (MGMT_GLOBAL.G_NOTIF_STATUS_RCA_CAUSE, 
                     MGMT_GLOBAL.G_NOTIF_STATUS_RCA_NO_CAUSE) AND 
     v_violation.violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL
  THEN
    l_source_type := RCA_COMPLETED_SEVERITY;
  END IF;

IF emdw_log.p_is_debug_set
  THEN
    emdw_log.debug('QMN: violation_guid = ' || v_violation.violation_guid 
                   || ' target=' || tgt_name
                   || ' type=' || tgt_type
                   || ' metric/policy=' || met_name
                   || ' column=' || met_col
                   || ' level=' || v_violation.violation_level
                   || ' source_type=' || l_source_type
                   || ' is_response=' || is_response
                   || ' message=' || v_violation.message,
                   CHECK_MODULE_NAME) ;
  END IF ;

-- Get the devices that need to be notified
  FOR device IN devices(v_violation.target_guid, tgt_name, tgt_type, met_name, met_col, 
                        v_violation.key_value, v_violation.violation_level,
                        is_response, l_ca_state, tgt_is_service)
  LOOP
    -- For use in error message
    pname := device.profile_name;
    dname := device.device_name;

IF emdw_log.p_is_debug_set
    THEN
      emdw_log.debug('QMN: Match found. Rule=' || device.rule_name 
                     || ' Owner=' || device.owner || ' Method=' || dname ||
                     ' Owner=' || pname,
                      CHECK_MODULE_NAME) ;
    END IF ;

-- Only check if this user has not been checked before
      IF lastUser IS NULL OR lastUser <> checkUser
      THEN
        lastUser := checkUser;
        has_priv := 0;
  
        -- Check if the device owner has access to the target
        SETEMUSERCONTEXT(checkUser, MGMT_USER.OP_SET_IDENTIFIER);
  
        BEGIN
        -- the following line has been changed by dcawley to fix a problem
        -- with VPD policies not being applied correctly by the RDBMS.
        -- See bug 4143213
          SELECT count(target_guid) INTO has_priv FROM MGMT_TARGETS
          WHERE target_guid = v_violation.target_guid;
        EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
          -- Continue onto the next device  since this device has no access to
          -- the target
          has_priv := 0;
        END;  
      END IF;
  
      IF has_priv = 0
      THEN
        IF emdw_log.p_is_debug_set
        THEN
           emdw_log.debug('QMN: ' || checkUser || ' has no access to ' ||
                          tgt_name || '/' || tgt_type,
                          CHECK_MODULE_NAME) ;
        END IF ;

GOTO end_device_loop;
      END IF;

END IF;
  
    -- Check the notification schedule to see if the device is available
    IF device.type NOT IN (SHORT_EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE) OR
       DEVICE_AVAILABLE(device.device_name, device.profile_name) 
    THEN
      -- Check the status to make sure it is contactable
      IF device.status = DEVICE_UP
      THEN
        qstart := SYSDATE;
 
        IF device.type = PROGRAM_DEVICE_TYPE AND
           IS_WINDOWS_PROGRAM(device.program)
        THEN
          l_device_type := WINDOWS_PROGRAM_DEVICE_TYPE;
        ELSE
          l_device_type := device.type;
        END IF;

-- Get the queue name for this user/object combination
        qname := GET_QUEUE(l_device_type, device.profile_name, 
                           v_violation.target_guid);
        IF qname IS NOT NULL
        THEN
          -- Set up the message for the queue
          qMsg := MGMT_NOTIFY_NOTIFICATION(l_notification_type, IS_NOT_PING,
                                           l_source_type, 
                                           l_source_id,
                                           device.device_name, 
                                           device.profile_name,
                                           device.type,
                                           device.rule_name, 
                                           device.owner, qname, 0);
          IF emdw_log.p_is_debug_set
          THEN
             emdw_log.debug('QMN: Queueing notification for' ||
                            ' device_name=' || dname ||
                            ' owner=' || pname,
                            CHECK_MODULE_NAME) ;
          END IF ;

QUEUE(qname, qMsg);
          
          TOTAL_ENQUEUE_TIME := TOTAL_ENQUEUE_TIME + (SYSDATE - qstart);

-- Update the local and overall stats
          IF device.type IN (LONG_EMAIL_DEVICE_TYPE, 
                             SHORT_EMAIL_DEVICE_TYPE)
          THEN
            NUM_EMAILS_QUEUED := NUM_EMAILS_QUEUED + 1;
            num_emails := num_emails + 1;
          ELSIF device.type = PROGRAM_DEVICE_TYPE
          THEN
            NUM_OSCMDS_QUEUED := NUM_OSCMDS_QUEUED + 1;
            num_oscmds := num_oscmds + 1;
          ELSIF device.type = SNMP_DEVICE_TYPE
          THEN
            NUM_SNMPS_QUEUED := NUM_SNMPS_QUEUED + 1;
            num_snmp := num_snmp + 1;
          ELSIF device.type = PLSQL_DEVICE_TYPE
          THEN
            NUM_PLSQLS_QUEUED := NUM_PLSQLS_QUEUED + 1;
            num_plsql := num_plsql + 1;
          ELSIF device.type = JAVA_DEVICE_TYPE
          THEN
            NUM_JAVA_QUEUED := NUM_JAVA_QUEUED + 1;
            num_java := num_java + 1;
          END IF;
        ELSE
          -- Could not find a queue to service notification. Must be an
          -- OS Command where no Windows OMSs are registered
          IF emdw_log.p_is_debug_set
          THEN
             emdw_log.debug('QMN: No Windows OMS to handle OS Command',
                            CHECK_MODULE_NAME) ;
          END IF ;

LOG_HISTORY(l_annotation_type, v_violation.violation_guid, 'N', 
                      'A Windows based Management Server is not registered ' ||
                      '(Rule Owner=' || device.owner || ', Rule Name=' || 
                      device.rule_name || ')', tz_region);
        END IF;
      ELSE
        -- The device is currently down so set this up for retry
        IF emdw_log.p_is_debug_set
        THEN
           emdw_log.debug('QMN: Device down so setup for retry',
                          CHECK_MODULE_NAME) ;
        END IF ;
        INSERT INTO mgmt_notify_requeue 
          (source_guid, source_type, notification_type,
           device_name, device_owner, device_type, rule_name, rule_owner)
        VALUES (v_violation.violation_guid, l_source_type, 
                RETRY_NOTIFICATION,
                device.device_name, device.profile_name, device.type,
                device.rule_name, device.owner);
      END IF;
    ELSE
      IF emdw_log.p_is_debug_set
      THEN
        emdw_log.debug('QMN: ' || dname || ' is not in notification schedule',
                       CHECK_MODULE_NAME) ;
      END IF ;
    END IF;

IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN
      -- Commit/Rollback only in central repository mode.
      -- In Standalone mode, this procedure is called in the severity insert 
      -- trigger. (Commits/Rollbacks are not allowed in triggers)
      COMMIT;
    END IF;

-- if the source is not CA then check for repeat and severity is not for clear
    IF l_source_type NOT IN (METRIC_CA_STATE_CHANGE, POLICY_CA_STATE_CHANGE) AND
       v_violation.violation_level NOT IN (mgmt_global.g_severity_clear, mgmt_global.g_severity_unreachable_clear,
                                           mgmt_global.g_severity_blackout_start, mgmt_global.g_severity_blackout_end, 
                                           mgmt_global.g_severity_error_end, mgmt_global.g_severity_unknown, 
                                           mgmt_global.g_severity_informational)
    THEN
      SELECT rule_guid, repeat
        INTO l_rule_guid, l_repeat
        FROM mgmt_notify_rules
       WHERE rule_name = device.rule_name
         AND owner = device.owner;

IF(l_repeat = 1 AND l_repeat_enabled = 1 AND l_max_rep_count > 0)
      THEN
        l_rule_guids.extend(1);
        l_rule_guids(l_rule_guids.COUNT) := l_rule_guid;
      END IF;
    END IF;

<< end_device_loop >>
    NULL;
  END LOOP;

-- check if the severity should produce an RCA task; this requires that the
-- target is a service and that the severity is for the Response-Status metric,
-- in other words, that its a Service Down alert.
  IF tgt_is_service = 1 AND 
     is_response = 1 AND 
     v_violation.notification_status <> MGMT_GLOBAL.G_NOTIF_STATUS_RCA_CAUSE AND
     v_violation.notification_status <> MGMT_GLOBAL.G_NOTIF_STATUS_RCA_NO_CAUSE AND
     v_violation.violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL
  THEN
    -- queue the RCA task
    qstart := SYSDATE;

-- Get the queue name for this user/object combination
    qname := GET_QUEUE(RCA_DEVICE_TYPE, 
                       'RCA', 
                       v_violation.target_guid);

qMsg := MGMT_NOTIFY_NOTIFICATION(NORMAL_NOTIFICATION, 
                               IS_NOT_PING,
                               RCA_SEVERITY, 
                               v_violation.violation_guid,
                               'RCA', 
                               'RCA',
                               RCA_DEVICE_TYPE,
                               'RCA_NORMAL', 
                               RAWTOHEX(v_violation.target_guid), 
                               qname,
                               0);
    QUEUE(qname, qMsg);

IF emdw_log.p_is_debug_set
    THEN
      emdw_log.debug('QMN: Queued RCA task',
                     CHECK_MODULE_NAME) ;
    END IF ;

TOTAL_ENQUEUE_TIME := TOTAL_ENQUEUE_TIME + (SYSDATE - qstart);
  END IF;
  
  IF num_emails > 0 OR num_oscmds > 0 OR num_plsql > 0 OR num_snmp > 0 OR num_java > 0
  THEN
    t := mgmt_global.sysdate_tzrgn(tz_region);

-- The message needs to be NLS'ed
    INSERT INTO MGMT_ANNOTATION (source_obj_type, source_obj_guid, timestamp,
                                 annotation_type, user_name, message)
    VALUES (l_annotation_type, v_violation.violation_guid, t, 'NOTIFICATION',
            '<SYSTEM>', l_annotation_msg ||
            decode(num_emails, 0, '', num_emails || ' e-mail messages ') ||
            decode(num_snmp, 0, '', num_snmp || ' SNMP traps ') ||
            decode(num_plsql, 0, '', num_plsql || '  PL/SQL procedures ') ||
            decode(num_oscmds, 0, '', num_oscmds || ' OS Commands ') || 
            decode(num_java, 0, '', num_java || ' Java callbacks')); 
  END IF;

-- if  l_rule_guids.COUNT > 0 then enqueue repeat signal for each rule
    IF l_rule_guids.COUNT > 0
    THEN
      BEGIN
        SELECT distinct(l.COLUMN_VALUE)
        BULK COLLECT INTO l_distinct_rules
        FROM TABLE (CAST(l_rule_guids as MGMT_USER_GUID_ARRAY)) l;

FOR i in 1 ..  l_distinct_rules.COUNT
        LOOP
          l_rule_guid := l_distinct_rules(i);
          queue_repeat_notif_input(l_source_id, l_rule_guid, 1, l_rep_frequency);
        END LOOP;
      EXCEPTION
      WHEN OTHERS THEN
         mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME,
                            MGMT_GLOBAL.UNEXPECTED_ERR,
                            MGMT_GLOBAL.UNEXPECTED_ERR_M ||
                            'QUEUE_METRIC_NOTIFICATIONS : '
                            || ' Error while enquing repeat signal for ( ' ||
                            l_source_id || ', ' || l_rule_guid || '  ' ||
                            SQLERRM, 'ORA', l_source_id);
      END;
    END IF;

-- Reset the context back to the original user
  SETEMUSERCONTEXT(origUser, MGMT_USER.OP_SET_IDENTIFIER);
  IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN
    -- Commit/Rollback only in central repository mode.
    -- In Standalone mode, this procedure is called in the 
    -- severity insert trigger. (Commits/Rollbacks are not allowed in triggers)
    COMMIT;
  END IF;

EXCEPTION
  WHEN OTHERS
  THEN
    IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN
      -- Commit/Rollback only in central repository mode.
      -- In Standalone mode, this procedure is called in the 
      -- severity insert trigger.(Commits/Rollbacks are not allowed in triggers)
      ROLLBACK;
    END IF;

-- Reset the context back to the original user
    IF origUser IS NOT NULL
    THEN
      SETEMUSERCONTEXT(origUser, MGMT_USER.OP_SET_IDENTIFIER);
    END IF;

IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN
      -- Commit/Rollback only in central repository mode.
      -- In Standalone mode, this procedure is called in the 
      -- severity insert trigger.(Commits/Rollbacks are not allowed in triggers)
      COMMIT;
    END IF;

IF emdw_log.p_is_debug_set
    THEN
      emdw_log.debug('QMN: Unexpected error ' || SQLERRM,
                     CHECK_MODULE_NAME) ;
    END IF ;

mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, 
                       MGMT_GLOBAL.UNEXPECTED_ERR,
                       MGMT_GLOBAL.UNEXPECTED_ERR_M || 
                       'QUEUE_METRIC_NOTIFICATIONS (' 
                       || pname || '/' || dname || ') ' || 
                       SQLERRM, 'ORA', v_violation.violation_guid);
END QUEUE_METRIC_NOTIFICATIONS;

-- PURPOSE
--   Procedure to find the devices that need to be notified for a job state 
--   change and queue the notifications
--
PROCEDURE QUEUE_JOB_NOTIFICATIONS(v_state_change IN MGMT_JOB_STATE_CHANGES%ROWTYPE)
IS
  qMsg        MGMT_NOTIFY_NOTIFICATION;
  tguid       RAW(16);
  l_job_id    RAW(16);
  tgt_guids   MGMT_USER_GUID_ARRAY;
  tgt_matches MGMT_LONG_STRING_ARRAY;
  tgt_types   MGMT_SHORT_STRING_ARRAY;
  tz_regions  MGMT_SHORT_STRING_ARRAY;
  job_name    VARCHAR2(64) := '%';
  job_owner   VARCHAR2(256) := '%';
  job_type    VARCHAR2(32) := '%';
  pname       VARCHAR2(64);
  dname       VARCHAR2(132);      
  qname       VARCHAR2(30);      
  origUser    VARCHAR2(256);
  checkUser   VARCHAR2(256);
  lastUser    VARCHAR2(256);
  has_priv    NUMBER := 0;
  qstart      DATE;
  num_emails  NUMBER := 0;
  num_oscmds  NUMBER := 0;
  num_plsql   NUMBER := 0;
  num_java    NUMBER := 0;
  num_snmp    NUMBER := 0;
  t           DATE;
  l_device_type NUMBER := 0;
  l_error_message VARCHAR2(256);
  l_tz_rgn MGMT_TARGETS.timezone_region%TYPE;
  
  CURSOR devices(ttypes MGMT_SHORT_STRING_ARRAY, 
                 tname_type MGMT_LONG_STRING_ARRAY,
                 jname VARCHAR2, jowner VARCHAR2, jtype VARCHAR2, 
                 job_state NUMBER)
  IS
    SELECT /*+ ORDERED PUSH_SUBQ */
    DISTINCT d.profile_name, d.device_name, d.type, d.status, d.program,
             cfg.owner, cfg.rule_name
    FROM
    (SELECT rule_name, owner
     FROM MGMT_NOTIFY_JOB_RULE_CONFIGS c
     WHERE (c.target_name||';'||c.target_type IN 
            (SELECT * FROM TABLE(CAST(tname_type AS MGMT_LONG_STRING_ARRAY))) 
           OR 
            (c.target_name = '%' AND c.target_type IN
             (SELECT * FROM TABLE(CAST(ttypes AS MGMT_SHORT_STRING_ARRAY)))) 
           OR (c.target_guid IS NOT NULL AND
               EXISTS (SELECT 1
                       FROM MGMT_FLAT_TARGET_ASSOC a, MGMT_TARGETS t
                       WHERE a.source_target_guid = c.target_guid
                       AND a.is_membership = 1
                       AND a.assoc_target_guid = t.target_guid
                       AND t.target_type = c.target_type
                       AND t.target_name||';'||c.target_type IN 
                           (SELECT * FROM TABLE(CAST(tname_type 
                            AS MGMT_LONG_STRING_ARRAY))) 
              )))
      AND jname LIKE c.job_name ESCAPE G_ESC
      AND jowner LIKE c.job_owner ESCAPE G_ESC
      AND jtype LIKE c.job_type ESCAPE G_ESC
      AND ((job_state = MGMT_JOBS.STATUS_BUCKET_SCHEDULED AND  
            want_job_scheduled = 1) OR
           (job_state = MGMT_JOBS.STATUS_BUCKET_RUNNING AND 
            want_job_running = 1) OR
           (job_state = MGMT_JOBS.STATUS_BUCKET_SUSPENDED AND 
            want_job_suspended = 1) OR
           (job_state = MGMT_JOBS.STATUS_BUCKET_OK AND 
            want_job_succeeded = 1) OR
           (job_state = MGMT_JOBS.STATUS_BUCKET_PROBLEM AND 
            want_job_problems = 1))) cfg,  mgmt_notify_notifyees n, 
                                     mgmt_notify_devices d
    WHERE n.rule_name = cfg.rule_name 
      AND n.owner = cfg.owner 
      AND ((d.device_name = n.device_name AND d.profile_name = n.profile_name) 
           OR
           (n.device_name = ' ' AND d.profile_name = n.profile_name AND
            d.type IN (SHORT_EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE)
           )
          );
BEGIN

BEGIN
    -- Get the target name and type
    SELECT DISTINCT job_tgt.target_guid, tgt.target_type, tgt.timezone_region,
           tgt.target_name||';'||tgt.target_type
    BULK COLLECT INTO tgt_guids, tgt_types, tz_regions, tgt_matches
    FROM MGMT_JOB_EXT_TARGETS job_tgt, MGMT_TARGETS tgt
    WHERE job_tgt.job_id = v_state_change.job_id
      AND tgt.target_guid = job_tgt.target_guid;
  EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    -- State change contains an invalid target guid
    IF emdw_log.p_is_debug_set
    THEN
      emdw_log.debug('QJN: invalid target: state_change_guid = '
                     || v_state_change.state_change_guid,
                     CHECK_MODULE_NAME) ;
    END IF;
    RETURN;
  END;

if tz_regions.count>0
  then 
      -- Use Target TimeZone when available 
      l_tz_rgn := tz_regions(1);
  else
      -- Use emdrep TimeZone for Targetless jobs
      BEGIN
        select TIMEZONE_REGION into l_tz_rgn from MGMT_TARGETS where target_type='oracle_emrep';
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
            l_tz_rgn := TO_CHAR(SYSTIMESTAMP,'TZR');
      END ;
  end if;
    
  -- Get the job details
  BEGIN
    SELECT job_name, job_owner, job_type INTO job_name, job_owner, job_type
    FROM MGMT_JOB WHERE job_id = v_state_change.job_id
    AND job_status != MGMT_JOBS.JOB_STATUS_DELETE_PENDING;
  EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    -- State change contains an invalid job id
    IF emdw_log.p_is_debug_set
    THEN
      emdw_log.debug('QJN: invalid job: state_change_guid = '
                     || v_state_change.state_change_guid 
                     || ' job_id=' || v_state_change.job_id,
                     CHECK_MODULE_NAME) ;
    END IF;

RETURN;
  END;

IF emdw_log.p_is_debug_set
  THEN
    emdw_log.debug('QJN: state_change_guid = '
                   || v_state_change.state_change_guid
                   || ' job_id=' || v_state_change.job_id
                   || ' execution_id=' || v_state_change.execution_id
                   || ' newstate=' || v_state_change.newstate
                   || ' status_bucket=' || v_state_change.status_bucket,
                   CHECK_MODULE_NAME) ;
  END IF;

-- First get the currently logged in user. this user name will be used
  -- later for resetting the context back to this user
  origUser := mgmt_user.get_current_em_user();

-- Check if the owner of the job wants to be notified
  BEGIN
    SELECT job_id INTO l_job_id FROM MGMT_JOB_NOTIFY_STATES
    WHERE job_id = v_state_change.job_id
      AND notify_state = v_state_change.status_bucket;
 
    IF emdw_log.p_is_debug_set
    THEN
      emdw_log.debug('QJN: the job owner ' || job_owner || 
                     ' has selected to be notified',
                     CHECK_MODULE_NAME) ;
    END IF;

-- Get the job owner's email addresses
    FOR oe IN (SELECT device_name, email_address, type, status
               FROM MGMT_NOTIFY_DEVICES
               WHERE profile_name = job_owner 
                 AND type IN (SHORT_EMAIL_DEVICE_TYPE,
                              LONG_EMAIL_DEVICE_TYPE))
    LOOP
      -- Do we need to check if the owner has access to the target?
      IF DEVICE_AVAILABLE(oe.device_name, job_owner)
      THEN
        -- Check the status to make sure it is contactable
        IF oe.status = DEVICE_UP
        THEN
          qstart := SYSDATE;
                                                                                
          -- Get the queue name for this user/object combination
         qname := GET_QUEUE(oe.type, job_owner,
                            v_state_change.job_id);
          IF qname IS NOT NULL
          THEN
            -- Set up the message for the queue
            qMsg := MGMT_NOTIFY_NOTIFICATION(NORMAL_NOTIFICATION, IS_NOT_PING,
                                             JOB_STATE_CHANGE,
                                             v_state_change.state_change_guid,
                                             oe.device_name, job_owner,
                                             oe.type, NULL, NULL, qname, 0);
            IF emdw_log.p_is_debug_set
            THEN
              emdw_log.debug('QJN: Queuing notification to ' ||
                             oe.device_name,
                             CHECK_MODULE_NAME) ;
            END IF;
            QUEUE(qname, qMsg);
            TOTAL_ENQUEUE_TIME := TOTAL_ENQUEUE_TIME + (SYSDATE - qstart);
                                                                                
            NUM_JOB_EMAILS_QUEUED := NUM_JOB_EMAILS_QUEUED + 1;
            num_emails := num_emails + 1;
          END IF;
        ELSE
          IF emdw_log.p_is_debug_set
          THEN
            emdw_log.debug('QJN: ' || oe.device_name || ' is down',
                           CHECK_MODULE_NAME) ;
          END IF;
        END IF;
      ELSE
        IF emdw_log.p_is_debug_set
        THEN
          emdw_log.debug('QJN: ' || oe.device_name || 
                         ' is not in the notification schedule',
                         CHECK_MODULE_NAME) ;
        END IF;
      END IF;
    END LOOP;
                
  EXCEPTION 
  WHEN NO_DATA_FOUND
  THEN
    NULL;
  END;
  
  FOR device IN devices(tgt_types, tgt_matches,
                        job_name, job_owner, job_type,
                        v_state_change.status_bucket)
  LOOP
    -- For use in error message
    pname := device.profile_name;
    dname := device.device_name;

IF emdw_log.p_is_debug_set
    THEN
      emdw_log.debug('QJN: Match found. Rule=' || device.rule_name
                     || ' Owner=' || device.owner || ' Method=' || dname ||
                     ' Owner=' || pname,
                      CHECK_MODULE_NAME) ;
    END IF ;

-- There is no need to check privileges for standalone mode
    -- since there is no VPD
    IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) 
    THEN
      -- For emails you need to make sure the user associated with the
      -- address has accees to the target. For all other notification methods
      -- you need to make sure the owner of the rule has access to the target
      -- since those methods are always owned by the Super User
      IF device.type IN (SHORT_EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE)
      THEN
        checkUser := device.profile_name;
      ELSE
        checkUser := device.owner;
      END IF;
                                                                                  
      -- Only check if this user has not been checked before
      IF lastUser IS NULL OR lastUser <> checkUser
      THEN
        has_priv := 0;
        lastUser := checkUser;
        -- Check if the device owner has access to the target
        SETEMUSERCONTEXT(checkUser, MGMT_USER.OP_SET_IDENTIFIER);
        -- Get a count of the targets the user can see
        SELECT count(target_guid) INTO has_priv FROM MGMT_TARGETS
        WHERE target_guid IN 
              (SELECT * FROM TABLE(CAST(tgt_guids AS MGMT_USER_GUID_ARRAY)));
  
        IF has_priv = 1
        THEN
          BEGIN
            SELECT job_id INTO l_job_id FROM MGMT_JOB
            WHERE job_id = v_state_change.job_id
            AND job_status != MGMT_JOBS.JOB_STATUS_DELETE_PENDING;
  
            has_priv := 1;
  
          EXCEPTION WHEN NO_DATA_FOUND
          THEN
            has_priv := 0;
          END;
        END IF;
      END IF;

IF has_priv = 0
      THEN
        -- Continue onto the next device  since this device has no access to
        -- the target
        IF emdw_log.p_is_debug_set
        THEN
           DECLARE
             l_tgts VARCHAR2(4000);
           BEGIN
             FOR i IN tgt_guids.FIRST..tgt_guids.LAST
             LOOP
               l_tgts := l_tgts || tgt_guids(i) || ', ';
             END LOOP; 
             emdw_log.debug('QJN: ' || checkUser || 
                            ' has no access to any of the job targets ' ||
                            l_tgts, 
                            CHECK_MODULE_NAME) ;
           EXCEPTION WHEN OTHERS
           THEN
             emdw_log.debug('QJN: ' || checkUser || 
                            ' has no access to any of the job targets', 
                            CHECK_MODULE_NAME) ;
           END;
        END IF ;

GOTO end_device_loop;
      END IF;
    END IF;
 
    -- Check the notification schedule to see if the device is available
    IF device.type NOT IN (SHORT_EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE) OR
       DEVICE_AVAILABLE(device.device_name, device.profile_name) 
    THEN
      -- Check the status to make sure it is contactable
      IF device.status = DEVICE_UP
      THEN
        qstart := SYSDATE;

IF device.type = PROGRAM_DEVICE_TYPE AND
           IS_WINDOWS_PROGRAM(device.program)
        THEN
          l_device_type := WINDOWS_PROGRAM_DEVICE_TYPE;
        ELSE
          l_device_type := device.type;
        END IF;

-- Get the queue name for this user/object combination
        qname := GET_QUEUE(l_device_type, device.profile_name, 
                           v_state_change.job_id);
        IF qname IS NOT NULL
        THEN
          -- Set up the message for the queue
          qMsg := MGMT_NOTIFY_NOTIFICATION(NORMAL_NOTIFICATION, IS_NOT_PING,
                                           JOB_STATE_CHANGE, 
                                           v_state_change.state_change_guid,
                                           device.device_name, 
                                           device.profile_name,
                                           device.type,
                                           device.rule_name, 
                                           device.owner, qname, 0);
          IF emdw_log.p_is_debug_set
          THEN
             emdw_log.debug('QJN: Queueing notification for' ||
                            ' device_name=' || dname ||
                            ' owner=' || pname,
                            CHECK_MODULE_NAME) ;
          END IF ;

QUEUE(qname, qMsg);
          
          TOTAL_ENQUEUE_TIME := TOTAL_ENQUEUE_TIME + (SYSDATE - qstart);

-- Update the local and overall stats
          IF device.type IN (LONG_EMAIL_DEVICE_TYPE,
                             SHORT_EMAIL_DEVICE_TYPE)
          THEN
            NUM_JOB_EMAILS_QUEUED := NUM_JOB_EMAILS_QUEUED + 1;
            num_emails := num_emails + 1;
          ELSIF device.type = PROGRAM_DEVICE_TYPE
          THEN
            NUM_JOB_OSCMDS_QUEUED := NUM_JOB_OSCMDS_QUEUED + 1;
            num_oscmds := num_oscmds + 1;
          ELSIF device.type = SNMP_DEVICE_TYPE
          THEN
            NUM_JOB_SNMPS_QUEUED := NUM_JOB_SNMPS_QUEUED + 1;
            num_snmp := num_snmp + 1;
          ELSIF device.type = PLSQL_DEVICE_TYPE
          THEN
            NUM_JOB_PLSQLS_QUEUED := NUM_JOB_PLSQLS_QUEUED + 1;
            num_plsql := num_plsql + 1;
          ELSIF device.type = JAVA_DEVICE_TYPE
          THEN
            NUM_JOB_JAVA_QUEUED := NUM_JOB_JAVA_QUEUED + 1;
            num_java := num_java + 1;
          END IF;
        ELSE
          -- Could not find a queue to service notification. Must be an
          -- OS Command where no Windows OMSs are registered
          IF emdw_log.p_is_debug_set
          THEN
             emdw_log.debug('QJN: No Windows OMS to handle OS Command',
                            CHECK_MODULE_NAME) ;
          END IF ;

LOG_HISTORY(MGMT_GLOBAL.G_ANNOTATION_SOURCE_JOB, 
                      v_state_change.state_change_guid, 'N', 
                      'A Windows based Management Server is not registered ' ||
                      '(Rule Owner=' || device.owner || ', Rule Name=' || 
                      device.rule_name || ')', l_tz_rgn);
        END IF;
      ELSE
        -- The device is currently down so set this up for retry
        IF emdw_log.p_is_debug_set
        THEN
           emdw_log.debug('QJN: Device down so setup for retry',
                          CHECK_MODULE_NAME) ;
        END IF ;

INSERT INTO mgmt_notify_requeue 
          (source_guid, source_type, notification_type,
           device_name, device_owner, device_type, rule_name, rule_owner)
        VALUES (v_state_change.state_change_guid, JOB_STATE_CHANGE, 
                RETRY_NOTIFICATION,
                device.device_name, device.profile_name, device.type,
                device.rule_name, device.owner);
      END IF;
    ELSE
      IF emdw_log.p_is_debug_set
      THEN
        emdw_log.debug('QJN: ' || dname || ' is not in notification schedule',
                       CHECK_MODULE_NAME) ;
      END IF ;
    END IF;

<< end_device_loop >>
    NULL;
  END LOOP;

IF num_emails > 0 OR num_oscmds > 0 OR num_plsql > 0 OR num_snmp > 0 OR num_java > 0
  THEN
    t := mgmt_global.sysdate_tzrgn(l_tz_rgn);

-- The message needs to be NLS'ed
    INSERT INTO MGMT_ANNOTATION (source_obj_type, source_obj_guid, timestamp,
                                 annotation_type, user_name, message)
    VALUES (JOB_STATE_CHANGE, v_state_change.state_change_guid, t, 
            'NOTIFICATION',
            '<SYSTEM>', 'The following notifications will be sent: ' ||
            decode(num_emails, 0, '', num_emails || ' e-mail messages ') ||
            decode(num_snmp, 0, '', num_snmp || ' SNMP traps ') ||
            decode(num_plsql, 0, '', num_plsql || '  PL/SQL procedures ') ||
            decode(num_oscmds, 0, '', num_oscmds || ' OS Commands ') ||
            decode(num_java, 0, '', num_java || ' Java callbacks'));
  END IF;

EXCEPTION
  WHEN OTHERS
  THEN

IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN
      -- Commit/Rollback only in central repository mode.
      -- In Standalone mode, this procedure is called in the 
      -- severity insert trigger. (Commits/Rollbacks are not allowed in triggers)
      ROLLBACK;
    END IF;

-- Reset the context back to the original user
    SETEMUSERCONTEXT(origUser, MGMT_USER.OP_SET_IDENTIFIER);
    IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN
      -- Commit/Rollback only in central repository mode.
      -- In Standalone mode, this procedure is called in the 
      -- severity insert trigger. (Commits/Rollbacks are not allowed in triggers)
      COMMIT;
    END IF;

IF emdw_log.p_is_debug_set
    THEN
      emdw_log.debug('QJN: Unexpected error ' || SQLERRM,
                     CHECK_MODULE_NAME) ;
    END IF ;

mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, 
                       MGMT_GLOBAL.UNEXPECTED_ERR,
                       MGMT_GLOBAL.UNEXPECTED_ERR_M || 
                       'QUEUE_JOB_NOTIFICATIONS (' 
                       || pname || '/' || dname || ') ' || 
                       SQLERRM, 'ORA', v_state_change.state_change_guid);
END QUEUE_JOB_NOTIFICATIONS;

--
-- PURPOSE
--  This procedure gets a list of all devices that are currently in the
--  down state and have pending notifications. It queues a notification that
--  the OMS will attempt to delivery. If the delivery succeeds the OMS has
--  to call the REQUEUE procedure 
--
PROCEDURE PING_DEVICES
IS
  sguid      RAW(16);
  hash_guid  RAW(16);
  stype      NUMBER;
  rname      VARCHAR2(64);
  rowner     VARCHAR2(256);
  qname      VARCHAR2(30);
  qMsg       MGMT_NOTIFY_NOTIFICATION;
  l_device_type NUMBER := 0;

-- This only works for retries. It needs to be updated for escalation and
  -- repeat notifications
  CURSOR devices IS
    SELECT device_name, profile_name, type, program
    FROM mgmt_notify_devices d
    WHERE d.status = DEVICE_DOWN AND EXISTS
          (SELECT 1 FROM MGMT_NOTIFY_REQUEUE r
           WHERE r.device_name = d.device_name 
             AND r.device_owner = d.profile_name 
             AND (r.notification_type = RETRY_NOTIFICATION AND
                  r.last_timestamp < SYSDATE - (r.num_requeues/1440))); 
BEGIN
  -- For each device that has notification that need to be retried
  FOR device IN devices
  LOOP
    IF device.type NOT IN (SHORT_EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE) OR
       DEVICE_AVAILABLE(device.device_name, device.profile_name) 
    THEN
      -- Get the source_guid of the oldest record
      SELECT r1.source_guid, r1.source_type, r1.rule_name, r1.rule_owner 
      INTO sguid, stype, rname, rowner 
      FROM MGMT_NOTIFY_REQUEUE r1
      WHERE r1.device_owner = device.profile_name AND
            r1.device_name = device.device_name AND
            r1.insertion_timestamp =
            (SELECT MIN(r2.insertion_timestamp) FROM MGMT_NOTIFY_REQUEUE r2
             WHERE r2.device_owner = r1.device_owner AND
                   r2.device_name = r1.device_name) AND ROWNUM = 1;
   
      BEGIN
        IF stype = JOB_STATE_CHANGE
        THEN
          SELECT job_id INTO hash_guid FROM MGMT_JOB_STATE_CHANGES
          WHERE state_change_guid = sguid;
        ELSE
          SELECT target_guid INTO hash_guid FROM MGMT_VIOLATIONS
          WHERE violation_guid = sguid;
        END IF;
      EXCEPTION WHEN NO_DATA_FOUND
      THEN
        -- The source record is missing so remove this requeue record
        DELETE FROM MGMT_NOTIFY_REQUEUE
        WHERE device_name = device.device_name
          AND device_owner = device.profile_name
          AND source_guid = sguid
          AND rule_name = rname
          AND rule_owner = rowner;
        GOTO next_device;
      END;
 
      IF device.type = PROGRAM_DEVICE_TYPE AND
         IS_WINDOWS_PROGRAM(device.program)
      THEN
        l_device_type := WINDOWS_PROGRAM_DEVICE_TYPE;
      ELSE
        l_device_type := device.type;
      END IF;

-- Get the queue name for this user/object combination
      qname := GET_QUEUE(l_device_type, device.profile_name, hash_guid);
      IF qname IS NOT NULL
      THEN
        -- Try and deliver the notification
        qMsg := MGMT_NOTIFY_NOTIFICATION(RETRY_NOTIFICATION, IS_PING, 
                                         stype, sguid, 
                                         device.device_name, 
                                         device.profile_name,
                                         device.type,
                                         rname, rowner, qname, 0);
        QUEUE(qname, qMsg);
      ELSE
        -- Could not find a queue to service notification. Must be an
        -- OS Command where no Windows OMSs are registered
        DELETE FROM MGMT_NOTIFY_REQUEUE
        WHERE device_name = device.device_name
          AND device_owner = device.profile_name
          AND source_guid = sguid
          AND rule_name = rname
          AND rule_owner = rowner;
      END IF;

-- Update the device to indicate a ping notification has been queued so
      -- that no more are queued
      UPDATE MGMT_NOTIFY_DEVICES 
         SET STATUS = DEVICE_BEING_PINGED,
             CONTACT_TIMESTAMP = SYSDATE
      WHERE device_name = device.device_name AND
            profile_name = device.profile_name;
      IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN
        -- Commit/Rollback only in central repository mode.
        -- In Standalone mode, this procedure is called in the severity insert
        -- trigger. (Commits/Rollbacks are not allowed in triggers)
        COMMIT;
      END IF;
   END IF;

<< next_device >>

IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN
     -- Commit/Rollback only in central repository mode.
     -- In Standalone mode, this procedure is called in the 
     -- severity insert trigger. (Commits/Rollbacks are not allowed in triggers)
     COMMIT;
   END IF;

END LOOP;

-- Simple mark device as DOWN and update timestamp.
  UPDATE mgmt_notify_devices 
     set status = DEVICE_DOWN,
         contact_timestamp = SYSDATE 
   WHERE status =  DEVICE_BEING_PINGED AND
         contact_timestamp < SYSDATE - ( G_DEV_PING_STATE_TIMEOUT/86400 );

IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN
   -- Commit/Rollback only in central repository mode.
   -- In Standalone mode, this procedure is called in the 
   -- severity insert trigger. (Commits/Rollbacks are not allowed in triggers)
   COMMIT;
 END IF;

EXCEPTION WHEN OTHERS
THEN
    mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME,
                       MGMT_GLOBAL.UNEXPECTED_ERR,
                       MGMT_GLOBAL.UNEXPECTED_ERR_M || 
                       'PING_DEVICES ' ||
                       SQLERRM);

END PING_DEVICES;

--
-- PURPOSE
--   To requeue all notifications for a device because the device is now
--   available again
PROCEDURE REQUEUE(v_device_name IN VARCHAR2,
                  v_device_owner IN VARCHAR2)
IS
  qMsg MGMT_NOTIFY_NOTIFICATION; 
  qname VARCHAR2(30);
  hash_guid RAW(16);
  ttl NUMBER := 1;
  l_program VARCHAR2(512);
  l_device_type NUMBER := 0;

CURSOR retries 
  IS
    SELECT source_guid, source_type, device_name, device_owner, device_type,
           rule_name, rule_owner, insertion_timestamp
    FROM mgmt_notify_requeue
    WHERE device_name = v_device_name AND
          device_owner = v_device_owner  AND
          notification_type = RETRY_NOTIFICATION
    ORDER BY insertion_timestamp ASC;

BEGIN

-- Make sure to check the device is available
  IF NOT DEVICE_AVAILABLE(v_device_name, v_device_owner)
  THEN
    RETURN;
  END IF;

-- Get the time to live of the notifications for this user
  SELECT notification_ttl INTO ttl FROM MGMT_NOTIFY_PROFILES
  WHERE profile_name = v_device_owner;

FOR r IN retries
  LOOP
    -- Check if the entry has been in the table more than a day and if so
    -- remove it
    IF r.insertion_timestamp < SYSDATE - ttl
    THEN
      DELETE FROM mgmt_notify_requeue
      WHERE device_name = r.device_name
        AND device_owner = r.device_owner
        AND source_guid = r.source_guid
        AND rule_name = r.rule_name
        AND rule_owner = r.rule_owner;
    ELSE

BEGIN
        IF r.source_type = JOB_STATE_CHANGE
        THEN
          SELECT job_id INTO hash_guid FROM MGMT_JOB_STATE_CHANGES
          WHERE state_change_guid = r.source_guid;
        ELSE
          SELECT target_guid INTO hash_guid FROM MGMT_VIOLATIONS
          WHERE violation_guid = r.source_guid;
        END IF;
      EXCEPTION WHEN NO_DATA_FOUND
      THEN
        -- The source record is missing so remove this requeue record
        DELETE FROM MGMT_NOTIFY_REQUEUE
        WHERE device_name = r.device_name
          AND device_owner = r.device_owner
          AND source_guid = r.source_guid
          AND rule_name = r.rule_name
          AND rule_owner = r.rule_owner;
        GOTO next_retry;
      END;

IF r.device_type = PROGRAM_DEVICE_TYPE
      THEN
         SELECT program INTO l_program FROM MGMT_NOTIFY_DEVICES
         WHERE device_name = r.device_name AND profile_name = r.device_owner;

IF IS_WINDOWS_PROGRAM(l_program)
         THEN
           l_device_type := WINDOWS_PROGRAM_DEVICE_TYPE;
         ELSE
           l_device_type := r.device_type;
         END IF;
      ELSE
         l_device_type := r.device_type;
      END IF;

-- Get the queue name for this user/object combination
      qname := GET_QUEUE(l_device_type, r.device_owner, hash_guid);
      IF qname IS NOT NULL
      THEN
        qMsg := MGMT_NOTIFY_NOTIFICATION(RETRY_NOTIFICATION, 0,
                                         r.source_type, r.source_guid, 
                                         r.device_name, r.device_owner,
                                         r.device_type, 
                                         r.rule_name, r.rule_owner, qname, 0);
          QUEUE(qname, qMsg);

DELETE FROM mgmt_notify_requeue
          WHERE device_name = r.device_name
            AND device_owner = r.device_owner
            AND source_guid = r.source_guid
            AND rule_name = r.rule_name
            AND rule_owner = r.rule_owner;

ELSE
        -- Could not find a queue to service notification. Must be an
        -- OS Command where no Windows OMSs are registered
        DELETE FROM MGMT_NOTIFY_REQUEUE
        WHERE device_name = r.device_name
          AND device_owner = r.device_owner
          AND source_guid = r.source_guid
          AND rule_name = r.rule_name
          AND rule_owner = r.rule_owner;
      END IF;
    END IF;

<< next_retry >>
    COMMIT;
  END LOOP;

END REQUEUE;

PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN) IS
BEGIN  
  MGMT_SQLTRACE.EXTENDED_SQL_TRACE_ON(EST_NOTIFICATION_NAME, p_value);
END DBMSJOB_EXTENDED_SQL_TRACE_ON;

-- PURPOSE
--   Main procedure to go through severity table, execute notification rules
--   and queue notifications for delivery. This should be called from a 
--   DBMS_JOB that is running on a specified interval
PROCEDURE CHECK_FOR_SEVERITIES
IS
  job_id         NUMBER;
  perf_start     TIMESTAMP;      
  sev_duration   INTERVAL DAY TO SECOND;
  job_duration   INTERVAL DAY TO SECOND;
  nSeverities    NUMBER := 0;
  nStateChanges  NUMBER := 0;
  total_queued   NUMBER := 0;
  severity       MGMT_VIOLATIONS%ROWTYPE; 
  state_change   MGMT_JOB_STATE_CHANGES%ROWTYPE;
  ca_severity    MGMT_VIOLATIONS%ROWTYPE; 
  guid_type      VARCHAR2(1); 
  notif_src_guid RAW(16); 
  last_run       DATE;
  this_run       DATE;
  l_rule_guid    RAW(16);
  l_repeat_count NUMBER;
  CURSOR job_ids IS
  SELECT job FROM USER_JOBS
   WHERE what = 'EMD_NOTIFICATION.CHECK_FOR_SEVERITIES();';

BEGIN

MGMT_SQLTRACE.EXTENDED_SQL_TRACE(EST_NOTIFICATION_NAME);

EMDW_LOG.SET_CONTEXT(v_context_Type=>G_LOG_CONTEXT,
                       v_context_Identifier=>'dbms_job');

BEGIN
    SELECT last_date, this_date INTO last_run, this_run from USER_JOBS
    WHERE what = 'EMD_NOTIFICATION.CHECK_FOR_SEVERITIES();';
  EXCEPTION
    WHEN TOO_MANY_ROWS
    THEN
      -- There should never be more than one job running this routine
      OPEN job_ids;
      FETCH job_ids INTO job_id;
      CLOSE job_ids;
      DBMS_JOB.REMOVE(job_id);
      RETURN;

WHEN OTHERS
    THEN
      BEGIN
        last_run := SYSDATE;
        this_run := SYSDATE;
      END;
  END;

IF last_run IS NULL
  THEN
    -- It will only be NULL the first time the job runs
    last_run := SYSDATE;

END IF;

IF this_run IS NULL
  THEN
    -- It should never be null
    this_run := SYSDATE;
  END IF;

-- Queue a notification for any device that is currently down that has
  -- pending retries. Note that this procedure will only
  -- attempt to queue one notification (i.e. if there are multiple retries, 
  -- only one will be queued) and update the status accordingly.
  -- The procedure REQUEUE will be called from the OMS once the message has
  -- been delivered which will result in all pending notifications being 
  -- queued
  PING_DEVICES();
  perf_start := LOCALTIMESTAMP;
  sev_duration := perf_start - perf_start; 
  job_duration := perf_start - perf_start;

-- log job run details
  IF EMDW_LOG.P_IS_INFO_SET
  THEN
    EMDW_LOG.INFO('(sysdate, last_run, this_run) = (' ||
                  to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') || ', ' ||
                  to_char(last_run, 'mm/dd/yyyy hh24:mi:ss') || ', ' ||
                  to_char(this_run, 'mm/dd/yyyy hh24:mi:ss') || ', ' ||
                  ')', 'check_for_severities');
  END IF;
  -- Go through each severity record and apply the notification rules
  WHILE TRUE
  LOOP
    BEGIN
      perf_start := LOCALTIMESTAMP;
      dequeue_notif_input(notif_src_guid, guid_type, l_rule_guid, l_repeat_count);
      IF guid_type = GUID_TYPE_SEVERITY
      THEN
        nSeverities := nSeverities + 1;
        SELECT * 
          INTO severity
          FROM mgmt_violations
         WHERE violation_guid = notif_src_guid;
        QUEUE_METRIC_NOTIFICATIONS(severity);
      ELSIF guid_type = GUID_TYPE_JOB_STATE_CHANGE 
      THEN  
        nStateChanges := nStateChanges + 1;
        SELECT * 
          INTO state_change
          FROM mgmt_job_state_changes
         WHERE state_change_guid  = notif_src_guid;

IF state_change.type = JOB_STATE_CHANGE
        THEN
          QUEUE_JOB_NOTIFICATIONS(state_change);
        ELSE
          BEGIN
            SELECT * INTO ca_severity FROM MGMT_VIOLATIONS
            WHERE violation_guid = state_change.violation_guid;

QUEUE_METRIC_NOTIFICATIONS(ca_severity, state_change);
          EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
            IF EMDW_LOG.P_IS_DEBUG_SET
            THEN
              EMDW_LOG.DEBUG('Severity record already deleted for state change job record : ' || notif_src_guid,
                             'check_for_severities');
            END IF;
          END;
        END IF;
      ELSIF guid_type = GUID_TYPE_REP_SEVERITY 
      THEN
        CHK_AND_QUEUE_REP_NOTIF(notif_src_guid, l_rule_guid, l_repeat_count);  
      END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      IF EMDW_LOG.P_IS_DEBUG_SET
      THEN
        EMDW_LOG.DEBUG('Got NO_DATA_FOUND while processing the record notif_src_guid : ' || notif_src_guid || 
        ' guid_type : ' || guid_type || ' Skipping this record', 'check_for_severities');
      END IF;
      -- commit the dequeue operation 
      COMMIT;
    WHEN OTHERS 
    THEN
      -- exit the loop if there is no message in the queue
      IF SQLCODE = QUEUE_TIMEOUT
      THEN 
        exit; 
      ELSE
        mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME,
                           MGMT_GLOBAL.UNEXPECTED_ERR,
                           MGMT_GLOBAL.UNEXPECTED_ERR_M || 'CHECK_FOR_SEVERITIES. '
                           || SQLERRM);
        -- commit the dequeue operation 
        COMMIT;
        exit; 
      END IF;  
    END;
    IF guid_type = GUID_TYPE_SEVERITY
    THEN
      sev_duration := sev_duration + LOCALTIMESTAMP - perf_start;
    ELSE  
      job_duration := job_duration + LOCALTIMESTAMP - perf_start;
    END IF;
  END LOOP;

-- Log the stats for severities handled and number of notifications queued
  IF nSeverities > 0
  THEN       
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, get_millisec_from_interval(sev_duration), 
                             SYSDATE, 'Y', PERF_NUM_SEVERITIES_CHECKED, 
                             nSeverities);
  END IF;
  total_queued := NUM_EMAILS_QUEUED + NUM_OSCMDS_QUEUED + NUM_PLSQLS_QUEUED +
                  NUM_SNMPS_QUEUED + NUM_RCAS_QUEUED + NUM_JAVA_QUEUED;
  IF total_queued > 0
  THEN       
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 
                             (TOTAL_ENQUEUE_TIME * (24 * 60 * 60 * 1000)),
                             SYSDATE, 'Y', PERF_NUM_QUEUED, 
                             total_queued);
  END IF;

IF NUM_EMAILS_QUEUED > 0
  THEN       
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, 
                             SYSDATE, 'N', PERF_NUM_EMAILS_QUEUED,
                             NUM_EMAILS_QUEUED);
  END IF;
  IF NUM_OSCMDS_QUEUED > 0
  THEN       
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, 
                             SYSDATE, 'N', PERF_NUM_OSCMDS_QUEUED,
                             NUM_OSCMDS_QUEUED);
  END IF;
  IF NUM_PLSQLS_QUEUED > 0
  THEN       
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, 
                             SYSDATE, 'N', PERF_NUM_PLSQLS_QUEUED,
                             NUM_PLSQLS_QUEUED);
  END IF;
  IF NUM_SNMPS_QUEUED > 0
  THEN       
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, 
                             SYSDATE, 'N', PERF_NUM_SNMPS_QUEUED,
                             NUM_SNMPS_QUEUED);
  END IF;
  IF NUM_JAVA_QUEUED > 0
  THEN       
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, 
                             SYSDATE, 'N', PERF_NUM_JAVA_QUEUED,
                             NUM_JAVA_QUEUED);
  END IF;
  -- Log the stats for state changes handled and number of notifications queued
                                                                                
  IF nStateChanges > 0
  THEN
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, get_millisec_from_interval(sev_duration),
                             SYSDATE, 'Y', PERF_NUM_JOBSTATES_CHECKED,
                             nStateChanges);
  END IF;
  total_queued := NUM_JOB_EMAILS_QUEUED + NUM_JOB_OSCMDS_QUEUED +
                  NUM_JOB_PLSQLS_QUEUED + NUM_JOB_SNMPS_QUEUED + NUM_JOB_JAVA_QUEUED;
  IF total_queued > 0
  THEN
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME,
                             (TOTAL_JOB_ENQUEUE_TIME * (24 * 60 * 60 * 1000)),
                             SYSDATE, 'Y', PERF_NUM_JOB_QUEUED,
                             total_queued);
  END IF;
                                                                                
  IF NUM_JOB_EMAILS_QUEUED > 0
  THEN
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0,
                             SYSDATE, 'N', PERF_NUM_JOB_EMAILS_QUEUED,
                             NUM_JOB_EMAILS_QUEUED);
  END IF;
  IF NUM_JOB_OSCMDS_QUEUED > 0
  THEN
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0,
                             SYSDATE, 'N', PERF_NUM_JOB_OSCMDS_QUEUED,
                             NUM_JOB_OSCMDS_QUEUED);
  END IF;
  IF NUM_JOB_PLSQLS_QUEUED > 0
  THEN
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0,
                             SYSDATE, 'N', PERF_NUM_JOB_PLSQLS_QUEUED,
                             NUM_JOB_PLSQLS_QUEUED);
  END IF;
  IF NUM_JOB_SNMPS_QUEUED > 0
  THEN
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0,
                             SYSDATE, 'N', PERF_NUM_JOB_SNMPS_QUEUED,
                             NUM_JOB_SNMPS_QUEUED);
  END IF;
  IF NUM_JOB_JAVA_QUEUED > 0
  THEN
    mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0,
                             SYSDATE, 'N', PERF_NUM_JOB_JAVA_QUEUED,
                             NUM_JOB_JAVA_QUEUED);
  END IF;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
  ROLLBACK;
  -- If we got a snapshot too old error because the query to get the
  -- severities ran for too long due to the amount of time spent
  -- processing and queuing notifications. Just return and the job
  -- will run again
  IF SQLCODE <> -1555
  THEN
    mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME,
                       MGMT_GLOBAL.UNEXPECTED_ERR,
                       MGMT_GLOBAL.UNEXPECTED_ERR_M || 'CHECK_FOR_SEVERITIES. '
                       || SQLERRM);
  END IF;

END CHECK_FOR_SEVERITIES;

PROCEDURE CLEANUP_ADMIN_NOTIFICATIONS
IS
  dq_options      dbms_aq.dequeue_options_t;
  qMsg_properties dbms_aq.message_properties_t;
  admin_msgid RAW(16);
  qMsg MGMT_NOTIFY_NOTIFICATION;
BEGIN
                                                                                
  -- NOTE: This should only be run once the MGMT_NOTIFY_QUEUES has
  -- been locked

IF G_RECURSIVE_CLEANUP
  THEN
    RETURN;
  END IF;
                                                                                
  -- Cleanup any old admin messages for OMSs that have gone away. This
  -- should not be necessary if the failover callback is reliably issued
  -- for each OMS that goes down
  FOR n IN (SELECT DISTINCT msg_id, consumer_name FROM aq$mgmt_notify_qtable
            WHERE msg_state = 'READY'
              AND consumer_name LIKE 'ADM%'
              AND consumer_name NOT IN (SELECT 'ADM'||failover_id 
                                        FROM MGMT_FAILOVER_TABLE))
  LOOP
    dq_options.consumer_name := n.consumer_name;
    dq_options.msgid := n.msg_id;
    BEGIN
      DBMS_AQ.DEQUEUE(queue_name => G_QUEUE_NAME,
                      dequeue_options    => dq_options,
                      message_properties => qMsg_properties,
                      payload            => qMsg,
                      msgid              => admin_msgid);
    EXCEPTION
    WHEN OTHERS
    THEN
      NULL;
    END;
  END LOOP;
                                                                                
  -- Unassign any queues for OMSs not in the failover table
  FOR oms IN (SELECT DISTINCT oms_id FROM MGMT_NOTIFY_QUEUES
              WHERE oms_id <> 0
                AND oms_id NOT IN (SELECT failover_id FROM MGMT_FAILOVER_TABLE))
  LOOP
    G_RECURSIVE_CLEANUP := TRUE;
    UNREGISTER_OMS(oms.oms_id);
    G_RECURSIVE_CLEANUP := FALSE;
  END LOOP;

EXCEPTION
WHEN OTHERS
THEN
  G_RECURSIVE_CLEANUP := FALSE;
  raise;

END CLEANUP_ADMIN_NOTIFICATIONS;

PROCEDURE REGISTER_OMS(oms_id_in IN NUMBER,
                       admin_qname_out OUT VARCHAR2,
                       windows_in IN NUMBER DEFAULT 0)
IS
  admin_qname VARCHAR2(30);
  nqueues NUMBER;
  nqueues_per_oms NUMBER;
  noms NUMBER;
  num_available NUMBER;
  num_reassigned NUMBER := 0;
  num_this_oms_reassigned NUMBER := 0;
  old_qname VARCHAR2(30);
  oms_ids SMP_EMD_INTEGER_ARRAY;
  qs_handled SMP_EMD_INTEGER_ARRAY;
  qMsg MGMT_NOTIFY_NOTIFICATION;
  device_types SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY('EMAIL%', 'OSCMD%',
                                                            'PLSQL%', 'SNMP%',
                                                            'JAVA%');
BEGIN

admin_qname := 'ADM' || oms_id_in;
  admin_qname_out := admin_qname;

-- Lock the queue table
  LOCK TABLE MGMT_NOTIFY_QUEUES IN EXCLUSIVE MODE;

-- Cleanup any old admin messages for OMSs that have gone away. This
  -- should not be necessary if the failover callback is reliably issued
  -- for each OMS that goes down
  CLEANUP_ADMIN_NOTIFICATIONS;
  -- Get the OMS names and the number of queues they are handling
  SELECT oms_id, count(qname) nqs_handled
  BULK COLLECT INTO oms_ids, qs_handled
  FROM MGMT_NOTIFY_QUEUES WHERE oms_id <> 0
  GROUP BY oms_id
  ORDER BY nqs_handled desc;
  noms := oms_ids.COUNT;

-- If there are no OMSs registered then assign all the queues to this OMS
  IF noms = 0
  THEN
    UPDATE MGMT_NOTIFY_QUEUES SET oms_id = oms_id_in,
                                  windows = windows_in;
    FOR q IN (SELECT qname FROM MGMT_NOTIFY_QUEUES)
    LOOP
      qMsg := MGMT_NOTIFY_NOTIFICATION(START_DEQUEUEING, 0, -1, NULL, 
                                       q.qname, oms_id_in, QUEUE_DEVICE_TYPE, 
                                       NULL, NULL, admin_qname, 0);
      QUEUE(admin_qname, qMsg);
    END LOOP;

COMMIT;
    RETURN;
  END IF;

-- Get the total number of queues
  SELECT COUNT(qname) INTO nqueues FROM MGMT_NOTIFY_QUEUES;

-- Work out how many queues there should be per OMS
  nqueues_per_oms :=  trunc(nqueues / (noms + 1));
  IF nqueues_per_oms = 0
  THEN
    COMMIT;
    RETURN;
  END IF;

-- Go through each current OMS and update the queues it is handling
  FOR i IN oms_ids.FIRST..oms_ids.LAST
  LOOP
    -- If enough queues have been reassigned then finish
    IF num_reassigned >= nqueues_per_oms
    THEN
      COMMIT;
      RETURN; 
    END IF;

-- Work out how many queues this OMS can reassign
    num_available := qs_handled(i) - nqueues_per_oms;

-- If it can spare more than necessary then limit it so a queue is not
    -- reassigned unnecessarily
    IF num_available > nqueues_per_oms
    THEN
      num_available := nqueues_per_oms;
    END IF;

-- Make sure there are some available for reassigning
    IF num_available > 0
    THEN
      -- Try and spread the different queue types across OMSs so that each
      -- one is handling a variety of device types
      num_this_oms_reassigned := 0;
      WHILE (num_this_oms_reassigned < num_available)
      LOOP
        FOR dt IN (SELECT column_value FROM 
                   (TABLE(CAST(device_types AS SMP_EMD_STRING_ARRAY)))) 
        LOOP
          IF num_this_oms_reassigned = num_available
          THEN
            EXIT;
          END IF;
          BEGIN
            -- Get the qname being ressigned
            SELECT qname INTO old_qname FROM MGMT_NOTIFY_QUEUES 
            WHERE oms_id = oms_ids(i) AND qname LIKE dt.column_value
              AND rownum < 2;

-- Reassign the names 
            UPDATE MGMT_NOTIFY_QUEUES SET oms_id = oms_id_in,
                                          windows = windows_in
            WHERE qname = old_qname;

-- Tell the OMS to stop monitoring the queue
            qMsg := MGMT_NOTIFY_NOTIFICATION(STOP_DEQUEUEING, 0, -1, NULL, 
                                             old_qname, oms_ids(i),
                                             QUEUE_DEVICE_TYPE, NULL, NULL,
                                             'ADM' || oms_ids(i), 0);
            QUEUE('ADM' || oms_ids(i), qMsg);

qMsg := MGMT_NOTIFY_NOTIFICATION(START_DEQUEUEING, 0, -1, NULL, 
                                             old_qname, oms_id_in,
                                             QUEUE_DEVICE_TYPE, NULL, NULL,
                                             admin_qname, 0);
            QUEUE(admin_qname, qMsg);
            -- Update total
            num_reassigned := num_reassigned + 1;
            num_this_oms_reassigned := num_this_oms_reassigned + 1;

EXCEPTION WHEN NO_DATA_FOUND THEN
           NULL;
          END;
        END LOOP;
      END LOOP;
    END IF;
  END LOOP;

COMMIT;
  
EXCEPTION
WHEN NO_DATA_FOUND
THEN
  ROLLBACK;
END REGISTER_OMS;

-- PURPOSE
--   To unregister an OMS from the notification system so that its queues
--   can be reassigned
PROCEDURE UNREGISTER_OMS(oms_id_in IN NUMBER)
IS
BEGIN
  OMS_FAILOVER(oms_id_in, SYSDATE);
END UNREGISTER_OMS;

-- PURPOSE
--  To handle failover when an OMS goes down
PROCEDURE OMS_FAILOVER(oms_id_in IN NUMBER,
                       last_timestamp_in IN DATE)
IS
  noms NUMBER;
  num_reassigned NUMBER := 0;
  num_for_reassignment NUMBER := 0;
  new_qname VARCHAR2(30);
  oms_ids SMP_EMD_INTEGER_ARRAY;
  qs_handled SMP_EMD_INTEGER_ARRAY;
  is_windows SMP_EMD_INTEGER_ARRAY;
  qMsg MGMT_NOTIFY_NOTIFICATION;
  more_messages BOOLEAN := TRUE;
  admin_msgid RAW(16);
BEGIN

-- Remove any messages in the queue for this OMS's admin queue
  WHILE (more_messages)
  LOOP
    BEGIN
      DEQUEUE('ADM' || oms_id_in, 1, admin_msgid, qMsg);
    EXCEPTION
    WHEN OTHERS
    THEN
      more_messages := FALSE;
    END;
  END LOOP;

-- Lock the queue table
  LOCK TABLE MGMT_NOTIFY_QUEUES IN EXCLUSIVE MODE;

-- Unassign the queues for this OMS
  UPDATE MGMT_NOTIFY_QUEUES SET oms_id = 0, windows = 0
  WHERE oms_id = oms_id_in;

-- Get the OMS names and the number of queues they are handling
  SELECT oms_id, count(qname) nqs_handled, sum(windows)
  BULK COLLECT INTO oms_ids, qs_handled, is_windows
  FROM MGMT_NOTIFY_QUEUES WHERE oms_id <> 0
  GROUP BY oms_id
  ORDER BY nqs_handled asc;
  noms := oms_ids.COUNT;

-- If there are no OMSs registered then just return
  IF noms = 0
  THEN
    RETURN;
  END IF;

-- Get the total number of queues and the number for reassigment
  SELECT COUNT(qname) INTO num_for_reassignment FROM MGMT_NOTIFY_QUEUES
  WHERE oms_id = 0;

WHILE num_reassigned < num_for_reassignment
  LOOP
    -- Go through each current OMS and assign it some queues
    FOR i IN oms_ids.FIRST..oms_ids.LAST
    LOOP

IF num_reassigned = num_for_reassignment
      THEN
        RETURN;
      END IF;

-- Get the qnames being ressigned
      SELECT qname INTO new_qname
      FROM MGMT_NOTIFY_QUEUES 
      WHERE oms_id = 0 AND rownum < 2;

-- Reassign the names 
      IF is_windows(i) > 0
      THEN
        UPDATE MGMT_NOTIFY_QUEUES SET oms_id = oms_ids(i), windows = 1
        WHERE qname = new_qname;
      ELSE
        UPDATE MGMT_NOTIFY_QUEUES SET oms_id = oms_ids(i), windows = 0
        WHERE qname = new_qname;
      END IF;

-- Tell the OMS to start servicing the new queue by sending a message
      -- on its admin queue
      qMsg := MGMT_NOTIFY_NOTIFICATION(START_DEQUEUEING, 0, -1, NULL, 
                                       new_qname, oms_ids(i), 
                                       QUEUE_DEVICE_TYPE, NULL, NULL,
                                       'ADM' || oms_ids(i), 0);
      QUEUE('ADM' || oms_ids(i), qMsg);

-- Update total
      num_reassigned := num_reassigned + 1;

END LOOP;
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
    MGMT_LOG.log_error(DELIVER_MODULE_NAME, SQLCODE, 
              'Error during failover: ' || SQLERRM);
    raise;
END OMS_FAILOVER;

-- PURPOSE
--   Set the number of notification queues
--
-- PARAMETERS
--  
PROCEDURE SET_NUMBER_QUEUES(
 v_num_email_qs IN NUMBER DEFAULT DEFAULT_NOTIFY_EMAIL_QUEUES,
 v_num_oscmd_qs IN NUMBER DEFAULT DEFAULT_NOTIFY_OSCMD_QUEUES,
 v_num_plsql_qs IN NUMBER DEFAULT DEFAULT_NOTIFY_PLSQL_QUEUES,
 v_num_snmp_qs IN NUMBER DEFAULT DEFAULT_NOTIFY_SNMP_QUEUES,
 v_num_java_qs IN NUMBER DEFAULT DEFAULT_NOTIFY_JAVA_QUEUES)
IS
BEGIN
 NULL;
END SET_NUMBER_QUEUES;

FUNCTION GET_PARAMETER_FORMATS(v_device_name IN VARCHAR2,
                               v_device_owner IN VARCHAR2)
RETURN MGMT_NOTIFY_PARAMETER_FORMATS
IS
  formats MGMT_NOTIFY_PARAMETER_FORMATS;
BEGIN

-- Get the parameters that should be included in the notifications
  SELECT MGMT_NOTIFY_PARAMETER_FORMAT(parameter, abbreviated, timestamp_format)
  BULK COLLECT INTO formats
  FROM MGMT_NOTIFY_DEVICE_PARAMS
  WHERE device_name = v_device_name AND profile_name = v_device_owner AND
        parameter LIKE '$%'
  ORDER BY position;

-- If no parameter customization has been done, then send all the parameters
  IF formats.COUNT = 0
  THEN
    formats.extend(12);
    formats(1) := MGMT_NOTIFY_PARAMETER_FORMAT('$TARGET_NAME', 0, NULL);
    formats(2) := MGMT_NOTIFY_PARAMETER_FORMAT('$TARGET_TYPE', 0, NULL);
    formats(3) := MGMT_NOTIFY_PARAMETER_FORMAT('$HOST', 0, NULL);
    formats(4) := MGMT_NOTIFY_PARAMETER_FORMAT('$METRIC', 0, NULL);
    formats(5) := MGMT_NOTIFY_PARAMETER_FORMAT('$METRIC_DESCRIPTION', 0, NULL);
    formats(6) := MGMT_NOTIFY_PARAMETER_FORMAT('$KEY_VALUE', 0, NULL);
    formats(7) := MGMT_NOTIFY_PARAMETER_FORMAT('$TIMESTAMP', 0, NULL);
    formats(8) := MGMT_NOTIFY_PARAMETER_FORMAT('$SEVERITY', 0, NULL);
    formats(9) := MGMT_NOTIFY_PARAMETER_FORMAT('$MESSAGE', 0, NULL);
    formats(10) := MGMT_NOTIFY_PARAMETER_FORMAT('$RULE_NAME', 0, NULL);
    formats(11) := MGMT_NOTIFY_PARAMETER_FORMAT('$RULE_OWNER', 0, NULL);
  END IF;

RETURN formats;

END GET_PARAMETER_FORMATS;

-- PURPOSE
--  To get the label for a key column belonging to the metric in a severity
--
FUNCTION GET_KEYS(v_metric_guid IN RAW,
                  v_target_guid IN RAW,
                  v_key_value_in IN VARCHAR2)
RETURN MGMT_NOTIFY_COLUMNS
IS
  kcol VARCHAR2(512);
  key VARCHAR2(64);
  pos NUMBER;
  lpos NUMBER;
  label VARCHAR2(64);
  nlsid VARCHAR2(64);
  nkeys NUMBER;
  checking boolean;
  keys MGMT_NOTIFY_COLUMNS := MGMT_NOTIFY_COLUMNS();
  p1 VARCHAR2(256);
  p2 VARCHAR2(256);
  p3 VARCHAR2(256);
  p4 VARCHAR2(256);
  p5 VARCHAR2(256);
  key_val VARCHAR2(256);
BEGIN

IF v_key_value_in = ' '
  THEN
    RETURN NULL;
  END IF;

-- Get the metric name and column
  SELECT key_column INTO kcol
  FROM MGMT_TARGETS t, MGMT_METRICS m
  WHERE t.target_guid = v_target_guid
    AND m.metric_guid = v_metric_guid
    AND t.target_type = m.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 = ' ');

BEGIN
    -- Check it this is a composite key
    SELECT key_part1_value,  key_part2_value, key_part3_value,
           key_part4_value, key_part5_value
    INTO p1, p2, p3, p4, p5
    FROM MGMT_METRICS_COMPOSITE_KEYS
    WHERE composite_key = v_key_value_in
      AND target_guid = v_target_guid;
  EXCEPTION WHEN NO_DATA_FOUND
  THEN
    -- Not a composite key, so get the details of the key column
    SELECT m1.column_label, m1.column_label_nlsid
    INTO label, nlsid
    FROM MGMT_TARGETS t, MGMT_METRICS m, MGMT_METRICS m1
    WHERE t.target_guid = v_target_guid
      AND m.metric_guid = v_metric_guid
      AND t.target_type = m.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 t.target_type = m1.target_type
      AND m1.type_meta_ver = t.type_meta_ver
      AND (m1.category_prop_1 = t.category_prop_1 or m1.category_prop_1 = ' ')
      AND (m1.category_prop_2 = t.category_prop_2 or m1.category_prop_2 = ' ')
      AND (m1.category_prop_3 = t.category_prop_3 or m1.category_prop_3 = ' ')
      AND (m1.category_prop_4 = t.category_prop_4 or m1.category_prop_4 = ' ')
      AND (m1.category_prop_5 = t.category_prop_5 or m1.category_prop_5 = ' ')
      AND m1.metric_name = m.metric_name
      AND m1.metric_column = kcol;

IF label IS NULL OR label = ' '
    THEN
      label := key;
    END IF;

keys.extend;
    keys(1) := MGMT_NOTIFY_COLUMN(label, nlsid, v_key_value_in);
    RETURN keys;
  END;

nkeys := 1;
  lpos := 0;
  checking := true;

WHILE checking 
  LOOP
    pos := INSTR(kcol,';', 1, nkeys);

IF pos = 0 
    THEN
      checking := false;
      pos := LENGTH(kcol) + 1;
    END IF;

key := SUBSTR(kcol, lpos+1, pos-lpos-1);

SELECT m1.column_label, m1.column_label_nlsid
    INTO label, nlsid
    FROM MGMT_TARGETS t, MGMT_METRICS m, MGMT_METRICS m1
    WHERE t.target_guid = v_target_guid
      AND m.metric_guid = v_metric_guid
      AND t.target_type = m.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 t.target_type = m1.target_type
      AND m1.type_meta_ver = t.type_meta_ver
      AND (m1.category_prop_1 = t.category_prop_1 or m1.category_prop_1 = ' ')
      AND (m1.category_prop_2 = t.category_prop_2 or m1.category_prop_2 = ' ')
      AND (m1.category_prop_3 = t.category_prop_3 or m1.category_prop_3 = ' ')
      AND (m1.category_prop_4 = t.category_prop_4 or m1.category_prop_4 = ' ')
      AND (m1.category_prop_5 = t.category_prop_5 or m1.category_prop_5 = ' ')
      AND m1.metric_name = m.metric_name 
      AND m1.metric_column = key;

IF nkeys = 1 THEN key_val := p1;
    ELSIF nkeys = 2 THEN key_val := p2; 
    ELSIF nkeys = 3 THEN key_val := p3; 
    ELSIF nkeys = 4 THEN key_val := p4; 
    ELSIF nkeys = 5 THEN key_val := p5; 
    END IF;
 
    IF label IS NULL OR label = ' '
    THEN
      label := key;
    END IF;

keys.extend;
    keys(keys.COUNT) := MGMT_NOTIFY_COLUMN(label, nlsid, key_val);

lpos := pos;
    nkeys := nkeys + 1;
  END LOOP;

RETURN keys;

EXCEPTION WHEN NO_DATA_FOUND
THEN
  RETURN NULL;

END GET_KEYS;

-- PURPOSE
--   Procedure to specify one or more SMTP gateways
--
PROCEDURE SET_EMAIL_GATEWAYS(v_gateways      IN MGMT_MEDIUM_STRING_ARRAY,
                             v_smtp_ports    IN MGMT_INTEGER_ARRAY,
                             v_email_address IN VARCHAR2,
                             v_email_name    IN VARCHAR2,
                             v_smtp_user     IN VARCHAR2 DEFAULT '',
                             v_smtp_pwd      IN VARCHAR2 DEFAULT '')
IS
BEGIN

-- Make sure some gateways have been entered
  IF v_gateways IS NULL OR v_gateways.COUNT = 0
  THEN
    raise_application_error(MGMT_GLOBAL.NOTIFICATION_NOGATEWAY_ERR,
                            MGMT_GLOBAL.NOTIFICATION_NOGATEWAY_ERR_MSG);
  END IF;

DELETE FROM mgmt_notify_email_gateway;

FOR i IN v_gateways.FIRST..v_gateways.LAST
  LOOP
    INSERT INTO mgmt_notify_email_gateway (MAIL_HOST, SMTP_PORT, EMAIL_ADDRESS, 
                                           EMAIL_NAME, SMTP_USER, SMTP_PWD,
                                           PRECEDENCE)
    VALUES (v_gateways(i), v_smtp_ports(i), v_email_address, v_email_name, 
            v_smtp_user, ENCRYPT(v_smtp_pwd), i);
  END LOOP;

END SET_EMAIL_GATEWAYS;

-- PURPOSE
--   Procedure to specify a number of email gateways.
--
PROCEDURE SET_EMAIL_GATEWAYS(v_gateways      IN SMP_EMD_STRING_ARRAY,
                             v_email_address IN VARCHAR2,
                             v_email_name    IN VARCHAR2)
IS
BEGIN
  -- Make sure some gateways have been entered
  IF v_gateways IS NULL OR v_gateways.COUNT = 0
  THEN
    raise_application_error(MGMT_GLOBAL.NOTIFICATION_NOGATEWAY_ERR,
                            MGMT_GLOBAL.NOTIFICATION_NOGATEWAY_ERR_MSG);
  END IF;

DELETE FROM mgmt_notify_email_gateway;

FOR i IN v_gateways.FIRST..v_gateways.LAST
  LOOP
    INSERT INTO mgmt_notify_email_gateway (MAIL_HOST, EMAIL_ADDRESS, 
                                           EMAIL_NAME, PRECEDENCE)
    VALUES (v_gateways(i), v_email_address, v_email_name, i);
  END LOOP;

END SET_EMAIL_GATEWAYS;

-- Procedure to add an administrator profile. This will raise an exception
-- if there is already a profile with the same name
PROCEDURE ADD_ADMIN_PROFILE(v_profile_name IN VARCHAR2,
                            v_description  IN VARCHAR2)
IS
BEGIN

INSERT INTO mgmt_notify_profiles (PROFILE_NAME, DESCRIPTION)
  VALUES (UPPER(v_profile_name), v_description);

END ADD_ADMIN_PROFILE;

-- PURPOSE
--   Procedure to add the name of an administrator profile that will serve
--   as an escalation point.
PROCEDURE SET_ESCALATION_PROFILE(v_profile_name            IN VARCHAR2,
                                 v_escalation_profile_name IN VARCHAR2,
                                 v_escalation_interval     IN NUMBER)
IS
BEGIN

UPDATE MGMT_NOTIFY_PROFILES
  SET escalation_profile_name = UPPER(v_escalation_profile_name),
      escalation_interval = v_escalation_interval
  WHERE profile_name = v_profile_name;

END SET_ESCALATION_PROFILE;

-- PURPOSE
--   Procedure to delete the name of an administrator profile that will serve
--   as an escalation point.
PROCEDURE DELETE_ESCALATION_PROFILE(v_profile_name IN VARCHAR2)
IS
BEGIN

UPDATE MGMT_NOTIFY_PROFILES
  SET escalation_profile_name = '',
      escalation_interval = ''
  WHERE profile_name = UPPER(v_profile_name);

END DELETE_ESCALATION_PROFILE;

-- PURPOSE
--   Procedure to add an email notification device to an administrator profile
--
PROCEDURE ADD_EMAIL_DEVICE(v_device_name   IN VARCHAR2,
                           v_profile_name  IN VARCHAR2,
                           v_email_address IN VARCHAR2,
                           v_type          IN NUMBER        
                                              DEFAULT LONG_EMAIL_DEVICE_TYPE,
                           v_description   IN VARCHAR2 DEFAULT '')
IS
BEGIN

BEGIN
    INSERT INTO mgmt_notify_devices (DEVICE_NAME, PROFILE_NAME, TYPE,
                                     EMAIL_ADDRESS, DEVICE_DESCRIPTION)
    VALUES (v_device_name, UPPER(v_profile_name), v_type, v_email_address, 
            v_description);
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX
    THEN
      NULL;
  END;
  
END ADD_EMAIL_DEVICE;

-- PURPOSE
--   Procedure to change the email address of an existing email notification
--   device that was added via ADD_EMAIL_DEVICE
--
PROCEDURE EDIT_EMAIL_DEVICE(v_device_name   IN VARCHAR2,
                            v_profile_name  IN VARCHAR2,
                            v_email_address IN VARCHAR2,
                            v_type          IN NUMBER 
                                            DEFAULT LONG_EMAIL_DEVICE_TYPE,
                            v_description   IN VARCHAR2 DEFAULT '')
IS
  dname VARCHAR2(132);
BEGIN

UPDATE MGMT_NOTIFY_DEVICES SET EMAIL_ADDRESS = v_email_address,
                                 DEVICE_DESCRIPTION = v_description,
                                 TYPE = v_type
  WHERE
    DEVICE_NAME = v_device_name AND
    PROFILE_NAME = UPPER(v_profile_name) AND
    TYPE IN (LONG_EMAIL_DEVICE_TYPE, SHORT_EMAIL_DEVICE_TYPE)
  RETURNING DEVICE_NAME INTO dname;

IF dname IS NULL
  THEN
    ROLLBACK;

raise_application_error(-20002, v_profile_name ||
                            ' does not have an EMAIL device named ' ||
                            v_device_name || '!');
  END IF;

END EDIT_EMAIL_DEVICE;

-- PURPOSE
--   Procedure to add a program/script notification device to an administrator
--   profile
--
PROCEDURE ADD_PROGRAM_DEVICE(v_device_name   IN VARCHAR2,
                             v_profile_name  IN VARCHAR2,
                             v_program       IN VARCHAR2,
                             v_description   IN VARCHAR2 DEFAULT '',
                             v_args          IN EMDSYSTEM_ARGS DEFAULT NULL)
IS
BEGIN

INSERT INTO mgmt_notify_devices (DEVICE_NAME, PROFILE_NAME, TYPE, PROGRAM, 
                                   DEVICE_DESCRIPTION )
  VALUES (v_device_name, UPPER(v_profile_name), PROGRAM_DEVICE_TYPE, 
          v_program, v_description);

If v_args IS NOT NULL and v_args.COUNT > 0
  THEN
   FOR i IN v_args.FIRST..v_args.LAST
   LOOP
     INSERT INTO mgmt_notify_device_params 
      (device_name, profile_name, parameter, position)
     VALUES (v_device_name, v_profile_name, v_args(i), i);
   END LOOP;
  END IF;

END ADD_PROGRAM_DEVICE;

-- PURPOSE
--   Procedure to change the program/script of an existing notification device
--   device that was added via ADD_PROGRAM_DEVICE
--
PROCEDURE EDIT_PROGRAM_DEVICE(v_device_name   IN VARCHAR2,
                              v_profile_name  IN VARCHAR2,
                              v_program       IN VARCHAR2,
                              v_description   IN VARCHAR2 DEFAULT '',
                              v_args          IN EMDSYSTEM_ARGS DEFAULT NULL)
IS
  dname VARCHAR2(132);
BEGIN

UPDATE MGMT_NOTIFY_DEVICES SET PROGRAM = v_program,
                                 DEVICE_DESCRIPTION = v_description
  WHERE
    DEVICE_NAME = v_device_name AND
    PROFILE_NAME = UPPER(v_profile_name) AND
    TYPE = PROGRAM_DEVICE_TYPE
  RETURNING DEVICE_NAME INTO dname;

IF dname IS NULL
  THEN
    ROLLBACK;
    
    raise_application_error(-20002, v_profile_name ||
                            ' does not have a PROGRAM device named ' ||
                            v_device_name || '!');
  END IF;

-- Always delete the parameters
  DELETE FROM MGMT_NOTIFY_DEVICE_PARAMS
  WHERE device_name = v_device_name AND profile_name = v_profile_name;

END EDIT_PROGRAM_DEVICE;

-- PURPOSE
--   Procedure to add a java callback notification device to an administrator
--   profile
-- PARAMETERS
--    V_DEVICE_NAME - the name of the device
--    V_PROFILE_NAME - the name of the admin profile
--    V_CLASS - this is the name of fully qualified class name that will be invoked 
--                on delivering the notification. Can be used to integrate 
--                other problem tracking systems
--    V_DESCRIPTION - the description of the device
PROCEDURE ADD_JAVA_DEVICE(v_device_name   IN VARCHAR2,
                          v_profile_name  IN VARCHAR2,
                          v_class         IN VARCHAR2,
                          v_description   IN VARCHAR2 DEFAULT '',
                          v_context       IN MGMT_MEDIUM_STRING_TABLE DEFAULT NULL)
IS
  l_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER;
BEGIN

-- Ensure that the caller is a super-user.
   IF MGMT_USER.has_priv(l_current_user, MGMT_USER.SUPER_USER) = 0 THEN
      raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR,
      'Only superusers can call ADD_JAVA_DEVICE');
   END IF;

INSERT INTO mgmt_notify_devices (DEVICE_NAME, PROFILE_NAME, TYPE, PROGRAM, 
                                   DEVICE_DESCRIPTION )
  VALUES (v_device_name, UPPER(v_profile_name), JAVA_DEVICE_TYPE, 
          v_class, v_description);

IF v_context IS NOT NULL and v_context.COUNT > 0
  THEN
   FOR i IN v_context.FIRST..v_context.LAST
   LOOP
     INSERT INTO mgmt_notify_device_params 
      (device_name, profile_name, parameter, position)
     VALUES (v_device_name, UPPER(v_profile_name), v_context(i), i);
   END LOOP;
  END IF;
END ADD_JAVA_DEVICE;

--
-- PROCEDURE: EDIT_JAVA_DEVICE
--
-- PURPOSE:
--   Modify the existing notification device of type Java.
--   Update p_class, p_description and context for the given p_device_name and p_profile_name
--   New context will override the previous context 
--
-- IN Parameters:
--   v_device_name  : Name of the notification device
--   v_profile_name : The name of the admin profile  
--   v_class        : Fully qualified name of the class implementing the notification interface
--   v_description  : Description for this device
--   v_context      : String array of context that will be passed to the notify method while delivering the notification.
--
-- OUT Parameters:
--    NONE
--
PROCEDURE EDIT_JAVA_DEVICE(v_device_name  IN VARCHAR2,
                           v_profile_name IN VARCHAR2,
                           v_class        IN VARCHAR2,
                           v_description  IN VARCHAR2 DEFAULT '',
                           v_context      IN MGMT_MEDIUM_STRING_TABLE DEFAULT NULL)
IS
  dname VARCHAR2(132);
  l_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER;
BEGIN

UPDATE MGMT_NOTIFY_DEVICES SET PROGRAM = v_class,
                                 DEVICE_DESCRIPTION = v_description
  WHERE
    DEVICE_NAME = v_device_name AND
    PROFILE_NAME = UPPER(v_profile_name) AND
    TYPE = JAVA_DEVICE_TYPE
  RETURNING DEVICE_NAME INTO dname;

IF dname IS NULL
  THEN
    ROLLBACK;

raise_application_error(-20002, v_profile_name ||
                            ' does not have a JAVA device named ' ||
                            v_device_name || '!');
  END IF;

-- Always delete the parameters
  DELETE FROM MGMT_NOTIFY_DEVICE_PARAMS
  WHERE device_name = v_device_name 
    AND profile_name = UPPER(v_profile_name);

IF v_context IS NOT NULL and v_context.COUNT > 0
  THEN
   FOR i IN v_context.FIRST..v_context.LAST
   LOOP
     INSERT INTO mgmt_notify_device_params
      (device_name, profile_name, parameter, position)
     VALUES (v_device_name, UPPER(v_profile_name), v_context(i), i);
   END LOOP;
  END IF;

END EDIT_JAVA_DEVICE;

-- PURPOSE
--   Function to test a PL/SQL notification device to see if it can be
--   called
FUNCTION TEST_PLSQL_DEVICE(v_plsql_proc IN VARCHAR2, v_err_msg OUT VARCHAR2)
RETURN NUMBER
IS
  sev MGMT_NOTIFY_SEVERITY;
  jsc MGMT_NOTIFY_JOB;
  casc MGMT_NOTIFY_CORRECTIVE_ACTION;
BEGIN

-- Make sure the procedure is valid before trying to execute it
  IF NOT em_check.is_valid_signature(v_plsql_proc,
                             mgmt_short_string_array('MGMT_NOTIFY_SEVERITY'),
                             v_err_msg) 
     AND 
     NOT em_check.is_valid_signature(v_plsql_proc,
                             mgmt_short_string_array('MGMT_NOTIFY_JOB'),
                             v_err_msg) 
     AND 
     NOT em_check.is_valid_signature(v_plsql_proc,
                      mgmt_short_string_array('MGMT_NOTIFY_CORRECTIVE_ACTION'),
                      v_err_msg) 
  THEN
    -- The procedure is not valid so return failure
    RETURN 0;
  END IF;

BEGIN
    -- Try a severity first
    sev := MGMT_NOTIFY_SEVERITY('Filesystem Rule','SYSMAN',
                                'myhost.com', 'host', '-07:00', 'myhost.com',
                                'Filesystems', 'Size of the filesystem',
                                'Filesystem Size (MB)', '94', 'Mount Point', 
                                '/usr', SYS_GUID(), SYSDATE, 20,
                                MGMT_GLOBAL.NOTIFICATION_PLSQL_TEST_MSG,
                                SYS_GUID(), SYS_GUID(), SYS_GUID(),
                                NULL);
    
    EXECUTE IMMEDIATE 'CALL ' ||
                       EM_CHECK.QUALIFIED_SQL_NAME(v_plsql_proc) || '(:s)'
                       USING sev;

RETURN 1;

EXCEPTION WHEN OTHERS
  THEN
    -- If the error is ORA-06576: not a valid function or procedure name
    IF SQLCODE = -6576
    THEN
      v_err_msg := SQLERRM;
      RETURN 0;
    END IF;
  END;

BEGIN
    -- Try a job notification
    jsc := MGMT_NOTIFY_JOB('Job Rule', 'SYSMAN', 'My Job', 'SYSMAN', 
                           'OSCommand', 1, SYSDATE, SYS_GUID(), 
                           SYS_GUID(), SYS_GUID(),
                           SMP_EMD_NVPAIR_ARRAY(SMP_EMD_NVPAIR('myhost.com',
                                                               'host')));
    EXECUTE IMMEDIATE 'CALL ' ||
                       EM_CHECK.QUALIFIED_SQL_NAME(v_plsql_proc) || '(:s)'
                       USING jsc;

RETURN 1;

EXCEPTION WHEN OTHERS
  THEN
    NULL;
  END;

BEGIN
    -- Try a corrective action notification
    casc := MGMT_NOTIFY_CORRECTIVE_ACTION
              ('Filesystem Rule', 'SYSMAN', 'Fix Filesystem', 'SYSMAN', 
               'OSCommand', 1, SYSDATE, SYS_GUID(), SYS_GUID(), SYS_GUID(),
                SMP_EMD_NVPAIR_ARRAY(SMP_EMD_NVPAIR('myhost.com', 'host')),
                'Filesystems', 'Filesystem Size (MB)', '94', 25,
                'Mount Point', '/usr', SYS_GUID(), NULL);

EXECUTE IMMEDIATE 'CALL ' ||
                       EM_CHECK.QUALIFIED_SQL_NAME(v_plsql_proc) || '(:s)'
                       USING casc;

RETURN 1;

EXCEPTION
  WHEN OTHERS
  THEN
    v_err_msg := SQLERRM;
    RETURN 0;
  END;

END TEST_PLSQL_DEVICE;

-- PURPOSE
--   Procedure to add a PL/SQL notification device to an administrator
--   profile
PROCEDURE ADD_PLSQL_DEVICE(v_device_name   IN VARCHAR2,
                           v_profile_name  IN VARCHAR2,
                           v_plsql_proc    IN VARCHAR2,
                           v_description   IN VARCHAR2 DEFAULT '')
IS
  l_err_msg VARCHAR2(1024);
BEGIN

-- Make sure the procedure is valid before saving it
  IF NOT em_check.is_valid_signature(v_plsql_proc,
                             mgmt_short_string_array('MGMT_NOTIFY_SEVERITY'),
                             l_err_msg)
     AND
     NOT em_check.is_valid_signature(v_plsql_proc,
                             mgmt_short_string_array('MGMT_NOTIFY_JOB'),
                             l_err_msg)
     AND
     NOT em_check.is_valid_signature(v_plsql_proc,
                      mgmt_short_string_array('MGMT_NOTIFY_CORRECTIVE_ACTION'),
                      l_err_msg)
  THEN
    -- The procedure is not valid so raise an exception
    RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, l_err_msg);
  END IF;

INSERT INTO mgmt_notify_devices (DEVICE_NAME, PROFILE_NAME, TYPE, PROGRAM, 
                                   DEVICE_DESCRIPTION)
  VALUES (v_device_name, UPPER(v_profile_name), PLSQL_DEVICE_TYPE, 
          v_plsql_proc, v_description);

END ADD_PLSQL_DEVICE;

-- PURPOSE
--   Procedure to change the name of the PL/SQL procedure of an existing 
--   notification device that was added via ADD_PLSQL_DEVICE
PROCEDURE EDIT_PLSQL_DEVICE(v_device_name   IN VARCHAR2,
                            v_profile_name  IN VARCHAR2,
                            v_plsql_proc    IN VARCHAR2,
                            v_description   IN VARCHAR2 DEFAULT '')
IS
  dname VARCHAR2(132);
  l_err_msg VARCHAR2(1024);
BEGIN

UPDATE MGMT_NOTIFY_DEVICES SET PROGRAM = v_plsql_proc,
                                 DEVICE_DESCRIPTION = v_description
  WHERE
    DEVICE_NAME = v_device_name AND
    PROFILE_NAME = UPPER(v_profile_name) AND
    TYPE = PLSQL_DEVICE_TYPE
  RETURNING DEVICE_NAME INTO dname;

IF dname IS NULL
  THEN
    ROLLBACK;
    
    raise_application_error(-20002, v_profile_name ||
                            ' does not have a PL/SQL device named ' ||
                            v_device_name || '!');
  END IF;

END EDIT_PLSQL_DEVICE;

-- PURPOSE
--   Procedure to add an SNMP notification device to an administrator profile
PROCEDURE ADD_SNMP_DEVICE(v_device_name  IN VARCHAR2,
                          v_profile_name IN VARCHAR2,
                          v_snmp_host    IN VARCHAR2,
                          v_snmp_port    IN NUMBER DEFAULT 162,
                          v_community    IN VARCHAR2 DEFAULT 'public',
                          v_description  IN VARCHAR2 DEFAULT '')
IS
BEGIN

INSERT INTO mgmt_notify_devices (DEVICE_NAME, PROFILE_NAME, TYPE, SNMP_HOST, 
                                   SNMP_PORT, SNMP_COMMUNITY, 
                                   DEVICE_DESCRIPTION)
  VALUES (v_device_name, UPPER(v_profile_name), SNMP_DEVICE_TYPE, v_snmp_host, 
          v_snmp_port, v_community, v_description);
  
END ADD_SNMP_DEVICE;

-- PURPOSE
--   Procedure to change the snmp host of an existing SNMP notification
--   device that was added via ADD_SNMP_DEVICE
-- PARAMETERS
--    V_DEVICE_NAME - the name of the device
--    V_PROFILE_NAME - the name of the admin profile
--    V_SNMP_HOST - the new name of the snmp host
PROCEDURE EDIT_SNMP_DEVICE(v_device_name  IN VARCHAR2,
                           v_profile_name IN VARCHAR2,
                           v_snmp_host    IN VARCHAR2,
                           v_snmp_port    IN NUMBER DEFAULT 162,
                           v_community    IN VARCHAR2 DEFAULT 'public',
                           v_description  IN VARCHAR2 DEFAULT '')
IS
  dname VARCHAR2(132);
BEGIN

UPDATE MGMT_NOTIFY_DEVICES SET SNMP_HOST = v_snmp_host, 
                                 SNMP_PORT = v_snmp_port,
                                 SNMP_COMMUNITY = v_community,
                                 DEVICE_DESCRIPTION = v_description
  WHERE
    DEVICE_NAME = v_device_name AND
    PROFILE_NAME = UPPER(v_profile_name) AND
    TYPE = SNMP_DEVICE_TYPE
  RETURNING DEVICE_NAME INTO dname;

IF dname IS NULL
  THEN
    ROLLBACK;
    
    raise_application_error(-20002, v_profile_name ||
                            ' does not have an SNMP device named ' ||
                            v_device_name || '!');
  END IF;

END EDIT_SNMP_DEVICE;

-- PURPOSE
--   Get the device owner for device - v_device_name of type v_type
-- PARAMETERS
--    V_DEVICE_NAME - the name of the device
--    V_TYPE - the type of the device
FUNCTION GET_DEVICE_OWNER(v_device_name  IN VARCHAR2,
                          v_type         IN INTEGER)
RETURN VARCHAR2 
IS
  CURSOR dOwnerCursor IS
    SELECT profile_name
      FROM mgmt_notify_devices
     WHERE device_name=v_device_name
       AND type=v_type;
  deviceOwner VARCHAR2(256) := '';
                                                                                
BEGIN
                                                                                
  FOR record IN dOwnerCursor
  LOOP
    deviceOwner := record.profile_name;
    exit;
  END LOOP;
                                                                                
  IF( deviceOwner IS NULL )
  THEN
    deviceOwner := '';
  END IF;
                                                                                
  RETURN deviceOwner;

END GET_DEVICE_OWNER;

-- PURPOSE
--   Procedure to set the start date of a user's notification schedule
-- PARAMETERS
--    V_SCHEDULE_OWNER - the owner of the schedule
--    V_SCHEDULE_NAME - the name of the schedule
--    V_START_DATE - the date on which the schedule starts. Note that the hour
--                   and minutes are ignored since the schedule starts at 12:00
--    V_NUM_WEEKS - the number of weeks after which the schedule repeats
--    V_TIMEZONE_REGION - the timezone region of the schedule owner
--
PROCEDURE SET_SCHEDULE(v_schedule_owner IN VARCHAR2,
                       v_schedule_name  IN VARCHAR2 DEFAULT ' ',
                       v_start_date     IN DATE,
                       v_num_weeks      IN NUMBER DEFAULT 1,
                       v_timezone_region IN VARCHAR2 DEFAULT 
                                         TO_CHAR(SYSTIMESTAMP,'TZR'))
IS
  sowner VARCHAR2(256);
  owner VARCHAR2(256);
  cuser VARCHAR2(256);
  email VARCHAR2(128);
  err_msg VARCHAR2(1024);
  r     BOOLEAN;
BEGIN

sowner := UPPER(v_schedule_owner);
  cuser := MGMT_USER.GET_CURRENT_EM_USER();

-- In general the schedule will be updated, so try updating first. If the
  -- schedule does not exist then create it
  UPDATE MGMT_NOTIFY_SCHEDULES
  SET start_date = TRUNC(v_start_date),
      num_weeks = v_num_weeks,
      updated_by = cuser,
      updated = SYSDATE,
      timezone_region = v_timezone_region
  WHERE schedule_owner = sowner AND
        schedule_name = v_schedule_name
  RETURNING schedule_owner INTO owner;

-- Check to make sure the update succeeded
  IF owner IS NULL
  THEN
    INSERT INTO MGMT_NOTIFY_SCHEDULES 
           (schedule_owner, schedule_name, start_date, num_weeks,
            updated_by, updated, timezone_region)
    VALUES (sowner, v_schedule_name, TRUNC(v_start_date), 
            v_num_weeks, cuser, SYSDATE, v_timezone_region);
  END IF;

END SET_SCHEDULE;

-- PURPOSE
--   Procedure to get the user who last updated a notification schedule
-- PARAMETERS
--    V_SCHEDULE_OWNER - the owner of the schedule
--    V_SCHEDULE_NAME - the name of the schedule
--    V_UPDATED_BY - the user who updated the schedule
--    V_UPDATED - when the update took place
--
PROCEDURE WHO_UPDATED_SCHEDULE(v_schedule_owner IN VARCHAR2,
                               v_schedule_name  IN VARCHAR2 DEFAULT ' ',
                               v_updated_by     OUT VARCHAR2,
                               v_updated        OUT DATE)
IS
  sowner VARCHAR2(256);
BEGIN

sowner := UPPER(v_schedule_owner);
  
  SELECT updated_by, updated INTO v_updated_by, v_updated
  FROM MGMT_NOTIFY_SCHEDULES
  WHERE schedule_owner = sowner AND
        schedule_name = v_schedule_name;

END WHO_UPDATED_SCHEDULE;

-- PURPOSE
--   Procedure to disable a user's notification schedule for a period of time
-- PARAMETERS
--    V_SCHEDULE_OWNER - the owner of the schedule
--    V_SCHEDULE_NAME - the name of the schedule
--    V_DISABLE_START - the date on which the disabling period starts
--    V_DISABLE_END - the date on which the disabling period ends
--
-- NOTES
--   To clear a disabling period pass NULL in for both v_disable_start and
--   v_disable_end
--
PROCEDURE DISABLE_SCHEDULE(v_schedule_owner IN VARCHAR2,
                           v_schedule_name  IN VARCHAR2 DEFAULT ' ',
                           v_disable_start  IN DATE,
                           v_disable_end    IN DATE)
IS
  sowner VARCHAR2(256);
  cuser VARCHAR2(256);
  email VARCHAR2(128);
  err_msg VARCHAR2(1024);
  r     BOOLEAN;

BEGIN

sowner := UPPER(v_schedule_owner);
  cuser := MGMT_USER.GET_CURRENT_EM_USER();

-- Keep track of who did the update
  UPDATE MGMT_NOTIFY_SCHEDULES
  SET disable_start = v_disable_start,
      disable_end = v_disable_end,
      updated_by = cuser,
      updated = SYSDATE
  WHERE schedule_owner = sowner AND
        schedule_name = v_schedule_name;

END DISABLE_SCHEDULE;

-- PURPOSE
--   Procedure to set the notification schedule of a notification device
-- PARAMETERS
--    V_SCHEDULE_OWNER - the owner of the schedule
--    V_SCHEDULE_NAME - the name of the schedule
--    V_DEVICE_NAME - the name of the device
--    V_DEVICE_OWNER - the name of the admin profile
--    V_SCHEDULE - the hours in this schedule when the device is available.
--                 This must be an array of 1344 characters
PROCEDURE SET_DEVICE_SCHEDULE(v_schedule_owner IN VARCHAR2,
                              v_schedule_name  IN VARCHAR2 DEFAULT ' ',
                              v_device_name    IN VARCHAR2,
                              v_device_owner   IN VARCHAR2,
                              v_schedule       IN VARCHAR2)
IS
  sowner VARCHAR2(256);
  downer VARCHAR2(256);
  cuser VARCHAR2(256);
  email VARCHAR2(128);
  err_msg VARCHAR2(1024);
  r     BOOLEAN;
BEGIN

sowner := UPPER(v_schedule_owner);
  downer := UPPER(v_device_owner);
  cuser := MGMT_USER.GET_CURRENT_EM_USER();

-- Delete the current schedule
  DELETE FROM MGMT_NOTIFY_DEV_SCHEDULES
  WHERE schedule_owner = sowner
    AND schedule_name = v_schedule_name
    AND device_owner = downer
    AND device_name = v_device_name;

IF v_schedule IS NOT NULL AND (LENGTH(v_schedule) > 0)
  THEN
    INSERT INTO mgmt_notify_dev_schedules
     (SCHEDULE_OWNER, SCHEDULE_NAME, DEVICE_NAME, DEVICE_OWNER, SCHEDULE)
    VALUES (sowner, v_schedule_name, v_device_name, downer, v_schedule);
  END IF;

-- Keep track of who did the update
  UPDATE MGMT_NOTIFY_SCHEDULES
  SET updated_by = cuser,
      updated = SYSDATE
  WHERE schedule_owner = sowner AND
        schedule_name = v_schedule_name;

END SET_DEVICE_SCHEDULE;

-- PURPOSE
--   Procedure to clear a user's schedule
-- PARAMETERS
--    V_SCHEDULE_OWNER - the owner of the schedule
--    V_SCHEDULE_NAME - the name of the schedule
PROCEDURE CLEAR_SCHEDULE(v_schedule_owner IN VARCHAR2, 
                         v_schedule_name IN VARCHAR2 DEFAULT ' ')
IS
BEGIN
  -- Delete the device schedules
  DELETE FROM MGMT_NOTIFY_DEV_SCHEDULES 
  WHERE schedule_owner = v_schedule_owner
    AND schedule_name = v_schedule_name;

-- Delete the schedule
  DELETE FROM MGMT_NOTIFY_SCHEDULES 
  WHERE schedule_owner = v_schedule_owner
    AND schedule_name = v_schedule_name;

END CLEAR_SCHEDULE;

-- PURPOSE
--   Function to check if a user has a schedule set
-- PARAMETERS
--    V_SCHEDULE_OWNER - the owner of the schedule
--    V_SCHEDULE_NAME - the name of the schedule
FUNCTION USER_SCHEDULE_SET(v_schedule_owner IN VARCHAR2, 
                           v_schedule_name IN VARCHAR2 DEFAULT ' ')
RETURN NUMBER
IS
  cnt NUMBER;
BEGIN
  -- Check if a schedule exists
  SELECT COUNT(schedule_owner) INTO cnt FROM MGMT_NOTIFY_SCHEDULES
  WHERE schedule_owner = v_schedule_owner
    AND schedule_name = v_schedule_name
    AND EXISTS (SELECT schedule_name FROM MGMT_NOTIFY_DEV_SCHEDULES
                WHERE schedule_owner = v_schedule_owner
                  AND schedule_name = v_schedule_name);

IF cnt = 0
  THEN
    RETURN 0;
  END IF;

RETURN 1;

END USER_SCHEDULE_SET;

-- Procedure to add a notification rule
PROCEDURE ADD_NOTIFICATION_RULE(v_rule_name   IN VARCHAR2,
                                v_owner       IN VARCHAR2,
                                v_description IN VARCHAR2,
                                v_public_rule IN NUMBER DEFAULT 0,
                                v_repeat_enabled IN NUMBER DEFAULT 0)
IS
BEGIN

-- Note that the owner column is not in use yet
  INSERT INTO mgmt_notify_rules (rule_name, owner, description, public_rule, repeat)
  VALUES (v_rule_name, v_owner, v_description, v_public_rule, v_repeat_enabled);

END ADD_NOTIFICATION_RULE;

--
-- PURPOSE
--  Procedure to add a configuration to a rule. This allows the specification of
--  the severity properties to which the rule applies. 
--
-- NOTES
--  For composite keys v_is_composite_key must be set to 1, the v_key_value 
--  parameter must contain the composite GUID, the associated key parts 
--  must be specified in v_key_part1..v_key_part5 and the target type must
--  be specified in v_target_guid
-- 
PROCEDURE ADD_CONFIGURATION_TO_RULE
(v_rule_name            IN VARCHAR2,
 v_owner                IN VARCHAR2,
 v_target_type          IN VARCHAR2 DEFAULT '%',
 v_target_name          IN VARCHAR2 DEFAULT '%',
 v_group_guid           IN VARCHAR2 DEFAULT NULL,
 v_metric_name          IN VARCHAR2 DEFAULT '%',
 v_metric_column        IN VARCHAR2 DEFAULT '%',
 v_key_value            IN VARCHAR2 DEFAULT '%',
 v_is_composite_key     IN NUMBER   DEFAULT 0,
 v_target_guid          IN RAW      DEFAULT NULL,
 v_key_part1            IN VARCHAR2 DEFAULT '',
 v_key_part2            IN VARCHAR2 DEFAULT '',
 v_key_part3            IN VARCHAR2 DEFAULT '',
 v_key_part4            IN VARCHAR2 DEFAULT '',
 v_key_part5            IN VARCHAR2 DEFAULT '',
 v_want_clears                  IN NUMBER DEFAULT 0,
 v_want_warnings                IN NUMBER DEFAULT 0,
 v_want_critical_alerts         IN NUMBER DEFAULT 0,
 v_want_target_up               IN NUMBER DEFAULT 0,
 v_want_target_down             IN NUMBER DEFAULT 0,
 v_want_target_unreachable_st   IN NUMBER DEFAULT 0,
 v_want_target_unreachable_end  IN NUMBER DEFAULT 0,
 v_want_target_metric_err_start IN NUMBER DEFAULT 0,
 v_want_target_metric_err_end   IN NUMBER DEFAULT 0,
 v_want_target_blackout_start   IN NUMBER DEFAULT 0,
 v_want_target_blackout_end     IN NUMBER DEFAULT 0,
 v_want_policy_clears           IN NUMBER DEFAULT 0,
 v_want_policy_violations       IN NUMBER DEFAULT 0,
 v_want_warning_job_succeeded   IN NUMBER DEFAULT 0,
 v_want_warning_job_problems    IN NUMBER DEFAULT 0,
 v_want_critical_job_succeeded  IN NUMBER DEFAULT 0,
 v_want_critical_job_problems   IN NUMBER DEFAULT 0,
 v_want_policy_job_succeeded    IN NUMBER DEFAULT 0,
 v_want_policy_job_problems     IN NUMBER DEFAULT 0,
 v_ignore_rca                   IN NUMBER DEFAULT 0
)
IS
BEGIN

-- This is a composite key match with wildcards
  INSERT INTO mgmt_notify_rule_configs
    (RULE_NAME, OWNER, TARGET_TYPE, TARGET_NAME, TARGET_GUID, METRIC_NAME,
     METRIC_COLUMN, KEY_VALUE, KEY_PART_1,  KEY_PART_2,  KEY_PART_3,
     KEY_PART_4, KEY_PART_5, WANT_CLEARS, WANT_WARNINGS, WANT_CRITICAL_ALERTS,
     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, WANT_POLICY_CLEARS, WANT_POLICY_VIOLATIONS,
     WANT_WARNING_JOB_SUCCEEDED, WANT_WARNING_JOB_PROBLEMS,
     WANT_CRITICAL_JOB_SUCCEEDED, WANT_CRITICAL_JOB_PROBLEMS,
     WANT_POLICY_JOB_SUCCEEDED, WANT_POLICY_JOB_PROBLEMS, IGNORE_RCA)
  VALUES
    (v_rule_name, v_owner, v_target_type, v_target_name, v_group_guid,
     v_metric_name, v_metric_column, v_key_value, NVL(v_key_part1, ' '), 
     NVL(v_key_part2, ' '),NVL(v_key_part3, ' '), NVL(v_key_part4, ' '),
     NVL(v_key_part5, ' '), v_want_clears, v_want_warnings,
     v_want_critical_alerts, v_want_target_up, v_want_target_down,
     v_want_target_unreachable_st, v_want_target_unreachable_end,
     v_want_target_metric_err_start, v_want_target_metric_err_end,
     v_want_target_blackout_start, v_want_target_blackout_end,
     v_want_policy_clears, v_want_policy_violations,
     v_want_warning_job_succeeded, v_want_warning_job_problems,
     v_want_critical_job_succeeded, v_want_critical_job_problems,
     v_want_policy_job_succeeded, v_want_policy_job_problems, v_ignore_rca);

-- Check if this is a composite key
  IF v_is_composite_key = 1 
  THEN
    BEGIN
      -- Store the key and its associated key parts
      INSERT INTO MGMT_METRICS_COMPOSITE_KEYS 
        (composite_key, target_guid, key_part1_value, key_part2_value, 
         key_part3_value, key_part4_value, key_part5_value)
      VALUES (v_key_value, v_target_guid, v_key_part1,  v_key_part2,
              v_key_part3,  v_key_part4,  v_key_part5);
    EXCEPTION WHEN DUP_VAL_ON_INDEX
    THEN
      -- The key already exists to ignore the error
      NULL;
    END;
  END IF;

END ADD_CONFIGURATION_TO_RULE;

-- PURPOSE
--  Procedure to add a configuration to a job rule. This allows the
--  specification of the job properties to which the rule applies. This is also
--  used for corrective actions in which case the metric details are also
--  supplied
PROCEDURE ADD_CONFIGURATION_TO_JOB_RULE
(v_rule_name            IN VARCHAR2,
 v_owner                IN VARCHAR2,
 v_target_type          IN VARCHAR2 DEFAULT '%',
 v_target_name          IN VARCHAR2 DEFAULT '%',
 v_group_guid           IN VARCHAR2 DEFAULT NULL,
 v_job_name             IN VARCHAR2 DEFAULT '%',
 v_job_owner            IN VARCHAR2 DEFAULT '%',
 v_job_type             IN VARCHAR2 DEFAULT '%',
 v_want_job_scheduled   IN NUMBER DEFAULT 0,
 v_want_job_running     IN NUMBER DEFAULT 0,
 v_want_job_succeeded   IN NUMBER DEFAULT 0,
 v_want_job_suspended   IN NUMBER DEFAULT 0,
 v_want_job_problems    IN NUMBER DEFAULT 0
)
IS
BEGIN

INSERT INTO mgmt_notify_job_rule_configs 
   (RULE_NAME, OWNER, TARGET_TYPE, TARGET_NAME, TARGET_GUID,
    JOB_NAME, JOB_OWNER, JOB_TYPE,
    WANT_JOB_SCHEDULED, WANT_JOB_RUNNING, WANT_JOB_SUCCEEDED, 
    WANT_JOB_SUSPENDED, WANT_JOB_PROBLEMS) 
  VALUES 
   (v_rule_name, v_owner, v_target_type, v_target_name, v_group_guid,
    UPPER(v_job_name), UPPER(v_job_owner), v_job_type, 
    v_want_job_scheduled, v_want_job_running,
    v_want_job_succeeded, v_want_job_suspended, v_want_job_problems);

END ADD_CONFIGURATION_TO_JOB_RULE;

-- PURPOSE
--  Procedure to set the properties of a rule
PROCEDURE SET_RULE_PROPERTIES
(v_rule_name                    IN VARCHAR2,
 v_owner                        IN VARCHAR2,
 v_description                  IN VARCHAR2,
 v_public_rule                  IN NUMBER DEFAULT 0,
 v_repeat_enabled               IN NUMBER DEFAULT 0)
IS
  rname   VARCHAR2(64);
BEGIN

UPDATE mgmt_notify_rules p 
  SET p.public_rule = v_public_rule,
      p.description = v_description,
      p.repeat = v_repeat_enabled 
  WHERE p.rule_name = v_rule_name 
    AND p.owner = v_owner;

END SET_RULE_PROPERTIES;

-- PURPOSE
--   Procedure to make a rule public
-- PARAMETERS
PROCEDURE MAKE_RULE_PUBLIC(v_rule_name   IN VARCHAR2,
                           v_owner       IN VARCHAR2,
                           v_make_public IN NUMBER)
IS
BEGIN

UPDATE MGMT_NOTIFY_RULES SET public_rule = v_make_public
  WHERE rule_name = v_rule_name AND owner = v_owner;
    
END MAKE_RULE_PUBLIC;

-- PURPOSE
--   Procedure to enable/disable repeat setting for a rule
-- PARAMETERS
--   P_RULE_NAME - name of the notification rule
--   P_OWNER - the owner of the notification rule
--   P_ENABLE_REPEAT - whether to enable or disable the repeat.
PROCEDURE SET_RULE_REPEAT(p_rule_name   IN VARCHAR2,
                          p_owner       IN VARCHAR2,
                          p_enable_repeat IN NUMBER)
IS
  l_rule_name mgmt_notify_rules.rule_name%type := NULL;
BEGIN

UPDATE MGMT_NOTIFY_RULES SET repeat = p_enable_repeat
  WHERE rule_name = p_rule_name AND owner = p_owner
  returning rule_name INTO l_rule_name;

IF(l_rule_name IS NULL)
  THEN 
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 
       ' No Notification Rule exists for the combination of rule_name = ' || p_rule_name ||
       ' and owner = ' || p_owner) ;
  END IF;   
END SET_RULE_REPEAT;

-- Procedure to assign notification devices to a notification rule
PROCEDURE ADD_DEVICE_TO_RULE(v_rule_name    IN VARCHAR2,
                             v_owner        IN VARCHAR2,
                             v_device_name  IN VARCHAR2,
                             v_profile_name IN VARCHAR2)
IS
BEGIN

INSERT INTO mgmt_notify_notifyees (RULE_NAME, OWNER, DEVICE_NAME,
                                     PROFILE_NAME)
  VALUES (v_rule_name, v_owner, v_device_name, UPPER(v_profile_name));

END ADD_DEVICE_TO_RULE;

-- PURPOSE
--   Function to check if a notification rule exists
-- PARAMETERS
--   V_RULE_NAME - name of the notification rule
--   V_OWNER - the owner of the notification rule
FUNCTION RULE_EXISTS(v_rule_name    IN VARCHAR2,
                     v_owner        IN VARCHAR2)
RETURN INTEGER
IS
  rname VARCHAR2(64);
BEGIN

SELECT rule_name INTO rname FROM MGMT_NOTIFY_RULES
  WHERE rule_name = v_rule_name AND owner = v_owner;
  
  IF rname IS NOT NULL
  THEN 
    RETURN 1;
  END IF;
  
  RETURN 0;

EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    RETURN 0;
    
END RULE_EXISTS;

-- PURPOSE
--   Function to find out if the email gateway has been setup
--   by the super user
--
-- NOTES
--   0 is returned if no gateway has been set up, 1 is returned if a
--   gateway has been set up
--
FUNCTION IS_MAIL_GATEWAY_SETUP
RETURN NUMBER
IS
  cnt NUMBER := 0;
BEGIN

SELECT COUNT(mail_host) INTO cnt FROM MGMT_NOTIFY_EMAIL_GATEWAY;

IF cnt > 0
  THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;

END IS_MAIL_GATEWAY_SETUP;

-- PURPOSE
--   Procedure to delete the configurations of a notification rule.
PROCEDURE DELETE_RULE_CONFIGURATIONS(v_rule_name IN VARCHAR2,
                                     v_owner     IN VARCHAR2)
IS
BEGIN

DELETE FROM MGMT_NOTIFY_RULE_CONFIGS
  WHERE RULE_NAME = v_rule_name AND OWNER = UPPER(v_owner);

DELETE FROM MGMT_NOTIFY_JOB_RULE_CONFIGS
  WHERE RULE_NAME = v_rule_name AND OWNER = UPPER(v_owner);

END DELETE_RULE_CONFIGURATIONS;

-- PURPOSE
--   Procedure to delete a specific configuration of a notification rule
-- PARAMETERS
--   V_RULE_NAME - name of the notification rule
--   V_OWNER - the owner of the notification rule
--   V_TARGET_NAME - the name of the target
--   V_TARGET_TYPE - the type of the target
--   V_TARGET_NAME - the name of the metric
--   V_TARGET_NAME - the name of the metric column
--   V_TARGET_NAME - the key value
--
-- NOTES
--   This is used to delete rule configurations when a UDM or transposed
--   metric is deleted. Only configurations that exactly match all input
--   parameters will be deleted
PROCEDURE DELETE_RULE_CONFIGURATION(v_rule_name IN VARCHAR2,
                                    v_owner     IN VARCHAR2,
                                    v_target_name IN VARCHAR2,
                                    v_target_type IN VARCHAR2,
                                    v_metric_name IN VARCHAR2,
                                    v_metric_column IN VARCHAR2,
                                    v_key_value IN VARCHAR2)
IS
BEGIN
                                                                                
  DELETE FROM MGMT_NOTIFY_RULE_CONFIGS
  WHERE RULE_NAME = v_rule_name
    AND OWNER = UPPER(v_owner)
    AND TARGET_NAME = v_target_name
    AND TARGET_TYPE = v_target_type
    AND METRIC_NAME = v_metric_name
    AND METRIC_COLUMN = v_metric_column
    AND KEY_VALUE = v_key_value;
                                                                                
END DELETE_RULE_CONFIGURATION;

-- PURPOSE
--   Procedure to remove notification devices from a notification rule
-- PARAMETERS
--   V_RULE_NAME - name of the notification rule
--   V_DEVICE_NAME - the name of the device
--   V_PROFILE_NAME - the name of the admin profile
PROCEDURE DELETE_DEVICE_FROM_RULE(v_rule_name       IN VARCHAR2,
                                  v_owner           IN VARCHAR2,
                                  v_device_name     IN VARCHAR2,
                                  v_profile_name    IN VARCHAR2)
IS
  rname VARCHAR2(64);
BEGIN

DELETE FROM MGMT_NOTIFY_NOTIFYEES
  WHERE RULE_NAME = v_rule_name AND
        OWNER = v_owner AND
        DEVICE_NAME = v_device_name AND
        PROFILE_NAME = UPPER(v_profile_name)
  RETURNING RULE_NAME INTO rname;

END DELETE_DEVICE_FROM_RULE;

-- PURPOSE
--   Procedure to remove notification devices from a notification rule
--   Note that this procedure does not take in a profile_name. This is needed
--   for deleting snmp device or program command device
-- PARAMETERS
--   V_RULE_NAME - name of the notification rule
--   V_OWNER - the owner of the notification rule
--   V_DEVICE_NAME - the name of the device
PROCEDURE DELETE_DEVICE_FROM_RULE(v_rule_name       IN VARCHAR2,
                                  v_owner           IN VARCHAR2,
                                  v_device_name     IN VARCHAR2)
IS
  rname VARCHAR2(64);
BEGIN

DELETE FROM MGMT_NOTIFY_NOTIFYEES
  WHERE RULE_NAME = v_rule_name AND
        OWNER = v_owner AND
        DEVICE_NAME = v_device_name
  RETURNING RULE_NAME INTO rname;

END DELETE_DEVICE_FROM_RULE;

-- PURPOSE
--   Procedure to delete an administrator profile
PROCEDURE DELETE_ADMIN_PROFILE(v_profile_name IN VARCHAR2)
IS
  log_msg VARCHAR2(512);
  pname   VARCHAR2(64);
BEGIN

DELETE FROM MGMT_NOTIFY_PROFILES WHERE PROFILE_NAME = UPPER(v_profile_name)
  RETURNING PROFILE_NAME INTO pname;

IF pname IS NULL
  THEN
    raise_application_error(-20001, 'There is no administrator profile named '
                            || v_profile_name || '!');
  END IF;

END DELETE_ADMIN_PROFILE;

-- PURPOSE
--   Procedure to delete a notification device associated with an administrator
--   profile
PROCEDURE DELETE_DEVICE(v_profile_name    IN VARCHAR2,
                        v_device_name     IN VARCHAR2)
IS
  dname VARCHAR2(132);
  dev_type NUMBER;
BEGIN

DELETE FROM MGMT_NOTIFY_DEVICES
  WHERE PROFILE_NAME = UPPER(v_profile_name) AND
        DEVICE_NAME = v_device_name
  RETURNING DEVICE_NAME INTO dname;

DELETE FROM MGMT_NOTIFY_NOTIFYEES
  WHERE DEVICE_NAME = v_device_name
    AND PROFILE_NAME = v_profile_name;

END DELETE_DEVICE;

-- PURPOSE
--   Procedure to delete a notification program device associated with an 
--   administrator profile
--   NOTE: This is a UI specific API and could change depending on the
--         UI design.
-- PARAMETERS
--   V_PROFILE_NAME - name of the notification rule
--   V_DEVICE_NAME - name of the device
PROCEDURE DELETE_PROGRAM_DEVICE(v_profile_name    IN VARCHAR2,
                                v_device_name     IN VARCHAR2)
IS
  pname VARCHAR2(64);
BEGIN

DELETE FROM MGMT_NOTIFY_DEVICES
  WHERE PROFILE_NAME = UPPER(v_profile_name) AND
        DEVICE_NAME = v_device_name AND
        (TYPE = PROGRAM_DEVICE_TYPE OR TYPE = PLSQL_DEVICE_TYPE )
  RETURNING PROFILE_NAME INTO pname;

DELETE FROM MGMT_NOTIFY_NOTIFYEES
  WHERE DEVICE_NAME = v_device_name
    AND PROFILE_NAME = v_profile_name;

END DELETE_PROGRAM_DEVICE;

-- PURPOSE
--   Procedure to delete a notification rule
PROCEDURE DELETE_NOTIFICATION_RULE(v_rule_name  IN VARCHAR2,
                                   v_owner      IN VARCHAR2,
                                   v_super_user IN VARCHAR2)
IS
  rname VARCHAR2(64);
  dName VARCHAR2(132) := '';
BEGIN
  DELETE FROM MGMT_NOTIFY_RULES
  WHERE rule_name = v_rule_name AND owner = UPPER(v_owner)
  RETURNING RULE_NAME INTO rname;

IF rname IS NULL
  THEN
    raise_application_error(-20000, v_owner || 
                            ' has no notification rule name d ' ||
                            v_rule_name || '!');
  END IF;
END DELETE_NOTIFICATION_RULE;

-- PURPOSE
--   Get the details of a violation for sending in a notification
PROCEDURE GET_VIOLATION_DETAILS(violation_guid_in IN RAW,
                                language_in IN VARCHAR2,
                                country_in IN VARCHAR2,
                                details_out OUT MGMT_MAX_STRING_ARRAY,
                                collection_timestamp_out OUT DATE,
                                logged_timestamp_out OUT DATE,
                                keys_out OUT MGMT_NOTIFY_COLUMNS,
                                ctxt_list_out OUT MGMT_NOTIFY_COLUMNS,
                                is_plsql_notification IN BOOLEAN DEFAULT FALSE)
IS
  hname VARCHAR2(256);
  kval VARCHAR2(256);
  coll_time VARCHAR2(64);
  tz_region VARCHAR2(64);
  tguid RAW(16);
  mguid RAW(16);
  pguid RAW(16);
  tgt_lt DATE;
  ctime DATE;
  ltime DATE;
  l_violation_type NUMBER;
  l_value NUMBER;
  l_metric_label VARCHAR2(64);
  l_column_label VARCHAR2(64);
  l_label VARCHAR2(64);
  l_nlsid VARCHAR2(64);
  l_policy_nlsid VARCHAR2(64);
BEGIN

details_out := MGMT_MAX_STRING_ARRAY();
  details_out.extend(17);

-- note: use target_name as display_name because we don't expose 
  --       target display_name
  BEGIN
    SELECT tgt.target_name, tgt.target_type, tgt.target_name, 
           tgt.type_display_name, tgt.timezone_region, 
           tgt.host_name, tgt.target_guid, tgt.last_load_time,
           s.violation_level, s.message, s.message_nlsid, s.message_params, 
           s.key_value, s.collection_timestamp,
           s.load_timestamp, s.violation_type
    INTO details_out(1), details_out(2), details_out(3), details_out(4),
         details_out(5), hname, tguid, tgt_lt, details_out(12), 
         details_out(13), details_out(14), details_out(15), 
         kval, ctime, ltime, l_violation_type
    FROM MGMT_TARGETS tgt, MGMT_VIOLATIONS s
    WHERE s.violation_guid = violation_guid_in
      AND tgt.target_guid = s.target_guid;
  EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    details_out := NULL;

IF emdw_log.p_is_debug_set
    THEN
      emdw_log.debug('GVD: invalid target: violation_guid = '
                     || violation_guid_in,
                     DELIVER_MODULE_NAME) ;
    END IF;

RETURN;
  END;

-- note: use target_name as display_name because we don't expose 
  --       target display_name
  details_out(6) := hname;

-- BEGIN
  --   SELECT display_name INTO details_out(6) FROM MGMT_TARGETS
  --   WHERE target_name = hname 
  --     AND target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE
  --     AND rownum < 2;
  -- EXCEPTION
  -- WHEN NO_DATA_FOUND
  -- THEN
  --   details_out(6) := hname;
  -- END;

IF l_violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY
  THEN
    BEGIN
      SELECT p.policy_name, '', p.policy_label_nlsid, '',
             p.description, p.policy_guid, p.metric_guid
      INTO details_out(7), details_out(8), l_policy_nlsid,
           details_out(10), details_out(11), pguid, mguid
      FROM MGMT_POLICIES p, MGMT_VIOLATIONS v
      WHERE v.violation_guid = violation_guid_in
      AND p.policy_guid = v.policy_guid;

details_out(9) := MGMT_MESSAGE.GET_MESSAGE(l_policy_nlsid, 'POLICY', 
                                                 language_in, country_in, 
                                                 details_out(7));
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      details_out := NULL;
      IF emdw_log.p_is_debug_set
      THEN
        emdw_log.debug('GVD: invalid policy: violation_guid = '
                       || violation_guid_in,
                       DELIVER_MODULE_NAME) ;
      END IF;

RETURN;
    END;
  ELSE
    BEGIN
      -- Get the metric details_out
      SELECT m.metric_name, m.metric_column, 
             m.metric_label_nlsid, m.column_label_nlsid, 
             m.description, m.metric_label, m.column_label,
             v.policy_guid, m.metric_guid
      INTO details_out(7), details_out(8), details_out(9), 
           details_out(10), details_out(11), l_metric_label, l_column_label,
           pguid, mguid
      FROM MGMT_TARGETS t, MGMT_METRICS m, MGMT_VIOLATIONS v
      WHERE v.violation_guid = violation_guid_in
      AND t.target_guid = tguid
      -- Apparently v.policy_guid should be used to lookup mgmt_policies
      -- from where the metric_guid can be taken. This means joining on
      -- mgmt_policies rather than going straight to mgmt_metrics
      AND m.metric_guid = v.policy_guid
      AND t.target_type = m.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 = ' ');

IF is_plsql_notification
      THEN
        details_out(9) := l_metric_label;
        details_out(10) := l_column_label;
      END IF;

EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      details_out := NULL;
      IF emdw_log.p_is_debug_set
      THEN
        emdw_log.debug('GVD: invalid metric: violation_guid = '
                       || violation_guid_in,
                       DELIVER_MODULE_NAME) ;
      END IF;
      RETURN;
    END;
  END IF;

-- Get the context
  ctxt_list_out := MGMT_NOTIFY_COLUMNS();
  FOR ctx IN (SELECT column_name, column_type,
                     decode(column_type, 0, to_char(column_value),
                                            1, column_str_value) col_value
              FROM mgmt_violation_context 
              WHERE target_guid = tguid
                AND policy_guid = pguid
                AND key_value = kval
                AND collection_timestamp = ctime)
  LOOP
    SELECT m1.column_label, m1.column_label_nlsid
    INTO l_label, l_nlsid
    FROM MGMT_TARGETS t, MGMT_METRICS m, MGMT_METRICS m1
    WHERE t.target_guid = tguid
      AND m.metric_guid = mguid
      AND t.target_type = m.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 t.target_type = m1.target_type
      AND m1.type_meta_ver = t.type_meta_ver
      AND (m1.category_prop_1 = t.category_prop_1 or m1.category_prop_1 = ' ')
      AND (m1.category_prop_2 = t.category_prop_2 or m1.category_prop_2 = ' ')
      AND (m1.category_prop_3 = t.category_prop_3 or m1.category_prop_3 = ' ')
      AND (m1.category_prop_4 = t.category_prop_4 or m1.category_prop_4 = ' ')
      AND (m1.category_prop_5 = t.category_prop_5 or m1.category_prop_5 = ' ')
      AND m1.metric_name = m.metric_name
      AND m1.metric_column = ctx.column_name;

ctxt_list_out.extend; 
    ctxt_list_out(ctxt_list_out.COUNT) := 
      MGMT_NOTIFY_COLUMN(l_label, l_nlsid, ctx.col_value);
  END LOOP;

-- Get the metric value
  SELECT value, string_value INTO l_value, details_out(16)
  FROM MGMT_VIOLATIONS WHERE violation_guid = violation_guid_in;
  IF details_out(16) IS NULL
  THEN
    details_out(16) := l_value;
  END IF;

keys_out := GET_KEYS(mguid, tguid, kval);

-- Convert to centiseconds to represent the uptime of the target
  details_out(17) := TRUNC((SYSDATE - tgt_lt) * (24*60*60*100));

-- pass out the collection timestamp as a date
  collection_timestamp_out := ctime;
  logged_timestamp_out := ltime;

END GET_VIOLATION_DETAILS;

FUNCTION GET_KEY_VALUE_NAME(keys_in IN MGMT_NOTIFY_COLUMNS)
RETURN VARCHAR2
IS
  key_value_name VARCHAR2(512);
BEGIN
  IF keys_in IS NULL OR keys_in.COUNT = 0
  THEN
    RETURN NULL;
  END IF;

FOR i in keys_in.FIRST..keys_in.LAST
  LOOP
    IF i > 1 
    THEN 
      key_value_name := key_value_name || ';'; 
    END IF;
     key_value_name := key_value_name || keys_in(i).column_label;
  END LOOP;

RETURN key_value_name;

END GET_KEY_VALUE_NAME;

FUNCTION GET_KEY_VALUE(keys_in IN MGMT_NOTIFY_COLUMNS)
RETURN VARCHAR2
IS
  key_value VARCHAR2(1290);
BEGIN
  IF keys_in IS NULL OR keys_in.COUNT = 0
  THEN
    RETURN NULL;
  END IF;

FOR i in keys_in.FIRST..keys_in.LAST 
  LOOP
    IF i > 1 
    THEN 
      key_value := key_value || ';';
    END IF;
     key_value := key_value || keys_in(i).value;
  END LOOP;

RETURN key_value;

END GET_KEY_VALUE;

-- PURPOSE
--  To send a metric severity notification to a PL/SQL procedure
PROCEDURE METRIC_SEVERITY_FOR_PLSQL(violation_guid_in IN RAW,
                                    language_in IN VARCHAR2,
                                    country_in IN VARCHAR2,
                                    device_owner_in IN VARCHAR2,
                                    device_name_in IN VARCHAR2,
                                    rule_owner_in IN VARCHAR2,
                                    rule_name_in IN VARCHAR2,
                                    procedure_name_out OUT VARCHAR2,
                                    error_code_out OUT NUMBER,
                                    error_out OUT VARCHAR2)
IS
  pname VARCHAR2(512);
  sev MGMT_NOTIFY_SEVERITY;
  details MGMT_MAX_STRING_ARRAY;
  cs DATE;
  ls DATE;
  mguid RAW(16);
  tguid RAW(16);
  kguid VARCHAR2(256);
  keys MGMT_NOTIFY_COLUMNS;
  ctxt_list MGMT_NOTIFY_COLUMNS;
BEGIN

error_code_out := 0;
  procedure_name_out := pname;

-- Get the details of the severity
  GET_VIOLATION_DETAILS(violation_guid_in, language_in, country_in, details, 
                        cs, ls, keys, ctxt_list, TRUE);

IF details IS NULL
  THEN
    error_code_out := NO_SEVERITY_ERR;
    RETURN;
  END IF;

SELECT target_guid, policy_guid, key_value 
  INTO tguid, mguid, kguid FROM MGMT_VIOLATIONS
  WHERE violation_guid = violation_guid_in;
 
  sev := MGMT_NOTIFY_SEVERITY(rule_name_in, rule_owner_in,
                              details(3), details(4), details(5), details(6),
                              details(9), details(11), details(10), details(14),
                              GET_KEY_VALUE_NAME(keys), GET_KEY_VALUE(keys), 
                              kguid, cs, details(12),
                              details(13), violation_guid_in, tguid, mguid, 
                              ctxt_list);

-- Set the module and action
  DBMS_APPLICATION_INFO.SET_MODULE(pname, violation_guid_in);

EXECUTE IMMEDIATE 'CALL ' || EM_CHECK.QUALIFIED_SQL_NAME(pname)
                    || '(:s)' USING sev;

LOG_DELIVERY_TIME(cs, ls, details(5), PLSQL_DEVICE_TYPE);

-- Clear the module and action
  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);

EXCEPTION 
WHEN OTHERS
THEN
 -- Clear the module and action
 DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);

error_code_out := SQLCODE;
 error_out := SQLERRM;
 procedure_name_out := pname;

END METRIC_SEVERITY_FOR_PLSQL;

-- PURPOSE
--   Get the details of a job state change for sending in a notification
PROCEDURE GET_JOB_STATE_CHANGE_DETAILS(state_change_guid_in IN RAW,
                                       language_in IN VARCHAR2,
                                       country_in IN VARCHAR2,
                                       details_out OUT MGMT_NOTIFY_JOB_SC)
IS
   target_names MGMT_MEDIUM_STRING_ARRAY;
   target_types MGMT_MEDIUM_STRING_ARRAY;
   timezones MGMT_SHORT_STRING_ARRAY;
   stat NUMBER(2);
   tguid RAW(16);
   mguid RAW(16);
   pguid RAW(16);
   coll_ts DATE;
   metric_name VARCHAR2(64);
   metric_column VARCHAR2(64);
   label VARCHAR2(64);
   label_nlsid VARCHAR2(64);
   policy VARCHAR2(64);
   mtype NUMBER;
   str_val VARCHAR2(1024);
   val NUMBER;
   vl NUMBER;
   kval VARCHAR2(256);
   keys MGMT_NOTIFY_COLUMNS;
   ctxt_list MGMT_NOTIFY_COLUMNS;
   l_label VARCHAR2(64);
   l_nlsid VARCHAR2(64);
   l_policy_name VARCHAR2(128);
   l_policy_nlsid VARCHAR2(64);
   l_job_type MGMT_JOB_TYPE_INFO.job_type_default%TYPE;
   l_job_type_nlsid  MGMT_JOB_TYPE_INFO.job_type_nlsid%TYPE;
   l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;
   l_tz_rgn MGMT_TARGETS.timezone_region%TYPE;
 
BEGIN

-- There will only be one record
  FOR sc IN (SELECT sc.*, j.job_name, j.job_owner
             FROM MGMT_JOB_STATE_CHANGES sc, MGMT_JOB j
             WHERE sc.state_change_guid = state_change_guid_in
               AND j.job_id = sc.job_id
               AND j.job_status != MGMT_JOBS.JOB_STATUS_DELETE_PENDING)
  LOOP

-- Get the target details
    SELECT DISTINCT tgt.target_name, tgt.target_type, tgt.timezone_region
    BULK COLLECT INTO target_names, target_types, timezones
    FROM MGMT_JOB_EXT_TARGETS jobtgt, MGMT_TARGETS tgt
    WHERE jobtgt.job_id = sc.job_id
      AND jobtgt.execution_id = sc.execution_id
      AND tgt.target_guid = jobtgt.target_guid;

IF target_names IS NULL OR target_names.COUNT = 0
    THEN
      details_out := NULL;
      IF emdw_log.p_is_debug_set
      THEN
        emdw_log.debug('GJD: targetless : job_id = '
                       || sc.job_id,
                       DELIVER_MODULE_NAME) ;
      END IF;
      -- Targetless Job, use emdrep timezone
      BEGIN
           select TIMEZONE_REGION into l_tz_rgn from MGMT_TARGETS where target_type='oracle_emrep';
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
          l_tz_rgn := TO_CHAR(SYSTIMESTAMP,'TZR');
      END;
    ELSE

-- Job with target, use target's timezone
      l_tz_rgn := timezones(1);
      
    END IF;

-- Get the job type
    BEGIN
      l_job_type_id := MGMT_JOB_ENGINE.get_job_type_id(sc.job_id);
      SELECT NVL(job_type_default, job_type), job_type_nlsid
        INTO l_job_type, l_job_type_nlsid
      FROM MGMT_JOB_TYPE_INFO 
      WHERE job_type_id = l_job_type_id;
    EXCEPTION 
    WHEN NO_DATA_FOUND
    THEN
      IF emdw_log.p_is_debug_set
      THEN
        emdw_log.debug('GJD: GET_JOB_STATE_CHANGE_DETAILS: Job type not ' ||
                       'found for this job_id = ' || sc.job_id ||
                       ', job_type_id = ' || l_job_type_id,
                       DELIVER_MODULE_NAME);
      END IF;
    END;

-- Check if this is for a job
    IF sc.type = JOB_STATE_CHANGE
    THEN
      details_out := MGMT_NOTIFY_JOB_SC(sc.job_name, l_job_type, 
                                        l_job_type_nlsid, sc.job_owner, 
                                        target_names, target_types, 
                                        l_tz_rgn, sc.logged, 
                                        sc.occurred, sc.newstate, 
                                        sc.job_id, sc.execution_id,
                                        sc.step_id, NULL, NULL, NULL, NULL, 
                                        NULL,0, NULL, NULL);

-- Check if it is for a CA run as a result of a metric severity
    ELSIF sc.type = METRIC_CA_STATE_CHANGE
    THEN
      -- Get the severity details
      SELECT target_guid, policy_guid, key_value, collection_timestamp, 
             value, string_value, violation_level
      INTO tguid, pguid, kval, coll_ts, val, str_val, vl
      FROM MGMT_VIOLATIONS
      WHERE violation_guid = sc.violation_guid;

mguid := pguid;

-- Get the details of the metric
      SELECT m.metric_name, m.metric_column, m.column_label,
             m.column_label_nlsid, m.metric_type 
      INTO metric_name, metric_column, label, label_nlsid, mtype
      FROM MGMT_TARGETS t, MGMT_METRICS m
      WHERE t.target_guid = tguid
        AND m.metric_guid = mguid
        AND t.target_type = m.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 = ' ');

IF mtype = 0
      THEN
        str_val := val;
      END IF;

-- Get the context
      ctxt_list := MGMT_NOTIFY_COLUMNS();
      FOR ctx IN (SELECT column_name, column_type,
                         decode(column_type, 0, to_char(column_value),
                                                1, column_str_value) col_value
                  FROM mgmt_violation_context
                  WHERE target_guid = tguid
                    AND policy_guid = pguid
                    AND key_value = kval
                    AND collection_timestamp = coll_ts)
      LOOP
        SELECT m1.column_label, m1.column_label_nlsid
        INTO l_label, l_nlsid
        FROM MGMT_TARGETS t, MGMT_METRICS m, MGMT_METRICS m1
        WHERE t.target_guid = tguid
          AND m.metric_guid = mguid
          AND t.target_type = m.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 t.target_type = m1.target_type
          AND m1.type_meta_ver = t.type_meta_ver
          AND (m1.category_prop_1 = t.category_prop_1 or 
               m1.category_prop_1 = ' ')
          AND (m1.category_prop_2 = t.category_prop_2 or 
               m1.category_prop_2 = ' ')
          AND (m1.category_prop_3 = t.category_prop_3 or 
               m1.category_prop_3 = ' ')
          AND (m1.category_prop_4 = t.category_prop_4 or 
               m1.category_prop_4 = ' ')
          AND (m1.category_prop_5 = t.category_prop_5 or 
               m1.category_prop_5 = ' ')
          AND m1.metric_name = m.metric_name
          AND m1.metric_column = ctx.column_name;
    
        ctxt_list.extend;
        ctxt_list(ctxt_list.COUNT) :=
          MGMT_NOTIFY_COLUMN(l_label, l_nlsid, ctx.col_value);
      END LOOP;

details_out := MGMT_NOTIFY_JOB_SC(sc.job_name, l_job_type,  
                                        l_job_type_nlsid, sc.job_owner, 
                                        target_names, target_types, 
                                        timezones(1), sc.logged, 
                                        sc.occurred, sc.newstate, 
                                        sc.job_id, sc.execution_id, sc.step_id,
                                        label, label_nlsid,
                                        metric_name, metric_column, 
                                        str_val, vl,
                                        GET_KEYS(mguid, tguid, kval),
                                        ctxt_list);
 
    -- Check if it is for a CA run as a result of a policy violation
    ELSIF sc.type = POLICY_CA_STATE_CHANGE
    THEN
      -- Get the violation details
      SELECT target_guid, policy_guid, key_value, collection_timestamp,
             value, string_value, violation_level
      INTO tguid, pguid, kval, coll_ts, val, str_val, vl
      FROM MGMT_VIOLATIONS
      WHERE violation_guid = sc.violation_guid;
                                                                                
      -- Get the details of the policy
      SELECT policy_name, policy_label_nlsid, metric_guid
        INTO l_policy_name, l_policy_nlsid, mguid
      FROM MGMT_POLICIES
      WHERE policy_guid = pguid;

policy := MGMT_MESSAGE.GET_MESSAGE(l_policy_nlsid, 'POLICY',
                                         language_in, country_in,
                                         l_policy_name);

IF str_val IS NULL
      THEN
        str_val := val;
      END IF;
                                                                                
      -- Get the context
      ctxt_list := MGMT_NOTIFY_COLUMNS();
      FOR ctx IN (SELECT column_name, column_type,
                         decode(column_type, 0, to_char(column_value),
                                                1, column_str_value) col_value
                  FROM mgmt_violation_context
                  WHERE target_guid = tguid
                    AND policy_guid = pguid
                    AND key_value = kval
                    AND collection_timestamp = coll_ts)
      LOOP 
        SELECT m1.column_label, m1.column_label_nlsid
        INTO l_label, l_nlsid
        FROM MGMT_TARGETS t, MGMT_METRICS m, MGMT_METRICS m1
        WHERE t.target_guid = tguid
          AND m.metric_guid = mguid
          AND t.target_type = m.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 t.target_type = m1.target_type
          AND m1.type_meta_ver = t.type_meta_ver
          AND (m1.category_prop_1 = t.category_prop_1 or 
               m1.category_prop_1 = ' ')
          AND (m1.category_prop_2 = t.category_prop_2 or 
               m1.category_prop_2 = ' ')
          AND (m1.category_prop_3 = t.category_prop_3 or 
               m1.category_prop_3 = ' ')
          AND (m1.category_prop_4 = t.category_prop_4 or 
               m1.category_prop_4 = ' ')
          AND (m1.category_prop_5 = t.category_prop_5 or 
               m1.category_prop_5 = ' ')
          AND m1.metric_name = m.metric_name
          AND m1.metric_column = ctx.column_name;
   
        ctxt_list.extend;
        ctxt_list(ctxt_list.COUNT) :=
          MGMT_NOTIFY_COLUMN(l_label, l_nlsid, ctx.col_value);
      END LOOP;

END IF;
  END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
   IF emdw_log.p_is_debug_set 
   THEN
     emdw_log.debug('GJD: unexpected error ' || SQLERRM,
                    DELIVER_MODULE_NAME) ;
  END IF;

details_out := NULL;

END GET_JOB_STATE_CHANGE_DETAILS;

-- PURPOSE
--  To send a job/ca state change notification to a PL/SQL procedure
PROCEDURE STATE_CHANGE_FOR_PLSQL(state_change_guid_in IN RAW,
                                 language_in IN VARCHAR2,
                                 country_in IN VARCHAR2,
                                 device_owner_in IN VARCHAR2,
                                 device_name_in IN VARCHAR2,
                                 rule_owner_in IN VARCHAR2,
                                 rule_name_in IN VARCHAR2,
                                 procedure_name_out OUT VARCHAR2,
                                 error_code_out OUT NUMBER,
                                 error_out OUT VARCHAR2)
IS
  pname VARCHAR2(512);
  change MGMT_NOTIFY_JOB;
  ca_change MGMT_NOTIFY_CORRECTIVE_ACTION;
  details MGMT_NOTIFY_JOB_SC;
  targets SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
  is_ca NUMBER;
  kguid RAW(16);
BEGIN

BEGIN
    -- Get the name of the procedure
    SELECT program INTO pname FROM MGMT_NOTIFY_DEVICES
    WHERE profile_name = device_owner_in AND device_name = device_name_in;
  EXCEPTION WHEN NO_DATA_FOUND
  THEN
   error_code_out := NO_DEVICE_ERR;
   procedure_name_out := NULL;
   RETURN;
  END;
                                                                                
  error_code_out := 0;
  procedure_name_out := pname;
                                                                                
  -- Get the details of the state change
  GET_JOB_STATE_CHANGE_DETAILS(state_change_guid_in, language_in, country_in, 
                               details);
                                                                                
  IF details IS NULL
  THEN
    error_code_out := NO_STATE_CHANGE_ERR;
    RETURN;
  END IF;
                                                                                
  IF details.target_names IS NOT NULL AND details.target_names.COUNT > 0
  THEN
    targets.extend(details.target_names.COUNT);
    FOR i IN details.target_names.FIRST..details.target_names.LAST
    LOOP
      targets(i) := SMP_EMD_NVPAIR(details.target_names(i),
                                   details.target_types(i));
    END LOOP;
  END IF;

SELECT is_corrective_action INTO is_ca FROM MGMT_JOB
  WHERE job_id = details.job_id
  AND job_status != MGMT_JOBS.JOB_STATUS_DELETE_PENDING;

IF is_ca = 0
  THEN
    change := MGMT_NOTIFY_JOB(rule_name_in, rule_owner_in, details.job_name,
                              details.job_owner, details.job_type,
                              details.status, details.occurred,
                              state_change_guid_in, 
                              details.job_id, details.execution_id, targets);

-- Set the module and action
    DBMS_APPLICATION_INFO.SET_MODULE(pname, state_change_guid_in);
                                                                                
    EXECUTE IMMEDIATE 'CALL ' || EM_CHECK.QUALIFIED_SQL_NAME(pname) 
            || '(:s)' USING change;
  ELSE
    BEGIN
     SELECT v.key_value INTO kguid 
     FROM MGMT_JOB_STATE_CHANGES sc, MGMT_VIOLATIONS v
     WHERE  sc.state_change_guid = state_change_guid_in
       AND  v.violation_guid = sc.violation_guid;
    EXCEPTION WHEN OTHERS THEN NULL; END;

ca_change := MGMT_NOTIFY_CORRECTIVE_ACTION
                      (rule_name_in, rule_owner_in, details.job_name,
                       details.job_owner, details.job_type,
                       details.status, details.occurred, state_change_guid_in, 
                       details.job_id, details.execution_id, targets,
                       details.metric_name, details.metric_column,
                       details.metric_value, details.violation_level,
                       GET_KEY_VALUE_NAME(details.keys), 
                       GET_KEY_VALUE(details.keys), 
                       kguid, details.ctxt_list);

-- Set the module and action
    DBMS_APPLICATION_INFO.SET_MODULE(pname, state_change_guid_in);
                                                                                
    EXECUTE IMMEDIATE 'CALL ' || EM_CHECK.QUALIFIED_SQL_NAME(pname)
            || '(:s)' USING ca_change;
  END IF;
                                                                                
  -- Clear the module and action
  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
                                                                                
EXCEPTION
WHEN OTHERS
THEN
 dbms_output.put_line(sqlerrm);
 error_code_out := SQLCODE;
 error_out := SQLERRM;
 procedure_name_out := pname;

END STATE_CHANGE_FOR_PLSQL;

-- PURPOSE
--  To set a notification up to be retried
PROCEDURE SETUP_FOR_RETRY(notification_in IN MGMT_NOTIFY_NOTIFICATION,
                          error_in IN VARCHAR2)
IS
BEGIN

-- If this was already a retry then update the stats
  IF notification_in.notification_type = RETRY_NOTIFICATION
  THEN
    UPDATE MGMT_NOTIFY_REQUEUE SET last_timestamp = SYSDATE,
                                   num_requeues = num_requeues + 1
    WHERE device_name = notification_in.device_name
      AND device_owner = notification_in.device_owner
      AND source_guid = notification_in.source_guid
      AND rule_name = notification_in.rule_name
      AND rule_owner = notification_in.rule_owner;
  ELSE
    INSERT INTO MGMT_NOTIFY_REQUEUE 
      (source_guid, source_type, notification_type, device_name, 
       device_owner, device_type, rule_name, rule_owner)
    VALUES (notification_in.source_guid, notification_in.source_type, 
            RETRY_NOTIFICATION, notification_in.device_name, 
            notification_in.device_owner, notification_in.device_type,
            notification_in.rule_name, notification_in.rule_owner);
  END IF;

-- Set the device to be down
  UPDATE MGMT_NOTIFY_DEVICES SET STATUS = DEVICE_DOWN, 
                                 STATUS_MESSAGE = substr(error_in,1,512),
                                 CONTACT_TIMESTAMP = SYSDATE
  WHERE device_name = notification_in.device_name 
    AND profile_name = notification_in.device_owner;
END;

-- PURPOSE
--  Called when a device has been successfully pinged
PROCEDURE PING_SUCCEEDED(notification_in IN MGMT_NOTIFY_NOTIFICATION)
IS
BEGIN
  -- Delete the requeue record
  DELETE FROM MGMT_NOTIFY_REQUEUE
  WHERE device_name = notification_in.device_name
    AND device_owner = notification_in.device_owner
    AND source_guid = notification_in.source_guid
    AND rule_name = notification_in.rule_name
    AND rule_owner = notification_in.rule_owner;

-- Set the device to be up
  UPDATE MGMT_NOTIFY_DEVICES SET STATUS = DEVICE_UP,  
                                 STATUS_MESSAGE = '',
                                 CONTACT_TIMESTAMP = SYSDATE
  WHERE device_name = notification_in.device_name  
    AND profile_name = notification_in.device_owner;

-- Requeue any pending notifications
  REQUEUE(notification_in.device_name,  notification_in.device_owner);

END PING_SUCCEEDED;

-- PURPOSE
--  Log the delivery time for a severity;
PROCEDURE LOG_DELIVERY_TIME(collection_time_in IN DATE,
                            load_time_in IN DATE,
                            tz_region_in IN VARCHAR2,
                            device_type_in IN NUMBER,
                            oms_name_in IN VARCHAR2 DEFAULT NULL)
IS
  now DATE;
  delivery_time NUMBER := 0;
  dt VARCHAR2(32);
  tdt VARCHAR2(32);
  errmsg VARCHAR2(256);
  err NUMBER;
BEGIN
  dt := PERF_DELIVERY_TIME;
  tdt := PERF_TOTAL_DELIVERY_TIME;

BEGIN
    IF device_type_in IN (EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE,
                        SHORT_EMAIL_DEVICE_TYPE)
    THEN
      dt := PERF_EMAIL_DELIVERY_TIME;
      tdt := PERF_EMAIL_TOTAL_DELIVERY_TIME;
    ELSIF device_type_in = PROGRAM_DEVICE_TYPE
    THEN
      dt := PERF_OSCMD_DELIVERY_TIME;
      tdt := PERF_OSCMD_TOTAL_DELIVERY_TIME;
    ELSIF device_type_in = PLSQL_DEVICE_TYPE
    THEN
      dt := PERF_PLSQL_DELIVERY_TIME;
      tdt := PERF_PLSQL_TOTAL_DELIVERY_TIME;
    ELSIF device_type_in = JAVA_DEVICE_TYPE
    THEN
      dt := PERF_JAVA_DELIVERY_TIME;
      tdt := PERF_JAVA_TOTAL_DELIVERY_TIME;
    ELSIF device_type_in = SNMP_DEVICE_TYPE
    THEN
      dt := PERF_SNMP_DELIVERY_TIME;
      tdt := PERF_SNMP_TOTAL_DELIVERY_TIME;
      mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, 
                         0,'device not found '||dt||' '||tdt,'','','','',
                         MGMT_GLOBAL.G_WARN);
    END IF;

delivery_time := (SYSDATE-load_time_in) * (24*60*60*1000);
    IF delivery_time > 0
    THEN
      mgmt_log.log_performance(EMD_NOTIFICATION.DELIVER_MODULE_NAME, 
                               delivery_time, SYSDATE, 'N', dt, 
                               delivery_time, NULL, NULL,
                               device_type_in, oms_name_in);
    END IF;

-- Log the time it took from the severity being loaded into
    -- the MGMT_VIOLATIONS table until the notification being delivered
    BEGIN
      now := mgmt_global.sysdate_tzrgn(tz_region_in);

-- Log the time it took from the severity being created until the 
      -- notification was delivered. Note that sysdate is converted to the
      -- target timezone
      delivery_time := (now-collection_time_in) * (24*60*60*1000);
      IF delivery_time > 0
      THEN
        mgmt_log.log_performance(EMD_NOTIFICATION.DELIVER_MODULE_NAME, 
                                 delivery_time, SYSDATE, 'N', tdt, 
                                 delivery_time, NULL, NULL,
                                 device_type_in, oms_name_in);
      END IF;
    EXCEPTION WHEN OTHERS
    THEN
      -- There is no point logging the time since we do not have the
      -- current time correctly changed into the target timezone
      NULL;
    END;

EXCEPTION
  WHEN OTHERS THEN
      mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, 
                         err,'ERROR '||errmsg||
                         ' during emd_notification.log_delivery_time','',
                         device_type_in,oms_name_in,'',MGMT_GLOBAL.G_ERROR);
    err := SQLCODE;
    errmsg := SUBSTR(SQLERRM,1,256);
    IF err <> OLDERR OR OLDERRTIME < SYSDATE-(1/24)
    THEN
      mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, 
                         err,'ERROR '||errmsg||
                         ' during emd_notification.log_delivery_time','',
                         device_type_in,oms_name_in,'',MGMT_GLOBAL.G_ERROR);
      OLDERR := err;
      OLDERRTIME := SYSDATE;
    END IF;
  END;

END LOG_DELIVERY_TIME;

-- PROCEDURE
--  To log a record of a delivery
--
PROCEDURE LOG_HISTORY(source_obj_type_in IN NUMBER,
                      source_obj_guid_in IN RAW,
                      delivered_in IN VARCHAR2,
                      message_in IN VARCHAR2,
                      tz_region_in IN VARCHAR2 DEFAULT NULL)
IS
 t DATE;
 tz_region VARCHAR2(64);
 guid RAW(16) := source_obj_guid_in;
 source_type NUMBER := source_obj_type_in;
BEGIN

IF tz_region_in IS NULL
  THEN
    SELECT timezone_region INTO tz_region
    FROM MGMT_TARGETS tgt, MGMT_VIOLATIONS sev
    WHERE sev.violation_guid = source_obj_guid_in
      AND tgt.target_guid = sev.target_guid;

t := mgmt_global.sysdate_tzrgn(tz_region);
  ELSE
    t := mgmt_global.sysdate_tzrgn(tz_region_in);
  END IF;

IF source_obj_type_in IN (METRIC_CA_STATE_CHANGE,
                            POLICY_CA_STATE_CHANGE)
  THEN
    source_type  := METRIC_SEVERITY;
    BEGIN
      SELECT violation_guid INTO guid FROM MGMT_JOB_STATE_CHANGES
      WHERE state_change_guid = source_obj_guid_in;
    EXCEPTION WHEN NO_DATA_FOUND
    THEN
      guid := source_obj_guid_in;
    END;
  END IF;

INSERT INTO MGMT_NOTIFICATION_LOG 
     (source_obj_type, source_obj_guid, timestamp, delivered, message) 
  VALUES (source_type, guid, t, delivered_in, message_in);

EXCEPTION
WHEN OTHERS
THEN

INSERT INTO MGMT_NOTIFICATION_LOG 
     (source_obj_type, source_obj_guid, timestamp, delivered, message) 
  VALUES (source_type, source_obj_guid_in, SYSDATE, delivered_in, 
          message_in);

END LOG_HISTORY;

--
-- PURPOSE
--
--   To remove deleted targets from notification rules
--
--  PARAMETERS
--
--   TARGET_NAME_IN - the target name of the deleted target
--   TARGET_TYPE_IN - the type of the deleted target
--
PROCEDURE HANDLE_TARGET_DELETED(target_name_in IN VARCHAR2,
                                target_type_in IN VARCHAR2,
                                target_guid_in IN RAW)
IS
  tguid RAW(16);
BEGIN

BEGIN
   SELECT target_guid INTO tguid FROM MGMT_TARGETS
   WHERE target_name = target_name_in
     AND target_type = target_type_in;
  EXCEPTION WHEN NO_DATA_FOUND
  THEN
    RETURN;
  END;

DELETE FROM MGMT_NOTIFY_RULE_CONFIGS 
  WHERE (target_name = target_name_in AND target_type = target_type_in) OR
        target_guid = tguid;

DELETE FROM MGMT_NOTIFY_JOB_RULE_CONFIGS 
  WHERE (target_name = target_name_in AND target_type = target_type_in) OR
        target_guid = tguid;

END HANDLE_TARGET_DELETED;

-- PURPOSE
--
--  To cleanup when a key value has been deleted
--
-- PARAMETERS
--
--  P_TARGET_GUID - the target GUID
--  P_METRIC_GUID - the metric GUID
--  P_KEY_VALUE - the key value
--
PROCEDURE HANDLE_METRIC_KEYVAL_DELETION(
 p_target_guid  mgmt_targets.target_guid%TYPE,
 p_metric_guid  mgmt_metrics.metric_guid%TYPE,
 p_key_value    mgmt_metrics_raw.key_value%TYPE)
IS
  l_target_name VARCHAR2(256);
  l_target_type VARCHAR2(64);
  l_met_col VARCHAR2(64);
BEGIN

--trace('called with: ' || p_target_guid || '/' || p_metric_guid || '/' ||
--p_key_value);

SELECT target_name, target_type INTO l_target_name, l_target_type
  FROM MGMT_TARGETS 
  WHERE target_guid = p_target_guid;

-- Using like here to match names in case the Console goes to supporting
  -- multiple target types in rules. It already supports multiple targets
  -- of the same type
  DELETE FROM MGMT_NOTIFY_RULE_CONFIGS
  WHERE l_target_type LIKE target_type
    AND l_target_name LIKE target_name
    AND key_value = p_key_value;

COMMIT;

EXCEPTION WHEN OTHERS
THEN
  NULL;
END HANDLE_METRIC_KEYVAL_DELETION;

-- PURPOSE
--
--  To queue a severity into MGMT_NOTIFY_INPUT_Q 
--
-- PARAMETERS
--
--  p_msg - violation GUID or state change guid + guid type
--  p_guid_type - JOB_STATE_CHANGE or SEVERITY
PROCEDURE QUEUE_NOTIF_INPUT(p_msg_in RAW,
                            p_guid_type NUMBER)
IS
  enq_options         dbms_aq.enqueue_options_t;
  qMsg_properties     dbms_aq.message_properties_t;
  qMsg_handle         RAW(16);
BEGIN

IF(length(p_msg_in) != 32 OR p_guid_type NOT IN (GUID_TYPE_SEVERITY, GUID_TYPE_JOB_STATE_CHANGE))
  THEN    
    IF EMDW_LOG.P_IS_INFO_SET
    THEN
       EMDW_LOG.INFO('Invalid severity / job_state_change GUID or invalid guid type : ' ||
                      p_msg_in ||  ' : ' || p_guid_type,  'queue_notif_input');
    END IF;
  END IF; 
  -- Add the message to the queue
  DBMS_AQ.ENQUEUE(queue_name => G_NOTIFY_INPUT_Q_NAME,
                  enqueue_options => enq_options,
                  message_properties => qMsg_properties,
                  payload => utl_raw.cast_to_raw(p_msg_in || '|' || p_guid_type),
                  msgid => qMsg_handle);
EXCEPTION
WHEN OTHERS
THEN
    mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME,
                       MGMT_GLOBAL.UNEXPECTED_ERR,
                       MGMT_GLOBAL.UNEXPECTED_ERR_M ||
                       'Error while enquing the severity : ' || p_msg_in || SQLERRM, 
                       'ORA',  p_msg_in);

END QUEUE_NOTIF_INPUT;

-- PURPOSE
--
--  To de-queue a severity from MGMT_NOTIFY_INPUT_Q 
--
-- PARAMETERS
--
--  p_notif_src_guid - SEVERITY OR JOB_STATE_CHANGE_GUID
--  p_guid_type - JOB_STATE_CHANGE or SEVERITY
--  p_rule_guid - RULE GUID in case of repeat signal, null otherwise
--  p_repeat_count - Repeat count in case of repeat notification, null otherwise 
PROCEDURE DEQUEUE_NOTIF_INPUT(p_notif_src_guid OUT RAW,
                              p_guid_type OUT NUMBER,
                              p_rule_guid OUT RAW,
                              p_repeat_count OUT NUMBER)
IS
  dq_options      dbms_aq.dequeue_options_t;
  qMsg_properties dbms_aq.message_properties_t;
  msg            RAW(500);
  msg_content    VARCHAR2(500);
  qmsg_id_out    RAW(32);
BEGIN

dq_options.navigation := DBMS_AQ.FIRST_MESSAGE;
  dq_options.wait := DBMS_AQ.NO_WAIT;

-- Dequeue a notification
  DBMS_AQ.DEQUEUE(queue_name => G_NOTIFY_INPUT_Q_NAME,
                  dequeue_options    => dq_options,
                  message_properties => qMsg_properties,
                  payload            => msg,
                  msgid              => qmsg_id_out);

msg_content := utl_raw.cast_to_varchar2(msg);

-- check for lenght
  -- change the code to extract the data
  p_notif_src_guid := hextoraw(substr(msg_content,1,32));
  p_guid_type := substr(msg_content,34,1);
  IF(length (msg_content) > 34)
  THEN 
    p_rule_guid := hextoraw(substr(msg_content,36,32));
    p_repeat_count := to_number(substr(msg_content,69));
  ELSE
    p_rule_guid := NULL;
    p_repeat_count := NULL;
  END IF;  
END DEQUEUE_NOTIF_INPUT;

-- PURPOSE
--
--  To update and en-queue RCA completed severity in MGMT_NOTIFY_INPUT_Q 
--
-- PARAMETERS
--
--  violation_guid_in - violation guid 
PROCEDURE UPDATE_AND_ENQUEUE_RCA_SEV(violation_guid_in IN RAW,
                                     notif_status_in IN NUMBER)
IS
BEGIN
  UPDATE mgmt_violations
     SET load_timestamp = SYSDATE,
         notification_status = notif_status_in
   WHERE violation_guid = violation_guid_in;

QUEUE_NOTIF_INPUT(violation_guid_in, EMD_NOTIFICATION.GUID_TYPE_SEVERITY);

END UPDATE_AND_ENQUEUE_RCA_SEV;

-- PURPOSE
--
--  To retrieve global repeat settings
--
-- PARAMETERS
--  p_enabled - 1 if enabled, 0 is disabled
--  p_frequency - frequency in minutes,
--  p_count -- maximum repeat count
--
PROCEDURE GET_GLOBAL_REPEAT_SETTINGS(
            p_enabled OUT NUMBER,
            p_frequency OUT NUMBER,
            p_count OUT NUMBER)
IS
BEGIN
    p_enabled := GET_CONFIG_VALUE(REPEAT_ENABLED_PARAM, REPEAT_ENABLED_DEFAULT);
    p_frequency := GET_CONFIG_VALUE(REPEAT_FREQUENCY_PARAM, REPEAT_FREQUENCY_DEFAULT);
    p_count := GET_CONFIG_VALUE(REPEAT_COUNT_PARAM, REPEAT_COUNT_DEFAULT);
END;

-- PURPOSE
--
--  To set global repeat settings
--
-- PARAMETERS
--  p_enabled - 1 if enabled, 0 is disabled
--  p_frequency - frequency in minutes,
--  p_count -- maximum repeat count
--
PROCEDURE SET_GLOBAL_REPEAT_SETTINGS(
            p_enabled IN NUMBER DEFAULT REPEAT_ENABLED_DEFAULT,
            p_frequency IN NUMBER DEFAULT REPEAT_FREQUENCY_DEFAULT,
            p_count IN NUMBER DEFAULT REPEAT_COUNT_DEFAULT)
IS
    l_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER;
BEGIN
    -- Ensure that the caller is a super-user.
    IF MGMT_USER.has_priv(l_current_user, MGMT_USER.SUPER_USER) = 0 THEN
        raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR,
            'Only superusers can set global repeat notification settings');
    END IF;

SET_CONFIG_VALUE(REPEAT_ENABLED_PARAM, p_enabled);
    SET_CONFIG_VALUE(REPEAT_FREQUENCY_PARAM, p_frequency);
    SET_CONFIG_VALUE(REPEAT_COUNT_PARAM, p_count);
END;

-- PURPOSE
--
--  To queue a repeat signal into MGMT_NOTIFY_INPUT_Q 
-- 
-- PARAMETERS
-- 
--  p_msg - violation GUID or state change guid + guid type
--  p_rule_guid - GUID of the rule for which repeat is enabled
--  p_repeat_count - Number of times the repeat is sent for this severity
--  p_delay - duration in minutes after which repeat notification should be send
PROCEDURE QUEUE_REPEAT_NOTIF_INPUT(p_msg_in RAW,
                                   p_rule_guid RAW,  
                                   p_repeat_count NUMBER,
                                   p_delay NUMBER)
IS
  enq_options         dbms_aq.enqueue_options_t;
  qMsg_properties     dbms_aq.message_properties_t;
  qMsg_handle         RAW(16);
  l_payload           VARCHAR2(256);
BEGIN
  qMsg_properties.delay := p_delay * 60;

IF(length(p_msg_in) != 32)
  THEN
    IF EMDW_LOG.P_IS_INFO_SET
    THEN
       EMDW_LOG.INFO('Invalid severity GUID : ' || p_msg_in ,  'queue_notif_input');
    END IF;
  END IF;
  l_payload := p_msg_in || '|' || GUID_TYPE_REP_SEVERITY || '|' || p_rule_guid || '|' || p_repeat_count;
  -- Add the message to the queue
  DBMS_AQ.ENQUEUE(queue_name => G_NOTIFY_INPUT_Q_NAME,
                  enqueue_options => enq_options,
                  message_properties => qMsg_properties,
                  payload => utl_raw.cast_to_raw(l_payload),
                  msgid => qMsg_handle);
EXCEPTION
WHEN OTHERS
THEN
    mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME,
                       MGMT_GLOBAL.UNEXPECTED_ERR,
                       MGMT_GLOBAL.UNEXPECTED_ERR_M ||
                       'Error while enquing the severity : ' || p_msg_in || SQLERRM,
                       'ORA',  p_msg_in);

END QUEUE_REPEAT_NOTIF_INPUT;

-- 
-- PROCEDURE: ACKNOWLEDGE_ALERT
--
-- PURPOSE
--  To acknowledge a particular alert record.
--
-- PARAMETERS
--  p_violation_guid - GUID of the violation record that needs to be acknowledged.
--  p_acknowledged_by - User name who aknowledged this alert.
--  p_annotation_type - Annotation type for severity acknowledged annotation
--  p_message - Annotation message
PROCEDURE ACKNOWLEDGE_ALERT(p_violation_guid  IN RAW,
                            p_acknowledged_by IN VARCHAR2,
                            p_annotation_type IN VARCHAR2,
                            p_message         IN VARCHAR2)
IS
  l_target_guid RAW(16);
  l_tz_region   VARCHAR2(64);
BEGIN
  IF( p_violation_guid IS NULL OR p_acknowledged_by IS NULL OR
      p_annotation_type IS NULL OR p_message IS NULL ) 
  THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 
                            'All the parameters are mandatory');
  END IF;

UPDATE mgmt_violations 
     SET acknowledged = 1,
         acknowledged_by = p_acknowledged_by
   WHERE violation_guid = p_violation_guid
   RETURNING target_guid INTO l_target_guid;

IF( l_target_guid IS NULL)
   THEN
     raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 
                             'Violation record does not exist');
   END IF;

SELECT TIMEZONE_REGION
    INTO l_tz_region
    FROM mgmt_targets
   WHERE target_guid = l_target_guid;

INSERT INTO MGMT_ANNOTATION (source_obj_type, source_obj_guid, 
                                timestamp, annotation_type, 
                                user_name, message)
   VALUES (MGMT_GLOBAL.G_ANNOTATION_SOURCE_SEVERITY, p_violation_guid, 
           mgmt_global.sysdate_tzrgn(l_tz_region), p_annotation_type,
           p_acknowledged_by, p_message);

END ACKNOWLEDGE_ALERT;

BEGIN
  -- Get the repository owner and set up the queue name to avoid string
  -- concatentation
  G_REPOSITORY_OWNER := MGMT_USER.GET_REPOSITORY_OWNER;
  G_QUEUE_NAME := G_REPOSITORY_OWNER || '.MGMT_NOTIFY_Q';
  G_NOTIFY_INPUT_Q_NAME := G_REPOSITORY_OWNER || '.MGMT_NOTIFY_INPUT_Q'; 
  G_DEV_PING_STATE_TIMEOUT := GET_CONFIG_VALUE(NOTIFY_DEV_PING_STATE_TIMEOUT, DEF_DEV_PING_STATE_TIMEOUT);

END EMD_NOTIFICATION;
/
show errors

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de