Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\rca\rca

Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\rca\rca_pkgbody.sql

Rem drv: <create type="pkgbodys" pos="emx/emx_pkgbodys.sql+"/>
Rem
Rem $Header: rca_pkgbody.sql 03-sep-2007.03:18:44 denath Exp $
Rem
Rem basic_rca_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      basic_rca_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    denath      07/19/07 - Fix 6219000.added error handling in peek_message
Rem                           for RCA messages not existing in AQ but in
Rem                           mgmt_notify_queue.
Rem    denath      09/01/07 - Backport denath_bug-6219000 from main
Rem    jriel       08/25/05 - fix NLS column label problem in comp tests 
Rem    jriel       07/29/05 - fix query for status in alert details 
Rem    scgrover    07/07/05 - add extended sql trace 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    jriel       06/27/05 - add is test avail func 
Rem    jriel       06/14/05 - fix severity lookup 
Rem    jriel       06/13/05 - add usage type 
Rem    jriel       06/08/05 - fix que task 
Rem    jriel       06/02/05 - add delete task 
Rem    jriel       05/26/05 - add outage duration 
Rem    jriel       05/11/05 - fix metrics table ref 
Rem    jriel       05/03/05 - no key is not null 
Rem    jriel       03/21/05 - fix svc affect 
Rem    chyu        03/18/05 - removing the echo off 
Rem    jriel       03/08/05 - check for updates 
Rem    jriel       03/02/05 - add procedures for datagen 
Rem    jriel       01/10/05 - make columns consistent 
Rem    jriel       01/10/05 - fix select metrics 
Rem    jriel       01/04/05 - UI feedback 
Rem    jriel       12/02/04 - add svc impacted 
Rem    jriel       11/22/04 - add types for results 
Rem    jriel       11/10/04 - add peek methods 
Rem    jriel       11/03/04 - fix notif content 
Rem    pmaddi      10/18/04 - Adding procedure to get the services impacted. 
Rem    pmaddi      10/15/04 - 
Rem    pmaddi      10/08/04 - 
Rem    jriel       10/13/04 - add set interactive proc 
Rem    pmaddi      09/28/04 - Adding comments for the functions. 
Rem    jriel       10/06/04 - fill in remove callbacks 
Rem    jriel       09/27/04 - 
Rem    pmaddi      09/26/04 - Adding functionality to check for the target 
Rem                           availabilty. 
Rem    pmaddi      09/25/04 - Checking if the target is a service type. 
Rem    pmaddi      09/23/04 - Modifying queries to complete rca functionality. 
Rem    pmaddi      08/26/04 - 
Rem    jriel       08/30/04 - add cleanup procedures 
Rem    pmaddi      08/19/04 - pmaddi_rca_metrix
Rem    pmaddi      07/29/04 - Adding more functions to rca. 
Rem    pmaddi      07/28/04 - Created
Rem

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB ON
SET PAGESIZE 100
CREATE OR REPLACE PACKAGE BODY mgmt_rca AS

PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN) IS
BEGIN  
  MGMT_SQLTRACE.EXTENDED_SQL_TRACE_ON(EST_RCA_NAME, p_value);
END DBMSJOB_EXTENDED_SQL_TRACE_ON;

PROCEDURE purge_rca_results IS
  CURSOR violation_ids IS
    SELECT  violation_guid
    FROM    mgmt_violations v, mgmt_rca_summary s
    WHERE   s.severity_guid = v.violation_guid (+)
    AND     v.violation_guid IS NOT NULL;
i NUMBER(1);
BEGIN
  MGMT_SQLTRACE.EXTENDED_SQL_TRACE(EST_RCA_NAME);
  -- 1. remove RCA results for severities that no longer exist
  -- this happens in this job because we can not create
  -- a constraint between RCA data and the mgmt_violations
  -- table and there is no callback mechanism for purge/delete
  -- of severities
  
  FOR record IN violation_ids
  LOOP
    i := 0;
    --delete_rca_for_severity(record.violation_guid, 1);
  END LOOP;  
  
  -- 2. remove RCA updates beyond the Nth, where N is a config
  -- parameter (e.g. remove oldest updates beyond the 20th).
  --
  --  TO BE IMPLEMENTED IN A LATER RELEASE

END purge_rca_results;

PROCEDURE store_rca_trace(event_guid_in   IN RAW,
      update_id_in  IN NUMBER,
      trace_doc_in  IN VARCHAR2) IS
BEGIN
  BEGIN
    INSERT INTO mgmt_rca_trace
      (event_guid, update_id, trace_doc) VALUES (event_guid_in, update_id_in, trace_doc_in);
  END;
END store_rca_trace;

-- PURPOSE:
--   This procedure returns all rca details information
--
-- IN Parameters:
-- 	severity_guid_in: RAW  severity guid
--	target_type_in: VARCHAR2 Target type
-- OUT Parameters:
--  is_service_type_out: NUMBER Flag to tell if the target is a service
--  is_status_metric_out: NUMBER Flag to indicate if the severity corresponds to Response/Status metric
--	rca_dep_alert_details_out: CURSOR  cursor containing services impacted by the failure.
--  rca_causes_alert_details_out: CURSOR  cursor containing list of root causes for the failure of this service.
PROCEDURE get_alert_details(severity_guid_in                    IN RAW,
                        target_type_in							            IN VARCHAR2,
                        root_cause_only_in                      IN NUMBER,
                        is_service_type_out                     OUT NUMBER,
                        is_status_metric_out					          OUT NUMBER,
                        rca_dep_alert_details_out               OUT cursorType,
                        rca_causes_alert_details_out            OUT cursorType) IS

BEGIN		
		is_service_type_out:=mgmt_service.implements_service_interface(target_type_in);

select count(*) into is_status_metric_out
		from mgmt_violations v, mgmt_metrics m, mgmt_targets t
		where v.violation_guid = severity_guid_in
		and m.metric_guid = v.policy_guid
		and m.metric_name = mgmt_global.G_AVAIL_METRIC_NAME
		and m.metric_column = mgmt_global.G_AVAIL_METRIC_COLUMN
		and t.target_guid = v.target_guid
		and m.target_type=t.target_type
		and t.type_meta_ver = m.type_meta_ver 
		and (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') 
		and (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') 
		and (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') 
		and (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') 
		and (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ');
		
		IF(is_service_type_out = SERVICE_TYPE AND is_status_metric_out>0) THEN
			get_failure_causes(severity_guid_in, -1, rca_causes_alert_details_out);			
		ELSE
			OPEN rca_causes_alert_details_out FOR
				select * from  dual where rownum<1;
		END IF;

get_affected_services_bysev(severity_guid_in, root_cause_only_in, rca_dep_alert_details_out);

END get_alert_details;

/*
 * Get the RCA metric details info. This procedure returns all the relevant
 * information for a given target name, target type, metric name, metric column
 * and key value. The values returned as OUT parameters are used for display in
 * the Metric Details pages.
 */

PROCEDURE get_affected_services_bymetric(target_name_in                 	IN  VARCHAR2,
                             target_type_in                 IN  VARCHAR2,
                             metric_name_in                 IN  VARCHAR2,
                             metric_column_in               IN  VARCHAR2,
                             metric_key_value_in	          IN  VARCHAR2,
                             has_priv_out                   OUT NUMBER,
                             page_type_in                   IN  VARCHAR2,
                             root_cause_only_in             IN  NUMBER,
                             start_date_in                  IN  DATE,
                             end_date_in                    IN  DATE,
                             rca_metric_test_results_out    OUT cursorType) IS

l_days NUMBER(2);
 l_target_guid RAW(16);
 l_metric_guid mgmt_metrics.metric_guid%TYPE;
 l_metric_column_type VARCHAR2(16);
 l_end_time DATE;
 l_timezone_region mgmt_targets.timezone_region%TYPE;
 l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
 l_category_prop_1 mgmt_targets.category_prop_1%TYPE;
 l_category_prop_2 mgmt_targets.category_prop_2%TYPE;
 l_category_prop_3 mgmt_targets.category_prop_3%TYPE;
 l_category_prop_4 mgmt_targets.category_prop_4%TYPE;
 l_category_prop_5 mgmt_targets.category_prop_5%TYPE;
 l_count NUMBER(4);
 l_cause_type NUMBER(1);
 BEGIN
  dbms_output.put_line('get_rca_metrics(): IN');

-- First, determine the target_guid from the table on which the security
  -- policy is defined, mgmt_targets. If no data is found, we exit immediately
  -- since there is no need to continue (but this should never happen):

BEGIN
    SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2,
           category_prop_3, category_prop_4, category_prop_5, timezone_region
      INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
           l_category_prop_3, l_category_prop_4, l_category_prop_5, l_timezone_region
      FROM mgmt_targets
     WHERE target_name = target_name_in
       AND target_type = target_type_in;

EXCEPTION
     WHEN NO_DATA_FOUND
        THEN return;

END;

-- Then, get metric guid and metric column type
  BEGIN
    
	-- note: the "rownum = 1" is to ensure only one row is returned. This may
    -- not return the correct metric row if EMD allow empty and non-empty
    -- values of category_prop_* columns to exist at the same time. The problem
    -- will also occur during migration.
    SELECT metric_guid
      INTO l_metric_guid
      FROM mgmt_metrics
     WHERE target_type = target_type_in
       AND metric_name = metric_name_in
       AND metric_column = metric_column_in
       AND type_meta_ver = l_type_meta_ver
       AND (category_prop_1 = l_category_prop_1 OR category_prop_1 = ' ')
       AND (category_prop_2 = l_category_prop_2 OR category_prop_2 = ' ')
       AND (category_prop_3 = l_category_prop_3 OR category_prop_3 = ' ')
       AND (category_prop_4 = l_category_prop_4 OR category_prop_4 = ' ')
       AND (category_prop_5 = l_category_prop_5 OR category_prop_5 = ' ')
       AND rownum = 1;

EXCEPTION
      WHEN NO_DATA_FOUND
        THEN raise MGMT_GLOBAL.no_such_metric_found;
  END;

-- check if the user has at least OPERATOR_TARGET privilege
  has_priv_out := mgmt_user.has_priv(
    mgmt_user.get_current_em_user(), mgmt_user.OPERATOR_TARGET, l_target_guid);

-- Determine the value for the 'days' parameter depending on the page type; needed
  -- for the cursor returning severity data.

/*
  IF (page_type_in = 'byDay')
  THEN
      l_days := 1;
  ELSIF (page_type_in = 'byWeek')
  THEN
      l_days := 7;
  ELSE
      l_days := 31;
  END IF;
  l_end_time := mgmt_global.SYSDATE_TZRGN(l_timezone_region) -l_days;
  */
  
  l_cause_type := ROOT_CAUSE_TYPE;  -- show root cause only
  IF root_cause_only_in = 0 THEN
    l_cause_type := 0;  -- any causes
  END IF;

-- rca_metric_test_results_out cursor contains the list of services affected
  -- by this metric; this should be factored into the get_svcs_aff_count_by*
  -- 
  BEGIN
		IF((metric_name_in = mgmt_global.G_AVAIL_METRIC_NAME)
				AND ( metric_column_in = mgmt_global.G_AVAIL_METRIC_COLUMN)) THEN
			open rca_metric_test_results_out for
			WITH common as (
      select mre1.source_guid, mre1.source_type, mt.target_name, mt.target_type, mt.timezone_region, mrs.rca_status, mrea.is_root_cause,
        ms.collection_timestamp, decode(ms.violation_duration, null, -1, ms.violation_duration*60) as violation_duration
      from mgmt_rca_event mre, mgmt_rca_event mre1,mgmt_rca_event_assoc mrea, mgmt_rca_summary mrs, mgmt_violations ms, 
          mgmt_targets mt
			where mre.target_guid = l_target_guid
			and mre.test_guid = l_metric_guid
			and mrea.cause_event_guid = mre.event_guid
			and mrea.is_root_cause >= l_cause_type
			and mrea.update_id=mrs.last_update_id			
			and mre1.event_guid=mrea.event_guid
			and mrs.event_guid = mre1.event_guid
			and ms.violation_guid = mrs.severity_guid
			and mt.target_guid = ms.target_guid)
					SELECT sev.message as message,sev.collection_timestamp as timestamp, sev.message_nlsid as msgNlsid,
							  sev.message_params as msgParams,common.target_name as targetName,
							common.target_type as targetType, common.source_guid as severityGuid, common.rca_status as rcaStatus, 
              common.is_root_cause as isRootCause, common.violation_duration as violation_duration
					from mgmt_severity sev, common
					where sev.severity_guid = common.source_guid 
					and common.source_type = STATUS_SEVERITY
					-- and sev.collection_timestamp > (mgmt_global.SYSDATE_TZRGN(common.timezone_region)-l_days)
					and sev.collection_timestamp >= mgmt_global.adjust_tz(start_date_in, l_timezone_region, common.timezone_region) -- start_date_in
          and sev.collection_timestamp <= mgmt_global.adjust_tz(end_date_in, l_timezone_region, common.timezone_region) -- end_date_in          
					union all
					select mrtr.message as message,mrtr.collection_timestamp as timestamp,mrtr.message_nlsid as msgNlsid,
							  mrtr.message_params as msgParams,common.target_name as targetname,
							common.target_type as targetType,common.source_guid as severityGuid, common.rca_status as rcaStatus, 
              common.is_root_cause as isRootCause, common.violation_duration as violation_duration
					from mgmt_rca_test_result mrtr,common
					where common.source_type = STATUS_TEST
					and mrtr.result_guid = common.source_guid
					--and mrtr.collection_timestamp > (mgmt_global.SYSDATE_TZRGN(common.timezone_region)-l_days);
					and mrtr.collection_timestamp >= mgmt_global.adjust_tz(start_date_in, l_timezone_region, common.timezone_region) -- start_date_in
          and mrtr.collection_timestamp <= mgmt_global.adjust_tz(end_date_in, l_timezone_region, common.timezone_region); -- end_date_in            
		ELSE
			open rca_metric_test_results_out for
			WITH common as (
      select mre1.source_guid, mre1.source_type, mt.target_name, mt.target_type, mt.timezone_region, mrs.rca_status, mrea.is_root_cause,
        ms.collection_timestamp, decode(ms.violation_duration, null, -1, ms.violation_duration*60) as violation_duration
			from mgmt_rca_event mre, mgmt_rca_event mre1, mgmt_rca_event_assoc mrea, mgmt_rca_summary mrs, mgmt_violations ms,
        mgmt_rca_metric_test mrmt,mgmt_targets mt
			where mre.target_guid = l_target_guid
      and mrmt.target_guid = l_target_guid
			and mrmt.metric_guid=l_metric_guid
			and mrmt.key_value=metric_key_value_in
			and mre.test_guid = mrmt.metric_test_guid
			and mrea.cause_event_guid = mre.event_guid
			and mre1.event_guid=mrea.event_guid
			and mrea.is_root_cause >= l_cause_type
			and mrea.update_id=mrs.last_update_id
			and mrs.event_guid = mre1.event_guid
			and ms.violation_guid = mrs.severity_guid
			and mt.target_guid = ms.target_guid)
					SELECT sev.message as message, sev.collection_timestamp as timestamp,sev.message_nlsid as msgNlsid,
							  sev.message_params as msgParams, common.target_name as targetName,
							common.target_type as targetType, common.source_guid as severityGuid, common.rca_status as rcaStatus, 
              common.is_root_cause as isRootCause, common.violation_duration as violation_duration
					from mgmt_severity sev, common
					where sev.severity_guid = common.source_guid 
					and common.source_type = STATUS_SEVERITY
					--and sev.collection_timestamp > (mgmt_global.SYSDATE_TZRGN(common.timezone_region)-l_days)
					and sev.collection_timestamp >= mgmt_global.adjust_tz(start_date_in, l_timezone_region, common.timezone_region) -- start_date_in
          and sev.collection_timestamp <= mgmt_global.adjust_tz(end_date_in, l_timezone_region, common.timezone_region) -- end_date_in          
          union all
					select mrtr.message as message,mrtr.collection_timestamp as timestamp,mrtr.message_nlsid as msgNlsid,
							  mrtr.message_params as msgParams,common.target_name as targetName,
							common.target_type as targetType,common.source_guid as severityGuid, common.rca_status as rcaStatus, 
              common.is_root_cause as isRootCause, common.violation_duration as violation_duration
					from mgmt_rca_test_result mrtr,common
					where common.source_type = STATUS_TEST
					and mrtr.result_guid = common.source_guid
					--and mrtr.collection_timestamp > (mgmt_global.SYSDATE_TZRGN(common.timezone_region)-l_days);
					and mrtr.collection_timestamp >= mgmt_global.adjust_tz(start_date_in, l_timezone_region, common.timezone_region) -- start_date_in
          and mrtr.collection_timestamp <= mgmt_global.adjust_tz(end_date_in, l_timezone_region, common.timezone_region); -- end_date_in                 
		END IF;
      EXCEPTION
       WHEN NO_DATA_FOUND
          THEN
              dbms_output.put_line('No data found for rca.');

END;
 END get_affected_services_bymetric;

--
-- PURPOSE:
--   This procedure returns all the information to be displayed on the availabilty page
--
-- IN Parameters:
--  target_name_in: VARCHAR2 Target name
--  target_type_in: VARCHAR2 Target type
--
-- OUT Parameters:
--	 is_service_down: NUMBER is service down flag
--   is_service_type_out: NUMBER  is service type flag
--	 rca_status_out: NUMBER Flag indicating the rca operation status(If rca has been run fo this severity).
--	 rca_no_of_causes_out: NUMBER is No of root causes identified
--	 rca_no_of_serv_affected_out: NUMBER No of services impacted
--
PROCEDURE get_availability(target_name_in   IN  VARCHAR2,
							target_type_in                IN  VARCHAR2,							
              root_cause_only_in            IN  NUMBER,
              severity_guid_out             OUT VARCHAR2,
              is_service_down					      OUT NUMBER,
							is_service_type_out           OUT NUMBER,
							rca_status_out 					      OUT NUMBER,
							rca_no_of_causes_out			    OUT NUMBER,
							rca_no_of_serv_affected_out		OUT NUMBER) IS
l_severity_guid RAW(16) := null;
BEGIN
	get_service_availability(target_name_in,target_type_in,is_service_down);
	if(is_service_down=0)then
		is_service_type_out:=mgmt_service.implements_service_interface(target_type_in);
		
        select get_severity(target_name_in,target_type_in) into l_severity_guid from dual;
        
		if (l_severity_guid is not null) then
            select rawtohex(l_severity_guid) into severity_guid_out from dual;
			select count(*) into rca_status_out
			from mgmt_rca_summary
			where
			severity_guid=l_severity_guid;
			
			if(is_service_type_out = SERVICE_TYPE) then
				select cause_count into rca_no_of_causes_out
				from mgmt_rca_run r, mgmt_rca_summary s
				where s.severity_guid = l_severity_guid
				and r.event_guid = s.event_guid
				and r.update_id = s.last_update_id;
			else
				rca_no_of_causes_out:=-1;
			end if;	
			
      get_svcs_aff_count_bysev(l_severity_guid, root_cause_only_in, rca_no_of_serv_affected_out);			
				
		else
      select '' into severity_guid_out from dual;
			rca_status_out:=-1;
			rca_no_of_causes_out:=-1;
			rca_no_of_serv_affected_out:=-1;
		end if;
	end if;
END get_availability;

--
-- PURPOSE:
--gets the list of root causes identified as the possible reasons for the failed service.
--
-- IN Parameters:
--   severity_guid_in: RAW  severity guid
--
-- OUT Parameters:
--   rca_causeoffailure_cur_out: CURSOR  cursor containing list of root causes.
--
PROCEDURE get_failure_causes(severity_guid_in                			IN RAW,						
							 update_id_in							 	IN  NUMBER,
							 rca_causeoffailure_cur_out              	OUT cursorType) IS
BEGIN
/*
	OPEN rca_causeoffailure_cur_out FOR
	with common as (select target_name, target_type, mre.source_type,mre.source_guid
	from mgmt_rca_summary mrs, mgmt_rca_event_assoc mrea, mgmt_rca_event mre,
	mgmt_rca_event mre1, mgmt_targets mt
	where mrs.severity_guid = severity_guid_in
	and mre1.event_guid = mrs.event_guid
	and mre1.event_guid = mrea.event_guid
	and mre.event_guid = mrea.cause_event_guid
	and mrea.update_id = mrs.last_update_id
	and mrea.is_root_cause = ROOT_CAUSE_TYPE
	and mt.target_guid = mre.target_guid
	)
	select res.message as message,cm.target_name as targetName,cm.target_type as targetType,res.collection_timestamp as timestamp,
		   res.message_nlsid as msgNlsid, res.message_params as msgParams
	from
	mgmt_rca_test_result res,common cm
	where
	(cm.source_type = STATUS_TEST or cm.source_type = METRIC_TEST)
	and res.result_guid = cm.source_guid
	union all
	select sev.message as message,cm.target_name as targetName,cm.target_type as targetType,sev.collection_timestamp as timestamp,
		   sev.message_nlsid as msgNlsid, sev.message_params as msgParams
	from mgmt_severity sev,common cm
	where
	(cm.source_type = STATUS_SEVERITY or cm.source_type = METRIC_SEVERITY)
	and sev.severity_guid = cm.source_guid;
*/

/*
SELECT res.message message, cm.target_name targetname, 
       cm.target_type targettype, res.collection_timestamp timestamp, 
       res.message_nlsid msgnlsid, res.message_params msgparams, 
       cm.event_action eventaction, cm.source_type sourcetype,
       HEXTORAW(NULL) severityguid, tes.key_value keyvalue,
       met.metric_name metname, met.metric_column metcolumn
    FROM mgmt_rca_test_result res, mgmt_metrics met, mgmt_targets t, mgmt_rca_metric_test tes,
           (SELECT mt.target_name, mt.target_type, mt.target_guid, 
                   mre.source_type, mre.source_guid, mre.event_action
              FROM mgmt_rca_summary mrs, mgmt_rca_event_assoc mrea, 
                   mgmt_rca_event mre, mgmt_rca_event mre1, mgmt_targets mt
              WHERE mrs.severity_guid = severity_guid_in
                AND mre1.event_guid = mrs.event_guid
                AND mre1.event_guid = mrea.event_guid
                AND mre.event_guid = mrea.cause_event_guid
                AND ((update_id_in=-1 and mrea.update_id = mrs.last_update_id)
                OR  (update_id_in<>-1 and mrea.update_id = update_id_in))
                AND mrea.is_root_cause = ROOT_CAUSE_TYPE
                AND mt.target_guid = mre.target_guid) cm
    WHERE (cm.source_type = STATUS_TEST
      OR  cm.source_type = METRIC_TEST)
      AND res.result_guid = cm.source_guid
      AND ((cm.source_type = METRIC_TEST and res.metric_test_guid = tes.metric_test_guid) OR
           (cm.source_Type = STATUS_TEST and res.metric_test_guid = met.metric_guid))
      AND t.target_guid = cm.target_guid
      AND met.metric_guid = tes.metric_guid
      AND met.type_meta_ver = t.type_meta_ver
      AND (met.category_prop_1 = t.category_prop_1 OR met.category_prop_1 = ' ')
      AND (met.category_prop_2 = t.category_prop_2 OR met.category_prop_2 = ' ')
      AND (met.category_prop_3 = t.category_prop_3 OR met.category_prop_3 = ' ')
      AND (met.category_prop_4 = t.category_prop_4 OR met.category_prop_4 = ' ')
      AND (met.category_prop_5 = t.category_prop_5 OR met.category_prop_5 = ' ')    
UNION ALL
SELECT sev.message message, cm.target_name targetname, 
       cm.target_type targettype, sev.collection_timestamp timestamp, 
       sev.message_nlsid msgnlsid, sev.message_params msgparams, 
       cm.event_action eventaction, cm.source_type sourcetype,
       sev.severity_guid severityguid, sev.key_value keyvalue,
       met.metric_name metname, met.metric_column metcolumn
    FROM mgmt_severity sev, mgmt_metrics met, mgmt_targets t,
           (SELECT mt.target_name, mt.target_type, mt.target_guid, 
                   mre.source_type, mre.source_guid, mre.event_action
              FROM mgmt_rca_summary mrs, mgmt_rca_event_assoc mrea, 
                   mgmt_rca_event mre, mgmt_rca_event mre1, mgmt_targets mt
              WHERE mrs.severity_guid = severity_guid_in
                AND mre1.event_guid = mrs.event_guid
                AND mre1.event_guid = mrea.event_guid
                AND mre.event_guid = mrea.cause_event_guid
                AND ((update_id_in=-1 and mrea.update_id = mrs.last_update_id)
					      OR (update_id_in<>-1 and mrea.update_id = update_id_in))
                AND mrea.is_root_cause = ROOT_CAUSE_TYPE
                AND mt.target_guid = mre.target_guid) cm
    WHERE (cm.source_type = STATUS_SEVERITY
      OR  cm.source_type = METRIC_SEVERITY)
      AND sev.severity_guid = cm.source_guid
      AND t.target_guid = cm.target_guid
      AND met.metric_guid = sev.metric_guid
      AND met.type_meta_ver = t.type_meta_ver
      AND (met.category_prop_1 = t.category_prop_1 OR met.category_prop_1 = ' ')
      AND (met.category_prop_2 = t.category_prop_2 OR met.category_prop_2 = ' ')
      AND (met.category_prop_3 = t.category_prop_3 OR met.category_prop_3 = ' ')
      AND (met.category_prop_4 = t.category_prop_4 OR met.category_prop_4 = ' ')
      AND (met.category_prop_5 = t.category_prop_5 OR met.category_prop_5 = ' ');
*/
/*
OPEN rca_causeoffailure_cur_out FOR 
      SELECT  t.target_name targetname, t.target_type targettype, 
              e.source_type sourcetype, e.event_action eventaction,
              r.result_guid resultguid, tes.key_value rkeyvalue, r.collection_timestamp rtimestamp, 
              r.message rmessage,  r.message_nlsid rmsgnlsid, r.message_params rmsgparams,
              v.violation_guid severityguid, v.key_value vkeyvalue, v.collection_timestamp vtimestamp, 
              v.message vmessage, v.message_nlsid vmsgnlsid, v.message_params vmsgparams,
              met.metric_name metname, met.metric_column metcolumn
      FROM mgmt_rca_event e, mgmt_rca_event_assoc a,  
               mgmt_rca_summary s, mgmt_rca_test_result r, mgmt_rca_metric_test tes,
               mgmt_violations v, mgmt_targets t, mgmt_metrics met
      WHERE s.severity_guid = severity_guid_in
      AND   a.event_guid = s.event_guid 
      AND   a.is_root_cause = ROOT_CAUSE_TYPE
      AND   ((update_id_in=-1 and a.update_id = s.last_update_id) OR (update_id_in<>-1 and a.update_id = update_id_in))   
      AND   e.event_guid = a.cause_event_guid
      AND   e.source_guid = r.result_guid (+)
      AND   e.source_guid = v.violation_guid (+)
      AND   t.target_guid = e.target_guid 
      AND   r.metric_test_guid = tes.metric_test_guid (+)
      AND   (
            (e.source_type = METRIC_TEST AND met.metric_guid = tes.metric_guid )
         OR
            (e.source_type = STATUS_TEST AND met.metric_guid = r.metric_test_guid)
         OR
            ((e.source_type = STATUS_SEVERITY OR e.source_type = METRIC_SEVERITY) AND met.metric_guid = v.policy_guid)
            )
      AND   met.type_meta_ver = t.type_meta_ver
      AND   (met.category_prop_1 = t.category_prop_1 OR met.category_prop_1 = ' ')
      AND   (met.category_prop_2 = t.category_prop_2 OR met.category_prop_2 = ' ')
      AND   (met.category_prop_3 = t.category_prop_3 OR met.category_prop_3 = ' ')
      AND   (met.category_prop_4 = t.category_prop_4 OR met.category_prop_4 = ' ')
      AND   (met.category_prop_5 = t.category_prop_5 OR met.category_prop_5 = ' ');
*/
OPEN rca_causeoffailure_cur_out FOR 
      SELECT  t.target_name targetname, t.target_type targettype, 
              e.source_type sourcetype, e.event_action eventaction,
              r.result_guid resultguid, tes.key_value rkeyvalue, r.collection_timestamp rtimestamp, 
              r.message rmessage,  r.message_nlsid rmsgnlsid, r.message_params rmsgparams,
              v.violation_guid severityguid, v.key_value vkeyvalue, v.collection_timestamp vtimestamp, 
              v.message vmessage, v.message_nlsid vmsgnlsid, v.message_params vmsgparams,
              met.metric_name metname, met.metric_column metcolumn
      FROM mgmt_rca_event e, mgmt_rca_test_result r, mgmt_rca_metric_test tes,
           mgmt_violations v, mgmt_targets t, mgmt_metrics met,
           (
            SELECT DISTINCT e.event_guid
            FROM mgmt_rca_event e, mgmt_rca_event_assoc a,  mgmt_rca_summary s
            WHERE s.severity_guid = severity_guid_in
            AND   a.event_guid = s.event_guid 
            AND   a.is_root_cause = ROOT_CAUSE_TYPE
            AND   ((update_id_in=-1 and a.update_id = s.last_update_id) OR (update_id_in<>-1 and a.update_id = update_id_in))  
            AND   e.event_guid = a.cause_event_guid
           ) x
      WHERE e.event_guid = x.event_guid
      AND   e.source_guid = r.result_guid (+)
      AND   e.source_guid = v.violation_guid (+)
      AND   t.target_guid = e.target_guid 
      AND   r.metric_test_guid = tes.metric_test_guid (+)
      AND  ( (e.source_type = METRIC_TEST AND met.metric_guid = tes.metric_guid)
         OR  (e.source_type = STATUS_TEST and met.metric_guid = r.metric_test_guid)
         OR  (( e.source_type = STATUS_SEVERITY or e.source_type = METRIC_SEVERITY) and met.metric_guid = v.policy_guid))
      AND   met.target_type = t.target_type
      AND   met.type_meta_ver = t.type_meta_ver
      AND   (met.category_prop_1 = t.category_prop_1 OR met.category_prop_1 = ' ')
      AND   (met.category_prop_2 = t.category_prop_2 OR met.category_prop_2 = ' ')
      AND   (met.category_prop_3 = t.category_prop_3 OR met.category_prop_3 = ' ')
      AND   (met.category_prop_4 = t.category_prop_4 OR met.category_prop_4 = ' ')
      AND   (met.category_prop_5 = t.category_prop_5 OR met.category_prop_5 = ' ');
END get_failure_causes;

--
-- PURPOSE:
--   gets the list of services affected by a specific severity
--
-- IN Parameters:
--   severity_guid_in: RAW  severity guid
--
-- OUT Parameters:
--   rca_affected_services_cur_out: CURSOR  cursor containing list of services affected by this services' failure.
--
PROCEDURE get_affected_services_bysev(severity_guid_in                IN RAW,     
                      root_cause_only_in                              IN NUMBER,
                      rca_affected_services_cur_out                   OUT cursorType) IS
l_cause_type NUMBER(1);
BEGIN

l_cause_type := ROOT_CAUSE_TYPE;
  IF root_cause_only_in = 0 THEN
    l_cause_type := 0;
  END IF;

OPEN rca_affected_services_cur_out FOR
		SELECT rs.severity_guid as severityGuid, rs.rca_status as rcaStatus, s.message as message, t.target_name as targetName, t.target_type as targetType,
           decode(s.violation_duration, null, -1, s.violation_duration*60) as violation_duration, s.collection_timestamp as timestamp, s.message_nlsid as msgNlsid, s.message_params as msgParams, x.is_root_cause as isRootCause
    FROM (
          SELECT DISTINCT(rs.severity_guid) AS svc_sev_guid, a.is_root_cause AS is_root_cause
          FROM   mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, mgmt_rca_summary rs
          WHERE  e.source_guid = severity_guid_in
          AND    a.cause_event_guid = e.event_guid
          AND    a.is_root_cause >= l_cause_type
          AND    rr.event_guid = a.event_guid
          AND    rr.update_id = a.update_id
          AND    rs.event_guid = rr.event_guid
        ) x, mgmt_violations s, mgmt_targets t, mgmt_rca_summary rs     
    WHERE s.violation_guid = x.svc_sev_guid       
    AND   t.target_guid = s.target_guid       
    AND   rs.severity_guid = x.svc_sev_guid;    
		 EXCEPTION
             WHEN NO_DATA_FOUND
                THEN
				OPEN rca_affected_services_cur_out FOR
				select * from dual where rownum <1;
        
END get_affected_services_bysev;

--
-- PURPOSE:
--  gets the list of services impacted by a specified target
--
-- IN Parameters:
--   target_name_in: target name
--   target_type_in: target type
--   num_of_days_in: NUMBER number of days for for which the data has to searched
--
-- OUT Parameters:
--   rca_affected_services_cur_out: CURSOR  cursor containing list of services affected by this services' failure.
--
PROCEDURE get_affected_services_byname(target_name_in     IN VARCHAR2,
                                       target_type_in     IN VARCHAR2,
                                       num_days_in        IN NUMBER,
                                       root_cause_only_in IN NUMBER,
                     rca_affected_services_cur_out        OUT cursorType) IS
l_cause_type NUMBER(1);
BEGIN
  
  l_cause_type := ROOT_CAUSE_TYPE;
  IF root_cause_only_in = 0 THEN
    l_cause_type := 0;
  END IF;
  
  IF num_days_in > 0 THEN
    BEGIN
    OPEN rca_affected_services_cur_out FOR
      SELECT rs.severity_guid as severityGuid, rs.rca_status as rcaStatus, s.message as message, 
           t.target_name as targetName, t.target_type as targetType,decode(s.violation_duration, null, -1, s.violation_duration*60) as violation_duration,
           s.collection_timestamp as timestamp, s.message_nlsid as msgNlsid, s.message_params as msgParams, x.is_root_cause AS isRootCause
			    FROM (SELECT DISTINCT rs.severity_guid AS svc_sev_guid, a.is_root_cause AS is_root_cause
			              FROM mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, 
			                   mgmt_rca_summary rs, mgmt_targets t, mgmt_violations s 
			              WHERE t.target_name = target_name_in
			                AND t.target_type = target_type_in
			                AND e.target_guid = t.target_guid 
			                AND s.collection_timestamp > (mgmt_global.sysdate_tzrgn(t.timezone_region) - num_days_in)
                      AND s.violation_guid = rs.severity_guid
			                AND a.cause_event_guid = e.event_guid 
			                AND a.is_root_cause >= l_cause_type 
			                AND rr.event_guid = a.event_guid 
			                AND rr.update_id = a.update_id 
			                AND rs.event_guid = rr.event_guid) x, mgmt_violations s,  mgmt_targets t, mgmt_rca_summary rs 
			    WHERE s.violation_guid = x.svc_sev_guid 
			      AND t.target_guid = s.target_guid 
            AND rs.severity_guid = x.svc_sev_guid;        
		 EXCEPTION
             WHEN NO_DATA_FOUND
                THEN
				OPEN rca_affected_services_cur_out FOR
				select * from dual where rownum <1;
    END; 
  ELSE
    -- num_days = 0; only looking for services that are currently down
    BEGIN    
    OPEN rca_affected_services_cur_out FOR
      SELECT rs.severity_guid as severityGuid, rs.rca_status as rcaStatus, s.message as message, 
           t.target_name as targetName, t.target_type as targetType, decode(s.violation_duration, null, -1, s.violation_duration*60) as violation_duration,
           s.collection_timestamp as timestamp, s.message_nlsid as msgNlsid, s.message_params as msgParams, 
           x.is_root_cause AS isRootCause
			    FROM (SELECT DISTINCT rs.severity_guid svc_sev_guid, a.is_root_cause AS is_root_cause
             FROM  mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, 
                   mgmt_rca_summary rs, mgmt_targets t, mgmt_current_violation s
             WHERE t.target_name = target_name_in
               AND t.target_type = target_type_in
               AND e.target_guid = t.target_guid 
               AND a.cause_event_guid = e.event_guid 
               AND a.is_root_cause >= l_cause_type
               AND s.violation_guid = rs.severity_guid
               AND rr.event_guid = a.event_guid 
               AND rr.update_id = a.update_id 
               AND rs.event_guid = rr.event_guid
               AND rs.rca_status = RCA_STATUS_OPEN
               AND rr.update_id = rs.last_update_id) x, mgmt_violations s, mgmt_targets t, mgmt_rca_summary rs 
			    WHERE s.violation_guid = x.svc_sev_guid 
			      AND t.target_guid = s.target_guid 
            AND rs.severity_guid = x.svc_sev_guid;
		 EXCEPTION
             WHEN NO_DATA_FOUND
                THEN
				OPEN rca_affected_services_cur_out FOR
				select * from dual where rownum <1;      
    END;
  END IF;
            
END get_affected_services_byname;

--
-- PURPOSE:
--Checks if the service is running or down .
--
-- IN Parameters:
--  target_name_in: VARCHAR2 Target name
--  target_type_in: VARCHAR2 Target type
--
-- OUT Parameters:
--   current_status_out: NUMBER Flag indicating the Current status indicating the availabilty of the service.
--
PROCEDURE get_service_availability(target_name_in           IN  VARCHAR2,
    				               	target_type_in          IN  VARCHAR2,
									current_status_out		OUT NUMBER) IS
l_target_guid mgmt_targets.target_guid%TYPE;
BEGIN
    l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in );
	current_status_out := mgmt_target.get_avail_current_status( l_target_guid );
END get_service_availability;

--
-- PURPOSE:
--Check the permissions of the user for the service and the target
--
-- IN Parameters:
--  service_name_in: VARCHAR2 Service name
--  service_type_in: VARCHAR2 Service type
--  target_name_in: VARCHAR2 Target name
--  target_type_in: VARCHAR2 Target type
--
-- OUT Parameters:
--   service_priv_out: NUMBER  numeric value inidcation if the user has the privileges to operate on the service.
--   target_priv_out: NUMBER  numeric value inidcation if the user has the privileges to operate on the target.
--
PROCEDURE check_target_privielges(service_name_in		IN  VARCHAR2,
								service_type_in			IN  VARCHAR2,
								target_name_in			IN  VARCHAR2,
								target_type_in			IN  VARCHAR2,
								service_priv_out		OUT	NUMBER,
								target_priv_out			OUT	NUMBER) IS
l_target_guid mgmt_targets.target_guid%TYPE;
l_service_guid mgmt_targets.target_guid%TYPE;								
BEGIN
	BEGIN
		select target_guid into l_service_guid
		from mgmt_targets where
		target_name=service_name_in
		and target_type=service_type_in;
		EXCEPTION
		  WHEN NO_DATA_FOUND
		  THEN
		   raise MGMT_GLOBAL.target_does_not_exist;
	end;
	BEGIN
		SELECT target_guid
		  INTO l_target_guid
		  FROM mgmt_targets
		 WHERE target_name = target_name_in
		   AND target_type = target_type_in;
	
		EXCEPTION
		  WHEN NO_DATA_FOUND
		  THEN
		   raise MGMT_GLOBAL.target_does_not_exist;
	END;
	
	target_priv_out := mgmt_user.has_priv(mgmt_user.get_current_em_user(), mgmt_user.OPERATOR_TARGET, l_service_guid);
	
	target_priv_out := mgmt_user.has_priv(	
    mgmt_user.get_current_em_user(), mgmt_user.OPERATOR_TARGET, l_target_guid);
END;

--
-- PURPOSE:
--gets the list of existing fault tests defined for the service.
--
-- IN Parameters:
--  service_name_in: VARCHAR2 Service name
--  service_type_in: VARCHAR2 Service type
--  target_name_in: VARCHAR2 Target name
--  target_type_in: VARCHAR2 Target type
--
-- OUT Parameters:
--   fault_tests_out: CURSOR  cursor containing list of fault tests defined for the service.
--
PROCEDURE get_fault_tests(service_name_in			IN  VARCHAR2,
							service_type_in			IN  VARCHAR2,
							target_name_in			IN  VARCHAR2,
							target_type_in			IN  VARCHAR2,
              test_scope_in       IN  NUMBER,
							fault_tests_cur_out		OUT cursorType,
              status_metric_cur_out OUT cursorType,
              comp_host_test_cur_out OUT cursorType) IS
l_target_guid mgmt_targets.target_guid%TYPE;
l_service_guid mgmt_targets.target_guid%TYPE;
l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
l_category_prop_1 mgmt_targets.category_prop_1%TYPE;
l_category_prop_2 mgmt_targets.category_prop_2%TYPE;
l_category_prop_3 mgmt_targets.category_prop_3%TYPE;
l_category_prop_4 mgmt_targets.category_prop_4%TYPE;
l_category_prop_5 mgmt_targets.category_prop_5%TYPE;
cate_prop	mgmt_category_prop_array;
l_show_host_tests NUMBER(1);
l_host_name VARCHAR2(256);
l_scope_guid RAW(16);
l_target_type VARCHAR2(64);
l_host_test_scope_guid RAW(16);
l_component_guid mgmt_targets.target_guid%TYPE;
BEGIN
	BEGIN
		SELECT  target_guid into l_service_guid
		FROM    mgmt_targets 
    WHERE   target_name=service_name_in
		AND     target_type=service_type_in;
		EXCEPTION
		  WHEN NO_DATA_FOUND
		  THEN
		   raise_application_error(-20001,'99999No service found for target name '||service_name_in||' and type '||service_type_in);--MGMT_GLOBAL.target_does_not_exist;		  
	END;
	
  BEGIN
    IF test_scope_in = SCOPE_SERVICE THEN
      BEGIN 
        l_target_type := target_type_in;
        
        SELECT target_guid, target_guid, type_meta_ver, category_prop_1, category_prop_2,
                category_prop_3, category_prop_4, category_prop_5, host_name
          INTO l_target_guid, l_component_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
             l_category_prop_3, l_category_prop_4, l_category_prop_5, l_host_name
          FROM mgmt_targets
         WHERE target_name = target_name_in
           AND target_type = target_type_in;
      
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise_application_error(-20001,'99999No target found for target name '||target_name_in||' and type '||target_type_in);
      END;
    ELSE
      BEGIN
        l_target_type := 'host';
        
        SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2,
                h.category_prop_3, h.category_prop_4, h.category_prop_5, h.target_name, t.target_guid
          INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
             l_category_prop_3, l_category_prop_4, l_category_prop_5, l_host_name, l_component_guid
          FROM mgmt_targets h, mgmt_targets t
         WHERE t.target_name = target_name_in
           AND t.target_type = target_type_in
           AND h.target_name = t.host_name
           AND h.target_type = 'host';
      
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise_application_error(-20001,'99999No target found for target name '||target_name_in||' and type '||target_type_in);        
      END;
    END IF;
	END;

-- get the scope guid = GUID("svc;comp")  
  l_scope_guid := get_scope_guid(l_service_guid, l_component_guid, test_scope_in);
  
	SELECT mgmt_category_prop_array(l_target_type,l_type_meta_ver,l_category_prop_1,l_category_prop_2,l_category_prop_3,l_category_prop_4,l_category_prop_5)
	INTO cate_prop FROM DUAL;
  
  BEGIN
	OPEN fault_tests_cur_out for		
			SELECT metric_name "metricName",metric_test_guid "MetricTestId",mm.metric_column "Metric",
            decode(threshold_source,1,rca_threshold, get_threshold(l_target_guid, metric_name,
						metric_column, key_value,cate_prop)) "Threshold", mrmt.operator "Operator",
            decode(num_keys,1,mrmt.key_value,0,'',
            concat_keys(num_keys,key_part1_value,key_part2_value,key_part3_value,key_part4_value,key_part5_value)) "Object",
            mm.column_label "collabel", mm.column_label_nlsid "colnlsid",mrmt.metric_guid "metricGuid",
            mm.is_transposed "transposed"
			FROM  mgmt_rca_metric_test mrmt, mgmt_metrics mm			
			WHERE mrmt.target_guid=l_target_guid			
			AND   mrmt.metric_guid=mm.metric_guid
			AND   scope_guid=l_scope_guid
			AND   scope=test_scope_in
      AND   mm.target_type = l_target_type
			AND   mm.type_meta_ver = l_type_meta_ver
			AND   (mm.category_prop_1 = l_category_prop_1 OR mm.category_prop_1 = ' ')
			AND   (mm.category_prop_2 = l_category_prop_2 OR mm.category_prop_2 = ' ')
			AND   (mm.category_prop_3 = l_category_prop_3 OR mm.category_prop_3 = ' ')
			AND   (mm.category_prop_4 = l_category_prop_4 OR mm.category_prop_4 = ' ')
			AND   (mm.category_prop_5 = l_category_prop_5 OR mm.category_prop_5 = ' ');
		EXCEPTION
		WHEN NO_DATA_FOUND THEN
				OPEN fault_tests_cur_out FOR
          select * from dual where rownum <1;	
  END;
        
  -- get the status metric
  BEGIN
  OPEN status_metric_cur_out FOR
    SELECT metric_name as "metricName", 'STATUS' as "MetricTestId", mm.metric_column as "Metric",
      'STATUS' as "Threshold", 'STATUS' as "Operator", 'STATUS' as "Object",
			mm.column_label as "Description", mm.column_label_nlsid as "Description_NlsId", 'STATUS' as "metricGuid"
		FROM  mgmt_metrics mm			
    WHERE mm.metric_name = mgmt_global.G_AVAIL_METRIC_NAME
    AND   mm.metric_column = mgmt_global.G_AVAIL_METRIC_COLUMN
    AND   mm.target_type = l_target_type	
    AND   mm.type_meta_ver = l_type_meta_ver
    AND   (mm.category_prop_1 = l_category_prop_1 OR mm.category_prop_1 = ' ')
    AND   (mm.category_prop_2 = l_category_prop_2 OR mm.category_prop_2 = ' ')
    AND   (mm.category_prop_3 = l_category_prop_3 OR mm.category_prop_3 = ' ')
    AND   (mm.category_prop_4 = l_category_prop_4 OR mm.category_prop_4 = ' ')
    AND   (mm.category_prop_5 = l_category_prop_5 OR mm.category_prop_5 = ' ');  
    EXCEPTION 
		WHEN NO_DATA_FOUND
                THEN
				OPEN status_metric_cur_out FOR
				select * from dual where rownum <1;	    
    END;
  
    l_show_host_tests := 1;
    IF test_scope_in <> SCOPE_SERVICE OR target_type_in = 'host' THEN
      l_show_host_tests := 0;
    END IF;
    
    IF l_show_host_tests = 0 THEN
      OPEN comp_host_test_cur_out FOR
        SELECT l_show_host_tests AS "showCompHostTest", l_host_name AS "compHostName", 0 AS "compHostTestCount" FROM DUAL;
    ELSE  
      l_host_test_scope_guid := get_scope_guid(l_service_guid, l_component_guid, SCOPE_COMPONENT);
      
      -- get the component host test info
      BEGIN
             
      OPEN comp_host_test_cur_out FOR 
        SELECT  l_show_host_tests AS "showCompHostTest",
                l_host_name AS "compHostName",
                x.testcount AS "compHostTestCount"
        FROM    dual,
          (SELECT  count(*) as testcount 
           FROM    mgmt_rca_metric_test 
           WHERE   scope = SCOPE_COMPONENT
           AND     scope_guid = l_host_test_scope_guid) x;
      EXCEPTION 
      WHEN NO_DATA_FOUND THEN
        OPEN comp_host_test_cur_out FOR
          SELECT l_show_host_tests AS "showCompHostTest", l_host_name AS "compHostName", 0 AS "compHostTestCount" FROM DUAL;
      END;          
    END IF;
    
END get_fault_tests;

--
-- PURPOSE:
--gets the list of metric types defined for the target.
--
-- IN Parameters:
--  target_name_in: VARCHAR2 Target name
--  target_type_in: VARCHAR2 Target type
--
-- OUT Parameters:
--   metric_types_cur_out: CURSOR  cursor containing list of metric types defined for the target.
PROCEDURE get_metric_types(target_name_in			IN  VARCHAR2,
							target_type_in			IN  VARCHAR2,
							metric_types_cur_out	OUT cursorType) IS
BEGIN
	OPEN metric_types_cur_out FOR
	select distinct metric_type "metricType"
	from 
	mgmt_metrics mm,
	mgmt_targets mt
	where 	
	mt.target_name=target_name_in
	and mt.target_type=target_type_in
	and mm.target_type=mt.target_type
	and mm.type_meta_ver=mt.type_meta_ver
	and (mm.category_prop_1=mt.category_prop_1 or mm.category_prop_1 = ' ')
	and (mm.category_prop_2=mt.category_prop_2 or mm.category_prop_2 = ' ')
	and (mm.category_prop_3=mt.category_prop_3 or mm.category_prop_3 = ' ')
	and (mm.category_prop_4=mt.category_prop_4 or mm.category_prop_4 = ' ')
	and (mm.category_prop_5=mt.category_prop_5 or mm.category_prop_5 = ' ')
	and metric_type in (0,1);--to be modified later
END get_metric_types;

--
-- PURPOSE:
--gets the list of metrics defined for the target.
--
-- IN Parameters:
--  target_name_in: VARCHAR2 Target name
--  target_type_in: VARCHAR2 Target type
--  metric_type_in: NUMBER Metric Type
--
-- OUT Parameters:
--   metric_types_cur_out: CURSOR  cursor containing list of metrics defined for the target.
PROCEDURE get_metrics_for_type(target_name_in			IN  VARCHAR2,
							target_type_in				IN  VARCHAR2,
							metric_type_in				IN	NUMBER,
              test_scope_in         IN NUMBER,
							metrics_cur_out				OUT cursorType,
              host_name_out         OUT VARCHAR2) IS
l_target_guid mgmt_targets.target_guid%TYPE;
l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
l_category_prop_1 mgmt_targets.category_prop_1%TYPE;
l_category_prop_2 mgmt_targets.category_prop_2%TYPE;
l_category_prop_3 mgmt_targets.category_prop_3%TYPE;
l_category_prop_4 mgmt_targets.category_prop_4%TYPE;
l_category_prop_5 mgmt_targets.category_prop_5%TYPE;	
l_target_type mgmt_targets.target_type%TYPE;
l_target_name mgmt_targets.target_name%TYPE;
l_target_info_cur cursorType;
BEGIN
	BEGIN
    IF test_scope_in = SCOPE_SERVICE THEN
      BEGIN    
        SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2,
             category_prop_3, category_prop_4, category_prop_5, host_name
          INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
             l_category_prop_3, l_category_prop_4, l_category_prop_5, host_name_out
          FROM mgmt_targets
         WHERE target_name = target_name_in
           AND target_type = target_type_in;
      
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise_application_error(-20001,'No target found for target name '||target_name_in||' and type '||target_type_in);      
      END;
    ELSE
      BEGIN        
        SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2,
             h.category_prop_3, h.category_prop_4, h.category_prop_5, h.target_name
          INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
             l_category_prop_3, l_category_prop_4, l_category_prop_5, host_name_out
          FROM mgmt_targets t, mgmt_targets h
         WHERE t.target_name = target_name_in
           AND t.target_type = target_type_in
           AND h.target_name = t.host_name
           AND h.target_type = 'host';
      
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise_application_error(-20001,'No target found for target name '||target_name_in||' and type '||target_type_in);  
      END;
    END IF;
    
    IF test_scope_in = SCOPE_SERVICE THEN
      l_target_type := target_type_in;
      l_target_name := target_name_in;    
    ELSE        
      l_target_type := 'host';           
      l_target_name := host_name_out;    
    END IF;

END;
  
  get_all_metrics(l_target_name, l_target_type, metrics_cur_out);
  
END get_metrics_for_type;

PROCEDURE get_all_metrics(target_name_in IN VARCHAR2,
                          target_type_in IN VARCHAR2,
                          metrics_cur_out OUT cursorType) IS

l_target_guid mgmt_targets.target_guid%TYPE;
  l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
  l_category_prop_1 mgmt_targets.category_prop_1%TYPE;
  l_category_prop_2 mgmt_targets.category_prop_2%TYPE;
  l_category_prop_3 mgmt_targets.category_prop_3%TYPE;
  l_category_prop_4 mgmt_targets.category_prop_4%TYPE;
  l_category_prop_5 mgmt_targets.category_prop_5%TYPE;

BEGIN

BEGIN
    SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2,
           category_prop_3, category_prop_4, category_prop_5
      INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
           l_category_prop_3, l_category_prop_4, l_category_prop_5
      FROM mgmt_targets
     WHERE target_name = target_name_in
       AND target_type = target_type_in;

EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        raise MGMT_GLOBAL.target_does_not_exist;
  END;

OPEN metrics_cur_out for
    -- note: This may not return the correct metric rows if EMD allow empty and
    -- non-empty values of category_prop_* columns to exist at the same time.
    -- The problem will also occur during migration.
    SELECT /*+ INDEX(met) */
           'NOT_TRANSPOSED' AS trans_key, met.metric_name, met.metric_column, met.key_column,
           met.metric_label, met.column_label, met.metric_type, met.is_transposed,
           thr.warning_threshold, thr.critical_threshold,
           err.collection_timestamp AS err_timestamp,
           cur.collection_timestamp AS collection_timestamp,
           DECODE(met.metric_type, 0, 0, 1, 0, 8, 0, met.metric_type) AS metric_sort_type,
           met.metric_label_nlsid, met.column_label_nlsid, 
           met.metric_guid, met.key_order, met.usage_type
      FROM mgmt_metrics met,
           (SELECT metric_guid, warning_threshold, critical_threshold
              FROM mgmt_metric_thresholds
             WHERE target_guid = l_target_guid
               AND key_value = ' ') thr,
           (SELECT metric_guid, MAX(collection_timestamp) AS collection_timestamp
              FROM mgmt_current_metric_errors
             WHERE target_guid = l_target_guid
             GROUP BY metric_guid) err,
           (SELECT metric_guid, MAX(collection_timestamp) AS collection_timestamp
              FROM mgmt_current_metrics
             WHERE target_guid = l_target_guid
             GROUP BY metric_guid) cur
     WHERE met.target_type = target_type_in
       AND met.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT
       AND met.remote = 0
       AND met.is_transposed = 0
       AND (met.metric_column = ' ' OR met.key_order = 0)
       AND met.metric_name NOT IN
           (SELECT /*+ INDEX(m) */ m.metric_name
              FROM mgmt_metrics m
             WHERE m.target_type = target_type_in
               AND m.metric_type = MGMT_GLOBAL.G_METRIC_TYPE_RAW
               AND m.type_meta_ver = l_type_meta_ver
               AND (m.category_prop_1 = l_category_prop_1 OR m.category_prop_1 = ' ')
               AND (m.category_prop_2 = l_category_prop_2 OR m.category_prop_2 = ' ')
               AND (m.category_prop_3 = l_category_prop_3 OR m.category_prop_3 = ' ')
               AND (m.category_prop_4 = l_category_prop_4 OR m.category_prop_4 = ' ')
               AND (m.category_prop_5 = l_category_prop_5 OR m.category_prop_5 = ' ') )
       AND met.type_meta_ver = l_type_meta_ver
       AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ')
       AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ')
       AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ')
       AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ')
       AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ')
       AND met.metric_guid = thr.metric_guid (+)
       AND met.metric_guid = err.metric_guid (+)
       AND met.metric_guid = cur.metric_guid (+)
  UNION ALL
    SELECT thr.key_value AS trans_key, met.metric_name, met.metric_column, met.key_column,
           met.metric_label, met.column_label, met.metric_type, met.is_transposed,
           thr.warning_threshold, thr.critical_threshold,
           sysdate AS err_timestamp,
           cur.collection_timestamp AS collection_timestamp,
           DECODE(met.metric_type, 0, 0, 1, 0, 8, 0, met.metric_type) AS metric_sort_type,
           met.metric_label_nlsid, met.column_label_nlsid, 
           met.metric_guid, met.key_order, met.usage_type
    FROM mgmt_metrics met, mgmt_targets t,
           (SELECT key_value,target_guid,metric_guid, warning_threshold, critical_threshold
              FROM mgmt_metric_thresholds
             WHERE target_guid = l_target_guid) thr,
           (SELECT key_value,metric_guid, MAX(collection_timestamp) AS collection_timestamp
              FROM mgmt_current_metrics
             WHERE target_guid = l_target_guid
             GROUP BY key_value,metric_guid) cur
    WHERE met.is_transposed = 1
      AND t.target_guid = l_target_guid         
      AND (met.metric_column = ' ' OR met.key_order = 0)
			AND met.type_meta_ver = t.type_meta_ver
			AND (met.category_prop_1 = t.category_prop_1 OR met.category_prop_1 = ' ')
			AND (met.category_prop_2 = t.category_prop_2 OR met.category_prop_2 = ' ')
			AND (met.category_prop_3 = t.category_prop_3 OR met.category_prop_3 = ' ')
			AND (met.category_prop_4 = t.category_prop_4 OR met.category_prop_4 = ' ')
			AND (met.category_prop_5 = t.category_prop_5 OR met.category_prop_5 = ' ')
			AND thr.target_guid = t.target_guid
			AND thr.metric_guid = met.metric_guid
      AND thr.metric_guid = cur.metric_guid (+)
      AND thr.key_value = cur.key_value (+)
     ORDER BY trans_key, metric_label, metric_sort_type DESC, column_label; 
		EXCEPTION 
				WHEN NO_DATA_FOUND
				THEN 
				OPEN metrics_cur_out FOR
          SELECT * FROM dual WHERE ROWNUM<1;     
          
END get_all_metrics;

--
-- PURPOSE:
--gets the metric details for the metric.
--
-- IN Parameters:
--  target_name_in: VARCHAR2 Target name
--  target_type_in: VARCHAR2 Target type
--  metric_name_in: VARCHAR2 Metric name
-- metric_column_in: VARCHAR2 Metric column corresponding to the fault test
-- metric_guid_in: RAW metric guid
--
-- OUT Parameters:
--   operator_out: operator type for the fault test
--   metric_det_cur_out: CURSOR  cursor containing details of the metric. The keys,column_labels and column_label_nlsids.
PROCEDURE get_metric_detail(target_name_in              IN  VARCHAR2,
                            target_type_in              IN  VARCHAR2,
                            metric_name_in              IN  VARCHAR2,
                            metric_column_in			      IN  VARCHAR2,
                            metric_guid_in				      IN	RAW,
                            test_scope_in               IN  NUMBER,
                            trans_key_in                IN  VARCHAR2,
                            operator_out		 		        OUT NUMBER,							
                            metric_detail_cur_out       OUT cursorType,
                            host_name_out               OUT VARCHAR2,
                            no_key_threshold_out        OUT VARCHAR2,
                            metric_info_cur_out         OUT cursorType) IS
l_target_guid mgmt_targets.target_guid%TYPE;
l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
l_category_prop_1 mgmt_targets.category_prop_1%TYPE;
l_category_prop_2 mgmt_targets.category_prop_2%TYPE;
l_category_prop_3 mgmt_targets.category_prop_3%TYPE;
l_category_prop_4 mgmt_targets.category_prop_4%TYPE;
l_category_prop_5 mgmt_targets.category_prop_5%TYPE;
l_target_type VARCHAR2(64);
l_has_keys NUMBER(1);
BEGIN
	operator_out:=0;	
	
	BEGIN
    IF test_scope_in = SCOPE_SERVICE THEN
      BEGIN
        l_target_type := target_type_in;
        
        SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2,
             category_prop_3, category_prop_4, category_prop_5, host_name
          INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
             l_category_prop_3, l_category_prop_4, l_category_prop_5, host_name_out
          FROM mgmt_targets
         WHERE target_name = target_name_in
           AND target_type = target_type_in;
      
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise_application_error(-20001,'No target found for target name '||target_name_in||' and type '||target_type_in);      
      END;
    ELSE
      BEGIN
        l_target_type := 'host';
        
        SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2,
             h.category_prop_3, h.category_prop_4, h.category_prop_5, h.target_name
          INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
             l_category_prop_3, l_category_prop_4, l_category_prop_5, host_name_out
          FROM mgmt_targets t, mgmt_targets h
         WHERE t.target_name = target_name_in
           AND t.target_type = target_type_in
           AND h.target_name = t.host_name
           AND h.target_type = 'host';
      
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise_application_error(-20001,'No target found for target name '||target_name_in||' and type '||target_type_in);  
      END;
    END IF;
	END;
	
	OPEN metric_detail_cur_out for 
    SELECT  metric_column, column_label "Description", column_label_nlsid "Description_NlsId",mm.metric_guid "metricGuid",mm.is_transposed "transposed"
    FROM    mgmt_metrics mm	
    WHERE   mm.metric_name=metric_name_in	
    AND     mm.target_type=l_target_type	
    AND     mm.type_meta_ver = l_type_meta_ver
    AND     (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ')
    AND     (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ')
    AND     (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ')
    AND     (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ')
    AND     (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' ')
    AND     key_order>0
    ORDER BY key_order ASC;

OPEN metric_info_cur_out for 
    SELECT  metric_label "metricLabel", metric_label_nlsid "metricNlsId", column_label "columnLabel", column_label_nlsid "columnNlsId"
    FROM    mgmt_metrics mm	
    WHERE   mm.metric_name=metric_name_in	
    AND     mm.metric_column=metric_column_in
    AND     mm.target_type=l_target_type	
    AND     mm.type_meta_ver = l_type_meta_ver
    AND     (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ')
    AND     (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ')
    AND     (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ')
    AND     (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ')
    AND     (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' ');

BEGIN
    IF trans_key_in IS NULL THEN
     BEGIN
      SELECT warning_operator, critical_threshold INTO operator_out, no_key_threshold_out
        FROM  mgmt_metric_thresholds mmt, mgmt_metrics mm
        WHERE mmt.target_guid = l_target_guid
        AND mm.metric_name=metric_name_in
        AND mm.metric_column=metric_column_in
        AND mm.target_type=l_target_type	
        AND mm.type_meta_ver = l_type_meta_ver
        AND (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ')
        AND (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ')
        AND (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ')
        AND (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ')
        AND (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' ')	
        AND mmt.metric_guid=mm.metric_guid	
        AND rownum=1;
      EXCEPTION 
      WHEN NO_DATA_FOUND
      THEN 
        operator_out:=8;
      WHEN OTHERS
      THEN raise_application_error(-20001,'Error when getting metric detail for the target '||target_name_in||'. '|| SQLERRM);	
     END;
    ELSE
     BEGIN
      SELECT warning_operator, critical_threshold INTO operator_out, no_key_threshold_out
        FROM  mgmt_metric_thresholds mmt, mgmt_metrics mm
        WHERE mmt.target_guid = l_target_guid
        AND mm.metric_name=metric_name_in
        AND mm.metric_column=metric_column_in
        AND mm.target_type=l_target_type	
        AND mm.type_meta_ver = l_type_meta_ver
        AND (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ')
        AND (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ')
        AND (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ')
        AND (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ')
        AND (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' ')	
        AND mmt.metric_guid=mm.metric_guid	
        AND mmt.key_value = trans_key_in
        AND rownum=1;
      EXCEPTION 
      WHEN NO_DATA_FOUND
      THEN 
        operator_out:=8;
      WHEN OTHERS
      THEN raise_application_error(-20001,'Error when getting metric detail for the target '||target_name_in||'. '|| SQLERRM);	    
     END;
    END IF;
  END;

END get_metric_detail;

--
-- PURPOSE:
--Gets the metric details for a particular fault test.
--
-- IN Parameters:
--  target_name_in: VARCHAR2 Target name
--  target_type_in: VARCHAR2 Target type
--  metric_test_guid_in: VARCHAR2 Metric test guid
--	 metric_name_in: VARCHAR2 Metric name
-- OUT parameters
--  num_keys_out: NUMBER Number of keys defined for the metric column 
--  metric_info_out: cursorType Cursor containing the metric information like metric column,threshold ,operator etc
--  metric_detail_cur_out: cursorType Cursor containing the list of keys and their correspong values
--
PROCEDURE get_metric_detail_for_edit(target_name_in             IN  VARCHAR2,
									target_type_in              IN  VARCHAR2,
									metric_test_guid_in			    IN  VARCHAR2,
									metric_name_in              IN  VARCHAR2,
                  test_scope_in               IN  NUMBER,
									num_keys_out 				        OUT NUMBER,
									metric_info_out				      OUT cursorType,
									metric_detail_cur_out       OUT cursorType,
                  host_name_out               OUT VARCHAR2) IS
l_metric_column mgmt_metrics.metric_column%TYPE;									
l_column_label mgmt_metrics.column_label%TYPE;
l_column_label_nlsid mgmt_metrics.column_label_nlsid%TYPE;
l_metric_label mgmt_metrics.metric_label%TYPE;
l_metric_label_nlsid mgmt_metrics.metric_label_nlsid%TYPE;
l_threshold mgmt_rca_metric_test.rca_threshold%TYPE;
l_keys_out varchar2(2000);
l_criticalthreshold mgmt_metric_thresholds.critical_threshold%TYPE;
l_operator mgmt_metric_thresholds.warning_operator%TYPE;
l_target_guid mgmt_targets.target_guid%TYPE;
l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
l_category_prop_1 mgmt_targets.category_prop_1%TYPE;
l_category_prop_2 mgmt_targets.category_prop_2%TYPE;
l_category_prop_3 mgmt_targets.category_prop_3%TYPE;
l_category_prop_4 mgmt_targets.category_prop_4%TYPE;
l_category_prop_5 mgmt_targets.category_prop_5%TYPE;
l_threshold_src mgmt_rca_metric_test.threshold_source%TYPE;
l_transposed NUMBER(1);
l_target_type VARCHAR2(64);
BEGIN
	l_threshold:='';
	l_threshold_src :=1;
	BEGIN
    IF test_scope_in = SCOPE_SERVICE THEN
      BEGIN
        l_target_type := target_type_in;
        
        SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2,
             category_prop_3, category_prop_4, category_prop_5, host_name
          INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
             l_category_prop_3, l_category_prop_4, l_category_prop_5, host_name_out
          FROM mgmt_targets
         WHERE target_name = target_name_in
           AND target_type = target_type_in;	
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise_application_error(-20001,'No target found for target name '||target_name_in||' and type '||target_type_in);
      END;
    ELSE 
      BEGIN
        l_target_type := 'host';
        
        SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2,
             h.category_prop_3, h.category_prop_4, h.category_prop_5, h.target_name
          INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
             l_category_prop_3, l_category_prop_4, l_category_prop_5, host_name_out
          FROM mgmt_targets h, mgmt_targets m
         WHERE m.target_name = target_name_in
           AND m.target_type = target_type_in	
           AND h.target_name = m.host_name
           AND h.target_type = 'host';
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise_application_error(-20001,'No target found for target name '||target_name_in||' and type '||target_type_in);      
      END;
    END IF;
	END;
  
	begin
		select num_keys, threshold_source, metric_column, column_label, column_label_nlsid, metric_label, metric_label_nlsid,
				decode(threshold_source,0,null,1,rca_threshold), 
        decode(num_keys,0,null,1,key_value,concat_keys(num_keys,mrmt.key_part1_value,mrmt.key_part2_value,mrmt.key_part3_value,mrmt.key_part4_value,mrmt.key_part5_value)),
        mm.is_transposed "transposed", mrmt.operator
		into num_keys_out, l_threshold_src, l_metric_column, l_column_label, l_column_label_nlsid, l_metric_label, l_metric_label_nlsid,
        l_threshold, l_keys_out, l_transposed, l_operator
		from mgmt_rca_metric_test mrmt, mgmt_metrics mm
		where mrmt.metric_test_guid=metric_test_guid_in
		and mm.target_type=l_target_type
		and mm.type_meta_ver = l_type_meta_ver
		and (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ')
		and (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ')
		and (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ')
		and (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ')
		and (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' ')
		and mrmt.target_guid=l_target_guid
		and mm.metric_guid=mrmt.metric_guid	
		AND rownum=1;
		EXCEPTION
		  WHEN NO_DATA_FOUND
		  THEN 
			num_keys_out:=0;
			l_threshold_src:=1;
			l_column_label:='Data not available';
	end;
	
	begin
      select     critical_threshold
      into       l_criticalthreshold
      from      mgmt_metric_thresholds mmt, mgmt_rca_metric_test mrmt
      where     mrmt.metric_test_guid = metric_test_guid_in
      and       mmt.target_guid = mrmt.target_guid
      and       mmt.metric_guid = mrmt.metric_guid	
      and       (mmt.key_value = mrmt.key_value or mmt.key_value='' or mmt.key_value=' ');
 		EXCEPTION
		WHEN NO_DATA_FOUND
		THEN l_criticalthreshold:='';
	end;
	
	if(l_criticalthreshold='' or l_criticalthreshold=' ')then 
		l_criticalthreshold:=null;
	end if;
  
	open metric_info_out for
    select l_metric_column "metricColumn",l_keys_out "keyValue",l_threshold "threshold",l_criticalthreshold "criticalThreshold",l_threshold_src "thresholdSrc", 
      l_operator "operator", l_column_label "Description",l_column_label_nlsid "Description_NlsId", l_metric_label "metricLabel", l_metric_label_nlsid "metricNlsId", 
      l_threshold "threshold",l_operator "operator", l_transposed "transposed"
      from dual;
	
	if(num_keys_out=0)then		
		OPEN metric_detail_cur_out for 
			select * from dual where rownum<1;	
	else
		OPEN metric_detail_cur_out for 
		select metric_column, column_label "columnLabel", column_label_nlsid "columnLabelNlsId"
		from mgmt_rca_metric_test mrmt, mgmt_metrics mm
		where mrmt.metric_test_guid=metric_test_guid_in		
		and mrmt.target_guid=l_target_guid
		and mm.target_type=l_target_type
		and mm.metric_name=metric_name_in
		AND mm.type_meta_ver = l_type_meta_ver
		AND (mm.category_prop_1 = l_category_prop_1 OR mm.category_prop_1 = ' ')
		AND (mm.category_prop_2 = l_category_prop_2 OR mm.category_prop_2 = ' ')
		AND (mm.category_prop_3 = l_category_prop_3 OR mm.category_prop_3 = ' ')
		AND (mm.category_prop_4 = l_category_prop_4 OR mm.category_prop_4 = ' ')
		AND (mm.category_prop_5 = l_category_prop_5 OR mm.category_prop_5 = ' ')
		and key_order>0
		order by key_order asc;
	end if;
END get_metric_detail_for_edit;

--
-- PURPOSE:
--Creates or updates the rca fault test.
--
-- IN Parameters:
--  service_guid_in: RAW Service guid
--  target_guid_in: RAW Target guid
--  metric_test_guid_in: RAW Metric test guid
--  metric_guid_in: RAW Metric guid
--  metric_name_in: VARCHAR2 Metric name for which the test is defined
--  metric_column_in: VARCHAR2 Metric column for which the test is defined
--  threshold_in: Threshold value for the fault test
--  operator_in:Operator for comparison in the rca fault test
--  use_test_threshold_in: NUMBER Flag for indicating if used defined threshold intead of predefined needs to be used.
--	 edit_type_in: NUMBER Operation type(Create or update)
--  metric_keys_array_in: mgmt_medium_string_array Array of key values to be stored.
-- OUT parameters
--  status_out: NUMBER The operation staus (Success or failed state) 
--
PROCEDURE create_fault_test(service_name_in			IN  VARCHAR2,
          service_type_in         IN  VARCHAR2,
					target_name_in				  IN  VARCHAR2,
          target_type_in          IN  VARCHAR2,
					metric_guid_in			  	IN  RAW,
					metric_name_in				  IN	VARCHAR2,
					metric_column_in			  IN	VARCHAR2,
					threshold_in				    IN	VARCHAR2,
					operator_in					    IN	NUMBER,
					use_test_threshold_in		IN 	NUMBER,					
					edit_type_in				    IN	NUMBER,
					metric_keys_array_in		IN	mgmt_medium_string_array,
          test_scope_in           IN  NUMBER,
					status_out					    OUT	NUMBER) IS 
l_threshold	varchar2(256);
l_operator mgmt_rca_metric_test.operator%TYPE;
l_target_type mgmt_targets.target_type%TYPE;
l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
l_category_prop_1 mgmt_targets.category_prop_1%TYPE;
l_category_prop_2 mgmt_targets.category_prop_2%TYPE;
l_category_prop_3 mgmt_targets.category_prop_3%TYPE;
l_category_prop_4 mgmt_targets.category_prop_4%TYPE;
l_category_prop_5 mgmt_targets.category_prop_5%TYPE;
l_count number(5);
l_service_guid RAW(16);
l_target_guid RAW(16);
l_scope_guid RAW(16);
l_component_guid RAW(16);
l_test_guid RAW(16);
l_key_value VARCHAR2(256);
BEGIN
	
  l_service_guid := mgmt_target.get_target_guid(service_name_in, service_type_in);
	
  BEGIN
    IF test_scope_in = SCOPE_SERVICE THEN
      BEGIN
        l_target_type := target_type_in;
        
        SELECT target_guid, target_guid, type_meta_ver, category_prop_1, category_prop_2,
                category_prop_3, category_prop_4, category_prop_5
          INTO  l_target_guid, l_component_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
                l_category_prop_3, l_category_prop_4, l_category_prop_5
          FROM mgmt_targets
         WHERE target_name = target_name_in
           AND target_type = target_type_in;		
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise MGMT_GLOBAL.target_does_not_exist;
      END;
    ELSE
      BEGIN
        l_target_type := 'host';
        
        SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2,
                h.category_prop_3, h.category_prop_4, h.category_prop_5, t.target_guid
          INTO  l_target_guid,l_type_meta_ver, l_category_prop_1, l_category_prop_2,
                l_category_prop_3, l_category_prop_4, l_category_prop_5, l_component_guid
          FROM mgmt_targets t, mgmt_targets h
         WHERE t.target_name = target_name_in
           AND t.target_type = target_type_in
           AND h.target_name = t.host_name
           AND h.target_type = 'host';
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise MGMT_GLOBAL.target_does_not_exist;      
      END;
    END IF;
	END;
  
  l_scope_guid := get_scope_guid(l_service_guid, l_component_guid, test_scope_in);
  
  l_test_guid := get_test_guid(l_target_guid, metric_guid_in, metric_keys_array_in, l_scope_guid, test_scope_in);
  
	status_out :=0;	
	if(edit_type_in=1)then				
		select count(*) into status_out 
		from mgmt_rca_metric_test
		where target_guid=l_target_guid
		and metric_guid=metric_guid_in
		and scope_guid=l_scope_guid
    and scope=test_scope_in
		and (key_value is null or key_value=metric_keys_array_in(6));		
	end if;
  
	if(status_out=0)then	
		l_threshold:=null;		
		if(use_test_threshold_in=0) then
			begin				
				select x.thr into l_threshold
				from
          (select critical_threshold as thr 
          from mgmt_metrics mm,	mgmt_metric_thresholds mmt
          where 	
          mmt.target_guid = l_target_guid
          and mm.metric_name=metric_name_in				
          and mm.metric_column=metric_column_in
          and mm.target_type=l_target_type
          and mm.type_meta_ver = l_type_meta_ver
          and (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ')
          and (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ')
          and (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ')
          and (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ')
          and (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' ')	
          and mmt.metric_guid=mm.metric_guid
          and (mmt.key_value = metric_keys_array_in(6) or mmt.key_value='' or mmt.key_value=' ')				
          order by mmt.key_value desc) x
				where rownum=1;
				EXCEPTION
				WHEN NO_DATA_FOUND
				THEN l_threshold:=null;				
			end;
		else
			l_threshold:=threshold_in;
		end if;
    
		if(edit_type_in=1)then
      BEGIN
        IF (metric_keys_array_in(6) IS NULL) THEN
          l_key_value := ' ';
        ELSE
          l_key_value := metric_keys_array_in(6);
        END IF;	
        insert into mgmt_rca_metric_test 
          (metric_test_guid, target_guid, metric_guid, key_value, key_part1_value, 
           key_part2_value, key_part3_value, key_part4_value, key_part5_value,
           scope, scope_guid, threshold_source, rca_threshold, operator) 
         values 
          (l_test_guid, l_target_guid, metric_guid_in, l_key_value, metric_keys_array_in(1), 
           metric_keys_array_in(2), metric_keys_array_in(3), metric_keys_array_in(4), metric_keys_array_in(5),	 
           test_scope_in, l_scope_guid, use_test_threshold_in, l_threshold, operator_in);
      END;
		else			
			update mgmt_rca_metric_test 
			set threshold_source=use_test_threshold_in, rca_threshold=l_threshold 
			where metric_test_guid=l_test_guid;
		end if;
	end if;
END create_fault_test;

--Purpose
--local function to concat the keys based on the number of keys
--
FUNCTION concat_keys(num_keys				IN NUMBER,
						key_part1_value		IN VARCHAR2,
						key_part2_value		IN VARCHAR2,
						key_part3_value		IN VARCHAR2,
						key_part4_value		IN VARCHAR2,
						key_part5_value		IN VARCHAR2) 
					return VARCHAR2 IS
l_key_value VARCHAR(2000);
BEGIN
	IF(num_keys>1) THEN
		l_key_value:=concat(key_part1_value,', ');
		l_key_value:=concat(l_key_value,key_part2_value);
	END IF;
	IF(num_keys>2) THEN
		l_key_value:=concat(l_key_value,', ');
		l_key_value:=concat(l_key_value,key_part3_value);
	END IF;
	IF(num_keys>3) THEN
		l_key_value:=concat(l_key_value,', ');
		l_key_value:=concat(l_key_value,key_part4_value);
	END IF;
	IF(num_keys>4) THEN
		l_key_value:=concat(l_key_value,', ');
		l_key_value:=concat(l_key_value,key_part5_value);
	END IF;
	return l_key_value;
END concat_keys;

--
-- PURPOSE:
--Checks to see if a threshold is defined for the metric
--
-- IN Parameters:
--  target_name_in: VARCHAR2 Target name
--  target_type_in: VARCHAR2 Target type
--  metric_name_in: VARCHAR2 Metric name
--  metric_column_in: VARCHAR2 Metric column
--  key_value_in: VARCHAR2 the key value. if it is a composite key the composite key guid is supplied
--
-- OUT Parameters:
--   avail_status_out: NUMBER  Flag indicating if the threshold is defined for the metric.
PROCEDURE check_threshold_availabilty(target_name_in    IN  VARCHAR2,
									target_type_in    IN  VARCHAR2,
									metric_name_in		IN	VARCHAR2,
									metric_column_in	IN	VARCHAR2,						
									key_value_in		  IN	VARCHAR2,
                  test_scope_in     IN  NUMBER,
									avail_status_out	OUT	NUMBER,
                  threshold_out     OUT VARCHAR2) IS
l_target_guid mgmt_targets.target_guid%TYPE;
l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
l_category_prop_1 mgmt_targets.category_prop_1%TYPE;
l_category_prop_2 mgmt_targets.category_prop_2%TYPE;
l_category_prop_3 mgmt_targets.category_prop_3%TYPE;
l_category_prop_4 mgmt_targets.category_prop_4%TYPE;
l_category_prop_5 mgmt_targets.category_prop_5%TYPE;
cate_prop	mgmt_category_prop_array;
l_target_type VARCHAR2(64);
BEGIN			
	BEGIN
    IF test_scope_in = SCOPE_SERVICE THEN
      BEGIN
        l_target_type := target_type_in;
        
        SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2,
             category_prop_3, category_prop_4, category_prop_5
          INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
             l_category_prop_3, l_category_prop_4, l_category_prop_5
          FROM mgmt_targets
         WHERE target_name = target_name_in
           AND target_type = target_type_in;
      
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise MGMT_GLOBAL.target_does_not_exist;
      END;
    ELSE
      BEGIN
        l_target_type := 'host';
        
        SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2,
             h.category_prop_3, h.category_prop_4, h.category_prop_5
          INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
             l_category_prop_3, l_category_prop_4, l_category_prop_5
          FROM mgmt_targets h, mgmt_targets m
         WHERE m.target_name = target_name_in
           AND m.target_type = target_type_in
           AND h.target_name = m.host_name
           AND h.target_type = 'host';
      
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise MGMT_GLOBAL.target_does_not_exist;      
      END;
    END IF;
  END;
  
	SELECT mgmt_category_prop_array(l_target_type,l_type_meta_ver,l_category_prop_1,l_category_prop_2,l_category_prop_3,l_category_prop_4,l_category_prop_5)
	INTO cate_prop FROM DUAL;
	
	threshold_out := get_threshold(l_target_guid, metric_name_in, metric_column_in, key_value_in, cate_prop);
	if(threshold_out is not null or threshold_out <> '' or threshold_out <> ' ')then
		avail_status_out:=1;
	else
		avail_status_out:=0;
	end if;
	
END check_threshold_availabilty;

--
-- PURPOSE:
--Checks to see if a threshold is defined for the metric
--
-- IN Parameters:
--  target_guid_in: RAW Target guid
--  metric_name_in: VARCHAR2 Metric name
--  metric_column_in: VARCHAR2 Metric column
--  key_value_in: VARCHAR2 the key value. if it is a composite key the composite key guid is supplied
--prop_array: mgmt_category_prop_array properties array to encapsulate the target properties
-- Return Parameter:
--   Threshold value if found otherwise null.						
FUNCTION get_threshold(target_guid_in		IN RAW,						
						metric_name_in		IN VARCHAR2,
						metric_column_in	IN VARCHAR2,						
						key_value_in		  IN VARCHAR2,
						prop_array			  IN mgmt_category_prop_array) 
						return VARCHAR2 IS
l_threshold VARCHAR2(256);
BEGIN
	l_threshold:=null;	
	BEGIN
	
		select x.thr into l_threshold
		from(select critical_threshold as thr
			from mgmt_metric_thresholds mmt,
			mgmt_metrics met
			where 
			mmt.target_guid=target_guid_in
			and met.metric_name=metric_name_in
			and met.target_type=prop_array(1)
			and met.metric_column=metric_column_in
			and mmt.metric_guid=met.metric_guid
			and met.type_meta_ver = prop_array(2)
			and (met.category_prop_1 = prop_array(3) or met.category_prop_1 = ' ')
			and (met.category_prop_2 = prop_array(4) or met.category_prop_2 = ' ')
			and (met.category_prop_3 = prop_array(5) or met.category_prop_3 = ' ')
			and (met.category_prop_4 = prop_array(6) or met.category_prop_4 = ' ')
			and (met.category_prop_5 = prop_array(7) or met.category_prop_5 = ' ')
			and (mmt.key_value=key_value_in or mmt.key_value=' ' or mmt.key_value='')
			ORDER BY mmt.key_value desc) x where rownum=1;
			EXCEPTION 
			WHEN NO_DATA_FOUND
			THEN 			
			l_threshold:=null;
	END;
	if(l_threshold='' or l_threshold=' ')then 
		l_threshold:=null;
	end if;
	return l_threshold;
END get_threshold;

--
-- PURPOSE:
--Deletes the fault test
--
-- IN Parameters:
--  metric_test_id_in: VARCHAR2 Metric test guid
--  metric_name_in: VARCHAR2 Metric name
--
-- OUT Parameters:
-- status_out: Status of the removal.
--
PROCEDURE delete_rca_metric(metric_test_id_in		IN	VARCHAR2,
							status_out				OUT NUMBER) IS
BEGIN
	delete from mgmt_rca_metric_test where metric_test_guid=metric_test_id_in;
	status_out := 1;
END delete_rca_metric;
--
-- PURPOSE:
--Gets all the key values identified for the metric.
--
-- IN Parameters:
--  target_name_in: VARCHAR2 Target name
--  target_type_in: VARCHAR2 Target type
--  metric_name_in: VARCHAR2 Metric name
--  key_part_in: the key part value in the case of composite or the just the key in acse of single key.
--  filter_value_in: VARCHAR2 The input filter value to narrow the search to a specific pattern
--  OUT parameters:
-- metric_all_keys_out:cursorType cursor containing the possible keys .
--						
PROCEDURE get_all_metric_keys(target_name_in			IN VARCHAR2,
							target_type_in				IN VARCHAR2,
							metric_name_in				IN VARCHAR2,
							key_part_in					  IN NUMBER,
							filter_value_in				IN VARCHAR2,
              test_scope_in         IN NUMBER,
							metric_all_keys_out		OUT cursorType,
              host_name_out         OUT VARCHAR2) IS
l_target_guid mgmt_targets.target_guid%TYPE;
l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
l_category_prop_1 mgmt_targets.category_prop_1%TYPE;
l_category_prop_2 mgmt_targets.category_prop_2%TYPE;
l_category_prop_3 mgmt_targets.category_prop_3%TYPE;
l_category_prop_4 mgmt_targets.category_prop_4%TYPE;
l_category_prop_5 mgmt_targets.category_prop_5%TYPE;
l_target_type VARCHAR2(64);
BEGIN
	BEGIN
    IF test_scope_in = SCOPE_SERVICE THEN
      BEGIN
        l_target_type := target_type_in;
        
        SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2,
             category_prop_3, category_prop_4, category_prop_5, host_name
          INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
             l_category_prop_3, l_category_prop_4, l_category_prop_5, host_name_out
          FROM mgmt_targets
         WHERE target_name = target_name_in
           AND target_type = target_type_in;
      EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
         raise MGMT_GLOBAL.target_does_not_exist;		
      END;
    ELSE
      BEGIN
        l_target_type := 'host';
        
        SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2,
             h.category_prop_3, h.category_prop_4, h.category_prop_5, h.target_name
          INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
             l_category_prop_3, l_category_prop_4, l_category_prop_5, host_name_out
          FROM mgmt_targets t, mgmt_targets h
         WHERE t.target_name = target_name_in
           AND t.target_type = target_type_in
           AND h.target_name = t.host_name
           AND h.target_type = 'host';
      EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
         raise MGMT_GLOBAL.target_does_not_exist;		
      END;
    END IF;      
	END;	
  
	OPEN metric_all_keys_out FOR
    WITH common AS
      (SELECT DISTINCT key_value,num_keys
        FROM  mgmt_current_metrics mmw,mgmt_metrics mm		
        WHERE mmw.target_guid=l_target_guid
        AND mm.metric_name=metric_name_in		
        AND mm.target_type=l_target_type
        AND mm.type_meta_ver = l_type_meta_ver
        AND (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ')
        AND (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ')
        AND (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ')
        AND (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ')
        AND (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' ')
        AND mm.metric_guid=mmw.metric_guid
        AND key_order=0)
      SELECT common.key_value as KeyValue
      FROM common
      WHERE num_keys=1
      AND lower(key_value) LIKE '%'||lower(filter_value_in)||'%'
    UNION ALL
      SELECT DISTINCT decode(key_part_in,1,key_part1_value,2,key_part2_value,3,key_part3_value,4,key_part4_value,5,key_part5_value) as KeyValue
      FROM common, mgmt_metrics_composite_keys mmck	
      WHERE common.num_keys>1
      AND mmck.composite_key=key_value	
      AND lower(1) like '%'||lower(filter_value_in)||'%';	
END get_all_metric_keys;

--
-- PURPOSE:
--Return the array of key parts based on number of keys.
--
-- IN Parameters:
--  num_keys: NUMBER Number of keys
--  key_part1_value: VARCHAR2 Key part 1 value
--  key_part2_value: VARCHAR2 Key part 2 value
--  key_part3_value: VARCHAR2 Key part 3 value
--  key_part4_value: VARCHAR2 Key part 4 value
--  key_part5_value: VARCHAR2 Key part 5 value
--  Return Parameter:
-- mgmt_medium_string_array of key parts.
--	
FUNCTION get_composite_keys(num_keys			IN NUMBER,
							key_part1_value		IN VARCHAR2,
							key_part2_value		IN VARCHAR2,
							key_part3_value		IN VARCHAR2,
							key_part4_value		IN VARCHAR2,
							key_part5_value		IN VARCHAR2) return mgmt_medium_string_array  IS
BEGIN
	if(num_keys=2) then
		return mgmt_medium_string_array(key_part1_value,key_part2_value);
	elsif (num_keys=3)then 
		return mgmt_medium_string_array(key_part1_value,key_part2_value,key_part3_value);
	elsif (num_keys=4)then 	
		return mgmt_medium_string_array(key_part1_value,key_part2_value,key_part3_value,key_part4_value);
	elsif (num_keys=4)then 
		return mgmt_medium_string_array(key_part1_value,key_part2_value,key_part3_value,key_part4_value,key_part5_value);
	end if;
	return mgmt_medium_string_array('key not found');
		
END get_composite_keys;

--
-- PURPOSE:
--Checks if the input key (or composite key) value is valid.
--
-- IN Parameters:
--  target_name_in: VARCHAR2 Target name
--  target_type_in: VARCHAR2 Target type
--  metric_name_in: VARCHAR2 Metric name
--  num_keys_in: NUMBER number of keys
--  metric_keys_array_in: mgmt_medium_string_array The key ( or composite key) value for which the existence has to verified
--  OUT parameters:
-- keys_status_out:VARCHAR2 Status indiacting the existence of the key( or composite key)
--
PROCEDURE check_keys_availability(target_name_in			IN VARCHAR2,
								target_type_in				IN VARCHAR2,
								metric_name_in				IN VARCHAR2,
								num_keys_in					  IN NUMBER,
								metric_keys_array_in	IN	mgmt_medium_string_array,
                test_scope_in         IN NUMBER,
								keys_status_out				OUT VARCHAR2) IS
l_key mgmt_metric_thresholds.key_value%TYPE;
l_target_guid mgmt_targets.target_guid%TYPE;
BEGIN

BEGIN
    IF test_scope_in = SCOPE_SERVICE THEN
      BEGIN
        SELECT target_guid  INTO l_target_guid
          FROM mgmt_targets
         WHERE target_name = target_name_in
           AND target_type = target_type_in;	
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise MGMT_GLOBAL.target_does_not_exist;		   
      END;
    ELSE
      BEGIN
        SELECT h.target_guid  INTO l_target_guid
          FROM mgmt_targets h, mgmt_targets m
         WHERE m.target_name = target_name_in
           AND m.target_type = target_type_in
           AND h.target_name = m.host_name
           AND h.target_type = 'host';
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
           raise MGMT_GLOBAL.target_does_not_exist;		   
      END;
  	END IF;  
  END;
  
	keys_status_out:='';
	if(num_keys_in=1)then
		begin
			select key_value INTO l_key
			from  mgmt_current_metrics mmw	
			where mmw.target_guid=l_target_guid
			and mmw.key_value=metric_keys_array_in(6)
			and rownum=1;
			EXCEPTION
        when NO_DATA_FOUND
        then l_key:=null;
		end;
	elsif(num_keys_in>1)then
		begin
			select composite_key INTO l_key
			from mgmt_metrics_composite_keys mmck
			where mmck.target_guid=l_target_guid			
			and composite_key=hextoraw(metric_keys_array_in(6))
			and rownum=1;
			exception 
        when NO_DATA_FOUND
        then l_key:=null;
		end;		
	end if;
  
	if(l_key is not null or l_key<>'')then	
		keys_status_out:='1';
	else
		keys_status_out:='0';
	end if;

END check_keys_availability;

--
-- PURPOSE:
--Check the user permision for the service and the dependent target
--
-- IN Parameters:
--  service_guid_in: RAW Serviceguid
--  target_guid_in: RAW Target guid
--
-- OUT Parameters:
-- permissions_out: Flag to indicate the permissions for the user.
--
PROCEDURE check_user_permissions(service_guid_in			IN  RAW,
								target_guid_in				IN  RAW,
								permissions_out				OUT NUMBER) IS
l_has_permission NUMBER(3);								
BEGIN
	permissions_out:=0;--Assuming user has all permissions
	l_has_permission:= mgmt_user.has_priv(mgmt_user.get_current_em_user(), mgmt_user.OPERATOR_TARGET, service_guid_in);
	IF(l_has_permission=0)THEN 
		permissions_out := 1;--has no permissions for the service
	END IF;
	l_has_permission:= mgmt_user.has_priv(mgmt_user.get_current_em_user(), mgmt_user.OPERATOR_TARGET, target_guid_in);
	IF(l_has_permission=0)THEN
		IF(permissions_out=1)THEN
			permissions_out := 2;--has no permissions for the target
		ELSE
			permissions_out := 3;--has no permissions for both the serice and the target
		END IF;
	END IF;
END check_user_permissions;

-- PURPOSE:
--  Delete RCA details associated with a target, typically called during
--  target removal.  This involves the following steps:
--
--      . delete all summary, run and trace rows associated with the target (if its a service)
--      . delete all event assoc rows associated with the target (if its a service)
--      . delete all tests and results associated with the service scope (if its a service)
--      . delete all tests and results associated with the target (service or not)
--      . delete all events associated with the target (service or not)
--
--  Because events are removed, it is possible that event associations may still reference
--  events which have been removed.  Therefore consumers of events must be able to outer-join
--  from the event_assoc table to event table and in cases where events are missing show
--  the appropriate message to the end-user.
--
-- IN:
--  target_name_in  Target Name to be removed
--  target_type_in  Target Type
--
-- OUT: 
--  hasPriv (either mgmt_user.USER_HAS_PRIV or USER_DOES_NOT_HAVE_PRIV)
--
PROCEDURE delete_target_rca(target_name_in IN VARCHAR2, 
                            target_type_in IN VARCHAR2,
                            target_guid_in IN RAW) IS
l_is_service NUMBER :=  mgmt_service.implements_service_interface(target_type_in);
l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user();
l_message VARCHAR2(1000);
BEGIN  
/*
  BEGIN
    l_message := concat('delete_target_rca: ',target_name_in);
    l_message := concat(l_message,' ');
    l_message := concat(l_message,RAWTOHEX(target_guid_in));
    insert into mgmt_rca_log (log_timestamp, log_message) values (SYSDATE, l_message);
  END;
*/  
  
  DELETE FROM mgmt_rca_event WHERE target_guid = target_guid_in;
  DELETE FROM mgmt_rca_test_result WHERE target_guid = target_guid_in;
  DELETE FROM mgmt_rca_metric_test WHERE target_guid = target_guid_in;

-- delete RCA results for the target (since its a service)
  IF l_is_service = 1 THEN
    remove_task_by_target(target_name_in, target_type_in, target_guid_in);
  
    DELETE FROM mgmt_rca_test_result WHERE metric_test_guid IN
      (SELECT metric_test_guid FROM mgmt_rca_metric_test WHERE scope_guid = target_guid_in);
    DELETE FROM mgmt_rca_metric_test WHERE scope_guid = target_guid_in;
    
    DELETE FROM mgmt_rca_trace WHERE event_guid IN
      (SELECT event_guid FROM mgmt_rca_summary WHERE target_guid = target_guid_in);
    DELETE FROM mgmt_rca_event_assoc WHERE event_guid IN
      (SELECT event_guid FROM mgmt_rca_summary WHERE target_guid = target_guid_in);    
    DELETE FROM mgmt_rca_run WHERE event_guid IN
      (SELECT event_guid FROM mgmt_rca_summary WHERE target_guid = target_guid_in);
    DELETE FROM mgmt_rca_summary WHERE target_guid = target_guid_in;
  END IF;
  
END delete_target_rca;

-- PURPOSE:
--  Delete all RCA data associated with a severity; the severity may be one that
--  triggers RCA (a service failure severity) or one that contributes to RCA 
--  results (is associated with an RCA event).  This involves the following steps:
--
--    . delete all summary, run, trace and event assoc associated with the severity (if service)
--    . delete events with source = severity guid
--
--  Because events are removed, it is possible that event associations may still reference
--  events which have been removed.  Therefore consumers of events must be able to outer-join
--  from the event_assoc table to event table and in cases where events are missing show
--  the appropriate message to the end-user.
--
-- IN:
--  target_name_in    Target Name to be removed
--  target_type_in    Target Type
--  severity_guid_in  the severity being removed
--
-- OUT: 
--  hasPriv (either mgmt_user.USER_HAS_PRIV or USER_DOES_NOT_HAVE_PRIV)
--
PROCEDURE delete_severity_rca(target_name_in IN VARCHAR2, 
                            target_type_in IN VARCHAR2,
                            severity_guid_in IN RAW,
                            hasPriv OUT NUMBER) IS
l_target_guid mgmt_targets.target_guid%TYPE;
l_is_service NUMBER :=  mgmt_service.implements_service_interface(target_type_in);
l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user();
l_message VARCHAR2(1000);
BEGIN
/*  
  BEGIN
    l_message := concat('delete_severity_rca: ',target_name_in);
    l_message := concat(l_message,' ');
    l_message := concat(l_message,RAWTOHEX(severity_guid_in));
    insert into mgmt_rca_log (log_timestamp, log_message) values (SYSDATE, l_message);
  END;
*/  
  
  hasPriv := MGMT_USER.USER_HAS_PRIV;

-- lookup the target guid
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);
  IF l_target_guid IS NULL THEN
    RETURN;
  END IF;
  
  -- check if user has priv
  IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_target_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN
    hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV;
    RETURN;
  END IF;
  
  delete_rca_for_severity(severity_guid_in, l_is_service);

END delete_severity_rca;

PROCEDURE delete_rca_for_severity(severity_guid_in IN RAW, is_service IN NUMBER) IS    
BEGIN
  DELETE FROM mgmt_rca_event WHERE source_guid = severity_guid_in;
      
  -- delete RCA results for the severity (since its a service)
  IF is_service = 1 THEN
    DELETE FROM mgmt_rca_trace WHERE event_guid IN
      (SELECT event_guid FROM mgmt_rca_summary WHERE severity_guid = severity_guid_in);
    DELETE FROM mgmt_rca_event_assoc WHERE event_guid IN
      (SELECT event_guid FROM mgmt_rca_summary WHERE  severity_guid = severity_guid_in);
    DELETE FROM mgmt_rca_run WHERE event_guid IN
      (SELECT event_guid FROM mgmt_rca_summary WHERE severity_guid = severity_guid_in);
    DELETE FROM mgmt_rca_summary WHERE severity_guid = severity_guid_in;  
  END IF;
  
END delete_rca_for_severity;

-- PURPOSE:
--  procedure called when a target association (the relationship between
--  a service and target) is removed.
--
-- IN:
--  target_name_in    Target Name to be removed
--  target_type_in    Target Type
--  service_name_in   Service Name to be removed
--  service_type_in   Service Type
--
-- OUT: 
--  hasPriv (either mgmt_user.USER_HAS_PRIV or USER_DOES_NOT_HAVE_PRIV)
-- 
PROCEDURE delete_target_assoc_rca(target_name_in IN VARCHAR2, 
                              target_type_in IN VARCHAR2,
                              service_name_in IN VARCHAR2, 
                              service_type_in IN VARCHAR2) IS
l_target_guid mgmt_targets.target_guid%TYPE;
l_service_guid mgmt_targets.target_guid%TYPE;
l_is_service NUMBER :=  mgmt_service.implements_service_interface(service_type_in);
l_message VARCHAR2(1000);
BEGIN
/*
  BEGIN
    l_message := concat('delete_target_assoc_rca: ',target_name_in);
    l_message := concat(l_message,' ');
    l_message := concat(l_message,service_name_in);
    insert into mgmt_rca_log (log_timestamp, log_message) values (SYSDATE, l_message);
  END;
*/

-- lookup the target guid
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);
  l_service_guid := mgmt_target.get_target_guid(service_name_in, service_type_in);
  IF l_target_guid IS NULL OR l_service_guid IS NULL THEN
    RETURN;
  END IF;  
  
  -- NOTE: 10/7/2004 JMR - longer term we may wish to consider doing a pending
  -- delete where the tests and results for the association are not removed
  -- since if the user removes and then readds the association all of the events
  -- from any previous RCA are gone!
    
  DELETE FROM mgmt_rca_event WHERE source_guid IN
    (SELECT result_guid FROM mgmt_rca_test_result r, mgmt_rca_metric_test t 
      WHERE   t.target_guid = l_target_guid 
      AND     t.scope_guid = l_service_guid
      AND     r.metric_test_guid = t.metric_test_guid);
  
  DELETE FROM mgmt_rca_test_result WHERE metric_test_guid IN 
    (SELECT metric_test_guid FROM mgmt_rca_metric_test
      WHERE   target_guid = l_target_guid
      AND     scope_guid = l_service_guid);
   
  DELETE FROM mgmt_rca_metric_test 
    WHERE   target_guid = l_target_guid
    AND     scope_guid = l_service_guid;  
    
END delete_target_assoc_rca;

-- PURPOSE:
--  procedure used to delete a fault test given the service the test applies
--  to and the guid for the test.
--
-- IN:
--  service_name_in   Service Name to be removed
--  service_type_in   Service Type
--  metric_test_guid_in  GUID (identifier) of the test
--
-- OUT: 
--  hasPriv (either mgmt_user.USER_HAS_PRIV or USER_DOES_NOT_HAVE_PRIV)
-- 
PROCEDURE delete_fault_test(service_name_in IN VARCHAR2, 
                              service_type_in IN VARCHAR2,
                              metric_test_guid_in IN RAW,
                              hasPriv OUT NUMBER) IS
l_service_guid mgmt_targets.target_guid%TYPE;
l_is_service NUMBER :=  mgmt_service.implements_service_interface(service_type_in);
l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user();
BEGIN
  hasPriv := MGMT_USER.USER_HAS_PRIV;
  
  -- lookup the target guid
  l_service_guid := mgmt_target.get_target_guid(service_name_in, service_type_in);
  IF l_service_guid IS NULL THEN
    RETURN;
  END IF;
  
  -- check if user has priv
  IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_service_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN
    hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV;
    RETURN;
  END IF;  
  
  -- NOTE: 10/7/2004 JMR - longer term we may wish to consider doing a pending
  -- delete where the events and results for the test are not removed
  -- since if the user removes and then readds the test all of the events
  -- from any previous RCA are gone!
    
  DELETE FROM mgmt_rca_event WHERE event_guid IN
    (SELECT event_guid FROM mgmt_rca_test_result 
      WHERE metric_test_guid = metric_test_guid_in);
      
  DELETE FROM mgmt_rca_test_result WHERE metric_test_guid = metric_test_guid_in;
      
  DELETE FROM mgmt_rca_metric_test WHERE metric_test_guid = metric_test_guid_in;
  
END delete_fault_test;

-- PURPOSE:
--   This procedure returns all rca details information
--
-- IN Parameters:
--	service_guid_in: Service guid
-- 	severity_guid_in: RAW  severity guid
--  update_id_in: NUMBER update inidcating the rca run or -1 to fetch the latest run detail
-- OUT Parameters:
--	user_privilege_out: NUMBER falg to check user privileges
--	alert_summary_out: cursorType  Cursor containing rca alert summary information
--	run_summary_out: cursorType  Cursor containing rca analysis summary information
--	rca_causes_out: cursorType  Cursor containing rca causes information
--	rca_trace_doc_out: CLOB  clob object containing rca analysis detail information
--
PROCEDURE get_rca_details(service_guid_in				IN  RAW,						
						severity_guid_in				IN  RAW,
						update_id_in					  IN	NUMBER,
            last_id_in              IN  NUMBER,
            root_cause_only_in      IN  NUMBER,
						user_privilege_out			OUT NUMBER,
            last_id_out             OUT NUMBER,
						alert_summary_out				OUT cursorType,
						run_summary_out					OUT cursorType,
						rca_causes_out					OUT cursorType,
            rca_impacts_out         OUT cursorType,
						rca_trace_doc_out				OUT CLOB) IS
l_update_id mgmt_rca_run.update_id%TYPE;
BEGIN
  /*
    if the update_id_in is -1 (requesting the latest) then we'll
    get the last update and compare it to the last_id_in.  If they
    are the same then the client already has the data from the last
    update.  If they are different then we'll continue on and
    load the rest of the information
  */
	user_privilege_out:=mgmt_user.has_priv(mgmt_user.get_current_em_user(), mgmt_user.VIEW_TARGET, service_guid_in);

l_update_id := update_id_in;
	IF(l_update_id = -1) THEN
		BEGIN
			select last_update_id into l_update_id
			from mgmt_rca_summary 
			where severity_guid=severity_guid_in;
			EXCEPTION 
				WHEN NO_DATA_FOUND
				THEN 
				raise_application_error(-20001,'No Root Cause Analysis found for specified severity');				
		END;
	END IF;
  
  last_id_out := l_update_id;
  IF (update_id_in = -1 AND l_update_id = last_id_in) THEN
    RETURN;
  END IF;
  
	rca_trace_doc_out:=NULL;
	IF (user_privilege_out<>0) THEN		
		
		get_alert_summary(severity_guid_in,	alert_summary_out);
		
		BEGIN
			OPEN run_summary_out FOR
			select exception_count "exceptions",cause_count "causesFound",start_time "analysisTime", status "rcaStatus", error_text "errorText",
        last_update_id "totalUpdate",last_run_time "mostRecentUpdate", confidence_possible "confidencePossible", confidence_actual "confidenceActual"
			from
			mgmt_rca_summary mrs,
			mgmt_rca_run mrr
			where
			mrs.severity_guid=severity_guid_in
			and mrr.event_guid=mrs.event_guid
			and mrr.update_id=l_update_id;
			EXCEPTION 
				WHEN NO_DATA_FOUND
				THEN 
				OPEN run_summary_out FOR
				select * from dual where rownum<1;
		END;
		
		BEGIN
			select mrt.trace_doc into rca_trace_doc_out
			from
			mgmt_rca_trace mrt,
			mgmt_rca_summary mrs
			where 
			mrs.severity_guid=severity_guid_in						
			and mrs.event_guid=mrt.event_guid
			and mrt.update_id=l_update_id;	
			EXCEPTION 
				WHEN NO_DATA_FOUND
				THEN rca_trace_doc_out:=NULL;
		END;
		
		get_failure_causes(severity_guid_in, l_update_id, rca_causes_out);

get_affected_services_bysev(severity_guid_in, root_cause_only_in, rca_impacts_out);			
    
	END IF;		
END get_rca_details;

-- PURPOSE:
--   This procedure returns all rca details information
--
-- IN Parameters:
--	service_guid_in: Service guid
-- 	severity_guid_in: RAW  severity guid
-- OUT Parameters:
--	user_privilege_out: NUMBER falg to check user privileges
--	alert_summary_out: cursorType  Cursor containing rca alert summary information
--	rca_analysis_summary_out: cursorType  Cursor containing rca analysis summary information
--	rca_history_out: cursorType  Cursor containing history of rca run for the severity.
--
PROCEDURE get_rca_history(service_guid_in				IN  RAW,						
						severity_guid_in				IN  RAW,						
						user_privilege_out				OUT NUMBER,						
						alert_summary_out				OUT cursorType,
						rca_analysis_summary_out		OUT cursorType,
						rca_history_out					OUT cursorType) IS
BEGIN

user_privilege_out:=mgmt_user.has_priv(mgmt_user.get_current_em_user(), mgmt_user.VIEW_TARGET, service_guid_in);
	
	IF (user_privilege_out<>0) THEN
	
		get_alert_summary(severity_guid_in,	alert_summary_out);
		
		BEGIN
			OPEN rca_analysis_summary_out FOR
			select last_update_id "totalUpdate",last_run_time "mostRecentUpdate",status "rcaStatus"
			from
			mgmt_rca_summary mrs,
			mgmt_rca_run mrr
			where
			mrs.severity_guid=severity_guid_in
			and mrr.event_guid=mrs.event_guid
			and mrr.update_id=mrs.last_update_id;
			EXCEPTION 
				WHEN NO_DATA_FOUND
				THEN 
				OPEN rca_analysis_summary_out FOR
				select * from dual where rownum<1;
		END;
		
		BEGIN
			OPEN rca_history_out FOR
			select start_time "analysisTime", confidence_possible "confidencePossible", confidence_actual "confidenceActual", exception_count "exceptions",
                cause_count "causesFound",update_id "updateId"
			from
			mgmt_rca_summary mrs,
			mgmt_rca_run mrr
			where
			mrs.severity_guid=severity_guid_in
			and mrr.event_guid=mrs.event_guid
			order by mrr.update_id asc;
			EXCEPTION 
				WHEN NO_DATA_FOUND
				THEN 
				OPEN rca_history_out FOR
				select * from dual where rownum<1;
		END;
	END IF;		
END get_rca_history;

-- PURPOSE:
--   This procedure returns all alert summary information
--
-- IN Parameters:
-- 	severity_guid_in: RAW  severity guid
-- OUT Parameters:
--	alert_summary_out: cursorType  Cursor containing rca alert summary information
--
PROCEDURE get_alert_summary(severity_guid_in				IN  RAW,						
							alert_summary_out				OUT cursorType) IS
BEGIN
	BEGIN
			OPEN alert_summary_out FOR
			SELECT  mm.metric_name "metric",mv.violation_level "severity",mv.collection_timestamp "timestamp",
              user_name "user",message "message",message_nlsid "message_nlsid",message_params "message_params"
			FROM    mgmt_violations mv, mgmt_metrics mm, mgmt_targets mt
			WHERE   mv.violation_guid = severity_guid_in
			AND     mm.metric_guid=mv.policy_guid
			AND     mt.target_guid=mv.target_guid
      AND     mm.target_type=mt.target_type
			AND     mm.type_meta_ver=mt.type_meta_ver
			AND    (mm.category_prop_1=mt.category_prop_1 or mm.category_prop_1 = ' ')
			AND    (mm.category_prop_2=mt.category_prop_2 or mm.category_prop_2 = ' ')
			AND    (mm.category_prop_3=mt.category_prop_3 or mm.category_prop_3 = ' ')
			AND    (mm.category_prop_4=mt.category_prop_4 or mm.category_prop_4 = ' ')
			AND    (mm.category_prop_5=mt.category_prop_5 or mm.category_prop_5 = ' ');
			EXCEPTION 
				WHEN NO_DATA_FOUND
				THEN 
				OPEN alert_summary_out FOR
				select * from dual where rownum<1;
			
		END;
END get_alert_summary;
-- PURPOSE:
--  Procedure used to add external events (if they should ever exist).
--
PROCEDURE add_rca_event (event_source_guid_in         IN RAW,
                         event_source_type_in         IN NUMBER,
                         event_target_guid_in         IN RAW,
                         collection_time_in           IN DATE,
                         event_guid_out               OUT RAW) IS                      
BEGIN
-- generate guid for the event
--  event_guid_out := genguid(event_source_guid_in, event_source_type_in, collection_time_in);

-- insert the event
  INSERT INTO mgmt_rca_event (event_guid, source_guid, source_type, collection_time, target_guid)
    VALUES (event_guid_out, event_source_guid_in, event_source_type_in, collection_time_in, event_target_guid_in);
    
END add_rca_event;
          
-- PURPOSE:
--  Procedure used to add external events association (if they should ever exist).
--                        
PROCEDURE associate_event_with_severity (event_guid_in         IN RAW,
                         severity_guid_in             IN RAW,
                         is_leaf_event_in             IN NUMBER) IS
l_event_guid RAW(16);
l_update_id NUMBER;
BEGIN

-- locate RCA for the severity
  BEGIN
    SELECT event_guid, last_update_id INTO l_event_guid, l_update_id 
      FROM mgmt_rca_summary 
      WHERE severity_guid = severity_guid_in;
    EXCEPTION
      WHEN NO_DATA_FOUND
        THEN raise MGMT_GLOBAL.no_such_metric_found;
  END;
  
-- insert the association
  BEGIN
    INSERT INTO mgmt_rca_event_assoc (event_guid, update_id, symptom_event_guid, cause_event_guid, is_root_cause)
      VALUES (l_event_guid, l_update_id, l_event_guid, event_guid_in, is_leaf_event_in);
  END;

END associate_event_with_severity;

-- PURPOSE:
--  public procedure (SDK?) used to add/update a fault test
--
PROCEDURE set_rca_fault_test(
                    service_name_in   IN VARCHAR2,
                    service_type_in   IN VARCHAR2,
                    target_name_in    IN VARCHAR2,
                    target_type_in    IN VARCHAR2,
                    metric_name_in    IN VARCHAR2,
                    metric_column_in  IN VARCHAR2,
                    metric_keys_array_in  IN mgmt_medium_string_array,
                    override_sev_threshold_in  IN NUMBER,
                    threshold_in      IN VARCHAR2,
                    new_test_in       IN NUMBER,
                    hasPriv           OUT NUMBER) IS  
l_target_guid mgmt_targets.target_guid%TYPE;
l_service_guid mgmt_targets.target_guid%TYPE;
l_is_service NUMBER :=  mgmt_service.implements_service_interface(service_type_in);
l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user();
l_metric_guid RAW(16);
l_test_guid RAW(16);
l_operator NUMBER(2);
l_key_str VARCHAR2(256);
l_test_guid_str VARCHAR2(512);
l_metric_status NUMBER;
BEGIN
  hasPriv := MGMT_USER.USER_HAS_PRIV;
  
  -- must be a service
  IF l_is_service = 0 THEN
    RAISE MGMT_GLOBAL.target_does_not_exist;
  END IF;
  
  -- lookup the target guid
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);
  l_service_guid := mgmt_target.get_target_guid(service_name_in, service_type_in);
  IF l_target_guid IS NULL OR l_service_guid IS NULL THEN
     RAISE MGMT_GLOBAL.target_does_not_exist;
  END IF;
  
  -- check if user has priv
  IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_target_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN
    hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV;
    RETURN;
  END IF;
  
  IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_service_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN
    hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV;
    RETURN;
  END IF;  
 
  -- get metric guid and metric column type
  BEGIN    
    -- note: the "rownum = 1" is to ensure only one row is returned. This may
    -- not return the correct metric row if EMD allow empty and non-empty
    -- values of category_prop_* columns to exist at the same time. The problem
    -- will also occur during migration.
    SELECT metric_guid INTO l_metric_guid
      FROM mgmt_metrics m, mgmt_targets t
     WHERE t.target_guid = l_target_guid 
       AND m.target_type = t.target_type
       AND m.metric_name = metric_name_in
       AND m.metric_column = metric_column_in
       AND m.type_meta_ver = t.type_meta_ver
       AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
       AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
       AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
       AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
       AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
       AND ROWNUM = 1;
    EXCEPTION
      WHEN NO_DATA_FOUND
        THEN RAISE MGMT_GLOBAL.no_such_metric_found;
  END; 
  
  -- get operator for the metric threshold
	BEGIN
    SELECT  warning_operator INTO l_operator
    FROM    mgmt_metric_thresholds mmt, mgmt_metrics mm, mgmt_targets t
    WHERE   mmt.target_guid = l_target_guid
    AND     t.target_guid = mmt.target_guid
    AND     mm.metric_name = metric_name_in
    AND     mm.metric_column = metric_column_in
    AND     mm.target_type = t.target_type	
    AND     mm.type_meta_ver = t.type_meta_ver
    AND     (mm.category_prop_1 = t.category_prop_1 OR mm.category_prop_1 = ' ')
    AND     (mm.category_prop_2 = t.category_prop_2 OR mm.category_prop_2 = ' ')
    AND     (mm.category_prop_3 = t.category_prop_3 OR mm.category_prop_3 = ' ')
    AND     (mm.category_prop_4 = t.category_prop_4 OR mm.category_prop_4 = ' ')
    AND     (mm.category_prop_5 = t.category_prop_5 OR mm.category_prop_5 = ' ')	
    AND     mmt.metric_guid = mm.metric_guid	
    AND     (key_value=' ' OR key_value='')
    AND     ROWNUM=1;
    EXCEPTION 
    WHEN NO_DATA_FOUND
    THEN 
      l_operator := 8;
    WHEN OTHERS
      THEN RAISE MGMT_GLOBAL.no_such_metric_found;
  END;
  
  -- generate the test guid           
  
  create_fault_test(service_name_in, service_type_in, target_name_in, target_type_in, 
					l_metric_guid, metric_name_in, metric_column_in, 
          threshold_in, l_operator, override_sev_threshold_in, 
          new_test_in, metric_keys_array_in, SCOPE_SERVICE,
          l_metric_status);  
  
END set_rca_fault_test;

-- PURPOSE:
--  public procedure (SDK?) used to remove a fault test
--
PROCEDURE delete_rca_fault_test(
                    service_name_in   IN VARCHAR2,
                    service_type_in   IN VARCHAR2,
                    target_name_in    IN VARCHAR2,
                    target_type_in    IN VARCHAR2,
                    metric_name_in    IN VARCHAR2,
                    metric_column_in  IN VARCHAR2,
                    metric_keys_array_in  IN mgmt_medium_string_array,
                    hasPriv           OUT NUMBER) IS                  
l_target_guid mgmt_targets.target_guid%TYPE;
l_service_guid mgmt_targets.target_guid%TYPE;
l_is_service NUMBER :=  mgmt_service.implements_service_interface(service_type_in);
l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user();
l_metric_guid RAW(16);
l_test_guid RAW(16);
l_operator mgmt_metric_thresholds.warning_operator%TYPE;
l_key_str VARCHAR2(256);
l_test_guid_str VARCHAR2(512);
BEGIN
  hasPriv := MGMT_USER.USER_HAS_PRIV;
  
  -- must be a service
  IF l_is_service = 0 THEN
    RAISE MGMT_GLOBAL.target_does_not_exist;
  END IF;
  
  -- lookup the target guid
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);
  l_service_guid := mgmt_target.get_target_guid(service_name_in, service_type_in);
  IF l_target_guid IS NULL OR l_service_guid IS NULL THEN
     RAISE MGMT_GLOBAL.target_does_not_exist;
  END IF;
  
  -- check if user has priv
  IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_target_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN
    hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV;
    RETURN;
  END IF;
  
  IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_service_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN
    hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV;
    RETURN;
  END IF;  
 
  -- get metric guid and metric column type
  BEGIN    
    -- note: the "rownum = 1" is to ensure only one row is returned. This may
    -- not return the correct metric row if EMD allow empty and non-empty
    -- values of category_prop_* columns to exist at the same time. The problem
    -- will also occur during migration.
    SELECT metric_guid INTO l_metric_guid
      FROM mgmt_metrics m, mgmt_targets t
     WHERE t.target_guid = l_target_guid 
       AND m.target_type = t.target_type
       AND m.metric_name = metric_name_in
       AND m.metric_column = metric_column_in
       AND m.type_meta_ver = t.type_meta_ver
       AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
       AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
       AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
       AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
       AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')
       AND ROWNUM = 1;
    EXCEPTION
      WHEN NO_DATA_FOUND
        THEN RAISE MGMT_GLOBAL.no_such_metric_found;
  END; 
  
  -- generate the test guid             
  l_test_guid := get_test_guid(l_target_guid, l_metric_guid, metric_keys_array_in, l_service_guid, SCOPE_SERVICE);
  
  DELETE FROM mgmt_rca_metric_test WHERE metric_test_guid = l_test_guid;
  
END delete_rca_fault_test;

FUNCTION get_scope_guid(service_guid_in     IN RAW,
                        target_guid_in      IN RAW,
                        test_scope_in       IN NUMBER)
RETURN RAW IS
l_scope_guid_str VARCHAR2(512);
BEGIN
  IF test_scope_in = SCOPE_SERVICE THEN
    RETURN service_guid_in;
  ELSE
    l_scope_guid_str := RAWTOHEX(service_guid_in) || ';' || RAWTOHEX(target_guid_in);  
    RETURN DBMS_OBFUSCATION_TOOLKIT.md5(input => UTL_RAW.cast_to_raw(l_scope_guid_str));  
  END IF;
END get_scope_guid;

FUNCTION get_test_guid(target_guid_in     IN RAW,
                    metric_guid_in        IN RAW,
                    metric_keys_array_in  IN mgmt_medium_string_array,
                    service_guid_in       IN RAW,
                    test_scope_in         IN NUMBER) 
RETURN RAW IS                    
l_key_str VARCHAR2(256);
l_test_guid_str VARCHAR2(512);
l_key_array smp_emd_string_array;
l_key_data NUMBER(1);
BEGIN

IF metric_keys_array_in IS NULL THEN
      l_key_str := NULL;
  ELSIF metric_keys_array_in.COUNT = 1 THEN
      l_key_str := metric_keys_array_in(1);
  ELSE
    l_key_data := 0;
    BEGIN
      l_key_array := smp_emd_string_array();
      FOR i in 1..metric_keys_array_in.COUNT
      LOOP
        l_key_array.extend(1);
        l_key_array(i) := metric_keys_array_in(i);
        IF metric_keys_array_in(i) IS NOT NULL THEN
          l_key_data := 1;
        END IF;
      END LOOP;
      IF l_key_data = 1 THEN
        l_key_str := RAWTOHEX(mgmt_global.get_composite_key_guid(l_key_array));
      END IF;
    END;
  END IF;
  
  l_test_guid_str := RAWTOHEX(target_guid_in) || ';' || RAWTOHEX(metric_guid_in) || ';' ;
  
  IF l_key_str IS NOT NULL THEN
    l_test_guid_str := l_test_guid_str || l_key_str || ';' ;
  END IF;
  
  l_test_guid_str := l_test_guid_str || test_scope_in || ';' || RAWTOHEX(service_guid_in);
  
  RETURN DBMS_OBFUSCATION_TOOLKIT.md5(
           input => UTL_RAW.cast_to_raw(l_test_guid_str));
           
END get_test_guid;

PROCEDURE get_possible_causes_detail(target_name_in                			    IN VARCHAR2,
                                     target_type_in                			    IN VARCHAR2,
                                     update_id_in							             	IN  NUMBER,
                                     interactive_rca_out                    OUT NUMBER,
                                     severity_guid_out                      OUT RAW,
                                     rca_causeoffailure_cur_out             OUT cursorType,
                                     rca_info_cur_out              	        OUT cursorType) IS
BEGIN
    -- check interactive flag 1st, if supports interactive then we are done (do no more)
    interactive_rca_out := get_interactive_flag(target_name_in, target_type_in);
    IF interactive_rca_out = 1 THEN
      OPEN rca_causeoffailure_cur_out FOR SELECT * FROM dual WHERE ROWNUM<1;
      OPEN rca_info_cur_out FOR SELECT * FROM dual WHERE ROWNUM<1;
      SELECT get_severity(target_name_in, target_type_in) INTO severity_guid_out FROM dual;
      RETURN;
    END IF;

select get_severity(target_name_in,target_type_in) into severity_guid_out from dual;
    get_failure_causes(severity_guid_out, update_id_in, rca_causeoffailure_cur_out);
    
    begin
        OPEN rca_info_cur_out FOR
      select last_run_time "completionTime", confidence_possible "confidencePossible", confidence_actual "confidenceActual", 
              severity_guid_out "severity", error_text "errorText"
			from
			mgmt_rca_summary mrs,
			mgmt_rca_run mrr
			where
			mrs.severity_guid=severity_guid_out
			and mrr.event_guid=mrs.event_guid
            and ((update_id_in=-1 and mrr.update_id=mrs.last_update_id)
                or
                mrr.update_id=update_id_in);
       EXCEPTION
      WHEN NO_DATA_FOUND
        THEN
            OPEN rca_info_cur_out FOR
            select * from dual where rownum<1;
    end;
END get_possible_causes_detail;

FUNCTION get_severity(target_name_in          IN VARCHAR2,
                        target_type_in        IN VARCHAR2) return raw is
l_severity_guid RAW(16) := null;
BEGIN
    begin
      /*
        select mcs.violation_guid into l_severity_guid
        from mgmt_current_violation mcs,mgmt_targets mt,mgmt_metrics mm
        where mt.target_name=target_name_in
        and mt.target_type=target_type_in
        and mcs.target_guid=mt.target_guid
        and mcs.policy_guid=mm.metric_guid
        and mcs.violation_level=mgmt_global.G_SEVERITY_CRITICAL 
        and mm.target_type = mt.target_type 
        and  mt.type_meta_ver = mm.type_meta_ver 
        and (mt.category_prop_1 = mm.category_prop_1 OR mm.category_prop_1 = ' ') 
        and (mt.category_prop_2 = mm.category_prop_2 OR mm.category_prop_2 = ' ') 
        and (mt.category_prop_3 = mm.category_prop_3 OR mm.category_prop_3 = ' ') 
        and (mt.category_prop_4 = mm.category_prop_4 OR mm.category_prop_4 = ' ') 
        and (mt.category_prop_5 = mm.category_prop_5 OR mm.category_prop_5 = ' ')
        and mm.metric_name = mgmt_global.G_AVAIL_METRIC_NAME
        and mm.metric_column = mgmt_global.G_AVAIL_METRIC_COLUMN;
      */
      SELECT  ca.severity_guid INTO l_severity_guid
      FROM    mgmt_targets t, mgmt_current_availability ca
      WHERE   t.target_name = target_name_in
      AND     t.target_type = target_type_in
      AND     ca.target_guid = t.target_guid
      AND     ca.current_status = MGMT_GLOBAL.G_STATUS_DOWN;
        EXCEPTION 
             WHEN NO_DATA_FOUND
                THEN
                l_severity_guid:=null;
		end;
    
    return l_severity_guid;
END get_severity;

--
-- PURPOSE: get the number of services currently affected by the
--          specified target.  Affected means services that are CURRENTLY
--          in a failed state.
--
PROCEDURE get_svcs_aff_count_byname(target_name_in          IN VARCHAR2,
                                target_type_in          IN VARCHAR2,
                                root_cause_only_in      IN NUMBER,
                                svcs_affected_out       OUT NUMBER) IS
l_severity_guid RAW(16) := null;        
BEGIN

IF root_cause_only_in = 1 THEN
    BEGIN
      SELECT COUNT(*) into svcs_affected_out FROM
      (SELECT DISTINCT rs.severity_guid svc_sev_guid
             FROM  mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, 
                   mgmt_rca_summary rs, mgmt_targets t, mgmt_current_violation s
             WHERE t.target_name = target_name_in
               AND t.target_type = target_type_in
               AND e.target_guid = t.target_guid 
               AND a.cause_event_guid = e.event_guid 
               AND a.is_root_cause = ROOT_CAUSE_TYPE
               AND s.violation_guid = rs.severity_guid
               AND rr.event_guid = a.event_guid 
               AND rr.update_id = a.update_id 
               AND rs.event_guid = rr.event_guid
               AND rs.rca_status = RCA_STATUS_OPEN
               AND rr.update_id = rs.last_update_id
        ) x;
    END;
  ELSE
    BEGIN
      SELECT COUNT(*) into svcs_affected_out FROM
      (SELECT DISTINCT rs.severity_guid svc_sev_guid 
             FROM  mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, 
                   mgmt_rca_summary rs, mgmt_targets t, mgmt_current_violation s
             WHERE t.target_name = target_name_in
               AND t.target_type = target_type_in
               AND e.target_guid = t.target_guid 
               AND a.cause_event_guid = e.event_guid 
               AND s.violation_guid = rs.severity_guid
               AND rr.event_guid = a.event_guid 
               AND rr.update_id = a.update_id 
               AND rs.event_guid = rr.event_guid
               AND rs.rca_status = RCA_STATUS_OPEN
               AND rr.update_id = rs.last_update_id
        ) x;
    END;  
  END IF;
END;

--
-- PURPOSE: get the number of services affected (caused) by a particular severity
--
PROCEDURE get_svcs_aff_count_bysev(severity_guid_in          IN RAW,
                                    root_cause_only_in          IN NUMBER,
                                    svcs_affected_out           OUT NUMBER) IS
BEGIN
  IF root_cause_only_in = 1 THEN
    BEGIN
      select count(*) into svcs_affected_out from
        (
          select distinct(rs.event_guid) 
          from mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, mgmt_rca_summary rs
          where e.source_guid = severity_guid_in
          and a.cause_event_guid = e.event_guid
          and a.is_root_cause = ROOT_CAUSE_TYPE
          and rr.event_guid = a.event_guid
          and rr.update_id = a.update_id
          and rs.event_guid = rr.event_guid
          and rs.rca_status = RCA_STATUS_OPEN
        ) x;
    END;
  ELSE
      select count(*) into svcs_affected_out from
        (
          select distinct(rs.event_guid) 
          from mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, mgmt_rca_summary rs
          where e.source_guid = severity_guid_in
          and a.cause_event_guid = e.event_guid
          and rr.event_guid = a.event_guid
          and rr.update_id = a.update_id
          and rs.event_guid = rr.event_guid
          and rs.rca_status = RCA_STATUS_OPEN
        ) x;  
  END IF;
END;

-- PURPOSE:
--   set the interactive RCA flag for a particular target
--
PROCEDURE set_interactive_test(target_name_in   IN VARCHAR2,
                               target_type_in   IN VARCHAR2,
                               interactive_rca_in IN NUMBER,
                               hasPriv OUT NUMBER) 
                               IS
l_interactive_rca NUMBER := 0;
l_collect_ondemand NUMBER := 1;
l_update_prop NUMBER := 1;
l_target_guid RAW(16);
l_resp_stat_metric_guid MGMT_METRICS.metric_guid%TYPE;
l_metric_name MGMT_METRICS.metric_name%TYPE;
l_metric_column MGMT_METRICS.metric_column%TYPE;
l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
l_category_prop_1 mgmt_targets.category_prop_1%TYPE;
l_category_prop_2 mgmt_targets.category_prop_2%TYPE;
l_category_prop_3 mgmt_targets.category_prop_3%TYPE;
l_category_prop_4 mgmt_targets.category_prop_4%TYPE;
l_category_prop_5 mgmt_targets.category_prop_5%TYPE;
l_violation_guid mgmt_current_violation.violation_guid%TYPE;
l_is_service NUMBER :=  mgmt_service.implements_service_interface(target_type_in);
l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user();
BEGIN
  hasPriv := MGMT_USER.USER_HAS_PRIV;
  
  -- must be a service
  IF l_is_service = 0 THEN
    RAISE MGMT_GLOBAL.target_does_not_exist;
  END IF;
  
  -- lookup the target guid
  BEGIN
    SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2,
           category_prop_3, category_prop_4, category_prop_5
      INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2,
           l_category_prop_3, l_category_prop_4, l_category_prop_5
      FROM mgmt_targets
     WHERE target_name = target_name_in
       AND target_type = target_type_in;
  END;  
  IF l_target_guid IS NULL THEN
     RAISE MGMT_GLOBAL.target_does_not_exist;
  END IF;
  
  -- check if user has priv
  IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_target_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN
    hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV;
    RETURN;
  END IF;
  
  -- see if there is a current flag (update or insert)
  BEGIN
    SELECT interactive_rca, collect_on_demand
      INTO l_interactive_rca, l_collect_ondemand
      FROM mgmt_rca_target_props
     WHERE target_guid = l_target_guid;
    EXCEPTION
      WHEN NO_DATA_FOUND
        THEN l_update_prop := 0;
  END;
  
  -- update the flag for this 
  IF l_update_prop = 0
  THEN
    INSERT INTO mgmt_rca_target_props (target_guid, interactive_rca, collect_on_demand) 
      VALUES (l_target_guid, interactive_rca_in, l_collect_ondemand);
  ELSE
    UPDATE mgmt_rca_target_props
    SET interactive_rca = interactive_rca_in
    WHERE target_guid = l_target_guid;
  END IF;
  
  -- if changing to automatic set recovery flag for swith to automatic
  -- check if there is a severity and submit an RCA task if there
  IF interactive_rca_in = 0
  THEN
    BEGIN
      -- get the metric guid for response/status                                       
    l_metric_name := MGMT_GLOBAL.G_AVAIL_METRIC_NAME;
    l_metric_column := MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN;

l_resp_stat_metric_guid := MGMT_METRIC.get_metric_guid_for_props(
        target_type_in,
        l_metric_name ,
        l_metric_column,
        l_type_meta_ver,
        l_category_prop_1,
        l_category_prop_2,
        l_category_prop_3,
        l_category_prop_4,
        l_category_prop_5);      
        
      -- look for a current policy violation
      SELECT violation_guid INTO l_violation_guid
      FROM mgmt_current_violation 
      WHERE target_guid = l_target_guid
      AND policy_guid = l_resp_stat_metric_guid;
      EXCEPTION
        WHEN NO_DATA_FOUND
          THEN l_violation_guid := NULL;
      
      IF l_violation_guid IS NOT NULL
      THEN
        -- have a current status violation so queue a RCA task for it
        queue_rca_task(l_target_guid, l_violation_guid);
      END IF;
    END;
  END IF;
      
END set_interactive_test;

PROCEDURE queue_rca_task(target_guid_in       IN RAW,
                         violation_guid_in    IN RAW)
                         IS
l_task_exists NUMBER(1);    
l_qname VARCHAR2(30);
BEGIN

-- 1st check to see if there are any current tasks for this combination  
  l_qname := emd_notification.GET_QUEUE(EMD_NOTIFICATION.RCA_DEVICE_TYPE, 'RCA', target_guid_in);  
  l_task_exists := check_message(l_qname, target_guid_in, violation_guid_in);
  IF l_task_exists = 0
  THEN
    -- none exists so queue one
    emd_notification.queue_rca_task(target_guid_in, violation_guid_in, EMD_NOTIFICATION.RCA_SEVERITY, 'RCA_NORMAL');
  END IF;
    
END queue_rca_task;

PROCEDURE queue_clear_cache(target_guid_in IN RAW)
IS
v_guid RAW(16);
l_message VARCHAR2(1000);
BEGIN
  /*
  BEGIN
    l_message := concat('queue_clear_cache: ',' ');
    l_message := concat(l_message,' ');
    l_message := concat(l_message,RAWTOHEX(target_guid_in));
    insert into mgmt_rca_log (log_timestamp, log_message) values (SYSDATE, l_message);
  END;
  */
    emd_notification.queue_rca_task(target_guid_in, v_guid, EMD_NOTIFICATION.RCA_SEVERITY, 'RCA_CLEAR_CACHE');
END queue_clear_cache;

-- PURPOSE:
--   get the interactive RCA flag for a particular target
--
PROCEDURE get_interactive_test(target_name_in   IN VARCHAR2,
                               target_type_in   IN VARCHAR2,
                               interactive_rca_out OUT NUMBER) 
                               IS
l_interactive_rca NUMBER := 0;
l_collect_ondemand NUMBER := 1;
l_is_service NUMBER :=  mgmt_service.implements_service_interface(target_type_in);
l_target_guid mgmt_targets.target_guid%TYPE;
BEGIN
  -- must be a service
  IF l_is_service = 0 THEN
    RAISE MGMT_GLOBAL.target_does_not_exist;
  END IF;
  
  -- lookup the target guid
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);
  IF l_target_guid IS NULL THEN
     RAISE MGMT_GLOBAL.target_does_not_exist;
  END IF;
  
  -- see if there is a current flag (update or insert)
  BEGIN
    SELECT interactive_rca, collect_on_demand
      INTO interactive_rca_out, l_collect_ondemand
      FROM mgmt_rca_target_props
     WHERE target_guid = l_target_guid;
    EXCEPTION
      WHEN NO_DATA_FOUND
        THEN interactive_rca_out := 0;
  END;
    
END get_interactive_test;

FUNCTION get_interactive_flag(target_name_in IN VARCHAR2,
                              target_type_in IN VARCHAR2)
                              RETURN NUMBER IS
l_flag NUMBER;
BEGIN
  l_flag := 0;
  BEGIN
    SELECT p.interactive_rca
      INTO l_flag
      FROM mgmt_rca_target_props p, mgmt_targets t
     WHERE p.target_guid = t.target_guid
       AND t.target_name = target_name_in
       AND t.target_type = target_type_in;
    EXCEPTION
      WHEN NO_DATA_FOUND
        THEN l_flag := 0;
  END;
  
  RETURN l_flag;
END get_interactive_flag;

-- PURPOSE:
--    get the list of events associated with a target that impact other services
--    over the period of time specified.  Also return the list of affected services
--    over that same time.
--
PROCEDURE get_events_and_impacts(target_name_in   IN VARCHAR2,
                                 target_type_in   IN VARCHAR2,
                                 num_days_in      IN NUMBER,
                                 events_cursor_out  OUT cursorType,
                                 impacts_cursor_out OUT cursorType) IS
l_target_guid mgmt_targets.target_guid%TYPE;
BEGIN
  -- lookup the target
  l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);
  IF l_target_guid IS NULL THEN
     RAISE MGMT_GLOBAL.target_does_not_exist;
  END IF;
  
  -- get 
END get_events_and_impacts;
    
-- PURPOSE:
--   Return RCA run details and severity details
--
-- IN Parameters:
--	service_guid_in: Service guid
-- 	severity_guid_in: RAW  severity guid
--  update_id_in: NUMBER update inidcating the rca run or -1 to fetch the latest run detail
-- 
-- OUT Parameters:
--	user_privilege_out: NUMBER falg to check user privileges
--	alert_summary_out: cursorType  Cursor containing rca alert summary information
--
PROCEDURE get_sev_summary(service_guid_in				IN  RAW,						
						severity_guid_in				IN  RAW,
            root_cause_only_in      IN  NUMBER,
						hasPriv         				OUT NUMBER,						
						alert_summary_out				OUT cursorType,
            rca_impacts_out         OUT cursorType) IS
BEGIN
	hasPriv := mgmt_user.has_priv(mgmt_user.get_current_em_user(), mgmt_user.VIEW_TARGET, service_guid_in);
	IF (hasPriv<>0) THEN		
			get_alert_summary(severity_guid_in,	alert_summary_out);		
	END IF;		
  
  get_affected_services_bysev(severity_guid_in, root_cause_only_in, rca_impacts_out);			  
END get_sev_summary;

PROCEDURE get_rca_notif_content(severity_guid_in        IN RAW,
							 rca_causeoffailure_cur_out              	OUT cursorType,
               target_timezone_out                      OUT VARCHAR2,
               severity_timestamp_out                   OUT TIMESTAMP)
               IS
BEGIN
		get_failure_causes(severity_guid_in, -1, rca_causeoffailure_cur_out);

BEGIN
      SELECT t.timezone_region, v.collection_timestamp INTO target_timezone_out, severity_timestamp_out
      FROM    mgmt_targets t, mgmt_violations v
      WHERE   v.violation_guid = severity_guid_in
      AND     t.target_guid = v.target_guid;
    END;
    
END get_rca_notif_content;

FUNCTION check_message(qname_in          IN VARCHAR2, 
                       target_guid_in    IN RAW, 
                       violation_guid_in IN RAW) RETURN NUMBER IS
  dq_options      dbms_aq.dequeue_options_t;
  qMsg_properties dbms_aq.message_properties_t;
  l_notif MGMT_NOTIFY_NOTIFICATION;
  l_id RAW(16);
  CURSOR msgs IS
    SELECT msg_id FROM aq$mgmt_notify_qtable where msg_state in ('READY','WAIT');

BEGIN
  
  dq_options.consumer_name := qname_in;
  dq_options.wait := 0;
  dq_options.navigation := DBMS_AQ.FIRST_MESSAGE;
  dq_options.dequeue_mode := DBMS_AQ.BROWSE;
  
  FOR msg IN msgs
  LOOP
    BEGIN
      -- peek the notification
      dq_options.msgid := msg.msg_id;

BEGIN

DBMS_AQ.DEQUEUE(queue_name => 'MGMT_NOTIFY_Q',
                      dequeue_options    => dq_options,
                      message_properties => qMsg_properties,
                      payload            => l_notif,
                      msgid              => l_id);

EXCEPTION
      WHEN NO_NOTIF_AQ_MSG
      THEN
         GOTO next_msg;
      END;
      
      -- check the target and violation
      IF HEXTORAW(l_notif.rule_owner) = target_guid_in AND l_notif.source_guid = violation_guid_in
      THEN
        RETURN 1;
      END IF;
    END;  
  
    << next_msg >>
    NULL;
  END LOOP;
  
  RETURN 0;

END check_message;

PROCEDURE remove_task(qname_in IN VARCHAR2, 
                      msgid_in IN RAW)
IS
  dequeue_options      dbms_aq.dequeue_options_t;
  message_properties   dbms_aq.message_properties_t;
  message              MGMT_NOTIFY_NOTIFICATION;  
  message_id           RAW(16);
BEGIN

dequeue_options.dequeue_mode  := DBMS_AQ.REMOVE;
   dequeue_options.msgid         := msgid_in;
   dequeue_options.consumer_name := qname_in;

DBMS_AQ.DEQUEUE(queue_name => 'MGMT_NOTIFY_Q',
           dequeue_options    => dequeue_options,
           message_properties => message_properties,
           payload            => message,
           msgid              => message_id);

END remove_task;

PROCEDURE remove_task_by_target(target_name_in    IN VARCHAR2,
                                target_type_in    IN VARCHAR2,
                                target_guid_in    IN RAW)
IS
  dq_options      dbms_aq.dequeue_options_t;
  qMsg_properties dbms_aq.message_properties_t;
  l_notif         MGMT_NOTIFY_NOTIFICATION;
  l_id            RAW(16);
  l_del_id        RAW(16);
  l_targ_guid     RAW(16);
  l_message VARCHAR2(1000);
  CURSOR msgs IS
    SELECT msg_id FROM aq$mgmt_notify_qtable where msg_state in ('READY','WAIT');

CURSOR ques IS
    SELECT qname FROM mgmt_notify_queues WHERE qname LIKE 'RCA%';
BEGIN
/*
  BEGIN
    l_message := concat('remove_task_by_target: ',target_name_in);
    l_message := concat(l_message,' ');
    l_message := concat(l_message,RAWTOHEX(target_guid_in));
    insert into mgmt_rca_log (log_timestamp, log_message) values (SYSDATE, l_message);
  END;
  */
  dq_options.wait := 0;
  dq_options.navigation := DBMS_AQ.FIRST_MESSAGE;
  dq_options.dequeue_mode := DBMS_AQ.BROWSE;
  
  l_targ_guid := target_guid_in;
  
  IF l_targ_guid IS NULL
  THEN
    BEGIN
      SELECT  target_guid INTO l_targ_guid
      FROM    mgmt_targets
      WHERE   target_name = target_name_in
      AND     target_type = target_type_in;
    END;  
  END IF;
  
  -- clear any cache entries
  -- queue_clear_cache(l_targ_guid);
    
  FOR que IN ques
  LOOP
    BEGIN
      dq_options.consumer_name  := que.qname;
      dq_options.navigation     := DBMS_AQ.FIRST_MESSAGE;    
      dq_options.msgid          := NULL;
      
      FOR msg IN msgs
      LOOP
        BEGIN
          -- peek the notification
          dq_options.msgid := msg.msg_id;

BEGIN

DBMS_AQ.DEQUEUE(queue_name => 'MGMT_NOTIFY_Q',
                          dequeue_options    => dq_options,
                          message_properties => qMsg_properties,
                          payload            => l_notif,
                          msgid              => l_id);

EXCEPTION
          WHEN NO_NOTIF_AQ_MSG
          THEN
              GOTO next_msg;
          END;
    
          IF HEXTORAW(l_notif.rule_owner) = l_targ_guid
          THEN
            l_del_id := l_id;
          END IF;
        END;  
      
        << next_msg >>
        NULL;
      END LOOP;
      
      IF l_del_id IS NOT NULL
      THEN
        BEGIN
           dq_options.dequeue_mode  := DBMS_AQ.REMOVE;
           dq_options.msgid         := l_del_id;
        
           DBMS_AQ.DEQUEUE(queue_name => 'MGMT_NOTIFY_Q',
                   dequeue_options    => dq_options,
                   message_properties => qMsg_properties,
                   payload            => l_notif,
                   msgid              => l_id);    
        END;
      END IF;
    END;
    
    << next_que >>
    NULL;
  END LOOP;
  
  -- COMMIT;

END remove_task_by_target;

PROCEDURE peek_message(qname_in IN VARCHAR2, notif_que_out OUT NOTIF_QUEUE_MSGS) IS
  i INTEGER := 0;
  l_msgs NOTIF_QUEUE_MSGS := NOTIF_QUEUE_MSGS();
  dq_options      dbms_aq.dequeue_options_t;
  qMsg_properties dbms_aq.message_properties_t;
  l_notif MGMT_NOTIFY_NOTIFICATION;
  l_id RAW(16);
  l_targ_name VARCHAR2(256);
  l_targ_type VARCHAR2(256);
  CURSOR msgs IS
    SELECT msg_id FROM aq$mgmt_notify_qtable where msg_state in ('READY','WAIT');

BEGIN

EXCEPTION
      WHEN NO_NOTIF_AQ_MSG
      THEN
         GOTO next_msg;
      END;
      
      -- get the target name
      BEGIN
          SELECT target_name, target_type INTO l_targ_name, l_targ_type
          FROM mgmt_targets
          WHERE target_guid = HEXTORAW(l_notif.rule_owner);
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN 
            l_targ_name := 'No Such Target';
            l_targ_type := l_notif.rule_owner;
      END;

l_msgs.extend(1);
      i := i + 1;
      l_msgs(i) := NOTIF_QUEUE_MSG(RAWTOHEX(l_id), l_notif, l_targ_name, l_targ_type,
                          qMsg_properties.delay, qMsg_properties.enqueue_time, qMsg_properties.state);
    END;  
  
    << next_msg >>
    NULL;
  END LOOP;
  
  notif_que_out := l_msgs;

END peek_message;
PROCEDURE get_rca_result_set(severity_array_in IN SEVERITY_GUID_ARRAY, results_out OUT RCA_RESULTS_ARRAY) IS
  i INTEGER := 0;
  c INTEGER := 0;
  l_causes RCA_CAUSE_ARRAY := RCA_CAUSE_ARRAY();
  l_cause_cursor cursorType;
  l_timezone VARCHAR2(64);
  l_timestamp TIMESTAMP;
  l_index NUMBER;
  l_targname VARCHAR2(256);
  l_targtype VARCHAR2(64);
  l_sourcetype NUMBER;
  l_eventaction VARCHAR2(512);
  l_resguid RAW(16);
  l_rkeyvalue VARCHAR2(256);
  l_rtime DATE;
  l_rmsg VARCHAR2(4000);
  l_rmsgid VARCHAR2(64);
  l_rmsgp VARCHAR2(4000);
  l_sevguid RAW(16);
  l_vkeyvalue VARCHAR2(256);
  l_vtime DATE;
  l_vmsg VARCHAR2(4000);
  l_vmsgid VARCHAR2(256);
  l_vmsgp VARCHAR2(4000); 
  l_metcolumn VARCHAR2(64);
  l_metname VARCHAR2(64);
  l_guid RAW(16);
BEGIN
   results_out := RCA_RESULTS_ARRAY();
   
   FOR l_index IN 1..severity_array_in.LAST
   LOOP
      l_guid := HEXTORAW(severity_array_in(l_index));
      get_rca_notif_content(l_guid, l_cause_cursor, l_timezone, l_timestamp);
  
      results_out.extend(1);
      i := i + 1;
      results_out(i) := RCA_RESULTS_RECORD(severity_array_in(l_index), l_timezone, l_timestamp, NULL);

results_out(i).cause_array := RCA_CAUSE_ARRAY();
      c := 0;
      LOOP
      FETCH l_cause_cursor INTO l_targname, l_targtype, l_sourcetype, l_eventaction, 
                l_resguid, l_rkeyvalue, l_rtime, l_rmsg, l_rmsgid, l_rmsgp, 
                l_sevguid, l_vkeyvalue, l_vtime, l_vmsg, l_vmsgid, l_vmsgp,
                l_metname, l_metcolumn;
      EXIT WHEN l_cause_cursor%NOTFOUND;
        results_out(i).cause_array.extend(1);
        c := c + 1;

IF l_sourcetype = STATUS_SEVERITY OR l_sourcetype = METRIC_SEVERITY
        THEN
          results_out(i).cause_array(c) := RCA_CAUSE_RECORD(l_targname, l_targtype, l_vtime, l_vmsg, l_vmsgid, l_vmsgp);
        ELSE
          results_out(i).cause_array(c) := RCA_CAUSE_RECORD(l_targname, l_targtype, l_rtime, l_rmsg, l_rmsgid, l_rmsgp);
        END IF;
      END LOOP;
      
   END LOOP;

END get_rca_result_set;

-- Callback to delete RCA data related to a target
PROCEDURE handle_target_delete(p_target_name VARCHAR2, 
                               p_target_type VARCHAR2,
                               p_target_guid RAW)
IS
l_message VARCHAR2(1000);
BEGIN
/*
  BEGIN
    l_message := concat('handle_target_delete: ',p_target_name);
    l_message := concat(l_message,' ');
    l_message := concat(l_message,RAWTOHEX(p_target_guid));
    insert into mgmt_rca_log (log_timestamp, log_message) values (SYSDATE, l_message);
  END;
*/
  delete_target_rca(p_target_name, p_target_type, p_target_guid);
END handle_target_delete;

PROCEDURE handle_assoc_delete(p_assoc_def_name IN VARCHAR2,
                              p_source_target_name IN VARCHAR2,
                              p_source_target_type IN VARCHAR2,
                              p_assoc_target_name IN VARCHAR2,
                              p_assoc_target_type IN VARCHAR2,
                              p_scope_target_name IN VARCHAR2,
                              p_scope_target_type IN VARCHAR2)
IS
BEGIN
  delete_target_assoc_rca(p_assoc_target_name, p_assoc_target_type,
                            p_source_target_name, p_source_target_type);
END handle_assoc_delete;

-- procedure used during data generation for test harness to verify
-- that target has a response-status metric
PROCEDURE validate_target_avail_metric( target_name_in    IN VARCHAR2,
                                        target_type_in    IN VARCHAR2,
                                        p_type_meta_ver   IN VARCHAR2,
                                        p_category_prop_1 IN VARCHAR2,
                                        p_category_prop_2 IN VARCHAR2,
                                        p_category_prop_3 IN VARCHAR2,
                                        p_category_prop_4 IN VARCHAR2,
                                        p_category_prop_5 IN VARCHAR2)
IS
  resp_stat_metric_guid mgmt_metrics.metric_guid%TYPE;                                  
  target_guid mgmt_targets.target_guid%TYPE;
  p_metric_name MGMT_METRICS.metric_name%TYPE;
  p_metric_column MGMT_METRICS.metric_column%TYPE;
BEGIN
  -- lookup the target
  target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in);

--
  -- Check if a host/response/status metric has been defined.  If not, 
  -- create one.  Otherwise, continue on.
  --
  BEGIN
    p_metric_name := MGMT_GLOBAL.G_AVAIL_METRIC_NAME;
    p_metric_column := MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN;

resp_stat_metric_guid := MGMT_METRIC.get_metric_guid_for_props(
        target_type_in,
        p_metric_name ,
        p_metric_column,
        p_type_meta_ver,
        p_category_prop_1,
        p_category_prop_2,
        p_category_prop_3,
        p_category_prop_4,
        p_category_prop_5);
        
  EXCEPTION
    WHEN MGMT_GLOBAL.no_such_metric_found THEN

-- Add Response/Status metric to the host name
      -- Insert 'Response' table metric.
      MGMT_METRIC.create_metric(
                        target_type_in,
                        p_metric_name,
                        p_type_meta_ver,
                        p_metric_type        => MGMT_GLOBAL.G_METRIC_TYPE_TABLE, 
                        p_metric_column_list => MGMT_METRIC_COLUMN_ARRAY(
                            MGMT_METRIC_COLUMN_OBJ.NEW(
                                p_column_name        => MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN,
                                p_column_type        => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
                                p_is_key             => MGMT_GLOBAL.G_FALSE))
                        );

resp_stat_metric_guid := MGMT_METRIC.get_metric_guid_for_props(
          target_type_in,
          p_metric_name,
          p_metric_column,
          p_type_meta_ver,
          p_category_prop_1,
          p_category_prop_2,
          p_category_prop_3,
          p_category_prop_4,
          p_category_prop_5);

END;

-- Add threshold for this metric
  BEGIN
    INSERT INTO mgmt_metric_thresholds
      (target_guid, metric_guid, key_value,
       warning_operator, warning_threshold, critical_operator, critical_threshold,
       num_occurences, num_warnings, num_criticals, eval_order, fixit_job)
    VALUES
      (target_guid, resp_stat_metric_guid, ' ',
       0, ' ', 0, '0', 1, 0, 0, 0, '');
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      -- Ignore duplicate errors
      NULL;
  END;

-- Add data for the Response/Status metric to the host name
  -- Collection time stamp should be in target timezone
  -- loadtimestamp should be in repository timezone
  -- INSERT INTO mgmt_metrics_raw
  --   (target_guid, metric_guid, collection_timestamp,
  --    key_value, value, string_value)
  -- VALUES
  --   (p_target_guid, resp_stat_metric_guid, 
  --    MGMT_GLOBAL.SYSDATE_TZRGN(:new.timezone_region), 
  --    ' ', 1, NULL);

END validate_target_avail_metric;

FUNCTION IS_TEST_BASED_AVAIL ( p_service_target_name IN VARCHAR2, 
                               p_service_target_type IN VARCHAR2) 
          RETURN NUMBER 
IS 
  l_avail_method VARCHAR2(256):= NULL;
  l_is_service NUMBER;
BEGIN    
    l_is_service := mgmt_service.implements_service_interface(p_service_target_type);    
    IF ( l_is_service = 1 ) THEN
      l_avail_method := mgmt_service.get_svc_avail_comp_method(p_service_target_name, p_service_target_type);
    END IF;
 
    IF ( l_avail_method = MGMT_SERVICE.G_TEST_BASED_AVAIL_COMP ) THEN
      RETURN 1;
    ELSIF ( l_avail_method = MGMT_SERVICE.G_SYSTEM_BASED_AVAIL_COMP ) THEN
      RETURN 0;
    END IF;

RETURN -1;
END;

end mgmt_rca;
/
show errors;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de