Rem Rem $Header: availability_pkgdefs.sql 21-may-2007.12:54:16 rrawat Exp $ Rem Rem availability_pkgdefs.sql Rem Rem Copyright (c) 2002, 2007, Oracle. All rights reserved. Rem Rem NAME Rem availability_pkgdefs.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem rrawat 05/21/07 - Backport rrawat_6027427_rr from main Rem kannatar 09/21/06 - Rem sbhagwat 03/29/06 - Retroactive Blackout Rem gsbhatia 06/26/05 - New repmgr header impl Rem neearora 03/03/05 - Added MODULE_NAME constant Rem gsbhatia 02/13/05 - updating repmgr header Rem gsbhatia 02/09/05 - updating repmgr header Rem gsbhatia 02/07/05 - updating repmgr header Rem ktlaw 01/11/05 - add repmgr header Rem pmaddi 07/29/04 - Moving rca specific functions to rca package. Rem pmaddi 07/21/04 - Adding 2 more parameters for getting rca Rem dependent targets list. Rem pmaddi 07/05/04 - Adding one more parameter in the procedure Rem get_avail_overview_details to get rca result Rem data. Rem ancheng 02/09/04 - add availability formula Rem ancheng 08/08/03 - severity/annotation change Rem rpatti 04/15/03 - add func to get target availability Rem rpinnama 09/18/02 - Remove authid current_user Rem tjaiswal 06/20/02 - Cleanup plsql after review Rem tjaiswal 06/17/02 - Misc avail plsql changes Rem tjaiswal 06/14/02 - Modify queries for avail overview details Rem tjaiswal 06/10/02 - Add plsql for avail ui changes Rem rpatti 05/21/02 - show more detailed availability states Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Created Rem CREATE OR REPLACE PACKAGE mgmt_avail IS TYPE CURSORTYPE IS REF CURSOR; -- Constant for module name MODULE_NAME CONSTANT VARCHAR2(10) := 'MGMT_AVAIL'; OUTAGE_DETAILS_VIEW_TYPE NUMBER(1) := 0; AVAIL_STATUS_VIEW_TYPE NUMBER(1) := 1; AVAIL_STATUS_WITH_RBK CONSTANT NUMBER(1) := 2; -- -- get_avail_current_status function -- -- PURPOSE: Procedure to find out the current status for a given target. -- -- IN parameters: -- target_name_in : target name -- target_type_in : target type -- -- Returns: -- the target's current availability state -- -- ERROR CODES: -- 1. target does not exist -- FUNCTION get_avail_current_status( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2) RETURN NUMBER; -- -- get_target_start_marker_time function -- -- PURPOSE: Procedure to find out the start marker time for a given target. -- start marker time for a target is the first time from when the -- target's availability is known -- -- IN parameters: -- target_name_in : target name -- target_type_in : target type -- -- Returns: -- the target's start marker time -- -- ERROR CODES: -- 1. target does not exist -- FUNCTION get_target_start_marker_time( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2) RETURN DATE; -- -- get_target_start_marker_time function -- -- PURPOSE: Procedure to find out the start marker time for a given target. -- start marker time for a target is the first time from when the -- target's availability is known -- -- IN parameters: -- target_guid_in : the target guid of the target -- -- Returns: -- the target's start marker time -- -- ERROR CODES: -- 1. target does not exist -- FUNCTION get_target_start_marker_time( target_guid_in IN mgmt_targets.target_guid%TYPE) RETURN DATE; -- -- get_target_end_marker_time function -- -- PURPOSE: Procedure to find out the end marker time for a given target. -- end marker time for a target is the time upto which the target's -- availability is known reliably -- -- IN parameters: -- target_name_in : target name -- target_type_in : target type -- -- Returns: -- the target's end marker time -- -- ERROR CODES: -- 1. target does not exist -- FUNCTION get_target_end_marker_time( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2) RETURN DATE; -- -- get_target_end_marker_time function -- -- PURPOSE: Procedure to find out the end marker time for a given target. -- end marker time for a target is the time upto which the target's -- availability is known reliably -- -- IN parameters: -- target_guid_in : the target guid of the target -- -- Returns: -- the target's end marker time -- -- ERROR CODES: -- 1. target does not exist -- FUNCTION get_target_end_marker_time( target_guid_in IN mgmt_targets.target_guid%TYPE) RETURN DATE; -- get_availability_summary procedure -- -- PURPOSE: Procedure to find out the availability summary data for a given target. -- IN parameters: -- target_name_in : target name -- target_type_in : target type -- num_of_days_in : number of days, for week(7) and month(31) -- -- OUT parameters: -- avail_summary_cur_out: REF CURSOR type, the cursor contains -- 1. current_status NUMBER, -- 2. up_time NUMBER, -- 3. times_down NUMBER, -- 4. total_scheduled_downtime NUMBER, -- 5. total_unscheduled_downtime NUMBER, -- 6. avg_repair_time NUMBER, -- 7. longest_downtime NUMBER -- 8. total_downtime NUMBER, -- 9. total_unmonitored_time NUMBER, -- 10. total_unknown_time NUMBER, -- -- ERROR CODES: -- 1. invalid target name -- 2. invalid target type -- PROCEDURE get_availability_summary( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, num_of_days_in IN NUMBER, avail_summary_cur_out OUT CURSORTYPE ); -- get_avail_overview_details procedure -- -- PURPOSE: Procedure to find out the availability summary data for a given -- target. -- IN parameters: -- target_name_in : target name -- target_type_in : target type -- start_date_in : the start date(in the target time zone) of the -- availability window -- end_date_in : the end date(in the target time zone) of the -- availability window -- num_of_days_in : number of days, for week(7) and month(31) -- view_type_in : the view type passed in by the ui -- if view_type_in = OUTAGE_DETAILS_VIEW_TYPE, then data for -- avail_met_sev_cur_out will not be determined -- if view_type_in = AVAIL_STATUS_VIEW_TYPE, then data for -- outage_detail_cur_out will not be determined -- -- NOTE: the start_date_in and end_date_in will ne used if both are available -- and in this case the num_of_days_in will be ignored -- only if both start_date_in and end_date_in are both null, then -- num_of_days_in will be used to compute the start date and end date -- -- OUT parameters: -- has_agent_view_priv_out: NUMBER if the user has the view privilege -- on the agent that monitors the target -- avail_formula_out: NUMBER determines which availability formula to use -- current_status_out: NUMBER The current status of the target -- -- start_marker_time_out: DATE: The target's start marker time -- -- end_marker_time_out: DATE: The target's end marker time -- -- avail_summary_cur_out: REF CURSOR type, the cursor contains -- 1. current_status NUMBER, -- 2. up_time NUMBER, -- 3. total_downtime NUMBER, -- 4. total_error_time NUMBER, -- 5. total_unmonitored_time NUMBER, -- 6. total_unreachable_time NUMBER, -- 7. total_blackout_time NUMBER, -- 8. total_unknown_time NUMBER, -- 9. times_down NUMBER, -- 10. avg_repair_time NUMBER, -- 11. longest_downtime NUMBER, -- -- avail_states_cur_out: REF CURSOR type, the cursor contains -- 1. target_guid RAW, -- 2. current_status NUMBER, -- 3. duration NUMBER, -- 4. start_timestamp DATE, -- 5. end_timestamp DATE, -- -- avail_met_sev_cur_out: REF CURSOR type, the cursor contains -- 1. severity_code NUMBER, -- 2. collection_timestamp DATE, -- 3. user_name VARCHAR2, -- 4. message VARCHAR2, -- -- outage_detail_cur_out: REF CURSOR type, the cursor contains -- 1. target_guid RAW, -- 2. outage_type NUMBER, -- 3. is_open NUMBER, -- 4. duration NUMBER, -- 5. start_timestamp DATE, -- 6. end_timestamp DATE, -- -- ERROR CODES: -- 1. invalid target name -- 2. invalid target type -- PROCEDURE get_avail_overview_details( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, view_type_in IN NUMBER, has_agent_view_priv_out OUT NUMBER, avail_pct_out OUT NUMBER, avail_formula_out OUT NUMBER, current_status_out OUT NUMBER, current_status_since_out OUT DATE, start_time_out OUT DATE, end_time_out OUT DATE, start_marker_time_out OUT DATE, end_marker_time_out OUT DATE, avail_summary_cur_out OUT CURSORTYPE, avail_states_cur_out OUT CURSORTYPE, avail_met_sev_cur_out OUT CURSORTYPE, outage_detail_cur_out OUT CURSORTYPE); -- add_avail_comment procedure -- -- PURPOSE: Procedure to add a comment for an avail state -- IN parameters: -- target_name_in : target name -- target_type_in : target type -- comment_in : the comment to be added for the avail state -- -- OUT parameters: -- None -- -- ERROR CODES: -- 1. invalid target name -- 2. invalid target type -- PROCEDURE add_avail_comment( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, annotation_in IN VARCHAR2 ); END mgmt_avail; / show errors;