Rem
Rem $Header: mgmt_host_mgmt_pkgbody.sql 04-sep-2003.10:58:46 ipoddar Exp $
Rem
Rem mgmt_host_mgmt_pkgbody.sql
Rem
Rem Copyright (c) 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      mgmt_host_mgmt_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    ipoddar     09/04/03 - ipoddar_host_mgmt_enhancements 
Rem    ipoddar     08/28/03 - change body 
Rem    ipoddar     08/27/03 - Created
Rem

CREATE OR REPLACE PACKAGE BODY mgmt_host_mgmt AS

PROCEDURE get_collection_item_info(target_name_in IN VARCHAR2,
                                   target_type_in IN VARCHAR2,
                                   collection_name_in IN VARCHAR2,
                                   properties_out OUT cursorType,
                                   metric_thresholds_out OUT cursorType)
IS
BEGIN

  OPEN properties_out FOR
  SELECT met.metric_name, prop.property_name, prop.property_value
  FROM mgmt_metrics met, mgmt_targets tgt, mgmt_collection_properties prop
  WHERE tgt.target_name = target_name_in
  AND tgt.target_type = target_type_in
  AND prop.coll_name = collection_name_in
  AND tgt.target_type = met.target_type
  AND met.metric_guid = prop.metric_guid
  AND tgt.target_guid = prop.target_guid
  AND met.type_meta_ver = tgt.type_meta_ver
  AND (met.category_prop_1 = tgt.category_prop_1 or met.category_prop_1 = ' ')
  AND (met.category_prop_2 = tgt.category_prop_2 or met.category_prop_2 = ' ')
  AND (met.category_prop_3 = tgt.category_prop_3 or met.category_prop_3 = ' ')
  AND (met.category_prop_4 = tgt.category_prop_4 or met.category_prop_4 = ' ')
  AND (met.category_prop_5 = tgt.category_prop_5 or met.category_prop_5 = ' ');

  OPEN metric_thresholds_out FOR
  SELECT met.metric_name, met.metric_column, 
  met.key_column, thr.key_value,
  thr.warning_operator, thr.critical_operator,
  thr.warning_threshold, thr.critical_threshold, thr.fixit_job
  FROM  mgmt_metrics met, mgmt_targets tgt, mgmt_metric_thresholds thr
  WHERE tgt.target_name = target_name_in
  AND tgt.target_type = target_type_in
  AND thr.coll_name = collection_name_in
  AND thr.metric_guid = met.metric_guid
  AND thr.target_guid = tgt.target_guid
  AND tgt.target_type = met.target_type
  AND met.type_meta_ver = tgt.type_meta_ver
  AND (met.category_prop_1 = tgt.category_prop_1 or met.category_prop_1 = ' ')
  AND (met.category_prop_2 = tgt.category_prop_2 or met.category_prop_2 = ' ')
  AND (met.category_prop_3 = tgt.category_prop_3 or met.category_prop_3 = ' ')
  AND (met.category_prop_4 = tgt.category_prop_4 or met.category_prop_4 = ' ')
  AND (met.category_prop_5 = tgt.category_prop_5 or met.category_prop_5 = ' ');
END get_collection_item_info;

PROCEDURE set_collection_item_info(target_name_in IN VARCHAR2,
                                   target_type_in IN VARCHAR2,
                                   collection_name_in IN VARCHAR2,
                                   properties_in IN MGMT_ITEM_PROPERTIES,
                                   metric_thresholds_in IN MGMT_METRIC_THR_OBJ_ARRAY)
IS
  l_target_guid mgmt_targets.target_guid%TYPE;
  l_metric_guid mgmt_metrics.metric_guid%TYPE;
BEGIN
  -- get the target_guid
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);

  DELETE FROM mgmt_collection_properties
  WHERE
  target_guid = l_target_guid AND
  coll_name = collection_name_in;
  
  DELETE FROM mgmt_metric_thresholds
  WHERE
  target_guid = l_target_guid AND
  coll_name = collection_name_in;
     
  FOR j in 1..properties_in.count LOOP
    l_metric_guid := mgmt_target.get_metric_guid(
            target_type_in, 
            properties_in(j).metric_name);
 
    INSERT INTO mgmt_collection_properties(
    target_guid, metric_guid, coll_name, property_name, property_value)
    VALUES (l_target_guid, l_metric_guid, collection_name_in,
            properties_in(j).property_name, 
            properties_in(j).property_value);
  END LOOP;

  FOR j in 1..metric_thresholds_in.count LOOP
    l_metric_guid := mgmt_target.get_metric_guid(
            target_type_in, 
            metric_thresholds_in(j).metric_name, 
            metric_thresholds_in(j).metric_column);

    INSERT INTO mgmt_metric_thresholds(
         target_guid, 
         metric_guid, 
         coll_name, 
         key_value,
         warning_operator, 
         warning_threshold, 
         critical_operator, 
         critical_threshold,
         fixit_job)
    VALUES (
         l_target_guid, 
         l_metric_guid, 
         collection_name_in,
         metric_thresholds_in(j).key_value,
         metric_thresholds_in(j).warning_operator,
         metric_thresholds_in(j).warning_threshold,
         metric_thresholds_in(j).critical_operator,
         metric_thresholds_in(j).critical_threshold,
         metric_thresholds_in(j).fixit_job);
  END LOOP;
END set_collection_item_info;


end mgmt_host_mgmt;
/

show errors;