Rem drv: <create type="triggers" pos="basic/basic_triggers.sql+"/>
Rem
Rem $Header: blackout_triggers.sql 02-aug-2006.11:52:46 rkpandey Exp $
Rem $Header: blackout_triggers.sql 02-aug-2006.11:52:46 rkpandey Exp $
Rem
Rem blackout_triggers.sql
Rem
Rem Copyright (c) 2002, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      blackout_triggers.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rkpandey    08/02/06 - Backport rkpandey_fix_host_notif from main
Rem    skini       12/12/05 - Backport skini_bug-4696597 from main 
Rem    rkpandey    03/31/06 - blackout agent at last 
Rem    skini       11/28/05 - Fix bu 4696597: when closing node-level 
Rem                           agent-side blackouts, process all current 
Rem                           targets 
Rem    skini       09/21/05 - After avail change, catch all exceptions 
Rem    pratagar    08/29/05 - bug 4572196 
Rem    pmodi       07/08/05 - Bug:4399250 - Use NLS message id for blackout 
Rem    skini       07/10/05 - Fix bug 4367848: catch NO_DATA_FOUND 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    skini       04/12/05 - Fix infinite loop 
Rem    skini       04/07/05 - Add update trigger to MMGT_BLACKOUTS 
Rem    skini       03/21/05 - Change blackout message 
Rem    kmanicka    03/04/05 - move to EMDW_LOG from dbms_output 
Rem    skini       02/20/05 - Fix 4150882: select distinct 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    snakai      11/22/04 - update beacon avail calls 
Rem    dhgoswam    11/02/04 - bug 3851644
Rem    snakai      10/31/04 - process blackout end events for beacon monitored 
Rem                           tgts 
Rem    rpinnama    09/13/04 - mgmt_severity -> mgmt_violations (esp for returning clause)
Rem    skini       09/03/04 - Fix equal start times 
Rem    skini       08/30/04 - Fix for bug 3413008: account for out-of-order 
Rem                           jobs 
Rem    skini       08/18/04 - Catch no_data_found 
Rem    gan         07/16/04 - handle duplicate record for severity bug 3492516 
Rem    skini       12/15/03 - Move end time check to trigger 
Rem    skini       12/05/03 - Obtain a lock before querying in insert trigger 
Rem    streddy     11/30/03 - Call master_agent package for blackouts 
Rem    skini       09/25/03 - Convert repos columns to utc columns 
Rem    skini       09/12/03 - Update source for emctl BOs 
Rem    skini       08/28/03 - Only update windows for the current occurrence 
Rem    ancheng     02/14/03 - target version fix
Rem    skini       01/16/03 - Insert flat target entries for cli blackouts
Rem    skini       12/05/02 - Generate messages
Rem    skini       12/03/02 - Change in set_blackout_status
Rem    skini       11/24/02 - Add repos timestamps to windows table
Rem    skini       11/15/02 - Use category props
Rem    skini       11/12/02 - Add triggers to update blackout windows
Rem    skini       08/26/02 - ENDED state
Rem    skini       08/25/02 - Use set_blackout_status
Rem    skini       08/20/02 - New blackout states
Rem    skini       08/05/02 - Update blackout status
Rem    skini       07/30/02 - Overlapping blackouts
Rem    skini       07/26/02 - continue blackout development
Rem    skini       07/23/02 - Handle overlapping blackouts
Rem    rpinnama    06/05/02 - Insert severity record on detecting blackout start.
Rem    skini       06/12/02 - Table name change
Rem    rpinnama    05/14/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem

rem
rem  capture some availability data by watching blackout state changes
rem  (0 = in blackout, 1 = out of blackout)
rem

CREATE OR REPLACE TRIGGER blackout_change
    BEFORE INSERT ON MGMT_BLACKOUT_STATE FOR EACH ROW
DECLARE

    latest_availability_rowid ROWID;
    latest_availability_status NUMBER;
    latest_availability_sev_guid RAW(16);
    new_annotation_guid RAW(16);
    l_emd_url VARCHAR2(2000);
    l_count NUMBER;
    l_target_guid RAW(16);
    is_target_type_host BOOLEAN;
    host_guid RAW(16);
    host_availability_status NUMBER;
    l_current_target_state NUMBER;

    l_current_blackout_state NUMBER;

    l_blackout_name MGMT_BLACKOUTS.blackout_name%TYPE;
    l_created_by MGMT_BLACKOUTS.created_by%TYPE;
    l_reason_id MGMT_BLACKOUTS.reason_id%TYPE;
    l_reason MGMT_BLACKOUT_REASON.reason%TYPE;

    l_created_thru MGMT_BLACKOUTS.created_thru%TYPE;
    
    l_actual_start_time DATE;
    l_actual_end_time DATE;
    l_occurrence_number NUMBER;

    response_metric_guid RAW(16);
    update_availability BOOLEAN := false;

    l_new_blackout_status NUMBER;
    l_monitoring_mode     NUMBER;

    l_open_windows MGMT_BLACKOUT_WINDOW_ARRAY;
    l_current_window MGMT_BLACKOUT_WINDOW_RECORD;

    -- A list of blackout windows to potentially end as part
    -- of the processing for the current window.
    -- Entries here are inserted in ascending order of end time.
    l_blackouts_to_end MGMT_BLACKOUT_WINDOW_ARRAY := MGMT_BLACKOUT_WINDOW_ARRAY();

    k NUMBER;

    l_start_msg VARCHAR2(1000);
    l_stop_msg VARCHAR2(1000);
    l_start_msg_params VARCHAR2(1000);
    l_stop_msg_params VARCHAR2(1000);

  BEGIN
    -- Check for invalid entries 
    BEGIN
    SELECT blackout_status, created_thru, occurrence_number,
           created_by, reason_id, blackout_name INTO 
       l_current_blackout_state, l_created_thru, l_occurrence_number,
       l_created_by, l_reason_id, l_blackout_name FROM
          MGMT_BLACKOUTS WHERE blackout_guid=:new.blackout_guid;

    IF l_reason_id IS NOT NULL THEN
        SELECT  reason INTO l_reason
        FROM    MGMT_BLACKOUT_REASON
        WHERE   reason_id=l_reason_id;
    ELSE
        l_reason := '<none>';
    END IF;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Blackout definition does not exist! Reject it?
        raise_application_error(MGMT_GLOBAL.INVALID_BLACKOUT_ERR,
          'Blackout ' || :new.blackout_guid || ' does not exist');        
    END;
    
    -- Check for duplicates. We reject the last one
    SELECT COUNT(*) INTO l_count FROM MGMT_BLACKOUT_STATE WHERE 
        blackout_guid=:new.blackout_guid AND
        target_guid=:new.target_guid AND 
        collection_timestamp=:new.collection_timestamp AND
        blackout_code=:new.blackout_code;

    IF l_count > 0 THEN
        raise_application_error(MGMT_GLOBAL.DUPLICATE_RECORD_ERR,
          'Duplicate record generated for guid ' || :new.blackout_guid);
    END IF;

    -- Lock a row in the blackouts proxy tables to serialize
    -- severity inserts for a single target
    SELECT target_guid INTO l_target_guid
    FROM   MGMT_BLACKOUT_PROXY_TARGETS
    WHERE  target_guid=:new.target_guid
    FOR UPDATE;

    -- Collect all active blackout windows that start before the current
    -- one. Some of them will potentially have to be started, others stopped
    SELECT  MGMT_BLACKOUT_WINDOW_RECORD(blackout_guid, occurrence_number, 
                                        start_time, end_time, status)
    BULK COLLECT INTO l_open_windows
    FROM    MGMT_BLACKOUT_WINDOWS
    WHERE   target_guid=:new.target_guid
    AND     status NOT IN  (MGMT_BLACKOUT.BLK_STATE_STOPPED,
                            MGMT_BLACKOUT.BLK_STATE_ENDED)
    AND     start_time < :new.collection_timestamp
    AND     blackout_guid != :new.blackout_guid
    ORDER BY start_time;

    IF l_open_windows IS NOT NULL AND l_open_windows.COUNT > 0 THEN
        -- We need to always lock the blackouts in the same order to
        -- prevent deadlocks. Proceed only after all locks are obtained.
        -- MGMT_BLACKOUT_ENGINE.lock_windows(l_open_windows);

        FOR i IN 1..l_open_windows.COUNT LOOP
            l_current_window := l_open_windows(i);

            -- Close all pending windows whose end time is less
            -- than the current start time. Note that these
            -- windows are already in sorted order.
            FOR j IN 1..l_blackouts_to_end.COUNT LOOP
                IF l_blackouts_to_end(j).end_time <= l_current_window.start_time THEN
                    IF EMDW_LOG.p_is_debug_set THEN
                        EMDW_LOG.debug('Closing window with end time ' ||
                            to_char(l_blackouts_to_end(j).end_time, 'mm/dd/yyyy hh:mi:ss pm'),MGMT_BLACKOUT_ENGINE.MODULE_NAME);
                    END IF;
                    MGMT_BLACKOUT_ENGINE.end_blackout_window(l_blackouts_to_end(j).blackout_guid,
                                                             l_blackouts_to_end(j).occurrence_number,
                                                             :new.target_guid,
                                                             l_blackouts_to_end(j).end_time);

                    l_blackouts_to_end.delete(j);
                END IF;
            END LOOP;

            -- Insert a START for the current window, if required
            IF l_current_window.status=MGMT_BLACKOUT.BLK_STATE_START_PENDING THEN
                IF EMDW_LOG.p_is_debug_set THEN
                    EMDW_LOG.debug('STARTING window with start time ' ||
                            to_char(l_current_window.start_time, 'mm/dd/yyyy hh:mi:ss pm'),MGMT_BLACKOUT_ENGINE.MODULE_NAME);
                END IF;
                MGMT_BLACKOUT_ENGINE.start_blackout_window(l_current_window.blackout_guid,
                                                           l_current_window.occurrence_number,
                                                           :new.target_guid);
            END IF;

            -- Add the end time in sorted order to the current list
            -- of blackouts that may need to be ended
            IF l_current_window.end_time IS NOT NULL THEN
                k := 1;
                WHILE k <= l_blackouts_to_end.COUNT AND 
                  l_blackouts_to_end(k).end_time < l_current_window.end_time LOOP
                    k := k+1;
                END LOOP;

                l_blackouts_to_end.extend(1);
                IF k < l_blackouts_to_end.COUNT THEN
                    FOR l IN k+1..l_blackouts_to_end.COUNT-1 LOOP
                        l_blackouts_to_end(l+1):= l_blackouts_to_end(l);
                    END LOOP;
                END IF;
                l_blackouts_to_end(k) := l_current_window;
                IF EMDW_LOG.p_is_debug_set THEN
                    EMDW_LOG.debug('Added to end list window with start time ' ||
                        to_char(l_current_window.start_time, 'mm/dd/yyyy hh:mi:ss pm') ||
                        ' end time ' || to_char(l_current_window.end_time, 'mm/dd/yyyy hh:mi:ss pm') ||
                        ' at index ' || k,MGMT_BLACKOUT_ENGINE.MODULE_NAME);
                END IF;
            END IF;
        END LOOP;

        -- End all remaining open windows that need to be ended
        -- before this one
        -- Note that the previous loop would have
        -- ended only blackout windows scheduled to end before the
        -- start of another one
        FOR j IN 1..l_blackouts_to_end.COUNT LOOP
            IF l_blackouts_to_end(j).end_time < :new.collection_timestamp THEN
                IF EMDW_LOG.p_is_debug_set THEN
                    EMDW_LOG.debug('Closing window with end time ' ||
                        to_char(l_blackouts_to_end(j).end_time, 'mm/dd/yyyy hh:mi:ss pm'),MGMT_BLACKOUT_ENGINE.MODULE_NAME);
                END IF;

                MGMT_BLACKOUT_ENGINE.end_blackout_window(l_blackouts_to_end(j).blackout_guid,
                                                         l_blackouts_to_end(j).occurrence_number,
                                                         :new.target_guid,
                                                         l_blackouts_to_end(j).end_time);

            END IF;
        END LOOP;
    END IF;

    -- Get the current target state, accounting for overlapping
    -- blackouts
    SELECT SUM(decode(blackout_status, 1, 1, 0)) INTO l_count
            FROM MGMT_BLACKOUT_FLAT_TARGETS WHERE
                target_guid=:new.target_guid;

    IF l_count > 0 THEN
        l_current_target_state := MGMT_BLACKOUT_ENGINE.IN_BLACKOUT;
    ELSE
        l_current_target_state := MGMT_BLACKOUT_ENGINE.NOT_IN_BLACKOUT;
    END IF;

    IF EMDW_LOG.p_is_debug_set THEN
        EMDW_LOG.debug('Old target state for ' || :new.target_guid ||
            ' is ' || l_current_target_state,MGMT_BLACKOUT_ENGINE.MODULE_NAME);
    END IF;

    -- Update the state of the target in the blackout flat target status
    UPDATE MGMT_BLACKOUT_FLAT_TARGETS SET blackout_status=:new.blackout_code
       WHERE blackout_guid=:new.blackout_guid AND
             target_guid=:new.target_guid;

    IF :new.blackout_code = MGMT_BLACKOUT_ENGINE.IN_BLACKOUT THEN
        :new.target_status := MGMT_BLACKOUT_ENGINE.IN_BLACKOUT;
    ELSE
        -- Obtain the overall state of the target; this is what we
        -- should actually set the target_state to
        SELECT SUM(decode(blackout_status, 1, 1, 0)) INTO l_count
            FROM MGMT_BLACKOUT_FLAT_TARGETS WHERE
                target_guid=:new.target_guid;

        IF l_count > 0 THEN
            -- In blackout
            :new.target_status := MGMT_BLACKOUT_ENGINE.IN_BLACKOUT;
        ELSE
            -- Not in blackout
            :new.target_status := MGMT_BLACKOUT_ENGINE.NOT_IN_BLACKOUT;
        END IF;

    END IF;

    -- Update the overall blackout state IF this is a cli blackout
    -- For repository blackouts, the overall state will be updated
    -- after all targets have been processed
    IF l_created_thru IS NOT NULL THEN
        IF :new.blackout_code=MGMT_BLACKOUT_ENGINE.IN_BLACKOUT  THEN
            UPDATE  MGMT_BLACKOUT_FLAT_TARGETS 
            SET     job_status=MGMT_BLACKOUT_ENGINE.BLK_JSTATE_STARTED
            WHERE   blackout_guid=:new.blackout_guid
            AND     target_guid=:new.target_guid;
        ELSE
            UPDATE  MGMT_BLACKOUT_FLAT_TARGETS
            SET     job_status=MGMT_BLACKOUT_ENGINE.BLK_JSTATE_STOPPED
            WHERE   blackout_guid=:new.blackout_guid
            AND     target_guid=:new.target_guid;
        END IF;

        MGMT_BLACKOUT_ENGINE.set_blackout_status(:new.blackout_guid, 
                               MGMT_BLACKOUT_ENGINE.BLK_MODE_NONE);

        SELECT  blackout_status 
        INTO    l_new_blackout_status 
        FROM    MGMT_BLACKOUTS 
        WHERE   blackout_guid=:new.blackout_guid;   

        IF l_new_blackout_status=MGMT_BLACKOUT_ENGINE.BLK_STATE_ENDED THEN
            SELECT  emd_url INTO l_emd_url 
            FROM    MGMT_TARGETS
            WHERE   target_guid=:new.target_guid;

            MGMT_BLACKOUT_ENGINE.submit_discard_state_job(:new.blackout_guid,
                                                          l_emd_url,
                                                          :new.collection_timestamp);
         END IF;

    END IF;

    IF EMDW_LOG.p_is_debug_set THEN
        EMDW_LOG.debug('New target state for ' || :new.target_guid ||
            ' is ' || :new.target_status,MGMT_BLACKOUT_ENGINE.MODULE_NAME);
    END IF;

    -- Generate a severity (which will affect availability)
    -- only if the state of the target has changed
    IF :new.target_status != l_current_target_state THEN
        update_availability := true;
    END IF;

    -- TODO: 
    --       Exclude beacons
    --       Generate appropriate annotations
    --       Log errors if we receive an invalid blackout code
    --       or target_guid.

    -- 
    -- Get Response/Status Metric guid for the target
    --   Can we avoid getting this guid and get this in severity trigger?
    --
    IF update_availability THEN
    BEGIN
      SELECT DISTINCT met.metric_guid INTO response_metric_guid
        FROM MGMT_TARGETS tgt, MGMT_METRICS met
       WHERE tgt.target_guid = :new.target_guid
         AND tgt.target_type = met.target_type
         AND met.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
         AND met.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN
         AND met.type_meta_ver = tgt.type_meta_ver
         AND (met.category_prop_1 = tgt.category_prop_1 OR 
              met.category_prop_1 = ' ')
         AND (met.category_prop_2 = tgt.category_prop_2 OR 
              met.category_prop_2 = ' ')
         AND (met.category_prop_3 = tgt.category_prop_3 OR 
              met.category_prop_3 = ' ')
         AND (met.category_prop_4 = tgt.category_prop_4 OR 
              met.category_prop_4 = ' ')
         AND (met.category_prop_5 = tgt.category_prop_5 OR 
              met.category_prop_5 = ' ');
             

      update_availability := TRUE;
      
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        update_availability := FALSE;
        
    END;
    END IF;

    IF (update_availability = TRUE) THEN

      IF (:new.target_status = MGMT_BLACKOUT_ENGINE.IN_BLACKOUT) THEN

        l_start_msg := REPLACE(MGMT_BLACKOUT_ENGINE.BLK_START_MSG, '{0}', l_created_by);
        l_start_msg := REPLACE(l_start_msg, '{1}', l_reason);
        l_start_msg := REPLACE(l_start_msg, '{2}', l_blackout_name);

        l_start_msg_params := l_created_by||'&'||l_reason||'&'||l_blackout_name ;


        -- Setting blackout for this target...
        BEGIN
          INSERT INTO mgmt_violations
            (target_guid, policy_guid, 
             collection_timestamp,
             violation_level, message, message_nlsid, message_params)
          VALUES 
            (:new.target_guid, response_metric_guid,
             :new.collection_timestamp, 
             MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START,
             l_start_msg, MGMT_BLACKOUT_ENGINE.BLK_START_MSG_NLSID, l_start_msg_params )
          RETURNING collection_timestamp
          INTO      l_actual_start_time;
        EXCEPTION
            WHEN MGMT_GLOBAL.duplicate_record THEN
                :new.collection_timestamp :=
                    MGMT_BLACKOUT_ENGINE.reinsert_blackout_severity(
                        :new.blackout_guid,
                        :new.target_guid,
                        response_metric_guid,
                        MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START,
                        MGMT_BLACKOUT_ENGINE.BO_START_MSG,
                        MGMT_BLACKOUT_ENGINE.BO_START_MSG_NLSID,
                        NULL
                        );

            WHEN MGMT_GLOBAL.internal_severity THEN
                :new.collection_timestamp :=
                    MGMT_BLACKOUT_ENGINE.reinsert_blackout_severity(
                        :new.blackout_guid,
                        :new.target_guid,
                        response_metric_guid,
                        MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START,
                        MGMT_BLACKOUT_ENGINE.BO_START_MSG,
                        MGMT_BLACKOUT_ENGINE.BO_START_MSG_NLSID,
                        NULL
                        );
        END;

         IF l_actual_start_time > :new.collection_timestamp THEN
         BEGIN
             :new.collection_timestamp := l_actual_start_time;
             SELECT end_time INTO l_actual_end_time
             FROM   MGMT_BLACKOUT_WINDOWS
             WHERE  blackout_guid=:new.blackout_guid
             AND    target_guid=:new.target_guid
             AND    occurrence_number=l_occurrence_number
             AND    status NOT IN (MGMT_BLACKOUT.BLK_STATE_STOPPED,
                                   MGMT_BLACKOUT.BLK_STATE_ENDED);

             -- Adjust the end time if required
            IF l_actual_end_time IS NOT NULL AND 
               l_actual_end_time <= l_actual_start_time THEN
               l_actual_end_time := l_actual_start_time+(1/(60*60*24));
            END IF;

            UPDATE MGMT_BLACKOUT_WINDOWS
            SET    start_time=l_actual_start_time,
                   end_time=l_actual_end_time
            WHERE  blackout_guid=:new.blackout_guid
            AND    target_guid=:new.target_guid
            AND    occurrence_number=l_occurrence_number
            AND    status NOT IN (MGMT_BLACKOUT.BLK_STATE_STOPPED, 
                                  MGMT_BLACKOUT.BLK_STATE_ENDED);

         EXCEPTION
         WHEN NO_DATA_FOUND THEN
             NULL;
         END;
         END IF;

        -- Blackout all the tests of Services (i.e. webapps)
        BEGIN
            MGMT_GENSVC_AVAIL.PROCESS_BLACKOUT_START(:new.target_guid, :new.collection_timestamp);
        EXCEPTION
            WHEN OTHERS THEN
                MGMT_BLACKOUT_ENGINE.log_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
                 'Error calling gensvc.process_blackout_start() for blackout ' ||
                 :new.blackout_guid || ' on target ' || :new.target_guid);
        END;

      ELSIF (:new.target_status = MGMT_BLACKOUT_ENGINE.NOT_IN_BLACKOUT) THEN

        -- Clearing blackout..
        BEGIN

        l_stop_msg := REPLACE(MGMT_BLACKOUT_ENGINE.BLK_STOP_MSG, '{0}', l_blackout_name);
        l_stop_msg_params := l_blackout_name ;

          INSERT INTO mgmt_violations
            (target_guid, policy_guid, 
             collection_timestamp,
             violation_level, message, message_nlsid, message_params)
           VALUES 
            (:new.target_guid, response_metric_guid,
             :new.collection_timestamp, 
             MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END,
             l_stop_msg, MGMT_BLACKOUT_ENGINE.BLK_STOP_MSG_NLSID, l_stop_msg_params );
        EXCEPTION
            WHEN MGMT_GLOBAL.duplicate_record THEN
                :new.collection_timestamp :=
                    MGMT_BLACKOUT_ENGINE.reinsert_blackout_severity(
                        :new.blackout_guid,
                        :new.target_guid,
                        response_metric_guid,
                        MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END,
                        MGMT_BLACKOUT_ENGINE.BO_END_MSG,
                        MGMT_BLACKOUT_ENGINE.BO_END_MSG_NLSID,
                        NULL
                        );

            WHEN MGMT_GLOBAL.internal_severity THEN
                :new.collection_timestamp :=
                    MGMT_BLACKOUT_ENGINE.reinsert_blackout_severity(
                        :new.blackout_guid,
                        :new.target_guid,
                        response_metric_guid,
                        MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END,
                        MGMT_BLACKOUT_ENGINE.BO_END_MSG,
                        MGMT_BLACKOUT_ENGINE.BO_END_MSG_NLSID,
                        NULL
                        );
        END;

        -- Select a new master agent if this is a multi-agent target
        SELECT monitoring_mode 
          INTO l_monitoring_mode
          FROM mgmt_targets
         WHERE target_guid = :new.target_guid;

        IF (l_monitoring_mode = mgmt_global.G_MON_MODE_OMS_MEDIATED) THEN
           em_master_agent.select_master_agent(:new.target_guid,
                                               :new.collection_timestamp);
        END IF;

        -- For services (i.e. webapps), discard the state of its beacons
        BEGIN
            MGMT_GENSVC_AVAIL.PROCESS_BLACKOUT_END(:new.target_guid, :new.collection_timestamp);
        EXCEPTION
            WHEN OTHERS THEN
                MGMT_BLACKOUT_ENGINE.log_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
                 'Error calling gensvc.process_blackout_end() for blackout ' ||
                 :new.blackout_guid || ' on target ' || :new.target_guid);
        END;

      ELSE
        NULL;
      END IF;
    ELSE
      NULL;
    END IF;

  END;
/
show errors;


CREATE OR REPLACE TRIGGER blackout_status AFTER UPDATE 
   ON MGMT_BLACKOUT_FLAT_TARGETS FOR EACH ROW
DECLARE
  l_created_thru MGMT_BLACKOUTS.created_thru%TYPE;
  l_occurrence_number MGMT_BLACKOUTS.occurrence_number%TYPE;
  l_status MGMT_BLACKOUTS.blackout_status%TYPE;
BEGIN
    SELECT  created_thru, occurrence_number 
    INTO    l_created_thru, l_occurrence_number
    FROM    MGMT_BLACKOUTS
    WHERE   blackout_guid=:new.blackout_guid;

    IF l_created_thru IS NULL THEN
        -- Compute the new status
        IF :new.job_status=MGMT_BLACKOUT_ENGINE.BLK_JSTATE_START_PROCESSING THEN
            l_status := MGMT_BLACKOUT.BLK_STATE_START_PENDING;
        ELSIF :new.job_status=MGMT_BLACKOUT_ENGINE.BLK_JSTATE_STOPPED THEN
            l_status := MGMT_BLACKOUT.BLK_STATE_ENDED;
        ELSE
            l_status := MGMT_BLACKOUT.BLK_STATE_STARTED;
        END IF;

        -- Update entries in the blackout window table. We only
        -- need to update "active" entries, ones that are 
        -- not in state stopped or ended.
        UPDATE  MGMT_BLACKOUT_WINDOWS 
        SET     status=l_status
        WHERE   blackout_guid=:new.blackout_guid 
        AND     occurrence_number=l_occurrence_number 
        AND     target_guid=:new.target_guid
        AND     status NOT IN (MGMT_BLACKOUT.BLK_STATE_STOPPED,
                               MGMT_BLACKOUT.BLK_STATE_ENDED);
    END IF;

END;
/
show errors;

CREATE OR REPLACE TRIGGER insert_flat_targets AFTER INSERT
   ON MGMT_BLACKOUT_TARGET_DETAILS FOR EACH ROW
DECLARE
    l_created_thru MGMT_BLACKOUTS.created_thru%TYPE;
    l_target_type MGMT_TARGETS.target_type%TYPE;
BEGIN
    -- For CLI blackouts, add entries to the flat targets table
    SELECT created_thru INTO l_created_thru 
    FROM   MGMT_BLACKOUTS
    WHERE  blackout_guid=:new.blackout_guid;

    IF l_created_thru IS NOT NULL THEN 
        -- This is an agent-side blackout
        INSERT INTO MGMT_BLACKOUT_FLAT_TARGETS(blackout_guid, target_guid,
            host_blackout, job_status, blackout_status, last_updated_time)
        VALUES
            (:new.blackout_guid, :new.target_guid, 0, 
             MGMT_BLACKOUT_ENGINE.BLK_JSTATE_START_PROCESSING,
             MGMT_BLACKOUT_ENGINE.NOT_IN_BLACKOUT, SYSDATE);
             
        --Find the target type to make sure that its of type host
        SELECT target_type INTO l_target_type
        FROM   MGMT_TARGETS
        WHERE  target_guid = :new.target_guid;

        --If include_members is true then insert all the dependent targets to the MGMT_BLACKOUT_FLAT_TARGETS
        IF (:new.include_members = 1 AND l_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN
            --Find all the targets guid for this url and insert in MGMT_BLACKOUT_FLAT_TARGETS
            FOR crec IN 
            (
                SELECT a.target_guid 
                FROM   MGMT_TARGETS a, MGMT_TARGETS h
                WHERE  h.target_guid = :new.target_guid
                AND    h.emd_url=a.emd_url
                AND    a.target_type != MGMT_GLOBAL.G_HOST_TARGET_TYPE
            )
            LOOP
                INSERT INTO MGMT_BLACKOUT_FLAT_TARGETS
                (
                       blackout_guid, 
                       target_guid,
                       host_blackout, 
                       job_status, 
                       blackout_status, 
                       last_updated_time
                 )
                 VALUES
                 (
                       :new.blackout_guid, 
                       crec.target_guid, 
                       0, 
                       MGMT_BLACKOUT_ENGINE.BLK_JSTATE_START_PROCESSING,
                       MGMT_BLACKOUT_ENGINE.NOT_IN_BLACKOUT, 
                       SYSDATE
                 );
            END LOOP;
        END IF;
    END IF;
END;
/
show errors;

CREATE OR REPLACE TRIGGER update_source BEFORE INSERT
   ON MGMT_BLACKOUT_SCHEDULE FOR EACH ROW
DECLARE
    l_created_thru MGMT_BLACKOUTS.created_thru%TYPE;
BEGIN
    BEGIN
        SELECT created_thru INTO l_created_thru 
        FROM   MGMT_BLACKOUTS
        WHERE  blackout_guid=:new.blackout_guid;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN;
    END;

    IF l_created_thru IS NOT NULL THEN
        IF :new.duration > 0 THEN
            :new.duration_source := 
                 MGMT_BLACKOUT_ENGINE.DURATION_SOURCE_DURATION;
        ELSIF :new.duration < 0 THEN
            :new.duration_source := 
                 MGMT_BLACKOUT_ENGINE.DURATION_SOURCE_INDEFINITE;
        ELSE
            :new.duration_source := 
                 MGMT_BLACKOUT_ENGINE.DURATION_SOURCE_ENDTIME;
        END IF;       
    END IF;
END;
/
show errors;

CREATE OR REPLACE TRIGGER blackout_window_insert BEFORE INSERT OR UPDATE
    ON MGMT_BLACKOUT_WINDOWS FOR EACH ROW
DECLARE 
    l_timezone_region MGMT_TARGETS.timezone_region%TYPE;
BEGIN
    SELECT timezone_region INTO l_timezone_region FROM MGMT_TARGETS WHERE
        target_guid=:new.target_guid;
     
    :new.utc_start_time := 
       MGMT_GLOBAL.to_utc(:new.start_time, l_timezone_region);

    IF :new.end_time IS NOT NULL THEN
        :new.utc_end_time := 
           MGMT_GLOBAL.to_utc(:new.end_time, l_timezone_region);
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        NULL;
END;
/
show errors;

CREATE OR REPLACE TRIGGER handle_related_targets
   BEFORE INSERT ON MGMT_BLACKOUT_STATE FOR EACH ROW
DECLARE
   l_created_thru MGMT_BLACKOUTS.created_thru%TYPE;   
   l_include_members MGMT_BLACKOUT_TARGET_DETAILS.include_members%TYPE;
   l_target_type MGMT_TARGETS.target_type%TYPE;

l_targets_to_process MGMT_USER_GUID_ARRAY;

BEGIN
   -- For CLI blackouts, add entries to the blackout state table
   SELECT created_thru INTO l_created_thru 
   FROM   MGMT_BLACKOUTS
   WHERE  blackout_guid=:new.blackout_guid;
		   
   IF l_created_thru IS NOT NULL THEN
      BEGIN    
          SELECT include_members INTO l_include_members 
          FROM   MGMT_BLACKOUT_TARGET_DETAILS
	      WHERE  blackout_guid=:new.blackout_guid
	      AND    target_guid=:new.target_guid;

      EXCEPTION
          -- If we get a NO_DATA_FOUND, we are processing a
          -- node-level blackout, and a target that is on
          -- the node but is not the host.
          WHEN NO_DATA_FOUND THEN
              l_include_members := 0;
      END;
                
      --Find the target type to make sure that its of type host
      SELECT target_type INTO l_target_type
      FROM   MGMT_TARGETS
      WHERE  target_guid = :new.target_guid;
	        
      --If include_members is true then insert all the dependent targets to the MGMT_BLACKOUT_STATE
      IF (l_include_members = 1 AND 
          l_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN 
          --Find all the targets guid for this host url and insert in MGMT_BLACKOUT_FLAT_TARGETS       
          SELECT t.target_guid
          BULK COLLECT INTO l_targets_to_process
          FROM   MGMT_TARGETS t, MGMT_BLACKOUT_FLAT_TARGETS ft
          WHERE  ft.blackout_guid=:new.blackout_guid
          AND    t.target_guid=ft.target_guid
          AND    t.target_guid != :new.target_guid
        ORDER BY DECODE(t.target_type, MGMT_GLOBAL.G_AGENT_TARGET_TYPE, 2, 1), 1;

          IF l_targets_to_process IS NOT NULL AND
             l_targets_to_process.COUNT > 0 THEN
              FOR i IN 1..l_targets_to_process.COUNT LOOP
                  INSERT INTO MGMT_BLACKOUT_STATE (
                    blackout_guid, 
                    target_guid,
                    collection_timestamp, 
                    load_timestamp, 
                    blackout_code, 
                    target_status
                  ) 
                  VALUES (
                    :new.blackout_guid, 
                    l_targets_to_process(i),
                    :new.collection_timestamp, 
                    :new.load_timestamp,
                    :new.blackout_code, 
                    :new.target_status
                  );
              END LOOP;
          END IF;
      END IF;
   END IF;	
END;
/
show errors;

-- Compute status for all agent-side blackouts on blackout update
CREATE OR REPLACE TRIGGER compute_agent_side_status
   BEFORE UPDATE ON MGMT_BLACKOUTS FOR EACH ROW
BEGIN
    IF :new.created_thru IS NOT NULL THEN
        :new.blackout_status := 
            MGMT_BLACKOUT_ENGINE.compute_blackout_status(:new.blackout_guid,
                                    MGMT_BLACKOUT_ENGINE.BLK_MODE_NONE,
                                    :new.last_start_time,
                                    :new.last_end_time);
    END IF;
END;
/
show errors;