Rem
Rem $Header: sdk_metric_external_pkgbody.sql 04-jul-2007.02:29:49 hbadheka Exp $
Rem
Rem sdk_metric_external_pkgbody.sql
Rem
Rem Copyright (c) 2006, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdk_metric_external_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    hbadheka    07/04/07 - Backport hbadheka_nlsbug_main from main
Rem    hbadheka    08/01/06 - Backport hbadheka_bug_5050958_3 from main
Rem    mgoswami    02/27/06 - RFI Bug 5061055 
Rem    hbadheka    06/26/07 - Fix for bug 5696616
Rem    bkolloju    02/16/06 - 10.2.0.3
Rem    hbadheka    01/23/06 - change generic metric 
Rem    hbadheka    01/23/06 - change generic metric 
Rem    hbadheka    01/03/06 - APIs for external metrics 
Rem    hbadheka    01/03/06 - APIs for external metrics 
Rem    hbadheka    01/03/06 - Created
Rem

CREATE OR REPLACE PACKAGE BODY mgmt_metric_external
AS
dummy_eval_proc VARCHAR2(40) := 'em_metric_eval.dummy_eval_proc' ;
--
-- Wrapper  around mgmt_metrics to create integrator metrics
--
PROCEDURE create_metric
             (p_producer_id        IN VARCHAR2,
              p_target_type        IN VARCHAR2,
              p_metric_name        IN VARCHAR2,
              p_metric_column_list IN MGMT_METRIC_COLUMN_ARRAY,
              p_category_list      IN MGMT_CATEGORY_ARRAY DEFAULT NULL,
              p_usage_type         IN NUMBER   DEFAULT 0,
              p_metric_label       IN VARCHAR2 DEFAULT NULL,
              p_description        IN VARCHAR2 DEFAULT ' ',
              p_unit               IN VARCHAR2 DEFAULT ' ',
              p_short_name         IN VARCHAR2 DEFAULT NULL
             )
IS
 l_metric mgmt_metrics.metric_name%type ;
BEGIN
  EM_CHECK.CHECK_NOT_NULL(p_producer_id,'p_producer_id') ;
  EM_CHECK.CHECK_NOT_NULL(p_target_type,'p_target_type') ;
  EM_CHECK.CHECK_NOT_NULL(p_metric_name,'p_metric_name') ;

  IF p_metric_column_list IS NULL OR
     p_metric_column_list.COUNT = 0
  THEN
    RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'metric column list cannot be empty') ;
  END IF ;

  SELECT metric_name
    INTO l_metric
    FROM mgmt_metrics
   WHERE target_type = p_target_type
     AND metric_name = p_metric_name 
     AND ROWNUM = 1;

  RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR,
           'Metric already exists') ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  mgmt_metric.create_metric
        ( p_target_type        => p_target_type,
          p_metric_name        => p_metric_name,
          p_metric_type        => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
          p_metric_label       => NVL(p_metric_label,p_metric_name),
          p_description        => NVL(p_description,p_metric_name),
          p_author             => MGMT_GLOBAL.G_AUTHOR_EXTERNAL||p_producer_id,
          p_short_name         => p_short_name,
          p_category_list      => p_category_list,
          p_metric_column_list => p_metric_column_list,
          p_is_repository      => MGMT_GLOBAL.G_TRUE,
          p_source_type        => MGMT_GLOBAL.G_METRIC_SOURCE_PLSQL,
          p_source             => dummy_eval_proc,
          p_non_thresholded_alerts => MGMT_GLOBAL.G_TRUE
         ) ;
END create_metric;
--
-- API to create a GenericAlertExternal Metric 
--
PROCEDURE create_generic_metric
             ( p_target_type        IN VARCHAR2,
               p_type_meta_ver      IN VARCHAR2 DEFAULT '1.0') 
IS
l_metric_columns mgmt_metric_column_array := mgmt_metric_column_array() ;
l_key_column mgmt_metrics.key_column%type ;
l_source mgmt_metrics.source%type ;
l_is_repository mgmt_metrics.is_repository%type ;
BEGIN
  BEGIN
    SELECT DISTINCT source,is_repository,key_column
      INTO l_source,l_is_repository,l_key_column
     FROM mgmt_metrics
     WHERE target_type = p_target_type
      AND metric_name = MGMT_GLOBAL.G_EXTERNAL_METRIC_NAME 
       AND metric_column = ' ' ;

    IF l_is_repository != MGMT_GLOBAL.G_TRUE OR
       l_source != dummy_eval_proc OR
       l_key_column != 'ProducerId;MetricName;MetricColumn'
    THEN
      RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR,
          MGMT_GLOBAL.G_EXTERNAL_METRIC_NAME||
         ' cannot be setup, conflicts with existing metric');
    END IF ;

    RETURN ;

  EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL ;
  WHEN TOO_MANY_ROWS THEN
    RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR,
     ' Found more than one metric definition for '||
        MGMT_GLOBAL.G_EXTERNAL_METRIC_NAME) ;
  END ;

-- Adding Metric Column Label and Column Label NLSID in order to fix bug 5696616
  l_metric_columns.extend(6) ;
  l_metric_columns(1) := mgmt_metric_column_obj.new
                            (p_column_name => 'ProducerId',
                             p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
                             p_column_label => 'ProducerId',
                             p_column_label_nlsid => 'ExternalAlert_ProducerId',
                             p_is_key      => MGMT_GLOBAL.G_TRUE) ;
  l_metric_columns(2) := mgmt_metric_column_obj.new
                            (p_column_name => 'MetricName',
                             p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
                             p_column_label => 'MetricName',
                             p_column_label_nlsid => 'ExternalAlert_MetricName',
                            p_is_key      => MGMT_GLOBAL.G_TRUE) ;
  l_metric_columns(3) := mgmt_metric_column_obj.new
                            (p_column_name => 'MetricColumn',
                             p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
                             p_column_label => 'MetricColumn',
                             p_column_label_nlsid => 'ExternalAlert_MetricColumn',
                             p_is_key      => MGMT_GLOBAL.G_TRUE) ;
  l_metric_columns(4) := mgmt_metric_column_obj.new
                            (p_column_name => 'Key1',
                             p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
                             p_column_label => 'Key1',
                             p_column_label_nlsid => 'ExternalAlert_Key1',
                             p_is_key      => MGMT_GLOBAL.G_TRUE) ;
  l_metric_columns(5) := mgmt_metric_column_obj.new
                            (p_column_name => 'Key2',
                             p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
                             p_column_label => 'Key2',
                             p_column_label_nlsid => 'ExternalAlert_Key2',
                             p_is_key      => MGMT_GLOBAL.G_TRUE) ;
  l_metric_columns(6) := mgmt_metric_column_obj.new
                           (p_column_name => MGMT_GLOBAL.G_EXTERNAL_METRIC_COLUMN,
                             p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING,
                             p_column_label => MGMT_GLOBAL.G_EXTERNAL_METRIC_COLUMN,
                             p_column_label_nlsid => MGMT_GLOBAL.G_EXTERNAL_METRIC_COLUMN,
                             p_is_key      => MGMT_GLOBAL.G_FALSE,
                             p_non_thresholded_alerts => MGMT_GLOBAL.G_TRUE) ;

  mgmt_metric.create_metric
        ( p_target_type        => p_target_type,
          p_type_meta_ver      => p_type_meta_ver,
          p_metric_name        => MGMT_GLOBAL.G_EXTERNAL_METRIC_NAME,
          p_metric_type        => MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
          p_metric_label       => MGMT_GLOBAL.G_EXTERNAL_METRIC_NAME,
--        Adding metric label NLSID to support I18N. Bug 5696616
          p_metric_label_nlsid => MGMT_GLOBAL.G_EXTERNAL_METRIC_NAME,
          p_description        => MGMT_GLOBAL.G_EXTERNAL_METRIC_NAME,
          p_author             => MGMT_GLOBAL.G_AUTHOR_ORACLE,
          p_short_name         => MGMT_GLOBAL.G_EXTERNAL_METRIC_NAME,
--          p_category_list      => p_category_list,
          p_metric_column_list => l_metric_columns,
          p_is_repository      => MGMT_GLOBAL.G_TRUE,
          p_source_type        => MGMT_GLOBAL.G_METRIC_SOURCE_PLSQL,
          p_source             => dummy_eval_proc,
          p_non_thresholded_alerts => MGMT_GLOBAL.G_TRUE
        ) ;
END create_generic_metric ;


--
-- procedure get_target_guid_active_blackouts
-- Returns active blackouts for the given target_guid

PROCEDURE get_tguid_active_blackouts
(
  target_guid_in IN VARCHAR2,
  
  p_lists_out  OUT CURSOR_TYPE
)
IS
  l_target_guid mgmt_targets.target_guid%TYPE;
  l_target_type mgmt_targets.target_type%TYPE;
  l_target_name mgmt_targets.target_name%TYPE;
  
BEGIN

  select TARGET_NAME, TARGET_TYPE into l_target_name, l_target_type 
  from MGMT_TARGETS where TARGET_GUID = HEXTORAW(target_guid_in);
 
  mgmt_blackout_ui.get_target_active_blackouts(l_target_name, l_target_type, p_lists_out);

END get_tguid_active_blackouts;


--
-- PROCEDURE get_target_active_blackout_windows
-- Returns list of start_time, end_time, timezone_offset of all the 
-- active blackout windows of the given target_guid

PROCEDURE get_target_blackout_windows
( 
  target_guid IN VARCHAR2,
  startTimeListOut OUT MGMT_DATE_TABLE,
  endTimeListOut OUT MGMT_DATE_TABLE,
  timeZoneOffsetListOut OUT MGMT_INTEGER_TABLE
)
IS

active_blackouts_cursor CURSOR_TYPE;
l_blackout_guid MGMT_BLACKOUTS.blackout_guid%TYPE;
l_blackout_name MGMT_BLACKOUTS.blackout_name%TYPE;
l_createdby MGMT_BLACKOUTS.created_by%TYPE;
l_createdthru MGMT_BLACKOUTS.created_thru%TYPE;
l_start_time MGMT_BLACKOUT_SCHEDULE.start_time%TYPE;
l_end_time MGMT_BLACKOUT_SCHEDULE.end_time%TYPE;
l_timezone_offset MGMT_BLACKOUT_SCHEDULE.timezone_offset%TYPE;
l_count NUMBER;


BEGIN

get_tguid_active_blackouts(HEXTORAW(target_guid), active_blackouts_cursor);
l_count := 1;

LOOP 
    FETCH active_blackouts_cursor
    INTO  l_blackout_name, l_createdby, l_createdthru;
    EXIT WHEN active_blackouts_cursor%NOTFOUND;
    
    
    select START_TIME, END_TIME, TIMEZONE_OFFSET INTO l_start_time, l_end_time, 
    l_timezone_offset from MGMT_BLACKOUT_SCHEDULE where BLACKOUT_GUID = 
    (select BLACKOUT_GUID from MGMT_BLACKOUTS where 
    BLACKOUT_NAME = l_blackout_name AND CREATED_BY = l_createdby AND 
    CREATED_THRU = l_createdthru);
    
    startTimeListOut(l_count) := l_start_time;
    endTimeListOut(l_count) := l_end_time;
    timeZoneOffsetListOut(l_count) := l_timezone_offset;
    
    DBMS_OUTPUT.PUT_LINE(l_start_time || '|' || l_end_time || '|' || l_timezone_offset);
    
    l_count := l_count + 1;
    
END LOOP;
CLOSE active_blackouts_cursor;

END get_target_blackout_windows;

END mgmt_metric_external;
/

show errors