Rem Rem $Header: blackout_ui_pkgdef.sql 20-sep-2006.04:59:08 kannatar Exp $ Rem Rem blackout_ui_pkgdef.sql Rem Rem Copyright (c) 2002, 2006, Oracle. All rights reserved. Rem Rem NAME Rem blackout_ui_pkgdef.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem sbhagwat 03/19/06 - Retroactive blackout Rem paachary 06/27/05 - Bug 4381215 (PERF: BLACKOUT CONFIG PAGE TAKES LONG TO COME UP) Rem ssukavan 03/21/05 - added get_targets_full_blackout Rem ssukavan 02/10/05 - update host constant Rem ssukavan 01/03/05 - Added a new function get_targets_oper_priv Rem ssukavan 12/30/04 - modified get_flat_targets_host. Rem mbhalla 11/25/04 - Add procedure to identify Active Blackouts for a Rem Target. Rem vkgarg 10/25/04 - add get_parent_target_types Rem vkgarg 10/21/04 - moving reasons procs to mgmt_blackout_ui pkg Rem vkgarg 10/19/04 - add check_priv_on_group Rem mbhalla 10/05/04 - grabtrans 'vkgarg_botrans' Rem rpatti 09/11/03 - support active/history views Rem rpatti 08/21/03 - support occurrences Rem rpatti 06/18/03 - update for recurring blackouts Rem rpatti 06/30/03 - use created_thru Rem rpatti 02/26/03 - fix for target deletions Rem skini 02/04/03 - Change agent user to SYSTEM Rem rpatti 12/04/02 - support job flag in view Rem rpatti 10/24/02 - add target blackout query Rem rpatti 08/29/02 - continue Rem rpatti 08/16/02 - change param order Rem rpatti 08/01/02 - continue Rem rpatti 07/31/02 - rpatti_blackouts_4 Rem rpatti 07/25/02 - continue Rem rpatti 07/17/02 - rename packages Rem rpatti 06/13/02 - rpatti_blackouts_2 Rem rpatti 05/28/02 - Created Rem -- -- Package: mgmt_blackout_ui -- -- PURPOSE: -- This package provides access to the blackout information. A blackout -- may span on multiple targets or nodes. Each blackout has a blackout schedule -- associated with it. -- -- -- NOTES: -- A target is uniquely identified by its name and its type. -- A blackout is uniquely identified by its name and user who creates this -- blackout. CREATE OR REPLACE PACKAGE mgmt_blackout_ui AS -- -- Type and constant definitions -- TYPE CURSOR_TYPE IS REF CURSOR; -- privilege related return codes HAS_PRIV NUMBER(1) := 1; NO_PRIV NUMBER(1) := -1; AGENT_STARTED_BLACKOUT NUMBER(1) := -2; NOT_CREATED_BY NUMBER(1) := -3; NO_SUCH_BLACKOUT NUMBER(1) := -4; INVALID_STATE_FOR_MODIFY NUMBER(1) := -5; NO_PRIVS_ON_SELECTED_TARGETS NUMBER(1) := -6; NO_OP_PRIVS_ON_HOST_TARGETS NUMBER(1) := -7; ACTIVE_BLACKOUTS NUMBER(1) := 0; OLD_BLACKOUTS NUMBER(1) := 1; ALL_BLACKOUTS NUMBER(1) := 2; FUTURE_BLACKOUTS NUMBER(1) := 3; --String to indicate all target types ALL_TARGET_TYPES VARCHAR2(10) := 'all_types'; --Parent Types PARENT_TYPE_COMPOSITE VARCHAR2(10) := 'composite'; PARENT_TYPE_GROUP VARCHAR2(10) := 'group'; PARENT_TYPE_HOST VARCHAR2(10) := 'host'; -- standard user name used for blackouts created on -- agent command line AGENT_USER VARCHAR(20) := ''; --Module Name to be used for log4plSql MODULE_NAME CONSTANT VARCHAR2(15) := 'EM.Blackouts.UI'; -- RBK config parameter name in the mgmt_parameters RBK_CONFIG_PARAMETER_NAME CONSTANT VARCHAR2(30) := 'RBK_CONFIG_PARAMETER_NAME'; --RBK messages for starting and stopping RBK_START_MSG CONSTANT VARCHAR2(1000) := 'Retroactive Blackout started by {0}. Reason: {1}.' ; RBK_STOP_MSG CONSTANT VARCHAR2(1000) := 'Retroactive Blackout ended.' ; RBK_START_MSG_NLSID CONSTANT VARCHAR2(128) := 'EM__SYS__RBK_START'; RBK_STOP_MSG_NLSID CONSTANT VARCHAR2(128) := 'EM__SYS__RBK_STOP'; -- RBK AUDIT related constants. RBK_MODULE_NAME CONSTANT VARCHAR2(200) := 'Blackout System'; RBK_CONFIG_CLASS CONSTANT VARCHAR2(200) := 'Retroactive Blackout Configuration'; RBK_TARGET_CLASS CONSTANT VARCHAR2(200) := 'Target'; RBK_CONFIG_TYPE CONSTANT VARCHAR2(200) := 'RBK_CONFIGURATION'; RBK_CONFIG_NAME CONSTANT VARCHAR2(200) := 'RBK_CONFIGURATION'; RBK_CONFIG_EVENT_NAME CONSTANT VARCHAR2(200) := 'RBK_CONFIG_CHANGE'; RBK_CREATE_EVENT_NAME CONSTANT VARCHAR2(200) := 'RBK_CREATE'; -- -- FUNCTION: get_target_types -- -- PURPOSE: -- Returns a list of target types that the user has priv on -- and that are not composite target types. FUNCTION get_target_types RETURN CURSOR_TYPE; -- -- FUNCTION: get_parent_target_types -- -- PURPOSE: -- Returns a list of parent target types FUNCTION get_parent_target_types RETURN CURSOR_TYPE; -- -- PROCEDURE: get_blackout_targets -- -- PURPOSE: -- Returns a list of targets that user is able to perform blackouts -- This is used in individual target blackout -- IN PARAMETERS: -- targetType: target type that user can specified that the return -- list contains targets in targetType -- -- OUT PARAMETER; -- tgtList: CURSOR type which contains target name, target type and target guid PROCEDURE get_blackout_targets (p_target_type IN VARCHAR2, p_blackout_tgtList OUT CURSOR_TYPE); -- -- PROCEDURE: get_blackouts -- -- PURPOSE: -- Returns a list of blackouts that the user has privilege on. -- User privilege is defined in the following ways: -- targets blackout: user must have operator_target privilege on all -- targets in this blackout. -- node blackout: user must have operator_target privilege on all -- the targets on the node. -- group blackout: user must have at least operator_target priv for all -- targets in the group or operator_target group privilege -- -- IN Parameters: -- blk_type: Whether we want to see active or historical blackouts or all -- ACTIVE - 0 -- HISTORY - 1 -- ALL - 2 -- FUTURE - 3 -- -- OUT Parameters: -- lists_out: CURSORTYPE A cursor which returns rows containing: -- blackout_guid: The GUID associated with this blackout -- blackout_name: VARCHAR2 -- reason: VARCHAR2 name of the reason -- start_time: DATE when the blackout starts -- duration: the time length for which blackout lasts -- end_time: DATE when the blackout ends -- created_by: VARCHAR2 The user name who creates this blackout -- last_updated_by: VARCHAR2 The user name who last updated this blackout -- blackout_desc: VARCHAR2 The comments given by the user, while creating the Blackout PROCEDURE get_blackouts (blk_type IN NUMBER, lists_out OUT CURSOR_TYPE); FUNCTION get_blackout_reasons RETURN CURSOR_TYPE; -- -- PURPOSE: -- This procedure returns the targets and timezones based on a set of targets -- selected. If a target is a group - it gets the timezones for the -- flattened list of targets.. -- PROCEDURE get_blackout_timezones (p_blackout_targets IN SMP_EMD_NVPAIR_ARRAY, p_blackout_tzs OUT CURSOR_TYPE ); -- -- PROCEDURE: get_flat_targets_host -- -- PURPOSE: -- This procedure returns the list of all targets on the host. -- PROCEDURE get_flat_targets_host (p_blk_host_targets IN SMP_EMD_NVPAIR_ARRAY, p_targets_on_hosts OUT CURSOR_TYPE ); -- -- PROCEDURE: get_flattened_targets -- -- PURPOSE: -- This procedure returns the flattened targets for a set of targets -- selected. If one of the targets is group - it gets the flattened list of -- targets. -- This procedure returns an array containing information of flattenend targets -- of members of the targets. -- The query has been rewritten to optimize performance for returning a single group hierarchy. -- Retrieving the completely populated (N-level) group hierarchy for more than one root target -- in a single SQL execution results in a very poor execution plan from the Oracle CBO at scale. -- But retrieving the same data for a single group is very efficient. -- So, results are fetched for the target guids one at a time and an array is populated. -- Finally, this array is returned to the front end. PROCEDURE get_flattened_targets (p_sel_targets IN SMP_EMD_NVPAIR_ARRAY, p_blkout_flat_tgt_array OUT MGMT_BLACKOUT_FLAT_TGTS_ARRAY ); -- -- PROCEDURE: get_blackout_details -- -- PURPOSE: -- Returns detail information of a blackout -- -- IN Parameters: -- blackout_name: name of the blackout. -- created_by: name of the user who created this blackout, -- if null, assume the current user. -- created_thru: the agent URL where this blackout was created -- if null, it means it was created from the UI -- get_hierarchy: flag to indicate - if we want the user selected -- targets (0) or the complete flattened list (1) -- -- -- OUT Parameters: -- blackout_def: blackout definition that has -- reason: blackout reason -- blackout_status: status of the blackout -- blackout_desc: annotation field for blackout -- timezone_delta: NUMBER the timezone delta between the chosen blackout time -- zone collected and GMT time. -- start_time: DATE when the blackout starts -- duration: NUMBER the time length for which blackout lasts in minutes -- end_time: DATE when the blackout ends -- job_flag: NUMBER can we run jobs on these targets during blackout -- blackout_targets: targets that are in the blackout -- blackout_state: blackout state for flattened list of -- targets that are in the blackout PROCEDURE get_blackout_details (p_blackout_name IN VARCHAR2, p_created_by IN VARCHAR2, p_created_thru IN VARCHAR2, p_get_hierarchy IN NUMBER, p_blackout_def OUT CURSOR_TYPE, p_blackout_targets OUT CURSOR_TYPE, p_blackout_state OUT CURSOR_TYPE, p_blackout_history OUT CURSOR_TYPE ); -- -- PROCEDURE: add_blackout -- -- PURPOSE: -- This procedure adds a new blackout to the repository. The user calling -- this procedure must have the operator_target privilege on the targets s/he -- wish to create blackout -- -- IN Parameters: -- blackout_name: the blackout name to add -- reason: the name of the reason -- blackout_desc: description of blackout -- job flag: should jobs run during the blackout -- blackout_targets: target_name, target_type pair -- schedule_record: struct that holds -- start_time: date and time when blackout starts -- duration: length in time blackout will last -- end_time: date and time when blackout ends -- freq_code: frequency code (type of repeating) -- exec_hours: start time in hours -- exec_minutes: start time in minutes -- rep_interval: repeat interval in minutes -- months: array of integers used to indicate month part of a set of dates -- days: array of integers used to indicate days in various frequcny types -- (see blackout type definitions of MGMT_BLACKOUT_SCHEDULE_RECORD for more -- detailed comments) -- timezone_delta: the time zone blackout occurs in -- -- OUT Parameters: -- NONE PROCEDURE add_blackout (p_blackout_name IN VARCHAR2, p_reason IN VARCHAR2, p_blackout_desc IN VARCHAR2 DEFAULT NULL, p_job_flag IN NUMBER DEFAULT 0, p_blackout_targets IN MGMT_BLACKOUT_TARGET_LIST, p_schedule_record IN MGMT_BLACKOUT_SCHEDULE_RECORD ); -- -- FUNCTION: check_modify_privs -- -- PURPOSE: -- This procedure checks if a user is allowed to edit -- an existing blackout in the repository. -- -- IN Parameters: -- blackout_name: the name of the blackout -- created_by: the user who created this blackout -- created_thru: the agent where this blackout was created -- applicable to CLI blackouts only -- -- OUT Parameters: -- HAS_PRIV(1) if user is allowed to edit, error code otherwise -- EXCEPTIONS -- mgmt_global.INVALID_BLACKOUT_ERR: No such blackout -- mgmt_global.BLKOUT_NOT_CREATED_USER_ERR: not creator of blackout (or a superuser) -- mgmt_global.BLKOUT_STARTED_AT_AGENT_ERR: blackout started at the agent -- mgmt_global.BLKOUT_INVALID_STATE_MOD_ERR: Invalid state for modify -- mgmt_global.BLKOUT_NO_PRIVS_SEL_TGTS_ERR: no blkout privileges on all selected targets -- mgmt_global.BLKOUT_NO_PRIVS_HOST_TGTS_ERR: no full blkout privileges on all selected hosts -- FUNCTION check_modify_privs (p_blackout_name VARCHAR2, p_created_by VARCHAR2, p_created_thru VARCHAR2) RETURN NUMBER; -- -- PROCEDURE: modify_blackout -- -- PURPOSE: -- This procedure modifies an existing blackout in the repository. -- The user calling -- this procedure must have the operator_target privilege on the targets s/he -- wish to create blackout -- -- IN Parameters: -- blackout_name: the name of the blackout -- reason: the name of the reason -- blackout_desc: description of blackout -- job flag: should jobs run during the blackout -- blackout_targets: target_name, target_type pair -- schedule_record: struct that holds -- start_time: date and time when blackout starts -- duration: length in time blackout will last -- end_time: date and time when blackout ends -- freq_code: frequency code (type of repeating) -- exec_hours: start time in hours -- exec_minutes: start time in minutes -- rep_interval: repeat interval in minutes -- months: array of integers used to indicate month part of a set of dates -- days: array of integers used to indicate days in various frequcny types -- (see blackout type definitions of MGMT_BLACKOUT_SCHEDULE_RECORD for more -- detailed comments) -- timezone_delta: the time zone blackout occurs in -- -- created_by: creator of the blackout -- -- OUT Parameters: -- NONE PROCEDURE modify_blackout (p_blackout_name IN VARCHAR2, p_reason IN VARCHAR2, p_blackout_desc IN VARCHAR2 DEFAULT NULL, p_job_flag IN NUMBER DEFAULT 0, p_blackout_targets IN MGMT_BLACKOUT_TARGET_LIST, p_schedule_record IN MGMT_BLACKOUT_SCHEDULE_RECORD, p_created_by IN VARCHAR2 ); -- -- FUNCTION: check_delete_privs -- -- PURPOSE: -- This procedure checks if a user is allowed to delete -- an existing blackout in the repository. -- -- IN Parameters: -- blackout_name: the name of the blackout -- created_by: the user who created this blackout -- created_thru: the agent where this blackout was created -- applicable to CLI blackouts only -- -- OUT Parameters: -- HAS_PRIV(1) if user is allowed to delete, exception otherwise -- EXCEPTIONS -- mgmt_global.INVALID_BLACKOUT_ERR: No such blackout -- mgmt_global.BLKOUT_NOT_CREATED_USER_ERR: not creator of blackout (or a superuser) -- mgmt_global.BLKOUT_STARTED_AT_AGENT_ERR: blackout started at the agent -- mgmt_global.BLKOUT_INVALID_STATE_DEL_ERR: Invalid state for delete -- mgmt_global.BLKOUT_NO_PRIVS_SEL_TGTS_ERR: no blkout privileges on all selected targets -- mgmt_global.BLKOUT_NO_PRIVS_HOST_TGTS_ERR: no full blkout privileges on all selected hosts -- FUNCTION check_delete_privs (p_blackout_name VARCHAR2, p_created_by VARCHAR2, p_created_thru VARCHAR2) RETURN NUMBER; -- -- PROCEDURE: delete_blackout -- -- PURPOSE: -- This procedure deletes an existing blackout in the repository. -- Any user that has the required privileges on all targets affected -- by the blackout can delete it. -- You can only delete a blackout that is either scheduled or stopped. -- -- IN Parameters: -- blackout_name: the name of the blackout -- created_by: the user who created this blackout -- created_thru: the agent where this blackout was created -- applicable to CLI blackouts only -- -- PROCEDURE delete_blackout (p_blackout_name VARCHAR2, p_created_by VARCHAR2, p_created_thru VARCHAR2); -- -- FUNCTION: check_stop_privs -- -- PURPOSE: -- This procedure checks if a user is allowed to stop -- an existing blackout in the repository. -- -- IN Parameters: -- blackout_name: the name of the blackout -- created_by: the user who created this blackout -- created_thru: the agent where this blackout was created -- applicable to CLI blackouts only -- -- OUT Parameters: -- HAS_PRIV(1) if user is allowed to stop, exception otherwise -- EXCEPTIONS -- mgmt_global.INVALID_BLACKOUT_ERR: No such blackout -- mgmt_global.BLKOUT_NOT_CREATED_USER_ERR: not creator of blackout (or a superuser) -- mgmt_global.BLKOUT_STARTED_AT_AGENT_ERR: blackout started at the agent -- mgmt_global.BLKOUT_INVALID_STATE_STOP_ERR: Invalid state for stop -- mgmt_global.BLKOUT_NO_PRIVS_SEL_TGTS_ERR: no blkout privileges on all selected targets -- mgmt_global.BLKOUT_NO_PRIVS_HOST_TGTS_ERR: no full blkout privileges on all selected hosts -- FUNCTION check_stop_privs (p_blackout_name VARCHAR2, p_created_by VARCHAR2, p_created_thru VARCHAR2) RETURN NUMBER; -- -- PROCEDURE: stop_blackout -- -- PURPOSE: -- This procedure stops a blackout in effect. -- Any user that has the required privileges on all targets affected -- by the blackout can stop it. -- You can only stop a blackout that is currently in blackout - this -- includes blackout started or stop failed states. -- -- IN Parameters: -- blackout_name: the name of the blackout -- -- PROCEDURE stop_blackout (p_blackout_name VARCHAR2, p_created_by VARCHAR2); -- -- PROCEDURE: get_target_blackouts -- -- PURPOSE: -- Returns a list of blackouts that are scheduled/in-progress -- for a given target -- -- IN Parameters: -- target_name_in : target name -- target_type_in : target type -- -- -- OUT Parameters: -- lists_out: CURSORTYPE A cursor which returns rows containing: -- blackout_name: VARCHAR2 -- reason: VARCHAR2 name of the reason -- comments: VARCHAR2 commenst on the blackout -- start_time: DATE when the blackout starts -- duration: the time length for which blackout lasts -- end_time: DATE when the blackout ends -- created_by: VARCHAR2 The user name who creates this blackout -- last_updated_by: VARCHAR2 The user name who last updated this blackout PROCEDURE get_target_blackouts (target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, lists_out OUT CURSOR_TYPE); -- -- PROCEDURE: get_blackout_by_target -- -- PURPOSE: -- Returns a list of Blackouts by targets under blackout -- -- IN Parameters: -- blk_type: Whether we want to see active or future or -- historical blackouts or all -- ACTIVE - 0 -- HISTORY - 1 -- ALL - 2 -- FUTURE - 3 -- -- OUT Parameters: -- lists_out: CURSORTYPE A cursor which returns rows containing various -- parameters for the display of backouts. PROCEDURE get_blackout_by_target ( blk_type IN NUMBER, lists_out OUT CURSOR_TYPE ); -- -- PROCEDURE: get_blackout_by_flat_targets -- -- PURPOSE: -- Returns a list of Blackouts by all included targets under blackout -- -- IN Parameters: -- blk_type: Whether we want to see active or future or -- historical blackouts or all -- ACTIVE - 0 -- HISTORY - 1 -- ALL - 2 -- FUTURE - 3 -- -- OUT Parameters: -- lists_out: CURSORTYPE A cursor which returns rows containing various -- parameters for the display of backouts. PROCEDURE get_blackout_by_flat_targets ( blk_type IN NUMBER, lists_out OUT CURSOR_TYPE ); -- -- PROCEDURE: search_blackouts_by_name -- -- PURPOSE: -- Returns a list of Blackouts with names matching the search criteria -- entered by the user. -- -- IN Parameters: -- blk_type: Whether we want to see active or future or -- historical blackouts or all -- ACTIVE - 0 -- HISTORY - 1 -- ALL - 2 -- FUTURE - 3 -- search_string: the string on which the search has to be performed -- -- OUT Parameters: -- lists_out: CURSORTYPE A cursor which returns rows containing various -- parameters for the display of backouts. PROCEDURE search_blackouts_by_name ( blk_type IN NUMBER, search_string IN VARCHAR2, lists_out OUT CURSOR_TYPE ); -- -- PROCEDURE: search_blackouts_by_target -- -- PURPOSE: -- Returns a list of Blackouts with target names matching the search criteria -- entered by the user. -- -- IN Parameters: -- p_target_type: The type of the target which has to be searched -- p_target_search_string: The string on which the target has to be -- searched -- p_parent_type: The type of the parent under which the target to be -- searched lies. This can be Group, Composite or Host -- p_parent_search_string: The string on which the target's parent has -- to be searched. -- p_blk_type: Whether we want to see active or future or -- historical blackouts or all -- ACTIVE - 0 -- HISTORY - 1 -- ALL - 2 -- FUTURE - 3 -- -- OUT Parameters: -- p_lists_out: CURSORTYPE A cursor which returns rows containing various -- parameters for the display of backouts. PROCEDURE search_blackouts_by_target ( p_target_type IN VARCHAR2, p_target_search_string IN VARCHAR2, p_parent_type IN VARCHAR2, p_parent_search_string IN VARCHAR2, p_blk_type IN NUMBER, p_lists_out OUT CURSOR_TYPE ); -- -- PROCEDURE: search_blackouts_flat_targets -- -- PURPOSE: -- Returns a list of Blackouts with target names matching the search criteria -- entered by the user. This search is performed on all the inluded targets -- (flattened targets list) also. -- -- IN Parameters: -- p_target_type: The type of the target which has to be searched -- p_target_search_string: The string on which the target has to be -- searched -- p_parent_type: The type of the parent under which the target to be -- searched lies. This can be Group, Composite or Host -- p_parent_search_string: The string on which the target's parent has -- to be searched. -- p_blk_type: Whether we want to see active or future or -- historical blackouts or all -- ACTIVE - 0 -- HISTORY - 1 -- ALL - 2 -- FUTURE - 3 -- -- OUT Parameters: -- p_lists_out: CURSORTYPE A cursor which returns rows containing various -- parameters for the display of backouts. PROCEDURE search_blackouts_flat_targets ( p_target_type IN VARCHAR2, p_target_search_string IN VARCHAR2, p_parent_type IN VARCHAR2, p_parent_search_string IN VARCHAR2, p_blk_type IN NUMBER, p_lists_out OUT CURSOR_TYPE ); -- -- PROCEDURE: get_targets_under_blackout -- -- PURPOSE: -- Returns a list of Target Names which are under a given Blackout. -- -- IN Parameters: -- p_blackout_name: The Blackout Name under which the required Targets -- lie. -- p_created_thru: A parameter to uniquely help identify the Blackout. -- p_created_by: A parameter to uniquely help identify the Blackout. -- -- OUT Parameters: -- lists_out: CURSORTYPE A cursor which contains a list of Target Names. PROCEDURE get_targets_under_blackout ( p_blackout_name IN VARCHAR2, p_created_thru IN VARCHAR2, p_created_by IN VARCHAR2, p_lists_out OUT CURSOR_TYPE ); -- -- PROCEDURE: get_group_blackouts_info -- -- PURPOSE: -- Fetches information for display on the Blackouts Page Segment -- on the Groups HomePage. -- -- IN Parameters: -- p_group_name: VARCHAR2 The name of the Group for which all the information -- has to be fetched. -- -- OUT Parameters: -- p_group_blackouts_future: NUMBER A Count of Scheduled Blackouts submitted -- to this Group. -- p_group_blackouts_active: NUMBER A Count of Active Blackouts submitted -- to this Group. -- p_members_blackouts_future: NUMBER Count of Scheduled Blackouts submitted -- to any Members of this Group. -- p_members_blackouts_active: NUMBER A Count of Active Blackouts submitted -- to any Members of this Group. -- PROCEDURE get_group_blackouts_info ( p_group_name IN VARCHAR2, p_group_type IN VARCHAR2, p_group_blackouts_future OUT NUMBER, p_group_blackouts_active OUT NUMBER, p_members_blackouts_future OUT NUMBER, p_members_blackouts_active OUT NUMBER ); -- -- FUNCTION: does_include_members -- -- PURPOSE: -- Confirms the target in ""mgmt_blackout_target_details" includes members -- or not Required by routine "search_blackouts_flat_targets" to avoid a -- cartesian join between "mgmt_blackouts", ""mgmt_blackout_target_details" -- and "mgmt_blackout_flat_targets" -- -- If I select "2 out of available 5 members in a group" in a blackout, -- target_guid for these 2 selected targets is available in both tables -- "mgmt_blackout_target_details" and "mgmt_blackout_flat_targets". -- This leads to cartesian join. -- -- IN Parameters: -- blackout_guid : GUID of the Blackout under process -- target_guid : GUID of target -- -- Return Value -- Yes -- The Current Target includes members in the Blackout Definition -- No -- The Current Target does not include members in the Blackout -- Definition FUNCTION does_include_members ( p_blackout_guid IN VARCHAR2, p_target_guid IN VARCHAR2 ) RETURN VARCHAR2; -- -- FUNCTION: check_operator_priv -- -- PURPOSE: -- This functions checks the Operator privileges of a user for a group -- and its members. If the include members flag is not set, then the target -- does not have children and hence it checks for Operator privilege only on -- the target. Else, if include members flag is set, then it checks -- for Operator Privileges on the Group and on its Members. -- -- -- -- IN Parameters: -- target_name : Name of the Target. Can be a group also -- target_type : Type of the Target -- include_members : include members flag. 1 means its a composite target -- user_name : Name of the user. By default it takes current user. -- -- -- Return Value -- MGMT_USER.USER_HAS_PRIV : If the user has Operator Privilege -- MGMT_USER.USER_DOES_NOT_HAVE_PRIV: If the user does not have Operator Privilege -- FUNCTION check_operator_priv ( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_include_members IN INTEGER, p_user_name IN VARCHAR2 DEFAULT NULL ) RETURN NUMBER; -- -- PROCEDURE: get_assoc_blackouts -- -- PURPOSE: -- Fetches the Blackouts that are associated with the given Blackout Reason. -- -- IN Parameters: -- p_reason: VARCHAR2 The given Blackout Reason. -- -- OUT Parameters: -- p_assoc_blackouts: REF CURSOR A cursor containing the names of Blackouts -- that are associated with the given Blackout Reason. -- PROCEDURE get_assoc_blackouts ( p_reason IN VARCHAR2, p_assoc_blackouts OUT MGMT_BLACKOUT_UI.CURSOR_TYPE ); -- -- PROCEDURE: delete_blackout_reason -- -- PURPOSE: -- Deletes the given Blackout Reason from the MGMT_BLACKOUT_REASON table. -- -- IN Parameters: -- p_reason: VARCHAR2 The given Blackout Reason which has to be deleted. -- PROCEDURE delete_blackout_reason ( p_reason IN VARCHAR2 ); -- -- PROCEDURE: update_reason_required -- -- PURPOSE: -- Updates the BLACKOUT_REASONS_REQUIRED property in the -- MGMT_PARAMETERS table. -- -- IN Parameters: -- p_reason_required The new value indicating whether Blackout Reasons -- are mandatory. -- PROCEDURE update_reason_required ( p_reason_required IN VARCHAR2 ); -- -- PROCEDURE: count_reasons_by_name -- -- PURPOSE: -- Fetches the number of Blackout Reasons with the given value. -- -- IN Parameters: -- p_reason: VARCHAR2 The given value. -- -- OUT Parameters: -- p_assoc_blackouts_count: NUMBER A count of the number of Blackout -- Reasons with the given value. -- PROCEDURE count_reasons_by_name ( p_reason IN VARCHAR2, p_reasons_count OUT NUMBER ); -- -- PROCEDURE: get_target_active_blackouts -- -- PURPOSE: -- Returns a list of blackouts that are in-progress -- for a given target. -- -- IN Parameters: -- target_name_in : target name -- target_type_in : target type -- -- OUT Parameters: -- p_lists_out: CURSORTYPE A cursor which returns rows containing -- info for the Blackouts. PROCEDURE get_target_active_blackouts ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, p_lists_out OUT CURSOR_TYPE ); -- -- PROCEDURE: get_targets_oper_priv -- -- PURPOSE: -- Returns a list of all targets -- for which the given user has Operator Privilege. -- -- IN Parameters: -- p_user_name : user name -- -- OUT Parameters: -- p_targets_list: CURSORTYPE A cursor which returns list of target name and type for whom -- operator privilege is present PROCEDURE get_targets_oper_priv ( p_user_name IN VARCHAR2, p_targets_list OUT CURSOR_TYPE ); -- -- PROCEDURE: get_targets_full_blackout -- -- PURPOSE: -- Returns a list of all composite targets which are under Full Blackout -- for the given the given Blackout . -- -- IN Parameters: -- p_blkout_name : blackout name -- -- OUT Parameters: -- p_targets_list: CURSORTYPE A cursor which returns list of target name and type -- under full Blackout PROCEDURE get_targets_full_blackout ( p_blkout_name IN VARCHAR2, p_targets_list OUT CURSOR_TYPE ); -- -- PROCEDURE: enable_retroactive_blackout -- -- PURPOSE: -- This api enables or disable the retroactive blackout(rbk) across EM. -- -- IN Parameters: -- p_enable_rbk : "true" means enable Retroactive blackout. -- "false" means to disable Retroactive blackout at the EM level. -- PROCEDURE enable_retroactive_blackout ( p_enable_rbk IN VARCHAR2 ); -- -- FUNCTION: is_retroactive_blackout_enable -- -- PURPOSE: -- Returns if retroactive blackout feature is enabled or not. -- -- IN Parameters: -- p_enable_rbk : "true" means enable Retroactive blackout. -- "false" means to disable Retroactive blackout at the EM level. -- FUNCTION is_retroactive_blackout_enable RETURN VARCHAR2; -- -- PROCEDURE: get_downtime_target_avail_info -- -- PURPOSE: -- This api return the target's downtime availability records. -- Downtime record includes Agent downtime and down time. -- -- IN Parameters: -- p_target_name : Target name whose downtime to fetch -- p_target_type : Target type whose downtime to fetch -- p_target_avail_cur : Availability record cursor having columns: -- target_name -- target_type -- status -- from_time (In targets tz) -- to_time (In targets tz) -- PROCEDURE get_downtime_target_avail_info( p_target_name IN MGMT_TARGETS.TARGET_NAME%TYPE, p_target_type IN MGMT_TARGETS.TARGET_TYPE%TYPE, p_target_avail_cur OUT CURSOR_TYPE); -- PROCEDURE validate_rbk_time_period -- -- PURPOSE: -- validate the rbk time period for the following: -- 1. If rbk time period is before any targets first availability data. -- 2. If the rbk time span overlaps with the last record of any target that is -- unsupported state i.e. unreachable, unknown or error -- -- IN Parameters: -- p_targets : List of Targets that needs to be checked for -- availability first start record. -- p_rbk_start_time : RBK start time -- p_rbk_end_time : RBK end time -- p_timezone_region : RBK timezone -- OUT Parameters: -- p_noAvailTargets : Targets with availability record not available for therbk time period -- target_guid -- target_name -- target_type -- from_time -- to_time -- status -- timezone of from and to time (This will in rbk tz) -- p_unSupStateTargets : Targets with last record in unsuported state -- target_guid -- target_name -- target_type -- from_time -- to_time -- status -- timezone of from and to time (This will in rbk tz) PROCEDURE validate_rbk_time_period( p_targets IN MGMT_TARGET_ARRAY, p_rbk_start_time IN DATE, p_rbk_end_time IN DATE, p_timezone_region IN VARCHAR2, p_noAvailTargets OUT MGMT_RBK_TARGET_INFO_ARRAY, p_unSupStateTargets OUT MGMT_RBK_TARGET_INFO_ARRAY); -- -- PROCEDURE: get_ovl_last_unsup_targets -- -- PURPOSE: -- This api return the target's which are currently in unsupported state -- they are unknown,error or unreachable state and rbk time end time should be -- greater than the start time since the target are currently in unsupported. -- In short it will check if the last availability record overlaps with -- rbk time span and if the state of the last record is unknown,error or -- unreachable. -- -- IN Parameters: -- p_targets : List of Targets that needs to be checked for -- availability first start record. -- p_rbk_end_time : RBK end time -- p_timezone_region : RBK timezone -- OUT Parameters: -- p_unSupStateTargets : Targets with last record in unsuported state -- target_guid -- target_name -- target_type -- from_time -- to_time -- status -- timezone of from and to time (This will in rbk tz) PROCEDURE get_ovl_last_unsup_targets( p_targets IN MGMT_TARGET_ARRAY, p_rbk_end_time IN DATE, p_timezone_region IN VARCHAR2, p_unSupStateTargets OUT MGMT_RBK_TARGET_INFO_ARRAY); -- -- PROCEDURE: get_no_avail_targets -- -- PURPOSE: -- This api return the target's that do not have availability record -- at the specified rbk start time. -- -- IN Parameters: -- p_targets : List of Targets that needs to be checked for -- availability first start record. -- p_rbk_start_time : RBK start time -- p_timezone_region : RBK timezone -- OUT Parameters: -- p_noAvailTargets : Availability record having columns: -- target_guid -- target_name -- target_type -- from_time -- to_time -- timezone of from and to time PROCEDURE get_no_avail_targets( p_targets IN MGMT_TARGET_ARRAY, p_rbk_start_time IN DATE, p_timezone_region IN VARCHAR2, p_noAvailTargets OUT MGMT_RBK_TARGET_INFO_ARRAY); -- -- PROCEDURE: get_overlap_blk_details -- -- PURPOSE: -- This api return the target's existing blackout records that overlaps with -- specified rbk time period. -- -- IN Parameters: -- p_targets : List of Targets that needs to be checked for -- blackout overlap. -- p_rbk_start_time : RBK start time -- p_rbk_end_time : RBK end time -- p_timezone_region : RBK timezone -- -- OUT Parameters: -- p_noAvailTargets : Availability record having columns: -- target_guid -- target_name -- target_type -- from_time -- to_time -- timezone of from and to time PROCEDURE get_overlap_blk_details(p_targets IN MGMT_TARGET_ARRAY, p_rbk_start_time IN DATE, p_rbk_end_time IN DATE, p_timezone_region IN VARCHAR2, p_ovlTargets OUT MGMT_RBK_TARGET_INFO_ARRAY); --Procecure:create_retroactive_blackout --Parameters: -- p_targets List of targets which needs to be under retroactive_blackout -- p_rbk_start_time_in Retroactive blackout start time -- p_rbk_end_time_in Retroactive blackout end time -- p_timezone_region Timezone region for retroactive blackout -- p_create_backup To create backup mgmt_availability -- p_err_targets List of Erroneous Targets on Error procedure create_retroactive_blackout (p_targets IN MGMT_TARGET_ARRAY, p_reason IN VARCHAR2, p_rbk_start_time_in DATE, p_rbk_end_time_in DATE, p_timezone_region IN VARCHAR2, p_create_backup IN VARCHAR2 DEFAULT 'N', p_err_targets OUT VARCHAR2_TABLE); -- PROCEDURE: get_blackout_guid -- PURPOSE: -- Get the blackout guid and current status for the specified blackout -- IN Parameters: -- p_blackout_name: Blackout name -- p_created_by : Blackout created by -- p_created_thru : Blackout created through -- OUT Parameters: -- p_blackout_guid_out : Blackout GUID -- p_blackout_status_out : Blackout status PROCEDURE get_blackout_guid(p_blackout_name VARCHAR2, p_created_by VARCHAR2, p_created_thru VARCHAR2, p_blackout_guid_out OUT RAW, p_blackout_status_out OUT NUMBER); END mgmt_blackout_ui; /