Rem
Rem $Header: group_cpf_pkgbody.sql 10-dec-2004.05:43:37 mningomb Exp $
Rem
Rem group_cpf_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2004, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      group_cpf_pkgbody.sql
Rem
Rem    DESCRIPTION
Rem      PL/SQL for getting the group patch advisories
Rem
Rem    NOTES
Rem    
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem       mningomb 12/10/04 - Cutover Critical patch advisories policy to 10.2 
Rem                           policy framework 
Rem       mningomb 11/24/04 - mningomb_cpfps
Rem       mningomb 11/17/04 - 
Rem    mningomb   10/21/04    Created
Rem

CREATE OR REPLACE PACKAGE BODY GROUP_PATCH_ADVISORIES
IS

PROCEDURE getHostHomeCount
  (p_alert_name IN varchar2,
   p_group_name in varchar2,
   p_group_type in varchar2,
   p_host_count OUT integer,
   p_home_count OUT integer)

  IS
   CURSOR count_cur(group_name in varchar2, group_type in varchar2, alert_name varchar2) IS
      select
                 count(unique t.host_name) as host_count,
                 count(unique t.home_location) as home_count
            from
                mgmt_policies p,
                mgmt_current_violation v,
                mgmt_violation_context arv,
                mgmt_violation_context hrv,
                mgmt_violation_context orv,
                (select unique h.target_guid as host_guid,
                    h.host_name as host_name,
                    p.property_value as home_location
                from mgmt_targets t, mgmt_targets h, mgmt_target_properties p, mgmt$group_flat_memberships m
                where m.composite_target_name = group_name and
                      m.composite_target_type = group_type and
                      m.member_target_guid = t.target_guid and
                      t.host_name = h.target_name and
                      h.target_type = 'host' and
                      p.target_guid = t.target_guid and
                      p.property_name  = 'OracleHome') t
            where
                p.author = 'ORACLE' and
                p.target_type = 'host' and
                p.policy_name = 'Critical Patch Advisories for Oracle Homes' and
				p.policy_guid = v.policy_guid and
                v.target_guid = t.host_guid and
			    (v.exempt_code = 0 or
                 (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and     
                v.target_guid = arv.target_guid and
                v.policy_guid = arv.policy_guid  and
                v.key_value = arv.key_value and
                arv.column_name = 'ADVISORY_NAME' and
                v.target_guid = hrv.target_guid and
                v.policy_guid = hrv.policy_guid  and
                v.key_value = hrv.key_value and
                hrv.column_name = 'HOME_LOCATION' and
                hrv.column_str_value = t.home_location and
                v.target_guid = orv.target_guid and
                v.policy_guid = orv.policy_guid  and 
                v.key_value = orv.key_value and
                orv.column_name = 'HOST_NAME' and
                orv.column_str_value = t.host_name and
                arv.column_str_value =  alert_name;

    countRec count_cur%ROWTYPE;
  BEGIN

  
  OPEN count_cur(p_group_name,p_group_type, p_alert_name);
    LOOP
      FETCH count_cur INTO countRec;
      EXIT WHEN count_cur%NOTFOUND;
      p_host_count := countRec.host_count;
      p_home_count := countRec.home_count;
     END LOOP;
   CLOSE count_cur;
  END;


   PROCEDURE get_group_patch_advisories
     ( p_group_name in varchar2,
       p_group_type in varchar2,
       p_advisories OUT advisories_ref_cur)
    IS
     adv_tab patch_advisories_type;
     hostCount integer := 0;
     homeCount integer := 0;

	BEGIN  
	  select patch_adv_type(name, url, impact, abstract, 0,0)
	  bulk collect into adv_tab 
	  from (
        select a.advisory_name name,
             a.url url,
             a.impact impact,
             a.abstract abstract
        from
            mgmt_bug_advisory a,
            (select
                 unique arv.column_str_value as unique_advs
            from
                mgmt_policies p,
                mgmt_current_violation v,
                mgmt_violation_context arv,
                mgmt_violation_context hrv,
                mgmt_violation_context orv,
                (select unique h.target_guid as host_guid,
                    h.host_name as host_name,
                    p.property_value as home_location
                from mgmt_targets t, mgmt_targets h, mgmt_target_properties p, mgmt$group_flat_memberships m
                where m.composite_target_name = p_group_name and
                      m.composite_target_type = p_group_type and
                      m.member_target_guid = t.target_guid and
                      t.host_name = h.target_name and
                      h.target_type = 'host' and
                      p.target_guid = t.target_guid and
                      p.property_name  = 'OracleHome') t
            where
                p.author = 'ORACLE' and
                p.target_type = 'host' and
                p.policy_name = 'Critical Patch Advisories for Oracle Homes' and
                p.policy_guid = v.policy_guid and
                v.target_guid = t.host_guid and
			    (v.exempt_code = 0 or
                 (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and     
                v.target_guid = arv.target_guid and
                v.policy_guid = arv.policy_guid  and
                v.key_value = arv.key_value and
                arv.column_name = 'ADVISORY_NAME' and
                v.target_guid = hrv.target_guid and
                v.policy_guid = hrv.policy_guid  and
                v.key_value = hrv.key_value and
                hrv.column_name = 'HOME_LOCATION' and
                hrv.column_str_value = t.home_location and
                v.target_guid = orv.target_guid and
                v.policy_guid = orv.policy_guid  and
                v.key_value = orv.key_value and
                orv.column_name = 'HOST_NAME' and
                orv.column_str_value = t.host_name) rv
        where
            a.advisory_name = rv.unique_advs);

   FOR indx in 1..adv_tab.count LOOP
    getHostHomeCount(adv_tab(indx).adv_name, p_group_name, p_group_type, hostCount, homeCount);
	dbms_output.PUT_LINE(adv_tab(indx).adv_name);
	adv_tab(indx).host_count := hostCount;
	adv_tab(indx).home_count := homeCount;
   END LOOP;
   OPEN p_advisories FOR
    SELECT adv_name,
           adv_url,
           adv_impact,
           adv_abstract,
           host_count,
           home_count
    FROM   TABLE(CAST(adv_tab AS patch_advisories_type))
    ORDER BY adv_name desc;

   END;
 
     PROCEDURE get_group_advisories_count
    ( p_group_name in varchar2,
      p_group_type in varchar2,
      p_advisories OUT varchar2) IS
	  
  BEGIN
   select count(*) into p_advisories from (
   		  select a.advisory_name name,
             a.url url,
             a.impact impact,
             a.abstract abstract
        from
            mgmt_bug_advisory a,
            (select
                 unique arv.column_str_value as unique_advs
            from
                mgmt_policies p,
                mgmt_current_violation v,
                mgmt_violation_context arv,
                mgmt_violation_context hrv,
                mgmt_violation_context orv,
                (select unique h.target_guid as host_guid,
                    h.host_name as host_name,
                    p.property_value as home_location
                from mgmt_targets t, mgmt_targets h, mgmt_target_properties p, mgmt$group_flat_memberships m
                where m.composite_target_name = p_group_name and
                      m.composite_target_type = p_group_type and
                      m.member_target_guid = t.target_guid and
                      t.host_name = h.target_name and
                      h.target_type = 'host' and
                      p.target_guid = t.target_guid and
                      p.property_name  = 'OracleHome') t
            where
                p.author = 'ORACLE' and
                p.target_type = 'host' and
                p.policy_name = 'Critical Patch Advisories for Oracle Homes' and
                p.policy_guid = v.policy_guid and
                v.target_guid = t.host_guid and
			    (v.exempt_code = 0 or
                 (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and     
                v.target_guid = arv.target_guid and
                v.policy_guid = arv.policy_guid  and
                v.key_value = arv.key_value and
                arv.column_name = 'ADVISORY_NAME' and
                v.target_guid = hrv.target_guid and
                v.policy_guid = hrv.policy_guid  and
                v.key_value = hrv.key_value and
                hrv.column_name = 'HOME_LOCATION' and
                hrv.column_str_value = t.home_location and
                v.target_guid = orv.target_guid and
                v.policy_guid = orv.policy_guid  and
                v.key_value = orv.key_value and
                orv.column_name = 'HOST_NAME' and
                orv.column_str_value = t.host_name) rv
        where
            a.advisory_name = rv.unique_advs);
  END;

   PROCEDURE get_group_affected_homes_count
    ( p_group_name in varchar2,
      p_group_type in varchar2,
      p_homes OUT varchar2) IS
   BEGIN
          select
             count(unique t.home_location) into p_homes

            from
                mgmt_policies p,
                mgmt_current_violation v,
                mgmt_violation_context arv,
                mgmt_violation_context hrv,
                mgmt_violation_context orv,
                (select unique h.target_guid as host_guid,
                    h.host_name as host_name,
                    p.property_value as home_location
                from mgmt_targets t, mgmt_targets h, mgmt_target_properties p, mgmt$group_flat_memberships m
                where m.composite_target_name = p_group_name and
                      m.composite_target_type = p_group_type and
                      m.member_target_guid = t.target_guid and
                      t.host_name = h.target_name and
                      h.target_type = 'host' and
                      p.target_guid = t.target_guid and
                      p.property_name  = 'OracleHome') t
            where
                p.author = 'ORACLE' and
                p.target_type = 'host' and
                p.policy_name = 'Critical Patch Advisories for Oracle Homes' and
                p.policy_guid = v.policy_guid and
                v.target_guid = t.host_guid and
			    (v.exempt_code = 0 or
                 (v.exempt_code = 2 and v.exempt_until <= SYSDATE)) and     
                v.target_guid = arv.target_guid and
                v.policy_guid = arv.policy_guid  and
                v.key_value = arv.key_value and
                arv.column_name = 'ADVISORY_NAME' and
                v.target_guid = hrv.target_guid and
                v.policy_guid = hrv.policy_guid  and
                v.key_value = hrv.key_value and
                hrv.column_name = 'HOME_LOCATION' and
                hrv.column_str_value = t.home_location and
                v.target_guid = orv.target_guid and
                v.policy_guid = orv.policy_guid  and
                v.key_value = orv.key_value and
                orv.column_name = 'HOST_NAME' ;
   
   END;
   


END;
/

show errors;