Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/blackout/blackout_engine_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2009/03/18 02:29:02 bram Exp $
Rem
Rem blackout_engine_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      blackout_engine_pkgbody.sql - Internal blackout routines
Rem
Rem    DESCRIPTION
Rem        This contains internal blackout routines called by the sdk
Rem        and UI
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED 
Rem    rrawat  05/27/08 - Bug-7114090
Rem    rkpande 09/06/07 - 6318405:Try to stop STOP_PROCESSING blackouts as well
Rem    rrawat  08/06/07 - Bug-6321884
Rem    rrawat  08/13/07 - Backport rrawat_bug-6321884 from main
Rem    rkpande 03/29/07 - Bug 5912875: fix callbacks for system privileges
Rem    rkpande 04/30/07 - Backport rkpandey_bug-5912875 from main
Rem    rkpande 10/27/07 - Backport rkpandey_bug-5010580_1 from main
Rem    rkpande 05/19/06 - Include cluster target in blackout 
Rem    rkpande 04/12/07 - Backport rkpandey_bug-5213248 from main
Rem    dgiaimo 04/06/07 - Fixing bug 5927961
Rem    dgiaimo 04/16/07 - Backport dgiaimo_bug-5927961 from main
Rem    rkpande 08/02/06 - Backport rkpandey_fix_host_notif from main
Rem    rkpande 04/18/06 - Bug 5010580 recompute status of emctl blk on target del
Rem    rkpande 03/25/06 - agent End blackout shud be the last 
Rem    rkpande 06/06/06 - 5061352: dont stop blackout in priv revoke 
Rem    rkpande 07/20/06 - Backport rkpandey_bug-5061352 from main 
Rem    skini   09/21/05 - Catch exceptions from call to suspend_collections() 
Rem    skini   09/02/05 - Remove function call 
Rem    skini   09/01/05 - Add purge policy on MGMT_BLACKOUTS 
Rem    dcawley 08/29/05 - Fix query in callback 
Rem    dcawley 08/15/05 - Change type for callback 
Rem    pmodi   07/21/05 - Change submit_discard_state_job signature to accpet 
Rem                       job name prefix 
Rem    skini   07/18/05 - Do not submit discard state jobs for repository 
Rem                       targets 
Rem    jsadras 07/08/05 - cutover emd_collection to mgmt_collection 
Rem    pmodi   07/08/05 - Bug:4399250 - Include msg nlsid 
Rem    mfidanb 06/24/05 - submit discard_state_job must support proxy targets 
Rem    dcawley 05/31/05 - Fix check for operator 
Rem    rzazuet 04/21/05 - Check returned lock status 
Rem    skini   04/07/05 - Add do_update flag to update_blackout_status 
Rem    ssukava 03/28/05 - Last_updated_by update on Editing Blackout 
Rem    skini   03/21/05 - Allow deadlocks to be retried 
Rem    ssukava 03/21/05 - Bug 4248388 Last Update User is not updated 
Rem                       correctly 
Rem    skini   03/09/05 - Account for partial states 
Rem    skini   03/06/05 - Add API to end agent-side blackout 
Rem    kmanick 03/04/05 - move to EMDW_LOG from dbms_output 
Rem    rzazuet 03/01/05 - Blackouts on redundancy groups 
Rem    rzazuet 02/24/05 - Report targets without OPERATOR in 
Rem                       check_modify_privs 
Rem    rzazuet 02/23/05 - Fix query in grant/revoke priv callback 
Rem    dcawley 02/21/05 - Replace enter super user mode 
Rem    rzazuet 02/21/05 - Enter super_user mode in cancel_job 
Rem    dcawley 01/26/05 - Change privilege callback 
Rem    ramalho 01/25/05 - assoc def name change, 
Rem                       ASSOC_DEF_MEMBER-->ASSOC_DEF_CONTAINS 
Rem    rzazuet 01/06/05 - Add best effort support for groups/hosts 
Rem    skini   01/20/05 - Code review changes; fix for bug 4128023 
Rem    skini   01/18/05 - Do not delete old blackout windows 
Rem    skini   01/13/05 - PK constraint for reasons table 
Rem    vkgarg  12/21/04 - update edit_schedule to use timezone region inplace 
Rem                       of delta 
Rem    rzazuet 12/13/04 - Fix 3925408 
Rem    dcawley 11/09/04 - Fix query on priv grants 
Rem    vkgarg  10/18/04 - setting reason as optional 
Rem    kmanick 09/20/04 - add user_model callbacks
Rem    skini   09/03/04 - Continue BO window fix 
Rem    skini   08/29/04 - Fix 3855061: account for non-cascading group privs 
Rem    ramalhot 08/24/04 - cutover to new assoc tables 
Rem    skini   08/22/04 - Fix indefinite BO on schedule edit 
Rem    skini   08/18/04 - Fix time skew issue 
Rem    skini   08/17/04 - Blackout fixes 
Rem    rzazuet 07/21/04 - Fix 2819028 
Rem    skini   07/23/04 - Fix bug 3770368 
Rem    dcawley 07/07/04 - Increase user name size 
Rem    dsahraw 06/22/04 - use get_job_schedule_record 
Rem    rzazuet 05/18/04 - Add create_immediate_blackout 
Rem    gan     03/29/04 - bug 3467408 
Rem    rzazuet 03/26/04 - Fix 3441090 
Rem    jsadras 03/18/04 - 
Rem    jsadras 03/16/04 - 
Rem    rzazuet 02/10/04 - Remove order by in get_blackout_details 
Rem    skini   01/08/04 - Fix timestamp issue 
Rem    skini   12/15/03 - Move end time check to trigger 
Rem    skini   12/12/03 - Fix bug 3300351 
Rem    skini   12/05/03 - Order blackout starts and stops by target_guid 
Rem    streddy 11/30/03 - Call master_agent package for blackouts 
Rem    skini   11/25/03 - Fix bogus end time when editing a bo 
Rem    skini   11/20/03 - Fix scheduled_time issues (3268831) 
Rem    skini   11/14/03 - Fix problem with recurring schedules and immediate 
Rem    skini   11/11/03 - Remove start job when stopping BOs scheduled in the 
Rem    dsahraw 10/22/03 - bug 3183060 
Rem    streddy 10/04/03 - Exclude cluster target types from host cascades 
Rem    streddy 10/02/03 - Comment out dbms_output 
Rem    skini   09/30/03 - merge 
Rem    skini   09/27/03 - Edit schedule for repeating BOs 
Rem    skini   09/26/03 - 
Rem    skini   09/25/03 - Fix repeating bo schedules 
Rem    skini   09/12/03 - Flat target query fixes 
Rem    rpinnam 09/08/03 - Add purge policy to purge blackout target state 
Rem    skini   09/08/03 - Change duration yet again to 1 
Rem    skini   09/08/03 - Fix blk submission against agents 
Rem    skini   09/08/03 - Change duration yet again to 1 
Rem    skini   09/05/03 - Fix duration issue 
Rem    skini   08/30/03 - Duplicate record handling 
Rem    skini   08/28/03 - Fix timezone issue with end blackout job 
Rem    skini   08/20/03 - Fix agent check 
Rem    skini   08/19/03 - Remove returning 
Rem    skini   08/19/03 - Add support for history, occ_no 
Rem    skini   08/11/03 - Fix bug 3089108: deal with recurring bos in start 
Rem    skini   07/03/03 - Remove end job only if edit/stop succeeds
Rem    skini   04/21/03 - Fix bug 2910581: fix start time when targets in different tzs
Rem    skini   03/25/03 - Use superuser mode in call to get_agent_time_url
Rem    skini   02/24/03 - Code review changes
Rem    skini   02/21/03 - Fix time adjustment
Rem    rpatti  02/26/03 - cleanup on target delete
Rem    skini   01/31/03 - Fix query on flat_targets
Rem    skini   01/17/03 - Allow agent-side bos to be deleted
Rem    skini   01/16/03 - Do not allow operations on CLI blackouts
Rem    skini   01/02/03 - Default p_stop_ts
Rem    dcawley 12/20/02 - Fix user model queries
Rem    skini   12/13/02 - New APIs to stop blackout/discard state
Rem    dcawley 12/10/02 - Column renamed to guid
Rem    skini   12/09/02 - Ensure that the end time is not in the past
Rem    skini   12/07/02 - Fix blackout status computation for edits
Rem    skini   12/05/02 - Fix to_agent_time
Rem    skini   12/03/02 - Introduce modificationFailed, 
Rem                             and partialModifyFailed states
Rem    skini   11/26/02 - Jobs and blackouts
Rem    skini   11/25/02 - 
Rem    skini   11/24/02 - Jobs and blackouts
Rem    skini   11/22/02 - Fix schedule change
Rem    skini   11/21/02 - Change schedule after BO in effect
Rem    skini   11/15/02 - Fix REM_PENDING for edit
Rem    skini   11/14/02 - Use changed signature of revoke_priv callback
Rem    skini   11/12/02 - Fix bug with blackout windows
Rem    skini   11/07/02 - Fix pending status
Rem    skini   11/06/02 - Update blackout windows when BO is stopped
Rem    rpinnam 10/09/02 - Return TRUE/FALSE in is_blacked_out
Rem    skini   10/05/02 - Fix state change bugs
Rem    skini   09/20/02 - Use actual stat time and duration
Rem    skini   09/18/02 - blackout windows
Rem    skini   08/26/02 - Introduce START_PROCESSING
Rem    skini   08/26/02 - Account for dependent targets
Rem    skini   08/26/02 - 
Rem    skini   08/23/02 - Finish up edit
Rem    skini   08/20/02 - New blackout states
Rem    skini   08/13/02 - Continue development, finish edit
Rem    skini   07/31/02 - Implement stop
Rem    skini   07/26/02 - Schema change to schedule
Rem    skini   07/23/02 - Logging, implement stop
Rem    skini   07/22/02 - 
Rem    skini   07/18/02 - Blackout APIs
Rem    skini   07/17/02 - Continue blackout development
Rem    skini   06/20/02 - skini_blackouts
Rem    skini   06/17/02 - Created
Rem

CREATE OR REPLACE PACKAGE BODY MGMT_BLACKOUT_ENGINE AS

-- The minimum duration
MIN_DURATION CONSTANT NUMBER := 1;
-- The minimum difference between the duration and the repeat
-- interval, for interval-based blackouts
MIN_DIFFERENCE CONSTANT NUMBER := 5;

-- PRIVATE PROCEDURES

-- Log an error message with the specified error code
PROCEDURE log_error(p_error_code NUMBER, p_error_message VARCHAR2) IS
BEGIN
    MGMT_LOG.log_error(MODULE_NAME, p_error_code, p_error_message);
END;

-- Return the specified time, assumed to be in repository timezone,
-- into the blackout timezone
FUNCTION to_blackout_time(p_timestamp DATE, p_timezone_info NUMBER,
                          p_tzoffset NUMBER,
                          p_tzregion VARCHAR2) RETURN DATE IS

l_rep_tzoffset_hours NUMBER;
l_rep_tzoffset_mins NUMBER;
l_rep_tzoffset NUMBER;

l_rep_tz MGMT_BLACKOUT_SCHEDULE.timezone_region%TYPE;
BEGIN
    IF p_timestamp IS NULL THEN
        RETURN null;
    END IF;

    IF p_timezone_info=TIMEZONE_REPOSITORY THEN
        RETURN p_timestamp;
    ELSIF p_timezone_info=TIMEZONE_RGN_SPECIFIED THEN
        SELECT to_char(SYSTIMESTAMP, 'TZR') INTO l_rep_tz FROM DUAL;
        RETURN MGMT_GLOBAL.adjust_tz(p_timestamp, l_rep_tz, p_tzregion);
    ELSE  -- Specified offset
        -- TODO Is there some way to not compute this each time?
        l_rep_tzoffset_hours := to_char(SYSTIMESTAMP, 'TZH');
        l_rep_tzoffset_mins := to_char(SYSTIMESTAMP, 'TZM');

        l_rep_tzoffset := l_rep_tzoffset_hours*60+l_rep_tzoffset_mins;

        RETURN p_timestamp-
               (l_rep_tzoffset-p_tzoffset)/(60*24);
    END IF;

END;

-- Return the current time in the timezone specified in the
-- blackout
FUNCTION sysdate_blackout_time(p_blackout_guid RAW) RETURN DATE IS
l_timezone_info NUMBER;
l_tzoffset NUMBER;
l_timezone_region MGMT_BLACKOUT_SCHEDULE.timezone_region%TYPE;

BEGIN
    SELECT timezone_info, timezone_offset, timezone_region INTO 
         l_timezone_info, l_tzoffset, l_timezone_region FROM
      MGMT_BLACKOUT_SCHEDULE WHERE
         blackout_guid=p_blackout_guid;

    RETURN to_blackout_time(SYSDATE, l_timezone_info, 
                            l_tzoffset, l_timezone_region);
END;

-- Convert the specified timestamp (in blackout time)
-- to utc
FUNCTION to_utc(p_blackout_guid RAW,
                p_timestamp DATE) RETURN DATE IS
l_timezone_info NUMBER;
l_tzoffset NUMBER;

l_rep_tzoffset_hours NUMBER;
l_rep_tzoffset_mins NUMBER;
l_rep_tzoffset NUMBER;

l_timezone_region MGMT_BLACKOUT_SCHEDULE.timezone_region%TYPE;
BEGIN
    IF p_timestamp IS NULL THEN
        RETURN null;
    END IF;

    -- Note: we use the current timezone offset of the agent
    -- since the blackout start times are specified using a
    -- specified timezone offset.
    SELECT timezone_info, timezone_offset, timezone_region INTO 
         l_timezone_info, l_tzoffset, l_timezone_region FROM
      MGMT_BLACKOUT_SCHEDULE WHERE
         blackout_guid=p_blackout_guid;


    IF l_timezone_info=TIMEZONE_SPECIFIED THEN
        RETURN p_timestamp-(l_tzoffset/(60*24));
    ELSIF l_timezone_info=TIMEZONE_RGN_SPECIFIED THEN
        RETURN MGMT_GLOBAL.to_utc(p_timestamp, l_timezone_region);
    ELSE
        -- timestamp was specified in repository timezone
        l_rep_tzoffset_hours := to_char(SYSTIMESTAMP, 'TZH');
        l_rep_tzoffset_mins := to_char(SYSTIMESTAMP, 'TZM');

        l_rep_tzoffset := l_rep_tzoffset_hours*60+l_rep_tzoffset_mins;

        RETURN p_timestamp-l_rep_tzoffset/(60*24);
    END IF;

END;

-- Convert the specified timestamp (in blackout time)
-- to repository time
FUNCTION to_rep_time(p_timestamp DATE, p_timezone_info NUMBER,
                     p_tzoffset NUMBER,
                     p_tzregion VARCHAR2) RETURN DATE IS

l_rep_tzoffset_hours NUMBER;
l_rep_tzoffset_mins NUMBER;
l_rep_tzoffset NUMBER;

l_rep_tz MGMT_BLACKOUT_SCHEDULE.timezone_region%TYPE;
BEGIN
    IF p_timestamp IS NULL THEN
        RETURN null;
    END IF;

    IF p_timezone_info=TIMEZONE_SPECIFIED THEN
        l_rep_tzoffset_hours := to_char(SYSTIMESTAMP, 'TZH');
        l_rep_tzoffset_mins := to_char(SYSTIMESTAMP, 'TZM');

        l_rep_tzoffset := l_rep_tzoffset_hours*60+l_rep_tzoffset_mins;

        RETURN p_timestamp+
               (l_rep_tzoffset-p_tzoffset)/(60*24);
    ELSIF p_timezone_info=TIMEZONE_RGN_SPECIFIED THEN
        SELECT to_char(SYSTIMESTAMP, 'TZR') INTO l_rep_tz FROM DUAL;
        RETURN MGMT_GLOBAL.adjust_tz(p_timestamp, p_tzregion, l_rep_tz);
    ELSE
        -- timestamp was specified in repository timezone
        RETURN p_timestamp;
    END IF;

END;

-- Convert the specified timestamp (in blackout time)
-- to repository time
FUNCTION to_rep_time(p_blackout_guid RAW,
                     p_timestamp DATE) RETURN DATE IS
l_timezone_info NUMBER;
l_tzoffset NUMBER;

l_rep_tzoffset_hours NUMBER;
l_rep_tzoffset_mins NUMBER;
l_rep_tzoffset NUMBER;

l_tzregion MGMT_BLACKOUT_SCHEDULE.timezone_region%TYPE;
BEGIN
    IF p_timestamp IS NULL THEN
        RETURN null;
    END IF;

    -- Note: we use the current timezone offset of the agent
    -- since the blackout start times are specified using a
    -- specified timezone offset.
    SELECT timezone_info, timezone_offset, timezone_region INTO 
         l_timezone_info, l_tzoffset, l_tzregion FROM
      MGMT_BLACKOUT_SCHEDULE WHERE
         blackout_guid=p_blackout_guid;

    RETURN to_rep_time(p_timestamp, l_timezone_info, l_tzoffset, l_tzregion);
END;

-- "Adjust" the agent time to account for severities that
-- may have come in since this time.
-- This could happen with immediate blackouts where severities
-- come in before the blackout can be inserted, and the 
-- collection timestamp is after the blackout start time 
FUNCTION adjust_agent_time(p_timestamp DATE,
                           p_target_guid RAW) RETURN DATE IS
l_agent_guid MGMT_TARGETS.target_guid%TYPE;
l_last_hb DATE;
l_last_load_time DATE;
l_comp_time DATE;
l_current_user MGMT_CREATED_USERS.user_name%TYPE := MGMT_USER.GET_CURRENT_EM_USER;

l_ret_date DATE;
BEGIN
    SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, 
                     MGMT_USER.OP_SET_IDENTIFIER);

    -- Get the "current" agent time, as defined by the heartbeat
    BEGIN
        SELECT t2.target_guid INTO l_agent_guid FROM
            MGMT_TARGETS t1, MGMT_TARGETS t2 WHERE
            t1.target_guid=p_target_guid AND
            t1.emd_url=t2.emd_url AND
            t2.target_type=MGMT_GLOBAL.G_AGENT_TARGET_TYPE;

        SELECT last_heartbeat_ts INTO l_last_hb FROM
             MGMT_EMD_PING p, MGMT_TARGETS t WHERE
                t.target_guid=p.target_guid AND
                t.target_guid=l_agent_guid;

        SELECT last_load_time INTO l_last_load_time FROM
            MGMT_TARGETS WHERE target_guid=p_target_guid;
           
        IF l_last_load_time IS NULL OR 
           l_last_load_time < (l_last_hb+(2/(60*24))) THEN
            l_comp_time := l_last_hb+(2/(60*24));
        ELSE
            l_comp_time := l_last_load_time;
        END IF;

        IF p_timestamp < l_comp_time THEN
            l_ret_date := l_comp_time;
        ELSE
            l_ret_date := p_timestamp;
        END IF;

        SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
        RETURN l_ret_date;
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
            RETURN p_timestamp;

        WHEN OTHERS THEN
            SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
            RAISE;
            
    END;

END;

-- Return the current time in the timezone of the specified target
FUNCTION current_agent_time(p_target_guid RAW) RETURN DATE IS
l_region MGMT_TARGETS.timezone_region%TYPE;
BEGIN
    BEGIN
        SELECT timezone_region INTO l_region FROM MGMT_TARGETS WHERE
             target_guid=p_target_guid;

        IF l_region IS NULL THEN
            RETURN SYSDATE;
        ELSE
            RETURN adjust_agent_time(MGMT_GLOBAL.sysdate_tzrgn(l_region),
                                     p_target_guid);
        END IF;
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            RETURN SYSDATE;
    END;
END;

FUNCTION current_agent_time_url(p_emd_url VARCHAR2) RETURN DATE IS
l_agent_guid MGMT_TARGETS.target_guid%TYPE;
l_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER;
l_agent_time DATE;
BEGIN
    -- Become super user, since the current user may not be
    -- able to see the agent
    SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, 
                     MGMT_USER.OP_SET_IDENTIFIER);

    SELECT target_guid INTO l_agent_guid FROM MGMT_TARGETS WHERE
        target_type=MGMT_GLOBAL.G_AGENT_TARGET_TYPE AND
        emd_url=p_emd_url;

    
    l_agent_time := current_agent_time(l_agent_guid);

    SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
    RETURN l_agent_time;

EXCEPTION
    -- Always revert back to being the same user as we entered
    WHEN OTHERS THEN
        IF l_current_user IS NOT NULL THEN
            SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
        END IF;

        RAISE;
END;
         
-- Convert the speecified timestamp to agent time
-- This returns the "adjusted" agent time: time adjusted
-- for clock skew. If p_unadjusted_time is non-null, the
-- unadjusted time is returned.
FUNCTION to_agent_time(p_blackout_guid RAW, 
                       p_target_guid RAW,
                       p_timestamp DATE,
                       p_unadjusted_time_out OUT DATE) RETURN DATE IS
l_timezone_info NUMBER;
l_tzoffset NUMBER;
l_target_tzoffset NUMBER;

l_rep_tzoffset_hours NUMBER;
l_rep_tzoffset_mins NUMBER;
l_rep_tzoffset NUMBER;

l_tzregion MGMT_BLACKOUT_SCHEDULE.timezone_region%TYPE;
l_target_region MGMT_TARGETS.timezone_region%TYPE;

l_ret_time DATE;
BEGIN

    -- Note: we use the current timezone offset of the agent
    -- since the blackout start times are specified using a
    -- specified timezone offset.
    SELECT timezone_info, timezone_offset, timezone_region INTO 
         l_timezone_info, l_tzoffset, l_tzregion FROM
      MGMT_BLACKOUT_SCHEDULE WHERE
         blackout_guid=p_blackout_guid;

    SELECT timezone_delta, timezone_region INTO 
           l_target_tzoffset, l_target_region FROM MGMT_TARGETS
        WHERE target_guid=p_target_guid;

    IF l_timezone_info=TIMEZONE_SPECIFIED THEN
        l_ret_time := p_timestamp -
                       (l_tzoffset-l_target_tzoffset)/(60*24);
    ELSIF l_timezone_info=TIMEZONE_RGN_SPECIFIED THEN
        l_ret_time := MGMT_GLOBAL.adjust_tz(p_timestamp,
                                            l_tzregion,
                                            l_target_region);
    ELSE
        -- timestamp was specified in repository timezone
        l_rep_tzoffset_hours := to_char(SYSTIMESTAMP, 'TZH');
        l_rep_tzoffset_mins := to_char(SYSTIMESTAMP, 'TZM');

        l_rep_tzoffset := l_rep_tzoffset_hours*60+l_rep_tzoffset_mins;

        l_ret_time := p_timestamp-
                         (l_rep_tzoffset-l_target_tzoffset)/(60*24);
    END IF;

    p_unadjusted_time_out := l_ret_time;

    RETURN adjust_agent_time(l_ret_time, p_target_guid);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_unadjusted_time_out := p_timestamp;
        RETURN p_timestamp;
END;

-- Overloaded to_agent_time
FUNCTION to_agent_time(p_blackout_guid RAW, 
                       p_target_guid RAW,
                       p_timestamp DATE) RETURN DATE IS
l_dummy DATE := null;
BEGIN
    RETURN to_agent_time(p_blackout_guid, p_target_guid, 
                         p_timestamp, l_dummy);
END;

-- Lock the blackout record with the specified guid; 
-- Return the current status. Return -1 if the record
-- could not be found
FUNCTION lock_blackout(p_blackout_guid RAW) RETURN NUMBER IS
l_blackout_status NUMBER;

l_bo_lock_handle VARCHAR2(256) ;
l_bo_lock_value VARCHAR2(256) ;
l_bo_lock_status NUMBER;

BEGIN
    -- We obtain a named lock to avoid deadlocks caused by multiple blackout
    -- windows being updated when processing windows for a single
    -- blackout
    l_bo_lock_handle :=
            MGMT_LOCK_UTIL.get_exclusive_lock(MGMT_LOCK_UTIL.BLACKOUT_GUID_LOCK,
                                          RAWTOHEX(p_blackout_guid),
                                          300,
                                          MGMT_GLOBAL.BLKOUT_CREATE_FAILED_ERR,
              'Could not lock blackout  ' || p_blackout_guid);

    SELECT blackout_status INTO l_blackout_status FROM MGMT_BLACKOUTS
        WHERE blackout_guid=p_blackout_guid;

    RETURN l_blackout_status;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- This normally should not happen. Log an error and go away
        log_error(BLACKOUT_NOT_EXIST,
           'lock_blackout: Blackout for GUID ' || p_blackout_guid ||
           ' does not exist');

        l_bo_lock_status :=
            MGMT_LOCK_UTIL.release_lock(l_bo_lock_handle) ;
        RETURN -1;
END;

-- Check that the user has OPERATOR privilege over
-- the specified target (and its derivatives, if
-- include_members is set to 1)
PROCEDURE check_operator_privs(p_target_name VARCHAR2,
                               p_target_type VARCHAR2)
IS
BEGIN
  -- This call will check if the user is a Super User so there is no
  -- need to explicitly make a call to check if the user has the super
  -- user privilege
  IF MGMT_USER.has_priv(MGMT_USER.get_current_em_user,
                        MGMT_USER.OPERATOR_TARGET,
                        p_target_name,
                        p_target_type) != MGMT_USER.USER_HAS_PRIV 
  THEN
    RAISE MGMT_GLOBAL.BLKOUT_NO_PRIVS_SEL_TGTS;
  END IF;
END;

-- Check that the current user has enough privileges to
-- create/modify/delete/stop the current blackout
-- If p_check_agent_bo is false, then a check to see whether
-- the blackout is an agent-side blackout is not made. In general,
-- most operations such as edit and stop cannot be applied to
-- agent-side blackouts. If p_check_agent_bo is true (the default),
-- then an error is raised if the blackout is an agent-side blackout.
PROCEDURE check_modify_privs(p_blackout_guid RAW,
                             p_check_agent_bo BOOLEAN DEFAULT TRUE) IS
l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user;

l_blk_tguids MGMT_USER_GUID_ARRAY;
l_priv_tguids MGMT_USER_GUID_ARRAY;
l_cursor TARGET_CURSOR;
l_agent_count NUMBER;
l_created_thru MGMT_BLACKOUTS.created_thru%TYPE;
l_no_priv_target_names MGMT_JOB_PARAMETER.scalar_value%TYPE;
l_invalid_target_count NUMBER := 0;
BEGIN
    -- CLI blackouts cannot be modified in any way
    IF p_check_agent_bo THEN
        SELECT created_thru INTO l_created_thru FROM MGMT_BLACKOUTS
            WHERE blackout_guid=p_blackout_guid;

        IF l_created_thru IS NOT NULL THEN
            raise_application_error(MGMT_GLOBAL.BLKOUT_STARTED_AT_AGENT_ERR,
             'Agent-side blackouts cannot be edited or stopped');
        END IF;
    END IF;

    -- None of the targets in the blackout can be agents; agents
    -- can never be directly blacked out
    SELECT COUNT(*) INTO l_agent_count FROM 
      MGMT_BLACKOUT_TARGET_DETAILS btd, MGMT_TARGETS t
           WHERE blackout_guid=p_blackout_guid AND
                 btd.target_guid=t.target_guid AND
                 t.target_type=MGMT_GLOBAL.G_AGENT_TARGET_TYPE;

    IF l_agent_count > 0 THEN
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
         'Agent targets cannot be directly blacked out');
    END IF;

    -- Superusers are all powerful
    IF MGMT_USER.HAS_PRIV(l_current_user, 
                          MGMT_USER.SUPER_USER) = MGMT_USER.USER_HAS_PRIV THEN
        RETURN;
    END IF;

    -- All other users, including the owner, need to have
    -- operator privileges over all top level targets in the target
    -- list. 
    SELECT target_guid BULK COLLECT INTO l_blk_tguids
    FROM   MGMT_BLACKOUT_TARGET_DETAILS
    WHERE  blackout_guid = p_blackout_guid;

    l_cursor := MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_blk_tguids);

    FETCH l_cursor BULK COLLECT INTO l_priv_tguids;
    CLOSE l_cursor;

    -- if we don't have operator privilege on all targets
    -- the user cannot do anything with the blackout
    IF l_priv_tguids IS NULL OR l_blk_tguids.COUNT != l_priv_tguids.COUNT THEN
       FOR crec IN (SELECT target_name
                    FROM   MGMT_BLACKOUT_TARGET_DETAILS bo, MGMT_TARGETS t
                    WHERE  bo.target_guid = t.target_guid
                    AND    bo.blackout_guid = p_blackout_guid
                    AND    (bo.target_guid NOT IN 
                             (SELECT * FROM TABLE (CAST(l_priv_tguids AS MGMT_USER_GUID_ARRAY))))
                   ) LOOP
          l_invalid_target_count := l_invalid_target_count + 1;
          IF l_invalid_target_count = 1 THEN
             l_no_priv_target_names := crec.target_name;
          ELSE
             l_no_priv_target_names := l_no_priv_target_names || ',' || crec.target_name;
          END IF;
       END LOOP;
       raise_application_error(MGMT_GLOBAL.BLKOUT_NO_PRIVS_SEL_TGTS_ERR,
                               'The current user (' || l_current_user || 
                               ') does not have sufficient privileges on these targets: ' ||
                               l_no_priv_target_names);
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        IF l_cursor%ISOPEN THEN
            CLOSE l_cursor;
        END IF;
        RAISE;
END;

-- Submit the job that processes stopping a blackout on one or
-- more targets
PROCEDURE submit_stop_blackout_job(p_blackout_guid RAW,
                                   p_emd_urls MGMT_JOB_VECTOR_PARAMS) IS
l_job_targets MGMT_JOB_TARGET_LIST;
l_job_params MGMT_JOB_PARAM_LIST;
l_job_name varchar2(64);
l_job_id RAW(16);
l_execution_id RAW(16);
l_schedule MGMT_JOB_SCHEDULE_RECORD;

BEGIN
    -- This has the same schedule as the blackout, but the start time
    -- is offset by the duration
    l_job_name := 'BLK_STOP_JOB' || SYS_GUID();

    l_job_targets := MGMT_JOB_TARGET_LIST();

    l_job_params := MGMT_JOB_PARAM_LIST();
    l_job_params.extend(2);
    
    l_job_params(1) := MGMT_JOB_PARAM_RECORD('blackout_guid', 1, 
                                             p_blackout_guid, 
                                             null);
    -- Set up the emd urls as a vector parameter
    l_job_params(2) := MGMT_JOB_PARAM_RECORD('emd_list', 0, null, p_emd_urls);

    -- The job has an immediate schedule
    l_schedule :=  MGMT_JOBS.get_job_schedule_record(MGMT_JOBS.ONE_TIME_FREQUENCY_CODE,
                                            SYSDATE, null, 0, 0, 0, null, null,
                                            MGMT_JOBS.TIMEZONE_REPOSITORY, 
                                            0, 0, null);

    MGMT_JOBS.submit_job(l_job_name,
                         'Blackout stop job for ' || p_blackout_guid,
                         'StopBlackoutJob',
                         l_job_targets,
                         l_job_params,
                         l_schedule,
                         l_job_id,
                         l_execution_id,
                         null,
                         MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY);

END;


-- Submit the job that processes the edit of a blackout.
-- Returns the job id and execution id
PROCEDURE submit_edit_blackout_job(p_blackout_guid RAW,
                                   p_job_id_out OUT RAW,
                                   p_execution_id_out OUT RAW) IS
l_job_targets MGMT_JOB_TARGET_LIST;
l_job_params MGMT_JOB_PARAM_LIST;
l_job_name varchar2(64);
l_schedule MGMT_JOB_SCHEDULE_RECORD;
BEGIN
    -- This has the same schedule as the blackout, but the start time
    -- is offset by the duration
    l_job_name := 'BLK_EDIT_JOB' || SYS_GUID();

    l_job_targets := MGMT_JOB_TARGET_LIST();

    l_job_params := MGMT_JOB_PARAM_LIST();
    l_job_params.extend(1);
    
    l_job_params(1) := MGMT_JOB_PARAM_RECORD('blackout_guid', 1, 
                                             p_blackout_guid, 
                                             null);


    -- The job has an immediate schedule
    l_schedule :=  MGMT_JOBS.get_job_schedule_record(MGMT_JOBS.ONE_TIME_FREQUENCY_CODE,
                                            SYSDATE, null, 0, 0, 0, null, null,
                                            MGMT_JOBS.TIMEZONE_REPOSITORY, 
                                            0, 0, null);

    MGMT_JOBS.submit_job(l_job_name,
                         'Blackout edit job for ' || p_blackout_guid,
                         'EditBlackoutJob',
                         l_job_targets,
                         l_job_params,
                         l_schedule,
                         p_job_id_out,
                         p_execution_id_out,
                         null,
                         MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY);

END;

-- Submit the job that processes the start of a blackout.
-- Returns the id of the submitted job
-- If p_reschedule is true, the job is being resheduled; so
-- do not adjust the start time
FUNCTION submit_start_blackout_job(p_blackout_guid RAW,
                                   p_blackout_name VARCHAR2,
                                   p_duration VARCHAR2,
                                   p_schedule IN OUT MGMT_BLACKOUT_SCHEDULE_RECORD,
                                   p_reschedule BOOLEAN,
                                   p_execution_id_out OUT RAW)
    RETURN RAW IS
l_job_targets MGMT_JOB_TARGET_LIST;
l_job_params MGMT_JOB_PARAM_LIST;
l_job_name varchar2(64);
l_job_id RAW(16);
l_schedule MGMT_JOB_SCHEDULE_RECORD;
l_start_time_rep DATE;

BEGIN
    -- First, submit a job to process the start of the blackout. 
    -- This has the same schedule as the blackout
    l_job_name := 'BLK_START_JOB' || SYS_GUID();

    l_job_targets := MGMT_JOB_TARGET_LIST();

    l_job_params := MGMT_JOB_PARAM_LIST();
    l_job_params.extend(3);
    
    l_job_params(1) := MGMT_JOB_PARAM_RECORD('blackout_guid', 1, 
                                             p_blackout_guid, 
                                             null);
    l_job_params(2) := MGMT_JOB_PARAM_RECORD('blackout_name', 1, 
                                             p_blackout_name, 
                                             null);
    l_job_params(3) := MGMT_JOB_PARAM_RECORD('blackout_duration', 1, 
                                             p_duration,
                                             null);

    -- This works around the fact that we don't support an immediate
    -- schedule. If the start time is in the past, start now.
    IF NOT p_reschedule AND
       p_schedule.frequency_code != MGMT_JOBS.ONE_TIME_FREQUENCY_CODE THEN
        l_start_time_rep := to_rep_time(p_schedule.start_time,
                                        p_schedule.timezone_info,
                                        p_schedule.timezone_offset,
                                        p_schedule.timezone_region);

        -- Ensure that the start time, in repository timezone,
        -- is not in the past
        IF l_start_time_rep <= SYSDATE THEN
            l_start_time_rep := SYSDATE + (1/(60*24));

            p_schedule.start_time := to_blackout_time(l_start_time_rep,
                                                      p_schedule.timezone_info,
                                                      p_schedule.timezone_offset,
                                                      p_schedule.timezone_region);

            IF p_schedule.frequency_code NOT IN
                (MGMT_JOBS.ONE_TIME_FREQUENCY_CODE,
                 MGMT_JOBS.INTERVAL_FREQUENCY_CODE) THEN

                -- Adjust the execution time by a minute as well
                p_schedule.execution_minutes := to_char(l_start_time_rep, 'mi');
                p_schedule.execution_hours := to_char(l_start_time_rep, 'HH24');

            END IF;
        END IF;
    END IF;

    -- Set up the schedule for the job. The job schedule follows the
    -- blackout schedule
    l_schedule := MGMT_JOBS.get_job_schedule_record(p_schedule.frequency_code,
                                           p_schedule.start_time, 
                                           p_schedule.end_time,
                                           p_schedule.execution_hours,
                                           p_schedule.execution_minutes,
                                           p_schedule.interval,
                                           p_schedule.months,
                                           p_schedule.days,
                                           p_schedule.timezone_info,
                                           -1, p_schedule.timezone_offset,
                                           p_schedule.timezone_region);

    IF l_schedule.frequency_code = ONE_TIME_FREQUENCY_CODE THEN
        l_schedule.end_time := null;
    END IF;

    MGMT_JOBS.submit_job(l_job_name,
                         'This is a Blackout start job',
                         'StartBlackoutJob',
                         l_job_targets,
                         l_job_params,
                         l_schedule,
                         l_job_id,
                         p_execution_id_out,
                         null,
                         MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY);

    RETURN l_job_id;

END;


-- Submit a "discard state" job for the specified emd and
-- set of targets
PROCEDURE submit_discard_state_job(p_emd_url VARCHAR2,
                                   p_target_names MGMT_JOB_VECTOR_PARAMS,
                                   p_target_types MGMT_JOB_VECTOR_PARAMS,
                                   p_stop_time DATE) IS
BEGIN

  submit_discard_state_job(p_emd_url, p_target_names, p_target_types, p_stop_time, 'N');

END submit_discard_state_job;

-- Submit a "discard state" job for the specified emd and
-- set of targets
PROCEDURE submit_discard_state_job(p_emd_url VARCHAR2,
                                   p_target_names MGMT_JOB_VECTOR_PARAMS,
                                   p_target_types MGMT_JOB_VECTOR_PARAMS,
                                   p_stop_time DATE,
                                   p_is_proxy VARCHAR2,
                                   p_job_name_prefix VARCHAR2 DEFAULT 'BLK_DISCST_JOB' ) IS
l_job_targets MGMT_JOB_TARGET_LIST;
l_job_params MGMT_JOB_PARAM_LIST;
l_job_name varchar2(64);
l_job_id RAW(16);
l_execution_id RAW(16);
l_schedule MGMT_JOB_SCHEDULE_RECORD;
l_stop_time DATE;
l_jobs MGMT_USER_GUID_ARRAY;
l_target_names MGMT_JOB_VECTOR_PARAMS;
l_target_types MGMT_JOB_VECTOR_PARAMS;
l_submit_target_names MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
l_submit_target_types MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
l_target_job_exist BOOLEAN;
z NUMBER := 0;

BEGIN
    -- Do not submit jobs for repository targets
    IF p_emd_url IS NULL OR LENGTH(p_emd_url)=0 THEN
        RETURN;
    END IF;

    IF p_stop_time IS NULL THEN
        l_stop_time := current_agent_time_url(p_emd_url);
    ELSE
        l_stop_time := p_stop_time;
    END IF;

    -- Adding logic from basic_loader_pkgbody.sql which was added
    -- by transaction neearora_bug-4607971

    BEGIN
      SELECT j.job_id BULK COLLECT
        INTO l_jobs
        FROM mgmt_job j, mgmt_job_parameter jp
       WHERE j.job_type = 'DiscardStateJob'
         AND j.job_id = jp.job_id
         AND jp.parameter_name = 'emdUrl'
         AND jp.scalar_value = p_emd_url
         AND j.job_status NOT IN (MGMT_JOBS.JOB_STATUS_EXPIRED,
                                  MGMT_JOBS.JOB_STATUS_DELETE_PENDING);
 
      IF (l_jobs IS NOT NULL AND l_jobs.COUNT > 0) THEN
        FOR j IN 1 .. p_target_names.COUNT
        LOOP
          l_target_job_exist := FALSE;
          FOR i IN 1 .. l_jobs.COUNT
          LOOP
            SELECT VECTOR_VALUE
              INTO l_target_names
              FROM mgmt_job_parameter
             WHERE job_id = l_jobs(i)
               AND parameter_name = 'targetNames'
               AND execution_id != MGMT_JOB_ENGINE.NO_EXECUTION
               AND ROWNUM < 2;

            SELECT VECTOR_VALUE
              INTO l_target_types
              FROM mgmt_job_parameter
             WHERE job_id = l_jobs(i)
               AND parameter_name = 'targetTypes'
               AND execution_id != MGMT_JOB_ENGINE.NO_EXECUTION
               AND ROWNUM < 2;

            IF (l_target_names IS NOT NULL AND l_target_names.COUNT > 0) THEN
              FOR k IN 1 .. l_target_names.COUNT
              LOOP
                IF(p_target_names(j) = l_target_names(k) AND
                   p_target_types(j) = l_target_types(k)) THEN
                   l_target_job_exist := TRUE;
                   exit;
                END IF;
              END LOOP;
              IF(l_target_job_exist) THEN
                exit;
              END IF;
            END IF;
          END LOOP;
          IF NOT (l_target_job_exist) THEN
            z := z + 1 ;

            l_submit_target_names.extend();
            l_submit_target_names(z) := p_target_names(j);
            l_submit_target_types.extend();
            l_submit_target_types(z) := p_target_types(j);
          END IF;
        END LOOP;
      ELSE
       l_submit_target_names := p_target_names;
       l_submit_target_types := p_target_types;
      END IF;
    EXCEPTION WHEN
      NO_DATA_FOUND THEN
       l_submit_target_names := p_target_names;
       l_submit_target_types := p_target_types;
    END;

    IF(l_submit_target_names IS NOT NULL AND l_submit_target_names.COUNT > 0)
    THEN
      -- Continue to submit a job since there are targets that need
      -- to have the discard state job submitted.

      -- This is an IMMEDIATE job
      l_job_name := NVL(p_job_name_prefix, 'BLK_DISCST_JOB') || SYS_GUID();

      l_job_targets := MGMT_JOB_TARGET_LIST();

      l_job_params := MGMT_JOB_PARAM_LIST();
      l_job_params.extend(5);
    
      l_job_params(1) := MGMT_JOB_PARAM_RECORD('emdUrl', 1, p_emd_url, null);
      l_job_params(2) := MGMT_JOB_PARAM_RECORD('targetNames', 0, null,
                                               l_submit_target_names);
      l_job_params(3) := MGMT_JOB_PARAM_RECORD('targetTypes', 0, null,
                                               l_submit_target_types);
      l_job_params(4) := MGMT_JOB_PARAM_RECORD('timestamp', 1, 
                                               to_char(l_stop_time, 
                                                       TIMESTAMP_FORMAT),
                                               null);
      l_job_params(5) := MGMT_JOB_PARAM_RECORD('proxyTarget', 1, p_is_proxy, null);


      -- Set up the schedule for the job. The job schedule is an 
      -- immediate schedule 
      l_schedule := MGMT_JOBS.get_job_schedule_record(MGMT_JOBS.ONE_TIME_FREQUENCY_CODE, 
                                             SYSDATE,
                                             null, 0, 0, 0, null, 
                                             null, 
                                             MGMT_JOBS.TIMEZONE_REPOSITORY, 
                                             0, 0, null);

      MGMT_JOBS.submit_job(l_job_name,
                           'This is a discard state job',
                           'DiscardStateJob',
                           l_job_targets,
                           l_job_params,
                           l_schedule,
                           l_job_id,
                           l_execution_id,
                           null,
                           MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY);
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        -- Log an error and move on. This should not hold up the 
        -- rest of the processing of the blackout
        log_error(DISCARD_STATE_ERROR, 'Could not submit discard state job for URL ' || p_emd_url || ':' || SQLERRM);
END;

-- Submit a discardState job for all targets in a blackout
-- with the specified emd url
PROCEDURE submit_discard_state_job(p_blackout_guid RAW,
                                   p_emd_url VARCHAR2,
                                   p_stop_time DATE) IS
l_target_names MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
l_target_types MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();

i NUMBER := 1;
BEGIN
    FOR crec IN (SELECT target_name, target_type FROM 
                    MGMT_TARGETS t, MGMT_BLACKOUT_FLAT_TARGETS ft WHERE
                         t.target_guid=ft.target_guid AND
                         ft.blackout_guid=p_blackout_guid AND
                         t.emd_url=p_emd_url) LOOP
        l_target_names.extend(1);
        l_target_types.extend(1);

        l_target_names(i) := crec.target_name;
        l_target_types(i) := crec.target_type;

        i := i+1;
    END LOOP;

    submit_discard_state_job(p_emd_url, l_target_names,
                             l_target_types, p_stop_time);
END;

-- Cancel the job specified by the job id.
PROCEDURE cancel_job(p_job_id RAW) IS
l_job_name MGMT_JOB.job_name%TYPE;
l_job_owner MGMT_JOB.job_name%TYPE;
l_current_user MGMT_CREATED_USERS.user_name%TYPE := MGMT_USER.GET_CURRENT_EM_USER;
BEGIN
    
    -- Become the super user
    SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, 
                     MGMT_USER.OP_SET_IDENTIFIER);

    SELECT job_name, job_owner INTO l_job_name, l_job_owner 
    FROM   MGMT_JOB
    WHERE  job_id=p_job_id;

    MGMT_JOBS.stop_all_executions(l_job_name, l_job_owner);

    -- Revert to the current user
    SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
      SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
    WHEN OTHERS THEN
      SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
      RAISE;
END;

-- Submit the job that processes the end of a blackout.
FUNCTION submit_end_blackout_job(p_blackout_guid RAW,
                                 p_cancel_current BOOLEAN DEFAULT FALSE) 
        RETURN RAW IS

l_job_targets MGMT_JOB_TARGET_LIST;
l_job_params MGMT_JOB_PARAM_LIST;
l_job_name varchar2(64);
l_job_id RAW(16);
l_execution_id RAW(16);
l_schedule MGMT_JOB_SCHEDULE_RECORD;
l_start_time DATE;
l_end_time DATE;
l_duration NUMBER;
l_current_job_id MGMT_BLACKOUTS.end_job_id%TYPE;

BEGIN
    -- Figure out the END time of the blackout
    SELECT scheduled_time, duration, end_job_id INTO 
           l_start_time, l_duration, l_current_job_id
        FROM MGMT_BLACKOUT_SCHEDULE s, MGMT_BLACKOUTS b
            WHERE s.blackout_guid=p_blackout_guid AND
                  b.blackout_guid=s.blackout_guid;

    IF l_duration <= 0 THEN
        RETURN null;
    END IF;

    IF p_cancel_current AND l_current_job_id IS NOT NULL THEN
        cancel_job(l_current_job_id);
    END IF;


    l_end_time := l_start_time+(l_duration/(24*60));
            
    -- The end time is in blackout time, convert to repository time
    l_end_time := to_rep_time(p_blackout_guid, l_end_time);

    -- This is an IMMEDIATE job whose start time 
    -- is offset by the duration
    l_job_name := 'BLK_END_JOB' || SYS_GUID();

    l_job_targets := MGMT_JOB_TARGET_LIST();

    l_job_params := MGMT_JOB_PARAM_LIST();
    l_job_params.extend(1);
    
    l_job_params(1) := MGMT_JOB_PARAM_RECORD('blackout_guid', 1, 
                                             p_blackout_guid, 
                                             null);


    -- Set up the schedule for the job. The job schedule is an 
    -- immediate schedule that starts at (start_time+duration)
    l_schedule := MGMT_JOBS.get_job_schedule_record(MGMT_JOBS.ONE_TIME_FREQUENCY_CODE, 
                                           l_end_time,
                                           null, 0, 0, 0, null, 
                                           null, 
                                           MGMT_JOBS.TIMEZONE_REPOSITORY, 
                                           0, 0, null);

    MGMT_JOBS.submit_job(l_job_name,
                         'This is a Blackout end job',
                         'EndBlackoutJob',
                         l_job_targets,
                         l_job_params,
                         l_schedule,
                         l_job_id,
                         l_execution_id,
                         null,
                         MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY);

    RETURN l_job_id;
END;

-- 
-- Submit start and (if applicable) end blackout jobs for the specified
-- blackout guid. The start job has the same schedule as the blackout.
-- The end job has a start time of (blackout_start_time + duration)
-- If the blackout is indefinite, there is no end job.
--
FUNCTION schedule_blackout(p_blackout_guid RAW,
                           p_blackout_name VARCHAR2,
                           p_reschedule BOOLEAN,
                           p_schedule IN OUT MGMT_BLACKOUT_SCHEDULE_RECORD,
                           p_duration_source_out OUT NUMBER,
                           p_execution_id_out OUT RAW)
           RETURN RAW IS
l_duration NUMBER := -1;
l_start_job_id RAW(16);

BEGIN
    p_duration_source_out := DURATION_SOURCE_ENDTIME;

    IF p_schedule.frequency_code = ONE_TIME_FREQUENCY_CODE THEN
        IF p_schedule.end_time IS NOT NULL THEN
            l_duration := (p_schedule.end_time-p_schedule.start_time)*60*24;
            p_schedule.duration := l_duration;
            p_duration_source_out := DURATION_SOURCE_ENDTIME;
        ELSIF p_schedule.duration > 0 THEN
            l_duration := p_schedule.duration;
            IF p_schedule.end_time IS NULL THEN
                p_schedule.end_time := 
                     p_schedule.start_time+(l_duration/(60*24));
            ELSIF (p_schedule.end_time-p_schedule.start_time)*60*24 != 
                  p_schedule.duration THEN
                raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR,
                    'The difference between the end time and the start time must be equal to the duration');
            END IF;

            p_duration_source_out := DURATION_SOURCE_DURATION;
        ELSE
            -- Indefinite blackout
            l_duration := -1;
            p_schedule.duration := -1;
            p_duration_source_out := DURATION_SOURCE_INDEFINITE;
        END IF;
    ELSIF p_schedule.frequency_code = INTERVAL_FREQUENCY_CODE THEN
        -- Validate that the difference between the frequency and the
        -- duration is at lease 5 minutes
        IF p_schedule.interval-p_schedule.duration < MIN_DIFFERENCE THEN
            raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR,
             'The difference between the interval and the duration must be at least ' || MIN_DIFFERENCE || ' minutes');
        END IF;
    END IF;

    -- The duration can never be zero. It is either -1 (for indefinite
    -- blackouts) or positive (for duration-based blackouts
    IF p_schedule.duration = 0 THEN
        raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR,
            'The duration must be -1 (for indefinite blackouts) or positive');
    END IF;


    IF p_schedule.duration > 0 AND 
       p_schedule.duration < MIN_DURATION THEN
        raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR,
         'The duration must be at least ' || MIN_DURATION || ' minutes');
    END IF;

    -- Submit a job to start the blackout
    l_start_job_id := submit_start_blackout_job(p_blackout_guid, 
                                                p_blackout_name, l_duration,
                                                p_schedule,
                                                p_reschedule,
                                                p_execution_id_out);

    RETURN l_start_job_id;

END;

-- Compute and insert the flat target list for the blackout
-- If p_delete_old is true, then the old list is deleted
-- and we start afresh
-- If p_use_current_time is true, then the current time is
-- used as the start time
-- If p_first_time is true, then we are scheduling the blackout
-- for the very first time
PROCEDURE compute_flat_target_list(p_blackout_guid RAW,
                                   p_delete_old BOOLEAN, 
                                   p_use_current_time BOOLEAN DEFAULT false,
                                   p_first_time BOOLEAN DEFAULT false) IS

l_scheduled_time DATE;
l_start_time DATE;
l_end_time DATE;
l_agent_start_time DATE;
l_agent_end_time DATE;

l_start_time_utc DATE;
l_end_time_utc DATE;

l_duration NUMBER;
l_tzoffset NUMBER;
l_blackout_status NUMBER;
l_count NUMBER;
l_job_flag NUMBER;

l_occurrence_number NUMBER;
l_timezone_region MGMT_BLACKOUT_SCHEDULE.timezone_region%TYPE;

l_unadj_agent_start_time DATE;
l_unadj_agent_end_time DATE;
l_clock_skew NUMBER;

l_all_target_guids MGMT_USER_GUID_ARRAY;
l_flat_target_guids MGMT_USER_GUID_ARRAY;
l_blk_owner MGMT_BLACKOUTS.created_by%TYPE := NULL;

l_cursor TARGET_CURSOR;
l_duration_source MGMT_BLACKOUT_SCHEDULE.duration_source%TYPE;
BEGIN
    SELECT scheduled_time, duration, timezone_offset, timezone_region,
           blackout_status, job_flag, occurrence_number, created_by,
           duration_source INTO 
         l_scheduled_time, l_duration, l_tzoffset, l_timezone_region,
         l_blackout_status, l_job_flag, l_occurrence_number, l_blk_owner,
         l_duration_source
    FROM
        MGMT_BLACKOUTS b, MGMT_BLACKOUT_SCHEDULE s
    WHERE 
         b.blackout_guid=p_blackout_guid AND
         b.blackout_guid=s.blackout_guid;

    IF l_duration < 0 THEN
        l_end_time := null;
    ELSE
        l_end_time := l_scheduled_time + (l_duration/(24*60));
    END IF;


    IF p_use_current_time THEN
        l_start_time := sysdate_blackout_time(p_blackout_guid);
    ELSE
        l_start_time := l_scheduled_time;
    END IF;

    l_start_time_utc := to_utc(p_blackout_guid, l_start_time);
    l_end_time_utc := to_utc(p_blackout_guid, l_end_time);

    IF p_delete_old THEN
        -- Delete all blackout windows that were from
        -- the current blackout
        DELETE FROM MGMT_BLACKOUT_WINDOWS WHERE 
            target_guid IN (SELECT target_guid FROM 
                               MGMT_BLACKOUT_FLAT_TARGETS WHERE 
                               blackout_guid=p_blackout_guid) AND
            blackout_guid=p_blackout_guid AND
            occurrence_number=l_occurrence_number AND
            status NOT IN (BLK_STATE_STOPPED, BLK_STATE_ENDED);
            
        DELETE FROM MGMT_BLACKOUT_FLAT_TARGETS WHERE 
            blackout_guid=p_blackout_guid;

        UPDATE MGMT_BLACKOUT_TARGET_DETAILS SET edit_state=ADD_PENDING
            WHERE blackout_guid=p_blackout_guid;
    END IF;

    SELECT target_guid BULK COLLECT INTO l_all_target_guids FROM ( 
        SELECT target_guid FROM MGMT_BLACKOUT_TARGET_DETAILS WHERE
              blackout_guid=p_blackout_guid AND
              edit_state=ADD_PENDING
        UNION
        SELECT assoc_target_guid AS TARGET_GUID 
        FROM   MGMT_FLAT_TARGET_ASSOC m, MGMT_TARGETS t
        WHERE  t.target_guid=m.assoc_target_guid
        AND    m.is_membership = 1 
        AND    t.target_type != MGMT_GLOBAL.G_AGENT_TARGET_TYPE 
        AND    m.source_target_guid IN
           (SELECT target_guid FROM MGMT_BLACKOUT_TARGET_DETAILS
            WHERE  blackout_guid=p_blackout_guid 
            AND    include_members=1 
            AND    edit_state IN (ADD_PENDING, INCLUDE_ADD_PENDING)
           ) 
        UNION
        SELECT target_guid AS TARGET_GUID FROM 
            MGMT_TARGETS tgt WHERE emd_url IN 
            (SELECT emd_url FROM MGMT_TARGETS t,
               MGMT_BLACKOUT_TARGET_DETAILS btd WHERE
                 btd.blackout_guid=p_blackout_guid AND
                 btd.include_members=1 AND
                 btd.edit_state IN (ADD_PENDING, INCLUDE_ADD_PENDING) AND
                 btd.target_guid=t.target_guid AND
                 t.target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE) 
    );

    -- Superusers have access to all targets.
    -- For non-superusers, pick targets with OPERATOR only.
    IF MGMT_USER.HAS_PRIV(l_blk_owner, 
                          MGMT_USER.SUPER_USER) = MGMT_USER.USER_HAS_PRIV THEN
        l_flat_target_guids := l_all_target_guids;
    ELSE
        l_cursor := MGMT_USER.HAS_PRIV(l_blk_owner, 
                                       MGMT_USER.OPERATOR_TARGET, 
                                       l_all_target_guids);

        FETCH l_cursor BULK COLLECT INTO l_flat_target_guids;
        CLOSE l_cursor;
    END IF;

    FOR i IN 1..l_flat_target_guids.COUNT LOOP        
        -- Insert a record in the flat targets table for each
        BEGIN
            -- get both the adjusted as well as unadjusted agent
            -- times. The difference is essentially the clock skew.
            l_agent_start_time := to_agent_time(p_blackout_guid, 
                                                l_flat_target_guids(i),
                                                l_start_time,
                                                l_unadj_agent_start_time);

            IF l_agent_start_time > l_unadj_agent_start_time THEN
                l_clock_skew := l_agent_start_time-l_unadj_agent_start_time;
            ELSE
                l_clock_skew := 0;
            END IF;

            IF l_duration > 0 THEN
                -- For one-time blackouts with a duration, make sure that
                -- we honour the duration
                IF p_first_time AND 
                    l_duration_source=DURATION_SOURCE_DURATION THEN
                    l_agent_end_time := l_agent_start_time +
                        l_duration/(60*24);
                ELSE
                    l_agent_end_time := to_agent_time(p_blackout_guid, 
                                                  l_flat_target_guids(i),
                                                  l_end_time,
                                                  l_unadj_agent_end_time);
                END IF;

                -- If there is a clock skew, add the clock skew to
                -- the unadjusted end time. This is because if the clock
                -- skew is sufficiently high, the adjusted start and end
                -- times could end up being the same!
                IF l_clock_skew > 0 AND 
                   l_agent_end_time != l_unadj_agent_end_time THEN
                    l_agent_end_time := l_unadj_agent_end_time+l_clock_skew;
                END IF;

                IF l_agent_end_time <= l_agent_start_time THEN
                    l_agent_end_time := l_agent_start_time+(1/(60*24*60));
                END IF;
            ELSE
                l_agent_end_time := null;
            END IF;


            INSERT INTO MGMT_BLACKOUT_FLAT_TARGETS(blackout_guid,
               target_guid, host_blackout, job_status) VALUES
             (p_blackout_guid, l_flat_target_guids(i), 1, 
                 BLK_JSTATE_START_PROCESSING);

        EXCEPTION
            -- When editing a blackout, we could end up attempting to add
            -- an existing target. These we ignore. Note: since the target 
            -- is already added, we need to preserve its current state
            WHEN DUP_VAL_ON_INDEX THEN
                NULL;
        END;

        DELETE  FROM MGMT_BLACKOUT_WINDOWS
        WHERE   blackout_guid=p_blackout_guid
        AND     target_guid=l_flat_target_guids(i)
        AND     start_time=l_agent_start_time;

        -- Insert/update the blackout window entry
        BEGIN
        INSERT INTO MGMT_BLACKOUT_WINDOWS(blackout_guid, 
                  occurrence_number, target_guid,
                  start_time, end_time) VALUES
            (p_blackout_guid, l_occurrence_number, l_flat_target_guids(i),
             l_agent_start_time, l_agent_end_time);
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                UPDATE  MGMT_BLACKOUT_WINDOWS
                SET     occurrence_number=l_occurrence_number,
                        end_time=l_agent_end_time,
                        status=BLK_STATE_START_PROCESSING
                WHERE   blackout_guid=p_blackout_guid
                AND     target_guid=l_flat_target_guids(i)
                AND     start_time=l_agent_start_time;
        END;
    END LOOP;

    -- Callback to the job system, inform it about the new windows
    MGMT_JOB_ENGINE.blackout_window_started(p_blackout_guid,
                                            l_job_flag,
                                            l_start_time_utc,
                                            l_end_time_utc);

EXCEPTION
    WHEN OTHERS THEN
        IF l_cursor%ISOPEN THEN
            CLOSE l_cursor;
        END IF;
        RAISE;
END;

-- Process the start of the blackout on the specified target.
-- If p_use_current_time is false, the actual blackout start
-- time is used. Otherwise, the current time is used in 
-- generating blackout severities.
PROCEDURE process_start_blk_on_target(p_blackout_guid RAW,
                                      p_occurrence_number NUMBER,
                                      p_target_guid RAW,
                                      p_use_current_time BOOLEAN DEFAULT false) IS
l_start_time DATE;
l_end_time DATE;
l_current_blackout_status NUMBER;
l_err_msg VARCHAR2(10000);
l_target_name mgmt_targets.target_name%type ;
l_target_type mgmt_targets.target_name%type ;
BEGIN
    IF p_use_current_time THEN
        l_start_time := current_agent_time(p_target_guid);
        SELECT end_time INTO l_end_time
        FROM   MGMT_BLACKOUT_WINDOWS
        WHERE  blackout_guid=p_blackout_guid AND
               target_guid=p_target_guid AND
               occurrence_number=p_occurrence_number AND
               status NOT IN (BLK_STATE_STOPPED, BLK_STATE_ENDED);
    ELSE
        -- Always select the start time from the blackout window
        SELECT start_time, end_time INTO 
               l_start_time, l_end_time 
        FROM   MGMT_BLACKOUT_WINDOWS
        WHERE  blackout_guid=p_blackout_guid AND
               target_guid=p_target_guid AND
               occurrence_number=p_occurrence_number AND
               status NOT IN (BLK_STATE_STOPPED, BLK_STATE_ENDED);
    END IF;

    SELECT blackout_status INTO l_current_blackout_status FROM
        MGMT_BLACKOUT_FLAT_TARGETS WHERE 
            blackout_guid=p_blackout_guid AND
            target_guid=p_target_guid;

    IF l_current_blackout_status != IN_BLACKOUT THEN
    
        BEGIN
            INSERT INTO MGMT_BLACKOUT_STATE(blackout_guid, target_guid,
                   collection_timestamp, blackout_code, target_status) VALUES
             (p_blackout_guid, p_target_guid, 
              l_start_time,
              IN_BLACKOUT, 
              IN_BLACKOUT);

        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                INSERT INTO MGMT_BLACKOUT_STATE(blackout_guid, target_guid,
               collection_timestamp, blackout_code) VALUES
           (p_blackout_guid, p_target_guid, l_start_time+(1/(24*60*60)), IN_BLACKOUT);

            -- Note: when starting a repository blackout, we should never
            -- get a duplicate record exception
            WHEN MGMT_GLOBAL.duplicate_record THEN
                log_error(DUPLICATE_RECORDS_INSERT,
                  'Duplicate start blackout record for ' || p_blackout_guid ||
                  ' for target ' || p_target_guid);

            WHEN MGMT_GLOBAL.internal_severity THEN
                log_error(DUPLICATE_RECORDS_INSERT,
                  'Internal severity error for blackout ' || p_blackout_guid ||
                  ' for target ' || p_target_guid || ':' || SQLERRM);

                -- Reinsert after adjusting the time. This error usually 
                -- indicates that a severity was inserted at a time
                -- later than our start time
                l_start_time := adjust_agent_time(l_start_time, p_target_guid);
                INSERT INTO MGMT_BLACKOUT_STATE(blackout_guid, target_guid,
                   collection_timestamp, blackout_code, target_status) VALUES
                 (p_blackout_guid, p_target_guid, 
                  l_start_time,
                  IN_BLACKOUT, 
                  IN_BLACKOUT);
        END;

        -- If there are any repository collections for this target,
        -- suspend them
        BEGIN
            SELECT  target_name, target_type
             INTO   l_target_name, l_target_type
             FROM   MGMT_TARGETS
             WHERE  target_guid = p_target_guid ;

            MGMT_COLLECTION.suspend_collection(p_target_name=>l_target_name,
                                               p_target_type=>l_target_type) ;
        EXCEPTION
            WHEN OTHERS THEN
                log_error(BLACKOUT_NOT_STOPPED, 'Cannot suspend rep collections for blackout ' || p_blackout_guid || ' on target ' || p_target_guid || ':' || SQLERRM);
        END;

    END IF;

EXCEPTION
    WHEN OTHERS THEN
        -- Propogate deadlocks
        IF SQLCODE=-60 THEN
            RAISE;
        END IF;

        -- If we received an unhandled exception here, do not
        -- penalize the entire blackout, but fail the blackout
        -- on this target. The overall status of the blackout will
        -- go to PARTIAL
        l_err_msg := SQLERRM;
        UPDATE  MGMT_BLACKOUT_FLAT_TARGETS
        SET     blackout_status=0,
                job_status=BLK_JSTATE_START_PROCESSING,
                error_message='Error starting blackout (start time:' ||
                to_char(l_start_time, 'mm/dd/yy hh:mi:ss pm') ||
                '): ' || l_err_msg
        WHERE   blackout_guid=p_blackout_guid
        AND     target_guid=p_target_guid;

        -- Remove the blackout window since we were no longer able to
        -- put the target under blackout
        DELETE  FROM MGMT_BLACKOUT_WINDOWS
        WHERE   blackout_guid=p_blackout_guid
        AND     target_guid=p_target_guid
        AND     occurrence_number=p_occurrence_number
        AND     status NOT IN (BLK_STATE_STOPPED, BLK_STATE_ENDED);
END;


-- Process the end of the blackout on the specified target
-- If p_stop_ts is not null, then use that to update the 
-- blackout records. This would be used only when all the
-- targets are off the same agent.
-- ELSE If p_use_current_time is TRUE, then use the "current"
-- time (suitably converted to agent timezone) to update
-- the blackout records
PROCEDURE process_end_blk_on_target(p_blackout_guid RAW,
                                    p_occurrence_number NUMBER,
                                    p_target_guid RAW,
                                    p_use_current_time BOOLEAN,
                                    p_stop_ts DATE) IS
l_target_status NUMBER;

l_start_time DATE;
l_duration NUMBER;
l_end_time DATE;
l_indefinite_blackout BOOLEAN := false;
l_current_blackout_status NUMBER;
l_current_job_status NUMBER;

l_status NUMBER;

l_err_msg VARCHAR2(10000);
l_target_name mgmt_targets.target_name%type ;
l_target_type mgmt_targets.target_name%type ;
BEGIN
    SELECT  blackout_status, job_status
    INTO    l_current_blackout_status, l_current_job_status
    FROM    MGMT_BLACKOUT_FLAT_TARGETS 
    WHERE   blackout_guid=p_blackout_guid 
    AND     target_guid=p_target_guid;

    -- Do not proceed further if the target is already
    -- out of blackout
    IF l_current_blackout_status != IN_BLACKOUT THEN
        UPDATE MGMT_BLACKOUT_FLAT_TARGETS 
        SET    job_status=BLK_JSTATE_STOPPED
        WHERE  blackout_guid=p_blackout_guid
        AND    target_guid=p_target_guid;

        IF l_current_job_status=BLK_JSTATE_START_PROCESSING THEN
            -- The blackout never started for this target
            DELETE  FROM MGMT_BLACKOUT_WINDOWS
            WHERE   blackout_guid=p_blackout_guid
            AND     target_guid=p_target_guid
            AND     occurrence_number=p_occurrence_number
            AND     status NOT IN (BLK_STATE_STOPPED, BLK_STATE_ENDED);
        END IF;

        RETURN;
    END IF;

    -- Obtain the latest blackout window record for this occurrence
    -- There could be multiple records for a given occurrence if the
    -- same blackout name is reused to create multiple blackouts
    FOR crec IN (
        SELECT  start_time, end_time, status 
        INTO    l_start_time, l_end_time, l_status 
        FROM    MGMT_BLACKOUT_WINDOWS 
        WHERE   blackout_guid=p_blackout_guid 
        AND     target_guid=p_target_guid 
        AND     occurrence_number=p_occurrence_number
        ORDER BY start_time DESC
    ) LOOP
        l_start_time := crec.start_time;
        l_end_time := crec.end_time;
        l_status := crec.status;
        EXIT;
    END LOOP;

    IF l_start_time IS NULL THEN
        log_error(BLACKOUT_NOT_EXIST,
                  'process_end_blk_on_target for blackout ' || 
                  p_blackout_guid || ': could not find any blackout windows');
    END IF;
                    
    IF l_end_time IS NOT NULL THEN
        -- This is a scheduled blackout. If it is ending, then we
        -- use the end time in the blackout window. If it was
        -- stopped prematurely, we use the current agent time.
        IF p_stop_ts IS NOT NULL THEN
            l_end_time := p_stop_ts;
        ELSIF p_use_current_time THEN
            l_end_time := current_agent_time(p_target_guid);
        END IF;
     ELSE
        -- This is an indefinite blackout; use current time,
        -- converted to agent time
        IF p_stop_ts IS NOT NULL THEN
            l_end_time := p_stop_ts;
        ELSE
            l_end_time := current_agent_time(p_target_guid);
        END IF;

        l_indefinite_blackout := true;
    END IF;
          
    -- Insert an END-BLACKOUT record for this target 
    -- Ensure that the end time is never less than the start time
    IF l_start_time >= l_end_time THEN
        l_end_time := l_start_time + (1/(60*60*24));
    END IF;

    BEGIN
        INSERT INTO MGMT_BLACKOUT_STATE(blackout_guid, target_guid,
               collection_timestamp, blackout_code) VALUES
           (p_blackout_guid, p_target_guid, l_end_time, NOT_IN_BLACKOUT)
                RETURNING target_status INTO l_target_status;
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            INSERT INTO MGMT_BLACKOUT_STATE(blackout_guid, target_guid,
               collection_timestamp, blackout_code) VALUES
           (p_blackout_guid, p_target_guid, l_end_time+(1/(24*60*60)), NOT_IN_BLACKOUT)
                RETURNING target_status INTO l_target_status;

        -- Note: when stopping a repository blackout, we should never
        -- get a duplicate record exception
        WHEN MGMT_GLOBAL.duplicate_record THEN
            log_error(DUPLICATE_RECORDS_INSERT,
             'Duplicate end blackout record for ' || p_blackout_guid ||
             ' for target ' || p_target_guid);

            l_target_status := NOT_IN_BLACKOUT;
            UPDATE MGMT_BLACKOUT_FLAT_TARGETS SET 
                blackout_status=NOT_IN_BLACKOUT WHERE
                    blackout_guid=p_blackout_guid AND
                    target_guid=p_target_guid;
                    
    END;

    UPDATE  MGMT_BLACKOUT_FLAT_TARGETS 
    SET     job_status=BLK_JSTATE_STOPPED
    WHERE   blackout_guid=p_blackout_guid 
    AND     target_guid=p_target_guid;

    
    -- Update the blackout window with the correct end time if
    -- the blackout was an indefinite blackout, or was stopped
    IF l_indefinite_blackout OR p_use_current_time THEN
    BEGIN
        -- Set the end time of the current blackout. Note that
        -- there can only be one instance of the
        -- blackout open at any given time
        UPDATE  MGMT_BLACKOUT_WINDOWS 
        SET     end_time=l_end_time,
                status=BLK_STATE_STOPPED
        WHERE   blackout_guid=p_blackout_guid
        AND     target_guid=p_target_guid
        AND     occurrence_number=p_occurrence_number;
    EXCEPTION
        WHEN OTHERS THEN
            log_error(BLACKOUT_NOT_STOPPED, 'Cannot update windows for blackout ' || p_blackout_guid || ' on target ' || p_target_guid || ':' || SQLERRM);
    END;
    END IF;

    -- If there are any repository collections for this target,
    -- resume them (if the target is now no longer blacked out)
    IF l_target_status=NOT_IN_BLACKOUT THEN
    BEGIN
        SELECT target_name,
               target_type
          INTO l_target_name,
               l_target_type
          FROM mgmt_targets
         WHERE target_guid = p_target_guid ;

        MGMT_COLLECTION.resume_collection(p_target_name=>l_target_name,
                                          p_target_type=>l_target_type) ;

    EXCEPTION
        WHEN OTHERS THEN
            log_error(BLACKOUT_NOT_STOPPED, 'Cannot resume rep collections for blackout ' || p_blackout_guid || ' on target ' || p_target_guid || ':' || SQLERRM);
    END;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        l_err_msg := SQLERRM;

        -- Propogate deadlocks
        IF SQLCODE=-60 THEN
            RAISE;
        END IF;

        -- If we received an unhandled exception here, do not
        -- penalize the entire blackout, but fail the blackout
        -- on this target. The overall status of the blackout will
        -- go to PARTIAL
        UPDATE  MGMT_BLACKOUT_FLAT_TARGETS
        SET     blackout_status=1,
                job_status=BLK_JSTATE_STOP_FAILED,
                error_message='Error ending blackout: ' || l_err_msg
        WHERE   blackout_guid=p_blackout_guid
        AND     target_guid=p_target_guid;

END;

FUNCTION reinsert_blackout_severity(p_blackout_guid RAW, 
                                    p_target_guid RAW,
                                    p_resp_metric_guid RAW,
                                    p_severity_code NUMBER,
                                    p_msg VARCHAR2,
                                    p_msg_nlsid VARCHAR2 DEFAULT NULL,
                                    p_msg_params VARCHAR2 DEFAULT NULL ) RETURN DATE IS
l_actual_start_time DATE;
l_collection_timestamp DATE;
l_avail_timestamp DATE;
l_marker_timestamp DATE;
BEGIN
    IF EMDW_LOG.p_is_debug_set THEN
        EMDW_LOG.debug(
            'Duplicate/Inconsistent blackout severity inserted for target ' 
               || p_target_guid || 'for blackout ' || p_blackout_guid,
               MGMT_BLACKOUT_ENGINE.MODULE_NAME );
    END IF;

    -- Find the max of the collection timestamp and marker and reinsert
    SELECT  marker_timestamp+(1/(60*60*24)) 
    INTO    l_marker_timestamp
    FROM    MGMT_AVAILABILITY_MARKER
    WHERE   target_guid = p_target_guid;

    SELECT  MAX(collection_timestamp)+(1/(60*60*24)) 
    INTO    l_collection_timestamp
    FROM    MGMT_VIOLATIONS
    WHERE   target_guid = p_target_guid
    AND     policy_guid = p_resp_metric_guid
    AND     violation_level =  p_severity_code;

    SELECT  start_collection_timestamp+(1/(60*60*24)) 
    INTO    l_avail_timestamp
    FROM    MGMT_CURRENT_AVAILABILITY
    WHERE   target_guid=p_target_guid;

    IF l_marker_timestamp > l_collection_timestamp THEN
        l_collection_timestamp := l_marker_timestamp;
    END IF;

    IF l_avail_timestamp > l_collection_timestamp THEN
        l_collection_timestamp := l_avail_timestamp;
    END IF;

    INSERT INTO mgmt_violations
       (target_guid, policy_guid, 
        collection_timestamp,
        violation_level, message, message_nlsid, message_params)
    VALUES 
         (p_target_guid, p_resp_metric_guid,
          l_collection_timestamp,
          p_severity_code, p_msg, p_msg_nlsid, p_msg_params)
    RETURNING collection_timestamp
    INTO  l_actual_start_time;

    IF EMDW_LOG.p_is_debug_set THEN
        EMDW_LOG.debug('Adjust collection time one second for ' 
                  || p_blackout_guid || ' into state IN_BLACKOUT ' || 
                  ' For target ' || p_target_guid ||
                  ' New timestamp is ' || l_collection_timestamp,
                   MGMT_BLACKOUT_ENGINE.MODULE_NAME);
    END IF;

    RETURN l_actual_start_time;
END;

-- Start a blackout window on a target. Called from the
-- blackout trigger code to process windows when jobs run
-- out of order    
PROCEDURE start_blackout_window(p_blackout_guid RAW,
                                p_occurrence_number NUMBER,
                                p_target_guid RAW) IS
BEGIN
    process_start_blk_on_target(p_blackout_guid, p_occurrence_number,
                                p_target_guid, false);

    UPDATE  MGMT_BLACKOUT_FLAT_TARGETS    
    SET     job_status=BLK_JSTATE_START_FAILED
    WHERE   blackout_guid=p_blackout_guid
    AND     target_guid=p_target_guid
    AND     blackout_status=1;

    set_blackout_status(p_blackout_guid, BLK_MODE_NONE);
END;

-- End a blackout window on a target. Called from the
-- blackout trigger code to process windows when jobs run
-- out of order   
PROCEDURE end_blackout_window(p_blackout_guid RAW,
                              p_occurrence_number NUMBER,
                              p_target_guid RAW,
                              p_end_ts DATE) IS
l_current_status MGMT_BLACKOUT_FLAT_TARGETS.job_status%TYPE;
l_target_name MGMT_TARGETS.target_name%TYPE;
l_target_type MGMT_TARGETS.target_type%TYPE;
l_emd_url MGMT_TARGETS.emd_url%TYPE;

l_target_names MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
l_target_types MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();

BEGIN
    SELECT  job_status INTO l_current_status
    FROM    MGMT_BLACKOUT_FLAT_TARGETS
    WHERE   blackout_guid=p_blackout_guid
    AND     target_guid=p_target_guid;
    
    process_end_blk_on_target(p_blackout_guid, p_occurrence_number,
                              p_target_guid, false, null);

    set_blackout_status(p_blackout_guid, BLK_MODE_NONE);

    -- Submit a discard state job, if required
    IF l_current_status=BLK_JSTATE_START_FAILED THEN
        SELECT  emd_url, target_name, target_type
        INTO    l_emd_url, l_target_name, l_target_type
        FROM    MGMT_TARGETS
        WHERE   target_guid=p_target_guid;

        IF l_emd_url IS NOT NULL THEN
            l_target_names.extend(1);
            l_target_types.extend(1);
        
            l_target_names(1) := l_target_name;
            l_target_types(1) := l_target_type;
            submit_discard_state_job(l_emd_url, l_target_names, l_target_types,
                                     p_end_ts);
        END IF;

    END IF;    
END;


-- Perform the processing of the start of the blackout: flatten out
-- the target list, and set their states. This method only
-- processes targets whose edit states are ADD_PENDING
-- If p_use_current_time is true, the current time (suitably
-- converted) is used as the start time of the blackout window
-- for the newly added target.
-- If p_duration_extended is true, the duration of the blackout
-- has been extended.
PROCEDURE process_start_blackout_targets(p_blackout_guid RAW,
                                         p_job_id RAW,
                                         p_execution_id RAW,
                                         p_job_param_name VARCHAR2,
                                         p_compute_flat_list BOOLEAN,
                                         p_use_current_time BOOLEAN,
                                         p_duration_extended BOOLEAN) IS
l_param_list MGMT_JOB_PARAM_LIST;
l_emd_list MGMT_JOB_VECTOR_PARAMS;
l_oms_med_targets MGMT_GUID_ARRAY; 
l_occurrence_number NUMBER;
BEGIN
    SELECT occurrence_number INTO l_occurrence_number FROM
        MGMT_BLACKOUTS WHERE blackout_guid=p_blackout_guid;

    IF p_compute_flat_list THEN
        compute_flat_target_list(p_blackout_guid, false, true);
    END IF;

    -- Inform master_agent package about oms-mediated targets going
    -- into blackout as part of this blackout-guid
    SELECT mgmt_guid_obj(b.target_guid, NULL)
      BULK COLLECT INTO l_oms_med_targets
      FROM MGMT_BLACKOUT_FLAT_TARGETS b, MGMT_TARGETS t
     WHERE b.blackout_guid = p_blackout_guid
       AND b.target_guid = t.target_guid
       AND b.job_status=BLK_JSTATE_START_PROCESSING
       AND t.monitoring_mode = mgmt_global.G_MON_MODE_OMS_MEDIATED;


    IF (l_oms_med_targets IS NOT NULL AND 
        l_oms_med_targets.COUNT > 0) THEN
      em_master_agent.set_blackout_target_list(l_oms_med_targets);
    END IF;


    FOR crec IN (SELECT target_guid 
                 FROM   MGMT_BLACKOUT_FLAT_TARGETS 
                 WHERE  blackout_guid=p_blackout_guid 
                 AND    job_status=BLK_JSTATE_START_PROCESSING
                 ORDER BY target_guid) LOOP
        
        process_start_blk_on_target(p_blackout_guid, l_occurrence_number,
                                    crec.target_guid,
                                    p_use_current_time);
    END LOOP;            
 
    -- Insert a job parameter called emd_list (sic) that will be used
    -- by subsequent steps of the blackout job.
    IF p_duration_extended THEN
        -- If p_duration_extended is true, then the emd_list
        -- should consist of all emds in the blackout
        SELECT DISTINCT emd_url BULK COLLECT INTO l_emd_list FROM
            MGMT_BLACKOUT_FLAT_TARGETS ft, MGMT_TARGETS t WHERE
                ft.target_guid=t.target_guid AND
                ft.blackout_guid=p_blackout_guid AND
                ft.blackout_status=1 AND
                t.emd_url IS NOT NULL;
                  
    ELSE
        -- Only collect emd urls for targets that have been newly added
        SELECT DISTINCT emd_url BULK COLLECT INTO l_emd_list FROM 
            MGMT_BLACKOUT_FLAT_TARGETS ft, MGMT_TARGETS t WHERE
                ft.target_guid=t.target_guid AND
                ft.blackout_guid=p_blackout_guid AND
                ft.job_status=BLK_JSTATE_START_PROCESSING AND
                ft.blackout_status=1 AND
                t.emd_url IS NOT NULL;
    END IF;

    IF l_emd_list IS NULL THEN
        l_emd_list := MGMT_JOB_VECTOR_PARAMS();
    END IF;

    l_param_list := MGMT_JOB_PARAM_LIST();
    l_param_list.extend(1);

    l_param_list(1) := MGMT_JOB_PARAM_RECORD(p_job_param_name, 0, null, 
                                             l_emd_list);

    MGMT_JOBS.add_job_parameters(p_job_id, p_execution_id, l_param_list);

    -- Update the job state of targets that do not have emd_urls to STARTED. 
    -- The job will not process these targets
    UPDATE MGMT_BLACKOUT_FLAT_TARGETS ft SET job_status=BLK_JSTATE_STARTED
        WHERE blackout_guid=p_blackout_guid AND
              job_status=BLK_JSTATE_START_PROCESSING AND
              blackout_status=1 AND
              target_guid IN (SELECT target_guid FROM MGMT_TARGETS t
                    WHERE t.target_guid=ft.target_guid AND
                          emd_url IS NULL);

   -- Clear the blackout list for the OMS mediated targets
    IF (l_oms_med_targets IS NOT NULL AND 
        l_oms_med_targets.COUNT > 0) THEN
      em_master_agent.clear_blackout_target_list;
      l_oms_med_targets := NULL;
    END IF;
EXCEPTION
   WHEN OTHERS THEN
     -- Always clear blackout list for OMS mediated targets
    IF (l_oms_med_targets IS NOT NULL AND 
        l_oms_med_targets.COUNT > 0) THEN
        em_master_agent.clear_blackout_target_list;
    END IF;
    RAISE;
END;

-- Return all blackout windows for the specified target,
-- 
FUNCTION get_blackout_windows(p_target_guid RAW,
                              p_query_time_out OUT DATE)
    RETURN BLACKOUT_CURSOR IS
l_cursor BLACKOUT_CURSOR;
l_current_time DATE;

BEGIN
    -- Get the "current" agent time, as defined by the heartbeat
    l_current_time := current_agent_time(p_target_guid);
    p_query_time_out := l_current_time;

    -- Put in a 10-min tolerance for the start time
    l_current_time := l_current_time + (10/(24*60));

    -- Note: blackout windows may be scheduled well into the future.
    -- The idea is that the agent cannot possibly send severities
    -- for a time later than our (conservative) estimate of the
    -- current time on the agent
    OPEN l_cursor FOR
      SELECT blackout_guid, start_time, end_time, status FROM
            MGMT_BLACKOUT_WINDOWS WHERE
               target_guid=p_target_guid AND
               start_time <= l_current_time;

    RETURN l_cursor;
END;

-- Return the actual start time of the blackout, as well as the 
-- blackout's timezone region
FUNCTION get_blackout_start_time(p_start_job_id RAW,
                                 p_timezone_region_out OUT VARCHAR2) 
   RETURN DATE IS
l_start_time DATE := null;
l_timezone_region MGMT_JOB_EXEC_SUMMARY.timezone_region%TYPE;
BEGIN
    -- Since the start job is a system job, there should be exactly one 
    -- execution even if the schedule is a repeating schedule
    BEGIN
      SELECT scheduled_time, timezone_region
      INTO   l_start_time, l_timezone_region
      FROM   MGMT_JOB_EXEC_SUMMARY
      WHERE  job_id=p_start_job_id
      AND    status NOT IN 
        (MGMT_JOB_ENGINE.WAITING_STATUS, MGMT_JOB_ENGINE.SKIPPED_STATUS);

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- This will happen if the schedule has expired
        RETURN null;
    END;
    
    p_timezone_region_out := l_timezone_region;
    RETURN l_start_time;
END;

-- Schedule the next iteration of the blackout, if the
-- schedule is repeating
PROCEDURE schedule_next_blackout(p_blackout_guid RAW) IS
l_start_job_id RAW(16);
l_frequency_code NUMBER;
l_start_time DATE;

l_occurrence_number NUMBER;

l_tzregion MGMT_JOB_EXEC_SUMMARY.timezone_region%TYPE;
l_status NUMBER;

BEGIN
    SELECT start_job_id, frequency_code, occurrence_number INTO 
      l_start_job_id, l_frequency_code, l_occurrence_number FROM
        MGMT_BLACKOUTS b, MGMT_BLACKOUT_SCHEDULE bs WHERE 
            b.blackout_guid=p_blackout_guid AND
            b.blackout_guid=bs.blackout_guid;
        
    IF l_frequency_code=ONE_TIME_FREQUENCY_CODE THEN
        -- We're done
        RETURN;
    END IF;

    -- Since the start job is a system job, there should be exactly one 
    -- execution even if the schedule is a repeating schedule
    BEGIN
      SELECT scheduled_time, timezone_region
      INTO   l_start_time, l_tzregion
      FROM   MGMT_JOB_EXEC_SUMMARY
      WHERE  job_id=l_start_job_id
      AND    status NOT IN 
        (MGMT_JOB_ENGINE.WAITING_STATUS, MGMT_JOB_ENGINE.SKIPPED_STATUS);

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- This will happen if the schedule has expired
        RETURN;
    END;

    UPDATE MGMT_BLACKOUTS SET scheduled_time=l_start_time,
       blackout_status=BLK_STATE_START_PENDING,
       occurrence_number=l_occurrence_number+1
         WHERE blackout_guid=p_blackout_guid;

    -- Blow up the list, create blackout windows
    compute_flat_target_list(p_blackout_guid, true);
END;

--
-- CREATE_BLACKOUT_INTERNAL
--
-- PURPOSE
--     Create a new blackout
--
--
FUNCTION create_blackout_internal(p_blackout_name IN VARCHAR2,
                                  p_description IN VARCHAR2,
                                  p_schedule IN MGMT_BLACKOUT_SCHEDULE_RECORD,
                                  p_reason IN VARCHAR2,
                                  p_job_flag IN NUMBER,
                                  p_targets IN MGMT_BLACKOUT_TARGET_LIST,
                                  p_start_job_id_out OUT RAW,
                                  p_execution_id_out OUT RAW)
     RETURN RAW IS
l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();
l_blackout_guid RAW(16);
l_target_guid RAW(16);
l_schedule MGMT_BLACKOUT_SCHEDULE_RECORD := p_schedule;
l_reason_id NUMBER;
l_count NUMBER;
l_start_time DATE;

l_duration_source NUMBER;
l_tz_region MGMT_JOB_EXEC_SUMMARY.timezone_region%TYPE;
l_lock_handle VARCHAR2(256) ;


l_bo_name_lock_handle VARCHAR2(256) ;
l_bo_name_lock_value VARCHAR2(256) ;
l_bo_name_lock_status NUMBER;

BEGIN
    -- GET a share lock on the user to prevent the user from being deleted
    -- for the duration of blackout creation.
    --Do not create blackout if user is being deleted.
    l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.BLKOUT_CREATE_FAILED_ERR) ;
     -- Only TIMEZONE_REPOSITORY and TIMEZONE_SPECIFIED are supported
     -- for blackouts
     IF l_schedule.timezone_info != TIMEZONE_SPECIFIED AND
        l_schedule.timezone_info != TIMEZONE_REPOSITORY AND
        l_schedule.timezone_info != TIMEZONE_RGN_SPECIFIED THEN
         raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR,
           'Invalid blackout timezone info');
     END IF;

     IF p_targets IS NULL OR p_targets.count=0 THEN
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
          'The target list of the blackout is empty');
     END IF;

     -- Grab a lock to prevent a user from creating two blackouts with
     -- the same name simultaneously
     l_bo_name_lock_value := l_current_user;
     l_bo_name_lock_handle :=
            MGMT_LOCK_UTIL.get_exclusive_lock(MGMT_LOCK_UTIL.BLACKOUT_NAME_LOCK,
                                          l_bo_name_lock_value,
                                          300,
                                          MGMT_GLOBAL.BLKOUT_CREATE_FAILED_ERR,
              'Could not lock blackout subsystem for user ' || l_current_user);

     l_blackout_guid := MGMT_BLACKOUT_ENGINE.generate_blackout_guid(p_blackout_name, 
                            l_current_user, null);

    SELECT COUNT(*) INTO l_count FROM MGMT_BLACKOUTS WHERE
        blackout_guid=l_blackout_guid;

    IF l_count > 0 THEN
        raise_application_error(MGMT_GLOBAL.BLKOUT_ALREADY_EXISTS_ERR,
          'A blackout with the same name already exists');
    END IF;

    -- Submit a job to enforce the beginning of the blackout
    -- Note that the bulk of the schedule will be validated by the
    -- job system when it processes the start job, so we don't do it
    -- here
    p_start_job_id_out :=
        schedule_blackout(l_blackout_guid, p_blackout_name, false,
                          l_schedule, l_duration_source, p_execution_id_out);

     -- Get the actual start time. Note that depending on the
     -- schedule, it may not be the same as the start time 
     -- specified in the schedlule.
     l_start_time := get_blackout_start_time(p_start_job_id_out, l_tz_region);

     -- Insert details into the blackout table
     INSERT INTO MGMT_BLACKOUTS
         (blackout_guid, blackout_name, 
          blackout_desc, blackout_status, job_flag,
          created_by, created_thru, last_updated_by,
          scheduled_time, start_job_id)
     VALUES
         (l_blackout_guid,
          p_blackout_name, p_description,
          BLK_STATE_START_PENDING, p_job_flag, 
          l_current_user, null, 
          l_current_user, l_start_time,
          p_start_job_id_out);

    -- Insert the schedule
    INSERT INTO MGMT_BLACKOUT_SCHEDULE(blackout_guid, schedule_index,
          frequency_code,
          start_time, end_time, duration,
          execution_hours, execution_minutes, interval, months, days,
          timezone_info, timezone_offset, timezone_region,
          duration_source) VALUES
       (l_blackout_guid, 1, l_schedule.frequency_code, l_schedule.start_time,
        l_schedule.end_time, l_schedule.duration,
        l_schedule.execution_hours,
        l_schedule.execution_minutes, l_schedule.interval,
        l_schedule.months, l_schedule.days,
        l_schedule.timezone_info, l_schedule.timezone_offset,
        l_schedule.timezone_region,
        l_duration_source);


    -- Ensure that the end time is not in the past
    IF l_schedule.end_time IS NOT NULL AND 
       sysdate_blackout_time(l_blackout_guid) > l_schedule.end_time THEN
        raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR, 'The blackout end time cannot be in the past');
    END IF;

     -- Set the reason only if reason is required for blackout
    IF p_reason IS NOT NULL THEN
        MGMT_BLACKOUT_ENGINE.set_reason(l_blackout_guid, p_reason);
    END IF; 

    -- Insert the targets
    FOR i IN 1..p_targets.COUNT LOOP
    BEGIN
        SELECT target_guid INTO l_target_guid FROM MGMT_TARGETS
            WHERE target_name=p_targets(i).target_name AND
                  target_type=p_targets(i).target_type;

        INSERT INTO MGMT_BLACKOUT_TARGET_DETAILS
            (blackout_guid, target_guid, include_members, edit_state)
        VALUES
            (l_blackout_guid, l_target_guid, p_targets(i).include_members,
                  MGMT_BLACKOUT_ENGINE.ADD_PENDING);

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR,
                p_targets(i).target_name || ':' || 
                p_targets(i).target_type);
    END;
    END LOOP;

    -- Blow up the list, create blackout windows
    compute_flat_target_list(l_blackout_guid, true, false, true);

    -- Check that the user has operator privs over all targets
    check_modify_privs(l_blackout_guid);

    RETURN l_blackout_guid;

EXCEPTION
    WHEN OTHERS THEN
        l_bo_name_lock_status :=
            MGMT_LOCK_UTIL.release_lock(l_bo_name_lock_handle) ;
        RAISE;
END;

--
-- Public procedures
-- 

--
-- CREATE_BLACKOUT
--
-- PURPOSE
--     Create a new blackout
--
--
FUNCTION create_blackout(p_blackout_name IN VARCHAR2,
                         p_description IN VARCHAR2,
                         p_schedule IN MGMT_BLACKOUT_SCHEDULE_RECORD,
                         p_reason IN VARCHAR2,
                         p_job_flag IN NUMBER,
                         p_targets IN MGMT_BLACKOUT_TARGET_LIST)
     RETURN RAW IS
l_blackout_guid RAW(16);
l_start_job_id MGMT_BLACKOUTS.start_job_id%TYPE;
l_execution_id RAW(16);
BEGIN
    l_blackout_guid := create_blackout_internal(p_blackout_name,
                                                p_description,
                                                p_schedule,
                                                p_reason,
                                                p_job_flag,
                                                p_targets,
                                                l_start_job_id,
                                                l_execution_id);

    IF EMDW_LOG.p_is_info_set THEN
        EMDW_LOG.info('Created blackout ' || 
                      p_blackout_name,
                      MODULE_NAME);
    END IF;

    RETURN l_blackout_guid;
END;

--
-- CREATE_IMMEDIATE_BLACKOUT
--
-- PURPOSE
--     Create a new immediate blackout with indefinite duration
--
--
FUNCTION create_immediate_blackout(p_blackout_name IN VARCHAR2,
                                   p_description IN VARCHAR2,
                                   p_reason IN VARCHAR2,
                                   p_job_flag IN NUMBER,
                                   p_targets IN MGMT_BLACKOUT_TARGET_LIST,
                                   p_caller_execution_id RAW DEFAULT NULL)
     RETURN RAW IS
l_blackout_guid RAW(16);
l_blk_start_job_id MGMT_BLACKOUTS.start_job_id%TYPE;
l_blk_execution_id RAW(16);
l_schedule MGMT_BLACKOUT_SCHEDULE_RECORD;
l_duration NUMBER := -1;
l_param_list MGMT_JOB_PARAM_LIST;
BEGIN
    -- create immediate indefinite schedule
    l_schedule :=  MGMT_BLACKOUT_SCHEDULE_RECORD(MGMT_BLACKOUT.ONE_TIME_FREQUENCY_CODE,
                              SYSDATE, null,
                              0, 0, 0, null, null, l_duration,
                              TIMEZONE_REPOSITORY, 0, null);

    l_blackout_guid := create_blackout_internal(p_blackout_name,
                                                p_description,
                                                l_schedule,
                                                p_reason,
                                                p_job_flag,
                                                p_targets,
                                                l_blk_start_job_id,
                                                l_blk_execution_id);

    process_start_blackout(l_blackout_guid, l_blk_start_job_id, l_blk_execution_id, 'emd_list');

    -- If execution_id was specified, associate it with the newly created blackout_guid.
    -- The blackout_guid will be used in the job engine to bypass the blackout check 
    -- so that the caller job can be retried if it fails.
    IF p_caller_execution_id IS NOT NULL THEN
        INSERT INTO MGMT_JOB_BLACKOUT_ASSOC(execution_id, blackout_guid) 
        VALUES (p_caller_execution_id, l_blackout_guid);
    END IF;

    IF EMDW_LOG.p_is_info_set THEN
        EMDW_LOG.info('Created immediate blackout ' || p_blackout_name,
                      MODULE_NAME);
    END IF;

    RETURN l_blackout_guid;
END;
     
-- Process all blackouts (for the given target only) whose
-- start time is before p_start_time that haven't already
-- started
PROCEDURE start_blackouts_on_target(p_target_guid RAW,
                                    p_start_time DATE) IS

l_current_state NUMBER;
l_upd_time DATE;
l_updated_by MGMT_BLACKOUTS.last_updated_by%TYPE := MGMT_USER.get_current_em_user;

l_count NUMBER;
BEGIN
    -- Do nothing if the target does not exist
    SELECT COUNT(1) INTO l_count
    FROM   MGMT_TARGETS
    WHERE  target_guid=p_target_guid;

    IF l_count=0 THEN
        RETURN;
    END IF;

    FOR crec IN (SELECT w.blackout_guid, b.occurrence_number FROM 
                   MGMT_BLACKOUT_WINDOWS w, MGMT_BLACKOUTS b,
                   MGMT_BLACKOUT_FLAT_TARGETS ft WHERE
                       w.target_guid=p_target_guid AND
                       (w.end_time IS NULL OR
                        w.end_time > p_start_time) AND
                       w.start_time < p_start_time AND
                       w.status NOT IN (BLK_STATE_ENDED, BLK_STATE_STOPPED) AND
                       w.blackout_guid=b.blackout_guid AND
                       w.blackout_guid=ft.blackout_guid AND
                       w.blackout_guid=ft.blackout_guid AND
                       w.target_guid=ft.target_guid AND
                       ft.blackout_status=0 AND
                       ft.job_status=BLK_JSTATE_START_PROCESSING
                  ORDER BY w.start_time) LOOP

        l_current_state := lock_blackout(crec.blackout_guid);

        IF l_current_state = BLK_STATE_START_PENDING OR 
           l_current_state = BLK_STATE_START_PARTIAL THEN
            process_start_blk_on_target(crec.blackout_guid, 
                                        crec.occurrence_number,
                                        p_target_guid);

            l_upd_time := sysdate_blackout_time(crec.blackout_guid);
            set_blackout_status(crec.blackout_guid, BLK_MODE_NONE);

            -- Set the job status for this target to START FAILED
            -- since we want to indicate that the agent was not
            -- contacted for this blackout
            UPDATE MGMT_BLACKOUT_FLAT_TARGETS
            SET    job_status=BLK_JSTATE_START_FAILED
            WHERE  blackout_guid=crec.blackout_guid
            AND    target_guid=p_target_guid;
        END IF;        
    END LOOP;
END;

-- Process all blackouts (for the given target only) whose
-- end time is before p_end_time that haven't already
-- ended
PROCEDURE end_blackouts_on_target(p_target_guid RAW,
                                  p_end_time DATE) IS
l_current_state NUMBER;
l_upd_time DATE;
l_updated_by MGMT_BLACKOUTS.last_updated_by%TYPE := MGMT_USER.get_current_em_user;

l_job_status NUMBER;

l_curr_occ_number NUMBER;
l_count NUMBER;
BEGIN
    -- Do nothing if the target does not exist
    SELECT COUNT(1) INTO l_count
    FROM   MGMT_TARGETS
    WHERE  target_guid=p_target_guid;

    IF l_count=0 THEN
        RETURN;
    END IF;

    FOR crec IN (SELECT w.blackout_guid, b.occurrence_number, 
                        ft.job_status, t.emd_url FROM 
                   MGMT_BLACKOUT_WINDOWS w, MGMT_BLACKOUTS b,
                   MGMT_BLACKOUT_FLAT_TARGETS ft, MGMT_TARGETS t WHERE
                       w.target_guid=p_target_guid AND
                       w.target_guid=t.target_guid AND
                       w.end_time IS NOT NULL AND
                       w.end_time < p_end_time AND
                       w.status NOT IN 
                          (BLK_STATE_STOPPED, BLK_STATE_ENDED) AND
                       w.blackout_guid=b.blackout_guid AND
                       w.blackout_guid=ft.blackout_guid AND
                       w.blackout_guid=ft.blackout_guid AND
                       w.target_guid=ft.target_guid AND
                       ft.blackout_status=1 AND
                       ft.job_status IN (BLK_JSTATE_START_PROCESSING,
                                         BLK_JSTATE_STARTED,
                                         BLK_JSTATE_START_FAILED)
                  ORDER BY w.end_time) LOOP

        l_current_state := lock_blackout(crec.blackout_guid);

        IF l_current_state >= 0 AND
           l_current_state != BLK_STATE_STOPPED AND
           l_current_state != BLK_STATE_ENDED THEN
            l_job_status := crec.job_status;  -- Current job status

            SELECT occurrence_number INTO l_curr_occ_number
            FROM   MGMT_BLACKOUTS
            WHERE  blackout_guid=crec.blackout_guid;

            -- Ensure that the occurrence number is the same
            -- as that of the window. If we have moved ahead,
            -- then the end processing has taken place already:
            -- do nothing
            IF l_curr_occ_number=crec.occurrence_number THEN
                process_end_blk_on_target(crec.blackout_guid, 
                                          crec.occurrence_number,
                                          p_target_guid,
                                          false,
                                          null);

                l_upd_time := sysdate_blackout_time(crec.blackout_guid);
                set_blackout_status(crec.blackout_guid, BLK_MODE_NONE);
                
                -- If the agent does not know about this blackout,
                -- we need to discard state
                IF l_job_status=BLK_JSTATE_START_FAILED THEN
                    submit_discard_state_job(crec.blackout_guid,
                                             crec.emd_url,
                                             current_agent_time(p_target_guid));
                END IF;
            END IF;
        END IF;        
    END LOOP;
END;

-- Handle the case where a group's membership changed
PROCEDURE handle_membership_change(p_container_guid RAW) IS
l_current_status NUMBER;
l_scheduled_time DATE;
l_duration NUMBER;
l_tzoffset NUMBER;
l_target_type MGMT_TARGETS.target_type%TYPE;

cr BLACKOUT_CURSOR;
l_blackout_guid MGMT_BLACKOUTS.blackout_guid%TYPE;

BEGIN
     IF EMDW_LOG.p_is_debug_set THEN
         EMDW_LOG.debug('IN handle_membership_change for : ' || p_container_guid,MODULE_NAME);
     END IF;
     SELECT target_type INTO l_target_type FROM MGMT_TARGETS WHERE
          target_guid=p_container_guid;

     IF l_target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE THEN
         -- Blackouts that are registered on the node
         OPEN cr FOR
             SELECT blackout_guid FROM MGMT_BLACKOUTS b WHERE EXISTS
                 (SELECT target_guid FROM MGMT_BLACKOUT_TARGET_DETAILS bt
                     WHERE bt.blackout_guid=b.blackout_guid AND
                           bt.target_guid=p_container_guid AND
                           bt.include_members=1) AND
             blackout_status=BLK_STATE_START_PENDING;

     ELSE
         -- Blackouts registered on the group, or groups
         -- that contain the group
         OPEN cr FOR
         SELECT blackout_guid FROM MGMT_BLACKOUTS b WHERE EXISTS
            (SELECT target_guid FROM MGMT_BLACKOUT_TARGET_DETAILS bt
               WHERE bt.blackout_guid=b.blackout_guid AND
                     bt.include_members=1 AND
                     (bt.target_guid=p_container_guid OR
                      bt.target_guid IN 
                          (SELECT source_target_guid FROM 
                             MGMT_FLAT_TARGET_ASSOC WHERE
                                assoc_target_guid=p_container_guid AND
                                is_membership = 1)
                      )
            ) AND
            blackout_status=BLK_STATE_START_PENDING;

     END IF;


     LOOP
        FETCH cr INTO l_blackout_guid;
        EXIT WHEN cr%NOTFOUND;

        IF EMDW_LOG.p_is_debug_set THEN
            EMDW_LOG.debug('Considering blackout ' || l_blackout_guid,MODULE_NAME);
        END IF;
        l_current_status := lock_blackout(l_blackout_guid);
        
        -- Recompute the flat list for each affected blackout.
        -- If the blackout has started, do nothing....
        IF l_current_status = BLK_STATE_START_PENDING THEN
            IF EMDW_LOG.p_is_debug_set THEN
                EMDW_LOG.debug('Recomputing flat list for BO ' ||
                                 l_blackout_guid,MODULE_NAME);
            END IF;
            compute_flat_target_list(l_blackout_guid, true);
        END IF;
    END LOOP;
                         
END;

-- Compute and return the new status of the blackout.
FUNCTION compute_blackout_status(p_blackout_guid RAW, p_mode NUMBER,
                                 p_last_start_time IN OUT DATE,
                                 p_last_end_time IN OUT DATE) 
        RETURN NUMBER IS

l_all_targets_count NUMBER;
l_targets_in_blackout NUMBER;

l_update_state NUMBER;
l_failed_targets NUMBER;

l_num_processed_targets NUMBER;
l_num_scheduled_targets NUMBER;

BEGIN
    -- The status of the blackout is STOPPED if we stopped the
    -- blackout on all targets, STOP_PENDING if all targets are
    -- in either STOPPED or STOP_PENDING state
    -- NOTE PL/SQL does not let me use constants inside decode's
    -- so I'm stuck with using the literals :-(
    SELECT SUM(decode(blackout_status, 1, 1, 0)),  -- targets in blackout
           COUNT(job_status) INTO 
              l_targets_in_blackout,
              l_all_targets_count  FROM 
        MGMT_BLACKOUT_FLAT_TARGETS WHERE 
            blackout_guid=p_blackout_guid;

    IF l_targets_in_blackout=l_all_targets_count THEN
        IF p_mode=BLK_MODE_STOP THEN
            -- If we were trying to stop the blackout and
            -- couldn't, then the state is stop failed
            l_update_state := BLK_STATE_STOP_FAILED;
        ELSIF p_mode=BLK_MODE_EDIT THEN
            -- If we only added targets, the status is STARTED;
            -- else, its EDIT_FAILED
            SELECT COUNT(job_status) INTO l_failed_targets FROM
                MGMT_BLACKOUT_FLAT_TARGETS WHERE 
                  blackout_guid=p_blackout_guid AND
                  job_status=BLK_JSTATE_STOP_FAILED;

            IF l_failed_targets > 0 THEN
                l_update_state := BLK_STATE_EDIT_FAILED;
            ELSE
                l_update_state := BLK_STATE_STARTED;
            END IF;
        ELSE
            l_update_state := BLK_STATE_STARTED;
        END IF;

        p_last_start_time := sysdate_blackout_time(p_blackout_guid);
        p_last_end_time := null;
    ELSIF l_targets_in_blackout > 0 THEN
        IF p_mode=BLK_MODE_STOP THEN
            l_update_state := BLK_STATE_STOP_PARTIAL;
        ELSIF p_mode=BLK_MODE_EDIT THEN
            l_update_state := BLK_STATE_EDIT_PARTIAL;
        ELSE
            SELECT  COUNT(*) INTO l_num_scheduled_targets
            FROM    MGMT_BLACKOUT_FLAT_TARGETS
            WHERE   blackout_guid=p_blackout_guid
            AND     job_status = BLK_JSTATE_START_PROCESSING;

            IF l_num_scheduled_targets > 0 THEN
                l_update_state := BLK_STATE_START_PARTIAL;
            ELSE
                l_update_state := BLK_STATE_END_PARTIAL;
            END IF;
        END IF;
    ELSE
        -- Check whether all targets in this blackout have been
        -- processed
        SELECT  COUNT(*) INTO l_num_processed_targets 
        FROM    MGMT_BLACKOUT_FLAT_TARGETS
        WHERE   blackout_guid=p_blackout_guid
        AND     job_status = BLK_JSTATE_STOPPED;

        IF l_num_processed_targets=l_all_targets_count THEN
            IF p_mode=BLK_MODE_STOP OR p_mode=BLK_MODE_EDIT THEN
                l_update_state := BLK_STATE_STOPPED;
            ELSE
                l_update_state := BLK_STATE_ENDED;
            END IF;

            p_last_end_time := sysdate_blackout_time(p_blackout_guid);
        ELSE
            l_update_state := BLK_STATE_END_PARTIAL;
        END IF;

    END IF;

    IF EMDW_LOG.p_is_info_set THEN
        EMDW_LOG.info('compute_blackout_status() for blackout ' ||
                      p_blackout_guid || ': new state ' || 
                      l_update_state,
                      MODULE_NAME);
    END IF;

    RETURN l_update_state;
END;

                                         
-- Update the blackout status from the state of its component targets
-- p_in_stop is true if a STOP of the blackout is currently being
-- processed: this affects some transitions
FUNCTION update_blackout_status(p_blackout_guid RAW,
                                p_mode NUMBER)
         RETURN NUMBER IS
l_current_blackout_state NUMBER;

l_update_state NUMBER;
l_last_start_time DATE;
l_last_end_time DATE;

l_start_job_id MGMT_BLACKOUTS.start_job_id%TYPE;
l_end_job_id MGMT_BLACKOUTS.end_job_id%TYPE;

l_occurrence_number MGMT_BLACKOUTS.occurrence_number%TYPE;
l_upd_time MGMT_BLACKOUT_HISTORY.end_time%TYPE;


BEGIN
    SELECT blackout_status, last_start_time, last_end_time INTO 
        l_current_blackout_state, l_last_start_time, l_last_end_time FROM
       MGMT_BLACKOUTS WHERE blackout_guid=p_blackout_guid;

    l_update_state := compute_blackout_status(p_blackout_guid, p_mode,
                                              l_last_start_time,
                                              l_last_end_time);
    
    IF EMDW_LOG.p_is_info_set THEN
        EMDW_LOG.info('update_blackout_status() for blackout ' ||
                      p_blackout_guid || ': old ' || 
                      l_current_blackout_state ||
                      ' new ' || l_update_state,
                      MODULE_NAME);
    END IF;

    IF l_current_blackout_state != l_update_state THEN   
        UPDATE MGMT_BLACKOUTS SET 
          blackout_status=l_update_state,
          last_start_time=l_last_start_time,
          last_end_time=l_last_end_time WHERE
            blackout_guid=p_blackout_guid;

        SELECT start_job_id, end_job_id, occurrence_number INTO
            l_start_job_id, l_end_job_id, l_occurrence_number FROM
               MGMT_BLACKOUTS WHERE blackout_guid=p_blackout_guid;

        -- If the blackout was stopped or ended, update the 
        -- history with the end time
        IF l_update_state=BLK_STATE_STOPPED OR
           l_update_state=BLK_STATE_ENDED THEN
            l_upd_time := sysdate_blackout_time(p_blackout_guid);

            UPDATE MGMT_BLACKOUT_HISTORY SET 
                end_time=l_upd_time
            WHERE
                blackout_guid=p_blackout_guid AND
                occurrence_number=l_occurrence_number;
        END IF;

        -- If we are updating the status as a result of a stop or edit
        -- operation, and the status of the blackout is STOPPED, then
        -- remove the end job
        IF (p_mode=BLK_MODE_STOP OR p_mode=BLK_MODE_EDIT) AND
            l_update_state=BLK_STATE_STOPPED THEN

            IF l_start_job_id IS NOT NULL THEN
                cancel_job(l_start_job_id);
            END IF;

            IF l_end_job_id IS NOT NULL THEN
                cancel_job(l_end_job_id);
            END IF;
        END IF;
        
    END IF;

    RETURN l_update_state;
END;

PROCEDURE set_blackout_status(p_blackout_guid RAW,
                              p_mode NUMBER) IS 
l_ignore NUMBER;
BEGIN
    l_ignore := update_blackout_status(p_blackout_guid, p_mode);
END;

-- Generate a blackout guid, given the blackout name,
-- creator and created-thru (emd url). 
FUNCTION generate_blackout_guid(p_blackout_name VARCHAR2,
                                p_creator VARCHAR2,
                                p_created_thru VARCHAR2) RETURN RAW IS
l_created_thru VARCHAR2(256);
l_creator VARCHAR2(64);
BEGIN
    IF p_blackout_name IS NULL THEN
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
            'The blackout name cannot be null');
    END IF;

    IF p_created_thru IS NULL THEN
        l_created_thru := '';
    ELSE
        l_created_thru := p_created_thru;
    END IF;

    IF p_creator IS NULL THEN
        l_creator := '<SYSTEM>';
    ELSE
        l_creator := p_creator;
    END IF;

    RETURN DBMS_OBFUSCATION_TOOLKIT.md5(
             input => UTL_RAW.cast_to_raw(p_blackout_name ||
                            ';'|| l_creator ||
                            ';'|| l_created_thru));
END;


-- Start the specified blackout, on all targets
PROCEDURE process_start_blackout(p_blackout_guid RAW,
                                 p_job_id RAW,
                                 p_execution_id RAW,
                                 p_job_param_name VARCHAR2) IS
l_blackout_status NUMBER;
l_new_state NUMBER;
l_end_job_id RAW(16);
l_upd_time DATE;

l_occurrence_number MGMT_BLACKOUTS.occurrence_number%TYPE;

l_new_status NUMBER;
l_targets_in_blk_count NUMBER;
l_targets_count NUMBER;

BEGIN
    -- Lock the record in the master blackout table 
    l_blackout_status := lock_blackout(p_blackout_guid);

    IF l_blackout_status < 0 THEN
        RETURN;
    END IF;

    -- For immediate blackouts, the start of the blackout has already been processed.
    -- Just return.
    IF l_blackout_status = BLK_STATE_START_PROCESSING THEN
        RETURN;
    END IF;

    SELECT occurrence_number INTO l_occurrence_number FROM
        MGMT_BLACKOUTS WHERE blackout_guid=p_blackout_guid;

    IF l_blackout_status=BLK_STATE_MODIFY_PENDING THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_PENDING_EDIT_ERR,
         'The blackout is currently being edited');
    ELSIF l_blackout_status=BLK_STATE_STOP_PENDING THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_STOP_PENDING_ERR,
         'The blackout is currently being stopped');
    ELSIF l_blackout_status IN (BLK_STATE_ENDED, BLK_STATE_STOPPED,
                                BLK_STATE_STARTED) THEN
        -- This could happen if some or all the blackout windows in the
        -- current blackout were started as side effects of
        -- processing other blackouts.
        -- In any case, proceed with the start job.
        log_error(BLACKOUT_NOT_STOPPED,
         'Blackout ' || p_blackout_guid || 
         ' occ no ' || l_occurrence_number ||
         ': current state ('|| l_blackout_status ||') not ' ||
         ' START_PENDING');
    ELSIF l_blackout_status != BLK_STATE_START_PENDING AND
          l_blackout_status != BLK_STATE_START_PARTIAL AND
          l_blackout_status != BLK_STATE_END_PARTIAL THEN
        -- This should generally not happen. It could happen with 
        -- a periodic schedule where for some reason the previous
        -- instance has not ended yet
        log_error(BLACKOUT_NOT_STOPPED,
         'Blackout ' || p_blackout_guid || 
         ' occ no ' || l_occurrence_number ||
         ': current state ('|| l_blackout_status ||') not ' ||
         ' START_PENDING');
    END IF;

   IF EMDW_LOG.p_is_info_set THEN
       EMDW_LOG.info('process_start_blackout() for blackout ' ||
                     p_blackout_guid || ': current status is ' || 
                     l_blackout_status,
                     MODULE_NAME);
    END IF;

    SELECT  COUNT(*) INTO l_targets_count
    FROM    MGMT_BLACKOUT_FLAT_TARGETS
    WHERE   blackout_guid=p_blackout_guid;
    
    IF l_blackout_status=BLK_STATE_STOPPED THEN
        -- The blackout has already stopped. Do not submit
        -- the end job.
        l_end_job_id := null;
        l_new_status := l_blackout_status;
    ELSIF l_targets_count = 0 THEN
        -- 5061352:There may be no targets in the flat targets as the
        -- privileges on the targets may have been revoked.
        -- End the blackout.
        process_end_blackout(p_blackout_guid);

    ELSE
        -- Submit the end job
        l_end_job_id := submit_end_blackout_job(p_blackout_guid);
        l_new_status := BLK_STATE_START_PROCESSING;
    END IF;

    -- Update the state to START_PROCESSING. It will change to
    -- STARTED or PARTIAL after all processing has been done
    l_upd_time := sysdate_blackout_time(p_blackout_guid);

    -- Insert a new row for this occurrence
    BEGIN
        INSERT INTO MGMT_BLACKOUT_HISTORY(blackout_guid, occurrence_number,
                                          start_time, end_time)
        VALUES
            (p_blackout_guid, l_occurrence_number, l_upd_time, null);
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        NULL;
    END;

    -- Process the blackout for all flat targets. Note:
    -- the flat list has already been computed
    process_start_blackout_targets(p_blackout_guid, p_job_id,
                                   p_execution_id, p_job_param_name, 
                                   false, false, false);

    SELECT  COUNT(*) INTO l_targets_in_blk_count
    FROM    MGMT_BLACKOUT_FLAT_TARGETS
    WHERE   blackout_guid=p_blackout_guid
    AND     blackout_status=1;

    -- It is possible that the process of starting the blackout
    -- failed for every target in the system. If that is the
    -- case, set the blackout status accordingly
    IF  l_targets_in_blk_count=0 THEN
        set_blackout_status(p_blackout_guid, BLK_MODE_NONE);
    ELSE
        UPDATE MGMT_BLACKOUTS 
        SET    blackout_status=l_new_status,
               last_updated_time=l_upd_time,
               end_job_id=l_end_job_id 
        WHERE  blackout_guid=p_blackout_guid;
    END IF;    

END;

-- Update job status for all targets on the specified emd
-- for the specified blackout, during start processing
-- for the blackout
PROCEDURE update_start_job_status(p_blackout_guid RAW,
                                  p_emd_url VARCHAR2,
                                  p_state NUMBER,
                                  p_message VARCHAR2) IS

BEGIN
    -- Note: if the state is STARTED, then all targets on that
    -- emd have the state STARTED, since if we can contact the
    -- agent, it will know about all targets. 
    IF p_state=BLK_JSTATE_STARTED THEN
        UPDATE MGMT_BLACKOUT_FLAT_TARGETS SET job_status=p_state,
               error_message=p_message
            WHERE blackout_guid=p_blackout_guid AND
                  target_guid IN (SELECT target_guid FROM MGMT_TARGETS
                                     WHERE emd_url=p_emd_url) AND
                  blackout_status=1 AND
                  job_status IN (BLK_JSTATE_START_PROCESSING,
                                 BLK_JSTATE_START_FAILED,
                                 BLK_JSTATE_STOP_FAILED);
    ELSIF p_state=BLK_JSTATE_START_FAILED THEN
        UPDATE MGMT_BLACKOUT_FLAT_TARGETS SET job_status=p_state,
               error_message=p_message
            WHERE blackout_guid=p_blackout_guid AND
                  target_guid IN (SELECT target_guid FROM MGMT_TARGETS
                                     WHERE emd_url=p_emd_url) AND
                  blackout_status=1 AND
                  job_status IN (BLK_JSTATE_START_PROCESSING,
                                 BLK_JSTATE_START_FAILED);
    ELSE
        log_error(UNEXPECTED_STATE, 'Unexpected state ' || p_state ||
           ' in start job for blackout ' || p_blackout_guid);
    END IF;
               
END;


-- Update job status for all targets on the specified emd
-- for the specified blackout, during stop processing
-- for the blackout
PROCEDURE update_stop_job_status(p_blackout_guid RAW,
                                 p_emd_url VARCHAR2,
                                 p_state NUMBER,
                                 p_message VARCHAR2,
                                 p_stop_ts DATE) IS
l_target_names MGMT_JOB_VECTOR_PARAMS;
l_target_types MGMT_JOB_VECTOR_PARAMS;
BEGIN
    -- This is a hack to work around the fact that the agent's
    -- removeBlackout() API does not end up discarding state;
    -- so we do it ourselves
    IF p_state=BLK_JSTATE_STOPPED THEN
        SELECT target_name, target_type 
           BULK COLLECT INTO l_target_names, l_target_types 
              FROM MGMT_TARGETS t, MGMT_BLACKOUT_FLAT_TARGETS ft WHERE
                 emd_url=p_emd_url AND
                 ft.target_guid=t.target_guid AND
                 ft.blackout_guid=p_blackout_guid AND
                 job_status=BLK_JSTATE_STOP_PROCESSING;

        submit_discard_state_job(p_emd_url, l_target_names, 
                                 l_target_types, p_stop_ts+(2/(24*60)));
    ELSE
        -- In this case process_end_blk_on_target() will
        -- not be called; update the status
        UPDATE  MGMT_BLACKOUT_FLAT_TARGETS 
        SET     job_status=p_state,
                error_message=p_message
        WHERE   blackout_guid=p_blackout_guid 
        AND     target_guid IN (SELECT  target_guid FROM MGMT_TARGETS
                                WHERE   emd_url=p_emd_url) 
        AND     job_status=BLK_JSTATE_STOP_PROCESSING;
    END IF;
    
END;

                       
-- End the specified blackout on all the specified targets.
-- If p_stop_ts is not null, then use that to update the 
-- blackout records. This would be used only when all the
-- targets are off the same agent.
-- ELSE If p_use_current_time is TRUE, then use the "current"
-- time (suitably converted to agent timezone) to update
-- the blackout records
PROCEDURE process_end_blackout(p_blackout_guid RAW,
                               p_target_guids MGMT_USER_GUID_ARRAY,
                               p_use_current_time BOOLEAN,
                               p_stop_ts DATE DEFAULT NULL) IS

l_target_status NUMBER;
l_blackout_status NUMBER;
l_end_job_id MGMT_BLACKOUTS.start_job_id%TYPE;
l_start_job_id MGMT_BLACKOUTS.end_job_id%TYPE;

l_current_emd_url MGMT_TARGETS.emd_url%TYPE := null;

l_target_name MGMT_TARGETS.target_name%TYPE;
l_target_type MGMT_TARGETS.target_type%TYPE;
l_emd_url MGMT_TARGETS.emd_url%TYPE;
l_target_guid MGMT_TARGETS.target_guid%TYPE;

l_target_names MGMT_JOB_VECTOR_PARAMS;
l_target_types MGMT_JOB_VECTOR_PARAMS;
l_emd_urls MGMT_JOB_VECTOR_PARAMS;
l_found BOOLEAN := false;

l_discard_tnames MGMT_JOB_VECTOR_PARAMS;
l_discard_ttypes MGMT_JOB_VECTOR_PARAMS;
l_discard_tguids MGMT_USER_GUID_ARRAY;
l_discard_emdurls MGMT_JOB_VECTOR_PARAMS;
N INTEGER := 1;

CURSOR l_discard_state_cursor(p_blackout_guid RAW,
                              p_target_guids MGMT_USER_GUID_ARRAY) IS
    SELECT target_name, target_type, t.target_guid, t.emd_url FROM
        MGMT_BLACKOUT_FLAT_TARGETS ft, MGMT_TARGETS t WHERE
           ft.target_guid=t.target_guid AND
           ft.blackout_guid=p_blackout_guid AND
/**** Uncommenting this results in a 3113
           ft.target_guid IN 
           (SELECT * FROM (TABLE(CAST(p_target_guids AS MGMT_USER_GUID_ARRAY)))) AND
*****/
           job_status IN (BLK_JSTATE_START_FAILED)
           ORDER BY emd_url;

l_occurrence_number NUMBER;

l_sorted_targets MGMT_USER_GUID_ARRAY;

BEGIN
    SELECT occurrence_number INTO l_occurrence_number FROM
        MGMT_BLACKOUTS WHERE blackout_guid=p_blackout_guid;
        
    -- Submit "Discard State" jobs for all emds that we were
    -- *not* able to contact at the beginning of the blackout
    -- This job will succeed only when it can successfully 
    -- contact the emd
    OPEN l_discard_state_cursor(p_blackout_guid, p_target_guids);
    FETCH l_discard_state_cursor BULK COLLECT INTO
        l_discard_tnames, l_discard_ttypes, 
        l_discard_tguids, l_discard_emdurls;
    CLOSE l_discard_state_cursor;

    -- Sort by target guid to prevent deadlocks
    -- Put agent at the end of the list otherwise all the targets processed
    -- after the agent will go to agent unreachable instead UNKNOWN.
    SELECT /*+ CARDINALITY(l 10)*/ l.column_value BULK COLLECT INTO l_sorted_targets
    FROM TABLE(CAST(p_target_guids as MGMT_USER_GUID_ARRAY)) l, MGMT_TARGETS t
    WHERE l.column_value = t.target_guid 
    ORDER BY DECODE(t.target_type, MGMT_GLOBAL.G_AGENT_TARGET_TYPE, 2, 1), 1;

    -- Loop over the list of targets chosen and end the blackout
    -- over them. Order them by target guid to avoid deadlocks
    FOR i IN 1..l_sorted_targets.COUNT LOOP

        process_end_blk_on_target(p_blackout_guid,
                                  l_occurrence_number,
                                  l_sorted_targets(i),
                                  p_use_current_time,
                                  p_stop_ts);

    END LOOP;

    IF l_discard_tnames IS NOT NULL THEN
        FOR i IN 1..l_discard_tnames.COUNT LOOP
            l_target_name := l_discard_tnames(i);
            l_target_type := l_discard_ttypes(i);
            l_emd_url := l_discard_emdurls(i);
            l_target_guid := l_discard_tguids(i);

            -- Yes, this is a linear search. The query has to be 
            -- "exploded" to work around the 3113 issue above
            l_found := false;
            FOR j IN 1..p_target_guids.COUNT LOOP
                IF p_target_guids(j)= l_target_guid THEN
                    l_found := true;
                END IF;
            END LOOP;

            IF NOT l_found THEN
                GOTO next_iteration;
            END IF;

            IF l_current_emd_url IS NULL THEN
                l_current_emd_url := l_emd_url;
            ELSIF l_current_emd_url != l_emd_url THEN
                submit_discard_state_job(l_current_emd_url, 
                                         l_target_names, l_target_types, 
                                         null);
                l_target_names := null;
                l_target_types := null;
                l_current_emd_url := l_emd_url;
                N := 1;
            END IF;

            IF l_target_names IS NULL THEN
                l_target_names := MGMT_JOB_VECTOR_PARAMS();
                l_target_types := MGMT_JOB_VECTOR_PARAMS();
            END IF;

            l_target_names.extend(1);
            l_target_types.extend(1);

            l_target_names(N) := l_target_name;
            l_target_types(N) := l_target_type;
            N := N+1;

            <<next_iteration>>
             NULL;
        END LOOP;

        -- Submit the discard state job for the last emd url
        IF l_current_emd_url IS NOT NULL THEN
            submit_discard_state_job(l_current_emd_url, 
                                     l_target_names, l_target_types,
                                     null);
        END IF;
    END IF;

    -- Inform the job system that the blackout ended...
    MGMT_JOB_ENGINE.blackout_window_ended(p_blackout_guid);

EXCEPTION
    WHEN OTHERS THEN
            IF l_discard_state_cursor%ISOPEN THEN
                CLOSE l_discard_state_cursor;
            END IF;
            RAISE;

END;

-- Process the stop of the blackout on all targets with the
-- specific emd_url
PROCEDURE process_stop_blackout(p_blackout_guid RAW,
                                p_emd_url VARCHAR2,
                                p_state NUMBER,
                                p_message VARCHAR2,
                                p_stop_ts DATE) IS

CURSOR l_targets_on_emd(p_blackout_guid RAW, p_emd_url VARCHAR2) IS
    SELECT ft.target_guid FROM 
        MGMT_BLACKOUT_FLAT_TARGETS ft, MGMT_TARGETS t WHERE
            ft.blackout_guid=p_blackout_guid AND
            ft.target_guid=t.target_guid AND
            emd_url=p_emd_url AND
            job_status IN (BLK_JSTATE_STOP_PROCESSING)
            ORDER BY ft.target_guid;

l_target_guids MGMT_USER_GUID_ARRAY;
l_start_time DATE;
l_stop_ts DATE;
BEGIN
    IF p_stop_ts IS NULL THEN
        l_stop_ts := current_agent_time_url(p_emd_url);
    ELSE
        l_stop_ts := p_stop_ts;
    END IF;

    OPEN l_targets_on_emd(p_blackout_guid, p_emd_url);
    FETCH l_targets_on_emd BULK COLLECT INTO l_target_guids;
    IF l_targets_on_emd%ISOPEN THEN
        CLOSE l_targets_on_emd;
    END IF;
    
    -- Update the job status
    update_stop_job_status(p_blackout_guid, p_emd_url, p_state, 
                           p_message, l_stop_ts);

    -- Finally, end the blackout for the targets, only if we were
    -- successful in contacting the emd
    IF p_state=BLK_JSTATE_STOPPED THEN
        process_end_blackout(p_blackout_guid, l_target_guids, 
                             true, l_stop_ts);
    ELSE
        -- If we're here, we weren't able to contact the emd regarding
        -- the end of the blackout. However, if the blackout has already
        -- expired, we can safely end the blackout (since the agent
        -- knows the duration and will end the blackout when it expires)
        -- TODO
        NULL;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        IF l_targets_on_emd%ISOPEN THEN
            CLOSE l_targets_on_emd;
        END IF;
        RAISE;
END;


-- End the specified blackout, against all targets
PROCEDURE process_end_blackout(p_blackout_guid RAW) IS
l_blackout_status NUMBER;

-- Cursor that fetches all targets that are currently
-- under blackout
CURSOR l_all_targets_cursor(p_blackout_guid RAW) IS 
    SELECT target_guid FROM MGMT_BLACKOUT_FLAT_TARGETS
        WHERE blackout_guid=p_blackout_guid AND 
              job_status IN (BLK_JSTATE_START_PROCESSING,
                   BLK_JSTATE_START_FAILED, BLK_JSTATE_STARTED,
                   BLK_JSTATE_STOP_FAILED)
     ORDER BY target_guid;

l_target_guids MGMT_USER_GUID_ARRAY;
l_new_state NUMBER;

BEGIN
    -- Lock the record in the master blackout table 
    l_blackout_status := lock_blackout(p_blackout_guid);
    
    IF l_blackout_status < 0 THEN
        RETURN;
    END IF;

    -- When the blackout is in one of (START_PROCESSING,
    -- MODIFY_PENDING, STOP_PENDING), we raise an exception
    -- that causes the job to fail. The job will be retried
    -- after an interval.
    IF l_blackout_status = BLK_STATE_START_PENDING THEN
        NULL;
    ELSIF l_blackout_status=BLK_STATE_START_PROCESSING THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_START_PROCESS_ERR,
         'The start of the blackout is currently being processed');
    ELSIF l_blackout_status=BLK_STATE_MODIFY_PENDING THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_PENDING_EDIT_ERR,
         'The blackout is currently being edited');
    ELSIF l_blackout_status=BLK_STATE_STOP_PENDING THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_STOP_PENDING_ERR,
         'The blackout is currently being stopped');
    ELSIF l_blackout_status=BLK_STATE_STOPPED OR 
          l_blackout_status=BLK_STATE_ENDED THEN
        log_error(UNEXPECTED_STATE, 
            'end_blackout: unexpected current state ' || l_blackout_status);
    END IF;

    OPEN l_all_targets_cursor(p_blackout_guid);
    FETCH l_all_targets_cursor BULK COLLECT INTO l_target_guids;
    IF l_all_targets_cursor%ISOPEN THEN
        CLOSE l_all_targets_cursor;
    END IF;

    process_end_blackout(p_blackout_guid, l_target_guids, false);
    set_blackout_status(p_blackout_guid, BLK_MODE_NONE);
    
    -- Schedule the next blackout, if the schedule is repeating
    schedule_next_blackout(p_blackout_guid);

EXCEPTION
    WHEN OTHERS THEN
        IF l_all_targets_cursor%ISOPEN THEN
            CLOSE l_all_targets_cursor;
        END IF;
        RAISE;
END;

-- Process the targets of a stop blackout, or targets that have been
-- removed from an edit blackout. Return a set of urls for emds that
-- must be contacted to end the blackout. These represent emds on
-- which the blackout was succesfully started.
PROCEDURE process_stop_blackout_targets(p_blackout_guid RAW,
                                        p_emd_urls_out OUT MGMT_JOB_VECTOR_PARAMS) IS
-- All targets for which we can end the blackout
-- without contacting the emd
CURSOR l_targets_cursor(p_blackout_guid RAW) IS 
    SELECT ft.target_guid FROM MGMT_BLACKOUT_FLAT_TARGETS ft,
           MGMT_TARGETS t WHERE
              blackout_guid=p_blackout_guid AND 
              t.target_guid=ft.target_guid AND
              (job_status IN (BLK_JSTATE_START_PROCESSING,
                   BLK_JSTATE_START_FAILED) OR
               emd_url IS NULL) AND
              edit_state=REM_PENDING
    ORDER BY ft.target_guid;

-- EMD URLs for all targets for which the respective emds
-- must be contacted before the blackout is stopped
CURSOR l_emd_cursor(p_blackout_guid RAW) IS 
    SELECT DISTINCT emd_url FROM MGMT_BLACKOUT_FLAT_TARGETS ft,
           MGMT_TARGETS t 
        WHERE blackout_guid=p_blackout_guid AND 
              job_status IN (BLK_JSTATE_STARTED, BLK_JSTATE_STOP_FAILED, BLK_JSTATE_STOP_PROCESSING) AND
              t.target_guid=ft.target_guid AND
              emd_url like '%http%' AND
              edit_state=REM_PENDING;

l_target_guids MGMT_USER_GUID_ARRAY;
l_new_state NUMBER;

BEGIN
    -- We immediately end the blackout on targets whose agents
    -- we were unable to contact at the start of the blackout.
    -- (or repository targets that do not have agents) 
    OPEN l_targets_cursor(p_blackout_guid);
    FETCH l_targets_cursor BULK COLLECT INTO l_target_guids;
    IF l_targets_cursor%ISOPEN THEN
        CLOSE l_targets_cursor;
    END IF;

    process_end_blackout(p_blackout_guid, l_target_guids, true);

    -- On targets whose agents we were able to contact at the
    -- start of the blackout (ie, current job status is STARTED),
    -- we cannot stop the blackout until we are able to contact
    -- the agents and stop the blackout on them. Insert a parameter
    -- containing all emd urls for which this applies. 
    -- Also Include the emd_urls for the agents where the current job
    -- status is STOP_PROCESSING. We have seen cases where the job gets killed
    -- without properly updating the blackout job status. 6318405
    -- This will help when the user tries to stop a partially stopped blackout.
    OPEN l_emd_cursor(p_blackout_guid);
    FETCH l_emd_cursor BULK COLLECT INTO p_emd_urls_out;
    IF l_emd_cursor%ISOPEN THEN
        CLOSE l_emd_cursor;
    END IF;
    
    -- If the agents need to be contacted, the job state is
    -- STOP_PROCESSING. Otherwise, it is STOPPED
    IF p_emd_urls_out IS NOT NULL AND
       p_emd_urls_out.COUNT > 0 THEN
        UPDATE MGMT_BLACKOUT_FLAT_TARGETS SET 
           job_status=BLK_JSTATE_STOP_PROCESSING WHERE
               blackout_guid=p_blackout_guid AND 
               edit_state=REM_PENDING AND
               target_guid IN 
                   (SELECT target_guid FROM MGMT_TARGETS WHERE emd_url IN 
                       (SELECT /*+ CARDINALITY(a 10)*/ * FROM 
                           TABLE(CAST(p_emd_urls_out AS MGMT_JOB_VECTOR_PARAMS)) a ));

    END IF;

    UPDATE MGMT_BLACKOUT_FLAT_TARGETS SET edit_state=NO_EDITS
        WHERE blackout_guid=p_blackout_guid AND
              edit_state=REM_PENDING;


EXCEPTION
    WHEN OTHERS THEN
        IF l_emd_cursor%ISOPEN THEN
            CLOSE l_emd_cursor;
        END IF;

        IF l_targets_cursor%ISOPEN THEN
            CLOSE l_targets_cursor;
        END IF;
        RAISE;


END;

-- Stop the specified agent-side blackout, on all targets
PROCEDURE stop_agent_side_blackout(p_blackout_guid RAW,
                                   p_end_time DATE DEFAULT NULL) IS
l_end_time DATE := p_end_time;
l_blackout_status MGMT_BLACKOUTS.blackout_status%TYPE;
BEGIN
    -- Lock the record in the master blackout table 
    l_blackout_status := lock_blackout(p_blackout_guid);

    IF l_blackout_status < 0 THEN
        RETURN;
    END IF;

    IF l_end_time IS NULL THEN
        l_end_time := sysdate_blackout_time(p_blackout_guid);
    END IF;

    FOR crec IN (SELECT target_guid
                 FROM   MGMT_BLACKOUT_FLAT_TARGETS
                 WHERE  blackout_guid=p_blackout_guid
                 AND    blackout_status=1) LOOP
    BEGIN
        -- Insert an entry into MGMT_BLACKOUT_STATE and we are done
        INSERT INTO MGMT_BLACKOUT_STATE(blackout_guid, target_guid,
                                        collection_timestamp, blackout_code)
        VALUES
            (p_blackout_guid, crec.target_guid, l_end_time, 0);    

    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            NULL;
    END;
    END LOOP;
END;
          
          
-- Put into motion the process of stopping the specified
-- blackout. Insert a job with a parameter that contains a list
-- of distinct emd url's. These represent the agents to be
-- contacted by subsequent steps of the job, to stop the
-- blackout on those agents.
PROCEDURE stop_blackout(p_blackout_guid RAW) IS
l_blackout_status NUMBER;

l_emd_urls MGMT_JOB_VECTOR_PARAMS;
l_new_state NUMBER;
l_upd_time DATE;

l_occurrence_number NUMBER;
l_end_time MGMT_BLACKOUTS.last_end_time%TYPE;
l_updated_by MGMT_BLACKOUTS.last_updated_by%TYPE := MGMT_USER.get_current_em_user;
BEGIN
    -- Lock the record in the master blackout table 
    l_blackout_status := lock_blackout(p_blackout_guid);
    
    IF l_blackout_status < 0 THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_NOT_EXIST_ERR,
          'The specified blackout does not exist');
    END IF;

    -- If the blackout was not started, then stop it
    -- anyway!
    IF l_blackout_status = BLK_STATE_START_PENDING THEN
        NULL;
    ELSIF l_blackout_status = BLK_STATE_START_PROCESSING THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_START_PROCESS_ERR,
            'The start of the blackout is currently being processed');
    ELSIF l_blackout_status = BLK_STATE_STOP_PENDING THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_STOP_PENDING_ERR,
            'The blackout is already pending stop');
    ELSIF l_blackout_status = BLK_STATE_STOPPED OR 
          l_blackout_status = BLK_STATE_ENDED THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_STOPPED_ERR,
            'The blackout has already ended or stopped');
    ELSIF l_blackout_status = BLK_STATE_MODIFY_PENDING THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_PENDING_EDIT_ERR,
          'The last set of edits to the blackout have not yet been committed');
    
    END IF;

    IF EMDW_LOG.p_is_info_set THEN
        EMDW_LOG.info('IN stop_blackout() for blackout ' || p_blackout_guid ||
                      ', current status is ' || 
                      l_blackout_status, MODULE_NAME);
    END IF;

    SELECT occurrence_number INTO l_occurrence_number FROM
        MGMT_BLACKOUTS WHERE blackout_guid=p_blackout_guid;

    -- Check that the caller has enough priviliges to modify the
    -- blackout
    check_modify_privs(p_blackout_guid);

    UPDATE MGMT_BLACKOUT_FLAT_TARGETS SET edit_state=REM_PENDING
        WHERE blackout_guid=p_blackout_guid;

    process_stop_blackout_targets(p_blackout_guid, l_emd_urls);
    
    -- Submit a stop blackout job only if there are targets 
    -- whose agents we need to contact
    IF l_emd_urls IS NOT NULL AND
       l_emd_urls.COUNT > 0 THEN
        -- Set the overall status of the blackout to STOP PENDING
        l_upd_time := sysdate_blackout_time(p_blackout_guid);
        UPDATE MGMT_BLACKOUTS SET blackout_status=BLK_STATE_STOP_PENDING,
               last_updated_time=l_upd_time,
               last_updated_by=l_updated_by
            WHERE blackout_guid=p_blackout_guid;

        submit_stop_blackout_job(p_blackout_guid, l_emd_urls);
    ELSE
        IF EMDW_LOG.p_is_info_set THEN
            EMDW_LOG.info('stop_blackout() for blackout ' || p_blackout_guid ||
                          ': attempting to update status ' ||
                          ' for blackout ' || p_blackout_guid,
                          MODULE_NAME);
        END IF;

        -- Update the overall blackout status, should go to STOPPED
        IF l_blackout_status = BLK_STATE_START_PENDING THEN
            -- Delete the windows, since the blackout never happened
            DELETE  FROM MGMT_BLACKOUT_WINDOWS 
            WHERE   blackout_guid=p_blackout_guid
            AND     occurrence_number=l_occurrence_number;

            l_new_state := update_blackout_status(p_blackout_guid, 
                                                  BLK_MODE_STOP);

        ELSE
            l_new_state := update_blackout_status(p_blackout_guid, 
                                                  BLK_MODE_STOP);
        END IF;

        IF l_new_state != BLK_STATE_STOPPED THEN
            log_error(UNEXPECTED_STATE,
              'stop_blackout(): Unexpected state while stopping blackout. Should be STOPPED, is actually ' || l_new_state);
        END IF;

        MGMT_JOB_ENGINE.blackout_window_ended(p_blackout_guid);

    END IF;


END;


-- Stop the specified blackout (the blackout has not yet expired)
-- It Waits for the blackout to get out of start processing state before it stops it.
-- The stop of the blackout may still happen asynchronously.
-- p_sleep_time is the time to sleep between status checks (in secs).
-- p_total_sleep_time is the total sleep time in secs.
PROCEDURE stop_blackout(p_blackout_guid RAW,
                        p_sleep_time NUMBER,
                        p_total_sleep_time NUMBER) IS
l_blackout_status   NUMBER;
l_sleep_time        NUMBER := p_sleep_time;
l_total_sleep_time  NUMBER := p_total_sleep_time;
l_cur_sleep_time    NUMBER := 0;
BEGIN
    IF l_sleep_time < 0 THEN
        l_sleep_time := BLK_STOP_SLEEP_TIME;
    END IF;

    IF l_total_sleep_time < 0 THEN
        l_total_sleep_time := BLK_STOP_TOTAL_SLEEP_TIME;
    END IF;

    LOOP
        SELECT blackout_status INTO l_blackout_status FROM MGMT_BLACKOUTS
            WHERE blackout_guid=p_blackout_guid;
       
        IF l_blackout_status != BLK_STATE_START_PROCESSING THEN
            EXIT;
        END IF;

        dbms_lock.sleep(l_sleep_time);
        l_cur_sleep_time := l_cur_sleep_time + l_sleep_time;
        IF l_cur_sleep_time >= l_total_sleep_time THEN
            EXIT;
        END IF;
    END LOOP;

    stop_blackout(p_blackout_guid);
END;


-- Set the reason for the blackout
PROCEDURE set_reason(p_blackout_guid RAW, p_reason VARCHAR2) IS
l_reason_id NUMBER;
l_updated_by MGMT_BLACKOUTS.last_updated_by%TYPE := MGMT_USER.get_current_em_user;
l_upd_time DATE := sysdate_blackout_time(p_blackout_guid);
BEGIN
    SELECT reason_id INTO l_reason_id FROM MGMT_BLACKOUT_REASON
       WHERE reason=p_reason AND ROWNUM=1;

    UPDATE MGMT_BLACKOUTS SET 
           reason_id=l_reason_id,
           last_updated_by=l_updated_by,
           last_updated_time=l_upd_time
    WHERE  blackout_guid=p_blackout_guid;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        raise_application_error(MGMT_GLOBAL.INVALID_BLACKOUT_REASON_ERR,
               'Invalid blackout reason ' || p_reason);
END;

-- Process the targets that were removed by an edit_blackout call
PROCEDURE edit_blackout_remove_targets(p_blackout_guid IN RAW,
                                       p_job_id IN RAW,
                                       p_execution_id IN RAW,
                                       p_job_param_name VARCHAR2) IS

CURSOR l_remove_targets(p_blackout_guid RAW) IS
        SELECT target_guid FROM MGMT_BLACKOUT_TARGET_DETAILS WHERE
              blackout_guid=p_blackout_guid AND
              edit_state IN (REM_PENDING, REM_REC_PENDING)
        UNION
        SELECT assoc_target_guid AS TARGET_GUID 
        FROM   MGMT_FLAT_TARGET_ASSOC m, MGMT_TARGETS t
        WHERE  t.target_guid=m.assoc_target_guid 
        AND    m.is_membership = 1
        AND    t.target_type != MGMT_GLOBAL.G_AGENT_TARGET_TYPE 
        AND    m.source_target_guid IN
           (SELECT target_guid FROM MGMT_BLACKOUT_TARGET_DETAILS
            WHERE  blackout_guid=p_blackout_guid 
            AND    edit_state IN (REM_REC_PENDING, INCLUDE_REM_PENDING)
           ) 
        UNION
        SELECT target_guid AS TARGET_GUID FROM 
            MGMT_TARGETS t WHERE emd_url IN 
            (SELECT emd_url FROM MGMT_TARGETS t,
               MGMT_BLACKOUT_TARGET_DETAILS btd WHERE
                 btd.blackout_guid=p_blackout_guid AND
                 btd.edit_state IN (REM_REC_PENDING, INCLUDE_REM_PENDING) AND
                 btd.target_guid=t.target_guid AND
                 t.target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE) AND
             target_type != MGMT_GLOBAL.G_HOST_TARGET_TYPE;
                 


CURSOR l_dependent_targets(p_blackout_guid RAW) IS
        SELECT target_guid FROM MGMT_BLACKOUT_TARGET_DETAILS WHERE
              blackout_guid=p_blackout_guid AND
              edit_state NOT IN (REM_PENDING, REM_REC_PENDING)
        UNION
        SELECT assoc_target_guid AS TARGET_GUID 
        FROM   MGMT_FLAT_TARGET_ASSOC m, MGMT_TARGETS t
        WHERE  t.target_guid=m.assoc_target_guid 
        AND    m.is_membership = 1
        AND    t.target_type != MGMT_GLOBAL.G_AGENT_TARGET_TYPE 
        AND    m.source_target_guid IN
           (SELECT target_guid FROM MGMT_BLACKOUT_TARGET_DETAILS
            WHERE  blackout_guid=p_blackout_guid 
            AND    include_members=1
            AND    edit_state NOT IN (REM_PENDING, REM_REC_PENDING, 
                                      INCLUDE_REM_PENDING)
           )
        UNION
        SELECT target_guid AS TARGET_GUID FROM 
            MGMT_TARGETS WHERE emd_url IN 
            (SELECT emd_url FROM MGMT_TARGETS t,
               MGMT_BLACKOUT_TARGET_DETAILS btd WHERE
                 btd.blackout_guid=p_blackout_guid AND
                 btd.include_members=1 AND
                 btd.edit_state NOT IN (REM_PENDING, REM_REC_PENDING, 
                                        INCLUDE_REM_PENDING) AND
                 btd.target_guid=t.target_guid AND
                 t.target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE) AND
             target_type != MGMT_GLOBAL.G_HOST_TARGET_TYPE;

l_remove_target_guids MGMT_USER_GUID_ARRAY;
l_dependent_target_guids MGMT_USER_GUID_ARRAY;
l_emd_urls MGMT_JOB_VECTOR_PARAMS;
l_param_list MGMT_JOB_PARAM_LIST;

BEGIN
    OPEN l_remove_targets(p_blackout_guid);
    FETCH l_remove_targets BULK COLLECT INTO l_remove_target_guids;

    IF l_remove_targets%ISOPEN THEN
        CLOSE l_remove_targets;
    END IF;

    OPEN l_dependent_targets(p_blackout_guid);
    FETCH l_dependent_targets BULK COLLECT INTO l_dependent_target_guids;

    IF l_dependent_targets%ISOPEN THEN
        CLOSE l_dependent_targets;
    END IF;

    -- Prep the flattened list of targets to be removed, making
    -- sure that we do not include any targets that show up in
    -- the original target list (the ones not yet deleted) 
    -- or its dependents
    UPDATE MGMT_BLACKOUT_FLAT_TARGETS SET edit_state=REM_PENDING
        WHERE blackout_guid=p_blackout_guid AND
              target_guid IN 
                 (SELECT * FROM TABLE(CAST(l_remove_target_guids AS MGMT_USER_GUID_ARRAY))) AND
              target_guid NOT IN 
                 (SELECT * FROM TABLE(CAST(l_dependent_target_guids AS MGMT_USER_GUID_ARRAY))) AND
              job_status != BLK_JSTATE_STOPPED;

    -- Process all the targets marked for removal
    process_stop_blackout_targets(p_blackout_guid, l_emd_urls);

    -- Add a new parameter to the job specifying the emd's to
    -- contact, for removal
    IF l_emd_urls IS NULL THEN
        l_emd_urls := MGMT_JOB_VECTOR_PARAMS();
    END IF;

    l_param_list := MGMT_JOB_PARAM_LIST();
    l_param_list.extend(1);

    l_param_list(1) := MGMT_JOB_PARAM_RECORD(p_job_param_name, 0, null, 
                                             l_emd_urls);

    MGMT_JOBS.add_job_parameters(p_job_id, p_execution_id, l_param_list);
        
EXCEPTION
    WHEN OTHERS THEN
    IF l_remove_targets%ISOPEN THEN
        CLOSE l_remove_targets;
    END IF;

    IF l_dependent_targets%ISOPEN THEN
        CLOSE l_dependent_targets;
    END IF;

    RAISE;
END;

PROCEDURE update_schedule(p_blackout_guid RAW,
                          p_schedule MGMT_BLACKOUT_SCHEDULE_RECORD,
                          p_duration_source NUMBER) IS
BEGIN
    DELETE FROM MGMT_BLACKOUT_SCHEDULE WHERE 
           blackout_guid=p_blackout_guid;

    INSERT INTO MGMT_BLACKOUT_SCHEDULE(blackout_guid, schedule_index,
        frequency_code,
        start_time, end_time, duration,
        execution_hours, execution_minutes, interval, months, days,
        timezone_info, timezone_offset, timezone_region,
        duration_source) VALUES
     (p_blackout_guid, 1, p_schedule.frequency_code, 
      p_schedule.start_time, p_schedule.end_time, 
      p_schedule.duration,
      p_schedule.execution_hours,
      p_schedule.execution_minutes, p_schedule.interval,
      p_schedule.months, p_schedule.days,
      p_schedule.timezone_info, 
      p_schedule.timezone_offset,
      p_schedule.timezone_region,
      p_duration_source);

END;

-- Reschedule the start job of the blackout
PROCEDURE reschedule_start_job(p_blackout_guid RAW,
                               p_schedule MGMT_BLACKOUT_SCHEDULE_RECORD,
                               p_current_status NUMBER) IS
l_start_job_name MGMT_JOB.job_name%TYPE;
l_start_job_owner MGMT_JOB.job_owner%TYPE;
l_blackout_name MGMT_BLACKOUTS.blackout_name%TYPE;
l_start_job_id MGMT_JOB.job_id%TYPE;
l_execution_id RAW(16);
l_schedule MGMT_BLACKOUT_SCHEDULE_RECORD := p_schedule;
l_end_job_id MGMT_BLACKOUTS.end_job_id%TYPE;
l_duration_updated BOOLEAN := false;

l_duration_source NUMBER;
l_upd_time DATE;
l_updated_by MGMT_BLACKOUTS.last_updated_by%TYPE := MGMT_USER.get_current_em_user;
l_start_time DATE;
l_end_time DATE;
l_occurrence_number MGMT_BLACKOUTS.occurrence_number%TYPE;

l_blackout_targets MGMT_USER_GUID_ARRAY;
l_blackout_tzs SMP_EMD_STRING_ARRAY;

l_tz_region MGMT_JOB_EXEC_SUMMARY.timezone_region%TYPE;

l_start_time_utc DATE;
l_end_time_utc DATE;
BEGIN
    -- Cancel the old start job
    SELECT job_name, job_owner, blackout_name INTO 
           l_start_job_name, l_start_job_owner, l_blackout_name 
    FROM   MGMT_BLACKOUTS b, MGMT_JOB j
    WHERE  b.start_job_id=j.job_id
    AND    b.blackout_guid=p_blackout_guid;

    IF l_start_job_name IS NULL THEN
        -- There must be a start job id
        log_error(START_JOB_NOT_FOUND, 'edit_schedule() did not find start job for blackout ' || p_blackout_guid);
    ELSE
        MGMT_JOBS.stop_all_executions(l_start_job_name, 
                                      l_start_job_owner);
    END IF;

    l_start_job_id :=
        schedule_blackout(p_blackout_guid, l_blackout_name, true,
                          l_schedule, l_duration_source, l_execution_id);
      
    -- Update the new schedule
    update_schedule(p_blackout_guid, l_schedule, l_duration_source);

    -- Obtain the start time of the scheduled job. This will be the
    -- new scheduled time
    l_start_time := get_blackout_start_time(l_start_job_id, l_tz_region);
    l_upd_time := sysdate_blackout_time(p_blackout_guid);
    l_start_time_utc := MGMT_GLOBAL.to_utc(l_start_time, l_tz_region);

    UPDATE MGMT_BLACKOUTS 
    SET    scheduled_time=l_start_time,
           last_updated_time=l_upd_time,
           last_updated_by=l_updated_by,
           start_job_id=l_start_job_id
    WHERE  blackout_guid=p_blackout_guid
    RETURNING occurrence_number INTO l_occurrence_number;

    IF l_schedule.duration=-1 THEN
        l_end_time := null;
        l_end_time_utc := null;
    ELSE
        l_end_time := l_start_time+(l_schedule.duration/(60*24));
        l_end_time_utc := MGMT_GLOBAL.to_utc(l_end_time, l_tz_region);
    END IF;

    -- If the blackout is already under way, do nothing to the
    -- blackout windows
    IF p_current_status != BLK_STATE_START_PENDING THEN
        RETURN;
    END IF;

    -- Using the current occurrence number, update the blackout
    -- windows for the upcoming occurrence
    SELECT t.target_guid, t.timezone_region BULK COLLECT INTO
           l_blackout_targets, l_blackout_tzs
    FROM   MGMT_BLACKOUT_WINDOWS w, MGMT_TARGETS t
    WHERE  blackout_guid=p_blackout_guid
    AND    occurrence_number=l_occurrence_number
    AND    t.target_guid=w.target_guid;

    -- Note: the blackout windows are set in agent timezone
    IF l_blackout_targets IS NOT NULL AND l_blackout_targets.COUNT > 0 THEN
        FOR i IN 1..l_blackout_targets.COUNT LOOP
            UPDATE MGMT_BLACKOUT_WINDOWS
            SET    start_time=MGMT_GLOBAL.from_utc(l_start_time_utc, 
                                                   l_blackout_tzs(i)),
                   end_time=decode(l_end_time_utc, null, null,
                                   MGMT_GLOBAL.from_utc(l_end_time_utc, 
                                                   l_blackout_tzs(i)))
            WHERE  blackout_guid=p_blackout_guid
            AND    occurrence_number=l_occurrence_number
            AND    target_guid=l_blackout_targets(i);
            
        END LOOP;    
    END IF;
END;

-- Edit the schedule. If the OUT parameter p_contact_agent_out
-- is set to TRUE, then the calling code (edit_blackout()) 
-- must contact all agents contributing to this blackout
-- and attempt to reestablish the blackout on the agents
PROCEDURE edit_schedule(p_blackout_guid RAW, 
                        p_occurrence_number NUMBER,
                        p_schedule MGMT_BLACKOUT_SCHEDULE_RECORD,
                        p_current_status NUMBER,
                        p_contact_agent_out OUT BOOLEAN,
                        p_new_end_time_out OUT DATE) IS

l_schedule MGMT_BLACKOUT_SCHEDULE_RECORD := p_schedule;
l_orig_schedule MGMT_BLACKOUT_SCHEDULE_RECORD;
l_upd_time DATE;
l_duration_updated BOOLEAN := false;
l_duration_source NUMBER;

l_end_job_id MGMT_JOB.job_id%TYPE;
l_updated_by MGMT_BLACKOUTS.last_updated_by%TYPE := MGMT_USER.get_current_em_user;
BEGIN
    p_contact_agent_out := false;
    -- Start with the original schedule
    SELECT MGMT_BLACKOUT_SCHEDULE_RECORD(frequency_code, start_time,
                                         end_time,
                                         execution_hours,
                                         execution_minutes, interval,
                                         months, days, duration,
                                         timezone_info, 
                                         timezone_offset,
                                         timezone_region),
             duration_source 
        INTO l_orig_schedule, l_duration_source FROM 
          MGMT_BLACKOUT_SCHEDULE WHERE
            blackout_guid=p_blackout_guid;

    IF p_current_status=BLK_STATE_START_PENDING THEN
        IF p_occurrence_number > 1 THEN
            -- Preserve the start time of the original schedule
            l_schedule.start_time := l_orig_schedule.start_time;
        END IF;

        -- All schedule changes are allowed
        reschedule_start_job(p_blackout_guid, l_schedule,
                             BLK_STATE_START_PENDING);
    ELSE
        -- For REPEATING blackouts, all schedule changes are allowed,
        -- but will take effect only for the next occurrence
        IF l_schedule.frequency_code != ONE_TIME_FREQUENCY_CODE THEN
            l_schedule.start_time := l_orig_schedule.start_time;
            reschedule_start_job(p_blackout_guid, l_schedule, 
                                 p_current_status);
            RETURN;
        END IF;

        -- For one-time blackouts, the only schedule change allowed 
        -- is extension of duration

/****
        Ignoring this check for now since the console does not seem
        to be passing the right information
        IF l_schedule.frequency_code != p_schedule.frequency_code OR
           l_schedule.timezone_info != p_schedule.timezone_info OR
           l_schedule.start_time != p_schedule.start_time OR
           l_schedule.timezone_offset != p_schedule.timezone_offset THEN
            raise_application_error(MGMT_GLOBAL.BLK_INVALID_SCHEDULE_ERR,
             'After the blackout has started, only the duration can be changed');
        END IF;
 ****/
      
        IF l_orig_schedule.end_time IS NOT NULL THEN
            -- The old schedule had an end-time/duration
            -- The end time can be changed, and must be greater than
            -- the last end time
            IF l_schedule.duration > 0 AND 
               l_orig_schedule.duration != l_schedule.duration THEN
                IF l_schedule.duration < l_orig_schedule.duration THEN
                    raise_application_error(MGMT_GLOBAL.BLK_INVALID_SCHEDULE_ERR,
                     'Duration can be only be increased after blackout starts');
                END IF;
                l_orig_schedule.duration := l_schedule.duration;
                l_orig_schedule.end_time := l_orig_schedule.start_time +
                           (l_orig_schedule.duration/(60*24));
                -- update timzezone info to replace timzone delta with timezone region
                l_orig_schedule.timezone_info   := l_schedule.timezone_info;
                l_orig_schedule.timezone_offset := l_schedule.timezone_offset;
                l_orig_schedule.timezone_region := l_schedule.timezone_region;
                l_duration_updated := TRUE;

            ELSIF l_schedule.end_time IS NOT NULL AND
               l_orig_schedule.end_time != l_schedule.end_time THEN
                IF l_schedule.end_time < l_orig_schedule.end_time THEN
                    raise_application_error(MGMT_GLOBAL.BLK_INVALID_SCHEDULE_ERR,
                     'Duration can be only be increased after blackout starts');
                END IF;
                l_orig_schedule.end_time := l_schedule.end_time;
                l_orig_schedule.duration := (l_orig_schedule.end_time-l_orig_schedule.start_time)*60*24;
                -- update timzezone info to replace timzone delta with timezone region
                l_orig_schedule.timezone_info   := l_schedule.timezone_info;
                l_orig_schedule.timezone_offset := l_schedule.timezone_offset;
                l_orig_schedule.timezone_region := l_schedule.timezone_region;                
                l_duration_updated := TRUE;
            END IF;
        ELSE
            -- The old schedule was indefinite. We will allow setting of
            -- a definite end time/duration
            IF l_schedule.duration > 0 THEN
                l_orig_schedule.duration := l_schedule.duration;
                l_orig_schedule.end_time := l_orig_schedule.start_time +
                           (l_schedule.duration/(60*24));
                -- update timzezone info to replace timzone delta with timezone region
                l_orig_schedule.timezone_info   := l_schedule.timezone_info;
                l_orig_schedule.timezone_offset := l_schedule.timezone_offset;
                l_orig_schedule.timezone_region := l_schedule.timezone_region;                           
                l_duration_updated := TRUE;
            ELSIF l_schedule.end_time IS NOT NULL THEN
                l_orig_schedule.end_time := l_schedule.end_time;
                l_orig_schedule.duration := (l_orig_schedule.end_time-l_orig_schedule.start_time)*60*24;
                -- update timzezone info to replace timzone delta with timezone region
                l_orig_schedule.timezone_info   := l_schedule.timezone_info;
                l_orig_schedule.timezone_offset := l_schedule.timezone_offset;
                l_orig_schedule.timezone_region := l_schedule.timezone_region;                
                l_duration_updated := TRUE;
            END IF;            

            
        END IF;

        IF l_orig_schedule.end_time IS NOT NULL AND
           l_duration_updated THEN
            update_schedule(p_blackout_guid, l_orig_schedule, l_duration_source);
            l_end_job_id := submit_end_blackout_job(p_blackout_guid, true);
            p_contact_agent_out := true;
            p_new_end_time_out := l_orig_schedule.end_time;

            l_upd_time := sysdate_blackout_time(p_blackout_guid);
            UPDATE MGMT_BLACKOUTS SET 
               last_updated_time=l_upd_time,
               last_updated_by=l_updated_by,
               end_job_id=l_end_job_id WHERE 
                   blackout_guid=p_blackout_guid;
        END IF;
    END IF;

END;

                                               
-- Edit the blackout. Parameters have the same semantics as in
-- MGMT_BLACKOUT.edit_blackout()
--
PROCEDURE edit_blackout(p_blackout_guid IN RAW,
                        p_description IN VARCHAR2,
                        p_schedule IN MGMT_BLACKOUT_SCHEDULE_RECORD,
                        p_reason IN VARCHAR2,
                        p_job_flag IN NUMBER,
                        p_targets_add IN MGMT_BLACKOUT_TARGET_LIST,
                        p_targets_remove IN MGMT_BLACKOUT_TARGET_LIST) IS
l_current_status NUMBER;
l_include_members NUMBER;
l_edit_state NUMBER;
l_target_guid RAW(16);

l_job_id RAW(16);
l_execution_id RAW(16);
l_upd_time DATE;
l_duration_extended BOOLEAN := false;
l_new_end_time DATE;
l_scheduled_time MGMT_BLACKOUTS.scheduled_time%TYPE;
l_job_flag MGMT_BLACKOUTS.job_flag%TYPE;

l_occurrence_number NUMBER;
l_frequency_code MGMT_BLACKOUT_SCHEDULE.frequency_code%TYPE;

l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user;
l_blk_owner MGMT_BLACKOUTS.created_by%TYPE := NULL;
l_switch_context_on_exit BOOLEAN := FALSE;

BEGIN
    l_current_status := lock_blackout(p_blackout_guid);

    IF l_current_status < 0 THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_NOT_EXIST_ERR,
          'The specified blackout does not exist');
    END IF;

    IF l_current_status = BLK_STATE_STOPPED OR 
       l_current_status = BLK_STATE_ENDED OR 
       l_current_status = BLK_STATE_END_PARTIAL THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_STOPPED_ERR,
           'Cannot edit a blackout that has ended/stopped');
    ELSIF l_current_status = BLK_STATE_START_PROCESSING OR
          l_current_status = BLK_STATE_START_PARTIAL THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_START_PROCESS_ERR,
            'The start of the blackout is currently being processed');
    ELSIF l_current_status = BLK_STATE_STOP_PENDING THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_STOP_PENDING_ERR,
           'Cannot edit a blackout that is STOP PENDING');
    ELSIF l_current_status = BLK_STATE_MODIFY_PENDING THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_PENDING_EDIT_ERR,
          'The last set of edits to the blackout have not yet been committed');
    
    END IF;

    check_modify_privs(p_blackout_guid);

    IF p_targets_add IS NULL OR p_targets_remove IS NULL THEN
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
          'p_targets_add and p_targets_remove must be non-null');
    END IF;

    SELECT created_by INTO l_blk_owner 
      FROM MGMT_BLACKOUTS
     WHERE blackout_guid=p_blackout_guid;

    IF l_blk_owner != l_current_user THEN
        -- Before switching context to the blackout owner, check that
        -- the current user has operator on the targets being added
        FOR i IN 1..p_targets_add.COUNT
        LOOP
            IF p_targets_add(i).target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE THEN
                raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
                 'Agent targets cannot be directly blacked out');
            END IF;

            -- The current user must have OPERATOR privilege over
            -- each target being added, and its dependent targets
            check_operator_privs(p_targets_add(i).target_name,
                                 p_targets_add(i).target_type);
        END LOOP;

        SETEMUSERCONTEXT(l_blk_owner, MGMT_USER.OP_SET_IDENTIFIER);
        l_switch_context_on_exit := TRUE;
    END IF;

    IF p_description IS NOT NULL THEN
        UPDATE MGMT_BLACKOUTS SET blackout_desc=p_description WHERE
            blackout_guid=p_blackout_guid;
    END IF;

    IF p_reason IS NOT NULL THEN
        set_reason(p_blackout_guid, p_reason);        
    END IF;

    UPDATE MGMT_BLACKOUTS SET job_flag=p_job_flag WHERE
        blackout_guid=p_blackout_guid
    RETURNING occurrence_number INTO l_occurrence_number;

    -- Edit the schedule. 
    IF p_schedule IS NOT NULL THEN
        edit_schedule(p_blackout_guid, l_occurrence_number,
                      p_schedule, 
                      l_current_status, l_duration_extended,
                      l_new_end_time);
    END IF;

    IF p_targets_remove.COUNT > 0 OR
       p_targets_add.COUNT > 0 OR 
       p_schedule IS NOT NULL THEN
        -- Clear the edit state of all targets in the blackout,
        -- prepping them for the mess to follow
        UPDATE MGMT_BLACKOUT_TARGET_DETAILS SET edit_state=NO_EDITS
            WHERE blackout_guid=p_blackout_guid;

        UPDATE MGMT_BLACKOUT_FLAT_TARGETS SET edit_state=NO_EDITS
            WHERE blackout_guid=p_blackout_guid;

        -- Set any STOP FAILED's from the previous edit to STARTED
        UPDATE MGMT_BLACKOUT_FLAT_TARGETS SET job_status=BLK_JSTATE_STARTED
            WHERE blackout_guid=p_blackout_guid AND
                  job_status=BLK_JSTATE_STOP_FAILED;
    ELSE
        IF l_switch_context_on_exit THEN
            SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
        END IF;
        -- At this point We need to update the last_updated_by 
        UPDATE MGMT_BLACKOUTS SET                  
           last_updated_by=l_current_user
          WHERE blackout_guid=p_blackout_guid;
        RETURN;
    END IF;

    IF l_duration_extended AND p_targets_remove.COUNT > 0 AND
       l_current_status != BLK_STATE_START_PENDING THEN
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
          'Blackout duration cannot be extended when targets are being removed');
    END IF;

    -- Do the targets to remove, first
    -- TODO This won't catch invalid targets
   l_upd_time := sysdate_blackout_time(p_blackout_guid);
    UPDATE MGMT_BLACKOUT_TARGET_DETAILS td 
       SET edit_state=decode(include_members, 1, REM_REC_PENDING,REM_PENDING),
           last_updated_time=l_upd_time
        WHERE blackout_guid=p_blackout_guid AND
              target_guid IN 
        (SELECT target_guid FROM MGMT_TARGETS WHERE
           (target_name, target_type) IN
              (SELECT /*+ CARDINALITY(a 10)*/ target_name, target_type FROM
                  TABLE(CAST(p_targets_remove AS MGMT_BLACKOUT_TARGET_LIST)) a ));  
    -- Now loop through the targets to add
    FOR i IN 1..p_targets_add.COUNT
    LOOP
    BEGIN
        IF p_targets_add(i).target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE THEN
            raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
             'Agent targets cannot be directly blacked out');
        END IF;

        -- The current user must have OPERATOR privilege over
        -- each target being added, and its dependent targets
        check_operator_privs(p_targets_add(i).target_name,
                             p_targets_add(i).target_type);

        l_upd_time := sysdate_blackout_time(p_blackout_guid);
        INSERT INTO MGMT_BLACKOUT_TARGET_DETAILS(blackout_guid, target_guid,
               include_members, last_updated_time, edit_state)
        SELECT p_blackout_guid, target_guid,
               p_targets_add(i).include_members,
               l_upd_time, ADD_PENDING
           FROM
               MGMT_TARGETS WHERE 
                   target_name=p_targets_add(i).target_name AND
                   target_type=p_targets_add(i).target_type;
         
        IF SQL%ROWCOUNT = 0 THEN
            -- The target was not found, or did not exist in the target list
            raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR,
                 p_targets_add(i).target_name || ':' || 
                 p_targets_add(i).target_type);
        END IF;
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            -- The target already exists. Check for changes to the
            -- 'include_members' column
            SELECT include_members, edit_state, t.target_guid INTO 
               l_include_members, l_edit_state, l_target_guid FROM
                 MGMT_BLACKOUT_TARGET_DETAILS td, MGMT_TARGETS t WHERE
                    blackout_guid=p_blackout_guid AND
                    t.target_guid=td.target_guid AND
                    t.target_name=p_targets_add(i).target_name AND
                    t.target_type=p_targets_add(i).target_type;

            IF l_include_members != p_targets_add(i).include_members THEN
                IF p_targets_add(i).include_members=1 THEN
                    l_edit_state := INCLUDE_ADD_PENDING;
                ELSE
                    l_edit_state := INCLUDE_REM_PENDING;
                END IF;
            ELSE
                l_edit_state := NO_EDITS;
            END IF;

            UPDATE MGMT_BLACKOUT_TARGET_DETAILS SET 
                   edit_state=l_edit_state,
                   include_members=p_targets_add(i).include_members
                WHERE blackout_guid=p_blackout_guid AND
                      target_guid=l_target_guid;
                   
    END;   
    END LOOP;


    -- If the current state of the blackout is SCHEDULED, then
    -- there is no need to submit a job. Just recompute the
    -- flat list and we're done
    IF l_current_status = BLK_STATE_START_PENDING THEN
        DELETE FROM MGMT_BLACKOUT_TARGET_DETAILS WHERE
            blackout_guid=p_blackout_guid AND
            edit_state IN (REM_PENDING, REM_REC_PENDING);
        compute_flat_target_list(p_blackout_guid, true);
 
        IF l_switch_context_on_exit THEN
            SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
        END IF;
        -- At this point We need to update the last_updated_by 
        UPDATE MGMT_BLACKOUTS SET                  
           last_updated_by=l_current_user
          WHERE blackout_guid=p_blackout_guid;
        RETURN;
    END IF;

    SELECT frequency_code INTO l_frequency_code
    FROM   MGMT_BLACKOUT_SCHEDULE
    WHERE  blackout_guid=p_blackout_guid;

    -- For recurring blackouts, changes always take place on
    -- the next occurrence
    IF l_frequency_code != ONE_TIME_FREQUENCY_CODE THEN
        DELETE FROM MGMT_BLACKOUT_TARGET_DETAILS WHERE
            blackout_guid=p_blackout_guid AND
            edit_state IN (REM_PENDING, REM_REC_PENDING);

        UPDATE MGMT_BLACKOUT_TARGET_DETAILS 
        SET    edit_state=decode(include_members, 1, INCLUDE_ADD_PENDING,
                                 ADD_PENDING)
        WHERE  blackout_guid=p_blackout_guid;

        IF l_switch_context_on_exit THEN
            SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
        END IF;
        -- At this point We need to update the last_updated_by 
        UPDATE MGMT_BLACKOUTS SET                  
           last_updated_by=l_current_user
          WHERE blackout_guid=p_blackout_guid;
        RETURN;
    END IF;

    IF p_targets_add.COUNT > 0 OR p_targets_remove.COUNT > 0 OR
       l_duration_extended THEN
        -- Submit a job to edit the blackout
        submit_edit_blackout_job(p_blackout_guid, l_job_id, l_execution_id);

        process_start_blackout_targets(p_blackout_guid, l_job_id,
                                       l_execution_id, 'start_emd_list',
                                       true, true, l_duration_extended);

        edit_blackout_remove_targets(p_blackout_guid, l_job_id,
                                     l_execution_id, 'end_emd_list');

        -- At this point, all flat targets have been computed with
        -- appropriate states. If the duration of the blackout has been
        -- extended, then we need to set all targets still in the flat
        -- list to START_PROCESSING, so that they get reprocessed
        IF l_duration_extended THEN
            -- Update all blackout windows to have the new end time
            UPDATE  MGMT_BLACKOUT_WINDOWS w 
            SET     end_time=
                        MGMT_BLACKOUT_ENGINE.to_agent_time(w.blackout_guid, 
                                                           w.target_guid, 
                                                           l_new_end_time) 
            WHERE   blackout_guid=p_blackout_guid
            AND     occurrence_number=l_occurrence_number 
            AND     target_guid IN 
                        (SELECT target_guid
                         FROM   MGMT_BLACKOUT_FLAT_TARGETS
                         WHERE  blackout_guid=p_blackout_guid
                         AND    job_status IN (BLK_JSTATE_STARTED, 
                                               BLK_JSTATE_START_PROCESSING,
                                               BLK_JSTATE_START_FAILED)
                        );
                     
            -- If no targets were added, we need to call the job system
            -- callback
            IF p_targets_add.COUNT=0 AND p_targets_remove.COUNT=0 THEN
                SELECT scheduled_time, job_flag INTO 
                   l_scheduled_time, l_job_flag FROM
                      MGMT_BLACKOUTS WHERE blackout_guid=p_blackout_guid;

                MGMT_JOB_ENGINE.blackout_window_started(p_blackout_guid,
                                                    l_job_flag,
                                                    to_utc(p_blackout_guid,
                                                           l_scheduled_time),
                                                    to_utc(p_blackout_guid,
                                                           l_new_end_time));
            END IF;
        END IF;


        -- Note that it is possible that the series of edits caused
        -- all targets to be out of blackout. In that case, the state
        -- of the blackout is STOPPED.
        l_upd_time := sysdate_blackout_time(p_blackout_guid);
        IF update_blackout_status(p_blackout_guid, 
                                  BLK_MODE_EDIT) != BLK_STATE_STOPPED THEN
            UPDATE MGMT_BLACKOUTS SET blackout_status=BLK_STATE_MODIFY_PENDING,
                  last_updated_time=l_upd_time
               WHERE blackout_guid=p_blackout_guid;
        END IF;        
    END IF;

    IF l_switch_context_on_exit THEN
        SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
    END IF;

    -- At this point We need to update the last_updated_by  
    -- as the Edit Operation is Successful for Active Blackouts
    UPDATE MGMT_BLACKOUTS SET                  
       last_updated_by=l_current_user
      WHERE blackout_guid=p_blackout_guid;
    
EXCEPTION
    WHEN OTHERS THEN
        IF l_switch_context_on_exit THEN
            SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
        END IF;
        RAISE;
END;

-- Delete a blackout. I can only delete a blackout if the
-- blackout has expired (the status is STOPPED)
PROCEDURE delete_blackout(p_blackout_guid RAW) IS
l_blackout_status NUMBER;
BEGIN
    l_blackout_status := lock_blackout(p_blackout_guid);

    IF l_blackout_status < 0 THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_NOT_EXIST_ERR,
          'The specified blackout does not exist');
    ELSIF l_blackout_status != BLK_STATE_STOPPED AND
          l_blackout_status != BLK_STATE_ENDED THEN
        raise_application_error(MGMT_GLOBAL.BLACKOUT_NOT_STOPPED_ERR,
          'Cannot delete a blackout that has not ended or was not stopped');
    END IF;

    check_modify_privs(p_blackout_guid, false);

    DELETE FROM MGMT_BLACKOUT_STATE WHERE blackout_guid=p_blackout_guid;
    DELETE FROM MGMT_BLACKOUT_FLAT_TARGETS WHERE 
           blackout_guid=p_blackout_guid;
    DELETE FROM MGMT_BLACKOUT_TARGET_DETAILS WHERE 
           blackout_guid=p_blackout_guid;
    DELETE FROM MGMT_BLACKOUT_SCHEDULE WHERE 
           blackout_guid=p_blackout_guid;
    DELETE FROM MGMT_BLACKOUTS WHERE 
           blackout_guid=p_blackout_guid;
END;

                         
-- Get details for the specified blackout
PROCEDURE get_blackout_details(p_blackout_guid RAW,
                               p_emd_url VARCHAR2,
                               p_blackout_details_out OUT BLACKOUT_CURSOR,
                               p_start_time_out OUT DATE,
                               p_end_time_out OUT DATE,
                               p_targets_out OUT TARGET_CURSOR,
                               p_blackout_job_state IN NUMBER DEFAULT -1) IS
l_start_time DATE;
l_end_time DATE;
l_target_guid MGMT_TARGETS.target_guid%TYPE;

l_occurrence_number MGMT_BLACKOUTS.occurrence_number%TYPE;
BEGIN
    -- First get information about the blackout
    OPEN p_blackout_details_out FOR
        SELECT blackout_name, created_by, blackout_status FROM
            MGMT_BLACKOUTS WHERE blackout_guid=p_blackout_guid;
            
    SELECT occurrence_number INTO l_occurrence_number
    FROM   MGMT_BLACKOUTS
    WHERE  blackout_guid=p_blackout_guid;

    -- Obtain the blackout start and end times from the
    -- blackout windows
    BEGIN
        -- If the same blackout is submitted, deleted, and resubmitted
        -- multiple times, there will be multiple blackout window entries 
        -- with the same occurrence number since we end up using the
        -- same blackout guid.
        -- These multiple windows will be in strictly monotonically 
        -- increasing order: just choose the latest one.
        -- It does not matter which target, since the windows should
        -- be identical for all targets on a given agent
        SELECT MAX(start_time)
        INTO   l_start_time
        FROM   MGMT_BLACKOUT_WINDOWS w, MGMT_TARGETS t
        WHERE  blackout_guid=p_blackout_guid
        AND    w.target_guid=t.target_guid
        AND    t.emd_url=p_emd_url
        AND    w.occurrence_number=l_occurrence_number;

        -- Select the end time corresponding to this start time
        -- Why do we query separately for the end time? Coz there
        -- can be multiple entries with the same occurrence 
        -- number if the blackout is resubmitted multiple times,
        -- and the most recent end_time could be null
        FOR crec IN (SELECT  end_time
                     FROM    MGMT_BLACKOUT_WINDOWS w, MGMT_TARGETS t
                     WHERE   blackout_guid=p_blackout_guid
                     AND     w.target_guid=t.target_guid
                     AND     t.emd_url=p_emd_url
                     AND     w.occurrence_number=l_occurrence_number
                     AND     start_time=l_start_time
                     ORDER BY end_time) LOOP
            IF crec.end_time IS NULL THEN
                l_end_time := null;
                EXIT;
            ELSE
                l_end_time := crec.end_time;
            END IF;
        END LOOP;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- This cannot happen! This would mean there are no windows set
        -- up for the blackout
        l_start_time := null;
        l_end_time := null;

        log_error(WINDOWS_NOT_FOUND, 'No blackout windows found for blackout ' || p_blackout_guid || ' on agent ' || p_emd_url);
    END;

    p_start_time_out := l_start_time;
    p_end_time_out := l_end_time;

    IF (p_blackout_job_state = -1) THEN
      OPEN p_targets_out FOR
         SELECT target_name, target_type FROM 
           MGMT_TARGETS t, MGMT_BLACKOUT_FLAT_TARGETS ft WHERE 
               ft.blackout_guid=p_blackout_guid AND
               t.target_guid=ft.target_guid AND 
               t.emd_url=p_emd_url AND
               ft.blackout_status=1 AND
               job_status IN
                   (BLK_JSTATE_START_PROCESSING,
                    BLK_JSTATE_STARTED,
                    BLK_JSTATE_START_FAILED);
    ELSE
      -- if p_blackout_job_state is specified, then return the
      -- targets for which blackout job is in that state
      OPEN p_targets_out FOR
         SELECT target_name, target_type FROM 
           MGMT_TARGETS t, MGMT_BLACKOUT_FLAT_TARGETS ft WHERE 
               ft.blackout_guid=p_blackout_guid AND
               t.target_guid=ft.target_guid AND 
               t.emd_url=p_emd_url AND
               job_status = p_blackout_job_state;
   END IF;
        
END;


-- Called by the edit jobs after they're done...
PROCEDURE cleanup_after_edit(p_blackout_guid IN RAW) IS
l_failed_target_count NUMBER;
BEGIN
    -- Check if all edits succeeded
    SELECT count(*) INTO l_failed_target_count FROM
        MGMT_BLACKOUT_FLAT_TARGETS WHERE 
            blackout_guid=p_blackout_guid AND
            job_status=BLK_JSTATE_STOP_FAILED;

    IF EMDW_LOG.p_is_debug_set THEN
        EMDW_LOG.debug('Failed target count is ' || 
                         l_failed_target_count,MODULE_NAME);
    END IF;

    -- Delete the targets that were removed. We can
    -- safely delete all targets with propogate set to 0
    -- whose status in the flat targets table was STOPPED
    DELETE FROM MGMT_BLACKOUT_TARGET_DETAILS td WHERE
        blackout_guid=p_blackout_guid AND
        edit_state=REM_PENDING AND
        include_members!=1 AND EXISTS      
           (SELECT 1 FROM MGMT_BLACKOUT_FLAT_TARGETS WHERE
               blackout_guid=p_blackout_guid AND
               target_guid=td.target_guid AND
               job_status=BLK_JSTATE_STOPPED);

    -- Remove all targets only if the edit succeeded. The
    -- blackout can be edited again if necessary
    IF l_failed_target_count=0 THEN
        DELETE FROM MGMT_BLACKOUT_TARGET_DETAILS td WHERE
            blackout_guid=p_blackout_guid AND
            edit_state=REM_PENDING;

    END IF;

    DELETE FROM MGMT_BLACKOUT_FLAT_TARGETS WHERE 
        blackout_guid=p_blackout_guid AND
        job_status=BLK_JSTATE_STOPPED;

    set_blackout_status(p_blackout_guid, BLK_MODE_EDIT);
END;

-- Called by the user model when the user model
-- grants privileges for the user on a target
PROCEDURE grant_priv_callback(p_user_names IN SMP_EMD_STRING_ARRAY,
                              p_priv_names IN SMP_EMD_STRING_ARRAY,
                              p_target_guids IN MGMT_USER_GUID_ARRAY,
                              p_type IN NUMBER) IS
l_state NUMBER;
l_target_guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY();

BEGIN

  -- This should not happen
  IF p_target_guids IS NULL OR p_target_guids.COUNT = 0 THEN
    RETURN;
  END IF;

  -- This means that a system privilege is granted
  -- Recompute all the blackouts owned by the users
  IF p_target_guids.COUNT = 1 and p_target_guids(p_target_guids.FIRST) = MGMT_USER.NO_GUID THEN
     FOR crec IN (SELECT blackout_guid
                    FROM MGMT_BLACKOUTS b
                   WHERE created_by IN (SELECT * FROM TABLE(CAST(p_user_names AS SMP_EMD_STRING_ARRAY))))
     LOOP
       l_state := lock_blackout(crec.blackout_guid);
       
       -- Recompute the flat target list only if the
       -- blackout has not yet started
       IF l_state=BLK_STATE_START_PENDING THEN
           compute_flat_target_list(crec.blackout_guid, true);
       END IF;
     END LOOP;

  ELSE

  -- This callback is only issued for grants of MAINTAIN_TARGET, OPERATOR_TARGET
  -- and FULL_TARGET

    FOR u IN p_user_names.FIRST..p_user_names.LAST
    LOOP

      -- The user has been granted at least OPERATOR privilege on new targets. 
      -- We need to recompute the flat target list of the blackouts owned by
      -- this user that contain groups and hosts with include_member=1
      -- and which the new targets are members of/hosted on.
      -- Bug 5061352: Also include the targets that have been granted privileges now.

      FOR crec IN (SELECT blackout_guid 
                FROM   MGMT_BLACKOUTS b 
                WHERE  created_by = p_user_names(u) 
                AND    blackout_status=BLK_STATE_START_PENDING
                AND EXISTS
                  ((SELECT target_guid FROM MGMT_BLACKOUT_TARGET_DETAILS td
                    WHERE  td.blackout_guid=b.blackout_guid 
                    AND    td.include_members=1
                    AND    td.target_guid IN
                       (SELECT target_guid
                        FROM   MGMT_TARGETS 
                        WHERE  target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE 
                        AND    emd_url IN 
                               (SELECT emd_url FROM MGMT_TARGETS WHERE target_guid IN
                                 (SELECT * FROM TABLE(CAST(p_target_guids AS MGMT_USER_GUID_ARRAY))))
                        UNION ALL
                        SELECT source_target_guid AS target_guid
                        FROM   MGMT_FLAT_TARGET_ASSOC
                        WHERE  is_membership = 1 
                        AND    assoc_target_guid IN
                              (SELECT * FROM TABLE(CAST(p_target_guids AS MGMT_USER_GUID_ARRAY))))
                    )
                    UNION ALL
                    (SELECT ut.column_value
                       FROM TABLE(CAST(p_target_guids AS MGMT_USER_GUID_ARRAY)) ut
                      WHERE EXISTS
                            (SELECT 1
                               FROM MGMT_BLACKOUT_TARGET_DETAILS td
                              WHERE ut.column_value = td.target_guid
                                AND td.blackout_guid=b.blackout_guid )
                    )
                  )
             )
     LOOP
        l_state := lock_blackout(crec.blackout_guid);
       
        -- Recompute the flat target list only if the
        -- blackout has not yet started
        IF l_state=BLK_STATE_START_PENDING THEN
           compute_flat_target_list(crec.blackout_guid, true);
        END IF;
      END LOOP;
    END LOOP;
  END IF;
END;

-- Called by the user model when the user model
-- revokes privileges for the user on a target
PROCEDURE revoke_priv_callback(p_user_names IN SMP_EMD_STRING_ARRAY,
                               p_priv_names IN SMP_EMD_STRING_ARRAY,
                               p_target_guids IN MGMT_USER_GUID_ARRAY,
                               p_type IN NUMBER) IS
l_state NUMBER;

l_target_guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY();
empty_blackout NUMBER := 0;
BEGIN

  IF p_target_guids IS NULL OR p_target_guids.COUNT = 0 THEN
    RETURN;    -- should not happen
  END IF;

  -- This means that a system privilege is granted
  -- Recompute all the blackouts owned by the users
  IF p_target_guids.COUNT = 1 and p_target_guids(p_target_guids.FIRST) = MGMT_USER.NO_GUID THEN
    FOR crec IN (SELECT blackout_guid
                   FROM MGMT_BLACKOUTS
                  WHERE created_by IN (SELECT * FROM TABLE(CAST(p_user_names AS SMP_EMD_STRING_ARRAY))))
    LOOP
       l_state := lock_blackout(crec.blackout_guid);

       -- Recompute the flat target list only if the
       -- blackout has not yet started
       IF l_state=BLK_STATE_START_PENDING THEN
           compute_flat_target_list(crec.blackout_guid, true);
       END IF;
     END LOOP;

  ELSE

    FOR u in p_user_names.FIRST..p_user_names.LAST
    LOOP

      -- Get the list of targets on which the user no longer has at least
      -- OPERATOR_TARGET privilege
     SELECT DISTINCT column_value BULK COLLECT INTO l_target_guids
     FROM TABLE(CAST(p_target_guids AS MGMT_USER_GUID_ARRAY))
     MINUS
     SELECT guid
     FROM   MGMT_PRIV_GRANTS
     WHERE  (grantee = p_user_names(u) OR
             grantee IN (SELECT role_name FROM MGMT_FLAT_ROLE_GRANTS
                         WHERE role_grantee IN
                          (SELECT role_name FROM MGMT_ROLE_GRANTS
                           WHERE grantee = p_user_names(u))) OR
             grantee IN (SELECT role_name FROM MGMT_ROLE_GRANTS
                         WHERE grantee = p_user_names(u)))
     AND     priv_name IN (MGMT_USER.OPERATOR_TARGET,
                           MGMT_USER.FULL_TARGET,
                           MGMT_USER.MAINTAIN_TARGET);

     -- If the user has been granted OPERATOR priv on all targets, we're done
     IF l_target_guids IS NULL OR l_target_guids.COUNT = 0 THEN
       RETURN;
     END IF;

     -- If we are here, the user does not have at least OPERATOR
     -- privilege on the target. Remove the target from the 
     -- target list of all blackouts created by the user
     -- that the target is part of
     FOR crec IN (SELECT b.blackout_guid, td.include_members, 
                  td.target_guid FROM 
                    MGMT_BLACKOUTS b, MGMT_BLACKOUT_TARGET_DETAILS td WHERE
                       b.blackout_guid=td.blackout_guid AND
                       b.created_by = p_user_names(u) AND
                       td.target_guid IN
                        (SELECT * FROM (TABLE(CAST(l_target_guids AS MGMT_USER_GUID_ARRAY)))))
     LOOP
       -- Remove the target from the blackout target list;
       -- this will affect the flat target list only if the
       -- blackout has not yet started
       l_state := lock_blackout(crec.blackout_guid);
       
       -- Recompute the flat target list only if the
       -- blackout has not yet started
       IF l_state=BLK_STATE_START_PENDING THEN
           compute_flat_target_list(crec.blackout_guid, true);
       END IF;

       empty_blackout := 0;
       -- if this blackout now has no targets in it 
       -- because the user has lost privilege - update the description
       -- with appropriate comments
       SELECT COUNT(1)
         INTO empty_blackout
         FROM MGMT_BLACKOUTS b
        WHERE blackout_guid = crec.blackout_guid
          AND NOT EXISTS (SELECT blackout_guid
                            FROM MGMT_BLACKOUT_TARGET_DETAILS btd
                           WHERE btd.blackout_guid = crec.blackout_guid);

       IF empty_blackout = 1 THEN
           -- add comments to the blackout to indicate
           -- that user has lost privs on all targets in this blackout
           IF l_state=BLK_STATE_START_PENDING
           THEN
               -- and blackout is in scheduled state, so we are stopping the blackout
               UPDATE MGMT_BLACKOUTS
                  SET blackout_desc = substr('Targets were removed from this blackout because the blackout creator ' || p_user_names(u) ||' no longer has Operator privileges on them. ' || blackout_desc, 1, 2000)
                WHERE blackout_guid = crec.blackout_guid;
           ELSIF (l_state=BLK_STATE_START_FAILED OR
                  l_state=BLK_STATE_STOPPED OR
                  l_state=BLK_STATE_ENDED)
            THEN
               -- just comment - and leave the blackout as it is..
               UPDATE MGMT_BLACKOUTS
                  SET blackout_desc = substr('Targets were removed from this blackout because the blackout creator ' || p_user_names(u)  ||' no longer has Operator privileges on them. There are no targets associated with it. ' || blackout_desc, 1, 2000)
                WHERE blackout_guid = crec.blackout_guid;
            ELSE
               -- just comment - different one as blkout is in progress.
               UPDATE MGMT_BLACKOUTS
                  SET blackout_desc = substr('Targets were removed from this blackout because the blackout creator ' || p_user_names(u)  ||' no longer has Operator privileges on them. There are no targets associated with it now, but there may be some targets that are currently in blackout as scheduled earlier. ' || blackout_desc, 1, 2000)
                WHERE blackout_guid = crec.blackout_guid;
           END IF;
       END IF;
     END LOOP;

     -- We now need to recompute the flat target list of the blackouts owned
     -- by this user that contain groups and hosts with include_members=1
     -- and which the targets are members of/hosted on.
     FOR crec IN (SELECT blackout_guid 
                FROM   MGMT_BLACKOUTS b 
                WHERE  created_by = p_user_names(u) 
                AND    blackout_status=BLK_STATE_START_PENDING
                AND EXISTS
                   (SELECT target_guid FROM MGMT_BLACKOUT_TARGET_DETAILS td
                    WHERE  td.blackout_guid=b.blackout_guid 
                    AND    td.include_members=1
                    AND    td.target_guid IN
                       (SELECT target_guid
                        FROM   MGMT_TARGETS 
                        WHERE  target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE 
                        AND    emd_url IN 
                               (SELECT emd_url FROM MGMT_TARGETS WHERE target_guid IN
                                 (SELECT * FROM TABLE(CAST(l_target_guids AS MGMT_USER_GUID_ARRAY))))
                        UNION
                        SELECT source_target_guid AS target_guid
                        FROM   MGMT_FLAT_TARGET_ASSOC
                        WHERE  is_membership = 1 
                        AND    assoc_target_guid IN
                              (SELECT * FROM TABLE(CAST(l_target_guids AS MGMT_USER_GUID_ARRAY))))
                   ))
     LOOP
       l_state := lock_blackout(crec.blackout_guid);
       -- Recompute the flat target list only if the
       -- blackout has not yet started
       IF l_state=BLK_STATE_START_PENDING THEN
           compute_flat_target_list(crec.blackout_guid, true);
       END IF;
     END LOOP;
    END LOOP;
  END IF;
END;

-- Return true if the target is blacked out at the specified
-- time. 
FUNCTION is_blacked_out(p_target_guid RAW,
                        p_timestamp DATE) RETURN BOOLEAN IS
l_windows BLACKOUT_CURSOR;
l_query_time DATE;

l_blackout_guid MGMT_BLACKOUTS.blackout_guid%TYPE;
l_start_time MGMT_BLACKOUT_WINDOWS.start_time%TYPE;
l_end_time MGMT_BLACKOUT_WINDOWS.end_time%TYPE;
l_status MGMT_BLACKOUTS.blackout_status%TYPE;

l_blacked_out BOOLEAN := FALSE;
l_start_blackouts BOOLEAN := FALSE;
l_end_blackouts BOOLEAN := FALSE;

BEGIN
    -- Loop over all blackout windows for the target
    l_windows := get_blackout_windows(p_target_guid, l_query_time);

    LOOP      
        FETCH l_windows INTO l_blackout_guid, l_start_time,
              l_end_time, l_status;

        EXIT WHEN l_windows%NOTFOUND;

        IF p_timestamp >= l_start_time AND 
           (l_end_time IS NULL OR
            p_timestamp <= l_end_time) THEN
            l_blacked_out := TRUE;

            -- Has the blackout start already been processed?
            IF l_status = BLK_STATE_START_PENDING OR
               l_status = BLK_STATE_START_PARTIAL THEN
                l_start_blackouts := true;
            END IF;
        ELSIF l_end_time IS NOT NULL AND
              p_timestamp > l_end_time THEN
            IF l_status != BLK_STATE_ENDED AND
               l_status != BLK_STATE_STOPPED THEN
                l_end_blackouts := TRUE;
            END IF;
        END IF;

    END LOOP;

    IF l_windows%ISOPEN THEN
        CLOSE l_windows;
    END IF;

    -- Insert blackout severities for the target if necessary
    IF l_start_blackouts THEN
        IF EMDW_LOG.p_is_debug_set THEN
            EMDW_LOG.debug('Need to start blackouts for this target',MODULE_NAME);
        END IF;
        start_blackouts_on_target(p_target_guid, p_timestamp);
    END IF;

    -- Insert blackout severities for the target if necessary
    IF l_end_blackouts THEN
        IF EMDW_LOG.p_is_debug_set THEN
            EMDW_LOG.debug('Need to end blackouts for this target',MODULE_NAME);
        END IF;
        end_blackouts_on_target(p_target_guid, p_timestamp);
    END IF;

    RETURN l_blacked_out;

EXCEPTION
    WHEN OTHERS THEN
        IF l_windows%ISOPEN THEN
            CLOSE l_windows;
        END IF;
    RETURN l_blacked_out;
END;

--
-- PURPOSE:
--   Cleanup blackouts as needed whenever a target is deleted
--     The blackout tables that have target info in them are marked
--     as exceptions and will not be cleaned by the admin routines.
--     We remove the target from blackouts and  check if any of these blackouts
--     that are started/Scheduled have no more corresponding
--     targets in the flat targets/target details table and call
--     stop blackout on these. If the blackout is
--        scheduled - this will work ok.
--        in progress - this will work ok.
--
--   IN Parameters:
--     target_name_in : target name of the deleted target
--     target_type_in : target type of the deleted target
--     target_guid_in : target guid of the deleted target
--
--
--   OUT Parameters:
--     NONE
PROCEDURE clean_blk_on_tgt_del
   (target_name_in IN VARCHAR2,
    target_type_in IN VARCHAR2,
    target_guid_in IN RAW)
IS
  l_inconsistent_blkouts BLACKOUT_CURSOR;
  l_blackouts MGMT_BLACKOUT_GUID_ARRAY;
  l_blackout_guid MGMT_BLACKOUTS.blackout_guid%TYPE;
  l_blackout_status MGMT_BLACKOUTS.blackout_status%TYPE;
  l_created_thru MGMT_BLACKOUTS.created_thru%TYPE;
BEGIN
 
  -- Get all blackouts that have this target guid 
  SELECT b.blackout_guid
    BULK COLLECT INTO l_blackouts
    FROM MGMT_BLACKOUTS b
   WHERE EXISTS (SELECT blackout_guid
                        FROM MGMT_BLACKOUT_TARGET_DETAILS btd
                       WHERE btd.blackout_guid = b.blackout_guid 
                         AND btd.target_guid = target_guid_in)
     OR EXISTS (SELECT blackout_guid
                        FROM MGMT_BLACKOUT_FLAT_TARGETS bfd
                       WHERE bfd.blackout_guid = b.blackout_guid 
                         AND bfd.target_guid = target_guid_in);

  -- IF there are no blackouts with this target
  -- we are done
  IF l_blackouts.COUNT = 0
  THEN
    RETURN;
  END IF;

  -- DELETE the target info
  DELETE FROM MGMT_BLACKOUT_TARGET_DETAILS
   WHERE target_guid = target_guid_in;

  DELETE FROM MGMT_BLACKOUT_FLAT_TARGETS
   WHERE target_guid = target_guid_in;

  -- check to see if any of these blackouts no longer
  -- have any targets associated with them
  OPEN l_inconsistent_blkouts FOR
    SELECT b.blackout_guid, b.blackout_status, b.created_thru
      FROM MGMT_BLACKOUTS b
     WHERE (NOT EXISTS (SELECT blackout_guid
                          FROM MGMT_BLACKOUT_TARGET_DETAILS btd
                         WHERE btd.blackout_guid = b.blackout_guid)
            AND NOT EXISTS (SELECT blackout_guid
                          FROM  MGMT_BLACKOUT_FLAT_TARGETS bfd
                         WHERE bfd.blackout_guid = b.blackout_guid))
       OR (b.blackout_guid IN
             (SELECT bl.blackout_guid FROM (TABLE(CAST(l_blackouts AS MGMT_BLACKOUT_GUID_ARRAY))) l, MGMT_BLACKOUTS bl
               WHERE l.column_value = bl.blackout_guid
                 AND bl.created_thru IS NOT NULL));
  LOOP
    FETCH l_inconsistent_blkouts INTO 
        l_blackout_guid, l_blackout_status, l_created_thru;

    EXIT WHEN l_inconsistent_blkouts%NOTFOUND;

    -- add comments to the blackout if possible to indicate
    -- that all targets in this blackout have been deleted
    -- we don't stop it if it is already stopped 
    IF (l_blackout_status = BLK_STATE_START_FAILED OR
        l_blackout_status = BLK_STATE_ENDED OR
        l_blackout_status = BLK_STATE_STOPPED OR
        l_created_thru IS NOT NULL)
    THEN
      -- For agent-side blackouts, the update trigger will recompute the
      -- blackout status
      UPDATE MGMT_BLACKOUTS
         SET blackout_desc = substr('Targets in this blackout were deleted. ' || blackout_desc, 1, 2000)
       WHERE blackout_guid = l_blackout_guid;
    ELSE
      -- Blkout in progress, so we are stopping the blackout
      UPDATE MGMT_BLACKOUTS
         SET blackout_desc = substr('All targets in this blackout were deleted;  blackout will be stopped. ' || blackout_desc, 1, 2000)
       WHERE blackout_guid = l_blackout_guid;
      stop_blackout(l_blackout_guid);
    END IF;
  END LOOP;

  IF l_inconsistent_blkouts%ISOPEN THEN
    CLOSE l_inconsistent_blkouts;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    IF l_inconsistent_blkouts%ISOPEN THEN
      CLOSE l_inconsistent_blkouts;
    END IF;
 
END clean_blk_on_tgt_del;

  -- Purge policy callback procedure for blackout state
  PROCEDURE BLACKOUT_STATE_PURGE(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS) IS

    l_purge_rowids t_rowid_list;
    l_rows_purged NUMBER := 0;
    CURSOR blackout_state_cursor (c_target_guid RAW,
                                  c_purge_till_time DATE) IS
      SELECT bs.ROWID
        FROM MGMT_BLACKOUT_STATE bs,
             (SELECT blackout_guid, max(collection_timestamp) max_coll
                FROM MGMT_BLACKOUT_STATE
               WHERE target_guid = c_target_guid
             AND blackout_code = 0
         AND collection_timestamp < c_purge_till_time
               GROUP BY blackout_guid) max_blk_end
       WHERE bs.target_guid = c_target_guid
         AND bs.blackout_guid = max_blk_end.blackout_guid
         AND bs.collection_timestamp < max_blk_end.max_coll;
  BEGIN

    OPEN blackout_state_cursor(pcb_params.target_guid, pcb_params.purge_upto_time);
    LOOP
      -- Bulk collect row ids
      FETCH blackout_state_cursor BULK COLLECT INTO l_purge_rowids LIMIT 500;

      -- Exit when no more row ids fetched
      EXIT WHEN l_purge_rowids.COUNT <= 0;

      -- BULK delete
      FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST
        DELETE FROM MGMT_BLACKOUT_STATE WHERE ROWID = l_purge_rowids(i);

      COMMIT;

      l_rows_purged := l_rows_purged + l_purge_rowids.COUNT;

      l_purge_rowids.DELETE;

    END LOOP;

    -- Close the cursor if open
    IF (blackout_state_cursor%ISOPEN) THEN
      CLOSE blackout_state_cursor;
    END IF;

    pcb_params.callback_result := 0;
    pcb_params.rows_processed  := l_rows_purged;
    pcb_params.error_code      := 0;
    pcb_params.error_msg       := NULL;

  END BLACKOUT_STATE_PURGE;

-- Purge policy callback procedure for blackouts
PROCEDURE BLACKOUT_PURGE(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS) IS
l_blackout_guids MGMT_USER_GUID_ARRAY;
l_rows_purged NUMBER := 0;

-- Purge until time is supposed to be in repository tz?
CURSOR blackout_cursor (c_purge_till_time DATE) IS
  SELECT    b.blackout_guid
    FROM    (SELECT MAX(end_time) "END_TIME", b.blackout_guid
             FROM   MGMT_BLACKOUT_HISTORY h, MGMT_BLACKOUTS b
             WHERE  h.blackout_guid=b.blackout_guid
             AND    b.blackout_status IN (BLK_STATE_STOPPED, BLK_STATE_ENDED)
             GROUP BY b.blackout_guid) b,
            MGMT_BLACKOUT_SCHEDULE s
    WHERE   b.blackout_guid=s.blackout_guid
    AND     b.end_time < c_purge_till_time; 

BEGIN

    OPEN blackout_cursor(pcb_params.purge_upto_time-1);

    LOOP
        -- Bulk collect row ids
        FETCH blackout_cursor BULK COLLECT INTO l_blackout_guids LIMIT 500;

        -- Exit when no more row ids fetched
        EXIT WHEN l_blackout_guids.COUNT <= 0;

        FOR i IN 1..l_blackout_guids.COUNT LOOP
        BEGIN
            delete_blackout(l_blackout_guids(i));

        EXCEPTION
            WHEN OTHERS THEN
                log_error(0, 'Purge of blackout ' || l_blackout_guids(i) ||
                             ' failed: ' || SQLERRM);
        END;
        END LOOP;

        l_rows_purged := l_rows_purged + l_blackout_guids.COUNT;

        l_blackout_guids.DELETE;

    END LOOP;

    -- Close the cursor if open
    IF (blackout_cursor%ISOPEN) THEN
      CLOSE blackout_cursor;
    END IF;

    pcb_params.callback_result := 0;
    pcb_params.rows_processed  := l_rows_purged;
    pcb_params.error_code      := 0;
    pcb_params.error_msg       := NULL;

END;

--
-- PROCEDURE : get_user_blackouts
--
-- PURPOSE
--
--   this is a callback which will be registered to the user_model pkg 
--   user_model pkg will call this procedure to get a list of blackouts which the 
--   user has
--  
-- PARAMETERS
--
--  user_name_in     - name of the user
--  user_objects_out - list of blackouts will be appended to user_objects_out
--  type_in          - type of user model callback
--
-- NOTES
--
  
PROCEDURE get_user_blackouts(user_name_in      IN VARCHAR2,
                             user_objects_out  OUT MGMT_USER_OBJECTS,
                             type_in           IN NUMBER) IS
                             
l_user_name        VARCHAR2(256) := UPPER(TRIM(user_name_in));
i INTEGER := 0;
l_user_blackouts MGMT_USER_OBJECTS := MGMT_USER_OBJECTS();

CURSOR c_blackouts IS SELECT blackout_name 
FROM MGMT_BLACKOUTS WHERE created_by=l_user_name;
BEGIN
    FOR c IN c_blackouts
    LOOP
        l_user_blackouts.extend(1);
        i := i + 1;
        l_user_blackouts(i) := MGMT_USER_OBJECT(MGMT_USER.USER_OBJECT_BLACKOUT,
                                                c.blackout_name,
                                                null,
                                                MGMT_USER.SYNC_DROP_OBJECT);
    END LOOP;
    user_objects_out := l_user_blackouts;
END;

-- PROCEDURE : drop_user_blackouts
--
-- PURPOSE
--
--   this is a callback which will be registered to the user_model pkg 
--   user_model pkg will call this procedure to reassign the blackouts
--   owned by a user to the repository owner.
--  
-- PARAMETERS
--
--  user_name_in     - name of the user
--  type_in          - type of user model callback
--
-- NOTES
--
        
PROCEDURE drop_user_blackouts(user_name_in      IN VARCHAR2,
                              type_in           IN NUMBER) IS

l_user_name        VARCHAR2(256) := UPPER(TRIM(user_name_in));
l_repository_owner VARCHAR2(256) := MGMT_USER.GET_REPOSITORY_OWNER;
BEGIN    
    reassign_user_blackouts(l_user_name,l_repository_owner,
                            MGMT_USER.USER_REASSIGN_CALLBACK);
END;



--
-- PROCEDURE : has_priv_on_blackout_targets
--
-- PURPOSE
--
--   this procedure checks if the user has OPERATOR_TARGET privalage on
--   all the targets on which the blackout operates on
--  
-- PARAMETERS
--
--  user_name_in     - name of the user
--  blackout_guid_in - guid of the blackout
--
--  RETURNS  : MGMT_USER.USER_HAS_PRIV if the user OPERATOR_TARGET on all the targets of the blackout
--             MGMT_USER.USER_DOES_NOT_HAVE_PRIV if the user does not have the privilege
--  NOTES
--

FUNCTION has_priv_on_blackout_targets(user_name_in      IN VARCHAR2,
                                      blackout_guid_in  IN RAW) RETURN NUMBER IS

CURSOR c_blackout_targets IS SELECT target_guid
FROM   MGMT_BLACKOUT_TARGET_DETAILS 
WHERE  blackout_guid = blackout_guid_in;

has_priv NUMBER := MGMT_USER.USER_HAS_PRIV;


BEGIN
    FOR c IN c_blackout_targets
    LOOP
        has_priv := MGMT_USER.has_priv(user_name_in,MGMT_USER.OPERATOR_TARGET,c.target_guid);
      
        IF has_priv != MGMT_USER.USER_HAS_PRIV THEN
            RETURN MGMT_USER.USER_DOES_NOT_HAVE_PRIV;
        END IF;
        
    END LOOP;

    RETURN MGMT_USER.USER_HAS_PRIV;
END;



-- PROCEDURE : reassign_user_blackouts
--
-- PURPOSE
--
--   this is a callback which will be registered to the user_model pkg 
--   user_model pkg will call this procedure to reassign the blackouts
--   owned by a user to a new user.
--  
-- PARAMETERS
--
--  user_name_in     - name of the user
--  new_user_name_in - the new user name
--  type_in          - type of user model callback
--
-- NOTES
--
        
PROCEDURE reassign_user_blackouts(user_name_in      IN VARCHAR2,
                                  new_user_name_in  IN VARCHAR2,
                                  type_in           IN NUMBER) IS
                                  
l_user_name        VARCHAR2(256) := UPPER(TRIM(user_name_in));
l_new_user_name    VARCHAR2(256) := UPPER(TRIM(new_user_name_in));

CURSOR c_blackouts IS SELECT blackout_guid,blackout_name
FROM   MGMT_BLACKOUTS
WHERE  created_by=l_user_name;

has_operator_target NUMBER;

BEGIN
    
    FOR c IN c_blackouts
    LOOP
            
        has_operator_target := has_priv_on_blackout_targets(l_new_user_name, c.blackout_guid);
        
        IF  has_operator_target = MGMT_USER.USER_HAS_PRIV THEN
            UPDATE MGMT_BLACKOUTS
            SET    created_by = l_new_user_name
            WHERE  blackout_guid =c.blackout_guid;
        ELSE
            mgmt_log.log_error(MGMT_USER.USERMODEL_MODULE_NAME,
                               MGMT_GLOBAL.CALLBACK_FAILED_ERR,
                               'REASSIGN_USER_BLACKOUTS ' || c.blackout_name 
                               || ' created by ' || l_user_name
                               || ' cannot be reassigned to ' || l_new_user_name);
            
        END IF;
    END LOOP;

END;


--
-- PROCEDURE : check_reassign_user_blackouts
--
-- PURPOSE
--
--   this is a callback which will be registered to the user_model pkg 
--   user_model pkg will call this procedure to to check if the blackouts owned 
--   by a user can be deleted or reassigned to another user
--  
-- PARAMETERS
--
--  user_name_in     - name of the user
--  new_user_name_in - the new user name
--  user_objects_out - returns the list of blackouts which are incompatable
--  type_in          - type of user model callback
--
-- NOTES
--
        
PROCEDURE check_reassign_user_blackouts(user_name_in      IN VARCHAR2,
                                        new_user_name_in  IN VARCHAR2,
                                        user_objects_out  OUT MGMT_USER_OBJECTS,
                                        type_in           IN NUMBER) IS

l_user_name        VARCHAR2(256) := UPPER(TRIM(user_name_in));
l_new_user_name    VARCHAR2(256) := UPPER(TRIM(new_user_name_in));
                                    
CURSOR c_blackouts IS SELECT blackout_guid,blackout_name
FROM   MGMT_BLACKOUTS
WHERE  created_by=l_user_name;

has_operator_target NUMBER;
i INTEGER := 0;
l_bad_user_blackouts MGMT_USER_OBJECTS := MGMT_USER_OBJECTS();

BEGIN
    
    FOR c IN c_blackouts
    LOOP
        has_operator_target := has_priv_on_blackout_targets(l_new_user_name, c.blackout_guid);
            
        IF  has_operator_target != MGMT_USER.USER_HAS_PRIV THEN
              l_bad_user_blackouts.extend(1);
              i := i + 1;
              l_bad_user_blackouts(i) := MGMT_USER_OBJECT(
                                                MGMT_USER.USER_OBJECT_BLACKOUT,
                                                c.blackout_name,
                                                null,
                                                MGMT_USER.SYNC_DROP_OBJECT);
        END IF; 
    END LOOP;
    
    user_objects_out := l_bad_user_blackouts;
END;


--
-- PURPOSE: recompute flat target list of blackouts that are in scheduled state
--          and submitted against hosts with include_members=1.
--          when a target gets added/removed to/from a host.
--
-- IN PARAMETERS: see sdk_assoc_pkgdef
--                       p_assoc_def_name VARCHAR2,
  --                     p_source_target_name VARCHAR2,
  --                     p_source_target_type VARCHAR2,
  --                     p_assoc_target_name  VARCHAR2,
  --                     p_assoc_target_type  VARCHAR2,
  --                     p_scope_target_name  VARCHAR2,
  --                     p_scope_target_type  VARCHAR2;
-- OUT PARAMETER: none
-- 
PROCEDURE host_change_callback(p_assoc_def_name VARCHAR2,
                               p_source_target_name VARCHAR2,
                               p_source_target_type VARCHAR2,
                               p_assoc_target_name  VARCHAR2,
                               p_assoc_target_type  VARCHAR2,
                               p_scope_target_name  VARCHAR2,
                               p_scope_target_type  VARCHAR2) AS
BEGIN
    -- The container host is the assoc target
    handle_membership_change(MGMT_TARGET.get_target_guid(p_assoc_target_name,
                                                         p_assoc_target_type));
END host_change_callback;


--
-- PURPOSE: recompute flat target list of blackouts that are in scheduled state
--          and submitted against groups with include_members=1.
--          when a target gets added/removed to/from a group.
--
-- IN PARAMETERS: see sdk_assoc_pkgdef
--                       p_assoc_def_name VARCHAR2,
  --                     p_source_target_name VARCHAR2,
  --                     p_source_target_type VARCHAR2,
  --                     p_assoc_target_name  VARCHAR2,
  --                     p_assoc_target_type  VARCHAR2,
  --                     p_scope_target_name  VARCHAR2,
  --                     p_scope_target_type  VARCHAR2;
-- OUT PARAMETER: none
-- 
PROCEDURE group_change_callback(p_assoc_def_name VARCHAR2,
                                p_source_target_name VARCHAR2,
                                p_source_target_type VARCHAR2,
                                p_assoc_target_name  VARCHAR2,
                                p_assoc_target_type  VARCHAR2,
                                p_scope_target_name  VARCHAR2,
                                p_scope_target_type  VARCHAR2) AS
BEGIN
    -- The container group is the source target
    handle_membership_change(MGMT_TARGET.get_target_guid(p_source_target_name,
                                                         p_source_target_type));
END group_change_callback;

-- Lock all windows specified in the array
PROCEDURE lock_windows(l_windows MGMT_BLACKOUT_WINDOW_ARRAY) IS
l_blackout_guids MGMT_USER_GUID_ARRAY;

l_state  NUMBER;
BEGIN
    SELECT  DISTINCT blackout_guid BULK COLLECT INTO l_blackout_guids
    FROM    (TABLE(CAST(l_windows AS MGMT_BLACKOUT_WINDOW_ARRAY)))
    ORDER BY blackout_guid;

    FOR i IN 1..l_blackout_guids.COUNT LOOP
        l_state := lock_blackout(l_blackout_guids(i));
    END LOOP;        
END;

END MGMT_BLACKOUT_ENGINE;
/

show errors;