Rem drv:
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/compliance/compliance_config_std_pkgdef.sql /st_emcore_10.2.0.4.2db11.2/1 2009/06/19 09:29:00 suvmalho Exp $
Rem
Rem compliance_config_std_pkgdef.sql
Rem
Rem Copyright (c) 2006, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem compliance_config_std_pkgdef.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem niramach 10/17/06 - Renamed the commit_txn procedure.
Rem groyal 08/27/06 - Rework trend-related procedures
Rem sthiruna 08/03/06 - Commenting 11g packages
Rem niramach 07/20/06 - Add update_cs_cqs_valid procedure to em_cs_compile_util pkg.
Rem rreilly 07/19/06 - generate RQS guids
Rem aragarwa 06/28/06 - Fixing xsd changes : removing context, adding
Rem targettype etc.
Rem rreilly 06/29/06 - adding the cs compile and evaluate packages
Rem sthiruna 06/12/06 - Fix for bug 5263883
Rem aragarwa 06/11/06 - Removing default for few elements
Rem aragarwa 05/17/06 - removing dname from hierarchy table.
Rem niramach 05/03/06 - Changes related to delete eval results api &
Rem niramach - target filteration for trend pages
Rem aragarwa 05/01/06 - Adding support for reusable queries and cs
Rem inclusion
Rem niramach 04/11/06 - Add delete cs eval results PL/SQL Api.
Rem aragarwa 04/10/06 - adding few more elements to rule tables.
Rem niramach 03/27/06 - Add procedures for trend overview sqls.
Rem aragarwa 02/28/06 - reflecting XSD and table changes.
Rem niramach 03/07/06 - Change the signature of update_cs_rulefolder_eval_summ.
Rem niramach 02/28/06 - Add set_repo_timing procedure.
Rem kchiasso 02/23/06 - change owner to author
Rem niramach 01/30/06 - Add procedures for cs evaluation.
Rem aragarwa 01/23/06 - Adding procedures and functions for adding rows
Rem in cs tables.
Rem niramach 01/13/06 - Package definition for configuration standard.
Rem niramach 01/13/06 - Created
Rem
Rem ****************************************************************************
Rem EM_CONFIG_STD package definition
Rem ****************************************************************************
CREATE OR REPLACE PACKAGE em_config_std
IS
G_MODULE_NAME CONSTANT VARCHAR2(30) := 'EM_CONFIG_STD';
G_RAW_TABLE_TYPE CONSTANT INTEGER := 0;
G_1HOUR_TABLE_TYPE CONSTANT INTEGER := 1;
G_1DAY_TABLE_TYPE CONSTANT INTEGER := 2;
FUNCTION add_rulefolder(
p_cs_guid RAW,
p_rulefolder_iname VARCHAR2,
p_rulefolder_dname VARCHAR2,
p_parent_guid RAW,
p_child_position NUMBER,
p_description VARCHAR2 DEFAULT NULL,
p_reference_url VARCHAR2 DEFAULT NULL
) RETURN RAW;
FUNCTION add_rule(
p_cs_guid RAW,
p_rule_iname VARCHAR2,
p_rule_dname VARCHAR2,
p_parent_guid RAW,
p_child_position NUMBER,
p_test VARCHAR2 DEFAULT NULL,
p_importance_level NUMBER DEFAULT MGMT_CONFIG_STD.G_IMPORTANCE_NORMAL,
p_test_type NUMBER DEFAULT NULL,
p_reference_url VARCHAR2 DEFAULT NULL,
p_description VARCHAR2 DEFAULT NULL,
p_rationale VARCHAR2 DEFAULT NULL,
p_fixtext VARCHAR2 DEFAULT NULL,
p_warning VARCHAR2 DEFAULT NULL,
p_message VARCHAR2 DEFAULT NULL,
p_clear_message VARCHAR2 DEFAULT NULL,
p_severity NUMBER DEFAULT MGMT_CONFIG_STD.G_VIOL_SEVERITY_INFO,
p_policy_guid RAW DEFAULT NULL,
p_change_advisor_tag MGMT_CS_CHANGE_ADVISOR_DEF DEFAULT NULL,
p_simple_test_condition NUMBER DEFAULT NULL
) RETURN RAW;
PROCEDURE add_fixlink(
p_rule_guid RAW,
p_display_label VARCHAR2,
p_link_template VARCHAR2,
p_link_encode NUMBER,
p_is_link_empage NUMBER
);
PROCEDURE add_simple_test(
p_rule_guid RAW,
p_property VARCHAR2,
p_operator NUMBER,
p_value VARCHAR2,
p_position NUMBER
);
PROCEDURE add_keyword(
p_cs_guid RAW,
p_keyword VARCHAR2
);
PROCEDURE add_reusable_query(
p_cs_guid RAW,
p_query_iname VARCHAR2,
p_description VARCHAR2 DEFAULT NULL,
p_query VARCHAR2
);
PROCEDURE add_overridden_parameter(
p_cs_inclusion_guid RAW,
p_name VARCHAR2,
p_value VARCHAR2,
p_reference VARCHAR2 DEFAULT NULL
);
PROCEDURE add_violation_context (
p_rule_guid RAW,
p_column_iname VARCHAR2,
p_column_dname VARCHAR2,
p_column_type NUMBER,
p_column_position NUMBER,
p_is_key NUMBER,
p_is_hidden NUMBER,
p_link_template VARCHAR2,
p_link_encode NUMBER,
p_is_empage NUMBER
);
PROCEDURE add_parameter(
p_cs_guid RAW,
p_param_iname VARCHAR2,
p_description VARCHAR2 DEFAULT NULL,
p_param_type NUMBER DEFAULT MGMT_CONFIG_STD.G_STD_PARAM_NUMTYPE,
p_param_value VARCHAR2,
p_prohibit_changes NUMBER DEFAULT MGMT_CONFIG_STD.G_STD_PARAM_PROCHANGE_FALSE,
p_lower_bound NUMBER DEFAULT 0,
p_upper_bound NUMBER DEFAULT 0,
p_choice_list MGMT_CS_CHOICE_LIST_ARRAY DEFAULT NULL,
p_must_match NUMBER DEFAULT 0
);
PROCEDURE delete_cs_data(
p_cs_guid RAW);
PROCEDURE delete_cs_metadata(
p_cs_guid RAW);
PROCEDURE remove_copied_policies(
p_cs_guid RAW);
-- PURPOSE
--
-- To set the repository timing flag of the given configuration standard.This flag
-- is to enable/disable logging the performance details of the cs evaluation in the
-- the system performance logs.
--
-- Parameters
-- p_config_std_name : Configuration Standard internal name
-- p_config_std_author: Author of the configuration standard
-- p_version : Version of the configuration standard.
-- p_enabled: Enabled/disabled flag . (0/1).
PROCEDURE set_repo_timing(
p_config_std_name IN VARCHAR2,
p_config_std_author IN VARCHAR2,
p_version IN NUMBER DEFAULT 1,
p_enabled IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE);
-- ---------------------------------------------------
-- Procedure: LOG_CS_EVALUATION_RESULTS
--
-- Description:
--
-- After all CS rules have been evaluated, summary
-- counts and the compliance score are calculated
-- for all targets. The results of these
-- calculations are stored in MGMT_CS_EVAL_SUMM_* tables.
--
-- Parameters:
--
-- p_config_std_name - Configuration Standard Name
-- p_rqs_guid - CS Runtime Query Set Guid
-- p_cs_guid - Configuration Standard Guid
-- p_target_type - CS Context class
-- p_targets_list - MGMT_TARGET_ARRAY having the list of MGMT_TARGET_OBJ
-- p_repo_timing_enabled - Flag to turn on/off performance data logging.
-- p_start_time - time the evaluation started
--
-- NOTE:
-- p_targets_list and p_start_time are NULL for DMQL CS.
-- These values are retrieved from repository tables
-- for this case.
-- ---------------------------------------------------
PROCEDURE log_cs_evaluation_results(
p_config_std_name IN VARCHAR2,
p_rqs_guid IN RAW,
p_cs_guid IN RAW,
p_target_type IN VARCHAR2,
p_targets_list IN MGMT_TARGET_ARRAY DEFAULT NULL,
p_repo_timing_enabled IN NUMBER,
p_start_time IN DATE DEFAULT NULL);
-- ---------------------------------------------------
-- Procedure: PROCESS_RULE_FOLDERS
--
-- Description:
--
-- Calculates CS evaluation results for all rule
-- folders. Rule Folders deepest in the hierarchy
-- are evaluated evaluated first. This is because
-- all the child rule folder counts are needed to get
-- the values for a parent rule folder.
--
-- Parameters:
--
-- p_rqs_guid - CS Runtime Query Set Guid
-- p_cs_guid - Unique ID for the CS
-- p_targets - table of evaluated targets
--
-- ---------------------------------------------------
PROCEDURE process_rule_folders(
p_rqs_guid IN RAW,
p_cs_guid IN RAW,
p_targets IN GUID_ARRAY);
-- ---------------------------------------------------
-- Procedure: PROCESS_CS
--
-- Description:
--
-- Calculates CS counts and compliance score for
-- all targets.
--
-- Parameters:
--
-- p_rqs_guid - CS Runtime Query Set Guid
-- p_cs_guid - Unique ID for the CS
-- p_targets - table of evaluated targets
-- p_eval_date - cs evaluation date
--
-- ---------------------------------------------------
PROCEDURE process_cs(
p_rqs_guid IN RAW,
p_cs_guid IN RAW,
p_targets IN GUID_ARRAY,
p_eval_date IN DATE);
-- ----------------------------------------------------
-- Compliance trend overview charts related procedures.
-- ----------------------------------------------------
--
-- PURPOSE
-- Returns the min and max timestamps for data availability for the specified
-- metric.
--
-- Parameters
-- p_rqs_guid : Guid of config std.
-- p_object_guid : Guid of config std/rule folder/rule for which trend data is required.
-- p_metric_name : Name of metric for which trend data to be shown
-- p_metric_column : Name of metric column for which trend data to be shown
-- p_target_name : Target for which trend data to be shown
-- p_min_collection_time: Min data availablity
-- p_max_collection_time: Max data availablity
--
PROCEDURE get_min_max_collection_time(
p_rqs_guid IN RAW,
p_object_guid IN RAW,
p_metric_name IN VARCHAR2,
p_metric_column IN VARCHAR2,
p_target_name IN VARCHAR2 DEFAULT '%',
p_min_collection_time OUT DATE,
p_max_collection_time OUT DATE
);
--
-- Returns the trend(historic) data for a given configuration standard and
-- time period.
--
-- Parameters
-- p_rqs_guid : Guid of config standard for which trend data is required.
-- p_target_name : Target for which trend data to be shown
-- p_historyForNDays : History for how many days?
-- p_historyStartDate : Start date
-- p_historyEndDate : End date
-- p_eval_summ_cursor: Cursor evaluation summary trend data
-- p_comp_score_buckets_cursor: Cursor for compliance score buckets chart
--
PROCEDURE get_cs_trend_data(
p_rqs_guid IN RAW,
p_target_name IN VARCHAR2 DEFAULT '%',
p_historyForNDays IN NUMBER DEFAULT NULL,
p_historyStartDate IN DATE DEFAULT NULL,
p_historyEndDate IN DATE DEFAULT NULL,
p_eval_summ_cursor OUT sys_refcursor,
p_comp_score_buckets_cursor OUT sys_refcursor
);
--
-- PURPOSE
-- Returns the trend(historic) data for a given rule folder and time period.
--
-- Parameters
-- p_rqs_guid: Guid of the config standard for which trend data is required.
-- p_folder_guid : Guid of folder for which trend data is required.
-- p_target_name : Target for which trend data to be shown
-- p_historyForNDays : History for how many days?
-- p_historyStartDate : Start date
-- p_historyEndDate : End date
-- p_eval_summ_cursor: Cursor evaluation summary trend data
--
PROCEDURE get_folder_trend_data(
p_rqs_guid IN RAW,
p_folder_guid IN RAW,
p_target_name IN VARCHAR2 DEFAULT '%',
p_historyForNDays IN NUMBER DEFAULT NULL,
p_historyStartDate IN DATE DEFAULT NULL,
p_historyEndDate IN DATE DEFAULT NULL,
p_eval_summ_cursor OUT sys_refcursor
);
--
-- PURPOSE
-- Returns the trend(historic) data for a given rule and time period.
--
-- Parameters
-- p_rqs_guid : Guid of config standard for which trend data is required.
-- p_rule_guid : Guid of rule for which trend data is required.
-- p_target_name : Target for which trend data to be shown
-- p_historyForNDays : History for how many days?
-- p_historyStartDate : Start date
-- p_historyEndDate : End date
-- p_eval_summ_cursor: Cursor evaluation summary trend data
--
PROCEDURE get_rule_trend_data(
p_rqs_guid IN RAW,
p_rule_guid IN RAW,
p_target_name IN VARCHAR2 DEFAULT '%',
p_historyForNDays IN NUMBER DEFAULT NULL,
p_historyStartDate IN DATE DEFAULT NULL,
p_historyEndDate IN DATE DEFAULT NULL,
p_eval_summ_cursor OUT sys_refcursor
);
--
-- PURPOSE
-- Returns the table type (Raw/1hour/1day) from which the historical data to be fetched.
--
-- Parameters
-- p_historyForNDays : Number of days for which historic data is required
--
FUNCTION get_trend_table_type (
p_historyForNDays IN NUMBER DEFAULT 7
) RETURN NUMBER ;
--
-- PURPOSE
-- Returns the table type (Raw/1hour/1day) from which the historical data to be fetched.
--
-- Parameters
-- p_historyStartDate : Start date from when onwards the historic data is required.
--
FUNCTION get_trend_table_type (
p_historyStartDate IN DATE
) RETURN NUMBER ;
--Returns the repository target guid.
FUNCTION get_repos_target_guid RETURN RAW;
--
-- PURPOSE
--
-- To delete the evaluation results of the given configuration standard for all targets
-- for which the user has operator target privileges.
--
-- Parameters
-- p_config_std_guid : Configuration Standard GUID
--
PROCEDURE delete_cs_eval_results (p_config_std_guid RAW);
--
-- PURPOSE
--
-- To delete the evaluation results of the rule folder of the given config std for
-- all targets evaluated for which the user has the target operator privileges.
-- This api will be called from delete eval results job.
--
-- Parameters
-- p_config_std_guid : Configuration Standard GUID
--
PROCEDURE delete_eval_results_data (
p_config_std_guid RAW);
--
-- PURPOSE
--
-- Executes the given delete cs statement and commits along the way based on the MAX_COMMIT value.
--
--
-- Parameters
-- p_execute_stmt : Statement to be executed
-- p_config_std_guid : Configuration Standard Guid
-- p_is_super_user : Whether the current em user is super user or not.
--
-- PROCEDURE commit_delete_cs_txn(p_execute_stmt VARCHAR2 , p_config_std_guid RAW,
-- p_is_super_user INTEGER );
--
-- PURPOSE
--
-- Submits the delete eval results job which takes care of deleting the evaluation
-- results from rule folder summary and policy evaluation results tables.
--
--
-- Parameters
-- p_config_std_guid : Configuration Standard GUID
--
PROCEDURE submit_delete_eval_results_job(
p_config_std_guid RAW);
END em_config_std;
/
show errors;