Rem
Rem $Header: test_metadata_read_pkgbody.sql 15-jun-2005.14:23:16 afontana Exp $
Rem
Rem test_metadata_read_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      test_metadata_read_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      API to read test metadata
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    afontana    06/15/05 - loosen version semantics for READ_TEST_LIST 
Rem    rmarripa    05/20/05 - remove READ_TEST_METADATA forward declaration 
Rem    afontana    05/24/05 - read metrics reads distinct 
Rem    afontana    04/26/05 - add default promotions to TestTargetMetrics 
Rem    afontana    01/12/05 - add short names 
Rem    rmarripa    11/23/04 - add proc to get avail metric 
Rem    afontana    11/18/04 - add is_for_summary 
Rem    afontana    11/16/04 - test metadata enhancements 
Rem    afontana    11/11/04 - change mgmt_test_target_map to 
Rem                           mgmt_test_target_map 
Rem    afontana    10/05/04 - add encrypt field to MGMT_TEST_METRIC_PROPS 
Rem    afontana    09/27/04 - read testMetadata with test properties 
Rem    afontana    09/16/04 - filter query to only read table metrics into 
Rem                           metrics_out 
Rem    afontana    09/10/04 - afontana_test_meta_repos
Rem    afontana    09/09/04 - add password attribute to properties 
Rem    afontana    08/30/04 - add level 
Rem    afontana    08/09/04 - Created
Rem

CREATE OR REPLACE PACKAGE BODY MGMT_TEST_METADATA_READ AS

-----------------------------------------------------------------------------
--
-- Private Forward Declarations
--
--

PROCEDURE READ_TEST_QUALS (
  v_test_type         IN VARCHAR2,
  v_quals             OUT mgmt_test_qualifier_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
);

PROCEDURE READ_TEST_PROPERTIES_NOGROUPS (
  v_test_type        IN VARCHAR2,
  v_properties       OUT mgmt_test_prop_arr,
  v_choices          OUT mgmt_test_choice_arr,
  v_levels           OUT mgmt_test_prop_level_arr,
  v_result           OUT INTEGER,
  v_err_desc         OUT VARCHAR2
);

PROCEDURE READ_TEST_PROPS (
  v_test_type         IN VARCHAR2,
  v_properties        OUT mgmt_test_prop_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
);


PROCEDURE READ_TEST_PROP_QUALS (
  v_test_type         IN VARCHAR2,
  v_quals             OUT mgmt_test_prop_qualifier_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
);

PROCEDURE READ_TEST_CHOICES (
  v_test_type         IN VARCHAR2,
  v_choices           OUT mgmt_test_choice_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
);

PROCEDURE READ_TEST_LEVELS (
  v_test_type         IN VARCHAR2,
  v_levels            OUT mgmt_test_prop_level_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
);

PROCEDURE READ_TEST_GROUPS (
  v_test_type         IN VARCHAR2,
  v_groups            OUT mgmt_test_prop_group_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
);

PROCEDURE READ_TEST_METRICS (
  v_test_type         IN VARCHAR2,
  v_metrics           OUT mgmt_test_metric_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
);

PROCEDURE READ_TEST_METRICS_OUT (
  v_test_type            IN VARCHAR2,   
  v_target_type          IN VARCHAR2,
  v_target_version       IN VARCHAR2,
  v_metric_name          IN VARCHAR2,
  v_metrics              OUT mgmt_test_metric_out_arr,
  v_result               OUT INTEGER,
  v_err_desc             OUT VARCHAR2
);

PROCEDURE READ_TEST_MCOLUMNS (
  v_test_type            IN VARCHAR2,   
  v_target_type          IN VARCHAR2,
  v_target_version       IN VARCHAR2,
  v_metric_name          IN VARCHAR2,
  v_metrics              OUT mgmt_test_mcolumn_out_arr,
  v_result               OUT INTEGER,
  v_err_desc             OUT VARCHAR2
);

PROCEDURE READ_TEST_MCOLUMN_VER (
  v_test_type         IN  VARCHAR2,
  v_metric            IN  VARCHAR2,
  v_mcolumns          OUT mgmt_test_mcolumn_ver_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
);

PROCEDURE READ_TEST_MPROP_VER (
  v_test_type         IN  VARCHAR2,
  v_mprops            OUT mgmt_test_mprop_ver_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
);

------------------------------------------------------------------------------
--
-- Public Procedures
--


--
-- PROCEDURE READ_TEST_LIST
-- 
-- Reads the entire list of tests that are not deprecated.
--
-- On success, returns 0.
-- On failure, returns < 0 with err_desc filled.
--
PROCEDURE READ_TEST_LIST (
  v_target_type     IN VARCHAR2,
  v_target_ver      IN VARCHAR2,
  v_cat_prop1       IN VARCHAR2,
  v_cat_prop2       IN VARCHAR2,
  v_cat_prop3       IN VARCHAR2,
  v_cat_prop4       IN VARCHAR2,
  v_cat_prop5       IN VARCHAR2,
  v_tests           OUT mgmt_test_display_arr,
  v_result          OUT INTEGER,
  v_err_desc        OUT VARCHAR2
) IS
  v_ctr           PLS_INTEGER;
BEGIN
  v_tests := mgmt_test_display_arr();
  
  v_ctr := 1;
     
    FOR disp IN (SELECT DISTINCT 
                    test.test_type, test.label, test.nlsid, 
                    test.resource_bundle
                   FROM mgmt_test test,
                        mgmt_test_target_map map
                  WHERE deprecated <> 'Y'
                    AND test.test_type = map.test_type
                    AND (map.target_type = v_target_type OR
                        v_target_type IS NULL)
                    AND (v_target_ver IS NULL OR 
                         em_target.compare_type_meta_vers(map.target_meta_ver, 
                                                          v_target_ver) <= 0)
                    AND (map.cat_prop1 = v_cat_prop1 OR
                        map.cat_prop1 = ' ')
                    AND (map.cat_prop2 = v_cat_prop2 OR
                        map.cat_prop2 = ' ')                        
                    AND (map.cat_prop3 = v_cat_prop3 OR
                        map.cat_prop3 = ' ')                       
                    AND (map.cat_prop4 = v_cat_prop4 OR
                        map.cat_prop4 = ' ')                        
                    AND (map.cat_prop5 = v_cat_prop5 OR
                        map.cat_prop5 = ' ')
                  ORDER BY label)
    LOOP
      v_tests.extend(1);
      v_tests(v_ctr) := 
        mgmt_test_display_obj(disp.test_type, disp.label, disp.nlsid,
                              disp.resource_bundle);
      v_ctr := v_ctr + 1;
    END LOOP;
  
  v_result := p_success;
  v_err_desc := NULL;

EXCEPTION
  WHEN OTHERS THEN
    v_tests := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_TEST_LIST ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_TEST_LIST;

--
-- PROCEDURE READ_COLLECTION_METADATA
-- 
-- Reads the entire list of metrics (in order)
-- and the collection generator.
--
-- On success, returns 0.
-- On failure, returns < 0 with err_desc filled.
--
PROCEDURE READ_COLLECTION_METADATA (
  v_test_type          IN VARCHAR2,
  v_test               OUT mgmt_test_metadata_obj,
  v_test_quals         OUT mgmt_test_qualifier_arr,
  v_metrics            OUT mgmt_test_metric_arr,
  v_valid_columns      OUT mgmt_test_mcolumn_ver_arr,
  v_valid_props        OUT mgmt_test_mprop_ver_arr,
  v_result             OUT INTEGER,
  v_err_desc           OUT VARCHAR2
) IS
BEGIN

  v_result := p_success;
  
  READ_TEST_METADATA(
    v_test_type, v_test, v_result, v_err_desc
  );
  IF v_result <> p_success THEN
    v_metrics := NULL;
    v_valid_columns := NULL;
    v_test := NULL;
    v_valid_props := NULL;
    v_test_quals := NULL;
    RETURN;
  END IF;

  READ_TEST_QUALS (
    v_test_type, v_test_quals, v_result, v_err_desc
  );
  IF v_result <> p_success THEN
    v_metrics := NULL;
    v_valid_columns := NULL;
    v_test := NULL;
    v_valid_props := NULL;
    v_test_quals := NULL;
    RETURN;
  END IF;

  READ_TEST_METRICS (
    v_test_type, v_metrics, v_result, v_err_desc
  );
  IF v_result <> p_success THEN
    v_metrics := NULL;
    v_valid_columns := NULL;
    v_test := NULL;
    v_valid_props := NULL;
    v_test_quals := NULL;
    RETURN;
  END IF;
  
  READ_TEST_MCOLUMN_VER (
    v_test_type, NULL, v_valid_columns, v_result, v_err_desc
  );
  IF v_result <> p_success THEN
    v_metrics := NULL;
    v_valid_columns := NULL;
    v_valid_props := NULL;
    v_test := NULL;
    v_test_quals := NULL;
    RETURN;
  END IF;
  
  READ_TEST_MPROP_VER (
    v_test_type, v_valid_props, v_result, v_err_desc
  );
  IF v_result <> p_success THEN
    v_metrics := NULL;
    v_valid_columns := NULL;
    v_valid_props := NULL;
    v_test := NULL;
    v_test_quals := NULL;
    RETURN;
  END IF;
  
  v_result := p_success;
  v_err_desc := NULL;

EXCEPTION
  WHEN OTHERS THEN
    v_metrics := NULL;
    v_valid_columns := NULL;
    v_valid_props := NULL;
    v_test := NULL;
    v_test_quals := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR(
       'MGMT_TEST_METADATA_READ.READ_COLLECTION_METADATA ' 
        || SQLERRM, 1, p_max_err_size);
                         
END READ_COLLECTION_METADATA;


--
-- PROCEDURE READ_TEST_PROPERTIES_ALL
--
-- Read a list of properties, choices and UI groups 
-- for a given test id and a given scope.
-- The input scope will be used as a filter.
-- If scope is NULL, all properties will be returned.
--
-- On success, returns 0.
-- On failure, returns < 0 with err_desc filled.
--
PROCEDURE READ_TEST_PROPERTIES_ALL (
  v_test_type          IN VARCHAR2,
  v_test               OUT mgmt_test_metadata_obj,
  v_test_quals         OUT mgmt_test_qualifier_arr,
  v_groups             OUT mgmt_test_prop_group_arr,
  v_properties         OUT mgmt_test_prop_arr,
  v_prop_quals         OUT mgmt_test_prop_qualifier_arr,
  v_choices            OUT mgmt_test_choice_arr,
  v_levels             OUT mgmt_test_prop_level_arr,
  v_result             OUT INTEGER,
  v_err_desc           OUT VARCHAR2
) IS
BEGIN

  v_result := p_success;
  v_err_desc := NULL;

  READ_TEST_METADATA(
    v_test_type, v_test, v_result, v_err_desc
  );
  IF v_result <> p_success THEN
    v_properties := NULL;
    v_choices := NULL;
    v_groups := NULL;
    v_levels := NULL;
    v_test := NULL;
    v_test_quals := NULL;
    v_prop_quals := NULL;
    RETURN;
  END IF;


  READ_TEST_QUALS (
    v_test_type, v_test_quals, v_result, v_err_desc
  );
  IF v_result <> p_success THEN
    v_properties := NULL;
    v_choices := NULL;
    v_groups := NULL;
    v_levels := NULL;
    v_test := NULL;
    v_test_quals := NULL;
    v_prop_quals := NULL;
    RETURN;
  END IF;


  READ_TEST_PROP_QUALS (
    v_test_type, v_prop_quals, v_result, v_err_desc
  );
  IF v_result <> p_success THEN
    v_properties := NULL;
    v_choices := NULL;
    v_groups := NULL;
    v_levels := NULL;
    v_test := NULL;
    v_test_quals := NULL;
    v_prop_quals := NULL;
    RETURN;
  END IF;

  READ_TEST_PROPERTIES_NOGROUPS (v_test_type,
                                 v_properties, v_choices, v_levels,
                                 v_result, v_err_desc);
  IF v_result <> p_success THEN
    v_properties := NULL;
    v_choices := NULL;
    v_groups := NULL;
    v_levels := NULL;
    v_test := NULL;
    v_test_quals := NULL;
    v_prop_quals := NULL;
    RETURN;
  END IF;
  
  READ_TEST_GROUPS (v_test_type, v_groups,
                    v_result, v_err_desc);
  IF v_result <> p_success THEN
    v_properties := NULL;
    v_choices := NULL;
    v_groups := NULL;
    v_levels := NULL;
    v_test := NULL;
    v_test_quals := NULL;
    v_prop_quals := NULL;
    RETURN;
  END IF;
  
EXCEPTION
  WHEN OTHERS THEN
    v_properties := NULL;
    v_choices := NULL;
    v_groups := NULL;
    v_levels := NULL;
    v_test := NULL;
    v_test_quals := NULL;
    v_prop_quals := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR(
                        'MGMT_TEST_METADATA_READ.READ_TEST_PROPERTIES_ALL ' 
                         || SQLERRM, 1, p_max_err_size);

END READ_TEST_PROPERTIES_ALL;

--
-- PROCEDURE READ_TEST_PROPERTIES_NOGROUPS
--
-- Read a list of properties and choices
-- for a given test id and a given scope.
-- The input scope will be used as a filter.
-- If scope is NULL, all properties will be returned.
--
-- Same as READ_TEST_PROPERTIES_ALL except it does not
-- return any groups.  Display order in this case will be
-- computed by ordering first according to group, then
-- according to the property display order.
--
-- On success, returns 0.
-- On failure, returns < 0 with err_desc filled.
--
PROCEDURE READ_TEST_PROPERTIES_NOGROUPS (
  v_test_type        IN VARCHAR2,
  v_properties       OUT mgmt_test_prop_arr,
  v_choices          OUT mgmt_test_choice_arr,
  v_levels           OUT mgmt_test_prop_level_arr,
  v_result           OUT INTEGER,
  v_err_desc         OUT VARCHAR2
) IS
BEGIN

  v_result := p_success;
  v_err_desc := NULL;

  READ_TEST_PROPS (v_test_type,
                   v_properties, v_result, v_err_desc);
  IF v_result <> p_success THEN
    v_properties := NULL;
    v_choices := NULL;
    v_levels := NULL;
    RETURN;
  END IF;

  READ_TEST_CHOICES (v_test_type,
                     v_choices, v_result, v_err_desc);
  IF v_result <> p_success THEN
    v_properties := NULL;
    v_choices := NULL;
    v_levels := NULL;
    RETURN;
  END IF;

  READ_TEST_LEVELS (v_test_type,
                     v_levels, v_result, v_err_desc);
  IF v_result <> p_success THEN
    v_properties := NULL;
    v_choices := NULL;
    v_levels := NULL;
    RETURN;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    v_properties := NULL;
    v_choices := NULL;
    v_levels := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR(
                   'MGMT_TEST_METADATA_READ.READ_TEST_PROPERTIES_NOGROUPS ' 
                    || SQLERRM, 1, p_max_err_size);

END READ_TEST_PROPERTIES_NOGROUPS;

--
-- PROCEDURE READ_TEST_METRIC_COLUMNS
-- 
-- Reads the metric columns for a given test_type and metric.
-- If metric is NULL, then all metric columns for a test will be read.
--
-- This function will search MGMT_METRICS for metric columns that exist
-- but are not listed in the test.  This allows a generic UI to absorb
-- new metric columns that are added whenever a beacon is upgraded.
--
-- On success, returns 0.
-- On failure, returns < 0 with err_desc filled.
--
PROCEDURE READ_TEST_METRIC_COLUMNS (
  v_test_type            IN  VARCHAR2,   
  v_target_type          IN  VARCHAR2,
  v_target_version       IN  VARCHAR2,
  v_metric_name          IN  VARCHAR2,
  v_test                 OUT mgmt_test_metadata_obj,
  v_test_quals           OUT mgmt_test_qualifier_arr,
  v_metrics              OUT mgmt_test_metric_out_arr,
  v_metric_columns       OUT mgmt_test_mcolumn_out_arr,
  v_test_promos          OUT mgmt_test_promotion_arr,
  v_result               OUT INTEGER,
  v_err_desc             OUT VARCHAR2
) IS
  v_resolved_version     MGMT_TARGET_TYPES.MAX_TYPE_META_VER%TYPE;
BEGIN

  v_result := p_success;
  v_err_desc := NULL;

  IF v_target_version IS NULL THEN
    SELECT MAX_TYPE_META_VER
      INTO v_resolved_version
      FROM MGMT_TARGET_TYPES
     WHERE TARGET_TYPE = v_target_type;
  ELSE
    v_resolved_version := v_target_version;
  END IF;


  READ_TEST_METRICS_OUT (v_test_type, v_target_type, v_resolved_version, 
                      v_metric_name, v_metrics, v_result, v_err_desc);
  IF v_result <> p_success THEN
    v_test := NULL;
    v_metrics := NULL;
    v_metric_columns := NULL;
    v_test_quals := NULL;
    v_test_promos := NULL;
    RETURN;
  END IF;

  READ_TEST_MCOLUMNS (v_test_type, v_target_type, v_resolved_version, 
                      v_metric_name, v_metric_columns, v_result, v_err_desc);
  IF v_result <> p_success THEN
    v_test := NULL;
    v_metrics := NULL;
    v_metric_columns := NULL;
    v_test_quals := NULL;
    v_test_promos := NULL;
    RETURN;
  END IF;

  READ_TEST_METADATA(
    v_test_type, v_test, v_result, v_err_desc
  );
  IF v_result <> p_success THEN
    v_test := NULL;
    v_metrics := NULL;
    v_metric_columns := NULL;
    v_test_quals := NULL;
    v_test_promos := NULL;
    RETURN;
  END IF;


  READ_TEST_QUALS (
    v_test_type, v_test_quals, v_result, v_err_desc
  );
  IF v_result <> p_success THEN
    v_test := NULL;
    v_metrics := NULL;
    v_metric_columns := NULL;
    v_test_quals := NULL;
    v_test_promos := NULL;
    RETURN;
  END IF;

  READ_DEFAULT_PROMOTIONS (
    v_test_type, v_test_promos, v_result, v_err_desc
  );
  IF v_result <> p_success THEN
    v_test := NULL;
    v_metrics := NULL;
    v_metric_columns := NULL;
    v_test_quals := NULL;
    v_test_promos := NULL;
    RETURN;
  END IF;


EXCEPTION
  WHEN OTHERS THEN
    v_test := NULL;
    v_metrics := NULL;
    v_metric_columns := NULL;
    v_test_quals := NULL;
    v_test_promos := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR(
                        'MGMT_TEST_METADATA_READ.READ_TEST_METRIC_COLUMNS ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_TEST_METRIC_COLUMNS;

--
-- PROCEDURE READ_DEFAULT_PROMOTIONS
--
-- Given a test type, read in all the default promotions.
--
PROCEDURE READ_DEFAULT_PROMOTIONS (
  v_test_type        IN VARCHAR2,
  v_promotions       OUT mgmt_test_promotion_arr,
  v_result           OUT INTEGER,
  v_err_desc         OUT VARCHAR2
) IS
  v_ctr              PLS_INTEGER;
BEGIN
  v_promotions := mgmt_test_promotion_arr();
  v_ctr := 1;
  
    FOR rec IN  (SELECT 
                src_metric_name,
                src_metric_column,
                src_metric_key1_value,
                src_metric_key2_value,
                src_metric_key3_value,
                src_metric_key4_value,          
                src_metric_key5_value,          
                eval_func,
                critical,
                warning,
                operator,
                num_occurrences,
                dest_metric_name,
                dest_metric_column,
                dest_metric_key1_value,
                dest_metric_key2_value,
                dest_metric_key3_value,
                dest_metric_key4_value,          
                dest_metric_key5_value, 
                default_chart
                FROM mgmt_test_default_promotion
                WHERE test_type = v_test_type)
    LOOP
      v_promotions.extend(1);
      v_promotions(v_ctr) := 
        mgmt_test_promotion_obj(
                rec.src_metric_name,
                rec.src_metric_column,
                rec.src_metric_key1_value,
                rec.src_metric_key2_value,
                rec.src_metric_key3_value,
                rec.src_metric_key4_value,          
                rec.src_metric_key5_value,          
                rec.eval_func,
                rec.critical,
                rec.warning,
                rec.operator,
                rec.num_occurrences,
                rec.dest_metric_name,
                rec.dest_metric_column,
                rec.dest_metric_key1_value,
                rec.dest_metric_key2_value,
                rec.dest_metric_key3_value,
                rec.dest_metric_key4_value,          
                rec.dest_metric_key5_value, 
                rec.default_chart);
      v_ctr := v_ctr + 1;
    END LOOP;
  v_result := p_success;
  v_err_desc := NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_promotions := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_DEFAULT_PROMOTIONS ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_DEFAULT_PROMOTIONS;

--
-- PROCEDURE READ_DEFAULT_THRESHOLDS
--
-- Given a test type, read the default beacon thresholds.
--
PROCEDURE READ_DEFAULT_THRESHOLDS (
  v_test_type          IN VARCHAR2,
  v_thresholds         OUT mgmt_test_thresholds_arr,
  v_result             OUT INTEGER,
  v_err_desc           OUT VARCHAR2
) IS
  v_ctr               PLS_INTEGER;
BEGIN
  v_thresholds := mgmt_test_thresholds_arr();
  v_ctr := 1;
  
    FOR rec IN  (SELECT 
                metric_name,
                metric_column,
                metric_key1_value,
                metric_key2_value,
                metric_key3_value,
                metric_key4_value,
                metric_key5_value,         
                critical,          
                warning,
                operator,
                num_occurrences
                FROM mgmt_test_default_thresholds
                WHERE test_type = v_test_type)
    LOOP
      v_thresholds.extend(1);
      v_thresholds(v_ctr) := 
        mgmt_test_thresholds_obj(
          rec.metric_name,
          rec.metric_column,
          rec.metric_key1_value,
          rec.metric_key2_value,
          rec.metric_key3_value,
          rec.metric_key4_value,
          rec.metric_key5_value,         
          rec.critical,          
          rec.warning,
          rec.operator,
          rec.num_occurrences);
      v_ctr := v_ctr + 1;
    END LOOP;
  v_result := p_success;
  v_err_desc := NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_thresholds := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_DEFAULT_THRESHOLDS ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_DEFAULT_THRESHOLDS;


------------------------------------------------------------------------------
--
-- Private Procedures
--

PROCEDURE READ_TEST_METADATA (
  v_test_type         IN VARCHAR2,
  v_metadata          OUT mgmt_test_metadata_obj,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
) IS
BEGIN

  SELECT mgmt_test_metadata_obj(
         v_test_type,
         rec.label,
         rec.nlsid,
         rec.resource_bundle,
         rec.collection_generator,
         rec.min_beacon_ver,
         rec.test_version,
         rec.deprecated,
         rec.avail_metric,
         rec.avail_metric_column,
         rec.help_id,
         rec.tip_text,
         rec.tip_text_nlsid,
         rec.validator
         )
    INTO v_metadata
    FROM (SELECT *
            FROM MGMT_TEST
           WHERE test_type = v_test_type) rec;

    v_result := p_success;

EXCEPTION
  WHEN OTHERS THEN
    v_metadata := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_TEST_METADATA' 
                         || SQLERRM, 1, p_max_err_size);    

END READ_TEST_METADATA;


PROCEDURE READ_TEST_QUALS (
  v_test_type         IN VARCHAR2,
  v_quals             OUT mgmt_test_qualifier_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
) IS
BEGIN
    SELECT mgmt_test_qualifier_obj(rec.qualifier,
                                   rec.text_value,
                                   rec.numeric_value)
    BULK COLLECT INTO v_quals
    FROM (SELECT *
            FROM MGMT_TEST_QUALIFIERS
           WHERE test_type = v_test_type) rec;

  v_result := p_success;
  v_err_desc := NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_quals := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_TEST_QUALS ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_TEST_QUALS;


PROCEDURE READ_TEST_PROPS (
  v_test_type         IN VARCHAR2,
  v_properties        OUT mgmt_test_prop_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
) IS
BEGIN
    SELECT mgmt_test_prop_obj(rec.prop_name,
                              rec.group_name,
                              rec.display_order,
                              rec.label,
                              rec.nlsid,
                              rec.property_type,
                              rec.default_numeric_value,
                              rec.default_string_value,
                              rec.max_value,
                              rec.min_value,
                              rec.hidden,
                              rec.encrypt,
                              rec.password,
                              rec.varies_per_beacon,
                              rec.tip_text,
                              rec.tip_text_nlsid,
                              rec.validator)
    BULK COLLECT INTO v_properties
    FROM (SELECT * 
            FROM mgmt_test_prop 
           WHERE test_type = v_test_type) rec;

  v_result := p_success;
  v_err_desc := NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_properties := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_TEST_PROPS ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_TEST_PROPS;


PROCEDURE READ_TEST_PROP_QUALS (
  v_test_type         IN VARCHAR2,
  v_quals             OUT mgmt_test_prop_qualifier_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
) IS
BEGIN
  SELECT mgmt_test_prop_qualifier_obj(rec.property,
                                      rec.qualifier,
                                      rec.text_value,
                                      rec.numeric_value)
  BULK COLLECT INTO v_quals
  FROM (SELECT *
          FROM MGMT_TEST_PROP_QUALIFIERS
         WHERE test_type = v_test_type) rec;

  v_result := p_success;
  v_err_desc := NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_quals := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_TEST_PROP_QUALS ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_TEST_PROP_QUALS;

PROCEDURE READ_TEST_CHOICES (
  v_test_type         IN VARCHAR2,
  v_choices           OUT mgmt_test_choice_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
) IS
  v_ctr               PLS_INTEGER;
BEGIN
  v_choices := mgmt_test_choice_arr();
  v_ctr := 1;
    FOR rec IN  (SELECT choice.prop_name,
                        choice.choice_name,
                        choice.display_order,
                        choice.label,
                        choice.nlsid
                 FROM mgmt_test_prop_choices choice
                WHERE choice.test_type = v_test_type)
    LOOP
      v_choices.extend(1);
      v_choices(v_ctr) := 
        mgmt_test_choice_obj(rec.prop_name,
                             rec.choice_name,
                             rec.label,
                             rec.nlsid,
                             rec.display_order);
      v_ctr := v_ctr + 1;
    END LOOP;
  v_result := p_success;
  v_err_desc := NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_choices := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_TEST_CHOICES ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_TEST_CHOICES;

PROCEDURE READ_TEST_LEVELS (
  v_test_type         IN VARCHAR2,
  v_levels            OUT mgmt_test_prop_level_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
) IS
  v_ctr               PLS_INTEGER;
BEGIN
  v_levels := mgmt_test_prop_level_arr();
  v_ctr := 1;
    FOR rec IN  (SELECT levels.property,
                        levels.level_name,
                        levels.optional
                 FROM mgmt_test_prop_level levels
                WHERE levels.test_type = v_test_type)
    LOOP
      v_levels.extend(1);
      v_levels(v_ctr) := 
        mgmt_test_prop_level_obj(rec.property,
                                rec.level_name,
                                rec.optional);
      v_ctr := v_ctr + 1;
    END LOOP;
  v_result := p_success;
  v_err_desc := NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_levels := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_TEST_LEVELS ' 
                         || SQLERRM, 1, p_max_err_size);    
END READ_TEST_LEVELS;


PROCEDURE READ_TEST_GROUPS (
  v_test_type         IN VARCHAR2,
  v_groups            OUT mgmt_test_prop_group_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
) IS
  v_ctr               PLS_INTEGER;
BEGIN

  SELECT mgmt_test_prop_group_obj(rec.group_name,
                                  rec.display_order,
                                  rec.label,
                                  rec.nlsid,
                                  rec.tip_text,
                                  rec.tip_text_nlsid)
  BULK COLLECT INTO v_groups
  FROM (SELECT * 
          FROM mgmt_test_prop_uigroup
         WHERE test_type =  v_test_type) rec;

  v_result := p_success;
  v_err_desc := NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_groups := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_TEST_GROUPS ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_TEST_GROUPS;


PROCEDURE READ_TEST_METRICS (
  v_test_type         IN VARCHAR2,
  v_metrics           OUT mgmt_test_metric_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
) IS
  v_ctr               PLS_INTEGER;
BEGIN
  v_metrics := mgmt_test_metric_arr();
  v_ctr := 1;
    FOR rec IN (SELECT metric_name,
                       metric_number,
                       interactive,
                       level_name
                 FROM mgmt_test_metrics
                WHERE test_type = v_test_type)
    LOOP
      v_metrics.extend(1);
      v_metrics(v_ctr) := 
        mgmt_test_metric_obj(rec.metric_name,
                             rec.metric_number,
                             rec.interactive,
                             rec.level_name);
      v_ctr := v_ctr + 1;
    END LOOP;
  v_result := p_success;
  v_err_desc := NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_metrics := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_TEST_METRICS ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_TEST_METRICS;

PROCEDURE READ_TEST_METRICS_OUT (
  v_test_type            IN VARCHAR2,   
  v_target_type          IN VARCHAR2,
  v_target_version       IN VARCHAR2,
  v_metric_name          IN VARCHAR2,
  v_metrics              OUT mgmt_test_metric_out_arr,
  v_result               OUT INTEGER,
  v_err_desc             OUT VARCHAR2
) IS
  v_ctr                 PLS_INTEGER;
  v_display_order       NUMBER;
BEGIN
  v_metrics := mgmt_test_metric_out_arr();
  v_ctr := 1;
  
    FOR rec IN (SELECT DISTINCT
                       test.metric_name name,
                       metr.metric_label label,
                       metr.metric_label_nlsid nlsid,
                       test.metric_number metric_number,
                       test.interactive interactive,
                       test.level_name
                 FROM mgmt_test_metrics test,
                      mgmt_metrics metr
                WHERE test.test_type = v_test_type
                  AND (v_metric_name IS NULL OR
                       v_metric_name = test.metric_name)
                  AND test.metric_name = metr.metric_name
                  AND metr.target_type = v_target_type
                  AND (v_target_version IS NULL OR
                       v_target_version = metr.type_meta_ver)
                  AND metr.metric_type = MGMT_GLOBAL.G_METRIC_TYPE_TABLE)
    LOOP
      v_metrics.extend(1);
      v_metrics(v_ctr) := 
        mgmt_test_metric_out_obj(rec.name,
                                  rec.label,
                                  rec.nlsid,
                                  rec.metric_number,
                                  rec.interactive,
                                  rec.level_name);
      v_ctr := v_ctr + 1;
    END LOOP;
  v_result := p_success;
  v_err_desc := NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_metrics := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_TEST_MCOLUMNS ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_TEST_METRICS_OUT;

PROCEDURE READ_TEST_MCOLUMNS (
  v_test_type            IN VARCHAR2,   
  v_target_type          IN VARCHAR2,
  v_target_version       IN VARCHAR2,
  v_metric_name          IN VARCHAR2,
  v_metrics              OUT mgmt_test_mcolumn_out_arr,
  v_result               OUT INTEGER,
  v_err_desc             OUT VARCHAR2
) IS
  v_ctr                 PLS_INTEGER;
  v_display_order       NUMBER;
BEGIN
  v_metrics := mgmt_test_mcolumn_out_arr();
  v_ctr := 1;
    FOR rec IN  (SELECT DISTINCT
    -- Distinct is not required but is used defensively
                        metr.metric_guid metric_guid,
                        metr.metric_name metric_name,
                        metr.metric_column metric_column,
                        metr.column_label label,
                        metr.column_label_nlsid nlsid,
                        metr.metric_type type,
                        metr.is_for_summary,
                        metr.short_name,
                        metr.short_name_nlsid
                 FROM mgmt_metrics metr
                 WHERE (v_metric_name = metr.metric_name
                       OR (v_metric_name IS NULL AND          
                      metr.metric_name IN
                      (SELECT tmetr.metric_name
                         FROM  mgmt_test_metrics tmetr
                        WHERE  tmetr.test_type = v_test_type)))
                  AND metr.target_type = v_target_type
                  AND (metr.type_meta_ver = v_target_version OR
                        v_target_version IS NULL)
                  AND metr.metric_type IN
                      (MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
                       MGMT_GLOBAL.G_METRIC_TYPE_STRING
                      )
                  AND metr.key_order = 0)
    LOOP
      v_metrics.extend(1);
      
      BEGIN      
        SELECT display_order
        INTO v_display_order
        FROM MGMT_TEST_MCOLUMNS
        WHERE test_type = v_test_type
          AND metric_name = rec.metric_name
          AND metric_column = rec.metric_column;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          v_display_order := NULL;
      END;
      
      v_metrics(v_ctr) := 
        mgmt_test_mcolumn_out_obj(RAWTOHEX(rec.metric_guid),
                                  rec.metric_name,
                                  rec.metric_column,
                                  v_display_order,
                                  rec.label,
                                  rec.nlsid,
                                  rec.type,
                                  rec.is_for_summary,
                                  rec.short_name,
                                  rec.short_name_nlsid);
      v_ctr := v_ctr + 1;
    END LOOP;
  v_result := p_success;
  v_err_desc := NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_metrics := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_TEST_MCOLUMNS ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_TEST_MCOLUMNS;

PROCEDURE READ_TEST_MCOLUMN_VER (
  v_test_type         IN  VARCHAR2,
  v_metric            IN  VARCHAR2,
  v_mcolumns          OUT mgmt_test_mcolumn_ver_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
) IS
  v_ctr               PLS_INTEGER;
BEGIN
  v_mcolumns := mgmt_test_mcolumn_ver_arr();
  v_ctr := 1;
    FOR rec IN  (SELECT DISTINCT
    -- Distinct is not required but is used defensively
                        metr.metric_name,
                        metr.metric_column,
                        metr.type_meta_ver
                 FROM mgmt_metrics metr
                 WHERE (v_metric = metr.metric_name
                       OR (v_metric IS NULL AND          
                      metr.metric_name IN
                      (SELECT tmetr.metric_name
                         FROM  mgmt_test_metrics tmetr
                        WHERE  tmetr.test_type = v_test_type)))
                  AND metr.target_type = MGMT_GLOBAL.G_BEACON_TARGET_TYPE
                  AND metr.metric_type IN
                      (MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
                       MGMT_GLOBAL.G_METRIC_TYPE_STRING
                      )
                  AND metr.key_order = 0)
    LOOP
      v_mcolumns.extend(1);
      v_mcolumns(v_ctr) := 
        mgmt_test_mcolumn_ver_obj(rec.metric_name,
                                  rec.metric_column,
                                  rec.type_meta_ver);
      v_ctr := v_ctr + 1;
    END LOOP;
  v_result := p_success;
  v_err_desc := NULL;
EXCEPTION
  WHEN OTHERS THEN
    v_mcolumns := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_TEST_MCOLUMN_VER ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_TEST_MCOLUMN_VER;

PROCEDURE READ_TEST_MPROP_VER (
  v_test_type         IN  VARCHAR2,
  v_mprops            OUT mgmt_test_mprop_ver_arr,
  v_result            OUT INTEGER,
  v_err_desc          OUT VARCHAR2
) IS
  v_ctr               PLS_INTEGER;
BEGIN
  v_mprops := mgmt_test_mprop_ver_arr();
  v_ctr := 1;

    FOR rec IN  (SELECT DISTINCT 
    -- Distinct is not required but is used defensively
                        metr.target_type,
                        metr.type_meta_ver,
                        metr.metric_name,
                        metr.property_name,
                        metr.scope,
                        metr.optional,
                        metr.encrypt
                 FROM mgmt_test_metric_props metr
                 WHERE metr.metric_name IN
                       (SELECT tmetr.metric_name
                          FROM mgmt_test_metrics tmetr
                         WHERE tmetr.test_type = v_test_type)
                  AND metr.target_type = MGMT_GLOBAL.G_BEACON_TARGET_TYPE)
    LOOP
      v_mprops.extend(1);
      v_mprops(v_ctr) := 
        mgmt_test_mprop_ver_obj(rec.target_type,
                                rec.type_meta_ver,
                                rec.metric_name,
                                rec.property_name,
                                rec.scope,
                                rec.optional,
                                rec.encrypt);
      v_ctr := v_ctr + 1;
    END LOOP;
  v_result := p_success;
  v_err_desc := NULL;

EXCEPTION
  WHEN OTHERS THEN
    v_mprops := NULL;
    v_result := p_unknown_sql_err;
    v_err_desc := SUBSTR('MGMT_TEST_METADATA_READ.READ_TEST_MPROP_VER ' 
                         || SQLERRM, 1, p_max_err_size);
END READ_TEST_MPROP_VER;

--
-- PROCEDURE GET_AVAIL_METRIC
--
-- Given a test type, read the availability metric
--
PROCEDURE GET_AVAIL_METRIC (
  v_test_type            IN VARCHAR2,
  v_metric_name          OUT VARCHAR2,
  v_metric_column        OUT VARCHAR2)
IS
BEGIN
  IF v_test_type IS NULL THEN
    RETURN;
  END IF;


  SELECT avail_metric, avail_metric_column 
    INTO v_metric_name, v_metric_column
    FROM mgmt_test
   WHERE test_type = v_test_type;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN;
END GET_AVAIL_METRIC;


END MGMT_TEST_METADATA_READ;
/
-- ------------------------------------------------------------------------

SHOW ERRORS;