Rem Rem $Header: gensvc_view_def.sql 19-sep-2006.06:10:45 kannatar Exp $ Rem Rem gensvc_view_def.sql Rem Rem Copyright (c) 2004, 2006, Oracle. All rights reserved. Rem Rem NAME Rem gensvc_view_def.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem sbhagwat 09/19/06 - Retroactive Blackout Rem andyao 12/07/05 - Backport andyao_bug-4739983 from main Rem andyao 09/23/05 - add license report per test type summary Rem yxie 09/21/05 - add license report proc Rem mfidanbo 08/30/05 - Rem mfidanbo 07/12/05 - optimize sql Rem mfidanbo 06/26/05 - provide page level consolidated data getter Rem mfidanbo 06/07/05 - fix top-level tgt tz issue Rem jriel 06/07/05 - add get topo member details Rem mfidanbo 05/17/05 - add new states to gensvcCompSummary Rem mfidanbo 04/10/05 - add compute_disp_dates Rem mfidanbo 03/19/05 - change func def Rem mfidanbo 03/16/05 - fix sev and out cursors Rem jriel 03/10/05 - network target changes Rem jpyang 02/17/05 - add has_component_array Rem jriel 02/22/05 - add sys guid Rem mfidanbo 01/05/05 - add get_component_summary Rem mfidanbo 11/18/04 - add get testXbcn list Rem mfidanbo 11/12/04 - sys_based vs test_based avail Rem mfidanbo 11/15/04 - fix rep schema Rem mfidanbo 11/10/04 - MTA changes Rem mfidanbo 11/01/04 - Add get_tests_for_svc Rem mfidanbo 10/26/04 - Key component summary Rem mfidanbo 10/25/04 - mfidanbo_sql_fix Rem mfidanbo 10/25/04 - Created Rem CREATE OR REPLACE PACKAGE gensvc_view IS TYPE CURSORTYPE IS REF CURSOR; -- PURPOSE: Getting the severities given a service -- PROCEDURE: get_severities_for_service -- IN target_name_in: name of the service -- IN target_type_in: type of service -- IN start_date_in: alerts which happened before will not be shown -- IN end_date_in: alerts which happened after will not be shown -- IN num_of_days_in: number of days for period -- OUT outages_cur_out: outages cursor which contains: -- severity_guid: severity guid related to the outage -- outage_type: type of outage (down,error etc) -- is_open: 'Y' means its still open, otherwise 'N' -- duration: duration of outage -- start_timestamp: start timestamp of outage -- end_timestamp: end timestamp of outage -- OUT metric_severities_cur_out: metric severities cursor -- violition_level: gives the severity type -- start_timestamp: start timestamp of severity -- duration: duration of severity -- message: message of severity -- message_nlsid: nlsID of the message -- message_params: parameters of the message -- v_guid: violition_guid PROCEDURE get_severities_for_service( 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, outages_cur_out OUT CURSORTYPE, severities_cur_out OUT CURSORTYPE, metric_severities_cur_out OUT CURSORTYPE); -- PURPOSE: Getting the availability of a single service -- PROCEDURE: get_avail_single_target -- IN target_name_in: name of the service -- IN target_type_in: type of service -- IN top_level_target_name : the top-level tgt name (tz will be determined) -- IN top_level_target_type : top_level tgt type -- IN start_date_in: availability before this wont be shown -- IN end_date_in: availability after this will not be shown -- IN num_of_days_in: number of days for period -- OUT start_time_out: start date of the availability period -- OUT end_time_out: end date of the availability period -- OUT tz_out : tz for start and end_time -- OUT current_status_out: current status of the service -- OUT avail_pct_out: availability percentage of the service -- OUT status_since_out: current status since -- OUT avail_states_cur_out: availability states cursor containing: -- target_guid: target_guid of the current svc -- target_name: target name of the current svc -- target_type: target type of the current svc -- current_status: current status of svc -- severity_guid: severity guid related to the availaiblity state -- historical_status: status of the availability state -- duration: duration of the avail state -- start_timestamp: start timestamp of the avail state -- end_timestamp: end timestamp of the avail state -- OUT severity_states_cur_out: severity states cursor containing: -- target_guid: target_guid of the current svc -- target_name: target name of the current svc -- target_type: target type of the current svc -- violation_level: severity type -- start_timestamp: start timestamp of severity -- end_timestamp: end timestamp of severity -- duration: severity duration PROCEDURE get_avail_single_target( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, top_level_target_name IN VARCHAR2, top_level_target_type IN VARCHAR2, top_level_tz IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, start_time_out OUT DATE, end_time_out OUT DATE, tz_out OUT VARCHAR2, current_status_out OUT NUMBER, avail_pct_out OUT NUMBER, status_since_out OUT DATE, avail_states_cur_out OUT CURSORTYPE, severity_states_cur_out OUT CURSORTYPE, is_test_avail_out OUT VARCHAR2); -- PURPOSE: Getting the availability of a service components -- PROCEDURE: get_avail_all_assoc_targets -- IN target_name_in: name of the service -- IN target_type_in: type of service -- IN start_date_in: availability before this wont be shown -- IN end_date_in: availability after this will not be shown -- IN num_of_days_in: number of days for period -- IN assoc_type_in: type of association -- OUT start_time_out: start date of the availability period -- OUT end_time_out: end date of the availability period -- OUT avail_states_cur_out: availability states cursor containing rows which define availability periods -- target_guid: guid of the target that this row applies to -- target_name: name of the target that this row applies to -- target_type: type of the target that this row applies to -- current_status: current status of this target -- historical_status: status corresponding to this particular availability period -- duration: duration of the avail state -- start_timestamp: start timestamp of the avail state -- end_timestamp: end timestamp of the avail state -- OUT perf_states_cur_out: severity states cursor containing: -- target_guid: guid of the target that this row applies to -- target_name: name of the target that this row applies to -- target_type: type of the target that this row applies to -- violation_level: severity type -- start_timestamp: start timestamp of severity -- end_timestamp: end timestamp of severity -- duration: severity duration PROCEDURE get_avail_all_assoc_targets( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, top_level_target_name IN VARCHAR2, top_level_target_type IN VARCHAR2, top_level_tz IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, assoc_type_in IN VARCHAR2, start_time_out OUT DATE, end_time_out OUT DATE, tz_out OUT VARCHAR2, avail_states_cur_out OUT CURSORTYPE, perf_states_cur_out OUT CURSORTYPE); PROCEDURE get_hist_key_comp_summary( 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, system_name_out OUT VARCHAR2, system_type_out OUT VARCHAR2, down_comps_out OUT NUMBER, up_comps_out OUT NUMBER, warning_alerts_out OUT NUMBER, critical_alerts_out OUT NUMBER); PROCEDURE get_hist_test_summary ( 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, tests_cur_out OUT CURSORTYPE); PROCEDURE get_curr_test_summary ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, tests_cur_out OUT CURSORTYPE); PROCEDURE get_curr_key_comp_summary( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, system_name_out OUT VARCHAR2, system_type_out OUT VARCHAR2, states_out OUT CURSORTYPE, warning_alerts_out OUT NUMBER, critical_alerts_out OUT NUMBER); PROCEDURE get_gensvc_alerts ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, alert_type_in IN VARCHAR2, alerts_out OUT CURSORTYPE); PROCEDURE get_tests_bcns_for_svc ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, only_key_tests IN VARCHAR2, only_key_bcns IN VARCHAR2, test_bcns_out OUT CURSORTYPE); PROCEDURE get_tests_for_svc ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, only_key IN VARCHAR2, tests_out OUT CURSORTYPE); PROCEDURE get_bcns_for_svc ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, only_key IN VARCHAR2, bcns_out OUT CURSORTYPE); PROCEDURE get_test_bcn_avail( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, top_level_target_name IN VARCHAR2, top_level_target_type IN VARCHAR2, top_level_tz IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, only_key IN VARCHAR2, start_date_out OUT DATE, end_date_out OUT DATE, tz_out OUT VARCHAR2, test_avail OUT CURSORTYPE, test_bcn_avail OUT CURSORTYPE, test_bcn_hist_avail OUT mgmt_bcn_txn_avail_array); -- Compute start and end dates computes the actual availability period -- to be fetched.. it is adjusted according to the target avail markers PROCEDURE compute_start_end_dates( target_guid_in IN RAW, tz_region_in IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, start_date_out OUT DATE, end_date_out OUT DATE); -- Compute display start, end dates is ONLY used for computing the UI -- displayed dates which are adjusted to target tz. PROCEDURE compute_disp_start_end_dates( target_guid_in IN RAW, tz_region_in IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, start_date_out OUT DATE, end_date_out OUT DATE); PROCEDURE get_component_summary ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, only_key IN VARCHAR2, components OUT CURSORTYPE, system_name OUT VARCHAR2, system_type OUT VARCHAR2); PROCEDURE single_tst_bcn_avail ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, test_name_in IN VARCHAR2, test_type_in IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, start_date_out OUT DATE, end_date_out OUT DATE, avail_pct_out OUT NUMBER, current_status OUT NUMBER, since OUT DATE, test_avail OUT CURSORTYPE, test_bcn_avail OUT CURSORTYPE, test_bcn_hist_avail OUT mgmt_bcn_txn_avail_array); FUNCTION has_component_array( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2 ) RETURN NUMBER; FUNCTION test_based_available( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2) RETURN NUMBER; PROCEDURE get_service_home_data ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, alert_type_in IN VARCHAR2, alerts_out OUT CURSORTYPE, tests_cur_out OUT CURSORTYPE, system_name_out OUT VARCHAR2, system_type_out OUT VARCHAR2, states_out OUT CURSORTYPE, warning_alerts_out OUT NUMBER, critical_alerts_out OUT NUMBER, metric_errors_out OUT NUMBER); PROCEDURE get_system_and_components ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, only_key IN VARCHAR2, components OUT CURSORTYPE, system_name OUT VARCHAR2, system_type OUT VARCHAR2, system_guid OUT RAW); PROCEDURE get_service_topology ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, net_tgt_name_out OUT VARCHAR2, net_tgt_guid_out OUT RAW, topo_cursor_out OUT CURSORTYPE, cause_cursor_out OUT CURSORTYPE); PROCEDURE get_topo_member_details( target_guid_list_in IN MGMT_TARGET_GUID_ARRAY, details_out OUT cursorType); FUNCTION get_empty_record ( tgt IN COMPONENT, start_time IN date, end_time IN date ) RETURN COMP_AVAIL; -- PROCEDURE get_slm_pack_tgt_test_list -- Returns the cursor for populating the Service Tests Summary table -- in the Service Level Management Pack Access report. PROCEDURE get_slm_pack_tgt_test_list ( tgt_test_list OUT CURSORTYPE); -- PROCEDURE get_slm_pack_report_summary -- Returns the cursor for populating the Service Tests and Beacons Summary table -- in the Service Level Management Pack Access report. PROCEDURE get_slm_pack_report_summary ( tgt_test_list OUT CURSORTYPE); -- PROCEDURE get_slm_pack_test_type_list -- Returns the cursor for populating the Service Test Types Summary table -- in the Service Level Management Pack Access report. PROCEDURE get_slm_pack_test_type_list (test_type_list OUT CURSORTYPE); -- PROCEDURE get_sev_for_service_with_rbk -- This is similar to get_severities_for_service api only exception to it is -- it also bring the severity information of rbk from mgmt_severity_rbk PROCEDURE get_sev_for_service_with_rbk( 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, outages_cur_out OUT CURSORTYPE, severities_cur_out OUT CURSORTYPE, metric_severities_cur_out OUT CURSORTYPE ); END gensvc_view; / show errors;