Rem drv: Rem Rem $Header: service_level_pkgdef.sql 03-jul-2006.06:00:49 skotha Exp $ Rem Rem service_level_pkgdef.sql Rem Rem Copyright (c) 2004, 2006, Oracle. All rights reserved. Rem Rem NAME Rem service_level_pkgdef.sql - Package defn for SL rules Rem Rem DESCRIPTION Rem Package defn for SL rules Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem skotha 07/03/06 - Service Dashboard enhancements for BAM Rem Integration. Rem gsbhatia 07/01/05 - New repmgr header impl Rem mkm 06/22/05 - fix for non-sysman users to access the page Rem mkm 06/16/05 - chagne of key_val deletin callback signature Rem mkm 06/10/05 - keyvalue deletion call back Rem nanand 05/13/05 - cleanup Rem mkm 03/16/05 - transposed metrics fix Rem mswamyt 04/04/05 - Fix errors Rem mswamyt 03/31/05 - Add constants Rem jmenon 02/04/05 - changing the default rule Rem jmenon 01/27/05 - Changes for callback fxns Rem mswamyt 01/24/05 - SL callback Rem mswamyt 01/12/05 - Add call back Rem mswamyt 12/15/04 - key_value fix Rem verangan 11/01/04 - Added key_value Rem snathan 10/21/04 - snathan_svclvl_merge Rem verangan 10/06/04 - Created Rem ----------------------------------------------------------- CREATE OR REPLACE PACKAGE EM_SL AS -- Module names MODULE_NAME CONSTANT VARCHAR2(10) := 'EM_SL'; -- Constants -- 00,10,01,11 G_NOT_BLACKOUT_NOT_AGENT_INCL CONSTANT NUMBER := 1; G_BLACKOUT_NOT_AGENT_INCL CONSTANT NUMBER := 2; G_NOT_BLACKOUT_AGENT_INCL CONSTANT NUMBER := 3; G_BLACKOUT_AGENT_INCL CONSTANT NUMBER := 4; -- Constant for number 1 and 0 G_TRUE CONSTANT BOOLEAN := TRUE; G_FALSE CONSTANT BOOLEAN := FALSE; -- Bound constant used for data validation END_HOUR CONSTANT NUMBER := 2400; BEGIN_HOUR CONSTANT NUMBER := 0 ; HUNDRED CONSTANT NUMBER := 100; NUM_DAYS_WEEK CONSTANT NUMBER := 7; -- Default Rule constants STANDARD_WEEK CONSTANT CHAR(7) := '1111100'; DEFAULT_BH_START CHAR(4) := '0000'; DEFAULT_BH_END CHAR(4) := '2400'; STANDARD_EXPECTED_SL CONSTANT NUMBER := 85.0; -- Type defs TYPE CURSORTYPE IS REF CURSOR; -- --------------------------------------------------------------- -- PROCEDURE : set_service_rule --------------------------------------------------------------- -- -- PURPOSE : Procedure that will be invoked to create/update -- service level rule for the service/target -- -- The service_rule_table will be updated with the value passed. -- -- Parameters -- Input Parameters: -- -- target_gud_in - Target Guid for which SL is defined -- -- date_sequence_in in BINARY_INTEGER -- A typical date sequence input will be -- 1111100 --> translating to a defn of -- business days as mon/tue/wed/thu/fri and -- non business days as sat and sun. -- The positions in date_sequence_in are mapped to -- mon/tue/wed/thu/fri/sat/sun -- -- start_time/end_time in VARCHAR2 -- A typical start and end time will be of the form -- 1030, 2030 -- > translating to busshour of 10:30AM to 8:30PM -- 0030- 2300--> translating to a buss hour of 00:30AM to 11:00PM -- -- avail_includes in NUMBER -- can be one of -- G_NOT_BLACKOUT_NOT_AGENT_INCL -- G_BLACKOUT_NOT_AGENT_INCL -- G_NOT_BLACKOUT_AGENT_INCL -- G_BLACKOUT_AGENT_INCL -- -- p_metric_guid_key_value_in in SMP_EMD_NVPAIR_ARRAY -- NV pair of metric guid and key value of the metric -- for SL defn -- -- exp_svc_level in NUMBER -- This should be less than 100. A typical value will be 95 -- -- OUTPUT Parameters : -- -- NONE --------------------------------------------------------------- PROCEDURE set_service_level( p_target_name_in IN MGMT_TARGETS.target_name%TYPE, p_target_type_in IN MGMT_TARGETS.target_type%TYPE, p_date_sequence_in IN VARCHAR2, p_start_time_in IN VARCHAR2, p_end_time_in IN VARCHAR2, p_avail_includes_in IN NUMBER, p_metric_guid_key_value_in IN SMP_EMD_NVPAIR_ARRAY, p_expected_sl_in IN NUMBER); --------------------------------------------------------------- -- PROCEDURE: get_service_rule --------------------------------------------------------------- -- -- PURPOSE : Procedure that will return the overall service rule -- for service. -- -- This procedure inturn will call get_service_coretimes and -- get_service_characterstics procedures. -- -- Parameters -- INPUT Parameters -- gud in RAW -- -- OUTPUT Parameters -- date_sequence OUT VARCHAR2 -- start_time/end_time OUT VARCHAR2 -- avail_includes OUT CHAR2 (can be 00,01,10,11) -- p_metric_guid_key_value_out OUT SMP_EMD_NVPAIR_ARRAY (NV pair of metric guid ane key value) -- p_metric_name_column_out OUT SMP_EMD_NVPAIR_ARRAY (NV pair of metric name and metric column) -- p_bmetric_guid_key_value_out OUT SMP_EMD_NVPAIR_ARRAY (NV pair of business metric guid ane key value) -- p_bmetric_name_column_out OUT SMP_EMD_NVPAIR_ARRAY (NV pair of business metric name and metric column) -- exp_svc_level OUT NUMBER --------------------------------------------------------------- PROCEDURE get_service_level( p_target_name_in IN MGMT_TARGETS.target_name%TYPE, p_target_type_in IN MGMT_TARGETS.target_type%TYPE, p_date_sequence_out OUT VARCHAR2, p_start_time_out OUT VARCHAR2, p_end_time_out OUT VARCHAR2, p_timezone_out OUT MGMT_TARGETS.timezone_region%TYPE, p_avail_includes_out OUT NUMBER, p_metric_guid_key_value_out OUT SMP_EMD_NVPAIR_ARRAY, p_metric_name_column_out OUT SMP_EMD_NVPAIR_ARRAY, p_bmetric_guid_key_value_out OUT SMP_EMD_NVPAIR_ARRAY, p_bmetric_name_column_out OUT SMP_EMD_NVPAIR_ARRAY, p_expected_sl_out OUT NUMBER); PROCEDURE set_service_level( p_target_name_in IN MGMT_TARGETS.target_name%TYPE, p_target_type_in IN MGMT_TARGETS.target_type%TYPE, p_date_sequence_in IN VARCHAR2, p_start_time_in IN VARCHAR2, p_end_time_in IN VARCHAR2, p_avail_includes_in IN NUMBER, p_metric_name_column_in IN SMP_EMD_NVPAIR_ARRAY, p_key_values_in IN VARCHAR2_TABLE, p_expected_sl_in IN NUMBER); PROCEDURE get_metricguids_from_names( p_target_name_in IN MGMT_TARGETS.target_name%TYPE, p_target_type_in IN MGMT_TARGETS.target_type%TYPE, p_metric_name_column_in IN SMP_EMD_NVPAIR_ARRAY, p_metric_guids_out OUT MGMT_USER_GUID_ARRAY); --------------------------------------------------------------- -- PROCEDURE : delete_service_level --------------------------------------------------------------- -- PURPOSE: -- This will remove even audit table entires for target -- Used mainly by the oratst. -- We dont have this functionality in UI now. -- -- Parameters -- INPUT : target_guid - service for which the rule has to be deleted -- --------------------------------------------------------------- PROCEDURE delete_service_level( p_target_name_in IN MGMT_TARGETS.target_name%TYPE, p_target_type_in IN MGMT_TARGETS.target_type%TYPE); --------------------------------------------------------- -- PROCEDURE update_sl --------------------------------------------------------- -- PURPOSE: -- A call-back function to create service level rule -- during target addition. -- -- Parameters -- INPUT : -- p_target_name - VARCHAR2 -- Name of the target added -- p_target_type - VARCHAR2 -- Type of the target added -- p_target_guid - MGMT_TARGET.target_guid%TYPE -- Guid of the target added -- PROCEDURE update_sl ( p_target_name IN mgmt_targets.target_name%TYPE, p_target_type IN mgmt_targets.target_type%TYPE, p_target_guid IN mgmt_targets.target_guid%TYPE ); --------------------------------------------------------- -- PROCEDURE delete_sl --------------------------------------------------------- -- PURPOSE: -- A call-back function to delete service level rule -- during target deletion. -- -- Parameters -- INPUT : -- p_target_name - VARCHAR2 -- Name of the target added -- p_target_type - VARCHAR2 -- Type of the target added -- p_target_guid - MGMT_TARGET.target_guid%TYPE -- Guid of the target added -- PROCEDURE delete_sl ( p_target_name IN mgmt_targets.target_name%TYPE, p_target_type IN mgmt_targets.target_type%TYPE, p_target_guid IN mgmt_targets.target_guid%TYPE ); --------------------------------------------------------- -- PROCEDURE update_sl_keyval_delete --------------------------------------------------------- -- PURPOSE: -- A call-back function to update mgmt_sl_metrics -- when key_value gets deleted for a metric. -- The entries are removed from history table as well. -- -- Parameters -- INPUT : -- p_target_guid - mgmt_targets.target_guid%TYPE -- Guid of the target affected -- p_metric_guid - mgmt_metrics.metric_guid%TYPE -- Guid of the metric affected -- p_key_value - mgmt_metrics_raw.key_value%TYPE -- key_value of the metric affected -- PROCEDURE update_sl_keyval_delete( p_target_guid IN mgmt_targets.target_guid%TYPE, p_metric_guid IN mgmt_metrics.metric_guid%TYPE, p_key_value IN mgmt_metrics_raw.key_value%TYPE ); END EM_SL; / SHOW ERRORS;