Rem
Rem $Header: ip_rca_pkgbody.sql 29-jul-2005.03:25:34 pmaddi Exp $
Rem
Rem ip_rca_pkgbody.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ip_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    pmaddi      08/03/05 - Fixing the perfromance problem for the most signficant impacts query
Rem    chyu        07/20/05 - remove the ECHO OFF statement 
Rem    gsbhatia    07/14/05 - Add repmgr header 
Rem    pmaddi      07/03/05 - pmaddi_rca_report
Rem    pmaddi      07/03/05 - Created
Rem

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

CREATE OR REPLACE PACKAGE BODY ip_rca AS

--This method returns the count of services impacted for all root causes(targets) identified in the system
-- along with the % of availablity of the target which caused the impact given start date and end date.
PROCEDURE get_most_significant_impacts(rca_impacts_cursor_out OUT CURSORTYPE,
                                    target_guid_in IN VARCHAR2,
                                    startDate IN DATE,
                                    endDate IN DATE,
												tz_console IN VARCHAR2) IS
	l_days number(3);
	BEGIN
		SELECT (endDate-startDate) INTO l_days FROM dual;
      IF(target_guid_in is null) THEN
         OPEN rca_impacts_cursor_out FOR
           WITH c AS
             (SELECT distinct e.target_guid, a.event_guid
               from mgmt_rca_event_assoc a, mgmt_rca_event e
               WHERE 
               a.is_root_cause = 1
               AND a.cause_event_guid = e.event_guid)
				--			 AND collection_time BETWEEN startDate AND endDate
             SELECT distinct compName, compType, impacts,
                        round(mgmt_target.get_avail_pct_value(compGuid,l_days),2) as availabilityPct,
                              round(duration,2) as duration, compGuid
                        FROM 
                               (SELECT count(distinct(rs.target_guid)) impacts, x.target_name as compName, x.target_type as compType, 
                               x.target_guid as compGuid, duration
                               FROM
                               (                               
                               SELECT t.target_guid,t.target_name, t.target_type, 
																				sum(decode(violation_type, 3, 0, decode(violation_duration,null, 
																				((MGMT_TARGET.SYSDATE_TARGET(t.target_guid)-
																						greatest(mgmt_global.adjust_tz(startDate,tz_console,t.timezone_region) ,
                                                                                       collection_timestamp))*24)*60,
                                                      (violation_duration*60)))) as duration
															FROM
                                    mgmt_violations v, mgmt_targets t,(SELECT distinct target_guid FROM c) causes
                              WHERE causes.target_guid = v.target_guid
                              AND v.target_guid = t.target_guid
															AND violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL
                              AND (
															(violation_duration is null)
															or
															(collection_timestamp BETWEEN
                                                mgmt_global.adjust_tz(startDate,tz_console,t.timezone_region) 
                                                AND mgmt_global.adjust_tz(endDate,tz_console,t.timezone_region))
														   )                               
                               GROUP BY t.target_guid,t.target_name, t.target_type
                                 )x, mgmt_rca_summary rs, c
                             WHERE                  
                             rs.event_guid = c.event_guid                             
                             AND c.target_guid = x.target_guid                             
                             group by x.target_guid,x.target_name, x.target_type,duration
                             ) x;
         ELSE
            OPEN rca_impacts_cursor_out FOR
               WITH c AS
                (SELECT e.target_guid as target_guid
                from mgmt_rca_event_assoc a, mgmt_rca_event e, mgmt_rca_summary rs
                WHERE rs.target_guid = target_guid_in
                  AND rs.event_guid = a.event_guid
                  AND a.is_root_cause = 1
                  AND a.cause_event_guid = e.event_guid
				  --AND collection_time BETWEEN startDate AND endDate
               )
                SELECT distinct compName, compType, impacts,
                  round(mgmt_target.get_avail_pct_value(compGuid,l_days),2) as availabilityPct,
                        round(duration,2) as duration, compGuid
                  FROM                  
                         (SELECT count(distinct(rs.target_guid)) impacts, x.target_name as compName, x.target_type as compType, 
                         x.target_guid as compGuid, duration
                         FROM
                         (
                         SELECT t.target_guid,t.target_name, t.target_type, sum(decode(violation_type, 3, 0, decode(violation_duration,null, 
																				((MGMT_TARGET.SYSDATE_TARGET(t.target_guid)-
																						greatest(mgmt_global.adjust_tz(startDate,tz_console,t.timezone_region) ,
                                                                                       collection_timestamp))*24)*60,
                                                      (violation_duration*60)))) as duration
                              FROM
                              mgmt_violations v, mgmt_targets t,(SELECT distinct target_guid FROM c) causes
                        WHERE causes.target_guid = v.target_guid
                        AND v.target_guid = t.target_guid
								AND violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL
                        AND (
												(violation_duration is null)
												or
												(collection_timestamp BETWEEN
                                          mgmt_global.adjust_tz(startDate,tz_console,t.timezone_region) 
                                          AND mgmt_global.adjust_tz(endDate,tz_console,t.timezone_region))
												)
																
                        GROUP BY t.target_guid,t.target_name, t.target_type
                     )x, mgmt_rca_event rs, c, mgmt_rca_event_assoc a, mgmt_rca_event e
                 WHERE
                 e.target_guid = c.target_guid
                 AND a.cause_event_guid = e.event_guid
                 AND rs.event_guid = a.event_guid
                 AND c.target_guid = x.target_guid
                 group by x.target_guid,x.target_name, x.target_type,duration
                 ) x;
         END IF;
END get_most_significant_impacts;

--This procedure returns the impacted service names and their availabilty
-- for a given root cause identified by rca system over a specified time period.
PROCEDURE get_cause_impacts(rca_impacts_cursor_out OUT CURSORTYPE,
                           target_guid_in IN VARCHAR2,
                           startDate IN DATE,
                           endDate IN DATE,
									tz_console IN VARCHAR2) IS
l_days number(3);
BEGIN
   
   SELECT (endDate-startDate) INTO l_days FROM dual;
   OPEN rca_impacts_cursor_out FOR
      SELECT distinct t.target_name as compName,t.target_type as compType,
         round(mgmt_target.get_avail_pct_value(v.target_guid,l_days),2) as availabilityPct
          FROM  mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr,
                mgmt_rca_summary rs,
                mgmt_targets t, mgmt_violations v
          WHERE 
		  			e.target_guid = target_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 rr.event_guid = rs.event_guid 
            AND rs.target_guid = t.target_guid
						AND v.target_guid = t.target_guid
						AND violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL
						AND (
							(violation_duration is null)
							or
							(v.collection_timestamp BETWEEN 
                mgmt_global.adjust_tz(startDate,tz_console,t.timezone_region) 
								AND mgmt_global.adjust_tz(endDate,tz_console,t.timezone_region))
								)
         ORDER BY compName DESC;

END get_cause_impacts;
end ip_rca;
/
show errors;